Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Janusz Jasinski
    Get the correct PO Number for an Invoice Number
    Topic posted September 6, 2019 by Janusz JasinskiBronze Crown: 15,000+ Points, last edited September 6, 2019, tagged BI Publisher, Fusion Financial reporting, Public Sector 
    57 Views, 9 Comments
    Title:
    Get the correct PO Number for an Invoice Number
    Content:

    Hi,

    We had some consultants in they wrote a large piece of SQL, of which the below is a small part. It's looking to get the correct PO number for a specific invoice number. Specifically the code in bold

    XTE = XLA_TRANSACTION_ENTITIES

    (
    CASE
    WHEN
    GJH.JE_SOURCE IN 
    (
    'Purchasing', 'Payables'
    )
    THEN
    (
    SELECT DISTINCT
    PHA.SEGMENT1 
    FROM
    AP_INVOICES_ALL AIA, PO_HEADERS_ALL PHA 
    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) 
    WHEN
    GJH.JE_SOURCE IN 
    (
    'Receipt Accounting'
    )
    THEN
    (
    SELECT
    CPOD.PO_NUMBER 
    FROM
    --cmr_transactions ct
    CMR_PURCHASE_ORDER_DTLS CPOD , CMR_RCV_EVENTS CRE , POZ_SUPPLIERS_V PS 
    WHERE
    CRE.CMR_PO_DISTRIBUTION_ID = CPOD.CMR_PO_DISTRIBUTION_ID 
    AND CPOD.VENDOR_ID = PS.VENDOR_ID 
    AND CPOD.ACTIVE_FLAG = 'Y' 
    AND CRE.ACCOUNTING_EVENT_ID = XTE.SOURCE_ID_INT_1 ) 
    ELSE
    NULL 
    END
    )
     
    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 advice?
     

    Comment

     

    • Aaron Leggett

      Hey Janusz, 

      Have you tried going down to the line & distribution levels? 

      Thanks

      Aaron

      • Aaron Leggett

        You can give this a whirl if your ap_invoice_distributions_all table is populated... 

        SELECT DISTINCT 
        pha.segment1
        FROM 
        po_headers_all pha, 
        po_lines_all pol,
        po_distributions_all pod,
        ap_invoices_all aia,
        ap_invoice_lines_all aila,
        ap_invoice_distributions aida
        WHERE 
        1=1
        AND pha.po_header_id = pol.po_header_id 
        AND pol.po_line_id = pod.po_line_id 
        AND aia.invoice_id = aila.invoice_id 
        AND aila.invoice_id = aida.invoice_id 
        AND aila.line_number = aida.invoice_line_number 
        AND pod.po_distribution_id = aida.po_distribution_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
         
        Thanks
         
        Aaron
    • Janusz Jasinski

      I have done the following but it doesn't feel clean at all

      SELECT SEGMENT1 FROM FUSION.PO_HEADERS_ALL WHERE PO_HEADER_ID IN ( SELECT PO_HEADER_ID FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID IN ( SELECT A.INVOICE_ID FROM AP_INVOICES_ALL A INNER JOIN ( SELECT INVOICE_NUM, MAX(OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER FROM AP_INVOICES_ALL GROUP BY INVOICE_NUM ) B ON A.OBJECT_VERSION_NUMBER = B.OBJECT_VERSION_NUMBER AND A.INVOICE_NUM = B.INVOICE_NUM WHERE A.INVOICE_NUM = '26427' ) )

    • Sudhakara Rao Kovuru

      Try this simple one....

      SELECT segment1 
      FROM   fusion.po_headers_all 
      WHERE  po_header_id IN (SELECT po_header_id 
                              FROM   ap_invoice_lines_all 
                              WHERE  po_header_id IS NOT NULL 
                                     AND invoice_id IN (SELECT A.invoice_id 
                                                        FROM   ap_invoices_all A 
                                                        WHERE  A.invoice_num = '26427' 
                                                       )) 

      • Janusz Jasinski

        I get two rows back but I want one

        • Sudhakara Rao Kovuru

          use distinct so that we can get 1 row

          SELECT DISTINCT segment1 
          FROM   fusion.po_headers_all 
          WHERE  po_header_id IN (SELECT po_header_id 
                                  FROM   ap_invoice_lines_all 
                                  WHERE  po_header_id IS NOT NULL 
                                         AND invoice_id IN (SELECT A.invoice_id 
                                                            FROM   ap_invoices_all A 
                                                            WHERE  A.invoice_num = '26427' 
                                                           )) 

        • Sudhakara Rao Kovuru

          Did you tried below query?