[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_RESOURCES_PVT
Source
1 PACKAGE BODY JTF_TASK_RESOURCES_PVT AS
2 /* $Header: jtfvtkrb.pls 115.26 2002/12/05 00:08:31 cjang ship $ */
3
4
5
6 Function validate_resource_type_code
7 (p_resource_type_code in varchar2 ) return boolean
8
9
10 is
11 cursor c_resource_type_code is
12 select 1 from jtf_objects_vl
13 where object_code = p_resource_type_code ;
14
15 x char ;
16 begin
17 open c_resource_type_code ;
18 fetch c_resource_type_code into x ;
19 if c_resource_type_code%notfound then
20 close c_resource_type_code ;
21 return FALSE ;
22 else
23 close c_resource_type_code ;
24 return true ;
25 end if ;
26 end ;
27
28 PROCEDURE validate_task_template (
29 x_return_status OUT NOCOPY VARCHAR2 ,
30 p_task_template_id IN NUMBER DEFAULT NULL ,
31 p_task_name IN VARCHAR2 DEFAULT NULL ,
32 x_task_template_id OUT NOCOPY NUMBER ,
33 x_task_name OUT NOCOPY VARCHAR2
34 )
35 IS
36 CURSOR c_task_id
37 IS
38 SELECT task_template_id ,task_name
39 FROM jtf_task_templates_tl
40 WHERE task_template_id = p_task_template_id
41 OR task_name = p_task_name;
42
43
44
45 l_task_template_id jtf_task_templates_tl.task_template_id%TYPE;
46 l_task_name jtf_task_templates_tl.task_name%TYPE;
47 done BOOLEAN := FALSE;
48 BEGIN
49 x_return_status := fnd_api.g_ret_sts_success;
50
51 --- Assume correct task id is supplied
52 IF p_task_template_id IS NOT NULL
53 THEN
54 OPEN c_task_id;
55 FETCH c_task_id INTO l_task_template_id,l_task_name;
56
57
58 IF c_task_id%NOTFOUND
59 THEN
60 x_return_status := fnd_api.g_ret_sts_unexp_error;
61 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TEMP_ID');
62 fnd_message.set_token('TASK_TEMPLATE_ID',P_TASK_TEMPLATE_ID);
63 fnd_msg_pub.add;
64
65
66 END IF;
67
68 END IF;
69
70 x_task_template_id := l_task_template_id;
71 x_task_name := l_task_name;
72
73 END;
74
75
76 PROCEDURE validate_task_type (
77 x_return_status OUT NOCOPY VARCHAR2 ,
78 p_task_type_id IN NUMBER DEFAULT NULL ,
79 p_name IN VARCHAR2 DEFAULT NULL ,
80 x_task_type_id OUT NOCOPY NUMBER ,
81 x_task_name OUT NOCOPY VARCHAR2 )
82 IS
83 CURSOR c_task_type_id
84 IS
85 SELECT task_type_id ,name
86 FROM jtf_task_types_tl
87 WHERE task_type_id = p_task_type_id
88 OR name= p_name;
89
90
91
92 l_task_type_id jtf_task_types_tl.task_type_id%TYPE;
93 l_task_name jtf_task_types_tl.name%TYPE;
94 done BOOLEAN := FALSE;
95 BEGIN
96 x_return_status := fnd_api.g_ret_sts_success;
97
98 --- Assume correct task type id is supplied
99 IF p_task_type_id IS NOT NULL
100 THEN
101 OPEN c_task_type_id;
102 FETCH c_task_type_id INTO l_task_type_id,l_task_name;
103
104
105 IF c_task_type_id%NOTFOUND
106 THEN
107 x_return_status := fnd_api.g_ret_sts_unexp_error;
108 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TYPE_ID');
109 fnd_message.set_token('TASK_TYPE_ID',P_TASK_TYPE_ID);
110 fnd_msg_pub.add;
111
112
113 END IF;
114
115 END IF;
116
117 x_task_type_id := l_task_type_id;
118 x_task_name:=l_task_name;
119
120 END;
121
122
123 Procedure VALIDATE_ENABLED_FLAG
124 (L_API_NAME IN VARCHAR2,
125 P_FLAG IN VARCHAR2,
126 P_FLAG_NAME IN VARCHAR2) IS
127
128 BEGIN
129 if (p_flag is not null) then
130 if (p_flag not in ('Y','N')) then
131 raise fnd_api.g_exc_error;
132 end if;
133 end if;
134 END;
135
136
137 procedure dump_long_line(txt in varchar2, v_str in varchar2) is
138 ln integer := length(v_str);
139 st integer := 1;
140 begin
141
142 loop
143
144 st := st + 72;
145 exit when (st >= ln);
146 end loop;
147 end dump_long_line;
148
149
150 Procedure CREATE_TASK_RSRC_REQ
151 (P_API_VERSION IN NUMBER ,
152 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
153 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
154 P_TASK_ID IN NUMBER DEFAULT NULL ,
155 P_TASK_NAME IN VARCHAR2 DEFAULT NULL ,
156 P_TASK_NUMBER IN VARCHAR2 DEFAULT NULL ,
157 P_TASK_TYPE_ID IN NUMBER DEFAULT NULL ,
158 P_TASK_TYPE_NAME IN VARCHAR2 DEFAULT NULL ,
159 P_TASK_TEMPLATE_ID IN NUMBER DEFAULT NULL ,
160 P_TASK_TEMPLATE_NAME IN VARCHAR2 DEFAULT NULL ,
161 P_RESOURCE_TYPE_CODE IN VARCHAR2 ,
162 P_REQUIRED_UNITS IN NUMBER ,
163 P_ENABLED_FLAG IN VARCHAR2 DEFAULT jtf_task_utl.g_no ,
164 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
165 X_MSG_COUNT OUT NOCOPY NUMBER ,
166 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
167 X_RESOURCE_REQ_ID OUT NOCOPY NUMBER ,
168 p_attribute1 IN VARCHAR2 DEFAULT null,
169 p_attribute2 IN VARCHAR2 DEFAULT null,
170 p_attribute3 IN VARCHAR2 DEFAULT null,
171 p_attribute4 IN VARCHAR2 DEFAULT null,
172 p_attribute5 IN VARCHAR2 DEFAULT null,
173 p_attribute6 IN VARCHAR2 DEFAULT null,
174 p_attribute7 IN VARCHAR2 DEFAULT null,
175 p_attribute8 IN VARCHAR2 DEFAULT null,
176 p_attribute9 IN VARCHAR2 DEFAULT null,
177 p_attribute10 IN VARCHAR2 DEFAULT null,
178 p_attribute11 IN VARCHAR2 DEFAULT null,
179 p_attribute12 IN VARCHAR2 DEFAULT null,
180 p_attribute13 IN VARCHAR2 DEFAULT null,
181 p_attribute14 IN VARCHAR2 DEFAULT null,
182 p_attribute15 IN VARCHAR2 DEFAULT null,
183 p_attribute_category IN VARCHAR2 DEFAULT null ) IS
184
185
186
187 --
188
189 --Declare the variables
190 --
191
192 l_api_version constant number := 1.0 ;
193 l_api_name constant varchar2(30) := 'CREATE_TASK_RSRC_REQ' ;
194 l_return_status varchar2(1) := fnd_api.g_ret_sts_success ;
195 l_task_id jtf_tasks_b.task_id%type := P_TASK_ID ;
196 l_task_number jtf_tasks_b.task_number%type := P_TASK_NUMBER ;
197 l_task_name jtf_tasks_tl.task_name%type := P_TASK_NAME ;
198 l_task_type_id jtf_task_types_b.task_type_id%type := P_TASK_TYPE_ID ;
199 l_task_type_name jtf_task_types_tl.name%type := P_TASK_TYPE_NAME ;
200 l_task_template_id jtf_task_templates_b.task_template_id%type:= P_TASK_TEMPLATE_ID ;
201 l_task_template_name jtf_task_templates_tl.task_name%type := P_TASK_TEMPLATE_NAME ;
202 l_enabled_flag jtf_task_rsc_reqs.enabled_flag%type := P_ENABLED_FLAG ;
203 l_resource_type_code jtf_task_rsc_reqs.resource_type_code%type:= P_RESOURCE_TYPE_CODE ;
204 l_required_units jtf_task_rsc_reqs.required_units%type := P_REQUIRED_UNITS ;
205 --l_resp_appl_id NUMBER := p_resp_appl_id ;
206 --l_resp_id NUMBER := p_resp_id ;
207 --l_user_id NUMBER := p_user_id ;
208 --l_login_id NUMBER := p_login_id ;
209 l_msg_data VARCHAR2(2000) ;
210 l_msg_count NUMBER ;
211 x char ;
212 l_resource_req_id NUMBER;
213 l_rowid rowid;
214
215
216 cursor rr_cur3 (l_rowid in rowid ) is
217 select 1 from jtf_task_rsc_reqs
218 where rowid = l_rowid ;
219
220 BEGIN
221
222
223 savepoint create_task_resource_pvt ;
224
225 x_return_status := fnd_api.g_ret_sts_success ;
226
227
228
229 --if p_enabled_flag = jtf_task_utl.g_yes then
230
231
232 select JTF_TASK_RSC_REQS_S.nextval into l_resource_req_id
233 from dual ;
234
235
236
237
238
239 JTF_TASK_RSC_REQS_PKG.INSERT_ROW (
240 X_ROWID => l_rowid,
241 X_RESOURCE_REQ_ID => l_resource_req_id,
242 X_TASK_TYPE_ID => l_task_type_id,
243 X_TASK_ID => l_task_id,
244 X_TASK_TEMPLATE_ID => l_task_template_id,
245 X_REQUIRED_UNITS => l_required_units,
246 X_ENABLED_FLAG => l_enabled_flag,
247 x_attribute1 => p_attribute1 ,
248 x_attribute2 => p_attribute2 ,
249 x_attribute3 => p_attribute3 ,
250 x_attribute4 => p_attribute4 ,
251 x_attribute5 => p_attribute5 ,
252 x_attribute6 => p_attribute6 ,
253 x_attribute7 => p_attribute7 ,
254 x_attribute8 => p_attribute8 ,
255 x_attribute9 => p_attribute9 ,
256 x_attribute10 => p_attribute10 ,
257 x_attribute11 => p_attribute11 ,
258 x_attribute12 => p_attribute12 ,
259 x_attribute13 => p_attribute13 ,
260 x_attribute14 => p_attribute14 ,
261 x_attribute15 => p_attribute15,
262 x_attribute_category => p_attribute_category ,
263 X_RESOURCE_TYPE_CODE =>l_resource_type_code,
264 X_CREATION_DATE => sysdate ,
265 X_CREATED_BY => jtf_task_utl.created_by ,
266 X_LAST_UPDATE_DATE => sysdate ,
267 X_LAST_UPDATED_BY => -1 ,
268 X_LAST_UPDATE_LOGIN => jtf_task_utl.login_id );
269
270
271
272
273 open rr_cur3 (l_rowid) ;
274 fetch rr_cur3 into x ;
275
276 IF rr_cur3%notfound THEN
277
278 x_return_status := fnd_api.g_ret_sts_unexp_error ;
279 fnd_message.set_name('JTF' ,'JTF_TASK_INSERTING_RESOURCE') ;
280 fnd_msg_pub.add ;
281 raise fnd_api.g_exc_unexpected_error ;
282
283 ELSE
284
285 X_RESOURCE_REQ_ID := l_resource_req_id ;
286 end if ;
287
288 exception
289 when fnd_api.g_exc_unexpected_error then
290
291 rollback to create_task_resource_pvt ;
292 x_return_status := fnd_api.g_ret_sts_unexp_error ;
293 fnd_msg_pub.count_and_get ( p_count => x_msg_count ,
294 p_data => x_msg_data );
295 when others then
296
297 rollback to create_task_resource_pvt ;
298 x_return_status := fnd_api.g_ret_sts_unexp_error ;
299
300 fnd_msg_pub.count_and_get ( p_count => x_msg_count ,
301 p_data => x_msg_data );
302 END;
303
304
305
306
307
308
309
310 --Procedure to Update the Task Resource Requirements
311
312
313 Procedure UPDATE_TASK_RSCR_REQ
314 (P_API_VERSION IN NUMBER ,
315 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER ,
316 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
317 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
318 P_RESOURCE_REQ_ID IN NUMBER ,
319 P_TASK_ID IN NUMBER default null ,
320 P_TASK_NAME IN VARCHAR2 default null ,
321 P_TASK_NUMBER IN VARCHAR2 default null ,
322 P_TASK_TYPE_ID IN NUMBER default null ,
323 P_TASK_TYPE_NAME IN VARCHAR2 ,
324 P_TASK_TEMPLATE_ID IN NUMBER default null ,
325 P_TASK_TEMPLATE_NAME IN VARCHAR2 ,
326 P_RESOURCE_TYPE_CODE IN VARCHAR2 ,
327 P_REQUIRED_UNITS IN NUMBER ,
328 P_ENABLED_FLAG IN VARCHAR2 DEFAULT jtf_task_utl.g_no ,
329 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
330 X_MSG_COUNT OUT NOCOPY NUMBER ,
331 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
332 p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
333 p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
334 p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
335 p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
336 p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
337 p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
338 p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
339 p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
340 p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
341 p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
342 p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
343 p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
344 p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
345 p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
346 p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char,
347 p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char ) IS
348
349
353 l_api_version constant number := 1.0 ;
350 --Declare the variables
351 --
352
354 l_api_name constant varchar2(30) := 'CREATE_TASK_RSRC_REQ';
355 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
356 l_task_id jtf_tasks_b.task_id%type := P_TASK_ID ;
357 l_task_number jtf_tasks_b.task_number%type := P_TASK_NUMBER ;
358 l_task_name jtf_tasks_tl.task_name%type := P_TASK_NAME ;
359 l_task_type_id jtf_task_types_b.task_type_id%type := P_TASK_TYPE_ID ;
360 l_task_type_name jtf_task_types_tl.name%type := P_TASK_TYPE_NAME ;
361 l_task_template_id jtf_task_templates_b.task_template_id%type := P_TASK_TEMPLATE_ID ;
362 l_task_template_name jtf_task_templates_tl.task_name%type := P_TASK_TEMPLATE_NAME ;
363 l_enabled_flag jtf_task_rsc_reqs.enabled_flag%type := P_ENABLED_FLAG ;
364 l_resource_type_code jtf_task_rsc_reqs.resource_type_code%type := P_RESOURCE_TYPE_CODE ;
365 l_required_units jtf_task_rsc_reqs.required_units%type := P_REQUIRED_UNITS ;
366 --l_resp_appl_id NUMBER := p_resp_appl_id ;
367 --l_resp_id NUMBER := p_resp_id ;
368 --l_user_id NUMBER := p_user_id ;
369 --l_login_id NUMBER := p_login_id ;
370 l_msg_data VARCHAR2(2000) ;
371 l_msg_count NUMBER ;
372 x CHAR ;
373 l_resource_req_id NUMBER := p_resource_req_id ;
374 l_rowid rowid ;
375
376
377
378
379
380 cursor trsr_get_cur is
381 select
382 P_TASK_ID task_id ,
383 P_TASK_TYPE_ID task_type_id ,
384 P_TASK_TEMPLATE_ID task_template_id ,
385 P_RESOURCE_TYPE_CODE resource_type_code ,
386 REQUIRED_UNITS required_units,
387 ENABLED_FLAG enabled_flag,
388 decode( p_attribute1 , fnd_api.g_miss_char , attribute1 , p_attribute1 ) attribute1 ,
389 decode( p_attribute2 , fnd_api.g_miss_char , attribute2 , p_attribute2 ) attribute2 ,
390 decode( p_attribute3 , fnd_api.g_miss_char , attribute3 , p_attribute3 ) attribute3 ,
391 decode( p_attribute4 , fnd_api.g_miss_char , attribute4 , p_attribute4 ) attribute4 ,
392 decode( p_attribute5 , fnd_api.g_miss_char , attribute5 , p_attribute5 ) attribute5 ,
393 decode( p_attribute6 , fnd_api.g_miss_char , attribute6 , p_attribute6 ) attribute6 ,
394 decode( p_attribute7 , fnd_api.g_miss_char , attribute7 , p_attribute7 ) attribute7 ,
395 decode( p_attribute8 , fnd_api.g_miss_char , attribute8 , p_attribute8 ) attribute8 ,
396 decode( p_attribute9 , fnd_api.g_miss_char , attribute9 , p_attribute9 ) attribute9 ,
397 decode( p_attribute10 , fnd_api.g_miss_char , attribute10 , p_attribute10 ) attribute10 ,
398 decode( p_attribute11 , fnd_api.g_miss_char , attribute11 , p_attribute11 ) attribute11 ,
399 decode( p_attribute12 , fnd_api.g_miss_char , attribute12 , p_attribute12 ) attribute12 ,
400 decode( p_attribute13 , fnd_api.g_miss_char , attribute13 , p_attribute13 ) attribute13 ,
401 decode( p_attribute14 , fnd_api.g_miss_char , attribute14 , p_attribute14 ) attribute14 ,
402 decode( p_attribute15 , fnd_api.g_miss_char , attribute15 , p_attribute15 ) attribute15 ,
403 decode( p_attribute_category,fnd_api.g_miss_char,attribute_category,p_attribute_category) attribute_category
404 from jtf_task_rsc_reqs
405 where resource_req_id = l_resource_req_id ;
406
407 x char ;
408
409
410
411 task_res trsr_get_cur%rowtype ;
412
413 BEGIN
414
415
416 savepoint update_task_resource_pvt ;
417
418 x_return_status := fnd_api.g_ret_sts_success ;
419
420
421
422 open trsr_get_cur ;
423 fetch trsr_get_cur into task_res ;
424
425 if trsr_get_cur%notfound then
426 fnd_message.set_name( 'JTF', 'JTF_TASK_INV_RES_REQ_ID') ;
427 fnd_msg_pub.add ;
428 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
429 raise fnd_api.g_exc_unexpected_error ;
430 end if ;
431
432 --l_required_units := task_res.REQUIRED_UNITS ;
433
434 l_enabled_flag := task_res.ENABLED_FLAG ;
435
436 if L_ENABLED_FLAG IS NULL THEN
437 L_ENABLED_FLAG := jtf_task_utl.g_no ;
438 END IF ;
439
440 if l_task_id IS NULL then
441
442 l_task_id := task_res.task_id ;
443 end if ;
444
445 if l_task_template_id IS NULL then
446
447 l_task_template_id := task_res.task_template_id ;
448 end if ;
449
450 if l_task_type_id IS NULL then
451
452 l_task_type_id := task_res.task_type_id ;
453 end if ;
454
455
456
457 /* if validate_resource_type_code ( p_resource_type_code =>l_resource_type_code) then
458 l_resource_type_code:=task_res.resource_type_code;
459
460 else
461 raise fnd_api.g_exc_error ;
462
463 end if ; */
464
468
465 --Task can be updated only if it is active
466
467 --if l_enabled_flag = fnd_api.G_true then
469
470 jtf_task_resources_pub.lock_task_resources
471 ( P_API_VERSION => 1.0 ,
472 P_INIT_MSG_LIST => fnd_api.g_false ,
473 P_COMMIT => fnd_api.g_false ,
474 P_RESOURCE_REQUIREMENT_ID => l_resource_req_id ,
475 P_OBJECT_VERSION_NUMBER => p_object_version_number,
476 X_RETURN_STATUS => x_return_status ,
477 X_MSG_DATA => x_msg_data ,
478 X_MSG_COUNT => x_msg_count ) ;
479
480 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
481 THEN
482 x_return_status := fnd_api.g_ret_sts_unexp_error;
483 RAISE fnd_api.g_exc_unexpected_error;
484 END IF;
485
486
487 p_object_version_number := p_object_version_number + 1 ;
488
489
490 JTF_TASK_RSC_REQS_PKG.UPDATE_ROW (
491 X_RESOURCE_REQ_ID =>l_resource_req_id,
492 X_TASK_TYPE_ID =>l_task_type_id,
493 X_TASK_ID => l_task_id ,
494 X_TASK_TEMPLATE_ID => l_task_template_id,
495 X_REQUIRED_UNITS => l_required_units,
496 X_ENABLED_FLAG => l_enabled_flag,
497 X_OBJECT_VERSION_NUMBER => p_object_version_number,
498 x_attribute1 => task_res.attribute1 ,
499 x_attribute2 => task_res.attribute2 ,
500 x_attribute3 => task_res.attribute3 ,
501 x_attribute4 => task_res.attribute4 ,
502 x_attribute5 => task_res.attribute5 ,
503 x_attribute6 => task_res.attribute6 ,
504 x_attribute7 => task_res.attribute7 ,
505 x_attribute8 => task_res.attribute8 ,
506 x_attribute9 => task_res.attribute9 ,
507 x_attribute10 => task_res.attribute10 ,
508 x_attribute11 => task_res.attribute11 ,
509 x_attribute12 => task_res.attribute12 ,
510 x_attribute13 => task_res.attribute13 ,
511 x_attribute14 => task_res.attribute14 ,
512 x_attribute15 => task_res.attribute15 ,
513 x_attribute_category => task_res.attribute_category ,
514 X_RESOURCE_TYPE_CODE => l_resource_type_code,
515 X_LAST_UPDATE_DATE =>sysdate,
516 X_LAST_UPDATED_BY => -1 ,
517 X_LAST_UPDATE_LOGIN =>jtf_task_utl.login_id
518 );
519
520
521
522
523 -- end if;
524
525
526 if trsr_get_cur%isopen then
527 close trsr_get_cur ;
528 end if ;
529
530 if fnd_api.to_boolean(p_commit) then
531 commit work ;
532 end if ;
533
534 -- fnd_msg_pub.count_and_get( p_count => x_msg_count ,
535 --p_data => x_msg_data ) ;
536
537
538 exception
539 when fnd_api.g_exc_unexpected_error then
540 if trsr_get_cur%isopen then
541 close trsr_get_cur ;
542 end if ;
543 rollback to update_task_resource_pvt ;
544 fnd_message.set_name( 'JTF', 'JTF_TASK_UPD_RES_REQ_ID') ;
545 fnd_message.set_token('RESOURCE_REQ_ID',P_RESOURCE_REQ_ID);
546 fnd_msg_pub.add ;
547 x_return_status := fnd_api.g_ret_sts_unexp_error ;
548 fnd_msg_pub.count_and_get ( p_count => x_msg_count ,
549 p_data => x_msg_data );
550 when others then
551 if trsr_get_cur%isopen then
552 close trsr_get_cur ;
553 end if ;
554 rollback to update_task_resource_pvt ;
555 x_return_status := fnd_api.g_ret_sts_unexp_error ;
556
557 fnd_msg_pub.count_and_get ( p_count => x_msg_count ,
558 p_data => x_msg_data );
559 end ;
560
561
562
563 --Procedure to Delete the Task Resource Requirements
564
565
566
567 Procedure DELETE_TASK_RSRC_REQ
568 (P_API_VERSION IN NUMBER ,
569 P_OBJECT_VERSION_NUMBER IN NUMBER ,
570 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
571 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
572 P_RESOURCE_REQ_ID IN NUMBER ,
573 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
574 X_MSG_COUNT OUT NOCOPY NUMBER ,
575 X_MSG_DATA OUT NOCOPY VARCHAR2 ) IS
576
577
578
579
580 l_resource_req_id jtf_task_rsc_reqs.resource_req_id%TYPE := p_resource_req_id ;
581
582
583
584 x char;
585
586 cursor c_res_req_del is
587 select 1
588 from jtf_task_rsc_reqs
589 where resource_req_id = l_resource_req_id ;
590
591
592 begin
593
594 savepoint delete_task_resource_pvt ;
595
596 x_return_status := fnd_api.g_ret_sts_success ;
597
598 ---call the table handler to delete the resource req
599
600
601 jtf_task_resources_pub.lock_task_resources
602 ( P_API_VERSION => 1.0 ,
603 P_INIT_MSG_LIST => fnd_api.g_false ,
604 P_COMMIT => fnd_api.g_false ,
605 P_RESOURCE_REQUIREMENT_ID => l_resource_req_id ,
609 X_MSG_COUNT => x_msg_count ) ;
606 P_OBJECT_VERSION_NUMBER => p_object_version_number,
607 X_RETURN_STATUS => x_return_status ,
608 X_MSG_DATA => x_msg_data ,
610
611 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
612 THEN
613 x_return_status := fnd_api.g_ret_sts_unexp_error;
614 RAISE fnd_api.g_exc_unexpected_error;
615 END IF;
616
617
618 JTF_TASK_RSC_REQS_PKG.DELETE_ROW
619 ( X_RESOURCE_REQ_ID => l_resource_req_id );
620
621 open c_res_req_del;
622 fetch c_res_req_del into x ;
623
624 if c_res_req_del%found then
625 fnd_message.set_name( 'JTF', 'JTF_TASK_DELETING_RES_REQ_ID') ;
626 fnd_msg_pub.add ;
627 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
628 raise fnd_api.g_exc_unexpected_error ;
629 close c_res_req_del ;
630
631
632 else
633 close c_res_req_del ;
634 end if ;
635
636 if c_res_req_del%isopen then
637 close c_res_req_del ;
638 end if;
639
640 if fnd_api.to_boolean(p_commit) then
641 commit work ;
642 end if ;
643
644 fnd_msg_pub.count_and_get( p_count => x_msg_count ,
645 p_data => x_msg_data ) ;
646
647
648 exception
649 when fnd_api.g_exc_unexpected_error then
650 rollback to delete_task_resource_pvt ;
651 x_return_status := fnd_api.g_ret_sts_unexp_error ;
652 fnd_msg_pub.count_and_get ( p_count => x_msg_count ,
653 p_data => x_msg_data );
654
655
656 when others then
657 rollback to delete_task_resource_pvt;
658 x_return_status := fnd_api.g_ret_sts_unexp_error ;
659
660 fnd_msg_pub.count_and_get ( p_count => x_msg_count ,
661 p_data => x_msg_data );
662 end ;
663
664
665
666
667
668
669 --Procedure to get the Task Resource Req
670
671 Procedure GET_TASK_RSRC_REQ
672 (
673 P_API_VERSION IN NUMBER ,
674 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT G_FALSE ,
675 P_COMMIT IN VARCHAR2 DEFAULT G_FALSE ,
676 P_RESOURCE_REQ_ID IN NUMBER ,
677 P_RESOURCE_REQ_NAME IN VARCHAR2 DEFAULT NULL ,
678 P_TASK_ID IN NUMBER DEFAULT NULL ,
679 P_TASK_NAME IN VARCHAR2 DEFAULT NULL ,
680 P_TASK_TYPE_ID IN NUMBER DEFAULT NULL ,
681 P_TASK_TYPE_NAME IN VARCHAR2 DEFAULT NULL ,
682 P_TASK_TEMPLATE_ID IN NUMBER DEFAULT NULL ,
683 P_TASK_TEMPLATE_NAME IN VARCHAR2 DEFAULT NULL ,
684 P_SORT_DATA IN JTF_TASK_RESOURCES_PUB.SORT_DATA,
685 P_QUERY_OR_NEXT_CODE IN VARCHAR2 default 'Q' ,
686 P_START_POINTER IN NUMBER ,
687 P_REC_WANTED IN NUMBER ,
688 P_SHOW_ALL IN VARCHAR2 default 'Y' ,
689 P_RESOURCE_TYPE_CODE IN VARCHAR2 ,
690 P_REQUIRED_UNITS IN NUMBER ,
691 P_ENABLED_FLAG IN VARCHAR2 DEFAULT jtf_task_utl.g_no ,
692 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
693 X_MSG_COUNT OUT NOCOPY NUMBER ,
694 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
695 X_TASK_RSC_REQ_REC OUT NOCOPY JTF_TASK_RESOURCES_PUB.TASK_RSC_REQ_TBL,
696 X_TOTAL_RETRIEVED OUT NOCOPY NUMBER ,
697 X_TOTAL_RETURNED OUT NOCOPY NUMBER ) IS
698
699 -- declare variables
700 l_api_name varchar2(30) := 'GET_TASK_RSRC_REQ';
701 v_cursor_id integer;
702 v_dummy integer;
703 v_cnt integer;
704 v_end integer;
705 v_start integer;
706 v_type jtf_task_resources_pub.task_rsc_req_rec;
707 v_tbl jtf_task_resources_pub.task_rsc_req_tbl;
708 v_select varchar2(2000);
709
710
711
712
713 Procedure create_sql_statement is
714
715 v_index integer;
716 v_first integer;
717 v_comma varchar2(5);
718 v_where varchar2(2000);
719 -- v_select varchar2(2000);
720 v_and char(1) := 'N';
721
722 procedure add_to_sql (p_in varchar2, --value in parameter
723 p_bind varchar2, --bind variable to use
724 p_field varchar2 --field associated with parameter
725
726 ) is
727 v_str varchar2(10);
728 begin
729 -- add_to_sql
730 if (p_in is not null) then
731 if (v_and = 'N') then
732 v_str := ' ';
733
734 v_and := 'Y';
735 else
736 v_str := ' and ';
737 end if;
738 v_where := v_where || v_str ||
739 p_field || ' = :' ||
740 p_bind;
741 end if;
742 end add_to_sql;
743
744 begin
745
746 --create_sql_statement
747
748 v_select := 'select '||'RESOURCE_REQ_ID,'||
752 'RESOURCE_TYPE_CODE,'||
749 'TASK_TYPE_ID,'||
750 'TASK_ID,'||
751 'TASK_TEMPLATE_ID,'||
753 'REQUIRED_UNITS,'||
754 'ENABLED_FLAG,'||
755 'ATTRIBUTE1,'||
756 'ATTRIBUTE2,'||
757 'ATTRIBUTE3,'||
758 'ATTRIBUTE4,'||
759 'ATTRIBUTE5,'||
760 'ATTRIBUTE6,'||
761 'ATTRIBUTE7,'||
762 'ATTRIBUTE8,'||
763 'ATTRIBUTE9,'||
764 'ATTRIBUTE10,'||
765 'ATTRIBUTE11,'||
766 'ATTRIBUTE12,'||
767 'ATTRIBUTE13,'||
768 'ATTRIBUTE14,'||
769 'ATTRIBUTE15,'||
770 'ATTRIBUTE_CATEGORY '||
771 'from jtf_task_rsc_reqs ';
772
773 add_to_sql(to_char(P_RESOURCE_REQ_ID),'b1', 'resource_req_id');
774 add_to_sql(to_char(P_TASK_TYPE_ID),'b2', 'task_type_id');
775 add_to_sql(to_char(P_TASK_ID),'b3', 'task_id');
776 add_to_sql(to_char(P_TASK_TEMPLATE_ID),'b4', 'task_template_id');
777 add_to_sql(P_RESOURCE_TYPE_CODE,'b5', 'resource_type_code');
778 add_to_sql(to_char(P_REQUIRED_UNITS),'b6', 'required_units');
779 add_to_sql(P_ENABLED_FLAG,'b7', 'enabled_flag');
780
781
782 if (v_where is not null) then
783 v_select := v_select || ' where ' ||v_where;
784 end if;
785
786
787 if (p_sort_data.count > 0) then --there is a sort preference
788
789 v_select := v_select || ' order by ';
790
791 v_index := p_sort_data.first;
792 v_first := v_index;
793
794 loop
795
796 if (v_first = v_index) then
797 v_comma := ' ';
798 else
799 v_comma := ', ';
800
801 end if;
802
803 v_select := v_select || v_comma ||
804 p_sort_data(v_index).field_name || ' ' ;
805
806 -- ascending or descending order
807 if (p_sort_data(v_index).asc_dsc_flag = 'A') then
808 v_select := v_select || 'asc ';
809 elsif (p_sort_data(v_index).asc_dsc_flag = 'D') then
810 v_select := v_select || 'desc ';
811 end if;
812
813 exit when v_index = p_sort_data.last;
814
815 v_index := p_sort_data.next(v_index);
816
817 end loop;
818
819 end if;
820
821 end create_sql_statement;
822
823 begin
824
825
826
827 x_return_status := fnd_api.g_ret_sts_success;
828
829
830 X_TASK_RSC_REQ_REC.delete;
831
832 if (p_query_or_next_code = 'Q') then
833
834
835 v_tbl.delete;
836
837 create_sql_statement;
838
839 dump_long_line('v_sel:',v_select);
840
841 v_cursor_id := dbms_sql.open_cursor;
842
843 dbms_sql.parse(v_cursor_id, v_select, dbms_sql.v7);
844
845 -- bind variables only if they added to the sql statement
846 if (P_RESOURCE_REQ_ID is not null) then
847 dbms_sql.bind_variable(v_cursor_id, ':b1', p_resource_req_id);
848 end if;
849
850 if (P_TASK_TYPE_ID is not null) then
851 dbms_sql.bind_variable(v_cursor_id, ':b2', p_task_type_id);
852 end if;
853
854 if (P_TASK_ID is not null) then
855 dbms_sql.bind_variable(v_cursor_id, ':b3', p_task_id);
856 end if;
857
858 if (P_TASK_TEMPLATE_ID is not null) then
859 dbms_sql.bind_variable(v_cursor_id, ':b4', p_task_template_id);
860 end if;
861
862 if (P_RESOURCE_TYPE_CODE is not null) then
863 dbms_sql.bind_variable(v_cursor_id, ':b5', p_resource_type_code);
864
865 end if;
866
867 if (P_REQUIRED_UNITS is not null) then
868 dbms_sql.bind_variable(v_cursor_id, ':b6', p_required_units);
869 end if;
870
871 if (P_ENABLED_FLAG is not null) then
872 dbms_sql.bind_variable(v_cursor_id, ':b7',p_enabled_flag );
873 end if;
874
875 -- define the output columns
876 dbms_sql.define_column(v_cursor_id, 1, v_type.RESOURCE_REQ_ID );
877
878
879 dbms_sql.define_column(v_cursor_id, 2, v_type.TASK_TYPE_ID );
880
881 dbms_sql.define_column(v_cursor_id, 3, v_type.TASK_ID );
882
883 dbms_sql.define_column(v_cursor_id, 4, v_type.TASK_TEMPLATE_ID );
884
885 dbms_sql.define_column(v_cursor_id, 5, v_type.RESOURCE_TYPE_CODE , 30);
886
887 dbms_sql.define_column(v_cursor_id, 6, v_type.REQUIRED_UNITS );
888
889 dbms_sql.define_column(v_cursor_id, 7, v_type.ENABLED_FLAG, 1 );
890
891 dbms_sql.define_column(v_cursor_id, 8, v_type.ATTRIBUTE1, 150);
892
893 dbms_sql.define_column(v_cursor_id, 9, v_type.ATTRIBUTE2, 150);
894
895 dbms_sql.define_column(v_cursor_id, 10, v_type.ATTRIBUTE3, 150);
896
900
897 dbms_sql.define_column(v_cursor_id, 11, v_type.ATTRIBUTE4, 150);
898
899 dbms_sql.define_column(v_cursor_id, 12, v_type.ATTRIBUTE5, 150);
901 dbms_sql.define_column(v_cursor_id, 13, v_type.ATTRIBUTE6, 150);
902
903 dbms_sql.define_column(v_cursor_id, 14, v_type.ATTRIBUTE7, 150);
904
905 dbms_sql.define_column(v_cursor_id, 15, v_type.ATTRIBUTE8, 150);
906
907 dbms_sql.define_column(v_cursor_id, 16, v_type.ATTRIBUTE9, 150);
908
909 dbms_sql.define_column(v_cursor_id, 17, v_type.ATTRIBUTE10, 150);
910
911 dbms_sql.define_column(v_cursor_id, 18, v_type.ATTRIBUTE11, 150);
912
913 dbms_sql.define_column(v_cursor_id, 19, v_type.ATTRIBUTE12, 150);
914
915
916 dbms_sql.define_column(v_cursor_id, 20, v_type.ATTRIBUTE13, 150);
917
918 dbms_sql.define_column(v_cursor_id, 21, v_type.ATTRIBUTE14, 150);
919
920 dbms_sql.define_column(v_cursor_id, 22, v_type.ATTRIBUTE15, 150);
921
922 dbms_sql.define_column(v_cursor_id, 23, v_type.ATTRIBUTE_CATEGORY, 30);
923
924
925
926 v_dummy := dbms_sql.execute(v_cursor_id);
927
928
929
930 v_cnt := 0;
931
932 loop
933
934 exit when (dbms_sql.fetch_rows(v_cursor_id) = 0);
935
936 v_cnt := v_cnt + 1;
937
938 -- retrieve the rows from the buffer
939
940 dbms_sql.column_value(v_cursor_id, 1, v_type.RESOURCE_REQ_ID);
941 dbms_sql.column_value(v_cursor_id, 2, v_type.TASK_TYPE_ID);
942 dbms_sql.column_value(v_cursor_id, 3, v_type.TASK_ID);
943 dbms_sql.column_value(v_cursor_id, 4, v_type.TASK_TEMPLATE_ID);
944 dbms_sql.column_value(v_cursor_id, 5, v_type.RESOURCE_TYPE_CODE);
945 dbms_sql.column_value(v_cursor_id, 6, v_type.REQUIRED_UNITS);
946 dbms_sql.column_value(v_cursor_id, 7, v_type.ENABLED_FLAG);
947 dbms_sql.column_value(v_cursor_id, 8, v_type.ATTRIBUTE1);
948 dbms_sql.column_value(v_cursor_id, 9, v_type.ATTRIBUTE2);
949 dbms_sql.column_value(v_cursor_id, 10, v_type.ATTRIBUTE3);
950 dbms_sql.column_value(v_cursor_id, 11, v_type.ATTRIBUTE4);
951 dbms_sql.column_value(v_cursor_id, 12, v_type.ATTRIBUTE5);
952 dbms_sql.column_value(v_cursor_id, 13, v_type.ATTRIBUTE6);
953 dbms_sql.column_value(v_cursor_id, 14, v_type.ATTRIBUTE7);
954
955 dbms_sql.column_value(v_cursor_id, 15, v_type.ATTRIBUTE8);
956 dbms_sql.column_value(v_cursor_id, 16, v_type.ATTRIBUTE9);
957 dbms_sql.column_value(v_cursor_id, 17, v_type.ATTRIBUTE10);
958 dbms_sql.column_value(v_cursor_id, 18, v_type.ATTRIBUTE11);
959 dbms_sql.column_value(v_cursor_id, 19, v_type.ATTRIBUTE12);
960 dbms_sql.column_value(v_cursor_id, 20, v_type.ATTRIBUTE13);
961 dbms_sql.column_value(v_cursor_id, 21, v_type.ATTRIBUTE14);
962 dbms_sql.column_value(v_cursor_id, 22, v_type.ATTRIBUTE15);
963 dbms_sql.column_value(v_cursor_id, 23, v_type.ATTRIBUTE_CATEGORY);
964
965
966 -- 'v_type.resource_req_id:'||
967 -- to_char(v_type.resource_req_id));
968
969
970 v_tbl(v_cnt) := v_type;
971
972 end loop;
973
974 dbms_sql.close_cursor(v_cursor_id);
975
976 end if;
977 --p_query_or_next_code;
978
979 -- copy records to be returned back
980
981 x_total_retrieved := v_tbl.count;
982
983
984 -- if table is empty do nothing
985 if (x_total_retrieved > 0) then
986 if (p_show_all = 'Y') then -- return all the rows
987 v_start := v_tbl.first;
988 v_end := v_tbl.last;
989 else
990 v_start := p_start_pointer;
991 v_end := p_start_pointer + p_rec_wanted - 1;
992 if (v_end > v_tbl.last) then
993 v_end := v_tbl.last;
994 end if;
995 end if;
996
997
998 for v_cnt in v_start..v_end loop
999 X_TASK_RSC_REQ_REC(v_cnt) := v_tbl(v_cnt);
1000 end loop;
1001 end if;
1002
1003 x_total_returned := X_TASK_RSC_REQ_REC.count;
1004
1005 exception
1006
1007 WHEN FND_API.G_EXC_ERROR THEN
1008 x_return_status := FND_API.G_RET_STS_ERROR ;
1009 FND_MSG_PUB.Count_And_Get
1010
1011 (p_count => x_msg_count ,
1012 p_data => x_msg_data
1013 );
1014 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1015
1016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1017 FND_MSG_PUB.Count_And_Get
1018 (p_count => x_msg_count ,
1019 p_data => x_msg_data
1020 );
1021 WHEN OTHERS THEN
1022
1023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1024
1025 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1026 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
1027 END IF;
1031 );
1028 FND_MSG_PUB.Count_And_Get
1029 (p_count => x_msg_count ,
1030 p_data => x_msg_data
1032
1033
1034 end;
1035
1036
1037 End ;