dbo.spi_proses_bc30

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_proses_bc30
(@datadiff varchar(max), @userid varchar(100))
AS
BEGIN
 declare @datasplit varchar(8000)
 declare @idpebhdr bigint
 declare @gabinvoice varchar(500)
 declare @NOINV varchar (500)
 declare @wherid varchar(500)
 declare @noproses varchar(50)
 declare @hasil varchar(50)
    exec [dbo].[spu_seq] @nomor_untuk = N'peb_out', @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 @idpebhdr =(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)
    
    if(select COUNT(*) from dbo.tblPebHdr where idpebhdr=@idpebhdr) = 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.tblPebHdr where idpebhdr=@idpebhdr)
    if @status <>  '00' 
    begin
  CLOSE a_cursor
  DEALLOCATE a_cursor
  select '07' as status, 'DATA SUDAH TERPORSES '+@gabinvoice as description
  return
    end
    
    set @status = (select top 1 noprosesdata from dbo.tblPebHdr where idpebhdr=@idpebhdr)
    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 + ' or idpebhdr = ' + CAST(@idpebhdr as varchar) 
     
 FETCH NEXT FROM a_cursor INTO @datasplit
 END
 CLOSE a_cursor
 DEALLOCATE a_cursor 
    declare @where varchar(500)
    set @where = SUBSTRING(@wherid, 4, LEN(@wherid) )
    
BEGIN TRY  
BEGIN TRANSACTION        
    exec(
' update [tblPebHdr] 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] ,[NAMABELI2] ,[ALMTBELI2] ,[NEGBELI2] ) 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] ,[NEGBELI]+[PELBONGKAR] ,[PELTRANSIT] ,[NOINV] ,[TGINV] ,[NOLPSE] ,[TGLPSE] ,[PROPBRG] ,[NEGTUJU] ,[FLSIE] ,[FLKARANT] ,[FLSM] ,[FLIZLAIN] ,[DELIVERY] ,[KDVAL] ,[KdHrg] ,(SELECT isnull(SUM(dbo.tblPebHdr.NilInv),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS NilInv ,(SELECT isnull(SUM(dbo.tblPebHdr.FREIGHT),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS FREIGHT ,(SELECT isnull(SUM(dbo.tblPebHdr.ASURANSI),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS ASURANSI ,KdAss ,(SELECT isnull(SUM(dbo.tblPebHdr.FOB),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS FOB ,MERK ,(SELECT isnull(SUM(dbo.tblPebHdr.JMCONT),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS JMCONT ,(SELECT isnull(SUM(dbo.tblPebHdr.BRUTO),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS BRUTO ,(SELECT isnull(SUM(dbo.tblPebHdr.NETTO),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS NETTO ,(SELECT isnull(SUM(dbo.tblPebHdr.JMBRG),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS JMBRG ,(SELECT isnull(SUM(dbo.tblPebHdr.JMBRGCTH),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS JMBRGCTH ,(SELECT isnull(SUM(dbo.tblPebHdr.JMBRGGAB),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS JMBRGGAB ,(SELECT isnull(SUM(dbo.tblPebHdr.JMBRGPJT),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) 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] ,(SELECT isnull(SUM(dbo.tblPebHdr.VOLUME),0) FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS VOLUME ,[KDHANGGAR] ,[KONS] ,[JMLKMSPEB] ,[PELMUATEKS] ,[NILKURS] ,[PNBP] ,[NOBC11] ,[TGBC11] ,[POSBC11] ,[SUBPOSBC11] ,@now ,'''+@userid+
''' ,@now ,'''+@userid+
''' ,''BC30'' ,''DATA BARU'' ,''SA'' + (SELECT top 1 noprosesdata FROM dbo.tblPebHdr where 1=1 and '+ @where +
' ) AS bc_in_from_in ,[NAMABELI2] ,[ALMTBELI2] ,[NEGBELI2] from tblPebHdr 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 ,''-'' ,''380'' ,[NOINV] ,[TGINV] from tblPebHdr where 1=1 and '+ @where +
' insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_hdr ,''-'' ,''217'' ,[NOINV] ,[TGINV] from tblPebHdr where 1=1 and '+ @where +
' insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) values ( @id_hdr ,''-'' ,''500'' ,''032/WBC.04/NK-EKS/2004'' ,''04/13/2004'' ) insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) values ( @id_hdr ,''-'' ,''998'' ,''SSB-042/KPU-01/BD.0201/2012'' ,''04/04/2012'' ) insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) values ( @id_hdr ,''-'' ,''920'' ,''KM-90/WBC.07/2021'' ,''04/26/2021'' ) --insert into dbo.tbl_bc_out_dok ( -- id_bc_out_hdr -- ,[CAR] -- ,[KdDok] -- ,[NoDok] -- ,[TgDok] --) values ( -- @id_hdr -- ,''-'' -- ,''111'' -- ,''042-BANK OF TOKYO'' -- ,''4/12/2013'' --) insert into dbo.tbl_bc_out_con ( [id_bc_out_hdr] ,[CAR] ,[NoCont] ,[Size] ,[Type] ,[NoSegel] ,[Stuff] ,[JnPartOf] ) select @id_hdr ,''-'' ,[NoCont] ,[Size] ,[Type] ,[NoSegel] ,[Stuff] ,[JnPartOf] FROM dbo.tblPebCon WHERE 1=1 and '+ @where +
' insert into dbo.tbl_bc_out_kms ( [id_bc_out_hdr] ,[CAR] ,[JnKemas] ,[JmKemas] ) select @id_hdr ,''-'' ,[JnKemas] ,[JmKemas] FROM dbo.[tblPebKms] 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 ,''-'' ,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.tblPebDtl WHERE 1=1 and '+ @where +
' GROUP BY dbo.tblPebDtl.HS, dbo.tblPebDtl.URBRG1, dbo.tblPebDtl.URBRG2, dbo.tblPebDtl.URBRG3, dbo.tblPebDtl.URBRG4, dbo.tblPebDtl.DMERK, dbo.tblPebDtl.SIZE, dbo.tblPebDtl.TYPE, dbo.tblPebDtl.KDBRG, dbo.tblPebDtl.JNKOLI, dbo.tblPebDtl.JNSATUAN, dbo.tblPebDtl.JNSATPE, dbo.tblPebDtl.NegAsal update [tblPebHdr] set status = ''01'' where 1=1 and '+ @where +
' declare @jmlcontener int set @jmlcontener = ( select count(*) from dbo.tbl_bc_out_con where id_bc_out_hdr=@id_hdr and [NoCont] is not null ) 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) ,JMCONT=@jmlcontener where id_bc_out_hdr=@id_hdr ')
    
 
   

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 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