AR Reversed Receipts

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Reversed Receipts Report (XML)
Short Name: ARXREV_XML
DB package: AR_ARXREV_XMLP_PKG
                        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
Parameter Name SQL text Validation
Reversed Receipt Reason
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Reversal GL Date High
 
Date
Reversal GL Date Low
 
Date
Bank Account High
 
LOV Oracle
Bank Account Low
 
LOV Oracle
Currency High
 
LOV Oracle
Currency Low
 
LOV Oracle
Order By
 
LOV Oracle