
/************************************************************************
   This CREATE statement is used only for compiling the procedure, that
   is why it is contained Before the create procedure statement.  The
   temporary table at execution time is created in p280_p006.
 ************************************************************************/

create table #t280_fypd	
( f_invtyp_c   char(14) null
, f_invtypnm_x char(30) null
, f_inv1_q     money null
, f_inv1_a     money null
, f_inv2_q     money null 
, f_inv2_a     money null
)      
go
drop proc p280_p006c
go

create procedure p280_p006c
/*
  -----------------------------------------------------------------------
      P280_P006C - Inventory Analysis Report - DETAIL TABLE t280dev.
  -----------------------------------------------------------------------
*/

/*
    ------  INPUT VARIABLES   ------
*/
   @bpd char(6)   = " " 
 , @epd char(6)   = " "
 , @loc char(3)   = " "
 , @div char(3)   = " " 
 , @dpt char(4)   = " "
 , @inv char(4)   = " "
 , @src char(6)   = " "
 , @stg char(3)   = " "
 , @fop char(6)   = " "
 , @afm char(3)   = " "
 , @srt char(15)  = " "
 , @act char(6)   = " "
 , @dev char(15)  = " "
 , @spc char(8)   = " "
 , @exp char(20)  = " None"
 , @ord char(15)  = " Category"
 , @grp char(30)  = " "
 , @dvn char(30)  = " "
 , @bus char(30)  = " "
 , @glv char(1)   = "V"
 
with recompile
as
/*
    ------ INTERNAL VARIABLES ------
*/
/*
    ------     MAIN LOGIC     ------
*/

/***********************************************************************
   Control Section for the Detail Reports
 ***********************************************************************/

if @srt = 'TYPE' 
   goto RptDevType

if @srt = 'LOC'  
   goto RptDevLoc 

if @srt = 'DIV'  
   goto RptDevDiv 

if @srt = 'DEPT' 
   goto RptDevDpt 

if @srt = 'FAMILY'
   goto RptDevFamily

if @srt = 'STAGE'
   goto RptDevStage

if @srt = 'FOP'  
   goto RptDevFOP 

if @srt = 'SOURCE'
   goto RptDevSource

if @srt = 'DEVICE'
   goto RptDevDevice

if @srt = 'DEVICE/SPEC'
   goto RptDevDevSpec

if @srt = 'ACCTCD'
   goto RptDevAcctcd


RptDevType:
begin
insert #t280_fypd 
select a.f_invtyp_c+a.f_invtyp_x	
     , a.f_invtypnm_x
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280type a                                                  
     , d280db1.dbo.t280dev  b                                                   
 where a.f_invtyp_c  = b.f_invtyp_c                                       
   and a.f_invtyp_c  != '0000'                                             
   and b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by a.f_invtyp_c
        , a.f_invtyp_x
        , a.f_invtypnm_x
union all   
select a.f_invtyp_c+a.f_invtyp_x	
     , a.f_invtypnm_x
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280type a                                                  
     , d280db1.dbo.t280dev  b                                                   
 where a.f_invtyp_c  = b.f_invtyp_c                                       
   and a.f_invtyp_c  != '0000'                                             
   and b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by a.f_invtyp_c
        , a.f_invtyp_x
        , a.f_invtypnm_x
union all
select a.f_invtyp_c+a.f_invtyp_x	
     , a.f_invtypnm_x
     , 0
     , $0.00
     , 0
     , $0.00
  from d280db1.dbo.t280type a                                                  
 where a.f_invtyp_c  != '0000'                                             
          
end
   goto ENDIT   

RptDevLoc: 
begin
insert #t280_fypd 
select '    ' 
     , b.f_loc_c
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_loc_c
union all   
select '    ' 
     , b.f_loc_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_loc_c
            
end
   goto ENDIT   


RptDevDiv: 
begin
insert #t280_fypd 
select '    ' 
     , b.f_div_c
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_div_c
union all   
select '    '
     , b.f_div_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_div_c
            
end
   goto ENDIT   


RptDevDpt: 
begin
insert #t280_fypd 
select '    '
     , b.f_dpt_c
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_dpt_c
union all   
select '    '
     , b.f_dpt_c 
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_dpt_c   
            
end
   goto ENDIT   


RptDevFamily:
begin
insert #t280_fypd 
select '    '
     , b.f_afm_c	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_afm_c   
union all   
select '    '      	
     , b.f_afm_c 
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_afm_c
            
end
   goto ENDIT   


RptDevStage: 
begin
insert #t280_fypd 
select '    '      	
     , b.f_stg_c     	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_stg_c
union all   
select '    '    
     , b.f_stg_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_stg_c
            
end
   goto ENDIT   


RptDevFOP:  
begin
insert #t280_fypd 
select '    '      	
     , b.f_fop_c  
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_fop_c   
union all   
select '    '   	
     , b.f_fop_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_fop_c
            
end
   goto ENDIT   


RptDevSource:
begin
insert #t280_fypd 
select '    '  
     , b.f_syssrc_c  	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_syssrc_c
union all   
select '    '
     , b.f_syssrc_c  	
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_syssrc_c
            
end
   goto ENDIT   


RptDevDevice:
begin
insert #t280_fypd 
select '    '  
     , b.i_nsid_c  	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.i_nsid_c  
union all   
select '    '
     , b.i_nsid_c  	
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.i_nsid_c  
            
end
   goto ENDIT   



RptDevDevSpec:
begin
insert #t280_fypd 
select '    '  
     , b.i_nsid_c+"  "+b.i_spec_c
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.i_nsid_c  
        , b.i_spec_c  	
union all   
select '    '
     , b.i_nsid_c+"  "+b.i_spec_c
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.i_nsid_c  
        , b.i_spec_c  	
            
end
   goto ENDIT   


RptDevAcctcd:
begin
insert #t280_fypd 
select '    '  
     , b.f_acct_c  	
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @bpd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_acct_c  
union all   
select '    '
     , b.f_acct_c  	
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280dev  b                                                   
 where b.f_fypd      = @epd
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_glval_i   like @glv
 group by b.f_acct_c  
            
end
   goto ENDIT   


ENDIT: 

/************************************************************************
   Return to the calling environment
 ************************************************************************/

return ( 0 )
go
