AR Open Items Revaluation
Description
Categories: BI Publisher, Enginatics
Repository: Github
Repository: Github
Imported from BI Publisher
Description: Open Items Revaluation Report
Application: Receivables
Source: Open Items Revaluation Report (XML)
Short Name: ARXINREV_XML
DB package: AR_ARXINREV_XMLP_PKG
Description: Open Items Revaluation Report
Application: Receivables
Source: Open Items Revaluation Report (XML)
Short Name: ARXINREV_XML
DB package: AR_ARXINREV_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
select trx.c_ledger "Ledger" , trx.c_balancing "Balancing Segment" , trx.c_account "Account Segment" , trx.c_flexdata "Accounting Flexfield" , trx.c_desc_all "Accounting Flexfield Desc." &lp_report_format_cols from ( select ps.c_ccid , ps.c_balancing , ps.c_account , ps.c_flexdata , ps.c_cust_name , ps.c_cust_number , ps.c_cust_city , ps.c_trx_id , ps.c_pay_id , ps.c_trx_number , ps.c_inv_type , ps.c_inv_date , ps.c_due_date , ps.c_curr , ps.c_pay_amount , ps.c_exchange_rate , ps.c_type , ps.c_revaluate_yes_no , ps.c_inv_gl_posted_date , ps.c_previous_cust_trx_id , ps.c_trx_length , ps.c_ledger , ps.c_operating_unit , ps.c_desc_all -- , ps.c_eop_rate , ps.c_receiptsformula , ps.c_adjustformula , ps.c_cmformula , ps.c_cm1formula -- , (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) c_open_orig , case when ps.p_minimum_accountable_unit is null then round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate),ps.p_precision) else round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit end calc_open_func -- , case when ps.p_minimum_accountable_unit is null then round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate),ps.p_precision) else round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit end calc_eop_amount -- -- , case -- when ps.c_eop_rate is null -- then to_number(null) -- when ps.c_eop_rate < ps.c_exchange_rate -- then (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate -- else (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate -- end calc_open_rev , case when ps.c_eop_rate is null then to_number(null) else case when ps.p_minimum_accountable_unit is null then round((((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) - ((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate)),ps.p_precision) else round((((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) - ((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate)) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit end end calc_open_rev from (select dist.code_combination_id c_ccid , fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') c_balancing , fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') c_account , fnd_flex_xml_publisher_apis.process_kff_combination_1('p_flexdata', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'VALUE') c_flexdata , substrb(party.party_name,1,50) c_cust_name , cust.account_number c_cust_number , loc.city c_cust_city , trx.customer_trx_id c_trx_id , pay.payment_schedule_id c_pay_id , ltrim(rtrim(trx.trx_number)) c_trx_number , typ.name c_inv_type , trx.trx_date c_inv_date , pay.due_date c_due_date , pay.invoice_currency_code c_curr , pay.amount_due_original c_pay_amount , nvl(trx.exchange_rate,1.00) c_exchange_rate , typ.type c_type , typ.global_attribute1 c_revaluate_yes_no , dist.gl_posted_date c_inv_gl_posted_date , nvl(previous_customer_trx_id,0) c_previous_cust_trx_id , length(ltrim(rtrim(nvl(to_char(doc_sequence_value),trx.trx_number)))) c_trx_length , sob.name c_ledger , fc.precision p_precision , fc.minimum_accountable_unit p_minimum_accountable_unit , haouv.name c_operating_unit , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') c_desc_all -- , case when pay.invoice_currency_code = sob.currency_code then 1 when typ.type = 'DEP' then nvl (trx.exchange_rate,1) when :p_rate_type_lookup = 'PERIOD' then (select /*+ push_pred */ decode(tr.eop_rate, 0,0, 1/tr.eop_rate) from gl_translation_rates tr where tr.set_of_books_id = sob.set_of_books_id and tr.to_currency_code = pay.invoice_currency_code and upper(tr.period_name) = upper(:p_revaluation_period) and tr.actual_flag = 'A' ) else case when gl_currency_api.get_rate_sql(pay.invoice_currency_code,sob.currency_code,:p_rate_date,:p_daily_rate_type) < 0 then null else gl_currency_api.get_rate_sql(pay.invoice_currency_code,sob.currency_code,:p_rate_date,:p_daily_rate_type) end end c_eop_rate -- , case when :p_cleared = 'N' then (select /*+ push_pred */ sum(nvl(app2.amount_applied,0) + nvl(app2.earned_discount_taken,0) + nvl(app2.unearned_discount_taken,0)) receipt_amt from ar_receivable_applications app2 where app2.applied_payment_schedule_id = pay.payment_schedule_id and app2.status = 'APP' and app2.gl_date <= gps.end_date and app2.application_type='CASH' and not exists (select 'reversed' from ar_cash_receipt_history crh where app2.cash_receipt_id = crh.cash_receipt_id and crh.status = 'REVERSED' and crh.gl_date <= gps.end_date ) ) else (select /*+ push_pred */ sum(nvl(app2.amount_applied,0) + nvl(app2.earned_discount_taken,0) + nvl(app2.unearned_discount_taken,0)) receipt_amt from ar_receivable_applications app2 where app2.applied_payment_schedule_id = pay.payment_schedule_id and app2.status = 'APP' and app2.gl_date <= gps.end_date and app2.application_type='CASH' and exists (select 'cleared cleared receipt' from ar_cash_receipt_history crh where app2.cash_receipt_id = crh.cash_receipt_id and crh.status = 'CLEARED' and crh.gl_date <= gps.end_date and nvl(crh.reversal_gl_date,gps.end_date+1) > gps.end_date ) ) end c_receiptsformula -- , (select /*+ push_pred */ sum(adj2.amount) adjustment_amt from ar_adjustments adj2 where adj2.payment_schedule_id = pay.payment_schedule_id and adj2.gl_date <= gps.end_date and adj2.status = 'A' ) c_adjustformula -- , (select /*+ push_pred */ sum(nvl(app2.amount_applied,0)) cm_amt from ar_receivable_applications app2 where app2.applied_payment_schedule_id = pay.payment_schedule_id and app2.gl_date <= gps.end_date and app2.status ='APP' and app2.application_type = 'CM' ) c_cmformula -- , (select /*+ push_pred */ sum(nvl(app2.amount_applied,0)) cm_amt from ar_receivable_applications app2 where app2.payment_schedule_id = pay.payment_schedule_id and app2.gl_date <= gps.end_date and (app2.status ='APP' or (app2.status='ACTIVITY' and app2.applied_payment_schedule_id = -8)) and app2.application_type = 'CM' ) c_cm1formula from ra_cust_trx_types typ , hz_cust_accounts cust , hz_parties party , hz_locations loc , hz_cust_acct_sites acct_site , hz_party_sites party_site , hz_cust_site_uses site , gl_code_combinations cc , gl_sets_of_books sob , fnd_currencies fc , hr_all_organization_units_vl haouv , ra_customer_trx trx , ar_xla_ctlgd_lines_v dist , ar_payment_schedules pay , (select /*+ push_pred */ gps.set_of_books_id,gps.end_date from gl_period_statuses gps where gps.application_id = 222 and gps.period_name = :p_revaluation_period ) gps where 1=1 and pay.gl_date <= gps.end_date and cust.cust_account_id = pay.customer_id and cust.party_id = party.party_id and trx.customer_trx_id = pay.customer_trx_id and pay.customer_site_use_id = site.site_use_id(+) and site.cust_acct_site_id = acct_site.cust_acct_site_id(+) and acct_site.party_site_id = party_site.party_site_id(+) and party_site.location_id = loc.location_id(+) and dist.customer_trx_id = trx.customer_trx_id and dist.account_class = 'REC' and dist.latest_rec_flag = 'Y' and dist.account_set_flag = 'N' and trx.complete_flag = 'Y' and trx.set_of_books_id = sob.set_of_books_id and sob.currency_code = fc.currency_code and trx.org_id = haouv.organization_id and typ.cust_trx_type_id = pay.cust_trx_type_id and typ.org_id = pay.org_id and dist.code_combination_id = cc.code_combination_id and dist.set_of_books_id = sob.set_of_books_id and ar_arxinrev_xmlp_pkg.c_daily_rate_lookup_error_p = 'N' and gps.set_of_books_id = sob.set_of_books_id and case when :p_rate_type_lookup = 'DAILY' and (:p_daily_rate_type is null or :p_rate_date is null) then 'Y' else 'N' end = 'N' ) ps order by c_ledger , c_balancing , c_account , c_flexdata , c_ccid , c_cust_name , c_cust_number , c_cust_number , c_trx_length , c_trx_number , c_inv_gl_posted_date , c_revaluate_yes_no , c_inv_type , c_inv_date , c_due_date , c_trx_id , c_exchange_rate , c_type , c_pay_id , c_curr , c_pay_amount ) trx where trx.c_open_orig != 0 and (trx.c_type != 'CM' or (trx.c_type = 'CM' and trx.c_previous_cust_trx_id = 0)) &lp_group_by_cols |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Ledger |
|
LOV | |
Operating Unit |
|
LOV | |
Report Format |
|
LOV Oracle | |
Revaluation Period |
|
LOV | |
Include Up To Due Date |
|
Date | |
Rate Type |
|
LOV Oracle | |
Daily Rate Type |
|
LOV Oracle | |
Daily Rate Date |
|
Date | |
Balancing Segment Low |
|
LOV | |
Balancing Segment High |
|
LOV | |
Transferred To GL only |
|
LOV Oracle | |
Cleared only |
|
LOV Oracle |