Skip to Content
avatar image
Former Member

Show Approver Name on Purchase Order

Hello,

We are trying to place the Approver's Name on our Purchase Orders (PLD). We are fairly new to using queries and the PLD so we are looking for some assistance. All POs have more than 1 potential approver, but only require 1 approval to pass. We only want the name of the person who approved the document to show and not the others who have not answered.


We tried using PLD for this, however, we can only see the Authorizer Code, which displays as a number. We need the name. Can we link tables in PLD in order to pull the U_Name from the OUSR table that is associated with the UserID in the WDD1 table?


We didn't see a way to do this, so we used the Crystal Converter to save a copy in Crystal as we thought we would need a query to accomplish this. We were able to get the approver name by usingthe following query:

SELECT T1.U_NAME

FROM OUSR T1 INNER JOIN WDD1 T2 ON T1.USERID = T2.UserID

WHERE T2.Status = 'Y'

We feel like we are on the right track with this, however, we need to associate this with the PO that has been approved. We assume we need to use the OPOR table but when we bring this table into the query and do an Inner Join ON OPOR.UserSign = OUSR.USERID, we get a massive number of results. This is what this revised query looks like:

SELECT T1.U_NAME

FROM OPOR T0 INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID INNER JOIN WDD1 T2 ON T1.USERID = T2.UserID

WHERE T2.Status = 'Y'

Where are we going wrong? Can we do this is PLD or do we need to use Crystal? We have been stuck at this point. Any help would be much appreciated!

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Feb 19, 2014 at 06:02 PM

    Thank you both for your input. We have made some progress based on your suggestions. The only issue we are having now is that our report is showing the names of both users who can/have approved POs. Again, we only want to show the person who actually approved the document, not who could have or has before.

    Here is our current query:

    SELECT Distinct(T1.[U_NAME])

    FROM OUSR T1 INNER JOIN WDD1 T3 ON T1.USERID = T3.UserID INNER JOIN OWDD T2 ON T2.WddCode = T3.WddCode

    WHERE T3.Status = 'Y' and T2.WtmCode = '1'

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 18, 2014 at 12:15 AM

    Hi,

    Try this query:

    SELECT distinct (T1.[U_NAME]) FROM OPOR T0 INNER JOIN OUSR T1 ON T0.UserSign = T1.USERID INNER JOIN WDD1 T2 ON T1.USERID = T2.UserID WHERE T2.[Status] = 'y'

    Thanks & Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 18, 2014 at 04:43 AM

    Hi Grant Garza...

    You can Use the CR to achieve your task.

    try to add the wddcode with the query and check for which approval document..

    Regards

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded