Show Approver Name on Purchase Order

[Former Member]

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!

[Former Member · Accepted solution]

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'

[Former Member · Accepted solution]

Hi,

You may try:

SELECT T1.U_NAME 'Approver'

from wdd1 T0 inner join OUSR T1 on T1.userid = T0.UserID

inner join OWDD T2 on T2.WddCode = T0.WddCode and T2.ObjType = '22'

inner join ODRF T3 on T3.DocEntry = T2.DocEntry

where T1.Status = 'y'

Thanks,

Gordon

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T2.[U_NAME] as Approver FROM OWDD T0 inner join WDD1 T1 on T0.[WddCode] = T1.[WddCode] INNER JOIN OUSR T2 ON T1.userID = T2.USERID WHERE T1.[Status]  = 'y' and  T0.[ObjType] = '22'

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Gordon, thank you for the reply.

I am actually getting an error with your query.  It says that "Status" is an invalid column name.

[Former Member · Accepted solution]

Nagarajan,  thank you for your reply as well.

I am essentially getting the same issue when I use this query as I have with my current one.  It is showing both names of people who could have approved the PO.  When I enter both your's and my queries into the Crystal Report, everything is duplicated.  By that I mean I am getting two headers, duplicates of each line item, duplicate footers, etc.  Basically, it is assigning each part of the report to each Approver. 

I am still struggling to get the query to only use the name of the sole Approver.  I am honestly a little surprised it is this difficult to get an Approver name on a document in SAP B1.  I would have thought that this would be a more common feature that would be easier to access.

[kothandaraman_nagarajan · Accepted solution]

Hi,

1. Run below query and save it under query manager

SELECT T3.[U_NAME] FROM OPOR T0 inner join OWDD T1 on t0.docentry =  t1.docentry inner join  WDD1 T2 on  T1.[WddCode]  =  T2.[WddCode] INNER JOIN OUSR T3 ON T2.UserID = T3.USERID WHERE T2.[Status]  = 'y' and  T1.[ObjType]  = 22 and t0.docnum = $[OPOR.docnum]

2. Assign above query in user defined value setup--->Search in Existing User-Defined Values according to Saved Query-->Select saved query from query manager--->Choose auto refresh--->Document total

3. Assign this under remark field (new UDF) to get approver name

4. Add this field in your PLD

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Got it! Thanks!

[KennedyT21]

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

[kothandaraman_nagarajan]

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