1 package body JTF_IAPP_FAMILIES_PKG as
2 /* $Header: jtfiappb.pls 120.2 2005/10/25 05:20:37 psanyal ship $ */
3
4 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
5 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
6
7 procedure INSERT_ROW (
8 X_APP_FAMILY_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
9 X_APP_FAMILY_ACCESS_NAME IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
10 X_DISPLAY_SEQUENCE in NUMBER,
11 X_ENABLED_FLAG in VARCHAR2,
12 X_APP_FAMILY_DISPLAY_NAME in VARCHAR2,
13 X_APP_FAMILY_DESCRIPTION in VARCHAR2,
14 X_OWNER in VARCHAR2
15 ) is
16 cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
17 where APP_FAMILY_ID = X_APP_FAMILY_ID
18 ;
19
20 cursor C_2 is
21 select JTF_IAPP_FAMILIES_B_S.nextval from SYS.DUAL
22 ;
23
24 l_app_family_id NUMBER := NULL;
25 l_last_updated_by NUMBER := G_USER_ID;
26
27 begin
28
29 If (X_APP_FAMILY_ID IS NULL) then
30 OPEN C_2;
31 FETCH C_2 INTO X_APP_FAMILY_ID;
32 CLOSE C_2;
33 End If;
34
35 if (X_APP_FAMILY_ACCESS_NAME IS NULL) then
36 X_APP_FAMILY_ACCESS_NAME := 'JTFFAM' || to_char(X_APP_FAMILY_ID);
37 end if;
38
39 -- check if this is from a seed upload
40 if (X_OWNER = 'SEED') then
41 l_last_updated_by := 1;
42 else
43 l_last_updated_by := G_USER_ID;
44 end if;
45
46 insert into JTF_IAPP_FAMILIES_B (
47 APP_FAMILY_ID,
48 APP_FAMILY_ACCESS_NAME,
49 DISPLAY_SEQUENCE,
50 ENABLED_FLAG,
51 DELETED_FLAG,
52 OBJECT_VERSION_NUMBER,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN
58 ) values (
59 X_APP_FAMILY_ID,
60 X_APP_FAMILY_ACCESS_NAME,
61 X_DISPLAY_SEQUENCE,
62 X_ENABLED_FLAG,
63 'N',
64 0,
65 SYSDATE,
66 G_USER_ID,
67 SYSDATE,
68 l_last_updated_by,
69 G_LOGIN_ID
70 );
71
72 insert into JTF_IAPP_FAMILIES_TL (
73 APP_FAMILY_ID,
74 APP_FAMILY_DISPLAY_NAME,
75 APP_FAMILY_DESCRIPTION,
76 CREATED_BY,
77 CREATION_DATE,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN,
81 LANGUAGE,
82 SOURCE_LANG
83 ) select
84 X_APP_FAMILY_ID,
85 X_APP_FAMILY_DISPLAY_NAME,
86 X_APP_FAMILY_DESCRIPTION,
87 G_USER_ID,
88 SYSDATE,
89 SYSDATE,
90 l_last_updated_by,
91 G_LOGIN_ID,
92 L.LANGUAGE_CODE,
93 userenv('LANG')
94 from FND_LANGUAGES L
95 where L.INSTALLED_FLAG in ('I', 'B')
96 and not exists
97 (select NULL
98 from JTF_IAPP_FAMILIES_TL T
99 where T.APP_FAMILY_ID = X_APP_FAMILY_ID
100 and T.LANGUAGE = L.LANGUAGE_CODE);
101
102 open c;
103 fetch c into l_app_family_id;
104 if (c%notfound) then
105 close c;
106 raise no_data_found;
107 end if;
108 close c;
109
110 end INSERT_ROW;
111
112 procedure UPDATE_ROW (
113 X_APP_FAMILY_ID in NUMBER,
114 X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
115 X_DISPLAY_SEQUENCE in NUMBER,
116 X_ENABLED_FLAG in VARCHAR2,
117 X_OBJECT_VERSION_NUMBER in NUMBER,
118 X_APP_FAMILY_DISPLAY_NAME in VARCHAR2,
119 X_APP_FAMILY_DESCRIPTION in VARCHAR2,
120 X_OWNER in VARCHAR2
121 ) is
122
123 l_last_updated_by NUMBER := G_USER_ID;
124
125 begin
126 update JTF_IAPP_FAMILIES_B set
127 APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME,
128 DISPLAY_SEQUENCE = X_DISPLAY_SEQUENCE,
129 ENABLED_FLAG = X_ENABLED_FLAG,
130 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
131 LAST_UPDATE_DATE = SYSDATE,
132 LAST_UPDATED_BY = G_USER_ID,
133 LAST_UPDATE_LOGIN = G_LOGIN_ID
134 where APP_FAMILY_ID = X_APP_FAMILY_ID;
135
136 if (sql%notfound) then
137 raise no_data_found;
138 end if;
139
140 -- check if this is from a seed upload
141 if (X_OWNER = 'SEED') then
142 l_last_updated_by := 1;
143 else
144 l_last_updated_by := G_USER_ID;
145 end if;
146
147 update JTF_IAPP_FAMILIES_TL set
148 APP_FAMILY_DISPLAY_NAME = X_APP_FAMILY_DISPLAY_NAME,
149 APP_FAMILY_DESCRIPTION = X_APP_FAMILY_DESCRIPTION,
150 LAST_UPDATE_DATE = SYSDATE,
151 LAST_UPDATED_BY = l_last_updated_by,
152 LAST_UPDATE_LOGIN = G_LOGIN_ID,
153 SOURCE_LANG = userenv('LANG')
154 where APP_FAMILY_ID = X_APP_FAMILY_ID
155 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
156
157 if (sql%notfound) then
158 raise no_data_found;
159 end if;
160 end UPDATE_ROW;
161
162 procedure DELETE_ROW (
163 X_APP_FAMILY_ID in NUMBER
164 ) is
165 begin
166 delete from JTF_IAPP_FAMILIES_TL
167 where APP_FAMILY_ID = X_APP_FAMILY_ID;
168
169 if (sql%notfound) then
170 raise no_data_found;
171 end if;
172
173 delete from JTF_IAPP_FAMILIES_B
174 where APP_FAMILY_ID = X_APP_FAMILY_ID;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179 end DELETE_ROW;
180
181 -- ALL APP to FAMILY related procedures
182
183 procedure ADD_APP_TO_FAMILY (
184 X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
185 X_APP_ID in NUMBER
186 ) is
187
188 cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
189 where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
190 ;
191
192 cursor C_2 is select f.APP_FAMILY_ID, APPLICATION_ID
193 from JTF_IAPP_FAMILIES_B f, JTF_IAPP_FAMILY_APP_MAP a
194 where f.APP_FAMILY_ID = a.APP_FAMILY_ID
195 AND f.APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
196 AND a.APPLICATION_ID = X_APP_ID
197 ;
198
199 l_app_family_id NUMBER := NULL;
200 l_app_id NUMBER := NULL;
201
202 begin
203
204 If (X_APP_FAMILY_ACCESS_NAME IS NOT NULL AND X_APP_ID IS NOT NULL ) then
205
206 OPEN C_2;
207 FETCH C_2 INTO l_app_family_id, l_app_id;
208
209 if (C_2%notfound) then
210
211 If (X_APP_FAMILY_ACCESS_NAME IS NOT NULL) then
212 OPEN C;
213 FETCH C INTO l_app_family_id;
214 CLOSE C;
215 End If;
216
217 insert into JTF_IAPP_FAMILY_APP_MAP (
218 APP_FAMILY_ID,
219 APPLICATION_ID,
220 CREATED_BY,
221 CREATION_DATE,
222 LAST_UPDATE_DATE,
223 LAST_UPDATED_BY,
224 LAST_UPDATE_LOGIN
225 ) values (
226 l_app_family_id,
227 X_APP_ID,
228 G_USER_ID,
229 SYSDATE,
230 SYSDATE,
231 G_USER_ID,
232 G_LOGIN_ID
233 );
234
235 end if;
236
237 CLOSE C_2;
238 End If;
239
240 end ADD_APP_TO_FAMILY;
241
242 procedure DELETE_ALL_APPS_IN_FAMILY (
243 X_APP_FAMILY_ACCESS_NAME in VARCHAR2
244 ) is
245
246 cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
247 where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
248 ;
249
250 l_app_family_id NUMBER := NULL;
251 begin
252
253 If (X_APP_FAMILY_ACCESS_NAME IS NOT NULL) then
254 OPEN C;
255 FETCH C INTO l_app_family_id;
256 CLOSE C;
257 End If;
258
259 delete from JTF_IAPP_FAMILY_APP_MAP
260 where APP_FAMILY_ID = l_app_family_id;
261
262 -- if (sql%notfound) then
263 -- raise no_data_found;
264 -- end if;
265
266 end DELETE_ALL_APPS_IN_FAMILY;
267
268
269 procedure DELETE_FAMILY_AND_APPS (
270 X_APP_FAMILY_ACCESS_NAME in VARCHAR2
271 ) is
272
273 begin
274
275 jtf_iapp_families_pkg.DELETE_ALL_APPS_IN_FAMILY (X_APP_FAMILY_ACCESS_NAME => X_APP_FAMILY_ACCESS_NAME);
276
277 update JTF_IAPP_FAMILIES_B set DELETED_FLAG = 'Y' where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME;
278
279 if (sql%notfound) then
280 raise no_data_found;
281 end if;
282
283 end DELETE_FAMILY_AND_APPS;
284
285
286 procedure DELETE_APP_IN_FAMILY (
287 X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
288 X_APP_ID in NUMBER
289 ) is
290
291 cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
292 where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
293 ;
294
295 l_app_family_id NUMBER := NULL;
296 begin
297
298 If (X_APP_FAMILY_ACCESS_NAME IS NOT NULL) then
299 OPEN C;
300 FETCH C INTO l_app_family_id;
301 CLOSE C;
302 End If;
303
304 delete from JTF_IAPP_FAMILY_APP_MAP
305 where APP_FAMILY_ID = l_app_family_id
306 AND APPLICATION_ID = X_APP_ID;
307
308 if (sql%notfound) then
309 raise no_data_found;
310 end if;
311
312
313 end DELETE_APP_IN_FAMILY;
314
315 procedure ADD_USER_DEF_RESP (
316 X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
317 X_USER_ID in NUMBER,
318 X_DEFAULT_RESP_ID in NUMBER
319 ) is
320
321 cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
322 where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
323 ;
324
325 cursor C_2 is select f.APP_FAMILY_ID, USER_ID
326 from JTF_IAPP_FAMILIES_B f, JTF_IAPP_FAMILY_USR_MAP u
327 where f.APP_FAMILY_ID = u.APP_FAMILY_ID
328 AND f.APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
329 AND u.USER_ID = X_USER_ID
330 ;
331
332 l_app_family_id NUMBER := NULL;
333 l_user_id NUMBER := NULL;
334
335 begin
336
337 If (X_APP_FAMILY_ACCESS_NAME IS NOT NULL
338 AND X_USER_ID IS NOT NULL ) then
339 OPEN C_2;
340 FETCH C_2 INTO l_app_family_id, l_user_id;
341
342 If (X_APP_FAMILY_ACCESS_NAME IS NOT NULL) then
343 OPEN C;
344 FETCH C INTO l_app_family_id;
345 CLOSE C;
346 End If;
347
348 if (C_2%notfound) then
349 insert into JTF_IAPP_FAMILY_USR_MAP (
350 APP_FAMILY_ID,
351 USER_ID,
352 DEFAULT_RESP_ID,
353 CREATED_BY,
354 CREATION_DATE,
355 LAST_UPDATE_DATE,
356 LAST_UPDATED_BY,
357 LAST_UPDATE_LOGIN
358 ) values (
359 l_app_family_id,
360 X_USER_ID,
361 X_DEFAULT_RESP_ID,
362 G_USER_ID,
363 SYSDATE,
364 SYSDATE,
365 G_USER_ID,
366 G_LOGIN_ID
367 );
368 else
369
370 update JTF_IAPP_FAMILY_USR_MAP set
371 DEFAULT_RESP_ID = X_DEFAULT_RESP_ID,
372 LAST_UPDATE_DATE = SYSDATE,
373 LAST_UPDATED_BY = G_USER_ID,
374 LAST_UPDATE_LOGIN = G_LOGIN_ID
375 where APP_FAMILY_ID = l_app_family_id
376 AND USER_ID = X_USER_ID;
377
378 if (sql%notfound) then
379 raise no_data_found;
380 end if;
381
382 end if;
383
384 CLOSE C_2;
385
386 End If;
387
388 end ADD_USER_DEF_RESP;
389
390 procedure DELETE_USER_DEF_RESP (
391 X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
392 X_USER_ID in NUMBER
393 ) is
394
395 cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
396 where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
397 ;
398
399 l_app_family_id NUMBER := NULL;
400
401 begin
402
403 If (X_APP_FAMILY_ACCESS_NAME IS NOT NULL) then
404
405 OPEN C;
406 FETCH C INTO l_app_family_id;
407 CLOSE C;
408
409 delete from JTF_IAPP_FAMILY_USR_MAP
410 where APP_FAMILY_ID = l_app_family_id
411 AND USER_ID = X_USER_ID;
412
413 if (sql%notfound) then
414 raise no_data_found;
415 end if;
416
417 End If;
418
419 end DELETE_USER_DEF_RESP;
420
421 procedure DELETE_USER_DEF_RESP_USING_ID (
422 X_APP_FAMILY_ID in NUMBER,
423 X_USER_ID in NUMBER
424 ) is
425
426 begin
427
428 If (X_APP_FAMILY_ID IS NOT NULL AND X_USER_ID IS NOT NULL) then
429
430 delete from JTF_IAPP_FAMILY_USR_MAP
431 where APP_FAMILY_ID = X_APP_FAMILY_ID
432 AND USER_ID = X_USER_ID;
433
434 if (sql%notfound) then
435 raise no_data_found;
436 end if;
437
438 End If;
439
440 end DELETE_USER_DEF_RESP_USING_ID;
441
442
443 procedure LOAD_ROW (
444 X_APP_FAMILY_ID in NUMBER,
445 X_APP_FAMILY_ACCESS_NAME in VARCHAR2,
446 X_DISPLAY_SEQUENCE in NUMBER,
447 X_ENABLED_FLAG in VARCHAR2,
448 X_APP_FAMILY_DISPLAY_NAME in VARCHAR2,
449 X_APP_FAMILY_DESCRIPTION in VARCHAR2,
450 X_APPLICATION_ID in NUMBER,
451 X_OWNER in VARCHAR2
452 )is
453
454 cursor C is select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
455 where APP_FAMILY_ACCESS_NAME = X_APP_FAMILY_ACCESS_NAME
456 ;
457 cursor c_2 is select nvl(max(app_family_id), 0)
458 from jtf_iapp_families_b where app_family_id < 10000
459 ;
460
461 l_app_family_id NUMBER := X_APP_FAMILY_ID;
462 l_app_pseudo_seq NUMBER := NULL;
463 l_app_family_name VARCHAR2(60) := X_APP_FAMILY_ACCESS_NAME;
464
465 begin
466
467 OPEN C;
468 FETCH C INTO l_app_family_id;
469 CLOSE C;
470
471 if(C%notfound) then
472
473 -- generate pseudo sequence
474 OPEN C_2;
475 FETCH C_2 INTO l_app_pseudo_seq;
476 l_app_pseudo_seq := l_app_pseudo_seq + 1;
477 CLOSE C_2;
478
479 jtf_iapp_families_pkg.INSERT_ROW (
480 X_APP_FAMILY_ID => l_app_pseudo_seq,
481 X_APP_FAMILY_ACCESS_NAME => l_app_family_name,
482 X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
483 X_ENABLED_FLAG => X_ENABLED_FLAG,
484 X_APP_FAMILY_DISPLAY_NAME => X_APP_FAMILY_DISPLAY_NAME,
485 X_APP_FAMILY_DESCRIPTION => X_APP_FAMILY_DESCRIPTION,
486 X_OWNER => X_OWNER
487 );
488 else
489 jtf_iapp_families_pkg.UPDATE_ROW (
490 X_APP_FAMILY_ID => l_app_family_id,
491 X_APP_FAMILY_ACCESS_NAME => l_app_family_name,
492 X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
493 X_ENABLED_FLAG => X_ENABLED_FLAG,
494 X_OBJECT_VERSION_NUMBER => null,
495 X_APP_FAMILY_DISPLAY_NAME => X_APP_FAMILY_DISPLAY_NAME,
496 X_APP_FAMILY_DESCRIPTION => X_APP_FAMILY_DESCRIPTION,
497 X_OWNER => X_OWNER
498 );
499 end if;
500
501 jtf_iapp_families_pkg.ADD_APP_TO_FAMILY (
502 X_APP_FAMILY_ACCESS_NAME => X_APP_FAMILY_ACCESS_NAME,
503 X_APP_ID => X_APPLICATION_ID);
504
505 end LOAD_ROW;
506
507 procedure TRANSLATE_ROW (
508 x_APP_FAMILY_ACCESS_NAME in VARCHAR2,
509 x_APP_FAMILY_DISPLAY_NAME in VARCHAR2,
510 x_APP_FAMILY_DESCRIPTION in VARCHAR2,
511 x_OWNER in VARCHAR2
512 )is
513
514 begin
515 update JTF_IAPP_FAMILIES_TL set
516 APP_FAMILY_DISPLAY_NAME = x_APP_FAMILY_DISPLAY_NAME,
517 APP_FAMILY_DESCRIPTION = x_APP_FAMILY_DESCRIPTION,
518 LAST_UPDATE_DATE = sysdate,
519 LAST_UPDATED_BY = decode(x_OWNER, 'SEED', 1, 0),
520 LAST_UPDATE_LOGIN = 0,
521 SOURCE_LANG = userenv('LANG')
522 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
526
523 and APP_FAMILY_ID = (select APP_FAMILY_ID from JTF_IAPP_FAMILIES_B
524 where APP_FAMILY_ACCESS_NAME = x_APP_FAMILY_ACCESS_NAME);
525 end TRANSLATE_ROW;
527
528 procedure ADD_LANGUAGE
529 is
530 begin
531 delete from JTF_IAPP_FAMILIES_TL T
532 where not exists
533 (select NULL
534 from JTF_IAPP_FAMILIES_B B
535 where B.APP_FAMILY_ID = T.APP_FAMILY_ID
536 );
537
538 update JTF_IAPP_FAMILIES_TL T set (
539 APP_FAMILY_DISPLAY_NAME,
540 APP_FAMILY_DESCRIPTION
541 ) = (select
542 B.APP_FAMILY_DISPLAY_NAME,
543 B.APP_FAMILY_DESCRIPTION
544 from JTF_IAPP_FAMILIES_TL B
545 where B.APP_FAMILY_ID = T.APP_FAMILY_ID
546 and B.LANGUAGE = T.SOURCE_LANG)
547 where (
548 T.APP_FAMILY_ID,
549 T.LANGUAGE
550 ) in (select
551 SUBT.APP_FAMILY_ID,
552 SUBT.LANGUAGE
553 from JTF_IAPP_FAMILIES_TL SUBB, JTF_IAPP_FAMILIES_TL SUBT
554 where SUBB.APP_FAMILY_ID = SUBT.APP_FAMILY_ID
555 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
556 and (SUBB.APP_FAMILY_DISPLAY_NAME <> SUBT.APP_FAMILY_DISPLAY_NAME
557 or SUBB.APP_FAMILY_DESCRIPTION <> SUBT.APP_FAMILY_DESCRIPTION
558 or (SUBB.APP_FAMILY_DESCRIPTION is null and SUBT.APP_FAMILY_DESCRIPTION is not null)
559 or (SUBB.APP_FAMILY_DESCRIPTION is not null and SUBT.APP_FAMILY_DESCRIPTION is null)
560 ));
561
562 insert into JTF_IAPP_FAMILIES_TL (
563 APP_FAMILY_ID,
564 APP_FAMILY_DISPLAY_NAME,
565 APP_FAMILY_DESCRIPTION,
566 CREATED_BY,
567 CREATION_DATE,
568 LAST_UPDATE_DATE,
569 LAST_UPDATED_BY,
570 LAST_UPDATE_LOGIN,
571 LANGUAGE,
572 SOURCE_LANG
573 ) select
574 B.APP_FAMILY_ID,
575 B.APP_FAMILY_DISPLAY_NAME,
576 B.APP_FAMILY_DESCRIPTION,
577 B.CREATED_BY,
578 B.CREATION_DATE,
579 B.LAST_UPDATE_DATE,
580 B.LAST_UPDATED_BY,
581 B.LAST_UPDATE_LOGIN,
582 L.LANGUAGE_CODE,
583 B.SOURCE_LANG
584 from JTF_IAPP_FAMILIES_TL B, FND_LANGUAGES L
585 where L.INSTALLED_FLAG in ('I', 'B')
586 and B.LANGUAGE = userenv('LANG')
587 and not exists
588 (select NULL
589 from JTF_IAPP_FAMILIES_TL T
590 where T.APP_FAMILY_ID = B.APP_FAMILY_ID
591 and T.LANGUAGE = L.LANGUAGE_CODE);
592 end ADD_LANGUAGE;
593
594 end JTF_IAPP_FAMILIES_PKG;
[Home] [Help]