Link between PO Tables and AP Tables

in Oracle E-Business Suite & Oracle Fusion Applications; HI all, I am trying to find a link between the PO tables, specifically po.po_requisition_headers_all to find of the Requisition ...
+ Post Reply + Post New Topic
Results 1 to 9 of 9
  1. #1
    Monziac is offline Junior Member
    Points: 110, Level: 2
    Join Date
    19 Mar 2009
    Posts
    2
    Points
    110

    Link between PO Tables and AP Tables

    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. #2
    Kirti's Avatar
    Kirti is offline Forum Advisor
    Points: 1,010, Level: 17
    Join Date
    17 Mar 2009
    Posts
    46
    Points
    1,010

    Re: Link between PO Tables and AP Tables

    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 :
    begin
    fnd_client_info.Set_org_context('ORG_ID');
    end;

  3. #3
    apps_expert's Avatar
    apps_expert is offline Forum Expert
    Points: 4,110, Level: 40
    Join Date
    20 Dec 2008
    Location
    Chennai, India
    Posts
    325
    Points
    4,110

    Re: Link between PO Tables and AP Tables

    Hi Jon you can get an invoice id against a po_header_id from the following query
    Code :
    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. #4
    Monziac is offline Junior Member
    Points: 110, Level: 2
    Join Date
    19 Mar 2009
    Posts
    2
    Points
    110

    Re: Link between PO Tables and AP Tables

    Thanks guys, let me try this out and see what I come up with.

    Thanks,
    Jon

  5. #5
    ranjithkeral is offline Junior Member
    Points: 250, Level: 4
    Join Date
    25 May 2009
    Location
    Gurgaon
    Posts
    15
    Points
    250

    Re: Link between PO Tables and AP Tables

    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. #6
    npillai is offline Junior Member
    Points: 10, Level: 1
    Join Date
    01 Jun 2009
    Posts
    1
    Points
    10

    Re: Link between PO Tables and AP Tables

    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. #7
    ranjithkeral is offline Junior Member
    Points: 250, Level: 4
    Join Date
    25 May 2009
    Location
    Gurgaon
    Posts
    15
    Points
    250

    Re: Link between PO Tables and AP Tables

    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. #8
    LNREDDY is offline Junior Member
    Points: 80, Level: 1
    Join Date
    13 Aug 2009
    Posts
    3
    Points
    80

    Re: Link between PO Tables and AP Tables

    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. #9
    grantie is offline Junior Member
    Points: 170, Level: 3
    Join Date
    30 Nov 2009
    Posts
    6
    Points
    170

    Re: Link between PO Tables and AP Tables

    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'

Other Solutions
  1. Link between Invoice Tables - GL Tables
    By tomloera in forum Oracle Financials
    Replies: 0
    Last Post: 02-15-2011, 03:35 PM
  2. tables in a DBC and free tables
    By ali0482 in forum Interview and Jobs Discussions
    Replies: 1
    Last Post: 09-02-2010, 05:17 PM
  3. AP Tables
    By yknev in forum Oracle Apps Technical
    Replies: 1
    Last Post: 03-04-2010, 04:36 AM