Query to extract Basic Invoice details from Oracle ERP Cloud

This Query helps extract invoice data from Oracle cloud ERP. This query can be pasted in Oracle BI catalog Data model.

SELECT aia.source                          INVOICE_SOURCE,

       xep.name                                   LEGAL_ENTITY,

       ffbu.bu_name                            BU_NAME,

       aia.invoice_num                       INVOICE_NUMBER,

       aia.approval_status                 INVOICE_STATUS,

       aia.invoice_date                       INVOICE_DATE,

       hz.party_name                         SUPPLIER_NAME,

       aia.invoice_type_lookup_code INVOICE_TYPE,

       aia.invoice_amount,

       Decode(aia.payment_status_flag,

              ‘Y’,’Fully Paid’,

              ‘P’,’Partial Paid’,

              ‘Unpaid’)                              PAYMENT_STATUS,

       aia.invoice_currency_code     INVOICE_CURRENCY_CODE,

   aia.payment_method_code        PAYMENT_METHOD_CODE

FROM   ap_invoices_all aia,

       xle_entity_profiles xep,

       fun_fin_business_units_v ffbu,

   hz_parties hz

WHERE ffbu.bu_id = aia.org_id

AND    aia.legal_entity_id = xep.legal_entity_id

AND    hz.party_id=aia.party_id

–AND    aia.invoice_date > SYSDATE-30