Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @datadiff | varchar | -1 | |
| @userid | varchar | 100 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_proses_bc27_new
(@datadiff varchar(max), @userid varchar(100))
AS
BEGIN
declare @datasplit varchar(8000)
declare @idbc27hdr bigint
declare @gabinvoice varchar(500)
declare @NOINV varchar (500)
declare @NAMA_PT varchar (500)
declare @wherid varchar(500)
declare @noproses varchar(50)
declare @hasil varchar(50)
declare @tbl table(gabinvoice varchar(100) null, namapt varchar(50) null)
exec [dbo].[spu_seq] @nomor_untuk = N'nobc27', @hasil = @hasil OUTPUT
set @noproses = @hasil
declare @status varchar(50)
set @wherid = ''
DECLARE a_cursor CURSOR FOR select f_result from dbo.fn_ParseCSVString (@datadiff, '|')
OPEN a_cursor
FETCH NEXT FROM a_cursor INTO @datasplit
WHILE @@FETCH_STATUS = 0
BEGIN -- begin here
set @idbc27hdr =(select data from dbo.Split(@datasplit, ',') where id=1)
set @gabinvoice =(select data from dbo.Split(@datasplit, ',') where id=2)
set @NOINV =(select data from dbo.Split(@datasplit, ',') where id=3)
set @NAMA_PT =(select data from dbo.Split(@datasplit, ',') where id=4)
insert into @tbl (gabinvoice, namapt) values (@gabinvoice, @NAMA_PT)
if(select COUNT(*) from dbo.tblbc27hdr where idbc27hdr=@idbc27hdr) = 0
begin
CLOSE a_cursor
DEALLOCATE a_cursor
select '06' as status, '(ADA) DATA TIDAK DITEMUKAN, SILAHKAN ULANGI PEMILIHAN DATA' as description
return
end
set @status = (select top 1 STATUS from dbo.tblbc27hdr where idbc27hdr=@idbc27hdr)
if @status <> '00'
begin
CLOSE a_cursor
DEALLOCATE a_cursor
select '07' as status, 'DATA SUDAH TERPORSES INV nya '+@gabinvoice as description
return
end
set @status = (select top 1 noprosesdata from dbo.tblbc27hdr where idbc27hdr=@idbc27hdr)
if @status is not null
begin
CLOSE a_cursor
DEALLOCATE a_cursor
select '07' as status, 'DATA SUDAH TERPORSES '+@gabinvoice as description
return
end
set @wherid = @wherid + '' + CAST(@idbc27hdr as varchar) + ','
FETCH NEXT FROM a_cursor INTO @datasplit
END
CLOSE a_cursor
DEALLOCATE a_cursor
declare @where varchar(500)
set @where = ' idbc27hdr in ( ' + SUBSTRING(@wherid, 0, LEN(@wherid) ) + ' )'
if(select count(*) from @tbl) = 0
begin
select '07' as status, 'pilih dahulu datanya' as description
return
end
if(select count(gabinvoice) from @tbl group by gabinvoice)> 1
begin
select '07' as status, 'Data pilihan tidak sama (gabungan invoice)' as description
return
end
if(select count(namapt) from @tbl group by namapt)> 1
begin
select '07' as status, 'Data pilihan tidak sama (nama beli)' as description
return
end
BEGIN TRY
BEGIN TRANSACTION
exec(
' update [tblbc27hdr] set noprosesdata = '''+@noproses+''' where 1=1 and '+ @where +
' declare @now date set @now = GETDATE() declare @id_hdr bigint insert into dbo.tbl_bc_out_hdr ( [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] ,[typedok] ,[statusdok] ,[bc_in_from_in] ,[noinv1] ,[tginv1] ) select top 1 ''-'' ,[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] ,(SELECT isnull(SUM(dbo.tblbc27Hdr.NilInv),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS NilInv ,(SELECT isnull(SUM(dbo.tblbc27Hdr.FREIGHT),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS FREIGHT ,(SELECT isnull(SUM(dbo.tblbc27Hdr.ASURANSI),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS ASURANSI ,KdAss ,(SELECT isnull(SUM(dbo.tblbc27Hdr.FOB),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS FOB ,MERK ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMCONT),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMCONT ,(SELECT isnull(SUM(dbo.tblbc27Hdr.BRUTO),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS BRUTO ,(SELECT isnull(SUM(dbo.tblbc27Hdr.NETTO),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS NETTO ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRG),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMBRG ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRGCTH),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMBRGCTH ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRGGAB),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMBRGGAB ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRGPJT),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS 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] ,@now ,'''+@userid+
''' ,@now ,'''+@userid+
''' ,''BC27'' ,''DATA PROCESSED'' ,''SA'' + (SELECT top 1 noprosesdata FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS bc_in_from_in ,'''+@noproses+
''' ,GETDATE() from [tblbc27hdr] where 1=1 and '+ @where +
' set @id_hdr = @@IDENTITY insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_hdr ,'''+@noproses+
''' ,''380'' ,[NOINV] ,[TGINV] from tblbc27Hdr where 1=1 and '+ @where +
' insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_hdr ,'''+@noproses+
''' ,''217'' ,[NOINV] ,[TGINV] from tblbc27Hdr where 1=1 and '+ @where +
' insert into dbo.tbl_bc_out_dtl ( [id_bc_out_hdr] ,[CAR] ,[SERIBRG] ,[HS] ,[URBRG1] ,[URBRG2] ,[URBRG3] ,[URBRG4] ,[DMERK] ,[SIZE] ,[TYPE] ,[KDBRG] ,[JMKOLI] ,[JNKOLI] ,[DNilInv] ,[FOBPERBRG] ,[FOBPERSAT] ,[JMSATUAN] ,[JNSATUAN] ,[NETDET] ,[JMSATPE] ,[JNSATPE] ,[DTLOK] ,[DVolume] ,[NegAsal] ) select @id_hdr ,'''+@noproses+
''' ,row_number() over(order by (select 0)) ,HS ,URBRG1 ,URBRG2 ,URBRG3 ,URBRG4 ,DMERK ,SIZE ,TYPE ,KDBRG ,SUM(JMKOLI) AS jmkoli ,JNKOLI ,SUM(DNilInv) AS DNilInv ,SUM(FOBPERBRG) AS FOBPERBRG ,SUM(FOBPERSAT) AS FOBPERSAT ,SUM(JMSATUAN) AS JMSATUAN ,JNSATUAN ,SUM(NETDET) AS NETDET ,SUM(JMSATPE) AS JMSATPE ,[JNSATPE] ,0 ,SUM(DVolume) AS DVolume ,[NegAsal] FROM dbo.[tblbc27dtl] WHERE 1=1 and '+ @where +
' GROUP BY dbo.tblbc27dtl.HS, dbo.tblbc27dtl.URBRG1, dbo.tblbc27dtl.URBRG2, dbo.tblbc27dtl.URBRG3, dbo.tblbc27dtl.URBRG4, dbo.tblbc27dtl.DMERK, dbo.tblbc27dtl.SIZE, dbo.tblbc27dtl.TYPE, dbo.tblbc27dtl.KDBRG, dbo.tblbc27dtl.JNKOLI, dbo.tblbc27dtl.JNSATUAN, dbo.tblbc27dtl.JNSATPE, dbo.tblbc27dtl.NegAsal update [tblbc27hdr] set status = ''01'' where 1=1 and '+ @where +
' update dbo.tbl_bc_out_hdr set JMBRG=(select count(*) as cnt from dbo.tbl_bc_out_dtl where id_bc_out_hdr=@id_hdr) where id_bc_out_hdr=@id_hdr -- update histori INSERT INTO tbl_bc_out_hdr_h_status (id_bc_out_hdr, statusdok, status_date ,[create_by] ,[create_date] ,[update_by] ,[update_date]) VALUES (@id_hdr, (select top 1 statusdok from tbl_bc_out_hdr where id_bc_out_hdr = @id_hdr), @now ,'''+@userid+
''' ,@now ,'''+@userid+
''' ,@now ) ')
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
Set @ErrMsg = ERROR_MESSAGE()
set @ErrSeverity = ERROR_SEVERITY()
select @ErrMsg as description,@ErrSeverity as status
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
--select * from dbo.tbl_bc_out_hdr
--select * from dbo.tbl_bc_out_dok
--select * from dbo.tbl_bc_out_dtl
-- delete from tbl_bc_out_hdr_h_status
--delete from dbo.tbl_bc_out_hdr
--delete from dbo.tbl_bc_out_dok
--delete from dbo.tbl_bc_out_dtl
select '00' as status, 'Data Procesed' as description
END
--go
--EXEC ('[spi_proses_bc27_new] @datadiff=''21019,900012 (PE9914008B),PE9914008B,PT. SEMARANG AUTOCOMP MANUFACTURING IND.|21018,900013 (PE9914003B),PE9914003B,PT. SURABAYA AUTOCOMP INDONESIA|21017,900014 (PE9914003B),PE9914003B,PT. JATIM AUTOCOMP INDONESIA|21016,900015 (PE9914009B),PE9914009B,PT. SEMARANG AUTOCOMP MANUFACTURING IND.|'', @userid=''admin'' ')
GO
GO
CREATE PROCEDURE dbo.spi_proses_bc27_new
(@datadiff varchar(max), @userid varchar(100))
AS
BEGIN
declare @datasplit varchar(8000)
declare @idbc27hdr bigint
declare @gabinvoice varchar(500)
declare @NOINV varchar (500)
declare @NAMA_PT varchar (500)
declare @wherid varchar(500)
declare @noproses varchar(50)
declare @hasil varchar(50)
declare @tbl table(gabinvoice varchar(100) null, namapt varchar(50) null)
exec [dbo].[spu_seq] @nomor_untuk = N'nobc27', @hasil = @hasil OUTPUT
set @noproses = @hasil
declare @status varchar(50)
set @wherid = ''
DECLARE a_cursor CURSOR FOR select f_result from dbo.fn_ParseCSVString (@datadiff, '|')
OPEN a_cursor
FETCH NEXT FROM a_cursor INTO @datasplit
WHILE @@FETCH_STATUS = 0
BEGIN -- begin here
set @idbc27hdr =(select data from dbo.Split(@datasplit, ',') where id=1)
set @gabinvoice =(select data from dbo.Split(@datasplit, ',') where id=2)
set @NOINV =(select data from dbo.Split(@datasplit, ',') where id=3)
set @NAMA_PT =(select data from dbo.Split(@datasplit, ',') where id=4)
insert into @tbl (gabinvoice, namapt) values (@gabinvoice, @NAMA_PT)
if(select COUNT(*) from dbo.tblbc27hdr where idbc27hdr=@idbc27hdr) = 0
begin
CLOSE a_cursor
DEALLOCATE a_cursor
select '06' as status, '(ADA) DATA TIDAK DITEMUKAN, SILAHKAN ULANGI PEMILIHAN DATA' as description
return
end
set @status = (select top 1 STATUS from dbo.tblbc27hdr where idbc27hdr=@idbc27hdr)
if @status <> '00'
begin
CLOSE a_cursor
DEALLOCATE a_cursor
select '07' as status, 'DATA SUDAH TERPORSES INV nya '+@gabinvoice as description
return
end
set @status = (select top 1 noprosesdata from dbo.tblbc27hdr where idbc27hdr=@idbc27hdr)
if @status is not null
begin
CLOSE a_cursor
DEALLOCATE a_cursor
select '07' as status, 'DATA SUDAH TERPORSES '+@gabinvoice as description
return
end
set @wherid = @wherid + '' + CAST(@idbc27hdr as varchar) + ','
FETCH NEXT FROM a_cursor INTO @datasplit
END
CLOSE a_cursor
DEALLOCATE a_cursor
declare @where varchar(500)
set @where = ' idbc27hdr in ( ' + SUBSTRING(@wherid, 0, LEN(@wherid) ) + ' )'
if(select count(*) from @tbl) = 0
begin
select '07' as status, 'pilih dahulu datanya' as description
return
end
if(select count(gabinvoice) from @tbl group by gabinvoice)> 1
begin
select '07' as status, 'Data pilihan tidak sama (gabungan invoice)' as description
return
end
if(select count(namapt) from @tbl group by namapt)> 1
begin
select '07' as status, 'Data pilihan tidak sama (nama beli)' as description
return
end
BEGIN TRY
BEGIN TRANSACTION
exec(
' update [tblbc27hdr] set noprosesdata = '''+@noproses+''' where 1=1 and '+ @where +
' declare @now date set @now = GETDATE() declare @id_hdr bigint insert into dbo.tbl_bc_out_hdr ( [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] ,[typedok] ,[statusdok] ,[bc_in_from_in] ,[noinv1] ,[tginv1] ) select top 1 ''-'' ,[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] ,(SELECT isnull(SUM(dbo.tblbc27Hdr.NilInv),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS NilInv ,(SELECT isnull(SUM(dbo.tblbc27Hdr.FREIGHT),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS FREIGHT ,(SELECT isnull(SUM(dbo.tblbc27Hdr.ASURANSI),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS ASURANSI ,KdAss ,(SELECT isnull(SUM(dbo.tblbc27Hdr.FOB),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS FOB ,MERK ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMCONT),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMCONT ,(SELECT isnull(SUM(dbo.tblbc27Hdr.BRUTO),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS BRUTO ,(SELECT isnull(SUM(dbo.tblbc27Hdr.NETTO),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS NETTO ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRG),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMBRG ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRGCTH),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMBRGCTH ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRGGAB),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS JMBRGGAB ,(SELECT isnull(SUM(dbo.tblbc27Hdr.JMBRGPJT),0) FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS 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] ,@now ,'''+@userid+
''' ,@now ,'''+@userid+
''' ,''BC27'' ,''DATA PROCESSED'' ,''SA'' + (SELECT top 1 noprosesdata FROM dbo.tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' ) AS bc_in_from_in ,'''+@noproses+
''' ,GETDATE() from [tblbc27hdr] where 1=1 and '+ @where +
' set @id_hdr = @@IDENTITY insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_hdr ,'''+@noproses+
''' ,''380'' ,[NOINV] ,[TGINV] from tblbc27Hdr where 1=1 and '+ @where +
' insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_hdr ,'''+@noproses+
''' ,''217'' ,[NOINV] ,[TGINV] from tblbc27Hdr where 1=1 and '+ @where +
' insert into dbo.tbl_bc_out_dtl ( [id_bc_out_hdr] ,[CAR] ,[SERIBRG] ,[HS] ,[URBRG1] ,[URBRG2] ,[URBRG3] ,[URBRG4] ,[DMERK] ,[SIZE] ,[TYPE] ,[KDBRG] ,[JMKOLI] ,[JNKOLI] ,[DNilInv] ,[FOBPERBRG] ,[FOBPERSAT] ,[JMSATUAN] ,[JNSATUAN] ,[NETDET] ,[JMSATPE] ,[JNSATPE] ,[DTLOK] ,[DVolume] ,[NegAsal] ) select @id_hdr ,'''+@noproses+
''' ,row_number() over(order by (select 0)) ,HS ,URBRG1 ,URBRG2 ,URBRG3 ,URBRG4 ,DMERK ,SIZE ,TYPE ,KDBRG ,SUM(JMKOLI) AS jmkoli ,JNKOLI ,SUM(DNilInv) AS DNilInv ,SUM(FOBPERBRG) AS FOBPERBRG ,SUM(FOBPERSAT) AS FOBPERSAT ,SUM(JMSATUAN) AS JMSATUAN ,JNSATUAN ,SUM(NETDET) AS NETDET ,SUM(JMSATPE) AS JMSATPE ,[JNSATPE] ,0 ,SUM(DVolume) AS DVolume ,[NegAsal] FROM dbo.[tblbc27dtl] WHERE 1=1 and '+ @where +
' GROUP BY dbo.tblbc27dtl.HS, dbo.tblbc27dtl.URBRG1, dbo.tblbc27dtl.URBRG2, dbo.tblbc27dtl.URBRG3, dbo.tblbc27dtl.URBRG4, dbo.tblbc27dtl.DMERK, dbo.tblbc27dtl.SIZE, dbo.tblbc27dtl.TYPE, dbo.tblbc27dtl.KDBRG, dbo.tblbc27dtl.JNKOLI, dbo.tblbc27dtl.JNSATUAN, dbo.tblbc27dtl.JNSATPE, dbo.tblbc27dtl.NegAsal update [tblbc27hdr] set status = ''01'' where 1=1 and '+ @where +
' update dbo.tbl_bc_out_hdr set JMBRG=(select count(*) as cnt from dbo.tbl_bc_out_dtl where id_bc_out_hdr=@id_hdr) where id_bc_out_hdr=@id_hdr -- update histori INSERT INTO tbl_bc_out_hdr_h_status (id_bc_out_hdr, statusdok, status_date ,[create_by] ,[create_date] ,[update_by] ,[update_date]) VALUES (@id_hdr, (select top 1 statusdok from tbl_bc_out_hdr where id_bc_out_hdr = @id_hdr), @now ,'''+@userid+
''' ,@now ,'''+@userid+
''' ,@now ) ')
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
Set @ErrMsg = ERROR_MESSAGE()
set @ErrSeverity = ERROR_SEVERITY()
select @ErrMsg as description,@ErrSeverity as status
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
--select * from dbo.tbl_bc_out_hdr
--select * from dbo.tbl_bc_out_dok
--select * from dbo.tbl_bc_out_dtl
-- delete from tbl_bc_out_hdr_h_status
--delete from dbo.tbl_bc_out_hdr
--delete from dbo.tbl_bc_out_dok
--delete from dbo.tbl_bc_out_dtl
select '00' as status, 'Data Procesed' as description
END
--go
--EXEC ('[spi_proses_bc27_new] @datadiff=''21019,900012 (PE9914008B),PE9914008B,PT. SEMARANG AUTOCOMP MANUFACTURING IND.|21018,900013 (PE9914003B),PE9914003B,PT. SURABAYA AUTOCOMP INDONESIA|21017,900014 (PE9914003B),PE9914003B,PT. JATIM AUTOCOMP INDONESIA|21016,900015 (PE9914009B),PE9914009B,PT. SEMARANG AUTOCOMP MANUFACTURING IND.|'', @userid=''admin'' ')
GO
Depends On
4Used By
No items found