AR Reversed Notes Receivable

Description

Categories: BI Publisher, Financials

Application: Receivables
Source: Reversed Notes Receivable Report (XML)
Short Name: ARXNRREV_XML
DB package: AR_ARXNRREV_XMLP_PKG

Copy

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)

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

Ledger

LOV Oracle

Report Non-Exchanged Notes

LOV Oracle

Note Receivable Status

LOV Oracle

Customer Number

LOV Oracle

Customer Name

LOV Oracle

Remittance Bank Account

LOV Oracle

Remittance Bank

LOV Oracle

End Reversal Date

Date

Start Reversal Date

Date

End Maturity Date

Date

Start Maturity Date

Date

Sort Option

LOV Oracle

Currency Code

LOV Oracle

Ask a question

Download

Related Blitz Reports

AR Notes Receivable AR Transactions and Payments CE Cleared Transactions - draft AR Reversed Receipts CE Cash In Transit - draft AR Format Automatic Receipts AR Miscellaneous Receipts AR Receipts Without Sites AR Automatic Receipts Awaiting Confirmation