dbo.sp_insertmodulbc23

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/****** Object: StoredProcedure dbo.sp_insertmodulbc23 Script Date: 12/26/2012 23:23:28 ******/
--SET ANSI_NULLS ON
--GO
--SET ANSI_WARNINGS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
CREATE PROCEDURE dbo.sp_insertmodulbc23
 @car as varchar(26), @idx as bigint, @conn varchar(MAX), @userid varchar(10)

AS
BEGIN
   declare @countdata int
   declare @detilitemcount int

   select @countdata = COUNT(*) from tbl_bc_in_hdr where id_bc_in_hdr = @idx and status is null 
   
   IF @countdata <> 1 
   BEGIN
 select '01' as status, ' DATA Not Found or Already Exist' as hasil
 return
   END
--BEGIN TRY
-- BEGIN TRANSACTION
-- insert header
 SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON
 
 set @detilitemcount = (select COUNT(*) from dbo.tbl_bc_in_dtl where dbo.tbl_bc_in_dtl.id_bc_in_hdr = @idx) 
 
 exec (
' INSERT INTO OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Hdr (CAR ,KdKpbc ,Tujuan ,JnsBarang ,TujuanKirim ,PasokNama ,PasokAlmt ,PasokNeg ,UsahaId ,UsahaNpwp ,KdTPB ,Registrasi ,UsahaNama ,UsahaAlmt ,UsahaStatus ,ApiKd ,ApiNo ,PpjkId ,PpjkNpwp ,PpjkNama ,PpjkAlmt ,PpjkNo ,PpjkTg ,IndId ,IndNpwp ,IndNama ,IndAlmt ,Moda ,AngkutNama ,AngkutNo ,AngkutFl ,PelBkr ,PelMuat ,PelTransit ,BC23No ,BC23Tg ,KdKpbcBongkar ,KdKpbcAwas ,DokTupKd ,DokTupNo ,DokTupTg ,PosNo ,PosSub ,TmpTbn ,KdVal ,Ndpbm ,NilInv ,Freight ,BTambahan ,Diskon ,KdAss ,Asuransi ,KdHrg ,Fob ,Cif ,Bruto ,Netto ,JmCont ,JmBrg ,TglTtd ,KotaTtd ,Namattd ,ContakPerson ,NoPhone ,NoFax ,Email ,Status ,Snrf ,Lengkap ,PosSubSub ) select top 1 '''+ @car +
''' ,KdKpbc ,Tujuan ,JnsBarang ,TujuanKirim ,PasokNama ,PasokAlmt ,PasokNeg ,UsahaId ,UsahaNpwp ,KdTPB ,Registrasi ,UsahaNama ,UsahaAlmt ,UsahaStatus ,ApiKd ,ApiNo ,PpjkId ,PpjkNpwp ,PpjkNama ,PpjkAlmt ,PpjkNo ,PpjkTg ,IndId ,IndNpwp ,IndNama ,IndAlmt ,Moda ,AngkutNama ,AngkutNo ,AngkutFl ,PelBkr ,PelMuat ,PelTransit ,BC23No ,BC23Tg ,KdKpbcBongkar ,KdKpbcAwas ,DokTupKd ,DokTupNo ,DokTupTg ,PosNo ,PosSub ,TmpTbn ,KdVal ,Ndpbm ,NilInv ,Freight ,BTambahan ,Diskon ,KdAss ,Asuransi ,KdHrg ,Fob ,Cif ,Bruto ,Netto ,JmCont ,'+@detilitemcount+
' ,TglTtd ,KotaTtd ,Namattd ,ContakPerson ,NoPhone ,NoFax ,Email ,''00'' ,Snrf ,0 ,PosSubSub from dbo.tbl_bc_in_hdr where id_bc_in_hdr = '+@idx+
' ')
-- end header
-- Inser Detil
 exec (
' INSERT INTO OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@conn+
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Dtl ( CAR ,Serial ,JnsBarangDtl ,TujuanKirimDtl ,NoHs ,SeriTrp ,BrgUrai ,Merk ,Tipe ,SpfLain ,KdBrg ,Penggunaan ,KemasJn ,KemasJm ,KdFasDtl ,BrgAsal ,DNilInv ,DCif ,KdSat ,JmlSat ,NettoDtl ,SatBmJm ,SatCukJm ,DtlOk ) select '''+ @car +
''' ,Serial ,JnsBarangDtl ,TujuanKirimDtl ,NoHs ,SeriTrp ,BrgUrai ,Merk ,Tipe ,SpfLain ,KdBrg ,Penggunaan ,KemasJn ,KemasJm ,KdFasDtl ,BrgAsal ,DNilInv ,DCif ,KdSat ,JmlSat ,NettoDtl ,SatBmJm ,SatCukJm ,-1 from dbo.tbl_bc_in_dtl where dbo.tbl_bc_in_dtl.id_bc_in_hdr ='+@idx+
' ');
 
---- end insert detil

--- begin insert dok
 exec (
' INSERT INTO OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@conn+
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Dok ( [CAR] ,[DokKd] ,[DokNo] ,[DokTg] ) select '''+ @car +
''' ,[DokKd] ,[DokNo] ,[DokTg] from dbo.tbl_bc_in_dok where dbo.tbl_bc_in_dok.id_bc_in_hdr ='+@idx+
' ')
-- end insert dok
 --- begin insert countener
 exec (
' INSERT INTO OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@conn+
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Con ( [CAR] ,[ContNo] ,[ContUkur] ,[ContTipe] ) select '''+ @car +
''' ,[ContNo] ,[ContUkur] ,[ContTipe] from dbo.tbl_bc_in_con where dbo.tbl_bc_in_con.id_bc_in_hdr ='+@idx+
' ')
-- end insert kontener

 --- begin insert trf
 exec (
' INSERT INTO OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@conn+
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Trf ( [CAR] ,[NoHs] ,[SeriTrp] ,[KdTrpBm] ,[KdSatBm] ,[TrpBm] ,[KdCuk] ,[KdTrpCuk] ,[KdSatCuk] ,[TrpCuk] ,[TrpPpn] ,[TrpPbm] ) select '''+ @car +
''' ,[NoHs] ,0 ,''1'' ,[KdSatBm] ,[TrpBm] ,[KdCuk] ,[KdTrpCuk] ,[KdSatCuk] ,isnull([TrpCuk],0) ,isnull([TrpPpn],0) ,isnull([TrpPbm],0) from dbo.tbl_bc_in_trf where dbo.tbl_bc_in_trf.id_bc_in_hdr ='+@idx+
' ')
--
 -- insert fas
 exec (
' INSERT INTO OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@conn+
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Fas ( [CAR] ,[Serial] ,[KdFasBM] ,[FasBM] ,[KdFasCuk] ,[FasCuk] ,[KdFasPpn] ,[FasPpn] ,[KdFasPph] ,[FasPph] ,[KdFasPbm] ,[FasPbm] ) select '''+ @car +
''' ,[Serial] ,[KdFasBM] ,[FasBM] ,[KdFasCuk] ,[FasCuk] ,[KdFasPpn] ,[FasPpn] ,[KdFasPph] ,[FasPph] ,[KdFasPbm] ,[FasPbm] from dbo.tbl_bc_in_fas where dbo.tbl_bc_in_fas.id_bc_in_hdr ='+@idx+
' ')
 declare @now datetime
 set @now = GETDATE()
 UPDATE tbl_bc_in_hdr set CAR = @car, Status = '00', statusdok='DATA PROCESSED' where id_bc_in_hdr = @idx
 UPDATE tbl_bc_in_dtl set CAR = @car where id_bc_in_hdr = @idx
 UPDATE dbo.tbl_bc_in_dok set CAR = @car where id_bc_in_hdr = @idx
 UPDATE dbo.tbl_bc_in_con set CAR = @car where id_bc_in_hdr = @idx
 
 UPDATE dbo.tbl_bc_in_dtl_partnumber set CAR = @car where id_bc_in_hdr = @idx
 
 INSERT INTO tbl_bc_in_hdr_h_status
          (id_bc_in_hdr, statusdok, status_date
          ,[create_by]
    ,[create_date]
    ,[update_by]
    ,[update_date])
 VALUES     (@idx, (select top 1 statusdok from tbl_bc_in_hdr where id_bc_in_hdr = @idx), @now
  ,@userid
  ,@now
  ,@userid
  ,@now
 )
 
    
 select '00' as status, ' Sukses'+' NO AJU BARU: '+@car as hasil
 
--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 hasil,@ErrSeverity as status
-- RAISERROR(@ErrMsg, @ErrSeverity, 1)
--END CATCH
   
END
GO