VIEW: APPS.AR_XLA_ARD_LINES_V

Source

View Text - Preformatted

SELECT /*+ index(hd XLA_AE_HEADERS_U1) */ ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , DECODE( ae.accounting_class_code, 'UNAPP','UNAPP', ard.source_type) , ae.GL_SL_LINK_ID , ae.GL_SL_LINK_TABLE , ae.code_combination_id , lk.unrounded_entered_dr , lk.unrounded_entered_cr , lk.unrounded_accounted_dr , lk.unrounded_accounted_cr , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , NULL , lk.unrounded_accounted_cr , NULL , NULL , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , ae.ae_header_id , crh.cash_receipt_id , hd.accounting_date , crh.gl_posted_date , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , ae.AE_LINE_NUM FROM ar_distributions_all ard, xla_distribution_links lk, xla_ae_lines ae, ar_cash_receipt_history_all crh, ar_cash_receipts_all cr, xla_ae_headers hd WHERE ard.source_table = 'CRH' AND ard.source_id = crh.cash_receipt_history_id AND ard.line_id = lk.source_distribution_id_num_1 AND lk.application_id = 222 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' AND ae.application_id = 222 AND lk.ae_header_id = ae.ae_header_id AND lk.ae_line_num = ae.ae_line_num AND lk.ae_header_id = hd.ae_header_id AND hd.EVENT_TYPE_CODE <> 'MANUAL' AND crh.cash_receipt_id = cr.cash_receipt_id AND cr.set_of_books_id = hd.ledger_id AND crh.event_id IS NOT NULL AND ae.accounting_class_code IN ( 'CASH', 'REMITTANCE', 'CONFIRMATION', 'FV_CASH_CR', 'FV_CASH_DR', 'RECEIVABLE', 'FREIGHT', 'TAX', 'DEFERRED_TAX', 'REVENUE', 'UNEARNED_REVENUE', 'UNBILL', 'EDISC', 'EDISC_NON_REC_TAX', 'UNEDISC', 'UNEDISC_NON_REC_TAX', 'ROUNDING', 'EXCHANGE_GAIN_LOSS', 'GAIN', 'LOSS', 'UNID', 'WRITE_OFF', 'ACC', 'BANK_CHG', 'CLAIM', 'SHORT_TERM_DEBT', 'FACTOR', 'FAC_BR', 'PMT_NET', 'PREPAY', 'REFUND', 'REM_BR', 'UNAPP') UNION SELECT /*+ index(hd XLA_AE_HEADERS_U1) */ ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , DECODE( ae.accounting_class_code, 'UNAPP','UNAPP', 'EXCHANGE_GAIN_LOSS', DECODE(SIGN(NVL(lk.unrounded_accounted_cr,0)-NVL(lk.unrounded_accounted_dr,0)), -1,'EXCH_LOSS','EXCH_GAIN'), ard.SOURCE_TYPE) , ae.GL_SL_LINK_ID , ae.GL_SL_LINK_TABLE , ae.code_combination_id , lk.unrounded_entered_dr , lk.unrounded_entered_cr , lk.unrounded_accounted_dr , lk.unrounded_accounted_cr , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , NULL , NULL , NULL , NULL , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , ae.ae_header_id , ra.cash_receipt_id , hd.accounting_date , ra.gl_posted_date , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , ae.AE_LINE_NUM FROM ar_distributions_all ard, xla_distribution_links lk, xla_ae_lines ae, ar_receivable_applications_all ra, xla_ae_headers hd WHERE ard.source_table = 'RA' AND ard.source_id = ra.receivable_application_id AND ard.line_id = lk.source_distribution_id_num_1 AND lk.application_id = 222 AND lk.source_distribution_type in ( 'AR_DISTRIBUTIONS_ALL', 'MFAR_DISTRIBUTIONS_ALL') AND ae.application_id = 222 AND lk.ae_header_id = ae.ae_header_id AND lk.ae_line_num = ae.ae_line_num AND lk.ae_header_id = hd.ae_header_id AND ra.set_of_books_id = hd.ledger_id AND hd.EVENT_TYPE_CODE <> 'MANUAL' AND ra.event_id IS NOT NULL AND ae.accounting_class_code IN ( 'CASH', 'REMITTANCE', 'CONFIRMATION', 'FV_CASH_CR', 'FV_CASH_DR', 'RECEIVABLE', 'FREIGHT', 'TAX', 'DEFERRED_TAX', 'REVENUE', 'UNEARNED_REVENUE', 'UNBILL', 'EDISC', 'EDISC_NON_REC_TAX', 'UNEDISC', 'UNEDISC_NON_REC_TAX', 'ROUNDING', 'EXCHANGE_GAIN_LOSS', 'GAIN', 'LOSS', 'UNID', 'WRITE_OFF', 'ACC', 'BANK_CHG', 'CLAIM', 'SHORT_TERM_DEBT', 'FACTOR', 'FAC_BR', 'PMT_NET', 'PREPAY', 'REFUND', 'REM_BR', 'UNAPP') UNION SELECT /*+ index(hd XLA_AE_HEADERS_U1) */ ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , DECODE(ae.accounting_class_code,'RECEIVABLE','REC',ard.SOURCE_TYPE) , ae.GL_SL_LINK_ID , ae.GL_SL_LINK_TABLE , ae.code_combination_id , lk.unrounded_entered_dr , lk.unrounded_entered_cr , lk.unrounded_accounted_dr , lk.unrounded_accounted_cr , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , NULL , NULL , NULL , NULL , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , ae.ae_header_id , NULL , NULL , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , ae.AE_LINE_NUM FROM ar_distributions_all ard, xla_distribution_links lk, xla_ae_lines ae, ar_adjustments_all adj, xla_ae_headers hd WHERE ard.source_table = 'ADJ' AND ard.source_id = adj.adjustment_id AND ard.line_id = lk.source_distribution_id_num_1 AND lk.application_id = 222 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' AND ae.application_id = 222 AND lk.ae_header_id = ae.ae_header_id AND lk.ae_line_num = ae.ae_line_num AND lk.ae_header_id = hd.ae_header_id AND adj.set_of_books_id = hd.ledger_id AND hd.EVENT_TYPE_CODE <> 'MANUAL' AND adj.event_id IS NOT NULL AND ae.accounting_class_code IN ( 'RECEIVABLE', 'ADJ', 'CHARGES', 'TAX', 'DEFERRED_TAX', 'FINCHRG_NON_REC_TAX', 'ADJ_NON_REC_TAX', 'ENDORSEMENT') UNION SELECT /*+ index(hd XLA_AE_HEADERS_U1) */ ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , DECODE(ae.accounting_class_code,'RECEIVABLE','REC', ard.SOURCE_TYPE) , ae.GL_SL_LINK_ID , ae.GL_SL_LINK_TABLE , ae.code_combination_id , lk.unrounded_entered_dr , lk.unrounded_entered_cr , lk.unrounded_accounted_dr , lk.unrounded_accounted_cr , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , NULL , NULL , NULL , NULL , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , ae.ae_header_id , NULL , NULL , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , ae.AE_LINE_NUM FROM ar_distributions_all ard, xla_distribution_links lk, xla_ae_lines ae, ar_transaction_history_all th, ra_customer_trx_all trx, xla_ae_headers hd WHERE ard.source_table = 'TH' AND ard.source_id = th.transaction_history_id AND ard.line_id = lk.source_distribution_id_num_1 AND lk.application_id = 222 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL' AND ae.application_id = 222 AND lk.ae_header_id = ae.ae_header_id AND lk.ae_line_num = ae.ae_line_num AND lk.ae_header_id = hd.ae_header_id AND hd.EVENT_TYPE_CODE <> 'MANUAL' AND th.customer_trx_id = trx.customer_trx_id AND trx.set_of_books_id = hd.ledger_id AND th.event_id IS NOT NULL AND ae.accounting_class_code IN ( 'RECEIVABLE', 'DEFERRED_TAX', 'TAX', 'UNPAID_BR', 'REM_BR', 'FAC_BR') UNION SELECT /*+ index(hd XLA_AE_HEADERS_U1) */ ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , ard.SOURCE_TYPE , ae.GL_SL_LINK_ID , ae.GL_SL_LINK_TABLE , ae.code_combination_id , lk.unrounded_entered_dr , lk.unrounded_entered_cr , lk.unrounded_accounted_dr , lk.unrounded_accounted_cr , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , NULL , NULL , NULL , NULL , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , ae.ae_header_id , NULL /*7041229*/ , hd.accounting_date , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , ae.AE_LINE_NUM FROM ar_distributions_all ard, xla_distribution_links lk, xla_ae_lines ae, ar_misc_cash_distributions_all mcd, xla_ae_headers hd WHERE ard.source_table = 'MCD' AND ard.source_id = mcd.misc_cash_distribution_id AND ard.line_id = lk.source_distribution_id_num_1 AND lk.application_id = 222 AND lk.source_distribution_type in ( 'AR_DISTRIBUTIONS_ALL', 'MFAR_DISTRIBUTIONS_ALL') AND ae.application_id = 222 AND lk.ae_header_id = ae.ae_header_id AND lk.ae_line_num = ae.ae_line_num AND lk.ae_header_id = hd.ae_header_id AND mcd.set_of_books_id = hd.ledger_id AND hd.EVENT_TYPE_CODE <> 'MANUAL' AND mcd.event_id IS NOT NULL AND ae.accounting_class_code IN ( 'FV_MISCCASH_CR', 'FV_MISCCASH_DR', 'MISC_CASH', 'CASH', 'CONFIRMATION', 'REMITTANCE', 'FACTOR', 'SHORT_TERM_DEBT', 'BANK_CHG', 'TAX') UNION SELECT ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , ard.SOURCE_TYPE , NULL , NULL , ard.code_combination_id , ard.AMOUNT_DR , ard.AMOUNT_CR , ard.ACCTD_AMOUNT_DR , ard.ACCTD_AMOUNT_CR , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , ard.FROM_AMOUNT_DR , ard.FROM_AMOUNT_CR , ard.FROM_ACCTD_AMOUNT_DR , ard.FROM_ACCTD_AMOUNT_CR , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , NULL , NULL , crh.gl_date , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , NULL FROM ar_distributions_all ard, ar_cash_receipt_history_all crh WHERE crh.posting_control_id <> -3 AND ard.source_table = 'CRH' AND ard.source_id = crh.cash_receipt_history_id AND crh.event_id IS NULL UNION SELECT ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , ard.SOURCE_TYPE , NULL , NULL , ard.code_combination_id , ard.AMOUNT_DR , ard.AMOUNT_CR , ard.ACCTD_AMOUNT_DR , ard.ACCTD_AMOUNT_CR , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , ard.FROM_AMOUNT_DR , ard.FROM_AMOUNT_CR , ard.FROM_ACCTD_AMOUNT_DR , ard.FROM_ACCTD_AMOUNT_CR , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , NULL , NULL , ra.gl_date , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , NULL FROM ar_distributions_all ard, ar_receivable_applications_all ra WHERE ra.posting_control_id <> -3 AND ard.source_table = 'RA' AND ard.source_id = ra.receivable_application_id AND ra.status in('APP','ACC','ACTIVITY','OTHER ACC') AND ra.event_id IS NULL UNION SELECT ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , ard.SOURCE_TYPE , NULL , NULL , ard.code_combination_id , ard.AMOUNT_DR , ard.AMOUNT_CR , ard.ACCTD_AMOUNT_DR , ard.ACCTD_AMOUNT_CR , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , ard.FROM_AMOUNT_DR , ard.FROM_AMOUNT_CR , ard.FROM_ACCTD_AMOUNT_DR , ard.FROM_ACCTD_AMOUNT_CR , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , NULL , NULL , ra.gl_date , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , NULL FROM ar_distributions_all ard, ar_receivable_applications_all ra WHERE ra.posting_control_id <> -3 AND ard.source_table = 'RA' AND ard.source_id = ra.receivable_application_id AND ra.status in('UNAPP','UNID') AND ra.event_id IS NULL AND EXISTS (SELECT NULL FROM xla_distribution_links lk WHERE lk.source_distribution_id_num_1 = ard.line_id AND lk.application_id = 222 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL') UNION SELECT ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , ard.SOURCE_TYPE , NULL , NULL , ard.code_combination_id , ard.AMOUNT_DR , ard.AMOUNT_CR , ard.ACCTD_AMOUNT_DR , ard.ACCTD_AMOUNT_CR , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , ard.FROM_AMOUNT_DR , ard.FROM_AMOUNT_CR , ard.FROM_ACCTD_AMOUNT_DR , ard.FROM_ACCTD_AMOUNT_CR , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , NULL , NULL , adj.gl_date , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , NULL FROM ar_distributions_all ard, ar_adjustments_all adj WHERE adj.posting_control_id <> -3 AND ard.source_table = 'ADJ' AND ard.source_id = adj.adjustment_id AND adj.event_id IS NULL UNION SELECT ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , ard.SOURCE_TYPE , NULL , NULL , ard.code_combination_id , ard.AMOUNT_DR , ard.AMOUNT_CR , ard.ACCTD_AMOUNT_DR , ard.ACCTD_AMOUNT_CR , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , ard.FROM_AMOUNT_DR , ard.FROM_AMOUNT_CR , ard.FROM_ACCTD_AMOUNT_DR , ard.FROM_ACCTD_AMOUNT_CR , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , NULL , NULL , th.gl_date , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , NULL FROM ar_distributions_all ard, ar_transaction_history_all th WHERE th.posting_control_id <> -3 AND ard.source_table = 'TH' AND ard.source_id = th.transaction_history_id AND th.event_id IS NULL UNION SELECT ard.LINE_ID , ard.SOURCE_ID , ard.SOURCE_TABLE , ard.SOURCE_TYPE , NULL , NULL , ard.code_combination_id , ard.AMOUNT_DR , ard.AMOUNT_CR , ard.ACCTD_AMOUNT_DR , ard.ACCTD_AMOUNT_CR , ard.ORG_ID , ard.SOURCE_TABLE_SECONDARY , ard.SOURCE_ID_SECONDARY , ard.CURRENCY_CODE , ard.CURRENCY_CONVERSION_RATE , ard.CURRENCY_CONVERSION_TYPE , ard.CURRENCY_CONVERSION_DATE , ard.TAXABLE_ENTERED_DR , ard.TAXABLE_ENTERED_CR , ard.TAXABLE_ACCOUNTED_DR , ard.TAXABLE_ACCOUNTED_CR , ard.TAX_LINK_ID , ard.THIRD_PARTY_ID , ard.THIRD_PARTY_SUB_ID , ard.REVERSED_SOURCE_ID , ard.TAX_CODE_ID , ard.LOCATION_SEGMENT_ID , ard.SOURCE_TYPE_SECONDARY , ard.TAX_GROUP_CODE_ID , ard.REF_CUSTOMER_TRX_LINE_ID , ard.REF_CUST_TRX_LINE_GL_DIST_ID , ard.REF_LINE_ID , ard.FROM_AMOUNT_DR , ard.FROM_AMOUNT_CR , ard.FROM_ACCTD_AMOUNT_DR , ard.FROM_ACCTD_AMOUNT_CR , ard.REF_ACCOUNT_CLASS , ard.ACTIVITY_BUCKET , ard.REF_DIST_CCID , ard.REF_MF_DIST_FLAG , NULL , NULL , mcd.gl_date , NULL , ard.CREATION_DATE , ard.CREATED_BY , ard.LAST_UPDATED_BY , ard.LAST_UPDATE_DATE , NULL FROM ar_distributions_all ard, ar_misc_cash_distributions_all mcd WHERE mcd.posting_control_id <> -3 AND ard.source_table = 'MCD' AND ard.source_id = mcd.misc_cash_distribution_id AND mcd.event_id IS NULL

