dbo.spi_getdata_bc30_ora

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.spi_getdata_bc30_ora 
 -- Add the parameters for the stored procedure here
( @SA_NO varchar(500), @INV_NO varchar(500), @username varchar(100) ) 
AS
BEGIN


declare  @tblPebHdr table(
[CAR] [varchar](200) NULL,
 [JnEks] [varchar](1) NULL,
 [KatEks] [varchar](2) NULL,
 [JNPEB] [varchar](1) NULL,
 [JNEKSOld] [varchar](1) NULL,
 [JNLAIN] [varchar](1) NULL,
 [JNDAG] [varchar](1) NULL,
 [JNBYR] [varchar](1) NULL,
 [IDEKS] [varchar](1) NULL,
 [NPWPEKS] [varchar](15) NULL,
 [NamaEks] [varchar](50) NULL,
 [AlmtEks] [varchar](70) NULL,
 [NoSiup] [varchar](30) NULL,
 [TgSiup] [date] NULL,
 [JnPerus] [varchar](1) NULL,
 [NoTdp] [varchar](30) NULL,
 [TgTdp] [date] NULL,
 [Niper] [varchar](4) NULL,
 [StatusH] [varchar](2) NULL,
 [IdQq] [varchar](1) NULL,
 [NpwpQq] [varchar](15) NULL,
 [NamaQq] [varchar](50) NULL,
 [AlmtQq] [varchar](70) NULL,
 [NiperQq] [varchar](4) NULL,
 [NAMABELI] [varchar](50) NULL,
 [ALMTBELI] [varchar](70) NULL,
 [NEGBELI] [varchar](2) NULL,
 [IDPPJK] [varchar](1) NULL,
 [NPWPPPJK] [varchar](15) NULL,
 [NamaPpjk] [varchar](50) NULL,
 [AlmtPpjk] [varchar](70) NULL,
 [NoPpjk] [varchar](30) NULL,
 [TgPpjk] [date] NULL,
 [MODA] [varchar](1) NULL,
 [TGEKS] [date] NULL,
 [CARRIER] [varchar](20) NULL,
 [VOY] [varchar](7) NULL,
 [NOSHIPORD] [varchar](25) NULL,
 [TGSHIPORD] [date] NULL,
 [PELMUAT] [varchar](5) NULL,
 [PELBONGKAR] [varchar](5) NULL,
 [PELTRANSIT] [varchar](5) NULL,
 [NOINV] [varchar](25) NULL,
 [TGINV] [date] NULL,
 [NOLPSE] [varchar](10) NULL,
 [TGLPSE] [date] NULL,
 [PROPBRG] [varchar](4) NULL,
 [NEGTUJU] [nvarchar](2) NULL,
 [FLSIE] [char](1) NULL,
 [FLKARANT] [char](1) NULL,
 [FLSM] [char](1) NULL,
 [FLIZLAIN] [char](1) NULL,
 [DELIVERY] [varchar](3) NULL,
 [KDVAL] [varchar](3) NULL,
 [KdHrg] [varchar](1) NULL,
 [NilInv] [float] NULL,
 [FREIGHT] [float] NULL,
 [ASURANSI] [float] NULL,
 [KdAss] [varchar](1) NULL,
 [FOB] [float] NULL,
 [MERK] [varchar](100) NULL,
 [JMCONT] [float] NULL,
 [BRUTO] [float] NULL,
 [NETTO] [float] NULL,
 [JMBRG] [float] NULL,
 [JMBRGCTH] [float] NULL,
 [JMBRGGAB] [float] NULL,
 [JMBRGPJT] [float] NULL,
 [NODAFT] [varchar](6) NULL,
 [TGDAFT] [date] NULL,
 [KDKTR] [varchar](6) NULL,
 [KTRGATE] [varchar](6) NULL,
 [NILPE] [float] NULL,
 [KDBANK] [varchar](3) NULL,
 [LOKBANK] [varchar](4) NULL,
 [NOSSP] [varchar](6) NULL,
 [TGSSP] [date] NULL,
 [JNTPB] [varchar](3) NULL,
 [LOKTPB] [varchar](10) NULL,
 [KDLOKBRG] [varchar](1) NULL,
 [KDKTRMUAT] [varchar](6) NULL,
 [KDKTRPRIKS] [varchar](6) NULL,
 [TGSIAP] [date] NULL,
 [WKSIAP] [varchar](6) NULL,
 [ASALDATA] [varchar](1) NULL,
 [TPNAME] [varchar](20) NULL,
 [PEBOK] [char](1) NULL,
 [LENGKAP] [char](1) NULL,
 [JNPARTOF] [varchar](1) NULL,
 [BENDERA] [varchar](2) NULL,
 [TTDPEB] [varchar](30) NULL,
 [STATUS] [varchar](2) NULL,
 [SNRF] [varchar](10) NULL,
 [NOBCF] [nvarchar](30) NULL,
 [TGBCF] [date] NULL,
 [SERIBCF] [float] NULL,
 [NOMOREDI] [varchar](20) NULL,
 [KMDT] [varchar](1) NULL,
 [VOLUME] [float] NULL,
 [KDHANGGAR] [varchar](9) NULL,
 [KONS] [varchar](1) NULL,
 [JMLKMSPEB] [float] NULL,
 [PELMUATEKS] [varchar](5) NULL,
 [NILKURS] [float] NULL,
 [PNBP] [float] NULL,
 [NOBC11] [varchar](6) NULL,
 [TGBC11] [date] NULL,
 [POSBC11] [varchar](4) NULL,
 [SUBPOSBC11] [varchar](2) NULL,
 [jnkemas] [varchar](50) NULL,
 [gabinvoice] [varchar](100) NULL,
 [filename] [varchar](500) NULL,
 [laodeddate] [date] NULL,
 [noprosesdata] [varchar](50) NULL,
 [NAMABELI2] [varchar](50) NULL,
 [ALMTBELI2] [varchar](70) NULL,
 [NEGBELI2] [varchar](2) NULL


)

declare @str varchar(MAX)
set @str = 
' SELECT ''-'',''1'',''41'',''3'',''4'',''5'' ,(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] ,CUST_NAME ,ADDRESS1 + '' '' + ADDRESS2 ,KD_NEGARA FROM OPENQUERY(ORACC, '' select * from MST_BC30_VIEW where SA_NO = ''''' +@SA_NO+  ''''' AND INV_NO = ''''' + @INV_NO  + 
''''' '') '
insert into @tblPebHdr (
[CAR]
,JnEks
,KatEks
,[JNDAG]
,JNBYR
,IDEKS
,NPWPEKS
,[NamaEks]
,[AlmtEks]
,[NoSiup]
,[TgSiup]
,[JnPerus]
,[NoTdp]
,[TgTdp]
,[Niper]
,[StatusH]
,NAMABELI
,ALMTBELI
,NEGBELI
)
exec(@str)
select * from @tblPebHdr


set @str = 
' SELECT * from OPENQUERY(ORACC, '' select * from DTL_BC30_VIEW where SA_NO = ''''' +@SA_NO+  
''''' '') ' 
 
exec(@str)  
 
END 

--go
--EXEC ('spi_getdata_bc30_ora @SA_NO=''PEWB80041B 42B 43B PEWS80032B 80033B 34B'', @INV_NO=''PEWB80043B'', @username=''admin'' ')


GO