dbo.spi_invoice_mesinsparepart

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.spi_invoice_mesinsparepart
(@tipe varchar(50),  @id_inv_mesin bigint, @username varchar(100) )
AS
BEGIN
  SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON 
  if @tipe <> 'INVMPK'
  begin
 select '06' as status, ' TYPE DATA SALAH' as description 
 return

  end
  
  if  (select COUNT(*)  from  tbl_upload_invoicempk where id_invoice_uploadmpk=@id_inv_mesin and status_upload <> 'SUKSES' ) > 0 
  begin
 select '06' as status, ' DATA TIDAK DAPAT DI PROSES' as description 
 return

  end
  
  declare @filepath varchar(1000), @filename varchar(100)
  select  @filepath=[filepath]+[filename] from [tbl_upload_invoicempk] where id_invoice_uploadmpk=@id_inv_mesin
  
  exec(
' declare @cnt int declare @username varchar(100) set @username = '''+ @username +
''' SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@filepath +
''', [INVOICE$])where F1 = ''part_name'' and F2 = ''kode_barang'' and F3=''part_no'' and F4=''kode_supplier'' and F5=''maker'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA INV MESIN SPAREPART TIDAK SESUAI, LIHAT URUTAN FIELDNYA'' AS description RETURN END declare @sekarang date set @sekarang = GETDATE() declare @tbltemp table( [part_name] [varchar](50) NULL, [part_no] [varchar](50) NULL, [kode_barang] [varchar](50) NULL, [kode_supplier] [varchar](50) NULL, [maker] [varchar](50) NULL, [order_qty] [float] NULL, [order_no] [varchar](50) NULL, [order_date] [date] NULL, [req_eta_date] [date] NULL, [shipping_by] [varchar](50) NULL, [invoice_no] [varchar](50) NULL, [invoice_date] [date] NULL, [inv_qty] [float] NULL, [inv_unit] [varchar](50) NULL, [rec_qty] [float] NULL, [rec_date] [date] NULL, [status_inv] [varchar](50) NULL, [amount] [float] NULL, [price] [float] NULL, [remark] [varchar](100) NULL, [nobl] [varchar](50) NULL, [tglbl] [date] NULL, [nocar] [varchar](50) NULL, [tglaju] [date] NULL, [status_data] [varchar](50) NULL, [create_date] [datetime] NULL, [create_by] [varchar](50) NULL, [update_date] [datetime] NULL, [update_by] [varchar](50) NULL ) insert into @tbltemp ( [part_name] , [part_no] , [kode_barang], [kode_supplier], [maker] , [order_qty] , [order_no] , [order_date] , [req_eta_date], [shipping_by] , [invoice_no] , [inv_qty] , [inv_unit], [status_inv] , [rec_qty], [rec_date], [amount] , [price], [remark], [nobl] , [nocar] , [status_data] , [create_date] , [update_date] , [create_by] , [update_by] ) SELECT [part_name] , [part_no] , [kode_barang], [kode_supplier], [maker] , [order_qty] , [order_no] , [order_date] , [req_eta_date], [shipping_by] , [invoice_no] , [inv_qty] , [unit], [status] , [rec_qty], [rec_date], [amount] , [price], [remark], ''-'', ''-'', ''new'', @sekarang, @sekarang, @username, @username FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@filepath +
''', [INVOICE$]) where part_no is not null or invoice_no is not null or kode_barang is not null order by invoice_no MERGE tbl_invoice_mesinsparepart AS TARGET USING @tbltemp AS SOURCE ON (SOURCE.invoice_no = TARGET.invoice_no and SOURCE.part_no = TARGET.part_no) WHEN NOT MATCHED BY TARGET THEN INSERT ( [part_name] , [part_no] , [kode_barang], [kode_supplier], [maker] , [order_qty] , [order_no] , [order_date] , [req_eta_date], [shipping_by] , [invoice_no] , [inv_qty] , [inv_unit], [status_inv] , [rec_qty], [rec_date], [amount] , [price], [remark], [nobl] , [nocar] , [status_data] , [create_date] , [update_date] , [create_by] , [update_by] ) VALUES ( SOURCE.[part_name] , SOURCE.[part_no] , SOURCE.[kode_barang], SOURCE.[kode_supplier], SOURCE.[maker] , SOURCE.[order_qty] , SOURCE.[order_no] , SOURCE.[order_date] , SOURCE.[req_eta_date], SOURCE.[shipping_by] , SOURCE.[invoice_no] , SOURCE.[inv_qty] , SOURCE.[inv_unit], SOURCE.[status_inv] , SOURCE.[rec_qty], SOURCE.[rec_date], SOURCE.[price], SOURCE.[amount] , SOURCE.[remark], SOURCE.[nobl] , SOURCE.[nocar] , SOURCE.[status_data] , SOURCE.[create_date] , SOURCE.[update_date] , SOURCE.[create_by] , SOURCE.[update_by] ) WHEN MATCHED AND (TARGET.[part_name] = SOURCE.[part_name] and TARGET.[part_no] = SOURCE.[part_no]) THEN UPDATE SET TARGET.[part_name] = SOURCE.[part_name], TARGET.[part_no]=SOURCE.[part_no], TARGET.[kode_barang]=SOURCE.[kode_barang], TARGET.[kode_supplier]=SOURCE.[kode_supplier], TARGET.[maker] =SOURCE.[maker], TARGET.[order_qty]=SOURCE.[order_qty], TARGET.[order_no]=SOURCE.[order_no], TARGET.[order_date] =SOURCE.[order_date], TARGET.[req_eta_date]=SOURCE.[req_eta_date], TARGET.[shipping_by] =SOURCE.[shipping_by], TARGET.[invoice_no] =SOURCE.[invoice_no] , TARGET.[inv_qty] =SOURCE.[inv_qty], TARGET.[inv_unit] =SOURCE.[inv_unit], TARGET.[status_inv] =SOURCE.[status_inv], TARGET.[rec_qty] =SOURCE.[rec_qty], TARGET.[rec_date] =SOURCE.[rec_date], TARGET.[amount] =SOURCE.[amount], TARGET.[price] =SOURCE.[price], TARGET.[remark]=SOURCE.[remark], TARGET.[nobl] =SOURCE.[nobl], TARGET.[nocar] =SOURCE.[nocar], TARGET.[status_data] =SOURCE.[status_data], TARGET.[create_date] =SOURCE.[create_date], TARGET.[update_date] =SOURCE.[update_date], TARGET.[create_by] =SOURCE.[create_by], TARGET.[update_by] =SOURCE.[update_by] ; update tbl_upload_invoicempk set status_upload = ''DATA PROCESSED'' where id_invoice_uploadmpk='+@id_inv_mesin+
' ')
  select '00' as status, 'DATA MESIN SPAREPART PROCCESSED' as description   
 
  
END


GO