AR_DISTRIBUTIONS_L_V

Details

• Schema: FUSION

• Object owner: AR

• Object type: VIEW

Columns

Name

EVENT_ID DIST_LINE_ID DISTRIBUTION_TYPE DIST_CODE_COMBINATION_ID DIST_SOURCE_ID DIST_SOURCE_ID_SECONDARY DIST_SOURCE_TABLE DIST_SOURCE_TABLE_SECONDARY DIST_SOURCE_TYPE DIST_SOURCE_TYPE_SECONDARY DIST_TAX_LINK_ID DIST_ENT_AMT_FROM DIST_CURRENCY_CODE_FROM DIST_ENT_AMT DIST_CURRENCY_CODE LINE_NUMBER LANGUAGE LEDGER_ID DIST_PAIRED_CCID DIST_PAIRED_SOURCE_TYPE DIST_PARTY_TYPE DIST_PARTY_ID DIST_PARTY_SITE_ID DIST_REF_DIST_CCID DIST_MFAR_ADDITIONAL_ENTRY DIST_GAIN_CCID DIST_LOSS_CCID BR_LINE_TRX_NUMBER BR_LINE_TRX_TYPE_NAME BR_LINE_TRX_DOCUMENT_NUMBER CM_APP_TRX_NUMBER CM_APP_TRX_TYPE_NAME CM_APP_TRX_DOC_SEQ_VALUE OVERRIDE_ACCTD_AMT_RECALC SUBLEDGER_BC_COMPLETE_FLAG DATA_SET_ID OVERRIDE_BC_CMPL_STATUS_FLAG REF_TRX_DIST_ACCOUNTED_CCID

Query

SQL_Statement

