[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
**Appends to a message the name of the object anf the operation (insert, update ,delete)
*/
PROCEDURE AddfailMsg
( p_object IN VARCHAR2,
p_operation IN VARCHAR2 ) IS
BEGIN
fnd_message.set_name('IEX', 'IEX_FAILED_OPERATION');
SELECT strategy_template_id
FROM iex_strategies
WHERE strategy_id =c_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 =c_template_id
and swit.work_item_template_id(+) =sxref.work_item_temp_id
and swit.strategy_id(+) =c_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 =c_strategy_id
and swit.work_item_template_id(+) =susit.work_item_temp_id
and swit.strategy_id(+) =c_strategy_id
order by order_by;
update all the work_items status to depending on the status passed
update the stragey status to depending on the status passed
**/
PROCEDURE CLOSE_STRY_AND_WITEMS
(p_api_version IN NUMBER,
p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_strategy_id IN NUMBER,
p_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_api_version CONSTANT NUMBER := 1.0;
SELECT work_item_id, object_version_number
FROM iex_strategy_work_items
WHERE strategy_id = p_strategy_id
and status_code IN ('OPEN','PRE-WAIT'); -- Changed for bug#7703351 by PNAVEENK on 22-1-2009
iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'Before Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items');
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('CLOSE_STRY_AND_WITEMS: ' || 'After Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items '||
'and Status =>'||l_return_status);
p_operation => 'UPDATE' );
IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'End of work items update ');
select object_version_number INTO l_object_version_number
FROM iex_strategies
where strategy_id =p_strategy_id;
iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
IEX_STRATEGY_PVT.Update_strategy(
P_Api_Version_Number =>l_api_version_number,
P_strategy_Rec =>l_strategy_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('CLOSE_STRY_AND_WITEMS: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
'and Status =>'||l_return_status);
p_operation => 'UPDATE' );
update the stragey status to depending on the status passed
**/
PROCEDURE CLOSE_STRATEGY
(p_api_version IN NUMBER,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_strategy_id IN NUMBER,
p_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_api_version NUMBER := 1.0;
SELECT work_item_id, object_version_number
FROM iex_strategy_work_items
WHERE strategy_id = p_strategy_id;
select object_version_number INTO l_object_version_number
FROM iex_strategies
where strategy_id =p_strategy_id;
iex_debug_pub.logMessage('CLOSE_STRATEGY: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
IEX_STRATEGY_PVT.Update_strategy(
P_Api_Version_Number =>2.0,
P_Init_Msg_List =>FND_API.G_TRUE,
p_commit =>FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
P_strategy_Rec =>l_strategy_Rec,
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('CLOSE_STRATEGY: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
'and Status =>'||l_return_status);
p_operation => 'UPDATE' );
update all the work_item status to depending on the status passed
**/
PROCEDURE UPDATE_WORK_ITEM
(p_api_version IN NUMBER,
p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_work_item_id IN NUMBER,
p_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name VARCHAR2(100) := 'UPDATE_WORK_ITEM';
SELECT object_version_number
FROM iex_strategy_work_items
WHERE work_item_id =p_work_item_id;
SAVEPOINT UPDATE_WORK_ITEM_PUB;
IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after init');
IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'Required Parameter p_work_item_id is invalid');
IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after p_work_item_id check');
IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'Required Parameter p_status is invalid');
IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after p_status check');
iex_debug_pub.logMessage('UPDATE_WORK_ITEM: ' || 'Before Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items');
iex_debug_pub.logMessage('UPDATE_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_WORK_ITEM: ' || 'After Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items '||
'and Status =>'||l_return_status);
p_operation => 'UPDATE' );
IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'End of work items update ');
ROLLBACK TO UPDATE_WORK_ITEM_PUB;
ROLLBACK TO UPDATE_WORK_ITEM_PUB;
ROLLBACK TO UPDATE_WORK_ITEM_PUB;
END UPDATE_WORK_ITEM;
* update next work item in the strategy table
* when a work item is created
**/
PROCEDURE UPDATE_NEXT_WORK_ITEM
(p_api_version IN NUMBER,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_work_item_id IN NUMBER,
p_strategy_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version NUMBER := 1.0;
l_api_name VARCHAR2(100) := 'UPDATE_NEXT_WORK_ITEM';
SAVEPOINT UPDATE_NEXT_WORK_ITEM_PUB;
IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_strategy_id is invalid');
IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'after p_strategy_id check');
IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_work_item_id is invalid');
IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'after p_work_item_id check');
select object_version_number INTO l_object_version_number
FROM iex_strategies
where strategy_id =p_strategy_id;
IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_stratgey_id is invalid');
iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || '---------------------------------');
IEX_STRATEGY_PVT.Update_strategy(
P_Api_Version_Number =>2.0,
P_Init_Msg_List =>FND_API.G_TRUE,
p_commit =>FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
P_strategy_Rec =>l_strategy_Rec,
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_NEXT_WORK_ITEM: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
'and Status =>'||l_return_status);
p_operation => 'UPDATE' );
ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
END UPDATE_NEXT_WORK_ITEM;
SELECT count(*) into x_work_item_done
FROM iex_strategy_work_items
WHERE strategy_id = p_strategy_id
and status_code not in ('CANCELLED','COMPLETE');
select sysdate into r_date from dual; -- default to sysdate;
select conversion_rate into l_conversion from mtl_uom_conversions
--Use the profile 'unit of measure class' value if it is set at application level, else use it from site level
/* where UOM_code = l_UOM and uom_class = (select fnd_profile.value('JTF_TIME_UOM_CLASS') from dual) */
where UOM_code = l_UOM and uom_class = l_jtf_time_uom_class
--End of Bug 7434190 22-Jan-2009 barathsr
and inventory_item_id = 0;
select p_date + l_conversion * l_unit / 24 into r_date from dual;
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = null,
SCHEDULE_START = null,
SCHEDULE_END = null,
WORK_TYPE = null,
CATEGORY_TYPE = null,
PRIORITY_TYPE = null,
WKITEM_RESOURCE_ID = null,
STRATEGY_ID = null,
STRATEGY_TEMPLATE_ID = null,
WORK_ITEM_TEMPLATE_ID = null,
STATUS_CODE = null,
START_TIME = null,
END_TIME = null,
WORK_ITEM_ORDER = null,
WKITEM_ESCALATED_YN = null
WHERE PARTY_ID = p_object_id;
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = null,
SCHEDULE_START = null,
SCHEDULE_END = null,
WORK_TYPE = null,
CATEGORY_TYPE = null,
PRIORITY_TYPE = null,
WKITEM_RESOURCE_ID = null,
STRATEGY_ID = null,
STRATEGY_TEMPLATE_ID = null,
WORK_ITEM_TEMPLATE_ID = null,
STATUS_CODE = null,
START_TIME = null,
END_TIME = null,
WORK_ITEM_ORDER = null,
WKITEM_ESCALATED_YN = null
WHERE CUST_ACCOUNT_ID = p_object_id;
UPDATE IEX_DLN_UWQ_SUMMARY
SET WORK_ITEM_ID = null,
SCHEDULE_START = null,
SCHEDULE_END = null,
WORK_TYPE = null,
CATEGORY_TYPE = null,
PRIORITY_TYPE = null,
WKITEM_RESOURCE_ID = null,
STRATEGY_ID = null,
STRATEGY_TEMPLATE_ID = null,
WORK_ITEM_TEMPLATE_ID = null,
STATUS_CODE = null,
START_TIME = null,
END_TIME = null,
WORK_ITEM_ORDER = null,
WKITEM_ESCALATED_YN = null
WHERE SITE_USE_ID = p_object_id;
select strat.jtf_object_id,
strat.jtf_object_type,
wkitem.WORK_ITEM_ID,
wkitem.schedule_start schedule_start,
wkitem.schedule_end schedule_end,
stry_temp_wkitem.category_type category,
stry_temp_wkitem.WORK_TYPE,
stry_temp_wkitem.PRIORITY_TYPE,
wkitem.resource_id,
wkitem.strategy_id,
strat.strategy_template_id,
wkitem.work_item_template_id,
wkitem.status_code workitem_status,
strat.status_code startegy_status,
wkitem.creation_date start_time,
wkitem.execute_end end_time, -- snuthala 28/08/2008 bug #6745580
wkitem.work_item_order wkitem_order,
wkitem.escalated_yn escalated_yn
from iex_strategies strat,
iex_strategy_work_items wkitem,
iex_stry_temp_work_items_b stry_temp_wkitem
where wkitem.work_item_id=p_work_item_id
AND wkitem.strategy_id = strat.strategy_id
AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id;
update iex_dln_uwq_summary
set work_item_id = l_work_item_id,
schedule_start = l_schedule_start,
schedule_end = l_schedule_end,
work_type = l_work_type,
category_type = l_category_type,
priority_type = l_priority_type,
wkitem_resource_id = l_wkitem_resource_id,
strategy_id = l_strategy_id,
strategy_template_id = l_strategy_template_id,
work_item_template_id = l_work_item_template_id,
status_code = l_workitem_status,
start_time = l_start_time,
end_time = l_end_time,
work_item_order = l_work_item_order,
wkitem_escalated_yn = l_escalated_yn
where party_id = l_jtf_object_id;
update iex_dln_uwq_summary
set work_item_id = l_work_item_id,
schedule_start = l_schedule_start,
schedule_end = l_schedule_end,
work_type = l_work_type,
category_type = l_category_type,
priority_type = l_priority_type,
wkitem_resource_id = l_wkitem_resource_id,
strategy_id = l_strategy_id,
strategy_template_id = l_strategy_template_id,
work_item_template_id = l_work_item_template_id,
status_code = l_workitem_status,
start_time = l_start_time,
end_time = l_end_time,
work_item_order = l_work_item_order,
wkitem_escalated_yn = l_escalated_yn
where cust_account_id = l_jtf_object_id;
update iex_dln_uwq_summary
set work_item_id = l_work_item_id,
schedule_start = l_schedule_start,
schedule_end = l_schedule_end,
work_type = l_work_type,
category_type = l_category_type,
priority_type = l_priority_type,
wkitem_resource_id = l_wkitem_resource_id,
strategy_id = l_strategy_id,
strategy_template_id = l_strategy_template_id,
work_item_template_id = l_work_item_template_id,
status_code = l_workitem_status,
start_time = l_start_time,
end_time = l_end_time,
work_item_order = l_work_item_order,
wkitem_escalated_yn = l_escalated_yn
where site_use_id = l_jtf_object_id;
select status_code
from iex_strategies
where strategy_id = p_strategy_id ;
select delinquency_id,object_id,object_type
from iex_strategies
where strategy_id = p_strategy_id ;
/** update work item and call send signal
* if send signal fails, roolback the work item
**/
/*
PROCEDURE UPDATE_AND_SENDSIGNAL( P_strategy_work_item_Rec IN
iex_strategy_work_items_pvt.strategy_work_item_Rec_Type,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)IS
l_return_status VARCHAR2(1);
SAVEPOINT UPDATE_AND_SENDSIGNAL;
iex_strategy_work_items_pvt.update_strategy_work_items(
p_api_version_number => 2.0,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_commit => p_commit,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_strategy_work_item_rec => p_strategy_work_item_rec,
xo_object_version_number => l_object_version_number);
ROLLBACK TO UPDATE_AND_SENDSIGNAL;
IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');
select count(*) INTO v_result from iex_strategies
where strategy_id =p_strategy_work_item_rec.strategy_id
and next_work_item_id =p_strategy_work_item_rec.work_item_id
and status_code ='OPEN';
IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');
rollback to UPDATE_AND_SENDSIGNAL;
IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');
ROLLBACK TO UPDATE_AND_SENDSIGNAL;
IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
END UPDATE_AND_SENDSIGNAL;