dbo.spi_adjusment

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_adjusment
(@filename varchar(100), @id_adjusment_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)
 IF @tipe='ADJRM'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\adjusment\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_upload_adjusment where id_adjusment_upload=@id_adjusment_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 +
''', [''ADJUSTMENT RM$'']) WHERE F1=''part_no'' AND F2=''tgl_adjusment_rm'' and F3=''qty_adjusment_rm'' and f4=''satuan_adjusment_rm'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE ADJUSMENT TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_adjusment_tmp table ( part_no varchar(100) null, tgl_adjusment_rm date null, qty_adjusment_rm float null, satuan_adjusment_rm varchar(50) null ); insert into @tbl_adjusment_tmp ( part_no, tgl_adjusment_rm, qty_adjusment_rm, satuan_adjusment_rm ) select part_no, tgl_adjusment_rm, qty_adjusment_rm, satuan_adjusment_rm FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''ADJUSTMENT RM$'']) delete from @tbl_adjusment_tm where part_no is 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_adjusment_tmp as x LEFT OUTER JOIN tbl_data_part_hs ON replace(tbl_data_part_hs.part_no, '' '', '''') = replace(x.part_no, '' '', '''') where tbl_data_part_hs.part_name 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_adjusment AS TARGET USING @tbl_adjusment_tmp AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') ) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_adjusment_rm, TARGET.date_adjusment = SOURCE.tgl_adjusment_rm, TARGET.unit = SOURCE.satuan_adjusment_rm WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_adjusment] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_adjusment_rm ,SOURCE.part_no ,(select top 1 part_name_customs from tbl_data_part_hs where part_no2=replace(SOURCE.part_no, '' '', '''')) ,(select top 1 part_name from tbl_data_part_hs where part_no2=replace(SOURCE.part_no, '' '', '''')) ,SOURCE.qty_adjusment_rm ,SOURCE.satuan_adjusment_rm ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_upload_adjusment set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_adjusment_upload =@id_adjusment_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 END
 
 IF @tipe='ADJFG'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\adjusment\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_upload_adjusment where id_adjusment_upload=@id_adjusment_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 +
''', [''ADJUSTMENT FG$'']) WHERE F1=''assy_code'' AND F2=''tgl_adjusment_fg'' and F3=''qty_adjusment_fg'' and f4=''satuan_adjusment_fg'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE ADJUSMENT TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_adjusment_tmp table ( assy_code varchar(100) null, tgl_adjusment_fg date null, qty_adjusment_fg float null, satuan_adjusment_fg varchar(50) null ); insert into @tbl_adjusment_tmp ( assy_code, tgl_adjusment_fg, qty_adjusment_fg, satuan_adjusment_fg ) select assy_code, tgl_adjusment_fg, qty_adjusment_fg, satuan_adjusment_fg FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''ADJUSTMENT FG$'']) delete @tbl_adjusment_tmp where assy_code is 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_adjusment_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 MERGE tbl_adjusment AS TARGET USING @tbl_adjusment_tmp AS SOURCE ON ( replace(SOURCE.assy_code, '' '', '''') = replace(TARGET.part_no, '' '', '''') ) WHEN MATCHED AND TARGET.part_no = SOURCE.assy_code THEN UPDATE set TARGET.qty = SOURCE.qty_adjusment_fg, TARGET.date_adjusment = SOURCE.tgl_adjusment_fg, TARGET.unit = SOURCE.satuan_adjusment_fg WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_adjusment] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_adjusment_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_adjusment_fg ,SOURCE.satuan_adjusment_fg ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_upload_adjusment set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_adjusment_upload =@id_adjusment_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 END
 
 IF @tipe='ADJSS'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\adjusment\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_upload_adjusment where id_adjusment_upload=@id_adjusment_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 +
''', [''ADJUSTMENT SISA SCRAP$'']) WHERE F1=''part_no'' AND F2=''tgl_adjusment_sisa_scrap'' and F3=''qty_adjusment_sisa_scrap'' and f4=''satuan_adjusment_sisa_scrap'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE ADJUSMENT TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_adjusment_tmp table ( part_no varchar(100) null, tgl_adjusment_sisa_scrap date null, qty_adjusment_sisa_scrap float null, satuan_adjusment_sisa_scrap varchar(50) null ); insert into @tbl_adjusment_tmp ( part_no, tgl_adjusment_sisa_scrap, qty_adjusment_sisa_scrap, satuan_adjusment_sisa_scrap ) select part_no, tgl_adjusment_sisa_scrap, qty_adjusment_sisa_scrap, satuan_adjusment_sisa_scrap FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''ADJUSTMENT SISA SCRAP$'']) delete from @tbl_adjusment_tmp where part_no is 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_adjusment_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_adjusment AS TARGET USING @tbl_adjusment_tmp AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') ) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_adjusment_sisa_scrap, TARGET.date_adjusment = SOURCE.tgl_adjusment_sisa_scrap, TARGET.unit = SOURCE.satuan_adjusment_sisa_scrap WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_adjusment] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_adjusment_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_adjusment_sisa_scrap ,SOURCE.satuan_adjusment_sisa_scrap ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_upload_adjusment set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_adjusment_upload =@id_adjusment_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 END

 IF @tipe='ADJMPK'
 BEGIN
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\adjusment\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_upload_adjusment where id_adjusment_upload=@id_adjusment_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 +
''', [''ADJUSTMENT MPK$'']) WHERE F1=''part_no'' AND F2=''tgl_adjusment_mpk'' and F3=''qty_adjusment_mpk'' and f4=''satuan_adjusment_mpk'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE ADJUSMENT TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_adjusment_tmp table ( part_no varchar(100) null, tgl_adjusment_mpk date null, qty_adjusment_mpk float null, satuan_adjusment_mpk varchar(50) null ); insert into @tbl_adjusment_tmp ( part_no, tgl_adjusment_mpk, qty_adjusment_mpk, satuan_adjusment_mpk ) select part_no, tgl_adjusment_mpk, qty_adjusment_mpk, satuan_adjusment_mpk FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''ADJUSTMENT MPK$'']) delete from @tbl_adjusment_tmp where part_no is 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_adjusment_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_adjusment AS TARGET USING @tbl_adjusment_tmp AS SOURCE ON ( replace(SOURCE.part_no, '' '', '''') = replace(TARGET.part_no, '' '', '''') ) WHEN MATCHED AND TARGET.part_no = SOURCE.part_no THEN UPDATE set TARGET.qty = SOURCE.qty_adjusment_mpk, TARGET.date_adjusment = SOURCE.tgl_adjusment_mpk, TARGET.unit = SOURCE.satuan_adjusment_mpk WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_adjusment] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_adjusment_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_adjusment_mpk ,SOURCE.satuan_adjusment_mpk ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_upload_adjusment set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_adjusment_upload =@id_adjusment_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 END
 
 
END
--go

-- exec('spi_adjusment @filename=''adjusment_material.xls'', @id_adjusment_upload=1, @tipe=''ADJRM'', @username=''admin'', @typebahan=''RM'' ')

--go
--exec('spi_adjusment @filename=''stock_awal.xls'', @id_stockawal_upload=8, @tipe=''STA'', @username=''admin'', @typebahan=''RM'' ')
GO