USE <Affected Sage Company MSSQL Database>
GO
CREATE procedure [dbo].[usp_email_edi_held_orders]
AS
-- =============================================
-- Author: Christopher Smith
-- Create date: 01/08/2017
-- Description: The procedure looks for orders held in the EDI IN Intermediary Tables
-- Changes:
-- 1.0.1 - 02/08/2017 - Added dateformat dmy as when run was reporting "Conversion failed when converting date and/or time from character string.". Ensured all date values were cast as datetime as well.
-- 1.0.2 - 11/04/2019 - Corrected the Sales Order lookup to check the invoice customer instead of delivery customer on opheadm
-- =============================================
-- exec [dbo].[usp_email_edi_held_orders]
-- =============================================
--------------------------------------------------------------------------------------------------------------------------
print 'Change to DMY format'
--------------------------------------------------------------------------------------------------------------------------
SET DATEFORMAT dmy;
--------------------------------------------------------------------------------------------------------------------------
print 'Populate temp table'
--------------------------------------------------------------------------------------------------------------------------
Create Table #results(
customer VARCHAR(8),
existingorderno varchar(10),
received_date char(8),
customer_order VARCHAR(20),
"filename" char(30),
warehouse char(2),
product char(20),
long_description char(40),
required_quantity int
)
insert into #results (customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity)
SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
FROM "scheme"."eiorhdm" t1
INNER JOIN "scheme"."eiordtm" t2
ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))
WHERE t1.update_status = 'h' and CAST(received_date as datetime) BETWEEN CAST(GETDATE() -7 as datetime) and CAST(GETDATE() as datetime)
--------------------------------------------------------------------------------------------------------------------------
print 'Check if need to send email'
--------------------------------------------------------------------------------------------------------------------------
if (select count(*) AS nos from #results) = 0
begin
print 'No Email to Send'
goto END1
end
--------------------------------------------------------------------------------------------------------------------------
print 'Check if existing SOP number exists and update table'
--------------------------------------------------------------------------------------------------------------------------
update t1
set t1.existingorderno = t2.order_no
from #results t1
INNER JOIN scheme.opheadm(nolock) t2
ON t1.customer_order = t2.customer_order_no and t1.customer = t2.invoice_customer
update #results
set existingorderno = 'NOMATCH'
from #results
where existingorderno IS NULL
--------------------------------------------------------------------------------------------------------------------------
print 'Create HTML for Email'
--------------------------------------------------------------------------------------------------------------------------
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Orders held in the EDI IN Intermediary Tables</H1>' +
N'<font size="5" face="arial" color="red"> <H2>Please review orders held in the EDI IN module, if the order held does not match an existing order further checks may need to be done</H2></font>' +
N'<table border="1">' +
N'<tr><th>Customer No</th><th>Existing SO No</th>' +
N'<th>Customer Order No</th><th>EDI Filename</th><th>Received Date</th>' +
N'<th>WH</th><th>Product</th>' +
N'<th>Description</th><th>Qty</th>' +
CAST ( ( select td = customer,'',
td = existingorderno,'',
td = customer_order,'',
td = "filename",'',
td = received_date,'',
td = warehouse,'',
td = product,'',
td = long_description,'',
td = required_quantity
from #results
order by customer, customer_order
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
N'</table>' +
N'<p> </p>' +
N'<p> </p>' +
N'<p>Regards.</p>' +
N'<p> </p>' +
N'<p>usp_email_edi_held_orders</p>'
--------------------------------------------------------------------------------------------------------------------------
print 'Send Email'
--------------------------------------------------------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '<SQL Email Profile>',
@recipients = '<Recipients>',
@copy_recipients = '<Copy_Address>',
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'Orders held in the EDI IN Intermediary Tables'
END1:
