Email from Trigger on order to invoice transfer 1

[clemyp]

Hi there,

I need a way of notifying our factory guys that we have processed some orders to fulfilment orders, we run a job called order to invoice transfer, Sometime the office guys forget to tell the factory that they have added another one to be picked.

How can I automate this? I can only think of setting up a trigger on sop10100, but I have no idea what the code would need to look like, so an example would be grand too.

many thanks Paul

 

[jymm]

how soon do you absoltely need it? I have a job that runs on a timed basis that sends the folks in our manufacturing area when certain things happen (like certain countries are in an order)... this way it is grouped to hourly or every x hours depending on the situation.

this sound like something you can use? You could prolly put it in a trigger if you want... let me know and I will put the code... you on sql 2005? and which version of GP?

 

[clemyp]

Hi Jymm,

That could be a solution, to run at a timed interval, I just need something that I can show the team so the code would be handy thank you. yes we have sql2005 and GP9.

Thanks again Paul

 

[jymm]

only problem I ran into when I tried to do a similar thing a while back is that GP does not store the TIME that the order / record was created - all of the time/date fields are date only. SO - at that point I created a table that just had the SOP number, a process flag & the time date in it - and then made a trigger that would insert that record. Then hourly I would run something like below. I have since gone to a warehouse system where we use eGP to copy the data, so I do not have that code anymore, but this should get you a good start. Changing it for a trigger should be pretty easy (get sopnumbe from inserted & then all you need is the code between & the @@rowcount) and you get a pretty email out of the deal.

YOU SHOULD be able to just change the below for your email addy and your GP company DB and see the output for yesterdays orders...

DECLARE @tableHTML  NVARCHAR(MAX) ;

select Hdr.sopnumbe as OrderNumber, hdr.shiptoname, dtl.itemdesc, dtl.Quantity, dtl.uofm, hdr.reqshipdate
from <YOUR GP COMPANY DB HERE>..sop10100 as hdr
inner join <YOUR GP COMPANY DB HERE>..sop10200 as dtl on hdr.sopnumbe = dtl.sopnumbe and hdr.soptype = dtl.soptype
where hdr.soptype = '2' /* order */ and hdr.creatddt >= DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) /* yesterday */

if @@RowCount > 0
Begin

	SET @tableHTML =
		N'<H1>New Orders</H1>' +
		N'<table border="1">' +
		N'<tr><th>Order #</th><th>Ship To</th><th>Item</th>' +
		N'<th>Quantity</th><th>UoM</th><th>Ship on</th></tr>' +
		CAST ( ( SELECT 	td = Hdr.sopnumbe, '',
				td = hdr.shiptoname, '',
				td = dtl.itemdesc, '',
				td = dtl.quantity, '',
				td = dtl.UofM, '',
				td = Cast(Hdr.reqshipDate as Char(12)), ''
				from <YOUR GP COMPANY DB HERE>..sop10100 as hdr
				inner join <YOUR GP COMPANY DB HERE>..sop10200 as dtl on hdr.sopnumbe = dtl.sopnumbe and hdr.soptype = dtl.soptype
				where hdr.soptype = '2' /* order */ and hdr.creatddt >= DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE()))
				order by Hdr.reqshipDate
				FOR XML PATH('tr'), TYPE 
		) AS NVARCHAR(MAX) ) +
		N'</table>' ;

	EXEC msdb.dbo.sp_send_dbmail @recipients='jymm@beer.com',
		@subject = 'New orders ',
		@body = @tableHTML,
		@body_format = 'HTML' ;
End

actually I use this code a fair amount for other things like emailing notifications to user groups... you can hard code the @reciepients like above, but I also have put the recipients into a variable.

make sense? This help or hinder?