AR Reversed Notes Receivable
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Reversed Notes Receivable Report
Application: Receivables
Source: Reversed Notes Receivable Report (XML)
Short Name: ARXNRREV_XML
DB package: AR_ARXNRREV_XMLP_PKG
Description: Reversed Notes Receivable Report
Application: Receivables
Source: Reversed Notes Receivable Report (XML)
Short Name: ARXNRREV_XML
DB package: AR_ARXNRREV_XMLP_PKG
Run
AR Reversed Notes Receivable and other Oracle EBS reports with Blitz Report™ on our demo environment
select :CP_order_by order_by_cu, CR.receipt_number note_number_cu, CR.amount note_amount_cu, CR.currency_code currency_code_cu, CR.issuer_name issuer_name_cu, CR.issue_date issue_date_cu, CR.reversal_date reversal_date_cu, PS.due_date maturity_date_cu, SUBSTRB(PARTY.PARTY_NAME,1,50) cust_name_cu, CU.ACCOUNT_NUMBER cust_num_cu, SU.location site_name_cu, CBA.bank_account_name bank_account_cu, BB.bank_name remittance_bank_cu, BBI.bank_name issuer_bank_name_cu, PSX.trx_number debit_memo_cu, DECODE(CRX.reversal_date, NULL, CRX.receipt_number, ' ') exchange_note_cu, LK.meaning status_cu, DECODE(:RP_SORT_BY_PHONETICS, 'Y', PARTY.ORGANIZATION_NAME_PHONETIC, SUBSTRB(PARTY.PARTY_NAME,1,50)) customer_name_sort_cu, DECODE(:RP_SORT_BY_PHONETICS, 'Y', BB.bank_name_alt, BB.bank_name) bank_name_sort_cu from ar_cash_receipts CR, ar_cash_receipts CRX, ar_cash_receipt_history RH, ar_receipt_methods RM, ar_receipt_classes RC, ce_bank_acct_uses BA, ce_bank_accounts CBA, ce_bank_branches_v BB, ce_bank_branches_v BBI, ar_payment_schedules PS, ar_payment_schedules PSX, HZ_CUST_ACCOUNTS CU, HZ_PARTIES PARTY, HZ_CUST_site_uses SU, ar_receivable_applications RAX, ar_lookups LK WHERE :P_ORDER_BY = 'CUSTOMER' AND CR.reversal_date is not NULL AND CR.reversal_date >= nvl(:P_START_REVERSAL_DATE, CR.reversal_date) AND CR.reversal_date <= nvl(:P_END_REVERSAL_DATE, CR.reversal_date) AND CR.currency_code = nvl(:P_CURRENCY_CODE, CR.currency_code) AND RH.cash_receipt_id = CR.cash_receipt_id AND RH.current_record_flag = 'Y' AND RM.receipt_method_id = CR.receipt_method_id AND RC.receipt_class_id = RM.receipt_class_id AND nvl(RC.notes_receivable,'N') = 'Y' AND BA.bank_acct_use_id = CR.remit_bank_acct_use_id AND BA.bank_account_id = CBA.bank_account_id AND BA.bank_acct_use_id = nvl(:P_BANK_ACCOUNT, BA.bank_acct_use_id) AND BB.branch_party_id = CBA.bank_branch_id AND BB.bank_name = nvl(:P_REMITTANCE_BANK, BB.bank_name) AND BBI.branch_party_id(+) = CR.issuer_bank_branch_id AND PS.cash_receipt_id = CR.cash_receipt_id AND PS.due_date >= nvl(:P_START_MATURITY_DATE, PS.due_date) AND PS.due_date <= nvl(:P_END_MATURITY_DATE, PS.due_date) AND CU.CUST_ACCOUNT_ID (+) = CR.pay_from_customer AND CU.PARTY_ID = PARTY.PARTY_ID(+) AND CU.ACCOUNT_NUMBER = nvl(:P_CUSTOMER_NUMBER, CU.ACCOUNT_NUMBER) AND SU.site_use_id(+) = CR.customer_site_use_id AND PSX.reversed_cash_receipt_id(+) = PS.cash_receipt_id AND nvl(PSX.payment_schedule_id, -1) in (select nvl(max(payment_schedule_id), -1) from ar_payment_schedules PSS where PSS.reversed_cash_receipt_id = CR.cash_receipt_id and PSS.class = 'DM') AND RAX.applied_payment_schedule_id (+) = PSX.payment_schedule_id AND nvl(RAX.receivable_application_id, -1) in (select nvl(max(RAXX.receivable_application_id), -1) from ar_receivable_applications RAXX where RAXX.applied_payment_schedule_id = RAX.applied_payment_schedule_id and RAXX.status = 'APP') AND CRX.cash_receipt_id(+) = RAX.cash_receipt_id AND DECODE(:P_NON_EXCHANGED, 'Y', DECODE(PSX.trx_number, NULL, 'N', DECODE(CRX.receipt_number, NULL, 'Y', DECODE(CRX.reversal_date, NULL, 'N', 'Y'))), 'Y') = 'Y' AND LK.lookup_code = DECODE(PSX.trx_number, NULL, 'RETURN', DECODE(CR.reversal_date-PS.due_date, 0, 'RETURN', DECODE((CR.reversal_date-PS.due_date+1)/abs(CR.reversal_date-PS.due_date+1), -1, 'RETURN', DECODE(RH.factor_flag, 'N', 'DELINQUENT', 'REPURCHASE')))) AND LK.lookup_type = 'AR_NOTE_STATUS' and LK.lookup_code = nvl(:P_NOTE_STATUS, LK.lookup_code) order by CR.reversal_date, DECODE(:RP_SORT_BY_PHONETICS, 'Y', BB.bank_name_alt, BB.bank_name) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Currency Code |
|
LOV Oracle | |
Sort Option |
|
LOV Oracle | |
Start Maturity Date |
|
Date | |
End Maturity Date |
|
Date | |
Start Reversal Date |
|
Date | |
End Reversal Date |
|
Date | |
Remittance Bank |
|
LOV Oracle | |
Remittance Bank Account |
|
LOV Oracle | |
Customer Name |
|
LOV Oracle | |
Customer Number |
|
LOV Oracle | |
Note Receivable Status |
|
LOV Oracle | |
Report Non-Exchanged Notes |
|
LOV Oracle |