[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.workflow_item_type itemtype,
b.work_item_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 b.strategy_id =p_strategy_id
and a.workflow_item_type IS NOT NULL;
select a.work_item_id
from iex_strategy_work_items a,
iex_stry_temp_work_items_vl b
where a.strategy_id = p_strategy_id
and a.work_item_template_id =b.work_item_temp_id
and (b.fulfil_temp_id IS NOT NULL or b.xdo_template_id IS NOT NULL) ;
select distinct item_key from wf_item_attr_values_ondemand
where name = 'WRITEOFF_ID' and text_value = c_id and item_type = 'IEXWRREQ';
select jtf_object_type,jtf_object_id from iex_strategies
where strategy_id = c_strategy_id;
* this is because custom and fulfillment work flows updates
* the work item and if the strategy is changed back from ONHOLD to OPEN,
* we need to resume the process.
**/
FUNCTION CHECK_WORK_ITEM_OPEN(p_strategy_id IN NUMBER)
RETURN NUMBER IS
v_result NUMBER;
select count(*)
INTO v_result
from iex_strategies a,
ieX_strategy_work_items b
where a.strategy_id =p_strategy_id
and a.strategy_id =b.strategy_id
and a.next_work_item_id =b.work_item_id
and b.status_code ='OPEN'
and a.status_code ='OPEN';
select count(*) INTO v_result from iex_strategies
where strategy_id =p_strategy_id
and next_work_item_id =p_work_item_id
and status_code ='OPEN';
* update work item status
*/
PROCEDURE update_workitem_Status(p_work_item_id IN NUMBER,
p_status IN VARCHAR2 )IS
l_api_version NUMBER := 1.0;
iex_debug_pub.logmessage ('**** BEGIN update_workitem_Status ************');
IEX_STRY_UTL_PUB.UPDATE_WORK_ITEM (p_api_version => l_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
p_work_item_id => p_work_item_id,
p_status => p_status);
iex_debug_pub.logmessage ('**** END update_workitem_Status ************');
END update_workitem_Status;
Select user_name,source_name -- bug 6717880/7170165 by Ehuh
from jtf_rs_resource_extns
where resource_id =p_resource_id;
SELECT b.user_id, b.user_name , b.resource_id
FROM JTF_RS_RESOURCE_EXTNS a
, JTF_RS_RESOURCE_EXTNS b
WHERE b.source_id = a.source_mgr_id
AND a.resource_id = p_resource_id;
select source_name from JTF_RS_RESOURCE_EXTNS
where resource_id = p_resource_id;
select closure_time_limit, closure_time_uom,
schedule_wait, schedule_uom,
optional_yn,escalate_yn,
option_wait_time, option_wait_time_uom
from iex_stry_temp_work_items_vl
where work_item_temp_id =p_work_item_temp_id;
select count(person_id)
from per_competence_elements
where competence_id = l_competence_id
and person_id = l_person_id
and trunc(NVL(effective_date_to,SYSDATE)) >= trunc(sysdate)
and trunc(effective_date_from) <= trunc(sysdate) ;
/*select sxref.strategy_temp_id TEMPLATE_ID,
sxref.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
sxref.work_item_order ORDER_BY
,nvl(swit.status_code,'NOTCREATED') STATUS
,swit.work_item_id WORK_ITEM_ID
,swit.strategy_id STRATEGY_ID
from iex_strategy_work_temp_xref sxref
,iex_strategy_work_items swit
where sxref.strategy_temp_id =p_template_id
and swit.work_item_template_id(+) =sxref.work_item_temp_id
and swit.strategy_id(+) =p_strategy_id
union all
select susit.strategy_template_id TEMPLATE_ID,
susit.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
susit.work_item_order ORDER_BY
,nvl(swit.status_code,'NOTCREATED') STATUS
,swit.work_item_id WORK_ITEM_ID
,susit.strategy_id STRATEGY_ID
from iex_strategy_user_items susit
,iex_strategy_work_items swit
where susit.strategy_id =p_strategy_id
and swit.work_item_template_id(+) =susit.work_item_temp_id
and swit.strategy_id(+) =p_strategy_id
order by order_by;
SELECT wkitem.strategy_id STRATEGY_ID,
wkitem.strategy_temp_id TEMPLATE_ID,
wkitem.work_item_order ORDER_BY,
wkitem.work_item_id WORK_ITEM_ID,
wkitem.work_item_template_id WORK_ITEM_TEMPLATE_ID,
wkitem.status_code STATUS
from iex_strategy_work_items wkitem,
iex_stry_temp_work_items_vl stry_temp_wkitem
WHERE
wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
and wkitem.strategy_id =p_strategy_id
--to be created work items
union all
SELECT stry.STRATEGY_ID STRATEGY_ID
, xref.STRATEGY_TEMP_ID TEMPLATE_ID
, xref.WORK_ITEM_ORDER ORDER_BY
, TO_NUMBER(NULL) WORK_ITEM_ID
, xref.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
, 'NOTCREATED' STATUS
FROM IEX_STRATEGIES stry
, IEX_STRATEGY_WORK_TEMP_XREF xref
, IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
WHERE stry.STRATEGY_TEMPLATE_ID = xref.STRATEGY_TEMP_ID
and xref.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
and stry.strategy_id =p_strategy_id
--not in workitems table
AND not exists ( select 'x' from iex_strategy_work_items wkitem where
wkitem.strategy_id = stry.strategy_id
and wkitem.work_item_template_id = xref.work_item_temp_id
and wkitem.work_item_order = xref.work_item_order
and wkitem.strategy_id =p_strategy_id
)
----skip workitems which is status-ed SKIP
and not exists ( select 'x' from iex_strategy_user_items uitems where
uitems.strategy_id = stry.strategy_id and
uitems.work_item_temp_id = xref.work_item_temp_id and
uitems.work_item_order = xref.work_item_order and
uitems.operation = 'SKIP'
and uitems.strategy_id =p_strategy_id
)
and (xref.work_item_order > (select max(wkitem_order) from iex_work_item_bali_v
where strategy_id = p_strategy_id and start_time is not null)
or (select count(*) from iex_work_item_bali_v where strategy_id = p_strategy_id ) = 0
) -- later on assignment of prior work items, and case of initial creation of wkitem
-- get all user items
union all
SELECT stry.STRATEGY_ID STRATEGY_ID
, uitem.STRATEGY_TEMPLATE_ID TEMPLATE_ID
, uitem.WORK_ITEM_ORDER ORDER_BY
, TO_NUMBER(NULL) WORK_ITEM_ID
, uitem.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
, uitem.operation STATUS
FROM IEX_STRATEGIES stry
, IEX_STRATEGY_user_items uitem
, IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
WHERE stry.STRATEGY_ID = uitem.STRATEGY_ID
and uitem.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
and stry.strategy_id =p_strategy_id
AND not exists
-- exclude useritem whoch is already a workitem
( select 'x' from iex_strategy_work_items wkitem
where wkitem.strategy_id = stry.strategy_id
and wkitem.work_item_template_id = uitem.work_item_temp_id
and uitem.work_item_order = wkitem.work_item_order
and wkitem.strategy_id =p_strategy_id)
order by ORDER_BY;
SELECT competence_id from iex_stry_temp_work_items_vl
where work_item_temp_id =p_work_item_temp_id;
select account_number,location from iex_strategies_bali_v
where strategy_id = p_str_id;
SELECT competence_id from iex_strategy_work_skills
where work_item_temp_id =p_work_item_temp_id;
SELECT callback_wait, callback_uom,optional_yn,option_wait_time,
option_wait_time_uom,escalate_yn, notify_yn,workflow_item_type,work_type,
category_type,fulfil_temp_id
from iex_stry_temp_work_items_vl
where work_item_temp_id =p_work_item_temp_id;
select jtf_object_type, jtf_object_id, cust_account_id,customer_site_use_id
into l_object_code, l_object_id, l_cust_account_id,l_siteuse_id
from iex_strategies
where strategy_id = p_strategy_id;
iex_debug_pub.logmessage ('populate_schedule_times: More than one row for this Strategy. Exception on selecting strategy!!!!!');
iex_debug_pub.logMessage ('populate_schedule_times: ' || 'ready to insert work items');
IEX_STRY_UTL_PUB.UPDATE_NEXT_WORK_ITEM
(p_api_version => l_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
p_strategy_id => p_strategy_id,
p_work_item_id => x_work_item_id);
select a.name,b.status_code into l_workitem_name , l_wkitem_status ----bug#5874874 gnramasa
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 = x_work_item_id;
--Update the UWQ summary table after creating workitem in OPEN status.
if l_wkitem_status='OPEN' then
IEX_STRY_UTL_PUB.refresh_uwq_str_summ(x_work_item_id);
SELECT application_id
FROM fnd_application_vl
WHERE application_short_name = x_short_name;
SELECT msg.message_number
FROM fnd_new_messages msg, fnd_languages_vl lng
WHERE msg.message_name = x_msg
and msg.application_id = x_id
and lng.LANGUAGE_CODE = msg.language_code
and lng.language_id = x_lang_id;
select strategy_template_id from iex_strategies
where strategy_id =p_strategy_id;
/*select sxref.strategy_temp_id TEMPLATE_ID,
sxref.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
sxref.work_item_order ORDER_BY
,nvl(swit.status_code,'NOTCREATED') STATUS
,swit.work_item_id WORK_ITEM_ID
,swit.strategy_id STRATEGY_ID
from iex_strategy_work_temp_xref sxref
,iex_strategy_work_items swit
where sxref.strategy_temp_id =p_template_id
and swit.work_item_template_id(+) =sxref.work_item_temp_id
and swit.strategy_id(+) =p_strategy_id
union all
select susit.strategy_template_id TEMPLATE_ID,
susit.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
susit.work_item_order ORDER_BY
,nvl(swit.status_code,'NOTCREATED') STATUS
,swit.work_item_id WORK_ITEM_ID
,susit.strategy_id STRATEGY_ID
from iex_strategy_user_items susit
,iex_strategy_work_items swit
where susit.strategy_id =p_strategy_id
and swit.work_item_template_id(+) =susit.work_item_temp_id
and swit.strategy_id(+) =p_strategy_id
order by order_by;
SELECT wkitem.strategy_id STRATEGY_ID,
wkitem.strategy_temp_id TEMPLATE_ID,
wkitem.work_item_order ORDER_BY,
wkitem.work_item_id WORK_ITEM_ID,
wkitem.work_item_template_id WORK_ITEM_TEMPLATE_ID,
wkitem.status_code STATUS
from iex_strategy_work_items wkitem,
iex_stry_temp_work_items_vl stry_temp_wkitem
WHERE
wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
and wkitem.strategy_id =p_strategy_id
--to be created work items
union all
SELECT stry.STRATEGY_ID STRATEGY_ID
, xref.STRATEGY_TEMP_ID TEMPLATE_ID
, xref.WORK_ITEM_ORDER ORDER_BY
, TO_NUMBER(NULL) WORK_ITEM_ID
, xref.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
, 'NOTCREATED' STATUS
FROM IEX_STRATEGIES stry
, IEX_STRATEGY_WORK_TEMP_XREF xref
, IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
WHERE stry.STRATEGY_TEMPLATE_ID = xref.STRATEGY_TEMP_ID
and xref.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
and stry.strategy_id =p_strategy_id
--not in workitems table
AND not exists ( select 'x' from iex_strategy_work_items wkitem where
wkitem.strategy_id = stry.strategy_id
and wkitem.work_item_template_id = xref.work_item_temp_id
and wkitem.work_item_order = xref.work_item_order
and wkitem.strategy_id =p_strategy_id
)
----skip workitems which is status-ed SKIP
and not exists ( select 'x' from iex_strategy_user_items uitems where
uitems.strategy_id = stry.strategy_id and
uitems.work_item_temp_id = xref.work_item_temp_id and
uitems.work_item_order = xref.work_item_order and
uitems.operation = 'SKIP'
and uitems.strategy_id =p_strategy_id
)
and (xref.work_item_order > (select max(wkitem_order) from iex_work_item_bali_v
where strategy_id = p_strategy_id and start_time is not null)
or (select count(*) from iex_work_item_bali_v where strategy_id = p_strategy_id ) = 0
) -- later on assignment of prior work items, and case of initial creation of wkitem
-- get all user items
union all
SELECT stry.STRATEGY_ID STRATEGY_ID
, uitem.STRATEGY_TEMPLATE_ID TEMPLATE_ID
, uitem.WORK_ITEM_ORDER ORDER_BY
, TO_NUMBER(NULL) WORK_ITEM_ID
, uitem.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
, uitem.operation STATUS
FROM IEX_STRATEGIES stry
, IEX_STRATEGY_user_items uitem
, IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
WHERE stry.STRATEGY_ID = uitem.STRATEGY_ID
and uitem.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
and stry.strategy_id =p_strategy_id
AND not exists
-- exclude useritem whoch is already a workitem
( select 'x' from iex_strategy_work_items wkitem
where wkitem.strategy_id = stry.strategy_id
and wkitem.work_item_template_id = uitem.work_item_temp_id
and uitem.work_item_order = wkitem.work_item_order
and wkitem.strategy_id =p_strategy_id)
order by ORDER_BY;
* if the update fails , go and wait
* for the signal again
**/
procedure close_strategy(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out NOCOPY varchar2) IS
l_strategy_id NUMBER ;
* update the attribute work_item_id in the workflow with the
* create workitem_id.
**/
procedure create_next_work_item(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out NOCOPY varchar2) IS
l_api_version NUMBER := 1.0;
select strategy_template_id from iex_strategies
where strategy_id =p_strategy_id;
*of custom or fulfillment wf's.then do nothing ,else update workitem and resume process
* If the strategy is ON-HOLD' or 'OPEN' THEN do not resume process, the agent will update the
*work item and update the strategy to 'OPEN' .
*04/08/02
* abort fulfilment and custom workflow when agent updates fulfilment or custom workitem
*04/16/02
*if the strategy status is 'OPEN' THEN resume process and it is ON-HOLD then do not resume,
* remain suspended
04/18/02
-- if the strategy is changed from on hold to OPEN and if the current work item has
-- aleady completed ( changed the status from 'OPEN to CCANCELLED, CLOSED, TIMEOUT),
-- then resume the process, othere wise remain suspended.
**/
--04/26/02 check if activity label is null before resuming process
-- agent is not going to do update, so send signal will do it.
--07/31/02 --abort the strategy workflow if the workflow is active or
--in error
procedure send_signal(
process in varchar2 ,
strategy_id in varchar2,
status in VARCHAR2,
work_item_id in number ,
signal_source in varchar2 ) IS
l_result VARCHAR2(100);
iex_debug_pub.logmessage('wait_signal: ' || 'agent has not updated the work item');
iex_debug_pub.logmessage('wait_signal: ' || 'agent has updated the work item');
SELECT STATUS_CODE INTO l_status_code FROM IEX_STRATEGY_WORK_ITEMS WHERE WORK_ITEM_ID = l_work_item_id;
UPDATE IEX_STRATEGY_WORK_ITEMS SET STATUS_CODE = 'OPEN' WHERE WORK_ITEM_ID = l_work_item_id;
--Update the UWQ summary table after workitem's status changes to OPEN from PRE-WAIT.
IEX_STRY_UTL_PUB.refresh_uwq_str_summ(l_work_item_id);
select decode(workflow_item_type,null,'N','Y'),
workflow_item_type INTO l_result,l_custom_itemtype
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select decode(fulfil_temp_id,null,'N',wf_yes),fulfil_temp_id
INTO l_result,l_fulfil_temp_id
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select decode(xdo_template_id,null,'N',wf_yes),xdo_template_id
INTO l_result,l_fulfil_temp_id
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select decode(fulfil_temp_id,null,'N',wf_yes),fulfil_temp_id
INTO l_result,l_fulfil_temp_id
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select decode(xdo_template_id,null,'N',wf_yes),xdo_template_id
INTO l_result,l_fulfil_temp_id
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select sysdate into l_execution_time from dual;
select a.post_execution_wait, a.execution_time_uom
into l_post_execution_wait, l_execution_time_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 = l_work_item_id;
select a.pre_execution_wait, a.schedule_uom
into l_pre_execution_wait, l_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 = l_work_item_id;
select sysdate + 1
into l_restart_create_wi_time
from dual;
select nvl(optional_yn,'N') INTO l_result
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select nvl(escalate_yn,'N') INTO l_result
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select nvl(notify_yn,'N') INTO l_result
from iex_stry_temp_work_items_vl
where work_item_temp_id =l_work_item_temp_id;
select sysdate+(23/24)
into l_execution_time
from dual;
select decode(count(*),0,'N','Y') into l_result
from iex_Strategies
where strategy_id =l_strategy_id
and status_code ='ONHOLD';
select count(*)
into l_timeout_wi
from iex_strategy_work_items
where work_item_id=l_work_item_id
and status_code='TIMEOUT';
procedure UPDATE_WORK_ITEM(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out NOCOPY varchar2) IS
l_api_version NUMBER := 1.0;
iex_debug_pub.logmessage ('**** START UPDATE_WORK_ITEM ************');
iex_debug_pub.logmessage('UPDATE_WORK_ITEM: ' || 'value of workitem id '||l_work_item_id);
IEX_STRY_UTL_PUB.UPDATE_WORK_ITEM (p_api_version => l_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
p_work_item_id => l_work_item_id,
p_status => 'TIMEOUT');
iex_debug_pub.logmessage ('**** END UPDATE_WORK_ITEM ************');
iex_debug_pub.logmessage('UPDATE_WORK_ITEM: ' || 'error message is ' || l_error);
END UPDATE_WORK_ITEM;
select party_name into l_party_name from hz_parties where party_id = l_party_id;
select count(person_id)
from per_competence_elements
where competence_id =l_competence_id
and person_id =l_person_id
and trunc(NVL(effective_date_to,SYSDATE)) >= trunc(sysdate)
and trunc(effective_date_from) <= trunc(sysdate) ;
select count(person_id)
from per_competence_elements
where competence_id =l_competence_id
and person_id =l_person_id
and trunc(NVL(effective_date_to,SYSDATE)) >= trunc(sysdate)
and trunc(effective_date_from) <= trunc(sysdate) ;
procedure update_work_item_to_open(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out NOCOPY varchar2) IS
L_STATUS_CODE varchar2(30);
iex_debug_pub.logmessage ('**** START update_work_item_to_open ************');
SELECT STATUS_CODE INTO l_status_code FROM IEX_STRATEGY_WORK_ITEMS WHERE WORK_ITEM_ID = l_work_item_id;
UPDATE IEX_STRATEGY_WORK_ITEMS SET STATUS_CODE = 'OPEN' WHERE WORK_ITEM_ID = l_work_item_id;
iex_debug_pub.logmessage ('**** END update_work_item_to_open ************');
iex_debug_pub.logmessage ('**** EXCEPTION update_work_item_to_open ************');
procedure UPDATE_ESC_FLAG(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out NOCOPY varchar2) IS
l_api_version_number NUMBER := 2.0;
SELECT status_code, object_version_number
FROM iex_strategy_work_items
WHERE work_item_id = p_work_item_id;
iex_debug_pub.logmessage ('**** START UPDATE_ESCALATION_FLAG ************');
iex_debug_pub.logmessage('UPDATE_ESC_FLAG: ' || 'value of workitem id '||l_work_item_id);
iex_debug_pub.logmessage ('UPDATE_ESC_FLAG: ' || 'Updating the work item..');
IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items(
P_Api_Version_Number =>l_api_version_number,
P_strategy_work_item_Rec =>l_strategy_work_item_Rec,
P_Init_Msg_List =>FND_API.G_TRUE,
p_commit =>FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
iex_debug_pub.logmessage ('UPDATE_ESC_FLAG: Return status = ' || l_return_status);
iex_debug_pub.logmessage('UPDATE_ESC_FLAG: Refreshing UWQ Summary..');
iex_debug_pub.logmessage('UPDATE_ESC_FLAG:Exception when refreshing UWQ Summary: '||SQLERRM);
iex_debug_pub.logmessage ('**** END UPDATE_ESC_FLAG ************');
iex_debug_pub.logmessage('UPDATE_ESC_FLAG: ' || 'error message is ' || l_error);
wf_core.context('IEX_STRATEGY_WF','UPDATE_ESC_FLAG',itemtype,
itemkey,to_char(actid),funcmode,l_error);
wf_core.context('IEX_STRATEGY_WF','UPDATE_ESC_FLAG',itemtype,
itemkey,to_char(actid),funcmode);
END UPDATE_ESC_FLAG;