Sage EDI IN Intermediary Email
Published May 01, 2019 / by Chris Smith / Leave a CommentI have found users don’t always read the logs from the Adaptus EDI Module anymore in Sage Line 500. There are a few reasons why orders get stuck, the most common is a customer has sent a duplicate EDI file although one was caused by incorrect product supersession setup causing an endless loop.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | 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: |