create PROCEDURE "DBA"."r_sd_stk"
        @as_compcode     char(2),
        @as_stcatcode    char(5),
        @as_edcatcode    char(5),
        @as_stitemno     char(50),
        @as_editemno     char(50),
        @as_stdate       char(10),
        @as_eddate       char(10),
        @as_location     char(10),
        @an_qty_dec      numeric,
        @an_cost_dec     numeric,
        @as_qty_format   char(20),
        @as_cost_format  char(20),
        @as_total_format char(20),
        @ai_stpt         int,
        @ai_stln         int

as
declare  
@ls_12Date      char(10),
@ls_6Date       char(10),
@ls_3Date       char(10),
@ls_4Date       char(10),
@ls_1Date       char(10),
@ld_total       decimal(18,6),
@ls_3SODate     char(10),
@ls_4SODate     char(10),
@ls_1SODate     char(10),
@ls_6SODate     char(10),
@ls_12SODate    char(10),
@ls_RmSODate    char(10)

BEGIN

   create table #stockinvoiced (
      item_no      char(50)      NULL,
      cat_code     char(5)       NULL,
      uom          char(5)       NULL,
      item_descr   char(200)     NULL,
      cat_descr    char(50)      NULL,
      doc_no       char(30)      NULL,
      doc_date     date          NULL,
      qty_12       decimal(18,6) NULL,
      qty_3        decimal(18,6) NULL,
      qty_1        decimal(18,6) NULL,
      warehouse    char(50)      NULL,
      sub_cat      char(5)       NULL,
      subcat_descr char(20)      NULL )

select @ls_12Date = convert(char(10),dateadd(day,1,dateadd(month,-12,@as_eddate)),111)
select @ls_3Date = convert(char(10),dateadd(day,1,dateadd(month,-3,@as_eddate)),111) 
select @ls_4Date = convert(char(10),dateadd(day,1,dateadd(month,-4,@as_eddate)),111) 
select @ls_1Date = convert(char(10),dateadd(day,1,dateadd(month,-1,@as_eddate)),111)
select @ls_6Date = convert(char(10),dateadd(day,1,dateadd(month,-6,@as_eddate)),111)

select @ls_4SODate = convert(char(10),dateadd(day,1,dateadd(month,-4,@as_eddate)),111)
select @ls_3SODate = convert(char(10),dateadd(day,1,dateadd(month,-3,@as_eddate)),111)
select @ls_1SODate = convert(char(10),dateadd(day,1,dateadd(month,-1,@as_eddate)),111)
select @ls_6SODate = convert(char(10),dateadd(day,1,dateadd(month,-6,@as_eddate)),111)
select @ls_12SODate = convert(char(10),dateadd(day,1,dateadd(month,-12,@as_eddate)),111)
select @ls_RmSODate = convert(char(10),dateadd(month,-12,@as_eddate),111)

insert into #stockinvoiced
select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, 
       convert(date,M.doc_date), 
       D.qty_order, 0,0, W.name, S.sub_cat, S.descr
from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S
where M.doc_no = D.doc_no
      and M.comp_code = D.comp_code
      and D.comp_code = A.comp_code
      and D.item_id = A.item_id
      and A.comp_code = C.comp_code
      and A.cat_code = C.cat_code
      and C.comp_code = S.comp_code
      and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat
      and S.cat_pos = @ai_stpt
      and S.cat_len = @ai_stln
      and M.comp_code = W.comp_code
      and M.wh_id = W.wh_id
      and M.inv_status <> 'C'
      and convert(char(10),M.doc_date,111) between @ls_12Date and @as_eddate
      and D.qty_order <> 0
      and D.item_no between @as_stitemno and @as_editemno
      and S.sub_cat between @as_stcatcode and @as_edcatcode
      and W.wh_code like @as_location
      and A.comp_code = @as_compcode

insert into #stockinvoiced
select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, 
       convert(date,M.doc_date), 
       0, D.qty_order, 0, W.name, S.sub_cat, S.descr
from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S
where M.doc_no = D.doc_no
      and M.comp_code = D.comp_code
      and D.comp_code = A.comp_code
      and D.item_id = A.item_id
      and A.comp_code = C.comp_code
      and A.cat_code = C.cat_code
      and C.comp_code = S.comp_code
      and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat
      and S.cat_pos = @ai_stpt
      and S.cat_len = @ai_stln
      and M.comp_code = W.comp_code
      and M.wh_id = W.wh_id
      and M.inv_status <> 'C'
      and convert(char(10),M.doc_date,111) between @ls_3Date and @as_eddate
      and D.qty_order <> 0
      and D.item_no between @as_stitemno and @as_editemno
      and S.sub_cat between @as_stcatcode and @as_edcatcode
      and W.wh_code like @as_location
      and A.comp_code = @as_compcode

insert into #stockinvoiced
select D.item_no, A.cat_code, A.uom, A.item_descr, C.descr, M.doc_no, 
       convert(date,M.doc_date), 
       0, 0, D.qty_order, W.name, S.sub_cat, S.descr
from ar_invmaster M, ar_invdtls D, in_item A, in_category C, in_warehouse W, in_sub_cat S
where M.doc_no = D.doc_no
      and M.comp_code = D.comp_code
      and D.comp_code = A.comp_code
      and D.item_id = A.item_id
      and A.comp_code = C.comp_code
      and A.cat_code = C.cat_code
      and C.comp_code = S.comp_code
      and substring(C.cat_code,@ai_stpt,@ai_stln) = S.sub_cat
      and S.cat_pos = @ai_stpt
      and S.cat_len = @ai_stln
      and M.comp_code = W.comp_code
      and M.wh_id = W.wh_id
      and M.inv_status <> 'C'
      and convert(char(10),M.doc_date,111) between @ls_1Date and @as_eddate
      and D.qty_order <> 0
      and D.item_no between @as_stitemno and @as_editemno
      and S.sub_cat between @as_stcatcode and @as_edcatcode
      and W.wh_code like @as_location
      and A.comp_code = @as_compcode

Select * from #stockinvoiced
END