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)

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)