dbo.spuload_coo

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spuload_coo
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
   SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON
 --Declare @tbl_coo_temp varchar(MAX)
 Declare @tbl_coo_temp_dtl varchar(MAX) 
 set @tbl_coo_temp_dtl= 'tbl_coo_temp_dtl' + replace(convert(varchar(255) , newid()), '-','')
 
 exec (
' create table ' + @tbl_coo_temp_dtl + 
' ( VENDOR varchar(50) NULL, INVOICE_NO varchar(51) NULL, CONTIANER_NO varchar(50) NULL, INVOICE_DATE varchar(50) NULL, ETD_VEBDER varchar(50) NULL, ETD_PORT varchar(50) NULL, ETA_PORT varchar(50) NULL, ETA_FACT varchar(50) NULL, TRANSPORT_WAY varchar(50) NULL, CARA_BAYAR varchar(50) NULL, ONEROUS varchar(50) NULL, SHIP_FROM varchar(50) NULL, VIA varchar(50) NULL, SHIP_TO varchar(50) NULL, PAY_BY varchar(50) NULL, SEAL_NO varchar(50) NULL, SHIP_NAME varchar(50) NULL, SHIP_COMPANY varchar(50) NULL, ORDER_NO varchar(50) NULL, ITEM_NUMBER varchar(50) NULL, DESCRIPTION varchar(150) NULL, ARRIV_PLAN_NUMBER varchar(50) NULL, UOM varchar(50) NULL, PRICE varchar(50) NULL, CURRENT_MONEY varchar(50) NULL, CTN_NO_PREFIX varchar(50) NULL, CTN_NO_FROM varchar(50) NULL, CTN_NO_TO varchar(50) NULL, CTN_QUANTITY varchar(50) NULL, PACKING_QTY varchar(50) NULL, NET_WEIGHT varchar(50) NULL, GROSS_WEIGHT varchar(50) NULL, WEIGHT_UOM varchar(50) NULL, VOLUME varchar(50) NULL, VOLUME_UOM varchar(50) NULL, BL_DATE varchar(50) NULL, BL_NO varchar(50) NULL, FREIGHT varchar(50) NULL, EURO_NO varchar(50) NULL, PALET_NO varchar(50) NULL, HS_CODE varchar(50) NULL, COUNTRY_OF varchar(50) NULL, AKHIR varchar(50) NULL --create_by varchar(30) --create_date datetime --status_inv int --car varchar(30) --nobc varchar(10) --tglbc datetime --nobl varchar(50) --tgbl date ) ')

 Declare @dynmaicsql as  nvarchar(1000);
 Declare @dynamicparam as nvarchar(1000);

  Set @dynmaicsql =  
