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_IN_IMPORT_FROM_GRN
@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
,@KODE_DOKUMEN_PABEAN varchar(255)
,@ms_manual_invoice_type_id int
declare @NDPBM decimal(18,0)
,@KODE_VALUTA varchar(255)
,@KODE_HARGA varchar(255)
,@allow_move_ms_date_business bit
SELECT
@data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50)
,@allow_move_ms_date_business=isnull(tempTable.item.value('allow_move_ms_date_business[1]', 'bit'),0)
,@KODE_VALUTA=left(ltrim(rtrim(isnull(tempTable.item.value('KODE_VALUTA[1]', 'varchar(305)'),''))),255)
,@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_manual_invoice_type_id=isnull(tempTable.item.value('ms_manual_invoice_type_id[1]', 'int'),-1)
FROM @PARAMETER.nodes('param/field') tempTable(item)
if not (@KODE_DOKUMEN_PABEAN='23' or
@KODE_DOKUMEN_PABEAN='271' or
@KODE_DOKUMEN_PABEAN='40'
)
begin
set @xStsno='02';
set @xStsdes='Gagal, Type BC ' + @KODE_DOKUMEN_PABEAN + 'tidak terdaftar ' ;
goto f
end
if @KODE_VALUTA=''
begin
set @xStsno='02';
set @xStsdes='Gagal, Pilih Mata Uang ' ;
goto f
end
declare @tmp_invoice table(
id bigint primary key
)
insert into @tmp_invoice (id)
SELECT
isnull(tempTable.item.value('id[1]', 'bigint'),-1) as id
FROM @PARAMETER.nodes('param/field/lst') tempTable(item)
if (select count(*) from @tmp_invoice)=0
begin
set @xStsno='02';
set @xStsdes='Gagal, Grn harus ada' ;
goto f
end
declare @result_date_business_id bigint,
@result_date_business date,
@result_date_business_id_from bigint,
@result_date_business_from date,
@is_move_date_business bit,
@business_date date=cast(@NOW as date)
EXEC [dbo].[MG_MS_CHECK_BUSINESS_DATE_AND_AVG]
@business_date = @business_date,
@allow_move_ms_date_business = @allow_move_ms_date_business,
@result_date_business_id = @result_date_business_id OUTPUT,
@result_date_business = @result_date_business OUTPUT,
@result_date_business_id_from = @result_date_business_id_from OUTPUT,
@result_date_business_from = @result_date_business_from OUTPUT,
@is_move_date_business = @is_move_date_business OUTPUT,
@xStsno = @xStsno OUTPUT,
@xStsdes = @xStsdes OUTPUT
if not(@xStsno='00')
begin
goto f
end
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 INVOICE
SET @resultX= (
SELECT
'Grn: '+ GRNUMBER + '(' + dbo.date_to_str(GRDATE) + ')'
+', Terpakai di' + BC_TYPE +
+'CAR: ' + isnull(CAR,'') +
+'NO: ' + isnull(BC_NO,'') +
+'DATE: ' + dbo.date_to_str3(BC_DATE )
+ @pemisah
AS [text()]
FROM
vw_ms_molts_grn
where exists(
select * from @tmp_invoice as xfilter
where vw_ms_molts_grn.id =xfilter.id
)
AND (used = 1)
order by GRDATE,GRNUMBER
For XML PATH (''))
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN
SET @xStsno='17';
SET @xStsdes='Gagal , Ada Grn yang sudah terpakai :' + @pemisah+ @pemisah+ @resultX + @pemisah+ @pemisah+ ' Periksa kembali data GRN ' ;
GOto f
end
IF (select count(*)
--faktur_no, faktur_date, inv_no, inv_date
from vw_ms_molts_grn a
--WHERE (a.id IN (select id from @tmp_invoice) and (faktur_date = '1970-01-01' or inv_date = '1970-01-01'))) >0
WHERE (a.id IN (select id from @tmp_invoice) and (inv_date = '1970-01-01'))) >0
BEGIN
SET @xStsno='05';
--SET @xStsdes='Gagal, Ada No/Tgl faktur atau No/Tgl Invoice yang kosong';
SET @xStsdes='No/Tgl Invoice yang kosong';
GOto f
END
--IF (SELECT count(*) AS Expr1
-- FROM vw_ms_molts_grn a
-- WHERE (a.id IN (select id from @tmp_invoice )))>0
-- BEGIN
-- SET @xStsno='05';
-- SET @xStsdes='Gagal, Invoice belum Approve, Ada invoice yang belum Approve';
-- 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 @total_bruto decimal(24,4)
declare @total_netto decimal(24,4)
select
@total_bruto= sum(isnull(test_compare.bruto,0))
, @total_netto= sum(isnull(test_compare.netto,0))
FROM test_compare INNER JOIN
dt_ms_item ON test_compare.PARTCODE = dt_ms_item.part_no 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_invoice as xfilter
where test_compare.id =xfilter.id
)
declare @ID_HEADER bigint
,@dt_ms_tpb_in_header_h_status_id bigint
declare @dt_ms_tpb_in_barang table (
ID bigint primary key,
ms_manual_invoice_detail_id bigint
)
BEGIN TRANSACTION;
INSERT INTO dt_ms_tpb_in_header
( ALAMAT_PEMASOK
, ALAMAT_PENGIRIM
, ID_PEMASOK
, ID_PENGIRIM
, KODE_ID_PEMASOK
, KODE_ID_PENGIRIM
, NAMA_PEMASOK
, NAMA_PENGIRIM
, NAMA_TTD
, JABATAN_TTD
, KODE_NEGARA_PEMASOK
, KODE_NEGARA_PENGIRIM
, KODE_STATUS
--,KODE_PEL_BONGKAR
--,KODE_KANTOR_BONGKAR
,BRUTO --akumulasi bruto detail
,NETTO
,BIAYA_TAMBAHAN
,DISKON
,ASURANSI
,KODE_HARGA
,KODE_VALUTA
,NOMOR_AJU
,NOMOR_DAFTAR
,TANGGAL_DAFTAR
,TANGGAL_AJU
,TANGGAL_TTD
,KOTA_TTD
,WAKTU_BONGKAR
,FREIGHT
,KODE_DOKUMEN_PABEAN
,mode_source
,mapping_supplier_id
,mapping_supplier_date
,mapping_supplier_by
,PJT
,KODE_ASURANSI
,KODE_TUJUAN_PEMASUKAN
,KODE_TUJUAN_PENGIRIMAN
,NDPBM
,create_date, create_by
,modify_date, modify_by
,ms_date_business_id
,is_move_business_date
,ms_date_business_id_from
)
VALUES (
case when @KODE_DOKUMEN_PABEAN ='23' then @check_supplier_customer_address else null end--ALAMAT_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_supplier_customer_address end--ALAMAT_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_id_nomor else null end--ID_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_id_nomor end--ID_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_id_type else null end--KODE_ID_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_id_type end--KODE_ID_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_supplier_customer_name else null end--NAMA_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_supplier_customer_name end-- NAMA_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='40' then 'BUDI SANCOKO' else '' end --NAMA TTD
, case when @KODE_DOKUMEN_PABEAN ='40' then 'KUASA DIREKSI' else '' end --JABATAN TTD
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_supplier_customer_country else null end--KODE_NEGARA_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_supplier_customer_country end-- KODE_NEGARA_PENGIRIM
,'00'-- KODE_STATUS
--,@KODE_PEL_BONGKAR
--,@KODE_KANTOR_BONGKAR
,@total_bruto--BRUTO
,@total_netto--BRUTO
,0--BIAYA_TAMBAHAN
,0--DISKON
,0--ASURANSI
,@KODE_HARGA
,isnull(@KODE_VALUTA,'IDR')
,dbo.draft_car_bc_in ('INVOICE BC ' + @KODE_DOKUMEN_PABEAN ,@KODE_DOKUMEN_PABEAN)
,null--@NOMOR_DAFTAR
,cast(@NOW as date)--@TANGGAL_DAFTAR
,cast(@NOW as date)
,null--@TANGGAL_TTD
,null--@KOTA_TTD
,null--@WAKTU_BONGKAR
,0--@FREIGHT
,@KODE_DOKUMEN_PABEAN
,6--- LOCAL PURCHASE
,@check_ms_supplier_customer_id
,@now
,@data_by
,case when @ms_manual_invoice_type_id=1 then 1 else 0 end--@PJT
,null--@KODE_ASURANSI
,Null--case when @KODE_DOKUMEN_PABEAN ='23' then null when @KODE_DOKUMEN_PABEAN ='262' then null else null end--KODE_TUJUAN_PEMASUKAN
,Null--case when @KODE_DOKUMEN_PABEAN ='262' or @KODE_DOKUMEN_PABEAN ='23' then null else null end--KODE_TUJUAN_PENGIRIMAN
,@NDPBM
,@now,@data_by
,@now,@data_by
,@result_date_business_id
,@is_move_date_business
,@result_date_business_id_from
)
set @ID_HEADER=SCOPE_IDENTITY();
UPDATE test_compare
SET source_out_id = @ID_HEADER, mode_source_out = 6
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
INSERT INTO dt_ms_tpb_in_header_h_status
(ID_HEADER, status_tpb_in_id, status_info, create_date, create_by)
VALUES (@ID_HEADER, 0, '',@now,@data_by)
set @dt_ms_tpb_in_header_h_status_id=SCOPE_IDENTITY();
update dt_ms_tpb_in_header
set dt_ms_tpb_in_header_h_status_id=@dt_ms_tpb_in_header_h_status_id
where ID_HEADER=@ID_HEADER
INSERT INTO dt_ms_tpb_in_dokumen
(
SERI_DOKUMEN
,FLAG_URL_DOKUMEN
, KODE_JENIS_DOKUMEN
, NOMOR_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
ROW_NUMBER() OVER(ORDER BY inv_date,inv_no) as rn
,FLAG_URL_DOKUMEN
,KODE_JENIS_DOKUMEN
,inv_no
,inv_date
,TIPE_DOKUMEN
, URL_DOKUMEN
,ID_HEADER
,ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
--, id_source
from (
SELECT DISTINCT
'' as FLAG_URL_DOKUMEN
, '380' as KODE_JENIS_DOKUMEN
, inv_no
--,ROW_NUMBER() OVER(ORDER BY inv_date,inv_no) as rn
, inv_date
,'02' as TIPE_DOKUMEN
,'' as URL_DOKUMEN
,@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,@now as create_date
,@data_by as create_by
,@now as modify_date
,@data_by as modify_by
,1 as mode_source
--,id as id_source
FROM test_compare
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
) x where x.inv_no is not null and dbo.trim(x.inv_no) <> ''
union all
select
ROW_NUMBER() OVER(ORDER BY faktur_date,faktur_no) as rn
,FLAG_URL_DOKUMEN
,KODE_JENIS_DOKUMEN
,faktur_no
,faktur_date
,TIPE_DOKUMEN
, URL_DOKUMEN
,ID_HEADER
,ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
--, id_source
from (
SELECT DISTINCT
'' as FLAG_URL_DOKUMEN
, '388' as KODE_JENIS_DOKUMEN
, replace(replace(faktur_no,'-',''),'.','') as faktur_no
--,ROW_NUMBER() OVER(ORDER BY faktur_date,faktur_no) as rn
, faktur_date
,'02' as TIPE_DOKUMEN
,'' as URL_DOKUMEN
,@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,@now as create_date
,@data_by as create_by
,@now as modify_date
,@data_by as modify_by
,1 as mode_source
--,id as id_source
FROM test_compare
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
--) y where y.faktur_no is not null and dbo.trim(y.faktur_no) <> ''
) y
union all
select
ROW_NUMBER() OVER(ORDER BY GRDATE,surat_jalan) as rn
,FLAG_URL_DOKUMEN
,KODE_JENIS_DOKUMEN
,surat_jalan
,GRDATE
,TIPE_DOKUMEN
, URL_DOKUMEN
,ID_HEADER
,ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
--, id_source
from (
SELECT DISTINCT
'' as FLAG_URL_DOKUMEN
, '705' as KODE_JENIS_DOKUMEN
, surat_jalan
--,ROW_NUMBER() OVER(ORDER BY GRDATE,surat_jalan) as rn
, GRDATE
,'02' as TIPE_DOKUMEN
,'' as URL_DOKUMEN
,@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,@now as create_date
,@data_by as create_by
,@now as modify_date
,@data_by as modify_by
,1 as mode_source
--,id as id_source
FROM test_compare
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
) z where z.surat_jalan is not null and dbo.trim(z.surat_jalan) <> ''
INSERT INTO dt_ms_tpb_in_barang (
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)
output
inserted.ID
, inserted.id_source
into @dt_ms_tpb_in_barang
select
test_compare.NETAMOUNT --CIF
, test_compare.NETAMOUNT --FOB
, test_compare.NETAMOUNT --HARGA_INVOICE
, test_compare.NETAMOUNT* @NDPBM --CIF_RUPIAH
, case when @KODE_DOKUMEN_PABEAN ='23' then null else test_compare.NETAMOUNT* @NDPBM end --HARGA_PENYERAHAN
, test_compare.PARTPRICE --HARGA_SATUAN
, test_compare.GRQTY --JUMLAH_SATUAN
, dt_ms_item.part_no --KODE_BARANG
, null --JUMLAH_KEMASAN
, null --KODE_KEMASAN
, dt_ms_item.uom --KODE_SATUAN
, null --KONDISI_BARANG
, null --KODE_GUNA
, 14 --KATEGORI_BARANG
, '-' --MERK
, '-' --SPESIFIKASI_LAIN
, dt_ms_item.ms_item_custom_no --TIPE
, '-' --UKURAN
, test_compare.netto --NETTO
, ROW_NUMBER() OVER(ORDER BY test_compare.id) as SERI_BARANG
, dt_ms_item.part_name --URAIAN
--, dt_ms_item_custom.ms_item_custom_name --URAIAN
, '02' --KODE_STATUS
, @ID_HEADER
, '-' --dt_ms_item_custom_h_tarif.nomor_hs
, case when @KODE_DOKUMEN_PABEAN ='23' then null else 'ID' end --KODE_NEGARA_ASAL
, 6
, test_compare.id
, ''
, @now
, @data_by
, @now
, @data_by
, dt_ms_item.ms_item_id--mapping_ms_item_id
, test_compare.GRQTY --qty_pakai
, test_compare.mapping_date--mapping_date
, test_compare.mapping_by--mapping_by
FROM test_compare INNER JOIN
dt_ms_item ON test_compare.PARTCODE = dt_ms_item.part_no 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_invoice as xfilter
where test_compare.id =xfilter.id
)
MERGE dt_ms_tpb_in_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='23' then 5
else 6
end as orderx
from dt_ms_tpb_in_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 ;
declare @cur_item_ID bigint
,@cur_ms_manual_invoice_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)
DECLARE barang_in_cursor CURSOR FOR
SELECT
new_item.ID
, new_item.ms_manual_invoice_detail_id
, dt_ms_item_custom_h_tarif.tarif_bm
, dt_ms_item_custom_h_tarif.tarif_ppnbm
, dt_ms_item_custom_h_tarif.tarif_pph
, dt_ms_item_custom_h_tarif.tarif_ppn
, dt_ms_tpb_in_barang.SERI_BARANG
, test_compare.NETAMOUNT
FROM dt_ms_item_custom INNER JOIN
dt_ms_item ON dt_ms_item_custom.ms_item_custom_no = dt_ms_item.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 INNER JOIN
test_compare ON dt_ms_item.part_no= test_compare.PARTCODE INNER JOIN
--(SELECT test_compare.id, ID)
@dt_ms_tpb_in_barang AS new_item ON test_compare.id = new_item.ms_manual_invoice_detail_id INNER JOIN
dt_ms_tpb_in_barang ON new_item.ID = dt_ms_tpb_in_barang.ID
OPEN barang_in_cursor
FETCH NEXT FROM barang_in_cursor INTO @cur_item_ID
,@cur_ms_manual_invoice_detail_id
,@cur_BM
,@cur_PPNBM
,@cur_PPH
,@cur_PPN
,@cur_SERI_BARANG
,@cur_price_total
WHILE @@FETCH_STATUS = 0
BEGIN
declare @cur_price_total_RUPIAH decimal(38,4)= (@cur_price_total * @NDPBM)
declare @nilai_BM decimal(38,4)=0
if (@cur_BM>0)
set @nilai_BM=((@cur_price_total * @NDPBM) * @cur_BM )/100
if (@cur_BM>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'BM' -- JENIS_TARIF
, 2 --KODE_FASILITAS
, null --KODE_SATUAN
,1--KODE_TARIF
,((@cur_price_total * @NDPBM) * @cur_BM )/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_BM --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
)
if (@cur_PPNBM>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'PPNBM' -- JENIS_TARIF
, 5 --KODE_FASILITAS
,null --KODE_SATUAN
,null --KODE_TARIF
,((@cur_price_total_RUPIAH +@nilai_BM) * @cur_PPNBM)/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_PPNBM --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
) ;
if (@cur_PPN>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'PPN' -- JENIS_TARIF
, 5 --KODE_FASILITAS
,null --KODE_SATUAN
,null --KODE_TARIF
,((@cur_price_total_RUPIAH +@nilai_BM) * @cur_PPN)/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_PPN --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
) ;
if (@cur_PPH>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'PPH' -- JENIS_TARIF
, 5 --KODE_FASILITAS
,null --KODE_SATUAN
,null--KODE_TARIF
,((@cur_price_total_RUPIAH +@nilai_BM) * @cur_PPH)/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_PPH --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
)
FETCH NEXT FROM barang_in_cursor INTO @cur_item_ID
,@cur_ms_manual_invoice_detail_id
,@cur_BM
,@cur_PPNBM
,@cur_PPH
,@cur_PPN
,@cur_SERI_BARANG
,@cur_price_total
END
CLOSE barang_in_cursor
DEALLOCATE barang_in_cursor
UPDATE dt_ms_tpb_in_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_in_header INNER JOIN
(
SELECT ID_HEADER
, SUM(ISNULL(FOB,0)) AS FOB
, SUM(ISNULL(CIF,0)) AS CIF
, SUM(ISNULL(HARGA_INVOICE,0)) AS HARGA_INVOICE
, SUM(ISNULL(CIF_RUPIAH,0)) AS CIF_RUPIAH
, SUM(ISNULL(HARGA_PENYERAHAN,0)) AS HARGA_PENYERAHAN
, SUM(ISNULL(NETTO,0)) AS NETTO
, COUNT(*) as JUMLAH_BARANG
FROM dt_ms_tpb_in_barang
GROUP BY ID_HEADER ) AS sum_detail ON dt_ms_tpb_in_header.ID_HEADER = sum_detail.ID_HEADER
where dt_ms_tpb_in_header.ID_HEADER =@ID_HEADER
insert into dt_ms_tpb_in_kemasan (
[ID_HEADER]
,[ID_HEADER_ORI]
,[JUMLAH_KEMASAN]
,[KODE_JENIS_KEMASAN]
,[MERK_KEMASAN]
,[create_date]
,[create_by]
,[modify_date]
,[modify_by]
) select
@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,1
,'PK'
,'-'
, @now
, @data_by
, @now
, @data_by
--- OLAH INSERT BAHAN BAKU DARI SELAIN FINISH GOOD
--- CALCULASI ASAL BARANG
--@dt_ms_tpb_in_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
--exec MG_MS_BC_IN_IMPORT_FROM_GRN
--'
--1 2 json 120 2 127.0.0.1 1502 40 2 -1 3244
--'
GO
GO
CREATE procedure dbo.MG_MS_BC_IN_IMPORT_FROM_GRN
@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
,@KODE_DOKUMEN_PABEAN varchar(255)
,@ms_manual_invoice_type_id int
declare @NDPBM decimal(18,0)
,@KODE_VALUTA varchar(255)
,@KODE_HARGA varchar(255)
,@allow_move_ms_date_business bit
SELECT
@data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50)
,@allow_move_ms_date_business=isnull(tempTable.item.value('allow_move_ms_date_business[1]', 'bit'),0)
,@KODE_VALUTA=left(ltrim(rtrim(isnull(tempTable.item.value('KODE_VALUTA[1]', 'varchar(305)'),''))),255)
,@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_manual_invoice_type_id=isnull(tempTable.item.value('ms_manual_invoice_type_id[1]', 'int'),-1)
FROM @PARAMETER.nodes('param/field') tempTable(item)
if not (@KODE_DOKUMEN_PABEAN='23' or
@KODE_DOKUMEN_PABEAN='271' or
@KODE_DOKUMEN_PABEAN='40'
)
begin
set @xStsno='02';
set @xStsdes='Gagal, Type BC ' + @KODE_DOKUMEN_PABEAN + 'tidak terdaftar ' ;
goto f
end
if @KODE_VALUTA=''
begin
set @xStsno='02';
set @xStsdes='Gagal, Pilih Mata Uang ' ;
goto f
end
declare @tmp_invoice table(
id bigint primary key
)
insert into @tmp_invoice (id)
SELECT
isnull(tempTable.item.value('id[1]', 'bigint'),-1) as id
FROM @PARAMETER.nodes('param/field/lst') tempTable(item)
if (select count(*) from @tmp_invoice)=0
begin
set @xStsno='02';
set @xStsdes='Gagal, Grn harus ada' ;
goto f
end
declare @result_date_business_id bigint,
@result_date_business date,
@result_date_business_id_from bigint,
@result_date_business_from date,
@is_move_date_business bit,
@business_date date=cast(@NOW as date)
EXEC [dbo].[MG_MS_CHECK_BUSINESS_DATE_AND_AVG]
@business_date = @business_date,
@allow_move_ms_date_business = @allow_move_ms_date_business,
@result_date_business_id = @result_date_business_id OUTPUT,
@result_date_business = @result_date_business OUTPUT,
@result_date_business_id_from = @result_date_business_id_from OUTPUT,
@result_date_business_from = @result_date_business_from OUTPUT,
@is_move_date_business = @is_move_date_business OUTPUT,
@xStsno = @xStsno OUTPUT,
@xStsdes = @xStsdes OUTPUT
if not(@xStsno='00')
begin
goto f
end
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 INVOICE
SET @resultX= (
SELECT
'Grn: '+ GRNUMBER + '(' + dbo.date_to_str(GRDATE) + ')'
+', Terpakai di' + BC_TYPE +
+'CAR: ' + isnull(CAR,'') +
+'NO: ' + isnull(BC_NO,'') +
+'DATE: ' + dbo.date_to_str3(BC_DATE )
+ @pemisah
AS [text()]
FROM
vw_ms_molts_grn
where exists(
select * from @tmp_invoice as xfilter
where vw_ms_molts_grn.id =xfilter.id
)
AND (used = 1)
order by GRDATE,GRNUMBER
For XML PATH (''))
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN
SET @xStsno='17';
SET @xStsdes='Gagal , Ada Grn yang sudah terpakai :' + @pemisah+ @pemisah+ @resultX + @pemisah+ @pemisah+ ' Periksa kembali data GRN ' ;
GOto f
end
IF (select count(*)
--faktur_no, faktur_date, inv_no, inv_date
from vw_ms_molts_grn a
--WHERE (a.id IN (select id from @tmp_invoice) and (faktur_date = '1970-01-01' or inv_date = '1970-01-01'))) >0
WHERE (a.id IN (select id from @tmp_invoice) and (inv_date = '1970-01-01'))) >0
BEGIN
SET @xStsno='05';
--SET @xStsdes='Gagal, Ada No/Tgl faktur atau No/Tgl Invoice yang kosong';
SET @xStsdes='No/Tgl Invoice yang kosong';
GOto f
END
--IF (SELECT count(*) AS Expr1
-- FROM vw_ms_molts_grn a
-- WHERE (a.id IN (select id from @tmp_invoice )))>0
-- BEGIN
-- SET @xStsno='05';
-- SET @xStsdes='Gagal, Invoice belum Approve, Ada invoice yang belum Approve';
-- 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 @total_bruto decimal(24,4)
declare @total_netto decimal(24,4)
select
@total_bruto= sum(isnull(test_compare.bruto,0))
, @total_netto= sum(isnull(test_compare.netto,0))
FROM test_compare INNER JOIN
dt_ms_item ON test_compare.PARTCODE = dt_ms_item.part_no 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_invoice as xfilter
where test_compare.id =xfilter.id
)
declare @ID_HEADER bigint
,@dt_ms_tpb_in_header_h_status_id bigint
declare @dt_ms_tpb_in_barang table (
ID bigint primary key,
ms_manual_invoice_detail_id bigint
)
BEGIN TRANSACTION;
INSERT INTO dt_ms_tpb_in_header
( ALAMAT_PEMASOK
, ALAMAT_PENGIRIM
, ID_PEMASOK
, ID_PENGIRIM
, KODE_ID_PEMASOK
, KODE_ID_PENGIRIM
, NAMA_PEMASOK
, NAMA_PENGIRIM
, NAMA_TTD
, JABATAN_TTD
, KODE_NEGARA_PEMASOK
, KODE_NEGARA_PENGIRIM
, KODE_STATUS
--,KODE_PEL_BONGKAR
--,KODE_KANTOR_BONGKAR
,BRUTO --akumulasi bruto detail
,NETTO
,BIAYA_TAMBAHAN
,DISKON
,ASURANSI
,KODE_HARGA
,KODE_VALUTA
,NOMOR_AJU
,NOMOR_DAFTAR
,TANGGAL_DAFTAR
,TANGGAL_AJU
,TANGGAL_TTD
,KOTA_TTD
,WAKTU_BONGKAR
,FREIGHT
,KODE_DOKUMEN_PABEAN
,mode_source
,mapping_supplier_id
,mapping_supplier_date
,mapping_supplier_by
,PJT
,KODE_ASURANSI
,KODE_TUJUAN_PEMASUKAN
,KODE_TUJUAN_PENGIRIMAN
,NDPBM
,create_date, create_by
,modify_date, modify_by
,ms_date_business_id
,is_move_business_date
,ms_date_business_id_from
)
VALUES (
case when @KODE_DOKUMEN_PABEAN ='23' then @check_supplier_customer_address else null end--ALAMAT_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_supplier_customer_address end--ALAMAT_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_id_nomor else null end--ID_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_id_nomor end--ID_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_id_type else null end--KODE_ID_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_id_type end--KODE_ID_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_supplier_customer_name else null end--NAMA_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_supplier_customer_name end-- NAMA_PENGIRIM
, case when @KODE_DOKUMEN_PABEAN ='40' then 'BUDI SANCOKO' else '' end --NAMA TTD
, case when @KODE_DOKUMEN_PABEAN ='40' then 'KUASA DIREKSI' else '' end --JABATAN TTD
, case when @KODE_DOKUMEN_PABEAN ='23' then @check_supplier_customer_country else null end--KODE_NEGARA_PEMASOK
, case when @KODE_DOKUMEN_PABEAN ='23' then null else @check_supplier_customer_country end-- KODE_NEGARA_PENGIRIM
,'00'-- KODE_STATUS
--,@KODE_PEL_BONGKAR
--,@KODE_KANTOR_BONGKAR
,@total_bruto--BRUTO
,@total_netto--BRUTO
,0--BIAYA_TAMBAHAN
,0--DISKON
,0--ASURANSI
,@KODE_HARGA
,isnull(@KODE_VALUTA,'IDR')
,dbo.draft_car_bc_in ('INVOICE BC ' + @KODE_DOKUMEN_PABEAN ,@KODE_DOKUMEN_PABEAN)
,null--@NOMOR_DAFTAR
,cast(@NOW as date)--@TANGGAL_DAFTAR
,cast(@NOW as date)
,null--@TANGGAL_TTD
,null--@KOTA_TTD
,null--@WAKTU_BONGKAR
,0--@FREIGHT
,@KODE_DOKUMEN_PABEAN
,6--- LOCAL PURCHASE
,@check_ms_supplier_customer_id
,@now
,@data_by
,case when @ms_manual_invoice_type_id=1 then 1 else 0 end--@PJT
,null--@KODE_ASURANSI
,Null--case when @KODE_DOKUMEN_PABEAN ='23' then null when @KODE_DOKUMEN_PABEAN ='262' then null else null end--KODE_TUJUAN_PEMASUKAN
,Null--case when @KODE_DOKUMEN_PABEAN ='262' or @KODE_DOKUMEN_PABEAN ='23' then null else null end--KODE_TUJUAN_PENGIRIMAN
,@NDPBM
,@now,@data_by
,@now,@data_by
,@result_date_business_id
,@is_move_date_business
,@result_date_business_id_from
)
set @ID_HEADER=SCOPE_IDENTITY();
UPDATE test_compare
SET source_out_id = @ID_HEADER, mode_source_out = 6
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
INSERT INTO dt_ms_tpb_in_header_h_status
(ID_HEADER, status_tpb_in_id, status_info, create_date, create_by)
VALUES (@ID_HEADER, 0, '',@now,@data_by)
set @dt_ms_tpb_in_header_h_status_id=SCOPE_IDENTITY();
update dt_ms_tpb_in_header
set dt_ms_tpb_in_header_h_status_id=@dt_ms_tpb_in_header_h_status_id
where ID_HEADER=@ID_HEADER
INSERT INTO dt_ms_tpb_in_dokumen
(
SERI_DOKUMEN
,FLAG_URL_DOKUMEN
, KODE_JENIS_DOKUMEN
, NOMOR_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
ROW_NUMBER() OVER(ORDER BY inv_date,inv_no) as rn
,FLAG_URL_DOKUMEN
,KODE_JENIS_DOKUMEN
,inv_no
,inv_date
,TIPE_DOKUMEN
, URL_DOKUMEN
,ID_HEADER
,ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
--, id_source
from (
SELECT DISTINCT
'' as FLAG_URL_DOKUMEN
, '380' as KODE_JENIS_DOKUMEN
, inv_no
--,ROW_NUMBER() OVER(ORDER BY inv_date,inv_no) as rn
, inv_date
,'02' as TIPE_DOKUMEN
,'' as URL_DOKUMEN
,@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,@now as create_date
,@data_by as create_by
,@now as modify_date
,@data_by as modify_by
,1 as mode_source
--,id as id_source
FROM test_compare
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
) x where x.inv_no is not null and dbo.trim(x.inv_no) <> ''
union all
select
ROW_NUMBER() OVER(ORDER BY faktur_date,faktur_no) as rn
,FLAG_URL_DOKUMEN
,KODE_JENIS_DOKUMEN
,faktur_no
,faktur_date
,TIPE_DOKUMEN
, URL_DOKUMEN
,ID_HEADER
,ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
--, id_source
from (
SELECT DISTINCT
'' as FLAG_URL_DOKUMEN
, '388' as KODE_JENIS_DOKUMEN
, replace(replace(faktur_no,'-',''),'.','') as faktur_no
--,ROW_NUMBER() OVER(ORDER BY faktur_date,faktur_no) as rn
, faktur_date
,'02' as TIPE_DOKUMEN
,'' as URL_DOKUMEN
,@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,@now as create_date
,@data_by as create_by
,@now as modify_date
,@data_by as modify_by
,1 as mode_source
--,id as id_source
FROM test_compare
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
--) y where y.faktur_no is not null and dbo.trim(y.faktur_no) <> ''
) y
union all
select
ROW_NUMBER() OVER(ORDER BY GRDATE,surat_jalan) as rn
,FLAG_URL_DOKUMEN
,KODE_JENIS_DOKUMEN
,surat_jalan
,GRDATE
,TIPE_DOKUMEN
, URL_DOKUMEN
,ID_HEADER
,ID_HEADER_ORI
, create_date
, create_by
, modify_date
, modify_by
, mode_source
--, id_source
from (
SELECT DISTINCT
'' as FLAG_URL_DOKUMEN
, '705' as KODE_JENIS_DOKUMEN
, surat_jalan
--,ROW_NUMBER() OVER(ORDER BY GRDATE,surat_jalan) as rn
, GRDATE
,'02' as TIPE_DOKUMEN
,'' as URL_DOKUMEN
,@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,@now as create_date
,@data_by as create_by
,@now as modify_date
,@data_by as modify_by
,1 as mode_source
--,id as id_source
FROM test_compare
WHERE exists(
select * from @tmp_invoice as xfilter
where test_compare.id =xfilter.id
)
) z where z.surat_jalan is not null and dbo.trim(z.surat_jalan) <> ''
INSERT INTO dt_ms_tpb_in_barang (
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)
output
inserted.ID
, inserted.id_source
into @dt_ms_tpb_in_barang
select
test_compare.NETAMOUNT --CIF
, test_compare.NETAMOUNT --FOB
, test_compare.NETAMOUNT --HARGA_INVOICE
, test_compare.NETAMOUNT* @NDPBM --CIF_RUPIAH
, case when @KODE_DOKUMEN_PABEAN ='23' then null else test_compare.NETAMOUNT* @NDPBM end --HARGA_PENYERAHAN
, test_compare.PARTPRICE --HARGA_SATUAN
, test_compare.GRQTY --JUMLAH_SATUAN
, dt_ms_item.part_no --KODE_BARANG
, null --JUMLAH_KEMASAN
, null --KODE_KEMASAN
, dt_ms_item.uom --KODE_SATUAN
, null --KONDISI_BARANG
, null --KODE_GUNA
, 14 --KATEGORI_BARANG
, '-' --MERK
, '-' --SPESIFIKASI_LAIN
, dt_ms_item.ms_item_custom_no --TIPE
, '-' --UKURAN
, test_compare.netto --NETTO
, ROW_NUMBER() OVER(ORDER BY test_compare.id) as SERI_BARANG
, dt_ms_item.part_name --URAIAN
--, dt_ms_item_custom.ms_item_custom_name --URAIAN
, '02' --KODE_STATUS
, @ID_HEADER
, '-' --dt_ms_item_custom_h_tarif.nomor_hs
, case when @KODE_DOKUMEN_PABEAN ='23' then null else 'ID' end --KODE_NEGARA_ASAL
, 6
, test_compare.id
, ''
, @now
, @data_by
, @now
, @data_by
, dt_ms_item.ms_item_id--mapping_ms_item_id
, test_compare.GRQTY --qty_pakai
, test_compare.mapping_date--mapping_date
, test_compare.mapping_by--mapping_by
FROM test_compare INNER JOIN
dt_ms_item ON test_compare.PARTCODE = dt_ms_item.part_no 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_invoice as xfilter
where test_compare.id =xfilter.id
)
MERGE dt_ms_tpb_in_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='23' then 5
else 6
end as orderx
from dt_ms_tpb_in_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 ;
declare @cur_item_ID bigint
,@cur_ms_manual_invoice_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)
DECLARE barang_in_cursor CURSOR FOR
SELECT
new_item.ID
, new_item.ms_manual_invoice_detail_id
, dt_ms_item_custom_h_tarif.tarif_bm
, dt_ms_item_custom_h_tarif.tarif_ppnbm
, dt_ms_item_custom_h_tarif.tarif_pph
, dt_ms_item_custom_h_tarif.tarif_ppn
, dt_ms_tpb_in_barang.SERI_BARANG
, test_compare.NETAMOUNT
FROM dt_ms_item_custom INNER JOIN
dt_ms_item ON dt_ms_item_custom.ms_item_custom_no = dt_ms_item.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 INNER JOIN
test_compare ON dt_ms_item.part_no= test_compare.PARTCODE INNER JOIN
--(SELECT test_compare.id, ID)
@dt_ms_tpb_in_barang AS new_item ON test_compare.id = new_item.ms_manual_invoice_detail_id INNER JOIN
dt_ms_tpb_in_barang ON new_item.ID = dt_ms_tpb_in_barang.ID
OPEN barang_in_cursor
FETCH NEXT FROM barang_in_cursor INTO @cur_item_ID
,@cur_ms_manual_invoice_detail_id
,@cur_BM
,@cur_PPNBM
,@cur_PPH
,@cur_PPN
,@cur_SERI_BARANG
,@cur_price_total
WHILE @@FETCH_STATUS = 0
BEGIN
declare @cur_price_total_RUPIAH decimal(38,4)= (@cur_price_total * @NDPBM)
declare @nilai_BM decimal(38,4)=0
if (@cur_BM>0)
set @nilai_BM=((@cur_price_total * @NDPBM) * @cur_BM )/100
if (@cur_BM>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'BM' -- JENIS_TARIF
, 2 --KODE_FASILITAS
, null --KODE_SATUAN
,1--KODE_TARIF
,((@cur_price_total * @NDPBM) * @cur_BM )/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_BM --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
)
if (@cur_PPNBM>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'PPNBM' -- JENIS_TARIF
, 5 --KODE_FASILITAS
,null --KODE_SATUAN
,null --KODE_TARIF
,((@cur_price_total_RUPIAH +@nilai_BM) * @cur_PPNBM)/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_PPNBM --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
) ;
if (@cur_PPN>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'PPN' -- JENIS_TARIF
, 5 --KODE_FASILITAS
,null --KODE_SATUAN
,null --KODE_TARIF
,((@cur_price_total_RUPIAH +@nilai_BM) * @cur_PPN)/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_PPN --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
) ;
if (@cur_PPH>0)
INSERT INTO dt_ms_tpb_in_barang_tarif
(JENIS_TARIF
, KODE_FASILITAS
, KODE_SATUAN
, KODE_TARIF
, NILAI_BAYAR
, NILAI_FASILITAS
, NILAI_SUDAH_DILUNASI
, TARIF
, TARIF_FASILITAS
, ID_BARANG
, ID_HEADER
)
values( 'PPH' -- JENIS_TARIF
, 5 --KODE_FASILITAS
,null --KODE_SATUAN
,null--KODE_TARIF
,((@cur_price_total_RUPIAH +@nilai_BM) * @cur_PPH)/100 --NILAI_BAYAR
,0 --NILAI_FASILITAS
,0 --NILAI_SUDAH_DILUNASI
,@cur_PPH --TARIF
,100 --TARIF_FASILITAS
,@cur_item_ID --ID_BARANG
,@ID_HEADER --ID_HEADER
)
FETCH NEXT FROM barang_in_cursor INTO @cur_item_ID
,@cur_ms_manual_invoice_detail_id
,@cur_BM
,@cur_PPNBM
,@cur_PPH
,@cur_PPN
,@cur_SERI_BARANG
,@cur_price_total
END
CLOSE barang_in_cursor
DEALLOCATE barang_in_cursor
UPDATE dt_ms_tpb_in_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_in_header INNER JOIN
(
SELECT ID_HEADER
, SUM(ISNULL(FOB,0)) AS FOB
, SUM(ISNULL(CIF,0)) AS CIF
, SUM(ISNULL(HARGA_INVOICE,0)) AS HARGA_INVOICE
, SUM(ISNULL(CIF_RUPIAH,0)) AS CIF_RUPIAH
, SUM(ISNULL(HARGA_PENYERAHAN,0)) AS HARGA_PENYERAHAN
, SUM(ISNULL(NETTO,0)) AS NETTO
, COUNT(*) as JUMLAH_BARANG
FROM dt_ms_tpb_in_barang
GROUP BY ID_HEADER ) AS sum_detail ON dt_ms_tpb_in_header.ID_HEADER = sum_detail.ID_HEADER
where dt_ms_tpb_in_header.ID_HEADER =@ID_HEADER
insert into dt_ms_tpb_in_kemasan (
[ID_HEADER]
,[ID_HEADER_ORI]
,[JUMLAH_KEMASAN]
,[KODE_JENIS_KEMASAN]
,[MERK_KEMASAN]
,[create_date]
,[create_by]
,[modify_date]
,[modify_by]
) select
@ID_HEADER as ID_HEADER
, - 1 as ID_HEADER_ORI
,1
,'PK'
,'-'
, @now
, @data_by
, @now
, @data_by
--- OLAH INSERT BAHAN BAKU DARI SELAIN FINISH GOOD
--- CALCULASI ASAL BARANG
--@dt_ms_tpb_in_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
--exec MG_MS_BC_IN_IMPORT_FROM_GRN
--'
--
--'
GO
Depends On
21- dbo.MG_MS_CHECK_BUSINESS_DATE_AND_AVG
- dbo.get_rate_valuta
- dbo.dt_currencies_header
- dbo.dt_currencies
- dbo.dt_currencies_master
- dbo.date_to_str
- dbo.date_to_str3
- dbo.vw_ms_molts_grn
- dbo.dt_ms_supplier_customer
- dbo.test_compare
- dbo.dt_ms_item
- dbo.dt_ms_item_custom
- dbo.dt_ms_item_custom_h_tarif
- dbo.dt_ms_tpb_in_header
- dbo.draft_car_bc_in
- dbo.dt_ms_tpb_in_header_h_status
- dbo.dt_ms_tpb_in_dokumen
- dbo.trim
- dbo.dt_ms_tpb_in_barang
- dbo.dt_ms_tpb_in_barang_tarif
- dbo.dt_ms_tpb_in_kemasan
Used By
No items found