[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_DelSelect varchar2(1000) ;
select score_value
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
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;
c_DelSelect :=
' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
' d.transaction_id, d.payment_schedule_id, ' ||
' d.delinquency_id object_id, ''DELINQUENT'' object_type , ' ||
' d.score_value ' ||
' , 40 strategy_level, d.delinquency_id jtf_object_id, ''IEX_DELINQUENCY'' jtf_object_type ' ||
' from iex_delinquencies d where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.delinquency_id = :pObjectID ';
' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
' d.transaction_id, d.payment_schedule_id, ' ||
' bankruptcy_id object_id, ''BANKRUPTCY'' object_type, ' ||
' d.score_value ' ||
' , NULL strategy_level, bankruptcy_id jtf_object_id, ''IEX_BANKRUPTCY'' jtf_object_type ' ||
' from iex_delinquencies d, iex_bankruptcies b where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.delinquency_id = b.delinquency_id ' ||
' and d.delinquency_id = :p_DelinquencyID ' ||
' and b.bankruptcy_id = :p_ObjectID ';
' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
' d.transaction_id, d.payment_schedule_id, ' ||
' writeoff_id object_id, ''WRITEOFF'' object_type, ' ||
' d.score_value ' ||
' , NULL strategy_level, writeoff_id jtf_object_id, ''IEX_WRITEOFF'' jtf_object_type ' ||
' from iex_delinquencies d, iex_writeoffs b where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.delinquency_id = b.delinquency_id ' ||
' and d.delinquency_id = :p_DelinquencyID ' ||
' and b.writeoff_id = :p_ObjectID ';
' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
' d.transaction_id, d.payment_schedule_id, ' ||
' repossession_id object_id, ''REPOSSESSION'' object_type, ' ||
' d.score_value ' ||
' , NULL strategy_level, repossession_id jtf_object_id, ''IEX_REPOSSESSION'' jtf_object_type ' ||
' from iex_delinquencies d, iex_repossessions b where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.delinquency_id = b.delinquency_id ' ||
' and d.delinquency_id = :p_DelinquencyID ' ||
' and b.repossession_id = :p_ObjectID ';
' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id, d.delinquency_id, ' ||
' d.transaction_id, d.payment_schedule_id, ' ||
'litigation_id object_id, ''LITIGATION'' object_type, ' ||
' d.score_value ' ||
' , NULL strategy_level, litigation_id jtf_object_id, ''IEX_LITIGATION'' jtf_object_type ' ||
' from iex_delinquencies d, iex_litigations b where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.delinquency_id = b.delinquency_id ' ||
' and d.delinquency_id = :p_DelinquencyID ' ||
' and b.litigation_id = :p_ObjectID ';
'select d.party_cust_id, null, null, null, null, null, ' ||
' d.PARTY_CUST_ID object_id, ''PARTY'' object_type, null' ||
' , 10 strategy_level, d.PARTY_CUST_ID jtf_object_id, ''PARTY'' jtf_object_type' ||
' from iex_delinquencies d' ||
' where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.party_cust_id = :pObjectID ' ||
' group by d.party_cust_id ';
' select d.party_cust_id, d.cust_account_id, null, null, null, null, ' ||
' d.cust_account_id object_id, ''ACCOUNT'' object_type, null, ' ||
' 20 strategy_level, d.cust_account_id jtf_object_id, ''IEX_ACCOUNT'' jtf_object_type ' ||
' from iex_delinquencies d ' ||
' where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.cust_account_id = :pObjectID ' ||
' group by d.party_cust_id, d.cust_account_id ';
' select d.party_cust_id, d.cust_account_id, d.customer_site_use_id , null, null, null, ' ||
' d.customer_site_use_id object_id, ''BILL_TO'' object_type, null, ' ||
' 30 strategy_level, d.customer_site_use_id jtf_object_id, ''IEX_BILLTO'' jtf_object_type ' ||
' from iex_delinquencies d ' ||
' where (d.status = ''DELINQUENT'' ' ||
' or d.status = ''PREDELINQUENT'') ' ||
' and d.customer_site_use_id = :pObjectID ' ||
' group by d.party_cust_id, d.cust_account_id, d.customer_site_use_id ';
' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
' NULL transaction_id, NULL payment_schedule_id, ' ||
' bankruptcy_id object_id, ''BANKRUPTCY'' object_type, ' ||
' NULL score_value ' ||
' , NULL strategy_level, bankruptcy_id jtf_object_id, ''IEX_BANKRUPTCY'' jtf_object_type ' ||
' from iex_bankruptcies where bankruptcy_id = :p_ObjectID ';
' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
' NULL transaction_id, NULL payment_schedule_id, ' ||
' writeoff_id object_id, ''WRITEOFF'' object_type, ' ||
' NULL score_value ' ||
' , NULL strategy_level, writeoff_id jtf_object_id, ''IEX_WRITEOFF'' jtf_object_type ' ||
' from iex_writeoffs where writeoff_id = :p_ObjectID ';
' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
' NULL transaction_id, NULL payment_schedule_id, ' ||
' repossession_id object_id, ''REPOSSESSION'' object_type, ' ||
' NULL score_value ' ||
' , NULL strategy_level, repossession_id jtf_object_id, ''IEX_REPOSSESSION'' jtf_object_type ' ||
' from iex_repossessions where repossession_id = :p_ObjectID ';
' select party_id, cust_account_id, customer_site_use_id, NULL delinquency_id, ' ||
' NULL transaction_id, NULL payment_schedule_id, ' ||
' litigation_id object_id, ''LITIGATION'' object_type, ' ||
' NULL score_value ' ||
' , NULL strategy_level, litigation_id jtf_object_id, ''IEX_LITIGATION'' jtf_object_type ' ||
' from iex_litigations where litigation_id = :p_ObjectID ';
iex_debug_pub.logmessage('create_strategy: ' || 'STEP 35 Query: ' || c_DelSelect );
Execute Immediate c_DelSelect into fPartyCustID, fcustAccountID, fCustomerSiteUseId,
fDelinquencyID, fTransactionID, fPaymentScheduleID, fObjectID,
fObjectType, fScoreValue, fStrategyLevel, fJTFObjectId, fJTFobjectType
using p_ObjectID;
select preference_value
into l_StrategyLevel
from iex_app_preferences_vl
where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' ;
select payment_schedule_id
into l_payment_schedule_id
from iex_delinquencies
where delinquency_id = l_stry_cnt_rec.delinquency_id;
IEX_DEBUG_PUB.LogMessage( 'While selecting payment_schedule_id Rised Exception ');
select status_code from iex_strategies where party_id = l_strategy_rec.party_id and
jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
and (checklist_yn IS null or checklist_yn = 'N') ;
select status_code from iex_strategies where CUST_ACCOUNT_ID = l_strategy_rec.CUST_ACCOUNT_ID and
jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
and (checklist_yn IS null or checklist_yn = 'N') ;
select status_code from iex_strategies where customer_site_use_ID = l_strategy_rec.customer_site_use_ID and
jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
and (checklist_yn IS null or checklist_yn = 'N') ;
select status_code from iex_strategies where
delinquency_id = l_strategy_rec.delinquency_id and
jtf_object_id = l_strategy_rec.jtf_object_id and jtf_object_type = l_strategy_rec.jtf_object_type
and (checklist_yn IS null or checklist_yn = 'N') ;
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_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
where ST.category_type = p_stry_cnt_rec.object_type and ST.Check_List_YN = 'N' AND
((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> 'N') and
st.strategy_level = l_DefaultStrategyLevel and
OBF.OBJECT_ID(+) = ST.Strategy_temp_ID and
OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
ORDER BY to_number(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
where ST.Check_List_YN = 'N' AND
((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> 'N') and
st.strategy_level = l_DefaultStrategyLevel and
OBF.OBJECT_ID(+) = ST.Strategy_temp_ID and
OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
and nvl(st.valid_from_dt, sysdate) <= nvl(st.valid_to_dt,SYSDATE)
and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
ORDER BY to_number(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
where ST.category_type = p_stry_cnt_rec.object_type and ST.Check_List_YN = 'N' AND
((ST.ENABLED_FLAG IS NULL) or ST.ENABLED_FLAG <> 'N') and
st.strategy_level = l_DefaultStrategyLevel and
OBF.OBJECT_ID(+) = ST.Strategy_temp_ID and
OBF.OBJECT_FILTER_TYPE(+) = 'IEXSTRAT'
and (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(st.valid_from_dt, SYSDATE))
AND TRUNC(NVL(st.valid_to_dt, SYSDATE)))
ORDER BY to_number(strategy_rank) DESC;
(select 'x' from iex_strategies SS where SS.delinquency_id = p_stry_cnt_rec.delinquency_id
and SS.OBJECT_TYPE = p_stry_cnt_rec.object_type)
ORDER BY to_number(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 strategy_id, status_code, object_version_number from iex_strategies
where ((delinquency_id = p_delinquency_id and
object_id = p_object_id and object_type = p_object_type)) and (checklist_yn IS NULL or checkList_YN = 'N')
and (status_code in ( 'OPEN', 'ONHOLD'));*/
select strategy_id, status_code, object_version_number from iex_strategies
where object_id = p_object_id and
object_type = p_object_type and
(checklist_yn IS NULL or checkList_YN = 'N') and
status_code in ( 'OPEN', 'ONHOLD');
select status from iex_delinquencies
where delinquency_id = p_delinquency_id;
debug_msg => '5. Update strategy ' || fStrategyID,
print_date => 'Y');
iex_strategy_pvt.update_strategy(
P_Api_Version_Number=>2.0,
p_commit => FND_API.G_FALSE,
P_Init_Msg_List =>FND_API.G_FALSE,
p_strategy_rec => l_strategy_rec,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
xo_object_version_number => l_object_version_number
);
debug_msg => 'IEX_STRATEGY_UPDATE_FAILED' || fObjectID,
print_date => 'Y');
FND_MESSAGE.Set_Name('IEX', 'IEX_STRATEGY_UPDATE_FAILED');
Select Strategy_Temp_ID FROM IEX_STRATEGY_TEMPLATES_B where
Check_List_YN = 'N';
select s.strategy_id, st.strategy_name, s.next_work_item_id, t.name from iex_strategies s,
iex_strategy_templates_vl st,
iex_strategy_work_items w,
iex_stry_temp_work_items_vl t
where ((s.delinquency_id = p_delinquency_id and
s.object_id = p_object_id and s.object_type = p_object_type)) and
(s.checklist_yn IS NULL or s.checkList_YN = 'N')
and s.strategy_template_id = st.strategy_temp_id(+)
and s.next_work_item_id = w.work_item_id
and w.work_item_template_id = t.work_item_temp_id(+)
order by s.creation_date desc;
select s.strategy_id, st.strategy_name, s.next_work_item_id, t.name from iex_strategies s,
iex_strategy_templates_vl st,
iex_strategy_work_items w,
iex_stry_temp_work_items_vl t
where (s.object_id = p_object_id and s.object_type = p_object_type) and
(s.checklist_yn IS NULL or s.checkList_YN = 'N')
and s.strategy_template_id = st.strategy_temp_id(+)
and s.next_work_item_id = w.work_item_id
and w.work_item_template_id = t.work_item_temp_id(+)
order by s.creation_date desc;
select strategy_id, status_code, object_version_number from iex_strategies where ((delinquency_id = p_delinquency_id and
object_id = p_object_id and object_type = p_object_type)) and (checklist_yn IS NULL or checkList_YN = 'N');
select strategy_id, status_code, object_version_number from iex_strategies
where (party_id = p_objectid and object_id = p_objectid and object_type = p_objecttype)
and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
select strategy_id, status_code, object_version_number from iex_strategies
where (cust_account_id = p_objectid and object_id = p_objectid and object_type = p_objecttype)
and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
select strategy_id, status_code, object_version_number from iex_strategies
where (customer_site_use_id = p_objectid and object_id = p_objectid and object_type = p_objecttype)
and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
select strategy_id, status_code, object_version_number from iex_strategies
where (delinquency_id = p_delinquencyid and object_id = p_objectid and object_type = p_objecttype)
and (checklist_yn IS NULL or checkList_YN = 'N') and Status_Code not in ('CLOSED', 'CANCELLED');
debug_msg => '5. Update strategy ' || fStrategyID,
print_date => 'Y');
iex_strategy_pvt.update_strategy(
P_Api_Version_Number=>2.0,
p_commit => FND_API.G_FALSE,
P_Init_Msg_List =>FND_API.G_FALSE,
p_strategy_rec => l_strategy_rec,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
xo_object_version_number => l_object_version_number
);
debug_msg => 'IEX_STRATEGY_UPDATE_FAILED' || fObjectID,
print_date => 'Y');
FND_MESSAGE.Set_Name('IEX', 'IEX_STRATEGY_UPDATE_FAILED');
select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
into l_DefaultStrategyLevel
from iex_app_preferences_vl
where preference_name = 'COLLECTIONS STRATEGY LEVEL' and enabled_flag = 'Y' ;