' set @icount =( select count(*) FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filepath+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@filename+
']'') where F1 = ''VENDOR'' and F2 = ''INVOICE_NO'' and F3 = ''CONTIANER_NO'' and F43 = ''AKHIR'' ) '
  Set @dynamicparam = '@icount int output'
  declare @jmldata int
  execute sp_executesql @dynmaicsql, @dynamicparam, @jmldata output
  if  @jmldata = 0 
  begin
   --exec ('drop table '+ @tbl_coo_temp +'')
   exec ('drop table '+ @tbl_coo_temp_dtl +'')   
   select '07' as status, 'Data Tidak Sesuai, Lakukan Load Ulang' as description
   return  
  end

  exec(
' BEGIN TRY insert into '+ @tbl_coo_temp_dtl +
' ( VENDOR , INVOICE_NO , CONTIANER_NO , INVOICE_DATE , ETD_VEBDER , ETD_PORT , ETA_PORT , ETA_FACT , TRANSPORT_WAY , CARA_BAYAR , ONEROUS , SHIP_FROM , VIA , SHIP_TO , PAY_BY , SEAL_NO , SHIP_NAME , SHIP_COMPANY , ORDER_NO , ITEM_NUMBER , DESCRIPTION , ARRIV_PLAN_NUMBER , UOM , PRICE , CURRENT_MONEY , CTN_NO_PREFIX , CTN_NO_FROM , CTN_NO_TO , CTN_QUANTITY , PACKING_QTY , NET_WEIGHT , GROSS_WEIGHT , WEIGHT_UOM , VOLUME , VOLUME_UOM , BL_DATE , BL_NO , FREIGHT , EURO_NO , PALET_NO , HS_CODE , COUNTRY_OF , AKHIR ) select F1 , F2 , F3 , F4 , F5 , F6 , F7 , F8 , F9 , F10 , F11 , F12 , F13 , F14, F15 , F16 , F17 , F18 , F19 , F20 , F21 , F22 , F23 , F24 , F25 , F26 , F27 , F28 , F29, F30, F31 , F32 , F33 , F34 , F35 , F36 , F37, F38 , F39 , F40 , F41 , F42 , F43 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filepath+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@filename+
']'') where F1 <> ''VENDOR'' and F2 <> ''INVOICE_NO'' and dbo.trim(F3) <> ''CONTIANER_NO'' END TRY BEGIN CATCH SELECT ''error'' AS status ,ERROR_MESSAGE() AS description; END CATCH; declare @VENDORX varchar(50) declare @vendorxx varchar(max) set @vendorxx = '''' DECLARE tbltemp_data_coo_cursorx CURSOR FOR select VENDOR from ' + @tbl_coo_temp_dtl +
' group by VENDOR OPEN tbltemp_data_coo_cursorx FETCH NEXT FROM tbltemp_data_coo_cursorx into @VENDORX WHILE @@FETCH_STATUS = 0 BEGIN if (select count(*) from dbo.tbl_data_supplier where subcode_as400 = @VENDORX) = 0 begin set @vendorxx = @vendorxx + @VENDORX + ''#'' end FETCH NEXT FROM tbltemp_data_coo_cursorx into @VENDORX END CLOSE tbltemp_data_coo_cursorx DEALLOCATE tbltemp_data_coo_cursorx if dbo.trim(@vendorxx) <> '''' begin drop table '+ @tbl_coo_temp_dtl +
' SELECT ''error'' AS status, @vendorxx + '' VENDOR TIDAK DITEMUKAN'' AS description return end BEGIN TRY declare @id_coo_data bigint declare @VENDOR varchar(50) , @INVOICE_NO varchar(51) , @INVOICE_DATE varchar(50), @cnt int, @SHIP_COMPANY varchar(50) DECLARE tbltemp_data_coo_cursor CURSOR FOR select VENDOR, INVOICE_NO, INVOICE_DATE, count(INVOICE_NO) as cnt, SHIP_COMPANY from '+ @tbl_coo_temp_dtl +
' group by vendor, invoice_no, invoice_date, SHIP_COMPANY OPEN tbltemp_data_coo_cursor FETCH NEXT FROM tbltemp_data_coo_cursor into @VENDOR, @INVOICE_NO, @INVOICE_DATE, @cnt, @SHIP_COMPANY WHILE @@FETCH_STATUS = 0 BEGIN if (select count(*) from tbl_coo_data where vendor = @VENDOR and no_invoice = @INVOICE_NO and tgl_invoice = @INVOICE_DATE) = 0 BEGIN insert into [tbl_coo_data] ([vendor],[no_invoice], [tgl_invoice], SHIP_COMPANY, [item_count], [file_name],[status_inv],[upload_by],[upload_date]) values (@VENDOR,@INVOICE_NO,@INVOICE_DATE,@SHIP_COMPANY,@cnt,'''+@filepath+@filename+''',''NEW'','''+@username+
''',GETDATE()) set @id_coo_data = @@IDENTITY insert into [tbl_coo_data_dtl] ( [id_coo_data] ,[VENDOR] ,[INVOICE_NO] ,[CONTIANER_NO] ,[INVOICE_DATE] ,[ETD_VEBDER] ,[ETD_PORT] ,[ETA_PORT] ,[ETA_FACT] ,[TRANSPORT_WAY] ,[CARA_BAYAR] ,[ONEROUS] ,[SHIP_FROM] ,[VIA] ,[SHIP_TO] ,[PAY_BY] ,[SEAL_NO] ,[SHIP_NAME] ,[SHIP_COMPANY] ,[ORDER_NO] ,[ITEM_NUMBER] ,[DESCRIPTION] ,[ARRIV_PLAN_NUMBER] ,[UOM] ,[PRICE] ,[CURRENT_MONEY] ,[CTN_NO_PREFIX] ,[CTN_NO_FROM] ,[CTN_NO_TO] ,[CTN_QUANTITY] ,[PACKING_QTY] ,[NET_WEIGHT] ,[GROSS_WEIGHT] ,[WEIGHT_UOM] ,[VOLUME] ,[VOLUME_UOM] ,[BL_DATE] ,[BL_NO] ,[FREIGHT] ,[EURO_NO] ,[PALET_NO] ,[HS_CODE] ,[COUNTRY_OF] ,[AKHIR] ,[create_by] ,[create_date] ,[status_inv] ) select @id_coo_data, VENDOR , INVOICE_NO , CONTIANER_NO , INVOICE_DATE , ETD_VEBDER , ETD_PORT , ETA_PORT , ETA_FACT , TRANSPORT_WAY , CARA_BAYAR , ONEROUS , SHIP_FROM , VIA , SHIP_TO , PAY_BY , SEAL_NO , SHIP_NAME , SHIP_COMPANY , ORDER_NO , ITEM_NUMBER , DESCRIPTION , ARRIV_PLAN_NUMBER , UOM , PRICE , CURRENT_MONEY , CTN_NO_PREFIX , CTN_NO_FROM , CTN_NO_TO , CTN_QUANTITY , PACKING_QTY , NET_WEIGHT , GROSS_WEIGHT , WEIGHT_UOM , VOLUME , VOLUME_UOM , BL_DATE , BL_NO , FREIGHT , EURO_NO , PALET_NO , HS_CODE , COUNTRY_OF , AKHIR, '''+@username+
''', GETDATE(), ''NEW'' from '+ @tbl_coo_temp_dtl +
' where VENDOR=@VENDOR and INVOICE_NO = @INVOICE_NO and INVOICE_DATE = @INVOICE_DATE END FETCH NEXT FROM tbltemp_data_coo_cursor into @VENDOR, @INVOICE_NO, @INVOICE_DATE, @cnt, @SHIP_COMPANY END CLOSE tbltemp_data_coo_cursor DEALLOCATE tbltemp_data_coo_cursor END TRY BEGIN CATCH drop table '+ @tbl_coo_temp_dtl +
' SELECT ''error'' AS status ,ERROR_MESSAGE() AS description; END CATCH; drop table '+ @tbl_coo_temp_dtl +
' update tbl_upload_coo set status_upload = ''Data Processed'' where id_coo_upload = '+@id+
' SELECT ''00'' AS status,''Data Processed'' AS description; ')   
 --update tbl_upload_coo set status_upload = 'Data Processed' where id_coo_upload = @id
 --SELECT '00' AS status,'Data Processed' AS description;
END
--go
--exec('[spuload_coo] @filepath=''C:/wamp/www/pemi2/loaderfiles/invoice/coo/'', @filename=''INV_BA01882_1.csv'', @username=''admin'', @tipe=''coo'', @trx=''add'', @id=22 ')
--go
--BEGIN TRY
   
-- exec('[spuload_coo] @filepath=''C:/wamp/www/pemi2/loaderfiles/invoice/coo/'', @filename=''Inv_With_Coo_20170201_20170431111.csv'', @username=''admin'', @tipe=''coo'', @trx=''add'', @id=3 ')
   
-- END TRY
-- BEGIN CATCH
-- SELECT
-- '06' AS status
-- ,ERROR_MESSAGE() AS description;
    
-- END CATCH;
GO