[Home] [Help]
PACKAGE BODY: APPS.JTF_PERZ_PROFILE_PVT
Source
1 PACKAGE BODY JTF_PERZ_PROFILE_PVT as
2 /* $Header: jtfzvpfb.pls 120.2 2005/11/02 22:47:33 skothe ship $ */
3 --
4 --
5 -- Start of Comments
6 --
7 -- NAME
8 --
9 -- JTF_PERZ_PROFILE_PVT
10 --
11 -- PURPOSE
12 -- Private API for managing common functionality across the personalization
13 -- framework.
14 --
15 -- NOTES
16 -- This is used by public as well as private APIs.
17 --
18 -- HISTORY
19 -- 06/07/99 SMATTEGU Created. Added check_profile_duplicates()
20 -- 06/10/99 CCHANDRA Added table handlers, copy routines etc.
21 -- 06/12/99 CCHANDRA Added all procedures
22 -- 07/30/99 SMATTEGU Modified insert_row_profile_Attribute to handle profile_attrib_id.
23 -- 07/30/99 SMATTEGU Modified update_row_profile_attribute, create_profile, update_profile
24 -- to reflect the profile_attrib_id change
25 -- 08/03/99 SMATTEGU Added save point and the corresponding
26 -- commit and rollback sections in update_profile()
27 -- 08/04/99 SMATTEGU Updated update_perz_profile table handler
28 -- * Commented profile_desc it is not sent from update_profile
29 -- * Commented the profile id section
30 -- * specified IN, IN OUTs for the parameters
31 -- 08/04/99 SMATTEGU done many changes to update_profile()
32 --
33 -- 08/04/99 SMATTEGU Updated the update_row_profile_attrib()
34 -- * commented the profile id and attribute id lines
35 -- * specified IN, IN OUTs for the parameters
36 -- * where clause must use the profile id and attribute name instead of rowid
37 -- 09/30/99 SMATTEGU Updated the create_profile() to handle profile_id also.
38 -- Updated insert_row_jtf_perz_profile(), insert_row_profile_attrib().
39 --
40 -- 11/04/99 SMATTEGU Changing the names to suite the standards and adding who columns
41 --
42 -- End of Comments
43
44 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_PERZ_PROFILE_PVT';
45 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvzpfb.pls';
46
47
48 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
49 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
50
51
52 -- ****************************************************************************
53 -- ****************************************************************************
54 -- TABLE HANDLERS
55 -- 1. insert_row_jtf_perz_profile()
56 -- 2. update_row_jtf_perz_profile()
57 -- 4. delete_row_jtf_perz_profile()
58 -- 5. update_row_profile_attrib()
59 -- 6. insert_row_profile_attrib()
60 -- 7. delete_row_profile_attrib()
61 --
62 -- ****************************************************************************
63
64 PROCEDURE insert_row_jtf_perz_profile(
65 X_ROWID OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
66 x_PROFILE_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
67 x_PROFILE_NAME IN VARCHAR2,
68 x_PROFILE_TYPE IN VARCHAR2,
69 x_PROFILE_DESCRIPTION IN VARCHAR2,
70 x_ACTIVE_FLAG IN VARCHAR2,
71 p_OBJECT_VERSION_NUMBER IN NUMBER
72 ) IS
73
74 CURSOR C IS SELECT rowid FROM JTF_PERZ_PROFILE
75 WHERE PROFILE_ID = x_PROFILE_ID;
76
77 CURSOR C2 IS SELECT JTF_PERZ_PROFILE_S.NEXTVAL FROM SYS.DUAL;
78
79 BEGIN
80
81 IF (X_PROFILE_ID IS NULL) OR ( X_PROFILE_ID = FND_API.G_MISS_NUM) THEN
82 OPEN C2;
83 FETCH C2 INTO X_PROFILE_ID;
84 CLOSE C2;
85 END IF;
86
87 INSERT INTO JTF_PERZ_PROFILE(
88 PROFILE_ID,
89 PROFILE_NAME,
90 PROFILE_TYPE,
91 PROFILE_DESCRIPTION,
92 ACTIVE_FLAG,
93 OBJECT_VERSION_NUMBER,
94 CREATED_BY,
95 LAST_UPDATE_DATE,
96 LAST_UPDATED_BY,
97 LAST_UPDATE_LOGIN)
98 VALUES ( x_PROFILE_ID,
99 decode( x_PROFILE_NAME, FND_API.G_MISS_CHAR, NULL ,x_PROFILE_NAME ),
100 decode( x_PROFILE_TYPE, FND_API.G_MISS_CHAR, NULL ,x_PROFILE_TYPE ),
101 decode( x_PROFILE_DESCRIPTION, FND_API.G_MISS_CHAR, NULL ,x_PROFILE_DESCRIPTION ),
102 decode( x_ACTIVE_FLAG, FND_API.G_MISS_CHAR, NULL ,x_ACTIVE_FLAG ),
103 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, 1, p_OBJECT_VERSION_NUMBER),
104 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);
105
106 OPEN C;
107 FETCH C INTO x_Rowid;
108 IF (C%NOTFOUND) THEN
109 CLOSE C;
110 RAISE NO_DATA_FOUND;
111 END IF;
112
113 END insert_row_jtf_perz_profile;
114 -- ****************************************************************************
115 --
116 -- Procedure to UPDATE a row in the JTF_PERZ_PROFILE table
117 --
118
119 PROCEDURE update_row_jtf_perz_profile(
120 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
121 x_PROFILE_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
122 x_PROFILE_NAME IN VARCHAR2,
123 x_PROFILE_TYPE IN VARCHAR2,
124 x_PROFILE_DESCRIPTION IN VARCHAR2,
125 x_ACTIVE_FLAG IN VARCHAR2,
126 p_OBJECT_VERSION_NUMBER IN NUMBER
127 ) IS
128
129 l_object_version_number NUMBER := p_OBJECT_VERSION_NUMBER;
130
131 BEGIN
132
133 UPDATE JTF_PERZ_PROFILE SET
134 PROFILE_NAME = decode( x_PROFILE_NAME, FND_API.G_MISS_CHAR, PROFILE_NAME, x_PROFILE_NAME ),
135 PROFILE_TYPE = decode( x_PROFILE_TYPE, FND_API.G_MISS_CHAR, PROFILE_TYPE, x_PROFILE_TYPE ),
136 PROFILE_DESCRIPTION = decode( x_PROFILE_DESCRIPTION, FND_API.G_MISS_CHAR,
137 PROFILE_DESCRIPTION,x_PROFILE_DESCRIPTION ),
138 ACTIVE_FLAG = decode( x_ACTIVE_FLAG, FND_API.G_MISS_CHAR, ACTIVE_FLAG,
139 x_ACTIVE_FLAG ),
140 OBJECT_VERSION_NUMBER = decode (p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
141 OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
142 LAST_UPDATE_DATE = SYSDATE,
143 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
144 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
145 WHERE profile_id = x_PROFILE_ID
146 and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
147
148 IF (SQL%NOTFOUND) THEN
149 RAISE NO_DATA_FOUND;
150 END IF;
151
152 END update_row_jtf_perz_profile;
153
154 -- ****************************************************************************
155 PROCEDURE insert_row_profile_attrib(
156 X_ROWID OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
157 X_PROFILE_ATTRIB_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
158 x_PROFILE_ID IN NUMBER,
159 x_PROFILE_ATTRIBUTE IN VARCHAR2,
160 x_ATTRIBUTE_TYPE IN VARCHAR2,
161 x_ATTRIBUTE_VALUE IN VARCHAR2
162 ) IS
163
164 CURSOR C IS SELECT rowid FROM jtf_perz_profile_attrib
165 WHERE PROFILE_ID = x_PROFILE_ID AND ROWNUM = 1;
166 CURSOR C2 IS SELECT jtf_perz_profile_attrib_s.NEXTVAL FROM SYS.DUAL;
167
168 BEGIN
169 IF (X_PROFILE_ATTRIB_ID IS NULL) OR
170 (X_PROFILE_ATTRIB_ID = FND_API.G_MISS_NUM) THEN
171 OPEN C2;
172 FETCH C2 INTO X_PROFILE_ATTRIB_ID;
173 CLOSE C2;
174 END IF;
175
176 INSERT INTO jtf_perz_profile_attrib(
177 PROFILE_ATTRIB_ID,
178 PROFILE_ID,
179 PROFILE_ATTRIBUTE,
180 ATTRIBUTE_TYPE,
181 ATTRIBUTE_VALUE,
182 CREATED_BY,
183 LAST_UPDATE_DATE,
184 LAST_UPDATED_BY,
185 LAST_UPDATE_LOGIN)
186 VALUES (
187 X_PROFILE_ATTRIB_ID,
188 x_PROFILE_ID,
189 decode (x_PROFILE_ATTRIBUTE, FND_API.G_MISS_CHAR, NULL, x_PROFILE_ATTRIBUTE ),
190 decode (x_ATTRIBUTE_TYPE, FND_API.G_MISS_CHAR, NULL ,x_ATTRIBUTE_TYPE),
191 decode (x_ATTRIBUTE_VALUE, FND_API.G_MISS_CHAR, NULL ,x_ATTRIBUTE_VALUE),
192 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID );
193
194 OPEN C;
195 FETCH C INTO x_Rowid;
196 IF (C%NOTFOUND) THEN
197 CLOSE C;
198 RAISE NO_DATA_FOUND;
199 END IF;
200
201 END insert_row_profile_attrib;
202
203 -- ****************************************************************************
204 PROCEDURE update_row_profile_attrib(
205 X_ROWID OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
206 x_PROFILE_ATTRIB_ID IN NUMBER,
207 x_PROFILE_ID IN NUMBER,
208 x_PROFILE_ATTRIBUTE IN VARCHAR2,
209 x_ATTRIBUTE_TYPE IN VARCHAR2,
210 x_ATTRIBUTE_VALUE IN VARCHAR2
211 ) IS
212
213 BEGIN
214
215 UPDATE jtf_perz_profile_attrib SET
216 ATTRIBUTE_TYPE = decode( x_ATTRIBUTE_TYPE, FND_API.G_MISS_CHAR,
217 ATTRIBUTE_TYPE, x_ATTRIBUTE_TYPE ),
218 ATTRIBUTE_VALUE = decode( x_ATTRIBUTE_VALUE, FND_API.G_MISS_CHAR,
219 ATTRIBUTE_VALUE, x_ATTRIBUTE_VALUE ),
220 LAST_UPDATE_DATE = SYSDATE,
221 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
222 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
223 WHERE profile_id = x_profile_id and
224 PROFILE_ATTRIBUTE = x_PROFILE_ATTRIBUTE;
225
226 IF (SQL%NOTFOUND) THEN
227 RAISE NO_DATA_FOUND;
228 END IF;
229
230 END update_row_profile_attrib;
231
232 -- ****************************************************************************
233 -- ****************************************************************************
234
235 -- PROCEDURE check_profile_duplicates()
236 --******************************************************************************
237 -- ****************************************************************************
238
239 PROCEDURE check_profile_duplicates(
240 p_profile_name IN VARCHAR2,
241 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
242 x_profile_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER
243
244 )
245 IS
246 l_temp_id NUMBER;
247
248 BEGIN
249
250
251 IF ((p_profile_name IS NOT NULL) AND (x_profile_id IS NULL)) THEN
252
253 -- dbms_output.put_line( 'chk profile '||p_profile_name);
254 SELECT profile_id INTO x_profile_id
255 FROM jtf_perz_profile
256 WHERE profile_name = p_profile_name;
257
258 if (x_profile_id IS NOT NULL) then
259 x_return_status := FND_API.G_TRUE;
260 else
261 x_return_status := FND_API.G_FALSE;
262 end if;
263
264 ELSIF ((p_profile_name IS NULL) AND (x_profile_id IS NOT NULL)) THEN
265
266 select profile_id INTO l_temp_id
267 from jtf_perz_profile
268 where profile_id = x_profile_id;
269
270 if (l_temp_id IS NOT NULL) then
271 x_return_status := FND_API.G_TRUE;
272 else
273 x_return_status := FND_API.G_FALSE;
274 end if;
275
276 ELSIF ((p_profile_name IS NOT NULL) AND (x_profile_id IS NOT NULL)) THEN
277
278 SELECT profile_id INTO l_temp_id
279 FROM jtf_perz_profile
280 WHERE profile_name = p_profile_name
281 and profile_id = x_profile_id;
282
283 if (l_temp_id IS NOT NULL) then
284 x_return_status := FND_API.G_TRUE;
285 else
286 x_return_status := FND_API.G_FALSE;
287 end if;
288
289 else
290 x_return_status := FND_API.G_FALSE;
291 end if;
292
293 -- dbms_output.put_line( 'chk profile rtn status '||x_return_status);
294 EXCEPTION
295
296 WHEN NO_DATA_FOUND THEN
297 x_return_status := FND_API.G_FALSE;
298 when OTHERS then
299 RAISE FND_API.G_EXC_ERROR;
300 END check_profile_duplicates;
301
302 -- ****************************************************************************
303 -- ****************************************************************************
304 -- DYNAMIC SQL PROCEDURES
305 -- ****************************************************************************
306
307
308 -- This precedure defines the columns for the D-SQL query
309 -- The columns are essentially a mapping of table columns from
310 -- the query to the output record set.
311
312 PROCEDURE Define_Columns_Profile(
313 p_profile_attrib_rec IN JTF_PERZ_PROFILE_PUB.PROFILE_OUT_REC_TYPE,
314 p_cur_profile IN NUMBER ) IS
315 BEGIN
316
317 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
318 THEN
319 -- dbms_output.put_line('JTF_PERZ_PROFILE_PVT Define columns');
320 null;
321 END IF;
322
323 dbms_sql.define_column(p_cur_profile, 1, p_profile_attrib_rec.PROFILE_ID);
324 dbms_sql.define_column(p_cur_profile, 2, p_profile_attrib_rec.PROFILE_NAME, 60);
325 dbms_sql.define_column(p_cur_profile, 3, p_profile_attrib_rec.PROFILE_TYPE, 30);
326 dbms_sql.define_column(p_cur_profile, 4, p_profile_attrib_rec.PROFILE_DESCRIPTION, 240);
327 dbms_sql.define_column(p_cur_profile, 5, p_profile_attrib_rec.ACTIVE_FLAG, 1);
328 dbms_sql.define_column(p_cur_profile, 6, p_profile_attrib_rec.PROFILE_ATTRIBUTE, 100);
329 dbms_sql.define_column(p_cur_profile, 7, p_profile_attrib_rec.ATTRIBUTE_TYPE, 100);
330 dbms_sql.define_column(p_cur_profile, 8, p_profile_attrib_rec.ATTRIBUTE_VALUE, 100);
331
332 END Define_Columns_Profile;
333 -- ****************************************************************************
334 -- This procedure defines the return columns for the D-SQL
335
336 PROCEDURE Get_Columns_Profile(p_cur_profile IN NUMBER,
337 p_profile_attrib_rec OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_PROFILE_PUB.PROFILE_OUT_REC_TYPE ) IS
338 BEGIN
339 --dbms_output.put_line('JTF_PERZ_PROFILE_PVT Get column values');
340
341 dbms_sql.column_value(p_cur_profile, 1, p_profile_attrib_rec.PROFILE_ID);
342 dbms_sql.column_value(p_cur_profile, 2, p_profile_attrib_rec.PROFILE_NAME);
343 dbms_sql.column_value(p_cur_profile, 3, p_profile_attrib_rec.PROFILE_TYPE);
344 dbms_sql.column_value(p_cur_profile, 4, p_profile_attrib_rec.PROFILE_DESCRIPTION);
345 dbms_sql.column_value(p_cur_profile, 5, p_profile_attrib_rec.ACTIVE_FLAG);
346 dbms_sql.column_value(p_cur_profile, 6, p_profile_attrib_rec.PROFILE_ATTRIBUTE);
347 dbms_sql.column_value(p_cur_profile, 7, p_profile_attrib_rec.ATTRIBUTE_TYPE);
348 dbms_sql.column_value(p_cur_profile, 8, p_profile_attrib_rec.ATTRIBUTE_VALUE);
349
350 END Get_Columns_Profile;
351 -- ****************************************************************************
352
353 -- This procedure will bind the variables for the dynamic SQL query
354
355 PROCEDURE Bind_Variables_Profile(
356 p_cur_profile IN NUMBER,
357 p_profile_attrib_tbl IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE,
358 p_profile_name IN VARCHAR2,
359 p_profile_type IN VARCHAR2,
360 p_profile_id IN NUMBER
361 ) IS
362
363 BEGIN
364
365 -- Bind variables
366 -- Only those that are not NULL
367
368 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
369 THEN
370 --dbms_output.put_line('JTF_PERZ_PROFILE_PVT Bind variables');
371 null;
372 END IF;
373
374 If (p_profile_name IS NOT NULL) Then
375 dbms_sql.bind_variable(p_cur_profile, 'p_profile_name', p_profile_name);
376 End if;
377
378 If (p_profile_id IS NOT NULL) Then
379 dbms_sql.bind_variable(p_cur_profile, 'p_profile_id', p_profile_id);
380 End if;
381
382 If (p_profile_type IS NOT NULL) Then
383 dbms_sql.bind_variable(p_cur_profile, 'p_profile_type', p_profile_type);
384 End if;
385
386 -- going beyond this only if more than one row
387 IF (p_profile_attrib_tbl.count > 0) THEN
388
389 If (p_profile_attrib_tbl(1).profile_attribute IS NOT NULL) Then
390 dbms_sql.bind_variable(p_cur_profile, 'p_profile_attribute', p_profile_attrib_tbl(1).profile_attribute);
391 End if;
392
393 If (p_profile_attrib_tbl(1).attribute_type IS NOT NULL) Then
394 dbms_sql.bind_variable(p_cur_profile, 'p_attribute_type', p_profile_attrib_tbl(1).attribute_type);
395 End if;
396
397 If (p_profile_attrib_tbl(1).attribute_value IS NOT NULL) Then
398 dbms_sql.bind_variable(p_cur_profile, 'p_attribute_value', p_profile_attrib_tbl(1).attribute_value);
399 End if;
400
401 END IF;
402
403 END Bind_Variables_Profile;
404 -- ****************************************************************************
405
406
407 -- This procedure generates the WHERE clause for Get_Activities
408
409 PROCEDURE Gen_Where_Profile(
410 p_profile_attrib_tbl IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE,
411 p_profile_name IN VARCHAR2,
412 p_profile_type IN VARCHAR2,
413 p_profile_id IN NUMBER,
414 x_head_where OUT NOCOPY /* file.sql.39 change */ VARCHAR2
415 ) IS
416
417 BEGIN
418
419 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
420 THEN
421 --dbms_output.put_line('JTF_PERZ_PROFILE_PVT Generate Where');
422 null;
423 END IF;
424
425
426 if (x_head_where is NULL) Then
427 x_head_where := 'WHERE prfl.profile_id = prfl_attrib.profile_id ';
428 else
429 x_head_where := x_head_where || 'AND prfl.profile_id = prfl_attrib.profile_id ';
430 End if;
431
432 If (p_profile_name IS NOT NULL) Then
433 if (x_head_where is NULL) Then
434 x_head_where := 'WHERE ';
435 else
436 x_head_where := x_head_where || 'AND ';
437 End if;
438 x_head_where := x_head_where || 'prfl.profile_name=:p_profile_name ';
439 End if;
440
441 If (p_profile_type IS NOT NULL) Then
442 if (x_head_where is NULL) Then
443 x_head_where := 'WHERE ';
444 else
445 x_head_where := x_head_where || 'AND ';
446 End if;
447 x_head_where := x_head_where || 'prfl.profile_type=:p_profile_type ';
448 End if;
449
450 If (p_profile_id IS NOT NULL) Then
451 if (x_head_where is NULL) Then
452 x_head_where := 'Where ';
453 else
454 x_head_where := x_head_where || 'And ';
455 End if;
456 x_head_where := x_head_where || 'prfl.profile_id=:p_profile_id ';
457 End if;
458
459 -- go beyond this only if table count > 0
460 IF (p_profile_attrib_tbl.count > 0) THEN
461
462 If ((p_profile_attrib_tbl(1).profile_attribute) IS NOT NULL) Then
463 if (x_head_where is NULL) Then
464 x_head_where := 'WHERE ';
465 else
466 x_head_where := x_head_where || 'AND ';
467 End if;
468 x_head_where := x_head_where || 'prfl_attrib.profile_attribute=:p_profile_attribute ';
469 End if;
470
471 If ((p_profile_attrib_tbl(1).attribute_type) IS NOT NULL ) Then
472 if (x_head_where is NULL) Then
473 x_head_where := 'WHERE ';
474 else
475 x_head_where := x_head_where || 'AND ';
476 End if;
477 x_head_where := x_head_where || 'prfl_attrib.attribute_type=:p_attribute_type ';
478 End if;
479
480 If ((p_profile_attrib_tbl(1).attribute_value) IS NOT NULL) Then
481 if (x_head_where is NULL) Then
482 x_head_where := 'WHERE ';
483 else
484 x_head_where := x_head_where || 'AND ';
485 End if;
486 x_head_where := x_head_where || 'prfl_attrib.attribute_value=:p_attribute_value ';
487
488 End if;
489
490 END IF;
491 --dbms_output.put_line('where in the end ' || x_head_where);
492
493 END Gen_Where_Profile;
494 -- ****************************************************************************
495
496 -- This procedure generate the Select and From clause for the profile store
497
498 PROCEDURE Gen_Select_Profile( x_select_cl OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ) IS
499
500 BEGIN
501
502 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
503 THEN
504 --dbms_output.put_line('JTF_PERZ_PVT GENERATE SELECT');
505 null;
506 END IF;
507
508 x_select_cl := 'Select ' ||
509 'prfl.PROFILE_ID,' ||
510 'prfl.PROFILE_NAME,' ||
511 'prfl.PROFILE_TYPE,' ||
512 'prfl.ACTIVE_FLAG,' ||
513 'prfl.PROFILE_DESCRIPTION,' ||
514 'prfl_attrib.PROFILE_ATTRIBUTE,' ||
515 'prfl_attrib.ATTRIBUTE_TYPE,' ||
516 'prfl_attrib.ATTRIBUTE_VALUE ' ||
517
518 ' from JTF_PERZ_PROFILE prfl, jtf_perz_profile_attrib prfl_attrib ';
519
520 --dbms_output.put_line(x_select_cl);
521
522 END Gen_Select_Profile;
523 -- ***************************************************************************
524 -- ****************************************************************************
525 -- Public APIs
526 -- Create Profile
527 -- Update Profile
528 -- Get Profile
529 -- ****************************************************************************
530 -- ****************************************************************************
531 PROCEDURE Create_Profile
532 ( p_api_version_number IN NUMBER,
533 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
534 p_commit IN VARCHAR2 := FND_API.G_FALSE,
535
536 p_profile_id IN NUMBER,
537 p_profile_name IN VARCHAR2 := NULL,
538 p_profile_type IN VARCHAR2 := NULL,
539 p_profile_desc IN VARCHAR2 := NULL,
540 p_profile_attrib_tbl IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
541 := JTF_PERZ_PROFILE_PUB.G_MISS_PROFILE_ATTRIB_TBL,
542
543 x_profile_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
544 x_profile_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
545 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
546 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
547 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
548 )IS
549
550 l_profile_attrib_tbl JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
551 := p_profile_attrib_tbl;
552 l_any_errors BOOLEAN := FALSE;
553 l_any_row_errors BOOLEAN := FALSE;
554 l_rowid ROWID;
555 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
556 l_api_name CONSTANT VARCHAR2(30) := 'Create Profile';
557 l_count NUMBER := p_profile_attrib_tbl.count;
558 l_curr_row NUMBER := NULL;
559
560 l_duplicate VARCHAR2(240) := FND_API.G_FALSE;
561 l_profile_name VARCHAR2(60) := p_profile_name;
562
563 -- Variables for ids
564 l_active_flag VARCHAR2(1) := 'Y';
565 l_profile_id NUMBER := NULL;
566 l_profile_attrib_id NUMBER;
567 l_is_duplicate VARCHAR2(1);
568 l_object_version_number NUMBER := NULL;
569
570 BEGIN
571 -- ******* Standard Begins ********
572
573 -- Standard Start of API savepoint
574 SAVEPOINT CREATE_PERZ_PROFILE_PVT;
575
576 --dbms_output.put_line('creating save point ');
577 /*
578 -- Standard call to check for call compatibility.
579 IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
580 THEN
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END IF;
583 */
584 -- Initialize message list if p_init_msg_list is set to TRUE.
585 IF FND_API.to_Boolean( p_init_msg_list )
586 THEN
587 FND_MSG_PUB.initialize;
588 END IF;
589
590 -- Initialize API return status to success
591 x_return_status := FND_API.G_RET_STS_SUCCESS;
592
593
594 -- profiles execution steps :
595 -- 1. check if duplicate exists
596 -- 2. if no,
597 -- 3. insert row with proflie data into profile table
598 -- 4. pick id and cycle through attributes in profile attribute table
599 -- 5. insert records into profile attribute table.
600 -- 6. if profile already exists, return error !
601
602
603 -- 1. CHECK IF DUPLICATE EXISTS
604 -- 1.1. CHECK DUPLICATE PROFILE
605
606 check_profile_duplicates ( p_profile_name,
607 l_duplicate,
608 l_profile_id );
609
610 --dbms_output.put_line(' l_duplicate ' || l_duplicate);
611
612 -- 1.2. IF PROFILE ALREADY EXISTS, RETURN ERROR !
613
614 IF (FND_API.To_Boolean(l_duplicate)) THEN
615 -- x_return_status := FND_API.G_RET_STS_ERROR ;
616 RAISE FND_API.G_EXC_ERROR;
617 END IF;
618
619 -- 1.3. IF NOT, CHECK IF THERE ARE ANY DUPLICATE ENTRIES AT ATTRIBUTES LEVEL
620
621 -- 2. if no duplicates, then create a profile
622
623 -- 3. insert row with proflie data into profile table
624
625 --dbms_output.put_line('inserting into profile table ');
626
627 if ((p_profile_id is not null) and
628 (p_profile_id <> FND_API.G_MISS_NUM)) then
629 l_profile_id := p_profile_id;
630 end if;
631
632 l_object_version_number := 1;
633 insert_row_jtf_perz_profile(
634 l_rowid,
635 l_profile_id,
636 p_profile_name,
637 p_profile_type,
638 p_profile_desc,
639 l_active_flag,
640 l_object_version_number );
641
642 -- copying ID to output.
643
644 x_profile_id := l_profile_id;
645 x_profile_name := p_profile_name;
646
647 -- 5. insert records into profile attribute table
648
649 FOR l_curr_row in 1..l_count LOOP
650 l_rowid := NULL;
651 insert_row_profile_attrib(
652 l_rowid,
653 l_profile_attrib_tbl(l_curr_row).ATTRIBUTE_ID,
654 l_profile_id,
655 l_profile_attrib_tbl(l_curr_row).profile_attribute,
656 l_profile_attrib_tbl(l_curr_row).attribute_type,
657 l_profile_attrib_tbl(l_curr_row).attribute_value
658 );
659
660 END LOOP;
661
662 -- ******** Standard Ends ***********
663 --
664 -- End of main API body.
665
666 -- Standard check of p_commit.
667 IF (FND_API.To_Boolean(p_commit)) THEN
668 COMMIT WORK;
669 END IF;
670
671 -- Standard call to get message count and if count is 1, get message info.
672 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
673 p_data => x_msg_data );
674
675 EXCEPTION
676
677 WHEN FND_API.G_EXC_ERROR THEN
678 -- dbms_output.put_line('stop 1 ');
679
680 ROLLBACK TO CREATE_PERZ_PROFILE_PVT;
681 x_return_status := FND_API.G_RET_STS_ERROR ;
682
683 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
684 p_data => x_msg_data );
685
686 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
687 -- dbms_output.put_line('stop 2 ');
688 ROLLBACK TO CREATE_PERZ_PROFILE_PVT;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
690
691 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
692 p_data => x_msg_data );
693
694 WHEN OTHERS THEN
695 -- dbms_output.put_line('stop 3 ');
696 ROLLBACK TO CREATE_PERZ_PROFILE_PVT;
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
698
699 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
700 THEN
701 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
702 END IF;
703
704 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
705 p_data => x_msg_data );
706
707 END Create_Profile;
708
709 -- ****************************************************************************
710
711 PROCEDURE Get_Profile
712 ( p_api_version_number IN NUMBER,
713 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
714
715 p_profile_id IN NUMBER := NULL,
716 p_profile_name IN VARCHAR2 := NULL,
717 p_profile_type IN VARCHAR2 := NULL,
718 p_profile_attrib_tbl IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
719 := JTF_PERZ_PROFILE_PUB.G_MISS_PROFILE_ATTRIB_TBL,
720
721 x_profile_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_PROFILE_PUB.PROFILE_OUT_TBL_TYPE,
722 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
723 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
724 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
725 )IS
726
727 l_profile_attrib_tbl JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
728 := p_profile_attrib_tbl;
729 l_profile_out_tbl JTF_PERZ_PROFILE_PUB.PROFILE_OUT_TBL_TYPE;
730 l_profile_out_rec JTF_PERZ_PROFILE_PUB.PROFILE_OUT_REC_TYPE;
731
732 l_any_errors BOOLEAN := FALSE;
733 l_any_row_errors BOOLEAN := FALSE;
734 l_rowid ROWID;
735 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
736 l_api_name CONSTANT VARCHAR2(30) := 'Get Profile';
737
738 l_count NUMBER := p_profile_attrib_tbl.count;
739 l_curr_row NUMBER := 0;
740 l_cur_profile_attrib NUMBER := NULL;
741
742 -- Variables for ids
743 l_active_flag VARCHAR2(1) := 'Y';
744 l_key VARCHAR2(50);
745 l_profile_id NUMBER;
746 l_profile_attrib_id NUMBER;
747 l_is_duplicate VARCHAR2(1);
748 l_ignore NUMBER;
749 i NUMBER := 1;
750 l_select_clause VARCHAR2(2000) := '';
751 l_head_where VARCHAR2(2000) := NULL;
752 l_returned_rec_count NUMBER := 0;
753
754 -- l_profile_attrib VARCHAR2(60) := p_profile_attrib_tbl(1).PROFILE_ATTRIBUTE;
755 -- l_attribute_type VARCHAR2(60) := p_profile_attrib_tbl(1).ATTRIBUTE_TYPE;
756
757 cursor prof1_curs (l_profname varchar2) is
758 SELECT a.PROFILE_ID, a.PROFILE_NAME,
759 a.PROFILE_TYPE, a.PROFILE_DESCRIPTION, a.ACTIVE_FLAG,
760 b.PROFILE_ATTRIBUTE, b.ATTRIBUTE_TYPE, b.ATTRIBUTE_VALUE
761 FROM JTF_PERZ_PROFILE a, JTF_PERZ_PROFILE_ATTRIB b
762 WHERE a.profile_id = b.profile_id AND a.profile_name = l_profname ;
763
764 cursor profid1_curs (l_profid number) is
765 SELECT a.PROFILE_ID, a.PROFILE_NAME,
766 a.PROFILE_TYPE, a.PROFILE_DESCRIPTION, a.ACTIVE_FLAG,
767 b.PROFILE_ATTRIBUTE, b.ATTRIBUTE_TYPE, b.ATTRIBUTE_VALUE
768 FROM JTF_PERZ_PROFILE a, JTF_PERZ_PROFILE_ATTRIB b
769 WHERE a.profile_id = b.profile_id AND a.profile_id = l_profid ;
770
771 cursor proftype1_curs (l_proftype varchar2) is
772 SELECT a.PROFILE_ID, a.PROFILE_NAME,
773 a.PROFILE_TYPE, a.PROFILE_DESCRIPTION, a.ACTIVE_FLAG,
774 b.PROFILE_ATTRIBUTE, b.ATTRIBUTE_TYPE, b.ATTRIBUTE_VALUE
775 FROM JTF_PERZ_PROFILE a, JTF_PERZ_PROFILE_ATTRIB b
776 WHERE a.profile_id = b.profile_id AND profile_type = l_proftype ;
777
778 cursor proftype2_curs (l_proftype varchar2, l_profile_attrib varchar2) is
779 SELECT a.PROFILE_ID, a.PROFILE_NAME,
780 a.PROFILE_TYPE, a.PROFILE_DESCRIPTION, a.ACTIVE_FLAG,
781 b.PROFILE_ATTRIBUTE, b.ATTRIBUTE_TYPE, b.ATTRIBUTE_VALUE
782 FROM JTF_PERZ_PROFILE a, JTF_PERZ_PROFILE_ATTRIB b
783 WHERE a.profile_id = b.profile_id
784 AND a.profile_type = l_proftype
785 AND b.PROFILE_ATTRIBUTE = l_profile_attrib ;
786
787 cursor proftype3_curs (l_proftype varchar2, l_attrib_type varchar2) is
788 SELECT a.PROFILE_ID, a.PROFILE_NAME,
789 a.PROFILE_TYPE, a.PROFILE_DESCRIPTION, a.ACTIVE_FLAG,
790 b.PROFILE_ATTRIBUTE, b.ATTRIBUTE_TYPE, b.ATTRIBUTE_VALUE
791 FROM JTF_PERZ_PROFILE a, JTF_PERZ_PROFILE_ATTRIB b
792 WHERE a.profile_id = b.profile_id
793 AND a.profile_type = l_proftype
794 AND b.ATTRIBUTE_TYPE = l_attrib_type ;
795
796 cursor proftype4_curs (l_proftype varchar2, l_profile_attrib varchar2, l_attrib_type varchar2) is
797 SELECT a.PROFILE_ID, a.PROFILE_NAME,
798 a.PROFILE_TYPE, a.PROFILE_DESCRIPTION, a.ACTIVE_FLAG,
799 b.PROFILE_ATTRIBUTE, b.ATTRIBUTE_TYPE, b.ATTRIBUTE_VALUE
800 FROM JTF_PERZ_PROFILE a, JTF_PERZ_PROFILE_ATTRIB b
801 WHERE a.profile_id = b.profile_id
802 AND a.profile_type = l_proftype
803 AND b.ATTRIBUTE_TYPE = l_attrib_type
804 AND b.PROFILE_ATTRIBUTE = l_profile_attrib;
805
806 cursor profid1attr_curs (l_profid number) is
807 SELECT PROFILE_ATTRIBUTE,ATTRIBUTE_TYPE,ATTRIBUTE_VALUE
808 FROM JTF_PERZ_PROFILE_ATTRIB
809 WHERE profile_id = l_profid;
810
811 curso_1prof prof1_curs%ROWTYPE;
812 curso_1profid profid1_curs%ROWTYPE;
813 curso_1proftype proftype1_curs%ROWTYPE;
814 curso_2proftype proftype2_curs%ROWTYPE;
815 curso_3proftype proftype3_curs%ROWTYPE;
816 curso_4proftype proftype4_curs%ROWTYPE;
817 curso_1profattr profid1attr_curs%ROWTYPE;
818
819 BEGIN
820 -- ******* Standard Begins ********
821
822 -- Initialize message list if p_init_msg_list is set to TRUE.
823 IF FND_API.to_Boolean( p_init_msg_list )
824 THEN
825 FND_MSG_PUB.initialize;
826 END IF;
827
828 -- Initialize API return status to success
829 l_return_status := FND_API.G_RET_STS_SUCCESS;
830
831
832 if (p_profile_id IS NOT NULL) then
833
834 for curso_1profid in profid1_curs(p_profile_id) loop
835 x_profile_tbl(i).Profile_ID := curso_1profid.PROFILE_ID ;
836 x_profile_tbl(i).PROFILE_NAME:= curso_1profid.PROFILE_NAME;
837 x_profile_tbl(i).PROFILE_TYPE:= curso_1profid.PROFILE_TYPE;
838 x_profile_tbl(i).PROFILE_DESCRIPTION:= curso_1profid.PROFILE_DESCRIPTION;
839 x_profile_tbl(i).ACTIVE_FLAG := curso_1profid.ACTIVE_FLAG;
840 x_profile_tbl(i).PROFILE_ATTRIBUTE := curso_1profid.PROFILE_ATTRIBUTE ;
841 x_profile_tbl(i).ATTRIBUTE_TYPE:= curso_1profid.ATTRIBUTE_TYPE;
842 x_profile_tbl(i).ATTRIBUTE_VALUE:= curso_1profid.ATTRIBUTE_VALUE;
843 i := i+1;
844 end loop;
845 x_return_status := FND_API.G_RET_STS_SUCCESS;
846 return;
847
848 elsif (p_profile_name IS NOT NULL) then
849
850 for curso_1prof in prof1_curs(p_profile_name) loop
851 x_profile_tbl(i).Profile_ID := curso_1prof.PROFILE_ID ;
852 x_profile_tbl(i).PROFILE_NAME:= curso_1prof.PROFILE_NAME;
853 x_profile_tbl(i).PROFILE_TYPE:= curso_1prof.PROFILE_TYPE;
854 x_profile_tbl(i).PROFILE_DESCRIPTION:= curso_1prof.PROFILE_DESCRIPTION;
855 x_profile_tbl(i).ACTIVE_FLAG := curso_1prof.ACTIVE_FLAG;
856 x_profile_tbl(i).PROFILE_ATTRIBUTE := curso_1prof.PROFILE_ATTRIBUTE ;
857 x_profile_tbl(i).ATTRIBUTE_TYPE:= curso_1prof.ATTRIBUTE_TYPE;
858 x_profile_tbl(i).ATTRIBUTE_VALUE:= curso_1prof.ATTRIBUTE_VALUE;
859 i := i+1;
860 end loop;
861 x_return_status := FND_API.G_RET_STS_SUCCESS;
862 return;
863
864 elsif ((p_profile_type IS NOT NULL) AND
865
866 ((p_profile_attrib_tbl(1).PROFILE_ATTRIBUTE IS NOT NULL) AND
867 (p_profile_attrib_tbl(1).PROFILE_ATTRIBUTE <> FND_API.G_MISS_CHAR)) AND
868
869 ((p_profile_attrib_tbl(1).ATTRIBUTE_TYPE IS NOT NULL) AND
870 (p_profile_attrib_tbl(1).ATTRIBUTE_TYPE <> FND_API.G_MISS_CHAR))) then
871
872 for curso_4proftype in proftype4_curs(p_profile_type, p_profile_attrib_tbl(1).PROFILE_ATTRIBUTE,
873 p_profile_attrib_tbl(1).ATTRIBUTE_TYPE) loop
874 x_profile_tbl(i).Profile_ID := curso_4proftype.PROFILE_ID ;
875 x_profile_tbl(i).PROFILE_NAME:= curso_4proftype.PROFILE_NAME;
876 x_profile_tbl(i).PROFILE_TYPE:= curso_4proftype.PROFILE_TYPE;
877 x_profile_tbl(i).PROFILE_DESCRIPTION:= curso_4proftype.PROFILE_DESCRIPTION;
878 x_profile_tbl(i).ACTIVE_FLAG := curso_4proftype.ACTIVE_FLAG;
879 x_profile_tbl(i).PROFILE_ATTRIBUTE := curso_4proftype.PROFILE_ATTRIBUTE ;
880 x_profile_tbl(i).ATTRIBUTE_TYPE:= curso_4proftype.ATTRIBUTE_TYPE;
881 x_profile_tbl(i).ATTRIBUTE_VALUE:= curso_4proftype.ATTRIBUTE_VALUE;
882 i := i+1;
883 end loop;
884
885 x_return_status := FND_API.G_RET_STS_SUCCESS;
886 return;
887
888 elsif ((p_profile_type IS NOT NULL) AND
889 (p_profile_attrib_tbl(1).PROFILE_ATTRIBUTE IS NOT NULL) AND
890 (p_profile_attrib_tbl(1).PROFILE_ATTRIBUTE <> FND_API.G_MISS_CHAR)) then
891
892 for curso_2proftype in proftype2_curs(p_profile_type, p_profile_attrib_tbl(1).PROFILE_ATTRIBUTE) loop
893 x_profile_tbl(i).Profile_ID := curso_2proftype.PROFILE_ID ;
894 x_profile_tbl(i).PROFILE_NAME:= curso_2proftype.PROFILE_NAME;
895 x_profile_tbl(i).PROFILE_TYPE:= curso_2proftype.PROFILE_TYPE;
896 x_profile_tbl(i).PROFILE_DESCRIPTION:= curso_2proftype.PROFILE_DESCRIPTION;
897 x_profile_tbl(i).ACTIVE_FLAG := curso_2proftype.ACTIVE_FLAG;
898 x_profile_tbl(i).PROFILE_ATTRIBUTE := curso_2proftype.PROFILE_ATTRIBUTE ;
899 x_profile_tbl(i).ATTRIBUTE_TYPE:= curso_2proftype.ATTRIBUTE_TYPE;
900 x_profile_tbl(i).ATTRIBUTE_VALUE:= curso_2proftype.ATTRIBUTE_VALUE;
901 i := i+1;
902 end loop;
903 x_return_status := FND_API.G_RET_STS_SUCCESS;
904 return;
905
906 elsif ((p_profile_type IS NOT NULL) AND
907 (p_profile_attrib_tbl(1).ATTRIBUTE_TYPE IS NOT NULL) AND
908 (p_profile_attrib_tbl(1).ATTRIBUTE_TYPE <> FND_API.G_MISS_CHAR)) then
909
910 for curso_3proftype in proftype3_curs(p_profile_type, p_profile_attrib_tbl(1).ATTRIBUTE_TYPE) loop
911 x_profile_tbl(i).Profile_ID := curso_3proftype.PROFILE_ID ;
912 x_profile_tbl(i).PROFILE_NAME:= curso_3proftype.PROFILE_NAME;
913 x_profile_tbl(i).PROFILE_TYPE:= curso_3proftype.PROFILE_TYPE;
914 x_profile_tbl(i).PROFILE_DESCRIPTION:= curso_3proftype.PROFILE_DESCRIPTION;
915 x_profile_tbl(i).ACTIVE_FLAG := curso_3proftype.ACTIVE_FLAG;
916 x_profile_tbl(i).PROFILE_ATTRIBUTE := curso_3proftype.PROFILE_ATTRIBUTE ;
917 x_profile_tbl(i).ATTRIBUTE_TYPE:= curso_3proftype.ATTRIBUTE_TYPE;
918 x_profile_tbl(i).ATTRIBUTE_VALUE:= curso_3proftype.ATTRIBUTE_VALUE;
919 i := i+1;
920 end loop;
921 x_return_status := FND_API.G_RET_STS_SUCCESS;
922 return;
923
924 else
925 for curso_1proftype in proftype1_curs(p_profile_type) loop
926 x_profile_tbl(i).Profile_ID := curso_1proftype.PROFILE_ID ;
927 x_profile_tbl(i).PROFILE_NAME:= curso_1proftype.PROFILE_NAME;
928 x_profile_tbl(i).PROFILE_TYPE:= curso_1proftype.PROFILE_TYPE;
929 x_profile_tbl(i).PROFILE_DESCRIPTION:= curso_1proftype.PROFILE_DESCRIPTION;
930 x_profile_tbl(i).ACTIVE_FLAG := curso_1proftype.ACTIVE_FLAG;
931 x_profile_tbl(i).PROFILE_ATTRIBUTE := curso_1proftype.PROFILE_ATTRIBUTE ;
932 x_profile_tbl(i).ATTRIBUTE_TYPE:= curso_1proftype.ATTRIBUTE_TYPE;
933 x_profile_tbl(i).ATTRIBUTE_VALUE:= curso_1proftype.ATTRIBUTE_VALUE;
934 i := i+1;
935 end loop;
936 x_return_status := FND_API.G_RET_STS_SUCCESS;
937
938 end if;
939
940
941 -- End of API body.
942
943
944 -- Success Message
945
946 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
947 THEN
948 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
949 FND_MESSAGE.Set_Token('ROW', 'personalize', TRUE);
950 FND_MSG_PUB.Add;
951 END IF;
952
953 -- Debug Message
954 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
955 THEN
956 --dbms_output.put_line('personalize');
957 null;
958 END IF;
959
960 -- Standard call to get message count and if count is 1, get message info.
961 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
962 p_data => x_msg_data );
963
964 EXCEPTION
965
966 WHEN FND_API.G_EXC_ERROR THEN
967 --dbms_output.put_line('stop 1 ');
968
969 --ROLLBACK TO CREATE_PERZ_PROFILE_PVT;
970 x_return_status := FND_API.G_RET_STS_ERROR ;
971
972 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
973 p_data => x_msg_data );
974
975 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
976 --dbms_output.put_line('stop 2 ');
977 -- ROLLBACK TO CREATE_PERZ_PROFILE_PVT;
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
979
980 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
981 p_data => x_msg_data );
982
983 WHEN OTHERS THEN
984 --dbms_output.put_line('stop 3 ');
985 --ROLLBACK TO CREATE_PERZ_PROFILE_PVT;
986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
987
988 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
989 THEN
990 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
991 END IF;
992
993 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
994 p_data => x_msg_data );
995
996
997 END Get_Profile;
998 -- ****************************************************************************
999
1000 PROCEDURE Update_Profile
1001 ( p_api_version_number IN NUMBER,
1002 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1003 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1004 p_profile_id IN NUMBER,
1005 p_profile_name IN VARCHAR2,
1006 p_profile_type IN VARCHAR2 := NULL,
1007 p_profile_desc IN VARCHAR2 ,
1008 p_active_flag IN VARCHAR2,
1009 p_Profile_ATTRIB_Tbl IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE,
1010 x_profile_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1011 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1012 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1013 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1014 ) IS
1015 -- problem is, this API will not check the validity of the p_active_flag
1016
1017 l_profile_attrib_tbl JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE;
1018 l_attributes_from_database JTF_PERZ_PROFILE_PUB.PROFILE_OUT_TBL_TYPE;
1019
1020 l_api_version NUMBER := 1.0;
1021 l_init_msg_list VARCHAR2(240):= FND_API.G_TRUE;
1022 l_msg_count NUMBER;
1023 l_msg_data VARCHAR2(2000);
1024 l_any_errors BOOLEAN := FALSE;
1025 l_any_row_errors BOOLEAN := FALSE;
1026 l_rowid ROWID;
1027 l_return_status VARCHAR2(240) := FND_API.G_RET_STS_SUCCESS;
1028 l_api_name CONSTANT VARCHAR2(30) := 'Update Profile';
1029 l_count NUMBER := p_profile_attrib_tbl.count;
1030 l_count_1 NUMBER := NULL;
1031 l_curr_row NUMBER := NULL;
1032 l_curr_row_1 NUMBER := NULL;
1033 l_duplicate VARCHAR2(240) := FND_API.G_FALSE;
1034 l_profile_name VARCHAR2(100) := NULL;
1035 l_profile_desc VARCHAR2(240) := FND_API.G_MISS_CHAR;
1036 l_active_flag VARCHAR2(1) := 'Y';
1037 l_is_duplicate VARCHAR2(1);
1038 l_found_flag BOOLEAN := FALSE;
1039 l_profile_type VARCHAR2(30);
1040
1041 -- Variables for ids
1042 l_profile_id NUMBER := NULL;
1043 l_profile_attrib_id NUMBER;
1044 l_object_version_number NUMBER := NULL;
1045 l_attrib_obj_version_no NUMBER := NULL;
1046
1047 BEGIN
1048 -- ******* Standard Begins ********
1049 -- Standard Start of API savepoint
1050 SAVEPOINT UPDATE_PERZ_PROFILE_PVT;
1051 /*
1052 -- Standard call to check for call compatibility.
1053 IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
1054 THEN
1055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1056 END IF;
1057 */
1058 -- Initialize message list if p_init_msg_list is set to TRUE.
1059 IF FND_API.to_Boolean( p_init_msg_list )
1060 THEN
1061 FND_MSG_PUB.initialize;
1062 END IF;
1063
1064 -- Initialize API return status to success
1065 x_return_status := FND_API.G_RET_STS_SUCCESS;
1066
1067 if (p_profile_id is not null) AND
1068 (p_profile_id <> FND_API.G_MISS_NUM) then
1069 l_profile_id := p_profile_id;
1070 else
1071 l_profile_id := NULL;
1072 end if;
1073 if (p_profile_name is not null) AND
1074 (p_profile_name <> FND_API.G_MISS_CHAR) then
1075 l_profile_name := p_profile_name;
1076 else
1077 l_profile_name := NULL;
1078 end if;
1079
1080 if (l_profile_id is null and l_profile_name is null) then
1081 raise FND_API.G_EXC_ERROR;
1082 end if;
1083 l_profile_type := NULL;
1084
1085 Get_Profile(
1086 l_api_version,
1087 l_init_msg_list,
1088 l_profile_id,
1089 l_profile_name,
1090 l_profile_type,
1091 l_profile_attrib_tbl,
1092 l_attributes_from_database,
1093 l_return_status,
1094 x_msg_count,
1095 x_msg_data );
1096
1097 l_profile_attrib_tbl := p_Profile_ATTRIB_Tbl;
1098 l_profile_id := l_attributes_from_database(1).PROFILE_ID;
1099 l_profile_name := l_attributes_from_database(1).profile_name;
1100 l_count_1 := l_attributes_from_database.count;
1101
1102 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1103 l_count := l_profile_attrib_tbl.count;
1104
1105 if (( p_profile_type is null) OR
1106 (p_profile_type = FND_API.G_MISS_CHAR)) then
1107 l_profile_type := FND_API.G_MISS_CHAR;
1108 else
1109 l_profile_type := p_profile_type;
1110 end if;
1111
1112
1113 if (( p_profile_desc is null) OR
1114 (p_profile_desc = FND_API.G_MISS_CHAR)) then
1115 l_profile_desc := FND_API.G_MISS_CHAR;
1116 else
1117 l_profile_desc := p_profile_desc;
1118 end if;
1119
1120 -- Select the object_version_number that corresponds to the
1121 -- profile object. This must be done separately as object_version_number
1122 -- is not part of the get_profile API.
1123
1124 select object_version_number into l_object_version_number
1125 from jtf_perz_profile where profile_id = l_profile_id;
1126
1127 -- In the following call, p_active_flag must be replaced with l_active_flag
1128 -- and l_active_flag must be initialized like l_profile_type above.
1129 -- for some reason, I am getting ORA 6502 when I trie to do that.
1130 -- This must be fixed in future - srikanth
1131
1132 update_row_jtf_perz_profile(
1133 l_rowid,
1134 l_profile_id,
1135 l_profile_name,
1136 l_profile_type,
1137 l_profile_desc,
1138 p_active_flag,
1139 l_object_version_number);
1140
1141 FOR l_curr_row in 1..l_count LOOP
1142
1143 l_rowid := NULL;
1144 l_found_flag := FALSE;
1145
1146 <<l_inner_loop>>
1147 FOR l_curr_row_1 in 1..l_count_1 LOOP
1148 IF (l_profile_attrib_tbl(l_curr_row).PROFILE_ATTRIBUTE =
1149 l_attributes_from_database(l_curr_row_1).PROFILE_ATTRIBUTE)
1150 THEN
1151 --UPDATE row with new attribute values.
1152 update_row_profile_attrib(
1153 l_rowid,
1154 l_profile_attrib_tbl(l_curr_row).ATTRIBUTE_ID,
1155 l_profile_id,
1156 l_profile_attrib_tbl(l_curr_row).profile_attribute,
1157 l_profile_attrib_tbl(l_curr_row).attribute_type,
1158 l_profile_attrib_tbl(l_curr_row).attribute_value
1159 );
1160 l_found_flag := TRUE;
1161 EXIT l_inner_loop;
1162 END IF;
1163
1164 END LOOP; -- end inner loop
1165
1166 IF NOT (l_found_flag) THEN
1167
1168 -- INSERT attribute into table
1169 insert_row_profile_attrib(
1170 l_rowid,
1171 l_profile_attrib_tbl(l_curr_row).ATTRIBUTE_ID,
1172 l_profile_id,
1173 l_profile_attrib_tbl(l_curr_row).profile_attribute,
1174 l_profile_attrib_tbl(l_curr_row).attribute_type,
1175 l_profile_attrib_tbl(l_curr_row).attribute_value
1176 );
1177 END IF;
1178
1179 END LOOP; -- end outer loop
1180
1181 if (x_return_status is not null) then
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183 end if;
1184
1185 END IF; -- for success check
1186 -- ******** Standard Ends ***********
1187 --
1188 -- End of main API body.
1189
1190 -- Standard check of p_commit.
1191 IF (FND_API.To_Boolean(p_commit)) THEN
1192 COMMIT WORK;
1193 END IF;
1194
1195 -- Standard call to get message count and if count is 1, get message info.
1196 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1197 p_data => x_msg_data );
1198
1199 EXCEPTION
1200
1201 WHEN FND_API.G_EXC_ERROR THEN
1202 ROLLBACK TO UPDATE_PERZ_PROFILE_PVT;
1203 x_return_status := FND_API.G_RET_STS_ERROR ;
1204
1205 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1206 p_data => x_msg_data );
1207
1208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1209 ROLLBACK TO UPDATE_PERZ_PROFILE_PVT;
1210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1211
1212 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1213 p_data => x_msg_data );
1214
1215 WHEN OTHERS THEN
1216 ROLLBACK TO UPDATE_PERZ_PROFILE_PVT;
1217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1218 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1219 THEN
1220 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1221 END IF;
1222
1223 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1224 p_data => x_msg_data );
1225
1226 END Update_Profile;
1227 -- ****************************************************************************
1228 -- ****************************************************************************
1229
1230 END JTF_PERZ_PROFILE_PVT;