dbo.gab_rpt_mutasi_mesin_all

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


CREATE FUNCTION dbo.gab_rpt_mutasi_mesin_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_machine_code group by kdbrg,nama_barang,satuan
 -- select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_machine_code group by kdbrg,nama_barang,satuan
 -- UNION ALL
 -- select t1.kdbrg, t1.nama_barang, t1.satuan from AW.DB_PASI_20201106.dbo.tbl_master_sparepart_code as t1
 -- LEFT JOIN AW.DB_PASI_20201106.dbo.tbl_master_machine_code t2 ON t2.kdbrg = t1.kdbrg
 -- WHERE t2.kdbrg IS NULL group by t1.kdbrg, t1.nama_barang, t1.satuan
 --),
 --ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from AW.DB_PASI_20201106.dbo.fn_saldo_awal_mesin(@date_start,@date_stop) group by kodebarang),
 --ct_brg_in (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive between @date_start and @date_stop group by kode_brg),
 --ct_brg_in_ceisa (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date between @date_start and @date_stop AND ( TIPE_ITEM='MECHINE' OR TIPE_ITEM='SPAREPART' ) group by KODE_BARANG),
 --ct_brg_out (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_nodaf between @date_start and @date_stop group by kode_brg),
 --ct_brg_out_sparepart (kodebarang,qty) as (select kode_barang,sum(pengeluaran) from AW.DB_PASI_20201106.dbo.tbl_stock_out_sparepart where tanggal_pengeluaran 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_machine where tgl_sto between @date_start and @date_stop group by kode_barang),
 --ct_brg_in_awal_periode (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive between case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_machine) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_machine order by tgl_sto desc) else '2010-01-01'end and dateadd(day,-1, @date_start) group by kode_brg),
 --ct_brg_in_ceisa_awal_periode (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date between case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_machine) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_machine order by tgl_sto desc) else '2010-01-01'end and dateadd(day,-1, @date_start) AND ( TIPE_ITEM='MECHINE' OR TIPE_ITEM='SPAREPART' ) group by KODE_BARANG),
 --ct_brg_out_awal_periode (kodebarang, qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_nodaf between case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_machine) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_machine 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_in_ceisa_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) + isnull(ct_brg_in_ceisa.qty,0)as decimal(24,4)) as pemasukan,
 -- cast(isnull(ct_brg_out.qty,0) + isnull(ct_brg_out_sparepart.qty,0) as decimal(24,4)) as pengeluaran,
 -- cast(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_in_ceisa_awal_periode.qty,0)- isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) + isnull(ct_brg_in_ceisa.qty,0) - isnull(ct_brg_out.qty,0) - isnull(ct_brg_out_sparepart.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_machine 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) - isnull(ct_brg_out_sparepart.qty,0) end) as decimal(24,4)) as selisih,
 -- 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_in_ceisa on ct_kodebrg.kodebarang = ct_brg_in_ceisa.kodebarang
 -- left outer join ct_brg_out on ct_kodebrg.kodebarang = ct_brg_out.kodebarang
 -- left outer join ct_brg_in_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_awal_periode.kodebarang
 -- left outer join ct_brg_in_ceisa_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_ceisa_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
 -- left outer join ct_brg_out_sparepart on ct_kodebrg.kodebarang = ct_brg_out_sparepart.kodebarang
 ct_kodebrg (kodebarang,namabarang,satuan) as (
 --select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_machine_code group by kdbrg,nama_barang,satuan
  select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_machine_code group by kdbrg,nama_barang,satuan
  UNION ALL 
  select t1.kdbrg, t1.nama_barang, t1.satuan from AW.DB_PASI_20201106.dbo.tbl_master_sparepart_code as t1
  LEFT JOIN AW.DB_PASI_20201106.dbo.tbl_master_machine_code t2 ON t2.kdbrg = t1.kdbrg
  WHERE t2.kdbrg IS NULL  group by t1.kdbrg, t1.nama_barang, t1.satuan 
 ),
 ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from aw_fn_saldo_awal_mesin(@date_start,@date_stop)  group by kodebarang),  
 ct_brg_in (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive between @date_start and @date_stop group by kode_brg),
 ct_brg_in_ceisa (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date between @date_start and @date_stop AND ( TIPE_ITEM='MECHINE' OR TIPE_ITEM='SPAREPART' ) group by KODE_BARANG),
 ct_brg_out (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_nodaf between @date_start and @date_stop group by kode_brg),
 ct_brg_out_sparepart (kodebarang,qty) as (select kode_barang,sum(pengeluaran) from AW.DB_PASI_20201106.dbo.tbl_stock_out_sparepart where tanggal_pengeluaran 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_machine where tgl_sto between @date_start and @date_stop group by kode_barang),
 ct_brg_in_awal_periode (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive  between  case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_machine) then   (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_machine order by tgl_sto desc) else '2010-01-01'end  and dateadd(day,-1, @date_start) group by kode_brg),
 ct_brg_in_ceisa_awal_periode (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date  between  case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_machine) then   (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_machine order by tgl_sto desc) else '2010-01-01'end  and dateadd(day,-1, @date_start) AND ( TIPE_ITEM='MECHINE' OR TIPE_ITEM='SPAREPART' ) group by KODE_BARANG),
 ct_brg_out_awal_periode (kodebarang, qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_nodaf between case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_machine) then   (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_machine 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_in_ceisa_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) + isnull(ct_brg_in_ceisa.qty,0)as decimal(24,4))  as pemasukan,
   cast(isnull(ct_brg_out.qty,0) + isnull(ct_brg_out_sparepart.qty,0) as decimal(24,4))  as pengeluaran,
   cast(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_in_ceisa_awal_periode.qty,0)- isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) +  isnull(ct_brg_in_ceisa.qty,0) - isnull(ct_brg_out.qty,0) - isnull(ct_brg_out_sparepart.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_machine 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_in_ceisa_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) +  isnull(ct_brg_in_ceisa.qty,0)  - isnull(ct_brg_out.qty,0) - isnull(ct_stock_opname.qty,0)  - isnull(ct_brg_out_sparepart.qty,0)  end) as decimal(24,4))  as selisih,
   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_in_ceisa on ct_kodebrg.kodebarang = ct_brg_in_ceisa.kodebarang   
   left outer join ct_brg_out on ct_kodebrg.kodebarang = ct_brg_out.kodebarang
   left outer join ct_brg_in_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_awal_periode.kodebarang
   left outer join ct_brg_in_ceisa_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_ceisa_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 
   left outer join ct_brg_out_sparepart on ct_kodebrg.kodebarang = ct_brg_out_sparepart.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(isnull(selisih,0) as decimal(24,4)) as selisih,
 unit as satuan_code, 
 '' as keterangan
 from dbo.f_mutasi_mpk(@date_start,@date_stop) 
)  

GO