AR Customer Credit Snapshot

with q_cust as
(
select
 sob.name ledger,
 haouv.name operating_unit,
 sob.currency_code functional_currency,
 cur.precision functional_currency_precision,
 sob.set_of_books_id,
 param.org_id qcust_org_id,
 cust_acct.cust_account_id customer_id,
 cust_acct.account_number customer_number,
 party.party_name customer_name,
 round((trunc(sysdate) - cust_acct.creation_date)/365,1) customer_age,
 round((trunc(sysdate) - acct_site.creation_date)/365,1) address_age,
 acct_site.cust_acct_site_id address_id,
 loc.address1 address1,
 loc.address2 address2,
 loc.address3 address3,
 loc.address4 address4,
 decode(loc.city, null, null, loc.city || ',') city,
 nvl(loc.state, loc.province) state,
 loc.postal_code postal_code,
 terr.territory_short_name country,
 site_uses.site_use_id site_use_id,
 site_uses.location location,
 site_uses.primary_flag,
 hcpc.name profile_class,
 cp.cust_account_profile_id customer_profile_id,
 cp.statement_cycle_id,
 ar_arxccs_xmlp_pkg.c_last_credit_memo_formulaform(sob.currency_code,cust_acct.cust_account_id, site_uses.site_use_id) c_last_credit_memo_formula,
 ar_arxccs_xmlp_pkg.c_data_not_foundformula(substrb ( party.party_name , 1 , 50 )) c_data_not_found,
 ar_arxccs_xmlp_pkg.c_customer_ageformula(round ( ( trunc ( sysdate ) - cust_acct.creation_date ) / 365 , 1 )) c_customer_age,
 ar_arxccs_xmlp_pkg.sel_contactformula(acct_site.cust_acct_site_id) sel_contact,
 ar_arxccs_xmlp_pkg.c_address_ageformula(round ( ( trunc ( sysdate ) - acct_site.creation_date ) / 365 , 1 )) c_address_age,
 ar_arxccs_xmlp_pkg.c_city_state_zipformula(decode ( loc.city , null , null , loc.city || ',' ), nvl ( loc.state , loc.province ), loc.postal_code) c_city_state_zip,
 ar_arxccs_xmlp_pkg.c_credit_summaryformula(cust_acct.cust_account_id, site_uses.site_use_id) c_credit_summary,
 ar_arxccs_xmlp_pkg.c_last_invoice_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_invoice_formula,
 ar_arxccs_xmlp_pkg.c_guarantee_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_guarantee_formula,
 ar_arxccs_xmlp_pkg.c_last_deposit_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_deposit_formula,
 ar_arxccs_xmlp_pkg.c_last_dm_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_dm_formula,
 ar_arxccs_xmlp_pkg.c_last_cb_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_cb_formula,
 ar_arxccs_xmlp_pkg.c_last_payment_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_payment_formula,
 ar_arxccs_xmlp_pkg.c_last_adj_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_adj_formula,
 ar_arxccs_xmlp_pkg.c_last_writeoff_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_writeoff_formula,
 ar_arxccs_xmlp_pkg.c_last_statement_formulaformul(cust_acct.cust_account_id, site_uses.site_use_id) c_last_statement_formula,
 ar_arxccs_xmlp_pkg.c_last_dn_formulaformula(cust_acct.cust_account_id, site_uses.site_use_id) c_last_dn_formula,
 ar_arxccs_xmlp_pkg.c_last_nsf_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_nsf_formula,
 ar_arxccs_xmlp_pkg.c_last_contact_formulaformula(sob.currency_code, cust_acct.cust_account_id, site_uses.site_use_id) c_last_contact_formula,
 ar_arxccs_xmlp_pkg.c_last_hold_formulaformula(cust_acct.cust_account_id, site_uses.site_use_id) c_last_hold_formula,
 ar_arxccs_xmlp_pkg.c_contact_p c_contact,
 ar_arxccs_xmlp_pkg.c_phone_number_p c_phone_number,
 ar_arxccs_xmlp_pkg.c_profile_site_use_id_p c_profile_site_use_id,
 ar_arxccs_xmlp_pkg.c_customer_profile_id_p c_customer_profile_id,
 ar_arxccs_xmlp_pkg.c_cred_summ_collector_p c_cred_summ_collector,
 ar_arxccs_xmlp_pkg.c_cred_summ_exempt_dun_p c_cred_summ_exempt_dun,
 ar_arxccs_xmlp_pkg.c_cred_summ_terms_p c_cred_summ_terms,
 ar_arxccs_xmlp_pkg.c_cred_summ_account_status_p c_cred_summ_account_status,
 ar_arxccs_xmlp_pkg.c_cred_summ_credit_hold_p c_cred_summ_credit_hold,
 ar_arxccs_xmlp_pkg.c_cred_summ_risk_code_p c_cred_summ_risk_code,
 ar_arxccs_xmlp_pkg.c_cred_summ_credit_rating_p c_cred_summ_credit_rating,
 ar_arxccs_xmlp_pkg.c_cred_summ_limit_tolerance_p c_cred_summ_limit_tolerance,
 ar_arxccs_xmlp_pkg.c_cred_summ_lt_exp_date_p c_cred_summ_limit_expire_date,
 ar_arxccs_xmlp_pkg.c_last_invoice_days_since_p c_last_invoice_days_since,
 ar_arxccs_xmlp_pkg.c_last_invoice_date_p c_last_invoice_date,
 ar_arxccs_xmlp_pkg.c_last_invoice_converted_p c_last_invoice_converted,
 ar_arxccs_xmlp_pkg.c_last_invoice_amount_p c_last_invoice_amount,
 ar_arxccs_xmlp_pkg.c_last_invoice_currency_p c_last_invoice_currency,
 ar_arxccs_xmlp_pkg.c_last_invoice_type_p c_last_invoice_type,
 ar_arxccs_xmlp_pkg.c_last_invoice_number_p c_last_invoice_number,
 ar_arxccs_xmlp_pkg.c_last_cm_rel_invoice_p c_last_cm_rel_invoice,
 ar_arxccs_xmlp_pkg.c_last_cm_converted_p c_last_cm_converted,
 ar_arxccs_xmlp_pkg.c_last_cm_amount_p c_last_cm_amount,
 ar_arxccs_xmlp_pkg.c_last_cm_id_p c_last_cm_id,
 ar_arxccs_xmlp_pkg.c_last_cm_prev_trx_p c_last_cm_prev_trx,
 ar_arxccs_xmlp_pkg.c_last_cm_days_since_p c_last_cm_days_since,
 ar_arxccs_xmlp_pkg.c_last_cm_date_p c_last_cm_date,
 ar_arxccs_xmlp_pkg.c_last_cm_currency_p c_last_cm_currency,
 ar_arxccs_xmlp_pkg.c_last_cm_type_p c_last_cm_type,
 ar_arxccs_xmlp_pkg.c_last_guar_days_since_p c_last_guar_days_since,
 ar_arxccs_xmlp_pkg.c_last_guar_date_p c_last_guar_date,
 ar_arxccs_xmlp_pkg.c_last_guar_converted_p c_last_guar_converted,
 ar_arxccs_xmlp_pkg.c_last_guar_amount_p c_last_guar_amount,
 ar_arxccs_xmlp_pkg.c_last_guar_currency_p c_last_guar_currency,
 ar_arxccs_xmlp_pkg.c_last_guar_type_p c_last_guar_type,
 ar_arxccs_xmlp_pkg.c_last_guar_number_p c_last_guar_number,
 ar_arxccs_xmlp_pkg.c_last_dep_days_since_p c_last_dep_days_since,
 ar_arxccs_xmlp_pkg.c_last_dep_date_p c_last_dep_date,
 ar_arxccs_xmlp_pkg.c_last_dep_converted_p c_last_dep_converted,
 ar_arxccs_xmlp_pkg.c_last_dep_amount_p c_last_dep_amount,
 ar_arxccs_xmlp_pkg.c_last_dep_currency_p c_last_dep_currency,
 ar_arxccs_xmlp_pkg.c_last_dep_type_p c_last_dep_type,
 ar_arxccs_xmlp_pkg.c_last_dep_number_p c_last_dep_number,
 ar_arxccs_xmlp_pkg.c_last_dm_days_since_p c_last_dm_days_since,
 ar_arxccs_xmlp_pkg.c_last_dm_date_p c_last_dm_date,
 ar_arxccs_xmlp_pkg.c_last_dm_converted_p c_last_dm_converted,
 ar_arxccs_xmlp_pkg.c_last_dm_amount_p c_last_dm_amount,
 ar_arxccs_xmlp_pkg.c_last_dm_currency_p c_last_dm_currency,
 ar_arxccs_xmlp_pkg.c_last_dm_type_p c_last_dm_type,
 ar_arxccs_xmlp_pkg.c_last_dm_number_p c_last_dm_number,
 ar_arxccs_xmlp_pkg.c_last_cb_days_since_p c_last_cb_days_since,
 ar_arxccs_xmlp_pkg.c_last_cb_date_p c_last_cb_date,
 ar_arxccs_xmlp_pkg.c_last_cb_converted_p c_last_cb_converted,
 ar_arxccs_xmlp_pkg.c_last_cb_amount_p c_last_cb_amount,
 ar_arxccs_xmlp_pkg.c_last_cb_currency_p c_last_cb_currency,
 ar_arxccs_xmlp_pkg.c_last_cb_type_p c_last_cb_type,
 ar_arxccs_xmlp_pkg.c_last_cm_number_p c_last_cm_number,
 ar_arxccs_xmlp_pkg.c_last_cb_number_p c_last_cb_number,
 ar_arxccs_xmlp_pkg.c_last_payment_rel_invoice_p c_last_payment_rel_invoice,
 ar_arxccs_xmlp_pkg.c_last_payment_days_since_p c_last_payment_days_since,
 ar_arxccs_xmlp_pkg.c_last_payment_date_p c_last_payment_date,
 ar_arxccs_xmlp_pkg.c_last_payment_converted_p c_last_payment_converted,
 ar_arxccs_xmlp_pkg.c_last_payment_amount_p c_last_payment_amount,
 ar_arxccs_xmlp_pkg.c_last_payment_currency_p c_last_payment_currency,
 ar_arxccs_xmlp_pkg.c_last_payment_type_p c_last_payment_type,
 ar_arxccs_xmlp_pkg.c_last_payment_number_p c_last_payment_number,
 ar_arxccs_xmlp_pkg.c_last_adj_days_since_p c_last_adj_days_since,
 ar_arxccs_xmlp_pkg.c_last_adj_date_p c_last_adj_date,
 ar_arxccs_xmlp_pkg.c_last_adj_converted_p c_last_adj_converted,
 ar_arxccs_xmlp_pkg.c_last_adj_amount_p c_last_adj_amount,
 ar_arxccs_xmlp_pkg.c_last_adj_currency_p c_last_adj_currency,
 ar_arxccs_xmlp_pkg.c_last_adj_rel_invoice_p c_last_adj_rel_invoice,
 ar_arxccs_xmlp_pkg.c_last_adj_type_p c_last_adj_type,
 ar_arxccs_xmlp_pkg.c_last_adj_number_p c_last_adj_number,
 ar_arxccs_xmlp_pkg.c_last_wo_days_since_p c_last_wo_days_since,
 ar_arxccs_xmlp_pkg.c_last_wo_date_p c_last_wo_date,
 ar_arxccs_xmlp_pkg.c_last_wo_converted_p c_last_wo_converted,
 ar_arxccs_xmlp_pkg.c_last_wo_amount_p c_last_wo_amount,
 ar_arxccs_xmlp_pkg.c_last_wo_currency_p c_last_wo_currency,
 ar_arxccs_xmlp_pkg.c_last_wo_rel_invoice_p c_last_wo_rel_invoice,
 ar_arxccs_xmlp_pkg.c_last_wo_type_p c_last_wo_type,
 ar_arxccs_xmlp_pkg.c_last_wo_number_p c_last_wo_number,
 ar_arxccs_xmlp_pkg.c_last_stmnt_next_trx_date_p c_last_stmnt_next_trx_date,
 ar_arxccs_xmlp_pkg.c_last_st_date_p c_last_st_date,
 ar_arxccs_xmlp_pkg.c_last_st_type_p c_last_st_type,
 ar_arxccs_xmlp_pkg.c_last_st_number_p c_last_st_number,
 ar_arxccs_xmlp_pkg.c_last_st_days_since_p c_last_st_days_since,
 ar_arxccs_xmlp_pkg.c_last_dn_days_since_p c_last_dn_days_since,
 ar_arxccs_xmlp_pkg.c_last_dn_date_p c_last_dn_date,
 ar_arxccs_xmlp_pkg.c_last_dn_currency_p c_last_dn_currency,
 ar_arxccs_xmlp_pkg.c_last_dn_amount_p c_last_dn_amount,
 ar_arxccs_xmlp_pkg.c_last_dn_type_p c_last_dn_type,
 ar_arxccs_xmlp_pkg.c_last_dn_number_p c_last_dn_number,
 ar_arxccs_xmlp_pkg.c_last_nsf_currency_p c_last_nsf_currency,
 ar_arxccs_xmlp_pkg.c_last_nsf_days_since_p c_last_nsf_days_since,
 ar_arxccs_xmlp_pkg.c_last_nsf_date_p c_last_nsf_date,
 ar_arxccs_xmlp_pkg.c_last_nsf_converted_p c_last_nsf_converted,
 ar_arxccs_xmlp_pkg.c_last_nsf_amount_p c_last_nsf_amount,
 ar_arxccs_xmlp_pkg.c_last_nsf_type_p c_last_nsf_type,
 ar_arxccs_xmlp_pkg.c_last_nsf_number_p c_last_nsf_number,
 ar_arxccs_xmlp_pkg.c_last_contact_days_since_p c_last_contact_days_since,
 ar_arxccs_xmlp_pkg.c_last_contact_date_p c_last_contact_date,
 ar_arxccs_xmlp_pkg.c_last_contact_amount_p c_last_contact_amount,
 ar_arxccs_xmlp_pkg.c_last_contact_converted_p c_last_contact_converted,
 ar_arxccs_xmlp_pkg.c_last_contact_currency_p c_last_contact_currency,
 ar_arxccs_xmlp_pkg.c_last_contact_rel_invoice_p c_last_contact_rel_invoice,
 ar_arxccs_xmlp_pkg.c_last_contact_number_p c_last_contact_number,
 ar_arxccs_xmlp_pkg.c_last_hold_days_since_p c_last_hold_days_since,
 ar_arxccs_xmlp_pkg.c_last_hold_date_p c_last_hold_date,
 ar_arxccs_xmlp_pkg.c_last_hold_amount_p c_last_hold_amount,
 ar_arxccs_xmlp_pkg.c_last_hold_number_p c_last_hold_number
from
 ar_system_parameters_all param,
 gl_sets_of_books sob,
 hr_all_organization_units_vl haouv,
 fnd_currencies cur,
 hz_cust_accounts cust_acct,
 hz_parties party,
 hz_cust_acct_sites acct_site,
 hz_party_sites party_site,
 hz_locations loc,
 hz_cust_site_uses site_uses,
 fnd_territories_vl terr,
 ar_collectors coll,
 hz_customer_profiles cp,
 hz_cust_profile_classes hcpc
where
 1=1 and
 param.set_of_books_id = sob.set_of_books_id and
 param.org_id = haouv.organization_id (+) and
 sob.currency_code = cur.currency_code and
 cp.cust_account_id = cust_acct.cust_account_id and
 cust_acct.party_id = party.party_id and
 cp.collector_id = coll.collector_id and
 cust_acct.cust_account_id = acct_site.cust_account_id and
 nvl(param.org_id,-99) = nvl(acct_site.org_id,-99) and
 site_uses.cust_acct_site_id = acct_site.cust_acct_site_id and
 nvl(site_uses.org_id,-99) = nvl(acct_site.org_id,-99) and
 site_uses.status = 'A' and
 acct_site.party_site_id = party_site.party_site_id and
 loc.location_id = party_site.location_id and
 site_uses.site_use_code = 'BILL_TO' and
 site_uses.status = 'A' and
 loc.country = terr.territory_code and
 cp.cust_account_profile_id =
  (select
    max(cp2.cust_account_profile_id)
   from
    hz_customer_profiles cp2
   where
    cust_acct.cust_account_id = cp2.cust_account_id and
    (site_uses.site_use_id = cp2.site_use_id or cp2.site_use_id is null )
  ) and
 cp.profile_class_id = hcpc.profile_class_id
 &p_org_where_param
),
q_currencies as
(
 select
 hca.cust_account_id customer_id,
 hcsua.site_use_id site_use_id,
 hcsua.org_id org_id,
 hcpa.cust_account_profile_id,
 hcpa.currency_code
 from
 hz_cust_accounts hca,
 hz_cust_acct_sites_all hcasa,
 hz_cust_site_uses_all hcsua,
 hz_cust_profile_amts hcpa
 where
 hca.cust_account_id = hcasa.cust_account_id and
 hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and
 nvl(hcasa.org_id,-99) = nvl(hcsua.org_id,-99) and
 hca.cust_account_id = hcpa.cust_account_id and
 hcpa.cust_account_profile_id =
 (select
  max(hcp2.cust_account_profile_id)
  from
  hz_customer_profiles hcp2
  where
  hcp2.cust_account_id = hca.cust_account_id and
  (hcp2.site_use_id = hcsua.site_use_id or hcp2.site_use_id is null )
 )
 union
 select
 apsa.customer_id,
 apsa.customer_site_use_id,
 apsa.org_id,
 hcp.cust_account_profile_id,
 apsa.invoice_currency_code
 from
 ar_payment_schedules_all apsa,
 hz_customer_profiles hcp
 where
 apsa.class not in ('CM', 'PMT') and
 apsa.customer_id = hcp.cust_account_id and
 hcp.cust_account_profile_id =
 (select
  max(hcp2.cust_account_profile_id)
  from
  hz_customer_profiles hcp2
  where
  hcp2.cust_account_id = apsa.customer_id and
  (hcp2.site_use_id = apsa.customer_site_use_id or hcp2.site_use_id is null )
 )
),
q_buckets as
(
 select
  ps.customer_id ps_customer_id,
  ps.customer_site_use_id ps_site_use_id,
  ps.org_id ps_org_id,
  ps.invoice_currency_code currency_bucket,
  sum(ps.amount_due_remaining) aging_balance_outstanding,
  sum(decode(:rp_bucket_line_type_0,
             'DISPUTE_ONLY',   decode(nvl(ps.amount_in_dispute,0),0,0,1),
             'PENDADJ_ONLY',   decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
             'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1),
                               case when ceil(trunc(sysdate)-ps.due_date) between to_number(:rp_bucket_days_from_0) and to_number(:rp_bucket_days_to_0) then 1 else 0 end
                               * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
                               * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
            )
      * ps.amount_due_remaining * 1
  ) total_cust_b0,
  sum(decode(:rp_bucket_line_type_1,
             'DISPUTE_ONLY',   decode(nvl(ps.amount_in_dispute,0),0,0,1),
             'PENDADJ_ONLY',   decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
             'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1),
                               case when ceil(trunc(sysdate)-ps.due_date) between to_number(:rp_bucket_days_from_1) and to_number(:rp_bucket_days_to_1) then 1 else 0 end
                               * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
                               * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
            )
      * ps.amount_due_remaining * 1
  ) total_cust_b1,
  sum(decode(:rp_bucket_line_type_2,
             'DISPUTE_ONLY',   decode(nvl(ps.amount_in_dispute,0),0,0,1),
             'PENDADJ_ONLY',   decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
             'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1),
                               case when ceil(trunc(sysdate)-ps.due_date) between to_number(:rp_bucket_days_from_2) and to_number(:rp_bucket_days_to_2) then 1 else 0 end
                               * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
                               * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
            )
      * ps.amount_due_remaining * 1
  ) total_cust_b2,
  sum(decode(:rp_bucket_line_type_3,
             'DISPUTE_ONLY',   decode(nvl(ps.amount_in_dispute,0),0,0,1),
             'PENDADJ_ONLY',   decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
             'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1),
                               case when ceil(trunc(sysdate)-ps.due_date) between to_number(:rp_bucket_days_from_3) and to_number(:rp_bucket_days_to_3) then 1 else 0 end
                               * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
                               * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
            )
      * ps.amount_due_remaining * 1
  ) total_cust_b3,
  sum(decode(:rp_bucket_line_type_4,
             'DISPUTE_ONLY',   decode(nvl(ps.amount_in_dispute,0),0,0,1),
             'PENDADJ_ONLY',   decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
             'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1),
                               case when ceil(trunc(sysdate)-ps.due_date) between to_number(:rp_bucket_days_from_4) and to_number(:rp_bucket_days_to_4) then 1 else 0 end
                               * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) *
                                 decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
            )
      * ps.amount_due_remaining * 1
  ) total_cust_b4,
  sum(decode(:rp_bucket_line_type_5,
             'DISPUTE_ONLY',   decode(nvl(ps.amount_in_dispute,0),0,0,1),
             'PENDADJ_ONLY',   decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
             'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1),
                               case when ceil(trunc(sysdate)-ps.due_date) between to_number(:rp_bucket_days_from_5) and to_number(:rp_bucket_days_to_5) then 1 else 0 end
                                       * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
                                       * decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
            )
      * ps.amount_due_remaining * 1
  ) total_cust_b5,
  sum(decode(:rp_bucket_line_type_6,
             'DISPUTE_ONLY',   decode(nvl(ps.amount_in_dispute,0),0,0,1),
             'PENDADJ_ONLY',   decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
             'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0), 0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1), 1),
                               case when ceil(trunc(sysdate)-ps.due_date) between to_number(:rp_bucket_days_from_6) and to_number(:rp_bucket_days_to_6) then 1 else 0 end
                               * decode(nvl(ps.amount_in_dispute,0), 0, 1, decode('', 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) *
                               decode(nvl(ps.amount_adjusted_pending,0), 0, 1, decode('', 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))
            )
      * ps.amount_due_remaining * 1
  ) total_cust_b6
 from
  ar_payment_schedules_all ps
 where
  ps.class not in ('CM', 'PMT')
  &p_org_where_ps
 group by
  ps.customer_id,
  ps.customer_site_use_id,
  ps.org_id,
  ps.invoice_currency_code
),
q_stmt_cycles as
(
 select distinct
 astc.statement_cycle_id,
 first_value(astc.name) over (partition by astc.statement_cycle_id order by astcd.statement_date desc,astcd.statement_cycle_date_id desc) statement_type,
 first_value(astcd.statement_date) over (partition by astc.statement_cycle_id order by astcd.statement_date desc,astcd.statement_cycle_date_id desc) statement_date,
 trunc(trunc(sysdate) - first_value(astcd.statement_date) over (partition by astc.statement_cycle_id order by astcd.statement_date desc,astcd.statement_cycle_date_id desc)) days_since
 from
 ar_statement_cycles astc,
 ar_statement_cycle_dates astcd
 where
 astc.statement_cycle_id = astcd.statement_cycle_id and
 astcd.printed = 'Y'
)
--
-- select main query starts here
select /*+ ordered push_pred(curr) push_pred(qb) push_pred(qsc) */
 qc.ledger,
 qc.operating_unit,
 qc.functional_currency,
 -- function call ensures the placeholder columns are populated
 nvl2(qc.c_credit_summary ||
      qc.sel_contact ||
      qc.c_last_invoice_formula ||
      qc.c_last_credit_memo_formula ||
      qc.c_guarantee_formula ||
      qc.c_last_deposit_formula ||
      qc.c_last_dm_formula ||
      qc.c_last_cb_formula ||
      qc.c_last_payment_formula ||
      qc.c_last_adj_formula ||
      qc.c_last_writeoff_formula ||
      qc.c_last_statement_formula ||
      qc.c_last_dn_formula ||
      qc.c_last_nsf_formula ||
      qc.c_last_contact_formula ||
      qc.c_last_hold_formula,
      qc.customer_name,
      qc.customer_name
     ) customer_name,
 qc.customer_number customer_number,
 --
 qc.location,
 qc.address1,
 qc.address2,
 qc.address3,
 qc.address4,
 qc.city,
 qc.state,
 qc.postal_code,
 qc.country,
 --qc.c_city_state_zip,
 qc.c_contact contact,
 qc.c_phone_number phone_number,
 qc.c_customer_age customer_age,
 qc.c_address_age address_age,
 --
 -- Customer Level Credit Summary
 --
 --qc.c_credit_summary,
 qc.profile_class,
 nvl2(qc.c_profile_site_use_id,'Site','Account') profile_level,
 qc.c_cred_summ_limit_tolerance credit_tolerance,
 qc.c_cred_summ_credit_rating credit_rating,
 qc.c_cred_summ_risk_code risk_code,
 qc.c_cred_summ_credit_hold credit_hold,
 qc.c_cred_summ_account_status account_status,
 qc.c_cred_summ_terms standard_terms,
 qc.c_cred_summ_exempt_dun exempt_from_dunning,
 qc.c_cred_summ_collector collector,
 --qc.c_cred_summ_limit_expire_date,
 --
 -- Currency
 --
 -- the function calls are required to populate the place holder columns
 nvl2(ar_arxccs_xmlp_pkg.cf_currency_lookupformula(hcpa.cust_account_id, curr.currency_code, hcpa.site_use_id, qc.site_use_id) ||
      ar_arxccs_xmlp_pkg.c_currency_lookupformula(qc.site_use_id, curr.currency_code) ||
      ar_arxccs_xmlp_pkg.c_credit_amounts_calcformu0114(qc.customer_id, qc.site_use_id,qc.functional_currency,curr.currency_code,hcpa.overall_credit_limit) ||
      ar_arxccs_xmlp_pkg.c_compute_amtformula(qc.functional_currency, qc.customer_id, qc.site_use_id, curr.currency_code, qb.aging_balance_outstanding) ||
      ar_arxccs_xmlp_pkg.c_high_inv_formulaformula(qc.functional_currency, qc.customer_id, qc.site_use_id, curr.currency_code) ||
      ar_arxccs_xmlp_pkg.c_rolling_summary_calcformula(qc.functional_currency, qc.customer_id, qc.site_use_id, curr.currency_code),
      curr.currency_code,curr.currency_code
      ) currency,
 --
 -- G_CREDIT_LIMITS
 --
 hcpa.overall_credit_limit credit_limit, -- 1,
 case when hcpa.currency_code is not null
 then case when hcpa.overall_credit_limit is null then ar_arxccs_xmlp_pkg.rp_no_limit_p end
 else ar_arxccs_xmlp_pkg.rp_no_limit_p
 end no_credit_limit,
 hcpa.trx_credit_limit order_credit_limit, -- 1,
 case when hcpa.currency_code is not null then ar_arxccs_xmlp_pkg.c_cred_summ_available1_p end available_credit, -- c_cred_summ_available1,
 case when hcpa.currency_code is not null then ar_arxccs_xmlp_pkg.c_cred_summ_exceeded1_p end exceeded_credit_amount, -- c_cred_summ_exceeded1,
 case when hcpa.currency_code is not null then ar_arxccs_xmlp_pkg.default_flag_p end default_flag, -- default_flag,
 --
 -- G_BUCKETS
 --
 -- aging
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.cp_default_flag_p end default_flag,
 case when qb.currency_bucket is not null then qb.aging_balance_outstanding end aging_outstanding_balance,
 &lp_aging_amount_cols
 &lp_aging_percent_cols
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_aging_credit_p end  aging_on_account_credit, -- c_aging_credit,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_aging_unapplied_p end  aging_unapplied_cash, -- c_aging_unapplied,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_aging_on_account_p end  aging_on_account_cash, -- c_aging_on_account,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_adjusted_balance_p end  aging_adjusted_balance, -- c_adjusted_balance,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_aging_in_collection_p end  aging_in_collection, -- c_aging_in_collection,
 -- customer history
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_cust_hist_high_invoice_amt_p end  hist_largest_invoice_amount, -- c_cust_hist_high_invoice_amt,
 case when qb.currency_bucket is not null then to_date(ar_arxccs_xmlp_pkg.c_cust_hist_high_inv_date_p) end  hist_largest_invoice_date, -- c_cust_hist_high_invoice_date,
 -- not using these function as it contains a bug compared to the rdf version
 --  ar_arxccs_xmlp_pkg.c_cust_hist_high_limit_amtform(qc.customer_id, qc.site_use_id)  c_cust_hist_high_limit_amt,
 --  ar_arxccs_xmlp_pkg.c_cust_hist_high_limit_date_p) c_cust_hist_high_limit_date,
 case when qb.currency_bucket is not null
 then
   --nvl(
    (select
     max(h.credit_limit)
     from
     ar_credit_histories h
     where
     h.customer_id = qc.customer_id and
     (h.site_use_id = qc.site_use_id or
      (h.site_use_id is null and not exists (select 1 from ar_credit_histories h2 where h2.site_use_id = qc.site_use_id and h2.customer_id = qc.customer_id)
      )
     ) and
     nvl(h.credit_limit,0) != 0
    )
   --,decode(nvl(hcpa.overall_credit_limit,0),0,to_number(null),hcpa.overall_credit_limit)
   --)
 end hist_highest_credit_limit,
 case when qb.currency_bucket is not null
 and (select
      max(h.credit_limit)
      from
      ar_credit_histories h
      where
      h.customer_id = qc.customer_id and
      (h.site_use_id = qc.site_use_id or
       (h.site_use_id is null and not exists (select 1 from ar_credit_histories h2 where h2.site_use_id = qc.site_use_id and h2.customer_id = qc.customer_id)
       )
      ) and
      nvl(h.credit_limit,0) != 0
     ) is null
 then ar_arxccs_xmlp_pkg.rp_no_limit_p
 else null
 end hist_highest_no_credit_limit,
 case when qb.currency_bucket is not null
 then
   --nvl(
    (select distinct
     first_value(trunc(h.last_update_date)) over (order by h.credit_limit desc range between unbounded preceding and unbounded following)
     from
     ar_credit_histories h
     where
     h.customer_id = qc.customer_id and
     (h.site_use_id = qc.site_use_id or
      (h.site_use_id is null and not exists (select 1 from ar_credit_histories h2 where h2.site_use_id = qc.site_use_id and h2.customer_id = qc.customer_id)
      )
     ) and
     nvl(h.credit_limit,0) != 0
    )
    --,decode(nvl(hcpa.overall_credit_limit,0),0,to_date(null),trunc(hcpa.last_update_date))
   --)
 end hist_highest_credit_limit_date,
 --
 -- 12mth rolling summary
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_sales_amount_p end   sales_amount, -- c_ytd_sales_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_payment_amount_p end  payments_amount, -- c_ytd_payment_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_credit_amount_p end  credits_amount, -- c_ytd_credit_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_finance_charge_amount_p end  finance_changes_amount, -- c_ytd_finance_charge_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_writeoff_amount_p end  written_off_amount, -- c_ytd_writeoff_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_earned_discount_amount_p end  earned_discounts_amount, -- c_ytd_earned_discount_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_unearned_discount_amoun end  unearned_discounts_amount, -- c_ytd_unearned_discount_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_nsf_amount_p end  nsf_stop_payments_amount, -- c_ytd_nsf_amount,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_sales_count_p end  sales_count, -- c_ytd_sales_count,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_payment_count_p end  payments_count, -- c_ytd_payment_count,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_credit_count_p end  credits_count, -- c_ytd_credit_count,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_finance_charge_count_p end  finance_changes_count, -- c_ytd_finance_charge_count,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_nsf_count_p end  nsf_stop_payments_count, -- c_ytd_nsf_count ,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_late_payments_count_p end  late_payments_count, -- c_ytd_late_payments_count,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_on_time_payments_count_p end  on_time_payments_count, -- c_ytd_on_time_payments_count,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_average_payment_days_p end  average_payment_days, -- c_ytd_average_payment_days,
 case when qb.currency_bucket is not null then ar_arxccs_xmlp_pkg.c_ytd_average_days_late_p end average_days_late, -- c_ytd_average_days_late,
 --
 -- Last Transactions Summary
 --
 --qc.c_last_invoice_formula,
 qc.c_last_invoice_number last_invoice_number,
 qc.c_last_invoice_type last_invoice_type,
 qc.c_last_invoice_currency last_invoice_curr,
 qc.c_last_invoice_amount last_invoice_amount,
 to_date(qc.c_last_invoice_date) last_invoice_date,
 qc.c_last_invoice_days_since last_invoice_days_since,
 --qc.c_last_credit_memo_formula,
 qc.c_last_cm_number last_credit_memo_number,
 qc.c_last_cm_type last_credit_memo_type,
 qc.c_last_cm_rel_invoice last_credit_memo_related_inv,
 qc.c_last_cm_currency last_credit_memo_curr,
 qc.c_last_cm_amount last_credit_memo_amount,
 to_date(qc.c_last_cm_date) last_credit_memo_date,
 qc.c_last_cm_days_since last_credit_memo_days_since,
 --qc.c_guarantee_formula,
 qc.c_last_guar_number last_guarantee_number,
 qc.c_last_guar_type last_guarantee_type,
 qc.c_last_guar_currency last_guarantee_curr,
 qc.c_last_guar_amount last_guarantee_amount,
 --qc.c_last_guar_converted last_guarantee_converted,
 to_date(qc.c_last_guar_date) last_guarantee_date,
 qc.c_last_guar_days_since last_guarantee_days_since,
 --qc.c_last_deposit_formula,
 qc.c_last_dep_number last_deposit_number,
 qc.c_last_dep_type last_deposit_type,
 qc.c_last_dep_currency last_deposit_curr,
 qc.c_last_dep_amount last_deposit_amount,
 to_date(qc.c_last_dep_date) last_deposit_date,
 qc.c_last_dep_days_since last_deposit_days_since,
 --qc.c_last_dm_formula,
 qc.c_last_dm_number last_debit_memo_number,
 qc.c_last_dm_type last_debit_memo_type,
 qc.c_last_dm_currency last_debit_memo_curr,
 qc.c_last_dm_amount last_debit_memo_amount,
 to_date(qc.c_last_dm_date) last_debit_memo_date,
 qc.c_last_dm_days_since last_debit_memo_days_since,
 --qc.c_last_cb_formula,
 qc.c_last_cb_number last_charge_back_number,
 qc.c_last_cb_type last_charge_back_type,
 qc.c_last_cb_currency last_charge_back_curr,
 qc.c_last_cb_amount last_charge_back_amount,
 to_date(qc.c_last_cb_date) last_charge_back_date,
 qc.c_last_cb_days_since last_charge_back_days_since,
 --qc.c_last_payment_formula,
 qc.c_last_payment_number last_payment_number,
 qc.c_last_payment_type last_payment_type,
 qc.c_last_payment_rel_invoice last_payment_related_inv,
 qc.c_last_payment_currency last_payment_curr,
 qc.c_last_payment_amount last_payment_amount,
 to_date(qc.c_last_payment_date) last_payment_date,
 qc.c_last_payment_days_since last_payment_days_since,
 --qc.c_last_adj_formula,
 qc.c_last_adj_number last_adjustment_number,
 qc.c_last_adj_type last_adjustment_type,
 qc.c_last_adj_rel_invoice last_adjustment_related_inv,
 qc.c_last_adj_currency last_adjustment_curr,
 qc.c_last_adj_amount last_adjustment_amount,
 to_date(qc.c_last_adj_date) last_adjustment_date,
 qc.c_last_adj_days_since last_adjustment_days_since,
 --qc.c_last_writeoff_formula,
 qc.c_last_wo_number last_writeoff_number,
 qc.c_last_wo_type last_writeoff_type,
 qc.c_last_wo_rel_invoice last_writeoff_related_inv,
 qc.c_last_wo_currency last_writeoff_curr,
 qc.c_last_wo_amount last_writeoff_amount,
 to_date(qc.c_last_wo_date) last_writeoff_date,
 qc.c_last_wo_days_since last_writeoff_days_since,
 /* not using the xml function as it contains a bug */
 --qc.c_last_statement_formula,
 --qc.c_last_st_number last_statement_number,
 -- qc.c_last_st_type last_statement_type,
 -- to_date(qc.c_last_st_date) last_statement_date,
 --qc.c_last_st_days_since last_statement_days_since,
 --to_date(qc.c_last_stmnt_next_trx_date) next_statement_date,
 case
 when qsc.statement_date is null
 then ar_arxccs_xmlp_pkg.rp_none_p
 else ar_arxccs_xmlp_pkg.rp_na_upper_p
 end last_statement_number,
 qsc.statement_type last_statement_type,
 qsc.statement_date last_statement_date,
 qsc.days_since last_statement_days_since,
 (select distinct
  first_value(ascd.statement_date) over (order by ascd.statement_date,ascd.statement_cycle_date_id)
  from
  hz_customer_profiles hcp,
  ar_statement_cycle_dates ascd
  where
  hcp.statement_cycle_id = ascd.statement_cycle_id and
  ascd.printed = 'N' and
  ascd.statement_date > qsc.statement_date and
  hcp.cust_account_id = qc.customer_id and
  hcp.site_use_id is null
 ) next_statement_date,
 --qc.c_last_dn_formula,
 qc.c_last_dn_number last_dunning_number,
 qc.c_last_dn_type last_dunning_type,
 qc.c_last_dn_currency last_dunning_curr,
 qc.c_last_dn_amount last_dunning_amount,
 to_date(qc.c_last_dn_date) last_dunning_date,
 qc.c_last_dn_days_since last_dunning_days_since,
 --qc.c_last_nsf_formula,
 qc.c_last_nsf_number last_nsf_stop_pmt_number,
 qc.c_last_nsf_type last_nsf_stop_pmt_type,
 qc.c_last_nsf_currency last_nsf_stop_pmt_curr,
 qc.c_last_nsf_amount last_nsf_stop_pmt_amount,
 to_date(qc.c_last_nsf_date) last_nsf_stop_pmt_date,
 qc.c_last_nsf_days_since last_nsf_stop_pmt_days_since,
 --qc.c_last_contact_formula,
 qc.c_last_contact_number last_phone_contact_number,
 qc.c_last_contact_rel_invoice last_phone_contact_related_inv,
 qc.c_last_contact_currency last_phone_contact_curr,
 qc.c_last_contact_amount last_phone_contact_amount,
 to_date(qc.c_last_contact_date) last_phone_contact_date,
 qc.c_last_contact_days_since last_phone_contact_days_since,
 --qc.c_last_hold_formula,
 qc.c_last_hold_number last_credit_hold_number,
 qc.c_last_hold_amount last_credit_hold_amount,
 to_date(qc.c_last_hold_date) last_credit_hold_date,
 qc.c_last_hold_days_since last_credit_hold_days_since,
 --
 to_char(qc.set_of_books_id) set_of_books_id,
 to_char(qc.qcust_org_id) org_id,
 to_char(qc.customer_id) customer_id,
 to_char(qc.site_use_id) site_use_id,
 to_char(qc.c_customer_profile_id) customer_profile_id
from
 q_cust qc,
 q_currencies curr,
 hz_cust_profile_amts hcpa,
 q_stmt_cycles qsc,
 q_buckets qb
where
 qc.customer_id  = curr.customer_id (+) and
 qc.site_use_id  = curr.site_use_id (+) and
 qc.qcust_org_id = curr.org_id (+) and
 qc.customer_profile_id = curr.cust_account_profile_id (+) and
 --
 curr.cust_account_profile_id = hcpa.cust_account_profile_id (+) and
 curr.currency_code = hcpa.currency_code (+) and
 --
 curr.customer_id = qb.ps_customer_id (+) and
 curr.site_use_id = qb.ps_site_use_id (+) and
 curr.currency_code = qb.currency_bucket (+) and
 --
 qc.statement_cycle_id = qsc.statement_cycle_id (+)
order by
 qc.customer_name,
 qc.primary_flag desc,
 qc.location,
 curr.currency_code