[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT task_number
INTO l_escalation_number
FROM jtf_tasks_vl
WHERE task_id = l_escalation_id;
UPDATE jtf_tasks_b
SET source_object_type_code = jtf_ec_pub.g_escalation_code,
source_object_id = l_escalation_id,
source_object_name = l_escalation_number
WHERE task_id = l_escalation_id;
PROCEDURE update_escalation (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_object_version_number IN OUT NOCOPY NUMBER,
p_escalation_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_escalation_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_escalation_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_description IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_escalation_status_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_escalation_status_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_open_date IN DATE DEFAULT fnd_api.g_miss_date,
p_close_date IN DATE DEFAULT fnd_api.g_miss_date,
p_escalation_priority_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_escalation_priority_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_owner_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_escalation_owner_type_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_owner_territory_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_assigned_by_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_assigned_by_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_customer_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_customer_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_cust_account_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_cust_account_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_address_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_address_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_target_date IN DATE DEFAULT fnd_api.g_miss_date,
/* p_timezone_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_timezone_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char,*/
p_reason_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_private_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_publish_flag IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
p_workflow_process_id IN NUMBER DEFAULT fnd_api.g_miss_num,
p_escalation_level IN VARCHAR2 DEFAULT fnd_api.g_miss_char,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
)
IS
l_api_version CONSTANT NUMBER := 1.0;
:= 'UPDATE_ESCALATION';
CURSOR c_escalation_update (l_escalation_id IN NUMBER)
IS
SELECT DECODE (
p_escalation_id,
fnd_api.g_miss_num,
task_id,
p_escalation_id
) escalation_id,
DECODE (
p_escalation_number,
fnd_api.g_miss_char,
task_number,
p_escalation_number
) escalation_number,
DECODE (
p_escalation_name,
fnd_api.g_miss_char,
task_name,
p_escalation_name
) escalation_name,
DECODE (
p_description,
fnd_api.g_miss_char,
description,
p_description
) description,
DECODE (
p_escalation_status_id,
fnd_api.g_miss_num,
task_status_id,
p_escalation_status_id
) escalation_status_id,
DECODE (
p_escalation_priority_id,
fnd_api.g_miss_num,
task_priority_id,
p_escalation_priority_id
) escalation_priority_id,
DECODE (
p_owner_id,
fnd_api.g_miss_num,
owner_id,
p_owner_id
) owner_id,
DECODE (
p_owner_territory_id,
fnd_api.g_miss_num,
owner_territory_id,
p_owner_territory_id
) owner_territory_id,
DECODE (
p_assigned_by_id,
fnd_api.g_miss_num,
assigned_by_id,
p_assigned_by_id
) assigned_by_id,
DECODE (
p_customer_id,
fnd_api.g_miss_num,
customer_id,
p_customer_id
) customer_id,
DECODE (
p_cust_account_id,
fnd_api.g_miss_num,
cust_account_id,
p_cust_account_id
) cust_account_id,
DECODE (
p_address_id,
fnd_api.g_miss_num,
address_id,
p_address_id
) address_id,
DECODE (
p_target_date,
fnd_api.g_miss_date,
planned_end_date,
p_target_date
) target_date,
DECODE (
p_reason_code,
fnd_api.g_miss_char,
reason_code,
p_reason_code
) reason_code,
DECODE (
p_private_flag,
fnd_api.g_miss_char,
private_flag,
p_private_flag
) private_flag,
DECODE (
p_publish_flag,
fnd_api.g_miss_char,
publish_flag,
p_publish_flag
) publish_flag,
DECODE (
p_workflow_process_id,
fnd_api.g_miss_num,
workflow_process_id,
p_workflow_process_id
) workflow_process_id,
DECODE (
p_escalation_level,
fnd_api.g_miss_char,
escalation_level,
p_escalation_level
) escalation_level,
DECODE (
p_open_date,
fnd_api.g_miss_date,
actual_start_date,
p_open_date
) open_date,
DECODE (
p_close_date,
fnd_api.g_miss_date,
actual_end_date,
p_close_date
) close_date
FROM jtf_tasks_vl
WHERE task_id =
l_escalation_id;
escalation_rec c_escalation_update%ROWTYPE;
select MAX(TASK_AUDIT_ID)
from JTF_TASK_AUDITS_B
where TASK_ID = l_escalation_id;
SAVEPOINT update_escalation_pvt;
SELECT DECODE (l_escalation_id, fnd_api.g_miss_num, NULL, l_escalation_id)
INTO
l_escalation_id
FROM dual;
SELECT DECODE (
l_escalation_number,
fnd_api.g_miss_char, NULL,
l_escalation_number
)
INTO
l_escalation_number
FROM dual;
OPEN c_escalation_update (l_escalation_id);
FETCH c_escalation_update INTO escalation_rec;
IF c_escalation_update%NOTFOUND
THEN
fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TASK_ID');
CLOSE c_escalation_update;
jtf_tasks_pub.update_task (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_object_version_number => p_object_version_number,
p_task_id => l_escalation_id,
p_task_name => l_escalation_name,
p_task_type_id => 22,
p_description => l_description,
p_task_status_id => l_escalation_status_id,
p_task_priority_id => p_escalation_priority_id,
p_task_priority_name => p_escalation_priority_name,
-- p_owner_type_code => jtf_ec_pub.g_escalation_owner_type_code,
p_owner_type_code => p_escalation_owner_type_code,
p_owner_id => p_owner_id,
p_owner_territory_id => p_owner_territory_id ,
p_assigned_by_id => l_assigned_by_id,
p_customer_number => p_customer_number,
p_customer_id => p_customer_id,
p_cust_account_id => p_cust_account_id,
p_cust_account_number => p_cust_account_number,
p_address_number => p_address_number,
p_address_id => p_address_id,
p_planned_start_date => null ,
p_planned_end_date => p_target_date,
/* p_scheduled_start_date => p_scheduled_start_date,
p_scheduled_end_date => p_scheduled_end_date,*/
p_actual_start_date => p_open_date,
p_actual_end_date => p_close_date ,
--- p_timezone_id => l_timezone_id,
-- p_source_object_type_code => escalation_code,
-- p_source_object_id => l_source_object_id,
-- p_source_object_name => l_source_object_name,
-- p_duration => l_duration,
-- p_duration_uom => l_duration_uom,
/* p_planned_effort => l_planned_effort,
p_planned_effort_uom => l_planned_effort_uom,
p_actual_effort => l_actual_effort,
p_actual_effort_uom => l_actual_effort_uom,
p_percentage_complete => l_percentage_complete,
*/
p_reason_code => p_reason_code,
p_private_flag => l_private_flag,
p_publish_flag => l_publish_flag,
/* p_restrict_closure_flag => l_restrict_closure_flag,
p_multi_booked_flag => l_multi_booked_flag,
p_milestone_flag => l_milestone_flag,
p_holiday_flag => l_holiday_flag,
p_billable_flag => l_billable_flag,
p_bound_mode_code => l_bound_mode_code,
p_soft_bound_flag => l_soft_bound_flag,
*/
p_workflow_process_id => escalation_rec.workflow_process_id,
/* p_notification_flag => l_notification_flag,
p_notification_period => l_notification_period,
p_notification_period_uom => l_notification_period_uom,
*/
/* p_parent_task_id => l_parent_task_id ,
p_alarm_start => l_alarm_start,
p_alarm_start_uom => l_alarm_start_uom,
p_alarm_on => l_alarm_on,
p_alarm_count => l_alarm_count,
p_alarm_fired_count => l_alarm_fired_count,
p_alarm_interval => l_alarm_interval,
p_alarm_interval_uom => l_alarm_interval_uom,
p_palm_flag => l_palm_flag,
p_wince_flag => l_wince_flag,
p_laptop_flag => l_laptop_flag,
p_device1_flag => l_device1_flag,
p_device2_flag => l_device2_flag,
p_device3_flag => l_device3_flag,
p_costs => l_costs,
p_currency_code => l_currency_code,
*/
p_escalation_level => p_escalation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute_category => p_attribute_category
);
jtf_esc_wf_events_pvt.publish_update_esc
(p_esc_rec => l_esc_rec_type);
ROLLBACK TO update_escalation_pvt;
ROLLBACK TO update_escalation_pvt;
ROLLBACK TO update_escalation_pvt;
PROCEDURE delete_escalation (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_object_version_number IN NUMBER ,
p_escalation_id IN NUMBER DEFAULT NULL,
p_escalation_number IN VARCHAR2 DEFAULT NULL,
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 CONSTANT VARCHAR2(30) := 'DELETE_TASK';
/*Commenting out the below cursor definition since call to jtf_tasks_pvt.delete_task deletes the reference records */
/* CURSOR c_delete_references
IS
SELECT task_reference_id,object_version_number
FROM jtf_task_references_vl
WHERE task_id = l_escalation_id; */
SELECT REFERENCE_CODE, OBJECT_TYPE_CODE, OBJECT_ID, TASK_ID
FROM JTF_TASK_REFERENCES_B
WHERE task_id = l_escalation_id;
SAVEPOINT delete_escalation_pvt;
jtf_tasks_pvt.delete_task (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_object_version_number => p_object_version_number,
p_task_id => l_escalation_id,
p_delete_future_recurrences => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
jtf_esc_wf_events_pvt.publish_delete_escRef
(p_esc_ref_rec => ref_recs(l_cnt1));
/*Commenting out the below code since call to jtf_tasks_pvt.delete_task deletes the reference records also
-- delete references....
---------------------------
FOR b IN c_delete_references
LOOP
jtf_ec_references_pvt.delete_references (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_object_version_number => b.object_version_number,
p_escalation_reference_id => b.task_reference_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
jtf_esc_wf_events_pvt.publish_delete_esc
(p_esc_rec => l_esc_rec_type);
ROLLBACK TO delete_escalation_pvt;
ROLLBACK TO delete_escalation_pvt;