1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Link between PO Tables and AP Tables

Discussion in 'Oracle Apps Technical' started by Monziac, Mar 19, 2009.

  1. Monziac

    Monziac Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    HI all, I am trying to find a link between the PO tables, specifically po.po_requisition_headers_all to find of the Requisition in Segment1 has been received/paid. I cant seem to find the link to get back to AP.AP_Payment_Schedules_All or whatever table I may need. Help is appreciated.

    Thanks,
    Jon
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    hi Jon

    There is Distribution ID in PO_Distributions_All which is stored as PO_DISTRIBUTION_ID in ap_invoices_all. Also AP_INVOICE_DISTRIBUTIONS_V is a view which will have the PO_HEADER_ID.

    Note : Do not forget to set the Org Context while querying AP_INVOICE_DISTRIBUTIONS_V

    Set it by the following script :
    Code (Text):

    begin
    fnd_client_info.Set_org_context('ORG_ID');
    end;
     
  3. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    Hi Jon you can get an invoice id against a po_header_id from the following query
    Code (Text):

    SELECT invoice_id
      FROM ap_invoice_distributions_all
     WHERE po_distribution_id IN (SELECT po_distribution_id
                                    FROM po_distributions_all
                                   WHERE po_header_id = <po_header_id>);
    Now you can look up requisitions against the purchase order
     
  4. Monziac

    Monziac Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks guys, let me try this out and see what I come up with.

    Thanks,
    Jon
     
  5. ranjithkeral

    ranjithkeral Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Gurgaon
    hi Monziac,

    I have seen the Link which has sent by Kirti.

    The Link is perfect. but the way he used to join will take more cost.
    So you can remodify the query as below.

    Code (SQL):

    SELECT invoice_num FROM ap_invoice_all aia WHERE
    EXISTS
             (
            SELECT 1
            FROM ap_invocie_distributions_all aida
            WHERE
            aida.invoice_id=aia.invoice_id
            AND
         EXISTS      (
                    SELECT 1
                    FROM po_distributions_all  pod
                    WHERE
                    pod.distribution_id=aida.distribution_id
                    AND
                    EXISTS (
                            SELECT 1 FROM po_headers_all po
                            WHERE po.po_header_id=pod.po_header_id
                            AND
                            segment1='xxxx' AND org_id=xxx
                            )
                     )
               )
     
    This query will be 10 time faster than the previous one.
    Try to apply this one.

    Regards
    Ranjith.k
     
  6. npillai

    npillai Guest

    Hi Ranjith

    If I want the data from the various tables, how do I go about it using the query above i.e
    I need data from ap_holds_all, po header, po lines, po distributions, req header, req lines etc

    Regards
    np
     
  7. ranjithkeral

    ranjithkeral Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Gurgaon
    hi,

    In such case you can use use all the tables directly.

    this type of corelated subquery will help you to avoid shadow tables
    from your "from clause" and you can improve ur query execution speed.

    Regards
     
  8. LNREDDY

    LNREDDY Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi guys's

    There is PO_DISTRIBUTIONS_ALL in PO_DISTRIBUTION_ID is there and AP_INVOICE_DISTRIBUTIONS_ALL IN PO_DISTRIBUTION_ID is there.u can give the linke.
    Ex:
    PO_DISTRIBUTIONS_ALL.po_distribution_id=AP_INVOICE_DISTRIBUTIONS_ALL.po_distribution_id
     
  9. grantie

    grantie Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Sorry to hijack this post, but it's relevant to what I'm doing.

    I too am writing a query that joins PO_Distributions to Invoice_Distributions (as well as a couple of other PO ones). I'm experiencing really bad performance and I wondered if someone could help me out and give me some tips. Please note that I am using Business Objects to generate the SQL. Also I'm not sure if I need the ORG_ID qualification against all of the tables. Thanks in advance.

    Code (SQL):
    SELECT
      PO.PO_HEADERS_ALL.SEGMENT1,
      PO.PO_LINES_ALL.LINE_NUM,
      AP.AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE,
      AP.AP_INVOICES_ALL.INVOICE_NUM
    FROM
      PO.PO_LINES_ALL,
      PO.PO_HEADERS_ALL,
      AP.AP_INVOICE_DISTRIBUTIONS_ALL,
      AP.AP_INVOICES_ALL,
      PO.PO_DISTRIBUTIONS_ALL,
      PO.PO_LINE_LOCATIONS_ALL
    WHERE
     PO.PO_LINES_ALL.PO_LINE_ID=PO.PO_LINE_LOCATIONS_ALL.PO_LINE_ID AND
     PO.PO_HEADERS_ALL.PO_HEADER_ID= PO.PO_LINES_ALL.PO_HEADER_ID AND  PO.PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID=PO.PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID
      AND  AP.AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID=AP.AP_INVOICES_ALL.INVOICE_ID
      AND  PO.PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID=AP.AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID AND
    PO.PO_LINES_ALL.ORG_ID= 132  AND
     PO.PO_HEADERS_ALL.ORG_ID = 132  AND  
    AP.AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID= 132 AND  
    AP.AP_INVOICES_ALL.ORG_ID= 132   AND  
    PO.PO_DISTRIBUTIONS_ALL.ORG_ID= 132   AND  
    PO.PO_LINE_LOCATIONS_ALL.ORG_ID= 132   AND  
      AP.AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE  BETWEEN  '01-01-2010 00:00:00'  AND  '13-01-2010 00:00:00'