OraFAQ Forum: SQL & PL/SQL » Problem with SQL to join Requisitions to Purchase Orders

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with SQL to join Requisitions to Purchase Orders

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with SQL to join Requisitions to Purchase Orders [message #210741]

Fri, 22 December 2006 02:54

2006-12-22
Messages: 2
Registered: December 2006

Junior Member

Hi,

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

Code: [Select all] [Show/ hide]

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 this line:

AND prda.distribution_id = pda.req_distribution_id(+)

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

Report message to a moderator

Re: Problem with SQL to join Requisitions to Purchase Orders [message #211246 is a reply to message #210741]

Wed, 27 December 2006 10:22

2006-12-22
Messages: 2
Registered: December 2006

Junior Member

In case it helps anyone else, I got this working. Someone on the Metalink Forum replied - I was joining in the wrong place - needed to be via the bits in bold below:

AND prla.line_location_id = plla.line_location_id(+)
AND plla.po_line_id = pla.po_line_id(+)
AND pla.po_header_id = pha.po_header_id(+)

Code: [Select all] [Show/ hide]

SELECT DISTINCT prha.creation_date
              , prha.segment1 req_no
              , prla.line_num req_line_number
              , prla.item_description
              , papf2.full_name req_prepaper
           FROM po.po_requisition_headers_all prha
              , hr.per_all_people_f papf2
              , po.po_requisition_lines_all prla
              , po.po_line_locations_all plla
              , po.po_lines_all pla
              , po.po_headers_all pha
          WHERE prha.preparer_id = papf2.person_id
            AND prha.requisition_header_id = prla.requisition_header_id
            AND prla.line_location_id = plla.line_location_id(+)
            AND plla.po_line_id = pla.po_line_id(+)
            AND pla.po_header_id = pha.po_header_id(+)
            AND prha.authorization_status = 'APPROVED'
            AND prla.closed_code IS NULL
            AND (   prla.cancel_flag = 'N'
                 OR prla.cancel_flag IS NULL)
       ORDER BY 1 DESC;

Report message to a moderator

Previous Topic:	About ADMIN_LOG_DIR directory in Oracle 10g
Next Topic:	need to export 2 million records

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 23 01:25:29 CDT 2025