The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
INTO p_unpaid_inv,
p_ontime_inv,
p_late_inv
from ar_payment_schedules aps,
hz_cust_accounts hzca
where aps.customer_id = hzca.cust_account_id
and aps.class IN ('INV', 'DM', 'CB')
and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
and hzca.party_id IN
(SELECT p_filter_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_filter_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) );
SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
INTO p_unpaid_inv,
p_ontime_inv,
p_late_inv
from ar_payment_schedules aps,
hz_cust_accounts hzca
where aps.customer_id = hzca.cust_account_id
and aps.class IN ('INV', 'DM', 'CB')
and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
and hzca.party_id = p_filter_id ;
SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
INTO p_unpaid_inv,
p_ontime_inv,
p_late_inv
from ar_payment_schedules aps,
hz_cust_accounts hzca
where aps.customer_id = hzca.cust_account_id
and aps.class IN ('INV', 'DM', 'CB')
and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
and hzca.cust_account_id = p_filter_id ;
SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
INTO p_unpaid_inv,
p_ontime_inv,
p_late_inv
from ar_payment_schedules aps,
iex_delinquencies del
where aps.class IN ('INV', 'DM', 'CB')
and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
and del.payment_schedule_id = aps.payment_schedule_id
and del.delinquency_id = p_filter_id;
SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
INTO p_unpaid_inv,
p_ontime_inv,
p_late_inv
from ar_payment_schedules aps
where aps.class IN ('INV', 'DM', 'CB')
and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
and aps.customer_site_use_id = p_filter_id;
SELECT JIOV.short_description outcome,
jii.start_date_time s_date,
JRREV.resource_Name res_name,
JIRV.short_description result
FROM JTF_IH_INTERACTIONS JII,
JTF_RS_RESOURCE_EXTNS_TL JRREV,
JTF_IH_OUTCOMES_TL JIOV,
JTF_IH_RESULTS_TL JIRV
WHERE JII.resource_id = JRREV.resource_id
AND JRREV.LANGUAGE (+)= USERENV('LANG')
AND JIOV.Outcome_id = JII.outcome_id
AND JIOV.LANGUAGE (+)= USERENV('LANG')
AND JIRV.Result_id(+) = JII.result_id
AND JIRV.LANGUAGE (+)= USERENV('LANG')
AND JII.Party_Id = p_party_id
AND JII.start_date_time = (select Max(i.start_date_time)
from jtf_ih_interactions i
where i.party_id = jii.party_id) ;
SELECT DISTINCT JIOV.short_description outcome,
jii.start_date_time s_date,
JRREV.resource_Name res_name,
JIRV.short_description result
FROM JTF_IH_INTERACTIONS JII,
JTF_RS_RESOURCE_EXTNS_TL JRREV,
JTF_IH_OUTCOMES_TL JIOV,
JTF_IH_RESULTS_B JIRB,
JTF_IH_RESULTS_TL JIRV,
JTF_IH_ACTIVITIES JIA
WHERE JII.resource_id = JRREV.resource_id
AND JRREV.LANGUAGE (+)= USERENV('LANG')
AND jii.interaction_id = jia.interaction_id
AND JIOV.Outcome_id = JII.outcome_id
AND JIOV.LANGUAGE (+)= USERENV('LANG')
AND JIRB.Result_id(+) = JII.result_id
AND JIRV.Result_id(+) =JIRB.Result_id
AND JIRV.LANGUAGE (+)= USERENV('LANG')
AND jia.cust_account_id = cust_acct_id
AND JII.start_date_time =
(select Max(i.start_date_time)
from jtf_ih_interactions i,
jtf_ih_activities a
where a.cust_account_id = cust_acct_id
AND a.interaction_id = i.interaction_id);
SELECT JIOV.short_description outcome,
jii.start_date_time s_date,
JRREV.resource_Name res_name,
JIRV.short_description result
FROM JTF_IH_INTERACTIONS JII,
JTF_RS_RESOURCE_EXTNS_TL JRREV,
JTF_IH_OUTCOMES_TL JIOV,
JTF_IH_RESULTS_TL JIRV
WHERE JII.resource_id = JRREV.resource_id
AND JRREV.LANGUAGE (+)= USERENV('LANG')
AND JIOV.Outcome_id = JII.outcome_id
AND JIOV.LANGUAGE (+)= USERENV('LANG')
AND JIRV.Result_id(+) = JII.result_id
AND JIRV.LANGUAGE (+)= USERENV('LANG')
AND JII.Party_Id IN
(SELECT p_party_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_party_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) )
AND JII.start_date_time = (select Max(i.start_date_time)
from jtf_ih_interactions i
where i.party_id = jii.party_id) ;
SELECT coll.name,
arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
FROM hz_customer_profiles cust_prof, ar_collectors coll
WHERE cust_prof.party_id = p_party_id
AND coll.collector_id(+) = cust_prof.collector_id
AND cust_prof.cust_account_id = -1;
SELECT coll.name,
arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
FROM hz_customer_profiles cust_prof, ar_collectors coll
WHERE cust_prof.cust_account_id = p_cust_account_id
AND coll.collector_id(+) = cust_prof.collector_id
AND cust_prof.site_use_id IS NULL;
SELECT coll.name,
arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
FROM hz_customer_profiles cust_prof, ar_collectors coll
WHERE cust_prof.site_use_id = p_customer_site_use_id
AND coll.collector_id(+) = cust_prof.collector_id;
SELECT cust_account_id, customer_site_use_id
FROM iex_delinquencies
WHERE delinquency_id = p_delinquency_id;
SELECT cust_account_id
FROM hz_cust_site_uses site_use, hz_cust_acct_sites acct_site
WHERE site_use.site_use_id = p_customer_site_use_id
AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id;
SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
-- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
g_curr_rec.base_currency
FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
ar_cmgt_setup_options cm_opt
WHERE prof.party_id = p_party_id
AND prof.site_use_id IS NULL
AND prof.status = 'A'
-- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
-- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
-- AND prof_amt.cust_account_id = prof.cust_account_id
AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id(+)
AND prof_amt.cust_account_id(+) = prof.cust_account_id
-- End fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
AND prof_amt.site_use_id IS NULL
--Begin-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
AND prof.cust_account_id = -1;
SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
-- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
g_curr_rec.base_currency
FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
ar_cmgt_setup_options cm_opt
WHERE prof.cust_account_id = p_cust_account_id
AND prof.site_use_id IS NULL
AND prof.status = 'A'
-- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
-- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
-- AND prof_amt.cust_account_id = p_cust_account_id
AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id(+)
AND prof_amt.cust_account_id(+) = p_cust_account_id
-- End fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
AND prof_amt.site_use_id IS NULL;
SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
-- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
g_curr_rec.base_currency
FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt,
ar_cmgt_setup_options cm_opt
WHERE prof.site_use_id = p_customer_site_use_id
AND prof.status = 'A'
-- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
-- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
-- AND prof_amt.site_use_id = p_customer_site_use_id;
SELECT distinct sob.currency_code
INTO g_curr_rec.base_currency
FROM ar_system_parameters sysp,
gl_sets_of_books sob
WHERE sob.set_of_books_id = sysp.set_of_books_id;
SELECT TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
TRUNC(sysdate) pastYearTo
INTO g_curr_rec.past_year_from,
g_curr_rec.past_year_to
FROM dual;
SELECT COUNT(1),
SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
Into l_profile_rec.Total_Promises,
l_profile_rec.Broken_Promises,
l_profile_rec.Open_Promises
FROM IEX_PROMISE_DETAILS IPD,
HZ_CUST_ACCOUNTS HZCA,
IEX_DELINQUENCIES DEL -- Moac Change Added Delinquency
WHERE IPD.cust_account_id = HZCA.cust_Account_id
AND IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
AND HZCA.Party_id IN
(SELECT p_party_id FROM dual
UNION
SELECT ar.related_party_id
FROM ar_paying_relationships_v ar
WHERE ar.party_id = p_party_id
AND TRUNC(sysdate) BETWEEN
TRUNC(NVL(ar.effective_start_date,sysdate)) AND
TRUNC(NVL(ar.effective_end_date,sysdate)) );
SELECT COUNT(1),
SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
Into l_profile_rec.Total_Promises,
l_profile_rec.Broken_Promises,
l_profile_rec.Open_Promises
FROM IEX_PROMISE_DETAILS IPD,
HZ_CUST_ACCOUNTS HZCA,
IEX_DELINQUENCIES DEL
WHERE IPD.cust_account_id = HZCA.cust_Account_id
AND IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
AND HZCA.Party_id = p_party_id ;
SELECT COUNT(1),
SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
Into l_profile_rec.Total_Promises,
l_profile_rec.Broken_Promises,
l_profile_rec.Open_Promises
FROM IEX_PROMISE_DETAILS IPD,
HZ_CUST_ACCOUNTS HZCA,
IEX_DELINQUENCIES DEL -- Moac Change Added Delinquency
WHERE IPD.cust_account_id = HZCA.cust_Account_id
AND IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
AND HZCA.cust_account_id = p_cust_account_id ;
SELECT COUNT(1),
SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
Into l_profile_rec.Total_Promises,
l_profile_rec.Broken_Promises,
l_profile_rec.Open_Promises
FROM IEX_PROMISE_DETAILS IPD
WHERE trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
AND IPD.delinquency_id = p_delinquency_id;
SELECT COUNT(1),
SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
Into l_profile_rec.Total_Promises,
l_profile_rec.Broken_Promises,
l_profile_rec.Open_Promises
FROM IEX_PROMISE_DETAILS IPD,
IEX_DELINQUENCIES DEL
WHERE trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
AND IPD.delinquency_id = DEL.delinquency_id
AND DEL.customer_site_use_id = p_customer_site_use_id;
/* SELECT sob.currency_code
INTO g_curr_rec.base_currency
FROM ar_system_parameters sysp,
gl_sets_of_books sob
WHERE sob.set_of_books_id = sysp.set_of_books_id;
SELECT TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
TRUNC(sysdate) pastYearTo
INTO g_curr_rec.past_year_from,
g_curr_rec.past_year_to
FROM dual; */