AR Reversed Notes Receivable
Description
Categories: BI Publisher
Columns: Order By Cu, Note Number Cu, Note Amount Cu, Currency Code Cu, Issuer Name Cu, Issue Date Cu, Reversal Date Cu, Maturity Date Cu, Cust Name Cu, Cust Num Cu ...
Columns: Order By Cu, Note Number Cu, Note Amount Cu, Currency Code Cu, Issuer Name Cu, Issue Date Cu, Reversal Date Cu, Maturity Date Cu, Cust Name Cu, Cust Num Cu ...
Application: Receivables
Source: Reversed Notes Receivable Report (XML)
Short Name: ARXNRREV_XML
DB package: AR_ARXNRREV_XMLP_PKG
Source: Reversed Notes Receivable Report (XML)
Short Name: ARXNRREV_XML
DB package: AR_ARXNRREV_XMLP_PKG
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 | |
---|---|---|---|
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 | |
Ledger |
|
LOV Oracle |