SQL & PL/SQL

Hi,

I have the following SQL used to find out which Purchase Order a requisition has been AutoCreated into:

SELECT prha.segment1 req_no
, prla.line_num req_line_no
, pha.segment1 po_no
FROM po.po_requisition_headers_all prha
, po.po_requisition_lines_all prla
, po.po_req_distributions_all prda
, po.po_distributions_all pda
, po.po_headers_all pha
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = prda.requisition_line_id
AND prda.distribution_id = pda.req_distribution_id(+)
AND pda.po_header_id = pha.po_header_id(+)
AND prha.segment1 = 243131;

This seems to work okay - the join between the PO and the REQ is via the line in bold in the SQL.

BUT - sometimes it seems that when going into Core Applications / iProcurement, the system will show that a REQ has been converted into a PO, but that same REQ/PO information is NOT returned using the SQL above.

When I query the tables, the PO the REQ has been converted into does NOT contain a "req_distribution_id" value on the po_distributions_all table. So on the face of it, I can't see how the system is able to link the REQ to the PO, if this information is missing.

I have pulled back the SQL used for the view in Core Apps, but it is so long and complicated I cannot really work out what is going on.

Does anyone know what other route a REQ might be joined to a PO, if not via the "req_distribution_id" foreign key on the po_distributions_all table?

Also - does anyone know where it is possible to obtain detailed Entity Relationship Diagrams the the Purchasing Tables? I have looked on the FND docs on the ETRM website, but they only contain high-level PDF docs showing how tables are linked, but do not include actual table names showing primary and foreign keys joining up the tables.

Thanks

Jim