dbo.spi_stock_opname_rm

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_stock_opname_rm
(@filename varchar(100), @id_stockopname_upload bigint, @tipe varchar(50),  @username varchar(100), @typebahan varchar(10) )
AS
BEGIN
  --
  SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON

    Declare @dir varchar(100)
 declare @filedir varchar(200)
 declare @filecount int
 declare @fullpath varchar(200)
 DECLARE @Files table(Names varchar(250) null)
 declare @dircmd varchar(500)
 declare @cmdmove varchar(500)
 declare @sts_nopart varchar(max)
 IF @tipe='STORM'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\stock_opname\bahan_baku'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
  
 --
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 if @filecount = 1
 begin
  select '06' as status, names+' 0 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload=@id_stockopname_upload and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_rm'' and F3=''qty_stock_opname_rm'' and f4=''satuan_stock_opname_rm'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_stockopname_tmp table ( part_no varchar(100) null, tgl_stock_opname_rm date null, qty_stock_opname_rm float null, satuan_stock_opname_rm varchar(50) null ); insert into @tbl_stockopname_tmp ( part_no, tgl_stock_opname_rm, qty_stock_opname_rm, satuan_stock_opname_rm ) select part_no, tgl_stock_opname_rm, qty_stock_opname_rm, satuan_stock_opname_rm FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) where part_no is not null declare @x_part_no varchar(50),@null_part_no varchar(max) set @null_part_no = '''' if (select count(*) from @tbl_stockopname_tmp as x where replace(x.part_no, '' '', '''') not in (select tbl_data_part_hs.part_no2 from tbl_data_part_hs )) > 0 begin DECLARE db_cursor CURSOR FOR select x.part_no from @tbl_stockopname_tmp as x where replace(x.part_no, '' '', '''') not in (select tbl_data_part_hs.part_no2 from tbl_data_part_hs ) OPEN db_cursor FETCH NEXT FROM db_cursor INTO @x_part_no WHILE @@FETCH_STATUS = 0 BEGIN set @null_part_no = @null_part_no + @x_part_no + '' | '' FETCH NEXT FROM db_cursor INTO @x_part_no END CLOSE db_cursor DEALLOCATE db_cursor end if dbo.trim(@null_part_no) <> '''' begin select ''06'' as status, @null_part_no + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description RETURN end MERGE tbl_stock_opname AS TARGET USING (select part_no, tgl_stock_opname_rm, sum(qty_stock_opname_rm) as qty_stock_opname_rm, satuan_stock_opname_rm from @tbl_stockopname_tmp group by part_no, tgl_stock_opname_rm, satuan_stock_opname_rm ) AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') and SOURCE.tgl_stock_opname_rm = TARGET.date_stock_opname) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_stock_opname_rm, TARGET.date_stock_opname = SOURCE.tgl_stock_opname_rm, TARGET.unit = SOURCE.satuan_stock_opname_rm WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_stock_opname] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_stock_opname_rm ,SOURCE.part_no ,(select top 1 part_name_customs from [tbl_data_part_hs] where tbl_data_part_hs.part_no2=replace(SOURCE.part_no, '' '', '''')) ,(select top 1 part_name from [tbl_data_part_hs] where tbl_data_part_hs.part_no2=replace(SOURCE.part_no, '' '', '''')) ,SOURCE.qty_stock_opname_rm ,(select top 1 [satuan] from [tbl_kode_barang] where kode_barang=replace(SOURCE.part_no, '' '', '''')) ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; select ''00'' as status, ''DATA SUKSES PROCESSED'' as description ')
 
 
 update tbl_uploadstockopname set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_stockopname_upload =@id_stockopname_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 --select '00' as status, 'DATA SUKSES PROCESSED' as description

 END
 
 IF @tipe='STOFG'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\stock_opname\bahan_jadi'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
  
 --
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 if @filecount = 1
 begin
  select '06' as status, names+' 00 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload=@id_stockopname_upload and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 
  
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) WHERE F1=''assy_code'' AND F2=''tgl_stock_opname_fg'' and F3=''qty_stock_opname_fg'' and f4=''satuan_stock_opname_fg'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_stockopname_tmp table ( assy_code varchar(100) null, tgl_stock_opname_fg date null, qty_stock_opname_fg float null, satuan_stock_opname_fg varchar(50) null ); insert into @tbl_stockopname_tmp ( assy_code, tgl_stock_opname_fg, qty_stock_opname_fg, satuan_stock_opname_fg ) select assy_code, tgl_stock_opname_fg, qty_stock_opname_fg, satuan_stock_opname_fg FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) where assy_code is not null declare @x_part_no varchar(50),@null_part_no varchar(max) set @null_part_no = '''' --DECLARE db_cursor CURSOR FOR -- select x.assy_code from @tbl_stockopname_tmp as x -- LEFT OUTER JOIN tbl_assy_list -- ON replace(tbl_assy_list.assy_code, '' '', '''') = replace(x.assy_code, '' '', '''') -- where tbl_assy_list.assy_code is null --OPEN db_cursor --FETCH NEXT FROM db_cursor INTO @x_part_no --WHILE @@FETCH_STATUS = 0 --BEGIN -- set @null_part_no = @null_part_no + @x_part_no + '' | '' --FETCH NEXT FROM db_cursor INTO @x_part_no --END --CLOSE db_cursor --DEALLOCATE db_cursor --if dbo.trim(@null_part_no) <> '''' --begin -- select ''06'' as status, @null_part_no + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description -- RETURN --end if (select count(*) from @tbl_stockopname_tmp as x where x.assy_code not in (select tbl_assy_list.assy_code from tbl_assy_list )) > 0 begin DECLARE db_cursor CURSOR FOR select x.assy_code from @tbl_stockopname_tmp as x where x.assy_code not in (select tbl_assy_list.assy_code from tbl_assy_list) OPEN db_cursor FETCH NEXT FROM db_cursor INTO @x_part_no WHILE @@FETCH_STATUS = 0 BEGIN set @null_part_no = @null_part_no + @x_part_no + '' | '' FETCH NEXT FROM db_cursor INTO @x_part_no END CLOSE db_cursor DEALLOCATE db_cursor end if dbo.trim(@null_part_no) <> '''' begin select ''06'' as status, @null_part_no + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description RETURN end MERGE tbl_stock_opname AS TARGET USING (select assy_code, tgl_stock_opname_fg, sum(qty_stock_opname_fg) as qty_stock_opname_fg, satuan_stock_opname_fg from @tbl_stockopname_tmp group by assy_code, tgl_stock_opname_fg, satuan_stock_opname_fg ) AS SOURCE ON ( replace(SOURCE.assy_code, '' '', '''') = replace(TARGET.part_no, '' '', '''') and SOURCE.tgl_stock_opname_fg = TARGET.date_stock_opname ) WHEN MATCHED AND TARGET.part_no = SOURCE.assy_code THEN UPDATE set TARGET.qty = SOURCE.qty_stock_opname_fg, TARGET.date_stock_opname = SOURCE.tgl_stock_opname_fg, TARGET.unit = SOURCE.satuan_stock_opname_fg WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_stock_opname] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_stock_opname_fg ,SOURCE.assy_code ,(select top 1 DESC_CARLINE from tbl_assy_list where ASSY_CODE=replace(SOURCE.assy_code, '' '', '''')) ,(select top 1 ASSY_NO from tbl_assy_list where ASSY_CODE=replace(SOURCE.assy_code, '' '', '''')) ,SOURCE.qty_stock_opname_fg ,SOURCE.satuan_stock_opname_fg ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; select ''00'' as status, ''DATA SUKSES PROCESSED'' as description ')
 
 
 update tbl_uploadstockopname set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_stockopname_upload =@id_stockopname_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 
 END
 
 IF @tipe='STOSS'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\stock_opname\bahan_sisa_scrap'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
  
 --
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 if @filecount = 1
 begin
  select '06' as status, names+' 0 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload=@id_stockopname_upload and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_sisa_scrap'' and F3=''qty_stock_opname_sisa_scrap'' and f4=''satuan_stock_opname_sisa_scrap'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_stockopname_tmp table ( part_no varchar(100) null, tgl_stock_opname_sisa_scrap date null, qty_stock_opname_sisa_scrap float null, satuan_stock_opname_sisa_scrap varchar(50) null ); insert into @tbl_stockopname_tmp ( part_no, tgl_stock_opname_sisa_scrap, qty_stock_opname_sisa_scrap, satuan_stock_opname_sisa_scrap ) select part_no, tgl_stock_opname_sisa_scrap, qty_stock_opname_sisa_scrap, satuan_stock_opname_sisa_scrap FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) where part_no is not null declare @x_part_no varchar(50),@null_part_no varchar(max) set @null_part_no = '''' DECLARE db_cursor CURSOR FOR select x.part_no from @tbl_stockopname_tmp as x LEFT OUTER JOIN tbl_scrap_data ON replace(tbl_scrap_data.part_no, '' '', '''') = replace(x.part_no, '' '', '''') where tbl_scrap_data.part_no is null OPEN db_cursor FETCH NEXT FROM db_cursor INTO @x_part_no WHILE @@FETCH_STATUS = 0 BEGIN set @null_part_no = @null_part_no + @x_part_no + '' | '' FETCH NEXT FROM db_cursor INTO @x_part_no END CLOSE db_cursor DEALLOCATE db_cursor if dbo.trim(@null_part_no) <> '''' begin select ''06'' as status, @null_part_no + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description RETURN end MERGE tbl_stock_opname AS TARGET USING (select part_no, tgl_stock_opname_sisa_scrap, sum(qty_stock_opname_sisa_scrap) as qty_stock_opname_sisa_scrap, satuan_stock_opname_sisa_scrap from @tbl_stockopname_tmp group by part_no, tgl_stock_opname_sisa_scrap, satuan_stock_opname_sisa_scrap ) AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') and SOURCE.tgl_stock_opname_sisa_scrap = TARGET.date_stock_opname ) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_stock_opname_sisa_scrap, TARGET.date_stock_opname = SOURCE.tgl_stock_opname_sisa_scrap, TARGET.unit = SOURCE.satuan_stock_opname_sisa_scrap WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_stock_opname] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_stock_opname_sisa_scrap ,SOURCE.part_no ,(select top 1 part_name_customs from [tbl_scrap_data] where part_no2=replace(SOURCE.part_no, '' '', '''')) ,(select top 1 part_name from [tbl_scrap_data] where part_no2=replace(SOURCE.part_no, '' '', '''')) ,SOURCE.qty_stock_opname_sisa_scrap ,SOURCE.satuan_stock_opname_sisa_scrap ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; select ''00'' as status, ''DATA SUKSES PROCESSED'' as description ')
 
 
 update tbl_uploadstockopname set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_stockopname_upload =@id_stockopname_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 
 END
 
 IF @tipe='STOMPK'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\stock_opname\bahan_mpk'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
  
 --
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 if @filecount = 1
 begin
  select '06' as status, names+' 0 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload=@id_stockopname_upload and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_mpk'' and F3=''qty_stock_opname_mpk'' and f4=''satuan_stock_opname_mpk'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_stockopname_tmp table ( part_no varchar(100) null, tgl_stock_opname_mpk date null, qty_stock_opname_mpk float null, satuan_stock_opname_mpk varchar(50) null ); insert into @tbl_stockopname_tmp ( part_no, tgl_stock_opname_mpk, qty_stock_opname_mpk, satuan_stock_opname_mpk ) select part_no, tgl_stock_opname_mpk, qty_stock_opname_mpk, satuan_stock_opname_mpk FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) where part_no is not null declare @x_part_no varchar(50),@null_part_no varchar(max) set @null_part_no = '''' DECLARE db_cursor CURSOR FOR select x.part_no from @tbl_stockopname_tmp as x LEFT OUTER JOIN [tbl_dt_mesindanalatkantor_code] ON replace([tbl_dt_mesindanalatkantor_code].[kode_brg], '' '', '''') = replace(x.part_no, '' '', '''') where tbl_dt_mesindanalatkantor_code.kode_brg is null OPEN db_cursor FETCH NEXT FROM db_cursor INTO @x_part_no WHILE @@FETCH_STATUS = 0 BEGIN set @null_part_no = @null_part_no + @x_part_no + '' | '' FETCH NEXT FROM db_cursor INTO @x_part_no END CLOSE db_cursor DEALLOCATE db_cursor if dbo.trim(@null_part_no) <> '''' begin select ''06'' as status, @null_part_no + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description RETURN end MERGE tbl_stock_opname AS TARGET USING (select part_no, tgl_stock_opname_mpk, sum(qty_stock_opname_mpk) as qty_stock_opname_mpk, satuan_stock_opname_mpk from @tbl_stockopname_tmp group by part_no, tgl_stock_opname_mpk, satuan_stock_opname_mpk ) AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') and SOURCE.tgl_stock_opname_mpk = TARGET.date_stock_opname ) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_stock_opname_mpk, TARGET.date_stock_opname = SOURCE.tgl_stock_opname_mpk, TARGET.unit = SOURCE.satuan_stock_opname_mpk WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_stock_opname] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_stock_opname_mpk ,SOURCE.part_no ,(select top 1 nama_barang from [tbl_dt_mesindanalatkantor_code] where kode_brg=replace(SOURCE.part_no, '' '', '''')) ,(select top 1 [keterangan] from [tbl_dt_mesindanalatkantor_code] where kode_brg=replace(SOURCE.part_no, '' '', '''')) ,SOURCE.qty_stock_opname_mpk ,SOURCE.satuan_stock_opname_mpk ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; select ''00'' as status, ''DATA SUKSES PROCESSED'' as description ')
 
 
 update tbl_uploadstockopname set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_stockopname_upload =@id_stockopname_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 --select '00' as status, 'DATA SUKSES PROCESSED' as description
 END

 IF @tipe='STOBP'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\stock_opname\bahan_penolong'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
  
 --
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 if @filecount = 1
 begin
  select '06' as status, names+' 0 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_uploadstockopname where id_stockopname_upload=@id_stockopname_upload and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) WHERE F1=''part_no'' AND F2=''tgl_stock_opname_bp'' and F3=''qty_stock_opname_bp'' and f4=''satuan_stock_opname_bp'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK OPNAME TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_stockopname_tmp table ( part_no varchar(100) null, tgl_stock_opname_bp date null, qty_stock_opname_bp float null, satuan_stock_opname_bp varchar(50) null ); insert into @tbl_stockopname_tmp ( part_no, tgl_stock_opname_bp, qty_stock_opname_bp, satuan_stock_opname_bp ) select part_no, tgl_stock_opname_bp, qty_stock_opname_bp, satuan_stock_opname_bp FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''STOCK OPNAME$'']) where part_no is not null declare @x_part_no varchar(50),@null_part_no varchar(max) set @null_part_no = '''' DECLARE db_cursor CURSOR FOR select x.part_no from @tbl_stockopname_tmp as x LEFT OUTER JOIN [tbl_data_bahan_penolong] ON replace([tbl_data_bahan_penolong].[part_no], '' '', '''') = replace(x.part_no, '' '', '''') where tbl_data_bahan_penolong.part_no is null OPEN db_cursor FETCH NEXT FROM db_cursor INTO @x_part_no WHILE @@FETCH_STATUS = 0 BEGIN set @null_part_no = @null_part_no + @x_part_no + '' | '' FETCH NEXT FROM db_cursor INTO @x_part_no END CLOSE db_cursor DEALLOCATE db_cursor if dbo.trim(@null_part_no) <> '''' begin select ''06'' as status, @null_part_no + '' ADA DATA TIDAK MATCH, PERIKSA KEMBALI DATANYA. PROSES TIDAK DAPAT DILANJUTKAN'' as description RETURN end MERGE tbl_stock_opname AS TARGET USING (select part_no, tgl_stock_opname_bp, sum(qty_stock_opname_bp) as qty_stock_opname_bp, satuan_stock_opname_bp from @tbl_stockopname_tmp group by part_no, tgl_stock_opname_bp, satuan_stock_opname_bp ) AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') and SOURCE.tgl_stock_opname_bp = TARGET.date_stock_opname ) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_stock_opname_bp, TARGET.date_stock_opname = SOURCE.tgl_stock_opname_bp, TARGET.unit = SOURCE.satuan_stock_opname_bp WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_stock_opname] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_stock_opname_bp ,SOURCE.part_no ,(select top 1 part_name_customs from [tbl_data_bahan_penolong] where part_no2=replace(SOURCE.part_no, '' '', '''')) ,(select top 1 [part_name] from [tbl_data_bahan_penolong] where part_no2=replace(SOURCE.part_no, '' '', '''')) ,SOURCE.qty_stock_opname_bp ,SOURCE.satuan_stock_opname_bp ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; select ''00'' as status, ''DATA SUKSES PROCESSED'' as description ')
 
 
 update tbl_uploadstockopname set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_stockopname_upload =@id_stockopname_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 --select '00' as status, 'DATA SUKSES PROCESSED' as description
 END
 
 
END
GO