228:
229: l_api_version NUMBER := 1.0;
230: l_esc_contacts Esc_Contacts_Rec_Type := p_esc_contacts_rec;
231: -- l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
232: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
233: l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
234: l_return_status varchar2(1) := 'x';
235: l_api_name varchar2(30) := 'Create_Esc_Contacts';
236: l_task_phone_id jtf_task_phones.task_phone_id%TYPE := NULL;
229: l_api_version NUMBER := 1.0;
230: l_esc_contacts Esc_Contacts_Rec_Type := p_esc_contacts_rec;
231: -- l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
232: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
233: l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
234: l_return_status varchar2(1) := 'x';
235: l_api_name varchar2(30) := 'Create_Esc_Contacts';
236: l_task_phone_id jtf_task_phones.task_phone_id%TYPE := NULL;
237: l_escalation_contact_id jtf_task_references_b.task_reference_id%TYPE;
379: x_esc_reference_id OUT NOCOPY NUMBER,
380: x_return_status OUT NOCOPY VARCHAR2) Is
381:
382: l_reference_documents Esc_Ref_Docs_Rec_Type := p_reference_documents_rec;
383: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
384: l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
385: l_return_status varchar2(1) := 'x';
386: l_task_ref_id jtf_task_references_b.task_reference_id%TYPE := NULL; --esc id where ref doc is escalated
387: l_api_name varchar2(30) := 'Create_Esc_Reference';
380: x_return_status OUT NOCOPY VARCHAR2) Is
381:
382: l_reference_documents Esc_Ref_Docs_Rec_Type := p_reference_documents_rec;
383: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
384: l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
385: l_return_status varchar2(1) := 'x';
386: l_task_ref_id jtf_task_references_b.task_reference_id%TYPE := NULL; --esc id where ref doc is escalated
387: l_api_name varchar2(30) := 'Create_Esc_Reference';
388: l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
546: p_resp_appl_id IN NUMBER,
547: p_resp_id IN NUMBER,
548: p_user_id IN NUMBER,
549: p_login_id IN NUMBER,
550: p_esc_id IN jtf_tasks_b.task_id%TYPE :=NULL,
551: -- p_esc_number IN jtf_tasks_b.task_number%TYPE :=NULL,
552: p_esc_record IN Esc_Rec_Type,
553: p_reference_documents IN Esc_Ref_Docs_Tbl_Type,
554: p_esc_contacts IN Esc_Contacts_Tbl_Type,
547: p_resp_id IN NUMBER,
548: p_user_id IN NUMBER,
549: p_login_id IN NUMBER,
550: p_esc_id IN jtf_tasks_b.task_id%TYPE :=NULL,
551: -- p_esc_number IN jtf_tasks_b.task_number%TYPE :=NULL,
552: p_esc_record IN Esc_Rec_Type,
553: p_reference_documents IN Esc_Ref_Docs_Tbl_Type,
554: p_esc_contacts IN Esc_Contacts_Tbl_Type,
555: p_cont_points IN Esc_Cont_Points_Tbl_Type,
563: l_esc_record Esc_Rec_Type := p_esc_record;
564: l_reference_documents Esc_Ref_Docs_Tbl_Type := p_reference_documents;
565: l_esc_contacts Esc_Contacts_Tbl_Type := p_esc_contacts;
566: l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
567: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
568: l_escalation_number jtf_tasks_b.task_number%TYPE;
569: l_return_status varchar2(1) := 'x';
570: l_owner_id jtf_tasks_b.owner_id%TYPE:=NULL;
571: l_req_count NUMBER := 0;
564: l_reference_documents Esc_Ref_Docs_Tbl_Type := p_reference_documents;
565: l_esc_contacts Esc_Contacts_Tbl_Type := p_esc_contacts;
566: l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
567: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
568: l_escalation_number jtf_tasks_b.task_number%TYPE;
569: l_return_status varchar2(1) := 'x';
570: l_owner_id jtf_tasks_b.owner_id%TYPE:=NULL;
571: l_req_count NUMBER := 0;
572: l_escalation_contact_id jtf_task_contacts.task_contact_id%TYPE;
566: l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
567: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
568: l_escalation_number jtf_tasks_b.task_number%TYPE;
569: l_return_status varchar2(1) := 'x';
570: l_owner_id jtf_tasks_b.owner_id%TYPE:=NULL;
571: l_req_count NUMBER := 0;
572: l_escalation_contact_id jtf_task_contacts.task_contact_id%TYPE;
573: l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
574: l_temp VARCHAR2(10);
571: l_req_count NUMBER := 0;
572: l_escalation_contact_id jtf_task_contacts.task_contact_id%TYPE;
573: l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
574: l_temp VARCHAR2(10);
575: l_esc_status_id jtf_tasks_b.task_status_id%TYPE;
576: l_task_phone_id jtf_task_phones.task_phone_id%TYPE := NULL;
577: l_user_id NUMBER := p_user_id;
578: l_resp_appl_id NUMBER := p_resp_appl_id;
579: l_resp_id NUMBER := p_resp_id;
592: and nvl(end_date_active, sysdate));
593:
594: cursor task_number(p_task_id NUMBER) is
595: SELECT task_number
596: FROM jtf_tasks_b
597: WHERE task_id = p_task_id;
598:
599: BEGIN
600:
1093: end if;
1094:
1095: -- Insert the wf_process_id
1096:
1097: update jtf_tasks_b
1098: set workflow_process_id = l_wf_process_id
1099: where task_id = l_escalation_id;
1100:
1101: ----------------------------------------------------------------------------------------------------
1203: p_resp_appl_id IN NUMBER := NULL,
1204: p_resp_id IN NUMBER := NULL,
1205: p_user_id IN NUMBER := NULL, -- used for last updated by
1206: p_login_id IN NUMBER := NULL,
1207: p_esc_id IN jtf_tasks_b.task_id%TYPE :=NULL,
1208: p_esc_number IN jtf_tasks_b.task_number%TYPE :=NULL,
1209: p_object_version IN NUMBER,
1210: p_esc_record IN Esc_Rec_Type,
1211: p_reference_documents IN Esc_Ref_Docs_Tbl_Type,
1204: p_resp_id IN NUMBER := NULL,
1205: p_user_id IN NUMBER := NULL, -- used for last updated by
1206: p_login_id IN NUMBER := NULL,
1207: p_esc_id IN jtf_tasks_b.task_id%TYPE :=NULL,
1208: p_esc_number IN jtf_tasks_b.task_number%TYPE :=NULL,
1209: p_object_version IN NUMBER,
1210: p_esc_record IN Esc_Rec_Type,
1211: p_reference_documents IN Esc_Ref_Docs_Tbl_Type,
1212: p_esc_contacts IN Esc_Contacts_Tbl_Type,
1222: l_esc_contacts Esc_Contacts_Tbl_Type := p_esc_contacts;
1223: l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
1224: l_notes Notes_Tbl_Type := p_notes;
1225: l_note_id jtf_notes_b.jtf_note_id%TYPE;
1226: l_escalation_id jtf_tasks_b.task_id%TYPE := NULL;
1227: l_escalation_number jtf_tasks_b.task_number%TYPE := NULL;
1228: l_esc_status_id jtf_tasks_b.task_status_id%TYPE;
1229: l_object_version_number jtf_tasks_b.object_version_number%TYPE := p_object_version;
1230: l_user_id NUMBER := p_user_id;
1223: l_cont_points Esc_Cont_Points_Tbl_Type := p_cont_points;
1224: l_notes Notes_Tbl_Type := p_notes;
1225: l_note_id jtf_notes_b.jtf_note_id%TYPE;
1226: l_escalation_id jtf_tasks_b.task_id%TYPE := NULL;
1227: l_escalation_number jtf_tasks_b.task_number%TYPE := NULL;
1228: l_esc_status_id jtf_tasks_b.task_status_id%TYPE;
1229: l_object_version_number jtf_tasks_b.object_version_number%TYPE := p_object_version;
1230: l_user_id NUMBER := p_user_id;
1231: l_resp_appl_id NUMBER := p_resp_appl_id;
1224: l_notes Notes_Tbl_Type := p_notes;
1225: l_note_id jtf_notes_b.jtf_note_id%TYPE;
1226: l_escalation_id jtf_tasks_b.task_id%TYPE := NULL;
1227: l_escalation_number jtf_tasks_b.task_number%TYPE := NULL;
1228: l_esc_status_id jtf_tasks_b.task_status_id%TYPE;
1229: l_object_version_number jtf_tasks_b.object_version_number%TYPE := p_object_version;
1230: l_user_id NUMBER := p_user_id;
1231: l_resp_appl_id NUMBER := p_resp_appl_id;
1232: l_resp_id NUMBER := p_resp_id;
1225: l_note_id jtf_notes_b.jtf_note_id%TYPE;
1226: l_escalation_id jtf_tasks_b.task_id%TYPE := NULL;
1227: l_escalation_number jtf_tasks_b.task_number%TYPE := NULL;
1228: l_esc_status_id jtf_tasks_b.task_status_id%TYPE;
1229: l_object_version_number jtf_tasks_b.object_version_number%TYPE := p_object_version;
1230: l_user_id NUMBER := p_user_id;
1231: l_resp_appl_id NUMBER := p_resp_appl_id;
1232: l_resp_id NUMBER := p_resp_id;
1233: l_login_id NUMBER := p_login_id;
1232: l_resp_id NUMBER := p_resp_id;
1233: l_login_id NUMBER := p_login_id;
1234: l_return_status varchar2(1) := 'x';
1235: l_closed_flag varchar2(1) := 'N';
1236: l_esc_close_date jtf_tasks_b.actual_end_date%TYPE;
1237: l_esc_task_id jtf_tasks_b.task_id%TYPE := NULL; --esc id where ref doc is escalated
1238: l_task_ref_id jtf_task_references_b.task_reference_id%TYPE;
1239: l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
1240: l_escalation_contact_id jtf_task_references_b.task_reference_id%TYPE;
1233: l_login_id NUMBER := p_login_id;
1234: l_return_status varchar2(1) := 'x';
1235: l_closed_flag varchar2(1) := 'N';
1236: l_esc_close_date jtf_tasks_b.actual_end_date%TYPE;
1237: l_esc_task_id jtf_tasks_b.task_id%TYPE := NULL; --esc id where ref doc is escalated
1238: l_task_ref_id jtf_task_references_b.task_reference_id%TYPE;
1239: l_escalation_reference_id jtf_task_references_b.task_reference_id%TYPE;
1240: l_escalation_contact_id jtf_task_references_b.task_reference_id%TYPE;
1241: l_task_phone_id jtf_task_phones.task_phone_id%TYPE := NULL;
1242: l_owner_changed VARCHAR2(1) := 'N';
1243: l_level_changed VARCHAR2(1) := 'N';
1244: l_status_changed VARCHAR2(1) := 'N';
1245: l_target_date_changed VARCHAR2(1) := 'N';
1246: l_old_owner_id jtf_tasks_b.owner_id%TYPE;
1247: l_old_status_id jtf_tasks_b.task_status_id%TYPE;
1248: l_old_escalation_level jtf_tasks_b.escalation_level%TYPE;
1249: l_old_target_date jtf_tasks_b.planned_end_date%TYPE;
1250: l_notif_not_sent VARCHAR2(2000);
1243: l_level_changed VARCHAR2(1) := 'N';
1244: l_status_changed VARCHAR2(1) := 'N';
1245: l_target_date_changed VARCHAR2(1) := 'N';
1246: l_old_owner_id jtf_tasks_b.owner_id%TYPE;
1247: l_old_status_id jtf_tasks_b.task_status_id%TYPE;
1248: l_old_escalation_level jtf_tasks_b.escalation_level%TYPE;
1249: l_old_target_date jtf_tasks_b.planned_end_date%TYPE;
1250: l_notif_not_sent VARCHAR2(2000);
1251: l_wf_process_id NUMBER;
1244: l_status_changed VARCHAR2(1) := 'N';
1245: l_target_date_changed VARCHAR2(1) := 'N';
1246: l_old_owner_id jtf_tasks_b.owner_id%TYPE;
1247: l_old_status_id jtf_tasks_b.task_status_id%TYPE;
1248: l_old_escalation_level jtf_tasks_b.escalation_level%TYPE;
1249: l_old_target_date jtf_tasks_b.planned_end_date%TYPE;
1250: l_notif_not_sent VARCHAR2(2000);
1251: l_wf_process_id NUMBER;
1252: l_object_name jtf_task_references_b.object_name%TYPE;
1245: l_target_date_changed VARCHAR2(1) := 'N';
1246: l_old_owner_id jtf_tasks_b.owner_id%TYPE;
1247: l_old_status_id jtf_tasks_b.task_status_id%TYPE;
1248: l_old_escalation_level jtf_tasks_b.escalation_level%TYPE;
1249: l_old_target_date jtf_tasks_b.planned_end_date%TYPE;
1250: l_notif_not_sent VARCHAR2(2000);
1251: l_wf_process_id NUMBER;
1252: l_object_name jtf_task_references_b.object_name%TYPE;
1253: l_object_id jtf_task_references_b.object_id%TYPE;
1259: where task_contact_id = p_task_contact_id;
1260:
1261: cursor c_get_old_esc_data(p_escalation_id NUMBER) Is
1262: select owner_id, task_status_id, escalation_level, planned_end_date
1263: from jtf_tasks_b
1264: where task_id = p_escalation_id;
1265:
1266: cursor c_get_ref_details(p_task_ref_id NUMBER) Is
1267: Select object_type_code,
2240: end if;
2241:
2242: -- Update the wf_process_id
2243:
2244: update jtf_tasks_b
2245: set workflow_process_id = l_wf_process_id
2246: where task_id = l_escalation_id;
2247:
2248: --------------------------------------------------------------------------------------------
2355: x_msg_count OUT NOCOPY NUMBER,
2356: x_msg_data OUT NOCOPY VARCHAR2,
2357: p_user_id IN NUMBER,
2358: p_login_id IN NUMBER,
2359: p_esc_id IN jtf_tasks_b.task_id%TYPE,
2360: p_esc_number IN jtf_tasks_b.task_number%TYPE,
2361: p_object_version IN NUMBER) Is
2362:
2363: l_api_version CONSTANT NUMBER := 1.0;
2356: x_msg_data OUT NOCOPY VARCHAR2,
2357: p_user_id IN NUMBER,
2358: p_login_id IN NUMBER,
2359: p_esc_id IN jtf_tasks_b.task_id%TYPE,
2360: p_esc_number IN jtf_tasks_b.task_number%TYPE,
2361: p_object_version IN NUMBER) Is
2362:
2363: l_api_version CONSTANT NUMBER := 1.0;
2364: l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ESCALATION';
2361: p_object_version IN NUMBER) Is
2362:
2363: l_api_version CONSTANT NUMBER := 1.0;
2364: l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ESCALATION';
2365: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
2366: l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
2367: l_return_status varchar2(1) := 'x';
2368: l_esc_task_id jtf_tasks_b.task_id%TYPE;
2369: l_object_version_number NUMBER := p_object_version;
2362:
2363: l_api_version CONSTANT NUMBER := 1.0;
2364: l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ESCALATION';
2365: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
2366: l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
2367: l_return_status varchar2(1) := 'x';
2368: l_esc_task_id jtf_tasks_b.task_id%TYPE;
2369: l_object_version_number NUMBER := p_object_version;
2370: l_user_id NUMBER := p_user_id;
2364: l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ESCALATION';
2365: l_escalation_id jtf_tasks_b.task_id%TYPE := p_esc_id;
2366: l_escalation_number jtf_tasks_b.task_number%TYPE := p_esc_number;
2367: l_return_status varchar2(1) := 'x';
2368: l_esc_task_id jtf_tasks_b.task_id%TYPE;
2369: l_object_version_number NUMBER := p_object_version;
2370: l_user_id NUMBER := p_user_id;
2371: l_login_id NUMBER := p_login_id;
2372: