dbo.gab_rpt_incoming_wh

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE FUNCTION dbo.gab_rpt_incoming_wh(
@date_start date,
@date_stop date,
@jn_doc varchar(255))
RETURNS TABLE  
---XencX---with encryption
AS 
RETURN (  
 select 
  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as id_data_in_hdr
 ,bc_type_name
 ,doc_no
 ,doc_date
 ,recieve_number
 ,recieve_date
 ,supplier_receiver_name
 ,barang_code
 ,barang_name
 ,doc_satuan_code 
 ,isnull(doc_qty,0) as doc_qty
 ,isnull(total_cif,0) as total_cif
 ,invoice_no
 ,invoice_date
 ,valuta_code
 from (
SELECT     
--id_data_in_hdr
 bc_type_name
, doc_no
, doc_date
, recieve_number
, recieve_date
, supplier_receiver_name
, barang_code
, barang_name
, doc_satuan_code
, doc_qty
, total_cif
,null as invoice_no
   ,null as invoice_date
, valuta_code
FROM         dbo.f_pemasukan_per_dok(@date_start, @date_stop)
--WHERE (bc_type_name = CASE WHEN @jn_doc = '-' THEN bc_type_name ELSE @jn_doc END)
where ((nullif(nullif(@jn_doc,''),'-')) is null or(upper(doc_in_from_name)=@jn_doc and (nullif(nullif(@jn_doc,''),'-')) is not null))

--and
 -- SELECT
 -- isnull(upper(dbo.enum_data_in_from.doc_in_from_name),'') as bc_type_name,
 -- isnull(dbo.tbl_bc_in_hdr.BC23No,'') as doc_no,
 -- cast(dbo.tbl_bc_in_hdr.BC23Tg as date) as doc_date,
 -- isnull(dbo.tbl_data_in_hdr.doc_in_no,'') as recieve_number,
 -- cast( dbo.tbl_data_in_hdr.doc_in_date as date) as recieve_date,
 -- isnull(dbo.tbl_bc_in_hdr.PasokNama,'') as supplier_receiver_name,
 -- isnull(isnull((case when (dbo.tbl_data_in_dtl.type_brg=5 or dbo.tbl_data_in_dtl.type_brg=9) then (select top 1 a.part_no from tbl_data_in_dtl a where a.id_data_in_hdr=dbo.tbl_data_in_hdr.id_data_in_hdr and a.part_name_customs = dbo.tbl_data_in_dtl.part_name_customs ) else (select top 1 kode_barang from tbl_kode_barang where nama_barang = dbo.tbl_data_in_dtl.part_name_customs) end), (select top 1 a.part_no from tbl_data_in_dtl a where a.id_data_in_hdr=dbo.tbl_data_in_hdr.id_data_in_hdr and a.part_name_customs = dbo.tbl_data_in_dtl.part_name_customs )),'')as barang_code ,
 -- isnull(dbo.tbl_data_in_dtl.part_name_customs,'') as barang_name,
 -- isnull(dbo.tbl_data_in_dtl.unit_in,'') as doc_satuan_code,
 -- isnull(cast(dbo.tbl_data_in_dtl.qty_in as decimal(24,4)),0) AS doc_qty ,
 ---- (case when dbo.tbl_data_in_dtl.type_brg=5 then sum(dbo.tbl_data_in_dtl.harga) else case when (SUM(dbo.tbl_data_in_dtl.harga * dbo.tbl_data_in_dtl.qty_in)/1000) = 0 then sum(dbo.tbl_data_in_dtl.qty_in) else (SUM(dbo.tbl_data_in_dtl.harga * dbo.tbl_data_in_dtl.qty_in)) end end ) AS total_cif
 -- isnull((case when dbo.tbl_data_in_dtl.type_brg=5 then dbo.tbl_data_in_dtl.harga else case when (dbo.tbl_data_in_dtl.harga * dbo.tbl_data_in_dtl.qty_in) = 0 then dbo.tbl_data_in_dtl.qty_in else ((dbo.tbl_data_in_dtl.harga * dbo.tbl_data_in_dtl.qty_in)/1000) end end ),0) AS total_cif
 -- ,null as invoice_no
 -- ,null as invoice_date
 -- ,'' as valuta_code
 -- FROM
 -- dbo.tbl_data_in_hdr INNER JOIN dbo.tbl_data_in_dtl ON dbo.tbl_data_in_hdr.id_data_in_hdr = dbo.tbl_data_in_dtl.id_data_in_hdr
 -- INNER JOIN dbo.tbl_bc_in_hdr ON dbo.tbl_data_in_hdr.id_terkait = dbo.tbl_bc_in_hdr.id_bc_in_hdr
 -- INNER JOIN dbo.enum_data_in_from ON dbo.tbl_data_in_hdr.doc_in_from_id = dbo.enum_data_in_from.doc_in_from_id
 -- where 1 = 1 and not(dbo.tbl_bc_in_hdr.PasokNama='PASI-AW' and doc_in_date>'2016-09-01') and (dbo.tbl_bc_in_hdr.[BC23No] <> '' or dbo.tbl_bc_in_hdr.[BC23No] is not null) and tbl_data_in_hdr.doc_in_from_id IN (2,3,4,5)
 -- and nullif(dbo.tbl_bc_in_hdr.[BC23No],'') is not null
 -- and doc_in_date between @date_start and @date_stop
 -- and ((nullif(nullif(@jn_doc,''),'-')) is null or(upper(dbo.enum_data_in_from.doc_in_from_name)=@jn_doc and (nullif(nullif(@jn_doc,''),'-')) is not null))
   -- GROUP BY dbo.tbl_data_in_hdr.id_data_in_hdr, dbo.tbl_data_in_hdr.doc_in_no, dbo.tbl_data_in_hdr.doc_in_date, dbo.tbl_bc_in_hdr.BC23No, dbo.tbl_bc_in_hdr.BC23Tg, dbo.tbl_bc_in_hdr.PasokNama, dbo.tbl_data_in_dtl.part_name_customs, dbo.tbl_data_in_dtl.unit_in, dbo.enum_data_in_from.doc_in_from_name, dbo.tbl_data_in_dtl.type_brg
   
  


    ---- REPORT MESIN
   union all
  SELECT       
    bc_type_name
   , doc_no
   , doc_date
   , recieve_number
   , recieve_date
   , supplier_receiver_name
   , barang_code
   , barang_name
   , doc_satuan_code
   , doc_qty
   , total_cif
   ,null as invoice_no
    ,null as invoice_date
    , valuta_code
FROM            dbo.gab_rpt_incoming_wh_mesin( @date_start, @date_stop,replace(@jn_doc,'BC',''))
 )xdata   
)
  
GO