on 02-17-2014 9:33 PM
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!
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
100 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.