[Home] [Help]
SELECT /*+INDEX(l ar_xla_lines_extract_n1)*/ ctlaf.customer_trx_id cm_app_customer_trx_id ,ctlaf.customer_trx_line_id cm_app_customer_trx_line_id ,ctlaf.line_number cm_app_line_number ,ctlaf.line_type cm_app_line_type ,ctlaf.interface_line_context cm_app_interface_line_ct ,ctlaf.interface_line_attribute1 cm_app_interface_line_attr1 ,ctlaf.interface_line_attribute2 cm_app_interface_line_attr2 ,ctlaf.interface_line_attribute3 cm_app_interface_line_attr3 ,ctlaf.interface_line_attribute4 cm_app_interface_line_attr4 ,ctlaf.interface_line_attribute5 cm_app_interface_line_attr5 ,ctlaf.interface_line_attribute6 cm_app_interface_line_attr6 ,ctlaf.interface_line_attribute7 cm_app_interface_line_attr7 ,ctlaf.interface_line_attribute8 cm_app_interface_line_attr8 ,ctlaf.interface_line_attribute9 cm_app_interface_line_attr9 ,ctlaf.interface_line_attribute10 cm_app_interface_line_attr10 ,ctlaf.interface_line_attribute11 cm_app_interface_line_attr11 ,ctlaf.interface_line_attribute12 cm_app_interface_line_attr12 ,ctlaf.interface_line_attribute13 cm_app_interface_line_attr13 ,decode(ctlaf.line_type, 'LINE', ctlaf.interface_line_attribute14 , decode(ctlaf2.interface_line_context, 'OKL_CONTRACTS', nvl(ctlaf.interface_line_attribute14, ctlaf2.interface_line_attribute14), 'OKL_INVESTOR', nvl(ctlaf.interface_line_attribute14, ctlaf2.interface_line_attribute14), ctlaf.interface_line_attribute14)) cm_app_interface_line_attr14 ,ctlaf.interface_line_attribute15 cm_app_interface_line_attr15 ,l.line_id cm_app_extract_line_id ,l.paired_ccid cm_app_extract_paired_ccid ,l.event_id event_id ,l.line_number line_number ,l.language language ,l.ledger_id ledger_id ,l.from_to_flag from_to_flag ,'AR_DISTRIBUTIONS_ALL' cm_app_distribution_type ,ardist.line_id cm_app_dist_line_id ,ardist.source_id cm_app_dist_source_id ,ardist.source_table cm_app_dist_source_table ,ardist.source_type cm_app_dist_source_type ,ardist.code_combination_id cm_app_dist_ccid FROM ar_xla_lines_extract l ,ar_distributions_all ardist ,ra_customer_trx_lines_all ctlaf ,ra_customer_trx_lines_all ctlaf2 WHERE ardist.line_id = l.line_id AND ardist.ref_customer_trx_line_id = ctlaf.customer_trx_line_id AND ctlaf.customer_trx_line_id(+) = l.customer_trx_line_id AND l.level_flag = 'L' AND l.posting_entity = 'APP' AND l.event_type_code IN ('CM_CREATE', 'CM_UPDATE') AND L.mfar_additional_entry = 'N' AND ctlaf.customer_trx_id = ctlaf2.customer_trx_id(+) AND ctlaf.link_to_cust_trx_line_id = ctlaf2.customer_trx_line_id(+)
SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1)*/ CTLAF.CUSTOMER_TRX_ID CM_APP_CUSTOMER_TRX_ID
, CTLAF.CUSTOMER_TRX_LINE_ID CM_APP_CUSTOMER_TRX_LINE_ID
, CTLAF.LINE_NUMBER CM_APP_LINE_NUMBER
, CTLAF.LINE_TYPE CM_APP_LINE_TYPE
, CTLAF.INTERFACE_LINE_CONTEXT CM_APP_INTERFACE_LINE_CT
, CTLAF.INTERFACE_LINE_ATTRIBUTE1 CM_APP_INTERFACE_LINE_ATTR1
, CTLAF.INTERFACE_LINE_ATTRIBUTE2 CM_APP_INTERFACE_LINE_ATTR2
, CTLAF.INTERFACE_LINE_ATTRIBUTE3 CM_APP_INTERFACE_LINE_ATTR3
, CTLAF.INTERFACE_LINE_ATTRIBUTE4 CM_APP_INTERFACE_LINE_ATTR4
, CTLAF.INTERFACE_LINE_ATTRIBUTE5 CM_APP_INTERFACE_LINE_ATTR5
, CTLAF.INTERFACE_LINE_ATTRIBUTE6 CM_APP_INTERFACE_LINE_ATTR6
, CTLAF.INTERFACE_LINE_ATTRIBUTE7 CM_APP_INTERFACE_LINE_ATTR7
, CTLAF.INTERFACE_LINE_ATTRIBUTE8 CM_APP_INTERFACE_LINE_ATTR8
, CTLAF.INTERFACE_LINE_ATTRIBUTE9 CM_APP_INTERFACE_LINE_ATTR9
, CTLAF.INTERFACE_LINE_ATTRIBUTE10 CM_APP_INTERFACE_LINE_ATTR10
, CTLAF.INTERFACE_LINE_ATTRIBUTE11 CM_APP_INTERFACE_LINE_ATTR11
, CTLAF.INTERFACE_LINE_ATTRIBUTE12 CM_APP_INTERFACE_LINE_ATTR12
, CTLAF.INTERFACE_LINE_ATTRIBUTE13 CM_APP_INTERFACE_LINE_ATTR13
, DECODE(CTLAF.LINE_TYPE
, 'LINE'
, CTLAF.INTERFACE_LINE_ATTRIBUTE14
, DECODE(CTLAF2.INTERFACE_LINE_CONTEXT
, 'OKL_CONTRACTS'
, NVL(CTLAF.INTERFACE_LINE_ATTRIBUTE14
, CTLAF2.INTERFACE_LINE_ATTRIBUTE14)
, 'OKL_INVESTOR'
, NVL(CTLAF.INTERFACE_LINE_ATTRIBUTE14
, CTLAF2.INTERFACE_LINE_ATTRIBUTE14)
, CTLAF.INTERFACE_LINE_ATTRIBUTE14)) CM_APP_INTERFACE_LINE_ATTR14
, CTLAF.INTERFACE_LINE_ATTRIBUTE15 CM_APP_INTERFACE_LINE_ATTR15
, L.LINE_ID CM_APP_EXTRACT_LINE_ID
, L.PAIRED_CCID CM_APP_EXTRACT_PAIRED_CCID
, L.EVENT_ID EVENT_ID
, L.LINE_NUMBER LINE_NUMBER
, L.LANGUAGE LANGUAGE
, L.LEDGER_ID LEDGER_ID
, L.FROM_TO_FLAG FROM_TO_FLAG
, 'AR_DISTRIBUTIONS_ALL' CM_APP_DISTRIBUTION_TYPE
, ARDIST.LINE_ID CM_APP_DIST_LINE_ID
, ARDIST.SOURCE_ID CM_APP_DIST_SOURCE_ID
, ARDIST.SOURCE_TABLE CM_APP_DIST_SOURCE_TABLE
, ARDIST.SOURCE_TYPE CM_APP_DIST_SOURCE_TYPE
, ARDIST.CODE_COMBINATION_ID CM_APP_DIST_CCID
FROM AR_XLA_LINES_EXTRACT L
, AR_DISTRIBUTIONS_ALL ARDIST
, RA_CUSTOMER_TRX_LINES_ALL CTLAF
, RA_CUSTOMER_TRX_LINES_ALL CTLAF2
WHERE ARDIST.LINE_ID = L.LINE_ID
AND ARDIST.REF_CUSTOMER_TRX_LINE_ID = CTLAF.CUSTOMER_TRX_LINE_ID
AND CTLAF.CUSTOMER_TRX_LINE_ID(+) = L.CUSTOMER_TRX_LINE_ID
AND L.LEVEL_FLAG = 'L'
AND L.POSTING_ENTITY = 'APP'
AND L.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND L.MFAR_ADDITIONAL_ENTRY = 'N'
AND CTLAF.CUSTOMER_TRX_ID = CTLAF2.CUSTOMER_TRX_ID(+)
AND CTLAF.LINK_TO_CUST_TRX_LINE_ID = CTLAF2.CUSTOMER_TRX_LINE_ID(+)
|
|
|
|