5 Replies Latest reply: Dec 27, 2006 10:25 AM by user16854 RSS

    SQL to link Requisitions with Purchase Orders

    user16854
      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
        • 1. Re: SQL to link Requisitions with Purchase Orders
          Warren Tolentino
          if the requisition has been turned into a po. a way back long i remember that i ran into the same
          situation before and i had some work around thru adding some UNION clause.

          e.g.
          SELECT prha.segment1 segment1, 
                 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
          UNION
          SELECT pha.segment1  segment1,
                 ...
            FROM po.po_headers_all pha
           WHERE ...
          • 2. Re: SQL to link Requisitions with Purchase Orders
            user16854
            Thanks for that - yes - this happened a long time ago.

            Sorry for asking what will appear a novice question - if I do this:
            SELECT prha.segment1 segment1, 
                   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 = 998
            The PO number is NOT returned.

            I'm not sure about how to do the UNION - if I do this:
            SELECT prha.segment1 segment1, 
                   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 = 998
            UNION
            SELECT prha.segment1 segment1, 
                   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 = 998;
            I still does not work. I guess I am doing something incorrect in my use of the UNION. I'm not sure how the union will work, because as far as I can see the problem is that there is no obvious way to link the PO to the REQ because of the missing req_distribution_id value on the PO dist table.

            Thanks

            Jim
            • 3. Re: SQL to link Requisitions with Purchase Orders
              Warren Tolentino
              if i still remember it i did by something like:
              -- 1st level of query before the UNION clause refers to requisitions
              SELECT ...
                FROM po_requisition_headers_all,
                     ...
               WHERE ...
              UNION 
              -- 2nd level of query after the UNION clause refers to the Purchase Orders
              SELECT
                FROM po_headers_all,
                     ...
               WHERE ...
              • 4. Re: SQL to link Requisitions with Purchase Orders
                user16854
                Thanks Warren - I can sort of understand the theory - the 1st level of the qry will reference the req. but the 2nd level - how can that work - because the SQL to qry the POs needs to in some way refer back to the req - doesn' it?

                I could do this for the 2nd level:
                SELECT pha.segment1 from
                po.po_headers_all pha
                WHERE....
                But what do I put in the WHERE clause - I can't get my head around it!

                Thanks
                • 5. Re: SQL to link Requisitions with Purchase Orders
                  user16854
                  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 this:
                  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(+)
                  The full SQL is as follows:
                  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;