VIEW: APPS.AR_AEL_GL_CB_REC_V

Source

View Text - Preformatted

SELECT /*+ORDERED */ /* $Header: araegcbr.sql 120.5 2005/09/23 10:35:34 sgnagara noship $ */ 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 , L1.meaning 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, L3.meaning 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, L2.meaning GL_TRANSFER_STATUS_NAME, decode(CRH.postable_flag,'N','Y','N') 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 */ L1.meaning||' '||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 */ 'CASH' ACCOUNTING_BASIS, null 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, /* TBD */ 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_cash_receipt_history_all CRH, ar_distributions_all ARD, ar_cash_receipts_all CR, ce_bank_accounts CBA, ce_bank_acct_uses BA, hz_cust_accounts CUST_ACCT, hz_parties PARTY, hz_cust_site_uses_all SU, ar_receivables_trx_all RCVTRX, ar_receipt_methods RM, ar_system_parameters_all SYS, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT, ar_lookups L1 , ar_lookups L2 , ar_lookups L3 WHERE L1.lookup_type = 'PAYMENT_CATEGORY_TYPE' AND L1.lookup_code = CR.type AND L2.lookup_type = 'YES/NO' AND L2.lookup_code = decode(CRH.postable_flag,'N','Y','N') AND L3.lookup_type = 'DISTRIBUTION_SOURCE_TYPE' AND L3.lookup_code = ARD.source_type AND 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 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 = BA.bank_acct_use_id(+) AND nvl(CR.org_id,-99) = nvl(BA.org_id(+),-99) AND BA.bank_account_id = CBA.bank_account_id(+) AND CR.receivables_trx_id = RCVTRX.receivables_trx_id(+) AND nvl(CR.org_id,-99) = nvl(RCVTRX.org_id(+),-99) AND nvl(SYS.org_id,-99) = nvl(CRH.org_id, -99) AND SYS.set_of_books_id = jeh.ledger_id AND SYS.accounting_method = 'CASH' AND CRH.cash_receipt_history_id = to_number(R.reference_3) AND R.reference_10 = 'AR_CASH_RECEIPT_HISTORY' AND R.je_header_id = JEH.je_header_id AND JEH.je_category in ('Trade Receipts', 'Rate Adjustments', 'Misc Receipts', 'Cross Currency') UNION ALL /*------------------------------------------------------------------------+ Accounting Method: CASH Cash Basis Distributions for Receivable Applications(APP) - Source can be CTLGD or ADJ - Might need separate union for CTLGD and ADJ +------------------------------------------------------------------------*/ 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 , L1.meaning 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, CBD.type ACCT_LINE_TYPE, L6.meaning ACCT_LINE_TYPE_NAME, CBD.code_combination_id CODE_COMBINATION_ID, CBD.currency_code CURRENCY_CODE, to_number(decode(sign(CBD.amount), -1, -CBD.amount, null)) ENTERED_DR, to_number(decode(sign(CBD.amount), -1, null, -CBD.amount)) ENTERED_CR, to_number(decode(sign(CBD.acctd_amount), -1, -CBD.acctd_amount, null)) ACCOUNTED_DR, to_number(decode(sign(CBD.acctd_amount), -1, null, -CBD.acctd_amount)) ACCOUNTED_CR, CR.exchange_date CURRENCY_CONVERSION_DATE, CR.exchange_rate_type CURRENCY_CONVERSION_TYPE, GLCT.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, CR.exchange_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, RA.gl_date ACCOUNTING_DATE, L2.meaning GL_TRANSFER_STATUS_NAME, decode(nvl(RA.postable,'Y'),'Y','N','Y') GL_TRANSFER_STATUS, decode(CBD.source, 'GL', 'CTLGD', 'ADJ', 'ADJ', CBD.source) SOURCE_TABLE, CBD.source_id SOURCE_ID, CBD.cash_basis_distribution_id AEL_ID, 'CBD' 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, L1.meaning||' '||CR.receipt_number, L5.meaning||' '||L4.meaning||' '|| 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, CBD.last_update_date LAST_UPDATE_DATE, CBD.last_updated_by LAST_UPDATED_BY, CBD.creation_date CREATION_DATE, CBD.created_by CREATED_BY, CBD.last_update_login LAST_UPDATE_LOGIN, /* The following columns are specific to AR Adjustments */ 'CASH' ACCOUNTING_BASIS, null TAX_CODE, to_number(null) TAX_CODE_ID, to_number(null) TAX_LINK_ID, to_number(null) TAXABLE_ENTERED_DR, to_number(null) TAXABLE_ENTERED_CR, to_number(null) TAXABLE_ACCOUNTED_DR, to_number(null) 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, L3.meaning 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_import_references R, ar_cash_basis_dists_all CBD, ar_receivable_applications_all RA, ar_cash_receipts_all CR, ce_bank_accounts CBA, ce_bank_acct_uses BA, hz_cust_accounts CUST_ACCT, hz_parties PARTY, hz_cust_site_uses_all SU, 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, ra_cust_trx_types_all CTT, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT, ar_lookups L1 , ar_lookups L2 , ar_lookups L3 , ar_lookups L4 , ar_lookups L5 , ar_lookups L6 WHERE L1.lookup_type = 'PAYMENT_CATEGORY_TYPE' AND L1.lookup_code = CR.type AND L2.lookup_type = 'YES/NO' AND L2.lookup_code = decode(nvl(RA.postable,'Y'),'Y','N','Y') AND L3.lookup_type(+) = 'STD_LINE_TYPE' AND L3.lookup_code(+) = CTL.line_type AND L4.lookup_type(+) = 'INV/CM' AND L4.lookup_code(+) = CTT.type AND L5.lookup_type = 'VIEW_ACCOUNTING' AND L5.lookup_code = 'APPLIED_TO' AND L6.lookup_type = 'STD_LINE_TYPE' AND L6.lookup_code = CBD.type AND RA.application_type = 'CASH' AND /* Receivable Applications that have been APPlied */ RA.status = 'APP' 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 GLCT.conversion_type(+) = CR.exchange_rate_type AND CR.doc_sequence_id = SEQ.doc_sequence_id (+) AND CR.receipt_method_id = RM.receipt_method_id 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 = BA.bank_acct_use_id(+) AND nvl(CR.org_id,-99) = nvl(BA.org_id(+),-99) AND BA.bank_account_id = CBA.bank_account_id(+) 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 RA.receivable_application_id = CBD.receivable_application_id AND nvl(SYS.org_id,-99) = nvl(CBD.org_id, -99) AND SYS.set_of_books_id = jeh.ledger_id AND SYS.accounting_method = 'CASH' AND CBD.source in ('GL', 'ADJ') AND CBD.cash_basis_distribution_id = to_number(R.reference_3) AND R.reference_10 = 'AR_CASH_BASIS_DISTRIBUTIONS' AND R.je_header_id = JEH.je_header_id AND JEH.je_category in ('Trade Receipts', 'Rate Adjustments', 'Cross Currency') UNION ALL /*------------------------------------------------------------------------+ Accounting Method: CASH Trade Receipt Receivable Applications(Non-APP) lines +------------------------------------------------------------------------*/ 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 , L1.meaning 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, null ACCT_LINE_TYPE, null ACCT_LINE_TYPE_NAME, RA.code_combination_id CODE_COMBINATION_ID, CR.currency_code CURRENCY_CODE, to_number(decode(sign(RA.amount_applied), -1, -RA.amount_applied, null)) ENTERED_DR, to_number(decode(sign(RA.amount_applied), -1, null, -RA.amount_applied)) ENTERED_CR, to_number(decode(sign(RA.acctd_amount_applied_from), -1, -RA.acctd_amount_applied_from, null)) ACCOUNTED_DR, to_number(decode(sign(RA.acctd_amount_applied_from), -1, null, -RA.acctd_amount_applied_from)) ACCOUNTED_CR, CR.exchange_date CURRENCY_CONVERSION_DATE, CR.exchange_rate_type CURRENCY_CONVERSION_TYPE, GLCT.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, CR.exchange_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, RA.gl_date ACCOUNTING_DATE, L2.meaning GL_TRANSFER_STATUS_NAME, decode(nvl(RA.postable,'Y'),'Y','N','Y') GL_TRANSFER_STATUS, 'CR' SOURCE_TABLE, CR.cash_receipt_id SOURCE_ID, RA.receivable_application_id AEL_ID, 'RA' 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, L1.meaning||' '||CR.receipt_number, L5.meaning||' '||L4.meaning||' '|| 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, RA.last_update_date LAST_UPDATE_DATE, RA.last_updated_by LAST_UPDATED_BY, RA.creation_date CREATION_DATE, RA.created_by CREATED_BY, RA.last_update_login LAST_UPDATE_LOGIN, /* The following columns are specific to AR Adjustments */ 'CASH' ACCOUNTING_BASIS, null TAX_CODE, to_number(null) TAX_CODE_ID, to_number(null) TAX_LINK_ID, to_number(null) TAXABLE_ENTERED_DR, to_number(null) TAXABLE_ENTERED_CR, to_number(null) TAXABLE_ACCOUNTED_DR, to_number(null) 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, L3.meaning 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_import_references R, ar_receivable_applications_all RA, ar_cash_receipts_all CR, ce_bank_accounts CBA, ce_bank_acct_uses BA, hz_cust_accounts CUST_ACCT, hz_parties PARTY, hz_cust_site_uses_all SU, 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, ra_cust_trx_types_all CTT, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT, ar_lookups L1 , ar_lookups L2 , ar_lookups L3 , ar_lookups L4 , ar_lookups L5 WHERE L1.lookup_type = 'PAYMENT_CATEGORY_TYPE' AND L1.lookup_code = CR.type AND L2.lookup_type = 'YES/NO' AND L2.lookup_code = decode(nvl(RA.postable,'Y'),'Y','N','Y') AND L3.lookup_type(+) = 'STD_LINE_TYPE' AND L3.lookup_code(+) = CTL.line_type AND L4.lookup_type(+) = 'INV/CM' AND L4.lookup_code(+) = CTT.type AND L5.lookup_type = 'VIEW_ACCOUNTING' AND L5.lookup_code = 'APPLIED_TO' AND RA.application_type = 'CASH' AND RA.status <> 'APP' 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 GLCT.conversion_type(+) = CR.exchange_rate_type AND CR.doc_sequence_id = SEQ.doc_sequence_id (+) AND CR.receipt_method_id = RM.receipt_method_id 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 = BA.bank_acct_use_id(+) AND nvl(CR.org_id,-99) = nvl(BA.org_id(+),-99) AND BA.bank_account_id = CBA.bank_account_id(+) 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 nvl(SYS.org_id,-99) = nvl(RA.org_id, -99) AND SYS.set_of_books_id = jeh.ledger_id AND SYS.accounting_method = 'CASH' 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 RA.receivable_application_id = to_number(R.reference_3) AND R.reference_10 = 'AR_RECEIVABLE_APPLICATIONS' AND R.je_header_id = JEH.je_header_id AND JEH.je_category in ('Trade Receipts', 'Rate Adjustments', 'Cross Currency') UNION ALL /*------------------------------------------------------------------------+ Accounting Method: CASH Misc Cash Distributions +------------------------------------------------------------------------*/ 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 , L1.meaning 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, null ACCT_LINE_TYPE, null ACCT_LINE_TYPE_NAME, MCD.code_combination_id CODE_COMBINATION_ID, CR.currency_code CURRENCY_CODE, to_number(decode(sign(MCD.amount), -1,-MCD.amount,null)) ENTERED_DR, to_number(decode(sign(MCD.amount), -1,null,MCD.amount)) ENTERED_CR, to_number(decode(sign(MCD.amount), -1,-MCD.acctd_amount,null)) ACCOUNTED_DR, to_number(decode(sign(MCD.amount), -1,null,MCD.acctd_amount)) ACCOUNTED_CR, CR.exchange_date CURRENCY_CONVERSION_DATE, CR.exchange_rate_type CURRENCY_CONVERSION_TYPE, GLCT.user_conversion_type CURRENCY_USER_CONVERSION_TYPE, CR.exchange_rate CURRENCY_CONVERSION_RATE, 'C' THIRD_PARTY_TYPE, to_number(null) THIRD_PARTY_ID, null THIRD_PARTY_NUMBER, null THIRD_PARTY_NAME, to_number(null) THIRD_PARTY_SUB_ID, null THIRD_PARTY_SUB_NAME, MCD.gl_date ACCOUNTING_DATE, L2.meaning GL_TRANSFER_STATUS_NAME, decode(MCD.posting_control_id,-3,'N','Y') GL_TRANSFER_STATUS, 'CRD' SOURCE_TABLE, CR.cash_receipt_id SOURCE_ID, MCD.misc_cash_distribution_id AEL_ID, 'MCD' AEL_TABLE, 'AE_LINE_REF - TBD' AE_LINE_REFERENCE, /* Line Ref: Trans Class + ' ' + Trans Number */ L1.meaning||' '||CR.receipt_number AE_LINE_REFERENCE, MCD.last_update_date LAST_UPDATE_DATE, MCD.last_updated_by LAST_UPDATED_BY, MCD.creation_date CREATION_DATE, MCD.created_by CREATED_BY, MCD.last_update_login LAST_UPDATE_LOGIN, /* The following columns are specific to AR Adjustments */ 'CASH' ACCOUNTING_BASIS, null TAX_CODE, to_number(null) TAX_CODE_ID, to_number(null) TAX_LINK_ID, to_number(null) TAXABLE_ENTERED_DR, to_number(null) TAXABLE_ENTERED_CR, to_number(null) TAXABLE_ACCOUNTED_DR, to_number(null) 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, /* TBD */ 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_misc_cash_distributions_all MCD, ar_cash_receipts_all CR, ce_bank_accounts CBA, ce_bank_acct_uses BA, ar_receivables_trx_all RCVTRX, ar_distribution_sets_all DSET, ar_receipt_methods RM, ar_system_parameters_all SYS, fnd_document_sequences SEQ, gl_daily_conversion_types GLCT, ar_lookups L1 , ar_lookups L2 WHERE L1.lookup_type = 'PAYMENT_CATEGORY_TYPE' AND L1.lookup_code = CR.type AND L2.lookup_type = 'YES/NO' AND L2.lookup_code = decode(MCD.posting_control_id,-3,'N','Y') AND MCD.cash_receipt_id = CR.cash_receipt_id AND nvl(MCD.org_id,-99) = nvl(CR.org_id,-99) AND GLCT.conversion_type(+) = CR.exchange_rate_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 = BA.bank_acct_use_id(+) AND nvl(CR.org_id,-99) = nvl(BA.org_id(+),-99) AND BA.bank_account_id = CBA.bank_account_id(+) 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 nvl(SYS.org_id,-99) = nvl(MCD.org_id, -99) AND SYS.set_of_books_id = jeh.ledger_id AND SYS.accounting_method = 'CASH' AND MCD.misc_cash_distribution_id = to_number(R.reference_3) AND R.reference_10 = 'AR_MISC_CASH_DISTRIBUTIONS' AND R.je_header_id = JEH.je_header_id AND JEH.je_category = 'Misc Receipts'

