Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @PARAMETER | xml | -1 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create procedure dbo.MG_MS_BC_OUT_IMPORT_FROM_MEMO
@PARAMETER xml=''
---XencX---with encryption
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @pemisah varchar(255)
declare @pemisah_replace varchar(255)
set @pemisah_replace='<br />'
--set @pemisah=CHAR(13) + CHAR(10) + '
'
set @pemisah = '
'
declare @resultX varchar(max)
declare @resultX2 varchar(max)
declare @XmlSts xml
declare @raw xml
declare @xStsnoheader varchar(2),
@xStsno varchar(2),
@xStsdes varchar(max)
set @xStsnoheader='07'
set @xStsno='00';
set @xStsdes='sukses'
BEGIN TRY
SET NOCOUNT ON
DECLARE @NOW datetime
set @NOW =GETDATE()
DECLARE
@data_by varchar(50)
,@ms_supplier_customer_id int
,@ms_memo_type_id int
,@KODE_DOKUMEN_PABEAN varchar(255)
SELECT
@data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50)
,@KODE_DOKUMEN_PABEAN=left(ltrim(rtrim(isnull(tempTable.item.value('bc_prefix[1]', 'varchar(305)'),''))),255)
,@ms_supplier_customer_id=isnull(tempTable.item.value('ms_supplier_customer_id[1]', 'int'),-1)
,@ms_memo_type_id=isnull(tempTable.item.value('memo_prefix_id[1]', 'int'),-1)
FROM @PARAMETER.nodes('param/field') tempTable(item)
if not (@KODE_DOKUMEN_PABEAN='25' or
@KODE_DOKUMEN_PABEAN='41' or
@KODE_DOKUMEN_PABEAN='27' or
@KODE_DOKUMEN_PABEAN='30' or
@KODE_DOKUMEN_PABEAN='PEMUSNAHAN'
)
begin
set @xStsno='02';
set @xStsdes='Gagal, Type BC ' + @KODE_DOKUMEN_PABEAN + 'tidak terdaftar ' ;
goto f
end
declare @KODE_TUJUAN_PENGIRIMAN varchar(255)=(
case when @ms_memo_type_id=0 then --0 SCRAP
case when @KODE_DOKUMEN_PABEAN='25' then
null
when @KODE_DOKUMEN_PABEAN='30' then
'1'
when @KODE_DOKUMEN_PABEAN='27' then
'1'
when @KODE_DOKUMEN_PABEAN='41' then
'5'
when @KODE_DOKUMEN_PABEAN='261' then
'4'
else
null
end
when @ms_memo_type_id=1 then --1 REPAIR
case when @KODE_DOKUMEN_PABEAN='25' then
null
when @KODE_DOKUMEN_PABEAN='30' then
'1'
when @KODE_DOKUMEN_PABEAN='27' then
'4'
when @KODE_DOKUMEN_PABEAN='41' then
'5'
when @KODE_DOKUMEN_PABEAN='261' then
'1'
else
null
end
when @ms_memo_type_id=2 then --2 SELLING
case when @KODE_DOKUMEN_PABEAN='25' then
null
when @KODE_DOKUMEN_PABEAN='30' then
'1'
when @KODE_DOKUMEN_PABEAN='27' then
'1'
when @KODE_DOKUMEN_PABEAN='41' then
'5'
when @KODE_DOKUMEN_PABEAN='261' then
'4'
else
'1'
end
end
)
declare @tmp_memo table(
ms_memo_header_id bigint primary key
)
insert into @tmp_memo
SELECT
isnull(tempTable.item.value('id[1]', 'bigint'),-1)
FROM @PARAMETER.nodes('param/field/lst') tempTable(item)
if (select count(*) from @tmp_memo)=0
begin
set @xStsno='02';
set @xStsdes='Gagal, Memo harus ada' ;
goto f
end
--- CHECK APAKAH 1 TYPE KURS
if (
select count(*) from (
SELECT valuta_code
FROM vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
GROUP BY valuta_code
) xgr)>1
begin
set @xStsno='02';
set @xStsdes='Gagal, Memo Harus 1 jenis valuta ' ;
goto f
end
declare @NDPBM decimal(18,0)
,@KODE_VALUTA varchar(255)
select top (1)
@KODE_VALUTA=valuta_code
from vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
if @KODE_VALUTA='IDR'
begin
set @NDPBM=1
end
else
begin
set @NDPBM=dbo.get_rate_valuta(@NOW, @KODE_VALUTA )
if @NDPBM=1
begin
declare @valuta_date date =@now
declare @date_part int= DATEPART(dw,@valuta_date)
if @date_part <4
begin
set @valuta_date= dateadd(DAY,-(3 + @date_part),@valuta_date)
end
else if @date_part >4
begin
set @valuta_date= dateadd(DAY,-(@date_part-4),@valuta_date)
end
if NOT EXISTS (SELECT * FROM dt_currencies_header INNER JOIN
dt_currencies ON dt_currencies_header.id = dt_currencies.headerid INNER JOIN
dt_currencies_master ON dt_currencies.currenciesid = dt_currencies_master.id
WHERE (dt_currencies_master.currenciescode = 'USD' and dt_currencies_header.startdate =@valuta_date)
)
begin
set @xStsno='02';
set @xStsdes='Gagal, Nilai KURS ' + @KODE_VALUTA+' untuk tanggal ' + dbo.date_to_str(@valuta_date) + ' sampai tanggal ' + dbo.date_to_str(dateadd(dd,6,@valuta_date )) + ' belum ada ' ;
goto f
end
end
end
--- CHECK STATUS MEMO
SET @resultX= (
SELECT
'MEMO: '+ doc_no + '(' + dbo.date_to_str(doc_date) + ')'
+', STATUS:' + ms_memo_header_status_name +
+ @pemisah
AS [text()]
FROM
vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
AND (used = 1) AND not((ms_memo_header_status_id = 1))
order by doc_date,doc_no
For XML PATH (''))
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN
SET @xStsno='17';
SET @xStsdes='Gagal , Ada Memo yang tidak dalam Status APPROVE :' + @pemisah+ @pemisah+ @resultX + @pemisah+ @pemisah+ ' Periksa kembali data Memo ' ;
GOto f
end
--- CHECK STATUS MEMO
SET @resultX= (
SELECT
'MEMO: '+ doc_no + '(' + dbo.date_to_str(doc_date) + ')'
+', Terpakai di' + BC_TYPE +
+'CAR: ' + isnull(CAR,'') +
+'NO: ' + isnull(BC_NO,'') +
+'DATE: ' + dbo.date_to_str3(BC_DATE )
+ @pemisah
AS [text()]
FROM
vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
AND (used = 1)
order by doc_date,doc_no
For XML PATH (''))
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN
SET @xStsno='17';
SET @xStsdes='Gagal , Ada Memo yang sudah terpakai :' + @pemisah+ @pemisah+ @resultX + @pemisah+ @pemisah+ ' Periksa kembali data Memo ' ;
GOto f
end
declare
@check_ms_supplier_customer_id int
, @check_id_type int
, @check_id_nomor varchar(255)
, @check_supplier_customer_code varchar(255)
, @check_supplier_customer_name varchar(255)
, @check_supplier_customer_address varchar(255)
, @check_supplier_customer_country varchar(255)
, @check_api_type_id int
, @check_api_no varchar(255)
, @check_skep_no varchar(255)
, @check_skep_date date
SELECT top(1)
@check_ms_supplier_customer_id=ms_supplier_customer_id
, @check_id_type=id_type
, @check_id_nomor=id_nomor
, @check_supplier_customer_code=supplier_customer_code
, @check_supplier_customer_name=supplier_customer_name
, @check_supplier_customer_address=supplier_customer_address
, @check_supplier_customer_country=supplier_customer_country
, @check_api_type_id=api_type_id
, @check_api_no=api_no
, @check_skep_no=skep_no
, @check_skep_date=skep_date
FROM dt_ms_supplier_customer
WHERE (ms_supplier_customer_id = @ms_supplier_customer_id)
declare @ID_HEADER bigint
,@dt_ms_tpb_out_header_h_status_id bigint
declare @dt_ms_tpb_out_barang table (
ID bigint primary key
,ID_HEADER bigint
,SERI_BARANG int
,ms_memo_detail_id bigint
,nomor_hs varchar(50)
,tarif_bm decimal(18, 2)
,tarif_cukai decimal(18, 2)
,tarif_pph decimal(18, 2)
,tarif_ppn decimal(18, 2)
,tarif_ppnbm decimal(18, 2)
,total_harga_rupiah decimal(38, 4)
,total_harga_usd decimal(38, 4)
,total_harga_bm decimal(38, 4)
,total_harga_penyerahan_plus_bm decimal(38, 4)
)
declare @dt_ms_tpb_out_bahan_baku table (
id_bahan_baku bigint primary key
,ID_BARANG bigint
,SERI_BARANG int
,ID_HEADER bigint
,nomor_hs varchar(50)
,tarif_bm decimal(18, 2)
,tarif_cukai decimal(18, 2)
,tarif_pph decimal(18, 2)
,tarif_ppn decimal(18, 2)
,tarif_ppnbm decimal(18, 2)
,total_harga_rupiah decimal(38, 4)
,total_harga_usd decimal(38, 4)
,tarif_bm_barang decimal(18, 2)
,KODE_ASAL int
,total_harga_bm decimal(38, 4)
,total_harga_penyerahan_plus_bm decimal(38, 4)
, KODE_JENIS_DOK_ASAL varchar(255)
, NOMOR_DAFTAR_DOK_ASAL varchar(255)
, TANGGAL_DAFTAR_DOK_ASAL date
)
BEGIN TRANSACTION;
INSERT INTO dt_ms_tpb_out_header
( ID_HEADER_ORI
, KODE_DOKUMEN_PABEAN
, KODE_JENIS_TPB
, KODE_TUJUAN_TPB
, KODE_TUJUAN_PENGIRIMAN
, NOMOR_AJU
, ID_PENERIMA_BARANG
, ALAMAT_PENERIMA_BARANG
, KODE_ID_PENERIMA_BARANG
, NAMA_PENERIMA_BARANG
, NAMA_PENGANGKUT
, NOMOR_IJIN_TPB_PENERIMA
, NOMOR_POLISI
, KODE_VALUTA
, NDPBM
, CIF
, HARGA_PENYERAHAN
, JUMLAH_BARANG
, JUMLAH_KONTAINER
, JUMLAH_KEMASAN
, NETTO
, BRUTO
, VOLUME
, mapping_supplier_id
, mapping_supplier_date
, mapping_supplier_by
, mode_source
, create_date
, create_by
, modify_date
, modify_by
,type_source_id)
VALUES (
- 1
, @KODE_DOKUMEN_PABEAN --KODE_DOKUMEN_PABEAN
, 1 --KODE_JENIS_TPB
, case when @KODE_DOKUMEN_PABEAN='27' then 1 else NULL end --KODE_TUJUAN_TPB
, @KODE_TUJUAN_PENGIRIMAN --KODE_TUJUAN_PENGIRIMAN
, dbo.draft_car_bc_in(case when @ms_memo_type_id =0 then 'SCRAP' when @ms_memo_type_id =1 then 'REPAIR' when @ms_memo_type_id =2 then 'SELLING' else 'MEMO' end + ' BC ' + @KODE_DOKUMEN_PABEAN ,@KODE_DOKUMEN_PABEAN) --NOMOR_AJU
, @check_id_nomor --ID_PENERIMA_BARANG
, @check_supplier_customer_address --ALAMAT_PENERIMA_BARANG
, @check_id_type --KODE_ID_PENERIMA_BARANG
, @check_supplier_customer_name --NAMA_PENERIMA_BARANG
, '-' --NAMA_PENGANGKUT
, @check_skep_no --NOMOR_IJIN_TPB_PENERIMA
, '-' --NOMOR_POLISI
, @KODE_VALUTA --KODE_VALUTA
, @ndpbm --NDPBM
, 0 --CIF
, 0 --HARGA_PENYERAHAN
, 0 --JUMLAH_BARANG
, 0 --JUMLAH_KONTAINER
, 0 --JUMLAH_KEMASAN
, 0 --NETTO
, 0 --BRUTO
, 0 --VOLUME
, @check_ms_supplier_customer_id
, @now
, @data_by
, 3
, @now
, @data_by
, @now
, @data_by
, @ms_memo_type_id)
set @ID_HEADER=SCOPE_IDENTITY();
UPDATE dt_ms_memo_header
SET source_out_id = @ID_HEADER, mode_source_out = 0
WHERE exists(
select * from @tmp_memo as xfilter
where dt_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
INSERT INTO dt_ms_tpb_out_header_h_status
(ID_HEADER, status_tpb_out_id, status_info, create_date, create_by)
VALUES (@ID_HEADER, 0, '',@now,@data_by)
set @dt_ms_tpb_out_header_h_status_id=SCOPE_IDENTITY();
update dt_ms_tpb_out_header
set dt_ms_tpb_out_header_h_status_id=@dt_ms_tpb_out_header_h_status_id
where ID_HEADER=@ID_HEADER
--INSERT INTO dt_ms_tpb_out_dokumen
--(FLAG_URL_DOKUMEN
-- , KODE_JENIS_DOKUMEN
-- , NOMOR_DOKUMEN
-- , SERI_DOKUMEN
-- , TANGGAL_DOKUMEN
-- , TIPE_DOKUMEN
-- , URL_DOKUMEN
-- , ID_HEADER
-- , ID_HEADER_ORI
-- , create_date
-- , create_by
-- , modify_date
-- , modify_by
-- , mode_source
-- , id_source
--)
-- SELECT
-- ''
-- , '380'
-- , invoice_no
-- ,ROW_NUMBER() OVER(ORDER BY invoice_date,invoice_no) as rn
-- , invoice_date
-- ,'02'
-- ,''
-- ,@ID_HEADER
-- , - 1
-- ,@now
-- ,@data_by
-- ,@now
-- ,@data_by
-- ,1
-- ,ms_memo_header_id
-- FROM vw_ms_memo_header
-- WHERE exists(
-- select * from @tmp_memo as xfilter
-- where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
-- )
-- union all
-- SELECT
-- ''
-- , '217'
-- , invoice_no
-- ,ROW_NUMBER() OVER(ORDER BY invoice_date,invoice_no) as rn
-- , invoice_date
-- ,'02'
-- ,''
-- ,@ID_HEADER
-- , - 1
-- ,@now
-- ,@data_by
-- ,@now
-- ,@data_by
-- ,1
-- ,ms_memo_header_id
-- FROM vw_ms_memo_header
-- WHERE exists(
-- select * from @tmp_memo as xfilter
-- where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
-- )
MERGE INTO dt_ms_tpb_out_barang USING (
select vw_ms_memo_detail.total_price --CIF
, vw_ms_memo_detail.total_price --FOB
, vw_ms_memo_detail.total_price --HARGA_INVOICE
, vw_ms_memo_detail.total_price* @NDPBM --CIF_RUPIAH
, vw_ms_memo_detail.total_price* @NDPBM --HARGA_PENYERAHAN
, vw_ms_memo_detail.total_price/vw_ms_memo_detail.quantity --HARGA_SATUAN
, vw_ms_memo_detail.quantity --JUMLAH_SATUAN
, dt_ms_item.part_no --KODE_BARANG
, vw_ms_memo_detail.package_qty --JUMLAH_KEMASAN
, vw_ms_memo_detail.package_code --KODE_KEMASAN
, dt_ms_item.uom --KODE_SATUAN
, case when @ms_memo_type_id =0 or @ms_memo_type_id =1 then 2 else 1 end --KONDISI_BARANG
, 4--case when dt_ms_item.ms_type_id=3 then 5 else end --KODE_GUNA
, case when dt_ms_item.ms_type_id=3 then 5 else 3 end --KATEGORI_BARANG
, '-' --MERK
, '-' --SPESIFIKASI_LAIN
, dt_ms_item.ms_item_custom_no --TIPE
, '-' --UKURAN
, vw_ms_memo_detail.netto_qty --NETTO
, ROW_NUMBER() OVER(ORDER BY vw_ms_memo_detail.ms_memo_detail_id ) as SERI_BARANG
, dt_ms_item_custom.ms_item_custom_name --URAIAN
, '02' --KODE_STATUS
, @ID_HEADER
, dt_ms_item_custom_h_tarif.nomor_hs
, vw_ms_memo_detail.negara_asal --KODE_NEGARA_ASAL
, 3
, vw_ms_memo_detail.ms_memo_detail_id
, ''
, @now
, @data_by
, @now
, @data_by
, vw_ms_memo_detail.ms_item_id--mapping_ms_item_id
, vw_ms_memo_detail.quantity--qty_pakai
, vw_ms_memo_detail.modify_date--mapping_date
, vw_ms_memo_detail.modify_by_id--mapping_by
, dt_ms_item_custom_h_tarif.tarif_bm
, dt_ms_item_custom_h_tarif.tarif_cukai
, dt_ms_item_custom_h_tarif.tarif_pph
, dt_ms_item_custom_h_tarif.tarif_ppn
, dt_ms_item_custom_h_tarif.tarif_ppnbm
,vw_ms_memo_detail.total_price* @NDPBM as total_harga_rupiah
FROM vw_ms_memo_detail INNER JOIN
dt_ms_item ON vw_ms_memo_detail.ms_item_id = dt_ms_item.ms_item_id INNER JOIN
dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no INNER JOIN
dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id
WHERE exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_detail.ms_memo_header_id =xfilter.ms_memo_header_id
)
) as source( CIF
, FOB
, HARGA_INVOICE
, CIF_RUPIAH
, HARGA_PENYERAHAN
, HARGA_SATUAN
, JUMLAH_SATUAN
, KODE_BARANG
, JUMLAH_KEMASAN
, KODE_KEMASAN
, KODE_SATUAN
, KONDISI_BARANG
, KODE_GUNA
, KATEGORI_BARANG
, MERK
, SPESIFIKASI_LAIN
, TIPE
, UKURAN
, NETTO
, SERI_BARANG
, URAIAN
, KODE_STATUS
, ID_HEADER
, POS_TARIF
, KODE_NEGARA_ASAL
, mode_source
, ms_memo_detail_id
, id_source_xml
, create_date
, create_by
, modify_date
, modify_by
, mapping_ms_item_id
, qty_pakai
, mapping_date
, mapping_by
, tarif_bm
, tarif_cukai
, tarif_pph
, tarif_ppn
, tarif_ppnbm
,total_harga_rupiah
) ON 1 = 0
WHEN NOT MATCHED THEN
INSERT ( CIF
, FOB
, HARGA_INVOICE
, CIF_RUPIAH
, HARGA_PENYERAHAN
, HARGA_SATUAN
, JUMLAH_SATUAN
, KODE_BARANG
, JUMLAH_KEMASAN
, KODE_KEMASAN
, KODE_SATUAN
, KONDISI_BARANG
, KODE_GUNA
, KATEGORI_BARANG
, MERK
, SPESIFIKASI_LAIN
, TIPE
, UKURAN
, NETTO
, SERI_BARANG
, URAIAN
, KODE_STATUS
, ID_HEADER
, POS_TARIF
, KODE_NEGARA_ASAL
, mode_source
, id_source
, id_source_xml
, create_date
, create_by
, modify_date
, modify_by
, mapping_ms_item_id
, qty_pakai
, mapping_date
, mapping_by)
VALUES (source.CIF
,source.FOB
,source.HARGA_INVOICE
,source.CIF_RUPIAH
,source.HARGA_PENYERAHAN
,source.HARGA_SATUAN
,source.JUMLAH_SATUAN
,source.KODE_BARANG
,source.JUMLAH_KEMASAN
,source.KODE_KEMASAN
,source.KODE_SATUAN
,source.KONDISI_BARANG
,source.KODE_GUNA
,source.KATEGORI_BARANG
,source.MERK
,source.SPESIFIKASI_LAIN
,source.TIPE
,source.UKURAN
,source.NETTO
,source.SERI_BARANG
,source.URAIAN
,source.KODE_STATUS
,source.ID_HEADER
,source.POS_TARIF
,source.KODE_NEGARA_ASAL
,source.mode_source
,source.ms_memo_detail_id
,source.id_source_xml
,source.create_date
,source.create_by
,source.modify_date
,source.modify_by
,source.mapping_ms_item_id
,source.qty_pakai
,source.mapping_date
,source.mapping_by
)
OUTPUT
inserted.ID
,source.ID_HEADER
, inserted.SERI_BARANG
,source.ms_memo_detail_id
,source.POS_TARIF
,source.tarif_bm
,source.tarif_cukai
,source.tarif_pph
,source.tarif_ppn
,source.tarif_ppnbm
,source.total_harga_rupiah
,case when source.tarif_bm=0 then 0 else (source.total_harga_rupiah * source.tarif_bm)/100 end
,(case when source.tarif_bm=0 then 0 else (source.total_harga_rupiah * source.tarif_bm)/100 end )+ source.total_harga_rupiah --total_harga_penyerahan_plus_bm
INTO @dt_ms_tpb_out_barang (
ID
,ID_HEADER
,SERI_BARANG
,ms_memo_detail_id
,nomor_hs
,tarif_bm
,tarif_cukai
,tarif_pph
,tarif_ppn
,tarif_ppnbm
,total_harga_rupiah
,total_harga_bm
,total_harga_penyerahan_plus_bm
);
MERGE INTO dt_ms_tpb_out_bahan_baku USING (
SELECT
ROW_NUMBER() OVER(PARTITION BY data_memo.ID ORDER BY vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL ) as SERI_BAHAN_BAKU
,data_memo.ID, data_memo.ID_HEADER,data_memo.SERI_BARANG,data_memo.tarif_bm, vw_ms_memo_detail_suplly_ori_document.ms_memo_detail_id, vw_ms_memo_detail_suplly_ori_document.ms_in_item_detail_id, vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL,
vw_ms_memo_detail_suplly_ori_document.NOMOR_DAFTAR_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.TANGGAL_DAFTAR_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.KODE_KANTOR,
vw_ms_memo_detail_suplly_ori_document.NOMOR_AJU_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.SERI_BARANG_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.KODE_VALUTA,
vw_ms_memo_detail_suplly_ori_document.NDPBM, vw_ms_memo_detail_suplly_ori_document.TOTAL_HARGA_RUPIAH, vw_ms_memo_detail_suplly_ori_document.TOTAL_HARGA_USD,
vw_ms_memo_detail_suplly_ori_document.JUMLAH, vw_ms_memo_detail_suplly_ori_document.ms_type_id, vw_ms_memo_detail_suplly_ori_document.ms_item_custom_no,
vw_ms_memo_detail_suplly_ori_document.ms_item_base_no, vw_ms_memo_detail_suplly_ori_document.part_no, vw_ms_memo_detail_suplly_ori_document.part_name, vw_ms_memo_detail_suplly_ori_document.uom,
vw_ms_memo_detail_suplly_ori_document.ms_item_id, vw_ms_memo_detail_suplly_ori_document.ms_item_base_name, vw_ms_memo_detail_suplly_ori_document.ms_item_custom_name,
vw_ms_memo_detail_suplly_ori_document.nomor_hs, vw_ms_memo_detail_suplly_ori_document.tarif_bm, vw_ms_memo_detail_suplly_ori_document.tarif_cukai, vw_ms_memo_detail_suplly_ori_document.tarif_pph,
vw_ms_memo_detail_suplly_ori_document.tarif_ppn, vw_ms_memo_detail_suplly_ori_document.tarif_ppnbm
FROM vw_ms_memo_detail_suplly_ori_document INNER JOIN
@dt_ms_tpb_out_barang AS data_memo ON vw_ms_memo_detail_suplly_ori_document.ms_memo_detail_id = data_memo.ms_memo_detail_id
where ( ( @KODE_DOKUMEN_PABEAN='41' and(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27'))
or ( @KODE_DOKUMEN_PABEAN='25' and(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='23'))
or ( @KODE_DOKUMEN_PABEAN='27' and(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='23'))
or ( @KODE_DOKUMEN_PABEAN='261' and not (vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27'or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='23'))
) and not(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='262')
) as source(SERI_BAHAN_BAKU, ID_BARANG,ID_HEADER,SERI_BARANG,tarif_bm_barang,
ms_memo_detail_id, ms_in_item_detail_id, KODE_JENIS_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL, TANGGAL_DAFTAR_DOK_ASAL, KODE_KANTOR, NOMOR_AJU_DOK_ASAL, SERI_BARANG_DOK_ASAL, KODE_VALUTA,
NDPBM, TOTAL_HARGA_RUPIAH, TOTAL_HARGA_USD, JUMLAH, ms_type_id, ms_item_custom_no, ms_item_base_no, part_no, part_name, uom, ms_item_id, ms_item_base_name, ms_item_custom_name, nomor_hs,
tarif_bm, tarif_cukai, tarif_pph, tarif_ppn, tarif_ppnbm
) ON 1 = 0
WHEN NOT MATCHED THEN
INSERT ( mode_source
, id_source
, KODE_STATUS
, KODE_ASAL_BAHAN_BAKU
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
, KODE_KANTOR
, NOMOR_AJU_DOK_ASAL
, SERI_BARANG_DOK_ASAL
, KODE_BARANG
, POS_TARIF
, URAIAN
, JUMLAH_SATUAN
, JENIS_SATUAN
, HARGA_PENYERAHAN
, HARGA_PEROLEHAN
, CIF
, NDPBM
, CIF_RUPIAH
, SERI_BAHAN_BAKU
, SERI_BARANG
, ID_BARANG
, ID_HEADER
, create_date
, create_by
, modify_date
, modify_by)
VALUES ( 0
, source.ms_in_item_detail_id
, case
when isnull(source.KODE_KANTOR ,'') =''then '02'
when (isnull(source.nomor_hs ,'') =''and @KODE_DOKUMEN_PABEAN='27' and source.KODE_JENIS_DOK_ASAL<>'40' ) then '02'
else
'03'
end
, case when @KODE_DOKUMEN_PABEAN='40' then
'1'
else
case when source.KODE_JENIS_DOK_ASAL='23' then '0'
when source.KODE_JENIS_DOK_ASAL='27' then '0'
else
'0'
end
end ---KODE_ASAL_BAHAN_BAKU
, source.KODE_JENIS_DOK_ASAL
, source.NOMOR_DAFTAR_DOK_ASAL
, source.TANGGAL_DAFTAR_DOK_ASAL
, source.KODE_KANTOR --'040300'
, source.NOMOR_AJU_DOK_ASAL
, source.SERI_BARANG_DOK_ASAL
, source.PART_NO
, source.nomor_hs
, source.ms_item_custom_name
, source.JUMLAH
, source.UOM
, case when @KODE_DOKUMEN_PABEAN='25' and (source.KODE_JENIS_DOK_ASAL='23' or source.KODE_JENIS_DOK_ASAL='27') then 0 else TOTAL_HARGA_RUPIAH end --HARGA_PENYERAHAN
, 0--source.HARGA_PEROLEHAN
, case when(@KODE_DOKUMEN_PABEAN='25' or @KODE_DOKUMEN_PABEAN='27') then
case when (source.KODE_JENIS_DOK_ASAL='23' or source.KODE_JENIS_DOK_ASAL='27') then TOTAL_HARGA_USD else 0 end
when(@KODE_DOKUMEN_PABEAN='41') then
TOTAL_HARGA_RUPIAH
else 0
end ---CIF
, source.NDPBM
, case when @KODE_DOKUMEN_PABEAN='25' and (source.KODE_JENIS_DOK_ASAL='23' or source.KODE_JENIS_DOK_ASAL='27') then TOTAL_HARGA_RUPIAH else 0 end --CIF_RUPIAH
, source.SERI_BAHAN_BAKU
, source.SERI_BARANG
, source.ID_BARANG
, source.ID_HEADER
, @now
, @data_by
, @now
, @data_by
)
OUTPUT
inserted.ID
,source.ID_BARANG
,source.SERI_BARANG
,source.ID_HEADER
,inserted.POS_TARIF
,source.tarif_bm
,source.tarif_cukai
,source.tarif_pph
,source.tarif_ppn
,source.tarif_ppnbm
,source.total_harga_rupiah
,source.total_harga_usd
,source.tarif_bm_barang
,inserted.KODE_ASAL_BAHAN_BAKU
,case when @KODE_DOKUMEN_PABEAN ='25' then
case when source.tarif_bm_barang>source.tarif_bm then
(source.total_harga_rupiah * source.tarif_bm_barang)/100
else
(source.total_harga_rupiah * source.tarif_bm)/100
end
else
(source.total_harga_rupiah * source.tarif_bm)/100
end --total_harga_bm
,(case when @KODE_DOKUMEN_PABEAN ='25' then
case when source.tarif_bm_barang>source.tarif_bm then
(source.total_harga_rupiah * source.tarif_bm_barang)/100
else
(source.total_harga_rupiah * source.tarif_bm)/100
end
else
(source.total_harga_rupiah * source.tarif_bm)/100
end) + source.total_harga_rupiah --total_harga_penyerahan_plus_bm
, inserted.KODE_JENIS_DOK_ASAL
, inserted.NOMOR_DAFTAR_DOK_ASAL
, inserted.TANGGAL_DAFTAR_DOK_ASAL
INTO @dt_ms_tpb_out_bahan_baku (
id_bahan_baku
,ID_BARANG
,SERI_BARANG
,ID_HEADER
,nomor_hs
,tarif_bm
,tarif_cukai
,tarif_pph
,tarif_ppn
,tarif_ppnbm
,total_harga_rupiah
,total_harga_usd
,tarif_bm_barang
,KODE_ASAL
,total_harga_bm
,total_harga_penyerahan_plus_bm
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
);
----MASUKAN TARIF SEMUA YANG LOKAL
INSERT INTO dt_ms_tpb_out_bahan_baku_tarif
(JENIS_TARIF
, KODE_ASAL_BAHAN_BAKU
, KODE_FASILITAS
, KODE_SATUAN
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BAHAN_BAKU
, ID_BARANG
, ID_HEADER
)
select
case when JenisTarif='tarif_bm' then 'BM'
when JenisTarif='tarif_cukai' then 'CUKAI'
when JenisTarif='tarif_pph' then 'PPH'
when JenisTarif='tarif_ppn' then 'PPN'
when JenisTarif='tarif_ppnbm' then 'PPNBM'
else'' end as JENIS_TARIF --- JENIS_TARIF
,KODE_ASAL ---KODE_ASAL_BAHAN_BAKU
,null ---KODE_FASILITAS
,null --KODE_SATUAN
,(total_harga_rupiah * isnull(nullif(ValueTarif,0),10)) /100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,ValueTarif --TARIF
,100 --TARIF_FASILITAS
,id_bahan_baku --ID_BAHAN_BAKU
,ID_BARANG --ID_BARANG
,ID_HEADER --ID_HEADER
from @dt_ms_tpb_out_bahan_baku
unpivot
(
ValueTarif
for JenisTarif in (tarif_ppn)
) unpiv
where KODE_ASAL=1
;
-- INTO @dt_ms_tpb_out_bahan_baku (
--id_bahan_baku
--,ID_BARANG
--,SERI_BARANG
--,ID_HEADER
--,nomor_hs
--,tarif_bm
--,tarif_cukai
--,tarif_pph
--,tarif_ppn
--,tarif_ppnbm
--,total_harga_rupiah
--,total_harga_usd
--,tarif_bm_barang
--,KODE_ASAL
--,total_harga_bm
--,total_harga_penyerahan_plus_bm
--);
----MASUKAN TARIF SEMUA YANG IMPORT
INSERT INTO dt_ms_tpb_out_bahan_baku_tarif
(JENIS_TARIF
, KODE_ASAL_BAHAN_BAKU
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BAHAN_BAKU
, ID_BARANG
, ID_HEADER
)
select
case when JenisTarif='tarif_bm' then 'BM'
when JenisTarif='tarif_cukai' then 'CUKAI'
when JenisTarif='tarif_pph' then 'PPH'
when JenisTarif='tarif_ppn' then 'PPN'
when JenisTarif='tarif_ppnbm' then 'PPNBM'
else'' end as JENIS_TARIF --- JENIS_TARIF
,KODE_ASAL ---KODE_ASAL_BAHAN_BAKU
,case when @KODE_DOKUMEN_PABEAN ='25' then 0 else 2 end ---KODE_FASILITAS
,null --KODE_SATUAN
,case when JenisTarif='tarif_bm' then 1 else null end --KODE_TARIF
,case when JenisTarif='tarif_bm' then total_harga_bm else (total_harga_penyerahan_plus_bm * isnull(nullif(ValueTarif,0),10)) /100 end --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,ValueTarif --TARIF
,100 --TARIF_FASILITAS
,id_bahan_baku --ID_BAHAN_BAKU
,ID_BARANG --ID_BARANG
,ID_HEADER --ID_HEADER
from @dt_ms_tpb_out_bahan_baku
unpivot
(
ValueTarif
for JenisTarif in (tarif_bm ,tarif_pph,tarif_ppn,tarif_ppnbm)
) unpiv
where KODE_ASAL=0 and ValueTarif>0
;
if @KODE_DOKUMEN_PABEAN='41'
begin
INSERT INTO dt_ms_tpb_out_dokumen
(FLAG_URL_DOKUMEN
, KODE_JENIS_DOKUMEN
, NOMOR_DOKUMEN
, SERI_DOKUMEN
, TANGGAL_DOKUMEN
, TIPE_DOKUMEN
, URL_DOKUMEN
, ID_HEADER
, ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
, id_source
)
SELECT
'' AS Expr1
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
,ROW_NUMBER() OVER(ORDER BY KODE_JENIS_DOK_ASAL,TANGGAL_DAFTAR_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL) as rn
, TANGGAL_DAFTAR_DOK_ASAL
,'01'
,''
,@ID_HEADER
, - 1
,@now
,@data_by
,@now
,@data_by
,1
, -1
FROM @dt_ms_tpb_out_bahan_baku
WHERE (ID_HEADER = @ID_HEADER) AND (KODE_JENIS_DOK_ASAL= '40')
GROUP BY ID_HEADER, TANGGAL_DAFTAR_DOK_ASAL, KODE_JENIS_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL
end
else if @KODE_DOKUMEN_PABEAN='27'
begin
INSERT INTO dt_ms_tpb_out_dokumen
(FLAG_URL_DOKUMEN
, KODE_JENIS_DOKUMEN
, NOMOR_DOKUMEN
, SERI_DOKUMEN
, TANGGAL_DOKUMEN
, TIPE_DOKUMEN
, URL_DOKUMEN
, ID_HEADER
, ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
, id_source
)
SELECT
'' AS Expr1
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
,ROW_NUMBER() OVER(ORDER BY KODE_JENIS_DOK_ASAL,TANGGAL_DAFTAR_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL) as rn
, TANGGAL_DAFTAR_DOK_ASAL
,'01'
,''
,@ID_HEADER
, - 1
,@now
,@data_by
,@now
,@data_by
,1
, -1
FROM @dt_ms_tpb_out_bahan_baku
WHERE (ID_HEADER = @ID_HEADER) AND (KODE_JENIS_DOK_ASAL= '271' or KODE_JENIS_DOK_ASAL= '27' or KODE_JENIS_DOK_ASAL= '23' )
GROUP BY ID_HEADER, TANGGAL_DAFTAR_DOK_ASAL, KODE_JENIS_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL
end
MERGE dt_ms_tpb_out_dokumen AS target
USING (
select id ,ROW_NUMBER() OVER(ORDER BY orderx ,SERI_DOKUMEN) as rn from (
SELECT id,
SERI_DOKUMEN,
case when KODE_JENIS_DOKUMEN='380' then 1
when KODE_JENIS_DOKUMEN='217' then 2
when KODE_JENIS_DOKUMEN='40' then 3
when KODE_JENIS_DOKUMEN='27' then 4
when KODE_JENIS_DOKUMEN='271' then 4
when KODE_JENIS_DOKUMEN='23' then 5
else 6
end as orderx
from dt_ms_tpb_out_dokumen
where ID_HEADER=@ID_HEADER
)xdata
) AS source (id, rn)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET SERI_DOKUMEN = source.rn ;
if @KODE_DOKUMEN_PABEAN ='25'
begin
declare @cur_item_ID bigint
,@cur_ms_memo_detail_id bigint
,@cur_BM decimal(38,2)
,@cur_PPNBM decimal(38,2)
,@cur_PPH decimal(38,2)
,@cur_PPN decimal(38,2)
,@cur_ID_BARANG bigint
,@cur_SERI_BARANG int
,@cur_price_total decimal(24, 4)
INSERT INTO dt_ms_tpb_out_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
select
case when JenisTarif='tarif_bm' then 'BM'
when JenisTarif='tarif_cukai' then 'CUKAI'
when JenisTarif='tarif_pph' then 'PPH'
when JenisTarif='tarif_ppn' then 'PPN'
when JenisTarif='tarif_ppnbm' then 'PPNBM'
else'' end as JENIS_TARIF --- JENIS_TARIF
,case when @KODE_DOKUMEN_PABEAN ='25' then 0 else 2 end ---KODE_FASILITAS
,null --KODE_SATUAN
,case when JenisTarif='tarif_bm' then 1 else null end --KODE_TARIF
,case when JenisTarif='tarif_bm' then total_harga_bm else (total_harga_penyerahan_plus_bm * isnull(nullif(ValueTarif,0),10)) /100 end --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,ValueTarif --TARIF
,100 --TARIF_FASILITAS
,ID --ID_BARANG
,ID_HEADER --ID_HEADER
from @dt_ms_tpb_out_barang
unpivot
(
ValueTarif
for JenisTarif in (tarif_bm ,tarif_pph,tarif_ppn,tarif_ppnbm)
) unpiv
where ValueTarif>0
;
end
update dt_ms_tpb_out_barang
set CIF=case when @KODE_DOKUMEN_PABEAN ='25' then sum_bahan_baku.CIF else dt_ms_tpb_out_barang.CIF end
,CIF_RUPIAH=case when @KODE_DOKUMEN_PABEAN ='25' then sum_bahan_baku.CIF_RUPIAH else dt_ms_tpb_out_barang.CIF_RUPIAH end
,JUMLAH_BAHAN_BAKU=sum_bahan_baku.JUMLAH_BAHAN_BAKU
from dt_ms_tpb_out_barang INNER JOIN
(
SELECT ID_BARANG
, SUM(CIF) AS CIF
, SUM(CIF_RUPIAH) AS CIF_RUPIAH
, COUNT(*) as JUMLAH_BAHAN_BAKU
FROM dt_ms_tpb_out_bahan_baku
GROUP BY ID_BARANG ) AS sum_bahan_baku ON dt_ms_tpb_out_barang.ID = sum_bahan_baku.ID_BARANG
where dt_ms_tpb_out_barang.ID_HEADER =@ID_HEADER
UPDATE dt_ms_tpb_out_header
SET CIF=sum_detail.CIF
,FOB=sum_detail.FOB
,HARGA_INVOICE =sum_detail.HARGA_INVOICE
,CIF_RUPIAH=sum_detail.CIF_RUPIAH
,HARGA_PENYERAHAN=sum_detail.HARGA_PENYERAHAN
,NETTO=sum_detail.NETTO
,JUMLAH_BARANG=sum_detail.JUMLAH_BARANG
FROM dt_ms_tpb_out_header INNER JOIN
(
SELECT ID_HEADER
, SUM(FOB) AS FOB
, SUM(CIF) AS CIF
, SUM(HARGA_INVOICE) AS HARGA_INVOICE
, SUM(CIF_RUPIAH) AS CIF_RUPIAH
, SUM(HARGA_PENYERAHAN) AS HARGA_PENYERAHAN
, SUM(NETTO) AS NETTO
, COUNT(*) as JUMLAH_BARANG
FROM dt_ms_tpb_out_barang
GROUP BY ID_HEADER ) AS sum_detail ON dt_ms_tpb_out_header.ID_HEADER = sum_detail.ID_HEADER
where dt_ms_tpb_out_header.ID_HEADER =@ID_HEADER
--- OLAH INSERT BAHAN BAKU DARI SELAIN FINISH GOOD
--- CALCULASI ASAL BARANG
--@dt_ms_tpb_out_barang
COMMIT TRANSACTION;
f:
set nocount off
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK TRAN;
set @xStsno='88'
set @xStsdes= ERROR_MESSAGE()
END CATCH
g:
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
select CAST( (select @XmlSts,isnull(@raw ,'') for xml path ('result')
) as XML) as dt
GO
GO
create procedure dbo.MG_MS_BC_OUT_IMPORT_FROM_MEMO
@PARAMETER xml=''
---XencX---with encryption
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @pemisah varchar(255)
declare @pemisah_replace varchar(255)
set @pemisah_replace='<br />'
--set @pemisah=CHAR(13) + CHAR(10) + '
'
set @pemisah = '
'
declare @resultX varchar(max)
declare @resultX2 varchar(max)
declare @XmlSts xml
declare @raw xml
declare @xStsnoheader varchar(2),
@xStsno varchar(2),
@xStsdes varchar(max)
set @xStsnoheader='07'
set @xStsno='00';
set @xStsdes='sukses'
BEGIN TRY
SET NOCOUNT ON
DECLARE @NOW datetime
set @NOW =GETDATE()
DECLARE
@data_by varchar(50)
,@ms_supplier_customer_id int
,@ms_memo_type_id int
,@KODE_DOKUMEN_PABEAN varchar(255)
SELECT
@data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50)
,@KODE_DOKUMEN_PABEAN=left(ltrim(rtrim(isnull(tempTable.item.value('bc_prefix[1]', 'varchar(305)'),''))),255)
,@ms_supplier_customer_id=isnull(tempTable.item.value('ms_supplier_customer_id[1]', 'int'),-1)
,@ms_memo_type_id=isnull(tempTable.item.value('memo_prefix_id[1]', 'int'),-1)
FROM @PARAMETER.nodes('param/field') tempTable(item)
if not (@KODE_DOKUMEN_PABEAN='25' or
@KODE_DOKUMEN_PABEAN='41' or
@KODE_DOKUMEN_PABEAN='27' or
@KODE_DOKUMEN_PABEAN='30' or
@KODE_DOKUMEN_PABEAN='PEMUSNAHAN'
)
begin
set @xStsno='02';
set @xStsdes='Gagal, Type BC ' + @KODE_DOKUMEN_PABEAN + 'tidak terdaftar ' ;
goto f
end
declare @KODE_TUJUAN_PENGIRIMAN varchar(255)=(
case when @ms_memo_type_id=0 then --0 SCRAP
case when @KODE_DOKUMEN_PABEAN='25' then
null
when @KODE_DOKUMEN_PABEAN='30' then
'1'
when @KODE_DOKUMEN_PABEAN='27' then
'1'
when @KODE_DOKUMEN_PABEAN='41' then
'5'
when @KODE_DOKUMEN_PABEAN='261' then
'4'
else
null
end
when @ms_memo_type_id=1 then --1 REPAIR
case when @KODE_DOKUMEN_PABEAN='25' then
null
when @KODE_DOKUMEN_PABEAN='30' then
'1'
when @KODE_DOKUMEN_PABEAN='27' then
'4'
when @KODE_DOKUMEN_PABEAN='41' then
'5'
when @KODE_DOKUMEN_PABEAN='261' then
'1'
else
null
end
when @ms_memo_type_id=2 then --2 SELLING
case when @KODE_DOKUMEN_PABEAN='25' then
null
when @KODE_DOKUMEN_PABEAN='30' then
'1'
when @KODE_DOKUMEN_PABEAN='27' then
'1'
when @KODE_DOKUMEN_PABEAN='41' then
'5'
when @KODE_DOKUMEN_PABEAN='261' then
'4'
else
'1'
end
end
)
declare @tmp_memo table(
ms_memo_header_id bigint primary key
)
insert into @tmp_memo
SELECT
isnull(tempTable.item.value('id[1]', 'bigint'),-1)
FROM @PARAMETER.nodes('param/field/lst') tempTable(item)
if (select count(*) from @tmp_memo)=0
begin
set @xStsno='02';
set @xStsdes='Gagal, Memo harus ada' ;
goto f
end
--- CHECK APAKAH 1 TYPE KURS
if (
select count(*) from (
SELECT valuta_code
FROM vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
GROUP BY valuta_code
) xgr)>1
begin
set @xStsno='02';
set @xStsdes='Gagal, Memo Harus 1 jenis valuta ' ;
goto f
end
declare @NDPBM decimal(18,0)
,@KODE_VALUTA varchar(255)
select top (1)
@KODE_VALUTA=valuta_code
from vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
if @KODE_VALUTA='IDR'
begin
set @NDPBM=1
end
else
begin
set @NDPBM=dbo.get_rate_valuta(@NOW, @KODE_VALUTA )
if @NDPBM=1
begin
declare @valuta_date date =@now
declare @date_part int= DATEPART(dw,@valuta_date)
if @date_part <4
begin
set @valuta_date= dateadd(DAY,-(3 + @date_part),@valuta_date)
end
else if @date_part >4
begin
set @valuta_date= dateadd(DAY,-(@date_part-4),@valuta_date)
end
if NOT EXISTS (SELECT * FROM dt_currencies_header INNER JOIN
dt_currencies ON dt_currencies_header.id = dt_currencies.headerid INNER JOIN
dt_currencies_master ON dt_currencies.currenciesid = dt_currencies_master.id
WHERE (dt_currencies_master.currenciescode = 'USD' and dt_currencies_header.startdate =@valuta_date)
)
begin
set @xStsno='02';
set @xStsdes='Gagal, Nilai KURS ' + @KODE_VALUTA+' untuk tanggal ' + dbo.date_to_str(@valuta_date) + ' sampai tanggal ' + dbo.date_to_str(dateadd(dd,6,@valuta_date )) + ' belum ada ' ;
goto f
end
end
end
--- CHECK STATUS MEMO
SET @resultX= (
SELECT
'MEMO: '+ doc_no + '(' + dbo.date_to_str(doc_date) + ')'
+', STATUS:' + ms_memo_header_status_name +
+ @pemisah
AS [text()]
FROM
vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
AND (used = 1) AND not((ms_memo_header_status_id = 1))
order by doc_date,doc_no
For XML PATH (''))
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN
SET @xStsno='17';
SET @xStsdes='Gagal , Ada Memo yang tidak dalam Status APPROVE :' + @pemisah+ @pemisah+ @resultX + @pemisah+ @pemisah+ ' Periksa kembali data Memo ' ;
GOto f
end
--- CHECK STATUS MEMO
SET @resultX= (
SELECT
'MEMO: '+ doc_no + '(' + dbo.date_to_str(doc_date) + ')'
+', Terpakai di' + BC_TYPE +
+'CAR: ' + isnull(CAR,'') +
+'NO: ' + isnull(BC_NO,'') +
+'DATE: ' + dbo.date_to_str3(BC_DATE )
+ @pemisah
AS [text()]
FROM
vw_ms_memo_header
where exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
AND (used = 1)
order by doc_date,doc_no
For XML PATH (''))
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN
SET @xStsno='17';
SET @xStsdes='Gagal , Ada Memo yang sudah terpakai :' + @pemisah+ @pemisah+ @resultX + @pemisah+ @pemisah+ ' Periksa kembali data Memo ' ;
GOto f
end
declare
@check_ms_supplier_customer_id int
, @check_id_type int
, @check_id_nomor varchar(255)
, @check_supplier_customer_code varchar(255)
, @check_supplier_customer_name varchar(255)
, @check_supplier_customer_address varchar(255)
, @check_supplier_customer_country varchar(255)
, @check_api_type_id int
, @check_api_no varchar(255)
, @check_skep_no varchar(255)
, @check_skep_date date
SELECT top(1)
@check_ms_supplier_customer_id=ms_supplier_customer_id
, @check_id_type=id_type
, @check_id_nomor=id_nomor
, @check_supplier_customer_code=supplier_customer_code
, @check_supplier_customer_name=supplier_customer_name
, @check_supplier_customer_address=supplier_customer_address
, @check_supplier_customer_country=supplier_customer_country
, @check_api_type_id=api_type_id
, @check_api_no=api_no
, @check_skep_no=skep_no
, @check_skep_date=skep_date
FROM dt_ms_supplier_customer
WHERE (ms_supplier_customer_id = @ms_supplier_customer_id)
declare @ID_HEADER bigint
,@dt_ms_tpb_out_header_h_status_id bigint
declare @dt_ms_tpb_out_barang table (
ID bigint primary key
,ID_HEADER bigint
,SERI_BARANG int
,ms_memo_detail_id bigint
,nomor_hs varchar(50)
,tarif_bm decimal(18, 2)
,tarif_cukai decimal(18, 2)
,tarif_pph decimal(18, 2)
,tarif_ppn decimal(18, 2)
,tarif_ppnbm decimal(18, 2)
,total_harga_rupiah decimal(38, 4)
,total_harga_usd decimal(38, 4)
,total_harga_bm decimal(38, 4)
,total_harga_penyerahan_plus_bm decimal(38, 4)
)
declare @dt_ms_tpb_out_bahan_baku table (
id_bahan_baku bigint primary key
,ID_BARANG bigint
,SERI_BARANG int
,ID_HEADER bigint
,nomor_hs varchar(50)
,tarif_bm decimal(18, 2)
,tarif_cukai decimal(18, 2)
,tarif_pph decimal(18, 2)
,tarif_ppn decimal(18, 2)
,tarif_ppnbm decimal(18, 2)
,total_harga_rupiah decimal(38, 4)
,total_harga_usd decimal(38, 4)
,tarif_bm_barang decimal(18, 2)
,KODE_ASAL int
,total_harga_bm decimal(38, 4)
,total_harga_penyerahan_plus_bm decimal(38, 4)
, KODE_JENIS_DOK_ASAL varchar(255)
, NOMOR_DAFTAR_DOK_ASAL varchar(255)
, TANGGAL_DAFTAR_DOK_ASAL date
)
BEGIN TRANSACTION;
INSERT INTO dt_ms_tpb_out_header
( ID_HEADER_ORI
, KODE_DOKUMEN_PABEAN
, KODE_JENIS_TPB
, KODE_TUJUAN_TPB
, KODE_TUJUAN_PENGIRIMAN
, NOMOR_AJU
, ID_PENERIMA_BARANG
, ALAMAT_PENERIMA_BARANG
, KODE_ID_PENERIMA_BARANG
, NAMA_PENERIMA_BARANG
, NAMA_PENGANGKUT
, NOMOR_IJIN_TPB_PENERIMA
, NOMOR_POLISI
, KODE_VALUTA
, NDPBM
, CIF
, HARGA_PENYERAHAN
, JUMLAH_BARANG
, JUMLAH_KONTAINER
, JUMLAH_KEMASAN
, NETTO
, BRUTO
, VOLUME
, mapping_supplier_id
, mapping_supplier_date
, mapping_supplier_by
, mode_source
, create_date
, create_by
, modify_date
, modify_by
,type_source_id)
VALUES (
- 1
, @KODE_DOKUMEN_PABEAN --KODE_DOKUMEN_PABEAN
, 1 --KODE_JENIS_TPB
, case when @KODE_DOKUMEN_PABEAN='27' then 1 else NULL end --KODE_TUJUAN_TPB
, @KODE_TUJUAN_PENGIRIMAN --KODE_TUJUAN_PENGIRIMAN
, dbo.draft_car_bc_in(case when @ms_memo_type_id =0 then 'SCRAP' when @ms_memo_type_id =1 then 'REPAIR' when @ms_memo_type_id =2 then 'SELLING' else 'MEMO' end + ' BC ' + @KODE_DOKUMEN_PABEAN ,@KODE_DOKUMEN_PABEAN) --NOMOR_AJU
, @check_id_nomor --ID_PENERIMA_BARANG
, @check_supplier_customer_address --ALAMAT_PENERIMA_BARANG
, @check_id_type --KODE_ID_PENERIMA_BARANG
, @check_supplier_customer_name --NAMA_PENERIMA_BARANG
, '-' --NAMA_PENGANGKUT
, @check_skep_no --NOMOR_IJIN_TPB_PENERIMA
, '-' --NOMOR_POLISI
, @KODE_VALUTA --KODE_VALUTA
, @ndpbm --NDPBM
, 0 --CIF
, 0 --HARGA_PENYERAHAN
, 0 --JUMLAH_BARANG
, 0 --JUMLAH_KONTAINER
, 0 --JUMLAH_KEMASAN
, 0 --NETTO
, 0 --BRUTO
, 0 --VOLUME
, @check_ms_supplier_customer_id
, @now
, @data_by
, 3
, @now
, @data_by
, @now
, @data_by
, @ms_memo_type_id)
set @ID_HEADER=SCOPE_IDENTITY();
UPDATE dt_ms_memo_header
SET source_out_id = @ID_HEADER, mode_source_out = 0
WHERE exists(
select * from @tmp_memo as xfilter
where dt_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
)
INSERT INTO dt_ms_tpb_out_header_h_status
(ID_HEADER, status_tpb_out_id, status_info, create_date, create_by)
VALUES (@ID_HEADER, 0, '',@now,@data_by)
set @dt_ms_tpb_out_header_h_status_id=SCOPE_IDENTITY();
update dt_ms_tpb_out_header
set dt_ms_tpb_out_header_h_status_id=@dt_ms_tpb_out_header_h_status_id
where ID_HEADER=@ID_HEADER
--INSERT INTO dt_ms_tpb_out_dokumen
--(FLAG_URL_DOKUMEN
-- , KODE_JENIS_DOKUMEN
-- , NOMOR_DOKUMEN
-- , SERI_DOKUMEN
-- , TANGGAL_DOKUMEN
-- , TIPE_DOKUMEN
-- , URL_DOKUMEN
-- , ID_HEADER
-- , ID_HEADER_ORI
-- , create_date
-- , create_by
-- , modify_date
-- , modify_by
-- , mode_source
-- , id_source
--)
-- SELECT
-- ''
-- , '380'
-- , invoice_no
-- ,ROW_NUMBER() OVER(ORDER BY invoice_date,invoice_no) as rn
-- , invoice_date
-- ,'02'
-- ,''
-- ,@ID_HEADER
-- , - 1
-- ,@now
-- ,@data_by
-- ,@now
-- ,@data_by
-- ,1
-- ,ms_memo_header_id
-- FROM vw_ms_memo_header
-- WHERE exists(
-- select * from @tmp_memo as xfilter
-- where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
-- )
-- union all
-- SELECT
-- ''
-- , '217'
-- , invoice_no
-- ,ROW_NUMBER() OVER(ORDER BY invoice_date,invoice_no) as rn
-- , invoice_date
-- ,'02'
-- ,''
-- ,@ID_HEADER
-- , - 1
-- ,@now
-- ,@data_by
-- ,@now
-- ,@data_by
-- ,1
-- ,ms_memo_header_id
-- FROM vw_ms_memo_header
-- WHERE exists(
-- select * from @tmp_memo as xfilter
-- where vw_ms_memo_header.ms_memo_header_id =xfilter.ms_memo_header_id
-- )
MERGE INTO dt_ms_tpb_out_barang USING (
select vw_ms_memo_detail.total_price --CIF
, vw_ms_memo_detail.total_price --FOB
, vw_ms_memo_detail.total_price --HARGA_INVOICE
, vw_ms_memo_detail.total_price* @NDPBM --CIF_RUPIAH
, vw_ms_memo_detail.total_price* @NDPBM --HARGA_PENYERAHAN
, vw_ms_memo_detail.total_price/vw_ms_memo_detail.quantity --HARGA_SATUAN
, vw_ms_memo_detail.quantity --JUMLAH_SATUAN
, dt_ms_item.part_no --KODE_BARANG
, vw_ms_memo_detail.package_qty --JUMLAH_KEMASAN
, vw_ms_memo_detail.package_code --KODE_KEMASAN
, dt_ms_item.uom --KODE_SATUAN
, case when @ms_memo_type_id =0 or @ms_memo_type_id =1 then 2 else 1 end --KONDISI_BARANG
, 4--case when dt_ms_item.ms_type_id=3 then 5 else end --KODE_GUNA
, case when dt_ms_item.ms_type_id=3 then 5 else 3 end --KATEGORI_BARANG
, '-' --MERK
, '-' --SPESIFIKASI_LAIN
, dt_ms_item.ms_item_custom_no --TIPE
, '-' --UKURAN
, vw_ms_memo_detail.netto_qty --NETTO
, ROW_NUMBER() OVER(ORDER BY vw_ms_memo_detail.ms_memo_detail_id ) as SERI_BARANG
, dt_ms_item_custom.ms_item_custom_name --URAIAN
, '02' --KODE_STATUS
, @ID_HEADER
, dt_ms_item_custom_h_tarif.nomor_hs
, vw_ms_memo_detail.negara_asal --KODE_NEGARA_ASAL
, 3
, vw_ms_memo_detail.ms_memo_detail_id
, ''
, @now
, @data_by
, @now
, @data_by
, vw_ms_memo_detail.ms_item_id--mapping_ms_item_id
, vw_ms_memo_detail.quantity--qty_pakai
, vw_ms_memo_detail.modify_date--mapping_date
, vw_ms_memo_detail.modify_by_id--mapping_by
, dt_ms_item_custom_h_tarif.tarif_bm
, dt_ms_item_custom_h_tarif.tarif_cukai
, dt_ms_item_custom_h_tarif.tarif_pph
, dt_ms_item_custom_h_tarif.tarif_ppn
, dt_ms_item_custom_h_tarif.tarif_ppnbm
,vw_ms_memo_detail.total_price* @NDPBM as total_harga_rupiah
FROM vw_ms_memo_detail INNER JOIN
dt_ms_item ON vw_ms_memo_detail.ms_item_id = dt_ms_item.ms_item_id INNER JOIN
dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no INNER JOIN
dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id
WHERE exists(
select * from @tmp_memo as xfilter
where vw_ms_memo_detail.ms_memo_header_id =xfilter.ms_memo_header_id
)
) as source( CIF
, FOB
, HARGA_INVOICE
, CIF_RUPIAH
, HARGA_PENYERAHAN
, HARGA_SATUAN
, JUMLAH_SATUAN
, KODE_BARANG
, JUMLAH_KEMASAN
, KODE_KEMASAN
, KODE_SATUAN
, KONDISI_BARANG
, KODE_GUNA
, KATEGORI_BARANG
, MERK
, SPESIFIKASI_LAIN
, TIPE
, UKURAN
, NETTO
, SERI_BARANG
, URAIAN
, KODE_STATUS
, ID_HEADER
, POS_TARIF
, KODE_NEGARA_ASAL
, mode_source
, ms_memo_detail_id
, id_source_xml
, create_date
, create_by
, modify_date
, modify_by
, mapping_ms_item_id
, qty_pakai
, mapping_date
, mapping_by
, tarif_bm
, tarif_cukai
, tarif_pph
, tarif_ppn
, tarif_ppnbm
,total_harga_rupiah
) ON 1 = 0
WHEN NOT MATCHED THEN
INSERT ( CIF
, FOB
, HARGA_INVOICE
, CIF_RUPIAH
, HARGA_PENYERAHAN
, HARGA_SATUAN
, JUMLAH_SATUAN
, KODE_BARANG
, JUMLAH_KEMASAN
, KODE_KEMASAN
, KODE_SATUAN
, KONDISI_BARANG
, KODE_GUNA
, KATEGORI_BARANG
, MERK
, SPESIFIKASI_LAIN
, TIPE
, UKURAN
, NETTO
, SERI_BARANG
, URAIAN
, KODE_STATUS
, ID_HEADER
, POS_TARIF
, KODE_NEGARA_ASAL
, mode_source
, id_source
, id_source_xml
, create_date
, create_by
, modify_date
, modify_by
, mapping_ms_item_id
, qty_pakai
, mapping_date
, mapping_by)
VALUES (source.CIF
,source.FOB
,source.HARGA_INVOICE
,source.CIF_RUPIAH
,source.HARGA_PENYERAHAN
,source.HARGA_SATUAN
,source.JUMLAH_SATUAN
,source.KODE_BARANG
,source.JUMLAH_KEMASAN
,source.KODE_KEMASAN
,source.KODE_SATUAN
,source.KONDISI_BARANG
,source.KODE_GUNA
,source.KATEGORI_BARANG
,source.MERK
,source.SPESIFIKASI_LAIN
,source.TIPE
,source.UKURAN
,source.NETTO
,source.SERI_BARANG
,source.URAIAN
,source.KODE_STATUS
,source.ID_HEADER
,source.POS_TARIF
,source.KODE_NEGARA_ASAL
,source.mode_source
,source.ms_memo_detail_id
,source.id_source_xml
,source.create_date
,source.create_by
,source.modify_date
,source.modify_by
,source.mapping_ms_item_id
,source.qty_pakai
,source.mapping_date
,source.mapping_by
)
OUTPUT
inserted.ID
,source.ID_HEADER
, inserted.SERI_BARANG
,source.ms_memo_detail_id
,source.POS_TARIF
,source.tarif_bm
,source.tarif_cukai
,source.tarif_pph
,source.tarif_ppn
,source.tarif_ppnbm
,source.total_harga_rupiah
,case when source.tarif_bm=0 then 0 else (source.total_harga_rupiah * source.tarif_bm)/100 end
,(case when source.tarif_bm=0 then 0 else (source.total_harga_rupiah * source.tarif_bm)/100 end )+ source.total_harga_rupiah --total_harga_penyerahan_plus_bm
INTO @dt_ms_tpb_out_barang (
ID
,ID_HEADER
,SERI_BARANG
,ms_memo_detail_id
,nomor_hs
,tarif_bm
,tarif_cukai
,tarif_pph
,tarif_ppn
,tarif_ppnbm
,total_harga_rupiah
,total_harga_bm
,total_harga_penyerahan_plus_bm
);
MERGE INTO dt_ms_tpb_out_bahan_baku USING (
SELECT
ROW_NUMBER() OVER(PARTITION BY data_memo.ID ORDER BY vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL ) as SERI_BAHAN_BAKU
,data_memo.ID, data_memo.ID_HEADER,data_memo.SERI_BARANG,data_memo.tarif_bm, vw_ms_memo_detail_suplly_ori_document.ms_memo_detail_id, vw_ms_memo_detail_suplly_ori_document.ms_in_item_detail_id, vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL,
vw_ms_memo_detail_suplly_ori_document.NOMOR_DAFTAR_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.TANGGAL_DAFTAR_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.KODE_KANTOR,
vw_ms_memo_detail_suplly_ori_document.NOMOR_AJU_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.SERI_BARANG_DOK_ASAL, vw_ms_memo_detail_suplly_ori_document.KODE_VALUTA,
vw_ms_memo_detail_suplly_ori_document.NDPBM, vw_ms_memo_detail_suplly_ori_document.TOTAL_HARGA_RUPIAH, vw_ms_memo_detail_suplly_ori_document.TOTAL_HARGA_USD,
vw_ms_memo_detail_suplly_ori_document.JUMLAH, vw_ms_memo_detail_suplly_ori_document.ms_type_id, vw_ms_memo_detail_suplly_ori_document.ms_item_custom_no,
vw_ms_memo_detail_suplly_ori_document.ms_item_base_no, vw_ms_memo_detail_suplly_ori_document.part_no, vw_ms_memo_detail_suplly_ori_document.part_name, vw_ms_memo_detail_suplly_ori_document.uom,
vw_ms_memo_detail_suplly_ori_document.ms_item_id, vw_ms_memo_detail_suplly_ori_document.ms_item_base_name, vw_ms_memo_detail_suplly_ori_document.ms_item_custom_name,
vw_ms_memo_detail_suplly_ori_document.nomor_hs, vw_ms_memo_detail_suplly_ori_document.tarif_bm, vw_ms_memo_detail_suplly_ori_document.tarif_cukai, vw_ms_memo_detail_suplly_ori_document.tarif_pph,
vw_ms_memo_detail_suplly_ori_document.tarif_ppn, vw_ms_memo_detail_suplly_ori_document.tarif_ppnbm
FROM vw_ms_memo_detail_suplly_ori_document INNER JOIN
@dt_ms_tpb_out_barang AS data_memo ON vw_ms_memo_detail_suplly_ori_document.ms_memo_detail_id = data_memo.ms_memo_detail_id
where ( ( @KODE_DOKUMEN_PABEAN='41' and(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27'))
or ( @KODE_DOKUMEN_PABEAN='25' and(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='23'))
or ( @KODE_DOKUMEN_PABEAN='27' and(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='23'))
or ( @KODE_DOKUMEN_PABEAN='261' and not (vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='40' or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='27'or vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='23'))
) and not(vw_ms_memo_detail_suplly_ori_document.KODE_JENIS_DOK_ASAL='262')
) as source(SERI_BAHAN_BAKU, ID_BARANG,ID_HEADER,SERI_BARANG,tarif_bm_barang,
ms_memo_detail_id, ms_in_item_detail_id, KODE_JENIS_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL, TANGGAL_DAFTAR_DOK_ASAL, KODE_KANTOR, NOMOR_AJU_DOK_ASAL, SERI_BARANG_DOK_ASAL, KODE_VALUTA,
NDPBM, TOTAL_HARGA_RUPIAH, TOTAL_HARGA_USD, JUMLAH, ms_type_id, ms_item_custom_no, ms_item_base_no, part_no, part_name, uom, ms_item_id, ms_item_base_name, ms_item_custom_name, nomor_hs,
tarif_bm, tarif_cukai, tarif_pph, tarif_ppn, tarif_ppnbm
) ON 1 = 0
WHEN NOT MATCHED THEN
INSERT ( mode_source
, id_source
, KODE_STATUS
, KODE_ASAL_BAHAN_BAKU
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
, KODE_KANTOR
, NOMOR_AJU_DOK_ASAL
, SERI_BARANG_DOK_ASAL
, KODE_BARANG
, POS_TARIF
, URAIAN
, JUMLAH_SATUAN
, JENIS_SATUAN
, HARGA_PENYERAHAN
, HARGA_PEROLEHAN
, CIF
, NDPBM
, CIF_RUPIAH
, SERI_BAHAN_BAKU
, SERI_BARANG
, ID_BARANG
, ID_HEADER
, create_date
, create_by
, modify_date
, modify_by)
VALUES ( 0
, source.ms_in_item_detail_id
, case
when isnull(source.KODE_KANTOR ,'') =''then '02'
when (isnull(source.nomor_hs ,'') =''and @KODE_DOKUMEN_PABEAN='27' and source.KODE_JENIS_DOK_ASAL<>'40' ) then '02'
else
'03'
end
, case when @KODE_DOKUMEN_PABEAN='40' then
'1'
else
case when source.KODE_JENIS_DOK_ASAL='23' then '0'
when source.KODE_JENIS_DOK_ASAL='27' then '0'
else
'0'
end
end ---KODE_ASAL_BAHAN_BAKU
, source.KODE_JENIS_DOK_ASAL
, source.NOMOR_DAFTAR_DOK_ASAL
, source.TANGGAL_DAFTAR_DOK_ASAL
, source.KODE_KANTOR --'040300'
, source.NOMOR_AJU_DOK_ASAL
, source.SERI_BARANG_DOK_ASAL
, source.PART_NO
, source.nomor_hs
, source.ms_item_custom_name
, source.JUMLAH
, source.UOM
, case when @KODE_DOKUMEN_PABEAN='25' and (source.KODE_JENIS_DOK_ASAL='23' or source.KODE_JENIS_DOK_ASAL='27') then 0 else TOTAL_HARGA_RUPIAH end --HARGA_PENYERAHAN
, 0--source.HARGA_PEROLEHAN
, case when(@KODE_DOKUMEN_PABEAN='25' or @KODE_DOKUMEN_PABEAN='27') then
case when (source.KODE_JENIS_DOK_ASAL='23' or source.KODE_JENIS_DOK_ASAL='27') then TOTAL_HARGA_USD else 0 end
when(@KODE_DOKUMEN_PABEAN='41') then
TOTAL_HARGA_RUPIAH
else 0
end ---CIF
, source.NDPBM
, case when @KODE_DOKUMEN_PABEAN='25' and (source.KODE_JENIS_DOK_ASAL='23' or source.KODE_JENIS_DOK_ASAL='27') then TOTAL_HARGA_RUPIAH else 0 end --CIF_RUPIAH
, source.SERI_BAHAN_BAKU
, source.SERI_BARANG
, source.ID_BARANG
, source.ID_HEADER
, @now
, @data_by
, @now
, @data_by
)
OUTPUT
inserted.ID
,source.ID_BARANG
,source.SERI_BARANG
,source.ID_HEADER
,inserted.POS_TARIF
,source.tarif_bm
,source.tarif_cukai
,source.tarif_pph
,source.tarif_ppn
,source.tarif_ppnbm
,source.total_harga_rupiah
,source.total_harga_usd
,source.tarif_bm_barang
,inserted.KODE_ASAL_BAHAN_BAKU
,case when @KODE_DOKUMEN_PABEAN ='25' then
case when source.tarif_bm_barang>source.tarif_bm then
(source.total_harga_rupiah * source.tarif_bm_barang)/100
else
(source.total_harga_rupiah * source.tarif_bm)/100
end
else
(source.total_harga_rupiah * source.tarif_bm)/100
end --total_harga_bm
,(case when @KODE_DOKUMEN_PABEAN ='25' then
case when source.tarif_bm_barang>source.tarif_bm then
(source.total_harga_rupiah * source.tarif_bm_barang)/100
else
(source.total_harga_rupiah * source.tarif_bm)/100
end
else
(source.total_harga_rupiah * source.tarif_bm)/100
end) + source.total_harga_rupiah --total_harga_penyerahan_plus_bm
, inserted.KODE_JENIS_DOK_ASAL
, inserted.NOMOR_DAFTAR_DOK_ASAL
, inserted.TANGGAL_DAFTAR_DOK_ASAL
INTO @dt_ms_tpb_out_bahan_baku (
id_bahan_baku
,ID_BARANG
,SERI_BARANG
,ID_HEADER
,nomor_hs
,tarif_bm
,tarif_cukai
,tarif_pph
,tarif_ppn
,tarif_ppnbm
,total_harga_rupiah
,total_harga_usd
,tarif_bm_barang
,KODE_ASAL
,total_harga_bm
,total_harga_penyerahan_plus_bm
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
, TANGGAL_DAFTAR_DOK_ASAL
);
----MASUKAN TARIF SEMUA YANG LOKAL
INSERT INTO dt_ms_tpb_out_bahan_baku_tarif
(JENIS_TARIF
, KODE_ASAL_BAHAN_BAKU
, KODE_FASILITAS
, KODE_SATUAN
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BAHAN_BAKU
, ID_BARANG
, ID_HEADER
)
select
case when JenisTarif='tarif_bm' then 'BM'
when JenisTarif='tarif_cukai' then 'CUKAI'
when JenisTarif='tarif_pph' then 'PPH'
when JenisTarif='tarif_ppn' then 'PPN'
when JenisTarif='tarif_ppnbm' then 'PPNBM'
else'' end as JENIS_TARIF --- JENIS_TARIF
,KODE_ASAL ---KODE_ASAL_BAHAN_BAKU
,null ---KODE_FASILITAS
,null --KODE_SATUAN
,(total_harga_rupiah * isnull(nullif(ValueTarif,0),10)) /100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,ValueTarif --TARIF
,100 --TARIF_FASILITAS
,id_bahan_baku --ID_BAHAN_BAKU
,ID_BARANG --ID_BARANG
,ID_HEADER --ID_HEADER
from @dt_ms_tpb_out_bahan_baku
unpivot
(
ValueTarif
for JenisTarif in (tarif_ppn)
) unpiv
where KODE_ASAL=1
;
-- INTO @dt_ms_tpb_out_bahan_baku (
--id_bahan_baku
--,ID_BARANG
--,SERI_BARANG
--,ID_HEADER
--,nomor_hs
--,tarif_bm
--,tarif_cukai
--,tarif_pph
--,tarif_ppn
--,tarif_ppnbm
--,total_harga_rupiah
--,total_harga_usd
--,tarif_bm_barang
--,KODE_ASAL
--,total_harga_bm
--,total_harga_penyerahan_plus_bm
--);
----MASUKAN TARIF SEMUA YANG IMPORT
INSERT INTO dt_ms_tpb_out_bahan_baku_tarif
(JENIS_TARIF
, KODE_ASAL_BAHAN_BAKU
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BAHAN_BAKU
, ID_BARANG
, ID_HEADER
)
select
case when JenisTarif='tarif_bm' then 'BM'
when JenisTarif='tarif_cukai' then 'CUKAI'
when JenisTarif='tarif_pph' then 'PPH'
when JenisTarif='tarif_ppn' then 'PPN'
when JenisTarif='tarif_ppnbm' then 'PPNBM'
else'' end as JENIS_TARIF --- JENIS_TARIF
,KODE_ASAL ---KODE_ASAL_BAHAN_BAKU
,case when @KODE_DOKUMEN_PABEAN ='25' then 0 else 2 end ---KODE_FASILITAS
,null --KODE_SATUAN
,case when JenisTarif='tarif_bm' then 1 else null end --KODE_TARIF
,case when JenisTarif='tarif_bm' then total_harga_bm else (total_harga_penyerahan_plus_bm * isnull(nullif(ValueTarif,0),10)) /100 end --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,ValueTarif --TARIF
,100 --TARIF_FASILITAS
,id_bahan_baku --ID_BAHAN_BAKU
,ID_BARANG --ID_BARANG
,ID_HEADER --ID_HEADER
from @dt_ms_tpb_out_bahan_baku
unpivot
(
ValueTarif
for JenisTarif in (tarif_bm ,tarif_pph,tarif_ppn,tarif_ppnbm)
) unpiv
where KODE_ASAL=0 and ValueTarif>0
;
if @KODE_DOKUMEN_PABEAN='41'
begin
INSERT INTO dt_ms_tpb_out_dokumen
(FLAG_URL_DOKUMEN
, KODE_JENIS_DOKUMEN
, NOMOR_DOKUMEN
, SERI_DOKUMEN
, TANGGAL_DOKUMEN
, TIPE_DOKUMEN
, URL_DOKUMEN
, ID_HEADER
, ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
, id_source
)
SELECT
'' AS Expr1
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
,ROW_NUMBER() OVER(ORDER BY KODE_JENIS_DOK_ASAL,TANGGAL_DAFTAR_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL) as rn
, TANGGAL_DAFTAR_DOK_ASAL
,'01'
,''
,@ID_HEADER
, - 1
,@now
,@data_by
,@now
,@data_by
,1
, -1
FROM @dt_ms_tpb_out_bahan_baku
WHERE (ID_HEADER = @ID_HEADER) AND (KODE_JENIS_DOK_ASAL= '40')
GROUP BY ID_HEADER, TANGGAL_DAFTAR_DOK_ASAL, KODE_JENIS_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL
end
else if @KODE_DOKUMEN_PABEAN='27'
begin
INSERT INTO dt_ms_tpb_out_dokumen
(FLAG_URL_DOKUMEN
, KODE_JENIS_DOKUMEN
, NOMOR_DOKUMEN
, SERI_DOKUMEN
, TANGGAL_DOKUMEN
, TIPE_DOKUMEN
, URL_DOKUMEN
, ID_HEADER
, ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
, id_source
)
SELECT
'' AS Expr1
, KODE_JENIS_DOK_ASAL
, NOMOR_DAFTAR_DOK_ASAL
,ROW_NUMBER() OVER(ORDER BY KODE_JENIS_DOK_ASAL,TANGGAL_DAFTAR_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL) as rn
, TANGGAL_DAFTAR_DOK_ASAL
,'01'
,''
,@ID_HEADER
, - 1
,@now
,@data_by
,@now
,@data_by
,1
, -1
FROM @dt_ms_tpb_out_bahan_baku
WHERE (ID_HEADER = @ID_HEADER) AND (KODE_JENIS_DOK_ASAL= '271' or KODE_JENIS_DOK_ASAL= '27' or KODE_JENIS_DOK_ASAL= '23' )
GROUP BY ID_HEADER, TANGGAL_DAFTAR_DOK_ASAL, KODE_JENIS_DOK_ASAL, NOMOR_DAFTAR_DOK_ASAL
end
MERGE dt_ms_tpb_out_dokumen AS target
USING (
select id ,ROW_NUMBER() OVER(ORDER BY orderx ,SERI_DOKUMEN) as rn from (
SELECT id,
SERI_DOKUMEN,
case when KODE_JENIS_DOKUMEN='380' then 1
when KODE_JENIS_DOKUMEN='217' then 2
when KODE_JENIS_DOKUMEN='40' then 3
when KODE_JENIS_DOKUMEN='27' then 4
when KODE_JENIS_DOKUMEN='271' then 4
when KODE_JENIS_DOKUMEN='23' then 5
else 6
end as orderx
from dt_ms_tpb_out_dokumen
where ID_HEADER=@ID_HEADER
)xdata
) AS source (id, rn)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET SERI_DOKUMEN = source.rn ;
if @KODE_DOKUMEN_PABEAN ='25'
begin
declare @cur_item_ID bigint
,@cur_ms_memo_detail_id bigint
,@cur_BM decimal(38,2)
,@cur_PPNBM decimal(38,2)
,@cur_PPH decimal(38,2)
,@cur_PPN decimal(38,2)
,@cur_ID_BARANG bigint
,@cur_SERI_BARANG int
,@cur_price_total decimal(24, 4)
INSERT INTO dt_ms_tpb_out_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
select
case when JenisTarif='tarif_bm' then 'BM'
when JenisTarif='tarif_cukai' then 'CUKAI'
when JenisTarif='tarif_pph' then 'PPH'
when JenisTarif='tarif_ppn' then 'PPN'
when JenisTarif='tarif_ppnbm' then 'PPNBM'
else'' end as JENIS_TARIF --- JENIS_TARIF
,case when @KODE_DOKUMEN_PABEAN ='25' then 0 else 2 end ---KODE_FASILITAS
,null --KODE_SATUAN
,case when JenisTarif='tarif_bm' then 1 else null end --KODE_TARIF
,case when JenisTarif='tarif_bm' then total_harga_bm else (total_harga_penyerahan_plus_bm * isnull(nullif(ValueTarif,0),10)) /100 end --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,ValueTarif --TARIF
,100 --TARIF_FASILITAS
,ID --ID_BARANG
,ID_HEADER --ID_HEADER
from @dt_ms_tpb_out_barang
unpivot
(
ValueTarif
for JenisTarif in (tarif_bm ,tarif_pph,tarif_ppn,tarif_ppnbm)
) unpiv
where ValueTarif>0
;
end
update dt_ms_tpb_out_barang
set CIF=case when @KODE_DOKUMEN_PABEAN ='25' then sum_bahan_baku.CIF else dt_ms_tpb_out_barang.CIF end
,CIF_RUPIAH=case when @KODE_DOKUMEN_PABEAN ='25' then sum_bahan_baku.CIF_RUPIAH else dt_ms_tpb_out_barang.CIF_RUPIAH end
,JUMLAH_BAHAN_BAKU=sum_bahan_baku.JUMLAH_BAHAN_BAKU
from dt_ms_tpb_out_barang INNER JOIN
(
SELECT ID_BARANG
, SUM(CIF) AS CIF
, SUM(CIF_RUPIAH) AS CIF_RUPIAH
, COUNT(*) as JUMLAH_BAHAN_BAKU
FROM dt_ms_tpb_out_bahan_baku
GROUP BY ID_BARANG ) AS sum_bahan_baku ON dt_ms_tpb_out_barang.ID = sum_bahan_baku.ID_BARANG
where dt_ms_tpb_out_barang.ID_HEADER =@ID_HEADER
UPDATE dt_ms_tpb_out_header
SET CIF=sum_detail.CIF
,FOB=sum_detail.FOB
,HARGA_INVOICE =sum_detail.HARGA_INVOICE
,CIF_RUPIAH=sum_detail.CIF_RUPIAH
,HARGA_PENYERAHAN=sum_detail.HARGA_PENYERAHAN
,NETTO=sum_detail.NETTO
,JUMLAH_BARANG=sum_detail.JUMLAH_BARANG
FROM dt_ms_tpb_out_header INNER JOIN
(
SELECT ID_HEADER
, SUM(FOB) AS FOB
, SUM(CIF) AS CIF
, SUM(HARGA_INVOICE) AS HARGA_INVOICE
, SUM(CIF_RUPIAH) AS CIF_RUPIAH
, SUM(HARGA_PENYERAHAN) AS HARGA_PENYERAHAN
, SUM(NETTO) AS NETTO
, COUNT(*) as JUMLAH_BARANG
FROM dt_ms_tpb_out_barang
GROUP BY ID_HEADER ) AS sum_detail ON dt_ms_tpb_out_header.ID_HEADER = sum_detail.ID_HEADER
where dt_ms_tpb_out_header.ID_HEADER =@ID_HEADER
--- OLAH INSERT BAHAN BAKU DARI SELAIN FINISH GOOD
--- CALCULASI ASAL BARANG
--@dt_ms_tpb_out_barang
COMMIT TRANSACTION;
f:
set nocount off
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK TRAN;
set @xStsno='88'
set @xStsdes= ERROR_MESSAGE()
END CATCH
g:
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
select CAST( (select @XmlSts,isnull(@raw ,'') for xml path ('result')
) as XML) as dt
GO
Depends On
22- dbo.vw_ms_memo_header
- dbo.get_rate_valuta
- dbo.dt_currencies_header
- dbo.dt_currencies
- dbo.dt_currencies_master
- dbo.date_to_str
- dbo.date_to_str3
- dbo.dt_ms_supplier_customer
- dbo.dt_ms_tpb_out_header
- dbo.draft_car_bc_in
- dbo.dt_ms_memo_header
- dbo.dt_ms_tpb_out_header_h_status
- dbo.dt_ms_tpb_out_barang
- dbo.vw_ms_memo_detail
- dbo.dt_ms_item
- dbo.dt_ms_item_custom
- dbo.dt_ms_item_custom_h_tarif
- dbo.dt_ms_tpb_out_bahan_baku
- dbo.vw_ms_memo_detail_suplly_ori_document
- dbo.dt_ms_tpb_out_bahan_baku_tarif
- dbo.dt_ms_tpb_out_dokumen
- dbo.dt_ms_tpb_out_barang_tarif
Used By
No items found