[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_custom_select varchar2(2000);
PROCEDURE update_strat_org
(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2
);
l_custom_select := ' SELECT p.party_name ' ||
' From hz_cust_acct_sites_all acct_sites, ' ||
' hz_party_sites party_site, ' ||
' hz_cust_accounts ca, ' ||
' hz_cust_site_uses_all site_uses, ' ||
' hz_parties p ' ||
' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
' AND acct_sites.party_site_id = party_site.party_site_id ' ||
' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
' AND site_uses.site_use_code = ''BILL_TO'' ' ||
' AND ca.party_id = p.party_id ';
l_custom_select := 'SELECT p.party_name ' ||
' From hz_cust_acct_sites_all acct_sites, ' ||
' hz_party_sites party_site, ' ||
' hz_cust_accounts ca, ' ||
' hz_cust_site_uses_all site_uses, ' ||
' hz_parties p,' ||
' iex_delinquencies_all del ' ||
' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
' AND acct_sites.party_site_id = party_site.party_site_id ' ||
' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
' AND site_uses.site_use_code = ''BILL_TO'' ' ||
' AND ca.party_id = p.party_id ' ||
' AND del.customer_site_use_id = site_uses.site_use_id ';
l_custom_select := l_custom_select || ' AND upper(p.party_name) >= upper(''' || p_customer_name_low || ''') ';
l_custom_select := l_custom_select || ' AND upper(p.party_name) <= upper(''' || p_customer_name_high || ''') ';
l_custom_select := l_custom_select || ' AND upper(ca.account_number) >= upper(''' || p_account_number_low || ''') ';
l_custom_select := l_custom_select || ' AND upper(ca.account_number) <= upper(''' || p_account_number_high || ''') ';
l_custom_select := l_custom_select || ' AND upper(site_uses.location) >= upper(''' || p_billto_location_low || ''') ';
l_custom_select := l_custom_select || ' AND upper(site_uses.location) <= upper(''' || p_billto_location_high || ''') ';
l_custom_select := l_custom_select || ' AND p.party_id ';
l_custom_select := l_custom_select || ' AND ca.cust_account_id ';
l_custom_select := l_custom_select || ' AND site_uses.site_use_id ';
l_custom_select := l_custom_select || ' AND del.delinquency_id ';
l_custom_select := l_custom_select || ' AND p.party_id ';
l_custom_select := l_custom_select || ' AND ca.cust_account_id ';
l_custom_select := l_custom_select || ' AND site_uses.site_use_id ';
l_custom_select := l_custom_select || ' AND del.delinquency_id ';
write_log(FND_LOG.LEVEL_STATEMENT,G_PKG_NAME || ' ' || l_api_name || ' - l_custom_select : '||l_custom_select);
select score_value,score_id
from iex_score_histories
where score_object_id = p_object_id
and score_object_code = p_object_type
order by creation_date desc;
select score_value, score_object_id, score_object_code,score_id
from iex_score_histories
where score_object_id in (p_object_id, p_object_id2)
and score_object_code in (p_object_type, p_object_type2)
order by creation_date desc;
select nvl(count(*),0)
from iex_bankruptcies
where party_id = p_par_id
and (disposition_code in ('GRANTED','NEGOTIATION')
OR (disposition_code is NULL));
SELECT predel_strategy_enabled
FROM iex_questionnaire_items;
SELECT decode(COUNT(*), 0, 'N', 'Y') into pre_delinquency_flag FROM IEX_STRATEGY_TEMPLATES_VL
WHERE CATEGORY_TYPE = l_DelStatusPreDel;
l_del_query := 'select d.party_cust_id, null, null, null, null, null,';
if l_custom_select IS NOT NULL then
l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.party_cust_id ) ';
l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''PARTY'' and JTF_OBJECT_ID = d.party_cust_id and STATUS_CODE = ''OPEN'') ';
l_del_query := 'select d.party_cust_id, d.cust_account_id, null, null, null, null,';
if l_custom_select IS NOT NULL then
l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.cust_account_id ) ';
l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_ACCOUNT'' and JTF_OBJECT_ID = d.cust_account_id and STATUS_CODE = ''OPEN'') ';
l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, null, null, null,';
if l_custom_select IS NOT NULL then
l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.customer_site_use_id ) ';
l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_BILLTO'' and JTF_OBJECT_ID = d.customer_site_use_id and STATUS_CODE = ''OPEN'') ';
l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id,';
if l_custom_select IS NOT NULL then
l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.delinquency_id ) ';
l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_DELINQUENCY'' and JTF_OBJECT_ID = d.delinquency_id and STATUS_CODE = ''OPEN'') ';
l_del_query := 'select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id,';
if l_custom_select IS NOT NULL then
l_del_query := l_del_query || ' and exists ( ' || l_custom_select || ' = d.delinquency_id ) ';
l_del_query := l_del_query || ' and not exists (select 1 from iex_strategies where JTF_OBJECT_TYPE = ''IEX_DELINQUENCY'' and JTF_OBJECT_ID = d.delinquency_id and STATUS_CODE = ''OPEN'') ';
select c.creation_date from iex_delinquencies_all c
where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
select c.creation_date from iex_delinquencies_all c
where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
select c.creation_date from iex_delinquencies_all c
where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
select c.creation_date from iex_delinquencies_all c
where (c.status = l_DelStatusDel or c.status = l_delStatusPreDel)
and c.party_cust_id = l_stry_cnt_rec.PARTY_CUST_ID
and c.cust_account_id = l_stry_cnt_rec.CUST_ACCOUNT_ID
and c.customer_site_use_id = l_stry_cnt_rec.customer_site_use_ID
and c.delinquency_id = l_stry_cnt_rec.delinquency_id
order by c.creation_date asc; -- Changed for bug#8248285 by PNAVEENK on 13-2-2009
select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
from iex_strategies where party_id = l_stry_cnt_rec.PARTY_CUST_ID
and jtf_object_id = l_stry_cnt_rec.jtf_object_id
and jtf_object_type = l_stry_cnt_rec.jtf_object_type
and checklist_yn = vCheckList;
select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
from iex_strategies where CUST_ACCOUNT_ID = l_stry_cnt_rec.CUST_ACCOUNT_ID
and jtf_object_id = l_stry_cnt_rec.jtf_object_id
and jtf_object_type = l_stry_cnt_rec.jtf_object_type
and checklist_yn = vCheckList;
select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
from iex_strategies where customer_site_use_ID = l_stry_cnt_rec.customer_site_use_ID
and jtf_object_id = l_stry_cnt_rec.jtf_object_id
and jtf_object_type = l_stry_cnt_rec.jtf_object_type
and checklist_yn = vCheckList;
select status_code, decode(score_value, null, 0, score_value), strategy_id, strategy_template_id
from iex_strategies where delinquency_id = l_stry_cnt_rec.delinquency_id
and jtf_object_id = l_stry_cnt_rec.jtf_object_id
and jtf_object_type = l_stry_cnt_rec.jtf_object_type
and checklist_yn = vCheckList;
select strategy_rank, decode(score_tolerance, null, 0, score_tolerance), change_strategy_yn
into vStrategyRank, vScoreTolerance, vChangeStrategy
from iex_strategy_templates_vl where strategy_temp_id = vStrategyTemplateId;
fnd_file.put_line(FND_FILE.LOG, ' Template ID selected ' || l_strategy_template_id);
' Strategy Template ID selected ' || l_strategy_template_id );
UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED',
last_update_date=sysdate --Added for bug#7594370 by PNAVEENK
WHERE STRATEGY_ID = vStrategyId;
UPDATE IEX_STRATEGIES SET STATUS_code = 'CANCELLED' WHERE STRATEGY_ID = vStrategyId;*/
select count(1)
into l_strat_count
from iex_strategies
where jtf_object_id = l_stry_cnt_rec.jtf_object_id
and jtf_object_type = l_stry_cnt_rec.jtf_object_type
and checklist_yn = vCheckList
and last_update_date>=trunc(sysdate)-1
and status_code not in ('OPEN','ONHOLD');
SELECT count(1)
into l_unpro_dels
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.party_cust_id=l_strategy_rec.object_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1
from iex_promise_details pd,
gl_sets_of_books gl,
ar_system_parameters_all sys
where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
and gl.set_of_books_id = sys.set_of_books_id
and ps.org_id = sys.org_id
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
SELECT count(1)
into l_unpro_dels
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.cust_account_id=l_strategy_rec.object_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1
from iex_promise_details pd,
gl_sets_of_books gl,
ar_system_parameters_all sys
where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
and gl.set_of_books_id = sys.set_of_books_id
and ps.org_id = sys.org_id
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
SELECT count(1)
into l_unpro_dels
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.customer_site_use_id=l_strategy_rec.object_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1
from iex_promise_details pd,
gl_sets_of_books gl,
ar_system_parameters_all sys
where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
and gl.set_of_books_id = sys.set_of_books_id
and ps.org_id = sys.org_id
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
SELECT count(1)
into l_unpro_dels
FROM ar_payment_schedules_all ps, iex_delinquencies_all del
WHERE del.delinquency_id=l_strategy_rec.object_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT')
and not exists(select 1
from iex_promise_details pd,
gl_sets_of_books gl,
ar_system_parameters_all sys
where pd.delinquency_id=del.delinquency_id
and pd.status='COLLECTABLE'
AND pd.state = 'PROMISE' -- Bug 16175748 bibeura
and gl.set_of_books_id = sys.set_of_books_id
and ps.org_id = sys.org_id
group by pd.delinquency_id
having sum(nvl(pd.promise_amount,0))>=ps.amount_due_remaining);
--Call gen_xml_body_strategy to insert this record to xml body
IF p_show_output = 'Y' THEN
if l_reassign_sty = 'N' THEN
gen_xml_body_strategy (p_strategy_rec => l_strategy_rec,
p_strategy_status => 'CREATE');
select s.strategy_id, s.delinquency_id,
s.object_id, s.object_type, s.strategy_template_id, s.jtf_object_type, s.jtf_object_id
from iex_strategies s where s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold) AND
checklist_yn = 'N';
vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
' where s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
/* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.party_cust_id = s.party_id '||
/* end add for bug 4408860 - add checking CLOSE status from case delinquency */
/* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
' and s.JTF_OBJECT_TYPE = ''PARTY''' ||
/* End add for bug 16568002 gnramasa 29th Mar 2013 */
' and not exists (select null from iex_delinquencies_all dd where dd.status '||
/* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
--' = ''' || l_DelStatusDel || ''' and dd.party_cust_id = s.party_id) ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
' where s.strategy_level = ' || l_DefaultStrategyLevel ||' and '||
' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
/* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id '||
/* end add for bug 4408860 - add checking CLOSE status from case delinquency */
/* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
' and s.JTF_OBJECT_TYPE = ''IEX_ACCOUNT''' ||
/* End add for bug 16568002 gnramasa 29th Mar 2013 */
' and not exists (select null from iex_delinquencies_all dd where dd.status '||
/* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
--' = ''' || l_DelStatusDel || ''' and dd.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id) ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists (' || l_custom_select || ' = s.cust_Account_id) ';
vPLSQL := 'select s.strategy_id, s.strategy_template_id, S.STATUS_CODE from iex_strategies s, iex_delinquencies_all d '||
' where s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
' s.status_code IN (''' || l_StratStatusOpen || ''', ''' || l_StratStatusOnhold || ''', ''' || l_StratStatusPending || ''') and '||
/* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
' (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and d.customer_site_use_id = s.customer_site_use_id '||
/* end add for bug 4408860 - add checking CLOSE status from case delinquency */
/* Begin add for bug 16568002 gnramasa 29th Mar 2013 */
' and s.JTF_OBJECT_TYPE = ''IEX_BILLTO''' ||
/* End add for bug 16568002 gnramasa 29th Mar 2013 */
' and not exists (select null from iex_delinquencies_all dd where dd.status '||
/* Begin add for bug 16563459 gnramasa 8th Apr 2013 */
--' = ''' || l_DelStatusDel || ''' and dd.customer_site_use_id = s.customer_site_use_id) ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.customer_site_use_id) ';
select s.strategy_id, s.strategy_template_id, s.status_code
from iex_strategies s, iex_delinquencies_all d where d.status = l_DelStatusCurrent and
s.strategy_level = l_DefaultStrategyLevel and
s.object_id = d.delinquency_id and
s.status_code IN (l_StratStatusOpen, l_StratStatusOnhold, l_StratStatusPending);
vPLSQL := 'select s.strategy_id, s.strategy_template_id, s.status_code '||
' from iex_strategies s, iex_delinquencies_all d '||
/* begin add for bug 4408860 - add checking CLOSE status from case delinquency */
' where (d.status = ''' || l_DelStatusCurrent || ''' or d.status = ''' || l_DelStatusClose || ''') and '||
/* end add for bug 4408860 - add checking CLOSE status from case delinquency */
/* Begin add for bug 16568002 gnramasa 1st Apr 2013 */
' s.JTF_OBJECT_TYPE = ''IEX_DELINQUENCY'' and ' ||
/* End add for bug 16568002 gnramasa 1st Apr 2013 */
' s.strategy_level = ' || l_DefaultStrategyLevel || ' and '||
' s.jtf_object_id = d.delinquency_id and '||
' s.status_code IN (''' || l_StratStatusOpen || ''' , ''' || l_StratStatusOnhold || ''' , ''' || l_StratStatusPending || ''') ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || ' = s.delinquency_id)'; --Bug# 6870773 Naveen
--Call gen_xml_body_strategy to insert this record to xml body
IF p_show_output = 'Y' THEN
gen_xml_body_strategy (p_strategy_id => l_strategy_id,
p_strategy_status => 'CLOSE');
select s.strategy_id strategy_id,
s.strategy_template_id strategy_template_id,
S.STATUS_CODE STATUS_CODE,
d.party_cust_id party_id
from iex_strategies s, iex_delinquencies_all d
where s.strategy_level = 10 and
s.status_code = 'ONHOLD' and
d.status in ('DELINQUENT','PREDELINQUENT') and
d.party_cust_id = s.party_id and
not exists (select 1 from iex_promise_details p
where p.status='COLLECTABLE'
AND d.delinquency_id=p.delinquency_id)
and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
--and exists ( l_custom_select = s.party_id)
group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.party_cust_id;
select s.strategy_id strategy_id,
s.strategy_template_id strategy_template_id,
S.STATUS_CODE STATUS_CODE,
d.cust_account_id cust_account_id
from iex_strategies s, iex_delinquencies_all d
where s.strategy_level = 20 and
s.status_code = 'ONHOLD' and
d.status in ('DELINQUENT','PREDELINQUENT') and
d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id and
not exists (select 1 from iex_promise_details p
where p.status='COLLECTABLE'
AND d.delinquency_id=p.delinquency_id)
and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
--and exists ( l_custom_select = s.cust_Account_id)
group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.cust_account_id;
select s.strategy_id strategy_id,
s.strategy_template_id strategy_template_id,
S.STATUS_CODE STATUS_CODE,
d.customer_site_use_id billto_id
from iex_strategies s, iex_delinquencies_all d
where s.strategy_level = 30 and
s.status_code = 'ONHOLD' and
d.status in ('DELINQUENT','PREDELINQUENT') and
d.customer_site_use_id = s.customer_site_use_id and
not exists (select 1 from iex_promise_details p
where p.status='COLLECTABLE'
AND d.delinquency_id=p.delinquency_id)
and nvl(s.org_id,-99) = decode(l_org_enabled,'Y',l_org_id,nvl(s.org_id,-99))
--and exists ( l_custom_select = s.customer_site_use_id)
group by s.strategy_id, s.strategy_template_id, S.STATUS_CODE,d.customer_site_use_id;
/* select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
into l_DefaultStrategyLevel,l_StrategyLevelName
from iex_app_preferences_vl
where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y'; */
vPLSQL := 'select s.strategy_id strategy_id, '||
' s.strategy_template_id strategy_template_id, '||
' S.STATUS_CODE STATUS_CODE, '||
' d.party_cust_id party_id '||
' from iex_strategies s, iex_delinquencies_all d '||
' where s.strategy_level = 10 and '||
' s.status_code = ''ONHOLD'' and '||
' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
' d.party_cust_id = s.party_id and '||
' not exists (select 1 from iex_promise_details p '||
' where p.status=''COLLECTABLE'' '||
' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
' AND d.delinquency_id=p.delinquency_id) ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for party id : = ' || l_party_id);
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
--Call gen_xml_body_strategy to insert this record to xml body
IF p_show_output = 'Y' THEN
gen_xml_body_strategy (p_strategy_id => l_strategy_id,
p_strategy_status => 'REOPEN');
vPLSQL := 'select s.strategy_id strategy_id, '||
' s.strategy_template_id strategy_template_id, '||
' S.STATUS_CODE STATUS_CODE, '||
' d.cust_account_id cust_account_id '||
' from iex_strategies s, iex_delinquencies_all d '||
' where s.strategy_level = 20 and '||
' s.status_code = ''ONHOLD'' and '||
' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
' d.CUST_ACCOUNT_id = s.CUST_ACCOUNT_id and '||
' not exists (select 1 from iex_promise_details p '||
' where p.status=''COLLECTABLE'' '||
' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
' AND d.delinquency_id=p.delinquency_id) ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || ' = s.cust_Account_id) ';
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for account id: = ' || l_cust_account_id);
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
--Call gen_xml_body_strategy to insert this record to xml body
IF p_show_output ='Y' THEN
gen_xml_body_strategy (p_strategy_id => l_strategy_id,
p_strategy_status => 'REOPEN');
vPLSQL := 'select s.strategy_id strategy_id, '||
' s.strategy_template_id strategy_template_id, '||
' S.STATUS_CODE STATUS_CODE, '||
' d.customer_site_use_id billto_id '||
' from iex_strategies s, iex_delinquencies_all d '||
' where s.strategy_level = 30 and '||
' s.status_code = ''ONHOLD'' and '||
' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
' d.customer_site_use_id = s.customer_site_use_id and '||
' not exists (select 1 from iex_promise_details p '||
' where p.status=''COLLECTABLE'' '||
' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
' AND d.delinquency_id=p.delinquency_id) ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.customer_site_use_id) ';
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for customer site use id : = ' || l_cust_site_use_id);
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
--Call gen_xml_body_strategy to insert this record to xml body
IF p_show_output = 'Y' THEN
gen_xml_body_strategy (p_strategy_id => l_strategy_id,
p_strategy_status => 'REOPEN');
vPLSQL := 'select s.strategy_id strategy_id, '||
' s.strategy_template_id strategy_template_id, '||
' S.STATUS_CODE STATUS_CODE, '||
' d.delinquency_id delinquency_id '||
' from iex_strategies s, iex_delinquencies_all d '||
' where s.strategy_level = 40 and '||
' s.status_code = ''ONHOLD'' and '||
' nvl(s.release_date,sysdate) <= SYSDATE and ' || -- Bug 14804876 bibeura
' d.status in (''DELINQUENT'',''PREDELINQUENT'') and '||
' d.delinquency_id = s.delinquency_id and '||
' not exists (select 1 from iex_promise_details p '||
' where p.status=''COLLECTABLE'' '||
' and p.state=''PROMISE'' '|| -- bug 9738518 PNAVEENK
' AND d.delinquency_id=p.delinquency_id) ';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= d.delinquency_id) ';
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Update strategy for delinquency id : = ' || l_delinquency_id);
write_log(FND_LOG.LEVEL_UNEXPECTED, 'Strategy updated. id = ' || l_strategy_id);
--Call gen_xml_body_strategy to insert this record to xml body
IF p_show_output = 'Y' THEN
gen_xml_body_strategy (p_strategy_id => l_strategy_id,
p_strategy_status => 'REOPEN');
SELECT ST.strategy_temp_id, ST.strategy_rank, OBF.ENTITY_NAME
from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF
where ST.category_type = pCategoryType and ST.Check_List_YN = 'N' AND
OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
and not exists
(select 'x' from iex_strategies SS where SS.delinquency_id = pDelinquencyID
and SS.OBJECT_TYPE = pCategoryType)
ORDER BY strategy_rank DESC;
vstr1 := ' select 1 from ' ;
FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF , iex_strategy_template_groups temgp
where ST.Check_List_YN = l_No AND
((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> l_No) and
st.strategy_level = l_DefaultStrategyLevel and
OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
OBF.OBJECT_FILTER_TYPE(+) = l_StratObjectFilterType
AND st.strategy_temp_group_id = temgp.group_id -- added scoring engine filter
AND DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',temgp.scoring_engine_id,'-1') =
DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',p_stry_cnt_rec.score_id , '-1') -- for bug 13388975 pnaveenk
and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
and exists (select 1 from IEX_STRATEGY_WORK_TEMP_XREF strx
where strx.strategy_temp_id = st.strategy_temp_id)
-- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
and ST.STRATEGY_RANK <= p_stry_cnt_rec.SCORE_VALUE
-- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
-- Bug 7392752 by Ehuh
and exists (select 1 from iex_strategy_template_groups tg
where tg.group_id = st.strategy_temp_group_id
and tg.enabled_flag <> 'N'
and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
and trunc(nvl(tg.valid_to_dt,sysdate)) )
and st.category_type in ('DELINQUENT','PREDELINQUENT') -- added for bug#7709114 by PNAVEENK on 22-1-2009
ORDER BY to_number(st.strategy_rank) DESC;
FOR SELECT ST.strategy_temp_id, to_number(ST.strategy_rank), OBF.ENTITY_NAME, obf.active_flag
from IEX_STRATEGY_TEMPLATES_B ST, IEX_OBJECT_FILTERS OBF , iex_strategy_template_groups temgp
where ST.category_type = chk_obj_type and ST.Check_List_YN = l_No AND
((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> l_No) and
st.strategy_level = l_DefaultStrategyLevel and
OBF.OBJECT_ID(+) = ST.Strategy_temp_Group_ID and
OBF.OBJECT_FILTER_TYPE(+) = l_StratObjectFilterType
AND st.strategy_temp_group_id = temgp.group_id -- added for bug 13388975
AND DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',temgp.scoring_engine_id,'-1') =
DECODE(fnd_profile.value('IEX_USE_STRATEGY_SCORING'),'Y',p_stry_cnt_rec.score_id , '-1')
and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
and exists (select 1 from IEX_STRATEGY_WORK_TEMP_XREF strx
where strx.strategy_temp_id = st.strategy_temp_id)
-- Begin - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
and ST.STRATEGY_RANK <= p_stry_cnt_rec.SCORE_VALUE
-- End - Andre Araujo -- 01/18/2005 - 4924879 - Improve performance by selecting less records
-- Bug 7392752 by Ehuh
and exists (select 1 from iex_strategy_template_groups tg
where tg.group_id = st.strategy_temp_group_id
and tg.enabled_flag <> 'N'
and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
and trunc(nvl(tg.valid_to_dt,sysdate)) )
ORDER BY to_number(st.strategy_rank) DESC;
' select 1 from ' || c_Rec_ENTITY_NAME ||
' where delinquency_id = ' || p_stry_cnt_rec.delinquency_id ||
' and rownum < 2 ';
' select 1 from ' || c_Rec_ENTITY_NAME ||
' where CUST_ACCOUNT_id = ' || p_stry_cnt_rec.CUST_ACCOUNT_id ||
' and rownum < 2 ';
' select 1 from ' || c_Rec_ENTITY_NAME ||
' where party_id = ' || p_stry_cnt_rec.PARTY_CUST_ID ||
' and rownum < 2 ';
Select st.Strategy_Temp_ID FROM IEX_STRATEGY_TEMPLATES_B st where
st.Check_List_YN = l_No AND st.ENABLED_FLAG <> l_No
-- Bug 7392752 by Ehuh
and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE)) AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
and exists (select 1 from iex_strategy_template_groups tg
where tg.group_id = st.strategy_temp_group_id
and tg.enabled_flag <> 'N'
and trunc(sysdate) between trunc(nvl(tg.valid_from_dt,sysdate))
and trunc(nvl(tg.valid_to_dt,sysdate)) );
l_con_update_re_st boolean;
SELECT organization_id from hr_operating_units where
mo_global.check_access(organization_id) = 'Y'
AND organization_id = nvl(P_ORG_ID,organization_id);
SELECT lookup_code FROM IEX_LOOKUPS_V
WHERE LOOKUP_TYPE='IEX_RUNNING_LEVEL'
AND iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
select preference_value
from iex_app_preferences_b
where preference_name='COLLECTIONS STRATEGY LEVEL'
and enabled_flag='Y'
and (org_id = p_org_id or org_id is null)
order by nvl(org_id,0) desc;
select preference_value
from iex_app_preferences_b
where preference_name='COLLECTIONS STRATEGY LEVEL'
and enabled_flag='Y'
and org_id is null;
select collections_methods
from iex_questionnaire_items;
select nvl(collections_method,'STRATEGIES')
from IEX_app_preferences_b where
org_id = p_org_id and enabled_flag ='Y';
SELECT COUNT(1)
from hr_operating_units a, IEX_app_preferences_b b
where mo_global.check_access(a.organization_id) = 'Y'
and a.organization_id = b.org_id
and b.collections_method = 'STRATEGIES'
and b.enabled_flag ='Y'
AND organization_id = nvl(p_org_id,organization_id);
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
fnd_file.put_line(FND_FILE.LOG, 'Update Multi Level Strategy Setup in Questionnaire table');
writelog(' Update Multi Level Strategy Setup in Questionnaire table');
IEX_CHECKLIST_UTILITY.UPDATE_MLSETUP;
writelog(' End update Multi Level Setup in Questionnaire table');
select count(1)
into l_count
from iex_strategies
where org_id is null
and strategy_level=l_DefaultStrategyLevel;
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
message => 'Atleast one OU must be registered with business level to run the program ');
-- select collections_methods into from iex_questionnaire_items;
-- select nvl(collections_method,'STRATEGIES') into l_org_id_coll_method from IEX_app_preferences_b where org_id = l_org_id and enabled_flag ='Y';
select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
into l_DefaultStrategyLevel from dual;
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
into l_DefaultStrategyLevel from dual;
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
into l_DefaultStrategyLevel,l_StrategyLevelName
from iex_app_preferences_b
where preference_name='COLLECTIONS STRATEGY LEVEL'
and enabled_flag='Y'
and org_id is null;
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
select count(1)
into l_count
from iex_strategies
where org_id is not null
and strategy_level=l_DefaultStrategyLevel;
select decode(l_StrategyLevelName, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50)
into l_DefaultStrategyLevel from dual;
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
into l_DefaultStrategyLevel,l_StrategyLevelName
from iex_app_preferences_b
where preference_name='COLLECTIONS STRATEGY LEVEL'
and enabled_flag='Y'
and org_id is null;
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'Iex Strategy Management concurrent program failed to run as the collections method is set up for dunning.');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'Opearting Unit passed is Not Setup for Startegy please check Setup');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'No Opearting Unit is Setup for Startegy please check Setup');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
message => 'At least one Opearting Unit is Setup for Dunning please check Setup');
l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
message => 'Exception occured while running the Concurrent Program : '||sqlerrm); -- added by snuthala for bug 10221334 on 21-10-2010
select strategy_id
from iex_strategies
where jtf_object_type in ('PARTY','IEX_ACCOUNT','IEX_BILLTO','IEX_DELINQUENCY')
and party_id = c_party_id
and strategy_level <> c_str_level
and status_code in ('OPEN' , 'ONHOLD');
update iex_strategies set status_code='CANCELLED' where strategy_id = l_str_id.strategy_id;
PROCEDURE update_strat_org
(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2
) IS
cursor c_bill_strat_wo_ou(p_org_id number)
is select st.strategy_id,su.org_id
from iex_strategies st,
hz_cust_site_uses_all su
where st.object_type='BILL_TO'
and st.org_id is null
and st.object_id=su.site_use_id
and su.org_id = p_org_id;
is select st.strategy_id,p_org_id
from iex_strategies st,
hz_parties hp
where st.object_type='PARTY'
and st.org_id is null
and st.object_id=hp.party_id
and not exists(select 1 from
hz_cust_accounts ca,
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all su
where hp.party_id = ca.party_id
and ca.cust_account_id=cas.cust_account_id
and cas.cust_acct_site_id=su.cust_acct_site_id
and su.org_id <> p_org_id)
group by st.strategy_id,p_org_id;
is select st.strategy_id,p_org_id
from iex_strategies st,
hz_cust_accounts ca
where st.object_type='ACCOUNT'
and st.org_id is null
and st.object_id=ca.cust_account_id
and not exists(select 1 from
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all su
where ca.cust_account_id=cas.cust_account_id
and cas.cust_acct_site_id=su.cust_acct_site_id
and su.org_id <> p_org_id);
is select st.strategy_id,del.org_id
from iex_strategies st,
iex_delinquencies_all del
where st.object_type='DELINQUENT'
and st.org_id is null
and st.object_id=del.delinquency_id
and del.org_id = p_org_id;
is select st.strategy_id,null
from iex_strategies st
where st.object_type=p_object_type
and st.org_id is not null;
update iex_strategies
set org_id=rec1.org_id
where strategy_id = rec1.strategy_id;
update iex_strategies
set org_id=null
where strategy_id = rec1.strategy_id;
update iex_strategies
set org_id=rec1.org_id
where strategy_id = rec1.strategy_id;
update iex_strategies
set org_id=null
where strategy_id = rec1.strategy_id;
update iex_strategies
set org_id=orgs(i)
where strategy_id = strategies(i);
write_log(FND_LOG.LEVEL_STATEMENT, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
fnd_file.put_line(FND_FILE.LOG, 'In API update_strat_org raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
SELECT competence_id
from iex_strategy_work_skills
where work_item_temp_id = p_work_item_temp_id;
select source_name
from jtf_rs_resource_extns
where resource_id = p_resource_id;
select
meaning
from fnd_lookups
where lookup_type= 'YES_NO'
and lookup_code = p_lookup_code;
select source_name
from jtf_rs_resource_extns
where resource_id = p_resource_id;
select using_customer_level,
using_account_level,
using_billto_level,
using_delinquency_level,
define_party_running_level,
define_ou_running_level
from iex_questionnaire_items;
SELECT iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',preference_value)
FROM iex_app_preferences_b
WHERE preference_name='COLLECTIONS STRATEGY LEVEL'
AND enabled_flag = 'Y'
AND org_id IS NULL;
select collections_methods
from iex_questionnaire_items;
select to_char(sysdate, 'YYYY-MM-DD')
into l_report_date
from dual;
select
sty.party_id,
sty.cust_Account_id,
sty.customer_site_use_id,
sty.delinquency_id,
sty.score_value,
tpl.strategy_name,
stry_temp_wkitem.name,
iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',swi.status_code) STATUS_MEANING,
jtf.source_name,
iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',(decode(sty.strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT',
30, 'BILL_TO', 40, 'DELINQUENCY'))) strategy_level_name,
sty.strategy_level strategy_level
from iex_strategies sty,
iex_strategy_templates_tl tpl,
iex_strategy_work_items swi,
iex_stry_temp_work_items_vl stry_temp_wkitem,
jtf_rs_resource_extns jtf
where sty.strategy_id = p_strategy_id
and sty.strategy_template_id = tpl.strategy_temp_id
and tpl.language = userenv('LANG')
and sty.next_work_item_id = swi.work_item_id
and swi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
and stry_temp_wkitem.language = userenv('LANG')
and swi.resource_id = jtf.resource_id;
select
sty.party_id,
sty.cust_Account_id,
sty.customer_site_use_id,
sty.delinquency_id,
sty.score_value,
tpl.strategy_name,
iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',(decode(sty.strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT',
30, 'BILL_TO', 40, 'DELINQUENCY'))) strategy_level_name,
sty.strategy_level strategy_level
from iex_strategies sty,
iex_strategy_templates_tl tpl
where sty.strategy_id = p_strategy_id
and sty.strategy_template_id = tpl.strategy_temp_id
and tpl.language = userenv('LANG');
SELECT tpl.strategy_name,
iex_utilities.get_lookup_meaning('IEX_RUNNING_LEVEL',(DECODE(p_strategy_rec.strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT',
30, 'BILL_TO', 40, 'DELINQUENCY'))) strategy_level_name
FROM iex_strategy_templates_tl tpl,
iex_strategy_templates_b tpb
WHERE tpb.strategy_temp_id = tpl.strategy_temp_id
AND tpl.strategy_temp_id = l_sty_template_id
AND tpl.language = userenv('LANG');
select stry_temp_wkitem.name,
stry_temp_wkitem.work_item_temp_id,
iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',(decode(stry_temp_wkitem.pre_execution_wait,0,'OPEN','PRE-WAIT'))) STATUS_MEANING
from iex_strategy_work_temp_xref xref
,iex_stry_temp_work_items_vl stry_temp_wkitem
where xref.work_item_temp_id = stry_temp_wkitem.work_item_temp_id
and xref.strategy_temp_id = l_sty_template_id
and stry_temp_wkitem.language = userenv('LANG')
order by xref.work_item_order;
select
party_name
from hz_parties
where party_id = p_party_id;
select
p.party_name,
c.account_number
from hz_parties p,
hz_cust_accounts c
where c.cust_account_id = p_cust_acct_id
and c.party_id = p.party_id;
select
p.party_name,
c.account_number,
site_uses.location
from hz_parties p,
hz_cust_accounts c,
hz_cust_acct_sites_all acct_sites,
hz_cust_site_uses_all site_uses
where site_uses.site_use_id = p_cust_site_use_id
and acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
and c.cust_account_id = acct_sites.cust_account_id
and p.party_id = c.party_id;
select
p.party_name,
aps.trx_number TRANSACTION_NUMBER,
b.account_number,
c.location
from iex_delinquencies_all del,
ar_payment_schedules_all aps ,
hz_parties p,
iex_strategies a,
hz_cust_accounts b,
hz_cust_site_uses_all c
where del.delinquency_id = p_delinquency_id
and del.payment_Schedule_id = aps.payment_Schedule_id
and del.party_cust_id = p.party_id
and del.delinquency_id = a.delinquency_id
and a.cust_account_id = b.cust_account_id
and a.customer_site_use_id = c.site_use_id;
select
name
from hr_all_organization_units_tl
where organization_id = l_org_id
and language = userenv('LANG');
select p.party_id party_id,
p.party_name party_name
from hz_parties p
where p.party_id in (
select
d.party_cust_id
from
iex_delinquencies_all d
group by d.party_cust_id
having count(distinct d.org_id) > 1);
select p.party_id party_id,
p.party_name party_name,
ca.account_number account_number,
ca.cust_account_id cust_account_id
from hz_parties p,
hz_cust_accounts ca
where p.party_id = ca.party_id
and ca.cust_account_id in (
select
d.cust_account_id
from
iex_delinquencies_all d
group by d.cust_account_id
having count(distinct d.org_id) > 1);
select collections_methods
from iex_questionnaire_items;
select nvl(collections_method,'STRATEGIES')
from IEX_app_preferences_b where
org_id = p_org_id and enabled_flag ='Y';
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
vPLSQL := 'select s.party_id party_id, s.party_name party_name from hz_parties s ' ||
' where s.party_id in ( select d.party_cust_id from iex_delinquencies_all d '||
' group by d.party_cust_id having count(distinct d.org_id) > 1)';
if l_custom_select IS NOT NULL then
vPLSQL := vPLSQL || ' and exists ( ' || l_custom_select || '= s.party_id) ';
writelog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
vPLSQL1 := 'select s.party_id party_id, s.party_name party_name, cu_ac.account_number account_number, cu_ac.cust_account_id cust_account_id '||
' from hz_parties s, hz_cust_accounts cu_ac where s.party_id = cu_ac.party_id and cu_ac.cust_account_id in ( '||
' select d.cust_account_id from iex_delinquencies_all d group by d.cust_account_id having count(distinct d.org_id) > 1)';
if l_custom_select IS NOT NULL then
vPLSQL1 := vPLSQL1 || ' and exists ( ' || l_custom_select || '= cu_ac.cust_account_id) ';
vPLSQL2 := 'SELECT organization_id,name from hr_operating_units where '||
' mo_global.check_access(organization_id) = ''Y''';
select STRATEGY_NAME ,ENABLED_FLAG
into l_DefaultTempName, l_EnabledFlag
from iex_strategy_templates_vl
where STRATEGY_TEMP_ID=l_DefaultTempID;
select SOURCE_NAME,USER_NAME
into l_SourceName,l_UserName
from jtf_rs_resource_extns
where RESOURCE_ID=l_default_rs_id;
select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 50),preference_value
into l_DefaultStrategyLevel,l_StrategyLevelName
from iex_app_preferences_vl
where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = l_Yes;
select DEFINE_PARTY_RUNNING_LEVEL,DEFINE_OU_RUNNING_LEVEL
into l_party_override,l_org_override
from IEX_QUESTIONNAIRE_ITEMS;