dbo.spi_proses_bc27

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_proses_bc27
(@idbc27hdr bigint, @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'nobc27', @hasil = @hasil OUTPUT
    set @noproses = @hasil 
 
    declare @status varchar(50)
 set @wherid = ''
 
  SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON

   
    
BEGIN TRY  
BEGIN TRANSACTION        
    exec(
' update [tblbc27Hdr] set noprosesdata = '''+@noproses+''' where 1=1 and idbc27hdr = '+@idbc27hdr+
' 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] ) select top 1 '''+@noproses+
''' ,[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 from [tblbc27hdr] where 1=1 and idbc27hdr = '+@idbc27hdr+
' 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 idbc27hdr = '+@idbc27hdr+
' insert into dbo.tbl_bc_out_dok ( id_bc_out_hdr ,[CAR] ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_hdr ,''-'' ,''217'' ,[NOINV] ,[TGINV] from tblbc27Hdr where 1=1 and idbc27hdr = '+@idbc27hdr+
' 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 idbc27hdr = '+@idbc27hdr+
' 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 idbc27hdr = '+@idbc27hdr+
' 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 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] @idbc27hdr=1, @userid=''admin'' ')
-- go


GO