dbo.gab_rpt_mutasi_scrap_all

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


CREATE FUNCTION dbo.gab_rpt_mutasi_scrap_all(
@date_start date
,@date_stop date  
)
 
RETURNS TABLE  
---XencX---with encryption
AS 
RETURN (   
   

with
 
 --ct_kodebrg (kodebarang,namabarang,satuan) as (select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_scrap_code group by kdbrg,nama_barang,satuan),
 --ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from AW.DB_PASI_20201106.dbo.fn_saldo_awal_scrap(@date_start,@date_stop) group by kodebarang),
 
 --ct_brg_in (kodebarang,qty) as (select kdbrg,sum(qty) from AW.DB_PASI_20201106.dbo.fn_scrap_in_join_all(@date_start,@date_stop) group by kdbrg),
 
 --ct_brg_out (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between @date_start and @date_stop group by kode_brg),
 --ct_brg_out_ceisa_scrap_25 (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa_scrap_25 where tanggal_daftar between @date_start and @date_stop group by KODE_BARANG),
 --ct_brg_out_ceisa_scrap_41 (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa_scrap_41 where tanggal_daftar between @date_start and @date_stop group by KODE_BARANG),
 --ct_penyesuaian (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_adjustment_scrap where tgl_sto between @date_start and @date_stop group by kode_barang),
 --ct_stock_opname (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_stock_opname_scrap where tgl_sto between @date_start and @date_stop group by kode_barang),
 
 --ct_brg_in_awal_periode (kodebarang,qty) as (select kdbrg,sum(qty) from AW.DB_PASI_20201106.dbo.fn_scrap_in_join_all( case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_scraper) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_scraper order by tgl_sto desc) else'2010-01-01' end , dateadd(day,-1, @date_start)) group by kdbrg),
 --ct_brg_out_awal_periode (kodebarang, qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_scraper) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_scraper order by tgl_sto desc) else'2010-01-01' end and dateadd(day,-1, @date_start) group by kode_brg)
 
 -- select
 -- ct_kodebrg.kodebarang as barang_code
 --, ct_kodebrg.namabarang as barang_name
 --,cast( isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0) as decimal(24,4)) as saldo_awal
 --,cast( isnull(ct_brg_in.qty,0) as decimal(24,4)) as pemasukan
 --,cast( ( isnull(ct_brg_out.qty,0) + isnull(ct_brg_out_ceisa_scrap_25.qty,0) + isnull(ct_brg_out_ceisa_scrap_41.qty,0)) as decimal(24,4)) as pengeluaran
 --,cast( isnull(ct_penyesuaian.qty,0) as decimal(24,4)) as penyesuaian
 --,cast( (isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) -( isnull(ct_brg_out.qty,0) + isnull(ct_brg_out_ceisa_scrap_25.qty,0) + isnull(ct_brg_out_ceisa_scrap_41.qty,0)) as decimal(24,4)) as saldo_akhir
 --,cast( isnull(ct_stock_opname.qty,0) as decimal(24,4)) as stock_opname
 ----,cast( (case when (select top 1 qty from AW.DB_PASI_20201106.dbo.tbl_stock_opname_scrap where tgl_sto between @date_start and @date_stop ) is null then 0
 ---- else (isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) - (isnull(ct_brg_out.qty,0) + isnull(ct_brg_out_ceisa_scrap_25.qty,0) + isnull(ct_brg_out_ceisa_scrap_41.qty,0)) - isnull(ct_stock_opname.qty,0) end) as decimal(24,4)) as selisih
 --,0 as selisih
 --, isnull(ct_kodebrg.satuan ,'') as satuan_code
 --,'' as keterangan
 --from ct_kodebrg
 -- left outer join ct_saldo_awal on ct_kodebrg.kodebarang = ct_saldo_awal.kodebarang
 -- left outer join ct_brg_in on ct_kodebrg.kodebarang = ct_brg_in.kodebarang
 -- left outer join ct_brg_out on ct_kodebrg.kodebarang = ct_brg_out.kodebarang
 -- left outer join ct_brg_out_ceisa_scrap_25 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_scrap_25.kodebarang
 -- left outer join ct_brg_out_ceisa_scrap_41 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_scrap_41.kodebarang
 -- left outer join ct_penyesuaian on ct_kodebrg.kodebarang = ct_penyesuaian.kodebarang
 -- left outer join ct_brg_in_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_awal_periode.kodebarang
 -- left outer join ct_brg_out_awal_periode on ct_kodebrg.kodebarang = ct_brg_out_awal_periode.kodebarang
 -- left outer join ct_stock_opname on ct_kodebrg.kodebarang = ct_stock_opname.kodebarang
 ct_kodebrg (kodebarang,namabarang,satuan) as (select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_scrap_code group by kdbrg,nama_barang,satuan),
 ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from aw_fn_saldo_awal_scrap(@date_start,@date_stop)  group by kodebarang),  
 
 ct_brg_in (kodebarang,qty) as (select kdbrg,sum(qty) from aw_fn_scrap_in_join_all(@date_start,@date_stop) group by kdbrg),
 
 ct_brg_out (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between @date_start and @date_stop group by kode_brg),
 ct_brg_out_ceisa_scrap_25 (kodebarang,qty) as (select int_kode_barang,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa_scrap_25 where tanggal_daftar between @date_start and @date_stop group by int_kode_barang),
 ct_brg_out_ceisa_scrap_41 (kodebarang,qty) as (select int_kode_barang,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa_scrap_41 where tanggal_daftar between @date_start and @date_stop group by int_kode_barang),
 ct_penyesuaian (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_adjustment_scrap where tgl_sto between @date_start and @date_stop group by kode_barang),
 ct_stock_opname (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_stock_opname_scrap where tgl_sto between @date_start and @date_stop group by kode_barang),
 
 ct_brg_in_awal_periode (kodebarang,qty) as (select kdbrg,sum(qty) from aw_fn_scrap_in_join_all( case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_scraper)  then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_scraper order by tgl_sto desc) else'2010-01-01'  end , dateadd(day,-1, @date_start)) group by kdbrg),
 ct_brg_out_awal_periode (kodebarang, qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between  case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_scraper)  then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_scraper order by tgl_sto desc) else'2010-01-01'  end and dateadd(day,-1, @date_start) group by kode_brg)    ,
  ct_brg_out_ceisa_scrap_25_awal (kodebarang, qty) as (select int_kode_barang,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa_scrap_25 where tanggal_daftar between  case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_scraper)  then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_scraper order by tgl_sto desc) else'2010-01-01'  end and dateadd(day,-1, @date_start) group by int_kode_barang)    ,
 ct_brg_out_ceisa_scrap_41_awal (kodebarang, qty) as (select int_kode_barang,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa_scrap_41 where tanggal_daftar between  case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_scraper)  then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_scraper order by tgl_sto desc) else'2010-01-01'  end and dateadd(day,-1, @date_start) group by int_kode_barang)    
  
  select 
   ct_kodebrg.kodebarang as barang_code
 , ct_kodebrg.namabarang as barang_name
 ,cast( isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)  - isnull(ct_brg_out_ceisa_scrap_25_awal.qty,0) -isnull(ct_brg_out_ceisa_scrap_41_awal.qty,0) as decimal(24,4)) as saldo_awal
 ,cast( isnull(ct_brg_in.qty,0) as decimal(24,4)) as pemasukan
 ,cast( (isnull(ct_brg_out.qty,0) + isnull(ct_brg_out_ceisa_scrap_25.qty,0) + isnull(ct_brg_out_ceisa_scrap_41.qty,0) )as decimal(24,4)) as pengeluaran
 ,cast( isnull(ct_penyesuaian.qty,0) as decimal(24,4)) as penyesuaian
 ,cast( (isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) - isnull(ct_brg_out_ceisa_scrap_25_awal.qty,0) -isnull(ct_brg_out_ceisa_scrap_41_awal.qty,0) + isnull(ct_brg_in.qty,0) - (isnull(ct_brg_out.qty,0) + isnull(ct_brg_out_ceisa_scrap_25.qty,0) + isnull(ct_brg_out_ceisa_scrap_41.qty,0) )  as decimal(24,4)) as saldo_akhir
 ,cast( isnull(ct_stock_opname.qty,0) as decimal(24,4)) as stock_opname
 --,cast( (case when (select top 1 qty from AW.DB_PASI_20201106.dbo.tbl_stock_opname_scrap where tgl_sto between @date_start and @date_stop ) is null then 0
 -- else (isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) - isnull(ct_brg_out.qty,0) - isnull(ct_stock_opname.qty,0) end) as decimal(24,4)) as selisih
 ,0 as selisih
 , isnull(ct_kodebrg.satuan ,'') as satuan_code
 ,'' as keterangan
 from ct_kodebrg  
  left outer join ct_saldo_awal on ct_kodebrg.kodebarang = ct_saldo_awal.kodebarang
  left outer join ct_brg_in on ct_kodebrg.kodebarang = ct_brg_in.kodebarang
  left outer join ct_brg_out on ct_kodebrg.kodebarang = ct_brg_out.kodebarang
  left outer join ct_brg_out_ceisa_scrap_25 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_scrap_25.kodebarang
  left outer join ct_brg_out_ceisa_scrap_41 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_scrap_41.kodebarang
  left outer join ct_penyesuaian on ct_kodebrg.kodebarang = ct_penyesuaian.kodebarang 
  left outer join ct_brg_in_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_awal_periode.kodebarang
  left outer join ct_brg_out_awal_periode on ct_kodebrg.kodebarang = ct_brg_out_awal_periode.kodebarang 
  left outer join ct_brg_out_ceisa_scrap_25_awal on ct_kodebrg.kodebarang = ct_brg_out_ceisa_scrap_25_awal.kodebarang 
  left outer join ct_brg_out_ceisa_scrap_41_awal on ct_kodebrg.kodebarang = ct_brg_out_ceisa_scrap_41_awal.kodebarang 
  left outer join ct_stock_opname on ct_kodebrg.kodebarang = ct_stock_opname.kodebarang   
 
 union all
 select  kodebarang as barang_code,
 namabarang as barang_name,
 cast(qty_saldoawal as decimal(24,4)) as saldo_awal,
 cast(qty_in as decimal(24,4)) as pemasukan,
 cast(qty_out as decimal(24,4)) as pengeluaran,
 cast( adj as decimal(24,4))  as penyesuaian ,
 cast(saldo_akhir as decimal(24,4)) as saldo_akhir,
 cast(stock_opname as decimal(24,4)) as stock_opname,
 cast(0 as decimal(24,4)) as selisih,
 unit as satuan_code,
 '' as keterangan
 from dbo.f_mutasi_scr(@date_start,@date_stop) 
)  

GO