Get Requsition ID based on PO

Question

In FSCM I am looking to modify the Search view on Add/Update PO page (Main Menu--> Purchasing--> Purchase Orders--> Add/Update POs) to display the Requisition ID associated with the PO in the search results page. The only table I have found that has both PO_ID and REQ_ID is PS_PO_LINE_DISTRIB however unless I use a SELECT DISTINCT clause I will get multiple PO_ID rows when there are more than 1 line on a PO.

Within Purchase Order Inquiry you can see the related Requisition ID's related to a PO by clicking on Document Status link inside the Purchase Order inquiry details page.

I started looking at the PeopleCode within the the Purchase Order Inquiry to see how they are linking the PO to a Requisition and it appears to use work tables with related PeopleCode function libraries, but I wasn't able to figure our how they get linked. I am hoping someone else may know the answer to this. Thank you.

Answer 1

I'm on an old version of PeopleSoft (SCM 8.80, Tools 8.51), so your mileage may vary. I'm assuming you're familiar with App Designer. If not, comment below and I'll add some details about what I'm clicking on.

Find the name of the Add/Update PO component.

Open the PURCHASE_ORDER component in App Designer. Now let's find the name of the search record. Note that there is a different record for the Add Search Record, so if you want to change that too, do all of this for that record as well.

Open the PO_SRCH record, and add the REQ_ID field to it. Make sure you mark the field as a key. You should consider saving your modified PO_SRCH under a new name in case you want to be able to revert to vanilla PeopleSoft. If you do, change the Search Record in the component to your new record name.

We can see that PO_SRCH is a view. So let's modify the view to pull in REQ_ID from PO_LINE_DISTRIB. As you mentioned above, there doesn't appear to be another table with both PO_ID and REQ_ID, so you'll have to do a SELECT DISTINCT.

We should do a LEFT OUTER JOIN instead of a standard join because if you do a standard join and you enter a purchase order with no lines and save it, then you'll never be able to retrieve that purchase order in this window. Since REQ_ID is a key field, we can't have a null, so we have to do the CASE.

One odd thing that I ran into here was building the view now gave me an error about selecting fewer columns in the SQL than I had in my record definition. I solved it by modifying the view for SQL Server. I've never had to do that before and I don't know why I had to do it for this specific record. But anyway, I entered the same SQL under the record's "Microsoft SQL Server" definition.

In the properties of PO_SRCH, we can see that it has a related language record. If you're only using one language, you can probably get away without changing this, but I'll do it for completeness. Open PO_SRCHLN. Now add REQ_ID to it (mark it as a key field like you did above), and save it as PO_SRCHLN2 (I'm saving it under a new name so I don't break anything else that may be using PO_SRCHLN).

Edit the SQL the same was as you did above. Note: I didn't have to also change the Microsoft SQL Server definition like I did above. I have no idea why.

Now build PO_SRCHLN2.

Go back to PO_SRCH and change its related language record to PO_SRCHLN2.

Now build PO_SRCH.

Hopefully you didn't get any errors and your search page has the requisition ID in it now. My system doesn't use requisitions so they're all blank in the example below, but the new field is there.