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