View Text - HTML Formatted

SELECT /*+ INDEX(HD XLA_AE_HEADERS_U1) */ ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, DECODE( AE.ACCOUNTING_CLASS_CODE
, 'UNAPP'
, 'UNAPP'
, ARD.SOURCE_TYPE)
, AE.GL_SL_LINK_ID
, AE.GL_SL_LINK_TABLE
, AE.CODE_COMBINATION_ID
, LK.UNROUNDED_ENTERED_DR
, LK.UNROUNDED_ENTERED_CR
, LK.UNROUNDED_ACCOUNTED_DR
, LK.UNROUNDED_ACCOUNTED_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, NULL
, LK.UNROUNDED_ACCOUNTED_CR
, NULL
, NULL
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, AE.AE_HEADER_ID
, CRH.CASH_RECEIPT_ID
, HD.ACCOUNTING_DATE
, CRH.GL_POSTED_DATE
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, AE.AE_LINE_NUM
FROM AR_DISTRIBUTIONS_ALL ARD
, XLA_DISTRIBUTION_LINKS LK
, XLA_AE_LINES AE
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPTS_ALL CR
, XLA_AE_HEADERS HD
WHERE ARD.SOURCE_TABLE = 'CRH'
AND ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND ARD.LINE_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
AND LK.APPLICATION_ID = 222
AND LK.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
AND AE.APPLICATION_ID = 222
AND LK.AE_HEADER_ID = AE.AE_HEADER_ID
AND LK.AE_LINE_NUM = AE.AE_LINE_NUM
AND LK.AE_HEADER_ID = HD.AE_HEADER_ID
AND HD.EVENT_TYPE_CODE <> 'MANUAL'
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.SET_OF_BOOKS_ID = HD.LEDGER_ID
AND CRH.EVENT_ID IS NOT NULL
AND AE.ACCOUNTING_CLASS_CODE IN ( 'CASH'
, 'REMITTANCE'
, 'CONFIRMATION'
, 'FV_CASH_CR'
, 'FV_CASH_DR'
, 'RECEIVABLE'
, 'FREIGHT'
, 'TAX'
, 'DEFERRED_TAX'
, 'REVENUE'
, 'UNEARNED_REVENUE'
, 'UNBILL'
, 'EDISC'
, 'EDISC_NON_REC_TAX'
, 'UNEDISC'
, 'UNEDISC_NON_REC_TAX'
, 'ROUNDING'
, 'EXCHANGE_GAIN_LOSS'
, 'GAIN'
, 'LOSS'
, 'UNID'
, 'WRITE_OFF'
, 'ACC'
, 'BANK_CHG'
, 'CLAIM'
, 'SHORT_TERM_DEBT'
, 'FACTOR'
, 'FAC_BR'
, 'PMT_NET'
, 'PREPAY'
, 'REFUND'
, 'REM_BR'
, 'UNAPP') UNION SELECT /*+ INDEX(HD XLA_AE_HEADERS_U1) */ ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, DECODE( AE.ACCOUNTING_CLASS_CODE
, 'UNAPP'
, 'UNAPP'
, 'EXCHANGE_GAIN_LOSS'
, DECODE(SIGN(NVL(LK.UNROUNDED_ACCOUNTED_CR
, 0)-NVL(LK.UNROUNDED_ACCOUNTED_DR
, 0))
, -1
, 'EXCH_LOSS'
, 'EXCH_GAIN')
, ARD.SOURCE_TYPE)
, AE.GL_SL_LINK_ID
, AE.GL_SL_LINK_TABLE
, AE.CODE_COMBINATION_ID
, LK.UNROUNDED_ENTERED_DR
, LK.UNROUNDED_ENTERED_CR
, LK.UNROUNDED_ACCOUNTED_DR
, LK.UNROUNDED_ACCOUNTED_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, NULL
, NULL
, NULL
, NULL
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, AE.AE_HEADER_ID
, RA.CASH_RECEIPT_ID
, HD.ACCOUNTING_DATE
, RA.GL_POSTED_DATE
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, AE.AE_LINE_NUM
FROM AR_DISTRIBUTIONS_ALL ARD
, XLA_DISTRIBUTION_LINKS LK
, XLA_AE_LINES AE
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, XLA_AE_HEADERS HD
WHERE ARD.SOURCE_TABLE = 'RA'
AND ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID
AND ARD.LINE_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
AND LK.APPLICATION_ID = 222
AND LK.SOURCE_DISTRIBUTION_TYPE IN ( 'AR_DISTRIBUTIONS_ALL'
, 'MFAR_DISTRIBUTIONS_ALL')
AND AE.APPLICATION_ID = 222
AND LK.AE_HEADER_ID = AE.AE_HEADER_ID
AND LK.AE_LINE_NUM = AE.AE_LINE_NUM
AND LK.AE_HEADER_ID = HD.AE_HEADER_ID
AND RA.SET_OF_BOOKS_ID = HD.LEDGER_ID
AND HD.EVENT_TYPE_CODE <> 'MANUAL'
AND RA.EVENT_ID IS NOT NULL
AND AE.ACCOUNTING_CLASS_CODE IN ( 'CASH'
, 'REMITTANCE'
, 'CONFIRMATION'
, 'FV_CASH_CR'
, 'FV_CASH_DR'
, 'RECEIVABLE'
, 'FREIGHT'
, 'TAX'
, 'DEFERRED_TAX'
, 'REVENUE'
, 'UNEARNED_REVENUE'
, 'UNBILL'
, 'EDISC'
, 'EDISC_NON_REC_TAX'
, 'UNEDISC'
, 'UNEDISC_NON_REC_TAX'
, 'ROUNDING'
, 'EXCHANGE_GAIN_LOSS'
, 'GAIN'
, 'LOSS'
, 'UNID'
, 'WRITE_OFF'
, 'ACC'
, 'BANK_CHG'
, 'CLAIM'
, 'SHORT_TERM_DEBT'
, 'FACTOR'
, 'FAC_BR'
, 'PMT_NET'
, 'PREPAY'
, 'REFUND'
, 'REM_BR'
, 'UNAPP') UNION SELECT /*+ INDEX(HD XLA_AE_HEADERS_U1) */ ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, DECODE(AE.ACCOUNTING_CLASS_CODE
, 'RECEIVABLE'
, 'REC'
, ARD.SOURCE_TYPE)
, AE.GL_SL_LINK_ID
, AE.GL_SL_LINK_TABLE
, AE.CODE_COMBINATION_ID
, LK.UNROUNDED_ENTERED_DR
, LK.UNROUNDED_ENTERED_CR
, LK.UNROUNDED_ACCOUNTED_DR
, LK.UNROUNDED_ACCOUNTED_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, NULL
, NULL
, NULL
, NULL
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, AE.AE_HEADER_ID
, NULL
, NULL
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, AE.AE_LINE_NUM
FROM AR_DISTRIBUTIONS_ALL ARD
, XLA_DISTRIBUTION_LINKS LK
, XLA_AE_LINES AE
, AR_ADJUSTMENTS_ALL ADJ
, XLA_AE_HEADERS HD
WHERE ARD.SOURCE_TABLE = 'ADJ'
AND ARD.SOURCE_ID = ADJ.ADJUSTMENT_ID
AND ARD.LINE_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
AND LK.APPLICATION_ID = 222
AND LK.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
AND AE.APPLICATION_ID = 222
AND LK.AE_HEADER_ID = AE.AE_HEADER_ID
AND LK.AE_LINE_NUM = AE.AE_LINE_NUM
AND LK.AE_HEADER_ID = HD.AE_HEADER_ID
AND ADJ.SET_OF_BOOKS_ID = HD.LEDGER_ID
AND HD.EVENT_TYPE_CODE <> 'MANUAL'
AND ADJ.EVENT_ID IS NOT NULL
AND AE.ACCOUNTING_CLASS_CODE IN ( 'RECEIVABLE'
, 'ADJ'
, 'CHARGES'
, 'TAX'
, 'DEFERRED_TAX'
, 'FINCHRG_NON_REC_TAX'
, 'ADJ_NON_REC_TAX'
, 'ENDORSEMENT') UNION SELECT /*+ INDEX(HD XLA_AE_HEADERS_U1) */ ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, DECODE(AE.ACCOUNTING_CLASS_CODE
, 'RECEIVABLE'
, 'REC'
, ARD.SOURCE_TYPE)
, AE.GL_SL_LINK_ID
, AE.GL_SL_LINK_TABLE
, AE.CODE_COMBINATION_ID
, LK.UNROUNDED_ENTERED_DR
, LK.UNROUNDED_ENTERED_CR
, LK.UNROUNDED_ACCOUNTED_DR
, LK.UNROUNDED_ACCOUNTED_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, NULL
, NULL
, NULL
, NULL
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, AE.AE_HEADER_ID
, NULL
, NULL
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, AE.AE_LINE_NUM
FROM AR_DISTRIBUTIONS_ALL ARD
, XLA_DISTRIBUTION_LINKS LK
, XLA_AE_LINES AE
, AR_TRANSACTION_HISTORY_ALL TH
, RA_CUSTOMER_TRX_ALL TRX
, XLA_AE_HEADERS HD
WHERE ARD.SOURCE_TABLE = 'TH'
AND ARD.SOURCE_ID = TH.TRANSACTION_HISTORY_ID
AND ARD.LINE_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
AND LK.APPLICATION_ID = 222
AND LK.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
AND AE.APPLICATION_ID = 222
AND LK.AE_HEADER_ID = AE.AE_HEADER_ID
AND LK.AE_LINE_NUM = AE.AE_LINE_NUM
AND LK.AE_HEADER_ID = HD.AE_HEADER_ID
AND HD.EVENT_TYPE_CODE <> 'MANUAL'
AND TH.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.SET_OF_BOOKS_ID = HD.LEDGER_ID
AND TH.EVENT_ID IS NOT NULL
AND AE.ACCOUNTING_CLASS_CODE IN ( 'RECEIVABLE'
, 'DEFERRED_TAX'
, 'TAX'
, 'UNPAID_BR'
, 'REM_BR'
, 'FAC_BR') UNION SELECT /*+ INDEX(HD XLA_AE_HEADERS_U1) */ ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, ARD.SOURCE_TYPE
, AE.GL_SL_LINK_ID
, AE.GL_SL_LINK_TABLE
, AE.CODE_COMBINATION_ID
, LK.UNROUNDED_ENTERED_DR
, LK.UNROUNDED_ENTERED_CR
, LK.UNROUNDED_ACCOUNTED_DR
, LK.UNROUNDED_ACCOUNTED_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, NULL
, NULL
, NULL
, NULL
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, AE.AE_HEADER_ID
, NULL /*7041229*/
, HD.ACCOUNTING_DATE
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, AE.AE_LINE_NUM
FROM AR_DISTRIBUTIONS_ALL ARD
, XLA_DISTRIBUTION_LINKS LK
, XLA_AE_LINES AE
, AR_MISC_CASH_DISTRIBUTIONS_ALL MCD
, XLA_AE_HEADERS HD
WHERE ARD.SOURCE_TABLE = 'MCD'
AND ARD.SOURCE_ID = MCD.MISC_CASH_DISTRIBUTION_ID
AND ARD.LINE_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
AND LK.APPLICATION_ID = 222
AND LK.SOURCE_DISTRIBUTION_TYPE IN ( 'AR_DISTRIBUTIONS_ALL'
, 'MFAR_DISTRIBUTIONS_ALL')
AND AE.APPLICATION_ID = 222
AND LK.AE_HEADER_ID = AE.AE_HEADER_ID
AND LK.AE_LINE_NUM = AE.AE_LINE_NUM
AND LK.AE_HEADER_ID = HD.AE_HEADER_ID
AND MCD.SET_OF_BOOKS_ID = HD.LEDGER_ID
AND HD.EVENT_TYPE_CODE <> 'MANUAL'
AND MCD.EVENT_ID IS NOT NULL
AND AE.ACCOUNTING_CLASS_CODE IN ( 'FV_MISCCASH_CR'
, 'FV_MISCCASH_DR'
, 'MISC_CASH'
, 'CASH'
, 'CONFIRMATION'
, 'REMITTANCE'
, 'FACTOR'
, 'SHORT_TERM_DEBT'
, 'BANK_CHG'
, 'TAX') UNION SELECT ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, ARD.SOURCE_TYPE
, NULL
, NULL
, ARD.CODE_COMBINATION_ID
, ARD.AMOUNT_DR
, ARD.AMOUNT_CR
, ARD.ACCTD_AMOUNT_DR
, ARD.ACCTD_AMOUNT_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, ARD.FROM_AMOUNT_DR
, ARD.FROM_AMOUNT_CR
, ARD.FROM_ACCTD_AMOUNT_DR
, ARD.FROM_ACCTD_AMOUNT_CR
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, NULL
, NULL
, CRH.GL_DATE
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, NULL
FROM AR_DISTRIBUTIONS_ALL ARD
, AR_CASH_RECEIPT_HISTORY_ALL CRH
WHERE CRH.POSTING_CONTROL_ID <> -3
AND ARD.SOURCE_TABLE = 'CRH'
AND ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND CRH.EVENT_ID IS NULL UNION SELECT ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, ARD.SOURCE_TYPE
, NULL
, NULL
, ARD.CODE_COMBINATION_ID
, ARD.AMOUNT_DR
, ARD.AMOUNT_CR
, ARD.ACCTD_AMOUNT_DR
, ARD.ACCTD_AMOUNT_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, ARD.FROM_AMOUNT_DR
, ARD.FROM_AMOUNT_CR
, ARD.FROM_ACCTD_AMOUNT_DR
, ARD.FROM_ACCTD_AMOUNT_CR
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, NULL
, NULL
, RA.GL_DATE
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, NULL
FROM AR_DISTRIBUTIONS_ALL ARD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE RA.POSTING_CONTROL_ID <> -3
AND ARD.SOURCE_TABLE = 'RA'
AND ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID
AND RA.STATUS IN('APP'
, 'ACC'
, 'ACTIVITY'
, 'OTHER ACC')
AND RA.EVENT_ID IS NULL UNION SELECT ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, ARD.SOURCE_TYPE
, NULL
, NULL
, ARD.CODE_COMBINATION_ID
, ARD.AMOUNT_DR
, ARD.AMOUNT_CR
, ARD.ACCTD_AMOUNT_DR
, ARD.ACCTD_AMOUNT_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, ARD.FROM_AMOUNT_DR
, ARD.FROM_AMOUNT_CR
, ARD.FROM_ACCTD_AMOUNT_DR
, ARD.FROM_ACCTD_AMOUNT_CR
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, NULL
, NULL
, RA.GL_DATE
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, NULL
FROM AR_DISTRIBUTIONS_ALL ARD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE RA.POSTING_CONTROL_ID <> -3
AND ARD.SOURCE_TABLE = 'RA'
AND ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID
AND RA.STATUS IN('UNAPP'
, 'UNID')
AND RA.EVENT_ID IS NULL
AND EXISTS (SELECT NULL
FROM XLA_DISTRIBUTION_LINKS LK
WHERE LK.SOURCE_DISTRIBUTION_ID_NUM_1 = ARD.LINE_ID
AND LK.APPLICATION_ID = 222
AND LK.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL') UNION SELECT ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, ARD.SOURCE_TYPE
, NULL
, NULL
, ARD.CODE_COMBINATION_ID
, ARD.AMOUNT_DR
, ARD.AMOUNT_CR
, ARD.ACCTD_AMOUNT_DR
, ARD.ACCTD_AMOUNT_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, ARD.FROM_AMOUNT_DR
, ARD.FROM_AMOUNT_CR
, ARD.FROM_ACCTD_AMOUNT_DR
, ARD.FROM_ACCTD_AMOUNT_CR
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, NULL
, NULL
, ADJ.GL_DATE
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, NULL
FROM AR_DISTRIBUTIONS_ALL ARD
, AR_ADJUSTMENTS_ALL ADJ
WHERE ADJ.POSTING_CONTROL_ID <> -3
AND ARD.SOURCE_TABLE = 'ADJ'
AND ARD.SOURCE_ID = ADJ.ADJUSTMENT_ID
AND ADJ.EVENT_ID IS NULL UNION SELECT ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, ARD.SOURCE_TYPE
, NULL
, NULL
, ARD.CODE_COMBINATION_ID
, ARD.AMOUNT_DR
, ARD.AMOUNT_CR
, ARD.ACCTD_AMOUNT_DR
, ARD.ACCTD_AMOUNT_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, ARD.FROM_AMOUNT_DR
, ARD.FROM_AMOUNT_CR
, ARD.FROM_ACCTD_AMOUNT_DR
, ARD.FROM_ACCTD_AMOUNT_CR
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, NULL
, NULL
, TH.GL_DATE
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, NULL
FROM AR_DISTRIBUTIONS_ALL ARD
, AR_TRANSACTION_HISTORY_ALL TH
WHERE TH.POSTING_CONTROL_ID <> -3
AND ARD.SOURCE_TABLE = 'TH'
AND ARD.SOURCE_ID = TH.TRANSACTION_HISTORY_ID
AND TH.EVENT_ID IS NULL UNION SELECT ARD.LINE_ID
, ARD.SOURCE_ID
, ARD.SOURCE_TABLE
, ARD.SOURCE_TYPE
, NULL
, NULL
, ARD.CODE_COMBINATION_ID
, ARD.AMOUNT_DR
, ARD.AMOUNT_CR
, ARD.ACCTD_AMOUNT_DR
, ARD.ACCTD_AMOUNT_CR
, ARD.ORG_ID
, ARD.SOURCE_TABLE_SECONDARY
, ARD.SOURCE_ID_SECONDARY
, ARD.CURRENCY_CODE
, ARD.CURRENCY_CONVERSION_RATE
, ARD.CURRENCY_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_DATE
, ARD.TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR
, ARD.TAX_LINK_ID
, ARD.THIRD_PARTY_ID
, ARD.THIRD_PARTY_SUB_ID
, ARD.REVERSED_SOURCE_ID
, ARD.TAX_CODE_ID
, ARD.LOCATION_SEGMENT_ID
, ARD.SOURCE_TYPE_SECONDARY
, ARD.TAX_GROUP_CODE_ID
, ARD.REF_CUSTOMER_TRX_LINE_ID
, ARD.REF_CUST_TRX_LINE_GL_DIST_ID
, ARD.REF_LINE_ID
, ARD.FROM_AMOUNT_DR
, ARD.FROM_AMOUNT_CR
, ARD.FROM_ACCTD_AMOUNT_DR
, ARD.FROM_ACCTD_AMOUNT_CR
, ARD.REF_ACCOUNT_CLASS
, ARD.ACTIVITY_BUCKET
, ARD.REF_DIST_CCID
, ARD.REF_MF_DIST_FLAG
, NULL
, NULL
, MCD.GL_DATE
, NULL
, ARD.CREATION_DATE
, ARD.CREATED_BY
, ARD.LAST_UPDATED_BY
, ARD.LAST_UPDATE_DATE
, NULL
FROM AR_DISTRIBUTIONS_ALL ARD
, AR_MISC_CASH_DISTRIBUTIONS_ALL MCD
WHERE MCD.POSTING_CONTROL_ID <> -3
AND ARD.SOURCE_TABLE = 'MCD'
AND ARD.SOURCE_ID = MCD.MISC_CASH_DISTRIBUTION_ID
AND MCD.EVENT_ID IS NULL