AP Unaccounted Invoice Distribution Details

This query extracts unpaid or unposted AP invoice distribution details from Oracle Fusion Payables. It combines invoice header, invoice line, distribution, supplier, supplier site, PO reference, approval status, accounting amount, and operating unit information for reporting and reconciliation.
categories  Finance Account Payable (AP)
back Back to Vault
xls Download Sample Report for AP Unaccounted Invoice Distribution Details
SQL Download SQL Query for AP Unaccounted Invoice Distribution Details

CSV SQL Query for AP Unaccounted Invoice Distribution Details

Copy 
SELECT
    HOU.NAME                         AS Operating_Unit_Name,
    AIA.INVOICE_NUM                  AS Invoice_Number,
    AIA.INVOICE_TYPE_LOOKUP_CODE     AS Invoice_Type,
    AIA.INVOICE_CURRENCY_CODE        AS Invoice_Currency_Code,
    AIA.INVOICE_DATE                 AS Invoice_Date,
    POZS.SEGMENT1                    AS Vendor_Number,
    HP.PARTY_NAME                    AS Vendor_Name,
    PSSV.VENDOR_SITE_CODE            AS Vendor_Site_Code,
    AIA.APPROVAL_STATUS              AS Invoice_Approval_Status,
    AIA.CANCELLED_DATE               AS Invoice_Cancelled_Date,
    AILA.LINE_NUMBER                 AS Invoice_Line_Number,
    AIDA.DISTRIBUTION_LINE_NUMBER    AS Distribution_Line_Number,
    AIDA.LINE_TYPE_LOOKUP_CODE       AS Invoice_Distribution_Line_Type,
    NVL(AIDA.AMOUNT, 0)              AS Invoice_Distribution_Entered_Amount,
    NVL(AIDA.BASE_AMOUNT, 0)         AS Invoice_Distribution_Base_Amount,
    NVL(AIDA.BASE_AMOUNT, AIDA.AMOUNT)
                                     AS Invoice_Distribution_Accounted_Amount,
    PHA.SEGMENT1                     AS Purchase_Order_Number,
    NVL(PDA.AMOUNT_CANCELLED, 0)     AS PO_Distribution_Amount_Cancelled,
    NVL(PDA.AMOUNT_FINANCED, 0)      AS PO_Distribution_Amount_Financed,
    NVL(PDA.AMOUNT_BILLED, 0)        AS PO_Distribution_Amount_Billed,
    AIA.INVOICE_ID                   AS Invoice_Id,
    AIDA.DIST_CODE_COMBINATION_ID    AS Distribution_Code_Combination_Id,
    AIDA.POSTED_FLAG                 AS Posted_Flag,
    AIDA.PO_DISTRIBUTION_ID          AS PO_Distribution_Id,
    AIDA.INVOICE_DISTRIBUTION_ID     AS Invoice_Distribution_Id,
    AIA.ORG_ID                       AS Operating_Unit_Id
FROM
    AP_INVOICES_ALL                  AIA,
    AP_INVOICE_LINES_ALL             AILA,
    AP_INVOICE_DISTRIBUTIONS_ALL     AIDA,
    PO_DISTRIBUTIONS_ALL             PDA,
    PO_HEADERS_ALL                   PHA,
    POZ_SUPPLIERS                    POZS,
    HZ_PARTIES                       HP,
    POZ_SUPPLIER_SITES_V             PSSV,
    HR_OPERATING_UNITS               HOU
WHERE
        AIA.INVOICE_ID               = AILA.INVOICE_ID
    AND AILA.INVOICE_ID              = AIDA.INVOICE_ID
    AND AILA.LINE_NUMBER             = AIDA.INVOICE_LINE_NUMBER
    AND AIDA.PO_DISTRIBUTION_ID      = PDA.PO_DISTRIBUTION_ID
    AND AIA.VENDOR_ID                = POZS.VENDOR_ID
    AND POZS.PARTY_ID                = HP.PARTY_ID
    AND AIA.VENDOR_SITE_ID           = PSSV.VENDOR_SITE_ID
    AND AIDA.POSTED_FLAG             = 'N'
    AND PDA.PO_HEADER_ID             = PHA.PO_HEADER_ID (+)
    AND AIA.ORG_ID                   = HOU.ORGANIZATION_ID
ORDER BY
    AIA.INVOICE_NUM,
    AILA.LINE_NUMBER,
    AIDA.DISTRIBUTION_LINE_NUMBER

AP Unaccounted Invoice Distribution Details in BI Connector

Try BI Connector  back
back Back to Vault

Can’t find the report you need?

Want to tweak one to fit your business logic?

Contact Sales
Banner