[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select user_id from jtf_rs_resource_extns
where resource_id =l_resource_id;
select a.dunning_id from iex_dunnings a
where a.object_id = p_workitem_id
and a.object_type = 'IEX_STRATEGY'
and a.status <> 'CLOSE';
SELECT
wkitem.resource_id ASSIGNED_TO
from
iex_strategy_work_items wkitem, iex_stry_temp_work_items_b stry_temp_wkitem_b, iex_stry_temp_work_items_tl stry_temp_wkitem_tl
, wf_item_types_tl item, jtf_rs_resource_extns res
WHERE
wkitem.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
and stry_temp_wkitem_b.work_item_temp_id =stry_temp_wkitem_tl.work_item_temp_id
and stry_temp_wkitem_tl.LANGUAGE = userenv('LANG')
and stry_temp_wkitem_b.WORKFLOW_ITEM_TYPE = item.name(+)
and item.language(+) = userenv('LANG')
and wkitem.resource_id = res.resource_id(+)
and wkitem.strategy_id = l_strategy_id
and wkitem.work_item_id = l_workitem_id;
select upper(b.category_type)
into l_dunning_method
from iex_strategy_work_items a, IEX_STRY_TEMP_WORK_ITEMS_VL b
where a.work_item_template_id = b.work_item_temp_id
and a.work_item_id = l_workitem_id
and b.work_type = 'AUTOMATIC';
select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
into l_DefaultStrategyLevel
from iex_strategies
where strategy_id = l_strategy_id;
select campaign_sched_id into l_campaign_sched_id from iex_delinquencies
where delinquency_id = l_delinquency_id;
l_dunning_rec_upd_old.last_update_date := sysdate;
IEX_DUNNING_PVT.Update_DUNNING(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_dunning_rec => l_dunning_rec_upd_old
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
iex_stry_utl_pub.update_work_item(
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_work_item_id => l_workitem_id,
p_status => 'INERROR_CHECK_NOTIFY',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_dunning_rec_upd.last_update_date := sysdate;
IEX_DUNNING_PVT.Update_DUNNING(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_dunning_rec => l_dunning_rec_upd
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
iex_debug_pub.logmessage ('update dunning l_status =>' || l_return_status);
Select user_name
from jtf_rs_resource_extns
where resource_id =p_resource_id;
select a.party_id, a.party_type, a.party_name,
a.person_first_name, a.person_last_name,
b.cust_account_id, b.status, b.payment_schedule_id,
b.aging_bucket_line_id, b.customer_site_use_id
from iex_delinquencies b, hz_parties a
where a.party_id(+) = b.party_cust_id
and b.delinquency_id = p_delinquency_id;
select a.party_id, a.party_type, a.party_name,
a.person_first_name, a.person_last_name, s.cust_account_id, s.customer_site_use_id
from hz_parties a, iex_strategy_work_items w, iex_strategies s
where a.party_id = s.party_id and s.strategy_id = w.strategy_id and w.work_item_id = p_work_item_id;
select a.amount_due_remaining
from ar_payment_schedules_all a, iex_delinquencies b
where a.payment_schedule_id(+) = b.payment_schedule_id
and b.delinquency_id = p_delinquency_id;
select a.post_execution_wait, a.execution_time_uom, a.schedule_wait, a.schedule_uom
from IEX_STRY_TEMP_WORK_ITEMS_VL a, IEX_STRATEGY_WORK_ITEMS b
where b.work_item_template_id = a.work_item_temp_id
and b.work_item_id = p_work_item_id;
select a.strategy_level,b.resource_id,a.strategy_id --Added strategy_id for bug#5502077 schekuri 02-May-2007
into l_strategy_level,l_resource_id,l_strategy_id
from iex_strategies a, iex_strategy_work_items b
where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
select ceil(wfi.BEGIN_DATE - wfa.begin_Date) into l_SkipFlag
from WF_ITEMS wfi, WF_ACTIVITIES wfa
WHERE wfi.ITEM_TYPE = 'IEXSTRY'
and wfa.version = (select min(wa.version) from wf_activities wa
where wa.item_type=wfa.item_type
and wa.name=wfa.name)
and wfi.item_key = l_Strategy_id
and wfa.item_type = wfi.item_type AND
wfa.name = 'RESET_WORK_ITEM_STATUS' ;
iex_stry_utl_pub.update_work_item(
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_work_item_id => l_work_item_id,
p_status => l_wk_status,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
end if;-- if update successful
select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 'DELINQUENCY') into l_strategy_level
from iex_strategies where strategy_id = l_strategy_id;
select count(*) into l_count from iex_dunnings where dunning_id in (
select dun.dunning_id from iex_dunnings dun
where dun.dunning_object_id = l_party_id
and dun.dunning_level = l_strategy_level
and dun.status = 'CLOSE'
and trunc(sysdate) = trunc(dun.creation_date) );
select count(*) into l_count from iex_dunnings where dunning_id in (
select dun.dunning_id from iex_dunnings dun
where dun.dunning_object_id = l_cust_account_id
and dun.dunning_level = l_strategy_level
and dun.status = 'CLOSE'
and trunc(sysdate) = trunc(dun.creation_date) );
select count(*) into l_count from iex_dunnings where dunning_id in (
select dun.dunning_id from iex_dunnings dun
where dun.dunning_object_id = l_customer_site_use_id
and dun.dunning_level = l_strategy_level
and dun.status = 'CLOSE'
and trunc(sysdate) = trunc(dun.creation_date) );
select cust_account_id into l_cust_account_id from iex_delinquencies
where delinquency_id = l_delinquency_id;
-- select count(*) into l_count from iex_dunnings where dunning_id in (
-- select dun.dunning_id from iex_delinquencies del, iex_dunnings dun
-- where del.cust_account_id = l_cust_account_id
-- and del.delinquency_id = dun.delinquency_id
-- and dun.status = 'CLOSE'
-- and trunc(sysdate) = trunc(dun.creation_date) );
select count(*) into l_count from iex_dunnings where dunning_id in (
select dun.dunning_id from iex_delinquencies del, iex_dunnings dun
where del.cust_account_id = l_cust_account_id
and ((del.delinquency_id = dun.delinquency_id and dun.status = 'CLOSE') or
(del.delinquency_id = dun.delinquency_id and dun.status = 'OPEN' and
del.delinquency_id <> l_delinquency_id )
)
and trunc(sysdate) = trunc(dun.creation_date) );
select user_id from jtf_rs_resource_extns
where resource_id =l_resource_id;
select a.dunning_id from iex_dunnings a
where a.object_id = p_workitem_id
and a.object_type = 'WORK_ITEM'
and a.status <> 'CLOSE';
select a.party_id, a.party_type, a.party_name,
a.person_first_name, a.person_last_name,
b.cust_account_id, b.status, b.payment_schedule_id,
b.aging_bucket_line_id, b.customer_site_use_id
from iex_delinquencies b, hz_parties a
where a.party_id(+) = b.party_cust_id
and b.delinquency_id = p_delinquency_id;
select a.amount_due_remaining
from ar_payment_schedules_all a, iex_delinquencies b
where a.payment_schedule_id(+) = b.payment_schedule_id
and b.delinquency_id = p_delinquency_id;
select delinquency_id, a.strategy_id
from iex_strategies a, iex_strategy_work_items b
where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
select a.xdo_template_id from IEX_STRY_TEMP_WORK_ITEMS_VL a, iex_strategy_work_items b
where a.work_item_temp_id = b.work_item_template_id and work_item_id = p_work_item_id;
select fulfil_temp_id from IEX_STRY_TEMP_WORK_ITEMS_VL a, iex_strategy_work_items b
where a.work_item_temp_id = b.work_item_template_id and work_item_id = p_work_item_id;
select a.party_id, a.party_type, a.party_name,
a.person_first_name, a.person_last_name, s.cust_account_id, s.customer_site_use_id
from hz_parties a, iex_strategy_work_items w, iex_strategies s
where a.party_id = s.party_id and s.strategy_id = w.strategy_id and w.work_item_id = p_work_item_id;
SELECT
wkitem.resource_id ASSIGNED_TO
from
iex_strategy_work_items wkitem, iex_stry_temp_work_items_b stry_temp_wkitem_b, iex_stry_temp_work_items_tl stry_temp_wkitem_tl
, wf_item_types_tl item, jtf_rs_resource_extns res
WHERE
wkitem.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
and stry_temp_wkitem_b.work_item_temp_id =stry_temp_wkitem_tl.work_item_temp_id
and stry_temp_wkitem_tl.LANGUAGE = userenv('LANG')
and stry_temp_wkitem_b.WORKFLOW_ITEM_TYPE = item.name(+)
and item.language(+) = userenv('LANG')
and wkitem.resource_id = res.resource_id(+)
and wkitem.strategy_id = l_strategy_id
and wkitem.work_item_id = l_workitem_id;
select a.strategy_level into l_strategy_level from iex_strategies a, iex_strategy_work_items b
where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
select upper(b.category_type)
into l_dunning_method
from iex_strategy_work_items a, IEX_STRY_TEMP_WORK_ITEMS_VL b
where a.work_item_template_id = b.work_item_temp_id
and a.work_item_id = l_workitem_id
and b.work_type = 'AUTOMATIC';
select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
into l_DefaultStrategyLevel
from iex_strategies
where strategy_id = l_strategy_id;
select campaign_sched_id into l_campaign_sched_id from iex_delinquencies_all
where delinquency_id = l_delinquency_id;
l_dunning_rec_upd_old.last_update_date := sysdate;
IEX_DUNNING_PVT.Update_DUNNING(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_dunning_rec => l_dunning_rec_upd_old
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
l_dunning_rec_upd.last_update_date := sysdate;
IEX_DUNNING_PVT.Update_DUNNING(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_dunning_rec => l_dunning_rec_upd
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);