Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Janusz Jasinski
    SQL gives me wrong purchase order number for invoice
    Topic posted October 29, 2019 by Janusz JasinskiSilver Crown: 22,500+ Points, tagged BI Publisher, Public Sector, Reports 
    39 Views, 5 Comments
    Title:
    SQL gives me wrong purchase order number for invoice
    Content:

    Hi,

    When I go into the front end of Fusion, I can see that an invoice number of 26427 only has 1 purchase number matched against it which is PO 911753

    When I do the following code, it gives me a PO of 90883 and 909615 which is wrong

    SELECT DISTINCT pha.segment1
    FROM 
    ap_invoices_all aia, -- AP_INVOICES_ALL contains records for invoices you enter
    po_headers_all pha,  -- PO_HEADERS_ALL contains header information for your purchasing documents. You need one row for each document you create.
    XLA_TRANSACTION_ENTITIES xte -- This table contains a row for each transaction for which events have been raised in Subledger Accounting.
    WHERE aia.po_header_id =pha.po_header_id
    AND aia.po_header_id IS NOT NULL
    AND aia.invoice_num =xte.transaction_number
    AND aia.invoice_id =xte.source_id_int_1
    AND aia.invoice_num = '26427'
    I also have tried the following code which yields the correct PO of 911753 but another one, namely 909615
     
    SELECT
    SEGMENT1 
    FROM
    FUSION.PO_HEADERS_ALL 
    WHERE
    PO_HEADER_ID IN 
    (
    SELECT
    PO_HEADER_ID 
    FROM
    FUSION.AP_INVOICE_LINES_ALL 
    WHERE
    INVOICE_ID IN 
    (
    SELECT
    INVOICE_ID 
    FROM
    FUSION.AP_INVOICES_ALL 
    WHERE
    INVOICE_NUM = '26427' 
    )
    AND PO_HEADER_ID IS NOT NULL
    )

    As I said, on the front end, it is showing PO 911753 very clearly. What do I need to issue in order to bring this back.

    This seems to happen where an invoice has been matched incorrectly to a Purchase Order on the Payables module, and then a correction is made to match it to the correct Purchase Order but the report does not seem to reflect this correction.

    Any ideas?

     

    Comment

     

    • Sudhakara Rao Kovuru
      Try this query and let me know
       
      SELECT poh.segment1,api.invoice_num
        FROM fusion.po_headers_all  poh,
         fusion.ap_invoices_all  api,    
             fusion.ap_invoice_lines_all  apil
       WHERE poh.po_header_id = apil.po_header_id
         AND poh.po_header_id = api.po_header_id
         and api.invoice_id = apil.invoice_id 
         and api.invoice_num = '26427' 
         and poh.po_header_id IS NOT NULL
       GROUP BY poh.segment1
      • Janusz Jasinski

        Needed to slightly amend the code

        SELECT
        poh.segment1,
        api.invoice_num 
        FROM
        fusion.po_headers_all poh,
        fusion.ap_invoices_all api,
        fusion.ap_invoice_lines_all apil 
        WHERE
        poh.po_header_id = apil.po_header_id 
        AND poh.po_header_id = api.po_header_id 
        and api.invoice_id = apil.invoice_id 
        and api.invoice_num = '26427' 
        and poh.po_header_id IS NOT NULL 
        GROUP BY
        poh.segment1,
        api.invoice_num
        This brings back 909615
        • Sudhakara Rao Kovuru

          Then this is the right PO... other po user might be wrongly/mistakenly updated on invoice 

          • Janusz Jasinski

            The front screen shows 911753 so are you saying that the product (Oracle Fusion) is wrong?

            I can see the description in ap_invoice_lines_all for 909615 is showing the old one as well

            Please see image