AR_DISTRIBUTIONS_BASE_V

Details

• Schema: FUSION

• Object owner: AR

• Object type: VIEW

Columns

Name
EVENT_ID BASE_CURRENCY LINE_ID DIST_CUR_CONVERSION_TYPE DIST_CUR_CONVERSION_RATE DIST_CUR_CONVERSION_DATE DIST_ACCTD_AMT DIST_TO_CUR_CONVERSION_TYPE DIST_TO_CUR_CONVERSION_RATE DIST_TO_CUR_CONVERSION_DATE DIST_TO_ACCTD_AMT GAIN_LOSS_AMT GAIN_LOSS_SIGN LINE_NUMBER LANGUAGE LEDGER_ID

Query

SQL_Statement

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 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 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 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 /*+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 , 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 FROM ar_xla_lines_extract licr , ar_xla_lines_extract litrx , (SELECT Decode(d.from_acctd_amount_cr,'',Decode(d.from_acctd_amount_dr,'',Decode(d.source_table,'RA',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)), 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)), Nvl(d.from_acctd_amount_cr,0) - Nvl(d.from_acctd_amount_dr,0)) from_amount, Decode(d.from_acctd_amount_cr,'',Decode(d.from_acctd_amount_dr,'',Decode(d.source_table,'RA',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))), (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, ar_receivable_applications_all ra WHERE d.source_id = ra.receivable_application_id (+) AND source_type IN ('REC','OTHER ACC','ACC','BANK_CHARGES','UNPAIDREC', 'ACTIVITY','TAX','FACTOR','REMITTANCE','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.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 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' AND not exists(select 'Y' from AR_DETAILED_DISTRIBUTIONS_ALL where source_dist_id = dist.line_id) UNION ALL SELECT /*+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 , 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 FROM ar_xla_lines_extract licr , ar_xla_lines_extract litrx , (SELECT Decode(mfar_dist.from_acctd_amount_cr,'',Decode(mfar_dist.from_acctd_amount_dr,'',Decode(d.source_table,'RA',Decode(Decode(d.source_type,'REC',Nvl(mfar_dist.acctd_amount_applied_to,0) + Nvl(mfar_dist.acctd_earned_discount_taken,0) + Nvl(mfar_dist.acctd_unearned_discount_taken,0), 'OTHER ACC',Nvl(mfar_dist.acctd_amount_applied_from,0), 'ACC',Nvl(mfar_dist.acctd_amount_applied_from,0), Nvl(mfar_dist.from_acctd_amount_cr,0) - Nvl(mfar_dist.from_acctd_amount_dr,0)),Nvl(mfar_dist.acctd_amount_dr,0) *- 1 + Nvl(mfar_dist.acctd_amount_cr,0),Nvl(mfar_dist.acctd_amount_applied_from,0), Nvl(mfar_dist.from_acctd_amount_cr,0) - Nvl(mfar_dist.from_acctd_amount_dr,0)), Nvl(mfar_dist.from_acctd_amount_cr,0) - Nvl(mfar_dist.from_acctd_amount_dr,0)), Nvl(mfar_dist.from_acctd_amount_cr,0) - Nvl(mfar_dist.from_acctd_amount_dr,0)), Nvl(mfar_dist.from_acctd_amount_cr,0) - Nvl(mfar_dist.from_acctd_amount_dr,0)) from_amount, Decode(mfar_dist.from_acctd_amount_cr,'',Decode(mfar_dist.from_acctd_amount_dr,'',Decode(mfar_dist.source_table,'RA',Decode(mfar_dist.source_type,'REC',Nvl(mfar_dist.acctd_amount_applied_from,0) - Nvl(mfar_dist.acctd_amount_applied_to,0), 0), (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)),(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))), (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_rec, mfar_dist_cash.DETAILED_DIST_ID DETAILED_DIST_ID_cash FROM ar_distributions_all d, ar_receivable_applications_all ra, AR_DETAILED_DISTRIBUTIONS_ALL mfar_dist, AR_DETAILED_DISTRIBUTIONS_ALL mfar_dist_cash WHERE d.source_id = ra.receivable_application_id (+) AND mfar_dist.source_dist_id = d.line_id AND d.source_type IN ('REC','OTHER ACC','ACC','BANK_CHARGES','UNPAIDREC', 'ACTIVITY','TAX','FACTOR','REMITTANCE','DEFERRED_TAX','UNEDISC', 'EDISC','CURR_ROUND','SHORT_TERM_DEBT','EXCH_GAIN', 'EXCH_LOSS','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX') AND mfar_dist.source_type=d.source_type AND mfar_dist_cash.PRIOR_DETAILED_DIST_ID = mfar_dist.DETAILED_DIST_ID AND mfar_dist_cash.source_type in ('CASH','REMITTANCE','CONFIRMATION', 'EDISC' , 'UNEDISC' , '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.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 in ('T','Y') 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.DETAILED_DIST_ID_cash = licr.source_id AND licr.source_table = 'MFAR' AND dist.DETAILED_DIST_ID_rec = litrx.source_id AND litrx.source_table = 'MFAR' 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 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 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 FROM ar_xla_lines_extract li WHERE li.posting_entity = 'MCD' AND li.level_flag = 'L' AND li.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 FROM ar_xla_lines_extract WHERE mfar_additional_entry = 'Y' UNION SELECT li.event_id event_id , li.base_currency_code base_currency , zd.tax_dist_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 , zd.tax_dist_amt_funcl_curr 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 , zd.tax_dist_amt_funcl_curr dist_to_acctd_amt , NULL gain_loss_amt , NULL gain_loss_sign , zd.tax_dist_id line_number , li.language language , li.ledger_id ledger_id FROM ar_xla_lines_extract li , ZX_TRX_TAX_DISTRIBUTIONS zd WHERE li.posting_entity = 'CRH' AND li.level_flag = 'L' AND li.mfar_additional_entry = 'N' AND zd.application_id = 235 AND zd.entity_code = 'ZX_TRANSACTION' AND zd.event_class_code = 'ADVANCE_RECEIPT' AND zd.event_type_code = 'ADVANCE_RECEIPT_UPDATE' AND li.event_id = zd.ACCOUNTING_EVENT_ID AND li.line_id = zd.trx_id UNION SELECT licr.event_id event_id , licr.base_currency_code base_currency , zd.tax_dist_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 , zd.tax_dist_amt_funcl_curr 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 , zd.tax_dist_amt_funcl_curr dist_to_acctd_amt , to_number(NULL) gain_loss_amt , '' gain_loss_sign, zd.tax_dist_id line_number , licr.language language , licr.ledger_id ledger_id FROM ar_xla_lines_extract licr , ar_xla_lines_extract litrx , ar_distributions_all dist, ZX_TRX_TAX_DISTRIBUTIONS zd WHERE licr.posting_entity = 'APP' AND licr.level_flag = 'L' AND zd.application_id = 235 AND zd.entity_code = 'ZX_TRANSACTION' AND zd.event_class_code = 'ADVANCE_RECEIPT' AND zd.event_type_code = 'ADVANCE_RECEIPT_UPDATE' AND licr.event_id = zd.ACCOUNTING_EVENT_ID AND licr.line_id = zd.trx_id 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 dist.source_id = litrx.header_table_id AND dist.line_id = licr.line_id AND dist.line_id = litrx.line_id AND dist.source_type IN ('REC','ACTIVITY') AND licr.event_type_code IN ('RECP_UPDATE') 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'