AR Reversed Notes Receivable

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)