dbo.f_mutasi_bb

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE FUNCTION dbo.f_mutasi_bb
( 
 @tgstart date, @tgend date, @tipebahan varchar(50) 
)
RETURNS  @mutasibb table (
     kodebarang varchar(100) null
    ,namabarang varchar(100) null
    ,qty_saldoawal float null
    ,qty_in float null
    ,qty_out float null
    ,saldo_akhir float null
    ,unit varchar(100) null
    ,stock_opname  float null
    ,selisih float null
    ,adj float null
    )
AS
begin

declare @datesaldoawal date
declare @stsstockopname int, @dtsto date

set @datesaldoawal=(select top 1 date_stock from dbo.tbl_stock_awal where typebahan='RM' order by date_stock desc)
set @stsstockopname = 0

if (
    select count(*) from (
  select count(*) as cnt from [dbo].[tbl_stock_opname] a

      where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
      where b.[date_stock_opname] <= @tgstart and b.typebahan  = 'RM' 
      )  and a.typebahan = 'RM'
      group by part_name_customs
  ) as x

) > 0 
begin
 set @stsstockopname = 1 
 set   @dtsto =  (select max(dt) from (
  select [date_stock_opname] as dt from [dbo].[tbl_stock_opname] a

      where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
      where b.[date_stock_opname] <= @tgstart and b.typebahan  = 'RM'
      )  and a.typebahan = 'RM'
      group by part_name_customs, [date_stock_opname]
  ) as x)
 set @datesaldoawal=dateadd(day, 1, @dtsto)  
end
declare @tgstartx date
if (select count(*) from  [tbl_stock_opname] where typebahan  = 'RM' and [date_stock_opname] between @tgstart and @tgend) > 0
begin
 set @tgstartx = (
  select max(date_stock_opname) from  [tbl_stock_opname] where typebahan  = 'RM' and [date_stock_opname] between @tgstart and @tgend
 )
 if (@tgend <> @tgstartx)
 begin
  set @tgstart = dateadd(day, 1, @tgstartx)  
    end
end

