[Home] [Help]
PACKAGE BODY: APPS.JTF_PERZ_DATA_PVT
Source
1 PACKAGE BODY JTF_PERZ_DATA_PVT as
2 /* $Header: jtfzvpdb.pls 120.2 2005/11/02 22:31:23 skothe ship $ */
3 --
4 --
5 -- Start of Comments
6 --
7 -- NAME
8 -- JTF_PERZ_DATA_PVT
9
10 -- HISTORY
11 --
12 -- 09/20/99 SMATTEGU Created
13 -- 09/30/99 SMATTEGU changed the save() to reflect profile_id fix
14 -- 10/26/99 SMATTEGU fixed bug 1051390 type is considered by
15 -- get_perz_data_summary() now
16 -- 11/02/99 SMATTEGU fixed bug 1050713 type is considered by
17 -- get_perz_data() now
18 --
19 -- 11/10/99 SMATTEGU fixed bug 1070584, who column changes
20 --
21 -- 01/24/2000 SMATTEGU Enhancement #1165283
22 -- 02/03/2000 SMATTEGU Enhancement #1181062 changing the
23 -- perz_data_name size from 60 to 120
24 -- End of Comments
25 --
26 --*****************************************************************************
27
28
29 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_PERZ_DATA_PVT';
30 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfvpzdb.pls';
31
32
33 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
34 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
35
36
37
38 -- *****************************************************************************
39 -- *****************************************************************************
40 -- TABLE HANDLERS
41 -- 1. insert_jtf_perz_data
42 -- 2. insert_jtf_perz_data_attrib
43 -- 3. update_jtf_perz_data
44 -- *****************************************************************************
45 -- *****************************************************************************
46 --
47
48
49 PROCEDURE insert_jtf_perz_data(
50 px_PERZ_DATA_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
51 p_PROFILE_ID NUMBER,
52 p_APPLICATION_ID NUMBER,
53 p_PERZ_DATA_NAME VARCHAR2,
54 p_PERZ_DATA_TYPE VARCHAR2,
55 p_PERZ_DATA_DESC VARCHAR2
56 )
57
58 IS
59 CURSOR C2 IS SELECT JTF_PERZ_DATA_S.nextval FROM sys.dual;
60 BEGIN
61 If (px_PERZ_DATA_ID IS NULL) OR (px_PERZ_DATA_ID = FND_API.G_MISS_NUM) then
62 OPEN C2;
63 FETCH C2 INTO px_PERZ_DATA_ID;
64 CLOSE C2;
65 End If;
66 INSERT INTO JTF_PERZ_DATA(
67 PERZ_DATA_ID,
68 PROFILE_ID,
69 APPLICATION_ID,
70 PERZ_DATA_NAME,
71 PERZ_DATA_TYPE,
72 PERZ_DATA_DESC,
73 OBJECT_VERSION_NUMBER,
74 CREATED_BY,
75 LAST_UPDATE_DATE,
76 LAST_UPDATED_BY,
77 LAST_UPDATE_LOGIN
78 ) VALUES (
79 px_PERZ_DATA_ID,
80 decode( p_PROFILE_ID, FND_API.G_MISS_NUM, NULL, p_PROFILE_ID),
81 decode( p_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_APPLICATION_ID),
82 decode( p_PERZ_DATA_NAME, FND_API.G_MISS_CHAR, NULL, p_PERZ_DATA_NAME),
83 decode( p_PERZ_DATA_TYPE, FND_API.G_MISS_CHAR, NULL, p_PERZ_DATA_TYPE),
84 decode( p_PERZ_DATA_DESC, FND_API.G_MISS_CHAR, NULL, p_PERZ_DATA_DESC),
85 1, G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);
86 End insert_jtf_perz_data;
87
88 -- *****************************************************************************
89 PROCEDURE insert_jtf_perz_data_attrib(
90 px_PERZ_DATA_ATTRIB_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
91 p_PERZ_DATA_ID NUMBER,
92 p_ATTRIBUTE_NAME VARCHAR2,
93 p_ATTRIBUTE_TYPE VARCHAR2,
94 p_ATTRIBUTE_VALUE VARCHAR2,
95 p_ATTRIBUTE_CONTEXT VARCHAR2)
96
97 IS
98 CURSOR C2 IS SELECT JTF_PERZ_DATA_ATTRIBUTES_S.nextval FROM sys.dual;
99 BEGIN
100 If (px_PERZ_DATA_ATTRIB_ID IS NULL) OR (px_PERZ_DATA_ATTRIB_ID = FND_API.G_MISS_NUM) then
101 OPEN C2;
102 FETCH C2 INTO px_PERZ_DATA_ATTRIB_ID;
103 CLOSE C2;
104 End If;
105 INSERT INTO JTF_PERZ_DATA_ATTRIB(
106 PERZ_DATA_ATTRIB_ID,
107 PERZ_DATA_ID,
108 ATTRIBUTE_NAME,
109 ATTRIBUTE_TYPE,
110 ATTRIBUTE_VALUE,
111 ATTRIBUTE_CONTEXT,
112 CREATED_BY,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY,
115 LAST_UPDATE_LOGIN
116 ) VALUES (
117 px_PERZ_DATA_ATTRIB_ID,
118 decode( p_PERZ_DATA_ID, FND_API.G_MISS_NUM, NULL, p_PERZ_DATA_ID),
119 decode( p_ATTRIBUTE_NAME, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_NAME),
120 decode( p_ATTRIBUTE_TYPE, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_TYPE),
121 decode( p_ATTRIBUTE_VALUE, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_VALUE),
122 decode( p_ATTRIBUTE_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CONTEXT),
123 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID );
124
125 End insert_jtf_perz_data_attrib;
126
127 -- *****************************************************************************
128
129 PROCEDURE update_jtf_perz_data(
130 p_PERZ_DATA_ID NUMBER,
131 p_PROFILE_ID NUMBER,
132 p_APPLICATION_ID NUMBER,
133 p_PERZ_DATA_NAME VARCHAR2,
134 p_PERZ_DATA_TYPE VARCHAR2,
135 p_PERZ_DATA_DESC VARCHAR2,
136 p_OBJECT_VERSION_NUMBER IN NUMBER)
137
138 IS
139 BEGIN
140 Update JTF_PERZ_DATA
141 SET
142 PROFILE_ID = decode( p_PROFILE_ID, FND_API.G_MISS_NUM, PROFILE_ID, p_PROFILE_ID),
143 APPLICATION_ID = decode( p_APPLICATION_ID, FND_API.G_MISS_NUM, APPLICATION_ID, p_APPLICATION_ID),
144 PERZ_DATA_NAME = decode( p_PERZ_DATA_NAME, FND_API.G_MISS_CHAR, PERZ_DATA_NAME, p_PERZ_DATA_NAME),
145 PERZ_DATA_TYPE = decode( p_PERZ_DATA_TYPE, FND_API.G_MISS_CHAR, PERZ_DATA_TYPE, p_PERZ_DATA_TYPE),
146 PERZ_DATA_DESC = decode( p_PERZ_DATA_DESC, FND_API.G_MISS_CHAR, PERZ_DATA_DESC, p_PERZ_DATA_DESC),
147 OBJECT_VERSION_NUMBER = decode (p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
148 OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
149 LAST_UPDATE_DATE = SYSDATE,
150 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
151 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
152 where PERZ_DATA_ID = p_PERZ_DATA_ID
153 and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
154
155 If (SQL%NOTFOUND) then
156 RAISE NO_DATA_FOUND;
157 End If;
158 END update_jtf_perz_data;
159
160 -- *****************************************************************************
161 -- *****************************************************************************
162 --
163 -- PRIVATE PROCEDURES
164 -- check_perz_data()
165 -- *****************************************************************************
166 PROCEDURE check_perz_data (
167 p_perz_data_name IN VARCHAR2,
168 p_application_id IN NUMBER,
169 p_profile_id IN NUMBER,
170 p_perz_data_type IN VARCHAR2,
171 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
172 x_perz_data_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
173 x_OBJECT_VERSION_NUMBER OUT NOCOPY /* file.sql.39 change */ NUMBER
174 ) IS
175 l_temp_id NUMBER;
176
177 BEGIN
178
179 --dbms_output.put_line(p_perz_data_name);
180
181 IF ((p_perz_data_name IS NOT NULL) AND
182 (p_perz_data_name <> FND_API.G_MISS_CHAR)) THEN
183
184
185 -- SMATTEGU Enhancement #1165283 BEGINS
186
187 if (p_perz_data_type <> FND_API.G_MISS_CHAR) THEN
188
189 SELECT perz_data_id, object_version_number
190 INTO x_perz_data_id, x_object_version_number
191 FROM jtf_perz_data
192 WHERE
193 perz_data_name = p_perz_data_name AND
194 application_id = p_application_id AND
195 profile_id = p_profile_id AND
196 perz_data_type = p_perz_data_type;
197
198 else -- p_perz_data_type is G_MISS_CHAR
199
200 SELECT perz_data_id, object_version_number
201 INTO x_perz_data_id, x_object_version_number
202 FROM jtf_perz_data
203 WHERE perz_data_name = p_perz_data_name AND
204 application_id = p_application_id AND
205 profile_id = p_profile_id;
206 end if;
207
208 -- SMATTEGU Enhancement #1165283 ENDS
209
210 if (x_perz_data_id IS NOT NULL) then
211 x_return_status := FND_API.G_TRUE;
212 else
213 x_return_status := FND_API.G_FALSE;
214 end if;
215
216 ELSE
217 IF ((x_perz_data_id IS NOT NULL) AND
218 (x_perz_data_id <> FND_API.G_MISS_NUM)) THEN
219
220 SELECT perz_data_id, object_version_number
221 INTO l_temp_id, x_object_version_number
222 from jtf_perz_data
223 where perz_data_id = x_perz_data_id;
224
225 if (l_temp_id IS NOT NULL) then
226 x_return_status := FND_API.G_TRUE;
227 else
228 x_return_status := FND_API.G_FALSE;
229 end if;
230
231 END IF;
232 END IF;
233
234 EXCEPTION
235
236 WHEN NO_DATA_FOUND THEN
237 x_return_status := FND_API.G_FALSE;
238
239 END check_perz_data;
240 -- *****************************************************************************
241 -- *****************************************************************************
242 -- APIs
243 -- Save_Perz_Data
244 -- Create_Perz_Data
245 -- Get_Perz_Data
246 -- Get_Perz_Data_Summary
247 -- Update_Perz_Data
248 -- Delete_Perz_Data
249 -- *****************************************************************************
250 -- *****************************************************************************
251
252 PROCEDURE Save_Perz_Data
253 ( p_api_version_number IN NUMBER,
254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
255 p_commit IN VARCHAR := FND_API.G_FALSE,
256
257 p_application_id IN NUMBER,
258 p_profile_id IN NUMBER,
259 p_profile_name IN VARCHAR2,
260 p_profile_type IN VARCHAR2,
261 p_profile_attrib IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
262 := JTF_PERZ_PROFILE_PUB.G_MISS_PROFILE_ATTRIB_TBL,
263 p_perz_data_id IN NUMBER,
264 p_perz_data_name IN VARCHAR2,
265 p_perz_data_type IN VARCHAR2,
266 p_perz_data_desc IN VARCHAR2,
267 p_data_attrib_tbl IN JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE
268 := JTF_PERZ_DATA_PUB.G_MISS_DATA_ATTRIB_TBL,
269
270 x_perz_data_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
271 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
272 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
273 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
274 )IS
275
276 --******** Save_Perz_Data local variable for standards **********
277 l_api_name CONSTANT VARCHAR2(30) := 'Save PerzData';
278 l_api_version_number NUMBER := p_api_version_number;
279
280 --******** Save_Perz_Data local variable for implementation *****
281 l_return_status VARCHAR2(240) := FND_API.G_RET_STS_SUCCESS;
282 l_perz_data_id NUMBER := p_perz_data_id;
283 l_PERZ_DATA_ATTRIB_ID NUMBER := NULL;
284 l_profile_id NUMBER;
285 l_is_duplicate VARCHAR2(1) := FND_API.G_FALSE;
286 l_profile_attrib JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
287 := p_profile_attrib;
288 l_profile_name VARCHAR2(60) := p_profile_name;
289 l_commit VARCHAR2(1) := FND_API.G_FALSE;
290 l_object_version_number NUMBER :=NULL;
291
292 BEGIN
293 -- ******* Standard Begins ********
294
295 -- Standard Start of API savepoint
296 SAVEPOINT SAVE_PERZ_DATA_PVT;
297
298 -- -- Standard call to check for call compatibility.
299 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
300 -- THEN
301 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 -- END IF;
303
304 -- Initialize message list if p_init_msg_list is set to TRUE.
308 END IF;
305 IF FND_API.to_Boolean( p_init_msg_list )
306 THEN
307 FND_MSG_PUB.initialize;
309
310 -- Initialize API return status to success
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312
313
314 -- CALL FLOW :
315 -- 1. Check for profile, if not existing create profile.
316 -- 2. check if duplicate PerzData exists if TRUE,
317 -- then call update()
318 -- else call insert()
319
320
321
322 -- 1. check profile
323
324
325 --dbms_output.put_line('before chk profile ');
326 if ((p_profile_id IS NOT NULL) AND
327 (p_profile_id <> FND_API.G_MISS_NUM)) then
328 l_profile_id := p_profile_id;
329 else
330 l_profile_id := NULL;
331 end if;
332
333
334 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
335 l_profile_name,
336 l_return_status,
337 l_profile_id
338 );
339 --dbms_output.put_line('aft chk profile, profileId:'||l_profile_id);
340
341 -- 1.1 if profile is not available, create profile
342
343 if (l_return_status = FND_API.G_FALSE) then
344
345 l_return_status := FND_API.G_RET_STS_SUCCESS;
346 l_commit := FND_API.G_FALSE;
347
348 l_profile_id := p_profile_id;
349
350 --dbms_output.put_line('before create profile ');
351 JTF_PERZ_PROFILE_PVT.Create_Profile(
352 p_api_version_number => l_api_version_number,
353 p_commit => l_commit,
354 p_profile_id => l_profile_id,
355 p_profile_name => p_profile_name,
356 p_profile_type => p_profile_type,
357 p_profile_attrib_tbl => l_profile_attrib,
358 x_profile_name => l_profile_name,
359 x_profile_id => l_profile_id,
360 x_return_status => l_return_status,
361 x_msg_count => x_msg_count,
362 x_msg_data => x_msg_data
363 );
364
365 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
366 RAISE FND_API.G_EXC_ERROR;
367 end if;
368 --dbms_output.put_line('aft chk profile, profileId:'||l_profile_id);
369 end if;
370
371 -- 2. CHECK IF DUPLICATE EXISTS
372 -- the duplicacy is defined as having the same perz data name
373 -- for the a profile id within an application id.
374
375 --dbms_output.put_line('stop 1');
376 check_perz_data (
377 p_perz_data_name,
378 p_application_id,
379 l_profile_id,
380 p_perz_data_type,
381 l_is_duplicate,
382 l_perz_data_id,
383 l_object_version_number);
384
385 --dbms_output.put_line(' l_duplicate ' || l_is_duplicate);
386 l_commit := FND_API.G_FALSE;
387
388 IF (FND_API.To_Boolean(l_is_duplicate)) THEN
389
390 --dbms_output.put_line('stop 3');
391 -- Call update_perz_data
392 Update_Perz_Data
393 ( l_api_version_number,
394 p_init_msg_list,
395 l_commit,
396
397 p_application_id,
398 l_profile_id ,
399
400 l_perz_data_id,
401 p_perz_data_name ,
402 p_perz_data_type ,
403 p_perz_data_desc,
404
405 p_data_attrib_tbl,
406
407 x_perz_data_id ,
408 x_return_status ,
409 x_msg_count,
410 x_msg_data
411 );
412
413 ELSE
414
415
416 --dbms_output.put_line('stop 2');
417 -- Call create_perz_data
418 Create_Perz_Data
419 ( l_api_version_number,
420 p_init_msg_list,
421 l_commit,
422
423 p_application_id,
424
425 l_profile_id ,
426 p_profile_name ,
427
428 l_perz_data_id,
429 p_perz_data_name ,
430 p_perz_data_type ,
431 p_perz_data_desc,
432
433 p_data_attrib_tbl,
434
435 x_perz_data_id ,
436 x_return_status ,
437 x_msg_count,
438 x_msg_data
439 );
440
441 END IF;
442
443 -- ******** Standard Ends ***********
444 --
445 -- End of main API body.
446
447 -- Standard check of p_commit.
448 IF (FND_API.To_Boolean(p_commit)) THEN
449 COMMIT WORK;
450 END IF;
451
452 -- Standard call to get message count and if count is 1, get message info.
453 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
454 p_data => x_msg_data );
455
456 EXCEPTION
457
458 WHEN FND_API.G_EXC_ERROR THEN
459 --dbms_output.put_line('stop 4 ');
460
461 ROLLBACK TO SAVE_PERZ_DATA_PVT;
462 x_return_status := FND_API.G_RET_STS_ERROR ;
463
464 FND_MSG_PUB.Count_And_Get
465 ( p_count => x_msg_count,
466 p_data => x_msg_data );
467
468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
469 --dbms_output.put_line('stop 5 ');
470 ROLLBACK TO SAVE_PERZ_DATA_PVT;
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
472 FND_MSG_PUB.Count_And_Get
473 ( p_count => x_msg_count,
474 p_data => x_msg_data );
475
476
477 WHEN OTHERS THEN
478 --dbms_output.put_line('stop 5 ');
479 ROLLBACK TO SAVE_PERZ_DATA_PVT;
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
481
482 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
483 THEN
484 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
485 END IF;
486
487 FND_MSG_PUB.Count_And_Get
488 ( p_count => x_msg_count,
489 p_data => x_msg_data );
490
491
492 END Save_Perz_Data;
493
494
495 -- *****************************************************************************
496 --
497
498
499 PROCEDURE Create_Perz_Data
503
500 ( p_api_version_number IN NUMBER,
501 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
502 p_commit IN VARCHAR := FND_API.G_FALSE,
504 p_application_id IN NUMBER,
505 p_profile_id IN NUMBER,
506 p_profile_name IN VARCHAR2,
507 p_perz_data_id IN NUMBER,
508 p_perz_data_name IN VARCHAR2,
509 p_perz_data_type IN VARCHAR2,
510 p_perz_data_desc IN VARCHAR2,
511 p_data_attrib_tbl IN JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE
512 := JTF_PERZ_DATA_PUB.G_MISS_DATA_ATTRIB_TBL,
513
514 x_perz_data_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
515 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
516 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
517 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
518 )IS
519 --******** Create_Perz_Data local variable for standards **********
520 l_api_name CONSTANT VARCHAR2(30) := 'Create PerzData';
521 l_api_version_number NUMBER := p_api_version_number;
522
523 -- ******* Create_Perz_Data Local Variables ********
524 l_perz_data_id NUMBER := p_perz_data_id;
525 l_PERZ_DATA_ATTRIB_ID NUMBER := NULL;
526 l_is_duplicate VARCHAR2(1) := FND_API.G_FALSE;
527 l_curr_row NUMBER := NULL;
528 l_object_version_number NUMBER := NULL;
529
530
531 BEGIN
532 -- ******* Standard Begins ********
533
534 -- Standard Start of API savepoint
535 SAVEPOINT CREATE_PERZ_DATA_PVT;
536
537 -- -- Standard call to check for call compatibility.
538 -- IF NOT FND_API.Compatible_API_Call
539 -- ( l_api_version_number,
540 -- p_api_version_number, l_api_name, G_PKG_NAME)
541 -- THEN
542 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 -- END IF;
544
545 -- Initialize message list if p_init_msg_list is set to TRUE.
546 IF FND_API.to_Boolean( p_init_msg_list )
547 THEN
548 FND_MSG_PUB.initialize;
549 END IF;
550
551 -- Initialize API return status to success
552 x_return_status := FND_API.G_RET_STS_SUCCESS;
553
554
555 -- CALL FLOW :
556 -- 1. check if duplicate exists if TRUE, return error
557 -- 2. if FALSE, do
558 -- 3. insert row with perz data into perz data table
559 -- 4. pick perz_data_id and cycle through attributes
560 -- insert into attributes table
561
562
563 -- 1. CHECK IF DUPLICATE EXISTS
564 -- the duplicacy is defined as having the same perz data name
565 -- for the a profile id within an application id.
566
567 check_perz_data (
568 p_perz_data_name,
569 p_application_id,
570 p_profile_id,
571 p_perz_data_type,
572 l_is_duplicate,
573 l_perz_data_id,
574 l_object_version_number);
575
576 --dbms_output.put_line(' l_duplicate ' || l_is_duplicate);
577
578 IF (FND_API.To_Boolean(l_is_duplicate)) THEN
579 x_return_status := FND_API.G_RET_STS_ERROR ;
580 RAISE FND_API.G_EXC_ERROR;
581 END IF;
582
583 insert_jtf_perz_data(
584 l_perz_data_id,
585 p_PROFILE_ID,
586 p_APPLICATION_ID,
587 p_PERZ_DATA_NAME,
588 p_PERZ_DATA_TYPE,
589 p_PERZ_DATA_DESC
590 );
591
592 -- copying ID to output.
593
594 x_perz_data_id := l_perz_data_id;
595
596
597 --dbms_output.put_line('perz_data_id from insert ' || l_perz_data_id);
598
599 -- 5. insert records into field map table
600
601 IF (p_data_attrib_tbl.COUNT > 0) THEN
602
603 FOR l_curr_row in 1..p_data_attrib_tbl.COUNT LOOP
604
605
606 l_PERZ_DATA_ATTRIB_ID :=p_data_attrib_tbl(l_curr_row).PERZ_DATA_ATTRIB_ID ;
607 --dbms_output.put_line('attribute count ' || p_data_attrib_tbl.count);
608
609 insert_jtf_perz_data_attrib(
610 l_PERZ_DATA_ATTRIB_ID,
611 l_perz_data_id,
612 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_NAME,
613 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_TYPE,
614 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE,
615 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_CONTEXT);
616 --dbms_output.put_line('profile attribute id from insert ' || l_perz_data_attrib_id);
617
618 END LOOP;
619 END IF;
620
621 -- ******** Standard Ends ***********
622 --
623 -- End of main API body.
624
625 -- Standard check of p_commit.
626 IF (FND_API.To_Boolean(p_commit)) THEN
627 COMMIT WORK;
628 END IF;
629
630 -- Standard call to get message count and if count is 1, get message info.
631 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
632 p_data => x_msg_data );
633
634 EXCEPTION
635
636 WHEN FND_API.G_EXC_ERROR THEN
637 --dbms_output.put_line('stop 1 ');
638
639 ROLLBACK TO CREATE_PERZ_DATA_PVT;
640 x_return_status := FND_API.G_RET_STS_ERROR ;
641
642 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
643 p_data => x_msg_data );
644
645 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
646 --dbms_output.put_line('stop 2 ');
647 ROLLBACK TO CREATE_PERZ_DATA_PVT;
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
649
650 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
651 p_data => x_msg_data );
652
653 WHEN OTHERS THEN
654 --dbms_output.put_line('stop 3 ');
655 ROLLBACK TO CREATE_PERZ_DATA_PVT;
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
657
658 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
659 THEN
660 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
661 END IF;
662
666
663 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
664 p_data => x_msg_data );
665
667 END Create_Perz_Data;
668 -- *****************************************************************************
669
670
671 PROCEDURE Get_Perz_Data
672 ( p_api_version_number IN NUMBER,
673 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
674
675 p_application_id IN NUMBER,
676 p_profile_id IN NUMBER,
677 p_profile_name IN VARCHAR2,
678 p_perz_data_id IN NUMBER,
679 p_perz_data_name IN VARCHAR2,
680 p_perz_data_type IN VARCHAR2 := NULL,
681
682 x_perz_data_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
683 x_perz_data_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
684 x_perz_data_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
685 x_perz_data_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
686 x_data_attrib_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE,
687
688 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
689 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
690 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
691 )IS
692
693
694 --******** Get_Perz_Data local variable for standards **********
695 l_api_name CONSTANT VARCHAR2(30) := 'Get PerzData';
696 l_api_version_number NUMBER := p_api_version_number;
697
698 -- ******* Get_Perz_Data Local Variables ********
699 l_perz_data_id NUMBER := p_perz_data_id;
700 l_count NUMBER := NULL;
701 l_data_out_tbl JTF_PERZ_DATA_PUB.DATA_OUT_TBL_TYPE;
702
703 -- temp variables
704
705 l_PERZ_DATA_ATTRIB_ID_temp NUMBER := NULL;
706 l_PERZ_DATA_ID_temp NUMBER := NULL;
707 l_ATTRIBUTE_NAME_temp VARCHAR2(60) := NULL;
708 l_ATTRIBUTE_TYPE_temp VARCHAR2(30) := NULL;
709 l_ATTRIBUTE_VALUE_temp VARCHAR2(300) := NULL;
710 l_ATTRIBUTE_CONTEXT_temp VARCHAR2(360) := NULL;
711
712 -- cursors
713 CURSOR C_Get_Perz_Data (p_perz_data_id NUMBER ) IS
714 SELECT perz_data_attrib_id, perz_data_id, attribute_name,
715 attribute_type, attribute_value, attribute_context
716 FROM jtf_perz_data_attrib
717 WHERE perz_data_id = p_perz_data_id;
718
719
720 BEGIN
721 -- ******* Standard Begins ********
722
723 -- -- Standard call to check for call compatibility.
724 -- IF NOT FND_API.Compatible_API_Call
725 -- ( l_api_version_number,
726 -- p_api_version_number, l_api_name, G_PKG_NAME)
727 -- THEN
728 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
729 -- END IF;
730
731 -- Initialize message list if p_init_msg_list is set to TRUE.
732 IF FND_API.to_Boolean( p_init_msg_list )
733 THEN
734 FND_MSG_PUB.initialize;
735 END IF;
736
737 -- Initialize API return status to success
738 x_return_status := FND_API.G_RET_STS_SUCCESS;
739
740 --1. CAll Get_Perz_Data_Summary
741 --2. If the out table has mor ethan one row rasie error
742 -- else get attributes for given perz data id.
743
744 Get_Perz_Data_Summary(
745 p_api_version_number,
746 p_init_msg_list,
747 p_application_id,
748
749 p_profile_id ,
750 p_profile_name ,
751
752 l_perz_data_id,
753 p_perz_data_name ,
754 p_perz_data_type ,
755
756 l_data_out_tbl,
757
758 x_return_status ,
759 x_msg_count,
760 x_msg_data
761 );
762
763 --dbms_output.put_line('Summary API return status:'||x_return_status );
764 --dbms_output.put_line('# Summary API returned:'||l_data_out_tbl.count );
765 if ( x_return_status = FND_API.G_RET_STS_SUCCESS) then
766
767 l_count := l_data_out_tbl.count;
768 if ( l_count = 1) then
769 x_perz_data_id := l_data_out_tbl(l_count).perz_data_id;
770 x_perz_data_name := l_data_out_tbl(l_count).perz_data_name;
771 x_perz_data_type := l_data_out_tbl(l_count).perz_data_type;
772 x_perz_data_desc := l_data_out_tbl(l_count).perz_data_desc;
773
774
775
776
777 l_PERZ_DATA_ATTRIB_ID_temp := NULL;
778 l_PERZ_DATA_ID_temp := NULL;
779 l_ATTRIBUTE_NAME_temp := NULL;
780 l_ATTRIBUTE_TYPE_temp := NULL;
781 l_ATTRIBUTE_VALUE_temp := NULL;
782 l_ATTRIBUTE_CONTEXT_temp := NULL;
783
784 l_count := 1;
785 Open C_Get_Perz_Data (x_perz_data_id);
786 LOOP
787 FETCH C_Get_Perz_Data INTO
788 l_PERZ_DATA_ATTRIB_ID_temp,
789 l_PERZ_DATA_ID_temp,
790 l_ATTRIBUTE_NAME_temp,
791 l_ATTRIBUTE_TYPE_temp,
792 l_ATTRIBUTE_VALUE_temp,
793 l_ATTRIBUTE_CONTEXT_temp;
794
795 EXIT WHEN C_Get_Perz_Data%NOTFOUND;
796
797 IF(C_Get_Perz_Data%FOUND) THEN
798 x_data_attrib_tbl(l_count).PERZ_DATA_ATTRIB_ID := l_PERZ_DATA_ATTRIB_ID_temp;
799 x_data_attrib_tbl(l_count).PERZ_DATA_ID := l_PERZ_DATA_ID_temp;
800 x_data_attrib_tbl(l_count).ATTRIBUTE_NAME := l_ATTRIBUTE_NAME_temp;
801 x_data_attrib_tbl(l_count).ATTRIBUTE_TYPE := l_ATTRIBUTE_TYPE_temp;
802 x_data_attrib_tbl(l_count).ATTRIBUTE_VALUE := l_ATTRIBUTE_VALUE_temp;
803 x_data_attrib_tbl(l_count).ATTRIBUTE_CONTEXT := l_ATTRIBUTE_CONTEXT_temp;
804 l_count := l_count +1;
805
806 END IF;
807 END LOOP;
808 CLOSE C_Get_Perz_Data;
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 else
812 -- Currently, this API supports only One PerzData Object
813 -- If there are more than one retrieved then, this results in an error
814 -- This is a limitation because of Java Layer cannot support more than
815 -- one PerzData.
816
817 RAISE FND_API.G_EXC_ERROR;
821
818 end if;
819 end if;
820
822 -- ******** Standard Ends ***********
823 -- Standard call to get message count and if count is 1, get message info.
824 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
825 p_data => x_msg_data );
826
827 EXCEPTION
828
829 WHEN FND_API.G_EXC_ERROR THEN
830
831 x_return_status := FND_API.G_RET_STS_ERROR ;
832
833 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
834 p_data => x_msg_data );
835
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
839
840 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
841 p_data => x_msg_data );
842
843 WHEN OTHERS THEN
844
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
846
847 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
848 THEN
849 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
850 END IF;
851
852 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
853 p_data => x_msg_data );
854
855
856
857 END Get_Perz_Data;
858 -- *****************************************************************************
859
860 PROCEDURE Get_Perz_Data_Summary
861 ( p_api_version_number IN NUMBER,
862 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
863
864 p_application_id IN NUMBER,
865 p_profile_id IN NUMBER,
866 p_profile_name IN VARCHAR2,
867 p_perz_data_id IN NUMBER,
868 p_perz_data_name IN VARCHAR2,
869 p_perz_data_type IN VARCHAR2 := NULL,
870
871 x_data_out_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_DATA_PUB.DATA_OUT_TBL_TYPE,
872
873 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
874 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
875 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
876 )IS
877
878 --******** Get_Perz_Data_Summary local variable for standards **********
879 l_api_name CONSTANT VARCHAR2(30) := 'Create PerzData';
880 l_api_version_number NUMBER := p_api_version_number;
881
882 -- ******* Get_Perz_Data_Summary Local Variables ********
883 l_profile_id NUMBER := p_profile_id;
884 l_profile_name VARCHAR2(60):= p_profile_name;
885 l_return_status VARCHAR2(240):= FND_API.G_TRUE;
886 l_count NUMBER := NULL;
887
888 -- Temporary Variables
889 -- SMATTEGU Enhancement #1181062 Begin
890 l_perz_data_temp_rec JTF_PERZ_DATA_PUB.DATA_OUT_REC_TYPE;
891 -- Instead of individual temp variables, this will be referred in the select
892 -- into statements etc.
893 -- SMATTEGU Enhancement #1181062 End
894 /*
895 l_perz_data_id_temp number;
896 l_profile_id_temp NUMBER;
897 l_application_id_temp NUMBER;
898 l_perz_data_name_temp VARCHAR2(60);
899 l_perz_data_type_temp VARCHAR2(30);
900 l_perz_data_desc_temp VARCHAR2(240);
901 */
902 -- cursors
903
904 CURSOR C_Get_Perz_Summary (p_profile_id NUMBER, p_application_id NUMBER) IS
905 SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
906 perz_data_type, perz_data_desc
907 FROM jtf_perz_data
908 WHERE Profile_ID = p_profile_id AND
909 Application_ID = p_application_id;
910
911 CURSOR C_Get_Perz_Summary_pzdid (p_perz_data_id NUMBER ) IS
912 SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
913 perz_data_type, perz_data_desc
914 FROM jtf_perz_data
915 WHERE perz_data_id = p_perz_data_id;
916
917 CURSOR C_Get_Perz_Summary_pzdn (p_perz_data_name VARCHAR2,
918 p_profile_id NUMBER, p_application_id NUMBER) IS
919 SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
920 perz_data_type, perz_data_desc
921 FROM jtf_perz_data
922 WHERE perz_data_name = p_perz_data_name AND
923 Profile_ID = p_profile_id AND
924 Application_ID = p_application_id;
925
926 CURSOR C_Get_Perz_Summary_pzdt (p_perz_data_type VARCHAR2,
927 p_profile_id NUMBER, p_application_id NUMBER) IS
928 SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
929 perz_data_type, perz_data_desc
930 FROM jtf_perz_data
931 WHERE perz_data_type = p_perz_data_type AND
932 Profile_ID = p_profile_id AND
933 Application_ID = p_application_id;
934
935 -- SMATTEGU Enhancement #1165283 BEGINS
936
937 CURSOR C_Get_Perz_Summary_pzdnt (p_perz_data_name VARCHAR2,
938 p_perz_data_type VARCHAR2, p_profile_id NUMBER,
939 p_application_id NUMBER) IS
940 SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
941 perz_data_type, perz_data_desc
942 FROM jtf_perz_data
943 WHERE perz_data_name = p_perz_data_name AND
944 perz_data_type = p_perz_data_type AND
945 Profile_ID = p_profile_id AND
946 Application_ID = p_application_id;
947
948 -- SMATTEGU Enhancement #1165283 ENDS
949
950 BEGIN
951 -- ******* Standard Begins ********
952
953 -- -- Standard call to check for call compatibility.
954 -- IF NOT FND_API.Compatible_API_Call
955 -- ( l_api_version_number,
956 -- p_api_version_number, l_api_name, G_PKG_NAME)
957 -- THEN
958 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
959 -- END IF;
960
961 -- Initialize message list if p_init_msg_list is set to TRUE.
962 IF FND_API.to_Boolean( p_init_msg_list )
963 THEN
964 FND_MSG_PUB.initialize;
965 END IF;
966
967 -- Initialize API return status to success
968 x_return_status := FND_API.G_RET_STS_SUCCESS;
969
973 l_perz_data_temp_rec := NULL;
970
971 if ((p_perz_data_id IS NOT NULL) AND
972 (p_perz_data_id <> FND_API.G_MISS_NUM)) then
974 /*
975 l_perz_data_id_temp := NULL;
976 l_profile_id_temp := NULL;
977 l_application_id_temp := NULL;
978 l_perz_data_name_temp := NULL;
979 l_perz_data_type_temp := NULL;
980 l_perz_data_desc_temp := NULL;
981 */
982 l_count := 1;
983
984 OPEN C_Get_Perz_Summary_pzdid(p_perz_data_id);
985
986 LOOP
987 FETCH C_Get_Perz_Summary_pzdid INTO
988 l_perz_data_temp_rec.perz_data_id,
989 l_perz_data_temp_rec.profile_id,
990 l_perz_data_temp_rec.application_id,
991 l_perz_data_temp_rec.perz_data_name,
992 l_perz_data_temp_rec.perz_data_type,
993 l_perz_data_temp_rec.perz_data_desc;
994 EXIT WHEN C_Get_Perz_Summary_pzdid%NOTFOUND;
995 IF ( C_Get_Perz_Summary_pzdid%FOUND = TRUE ) THEN
996 x_data_out_tbl(l_count).perz_data_id := l_perz_data_temp_rec.perz_data_id;
997 x_data_out_tbl(l_count).profile_id := l_perz_data_temp_rec.profile_id;
998 x_data_out_tbl(l_count).application_id := l_perz_data_temp_rec.application_id;
999 x_data_out_tbl(l_count).perz_data_name := l_perz_data_temp_rec.perz_data_name;
1000 x_data_out_tbl(l_count).perz_data_type := l_perz_data_temp_rec.perz_data_type;
1001 x_data_out_tbl(l_count).perz_data_desc := l_perz_data_temp_rec.perz_data_desc;
1002 l_count := l_count + 1;
1003 END IF;
1004 END LOOP;
1005 CLOSE C_Get_Perz_Summary_pzdid;
1006 x_return_status := FND_API.G_RET_STS_SUCCESS;
1007
1008 else
1009
1010 l_profile_id := p_profile_id;
1011 l_profile_name := p_profile_name;
1012 l_return_status := FND_API.G_TRUE;
1013
1014 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
1015 l_profile_name,
1016 l_return_status,
1017 l_profile_id
1018 );
1019
1020
1021 -- If profile does not exists, raise an error and exit
1022
1023 if (l_return_status = FND_API.G_FALSE) then
1024 RAISE FND_API.G_EXC_ERROR;
1025 end if;
1026
1027 if (((l_profile_id IS NOT NULL) AND
1028 (l_profile_id <> FND_API.G_MISS_NUM)) AND
1029 ((p_application_id IS NOT NULL) AND
1030 (p_application_id <> FND_API.G_MISS_NUM))) then
1031
1032 if ((p_perz_data_name IS NOT NULL) AND
1033 (p_perz_data_name <> FND_API.G_MISS_CHAR)) then
1034
1035 -- SMATTEGU Enhancement #1165283 BEGINS
1036
1037 if ((p_perz_data_type IS NOT NULL) AND
1038 (p_perz_data_type <> FND_API.G_MISS_CHAR)) then
1039
1040 l_perz_data_temp_rec := NULL;
1041 /*
1042 l_perz_data_id_temp := NULL;
1043 l_perz_data_name_temp := NULL;
1044 l_perz_data_type_temp := NULL;
1045 l_perz_data_desc_temp := NULL;
1046 l_application_id_temp := NULL;
1047 l_profile_id_temp := NULL;
1048 */
1049 l_count := 1;
1050 OPEN C_Get_Perz_Summary_pzdnt
1051 (p_perz_data_name,p_perz_data_type,
1052 l_profile_id, p_application_id);
1053 LOOP
1054 FETCH C_Get_Perz_Summary_pzdnt INTO
1055 l_perz_data_temp_rec.perz_data_id,
1056 l_perz_data_temp_rec.profile_id,
1057 l_perz_data_temp_rec.application_id,
1058 l_perz_data_temp_rec.perz_data_name,
1059 l_perz_data_temp_rec.perz_data_type,
1060 l_perz_data_temp_rec.perz_data_desc;
1061 EXIT WHEN C_Get_Perz_Summary_pzdnt%NOTFOUND;
1062 IF (C_Get_Perz_Summary_pzdnt%FOUND = TRUE) THEN
1063 x_data_out_tbl(l_count).perz_data_id := l_perz_data_temp_rec.perz_data_id;
1064 x_data_out_tbl(l_count).profile_id := l_perz_data_temp_rec.profile_id;
1065 x_data_out_tbl(l_count).application_id := l_perz_data_temp_rec.application_id;
1066 x_data_out_tbl(l_count).perz_data_name := l_perz_data_temp_rec.perz_data_name;
1067 x_data_out_tbl(l_count).perz_data_type := l_perz_data_temp_rec.perz_data_type;
1068 x_data_out_tbl(l_count).perz_data_desc := l_perz_data_temp_rec.perz_data_desc;
1069 l_count := l_count + 1;
1070 END IF;
1071 END LOOP;
1072 CLOSE C_Get_Perz_Summary_pzdnt ;
1073 x_return_status := FND_API.G_RET_STS_SUCCESS;
1074 else
1075 l_perz_data_temp_rec := NULL;
1076 /*
1077
1078 l_perz_data_id_temp := NULL;
1079 l_perz_data_name_temp := NULL;
1080 l_perz_data_type_temp := NULL;
1081 l_perz_data_desc_temp := NULL;
1082 l_application_id_temp := NULL;
1083 l_profile_id_temp := NULL;
1084 */
1085 l_count := 1;
1086 OPEN C_Get_Perz_Summary_pzdn (p_perz_data_name,
1087 l_profile_id, p_application_id);
1088 LOOP
1089 FETCH C_Get_Perz_Summary_pzdn INTO
1090 l_perz_data_temp_rec.perz_data_id,
1091 l_perz_data_temp_rec.profile_id,
1092 l_perz_data_temp_rec.application_id,
1093 l_perz_data_temp_rec.perz_data_name,
1094 l_perz_data_temp_rec.perz_data_type,
1095 l_perz_data_temp_rec.perz_data_desc;
1096 EXIT WHEN C_Get_Perz_Summary_pzdn%NOTFOUND;
1097 IF (C_Get_Perz_Summary_pzdn%FOUND = TRUE) THEN
1098 x_data_out_tbl(l_count).perz_data_id := l_perz_data_temp_rec.perz_data_id;
1099 x_data_out_tbl(l_count).profile_id := l_perz_data_temp_rec.profile_id;
1100 x_data_out_tbl(l_count).application_id := l_perz_data_temp_rec.application_id;
1101 x_data_out_tbl(l_count).perz_data_name := l_perz_data_temp_rec.perz_data_name;
1102 x_data_out_tbl(l_count).perz_data_type := l_perz_data_temp_rec.perz_data_type;
1103 x_data_out_tbl(l_count).perz_data_desc := l_perz_data_temp_rec.perz_data_desc;
1104 l_count := l_count + 1;
1105 END IF;
1106 END LOOP;
1107 CLOSE C_Get_Perz_Summary_pzdn ;
1108 x_return_status := FND_API.G_RET_STS_SUCCESS;
1109 end if;
1110
1111 -- SMATTEGU Enhancement #1165283 ENDS
1112
1113 elsif((p_perz_data_type IS NOT NULL) AND
1117 l_perz_data_id_temp := NULL;
1114 (p_perz_data_type <> FND_API.G_MISS_CHAR)) then
1115 l_perz_data_temp_rec := NULL;
1116 /*
1118 l_perz_data_name_temp := NULL;
1119 l_perz_data_type_temp := NULL;
1120 l_perz_data_desc_temp := NULL;
1121 l_application_id_temp := NULL;
1122 l_profile_id_temp := NULL;
1123 */
1124 l_count := 1;
1125 OPEN C_Get_Perz_Summary_pzdt (p_perz_data_type,
1126 l_profile_id, p_application_id);
1127 LOOP
1128 FETCH C_Get_Perz_Summary_pzdt INTO
1129 l_perz_data_temp_rec.perz_data_id,
1130 l_perz_data_temp_rec.profile_id,
1131 l_perz_data_temp_rec.application_id,
1132 l_perz_data_temp_rec.perz_data_name,
1133 l_perz_data_temp_rec.perz_data_type,
1134 l_perz_data_temp_rec.perz_data_desc;
1135 EXIT WHEN C_Get_Perz_Summary_pzdt%NOTFOUND;
1136 IF (C_Get_Perz_Summary_pzdt%FOUND = TRUE) THEN
1137 x_data_out_tbl(l_count).perz_data_id := l_perz_data_temp_rec.perz_data_id;
1138 x_data_out_tbl(l_count).profile_id := l_perz_data_temp_rec.profile_id;
1139 x_data_out_tbl(l_count).application_id := l_perz_data_temp_rec.application_id;
1140 x_data_out_tbl(l_count).perz_data_name := l_perz_data_temp_rec.perz_data_name;
1141 x_data_out_tbl(l_count).perz_data_type := l_perz_data_temp_rec.perz_data_type;
1142 x_data_out_tbl(l_count).perz_data_desc := l_perz_data_temp_rec.perz_data_desc;
1143 l_count := l_count + 1;
1144 END IF;
1145 END LOOP;
1146 CLOSE C_Get_Perz_Summary_pzdt ;
1147 x_return_status := FND_API.G_RET_STS_SUCCESS;
1148
1149 else
1150 l_perz_data_temp_rec := NULL;
1151 /*
1152 l_perz_data_id_temp := NULL;
1153 l_perz_data_name_temp := NULL;
1154 l_perz_data_type_temp := NULL;
1155 l_perz_data_desc_temp := NULL;
1156 l_application_id_temp := NULL;
1157 l_profile_id_temp := NULL;
1158 */
1159 l_count := 1;
1160 OPEN C_Get_Perz_Summary(l_profile_id, p_application_id);
1161 LOOP
1162 FETCH C_Get_Perz_Summary into
1163 l_perz_data_temp_rec.perz_data_id,
1164 l_perz_data_temp_rec.profile_id,
1165 l_perz_data_temp_rec.application_id,
1166 l_perz_data_temp_rec.perz_data_name,
1167 l_perz_data_temp_rec.perz_data_type,
1168 l_perz_data_temp_rec.perz_data_desc;
1169 EXIT WHEN C_Get_Perz_Summary%NOTFOUND;
1170 IF ( C_Get_Perz_Summary%FOUND) THEN
1171 x_data_out_tbl(l_count).perz_data_id := l_perz_data_temp_rec.perz_data_id;
1172 x_data_out_tbl(l_count).profile_id := l_perz_data_temp_rec.profile_id;
1173 x_data_out_tbl(l_count).application_id := l_perz_data_temp_rec.application_id;
1174 x_data_out_tbl(l_count).perz_data_name := l_perz_data_temp_rec.perz_data_name;
1175 x_data_out_tbl(l_count).perz_data_type := l_perz_data_temp_rec.perz_data_type;
1176 x_data_out_tbl(l_count).perz_data_desc := l_perz_data_temp_rec.perz_data_desc;
1177 l_count := l_count + 1;
1178 END IF;
1179 END LOOP;
1180 CLOSE C_Get_Perz_Summary;
1181 x_return_status := FND_API.G_RET_STS_SUCCESS;
1182 end if;
1183 end if;
1184 end if;
1185
1186
1187
1188 -- ******** Standard Ends ***********
1189 -- Standard call to get message count and if count is 1, get message info.
1190 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1191 p_data => x_msg_data );
1192
1193 EXCEPTION
1194
1195 WHEN FND_API.G_EXC_ERROR THEN
1196
1197 x_return_status := FND_API.G_RET_STS_ERROR ;
1198
1199 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1200 p_data => x_msg_data );
1201
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1205
1206 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1207 p_data => x_msg_data );
1208
1209 WHEN OTHERS THEN
1210
1211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1212
1213 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1214 THEN
1215 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1216 END IF;
1217
1218 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1219 p_data => x_msg_data );
1220
1221
1222 END Get_Perz_Data_Summary;
1223 -- *****************************************************************************
1224 --
1225
1226
1227 PROCEDURE Update_Perz_Data
1228 ( p_api_version_number IN NUMBER,
1229 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1230 p_commit IN VARCHAR := FND_API.G_FALSE,
1231
1232 p_application_id IN NUMBER,
1233 p_profile_id IN NUMBER,
1234
1235 p_perz_data_id IN NUMBER,
1236 p_perz_data_name IN VARCHAR2,
1237 p_perz_data_type IN VARCHAR2 := NULL,
1238 p_perz_data_desc IN VARCHAR2,
1239 p_data_attrib_tbl IN JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE
1240 := JTF_PERZ_DATA_PUB.G_MISS_DATA_ATTRIB_TBL,
1241
1242 x_perz_data_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1243 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1244 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1245 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1246 )IS
1247
1248 --******** Update_Perz_Data local variable for standards **********
1249 l_api_name CONSTANT VARCHAR2(30) := 'Update PerzData';
1250 l_api_version_number NUMBER := p_api_version_number;
1251
1252 -- ******* Update_Perz_Data Local Variables ********
1253 l_perz_data_id NUMBER := p_perz_data_id;
1254 l_PERZ_DATA_ATTRIB_ID NUMBER := NULL;
1255 l_is_duplicate VARCHAR2(1) := FND_API.G_FALSE;
1256 l_curr_row NUMBER := NULL;
1257 l_object_version_number NUMBER := NULL;
1258
1262
1259
1260 BEGIN
1261 -- ******* Standard Begins ********
1263 -- Standard Start of API savepoint
1264 SAVEPOINT UPDATE_PERZ_DATA_PVT;
1265
1266 -- -- Standard call to check for call compatibility.
1267 -- IF NOT FND_API.Compatible_API_Call
1268 -- ( l_api_version_number,
1269 -- p_api_version_number, l_api_name, G_PKG_NAME)
1270 -- THEN
1271 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1272 -- END IF;
1273
1274 -- Initialize message list if p_init_msg_list is set to TRUE.
1275 IF FND_API.to_Boolean( p_init_msg_list )
1276 THEN
1277 FND_MSG_PUB.initialize;
1278 END IF;
1279
1280 -- Initialize API return status to success
1281 x_return_status := FND_API.G_RET_STS_SUCCESS;
1282
1283
1284 -- CALL FLOW
1285 -- 1. Select Perz Data ID from database
1286 -- 2. Delete all the attributes for that Perz Data ID
1287 -- 3. Update Perz Data header information
1288 -- 4. Insert all new rocords from input.
1289
1290
1291
1292 -- 1. CHECK IF DUPLICATE EXISTS
1293 -- the duplicacy is defined as having the same perz data name
1294 -- for the a profile id within an application id.
1295
1296 check_perz_data (
1297 p_perz_data_name,
1298 p_application_id,
1299 p_profile_id,
1300 p_perz_data_type,
1301 l_is_duplicate,
1302 l_perz_data_id,
1303 l_object_version_number
1304 );
1305
1306 IF (FND_API.To_Boolean(l_is_duplicate)) THEN
1307 -- 2. Delete all the attributes for that Perz Data ID
1308
1309 DELETE FROM JTF_PERZ_DATA_ATTRIB WHERE
1310 PERZ_DATA_ID = l_perz_data_id;
1311
1312 -- 3. Update Perz Data header information
1313 update_jtf_perz_data(
1314 l_perz_data_id,
1315 p_PROFILE_ID,
1316 p_APPLICATION_ID,
1317 p_PERZ_DATA_NAME,
1318 p_PERZ_DATA_TYPE,
1319 p_PERZ_DATA_DESC,
1320 l_object_version_number);
1321
1322
1323 -- 4. Insert all new rocords from input.
1324
1325 IF (p_data_attrib_tbl.COUNT > 0) THEN
1326 FOR l_curr_row in 1..p_data_attrib_tbl.COUNT LOOP
1327
1328 l_PERZ_DATA_ATTRIB_ID :=p_data_attrib_tbl(l_curr_row).PERZ_DATA_ATTRIB_ID ;
1329 insert_jtf_perz_data_attrib(
1330 l_PERZ_DATA_ATTRIB_ID,
1331 l_perz_data_id,
1332 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_NAME,
1333 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_TYPE,
1334 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE,
1335 p_data_attrib_tbl(l_curr_row).ATTRIBUTE_CONTEXT);
1336 END LOOP;
1337 END IF;
1338 ELSE
1339 -- the perz data id does not exist
1340 x_return_status := FND_API.G_RET_STS_ERROR ;
1341 RAISE FND_API.G_EXC_ERROR;
1342 END IF;
1343
1344
1345 -- copying ID to output.
1346
1347 x_perz_data_id := l_perz_data_id;
1348
1349
1350 -- ******** Standard Ends ***********
1351 --
1352 -- End of main API body.
1353
1354 -- Standard check of p_commit.
1355 IF (FND_API.To_Boolean(p_commit)) THEN
1356 COMMIT WORK;
1357 END IF;
1358
1359 -- Standard call to get message count and if count is 1, get message info.
1360 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1361 p_data => x_msg_data );
1362
1363 EXCEPTION
1364
1365 WHEN FND_API.G_EXC_ERROR THEN
1366
1367 ROLLBACK TO UPDATE_PERZ_DATA_PVT;
1368 x_return_status := FND_API.G_RET_STS_ERROR ;
1369
1370 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1371 p_data => x_msg_data );
1372
1373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1374 ROLLBACK TO UPDATE_PERZ_DATA_PVT;
1375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1376
1377 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1378 p_data => x_msg_data );
1379
1380 WHEN OTHERS THEN
1381 ROLLBACK TO UPDATE_PERZ_DATA_PVT;
1382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1383
1384 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1385 THEN
1386 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1387 END IF;
1388
1389 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1390 p_data => x_msg_data );
1391
1392 END Update_Perz_Data;
1393
1394 -- *****************************************************************************
1395
1396 PROCEDURE Delete_Perz_Data
1397 ( p_api_version_number IN NUMBER,
1398 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1399 p_commit IN VARCHAR := FND_API.G_FALSE,
1400
1401 p_application_id IN NUMBER,
1402 p_profile_id IN NUMBER,
1403 p_perz_data_id IN NUMBER,
1404
1405 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1406 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1407 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1408 )IS
1409
1410 --******** Update_Perz_Data local variable for standards **********
1411 l_api_name CONSTANT VARCHAR2(30) := 'Create PerzData';
1412 l_api_version_number NUMBER := p_api_version_number;
1413
1414 -- ******* Update_Perz_Data Local Variables ********
1415 -- l_perz_data_id NUMBER := p_perz_data_id;
1416 BEGIN
1417
1418 -- ******* Standard Begins ********
1419
1420 -- Standard Start of API savepoint
1421 SAVEPOINT DELETE_PERZ_DATA_PVT;
1422
1423 -- -- Standard call to check for call compatibility.
1424 -- IF NOT FND_API.Compatible_API_Call
1425 -- ( l_api_version_number,
1426 -- p_api_version_number, l_api_name, G_PKG_NAME)
1430
1427 -- THEN
1428 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1429 -- END IF;
1431 -- Initialize message list if p_init_msg_list is set to TRUE.
1432 IF FND_API.to_Boolean( p_init_msg_list )
1433 THEN
1434 FND_MSG_PUB.initialize;
1435 END IF;
1436
1437 -- Initialize API return status to success
1438 x_return_status := FND_API.G_RET_STS_SUCCESS;
1439
1440
1441 -- CALL FLOW
1442 --1 Delete all the attributes
1443 --2 Delete the Perz Data
1444
1445 DELETE FROM JTF_PERZ_DATA_ATTRIB WHERE
1446 PERZ_DATA_ID = p_perz_data_id;
1447 DELETE FROM JTF_PERZ_DATA WHERE
1448 PERZ_DATA_ID = p_perz_data_id;
1449
1450
1451 -- ******** Standard Ends ***********
1452 --
1453 -- End of main API body.
1454
1455 -- Standard check of p_commit.
1456 IF (FND_API.To_Boolean(p_commit)) THEN
1457 COMMIT WORK;
1458 END IF;
1459
1460 -- Standard call to get message count and if count is 1, get message info.
1461 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1462 p_data => x_msg_data );
1463
1464 EXCEPTION
1465
1466 WHEN FND_API.G_EXC_ERROR THEN
1467
1468 ROLLBACK TO DELETE_PERZ_DATA_PVT;
1469 x_return_status := FND_API.G_RET_STS_ERROR ;
1470
1471 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1472 p_data => x_msg_data );
1473
1474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475 ROLLBACK TO DELETE_PERZ_DATA_PVT;
1476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1477
1478 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1479 p_data => x_msg_data );
1480
1481 WHEN OTHERS THEN
1482 ROLLBACK TO DELETE_PERZ_DATA_PVT;
1483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1484
1485 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1486 THEN
1487 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1488 END IF;
1489
1490 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1491 p_data => x_msg_data );
1492
1493
1494 END Delete_Perz_Data;
1495
1496 -- *****************************************************************************
1497
1498
1499
1500 -- *****************************************************************************
1501 -- *****************************************************************************
1502 END JTF_PERZ_DATA_PVT;