I am trying to write a query to reconcile cost balances between PeopleSoft Asset Management and the General Ledger.
I have written two queries that summarize cost by Business Unit, Account & Department. I would like to join the results of these two queries to show a side-by-side comparison of the amounts in each module - preferrably with the ability to add a column to calculate the difference. There could be a case where one BU/Account/Dept combination has a balance in one module - but not the other.
Is this possible?
Thanks,
Tina
| Title | Under | Posted on |
|---|---|---|
| MD5 - SHA checksum of a file. | PeopleSoft Technical | 02/07/2012 - 5:29am |
| nVision Tabular Report through PIA with prompts | PeopleSoft Technical | 02/02/2012 - 10:07pm |
| Can we create an AE to mass update Position - Jobcode data? | PeopleSoft Technical | 01/18/2012 - 3:11am |
| Pay Components on job data can be defaulted and setup based on the rules? | PeopleSoft Functional | 01/05/2012 - 4:58am |
Hello Tina,
I wrote a sql that matches your requirement but I have not tried it using PS query. As a sample, I took PSOPRDEFN (having OPRID and EMPLID fields) record and a custom record PS_ROLEUSER_SP (having ROLEUSER, ROLENAME and EMPLID fields). Attached is the query:
****************************************************
SELECT A.OPRID, A.EMPLID, B.ROLEUSER, B.EMPLID
FROM PSOPRDEFN A
LEFT JOIN PS_ROLEUSER_SP B
ON A.OPRID = B.ROLEUSER
UNION ALL
SELECT A.OPRID, A.EMPLID, B.ROLEUSER, B.EMPLID
FROM PS_ROLEUSER_SP B
LEFT JOIN PSOPRDEFN A
ON A.OPRID = B.ROLEUSER
WHERE A.OPRID IS NULL;
*******************************************************
With this query I am able to get the rows that have corresponding rows in the other record as well as those rows that does not match with each other (thus showing a NULL value in the fields).
I will try to get any other possible solutions if I come across.
Post new comment