[Home] [Help]
PACKAGE BODY: APPS.OKC_TASK_PVT
Source
1 PACKAGE BODY OKC_TASK_PVT AS
2 /* $Header: OKCRTSKB.pls 120.0 2005/05/26 09:25:47 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ----------------------------------------------------------------------------------
6 -- Start of comments
7 -- Procedure Name : create_task
8 -- Description : Procedure to create a task for a resolved timevalue
9 -- Version : 1.0
10 -- End of comments
11 ----------------------------------------------------------------------------------
12 PROCEDURE create_task(p_api_version IN NUMBER
13 ,p_init_msg_list IN VARCHAR2
14 ,p_resolved_time_id IN NUMBER
15 ,p_timezone_id IN NUMBER
16 ,p_timezone_name IN VARCHAR2
17 ,p_tve_id IN NUMBER
18 ,p_planned_end_date IN DATE
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_msg_count OUT NOCOPY NUMBER
21 ,x_msg_data OUT NOCOPY VARCHAR2
22 ,x_task_id OUT NOCOPY NUMBER) IS
23 l_api_name CONSTANT VARCHAR2(30) := 'create_task';
24 l_task_id jtf_tasks_b.task_id%TYPE;
25 l_task_name jtf_tasks_tl.task_name%TYPE;
26 l_task_type_id jtf_task_types_b.task_type_id%TYPE;
27 l_task_type_name jtf_task_types_tl.name%TYPE;
28 l_status_id jtf_task_statuses_b.task_status_id%type;
29 l_status_name jtf_task_statuses_tl.name%type;
30 l_owner_type_code jtf_objects_b.object_code%TYPE;
31 l_source_object_code jtf_objects_b.object_code%TYPE;
32 l_source_object_name jtf_tasks_b.source_object_name%TYPE;
33 l_private_flag jtf_tasks_b.private_flag%TYPE;
34 l_notification_flag jtf_tasks_b.notification_flag%TYPE;
35 l_notification_period jtf_tasks_b.notification_period%TYPE;
36 l_notification_period_uom jtf_tasks_b.notification_period_uom%TYPE;
37 l_escalate_days jtf_tasks_b.alarm_start%TYPE;
38 l_alarm_start jtf_tasks_b.alarm_start%TYPE;
39 l_alarm_start_uom jtf_tasks_b.alarm_start_uom%TYPE;
40 l_alarm_count jtf_tasks_b.alarm_count%TYPE;
41 l_alarm_interval jtf_tasks_b.alarm_interval%TYPE;
42 l_alarm_interval_uom jtf_tasks_b.alarm_interval_uom%TYPE;
43 l_deleted_flag jtf_tasks_b.deleted_flag%TYPE;
44 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
45 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
46 l_resolved_time_id VARCHAR2(300);
47 l_day_uom VARCHAR2(30);
48
49 -- Defined for bug 1652537
50 TYPE rules_cur_type is REF CURSOR;
51 rules_cur rules_cur_type;
52
53
54 --Select the rule details
55 /* Cursor rules_cur(p_tve_id IN NUMBER) is
56 select rule_information1 task_name
57 ,rule_information3 notification_period
58 ,rule_information4 resource_id
59 ,rule_information5 escalate_days
60 from okc_rules_v
61 where rule_information_category = 'NTN'
62 and rule_information2 = p_tve_id;
63 rules_rec rules_cur%ROWTYPE;
64 */
65 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
66 -- Read OKCSCHRULE - Contract Schedule Rule
67 --Select the task_type
68 Cursor task_cur is
69 select task_type_id, name
70 from jtf_task_types_vl
71 where task_type_id = 23;
72 -- where name = 'OKCSCHRULE';
73
74 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
75 --Select the task status
76 Cursor status_cur is
77 select task_status_id, name
78 from jtf_task_statuses_vl
79 where task_status_id = 10;
80 --where name = 'Open';
81
82 --Select the object code
83 Cursor object_cur is
84 select object_code
85 from jtf_objects_vl
86 where object_code = 'OKC_RESTIME'
87 and object_code in (select object_code
88 from jtf_object_usages
89 where object_user_code = 'TASK');
90
91 --Select the owner type code
92 Cursor owner_type_cur is
93 select object_code
94 from jtf_objects_vl
95 where object_code = 'OKX_TASKRES'
96 and object_code in (select object_code
97 from jtf_object_usages
98 where object_user_code = 'RESOURCES');
99
100 CURSOR day_cur is
101 SELECT UOM_CODE FROM OKC_TIME_CODE_UNITS_B
102 where tce_code = 'DAY'
103 and rownum < 2;
104
105 l_notfound BOOLEAN;
106 l_app_id NUMBER;
107 l_rule_df_name VARCHAR2(40);
108 l_list_of_rules VARCHAR2(4000);
109 l_list_of_rules1 VARCHAR2(4000);
110 l_sql_string VARCHAR2(4000);
111
112 -- bug 1757364
113 l_k_number okc_k_headers_b.contract_number%TYPE := '';
114
115 BEGIN
116 --Start : Modified for better error handling - Bug 1652537
117
118 -- Select the rule details
119
120 -- Resolve all values related to contracts
121 -- Get the application_id and rule definition names
122 l_app_id := OKC_TIME_UTIL_PUB.get_app_id;
123 if l_app_id is null then
124 return;
125 end if;
126
127 l_rule_df_name := OKC_TIME_UTIL_PUB.get_rule_df_name;
128 if l_rule_df_name is null then
129 return;
130 end if;
131
132 -- Get all the rule types (e.g. NTN) from metadata which are related to timevalues.
133 -- Get all the rule types (e.g. NTN) from metadata which are related to tasks.
134
135 l_list_of_rules := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TIMEVALUES');
136 l_list_of_rules1 := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TASK_RS');
137
138 x_return_status := OKC_API.G_RET_STS_SUCCESS;
139 -- For these rules, get relevant information using tve_id.
140
141 /* Changed the rules cursor in the following query from okc_rules_v to okc_rules_b for performance
142 Also using explicit to_char conversion on tveid in the following query .
143 These changes are done to enhance performance */
144 l_sql_string := 'select r.rule_information1 task_name,r.rule_information3 notification_period,rule_information4 resource_id,rule_information5 escalate_days ' ||
145 'from okc_rules_b r '||
146 'where r.rule_information2 = to_char(:p_tve_id) ' ||
147 'and r.rule_information_category in '|| l_list_of_rules ||
148 'and r.rule_information_category in '|| l_list_of_rules1 ;
149 OPEN rules_cur for l_sql_string using p_tve_id;
150 FETCH rules_cur into l_task_name, l_notification_period, l_resource_id, l_escalate_days;
151 l_notfound := rules_cur%NOTFOUND;
152 CLOSE rules_cur;
153 if l_notfound THEN
154 OKC_API.set_message(G_APP_NAME,'OKC_RULE_NOT_FOUND');
155 x_return_status := OKC_API.G_RET_STS_ERROR;
156 return;
157 end if;
158
159 --End : Modified for better error handling - bug 1652537
160 --------------------------------------------------------------------------------------------------
161 OPEN day_cur;
162 FETCH day_cur INTO l_day_uom;
163 l_notfound := day_cur%NOTFOUND;
164 CLOSE day_cur;
165 if l_notfound THEN
166 OKC_API.set_message(G_APP_NAME,'OKC_TIME_CODE_NOT_FOUND');
167 x_return_status := OKC_API.G_RET_STS_ERROR;
168 return;
169 end if;
170 /*
171 OPEN rules_cur(p_tve_id);
172 FETCH rules_cur into l_task_name, l_notification_period, l_resource_id, l_escalate_days;
173 l_notfound := rules_cur%NOTFOUND;
174 CLOSE rules_cur;
175 if l_notfound THEN
176 x_return_status := OKC_API.G_RET_STS_ERROR;
177 return;
178 end if;
179 */
180
181 OPEN task_cur;
182 FETCH task_cur into l_task_type_id, l_task_type_name;
183 l_notfound := task_cur%NOTFOUND;
184 CLOSE task_cur;
185 if l_notfound THEN
186 OKC_API.set_message(G_APP_NAME,'OKC_TASK_TYPE_NOT_FOUND');
187 x_return_status := OKC_API.G_RET_STS_ERROR;
188 return;
189 end if;
190
191 OPEN status_cur;
192 FETCH status_cur into l_status_id, l_status_name;
193 l_notfound := status_cur%NOTFOUND;
194 CLOSE status_cur;
195 if l_notfound THEN
196 OKC_API.set_message(G_APP_NAME,'OKC_TASK_STATUS_NOT_FOUND');
197 x_return_status := OKC_API.G_RET_STS_ERROR;
198 return;
199 end if;
200
201 OPEN object_cur;
202 FETCH object_cur into l_source_object_code;
203 l_notfound := object_cur%NOTFOUND;
204 CLOSE object_cur;
205 if l_notfound THEN
206 OKC_API.set_message(G_APP_NAME,'OKC_OBJECT_CODE_NOT_FOUND');
207 x_return_status := OKC_API.G_RET_STS_ERROR;
208 return;
209 end if;
210
211 OPEN owner_type_cur;
212 FETCH owner_type_cur into l_owner_type_code;
213 l_notfound := owner_type_cur%NOTFOUND;
214 CLOSE owner_type_cur;
215 if l_notfound THEN
216 OKC_API.set_message(G_APP_NAME,'OKC_TASK_OWNER_NOT_FOUND');
217 x_return_status := OKC_API.G_RET_STS_ERROR;
218 return;
219 end if;
220
221 l_private_flag := 'N';
222 l_notification_flag := 'N';
223 l_notification_period_uom := l_day_uom;
224 l_resolved_time_id := to_char(p_resolved_time_id);
225
226 -- bug 1757364
227 l_k_number := OKC_QUERY.Get_Contract_Number(p_resolved_time_id);
228 l_k_number := SUBSTR(l_k_number,1,80);
229
230 -- end bug 1757364
231
232 IF l_escalate_days IS NOT NULL THEN
233 l_alarm_start := l_escalate_days;
234 l_alarm_start_uom := l_day_uom;
235 l_alarm_count := 2;
236 l_alarm_interval := l_escalate_days;
237 l_alarm_interval_uom := l_day_uom;
238 --Call the public API JTF_TASKS_PUB to create a Task
239 jtf_tasks_pub.create_task(p_api_version => p_api_version
240 ,p_init_msg_list => p_init_msg_list
241 ,p_task_id => l_task_id
242 ,p_task_name => l_task_name
243 ,p_task_type_name => l_task_type_name
244 ,p_task_type_id => l_task_type_id
245 ,p_task_status_name => l_status_name
246 ,p_task_status_id => l_status_id
247 ,p_owner_type_code => l_owner_type_code
248 ,p_owner_id => l_resource_id
249 ,p_planned_end_date => p_planned_end_date
250 ,p_timezone_id => p_timezone_id
251 ,p_timezone_name => p_timezone_name
252 ,p_source_object_type_code => l_source_object_code
253 ,p_source_object_id => p_resolved_time_id
254 ,p_source_object_name => l_k_number
255 ,p_private_flag => l_private_flag
256 ,p_notification_flag => l_notification_flag
257 ,p_notification_period => l_notification_period
258 ,p_notification_period_uom => l_notification_period_uom
259 ,p_alarm_start => l_alarm_start
260 ,p_alarm_start_uom => l_alarm_start_uom
261 ,p_alarm_on => 'Y'
262 ,p_alarm_count => l_alarm_count
263 ,p_alarm_interval => l_alarm_interval
264 ,p_alarm_interval_uom => l_alarm_interval_uom
265 ,x_return_status => x_return_status
266 ,x_msg_count => x_msg_count
267 ,x_msg_data => x_msg_data
268 ,x_task_id => x_task_id);
269 ELSIF l_escalate_days IS NULL THEN
270 --Call the public API JTF_TASKS_PUB to create a Task
271 jtf_tasks_pub.create_task(p_api_version => p_api_version
272 ,p_init_msg_list => p_init_msg_list
273 ,p_task_id => l_task_id
274 ,p_task_name => l_task_name
275 ,p_task_type_name => l_task_type_name
276 ,p_task_type_id => l_task_type_id
277 ,p_task_status_name => l_status_name
278 ,p_task_status_id => l_status_id
279 ,p_owner_type_code => l_owner_type_code
280 ,p_owner_id => l_resource_id
281 ,p_planned_end_date => p_planned_end_date
282 ,p_timezone_id => p_timezone_id
283 ,p_timezone_name => p_timezone_name
284 ,p_source_object_type_code => l_source_object_code
285 ,p_source_object_id => p_resolved_time_id
286 ,p_source_object_name => l_k_number
287 ,p_private_flag => l_private_flag
288 ,p_notification_flag => l_notification_flag
289 ,p_notification_period => l_notification_period
290 ,p_notification_period_uom => l_notification_period_uom
291 ,x_return_status => x_return_status
292 ,x_msg_count => x_msg_count
293 ,x_msg_data => x_msg_data
294 ,x_task_id => x_task_id);
295 END IF;
296
297 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
298 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
299 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
300 raise OKC_API.G_EXCEPTION_ERROR;
301 END IF;
302 EXCEPTION
303 WHEN OKC_API.G_EXCEPTION_ERROR THEN
304 x_return_status := 'OKC_API.G_RET_STS_ERROR';
305 NULL;
306 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
307 x_return_status := 'OKC_API.G_RET_STS_UNEXP_ERROR';
308 NULL;
309 WHEN OTHERS THEN
310 OKC_API.set_message(p_app_name => g_app_name,
311 p_msg_name => g_unexpected_error,
312 p_token1 => g_sqlcode_token,
313 p_token1_value => sqlcode,
314 p_token2 => g_sqlerrm_token,
315 p_token2_value => sqlerrm);
316 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
317 END create_task;
318
319 -------------------------------------------------------------------------------------
320 -- Start of comments
321 -- Procedure Name : create_condition_task
322 -- Description : Procedure to create a Task for a condition occurrence
323 -- Version : 1.0
324 -- End of comments
325 -------------------------------------------------------------------------------------
326 PROCEDURE create_condition_task(p_api_version IN NUMBER
327 ,p_init_msg_list IN VARCHAR2
328 ,p_cond_occr_id IN NUMBER
329 ,p_condition_name IN VARCHAR2
330 ,p_task_owner_id IN NUMBER
331 ,p_actual_end_date IN DATE
332 ,x_return_status OUT NOCOPY VARCHAR2
333 ,x_msg_count OUT NOCOPY NUMBER
334 ,x_msg_data OUT NOCOPY VARCHAR2
335 ,x_task_id OUT NOCOPY NUMBER) IS
336 l_api_name CONSTANT VARCHAR2(30) := 'create_condition_task';
337 l_task_id jtf_tasks_b.task_id%TYPE;
338 l_task_type_id jtf_task_types_b.task_type_id%TYPE;
339 l_task_type_name jtf_task_types_tl.name%TYPE;
340 l_status_id jtf_task_statuses_b.task_status_id%type;
341 l_status_name jtf_task_statuses_tl.name%type;
342 l_source_object_code jtf_objects_b.object_code%TYPE;
343 l_private_flag jtf_tasks_b.private_flag%TYPE;
344 l_deleted_flag jtf_tasks_b.deleted_flag%TYPE;
345 l_source_object_name jtf_tasks_b.source_object_name%TYPE;
346 l_owner_type_code jtf_objects_b.object_code%TYPE;
347 l_owner_id jtf_tasks_b.owner_id%TYPE;
348
349 l_source_doc_number VARCHAR2(200);
350
351 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
352 -- Read OKCCONDITION - Contract Condition
353 --Select task_type
354 Cursor task_cur is
355 select task_type_id, name
356 from jtf_task_types_vl
357 where task_type_id = 18;
358 --where name = 'OKCCONDITION';
359
360 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
361 --Select task status
362 Cursor status_cur is
363 select task_status_id, name
364 from jtf_task_statuses_vl
365 where task_status_id = 9;
366 --where name = 'Closed';
367
368 --Select object code
369 Cursor object_cur is
370 select object_code
371 from jtf_objects_vl
372 where object_code = 'OKC_COND_OCCR'
373 and object_code in (select object_code
374 from jtf_object_usages
375 where object_user_code = 'TASK');
376
377 --Select the owner type code
378 Cursor owner_type_cur is
379 select object_code
380 from jtf_objects_vl
381 where object_code = 'OKX_TASKRES'
382 and object_code in (select object_code
383 from jtf_object_usages
384 where object_user_code = 'RESOURCES');
385
386 l_notfound BOOLEAN;
387 BEGIN
388 l_private_flag := 'N';
389
390 OPEN task_cur;
391 FETCH task_cur into l_task_type_id, l_task_type_name;
392 l_notfound := task_cur%NOTFOUND;
393 CLOSE task_cur;
394 if l_notfound THEN
395 OKC_API.set_message(G_APP_NAME,'OKC_TASK_TYPE_NOT_FOUND');
396 x_return_status := OKC_API.G_RET_STS_ERROR;
397 return;
398 end if;
399
400 OPEN status_cur;
401 FETCH status_cur into l_status_id, l_status_name;
402 l_notfound := status_cur%NOTFOUND;
403 CLOSE status_cur;
404 if l_notfound THEN
405 OKC_API.set_message(G_APP_NAME,'OKC_TASK_STATUS_NOT_FOUND');
406 x_return_status := OKC_API.G_RET_STS_ERROR;
407 return;
408 end if;
409
410 OPEN object_cur;
411 FETCH object_cur into l_source_object_code;
412 l_notfound := object_cur%NOTFOUND;
413 CLOSE object_cur;
414 if l_notfound THEN
415 OKC_API.set_message(G_APP_NAME,'OKC_OBJECT_CODE_NOT_FOUND');
416 x_return_status := OKC_API.G_RET_STS_ERROR;
417 return;
418 end if;
419
420 OPEN owner_type_cur;
421 FETCH owner_type_cur into l_owner_type_code;
422 l_notfound := owner_type_cur%NOTFOUND;
423 CLOSE owner_type_cur;
424 if l_notfound THEN
425 OKC_API.set_message(G_APP_NAME,'OKC_TASK_OWNER_NOT_FOUND');
426 x_return_status := OKC_API.G_RET_STS_ERROR;
427 return;
428 end if;
429
430 -- get the source document number (to be displayed in tasks window)
431 -- bug 1757364
432 l_source_doc_number := OKC_QUERY.Get_Source_Doc_Number(p_cond_occr_id);
433 l_source_doc_number := SUBSTR(l_source_doc_number,1,80);
434
435 --Call to the procedure of the public API JTF_TASKS_PUB to create a Task
436 jtf_tasks_pub.create_task(p_api_version => p_api_version
437 ,p_init_msg_list => p_init_msg_list
438 ,p_task_id => l_task_id
439 ,p_task_name => p_condition_name
440 ,p_task_type_name => l_task_type_name
441 ,p_task_type_id => l_task_type_id
442 ,p_task_status_name => l_status_name
443 ,p_task_status_id => l_status_id
444 ,p_actual_end_date => p_actual_end_date
445 ,p_source_object_type_code => l_source_object_code
446 ,p_source_object_name => l_source_doc_number
447 ,p_source_object_id => p_cond_occr_id
448 ,p_owner_id => p_task_owner_id
449 ,p_owner_type_code => l_owner_type_code
450 ,p_private_flag => l_private_flag
451 ,x_return_status => x_return_status
452 ,x_msg_count => x_msg_count
453 ,x_msg_data => x_msg_data
454 ,x_task_id => x_task_id);
455
456 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
457 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
458 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
459 raise OKC_API.G_EXCEPTION_ERROR;
460 END IF;
461 EXCEPTION
462 WHEN OKC_API.G_EXCEPTION_ERROR THEN
463 x_return_status := 'OKC_API.G_RET_STS_ERROR';
464 NULL;
465 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
466 x_return_status := 'OKC_API.G_RET_STS_UNEXP_ERROR';
467 NULL;
468 WHEN OTHERS THEN
469 x_return_status := OKC_API.HANDLE_EXCEPTIONS
470 (l_api_name,
471 G_PKG_NAME,
472 'OTHERS',
473 x_msg_count,
474 x_msg_data,
475 '_PROCESS');
476
477 END create_condition_task;
478
479 -- Start of comments
480 -- Procedure Name : create_contingent_task
481 -- Description : Procedure to create a Task for a contingent event
482 -- Version : 1.0
483 -- End of comments
484 PROCEDURE create_contingent_task(p_api_version IN NUMBER
485 ,p_init_msg_list IN VARCHAR2
486 ,p_contract_id IN NUMBER
487 ,p_contract_number IN VARCHAR2
488 ,p_contingent_name IN VARCHAR2
489 ,p_task_owner_id IN NUMBER
490 ,p_actual_end_date IN DATE
491 ,x_return_status OUT NOCOPY VARCHAR2
492 ,x_msg_count OUT NOCOPY NUMBER
493 ,x_msg_data OUT NOCOPY VARCHAR2
494 ,x_task_id OUT NOCOPY NUMBER) IS
495 l_api_name CONSTANT VARCHAR2(30) := 'create_contingent_task';
496 l_task_id jtf_tasks_b.task_id%TYPE;
497 l_task_type_id jtf_task_types_b.task_type_id%TYPE;
498 l_task_type_name jtf_task_types_tl.name%TYPE;
499 l_status_id jtf_task_statuses_b.task_status_id%type;
500 l_status_name jtf_task_statuses_tl.name%type;
501 l_source_object_code jtf_objects_b.object_code%TYPE;
502 l_private_flag jtf_tasks_b.private_flag%TYPE;
503 l_deleted_flag jtf_tasks_b.deleted_flag%TYPE;
504 l_source_object_name jtf_tasks_b.source_object_name%TYPE;
505 l_owner_type_code jtf_objects_b.object_code%TYPE;
506 l_owner_id jtf_tasks_b.owner_id%TYPE;
507
508 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
509 -- Read OKCCONTINGENT - Contract Contingent Event
510 --Select task_type
511 Cursor task_cur is
512 select task_type_id, name
513 from jtf_task_types_vl
514 where task_type_id = 24;
515 --where name = 'OKCCONTINGENT';
516
517 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
518 --Select task status
519 Cursor status_cur is
520 select task_status_id, name
521 from jtf_task_statuses_vl
522 where task_status_id = 9;
523 --where name = 'Closed';
524
525 --Select object code
526 Cursor object_cur is
527 select object_code
528 from jtf_objects_vl
529 where object_code = 'OKC_K_HEADER'
530 and object_code in (select object_code
531 from jtf_object_usages
532 where object_user_code = 'TASK');
533
534 --Select the owner type code
535 Cursor owner_type_cur is
536 select object_code
537 from jtf_objects_vl
538 where object_code = 'OKX_TASKRES'
539 and object_code in (select object_code
540 from jtf_object_usages
541 where object_user_code = 'RESOURCES');
542
543 l_notfound BOOLEAN;
544
545 BEGIN
546 l_private_flag := 'N';
547
548 OPEN task_cur;
549 FETCH task_cur into l_task_type_id, l_task_type_name;
550 l_notfound := task_cur%NOTFOUND;
551 CLOSE task_cur;
552 if l_notfound THEN
553 x_return_status := OKC_API.G_RET_STS_ERROR;
554 OKC_API.set_message(G_APP_NAME,'OKC_TASK_TYPE_NOT_FOUND');
555 return;
556 end if;
557
558 OPEN status_cur;
559 FETCH status_cur into l_status_id, l_status_name;
560 l_notfound := status_cur%NOTFOUND;
561 CLOSE status_cur;
562 if l_notfound THEN
563 x_return_status := OKC_API.G_RET_STS_ERROR;
564 OKC_API.set_message(G_APP_NAME,'OKC_TASK_STATUS_NOT_FOUND');
565 return;
566 end if;
567
568 OPEN object_cur;
569 FETCH object_cur into l_source_object_code;
570 l_notfound := object_cur%NOTFOUND;
571 CLOSE object_cur;
572 if l_notfound THEN
573 x_return_status := OKC_API.G_RET_STS_ERROR;
574 OKC_API.set_message(G_APP_NAME,'OKC_OBJECT_CODE_NOT_FOUND');
575 return;
576 end if;
577
578 OPEN owner_type_cur;
579 FETCH owner_type_cur into l_owner_type_code;
580 l_notfound := owner_type_cur%NOTFOUND;
581 CLOSE owner_type_cur;
582 if l_notfound THEN
583 x_return_status := OKC_API.G_RET_STS_ERROR;
584 OKC_API.set_message(G_APP_NAME,'OKC_TASK_OWNER_NOT_FOUND');
585 return;
586 end if;
587
588 --Call to the procedure of the public API JTF_TASKS_PUB to create a Task
589 jtf_tasks_pub.create_task(p_api_version => p_api_version
590 ,p_init_msg_list => p_init_msg_list
591 ,p_task_id => l_task_id
592 ,p_task_name => p_contingent_name
593 ,p_task_type_name => l_task_type_name
594 ,p_task_type_id => l_task_type_id
595 ,p_task_status_name => l_status_name
596 ,p_task_status_id => l_status_id
597 ,p_actual_end_date => p_actual_end_date
598 ,p_source_object_type_code => l_source_object_code
599 ,p_source_object_name => p_contract_number
600 ,p_source_object_id => p_contract_id
601 ,p_owner_id => p_task_owner_id
602 ,p_owner_type_code => l_owner_type_code
603 ,p_private_flag => l_private_flag
604 ,x_return_status => x_return_status
605 ,x_msg_count => x_msg_count
606 ,x_msg_data => x_msg_data
607 ,x_task_id => x_task_id);
608
609 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
610 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
611 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
612 raise OKC_API.G_EXCEPTION_ERROR;
613 END IF;
614 EXCEPTION
615 WHEN OKC_API.G_EXCEPTION_ERROR THEN
616 x_return_status := 'OKC_API.G_RET_STS_ERROR';
617 NULL;
618 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
619 x_return_status := 'OKC_API.G_RET_STS_UNEXP_ERROR';
620 NULL;
621 WHEN OTHERS THEN
622 x_return_status := OKC_API.HANDLE_EXCEPTIONS
623 (l_api_name,
624 G_PKG_NAME,
625 'OTHERS',
626 x_msg_count,
627 x_msg_data,
628 '_PROCESS');
629
630 END create_contingent_task;
631
632 -- Start of comments
633 -- Procedure Name : update_task
634 -- Description : Procedure to update a Task
635 -- Version : 1.0
636 -- End of comments
637 PROCEDURE update_task(p_api_version IN NUMBER
638 ,p_init_msg_list IN VARCHAR2
639 ,p_object_version_number IN OUT NOCOPY NUMBER
640 ,p_task_id IN NUMBER
641 ,p_task_number IN NUMBER
642 ,p_workflow_process_id IN NUMBER
643 ,p_actual_end_date IN DATE
644 ,p_alarm_fired_count IN NUMBER
645 ,x_return_status OUT NOCOPY VARCHAR2
646 ,x_msg_count OUT NOCOPY NUMBER
647 ,x_msg_data OUT NOCOPY VARCHAR2) IS
648 l_api_name CONSTANT VARCHAR2(30) := 'update_task';
649 l_task_status_name jtf_tasks_v.task_status%TYPE;
650 l_task_status_id jtf_tasks_v.task_status_id%TYPE;
651 l_close_status_id jtf_tasks_v.task_status_id%TYPE;
652 l_open_status_id jtf_tasks_v.task_status_id%TYPE;
653 l_close_status_name jtf_tasks_v.task_status%TYPE;
654 l_open_status_name jtf_tasks_v.task_status%TYPE;
655 l_source_object_id jtf_tasks_b.source_object_id%TYPE;
656 l_source_object_name jtf_tasks_b.source_object_name%TYPE;
657
658 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
659 --Select task status
660 Cursor update_status_cur is
661 select task_status_id, name
662 from jtf_task_statuses_vl
663 where task_status_id = 9;
664 --where name = 'Closed';
665
666 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
667 --Select task_status
668 Cursor status_cur is
669 select task_status_id, name
670 from jtf_task_statuses_vl
671 where task_status_id = 10;
672 --where name = 'Open';
673
674 -- Get source Object Details
675 Cursor source_obj_details IS
676 Select SOURCE_OBJECT_ID
677 ,SOURCE_OBJECT_NAME
678 From jtf_tasks_b
679 Where task_id = p_task_id;
680
681 l_notfound BOOLEAN;
682
683 BEGIN
684 --If the actual date is not null then update the status to Closed
685 IF p_actual_end_date IS NOT NULL THEN
686 OPEN update_status_cur;
687 FETCH update_status_cur into l_close_status_id, l_close_status_name;
688 l_notfound := update_status_cur%NOTFOUND;
689 CLOSE update_status_cur;
690 if l_notfound THEN
691 x_return_status := OKC_API.G_RET_STS_ERROR;
692 OKC_API.set_message(G_APP_NAME,'OKC_TASK_STATUS_NOT_FOUND');
693 return;
694 end if;
695 l_task_status_name := l_close_status_name;
696 l_task_status_id := l_close_status_id;
697 ELSIF p_actual_end_date IS NULL THEN
698 OPEN status_cur;
699 FETCH status_cur into l_open_status_id, l_open_status_name;
700 l_notfound := status_cur%NOTFOUND;
701 CLOSE status_cur;
702 if l_notfound THEN
703 x_return_status := OKC_API.G_RET_STS_ERROR;
704 OKC_API.set_message(G_APP_NAME,'OKC_TASK_STATUS_NOT_FOUND');
705 return;
706 end if;
707 l_task_status_name := l_open_status_name;
708 l_task_status_id := l_open_status_id;
709 END IF;
710
711 -- Get the source object id and name
712 OPEN source_obj_details;
713 FETCH source_obj_details INTO l_source_object_id, l_source_object_name;
714 IF source_obj_details%NOTFOUND THEN
715 OKC_API.set_message(G_APP_NAME,'OKC_TASK_SOURCE_NOT_FOUND');
716 return;
717 END IF;
718 CLOSE source_obj_details;
719
720 --Call to the procedure of public API JTF_TASKS_PUB to update a task
721 jtf_tasks_pub.update_task(p_api_version => p_api_version
722 ,p_init_msg_list => p_init_msg_list
723 ,p_object_version_number => p_object_version_number
724 ,p_task_id => p_task_id
725 ,p_task_number => p_task_number
726 ,p_workflow_process_id => p_workflow_process_id
727 ,p_actual_end_date => p_actual_end_date
728 ,p_alarm_fired_count => p_alarm_fired_count
729 ,p_task_status_id => l_task_status_id
730 ,p_task_status_name => l_task_status_name
731 ,p_source_object_id => l_source_object_id
732 ,p_source_object_name => l_source_object_name
733 ,x_return_status => x_return_status
734 ,x_msg_count => x_msg_count
735 ,x_msg_data => x_msg_data);
736
737 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
738 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
739 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
740 raise OKC_API.G_EXCEPTION_ERROR;
741 END IF;
742 EXCEPTION
743 WHEN OKC_API.G_EXCEPTION_ERROR THEN
744 x_return_status := 'OKC_API.G_RET_STS_ERROR';
745 NULL;
746 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
747 x_return_status := 'OKC_API.G_RET_STS_UNEXP_ERROR';
748 NULL;
749 WHEN OTHERS THEN
750 OKC_API.set_message(p_app_name => g_app_name,
751 p_msg_name => g_unexpected_error,
752 p_token1 => g_sqlcode_token,
753 p_token1_value => sqlcode,
754 p_token2 => g_sqlerrm_token,
755 p_token2_value => sqlerrm);
756 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
757
758 END update_task;
759
760 -- Start of comments
761 -- Procedure Name : delete_task
762 -- Description : Procedure to delete a Task/s
763 -- Version : 1.0
764 -- End of comments
765 --Pass the p_tve_id(Time value ID) to delete multiple tasks(ex: When a rule is deleted)
766 --Pass the p_rtv_id(Resolved Time ID) to delete a single task(ex: When a contract is terminated)
767 PROCEDURE delete_task(p_api_version IN NUMBER
768 ,p_init_msg_list IN VARCHAR2
769 ,p_tve_id IN NUMBER
770 ,p_rtv_id IN NUMBER
771 ,x_return_status OUT NOCOPY VARCHAR2
772 ,x_msg_count OUT NOCOPY NUMBER
773 ,x_msg_data OUT NOCOPY VARCHAR2) IS
774
775 l_api_name CONSTANT VARCHAR2(30) := 'delete_task';
776
777 --Select task for a given resolved timevalue id
778 Cursor delete_tasks_cur(p_rtv_id IN NUMBER, p_status_id IN NUMBER) IS
779 select jtf.task_id, jtf.task_number, jtf.object_version_number
780 from jtf_tasks_b jtf
781 where jtf.source_object_id = p_rtv_id
782 and jtf.source_object_type_code = 'OKC_RESTIME'
783 and jtf.task_status_id = p_status_id;
784
785 -- Replaced name with seeded ids to avoid translation issues - Bug 1683539
786 --Select task status
787 Cursor delete_status_cur is
788 select task_status_id
789 from jtf_task_statuses_vl
790 where task_status_id = 10;
791 --where name = 'Open';
792
793 --Select all the resolved time values for a given timevalue id
794 Cursor delete_rule_cur(p_tve_id IN NUMBER) IS
795 select rtv.id
796 from okc_resolved_timevalues rtv
797 where rtv.tve_id = p_tve_id;
798
799 TYPE delete_rec_type IS RECORD(
800 task_id jtf_tasks_b.task_id%TYPE,
801 task_number jtf_tasks_b.task_number%TYPE,
802 object_version_number jtf_tasks_b.object_version_number%TYPE);
803 TYPE delete_tasks_tbl_type IS TABLE OF delete_rec_type
804 INDEX BY BINARY_INTEGER;
805 delete_tasks_tbl delete_tasks_tbl_type;
806
807 delete_ctr NUMBER := 0;
808 i NUMBER := 0;
809 l_status_id jtf_tasks_b.task_status_id%TYPE;
810 l_task_id jtf_tasks_b.task_id%TYPE;
811 l_task_number jtf_tasks_b.task_number%TYPE;
812 l_object_version_number jtf_tasks_b.object_version_number%TYPE;
813 BEGIN
814 --If the timevalue ID is not null then delete all the tasks for a rule with status = 'Open'
815 IF p_tve_id IS NOT NULL THEN
816 OPEN delete_status_cur;
817 FETCH delete_status_cur into l_status_id;
818 CLOSE delete_status_cur;
819
820 FOR delete_rule_rec in delete_rule_cur(p_tve_id) LOOP
821 FOR delete_tasks_rec in delete_tasks_cur(p_rtv_id => delete_rule_rec.id,
822 p_status_id => l_status_id) LOOP
823 delete_ctr := delete_ctr + 1;
824 delete_tasks_tbl (delete_ctr).task_id := delete_tasks_rec.task_id;
825 delete_tasks_tbl (delete_ctr).task_number := delete_tasks_rec.task_number;
826 delete_tasks_tbl (delete_ctr).object_version_number := delete_tasks_rec.object_version_number;
827 END LOOP;
828 END LOOP;
829
830 IF delete_tasks_tbl.COUNT > 0 THEN
831 i := delete_tasks_tbl.FIRST;
832 LOOP
833 --Call the procedure of public API JTF_TASKS_PUB to delete tasks
834 jtf_tasks_pub.delete_task(p_api_version => p_api_version
835 ,p_init_msg_list => p_init_msg_list
836 ,p_object_version_number => delete_tasks_tbl(i).object_version_number
837 ,p_task_id => delete_tasks_tbl(i).task_id
838 ,p_task_number => delete_tasks_tbl(i).task_number
839 ,x_return_status => x_return_status
840 ,x_msg_count => x_msg_count
841 ,x_msg_data => x_msg_data);
842 EXIT WHEN (i = delete_tasks_tbl.LAST);
843 i := delete_tasks_tbl.NEXT(i);
844 END LOOP;
845 END IF;
846 END IF;
847
848 --If the resolved timevalue id is not null then delete a single task
849 -- where source_object_id(JTF_TASKS_B) = p_rtv_id(resolved timevalue ID)
850 -- and the status is OKCOPEN
851 IF p_rtv_id IS NOT NULL THEN
852 OPEN delete_status_cur;
853 FETCH delete_status_cur into l_status_id;
854 CLOSE delete_status_cur;
855 FOR delete_tasks_rec in delete_tasks_cur(p_rtv_id => p_rtv_id, p_status_id => l_status_id) LOOP
856 delete_ctr := delete_ctr + 1;
857 delete_tasks_tbl (delete_ctr).task_id := delete_tasks_rec.task_id;
858 delete_tasks_tbl (delete_ctr).task_number := delete_tasks_rec.task_number;
859 delete_tasks_tbl (delete_ctr).object_version_number := delete_tasks_rec.object_version_number;
860 END LOOP;
861
862 IF delete_tasks_tbl.COUNT > 0 THEN
863 i := delete_tasks_tbl.FIRST;
864 LOOP
865 --Call to the procedure of public API JTF_TASKS_PUB to delete tasks
866 jtf_tasks_pub.delete_task(p_api_version => p_api_version
867 ,p_init_msg_list => p_init_msg_list
868 ,p_object_version_number => delete_tasks_tbl(i).object_version_number
869 ,p_task_id => delete_tasks_tbl(i).task_id
870 ,p_task_number => delete_tasks_tbl(i).task_number
871 ,x_return_status => x_return_status
872 ,x_msg_count => x_msg_count
873 ,x_msg_data => x_msg_data);
874 EXIT WHEN (i = delete_tasks_tbl.LAST);
875 i := delete_tasks_tbl.NEXT(i);
876 END LOOP;
877 END IF;
878 END IF;
879
880 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
881 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
883 raise OKC_API.G_EXCEPTION_ERROR;
884 END IF;
885 EXCEPTION
886 WHEN OKC_API.G_EXCEPTION_ERROR THEN
887 x_return_status := 'OKC_API.G_RET_STS_ERROR';
888 NULL;
889 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
890 x_return_status := 'OKC_API.G_RET_STS_UNEXP_ERROR';
891 NULL;
892 WHEN OTHERS THEN
893 OKC_API.set_message(p_app_name => g_app_name,
894 p_msg_name => g_unexpected_error,
895 p_token1 => g_sqlcode_token,
896 p_token1_value => sqlcode,
897 p_token2 => g_sqlerrm_token,
898 p_token2_value => sqlerrm);
899 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
900
901
902 END delete_task;
903 END OKC_TASK_PVT;