[Home] [Help]
PACKAGE BODY: APPS.JTF_PERZ_DDF_PVT
Source
1 PACKAGE BODY JTF_PERZ_DDF_PVT as
2 /* $Header: jtfzvddb.pls 120.2 2005/11/02 22:19:41 skothe ship $ */
3 --
4 --
5 -- Start of Comments
6 --
7 -- NAME
8 -- jtf_perz_ddf_pvt
9 --
10 -- PURPOSE
11 -- Public API for creating, getting, updating and deleteing data defaults
12 -- in the Personalization Framework.
13 --
14 -- NOTES
15 -- This is a pulicly accessible pacakge. It should be used by all
16 -- sources for creating, getting, updating and deleting personalized data defaults
17 -- in the Personalization Framework.
18 --
19 -- HISTORY
20 -- 09/10/99 SMATTEGU Created and documented the following
21 -- save_data_default()
22 -- create_data_default()
23 -- update_data_default()
24 -- delete_data_default()
25 -- 09/30/99 SMATTEGU changed the save() to reflect profile_id fix
26 -- 11/04/99 SMATTEGU Modifying the code to take into account
27 -- the who columns
28 -- Fixing an extra empty record problem
29 -- in get() BUG#1063736
30 --
31 -- End of Comments
32
33 --
34
35 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_PERZ_DDF_PVT';
36 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfzvddb.pls';
37
38
39 -- ****************************************************************************
40 -- TABLE HANDLERS
41 --
42 -- The following are the table handlers
43 --
44 -- 1. insert_jtf_perz_data_default
45 -- 2. update_jtf_perz_data_default
46 -- 3. delete_jtf_perz_data_default
47 -- ****************************************************************************
48 -- ****************************************************************************
49 PROCEDURE insert_jtf_perz_data_default(
50 x_PERZ_DDF_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
51 p_PROFILE_ID IN NUMBER,
52 p_APPLICATION_ID IN NUMBER,
53 p_PERZ_DDF_CONTEXT IN VARCHAR2,
54 p_GUI_OBJECT_NAME IN VARCHAR2,
55 p_GUI_OBJECT_ID IN NUMBER,
56 p_DDF_VALUE IN VARCHAR2,
57 p_DDF_VALUE_TYPE IN VARCHAR2,
58 p_OBJECT_VERSION_NUMBER IN NUMBER
59 ) IS
60 CURSOR C2 IS SELECT JTF_PERZ_DATA_DEFAULT_S.nextval FROM sys.dual;
61 BEGIN
62 If (x_PERZ_DDF_ID IS NULL) then
63 OPEN C2;
64 FETCH C2 INTO x_PERZ_DDF_ID;
65 CLOSE C2;
66 End If;
67 INSERT INTO JTF_PERZ_DATA_DEFAULT(
68 PERZ_DATA_DEFAULT_ID,
69 PROFILE_ID,
70 APPLICATION_ID,
71 PERZ_DDF_CONTEXT,
72 GUI_OBJECT_NAME,
73 GUI_OBJECT_ID,
74 PERZ_DDF_VALUE,
75 PERZ_DDF_VALUE_TYPE,
76 OBJECT_VERSION_NUMBER,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN
81 ) VALUES (
82 x_PERZ_DDF_ID,
83 decode( p_PROFILE_ID, FND_API.G_MISS_NUM, NULL, p_PROFILE_ID),
84 decode( p_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_APPLICATION_ID),
85 decode( p_PERZ_DDF_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_PERZ_DDF_CONTEXT),
86 decode( p_GUI_OBJECT_NAME, FND_API.G_MISS_CHAR, NULL, p_GUI_OBJECT_NAME),
87 decode( p_GUI_OBJECT_ID, FND_API.G_MISS_NUM, NULL, p_GUI_OBJECT_ID),
88 decode( p_DDF_VALUE, FND_API.G_MISS_CHAR, NULL, p_DDF_VALUE),
89 decode( p_DDF_VALUE_TYPE, FND_API.G_MISS_CHAR, NULL, p_DDF_VALUE_TYPE),
90 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, 1, p_OBJECT_VERSION_NUMBER),
91 FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID
92 );
93 End insert_jtf_perz_data_default;
94 -- ****************************************************************************
95
96 PROCEDURE update_jtf_perz_data_default(
97 x_PERZ_DDF_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
98 p_PROFILE_ID IN NUMBER,
99 p_APPLICATION_ID IN NUMBER,
100 p_PERZ_DDF_CONTEXT IN VARCHAR2,
101 p_GUI_OBJECT_NAME IN VARCHAR2,
102 p_GUI_OBJECT_ID IN NUMBER,
103 p_DDF_VALUE IN VARCHAR2,
104 p_DDF_VALUE_TYPE IN VARCHAR2,
105 p_OBJECT_VERSION_NUMBER IN NUMBER
106 ) IS
107
108 l_object_version_number NUMBER := p_OBJECT_VERSION_NUMBER;
109 BEGIN
110 Update JTF_PERZ_DATA_DEFAULT
111 SET
112 -- PROFILE_ID = decode( p_PROFILE_ID, FND_API.G_MISS_NUM, PROFILE_ID, p_PROFILE_ID),
113 -- APPLICATION_ID = decode( p_APPLICATION_ID, FND_API.G_MISS_NUM, APPLICATION_ID, p_APPLICATION_ID),
114 -- PERZ_DDF_CONTEXT = decode( p_PERZ_DDF_CONTEXT, FND_API.G_MISS_CHAR, PERZ_DDF_CONTEXT, p_PERZ_DDF_CONTEXT),
115 -- GUI_OBJECT_NAME = decode( p_GUI_OBJECT_NAME, FND_API.G_MISS_CHAR, GUI_OBJECT_NAME, p_GUI_OBJECT_NAME),
116 -- GUI_OBJECT_ID = decode( p_GUI_OBJECT_ID, FND_API.G_MISS_NUM, GUI_OBJECT_ID, p_GUI_OBJECT_ID),
117 PERZ_DDF_VALUE = decode( p_DDF_VALUE, FND_API.G_MISS_CHAR, PERZ_DDF_VALUE, p_DDF_VALUE),
118 PERZ_DDF_VALUE_TYPE = decode( p_DDF_VALUE_TYPE, FND_API.G_MISS_CHAR, PERZ_DDF_VALUE_TYPE, p_DDF_VALUE_TYPE),
119 OBJECT_VERSION_NUMBER = decode (p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
120 OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
121 LAST_UPDATE_DATE = SYSDATE,
122 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
123 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
124 where PERZ_DATA_DEFAULT_ID = x_PERZ_DDF_ID
125 and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
126
127 If (SQL%NOTFOUND) then
128 RAISE NO_DATA_FOUND;
129 End If;
130 END update_jtf_perz_data_default;
131 -- ****************************************************************************
132
133 PROCEDURE delete_jtf_perz_data_default(
134 p_PERZ_DDF_ID IN NUMBER)
135 IS
136 BEGIN
137 DELETE FROM JTF_PERZ_DATA_DEFAULT
138 WHERE PERZ_DATA_DEFAULT_ID = p_PERZ_DDF_ID;
139 If (SQL%NOTFOUND) then
140 RAISE NO_DATA_FOUND;
141 End If;
142 END delete_jtf_perz_data_default;
143
144 -- ***************************************************************************
145 -- ***************************************************************************
146 --
147 --Private APIs
148 -- Check_ddf()
149 -- get_ddf(
150
151
152 -- ***************************************************************************
153 -- ***************************************************************************
154
155 PROCEDURE Get_ddf(
156 x_PERZ_DDF_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
157 p_PROFILE_ID IN NUMBER,
158 p_APPLICATION_ID IN NUMBER,
159 p_PERZ_DDF_CONTEXT IN VARCHAR2,
160 p_GUI_OBJECT_NAME IN VARCHAR2,
161 p_GUI_OBJECT_ID IN NUMBER,
162 p_ddf_value IN VARCHAR2,
163 p_ddf_value_type IN VARCHAR2,
164 x_ddf_out_tbl OUT NOCOPY /* file.sql.39 change */ JTF_PERZ_DDF_PUB.DDF_OUT_TBL_TYPE,
165 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
166 )IS
167
168 l_count NUMBER := NULL;
169 l_ddf_out_rec_temp JTF_PERZ_DDF_PUB.DDF_OUT_REC_TYPE := NULL;
170
171 cursor c_get_ddf_paoc (p_profile_id NUMBER, p_application_id NUMBER,
172 p_gui_object_id NUMBER, p_perz_ddf_context VARCHAR2) is
173 select perz_data_default_id, perz_ddf_context,
174 profile_id, application_id, gui_object_name,
175 gui_object_id, PERZ_ddf_value, PERZ_ddf_value_type
176 from JTF_PERZ_DATA_DEFAULT
177 where
178 PROFILE_ID = p_PROFILE_ID
179 and APPLICATION_ID = p_APPLICATION_ID
180 and GUI_OBJECT_ID = p_GUI_OBJECT_ID
181 and PERZ_DDF_CONTEXT = p_PERZ_DDF_CONTEXT;
182
183 cursor c_get_ddf_pao (p_profile_id NUMBER, p_application_id NUMBER,
184 p_gui_object_id NUMBER) is
185 select perz_data_default_id, perz_ddf_context,
186 profile_id, application_id, gui_object_name,
187 gui_object_id, PERZ_ddf_value, PERZ_ddf_value_type
188 from JTF_PERZ_DATA_DEFAULT
189 where
190 PROFILE_ID = p_PROFILE_ID
191 and APPLICATION_ID = p_APPLICATION_ID
192 and GUI_OBJECT_ID = p_GUI_OBJECT_ID;
193
194 cursor c_get_ddf_panc (p_profile_id NUMBER, p_application_id NUMBER,
195 p_gui_object_name VARCHAR2, p_perz_ddf_context VARCHAR2) is
196 select perz_data_default_id, perz_ddf_context,
197 profile_id, application_id, gui_object_name,
198 gui_object_id, PERZ_ddf_value, PERZ_ddf_value_type
199
200 from JTF_PERZ_DATA_DEFAULT
201 where
202 PROFILE_ID = p_PROFILE_ID
203 and APPLICATION_ID = p_APPLICATION_ID
204 and GUI_OBJECT_NAME = p_GUI_OBJECT_NAME
205 and PERZ_DDF_CONTEXT = p_PERZ_DDF_CONTEXT;
206
207 cursor c_get_ddf_pan (p_profile_id NUMBER, p_application_id NUMBER,
208 p_gui_object_name VARCHAR2) is
209 select perz_data_default_id, perz_ddf_context,
210 profile_id, application_id, gui_object_name,
211 gui_object_id, PERZ_ddf_value, PERZ_ddf_value_type
212 from JTF_PERZ_DATA_DEFAULT
213 where
214 PROFILE_ID = p_PROFILE_ID
215 and APPLICATION_ID = p_APPLICATION_ID
216 and GUI_OBJECT_NAME = p_GUI_OBJECT_NAME;
217
218 BEGIN
219
220 if ((( p_PROFILE_ID is NOT NULL) AND
221 ( p_PROFILE_ID <> FND_API.G_MISS_NUM)) AND
222 ((p_APPLICATION_ID is NOT NULL) AND
223 ( p_APPLICATION_ID <> FND_API.G_MISS_NUM))) then
224
225 if ((p_GUI_OBJECT_ID is NOT NULL) AND
226 (p_GUI_OBJECT_ID <>FND_API.G_MISS_NUM)) then
227 if (((p_PERZ_DDF_CONTEXT is NOT NULL) AND
228 (p_PERZ_DDF_CONTEXT <> FND_API.G_MISS_CHAR))) then
229
230 l_ddf_out_rec_temp := NULL;
231 l_count := 1;
232 open c_get_ddf_paoc (p_profile_id , p_application_id ,
233 p_gui_object_id , p_perz_ddf_context ) ;
234 LOOP
235 FETCH c_get_ddf_paoc into
236 l_ddf_out_rec_temp.perz_ddf_id,
237 l_ddf_out_rec_temp.perz_ddf_context,
238 l_ddf_out_rec_temp.profile_id,
239 l_ddf_out_rec_temp.application_id,
240 l_ddf_out_rec_temp.gui_object_name,
241 l_ddf_out_rec_temp.gui_object_id,
242 l_ddf_out_rec_temp.ddf_value,
243 l_ddf_out_rec_temp.ddf_value_type;
244 EXIT WHEN c_get_ddf_paoc%NOTFOUND;
245 IF ( c_get_ddf_paoc%FOUND = TRUE ) THEN
246 x_ddf_out_tbl(l_count).perz_ddf_id := l_ddf_out_rec_temp.perz_ddf_id;
247 x_ddf_out_tbl(l_count).perz_ddf_context := l_ddf_out_rec_temp.perz_ddf_context;
248 x_ddf_out_tbl(l_count).profile_id := l_ddf_out_rec_temp.profile_id;
249 x_ddf_out_tbl(l_count).application_id := l_ddf_out_rec_temp.application_id;
250 x_ddf_out_tbl(l_count).gui_object_name := l_ddf_out_rec_temp.gui_object_name;
251 x_ddf_out_tbl(l_count).gui_object_id := l_ddf_out_rec_temp.gui_object_id;
252 x_ddf_out_tbl(l_count).ddf_value := l_ddf_out_rec_temp.ddf_value;
253 x_ddf_out_tbl(l_count).ddf_value_type := l_ddf_out_rec_temp.ddf_value_type;
254 l_count := l_count +1;
255 END IF;
256 END LOOP;
257 close c_get_ddf_paoc;
258 x_return_status := FND_API.G_TRUE;
259 else
260
261 l_ddf_out_rec_temp := NULL;
262 l_count := 1;
263 open c_get_ddf_pao (p_profile_id, p_application_id,
264 p_gui_object_id ) ;
265 LOOP
266 FETCH c_get_ddf_pao into
267 l_ddf_out_rec_temp.perz_ddf_id,
268 l_ddf_out_rec_temp.perz_ddf_context,
269 l_ddf_out_rec_temp.profile_id,
270 l_ddf_out_rec_temp.application_id,
271 l_ddf_out_rec_temp.gui_object_name,
272 l_ddf_out_rec_temp.gui_object_id,
273 l_ddf_out_rec_temp.ddf_value,
274 l_ddf_out_rec_temp.ddf_value_type;
275 EXIT WHEN c_get_ddf_pao%NOTFOUND;
276 IF ( c_get_ddf_pao%FOUND = TRUE ) THEN
277 x_ddf_out_tbl(l_count).perz_ddf_id := l_ddf_out_rec_temp.perz_ddf_id;
278 x_ddf_out_tbl(l_count).perz_ddf_context := l_ddf_out_rec_temp.perz_ddf_context;
279 x_ddf_out_tbl(l_count).profile_id := l_ddf_out_rec_temp.profile_id;
280 x_ddf_out_tbl(l_count).application_id := l_ddf_out_rec_temp.application_id;
281 x_ddf_out_tbl(l_count).gui_object_name := l_ddf_out_rec_temp.gui_object_name;
282 x_ddf_out_tbl(l_count).gui_object_id := l_ddf_out_rec_temp.gui_object_id;
283 x_ddf_out_tbl(l_count).ddf_value := l_ddf_out_rec_temp.ddf_value;
284 x_ddf_out_tbl(l_count).ddf_value_type := l_ddf_out_rec_temp.ddf_value_type;
285 l_count := l_count +1;
286 END IF;
287 END LOOP;
288 close c_get_ddf_pao;
289 x_return_status := FND_API.G_TRUE;
290 end if;
291
292 elsif ((p_GUI_OBJECT_NAME is NOT NULL) AND
293 (p_GUI_OBJECT_NAME <>FND_API.G_MISS_CHAR)) then
294 if (((p_PERZ_DDF_CONTEXT is NOT NULL) AND
295 (p_PERZ_DDF_CONTEXT <> FND_API.G_MISS_CHAR))) then
296 l_count := 1;
297 l_ddf_out_rec_temp := NULL;
298 open c_get_ddf_panc (p_profile_id , p_application_id ,
299 p_gui_object_name , p_perz_ddf_context ) ;
300 LOOP
301 fetch c_get_ddf_panc into
302 l_ddf_out_rec_temp.perz_ddf_id,
303 l_ddf_out_rec_temp.perz_ddf_context,
304 l_ddf_out_rec_temp.profile_id,
305 l_ddf_out_rec_temp.application_id,
306 l_ddf_out_rec_temp.gui_object_name,
307 l_ddf_out_rec_temp.gui_object_id,
308 l_ddf_out_rec_temp.ddf_value,
309 l_ddf_out_rec_temp.ddf_value_type;
310 EXIT WHEN c_get_ddf_panc%NOTFOUND;
311 IF ( c_get_ddf_panc%FOUND = TRUE ) THEN
312 x_ddf_out_tbl(l_count).perz_ddf_id := l_ddf_out_rec_temp.perz_ddf_id;
313 x_ddf_out_tbl(l_count).perz_ddf_context := l_ddf_out_rec_temp.perz_ddf_context;
314 x_ddf_out_tbl(l_count).profile_id := l_ddf_out_rec_temp.profile_id;
315 x_ddf_out_tbl(l_count).application_id := l_ddf_out_rec_temp.application_id;
316 x_ddf_out_tbl(l_count).gui_object_name := l_ddf_out_rec_temp.gui_object_name;
317 x_ddf_out_tbl(l_count).gui_object_id := l_ddf_out_rec_temp.gui_object_id;
318 x_ddf_out_tbl(l_count).ddf_value := l_ddf_out_rec_temp.ddf_value;
319 x_ddf_out_tbl(l_count).ddf_value_type := l_ddf_out_rec_temp.ddf_value_type;
320 l_count := l_count +1;
321 END IF;
322 END LOOP;
323 close c_get_ddf_panc ;
324 x_return_status := FND_API.G_TRUE;
325 else
326 l_count := 1;
327 open c_get_ddf_pan (p_profile_id ,
328 p_application_id ,
329 p_gui_object_name ) ;
330 LOOP
331 FETCH c_get_ddf_pan into
332 l_ddf_out_rec_temp.perz_ddf_id,
333 l_ddf_out_rec_temp.perz_ddf_context,
334 l_ddf_out_rec_temp.profile_id,
335 l_ddf_out_rec_temp.application_id,
336 l_ddf_out_rec_temp.gui_object_name,
337 l_ddf_out_rec_temp.gui_object_id,
338 l_ddf_out_rec_temp.ddf_value,
339 l_ddf_out_rec_temp.ddf_value_type;
340 EXIT WHEN c_get_ddf_pan%NOTFOUND;
341 IF ( c_get_ddf_pan%FOUND = TRUE ) THEN
342 x_ddf_out_tbl(l_count).perz_ddf_id := l_ddf_out_rec_temp.perz_ddf_id;
343 x_ddf_out_tbl(l_count).perz_ddf_context := l_ddf_out_rec_temp.perz_ddf_context;
344 x_ddf_out_tbl(l_count).profile_id := l_ddf_out_rec_temp.profile_id;
345 x_ddf_out_tbl(l_count).application_id := l_ddf_out_rec_temp.application_id;
346 x_ddf_out_tbl(l_count).gui_object_name := l_ddf_out_rec_temp.gui_object_name;
347 x_ddf_out_tbl(l_count).gui_object_id := l_ddf_out_rec_temp.gui_object_id;
348 x_ddf_out_tbl(l_count).ddf_value := l_ddf_out_rec_temp.ddf_value;
349 x_ddf_out_tbl(l_count).ddf_value_type := l_ddf_out_rec_temp.ddf_value_type;
350 l_count := l_count +1;
351 END IF;
352 END LOOP;
353 close c_get_ddf_pan;
354 x_return_status := FND_API.G_TRUE;
355 end if;
356 else
357 x_return_status := FND_API.G_FALSE;
358 end if;
359 else
360 x_return_status := FND_API.G_FALSE;
361 end if;
362
363
364 END Get_ddf;
365
366 -- ***************************************************************************
367
368 PROCEDURE Check_ddf(
369 x_PERZ_DDF_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
370 p_PROFILE_ID IN NUMBER,
371 p_APPLICATION_ID IN NUMBER,
372 p_PERZ_DDF_CONTEXT IN VARCHAR2,
373 p_GUI_OBJECT_NAME IN VARCHAR2,
374 p_GUI_OBJECT_ID IN NUMBER,
375 x_OBJECT_VERSION_NUMBER OUT NOCOPY /* file.sql.39 change */ NUMBER
376 ) IS
377
378 l_perz_ddf_id NUMBER := NULL;
379
380 -- Usage Note:
381 -- If this procedure is supplied with arguments set which
382 -- cannot uniquely identify a DDF, then this procedre raises error
383 -- Hence, to correctly use this procedure supply any one of the
384 -- following combinations:
385 --
386 -- 1: x_perz_ddf_id
387 --
388 -- 2:
389 -- p_PROFILE_ID
390 -- p_APPLICATION_ID
391 -- p_PERZ_DDF_CONTEXT
392 -- p_GUI_OBJECT_NAME
393 --
394 -- 3:
395 -- p_PROFILE_ID
396 -- p_APPLICATION_ID
397 -- p_PERZ_DDF_CONTEXT
398 -- p_GUI_OBJECT_ID
399 --
400
401 BEGIN
402
403 if ((x_PERZ_DDF_ID is NOT NULL) AND
404 (x_PERZ_DDF_ID <> FND_API.G_MISS_NUM)) then
405
406 select PERZ_DATA_DEFAULT_ID, OBJECT_VERSION_NUMBER
407 into l_perz_ddf_id, x_OBJECT_VERSION_NUMBER
408 from JTF_PERZ_DATA_DEFAULT
409 where PERZ_DATA_DEFAULT_ID = x_PERZ_DDF_ID;
410
411 if (SQL%NOTFOUND) then
412 RAISE NO_DATA_FOUND;
413 End If;
414
415 elsif ((( p_PROFILE_ID is NOT NULL) AND
416 ( p_PROFILE_ID <> FND_API.G_MISS_NUM)) AND
417 ((p_APPLICATION_ID is NOT NULL) AND
418 ( p_APPLICATION_ID <> FND_API.G_MISS_NUM)) AND
419 ((p_PERZ_DDF_CONTEXT is NOT NULL) AND
420 ( p_PERZ_DDF_CONTEXT <> FND_API.G_MISS_CHAR))) then
421
422 if ((p_GUI_OBJECT_ID is NOT NULL) AND
423 (p_GUI_OBJECT_ID <>FND_API.G_MISS_NUM)) then
424 BEGIN
425 select PERZ_DATA_DEFAULT_ID, OBJECT_VERSION_NUMBER
426 into l_perz_ddf_id, x_OBJECT_VERSION_NUMBER
427 from JTF_PERZ_DATA_DEFAULT
428 where PROFILE_ID = p_PROFILE_ID
429 and APPLICATION_ID = p_APPLICATION_ID
430 and PERZ_DDF_CONTEXT = p_PERZ_DDF_CONTEXT
431 and GUI_OBJECT_ID = p_GUI_OBJECT_ID;
432
433 x_PERZ_DDF_ID := l_perz_ddf_id;
434
435 EXCEPTION
436 WHEN NO_DATA_FOUND THEN
437 x_PERZ_DDF_ID := NULL;
438
439 WHEN OTHERS THEN
440 x_PERZ_DDF_ID := NULL;
441
442 END;
443 else
444 if ((p_GUI_OBJECT_NAME is NOT NULL) AND
445 (p_GUI_OBJECT_NAME <> FND_API.G_MISS_CHAR)) THEN
446 BEGIN
447 select PERZ_DATA_DEFAULT_ID, OBJECT_VERSION_NUMBER
448 into l_perz_ddf_id, x_OBJECT_VERSION_NUMBER
449 from JTF_PERZ_DATA_DEFAULT
450 where PROFILE_ID = p_PROFILE_ID
451 and APPLICATION_ID = p_APPLICATION_ID
452 and PERZ_DDF_CONTEXT = p_PERZ_DDF_CONTEXT
453 and GUI_OBJECT_NAME = p_GUI_OBJECT_NAME;
454
455 x_PERZ_DDF_ID := l_perz_ddf_id;
456
457 EXCEPTION
458 WHEN NO_DATA_FOUND THEN
459 x_PERZ_DDF_ID := NULL;
460
461 WHEN OTHERS THEN
462 x_PERZ_DDF_ID := NULL;
463
464 END;
465
466 else
467 RAISE FND_API.G_EXC_ERROR;
468 End If;
469 End If;
470
471 else
472 RAISE FND_API.G_EXC_ERROR;
473
474 end if;
475
476 END Check_ddf;
477
478 -- ***************************************************************************
479 -- Start of Comments
480 --
481 -- API name : create_data_default
482 -- Type : Public
483 -- Function : Create a data default for a given profile and application id.
484 --
485 -- Parameters :
486 -- IN :
487 -- p_api_version_number IN NUMBER Required
488 -- p_init_msg_list IN VARCHAR2 Optional
489 -- p_commit IN VARCHAR Optional
490 --
491 -- p_application_id IN NUMBER Required
492
493 -- p_profile_id IN NUMBER Required
494 -- p_profile_name IN VARCHAR2 Optional
495 --
496 -- p_perz_ddf_id IN NUMBER Optional
497 -- p_perz_ddf_context IN VARCHAR2 Required
498 -- p_gui_object_name IN VARCHAR2 Required
499 -- p_gui_object_id IN NUMBER Optional
500 -- p_ddf_value IN VARCHAR2 Required
501 -- p_ddf_value_type IN VARCHAR2 Required
502 --
503 -- OUT :
504 -- x_perz_ddf_id OUT NUMBER
505 -- x_return_status OUT VARCHAR2
506 -- x_msg_count OUT NUMBER
507 -- x_msg_data OUT VARCHAR2
508 --
509 --
510 -- Version : Current version 1.0
511 -- Initial version 1.0
512 --
513 -- Notes:
514 -- This API is used to create a Data Default. A data default is
515 -- associated with a GUI object, default value and type.
516 -- The same GUI object can have different values in different
517 -- (profile_id, application_id, ddf_context) combination.
518 --
519 -- Also, for the same profile, applicatin and contgext,
520 -- different data defaults can be associated with the same
521 -- GUI object, if one GUI object has GUI Object ID and other
522 -- does not. If this is not allowed, this can be fixed by
523 -- creating the unique keyindex on profile id, application id,
524 -- gui object name
525 --
526 --
527 --
528
529 -- *****************************************************************************
530
531 PROCEDURE create_data_default
532 (
533 p_api_version_number IN NUMBER,
534 p_init_msg_list IN VARCHAR2,
535 p_commit IN VARCHAR,
536
537 p_application_id IN NUMBER,
538
539 p_profile_id IN NUMBER,
540 p_profile_name IN VARCHAR2,
541
542 p_perz_ddf_id IN NUMBER,
543 p_perz_ddf_context IN VARCHAR2,
544
545 p_gui_object_name IN VARCHAR2,
546 p_gui_object_id IN NUMBER,
547 p_ddf_value IN VARCHAR2,
548 p_ddf_value_type IN VARCHAR2,
549
550 x_perz_ddf_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
551
552 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
553 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
554 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
555 )IS
556 -- ******* Create_data_default Local Variables ********
557 -- Following variables are needed to adhere to standards
558 l_api_version_number NUMBER := p_api_version_number;
559 l_api_name CONSTANT VARCHAR2(30) := 'Create_data_default';
560 -- Following variables are needed for implementation
561 l_profile_id NUMBER := NULL;
562 l_profile_name VARCHAR2(60) := NULL;
563 l_return_status VARCHAR2(240);
564 l_perz_ddf_id NUMBER := p_perz_ddf_id;
565 l_object_version_number NUMBER := NULL;
566
567 BEGIN
568
569 -- ******* create_data_default execution plan ********
570 --1. Check if the profile exists
571 -- check_duplicate_profiles()
572 -- If not, raise an error and exit
573 --2. call the insert table handler()
574
575 -- ******* create_data_default Standard Begins ********
576
577 -- Standard Start of API savepoint
578 SAVEPOINT CREATE_PERZ_DDF_PVT;
579
580 -- Standard call to check for call compatibility.
581 -- IF NOT FND_API.Compatible_API_Call (
582 -- l_api_version_number,
583 -- p_api_version_number,
584 -- l_api_name,
585 -- G_PKG_NAME)
586 -- THEN
587 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
588 -- END IF;
589
590 -- Initialize message list if p_init_msg_list is set to TRUE.
591 IF FND_API.to_Boolean( p_init_msg_list )
592 THEN
593 FND_MSG_PUB.initialize;
594 END IF;
595
596 -- Initialize API return status to success
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598
599 -- Create_data_default implementation
600
601 --1. Check if the profile exists. This check will be done
602 -- irrespective of whether the user had provided the id or not.
603 -- Beacuse, the mobile users must have created the profile on the
604 -- client and not on the server.
605
606 l_profile_id := p_profile_id;
607 l_profile_name := p_profile_name;
608 l_return_status := FND_API.G_TRUE;
609
610 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
611 l_profile_name,
612 l_return_status,
613 l_profile_id
614 );
615
616
617 -- If profile does not exists, raise an error and exit
618
619 if (l_return_status = FND_API.G_FALSE) then
620 RAISE FND_API.G_EXC_ERROR;
621 end if;
622
623 --2 Calling the insert table handler
624 l_object_version_number := 1;
625 insert_jtf_perz_data_default(
626 l_perz_ddf_id,
627 l_profile_id,
628 p_APPLICATION_ID,
629 p_PERZ_DDF_CONTEXT,
630 p_GUI_OBJECT_NAME,
631 p_GUI_OBJECT_ID,
632 p_DDF_VALUE,
633 p_DDF_VALUE_TYPE,
634 l_object_version_number
635 );
636
637 if (( l_perz_ddf_id is NULL)
638 OR (l_perz_ddf_id = FND_API.G_MISS_NUM)) then
639 RAISE FND_API.G_EXC_ERROR;
640 else
641 x_perz_ddf_id := l_perz_ddf_id;
642 end if;
643
644 -- ******** Standard Ends ***********
645 --
646 -- End of API body.
647 --
648 -- 7. Commit the whole thing
649
650 -- Standard check of p_commit.
651 IF FND_API.To_Boolean ( p_commit )
652 THEN
653 COMMIT WORK;
654 END IF;
655
656 -- Debug Message
657 -- Standard call to get message count and if count is 1, get message info.
658 FND_MSG_PUB.Count_And_Get
659 (
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 );
663
664
665 EXCEPTION
666
667 WHEN FND_API.G_EXC_ERROR THEN
668
669 ROLLBACK TO CREATE_PERZ_DDF_PVT;
670 x_return_status := FND_API.G_RET_STS_ERROR ;
671
672 FND_MSG_PUB.Count_And_Get
673 ( p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676
677 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
678 THEN
679 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
680 END IF;
681
682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
683
684 ROLLBACK TO CREATE_PERZ_DDF_PVT;
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
686
687 FND_MSG_PUB.Count_And_Get
688 ( p_count => x_msg_count,
689 p_data => x_msg_data
690 );
691
692 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
693 THEN
694 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
695 END IF;
696
697 WHEN OTHERS THEN
698
699 ROLLBACK TO CREATE_PERZ_DDF_PVT;
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
701
702 FND_MSG_PUB.Count_And_Get
703 ( p_count => x_msg_count,
704 p_data => x_msg_data
705 );
706
707 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
708 THEN
709 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
710 END IF;
711
712 END create_data_default;
713
714 -- *****************************************************************************
715
716
717 -- Start of Comments
718 --
719 -- API name : update_data_default
720 -- Type : Public
721 -- Function : Update data default object in the Framework.
722 --
723 -- Parameters :
724 -- IN :
725 -- p_api_version_number IN NUMBER Required
726 -- p_init_msg_list IN VARCHAR2 Optional
727 -- p_commit IN VARCHAR Optional
728 --
729 -- p_application_id IN NUMBER Optional
730
731 -- p_profile_id IN NUMBER Optional
732 -- p_profile_name IN VARCHAR2 Optional
733 --
734 -- p_perz_ddf_id IN NUMBER Optional
735 -- p_perz_ddf_context IN VARCHAR2 Required
736 -- p_gui_object_name IN VARCHAR2 Required
737 -- p_gui_object_id IN NUMBER Optional
738 -- p_ddf_value IN VARCHAR2 Required
739 -- p_ddf_value_type IN VARCHAR2 Required
740 --
741 -- OUT :
742 -- x_perz_ddf_id OUT NUMBER
743 -- x_return_status OUT VARCHAR2
744 -- x_msg_count OUT NUMBER
745 -- x_msg_data OUT VARCHAR2
746 --
747 --
748 -- Version : Current version 1.0
749 -- Initial version 1.0
750 --
751 -- Notes:
752 -- For a given data default object, this API only updates
753 -- GUI object name, id and associated value and type fields only.
754 --
755 --
756 -- *****************************************************************************
757 --
758
759 PROCEDURE update_data_default
760 (
761 p_api_version_number IN NUMBER,
762 p_init_msg_list IN VARCHAR2,
763 p_commit IN VARCHAR,
764
765 p_application_id IN NUMBER,
766
767 p_profile_id IN NUMBER,
768 p_profile_name IN VARCHAR2,
769
770 p_perz_ddf_id IN NUMBER,
771 p_perz_ddf_context IN VARCHAR2,
772
773 p_gui_object_name IN VARCHAR2,
774 p_gui_object_id IN NUMBER,
775 p_ddf_value IN VARCHAR2,
776 p_ddf_value_type IN VARCHAR2,
777
778 x_perz_ddf_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
779
780 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
781 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
782 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
783 )IS
784 -- ******* Update_data_default Local Variables ********
785 -- Following variables are needed to adhere to standards
786 l_api_version_number NUMBER := p_api_version_number;
787 l_api_name CONSTANT VARCHAR2(30) := 'Update_data_default';
788 -- Following variables are needed for implementation
789 l_profile_id NUMBER := NULL;
790 l_profile_name VARCHAR2(60) := NULL;
791 l_return_status VARCHAR2(240);
792 l_perz_ddf_id NUMBER := p_perz_ddf_id;
793 l_object_version_number NUMBER := NULL;
794
795 BEGIN
796
797 -- ******* Update_data_default execution plan ********
798 --1. Check if the profile exists
799 -- check_duplicate_profiles()
800 -- If not, raise an error and exit
801 --2. Check if DDF exists
802 --3. call the update table handler()
803
804 -- ******* update_data_default Standard Begins ********
805
806 -- Standard Start of API savepoint
807 SAVEPOINT UPDATE_PERZ_DDF_PVT;
808
809 -- Standard call to check for call compatibility.
810 -- IF NOT FND_API.Compatible_API_Call (
811 -- l_api_version_number,
812 -- p_api_version_number,
813 -- l_api_name,
814 -- G_PKG_NAME)
815 -- THEN
816 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
817 -- END IF;
818
819 -- Initialize message list if p_init_msg_list is set to TRUE.
820 IF FND_API.to_Boolean( p_init_msg_list )
821 THEN
822 FND_MSG_PUB.initialize;
823 END IF;
824
825 -- Initialize API return status to success
826 x_return_status := FND_API.G_RET_STS_SUCCESS;
827
828 -- update_data_default implementation
829 --1. Check if the profile exists. This check will be done
830 -- irrespective of whether the user had provided the id or not.
831 -- Beacuse, the mobile users must have created the profile on the
832 -- client and not on the server.
833
834 l_profile_id := p_profile_id;
835 l_profile_name := p_profile_name;
836 l_return_status := FND_API.G_TRUE;
837
838 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
839 l_profile_name,
840 l_return_status,
841 l_profile_id
842 );
843
844
845 -- If profile does not exists, raise an error and exit
846
847 if (l_return_status = FND_API.G_FALSE) then
848 RAISE FND_API.G_EXC_ERROR;
849 end if;
850
851 --2. Check if DDF exists in the DB.
852 -- This must be done to obtain the perz_ddf_id. Other wise,
853 -- an inline update statement must be written for each of the
854 -- case (here cases being, what inputs have been provided)
855
856 Check_ddf(
857 l_perz_ddf_id,
858 l_profile_id,
859 p_APPLICATION_ID,
860 p_PERZ_DDF_CONTEXT,
861 p_GUI_OBJECT_NAME,
862 p_GUI_OBJECT_ID,
863 l_OBJECT_VERSION_NUMBER
864 );
865
866 if (( l_perz_ddf_id is NULL)
867 OR (l_perz_ddf_id = FND_API.G_MISS_NUM)) then
868 RAISE FND_API.G_EXC_ERROR;
869 else
870
871 --3. Call the update handler
872
873 update_jtf_perz_data_default(
874 l_perz_ddf_id,
875 l_profile_id,
876 p_APPLICATION_ID,
877 p_PERZ_DDF_CONTEXT,
878 p_GUI_OBJECT_NAME,
879 p_GUI_OBJECT_ID,
880 p_DDF_VALUE,
881 p_DDF_VALUE_TYPE,
882 l_OBJECT_VERSION_NUMBER
883 );
884
885 if (( l_perz_ddf_id is NULL)
886 OR (l_perz_ddf_id = FND_API.G_MISS_NUM)) then
887 RAISE FND_API.G_EXC_ERROR;
888 else
889 x_perz_ddf_id := l_perz_ddf_id;
890 end if;
891
892 end if;
893
894 -- ******** Standard Ends ***********
895 --
896 -- End of API body.
897 --
898 -- 7. Commit the whole thing
899
900 -- Standard check of p_commit.
901 IF FND_API.To_Boolean ( p_commit )
902 THEN
903 COMMIT WORK;
904 END IF;
905
906 -- Debug Message
907 -- Standard call to get message count and if count is 1, get message info.
908 FND_MSG_PUB.Count_And_Get
909 (
910 p_count => x_msg_count,
911 p_data => x_msg_data
912 );
913
914
915 EXCEPTION
916
917 WHEN FND_API.G_EXC_ERROR THEN
918
919 ROLLBACK TO UPDATE_PERZ_DDF_PVT;
920 x_return_status := FND_API.G_RET_STS_ERROR ;
921
922 FND_MSG_PUB.Count_And_Get
923 ( p_count => x_msg_count,
924 p_data => x_msg_data
925 );
926
927 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
928 THEN
929 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
930 END IF;
931
932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
933
934 ROLLBACK TO UPDATE_PERZ_DDF_PVT;
935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
936
937 FND_MSG_PUB.Count_And_Get
938 ( p_count => x_msg_count,
939 p_data => x_msg_data
940 );
941
942 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
943 THEN
944 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
945 END IF;
946
947 WHEN OTHERS THEN
948
949 ROLLBACK TO UPDATE_PERZ_DDF_PVT;
950 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
951
952 FND_MSG_PUB.Count_And_Get
953 ( p_count => x_msg_count,
954 p_data => x_msg_data
955 );
956
957 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
958 THEN
959 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
960 END IF;
961
962
963 END update_data_default;
964
965 -- *****************************************************************************
966 -- Start of Comments
967 --
968 -- API name : save_data_default
969 -- Type : Public
970 -- Function : Create or update if exists, a personalized data default
971 --
972 -- Parameters :
973 -- IN :
974 -- p_api_version_number IN NUMBER Required
975 -- p_init_msg_list IN VARCHAR2 Optional
976 -- p_commit IN VARCHAR Optional
977 --
978 -- p_application_id IN NUMBER Required
979
980 -- p_profile_id IN NUMBER Required
981 -- p_profile_name IN VARCHAR2 Optional
982 -- p_profile_type IN VARCHAR2 Optional
983 -- p_profile_attrib IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE Optional
984 --
985 -- p_perz_ddf_id IN NUMBER Optional
986 -- p_perz_ddf_context IN VARCHAR2 Required
987 -- p_gui_object_name IN VARCHAR2 Required
988 -- p_gui_object_id IN NUMBER Optional
989 -- p_ddf_value IN VARCHAR2 Required
990 -- p_ddf_value_type IN VARCHAR2 Required
991 --
992 -- OUT :
993 -- x_perz_ddf_id OUT NUMBER
994 -- x_return_status OUT VARCHAR2
995 -- x_msg_count OUT NUMBER
996 -- x_msg_data OUT VARCHAR2
997 --
998 --
999 -- Version : Current version 1.0
1000 -- Initial version 1.0
1001 --
1002 -- Notes:
1003 -- Data Default is used a single value tied to any given GUI object
1004 -- This Association can be identified by ddf_name. The same GUI object
1005 -- can have different values in different (profile_id, application_id)
1006 -- combination.
1007 -- The perz_ddf_context is used to store under what context the GUI
1008 -- object will have the assigned value for any given profile_id
1009 -- application id combination
1010 --
1011 --
1012 -- *****************************************************************************
1013 --
1014 PROCEDURE save_data_default
1015 ( p_api_version_number IN NUMBER,
1016 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1017 p_commit IN VARCHAR := FND_API.G_FALSE,
1018
1019 p_application_id IN NUMBER,
1020
1021 p_profile_id IN NUMBER,
1022 p_profile_name IN VARCHAR2,
1023 p_profile_type IN VARCHAR2,
1024 p_profile_attrib IN JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
1025 := JTF_PERZ_PROFILE_PUB.G_MISS_PROFILE_ATTRIB_TBL,
1026
1027 p_perz_ddf_id IN NUMBER ,
1028 p_perz_ddf_context IN VARCHAR2 ,
1029
1030 p_gui_object_name IN VARCHAR2 ,
1031 p_gui_object_id IN NUMBER ,
1032 p_ddf_value IN VARCHAR2 ,
1033 p_ddf_value_type IN VARCHAR2 ,
1034
1035 x_perz_ddf_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1036
1037 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1038 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1039 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1040 )IS
1041
1042
1043 -- ******* save_data_default Local Variables ********
1044 -- Following variables are needed to adhere to standards
1045 l_api_version_number NUMBER := p_api_version_number;
1046 l_api_name CONSTANT VARCHAR2(30) := 'save_data_default';
1047 -- Following variables are needed for implementation
1048 l_profile_id NUMBER := NULL;
1049 l_profile_name VARCHAR2(60) := p_profile_name;
1050 l_profile_type VARCHAR2(30) := p_profile_type;
1051 l_profile_attrib JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE
1052 := p_profile_attrib;
1053 l_perz_ddf_id NUMBER := p_perz_ddf_id;
1054 l_return_status VARCHAR2(240) := FND_API.G_TRUE;
1055 l_commit VARCHAR2(1) := FND_API.G_TRUE;
1056 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1057 l_object_version_number NUMBER := NULL;
1058
1059 BEGIN
1060
1061 -- ******* save_data_default execution plan ********
1062 --1. Check if the profile exists
1063 -- check_duplicate_profiles()
1064 -- If not, call create_profile()
1065 --2. Check if DDF exists
1066 -- If not, call create_data_default()
1067 -- If yes, update_data_default()
1068
1069 -- ******* save_data_default Standard Begins ********
1070
1071 -- Standard Start of API savepoint
1072 SAVEPOINT SAVE_PERZ_DDF_PVT;
1073
1074 -- Standard call to check for call compatibility.
1075 -- IF NOT FND_API.Compatible_API_Call (
1076 -- l_api_version_number,
1077 -- p_api_version_number,
1078 -- l_api_name,
1079 -- G_PKG_NAME)
1080 -- THEN
1081 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082 -- END IF;
1083
1084 -- Initialize message list if p_init_msg_list is set to TRUE.
1085 IF FND_API.to_Boolean( p_init_msg_list )
1086 THEN
1087 FND_MSG_PUB.initialize;
1088 END IF;
1089
1090 -- Initialize API return status to success
1091 x_return_status := FND_API.G_RET_STS_SUCCESS;
1092
1093 -- save_data_default implementation
1094 --1. Check if the profile exists. This check will be done
1095 -- irrespective of whether the user had provided the id or not.
1096 -- Beacuse, the mobile users must have created the profile on the
1097 -- client and not on the server.
1098
1099
1100 l_profile_id := p_profile_id;
1101 l_profile_name := p_profile_name;
1102 l_profile_type := p_profile_type;
1103 l_return_status := FND_API.G_TRUE;
1104
1105 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
1106 l_profile_name,
1107 l_return_status,
1108 l_profile_id
1109 );
1110
1111
1112 -- If profile does not exists, call create_profile()
1113
1114 if (l_return_status = FND_API.G_FALSE) then
1115
1116
1117 l_return_status := FND_API.G_RET_STS_SUCCESS;
1118 l_commit := FND_API.G_FALSE;
1119 l_profile_id := p_profile_id;
1120
1121 JTF_PERZ_PROFILE_PVT.Create_Profile(
1122 p_api_version_number => l_api_version_number,
1123 p_init_msg_list => l_init_msg_list,
1124 p_commit => l_commit,
1125 p_profile_id => l_profile_id,
1126 p_profile_name => l_profile_name,
1127 p_profile_type => l_profile_type,
1128 p_profile_attrib_tbl => l_profile_attrib,
1129 x_profile_name => l_profile_name,
1130 x_profile_id => l_profile_id,
1131 x_return_status => l_return_status,
1132 x_msg_count => x_msg_count,
1133 x_msg_data => x_msg_data
1134 );
1135 --dbms_output.put_line('profile return status '||l_return_status);
1136 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1137
1138 RAISE FND_API.G_EXC_ERROR;
1139 end if;
1140 end if;
1141
1142
1143
1144 --2. Check if DDF exists in the DB.
1145 -- This must be done to obtain the perz_ddf_id. Other wise,
1146 -- an inline update statement must be written for each of the
1147 -- case (here cases being, what inputs have been provided)
1148
1149 l_perz_ddf_id := p_perz_ddf_id;
1150 Check_ddf(
1151 l_perz_ddf_id,
1152 l_profile_id,
1153 p_APPLICATION_ID,
1154 p_PERZ_DDF_CONTEXT,
1155 p_GUI_OBJECT_NAME,
1156 p_GUI_OBJECT_ID,
1157 l_OBJECT_VERSION_NUMBER
1158 );
1159
1160 if (( l_perz_ddf_id is NULL)
1161 OR (l_perz_ddf_id = FND_API.G_MISS_NUM)) then
1162
1163 --3. If DDF does not exist, create it
1164
1165 l_return_status := FND_API.G_RET_STS_SUCCESS;
1166 l_commit := FND_API.G_FALSE;
1167 l_profile_name := NULL;
1168
1169 create_data_default
1170 (
1171 l_api_version_number,
1172 l_init_msg_list ,
1173 l_commit ,
1174 p_application_id,
1175 l_profile_id ,
1176 l_profile_name ,
1177 l_perz_ddf_id ,
1178 p_perz_ddf_context,
1179 p_gui_object_name,
1180 p_gui_object_id ,
1181 p_ddf_value ,
1182 p_ddf_value_type,
1183 x_perz_ddf_id ,
1184 x_return_status ,
1185 x_msg_count ,
1186 x_msg_data
1187 );
1188
1189 else
1190
1191 --3. If DDF exists, update it
1192
1193 l_return_status := FND_API.G_RET_STS_SUCCESS;
1194 l_commit := FND_API.G_FALSE;
1195 l_profile_name := NULL;
1196
1197 update_data_default
1198 (
1199 l_api_version_number,
1200 l_init_msg_list ,
1201 l_commit ,
1202 p_application_id,
1203 l_profile_id ,
1204 l_profile_name ,
1205 l_perz_ddf_id ,
1206 p_perz_ddf_context,
1207 p_gui_object_name,
1208 p_gui_object_id ,
1209 p_ddf_value ,
1210 p_ddf_value_type,
1211 x_perz_ddf_id ,
1212 x_return_status ,
1213 x_msg_count ,
1214 x_msg_data
1215 );
1216 end if ;
1217 -- ******** Standard Ends ***********
1218 --
1219 -- End of API body.
1220 --
1221 -- 7. Commit the whole thing
1222
1223 -- Standard check of p_commit.
1224 IF FND_API.To_Boolean ( p_commit )
1225 THEN
1226 COMMIT WORK;
1227 END IF;
1228
1229 -- Debug Message
1230 -- Standard call to get message count and if count is 1, get message info.
1231 FND_MSG_PUB.Count_And_Get
1232 (
1233 p_count => x_msg_count,
1234 p_data => x_msg_data
1235 );
1236
1237
1238 EXCEPTION
1239
1240 WHEN FND_API.G_EXC_ERROR THEN
1241
1242 ROLLBACK TO SAVE_PERZ_DDF_PVT;
1243 x_return_status := FND_API.G_RET_STS_ERROR ;
1244
1245 FND_MSG_PUB.Count_And_Get
1246 ( p_count => x_msg_count,
1247 p_data => x_msg_data
1248 );
1249
1250 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1251 THEN
1252 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1253 END IF;
1254
1255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1256
1257 ROLLBACK TO SAVE_PERZ_DDF_PVT;
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1259
1260 FND_MSG_PUB.Count_And_Get
1261 ( p_count => x_msg_count,
1262 p_data => x_msg_data
1263 );
1264
1265 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1266 THEN
1267 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1268 END IF;
1269
1270 WHEN OTHERS THEN
1271
1272 ROLLBACK TO SAVE_PERZ_DDF_PVT;
1273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1274
1275 FND_MSG_PUB.Count_And_Get
1276 ( p_count => x_msg_count,
1277 p_data => x_msg_data
1278 );
1279
1280 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1281 THEN
1282 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1283 END IF;
1284
1285
1286 END save_data_default;
1287 -- *****************************************************************************
1288 -- Start of Comments
1289 --
1290 -- API name : get_data_default
1291 -- Type : Public
1292 -- Function : Get personalized data default object, and associated
1293 -- values for a given personalized data object and
1294 -- profile and app id.
1295 --
1296 -- Parameters :
1297 -- IN :
1298 -- p_api_version_number IN NUMBER Required
1299 -- p_init_msg_list IN VARCHAR2 Optional
1300 --
1301 -- p_application_id IN NUMBER Required
1302
1303 -- p_profile_id IN NUMBER Required
1304 -- p_profile_name IN VARCHAR2 Optional
1305 --
1306 -- p_perz_ddf_id IN NUMBER Optional
1307 -- p_perz_ddf_context IN VARCHAR2 Required
1308 -- p_gui_object_name IN VARCHAR2 Required
1309 -- p_gui_object_id IN NUMBER Optional
1310 -- p_ddf_value IN VARCHAR2 Required
1311 -- p_ddf_value_type IN VARCHAR2 Required
1312 --
1313 -- OUT :
1314 -- x_ddf_out_tbl OUT JTF_PERZ_DDF_PUB.DDF_OUT_TBL_TYPE
1315 -- x_return_status OUT VARCHAR2
1316 -- x_msg_count OUT NUMBER
1317 -- x_msg_data OUT VARCHAR2
1318 --
1319 --
1320 -- Version : Current version 1.0
1321 -- Initial version 1.0
1322 --
1323 -- Notes:
1324 --
1325 -- This API can be used in two ways,
1326 -- 1. To get the table of DDF by supplying profile id/name, application_id,
1327 -- gui_object_id/name and dddf_context
1328 -- (In this case out put table will have only one row) OR
1329 -- 2. To get the table of DDF by supplying profile id/name, application_id,
1330 -- gui_object_id/name .
1331 -- *****************************************************************************
1332
1333 PROCEDURE get_data_default
1334 ( p_api_version_number IN NUMBER,
1335 p_init_msg_list IN VARCHAR2,
1336
1337 p_application_id IN NUMBER,
1338
1339 p_profile_id IN NUMBER,
1340 p_profile_name IN VARCHAR2,
1341
1342 p_perz_ddf_id IN NUMBER,
1343 p_perz_ddf_context IN VARCHAR2,
1344
1345 p_gui_object_name IN VARCHAR2,
1346 p_gui_object_id IN NUMBER,
1347
1348 p_ddf_value IN VARCHAR2,
1349 p_ddf_value_type IN VARCHAR2,
1350
1351 x_ddf_out_tbl OUT NOCOPY /* file.sql.39 change */ jtf_perz_ddf_pub.DDF_OUT_TBL_TYPE,
1352 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1353 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1354 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1355 )IS
1356 -- ******* get_data_default Local Variables ********
1357 -- Following variables are needed to adhere to standards
1358 l_api_version_number NUMBER := p_api_version_number;
1359 l_api_name CONSTANT VARCHAR2(30) := 'get_data_default';
1360 -- Following variables are needed for implementation
1361 l_profile_id NUMBER := NULL;
1362 l_profile_name VARCHAR2(60) := NULL;
1363 l_return_status VARCHAR2(240);
1364 l_perz_ddf_id NUMBER := p_perz_ddf_id;
1365 l_ddf_out_tbl JTF_PERZ_DDF_PUB.DDF_OUT_TBL_TYPE
1366 := x_ddf_out_tbl;
1367
1368
1369 BEGIN
1370
1371 -- ******* get_data_default execution plan ********
1372 --1. Check if the profile exists
1373 -- check_duplicate_profiles()
1374 -- If not, raise an error and exit
1375 --2. get the ddf
1376
1377 -- ******* get_data_default Standard Begins ********
1378
1379 -- Standard call to check for call compatibility.
1380 -- IF NOT FND_API.Compatible_API_Call (
1381 -- l_api_version_number,
1382 -- p_api_version_number,
1383 -- l_api_name,
1384 -- G_PKG_NAME)
1385 -- THEN
1386 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1387 -- END IF;
1388
1389 -- Initialize message list if p_init_msg_list is set to TRUE.
1390 IF FND_API.to_Boolean( p_init_msg_list )
1391 THEN
1392 FND_MSG_PUB.initialize;
1393 END IF;
1394
1395 -- Initialize API return status to success
1396 x_return_status := FND_API.G_RET_STS_SUCCESS;
1397
1398 -- get_data_default implementation
1399
1400 --1. Check if the profile exists. This check will be done
1401 -- irrespective of whether the user had provided the id or not.
1402 -- Beacuse, the mobile users must have created the profile on the
1403 -- client and not on the server.
1404
1405 l_profile_id := p_profile_id;
1406 l_profile_name := p_profile_name;
1407 l_return_status := FND_API.G_TRUE;
1408
1409 JTF_PERZ_PROFILE_PVT.check_profile_duplicates(
1410 l_profile_name,
1411 l_return_status,
1412 l_profile_id
1413 );
1414
1415
1416 -- If profile does not exists, raise an error and exit
1417
1418 if (l_return_status = FND_API.G_FALSE) then
1419 RAISE FND_API.G_EXC_ERROR;
1420 end if;
1421
1422 -- get ddf
1423
1424 l_perz_ddf_id := p_perz_ddf_id;
1425 l_return_status := FND_API.G_TRUE;
1426 l_ddf_out_tbl := x_ddf_out_tbl;
1427
1428
1429 get_ddf(
1430 l_perz_ddf_id,
1431 l_profile_id,
1432 p_APPLICATION_ID,
1433 p_PERZ_DDF_CONTEXT,
1434 p_GUI_OBJECT_NAME,
1435 p_GUI_OBJECT_ID,
1436 p_ddf_value,
1437 p_ddf_value_type,
1438 l_ddf_out_tbl,
1439 l_return_status
1440 );
1441
1442 if (l_return_status = FND_API.G_FALSE) then
1443 RAISE FND_API.G_EXC_ERROR;
1444 end if;
1445 x_ddf_out_tbl := l_ddf_out_tbl;
1446
1447 EXCEPTION
1448
1449 WHEN FND_API.G_EXC_ERROR THEN
1450
1451 x_return_status := FND_API.G_RET_STS_ERROR ;
1452
1453 FND_MSG_PUB.Count_And_Get
1454 ( p_count => x_msg_count,
1455 p_data => x_msg_data
1456 );
1457
1458 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1459 THEN
1460 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1461 END IF;
1462
1463 END get_data_default;
1464 -- *****************************************************************************
1465
1466 -- Start of Comments
1467 --
1468 -- API name : delete_data_default
1469 -- Type : Public
1470 -- Function : Deletes a data dafault object in the framework.
1471 --
1472 -- Paramaeters :
1473 -- IN :
1474 -- p_api_version_number IN NUMBER Required
1475 -- p_init_msg_list IN VARCHAR2 Optional
1476 -- p_commit IN VARCHAR Optional
1477 --
1478 -- p_application_id IN NUMBER Required
1479 -- p_profile_id IN NUMBER Required
1480 -- p_perz_ddf_id IN NUMBER Required
1481 --
1482 -- OUT :
1483 -- x_return_status OUT VARCHAR2
1484 -- x_msg_count OUT NUMBER
1485 -- x_msg_data OUT VARCHAR2
1486 --
1487 --
1488 --
1489 -- Version : Current version 1.0
1490 -- Initial version 1.0
1491 --
1492 -- Notes:
1493 -- This API accepts only the ids - profile id, application id
1494 -- and perz_ddf_id to delete the data default object.
1495 --
1496 -- *****************************************************************************
1497
1498 PROCEDURE delete_data_default
1499 ( p_api_version_number IN NUMBER,
1500 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1501 p_commit IN VARCHAR := FND_API.G_FALSE,
1502
1503 p_application_id IN NUMBER,
1504 p_profile_id IN NUMBER,
1505 p_perz_ddf_id IN NUMBER,
1506
1507 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1508 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1509 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1510 )IS
1511 -- ******* Delete_data_default Local Variables ********
1512 -- Following variables are needed to adhere to standards
1513 l_api_version_number NUMBER := p_api_version_number;
1514 l_api_name CONSTANT VARCHAR2(30) := 'Delete_data_default';
1515 -- Following variables are needed for implementation
1516
1517 BEGIN
1518
1519 -- ******* delete_data_default Standard Begins ********
1520
1521 -- Standard Start of API savepoint
1522 SAVEPOINT DELETE_PERZ_DDF_PVT;
1523
1524 -- Standard call to check for call compatibility.
1525 -- IF NOT FND_API.Compatible_API_Call (
1526 -- l_api_version_number,
1527 -- p_api_version_number,
1528 -- l_api_name,
1529 -- G_PKG_NAME)
1530 -- THEN
1531 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1532 -- END IF;
1533
1534 -- Initialize message list if p_init_msg_list is set to TRUE.
1535 IF FND_API.to_Boolean( p_init_msg_list )
1536 THEN
1537 FND_MSG_PUB.initialize;
1538 END IF;
1539
1540 -- Initialize API return status to success
1541 x_return_status := FND_API.G_RET_STS_SUCCESS;
1542
1543 if ((p_perz_ddf_id is not null)
1544 AND (p_perz_ddf_id <> FND_API.G_MISS_NUM)) then
1545 -- Call delete_row()
1546
1547 delete_jtf_perz_data_default( p_perz_ddf_id);
1548 else
1549 RAISE FND_API.G_EXC_ERROR;
1550
1551 end if;
1552 -- ******** Standard Ends ***********
1553 --
1554 -- End of API body.
1555 --
1556 -- 7. Commit the whole thing
1557
1558 -- Standard check of p_commit.
1559 IF FND_API.To_Boolean ( p_commit )
1560 THEN
1561 COMMIT WORK;
1562 END IF;
1563
1564 -- Debug Message
1565 -- Standard call to get message count and if count is 1, get message info.
1566 FND_MSG_PUB.Count_And_Get
1567 (
1568 p_count => x_msg_count,
1569 p_data => x_msg_data
1570 );
1571
1572
1573 EXCEPTION
1574
1575 WHEN FND_API.G_EXC_ERROR THEN
1576
1577 ROLLBACK TO DELETE_PERZ_DDF_PVT;
1578 x_return_status := FND_API.G_RET_STS_ERROR ;
1579
1580 FND_MSG_PUB.Count_And_Get
1581 ( p_count => x_msg_count,
1582 p_data => x_msg_data
1583 );
1584
1585 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1586 THEN
1587 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1588 END IF;
1589
1590 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1591
1592 ROLLBACK TO DELETE_PERZ_DDF_PVT;
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1594
1595 FND_MSG_PUB.Count_And_Get
1596 ( p_count => x_msg_count,
1597 p_data => x_msg_data
1598 );
1599
1600 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1601 THEN
1602 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1603 END IF;
1604
1605 WHEN OTHERS THEN
1606
1607 ROLLBACK TO DELETE_PERZ_DDF_PVT;
1608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1609
1610 FND_MSG_PUB.Count_And_Get
1611 ( p_count => x_msg_count,
1612 p_data => x_msg_data
1613 );
1614
1615 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1616 THEN
1617 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1618 END IF;
1619
1620
1621 END delete_data_default;
1622 -- *****************************************************************************
1623 -- *****************************************************************************
1624 END JTF_PERZ_DDF_PVT ;