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_OUT_EXTRACT_DATA_BC30_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='sukses'
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 @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
DECLARE @tblPebHdr table (
[CAR] [varchar](200) NOT NULL PRIMARY KEY,
[JnEks] [varchar](200) NULL,
[KatEks] [varchar](200) NULL,
[JNPEB] [varchar](200) NULL,
[JNEKSOld] [varchar](200) NULL,
[JNLAIN] [varchar](200) NULL,
[JNDAG] [varchar](200) NULL,
[JNBYR] [varchar](200) NULL,
[IDEKS] [varchar](200) NULL,
[NPWPEKS] [varchar](200) NULL,
[NamaEks] [varchar](200) NULL,
[AlmtEks] [varchar](200) NULL,
[NoSiup] [varchar](200) NULL,
[TgSiup] [varchar](200) NULL,
[JnPerus] [varchar](200) NULL,
[NoTdp] [varchar](200) NULL,
[TgTdp] [varchar](200) NULL,
[Niper] [varchar](200) NULL,
[StatusH] [varchar](200) NULL,
[IdQq] [varchar](200) NULL,
[NpwpQq] [varchar](200) NULL,
[NamaQq] [varchar](200) NULL,
[AlmtQq] [varchar](200) NULL,
[NiperQq] [varchar](200) NULL,
[NAMABELI] [varchar](200) NULL,
[ALMTBELI] [varchar](200) NULL,
[NEGBELI] [varchar](200) NULL,
[IDPPJK] [varchar](200) NULL,
[NPWPPPJK] [varchar](200) NULL,
[NamaPpjk] [varchar](200) NULL,
[AlmtPpjk] [varchar](200) NULL,
[NoPpjk] [varchar](200) NULL,
[TgPpjk] [varchar](200) NULL,
[MODA] [varchar](200) NULL,
[TGEKS] [varchar](200) NULL,
[CARRIER] [varchar](200) NULL,
[VOY] [varchar](200) NULL,
[NOSHIPORD] [varchar](200) NULL,
[TGSHIPORD] [varchar](200) NULL,
[PELMUAT] [varchar](200) NULL,
[PELBONGKAR] [varchar](200) NULL,
[PELTRANSIT] [varchar](200) NULL,
[NOINV] [varchar](200) NULL,
[TGINV] [varchar](200) NULL,
[NOLPSE] [varchar](200) NULL,
[TGLPSE] [varchar](200) NULL,
[PROPBRG] [varchar](200) NULL,
[NEGTUJU] [varchar](200) NULL,
[FLSIE] [varchar](200) NULL,
[FLKARANT] [varchar](200) NULL,
[FLSM] [varchar](200) NULL,
[FLIZLAIN] [varchar](200) NULL,
[DELIVERY] [varchar](200) NULL,
[KDVAL] [varchar](200) NULL,
[KdHrg] [varchar](200) NULL,
[NilInv] [float] NULL,
[FREIGHT] [float] NULL,
[ASURANSI] [float] NULL,
[KdAss] [varchar](200) NULL,
[FOB] [float] NULL,
[MERK] [varchar](200) NULL,
[JMCONT] [float] NULL,
[BRUTO] [float] NULL,
[NETTO] [float] NULL,
[JMBRG] [float] NULL,
[JMBRGCTH] [float] NULL,
[JMBRGGAB] [float] NULL,
[JMBRGPJT] [float] NULL,
[NODAFT] [varchar](200) NULL,
[TGDAFT] [date] NULL,
[KDKTR] [varchar](200) NULL,
[KTRGATE] [varchar](200) NULL,
[NILPE] [float] NULL,
[KDBANK] [varchar](200) NULL,
[LOKBANK] [varchar](200) NULL,
[NOSSP] [varchar](200) NULL,
[TGSSP] [date] NULL,
[JNTPB] [varchar](200) NULL,
[LOKTPB] [varchar](200) NULL,
[KDLOKBRG] [varchar](200) NULL,
[KDKTRMUAT] [varchar](200) NULL,
[KDKTRPRIKS] [varchar](200) NULL,
[TGSIAP] [date] NULL,
[WKSIAP] [varchar](200) NULL,
[ASALDATA] [varchar](200) NULL,
[TPNAME] [varchar](20) NULL,
[PEBOK] [varchar](200) NULL,
[LENGKAP] [varchar](200) NULL,
[JNPARTOF] [varchar](200) NULL,
[BENDERA] [varchar](200) NULL,
[TTDPEB] [varchar](200) NULL,
[STATUS] [varchar](200) NULL,
[SNRF] [varchar](200) NULL,
[NOBCF] [varchar](200) NULL,
[TGBCF] [date] NULL,
[SERIBCF] [float] NULL,
[NOMOREDI] [varchar](200) NULL,
[KMDT] [varchar](200) NULL,
[VOLUME] [float] NULL,
[KDHANGGAR] [varchar](200) NULL,
[KONS] [varchar](200) NULL,
[JMLKMSPEB] [float] NULL,
[PELMUATEKS] [varchar](200) NULL,
[NILKURS] [float] NULL,
[PNBP] [float] NULL,
[NOBC11] [varchar](200) NULL,
[TGBC11] [date] NULL,
[POSBC11] [varchar](200) NULL,
[SUBPOSBC11] [varchar](200) NULL)
insert into @tblPebHdr
SELECT
tempTable.item.value('rID[1]', 'varchar(200)') ,--[CAR]
tempTable.item.value('r1[1]', 'varchar(200)') ,--[JnEks]
tempTable.item.value('r2[1]', 'varchar(200)') ,--[KatEks]
tempTable.item.value('r3[1]', 'varchar(200)') ,--[JNPEB]
tempTable.item.value('r4[1]', 'varchar(200)') ,--[JNEKSOld]
tempTable.item.value('r5[1]', 'varchar(200)') ,--[JNLAIN]
tempTable.item.value('r6[1]', 'varchar(200)') ,--[JNDAG]
tempTable.item.value('r7[1]', 'varchar(200)') ,--[JNBYR]
tempTable.item.value('r8[1]', 'varchar(200)') ,--[IDEKS]
tempTable.item.value('r9[1]', 'varchar(200)') ,--[NPWPEKS]
tempTable.item.value('r10[1]', 'varchar(200)') ,--[NamaEks]
tempTable.item.value('r11[1]', 'varchar(200)') ,--[AlmtEks]
tempTable.item.value('r12[1]', 'varchar(200)') ,--[NoSiup]
tempTable.item.value('r13[1]', 'varchar(200)') ,--[TgSiup]
tempTable.item.value('r14[1]', 'varchar(200)') ,--[JnPerus]
tempTable.item.value('r15[1]', 'varchar(200)') ,--[NoTdp]
tempTable.item.value('r16[1]', 'varchar(200)') ,--[TgTdp]
tempTable.item.value('r17[1]', 'varchar(200)') ,--[Niper]
tempTable.item.value('r18[1]', 'varchar(200)') ,--[StatusH]
tempTable.item.value('r19[1]', 'varchar(200)') ,--[IdQq]
tempTable.item.value('r20[1]', 'varchar(200)') ,--[NpwpQq]
tempTable.item.value('r21[1]', 'varchar(200)') ,--[NamaQq]
tempTable.item.value('r22[1]', 'varchar(200)') ,--[AlmtQq]
tempTable.item.value('r23[1]', 'varchar(200)') ,--[NiperQq]
tempTable.item.value('r24[1]', 'varchar(200)') ,--[NAMABELI]
tempTable.item.value('r25[1]', 'varchar(200)') ,--[ALMTBELI]
tempTable.item.value('r26[1]', 'varchar(200)') ,--[NEGBELI]
tempTable.item.value('r27[1]', 'varchar(200)') ,--[IDPPJK]
tempTable.item.value('r28[1]', 'varchar(200)') ,--[NPWPPPJK]
tempTable.item.value('r29[1]', 'varchar(200)') ,--[NPWPPPJK]
tempTable.item.value('r30[1]', 'varchar(200)') ,--[AlmtPpjk]
tempTable.item.value('r31[1]', 'varchar(200)') ,--[NoPpjk]
tempTable.item.value('r32[1]', 'varchar(200)') ,--[TgPpjk]
tempTable.item.value('r33[1]', 'varchar(200)') ,--[MODA]
tempTable.item.value('r34[1]', 'varchar(200)') ,--[TGEKS]
tempTable.item.value('r35[1]', 'varchar(200)') ,--[CARRIER]
tempTable.item.value('r36[1]', 'varchar(200)') ,--[VOY]
tempTable.item.value('r37[1]', 'varchar(200)') ,--[NOSHIPORD]
tempTable.item.value('r38[1]', 'varchar(200)') ,--[TGSHIPORD]
tempTable.item.value('r39[1]', 'varchar(200)') ,--[PELMUAT]
tempTable.item.value('r40[1]', 'varchar(200)') ,--[PELBONGKAR]
tempTable.item.value('r41[1]', 'varchar(200)') ,--[PELTRANSIT]
tempTable.item.value('r42[1]', 'varchar(200)') ,--[NOINV]
tempTable.item.value('r43[1]', 'varchar(200)') ,--[TGINV]
tempTable.item.value('r44[1]', 'varchar(200)') ,--[NOLPSE]
tempTable.item.value('r45[1]', 'varchar(200)') ,--[TGLPSE]
tempTable.item.value('r46[1]', 'varchar(200)') ,--[PROPBRG]
tempTable.item.value('r47[1]', 'varchar(200)') ,--[NEGTUJU]
tempTable.item.value('r48[1]', 'varchar(200)') ,--[FLSIE]
tempTable.item.value('r49[1]', 'varchar(200)') ,--[FLKARANT]
tempTable.item.value('r50[1]', 'varchar(200)') ,--[FLSM]
tempTable.item.value('r51[1]', 'varchar(200)') ,--[FLIZLAIN]
tempTable.item.value('r52[1]', 'varchar(200)') ,--[DELIVERY]
tempTable.item.value('r53[1]', 'varchar(200)') ,--[KDVAL]
tempTable.item.value('r54[1]', 'varchar(200)') ,--[KdHrg]
dbo.toDBdec(tempTable.item.value('r55[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NilInv]
dbo.toDBdec(tempTable.item.value('r56[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[FREIGHT]
dbo.toDBdec(tempTable.item.value('r57[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[ASURANSI]
tempTable.item.value('r58[1]', 'varchar(200)') ,--[KdAss]
dbo.toDBdec(tempTable.item.value('r59[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[FOB]
tempTable.item.value('r60[1]', 'varchar(200)') ,--[MERK]
dbo.toDBdec(tempTable.item.value('r61[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMCONT]
dbo.toDBdec(tempTable.item.value('r62[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[BRUTO]
dbo.toDBdec(tempTable.item.value('r63[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NETTO]
dbo.toDBdec(tempTable.item.value('r64[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRG]
dbo.toDBdec(tempTable.item.value('r65[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRGCTH]
dbo.toDBdec(tempTable.item.value('r66[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRGGAB]
dbo.toDBdec(tempTable.item.value('r67[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRGPJT]
tempTable.item.value('r68[1]', 'varchar(200)') ,--[NODAFT]
dbo.str_to_date (tempTable.item.value('r69[1]', 'varchar(200)')) ,--[TGDAFT]
tempTable.item.value('r70[1]', 'varchar(200)') ,--[KDKTR]
tempTable.item.value('r71[1]', 'varchar(200)') ,--[KTRGATE]
dbo.toDBdec(tempTable.item.value('r72[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NILPE]
tempTable.item.value('r73[1]', 'varchar(200)') ,--[KDBANK]
tempTable.item.value('r74[1]', 'varchar(200)') ,--[LOKBANK]
tempTable.item.value('r75[1]', 'varchar(200)') ,--[NOSSP]
dbo.str_to_date (tempTable.item.value('r76[1]', 'varchar(200)')) ,--[TGSSP]
tempTable.item.value('r77[1]', 'varchar(200)') ,--[JNTPB]
tempTable.item.value('r78[1]', 'varchar(200)') ,--[LOKTPB]
tempTable.item.value('r79[1]', 'varchar(200)') ,--[KDLOKBRG]
tempTable.item.value('r80[1]', 'varchar(200)') ,--[KDKTRMUAT]
tempTable.item.value('r81[1]', 'varchar(200)') ,--[KDKTRPRIKS]
dbo.str_to_date (tempTable.item.value('r82[1]', 'varchar(200)')) ,--[TGSIAP]
tempTable.item.value('r83[1]', 'varchar(200)') ,--[WKSIAP]
tempTable.item.value('r84[1]', 'varchar(200)') ,--[ASALDATA]
tempTable.item.value('r85[1]', 'varchar(200)') ,--[TPNAME]
tempTable.item.value('r86[1]', 'varchar(200)') ,--[PEBOK]
tempTable.item.value('r87[1]', 'varchar(200)') ,--[LENGKAP]
tempTable.item.value('r88[1]', 'varchar(200)') ,--[JNPARTOF]
tempTable.item.value('r89[1]', 'varchar(200)') ,--[BENDERA]
tempTable.item.value('r90[1]', 'varchar(200)') ,--[TTDPEB]
tempTable.item.value('r91[1]', 'varchar(200)') ,--[STATUS]
tempTable.item.value('r92[1]', 'varchar(200)') ,--[SNRF]
tempTable.item.value('r93[1]', 'varchar(200)') ,--[NOBCF]
dbo.str_to_date (tempTable.item.value('r94[1]', 'varchar(200)')) ,--[TGBCF]
dbo.toDBdec(tempTable.item.value('r95[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[SERIBCF]
tempTable.item.value('r96[1]', 'varchar(200)') ,--[NOMOREDI]
tempTable.item.value('r97[1]', 'varchar(200)') ,--[KMDT]
dbo.toDBdec(tempTable.item.value('r98[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[VOLUME]
tempTable.item.value('r99[1]', 'varchar(200)') ,--[KDHANGGAR]
tempTable.item.value('r100[1]', 'varchar(200)') ,--[KONS]
dbo.toDBdec(tempTable.item.value('r101[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMLKMSPEB]
tempTable.item.value('r102[1]', 'varchar(200)') ,--[PELMUATEKS]
dbo.toDBdec(tempTable.item.value('r103[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NILKURS]
dbo.toDBdec(tempTable.item.value('r104[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[PNBP]
tempTable.item.value('r105[1]', 'varchar(200)') ,--[NOBC11]
dbo.str_to_date (tempTable.item.value('r106[1]', 'varchar(200)')) ,--[TGBC11]
tempTable.item.value('r107[1]', 'varchar(200)') ,--[POSBC11]
tempTable.item.value('r108[1]', 'varchar(200)') --[SUBPOSBC11]
FROM @PARAMETER.nodes('param/field/xhdata/xh') tempTable(item)
declare @username varchar(255) =(
SELECT TOP (1) username
FROM users
WHERE (id = @id))
declare @id_bc30_hdr bigint
MERGE [DB_PASI_NEW].[dbo].[tblPebHdr] AS TARGET
USING @tblPebHdr AS SOURCE
ON (TARGET.car = SOURCE.car)
WHEN MATCHED THEN
UPDATE SET
TARGET.CAR=SOURCE.CAR,
TARGET.JnEks=SOURCE.JnEks,
TARGET.KatEks=SOURCE.KatEks,
TARGET.JNPEB=SOURCE.JNPEB,
TARGET.JNEKSOld=SOURCE.JNEKSOld,
TARGET.JNLAIN=SOURCE.JNLAIN,
TARGET.JNDAG=SOURCE.JNDAG,
TARGET.JNBYR=SOURCE.JNBYR,
TARGET.IDEKS=SOURCE.IDEKS,
TARGET.NPWPEKS=SOURCE.NPWPEKS,
TARGET.NamaEks=SOURCE.NamaEks,
TARGET.AlmtEks=SOURCE.AlmtEks,
TARGET.NoSiup=SOURCE.NoSiup,
TARGET.TgSiup=SOURCE.TgSiup,
TARGET.JnPerus=SOURCE.JnPerus,
TARGET.NoTdp=SOURCE.NoTdp,
TARGET.TgTdp=SOURCE.TgTdp,
TARGET.Niper=SOURCE.Niper,
TARGET.StatusH=SOURCE.StatusH,
TARGET.IdQq=SOURCE.IdQq,
TARGET.NpwpQq=SOURCE.NpwpQq,
TARGET.NamaQq=SOURCE.NamaQq,
TARGET.AlmtQq=SOURCE.AlmtQq,
TARGET.NiperQq=SOURCE.NiperQq,
TARGET.NAMABELI=SOURCE.NAMABELI,
TARGET.ALMTBELI=SOURCE.ALMTBELI,
TARGET.NEGBELI=SOURCE.NEGBELI,
TARGET.IDPPJK=SOURCE.IDPPJK,
TARGET.NPWPPPJK=SOURCE.NPWPPPJK,
TARGET.NamaPpjk=SOURCE.NamaPpjk,
TARGET.AlmtPpjk=SOURCE.AlmtPpjk,
TARGET.NoPpjk=SOURCE.NoPpjk,
TARGET.TgPpjk=SOURCE.TgPpjk,
TARGET.MODA=SOURCE.MODA,
TARGET.TGEKS=SOURCE.TGEKS,
TARGET.CARRIER=SOURCE.CARRIER,
TARGET.VOY=SOURCE.VOY,
TARGET.NOSHIPORD=SOURCE.NOSHIPORD,
TARGET.TGSHIPORD=SOURCE.TGSHIPORD,
TARGET.PELMUAT=SOURCE.PELMUAT,
TARGET.PELBONGKAR=SOURCE.PELBONGKAR,
TARGET.PELTRANSIT=SOURCE.PELTRANSIT,
TARGET.NOINV=SOURCE.NOINV,
TARGET.TGINV=SOURCE.TGINV,
TARGET.NOLPSE=SOURCE.NOLPSE,
TARGET.TGLPSE=SOURCE.TGLPSE,
TARGET.PROPBRG=SOURCE.PROPBRG,
TARGET.NEGTUJU=SOURCE.NEGTUJU,
TARGET.FLSIE=SOURCE.FLSIE,
TARGET.FLKARANT=SOURCE.FLKARANT,
TARGET.FLSM=SOURCE.FLSM,
TARGET.FLIZLAIN=SOURCE.FLIZLAIN,
TARGET.DELIVERY=SOURCE.DELIVERY,
TARGET.KDVAL=SOURCE.KDVAL,
TARGET.KdHrg=SOURCE.KdHrg,
TARGET.NilInv=SOURCE.NilInv,
TARGET.FREIGHT=SOURCE.FREIGHT,
TARGET.ASURANSI=SOURCE.ASURANSI,
TARGET.KdAss=SOURCE.KdAss,
TARGET.FOB=SOURCE.FOB,
TARGET.MERK=SOURCE.MERK,
TARGET.JMCONT=SOURCE.JMCONT,
TARGET.BRUTO=SOURCE.BRUTO,
TARGET.NETTO=SOURCE.NETTO,
TARGET.JMBRG=SOURCE.JMBRG,
TARGET.JMBRGCTH=SOURCE.JMBRGCTH,
TARGET.JMBRGGAB=SOURCE.JMBRGGAB,
TARGET.JMBRGPJT=SOURCE.JMBRGPJT,
TARGET.NODAFT=SOURCE.NODAFT,
TARGET.TGDAFT=SOURCE.TGDAFT,
TARGET.KDKTR=SOURCE.KDKTR,
TARGET.KTRGATE=SOURCE.KTRGATE,
TARGET.NILPE=SOURCE.NILPE,
TARGET.KDBANK=SOURCE.KDBANK,
TARGET.LOKBANK=SOURCE.LOKBANK,
TARGET.NOSSP=SOURCE.NOSSP,
TARGET.TGSSP=SOURCE.TGSSP,
TARGET.JNTPB=SOURCE.JNTPB,
TARGET.LOKTPB=SOURCE.LOKTPB,
TARGET.KDLOKBRG=SOURCE.KDLOKBRG,
TARGET.KDKTRMUAT=SOURCE.KDKTRMUAT,
TARGET.KDKTRPRIKS=SOURCE.KDKTRPRIKS,
TARGET.TGSIAP=SOURCE.TGSIAP,
TARGET.WKSIAP=SOURCE.WKSIAP,
TARGET.ASALDATA=SOURCE.ASALDATA,
TARGET.TPNAME=SOURCE.TPNAME,
TARGET.PEBOK=SOURCE.PEBOK,
TARGET.LENGKAP=SOURCE.LENGKAP,
TARGET.JNPARTOF=SOURCE.JNPARTOF,
TARGET.BENDERA=SOURCE.BENDERA,
TARGET.TTDPEB=SOURCE.TTDPEB,
TARGET.STATUS=SOURCE.STATUS,
TARGET.SNRF=SOURCE.SNRF,
TARGET.NOBCF=SOURCE.NOBCF,
TARGET.TGBCF=SOURCE.TGBCF,
TARGET.SERIBCF=SOURCE.SERIBCF,
TARGET.NOMOREDI=SOURCE.NOMOREDI,
TARGET.KMDT=SOURCE.KMDT,
TARGET.VOLUME=SOURCE.VOLUME,
TARGET.KDHANGGAR=SOURCE.KDHANGGAR,
TARGET.KONS=SOURCE.KONS,
TARGET.JMLKMSPEB=SOURCE.JMLKMSPEB,
TARGET.PELMUATEKS=SOURCE.PELMUATEKS,
TARGET.NILKURS=SOURCE.NILKURS,
TARGET.PNBP=SOURCE.PNBP,
TARGET.NOBC11=SOURCE.NOBC11,
TARGET.TGBC11=SOURCE.TGBC11,
TARGET.POSBC11=SOURCE.POSBC11,
TARGET.SUBPOSBC11=SOURCE.SUBPOSBC11 ,
TARGET.update_date=@NOW,
TARGET.update_by=@username
,@id_bc30_hdr=TARGET.idpebhdr
WHEN NOT MATCHED BY TARGET THEN
INSERT (
CAR, JnEks, KatEks, JNPEB, JNEKSOld, JNLAIN, JNDAG, JNBYR, IDEKS, NPWPEKS, NamaEks, AlmtEks, NoSiup, TgSiup, JnPerus, NoTdp, TgTdp, Niper, StatusH, IdQq, NpwpQq, NamaQq, AlmtQq, NiperQq, NAMABELI, ALMTBELI, NEGBELI, IDPPJK, NPWPPPJK, NamaPpjk, AlmtPpjk, NoPpjk, TgPpjk, MODA, TGEKS, CARRIER, VOY, NOSHIPORD, TGSHIPORD, PELMUAT, PELBONGKAR, PELTRANSIT, NOINV, TGINV, NOLPSE, TGLPSE, PROPBRG, NEGTUJU, FLSIE, FLKARANT, FLSM, FLIZLAIN, DELIVERY, KDVAL, KdHrg, NilInv, FREIGHT, ASURANSI, KdAss, FOB, MERK, JMCONT, BRUTO, NETTO, JMBRG, JMBRGCTH, JMBRGGAB, JMBRGPJT, NODAFT, TGDAFT, KDKTR, KTRGATE, NILPE, KDBANK, LOKBANK, NOSSP, TGSSP, JNTPB, LOKTPB, KDLOKBRG, KDKTRMUAT, KDKTRPRIKS, TGSIAP, WKSIAP, ASALDATA, TPNAME, PEBOK, LENGKAP, JNPARTOF, BENDERA, TTDPEB, STATUS, SNRF, NOBCF, TGBCF, SERIBCF, NOMOREDI, KMDT, VOLUME, KDHANGGAR, KONS, JMLKMSPEB, PELMUATEKS, NILKURS, PNBP, NOBC11, TGBC11, POSBC11, SUBPOSBC11, create_date, create_by, update_date, update_by, status_dok
)
VALUES (
SOURCE.CAR
, SOURCE.JnEks
, SOURCE.KatEks
,SOURCE.JNPEB
, SOURCE.JNEKSOld
,SOURCE.JNLAIN
,SOURCE.JNDAG
, SOURCE.JNBYR
, SOURCE.IDEKS
, SOURCE.NPWPEKS
, SOURCE.NamaEks
, SOURCE.AlmtEks
, SOURCE.NoSiup
, SOURCE.TgSiup
, SOURCE.JnPerus
, SOURCE.NoTdp
, SOURCE.TgTdp
, SOURCE.Niper
, SOURCE.StatusH
, SOURCE.IdQq
, SOURCE.NpwpQq
, SOURCE.NamaQq
, SOURCE.AlmtQq
, SOURCE.NiperQq
, SOURCE.NAMABELI
, SOURCE.ALMTBELI
, SOURCE.NEGBELI
, SOURCE.IDPPJK
, SOURCE.NPWPPPJK
, SOURCE.NamaPpjk
, SOURCE.AlmtPpjk
, SOURCE.NoPpjk
, SOURCE.TgPpjk
, SOURCE.MODA
, SOURCE.TGEKS
, SOURCE.CARRIER
, SOURCE.VOY
, SOURCE.NOSHIPORD
, SOURCE.TGSHIPORD
, SOURCE.PELMUAT
, SOURCE.PELBONGKAR
, SOURCE.PELTRANSIT
, SOURCE.NOINV
, SOURCE.TGINV
, SOURCE.NOLPSE
, SOURCE.TGLPSE
, SOURCE.PROPBRG
, SOURCE.NEGTUJU
, SOURCE.FLSIE
, SOURCE.FLKARANT
, SOURCE.FLSM
, SOURCE.FLIZLAIN
, SOURCE.DELIVERY
, SOURCE.KDVAL
, SOURCE.KdHrg
, SOURCE.NilInv
, SOURCE.FREIGHT
, SOURCE.ASURANSI
, SOURCE.KdAss
, SOURCE.FOB
, SOURCE.MERK
, SOURCE.JMCONT
, SOURCE.BRUTO
, SOURCE.NETTO
, SOURCE.JMBRG
, SOURCE.JMBRGCTH
, SOURCE.JMBRGGAB
, SOURCE.JMBRGPJT
, SOURCE.NODAFT
, SOURCE.TGDAFT
, SOURCE.KDKTR
, SOURCE.KTRGATE
, SOURCE.NILPE
, SOURCE.KDBANK
, SOURCE.LOKBANK
, SOURCE.NOSSP
, SOURCE.TGSSP
, SOURCE.JNTPB
, SOURCE.LOKTPB
, SOURCE.KDLOKBRG
, SOURCE.KDKTRMUAT
, SOURCE.KDKTRPRIKS
, SOURCE.TGSIAP
, SOURCE.WKSIAP
, SOURCE.ASALDATA
, SOURCE.TPNAME
, SOURCE.PEBOK
, SOURCE.LENGKAP
, SOURCE.JNPARTOF
, SOURCE.BENDERA
, SOURCE.TTDPEB
, SOURCE.STATUS
, SOURCE.SNRF
, SOURCE.NOBCF
, SOURCE.TGBCF
, SOURCE.SERIBCF
, SOURCE.NOMOREDI
, SOURCE.KMDT
, SOURCE.VOLUME
, SOURCE.KDHANGGAR
, SOURCE.KONS
, SOURCE.JMLKMSPEB
, SOURCE.PELMUATEKS
, SOURCE.NILKURS
, SOURCE.PNBP
, SOURCE.NOBC11
, SOURCE.TGBC11
, SOURCE.POSBC11
, SOURCE.SUBPOSBC11
, @NOW,
@username,
@NOW,
@username,
'DATA PROCESSED'
);
IF @id_bc30_hdr IS NULL
BEGIN
set @id_bc30_hdr = SCOPE_IDENTITY() ;
END;
declare @tblPEBdtl table (
[CAR] [varchar](200) not NULL,
[SERIBRG] [varchar](200) not NULL,
[HS] [varchar](200) NULL,
[URBRG1] [varchar](200) NULL,
[URBRG2] [varchar](200) NULL,
[URBRG3] [varchar](200) NULL,
[URBRG4] [varchar](200) NULL,
[DMERK] [varchar](200) NULL,
[SIZE] [varchar](200) NULL,
[TYPE] [varchar](200) NULL,
[KDBRG] [varchar](200) NULL,
[KDIZIN] [varchar](200) NULL,
[NOIZIN] [varchar](200) NULL,
[TGIZIN] [varchar](200) NULL,
[JMKOLI] [float] NULL,
[JNKOLI] [varchar](200) NULL,
[DNilInv] [float] NULL,
[FOBPERBRG] [float] NULL,
[FOBPERSAT] [float] NULL,
[KDPE] [varchar](200) NULL,
[TARIPPE] [float] NULL,
[HPATOK] [float] NULL,
[JMSATUAN] [float] NULL,
[JNSATUAN] [varchar](200) NULL,
[NETDET] [float] NULL,
[JMSATPE] [float] NULL,
[JNSATPE] [varchar](200) NULL,
[KDVALPE] [varchar](200) NULL,
[NILVALPE] [float] NULL,
[PEPERBRG] [float] NULL,
[DTLOK] [varchar](200) NULL,
[NCV] [varchar](200) NULL,
[DVolume] [float] NULL,
[NegAsal] [varchar](200) NULL,
[NOINV] [varchar](200) NULL,
[TGINV] [date] NULL,
primary key ([CAR],[SERIBRG])
)
insert into @tblPEBdtl
SELECT
tempTable.item.value('rID[1]', 'varchar(200)') ,--CAR
tempTable.item.value('r1[1]', 'varchar(200)') ,--[SERIBRG]
tempTable.item.value('r2[1]', 'varchar(200)') ,--[[HS]]
tempTable.item.value('r3[1]', 'varchar(200)') ,--[[URBRG1]]
tempTable.item.value('r4[1]', 'varchar(200)') ,--[URBRG2]
tempTable.item.value('r5[1]', 'varchar(200)') ,--[URBRG3]
tempTable.item.value('r6[1]', 'varchar(200)') ,--[URBRG4]
tempTable.item.value('r7[1]', 'varchar(200)') ,--[DMERK]
tempTable.item.value('r8[1]', 'varchar(200)') ,--[SIZE]
tempTable.item.value('r9[1]', 'varchar(200)') ,--[TYPE]
tempTable.item.value('r10[1]', 'varchar(200)') ,--[KDBRG]
tempTable.item.value('r11[1]', 'varchar(200)') ,--[KDIZIN]
tempTable.item.value('r12[1]', 'varchar(200)') ,--[NOIZIN]
tempTable.item.value('r13[1]', 'varchar(200)') ,--[TGIZIN]
dbo.toDBdec(tempTable.item.value('r14[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[JMKOLI]
tempTable.item.value('r15[1]', 'varchar(200)') ,--[JNKOLI]
dbo.toDBdec(tempTable.item.value('r16[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[DNilInv]
dbo.toDBdec(tempTable.item.value('r17[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[FOBPERBRG]
dbo.toDBdec(tempTable.item.value('r18[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[FOBPERSAT]
dbo.toDBdec(tempTable.item.value('r19[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[KDPE]
dbo.toDBdec(tempTable.item.value('r20[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[TARIPPE]
dbo.toDBdec(tempTable.item.value('r21[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[HPATOK]
tempTable.item.value('r22[1]', 'varchar(200)') ,--[JMSATUAN]
tempTable.item.value('r23[1]', 'varchar(200)') ,--[JNSATUAN]
dbo.toDBdec(tempTable.item.value('r24[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[NETDET]
dbo.toDBdec(tempTable.item.value('r25[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[JMSATPE]
tempTable.item.value('r26[1]', 'varchar(200)') ,--[JNSATPE]
tempTable.item.value('r27[1]', 'varchar(200)') ,--[KDVALPE]
dbo.toDBdec(tempTable.item.value('r28[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[PEPERBRG]
dbo.toDBdec(tempTable.item.value('r33[1]', 'varchar(200)'),@fGroup,@fDecimal), --[NILVALPE]
tempTable.item.value('r29[1]', 'varchar(200)') ,--[DTLOK]
tempTable.item.value('r30[1]', 'varchar(200)') ,--[NCV]
dbo.toDBdec(tempTable.item.value('r31[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[DVolume]
tempTable.item.value('r32[1]', 'varchar(200)'), --[NegAsal]
tempTable.item.value('r34[1]', 'varchar(200)') ,--[NOINV]
dbo.str_to_date (tempTable.item.value('r35[1]', 'varchar(200)')) --[TGINV]
FROM @PARAMETER.nodes('param/field/xddata/xd') tempTable(item)
MERGE [DB_PASI_NEW].[dbo].tblPebDtl AS TARGET
USING @tblPEBdtl AS SOURCE
ON (TARGET.CAR = SOURCE.CAR and TARGET.SERIBRG = SOURCE.SERIBRG)
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.idpebhdr=@id_bc30_hdr,
TARGET.CAR=SOURCE.CAR,
TARGET.SERIBRG=SOURCE.SERIBRG,
TARGET.HS=SOURCE.HS,
TARGET.URBRG1=SOURCE.URBRG1,
TARGET.URBRG2=SOURCE.URBRG2,
TARGET.URBRG3=SOURCE.URBRG3,
TARGET.URBRG4=SOURCE.URBRG4,
TARGET.DMERK=SOURCE.DMERK,
TARGET.SIZE=SOURCE.SIZE,
TARGET.TYPE=SOURCE.TYPE,
TARGET.KDBRG=SOURCE.KDBRG,
TARGET.KDIZIN=SOURCE.KDIZIN,
TARGET.NOIZIN=SOURCE.NOIZIN,
TARGET.TGIZIN=SOURCE.TGIZIN,
TARGET.JMKOLI=SOURCE.JMKOLI,
TARGET.JNKOLI=SOURCE.JNKOLI,
TARGET.DNilInv=SOURCE.DNilInv,
TARGET.FOBPERBRG=SOURCE.FOBPERBRG,
TARGET.FOBPERSAT=SOURCE.FOBPERSAT,
TARGET.KDPE=SOURCE.KDPE,
TARGET.TARIPPE=SOURCE.TARIPPE,
TARGET.HPATOK=SOURCE.HPATOK,
TARGET.JMSATUAN=SOURCE.JMSATUAN,
TARGET.JNSATUAN=SOURCE.JNSATUAN,
TARGET.NETDET=SOURCE.NETDET,
TARGET.JMSATPE=SOURCE.JMSATPE,
TARGET.JNSATPE=SOURCE.JNSATPE,
TARGET.KDVALPE=SOURCE.KDVALPE,
TARGET.NILVALPE=SOURCE.NILVALPE,
TARGET.PEPERBRG=SOURCE.PEPERBRG,
TARGET.DTLOK=SOURCE.DTLOK,
TARGET.NCV=SOURCE.NCV,
TARGET.DVolume=SOURCE.DVolume,
TARGET.NegAsal=SOURCE.NegAsal,
TARGET.NOINV=SOURCE.NOINV,
TARGET.TGINV=SOURCE.TGINV,
TARGET.update_date=@NOW,
TARGET.update_by=@username
WHEN NOT MATCHED BY TARGET THEN
INSERT (
idpebhdr, CAR, SERIBRG, HS, URBRG1, URBRG2, URBRG3, URBRG4, DMERK, SIZE, TYPE, KDBRG, KDIZIN, NOIZIN, TGIZIN, JMKOLI, JNKOLI, DNilInv, FOBPERBRG, FOBPERSAT, KDPE, TARIPPE, HPATOK, JMSATUAN, JNSATUAN, NETDET, JMSATPE, JNSATPE, KDVALPE, NILVALPE, PEPERBRG, DTLOK, NCV, DVolume, NegAsal, NOINV, TGINV, create_date, create_by, update_date, update_by, status
) VALUES (
@id_bc30_hdr
, SOURCE.CAR
, SOURCE.SERIBRG
, SOURCE.HS
, SOURCE.URBRG1
, SOURCE.URBRG2
, SOURCE.URBRG3
, SOURCE.URBRG4
, SOURCE.DMERK
, SOURCE.SIZE
, SOURCE.TYPE
, SOURCE.KDBRG
, SOURCE.KDIZIN
, SOURCE.NOIZIN
, SOURCE.TGIZIN
, SOURCE.JMKOLI
, SOURCE.JNKOLI
, SOURCE.DNilInv
, SOURCE.FOBPERBRG
, SOURCE.FOBPERSAT
, SOURCE.KDPE
, SOURCE.TARIPPE
, SOURCE.HPATOK
, SOURCE.JMSATUAN
, SOURCE.JNSATUAN
, SOURCE.NETDET
, SOURCE.JMSATPE
, SOURCE.JNSATPE
, SOURCE.KDVALPE
, SOURCE.NILVALPE
, SOURCE.PEPERBRG
, SOURCE.DTLOK
, SOURCE.NCV
, SOURCE.DVolume
, SOURCE.NegAsal
, SOURCE.NOINV
, SOURCE.TGINV
,@NOW
,@username
,@NOW
,@username
,'DATA PROCESSED'
)
--WHEN NOT MATCHED BY SOURCE THEN
--delete
;
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_OUT_EXTRACT_DATA_BC30_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='sukses'
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 @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
DECLARE @tblPebHdr table (
[CAR] [varchar](200) NOT NULL PRIMARY KEY,
[JnEks] [varchar](200) NULL,
[KatEks] [varchar](200) NULL,
[JNPEB] [varchar](200) NULL,
[JNEKSOld] [varchar](200) NULL,
[JNLAIN] [varchar](200) NULL,
[JNDAG] [varchar](200) NULL,
[JNBYR] [varchar](200) NULL,
[IDEKS] [varchar](200) NULL,
[NPWPEKS] [varchar](200) NULL,
[NamaEks] [varchar](200) NULL,
[AlmtEks] [varchar](200) NULL,
[NoSiup] [varchar](200) NULL,
[TgSiup] [varchar](200) NULL,
[JnPerus] [varchar](200) NULL,
[NoTdp] [varchar](200) NULL,
[TgTdp] [varchar](200) NULL,
[Niper] [varchar](200) NULL,
[StatusH] [varchar](200) NULL,
[IdQq] [varchar](200) NULL,
[NpwpQq] [varchar](200) NULL,
[NamaQq] [varchar](200) NULL,
[AlmtQq] [varchar](200) NULL,
[NiperQq] [varchar](200) NULL,
[NAMABELI] [varchar](200) NULL,
[ALMTBELI] [varchar](200) NULL,
[NEGBELI] [varchar](200) NULL,
[IDPPJK] [varchar](200) NULL,
[NPWPPPJK] [varchar](200) NULL,
[NamaPpjk] [varchar](200) NULL,
[AlmtPpjk] [varchar](200) NULL,
[NoPpjk] [varchar](200) NULL,
[TgPpjk] [varchar](200) NULL,
[MODA] [varchar](200) NULL,
[TGEKS] [varchar](200) NULL,
[CARRIER] [varchar](200) NULL,
[VOY] [varchar](200) NULL,
[NOSHIPORD] [varchar](200) NULL,
[TGSHIPORD] [varchar](200) NULL,
[PELMUAT] [varchar](200) NULL,
[PELBONGKAR] [varchar](200) NULL,
[PELTRANSIT] [varchar](200) NULL,
[NOINV] [varchar](200) NULL,
[TGINV] [varchar](200) NULL,
[NOLPSE] [varchar](200) NULL,
[TGLPSE] [varchar](200) NULL,
[PROPBRG] [varchar](200) NULL,
[NEGTUJU] [varchar](200) NULL,
[FLSIE] [varchar](200) NULL,
[FLKARANT] [varchar](200) NULL,
[FLSM] [varchar](200) NULL,
[FLIZLAIN] [varchar](200) NULL,
[DELIVERY] [varchar](200) NULL,
[KDVAL] [varchar](200) NULL,
[KdHrg] [varchar](200) NULL,
[NilInv] [float] NULL,
[FREIGHT] [float] NULL,
[ASURANSI] [float] NULL,
[KdAss] [varchar](200) NULL,
[FOB] [float] NULL,
[MERK] [varchar](200) NULL,
[JMCONT] [float] NULL,
[BRUTO] [float] NULL,
[NETTO] [float] NULL,
[JMBRG] [float] NULL,
[JMBRGCTH] [float] NULL,
[JMBRGGAB] [float] NULL,
[JMBRGPJT] [float] NULL,
[NODAFT] [varchar](200) NULL,
[TGDAFT] [date] NULL,
[KDKTR] [varchar](200) NULL,
[KTRGATE] [varchar](200) NULL,
[NILPE] [float] NULL,
[KDBANK] [varchar](200) NULL,
[LOKBANK] [varchar](200) NULL,
[NOSSP] [varchar](200) NULL,
[TGSSP] [date] NULL,
[JNTPB] [varchar](200) NULL,
[LOKTPB] [varchar](200) NULL,
[KDLOKBRG] [varchar](200) NULL,
[KDKTRMUAT] [varchar](200) NULL,
[KDKTRPRIKS] [varchar](200) NULL,
[TGSIAP] [date] NULL,
[WKSIAP] [varchar](200) NULL,
[ASALDATA] [varchar](200) NULL,
[TPNAME] [varchar](20) NULL,
[PEBOK] [varchar](200) NULL,
[LENGKAP] [varchar](200) NULL,
[JNPARTOF] [varchar](200) NULL,
[BENDERA] [varchar](200) NULL,
[TTDPEB] [varchar](200) NULL,
[STATUS] [varchar](200) NULL,
[SNRF] [varchar](200) NULL,
[NOBCF] [varchar](200) NULL,
[TGBCF] [date] NULL,
[SERIBCF] [float] NULL,
[NOMOREDI] [varchar](200) NULL,
[KMDT] [varchar](200) NULL,
[VOLUME] [float] NULL,
[KDHANGGAR] [varchar](200) NULL,
[KONS] [varchar](200) NULL,
[JMLKMSPEB] [float] NULL,
[PELMUATEKS] [varchar](200) NULL,
[NILKURS] [float] NULL,
[PNBP] [float] NULL,
[NOBC11] [varchar](200) NULL,
[TGBC11] [date] NULL,
[POSBC11] [varchar](200) NULL,
[SUBPOSBC11] [varchar](200) NULL)
insert into @tblPebHdr
SELECT
tempTable.item.value('rID[1]', 'varchar(200)') ,--[CAR]
tempTable.item.value('r1[1]', 'varchar(200)') ,--[JnEks]
tempTable.item.value('r2[1]', 'varchar(200)') ,--[KatEks]
tempTable.item.value('r3[1]', 'varchar(200)') ,--[JNPEB]
tempTable.item.value('r4[1]', 'varchar(200)') ,--[JNEKSOld]
tempTable.item.value('r5[1]', 'varchar(200)') ,--[JNLAIN]
tempTable.item.value('r6[1]', 'varchar(200)') ,--[JNDAG]
tempTable.item.value('r7[1]', 'varchar(200)') ,--[JNBYR]
tempTable.item.value('r8[1]', 'varchar(200)') ,--[IDEKS]
tempTable.item.value('r9[1]', 'varchar(200)') ,--[NPWPEKS]
tempTable.item.value('r10[1]', 'varchar(200)') ,--[NamaEks]
tempTable.item.value('r11[1]', 'varchar(200)') ,--[AlmtEks]
tempTable.item.value('r12[1]', 'varchar(200)') ,--[NoSiup]
tempTable.item.value('r13[1]', 'varchar(200)') ,--[TgSiup]
tempTable.item.value('r14[1]', 'varchar(200)') ,--[JnPerus]
tempTable.item.value('r15[1]', 'varchar(200)') ,--[NoTdp]
tempTable.item.value('r16[1]', 'varchar(200)') ,--[TgTdp]
tempTable.item.value('r17[1]', 'varchar(200)') ,--[Niper]
tempTable.item.value('r18[1]', 'varchar(200)') ,--[StatusH]
tempTable.item.value('r19[1]', 'varchar(200)') ,--[IdQq]
tempTable.item.value('r20[1]', 'varchar(200)') ,--[NpwpQq]
tempTable.item.value('r21[1]', 'varchar(200)') ,--[NamaQq]
tempTable.item.value('r22[1]', 'varchar(200)') ,--[AlmtQq]
tempTable.item.value('r23[1]', 'varchar(200)') ,--[NiperQq]
tempTable.item.value('r24[1]', 'varchar(200)') ,--[NAMABELI]
tempTable.item.value('r25[1]', 'varchar(200)') ,--[ALMTBELI]
tempTable.item.value('r26[1]', 'varchar(200)') ,--[NEGBELI]
tempTable.item.value('r27[1]', 'varchar(200)') ,--[IDPPJK]
tempTable.item.value('r28[1]', 'varchar(200)') ,--[NPWPPPJK]
tempTable.item.value('r29[1]', 'varchar(200)') ,--[NPWPPPJK]
tempTable.item.value('r30[1]', 'varchar(200)') ,--[AlmtPpjk]
tempTable.item.value('r31[1]', 'varchar(200)') ,--[NoPpjk]
tempTable.item.value('r32[1]', 'varchar(200)') ,--[TgPpjk]
tempTable.item.value('r33[1]', 'varchar(200)') ,--[MODA]
tempTable.item.value('r34[1]', 'varchar(200)') ,--[TGEKS]
tempTable.item.value('r35[1]', 'varchar(200)') ,--[CARRIER]
tempTable.item.value('r36[1]', 'varchar(200)') ,--[VOY]
tempTable.item.value('r37[1]', 'varchar(200)') ,--[NOSHIPORD]
tempTable.item.value('r38[1]', 'varchar(200)') ,--[TGSHIPORD]
tempTable.item.value('r39[1]', 'varchar(200)') ,--[PELMUAT]
tempTable.item.value('r40[1]', 'varchar(200)') ,--[PELBONGKAR]
tempTable.item.value('r41[1]', 'varchar(200)') ,--[PELTRANSIT]
tempTable.item.value('r42[1]', 'varchar(200)') ,--[NOINV]
tempTable.item.value('r43[1]', 'varchar(200)') ,--[TGINV]
tempTable.item.value('r44[1]', 'varchar(200)') ,--[NOLPSE]
tempTable.item.value('r45[1]', 'varchar(200)') ,--[TGLPSE]
tempTable.item.value('r46[1]', 'varchar(200)') ,--[PROPBRG]
tempTable.item.value('r47[1]', 'varchar(200)') ,--[NEGTUJU]
tempTable.item.value('r48[1]', 'varchar(200)') ,--[FLSIE]
tempTable.item.value('r49[1]', 'varchar(200)') ,--[FLKARANT]
tempTable.item.value('r50[1]', 'varchar(200)') ,--[FLSM]
tempTable.item.value('r51[1]', 'varchar(200)') ,--[FLIZLAIN]
tempTable.item.value('r52[1]', 'varchar(200)') ,--[DELIVERY]
tempTable.item.value('r53[1]', 'varchar(200)') ,--[KDVAL]
tempTable.item.value('r54[1]', 'varchar(200)') ,--[KdHrg]
dbo.toDBdec(tempTable.item.value('r55[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NilInv]
dbo.toDBdec(tempTable.item.value('r56[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[FREIGHT]
dbo.toDBdec(tempTable.item.value('r57[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[ASURANSI]
tempTable.item.value('r58[1]', 'varchar(200)') ,--[KdAss]
dbo.toDBdec(tempTable.item.value('r59[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[FOB]
tempTable.item.value('r60[1]', 'varchar(200)') ,--[MERK]
dbo.toDBdec(tempTable.item.value('r61[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMCONT]
dbo.toDBdec(tempTable.item.value('r62[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[BRUTO]
dbo.toDBdec(tempTable.item.value('r63[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NETTO]
dbo.toDBdec(tempTable.item.value('r64[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRG]
dbo.toDBdec(tempTable.item.value('r65[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRGCTH]
dbo.toDBdec(tempTable.item.value('r66[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRGGAB]
dbo.toDBdec(tempTable.item.value('r67[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMBRGPJT]
tempTable.item.value('r68[1]', 'varchar(200)') ,--[NODAFT]
dbo.str_to_date (tempTable.item.value('r69[1]', 'varchar(200)')) ,--[TGDAFT]
tempTable.item.value('r70[1]', 'varchar(200)') ,--[KDKTR]
tempTable.item.value('r71[1]', 'varchar(200)') ,--[KTRGATE]
dbo.toDBdec(tempTable.item.value('r72[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NILPE]
tempTable.item.value('r73[1]', 'varchar(200)') ,--[KDBANK]
tempTable.item.value('r74[1]', 'varchar(200)') ,--[LOKBANK]
tempTable.item.value('r75[1]', 'varchar(200)') ,--[NOSSP]
dbo.str_to_date (tempTable.item.value('r76[1]', 'varchar(200)')) ,--[TGSSP]
tempTable.item.value('r77[1]', 'varchar(200)') ,--[JNTPB]
tempTable.item.value('r78[1]', 'varchar(200)') ,--[LOKTPB]
tempTable.item.value('r79[1]', 'varchar(200)') ,--[KDLOKBRG]
tempTable.item.value('r80[1]', 'varchar(200)') ,--[KDKTRMUAT]
tempTable.item.value('r81[1]', 'varchar(200)') ,--[KDKTRPRIKS]
dbo.str_to_date (tempTable.item.value('r82[1]', 'varchar(200)')) ,--[TGSIAP]
tempTable.item.value('r83[1]', 'varchar(200)') ,--[WKSIAP]
tempTable.item.value('r84[1]', 'varchar(200)') ,--[ASALDATA]
tempTable.item.value('r85[1]', 'varchar(200)') ,--[TPNAME]
tempTable.item.value('r86[1]', 'varchar(200)') ,--[PEBOK]
tempTable.item.value('r87[1]', 'varchar(200)') ,--[LENGKAP]
tempTable.item.value('r88[1]', 'varchar(200)') ,--[JNPARTOF]
tempTable.item.value('r89[1]', 'varchar(200)') ,--[BENDERA]
tempTable.item.value('r90[1]', 'varchar(200)') ,--[TTDPEB]
tempTable.item.value('r91[1]', 'varchar(200)') ,--[STATUS]
tempTable.item.value('r92[1]', 'varchar(200)') ,--[SNRF]
tempTable.item.value('r93[1]', 'varchar(200)') ,--[NOBCF]
dbo.str_to_date (tempTable.item.value('r94[1]', 'varchar(200)')) ,--[TGBCF]
dbo.toDBdec(tempTable.item.value('r95[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[SERIBCF]
tempTable.item.value('r96[1]', 'varchar(200)') ,--[NOMOREDI]
tempTable.item.value('r97[1]', 'varchar(200)') ,--[KMDT]
dbo.toDBdec(tempTable.item.value('r98[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[VOLUME]
tempTable.item.value('r99[1]', 'varchar(200)') ,--[KDHANGGAR]
tempTable.item.value('r100[1]', 'varchar(200)') ,--[KONS]
dbo.toDBdec(tempTable.item.value('r101[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[JMLKMSPEB]
tempTable.item.value('r102[1]', 'varchar(200)') ,--[PELMUATEKS]
dbo.toDBdec(tempTable.item.value('r103[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[NILKURS]
dbo.toDBdec(tempTable.item.value('r104[1]', 'varchar(200)') ,@fGroup,@fDecimal) ,--[PNBP]
tempTable.item.value('r105[1]', 'varchar(200)') ,--[NOBC11]
dbo.str_to_date (tempTable.item.value('r106[1]', 'varchar(200)')) ,--[TGBC11]
tempTable.item.value('r107[1]', 'varchar(200)') ,--[POSBC11]
tempTable.item.value('r108[1]', 'varchar(200)') --[SUBPOSBC11]
FROM @PARAMETER.nodes('param/field/xhdata/xh') tempTable(item)
declare @username varchar(255) =(
SELECT TOP (1) username
FROM users
WHERE (id = @id))
declare @id_bc30_hdr bigint
MERGE [DB_PASI_NEW].[dbo].[tblPebHdr] AS TARGET
USING @tblPebHdr AS SOURCE
ON (TARGET.car = SOURCE.car)
WHEN MATCHED THEN
UPDATE SET
TARGET.CAR=SOURCE.CAR,
TARGET.JnEks=SOURCE.JnEks,
TARGET.KatEks=SOURCE.KatEks,
TARGET.JNPEB=SOURCE.JNPEB,
TARGET.JNEKSOld=SOURCE.JNEKSOld,
TARGET.JNLAIN=SOURCE.JNLAIN,
TARGET.JNDAG=SOURCE.JNDAG,
TARGET.JNBYR=SOURCE.JNBYR,
TARGET.IDEKS=SOURCE.IDEKS,
TARGET.NPWPEKS=SOURCE.NPWPEKS,
TARGET.NamaEks=SOURCE.NamaEks,
TARGET.AlmtEks=SOURCE.AlmtEks,
TARGET.NoSiup=SOURCE.NoSiup,
TARGET.TgSiup=SOURCE.TgSiup,
TARGET.JnPerus=SOURCE.JnPerus,
TARGET.NoTdp=SOURCE.NoTdp,
TARGET.TgTdp=SOURCE.TgTdp,
TARGET.Niper=SOURCE.Niper,
TARGET.StatusH=SOURCE.StatusH,
TARGET.IdQq=SOURCE.IdQq,
TARGET.NpwpQq=SOURCE.NpwpQq,
TARGET.NamaQq=SOURCE.NamaQq,
TARGET.AlmtQq=SOURCE.AlmtQq,
TARGET.NiperQq=SOURCE.NiperQq,
TARGET.NAMABELI=SOURCE.NAMABELI,
TARGET.ALMTBELI=SOURCE.ALMTBELI,
TARGET.NEGBELI=SOURCE.NEGBELI,
TARGET.IDPPJK=SOURCE.IDPPJK,
TARGET.NPWPPPJK=SOURCE.NPWPPPJK,
TARGET.NamaPpjk=SOURCE.NamaPpjk,
TARGET.AlmtPpjk=SOURCE.AlmtPpjk,
TARGET.NoPpjk=SOURCE.NoPpjk,
TARGET.TgPpjk=SOURCE.TgPpjk,
TARGET.MODA=SOURCE.MODA,
TARGET.TGEKS=SOURCE.TGEKS,
TARGET.CARRIER=SOURCE.CARRIER,
TARGET.VOY=SOURCE.VOY,
TARGET.NOSHIPORD=SOURCE.NOSHIPORD,
TARGET.TGSHIPORD=SOURCE.TGSHIPORD,
TARGET.PELMUAT=SOURCE.PELMUAT,
TARGET.PELBONGKAR=SOURCE.PELBONGKAR,
TARGET.PELTRANSIT=SOURCE.PELTRANSIT,
TARGET.NOINV=SOURCE.NOINV,
TARGET.TGINV=SOURCE.TGINV,
TARGET.NOLPSE=SOURCE.NOLPSE,
TARGET.TGLPSE=SOURCE.TGLPSE,
TARGET.PROPBRG=SOURCE.PROPBRG,
TARGET.NEGTUJU=SOURCE.NEGTUJU,
TARGET.FLSIE=SOURCE.FLSIE,
TARGET.FLKARANT=SOURCE.FLKARANT,
TARGET.FLSM=SOURCE.FLSM,
TARGET.FLIZLAIN=SOURCE.FLIZLAIN,
TARGET.DELIVERY=SOURCE.DELIVERY,
TARGET.KDVAL=SOURCE.KDVAL,
TARGET.KdHrg=SOURCE.KdHrg,
TARGET.NilInv=SOURCE.NilInv,
TARGET.FREIGHT=SOURCE.FREIGHT,
TARGET.ASURANSI=SOURCE.ASURANSI,
TARGET.KdAss=SOURCE.KdAss,
TARGET.FOB=SOURCE.FOB,
TARGET.MERK=SOURCE.MERK,
TARGET.JMCONT=SOURCE.JMCONT,
TARGET.BRUTO=SOURCE.BRUTO,
TARGET.NETTO=SOURCE.NETTO,
TARGET.JMBRG=SOURCE.JMBRG,
TARGET.JMBRGCTH=SOURCE.JMBRGCTH,
TARGET.JMBRGGAB=SOURCE.JMBRGGAB,
TARGET.JMBRGPJT=SOURCE.JMBRGPJT,
TARGET.NODAFT=SOURCE.NODAFT,
TARGET.TGDAFT=SOURCE.TGDAFT,
TARGET.KDKTR=SOURCE.KDKTR,
TARGET.KTRGATE=SOURCE.KTRGATE,
TARGET.NILPE=SOURCE.NILPE,
TARGET.KDBANK=SOURCE.KDBANK,
TARGET.LOKBANK=SOURCE.LOKBANK,
TARGET.NOSSP=SOURCE.NOSSP,
TARGET.TGSSP=SOURCE.TGSSP,
TARGET.JNTPB=SOURCE.JNTPB,
TARGET.LOKTPB=SOURCE.LOKTPB,
TARGET.KDLOKBRG=SOURCE.KDLOKBRG,
TARGET.KDKTRMUAT=SOURCE.KDKTRMUAT,
TARGET.KDKTRPRIKS=SOURCE.KDKTRPRIKS,
TARGET.TGSIAP=SOURCE.TGSIAP,
TARGET.WKSIAP=SOURCE.WKSIAP,
TARGET.ASALDATA=SOURCE.ASALDATA,
TARGET.TPNAME=SOURCE.TPNAME,
TARGET.PEBOK=SOURCE.PEBOK,
TARGET.LENGKAP=SOURCE.LENGKAP,
TARGET.JNPARTOF=SOURCE.JNPARTOF,
TARGET.BENDERA=SOURCE.BENDERA,
TARGET.TTDPEB=SOURCE.TTDPEB,
TARGET.STATUS=SOURCE.STATUS,
TARGET.SNRF=SOURCE.SNRF,
TARGET.NOBCF=SOURCE.NOBCF,
TARGET.TGBCF=SOURCE.TGBCF,
TARGET.SERIBCF=SOURCE.SERIBCF,
TARGET.NOMOREDI=SOURCE.NOMOREDI,
TARGET.KMDT=SOURCE.KMDT,
TARGET.VOLUME=SOURCE.VOLUME,
TARGET.KDHANGGAR=SOURCE.KDHANGGAR,
TARGET.KONS=SOURCE.KONS,
TARGET.JMLKMSPEB=SOURCE.JMLKMSPEB,
TARGET.PELMUATEKS=SOURCE.PELMUATEKS,
TARGET.NILKURS=SOURCE.NILKURS,
TARGET.PNBP=SOURCE.PNBP,
TARGET.NOBC11=SOURCE.NOBC11,
TARGET.TGBC11=SOURCE.TGBC11,
TARGET.POSBC11=SOURCE.POSBC11,
TARGET.SUBPOSBC11=SOURCE.SUBPOSBC11 ,
TARGET.update_date=@NOW,
TARGET.update_by=@username
,@id_bc30_hdr=TARGET.idpebhdr
WHEN NOT MATCHED BY TARGET THEN
INSERT (
CAR, JnEks, KatEks, JNPEB, JNEKSOld, JNLAIN, JNDAG, JNBYR, IDEKS, NPWPEKS, NamaEks, AlmtEks, NoSiup, TgSiup, JnPerus, NoTdp, TgTdp, Niper, StatusH, IdQq, NpwpQq, NamaQq, AlmtQq, NiperQq, NAMABELI, ALMTBELI, NEGBELI, IDPPJK, NPWPPPJK, NamaPpjk, AlmtPpjk, NoPpjk, TgPpjk, MODA, TGEKS, CARRIER, VOY, NOSHIPORD, TGSHIPORD, PELMUAT, PELBONGKAR, PELTRANSIT, NOINV, TGINV, NOLPSE, TGLPSE, PROPBRG, NEGTUJU, FLSIE, FLKARANT, FLSM, FLIZLAIN, DELIVERY, KDVAL, KdHrg, NilInv, FREIGHT, ASURANSI, KdAss, FOB, MERK, JMCONT, BRUTO, NETTO, JMBRG, JMBRGCTH, JMBRGGAB, JMBRGPJT, NODAFT, TGDAFT, KDKTR, KTRGATE, NILPE, KDBANK, LOKBANK, NOSSP, TGSSP, JNTPB, LOKTPB, KDLOKBRG, KDKTRMUAT, KDKTRPRIKS, TGSIAP, WKSIAP, ASALDATA, TPNAME, PEBOK, LENGKAP, JNPARTOF, BENDERA, TTDPEB, STATUS, SNRF, NOBCF, TGBCF, SERIBCF, NOMOREDI, KMDT, VOLUME, KDHANGGAR, KONS, JMLKMSPEB, PELMUATEKS, NILKURS, PNBP, NOBC11, TGBC11, POSBC11, SUBPOSBC11, create_date, create_by, update_date, update_by, status_dok
)
VALUES (
SOURCE.CAR
, SOURCE.JnEks
, SOURCE.KatEks
,SOURCE.JNPEB
, SOURCE.JNEKSOld
,SOURCE.JNLAIN
,SOURCE.JNDAG
, SOURCE.JNBYR
, SOURCE.IDEKS
, SOURCE.NPWPEKS
, SOURCE.NamaEks
, SOURCE.AlmtEks
, SOURCE.NoSiup
, SOURCE.TgSiup
, SOURCE.JnPerus
, SOURCE.NoTdp
, SOURCE.TgTdp
, SOURCE.Niper
, SOURCE.StatusH
, SOURCE.IdQq
, SOURCE.NpwpQq
, SOURCE.NamaQq
, SOURCE.AlmtQq
, SOURCE.NiperQq
, SOURCE.NAMABELI
, SOURCE.ALMTBELI
, SOURCE.NEGBELI
, SOURCE.IDPPJK
, SOURCE.NPWPPPJK
, SOURCE.NamaPpjk
, SOURCE.AlmtPpjk
, SOURCE.NoPpjk
, SOURCE.TgPpjk
, SOURCE.MODA
, SOURCE.TGEKS
, SOURCE.CARRIER
, SOURCE.VOY
, SOURCE.NOSHIPORD
, SOURCE.TGSHIPORD
, SOURCE.PELMUAT
, SOURCE.PELBONGKAR
, SOURCE.PELTRANSIT
, SOURCE.NOINV
, SOURCE.TGINV
, SOURCE.NOLPSE
, SOURCE.TGLPSE
, SOURCE.PROPBRG
, SOURCE.NEGTUJU
, SOURCE.FLSIE
, SOURCE.FLKARANT
, SOURCE.FLSM
, SOURCE.FLIZLAIN
, SOURCE.DELIVERY
, SOURCE.KDVAL
, SOURCE.KdHrg
, SOURCE.NilInv
, SOURCE.FREIGHT
, SOURCE.ASURANSI
, SOURCE.KdAss
, SOURCE.FOB
, SOURCE.MERK
, SOURCE.JMCONT
, SOURCE.BRUTO
, SOURCE.NETTO
, SOURCE.JMBRG
, SOURCE.JMBRGCTH
, SOURCE.JMBRGGAB
, SOURCE.JMBRGPJT
, SOURCE.NODAFT
, SOURCE.TGDAFT
, SOURCE.KDKTR
, SOURCE.KTRGATE
, SOURCE.NILPE
, SOURCE.KDBANK
, SOURCE.LOKBANK
, SOURCE.NOSSP
, SOURCE.TGSSP
, SOURCE.JNTPB
, SOURCE.LOKTPB
, SOURCE.KDLOKBRG
, SOURCE.KDKTRMUAT
, SOURCE.KDKTRPRIKS
, SOURCE.TGSIAP
, SOURCE.WKSIAP
, SOURCE.ASALDATA
, SOURCE.TPNAME
, SOURCE.PEBOK
, SOURCE.LENGKAP
, SOURCE.JNPARTOF
, SOURCE.BENDERA
, SOURCE.TTDPEB
, SOURCE.STATUS
, SOURCE.SNRF
, SOURCE.NOBCF
, SOURCE.TGBCF
, SOURCE.SERIBCF
, SOURCE.NOMOREDI
, SOURCE.KMDT
, SOURCE.VOLUME
, SOURCE.KDHANGGAR
, SOURCE.KONS
, SOURCE.JMLKMSPEB
, SOURCE.PELMUATEKS
, SOURCE.NILKURS
, SOURCE.PNBP
, SOURCE.NOBC11
, SOURCE.TGBC11
, SOURCE.POSBC11
, SOURCE.SUBPOSBC11
, @NOW,
@username,
@NOW,
@username,
'DATA PROCESSED'
);
IF @id_bc30_hdr IS NULL
BEGIN
set @id_bc30_hdr = SCOPE_IDENTITY() ;
END;
declare @tblPEBdtl table (
[CAR] [varchar](200) not NULL,
[SERIBRG] [varchar](200) not NULL,
[HS] [varchar](200) NULL,
[URBRG1] [varchar](200) NULL,
[URBRG2] [varchar](200) NULL,
[URBRG3] [varchar](200) NULL,
[URBRG4] [varchar](200) NULL,
[DMERK] [varchar](200) NULL,
[SIZE] [varchar](200) NULL,
[TYPE] [varchar](200) NULL,
[KDBRG] [varchar](200) NULL,
[KDIZIN] [varchar](200) NULL,
[NOIZIN] [varchar](200) NULL,
[TGIZIN] [varchar](200) NULL,
[JMKOLI] [float] NULL,
[JNKOLI] [varchar](200) NULL,
[DNilInv] [float] NULL,
[FOBPERBRG] [float] NULL,
[FOBPERSAT] [float] NULL,
[KDPE] [varchar](200) NULL,
[TARIPPE] [float] NULL,
[HPATOK] [float] NULL,
[JMSATUAN] [float] NULL,
[JNSATUAN] [varchar](200) NULL,
[NETDET] [float] NULL,
[JMSATPE] [float] NULL,
[JNSATPE] [varchar](200) NULL,
[KDVALPE] [varchar](200) NULL,
[NILVALPE] [float] NULL,
[PEPERBRG] [float] NULL,
[DTLOK] [varchar](200) NULL,
[NCV] [varchar](200) NULL,
[DVolume] [float] NULL,
[NegAsal] [varchar](200) NULL,
[NOINV] [varchar](200) NULL,
[TGINV] [date] NULL,
primary key ([CAR],[SERIBRG])
)
insert into @tblPEBdtl
SELECT
tempTable.item.value('rID[1]', 'varchar(200)') ,--CAR
tempTable.item.value('r1[1]', 'varchar(200)') ,--[SERIBRG]
tempTable.item.value('r2[1]', 'varchar(200)') ,--[[HS]]
tempTable.item.value('r3[1]', 'varchar(200)') ,--[[URBRG1]]
tempTable.item.value('r4[1]', 'varchar(200)') ,--[URBRG2]
tempTable.item.value('r5[1]', 'varchar(200)') ,--[URBRG3]
tempTable.item.value('r6[1]', 'varchar(200)') ,--[URBRG4]
tempTable.item.value('r7[1]', 'varchar(200)') ,--[DMERK]
tempTable.item.value('r8[1]', 'varchar(200)') ,--[SIZE]
tempTable.item.value('r9[1]', 'varchar(200)') ,--[TYPE]
tempTable.item.value('r10[1]', 'varchar(200)') ,--[KDBRG]
tempTable.item.value('r11[1]', 'varchar(200)') ,--[KDIZIN]
tempTable.item.value('r12[1]', 'varchar(200)') ,--[NOIZIN]
tempTable.item.value('r13[1]', 'varchar(200)') ,--[TGIZIN]
dbo.toDBdec(tempTable.item.value('r14[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[JMKOLI]
tempTable.item.value('r15[1]', 'varchar(200)') ,--[JNKOLI]
dbo.toDBdec(tempTable.item.value('r16[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[DNilInv]
dbo.toDBdec(tempTable.item.value('r17[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[FOBPERBRG]
dbo.toDBdec(tempTable.item.value('r18[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[FOBPERSAT]
dbo.toDBdec(tempTable.item.value('r19[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[KDPE]
dbo.toDBdec(tempTable.item.value('r20[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[TARIPPE]
dbo.toDBdec(tempTable.item.value('r21[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[HPATOK]
tempTable.item.value('r22[1]', 'varchar(200)') ,--[JMSATUAN]
tempTable.item.value('r23[1]', 'varchar(200)') ,--[JNSATUAN]
dbo.toDBdec(tempTable.item.value('r24[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[NETDET]
dbo.toDBdec(tempTable.item.value('r25[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[JMSATPE]
tempTable.item.value('r26[1]', 'varchar(200)') ,--[JNSATPE]
tempTable.item.value('r27[1]', 'varchar(200)') ,--[KDVALPE]
dbo.toDBdec(tempTable.item.value('r28[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[PEPERBRG]
dbo.toDBdec(tempTable.item.value('r33[1]', 'varchar(200)'),@fGroup,@fDecimal), --[NILVALPE]
tempTable.item.value('r29[1]', 'varchar(200)') ,--[DTLOK]
tempTable.item.value('r30[1]', 'varchar(200)') ,--[NCV]
dbo.toDBdec(tempTable.item.value('r31[1]', 'varchar(200)'),@fGroup,@fDecimal) ,--[DVolume]
tempTable.item.value('r32[1]', 'varchar(200)'), --[NegAsal]
tempTable.item.value('r34[1]', 'varchar(200)') ,--[NOINV]
dbo.str_to_date (tempTable.item.value('r35[1]', 'varchar(200)')) --[TGINV]
FROM @PARAMETER.nodes('param/field/xddata/xd') tempTable(item)
MERGE [DB_PASI_NEW].[dbo].tblPebDtl AS TARGET
USING @tblPEBdtl AS SOURCE
ON (TARGET.CAR = SOURCE.CAR and TARGET.SERIBRG = SOURCE.SERIBRG)
--When records are matched, update
--the records IF there is any change
WHEN MATCHED THEN
UPDATE SET
TARGET.idpebhdr=@id_bc30_hdr,
TARGET.CAR=SOURCE.CAR,
TARGET.SERIBRG=SOURCE.SERIBRG,
TARGET.HS=SOURCE.HS,
TARGET.URBRG1=SOURCE.URBRG1,
TARGET.URBRG2=SOURCE.URBRG2,
TARGET.URBRG3=SOURCE.URBRG3,
TARGET.URBRG4=SOURCE.URBRG4,
TARGET.DMERK=SOURCE.DMERK,
TARGET.SIZE=SOURCE.SIZE,
TARGET.TYPE=SOURCE.TYPE,
TARGET.KDBRG=SOURCE.KDBRG,
TARGET.KDIZIN=SOURCE.KDIZIN,
TARGET.NOIZIN=SOURCE.NOIZIN,
TARGET.TGIZIN=SOURCE.TGIZIN,
TARGET.JMKOLI=SOURCE.JMKOLI,
TARGET.JNKOLI=SOURCE.JNKOLI,
TARGET.DNilInv=SOURCE.DNilInv,
TARGET.FOBPERBRG=SOURCE.FOBPERBRG,
TARGET.FOBPERSAT=SOURCE.FOBPERSAT,
TARGET.KDPE=SOURCE.KDPE,
TARGET.TARIPPE=SOURCE.TARIPPE,
TARGET.HPATOK=SOURCE.HPATOK,
TARGET.JMSATUAN=SOURCE.JMSATUAN,
TARGET.JNSATUAN=SOURCE.JNSATUAN,
TARGET.NETDET=SOURCE.NETDET,
TARGET.JMSATPE=SOURCE.JMSATPE,
TARGET.JNSATPE=SOURCE.JNSATPE,
TARGET.KDVALPE=SOURCE.KDVALPE,
TARGET.NILVALPE=SOURCE.NILVALPE,
TARGET.PEPERBRG=SOURCE.PEPERBRG,
TARGET.DTLOK=SOURCE.DTLOK,
TARGET.NCV=SOURCE.NCV,
TARGET.DVolume=SOURCE.DVolume,
TARGET.NegAsal=SOURCE.NegAsal,
TARGET.NOINV=SOURCE.NOINV,
TARGET.TGINV=SOURCE.TGINV,
TARGET.update_date=@NOW,
TARGET.update_by=@username
WHEN NOT MATCHED BY TARGET THEN
INSERT (
idpebhdr, CAR, SERIBRG, HS, URBRG1, URBRG2, URBRG3, URBRG4, DMERK, SIZE, TYPE, KDBRG, KDIZIN, NOIZIN, TGIZIN, JMKOLI, JNKOLI, DNilInv, FOBPERBRG, FOBPERSAT, KDPE, TARIPPE, HPATOK, JMSATUAN, JNSATUAN, NETDET, JMSATPE, JNSATPE, KDVALPE, NILVALPE, PEPERBRG, DTLOK, NCV, DVolume, NegAsal, NOINV, TGINV, create_date, create_by, update_date, update_by, status
) VALUES (
@id_bc30_hdr
, SOURCE.CAR
, SOURCE.SERIBRG
, SOURCE.HS
, SOURCE.URBRG1
, SOURCE.URBRG2
, SOURCE.URBRG3
, SOURCE.URBRG4
, SOURCE.DMERK
, SOURCE.SIZE
, SOURCE.TYPE
, SOURCE.KDBRG
, SOURCE.KDIZIN
, SOURCE.NOIZIN
, SOURCE.TGIZIN
, SOURCE.JMKOLI
, SOURCE.JNKOLI
, SOURCE.DNilInv
, SOURCE.FOBPERBRG
, SOURCE.FOBPERSAT
, SOURCE.KDPE
, SOURCE.TARIPPE
, SOURCE.HPATOK
, SOURCE.JMSATUAN
, SOURCE.JNSATUAN
, SOURCE.NETDET
, SOURCE.JMSATPE
, SOURCE.JNSATPE
, SOURCE.KDVALPE
, SOURCE.NILVALPE
, SOURCE.PEPERBRG
, SOURCE.DTLOK
, SOURCE.NCV
, SOURCE.DVolume
, SOURCE.NegAsal
, SOURCE.NOINV
, SOURCE.TGINV
,@NOW
,@username
,@NOW
,@username
,'DATA PROCESSED'
)
--WHEN NOT MATCHED BY SOURCE THEN
--delete
;
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