[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_USERTYPES_PKG
Source
1 package body JTF_UM_USERTYPES_PKG as
2 /* $Header: JTFUMUTB.pls 120.6.12010000.2 2008/08/07 10:34:35 ruddas ship $ */
3 procedure INSERT_ROW (
4 X_USERTYPE_ID out NOCOPY NUMBER,
5 X_EFFECTIVE_END_DATE in DATE,
6 X_APPROVAL_ID in NUMBER,
7 X_APPLICATION_ID in NUMBER,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_EMAIL_NOTIFICATION_FLAG in VARCHAR2,
10 X_IS_SELF_SERVICE_FLAG in VARCHAR2,
11 X_EFFECTIVE_START_DATE in DATE,
12 X_USERTYPE_KEY in VARCHAR2,
13 X_USERTYPE_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER,
20 X_USERTYPE_SHORTNAME in VARCHAR2,
21 X_DISPLAY_ORDER in NUMBER
22 ) is
23 cursor C is select ROWID from JTF_UM_USERTYPES_B
24 where USERTYPE_ID = X_USERTYPE_ID
25 ;
26 begin
27 insert into JTF_UM_USERTYPES_B (
28 EFFECTIVE_END_DATE,
29 APPROVAL_ID,
30 APPLICATION_ID,
31 ENABLED_FLAG,
32 EMAIL_NOTIFICATION_FLAG,
33 IS_SELF_SERVICE_FLAG,
34 EFFECTIVE_START_DATE,
35 USERTYPE_ID,
36 USERTYPE_KEY,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_LOGIN,
42 DISPLAY_ORDER
43 ) values (
44 X_EFFECTIVE_END_DATE,
45 X_APPROVAL_ID,
46 X_APPLICATION_ID,
47 X_ENABLED_FLAG,
48 X_EMAIL_NOTIFICATION_FLAG,
49 X_IS_SELF_SERVICE_FLAG,
50 X_EFFECTIVE_START_DATE,
51 JTF_UM_USERTYPES_B_S.NEXTVAL,
52 X_USERTYPE_KEY,
53 X_CREATION_DATE,
54 X_CREATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATED_BY,
57 X_LAST_UPDATE_LOGIN,
58 X_DISPLAY_ORDER
59 ) RETURNING USERTYPE_ID INTO X_USERTYPE_ID;
60
61 insert into JTF_UM_USERTYPES_TL (
62 CREATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN,
66 USERTYPE_NAME,
67 DESCRIPTION,
68 CREATION_DATE,
69 USERTYPE_ID,
70 LANGUAGE,
71 SOURCE_LANG,
72 USERTYPE_SHORTNAME
73 ) select
74 X_CREATED_BY,
75 X_LAST_UPDATE_DATE,
76 X_LAST_UPDATED_BY,
77 X_LAST_UPDATE_LOGIN,
78 X_USERTYPE_NAME,
79 X_DESCRIPTION,
80 X_CREATION_DATE,
81 X_USERTYPE_ID,
82 L.LANGUAGE_CODE,
83 userenv('LANG'),
84 X_USERTYPE_SHORTNAME
85 from FND_LANGUAGES L
86 where L.INSTALLED_FLAG in ('I', 'B')
87 and not exists
88 (select NULL
89 from JTF_UM_USERTYPES_TL T
90 where T.USERTYPE_ID = X_USERTYPE_ID
91 and T.LANGUAGE = L.LANGUAGE_CODE);
92
93 open c;
94 if (c%notfound) then
95 close c;
96 raise no_data_found;
97 end if;
98 close c;
99
100 end INSERT_ROW;
101
102 procedure LOCK_ROW (
103 X_USERTYPE_ID in NUMBER,
104 X_EFFECTIVE_END_DATE in DATE,
105 X_APPROVAL_ID in NUMBER,
106 X_APPLICATION_ID in NUMBER,
107 X_ENABLED_FLAG in VARCHAR2,
108 X_EMAIL_NOTIFICATION_FLAG in VARCHAR2,
109 X_IS_SELF_SERVICE_FLAG in VARCHAR2,
110 X_EFFECTIVE_START_DATE in DATE,
111 X_USERTYPE_KEY in VARCHAR2,
112 X_USERTYPE_NAME in VARCHAR2,
113 X_DESCRIPTION in VARCHAR2
114 ) is
115 cursor c is select
116 EFFECTIVE_END_DATE,
117 APPROVAL_ID,
118 APPLICATION_ID,
119 ENABLED_FLAG,
120 EMAIL_NOTIFICATION_FLAG,
121 IS_SELF_SERVICE_FLAG,
122 EFFECTIVE_START_DATE,
123 USERTYPE_KEY
124 from JTF_UM_USERTYPES_B
125 where USERTYPE_ID = X_USERTYPE_ID
126 for update of USERTYPE_ID nowait;
127 recinfo c%rowtype;
128
129 cursor c1 is select
130 USERTYPE_NAME,
131 DESCRIPTION,
132 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
133 from JTF_UM_USERTYPES_TL
134 where USERTYPE_ID = X_USERTYPE_ID
135 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
136 for update of USERTYPE_ID nowait;
137 begin
138 open c;
139 fetch c into recinfo;
140 if (c%notfound) then
141 close c;
142 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
143 app_exception.raise_exception;
144 end if;
145 close c;
146 if ( ((recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
147 OR ((recinfo.EFFECTIVE_END_DATE is null) AND (X_EFFECTIVE_END_DATE is null)))
148 AND ((recinfo.APPROVAL_ID = X_APPROVAL_ID)
149 OR ((recinfo.APPROVAL_ID is null) AND (X_APPROVAL_ID is null)))
150 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
151 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
152 AND (recinfo.EMAIL_NOTIFICATION_FLAG = X_EMAIL_NOTIFICATION_FLAG)
153 AND (recinfo.IS_SELF_SERVICE_FLAG = X_IS_SELF_SERVICE_FLAG)
154 AND (recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
155 AND (recinfo.USERTYPE_KEY = X_USERTYPE_KEY)
156 ) then
157 null;
158 else
159 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160 app_exception.raise_exception;
161 end if;
162
163 for tlinfo in c1 loop
164 if (tlinfo.BASELANG = 'Y') then
165 if ( (tlinfo.USERTYPE_NAME = X_USERTYPE_NAME)
166 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
167 ) then
168 null;
169 else
170 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171 app_exception.raise_exception;
172 end if;
173 end if;
174 end loop;
175 return;
176 end LOCK_ROW;
177
178 procedure UPDATE_ROW (
179 X_USERTYPE_ID in NUMBER,
180 X_EFFECTIVE_END_DATE in DATE,
181 X_APPROVAL_ID in NUMBER,
182 X_APPLICATION_ID in NUMBER,
183 X_ENABLED_FLAG in VARCHAR2,
184 X_EMAIL_NOTIFICATION_FLAG in VARCHAR2,
185 X_IS_SELF_SERVICE_FLAG in VARCHAR2,
186 X_USERTYPE_KEY in VARCHAR2,
187 X_USERTYPE_NAME in VARCHAR2,
188 X_DESCRIPTION in VARCHAR2,
189 X_LAST_UPDATE_DATE in DATE,
190 X_LAST_UPDATED_BY in NUMBER,
191 X_LAST_UPDATE_LOGIN in NUMBER,
192 X_USERTYPE_SHORTNAME in VARCHAR2,
193 X_DISPLAY_ORDER in NUMBER
194 ) is
195 begin
196 update JTF_UM_USERTYPES_B set
197 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
198 APPROVAL_ID = X_APPROVAL_ID,
199 APPLICATION_ID = X_APPLICATION_ID,
200 ENABLED_FLAG = X_ENABLED_FLAG,
201 EMAIL_NOTIFICATION_FLAG = X_EMAIL_NOTIFICATION_FLAG,
202 IS_SELF_SERVICE_FLAG = X_IS_SELF_SERVICE_FLAG,
203 USERTYPE_KEY = X_USERTYPE_KEY,
204 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
205 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
206 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
207 DISPLAY_ORDER = X_DISPLAY_ORDER
208 where USERTYPE_ID = X_USERTYPE_ID;
209
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213
214 update JTF_UM_USERTYPES_TL set
215 USERTYPE_NAME = X_USERTYPE_NAME,
216 DESCRIPTION = X_DESCRIPTION,
217 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
218 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
219 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
220 SOURCE_LANG = userenv('LANG'),
221 USERTYPE_SHORTNAME = X_USERTYPE_SHORTNAME
222 where USERTYPE_ID = X_USERTYPE_ID
223 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
224
225 if (sql%notfound) then
226 raise no_data_found;
227 end if;
228 end UPDATE_ROW;
229
230 procedure DELETE_ROW (
231 X_USERTYPE_ID in NUMBER
232 ) is
233 begin
234 delete from JTF_UM_USERTYPES_TL
235 where USERTYPE_ID = X_USERTYPE_ID;
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240
241 delete from JTF_UM_USERTYPES_B
242 where USERTYPE_ID = X_USERTYPE_ID;
243
244 if (sql%notfound) then
245 raise no_data_found;
246 end if;
247 end DELETE_ROW;
248
249 procedure ADD_LANGUAGE
250 is
251 begin
252 delete from JTF_UM_USERTYPES_TL T
253 where not exists
254 (select NULL
255 from JTF_UM_USERTYPES_B B
256 where B.USERTYPE_ID = T.USERTYPE_ID
257 );
258
259 update JTF_UM_USERTYPES_TL T set (
260 USERTYPE_NAME,
261 DESCRIPTION,
262 USERTYPE_SHORTNAME
263 ) = (select
264 B.USERTYPE_NAME,
265 B.DESCRIPTION,
266 B.USERTYPE_SHORTNAME
267 from JTF_UM_USERTYPES_TL B
268 where B.USERTYPE_ID = T.USERTYPE_ID
269 and B.LANGUAGE = T.SOURCE_LANG)
270 where (
271 T.USERTYPE_ID,
272 T.LANGUAGE
273 ) in (select
274 SUBT.USERTYPE_ID,
275 SUBT.LANGUAGE
276 from JTF_UM_USERTYPES_TL SUBB, JTF_UM_USERTYPES_TL SUBT
277 where SUBB.USERTYPE_ID = SUBT.USERTYPE_ID
278 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
279 and (SUBB.USERTYPE_NAME <> SUBT.USERTYPE_NAME
280 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
281 or SUBB.USERTYPE_SHORTNAME <> SUBT.USERTYPE_SHORTNAME
282 ));
283
284 insert into JTF_UM_USERTYPES_TL (
285 CREATED_BY,
286 LAST_UPDATE_DATE,
287 LAST_UPDATED_BY,
288 LAST_UPDATE_LOGIN,
289 USERTYPE_NAME,
290 DESCRIPTION,
291 CREATION_DATE,
292 USERTYPE_ID,
293 LANGUAGE,
294 SOURCE_LANG,
295 USERTYPE_SHORTNAME
296 ) select
297 B.CREATED_BY,
298 B.LAST_UPDATE_DATE,
299 B.LAST_UPDATED_BY,
300 B.LAST_UPDATE_LOGIN,
301 B.USERTYPE_NAME,
302 B.DESCRIPTION,
303 B.CREATION_DATE,
304 B.USERTYPE_ID,
305 L.LANGUAGE_CODE,
306 B.SOURCE_LANG,
307 B.USERTYPE_SHORTNAME
308 from JTF_UM_USERTYPES_TL B, FND_LANGUAGES L
309 where L.INSTALLED_FLAG in ('I', 'B')
310 and B.LANGUAGE = userenv('LANG')
311 and not exists
312 (select NULL
313 from JTF_UM_USERTYPES_TL T
314 where T.USERTYPE_ID = B.USERTYPE_ID
315 and T.LANGUAGE = L.LANGUAGE_CODE);
316 end ADD_LANGUAGE;
317
318 --For this procedure, if USERTYPE_ID passed as input is NULL, then create a new record
319 -- otherwise, modify the existing record.
320
321 procedure LOAD_ROW (
322 X_USERTYPE_ID IN NUMBER,
323 X_EFFECTIVE_START_DATE IN DATE,
324 X_EFFECTIVE_END_DATE IN DATE,
325 X_OWNER IN VARCHAR2,
326 X_APPROVAL_ID IN NUMBER,
327 X_APPLICATION_ID IN NUMBER,
328 X_ENABLED_FLAG IN VARCHAR2,
329 X_EMAIL_NOTIFICATION_FLAG IN VARCHAR2,
330 X_IS_SELF_SERVICE_FLAG IN VARCHAR2,
331 X_USERTYPE_KEY IN VARCHAR2,
332 X_USERTYPE_NAME IN VARCHAR2,
333 X_DESCRIPTION IN VARCHAR2,
334 X_USERTYPE_SHORTNAME in VARCHAR2,
335 X_DISPLAY_ORDER in NUMBER,
336 x_last_update_date in varchar2 default NULL,
337 X_CUSTOM_MODE in varchar2 default NULL
338 ) is
339 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
340 l_usertype_id NUMBER := 0;
341 f_luby number; -- entity owner in file
342 f_ludate date; -- entity update date in file
343 db_luby number; -- entity owner in db
344 db_ludate date; -- entity update date in db
345 v_db_owner_id number;
346 v_db_display_order number;
347 v_db_usertype_shortname varchar2(230);
348 begin
349 --if (x_owner = 'SEED') then
350 -- l_user_id := 1;
351 -- end if;
352
353 -- Translate owner to file_last_updated_by
354 f_luby := fnd_load_util.owner_id(x_owner);
355
356 -- Translate char last_update_date to date
357 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
358
359 -- If USERTYPE_ID passed in NULL, insert the record
360 if ( X_USERTYPE_ID is NULL ) THEN
361 INSERT_ROW(
362 X_USERTYPE_ID => l_usertype_id,
363 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
364 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
365 X_APPROVAL_ID => X_APPROVAL_ID,
366 X_APPLICATION_ID => X_APPLICATION_ID,
367 X_ENABLED_FLAG => X_ENABLED_FLAG,
368 X_EMAIL_NOTIFICATION_FLAG => X_EMAIL_NOTIFICATION_FLAG,
369 X_IS_SELF_SERVICE_FLAG => X_IS_SELF_SERVICE_FLAG,
370 X_USERTYPE_KEY => X_USERTYPE_KEY,
371 X_USERTYPE_NAME => X_USERTYPE_NAME,
372 X_DESCRIPTION => X_DESCRIPTION,
373 X_CREATION_DATE => f_ludate,
374 X_CREATED_BY => f_luby,
375 X_LAST_UPDATE_DATE => f_ludate,
376 X_LAST_UPDATED_BY => f_luby,
377 X_LAST_UPDATE_LOGIN => l_user_id,
378 X_USERTYPE_SHORTNAME => X_USERTYPE_SHORTNAME,
379 X_DISPLAY_ORDER => X_DISPLAY_ORDER
380
381 );
382 else
383 -- This select stmnt also checks if
384 -- there is a row for this app_id and this app_short_name
385 -- Exception is thrown otherwise.
386 select LAST_UPDATED_BY, LAST_UPDATE_DATE
387 into db_luby, db_ludate
388 FROM JTF_UM_USERTYPES_B
389 where USERTYPE_ID = X_USERTYPE_ID;
390
391 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
392 db_ludate, X_CUSTOM_MODE)) then
393
394 UPDATE_ROW(
395 X_USERTYPE_ID => X_USERTYPE_ID,
396 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
397 X_APPROVAL_ID => X_APPROVAL_ID,
398 X_APPLICATION_ID => X_APPLICATION_ID,
399 X_ENABLED_FLAG => X_ENABLED_FLAG,
400 X_EMAIL_NOTIFICATION_FLAG => X_EMAIL_NOTIFICATION_FLAG,
401 X_IS_SELF_SERVICE_FLAG => X_IS_SELF_SERVICE_FLAG,
402 X_USERTYPE_KEY => X_USERTYPE_KEY,
403 X_USERTYPE_NAME => X_USERTYPE_NAME,
404 X_DESCRIPTION => X_DESCRIPTION,
405 X_LAST_UPDATE_DATE => f_ludate,
406 X_LAST_UPDATED_BY => f_luby,
407 X_LAST_UPDATE_LOGIN => l_user_id,
408 X_USERTYPE_SHORTNAME => X_USERTYPE_SHORTNAME,
409 X_DISPLAY_ORDER => X_DISPLAY_ORDER
410 );
411
412 else
413
417 where USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, X_EFFECTIVE_START_DATE);
414 select LAST_UPDATED_BY, DISPLAY_ORDER, USERTYPE_SHORTNAME
415 into v_db_owner_id, v_db_display_order, v_db_usertype_shortname
416 from JTF_UM_USERTYPES_VL
418
419 if
420 ((v_db_display_order is NULL) AND
421 (v_db_usertype_shortname = 'CHANGE ME IN THE ADMIN CONSOLEx_ USERTYPE SETUP SCREEN') ) then
422
423 UPDATE_ROW_SPECIAL(
424 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, X_EFFECTIVE_START_DATE),
425 X_USERTYPE_NAME => x_usertype_name,
426 X_USERTYPE_SHORTNAME => NVL(x_usertype_shortname, 'CHANGE ME IN THE ADMIN CONSOLE: USERTYPE SETUP SCREEN'),
427 X_DISPLAY_ORDER => x_display_order,
428 X_OWNER => x_owner);
429
430 end if;
431 end if;
432 end if;
433 end LOAD_ROW;
434
435 FUNCTION IS_TEMPLATE_ASSIGNED(X_USERTYPE_ID NUMBER, X_TEMPLATE_ID NUMBER) RETURN BOOLEAN IS
436 l_dummy NUMBER;
437 CURSOR C IS SELECT USERTYPE_ID FROM JTF_UM_USERTYPE_TMPL WHERE USERTYPE_ID = X_USERTYPE_ID AND TEMPLATE_ID = X_TEMPLATE_ID AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE);
438 begin
439 open c;
440 fetch c into l_dummy;
441 if(c%NOTFOUND) then
442 return (false);
443 else
444 return (true);
445 end if;
446 close c;
447 end IS_TEMPLATE_ASSIGNED;
448
449
450 procedure REMOVE_TEMPLATE_ASSIGNMENT(
451 X_USERTYPE_ID IN NUMBER
452 ) is
453 begin
454
455 UPDATE JTF_UM_USERTYPE_TMPL SET
456 EFFECTIVE_END_DATE = SYSDATE,
457 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
458 LAST_UPDATE_DATE= SYSDATE
459 WHERE USERTYPE_ID = X_USERTYPE_ID;
460
461 end REMOVE_TEMPLATE_ASSIGNMENT;
462
463 procedure CREATE_TEMPLATE_ASSIGNMENT(
464 X_USERTYPE_ID IN NUMBER,
465 X_TEMPLATE_ID IN NUMBER,
466 X_EFFECTIVE_START_DATE IN DATE DEFAULT SYSDATE,
467 X_EFFECTIVE_END_DATE IN DATE DEFAULT NULL,
468 X_CREATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
469 X_LAST_UPDATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID
470 ) is
471 begin
472
473 INSERT INTO JTF_UM_USERTYPE_TMPL(
474 USERTYPE_ID,
475 TEMPLATE_ID,
476 EFFECTIVE_START_DATE,
477 EFFECTIVE_END_DATE,
478 CREATED_BY,
479 CREATION_DATE,
480 LAST_UPDATED_BY,
481 LAST_UPDATE_DATE)
482 VALUES(
483 X_USERTYPE_ID,
484 X_TEMPLATE_ID,
485 X_EFFECTIVE_START_DATE,
486 X_EFFECTIVE_END_DATE,
487 X_CREATED_BY,
488 SYSDATE,
489 X_LAST_UPDATED_BY,
490 SYSDATE
491 );
492 end CREATE_TEMPLATE_ASSIGNMENT;
493
494 procedure ASSOCIATE_TEMPLATE(
495 X_USERTYPE_ID IN NUMBER,
496 X_TEMPLATE_ID IN NUMBER
497 ) is
498 begin
499
500 IF NOT IS_TEMPLATE_ASSIGNED(X_USERTYPE_ID, X_TEMPLATE_ID) THEN
501 REMOVE_TEMPLATE_ASSIGNMENT(X_USERTYPE_ID);
502 CREATE_TEMPLATE_ASSIGNMENT(X_USERTYPE_ID, X_TEMPLATE_ID);
503 END IF;
504
505 end ASSOCIATE_TEMPLATE;
506
507 procedure UPDATE_TEMPLATE_ASSIGNMENT(
508 X_USERTYPE_ID IN NUMBER,
509 X_TEMPLATE_ID IN NUMBER,
510 X_EFFECTIVE_START_DATE IN DATE,
511 X_EFFECTIVE_END_DATE IN DATE,
512 X_LAST_UPDATE_DATE IN DATE,
513 X_LAST_UPDATED_BY IN NUMBER,
514 X_LAST_UPDATE_LOGIN IN NUMBER
515 ) is
516 begin
517 update JTF_UM_USERTYPE_TMPL
518 set EFFECTIVE_END_DATE=X_EFFECTIVE_END_DATE,
519 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
520 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
521 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
522 where USERTYPE_ID = X_USERTYPE_ID
523 and TEMPLATE_ID = X_TEMPLATE_ID
524 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
525
526 end UPDATE_TEMPLATE_ASSIGNMENT;
527
528 procedure LOAD_USERTYPE_TMPL_ROW(
529 X_USERTYPE_ID IN NUMBER,
530 X_TEMPLATE_ID IN NUMBER,
531 X_EFFECTIVE_START_DATE IN DATE,
532 X_EFFECTIVE_END_DATE IN DATE,
533 X_OWNER IN VARCHAR2,
534 x_last_update_date in varchar2 default NULL,
535 X_CUSTOM_MODE in varchar2 default NULL
536 ) is
537 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
538 h_record_exists NUMBER := 0;
539 f_luby number; -- entity owner in file
540 f_ludate date; -- entity update date in file
541 db_luby number; -- entity owner in db
542 db_ludate date; -- entity update date in db
543 begin
544 -- if (x_owner = 'SEED') then
545 -- l_user_id := 1;
546 -- end if;
547
548 select count(*)
549 into h_record_exists
550 from jtf_UM_USERTYPE_TMPL
551 where USERTYPE_ID = X_USERTYPE_ID
552 and TEMPLATE_ID = X_TEMPLATE_ID
553 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
554
555 -- Translate owner to file_last_updated_by
556 f_luby := fnd_load_util.owner_id(x_owner);
557
558 -- Translate char last_update_date to date
559 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
560
564
561
562
563 -- TRY update, and if it fails, insert
565 if ( h_record_exists = 0 ) then
566 CREATE_TEMPLATE_ASSIGNMENT(
567 X_USERTYPE_ID => X_USERTYPE_ID,
568 X_TEMPLATE_ID => X_TEMPLATE_ID,
569 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
570 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
571 X_CREATED_BY => f_luby,
572 X_LAST_UPDATED_BY => f_luby
573 );
574 else
575 -- This select stmnt also checks if
576 -- there is a row for this app_id and this app_short_name
577 -- Exception is thrown otherwise.
578 select LAST_UPDATED_BY, LAST_UPDATE_DATE
579 into db_luby, db_ludate
580 FROM JTF_UM_USERTYPE_TMPL
581 where USERTYPE_ID = X_USERTYPE_ID
582 and TEMPLATE_ID = X_TEMPLATE_ID
583 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
584
585 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
586 db_ludate, X_CUSTOM_MODE)) then
587 UPDATE_TEMPLATE_ASSIGNMENT(
588 X_USERTYPE_ID => X_USERTYPE_ID,
589 X_TEMPLATE_ID => X_TEMPLATE_ID,
590 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
591 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
592 X_LAST_UPDATE_DATE => f_ludate,
593 X_LAST_UPDATED_BY => f_luby,
594 X_LAST_UPDATE_LOGIN => l_user_id
595 );
596 end if;
597
598 end if;
599
600 end LOAD_USERTYPE_TMPL_ROW;
601
602 -- USERTYPE - SUBSCRIPTION ASSIGNMENT
603
604 procedure REMOVE_SUBSCRIPTION_ASSIGNMENT(
605 X_USERTYPE_ID IN NUMBER,
606 X_SUBSCRIPTION_ID IN NUMBER
607 ) is
608 begin
609
610 UPDATE JTF_UM_USERTYPE_SUBSCRIP SET
611 EFFECTIVE_END_DATE = SYSDATE,
612 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
613 LAST_UPDATE_DATE= SYSDATE
614 WHERE USERTYPE_ID = X_USERTYPE_ID
615 AND SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
616
617 end REMOVE_SUBSCRIPTION_ASSIGNMENT;
618
619 procedure CREATE_SUBSCRIPTION_ASSIGNMENT(
620 X_USERTYPE_ID NUMBER,
621 X_SUBSCRIPTION_ID NUMBER,
622 X_SUBSCRIPTION_FLAG VARCHAR2,
623 X_DISPLAY_ORDER NUMBER,
624 X_EFFECTIVE_START_DATE IN DATE DEFAULT SYSDATE,
625 X_EFFECTIVE_END_DATE IN DATE DEFAULT NULL,
626 X_CREATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
627 X_LAST_UPDATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID
628 ) is
629 begin
630 INSERT INTO JTF_UM_USERTYPE_SUBSCRIP(
631 USERTYPE_ID,
632 SUBSCRIPTION_ID,
633 SUBSCRIPTION_FLAG,
634 SUBSCRIPTION_DISPLAY_ORDER,
635 EFFECTIVE_START_DATE,
636 EFFECTIVE_END_DATE,
637 CREATED_BY,
638 CREATION_DATE,
639 LAST_UPDATED_BY,
640 LAST_UPDATE_DATE)
641 VALUES(
642 X_USERTYPE_ID,
643 X_SUBSCRIPTION_ID,
644 X_SUBSCRIPTION_FLAG,
645 X_DISPLAY_ORDER,
646 X_EFFECTIVE_START_DATE,
647 X_EFFECTIVE_END_DATE,
648 X_CREATED_BY,
649 SYSDATE,
650 X_LAST_UPDATED_BY,
651 SYSDATE
652 );
653 end CREATE_SUBSCRIPTION_ASSIGNMENT;
654
655 procedure UPDATE_SUBSCRIPTION_ASSIGNMENT(
656 X_USERTYPE_ID NUMBER,
657 X_SUBSCRIPTION_ID NUMBER,
658 X_SUBSCRIPTION_FLAG VARCHAR2,
659 X_DISPLAY_ORDER NUMBER
660 ) is
661 begin
662 update JTF_UM_USERTYPE_SUBSCRIP
663 set SUBSCRIPTION_FLAG=X_SUBSCRIPTION_FLAG,
664 SUBSCRIPTION_DISPLAY_ORDER = X_DISPLAY_ORDER
665 where USERTYPE_ID = X_USERTYPE_ID
666 and SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
667
668 end UPDATE_SUBSCRIPTION_ASSIGNMENT;
669
670 procedure UPDATE_SUBSCRIPTION_ASSIGNMENT(
671 X_USERTYPE_ID IN NUMBER,
672 X_SUBSCRIPTION_ID IN NUMBER,
673 X_SUBSCRIPTION_FLAG IN VARCHAR2,
674 X_DISPLAY_ORDER IN NUMBER,
675 X_EFFECTIVE_START_DATE IN DATE,
676 X_EFFECTIVE_END_DATE IN DATE,
677 X_LAST_UPDATE_DATE IN DATE,
678 X_LAST_UPDATED_BY IN NUMBER,
679 X_LAST_UPDATE_LOGIN IN NUMBER
680 ) is
681 begin
682 update JTF_UM_USERTYPE_SUBSCRIP
683 set SUBSCRIPTION_FLAG=X_SUBSCRIPTION_FLAG,
684 SUBSCRIPTION_DISPLAY_ORDER = X_DISPLAY_ORDER,
685 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
686 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
687 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
688 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
689 where USERTYPE_ID = X_USERTYPE_ID
690 and SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
691 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
692
693 end UPDATE_SUBSCRIPTION_ASSIGNMENT;
694
695
696 procedure LOAD_USERTYPES_SUB_ROW(
697 X_USERTYPE_ID IN NUMBER,
698 X_SUBSCRIPTION_ID IN NUMBER,
699 X_EFFECTIVE_START_DATE IN DATE,
700 X_EFFECTIVE_END_DATE IN DATE,
704 x_last_update_date in varchar2 default NULL,
701 X_SUBSCRIPTION_FLAG IN VARCHAR2,
702 X_DISPLAY_ORDER IN NUMBER,
703 X_OWNER IN VARCHAR2,
705 X_CUSTOM_MODE in varchar2 default NULL
706 ) is
707 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
708 h_record_exists NUMBER := 0;
709 f_luby number; -- entity owner in file
710 f_ludate date; -- entity update date in file
711 db_luby number; -- entity owner in db
712 db_ludate date; -- entity update date in db
713 begin
714 -- if (x_owner = 'SEED') then
715 -- l_user_id := 1;
716 -- end if;
717
718 select count(*)
719 into h_record_exists
720 from jtf_UM_USERTYPE_SUBSCRIP
721 where USERTYPE_ID = X_USERTYPE_ID
722 and SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
723 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
724
725 -- Translate owner to file_last_updated_by
726 f_luby := fnd_load_util.owner_id(x_owner);
727
728 -- Translate char last_update_date to date
729 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
730
731
732 -- TRY update, and if it fails, insert
733
734 if ( h_record_exists = 0 ) then
735 CREATE_SUBSCRIPTION_ASSIGNMENT(
736 X_USERTYPE_ID => X_USERTYPE_ID,
737 X_SUBSCRIPTION_ID => X_SUBSCRIPTION_ID,
738 X_SUBSCRIPTION_FLAG => X_SUBSCRIPTION_FLAG,
739 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
740 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
741 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
742 X_CREATED_BY => f_luby,
743 X_LAST_UPDATED_BY => f_luby
744 );
745 else
746 -- This select stmnt also checks if
747 -- there is a row for this app_id and this app_short_name
748 -- Exception is thrown otherwise.
749 select LAST_UPDATED_BY, LAST_UPDATE_DATE
750 into db_luby, db_ludate
751 FROM JTF_UM_USERTYPE_SUBSCRIP
752 where USERTYPE_ID = X_USERTYPE_ID
753 and SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
754 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
755
756 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
757 db_ludate, X_CUSTOM_MODE)) then
758 UPDATE_SUBSCRIPTION_ASSIGNMENT(
759 X_USERTYPE_ID => X_USERTYPE_ID,
760 X_SUBSCRIPTION_ID => X_SUBSCRIPTION_ID,
761 X_SUBSCRIPTION_FLAG => X_SUBSCRIPTION_FLAG,
762 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
763 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
764 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
765 X_LAST_UPDATE_DATE => f_ludate,
766 X_LAST_UPDATED_BY => f_luby,
767 X_LAST_UPDATE_LOGIN => l_user_id
768 );
769 end if;
770
771 end if;
772
773 end LOAD_USERTYPES_SUB_ROW;
774
775 procedure TRANSLATE_ROW (
776 X_USERTYPE_ID in NUMBER, -- key field
777 X_USERTYPE_NAME in VARCHAR2, -- translated name
778 X_DESCRIPTION in VARCHAR2, -- translated description
779 X_USERTYPE_SHORTNAME in VARCHAR2,
780 X_OWNER in VARCHAR2, -- owner field
781 x_last_update_date in varchar2 default NULL,
782 X_CUSTOM_MODE in varchar2 default NULL
783 )
784
785 is
786 f_luby number; -- entity owner in file
787 f_ludate date; -- entity update date in file
788 db_luby number; -- entity owner in db
789 db_ludate date; -- entity update date in db
790
791
792 begin
793
794 -- Translate owner to file_last_updated_by
795 f_luby := fnd_load_util.owner_id(x_owner);
796
797 -- Translate char last_update_date to date
798 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
799
800 -- This select stmnt also checks if
801 -- there is a row for this app_id and this app_short_name
802 -- Exception is thrown otherwise.
803 select LAST_UPDATED_BY, LAST_UPDATE_DATE
804 into db_luby, db_ludate
805 FROM JTF_UM_USERTYPES_TL
806 where USERTYPE_ID = X_USERTYPE_ID
807 and LANGUAGE = userenv('LANG');
808
809 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
810 db_ludate, X_CUSTOM_MODE)) then
811
812 update JTF_UM_USERTYPES_TL set
813 USERTYPE_NAME = X_USERTYPE_NAME,
814 DESCRIPTION = X_DESCRIPTION,
815 LAST_UPDATE_DATE = f_ludate,
816 LAST_UPDATED_BY = f_luby,
817 LAST_UPDATE_LOGIN = 0,
818 USERTYPE_SHORTNAME = X_USERTYPE_SHORTNAME,
819 SOURCE_LANG = userenv('LANG')
820 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
821 and USERTYPE_ID = X_USERTYPE_ID;
822
823 end if;
824
825 end TRANSLATE_ROW;
826
827 procedure INSERT_UMREG_ROW (
828 X_USERTYPE_ID in NUMBER,
829 X_LAST_APPROVER_COMMENT in VARCHAR2,
830 X_APPROVER_USER_ID in NUMBER,
831 X_EFFECTIVE_END_DATE in DATE,
832 X_WF_ITEM_TYPE in VARCHAR2,
833 X_EFFECTIVE_START_DATE in DATE,
834 X_USERTYPE_REG_ID out NOCOPY NUMBER,
835 X_USER_ID in NUMBER,
836 X_STATUS_CODE in VARCHAR2,
837 X_CREATION_DATE in DATE,
838 X_CREATED_BY in NUMBER,
839 X_LAST_UPDATE_DATE in DATE,
840 X_LAST_UPDATED_BY in NUMBER,
841 X_LAST_UPDATE_LOGIN in NUMBER
842 ) is
843
844 lcnt NUMBER ;
845
846 begin
847
848 -- Changes for 4287135
849 -- Check if there are any valid records for this customer
850 -- If any PENDING records are present no new records
851 -- for this FND User ID is possible
852
853 SELECT COUNT(*) INTO lcnt
854 FROM jtf_um_usertype_reg
855 WHERE user_id =X_USER_ID and status_code in ( 'PENDING', 'UPGRADE_APPROVAL_PENDING') --- Changes done for Bug 7291138 / bug 6617457
856 AND NVL(effective_end_date, SYSDATE + 1) > SYSDATE;
857
858 IF lcnt > 0 THEN
859 raise_application_error(-20001, ' WEB REGISTRATION PENDING FOR THIS FND USER ID ' || x_user_id );
860 END IF;
861
862 -- As we allow re-registration (update in bug 4287135 )
863 -- any previous approved entry has to be end dated.
864 UPDATE jtf_um_usertype_reg
865 SET effective_end_date=SYSDATE
866 WHERE user_id =X_USER_ID and status_code in ( 'APPROVED', 'UPGRADE') --- Changes done for Bug 7291138 / bug 6617457
867 AND NVL(effective_end_date, SYSDATE + 1) > SYSDATE;
868
869 -- End of changes for 4287135
870
871 insert into JTF_UM_USERTYPE_REG (
872 LAST_APPROVER_COMMENT,
873 APPROVER_USER_ID,
874 EFFECTIVE_END_DATE,
875 WF_ITEM_TYPE,
876 EFFECTIVE_START_DATE,
877 USERTYPE_REG_ID,
878 USERTYPE_ID,
879 USER_ID,
880 STATUS_CODE,
881 CREATION_DATE,
882 CREATED_BY,
883 LAST_UPDATE_DATE,
884 LAST_UPDATED_BY,
885 LAST_UPDATE_LOGIN
886 ) values (
887 X_LAST_APPROVER_COMMENT,
888 X_APPROVER_USER_ID,
889 X_EFFECTIVE_END_DATE,
890 X_WF_ITEM_TYPE,
891 X_EFFECTIVE_START_DATE,
892 JTF_UM_UT_SUBSC_REG_S.NEXTVAL,
893 X_USERTYPE_ID,
894 X_USER_ID,
895 X_STATUS_CODE,
896 X_CREATION_DATE,
897 X_CREATED_BY,
898 X_LAST_UPDATE_DATE,
899 X_LAST_UPDATED_BY,
900 X_LAST_UPDATE_LOGIN
901 ) RETURNING USERTYPE_REG_ID INTO X_USERTYPE_REG_ID;
902 end INSERT_UMREG_ROW;
903
904 procedure UPDATE_ROW_SPECIAL (
905 X_OWNER in VARCHAR2,
906 X_USERTYPE_ID in NUMBER,
907 X_USERTYPE_NAME in VARCHAR2,
908 X_USERTYPE_SHORTNAME in VARCHAR2,
909 X_DISPLAY_ORDER in NUMBER
910 ) is
911 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
912 begin
913 --if (x_owner = 'SEED') then
914 -- l_user_id := 1;
915 --end if;
916 update JTF_UM_USERTYPES_B set
917 LAST_UPDATE_LOGIN = l_user_id,
918 LAST_UPDATE_DATE = SYSDATE,
919 DISPLAY_ORDER = X_DISPLAY_ORDER
920 where USERTYPE_ID = X_USERTYPE_ID;
921
922 if (sql%notfound) then
923 raise no_data_found;
924 end if;
925
926 update JTF_UM_USERTYPES_TL set
927 USERTYPE_NAME = X_USERTYPE_NAME,
928 LAST_UPDATED_BY = l_user_id,
929 LAST_UPDATE_LOGIN = l_user_id,
930 LAST_UPDATE_DATE = SYSDATE,
931 SOURCE_LANG = userenv('LANG'),
932 USERTYPE_SHORTNAME = X_USERTYPE_SHORTNAME
933 where USERTYPE_ID = X_USERTYPE_ID
934 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
935
936 if (sql%notfound) then
937 raise no_data_found;
938 end if;
939 end UPDATE_ROW_SPECIAL;
940
941 end JTF_UM_USERTYPES_PKG;