;with
ct_kodebrg (kodebarang, partcustoms, satuan) as
  (
   select kode_barang, nama_barang, satuan from tbl_kode_barang
   
  ) ,
  ct_saldo_awal
  (kodebarang, partcustoms, qty) as (
   select b.kode_barang,b.part_name_customs, sum(a.qty) from dbo.saldoawal(@tgstart,@tgend,'RM') a
   inner join tbl_data_part_hs b on replace(a.part_no, char(32),'')=b.part_no2
   group by b.kode_barang,b.part_name_customs

  
   ),
   ct_brg_in(
    partcustoms, qty
   ) as (
--select part_name_customs, sum(qty) from (
-- SELECT tbl_data_part_hs.part_name_customs,SUM(ISNULL(tbl_data_in_dtl.qty_in, 0) * ISNULL(tbl_data_in_dtl.pengali, 1)) AS qty
--FROM tbl_data_in_dtl INNER JOIN
-- tbl_data_in_hdr ON tbl_data_in_dtl.id_data_in_hdr = tbl_data_in_hdr.id_data_in_hdr RIGHT OUTER JOIN
-- tbl_data_part_hs ON tbl_data_in_dtl.part_number = tbl_data_part_hs.part_no LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where type_brg = 1 and tbl_data_in_hdr.doc_in_date between @tgstart and @tgend
--GROUP BY ISNULL(NULLIF (tbl_data_part_hs_article.part_no, ''), tbl_data_in_dtl.part_number),
--tbl_data_part_hs_article.part_no,
--tbl_data_in_dtl.part_number,
-- tbl_data_part_hs.part_name_customs
-- ) x group by part_name_customs
   SELECT        v_data_part_hs.part_name_customs AS Expr1, SUM(ISNULL([v_data_in_dtl_part_articel].qty_in, 0) * ISNULL([v_data_in_dtl_part_articel].pengali, 1)) AS Expr2
FROM            [v_data_in_dtl_part_articel]  LEFT OUTER JOIN
                         v_data_part_hs ON [v_data_in_dtl_part_articel].[part_no_article] = v_data_part_hs.part_no 
WHERE        ([v_data_in_dtl_part_articel].doc_in_date BETWEEN @tgstart and @tgend )
GROUP BY v_data_part_hs.part_name_customs
   ),

   ct_retur_in(
   partcustoms, qty
   ) as (
   select part_name_customs, sum(qty) from
   ( SELECT        tbl_data_part_hs.part_name_customs, SUM(isnull(qty_return, 0)) AS qty
FROM            tbl_data_out_dtl INNER JOIN
                         tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr RIGHT OUTER JOIN
                         tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
    where status_return = 'RETURNPRODUKSI'
    and  [tbl_data_out_dtl].out_date  between @tgstart and @tgend
    group by tbl_data_part_hs.part_name_customs
   ) x group by part_name_customs

   ),

   ct_brg_out(
   partcustoms, qty
   ) as (
   --select [part_name_customs], sum(qty) from
   --( select [part_name_customs], sum([qty] * isnull(pengali, 1)) as qty
     
    
   -- FROM tbl_data_out_dtl INNER JOIN
   -- tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr
   -- where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
   -- and [tbl_data_out_dtl].out_date between @tgstart and @tgend
   -- group by [part_name_customs]
   --) x group by [part_name_customs]
   select part_name_customs, sum(qty) from
   ( SELECT        tbl_data_part_hs.part_name_customs, SUM((tbl_data_out_dtl.qty * ISNULL(tbl_data_out_dtl.pengali, 1))) AS qty
FROM            tbl_data_out_dtl INNER JOIN
                         tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr RIGHT OUTER JOIN
                         tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
    where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
    and  [tbl_data_out_dtl].out_date  between @tgstart and @tgend
    group by tbl_data_part_hs.part_name_customs
   ) x group by part_name_customs

   ),
   ct_brg_in_awal_periode(
    partcustoms, qty
   ) as (

--select part_name_customs, sum(qty) from (
-- SELECT tbl_data_part_hs.part_name_customs,SUM(ISNULL(tbl_data_in_dtl.qty_in, 0) * ISNULL(tbl_data_in_dtl.pengali, 1)) AS qty
--FROM tbl_data_in_dtl INNER JOIN
-- tbl_data_in_hdr ON tbl_data_in_dtl.id_data_in_hdr = tbl_data_in_hdr.id_data_in_hdr RIGHT OUTER JOIN
-- tbl_data_part_hs ON tbl_data_in_dtl.part_number = tbl_data_part_hs.part_no LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where type_brg = 1 and tbl_data_in_hdr.doc_in_date between @datesaldoawal and dateadd(day, -1, @tgstart)
--GROUP BY ISNULL(NULLIF (tbl_data_part_hs_article.part_no, ''), tbl_data_in_dtl.part_number),
--tbl_data_part_hs_article.part_no,
--tbl_data_in_dtl.part_number,
-- tbl_data_part_hs.part_name_customs
-- ) x group by part_name_customs
   SELECT        v_data_part_hs.part_name_customs AS Expr1, SUM(ISNULL([v_data_in_dtl_part_articel].qty_in, 0) * ISNULL([v_data_in_dtl_part_articel].pengali, 1)) AS Expr2
FROM            [v_data_in_dtl_part_articel]  LEFT OUTER JOIN
                         v_data_part_hs ON [v_data_in_dtl_part_articel].[part_no_article] = v_data_part_hs.part_no 
WHERE        ([v_data_in_dtl_part_articel].doc_in_date BETWEEN @datesaldoawal and dateadd(day, -1, @tgstart) )
GROUP BY v_data_part_hs.part_name_customs
   ),


   
   ct_retur_in_awal_periode(
   partcustoms, qty
   ) as (
   --select [part_name_customs], sum(qty) from
   --( select [part_name_customs], sum([qty] * isnull(pengali, 1)) as qty
     
    
   -- FROM tbl_data_out_dtl INNER JOIN
   -- tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr
   -- where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
   -- and [tbl_data_out_dtl].out_date between @datesaldoawal and dateadd(day, -1, @tgstart)
   -- group by [part_name_customs]
   --) x group by [part_name_customs]
    --) x group by [part_name_customs]
   select part_name_customs, sum(qty) from
   ( SELECT        tbl_data_part_hs.part_name_customs,  sum(isnull(qty_return, 0)) AS qty
FROM            tbl_data_out_dtl INNER JOIN
                         tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr RIGHT OUTER JOIN
                         tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
    where status_return = 'RETURNPRODUKSI'
    and  [tbl_data_out_dtl].out_date between  @datesaldoawal and dateadd(day, -1, @tgstart) 
    group by tbl_data_part_hs.part_name_customs
   ) x group by part_name_customs

   ),

   ct_brg_out_awal_periode(
   partcustoms, qty
   ) as (
   --select [part_name_customs], sum(qty) from
   --( select [part_name_customs], sum([qty] * isnull(pengali, 1)) as qty
     
    
   -- FROM tbl_data_out_dtl INNER JOIN
   -- tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr
   -- where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
   -- and [tbl_data_out_dtl].out_date between @datesaldoawal and dateadd(day, -1, @tgstart)
   -- group by [part_name_customs]
   --) x group by [part_name_customs]
    --) x group by [part_name_customs]
   select part_name_customs, sum(qty) from
   ( SELECT        tbl_data_part_hs.part_name_customs, SUM((tbl_data_out_dtl.qty * ISNULL(tbl_data_out_dtl.pengali, 1)) ) AS qty
FROM            tbl_data_out_dtl INNER JOIN
                         tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr RIGHT OUTER JOIN
                         tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
    where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
    and  [tbl_data_out_dtl].out_date between  @datesaldoawal and dateadd(day, -1, @tgstart) 
    group by tbl_data_part_hs.part_name_customs
   ) x group by part_name_customs

   ),
   cte_inject_in_rm(
   kodebarang, qty 
   ) as 
   (
   select [kode_barang], sum([qty])  from  [dbo].[tbl_inject_rm_in]
   where tgl between @tgstart and @tgend
   group by [kode_barang]

   ),
   cte_inject_in_rm_periode(
   kodebarang, qty 
   ) as 
   (
   select [kode_barang], sum([qty])  from  [dbo].[tbl_inject_rm_in]
   where tgl between  @datesaldoawal and dateadd(day, -1, @tgstart)  
   group by [kode_barang]

   ),
    cte_inject_out_rm(
   kodebarang, qty 
   ) as 
   (
   select [kode_barang], sum([qty])  from  [dbo].[tbl_inject_rm_out]
   where tgl between @tgstart and @tgend
   group by [kode_barang]

   ),
    cte_inject_out_rm_periode(
   kodebarang, qty 
   ) as 
   (
   select [kode_barang], sum([qty])  from  [dbo].[tbl_inject_rm_out]
   where tgl  between @datesaldoawal and dateadd(day, -1, @tgstart) 
   group by [kode_barang]

   ),
   cte_stock_opname_awal(
   partcustoms, qty    
   ) as (
   
    SELECT     tbl_data_part_hs.part_name_customs,    SUM(a.qty) AS Expr1
FROM            tbl_stock_opname AS a RIGHT OUTER JOIN
                         tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no

    where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
    where b.[date_stock_opname] < @tgstart and b.typebahan  = 'RM' 
    )  and a.typebahan = 'RM'
     group by tbl_data_part_hs.part_name_customs
   
   ),
    cte_stock_opname_akhir(
   partcustoms, qty    
   ) as (
   
     SELECT     tbl_data_part_hs.part_name_customs,    SUM(a.qty) AS Expr1
FROM            tbl_stock_opname AS a RIGHT OUTER JOIN
                         tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no

    where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
    where b.[date_stock_opname] <= @tgend  and b.typebahan  = 'RM' 
    )  and a.typebahan = 'RM'
     group by tbl_data_part_hs.part_name_customs
   
   ),
       cte_stock_opname_count(
   partcustoms, cnt, [date_stock_opname]   
   ) as (
   
  
   SELECT        tbl_data_part_hs.part_name_customs, COUNT(*) AS cnt, a.date_stock_opname
FROM            tbl_stock_opname AS a RIGHT OUTER JOIN
                         tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no

    where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
    where b.[date_stock_opname] < @tgstart and b.typebahan  = 'RM' 
    )  and a.typebahan = 'RM'
     group by  tbl_data_part_hs.part_name_customs, [date_stock_opname]
   
   ),
    cte_stock_opname_tgl_akhir(
   partcustoms, qty    
   ) as (
   
    select part_name_customs, sum(qty) from [dbo].[tbl_stock_opname] a

    where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
    where b.[date_stock_opname] = @tgend and b.typebahan  = 'RM' 
    )  and a.typebahan = 'RM'
    group by part_name_customs
   
   )
   
      
   
   
  insert into  @mutasibb (
     kodebarang
    ,namabarang
    ,qty_saldoawal
    ,qty_in 
    ,qty_out
    ,saldo_akhir
    ,unit
    ,stock_opname
    ,selisih
    ,adj
    )
   select ct_kodebrg.kodebarang, ct_kodebrg.partcustoms,
  
   case when isnull(cte_stock_opname_count.cnt,0) = 0 then  
   (isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)  +  isnull(ct_retur_in_awal_periode.qty,0) +  isnull(cte_inject_in_rm_periode.qty,0)) -  (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
   else 
   case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then 
   isnull(cte_stock_opname_awal.qty,0) 
   else  
   --((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) ))
    (isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)  +  isnull(ct_retur_in_awal_periode.qty,0) ) -  (isnull(ct_brg_out_awal_periode.qty,0)))) 
   
   end 
   end  as qty_saldoawal,
  
   isnull(ct_brg_in.qty,0) + isnull(ct_retur_in.qty,0) + isnull(cte_inject_in_rm.qty, 0) as qty_in,
   isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0) as qty_out,
  
  (
  (case when  isnull(cte_stock_opname_count.cnt,0)= 0 then  
   (isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)   +  isnull(ct_retur_in_awal_periode.qty,0)  +  isnull(cte_inject_in_rm_periode.qty,0)) -  (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
   else 
   case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then 
   isnull(cte_stock_opname_awal.qty,0) 
   else  
   (isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)  +  isnull(ct_retur_in_awal_periode.qty,0)  +  isnull(cte_inject_in_rm_periode.qty,0)) -  (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) ))) 
   end 
   end
    + 
   ((isnull(ct_brg_in.qty,0)  + isnull(ct_retur_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
  ) as saldo_akhir
   ,ct_kodebrg.satuan as unit

  -- ,isnull(cte_stock_opname_akhir.qty,0) as sto
  ,case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'RM') = 0 then 0 else  isnull(cte_stock_opname_tgl_akhir.qty,0) end as sto
   
   ,case when cte_stock_opname_tgl_akhir.qty is null then 0 else 
   cte_stock_opname_tgl_akhir.qty -
   (
   (case when  isnull(cte_stock_opname_count.cnt,0)= 0 then  
    (isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)  +  isnull(ct_retur_in_awal_periode.qty,0)  +  isnull(cte_inject_in_rm_periode.qty,0)) -  (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
    else 
    case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then 
    isnull(cte_stock_opname_awal.qty,0) 
    else  
    (isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)  +  isnull(ct_retur_in_awal_periode.qty,0) +  isnull(cte_inject_in_rm_periode.qty,0)) -  (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) ))) 
    end 
    end
     + 
    ((isnull(ct_brg_in.qty,0)   + isnull(ct_retur_in.qty,0)  + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
   ) 
   
   end  as selisih
   ,case when cte_stock_opname_tgl_akhir.qty is null then 0 else 
   cte_stock_opname_tgl_akhir.qty -
   (
   (case when  isnull(cte_stock_opname_count.cnt,0)= 0 then  
    (isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)  +  isnull(ct_retur_in_awal_periode.qty,0) +  isnull(cte_inject_in_rm_periode.qty,0)) -  (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
    else 
    case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then 
    isnull(cte_stock_opname_awal.qty,0) 
    else  
    (isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)  +  isnull(ct_retur_in_awal_periode.qty,0) +  isnull(cte_inject_in_rm_periode.qty,0)) -  (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) ))) 
    end 
    end
     + 
    ((isnull(ct_brg_in.qty,0)   + isnull(ct_retur_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
   ) 
   
   end 
   from ct_kodebrg  
   left outer join ct_saldo_awal on ct_kodebrg.partcustoms = ct_saldo_awal.partcustoms
   left outer join ct_brg_in on ct_kodebrg.partcustoms = ct_brg_in.partcustoms
   left outer join ct_brg_out on ct_kodebrg.partcustoms = ct_brg_out.partcustoms
   left outer join ct_brg_in_awal_periode on ct_kodebrg.partcustoms = ct_brg_in_awal_periode.partcustoms
   left outer join ct_brg_out_awal_periode on ct_kodebrg.partcustoms = ct_brg_out_awal_periode.partcustoms
   left outer join cte_inject_in_rm on ct_kodebrg.kodebarang = cte_inject_in_rm.kodebarang
   left outer join cte_inject_out_rm on ct_kodebrg.kodebarang = cte_inject_out_rm.kodebarang
   left outer join cte_inject_in_rm_periode on ct_kodebrg.kodebarang = cte_inject_in_rm_periode.kodebarang
   left outer join cte_inject_out_rm_periode on ct_kodebrg.kodebarang = cte_inject_out_rm_periode.kodebarang  
   left outer join cte_stock_opname_awal on ct_kodebrg.partcustoms = cte_stock_opname_awal.partcustoms
   left outer join cte_stock_opname_akhir on ct_kodebrg.partcustoms = cte_stock_opname_akhir.partcustoms  
   left outer join cte_stock_opname_count on ct_kodebrg.partcustoms = cte_stock_opname_count.partcustoms 
   left outer join cte_stock_opname_tgl_akhir on ct_kodebrg.partcustoms = cte_stock_opname_tgl_akhir.partcustoms 

    left outer join ct_retur_in on ct_kodebrg.partcustoms = ct_retur_in.partcustoms
   left outer join ct_retur_in_awal_periode on ct_kodebrg.partcustoms = ct_retur_in_awal_periode.partcustoms
  
  
 
     
 return
end
GO