AR Reversed Receipts

                        select
                                 substr(nvl(cba.bank_account_name, 'None'),1,30)
                                        Bank_Account_A ,
                                 substr(PARTY.PARTY_NAME,1,10)          Name,
                                substr(cust.ACCOUNT_NUMBER,1,10)        Number_A,
                                substr(su.location,1,8)                     Location,
                                substr(branch.bank_name,1,60)   Bank_Name,
                                substr(cba.bank_account_num,1,15)   Bank_Account,
                                substr(cr.receipt_number,1,7)               Receipt_Number,
                                substr(l1.meaning,1,8)                      Type ,
                                ps.due_date         Maturity_Date,
                                substr(l2.meaning,1,8)                      Reason,
                                substr(cr.currency_code,1,15)   Currency_A,
                                l1.lookup_code                              Reversal_Category,
                                crh1.gl_date                                              Original_GL_Date,
                                crh2.gl_date                        Reversal_GL_Date,
                                substr(l3.meaning,1,4)                      R_Type,
                                crh1.amount + nvl(crh1.factor_discount_amount,0)
                                        Amount,
                                crh1.acctd_amount + nvl(crh1.acctd_factor_discount_amount,0)
                                        Functional_Amount,
                            AR_ARXREV_XMLP_PKG.c_summary_label_bankformula(substr ( branch.bank_name , 1 , 60 )) C_SUMMARY_LABEL_BANK,
                            AR_ARXREV_XMLP_PKG.c_calc_amountformula(substr ( l3.meaning , 1 , 4 ), crh1.amount + nvl ( crh1.factor_discount_amount , 0 ), crh1.acctd_amount + nvl ( crh1.acctd_factor_discount_amount , 0 ), l1.lookup_code) C_CALC_AMOUNT,
                            AR_ARXREV_XMLP_PKG.c_cash_amt_func_p c_cash_amt_func,
                            AR_ARXREV_XMLP_PKG.c_cash_amt_p c_cash_amt,
                            AR_ARXREV_XMLP_PKG.c_misc_amt_func_p c_misc_amt_func,
                            AR_ARXREV_XMLP_PKG.c_misc_amt_p c_misc_amt,
                            AR_ARXREV_XMLP_PKG.c_nsf_amt_func_p c_nsf_amt_func,
                            AR_ARXREV_XMLP_PKG.c_nsf_amt_p c_nsf_amt,
                            AR_ARXREV_XMLP_PKG.c_rev_amt_func_p c_rev_amt_func,
                            AR_ARXREV_XMLP_PKG.c_rev_amt_p c_rev_amt,
                            AR_ARXREV_XMLP_PKG.c_stop_amt_func_p c_stop_amt_func,
                            AR_ARXREV_XMLP_PKG.c_stop_amt_p c_stop_amt
                        from    HZ_CUST_ACCOUNTS cust,
                                    HZ_PARTIES PARTY,
                                ar_cash_receipts cr,
                                 ar_payment_schedules ps,
                                ar_lookups  l1,
                                ar_lookups l2,
                                ar_lookups l3,
                                ar_cash_receipt_history crh1,
                                ar_cash_receipt_history crh2,
                                ce_bank_accounts   cba,
                                ce_bank_branches_v branch,
                                ce_bank_acct_uses bank,
                                HZ_CUST_site_uses su
                        where   cr.pay_from_customer     = cust.CUST_ACCOUNT_ID(+)
                        AND      CUST.PARTY_ID  = PARTY.PARTY_ID(+)
                        and     cr.status                    = cr.reversal_category
                        &lp_reason
                        and     cr.reversal_category     = l1.lookup_code
                        and     l1.lookup_type           = 'REVERSAL_CATEGORY_TYPE'
                        and     cr.reversal_reason_code  = l2.lookup_code
                        and     l2.lookup_type          = 'CKAJST_REASON'
                        and     cr.type             = l3.lookup_code
                        and     l3.lookup_type          = 'PAYMENT_CATEGORY_TYPE'
                        and     cr.cash_receipt_id  = crh1.cash_receipt_id
                        and     ps.cash_receipt_id(+)   = cr.cash_receipt_id
                        and     crh1.first_posted_record_flag = 'Y'
                        and     cr.cash_receipt_id  = crh2.cash_receipt_id
                        and     crh2.status         = 'REVERSED'
                        &lp_rev_gl_start_date
                        &lp_rev_gl_end_date
                        and    cr.remit_bank_acct_use_id
                                    = bank.bank_acct_use_id (+)
                        and    bank.bank_account_id   = cba.bank_account_id (+)
                        and      cba.bank_branch_id = branch.branch_party_id
                        &lp_bank_account_low
                        &lp_bank_account_high
                        &lp_customer_low
                        &lp_customer_high
                        &lp_curr_low
                        &lp_curr_high
                        and    cr.customer_site_use_id  = su.site_use_id (+)
                        and    :l_order_rpt_by      = 'BANK'
                        order by    
                            PARTY.PARTY_NAME,
							BANK_NAME,BANK_ACCOUNT,CURRENCY_A,NAME,NUMBER_A,LOCATION,RECEIPT_NUMBER,MATURITY_DATE