[Home] [Help]
PACKAGE BODY: APPS.JTF_PERZ_LF_PVT
Source
1 PACKAGE BODY JTF_PERZ_LF_PVT as
2 /* $Header: jtfzvlfb.pls 120.2 2005/11/02 22:19:00 skothe ship $ */
3 --
4 --
5 -- Start of Comments
6 --
7 -- NAME
8 -- JTF_PERZ_LF_PVT
9 --
10 -- PURPOSE
11 -- Private API for the look and feel objects.
12 --
13 -- NOTES
14 -- This is a pulicly accessible pacakge. It should be used by all
15 -- sources for creating, getting and updating look and feel objects
16 -- in the Personalization framework.
17 --
18 --
19 -- HISTORY
20 -- 06/15/99 SMATTEGU Created
21 -- 08/13/99 SMATTEGU Removed the handle_lf_object_map
22 -- 08/18/99 SMATTEGU Changed
23 -- 08/18/99 SMATTEGU Added
24 -- 09/07/99 SMATTEGU Re-Written the entire package
25 -- because of changes in specs
26 -- and data model
27 -- 09/30/99 SMATTEGU changed the save() to reflect profile_id fix
28 -- 11/10/99 SMATTEGU Bug# 1070584 Who column Changes and
29 -- other name changes
30 -- 11/10/99 SMATTEGU Bug# 1070665 fixed
31 -- 11/11/99 SMATTEGU Bug# 1071208 fixed
32 -- 11/15/99 SMATTEGU Bug# 1075579 fixed
33 -- 12/1/1999 SMATTEGU Bug#1097254 enhancing the scope of
34 -- update_lf_object to include updating object type.
35 -- 12/2/1999 SMATTEGU Bug#1098513 fixed
36
37
38 -- End of Comments
39
40 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_PERZ_LF_PVT';
41 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvplfb.pls';
42 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
43 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
44 --
45
46 -- ****************************************************************************
47 -- TABLE HANDLERS
48 --
49 -- The following are the table handlers
50 --
51 -- 1. insert_jtf_perz_lf_attrib
52 -- 2. Insert_Row_jtf_perz_lf_object
53 -- 3. insert_jtf_perz_lf_obj_type
54 -- 4. insert_jtf_perz_obj_type_map
55 -- 5. Insert_jtf_perz_lf_value
56 -- 6. insert_jtf_perz_obj_map
57
58 -- 7. update_jtf_perz_lf_obj_type
59 -- 8. update_jtf_perz_lf_value
60 -- 9. update_jtf_perz_lf_object
61
62 -- ****************************************************************************
63 PROCEDURE update_jtf_perz_lf_object (
64 x_object_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
65 x_parent_id IN NUMBER,
66 x_application_id IN NUMBER,
67 x_object_description IN VARCHAR2,
68 x_rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
69 p_OBJECT_VERSION_NUMBER IN NUMBER
70 ) IS
71 CURSOR C IS SELECT rowid FROM JTF_PERZ_LF_OBJECT
72 WHERE object_id = x_object_id;
73 BEGIN
74 update JTF_PERZ_LF_OBJECT
75 set parent_id = decode( x_parent_id, FND_API.G_MISS_NUM,parent_id,
76 NULL, parent_id, x_parent_id),
77 object_description = decode( x_object_description, FND_API.G_MISS_CHAR,
78 object_description, NULL, object_description, x_object_description),
79 OBJECT_VERSION_NUMBER = decode (p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
80 OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
81 LAST_UPDATE_DATE = SYSDATE,
82 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
83 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
84 WHERE object_id = x_object_id
85 and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
86
87 If (SQL%NOTFOUND) then
88 RAISE NO_DATA_FOUND;
89 End If;
90
91 OPEN C;
92 FETCH C INTO x_Rowid;
93 If (C%NOTFOUND) then
94 CLOSE C;
95 RAISE NO_DATA_FOUND;
96 End If;
97
98 end update_jtf_perz_lf_object;
99 -- ****************************************************************************
100 PROCEDURE update_jtf_perz_lf_obj_type (
101 x_object_type_desc IN VARCHAR2,
102 x_object_type_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
103 x_rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
104 p_OBJECT_VERSION_NUMBER IN NUMBER
105 )IS
106
107 CURSOR C IS SELECT rowid FROM JTF_PERZ_LF_OBJECT_TYPE
108 where object_type_id = x_object_type_id;
109 BEGIN
110 Update JTF_PERZ_LF_OBJECT_TYPE
111 SET
112 OBJECT_TYPE_DESC =
113 decode( x_object_type_desc, FND_API.G_MISS_CHAR,
114 OBJECT_TYPE_DESC,x_object_type_desc),
115 OBJECT_VERSION_NUMBER = decode (p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
116 OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
117 LAST_UPDATE_DATE = SYSDATE,
118 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
119 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
120 where object_type_id = x_object_type_id
121 and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
122
123 If (SQL%NOTFOUND) then
124 RAISE NO_DATA_FOUND;
125 End If;
126
127 OPEN C;
128 FETCH C INTO x_Rowid;
129 If (C%NOTFOUND) then
130 CLOSE C;
131 RAISE NO_DATA_FOUND;
132 End If;
133 END update_jtf_perz_lf_obj_type;
134 -- ****************************************************************************
135 PROCEDURE update_jtf_perz_lf_value(
136 x_Rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
137 x_value_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
138 x_PROFILE_ID IN NUMBER,
139 x_MAP_ID IN NUMBER,
140 x_ATTRIBUTE_VALUE IN VARCHAR2,
141 x_ACTIVE_FLAG IN VARCHAR2,
142 x_PRIORITY IN NUMBER,
143 p_OBJECT_VERSION_NUMBER IN NUMBER
144 ) IS
145
146 CURSOR C IS SELECT rowid FROM JTF_PERZ_LF_VALUE
147 WHERE PERZ_LF_VALUE_ID = x_VALUE_ID;
148
149 BEGIN
150
151 Update JTF_PERZ_LF_VALUE
152 SET
153 PROFILE_ID = decode( x_PROFILE_ID, FND_API.G_MISS_NUM,PROFILE_ID,x_PROFILE_ID),
154 OBJ_MAP_ID = decode( x_MAP_ID, FND_API.G_MISS_NUM,OBJ_MAP_ID,x_MAP_ID),
155 ATTRIBUTE_VALUE = decode( x_ATTRIBUTE_VALUE, FND_API.G_MISS_CHAR,ATTRIBUTE_VALUE,x_ATTRIBUTE_VALUE),
156 ACTIVE_FLAG = decode( x_ACTIVE_FLAG, FND_API.G_MISS_CHAR,ACTIVE_FLAG,x_ACTIVE_FLAG),
157 PRIORITY = decode( x_PRIORITY, FND_API.G_MISS_NUM,PRIORITY,x_PRIORITY),
158 OBJECT_VERSION_NUMBER = decode (p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
159 OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
160 LAST_UPDATE_DATE = SYSDATE,
161 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
162 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
163 where PERZ_LF_VALUE_ID = x_value_id;
164
165 If (SQL%NOTFOUND) then
166 RAISE NO_DATA_FOUND;
167 End If;
168 OPEN C;
169 FETCH C INTO x_Rowid;
170 If (C%NOTFOUND) then
171 CLOSE C;
172 RAISE NO_DATA_FOUND;
173 End If;
174
175 END update_jtf_perz_lf_value;
176 --
177 -- ****************************************************************************
178
179
180 PROCEDURE insert_jtf_perz_obj_map(
181 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
182 x_OBJ_MAP_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
183 x_TYPE_MAP_ID IN NUMBER,
184 x_OBJECT_ID IN NUMBER
185 ) IS
186 CURSOR C IS SELECT rowid FROM JTF_PERZ_OBJ_MAP
187 WHERE OBJ_MAP_ID = x_OBJ_MAP_ID;
188 CURSOR C2 IS SELECT JTF_PERZ_OBJ_MAP_s.nextval FROM sys.dual;
189 BEGIN
190 If (x_OBJ_MAP_ID IS NULL) then
191 OPEN C2;
192 FETCH C2 INTO x_OBJ_MAP_ID;
193 CLOSE C2;
194 End If;
195 INSERT INTO JTF_PERZ_OBJ_MAP(
196 OBJ_MAP_ID,
197 MAP_ID,
198 OBJECT_ID,
199 CREATED_BY,
200 LAST_UPDATE_DATE,
201 LAST_UPDATED_BY,
202 LAST_UPDATE_LOGIN
203 ) VALUES (
204 x_OBJ_MAP_ID,
205 decode(x_TYPE_MAP_ID,FND_API.G_MISS_NUM, NULL,x_TYPE_MAP_ID),
206 decode( x_OBJECT_ID, FND_API.G_MISS_NUM, NULL,x_OBJECT_ID),
207 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);
208 OPEN C;
209 FETCH C INTO x_Rowid;
210 If (C%NOTFOUND) then
211 CLOSE C;
212 RAISE NO_DATA_FOUND;
213 End If;
214 End insert_jtf_perz_obj_map;
215
216
217 -- -- ****************************************************************************
218 --
219 PROCEDURE Insert_jtf_perz_lf_value(
220 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
221 x_VALUE_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
222 x_PROFILE_ID IN NUMBER,
223 x_MAP_ID IN NUMBER,
224 x_ATTRIBUTE_VALUE IN VARCHAR2,
225 x_ACTIVE_FLAG IN VARCHAR2,
226 x_PRIORITY IN NUMBER
227 ) IS
228 CURSOR C IS SELECT rowid FROM JTF_PERZ_LF_VALUE
229 WHERE PERZ_LF_VALUE_ID = x_VALUE_ID;
230 CURSOR C2 IS SELECT JTF_PERZ_LF_VALUE_s.nextval FROM sys.dual;
231
232
233 BEGIN
234
235 If ( x_VALUE_ID IS NULL) then
236 OPEN C2;
237 FETCH C2 INTO x_VALUE_ID;
238 CLOSE C2;
239 End If;
240 INSERT INTO JTF_PERZ_LF_VALUE(
241 PERZ_LF_VALUE_ID,
242 PROFILE_ID,
243 OBJ_MAP_ID,
244 ATTRIBUTE_VALUE,
245 ACTIVE_FLAG,
246 PRIORITY,
247 OBJECT_VERSION_NUMBER,
248 CREATED_BY,
249 LAST_UPDATE_DATE,
250 LAST_UPDATED_BY,
251 LAST_UPDATE_LOGIN
252 ) VALUES (
253 x_VALUE_ID,
254 x_PROFILE_ID,
255 decode( x_MAP_ID, FND_API.G_MISS_NUM, NULL,x_MAP_ID),
256 decode( x_ATTRIBUTE_VALUE, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_VALUE),
257 decode( x_ACTIVE_FLAG, FND_API.G_MISS_CHAR, NULL,x_ACTIVE_FLAG),
258 decode( x_PRIORITY, FND_API.G_MISS_NUM, NULL,x_PRIORITY),
259 1,G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);
260 OPEN C;
261 FETCH C INTO x_Rowid;
262 If (C%NOTFOUND) then
263 CLOSE C;
264 RAISE NO_DATA_FOUND;
265 End If;
266 End Insert_jtf_perz_lf_value;
267
268 -- ****************************************************************************
269
270 PROCEDURE insert_jtf_perz_obj_type_map(
271 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
272 x_MAP_ID OUT NOCOPY /* file.sql.39 change */ NUMBER,
273 x_OBJECT_TYPE_ID IN NUMBER,
274 x_ATTRIBUTE_ID IN NUMBER
275 ) IS
276 CURSOR C IS SELECT rowid FROM jtf_perz_obj_type_map
277 WHERE obj_type_map_id = x_MAP_ID;
278 CURSOR C2 IS SELECT jtf_perz_obj_type_map_s.nextval FROM sys.dual;
279 BEGIN
280 If (x_MAP_ID IS NULL) then
281 OPEN C2;
282 FETCH C2 INTO x_MAP_ID;
283 CLOSE C2;
284 End If;
285 INSERT INTO jtf_perz_obj_type_map(
286 obj_type_map_id,
287 OBJECT_TYPE_ID,
288 ATTRIBUTE_ID,
289 CREATED_BY,
290 LAST_UPDATE_DATE,
291 LAST_UPDATED_BY,
292 LAST_UPDATE_LOGIN
293 ) VALUES (
294 x_MAP_ID,
295 decode( x_OBJECT_TYPE_ID, FND_API.G_MISS_NUM, NULL,x_OBJECT_TYPE_ID),
296 decode( x_ATTRIBUTE_ID, FND_API.G_MISS_NUM, NULL,x_ATTRIBUTE_ID),
297 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);
298 OPEN C;
299 FETCH C INTO x_Rowid;
300 If (C%NOTFOUND) then
301 CLOSE C;
302 RAISE NO_DATA_FOUND;
303 End If;
304 End insert_jtf_perz_obj_type_map;
305
306
307 -- ****************************************************************************
308 /*
309 PROCEDURE update_row_jtf_perz_lf_attrib(
310 x_Rowid IN OUT VARCHAR2,
311 x_ATTRIBUTE_ID IN NUMBER,
312 x_ATTRIBUTE_NAME IN VARCHAR2,
313 x_ATTRIBUTE_TYPE IN VARCHAR2
314 ) IS
315 BEGIN
316 Update JTF_PERZ_LF_ATTRIB
317 SET
318 ATTRIBUTE_ID = decode( x_ATTRIBUTE_ID, FND_API.G_MISS_NUM,ATTRIBUTE_ID,x_ATTRIBUTE_ID),
319 ATTRIBUTE_NAME = decode( x_ATTRIBUTE_NAME, FND_API.G_MISS_CHAR,ATTRIBUTE_NAME,x_ATTRIBUTE_NAME),
320 ATTRIBUTE_TYPE = decode( x_ATTRIBUTE_TYPE, FND_API.G_MISS_CHAR,ATTRIBUTE_TYPE,x_ATTRIBUTE_TYPE)
321 where rowid = X_RowId;
322
323 If (SQL%NOTFOUND) then
324 RAISE NO_DATA_FOUND;
325 End If;
326 END update_row_jtf_perz_lf_attrib;
327 */
328
329 -- ****************************************************************************
330 PROCEDURE insert_jtf_perz_lf_attrib(
331 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
332 x_ATTRIBUTE_ID OUT NOCOPY /* file.sql.39 change */ NUMBER,
333 x_ATTRIBUTE_NAME IN VARCHAR2,
334 x_ATTRIBUTE_TYPE IN VARCHAR2
335 ) IS
336
337 CURSOR C IS SELECT rowid FROM JTF_PERZ_LF_ATTRIB
338 WHERE ATTRIBUTE_ID = x_ATTRIBUTE_ID;
339
340 CURSOR C2 IS SELECT JTF_PERZ_LF_ATTRIB_s.nextval FROM sys.dual;
341
342 BEGIN
343
344 If (x_ATTRIBUTE_ID IS NULL) then
345 OPEN C2;
346 FETCH C2 INTO x_ATTRIBUTE_ID;
347 CLOSE C2;
348 End If;
349
350 INSERT INTO JTF_PERZ_LF_ATTRIB(
351 ATTRIBUTE_ID,
352 ATTRIBUTE_NAME,
353 ATTRIBUTE_TYPE,
354 OBJECT_VERSION_NUMBER,
355 CREATED_BY,
356 LAST_UPDATE_DATE,
357 LAST_UPDATED_BY,
358 LAST_UPDATE_LOGIN
359 )
360 VALUES (
361 x_ATTRIBUTE_ID,
362 decode( x_ATTRIBUTE_NAME, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_NAME),
363 decode( x_ATTRIBUTE_TYPE, FND_API.G_MISS_CHAR, NULL,x_ATTRIBUTE_TYPE),
364 1,G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
365 );
366
367 OPEN C;
368 FETCH C INTO x_Rowid;
369 If (C%NOTFOUND) then
370 CLOSE C;
371 RAISE NO_DATA_FOUND;
372 End If;
373 End insert_jtf_perz_lf_attrib;
374 -- ****************************************************************************
375
376 PROCEDURE Insert_jtf_perz_lf_obj_type(
377 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
378 x_OBJECT_TYPE_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
379 x_OBJECT_TYPE IN VARCHAR2,
380 x_OBJECT_TYPE_DESC IN VARCHAR2
381 ) IS
382
383 CURSOR C IS SELECT rowid FROM JTF_PERZ_LF_OBJECT_TYPE
384 WHERE OBJECT_TYPE_ID = x_OBJECT_TYPE_ID;
385 CURSOR C2 IS SELECT JTF_PERZ_LF_OBJ_TYPE_s.nextval FROM sys.dual;
386
387 BEGIN
388
389 If (x_OBJECT_TYPE_ID IS NULL) then
390 OPEN C2;
391 FETCH C2 INTO x_OBJECT_TYPE_ID;
392 CLOSE C2;
393 End If;
394
395
396 INSERT INTO JTF_PERZ_LF_OBJECT_TYPE(
397 OBJECT_TYPE_ID,
398 OBJECT_TYPE_NAME,
399 OBJECT_TYPE_DESC,
400 OBJECT_VERSION_NUMBER,
401 CREATED_BY,
402 LAST_UPDATE_DATE,
403 LAST_UPDATED_BY,
404 LAST_UPDATE_LOGIN
405 )
406 VALUES (
407 x_OBJECT_TYPE_ID,
408 decode( x_OBJECT_TYPE, FND_API.G_MISS_CHAR, NULL,x_OBJECT_TYPE),
409 decode( x_OBJECT_TYPE_DESC, FND_API.G_MISS_CHAR, NULL,x_OBJECT_TYPE_DESC),
410 1, G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
411 );
412
413 OPEN C;
414 FETCH C INTO x_Rowid;
415 If (C%NOTFOUND) then
416 CLOSE C;
417 RAISE NO_DATA_FOUND;
418 End If;
419
420 End Insert_jtf_perz_lf_obj_type;
421
422 -- ****************************************************************************
423
424 PROCEDURE Insert_jtf_perz_lf_object(
425 x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
426 x_OBJECT_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
427 x_PARENT_ID IN NUMBER,
428 x_APPLICATION_ID IN NUMBER,
429 x_OBJECT_NAME IN VARCHAR2,
430 x_OBJECT_DESCRIPTION IN VARCHAR2
431 ) IS
432
433 CURSOR C IS SELECT rowid FROM JTF_PERZ_LF_OBJECT
434 WHERE OBJECT_ID = x_OBJECT_ID;
435 CURSOR C2 IS SELECT JTF_PERZ_LF_OBJECT_s.nextval FROM sys.dual;
436
437 BEGIN
438
439 If (x_OBJECT_ID IS NULL) then
440 OPEN C2;
441 FETCH C2 INTO x_OBJECT_ID;
442 CLOSE C2;
443 End If;
444
445 INSERT INTO JTF_PERZ_LF_OBJECT(
446 OBJECT_ID,
447 PARENT_ID,
448 APPLICATION_ID,
449 OBJECT_NAME,
450 OBJECT_DESCRIPTION,
451 OBJECT_VERSION_NUMBER,
452 CREATED_BY,
453 LAST_UPDATE_DATE,
454 LAST_UPDATED_BY,
455 LAST_UPDATE_LOGIN
456 )
457 VALUES (
458 x_OBJECT_ID,
459 decode( x_PARENT_ID, FND_API.G_MISS_NUM, NULL,x_PARENT_ID),
460 decode( x_APPLICATION_ID, FND_API.G_MISS_NUM, NULL,x_APPLICATION_ID),
461 decode( x_OBJECT_NAME, FND_API.G_MISS_CHAR, NULL,x_OBJECT_NAME),
462 decode( x_OBJECT_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,x_OBJECT_DESCRIPTION),
463 1, G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
464 );
465
466 OPEN C;
467 FETCH C INTO x_Rowid;
468 If (C%NOTFOUND) then
469 CLOSE C;
470 RAISE NO_DATA_FOUND;
471 End If;
472
473 End Insert_jtf_perz_lf_object;
474
475 -- ***************************************************************************
476 -- ***************************************************************************
477 --
478 --Private APIs
479 --
480 -- check_attribute()
481 -- Check_duplicate_obj()
482 -- Check_duplicate_obj_type()
483 -- get_obj_type_details()
484
485 -- ***************************************************************************
486 -- ***************************************************************************
487
488 -- Start of Comments
489 --
490 -- API name : check_attribute
491 -- Type : Private
492 -- Function :
493 -- Check if attribute exists
494 --
495 -- Paramaeters :
496 -- IN:
497 -- p_attribute_type IN VARCHAR2,
498 -- p_attribute_name IN VARCHAR2,
499 -- OUT:
500 -- x_rowid OUT ROWID,
501 -- x_attribute_id IN OUT NUMBER,
502 -- x_return_status OUT VARCHAR2
503 -- *****************************************************************************
504 procedure check_attribute(
505 p_attribute_name IN VARCHAR2,
506 p_attribute_type IN VARCHAR2,
507 x_rowid OUT NOCOPY /* file.sql.39 change */ ROWID,
508 x_attribute_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
509 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
510 )IS
511
512 l_attribute_id NUMBER := x_attribute_id;
513 BEGIN
514
515 if (l_attribute_id is null) then
516 select rowid, attribute_id
517 into x_rowid, x_attribute_id
518 from jtf_perz_lf_attrib
519 where attribute_name = p_attribute_name
520 and attribute_type = p_attribute_type;
521
522 if (x_rowid is not null) then
523 x_return_status := FND_API.G_TRUE;
524 else
525 x_return_status := FND_API.G_FALSE;
526 end if;
527 else
528 select rowid, attribute_id
529 into x_rowid, x_attribute_id
530 from jtf_perz_lf_attrib
531 where attribute_id = l_attribute_id;
532
533 if (x_rowid is not null) then
534 x_return_status := FND_API.G_TRUE;
535 else
536 x_return_status := FND_API.G_FALSE;
537 end if;
538 end if;
539
540 EXCEPTION
541
542 WHEN NO_DATA_FOUND THEN
543 x_return_status := FND_API.G_FALSE;
544
545 WHEN OTHERS THEN
546 x_return_status := FND_API.G_FALSE;
547
548 END check_attribute;
549 -- *****************************************************************************
550
551 -- Start of Comments
552 --
553 -- API name : Check_duplicate_obj_type
554 -- Type : Private
555 -- Function : Checks if the object already exists or not
556 --
557 -- Paramaeters :
558 -- IN :
559 -- p_object_type IN VARCHAR2 Required
560 --
561 -- OUT :
562 -- x_return_status OUT VARCHAR2
563 --
564 -- IN OUT:
565 -- x_object_type_id IN OUT NUMBER
566 -- *****************************************************************************
567 procedure check_duplicate_obj_type (
568 p_object_type IN VARCHAR2,
569 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
570 x_object_type_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
571 x_OBJECT_VERSION_NUMBER OUT NOCOPY /* file.sql.39 change */ NUMBER
572 ) IS
573
574 l_object_type_id NUMBER := NULL;
575
576 BEGIN
577
578 if ((p_object_type is not null) and
579 (p_object_type <> FND_API.G_MISS_CHAR)) and
580 ((x_object_type_id is null) OR
581 (x_object_type_id = FND_API.G_MISS_NUM))
582 then
583 select object_type_id, object_version_number
584 into l_object_type_id, x_object_version_number
585 from jtf_perz_lf_object_type
586 where object_type_name = p_object_type;
587
588 x_object_type_id := l_object_type_id;
589 x_return_status := FND_API.G_TRUE;
590
591 elsif ((x_object_type_id is not null) and
592 (x_object_type_id <> FND_API.G_MISS_NUM)) and
593 ((p_object_type is null) OR
594 (p_object_type <> FND_API.G_MISS_CHAR))
595 then
596
597 select object_type_id, object_version_number
598 into l_object_type_id, x_object_version_number
599 from jtf_perz_lf_object_type
600 where object_type_id = x_object_type_id ;
601
602 x_object_type_id := l_object_type_id;
603 x_return_status := FND_API.G_TRUE;
604
605 elsif ((p_object_type is not null) and
606 (x_object_type_id is not null) and
607 (p_object_type <> FND_API.G_MISS_CHAR) and
608 (x_object_type_id <> FND_API.G_MISS_NUM))
609 then
610 select object_type_id, object_version_number
611 into l_object_type_id, x_object_version_number
612 from jtf_perz_lf_object_type
613 where object_type_id = x_object_type_id;
614 --and object_type_name = upper(p_object_type);
615
616 x_object_type_id := l_object_type_id;
617 x_return_status := FND_API.G_TRUE;
618
619 else
620 x_return_status := FND_API.G_FALSE;
621 end if;
622
623 EXCEPTION
624
625 WHEN NO_DATA_FOUND THEN
626 x_return_status := FND_API.G_FALSE;
627
628 WHEN OTHERS THEN
629 x_return_status := FND_API.G_FALSE;
630
631 END check_duplicate_obj_type;
632 -- *****************************************************************************
633
634 -- Start of Comments
635 --
636 -- API name : Check_duplicate_obj
637 -- Type : Private
638 -- Function : Checks if the object already exists or not
639 --
640 -- Paramaeters :
641 -- IN :
642 -- p_application_id IN NUMBER Required
643 -- p_object_name IN VARCHAR2 Required
644
645 --
646 -- OUT :
647 -- x_return_status OUT BOOLEAN
648 --
649 -- IN OUT:
650 -- x_object_id IN OUT NUMBER
651 -- *****************************************************************************
652 procedure check_duplicate_obj (
653 p_application_id IN NUMBER,
654 p_object_name IN VARCHAR2,
655 x_object_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
656 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
657 x_OBJECT_VERSION_NUMBER OUT NOCOPY /* file.sql.39 change */ NUMBER
658 ) IS
659
660 l_object_id NUMBER := NULL;
661
662 BEGIN
663
664 -- Please take care of G_MISS_NUM , G_MISS_CHAR -srikanth
665 if (((p_object_name is not null) OR (p_object_name <> FND_API.G_MISS_CHAR))
666 and ((x_object_id is null) OR (x_object_id = FND_API.G_MISS_NUM))) then
667
668 select object_id, object_version_number
669 into l_object_id, x_object_version_number
670 from jtf_perz_lf_object
671 where object_name = p_object_name
672 and application_id = p_application_id;
673
674 x_object_id := l_object_id;
675 x_return_status := FND_API.G_TRUE;
676
677
678 elsif (((p_object_name is null) OR (p_object_name = FND_API.G_MISS_CHAR))
679 and ((x_object_id is not null) OR (x_object_id <> FND_API.G_MISS_NUM))) then
680
681 select object_id, object_version_number
682 into l_object_id, x_object_version_number
683 from jtf_perz_lf_object
684 where object_id = x_object_id
685 and application_id = p_application_id;
686
687 x_object_id := l_object_id;
688 x_return_status := FND_API.G_TRUE;
689
690
691 elsif (((p_object_name is not null) OR (p_object_name <> FND_API.G_MISS_CHAR))
692 and ((x_object_id is not null) OR (x_object_id <> FND_API.G_MISS_NUM))) then
693
694 select object_id, object_version_number
695 into l_object_id, x_object_version_number
696 from jtf_perz_lf_object
697 where object_id = x_object_id and
698 object_name = p_object_name
699 and application_id = p_application_id;
700
701 x_object_id := l_object_id;
702 x_return_status := FND_API.G_TRUE;
703
704 else
705 x_return_status := FND_API.G_FALSE;
706 end if;
707 -- dbms_output.put_line(' return status in check duplicate obj is:'||x_return_status);
708 EXCEPTION
709
710 WHEN NO_DATA_FOUND THEN
711 x_return_status := FND_API.G_FALSE;
712 -- dbms_output.put_line(' return status in check duplicate obj is:'||x_return_status);
713
714 WHEN OTHERS THEN
715 x_return_status := FND_API.G_FALSE;
716 -- dbms_output.put_line(' return status in check duplicate obj is:'||x_return_status);
717
718 END check_duplicate_obj;
719
720 -- ***************************************************************************
721
722 -- Start of Comments
723 --
724 -- API name : Get_obj_type_details
725 -- Type : Private
726 -- Function : Gets the object type and it's associated attributes.
727 -- If the procedure is not sucessful,
728 -- then it returns FALSE else TRUE
729 --
730 --
731 -- Paramaeters :
732 -- IN :
733 -- p_Object_type VARCHAR2
734 -- IN OUT :
735 -- p_Object_type_Id NUMBER
736 --
737 -- OUT :
738 -- x_object_type_desc VARCHAR2
739 -- x_return_status VARCHAR2
740 -- x_attrib_rec_tbl JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE
741 --
742 -- ***************************************************************************
743 procedure get_obj_type_details
744 (
745 p_Object_type IN VARCHAR2,
746 p_Object_type_Id IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
747 x_object_type_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
748 x_attrib_rec_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE,
749 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
750 )IS
751 l_Object_type_Id NUMBER := NULL;
752 l_count NUMBER := 0;
753
754 l_attribute_id NUMBER;
755 l_attribute_name VARCHAR2(30);
756 l_attribute_type VARCHAR2(30);
757
758 CURSOR C_GET_ATTRIBUTES (p_Object_type_Id NUMBER) IS
759 SELECT a.attribute_id, b.attribute_name, b.attribute_type
760 from jtf_perz_obj_type_map a, jtf_perz_lf_attrib b
761 where a.object_type_id = p_object_type_id
762 and a.attribute_id = b.attribute_id;
763 BEGIN
764
765 if ((p_object_type_id is not NULL) OR
766 (p_object_type_id <> FND_API.G_MISS_NUM)) then
767
768 select object_type_id, object_type_desc
769 into l_object_type_id, x_object_type_desc
770 from jtf_perz_lf_object_type
771 where object_type_id = p_object_type_id;
772 x_return_status := FND_API.G_TRUE;
773
774 elsif (( p_object_type is not null) OR
775 ( p_object_type <> FND_API.G_MISS_CHAR)) then
776
777 select object_type_id, object_type_desc
778 into l_object_type_id, x_object_type_desc
779 from jtf_perz_lf_object_type
780 where object_type_name = p_object_type;
781 x_return_status := FND_API.G_TRUE;
782
783 else
784 x_return_status := FND_API.G_FALSE;
785 end if;
786
787 if (x_return_status = FND_API.G_TRUE) then
788
789 l_count := 1;
790 OPEN C_GET_ATTRIBUTES (l_object_type_id);
791 --dbms_output.put_line(' 1 l_object_type_id:'||l_object_type_id);
792 loop
793 FETCH C_GET_ATTRIBUTES INTO
794 l_attribute_id,
795 l_attribute_name,
796 l_attribute_type;
797 EXIT WHEN C_GET_ATTRIBUTES%NOTFOUND;
798 IF (C_GET_ATTRIBUTES%FOUND = TRUE) THEN
799 x_attrib_rec_tbl(l_count).attribute_id := l_attribute_id;
800 x_attrib_rec_tbl(l_count).attribute_name := l_attribute_name;
801 x_attrib_rec_tbl(l_count).attribute_type := l_attribute_type;
802
803 l_count := l_count+ 1;
804 END IF;
805 end loop;
806 CLOSE C_GET_ATTRIBUTES;
807 p_object_type_id := l_object_type_id;
808 --dbms_output.put_line('2 l_object_type_id:'||l_object_type_id);
809 x_return_status := FND_API.G_TRUE;
810
811 end if;
812
813
814 EXCEPTION
815 WHEN NO_DATA_FOUND THEN
816 x_return_status := FND_API.G_FALSE;
817
818 WHEN OTHERS THEN
819 x_return_status := FND_API.G_FALSE;
820 END get_obj_type_details;
821
822 -- ****************************************************************************
823 -- ****************************************************************************
824 -- PUBLIC APIs
825 --
826 -- Create_lf_object()
827 -- Update_lf_object()
828 -- get_lf_object()
829 -- save_lf_object()
830 -- save_lf_object_type()
831 -- get_lf_object_type()
832 -- *****************************************************************************
833 -- *****************************************************************************
834
835 -- Start of Comments
836 --
837 -- API name : Create_lf_object
838 -- Type : Public
839 -- Function : Create attribute value pairs for a given object and profile
840 --
841 -- Paramaeters :
842 -- IN :
843 -- p_api_version_number IN NUMBER Required
844 -- p_init_msg_list IN VARCHAR2 Optional
845 -- Default = FND_API.G_FALSE
846 -- p_commit IN VARCHAR2 Optional
847
848 -- p_profile_id IN NUMBER Optional
849 -- p_profile_name IN VARCHAR2 Required
850
851 -- p_application_id IN NUMBER Required
852 -- p_parent_id IN NUMBER Optional
853 -- p_object_id IN NUMBER Optional
854 -- p_object_name IN VARCHAR2 Required
855
856 -- p_object_type_id IN NUMBER Optional
857 -- p_object_type IN VARCHAR2 Optional
858
859 -- p_attrib_value_tbl IN JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
860 -- := JTF_PERZ_LF_PUB.G_MISS_ATTRIB_VALUE_TBL
861
862 -- OUT :
863 -- x_object_id OUT NUMBER
864 -- x_return_status OUT VARCHAR2(1)
865 -- x_msg_count OUT NUMBER
866 -- x_msg_data OUT VARCHAR2(2000)
867 --
868 --
869 -- Version : Current version 1.0
870 -- Initial version 1.0
871 --
872 -- Notes: Personalization Framework API to create the Object attrib-
873 -- Value pair with their corresponding profile.
874 --
875 --
876 -- *****************************************************************************
877
878 PROCEDURE Create_lf_object
879 ( p_api_version_number IN NUMBER,
880 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
881 p_commit IN VARCHAR2 := FND_API.G_FALSE,
882
883 p_profile_id IN NUMBER,
884 p_profile_name IN VARCHAR2,
885
886 p_application_id IN NUMBER,
887 p_parent_id IN NUMBER,
888 p_object_id IN NUMBER,
889 p_object_name IN VARCHAR2,
890
891 p_object_type_id IN NUMBER,
892 p_object_type IN VARCHAR2,
893
894 p_attrib_value_tbl IN JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
895 := JTF_PERZ_LF_PUB.G_MISS_ATTRIB_VALUE_TBL,
896
897 x_object_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
898 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
899 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
900 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
901 )IS
902 -- ******* Create_lf_object Local Variables ********
903 -- Following variables are needed to adhere to standards
904 l_api_version_number NUMBER := p_api_version_number;
905 l_api_name CONSTANT VARCHAR2(30) := 'Create_lf_Object';
906
907 -- Following variables are needed for implementation
908 l_profile_id NUMBER := NULL;
909 l_profile_name VARCHAR2(60) := NULL;
910 l_return_status VARCHAR2(240);
911 l_object_type VARCHAR2(60) := p_object_type;
912 l_object_type_id NUMBER := p_object_type_id;
913 l_rowid ROWID := NULL;
914 l_object_id NUMBER := NULL;
915 l_object_name VARCHAR2(60) := NULL;
916 l_object_description VARCHAR2(240) := NULL;
917 l_count NUMBER := p_attrib_value_tbl.count;
918 l_curr_row NUMBER := NULL;
919 l_attrib_value_tbl JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
920 := p_attrib_value_tbl;
921 l_type_map_id NUMBER := NULL;
922 l_obj_map_id NUMBER := NULL;
923 l_active_flag VARCHAR2(1) := 'Y';
924 l_value_id NUMBER := NULL;
925 l_object_version_number NUMBER :=NULL;
926 l_obj_type_version_no NUMBER := NULL;
927 BEGIN
928
929 -- ******* Create_lf_object Execution Plan ********
930 -- Create_lf_object execution steps
931 --1. Check if the profile exists
932 -- check_duplicate_profiles()
933 -- If not, raise an error and exit
934 --2. Check if Type Exists
935 -- If not, raise an error and exit
936 --3. Create the Object
937 -- If not successful, raise error and exit
938 --4. Loop though for each object type - attribute map defined
939 -- For each map id
940 -- Create object map
941 -- Create a value rec
942
943 -- *******Create_lf_object Standard Begins ********
944
945 -- Standard Start of API savepoint
946 SAVEPOINT CREATE_PERZ_LF_PVT;
947
948 -- Standard call to check for call compatibility.
949 -- IF NOT FND_API.Compatible_API_Call (
950 -- l_api_version_number,
951 -- p_api_version_number,
952 -- l_api_name,
953 -- G_PKG_NAME)
954 -- THEN
955 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
956 -- END IF;
957
958 -- Initialize message list if p_init_msg_list is set to TRUE.
959 IF FND_API.to_Boolean( p_init_msg_list )
960 THEN
961 FND_MSG_PUB.initialize;
962 END IF;
963
964 -- Initialize API return status to success
965 x_return_status := FND_API.G_RET_STS_SUCCESS;
966
967 -- Create_lf_object implementation
968
969 --1. Check if the profile exists
970
971 l_profile_id := p_profile_id;
972 l_profile_name := p_profile_name;
973 l_return_status := FND_API.G_TRUE;
974 if ((p_profile_id is NULL) OR
975 (p_profile_id = FND_API.G_MISS_NUM)) then
976 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
977 l_profile_name,
978 l_return_status,
979 l_profile_id
980 );
981
982
983 -- If profile does not exists, raise an error and exit
984
985 if (l_return_status = FND_API.G_FALSE) then
986 RAISE FND_API.G_EXC_ERROR;
987 end if;
988
989 end if;
990
991 --2. Check if Type Exists
992
993 l_return_status := FND_API.G_TRUE;
994 check_duplicate_obj_type (
995 l_object_type,
996 l_return_status,
997 l_object_type_id,
998 l_obj_type_version_no
999 );
1000
1001 -- If object type is not found raising the error
1002 if (l_return_status = FND_API.G_FALSE) then
1003 --dbms_output.put_line('object type check failed');
1004 RAISE FND_API.G_EXC_ERROR;
1005 end if;
1006
1007 --3.Create the Object
1008
1009 -- Check if parent exists only if the parent id is given
1010 l_return_status := FND_API.G_TRUE;
1011 l_object_id := p_parent_id;
1012 l_object_name := NULL;
1013 if (p_parent_id is not null) AND
1014 (p_parent_id <> FND_API.G_MISS_NUM)then
1015 check_duplicate_obj (
1016 p_application_id,
1017 l_object_name,
1018 l_object_id,
1019 l_return_status,
1020 l_object_version_number
1021 );
1022
1023 if (l_return_status = FND_API.G_FALSE) then
1024 RAISE FND_API.G_EXC_ERROR;
1025 end if;
1026 end if;
1027
1028 -- Check if object exists only if the parent id is given
1029 l_return_status := FND_API.G_TRUE;
1030 l_object_id := p_object_id;
1031 l_object_name := p_object_name;
1032 check_duplicate_obj (
1033 p_application_id,
1034 l_object_name,
1035 l_object_id,
1036 l_return_status,
1037 l_object_version_number
1038 );
1039 if (l_return_status = FND_API.G_TRUE) then
1040 --dbms_output.put_line('object check failed ');
1041 RAISE FND_API.G_EXC_ERROR;
1042 end if;
1043
1044 -- Create Object
1045 l_object_name := p_object_name ;
1046 l_object_id := p_object_id;
1047 -- Object_description is not a parameter in public and pvt spec. hence
1048 -- passing null to the table handler - Srikanth 9-3-99
1049 Insert_jtf_perz_lf_object(
1050 l_rowid,
1051 l_object_id,
1052 p_parent_id,
1053 p_application_id,
1054 p_object_name,
1055 l_object_description
1056 );
1057
1058 if (l_rowid is null) then
1059 RAISE FND_API.G_EXC_ERROR;
1060 end if;
1061
1062 -- Loop though for each attribute defined in the attrib_value_tbl
1063 -- For each of the object_type - attribute map
1064 -- create object (instance of the obj type) map
1065 -- Create a value rec
1066
1067 FOR l_curr_row in 1..l_count LOOP
1068
1069 if ((l_attrib_value_tbl(l_curr_row).attribute_id is null) OR
1070 (l_attrib_value_tbl(l_curr_row).attribute_id =FND_API.G_MISS_NUM)) then
1071 if ((l_attrib_value_tbl(l_curr_row).attribute_name is not null) AND
1072 (l_attrib_value_tbl(l_curr_row).attribute_name <> FND_API.G_MISS_CHAR)) AND
1073 ((l_attrib_value_tbl(l_curr_row).attribute_type is not null) AND
1074 (l_attrib_value_tbl(l_curr_row).attribute_type <> FND_API.G_MISS_CHAR))
1075 then
1076 begin
1077 select a.obj_type_map_id
1078 into l_type_map_id
1079 from jtf_perz_obj_type_map a, jtf_perz_lf_attrib b
1080 where object_type_id = l_object_type_id
1081 and attribute_name = l_attrib_value_tbl(l_curr_row).attribute_name
1082 and attribute_type = l_attrib_value_tbl(l_curr_row).attribute_type
1083 and a.attribute_id = b.attribute_id
1084 and a.object_type_id = l_object_type_id;
1085
1086 EXCEPTION
1087 WHEN NO_DATA_FOUND then
1088 --dbms_output.put_line('In 1st select 1 exception');
1089 RAISE FND_API.G_EXC_ERROR;
1090 WHEN OTHERS then
1091 --dbms_output.put_line('In 1st select 2 exception');
1092 RAISE FND_API.G_EXC_ERROR;
1093 end;
1094 end if;
1095 else
1096 begin
1097 select a.obj_type_map_id
1098 into l_type_map_id
1099 from jtf_perz_obj_type_map a
1100 where a.object_type_id = l_object_type_id
1101 and a.attribute_id = l_attrib_value_tbl(l_curr_row).attribute_id;
1102
1103 EXCEPTION
1104 WHEN NO_DATA_FOUND then
1105 --dbms_output.put_line('In 2nd select 1 exception');
1106 RAISE FND_API.G_EXC_ERROR;
1107 WHEN OTHERS then
1108 --dbms_output.put_line('In 2nd select 2 exception');
1109 RAISE FND_API.G_EXC_ERROR;
1110 END;
1111
1112 end if;
1113
1114 l_rowid := NULL;
1115 l_obj_map_id := NULL;
1116 insert_jtf_perz_obj_map(
1117 l_rowid,
1118 l_obj_map_id,
1119 l_type_map_id,
1120 l_object_id
1121 );
1122
1123 if (l_rowid is null) then
1124 --dbms_output.put_line('In insert into obj map error');
1125 RAISE FND_API.G_EXC_ERROR;
1126 end if;
1127
1128 l_rowid := NULL;
1129 l_value_id := NULL;
1130 --dbms_output.put_line('l_obj_map_id '||l_obj_map_id);
1131 insert_jtf_perz_lf_value(
1132 l_rowid,
1133 l_value_id,
1134 l_profile_id,
1135 l_obj_map_id,
1136 l_attrib_value_tbl(l_curr_row).attribute_value,
1137 l_active_flag,
1138 l_attrib_value_tbl(l_curr_row).priority
1139 );
1140
1141 if (l_rowid is null) then
1142 --dbms_output.put_line('In insert into lf value error');
1143 RAISE FND_API.G_EXC_ERROR;
1144 end if;
1145 END LOOP;
1146 x_object_id := l_object_id;
1147
1148 -- ******** Standard Ends ***********
1149 --
1150 -- End of API body.
1151 --
1152 -- 7. Commit the whole thing
1153
1154 -- Standard check of p_commit.
1155 IF FND_API.To_Boolean ( p_commit )
1156 THEN
1157 COMMIT WORK;
1158 END IF;
1159
1160 -- Debug Message
1161 -- Standard call to get message count and if count is 1, get message info.
1162 FND_MSG_PUB.Count_And_Get (
1163 p_count => x_msg_count,
1164 p_data => x_msg_data
1165 );
1166
1167
1168 EXCEPTION
1169
1170 WHEN FND_API.G_EXC_ERROR THEN
1171
1172 ROLLBACK TO CREATE_PERZ_LF_PVT;
1173 x_return_status := FND_API.G_RET_STS_ERROR ;
1174 --x_return_status := sqlcode||sqlerrm;
1175
1176 FND_MSG_PUB.Count_And_Get
1177 ( p_count => x_msg_count,
1178 p_data => x_msg_data
1179 );
1180
1181 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1182 THEN
1183 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1184 END IF;
1185
1186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1187
1188 ROLLBACK TO CREATE_PERZ_LF_PVT;
1189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1190
1191 FND_MSG_PUB.Count_And_Get
1192 ( p_count => x_msg_count,
1193 p_data => x_msg_data
1194 );
1195
1196 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1197 THEN
1198 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1199 END IF;
1200
1201 WHEN OTHERS THEN
1202
1203 ROLLBACK TO CREATE_PERZ_LF_PVT;
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1205
1206 FND_MSG_PUB.Count_And_Get
1207 ( p_count => x_msg_count,
1208 p_data => x_msg_data
1209 );
1210
1211 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1212 THEN
1213 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1214 END IF;
1215
1216 END Create_lf_object;
1217
1218 -- *****************************************************************************
1219 -- *****************************************************************************
1220
1221 -- Start of Comments
1222 --
1223 -- API name : Update_lf_object
1224 -- Type : Public
1225 -- Function : Update attribute-value pairs for a given LF object and profile
1226 --
1227 -- Paramaeters :
1228 -- IN :
1229 -- p_api_version_number IN NUMBER Required
1230 -- p_init_msg_list IN VARCHAR2 Optional
1231 -- Default = FND_API.G_FALSE
1232 -- p_commit IN VARCHAR2
1233 -- Default = FND_API.G_FALSE
1234 --
1235 -- p_profile_id IN NUMBER Optional
1236 -- p_profile_name IN VARCHAR2 Optional
1237 --
1238 -- p_application_id IN NUMBER Required
1239 -- p_parent_id IN NUMBER Required
1240 -- p_object_id IN NUMBER Optional
1241 -- p_object_name IN VARCHAR2 Optional
1242 -- p_active_flag IN VARCHAR2 Optional
1243
1244 -- p_object_type_id IN NUMBER Optional
1245 -- p_object_type IN VARCHAR2 Optional
1246 -- p_attrib_value_tbl IN JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
1247 -- := JTF_PERZ_LF_PUB.G_MISS_ATTRIB_VALUE_TBL Optional
1248 --
1249 -- OUT :
1250 -- x_object_id OUT NUMBER
1251 -- x_return_status OUT VARCHAR2(1)
1252 -- x_msg_count OUT NUMBER
1253 -- x_msg_data OUT VARCHAR2(2000)
1254 --
1255 --
1256 --
1257 -- Version : Current version 1.0
1258 -- Initial version 1.0
1259 --
1260 -- Notes: Object id or name must be specified.
1261 -- Profile id or name must be specified.
1262 -- Current Restrictions: Will not handle the case where the object_type
1263 -- it self is changes on the object - Srikanth 9-8-99
1264 --
1265 -- *****************************************************************************
1266 PROCEDURE Update_lf_object
1267 ( p_api_version_number IN NUMBER,
1268 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1269 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1270
1271 p_profile_id IN NUMBER,
1272 p_profile_name IN VARCHAR2 := NULL,
1273
1274 p_application_id IN NUMBER,
1275 p_parent_id IN NUMBER := NULL,
1276 p_object_Id IN NUMBER,
1277 p_object_name IN VARCHAR2 := NULL,
1278 p_active_flag IN VARCHAR2,
1279
1280 p_object_type_id IN NUMBER,
1281 p_object_type IN VARCHAR2 := NULL,
1282
1283 p_attrib_value_tbl IN JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
1284 := JTF_PERZ_LF_PUB.G_MISS_ATTRIB_VALUE_TBL,
1285
1286 x_object_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1287 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1288 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1289 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1290 )IS
1291
1292 -- ******* Update_lf_object Local Variables - Standards ********
1293 l_api_name VARCHAR2(60) := 'Update Object';
1294 l_api_version_number NUMBER := p_api_version_number;
1295
1296 -- ******* Update_lf_object Local Variables ********
1297 l_return_status VARCHAR2(240) := FND_API.G_TRUE;
1298 l_Object_Tbl JTF_PERZ_LF_PUB.LF_OBJECT_OUT_TBL_TYPE;
1299 l_profile_id NUMBER := NULL;
1300 l_profile_name VARCHAR2(60) := NULL;
1301 l_object_type VARCHAR2(60) := p_object_type;
1302 l_object_type_id NUMBER := p_object_type_id;
1303 l_rowid ROWID := NULL;
1304 l_object_name VARCHAR2(60) := p_object_name;
1305 l_parent_name VARCHAR2(60) := NULL;
1306 l_object_id NUMBER := p_object_id;
1307 l_parent_id NUMBER := p_parent_id;
1308
1309 l_attrib_value_tbl JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
1310 := p_attrib_value_tbl;
1311 l_db_attrib_value_tbl JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE;
1312 l_count NUMBER := p_attrib_value_tbl.count;
1313 l_curr_row NUMBER := NULL;
1314 l_value_id NUMBER := NULL;
1315 l_active_flag VARCHAR2(1) := upper(p_active_flag);
1316 l_db_active_flag VARCHAR2(1) := NULL;
1317 l_obj_map_id NUMBER := NULL;
1318 l_map_id NUMBER := NULL;
1319 l_attribute_id NUMBER := NULL;
1320 l_object_description VARCHAR2(240) := NULL;
1321 l_object_version_number NUMBER := NULL;
1322 l_obj_type_version_no NUMBER := NULL;
1323 l_parent_version_number NUMBER := NULL;
1324 l_obj_value_ver_no NUMBER := NULL;
1325
1326
1327
1328 BEGIN
1329 -- ******* Update_lf_object Execution Plan ********
1330 -- update_lf_object execution steps
1331 --
1332 --1. Check Profile, raise exception if error
1333 --2. Check Object Type, raise exception if error
1334 --3. Check Object, raise exception if error
1335 --4. Loop through attribute table supplied (p_Attrib_Value_tbl)
1336 -- and compare the values with the above plsql table type
1337 -- 4.1 Check the attribute
1338 -- if different, raise the error
1339 -- if not, compare the values with the database
1340 -- if different, update the values in DB table
1341 -- End Loop
1342 --
1343 --
1344 --5. Commit the whole thing
1345
1346 -- ******* Standard Begins ********
1347
1348 -- Standard Start of API savepoint
1349 SAVEPOINT UPDATE_PERZ_LF_PVT;
1350
1351 -- Standard call to check for call compatibility.
1352 --IF NOT FND_API.Compatible_API_Call
1353 --( l_api_version_number, p_api_version_number,
1354 -- l_api_name, G_PKG_NAME) THEN
1355 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1356 --END IF;
1357
1358 -- Initialize message list if p_init_msg_list is set to TRUE.
1359 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1360 FND_MSG_PUB.initialize;
1361 END IF;
1362
1363 -- Initialize API return status to success
1364 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1365
1366
1367 -- Update_lf_object implementation
1368
1369 --1. Check if the profile exists
1370
1371 l_profile_id := p_profile_id;
1372 l_profile_name := p_profile_name;
1373 l_return_status := FND_API.G_TRUE;
1374 if ((p_profile_id is NULL) OR
1375 (p_profile_id = FND_API.G_MISS_NUM)) then
1376 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
1377 l_profile_name,
1378 l_return_status,
1379 l_profile_id
1380 );
1381
1382
1383 -- If profile does not exists, raise an error and exit
1384
1385 if (l_return_status = FND_API.G_FALSE) then
1386 RAISE FND_API.G_EXC_ERROR;
1387 end if;
1388 end if;
1389 --dbms_output.put_line('out 1');
1390 --2. Check if Type Exists
1391
1392 l_return_status := FND_API.G_TRUE;
1393 check_duplicate_obj_type (
1394 l_object_type,
1395 l_return_status,
1396 l_object_type_id,
1397 l_obj_type_version_no
1398 );
1399
1400 -- If object type is not found raising the error
1401 if (l_return_status = FND_API.G_FALSE) then
1402 RAISE FND_API.G_EXC_ERROR;
1403 end if;
1404
1405 --dbms_output.put_line('out 2');
1406
1407 -- 3. check the object
1408
1409 l_return_status := FND_API.G_TRUE;
1410 l_object_name := p_object_name;
1411 l_object_id := p_object_id;
1412 check_duplicate_obj (
1413 p_application_id,
1414 l_object_name,
1415 l_object_id,
1416 l_return_status,
1417 l_object_version_number
1418 );
1419 if (l_return_status = FND_API.G_FALSE) then
1420 RAISE FND_API.G_EXC_ERROR;
1421 else
1422 -- Check if the input parent id exists
1423 l_return_status := FND_API.G_TRUE;
1424 l_parent_name := NULL;
1425 l_parent_id := p_parent_id;
1426 if (l_parent_id is not null) then
1427 check_duplicate_obj (
1428 p_application_id,
1429 l_parent_name,
1430 l_parent_id,
1431 l_return_status,
1432 l_parent_version_number
1433 );
1434 if (l_return_status = FND_API.G_FALSE) then
1435 -- parent id does not exist
1436 RAISE FND_API.G_EXC_ERROR;
1437 else
1438 -- Update the object with the parent id
1439 update_jtf_perz_lf_object (
1440 l_object_id,
1441 l_parent_id,
1442 p_application_id,
1443 l_object_description,
1444 l_rowid,
1445 l_object_version_number
1446 ) ;
1447 end if;
1448 end if;
1449 end if;
1450
1451 --dbms_output.put_line('out 3');
1452
1453 -- Make sure the active flag is given else default it to Yes(Y)
1454 if (l_active_flag is null) then
1455 l_active_flag := 'Y';
1456 /*
1457 else
1458 if (l_active_flag <> 'Y') OR (l_active_flag != 'N') then
1459 --dbms_output.put_line('out 3.2' || l_active_flag);
1460 -- The above comparision must change to FND_API... in final version or later
1461 -- Srikanth - 9-7-99
1462 RAISE FND_API.G_EXC_ERROR;
1463 end if;
1464 */
1465 end if;
1466
1467 --dbms_output.put_line('out 3.2');
1468 for l_curr_row in 1..l_count
1469 LOOP
1470 -- 4.1 Check the attribute
1471 l_rowid := NULL;
1472 l_return_status := FND_API.G_TRUE;
1473 l_attribute_id := NULL;
1474 --dbms_output.put_line('attribute name '||l_attrib_value_tbl(l_curr_row).attribute_name);
1475 --dbms_output.put_line('attribute type '||l_attrib_value_tbl(l_curr_row).attribute_type);
1476 check_attribute(
1477 l_attrib_value_tbl(l_curr_row).attribute_name,
1478 l_attrib_value_tbl(l_curr_row).attribute_type,
1479 l_rowid,
1480 l_attribute_id,
1481 l_return_status
1482 );
1483 if (l_return_status = FND_API.G_FALSE) then
1484 --dbms_output.put_line('out 4');
1485 --RAISE FND_API.G_EXC_ERROR;
1486 -- Enhancement# 1097254
1487 -- This enhancement is done as a temporary solution to
1488 -- allow the java layer users to add an attribute to
1489 -- the object_type on the fly.
1490 -- This was done as there is no API corresponsing to
1491 -- save_lf_object_type at the Java layer and it is not
1492 -- exposed as the class to end users.
1493 -- Once the api is available, the java layer will make
1494 -- separate calls to save_lf_object_type() and
1495 -- save_lf_object() to accomplish the same thing.
1496 -- These are slated to be fixed in future release.
1497 -- Till then, we can create the attribute record and
1498 -- that of the attribute - object type map record.
1499 -- insert row into the attribute table
1500 -- Enhancement 1097254 Begins
1501 l_rowid := NULL;
1502 l_attribute_id := NULL;
1503 insert_jtf_perz_lf_attrib(
1504 l_rowid,
1505 l_attribute_id,
1506 l_attrib_value_tbl(l_curr_row).attribute_name,
1507 l_attrib_value_tbl(l_curr_row).attribute_type
1508 );
1509 if (l_rowid is null) then
1510 -- raising the error if unable to insert the attribute record
1511 RAISE FND_API.G_EXC_ERROR;
1512 end if;
1513 -- create a map entry object_type - attribute in
1514 -- jtf_perz_obj_type_map table
1515 l_map_id := null;
1516 l_rowid := NULL;
1517 insert_jtf_perz_obj_type_map(
1518 l_rowid,
1519 l_map_id,
1520 l_object_type_id,
1521 l_attribute_id
1522 );
1523 if (l_rowid is null) then
1524 -- raising the error if unable to insert the map record
1525 RAISE FND_API.G_EXC_ERROR;
1526 end if;
1527
1528 l_attrib_value_tbl(l_curr_row).attribute_id := l_attribute_id;
1529 l_db_attrib_value_tbl(l_curr_row).attribute_id := l_attribute_id;
1530 l_value_id := NULL;
1531 l_rowid := NULL;
1532 l_obj_map_id := NULL;
1533
1534 -- Enhancement 1097254 Ends
1535 else
1536 l_attrib_value_tbl(l_curr_row).attribute_id := l_attribute_id;
1537 l_db_attrib_value_tbl(l_curr_row).attribute_id := l_attribute_id;
1538 l_value_id := NULL;
1539 l_rowid := NULL;
1540 l_obj_map_id := NULL;
1541 end if;
1542 --dbms_output.put_line('out 5');
1543 BEGIN
1544 select d.perz_lf_value_id, d.obj_map_id, d.attribute_value,
1545 d.active_flag, d.priority, object_version_number
1546 into l_value_id, l_obj_map_id,
1547 l_db_attrib_value_tbl(l_curr_row).attribute_value,
1548 l_db_active_flag,
1549 l_db_attrib_value_tbl(l_curr_row).priority,
1550 l_obj_value_ver_no
1551
1552 from jtf_perz_lf_value d, jtf_perz_obj_map e,
1553 jtf_perz_obj_type_map f
1554 where
1555 e.object_id = l_object_id
1556 and f.object_type_id = l_object_type_id
1557 and f.attribute_id = l_attribute_id
1558 and d.profile_id = l_profile_id
1559 and e.map_id = f.obj_type_map_id
1560 and e.obj_map_id = d.obj_map_id;
1561
1562 --dbms_output.put_line('out 6');
1563 if (( l_db_attrib_value_tbl(l_curr_row).attribute_value <>
1564 l_attrib_value_tbl(l_curr_row).attribute_value) OR
1565 ( l_db_active_flag <> l_active_flag) OR
1566 ( l_db_attrib_value_tbl(l_curr_row).priority <>
1567 l_attrib_value_tbl(l_curr_row).priority))
1568 THEN
1569 update_jtf_perz_lf_value
1570 ( l_rowid,
1571 l_value_id,
1572 l_profile_id,
1573 l_obj_map_id,
1574 l_attrib_value_tbl(l_curr_row).attribute_value,
1575 l_active_flag,
1576 l_attrib_value_tbl(l_curr_row).priority,
1577 l_obj_value_ver_no);
1578 --dbms_output.put_line('out 7');
1579 end if;
1580 EXCEPTION
1581 WHEN NO_DATA_FOUND THEN
1582 --dbms_output.put_line('ecode '|| sqlcode);
1583 --dbms_output.put_line('etext '|| sqlerrm);
1584 --dbms_output.put_line('out 8');
1585 -- insert table handlers must be called for
1586 -- value and object map here - Srikanth
1587 -- Check if the attribute - object type map exists
1588 -- if yes
1589 -- call the table handler for object map
1590 -- with appropriate map id
1591 -- pass the obj_map_id to insert_jtf_perz_lf_value()
1592 -- if not raise error
1593
1594 -- Checking the attribute - object type map
1595 BEGIN
1596 l_map_id := NULL;
1597 select obj_type_map_id into l_map_id
1598 from jtf_perz_obj_type_map
1599 where attribute_id = l_attribute_id
1600 and object_type_id = l_object_type_id;
1601 -- SMATTEGU Bug#1098513 changes begin
1602 BEGIN
1603 select OBJ_MAP_ID into l_obj_map_id
1604 from jtf_perz_obj_map
1605 where MAP_ID = l_map_id
1606 and OBJECT_ID = l_object_id;
1607 EXCEPTION
1608 WHEN NO_DATA_FOUND THEN
1609 l_rowid := NULL;
1610 l_obj_map_id := NULL;
1611 insert_jtf_perz_obj_map(
1612 l_rowid,
1613 l_obj_map_id,
1614 l_map_id,
1615 l_object_id
1616 );
1617 if (l_rowid is null) then
1618 --dbms_output.put_line('while inserting object map');
1619 RAISE FND_API.G_EXC_ERROR;
1620 end if;
1621 END;
1622 -- SMATTEGU Bug#1098513 changes end
1623
1624 l_rowid := NULL;
1625 l_value_id := NULL;
1626 --dbms_output.put_line('l_obj_map_id '||l_obj_map_id);
1627 insert_jtf_perz_lf_value(
1628 l_rowid,
1629 l_value_id,
1630 l_profile_id,
1631 l_obj_map_id,
1632 l_attrib_value_tbl(l_curr_row).attribute_value,
1633 l_active_flag,
1634 l_attrib_value_tbl(l_curr_row).priority
1635 );
1636
1637 if (l_rowid is null) then
1638 RAISE FND_API.G_EXC_ERROR;
1639 end if;
1640
1641 EXCEPTION
1642 WHEN NO_DATA_FOUND THEN
1643 RAISE FND_API.G_EXC_ERROR;
1644 WHEN OTHERS THEN
1645 RAISE FND_API.G_EXC_ERROR;
1646 END;
1647 END;
1648 END LOOP;
1649 x_object_id := l_object_id;
1650 -- End of API body.
1651 --
1652 -- 5. Commit the whole thing
1653
1654 -- Standard check of p_commit.
1655 IF FND_API.To_Boolean ( p_commit ) THEN
1656 COMMIT WORK;
1657 END IF;
1658
1659 -- Debug Message
1660 -- Standard call to get message count and if count is 1, get message info.
1661 FND_MSG_PUB.Count_And_Get(
1662 p_count => x_msg_count,
1663 p_data => x_msg_data
1664 );
1665 EXCEPTION
1666
1667 WHEN FND_API.G_EXC_ERROR THEN
1668
1669 ROLLBACK TO UPDATE_PERZ_LF_PVT;
1670 x_return_status := FND_API.G_RET_STS_ERROR ;
1671
1672 FND_MSG_PUB.Count_And_Get
1673 ( p_count => x_msg_count,
1674 p_data => x_msg_data
1675 );
1676
1677
1678 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1679 THEN
1680 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1681 END IF;
1682
1683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1684
1685 ROLLBACK TO UPDATE_PERZ_LF_PVT;
1686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1687
1688 FND_MSG_PUB.Count_And_Get
1689 ( p_count => x_msg_count,
1690 p_data => x_msg_data
1691 );
1692
1693 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1694 THEN
1695 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1696 END IF;
1697
1698 WHEN OTHERS THEN
1699
1700 ROLLBACK TO UPDATE_PERZ_LF_PVT;
1701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1702
1703 FND_MSG_PUB.Count_And_Get
1704 ( p_count => x_msg_count,
1705 p_data => x_msg_data
1706 );
1707
1708 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1709 THEN
1710 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1711 END IF;
1712 END Update_LF_Object;
1713 -- *****************************************************************************
1714 -- *****************************************************************************
1715
1716 -- Start of Comments
1717 --
1718 -- API name : save_lf_object
1719 -- Type : Public
1720 -- Function : Create and update if exists, attribute value pairs for
1721 -- a given object and profile in an application_id domain.
1722 --
1723 -- Paramaeters :
1724 -- IN :
1725 -- p_api_version_number IN NUMBER Required
1726 -- p_init_msg_list IN VARCHAR2 Optional
1727 -- Default = FND_API.G_FALSE
1728 -- p_application_id IN NUMBER Required
1729 -- p_profile_id IN NUMBER Required
1730 -- p_profile_name IN VARCHAR2 Optional
1731 -- p_profile_type IN VARCHAR2,
1732 -- p_profile_attrib_tbl IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE Optional
1733 -- p_parent_id IN NUMBER Required
1734 -- p_object_id IN NUMBER Optional
1735 -- p_object_name IN VARCHAR2 Required
1736 -- p_object_description IN VARCHAR2 Optional
1737 -- p_object_type_id IN NUMBER Optional
1738 -- p_object_type IN VARCHAR2 Required
1739 -- p_active_flag IN VARCHAR2 Optional
1740 -- Default = NO
1741 -- p_attrib_value_tbl IN JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
1742 -- := JTF_PERZ_LF_PUB.G_MISS_ATTRIB_VALUE_TBL Optional
1743 -- p_commit IN VARCHAR2 Optional
1744 --
1745 -- OUT :
1746 -- x_object_id OUT NUMBER
1747 -- x_return_status OUT VARCHAR2(1)
1748 -- x_msg_count OUT NUMBER
1749 -- x_msg_data OUT VARCHAR2(2000)
1750 --
1751 --
1752 -- Version : Current version 1.0
1753 -- Initial version 1.0
1754 --
1755 -- Notes:
1756 --
1757 -- *****************************************************************************
1758
1759 PROCEDURE save_lf_object
1760 ( p_api_version_number IN NUMBER,
1761 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1762 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1763
1764 p_profile_id IN NUMBER,
1765 p_profile_name IN VARCHAR2,
1766 p_profile_type IN VARCHAR2,
1767 p_profile_attrib_tbl IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
1768 := JTF_PERZ_PROFILE_PUB.G_MISS_PROFILE_ATTRIB_TBL,
1769
1770 p_application_id IN NUMBER,
1771 p_parent_id IN NUMBER,
1772 p_object_type_id IN NUMBER,
1773 p_object_type IN VARCHAR2,
1774
1775 p_object_id IN NUMBER,
1776 p_object_name IN VARCHAR2,
1777 p_object_description IN VARCHAR2,
1778
1779 p_active_flag IN VARCHAR2,
1780 p_attrib_value_tbl IN JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
1781 := JTF_PERZ_LF_PUB.G_MISS_ATTRIB_VALUE_TBL,
1782
1783 x_object_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1784 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1785 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1786 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1787 )IS
1788 -- ******* save_lf_object Local Variables - for standards ********
1789 l_api_name VARCHAR2(60) := 'Save LF Object';
1790 l_api_version_number NUMBER := p_api_version_number;
1791
1792 -- ******* save_lf_object Local Variables - for implementation ********
1793 l_return_status VARCHAR2(240) := FND_API.G_TRUE;
1794 l_profile_id NUMBER := NULL;
1795 l_profile_name VARCHAR2(60) := p_profile_name;
1796 l_profile_type VARCHAR2(30) := p_profile_type;
1797 l_profile_attrib JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
1798 := p_profile_attrib_tbl;
1799 l_commit VARCHAR2(1) := FND_API.G_TRUE;
1800 l_attrib_value_tbl JTF_PERZ_LF_PUB.ATTRIB_VALUE_TBL_TYPE
1801 := p_attrib_value_tbl;
1802 l_object_type VARCHAR2(60) := p_object_type;
1803 l_object_type_id NUMBER := p_object_type_id;
1804 l_rowid ROWID := NULL;
1805 l_object_name VARCHAR2(60) := p_object_name;
1806 l_object_id NUMBER := p_object_id;
1807 l_count NUMBER := p_attrib_value_tbl.count;
1808 l_curr_row NUMBER := NULL;
1809 l_out_object_id NUMBER := NULL;
1810 l_out_object_type_id NUMBER := p_object_type_id;
1811 l_out_obj_type_map_tbl JTF_PERZ_LF_PVT.OBJ_TYPE_MAP_TBL_TYPE;
1812 l_attrib_rec_tbl JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE;
1813 l_active_flag VARCHAR2(1) := upper(p_active_flag);
1814 l_object_type_desc VARCHAR2(240) := NULL;
1815 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1816
1817 l_object_version_number NUMBER := NULL;
1818 l_obj_type_version_no NUMBER := NULL;
1819 l_parent_version_number NUMBER := NULL;
1820 l_obj_value_ver_no NUMBER := NULL;
1821
1822 BEGIN
1823
1824 -- ******* Execution Plan ********
1825
1826 -- save_lf_object execution steps
1827 -- 0. check profile
1828 -- 0.1 if not, create profile
1829 -- 1. Check if object_type exists,
1830 -- 1.1 If not, call save_lf_object_type()
1831 -- 2. check if object exists
1832 -- 2.1 if object exists, Call update_lf_object()
1833 -- 2.2 if not, call create_lf_object()
1834 -- 3. Commit the whole thing
1835
1836
1837 -- ******* Standard Begins ********
1838
1839 -- Standard Start of API savepoint
1840 SAVEPOINT save_lf_object;
1841
1842 -- Standard call to check for call compatibility.
1843 IF NOT FND_API.Compatible_API_Call
1844 ( l_api_version_number,
1845 p_api_version_number,
1846 l_api_name,
1847 G_PKG_NAME)
1848 THEN
1849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1850 END IF;
1851
1852 -- Initialize message list if p_init_msg_list is set to TRUE.
1853 IF FND_API.to_Boolean( p_init_msg_list )
1854 THEN
1855 FND_MSG_PUB.initialize;
1856 END IF;
1857
1858 -- Initialize API return status to success
1859 x_return_status := FND_API.G_RET_STS_SUCCESS;
1860
1861
1862
1863 -- ******* save_lf_object implementation ********
1864 --1. Check if the profile exists. This check will be performed
1865 -- irrespective of whether profile id is given or not.
1866 -- Because, in case of mobile users they might have created
1867 -- a profile on the mobile client and passing the
1868 -- profile id.
1869
1870 l_profile_id := p_profile_id;
1871 l_profile_name := p_profile_name;
1872 l_profile_type := p_profile_type;
1873 l_return_status := FND_API.G_TRUE;
1874
1875 --dbms_output.put_line (' profile id'|| l_profile_id);
1876 --dbms_output.put_line (' profile name'|| l_profile_name);
1877 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
1878 l_profile_name,
1879 l_return_status,
1880 l_profile_id
1881 );
1882
1883 --dbms_output.put_line (' out 1');
1884
1885 -- If profile does not exists, create it.
1886 -- If not successfuk in creation raise an error and exit
1887
1888 if (l_return_status = FND_API.G_FALSE) then
1889
1890 --dbms_output.put_line (' out 2');
1891 l_return_status := FND_API.G_RET_STS_SUCCESS;
1892 l_commit := FND_API.G_FALSE;
1893 l_profile_id := p_profile_id;
1894
1895 JTF_PERZ_PROFILE_PVT.Create_Profile(
1896 p_api_version_number => l_api_version_number,
1897 p_init_msg_list => l_init_msg_list,
1898 p_commit => l_commit,
1899 p_profile_id => l_profile_id,
1900 p_profile_name => l_profile_name,
1901 p_profile_type => l_profile_type,
1902 p_profile_attrib_tbl => l_profile_attrib,
1903 x_profile_name => l_profile_name,
1904 x_profile_id => l_profile_id,
1905 x_return_status => l_return_status,
1906 x_msg_count => x_msg_count,
1907 x_msg_data => x_msg_data
1908 );
1909 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1910 RAISE FND_API.G_EXC_ERROR;
1911 end if;
1912 end if;
1913
1914
1915
1916 -- Enhancement# 1075579
1917 -- This enhancement is done as a temporary solution to allow the
1918 -- java layer users to add an attribute to the object_type on the fly.
1919 -- This was done as there is no API corresponsing to save_lf_object_type
1920 -- at the Java layer and it is not exposed as the class to end users.
1921 -- Once the api is available, the java layer will make separate calls to
1922 -- save_lf_object_type() and save_lf_object() to accomplish the same thing.
1923 -- These are slated to be fixed in future release. Till then, we can comment
1924 -- the following checking for object type and calling the save_lf_object_type()
1925 -- selectively there by forcing the save_lf_object_api() call for
1926 -- each save_lf_object() call. This might have some performance impact.
1927 --Srikanth
1928
1929 /* Enhancement# 1075579 comments start
1930 --2. Check if Type Exists
1931
1932 l_return_status := FND_API.G_TRUE;
1933 check_duplicate_obj_type (
1934 l_object_type,
1935 l_return_status,
1936 l_object_type_id,
1937 l_obj_type_version_no
1938 );
1939
1940 -- If object type does not exists, create it.
1941 -- If not successful in creation raise an error and exit
1942 if (l_return_status = FND_API.G_FALSE) then
1943
1944 Enhancement# 1075579 comments end */
1945 l_return_status := FND_API.G_RET_STS_SUCCESS;
1946 l_commit := FND_API.G_FALSE;
1947
1948 for l_curr_row in 1..l_count
1949 LOOP
1950 l_attrib_rec_tbl(l_curr_row).ATTRIBUTE_ID := l_attrib_value_tbl(l_curr_row).ATTRIBUTE_ID;
1951 l_attrib_rec_tbl(l_curr_row).ATTRIBUTE_NAME := l_attrib_value_tbl(l_curr_row).ATTRIBUTE_NAME;
1952 l_attrib_rec_tbl(l_curr_row).ATTRIBUTE_TYPE := l_attrib_value_tbl(l_curr_row).ATTRIBUTE_TYPE;
1953
1954 END LOOP;
1955
1956 save_lf_object_type
1957 (
1958 p_api_version_number => l_api_version_number,
1959 p_init_msg_list => l_init_msg_list,
1960 p_commit => l_commit,
1961
1962 p_object_type_id => l_object_type_id,
1963 p_object_type => l_object_type,
1964 p_object_type_desc => l_object_type_desc,
1965
1966 p_attrib_rec_tbl => l_attrib_rec_tbl,
1967
1968 x_object_type_id => l_out_object_type_id,
1969 x_obj_type_map_tbl => l_out_obj_type_map_tbl,
1970 x_return_status => l_return_status,
1971 x_msg_count => x_msg_count,
1972 x_msg_data => x_msg_data
1973 );
1974
1975 if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
1976 l_object_type_id := l_out_object_type_id;
1977 -- dbms_output.put_line(' cre type id' ||l_object_type_id);
1978 else
1979 --dbms_output.put_line (' out 6');
1980 RAISE FND_API.G_EXC_ERROR;
1981 end if;
1982
1983 /* Enhancement# 1075579 comments start
1984 end if;
1985 Enhancement# 1075579 comments end */
1986
1987 -- 2. check if object exists
1988
1989
1990
1991 l_return_status := FND_API.G_TRUE;
1992 l_object_name := p_object_name;
1993 l_object_id := p_object_id;
1994
1995 check_duplicate_obj (
1996 p_application_id,
1997 l_object_name,
1998 l_object_id,
1999 l_return_status,
2000 l_object_version_number
2001 );
2002 --dbms_output.put_line(' out 7');
2003 if (l_return_status = FND_API.G_FALSE) then
2004 --dbms_output.put_line(' out 8');
2005 l_return_status := FND_API.G_RET_STS_SUCCESS;
2006 l_commit := FND_API.G_FALSE;
2007
2008 Create_lf_object (
2009 p_api_version_number => l_api_version_number,
2010 p_init_msg_list => l_init_msg_list,
2011 p_commit => l_commit,
2012
2013 p_profile_id => l_profile_id,
2014 p_profile_name => p_profile_name,
2015
2016 p_application_id => p_application_id,
2017 p_parent_id => p_parent_id,
2018 p_object_id => l_object_id,
2019 p_object_name => l_object_name,
2020
2021 p_object_type_id => l_object_type_id,
2022 p_object_type => l_object_type,
2023
2024 p_attrib_value_tbl => l_attrib_value_tbl,
2025
2026 x_object_id => l_out_object_id,
2027 x_return_status => l_return_status,
2028 x_msg_count => x_msg_count,
2029 x_msg_data => x_msg_data
2030 );
2031
2032 if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
2033 --dbms_output.put_line(' out 8.5');
2034 l_object_id := l_out_object_id;
2035 x_object_id := l_out_object_id;
2036 x_return_status := FND_API.G_RET_STS_SUCCESS;
2037 else
2038 --dbms_output.put_line(' out 9');
2039 RAISE FND_API.G_EXC_ERROR;
2040 end if;
2041
2042 else
2043 --dbms_output.put_line(' out 10');
2044 l_return_status := FND_API.G_RET_STS_SUCCESS;
2045 l_commit := FND_API.G_FALSE;
2046
2047 -- Make sure the active flag is given else default it to Yes(Y)
2048 if (l_active_flag is null) then
2049 l_active_flag := 'Y';
2050 end if;
2051
2052 Update_lf_object(
2053 p_api_version_number => l_api_version_number,
2054 p_init_msg_list => l_init_msg_list,
2055 p_commit => l_commit,
2056
2057 p_profile_id => l_profile_id,
2058 p_profile_name => p_profile_name,
2059
2060 p_application_id => p_application_id,
2061 p_parent_id => p_parent_id,
2062 p_object_id => l_object_id,
2063 p_object_name => l_object_name,
2064 p_active_flag => l_active_flag,
2065
2066 p_object_type_id => l_object_type_id,
2067 p_object_type => l_object_type,
2068
2069 p_attrib_value_tbl => l_attrib_value_tbl,
2070
2071 x_object_id => l_out_object_id,
2072 x_return_status => l_return_status,
2073 x_msg_count => x_msg_count,
2074 x_msg_data => x_msg_data
2075 );
2076
2077 if (l_return_status = FND_API.G_RET_STS_SUCCESS) then
2078 --dbms_output.put_line (' out 11');
2079 l_object_id := l_out_object_id;
2080 x_object_id := l_out_object_id;
2081 x_return_status := FND_API.G_RET_STS_SUCCESS;
2082 -- dbms_output.put_line(' out 6');
2083 -- dbms_output.put_line('object_id '||x_object_id);
2084 else
2085 --dbms_output.put_line (' out 12');
2086 RAISE FND_API.G_EXC_ERROR;
2087 end if;
2088
2089 end if;
2090
2091 -- 3. Commit the whole thing
2092
2093 -- ******** Standard Ends ***********
2094 --
2095 -- End of API body.
2096 --
2097
2098 -- Standard check of p_commit.
2099 IF FND_API.To_Boolean ( p_commit )
2100 THEN
2101 COMMIT WORK;
2102 END IF;
2103
2104 -- Debug Message
2105 -- Standard call to get message count and if count is 1, get message info.
2106 FND_MSG_PUB.Count_And_Get
2107 ( p_count => x_msg_count,
2108 p_data => x_msg_data
2109 );
2110
2111 EXCEPTION
2112
2113 WHEN FND_API.G_EXC_ERROR THEN
2114
2115 ROLLBACK TO save_lf_object;
2116 x_return_status := FND_API.G_RET_STS_ERROR ;
2117
2118 FND_MSG_PUB.Count_And_Get
2119 ( p_count => x_msg_count,
2120 p_data => x_msg_data
2121 );
2122
2123 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2124 THEN
2125 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2126 END IF;
2127
2128 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2129
2130 ROLLBACK TO save_lf_object;
2131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2132
2133 FND_MSG_PUB.Count_And_Get
2134 ( p_count => x_msg_count,
2135 p_data => x_msg_data
2136 );
2137
2138 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2139 THEN
2140 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2141 END IF;
2142
2143 WHEN OTHERS THEN
2144
2145 ROLLBACK TO save_lf_object;
2146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2147
2148 FND_MSG_PUB.Count_And_Get
2149 ( p_count => x_msg_count,
2150 p_data => x_msg_data
2151 );
2152
2153 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2154 THEN
2155 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2156 END IF;
2157 END save_lf_object;
2158
2159 -- -- *****************************************************************************
2160 -- -- *****************************************************************************
2161 -- Start of Comments
2162 --
2163 -- API name : Get_lf_object
2164 -- Type : Public
2165 -- Function : Get attribute value pairs for a given LF object and profile
2166 --
2167 -- Paramaeters :
2168 -- IN :
2169 -- p_api_version_number IN NUMBER Required
2170 -- p_init_msg_list IN VARCHAR2 Optional
2171 -- Default = FND_API.G_FALSE
2172 -- p_profile_id IN NUMBER Optional
2173 -- p_profile_name IN VARCHAR2 Optional
2174 -- p_parent_id IN NUMBER Optional
2175 -- p_object_id IN NUMBER Optional
2176 -- p_object_name IN VARCHAR2 Optional
2177 -- p_obj_active_flag IN VARCHAR2 Optional
2178 -- p_get_children_flag IN VARCHAR2 Optional
2179 --
2180 -- OUT :
2181 -- x_object_id OUT Optional
2182 -- x_Object_Tbl OUT JTF_PERZ_LF_PUB.LF_OBJECT_OUT_TBL_TYPE,
2183 -- x_return_status OUT VARCHAR2(1)
2184 -- x_msg_count OUT NUMBER
2185 -- x_msg_data OUT VARCHAR2(2000)
2186 --
2187 --
2188 --
2189 -- Version : Current version 1.0
2190 -- Initial version 1.0
2191 --
2192 -- Notes:
2193 --
2194 -- *****************************************************************************
2195
2196 PROCEDURE Get_lf_object
2197 ( p_api_version_number IN NUMBER,
2198 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2199
2200 p_application_id IN NUMBER,
2201 p_priority IN NUMBER,
2202 p_profile_id IN NUMBER,
2203 p_profile_name IN VARCHAR2,
2204 P_Object_Id IN NUMBER,
2205 p_Object_Name IN VARCHAR,
2206 p_obj_active_flag IN VARCHAR2,
2207 p_get_children_flag IN VARCHAR2,
2208 x_Object_Tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_LF_PUB.LF_OBJECT_OUT_TBL_TYPE,
2209 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2210 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2211 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2212 )IS
2213 -- local variable in get_lf_object to adhere to standards
2214
2215 l_api_version_number NUMBER := p_api_version_number;
2216 l_api_name CONSTANT VARCHAR2(30)
2217 := 'Get LF Object';
2218 -- local variable in get_lf_object to adhere to standards
2219 l_profile_id NUMBER := NULL;
2220 l_return_status VARCHAR2(240) := FND_API.G_RET_STS_SUCCESS;
2221 l_profile_name VARCHAR2(60) := NULL;
2222 l_object_name VARCHAR2(60) := p_object_name;
2223 l_object_id NUMBER := p_object_id;
2224 l_active_flag VARCHAR2(1) := upper(p_obj_active_flag);
2225 l_count NUMBER;
2226 l_value_id NUMBER;
2227
2228 l_object_out_rec JTF_PERZ_LF_PUB.LF_OBJECT_OUT_REC_TYPE := NULL;
2229
2230 l_Object_Tbl JTF_PERZ_LF_PUB.LF_OBJECT_OUT_TBL_TYPE;
2231
2232 Cursor C_get_child_pioi(p_profile_id NUMBER, p_application_id NUMBER,
2233 p_object_id NUMBER, p_active_flag VARCHAR2) IS
2234
2235 select /*+ first_rows */
2236 d.perz_lf_value_id, a.parent_id, a.object_id, a.application_id, a.object_name,
2237 a.object_description, b.object_type_id, b.object_type_name,
2238 c.attribute_id, c.attribute_name, c.attribute_type,
2239 d.attribute_value, d.active_flag, d.priority
2240 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2241 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2242 jtf_perz_obj_map e, jtf_perz_obj_type_map f
2243 where
2244 a.parent_id = p_object_id
2245 and a.application_id = p_application_id
2246 and d.profile_id = p_profile_id
2247 and d.active_flag = p_active_flag
2248 and e.object_id = a.object_id
2249 and e.obj_map_id = d.obj_map_id
2250 and e.map_id = f.obj_type_map_id
2251 and f.object_type_id = b.object_type_id
2252 and f.attribute_id = c.attribute_id
2253 ORDER BY a.object_id;
2254
2255 Cursor C_get_child_pnoi(p_profile_name VARCHAR2, p_application_id NUMBER,
2256 p_object_id NUMBER, p_active_flag VARCHAR2) IS
2257
2258 select /*+ first_rows */
2259 d.perz_lf_value_id, a.parent_id, a.object_id, a.application_id, a.object_name,
2260 a.object_description, b.object_type_id, b.object_type_name,
2261 c.attribute_id, c.attribute_name, c.attribute_type,
2262 d.attribute_value, d.active_flag, d.priority
2263 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2264 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2265 jtf_perz_obj_map e, jtf_perz_obj_type_map f,
2266 jtf_perz_profile p
2267 where
2268 a.parent_id = p_object_id
2269 and a.application_id = p_application_id
2270 and d.profile_id = p.profile_id
2271 and d.active_flag = p_active_flag
2272 and p.profile_name = p_profile_name
2273 and e.object_id = a.object_id
2274 and e.obj_map_id = d.obj_map_id
2275 and e.map_id = f.obj_type_map_id
2276 and f.object_type_id = b.object_type_id
2277 and f.attribute_id = c.attribute_id
2278 ORDER BY a.object_id;
2279
2280 Cursor C_get_child_pnon(p_profile_name VARCHAR2, p_application_id NUMBER,
2281 p_object_name VARCHAR2, p_active_flag VARCHAR2) IS
2282
2283 select d.perz_lf_value_id, a.parent_id, a.object_id, a.application_id, a.object_name,
2284 a.object_description, b.object_type_id, b.object_type_name,
2285 c.attribute_id, c.attribute_name, c.attribute_type,
2286 d.attribute_value, d.active_flag, d.priority
2287 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2288 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2289 jtf_perz_obj_map e, jtf_perz_obj_type_map f,
2290 jtf_perz_lf_object o, jtf_perz_profile p
2291 where
2292 o.object_name = p_object_name
2293 and o.object_id = a.parent_id
2294 and a.application_id = p_application_id
2295 and d.profile_id = p.profile_id
2296 and d.active_flag = p_active_flag
2297 and p.profile_name = p_profile_name
2298 and e.object_id = a.object_id
2299 and e.obj_map_id = d.obj_map_id
2300 and e.map_id = f.obj_type_map_id
2301 and f.object_type_id = b.object_type_id
2302 and f.attribute_id = c.attribute_id
2303 ORDER BY a.object_id;
2304
2305 Cursor C_get_child_pion(p_profile_id NUMBER, p_application_id NUMBER,
2306 p_object_name VARCHAR2, p_active_flag VARCHAR2) IS
2307
2308 select d.perz_lf_value_id, a.parent_id, a.object_id, a.application_id, a.object_name,
2309 a.object_description, b.object_type_id, b.object_type_name,
2310 c.attribute_id, c.attribute_name, c.attribute_type,
2311 d.attribute_value, d.active_flag, d.priority
2312 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2313 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2314 jtf_perz_obj_map e, jtf_perz_obj_type_map f,
2315 jtf_perz_lf_object o
2316 where
2317 o.object_name = p_object_name
2318 and o.object_id = a.parent_id
2319 and a.application_id = p_application_id
2320 and d.profile_id = p_profile_id
2321 and d.active_flag = p_active_flag
2322 and e.object_id = a.object_id
2323 and e.obj_map_id = d.obj_map_id
2324 and e.map_id = f.obj_type_map_id
2325 and f.object_type_id = b.object_type_id
2326 and f.attribute_id = c.attribute_id
2327 ORDER BY a.object_id;
2328
2329 Cursor C_get_no_child_pioi (p_profile_id NUMBER, p_application_id NUMBER,
2330 p_object_id NUMBER, p_active_flag VARCHAR2) IS
2331 select d.perz_lf_value_id, a.parent_id, a.object_id,
2332 a.application_id, a.object_name,
2333 a.object_description, b.object_type_id, b.object_type_name,
2334 c.attribute_id, c.attribute_name, c.attribute_type,
2335 d.attribute_value, d.active_flag, d.priority
2336 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2337 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2338 jtf_perz_obj_map e, jtf_perz_obj_type_map f
2339 where
2340 a.object_id = p_object_id
2341 and a.application_id = p_application_id
2342 and d.profile_id = p_profile_id
2343 and d.active_flag = p_active_flag
2344 and e.object_id = a.object_id
2345 and e.obj_map_id = d.obj_map_id
2346 and e.map_id = f.obj_type_map_id
2347 and f.object_type_id = b.object_type_id
2348 and f.attribute_id = c.attribute_id
2349 ORDER BY a.object_id;
2350
2351 Cursor C_get_no_child_pnoi (p_profile_name VARCHAR2, p_application_id NUMBER,
2352 p_object_id NUMBER, p_active_flag VARCHAR2) IS
2353 select d.perz_lf_value_id, a.parent_id, a.object_id,
2354 a.application_id, a.object_name,
2355 a.object_description, b.object_type_id, b.object_type_name,
2356 c.attribute_id, c.attribute_name, c.attribute_type,
2357 d.attribute_value, d.active_flag, d.priority
2358 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2359 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2360 jtf_perz_obj_map e, jtf_perz_obj_type_map f,
2361 jtf_perz_profile p
2362 where
2363 a.object_id = p_object_id
2364 and a.application_id = p_application_id
2365 and d.profile_id = p.profile_id
2366 and p.profile_name = p_profile_name
2367 and d.active_flag = p_active_flag
2368 and e.object_id = a.object_id
2369 and e.obj_map_id = d.obj_map_id
2370 and e.map_id = f.obj_type_map_id
2371 and f.object_type_id = b.object_type_id
2372 and f.attribute_id = c.attribute_id
2373 ORDER BY a.object_id;
2374
2375 Cursor C_get_no_child_pnon (p_profile_name VARCHAR2, p_application_id NUMBER,
2376 p_object_name VARCHAR2, p_active_flag VARCHAR2) IS
2377 select d.perz_lf_value_id, a.parent_id, a.object_id,
2378 a.application_id, a.object_name,
2379 a.object_description, b.object_type_id, b.object_type_name,
2380 c.attribute_id, c.attribute_name, c.attribute_type,
2381 d.attribute_value, d.active_flag, d.priority
2382 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2383 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2384 jtf_perz_obj_map e, jtf_perz_obj_type_map f,
2385 jtf_perz_profile p
2386 where
2387 a.object_name = p_object_name
2388 and a.application_id = p_application_id
2389 and d.profile_id = p.profile_id
2390 and p.profile_name = p_profile_name
2391 and d.active_flag = p_active_flag
2392 and e.object_id = a.object_id
2393 and e.obj_map_id = d.obj_map_id
2394 and e.map_id = f.obj_type_map_id
2395 and f.object_type_id = b.object_type_id
2396 and f.attribute_id = c.attribute_id
2397 ORDER BY a.object_id;
2398
2399
2400 Cursor C_get_no_child_pion (p_profile_id NUMBER, p_application_id NUMBER,
2401 p_object_name VARCHAR2, p_active_flag VARCHAR2) IS
2402 select d.perz_lf_value_id, a.parent_id, a.object_id,
2403 a.application_id, a.object_name,
2404 a.object_description, b.object_type_id, b.object_type_name,
2405 c.attribute_id, c.attribute_name, c.attribute_type,
2406 d.attribute_value, d.active_flag, d.priority
2407 from jtf_perz_lf_object a, jtf_perz_lf_object_type b,
2408 jtf_perz_lf_attrib c, jtf_perz_lf_value d,
2409 jtf_perz_obj_map e, jtf_perz_obj_type_map f
2410 where
2411 a.object_name = p_object_name
2412 and a.application_id = p_application_id
2413 and d.profile_id = p_profile_id
2414 and d.active_flag = p_active_flag
2415 and e.object_id = a.object_id
2416 and e.obj_map_id = d.obj_map_id
2417 and e.map_id = f.obj_type_map_id
2418 and f.object_type_id = b.object_type_id
2419 and f.attribute_id = c.attribute_id
2420 ORDER BY a.object_id;
2421
2422
2423
2424 BEGIN
2425
2426 -- ******* Get_lf_object Execution Plan ********
2427
2428 -- 1. Check the validity of the profile name (if only name is given)
2429 -- if not valid raise error and exit
2430 -- 2. If object name is given, check it's validity
2431 -- 3. If the get_children flag, select the info
2432 -- where object_id supplied is the parent_id
2433 -- 4. If get_children_flag is not
2434 -- select the info depensing on the profile, object and
2435 -- application ids.
2436
2437 -- ******* Standard Begins ********
2438
2439 -- Standard call to check for call compatibility.
2440 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
2441 -- THEN
2442 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2443 -- END IF;
2444
2445 -- Initialize message list if p_init_msg_list is set to TRUE.
2446 IF FND_API.to_Boolean( p_init_msg_list )
2447 THEN
2448 FND_MSG_PUB.initialize;
2449 END IF;
2450
2451 -- Initialize API return status to success
2452 x_return_status := FND_API.G_RET_STS_SUCCESS;
2453
2454
2455 -- ******* Get_lf_object implementation ********
2456
2457 -- Make sure the active flag is given else default it to Yes(Y)
2458 if (l_active_flag is null) OR (l_active_flag = FND_API.G_MISS_CHAR) then
2459 l_active_flag := 'Y';
2460 end if;
2461
2462 l_object_name := p_object_name;
2463 l_object_id := p_object_id;
2464 l_profile_id := p_profile_id;
2465 l_profile_name := p_profile_name;
2466
2467 -- Assumption is application id is provided
2468 -- irrespective what ever be the case - srikanth Nov 9th 1999
2469 -- 3. If the get_children flag
2470
2471 if (UPPER(p_get_children_flag) = 'Y') then
2472
2473 -- get the children only if the parent is active.
2474 -- Currently this condition is not handled -Srikanth 9-5-99
2475
2476 if (((p_profile_id IS NOT NULL) AND
2477 (p_profile_id <> FND_API.G_MISS_NUM)) AND
2478 ((p_object_id IS NOT NULL) AND
2479 (p_object_id <> FND_API.G_MISS_NUM))) then
2480 l_count := 1;
2481
2482 open C_get_child_pioi (l_profile_id, p_application_id,
2483 l_object_id,l_active_flag);
2484 loop
2485
2486 fetch C_get_child_pioi
2487 into l_value_id,
2488 l_object_out_rec.parent_id,
2489 l_object_out_rec.object_id,
2490 l_object_out_rec.application_id,
2491 l_object_out_rec.object_name,
2492 l_object_out_rec.object_description,
2493 l_object_out_rec.object_type_id,
2494 l_object_out_rec.object_type,
2495 l_object_out_rec.attribute_id,
2496 l_object_out_rec.attribute_name,
2497 l_object_out_rec.attribute_type,
2498 l_object_out_rec.attribute_value,
2499 l_object_out_rec.active_flag,
2500 l_object_out_rec.priority;
2501 exit when C_get_child_pioi%NOTFOUND;
2502 if ( C_get_child_pioi%FOUND = TRUE) then
2503 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2504 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2505 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2506 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2507 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2508 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2509 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2510 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2511 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2512 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2513 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2514 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2515 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2516 l_count := l_count + 1;
2517 end if;
2518 end loop;
2519 close C_get_child_pioi;
2520
2521 elsif (((p_profile_name IS NOT NULL) AND
2522 (p_profile_name <> FND_API.G_MISS_CHAR ))AND
2523 ((p_object_id IS NOT NULL) AND
2524 (p_object_id <> FND_API.G_MISS_NUM))) then
2525 l_count := 1;
2526
2527 open C_get_child_pnoi (l_profile_name, p_application_id,
2528 l_object_id,l_active_flag);
2529 loop
2530
2531 fetch C_get_child_pnoi
2532 into l_value_id,
2533 l_object_out_rec.parent_id,
2534 l_object_out_rec.object_id,
2535 l_object_out_rec.application_id,
2536 l_object_out_rec.object_name,
2537 l_object_out_rec.object_description,
2538 l_object_out_rec.object_type_id,
2539 l_object_out_rec.object_type,
2540 l_object_out_rec.attribute_id,
2541 l_object_out_rec.attribute_name,
2542 l_object_out_rec.attribute_type,
2543 l_object_out_rec.attribute_value,
2544 l_object_out_rec.active_flag,
2545 l_object_out_rec.priority;
2546 exit when C_get_child_pnoi%NOTFOUND;
2547 if ( C_get_child_pnoi%FOUND = TRUE) then
2548 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2549 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2550 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2551 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2552 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2553 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2554 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2555 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2556 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2557 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2558 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2559 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2560 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2561 l_count := l_count + 1;
2562 end if;
2563 end loop;
2564 close C_get_child_pnoi;
2565
2566 elsif (((p_profile_name IS NOT NULL) AND
2567 (p_profile_name <> FND_API.G_MISS_CHAR) )AND
2568 ((p_object_name IS NOT NULL) AND
2569 (p_object_name <> FND_API.G_MISS_CHAR))) then
2570 l_count := 1;
2571
2572 open C_get_child_pnon (l_profile_name, p_application_id,
2573 l_object_name,l_active_flag);
2574 loop
2575
2576 fetch C_get_child_pnon
2577 into l_value_id,
2578 l_object_out_rec.parent_id,
2579 l_object_out_rec.object_id,
2580 l_object_out_rec.application_id,
2581 l_object_out_rec.object_name,
2582 l_object_out_rec.object_description,
2583 l_object_out_rec.object_type_id,
2584 l_object_out_rec.object_type,
2585 l_object_out_rec.attribute_id,
2586 l_object_out_rec.attribute_name,
2587 l_object_out_rec.attribute_type,
2588 l_object_out_rec.attribute_value,
2589 l_object_out_rec.active_flag,
2590 l_object_out_rec.priority;
2591 exit when C_get_child_pnon%NOTFOUND;
2592 if ( C_get_child_pnon%FOUND = TRUE) then
2593 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2594 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2595 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2596 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2597 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2598 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2599 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2600 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2601 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2602 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2603 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2604 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2605 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2606 l_count := l_count + 1;
2607 end if;
2608 end loop;
2609 close C_get_child_pnon;
2610
2611 elsif (((p_profile_id IS NOT NULL) AND
2612 (p_profile_id <> FND_API.G_MISS_NUM) )AND
2613 ((p_object_name IS NOT NULL) AND
2614 (p_object_name <> FND_API.G_MISS_CHAR))) then
2615 l_count := 1;
2616
2617 open C_get_child_pion (l_profile_id, p_application_id,
2618 l_object_name,l_active_flag);
2619 loop
2620
2621 fetch C_get_child_pion
2622 into l_value_id,
2623 l_object_out_rec.parent_id,
2624 l_object_out_rec.object_id,
2625 l_object_out_rec.application_id,
2626 l_object_out_rec.object_name,
2627 l_object_out_rec.object_description,
2628 l_object_out_rec.object_type_id,
2629 l_object_out_rec.object_type,
2630 l_object_out_rec.attribute_id,
2631 l_object_out_rec.attribute_name,
2632 l_object_out_rec.attribute_type,
2633 l_object_out_rec.attribute_value,
2634 l_object_out_rec.active_flag,
2635 l_object_out_rec.priority;
2636 exit when C_get_child_pion%NOTFOUND;
2637 if ( C_get_child_pion%FOUND = TRUE) then
2638 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2639 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2640 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2641 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2642 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2643 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2644 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2645 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2646 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2647 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2648 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2649 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2650 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2651 l_count := l_count + 1;
2652 end if;
2653 end loop;
2654 close C_get_child_pion;
2655
2656 else
2657 RAISE FND_API.G_EXC_ERROR;
2658 end if;
2659
2660
2661 else /* Do Not get children case */
2662
2663 if (((p_profile_id IS NOT NULL) AND
2664 (p_profile_id <> FND_API.G_MISS_NUM)) AND
2665 ((p_object_id IS NOT NULL) AND
2666 (p_object_id <> FND_API.G_MISS_NUM))) then
2667 l_count := 1;
2668
2669 open C_get_no_child_pioi (l_profile_id, p_application_id,
2670 l_object_id,l_active_flag);
2671 loop
2672
2673 fetch C_get_no_child_pioi
2674 into l_value_id,
2675 l_object_out_rec.parent_id,
2676 l_object_out_rec.object_id,
2677 l_object_out_rec.application_id,
2678 l_object_out_rec.object_name,
2679 l_object_out_rec.object_description,
2680 l_object_out_rec.object_type_id,
2681 l_object_out_rec.object_type,
2682 l_object_out_rec.attribute_id,
2683 l_object_out_rec.attribute_name,
2684 l_object_out_rec.attribute_type,
2685 l_object_out_rec.attribute_value,
2686 l_object_out_rec.active_flag,
2687 l_object_out_rec.priority;
2688 exit when C_get_no_child_pioi%NOTFOUND;
2689 if ( C_get_no_child_pioi%FOUND = TRUE) then
2690 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2691 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2692 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2693 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2694 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2695 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2696 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2697 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2698 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2699 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2700 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2701 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2702 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2703 l_count := l_count + 1;
2704 end if;
2705 end loop;
2706 close C_get_no_child_pioi;
2707
2708 elsif (((p_profile_name IS NOT NULL) AND
2709 (p_profile_name <> FND_API.G_MISS_CHAR) )AND
2710 ((p_object_id IS NOT NULL) AND
2711 (p_object_id <> FND_API.G_MISS_NUM))) then
2712 l_count := 1;
2713
2714 open C_get_no_child_pnoi (l_profile_name, p_application_id,
2715 l_object_id,l_active_flag);
2716 loop
2717
2718 fetch C_get_no_child_pnoi
2719 into l_value_id,
2720 l_object_out_rec.parent_id,
2721 l_object_out_rec.object_id,
2722 l_object_out_rec.application_id,
2723 l_object_out_rec.object_name,
2724 l_object_out_rec.object_description,
2725 l_object_out_rec.object_type_id,
2726 l_object_out_rec.object_type,
2727 l_object_out_rec.attribute_id,
2728 l_object_out_rec.attribute_name,
2729 l_object_out_rec.attribute_type,
2730 l_object_out_rec.attribute_value,
2731 l_object_out_rec.active_flag,
2732 l_object_out_rec.priority;
2733 exit when C_get_no_child_pnoi%NOTFOUND;
2734 if ( C_get_no_child_pnoi%FOUND = TRUE) then
2735 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2736 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2737 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2738 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2739 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2740 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2741 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2742 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2743 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2744 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2745 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2746 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2747 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2748 l_count := l_count + 1;
2749 end if;
2750 end loop;
2751 close C_get_no_child_pnoi;
2752
2753 elsif (((p_profile_name IS NOT NULL) AND
2754 (p_profile_name <> FND_API.G_MISS_CHAR) )AND
2755 ((p_object_name IS NOT NULL) AND
2756 (p_object_name <> FND_API.G_MISS_CHAR))) then
2757 l_count := 1;
2758
2759 open C_get_no_child_pnon (l_profile_name, p_application_id,
2760 l_object_name,l_active_flag);
2761 loop
2762
2763 fetch C_get_no_child_pnon
2764 into l_value_id,
2765 l_object_out_rec.parent_id,
2766 l_object_out_rec.object_id,
2767 l_object_out_rec.application_id,
2768 l_object_out_rec.object_name,
2769 l_object_out_rec.object_description,
2770 l_object_out_rec.object_type_id,
2771 l_object_out_rec.object_type,
2772 l_object_out_rec.attribute_id,
2773 l_object_out_rec.attribute_name,
2774 l_object_out_rec.attribute_type,
2775 l_object_out_rec.attribute_value,
2776 l_object_out_rec.active_flag,
2777 l_object_out_rec.priority;
2778 exit when C_get_no_child_pnon%NOTFOUND;
2779 if ( C_get_no_child_pnon%FOUND = TRUE) then
2780 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2781 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2782 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2783 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2784 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2785 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2786 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2787 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2788 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2789 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2790 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2791 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2792 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2793 l_count := l_count + 1;
2794 end if;
2795 end loop;
2796 close C_get_no_child_pnon;
2797
2798 elsif (((p_profile_id IS NOT NULL) AND
2799 (p_profile_id <> FND_API.G_MISS_NUM) )AND
2800 ((p_object_name IS NOT NULL) AND
2801 (p_object_name <> FND_API.G_MISS_CHAR))) then
2802 l_count := 1;
2803
2804 open C_get_no_child_pion (l_profile_id, p_application_id,
2805 l_object_name,l_active_flag);
2806 loop
2807
2808 fetch C_get_no_child_pion
2809 into l_value_id,
2810 l_object_out_rec.parent_id,
2811 l_object_out_rec.object_id,
2812 l_object_out_rec.application_id,
2813 l_object_out_rec.object_name,
2814 l_object_out_rec.object_description,
2815 l_object_out_rec.object_type_id,
2816 l_object_out_rec.object_type,
2817 l_object_out_rec.attribute_id,
2818 l_object_out_rec.attribute_name,
2819 l_object_out_rec.attribute_type,
2820 l_object_out_rec.attribute_value,
2821 l_object_out_rec.active_flag,
2822 l_object_out_rec.priority;
2823 exit when C_get_no_child_pion%NOTFOUND;
2824 if ( C_get_no_child_pion%FOUND = TRUE) then
2825 l_object_tbl(l_count).parent_id := l_object_out_rec.parent_id;
2826 l_object_tbl(l_count).object_id := l_object_out_rec.object_id;
2827 l_object_tbl(l_count).application_id := l_object_out_rec.application_id;
2828 l_object_tbl(l_count).object_name := l_object_out_rec.object_name;
2829 l_object_tbl(l_count).object_description := l_object_out_rec.object_description;
2830 l_object_tbl(l_count).object_type_id := l_object_out_rec.object_type_id;
2831 l_object_tbl(l_count).object_type := l_object_out_rec.object_type;
2832 l_object_tbl(l_count).attribute_id := l_object_out_rec.attribute_id;
2833 l_object_tbl(l_count).attribute_name := l_object_out_rec.attribute_name;
2834 l_object_tbl(l_count).attribute_type := l_object_out_rec.attribute_type;
2835 l_object_tbl(l_count).attribute_value := l_object_out_rec.attribute_value;
2836 l_object_tbl(l_count).active_flag := l_object_out_rec.active_flag;
2837 l_object_tbl(l_count).priority := l_object_out_rec.priority;
2838 l_count := l_count + 1;
2839 end if;
2840 end loop;
2841 close C_get_no_child_pion;
2842
2843 else
2844 RAISE FND_API.G_EXC_ERROR;
2845 end if;
2846
2847
2848 /* If we have to raise an error if there are no records in the value table,
2849 then uncomment the following three lines - Srikanth
2850 if(l_object_tbl.count = 0) then
2851 RAISE FND_API.G_EXC_ERROR;
2852 end if;
2853
2854 */
2855 end if;
2856
2857 x_object_tbl := l_object_tbl;
2858
2859
2860 EXCEPTION
2861
2862 WHEN FND_API.G_EXC_ERROR THEN
2863
2864 x_return_status := FND_API.G_RET_STS_ERROR ;
2865
2866 FND_MSG_PUB.Count_And_Get
2867 ( p_count => x_msg_count,
2868 p_data => x_msg_data
2869 );
2870
2871 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2872 THEN
2873 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2874 END IF;
2875
2876 END Get_lf_object;
2877
2878 -- *****************************************************************************
2879 -- *****************************************************************************
2880
2881 -- Start of Comments
2882 --
2883 -- API name : Get_lf_object_type
2884 -- Type : Public
2885 -- Function : Get attribute pairs for a given LF object_type
2886 --
2887 -- Paramaeters :
2888 -- IN :
2889 -- p_api_version_number IN NUMBER Required
2890 -- p_init_msg_list IN VARCHAR2 Optional
2891 -- Default = FND_API.G_FALSE
2892 --
2893 -- p_object_type IN VARCHAR2 Optional
2894 -- p_object_type_desc IN VARCHAR2 Optional
2895 -- p_object_type_id IN NUMBER Optional
2896 --
2897
2898 -- OUT :
2899 -- x_object_type_id OUT NUMBER
2900 -- x_object_type_desc OUT VARCHAR,
2901 -- x_attrib_rec_tbl OUT JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE
2902 -- x_return_status OUT VARCHAR2(1)
2903 -- x_msg_count OUT NUMBER
2904 -- x_msg_data OUT VARCHAR2(2000)
2905 --
2906 --
2907 --
2908 -- Version : Current version 1.0
2909 -- Initial version 1.0
2910 --
2911 -- Notes:
2912 --
2913 -- *****************************************************************************
2914
2915 PROCEDURE Get_lf_object_type
2916 ( p_api_version_number IN NUMBER,
2917 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2918
2919 p_Object_type IN VARCHAR,
2920 p_Object_type_Id IN NUMBER,
2921
2922 x_Object_type_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
2923 x_object_type_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2924
2925 x_attrib_rec_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE,
2926 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2927 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2928 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2929 )IS
2930
2931 l_api_version_number NUMBER := p_api_version_number;
2932 l_api_name CONSTANT VARCHAR2(30) := 'GET_LF_OBJECT_TYPE';
2933 l_return_status VARCHAR2(240) := FND_API.G_RET_STS_SUCCESS;
2934 l_attrib_rec_tbl JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE;
2935 l_Object_type_Id NUMBER := p_Object_type_Id;
2936 l_Object_type VARCHAR2(60) := p_Object_type;
2937 l_object_type_desc VARCHAR2(240) := NULL;
2938
2939 BEGIN
2940
2941 -- ******* Execution Plan Get_lf_object_type ********
2942 --1.Call get_obj_type_details()
2943 -- 2. return the output returned by the above function
2944 -- along with appropriate return status.
2945 --
2946
2947 -- ******* Standard Begins Get_lf_object_type********
2948
2949 -- Standard call to check for call compatibility.
2950 IF NOT FND_API.Compatible_API_Call
2951 ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
2952 THEN
2953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2954 END IF;
2955
2956 -- Initialize message list if p_init_msg_list is set to TRUE.
2957 IF FND_API.to_Boolean( p_init_msg_list )
2958 THEN
2959 FND_MSG_PUB.initialize;
2960 END IF;
2961
2962 -- Initialize API return status to success
2963 x_return_status := FND_API.G_RET_STS_SUCCESS;
2964
2965 -- ******* Get_lf_object_type implementation ********
2966
2967 -- Calling the get_obj_type_details
2968 -- dbms_output.put_line('out 1');
2969 l_return_status := FND_API.G_TRUE;
2970
2971 get_obj_type_details
2972 (
2973 l_Object_type,
2974 l_Object_type_Id,
2975 l_object_type_desc,
2976 l_attrib_rec_tbl,
2977 l_return_status
2978 );
2979
2980 -- dbms_output.put_line('out 2');
2981
2982 if (l_return_status = FND_API.G_FALSE) then
2983 -- dbms_output.put_line('out 3');
2984 x_return_status := FND_API.G_RET_STS_ERROR ;
2985 else
2986 --dbms_output.put_line('out 4');
2987 x_Object_type_Id := l_Object_type_Id;
2988 --dbms_output.put_line('l_Object_type_Id:'||l_Object_type_Id);
2989 x_object_type_desc := l_object_type_desc;
2990 x_attrib_rec_tbl := l_attrib_rec_tbl;
2991 end if;
2992
2993 END Get_lf_object_type;
2994 -- *****************************************************************************
2995 -- *****************************************************************************
2996 -- Start of Comments
2997 --
2998 -- API name : save_lf_object_type
2999 -- Type : Public
3000 -- Function : This procedure will create or update the given lf
3001 -- object type.
3002 --
3003 -- Paramaeters :
3004 -- IN :
3005 -- p_api_version_number IN NUMBER Required
3006 -- p_init_msg_list IN VARCHAR2 Optional
3007 -- Default = FND_API.G_FALSE
3008 -- p_commit IN VARCHAR2 Optional
3009
3010 -- p_object_type_id IN NUMBER Optional
3011 -- p_object_type IN VARCHAR2 Optional
3012 -- p_object_type_desc IN VARCHAR2 Optional
3013
3014 -- p_attribute_rec_tbl IN JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE Required
3015 --
3016 -- OUT :
3017 -- x_object_type_id OUT NUMBER
3018 -- x_obj_type_map_tbl OUT JTF_PERZ_LF_PUB.OBJ_TYPE_MAP_TBL_TYPE
3019 -- x_return_status OUT VARCHAR2
3020 -- x_msg_count OUT NUMBER
3021 -- x_msg_data OUT VARCHAR2(2000)
3022 --
3023 --
3024 -- Version : Current version 1.0
3025 -- Initial version 1.0
3026 --
3027 -- Notes:
3028 --
3029 -- *****************************************************************************
3030
3031 PROCEDURE save_lf_object_type
3032 ( p_api_version_number IN NUMBER,
3033 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3034 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3035
3036 p_object_type_id IN NUMBER,
3037 p_object_type IN VARCHAR2,
3038 p_object_type_desc IN VARCHAR2,
3039
3040 p_attrib_rec_tbl IN JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE
3041 := JTF_PERZ_LF_PUB.G_MISS_ATTRIB_REC_TBL,
3042
3043 x_object_type_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
3044
3045 x_obj_type_map_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_LF_PVT.OBJ_TYPE_MAP_TBL_TYPE,
3046 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3047 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3048 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3049 )IS
3050
3051 -- ******* Local Variables ********
3052 -- Following variables are needed to adhere to standards
3053 l_api_version_number NUMBER := p_api_version_number;
3054 l_api_name CONSTANT VARCHAR2(30) := 'Save Object Type';
3055
3056 -- Following variables are needed to implement this procedure
3057 l_return_status VARCHAR2(240) := FND_API.G_RET_STS_SUCCESS;
3058 l_rowid ROWID := NULL;
3059 l_OBJECT_TYPE_ID NUMBER := p_object_type_id;
3060 l_OBJECT_TYPE VARCHAR2(60) := p_object_type;
3061 l_OBJECT_TYPE_DESC VARCHAR2(240) := p_object_type_desc;
3062 l_attrib_rec_tbl JTF_PERZ_LF_PUB.ATTRIB_REC_TBL_TYPE
3063 := p_attrib_rec_tbl;
3064 l_count NUMBER := p_attrib_rec_tbl.count;
3065 l_curr_row NUMBER := NULL;
3066 l_inserted_map NUMBER := 0;
3067 l_map_id NUMBER := NULL;
3068 l_attribute_id NUMBER := NULL;
3069
3070 l_obj_type_version_no NUMBER := NULL;
3071 BEGIN
3072
3073 -- ******* Execution Plan ********
3074 -- save_lf_object_type execution steps
3075
3076 --Check for duplicate object_type by calling check_duplicate_obj_type()
3077 -- If the object type exists, loop through each attribute supplied
3078 -- compare the supplied attribute and that of the attribute associated
3079 -- with the object type in DB.
3080 -- For each attribute that is not in the DB, insert the attribute and
3081 -- object type - attribute map.
3082 -- If the object does not exist
3083 -- Create the object
3084 -- loop through each attribute supplied
3085 -- If the attribute exists in the attribute store already then
3086 -- create the object type - attribute map
3087 -- else
3088 -- create the attribute that is not present in the DB
3089 -- create the object type - attribute map
3090
3091 -- ******* Standard Begins ********
3092
3093 -- Standard Start of API savepoint
3094 SAVEPOINT SAVE_PERZ_LF_TYPE_PVT;
3095
3096 -- Standard call to check for call compatibility.
3097 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
3098 -- THEN
3099 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3100 -- END IF;
3101
3102 -- Initialize message list if p_init_msg_list is set to TRUE.
3103 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3104 FND_MSG_PUB.initialize;
3105 END IF;
3106
3107 -- Initialize API return status to success
3108 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3109
3110 -- ******* save_lf_object_type implementation ********
3111
3112 -- Check if object type exists
3113
3114 l_return_status := FND_API.G_TRUE;
3115 check_duplicate_obj_type (
3116 l_object_type ,
3117 l_return_status ,
3118 l_object_type_id,
3119 l_obj_type_version_no
3120 );
3121
3122
3123 if (l_return_status = FND_API.G_TRUE) then
3124 -- Object Type already exists.
3125 -- Update the Object Type Description
3126 -- Loop through each attribute supplied
3127 -- compare the supplied attribute and that of the attribute associated
3128 -- with the object type in DB.
3129 -- For each attribute that is not associated with the type in the database,
3130 -- Check if the attribute is in the DB
3131 -- If not insert the attribute and create the
3132 -- object type - attribute map
3133 -- If yes i.e., attribute exists in the DB, create the
3134 -- object type - attribute map.
3135
3136 -- Copying the object type id to output parameter
3137 x_object_type_id := l_object_type_id;
3138
3139
3140 -- Update the object
3141 l_rowid := NULL;
3142 update_jtf_perz_lf_obj_type(
3143 l_object_type_desc,
3144 l_object_type_id,
3145 l_rowid,
3146 l_obj_type_version_no
3147 );
3148
3149 if (l_rowid is null) then
3150 RAISE FND_API.G_EXC_ERROR;
3151 end if;
3152 IF (l_count> 0) THEN
3153 FOR l_curr_row in 1..l_count LOOP
3154 -- Check if the attribute is there in the DB or not
3155 l_rowid := NULL;
3156 l_return_status := FND_API.G_TRUE;
3157 l_attribute_id := NULL;
3158 --dbms_output.put_line('attribute name '||l_attrib_value_tbl(l_curr_row).attribute_name);
3159 --dbms_output.put_line('attribute type '||l_attrib_value_tbl(l_curr_row).attribute_type);
3160 check_attribute(
3161 p_attrib_rec_tbl(l_curr_row).ATTRIBUTE_NAME,
3162 p_attrib_rec_tbl(l_curr_row).ATTRIBUTE_TYPE,
3163 l_rowid,
3164 l_attribute_id,
3165 l_return_status
3166 );
3167 if (l_return_status = FND_API.G_FALSE) then
3168 --dbms_output.put_line('out 4');
3169 -- If the attribute is not there in the DB
3170 -- Create the attribute
3171
3172 l_rowid := NULL;
3173 l_attribute_id := NULL;
3174 insert_jtf_perz_lf_attrib(
3175 l_rowid,
3176 l_attribute_id,
3177 l_attrib_rec_tbl(l_curr_row).attribute_name,
3178 l_attrib_rec_tbl(l_curr_row).attribute_type
3179 );
3180 if (l_rowid is null) then
3181 -- unable to insert the attribute record
3182 RAISE FND_API.G_EXC_ERROR;
3183 end if;
3184 end if;
3185
3186 -- Now, we know we have an attribute in the DB.
3187 -- inserting record in x_obj_type_map_tbl for output
3188 x_obj_type_map_tbl(l_inserted_map).ATTRIBUTE_ID := l_attribute_id;
3189 x_obj_type_map_tbl(l_inserted_map).OBJECT_TYPE_ID := l_object_type_id;
3190
3191
3192 -- For a given object type Check if the attribute map exists or not
3193
3194 BEGIN
3195 select obj_type_map_id into x_obj_type_map_tbl(l_inserted_map).TYPE_MAP_ID
3196 from jtf_perz_obj_type_map
3197 where object_type_id = l_object_type_id
3198 and attribute_id = l_attribute_id;
3199 l_inserted_map := l_inserted_map + 1;
3200
3201 EXCEPTION
3202 WHEN NO_DATA_FOUND THEN
3203 -- There is no map for the given object type and attribute
3204 -- Create the map
3205 l_map_id := null;
3206 l_rowid := NULL;
3207 insert_jtf_perz_obj_type_map(
3208 l_rowid,
3209 l_map_id,
3210 l_object_type_id,
3211 l_attribute_id
3212 );
3213 if (l_rowid is null) then
3214 -- raising the error if unable to insert the map record
3215 RAISE FND_API.G_EXC_ERROR;
3216 else
3217 -- inserting record in x_obj_type_map_tbl for output
3218 x_obj_type_map_tbl(l_inserted_map).TYPE_MAP_ID := l_map_id;
3219 l_inserted_map := l_inserted_map + 1;
3220 end if;
3221 END;
3222
3223 END LOOP;
3224
3225 end if;
3226
3227 else
3228
3229 -- Object Type does not exists.
3230
3231 -- Create the object type
3232 -- dbms_output.put_line('l_object_type_id '||l_object_type_id);
3233 -- dbms_output.put_line('l_object_type '||l_OBJECT_TYPE);
3234 -- dbms_output.put_line('l_object_type_desc '||l_OBJECT_TYPE_desc);
3235
3236 if ((l_OBJECT_TYPE is not null) and
3237 (l_OBJECT_TYPE <> FND_API.G_MISS_CHAR)) then
3238 Insert_JTF_PERZ_LF_OBJ_TYPE(
3239 l_Rowid,
3240 l_OBJECT_TYPE_ID,
3241 l_OBJECT_TYPE,
3242 l_OBJECT_TYPE_DESC
3243 );
3244 else
3245 RAISE FND_API.G_EXC_ERROR;
3246 end if;
3247
3248
3249 if (l_rowid is null) then
3250 RAISE FND_API.G_EXC_ERROR;
3251 else
3252 x_OBJECT_TYPE_ID := l_OBJECT_TYPE_ID;
3253 end if;
3254 -- loop through each attribute supplied
3255 -- If the attribute exists in the attribute store already then
3256 -- create the object type - attribute map
3257 -- else
3258 -- create the attribute that is not present in the DB
3259 -- create the object type - attribute map
3260
3261 -- Copying the object type id to output parameter
3262 x_object_type_id := l_object_type_id;
3263
3264 IF (l_count> 0) THEN
3265
3266 FOR l_curr_row in 1..l_count LOOP
3267 BEGIN
3268 SELECT attribute_id INTO l_attribute_id
3269 FROM JTF_PERZ_LF_ATTRIB
3270 WHERE attribute_name = p_attrib_rec_tbl(l_curr_row).ATTRIBUTE_NAME and
3271 attribute_type = p_attrib_rec_tbl(l_curr_row).ATTRIBUTE_TYPE;
3272 EXCEPTION
3273 WHEN NO_DATA_FOUND THEN
3274 -- insert row into the attribute table
3275 l_rowid := NULL;
3276 l_attribute_id := NULL;
3277 insert_jtf_perz_lf_attrib(
3278 l_rowid,
3279 l_attribute_id,
3280 l_attrib_rec_tbl(l_curr_row).attribute_name,
3281 l_attrib_rec_tbl(l_curr_row).attribute_type
3282 );
3283 if (l_rowid is null) then
3284 -- raising the error if unable to insert the attribute record
3285 RAISE FND_API.G_EXC_ERROR;
3286 end if;
3287 END;
3288 -- create a map entry object_type - attribute in
3289 -- jtf_perz_obj_type_map table
3290 l_map_id := null;
3291 l_rowid := NULL;
3292 insert_jtf_perz_obj_type_map(
3293 l_rowid,
3294 l_map_id,
3295 l_object_type_id,
3296 l_attribute_id
3297 );
3298 if (l_rowid is null) then
3299 -- raising the error if unable to insert the map record
3300 RAISE FND_API.G_EXC_ERROR;
3301 else
3302 -- inserting record in x_obj_type_map_tbl for output
3303 x_obj_type_map_tbl(l_inserted_map).ATTRIBUTE_ID := l_attribute_id;
3304 x_obj_type_map_tbl(l_inserted_map).OBJECT_TYPE_ID := l_object_type_id;
3305 x_obj_type_map_tbl(l_inserted_map).TYPE_MAP_ID := l_map_id;
3306 l_inserted_map := l_inserted_map + 1;
3307 end if;
3308 END LOOP;
3309
3310 end if;
3311 end if;
3312
3313 -- ******** Standard Ends ***********
3314 --
3315 -- End of API body.
3316 --
3317 -- 7. Commit the whole thing
3318
3319 -- Standard check of p_commit.
3320 IF FND_API.To_Boolean ( p_commit )
3321 THEN
3322 COMMIT WORK;
3323 END IF;
3324
3325 -- Debug Message
3326 -- Standard call to get message count and if count is 1, get message info.
3327 FND_MSG_PUB.Count_And_Get
3328 ( p_count => x_msg_count,
3329 p_data => x_msg_data
3330 );
3331
3332
3333 EXCEPTION
3334
3335 WHEN FND_API.G_EXC_ERROR THEN
3336
3337 ROLLBACK TO SAVE_PERZ_LF_TYPE_PVT;
3338 x_return_status := FND_API.G_RET_STS_ERROR ;
3339
3340 FND_MSG_PUB.Count_And_Get
3341 ( p_count => x_msg_count,
3342 p_data => x_msg_data
3343 );
3344
3345 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3346 THEN
3347 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3348 END IF;
3349
3350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3351
3352 ROLLBACK TO SAVE_PERZ_LF_TYPE_PVT;
3353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3354
3355 FND_MSG_PUB.Count_And_Get
3356 ( p_count => x_msg_count,
3357 p_data => x_msg_data
3358 );
3359
3360 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3361 THEN
3362 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3363 END IF;
3364
3365 WHEN OTHERS THEN
3366
3367 ROLLBACK TO SAVE_PERZ_LF_TYPE_PVT;
3368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3369
3370 FND_MSG_PUB.Count_And_Get
3371 ( p_count => x_msg_count,
3372 p_data => x_msg_data
3373 );
3374
3375 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3376 THEN
3377 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3378 END IF;
3379
3380 END save_lf_object_type;
3381 -- *****************************************************************************
3382 -- *****************************************************************************
3383
3384 END JTF_PERZ_LF_PVT ;