Oracle Fusion ERP – Invoice Payment Details Query

by admin

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|

Legend *) Required fields are marked
**) You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Category: OracleSaaS