ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 359F2BEB002311B8E0633D7D37A2C85F select /*+ push_pred(x) */ x.*, hcpa.cust_account_profile_id, hcps.cust_account_profile_id from ( select substrb(hp.party_name,1,50) cust_name, hca.account_number cust_no, nvl(sales.name,jrrev.resource_name) sort_field1, arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) sort_field2, nvl(sales.salesrep_id, -3) salesrep_id, rt.name term, site.site_use_id, loc.state cust_state, loc.city cust_city, (select ftv.territory_short_name from fnd_territories_vl ftv where ftv.territory_code = loc.country) cust_country, decode(decode(upper(rtrim(rpad(:p_in_format_option_low, 1))),'D','D',null),null,-1,acct_site.cust_acct_site_id) addr_id, nvl(hca.cust_account_id,-999) cust_id, hca.party_id, ps.payment_schedule_id payment_sched_id, ps.class class, ps.trx_date, ps.due_date, decode(nvl2(:p_in_currency,'N','Y'), 'Y', ps.amount_due_original * nvl(ps.exchange_rate, 1), ps.amount_due_original) amt_due_original, xxen_ar_arxagrw_pkg.comp_amt_due_remainingformula ( ps.class, arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id), ps.payment_schedule_id, ps.amt_due_remaining, ps.amount_applied, ps.amount_credited, ps.amount_adjusted ) amt_due_remaining, ps.trx_number invnum, ceil(:p_in_as_of_date_low - ps.trx_date) days_past_trx, ceil(:p_in_as_of_date_low - ps.due_date) days_past_due, ps.amount_adjusted amount_adjusted, ps.amount_applied amount_applied, ps.amount_credited amount_credited, ps.gl_date gl_date, decode(ps.invoice_currency_code, gsob.currency_code, NULL, decode(ps.exchange_rate, null, '*', null)) data_converted, nvl(ps.exchange_rate, 1) ps_exchange_rate, c.code_combination_id, c.chart_of_accounts_id, ci.cons_billing_number cons_billing_number, arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type, ps.invoice_currency_code, gsob.currency_code functional_currency, gsob.name ledger, ps.org_id from hz_cust_accounts hca, hz_parties hp, (select a.customer_id, a.customer_site_use_id , a.customer_trx_id, a.payment_schedule_id, a.class , sum(a.primary_salesrep_id) primary_salesrep_id, a.term_id, a.trx_date, a.due_date, sum(a.amount_due_remaining) amt_due_remaining, a.trx_number, a.amount_due_original, a.amount_adjusted, a.amount_applied , a.amount_credited , a.amount_adjusted_pending, a.gl_date , a.cust_trx_type_id, a.org_id, a.invoice_currency_code, a.exchange_rate, sum(a.cons_inv_id) cons_inv_id from ( select ps.customer_id, ps.customer_site_use_id, ps.customer_trx_id, ps.payment_schedule_id, ps.class, 0 primary_salesrep_id, ps.term_id, ps.trx_date, ps.due_date, nvl(sum(decode(nvl2(:p_in_currency,'N','Y'), 'Y', nvl(adj.acctd_amount, 0), adj.amount)),0) * (-1) amount_due_remaining, ps.trx_number, ps.amount_due_original, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.amount_adjusted_pending, ps.gl_date, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate,1) exchange_rate, 0 cons_inv_id from ar_payment_schedules ps, ar_adjustments adj where ps.gl_date <= :p_in_as_of_date_low and ps.customer_id > 0 and ps.gl_date_closed > :p_in_as_of_date_low and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code,upper(:p_in_currency)) = ps.invoice_currency_code and adj.payment_schedule_id = ps.payment_schedule_id and adj.status = 'A' and adj.gl_date > :p_in_as_of_date_low and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and adj.org_id = ps.org_id group by ps.customer_id, ps.customer_site_use_id, ps.customer_trx_id, ps.class, ps.term_id, ps.trx_date, ps.due_date, ps.trx_number, ps.amount_due_original, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.amount_adjusted_pending, ps.gl_date, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate,1), ps.payment_schedule_id union all select ps.customer_id, ps.customer_site_use_id, ps.customer_trx_id, ps.payment_schedule_id, ps.class, 0 primary_salesrep_id, ps.term_id, ps.trx_date, ps.due_date, nvl(sum(decode( nvl2(:p_in_currency,'N','Y'), 'Y', (decode( ps.class, 'CM', decode( app.application_type, 'CM', app.acctd_amount_applied_from, app.acctd_amount_applied_to), app.acctd_amount_applied_to ) + nvl(app.acctd_earned_discount_taken,0) + nvl(app.acctd_unearned_discount_taken,0) ) , ( app.amount_applied + nvl(app.earned_discount_taken,0) + nvl(app.unearned_discount_taken,0) ) ) * decode( ps.class, 'CM', decode(app.application_type, 'CM', -1, 1), 1 ) ),0 ) amount_due_remaining, ps.trx_number, ps.amount_due_original, ps.amount_adjusted, ps.amount_applied , ps.amount_credited, ps.amount_adjusted_pending, ps.gl_date gl_date, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, 0 cons_inv_id from ar_payment_schedules ps, ar_receivable_applications app where ps.gl_date <= :p_in_as_of_date_low and ps.customer_id > 0 and ps.gl_date_closed > :p_in_as_of_date_low and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and (app.applied_payment_schedule_id = ps.payment_schedule_id or app.payment_schedule_id = ps.payment_schedule_id ) and app.status IN ('APP', 'ACTIVITY') and nvl( app.confirmed_flag, 'Y' ) = 'Y' and app.gl_date > :p_in_as_of_date_low and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and app.org_id = ps.org_id group by ps.customer_id, ps.customer_site_use_id, ps.customer_trx_id, ps.class, ps.term_id, ps.trx_date, ps.due_date, ps.trx_number, ps.amount_due_original, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.amount_adjusted_pending, ps.gl_date, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1), ps.payment_schedule_id union all select ps.customer_id, ps.customer_site_use_id, ps.customer_trx_id, ps.payment_schedule_id, ps.class class, nvl(ct.primary_salesrep_id, -3) primary_salesrep_id, ps.term_id, ps.trx_date, ps.due_date, decode( nvl2(:p_in_currency,'N','Y'), 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining) amt_due_remaining, ps.trx_number, ps.amount_due_original, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.amount_adjusted_pending, ps.gl_date, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, ps.cons_inv_id from ar_payment_schedules ps, ra_customer_trx ct where ps.gl_date <= :p_in_as_of_date_low and ps.gl_date_closed > :p_in_as_of_date_low and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.customer_trx_id = ct.customer_trx_id and ps.class <> 'CB' and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and ct.org_id = ps.org_id union all select ps.customer_id, ps.customer_site_use_id , ps.customer_trx_id, ps.payment_schedule_id, ps.class class, nvl(ct.primary_salesrep_id,-3) primary_salesrep_id, ps.term_id, ps.trx_date, ps.due_date, decode( nvl2(:p_in_currency,'N','Y'), 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining) amt_due_remaining, ps.trx_number, ps.amount_due_original, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.amount_adjusted_pending, ps.gl_date, ps.cust_trx_type_id, ps.org_id, ps.invoice_currency_code, nvl(ps.exchange_rate, 1) exchange_rate, ps.cons_inv_id from ar_payment_schedules ps, ra_customer_trx ct, ar_adjustments adj where ps.gl_date <= :p_in_as_of_date_low and ps.gl_date_closed > :p_in_as_of_date_low and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and ps.class = 'CB' and ps.customer_trx_id = adj.chargeback_customer_trx_id (+) and ps.org_id = adj.org_id (+) and adj.customer_trx_id = ct.customer_trx_id (+) and adj.org_id = ct.org_id (+) and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' ) a group by a.customer_id, a.customer_site_use_id, a.customer_trx_id, a.payment_schedule_id, a.class, a.term_id, a.trx_date, a.due_date, a.trx_number, a.amount_due_original, a.amount_adjusted, a.amount_applied, a.amount_credited, a.amount_adjusted_pending, a.gl_date, a.cust_trx_type_id, a.org_id, a.invoice_currency_code, a.exchange_rate ) ps, ar_cons_inv ci, ra_salesreps_all sales, jtf_rs_resource_extns_vl jrrev, hz_cust_site_uses site, hz_cust_acct_sites acct_site, hz_party_sites party_site, hz_locations loc, ra_cust_trx_line_gl_dist gld, ar_dispute_history dh, ra_terms rt, gl_code_combinations c, ra_customer_trx rct, gl_sets_of_books gsob where --upper(RTRIM(RPAD(:p_in_summary_option_low,1)) ) = 'I' ps.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 loc.location_id = party_site.location_id and ps.customer_id = hca.cust_account_id and hca.party_id = hp.party_id and ps.customer_trx_id = gld.customer_trx_id and gld.account_class = 'REC' and gld.latest_rec_flag = 'Y' and gld.code_combination_id = c.code_combination_id and ps.payment_schedule_id = dh.payment_schedule_id(+) and ps.term_id = rt.term_id (+) and :p_in_as_of_date_low >= nvl(dh.start_date(+), :p_in_as_of_date_low) and :p_in_as_of_date_low < nvl(dh.end_date(+), :p_in_as_of_date_low + 1) and ( dh.dispute_history_id is null or dh.dispute_history_id = (select max(dh2.dispute_history_id) from ar_dispute_history dh2 where dh2.payment_schedule_id = ps.payment_schedule_id and :p_in_as_of_date_low >= nvl(dh2.start_date(+), :p_in_as_of_date_low) and :p_in_as_of_date_low < nvl(dh2.end_date(+), :p_in_as_of_date_low + 1) ) ) and rct.customer_trx_id = ps.customer_trx_id and gsob.set_of_books_id = rct.set_of_books_id and ps.cons_inv_id = ci.cons_inv_id(+) and nvl(ps.primary_salesrep_id,-3) = sales.salesrep_id and sales.org_id = ps.org_id and jrrev.resource_id = sales.resource_id and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and gld.org_id = ps.org_id and site.org_id = ps.org_id and 2=2 union all select substrb(nvl(hp.party_name,:p_short_unid_phrase),1,50) cust_name, hca.account_number cust_no, nvl(sales.name,jrrev.resource_name), initcap(:p_payment_meaning), nvl(sales.salesrep_id,-3), null term, site.site_use_id, loc.state cust_state, loc.city cust_city, (select ftv.territory_short_name from fnd_territories_vl ftv where ftv.territory_code = loc.country) cust_country, decode(decode(upper(RTRIM(RPAD(:p_in_format_option_low, 1))),'D','D',NULL),NULL,-1,acct_site.cust_acct_site_id) addr_id, nvl(hca.cust_account_id, -999) cust_id, hca.party_id, ps.payment_schedule_id, app.class, ps.trx_date, ps.due_date, decode(nvl2(:p_in_currency,'N','Y'), 'Y', ps.amount_due_original * nvl(ps.exchange_rate, 1), ps.amount_due_original) amt_due_original, xxen_ar_arxagrw_pkg.Comp_Amt_Due_RemainingFormula ( app.class, initcap(:p_payment_meaning), ps.payment_schedule_id, decode(nvl2(:p_in_currency,'N','Y'), 'Y', nvl(-SUM(app.acctd_amount), 0), nvl(-SUM(app.amount), 0)), -- amount due remaining ps.amount_applied, ps.amount_credited, ps.amount_adjusted ) amt_due_remaining, ps.trx_number, ceil(:p_in_as_of_date_low - ps.trx_date) days_past_trx, ceil(:p_in_as_of_date_low - ps.due_date) days_past_due, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.gl_date, decode(ps.invoice_currency_code, gsob.currency_code, NULL, decode(ps.exchange_rate, NULL, '*', NULL)), nvl(ps.exchange_rate, 1), app.code_combination_id, app.chart_of_accounts_id, ci.cons_billing_number cons_billing_number, initcap(:p_payment_meaning), ps.invoice_currency_code, gsob.currency_code functional_currency, gsob.name ledger, ps.org_id from hz_cust_accounts hca, hz_parties hp, ar_payment_schedules ps, ar_cons_inv ci, ra_salesreps_all sales, jtf_rs_resource_extns_vl jrrev, hz_cust_site_uses site, hz_cust_acct_sites acct_site, hz_party_sites party_site, hz_locations loc, ar_cash_receipts acr, gl_sets_of_books gsob, ( select c.code_combination_id, c.chart_of_accounts_id, ps.payment_schedule_id payment_schedule_id, decode(app.applied_payment_schedule_id, -4, 'CLAIM', ps.class) class, app.acctd_amount_applied_from acctd_amount, app.amount_applied amount, app.status status from ar_receivable_applications app, gl_code_combinations c, ar_payment_schedules ps where app.gl_date <= :p_in_as_of_date_low --and upper(RTRIM(RPAD(:p_in_summary_option_low,1))) = 'I' and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and ps.cash_receipt_id = app.cash_receipt_id and app.code_combination_id = c.code_combination_id and app.status in ( 'ACC', 'UNAPP', 'UNID','OTHER ACC') and nvl(app.confirmed_flag, 'Y') = 'Y' and ps.gl_date_closed > :p_in_as_of_date_low and ((app.reversal_gl_date is not null and ps.gl_date <= :p_in_as_of_date_low ) or app.reversal_gl_date is null ) and decode(upper(:p_in_currency), null, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and nvl( ps.receipt_confirmed_flag, 'Y' ) = 'Y' and app.org_id = ps.org_id ) app where ps.payment_schedule_id = app.payment_schedule_id and ps.customer_id = hca.cust_account_id(+) and hca.party_id = hp.party_id(+) and ps.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 loc.location_id(+) = party_site.location_id and acr.cash_receipt_id = ps.cash_receipt_id and gsob.set_of_books_id = acr.set_of_books_id and ps.cons_inv_id = ci.cons_inv_id(+) and sales.salesrep_id = -3 and sales.org_id = ps.org_id and jrrev.resource_id = sales.resource_id and site.org_id (+) = ps.org_id and 2=2 group by hp.party_name, hca.account_number, site.site_use_id, nvl(sales.name,jrrev.resource_name), nvl(sales.salesrep_id,-3), loc.state, loc.city, loc.country, acct_site.cust_acct_site_id, hca.cust_account_id, hca.party_id, ps.payment_schedule_id, ps.trx_date, ps.due_date, ps.trx_number, ps.amount_due_original, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, ps.gl_date, ps.amount_in_dispute, ps.amount_adjusted_pending, ps.invoice_currency_code, ps.exchange_rate, app.class, app.code_combination_id, app.chart_of_accounts_id, decode( app.status, 'UNID', 'UNID','OTHER ACC','OTHER ACC','UNAPP'), ci.cons_billing_number , initcap(:p_payment_meaning), gsob.currency_code, gsob.name, ps.org_id union all select substrb(nvl(hp.party_name, :p_short_unid_phrase),1,50) cust_name, hca.account_number cust_no, nvl(sales.name,jrrev.resource_name), initcap(:p_risk_meaning), nvl(sales.salesrep_id,-3), null term, site.site_use_id, loc.state cust_state, loc.city cust_city, (select ftv.territory_short_name from fnd_territories_vl ftv where ftv.territory_code = loc.country) cust_country, decode(decode(upper(RTRIM(RPAD(:p_in_format_option_low, 1))),'D','D',NULL),NULL,-1,acct_site.cust_acct_site_id) addr_id, nvl(hca.cust_account_id, -999) cust_id, hca.party_id, ps.payment_schedule_id, initcap(:p_risk_meaning), ps.trx_date, ps.due_date, decode(nvl2(:p_in_currency,'N','Y'), 'Y', ps.amount_due_original * nvl(ps.exchange_rate, 1), ps.amount_due_original) amt_due_original, xxen_ar_arxagrw_pkg.comp_amt_due_remainingformula ( initcap(:p_risk_meaning), initcap(:p_risk_meaning), ps.payment_schedule_id, decode( nvl2(:p_in_currency,'N','Y'), 'Y', crh.acctd_amount, crh.amount), -- amount due remaining ps.amount_applied, ps.amount_credited, ps.amount_adjusted ) amt_due_remaining, ps.trx_number, ceil(:p_in_as_of_date_low - ps.trx_date) days_past_trx, ceil(:p_in_as_of_date_low - ps.due_date) days_past_due, ps.amount_adjusted, ps.amount_applied, ps.amount_credited, crh.gl_date, decode(ps.invoice_currency_code, gsob.currency_code, NULL, decode(crh.exchange_rate, NULL, '*', NULL)), nvl(crh.exchange_rate, 1), c.code_combination_id, c.chart_of_accounts_id, ci.cons_billing_number cons_billing_number, initcap(:p_risk_meaning), ps.invoice_currency_code, gsob.currency_code functional_currency, gsob.name ledger, ps.org_id from hz_cust_accounts hca, hz_parties hp, ar_payment_schedules ps, ar_cons_inv ci, hz_cust_site_uses site, hz_cust_acct_sites acct_site, hz_party_sites party_site, hz_locations loc, ar_cash_receipts cr, ar_cash_receipt_history crh, gl_code_combinations c, ra_salesreps_all sales, jtf_rs_resource_extns_vl jrrev, gl_sets_of_books gsob where crh.gl_date <= :p_in_as_of_date_low --and upper(RTRIM(RPAD(:p_in_summary_option_low,1))) = 'I' and upper(:p_risk_option) != 'NONE' and ps.customer_id = hca.cust_account_id(+) and hca.party_id = hp.party_id(+) and ps.cash_receipt_id = cr.cash_receipt_id and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and cr.cash_receipt_id = crh.cash_receipt_id and crh.account_code_combination_id = c.code_combination_id and ps.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 loc.location_id(+) = party_site.location_id and decode(upper(:p_in_currency), NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and (crh.current_record_flag = 'Y' or crh.reversal_gl_date > :p_in_as_of_date_low ) and crh.status not in (decode(crh.factor_flag,'Y','RISK_ELIMINATED','N','CLEARED'),'REVERSED') and not exists (select 'x' from ar_receivable_applications ra where ra.cash_receipt_id = cr.cash_receipt_id and ra.status = 'ACTIVITY' and applied_payment_schedule_id = -2) and cr.cash_receipt_id not in (select ps.reversed_cash_receipt_id from ar_payment_schedules ps where ps.reversed_cash_receipt_id=cr.cash_receipt_id and ps.class='DM' and ps.gl_date<= (:p_in_as_of_date_low)) and gsob.set_of_books_id = cr.set_of_books_id and ps.cons_inv_id = ci.cons_inv_id(+) and sales.salesrep_id = -3 and sales.org_id = ps.org_id and jrrev.resource_id = sales.resource_id and crh.org_id = ps.org_id and cr.org_id = ps.org_id and site.org_id (+) = ps.org_id and 2=2 union all select substrb(hp.party_name,1,50) cust_name, hca.account_number cust_no, nvl(sales.name,jrrev.resource_name) sort_field1, arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) sort_field2, nvl(sales.salesrep_id, -3) salesrep_id, rt.name term, site.site_use_id contact_site_id, loc.state cust_state, loc.city cust_city, (select ftv.territory_short_name from fnd_territories_vl ftv where ftv.territory_code = loc.country) cust_country, decode(decode(upper(rtrim(rpad(:p_in_format_option_low, 1))),'D','D',null),null,-1,acct_site.cust_acct_site_id) addr_id, nvl(hca.cust_account_id,-999) cust_id, hca.party_id, ps.payment_schedule_id payment_sched_id, ps.class class, ps.trx_date, ps.due_date, decode(nvl2(:p_in_currency,'N','Y'), 'Y', ps.amount_due_original * nvl(ps.exchange_rate, 1), ps.amount_due_original) amt_due_original, xxen_ar_arxagrw_pkg.comp_amt_due_remainingformula ( ps.class, arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id), ps.payment_schedule_id, decode( nvl2(:p_in_currency,'N','Y'), 'Y', ps.acctd_amount_due_remaining,ps.amount_due_remaining), -- amount due remaining ps.amount_applied, ps.amount_credited, ps.amount_adjusted ) amt_due_remaining, ps.trx_number invnum, ceil(:p_in_as_of_date_low - ps.trx_date) days_past_trx, ceil(:p_in_as_of_date_low - ps.due_date) days_past_due, ps.amount_adjusted amount_adjusted, ps.amount_applied amount_applied, ps.amount_credited amount_credited, ps.gl_date gl_date, decode(ps.invoice_currency_code, gsob.currency_code, NULL, decode(ps.exchange_rate, NULL, '*', NULL)) data_converted, nvl(ps.exchange_rate, 1) ps_exchange_rate, c.code_combination_id, c.chart_of_accounts_id, ci.cons_billing_number cons_billing_number, arpt_sql_func_util.get_org_trx_type_details(ps.cust_trx_type_id,ps.org_id) invoice_type, ps.invoice_currency_code, gsob.currency_code functional_currency, gsob.name ledger, ps.org_id from hz_cust_accounts hca, hz_parties hp, ar_payment_schedules ps, ar_cons_inv ci, ra_salesreps_all sales, jtf_rs_resource_extns_vl jrrev, hz_cust_site_uses site, hz_cust_acct_sites acct_site, hz_party_sites party_site, hz_locations loc, ar_transaction_history th, ar_distributions dist, gl_code_combinations c, ra_customer_trx ct, ra_terms rt, gl_sets_of_books gsob where ps.gl_date <= :p_in_as_of_date_low --and upper(RTRIM(RPAD(:p_in_summary_option_low,1)) ) = 'I' and ps.customer_site_use_id = site.site_use_id and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and site.cust_acct_site_id = acct_site.cust_acct_site_id and acct_site.party_site_id = party_site.party_site_id and loc.location_id = party_site.location_id and ps.gl_date_closed > :p_in_as_of_date_low and ps.class = 'BR' and decode(upper(:p_in_currency),NULL, ps.invoice_currency_code, upper(:p_in_currency)) = ps.invoice_currency_code and hca.party_id = hp.party_id and th.transaction_history_id = (select max(transaction_history_id) from ar_transaction_history th2, ar_distributions dist2 where th2.transaction_history_id = dist2.source_id and dist2.source_table = 'TH' and th2.gl_date <= :p_in_as_of_date_low and dist2.amount_dr is not null and th2.customer_trx_id = ps.customer_trx_id) and th.transaction_history_id = dist.source_id and dist.source_table = 'TH' and dist.amount_dr is not null and dist.source_table_secondary is NULL and dist.code_combination_id = c.code_combination_id and gsob.set_of_books_id = ct.set_of_books_id and ps.cons_inv_id = ci.cons_inv_id(+) and ps.customer_id = hca.cust_account_id and ps.customer_trx_id = ct.customer_trx_id and ct.customer_trx_id = th.customer_trx_id and nvl(ct.primary_salesrep_id,-3) = sales.salesrep_id and sales.org_id = ct.org_id and jrrev.resource_id = sales.resource_id and ct.org_id = ps.org_id and site.org_id = ps.org_id and ps.term_id = rt.term_id (+) and :p_br_enabled = 'Y' and 2=2 ) x, hz_customer_profiles hcpa, hz_customer_profiles hcps where 3=3 and mo_global.check_access(x.org_id) = 'Y' and hcpa.cust_account_id (+) = x.cust_id and hcpa.site_use_id (+) is null and hcps.cust_account_id (+) = x.cust_id and hcps.site_use_id (+) = x.site_use_id Y #,##0.00;[Red]-#,##0.00 B057D82F30B17A2DE0530100007FD563 XXEN_AR_ARXAGRW_PKG US AR Aging - DIagnostic Report 2=2 3=3 :g_bucket_category :lp_org_id_in_list :p_br_enabled :p_coaid :p_conc_request_id :p_credit_option :p_credit_option_dsp :p_in_amt_due_high :p_in_amt_due_low :p_in_as_of_date_low :p_in_bal_segment_high :p_in_bal_segment_low :p_in_bucket_type_low :p_in_collector_high :p_in_collector_low :p_in_currency :p_in_customer_name_high :p_in_customer_name_low :p_in_customer_num_high :p_in_customer_num_low :p_in_format_option_low :p_in_invoice_type_high :p_in_invoice_type_low :p_in_salesrep_name_high :p_in_salesrep_name_low :p_in_sortoption :p_in_summary_option_low :p_min_precision :p_payment_meaning :p_rep_type :p_reporting_entity_id :p_reporting_entity_name :p_reporting_level :p_reporting_level_name :p_risk_meaning :p_risk_option :p_short_unid_phrase 1 -90 :p_coaid Number select gsob.chart_of_accounts_id from gl_sets_of_books gsob, ar_system_parameters asp, hr_all_organization_units haou where gsob.set_of_books_id=asp.set_of_books_id and asp.org_id=haou.organization_id and ( :$flex$.reporting_context is null or :$flex$.reporting_level='1000' and xxen_util.contains(:$flex$.reporting_context,gsob.name)='Y' or :$flex$.reporting_level='3000' and xxen_util.contains(:$flex$.reporting_context,haou.name)='Y' ) and rownum=1 Y US Chart of Accounts Id 2 -80 :p_rep_type Char select lookup_code from ar_lookups where lookup_type = 'AR_AGING_TYPE' and lookup_code='ARXAGR' Y US Report type 3 -70 :p_in_sortoption LOV Oracle AR_ARXAGR_SORT_BY Y select lookup_code id, meaning value, null description from ar_lookups where lookup_type='SORT_BY_ARXAGR' order by value,description Salesperson Y US Order By 4 -60 :p_payment_meaning Char select rtrim(rpad(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM/ADJ','PMT'),20)) from dual US Payment Meaning 5 -50 :p_risk_meaning Char select rtrim(rpad(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('MISC_PHRASES','RISK'),20)) from dual US Risk Meaning 6 -40 :p_short_unid_phrase Char select rtrim(rpad(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('MISC_PHRASES','UNIDENTIFIED_PAYMENT'),18)) from dual US Unidentified Meaning 7 -30 :p_in_format_option_low LOV Oracle AR_REPORT_FORMAT Y select l.lookup_code id, l.meaning value, null description from ar_lookups l where l.lookup_type='REPORT_FORMAT' and l.enabled_flag='Y' and sysdate between nvl(l.start_date_active,sysdate) and nvl(l.end_date_active,sysdate) order by l.meaning,l.lookup_code D Y US Report Format 8 -20 &lp_bal_seg_p <parameter_value> Char select nvl( ( select fifsv.form_left_prompt from fnd_segment_attribute_values fsav , fnd_id_flex_segments_vl fifsv where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_BALANCING' and fsav.id_flex_num = ( select distinct case when count(distinct gsob.chart_of_accounts_id) over () = 1 then gsob.chart_of_accounts_id else null end from gl_sets_of_books gsob where :$flex$.reporting_level = '1000' and :$flex$.reporting_context is not null and xxen_util.contains(:$flex$.reporting_context,gsob.name) = 'Y' union select distinct case when count(distinct gsob.chart_of_accounts_id) over () = 1 then gsob.chart_of_accounts_id else null end from gl_sets_of_books gsob , ar_system_parameters aps , hr_all_organization_units haou where gsob.set_of_books_id = aps.set_of_books_id and haou.organization_id = aps.set_of_books_id and :$flex$.reporting_level = '3000' and :$flex$.reporting_context is not null and xxen_util.contains(:$flex$.reporting_context,haou.name) = 'Y' union select distinct case when count(distinct gsob.chart_of_accounts_id) over () = 1 then gsob.chart_of_accounts_id else null end from gl_sets_of_books gsob , ar_system_parameters aps where gsob.set_of_books_id = aps.set_of_books_id and :$flex$.reporting_context is null ) and fsav.attribute_value = 'Y' and fifsv.application_id = fsav.application_id and fifsv.id_flex_code = fsav.id_flex_code and fifsv.id_flex_num = fsav.id_flex_num and fifsv.application_column_name = fsav.application_column_name ), 'Balancing Segment') from dual US Balancing Segment Label 9 -10 &lp_acc_seg_p <parameter_value> Char select nvl( ( select fifsv.form_left_prompt from fnd_segment_attribute_values fsav , fnd_id_flex_segments_vl fifsv where fsav.application_id = 101 and fsav.id_flex_code = 'GL#' and fsav.segment_attribute_type = 'GL_ACCOUNT' and fsav.id_flex_num = ( select distinct case when count(distinct gsob.chart_of_accounts_id) over () = 1 then gsob.chart_of_accounts_id else null end from gl_sets_of_books gsob where :$flex$.reporting_level = '1000' and :$flex$.reporting_context is not null and xxen_util.contains(:$flex$.reporting_context,gsob.name) = 'Y' union select distinct case when count(distinct gsob.chart_of_accounts_id) over () = 1 then gsob.chart_of_accounts_id else null end from gl_sets_of_books gsob , ar_system_parameters aps , hr_all_organization_units haou where gsob.set_of_books_id = aps.set_of_books_id and haou.organization_id = aps.set_of_books_id and :$flex$.reporting_level = '3000' and :$flex$.reporting_context is not null and xxen_util.contains(:$flex$.reporting_context,haou.name) = 'Y' union select distinct case when count(distinct gsob.chart_of_accounts_id) over () = 1 then gsob.chart_of_accounts_id else null end from gl_sets_of_books gsob , ar_system_parameters aps where gsob.set_of_books_id = aps.set_of_books_id and :$flex$.reporting_context is null ) and fsav.attribute_value = 'Y' and fifsv.application_id = fsav.application_id and fifsv.id_flex_code = fsav.id_flex_code and fifsv.id_flex_num = fsav.id_flex_num and fifsv.application_column_name = fsav.application_column_name ), 'Account') from dual US Account Segment Label 10 40 :p_in_as_of_date_low Date sysdate Y US As Of Date 11 70 :p_credit_option_dsp LOV custom Y select l.meaning value, l.description description from ar_lookups l where l.lookup_type='OPEN_CREDITS' and l.enabled_flag='Y' and sysdate between nvl(l.start_date_active,sysdate) and nvl(l.end_date_active,sysdate) order by l.meaning select meaning from ar_lookups where lookup_type = 'OPEN_CREDITS' and lookup_code = 'SUMMARY' Y US Show On Account 12 80 :p_risk_option LOV Oracle AR_SHOW_RISK Y select l.lookup_code id, l.meaning value, null description from ar_lookups l where l.lookup_type='SHOW_RISK' order by l.meaning,l.lookup_code NONE Y US Show Receipts At Risk 13 120 2=2 hp.party_name = :p_in_customer_name LOV Oracle AR_CUSTOMER_NAME_WIDE Y Y select party.party_name id, party.party_name value, null description from hz_cust_accounts cu,hz_parties party where cu.party_id=party.party_id and exists (select cas.cust_account_id from hz_cust_acct_sites cas where cas.cust_account_id=cu.cust_account_id) order by value,description US Customer Name 14 150 2=2 hca.account_number = :p_in_customer_num LOV Oracle AR_CUSTOMER_NUMBER Y Y select cust.account_number id, cust.account_number value, null description from hz_cust_accounts cust,hz_parties party where cust.party_id=party.party_id order by cust.account_number US Customer Number 15 180 :p_in_currency LOV Oracle AR_SRS_CURRENCY Y select currency_code id, currency_code value, name description from fnd_currencies_vl where currency_flag='Y' and enabled_flag in ('Y','N') order by currency_code US Entered Currency 16 190 3=3 x.invnum=:p_trx_number Char US Transaction Number