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.

4 thoughts on “Purchase Request Line Items in Email

  1. Hello.
    Thank you for your response on the MagicSolutions Group.

    Would you mind sharing what your BR looks like for this? I’m a “NEWBIE” wilth SDE, SQL, BR, etc…but I’m learning.

    I’m trying to creat the BR, but its just not happening, and I was wondering if you would be willing to share a sample BR that pertains to this?
    Thanks;
    Dustin

  2. Hi Dustin, I have shown how to use the business rule in my post “Making the purchasing module work for your organisation – Part 2” Regards, Alan

  3. Hi.

    Found that function useful to tweek to show a list of documents (attachments) with a PR.

    Keep the site going. Great resource.

    Thanks, Thomas

  4. Hi Thomas,

    Thanks very much for the positive comments. The site will keep going – I just need to keep thinking of things to say 🙂

    Regards,

    Alan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s