CODEWITHSUNDEEP
sqloracle11g
Caffeinated
Caffeinated

Reputation: 12484

Needing assistance with SQL eBS task?

I want to make a SQL query that will generate the following information, which is contained in eBS :

enter image description here

How would I go about doing this? I have been using eTRM to try and narrow down some of the columns, but I can't seem to figure out where to get the approved PO's and non-approved PO's. thanks

Upvotes: 1

Views: 33

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

PO requisitions are linked to POs through distributions. That is, 

po_requisition_distributions.distribution_id = po_distributions.req_distribution_id

That means, there is not necessarily a 1:1 relationship between requisition lines and purchase orders. So, you may need more than just two categories (linked to approved / linked to not-approved), depending on how you use eBS at your location.

Here is a query that links PO requisitions to POs that may get you started

SELECT porl.item_description,
       hl.city,
       poh.segment1 po#,
       poh.authorization_status
FROM   po_requisition_lines_all porl
       INNER JOIN po_vendor_sites_all povs ON povs.vendor_site_id = porl.vendor_site_id
       INNER JOIN hz_party_sites hps ON hps.party_site_id = povs.party_site_id
       INNER JOIN hz_locations hl ON hl.location_id = hps.location_id
       INNER JOIN po_req_distributions_all pord ON pord.requisition_line_id = porl.requisition_line_id
       LEFT JOIN po_distributions_all pod ON pod.req_distribution_id = pord.distribution_id
       LEFT JOIN po_headers_all poh ON poh.po_header_id = pod.po_header_id
WHERE  porl.org_id = 321
AND    hl.city IS NOT NULL
AND    ROWNUM <= 10;

Upvotes: 1

Related Questions