dbo.spi_bc25_scrap_fg

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spi_bc25_scrap_fg
(@filename varchar(100), @id_upload_scrap_fg bigint, @tipe varchar(50),  @username varchar(100) )
AS
BEGIN
    --
 SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
 
    Declare @dir varchar(100)
 declare @filedir varchar(200)
 declare @filecount int
 declare @fullpath varchar(500)
 DECLARE @Files table(Names varchar(250) null)
 declare @dircmd varchar(500)
 declare @cmdmove varchar(500)
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\scrap\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+' 0 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_upload_bc25_scrap_fg where [id_upload_scrap_fg]=@id_upload_scrap_fg and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 BEGIN TRY 
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+ @fullpath +
''', [SCRAP_FG$]) WHERE F1=''CAR'' AND F2=''TGAJU'' and F3=''NODAFT'' and F4=''TGDAFT'' and F4=''TGDAFT'' and F8=''ASSY_CODE'' and F10=''unit'' and F11=''NilInv'' and F13=''query'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA SCRAP FG TIDAK SESUAI'' AS description RETURN END Declare @tbl_dt_bc25scrap_fg table ( CAR varchar(26) not null, TGAJU date not null, NAMABELI varchar(50) not null, ALMTBELI varchar(70) not null, NEGBELI varchar(10) not null, NODAFT varchar(20) not null, TGDAFT date not null, KDVAL varchar(3) not null, NilInv float not null, assy_code varchar(50) not null, qty float not null, unit varchar(5) not null, query varchar(100) ); insert into @tbl_dt_bc25scrap_fg ( CAR, TGAJU, NAMABELI, ALMTBELI, NEGBELI, NODAFT, TGDAFT, KDVAL, NilInv, assy_code, qty, unit, query ) select dbo.trim(CAR), TGAJU, dbo.trim(NAMABELI), dbo.trim(ALMTBELI), NEGBELI, dbo.trim(NODAFT), TGDAFT, dbo.trim(KDVAL), NilInv, dbo.trim(assy_code), qty float, unit, query FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [SCRAP_FG$]) delete from @tbl_dt_bc25scrap_fg where dbo.trim(assy_code) = '''' or dbo.trim(CAR) = '''' or dbo.trim(TGAJU) = '''' or dbo.trim(NODAFT) = '''' declare @cnt2 int,@cnt3 int set @cnt2=(select count(*) from ( select assy_code from @tbl_dt_bc25scrap_fg except select [ASSY_CODE] from dbo.[tbl_assy_list] ) x ) if @cnt2 > 0 begin set @cnt3=(select count(*) from @tbl_dt_bc25scrap_fg a inner join tbl_assy_list ON tbl_assy_list.ASSY_CODE= a.assy_code) select ''07'' as status, ''ADA DATA ASSY TIDAK MATCH: ''+cast(@cnt2 as varchar)+'' MATCH: ''+ cast(@cnt3 as varchar) +'' Mohon dipriksa kembali datanya'' as description return end set @cnt3 = ( select count(*) from ( select a.CAR, a.TGAJU from tbl_bc25_scrap_fg a inner join @tbl_dt_bc25scrap_fg b on (a.CAR = b.car and b.TGAJU = a.TGAJU) ) x ) if @cnt3 > 0 begin SELECT ''06'' AS status, ''DATA SUDAH ADA'' AS description RETURN end declare @jam date, @usernm varchar(100) set @jam = getdate() set @usernm = '''+ @username +
''' insert into [tbl_bc25_scrap_fg] ( [CAR] ,[TGAJU] ,[NAMABELI] ,[ALMTBELI] ,[NEGBELI] ,[typedok] ,[NODAFT] ,[TGDAFT] ,[KDVAL] ,[NilInv] ,[assy_code] ,[part_number] ,[part_name_customs] ,[qty] ,[unit] ,[type_items] ,status_data ,[create_by] ,[update_by] ,[update_date] ,[create_date] ) select a.CAR, a.TGAJU, a.NAMABELI, a.ALMTBELI, a.NEGBELI, ''BC25'', a.NODAFT, a.TGDAFT, a.KDVAL, a.NilInv, a.assy_code, (select top 1 [ASSY_NO] from [tbl_assy_list] where ASSY_CODE = a.assy_code ), ''-'', a.qty, a.unit, ''SCRAPFG'', ''BARU'', @usernm, @usernm, @jam, @jam from @tbl_dt_bc25scrap_fg a ')
END TRY
 BEGIN CATCH
  DECLARE @ErrMsg1 nvarchar(4000), @ErrSeverity1 int
  Set @ErrMsg1 = ERROR_MESSAGE()
  set @ErrSeverity1 = ERROR_SEVERITY()
  select @ErrSeverity1 as status, @ErrMsg1 as description
  RAISERROR(@ErrMsg1, @ErrSeverity1, 1) 
 END CATCH
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 update tbl_upload_bc25_scrap_fg set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and [id_upload_scrap_fg]=@id_upload_scrap_fg
 --select '00' as status, COUNT(*) as description from @Files
 select '00' as status, 'SUKSES UPLOAD' as description

END
GO