dbo.sp_get_from_module_bc23

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.sp_get_from_module_bc23 
@car varchar(255) ,@username varchar(255) , @conn varchar(255), @dok_in_from_in varchar(54)
AS
BEGIN
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
 Declare @dynmaicsql as  nvarchar(1000);
 Declare @dynamicparam as nvarchar(1000);
 Declare @returnedcar as int
 
 --Microsoft.ACE.OLEDB.12.0
 Set @dynmaicsql = 
'set @car=(select count(*) from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Hdr where CAR = '''+ @car +
''') '
 Set @dynamicparam = '@car varchar(100) output'
 execute sp_executesql
  @dynmaicsql   
  ,@dynamicparam
  ,@returnedcar  output
          
    if @returnedcar = 0
    begin
  select '06' as status, ' CAR NOT FOUND' as description 
  return
    end;
 
 
 
 if @dok_in_from_in = 'INV HIB'
 begin
  
   
  declare @temp_tbl_dok table(
   car varchar(50) null, dokkd varchar(50) null, dokno varchar(50) null, doktgl date null
  )
  
  
  DECLARE @sql NVARCHAR(MAX);

  SET @sql = 
N' select [CAR] ,[DokKd] ,[DokNo] ,[DokTg] from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Dok where CAR = '''+ @car +'''';
  insert into @temp_tbl_dok EXEC sp_executesql @sql
  
  
  if (select count(*)  FROM 
   (
    --select a.r3, a.r5 from [dbo].[tbl_inv_hdr] a where exists (select b.dokno, b.doktgl from @temp_tbl_dok b where b.dokkd='380' and a.r3=b.dokno and a.r5 = (SUBSTRING(convert(varchar, b.doktgl, 126), 3,2) + '/' + SUBSTRING(convert(varchar, b.doktgl, 126), 6,2) + '/' + SUBSTRING(convert(varchar, b.doktgl, 126), 9,2)))
      select a.r3 from [dbo].[tbl_inv_hdr] a where exists (select b.dokno from @temp_tbl_dok b where b.dokkd='380' and a.r3=b.dokno)
   ) x
   ) <> (select count(*) from @temp_tbl_dok  where dokkd='380')
  begin
      declare @txtr3 varchar(max)
      declare @dokr3 varchar(50)
      set @txtr3 = ''
   DECLARE a_cursor CURSOR FOR select a.dokno   from [dbo].[tbl_inv_hdr] right outer join @temp_tbl_dok a on [tbl_inv_hdr].r3 = a.dokno  where dokkd='380' and [tbl_inv_hdr].r3 is null
   OPEN a_cursor
   FETCH NEXT FROM a_cursor INTO @dokr3
   WHILE @@FETCH_STATUS = 0 
   BEGIN -- begin here
    set @txtr3 = @txtr3 + ' | '  + @dokr3 
    
    FETCH NEXT FROM a_cursor INTO @dokr3  
   END  

   CLOSE a_cursor  
   DEALLOCATE a_cursor 
   select '06' as status, ' DATA INVOICE TIDAK DITEMUKAN' + @txtr3  + ' CEK NOMOR DAN TGL INV' as description 
      return 
  end
  
  if (select count(*) from (SELECT  b.partnumber 
   FROM         tbl_inv_hdr a INNER JOIN
          v_noinv_dtl b ON a.inv_hdr_id = b.inv_hdr_id
   where exists (select c.dokno from @temp_tbl_dok c where c.dokkd='380' and a.r3=c.dokno)    
   except
   select part_no from dbo.tbl_data_part_hs
  ) xxx) > 0
  begin
   select '06' as status, 'ADA PART TIDAK SAMA..SILAHKAN CEK INVOICE DAN PART NUMBER DI PART HS' as description
   return 
  end
 end 
    
    
    
     
    --select '06' as status, 'MAINTENANCE DULU YA.... LAGI DI CEK' as description
 --return

 declare @nobl varchar(100)
 declare @tglbl date
 set @dynmaicsql = 
'set @nobl=(select top 1 DokNo from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Dok where CAR = '''+ @car +
''' and (DokKd=''740'' or DokKd=''705'' )) set @tglbl=(select top 1 DokTg from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Dok where CAR = '''+ @car +
''' and (DokKd=''740'' or DokKd=''705'' )) '
 
 Set @dynamicparam = '@nobl varchar(100) output, @tglbl date output'
 execute sp_executesql
  @dynmaicsql   
  ,@dynamicparam
  ,@nobl  output
  ,@tglbl output
 declare @noblx varchar(100)
 declare @tglblx date
 set @noblx = @nobl
 set @tglblx = @tglbl
 
 exec(
' DECLARE @tmpbc23hdr table ( CAR varchar(26) null, KdKpbc varchar(6) null, Tujuan varchar(1) null, JnsBarang varchar(2) null, TujuanKirim varchar(2) null, PasokNama varchar(50) null, PasokAlmt varchar(70) null, PasokNeg varchar(2) null, UsahaId varchar(1) null, UsahaNpwp varchar(15) null, KdTPB varchar(1) null, Registrasi varchar(20) null, UsahaNama varchar(50) null, UsahaAlmt varchar(70) null, UsahaStatus varchar(2) null, ApiKd varchar(1) null, ApiNo varchar(15) null, PpjkId varchar(1) null, PpjkNpwp varchar(15) null, PpjkNama varchar(50) null, PpjkAlmt varchar(70) null, PpjkNo varchar(6) null, PpjkTg datetime null, IndId varchar(1) null, IndNpwp varchar(15) null, IndNama varchar(50) null, IndAlmt varchar(70) null, Moda varchar(1) null, AngkutNama varchar(20) null, AngkutNo varchar(7) null, AngkutFl varchar(2) null, PelBkr varchar(5) null, PelMuat varchar(5) null, PelTransit varchar(5) null, BC23No varchar(6) null, BC23Tg datetime null, KdKpbcBongkar varchar(6) null, KdKpbcAwas varchar(6) null, DokTupKd varchar(1) null, DokTupNo varchar(6) null, DokTupTg datetime null, PosNo varchar(4) null, PosSub varchar(4) null, TmpTbn varchar(4) null, KdVal varchar(3) null, Ndpbm float null , NilInv float null, Freight float null, BTambahan float null, Diskon float null, KdAss varchar(1) null, Asuransi float null, KdHrg varchar(1) null, Fob float null, Cif float null, Bruto float null, Netto float null, JmCont int null, JmBrg int null, TglTtd datetime null, KotaTtd varchar(30) null, Namattd varchar(30) null, ContakPerson varchar(30) null, NoPhone varchar(25) null, NoFax varchar(25) null, Email varchar(50) null, Status varchar(2) null, Snrf varchar(10) null, Lengkap char(1) null, PosSubSub varchar(4) null ) insert into @tmpbc23hdr( [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 [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] from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Hdr where CAR = '''+ @car +
''' --SELECT * FROM @tmpbc23hdr MERGE tbl_bc_in_hdr AS TARGET USING @tmpbc23hdr AS SOURCE ON (TARGET.car = SOURCE.car) --When records are matched, update --the records IF there is any change WHEN MATCHED THEN UPDATE SET TARGET.CAR=SOURCE.CAR, TARGET.KdKpbc=SOURCE.KdKpbc, TARGET.Tujuan=SOURCE.Tujuan, TARGET.JnsBarang=SOURCE.JnsBarang, TARGET.TujuanKirim=SOURCE.TujuanKirim, TARGET.PasokNama=SOURCE.PasokNama, TARGET.PasokAlmt=SOURCE.PasokAlmt, TARGET.PasokNeg=SOURCE.PasokNeg, TARGET.UsahaId=SOURCE.UsahaId, TARGET.UsahaNpwp=SOURCE.UsahaNpwp, TARGET.KdTPB=SOURCE.KdTPB, TARGET.Registrasi=SOURCE.Registrasi, TARGET.UsahaNama=SOURCE.UsahaNama, TARGET.UsahaAlmt=SOURCE.UsahaAlmt, TARGET.UsahaStatus=SOURCE.UsahaStatus, TARGET.ApiKd=SOURCE.ApiKd, TARGET.ApiNo=SOURCE.ApiNo, TARGET.PpjkId=SOURCE.PpjkId, TARGET.PpjkNpwp=SOURCE.PpjkNpwp, TARGET.PpjkNama=SOURCE.PpjkNama, TARGET.PpjkAlmt=SOURCE.PpjkAlmt, TARGET.PpjkNo=SOURCE.PpjkNo, TARGET.PpjkTg=SOURCE.PpjkTg, TARGET.IndId=SOURCE.IndId, TARGET.IndNpwp=SOURCE.IndNpwp, TARGET.IndNama=SOURCE.IndNama, TARGET.IndAlmt=SOURCE.IndAlmt, TARGET.Moda=SOURCE.Moda, TARGET.AngkutNama=SOURCE.AngkutNama, TARGET.AngkutNo=SOURCE.AngkutNo, TARGET.AngkutFl=SOURCE.AngkutFl, TARGET.PelBkr=SOURCE.PelBkr, TARGET.PelMuat=SOURCE.PelMuat, TARGET.PelTransit=SOURCE.PelTransit, TARGET.BC23No=SOURCE.BC23No, TARGET.BC23Tg=SOURCE.BC23Tg, TARGET.KdKpbcBongkar=SOURCE.KdKpbcBongkar, TARGET.KdKpbcAwas=SOURCE.KdKpbcAwas, TARGET.DokTupKd=SOURCE.DokTupKd, TARGET.DokTupNo=SOURCE.DokTupNo, TARGET.DokTupTg=SOURCE.DokTupTg, TARGET.PosNo=SOURCE.PosNo, TARGET.PosSub=SOURCE.PosSub, TARGET.TmpTbn=SOURCE.TmpTbn, TARGET.KdVal=SOURCE.KdVal, TARGET.Ndpbm=SOURCE.Ndpbm, TARGET.NilInv=SOURCE.NilInv, TARGET.Freight=SOURCE.Freight, TARGET.BTambahan=SOURCE.BTambahan, TARGET.Diskon=SOURCE.Diskon, TARGET.KdAss=SOURCE.KdAss, TARGET.Asuransi=SOURCE.Asuransi, TARGET.KdHrg=SOURCE.KdHrg, TARGET.Fob=SOURCE.Fob, TARGET.Cif=SOURCE.Cif, TARGET.Bruto=SOURCE.Bruto, TARGET.Netto=SOURCE.Netto, TARGET.JmCont=SOURCE.JmCont, TARGET.JmBrg=SOURCE.JmBrg, TARGET.TglTtd=SOURCE.TglTtd, TARGET.KotaTtd=SOURCE.KotaTtd, TARGET.Namattd=SOURCE.Namattd, TARGET.ContakPerson=SOURCE.ContakPerson, TARGET.NoPhone=SOURCE.NoPhone, TARGET.NoFax=SOURCE.NoFax, TARGET.Email=SOURCE.Email, TARGET.Status=SOURCE.Status, TARGET.Snrf=SOURCE.Snrf, TARGET.Lengkap=SOURCE.Lengkap, TARGET.PosSubSub=SOURCE.PosSubSub WHEN NOT MATCHED BY TARGET THEN INSERT ( 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, create_date, create_by, [typedok] ,statusdok ,bc_in_from_in ,nobl ,tglbl ) VALUES ( SOURCE.CAR, SOURCE.KdKpbc, SOURCE.Tujuan, SOURCE.JnsBarang, SOURCE.TujuanKirim, SOURCE.PasokNama, SOURCE.PasokAlmt, SOURCE.PasokNeg, SOURCE.UsahaId, SOURCE.UsahaNpwp, SOURCE.KdTPB, SOURCE.Registrasi, SOURCE.UsahaNama, SOURCE.UsahaAlmt, SOURCE.UsahaStatus, SOURCE.ApiKd, SOURCE.ApiNo, SOURCE.PpjkId, SOURCE.PpjkNpwp, SOURCE.PpjkNama, SOURCE.PpjkAlmt, SOURCE.PpjkNo, SOURCE.PpjkTg, SOURCE.IndId, SOURCE.IndNpwp, SOURCE.IndNama, SOURCE.IndAlmt, SOURCE.Moda, SOURCE.AngkutNama, SOURCE.AngkutNo, SOURCE.AngkutFl, SOURCE.PelBkr, SOURCE.PelMuat, SOURCE.PelTransit, SOURCE.BC23No, SOURCE.BC23Tg, SOURCE.KdKpbcBongkar, SOURCE.KdKpbcAwas, SOURCE.DokTupKd, SOURCE.DokTupNo, SOURCE.DokTupTg, SOURCE.PosNo, SOURCE.PosSub, SOURCE.TmpTbn, SOURCE.KdVal, SOURCE.Ndpbm, SOURCE.NilInv, SOURCE.Freight, SOURCE.BTambahan, SOURCE.Diskon, SOURCE.KdAss, SOURCE.Asuransi, SOURCE.KdHrg, SOURCE.Fob, SOURCE.Cif, SOURCE.Bruto, SOURCE.Netto, SOURCE.JmCont, SOURCE.JmBrg, SOURCE.TglTtd, SOURCE.KotaTtd, SOURCE.Namattd, SOURCE.ContakPerson, SOURCE.NoPhone, SOURCE.NoFax, SOURCE.Email, SOURCE.Status, SOURCE.Snrf, SOURCE.Lengkap, SOURCE.PosSubSub, GETDATE(), '''+@username+
''' ,''BC23'' ,''DATA PROCESSED'' ,'''+@dok_in_from_in+
''' ,'''+@noblx+
''' ,'''+@tglblx+
''' ); declare @id_bc_in_hdr bigint set @id_bc_in_hdr=(select id_bc_in_hdr from tbl_bc_in_hdr where CAR='''+@car+
''' ) DECLARE @tmpbc23detail table ( CAR varchar(26) null, Serial int null, JnsBarangDtl varchar(2) null, TujuanKirimDtl varchar(2) null, NoHs varchar(12) null, SeriTrp smallint null, BrgUrai varchar(95) null, Merk varchar(15) null, Tipe varchar(15) null, SpfLain varchar(15) null, KdBrg varchar(15) null, Penggunaan varchar(1) null, KemasJn varchar(2) null, KemasJm int null, KdFasDtl varchar(2) null, BrgAsal varchar(2) null, DNilInv float null, DCif float null, KdSat varchar(3) null, JmlSat float null, NettoDtl float null, SatBmJm float null, SatCukJm float null, DtlOk char(1) null ) insert into @tmpbc23detail( [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] ,[DtlOk] from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Dtl where CAR = '''+ @car +
''' --select * from @tmpbc23detail MERGE [dbo].[tbl_bc_in_dtl] AS TARGET USING @tmpbc23detail AS SOURCE ON (TARGET.CAR = SOURCE.CAR and TARGET.Serial = SOURCE.Serial) --When records are matched, update --the records IF there is any change WHEN MATCHED THEN UPDATE SET TARGET.CAR=SOURCE.CAR, TARGET.Serial=SOURCE.Serial, TARGET.JnsBarangDtl=SOURCE.JnsBarangDtl, TARGET.TujuanKirimDtl=SOURCE.TujuanKirimDtl, TARGET.NoHs=SOURCE.NoHs, TARGET.SeriTrp=SOURCE.SeriTrp, TARGET.BrgUrai=SOURCE.BrgUrai, TARGET.Merk=SOURCE.Merk, TARGET.Tipe=SOURCE.Tipe, TARGET.SpfLain=SOURCE.SpfLain, TARGET.KdBrg=SOURCE.KdBrg, TARGET.Penggunaan=SOURCE.Penggunaan, TARGET.KemasJn=SOURCE.KemasJn, TARGET.KemasJm=SOURCE.KemasJm, TARGET.KdFasDtl=SOURCE.KdFasDtl, TARGET.BrgAsal=SOURCE.BrgAsal, TARGET.DNilInv=SOURCE.DNilInv, TARGET.DCif=SOURCE.DCif, TARGET.KdSat=SOURCE.KdSat, TARGET.JmlSat=SOURCE.JmlSat, TARGET.NettoDtl=SOURCE.NettoDtl, TARGET.SatBmJm=SOURCE.SatBmJm, TARGET.SatCukJm=SOURCE.SatCukJm, TARGET.DtlOk=SOURCE.DtlOk WHEN NOT MATCHED BY TARGET THEN INSERT ( id_bc_in_hdr ,[CAR] ,[Serial] ,[JnsBarangDtl] ,[TujuanKirimDtl] ,[NoHs] ,[SeriTrp] ,[BrgUrai] ,[Merk] ,[Tipe] ,[SpfLain] ,[KdBrg] ,[Penggunaan] ,[KemasJn] ,[KemasJm] ,[KdFasDtl] ,[BrgAsal] ,[DNilInv] ,[DCif] ,[KdSat] ,[JmlSat] ,[NettoDtl] ,[SatBmJm] ,[SatCukJm] ,[DtlOk] ) VALUES ( @id_bc_in_hdr, SOURCE.CAR, SOURCE.Serial, SOURCE.JnsBarangDtl, SOURCE.TujuanKirimDtl, SOURCE.NoHs, SOURCE.SeriTrp, SOURCE.BrgUrai, SOURCE.Merk, SOURCE.Tipe, SOURCE.SpfLain, SOURCE.KdBrg, SOURCE.Penggunaan, SOURCE.KemasJn, SOURCE.KemasJm, SOURCE.KdFasDtl, SOURCE.BrgAsal, SOURCE.DNilInv, SOURCE.DCif, SOURCE.KdSat, SOURCE.JmlSat, SOURCE.NettoDtl, SOURCE.SatBmJm, SOURCE.SatCukJm, SOURCE.DtlOk ) --WHEN NOT MATCHED BY SOURCE THEN --delete ; DECLARE @tmpbc23Dok table ( CAR varchar(26) null, DokKd varchar(3) null, DokNo varchar(25) null, DokTg datetime null ) insert into @tmpbc23Dok( [CAR] ,[DokKd] ,[DokNo] ,[DokTg] ) select [CAR] ,[DokKd] ,[DokNo] ,[DokTg] from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @conn +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblBC23Dok where CAR = '''+ @car +
''' ----select * from @tmpbc23Dok MERGE [dbo].[tbl_bc_in_dok] AS TARGET USING @tmpbc23Dok AS SOURCE ON (TARGET.car = SOURCE.car and TARGET.DokKd = SOURCE.DokKd and TARGET.DokNo = SOURCE.DokNo) --When records are matched, update --the records IF there is any change WHEN MATCHED THEN UPDATE SET TARGET.CAR=SOURCE.CAR, TARGET.DokKd=SOURCE.DokKd, TARGET.DokNo=SOURCE.DokNo, TARGET.DokTg=SOURCE.DokTg WHEN NOT MATCHED BY TARGET THEN INSERT ( id_bc_in_hdr ,[CAR] ,[DokKd] ,[DokNo] ,[DokTg] ) VALUES ( @id_bc_in_hdr ,SOURCE.CAR, SOURCE.DokKd, SOURCE.DokNo, SOURCE.DokTg ); MERGE [dbo].[tbl_inv_hdr] AS TARGET USING (select DokNo, DokTg from tbl_bc_in_dok where id_bc_in_hdr = @id_bc_in_hdr and DokKd=''380'' ) AS SOURCEE ON (TARGET.r3 = SOURCEE.DokNo) --ON (TARGET.r3 = SOURCEE.DokNo and TARGET.r5 = (SUBSTRING(convert(varchar, SOURCEE.[DokTg], 126), 3,2) + ''/'' + SUBSTRING(convert(varchar, SOURCEE.[DokTg], 126), 6,2) + ''/'' + SUBSTRING(convert(varchar, SOURCEE.[DokTg], 126), 9,2))) WHEN MATCHED THEN UPDATE SET [nobl]= '''+@noblx+''',[tgbl]='''+@tglblx+''',[car]='''+@car+
''', status_inv=1 ; MERGE dbo.tbl_bc_in_dtl_partnumber as TARGET USING ( select dbo.v_noinv_dtl.[inv_dtl] as inv_dtl ,dbo.v_noinv_dtl.[inv_hdr_id] as inv_hdr_id ,dbo.v_noinv_dtl.[noinv] as noinv ,(CASE WHEN (dbo.v_noinv_dtl.[car] = 0) THEN ''-'' else v_noinv_dtl.[car] END) as car ,dbo.v_noinv_dtl.[nobc] as nobc ,dbo.v_noinv_dtl.[tglbc] as tglbc ,(select top 1 tbl_data_part_hs.part_no2 from tbl_data_part_hs where tbl_data_part_hs.part_no = dbo.v_noinv_dtl.partnumber) as part_no2 ,dbo.v_noinv_dtl.partnumber as partnumber ,(select top 1 tbl_data_part_hs.part_name from tbl_data_part_hs where tbl_data_part_hs.part_no = dbo.v_noinv_dtl.partnumber) as part_name ,isnull(dbo.tbl_data_part_hs.part_name_customs,''-UNKNOWN'' + replace(convert(varchar(255) , newid()), ''-'','''')) as part_name_customs ,isnull(dbo.tbl_data_part_hs.bm, 0) as bm ,dbo.v_noinv_dtl.qty as qty ,dbo.v_noinv_dtl.r7 as unit ,dbo.v_noinv_dtl.amount as amount ,dbo.v_noinv_dtl.kdval as kdval ,dbo.v_noinv_dtl.box as box ,dbo.v_noinv_dtl.pack as pack ,dbo.tbl_data_part_hs.hs_code as hs_code ,dbo.tbl_data_part_hs.id as part_hs_id ,(select top 1 Serial from tbl_bc_in_dtl where id_bc_in_hdr = @id_bc_in_hdr and BrgUrai=dbo.tbl_data_part_hs.part_name) as Serial FROM dbo.v_noinv_dtl LEFT OUTER JOIN dbo.tbl_data_part_hs ON (dbo.v_noinv_dtl.partnumber = dbo.tbl_data_part_hs.part_no) WHERE nobl = '''+@noblx+''' and tgbl ='''+@tglblx+
''' ) AS SOURCE ON (TARGET.[inv_hdr_id] = SOURCE.inv_hdr_id and TARGET.inv_dtl = SOURCE.inv_dtl) WHEN NOT MATCHED THEN INSERT ( [inv_dtl] ,[inv_hdr_id] ,[id_bc_in_hdr] ,[noinv] ,[car] ,[nobc] ,[tglbc] ,part_no2 ,[part_number] ,[part_name] ,[part_name_customs] ,[bm] ,[qty] ,[unit] ,[harga] ,[kdval] ,[box] ,[pack] ,hscode ,id_partnumber ,seri_detil ) VALUES ( SOURCE.inv_dtl ,SOURCE.inv_hdr_id ,@id_bc_in_hdr ,SOURCE.noinv ,SOURCE.car ,SOURCE.nobc ,SOURCE.tglbc ,SOURCE.part_no2 ,SOURCE.partnumber ,SOURCE.part_name ,SOURCE.part_name_customs ,SOURCE.bm ,SOURCE.qty ,SOURCE.unit ,SOURCE.amount ,SOURCE.kdval ,SOURCE.box ,SOURCE.pack ,SOURCE.hs_code ,SOURCE.part_hs_id ,SOURCE.Serial ) WHEN MATCHED THEN UPDATE SET TARGET.[car] =SOURCE.car ,TARGET.[nobc] =SOURCE.nobc ,TARGET.[tglbc] =SOURCE.tglbc ,TARGET.part_no2 =SOURCE.part_no2 ,TARGET.[part_number] =SOURCE.partnumber ,TARGET.[part_name] =SOURCE.part_name ,TARGET.[part_name_customs] =SOURCE.part_name_customs ,TARGET.[bm] =SOURCE.bm ,TARGET.[qty] =SOURCE.qty ,TARGET.[unit] =SOURCE.unit ,TARGET.[harga] =SOURCE.amount ,TARGET.[kdval] =SOURCE.kdval ,TARGET.[box] =SOURCE.box ,TARGET.[pack] =SOURCE.pack ,TARGET.hscode =SOURCE.hs_code ,TARGET.id_partnumber =SOURCE.part_hs_id ,TARGET.seri_detil =SOURCE.Serial ; --update [tbl_inv_hdr] set [nobl]= '''+@noblx+''',[tgbl]='''+@tglblx+
''' -- where r3 in ( --select -- [DokNo] -- from [tbl_bc_in_dok] where id_bc_in_hdr = @id_bc_in_hdr and DokKd=''380'') SELECT ''00'' AS status, ''Data BC 23 Berhasil di update/insert'' AS description; ')
END
--go
--exec('[sp_get_from_module_bc23] @car =''00000000080420130613001276'', @username=''admin'', @conn=''C:\wamp\www\pemi_1\sample txt\dbTPB.mdb'', @dok_in_from_in=''INV HIB'' ')

GO