View Text - HTML Formatted

SELECT /*+ORDERED */ /* $HEADER: ARAEGCBR.SQL 120.5 2005/09/23 10:35:34 SGNAGARA NOSHIP $ */ 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
, L1.MEANING 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
, L3.MEANING 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
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(CRH.POSTABLE_FLAG
, 'N'
, 'Y'
, 'N') 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 */ L1.MEANING||' '||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 */ 'CASH' ACCOUNTING_BASIS
, NULL 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
, /* TBD */ 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_CASH_RECEIPT_HISTORY_ALL CRH
, AR_DISTRIBUTIONS_ALL ARD
, AR_CASH_RECEIPTS_ALL CR
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL SU
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_RECEIPT_METHODS RM
, AR_SYSTEM_PARAMETERS_ALL SYS
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
WHERE L1.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND L1.LOOKUP_CODE = CR.TYPE
AND L2.LOOKUP_TYPE = 'YES/NO'
AND L2.LOOKUP_CODE = DECODE(CRH.POSTABLE_FLAG
, 'N'
, 'Y'
, 'N')
AND L3.LOOKUP_TYPE = 'DISTRIBUTION_SOURCE_TYPE'
AND L3.LOOKUP_CODE = ARD.SOURCE_TYPE
AND 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 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 = BA.BANK_ACCT_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(BA.ORG_ID(+)
, -99)
AND BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
AND CR.RECEIVABLES_TRX_ID = RCVTRX.RECEIVABLES_TRX_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(RCVTRX.ORG_ID(+)
, -99)
AND NVL(SYS.ORG_ID
, -99) = NVL(CRH.ORG_ID
, -99)
AND SYS.SET_OF_BOOKS_ID = JEH.LEDGER_ID
AND SYS.ACCOUNTING_METHOD = 'CASH'
AND CRH.CASH_RECEIPT_HISTORY_ID = TO_NUMBER(R.REFERENCE_3)
AND R.REFERENCE_10 = 'AR_CASH_RECEIPT_HISTORY'
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('TRADE RECEIPTS'
, 'RATE ADJUSTMENTS'
, 'MISC RECEIPTS'
, 'CROSS CURRENCY') UNION ALL /*------------------------------------------------------------------------+ ACCOUNTING METHOD: CASH CASH BASIS DISTRIBUTIONS FOR RECEIVABLE APPLICATIONS(APP) - SOURCE CAN BE CTLGD OR ADJ - MIGHT NEED SEPARATE UNION FOR CTLGD
AND ADJ +------------------------------------------------------------------------*/ 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
, L1.MEANING 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
, CBD.TYPE ACCT_LINE_TYPE
, L6.MEANING ACCT_LINE_TYPE_NAME
, CBD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CBD.CURRENCY_CODE CURRENCY_CODE
, TO_NUMBER(DECODE(SIGN(CBD.AMOUNT)
, -1
, -CBD.AMOUNT
, NULL)) ENTERED_DR
, TO_NUMBER(DECODE(SIGN(CBD.AMOUNT)
, -1
, NULL
, -CBD.AMOUNT)) ENTERED_CR
, TO_NUMBER(DECODE(SIGN(CBD.ACCTD_AMOUNT)
, -1
, -CBD.ACCTD_AMOUNT
, NULL)) ACCOUNTED_DR
, TO_NUMBER(DECODE(SIGN(CBD.ACCTD_AMOUNT)
, -1
, NULL
, -CBD.ACCTD_AMOUNT)) ACCOUNTED_CR
, CR.EXCHANGE_DATE CURRENCY_CONVERSION_DATE
, CR.EXCHANGE_RATE_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, CR.EXCHANGE_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
, RA.GL_DATE ACCOUNTING_DATE
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y') GL_TRANSFER_STATUS
, DECODE(CBD.SOURCE
, 'GL'
, 'CTLGD'
, 'ADJ'
, 'ADJ'
, CBD.SOURCE) SOURCE_TABLE
, CBD.SOURCE_ID SOURCE_ID
, CBD.CASH_BASIS_DISTRIBUTION_ID AEL_ID
, 'CBD' 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
, L1.MEANING||' '||CR.RECEIPT_NUMBER
, L5.MEANING||' '||L4.MEANING||' '|| 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
, CBD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CBD.LAST_UPDATED_BY LAST_UPDATED_BY
, CBD.CREATION_DATE CREATION_DATE
, CBD.CREATED_BY CREATED_BY
, CBD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR ADJUSTMENTS */ 'CASH' ACCOUNTING_BASIS
, NULL TAX_CODE
, TO_NUMBER(NULL) TAX_CODE_ID
, TO_NUMBER(NULL) TAX_LINK_ID
, TO_NUMBER(NULL) TAXABLE_ENTERED_DR
, TO_NUMBER(NULL) TAXABLE_ENTERED_CR
, TO_NUMBER(NULL) TAXABLE_ACCOUNTED_DR
, TO_NUMBER(NULL) 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
, L3.MEANING 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_IMPORT_REFERENCES R
, AR_CASH_BASIS_DISTS_ALL CBD
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL SU
, 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
, RA_CUST_TRX_TYPES_ALL CTT
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
, AR_LOOKUPS L4
, AR_LOOKUPS L5
, AR_LOOKUPS L6
WHERE L1.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND L1.LOOKUP_CODE = CR.TYPE
AND L2.LOOKUP_TYPE = 'YES/NO'
AND L2.LOOKUP_CODE = DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y')
AND L3.LOOKUP_TYPE(+) = 'STD_LINE_TYPE'
AND L3.LOOKUP_CODE(+) = CTL.LINE_TYPE
AND L4.LOOKUP_TYPE(+) = 'INV/CM'
AND L4.LOOKUP_CODE(+) = CTT.TYPE
AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L5.LOOKUP_CODE = 'APPLIED_TO'
AND L6.LOOKUP_TYPE = 'STD_LINE_TYPE'
AND L6.LOOKUP_CODE = CBD.TYPE
AND RA.APPLICATION_TYPE = 'CASH'
AND /* RECEIVABLE APPLICATIONS THAT HAVE BEEN APPLIED */ RA.STATUS = 'APP'
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 GLCT.CONVERSION_TYPE(+) = CR.EXCHANGE_RATE_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+)
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
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 = BA.BANK_ACCT_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(BA.ORG_ID(+)
, -99)
AND BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
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 RA.RECEIVABLE_APPLICATION_ID = CBD.RECEIVABLE_APPLICATION_ID
AND NVL(SYS.ORG_ID
, -99) = NVL(CBD.ORG_ID
, -99)
AND SYS.SET_OF_BOOKS_ID = JEH.LEDGER_ID
AND SYS.ACCOUNTING_METHOD = 'CASH'
AND CBD.SOURCE IN ('GL'
, 'ADJ')
AND CBD.CASH_BASIS_DISTRIBUTION_ID = TO_NUMBER(R.REFERENCE_3)
AND R.REFERENCE_10 = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('TRADE RECEIPTS'
, 'RATE ADJUSTMENTS'
, 'CROSS CURRENCY') UNION ALL /*------------------------------------------------------------------------+ ACCOUNTING METHOD: CASH TRADE RECEIPT RECEIVABLE APPLICATIONS(NON-APP) LINES +------------------------------------------------------------------------*/ 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
, L1.MEANING 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
, NULL ACCT_LINE_TYPE
, NULL ACCT_LINE_TYPE_NAME
, RA.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CR.CURRENCY_CODE CURRENCY_CODE
, TO_NUMBER(DECODE(SIGN(RA.AMOUNT_APPLIED)
, -1
, -RA.AMOUNT_APPLIED
, NULL)) ENTERED_DR
, TO_NUMBER(DECODE(SIGN(RA.AMOUNT_APPLIED)
, -1
, NULL
, -RA.AMOUNT_APPLIED)) ENTERED_CR
, TO_NUMBER(DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM)
, -1
, -RA.ACCTD_AMOUNT_APPLIED_FROM
, NULL)) ACCOUNTED_DR
, TO_NUMBER(DECODE(SIGN(RA.ACCTD_AMOUNT_APPLIED_FROM)
, -1
, NULL
, -RA.ACCTD_AMOUNT_APPLIED_FROM)) ACCOUNTED_CR
, CR.EXCHANGE_DATE CURRENCY_CONVERSION_DATE
, CR.EXCHANGE_RATE_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, CR.EXCHANGE_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
, RA.GL_DATE ACCOUNTING_DATE
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y') GL_TRANSFER_STATUS
, 'CR' SOURCE_TABLE
, CR.CASH_RECEIPT_ID SOURCE_ID
, RA.RECEIVABLE_APPLICATION_ID AEL_ID
, 'RA' 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
, L1.MEANING||' '||CR.RECEIPT_NUMBER
, L5.MEANING||' '||L4.MEANING||' '|| 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
, RA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RA.LAST_UPDATED_BY LAST_UPDATED_BY
, RA.CREATION_DATE CREATION_DATE
, RA.CREATED_BY CREATED_BY
, RA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR ADJUSTMENTS */ 'CASH' ACCOUNTING_BASIS
, NULL TAX_CODE
, TO_NUMBER(NULL) TAX_CODE_ID
, TO_NUMBER(NULL) TAX_LINK_ID
, TO_NUMBER(NULL) TAXABLE_ENTERED_DR
, TO_NUMBER(NULL) TAXABLE_ENTERED_CR
, TO_NUMBER(NULL) TAXABLE_ACCOUNTED_DR
, TO_NUMBER(NULL) 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
, L3.MEANING 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_IMPORT_REFERENCES R
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES_ALL SU
, 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
, RA_CUST_TRX_TYPES_ALL CTT
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AR_LOOKUPS L1
, AR_LOOKUPS L2
, AR_LOOKUPS L3
, AR_LOOKUPS L4
, AR_LOOKUPS L5
WHERE L1.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND L1.LOOKUP_CODE = CR.TYPE
AND L2.LOOKUP_TYPE = 'YES/NO'
AND L2.LOOKUP_CODE = DECODE(NVL(RA.POSTABLE
, 'Y')
, 'Y'
, 'N'
, 'Y')
AND L3.LOOKUP_TYPE(+) = 'STD_LINE_TYPE'
AND L3.LOOKUP_CODE(+) = CTL.LINE_TYPE
AND L4.LOOKUP_TYPE(+) = 'INV/CM'
AND L4.LOOKUP_CODE(+) = CTT.TYPE
AND L5.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L5.LOOKUP_CODE = 'APPLIED_TO'
AND RA.APPLICATION_TYPE = 'CASH'
AND RA.STATUS <> 'APP'
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 GLCT.CONVERSION_TYPE(+) = CR.EXCHANGE_RATE_TYPE
AND CR.DOC_SEQUENCE_ID = SEQ.DOC_SEQUENCE_ID (+)
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
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 = BA.BANK_ACCT_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(BA.ORG_ID(+)
, -99)
AND BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
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 NVL(SYS.ORG_ID
, -99) = NVL(RA.ORG_ID
, -99)
AND SYS.SET_OF_BOOKS_ID = JEH.LEDGER_ID
AND SYS.ACCOUNTING_METHOD = 'CASH'
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 RA.RECEIVABLE_APPLICATION_ID = TO_NUMBER(R.REFERENCE_3)
AND R.REFERENCE_10 = 'AR_RECEIVABLE_APPLICATIONS'
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY IN ('TRADE RECEIPTS'
, 'RATE ADJUSTMENTS'
, 'CROSS CURRENCY') UNION ALL /*------------------------------------------------------------------------+ ACCOUNTING METHOD: CASH MISC CASH DISTRIBUTIONS +------------------------------------------------------------------------*/ 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
, L1.MEANING 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
, NULL ACCT_LINE_TYPE
, NULL ACCT_LINE_TYPE_NAME
, MCD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CR.CURRENCY_CODE CURRENCY_CODE
, TO_NUMBER(DECODE(SIGN(MCD.AMOUNT)
, -1
, -MCD.AMOUNT
, NULL)) ENTERED_DR
, TO_NUMBER(DECODE(SIGN(MCD.AMOUNT)
, -1
, NULL
, MCD.AMOUNT)) ENTERED_CR
, TO_NUMBER(DECODE(SIGN(MCD.AMOUNT)
, -1
, -MCD.ACCTD_AMOUNT
, NULL)) ACCOUNTED_DR
, TO_NUMBER(DECODE(SIGN(MCD.AMOUNT)
, -1
, NULL
, MCD.ACCTD_AMOUNT)) ACCOUNTED_CR
, CR.EXCHANGE_DATE CURRENCY_CONVERSION_DATE
, CR.EXCHANGE_RATE_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, CR.EXCHANGE_RATE CURRENCY_CONVERSION_RATE
, 'C' THIRD_PARTY_TYPE
, TO_NUMBER(NULL) THIRD_PARTY_ID
, NULL THIRD_PARTY_NUMBER
, NULL THIRD_PARTY_NAME
, TO_NUMBER(NULL) THIRD_PARTY_SUB_ID
, NULL THIRD_PARTY_SUB_NAME
, MCD.GL_DATE ACCOUNTING_DATE
, L2.MEANING GL_TRANSFER_STATUS_NAME
, DECODE(MCD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y') GL_TRANSFER_STATUS
, 'CRD' SOURCE_TABLE
, CR.CASH_RECEIPT_ID SOURCE_ID
, MCD.MISC_CASH_DISTRIBUTION_ID AEL_ID
, 'MCD' AEL_TABLE
, 'AE_LINE_REF - TBD' AE_LINE_REFERENCE
, /* LINE REF: TRANS CLASS + ' ' + TRANS NUMBER */ L1.MEANING||' '||CR.RECEIPT_NUMBER AE_LINE_REFERENCE
, MCD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MCD.LAST_UPDATED_BY LAST_UPDATED_BY
, MCD.CREATION_DATE CREATION_DATE
, MCD.CREATED_BY CREATED_BY
, MCD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, /* THE FOLLOWING COLUMNS ARE SPECIFIC TO AR ADJUSTMENTS */ 'CASH' ACCOUNTING_BASIS
, NULL TAX_CODE
, TO_NUMBER(NULL) TAX_CODE_ID
, TO_NUMBER(NULL) TAX_LINK_ID
, TO_NUMBER(NULL) TAXABLE_ENTERED_DR
, TO_NUMBER(NULL) TAXABLE_ENTERED_CR
, TO_NUMBER(NULL) TAXABLE_ACCOUNTED_DR
, TO_NUMBER(NULL) 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
, /* TBD */ 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_MISC_CASH_DISTRIBUTIONS_ALL MCD
, AR_CASH_RECEIPTS_ALL CR
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, AR_RECEIVABLES_TRX_ALL RCVTRX
, AR_DISTRIBUTION_SETS_ALL DSET
, AR_RECEIPT_METHODS RM
, AR_SYSTEM_PARAMETERS_ALL SYS
, FND_DOCUMENT_SEQUENCES SEQ
, GL_DAILY_CONVERSION_TYPES GLCT
, AR_LOOKUPS L1
, AR_LOOKUPS L2
WHERE L1.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND L1.LOOKUP_CODE = CR.TYPE
AND L2.LOOKUP_TYPE = 'YES/NO'
AND L2.LOOKUP_CODE = DECODE(MCD.POSTING_CONTROL_ID
, -3
, 'N'
, 'Y')
AND MCD.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND NVL(MCD.ORG_ID
, -99) = NVL(CR.ORG_ID
, -99)
AND GLCT.CONVERSION_TYPE(+) = CR.EXCHANGE_RATE_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 = BA.BANK_ACCT_USE_ID(+)
AND NVL(CR.ORG_ID
, -99) = NVL(BA.ORG_ID(+)
, -99)
AND BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
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 NVL(SYS.ORG_ID
, -99) = NVL(MCD.ORG_ID
, -99)
AND SYS.SET_OF_BOOKS_ID = JEH.LEDGER_ID
AND SYS.ACCOUNTING_METHOD = 'CASH'
AND MCD.MISC_CASH_DISTRIBUTION_ID = TO_NUMBER(R.REFERENCE_3)
AND R.REFERENCE_10 = 'AR_MISC_CASH_DISTRIBUTIONS'
AND R.JE_HEADER_ID = JEH.JE_HEADER_ID
AND JEH.JE_CATEGORY = 'MISC RECEIPTS'