SELECT /*+INDEX(l ar_xla_lines_extract_n1) leading(l)*/ l.event_id event_id , dat.line_id dist_line_id , 'AR_DISTRIBUTIONS_ALL' distribution_type , dat.code_combination_id dist_code_combination_id , dat.source_id dist_source_id , dat.source_id_secondary dist_source_id_secondary , dat.source_table dist_source_table , dat.source_table_secondary dist_source_table_secondary , dat.source_type dist_source_type , dat.source_type_secondary dist_source_type_secondary , CASE WHEN(dat.source_type IN('TAX', 'ADJ_NON_REC_TAX', 'FINCHRG_NON_REC_TAX')) THEN (SELECT max(tax_line_id) FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.application_id= 222 AND zx.entity_code = 'ADJUSTMENTS') ELSE NULL END dist_tax_link_id , NVL(dat.amount_cr,0) - NVL(dat.amount_dr,0) dist_ent_amt_from , dat.currency_code dist_currency_code_from , NVL(dat.amount_cr,0) - NVL(dat.amount_dr,0) dist_ent_amt , dat.currency_code dist_currency_code , l.line_number line_number , l.language language , l.ledger_id ledger_id , l.paired_ccid dist_paired_ccid , 'REC' dist_paired_source_type , DECODE(dat.third_party_id,NULL,NULL,'C') dist_party_type , dat.third_party_id dist_party_id , TO_NUMBER(DECODE(dat.third_party_id,NULL,NULL,dat.third_party_sub_id)) dist_party_site_id , dat.ref_dist_ccid dist_ref_dist_ccid , 'N' DIST_MFAR_ADDITIONAL_ENTRY , TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, l.SUBLEDGER_BC_COMPLETE_FLAG, l.DATA_SET_ID, DECODE(l.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_distributions_all dat , ar_xla_lines_extract l WHERE l.posting_entity = 'ADJ' AND l.source_id = dat.source_id AND l.source_table = dat.source_table AND l.line_id = dat.line_id AND l.level_flag = 'L' AND l.mfar_additional_entry = 'N' UNION SELECT /*+INDEX(l ar_xla_lines_extract_n1) leading(l)*/ l.event_id event_id , daf.line_id dist_line_id , 'AR_DISTRIBUTIONS_ALL' distribution_type , daf.code_combination_id dist_code_combination_id , daf.source_id dist_source_id , daf.source_id_secondary dist_source_id_secondary , daf.source_table dist_source_table , daf.source_table_secondary dist_source_table_secondary , daf.source_type dist_source_type , daf.source_type_secondary dist_source_type_secondary , daf.tax_link_id dist_tax_link_id , NVL(daf.amount_cr,0) - NVL(daf.amount_dr,0) dist_ent_amt_from , daf.currency_code dist_currency_code_from , NVL(daf.amount_cr,0) - NVL(daf.amount_dr,0) dist_ent_amt , daf.currency_code dist_currency_code , l.line_number line_number , l.language language , l.ledger_id ledger_id , NULL dist_paired_ccid , NVL(l.crh_prv_status,'UNAPP') dist_paired_source_type , 'C' dist_party_type , daf.third_party_id dist_party_id , TO_NUMBER(DECODE(daf.third_party_id,NULL,NULL,daf.third_party_sub_id)) dist_party_site_id , daf.ref_dist_ccid dist_ref_dist_ccid , 'N' , TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, l.SUBLEDGER_BC_COMPLETE_FLAG, l.DATA_SET_ID, DECODE(l.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_distributions_all daf , ar_xla_lines_extract l WHERE l.posting_entity = 'CRH' AND l.source_id = daf.source_id AND l.source_table = daf.source_table AND l.line_id = daf.line_id AND l.level_flag = 'L' AND l.mfar_additional_entry = 'N' UNION SELECT /*+INDEX(l ar_xla_lines_extract_n1) leading(l)*/ l.event_id event_id , daf.line_id dist_line_id , 'AR_DISTRIBUTIONS_ALL' distribution_type , daf.code_combination_id dist_code_combination_id , daf.source_id dist_source_id , daf.source_id_secondary dist_source_id_secondary , daf.source_table dist_source_table , daf.source_table_secondary dist_source_table_secondary , daf.source_type dist_source_type , daf.source_type_secondary dist_source_type_secondary , daf.tax_link_id dist_tax_link_id , NVL(daf.amount_cr,0) - NVL(daf.amount_dr,0) dist_ent_amt_from , daf.currency_code dist_currency_code_from , NVL(daf.amount_cr,0) - NVL(daf.amount_dr,0) dist_ent_amt , daf.currency_code dist_currency_code , l.line_number line_number , l.language language , l.ledger_id ledger_id , l.paired_ccid dist_paired_ccid , 'UNAPP' dist_paired_source_type , DECODE(daf.third_party_id,NULL,NULL,'C') dist_party_type , daf.third_party_id dist_party_id , TO_NUMBER(DECODE(daf.third_party_id,NULL,NULL,daf.third_party_sub_id)) dist_party_site_id , daf.ref_dist_ccid dist_ref_dist_ccid , 'N' , TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, r.trx_number br_line_trx_number , type.name br_line_trx_type_name , r.doc_sequence_value br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, l.SUBLEDGER_BC_COMPLETE_FLAG, l.DATA_SET_ID, DECODE(l.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_distributions_all daf , ar_xla_lines_extract l , ra_customer_trx_lines_all line, ra_customer_trx_all r, ra_cust_trx_types_all type WHERE l.posting_entity = 'TH' AND l.source_id = daf.source_id AND l.source_table = daf.source_table AND l.line_id = daf.line_id AND l.level_flag = 'L' AND l.mfar_additional_entry = 'N' AND daf.source_id_secondary=line.customer_trx_line_id(+) AND line.br_ref_customer_trx_id=r.customer_trx_id(+) AND r.cust_trx_type_seq_id=type.cust_trx_type_seq_id(+) UNION SELECT /*+INDEX(licr ar_xla_lines_extract_n1) leading(licr)*/ licr.event_id event_id , dat.line_id dist_line_id , 'AR_DISTRIBUTIONS_ALL' distribution_type , dat.code_combination_id dist_code_combination_id , dat.source_id dist_source_id , dat.source_id_secondary dist_source_id_secondary , dat.source_table dist_source_table , dat.source_table_secondary dist_source_table_secondary , dat.source_type dist_source_type , dat.source_type_secondary dist_source_type_secondary , CASE WHEN(dat.source_type IN('TAX')) THEN (decode(dat.source_type_secondary, 'EDISC', (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_EARNED'), 'UNEDISC', (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_UNEARNED'), 'PAYMENT', (SELECT tax_line_id FROM ra_customer_trx_lines_all cust_lines WHERE cust_lines.link_to_cust_trx_line_id = dat.tax_link_id AND cust_lines.line_type = 'TAX' AND cust_lines.vat_tax_id = dat.tax_code_id), NULL) ) WHEN(dat.source_type IN('EDISC_NON_REC_TAX')) THEN (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_EARNED') WHEN(dat.source_type IN('UNEDISC_NON_REC_TAX')) THEN (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_UNEARNED') ELSE NULL END dist_tax_link_id , Decode(dat.from_amount_cr,'',Decode(dat.from_amount_dr,'',Decode(dat.source_type,'REC',Decode(ra.amount_applied + Nvl(ra.earned_discount_taken,0) + Nvl(ra.unearned_discount_taken,0),Nvl(dat.amount_dr,0) *- 1 + Nvl(dat.amount_cr,0),Nvl(ra.amount_applied_from,ra.amount_applied), Nvl(dat.from_amount_cr,0) - Nvl(dat.from_amount_dr,0)), Nvl(dat.from_amount_cr,0) - Nvl(dat.from_amount_dr,0)), Nvl(dat.from_amount_cr,0) - Nvl(dat.from_amount_dr,0)), Nvl(dat.from_amount_cr,0) - Nvl(dat.from_amount_dr,0)) dist_ent_amt_from, cr.currency_code dist_currency_code_from , NVL(dat.amount_cr,0)- NVL(dat.amount_dr,0) dist_ent_amt , dat.currency_code dist_currency_code , licr.line_number line_number , licr.language language , licr.ledger_id ledger_id , NULL dist_paired_ccid , 'UNAPP' dist_paired_source_type , 'C' dist_party_type , dat.third_party_id dist_party_id , TO_NUMBER(DECODE(dat.third_party_id,NULL,NULL,dat.third_party_sub_id)) dist_party_site_id , dat.ref_dist_ccid dist_ref_dist_ccid , 'N' , DECODE(SIGN(NVL(ra.acctd_amount_applied_from,0)-NVL(ra.acctd_amount_applied_to,0)), 1, arp_xla_extract_main_pkg.get_glr_ccid(ra.receivable_application_id,'EXCH_GAIN'), to_number(NULL)) dist_gain_ccid, DECODE(SIGN(NVL(ra.acctd_amount_applied_from,0)-NVL(ra.acctd_amount_applied_to,0)),-1, arp_xla_extract_main_pkg.get_glr_ccid(ra.receivable_application_id,'EXCH_LOSS'), to_number(NULL)) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, licr.SUBLEDGER_BC_COMPLETE_FLAG, licr.DATA_SET_ID, DECODE(licr.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_xla_lines_extract licr , ar_distributions_all dat , ar_receivable_applications_all ra, ar_cash_receipts_all cr WHERE licr.posting_entity = 'APP' AND licr.level_flag = 'L' AND licr.source_id = dat.source_id AND licr.source_table = dat.source_table AND licr.line_id = dat.line_id AND dat.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS') AND ra.receivable_application_id = licr.header_table_id AND ra.status = 'APP' AND ra.cash_receipt_id = cr.cash_receipt_id AND NVL(ra.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST') AND licr.from_to_flag IN ('T','Y') AND licr.mfar_additional_entry = 'N' AND not exists(select 'Y' from AR_DETAILED_DISTRIBUTIONS_ALL where source_dist_id = dat.line_id) UNION SELECT /*+INDEX(licr ar_xla_lines_extract_n1) leading(licr)*/ licr.event_id event_id , dat.line_id dist_line_id , 'AR_DETAILED_DISTRIBUTIONS_ALL' distribution_type , dat.code_combination_id dist_code_combination_id , dat.source_id dist_source_id , dat.source_id_secondary dist_source_id_secondary , dat.source_table dist_source_table , dat.source_table_secondary dist_source_table_secondary , mfar_dist.source_type dist_source_type , dat.source_type_secondary dist_source_type_secondary , CASE WHEN(dat.source_type IN('TAX')) THEN (decode(dat.source_type_secondary, 'EDISC', (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_EARNED'), 'UNEDISC', (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_UNEARNED'), 'PAYMENT', (SELECT tax_line_id FROM ra_customer_trx_lines_all cust_lines WHERE cust_lines.link_to_cust_trx_line_id = dat.tax_link_id AND cust_lines.line_type = 'TAX' AND cust_lines.vat_tax_id = dat.tax_code_id), NULL) ) WHEN(dat.source_type IN('EDISC_NON_REC_TAX')) THEN (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_EARNED') WHEN(dat.source_type IN('UNEDISC_NON_REC_TAX')) THEN (SELECT tax_line_id FROM zx_lines zx WHERE zx.trx_id = dat.source_id AND dat.tax_code_id = zx.tax_rate_id AND dat.tax_link_id = zx.trx_line_id AND zx.trx_level_type = 'LINE_UNEARNED') ELSE NULL END dist_tax_link_id , Decode(mfar_dist.from_amount_cr,'',Decode(mfar_dist.from_amount_dr,'',Decode(dat.source_type,'REC',Decode(mfar_dist.amount_applied + Nvl(mfar_dist.earned_discount_taken,0) + Nvl(mfar_dist.unearned_discount_taken,0),Nvl(mfar_dist.amount_dr,0) *- 1 + Nvl(mfar_dist.amount_cr,0),Nvl(mfar_dist.amount_applied_from,mfar_dist.amount_applied), Nvl(mfar_dist.from_amount_cr,0) - Nvl(mfar_dist.from_amount_dr,0)), Nvl(mfar_dist.from_amount_cr,0) - Nvl(mfar_dist.from_amount_dr,0)), Nvl(mfar_dist.from_amount_cr,0) - Nvl(mfar_dist.from_amount_dr,0)), Nvl(mfar_dist.from_amount_cr,0) - Nvl(mfar_dist.from_amount_dr,0)) dist_ent_amt_from, cr.currency_code dist_currency_code_from , NVL(mfar_dist.amount_cr,0)- NVL(mfar_dist.amount_dr,0) dist_ent_amt , dat.currency_code dist_currency_code , licr.line_number line_number , licr.language language , licr.ledger_id ledger_id , NULL dist_paired_ccid , 'UNAPP' dist_paired_source_type , 'C' dist_party_type , dat.third_party_id dist_party_id , TO_NUMBER(DECODE(dat.third_party_id,NULL,NULL,dat.third_party_sub_id)) dist_party_site_id , dat.ref_dist_ccid dist_ref_dist_ccid , 'N' , DECODE(SIGN(NVL(ra.acctd_amount_applied_from,0)-NVL(ra.acctd_amount_applied_to,0)), 1, arp_xla_extract_main_pkg.get_glr_ccid(ra.receivable_application_id,'EXCH_GAIN'), to_number(NULL)) dist_gain_ccid, DECODE(SIGN(NVL(ra.acctd_amount_applied_from,0)-NVL(ra.acctd_amount_applied_to,0)),-1, arp_xla_extract_main_pkg.get_glr_ccid(ra.receivable_application_id,'EXCH_LOSS'), to_number(NULL)) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, licr.SUBLEDGER_BC_COMPLETE_FLAG, licr.DATA_SET_ID, DECODE(licr.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), licr.REF_TRX_DIST_ACCOUNTED_CCID FROM ar_xla_lines_extract licr , ar_distributions_all dat , ar_receivable_applications_all ra, ar_cash_receipts_all cr, AR_DETAILED_DISTRIBUTIONS_ALL mfar_dist WHERE licr.posting_entity = 'APP' AND licr.level_flag = 'L' AND licr.source_id = mfar_dist.DETAILED_DIST_ID AND licr.source_table = 'MFAR' AND licr.line_id = dat.line_id AND dat.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS') AND ra.receivable_application_id = licr.header_table_id AND ra.status = 'APP' AND ra.cash_receipt_id = cr.cash_receipt_id AND NVL(ra.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST') AND licr.from_to_flag IN ('T','Y') AND licr.mfar_additional_entry = 'N' UNION SELECT /*+INDEX(licr ar_xla_lines_extract_n1) leading(licr)*/ licr.event_id event_id , dat.line_id dist_line_id , 'AR_DISTRIBUTIONS_ALL' distribution_type , dat.code_combination_id dist_code_combination_id , dat.source_id dist_source_id , dat.source_id_secondary dist_source_id_secondary , dat.source_table dist_source_table , dat.source_table_secondary dist_source_table_secondary , dat.source_type dist_source_type , dat.source_type_secondary dist_source_type_secondary , dat.tax_link_id dist_tax_link_id , NVL(dat.from_amount_cr,0) - NVL(dat.from_amount_dr,0) dist_ent_amt_from , cr.currency_code dist_currency_code_from , NVL(dat.amount_cr,0)- NVL(dat.amount_dr,0) dist_ent_amt , apptrx.invoice_currency_code dist_currency_code , licr.line_number line_number , licr.language language , licr.ledger_id ledger_id , NULL dist_paired_ccid , 'UNAPP' dist_paired_source_type , 'C' dist_party_type , dat.third_party_id dist_party_id , TO_NUMBER(DECODE(dat.third_party_id,NULL,NULL,dat.third_party_sub_id)) dist_party_site_id , dat.ref_dist_ccid dist_ref_dist_ccid , 'N' , TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, licr.SUBLEDGER_BC_COMPLETE_FLAG, licr.DATA_SET_ID, DECODE(licr.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_xla_lines_extract licr , ar_distributions_all dat , ar_receivable_applications_all ra, ar_cash_receipts_all cr , ra_customer_trx_all apptrx WHERE licr.posting_entity = 'APP' AND licr.level_flag = 'L' AND licr.cash_receipt_id IS NULL AND licr.customer_trx_id IS NOT NULL AND licr.source_id = dat.source_id AND licr.source_table = dat.source_table AND licr.line_id = dat.line_id AND dat.source_type IN ('EXCH_GAIN','EXCH_LOSS') AND ra.receivable_application_id = licr.header_table_id AND ra.status = 'APP' AND NVL(ra.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST') AND licr.event_type_code IN ('RECP_CREATE','RECP_UPDATE','RECP_RATE_ADJUST','RECP_REVERSE') AND ra.cash_receipt_id = cr.cash_receipt_id AND ra.applied_customer_trx_id = apptrx.customer_trx_id AND licr.from_to_flag = 'T' AND licr.mfar_additional_entry = 'N' UNION SELECT /*+INDEX(licm ar_xla_lines_extract_n1) leading(licm)*/ licm.event_id event_id , dat.line_id dist_line_id , 'AR_DISTRIBUTIONS_ALL' distribution_type , dat.code_combination_id dist_code_combination_id , dat.source_id dist_source_id , dat.source_id_secondary dist_source_id_secondary , dat.source_table dist_source_table , dat.source_table_secondary dist_source_table_secondary , dat.source_type dist_source_type , dat.source_type_secondary dist_source_type_secondary , dat.tax_link_id dist_tax_link_id , NVL(dat.from_amount_cr,0)-NVL(dat.from_amount_dr,0) dist_ent_amt_from , cm.invoice_currency_code dist_currency_code_from , NVL(dat.amount_cr,0) - NVL(dat.amount_dr,0) dist_ent_amt , dat.currency_code dist_currency_code , licm.line_number line_number , licm.language language , licm.ledger_id ledger_id , NULL dist_paired_ccid , 'UNAPP' dist_paired_source_type , DECODE(dat.third_party_id,NULL,NULL,'C') dist_party_type , dat.third_party_id dist_party_id , TO_NUMBER(DECODE(dat.third_party_id,NULL,NULL,dat.third_party_sub_id)) dist_party_site_id , dat.ref_dist_ccid dist_ref_dist_ccid , 'N', TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , invcm.trx_number cm_app_trx_number , cttaf.name cm_app_trx_type_name , invcm.doc_sequence_value cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, licm.SUBLEDGER_BC_COMPLETE_FLAG, licm.DATA_SET_ID, DECODE(licm.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_xla_lines_extract licm , ar_distributions_all dat , ar_receivable_applications_all ra, ra_customer_trx_all cm, ra_customer_trx_all invcm, ra_cust_trx_types_all cttaf WHERE licm.posting_entity = 'APP' AND licm.level_flag = 'L' AND licm.customer_trx_id IS NOT NULL AND licm.source_id = dat.source_id AND licm.source_table = dat.source_table AND licm.line_id = dat.line_id AND dat.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS') AND ra.receivable_application_id = licm.header_table_id AND ra.status IN ('APP','ACTIVITY') AND NVL(ra.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST') AND ra.customer_trx_id IS NOT NULL AND(ra.applied_customer_trx_id = licm.customer_trx_id OR ra.customer_trx_id = licm.customer_trx_id) AND ra.customer_trx_id = cm.customer_trx_id AND licm.event_type_code IN('CM_CREATE', 'CM_UPDATE') AND licm.mfar_additional_entry = 'N' AND ra.applied_customer_trx_id = invcm.customer_trx_id (+) AND invcm.cust_trx_type_seq_id = cttaf.cust_trx_type_seq_id (+) AND not exists(select 'Y' from AR_DETAILED_DISTRIBUTIONS_ALL where source_dist_id = dat.line_id) UNION ALL SELECT /*+INDEX(licm ar_xla_lines_extract_n1) leading(licm)*/ licm.event_id event_id , dat.line_id dist_line_id , 'AR_DETAILED_DISTRIBUTIONS_ALL' distribution_type , dat.code_combination_id dist_code_combination_id , dat.source_id dist_source_id , dat.source_id_secondary dist_source_id_secondary , dat.source_table dist_source_table , dat.source_table_secondary dist_source_table_secondary , dat.source_type dist_source_type , dat.source_type_secondary dist_source_type_secondary , dat.tax_link_id dist_tax_link_id , NVL(mfar_dist.from_amount_cr,0)-NVL(mfar_dist.from_amount_dr,0) dist_ent_amt_from , cm.invoice_currency_code dist_currency_code_from , NVL(mfar_dist.amount_cr,0) - NVL(mfar_dist.amount_dr,0) dist_ent_amt , dat.currency_code dist_currency_code , licm.line_number line_number , licm.language language , licm.ledger_id ledger_id , NULL dist_paired_ccid , 'UNAPP' dist_paired_source_type , DECODE(dat.third_party_id,NULL,NULL,'C') dist_party_type , dat.third_party_id dist_party_id , TO_NUMBER(DECODE(dat.third_party_id,NULL,NULL,dat.third_party_sub_id)) dist_party_site_id , trx_dist.CODE_COMBINATION_ID dist_ref_dist_ccid , 'N', TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , invcm.trx_number cm_app_trx_number , cttaf.name cm_app_trx_type_name , invcm.doc_sequence_value cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, licm.SUBLEDGER_BC_COMPLETE_FLAG, licm.DATA_SET_ID, DECODE(licm.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), licm.REF_TRX_DIST_ACCOUNTED_CCID FROM ar_xla_lines_extract licm , ar_distributions_all dat , ar_receivable_applications_all ra, ra_customer_trx_all cm, ra_customer_trx_all invcm, ra_cust_trx_types_all cttaf, AR_DETAILED_DISTRIBUTIONS_ALL mfar_dist, RA_CUST_TRX_LINE_GL_DIST_ALL trx_dist WHERE licm.posting_entity = 'APP' AND licm.level_flag = 'L' AND licm.customer_trx_id IS NOT NULL AND licm.source_id = mfar_dist.detailed_dist_id AND licm.source_table = 'MFAR' AND licm.line_id = dat.line_id AND dat.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS') AND ra.receivable_application_id = licm.header_table_id AND ra.status IN ('APP','ACTIVITY') AND NVL(ra.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST') AND ra.customer_trx_id IS NOT NULL AND(ra.applied_customer_trx_id = licm.customer_trx_id OR ra.customer_trx_id = licm.customer_trx_id) AND ra.customer_trx_id = cm.customer_trx_id AND licm.event_type_code IN('CM_CREATE', 'CM_UPDATE') AND licm.mfar_additional_entry = 'N' AND ra.applied_customer_trx_id = invcm.customer_trx_id (+) AND invcm.cust_trx_type_seq_id = cttaf.cust_trx_type_seq_id (+) AND trx_dist.cust_trx_line_gl_dist_id = mfar_dist.ref_cust_trx_line_gl_dist_id UNION SELECT /*+INDEX(l ar_xla_lines_extract_n1) leading(l)*/ l.event_id event_id, dat.line_id dist_line_id, 'AR_DISTRIBUTIONS_ALL' distribution_type, dat.code_combination_id dist_code_combination_id, dat.source_id dist_source_id, dat.source_id_secondary dist_source_id_secondary, dat.source_table dist_source_table, dat.source_table_secondary dist_source_table_secondary, dat.source_type dist_source_type, dat.source_type_secondary dist_source_type_secondary, decode (dat.source_type,'TAX',dat.tax_link_id,null) dist_tax_link_id, nvl(dat.amount_cr, 0) -nvl(dat.amount_dr, 0) dist_ent_amt_from, dat.currency_code dist_currency_code_from, nvl(dat.amount_cr, 0) -nvl(dat.amount_dr, 0) dist_ent_amt, dat.currency_code dist_currency_code, l.line_number line_number, l.LANGUAGE LANGUAGE, l.ledger_id ledger_id, NULL dist_paired_ccid, NULL dist_paired_source_type, decode(dat.third_party_id, NULL, NULL, 'C') dist_party_type, dat.third_party_id dist_party_id, to_number(decode(dat.third_party_id, NULL, NULL, dat.third_party_sub_id)) dist_party_site_id, dat.ref_dist_ccid dist_ref_dist_ccid, 'N' , TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, l.SUBLEDGER_BC_COMPLETE_FLAG, l.DATA_SET_ID, DECODE(l.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_distributions_all dat, ar_xla_lines_extract l WHERE l.posting_entity = 'MCD' AND l.source_id = dat.source_id AND l.source_table = dat.source_table AND l.line_id = dat.line_id AND l.level_flag = 'L' AND l.mfar_additional_entry = 'N' UNION SELECT event_id event_id, line_id dist_line_id, 'AR_DETAILED_DISTRIBUTIONS_ALL' distribution_type, ref_ctlgd_ccid dist_code_combination_id, source_id dist_source_id, NULL dist_source_id_secondary, source_table dist_source_table, NULL dist_source_table_secondary, decode(crh_status, 'REMITTED', 'REMITTANCE', 'CONFIRMED', 'CONFIRMATION', 'CLEARED', decode(POSTING_ENTITY, 'MISC_RECEIPT_HISTORY', 'MISCCASH', 'CASH')) dist_source_type, additional_char1 dist_source_type_secondary, NULL dist_tax_link_id, from_amount dist_ent_amt_from, from_currency_code dist_currency_code_from, amount dist_ent_amt, to_currency_code dist_currency_code, line_number line_number, 'US' LANGUAGE, ledger_id ledger_id, NULL dist_paired_ccid, crh_status dist_paired_source_type, decode(third_party_id, NULL, NULL, 'C') dist_party_type, third_party_id dist_party_id, third_party_site_id dist_party_site_id, ref_dist_ccid dist_ref_dist_ccid, 'Y' , TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, SUBLEDGER_BC_COMPLETE_FLAG, DATA_SET_ID, DECODE(SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), REF_TRX_DIST_ACCOUNTED_CCID FROM ar_xla_lines_extract WHERE mfar_additional_entry = 'Y' UNION SELECT licr.event_id event_id , zd.tax_dist_id dist_line_id , 'ZX_DISTRIBUTIONS_ALL' distribution_type , DECODE(SIGN(NVL(dat.amount_dr,-1)), 1,zd.debit_account_ccid,zd.credit_account_ccid) dist_code_combination_id, dat.source_id dist_source_id , dat.source_id_secondary dist_source_id_secondary , dat.source_table dist_source_table , dat.source_table_secondary dist_source_table_secondary , DECODE(SIGN(NVL(dat.amount_dr,-1)), 1,'ADVANCE_REC_TAX_UNAPPLY_D','ADVANCE_REC_TAX_APPLY_C') dist_source_type, NULL dist_source_type_secondary , zd.tax_line_id dist_tax_link_id , zd.tax_dist_amt_tax_curr dist_ent_amt_from, zd.tax_currency_code dist_currency_code_from, zd.tax_dist_amt dist_ent_amt, zd.trx_currency_code dist_currency_code , zd.tax_dist_id line_number , licr.language language , licr.ledger_id ledger_id , NULL dist_paired_ccid , 'UNAPP' dist_paired_source_type , 'C' dist_party_type , dat.third_party_id dist_party_id , TO_NUMBER(DECODE(dat.third_party_id,NULL,NULL,dat.third_party_sub_id)) dist_party_site_id , dat.ref_dist_ccid dist_ref_dist_ccid , 'N' , to_number(NULL) dist_gain_ccid, to_number(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, licr.SUBLEDGER_BC_COMPLETE_FLAG, licr.DATA_SET_ID, DECODE(licr.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_xla_lines_extract licr , ZX_TRX_TAX_DISTRIBUTIONS zd, ar_distributions_all dat , ar_receivable_applications_all ra, ar_cash_receipts_all cr 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.source_id = dat.source_id AND licr.source_table = dat.source_table AND licr.line_id = dat.line_id AND dat.source_type IN ('REC','ACTIVITY') AND ra.receivable_application_id = licr.header_table_id AND ra.status IN ('APP','ACTIVITY') AND ra.cash_receipt_id = cr.cash_receipt_id AND NVL(ra.upgrade_method,'XX') NOT IN ('R12_11ICASH_POST') AND licr.from_to_flag IN ( 'T','F') AND licr.mfar_additional_entry = 'N' UNION SELECT l.event_id event_id , zd.tax_dist_id dist_line_id , 'ZX_DISTRIBUTIONS_ALL' distribution_type , DECODE(SIGN(NVL(daf.amount_dr,-1)), 1,zd.debit_account_ccid,zd.credit_account_ccid) dist_code_combination_id, daf.source_id dist_source_id , daf.source_id_secondary dist_source_id_secondary , daf.source_table dist_source_table , daf.source_table_secondary dist_source_table_secondary , DECODE(SIGN(NVL(daf.amount_dr,-1)), 1,'ADVANCE_REC_TAX_APPLY_D','ADVANCE_REC_TAX_UNAPPLY_C') dist_source_type, NULL , zd.tax_line_id dist_tax_link_id , zd.tax_dist_amt_tax_curr dist_ent_amt_from, zd.tax_currency_code dist_currency_code_from, zd.tax_dist_amt dist_ent_amt, zd.trx_currency_code dist_currency_code , zd.tax_dist_id line_number , l.language language , l.ledger_id ledger_id , NULL dist_paired_ccid , NVL(l.crh_prv_status,'UNAPP') dist_paired_source_type , 'C' dist_party_type , daf.third_party_id dist_party_id , TO_NUMBER(DECODE(daf.third_party_id,NULL,NULL,daf.third_party_sub_id)) dist_party_site_id , daf.ref_dist_ccid dist_ref_dist_ccid , 'N' , TO_NUMBER(NULL) dist_gain_ccid, TO_NUMBER(NULL) dist_loss_ccid, NULL br_line_trx_number , NULL br_line_trx_type_name , NULL br_line_trx_document_number , NULL cm_app_trx_number , NULL cm_app_trx_type_name , NULL cm_app_trx_doc_seq_value, 'Y' override_acctd_amt_recalc, l.SUBLEDGER_BC_COMPLETE_FLAG, l.DATA_SET_ID, DECODE(l.SUBLEDGER_BC_COMPLETE_FLAG, NULL, NULL, 'Y'), NULL FROM ar_distributions_all daf , ZX_TRX_TAX_DISTRIBUTIONS zd, ar_xla_lines_extract l WHERE l.posting_entity = 'CRH' AND l.source_id = daf.source_id AND l.source_table = daf.source_table AND l.line_id = daf.line_id AND l.level_flag = 'L' AND l.mfar_additional_entry = 'N' AND daf.source_type IN ('UNAPP') 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 l.event_id = zd.ACCOUNTING_EVENT_ID AND l.line_id = zd.trx_id