View: FII_AR_OLTP_INV_V

View Text

SELECT INV_H.CUSTOMER_TRX_ID
, INST.INSTANCE_CODE
, INV.ORG_ID
, INV.SET_OF_BOOKS_ID
, SOB.PERIOD_SET_NAME
, SOB.ACCOUNTED_PERIOD_TYPE
, INV.GL_DATE
, SOB.CURRENCY_CODE
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( INV.AMOUNT
, INV.ACCTD_AMOUNT
, INV_H.INVOICE_CURRENCY_CODE
, SOB.CURRENCY_CODE
, INV_H.TRX_DATE
, INV_H.EXCHANGE_RATE_TYPE)
, 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
, INST.INSTANCE_CODE
, ADJ.ORG_ID
, ADJ.SET_OF_BOOKS_ID
, SOB.PERIOD_SET_NAME
, SOB.ACCOUNTED_PERIOD_TYPE
, ADJ.GL_DATE
, SOB.CURRENCY_CODE
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( NVL(AD.AMOUNT_DR
, 0) - NVL(AD.AMOUNT_CR
, 0)
, NVL(AD.ACCTD_AMOUNT_DR
, 0) - NVL(AD.ACCTD_AMOUNT_CR
, 0)
, CT.INVOICE_CURRENCY_CODE
, SOB.CURRENCY_CODE
, ADJ.GL_DATE
, AD.CURRENCY_CONVERSION_TYPE)
, 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