Below is the entire procedure and the changes is for header record 'H'
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[Get_INVOICES] Script Date: 03/31/2020 10:51:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Get_INVOICES]
AS
BEGIN
Set NOCOUNT ON
declare
@th_record_type char(1),
@th_vendor_num varchar(15),
@th_invoice_num varchar(30),
@th_invoice_date varchar(10),
@th_period char(6),
@th_terminal varchar(15),
@th_PO varchar(20),
@th_terms varchar(15),
@th_due_date varchar(10),
@th_gross_amount money,
@th_disc_amount money,
@th_net_amount money,
@th_invoice_type char(1),
@th_credit_rebill char(1),
@th_total_trip_miles float,
@th_ivh_hdrnumber int,
@th_invoicenumber varchar(12),
@th_ivh_invoicenumber varchar(12),
@td_record_type char(1),
@td_billoflading varchar(15),
@td_site varchar(15),
@td_product varchar(15),
@td_terminal varchar(15),
@td_carrier varchar(15),
@td_productbillingbasis char(1),
@td_grossunits float,
@td_netunits float,
@td_unitcost money,
@td_extendedcost money,
@StartDate as datetime,
@EndDate as datetime,
@ErrorCode varchar(10),
@ErrorDesc varchar(250),
@FS int,
@OLEResult int,
@FileID int,
@FileName varchar(255),
@FileName2 varchar(255),
@Text1 varchar(255),
@th_record1 char(1),
@th_record2 varchar(30),
@th_record3 varchar(30),
@th_record4 varchar(30),
@th_record5 varchar(30),
@th_record6 varchar(30),
@th_record7 varchar(30),
@th_record8 varchar(30),
@th_record9 varchar(30),
@th_record10 varchar(30),
@th_record11 varchar(30),
@th_record12 varchar(30),
@th_record13 varchar(30),
@th_record14 varchar(30),
@th_record15 varchar(30),
@th_record16 varchar(30),
@th_record17 varchar(30)
truncate table dbo.FTP_INV
--set @StartDate = GETDATE() - 14
--set @EndDate = GETDATE() - 7
Declare get_header_records cursor for
select 'H',ivh_revtype1,ivh_ref_number, CONVERT(VARCHAR(10),ivh_billdate, 110),
'','',ord_hdrnumber,'','','','',ivh_totalcharge,'','',ivh_loaded_distance,ivh_hdrnumber,ivh_invoicenumber
from [invoiceheader]
where ivh_billto = 'SAMPLECOMPANY'
and ivh_invoicenumber not in (select ivh_invoicenumber from InvoicesSent)
--and ivh_deliverydate between @StartDate and @EndDate
open get_header_records
fetch get_header_records
into @th_record_type, @th_vendor_num, @th_invoice_num, @th_invoice_date,
@th_period, @th_terminal, @th_PO, @th_terms, @th_due_date, @th_gross_amount, @th_disc_amount,
@th_net_amount, @th_invoice_type, @th_credit_rebill, @th_total_trip_miles, @th_ivh_hdrnumber, @th_ivh_invoicenumber
if @@FETCH_STATUS <> 0
BEGIN
close get_header_records
deallocate get_header_records
goto EOJ
END
While @@fetch_status = 0
BEGIN
insert into FTP_INV
select 'H', ivh_revtype1, ivh_ref_number, CONVERT(VARCHAR(10), ivh_billdate, 110), '', '', ord_hdrnumber,
'','', '','',cast(ivh_totalcharge as decimal(9,2)), '' ,'' ,ivh_loaded_distance,ivh_invoicenumber,''
from [invoiceheader]
where ivh_billto = 'SAMPLECOMPANY' and ivh_hdrnumber = @th_ivh_hdrnumber
insert into FTP_INV
select 'D', (select TOP 1[ref_number] FROM [referencenumber] R
where R.ref_type = 'BL#' and R.ref_table = 'freightdetail' and R.ord_hdrnumber = D.ord_hdrnumber),
(substring(C.cmp_name, 10, 3) + SUBSTRING(C.cmp_name,14, 4)),
D.cmd_code, H.ivh_originpoint, '', '',
D.ivd_quantity, D.ivd_quantity , cast(D.ivd_rate as decimal(9,4)),
cast(D.ivd_charge as decimal(9,2)), '','','','','',''
FROM [invoicedetail] D
inner join [invoiceheader] H on H.ivh_hdrnumber = D.ivh_hdrnumber
inner join [company] C on C.cmp_id = H.ivh_consignee
where H.ivh_billto = 'SAMPLECOMPANY' and D.ord_hdrnumber = @th_PO and D.ivh_hdrnumber = @th_ivh_hdrnumber
and D.fgt_number is not null
insert into FTP_INV
select 'I', D.cht_itemcode, cast(D.ivd_charge as decimal(9,2)),'','','','','','','','','','','','','',''
FROM [invoicedetail] D
inner join [invoiceheader] H on H.ivh_hdrnumber = D.ivh_hdrnumber
inner join [company] C on C.cmp_id = H.ivh_consignee
where H.ivh_billto = 'SAMPLECOMPANY' and D.ord_hdrnumber = @th_PO and D.ivh_hdrnumber = @th_ivh_hdrnumber
and D.fgt_number is null
fetch get_header_records into @th_record_type, @th_vendor_num, @th_invoice_num, @th_invoice_date,
@th_period, @th_terminal, @th_PO, @th_terms, @th_due_date, @th_gross_amount, @th_disc_amount,
@th_net_amount, @th_invoice_type, @th_credit_rebill, @th_total_trip_miles, @th_ivh_hdrnumber, @th_ivh_invoicenumber
end
close get_header_records
deallocate get_header_records
-- Step 2 write the Invoice records from SQL to File CSV format.
set @ErrorCode = 0
set @ErrorDesc = ''
set @filename = 'C:\TEST\OUT\SAMPLECOMPANY'
set @filename = @filename + cast(DATEPART(YY,GETDATE())as varchar(30))
set @filename = @filename + Right('0' + cast(DATEPART(MM,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(DD,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(HH,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(MI,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(SS,GETDATE())as varchar(30)),2)
set @FileName = @FileName + '.csv'
execute @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
if @OLEResult <> 0 BEGIN
set @ErrorDesc = 'Execute Create Scripting.FileSystemObject Failed'
goto ERRORS
END
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID Out, @FileName, 8, 1
if @OLEResult <> 0 BEGIN
set @ErrorDesc = 'Execute OpenTextFile Failed'
goto ERRORS
END
Declare Get_Invoices cursor for
select T.th_record1, T.th_record2, T.th_record3,
T.th_record4, T.th_record5, T.th_record6,
T.th_record7, T.th_record8, T.th_record9,
T.th_record10, T.th_record11, T.th_record12,
T.th_record13, T.th_record14, T.th_record15
from FTP_INVT
open Get_Invoices
Fetch Get_Invoices into @th_record1, @th_record2, @th_record3,
@th_record4, @th_record5, @th_record6,
@th_record7, @th_record8, @th_record9,
@th_record10, @th_record11, @th_record12,
@th_record13, @th_record14, @th_record15
While @@FETCH_STATUS = 0
BEGIN
set @Text1 = @th_record1 +',' + @th_record2 + ','+ @th_record3 + ',' + @th_record4 + ',' + @th_record5 + ',' + @th_record6
+ ',' + @th_record7 + ',' + @th_record8 + ',' + @th_record9 + ',' + @th_record10
+ ',' + @th_record11 + ',' + @th_record12 + ',' + @th_record13 + ',' + @th_record14 + ',' + @th_record15
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
if @OLEResult <> 0 BEGIN
set @ErrorDesc = 'Execute WriteLine from Text1 Failed'
goto ERRORS
END
Fetch Get_Invoices into @th_record1, @th_record2, @th_record3,
@th_record4, @th_record5, @th_record6,
@th_record7, @th_record8, @th_record9,
@th_record10, @th_record11, @th_record12,
@th_record13, @th_record14, @th_record15
END
close Get_Invoices
deallocate Get_Invoices
insert into InvoicesSentEDI
(ivh_invoicenumber,ivh_ref_number,ivh_FTP_Date)
select H.ivh_invoicenumber,H.ivh_ref_number,GETDATE()
from invoiceheader as H
join FTP_INVas T on T.th_record16 = H.ivh_invoicenumber
and T.th_record1 = 'H'
where ivh_billto = 'SAMPLECOMPANY'
and ivh_invoicenumber not in (select ivh_invoicenumber from InvoicesSent)
goto EOJ
ERRORS:
Print @ErrorDesc
--set @ReturnCode = 1
EOJ:
END