eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_CM_LINES_BASE_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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