AR Receipt Register
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Receivables
Description: Receivables Receipt Register
Provided Templates:
Default: Detail - Detail Listing with no Pivot Summarization
Pivot: Summary by Receipt Date - Summary by Balancing Segment, Receipt Currency, Receipt Date
Pivot: Summary by Receipt Status - Summary by Balancing Segment, Receipt Status
Pivot: Summary by Batch - Summary by Balancing Segment, ... more
Description: Receivables Receipt Register
Provided Templates:
Default: Detail - Detail Listing with no Pivot Summarization
Pivot: Summary by Receipt Date - Summary by Balancing Segment, Receipt Currency, Receipt Date
Pivot: Summary by Receipt Status - Summary by Balancing Segment, Receipt Status
Pivot: Summary by Batch - Summary by Balancing Segment, ... more
select x.ledger, x.ledger_currency, x.operating_unit, x.customer_name, x.customer_number, x.customer_name_alt, x.customer_location, x.customer_address, x.customer_country, x.customer_tax_number, x.collector, x.collector_account, x.collector_site, -- Receipt Main Details x.batch_name, x.batch_source, x.receipt_number, x.receipt_date, x.gl_date, x.receipt_type, x.receipt_method, x.receipt_status, x.receipt_history_status, x.receipt_currency, -- entered amounts x.receipt_amount, x.factor_discount_amount, x.applied_amount, x.on_account_amount, x.unidentified_amount, x.unapplied_amount, x.miscellaneous_amount, x.cash_claims_amount, x.writeoff_amount, x.refund_amount, x.chargeback_amount, x.short_term_debt_amount, x.earned_discount_taken, x.unearned_discount_taken, -- acctd amounts x.acctd_receipt_amount, x.acctd_factor_discount_amount, x.acctd_applied_amount, x.acctd_on_account_amount, x.acctd_unidentified_amount, x.acctd_unapplied_amount, x.acctd_miscellaneous_amount, x.acctd_cash_claims_amount, x.acctd_writeoff_amount, x.acctd_refund_amount, x.acctd_chargeback_amount, x.acctd_short_term_debt_amount, x.acctd_earned_discount_taken, x.acctd_unearned_discount_taken, x.acctd_gain_loss, -- Revaluation Columns &lp_reval_columns -- x.first_apply_date, x.last_apply_date, x.first_applied_in_full_date, x.first_apply_gl_date, x.last_apply_gl_date, x.first_applied_in_full_gl_date, -- Additional Receipt Info x.doc_sequence_name, x.doc_sequence_value, x.receipt_creation_date, x.receipt_created_by, x.receipt_last_updated_date, x.receipt_last_updated_by, x.issue_date, x.deposit_date, x.anticipated_clearing_date, x.misc_payment_source, x.reference_type, x.reference_number, x.customer_receipt_reference, x.tax_code, x.exchange_rate, x.exchange_date, x.exchange_rate_type, x.receipt_comments, x.application_notes, x.reversal_date, x.reversal_category, x.reversal_reason, x.reversal_comments, -- x.bank_name, x.bank_name_alt, x.bank_number, x.bank_branch_name, x.bank_branch_name_alt, x.branch_number, x.bank_branch_country, x.bank_account_name, x.bank_account_name_alt, x.bank_account_number, x.bank_account_currency, x.bank_account_description, -- Debit GL Account Info x.debit_account, x.debit_account_desc, x.balancing_segment, x.balancing_segment_desc, x.account_segment, x.account_segment_desc, x.debit_account_pivot_label, x.bal_seg_pivot_label, x.account_pivot_label, x.cust_name_pivot_label, x.cust_number_pivot_label, x.reporting_entity, x.cash_receipt_id from ( select /*+ push_pred(araa, araa2) */ gl.name ledger, gl.currency_code ledger_currency, haou.name operating_unit, substrb(hp.party_name,1,240) customer_name, hca.account_number customer_number, decode(hp.party_type,'ORGANIZATION',hp.organization_name_phonetic, null) customer_name_alt, hcsua.location customer_location, hz_format_pub.format_address(hps.location_id,null,null,' , ') customer_address, ftv2.territory_short_name customer_country, hp.jgzz_fiscal_code customer_tax_number, nvl(ac2.name,ac.name) collector, ac.name collector_account, ac2.name collector_site, -- Receipt Main Details decode(acrha.status, 'REVERSED', aba.name, aba.name) batch_name, absa.name batch_source, acra.receipt_number, acra.receipt_date, acrha.gl_date, xxen_util.meaning(acra.type,'PAYMENT_CATEGORY_TYPE',222) receipt_type, arm.name receipt_method, case when acra.status in ('REV', 'NSF', 'STOP') and acrha.status <> 'REVERSED' then case :p_mrc_flag when 'R' then (select case when sum(decode(araa2.status,'UNID',araa2.amount_applied,0)) != 0 then xxen_util.meaning('UNID','CHECK_STATUS',222) when sum(decode(araa2.status,'UNAPP',araa2.amount_applied,0)) != 0 then xxen_util.meaning('UNAPP','CHECK_STATUS',222) else xxen_util.meaning('APP','CHECK_STATUS',222) end from ar_receivable_apps_all_mrc_v araa2 where araa2.cash_receipt_id = acra.cash_receipt_id ) else (select case when sum(decode(araa2.status,'UNID',araa2.amount_applied,0)) != 0 then xxen_util.meaning('UNID','CHECK_STATUS',222) when sum(decode(araa2.status,'UNAPP',araa2.amount_applied,0)) != 0 then xxen_util.meaning('UNAPP','CHECK_STATUS',222) else xxen_util.meaning('APP','CHECK_STATUS',222) end from ar_receivable_applications_all araa2 where araa2.cash_receipt_id = acra.cash_receipt_id ) end else xxen_util.meaning(acra.status,'CHECK_STATUS',222) end receipt_status, xxen_util.meaning(acrha.status,'RECEIPT_CREATION_STATUS',222) receipt_history_status, acra.currency_code receipt_currency, decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id, 0 , (nvl(acrha3.amount,0))* -1), decode(acrha3.cash_receipt_history_id, acrha.cash_receipt_history_id, (acrha.amount), (acrha.amount -(nvl(acrha3.amount,0)))) ) + decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id, 0, (nvl(acrha3.factor_discount_amount,0))* -1), decode(acrha3.cash_receipt_history_id ,acrha.cash_receipt_history_id, nvl(acrha.factor_discount_amount,0), (nvl(acrha.factor_discount_amount,0) -(nvl(acrha3.factor_discount_amount,0)))) ) receipt_amount, decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id, 0, (nvl(acrha3.factor_discount_amount,0))* -1), decode(acrha3.cash_receipt_history_id ,acrha.cash_receipt_history_id, nvl(acrha.factor_discount_amount,0), (nvl(acrha.factor_discount_amount,0) -(nvl(acrha3.factor_discount_amount,0)))) ) factor_discount_amount, araa.applied_amount, araa.on_account_amount, araa.unidentified_amount, decode(araa.unapplied_amt,0,to_number(null),araa.unapplied_amt) unapplied_amount, case when acra.type = 'MISC' then decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id, 0 , (nvl(acrha3.amount,0))* -1), decode(acrha3.cash_receipt_history_id, acrha.cash_receipt_history_id, (acrha.amount), (acrha.amount -(nvl(acrha3.amount,0)))) ) + decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id, 0, (nvl(acrha3.factor_discount_amount,0))* -1), decode(acrha3.cash_receipt_history_id ,acrha.cash_receipt_history_id, nvl(acrha.factor_discount_amount,0), (nvl(acrha.factor_discount_amount,0) -(nvl(acrha3.factor_discount_amount,0)))) ) end miscellaneous_amount, araa.cash_claims_amount, araa.writeoff_amount, araa.refund_amount, araa.chargeback_amount, araa.short_term_debt_amount, araa.earned_discount_taken, araa.unearned_discount_taken, -- acctd decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id, 0, (nvl(acrha3.acctd_amount,0))* -1), decode(acrha3.cash_receipt_history_id, acrha.cash_receipt_history_id, (acrha.acctd_amount) , (acrha.acctd_amount -(nvl(acrha3.acctd_amount,0)))) ) + decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id,0, (nvl(acrha3.acctd_factor_discount_amount,0))* -1), decode(acrha3.cash_receipt_history_id ,acrha.cash_receipt_history_id, nvl(acrha.acctd_factor_discount_amount,0), (nvl(acrha.acctd_factor_discount_amount,0) -(nvl(acrha3.acctd_factor_discount_amount,0)))) ) acctd_receipt_amount, decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id,0, (nvl(acrha3.acctd_factor_discount_amount,0))* -1), decode(acrha3.cash_receipt_history_id ,acrha.cash_receipt_history_id, nvl(acrha.acctd_factor_discount_amount,0), (nvl(acrha.acctd_factor_discount_amount,0) -(nvl(acrha3.acctd_factor_discount_amount,0)))) ) acctd_factor_discount_amount, araa.acctd_applied_amount, araa.acctd_on_account_amount, araa.acctd_unidentified_amt acctd_unidentified_amount, decode(araa.acctd_unapplied_amt,0,to_number(null),araa.acctd_unapplied_amt) acctd_unapplied_amount, case when acra.type = 'MISC' then decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id, 0, (nvl(acrha3.acctd_amount,0))* -1), decode(acrha3.cash_receipt_history_id, acrha.cash_receipt_history_id, (acrha.acctd_amount) , (acrha.acctd_amount -(nvl(acrha3.acctd_amount,0)))) ) + decode(acrha.status, 'REVERSED',decode(acrha3.cash_receipt_history_id , acrha.cash_receipt_history_id,0, (nvl(acrha3.acctd_factor_discount_amount,0))* -1), decode(acrha3.cash_receipt_history_id ,acrha.cash_receipt_history_id, nvl(acrha.acctd_factor_discount_amount,0), (nvl(acrha.acctd_factor_discount_amount,0) -(nvl(acrha3.acctd_factor_discount_amount,0)))) ) end acctd_miscellaneous_amount, araa.acctd_cash_claims_amount, araa.acctd_writeoff_amount, araa.acctd_refund_amount, araa.acctd_chargeback_amount, araa.acctd_short_term_debt_amount, araa.acctd_earned_discount_taken, araa.acctd_unearned_discount_taken, araa.acctd_gain_loss, -- (select min(trunc(araa.apply_date)) from ar_receivable_applications_all araa where araa.org_id = acra.org_id and araa.cash_receipt_id = acra.cash_receipt_id and nvl(araa.confirmed_flag,'Y')='Y' and araa.status = 'APP' ) first_apply_date, (select max(trunc(araa.apply_date)) from ar_receivable_applications_all araa, ar_payment_schedules_all apsa where araa.org_id = acra.org_id and araa.cash_receipt_id = acra.cash_receipt_id and araa.applied_payment_schedule_id = apsa.payment_schedule_id and nvl(araa.confirmed_flag,'Y')='Y' and araa.status = 'APP' and decode(sign(araa.amount_applied),-1,-1,1) = decode(sign(apsa.amount_due_original),-1,-1,1) ) last_apply_date, araa2.first_applied_in_full_date, (select min(trunc(araa.gl_date)) from ar_receivable_applications_all araa where araa.org_id = acra.org_id and araa.cash_receipt_id = acra.cash_receipt_id and nvl(araa.confirmed_flag,'Y')='Y' and araa.status = 'APP' ) first_apply_gl_date, (select max(trunc(araa.gl_date)) from ar_receivable_applications_all araa, ar_payment_schedules_all apsa where araa.org_id = acra.org_id and araa.cash_receipt_id = acra.cash_receipt_id and araa.applied_payment_schedule_id = apsa.payment_schedule_id and nvl(araa.confirmed_flag,'Y')='Y' and araa.status = 'APP' and decode(sign(araa.amount_applied),-1,-1,1) = decode(sign(apsa.amount_due_original),-1,-1,1) ) last_apply_gl_date, araa2.first_applied_in_full_gl_date, -- Additional Receipt Info acra.reversal_date, xxen_util.meaning(acra.reversal_category,'REVERSAL_CATEGORY_TYPE',222) reversal_category, xxen_util.meaning(acra.reversal_reason_code,'CKAJST_REASON',222) reversal_reason, acra.reversal_comments, fds.name doc_sequence_name, acra.doc_sequence_value doc_sequence_value, acra.creation_date receipt_creation_date, xxen_util.user_name(acra.created_by) receipt_created_by, acra.last_update_date receipt_last_updated_date, xxen_util.user_name(acra.last_updated_by) receipt_last_updated_by, acra.issue_date, acra.deposit_date, acra.anticipated_clearing_date, acra.misc_payment_source, xxen_util.meaning(acra.reference_type,'CB_REFERENCE_TYPE',222) reference_type, case when acra.reference_type='REMITTANCE' then (select ab.name from ar_batches_all ab where acra.reference_id=ab.batch_id) when acra.reference_type='RECEIPT' then (select acra.receipt_number from ar_cash_receipts_all acra0 where acra.reference_id=acra0.cash_receipt_id) when acra.reference_type='PAYMENT_BATCH' then (select aisca.checkrun_name from ap_inv_selection_criteria_all aisca where acra.reference_id=aisca.checkrun_id) when acra.reference_type='PAYMENT' then (select to_char(aca.check_number) from ap_checks_all aca where acra.reference_id=aca.check_id) when acra.reference_type='CREDIT_MEMO' then (select rcta.trx_number from ra_customer_trx_all rcta where acra.reference_id=rcta.customer_trx_id) end reference_number, acra.customer_receipt_reference customer_receipt_reference, avta.tax_code, acra.exchange_rate, acra.exchange_date, acra.exchange_rate_type, acra.comments receipt_comments, acra.application_notes, cbbv.bank_name, cbbv.bank_name_alt, cbbv.bank_number, cbbv.bank_branch_name, cbbv.bank_branch_name_alt, cbbv.branch_number, ftv.territory_short_name bank_branch_country, cba.bank_account_name, cba.bank_account_name_alt, cba.masked_account_num bank_account_number, cba.currency_code bank_account_currency, cba.description bank_account_description, -- Debit GL Account Info nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'VALUE'),null) debit_account, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION'),null) debit_account_desc, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE'),null) balancing_segment, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION'),null) balancing_segment_desc, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE'),null) account_segment, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION'),null) account_segment_desc, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'VALUE') || ' - ' || fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION'),null) debit_account_pivot_label, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') || ' - ' || fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION'),null) bal_seg_pivot_label, nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') || ' - ' || fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION'),null) account_pivot_label, hp.party_name || ' - ' || hca.account_number cust_name_pivot_label, hca.account_number || ' - ' || hp.party_name cust_number_pivot_label, decode(:reporting_level,1000,gl.name,3000,haou.name,null) reporting_entity, acra.cash_receipt_id, decode(gl.currency_code,:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where gl.currency_code=gdr.from_currency and gdr.to_currency=:p_reval_currency and :p_reval_conv_date=gdr.conversion_date and gdct.user_conversion_type=:p_reval_conv_type and gdct.conversion_type=gdr.conversion_type)) reval_conv_rate from (select araa.org_id, araa.cash_receipt_id, -- sum(case araa.status when 'APP' then nvl(araa.amount_applied_from,araa.amount_applied) when 'ACTIVITY' then decode(araa.receivables_trx_id,-16,araa.amount_applied,null) else null end ) applied_amount, sum(case araa.status when 'APP' then araa.earned_discount_taken when 'ACTIVITY' then decode(araa.receivables_trx_id,-16,araa.earned_discount_taken,null) else null end ) earned_discount_taken, sum(case araa.status when 'APP' then araa.unearned_discount_taken when 'ACTIVITY' then decode(araa.receivables_trx_id,-16,araa.unearned_discount_taken,null) else null end ) unearned_discount_taken, < |