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,
x1.cust_country customer_country,
x1.cust_class customer_classification,
x1.credit_limits,
x1.order_credit_limits,
x1.collector collector,
&lp_invoice_cols_s
x1.revaluation_from_currency amounts_currency,
case when nvl(x1.rk,1)=1 then nvl(sum(x1.amt_due_original),0) end original_amount,
case when nvl(x1.rk,1)=1 then nvl(sum(x1.amt_due_remaining),0) end outstanding_amount,
--
case when nvl(x1.rk,1)=1 then nvl(sum(
case
when x1.days_past_due > 0
or (:p_credit_option='DETAIL' and x1.class in ('PMT','CM','CLAIM'))
or (:p_risk_option='DETAIL' and x1.invoice_type=:p_risk_meaning)
then x1.amt_due_remaining
else 0
end
),0) end past_due_amount,
--
&lp_on_acc_summ_cols
:p_age_basis Aging_Basis,
&lp_aging_amount_cols
&lp_aging_pct_cols
-- Revaluation Columns
&lp_reval_columns
&lp_reval_aging_amount_cols
--
&party_dff_cols1
&cust_dff_cols1
--
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",
-- SLA informations
fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_acct_flex_bal_seg', 'SQLGL', 'GL#', x1.chart_of_accounts_id, null, x1.rec_code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') "Sla Receivables &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.rec_code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') "Sla Receivables &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.rec_code_combination_id, 'ALL', 'Y', 'VALUE') sla_receivables_segments,
fnd_flex_xml_publisher_apis.process_kff_combination_1('lp_accounting_flexfield', 'SQLGL', 'GL#', x1.chart_of_accounts_id, null, x1.rec_code_combination_id, 'ALL', 'Y', 'DESCRIPTION') sla_receivables_segments_desc,
nvl(sum(x1.accounted_dr),0) sla_receivables_accounted_dr,
nvl(sum(x1.accounted_cr),0) sla_receivables_accounted_cr,
nvl(sum(x1.accounted_dr),0)-nvl(sum(x1.accounted_cr),0) sla_receivables_accounted_amt
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,
hcpamv.credit_limits,
hcpamv.order_credit_limits,
x.cust_name,
x.cust_no,
x.cust_country,
x.cust_class,
(select ac.name
from ar_collectors ac
where ac.collector_id=nvl(hcps.collector_id,hcpa.collector_id)) collector,
x.class,
x.cons_billing_number,
x.invnum,
x.invoice_currency_code,
x.term,
x.trx_date,
x.due_date,
x.days_past_trx,
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.comments,
--
&lp_bucket_cols1
--
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,
--
&party_dff_cols2
&cust_dff_cols2
--
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,
nvl(sla.code_combination_id, x.code_combination_id) rec_code_combination_id,
x.payment_sched_id,
nvl(
round(sla.accounted_dr * x.installment_ratio, 2),
case when x.rec_acctd_amount >= 0 then x.rec_acctd_amount else 0 end
) accounted_dr,
nvl(
round(sla.accounted_cr * x.installment_ratio, 2),
case when x.rec_acctd_amount < 0 then abs(x.rec_acctd_amount) else 0 end
) accounted_cr,
row_number() over (partition by x.payment_sched_id order by nvl(sla.code_combination_id, x.code_combination_id)) rk
from
(--start x
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,
xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222) cust_class,
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,
--
&party_dff_cols3
&cust_dff_cols3
--
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,
--
&lp_bucket_cols2
--
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,
rct.comments,
ps.invoice_currency_code,
gsob.currency_code functional_currency,
gsob.name ledger,
gsob.set_of_books_id ledger_id,
ps.org_id,
gld.acctd_amount rec_acctd_amount,
ps.customer_trx_id,
'TRANSACTIONS' sla_entity_code,
ps.customer_trx_id sla_source_id,
ps.amount_due_original/nvl(
(select sum(ps2.amount_due_original)
from ar_payment_schedules ps2
where ps2.customer_trx_id=ps.customer_trx_id and
ps2.class<>'PMT'), 0) installment_ratio
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
&lp_invoice_type_low
&lp_invoice_type_high
&lp_bal_seg_low
&lp_bal_seg_high
&lp_acc_seg_low
&lp_acc_seg_high
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,
xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222) cust_class,
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,
--
&party_dff_cols3
&cust_dff_cols3
--
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),
--
&lp_bucket_cols3
--
app.code_combination_id,
app.chart_of_accounts_id,
ci.cons_billing_number cons_billing_number,
initcap(:p_payment_meaning) invoice_type,
acr.comments,
ps.invoice_currency_code,
gsob.currency_code functional_currency,
gsob.name ledger,
gsob.set_of_books_id ledger_id,
ps.org_id,
-- Derive accounted amount from ar_receivable_applications for Payments
decode(nvl2(:p_in_currency,'N','Y'), 'Y', nvl(-SUM(app.acctd_amount), 0), nvl(-SUM(app.amount), 0)) rec_acctd_amount,
ps.customer_trx_id,
to_char(null) sla_entity_code,
to_number(null) sla_source_id,
1 installment_ratio
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'
&lp_bal_seg_low
&lp_bal_seg_high
&lp_acc_seg_low
&lp_acc_seg_high
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,
hca.customer_class_code,
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,
--
&party_dff_cols3_g
&cust_dff_cols3_g
--
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),
acr.comments,
gsob.currency_code,
gsob.name,
gsob.set_of_books_id,
ps.org_id,
ps.customer_trx_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,
xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222) cust_class,
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,
--
&party_dff_cols3
&cust_dff_cols3
--
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),
--
&lp_bucket_cols4
--
c.code_combination_id,
c.chart_of_accounts_id,
ci.cons_billing_number cons_billing_number,
initcap(:p_risk_meaning) invoice_type,
cr.comments,
ps.invoice_currency_code,
gsob.currency_code functional_currency,
gsob.name ledger,
gsob.set_of_books_id ledger_id,
ps.org_id,
-- Derive accounted amount from ar_cash_receipt_history for Risk
decode(nvl2(:p_in_currency,'N','Y'), 'Y', crh.acctd_amount, crh.amount) rec_acctd_amount,
ps.customer_trx_id,
to_char(null) sla_entity_code,
to_number(null) sla_source_id,
1 installment_ratio
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
&lp_bal_seg_low
&lp_bal_seg_high
&lp_acc_seg_low
&lp_acc_seg_high
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,
xxen_util.meaning(hca.customer_class_code,'CUSTOMER CLASS',222) cust_class,
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,
--
&party_dff_cols3
&cust_dff_cols3
--
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,
--
&lp_bucket_cols3
--
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,
ct.comments,
ps.invoice_currency_code,
gsob.currency_code functional_currency,
gsob.name ledger,
gsob.set_of_books_id ledger_id,
ps.org_id,
to_number(null) rec_acctd_amount,
ps.customer_trx_id,
'TRANSACTIONS' sla_entity_code,
ps.customer_trx_id sla_source_id,
ps.amount_due_original/nvl(
(select sum(ps2.amount_due_original)
from ar_payment_schedules ps2
where ps2.customer_trx_id=ps.customer_trx_id
and ps2.class<>'PMT'), 0) installment_ratio
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
&lp_invoice_type_low
&lp_invoice_type_high
&lp_bal_seg_low
&lp_bal_seg_high
&lp_acc_seg_low
&lp_acc_seg_high
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,
(select
hcpam.cust_account_profile_id,
listagg(hcpam.currency_code||'='||to_char(hcpam.overall_credit_limit),',') within group (order by hcpam.currency_code) as credit_limits,
listagg(hcpam.currency_code||'='||to_char(hcpam.trx_credit_limit),',') within group (order by hcpam.currency_code) as order_credit_limits
from
hz_cust_profile_amts hcpam
where
hcpam.overall_credit_limit is not null or hcpam.trx_credit_limit is not null
group by
hcpam.cust_account_profile_id
) hcpamv,
(
select
sla_inner.ledger_id,
sla_inner.entity_code,
sla_inner.source_id,
sla_inner.code_combination_id,
sla_inner.accounted_dr,
sla_inner.accounted_cr,
sla_inner.accounting_date
from
(
select /*+ full(xte) full(xah) full(xal) use_hash(xte xah xal) */
xte.ledger_id,
xte.entity_code,
xte.source_id_int_1 source_id,
xal.code_combination_id,
sum(nvl(xal.accounted_dr,0)) accounted_dr,
sum(nvl(xal.accounted_cr,0)) accounted_cr,
max(xah.accounting_date) accounting_date
from
xla_ae_headers xah,
xla_ae_lines xal,
xla_transaction_entities xte
where
xte.entity_code='TRANSACTIONS' and
xte.application_id=xah.application_id and
xte.entity_id=xah.entity_id and
xah.ae_header_id=xal.ae_header_id and
xah.application_id=xal.application_id and
xah.application_id=222 and
xal.accounting_class_code='RECEIVABLE' and
xah.ledger_id=xte.ledger_id and
exists (
select null
from
gl_code_combinations gcc,
gl_ledgers gl
where
gcc.code_combination_id=xal.code_combination_id and
gl.ledger_id=xte.ledger_id and
gcc.chart_of_accounts_id=gl.chart_of_accounts_id
)
group by
xte.ledger_id,
xte.entity_code,
xte.source_id_int_1,
xal.code_combination_id
) sla_inner
) sla
where
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 and
hcpa.cust_account_profile_id=hcpamv.cust_account_profile_id(+)and
nvl(x.amt_due_remaining,0) != 0 and
x.ledger_id=sla.ledger_id(+) and
x.sla_entity_code=sla.entity_code(+) and
x.sla_source_id=sla.source_id(+)
) x1
where
1=1
group by
x1.ledger,
x1.operating_unit,
x1.code_combination_id,
x1.chart_of_accounts_id,
x1.sort_field1,
x1.cust_name,
x1.cust_no,
x1.cust_country,
x1.cust_class,
x1.credit_limits,
x1.order_credit_limits,
--
&party_dff_cols1_g
&cust_dff_cols1_g
--
x1.collector,
x1.revaluation_from_currency,
x1.rec_code_combination_id,
x1.payment_sched_id,
x1.rk,
x1.reval_conv_rate
&lp_invoice_cols_g
order by
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'),
x1.sort_field1,
x1.cust_name,
x1.cust_no,
x1.payment_sched_id,
x1.rk
&lp_invoice_cols_g |
