Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @filename | varchar | 100 | |
| @idfile | bigint | 8 | |
| @username | varchar | 70 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.spi_getdata_bc27
-- Add the parameters for the stored procedure here
(@filename varchar(100), @idfile bigint, @username varchar(70) )
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @filecount int, @uruterror int
Declare @Size 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_bc27_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\bc27'
set @dircsv='dir '+@dir+'\exim\bc27\'+'*.csv'
set @formatfile = @dir+'\exim\bc27\format\'+'fmt2header.xml'
set @formatfiledtl = @dir+'\exim\bc27\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
Declare @dynmaicsql as nvarchar(1000);
Declare @dynamicparam as nvarchar(1000);
declare @jmlheader int
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 @tbl table ( part_no varchar(100) ,assy_code varchar(100) ) insert into @tbl ( part_no, assy_code ) select F6, F13 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
'] '') where F1 = ''D0'' declare @cntnull_part int, @cntnull_assy int set @cntnull_part = (select count(*) from ( select b.part_no2 from @tbl a left outer join tbl_data_part_hs b on REPLACE(a.part_no,'' '','''') = b.part_no2 where b.part_no2 is null ) x ) set @cntnull_assy = ( select count(*) from ( select b.ASSY_CODE from @tbl a left outer join tbl_assy_list b on REPLACE(a.assy_code,'' '','''') = b.ASSY_CODE where b.ASSY_CODE is null ) x ) --select @cntnull_part, @cntnull_assy if @cntnull_part <> 0 and @cntnull_assy <> 0 begin select ''99'' as status, ''PARTNUMBER atau ASSYCODE tidak ditemukan'' as description return end if @cntnull_part = 0 and @cntnull_assy = 0 begin select ''99'' as status, ''PARTNUMBER atau ASSYCODE sama code nya.. Hub. admin'' as description return end declare @jns_brg varchar(10) if @cntnull_part = 0 begin set @jns_brg = ''B'' end if @cntnull_assy = 0 begin set @jns_brg = ''F'' end insert into dbo.tblbc27Hdr ( [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] ,[jnkemas] ,[gabinvoice] ,[filename] ,[laodeddate] ) SELECT ''-'' ,[JnEks] ,[KatEks] ,[JNPEB] ,@jns_brg as [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] ,''ID'' as [NEGBELI] ,[IDPPJK] ,[NPWPPPJK] ,[NamaPpjk] ,[AlmtPpjk] ,[NoPpjk] ,convert(datetime,[TgPpjk],103) ,[MODA] ,convert(datetime,[TGEKS],103) ,[CARRIER] ,[VOY] ,[NOSHIPORD] ,[TGSHIPORD] ,[PELMUAT] ,[PELBONGKAR] ,[PELTRANSIT] ,[NOINV] ,convert(datetime,[TGINV],103) ,[NOLPSE] ,convert(datetime,[TGLPSE],103) ,[PROPBRG] ,[NEGTUJU] ,[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] ,[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'' ,[VOLUME] ,[KDHANGGAR] ,[KONS] ,[JMLKMSPEB] ,[PELMUATEKS] ,[NILKURS] ,[PNBP] ,[NOBC11] ,convert(datetime,[TGBC11], 103) ,[POSBC11] ,[SUBPOSBC11] ,[JNKMSPEB] ,[CAR] ,'''+@FileName+
''' ,GETDATE() FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test declare @idbc27hdr1 bigint set @idbc27hdr1 = @@IDENTITY insert into dbo.[tblbc27dtl] ( [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] ,URBRG1 as [TYPE] ,(case when @jns_brg = ''B'' then (select top 1 x.kode_barang from tbl_data_part_hs x where x.part_no2 = REPLACE(URBRG1,'' '','''') ) else KDBRG end ) as [KDBRG] ,KDIZIN as [KDIZIN] ,NOIZIN as [NOIZIN] ,TGIZIN as [TGIZIN] ,isnull(JMKOLI, 0) as [JMKOLI] ,JNKOLI as [JNKOLI] ,isnull(DNilInv, 0) as [DNilInv] ,isnull(FOBPERBRG, 0) as [FOBPERBRG] ,isnull(FOBPERSAT, 0) as [FOBPERSAT] ,KDPE as [KDPE] ,TARIPPE as [TARIPPE] ,HPATOK as [HPATOK] ,isnull(JMSATUAN, 0) as [JMSATUAN] ,JNSATUAN as [JNSATUAN] ,isnull(NETDET, 0) as [NETDET] ,isnull(JMSATPE, 0) 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 @idbc27hdr 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 @idbc27hdr = (select idbc27hdr from dbo.tblbc27Hdr where gabinvoice=@car and NOINV=@noinv and TGINV=convert(datetime,@TGINV,103)) set @idbc27hdr = @idbc27hdr1 update tblbc27Dtl set idbc27hdr=@idbc27hdr, [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'') select ''00'' as status, ''SUKSES'' as description ')
END TRY
BEGIN CATCH
select ERROR_NUMBER() as status, ERROR_MESSAGE() as description
END CATCH;
update dbo.tbl_upload_bc27 set status_upload='Data Processed' where filename=@FileName
EXEC xp_cmdshell @cmdmove, NO_OUTPUT
--select * from tblPebHdr
--select * from tblPebDtl
--select '00' as status, 'SUKSES' as description
END
--go
-- exec('[spi_getdata_bc27] @filename=''BC30-PE2500022BSA202006212.csv'', @idfile=162710, @username=''admin'' ')
GO
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.spi_getdata_bc27
-- Add the parameters for the stored procedure here
(@filename varchar(100), @idfile bigint, @username varchar(70) )
AS
BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @filecount int, @uruterror int
Declare @Size 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_bc27_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\bc27'
set @dircsv='dir '+@dir+'\exim\bc27\'+'*.csv'
set @formatfile = @dir+'\exim\bc27\format\'+'fmt2header.xml'
set @formatfiledtl = @dir+'\exim\bc27\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
Declare @dynmaicsql as nvarchar(1000);
Declare @dynamicparam as nvarchar(1000);
declare @jmlheader int
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 @tbl table ( part_no varchar(100) ,assy_code varchar(100) ) insert into @tbl ( part_no, assy_code ) select F6, F13 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@FileName+
'] '') where F1 = ''D0'' declare @cntnull_part int, @cntnull_assy int set @cntnull_part = (select count(*) from ( select b.part_no2 from @tbl a left outer join tbl_data_part_hs b on REPLACE(a.part_no,'' '','''') = b.part_no2 where b.part_no2 is null ) x ) set @cntnull_assy = ( select count(*) from ( select b.ASSY_CODE from @tbl a left outer join tbl_assy_list b on REPLACE(a.assy_code,'' '','''') = b.ASSY_CODE where b.ASSY_CODE is null ) x ) --select @cntnull_part, @cntnull_assy if @cntnull_part <> 0 and @cntnull_assy <> 0 begin select ''99'' as status, ''PARTNUMBER atau ASSYCODE tidak ditemukan'' as description return end if @cntnull_part = 0 and @cntnull_assy = 0 begin select ''99'' as status, ''PARTNUMBER atau ASSYCODE sama code nya.. Hub. admin'' as description return end declare @jns_brg varchar(10) if @cntnull_part = 0 begin set @jns_brg = ''B'' end if @cntnull_assy = 0 begin set @jns_brg = ''F'' end insert into dbo.tblbc27Hdr ( [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] ,[jnkemas] ,[gabinvoice] ,[filename] ,[laodeddate] ) SELECT ''-'' ,[JnEks] ,[KatEks] ,[JNPEB] ,@jns_brg as [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] ,''ID'' as [NEGBELI] ,[IDPPJK] ,[NPWPPPJK] ,[NamaPpjk] ,[AlmtPpjk] ,[NoPpjk] ,convert(datetime,[TgPpjk],103) ,[MODA] ,convert(datetime,[TGEKS],103) ,[CARRIER] ,[VOY] ,[NOSHIPORD] ,[TGSHIPORD] ,[PELMUAT] ,[PELBONGKAR] ,[PELTRANSIT] ,[NOINV] ,convert(datetime,[TGINV],103) ,[NOLPSE] ,convert(datetime,[TGLPSE],103) ,[PROPBRG] ,[NEGTUJU] ,[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] ,[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'' ,[VOLUME] ,[KDHANGGAR] ,[KONS] ,[JMLKMSPEB] ,[PELMUATEKS] ,[NILKURS] ,[PNBP] ,[NOBC11] ,convert(datetime,[TGBC11], 103) ,[POSBC11] ,[SUBPOSBC11] ,[JNKMSPEB] ,[CAR] ,'''+@FileName+
''' ,GETDATE() FROM OPENROWSET( BULK '''+@fileinv+
''', FORMATFILE = '''+@formatfile+''', FIRSTROW = 1, MAXERRORS = 0, LASTROW='+@jmlheader+
') as test declare @idbc27hdr1 bigint set @idbc27hdr1 = @@IDENTITY insert into dbo.[tblbc27dtl] ( [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] ,URBRG1 as [TYPE] ,(case when @jns_brg = ''B'' then (select top 1 x.kode_barang from tbl_data_part_hs x where x.part_no2 = REPLACE(URBRG1,'' '','''') ) else KDBRG end ) as [KDBRG] ,KDIZIN as [KDIZIN] ,NOIZIN as [NOIZIN] ,TGIZIN as [TGIZIN] ,isnull(JMKOLI, 0) as [JMKOLI] ,JNKOLI as [JNKOLI] ,isnull(DNilInv, 0) as [DNilInv] ,isnull(FOBPERBRG, 0) as [FOBPERBRG] ,isnull(FOBPERSAT, 0) as [FOBPERSAT] ,KDPE as [KDPE] ,TARIPPE as [TARIPPE] ,HPATOK as [HPATOK] ,isnull(JMSATUAN, 0) as [JMSATUAN] ,JNSATUAN as [JNSATUAN] ,isnull(NETDET, 0) as [NETDET] ,isnull(JMSATPE, 0) 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 @idbc27hdr 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 @idbc27hdr = (select idbc27hdr from dbo.tblbc27Hdr where gabinvoice=@car and NOINV=@noinv and TGINV=convert(datetime,@TGINV,103)) set @idbc27hdr = @idbc27hdr1 update tblbc27Dtl set idbc27hdr=@idbc27hdr, [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'') select ''00'' as status, ''SUKSES'' as description ')
END TRY
BEGIN CATCH
select ERROR_NUMBER() as status, ERROR_MESSAGE() as description
END CATCH;
update dbo.tbl_upload_bc27 set status_upload='Data Processed' where filename=@FileName
EXEC xp_cmdshell @cmdmove, NO_OUTPUT
--select * from tblPebHdr
--select * from tblPebDtl
--select '00' as status, 'SUKSES' as description
END
--go
-- exec('[spi_getdata_bc27] @filename=''BC30-PE2500022BSA202006212.csv'', @idfile=162710, @username=''admin'' ')
GO
Depends On
2Used By
No items found