VIEW: APPS.AR_DISTRIBUTIONS_L_V

Source

View Text - Preformatted

SELECT /*+INDEX(l ar_xla_lines_extract_n1)*/ 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 ,dat.tax_link_id 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' ,to_number(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)*/ 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) 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)*/ 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) FROM ar_distributions_all daf ,ar_xla_lines_extract l 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' UNION SELECT /*+INDEX(licr ar_xla_lines_extract_n1)*/ 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 ,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((nvl(ra.acctd_amount_applied_from,0)-nvl(ra.acctd_amount_applied_to,0)),0,to_number(NULL), arp_xla_extract_main_pkg.get_glr_ccid(ra.receivable_application_id) ) 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 = 'T' AND licr.mfar_additional_entry = 'N' UNION SELECT /*+INDEX(licr ar_xla_lines_extract_n1)*/ 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) 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)*/ 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) FROM ar_xla_lines_extract licm, ar_distributions_all dat, ar_receivable_applications_all ra, ra_customer_trx_all cm 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' UNION SELECT /*+INDEX(l ar_xla_lines_extract_n1)*/ 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 ,dat.tax_link_id 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) 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 /*+INDEX(licr ar_xla_lines_extract_n1)*/ licr.event_id event_id ,dat.cash_basis_distribution_id dist_line_id ,'AR_CASH_BASIS_DISTS_ALL' distribution_type ,dat.code_combination_id dist_code_combination_id ,dat.receivable_application_id dist_source_id ,dat.receivable_application_id_cash dist_source_id_secondary ,'RA' dist_source_table ,NULL dist_source_table_secondary ,'REC' dist_source_type ,dat.activity_bucket dist_source_type_secondary ,NULL dist_tax_link_id ,dat.from_amount dist_ent_amt_from ,cr.currency_code dist_currency_code_from ,dat.amount 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 ,DECODE(cr.pay_from_customer,NULL,NULL,'C') dist_party_type ,cr.pay_from_customer dist_party_id ,NULL dist_party_site_id ,dat.code_combination_id dist_ref_dist_ccid ,'N' ,to_number(NULL) FROM ar_xla_lines_extract licr, ar_cash_basis_dists_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.receivable_application_id AND licr.line_id = dat.cash_basis_distribution_id AND ra.receivable_application_id = licr.header_table_id AND ra.status = 'APP' AND ra.cash_receipt_id = cr.cash_receipt_id AND ra.upgrade_method = 'R12_11ICASH_POST' AND licr.from_to_flag = 'T' AND licr.mfar_additional_entry = 'N' UNION SELECT /*+INDEX(licm ar_xla_lines_extract_n1)*/ licm.event_id event_id ,dat.cash_basis_distribution_id dist_line_id ,'AR_CASH_BASIS_DISTS_ALL' distribution_type ,dat.code_combination_id dist_code_combination_id ,dat.receivable_application_id dist_source_id ,dat.receivable_application_id_cash dist_source_id_secondary ,'RA' dist_source_table ,NULL dist_source_table_secondary ,'REC' dist_source_type ,dat.activity_bucket dist_source_type_secondary ,NULL dist_tax_link_id ,dat.from_amount dist_ent_amt_from ,cm.invoice_currency_code dist_currency_code_from ,dat.amount 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(cm.bill_to_customer_id,NULL,NULL,'C') dist_party_type ,cm.bill_to_customer_id dist_party_id ,NULL dist_party_site_id ,dat.code_combination_id dist_ref_dist_ccid ,'N' ,to_number(NULL) FROM ar_xla_lines_extract licm, ar_cash_basis_dists_all dat, ar_receivable_applications_all ra, ra_customer_trx_all cm WHERE licm.posting_entity = 'APP' AND licm.level_flag = 'L' AND licm.customer_trx_id IS NOT NULL AND licm.source_id = dat.receivable_application_id AND licm.line_id = dat.cash_basis_distribution_id AND ra.receivable_application_id = licm.header_table_id AND ra.status = 'APP' AND ra.upgrade_method = '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' UNION SELECT event_id event_id ,line_id dist_line_id ,'MFAR_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(source_table,'MCD', '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) FROM ar_xla_lines_extract WHERE mfar_additional_entry = 'Y'

View Text - HTML Formatted

SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1)*/ 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
, DAT.TAX_LINK_ID 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'
, TO_NUMBER(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)*/ 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)
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)*/ 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)
FROM AR_DISTRIBUTIONS_ALL DAF
, AR_XLA_LINES_EXTRACT L
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' UNION SELECT /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1)*/ 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
, 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((NVL(RA.ACCTD_AMOUNT_APPLIED_FROM
, 0)-NVL(RA.ACCTD_AMOUNT_APPLIED_TO
, 0))
, 0
, TO_NUMBER(NULL)
, ARP_XLA_EXTRACT_MAIN_PKG.GET_GLR_CCID(RA.RECEIVABLE_APPLICATION_ID) )
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 = 'T'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1)*/ 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)
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)*/ 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)
FROM AR_XLA_LINES_EXTRACT LICM
, AR_DISTRIBUTIONS_ALL DAT
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, RA_CUSTOMER_TRX_ALL CM
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' UNION SELECT /*+INDEX(L AR_XLA_LINES_EXTRACT_N1)*/ 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
, DAT.TAX_LINK_ID 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)
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 /*+INDEX(LICR AR_XLA_LINES_EXTRACT_N1)*/ LICR.EVENT_ID EVENT_ID
, DAT.CASH_BASIS_DISTRIBUTION_ID DIST_LINE_ID
, 'AR_CASH_BASIS_DISTS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.RECEIVABLE_APPLICATION_ID DIST_SOURCE_ID
, DAT.RECEIVABLE_APPLICATION_ID_CASH DIST_SOURCE_ID_SECONDARY
, 'RA' DIST_SOURCE_TABLE
, NULL DIST_SOURCE_TABLE_SECONDARY
, 'REC' DIST_SOURCE_TYPE
, DAT.ACTIVITY_BUCKET DIST_SOURCE_TYPE_SECONDARY
, NULL DIST_TAX_LINK_ID
, DAT.FROM_AMOUNT DIST_ENT_AMT_FROM
, CR.CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, DAT.AMOUNT 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
, DECODE(CR.PAY_FROM_CUSTOMER
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, CR.PAY_FROM_CUSTOMER DIST_PARTY_ID
, NULL DIST_PARTY_SITE_ID
, DAT.CODE_COMBINATION_ID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
FROM AR_XLA_LINES_EXTRACT LICR
, AR_CASH_BASIS_DISTS_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.RECEIVABLE_APPLICATION_ID
AND LICR.LINE_ID = DAT.CASH_BASIS_DISTRIBUTION_ID
AND RA.RECEIVABLE_APPLICATION_ID = LICR.HEADER_TABLE_ID
AND RA.STATUS = 'APP'
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND RA.UPGRADE_METHOD = 'R12_11ICASH_POST'
AND LICR.FROM_TO_FLAG = 'T'
AND LICR.MFAR_ADDITIONAL_ENTRY = 'N' UNION SELECT /*+INDEX(LICM AR_XLA_LINES_EXTRACT_N1)*/ LICM.EVENT_ID EVENT_ID
, DAT.CASH_BASIS_DISTRIBUTION_ID DIST_LINE_ID
, 'AR_CASH_BASIS_DISTS_ALL' DISTRIBUTION_TYPE
, DAT.CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID
, DAT.RECEIVABLE_APPLICATION_ID DIST_SOURCE_ID
, DAT.RECEIVABLE_APPLICATION_ID_CASH DIST_SOURCE_ID_SECONDARY
, 'RA' DIST_SOURCE_TABLE
, NULL DIST_SOURCE_TABLE_SECONDARY
, 'REC' DIST_SOURCE_TYPE
, DAT.ACTIVITY_BUCKET DIST_SOURCE_TYPE_SECONDARY
, NULL DIST_TAX_LINK_ID
, DAT.FROM_AMOUNT DIST_ENT_AMT_FROM
, CM.INVOICE_CURRENCY_CODE DIST_CURRENCY_CODE_FROM
, DAT.AMOUNT 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(CM.BILL_TO_CUSTOMER_ID
, NULL
, NULL
, 'C') DIST_PARTY_TYPE
, CM.BILL_TO_CUSTOMER_ID DIST_PARTY_ID
, NULL DIST_PARTY_SITE_ID
, DAT.CODE_COMBINATION_ID DIST_REF_DIST_CCID
, 'N'
, TO_NUMBER(NULL)
FROM AR_XLA_LINES_EXTRACT LICM
, AR_CASH_BASIS_DISTS_ALL DAT
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, RA_CUSTOMER_TRX_ALL CM
WHERE LICM.POSTING_ENTITY = 'APP'
AND LICM.LEVEL_FLAG = 'L'
AND LICM.CUSTOMER_TRX_ID IS NOT NULL
AND LICM.SOURCE_ID = DAT.RECEIVABLE_APPLICATION_ID
AND LICM.LINE_ID = DAT.CASH_BASIS_DISTRIBUTION_ID
AND RA.RECEIVABLE_APPLICATION_ID = LICM.HEADER_TABLE_ID
AND RA.STATUS = 'APP'
AND RA.UPGRADE_METHOD = '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' UNION SELECT EVENT_ID EVENT_ID
, LINE_ID DIST_LINE_ID
, 'MFAR_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(SOURCE_TABLE
, 'MCD'
, '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)
FROM AR_XLA_LINES_EXTRACT
WHERE MFAR_ADDITIONAL_ENTRY = 'Y'