eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_AEL_GL_REC_V

Source

View Text - Preformatted


SELECT /*+ ORDERED */ JEH.je_header_id JE_HEADER_ID , R.je_line_num JE_LINE_NUM , 222 APPLICATION_ID , CR.set_of_books_id SET_OF_BOOKS_ID , CR.org_id ORG_ID , CR.type TRX_CLASS , ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'PAYMENT_CATEGORY_TYPE', CR.type) TRX_CLASS_NAME , CR.receipt_method_id TRX_TYPE_N , RM.name TRX_TYPE_NAME , CR.receipt_number TRX_NUMBER_DISPLAYED , CR.receipt_number TRX_NUMBER_C , CR.receipt_date TRX_DATE , CR.comments COMMENTS , CR.doc_sequence_id DOC_SEQUENCE_ID , SEQ.name DOC_SEQUENCE_NAME , CR.doc_sequence_value DOC_SEQUENCE_VALUE, 'CR' TRX_HDR_TABLE, CR.cash_receipt_id TRX_HDR_ID, ARD.source_type ACCT_LINE_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'DISTRIBUTION_SOURCE_TYPE', ARD.source_type ) ACCT_LINE_TYPE_NAME, ARD.code_combination_id CODE_COMBINATION_ID, ARD.currency_code CURRENCY_CODE, ARD.amount_dr ENTERED_DR, ARD.amount_cr ENTERED_CR, ARD.acctd_amount_dr ACCOUNTED_DR, ARD.acctd_amount_cr ACCOUNTED_CR, ARD.currency_conversion_date CURRENCY_CONVERSION_DATE, ARD.currency_conversion_type CURRENCY_CONVERSION_TYPE, GLCT.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, ARD.currency_conversion_rate CURRENCY_CONVERSION_RATE, 'C' THIRD_PARTY_TYPE, CR.pay_from_customer THIRD_PARTY_ID, CUST_ACCT.account_number THIRD_PARTY_NUMBER, substrb(party.party_name,1,50) THIRD_PARTY_NAME, CR.customer_site_use_id THIRD_PARTY_SUB_ID, SU.location THIRD_PARTY_SUB_NAME, CRH.gl_date ACCOUNTING_DATE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'YES/NO', decode(CRH.posting_control_id,-3, 'N','Y') ) GL_TRANSFER_STATUS_NAME, decode(CRH.posting_control_id, -3, 'N','Y') GL_TRANSFER_STATUS, ARD.source_table SOURCE_TABLE, ARD.source_id SOURCE_ID, ARD.line_id AEL_ID, 'ARD' AEL_TABLE, /* Line Ref: Trans Class + ' ' + Trans Number */ ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'PAYMENT_CATEGORY_TYPE', CR.type) ||' '|| CR.receipt_number AE_LINE_REFERENCE, rpad(CR.receipt_number, 30) AE_LINE_REFERENCE_INTERNAL, ARD.last_update_date LAST_UPDATE_DATE, ARD.last_updated_by LAST_UPDATED_BY, ARD.creation_date CREATION_DATE, ARD.created_by CREATED_BY, ARD.last_update_login LAST_UPDATE_LOGIN, /* The following columns are specific to AR Receipts */ 'ACCRUAL' ACCOUNTING_BASIS, VAT.tax_code TAX_CODE, ARD.tax_code_id TAX_CODE_ID, ARD.tax_link_id TAX_LINK_ID, ARD.taxable_entered_dr TAXABLE_ENTERED_DR, ARD.taxable_entered_cr TAXABLE_ENTERED_CR, ARD.taxable_accounted_dr TAXABLE_ACCOUNTED_DR, ARD.taxable_accounted_cr TAXABLE_ACCOUNTED_CR, CR.currency_code TRX_HDR_CURRENCY, CBA.bank_account_name BANK_ACCOUNT_NAME, CR.reversal_comments REVERSAL_COMMENTS, CR.reversal_date REVERSAL_DATE, to_date(null) APPLICATION_DATE, CR.deposit_date PAYMENT_DEPOSIT_DATE, null APPLIED_TO_TRX_HDR_TABLE, to_number(null) APPLIED_TO_TRX_HDR_ID, null APPLIED_TO_TRX_HDR_NUMBER_C, null APPLIED_TO_TRX_HDR_NUMBER_DISP, null APPLIED_TO_TRX_HDR_CURRENCY, to_date(null) APPLIED_TO_TRX_HDR_DATE, null APPLIED_TO_TRX_LINE_TYPE_NAME, null APPLIED_TO_TRX_LINE_TYPE, to_number(null) APPLIED_TO_TRX_LINE_NUMBER, RCVTRX.name AR_ACTIVITY_NAME, null DISTRIBUTION_SET_NAME FROM gl_je_headers JEH, gl_import_references R, ar_distributions_all ARD, ar_cash_receipt_history_all CRH, ar_cash_receipts_all CR, ce_bank_accounts CBA, ce_bank_acct_uses APBA, hz_cust_accounts CUST_ACCT, hz_parties PARTY, hz_cust_site_uses_all SU, ar_receivables_trx_all RCVTRX, ar_vat_tax_all VAT, ar_receipt_methods RM, ar_system_parameters_all SYS, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT WHERE CRH.cash_receipt_id = CR.cash_receipt_id AND nvl(CRH.org_id,-99) = nvl(CR.org_id,-99) AND ARD.source_table = 'CRH' AND ARD.source_id = CRH.cash_receipt_history_id AND nvl(ARD.org_id,-99) = nvl(CRH.org_id,-99) AND GLCT.conversion_type(+) = ARD.currency_conversion_type AND CR.doc_sequence_id = SEQ.doc_sequence_id (+) AND CR.receipt_method_id = RM.receipt_method_id AND ARD.tax_code_id = VAT.vat_tax_id(+) AND nvl(ARD.org_id,-99) = nvl(VAT.org_id(+),-99) AND CR.pay_from_customer = CUST_ACCT.cust_account_id(+) AND CUST_ACCT.party_id = PARTY.party_id(+) AND CR.customer_site_use_id = SU.site_use_id(+) AND nvl(CR.org_id,-99) = nvl(SU.org_id(+),-99) AND CR.remit_bank_acct_use_id = APBA.bank_acct_use_id(+) AND nvl(CR.org_id,-99) = nvl(APBA.org_id(+),-99) AND CR.receivables_trx_id = RCVTRX.receivables_trx_id(+) AND nvl(CR.org_id,-99) = nvl(RCVTRX.org_id(+),-99) AND ARD.line_id = to_number(R.reference_3) AND R.je_header_id = JEH.je_header_id AND JEH.je_category in ('Misc Receipts','Trade Receipts', 'Rate Adjustments', 'Cross Currency') AND APBA.bank_account_id = CBA.bank_account_id (+) AND nvl(SYS.org_id,-99) = nvl(CRH.org_id,-99) AND SYS.set_of_books_id = JEH.ledger_id AND SYS.accounting_method = 'ACCRUAL' UNION ALL /*------------------------------------------------------------------------+ Trade Receipt Applications(Non-CASH AE Lines) JE Categories: Trade Receipts, Rate Adjustments, Cross Currency +------------------------------------------------------------------------*/ select /*+ ORDERED */ JEH.je_header_id JE_HEADER_ID , R.je_line_num JE_LINE_NUM , 222 APPLICATION_ID , CR.set_of_books_id SET_OF_BOOKS_ID , CR.org_id ORG_ID , CR.type TRX_CLASS , ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'PAYMENT_CATEGORY_TYPE', CR.type) TRX_CLASS_NAME , CR.receipt_method_id TRX_TYPE_N , RM.name TRX_TYPE_NAME , CR.receipt_number TRX_NUMBER_DISPLAYED , CR.receipt_number TRX_NUMBER_C , CR.receipt_date TRX_DATE , CR.comments COMMENTS , CR.doc_sequence_id DOC_SEQUENCE_ID , SEQ.name DOC_SEQUENCE_NAME , CR.doc_sequence_value DOC_SEQUENCE_VALUE, 'CR' TRX_HDR_TABLE, CR.cash_receipt_id TRX_HDR_ID, ARD.source_type ACCT_LINE_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'DISTRIBUTION_SOURCE_TYPE', ARD.source_type ) ACCT_LINE_TYPE_NAME, ARD.code_combination_id CODE_COMBINATION_ID, ARD.currency_code CURRENCY_CODE, TO_NUMBER( decode(r.reference_9, 'TRADE_APP',decode(sign(ra.amount_applied),1,NULL,abs(ra.amount_applied)), 'CCURR_APP',decode(sign(ra.amount_applied),1,NULL,abs(ra.amount_applied)), 'TRADE_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.unearned_discount_taken)), decode(nvl(ra.unearned_discount_taken,0), 0,decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.earned_discount_taken)), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.earned_discount_taken)), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.unearned_discount_taken)), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.earned_discount_taken)), decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.unearned_discount_taken))))), decode( gjl.entered_dr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.unearned_discount_taken), abs(ra.earned_discount_taken)))))), 'CCURR_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.unearned_discount_taken)), decode(nvl(ra.unearned_discount_taken,0), 0,decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.earned_discount_taken)), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.earned_discount_taken)), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.unearned_discount_taken)), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.earned_discount_taken)), decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.unearned_discount_taken))))), decode( gjl.entered_dr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.unearned_discount_taken), abs(ra.earned_discount_taken)))))), ARD.amount_dr)) ENTERED_DR, TO_NUMBER( decode(r.reference_9, 'TRADE_APP',decode(sign(ra.amount_applied),1,abs(ra.amount_applied),NULL), 'CCURR_APP',decode(sign(ra.amount_applied),1,abs(ra.amount_applied), NULL), 'TRADE_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.unearned_discount_taken), NULL), decode( nvl(ra.unearned_discount_taken,0), 0, decode(sign(ra.earned_discount_taken),1,abs(ra.earned_discount_taken), NULL), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,abs(ra.earned_discount_taken), NULL), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.unearned_discount_taken), NULL), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,abs(ra.earned_discount_taken),NULL), decode(sign(ra.unearned_discount_taken),1,abs(ra.unearned_discount_taken),NULL)))), decode( gjl.entered_cr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.earned_discount_taken), abs(ra.unearned_discount_taken)))))), 'CCURR_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.unearned_discount_taken), NULL), decode( nvl(ra.unearned_discount_taken,0), 0, decode(sign(ra.earned_discount_taken),1,abs(ra.earned_discount_taken), NULL), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,abs(ra.earned_discount_taken), NULL), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.unearned_discount_taken), NULL), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,abs(ra.earned_discount_taken),NULL), decode(sign(ra.unearned_discount_taken),1,abs(ra.unearned_discount_taken),NULL)))), decode( gjl.entered_cr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.earned_discount_taken), abs(ra.unearned_discount_taken)))))), ARD.amount_cr)) ENTERED_CR, TO_NUMBER( decode(r.reference_9, 'TRADE_APP',decode(sign(ra.amount_applied),1,NULL,abs(ra.acctd_amount_applied_to)), 'CCURR_APP',decode(sign(ra.amount_applied),1,NULL,abs(ra.acctd_amount_applied_to)), 'TRADE_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.acctd_unearned_discount_taken)), decode( nvl(ra.unearned_discount_taken,0), 0, decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.acctd_earned_discount_taken)), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.acctd_earned_discount_taken)), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,NULL, abs(ra.acctd_unearned_discount_taken)), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.acctd_earned_discount_taken)), decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.acctd_unearned_discount_taken))))), decode( gjl.entered_dr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.acctd_unearned_discount_taken), abs(ra.acctd_earned_discount_taken)))))), 'CCURR_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.acctd_unearned_discount_taken)), decode( nvl(ra.unearned_discount_taken,0), 0, decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.acctd_earned_discount_taken)), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.acctd_earned_discount_taken)), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,NULL, abs(ra.acctd_unearned_discount_taken)), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,NULL,abs(ra.acctd_earned_discount_taken)), decode(sign(ra.unearned_discount_taken),1,NULL,abs(ra.acctd_unearned_discount_taken))))), decode( gjl.entered_dr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.acctd_unearned_discount_taken), abs(ra.acctd_earned_discount_taken)))))), ARD.acctd_amount_dr)) ACCOUNTED_DR, TO_NUMBER( decode(r.reference_9, 'TRADE_APP',decode(sign(ra.amount_applied),1,abs(ra.acctd_amount_applied_to),NULL), 'CCURR_APP',decode(sign(ra.amount_applied),1,abs(ra.acctd_amount_applied_to), NULL), 'TRADE_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.acctd_unearned_discount_taken), NULL), decode( nvl(ra.unearned_discount_taken,0), 0, decode(sign(ra.earned_discount_taken),1,abs(ra.acctd_earned_discount_taken), NULL), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,abs(ra.acctd_earned_discount_taken), NULL), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.acctd_unearned_discount_taken), NULL), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,abs(ra.acctd_earned_discount_taken),NULL), decode(sign(ra.unearned_discount_taken),1,abs(ra.acctd_unearned_discount_taken),NULL)))), decode( gjl.entered_cr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.acctd_earned_discount_taken), abs(ra.acctd_unearned_discount_taken)))))), 'CCURR_DISCAPP',decode( nvl(ra.earned_discount_taken,0), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.acctd_unearned_discount_taken), NULL), decode( nvl(ra.unearned_discount_taken,0), 0, decode(sign(ra.earned_discount_taken),1,abs(ra.acctd_earned_discount_taken), NULL), decode((sign(ra.earned_discount_taken)-sign(ra.unearned_discount_taken)), 0,decode(sign(abs(ra.earned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.earned_discount_taken),1,abs(ra.acctd_earned_discount_taken), NULL), decode(sign(abs(ra.unearned_discount_taken) - nvl(gjl.entered_cr, gjl.entered_dr)), 0,decode(sign(ra.unearned_discount_taken),1,abs(ra.acctd_unearned_discount_taken), NULL), decode( arpt_sql_func_util.is_max_rowid(r.rowid), 'Y', decode(sign(ra.earned_discount_taken),1,abs(ra.acctd_earned_discount_taken),NULL), decode(sign(ra.unearned_discount_taken),1,abs(ra.acctd_unearned_discount_taken),NULL)))), decode( gjl.entered_cr, NULL, NULL, decode( sign(ra.earned_discount_taken), 1, abs(ra.acctd_earned_discount_taken), abs(ra.acctd_unearned_discount_taken)))))), ARD.acctd_amount_cr)) ACCOUNTED_CR , ARD.currency_conversion_date CURRENCY_CONVERSION_DATE, ARD.currency_conversion_type CURRENCY_CONVERSION_TYPE, GLCT.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, ARD.currency_conversion_rate CURRENCY_CONVERSION_RATE, 'C' THIRD_PARTY_TYPE, decode(ra.status ,'APP',ARD.third_party_id,CR.pay_from_customer) THIRD_PARTY_ID, decode(ra.status ,'APP',CUST_ACCT.account_number,CUST_ACCT1.account_number ) THIRD_PARTY_NUMBER, decode(ra.status ,'APP',substrb(party.party_name,1,50) ,substrb(party1.party_name,1,50)) THIRD_PARTY_NAME, decode(ra.status ,'APP',ARD.third_party_sub_id,CR.customer_site_use_id) THIRD_PARTY_SUB_ID, decode(ra.status ,'APP',SU.location,SU1.location) THIRD_PARTY_SUB_NAME, RA.gl_date ACCOUNTING_DATE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'YES/NO', decode(RA.posting_control_id,-3, 'N','Y') ) GL_TRANSFER_STATUS_NAME, decode(RA.posting_control_id, -3,'N','Y') GL_TRANSFER_STATUS, ARD.source_table SOURCE_TABLE, ARD.source_id SOURCE_ID, ARD.line_id AEL_ID, 'ARD' AEL_TABLE, /* Line Ref: If applied to trans 'Applied To ' + ' ' + Applied To Trans Class + ' ' + Applied To Trans Number else Trans Class + ' ' + Trans Number */ decode(RA.applied_customer_trx_id, null, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'PAYMENT_CATEGORY_TYPE', CR.type)||' '||CR.receipt_number, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'VIEW_ACCOUNTING', 'APPLIED_TO' ) ||' '||ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'INV/CM', CTT.type )||' '|| CT.trx_number) AE_LINE_REFERENCE, decode(RA.applied_customer_trx_id, null, rpad(CR.receipt_number, 30), rpad(CT.trx_number, 20)) AE_LINE_REFERENCE_INTERNAL, ARD.last_update_date LAST_UPDATE_DATE, ARD.last_updated_by LAST_UPDATED_BY, ARD.creation_date CREATION_DATE, ARD.created_by CREATED_BY, ARD.last_update_login LAST_UPDATE_LOGIN, /* The following columns are specific to AR Adjustments */ 'ACCRUAL' ACCOUNTING_BASIS, VAT.tax_code TAX_CODE, ARD.tax_code_id TAX_CODE_ID, ARD.tax_link_id TAX_LINK_ID, ARD.taxable_entered_dr TAXABLE_ENTERED_DR, ARD.taxable_entered_cr TAXABLE_ENTERED_CR, ARD.taxable_accounted_dr TAXABLE_ACCOUNTED_DR, ARD.taxable_accounted_cr TAXABLE_ACCOUNTED_CR, CR.currency_code TRX_HDR_CURRENCY, CBA.bank_account_name BANK_ACCOUNT_NAME, CR.reversal_comments REVERSAL_COMMENTS, CR.reversal_date REVERSAL_DATE, RA.apply_date APPLICATION_DATE, CR.deposit_date PAYMENT_DEPOSIT_DATE, 'CT' APPLIED_TO_TRX_HDR_TABLE, RA.applied_customer_trx_id APPLIED_TO_TRX_HDR_ID, CT.trx_number APPLIED_TO_TRX_HDR_NUMBER_C, CT.trx_number APPLIED_TO_TRX_HDR_NUMBER_DISP, CT.invoice_currency_code APPLIED_TO_TRX_HDR_CURRENCY, CT.trx_date APPLIED_TO_TRX_HDR_DATE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'STD_LINE_TYPE', CTL.line_type ) APPLIED_TO_TRX_LINE_TYPE_NAME, CTL.line_type APPLIED_TO_TRX_LINE_TYPE, CTL.line_number APPLIED_TO_TRX_LINE_NUMBER, RCVTRX.name AR_ACTIVITY_NAME, null DISTRIBUTION_SET_NAME FROM gl_je_headers JEH, gl_je_lines GJL, gl_import_references R, ar_distributions_all ARD, ar_receivable_applications_all RA, ar_cash_receipts_all CR, ce_bank_accounts CBA, ce_bank_acct_uses APBA, hz_cust_accounts CUST_ACCT, hz_parties PARTY, hz_cust_site_uses_all SU, hz_cust_accounts CUST_ACCT1, hz_parties PARTY1, hz_cust_site_uses_all SU1, ra_cust_trx_types_all CTT, ar_vat_tax_all VAT, ar_receivables_trx_all RCVTRX, ar_receipt_methods RM, ar_system_parameters_all SYS, ra_customer_trx_all CT, ra_customer_trx_lines_all CTL, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT WHERE RA.application_type = 'CASH' AND nvl(RA.postable, 'Y') = 'Y' AND RA.cash_receipt_id = CR.cash_receipt_id AND nvl(RA.org_id,-99) = nvl(CR.org_id,-99) AND ARD.source_table = 'RA' AND ARD.source_id = RA.receivable_application_id AND nvl(ARD.org_id,-99) = nvl(RA.org_id,-99) AND GLCT.conversion_type(+) = ARD.currency_conversion_type AND CR.doc_sequence_id = SEQ.doc_sequence_id (+) AND CR.receipt_method_id = RM.receipt_method_id AND ARD.tax_code_id = VAT.vat_tax_id(+) AND nvl(ARD.org_id,-99) = nvl(VAT.org_id(+),-99) AND ARD.third_party_id = CUST_ACCT.cust_account_id(+) AND CUST_ACCT.party_id = PARTY.party_id(+) AND ARD.third_party_sub_id = SU.site_use_id(+) AND nvl(ARD.org_id,-99) = nvl(SU.org_id(+),-99) AND CR.pay_from_customer = CUST_ACCT1.cust_account_id(+) AND CUST_ACCT1.party_id = PARTY1.party_id(+) AND CR.customer_site_use_id = SU1.site_use_id(+) AND nvl(CR.org_id,-99) = nvl(SU1.org_id(+),-99) AND CR.remit_bank_acct_use_id = APBA.bank_acct_use_id(+) AND nvl(CR.org_id,-99) = nvl(APBA.org_id(+),-99) AND CR.receivables_trx_id = RCVTRX.receivables_trx_id(+) AND nvl(CR.org_id,-99) = nvl(RCVTRX.org_id(+),-99) AND CT.cust_trx_type_id = CTT.cust_trx_type_id(+) AND nvl(CT.org_id, -99) = nvl(CTT.org_id(+), -99) AND RA.applied_customer_trx_id = CT.customer_trx_id(+) AND nvl(RA.org_id, -99) = nvl(CT.org_id(+), -99) AND RA.applied_customer_trx_line_id = CTL.customer_trx_line_id(+) AND nvl(RA.org_id, -99) = nvl(CTL.org_id(+), -99) AND ARD.line_id = to_number(R.reference_3) AND R.je_header_id = JEH.je_header_id AND GJL.je_header_id = R.je_header_id AND GJL.je_line_num = R.je_line_num AND JEH.je_category in ('Trade Receipts', 'Rate Adjustments', 'Cross Currency') AND APBA.bank_account_id = CBA.bank_account_id (+) AND nvl(SYS.org_id,-99) = nvl(CR.org_id,-99) AND SYS.set_of_books_id = JEH.ledger_id AND SYS.accounting_method = 'ACCRUAL' UNION ALL /*------------------------------------------------------------------------+ Misc Cash Distributions(Non-Cash and Tax AE Lines) JE Categories: Misc Receipts, Rate Adjustments +------------------------------------------------------------------------*/ select /*+ ORDERED */ JEH.je_header_id JE_HEADER_ID , R.je_line_num JE_LINE_NUM , 222 APPLICATION_ID , CR.set_of_books_id SET_OF_BOOKS_ID , CR.org_id ORG_ID , CR.type TRX_CLASS , ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'PAYMENT_CATEGORY_TYPE', CR.type) TRX_CLASS_NAME , CR.receipt_method_id TRX_TYPE_N , RM.name TRX_TYPE_NAME , CR.receipt_number TRX_NUMBER_DISPLAYED , CR.receipt_number TRX_NUMBER_C , CR.receipt_date TRX_DATE , CR.comments COMMENTS , CR.doc_sequence_id DOC_SEQUENCE_ID , SEQ.name DOC_SEQUENCE_NAME , CR.doc_sequence_value DOC_SEQUENCE_VALUE, 'CR' TRX_HDR_TABLE, CR.cash_receipt_id TRX_HDR_ID, ARD.source_type ACCT_LINE_TYPE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'DISTRIBUTION_SOURCE_TYPE', ARD.source_type ) ACCT_LINE_TYPE_NAME, ARD.code_combination_id CODE_COMBINATION_ID, ARD.currency_code CURRENCY_CODE, ARD.amount_dr ENTERED_DR, ARD.amount_cr ENTERED_CR, ARD.acctd_amount_dr ACCOUNTED_DR, ARD.acctd_amount_cr ACCOUNTED_CR, ARD.currency_conversion_date CURRENCY_CONVERSION_DATE, ARD.currency_conversion_type CURRENCY_CONVERSION_TYPE, GLCT.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, ARD.currency_conversion_rate CURRENCY_CONVERSION_RATE, 'C' THIRD_PARTY_TYPE, ARD.third_party_id THIRD_PARTY_ID, null THIRD_PARTY_NUMBER, null THIRD_PARTY_NAME, ARD.third_party_sub_id THIRD_PARTY_SUB_ID, null THIRD_PARTY_SUB_NAME, MCD.gl_date ACCOUNTING_DATE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'YES/NO', decode(MCD.posting_control_id,-3, 'N','Y') ) GL_TRANSFER_STATUS_NAME, decode(MCD.posting_control_id,-3,'N','Y') GL_TRANSFER_STATUS, ARD.source_table SOURCE_TABLE, ARD.source_id SOURCE_ID, ARD.line_id AEL_ID, 'ARD' AEL_TABLE, /* Line Ref: Trans Class + ' ' + Trans Number */ ARPT_SQL_FUNC_UTIL.get_lookup_meaning( 'PAYMENT_CATEGORY_TYPE', CR.type)||' '||CR.receipt_number AE_LINE_REFERENCE, rpad(CR.receipt_number, 30) AE_LINE_REFERENCE_INTERNAL, ARD.last_update_date LAST_UPDATE_DATE, ARD.last_updated_by LAST_UPDATED_BY, ARD.creation_date CREATION_DATE, ARD.created_by CREATED_BY, ARD.last_update_login LAST_UPDATE_LOGIN, /* The following columns are specific to AR Adjustments */ 'ACCRUAL' ACCOUNTING_BASIS, VAT.tax_code TAX_CODE, ARD.tax_code_id TAX_CODE_ID, ARD.tax_link_id TAX_LINK_ID, ARD.taxable_entered_dr TAXABLE_ENTERED_DR, ARD.taxable_entered_cr TAXABLE_ENTERED_CR, ARD.taxable_accounted_dr TAXABLE_ACCOUNTED_DR, ARD.taxable_accounted_cr TAXABLE_ACCOUNTED_CR, CR.currency_code TRX_HDR_CURRENCY, CBA.bank_account_name BANK_ACCOUNT_NAME, CR.reversal_comments REVERSAL_COMMENTS, CR.reversal_date REVERSAL_DATE, to_date(null) APPLICATION_DATE, CR.deposit_date PAYMENT_DEPOSIT_DATE, null APPLIED_TO_TRX_HDR_TABLE, to_number(null) APPLIED_TO_TRX_HDR_ID, null APPLIED_TO_TRX_HDR_NUMBER_C, null APPLIED_TO_TRX_HDR_NUMBER_DISP, null APPLIED_TO_TRX_HDR_CURRENCY, to_date(null) APPLIED_TO_TRX_HDR_DATE, null APPLIED_TO_TRX_LINE_TYPE_NAME, null APPLIED_TO_TRX_LINE_TYPE, to_number(null) APPLIED_TO_TRX_LINE_NUMBER, RCVTRX.name AR_ACTIVITY_NAME, DSET.distribution_set_name DISTRIBUTION_SET_NAME FROM gl_je_headers JEH, gl_import_references R, ar_distributions_all ARD, ar_misc_cash_distributions_all MCD, ar_cash_receipts_all CR, ce_bank_accounts CBA, ce_bank_acct_uses APBA, ar_receivables_trx_all RCVTRX, ar_distribution_sets_all DSET, ar_vat_tax_all VAT, ar_receipt_methods RM, ar_system_parameters_all SYS, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT WHERE MCD.cash_receipt_id = CR.cash_receipt_id AND nvl(MCD.org_id,-99) = nvl(CR.org_id,-99) AND ARD.source_table = 'MCD' AND ARD.source_id = MCD.misc_cash_distribution_id AND nvl(ARD.org_id,-99) = nvl(MCD.org_id,-99) AND ARD.tax_code_id = VAT.vat_tax_id(+) AND nvl(ARD.org_id,-99) = nvl(VAT.org_id(+),-99) AND GLCT.conversion_type(+) = ARD.currency_conversion_type AND CR.doc_sequence_id = SEQ.doc_sequence_id (+) AND CR.receipt_method_id = RM.receipt_method_id AND CR.remit_bank_acct_use_id = APBA.bank_acct_use_id(+) AND nvl(CR.org_id,-99) = nvl(APBA.org_id(+),-99) AND CR.receivables_trx_id = RCVTRX.receivables_trx_id(+) AND nvl(CR.org_id,-99) = nvl(RCVTRX.org_id(+),-99) AND CR.distribution_set_id = DSET.distribution_set_id(+) AND nvl(CR.org_id,-99) = nvl(DSET.org_id(+),-99) AND ARD.line_id = to_number(R.reference_3) AND R.je_header_id = JEH.je_header_id AND JEH.je_category in ('Misc Receipts', 'Rate Adjustments') AND APBA.bank_account_id = CBA.bank_account_id (+) AND nvl(SYS.org_id,-99) = nvl(CR.org_id,-99) AND SYS.set_of_books_id = JEH.ledger_id AND SYS.accounting_method = 'ACCRUAL'

View Text - HTML Formatted

SELECT /*+ ORDERED */ JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CR.ORG_ID ORG_ID
, CR.TYPE TRX_CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'PAYMENT_CATEGORY_TYPE'
, CR.TYPE) TRX_CLASS_NAME
, CR.RECEIPT_METHOD_ID TRX_TYPE_N
, RM.NAME TRX_TYPE_NAME
, CR.RECEIPT_NUMBER TRX_NUMBER_DISPLAYED
, CR.RECEIPT_NUMBER TRX_NUMBER_C
, CR.RECEIPT_DATE TRX_DATE
, CR.COMMENTS COMMENTS
, CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CR' TRX_HDR_TABLE
, CR.CASH_RECEIPT_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'DISTRIBUTION_SOURCE_TYPE'
, ARD.SOURCE_TYPE ) ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, ARD.CURRENCY_CODE CURRENCY_CODE
, ARD.AMOUNT_DR ENTERED_DR
, ARD.AMOUNT_CR ENTERED_CR
, ARD.ACCTD_AMOUNT_DR ACCOUNTED_DR
, ARD.ACCTD_AMOUNT_CR ACCOUNTED_CR
, ARD.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARD.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, CR.PAY_FROM_CUSTOMER THIRD_PARTY_ID
, CUST_ACCT.ACCOUNT_NUMBER THIRD_PARTY_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) THIRD_PARTY_NAME
, CR.CUSTOMER_SITE_USE_ID THIRD_PARTY_SUB_ID
, SU.LOCATION THIRD_PARTY_SUB_NAME
, CRH.GL_DATE ACCOUNTING_DATE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'YES/NO'
, DECODE(CRH.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') ) GL_TRANSFER_STATUS_NAME
, DECODE(CRH.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, ARD.LINE_ID AEL_ID
, 'ARD' AEL_TABLE
, /* LINE REF: TRANS CLASS + ' ' + TRANS NUMBER */ ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'PAYMENT_CATEGORY_TYPE'
, CR.TYPE) ||' '|| CR.RECEIPT_NUMBER AE_LINE_REFERENCE
, RPAD(CR.RECEIPT_NUMBER
, 30) AE_LINE_REFERENCE_INTERNAL
, ARD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ARD.LAST_UPDATED_BY LAST_UPDATED_BY
, ARD.CREATION_DATE CREATION_DATE
, ARD.CREATED_BY CREATED_BY
, ARD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR RECEIPTS */ 'ACCRUAL' ACCOUNTING_BASIS
, VAT.TAX_CODE TAX_CODE
, ARD.TAX_CODE_ID TAX_CODE_ID
, ARD.TAX_LINK_ID TAX_LINK_ID
, ARD.TAXABLE_ENTERED_DR TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR TAXABLE_ACCOUNTED_CR
, CR.CURRENCY_CODE TRX_HDR_CURRENCY
, CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, CR.REVERSAL_COMMENTS REVERSAL_COMMENTS
, CR.REVERSAL_DATE REVERSAL_DATE
, TO_DATE(NULL) APPLICATION_DATE
, CR.DEPOSIT_DATE PAYMENT_DEPOSIT_DATE
, NULL APPLIED_TO_TRX_HDR_TABLE
, TO_NUMBER(NULL) APPLIED_TO_TRX_HDR_ID
, NULL APPLIED_TO_TRX_HDR_NUMBER_C
, NULL APPLIED_TO_TRX_HDR_NUMBER_DISP
, NULL APPLIED_TO_TRX_HDR_CURRENCY
, TO_DATE(NULL) APPLIED_TO_TRX_HDR_DATE
, NULL APPLIED_TO_TRX_LINE_TYPE_NAME
, NULL APPLIED_TO_TRX_LINE_TYPE
, TO_NUMBER(NULL) APPLIED_TO_TRX_LINE_NUMBER
, RCVTRX.NAME AR_ACTIVITY_NAME
, NULL DISTRIBUTION_SET_NAME
FROM GL_JE_HEADERS JEH
, GL_IMPORT_REFERENCES R
, AR_DISTRIBUTIONS_ALL ARD
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPTS_ALL CR
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES APBA
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL SU
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_VAT_TAX_ALL VAT
, AR_RECEIPT_METHODS RM
, AR_SYSTEM_PARAMETERS_ALL SYS
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(CRH.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99)
AND ARD.SOURCE_TABLE = 'CRH'
AND ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND NVL(ARD.ORG_ID
, -99) = NVL(CRH.ORG_ID
, -99)
AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+)
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND ARD.TAX_CODE_ID = VAT.VAT_TAX_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(VAT.ORG_ID(+)
, -99)
AND CR.PAY_FROM_CUSTOMER = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND CR.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(SU.ORG_ID(+)
, -99)
AND CR.REMIT_BANK_ACCT_USE_ID = APBA.BANK_ACCT_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(APBA.ORG_ID(+)
, -99)
AND CR.RECEIVABLES_TRX_ID = RCVTRX.RECEIVABLES_TRX_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(RCVTRX.ORG_ID(+)
, -99)
AND ARD.LINE_ID = TO_NUMBER(R.REFERENCE_3)
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('MISC RECEIPTS'
, 'TRADE RECEIPTS'
, 'RATE ADJUSTMENTS'
, 'CROSS CURRENCY')
AND APBA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)
AND NVL(SYS.ORG_ID
, -99) = NVL(CRH.ORG_ID
, -99)
AND SYS.SET_OF_BOOKS_ID = JEH.LEDGER_ID
AND SYS.ACCOUNTING_METHOD = 'ACCRUAL' UNION ALL /*------------------------------------------------------------------------+ TRADE RECEIPT APPLICATIONS(NON-CASH AE LINES) JE CATEGORIES: TRADE RECEIPTS
, RATE ADJUSTMENTS
, CROSS CURRENCY +------------------------------------------------------------------------*/ SELECT /*+ ORDERED */ JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CR.ORG_ID ORG_ID
, CR.TYPE TRX_CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'PAYMENT_CATEGORY_TYPE'
, CR.TYPE) TRX_CLASS_NAME
, CR.RECEIPT_METHOD_ID TRX_TYPE_N
, RM.NAME TRX_TYPE_NAME
, CR.RECEIPT_NUMBER TRX_NUMBER_DISPLAYED
, CR.RECEIPT_NUMBER TRX_NUMBER_C
, CR.RECEIPT_DATE TRX_DATE
, CR.COMMENTS COMMENTS
, CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CR' TRX_HDR_TABLE
, CR.CASH_RECEIPT_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'DISTRIBUTION_SOURCE_TYPE'
, ARD.SOURCE_TYPE ) ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, ARD.CURRENCY_CODE CURRENCY_CODE
, TO_NUMBER( DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, NULL
, ABS(RA.AMOUNT_APPLIED))
, 'CCURR_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, NULL
, ABS(RA.AMOUNT_APPLIED))
, 'TRADE_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.UNEARNED_DISCOUNT_TAKEN))
, DECODE(NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.EARNED_DISCOUNT_TAKEN))
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.UNEARNED_DISCOUNT_TAKEN))
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)))))
, DECODE( GJL.ENTERED_DR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, ABS(RA.EARNED_DISCOUNT_TAKEN))))))
, 'CCURR_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.UNEARNED_DISCOUNT_TAKEN))
, DECODE(NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.EARNED_DISCOUNT_TAKEN))
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.UNEARNED_DISCOUNT_TAKEN))
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)))))
, DECODE( GJL.ENTERED_DR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, ABS(RA.EARNED_DISCOUNT_TAKEN))))))
, ARD.AMOUNT_DR)) ENTERED_DR
, TO_NUMBER( DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, ABS(RA.AMOUNT_APPLIED)
, NULL)
, 'CCURR_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, ABS(RA.AMOUNT_APPLIED)
, NULL)
, 'TRADE_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, NULL))))
, DECODE( GJL.ENTERED_CR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, ABS(RA.UNEARNED_DISCOUNT_TAKEN))))))
, 'CCURR_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.UNEARNED_DISCOUNT_TAKEN)
, NULL))))
, DECODE( GJL.ENTERED_CR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.EARNED_DISCOUNT_TAKEN)
, ABS(RA.UNEARNED_DISCOUNT_TAKEN))))))
, ARD.AMOUNT_CR)) ENTERED_CR
, TO_NUMBER( DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, NULL
, ABS(RA.ACCTD_AMOUNT_APPLIED_TO))
, 'CCURR_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, NULL
, ABS(RA.ACCTD_AMOUNT_APPLIED_TO))
, 'TRADE_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN))
, DECODE( NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN))
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)))))
, DECODE( GJL.ENTERED_DR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))))))
, 'CCURR_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN))
, DECODE( NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN))
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, NULL
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)))))
, DECODE( GJL.ENTERED_DR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN))))))
, ARD.ACCTD_AMOUNT_DR)) ACCOUNTED_DR
, TO_NUMBER( DECODE(R.REFERENCE_9
, 'TRADE_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, ABS(RA.ACCTD_AMOUNT_APPLIED_TO)
, NULL)
, 'CCURR_APP'
, DECODE(SIGN(RA.AMOUNT_APPLIED)
, 1
, ABS(RA.ACCTD_AMOUNT_APPLIED_TO)
, NULL)
, 'TRADE_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, NULL))))
, DECODE( GJL.ENTERED_CR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN))))))
, 'CCURR_DISCAPP'
, DECODE( NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE((SIGN(RA.EARNED_DISCOUNT_TAKEN)-SIGN(RA.UNEARNED_DISCOUNT_TAKEN))
, 0
, DECODE(SIGN(ABS(RA.EARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(ABS(RA.UNEARNED_DISCOUNT_TAKEN) - NVL(GJL.ENTERED_CR
, GJL.ENTERED_DR))
, 0
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE( ARPT_SQL_FUNC_UTIL.IS_MAX_ROWID(R.ROWID)
, 'Y'
, DECODE(SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, NULL)
, DECODE(SIGN(RA.UNEARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN)
, NULL))))
, DECODE( GJL.ENTERED_CR
, NULL
, NULL
, DECODE( SIGN(RA.EARNED_DISCOUNT_TAKEN)
, 1
, ABS(RA.ACCTD_EARNED_DISCOUNT_TAKEN)
, ABS(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN))))))
, ARD.ACCTD_AMOUNT_CR)) ACCOUNTED_CR
, ARD.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARD.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, DECODE(RA.STATUS
, 'APP'
, ARD.THIRD_PARTY_ID
, CR.PAY_FROM_CUSTOMER) THIRD_PARTY_ID
, DECODE(RA.STATUS
, 'APP'
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT1.ACCOUNT_NUMBER ) THIRD_PARTY_NUMBER
, DECODE(RA.STATUS
, 'APP'
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, SUBSTRB(PARTY1.PARTY_NAME
, 1
, 50)) THIRD_PARTY_NAME
, DECODE(RA.STATUS
, 'APP'
, ARD.THIRD_PARTY_SUB_ID
, CR.CUSTOMER_SITE_USE_ID) THIRD_PARTY_SUB_ID
, DECODE(RA.STATUS
, 'APP'
, SU.LOCATION
, SU1.LOCATION) THIRD_PARTY_SUB_NAME
, RA.GL_DATE ACCOUNTING_DATE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'YES/NO'
, DECODE(RA.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') ) GL_TRANSFER_STATUS_NAME
, DECODE(RA.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, ARD.LINE_ID AEL_ID
, 'ARD' AEL_TABLE
, /* LINE REF: IF APPLIED TO TRANS 'APPLIED TO ' + ' ' + APPLIED TO TRANS CLASS + ' ' + APPLIED TO TRANS NUMBER ELSE TRANS CLASS + ' ' + TRANS NUMBER */ DECODE(RA.APPLIED_CUSTOMER_TRX_ID
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'PAYMENT_CATEGORY_TYPE'
, CR.TYPE)||' '||CR.RECEIPT_NUMBER
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'VIEW_ACCOUNTING'
, 'APPLIED_TO' ) ||' '||ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'INV/CM'
, CTT.TYPE )||' '|| CT.TRX_NUMBER) AE_LINE_REFERENCE
, DECODE(RA.APPLIED_CUSTOMER_TRX_ID
, NULL
, RPAD(CR.RECEIPT_NUMBER
, 30)
, RPAD(CT.TRX_NUMBER
, 20)) AE_LINE_REFERENCE_INTERNAL
, ARD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ARD.LAST_UPDATED_BY LAST_UPDATED_BY
, ARD.CREATION_DATE CREATION_DATE
, ARD.CREATED_BY CREATED_BY
, ARD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR ADJUSTMENTS */ 'ACCRUAL' ACCOUNTING_BASIS
, VAT.TAX_CODE TAX_CODE
, ARD.TAX_CODE_ID TAX_CODE_ID
, ARD.TAX_LINK_ID TAX_LINK_ID
, ARD.TAXABLE_ENTERED_DR TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR TAXABLE_ACCOUNTED_CR
, CR.CURRENCY_CODE TRX_HDR_CURRENCY
, CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, CR.REVERSAL_COMMENTS REVERSAL_COMMENTS
, CR.REVERSAL_DATE REVERSAL_DATE
, RA.APPLY_DATE APPLICATION_DATE
, CR.DEPOSIT_DATE PAYMENT_DEPOSIT_DATE
, 'CT' APPLIED_TO_TRX_HDR_TABLE
, RA.APPLIED_CUSTOMER_TRX_ID APPLIED_TO_TRX_HDR_ID
, CT.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_C
, CT.TRX_NUMBER APPLIED_TO_TRX_HDR_NUMBER_DISP
, CT.INVOICE_CURRENCY_CODE APPLIED_TO_TRX_HDR_CURRENCY
, CT.TRX_DATE APPLIED_TO_TRX_HDR_DATE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'STD_LINE_TYPE'
, CTL.LINE_TYPE ) APPLIED_TO_TRX_LINE_TYPE_NAME
, CTL.LINE_TYPE APPLIED_TO_TRX_LINE_TYPE
, CTL.LINE_NUMBER APPLIED_TO_TRX_LINE_NUMBER
, RCVTRX.NAME AR_ACTIVITY_NAME
, NULL DISTRIBUTION_SET_NAME
FROM GL_JE_HEADERS JEH
, GL_JE_LINES GJL
, GL_IMPORT_REFERENCES R
, AR_DISTRIBUTIONS_ALL ARD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES APBA
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT1
, HZ_PARTIES PARTY1
, HZ_CUST_SITE_USES_ALL SU1
, RA_CUST_TRX_TYPES_ALL CTT
, AR_VAT_TAX_ALL VAT
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_RECEIPT_METHODS RM
, AR_SYSTEM_PARAMETERS_ALL SYS
, RA_CUSTOMER_TRX_ALL CT
, RA_CUSTOMER_TRX_LINES_ALL CTL
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE RA.APPLICATION_TYPE = 'CASH'
AND NVL(RA.POSTABLE
, 'Y') = 'Y'
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(RA.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99)
AND ARD.SOURCE_TABLE = 'RA'
AND ARD.SOURCE_ID = RA.RECEIVABLE_APPLICATION_ID
AND NVL(ARD.ORG_ID
, -99) = NVL(RA.ORG_ID
, -99)
AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+)
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND ARD.TAX_CODE_ID = VAT.VAT_TAX_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(VAT.ORG_ID(+)
, -99)
AND ARD.THIRD_PARTY_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND ARD.THIRD_PARTY_SUB_ID = SU.SITE_USE_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(SU.ORG_ID(+)
, -99)
AND CR.PAY_FROM_CUSTOMER = CUST_ACCT1.CUST_ACCOUNT_ID(+)
AND CUST_ACCT1.PARTY_ID = PARTY1.PARTY_ID(+)
AND CR.CUSTOMER_SITE_USE_ID = SU1.SITE_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(SU1.ORG_ID(+)
, -99)
AND CR.REMIT_BANK_ACCT_USE_ID = APBA.BANK_ACCT_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(APBA.ORG_ID(+)
, -99)
AND CR.RECEIVABLES_TRX_ID = RCVTRX.RECEIVABLES_TRX_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(RCVTRX.ORG_ID(+)
, -99)
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND NVL(CT.ORG_ID
, -99) = NVL(CTT.ORG_ID(+)
, -99)
AND RA.APPLIED_CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND NVL(RA.ORG_ID
, -99) = NVL(CT.ORG_ID(+)
, -99)
AND RA.APPLIED_CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID(+)
AND NVL(RA.ORG_ID
, -99) = NVL(CTL.ORG_ID(+)
, -99)
AND ARD.LINE_ID = TO_NUMBER(R.REFERENCE_3)
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND GJL.JE_HEADER_ID = R.JE_HEADER_ID
AND GJL.JE_LINE_NUM = R.JE_LINE_NUM
AND JEH.JE_CATEGORY IN ('TRADE RECEIPTS'
, 'RATE ADJUSTMENTS'
, 'CROSS CURRENCY')
AND APBA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)
AND NVL(SYS.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99)
AND SYS.SET_OF_BOOKS_ID = JEH.LEDGER_ID
AND SYS.ACCOUNTING_METHOD = 'ACCRUAL' UNION ALL /*------------------------------------------------------------------------+ MISC CASH DISTRIBUTIONS(NON-CASH
AND TAX AE LINES) JE CATEGORIES: MISC RECEIPTS
, RATE ADJUSTMENTS +------------------------------------------------------------------------*/ SELECT /*+ ORDERED */ JEH.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 222 APPLICATION_ID
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CR.ORG_ID ORG_ID
, CR.TYPE TRX_CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'PAYMENT_CATEGORY_TYPE'
, CR.TYPE) TRX_CLASS_NAME
, CR.RECEIPT_METHOD_ID TRX_TYPE_N
, RM.NAME TRX_TYPE_NAME
, CR.RECEIPT_NUMBER TRX_NUMBER_DISPLAYED
, CR.RECEIPT_NUMBER TRX_NUMBER_C
, CR.RECEIPT_DATE TRX_DATE
, CR.COMMENTS COMMENTS
, CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, SEQ.NAME DOC_SEQUENCE_NAME
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, 'CR' TRX_HDR_TABLE
, CR.CASH_RECEIPT_ID TRX_HDR_ID
, ARD.SOURCE_TYPE ACCT_LINE_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'DISTRIBUTION_SOURCE_TYPE'
, ARD.SOURCE_TYPE ) ACCT_LINE_TYPE_NAME
, ARD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, ARD.CURRENCY_CODE CURRENCY_CODE
, ARD.AMOUNT_DR ENTERED_DR
, ARD.AMOUNT_CR ENTERED_CR
, ARD.ACCTD_AMOUNT_DR ACCOUNTED_DR
, ARD.ACCTD_AMOUNT_CR ACCOUNTED_CR
, ARD.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, ARD.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, ARD.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, ARD.THIRD_PARTY_ID THIRD_PARTY_ID
, NULL THIRD_PARTY_NUMBER
, NULL THIRD_PARTY_NAME
, ARD.THIRD_PARTY_SUB_ID THIRD_PARTY_SUB_ID
, NULL THIRD_PARTY_SUB_NAME
, MCD.GL_DATE ACCOUNTING_DATE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'YES/NO'
, DECODE(MCD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') ) GL_TRANSFER_STATUS_NAME
, DECODE(MCD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, ARD.SOURCE_TABLE SOURCE_TABLE
, ARD.SOURCE_ID SOURCE_ID
, ARD.LINE_ID AEL_ID
, 'ARD' AEL_TABLE
, /* LINE REF: TRANS CLASS + ' ' + TRANS NUMBER */ ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'PAYMENT_CATEGORY_TYPE'
, CR.TYPE)||' '||CR.RECEIPT_NUMBER AE_LINE_REFERENCE
, RPAD(CR.RECEIPT_NUMBER
, 30) AE_LINE_REFERENCE_INTERNAL
, ARD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ARD.LAST_UPDATED_BY LAST_UPDATED_BY
, ARD.CREATION_DATE CREATION_DATE
, ARD.CREATED_BY CREATED_BY
, ARD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR ADJUSTMENTS */ 'ACCRUAL' ACCOUNTING_BASIS
, VAT.TAX_CODE TAX_CODE
, ARD.TAX_CODE_ID TAX_CODE_ID
, ARD.TAX_LINK_ID TAX_LINK_ID
, ARD.TAXABLE_ENTERED_DR TAXABLE_ENTERED_DR
, ARD.TAXABLE_ENTERED_CR TAXABLE_ENTERED_CR
, ARD.TAXABLE_ACCOUNTED_DR TAXABLE_ACCOUNTED_DR
, ARD.TAXABLE_ACCOUNTED_CR TAXABLE_ACCOUNTED_CR
, CR.CURRENCY_CODE TRX_HDR_CURRENCY
, CBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, CR.REVERSAL_COMMENTS REVERSAL_COMMENTS
, CR.REVERSAL_DATE REVERSAL_DATE
, TO_DATE(NULL) APPLICATION_DATE
, CR.DEPOSIT_DATE PAYMENT_DEPOSIT_DATE
, NULL APPLIED_TO_TRX_HDR_TABLE
, TO_NUMBER(NULL) APPLIED_TO_TRX_HDR_ID
, NULL APPLIED_TO_TRX_HDR_NUMBER_C
, NULL APPLIED_TO_TRX_HDR_NUMBER_DISP
, NULL APPLIED_TO_TRX_HDR_CURRENCY
, TO_DATE(NULL) APPLIED_TO_TRX_HDR_DATE
, NULL APPLIED_TO_TRX_LINE_TYPE_NAME
, NULL APPLIED_TO_TRX_LINE_TYPE
, TO_NUMBER(NULL) APPLIED_TO_TRX_LINE_NUMBER
, RCVTRX.NAME AR_ACTIVITY_NAME
, DSET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET_NAME
FROM GL_JE_HEADERS JEH
, GL_IMPORT_REFERENCES R
, AR_DISTRIBUTIONS_ALL ARD
, AR_MISC_CASH_DISTRIBUTIONS_ALL MCD
, AR_CASH_RECEIPTS_ALL CR
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES APBA
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_DISTRIBUTION_SETS_ALL DSET
, AR_VAT_TAX_ALL VAT
, AR_RECEIPT_METHODS RM
, AR_SYSTEM_PARAMETERS_ALL SYS
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE MCD.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(MCD.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99)
AND ARD.SOURCE_TABLE = 'MCD'
AND ARD.SOURCE_ID = MCD.MISC_CASH_DISTRIBUTION_ID
AND NVL(ARD.ORG_ID
, -99) = NVL(MCD.ORG_ID
, -99)
AND ARD.TAX_CODE_ID = VAT.VAT_TAX_ID(+)
AND NVL(ARD.ORG_ID
, -99) = NVL(VAT.ORG_ID(+)
, -99)
AND GLCT.CONVERSION_TYPE(+) = ARD.CURRENCY_CONVERSION_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+)
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND CR.REMIT_BANK_ACCT_USE_ID = APBA.BANK_ACCT_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(APBA.ORG_ID(+)
, -99)
AND CR.RECEIVABLES_TRX_ID = RCVTRX.RECEIVABLES_TRX_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(RCVTRX.ORG_ID(+)
, -99)
AND CR.DISTRIBUTION_SET_ID = DSET.DISTRIBUTION_SET_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(DSET.ORG_ID(+)
, -99)
AND ARD.LINE_ID = TO_NUMBER(R.REFERENCE_3)
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('MISC RECEIPTS'
, 'RATE ADJUSTMENTS')
AND APBA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)
AND NVL(SYS.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99)
AND SYS.SET_OF_BOOKS_ID = JEH.LEDGER_ID
AND SYS.ACCOUNTING_METHOD = 'ACCRUAL'