Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | True |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @PARAMETER | xml | -1 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create procedure dbo.EXIM_DOC_IN_EXTRACT_DATA_BC_23_DEKSTOP
@PARAMETER xml=''
with encryption
as
declare @XmlSts xml
declare @raw xml
declare @xStsnoheader varchar(2),
@xStsno varchar(2),
@xStsdes varchar(2000)
set @xStsnoheader='06'
set @xStsno='00';
set @xStsdes='success'
SET NOCOUNT ON
exec MG_CHECK_CREATE_PERIODE @xStsno output,@xStsdes output;
if not(
@xStsno ='00'
)
begin
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
goto g
end
SET NOCOUNT OFF
BEGIN TRY
BEGIN TRANSACTION;
SET NOCOUNT ON
DECLARE @NOW datetime
set @NOW =GETDATE()
declare
@id int ,
@readonly int
execUTE [dbo].[S_ALL_LOG_DETAIL]
@LOG_PARAM =@PARAMETER,
@id = @id OUTPUT,
@readonly = @readonly OUTPUT,
@no = @xStsno OUTPUT,
@des = @xStsdes OUTPUT
if @xStsno <>'00'
goto f
if @readonly=1
begin
set @xStsno='03';
set @xStsdes='Maaf , Hak aksess anda hanya readonly';
goto f
end
declare @username varchar(255) =(
SELECT TOP (1) username
FROM users
WHERE (id = @id))
declare @data_by int
,@bc_type_id int
,@aktivasi varchar(255)
,@edinum varchar(255)
,@replace_if_exists bit
,@replace_info varchar(1000)
SELECT
@aktivasi= tempTable.item.value('aktivasi[1]', 'varchar(255)') ,
@edinum= tempTable.item.value('edinum[1]', 'varchar(255)') ,
@replace_info= isnull(tempTable.item.value('replace_info[1]', 'varchar(1000)') ,''),
@replace_if_exists= isnull(tempTable.item.value('replace_if_exists[1]', 'bit'),0) ,
@bc_type_id= tempTable.item.value('bc_type_id[1]', 'int')
FROM @PARAMETER.nodes('param/field') tempTable(item)
declare @fDecimal varchar(255),
@fGroup varchar(255)
SELECT
@fDecimal= tempTable.item.value('DecimalSeparator[1]', 'varchar(255)') ,
@fGroup= tempTable.item.value('GroupSeparator[1]', 'varchar(255)')
FROM @PARAMETER.nodes('param/field/float_info') tempTable(item)
set @data_by=@id
declare @interface_module_bc_id bigint
SELECT top 1 @interface_module_bc_id= interface_module_bc_id
FROM vw_interface_module
WHERE (edinum = @edinum) AND (bc_type_id = @bc_type_id) AND (aktivasi = @aktivasi)
if @interface_module_bc_id is null
begin
set @xStsno='02';
set @xStsdes='Lakukan Setting Terlebih Dahulu';
goto f
end
--SELECT top 1
--@update_car=tempTable.item.value('rID[1]', 'varchar(30)')
-- FROM @PARAMETER.nodes('param/field/xhdata/xh') tempTable(item)
DECLARE @tmpbc23hdr table (
CAR varchar(500) null,
KdKpbc varchar(500) null,--r1
Tujuan varchar(500) null,
JnsBarang varchar(500) null,
TujuanKirim varchar(500) null,
PasokNama varchar(500) null,
PasokAlmt varchar(500) null,
PasokNeg varchar(500) null,
UsahaId varchar(500) null,
UsahaNpwp varchar(500) null,
KdTPB varchar(500) null,
Registrasi varchar(500) null,
UsahaNama varchar(500) null,
UsahaAlmt varchar(500) null,
UsahaStatus varchar(500) null,
ApiKd varchar(500) null,
ApiNo varchar(500) null,
PpjkId varchar(500) null,
PpjkNpwp varchar(500) null,
PpjkNama varchar(500) null,
PpjkAlmt varchar(500) null,
PpjkNo varchar(500) null,
PpjkTg date null,
IndId varchar(500) null,
IndNpwp varchar(500) null,
IndNama varchar(500) null,
IndAlmt varchar(500) null,
Moda varchar(500) null,
AngkutNama varchar(500) null,
AngkutNo varchar(500) null,
AngkutFl varchar(500) null,
PelBkr varchar(500) null,
PelMuat varchar(500) null,
PelTransit varchar(500) null,
BC23No varchar(500) null,
BC23Tg date null,
KdKpbcBongkar varchar(500) null,
KdKpbcAwas varchar(500) null,
DokTupKd varchar(500) null,
DokTupNo varchar(500) null,
DokTupTg date null,
PosNo varchar(500) null,
PosSub varchar(500) null,
TmpTbn varchar(500) null,
KdVal varchar(500) null,
Ndpbm float null ,
NilInv float null,
Freight float null,
BTambahan float null,
Diskon float null,
KdAss varchar(500) null,
Asuransi float null,
KdHrg varchar(500) null,
Fob float null,
Cif float null,
Bruto float null,
Netto float null,
JmCont int null,
JmBrg int null,
TglTtd date null,
KotaTtd varchar(500) null,
Namattd varchar(500) null,
ContakPerson varchar(500) null,
NoPhone varchar(500) null,
NoFax varchar(500) null,
Email varchar(500) null,
Status varchar(500) null,
Snrf varchar(500) null,
Lengkap varchar(500) null
)
insert into @tmpbc23hdr
SELECT
tempTable.item.value('rID[1]', 'varchar(30)') ,--CAR
tempTable.item.value('r1[1]', 'varchar(255)') ,--KdKpbc
tempTable.item.value('r2[1]', 'varchar(255)') ,--Tujuan
tempTable.item.value('r3[1]', 'varchar(255)') ,--JnsBarang
tempTable.item.value('r4[1]', 'varchar(255)') ,--TujuanKirim
tempTable.item.value('r5[1]', 'varchar(255)') ,--PasokNama
tempTable.item.value('r6[1]', 'varchar(255)') ,--PasokAlmt
tempTable.item.value('r7[1]', 'varchar(255)') ,--PasokNeg
tempTable.item.value('r8[1]', 'varchar(255)') ,--UsahaId
tempTable.item.value('r9[1]', 'varchar(255)') ,--UsahaNpwp
tempTable.item.value('r10[1]', 'varchar(255)') ,--KdTPB
tempTable.item.value('r11[1]', 'varchar(255)') ,--Registrasi
tempTable.item.value('r12[1]', 'varchar(255)') ,--UsahaNama
tempTable.item.value('r13[1]', 'varchar(255)') ,--UsahaAlmt
tempTable.item.value('r14[1]', 'varchar(255)') ,--UsahaStatus
tempTable.item.value('r15[1]', 'varchar(255)') ,--ApiKd
tempTable.item.value('r16[1]', 'varchar(255)') ,--ApiNo
tempTable.item.value('r17[1]', 'varchar(255)') ,--PpjkId
tempTable.item.value('r18[1]', 'varchar(255)') ,--PpjkNpwp
tempTable.item.value('r19[1]', 'varchar(255)') ,--PpjkNama
tempTable.item.value('r20[1]', 'varchar(255)') ,--PpjkAlmt
tempTable.item.value('r21[1]', 'varchar(255)') ,--PpjkNo
dbo.str_to_date (tempTable.item.value('r22[1]', 'varchar(255)')) ,--PpjkTg
tempTable.item.value('r23[1]', 'varchar(255)') ,--IndId
tempTable.item.value('r24[1]', 'varchar(255)') ,--IndNpwp
tempTable.item.value('r25[1]', 'varchar(255)') ,--IndNama
tempTable.item.value('r26[1]', 'varchar(255)') ,--IndAlmt
tempTable.item.value('r27[1]', 'varchar(255)') ,--Moda
tempTable.item.value('r28[1]', 'varchar(255)') ,--AngkutNama
tempTable.item.value('r29[1]', 'varchar(255)') ,--AngkutNo
tempTable.item.value('r30[1]', 'varchar(255)') ,--AngkutFl
tempTable.item.value('r31[1]', 'varchar(255)') ,--PelBkr
tempTable.item.value('r32[1]', 'varchar(255)') ,--PelMuat
tempTable.item.value('r33[1]', 'varchar(255)') ,--PelTransit
tempTable.item.value('r34[1]', 'varchar(255)') ,--BC23No
dbo.str_to_date (tempTable.item.value('r35[1]', 'varchar(255)')) ,--BC23Tg
tempTable.item.value('r36[1]', 'varchar(255)') ,--KdKpbcBongkar
tempTable.item.value('r37[1]', 'varchar(255)') ,--KdKpbcAwas
tempTable.item.value('r38[1]', 'varchar(255)') ,--DokTupKd
tempTable.item.value('r39[1]', 'varchar(255)') ,--DokTupNo
dbo.str_to_date (tempTable.item.value('r40[1]', 'varchar(255)')) ,--DokTupTg
tempTable.item.value('r41[1]', 'varchar(255)') ,--PosNo
tempTable.item.value('r42[1]', 'varchar(255)') ,--PosSub
tempTable.item.value('r43[1]', 'varchar(255)') ,--TmpTbn
tempTable.item.value('r44[1]', 'varchar(255)') ,--KdVal
dbo.toDBdec(tempTable.item.value('r45[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Ndpbm
dbo.toDBdec(tempTable.item.value('r46[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--NilInv
dbo.toDBdec(tempTable.item.value('r47[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Freight
dbo.toDBdec(tempTable.item.value('r48[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--BTambahan
dbo.toDBdec(tempTable.item.value('r49[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Diskon
tempTable.item.value('r50[1]', 'varchar(255)') ,--KdAss
dbo.toDBdec(tempTable.item.value('r51[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Asuransi
tempTable.item.value('r52[1]', 'varchar(255)') ,--KdHrg
dbo.toDBdec(tempTable.item.value('r53[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Fob
dbo.toDBdec(tempTable.item.value('r54[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Cif
dbo.toDBdec(tempTable.item.value('r55[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Bruto
dbo.toDBdec(tempTable.item.value('r56[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Netto
isnull(tempTable.item.value('r57[1]', 'int'),0) ,--JmCont
isnull(tempTable.item.value('r58[1]', 'int'),0) ,--JmBrg
dbo.str_to_date (tempTable.item.value('r59[1]', 'varchar(255)')) ,--TglTtd
tempTable.item.value('r60[1]', 'varchar(255)') ,--KotaTtd
tempTable.item.value('r61[1]', 'varchar(255)') ,--Namattd
tempTable.item.value('r62[1]', 'varchar(255)') ,--ContakPerson
tempTable.item.value('r63[1]', 'varchar(255)') ,--NoPhone
tempTable.item.value('r64[1]', 'varchar(255)') ,--NoFax
tempTable.item.value('r65[1]', 'varchar(255)') ,--Email
tempTable.item.value('r66[1]', 'varchar(255)') ,--Status
tempTable.item.value('r67[1]', 'varchar(255)') ,--Snrf
tempTable.item.value('r68[1]', 'varchar(255)') --Lengkap
FROM @PARAMETER.nodes('param/field/xhdata/xh') tempTable(item) ;
declare @id_bc23_hdr bigint
--select top 1 @id_bc23_hdr=id_bc23_hdr from [DB_PASI_NEW].[dbo].[tblBC23Hdr]
--where CAR =(select top 1 CAR from @tmpbc23hdr)
MERGE [DB_PASI_NEW].[dbo].[tblBC23Hdr] AS TARGET
USING @tmpbc23hdr AS SOURCE
ON (TARGET.car = SOURCE.car)
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.CAR=SOURCE.CAR,
TARGET.KdKpbc=SOURCE.KdKpbc,
TARGET.Tujuan=SOURCE.Tujuan,
TARGET.JnsBarang=SOURCE.JnsBarang,
TARGET.TujuanKirim=SOURCE.TujuanKirim,
TARGET.PasokNama=SOURCE.PasokNama,
TARGET.PasokAlmt=SOURCE.PasokAlmt,
TARGET.PasokNeg=SOURCE.PasokNeg,
TARGET.UsahaId=SOURCE.UsahaId,
TARGET.UsahaNpwp=SOURCE.UsahaNpwp,
TARGET.KdTPB=SOURCE.KdTPB,
TARGET.Registrasi=SOURCE.Registrasi,
TARGET.UsahaNama=SOURCE.UsahaNama,
TARGET.UsahaAlmt=SOURCE.UsahaAlmt,
TARGET.UsahaStatus=SOURCE.UsahaStatus,
TARGET.ApiKd=SOURCE.ApiKd,
TARGET.ApiNo=SOURCE.ApiNo,
TARGET.PpjkId=SOURCE.PpjkId,
TARGET.PpjkNpwp=SOURCE.PpjkNpwp,
TARGET.PpjkNama=SOURCE.PpjkNama,
TARGET.PpjkAlmt=SOURCE.PpjkAlmt,
TARGET.PpjkNo=SOURCE.PpjkNo,
TARGET.PpjkTg=SOURCE.PpjkTg,
TARGET.IndId=SOURCE.IndId,
TARGET.IndNpwp=SOURCE.IndNpwp,
TARGET.IndNama=SOURCE.IndNama,
TARGET.IndAlmt=SOURCE.IndAlmt,
TARGET.Moda=SOURCE.Moda,
TARGET.AngkutNama=SOURCE.AngkutNama,
TARGET.AngkutNo=SOURCE.AngkutNo,
TARGET.AngkutFl=SOURCE.AngkutFl,
TARGET.PelBkr=SOURCE.PelBkr,
TARGET.PelMuat=SOURCE.PelMuat,
TARGET.PelTransit=SOURCE.PelTransit,
TARGET.BC23No=SOURCE.BC23No,
TARGET.BC23Tg=SOURCE.BC23Tg,
TARGET.KdKpbcBongkar=SOURCE.KdKpbcBongkar,
TARGET.KdKpbcAwas=SOURCE.KdKpbcAwas,
TARGET.DokTupKd=SOURCE.DokTupKd,
TARGET.DokTupNo=SOURCE.DokTupNo,
TARGET.DokTupTg=SOURCE.DokTupTg,
TARGET.PosNo=SOURCE.PosNo,
TARGET.PosSub=SOURCE.PosSub,
TARGET.TmpTbn=SOURCE.TmpTbn,
TARGET.KdVal=SOURCE.KdVal,
TARGET.Ndpbm=SOURCE.Ndpbm,
TARGET.NilInv=SOURCE.NilInv,
TARGET.Freight=SOURCE.Freight,
TARGET.BTambahan=SOURCE.BTambahan,
TARGET.Diskon=SOURCE.Diskon,
TARGET.KdAss=SOURCE.KdAss,
TARGET.Asuransi=SOURCE.Asuransi,
TARGET.KdHrg=SOURCE.KdHrg,
TARGET.Fob=SOURCE.Fob,
TARGET.Cif=SOURCE.Cif,
TARGET.Bruto=SOURCE.Bruto,
TARGET.Netto=SOURCE.Netto,
TARGET.JmCont=SOURCE.JmCont,
TARGET.JmBrg=SOURCE.JmBrg,
TARGET.TglTtd=SOURCE.TglTtd,
TARGET.KotaTtd=SOURCE.KotaTtd,
TARGET.Namattd=SOURCE.Namattd,
TARGET.ContakPerson=SOURCE.ContakPerson,
TARGET.NoPhone=SOURCE.NoPhone,
TARGET.NoFax=SOURCE.NoFax,
TARGET.Email=SOURCE.Email,
TARGET.Status=SOURCE.Status,
TARGET.Snrf=SOURCE.Snrf,
TARGET.Lengkap=SOURCE.Lengkap,
TARGET.update_date=@NOW,
TARGET.update_by=@username
,@id_bc23_hdr=TARGET.id_bc23_hdr
WHEN NOT MATCHED BY TARGET THEN
INSERT (
CAR,
KdKpbc,
Tujuan,
JnsBarang,
TujuanKirim,
PasokNama,
PasokAlmt,
PasokNeg,
UsahaId,
UsahaNpwp,
KdTPB,
Registrasi,
UsahaNama,
UsahaAlmt,
UsahaStatus,
ApiKd,
ApiNo,
PpjkId,
PpjkNpwp,
PpjkNama,
PpjkAlmt,
PpjkNo,
PpjkTg,
IndId,
IndNpwp,
IndNama,
IndAlmt,
Moda,
AngkutNama,
AngkutNo,
AngkutFl,
PelBkr,
PelMuat,
PelTransit,
BC23No,
BC23Tg,
KdKpbcBongkar,
KdKpbcAwas,
DokTupKd,
DokTupNo,
DokTupTg,
PosNo,
PosSub,
TmpTbn,
KdVal,
Ndpbm,
NilInv,
Freight,
BTambahan,
Diskon,
KdAss,
Asuransi,
KdHrg,
Fob,
Cif,
Bruto,
Netto,
JmCont,
JmBrg,
TglTtd,
KotaTtd,
Namattd,
ContakPerson,
NoPhone,
NoFax,
Email,
Status,
Snrf,
Lengkap,
create_date,
create_by,
update_date,
update_by,
status_2
)
VALUES (
SOURCE.CAR,
SOURCE.KdKpbc,
SOURCE.Tujuan,
SOURCE.JnsBarang,
SOURCE.TujuanKirim,
SOURCE.PasokNama,
SOURCE.PasokAlmt,
SOURCE.PasokNeg,
SOURCE.UsahaId,
SOURCE.UsahaNpwp,
SOURCE.KdTPB,
SOURCE.Registrasi,
SOURCE.UsahaNama,
SOURCE.UsahaAlmt,
SOURCE.UsahaStatus,
SOURCE.ApiKd,
SOURCE.ApiNo,
SOURCE.PpjkId,
SOURCE.PpjkNpwp,
SOURCE.PpjkNama,
SOURCE.PpjkAlmt,
SOURCE.PpjkNo,
SOURCE.PpjkTg,
SOURCE.IndId,
SOURCE.IndNpwp,
SOURCE.IndNama,
SOURCE.IndAlmt,
SOURCE.Moda,
SOURCE.AngkutNama,
SOURCE.AngkutNo,
SOURCE.AngkutFl,
SOURCE.PelBkr,
SOURCE.PelMuat,
SOURCE.PelTransit,
SOURCE.BC23No,
SOURCE.BC23Tg,
SOURCE.KdKpbcBongkar,
SOURCE.KdKpbcAwas,
SOURCE.DokTupKd,
SOURCE.DokTupNo,
SOURCE.DokTupTg,
SOURCE.PosNo,
SOURCE.PosSub,
SOURCE.TmpTbn,
SOURCE.KdVal,
SOURCE.Ndpbm,
SOURCE.NilInv,
SOURCE.Freight,
SOURCE.BTambahan,
SOURCE.Diskon,
SOURCE.KdAss,
SOURCE.Asuransi,
SOURCE.KdHrg,
SOURCE.Fob,
SOURCE.Cif,
SOURCE.Bruto,
SOURCE.Netto,
SOURCE.JmCont,
SOURCE.JmBrg,
SOURCE.TglTtd,
SOURCE.KotaTtd,
SOURCE.Namattd,
SOURCE.ContakPerson,
SOURCE.NoPhone,
SOURCE.NoFax,
SOURCE.Email,
SOURCE.Status,
SOURCE.Snrf,
SOURCE.Lengkap,
@NOW,
@username,
@NOW,
@username,
'DATA PROCESSED'
);
IF @id_bc23_hdr IS NULL
BEGIN
set @id_bc23_hdr = SCOPE_IDENTITY() ;
END;
DECLARE @tmpbc23detail table (
CAR varchar(500) null,
Serial int null,
JnsBarangDtl varchar(500) null,
TujuanKirimDtl varchar(500) null,
NoHs varchar(500) null,
SeriTrp smallint null,
BrgUrai varchar(500) null,
Merk varchar(500) null,
Tipe varchar(500) null,
SpfLain varchar(500) null,
KdBrg varchar(500) null,
Penggunaan varchar(500) null,
KemasJn varchar(500) null,
KemasJm int null,
KdFasDtl varchar(500) null,
BrgAsal varchar(500) null,
DNilInv float null,
DCif float null,
KdSat varchar(500) null,
JmlSat float null,
NettoDtl float null,
SatBmJm float null,
SatCukJm float null,
DtlOk varchar(500) null
)
insert into @tmpbc23detail
SELECT
tempTable.item.value('rID[1]', 'varchar(255)') ,--CAR
tempTable.item.value('r1[1]', 'int') ,--Serial
tempTable.item.value('r2[1]', 'varchar(255)') ,--JnsBarangDtl
tempTable.item.value('r3[1]', 'varchar(255)') ,--TujuanKirimDtl
tempTable.item.value('r4[1]', 'varchar(255)') ,--NoHs
tempTable.item.value('r5[1]', 'smallint') ,--SeriTrp
tempTable.item.value('r6[1]', 'varchar(255)') ,--BrgUrai
tempTable.item.value('r7[1]', 'varchar(255)') ,--Merk
tempTable.item.value('r8[1]', 'varchar(255)') ,--Tipe
tempTable.item.value('r9[1]', 'varchar(255)') ,--SpfLain
tempTable.item.value('r10[1]', 'varchar(255)') ,--KdBrg
tempTable.item.value('r11[1]', 'varchar(255)') ,--Penggunaan
tempTable.item.value('r12[1]', 'varchar(255)') ,--KemasJn
tempTable.item.value('r13[1]', 'int') ,--KemasJm
tempTable.item.value('r14[1]', 'varchar(255)') ,--KdFasDtl
tempTable.item.value('r15[1]', 'varchar(255)') ,--BrgAsal
dbo.toDBdec(tempTable.item.value('r16[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--DNilInv
dbo.toDBdec(tempTable.item.value('r17[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--DCif
tempTable.item.value('r18[1]', 'varchar(255)') ,--KdSat
dbo.toDBdec(tempTable.item.value('r19[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--JmlSat
dbo.toDBdec(tempTable.item.value('r20[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--NettoDtl
dbo.toDBdec(tempTable.item.value('r21[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--SatBmJm
dbo.toDBdec(tempTable.item.value('r22[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--SatCukJm
tempTable.item.value('r23[1]', 'varchar(255)') --DtlOk
FROM @PARAMETER.nodes('param/field/xddata/xd') tempTable(item)
MERGE [DB_PASI_NEW].[dbo].[tblBC23Dtl] AS TARGET
USING @tmpbc23detail AS SOURCE
ON (TARGET.CAR = SOURCE.CAR and TARGET.Serial = SOURCE.Serial)
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.id_bc23_hdr=@id_bc23_hdr,
TARGET.CAR=SOURCE.CAR,
TARGET.Serial=SOURCE.Serial,
TARGET.JnsBarangDtl=SOURCE.JnsBarangDtl,
TARGET.TujuanKirimDtl=SOURCE.TujuanKirimDtl,
TARGET.NoHs=SOURCE.NoHs,
TARGET.SeriTrp=SOURCE.SeriTrp,
TARGET.BrgUrai=SOURCE.BrgUrai,
TARGET.Merk=SOURCE.Merk,
TARGET.Tipe=SOURCE.Tipe,
TARGET.SpfLain=SOURCE.SpfLain,
TARGET.KdBrg=SOURCE.KdBrg,
TARGET.Penggunaan=SOURCE.Penggunaan,
TARGET.KemasJn=SOURCE.KemasJn,
TARGET.KemasJm=SOURCE.KemasJm,
TARGET.KdFasDtl=SOURCE.KdFasDtl,
TARGET.BrgAsal=SOURCE.BrgAsal,
TARGET.DNilInv=SOURCE.DNilInv,
TARGET.DCif=SOURCE.DCif,
TARGET.KdSat=SOURCE.KdSat,
TARGET.JmlSat=SOURCE.JmlSat,
TARGET.NettoDtl=SOURCE.NettoDtl,
TARGET.SatBmJm=SOURCE.SatBmJm,
TARGET.SatCukJm=SOURCE.SatCukJm,
TARGET.DtlOk=SOURCE.DtlOk,
TARGET.update_date=@NOW,
TARGET.update_by=@username
WHEN NOT MATCHED BY TARGET THEN
INSERT (
id_bc23_hdr
, [CAR]
,[Serial]
,[JnsBarangDtl]
,[TujuanKirimDtl]
,[NoHs]
,[SeriTrp]
,[BrgUrai]
,[Merk]
,[Tipe]
,[SpfLain]
,[KdBrg]
,[Penggunaan]
,[KemasJn]
,[KemasJm]
,[KdFasDtl]
,[BrgAsal]
,[DNilInv]
,[DCif]
,[KdSat]
,[JmlSat]
,[NettoDtl]
,[SatBmJm]
,[SatCukJm]
,[DtlOk]
,create_date,
create_by,
update_date,
update_by,
[status]
) VALUES (
@id_bc23_hdr,
SOURCE.CAR,
SOURCE.Serial,
SOURCE.JnsBarangDtl,
SOURCE.TujuanKirimDtl,
SOURCE.NoHs,
SOURCE.SeriTrp,
SOURCE.BrgUrai,
SOURCE.Merk,
SOURCE.Tipe,
SOURCE.SpfLain,
SOURCE.KdBrg,
SOURCE.Penggunaan,
SOURCE.KemasJn,
SOURCE.KemasJm,
SOURCE.KdFasDtl,
SOURCE.BrgAsal,
SOURCE.DNilInv,
SOURCE.DCif,
SOURCE.KdSat,
SOURCE.JmlSat,
SOURCE.NettoDtl,
SOURCE.SatBmJm,
SOURCE.SatCukJm,
SOURCE.DtlOk,
@NOW,
@username,
@NOW,
@username,
'DATA PROCESSED'
)
--WHEN NOT MATCHED BY SOURCE THEN
--delete
;
DECLARE @tmpbc23Dok table (
CAR varchar(500) null,
DokKd varchar(500) null,
DokNo varchar(500) null,
DokTg date null
)
INSERT @tmpbc23Dok
SELECT
tempTable.item.value('rID[1]', 'varchar(255)') ,--CAR
tempTable.item.value('r1[1]', 'varchar(3)') ,--bc_dok_code
tempTable.item.value('r2[1]', 'varchar(25)') ,--bc_dok_no
dbo.str_to_date (tempTable.item.value('r3[1]', 'varchar(8)'))
FROM @PARAMETER.nodes('param/field/xndata/xn') tempTable(item)
MERGE [DB_PASI_NEW].[dbo].[tblBC23Dok] AS TARGET
USING @tmpbc23Dok AS SOURCE
ON (TARGET.car = SOURCE.car and TARGET.DokKd = SOURCE.DokKd and TARGET.DokNo = SOURCE.DokNo )
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.id_bc23_hdr=@id_bc23_hdr ,
TARGET.CAR=SOURCE.CAR,
TARGET.DokKd=SOURCE.DokKd,
TARGET.DokNo=SOURCE.DokNo,
TARGET.DokTg=SOURCE.DokTg
WHEN NOT MATCHED BY TARGET THEN
INSERT (
id_bc23_hdr
, [CAR]
,[DokKd]
,[DokNo]
,[DokTg]
)
VALUES (
@id_bc23_hdr,
SOURCE.CAR,
SOURCE.DokKd,
SOURCE.DokNo,
SOURCE.DokTg
);
f:
COMMIT TRANSACTION;
set nocount off
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION;
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.EXIM_DOC_IN_EXTRACT_DATA_BC_23_DEKSTOP
@PARAMETER xml=''
with encryption
as
declare @XmlSts xml
declare @raw xml
declare @xStsnoheader varchar(2),
@xStsno varchar(2),
@xStsdes varchar(2000)
set @xStsnoheader='06'
set @xStsno='00';
set @xStsdes='success'
SET NOCOUNT ON
exec MG_CHECK_CREATE_PERIODE @xStsno output,@xStsdes output;
if not(
@xStsno ='00'
)
begin
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
goto g
end
SET NOCOUNT OFF
BEGIN TRY
BEGIN TRANSACTION;
SET NOCOUNT ON
DECLARE @NOW datetime
set @NOW =GETDATE()
declare
@id int ,
@readonly int
execUTE [dbo].[S_ALL_LOG_DETAIL]
@LOG_PARAM =@PARAMETER,
@id = @id OUTPUT,
@readonly = @readonly OUTPUT,
@no = @xStsno OUTPUT,
@des = @xStsdes OUTPUT
if @xStsno <>'00'
goto f
if @readonly=1
begin
set @xStsno='03';
set @xStsdes='Maaf , Hak aksess anda hanya readonly';
goto f
end
declare @username varchar(255) =(
SELECT TOP (1) username
FROM users
WHERE (id = @id))
declare @data_by int
,@bc_type_id int
,@aktivasi varchar(255)
,@edinum varchar(255)
,@replace_if_exists bit
,@replace_info varchar(1000)
SELECT
@aktivasi= tempTable.item.value('aktivasi[1]', 'varchar(255)') ,
@edinum= tempTable.item.value('edinum[1]', 'varchar(255)') ,
@replace_info= isnull(tempTable.item.value('replace_info[1]', 'varchar(1000)') ,''),
@replace_if_exists= isnull(tempTable.item.value('replace_if_exists[1]', 'bit'),0) ,
@bc_type_id= tempTable.item.value('bc_type_id[1]', 'int')
FROM @PARAMETER.nodes('param/field') tempTable(item)
declare @fDecimal varchar(255),
@fGroup varchar(255)
SELECT
@fDecimal= tempTable.item.value('DecimalSeparator[1]', 'varchar(255)') ,
@fGroup= tempTable.item.value('GroupSeparator[1]', 'varchar(255)')
FROM @PARAMETER.nodes('param/field/float_info') tempTable(item)
set @data_by=@id
declare @interface_module_bc_id bigint
SELECT top 1 @interface_module_bc_id= interface_module_bc_id
FROM vw_interface_module
WHERE (edinum = @edinum) AND (bc_type_id = @bc_type_id) AND (aktivasi = @aktivasi)
if @interface_module_bc_id is null
begin
set @xStsno='02';
set @xStsdes='Lakukan Setting Terlebih Dahulu';
goto f
end
--SELECT top 1
--@update_car=tempTable.item.value('rID[1]', 'varchar(30)')
-- FROM @PARAMETER.nodes('param/field/xhdata/xh') tempTable(item)
DECLARE @tmpbc23hdr table (
CAR varchar(500) null,
KdKpbc varchar(500) null,--r1
Tujuan varchar(500) null,
JnsBarang varchar(500) null,
TujuanKirim varchar(500) null,
PasokNama varchar(500) null,
PasokAlmt varchar(500) null,
PasokNeg varchar(500) null,
UsahaId varchar(500) null,
UsahaNpwp varchar(500) null,
KdTPB varchar(500) null,
Registrasi varchar(500) null,
UsahaNama varchar(500) null,
UsahaAlmt varchar(500) null,
UsahaStatus varchar(500) null,
ApiKd varchar(500) null,
ApiNo varchar(500) null,
PpjkId varchar(500) null,
PpjkNpwp varchar(500) null,
PpjkNama varchar(500) null,
PpjkAlmt varchar(500) null,
PpjkNo varchar(500) null,
PpjkTg date null,
IndId varchar(500) null,
IndNpwp varchar(500) null,
IndNama varchar(500) null,
IndAlmt varchar(500) null,
Moda varchar(500) null,
AngkutNama varchar(500) null,
AngkutNo varchar(500) null,
AngkutFl varchar(500) null,
PelBkr varchar(500) null,
PelMuat varchar(500) null,
PelTransit varchar(500) null,
BC23No varchar(500) null,
BC23Tg date null,
KdKpbcBongkar varchar(500) null,
KdKpbcAwas varchar(500) null,
DokTupKd varchar(500) null,
DokTupNo varchar(500) null,
DokTupTg date null,
PosNo varchar(500) null,
PosSub varchar(500) null,
TmpTbn varchar(500) null,
KdVal varchar(500) null,
Ndpbm float null ,
NilInv float null,
Freight float null,
BTambahan float null,
Diskon float null,
KdAss varchar(500) null,
Asuransi float null,
KdHrg varchar(500) null,
Fob float null,
Cif float null,
Bruto float null,
Netto float null,
JmCont int null,
JmBrg int null,
TglTtd date null,
KotaTtd varchar(500) null,
Namattd varchar(500) null,
ContakPerson varchar(500) null,
NoPhone varchar(500) null,
NoFax varchar(500) null,
Email varchar(500) null,
Status varchar(500) null,
Snrf varchar(500) null,
Lengkap varchar(500) null
)
insert into @tmpbc23hdr
SELECT
tempTable.item.value('rID[1]', 'varchar(30)') ,--CAR
tempTable.item.value('r1[1]', 'varchar(255)') ,--KdKpbc
tempTable.item.value('r2[1]', 'varchar(255)') ,--Tujuan
tempTable.item.value('r3[1]', 'varchar(255)') ,--JnsBarang
tempTable.item.value('r4[1]', 'varchar(255)') ,--TujuanKirim
tempTable.item.value('r5[1]', 'varchar(255)') ,--PasokNama
tempTable.item.value('r6[1]', 'varchar(255)') ,--PasokAlmt
tempTable.item.value('r7[1]', 'varchar(255)') ,--PasokNeg
tempTable.item.value('r8[1]', 'varchar(255)') ,--UsahaId
tempTable.item.value('r9[1]', 'varchar(255)') ,--UsahaNpwp
tempTable.item.value('r10[1]', 'varchar(255)') ,--KdTPB
tempTable.item.value('r11[1]', 'varchar(255)') ,--Registrasi
tempTable.item.value('r12[1]', 'varchar(255)') ,--UsahaNama
tempTable.item.value('r13[1]', 'varchar(255)') ,--UsahaAlmt
tempTable.item.value('r14[1]', 'varchar(255)') ,--UsahaStatus
tempTable.item.value('r15[1]', 'varchar(255)') ,--ApiKd
tempTable.item.value('r16[1]', 'varchar(255)') ,--ApiNo
tempTable.item.value('r17[1]', 'varchar(255)') ,--PpjkId
tempTable.item.value('r18[1]', 'varchar(255)') ,--PpjkNpwp
tempTable.item.value('r19[1]', 'varchar(255)') ,--PpjkNama
tempTable.item.value('r20[1]', 'varchar(255)') ,--PpjkAlmt
tempTable.item.value('r21[1]', 'varchar(255)') ,--PpjkNo
dbo.str_to_date (tempTable.item.value('r22[1]', 'varchar(255)')) ,--PpjkTg
tempTable.item.value('r23[1]', 'varchar(255)') ,--IndId
tempTable.item.value('r24[1]', 'varchar(255)') ,--IndNpwp
tempTable.item.value('r25[1]', 'varchar(255)') ,--IndNama
tempTable.item.value('r26[1]', 'varchar(255)') ,--IndAlmt
tempTable.item.value('r27[1]', 'varchar(255)') ,--Moda
tempTable.item.value('r28[1]', 'varchar(255)') ,--AngkutNama
tempTable.item.value('r29[1]', 'varchar(255)') ,--AngkutNo
tempTable.item.value('r30[1]', 'varchar(255)') ,--AngkutFl
tempTable.item.value('r31[1]', 'varchar(255)') ,--PelBkr
tempTable.item.value('r32[1]', 'varchar(255)') ,--PelMuat
tempTable.item.value('r33[1]', 'varchar(255)') ,--PelTransit
tempTable.item.value('r34[1]', 'varchar(255)') ,--BC23No
dbo.str_to_date (tempTable.item.value('r35[1]', 'varchar(255)')) ,--BC23Tg
tempTable.item.value('r36[1]', 'varchar(255)') ,--KdKpbcBongkar
tempTable.item.value('r37[1]', 'varchar(255)') ,--KdKpbcAwas
tempTable.item.value('r38[1]', 'varchar(255)') ,--DokTupKd
tempTable.item.value('r39[1]', 'varchar(255)') ,--DokTupNo
dbo.str_to_date (tempTable.item.value('r40[1]', 'varchar(255)')) ,--DokTupTg
tempTable.item.value('r41[1]', 'varchar(255)') ,--PosNo
tempTable.item.value('r42[1]', 'varchar(255)') ,--PosSub
tempTable.item.value('r43[1]', 'varchar(255)') ,--TmpTbn
tempTable.item.value('r44[1]', 'varchar(255)') ,--KdVal
dbo.toDBdec(tempTable.item.value('r45[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Ndpbm
dbo.toDBdec(tempTable.item.value('r46[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--NilInv
dbo.toDBdec(tempTable.item.value('r47[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Freight
dbo.toDBdec(tempTable.item.value('r48[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--BTambahan
dbo.toDBdec(tempTable.item.value('r49[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Diskon
tempTable.item.value('r50[1]', 'varchar(255)') ,--KdAss
dbo.toDBdec(tempTable.item.value('r51[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Asuransi
tempTable.item.value('r52[1]', 'varchar(255)') ,--KdHrg
dbo.toDBdec(tempTable.item.value('r53[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Fob
dbo.toDBdec(tempTable.item.value('r54[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Cif
dbo.toDBdec(tempTable.item.value('r55[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Bruto
dbo.toDBdec(tempTable.item.value('r56[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--Netto
isnull(tempTable.item.value('r57[1]', 'int'),0) ,--JmCont
isnull(tempTable.item.value('r58[1]', 'int'),0) ,--JmBrg
dbo.str_to_date (tempTable.item.value('r59[1]', 'varchar(255)')) ,--TglTtd
tempTable.item.value('r60[1]', 'varchar(255)') ,--KotaTtd
tempTable.item.value('r61[1]', 'varchar(255)') ,--Namattd
tempTable.item.value('r62[1]', 'varchar(255)') ,--ContakPerson
tempTable.item.value('r63[1]', 'varchar(255)') ,--NoPhone
tempTable.item.value('r64[1]', 'varchar(255)') ,--NoFax
tempTable.item.value('r65[1]', 'varchar(255)') ,--Email
tempTable.item.value('r66[1]', 'varchar(255)') ,--Status
tempTable.item.value('r67[1]', 'varchar(255)') ,--Snrf
tempTable.item.value('r68[1]', 'varchar(255)') --Lengkap
FROM @PARAMETER.nodes('param/field/xhdata/xh') tempTable(item) ;
declare @id_bc23_hdr bigint
--select top 1 @id_bc23_hdr=id_bc23_hdr from [DB_PASI_NEW].[dbo].[tblBC23Hdr]
--where CAR =(select top 1 CAR from @tmpbc23hdr)
MERGE [DB_PASI_NEW].[dbo].[tblBC23Hdr] AS TARGET
USING @tmpbc23hdr AS SOURCE
ON (TARGET.car = SOURCE.car)
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.CAR=SOURCE.CAR,
TARGET.KdKpbc=SOURCE.KdKpbc,
TARGET.Tujuan=SOURCE.Tujuan,
TARGET.JnsBarang=SOURCE.JnsBarang,
TARGET.TujuanKirim=SOURCE.TujuanKirim,
TARGET.PasokNama=SOURCE.PasokNama,
TARGET.PasokAlmt=SOURCE.PasokAlmt,
TARGET.PasokNeg=SOURCE.PasokNeg,
TARGET.UsahaId=SOURCE.UsahaId,
TARGET.UsahaNpwp=SOURCE.UsahaNpwp,
TARGET.KdTPB=SOURCE.KdTPB,
TARGET.Registrasi=SOURCE.Registrasi,
TARGET.UsahaNama=SOURCE.UsahaNama,
TARGET.UsahaAlmt=SOURCE.UsahaAlmt,
TARGET.UsahaStatus=SOURCE.UsahaStatus,
TARGET.ApiKd=SOURCE.ApiKd,
TARGET.ApiNo=SOURCE.ApiNo,
TARGET.PpjkId=SOURCE.PpjkId,
TARGET.PpjkNpwp=SOURCE.PpjkNpwp,
TARGET.PpjkNama=SOURCE.PpjkNama,
TARGET.PpjkAlmt=SOURCE.PpjkAlmt,
TARGET.PpjkNo=SOURCE.PpjkNo,
TARGET.PpjkTg=SOURCE.PpjkTg,
TARGET.IndId=SOURCE.IndId,
TARGET.IndNpwp=SOURCE.IndNpwp,
TARGET.IndNama=SOURCE.IndNama,
TARGET.IndAlmt=SOURCE.IndAlmt,
TARGET.Moda=SOURCE.Moda,
TARGET.AngkutNama=SOURCE.AngkutNama,
TARGET.AngkutNo=SOURCE.AngkutNo,
TARGET.AngkutFl=SOURCE.AngkutFl,
TARGET.PelBkr=SOURCE.PelBkr,
TARGET.PelMuat=SOURCE.PelMuat,
TARGET.PelTransit=SOURCE.PelTransit,
TARGET.BC23No=SOURCE.BC23No,
TARGET.BC23Tg=SOURCE.BC23Tg,
TARGET.KdKpbcBongkar=SOURCE.KdKpbcBongkar,
TARGET.KdKpbcAwas=SOURCE.KdKpbcAwas,
TARGET.DokTupKd=SOURCE.DokTupKd,
TARGET.DokTupNo=SOURCE.DokTupNo,
TARGET.DokTupTg=SOURCE.DokTupTg,
TARGET.PosNo=SOURCE.PosNo,
TARGET.PosSub=SOURCE.PosSub,
TARGET.TmpTbn=SOURCE.TmpTbn,
TARGET.KdVal=SOURCE.KdVal,
TARGET.Ndpbm=SOURCE.Ndpbm,
TARGET.NilInv=SOURCE.NilInv,
TARGET.Freight=SOURCE.Freight,
TARGET.BTambahan=SOURCE.BTambahan,
TARGET.Diskon=SOURCE.Diskon,
TARGET.KdAss=SOURCE.KdAss,
TARGET.Asuransi=SOURCE.Asuransi,
TARGET.KdHrg=SOURCE.KdHrg,
TARGET.Fob=SOURCE.Fob,
TARGET.Cif=SOURCE.Cif,
TARGET.Bruto=SOURCE.Bruto,
TARGET.Netto=SOURCE.Netto,
TARGET.JmCont=SOURCE.JmCont,
TARGET.JmBrg=SOURCE.JmBrg,
TARGET.TglTtd=SOURCE.TglTtd,
TARGET.KotaTtd=SOURCE.KotaTtd,
TARGET.Namattd=SOURCE.Namattd,
TARGET.ContakPerson=SOURCE.ContakPerson,
TARGET.NoPhone=SOURCE.NoPhone,
TARGET.NoFax=SOURCE.NoFax,
TARGET.Email=SOURCE.Email,
TARGET.Status=SOURCE.Status,
TARGET.Snrf=SOURCE.Snrf,
TARGET.Lengkap=SOURCE.Lengkap,
TARGET.update_date=@NOW,
TARGET.update_by=@username
,@id_bc23_hdr=TARGET.id_bc23_hdr
WHEN NOT MATCHED BY TARGET THEN
INSERT (
CAR,
KdKpbc,
Tujuan,
JnsBarang,
TujuanKirim,
PasokNama,
PasokAlmt,
PasokNeg,
UsahaId,
UsahaNpwp,
KdTPB,
Registrasi,
UsahaNama,
UsahaAlmt,
UsahaStatus,
ApiKd,
ApiNo,
PpjkId,
PpjkNpwp,
PpjkNama,
PpjkAlmt,
PpjkNo,
PpjkTg,
IndId,
IndNpwp,
IndNama,
IndAlmt,
Moda,
AngkutNama,
AngkutNo,
AngkutFl,
PelBkr,
PelMuat,
PelTransit,
BC23No,
BC23Tg,
KdKpbcBongkar,
KdKpbcAwas,
DokTupKd,
DokTupNo,
DokTupTg,
PosNo,
PosSub,
TmpTbn,
KdVal,
Ndpbm,
NilInv,
Freight,
BTambahan,
Diskon,
KdAss,
Asuransi,
KdHrg,
Fob,
Cif,
Bruto,
Netto,
JmCont,
JmBrg,
TglTtd,
KotaTtd,
Namattd,
ContakPerson,
NoPhone,
NoFax,
Email,
Status,
Snrf,
Lengkap,
create_date,
create_by,
update_date,
update_by,
status_2
)
VALUES (
SOURCE.CAR,
SOURCE.KdKpbc,
SOURCE.Tujuan,
SOURCE.JnsBarang,
SOURCE.TujuanKirim,
SOURCE.PasokNama,
SOURCE.PasokAlmt,
SOURCE.PasokNeg,
SOURCE.UsahaId,
SOURCE.UsahaNpwp,
SOURCE.KdTPB,
SOURCE.Registrasi,
SOURCE.UsahaNama,
SOURCE.UsahaAlmt,
SOURCE.UsahaStatus,
SOURCE.ApiKd,
SOURCE.ApiNo,
SOURCE.PpjkId,
SOURCE.PpjkNpwp,
SOURCE.PpjkNama,
SOURCE.PpjkAlmt,
SOURCE.PpjkNo,
SOURCE.PpjkTg,
SOURCE.IndId,
SOURCE.IndNpwp,
SOURCE.IndNama,
SOURCE.IndAlmt,
SOURCE.Moda,
SOURCE.AngkutNama,
SOURCE.AngkutNo,
SOURCE.AngkutFl,
SOURCE.PelBkr,
SOURCE.PelMuat,
SOURCE.PelTransit,
SOURCE.BC23No,
SOURCE.BC23Tg,
SOURCE.KdKpbcBongkar,
SOURCE.KdKpbcAwas,
SOURCE.DokTupKd,
SOURCE.DokTupNo,
SOURCE.DokTupTg,
SOURCE.PosNo,
SOURCE.PosSub,
SOURCE.TmpTbn,
SOURCE.KdVal,
SOURCE.Ndpbm,
SOURCE.NilInv,
SOURCE.Freight,
SOURCE.BTambahan,
SOURCE.Diskon,
SOURCE.KdAss,
SOURCE.Asuransi,
SOURCE.KdHrg,
SOURCE.Fob,
SOURCE.Cif,
SOURCE.Bruto,
SOURCE.Netto,
SOURCE.JmCont,
SOURCE.JmBrg,
SOURCE.TglTtd,
SOURCE.KotaTtd,
SOURCE.Namattd,
SOURCE.ContakPerson,
SOURCE.NoPhone,
SOURCE.NoFax,
SOURCE.Email,
SOURCE.Status,
SOURCE.Snrf,
SOURCE.Lengkap,
@NOW,
@username,
@NOW,
@username,
'DATA PROCESSED'
);
IF @id_bc23_hdr IS NULL
BEGIN
set @id_bc23_hdr = SCOPE_IDENTITY() ;
END;
DECLARE @tmpbc23detail table (
CAR varchar(500) null,
Serial int null,
JnsBarangDtl varchar(500) null,
TujuanKirimDtl varchar(500) null,
NoHs varchar(500) null,
SeriTrp smallint null,
BrgUrai varchar(500) null,
Merk varchar(500) null,
Tipe varchar(500) null,
SpfLain varchar(500) null,
KdBrg varchar(500) null,
Penggunaan varchar(500) null,
KemasJn varchar(500) null,
KemasJm int null,
KdFasDtl varchar(500) null,
BrgAsal varchar(500) null,
DNilInv float null,
DCif float null,
KdSat varchar(500) null,
JmlSat float null,
NettoDtl float null,
SatBmJm float null,
SatCukJm float null,
DtlOk varchar(500) null
)
insert into @tmpbc23detail
SELECT
tempTable.item.value('rID[1]', 'varchar(255)') ,--CAR
tempTable.item.value('r1[1]', 'int') ,--Serial
tempTable.item.value('r2[1]', 'varchar(255)') ,--JnsBarangDtl
tempTable.item.value('r3[1]', 'varchar(255)') ,--TujuanKirimDtl
tempTable.item.value('r4[1]', 'varchar(255)') ,--NoHs
tempTable.item.value('r5[1]', 'smallint') ,--SeriTrp
tempTable.item.value('r6[1]', 'varchar(255)') ,--BrgUrai
tempTable.item.value('r7[1]', 'varchar(255)') ,--Merk
tempTable.item.value('r8[1]', 'varchar(255)') ,--Tipe
tempTable.item.value('r9[1]', 'varchar(255)') ,--SpfLain
tempTable.item.value('r10[1]', 'varchar(255)') ,--KdBrg
tempTable.item.value('r11[1]', 'varchar(255)') ,--Penggunaan
tempTable.item.value('r12[1]', 'varchar(255)') ,--KemasJn
tempTable.item.value('r13[1]', 'int') ,--KemasJm
tempTable.item.value('r14[1]', 'varchar(255)') ,--KdFasDtl
tempTable.item.value('r15[1]', 'varchar(255)') ,--BrgAsal
dbo.toDBdec(tempTable.item.value('r16[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--DNilInv
dbo.toDBdec(tempTable.item.value('r17[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--DCif
tempTable.item.value('r18[1]', 'varchar(255)') ,--KdSat
dbo.toDBdec(tempTable.item.value('r19[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--JmlSat
dbo.toDBdec(tempTable.item.value('r20[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--NettoDtl
dbo.toDBdec(tempTable.item.value('r21[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--SatBmJm
dbo.toDBdec(tempTable.item.value('r22[1]', 'varchar(255)'),@fGroup,@fDecimal) ,--SatCukJm
tempTable.item.value('r23[1]', 'varchar(255)') --DtlOk
FROM @PARAMETER.nodes('param/field/xddata/xd') tempTable(item)
MERGE [DB_PASI_NEW].[dbo].[tblBC23Dtl] AS TARGET
USING @tmpbc23detail AS SOURCE
ON (TARGET.CAR = SOURCE.CAR and TARGET.Serial = SOURCE.Serial)
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.id_bc23_hdr=@id_bc23_hdr,
TARGET.CAR=SOURCE.CAR,
TARGET.Serial=SOURCE.Serial,
TARGET.JnsBarangDtl=SOURCE.JnsBarangDtl,
TARGET.TujuanKirimDtl=SOURCE.TujuanKirimDtl,
TARGET.NoHs=SOURCE.NoHs,
TARGET.SeriTrp=SOURCE.SeriTrp,
TARGET.BrgUrai=SOURCE.BrgUrai,
TARGET.Merk=SOURCE.Merk,
TARGET.Tipe=SOURCE.Tipe,
TARGET.SpfLain=SOURCE.SpfLain,
TARGET.KdBrg=SOURCE.KdBrg,
TARGET.Penggunaan=SOURCE.Penggunaan,
TARGET.KemasJn=SOURCE.KemasJn,
TARGET.KemasJm=SOURCE.KemasJm,
TARGET.KdFasDtl=SOURCE.KdFasDtl,
TARGET.BrgAsal=SOURCE.BrgAsal,
TARGET.DNilInv=SOURCE.DNilInv,
TARGET.DCif=SOURCE.DCif,
TARGET.KdSat=SOURCE.KdSat,
TARGET.JmlSat=SOURCE.JmlSat,
TARGET.NettoDtl=SOURCE.NettoDtl,
TARGET.SatBmJm=SOURCE.SatBmJm,
TARGET.SatCukJm=SOURCE.SatCukJm,
TARGET.DtlOk=SOURCE.DtlOk,
TARGET.update_date=@NOW,
TARGET.update_by=@username
WHEN NOT MATCHED BY TARGET THEN
INSERT (
id_bc23_hdr
, [CAR]
,[Serial]
,[JnsBarangDtl]
,[TujuanKirimDtl]
,[NoHs]
,[SeriTrp]
,[BrgUrai]
,[Merk]
,[Tipe]
,[SpfLain]
,[KdBrg]
,[Penggunaan]
,[KemasJn]
,[KemasJm]
,[KdFasDtl]
,[BrgAsal]
,[DNilInv]
,[DCif]
,[KdSat]
,[JmlSat]
,[NettoDtl]
,[SatBmJm]
,[SatCukJm]
,[DtlOk]
,create_date,
create_by,
update_date,
update_by,
[status]
) VALUES (
@id_bc23_hdr,
SOURCE.CAR,
SOURCE.Serial,
SOURCE.JnsBarangDtl,
SOURCE.TujuanKirimDtl,
SOURCE.NoHs,
SOURCE.SeriTrp,
SOURCE.BrgUrai,
SOURCE.Merk,
SOURCE.Tipe,
SOURCE.SpfLain,
SOURCE.KdBrg,
SOURCE.Penggunaan,
SOURCE.KemasJn,
SOURCE.KemasJm,
SOURCE.KdFasDtl,
SOURCE.BrgAsal,
SOURCE.DNilInv,
SOURCE.DCif,
SOURCE.KdSat,
SOURCE.JmlSat,
SOURCE.NettoDtl,
SOURCE.SatBmJm,
SOURCE.SatCukJm,
SOURCE.DtlOk,
@NOW,
@username,
@NOW,
@username,
'DATA PROCESSED'
)
--WHEN NOT MATCHED BY SOURCE THEN
--delete
;
DECLARE @tmpbc23Dok table (
CAR varchar(500) null,
DokKd varchar(500) null,
DokNo varchar(500) null,
DokTg date null
)
INSERT @tmpbc23Dok
SELECT
tempTable.item.value('rID[1]', 'varchar(255)') ,--CAR
tempTable.item.value('r1[1]', 'varchar(3)') ,--bc_dok_code
tempTable.item.value('r2[1]', 'varchar(25)') ,--bc_dok_no
dbo.str_to_date (tempTable.item.value('r3[1]', 'varchar(8)'))
FROM @PARAMETER.nodes('param/field/xndata/xn') tempTable(item)
MERGE [DB_PASI_NEW].[dbo].[tblBC23Dok] AS TARGET
USING @tmpbc23Dok AS SOURCE
ON (TARGET.car = SOURCE.car and TARGET.DokKd = SOURCE.DokKd and TARGET.DokNo = SOURCE.DokNo )
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.id_bc23_hdr=@id_bc23_hdr ,
TARGET.CAR=SOURCE.CAR,
TARGET.DokKd=SOURCE.DokKd,
TARGET.DokNo=SOURCE.DokNo,
TARGET.DokTg=SOURCE.DokTg
WHEN NOT MATCHED BY TARGET THEN
INSERT (
id_bc23_hdr
, [CAR]
,[DokKd]
,[DokNo]
,[DokTg]
)
VALUES (
@id_bc23_hdr,
SOURCE.CAR,
SOURCE.DokKd,
SOURCE.DokNo,
SOURCE.DokTg
);
f:
COMMIT TRANSACTION;
set nocount off
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION;
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
6Used By
No items found