dbo.fn_rpt_kb_ekspor

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fn_rpt_kb_ekspor( 
  @start_date date
, @end_date date
, @source varchar(255) = 'ALL'
) 

RETURNS  @rpt_kb_ekspor TABLE (
 id bigint identity(1,1) 
 , data_source varchar(255) 
 , NOMOR_AJU varchar(255)
 , NOMOR_DAFTAR varchar(255)
 , TANGGAL_DAFTAR date
 , URAIAN varchar(255)
 , JUMLAH_SATUAN decimal(38,4)
 , KODE_SATUAN varchar(255)
 , CIF decimal(38,2)
 , KODE_VALUTA varchar(255)
 , NDPBM decimal(38,4)
 , CIF_RUPIAH decimal(38,2)
 , TANGGAL_REKAM date NULL
 , KETERANGAN varchar(255) NULL
 , NOMOR_INVOICE varchar(255)
 , BRUTO decimal(38,4)
 , JUMLAH_KEMASAN decimal(38,4)
 , KODE_KEMASAN varchar(255) 
  )  
WITH EXECUTE AS CALLER 
AS
BEGIN   

if(@source = 'All' OR @source = 'WH')
 begin
  ;WITH
   cte_hdr (
    urut,
    id_sa_hdr, tableid, NOMOR_AJU, NOMOR_DAFTAR, TANGGAL_DAFTAR, URAIAN, JUMLAH_SATUAN, KODE_SATUAN, CIF, KODE_VALUTA, 
    NDPBM, CIF_RUPIAH, TANGGAL_REKAM, KETERANGAN, JUMLAH_KEMASAN, KODE_KEMASAN
   ) as (
   SELECT
    ROW_NUMBER() OVER (PARTITION BY a.tableid, a.id_sa_hdr ORDER BY a.tableid, a.id_sa_hdr),
    id_sa_hdr, tableid, NOMOR_AJU, NOMOR_DAFTAR, TANGGAL_DAFTAR, URAIAN, JUMLAH_SATUAN, KODE_SATUAN, CIF, KODE_VALUTA, 
    NDPBM, CIF_RUPIAH, TANGGAL_REKAM, KETERANGAN, JUMLAH_KEMASAN, KODE_KEMASAN
    from (Select 2 as tableid
      , b.id_bc_out_hdr as id_sa_hdr
      , b.CAR AS NOMOR_AJU
      , b.NODAFT as NOMOR_DAFTAR
      , b.TGDAFT as TANGGAL_DAFTAR
      , COncat(a.URBRG1, ' ', a.URBRG2,' ',a.URBRG3) as URAIAN
      , a.JMSATUAN as JUMLAH_SATUAN
      , a.JNSATUAN as KODE_SATUAN
      , a.DNilInv  as CIF
      , b.KDVAL as KODE_VALUTA
      , dbo.get_rate_valuta(b.TGDAFT,b.KDVAL) as NDPBM
      , isnull(a.DNilInv *  dbo.get_rate_valuta(b.TGDAFT,b.KDVAL),0) as CIF_RUPIAH
      , NULL as TANGGAL_REKAM
      , NULL as KETERANGAN
      , a.JMKOLI as JUMLAH_KEMASAN
      , a.JNKOLI as KODE_KEMASAN
       from tbl_bc_out_dtl a
       Left Join tbl_bc_out_hdr b ON a.id_bc_out_hdr = b.id_bc_out_hdr
       where b.NODAFT is not null AND b.TGDAFT is not null
        AND (b.TGDAFT BETWEEN @start_date AND @end_date)
        AND b.typedok = 'BC30'

      UNION ALL

      Select 3 as tableid
      , b.ID_HEADER
      , b.NOMOR_AJU
      , b.NOMOR_DAFTAR
      , b.TANGGAL_DAFTAR
      , a.URAIAN
      , a.JUMLAH_SATUAN
      , a.KODE_SATUAN
      , a.CIF
      , b.KODE_VALUTA
      , dbo.get_rate_valuta(b.TANGGAL_DAFTAR,b.KODE_VALUTA) as NDPBM
      , isnull(a.CIF *  dbo.get_rate_valuta(b.TANGGAL_DAFTAR,b.KODE_VALUTA),0) as CIF_RUPIAH
      , NULL as TANGGAL_REKAM
      , NULL as KETERANGAN
      , a.JUMLAH_KEMASAN
      , a.KODE_KEMASAN
       from dt_ms_tpb_out_barang a
       Left Join dt_ms_tpb_out_header b ON a.ID_HEADER = b.ID_HEADER
       where b.NOMOR_DAFTAR is not null AND b.TANGGAL_DAFTAR is not null
        AND (b.TANGGAL_DAFTAR BETWEEN @start_date AND @end_date)
        AND b.KODE_DOKUMEN_PABEAN IN ('30')

    ) a
   ), 
   ct_invoice (
    urut,
    tableid,
    id_sa_hdr,
    NOMOR_DOKUMEN,
    TANGGAL_DOKUMEN
   ) as (
    SELECT
    ROW_NUMBER() OVER (PARTITION BY a.tableid, a.id_sa_hdr ORDER BY a.tableid, a.id_sa_hdr),
    id_sa_hdr
    , tableid
    , NoDok
    , TgDok
    FROM (select 2 as tableid
       , b.id_bc_out_hdr as id_sa_hdr
       , a.NoDok
       , a.TgDok
         from tbl_bc_out_dok a
      Left Join tbl_bc_out_hdr b ON a.id_bc_out_hdr = b.id_bc_out_hdr
      Where a.KdDok = '380'
      AND b.NODAFT is not null AND b.TGDAFT is not null
          AND (b.TGDAFT BETWEEN @start_date AND @end_date)
          AND b.typedok = 'BC30'

     UNION ALL
     Select 3 as tableid
       , b.ID_HEADER
       , a.NOMOR_DOKUMEN
       , a.TANGGAL_DOKUMEN
         from dt_ms_tpb_out_dokumen a
      Left Join dt_ms_tpb_out_header b ON a.ID_HEADER = b.ID_HEADER
      Where a.KODE_JENIS_DOKUMEN = '380'
      AND b.NOMOR_DAFTAR is not null AND b.TANGGAL_DAFTAR is not null
          AND (b.TANGGAL_DAFTAR BETWEEN @start_date AND @end_date)
          AND b.KODE_DOKUMEN_PABEAN IN ('30')

    ) a 
   ), 
   ct_sumary_header (
    urut,
    tableid,
    id_sa_hdr,
    BRUTO
   ) as (
    SELECT
    ROW_NUMBER() OVER (PARTITION BY a.tableid, a.id_sa_hdr ORDER BY a.tableid, a.id_sa_hdr),
    tableid
    ,id_sa_hdr 
    , Bruto
    FROM (
     Select 2 as tableid
       , a.id_bc_out_hdr as id_sa_hdr
       , a.Bruto
         from tbl_bc_out_hdr a
      Where a.NODAFT is not null AND a.TGDAFT is not null
          AND (a.TGDAFT BETWEEN @start_date AND @end_date)
          AND a.typedok = 'BC30'

     UNION ALL
  
     Select  3 as tableid
       , a.ID_HEADER
       , a.Bruto
         from dt_ms_tpb_out_header a
      Where a.NOMOR_DAFTAR is not null AND a.TANGGAL_DAFTAR is not null
          AND (a.TANGGAL_DAFTAR BETWEEN @start_date AND @end_date)
          AND a.KODE_DOKUMEN_PABEAN IN ('30')
   
    ) a 
   )

   Insert into @rpt_kb_ekspor (data_source
    , NOMOR_AJU
    , NOMOR_DAFTAR
    , TANGGAL_DAFTAR
    , URAIAN
    , JUMLAH_SATUAN
    , KODE_SATUAN
    , CIF
    , KODE_VALUTA
    , NDPBM
    , CIF_RUPIAH
    , TANGGAL_REKAM
    , KETERANGAN
    , NOMOR_INVOICE
    , BRUTO
    , JUMLAH_KEMASAN
    , KODE_KEMASAN)
   SELECT 'WH' 
   , NOMOR_AJU
   , NOMOR_DAFTAR
   , TANGGAL_DAFTAR
   , URAIAN
   , JUMLAH_SATUAN
   , a.KODE_SATUAN
   , CIF
   , KODE_VALUTA
   , NDPBM
   , CIF_RUPIAH
   , TANGGAL_REKAM
   , KETERANGAN
   , b.NOMOR_DOKUMEN
   , BRUTO
   , JUMLAH_KEMASAN
   , KODE_KEMASAN
   from cte_hdr a
    Left Join ct_invoice b ON a.tableid = b.tableid AND a.id_sa_hdr = b.id_sa_hdr AND a.urut = b.urut
    Left Join ct_sumary_header c ON a.tableid = c.tableid AND a.id_sa_hdr = c.id_sa_hdr
   ORDER BY a.NOMOR_AJU
 end

