AR Aging - 7 Buckets - By Salesperson/Agent
Description
Categories: Enginatics
Repository: Github
Repository: Github
Application: Receivables
Source: Aging - 7 Buckets - By Salesperson/Agent Report
Short Name: ARXAGRW
Package: XXEN_AR_ARXAGRW_PKG
Source: Aging - 7 Buckets - By Salesperson/Agent Report
Short Name: ARXAGRW
Package: XXEN_AR_ARXAGRW_PKG
Run
AR Aging - 7 Buckets - By Salesperson/Agent and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
select x1.ledger ledger, x1.operating_unit operating_unit, fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_bal_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') "&lp_bal_seg_p", fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_acc_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') "&lp_acc_seg_p", fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_accounting_flexfield', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'ALL', 'Y', 'VALUE') gl_account_segments, x1.sort_field1 salesperson, x1.cust_name customer, x1.cust_no customer_Number, &lp_invoice_cols_s x1.revaluation_from_currency amounts_currency, nvl(sum(x1.amt_due_original),0) original_amount, nvl(sum(x1.amt_due_remaining),0) outstanding_amount, &lp_on_acc_summ_cols &lp_aging_amount_cols &lp_aging_pct_cols -- Revaluation Columns &lp_reval_columns -- fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_bal_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION') "&lp_bal_seg_p Desc", fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_acc_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') "&lp_acc_seg_p Desc", fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_accounting_flexfield', 'SQLGL', 'GL#', x1.chart_of_accounts_id, NULL, x1.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') gl_account_segments_desc from ( --start x1 select x.ledger, (select haou.name from hr_all_organization_units haou where haou.organization_id = x.org_id) operating_unit, x.sort_field1, x.sort_field2, x.cust_name, x.cust_no, x.class, x.cons_billing_number, x.invnum, x.invoice_currency_code, x.due_date, x.days_past_due, x.amt_due_original, x.amount_adjusted, x.amount_applied, x.amount_credited, x.gl_date, x.data_converted, x.ps_exchange_rate, x.code_combination_id, x.chart_of_accounts_id, x.invoice_type, x.b0, x.b1, x.b2, x.b3, x.b4, x.b5, x.b6, case when (:p_credit_option = 'SUMMARY' and x.class in ('PMT','CM','CLAIM')) or (:p_risk_option = 'SUMMARY' AND x.invoice_type = :p_risk_meaning) then to_number(null) else x.amt_due_remaining end amt_due_remaining, case when :p_credit_option = 'SUMMARY' AND x.class = 'PMT' then x.amt_due_remaining else null end on_account_amount_cash, case when :p_credit_option = 'SUMMARY' AND x.class = 'CM' then x.amt_due_remaining else null end on_account_amount_credit, case when :p_risk_option = 'SUMMARY' AND x.invoice_type = :p_risk_meaning then x.amt_due_remaining else null end on_account_amount_risk, case when :p_credit_option = 'SUMMARY' AND x.class = 'CLAIM' then x.amt_due_remaining else null end cust_amount_claim, nvl(:p_in_currency,x.functional_currency) revaluation_from_currency, decode(nvl(:p_in_currency,x.functional_currency),:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where nvl(:p_in_currency,x.functional_currency)=gdr.from_currency and gdr.to_currency=:p_reval_currency and :p_reval_conv_date=gdr.conversion_date and gdct.user_conversion_type=:p_reval_conv_type and gdct.conversion_type=gdr.conversion_type)) reval_conv_rate, xxen_util.meaning(:p_reporting_level,'FND_MO_REPORTING_LEVEL',0) reporting_level, case :p_reporting_level when '1000' then x.ledger when '3000' then (select haou.name from hr_all_organization_units haou where haou.organization_id = x.org_id) end reporting_entity from ( --start x select substrb(party.party_name,1,50) cust_name, cust_acct.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) inv_tid, site.site_use_id contact_site_id, loc.state cust_state, loc.city cust_city, 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(cust_acct.cust_account_id,-999) cust_id, ps.payment_schedule_id payment_sched_id, ps.class class, ps.due_date 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.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, case when xxen_ar_arxagrw_pkg.bucket_line_type(0) is not null then arpt_sql_func_util.bucket_function ( xxen_ar_arxagrw_pkg.bucket_line_type(0), dh.amount_in_dispute, ps.amount_adjusted_pending, xxen_ar_arxagrw_pkg.bucket_days_from(0), xxen_ar_arxagrw_pkg.bucket_days_to(0), ps.due_date, xxen_ar_arxagrw_pkg.bucket_category, :p_in_as_of_date_low ) else to_number(null) end b0, case when xxen_ar_arxagrw_pkg.bucket_line_type(0) is not null then arpt_sql_func_util.bucket_function ( xxen_ar_arxagrw_pkg.bucket_line_type(1), dh.amount_in_dispute, ps.amount_adjusted_pending, xxen_ar_arxagrw_pkg.bucket_days_from(1), xxen_ar_arxagrw_pkg.bucket_days_to(1), ps.due_date, xxen_ar_arxagrw_pkg.bucket_category, :p_in_as_of_date_low ) else to_number(null) end b1, case when xxen_ar_arxagrw_pkg.bucket_line_type(0) is not null then arpt_sql_func_util.bucket_function ( xxen_ar_arxagrw_pkg.bucket_line_type(2), dh.amount_in_dispute, ps.amount_adjusted_pending, xxen_ar_arxagrw_pkg.bucket_days_from(2), xxen_ar_arxagrw_pkg.bucket_days_to(2), ps.due_date, xxen_ar_arxagrw_pkg.bucket_category, :p_in_as_of_date_low ) else to_number(null) end b2, case when xxen_ar_arxagrw_pkg.bucket_line_type(0) is not null then arpt_sql_func_util.bucket_function ( xxen_ar_arxagrw_pkg.bucket_line_type(3), dh.amount_in_dispute, ps.amount_adjusted_pending, xxen_ar_arxagrw_pkg.bucket_days_from(3), xxen_ar_arxagrw_pkg.bucket_days_to(3), ps.due_date, xxen_ar_arxagrw_pkg.bucket_category, :p_in_as_of_date_low ) else to_number(null) end b3, case when xxen_ar_arxagrw_pkg.bucket_line_type(0) is not null then arpt_sql_func_util.bucket_function ( xxen_ar_arxagrw_pkg.bucket_line_type(4), dh.amount_in_dispute, ps.amount_adjusted_pending, xxen_ar_arxagrw_pkg.bucket_days_from(4), xxen_ar_arxagrw_pkg.bucket_days_to(4), ps.due_date, xxen_ar_arxagrw_pkg.bucket_category, :p_in_as_of_date_low ) else to_number(null) end b4, case when xxen_ar_arxagrw_pkg.bucket_line_type(0) is not null then arpt_sql_func_util.bucket_function ( xxen_ar_arxagrw_pkg.bucket_line_type(5), dh.amount_in_dispute, ps.amount_adjusted_pending, xxen_ar_arxagrw_pkg.bucket_days_from(5), xxen_ar_arxagrw_pkg.bucket_days_to(5), ps.due_date, xxen_ar_arxagrw_pkg.bucket_category, :p_in_as_of_date_low ) else to_number(null) end b5, case when xxen_ar_arxagrw_pkg.bucket_line_type(0) is not null then arpt_sql_func_util.bucket_function ( xxen_ar_arxagrw_pkg.bucket_line_type(6), dh.amount_in_dispute, ps.amount_adjusted_pending, xxen_ar_arxagrw_pkg.bucket_days_from(6), xxen_ar_arxagrw_pkg.bucket_days_to(6), ps.due_date, xxen_ar_arxagrw_pkg.bucket_category, :p_in_as_of_date_low ) else to_number(null) end b6, 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 cust_acct, hz_parties party, (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.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.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.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.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.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.due_date 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, ct.primary_salesrep_id primary_salesrep_id, ps.due_date 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 adj.customer_trx_id = ct.customer_trx_id and xxen_ar_arxagrw_pkg.include_org_id(ps.org_id) = 'Y' and ct.org_id = ps.org_id and adj.org_id = ps.org_id ) a group by a.customer_id, a.customer_site_use_id, a.customer_trx_id, a.payment_schedule_id, a.class, 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 |