ORACLE MASTERMINDS: Customer Credit Snapshot

Monday, February 25, 2019

Customer Credit Snapshot

Report will fetch and display all customer credit card snap shot details in excel format

<?xml version="1.0" encoding="UTF-8" ?>




<dataTemplate name="ARXCCS" defaultPackage="AR_ARXCCS_XMLP_PKG" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter name="P_CONC_REQUEST_ID" dataType="number" defaultValue="0"></parameter>
<parameter name="P_CUSTOMER_NUMBER_LOW" dataType="character"></parameter>
<parameter name="P_CUSTOMER_NUMBER_HIGH" dataType="character"></parameter>
<parameter name="P_CUSTOMER_NAME_LOW" dataType="character"></parameter>
<parameter name="P_CUSTOMER_NAME_HIGH" dataType="character"></parameter>
<parameter name="P_BUCKET_NAME_LOW" dataType="character" defaultValue="Credit Snapshot"></parameter>
<parameter name="P_BUCKET_NAME_HIGH" dataType="character" defaultValue="Credit SnapShot"></parameter>
<parameter name="P_COLLECTOR_LOW" dataType="character"></parameter>
<parameter name="P_COLLECTOR_HIGH" dataType="character"></parameter>


<parameter name="P_ATRATE" dataType="character" defaultValue="X"></parameter>

<parameter name="P_REPORTING_ENTITY_ID" dataType="number"></parameter>

<parameter name="P_REPORTING_LEVEL" dataType="character" defaultValue="1"></parameter>