if(@source = 'All' OR @source = 'AW')
 begin
  --set @source = 'AW'

  ;WITH
cte_hdr (
 urut,
 id_bc_out_hdr,
 NOMOR_AJU,
 NOMOR_DAFTAR,
 TANGGAL_DAFTAR,
 TANGGAL_KELUAR, 
 SERI_BARANG,
 KODE_BARANG,
 URAIAN,
 JUMLAH_SATUAN,
 KODE_SATUAN,
 CIF,
 POS_TARIF,
 TARIF_BM
) as (
SELECT
 ROW_NUMBER() OVER (PARTITION BY a.idpebhdr ORDER BY a.idpebhdr),
 a.idpebhdr AS id_bc_out_hdr,
 a.CAR,
 a.NODAFT,
 a.TGDAFT,
 a.TGDAFT AS doc_out_date,
 b.SERIBRG,
 b.KDBRG,
 COncat(b.URBRG1, ' ', b.URBRG2,' ',b.URBRG3) as URAIAN
 , b.JMSATUAN
 , b.JNSATUAN
 , b.DnilInv 
 , b.HS
 ,'10'
 FROM 
 AW.DB_PASI_20201106.dbo.tblPebDtl b
 INNER JOIN
 AW.DB_PASI_20201106.dbo.tblPebHdr a ON b.idpebhdr = a.idpebhdr
 where a.NODAFT is not null AND a.TGDAFT is not null
  AND (a.TGDAFT BETWEEN @start_date AND @end_date)
 
)
,
ct_sumary_detail (
 urut,
 id_bc_out_hdr,
 JUMLAH_KEMASAN,
 KODE_KEMASAN,
 CIF
) as (
 SELECT
 ROW_NUMBER() OVER (PARTITION BY a.idpebhdr ORDER BY a.idpebhdr),
 a.idpebhdr
 , sum(isnull(b.JMKOLI,0)) as KemasJm
    , b.JNKOLI as KODE_KEMASAN
 , sum(isnull(b.DNilInv,0)) as CIF
 FROM AW.DB_PASI_20201106.dbo.tblPebDtl b
 INNER JOIN
 AW.DB_PASI_20201106.dbo.tblPebHdr a ON b.idpebhdr = a.idpebhdr
 where a.NODAFT is not null AND a.TGDAFT is not null
 AND (a.TGDAFT BETWEEN @start_date AND @end_date)
 GROUP BY a.idpebhdr, b.JNKOLI
)
,
ct_sumary_header (
 urut,
 id_bc_out_hdr,
 BRUTO,
 CIF,
 KPBC,
 NDPBM,
 NAMA_PENERIMA,
 ALAMAT_PENERIMA,
 NAMA_ANGKUT,
 PELMUAT,
 KDVAL
) as (
 SELECT
 ROW_NUMBER() OVER (PARTITION BY a.idpebhdr ORDER BY a.idpebhdr),
 idpebhdr
 , BRUTO
 , NilInv
 , KDKTR
 , dbo.get_rate_valuta(a.TGDAFT,KDVAL)
 , NAMABELI
 , ALMTBELI
 , CARRIER
 , PELMUAT
 ,a.KDVAL
 FROM 
 AW.DB_PASI_20201106.dbo.tblPebHdr a
 where a.NODAFT is not null AND a.TGDAFT is not null
 AND (a.TGDAFT BETWEEN @start_date AND @end_date)
)
,
ct_invoice (
 urut,
 id_bc_out_hdr,
 NOMOR_DOKUMEN,
 TANGGAL_DOKUMEN,
 AMOUNT_INVOICE
) as (
 SELECT
 ROW_NUMBER() OVER (PARTITION BY a.idpebhdr ORDER BY a.idpebhdr),
 a.idpebhdr
 , b.NoDok
 , b.TgDok
 , a.NilInv

 FROM AW.DB_PASI_20201106.dbo.tblPebDok b
 LEFT OUTER JOIN
 AW.DB_PASI_20201106.dbo.tblPebHdr a ON b.idpebhdr = a.idpebhdr
 where a.NODAFT is not null AND a.TGDAFT is not NULL
    AND b.KdDok= '380'
 AND (a.TGDAFT BETWEEN @start_date AND @end_date)
)
,
ct_bl (
 urut,
 id_bc_out_hdr,
 NOMOR_DOKUMEN,
 TANGGAL_DOKUMEN
) as (
 SELECT
 ROW_NUMBER() OVER (PARTITION BY a.idpebhdr ORDER BY a.idpebhdr),
 a.idpebhdr
 , b.NoDok
 , b.TgDok
 
 FROM AW.DB_PASI_20201106.dbo.tblPebDok b
 LEFT OUTER JOIN
 AW.DB_PASI_20201106.dbo.tblPebHdr a ON b.idpebhdr = a.idpebhdr
 where a.NODAFT is not null AND a.TGDAFT is not NULL
    AND b.KdDok  IN ('705', '740')
 AND (a.TGDAFT BETWEEN @start_date AND @end_date)

),
ct_cont (
 urut,
 id_bc_out_hdr,
 NOMOR_KONTAINER,
 UKURAN_KONTAINER
) as (
 SELECT
 ROW_NUMBER() OVER (PARTITION BY a.idpebhdr ORDER BY a.idpebhdr),
 a.idpebhdr
 , b.NoCont
 , b.Size

 FROM AW.DB_PASI_20201106.dbo.tblPebCon b
 LEFT OUTER JOIN
 AW.DB_PASI_20201106.dbo.tblPebHdr a ON b.idpebhdr = a.idpebhdr
 where a.NODAFT is not null AND a.TGDAFT is not NULL
 AND (a.TGDAFT BETWEEN @start_date AND @end_date)
)

