[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS (
p_case_folder_id => g_data_case_folder_id,
p_data_point_id => p_data_point_id,
p_data_point_value => p_data_point_value,
p_score => NULL,
p_errmsg => l_errmsg,
p_resultout => l_resultout);
AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS (
p_case_folder_id => p_case_folder_id,
p_data_point_id => p_data_point_id,
p_data_point_value => p_data_point_value,
p_score => NULL,
p_errmsg => l_errmsg,
p_resultout => l_resultout);
p_error_msg := 'Unable to create/Update records in AR_CMGT_CF_DTLS for Data Point Id: '||p_data_point_id;
AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS (
p_case_folder_id => g_data_case_folder_id,
p_data_point_id => p_data_point_id,
p_data_point_value => p_data_point_value,
p_score => NULL,
p_errmsg => x_error_msg,
p_resultout => x_resultout);
AR_CMGT_CONTROLS.UPDATE_CASE_FOLDER_DETAILS (
p_case_folder_id => p_case_folder_id,
p_data_point_id => p_data_point_id,
p_data_point_value => p_data_point_value,
p_score => NULL,
p_errmsg => x_error_msg,
p_resultout => x_resultout);
x_error_msg := 'Unable to create/Update records in AR_CMGT_CF_DTLS for Data Point Id: '||p_data_point_id;
SELECT sum(amount_settled), sum(ded_count)
INTO l_deduction_amount, l_deduction_count
FROM (
SELECT round(gl_currency_api.convert_amount(currency_code,
p_limit_currency, sysdate,
p_exchange_rate_type,
sum(amount_settled)),2) amount_settled,
count(*) ded_count
FROM ozf_claims_all
WHERE status_code = 'CLOSED'
AND claim_class = 'DEDUCTION'
AND settled_date >= ADD_MONTHS(sysdate,(-p_period))
AND cust_account_id IN (
select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type =
FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
UNION
select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
AND currency_code IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id)
= p_credit_request_id)
group by currency_code);
SELECT sum(amount_settled), sum(ded_count)
INTO l_deduction_amount, l_deduction_count
FROM (
SELECT round(gl_currency_api.convert_amount(currency_code,
p_limit_currency, sysdate,
p_exchange_rate_type,
sum(amount_settled)),2) amount_settled,
count(*) ded_count
FROM ozf_claims_all
WHERE status_code = 'CLOSED'
AND claim_class = 'DEDUCTION'
AND cust_account_id = p_cust_account_id
AND org_id = decode(p_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
AND currency_code IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id)
= p_credit_request_id)
AND settled_date >= ADD_MONTHS(sysdate,(-p_period))
group by currency_code );
SELECT sum(amount_settled), sum(ded_count)
INTO l_deduction_amount, l_deduction_count
FROM (
SELECT round(gl_currency_api.convert_amount(currency_code,
p_limit_currency, sysdate,
p_exchange_rate_type,
sum(amount_settled)),2) amount_settled,
count(*) ded_count
FROM ozf_claims_all
WHERE status_code = 'CLOSED'
AND claim_class = 'DEDUCTION'
AND settled_date >= ADD_MONTHS(sysdate,(-p_period))
AND cust_account_id = p_cust_account_id
AND cust_billto_acct_site_id = p_site_use_id
AND currency_code IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id)
= p_credit_request_id)
group by currency_code );
SELECT currency from ar_cmgt_curr_usage_gt;
l_party_sql VARCHAR2(4000) :='select LARGEST_INV_DATE,
largest_inv_amount
from ( select as_of_date LARGEST_INV_DATE,
gl_currency_api.convert_amount(currency,
:1,sysdate,
:2,
largest_inv_amount)largest_inv_amount,
RANK() OVER (PARTITION BY currency
ORDER BY largest_inv_amount desc,
largest_inv_cust_trx_id desc) rank_amount
FROM AR_TRX_SUMMARY
where cust_account_id in (
select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = ''ORGANIZATION''
and parent_table_name = ''HZ_PARTIES''
and child_object_type = ''ORGANIZATION''
and parent_id = :3
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type =
FND_PROFILE.VALUE(''AR_CMGT_HIERARCHY_TYPE'')
and :4 <> ''LNS''
union select :5 from dual
union
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = :6
and participant_type_code = ''COBORROWER''
and :7 = ''LNS''
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
and currency = :8
and largest_inv_cust_trx_id is not null
and as_of_date >= ADD_MONTHS(sysdate,(-:9)) )
Where rank_amount = 1 ';
l_account_sql VARCHAR2(4000) :='select LARGEST_INV_DATE,
largest_inv_amount
from ( select as_of_date LARGEST_INV_DATE,
gl_currency_api.convert_amount(currency,
:1,sysdate,
:2,
largest_inv_amount)largest_inv_amount,
RANK() OVER (PARTITION BY cust_account_id,currency
ORDER BY largest_inv_amount desc,
largest_inv_cust_trx_id desc) rank_amount
FROM AR_TRX_SUMMARY
where cust_account_id = :3
and org_id = decode(:4,''Y'', org_id, ''N'',
decode(:5,null, org_id, :6), null,
decode(:7,null, org_id, :8))
and currency = :9
and largest_inv_cust_trx_id is not null
and as_of_date >= ADD_MONTHS(sysdate,(-:10)) )
Where rank_amount = 1';
l_site_sql VARCHAR2(4000) :='select LARGEST_INV_DATE,
largest_inv_amount
from ( select as_of_date LARGEST_INV_DATE,
gl_currency_api.convert_amount(currency,
:1,sysdate,
:2,
largest_inv_amount)largest_inv_amount,
RANK() OVER (PARTITION BY cust_account_id, site_use_id,currency
ORDER BY largest_inv_amount desc,
largest_inv_cust_trx_id desc) rank_amount
FROM AR_TRX_SUMMARY
where cust_account_id = :3
and site_use_id = :4
and currency = :5
and largest_inv_cust_trx_id is not null
and as_of_date >= ADD_MONTHS(sysdate,(-:6)) )
Where rank_amount = 1 ';
l_high_credit_party_sql VARCHAR2(4000) :='select op_bal_high_watermark_date,
op_bal_high_watermark
from ( select as_of_date op_bal_high_watermark_date,
gl_currency_api.convert_amount(currency,
:1,sysdate,
:2,
op_bal_high_watermark)op_bal_high_watermark,
RANK() OVER (PARTITION BY currency
ORDER BY op_bal_high_watermark desc,
largest_inv_cust_trx_id desc) high_amount
FROM AR_TRX_SUMMARY
where cust_account_id in (
select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = ''ORGANIZATION''
and parent_table_name = ''HZ_PARTIES''
and child_object_type = ''ORGANIZATION''
and parent_id = :3
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type =
FND_PROFILE.VALUE(''AR_CMGT_HIERARCHY_TYPE'')
union select :4 from dual
))
and currency = :5
and largest_inv_cust_trx_id is not null
and as_of_date >= ADD_MONTHS(sysdate,(-:6)) )
Where high_amount = 1';
l_high_credit_account_sql VARCHAR2(4000) :='select op_bal_high_watermark_date,
op_bal_high_watermark
from ( select as_of_date op_bal_high_watermark_date,
gl_currency_api.convert_amount(currency,
:1,sysdate,
:2,
op_bal_high_watermark)op_bal_high_watermark,
RANK() OVER (PARTITION BY cust_account_id,currency,org_id
ORDER BY op_bal_high_watermark desc,
largest_inv_cust_trx_id desc) high_amount
FROM AR_TRX_SUMMARY
where cust_account_id = :3
and org_id = decode(:4,''Y'', org_id, ''N'',
decode(:5,null, org_id, :6), null,
decode(:7,null, org_id, :8))
and currency = :9
and largest_inv_cust_trx_id is not null
and as_of_date >= ADD_MONTHS(sysdate,(-:10)) )
Where high_amount = 1';
l_high_credit_site_sql VARCHAR2(4000) :='select op_bal_high_watermark_date,
op_bal_high_watermark
from ( select as_of_date op_bal_high_watermark_date,
gl_currency_api.convert_amount(currency,
:1,sysdate,
:2,
op_bal_high_watermark)op_bal_high_watermark,
RANK() OVER (PARTITION BY cust_account_id, site_use_id,currency
ORDER BY op_bal_high_watermark desc,
largest_inv_cust_trx_id desc) high_amount
FROM AR_TRX_SUMMARY
where cust_account_id = :3
and site_use_id = :4
and currency = :5
and largest_inv_cust_trx_id is not null
and as_of_date >= ADD_MONTHS(sysdate,(-:6)) )
Where high_amount = 1';
This code now selects the converted amount and date
(max) at one time, then substr them out to individual
fields for the datapoint(s). Incidentally, the original
logic returned the raw amount (in AR_TRX_SUMMARY currency)
rather than the converted amount -- I considered this a bug
and now return the converted amount (to CF currency) */
SELECT max(ltrim(
to_char( gl_currency_api.convert_amount(currency,
p_limit_currency,sysdate,
p_exchange_rate_type,
op_bal_high_watermark),
'0999999999999999999D00')) || '~' ||
to_char(as_of_date, 'YYYYMMDD'))
INTO l_result
FROM ar_trx_summary
WHERE op_bal_high_watermark IS NOT NULL
AND as_of_date >= ADD_MONTHS(sysdate,(-p_period))
AND cust_account_id IN (
SELECT cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
FROM hz_hierarchy_nodes
WHERE parent_object_type = 'ORGANIZATION'
AND parent_table_name = 'HZ_PARTIES'
AND child_object_type = 'ORGANIZATION'
AND parent_id = p_party_id
AND effective_start_date <= sysdate
AND effective_end_date >= sysdate
AND hierarchy_type =
FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
AND g_source_name <> 'LNS'
UNION
SELECT p_party_id FROM DUAL
UNION
SELECT hz_party_id
FROM LNS_LOAN_PARTICIPANTS_V
WHERE loan_id = g_source_id
AND participant_type_code = 'COBORROWER'
AND g_source_name = 'LNS'
AND (end_date_active is null OR
(sysdate between start_date_active
and end_date_active))))
AND currency IN ( SELECT CURRENCY
FROM ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id)
= p_credit_request_id);
SELECT max(ltrim(
to_char( gl_currency_api.convert_amount(currency,
p_limit_currency,sysdate,
p_exchange_rate_type,
op_bal_high_watermark),
'0999999999999999999D00')) || '~' ||
to_char(as_of_date, 'YYYYMMDD'))
INTO l_result
FROM ar_trx_summary
WHERE op_bal_high_watermark IS NOT NULL
AND as_of_date >= ADD_MONTHS(sysdate,(-p_period))
AND cust_account_id = p_cust_account_id
AND site_use_id = DECODE(p_analysis_level, 'S', p_site_use_id,
site_use_id)
AND org_id = decode(p_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
AND currency IN ( SELECT CURRENCY
FROM ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id)
= p_credit_request_id);
SELECT last_payment_amount, last_payment_date,
last_payment_number, currency
INTO l_last_payment_amount, l_last_payment_date,
l_last_payment_number, l_last_payment_currency
FROM AR_TRX_BAL_SUMMARY
WHERE cust_account_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type = FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
union select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id)
= p_credit_request_id)
and last_payment_number IS NOT NULL
and last_payment_date IS NOT NULL
and last_payment_date = ( select max(last_payment_date) from
ar_trx_bal_summary
where cust_account_id in
(select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type = FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
union select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) =
p_credit_request_id)
and last_payment_date is not null
and last_payment_number is not null)
and rownum = 1;
SELECT last_payment_amount, last_payment_date,
last_payment_number, currency
INTO l_last_payment_amount, l_last_payment_date,
l_last_payment_number, l_last_payment_currency
FROM AR_TRX_BAL_SUMMARY
where cust_account_id = p_cust_account_id
and last_payment_date IS NOT NULL
and last_payment_number IS NOT NULL
and org_id = decode(p_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, p_org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
and currency in ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
and last_payment_date = ( select max(last_payment_date) from
ar_trx_bal_summary
where cust_account_id = p_cust_account_id
and last_payment_date IS NOT NULL
and last_payment_number IS NOT NULL
and currency in ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) =
p_credit_request_id))
and rownum = 1;
SELECT last_payment_amount, last_payment_date,
last_payment_number, currency
INTO l_last_payment_amount, l_last_payment_date,
l_last_payment_number, l_last_payment_currency
FROM AR_TRX_BAL_SUMMARY
where cust_account_id = p_cust_account_id
and site_use_id = p_site_use_id
and last_payment_date IS NOT NULL
and last_payment_number IS NOT NULL
and currency in ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
and last_payment_date = ( select max(last_payment_date) from
ar_trx_bal_summary
where cust_account_id = p_cust_account_id
and site_use_id = p_site_use_id
and last_payment_date IS NOT NULL
and last_payment_number IS NOT NULL
and currency in ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) =
p_credit_request_id))
and rownum = 1;
p_errormsg := 'Error While inserting Last payment info., SqlError: '||sqlerrm;
SELECT max(correspondence_date), count(*)
INTO l_correspondence_date, l_dunning_count
FROM ar_correspondences_all
WHERE correspondence_type = 'DUNNING'
AND customer_id = p_cust_account_id
AND org_id = decode(p_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
AND correspondence_date >= ADD_MONTHS(sysdate,(-p_period));
SELECT max(correspondence_date), count(*)
INTO l_correspondence_date, l_dunning_count
FROM ar_correspondences_all
WHERE correspondence_type = 'DUNNING'
AND customer_id = p_cust_account_id
AND site_use_id = p_site_use_id
AND correspondence_date >= ADD_MONTHS(sysdate,(-p_period));
SELECT max(correspondence_date), count(*)
INTO l_correspondence_date, l_dunning_count
FROM ar_correspondences_all
WHERE correspondence_type = 'DUNNING'
AND customer_id in (
select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type =
FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
union select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
AND correspondence_date >= ADD_MONTHS(sysdate,(-p_period));
SELECT data_point_value
FROM ar_cmgt_cf_dtls
WHERE case_folder_id = p_case_folder_id
AND data_point_id = 34; -- receivable balance
select application_id
into l_application_id
from fnd_application
where application_short_name = 'ONT' ;
SELECT credit_check_rule_id
INTO l_credit_check_rule_id
FROM ar_cmgt_credit_requests
WHERE credit_request_id = p_credit_request_id;
o revised initial select to fetch cust_acct_site_id
instead of site_use_id. This is used to correctly
join to the errant ar_cmgt_financial_data table.
o revised second select to only join to
ar_cmgt_financial_data once (was joining twice)
o Added index ar_cmgt_financial_data_n2 that contains following:
1) party_id
2) reporting_currency
3) cust_account_id
4) site_use_id
The reason the index is in this particular order is that party_id
and currency are guaranteed. cust and/or site are optional and
may be provided as cust or cust + site.
*/
PROCEDURE GetFinancialData (
p_credit_request_id IN NUMBER,
p_case_folder_id IN NUMBER,
p_mode IN VARCHAR2,
p_resultout OUT NOCOPY VARCHAR2,
p_errmsg OUT NOCOPY VARCHAR2) IS
l_reporting_currency ar_cmgt_financial_data.reporting_currency%type;
SELECT cmcf.party_id,
cmcf.cust_account_id,
nvl(hzs.cust_acct_site_id,cmcf.site_use_id),
cmcf.limit_currency
INTO l_party_id, l_cust_account_id, l_site_use_id, l_limit_currency
FROM ar_cmgt_case_folders cmcf,
hz_cust_site_uses_all hzs
WHERE cmcf.case_folder_id = p_case_folder_id
AND cmcf.site_use_id = hzs.site_use_id (+);
SELECT to_number(
substr(
max(to_char(curr_fin_st_date, 'YYYYMMDD') ||
ltrim(to_char(financial_data_id,
'0999999999999999999999')))
,9))
INTO l_financial_data_id
FROM ar_cmgt_financial_data
WHERE reporting_currency = l_limit_currency
AND party_id = l_party_id
AND cust_account_id = l_cust_account_id
AND site_use_id = l_site_use_id;
SELECT reporting_currency,
monetary_unit,
curr_fin_st_date,
reporting_period,
cash,
net_receivables,
inventories,
other_cur_assets,
total_cur_assets,
net_fixed_assets,
other_non_cur_assets,
total_assets,
accounts_payable,
short_term_debt,
other_cur_liabilities,
total_cur_liabilities,
long_term_debt,
other_non_cur_liabilities,
total_liabilities,
stockholder_equity,
total_liabilities_equity,
revenue,
cost_of_goods_sold,
sga_expenses,
operating_income,
operating_margin,
non_operating_income,
non_operating_expenses,
pre_tax_net_income,
income_taxes,
net_income,
earnings_per_share
INTO
l_reporting_currency,
l_monetary_unit ,
l_curr_fin_st_date ,
l_reporting_period ,
l_cash ,
l_net_receivables ,
l_inventories ,
l_other_cur_assets ,
l_total_cur_assets,
l_net_fixed_assets,
l_other_non_cur_assets,
l_total_assets ,
l_accounts_payable ,
l_short_term_debt ,
l_other_cur_liabilities,
l_total_cur_liabilities ,
l_long_term_debt ,
l_other_non_cur_liabilities,
l_total_liabilities ,
l_stockholder_equity ,
l_total_liabilities_equity ,
l_revenue ,
l_cost_of_goods_sold ,
l_sga_expenses ,
l_operating_income ,
l_operating_margin ,
l_non_operating_income ,
l_non_operating_expenses ,
l_pre_tax_net_income ,
l_income_taxes ,
l_net_income ,
l_earnings_per_share
FROM ar_cmgt_financial_data
WHERE financial_data_id = l_financial_data_id;
SELECT trx_credit_limit, overall_credit_limit
FROM hz_cust_profile_amts
WHERE cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;
SELECT tax_name, year_established,
sic_code, -- industrial code
sic_code_type, -- industrial code type
url,
employees_total,
duns_number
FROM hz_parties
WHERE party_id = p_party_id;
SELECT bond_rating,
pending_litigations,
entity_type,
stock_exchange,
current_stock_price,
market_capitalization,
nvl(limit_amount, trx_amount) requested_amount,
market_cap_monetary_unit,
legal_entity_name
FROM ar_cmgt_credit_requests
WHERE credit_request_id = p_credit_request_id;
SELECT last_updated, -- last_credit_review_date
case_folder_number,
status,
check_list_id
FROM ar_cmgt_case_folders
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_site_use_id
AND type = 'DATA';
SELECT SUM(b.score) score
FROM ar_cmgt_case_folders a, ar_cmgt_cf_dtls b
WHERE a.party_id = p_party_id
AND a.cust_account_id = p_cust_account_id
AND a.site_use_id = p_site_use_id
AND a.type = 'DATA'
AND a.case_folder_id = b.case_folder_id;
p_data_point_value => c_case_folder_rec.last_updated,
p_mode => p_mode,
p_error_msg => l_errmsg,
p_resultout => l_resultout);
SELECT age.aging_bucket_id,age.aging_bucket_line_id
FROM AR_CMGT_SETUP_OPTIONS sys,
ar_aging_bucket_lines age
WHERE sys.aging_bucket_id = age.aging_bucket_id;
SELECT DECODE(party_id,-99,NULL,party_id),
DECODE(cust_account_id,-99,NULL,cust_account_id),
DECODE(site_use_id,-99,NULL,site_use_id),
limit_currency,
credit_request_id
INTO l_party_id,
l_cust_account_id,
l_customer_site_use_id,
l_currency_code,
l_credit_request_id
FROM ar_cmgt_case_folders
WHERE case_folder_id = p_case_folder_id;
SELECT age.bucket_name,
default_exchange_rate_type
INTO l_bucket_name,
l_exchange_rate_type
FROM AR_CMGT_SETUP_OPTIONS sys,
ar_aging_buckets age
WHERE sys.aging_bucket_id = age.aging_bucket_id;
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => g_data_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_0,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => p_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_0,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => g_data_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_1,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => p_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_1,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => g_data_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_2,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => p_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_2,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => g_data_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_3,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => p_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_3,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => g_data_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_4,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => p_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_4,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => g_data_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_5,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => p_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_5,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => g_data_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_6,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
ar_cmgt_controls.update_aging_dtls
(p_case_folder_id => p_case_folder_id,
p_aging_bucket_id => aging_rec.aging_bucket_id,
p_aging_bucket_line_id => aging_rec.aging_bucket_line_id,
p_amount => l_bucket_amount_6,
p_error_msg => p_error_msg,
p_resultout => p_resultout);
UPDATE ar_cmgt_cf_dnb_dtls
SET source_key = p_source_key,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id
AND source_table_name = p_source_table_name
AND nvl(source_key,'X') = nvl(p_source_key,'X')
AND nvl(source_key_type,'X') = nvl(p_source_key_type,'X')
AND nvl(source_key_column_name,'X') = nvl(p_source_key_column_name,'X')
AND nvl(source_key_column_type_name,'X') = nvl(p_source_key_column_type,'X');
SELECT ar_cmgt_case_folders_s.nextval
INTO p_case_folder_id
FROM dual;
SELECT ar_cmgt_case_folder_number_s.nextval
INTO l_case_folder_number
FROM dual;
SELECT case_folder_id
INTO g_data_case_folder_id
FROM ar_cmgt_case_folders
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_cust_acct_site_id
-- AND limit_currency = p_limit_currency
AND type = 'DATA';
UPDATE ar_cmgt_case_folders
set case_folder_number = l_case_folder_number,
check_list_id = p_check_list_id,
score_model_id = p_score_model_id,
limit_currency = p_limit_currency,
exchange_rate_type = p_exchange_rate_type,
credit_classification = p_credit_classification,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_updated = sysdate,
credit_request_id = p_credit_request_id
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND site_use_id = p_cust_acct_site_id
AND type = 'DATA';
SELECT ar_cmgt_case_folders_s.nextval
INTO g_data_case_folder_id
FROM dual;
SELECT ESTIMATED_IND,
CONSOLIDATED_IND,
REPORT_START_DATE,
REPORT_END_DATE,
DATE_REPORT_ISSUED,
AUDIT_IND,
FORECAST_IND,
FISCAL_IND,
FINAL_IND,
SIGNED_BY_PRINCIPALS_IND,
RESTATED_IND,
UNBALANCED_IND,
QUALIFIED_IND,
OPENING_IND,
PROFORMA_IND,
TRIAL_BALANCE_IND
FROM hz_financial_reports
WHERE financial_report_id = p_financial_report_id;
SELECT data_element_id, source_table_name, source_column_name
FROM ar_cmgt_dnb_elements_vl
WHERE scorable_flag = 'Y';
SELECT control_yr,
incorp_year,
year_established,
employees_total,
total_payments,
maximum_credit_recommendation,
oob_ind,
TOTAL_EMP_EST_IND
TOTAL_EMPLOYEES_IND,
SIC_CODE,
RENT_OWN_IND,
REGISTRATION_TYPE,
LEGAL_STATUS,
HQ_BRANCH_IND,
BRANCH_FLAG,
LOCAL_ACTIVITY_CODE_TYPE,
LOCAL_ACTIVITY_CODE,
SIC_CODE_TYPE,
GLOBAL_FAILURE_SCORE,
IMPORT_IND,
DUNS_NUMBER_C,
TOTAL_EMP_EST_IND,
PARENT_SUB_IND,
FAILURE_SCORE,
FAILURE_SCORE_COMMENTARY
FROM hz_organization_profiles
WHERE organization_profile_id = p_org_profile_id;
SELECT decode(PAYDEX_SCORE,'UN', null, paydex_score ) paydex_score,
decode(paydex_three_months_ago, 'UN', null, paydex_three_months_ago ) paydex_three_months_ago,
AVG_HIGH_CREDIT,
HIGH_CREDIT,
CREDIT_SCORE_NATL_PERCENTILE,
CREDIT_SCORE_INCD_DEFAULT,
CREDIT_SCORE_AGE,
FAILURE_SCORE_CLASS,
FAILURE_SCORE_NATNL_PERCENTILE,
FAILURE_SCORE_INCD_DEFAULT,
FAILURE_SCORE_AGE,
LOW_RNG_DELQ_SCR,
HIGH_RNG_DELQ_SCR,
DELQ_PMT_RNG_PRCNT,
DELQ_PMT_PCTG_FOR_ALL_FIRMS,
NUM_TRADE_EXPERIENCES,
DEBARMENTS_COUNT,
BANKRUPTCY_IND,
DEBARMENT_IND,
BUSINESS_DISCONTINUED,
NUM_SPCL_EVENT,
MAXIMUM_CREDIT_CURRENCY_CODE,
CREDIT_SCORE,
CREDIT_SCORE_CLASS,
CREDIT_SCORE_OVERRIDE_CODE,
PRNT_BKCY_CHAPTER_CONV,
NUM_PRNT_BKCY_CONVS,
PRNT_BKCY_FILG_CHAPTER,
PRNT_BKCY_FILG_TYPE,
NUM_PRNT_BKCY_FILING,
NO_TRADE_IND,
JUDGEMENT_IND,
LIEN_IND,
SUIT_IND,
PAYDEX_INDUSTRY_DAYS,
FINCL_LGL_EVENT_IND,
DISASTER_IND,
CRIMINAL_PROCEEDING_IND,
FINCL_EMBT_IND,
PAYDEX_NORM,
RATING,
SECURED_FLNG_IND,
CLAIMS_IND,
SUIT_JUDGE_IND,
DET_HISTORY_IND,
OTHER_SPEC_EVNT_IND,
OPRG_SPEC_EVNT_IND,
CREDIT_SCORE_COMMENTARY
FROM HZ_CREDIT_RATINGS
WHERE credit_rating_id = p_credit_rating_id;
SELECT FINANCIAL_number, financial_number_name
FROM hz_financial_numbers
WHERE financial_report_id in ( SELECT source_key
from ar_cmgt_cf_dnb_dtls
WHERE source_table_name = 'HZ_FINANCIAL_REPORTS'
and case_folder_id = p_case_folder_id);
SELECT COUNTRY
FROM hz_locations
where location_id = p_location_id;
SELECT rel.relationship_id, rel.relationship_code
FROM hz_relationships rel
WHERE rel.object_id = p_party_id
AND rel.relationship_code in ('HEADQUARTERS_OF','PARENT_OF',
'DOMESTIC_ULTIMATE_OF','GLOBAL_ULTIMATE_OF')
AND rel.actual_content_source = 'DNB'
AND rel.start_date <= sysdate
AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES';
SELECT contact_point_id, phone_line_type
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
AND contact_point_type = 'PHONE'
AND actual_content_source = 'DNB'
AND phone_line_type in ('FAX','GEN')
AND status = 'A';
SELECT data_point_id
FROM ar_cmgt_data_points_vl
WHERE data_point_category = 'DNB';
SELECT hq_branch_ind
INTO l_hq_branch_ind
FROM hz_parties
WHERE party_id = p_party_id;
SELECT ORGANIZATION_PROFILE_ID
INTO l_organization_profile_id
FROM HZ_ORGANIZATION_PROFILES
WHERE party_id = p_party_id
AND effective_end_date IS NULL
AND ACTUAL_CONTENT_SOURCE = 'DNB';
select credit_check_rule_id,
source_name
from ar_cmgt_credit_requests
where credit_request_id = p_cr_req_id;
select conversion_type
from oe_credit_check_rules
where credit_check_rule_id = p_cr_chk_rule_id;
SELECT default_exchange_rate_type
FROM ar_cmgt_setup_options;
SELECT '4'||'^'||fnd_number.number_to_canonical(SUM(avg_pmt_days)) avg_pmt_days,
'5'||'^'||fnd_number.number_to_canonical(SUM(weight_avg_days_late)) weight_avg_days_late,
'8'||'^'||fnd_number.number_to_canonical(SUM(days_credit_granted)) days_credit_granted,
'13'||'^'||fnd_number.number_to_canonical(SUM(nsf_payment_count)) nsf_payment_count,
'14'||'^'||fnd_number.number_to_canonical(SUM(nsf_payment_amount)) nsf_payment_amount,
'17'||'^'||fnd_number.number_to_canonical(SUM(credit_memo_value)) credit_memo_value,
'18'||'^'||fnd_number.number_to_canonical(SUM(credit_memo_count)) credit_memo_count,
'21'||'^'||fnd_number.number_to_canonical(SUM(per_inv_paid_promptly)) per_inv_paid_promptly,
'22'||'^'||fnd_number.number_to_canonical(SUM(per_inv_paid_late)) per_inv_paid_late,
'23'||'^'||fnd_number.number_to_canonical(SUM(per_inv_with_discount)) per_inv_with_discount,
'41'||'^'||fnd_number.number_to_canonical(SUM(inv_paid_amount)) inv_paid_amount,
'42'||'^'||fnd_number.number_to_canonical(SUM(inv_paid_count)) inv_paid_count,
'43'||'^'||fnd_number.number_to_canonical(SUM(earned_disc_value)) earned_disc_value,
'44'||'^'||fnd_number.number_to_canonical(SUM(earned_disc_count)) earned_disc_count,
'45'||'^'||fnd_number.number_to_canonical(SUM(unearned_disc_value)) unearned_disc_value,
'46'||'^'||fnd_number.number_to_canonical(SUM(unearned_disc_count)) unearned_disc_count,
'156'||'^'||fnd_number.number_to_canonical(SUM(total_cash_receipts_value)) total_cash_receipts_value,
'157'||'^'||fnd_number.number_to_canonical(SUM(total_cash_receipts_count)) total_cash_receipts_count,
'158'||'^'||fnd_number.number_to_canonical(SUM(total_invoices_value)) total_invoices_value,
'159'||'^'||fnd_number.number_to_canonical(SUM(total_invoices_count)) total_invoices_count,
'160'||'^'||fnd_number.number_to_canonical(SUM(total_bills_receivables_value)) total_bills_receivables_value,
'161'||'^'||fnd_number.number_to_canonical(SUM(total_bills_receivables_count)) total_bills_receivables_count,
'162'||'^'||fnd_number.number_to_canonical(SUM(total_debit_memos_value)) total_debit_memos_value,
'163'||'^'||fnd_number.number_to_canonical(SUM(total_debit_memos_count)) total_debit_memos_count,
'164'||'^'||fnd_number.number_to_canonical(SUM(total_chargeback_value)) total_chargeback_value,
'165'||'^'||fnd_number.number_to_canonical(SUM(total_chargeback_count)) total_chargeback_count,
'166'||'^'||fnd_number.number_to_canonical(SUM(total_adjustments_value)) total_adjustments_value,
'167'||'^'||fnd_number.number_to_canonical(SUM(total_adjustments_count)) total_adjustments_count,
'168'||'^'||fnd_number.number_to_canonical(SUM(total_deposits_value)) total_deposits_value,
'169'||'^'||fnd_number.number_to_canonical(SUM(total_deposits_count)) total_deposits_count
FROM ( SELECT
round(gl_currency_api.convert_amount(CURRENCY,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(INV_INST_PMT_DAYS_SUM)
/decode(SUM(SUM_APP_AMT),0,1,
SUM(SUM_APP_AMT)))),2) avg_pmt_days, -- weighted Average Payment Days
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(SUM_APP_AMT_DAYS_LATE)
/decode(SUM(SUM_APP_AMT),0,1,SUM(SUM_APP_AMT)))),2) weight_avg_days_late, -- Weighted Average Days Late
round(SUM(DAYS_CREDIT_GRANTED_SUM)/
decode(SUM(TOTAL_INVOICES_VALUE),0,1,
SUM(TOTAL_INVOICES_VALUE)),2) days_credit_granted, -- days credit granted
SUM(NSF_STOP_PAYMENT_COUNT) nsf_payment_count, -- NSF/Stop Payment Count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(NSF_STOP_PAYMENT_AMOUNT)),2) nsf_payment_amount, -- NSF/Stop Payment Amount
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CREDIT_MEMOS_VALUE)),2) credit_memo_value, -- Credit memos value
SUM(TOTAL_CREDIT_MEMOS_COUNT) credit_memo_count, -- Credit memos count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_tot_inv_inst_paid)-
SUM(count_of_inv_inst_paid_late))*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid))),2) per_inv_paid_promptly, -- % of invoice paid promptly
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_inv_inst_paid_late)*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid)))),2) per_inv_paid_late, -- % of invoice paid late
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_disc_inv_inst)*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid)))),2) per_inv_with_discount, -- % of invoice with discount taken
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(INV_PAID_AMOUNT)),2) inv_paid_amount, -- invoices paid amount
SUM(count_of_tot_inv_inst_paid) inv_paid_count, -- invoices paid count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_EARNED_DISC_VALUE)),2) earned_disc_value, -- Earned Dscount Value
SUM(TOTAL_EARNED_DISC_COUNT) earned_disc_count, -- Earned Dscount count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_UNEARNED_DISC_VALUE)),2) unearned_disc_value, -- UnEarned Dscount Value
SUM(TOTAL_UNEARNED_DISC_COUNT) unearned_disc_count, -- UnEarned Dscount count
(round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CASH_RECEIPTS_VALUE)),2) * -1) total_cash_receipts_value, -- see the comment above
SUM(TOTAL_CASH_RECEIPTS_COUNT) total_cash_receipts_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_INVOICES_VALUE)),2) total_invoices_value,
SUM(TOTAL_INVOICES_COUNT) total_invoices_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_BILLS_RECEIVABLES_VALUE)),2) total_bills_receivables_value,
SUM(TOTAL_BILLS_RECEIVABLES_COUNT) TOTAL_BILLS_RECEIVABLES_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_DEBIT_MEMOS_VALUE)),2) total_debit_memos_value,
SUM(TOTAL_DEBIT_MEMOS_COUNT) TOTAL_debit_memos_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CHARGEBACK_VALUE)),2) total_chargeback_value,
SUM(TOTAL_chargeback_COUNT) TOTAL_chargeback_count ,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_ADJUSTMENTS_VALUE)),2) total_adjustments_value,
SUM(TOTAL_adjustments_COUNT) TOTAL_adjustments_count ,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_DEPOSITS_VALUE)),2) total_deposits_value,
SUM(TOTAL_deposits_COUNT) TOTAL_deposits_count
FROM AR_TRX_SUMMARY
WHERE CUST_ACCOUNT_ID in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type = FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
union select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
/*( SELECT * FROM
TABLE(CAST(l_curr_array_list AS curr_array_type))) */
and as_of_date >= ADD_MONTHS(sysdate,(-l_period))
group by currency );
SELECT '4'||'^'||fnd_number.number_to_canonical(SUM(avg_pmt_days)) avg_pmt_days,
'5'||'^'||fnd_number.number_to_canonical(SUM(weight_avg_days_late)) weight_avg_days_late,
'8'||'^'||fnd_number.number_to_canonical(SUM(days_credit_granted)) days_credit_granted,
'13'||'^'||fnd_number.number_to_canonical(SUM(nsf_payment_count)) nsf_payment_count,
'14'||'^'||fnd_number.number_to_canonical(SUM(nsf_payment_amount)) nsf_payment_amount,
'17'||'^'||fnd_number.number_to_canonical(SUM(credit_memo_value)) credit_memo_value,
'18'||'^'||fnd_number.number_to_canonical(SUM(credit_memo_count)) credit_memo_count,
'21'||'^'||fnd_number.number_to_canonical(SUM(per_inv_paid_promptly)) per_inv_paid_promptly,
'22'||'^'||fnd_number.number_to_canonical(SUM(per_inv_paid_late)) per_inv_paid_late,
'23'||'^'||fnd_number.number_to_canonical(SUM(per_inv_with_discount)) per_inv_with_discount,
'41'||'^'||fnd_number.number_to_canonical(SUM(inv_paid_amount)) inv_paid_amount,
'42'||'^'||fnd_number.number_to_canonical(SUM(inv_paid_count)) inv_paid_count,
'43'||'^'||fnd_number.number_to_canonical(SUM(earned_disc_value)) earned_disc_value,
'44'||'^'||fnd_number.number_to_canonical(SUM(earned_disc_count)) earned_disc_count,
'45'||'^'||fnd_number.number_to_canonical(SUM(unearned_disc_value)) unearned_disc_value,
'46'||'^'||fnd_number.number_to_canonical(SUM(unearned_disc_count)) unearned_disc_count,
'156'||'^'||fnd_number.number_to_canonical(SUM(total_cash_receipts_value)) total_cash_receipts_value,
'157'||'^'||fnd_number.number_to_canonical(SUM(total_cash_receipts_count)) total_cash_receipts_count,
'158'||'^'||fnd_number.number_to_canonical(SUM(total_invoices_value)) total_invoices_value,
'159'||'^'||fnd_number.number_to_canonical(SUM(total_invoices_count)) total_invoices_count,
'160'||'^'||fnd_number.number_to_canonical(SUM(total_bills_receivables_value)) total_bills_receivables_value,
'161'||'^'||fnd_number.number_to_canonical(SUM(total_bills_receivables_count)) total_bills_receivables_count,
'162'||'^'||fnd_number.number_to_canonical(SUM(total_debit_memos_value)) total_debit_memos_value,
'163'||'^'||fnd_number.number_to_canonical(SUM(total_debit_memos_count)) total_debit_memos_count,
'164'||'^'||fnd_number.number_to_canonical(SUM(total_chargeback_value)) total_chargeback_value,
'165'||'^'||fnd_number.number_to_canonical(SUM(total_chargeback_count)) total_chargeback_count,
'166'||'^'||fnd_number.number_to_canonical(SUM(total_adjustments_value)) total_adjustments_value,
'167'||'^'||fnd_number.number_to_canonical(SUM(total_adjustments_count)) total_adjustments_count,
'168'||'^'||fnd_number.number_to_canonical(SUM(total_deposits_value)) total_deposits_value,
'169'||'^'||fnd_number.number_to_canonical(SUM(total_deposits_count)) total_deposits_count
FROM ( SELECT
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(INV_INST_PMT_DAYS_SUM)
/decode(SUM(SUM_APP_AMT),0,1,
SUM(SUM_APP_AMT)))),2) avg_pmt_days, -- weighted Average Payment Days
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(SUM_APP_AMT_DAYS_LATE)
/decode(SUM(SUM_APP_AMT),0,1,SUM(SUM_APP_AMT)))),2) weight_avg_days_late, -- Weighted Average Days Late
round(SUM(DAYS_CREDIT_GRANTED_SUM)/
decode(SUM(TOTAL_INVOICES_VALUE),0,1,
SUM(TOTAL_INVOICES_VALUE)),2) days_credit_granted, -- days credit granted
SUM(NSF_STOP_PAYMENT_COUNT) nsf_payment_count, -- NSF/Stop Payment Count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(NSF_STOP_PAYMENT_AMOUNT)),2) nsf_payment_amount, -- NSF/Stop Payment Amount
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CREDIT_MEMOS_VALUE)),2) credit_memo_value, -- Credit memos value
SUM(TOTAL_CREDIT_MEMOS_COUNT) credit_memo_count, -- Credit memos count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_tot_inv_inst_paid)-
SUM(count_of_inv_inst_paid_late))*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid))),2) per_inv_paid_promptly, -- % of invoice paid promptly
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_inv_inst_paid_late)*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid)))),2) per_inv_paid_late, -- % of invoice paid late
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_disc_inv_inst)*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid)))),2) per_inv_with_discount, -- % of invoice with discount taken
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(INV_PAID_AMOUNT)),2) inv_paid_amount, -- invoices paid amount
SUM(count_of_tot_inv_inst_paid) inv_paid_count, -- invoices paid count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_EARNED_DISC_VALUE)),2) earned_disc_value, -- Earned Dscount Value
SUM(TOTAL_EARNED_DISC_COUNT) earned_disc_count, -- Earned Dscount count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_UNEARNED_DISC_VALUE)),2) unearned_disc_value, -- UnEarned Dscount Value
SUM(TOTAL_UNEARNED_DISC_COUNT) unearned_disc_count, -- UnEarned Dscount count
(round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CASH_RECEIPTS_VALUE)),2) * -1) total_cash_receipts_value, -- see the comments above
SUM(TOTAL_CASH_RECEIPTS_COUNT) total_cash_receipts_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_INVOICES_VALUE)),2) total_invoices_value,
SUM(TOTAL_INVOICES_COUNT) total_invoices_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_BILLS_RECEIVABLES_VALUE)),2) total_bills_receivables_value,
SUM(TOTAL_BILLS_RECEIVABLES_COUNT) TOTAL_BILLS_RECEIVABLES_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_DEBIT_MEMOS_VALUE)),2) total_debit_memos_value,
SUM(TOTAL_DEBIT_MEMOS_COUNT) TOTAL_debit_memos_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CHARGEBACK_VALUE)),2) total_chargeback_value,
SUM(TOTAL_chargeback_COUNT) TOTAL_chargeback_count ,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_ADJUSTMENTS_VALUE)),2) total_adjustments_value,
SUM(TOTAL_adjustments_COUNT) TOTAL_adjustments_count ,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_DEPOSITS_VALUE)),2) total_deposits_value,
SUM(TOTAL_deposits_COUNT) TOTAL_deposits_count
FROM AR_TRX_SUMMARY
WHERE org_id = decode(l_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
and CUST_ACCOUNT_ID = p_cust_account_id
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
and as_of_date >= ADD_MONTHS(sysdate,(-l_period))
group by currency );
SELECT '4'||'^'||fnd_number.number_to_canonical(SUM(avg_pmt_days)) avg_pmt_days,
'5'||'^'||fnd_number.number_to_canonical(SUM(weight_avg_days_late)) weight_avg_days_late,
'8'||'^'||fnd_number.number_to_canonical(SUM(days_credit_granted)) days_credit_granted,
'13'||'^'||fnd_number.number_to_canonical(SUM(nsf_payment_count)) nsf_payment_count,
'14'||'^'||fnd_number.number_to_canonical(SUM(nsf_payment_amount)) nsf_payment_amount,
'17'||'^'||fnd_number.number_to_canonical(SUM(credit_memo_value)) credit_memo_value,
'18'||'^'||fnd_number.number_to_canonical(SUM(credit_memo_count)) credit_memo_count,
'21'||'^'||fnd_number.number_to_canonical(SUM(per_inv_paid_promptly)) per_inv_paid_promptly,
'22'||'^'||fnd_number.number_to_canonical(SUM(per_inv_paid_late)) per_inv_paid_late,
'23'||'^'||fnd_number.number_to_canonical(SUM(per_inv_with_discount)) per_inv_with_discount,
'41'||'^'||fnd_number.number_to_canonical(SUM(inv_paid_amount)) inv_paid_amount,
'42'||'^'||fnd_number.number_to_canonical(SUM(inv_paid_count)) inv_paid_count,
'43'||'^'||fnd_number.number_to_canonical(SUM(earned_disc_value)) earned_disc_value,
'44'||'^'||fnd_number.number_to_canonical(SUM(earned_disc_count)) earned_disc_count,
'45'||'^'||fnd_number.number_to_canonical(SUM(unearned_disc_value)) unearned_disc_value,
'46'||'^'||fnd_number.number_to_canonical(SUM(unearned_disc_count)) unearned_disc_count,
'156'||'^'||fnd_number.number_to_canonical(SUM(total_cash_receipts_value)) total_cash_receipts_value,
'157'||'^'||fnd_number.number_to_canonical(SUM(total_cash_receipts_count)) total_cash_receipts_count,
'158'||'^'||fnd_number.number_to_canonical(SUM(total_invoices_value)) total_invoices_value,
'159'||'^'||fnd_number.number_to_canonical(SUM(total_invoices_count)) total_invoices_count,
'160'||'^'||fnd_number.number_to_canonical(SUM(total_bills_receivables_value)) total_bills_receivables_value,
'161'||'^'||fnd_number.number_to_canonical(SUM(total_bills_receivables_count)) total_bills_receivables_count,
'162'||'^'||fnd_number.number_to_canonical(SUM(total_debit_memos_value)) total_debit_memos_value,
'163'||'^'||fnd_number.number_to_canonical(SUM(total_debit_memos_count)) total_debit_memos_count,
'164'||'^'||fnd_number.number_to_canonical(SUM(total_chargeback_value)) total_chargeback_value,
'165'||'^'||fnd_number.number_to_canonical(SUM(total_chargeback_count)) total_chargeback_count,
'166'||'^'||fnd_number.number_to_canonical(SUM(total_adjustments_value)) total_adjustments_value,
'167'||'^'||fnd_number.number_to_canonical(SUM(total_adjustments_count)) total_adjustments_count,
'168'||'^'||fnd_number.number_to_canonical(SUM(total_deposits_value)) total_deposits_value,
'169'||'^'||fnd_number.number_to_canonical(SUM(total_deposits_count)) total_deposits_count
FROM ( SELECT
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(INV_INST_PMT_DAYS_SUM)
/decode(SUM(SUM_APP_AMT),0,1,
SUM(SUM_APP_AMT)))),2) avg_pmt_days, -- weighted Average Payment Days
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(SUM_APP_AMT_DAYS_LATE)
/decode(SUM(SUM_APP_AMT),0,1,SUM(SUM_APP_AMT)))),2) weight_avg_days_late, -- Weighted Average Days Late
round(SUM(DAYS_CREDIT_GRANTED_SUM)/
decode(SUM(TOTAL_INVOICES_VALUE),0,1,
SUM(TOTAL_INVOICES_VALUE)),2) days_credit_granted, -- days credit granted
SUM(NSF_STOP_PAYMENT_COUNT) nsf_payment_count, -- NSF/Stop Payment Count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(NSF_STOP_PAYMENT_AMOUNT)),2) nsf_payment_amount, -- NSF/Stop Payment Amount
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CREDIT_MEMOS_VALUE)),2) credit_memo_value, -- Credit memos value
SUM(TOTAL_CREDIT_MEMOS_COUNT) credit_memo_count, -- Credit memos count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_tot_inv_inst_paid)-
SUM(count_of_inv_inst_paid_late))*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid))),2) per_inv_paid_promptly, -- % of invoice paid promptly
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_inv_inst_paid_late)*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid)))),2) per_inv_paid_late, -- % of invoice paid late
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(count_of_disc_inv_inst)*100)/
decode(SUM(count_of_tot_inv_inst_paid),0,1,
SUM(count_of_tot_inv_inst_paid)))),2) per_inv_with_discount, -- % of invoice with discount taken
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(INV_PAID_AMOUNT)),2) inv_paid_amount, -- invoices paid amount
SUM(count_of_tot_inv_inst_paid) inv_paid_count, -- invoices paid count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_EARNED_DISC_VALUE)),2) earned_disc_value, -- Earned Dscount Value
SUM(TOTAL_EARNED_DISC_COUNT) earned_disc_count, -- Earned Dscount count
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_UNEARNED_DISC_VALUE)),2) unearned_disc_value, -- UnEarned Dscount Value
SUM(TOTAL_UNEARNED_DISC_COUNT) unearned_disc_count, -- UnEarned Dscount count
(round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CASH_RECEIPTS_VALUE)),2) * -1) total_cash_receipts_value, -- see the comments above
SUM(TOTAL_CASH_RECEIPTS_COUNT) total_cash_receipts_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_INVOICES_VALUE)),2) total_invoices_value,
SUM(TOTAL_INVOICES_COUNT) total_invoices_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_BILLS_RECEIVABLES_VALUE)),2) total_bills_receivables_value,
SUM(TOTAL_BILLS_RECEIVABLES_COUNT) TOTAL_BILLS_RECEIVABLES_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_DEBIT_MEMOS_VALUE)),2) total_debit_memos_value,
SUM(TOTAL_DEBIT_MEMOS_COUNT) TOTAL_debit_memos_count,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_CHARGEBACK_VALUE)),2) total_chargeback_value,
SUM(TOTAL_chargeback_COUNT) TOTAL_chargeback_count ,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_ADJUSTMENTS_VALUE)),2) total_adjustments_value,
SUM(TOTAL_adjustments_COUNT) TOTAL_adjustments_count ,
round(gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(TOTAL_DEPOSITS_VALUE)),2) total_deposits_value,
SUM(TOTAL_deposits_COUNT) TOTAL_deposits_count
FROM AR_TRX_SUMMARY
WHERE CUST_ACCOUNT_ID = p_cust_account_id
and CURRENCY IN ( SELECT a.CURRENCY FROM
ar_cmgt_curr_usage_gt a
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
and as_of_date >= ADD_MONTHS(sysdate,(-l_period))
and site_use_id = p_cust_acct_site_id
group by currency );
SELECT '34'||'^'||fnd_number.number_to_canonical(SUM(current_receivable_balance)) current_receivable_balance,
'9'||'^'|| fnd_number.number_to_canonical(SUM(unapplied_cash_amount)) unapplied_cash_amount, -- unapplied case amount
'10'||'^'||fnd_number.number_to_canonical(SUM(unapplied_cash_count)) unapplied_cash_count, -- unapplied cash count
'48'||'^'||fnd_number.number_to_canonical(SUM(past_due_inv_value)) past_due_inv_value,
'49'||'^'||fnd_number.number_to_canonical(SUM(past_due_inv_inst_count)) past_due_inv_inst_count,
'50'||'^'||fnd_number.number_to_canonical(SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
'51'||'^'||fnd_number.number_to_canonical(SUM(disputed_inv_count)) disputed_inv_count,
'56'||'^'||fnd_number.number_to_canonical(SUM(pending_adj_value)) pending_adj_value,
'58'||'^'||fnd_number.number_to_canonical(SUM(total_receipts_at_risk_value)) total_receipts_at_risk_value,
'170'||'^'||fnd_number.number_to_canonical(SUM(op_invoices_value)) op_invoices_value,
'171'||'^'||fnd_number.number_to_canonical(SUM(op_invoices_count)) op_invoices_count,
'172'||'^'||fnd_number.number_to_canonical(SUM(op_debit_memos_value)) op_debit_memos_value,
'173'||'^'||fnd_number.number_to_canonical(SUM(op_debit_memos_count)) op_debit_memos_count,
'174'||'^'||fnd_number.number_to_canonical(SUM(op_deposits_value)) op_deposits_value,
'175'||'^'||fnd_number.number_to_canonical(SUM(op_deposits_count)) op_deposits_count,
'176'||'^'||fnd_number.number_to_canonical(SUM(op_bills_receivables_value)) op_bills_receivables_value,
'177'||'^'||fnd_number.number_to_canonical(SUM(op_bills_receivables_count)) op_bills_receivables_count,
'178'||'^'||fnd_number.number_to_canonical(SUM(op_chargeback_value)) op_chargeback_value,
'179'||'^'||fnd_number.number_to_canonical(SUM(op_chargeback_count)) op_chargeback_count,
'180'||'^'||fnd_number.number_to_canonical(SUM(op_credit_memos_value)) op_credit_memos_value,
'181'||'^'||fnd_number.number_to_canonical(SUM(op_credit_memos_count)) op_credit_memos_count,
'209'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_value)) current_invoice_value,
'210'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_count)) current_invoice_count
FROM (
SELECT
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
nvl(UNRESOLVED_CASH_VALUE,0) ))) current_receivable_balance, -- Current Receivables Balance (Opening balance)
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(UNRESOLVED_CASH_VALUE)) unapplied_cash_amount, -- unapplied case amount
SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(past_due_inv_value)) past_due_inv_value,
SUM(past_due_inv_inst_count) past_due_inv_inst_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
SUM(disputed_inv_count) disputed_inv_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(pending_adj_value)) pending_adj_value,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_invoices_value)) op_invoices_value,
SUM(op_invoices_count) op_invoices_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_debit_memos_value)) op_debit_memos_value,
SUM(op_debit_memos_count) op_debit_memos_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_deposits_value)) op_deposits_value,
SUM(op_deposits_count) op_deposits_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_bills_receivables_value)) op_bills_receivables_value,
SUM(op_bills_receivables_count) op_bills_receivables_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_chargeback_value)) op_chargeback_value,
SUM(op_chargeback_count) op_chargeback_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_credit_memos_value)) op_credit_memos_value,
SUM(op_credit_memos_count) op_credit_memos_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(nvl(op_invoices_value,0) - nvl(past_due_inv_value,0))) current_invoice_value,
SUM(nvl(op_invoices_count,0) - nvl(past_due_inv_inst_count,0)) current_invoice_count
FROM AR_TRX_BAL_SUMMARY
WHERE cust_account_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type = FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
union select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
group by currency);
SELECT '34'||'^'||fnd_number.number_to_canonical(SUM(current_receivable_balance)) current_receivable_balance,
'9'||'^'|| fnd_number.number_to_canonical(SUM(unapplied_cash_amount)) unapplied_cash_amount, -- unapplied case amount
'10'||'^'||fnd_number.number_to_canonical(SUM(unapplied_cash_count)) unapplied_cash_count, -- unapplied cash count
'48'||'^'||fnd_number.number_to_canonical(SUM(past_due_inv_value)) past_due_inv_value,
'49'||'^'||fnd_number.number_to_canonical(SUM(past_due_inv_inst_count)) past_due_inv_inst_count,
'50'||'^'||fnd_number.number_to_canonical(SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
'51'||'^'||fnd_number.number_to_canonical(SUM(disputed_inv_count)) disputed_inv_count,
'56'||'^'||fnd_number.number_to_canonical(SUM(pending_adj_value)) pending_adj_value,
'58'||'^'||fnd_number.number_to_canonical(SUM(total_receipts_at_risk_value)) total_receipts_at_risk_value,
'170'||'^'||fnd_number.number_to_canonical(SUM(op_invoices_value)) op_invoices_value,
'171'||'^'||fnd_number.number_to_canonical(SUM(op_invoices_count)) op_invoices_count,
'172'||'^'||fnd_number.number_to_canonical(SUM(op_debit_memos_value)) op_debit_memos_value,
'173'||'^'||fnd_number.number_to_canonical(SUM(op_debit_memos_count)) op_debit_memos_count,
'174'||'^'||fnd_number.number_to_canonical(SUM(op_deposits_value)) op_deposits_value,
'175'||'^'||fnd_number.number_to_canonical(SUM(op_deposits_count)) op_deposits_count,
'176'||'^'||fnd_number.number_to_canonical(SUM(op_bills_receivables_value)) op_bills_receivables_value,
'177'||'^'||fnd_number.number_to_canonical(SUM(op_bills_receivables_count)) op_bills_receivables_count,
'178'||'^'||fnd_number.number_to_canonical(SUM(op_chargeback_value)) op_chargeback_value,
'179'||'^'||fnd_number.number_to_canonical(SUM(op_chargeback_count)) op_chargeback_count,
'180'||'^'||fnd_number.number_to_canonical(SUM(op_credit_memos_value)) op_credit_memos_value,
'181'||'^'||fnd_number.number_to_canonical(SUM(op_credit_memos_count)) op_credit_memos_count,
'209'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_value)) current_invoice_value,
'210'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_count)) current_invoice_count
FROM (
SELECT
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
nvl(UNRESOLVED_CASH_VALUE,0) ))) current_receivable_balance, -- Current Receivables Balance (Opening balance)
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(UNRESOLVED_CASH_VALUE)) unapplied_cash_amount, -- unapplied case amount
SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(past_due_inv_value)) past_due_inv_value,
SUM(past_due_inv_inst_count) past_due_inv_inst_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
SUM(disputed_inv_count) disputed_inv_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(pending_adj_value)) pending_adj_value,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_invoices_value)) op_invoices_value,
SUM(op_invoices_count) op_invoices_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_debit_memos_value)) op_debit_memos_value,
SUM(op_debit_memos_count) op_debit_memos_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_deposits_value)) op_deposits_value,
SUM(op_deposits_count) op_deposits_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_bills_receivables_value)) op_bills_receivables_value,
SUM(op_bills_receivables_count) op_bills_receivables_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_chargeback_value)) op_chargeback_value,
SUM(op_chargeback_count) op_chargeback_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_credit_memos_value)) op_credit_memos_value,
SUM(op_credit_memos_count) op_credit_memos_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(nvl(op_invoices_value,0) - nvl(past_due_inv_value,0))) current_invoice_value,
SUM(nvl(op_invoices_count,0) - nvl(past_due_inv_inst_count,0)) current_invoice_count
FROM AR_TRX_BAL_SUMMARY
WHERE org_id = decode(l_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
and cust_account_id = p_cust_account_id
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
/* ( SELECT * FROM
TABLE(CAST(l_curr_array_list AS curr_array_type))) */
--and site_use_id = decode(l_analysis_level,'S',p_cust_acct_site_id,site_use_id)
group by currency);
SELECT '34'||'^'||fnd_number.number_to_canonical(SUM(current_receivable_balance)) current_receivable_balance,
'9'||'^'|| fnd_number.number_to_canonical(SUM(unapplied_cash_amount)) unapplied_cash_amount, -- unapplied case amount
'10'||'^'||fnd_number.number_to_canonical(SUM(unapplied_cash_count)) unapplied_cash_count, -- unapplied cash count
'48'||'^'||fnd_number.number_to_canonical(SUM(past_due_inv_value)) past_due_inv_value,
'49'||'^'||fnd_number.number_to_canonical(SUM(past_due_inv_inst_count)) past_due_inv_inst_count,
'50'||'^'||fnd_number.number_to_canonical(SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
'51'||'^'||fnd_number.number_to_canonical(SUM(disputed_inv_count)) disputed_inv_count,
'56'||'^'||fnd_number.number_to_canonical(SUM(pending_adj_value)) pending_adj_value,
'58'||'^'||fnd_number.number_to_canonical(SUM(total_receipts_at_risk_value)) total_receipts_at_risk_value,
'170'||'^'||fnd_number.number_to_canonical(SUM(op_invoices_value)) op_invoices_value,
'171'||'^'||fnd_number.number_to_canonical(SUM(op_invoices_count)) op_invoices_count,
'172'||'^'||fnd_number.number_to_canonical(SUM(op_debit_memos_value)) op_debit_memos_value,
'173'||'^'||fnd_number.number_to_canonical(SUM(op_debit_memos_count)) op_debit_memos_count,
'174'||'^'||fnd_number.number_to_canonical(SUM(op_deposits_value)) op_deposits_value,
'175'||'^'||fnd_number.number_to_canonical(SUM(op_deposits_count)) op_deposits_count,
'176'||'^'||fnd_number.number_to_canonical(SUM(op_bills_receivables_value)) op_bills_receivables_value,
'177'||'^'||fnd_number.number_to_canonical(SUM(op_bills_receivables_count)) op_bills_receivables_count,
'178'||'^'||fnd_number.number_to_canonical(SUM(op_chargeback_value)) op_chargeback_value,
'179'||'^'||fnd_number.number_to_canonical(SUM(op_chargeback_count)) op_chargeback_count,
'180'||'^'||fnd_number.number_to_canonical(SUM(op_credit_memos_value)) op_credit_memos_value,
'181'||'^'||fnd_number.number_to_canonical(SUM(op_credit_memos_count)) op_credit_memos_count,
'209'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_value)) current_invoice_value,
'210'||'^'||fnd_number.number_to_canonical(SUM(current_invoice_count)) current_invoice_count
FROM (
SELECT
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM((nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
nvl(UNRESOLVED_CASH_VALUE,0) ))) current_receivable_balance, -- Current Receivables Balance (Opening balance)
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(UNRESOLVED_CASH_VALUE)) unapplied_cash_amount, -- unapplied case amount
SUM(UNRESOLVED_CASH_COUNT) unapplied_cash_count, -- unapplied cash count
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(past_due_inv_value)) past_due_inv_value,
SUM(past_due_inv_inst_count) past_due_inv_inst_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(inv_amt_in_dispute)) inv_amt_in_dispute,
SUM(disputed_inv_count) disputed_inv_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(pending_adj_value)) pending_adj_value,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(receipts_at_risk_value)) total_receipts_at_risk_value,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_invoices_value)) op_invoices_value,
SUM(op_invoices_count) op_invoices_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_debit_memos_value)) op_debit_memos_value,
SUM(op_debit_memos_count) op_debit_memos_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_deposits_value)) op_deposits_value,
SUM(op_deposits_count) op_deposits_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_bills_receivables_value)) op_bills_receivables_value,
SUM(op_bills_receivables_count) op_bills_receivables_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_chargeback_value)) op_chargeback_value,
SUM(op_chargeback_count) op_chargeback_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(op_credit_memos_value)) op_credit_memos_value,
SUM(op_credit_memos_count) op_credit_memos_count,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
SUM(nvl(op_invoices_value,0) - nvl(past_due_inv_value,0))) current_invoice_value,
SUM(nvl(op_invoices_count,0) - nvl(past_due_inv_inst_count,0)) current_invoice_count
FROM AR_TRX_BAL_SUMMARY
WHERE cust_account_id = p_cust_account_id
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
/* ( SELECT * FROM
TABLE(CAST(l_curr_array_list AS curr_array_type))) */
and site_use_id = p_cust_acct_site_id
group by currency);
SELECT SUM(dso) dso,
SUM(delinquent_dso) delinquent_dso
FROM (
SELECT gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
)*l_certified_dso_days)) dso,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
- nvl(BEST_CURRENT_RECEIVABLES,0))*l_certified_dso_days)) delinquent_dso
FROM ar_trx_bal_summary
WHERE cust_account_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type = FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
union select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
group by currency);
SELECT SUM(dso) dso,
SUM(delinquent_dso) delinquent_dso
FROM (
SELECT gl_currency_api.convert_amount (currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
)*l_certified_dso_days)) dso,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
- nvl(BEST_CURRENT_RECEIVABLES,0))*l_certified_dso_days)) delinquent_dso
FROM ar_trx_bal_summary
WHERE cust_account_id = p_cust_account_id
and org_id = decode(l_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
group by currency);
SELECT SUM(dso) dso,
SUM(delinquent_dso) delinquent_dso
FROM (
SELECT gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0) +
nvl(UNRESOLVED_CASH_VALUE,0) )*l_certified_dso_days)) dso,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(OP_INVOICES_VALUE,0) + nvl(OP_DEBIT_MEMOS_VALUE,0) +
nvl(OP_DEPOSITS_VALUE,0) + nvl(OP_BILLS_RECEIVABLES_VALUE,0) +
nvl(OP_CHARGEBACK_VALUE,0) + nvl(OP_CREDIT_MEMOS_VALUE,0)
- nvl(BEST_CURRENT_RECEIVABLES,0))*l_certified_dso_days)) delinquent_dso
FROM ar_trx_bal_summary
WHERE cust_account_id = p_cust_account_id
and site_use_id = p_cust_acct_site_id
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
group by currency);
SELECT SUM(dso) dso,
SUM(delinquent_dso) delinquent_dso
FROM (
SELECT gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
nvl(TOTAL_ADJUSTMENTS_VALUE,0))
)) dso,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
((SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
nvl(TOTAL_ADJUSTMENTS_VALUE,0)
)))) delinquent_dso
FROM ar_trx_summary
WHERE cust_account_id in (select cust_account_id
FROM hz_cust_accounts
WHERE party_id in
( SELECT child_id
from hz_hierarchy_nodes
where parent_object_type = 'ORGANIZATION'
and parent_table_name = 'HZ_PARTIES'
and child_object_type = 'ORGANIZATION'
and parent_id = p_party_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate
and hierarchy_type = FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE')
and g_source_name <> 'LNS'
union select p_party_id from dual
UNION
select hz_party_id
from LNS_LOAN_PARTICIPANTS_V
where loan_id = g_source_id
and participant_type_code = 'COBORROWER'
and g_source_name = 'LNS'
and (end_date_active is null OR
(sysdate between start_date_active and end_date_active)
)
))
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
and as_of_date >= (sysdate -l_certified_dso_days)
group by currency);
SELECT SUM(dso) dso,
SUM(delinquent_dso) delinquent_dso
FROM (
SELECT gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
nvl(TOTAL_ADJUSTMENTS_VALUE,0))
)) dso,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
nvl(TOTAL_ADJUSTMENTS_VALUE,0)
))) delinquent_dso
FROM ar_trx_summary
WHERE cust_account_id = p_cust_account_id
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
and org_id = decode(l_global_exposure_flag,'Y', org_id, 'N',
decode(p_org_id,null, org_id, p_org_id), null,
decode(p_org_id,null, org_id, p_org_id))
and as_of_date >= (sysdate -l_certified_dso_days)
group by currency);
SELECT SUM(dso) dso,
SUM(delinquent_dso) delinquent_dso
FROM (
SELECT gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
nvl(TOTAL_ADJUSTMENTS_VALUE,0))
)) dso,
gl_currency_api.convert_amount(currency,
l_limit_currency,sysdate,
l_exchange_rate_type,
(SUM(nvl(TOTAL_INVOICES_VALUE,0) + nvl(TOTAL_DEBIT_MEMOS_VALUE,0) +
nvl(TOTAL_DEPOSITS_VALUE,0) + nvl(TOTAL_BILLS_RECEIVABLES_VALUE,0) +
nvl(TOTAL_CHARGEBACK_VALUE,0) + nvl(TOTAL_CREDIT_MEMOS_VALUE,0) +
nvl(TOTAL_ADJUSTMENTS_VALUE,0)
))) delinquent_dso
FROM ar_trx_summary
WHERE cust_account_id = p_cust_account_id
and CURRENCY IN ( SELECT CURRENCY FROM
ar_cmgt_curr_usage_gt
WHERE nvl(credit_request_id,p_credit_request_id) = p_credit_request_id)
and site_use_id = p_cust_acct_site_id
and as_of_date >= (sysdate -l_certified_dso_days)
group by currency);
SELECT period, cer_dso_days
INTO l_period, l_certified_dso_days
FROM ar_cmgt_setup_options;
SELECT nvl(source_name, 'OCM'), nvl(source_column1, -99)
INTO g_source_name, g_source_id
FROM ar_cmgt_credit_requests
WHERE credit_request_id = p_credit_request_id;
INSERT INTO ar_cmgt_curr_usage_gt ( credit_request_id, currency) values
( p_credit_request_id, l_curr_tbl(i).usage_curr_code);
INSERT INTO ar_cmgt_curr_usage_gt(currency)
( select distinct currency from ar_trx_bal_summary);
update ar_cmgt_credit_requests
set limit_currency = l_limit_currency,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE credit_request_id = p_credit_request_id;
UPDATE ar_cmgt_case_folders
set last_updated = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id;
UPDATE ar_cmgt_cf_dtls
SET score = null,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id;
UPDATE ar_cmgt_case_folders
SET status = 'SAVED',
last_updated = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id
AND type = 'CASE';
UPDATE ar_cmgt_cf_dtls
set included_in_checklist = 'Y'
WHERE case_folder_id = p_case_folder_id
AND data_point_id in (
SELECT data_point_id
FROM ar_cmgt_check_list_dtls
WHERE check_list_id = p_check_list_id);