[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_TYPES_PKG
Source
1 PACKAGE BODY jtf_task_types_pkg AS
2 /* $Header: jtftktyb.pls 120.2 2006/05/30 13:18:27 sbarat ship $ */
3 PROCEDURE insert_row (
4 x_rowid IN OUT NOCOPY VARCHAR2,
5 x_task_type_id IN NUMBER,
6 x_start_date_active IN DATE,
7 x_end_date_active IN DATE,
8 x_seeded_flag IN VARCHAR2,
9 x_workflow IN VARCHAR2,
10 x_planned_effort IN NUMBER,
11 x_planned_effort_uom IN VARCHAR2,
12 x_schedule_flag IN VARCHAR2,
13 x_notification_flag IN VARCHAR2,
14 x_private_flag IN VARCHAR2,
15 x_attribute1 IN VARCHAR2,
16 x_attribute2 IN VARCHAR2,
17 x_attribute3 IN VARCHAR2,
18 x_attribute4 IN VARCHAR2,
19 x_attribute5 IN VARCHAR2,
20 x_attribute6 IN VARCHAR2,
21 x_attribute7 IN VARCHAR2,
22 x_attribute8 IN VARCHAR2,
23 x_attribute9 IN VARCHAR2,
24 x_attribute10 IN VARCHAR2,
25 x_attribute11 IN VARCHAR2,
26 x_attribute12 IN VARCHAR2,
27 x_attribute13 IN VARCHAR2,
28 x_attribute14 IN VARCHAR2,
29 x_attribute15 IN VARCHAR2,
30 x_attribute_category IN VARCHAR2,
31 x_name IN VARCHAR2,
32 x_description IN VARCHAR2,
33 x_creation_date IN DATE,
34 x_created_by IN NUMBER,
35 x_last_update_date IN DATE,
36 x_last_updated_by IN NUMBER,
37 x_last_update_login IN NUMBER,
38 x_rule IN VARCHAR2,
39 x_workflow_type IN VARCHAR2 default null
40 )
41 IS
42 CURSOR c
43 IS
44 SELECT ROWID
45 FROM jtf_task_types_b
46 WHERE task_type_id = x_task_type_id;
47 BEGIN
48 INSERT INTO jtf_task_types_b (
49 task_type_id,
50 object_version_number,
51 start_date_active,
52 end_date_active,
53 seeded_flag,
54 workflow,
55 planned_effort,
56 planned_effort_uom,
57 schedule_flag,
58 notification_flag,
59 private_flag,
60 attribute1,
61 attribute2,
62 attribute3,
63 attribute4,
64 attribute5,
65 attribute6,
66 attribute7,
67 attribute8,
68 attribute9,
69 attribute10,
70 attribute11,
71 attribute12,
72 attribute13,
73 attribute14,
74 attribute15,
75 attribute_category,
76 creation_date,
77 created_by,
78 last_update_date,
79 last_updated_by,
80 last_update_login,
81 rule,
82 workflow_type
83 )
84 VALUES (
85 x_task_type_id,
86 1,
87 x_start_date_active,
88 x_end_date_active,
89 x_seeded_flag,
90 x_workflow,
91 x_planned_effort,
92 x_planned_effort_uom,
93 x_schedule_flag,
94 x_notification_flag,
95 x_private_flag,
96 x_attribute1,
97 x_attribute2,
98 x_attribute3,
99 x_attribute4,
100 x_attribute5,
101 x_attribute6,
102 x_attribute7,
103 x_attribute8,
104 x_attribute9,
105 x_attribute10,
106 x_attribute11,
107 x_attribute12,
108 x_attribute13,
109 x_attribute14,
110 x_attribute15,
111 x_attribute_category,
112 x_creation_date,
113 x_created_by,
114 x_last_update_date,
115 x_last_updated_by,
116 x_last_update_login,
117 x_rule,
118 x_workflow_type
119 );
120 INSERT INTO jtf_task_types_tl
121 (task_type_id,
122 name,
123 description,
124 created_by,
125 creation_date,
126 last_updated_by,
127 last_update_date,
128 last_update_login,
129 language,
130 source_lang
131 )
132 SELECT x_task_type_id,
133 x_name,
134 x_description,
135 x_created_by,
136 x_creation_date,
137 x_last_updated_by,
138 x_last_update_date,
139 x_last_update_login,
140 l.language_code,
141 USERENV ('LANG')
142 FROM fnd_languages l
143 WHERE l.installed_flag IN ('I', 'B')
144 AND NOT EXISTS (SELECT NULL
145 FROM jtf_task_types_tl t
146 WHERE t.task_type_id = x_task_type_id
147 AND t.language = l.language_code);
148 OPEN c;
149 FETCH c INTO x_rowid;
150
151 IF (c%NOTFOUND)
152 THEN
153 CLOSE c;
154 RAISE NO_DATA_FOUND;
155 END IF;
156
157 CLOSE c;
158 END insert_row;
159
160 PROCEDURE lock_row (
161 x_task_type_id IN NUMBER,
162 x_object_version_number IN NUMBER
163 )
164 IS
165 CURSOR c
166 IS
167 SELECT object_version_number
168 FROM jtf_task_types_vl
169 WHERE task_type_id = x_task_type_id
170 AND object_version_number = x_object_version_number
171 FOR UPDATE OF task_type_id NOWAIT;
172
173 recinfo c%ROWTYPE;
174 BEGIN
175 OPEN c;
176 FETCH c INTO recinfo;
177
178 IF (c%NOTFOUND)
179 THEN
180 CLOSE c;
181 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
182 app_exception.raise_exception;
183 END IF;
184
185 CLOSE c;
186
187 IF recinfo.object_version_number = x_object_version_number
188 THEN
189 NULL;
190 ELSE
191 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
192 app_exception.raise_exception;
193 NULL;
194 END IF;
195 END lock_row;
196
197 PROCEDURE update_row (
198 x_task_type_id IN NUMBER,
199 x_object_version_number IN NUMBER,
200 x_start_date_active IN DATE,
201 x_end_date_active IN DATE,
202 x_seeded_flag IN VARCHAR2,
203 x_workflow IN VARCHAR2,
204 x_planned_effort IN NUMBER,
205 x_planned_effort_uom IN VARCHAR2,
206 x_schedule_flag IN VARCHAR2,
207 x_notification_flag IN VARCHAR2,
208 x_private_flag IN VARCHAR2,
209 x_attribute1 IN VARCHAR2,
210 x_attribute2 IN VARCHAR2,
211 x_attribute3 IN VARCHAR2,
212 x_attribute4 IN VARCHAR2,
213 x_attribute5 IN VARCHAR2,
214 x_attribute6 IN VARCHAR2,
215 x_attribute7 IN VARCHAR2,
216 x_attribute8 IN VARCHAR2,
217 x_attribute9 IN VARCHAR2,
218 x_attribute10 IN VARCHAR2,
219 x_attribute11 IN VARCHAR2,
220 x_attribute12 IN VARCHAR2,
221 x_attribute13 IN VARCHAR2,
222 x_attribute14 IN VARCHAR2,
223 x_attribute15 IN VARCHAR2,
224 x_attribute_category IN VARCHAR2,
225 x_name IN VARCHAR2,
226 x_description IN VARCHAR2,
227 x_last_update_date IN DATE,
228 x_last_updated_by IN NUMBER,
229 x_last_update_login IN NUMBER,
230 x_rule IN VARCHAR2,
231 x_workflow_type IN VARCHAR2 DEFAULT NULL
232 )
233 IS
234 BEGIN
235 UPDATE jtf_task_types_b
236 SET start_date_active = x_start_date_active,
237 object_version_number = x_object_version_number + 1,
238 end_date_active = x_end_date_active,
239 seeded_flag = x_seeded_flag,
240 workflow = x_workflow,
241 planned_effort = x_planned_effort,
242 planned_effort_uom = x_planned_effort_uom,
243 schedule_flag = x_schedule_flag,
244 notification_flag = x_notification_flag,
245 private_flag = x_private_flag,
246 attribute1 = x_attribute1,
247 attribute2 = x_attribute2,
248 attribute3 = x_attribute3,
249 attribute4 = x_attribute4,
250 attribute5 = x_attribute5,
251 attribute6 = x_attribute6,
252 attribute7 = x_attribute7,
253 attribute8 = x_attribute8,
254 attribute9 = x_attribute9,
255 attribute10 = x_attribute10,
256 attribute11 = x_attribute11,
257 attribute12 = x_attribute12,
258 attribute13 = x_attribute13,
259 attribute14 = x_attribute14,
260 attribute15 = x_attribute15,
261 attribute_category = x_attribute_category,
262 last_update_date = x_last_update_date,
263 last_updated_by = x_last_updated_by,
264 last_update_login = x_last_update_login,
265 rule = x_rule,
266 workflow_type = x_workflow_type
267 WHERE task_type_id = x_task_type_id;
268
269 --and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER ;
270 IF (SQL%NOTFOUND)
271 THEN
272 RAISE NO_DATA_FOUND;
273 END IF;
274
275 -- Added Index Hint on 30/05/2006 for bug# 5213367
276 UPDATE /*+ INDEX(a JTF_TASK_TYPES_TL_U1) */ jtf_task_types_tl a
277 SET a.name = x_name,
278 a.description = x_description,
279 a.last_update_date = x_last_update_date,
280 a.last_updated_by = x_last_updated_by,
281 a.last_update_login = x_last_update_login,
282 a.source_lang = USERENV ('LANG')
283 WHERE a.task_type_id = x_task_type_id
284 AND USERENV ('LANG') IN (a.language, a.source_lang);
285
286 IF (SQL%NOTFOUND)
287 THEN
288 RAISE NO_DATA_FOUND;
289 END IF;
290 END update_row;
291
292 PROCEDURE delete_row (x_task_type_id IN NUMBER)
293 IS
294 BEGIN
295 DELETE
296 FROM jtf_task_types_tl
297 WHERE task_type_id = x_task_type_id;
298
299 IF (SQL%NOTFOUND)
300 THEN
301 RAISE NO_DATA_FOUND;
302 END IF;
303
304 DELETE
305 FROM jtf_task_types_b
306 WHERE task_type_id = x_task_type_id;
307
308 IF (SQL%NOTFOUND)
309 THEN
310 RAISE NO_DATA_FOUND;
311 END IF;
312 END delete_row;
313
314 PROCEDURE add_language
315 IS
316 BEGIN
317
318 /* Solving Perf. Bug 3723927*/
319 /* The following delete and update statements are commented out */
320 /* as a quick workaround to fix the time-consuming table handler issue */
321 /*
322 DELETE
323 FROM jtf_task_types_tl t
324 WHERE NOT EXISTS (SELECT NULL
325 FROM jtf_task_types_b b
326 WHERE b.task_type_id = t.task_type_id);
327 UPDATE jtf_task_types_tl t
328 SET (name, description) = ( SELECT b.name, b.description
329 FROM jtf_task_types_tl b
330 WHERE b.task_type_id = t.task_type_id
331 AND b.language = t.source_lang)
332 WHERE (t.task_type_id, t.language) IN
333 (SELECT subt.task_type_id, subt.language
334 FROM jtf_task_types_tl subb, jtf_task_types_tl subt
335 WHERE subb.task_type_id = subt.task_type_id
336 AND subb.language = subt.source_lang
337 AND ( subb.name <> subt.name
338 OR subb.description <> subt.description
339 OR ( subb.description IS NULL
340 AND subt.description IS NOT NULL)
341 OR ( subb.description IS NOT NULL
342 AND subt.description IS NULL)));
343 */
344
345 INSERT INTO jtf_task_types_tl
346 (task_type_id,
347 name,
348 description,
349 created_by,
350 creation_date,
351 last_updated_by,
352 last_update_date,
353 last_update_login,
354 language,
355 source_lang
356 )
357 SELECT /*+ INDEX(b JTF_TASK_TYPES_TL_U1) INDEX (l FND_LANGUAGES_N1) */ -- Added Index Hint on 30/05/2006 for bug# 5213367
358 b.task_type_id,
359 b.name,
360 b.description,
361 b.created_by,
362 b.creation_date,
363 b.last_updated_by,
364 b.last_update_date,
365 b.last_update_login,
366 l.language_code,
367 b.source_lang
368 FROM jtf_task_types_tl b, fnd_languages l
369 WHERE l.installed_flag IN ('I', 'B')
370 AND b.language = USERENV ('LANG')
371 AND NOT EXISTS (SELECT NULL
372 FROM jtf_task_types_tl t
373 WHERE t.task_type_id = b.task_type_id
374 AND t.language = l.language_code);
375 END add_language;
376
377 PROCEDURE translate_row (
378 x_task_type_id IN VARCHAR2,
379 x_name IN VARCHAR2,
380 x_description IN VARCHAR2,
381 x_owner in varchar2
382 )
383 IS
384 l_user_id NUMBER := 0;
385 BEGIN
386 IF x_owner = 'SEED'
387 THEN
388 l_user_id := 1;
389 END IF;
390
391 -- Added Index Hint on 30/05/2006 for bug# 5213367
392 UPDATE /*+ INDEX(a JTF_TASK_TYPES_TL_U1) */ jtf_task_types_tl a
393 SET a.name = NVL (x_name, a.name),
394 a.description = NVL (x_description, a.description),
395 a.last_update_date = SYSDATE,
396 a.last_update_login = 0,
397 a.source_lang = USERENV ('LANG'),
398 a.last_updated_by = l_user_id
399 WHERE a.task_type_id = x_task_type_id
400 AND USERENV ('LANG') IN (a.language, a.source_lang);
401
402 IF (SQL%NOTFOUND)
403 THEN
404 RAISE NO_DATA_FOUND;
405 END IF;
406 END translate_row;
407
408 PROCEDURE load_row (
409 x_task_type_id IN NUMBER,
410 x_start_date_active IN DATE,
411 x_end_date_active IN DATE,
412 x_seeded_flag IN VARCHAR2,
413 x_workflow IN VARCHAR2,
414 x_planned_effort IN NUMBER,
415 x_planned_effort_uom IN VARCHAR2,
416 x_schedule_flag IN VARCHAR2,
417 x_notification_flag IN VARCHAR2,
418 x_private_flag IN VARCHAR2,
419 x_attribute1 IN VARCHAR2,
420 x_attribute2 IN VARCHAR2,
421 x_attribute3 IN VARCHAR2,
422 x_attribute4 IN VARCHAR2,
423 x_attribute5 IN VARCHAR2,
424 x_attribute6 IN VARCHAR2,
425 x_attribute7 IN VARCHAR2,
426 x_attribute8 IN VARCHAR2,
427 x_attribute9 IN VARCHAR2,
428 x_attribute10 IN VARCHAR2,
429 x_attribute11 IN VARCHAR2,
430 x_attribute12 IN VARCHAR2,
431 x_attribute13 IN VARCHAR2,
432 x_attribute14 IN VARCHAR2,
433 x_attribute15 IN VARCHAR2,
434 x_attribute_category IN VARCHAR2,
435 x_name IN VARCHAR2,
436 x_description IN VARCHAR2,
437 x_creation_date IN DATE,
438 x_created_by IN NUMBER,
439 x_last_update_date IN DATE,
440 x_last_updated_by IN NUMBER,
441 x_last_update_login IN NUMBER,
442 x_rule IN VARCHAR2,
443 x_owner IN VARCHAR2,
444 x_workflow_type IN VARCHAR2
445 )
446 AS
447 l_user_id NUMBER := 0;
448 l_task_type_id NUMBER;
449 l_rowid ROWID;
450 l_object_version_number NUMBER;
451 BEGIN
452 IF x_owner = 'SEED'
453 THEN
454 l_user_id := 1;
455 END IF;
456
457 SELECT task_type_id, object_version_number
458 INTO l_task_type_id, l_object_version_number
459 FROM jtf_task_types_b
460 WHERE task_type_id = x_task_type_id;
461
462
463
464
465 UPDATE jtf_task_types_b
466 SET start_date_active = x_start_date_active,
467 object_version_number = l_object_version_number + 1,
468 end_date_active = x_end_date_active,
469 seeded_flag = x_seeded_flag,
470 workflow = x_workflow,
471 planned_effort = x_planned_effort,
472 planned_effort_uom = x_planned_effort_uom,
473 schedule_flag = x_schedule_flag,
474 notification_flag = x_notification_flag,
475 private_flag = x_private_flag,
476 last_update_date = sysdate,
477 last_updated_by = l_user_id,
478 last_update_login = 0,
479 rule = x_rule,
480 workflow_type = x_workflow_type
481 WHERE task_type_id = l_task_type_id;
482
483 -- Added Index Hint on 30/05/2006 for bug# 5213367
484 UPDATE /*+ INDEX(a JTF_TASK_TYPES_TL_U1) */ jtf_task_types_tl a
485 SET a.name = x_name,
486 a.description = x_description,
487 a.last_update_date = sysdate,
488 a.last_updated_by = l_user_id,
489 a.last_update_login = 0,
490 a.source_lang = USERENV ('LANG')
491 WHERE a.task_type_id = l_task_type_id
492 AND USERENV ('LANG') IN (a.language, a.source_lang);
493
494
495
496 EXCEPTION
497 WHEN NO_DATA_FOUND
498 THEN
499 jtf_task_types_pkg.insert_row (
500 x_rowid => l_rowid,
501 x_rule => x_rule,
502 x_task_type_id => x_task_type_id,
503 x_start_date_active => x_start_date_active,
504 x_end_date_active => x_end_date_active,
505 x_seeded_flag => x_seeded_flag,
506 x_workflow => x_workflow,
507 x_planned_effort => x_planned_effort,
508 x_planned_effort_uom => x_planned_effort_uom,
509 x_schedule_flag => x_schedule_flag,
510 x_notification_flag => x_notification_flag,
511 x_private_flag => x_private_flag,
512 x_attribute1 => x_attribute1,
513 x_attribute2 => x_attribute2,
514 x_attribute3 => x_attribute3,
515 x_attribute4 => x_attribute4,
516 x_attribute5 => x_attribute5,
517 x_attribute6 => x_attribute6,
518 x_attribute7 => x_attribute7,
519 x_attribute8 => x_attribute8,
520 x_attribute9 => x_attribute9,
521 x_attribute10 => x_attribute10,
522 x_attribute11 => x_attribute11,
523 x_attribute12 => x_attribute12,
524 x_attribute13 => x_attribute13,
525 x_attribute14 => x_attribute14,
526 x_attribute15 => x_attribute15,
527 x_attribute_category => x_attribute_category,
528 x_name => x_name,
529 x_description => x_description,
530 x_creation_date => SYSDATE,
531 x_created_by => l_user_id,
532 x_last_update_date => SYSDATE,
533 x_last_updated_by => l_user_id,
534 x_last_update_login => 0,
535 x_workflow_type => x_workflow_type
536 );
537
538
539 END;
540 END jtf_task_types_pkg;