DBA Data[Home] [Help]

VIEW: APPS.AR_DISTRIBUTIONS_BASE_V

Source

View Text - Preformatted

SELECT /*+INDEX (li ar_xla_lines_extract_n1)*/ li.event_id event_id, li.base_currency_code base_currency, li.line_id line_id, li.exchange_rate_type dist_cur_conversion_type, li.exchange_rate dist_cur_conversion_rate, li.exchange_date dist_cur_conversion_date, li.acctd_amount dist_acctd_amt, li.exchange_rate_type dist_to_cur_conversion_type, li.exchange_rate dist_to_cur_conversion_rate, li.exchange_date dist_to_cur_conversion_date, li.acctd_amount dist_to_acctd_amt, NULL gain_loss_amt, NULL gain_loss_sign, li.line_number line_number, li.language language, li.ledger_id ledger_id, 'Y' recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract li WHERE li.posting_entity = 'ADJ' AND li.level_flag = 'L' AND li.mfar_additional_entry = 'N' UNION /*********************** * 2 Cash Receipts * ***********************/ SELECT /*+INDEX (li ar_xla_lines_extract_n1)*/ li.event_id event_id, li.base_currency_code base_currency, li.line_id line_id, li.exchange_rate_type dist_cur_conversion_type, li.exchange_rate dist_cur_conversion_rate, li.exchange_date dist_cur_conversion_date, DECODE(NVL(li.receivables_trx_id,0), -16,from_acctd_amount, li.acctd_amount) dist_acctd_amt, li.exchange_rate_type dist_to_cur_conversion_type, li.exchange_rate dist_to_cur_conversion_rate, li.exchange_date dist_to_cur_conversion_date, li.acctd_amount dist_to_acctd_amt, NULL gain_loss_amt, NULL gain_loss_sign, li.line_number line_number, li.language language, li.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract li WHERE li.posting_entity = 'CRH' AND li.level_flag = 'L' AND li.mfar_additional_entry = 'N' UNION /*********************** * 3 Bill Receivables * ***********************/ SELECT /*+INDEX (li ar_xla_lines_extract_n1)*/ li.event_id event_id, li.base_currency_code base_currency, li.line_id line_id, li.exchange_rate_type dist_cur_conversion_type, li.exchange_rate dist_cur_conversion_rate, li.exchange_date dist_cur_conversion_date, li.acctd_amount dist_acctd_amt, li.exchange_rate_type dist_to_cur_conversion_type, li.exchange_rate dist_to_cur_conversion_rate, li.exchange_date dist_to_cur_conversion_date, li.acctd_amount dist_to_acctd_amt, NULL gain_loss_amt, NULL gain_loss_sign, li.line_number line_number, li.language language, li.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract li WHERE li.posting_entity = 'TH' AND li.level_flag = 'L' AND li.mfar_additional_entry = 'N' UNION /************************ * 4 To doc for REC APP * ************************/ SELECT /*+ leading(licr, dist, litrx) INDEX(licr ar_xla_lines_extract_n1) INDEX(litrx ar_xla_lines_extract_n2) */ licr.event_id event_id, licr.base_currency_code base_currency, licr.line_id line_id, licr.exchange_rate_type dist_cur_conversion_type, licr.exchange_rate dist_cur_conversion_rate, licr.exchange_date dist_cur_conversion_date, decode(dist.acctd_amount_applied_to, 0, decode(dist.acctd_amount_applied_from, 0, dist.from_amount, decode(NVL(licr.exchange_rate, 1), NVL(litrx.exchange_rate, 1), decode(cr.currency_code, ct.invoice_currency_code, dist.from_amount, licr.from_acctd_amount), licr.from_acctd_amount)), dist.from_amount) dist_acctd_amt, litrx.exchange_rate_type dist_to_cur_conversion_type, litrx.exchange_rate dist_to_cur_conversion_rate, litrx.exchange_date dist_to_cur_conversion_date, litrx.acctd_amount dist_to_acctd_amt, dist.gain_loss gain_loss_amt, DECODE(SIGN(dist.gain_loss), -1, '-','+') gain_loss_sign, licr.line_number line_number, licr.language language, licr.ledger_id ledger_id, 'Y' recp_override_acctd_amt, dist.activity_bucket dist_activity_bucket FROM ar_xla_lines_extract licr, ar_xla_lines_extract litrx, ar_cash_receipts_all cr, ra_customer_trx_all ct, (SELECT decode(nvl(ra.acctd_amount_applied_from,0),nvl(ra.acctd_amount_applied_to,0),NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0), decode(d.from_acctd_amount_cr, '',decode(d.from_acctd_amount_dr, '',decode(d.source_type,'TAX',decode(d.source_type_secondary,'RECONCILE',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0),NVL(d.from_acctd_amount_cr,0) - NVL(d.from_acctd_amount_dr,0)), 'DEFERRED_TAX',decode(d.source_type_secondary,'RECONCILE',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0),NVL(d.from_acctd_amount_cr,0) - NVL(d.from_acctd_amount_dr,0)), decode(decode(d.source_type,'REC',nvl(ra.acctd_amount_applied_to,0)+nvl(ra.acctd_earned_discount_taken,0)+nvl(ra.acctd_unearned_discount_taken,0), 'OTHER ACC',nvl(ra.acctd_amount_applied_from,0), 'ACC',nvl(ra.acctd_amount_applied_from,0), NVL(d.from_acctd_amount_cr,0)- NVL(d.from_acctd_amount_dr,0)), nvl(d.acctd_amount_dr,0)*-1+nvl(d.acctd_amount_cr,0),nvl(ra.acctd_amount_applied_from,0), NVL(d.from_acctd_amount_cr,0) -NVL(d.from_acctd_amount_dr,0))), decode(d.source_type,'EDISC',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'UNEDISC',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'EDISC_NON_REC_TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'UNEDISC_NON_REC_TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'DEFERRED_TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'REC',decode(d.ref_mf_dist_flag,'D',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0),NVL(d.from_acctd_amount_cr,0) -NVL(d.from_acctd_amount_dr,0)) ,NVL(d.from_acctd_amount_cr,0) -NVL(d.from_acctd_amount_dr,0))), decode(d.source_type,'EDISC',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'UNEDISC',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'EDISC_NON_REC_TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'UNEDISC_NON_REC_TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'DEFERRED_TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'TAX',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0) ,'REC',decode(d.ref_mf_dist_flag,'D',NVL(d.acctd_amount_cr,0) - NVL(d.acctd_amount_dr,0),NVL(d.from_acctd_amount_cr,0) -NVL(d.from_acctd_amount_dr,0)) ,NVL(d.from_acctd_amount_cr,0) -NVL(d.from_acctd_amount_dr,0)))) from_amount, decode(nvl(ra.acctd_amount_applied_from,0),nvl(ra.acctd_amount_applied_to,0),to_number(null), decode(d.from_acctd_amount_cr, '',decode(d.from_acctd_amount_dr, '',decode(d.source_type,'REC',nvl(ra.acctd_amount_applied_from,0)-nvl(ra.acctd_amount_applied_to,0),0), (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))), (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, d.activity_bucket activity_bucket, ra.acctd_amount_applied_to acctd_amount_applied_to, ra.acctd_amount_applied_from acctd_amount_applied_from FROM ar_distributions_all d, ar_receivable_applications_all ra where d.source_id = ra.receivable_application_id and d.source_table = 'RA' and ra.posting_control_id = -3 and source_type IN ('REC','OTHER ACC','ACC','BANK_CHARGES','ACTIVITY','FACTOR','REMITTANCE', 'TAX','DEFERRED_TAX','UNEDISC','EDISC','CURR_ROUND', 'SHORT_TERM_DEBT','EXCH_GAIN','EXCH_LOSS','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX') ) dist WHERE licr.posting_entity = 'APP' AND licr.level_flag = 'L' AND licr.cash_receipt_id IS NOT NULL AND licr.cash_receipt_id = cr.cash_receipt_id AND licr.customer_trx_id IS NULL AND licr.from_to_flag = 'F' AND litrx.posting_entity = 'APP' AND litrx.level_flag = 'L' AND litrx.cash_receipt_id IS NULL AND litrx.customer_trx_id IS NOT NULL AND litrx.customer_trx_id = ct.customer_trx_id AND litrx.from_to_flag = 'T' AND licr.header_table_id = litrx.header_table_id AND licr.event_id = litrx.event_id AND dist.source_id = litrx.header_table_id AND dist.line_id = licr.line_id AND dist.line_id = litrx.line_id AND licr.event_type_code IN ('RECP_CREATE','RECP_UPDATE','RECP_RATE_ADJUST','RECP_REVERSE') AND NVL(licr.ADDITIONAL_CHAR1,'XX') <> 'R12_11ICASH_POST' AND NVL(litrx.ADDITIONAL_CHAR1,'XX') <> 'R12_11ICASH_POST' AND licr.mfar_additional_entry = 'N' AND litrx.mfar_additional_entry = 'N' UNION /******************** * CM APP doc * ********************/ SELECT /*+INDEX (line_ext ar_xla_lines_extract_n1)*/ line_ext.event_id event_id, line_ext.base_currency_code base_currency, line_ext.line_id line_id, line_ext.exchange_rate_type dist_cur_conversion_type, line_ext.exchange_rate dist_cur_conversion_rate, line_ext.exchange_date dist_cur_conversion_date, dist_layer.from_amount dist_acctd_amt, line_ext.exchange_rate_type dist_to_cur_conversion_type, line_ext.exchange_rate dist_to_cur_conversion_rate, line_ext.exchange_date dist_to_cur_conversion_date, line_ext.acctd_amount dist_to_acctd_amt, dist_layer.gain_loss gain_loss_amt, DECODE(SIGN(dist_layer.gain_loss), -1, '-','+') gain_loss_sign, line_ext.line_number line_number, line_ext.language language, line_ext.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket 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') ) dist_layer 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 = 'F' AND dist_layer.line_id= line_ext.line_id AND line_ext.event_type_code IN ('CM_CREATE','CM_UPDATE') AND NVL(line_ext.ADDITIONAL_CHAR1,'XX') <> 'R12_11ICASH_POST' AND line_ext.mfar_additional_entry = 'N' UNION SELECT /*+INDEX (line_ext ar_xla_lines_extract_n1)*/ line_ext.event_id event_id, line_ext.base_currency_code base_currency, line_ext.line_id line_id, line_ext.exchange_rate_type dist_cur_conversion_type, line_ext.exchange_rate dist_cur_conversion_rate, line_ext.exchange_date dist_cur_conversion_date, dist_layer.from_amount dist_acctd_amt, line_ext.exchange_rate_type dist_to_cur_conversion_type, line_ext.exchange_rate dist_to_cur_conversion_rate, line_ext.exchange_date dist_to_cur_conversion_date, line_ext.acctd_amount dist_to_acctd_amt, dist_layer.gain_loss gain_loss_amt, DECODE(SIGN(dist_layer.gain_loss), -1, '-','+') gain_loss_sign, line_ext.line_number line_number, line_ext.language language, line_ext.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket 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','ACTIVITY' ) ) dist_layer 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 dist_layer.line_id= line_ext.line_id AND line_ext.event_type_code IN ('CM_CREATE','CM_UPDATE') AND NVL(line_ext.ADDITIONAL_CHAR1,'XX') <> 'R12_11ICASH_POST' AND line_ext.mfar_additional_entry = 'N' /****************************** * Misc Cash Distributions * ******************************/ UNION SELECT /*+INDEX (li ar_xla_lines_extract_n1)*/ li.event_id event_id, li.base_currency_code base_currency, li.line_id line_id, li.exchange_rate_type dist_cur_conversion_type, li.exchange_rate dist_cur_conversion_rate, li.exchange_date dist_cur_conversion_date, li.acctd_amount dist_acctd_amt, li.exchange_rate_type dist_to_cur_conversion_type, li.exchange_rate dist_to_cur_conversion_rate, li.exchange_date dist_to_cur_conversion_date, li.acctd_amount dist_to_acctd_amt, NULL gain_loss_amt, NULL gain_loss_sign, li.line_number line_number, li.language language, li.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract li WHERE li.posting_entity = 'MCD' AND li.level_flag = 'L' AND li.mfar_additional_entry = 'N' UNION /******************************* * Cash Basis Upgrade RECP APP * *******************************/ SELECT /*+INDEX(licr ar_xla_lines_extract_n1) INDEX(litrx ar_xla_lines_extract_n1)*/ licr.event_id event_id, licr.base_currency_code base_currency, licr.line_id line_id, licr.exchange_rate_type dist_cur_conversion_type, licr.exchange_rate dist_cur_conversion_rate, licr.exchange_date dist_cur_conversion_date, d.from_acctd_amount dist_acctd_amt, litrx.exchange_rate_type dist_to_cur_conversion_type, litrx.exchange_rate dist_to_cur_conversion_rate, litrx.exchange_date dist_to_cur_conversion_date, litrx.acctd_amount dist_to_acctd_amt, NVL(d.from_acctd_amount,0) - NVL(d.acctd_amount,0) gain_loss_amt, DECODE(SIGN(NVL(d.from_acctd_amount,0)- NVL(d.acctd_amount,0)), 1,'+','-') gain_loss_sign, licr.line_number line_number, licr.language language, licr.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract licr, ar_xla_lines_extract litrx, ar_cash_basis_dists_all d WHERE licr.posting_entity = 'APP' AND licr.level_flag = 'L' AND licr.cash_receipt_id IS NOT NULL AND licr.customer_trx_id IS NULL AND licr.from_to_flag = 'F' AND litrx.posting_entity = 'APP' AND litrx.level_flag = 'L' AND litrx.cash_receipt_id IS NULL AND litrx.customer_trx_id IS NOT NULL AND litrx.from_to_flag = 'T' AND licr.header_table_id = litrx.header_table_id AND licr.event_id = litrx.event_id AND d.receivable_Application_id = litrx.header_table_id AND d.cash_basis_distribution_id = licr.line_id AND d.cash_basis_distribution_id = litrx.line_id AND licr.event_type_code IN ('RECP_CREATE','RECP_UPDATE','RECP_RATE_ADJUST','RECP_REVERSE') AND licr.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST' AND litrx.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST' AND licr.mfar_additional_entry = 'N' AND litrx.mfar_additional_entry = 'N' UNION /***************************** * Cash Basis Upgrade CM APP * *****************************/ SELECT /*+INDEX (line_ext ar_xla_lines_extract_n1)*/ line_ext.event_id event_id, line_ext.base_currency_code base_currency, line_ext.line_id line_id, line_ext.exchange_rate_type dist_cur_conversion_type, line_ext.exchange_rate dist_cur_conversion_rate, line_ext.exchange_date dist_cur_conversion_date, dist_layer.from_acctd_amount dist_acctd_amt, line_ext.exchange_rate_type dist_to_cur_conversion_type, line_ext.exchange_rate dist_to_cur_conversion_rate, line_ext.exchange_date dist_to_cur_conversion_date, line_ext.acctd_amount dist_to_acctd_amt, NVL(dist_layer.from_acctd_amount,0) - NVL(dist_layer.acctd_amount,0) gain_loss_amt, DECODE(SIGN( NVL(dist_layer.from_acctd_amount,0) - NVL(dist_layer.acctd_amount,0)), -1, '-','+') gain_loss_sign, line_ext.line_number line_number, line_ext.language language, line_ext.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract line_ext, ar_cash_basis_dists_all dist_layer 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 = 'F' AND dist_layer.cash_basis_distribution_id = line_ext.line_id AND line_ext.event_type_code IN ('CM_CREATE','CM_UPDATE') AND line_ext.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST' AND line_ext.mfar_additional_entry = 'N' UNION SELECT /*+INDEX (line_ext ar_xla_lines_extract_n1)*/ line_ext.event_id event_id, line_ext.base_currency_code base_currency, line_ext.line_id line_id, line_ext.exchange_rate_type dist_cur_conversion_type, line_ext.exchange_rate dist_cur_conversion_rate, line_ext.exchange_date dist_cur_conversion_date, dist_layer.from_acctd_amount dist_acctd_amt, line_ext.exchange_rate_type dist_to_cur_conversion_type, line_ext.exchange_rate dist_to_cur_conversion_rate, line_ext.exchange_date dist_to_cur_conversion_date, line_ext.acctd_amount dist_to_acctd_amt, NVL(dist_layer.from_acctd_amount,0) - NVL(dist_layer.acctd_amount,0) gain_loss_amt, DECODE(SIGN(NVL(dist_layer.from_acctd_amount,0) - NVL(dist_layer.acctd_amount,0)), -1, '-','+') gain_loss_sign, line_ext.line_number line_number, line_ext.language language, line_ext.ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract line_ext, ar_cash_basis_dists_all dist_layer 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 dist_layer.cash_basis_distribution_id = line_ext.line_id AND line_ext.event_type_code IN ('CM_CREATE','CM_UPDATE') AND line_ext.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST' AND line_ext.mfar_additional_entry = 'N' UNION /*MFAR Additional Entries*/ SELECT event_id event_id, base_currency_code base_currency, line_id line_id, from_exchange_rate_type dist_cur_conversion_type, from_exchange_rate dist_cur_conversion_rate, from_exchange_date dist_cur_conversion_date, from_acctd_amount dist_acctd_amt, exchange_rate_type dist_to_cur_conversion_type, exchange_rate dist_to_cur_conversion_rate, exchange_date dist_to_cur_conversion_date, acctd_amount dist_to_acctd_amt, from_acctd_amount - acctd_amount gain_loss_amt, '+' gain_loss_sign, line_number line_number, 'US' language, ledger_id ledger_id, NULL recp_override_acctd_amt, NULL dist_activity_bucket FROM ar_xla_lines_extract WHERE mfar_additional_entry = 'Y'
View Text - HTML Formatted

SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, 'Y' RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'ADJ'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /*********************** * 2 CASH RECEIPTS * ***********************/ SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DECODE(NVL(LI.RECEIVABLES_TRX_ID
, 0)
, -16
, FROM_ACCTD_AMOUNT
, LI.ACCTD_AMOUNT) DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'CRH'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /*********************** * 3 BILL RECEIVABLES * ***********************/ SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'TH'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /************************ * 4 TO DOC FOR REC APP * ************************/ SELECT /*+ LEADING(LICR
, DIST
, LITRX) INDEX(LICR AR_XLA_LINES_EXTRACT_N1) INDEX(LITRX AR_XLA_LINES_EXTRACT_N2) */ LICR.EVENT_ID EVENT_ID
, LICR.BASE_CURRENCY_CODE BASE_CURRENCY
, LICR.LINE_ID LINE_ID
, LICR.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LICR.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LICR.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DECODE(DIST.ACCTD_AMOUNT_APPLIED_TO
, 0
, DECODE(DIST.ACCTD_AMOUNT_APPLIED_FROM
, 0
, DIST.FROM_AMOUNT
, DECODE(NVL(LICR.EXCHANGE_RATE
, 1)
, NVL(LITRX.EXCHANGE_RATE
, 1)
, DECODE(CR.CURRENCY_CODE
, CT.INVOICE_CURRENCY_CODE
, DIST.FROM_AMOUNT
, LICR.FROM_ACCTD_AMOUNT)
, LICR.FROM_ACCTD_AMOUNT))
, DIST.FROM_AMOUNT) DIST_ACCTD_AMT
, LITRX.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LITRX.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LITRX.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LITRX.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, DIST.GAIN_LOSS GAIN_LOSS_AMT
, DECODE(SIGN(DIST.GAIN_LOSS)
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LICR.LINE_NUMBER LINE_NUMBER
, LICR.LANGUAGE LANGUAGE
, LICR.LEDGER_ID LEDGER_ID
, 'Y' RECP_OVERRIDE_ACCTD_AMT
, DIST.ACTIVITY_BUCKET DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LICR
, AR_XLA_LINES_EXTRACT LITRX
, AR_CASH_RECEIPTS_ALL CR
, RA_CUSTOMER_TRX_ALL CT
, (SELECT DECODE(NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, DECODE(D.FROM_ACCTD_AMOUNT_CR
, ''
, DECODE(D.FROM_ACCTD_AMOUNT_DR
, ''
, DECODE(D.SOURCE_TYPE
, 'TAX'
, DECODE(D.SOURCE_TYPE_SECONDARY
, 'RECONCILE'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) - NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, 'DEFERRED_TAX'
, DECODE(D.SOURCE_TYPE_SECONDARY
, 'RECONCILE'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) - NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, DECODE(DECODE(D.SOURCE_TYPE
, 'REC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)+NVL(RA.ACCTD_EARNED_DISCOUNT_TAKEN
, 0)+NVL(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN
, 0)
, 'OTHER ACC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, 'ACC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0)- NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, NVL(D.ACCTD_AMOUNT_DR
, 0)*-1+NVL(D.ACCTD_AMOUNT_CR
, 0)
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)))
, DECODE(D.SOURCE_TYPE
, 'EDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'EDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'DEFERRED_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'REC'
, DECODE(D.REF_MF_DIST_FLAG
, 'D'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)))
, DECODE(D.SOURCE_TYPE
, 'EDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'EDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'UNEDISC_NON_REC_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'DEFERRED_TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'TAX'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, 'REC'
, DECODE(D.REF_MF_DIST_FLAG
, 'D'
, NVL(D.ACCTD_AMOUNT_CR
, 0) - NVL(D.ACCTD_AMOUNT_DR
, 0)
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0))
, NVL(D.FROM_ACCTD_AMOUNT_CR
, 0) -NVL(D.FROM_ACCTD_AMOUNT_DR
, 0)))) FROM_AMOUNT
, DECODE(NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)
, NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)
, TO_NUMBER(NULL)
, DECODE(D.FROM_ACCTD_AMOUNT_CR
, ''
, DECODE(D.FROM_ACCTD_AMOUNT_DR
, ''
, DECODE(D.SOURCE_TYPE
, 'REC'
, NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)-NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0)
, 0)
, (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)))
, (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
, D.ACTIVITY_BUCKET ACTIVITY_BUCKET
, RA.ACCTD_AMOUNT_APPLIED_TO ACCTD_AMOUNT_APPLIED_TO
, RA.ACCTD_AMOUNT_APPLIED_FROM ACCTD_AMOUNT_APPLIED_FROM
FROM AR_DISTRIBUTIONS_ALL D
, AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE D.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID
AND D.SOURCE_TABLE = 'RA'
AND RA.POSTING_CONTROL_ID = -3
AND SOURCE_TYPE IN ('REC'
, 'OTHER ACC'
, 'ACC'
, 'BANK_CHARGES'
, 'ACTIVITY'
, 'FACTOR'
, 'REMITTANCE'
, 'TAX'
, 'DEFERRED_TAX'
, 'UNEDISC'
, 'EDISC'
, 'CURR_ROUND'
, 'SHORT_TERM_DEBT'
, 'EXCH_GAIN'
, 'EXCH_LOSS'
, 'EDISC_NON_REC_TAX'
, 'UNEDISC_NON_REC_TAX') ) DIST
WHERE LICR.POSTING_ENTITY = 'APP'
AND LICR.LEVEL_FLAG = 'L'
AND LICR.CASH_RECEIPT_ID IS NOT NULL
AND LICR.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND LICR.CUSTOMER_TRX_ID IS NULL
AND LICR.FROM_TO_FLAG = 'F'
AND LITRX.POSTING_ENTITY = 'APP'
AND LITRX.LEVEL_FLAG = 'L'
AND LITRX.CASH_RECEIPT_ID IS NULL
AND LITRX.CUSTOMER_TRX_ID IS NOT NULL
AND LITRX.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND LITRX.FROM_TO_FLAG = 'T'
AND LICR.HEADER_TABLE_ID = LITRX.HEADER_TABLE_ID
AND LICR.EVENT_ID = LITRX.EVENT_ID
AND DIST.SOURCE_ID = LITRX.HEADER_TABLE_ID
AND DIST.LINE_ID = LICR.LINE_ID
AND DIST.LINE_ID = LITRX.LINE_ID
AND LICR.EVENT_TYPE_CODE IN ('RECP_CREATE'
, 'RECP_UPDATE'
, 'RECP_RATE_ADJUST'
, 'RECP_REVERSE')
AND NVL(LICR.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND NVL(LITRX.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N'
AND LITRX.MFAR_ADDITIONAL_ENTRY = 'N' UNION /******************** * CM APP DOC * ********************/ SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, DIST_LAYER.GAIN_LOSS GAIN_LOSS_AMT
, DECODE(SIGN(DIST_LAYER.GAIN_LOSS)
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
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') ) DIST_LAYER
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 = 'F'
AND DIST_LAYER.LINE_ID= LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND NVL(LINE_EXT.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, DIST_LAYER.GAIN_LOSS GAIN_LOSS_AMT
, DECODE(SIGN(DIST_LAYER.GAIN_LOSS)
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
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'
, 'ACTIVITY' ) ) DIST_LAYER
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 DIST_LAYER.LINE_ID= LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND NVL(LINE_EXT.ADDITIONAL_CHAR1
, 'XX') <> 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' /****************************** * MISC CASH DISTRIBUTIONS * ******************************/ UNION SELECT /*+INDEX (LI AR_XLA_LINES_EXTRACT_N1)*/ LI.EVENT_ID EVENT_ID
, LI.BASE_CURRENCY_CODE BASE_CURRENCY
, LI.LINE_ID LINE_ID
, LI.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_ACCTD_AMT
, LI.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LI.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LI.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LI.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NULL GAIN_LOSS_AMT
, NULL GAIN_LOSS_SIGN
, LI.LINE_NUMBER LINE_NUMBER
, LI.LANGUAGE LANGUAGE
, LI.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LI
WHERE LI.POSTING_ENTITY = 'MCD'
AND LI.LEVEL_FLAG = 'L'
AND LI.MFAR_ADDITIONAL_ENTRY = 'N' UNION /******************************* * CASH BASIS UPGRADE RECP APP * *******************************/ SELECT /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1) INDEX(LITRX AR_XLA_LINES_EXTRACT_N1)*/ LICR.EVENT_ID EVENT_ID
, LICR.BASE_CURRENCY_CODE BASE_CURRENCY
, LICR.LINE_ID LINE_ID
, LICR.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LICR.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LICR.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, D.FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, LITRX.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LITRX.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LITRX.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LITRX.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NVL(D.FROM_ACCTD_AMOUNT
, 0) - NVL(D.ACCTD_AMOUNT
, 0) GAIN_LOSS_AMT
, DECODE(SIGN(NVL(D.FROM_ACCTD_AMOUNT
, 0)- NVL(D.ACCTD_AMOUNT
, 0))
, 1
, '+'
, '-') GAIN_LOSS_SIGN
, LICR.LINE_NUMBER LINE_NUMBER
, LICR.LANGUAGE LANGUAGE
, LICR.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LICR
, AR_XLA_LINES_EXTRACT LITRX
, AR_CASH_BASIS_DISTS_ALL D
WHERE LICR.POSTING_ENTITY = 'APP'
AND LICR.LEVEL_FLAG = 'L'
AND LICR.CASH_RECEIPT_ID IS NOT NULL
AND LICR.CUSTOMER_TRX_ID IS NULL
AND LICR.FROM_TO_FLAG = 'F'
AND LITRX.POSTING_ENTITY = 'APP'
AND LITRX.LEVEL_FLAG = 'L'
AND LITRX.CASH_RECEIPT_ID IS NULL
AND LITRX.CUSTOMER_TRX_ID IS NOT NULL
AND LITRX.FROM_TO_FLAG = 'T'
AND LICR.HEADER_TABLE_ID = LITRX.HEADER_TABLE_ID
AND LICR.EVENT_ID = LITRX.EVENT_ID
AND D.RECEIVABLE_APPLICATION_ID = LITRX.HEADER_TABLE_ID
AND D.CASH_BASIS_DISTRIBUTION_ID = LICR.LINE_ID
AND D.CASH_BASIS_DISTRIBUTION_ID = LITRX.LINE_ID
AND LICR.EVENT_TYPE_CODE IN ('RECP_CREATE'
, 'RECP_UPDATE'
, 'RECP_RATE_ADJUST'
, 'RECP_REVERSE')
AND LICR.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LITRX.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N'
AND LITRX.MFAR_ADDITIONAL_ENTRY = 'N' UNION /***************************** * CASH BASIS UPGRADE CM APP * *****************************/ SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0) GAIN_LOSS_AMT
, DECODE(SIGN( NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0))
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, AR_CASH_BASIS_DISTS_ALL DIST_LAYER
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 = 'F'
AND DIST_LAYER.CASH_BASIS_DISTRIBUTION_ID = LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND LINE_EXT.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX (LINE_EXT AR_XLA_LINES_EXTRACT_N1)*/ LINE_EXT.EVENT_ID EVENT_ID
, LINE_EXT.BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_EXT.LINE_ID LINE_ID
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, DIST_LAYER.FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, LINE_EXT.EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, LINE_EXT.EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, LINE_EXT.EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, LINE_EXT.ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0) GAIN_LOSS_AMT
, DECODE(SIGN(NVL(DIST_LAYER.FROM_ACCTD_AMOUNT
, 0) - NVL(DIST_LAYER.ACCTD_AMOUNT
, 0))
, -1
, '-'
, '+') GAIN_LOSS_SIGN
, LINE_EXT.LINE_NUMBER LINE_NUMBER
, LINE_EXT.LANGUAGE LANGUAGE
, LINE_EXT.LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT LINE_EXT
, AR_CASH_BASIS_DISTS_ALL DIST_LAYER
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 DIST_LAYER.CASH_BASIS_DISTRIBUTION_ID = LINE_EXT.LINE_ID
AND LINE_EXT.EVENT_TYPE_CODE IN ('CM_CREATE'
, 'CM_UPDATE')
AND LINE_EXT.ADDITIONAL_CHAR1 = 'R12_11ICASH_POST'
AND LINE_EXT.MFAR_ADDITIONAL_ENTRY = 'N' UNION /*MFAR ADDITIONAL ENTRIES*/ SELECT EVENT_ID EVENT_ID
, BASE_CURRENCY_CODE BASE_CURRENCY
, LINE_ID LINE_ID
, FROM_EXCHANGE_RATE_TYPE DIST_CUR_CONVERSION_TYPE
, FROM_EXCHANGE_RATE DIST_CUR_CONVERSION_RATE
, FROM_EXCHANGE_DATE DIST_CUR_CONVERSION_DATE
, FROM_ACCTD_AMOUNT DIST_ACCTD_AMT
, EXCHANGE_RATE_TYPE DIST_TO_CUR_CONVERSION_TYPE
, EXCHANGE_RATE DIST_TO_CUR_CONVERSION_RATE
, EXCHANGE_DATE DIST_TO_CUR_CONVERSION_DATE
, ACCTD_AMOUNT DIST_TO_ACCTD_AMT
, FROM_ACCTD_AMOUNT - ACCTD_AMOUNT GAIN_LOSS_AMT
, '+' GAIN_LOSS_SIGN
, LINE_NUMBER LINE_NUMBER
, 'US' LANGUAGE
, LEDGER_ID LEDGER_ID
, NULL RECP_OVERRIDE_ACCTD_AMT
, NULL DIST_ACTIVITY_BUCKET
FROM AR_XLA_LINES_EXTRACT
WHERE MFAR_ADDITIONAL_ENTRY = 'Y'