Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.spi_getdata_bc30
-- Add the parameters for the stored procedure here
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @filecount int, @uruterror int
Declare @Size varchar(50)
Declare @FileName varchar(50)
declare @fileinv varchar(100), @logerror varchar(100), @logerror_dtl varchar(100)
declare @cmdmove varchar(255)
DECLARE @Files table(Names varchar(250) null)
Declare @mdate varchar(14)
Declare @dir varchar(100)
declare @dircsv varchar(500)
-- for bulk
declare @tbl_load_bc30_all varchar(MAX)
declare @filedir varchar(400)
declare @formatfile varchar(400)
declare @formatfiledtl varchar(400)
----
set @dir = (select top 1 direktori from dbo.tbl_direktori)
set @filedir=@dir+'\exim\bc30'
set @dircsv='dir '+@dir+'\exim\bc30\'+'*.csv'
set @formatfile = @dir+'\exim\bc30\format\'+'fmt2header.xml'
set @formatfiledtl = @dir+'\exim\bc30\format\'+'fmt2detil.xml'
INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircsv
set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
if @filecount = 1
begin
select '01: ' as status, names as description from @Files where Names like '%File Not Found%'
return
end
DELETE @Files WHERE Names LIKE '%%'
--delete all informational messages
DELETE @Files WHERE Names LIKE ' %'
--delete the null values
DELETE @Files WHERE Names IS NULL
--get rid of dateinfo
UPDATE @Files SET Names =RIGHT(Names,(LEN(Names)-20))
--get rid of leading spaces
UPDATE @Files SET Names =LTRIM(Names)
--split data into size and filename
Declare @dynmaicsql as nvarchar(1000);
Declare @dynamicparam as nvarchar(1000);
declare @jmlheader int
set @uruterror=1
DECLARE file_cursor CURSOR FOR
SELECT LEFT(Names,PATINDEX('% %',Names)) AS Size, RIGHT(Names,LEN(Names) -PATINDEX('% %',Names)) AS FileName FROM @Files where Names like '%.csv' order by Names
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @Size, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN -- begin here
set @fileinv = @filedir +'\' + @FileName
set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @FileName
set @mdate = (SELECT CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':',''))
set @logerror = @filedir+'\log\error' + @mdate + CONVERT(VARCHAR, @uruterror) + '.txt'
set @logerror_dtl =@filedir+'\log\errordtl' + @mdate + CONVERT(VARCHAR, @uruterror) + '.txt'
Set @dynmaicsql =
' set @jmlhdr = (select COUNT(*) as cnt FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
'] '') where F1 = ''H0'') '
Set @dynamicparam = '@jmlhdr int output'
execute sp_executesql @dynmaicsql, @dynamicparam, @jmlheader output
declare @jmldetil int
set @jmldetil = @jmlheader+1
BEGIN TRY
exec(
' declare @invgab varchar(100) declare @idpebhdrz bigint set @invgab = (select top 1 CAR FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+
''', FIRSTROW = 1, MAXERRORS = 0, LASTROW=1) as xxx ) if (select count(*) from dbo.tblPebHdr where gabinvoice=@invgab) > 0 begin select ''09'' as status, ''Data Sudah Ada ''+CHAR(13)+CHAR(10)+'' Jika Akan Update Silahkan gunakan Menu Update+''+CHAR(13)+CHAR(10)+'' 0'' as description return end insert into dbo.tblPebHdr ( [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] ,[NAMABELI2] ,[ALMTBELI2] ,[NEGBELI2] ,[NOSHIPORD] ,[TGSHIPORD] ,[jnkemas] ,[gabinvoice] ,[filename] ,[laodeddate] ) SELECT ''-'' ,[JnEks] ,[KatEks] ,[JNPEB] ,[JNEKSOld] ,[JNLAIN] ,[JNDAG] ,[JNBYR] ,[IDEKS] ,(select top 1 NPWPEKS from dbo.tblEksportir) as [NPWPEKS] ,(select top 1 NpwpEks from dbo.tblEksportir) as [NamaEks] ,(select top 1 AlmtEks from dbo.tblEksportir) as [AlmtEks] ,(select top 1 NoSiup from dbo.tblEksportir) as [NoSiup] ,(select top 1 TgSiup from dbo.tblEksportir) as [TgSiup] ,(select top 1 JnPerus from dbo.tblEksportir) as [JnPerus] ,(select top 1 NoTdp from dbo.tblEksportir) as [NoTdp] ,(select top 1 TgTdp from dbo.tblEksportir) as [TgTdp] ,(select top 1 Niper from dbo.tblEksportir) as [Niper] ,(select top 1 StatusH from dbo.tblEksportir) as [StatusH] ,[IdQq] ,[NpwpQq] ,[NamaQq] ,[AlmtQq] ,[NiperQq] ,[NAMABELI] ,[ALMTBELI] ,case when (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGBELI]) is not null then (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGBELI]) else substring([NEGBELI], 1, 2) end ,[IDPPJK] ,[NPWPPPJK] ,[NamaPpjk] ,[AlmtPpjk] ,[NoPpjk] ,convert(datetime,[TgPpjk],103) ,[MODA] ,convert(datetime,[TGEKS],103) ,substring([CARRIER], 1, 20) as CARRIER ,[VOY] --,[NOSHIPORD] --,[TGSHIPORD] ,(case when [PELMUATEKS]=''IDCKG'' then ''IDCGK'' else [PELMUATEKS] end) as [PELMUAT] ,[PELBONGKAR] ,[PELTRANSIT] ,[NOINV] ,convert(datetime,[TGINV],103) ,[NOLPSE] ,convert(datetime,[TGLPSE],103) ,[PROPBRG] ,case when (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGTUJU]) is not null then (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGTUJU]) else substring([NEGTUJU], 1, 2) end ,[FLSIE] ,[FLKARANT] ,[FLSM] ,[FLIZLAIN] ,[DELIVERY] ,[KDVAL] ,[KdHrg] ,[NilInv] ,[FREIGHT] ,[ASURANSI] ,[KdAss] ,[NilInv] as FOB ,[MERK] ,[JMCONT] ,[BRUTO] ,[NETTO] ,[JMBRG] ,[JMBRGCTH] ,[JMBRGGAB] ,[JMBRGPJT] ,[NODAFT] ,convert(datetime,[TGDAFT], 103) ,[KDKTR] ,[KTRGATE] ,[NILPE] ,[KDBANK] ,[LOKBANK] ,[NOSSP] ,convert(datetime,[TGSSP], 103) ,[JNTPB] ,''1503000000'' as [LOKTPB] ,[KDLOKBRG] ,[KDKTRMUAT] ,[KDKTRPRIKS] ,convert(datetime,[TGSIAP], 103) ,[WKSIAP] ,[ASALDATA] ,[TPNAME] ,0 ,0 ,[JNPARTOF] ,[BENDERA] ,(select top 1 TtdPeb from dbo.tblEksportir) as [TTDPEB] ,''00'' ,[SNRF] ,[NOBCF] ,convert(datetime,[TGBCF], 103) ,[SERIBCF] ,[NOMOREDI] ,''1'' ,round(isnull([VOLUME], 0), 2) as [VOLUME] ,[KDHANGGAR] ,[KONS] ,[JMLKMSPEB] ,(case when [PELMUATEKS]=''IDCKG'' then ''IDCGK'' else [PELMUATEKS] end) as [PELMUATEKS] ,[NILKURS] ,0 as [PNBP] ,[NOBC11] ,convert(datetime,[TGBC11], 103) ,[POSBC11] ,[SUBPOSBC11] ,[NAMABELI2] ,[ALMTBELI2] ,[NEGBELI2] ,[NOSHIPORD] ,convert(datetime,[TGSHIPORD], 103) ,[JNKMSPEB] ,[CAR] ,'''+@FileName+
''' ,GETDATE() FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test set @idpebhdrz=@@IDENTITY insert into dbo.tblPebCon ( idpebhdr, CAR, NoCont, Size, Type, Stuff ) select @idpebhdrz, ''-'', REPLACE(NOCONT, '' '', '''') as NOCONT, CONTUKUR,(case when CONTTYPE=''F'' then ''1'' else '''' end) as CONTSIZE,CONTTYPE FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test group by test.NOCONT, test.CONTUKUR, test.CONTTYPE insert into dbo.tblPebKms ( [idpebhdr] ,[CAR] ,[JnKemas] ,[JmKemas] ) select @idpebhdrz, ''-'', JNKMSPEB, case when (select MODA from dbo.tblPebHdr where [idpebhdr]=@idpebhdrz) = ''4'' or (select NEGBELI from dbo.tblPebHdr where [idpebhdr]=@idpebhdrz) = ''US'' then sum(cast(JMKEMAS as float)) else isnull(sum(cast(JMBOX as float)), sum(cast(JMKEMAS as float))) end FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test group by JNKMSPEB insert into dbo.[tblPebDtl] ( [CAR] ,[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 ,gabinvoice ) select ''-'' ,REPLACE(HS, ''.'','''') ,URBRG1 as [URBRG1] ,substring(URBRG2, 1, 40) as [URBRG2] ,substring(URBRG2, 40, 40) as [URBRG3] ,URBRG4 as [URBRG4] ,DMERK as [DMERK] ,isnull(SIZE, ''-'') as [SIZE] ,isnull(TYPE, ''-'') as [TYPE] ,KDBRG as [KDBRG] ,KDIZIN as [KDIZIN] ,NOIZIN as [NOIZIN] ,TGIZIN as [TGIZIN] ,JMKOLI as [JMKOLI] ,JNKOLI as [JNKOLI] ,DNilInv as [DNilInv] ,FOBPERBRG as [FOBPERBRG] ,FOBPERSAT as [FOBPERSAT] ,KDPE as [KDPE] ,TARIPPE as [TARIPPE] ,HPATOK as [HPATOK] ,JMSATUAN as [JMSATUAN] ,JNSATUAN as [JNSATUAN] ,NETDET as [NETDET] ,JMSATPE as [JMSATPE] ,JNSATPE as [JNSATPE] ,KDVALPE as [KDVALPE] ,isnull(NILVALPE, 0) as [NILVALPE] ,isnull(PEPERBRG, 0) as [PEPERBRG] ,0 as [DTLOK] ,0 as [NCV] ,0 as [DVolume] ,[NegAsal] ,NOINV ,GABINVOICE FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfiledtl+''', FIRSTROW ='+@jmldetil+
', MAXERRORS = 0) as detil declare @car varchar(100), @noinv varchar(100), @TGINV varchar(100) declare @idpebhdr bigint DECLARE a_cursor CURSOR FOR select [CAR], [NOINV],[TGINV] FROM OPENROWSET( BULK '''+@fileinv+''',FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test OPEN a_cursor FETCH NEXT FROM a_cursor INTO @car, @noinv, @TGINV WHILE @@FETCH_STATUS = 0 BEGIN -- begin here set @idpebhdr = (select idpebhdr from dbo.tblPebHdr where gabinvoice=@car and NOINV=@noinv and TGINV=convert(datetime,@TGINV,103)) update tblPebDtl set idpebhdr=@idpebhdr, [TGINV]=convert(datetime,@TGINV,103) where gabinvoice=@car and NOINV=@noinv FETCH NEXT FROM a_cursor INTO @car, @noinv, @TGINV END CLOSE a_cursor DEALLOCATE a_cursor --select [CAR], [NOINV],[TGINV] --FROM OPENROWSET( BULK '''+@fileinv+
''', --FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test for xml path(''Header''), Elements XSINIL,root (''Data'') ')
END TRY
BEGIN CATCH
EXEC xp_cmdshell @cmdmove, NO_OUTPUT
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
Set @ErrMsg = ERROR_MESSAGE()
set @ErrSeverity = ERROR_SEVERITY()
select @ErrSeverity as status, @ErrMsg as description
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
update dbo.tbl_upload_bc30 set status_upload='Data Processed' where filename=@FileName
EXEC xp_cmdshell @cmdmove, NO_OUTPUT
set @uruterror = @uruterror + 1;
FETCH NEXT FROM file_cursor INTO @Size, @FileName
END
CLOSE file_cursor
DEALLOCATE file_cursor
--select * from tblPebHdr
--select * from tblPebDtl
select '00' as status, COUNT(*) as description from @Files
END
GO
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.spi_getdata_bc30
-- Add the parameters for the stored procedure here
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @filecount int, @uruterror int
Declare @Size varchar(50)
Declare @FileName varchar(50)
declare @fileinv varchar(100), @logerror varchar(100), @logerror_dtl varchar(100)
declare @cmdmove varchar(255)
DECLARE @Files table(Names varchar(250) null)
Declare @mdate varchar(14)
Declare @dir varchar(100)
declare @dircsv varchar(500)
-- for bulk
declare @tbl_load_bc30_all varchar(MAX)
declare @filedir varchar(400)
declare @formatfile varchar(400)
declare @formatfiledtl varchar(400)
----
set @dir = (select top 1 direktori from dbo.tbl_direktori)
set @filedir=@dir+'\exim\bc30'
set @dircsv='dir '+@dir+'\exim\bc30\'+'*.csv'
set @formatfile = @dir+'\exim\bc30\format\'+'fmt2header.xml'
set @formatfiledtl = @dir+'\exim\bc30\format\'+'fmt2detil.xml'
INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircsv
set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
if @filecount = 1
begin
select '01: ' as status, names as description from @Files where Names like '%File Not Found%'
return
end
DELETE @Files WHERE Names LIKE '%
--delete all informational messages
DELETE @Files WHERE Names LIKE ' %'
--delete the null values
DELETE @Files WHERE Names IS NULL
--get rid of dateinfo
UPDATE @Files SET Names =RIGHT(Names,(LEN(Names)-20))
--get rid of leading spaces
UPDATE @Files SET Names =LTRIM(Names)
--split data into size and filename
Declare @dynmaicsql as nvarchar(1000);
Declare @dynamicparam as nvarchar(1000);
declare @jmlheader int
set @uruterror=1
DECLARE file_cursor CURSOR FOR
SELECT LEFT(Names,PATINDEX('% %',Names)) AS Size, RIGHT(Names,LEN(Names) -PATINDEX('% %',Names)) AS FileName FROM @Files where Names like '%.csv' order by Names
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @Size, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN -- begin here
set @fileinv = @filedir +'\' + @FileName
set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @FileName
set @mdate = (SELECT CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':',''))
set @logerror = @filedir+'\log\error' + @mdate + CONVERT(VARCHAR, @uruterror) + '.txt'
set @logerror_dtl =@filedir+'\log\errordtl' + @mdate + CONVERT(VARCHAR, @uruterror) + '.txt'
Set @dynmaicsql =
' set @jmlhdr = (select COUNT(*) as cnt FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
'] '') where F1 = ''H0'') '
Set @dynamicparam = '@jmlhdr int output'
execute sp_executesql @dynmaicsql, @dynamicparam, @jmlheader output
declare @jmldetil int
set @jmldetil = @jmlheader+1
BEGIN TRY
exec(
' declare @invgab varchar(100) declare @idpebhdrz bigint set @invgab = (select top 1 CAR FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+
''', FIRSTROW = 1, MAXERRORS = 0, LASTROW=1) as xxx ) if (select count(*) from dbo.tblPebHdr where gabinvoice=@invgab) > 0 begin select ''09'' as status, ''Data Sudah Ada ''+CHAR(13)+CHAR(10)+'' Jika Akan Update Silahkan gunakan Menu Update+''+CHAR(13)+CHAR(10)+'' 0'' as description return end insert into dbo.tblPebHdr ( [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] ,[NAMABELI2] ,[ALMTBELI2] ,[NEGBELI2] ,[NOSHIPORD] ,[TGSHIPORD] ,[jnkemas] ,[gabinvoice] ,[filename] ,[laodeddate] ) SELECT ''-'' ,[JnEks] ,[KatEks] ,[JNPEB] ,[JNEKSOld] ,[JNLAIN] ,[JNDAG] ,[JNBYR] ,[IDEKS] ,(select top 1 NPWPEKS from dbo.tblEksportir) as [NPWPEKS] ,(select top 1 NpwpEks from dbo.tblEksportir) as [NamaEks] ,(select top 1 AlmtEks from dbo.tblEksportir) as [AlmtEks] ,(select top 1 NoSiup from dbo.tblEksportir) as [NoSiup] ,(select top 1 TgSiup from dbo.tblEksportir) as [TgSiup] ,(select top 1 JnPerus from dbo.tblEksportir) as [JnPerus] ,(select top 1 NoTdp from dbo.tblEksportir) as [NoTdp] ,(select top 1 TgTdp from dbo.tblEksportir) as [TgTdp] ,(select top 1 Niper from dbo.tblEksportir) as [Niper] ,(select top 1 StatusH from dbo.tblEksportir) as [StatusH] ,[IdQq] ,[NpwpQq] ,[NamaQq] ,[AlmtQq] ,[NiperQq] ,[NAMABELI] ,[ALMTBELI] ,case when (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGBELI]) is not null then (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGBELI]) else substring([NEGBELI], 1, 2) end ,[IDPPJK] ,[NPWPPPJK] ,[NamaPpjk] ,[AlmtPpjk] ,[NoPpjk] ,convert(datetime,[TgPpjk],103) ,[MODA] ,convert(datetime,[TGEKS],103) ,substring([CARRIER], 1, 20) as CARRIER ,[VOY] --,[NOSHIPORD] --,[TGSHIPORD] ,(case when [PELMUATEKS]=''IDCKG'' then ''IDCGK'' else [PELMUATEKS] end) as [PELMUAT] ,[PELBONGKAR] ,[PELTRANSIT] ,[NOINV] ,convert(datetime,[TGINV],103) ,[NOLPSE] ,convert(datetime,[TGLPSE],103) ,[PROPBRG] ,case when (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGTUJU]) is not null then (select top 1 tblNegara.KdEdi from dbo.tblNegara where Konversi = [NEGTUJU]) else substring([NEGTUJU], 1, 2) end ,[FLSIE] ,[FLKARANT] ,[FLSM] ,[FLIZLAIN] ,[DELIVERY] ,[KDVAL] ,[KdHrg] ,[NilInv] ,[FREIGHT] ,[ASURANSI] ,[KdAss] ,[NilInv] as FOB ,[MERK] ,[JMCONT] ,[BRUTO] ,[NETTO] ,[JMBRG] ,[JMBRGCTH] ,[JMBRGGAB] ,[JMBRGPJT] ,[NODAFT] ,convert(datetime,[TGDAFT], 103) ,[KDKTR] ,[KTRGATE] ,[NILPE] ,[KDBANK] ,[LOKBANK] ,[NOSSP] ,convert(datetime,[TGSSP], 103) ,[JNTPB] ,''1503000000'' as [LOKTPB] ,[KDLOKBRG] ,[KDKTRMUAT] ,[KDKTRPRIKS] ,convert(datetime,[TGSIAP], 103) ,[WKSIAP] ,[ASALDATA] ,[TPNAME] ,0 ,0 ,[JNPARTOF] ,[BENDERA] ,(select top 1 TtdPeb from dbo.tblEksportir) as [TTDPEB] ,''00'' ,[SNRF] ,[NOBCF] ,convert(datetime,[TGBCF], 103) ,[SERIBCF] ,[NOMOREDI] ,''1'' ,round(isnull([VOLUME], 0), 2) as [VOLUME] ,[KDHANGGAR] ,[KONS] ,[JMLKMSPEB] ,(case when [PELMUATEKS]=''IDCKG'' then ''IDCGK'' else [PELMUATEKS] end) as [PELMUATEKS] ,[NILKURS] ,0 as [PNBP] ,[NOBC11] ,convert(datetime,[TGBC11], 103) ,[POSBC11] ,[SUBPOSBC11] ,[NAMABELI2] ,[ALMTBELI2] ,[NEGBELI2] ,[NOSHIPORD] ,convert(datetime,[TGSHIPORD], 103) ,[JNKMSPEB] ,[CAR] ,'''+@FileName+
''' ,GETDATE() FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test set @idpebhdrz=@@IDENTITY insert into dbo.tblPebCon ( idpebhdr, CAR, NoCont, Size, Type, Stuff ) select @idpebhdrz, ''-'', REPLACE(NOCONT, '' '', '''') as NOCONT, CONTUKUR,(case when CONTTYPE=''F'' then ''1'' else '''' end) as CONTSIZE,CONTTYPE FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test group by test.NOCONT, test.CONTUKUR, test.CONTTYPE insert into dbo.tblPebKms ( [idpebhdr] ,[CAR] ,[JnKemas] ,[JmKemas] ) select @idpebhdrz, ''-'', JNKMSPEB, case when (select MODA from dbo.tblPebHdr where [idpebhdr]=@idpebhdrz) = ''4'' or (select NEGBELI from dbo.tblPebHdr where [idpebhdr]=@idpebhdrz) = ''US'' then sum(cast(JMKEMAS as float)) else isnull(sum(cast(JMBOX as float)), sum(cast(JMKEMAS as float))) end FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test group by JNKMSPEB insert into dbo.[tblPebDtl] ( [CAR] ,[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 ,gabinvoice ) select ''-'' ,REPLACE(HS, ''.'','''') ,URBRG1 as [URBRG1] ,substring(URBRG2, 1, 40) as [URBRG2] ,substring(URBRG2, 40, 40) as [URBRG3] ,URBRG4 as [URBRG4] ,DMERK as [DMERK] ,isnull(SIZE, ''-'') as [SIZE] ,isnull(TYPE, ''-'') as [TYPE] ,KDBRG as [KDBRG] ,KDIZIN as [KDIZIN] ,NOIZIN as [NOIZIN] ,TGIZIN as [TGIZIN] ,JMKOLI as [JMKOLI] ,JNKOLI as [JNKOLI] ,DNilInv as [DNilInv] ,FOBPERBRG as [FOBPERBRG] ,FOBPERSAT as [FOBPERSAT] ,KDPE as [KDPE] ,TARIPPE as [TARIPPE] ,HPATOK as [HPATOK] ,JMSATUAN as [JMSATUAN] ,JNSATUAN as [JNSATUAN] ,NETDET as [NETDET] ,JMSATPE as [JMSATPE] ,JNSATPE as [JNSATPE] ,KDVALPE as [KDVALPE] ,isnull(NILVALPE, 0) as [NILVALPE] ,isnull(PEPERBRG, 0) as [PEPERBRG] ,0 as [DTLOK] ,0 as [NCV] ,0 as [DVolume] ,[NegAsal] ,NOINV ,GABINVOICE FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfiledtl+''', FIRSTROW ='+@jmldetil+
', MAXERRORS = 0) as detil declare @car varchar(100), @noinv varchar(100), @TGINV varchar(100) declare @idpebhdr bigint DECLARE a_cursor CURSOR FOR select [CAR], [NOINV],[TGINV] FROM OPENROWSET( BULK '''+@fileinv+''',FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test OPEN a_cursor FETCH NEXT FROM a_cursor INTO @car, @noinv, @TGINV WHILE @@FETCH_STATUS = 0 BEGIN -- begin here set @idpebhdr = (select idpebhdr from dbo.tblPebHdr where gabinvoice=@car and NOINV=@noinv and TGINV=convert(datetime,@TGINV,103)) update tblPebDtl set idpebhdr=@idpebhdr, [TGINV]=convert(datetime,@TGINV,103) where gabinvoice=@car and NOINV=@noinv FETCH NEXT FROM a_cursor INTO @car, @noinv, @TGINV END CLOSE a_cursor DEALLOCATE a_cursor --select [CAR], [NOINV],[TGINV] --FROM OPENROWSET( BULK '''+@fileinv+
''', --FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test for xml path(''Header''), Elements XSINIL,root (''Data'') ')
END TRY
BEGIN CATCH
EXEC xp_cmdshell @cmdmove, NO_OUTPUT
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
Set @ErrMsg = ERROR_MESSAGE()
set @ErrSeverity = ERROR_SEVERITY()
select @ErrSeverity as status, @ErrMsg as description
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
update dbo.tbl_upload_bc30 set status_upload='Data Processed' where filename=@FileName
EXEC xp_cmdshell @cmdmove, NO_OUTPUT
set @uruterror = @uruterror + 1;
FETCH NEXT FROM file_cursor INTO @Size, @FileName
END
CLOSE file_cursor
DEALLOCATE file_cursor
--select * from tblPebHdr
--select * from tblPebDtl
select '00' as status, COUNT(*) as description from @Files
END
GO
Depends On
2Used By
No items found