View: FII_AR_REC_FCV

Product:	FII - Financial Intelligence
Description:	FII_AR_REC_FCV is an internal view used to support Operations Indicators Portlet
Implementation/DBA Data:	APPS.FII_AR_REC_FCV

View Text

SELECT INV_H.CUSTOMER_TRX_ID
, INV.SET_OF_BOOKS_ID
, INV.SET_OF_BOOKS_ID||'-'||INST.INSTANCE_CODE
, DECODE(INV_H.BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, INV_H.BILL_TO_CUSTOMER_ID||'-'||INST.INSTANCE_CODE|| '-CUST_ACCT-TPRT')
, INV.ORG_ID||'-'||INST.INSTANCE_CODE
, TRUNC(INV.GL_DATE)
, INV.GL_DATE
, INV_H.TRX_DATE
, INST.INSTANCE_CODE
, SOB.CURRENCY_CODE
, INV.ACCTD_AMOUNT
FROM EDW_LOCAL_INSTANCE INST
, RA_CUST_TRX_LINE_GL_DIST_ALL INV
, RA_CUSTOMER_TRX_ALL INV_H
, GL_SETS_OF_BOOKS SOB
, RA_CUST_TRX_TYPES_ALL INV_T
WHERE SOB.SET_OF_BOOKS_ID = INV.SET_OF_BOOKS_ID
AND INV_H.CUSTOMER_TRX_ID = INV.CUSTOMER_TRX_ID AND INV.ACCOUNT_CLASS = 'REC' AND INV.ACCOUNT_SET_FLAG = 'N' AND INV_H.COMPLETE_FLAG = 'Y'
AND INV_T.CUST_TRX_TYPE_ID (+) = INV_H.CUST_TRX_TYPE_ID
AND INV_T.ORG_ID(+) = INV_H.ORG_ID
AND NVL(INV_T.POST_TO_GL
, 'Y') = 'Y'
AND NVL(INV_T.ACCOUNTING_AFFECT_FLAG
, 'Y') = 'Y' UNION ALL SELECT CT.CUSTOMER_TRX_ID
, ADJ.SET_OF_BOOKS_ID
, ADJ.SET_OF_BOOKS_ID||'-'||INST.INSTANCE_CODE
, DECODE(CT.BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL)
, 'NA_EDW'
, CT.BILL_TO_CUSTOMER_ID||'-'||INST.INSTANCE_CODE|| '-CUST_ACCT-TPRT')
, ADJ.ORG_ID||'-'||INST.INSTANCE_CODE
, TRUNC(ADJ.GL_DATE)
, ADJ.GL_DATE
, CT.TRX_DATE
, INST.INSTANCE_CODE
, SOB.CURRENCY_CODE
, NVL(AD.ACCTD_AMOUNT_DR
, 0) - NVL(ACCTD_AMOUNT_CR
, 0) FROM EDW_LOCAL_INSTANCE INST
, AR_ADJUSTMENTS_ALL ADJ
, RA_CUSTOMER_TRX_ALL CT
, GL_SETS_OF_BOOKS SOB
, AR_DISTRIBUTIONS_ALL AD WHERE SOB.SET_OF_BOOKS_ID = ADJ.SET_OF_BOOKS_ID
AND NVL(ADJ.STATUS
, 'A') = 'A' AND NVL(ADJ.POSTABLE
, 'Y') = 'Y' AND CT.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID AND CT.COMPLETE_FLAG = 'Y' AND AD.SOURCE_ID = ADJ.ADJUSTMENT_ID AND AD.SOURCE_TABLE = 'ADJ' AND AD.SOURCE_TYPE = 'REC'

Columns