Purchase Request Line Items in Email

One of the difficulties with the Purchasing Module that I have found is the inability to easily send the request to the Vendor (or for that matter the customer/approver) including the line items. Well everything is possible of course so I thought I would post a solution for comment/criticism:

You need to create a simple SQL function something like the one attached: Function GET_PR_LINE_ITEMS

Then grant permissions to Public to Execute this function.

Finally use the function in your business rule e.g. When PR is updated with a change of status from Approved to Ordered send email.

In the body of the email you would then have:

Line Items:
{MATH, (SELECT "_SMDBA_"."GET_PR_LINE_ITEMS"({TR,Req #}))}

What basically happens is that the function concatenates all the line items into a single string that can then be used in the body of an email.

I have also uploaded created a function that returns an HTML table row for every line item: Function GET_PR_LINE_ITEMS_HTML. To use this function you would, in the body of the notification you would have…

<table border="1"><tr><td colspan="4">Line Items</td></tr>{MATH, (SELECT "_SMDBA_"."GET_PR_LINE_ITEMS_HTML"({TR,Req #}))}</table>

Hope this maybe of use.

Advertisements