Payment Details Query

SELECT FABV.BU_NAME,

       AIA.INVOICE_NUM,

       AIA.ACCTS_PAY_CODE_COMBINATION_ID

       ACCTS_PAY_CODE_COMBINATION_ID,

       AIA.INVOICE_CURRENCY_CODE

       INVOICE_CURRENCY_CODE,

       AIA.PAYMENT_CURRENCY_CODE

       PAYMENT_CURRENCY_CODE,

       AIA.INVOICE_AMOUNT

       INVOICE_AMOUNT,

       TO_CHAR(AIA.INVOICE_DATE, 'DD-MON-RRRR') INVOICE_DATE,

       AIA.SOURCE

       INVOICE_SOURCE,

       AIA.INVOICE_TYPE_LOOKUP_CODE

       INVOICE_TYPE_LOOKUP_CODE,

       AIA.DOC_SEQUENCE_VALUE

       DOC_SEQUENCE_VALUE,

       AIA.GL_DATE,

       AIA.CUST_REGISTRATION_NUMBER

       CUST_REGISTRATION_NUMBER,

       AIPA.AMOUNT TRX_AMOUNT,

       AIPA.PERIOD_NAME,

       AIA.DESCRIPTION,

       GL.CURRENCY_CODE

       FUNCTIONAL_CURRENCY,

       DECODE(AIA.PAYMENT_CURRENCY_CODE, GL.CURRENCY_CODE, AIPA.AMOUNT, AIPA.PAYMENT_BASE_AMOUNT)

       PAYMENT_BASE_AMOUNT,

       STATUS_LOOKUP_CODE,

       VOID_DATE

  FROM AP_CHECKS_ALL ACA,

       AP_INVOICE_PAYMENTS_ALL AIPA,

       AP_INVOICES_ALL AIA,

       POZ_SUPPLIERS_V PSV,

       POZ_SUPPLIER_SITES_V PSSV,

       FND_LOOKUP_VALUES FLV,

       XLE_ENTITY_PROFILES XLP,

       FUN_ALL_BUSINESS_UNITS_V FABV,

       GL_LEDGERS GL

 WHERE ACA.CHECK_ID = AIPA.CHECK_ID

       AND AIA.INVOICE_ID = AIPA.INVOICE_ID

       AND ACA.VENDOR_ID = PSV.VENDOR_ID

       AND PSSV.VENDOR_ID = PSV.VENDOR_ID

       AND PSSV.VENDOR_SITE_ID = ACA.VENDOR_SITE_ID

       AND FLV.LOOKUP_CODE = ACA.PAYMENT_METHOD_CODE

       AND FLV.LOOKUP_TYPE = 'PAYMENT METHOD'

       AND FLV.LANGUAGE = 'US'

       AND XLP.LEGAL_ENTITY_ID = ACA.LEGAL_ENTITY_ID

       AND ACA.ORG_ID = FABV.BU_ID

       AND FABV.PRIMARY_LEDGER_ID = GL.LEDGER_ID