AR_CM_LINES_BASE

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
EVENT_ID LINE_ID CM_LINE_CUR_CODE CM_LINE_CUR_CONVERSION_TYPE CM_LINE_CUR_CONVERSION_DATE CM_LINE_CUR_CONVERSION_RATE CM_LINE_ACCTD_AMT LINE_NUMBER LANGUAGE LEDGER_ID CM_DIST_TYPE CM_DIST_IDENTIFER GAIN_LOSS_AMT GAIN_LOSS_SIGN GAIN_LOSS_REF CM_APP_FROM_TO_FLAG

Name

EVENT_ID

LINE_ID

CM_LINE_CUR_CODE

CM_LINE_CUR_CONVERSION_TYPE

CM_LINE_CUR_CONVERSION_DATE

CM_LINE_CUR_CONVERSION_RATE

CM_LINE_ACCTD_AMT

LINE_NUMBER

LANGUAGE

LEDGER_ID

CM_DIST_TYPE

CM_DIST_IDENTIFER

GAIN_LOSS_AMT

GAIN_LOSS_SIGN

GAIN_LOSS_REF

CM_APP_FROM_TO_FLAG

Query

SQL_Statement
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, NULL cm_app_from_to_flag 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, line_ext.from_to_flag cm_app_from_to_flag 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, line_ext.from_to_flag cm_app_from_to_flag 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', 'DEFERRED_TAX', 'TAX', 'CURR_ROUND')) 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 AND not exists(select 'Y' from AR_DETAILED_DISTRIBUTIONS_ALL where source_dist_id = dist_layer.line_id) UNION ALL 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_DETAILED_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, line_ext.from_to_flag cm_app_from_to_flag FROM ar_xla_lines_extract line_ext , (SELECT NVL(mfar_dist.from_acctd_amount_cr,0) - NVL(mfar_dist.from_acctd_amount_dr,0) from_amount , (NVL(mfar_dist.from_acctd_amount_cr,0) -NVL(mfar_dist.from_acctd_amount_dr,0)) - (NVL(mfar_dist.acctd_amount_cr,0) -NVL(mfar_dist.acctd_amount_dr,0)) gain_loss, d.source_id source_id , d.line_id line_id, mfar_dist.DETAILED_DIST_ID DETAILED_DIST_ID FROM ar_distributions_all d, AR_DETAILED_DISTRIBUTIONS_ALL mfar_dist WHERE d.source_table = 'RA' AND d.source_type IN ('REC','EXCH_GAIN','EXCH_LOSS','ACTIVITY', 'DEFERRED_TAX', 'TAX', 'CURR_ROUND') AND d.line_id = mfar_dist.source_dist_id) 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 AND dist_layer.DETAILED_DIST_ID = line_ext.SOURCE_ID AND line_ext.SOURCE_TABLE = 'MFAR'

SQL_Statement

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,

NULL cm_app_from_to_flag

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,

line_ext.from_to_flag cm_app_from_to_flag

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,

line_ext.from_to_flag cm_app_from_to_flag

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', 'DEFERRED_TAX', 'TAX', 'CURR_ROUND')) 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

AND not exists(select 'Y' from AR_DETAILED_DISTRIBUTIONS_ALL where source_dist_id = dist_layer.line_id)

UNION ALL

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_DETAILED_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,

line_ext.from_to_flag cm_app_from_to_flag

FROM ar_xla_lines_extract line_ext ,

(SELECT NVL(mfar_dist.from_acctd_amount_cr,0) - NVL(mfar_dist.from_acctd_amount_dr,0) from_amount ,

(NVL(mfar_dist.from_acctd_amount_cr,0) -NVL(mfar_dist.from_acctd_amount_dr,0)) - (NVL(mfar_dist.acctd_amount_cr,0) -NVL(mfar_dist.acctd_amount_dr,0)) gain_loss,

d.source_id source_id ,

d.line_id line_id,

mfar_dist.DETAILED_DIST_ID DETAILED_DIST_ID

FROM ar_distributions_all d,

AR_DETAILED_DISTRIBUTIONS_ALL mfar_dist

WHERE d.source_table = 'RA'

AND d.source_type IN ('REC','EXCH_GAIN','EXCH_LOSS','ACTIVITY', 'DEFERRED_TAX', 'TAX', 'CURR_ROUND')

AND d.line_id = mfar_dist.source_dist_id) 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

AND dist_layer.DETAILED_DIST_ID = line_ext.SOURCE_ID

AND line_ext.SOURCE_TABLE = 'MFAR'