Oracle Fusion ERP – Invoice Payment Details Query
SELECT
FABV.BU_NAME
, AIA.INVOICE_NUM 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 GL_DATE
, AIA.CUST_REGISTRATION_NUMBER CUST_REGISTRATION_NUMBER
, AIPA.AMOUNT TRX_AMOUNT
, AIPA.PERIOD_NAME PERIOD_NAME
, AIA.DESCRIPTION DESCRIPTION
, GL.CURRENCY_CODE FUNCTIONAL_CURRENCY
, DECODE( AIA.PAYMENT_CURRENCY_CODE , GL.CURRENCY_CODE , AIPA.AMOUNT , AIPA.PAYMENT_BASE_AMOUNT) PAYMENT_BASE_AMOUNT
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
Comments |0|
Category: OracleSaaS