dbo.spi_stock_awal_fg

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_stock_awal_fg
(@filename varchar(100), @id_stockawal_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)
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\inventory\stock_awal_fg'
 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_stockawal where id_stockawal_upload=@id_stockawal_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 AWAL$'']) WHERE F1=''assy_code'' AND F2=''tgl_stock_awal'' and F3=''qty_stock_awal'' and f4=''satuan_stock_awal'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE STOCK AWAL TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_stockawal_tmp table ( part_no varchar(100) null, tgl_stock_awal date null, qty_stock_awal float null, satuan_stock_awal varchar(50) null ); insert into @tbl_stockawal_tmp ( part_no, tgl_stock_awal, qty_stock_awal, satuan_stock_awal ) select assy_code, tgl_stock_awal, qty_stock_awal, satuan_stock_awal FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''STOCK AWAL$'']) declare @cnt2 int,@cnt3 int set @cnt2=(select count(*) from ( select part_no from @tbl_stockawal_tmp except select ASSY_CODE from dbo.tbl_assy_list ) x ) if @cnt2 > 0 begin set @cnt3=(select count(*) from @tbl_stockawal_tmp a inner join tbl_assy_list ON tbl_assy_list.ASSY_CODE= a.part_no) select ''07'' as status, ''ADA DATA TIDAK MATCH: ''+cast(@cnt2 as varchar)+'' MATCH: ''+ cast(@cnt3 as varchar) +'' Mohon dipriksa kembali datanya'' as description return end MERGE tbl_stock_awal AS TARGET USING @tbl_stockawal_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_stock_awal, TARGET.date_stock = SOURCE.tgl_stock_awal, TARGET.unit = SOURCE.satuan_stock_awal WHEN NOT MATCHED BY TARGET THEN INSERT ( [date_stock] ,[part_no] ,[part_name_customs] ,[part_name] ,[qty] ,[unit] ,[typebahan] ,create_by, update_by, create_date, update_date) values ( SOURCE.tgl_stock_awal ,SOURCE.part_no ,(select top 1 DESC_ASSY from tbl_assy_list where ASSY_CODE=replace(SOURCE.part_no, '' '', '''')) ,(select top 1 ASSY_NO from tbl_assy_list where ASSY_CODE=replace(SOURCE.part_no, '' '', '''')) ,SOURCE.qty_stock_awal ,SOURCE.satuan_stock_awal ,'''+@typebahan+
''' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_upload_stockawal set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_stockawal_upload =@id_stockawal_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 
END
--go
--exec('spi_stock_awal @filename=''stock_awal.xls'', @id_stockawal_upload=8, @tipe=''STA'', @username=''admin'', @typebahan=''RM'' ')
GO