[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 org_id from iex_strategies where strategy_id = p_strategy_id;
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 org_id
into v_org_id
from iex_delinquencies_all
where delinquency_id=l_delinquency_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
select org_id
into v_org_id
from iex_delinquencies_all
where customer_site_use_id=l_customer_site_use_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
select org_id
into v_org_id
from iex_delinquencies_all
where cust_account_id=l_cust_account_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
select org_id
into v_org_id
from iex_delinquencies_all
where party_cust_id= l_party_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
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 upper(b.category_type),
nvl(include_disputed_items,'N') -- bug 14772139
into l_dunning_method,
l_include_disputed_items -- bug 14772139
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';-- bug 14772139
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
);
IEX_DUNNING_PVT.INSERT_DUNNING_TRANSACTION(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_delinquencies_tbl => l_del_tbl
, p_ag_dn_xref_id => 0
, p_dunning_id => l_dunning_id
, p_correspondence_date => sysdate
, p_running_level => l_DefaultStrategyLevel
, p_grace_days => 0
, p_include_dispute_items => l_include_disputed_items -- 'Y' bug 14772139
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_workitem_id => l_workitem_id); -- bug 14772139
update iex_xml_request_histories
set status='CANCELLED'
where object_type='IEX_STRATEGY'
and status<>'CANCELLED'
and xml_request_id in (select xml_request_id
from iex_dunnings
where object_type='IEX_STRATEGY'
and object_id=l_workitem_id);
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_all 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_all 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' ;
select party_id from iex_strategies
where strategy_id = p_strategy_id;
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_all
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_all 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_all 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_all 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 org_id from iex_strategies where strategy_id = p_strategy_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 org_id
into v_org_id
from iex_delinquencies_all
where delinquency_id=l_delinquency_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
select org_id
into v_org_id
from iex_delinquencies_all
where customer_site_use_id=l_customer_site_use_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
select org_id
into v_org_id
from iex_delinquencies_all
where cust_account_id=l_cust_account_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
select org_id
into v_org_id
from iex_delinquencies_all
where party_cust_id= l_party_id
AND status = 'DELINQUENT'
and org_id is not null
and rownum<=1;
/*select org_id
into v_org_id
from iex_delinquencies_all
where cust_account_id=l_cust_account_id
and org_id is not null
and rownum<=1;*/
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 upper(b.category_type),
nvl(include_disputed_items,'N') -- bug 14772139
into l_dunning_method,
l_include_disputed_items -- bug 14772139
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';-- bug 14772139
select max(dunning_id) into l_parent_dunning_id from iex_dunnings where object_id = p_work_item_id; -- added to fix 16433137 SNUTHALA MAR/08/2013
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
);
IEX_DUNNING_PVT.INSERT_DUNNING_TRANSACTION(
p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_delinquencies_tbl => l_del_tbl
, p_ag_dn_xref_id => 0
, p_dunning_id => l_dunning_id
, p_correspondence_date => sysdate
, p_running_level => l_DefaultStrategyLevel
, p_grace_days => 0
, p_include_dispute_items => l_include_disputed_items -- 'Y' bug 14772139
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_workitem_id => l_workitem_id); -- bug 14772139
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_TRUE
, 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
);
select dun.object_id
into l_work_item_id
from iex_dunnings dun,
iex_strategy_work_items wi
where dun.object_id=wi.work_item_id
and dun.object_type='IEX_STRATEGY'
and wi.status_code in ('OPEN','INERROR_CHECK_NOTIFY')
and dun.xml_request_id=p_xml_request_id;
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 => 'INERROR_CHECK_NOTIFY',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
select wi.work_item_id work_item_id
from iex_dunnings dun,
iex_strategy_work_items wi,
iex_xml_request_histories xrh
where dun.object_id=wi.work_item_id
and xrh.object_type='IEX_STRATEGY'
and xrh.creation_date>=nvl(l_from_date,xrh.creation_date)
and dun.xml_request_id=xrh.xml_request_id
and xrh.status not in ('SUCCESSFUL','SUCCESSFUL WITH WARNINGS','OPEN','CANCELLED')
and wi.status_code in ('INERROR_CHECK_NOTIFY');
select notification_id from wf_notifications
where message_type='IEXSTFFM'
and MESSAGE_NAME='SEND FAILER MESSAGE'
and status='OPEN'
AND context like p_context; -- 'IEXSTFFM:14515%' ;
update iex_xml_request_histories
set status='OTHER PROCESSING FAILURE',
failure_reason='Request status timed out'
where object_type='IEX_STRATEGY'
--and status<>'CANCELLED'
and status in ('IN PROCESS','XMLDATA','XMLDOC')
and xml_request_id in (select xml_request_id
from iex_dunnings
where object_type='IEX_STRATEGY'
and object_id=l_work_item_id);
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 => 'INERROR_CHECK_NOTIFY',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
select failure_reason into l_failure_reason from iex_xml_request_histories where xml_request_id = (select max(xml_request_id) from
iex_dunnings
where object_type='IEX_STRATEGY'
and object_id=l_work_item_id)
and failure_reason is not null;