--Insert into @rpt_return (data_source
-- , NOMOR_AJU
-- , NOMOR_DAFTAR
-- , TANGGAL_DAFTAR
-- , TANGGAL_KELUAR
-- , URAIAN
-- , JUMLAH_SATUAN
-- , KODE_SATUAN
-- , CIF
-- , NOMOR_INVOICE
-- , BRUTO
-- , JUMLAH_KEMASAN
-- , KODE_KEMASAN
-- , PELABUHAN
-- , KPBC
-- , KODE_VALUTA
-- , NAMA_PENERIMA
-- , ALAMAT_PENERIMA
-- )



   Insert into @rpt_kb_ekspor (data_source
    , NOMOR_AJU
    , NOMOR_DAFTAR
    , TANGGAL_DAFTAR
    , URAIAN
    , JUMLAH_SATUAN
    , KODE_SATUAN
    , CIF
    , KODE_VALUTA
    , NDPBM
    , CIF_RUPIAH
    , TANGGAL_REKAM
    , KETERANGAN
    , NOMOR_INVOICE
    , BRUTO
    , JUMLAH_KEMASAN
    , KODE_KEMASAN)
     SELECT 'AW' 
     , a.NOMOR_AJU
     , a.NOMOR_DAFTAR
     , a.TANGGAL_DAFTAR
     , a.URAIAN
     , a.JUMLAH_SATUAN
     , a.KODE_SATUAN
     , b.CIF
     , c.KDVAL
     , dbo.get_rate_valuta(a.TANGGAL_DAFTAR,c.KDVAL)
     , b.CIF * dbo.get_rate_valuta(a.TANGGAL_DAFTAR,c.KDVAL)
     , a.TANGGAL_DAFTAR
     , ''
     , e.NOMOR_DOKUMEN
     , c.BRUTO
     , b.JUMLAH_KEMASAN
     , b.KODE_KEMASAN
     from cte_hdr a
     LEFT JOIN ct_sumary_detail b ON a.id_bc_out_hdr = b.id_bc_out_hdr AND a.urut = b.urut
     LEFT JOIN ct_sumary_header c ON a.id_bc_out_hdr = c.id_bc_out_hdr AND a.urut = c.urut
     LEFT JOIN ct_invoice d ON a.id_bc_out_hdr = d.id_bc_out_hdr AND a.urut = d.urut
     LEFT JOIN ct_bl e ON a.id_bc_out_hdr = e.id_bc_out_hdr AND a.urut = e.urut
     LEFT JOIN ct_cont f ON a.id_bc_out_hdr = f.id_bc_out_hdr AND a.urut = f.urut
     LEFT JOIN ct_sumary_header g ON a.id_bc_out_hdr = g.id_bc_out_hdr AND g.urut = 1
     ORDER BY a.NOMOR_AJU, a.SERI_BARANG

  
 end

 RETURN;

end; 

GO