AR Notes Receivable

            select  cr.currency_code        receipt_currency1,
                bb.bank_name        remit_bank_name1,
                decode(:RP_SORT_BY_PHONETICS, 'Y', bb.bank_name_alt, bb.bank_name) remit_bank_name1_sort,
                cba.bank_account_num        remit_bank_account1,
                SUBSTRB(PARTY.PARTY_NAME,1,50)  customer_name1,
                decode(:RP_SORT_BY_PHONETICS, 'Y', PARTY.ORGANIZATION_NAME_PHONETIC,         SUBSTRB(PARTY.PARTY_NAME,1,50)) customer_name1_sort,
                site.location       customer_site1,
                cr.issue_date       issue_date1,
                ps.due_date     maturity_date1,
                cr.receipt_number       note_number1,
                cr.amount           amount1,
                ecr.receipt_number      exchanged_note_number1,
                lk.meaning          status1
            from    ar_cash_receipts        cr,
                ar_cash_receipts        ecr,
                ar_receipt_methods      rm,
                ar_receipt_classes      rc,
                ar_cash_receipt_history crh,
                ar_receivable_applications  ra,
                ar_payment_schedules    ps,
                ar_payment_schedules    eps,
                            ce_bank_accounts                    cba,
                ce_bank_acct_uses       ba,
                ce_bank_branches_v      bb,
                HZ_CUST_ACCOUNTS    cust,
                HZ_PARTIES      PARTY,
                HZ_CUST_site_uses       site,
                ar_lookups      lk
            where   :P_ORDER_BY='REMITTANCE_BANK'
            and cr.receipt_method_id = rm.receipt_method_id
            and rm.receipt_class_id = rc.receipt_class_id
            and rc.notes_receivable = 'Y'
            and cr.reversal_date is NULL
            &LP_CURRENCY_CODE
            and cr.cash_receipt_id = ps.cash_receipt_id (+)
            &LP_START_DATE
            &LP_END_DATE
            and cr.cash_receipt_id = ra.cash_receipt_id
            and ra.applied_payment_schedule_id = eps.payment_schedule_id (+)
            and (ra.receivable_application_id,cr.cash_receipt_id) in
                (select max(ra2.receivable_application_id), cr2.cash_receipt_id
                from    ar_cash_receipts        cr2,
                    ar_receivable_applications  ra2,
                    ar_payment_schedules    ps2
                where   cr2.cash_receipt_id = ra2.cash_receipt_id
                and ra2.applied_payment_schedule_id = ps2.payment_schedule_id (+)
                and (nvl(ps2.reversed_cash_receipt_id,0),cr2.cash_receipt_id) in
                    (select max(nvl(ps3.reversed_cash_receipt_id,0)), cr3.cash_receipt_id
                    from    ar_cash_receipts        cr3,
                        ar_receivable_applications  ra3,
                        ar_payment_schedules    ps3
                    where   cr3.cash_receipt_id = ra3.cash_receipt_id
                    and ra3.applied_payment_schedule_id = ps3.payment_schedule_id (+)
                    group by cr3.cash_receipt_id)
                group by cr2.cash_receipt_id)
            and eps.reversed_cash_receipt_id  = ecr.cash_receipt_id (+)
            and cr.cash_receipt_id = crh.cash_receipt_id
            and crh.current_record_flag = 'Y'
            and crh.status in ('CONFIRMED', 'REMITTED', 'CLEARED', 'RISK_ELIMINATED')
            and lk.lookup_type = decode(crh.status,
                         'CONFIRMED', 'AR_NOTE_STATUS',
                         'REMITTED', 'RECEIPT_CREATION_STATUS',
                         'CLEARED', decode(crh.factor_flag, 'N', 'AR_NOTE_STATUS', 'RECEIPT_CREATION_STATUS'),
                         'RISK_ELIMINATED', 'AR_NOTE_STATUS')
            and lk.lookup_code  = decode(crh.status,
                         'CONFIRMED', decode(eps.reversed_cash_receipt_id, NULL, 'OPEN', 'EXCHANGE'),
                         'REMITTED', 'REMITTED',
                         'CLEARED', decode(crh.factor_flag, 'N', 'MATURED', 'REMITTED'),
                         'RISK_ELIMINATED', 'MATURED')
            &LP_NOTE_STATUS
            &LP_BANK_ACCOUNT_ID
            and cr.remit_bank_acct_use_id = ba.bank_acct_use_id (+)
            and           ba.bank_account_id = cba.bank_account_id (+)
            and     cba.bank_branch_id = bb.branch_party_id (+)
            &LP_REMITTANCE_BANK
            and cr.pay_from_customer = cust.CUST_ACCOUNT_ID (+)
            AND     CUST.PARTY_ID  = PARTY.PARTY_ID(+)
            &LP_CUSTOMER_NAME_LOW
            &LP_CUSTOMER_NAME_HIGH
            &LP_CUSTOMER_NUMBER_LOW
            &LP_CUSTOMER_NUMBER_HIGH
            and  cr.customer_site_use_id = site.site_use_id (+)
            order by    receipt_currency1,remit_bank_name1_sort,remit_bank_account1,maturity_date1,decode(:RP_SORT_BY_PHONETICS, 'Y', PARTY.ORGANIZATION_NAME_PHONETIC, PARTY.PARTY_NAME),
                site.location,
                cr.receipt_number