</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name="Q_SETUP">
<![CDATA[
SELECT param.org_id setup_org_id,
sob.name Company_Name,
sob.chart_of_accounts_id coa_id,
sob.currency_code Functional_Currency,
cur.precision Functional_Currency_Precision,
AR_ARXCCS_XMLP_PKG.report_nameformula(sob.name) Report_Name
FROM gl_sets_of_books sob,
ar_system_parameters_all param,
fnd_currencies cur
WHERE sob.set_of_books_id = param.set_of_books_id
AND sob.currency_code = cur.currency_code
&p_org_where_param
]]>
</sqlStatement>
<sqlStatement name="Q_CUST">
<![CDATA[
select :setup_org_id qcust_org_id,
cust_acct.cust_account_id Customer_id,
cust_acct.account_number Customer_Number,
substrb(party.party_name,1,50) 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,
cp.cust_account_profile_id Customer_Profile_Id,
AR_ARXCCS_XMLP_PKG.c_last_credit_memo_formulaform(:FUNCTIONAL_CURRENCY,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(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_LAST_INVOICE_FORMULA,
--AR_ARXCCS_XMLP_PKG.c_last_credit_memo_formulaform(cust_acct.cust_account_id, site_uses.site_use_id) --C_LAST_CREDIT_MEMO_FORMULA,
AR_ARXCCS_XMLP_PKG.c_guarantee_formulaformula(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_GUARANTEE_FORMULA,
AR_ARXCCS_XMLP_PKG.c_last_deposit_formulaformula(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_LAST_DEPOSIT_FORMULA,
AR_ARXCCS_XMLP_PKG.c_last_dm_formulaformula(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_LAST_DM_FORMULA,
AR_ARXCCS_XMLP_PKG.c_last_cb_formulaformula(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_LAST_CB_FORMULA,
AR_ARXCCS_XMLP_PKG.c_last_payment_formulaformula(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_LAST_PAYMENT_FORMULA,
AR_ARXCCS_XMLP_PKG.c_last_adj_formulaformula(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_LAST_ADJ_FORMULA,
AR_ARXCCS_XMLP_PKG.c_last_writeoff_formulaformula(:FUNCTIONAL_CURRENCY, 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(:FUNCTIONAL_CURRENCY, cust_acct.cust_account_id, site_uses.site_use_id) C_LAST_NSF_FORMULA,
AR_ARXCCS_XMLP_PKG.c_last_contact_formulaformula(:FUNCTIONAL_CURRENCY, 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_limit_expire_date c_cred_summ_limit_expire_date,
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,
'X' dummy
from 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_all site_uses,
fnd_territories_vl terr,
ar_collectors coll,
hz_customer_profiles cp
where 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 acct_site.org_id = :setup_org_id
and site_uses.status = 'A'
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_number_low
&lp_customer_number_high
&lp_coll_name_low
&lp_coll_name_high
&p_org_where_ad
&p_org_where_su
and cust_acct.cust_account_id = acct_site.cust_account_id
and site_uses.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 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
))
order by party.party_name, site_uses.primary_flag desc, site_uses.location
]]>
</sqlStatement>
<sqlStatement name="Q_BUCKET">
<![CDATA[
select 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),
decode( greatest(:rp_bucket_days_from_0,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_0,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),
decode( greatest(to_number(:rp_bucket_days_from_1),
ceil(trunc(sysdate)-ps.due_date)),
least(to_number(:rp_bucket_days_to_1),
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),
decode( greatest(:rp_bucket_days_from_2,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_2,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),
decode( greatest(:rp_bucket_days_from_3,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_3,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),
decode( greatest(:rp_bucket_days_from_4,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_4,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),
decode( greatest(:rp_bucket_days_from_5,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_5,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),
decode( greatest(:rp_bucket_days_from_6,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_6,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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,
max(decode(ps.invoice_currency_code, :functional_currency, NULL,
decode(ps.exchange_rate, NULL, '*', NULL))) Aging_Convert_Bucket
, ps.customer_id ps_customer
, ps.Customer_site_use_id ps_site,
--AR_ARXCCS_XMLP_PKG.c_calc_percentformula() C_CALC_PERCENT,
AR_ARXCCS_XMLP_PKG.C_CALC_PERCENTFormula(sum(ps.amount_due_remaining),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),
decode( greatest(:rp_bucket_days_from_0,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_0,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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), 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),
decode( greatest(to_number(:rp_bucket_days_from_1),
ceil(trunc(sysdate)-ps.due_date)),
least(to_number(:rp_bucket_days_to_1),
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),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),
decode( greatest(:rp_bucket_days_from_2,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_2,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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), 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),
decode( greatest(:rp_bucket_days_from_3,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_3,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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), 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),
decode( greatest(:rp_bucket_days_from_4,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_4,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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), 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),
decode( greatest(:rp_bucket_days_from_5,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_5,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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),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),
decode( greatest(:rp_bucket_days_from_6,
ceil(trunc(sysdate)-ps.due_date)),
least(:rp_bucket_days_to_6,
ceil(trunc(sysdate)-ps.due_date)),1,
0)
* 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)) C_CALC_PERCENT,
AR_ARXCCS_XMLP_PKG.c_compute_amtformula(:FUNCTIONAL_CURRENCY, :CUSTOMER_ID, :SITE_USE_ID, ps.invoice_currency_code, sum ( ps.amount_due_remaining )) C_COMPUTE_AMT,
AR_ARXCCS_XMLP_PKG.c_high_inv_formulaformula(:FUNCTIONAL_CURRENCY, :CUSTOMER_ID, :SITE_USE_ID, ps.invoice_currency_code) C_HIGH_INV_FORMULA,
AR_ARXCCS_XMLP_PKG.c_cust_hist_high_limit_amtform(:CUSTOMER_ID, :SITE_USE_ID) c_cust_hist_high_limit_amt,
AR_ARXCCS_XMLP_PKG.c_rolling_summary_calcformula(:FUNCTIONAL_CURRENCY, :CUSTOMER_ID, :SITE_USE_ID, ps.invoice_currency_code) C_ROLLING_SUMMARY_CALC,
AR_ARXCCS_XMLP_PKG.c_currency_lookupformula(:SITE_USE_ID, ps.invoice_currency_code) C_currency_lookup,
AR_ARXCCS_XMLP_PKG.CP_DEFAULT_FLAG_p CP_DEFAULT_FLAG,
AR_ARXCCS_XMLP_PKG.c_percent_b0_p c_percent_b0,
AR_ARXCCS_XMLP_PKG.c_percent_b1_p c_percent_b1,
AR_ARXCCS_XMLP_PKG.c_percent_b2_p c_percent_b2,
AR_ARXCCS_XMLP_PKG.c_percent_b3_p c_percent_b3,
AR_ARXCCS_XMLP_PKG.c_percent_b4_p c_percent_b4,
AR_ARXCCS_XMLP_PKG.c_percent_b5_p c_percent_b5,
AR_ARXCCS_XMLP_PKG.c_percent_b6_p c_percent_b6,
AR_ARXCCS_XMLP_PKG.c_aging_on_account_p c_aging_on_account,
AR_ARXCCS_XMLP_PKG.c_aging_unapplied_p c_aging_unapplied,
AR_ARXCCS_XMLP_PKG.c_aging_convert_on_account_p c_aging_convert_on_account,
AR_ARXCCS_XMLP_PKG.c_aging_convert_unapplied_p c_aging_convert_unapplied,
AR_ARXCCS_XMLP_PKG.c_aging_credit_p c_aging_credit,
AR_ARXCCS_XMLP_PKG.c_aging_convert_credit_p c_aging_convert_credit,
AR_ARXCCS_XMLP_PKG.c_adjusted_balance_p c_adjusted_balance,
AR_ARXCCS_XMLP_PKG.c_aging_convert_collection_p c_aging_convert_collection,
AR_ARXCCS_XMLP_PKG.c_aging_in_collection_p c_aging_in_collection,
AR_ARXCCS_XMLP_PKG.c_cust_hist_high_invoice_amt_p c_cust_hist_high_invoice_amt,
--AR_ARXCCS_XMLP_PKG.c_cust_hist_conv_high_invoice c_cust_hist_conv_high_invoice,
AR_ARXCCS_XMLP_PKG.c_cust_hist_conv_high_inv_p c_cust_hist_conv_high_invoice,
-- AR_ARXCCS_XMLP_PKG.c_cust_hist_high_invoice_date c_cust_hist_high_invoice_date,
AR_ARXCCS_XMLP_PKG.c_cust_hist_high_inv_date_p c_cust_hist_high_invoice_date,
AR_ARXCCS_XMLP_PKG.c_cust_hist_high_limit_date_p c_cust_hist_high_limit_date,
AR_ARXCCS_XMLP_PKG.c_ytd_nsf_count_p c_ytd_nsf_count,
AR_ARXCCS_XMLP_PKG.c_ytd_convert_nsf_p c_ytd_convert_nsf,
AR_ARXCCS_XMLP_PKG.c_ytd_nsf_amount_p c_ytd_nsf_amount,
AR_ARXCCS_XMLP_PKG.c_ytd_conv_unearned_discount_p c_ytd_conv_unearned_discount,
AR_ARXCCS_XMLP_PKG.c_ytd_unearned_discount_amoun c_ytd_unearned_discount_amount,
-- AR_ARXCCS_XMLP_PKG.c_ytd_convert_earned_discount c_ytd_convert_earned_discount,
AR_ARXCCS_XMLP_PKG.c_ytd_convert_earned_dis_p c_ytd_convert_earned_discount,
AR_ARXCCS_XMLP_PKG.c_ytd_earned_discount_amount_p c_ytd_earned_discount_amount,
AR_ARXCCS_XMLP_PKG.c_ytd_on_time_payments_count_p c_ytd_on_time_payments_count,
AR_ARXCCS_XMLP_PKG.c_ytd_late_payments_count_p c_ytd_late_payments_count,
AR_ARXCCS_XMLP_PKG.c_ytd_average_days_late_p c_ytd_average_days_late,
AR_ARXCCS_XMLP_PKG.c_ytd_average_payment_days_p c_ytd_average_payment_days,
AR_ARXCCS_XMLP_PKG.c_ytd_finance_charge_count_p c_ytd_finance_charge_count,
AR_ARXCCS_XMLP_PKG.c_ytd_convert_finance_charge_p c_ytd_convert_finance_charge,
AR_ARXCCS_XMLP_PKG.c_ytd_finance_charge_amount_p c_ytd_finance_charge_amount,
AR_ARXCCS_XMLP_PKG.c_ytd_credit_count_p c_ytd_credit_count,
AR_ARXCCS_XMLP_PKG.c_ytd_convert_credit_p c_ytd_convert_credit,
AR_ARXCCS_XMLP_PKG.c_ytd_credit_amount_p c_ytd_credit_amount,
AR_ARXCCS_XMLP_PKG.c_ytd_payment_count_p c_ytd_payment_count,
AR_ARXCCS_XMLP_PKG.c_ytd_convert_payment_p c_ytd_convert_payment,
AR_ARXCCS_XMLP_PKG.c_ytd_payment_amount_p c_ytd_payment_amount,
AR_ARXCCS_XMLP_PKG.c_ytd_sales_count_p c_ytd_sales_count,
AR_ARXCCS_XMLP_PKG.c_ytd_convert_sales_p c_ytd_convert_sales,
AR_ARXCCS_XMLP_PKG.c_ytd_sales_amount_p c_ytd_sales_amount,
AR_ARXCCS_XMLP_PKG.c_ytd_convert_writeoff_p c_ytd_convert_writeoff,
AR_ARXCCS_XMLP_PKG.c_ytd_writeoff_amount_p c_ytd_writeoff_amount,
AR_ARXCCS_XMLP_PKG.CP_limit_currency_p CP_limit_currency,
AR_ARXCCS_XMLP_PKG.CP_related_currencies_p CP_related_currencies,
AR_ARXCCS_XMLP_PKG.CP_txn_cur_p CP_txn_cur
from ar_payment_schedules_all ps
where ps.customer_id = :customer_id
and ps.customer_site_use_id = :site_use_id
and ps.class not in ('CM', 'PMT')
and ps.org_id = :qcust_org_id
&p_org_where_ps
and ps.customer_id=:Customer_id
and ps.Customer_site_use_id=:Site_Use_id
group by ps.invoice_currency_code, ps.customer_id, ps.customer_site_use_id
order by ps.invoice_currency_code
]]>
</sqlStatement>
<sqlStatement name="Q_CREDIT_AMOUNTS">
<![CDATA[
select cust_account_id qc_customer,
site_use_id qc_site,
currency_code currency_credit,
trx_credit_limit order_credit_limit,
overall_credit_limit credit_limit,
-- AR_ARXCCS_XMLP_PKG.c_credit_amounts_calcformula(cust_account_id, site_use_id) C_CREDIT_AMOUNTS_CALC,
AR_ARXCCS_XMLP_PKG.c_credit_amounts_calcformula(cust_account_id,site_use_id,:functional_currency,currency_code,overall_credit_limit) C_CREDIT_AMOUNTS_CALC,
AR_ARXCCS_XMLP_PKG.c_cred_summ_convert_limit_p c_cred_summ_convert_limit,
AR_ARXCCS_XMLP_PKG.c_cred_summ_exceeded_p c_cred_summ_exceeded,
AR_ARXCCS_XMLP_PKG.c_cred_summ_available_p c_cred_summ_available,
AR_ARXCCS_XMLP_PKG.CP_trx_amount_p CP_trx_amount,
AR_ARXCCS_XMLP_PKG.CP_trx_curr_p CP_trx_curr
from hz_cust_profile_amts
where cust_account_profile_id = :c_customer_profile_id
and currency_code = :cp_limit_currency
and :cp_default_flag = 'N'
UNION
select to_number(:ps_customer) qc_customer,
to_number(:ps_site) qc_site,
pa.currency_code currency_credit,
pa.trx_credit_limit order_credit_limit,
pa.overall_credit_limit credit_limit,
AR_ARXCCS_XMLP_PKG.c_credit_amounts_calcformula(to_number(:ps_customer),to_number(:ps_site),:functional_currency,pa.currency_code,pa.overall_credit_limit) C_CREDIT_AMOUNTS_CALC,
AR_ARXCCS_XMLP_PKG.c_cred_summ_convert_limit_p c_cred_summ_convert_limit,
AR_ARXCCS_XMLP_PKG.c_cred_summ_exceeded_p c_cred_summ_exceeded,
AR_ARXCCS_XMLP_PKG.c_cred_summ_available_p c_cred_summ_available,
AR_ARXCCS_XMLP_PKG.CP_trx_amount_p CP_trx_amount,
AR_ARXCCS_XMLP_PKG.CP_trx_curr_p CP_trx_curr
from hz_credit_profiles p,
hz_credit_profile_amts pa
where p.credit_profile_id = pa.credit_profile_id
and pa.currency_code = :cp_limit_currency
and NVL(p.effective_date_from, sysdate-1) < sysdate
and NVL(p.effective_date_to, sysdate+1) > sysdate
and NVL(p.enable_flag, 'N') ='Y'
and :cp_default_flag = 'Y'
and rownum = 1
]]>
</sqlStatement>
<sqlStatement name="Q_TRX_CONVERSION">
<![CDATA[
SELECT Invoice_currency_code trx_cur
, customer_id trx_cust
, customer_site_use_id trx_site
, NVL(SUM(AMOUNT_DUE_REMAINING), 0) trx_amount_due
, exchange_rate_type,
AR_ARXCCS_XMLP_PKG.cf_calc_rate_amountformula(Invoice_currency_code, :CUSTOMER_ID, :SITE_USE_ID, NVL ( SUM ( AMOUNT_DUE_REMAINING ) , 0 )) CF_calc_RATE_amount,
AR_ARXCCS_XMLP_PKG.CP_limit_curr_amt_p CP_limit_curr_amt,
AR_ARXCCS_XMLP_PKG.CP_rate_p CP_rate,
AR_ARXCCS_XMLP_PKG.CP_adjusted_amount_p CP_adjusted_amount
FROM AR_PAYMENT_SCHEDULES_ALL PS
WHERE ps.customer_id = :customer_id
AND (ps.customer_site_use_id = :site_use_id OR (:site_use_id is NULL))
AND ps.status= 'OP'
AND ps.class not IN ('CM','PMT')
and ps.org_id = :qcust_org_id
&p_org_where_ps
group by ps.invoice_currency_code, customer_id, customer_site_use_id,exchange_rate_type
]]>
</sqlStatement>
<sqlStatement name="Q_customer_limit">
<![CDATA[
SELECT Invoice_currency_code trx_cur2
, customer_id trx_cust2
, customer_site_use_id trx_site2
, NVL(SUM(AMOUNT_DUE_REMAINING), 0) trx_amount_due
,exchange_rate_type,
AR_ARXCCS_XMLP_PKG.cf_calc_rate_amount_formula1fo(Invoice_currency_code, :CUSTOMER_ID, :SITE_USE_ID, NVL ( SUM ( AMOUNT_DUE_REMAINING ) , 0 )) CF_CALC_RATE_AMOUNT_FORMULA1,
AR_ARXCCS_XMLP_PKG.CP_limit_curr_amt1_p CP_limit_curr_amt1,
AR_ARXCCS_XMLP_PKG.CP_rate1_p CP_rate1,
AR_ARXCCS_XMLP_PKG.CP_adjusted_amount1_p CP_adjusted_amount1
FROM AR_PAYMENT_SCHEDULES_ALL PS
WHERE ps.customer_id = :customer_id
AND ps.status= 'OP'
and ps.class not in ('CM', 'PMT')
&p_org_where_ps
and customer_id=:qc_customer1
group by ps.invoice_currency_code, customer_id, customer_site_use_id, exchange_rate_type,amount_due_remaining
order by ps.invoice_currency_code
]]>
</sqlStatement>
<sqlStatement name="Q_ORG">
<![CDATA[
select currency_code org_Currency_code ,
OVERALL_CREDIT_LIMIT org_overall_limit,
TRX_CREDIT_LIMIT Org_trx_Limit,
--AR_ARXCCS_XMLP_PKG.c_org_credit_calcformula(:CUSTOMER_ID, :SITE_USE_ID) C_OERG_CREDIT_CALC,
AR_ARXCCS_XMLP_PKG.c_org_credit_calcformula(:CUSTOMER_ID, :SITE_USE_ID,:functional_currency,currency_code,OVERALL_CREDIT_LIMIT) C_OERG_CREDIT_CALC,
--AR_ARXCCS_XMLP_PKG.c_org_profileformula(:SITE_USE_ID) C_org_profile,
AR_ARXCCS_XMLP_PKG.c_org_profileformula() C_org_profile,
AR_ARXCCS_XMLP_PKG.C_cred_summ_exceeded2_p C_cred_summ_exceeded2,
AR_ARXCCS_XMLP_PKG.C_cred_summ_available2_p C_cred_summ_available2,
AR_ARXCCS_XMLP_PKG.C_cred_convert_limit2_p C_cred_convert_limit2,
AR_ARXCCS_XMLP_PKG.CP_trx_amount2_p CP_trx_amount2,
AR_ARXCCS_XMLP_PKG.CP_trx_cur2_p CP_trx_cur2,
AR_ARXCCS_XMLP_PKG.C_org_credit_hold_p C_org_credit_hold,
AR_ARXCCS_XMLP_PKG.C_org_credit_rating_p C_org_credit_rating,
AR_ARXCCS_XMLP_PKG.C_Org_limit_tolerance_p C_Org_limit_tolerance,
AR_ARXCCS_XMLP_PKG.C_credit_profile_id_p C_credit_profile_id
from HZ_Credit_profile_amts cpa
where cpa.credit_profile_id IN ( Select credit_profile_id
From HZ_Credit_Profiles
Where Organization_id = :p_reporting_entity_id
and enable_flag = 'Y'
and Effective_Date_From <= SYSDATE
and Effective_Date_To >= SYSDATE)
and currency_code = :LIMIT_CURRENCY
]]>
</sqlStatement>
<sqlStatement name="Q_org_trx">
<![CDATA[
SELECT Invoice_currency_code org_trx_cur
, customer_id org_trx_cust
, customer_site_use_id org_trx_site
, NVL(SUM(AMOUNT_DUE_REMAINING), 0) trx_amount_due,
AR_ARXCCS_XMLP_PKG.cf_org_calc_amountformula(Invoice_currency_code, :CUSTOMER_ID, :SITE_USE_ID, NVL ( SUM ( AMOUNT_DUE_REMAINING ) , 0 )) CF_org_calc_amount,
AR_ARXCCS_XMLP_PKG.CP_limit_curr_amt2_p CP_limit_curr_amt2,
AR_ARXCCS_XMLP_PKG.CP_Rate2_p CP_Rate2,
AR_ARXCCS_XMLP_PKG.CP_adjusted_amount2_p CP_adjusted_amount2
FROM AR_PAYMENT_SCHEDULES_ALL PS
WHERE ps.customer_id = :customer_id
AND ps.status= 'OP'
and ps.class not in ('CM', 'PMT')
&p_org_where_ps
group by ps.invoice_currency_code, customer_id, customer_site_use_id, amount_due_remaining
order by ps.invoice_currency_code
]]>
</sqlStatement>
<sqlStatement name="Q_CREDIT_LIMITS">
<![CDATA[
select currency_code currency_credit1 ,
overall_credit_limit credit_limit1,
trx_credit_limit order_credit_limit1,
cust_account_id qc_customer1,
site_use_id qc_site1,
-- AR_ARXCCS_XMLP_PKG.c_credit_amounts_calcformu0114(:CUSTOMER_ID, :SITE_USE_ID) C_CREDIT_AMOUNTS_CALC1,
AR_ARXCCS_XMLP_PKG.c_credit_amounts_calcformu0114(:CUSTOMER_ID, :SITE_USE_ID,:functional_currency,currency_code,overall_credit_limit) C_CREDIT_AMOUNTS_CALC1,
AR_ARXCCS_XMLP_PKG.cf_currency_lookupformula(cust_account_id, currency_code, site_use_id, :SITE_USE_ID) CF_CURRENCY_LOOKUP,
AR_ARXCCS_XMLP_PKG.CP_TRX_AMOUNT1_p CP_TRX_AMOUNT1,
AR_ARXCCS_XMLP_PKG.C_CRED_SUMM_AVAILABLE1_p C_CRED_SUMM_AVAILABLE1,
AR_ARXCCS_XMLP_PKG.C_CRED_CONVERT_LIMIT1_p C_CRED_CONVERT_LIMIT1,
AR_ARXCCS_XMLP_PKG.C_CRED_SUMM_EXCEEDED1_p C_CRED_SUMM_EXCEEDED1,
AR_ARXCCS_XMLP_PKG.CP_TRX_CUR1_p CP_TRX_CUR1,
AR_ARXCCS_XMLP_PKG.DEFAULT_FLAG_p DEFAULT_FLAG,
AR_ARXCCS_XMLP_PKG.LIMIT_CURRENCY_p LIMIT_CURRENCY,
AR_ARXCCS_XMLP_PKG.RELATED_CURRENCIES_p RELATED_CURRENCIES
from hz_cust_profile_amts
where cust_account_profile_id = :c_customer_profile_id
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="afterParameterFormTrigger" source="AR_ARXCCS_XMLP_PKG.afterpform" />
<dataTrigger name="beforeReportTrigger" source="AR_ARXCCS_XMLP_PKG.beforereport" />
<dataStructure>
<group name="G_SETUP" dataType="varchar2" source="Q_SETUP">
<element name="setup_org_id" dataType="number" value="SETUP_ORG_ID" />
<element name="Company_Name" dataType="varchar2" value="COMPANY_NAME" />
<element name="coa_id" dataType="number" value="COA_ID" />
<element name="Functional_Currency" dataType="varchar2" value="FUNCTIONAL_CURRENCY" />
<element name="Functional_Currency_Preci" dataType="number" value="FUNCTIONAL_CURRENCY_PRECISION" />
<element name="Report_Name" dataType="varchar2" value="Report_Name" />
<group name="G_CUST" dataType="varchar2" source="Q_CUST">
<element name="qcust_org_id" dataType="number" value="QCUST_ORG_ID" />
<element name="Customer_Name" dataType="varchar2" value="CUSTOMER_NAME" />
<element name="C_DATA_NOT_FOUND" dataType="number" value="C_DATA_NOT_FOUND" />
<element name="Customer_Number" dataType="varchar2" value="CUSTOMER_NUMBER" />
<element name="Customer_Age" dataType="number" value="CUSTOMER_AGE" />
<element name="c_customer_age" dataType="varchar2" value="c_customer_age" />
<element name="Customer_id" dataType="number" value="CUSTOMER_ID" />
<element name="Address_Age" dataType="number" value="ADDRESS_AGE" />
<element name="Address1" dataType="varchar2" value="ADDRESS1" />
<element name="Address2" dataType="varchar2" value="ADDRESS2" />
<element name="Address3" dataType="varchar2" value="ADDRESS3" />
<element name="Address4" dataType="varchar2" value="ADDRESS4" />
<element name="City" dataType="varchar2" value="CITY" />
<element name="State" dataType="varchar2" value="STATE" />
<element name="Postal_Code" dataType="varchar2" value="POSTAL_CODE" />
<element name="Country" dataType="varchar2" value="COUNTRY" />
<element name="Site_Use_id" dataType="number" value="SITE_USE_ID" />
<element name="Address_id" dataType="number" value="ADDRESS_ID" />
<element name="Location" dataType="varchar2" value="LOCATION" />
<element name="C_Contact" dataType="varchar2" value="C_Contact" />
<element name="C_Phone_Number" dataType="varchar2" value="C_Phone_Number" />
<element name="Sel_Contact" dataType="varchar2" value="Sel_Contact" />
<element name="c_address_age" dataType="varchar2" value="c_address_age" />
<element name="c_city_state_zip" dataType="varchar2" value="c_city_state_zip" />
<element name="C_CREDIT_SUMMARY" dataType="number" value="C_CREDIT_SUMMARY" />
<element name="C_LAST_CREDIT_MEMO_FORMULA" dataType="number" value="C_LAST_CREDIT_MEMO_FORMULA" />
<element name="c_profile_site_use_id" dataType="number" value="c_profile_site_use_id" />
<element name="c_customer_profile_id" dataType="number" value="c_customer_profile_id" />
<element name="c_cred_summ_collector" dataType="varchar2" value="c_cred_summ_collector" />
<element name="c_cred_summ_exempt_dun" dataType="varchar2" value="c_cred_summ_exempt_dun" />
<element name="c_cred_summ_terms" dataType="varchar2" value="c_cred_summ_terms" />
<element name="c_cred_summ_account_status" dataType="varchar2" value="c_cred_summ_account_status" />
<element name="c_cred_summ_credit_hold" dataType="varchar2" value="c_cred_summ_credit_hold" />
<element name="c_cred_summ_risk_code" dataType="varchar2" value="c_cred_summ_risk_code" />
<element name="c_cred_summ_credit_rating" dataType="varchar2" value="c_cred_summ_credit_rating" />
<element name="c_cred_summ_limit_tolerance" dataType="varchar2" value="c_cred_summ_limit_tolerance" />
<element name="c_cred_summ_limit_expire_date" dataType="varchar2" value="c_cred_summ_limit_expire_date" />
<element name="C_LAST_INVOICE_FORMULA" dataType="number" value="C_LAST_INVOICE_FORMULA" />
<element name="c_last_invoice_days_since" dataType="varchar2" value="c_last_invoice_days_since" />
<element name="c_last_invoice_date" dataType="varchar2" value="c_last_invoice_date" />
<element name="c_last_invoice_converted" dataType="varchar2" value="c_last_invoice_converted" />
<element name="c_last_invoice_amount" dataType="number" value="c_last_invoice_amount" />
<element name="cd_last_invoice_amount" dataType="varchar2" value="c_last_invoice_amount" function="CURRENCY_FORMAT" currencyCode="c_last_invoice_currency" width="19" />
<element name="c_last_invoice_currency" dataType="varchar2" value="c_last_invoice_currency" />
<element name="c_last_invoice_type" dataType="varchar2" value="c_last_invoice_type" />
<element name="c_last_invoice_number" dataType="varchar2" value="c_last_invoice_number" />
<element name="c_last_cm_rel_invoice" dataType="varchar2" value="c_last_cm_rel_invoice" />
<element name="c_last_cm_converted" dataType="varchar2" value="c_last_cm_converted" />
<element name="c_last_cm_amount" dataType="number" value="c_last_cm_amount" />
<element name="c_last_cm_id" dataType="number" value="c_last_cm_id" />
<element name="c_last_cm_prev_trx" dataType="number" value="c_last_cm_prev_trx" />
<element name="c_last_cm_days_since" dataType="varchar2" value="c_last_cm_days_since" />
<element name="c_last_cm_date" dataType="varchar2" value="c_last_cm_date" />
<element name="c_last_cm_currency" dataType="varchar2" value="c_last_cm_currency" />
<element name="c_last_cm_type" dataType="varchar2" value="c_last_cm_type" />
<element name="c_last_cm_number" dataType="varchar2" value="c_last_cm_number" />
<element name="cd_last_cm_amount" dataType="varchar2" value="c_last_cm_amount" function="CURRENCY_FORMAT" currencyCode="c_last_cm_currency" width="19" />
<element name="C_GUARANTEE_FORMULA" dataType="number" value="C_GUARANTEE_FORMULA" />
<element name="cd_last_guar_amount" dataType="varchar2" value="c_last_guar_amount" function="CURRENCY_FORMAT" currencyCode="c_last_guar_currency" width="19" />
<element name="c_last_guar_days_since" dataType="varchar2" value="c_last_guar_days_since" />
<element name="c_last_guar_date" dataType="varchar2" value="c_last_guar_date" />
<element name="c_last_guar_converted" dataType="varchar2" value="c_last_guar_converted" />
<element name="c_last_guar_amount" dataType="number" value="c_last_guar_amount" />
<element name="c_last_guar_currency" dataType="varchar2" value="c_last_guar_currency" />
<element name="c_last_guar_type" dataType="varchar2" value="c_last_guar_type" />
<element name="c_last_guar_number" dataType="varchar2" value="c_last_guar_number" />
<element name="C_LAST_DEPOSIT_FORMULA" dataType="number" value="C_LAST_DEPOSIT_FORMULA" />
<element name="cd_last_dep_amount" dataType="varchar2" value="c_last_dep_amount" function="CURRENCY_FORMAT" currencyCode="c_last_dep_currency" width="19" />
<element name="c_last_dep_days_since" dataType="varchar2" value="c_last_dep_days_since" />
<element name="c_last_dep_date" dataType="varchar2" value="c_last_dep_date" />
<element name="c_last_dep_converted" dataType="varchar2" value="c_last_dep_converted" />
<element name="c_last_dep_amount" dataType="number" value="c_last_dep_amount" />
<element name="c_last_dep_currency" dataType="varchar2" value="c_last_dep_currency" />
<element name="c_last_dep_type" dataType="varchar2" value="c_last_dep_type" />
<element name="c_last_dep_number" dataType="varchar2" value="c_last_dep_number" />
<element name="C_LAST_DM_FORMULA" dataType="number" value="C_LAST_DM_FORMULA" />
<element name="cd_last_dm_amount" dataType="varchar2" value="c_last_dm_amount" function="CURRENCY_FORMAT" currencyCode="c_last_dm_currency" width="19" />
<element name="c_last_dm_days_since" dataType="varchar2" value="c_last_dm_days_since" />
<element name="c_last_dm_date" dataType="varchar2" value="c_last_dm_date" />
<element name="c_last_dm_converted" dataType="varchar2" value="c_last_dm_converted" />
<element name="c_last_dm_amount" dataType="number" value="c_last_dm_amount" />
<element name="c_last_dm_currency" dataType="varchar2" value="c_last_dm_currency" />
<element name="c_last_dm_type" dataType="varchar2" value="c_last_dm_type" />
<element name="c_last_dm_number" dataType="varchar2" value="c_last_dm_number" />
<element name="C_LAST_CB_FORMULA" dataType="number" value="C_LAST_CB_FORMULA" />
<element name="cd_last_cb_amount" dataType="varchar2" value="c_last_cb_amount" function="CURRENCY_FORMAT" currencyCode="c_last_cb_currency" width="19" />
<element name="c_last_cb_days_since" dataType="number" value="c_last_cb_days_since" />
<element name="c_last_cb_date" dataType="varchar2" value="c_last_cb_date" />
<element name="c_last_cb_converted" dataType="varchar2" value="c_last_cb_converted" />
<element name="c_last_cb_amount" dataType="number" value="c_last_cb_amount" />
<element name="c_last_cb_currency" dataType="varchar2" value="c_last_cb_currency" />
<element name="c_last_cb_type" dataType="varchar2" value="c_last_cb_type" />
<element name="c_last_cb_number" dataType="varchar2" value="c_last_cb_number" />
<element name="C_LAST_PAYMENT_FORMULA" dataType="number" value="C_LAST_PAYMENT_FORMULA" />
<element name="cd_last_payment_amount" dataType="varchar2" value="c_last_payment_amount" function="CURRENCY_FORMAT" currencyCode="c_last_payment_currency" width="19" />
<element name="c_last_payment_rel_invoice" dataType="varchar2" value="c_last_payment_rel_invoice" />
<element name="c_last_payment_days_since" dataType="varchar2" value="c_last_payment_days_since" />
<element name="c_last_payment_date" dataType="varchar2" value="c_last_payment_date" />
<element name="c_last_payment_converted" dataType="varchar2" value="c_last_payment_converted" />
<element name="c_last_payment_amount" dataType="number" value="c_last_payment_amount" />
<element name="c_last_payment_currency" dataType="varchar2" value="c_last_payment_currency" />
<element name="c_last_payment_type" dataType="varchar2" value="c_last_payment_type" />
<element name="c_last_payment_number" dataType="varchar2" value="c_last_payment_number" />
<element name="C_LAST_ADJ_FORMULA" dataType="number" value="C_LAST_ADJ_FORMULA" />
<element name="cd_last_adj_amount" dataType="varchar2" value="c_last_adj_amount" function="CURRENCY_FORMAT" currencyCode="c_last_adj_currency" width="19" />
<element name="c_last_adj_days_since" dataType="varchar2" value="c_last_adj_days_since" />
<element name="c_last_adj_date" dataType="varchar2" value="c_last_adj_date" />
<element name="c_last_adj_converted" dataType="varchar2" value="c_last_adj_converted" />
<element name="c_last_adj_amount" dataType="number" value="c_last_adj_amount" />
<element name="c_last_adj_currency" dataType="varchar2" value="c_last_adj_currency" />
<element name="c_last_adj_rel_invoice" dataType="varchar2" value="c_last_adj_rel_invoice" />
<element name="c_last_adj_type" dataType="varchar2" value="c_last_adj_type" />
<element name="c_last_adj_number" dataType="varchar2" value="c_last_adj_number" />
<element name="C_LAST_WRITEOFF_FORMULA" dataType="number" value="C_LAST_WRITEOFF_FORMULA" />
<element name="cd_last_wo_amount" dataType="varchar2" value="c_last_wo_amount" function="CURRENCY_FORMAT" currencyCode="c_last_wo_currency" width="19" />
<element name="c_last_wo_days_since" dataType="varchar2" value="c_last_wo_days_since" />
<element name="c_last_wo_date" dataType="varchar2" value="c_last_wo_date" />
<element name="c_last_wo_converted" dataType="varchar2" value="c_last_wo_converted" />
<element name="c_last_wo_amount" dataType="number" value="c_last_wo_amount" />
<element name="c_last_wo_currency" dataType="varchar2" value="c_last_wo_currency" />
<element name="c_last_wo_rel_invoice" dataType="varchar2" value="c_last_wo_rel_invoice" />
<element name="c_last_wo_type" dataType="varchar2" value="c_last_wo_type" />
<element name="c_last_wo_number" dataType="varchar2" value="c_last_wo_number" />
<element name="C_LAST_STATEMENT_FORMULA" dataType="number" value="C_LAST_STATEMENT_FORMULA" />
<element name="c_last_stmnt_next_trx_date" dataType="varchar2" value="c_last_stmnt_next_trx_date" />
<element name="c_last_st_date" dataType="varchar2" value="c_last_st_date" />
<element name="c_last_st_type" dataType="varchar2" value="c_last_st_type" />
<element name="c_last_st_number" dataType="varchar2" value="c_last_st_number" />
<element name="c_last_st_days_since" dataType="varchar2" value="c_last_st_days_since" />
<element name="C_LAST_DN_FORMULA" dataType="number" value="C_LAST_DN_FORMULA" />
<element name="cd_last_dn_amount" dataType="varchar2" value="c_last_dn_amount" function="CURRENCY_FORMAT" currencyCode="c_last_dn_currency" width="19" />
<element name="c_last_dn_days_since" dataType="varchar2" value="c_last_dn_days_since" />
<element name="c_last_dn_date" dataType="varchar2" value="c_last_dn_date" />
<element name="c_last_dn_currency" dataType="varchar2" value="c_last_dn_currency" />
<element name="c_last_dn_amount" dataType="number" value="c_last_dn_amount" />
<element name="c_last_dn_type" dataType="varchar2" value="c_last_dn_type" />
<element name="c_last_dn_number" dataType="varchar2" value="c_last_dn_number" />
<element name="C_LAST_NSF_FORMULA" dataType="number" value="C_LAST_NSF_FORMULA" />
<element name="cd_last_nsf_amount" dataType="varchar2" value="c_last_nsf_amount" function="CURRENCY_FORMAT" currencyCode="c_last_nsf_currency" width="19" />
<element name="c_last_nsf_currency" dataType="varchar2" value="c_last_nsf_currency" />
<element name="c_last_nsf_days_since" dataType="varchar2" value="c_last_nsf_days_since" />
<element name="c_last_nsf_date" dataType="varchar2" value="c_last_nsf_date" />
<element name="c_last_nsf_converted" dataType="varchar2" value="c_last_nsf_converted" />
<element name="c_last_nsf_amount" dataType="number" value="c_last_nsf_amount" />
<element name="c_last_nsf_type" dataType="varchar2" value="c_last_nsf_type" />
<element name="c_last_nsf_number" dataType="varchar2" value="c_last_nsf_number" />
<element name="C_LAST_CONTACT_FORMULA" dataType="number" value="C_LAST_CONTACT_FORMULA" />
<element name="cd_last_contact_amount" dataType="varchar2" value="c_last_contact_amount" function="CURRENCY_FORMAT" currencyCode="c_last_contact_currency" width="19" />
<element name="c_last_contact_days_since" dataType="varchar2" value="c_last_contact_days_since" />
<element name="c_last_contact_date" dataType="varchar2" value="c_last_contact_date" />
<element name="c_last_contact_amount" dataType="number" value="c_last_contact_amount" />
<element name="c_last_contact_converted" dataType="varchar2" value="c_last_contact_converted" />
<element name="c_last_contact_currency" dataType="varchar2" value="c_last_contact_currency" />
<element name="c_last_contact_rel_invoice" dataType="varchar2" value="c_last_contact_rel_invoice" />
<element name="c_last_contact_number" dataType="varchar2" value="c_last_contact_number" />
<element name="C_LAST_HOLD_FORMULA" dataType="number" value="C_LAST_HOLD_FORMULA" />
<element name="cd_last_hold_amount" dataType="varchar2" value="c_last_hold_amount" function="CURRENCY_FORMAT" currencyCode="functional_currency" width="19" />
<element name="c_last_hold_days_since" dataType="varchar2" value="c_last_hold_days_since" />
<element name="c_last_hold_date" dataType="varchar2" value="c_last_hold_date" />
<element name="c_last_hold_amount" dataType="number" value="c_last_hold_amount" />
<element name="c_last_hold_number" dataType="varchar2" value="c_last_hold_number" />
<element name="Customer_Profile_Id" dataType="number" value="CUSTOMER_PROFILE_ID" />
<group name="G_BUCKET" dataType="varchar2" source="Q_BUCKET">
<element name="Currency_Bucket" dataType="varchar2" value="CURRENCY_BUCKET" />
<element name="Aging_Balance_Outstanding" dataType="number" value="AGING_BALANCE_OUTSTANDING" />
<element name="cd_aging_balance_os" dataType="varchar2" value="AGING_BALANCE_OUTSTANDING" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="Total_Cust_b0" dataType="number" value="TOTAL_CUST_B0" />
<element name="Total_Cust_b1" dataType="number" value="TOTAL_CUST_B1" />
<element name="Total_Cust_b2" dataType="number" value="TOTAL_CUST_B2" />
<element name="Total_Cust_b3" dataType="number" value="TOTAL_CUST_B3" />
<element name="Total_Cust_b4" dataType="number" value="TOTAL_CUST_B4" />
<element name="Total_Cust_b5" dataType="number" value="TOTAL_CUST_B5" />
<element name="Total_Cust_b6" dataType="number" value="TOTAL_CUST_B6" />
<element name="CP_DEFAULT_FLAG" dataType="varchar2" value="CP_DEFAULT_FLAG" />
<element name="cd_total_cust_b6" dataType="varchar2" value="total_cust_b6" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_total_cust_b5" dataType="varchar2" value="total_cust_b5" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_total_cust_b4" dataType="varchar2" value="total_cust_b4" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_total_cust_b3" dataType="varchar2" value="total_cust_b3" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_total_cust_b2" dataType="varchar2" value="total_cust_b2" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_total_cust_b1" dataType="varchar2" value="TOTAL_CUST_B1" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_total_cust_b0" dataType="varchar2" value="total_cust_b0" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="Aging_Convert_Bucket" dataType="varchar2" value="AGING_CONVERT_BUCKET" />
<element name="C_CALC_PERCENT" dataType="number" value="C_CALC_PERCENT" />
<element name="c_percent_b0" dataType="varchar2" value="c_percent_b0" />
<element name="c_percent_b1" dataType="varchar2" value="c_percent_b1" />
<element name="c_percent_b2" dataType="varchar2" value="c_percent_b2" />
<element name="c_percent_b3" dataType="varchar2" value="c_percent_b3" />
<element name="c_percent_b4" dataType="varchar2" value="c_percent_b4" />
<element name="c_percent_b5" dataType="varchar2" value="c_percent_b5" />
<element name="c_percent_b6" dataType="varchar2" value="c_percent_b6" />
<element name="C_COMPUTE_AMT" dataType="number" value="C_COMPUTE_AMT" />
<element name="c_aging_on_account" dataType="number" value="c_aging_on_account" />
<element name="c_aging_unapplied" dataType="number" value="c_aging_unapplied" />
<element name="c_aging_convert_on_account" dataType="varchar2" value="c_aging_convert_on_account" />
<element name="c_aging_convert_unapplied" dataType="varchar2" value="c_aging_convert_unapplied" />
<element name="c_aging_credit" dataType="number" value="c_aging_credit" />
<element name="c_aging_convert_credit" dataType="varchar2" value="c_aging_convert_credit" />
<element name="c_adjusted_balance" dataType="number" value="c_adjusted_balance" />
<element name="c_aging_convert_collection" dataType="varchar2" value="c_aging_convert_collection" />
<element name="c_aging_in_collection" dataType="number" value="c_aging_in_collection" />
<element name="cd_aging_in_collection" dataType="varchar2" value="C_AGING_IN_COLLECTION" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_aging_on_account" dataType="varchar2" value="C_AGING_ON_ACCOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_aging_unapplied" dataType="varchar2" value="C_AGING_UNAPPLIED" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_aging_credit" dataType="varchar2" value="C_AGING_CREDIT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_adjusted_balance" dataType="varchar2" value="C_ADJUSTED_BALANCE" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="c_cust_hist_high_invoice_amt" dataType="number" value="c_cust_hist_high_invoice_amt" />
<element name="cd_cust_hist_high_inv_amt" dataType="varchar2" value="C_CUST_HIST_HIGH_INVOICE_AMT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="c_cust_hist_conv_high_invoice" dataType="varchar2" value="c_cust_hist_conv_high_invoice" />
<element name="c_cust_hist_high_invoice_date" dataType="varchar2" value="c_cust_hist_high_invoice_date" />
<element name="C_HIGH_INV_FORMULA" dataType="number" value="C_HIGH_INV_FORMULA" />
<element name="c_cust_hist_high_limit_amt" dataType="number" value="c_cust_hist_high_limit_amt" />
<element name="cd_cust_hist_high_lim_amt" dataType="varchar2" value="C_CUST_HIST_HIGH_LIMIT_AMT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="c_cust_hist_high_limit_date" dataType="date" value="c_cust_hist_high_limit_date" />
<element name="c_ytd_nsf_count" dataType="number" value="c_ytd_nsf_count" />
<element name="c_ytd_convert_nsf" dataType="varchar2" value="c_ytd_convert_nsf" />
<element name="c_ytd_nsf_amount" dataType="number" value="c_ytd_nsf_amount" />
<element name="c_ytd_conv_unearned_discount" dataType="varchar2" value="c_ytd_conv_unearned_discount" />
<element name="c_ytd_unearned_discount_amount" dataType="number" value="c_ytd_unearned_discount_amount" />
<element name="c_ytd_convert_earned_discount" dataType="varchar2" value="c_ytd_convert_earned_discount" />
<element name="c_ytd_earned_discount_amount" dataType="number" value="c_ytd_earned_discount_amount" />
<element name="c_ytd_on_time_payments_count" dataType="number" value="c_ytd_on_time_payments_count" />
<element name="c_ytd_late_payments_count" dataType="number" value="c_ytd_late_payments_count" />
<element name="c_ytd_average_days_late" dataType="number" value="c_ytd_average_days_late" />
<element name="c_ytd_average_payment_days" dataType="number" value="c_ytd_average_payment_days" />
<element name="c_ytd_finance_charge_count" dataType="number" value="c_ytd_finance_charge_count" />
<element name="c_ytd_convert_finance_charge" dataType="varchar2" value="c_ytd_convert_finance_charge" />
<element name="c_ytd_finance_charge_amount" dataType="number" value="c_ytd_finance_charge_amount" />
<element name="c_ytd_credit_count" dataType="number" value="c_ytd_credit_count" />
<element name="c_ytd_convert_credit" dataType="varchar2" value="c_ytd_convert_credit" />
<element name="c_ytd_credit_amount" dataType="number" value="c_ytd_credit_amount" />
<element name="c_ytd_payment_count" dataType="number" value="c_ytd_payment_count" />
<element name="c_ytd_convert_payment" dataType="varchar2" value="c_ytd_convert_payment" />
<element name="c_ytd_payment_amount" dataType="number" value="c_ytd_payment_amount" />
<element name="c_ytd_sales_count" dataType="number" value="c_ytd_sales_count" />
<element name="c_ytd_convert_sales" dataType="varchar2" value="c_ytd_convert_sales" />
<element name="c_ytd_sales_amount" dataType="number" value="c_ytd_sales_amount" />
<element name="c_ytd_convert_writeoff" dataType="varchar2" value="c_ytd_convert_writeoff" />
<element name="c_ytd_writeoff_amount" dataType="number" value="c_ytd_writeoff_amount" />
<element name="C_ROLLING_SUMMARY_CALC" dataType="number" value="C_ROLLING_SUMMARY_CALC" />
<element name="cd_ytd_nsf_amount" dataType="varchar2" value="C_YTD_NSF_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_ytd_unearned_dis_amt" dataType="varchar2" value="C_YTD_UNEARNED_DISCOUNT_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_ytd_earned_dis_amt" dataType="varchar2" value="C_YTD_EARNED_DISCOUNT_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_ytd_fin_charge_amount" dataType="varchar2" value="C_YTD_FINANCE_CHARGE_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_ytd_credit_amount" dataType="varchar2" value="C_YTD_CREDIT_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_ytd_payment_amount" dataType="varchar2" value="C_YTD_PAYMENT_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="CP_limit_currency" dataType="varchar2" value="CP_limit_currency" />
<element name="C_currency_lookup" dataType="number" value="C_currency_lookup" />
<element name="CP_related_currencies" dataType="varchar2" value="CP_related_currencies" />
<element name="CP_txn_cur" dataType="varchar2" value="CP_txn_cur" />
<element name="cd_ytd_sales_amount" dataType="varchar2" value="C_YTD_SALES_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="cd_ytd_writeoff_amount" dataType="varchar2" value="C_YTD_WRITEOFF_AMOUNT" function="CURRENCY_FORMAT" currencyCode="Currency_Bucket" width="19" />
<element name="ps_customer" dataType="number" value="PS_CUSTOMER" />
<element name="ps_site" dataType="number" value="PS_SITE" />
<group name="G_CREDIT_AMOUNTS" dataType="varchar2" source="Q_CREDIT_AMOUNTS">
<element name="CREDIT_LIMIT" dataType="number" value="CREDIT_LIMIT" />
<element name="QC_CUSTOMER" dataType="number" value="QC_CUSTOMER" />
<element name="QC_SITE" dataType="number" value="QC_SITE" />
<element name="ORDER_CREDIT_LIMIT" dataType="number" value="ORDER_CREDIT_LIMIT" />
<element name="CURRENCY_CREDIT" dataType="varchar2" value="CURRENCY_CREDIT" />
<element name="C_CREDIT_AMOUNTS_CALC" dataType="number" value="C_CREDIT_AMOUNTS_CALC" />
<element name="c_cred_summ_convert_limit" dataType="varchar2" value="c_cred_summ_convert_limit" />
<element name="c_cred_summ_exceeded" dataType="number" value="c_cred_summ_exceeded" />
<element name="cd_credit_limit" dataType="varchar2" value="credit_limit" function="CURRENCY_FORMAT" currencyCode="Currency_Credit" width="19" />
<element name="c_cred_summ_available" dataType="number" value="c_cred_summ_available" />
<element name="cd_order_credit_limit" dataType="varchar2" value="order_credit_limit" function="CURRENCY_FORMAT" currencyCode="Currency_Credit" width="19" />
<element name="cd_cred_summ_available" dataType="varchar2" value="c_cred_summ_available" function="CURRENCY_FORMAT" currencyCode="Currency_Credit" width="19" />
<element name="cd_cred_summ_exceeded" dataType="varchar2" value="c_cred_summ_exceeded" function="CURRENCY_FORMAT" currencyCode="Currency_Credit" width="19" />
<element name="CP_trx_amount" dataType="number" value="CP_trx_amount" />
<element name="CP_trx_curr" dataType="number" value="CP_trx_curr" />
<group name="G_TRX_CONVERSION" dataType="varchar2" source="Q_TRX_CONVERSION">
<element name="exchange_rate_type" dataType="varchar2" value="EXCHANGE_RATE_TYPE" />
<element name="trx_cur" dataType="varchar2" value="TRX_CUR" />
<element name="trx_amount_due" dataType="number" value="TRX_AMOUNT_DUE" />
<element name="CP_limit_curr_amt" dataType="number" value="CP_limit_curr_amt" />
<element name="CF_calc_RATE_amount" dataType="number" value="CF_calc_RATE_amount" />
<element name="trx_cust" dataType="number" value="TRX_CUST" />
<element name="trx_site" dataType="number" value="TRX_SITE" />
<element name="CD_TRX_AMOUNT" dataType="varchar2" value="CP_adjusted_amount" function="CURRENCY_FORMAT" currencyCode="trx_cur" width="19" />
<element name="CD_LIMIT_CUR_AMT" dataType="varchar2" value="cp_limit_curr_amt" function="CURRENCY_FORMAT" currencyCode="cp_limit_currency" width="19" />
<element name="CP_rate" dataType="number" value="CP_rate" />
<element name="CP_adjusted_amount" dataType="number" value="CP_adjusted_amount" />
</group>
</group>
</group>
<group name="G_CREDIT_LIMITS" dataType="varchar2" source="Q_CREDIT_LIMITS">
<element name="currency_credit1" dataType="varchar2" value="CURRENCY_CREDIT1" />
<element name="credit_limit1" dataType="number" value="CREDIT_LIMIT1" />
<element name="order_credit_limit1" dataType="number" value="ORDER_CREDIT_LIMIT1" />
<element name="qc_customer1" dataType="number" value="QC_CUSTOMER1" />
<element name="qc_site1" dataType="number" value="QC_SITE1" />
<element name="CP_TRX_AMOUNT1" dataType="number" value="CP_TRX_AMOUNT1" />
<element name="C_CRED_SUMM_AVAILABLE1" dataType="number" value="C_CRED_SUMM_AVAILABLE1" />
<element name="CD_CREDIT_LIMIT1" dataType="varchar2" value="CREDIT_LIMIT1" function="CURRENCY_FORMAT" currencyCode="Currency_Credit1" width="19" />
<element name="CD_CRED_SUMM_AVAILABLE1" dataType="varchar2" value="c_cred_summ_available1" function="CURRENCY_FORMAT" currencyCode="Currency_Credit1" width="19" />
<element name="C_CRED_CONVERT_LIMIT1" dataType="varchar2" value="C_CRED_CONVERT_LIMIT1" />
<element name="C_CRED_SUMM_EXCEEDED1" dataType="number" value="C_CRED_SUMM_EXCEEDED1" />
<element name="CP_TRX_CUR1" dataType="varchar2" value="CP_TRX_CUR1" />
<element name="DEFAULT_FLAG" dataType="varchar2" value="DEFAULT_FLAG" />
<element name="LIMIT_CURRENCY" dataType="varchar2" value="LIMIT_CURRENCY" />
<element name="C_CREDIT_AMOUNTS_CALC1" dataType="number" value="C_CREDIT_AMOUNTS_CALC1" />
<element name="RELATED_CURRENCIES" dataType="varchar2" value="RELATED_CURRENCIES" />
<element name="CD_CRED_SUMM_EXCEEDED1" dataType="varchar2" value="c_cred_summ_exceeded1" function="CURRENCY_FORMAT" currencyCode="Currency_Credit1" width="19" />
<element name="CD_ORDER_CREDIT_LIMIT1" dataType="varchar2" value="order_credit_limit1" function="CURRENCY_FORMAT" currencyCode="Currency_Credit1" width="19" />
<element name="CF_CURRENCY_LOOKUP" dataType="number" value="CF_CURRENCY_LOOKUP" />
<group name="G_org_Limits" dataType="varchar2" source="Q_ORG">
<element name="org_Currency_code" dataType="varchar2" value="ORG_CURRENCY_CODE" />
<element name="org_overall_limit" dataType="number" value="ORG_OVERALL_LIMIT" />
<element name="Org_trx_Limit" dataType="number" value="ORG_TRX_LIMIT" />
<element name="C_cred_summ_exceeded2" dataType="number" value="C_cred_summ_exceeded2" />
<element name="C_cred_summ_available2" dataType="number" value="C_cred_summ_available2" />
<element name="Cd_credit_limit2" dataType="varchar2" value="org_overall_limit" function="CURRENCY_FORMAT" currencyCode="org_currency_code" width="19" />
<element name="Cd_order_credit_limit2" dataType="varchar2" value="org_trx_limit" function="CURRENCY_FORMAT" currencyCode="Org_Currency_Code" width="19" />
<element name="Cd_cred_summ_available2" dataType="varchar2" value="c_cred_summ_available2" function="CURRENCY_FORMAT" currencyCode="org_currency_code" width="19" />
<element name="C_cred_convert_limit2" dataType="varchar2" value="C_cred_convert_limit2" />
<element name="CP_trx_amount2" dataType="number" value="CP_trx_amount2" />
<element name="CP_trx_cur2" dataType="varchar2" value="CP_trx_cur2" />
<element name="C_OERG_CREDIT_CALC" dataType="number" value="C_OERG_CREDIT_CALC" />
<element name="Cd_cred_summ_exceeded2" dataType="varchar2" value="c_cred_summ_exceeded2" function="CURRENCY_FORMAT" currencyCode="Org_currency_code" width="19" />
<element name="C_org_profile" dataType="number" value="C_org_profile" />
<element name="C_org_credit_hold" dataType="varchar2" value="C_org_credit_hold" />
<element name="C_org_credit_rating" dataType="varchar2" value="C_org_credit_rating" />
<element name="C_Org_limit_tolerance" dataType="varchar2" value="C_Org_limit_tolerance" />
<element name="C_credit_profile_id" dataType="number" value="C_credit_profile_id" />
<group name="G_org_trx_cur" dataType="varchar2" source="Q_org_trx">
<element name="org_trx_cur" dataType="varchar2" value="ORG_TRX_CUR" />
<element name="org_trx_cust" dataType="number" value="ORG_TRX_CUST" />
<element name="org_trx_site" dataType="number" value="ORG_TRX_SITE" />
<element name="trx_amount_due2" dataType="number" value="TRX_AMOUNT_DUE" />
<element name="CP_limit_curr_amt2" dataType="number" value="CP_limit_curr_amt2" />
<element name="CP_Rate2" dataType="number" value="CP_Rate2" />
<element name="CP_adjusted_amount2" dataType="number" value="CP_adjusted_amount2" />
<element name="Cd_trx_amount2" dataType="varchar2" value="CP_adjusted_amount2" function="CURRENCY_FORMAT" currencyCode="org_trx_cur" width="19" />
<element name="Cd_limit_curr_amt2" dataType="number" value="cp_limit_curr_amt2" function="CURRENCY_FORMAT" currencyCode="limit_currency" width="19" />
<element name="CF_org_calc_amount" dataType="number" value="CF_org_calc_amount" />
</group>
</group>
<group name="G_customer_limit" dataType="varchar2" source="Q_customer_limit">
<element name="exchange_rate_type1" dataType="varchar2" value="EXCHANGE_RATE_TYPE" />
<element name="trx_amount_due1" dataType="number" value="TRX_AMOUNT_DUE" />
<element name="trx_cur2" dataType="varchar2" value="TRX_CUR2" />
<element name="trx_cust2" dataType="number" value="TRX_CUST2" />
<element name="CP_limit_curr_amt1" dataType="number" value="CP_limit_curr_amt1" />
<element name="CP_rate1" dataType="number" value="CP_rate1" />
<element name="CP_adjusted_amount1" dataType="number" value="CP_adjusted_amount1" />
<element name="Cd_trx_amount1" dataType="varchar2" value="CP_adjusted_amount1" function="CURRENCY_FORMAT" currencyCode="trx_cur2" width="19" />
<element name="Cd_limit_cur_amt1" dataType="varchar2" value="cp_limit_curr_amt1" function="CURRENCY_FORMAT" currencyCode="limit_currency" width="19" />
<element name="CF_CALC_RATE_AMOUNT_FORMULA1" dataType="number" value="CF_CALC_RATE_AMOUNT_FORMULA1" />
<element name="trx_site2" dataType="number" value="TRX_SITE2" />
</group>
</group>
</group>
</group>
<element name="RP_COMPANY_NAME" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_COMPANY_NAME_p" />
<element name="RP_REPORT_NAME" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_REPORT_NAME_p" />
<element name="RP_DATA_FOUND" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_DATA_FOUND_p" />
<element name="RP_DATE_RANGE" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_DATE_RANGE_p" />
<element name="RP_BUCKET_DAYS_FROM_0" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_FROM_0_p" />
<element name="RP_BUCKET_DAYS_FROM_1" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_FROM_1_p" />
<element name="RP_BUCKET_DAYS_FROM_2" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_FROM_2_p" />
<element name="RP_BUCKET_DAYS_FROM_3" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_FROM_3_p" />
<element name="RP_BUCKET_DAYS_FROM_4" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_FROM_4_p" />
<element name="RP_BUCKET_DAYS_FROM_5" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_FROM_5_p" />
<element name="RP_BUCKET_DAYS_TO_0" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_TO_0_p" />
<element name="RP_BUCKET_DAYS_TO_1" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_TO_1_p" />
<element name="RP_BUCKET_DAYS_TO_2" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_TO_2_p" />
<element name="RP_BUCKET_DAYS_TO_3" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_TO_3_p" />
<element name="RP_BUCKET_DAYS_TO_4" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_TO_4_p" />
<element name="RP_BUCKET_DAYS_TO_5" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_TO_5_p" />
<element name="RP_BUCKET_LINE_TYPE_0" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_LINE_TYPE_0_p" />
<element name="RP_BUCKET_LINE_TYPE_1" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_LINE_TYPE_1_p" />
<element name="RP_BUCKET_LINE_TYPE_2" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_LINE_TYPE_2_p" />
<element name="RP_BUCKET_LINE_TYPE_3" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_LINE_TYPE_3_p" />
<element name="RP_BUCKET_LINE_TYPE_4" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_LINE_TYPE_4_p" />
<element name="RP_BUCKET_LINE_TYPE_5" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_LINE_TYPE_5_p" />
<element name="RP_BUCKET_TITLE0" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_TITLE0_p" />
<element name="RP_BUCKET_TITLE1" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_TITLE1_p" />
<element name="RP_BUCKET_TITLE2" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_TITLE2_p" />
<element name="RP_BUCKET_TITLE3" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_TITLE3_p" />
<element name="RP_BUCKET_TITLE4" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_TITLE4_p" />
<element name="RP_BUCKET_TITLE5" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_TITLE5_p" />
<element name="RP_BUCKET_CATEGORY" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_CATEGORY_p" />
<element name="RP_BUCKET_DAYS_FROM_6" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_FROM_6_p" />
<element name="RP_BUCKET_DAYS_TO_6" dataType="number" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_DAYS_TO_6_p" />
<element name="RP_BUCKET_LINE_TYPE_6" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_LINE_TYPE_6_p" />
<element name="RP_BUCKET_TITLE6" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_BUCKET_TITLE6_p" />
<element name="C_industry_code" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.C_industry_code_p" />
<element name="C_sales_title" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.C_sales_title_p" />
<element name="RP_UNAVAILABLE" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_UNAVAILABLE_p" />
<element name="RP_YEARS" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_YEARS_p" />
<element name="RP_NONE" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_NONE_p" />
<element name="RP_NA_UPPER" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_NA_UPPER_p" />
<element name="RP_NO_LIMIT" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_NO_LIMIT_p" />
<element name="RP_MESSAGE" dataType="varchar2" value="AR_ARXCCS_XMLP_PKG.RP_MESSAGE_p" />
</dataStructure>
<dataTrigger name="afterReportTrigger" source="AR_ARXCCS_XMLP_PKG.afterreport()" />
</dataTemplate>

1 comment:

shivaniFebruary 25, 2019 at 3:21 AM
Thanks for sharing a useful information.. we have learnt so much information from your blog..... keep sharing
Workday HCM Online Training
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
ReplyDelete
Replies

Add comment