View: AR_CM_LINES_BASE_V

Product:	AR - Receivables
Description:	credit memo line base extract
Implementation/DBA Data:	APPS.AR_CM_LINES_BASE_V

View Text

SELECT /*+ INDEX(HE AR_XLA_LINES_EXTRACT_N1) */ HE.EVENT_ID EVENT_ID
, CTLGD.CUST_TRX_LINE_GL_DIST_ID LINE_ID
, HE.BASE_CURRENCY_CODE CM_LINE_CUR_CODE
, HE.EXCHANGE_RATE_TYPE CM_LINE_CUR_CONVERSION_TYPE
, HE.EXCHANGE_DATE CM_LINE_CUR_CONVERSION_DATE
, HE.EXCHANGE_RATE CM_LINE_CUR_CONVERSION_RATE
, HE.ACCTD_AMOUNT CM_LINE_ACCTD_AMT
, HE.LINE_NUMBER LINE_NUMBER
, HE.LANGUAGE LANGUAGE
, HE.LEDGER_ID LEDGER_ID
, 'RA_CUST_TRX_LINE_GL_DIST_ALL' CM_DIST_TYPE
, HE.CUST_TRX_LINE_GL_DIST_ID CM_DIST_IDENTIFER
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, DECODE(CT.INVOICE_CURRENCY_CODE
, HE.BASE_CURRENCY_CODE
, TO_CHAR(HE.EVENT_ID)
, NULL) GAIN_LOSS_REF
FROM AR_XLA_LINES_EXTRACT HE
, RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, RA_CUSTOMER_TRX_ALL CT
WHERE HE.CUST_TRX_LINE_GL_DIST_ID IS NOT NULL
AND HE.LEVEL_FLAG = 'L'
AND HE.CUST_TRX_LINE_GL_DIST_ID = CTLGD.CUST_TRX_LINE_GL_DIST_ID
AND HE.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND HE.POSTING_ENTITY = 'CTLGD'
AND HE.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE') UNION SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, 'AR_DISTRIBUTIONS_ALL' CM_DIST_TYPE
, LINE_EXT.LINE_ID CM_DIST_IDENTIFER
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, DECODE(CT.INVOICE_CURRENCY_CODE
, LINE_EXT.BASE_CURRENCY_CODE
, TO_CHAR(LINE_EXT.EVENT_ID)
, NULL) GAIN_LOSS_REF
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, RA_CUSTOMER_TRX_ALL CT
WHERE CT.CUSTOMER_TRX_ID = LINE_EXT.CUSTOMER_TRX_ID
AND LINE_EXT.POSTING_ENTITY = 'APP'
AND LINE_EXT.LEVEL_FLAG = 'L'
AND LINE_EXT.CUSTOMER_TRX_ID IS NOT NULL
AND LINE_EXT.FROM_TO_FLAG = 'F'
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE') UNION SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, 'AR_DISTRIBUTIONS_ALL' CM_DIST_TYPE
, LINE_EXT.LINE_ID CM_DIST_IDENTIFER
, DIST_LAYER.GAIN_LOSS GAIN_LOSS_AMT
, DECODE(SIGN(DIST_LAYER.GAIN_LOSS)
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, DECODE(CT.INVOICE_CURRENCY_CODE
, LINE_EXT.BASE_CURRENCY_CODE
, TO_CHAR(LINE_EXT.EVENT_ID)
, NULL) GAIN_LOSS_REF
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, (SELECT NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) - NVL(D.FROM_ACCTD_AMOUNT_DR
, 0) FROM_AMOUNT
, (NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)) - (NVL(D.ACCTD_AMOUNT_CR
, 0) -NVL(D.ACCTD_AMOUNT_DR
, 0)) GAIN_LOSS
, D.SOURCE_ID SOURCE_ID
, D.LINE_ID LINE_ID
FROM AR_DISTRIBUTIONS_ALL D
WHERE D.SOURCE_TABLE = 'RA'
AND D.SOURCE_TYPE IN ('REC'
, 'EXCH_GAIN'
, 'EXCH_LOSS'
, 'ACTIVITY' ) ) DIST_LAYER
, RA_CUSTOMER_TRX_ALL CT
WHERE LINE_EXT.POSTING_ENTITY = 'APP'
AND LINE_EXT.LEVEL_FLAG = 'L'
AND LINE_EXT.CUSTOMER_TRX_ID IS NOT NULL
AND LINE_EXT.FROM_TO_FLAG = 'T'
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND DIST_LAYER.LINE_ID= LINE_EXT.LINE_ID
AND CT.CUSTOMER_TRX_ID = LINE_EXT.CUSTOMER_TRX_ID

Columns