1 package body JTF_UM_SUBSCRIPTIONS_PKG as
2 /* $Header: JTFUMSBB.pls 120.4 2006/01/16 01:26:40 vimohan ship $ */
3 MODULE_NAME CONSTANT VARCHAR2(50) := 'JTF.UM.PLSQL.JTF_UM_SUBSCRIPTIONS_PKG';
4 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME);
5
6 procedure INSERT_ROW (
7 X_SUBSCRIPTION_ID out NOCOPY NUMBER,
8 X_APPLICATION_ID in NUMBER,
9 X_EFFECTIVE_START_DATE in DATE,
10 X_SUBSCRIPTION_KEY in VARCHAR2,
11 X_ENABLED_FLAG in VARCHAR2,
12 X_EFFECTIVE_END_DATE in DATE,
13 X_APPROVAL_ID in NUMBER,
14 X_PARENT_SUBSCRIPTION_ID in NUMBER,
15 X_AVAILABILITY_CODE in VARCHAR2,
16 X_LOGON_DISPLAY_FREQUENCY in NUMBER,
17 X_SUBSCRIPTION_NAME in VARCHAR2,
18 X_DESCRIPTION in VARCHAR2,
19 X_CREATION_DATE in DATE,
20 X_CREATED_BY in NUMBER,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_LOGIN in NUMBER,
24 X_AUTH_DELEGATION_ROLE_ID in NUMBER
25 )
26 is
27 cursor C is select ROWID from JTF_UM_SUBSCRIPTIONS_B
28 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
29 ;
30
31 begin
32 insert into JTF_UM_SUBSCRIPTIONS_B (
33 APPLICATION_ID,
34 EFFECTIVE_START_DATE,
35 SUBSCRIPTION_ID,
36 SUBSCRIPTION_KEY,
37 ENABLED_FLAG,
38 EFFECTIVE_END_DATE,
39 APPROVAL_ID,
40 PARENT_SUBSCRIPTION_ID,
41 AVAILABILITY_CODE,
42 LOGON_DISPLAY_FREQUENCY,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN,
48 AUTH_DELEGATION_ROLE_ID
49 ) values (
50 X_APPLICATION_ID,
51 X_EFFECTIVE_START_DATE,
52 JTF_UM_SUBSCRIPTIONS_B_S.NEXTVAL,
53 X_SUBSCRIPTION_KEY,
54 X_ENABLED_FLAG,
55 X_EFFECTIVE_END_DATE,
56 X_APPROVAL_ID,
57 X_PARENT_SUBSCRIPTION_ID,
58 X_AVAILABILITY_CODE,
59 X_LOGON_DISPLAY_FREQUENCY,
60 X_CREATION_DATE,
61 X_CREATED_BY,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_LOGIN,
65 X_AUTH_DELEGATION_ROLE_ID
66 )RETURNING SUBSCRIPTION_ID INTO X_SUBSCRIPTION_ID;
67
68 insert into JTF_UM_SUBSCRIPTIONS_TL (
69 LAST_UPDATED_BY,
70 CREATION_DATE,
71 CREATED_BY,
72 LAST_UPDATE_LOGIN,
73 LAST_UPDATE_DATE,
74 DESCRIPTION,
75 APPLICATION_ID,
76 SUBSCRIPTION_ID,
77 SUBSCRIPTION_NAME,
78 LANGUAGE,
79 SOURCE_LANG
80 ) select
81 X_LAST_UPDATED_BY,
82 X_CREATION_DATE,
83 X_CREATED_BY,
84 X_LAST_UPDATE_LOGIN,
85 X_LAST_UPDATE_DATE,
86 X_DESCRIPTION,
87 X_APPLICATION_ID,
88 X_SUBSCRIPTION_ID,
89 X_SUBSCRIPTION_NAME,
90 L.LANGUAGE_CODE,
91 userenv('LANG')
92 from FND_LANGUAGES L
93 where L.INSTALLED_FLAG in ('I', 'B')
94 and not exists
95 (select NULL
96 from JTF_UM_SUBSCRIPTIONS_TL T
97 where T.SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
98 and T.LANGUAGE = L.LANGUAGE_CODE);
99
100 open c;
101 if (c%notfound) then
102 close c;
103 raise no_data_found;
104 end if;
105 close c;
106
107 end INSERT_ROW;
108
109 procedure LOCK_ROW (
110 X_SUBSCRIPTION_ID in NUMBER,
111 X_APPLICATION_ID in NUMBER,
112 X_EFFECTIVE_START_DATE in DATE,
113 X_SUBSCRIPTION_KEY in VARCHAR2,
114 X_ENABLED_FLAG in VARCHAR2,
115 X_EFFECTIVE_END_DATE in DATE,
116 X_APPROVAL_ID in NUMBER,
117 X_PARENT_SUBSCRIPTION_ID in NUMBER,
118 X_AVAILABILITY_CODE in VARCHAR2,
119 X_LOGON_DISPLAY_FREQUENCY in NUMBER,
120 X_SUBSCRIPTION_NAME in VARCHAR2,
121 X_DESCRIPTION in VARCHAR2,
122 X_AUTH_DELEGATION_ROLE_ID in NUMBER
123 ) is
124 cursor c is select
125 APPLICATION_ID,
126 EFFECTIVE_START_DATE,
127 SUBSCRIPTION_KEY,
128 ENABLED_FLAG,
129 EFFECTIVE_END_DATE,
130 APPROVAL_ID,
131 PARENT_SUBSCRIPTION_ID,
132 AVAILABILITY_CODE,
133 LOGON_DISPLAY_FREQUENCY,
134 AUTH_DELEGATION_ROLE_ID
135 from JTF_UM_SUBSCRIPTIONS_B
136 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
137 for update of SUBSCRIPTION_ID nowait;
138 recinfo c%rowtype;
139
140 cursor c1 is select
141 SUBSCRIPTION_NAME,
142 DESCRIPTION,
143 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
144 from JTF_UM_SUBSCRIPTIONS_TL
145 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
146 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
147 for update of SUBSCRIPTION_ID nowait;
148 begin
149 open c;
150 fetch c into recinfo;
151 if (c%notfound) then
152 close c;
153 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
154 app_exception.raise_exception;
155 end if;
156 close c;
157 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
158 AND (recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
159 AND (recinfo.SUBSCRIPTION_KEY = X_SUBSCRIPTION_KEY)
160 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
161 AND ((recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
162 OR ((recinfo.EFFECTIVE_END_DATE is null) AND (X_EFFECTIVE_END_DATE is null)))
163 AND ((recinfo.APPROVAL_ID = X_APPROVAL_ID)
164 OR ((recinfo.APPROVAL_ID is null) AND (X_APPROVAL_ID is null)))
165 AND ((recinfo.PARENT_SUBSCRIPTION_ID = X_PARENT_SUBSCRIPTION_ID)
166 OR ((recinfo.PARENT_SUBSCRIPTION_ID is null) AND (X_PARENT_SUBSCRIPTION_ID is null)))
167 AND ((recinfo.AVAILABILITY_CODE = X_AVAILABILITY_CODE)
168 OR ((recinfo.AVAILABILITY_CODE is null) AND (X_AVAILABILITY_CODE is null)))
169 AND ((recinfo.LOGON_DISPLAY_FREQUENCY = X_LOGON_DISPLAY_FREQUENCY)
170 OR ((recinfo.LOGON_DISPLAY_FREQUENCY is null) AND (X_LOGON_DISPLAY_FREQUENCY is null)))
171 ) then
172 null;
173 else
174 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
175 app_exception.raise_exception;
176 end if;
177
178 for tlinfo in c1 loop
179 if (tlinfo.BASELANG = 'Y') then
180 if ( (tlinfo.SUBSCRIPTION_NAME = X_SUBSCRIPTION_NAME)
181 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
182 ) then
183 null;
184 else
185 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
186 app_exception.raise_exception;
187 end if;
188 end if;
189 end loop;
190 return;
191 end LOCK_ROW;
192
193 procedure UPDATE_ROW (
194 X_SUBSCRIPTION_ID in NUMBER,
195 X_APPLICATION_ID in NUMBER,
196 X_SUBSCRIPTION_KEY in VARCHAR2,
197 X_ENABLED_FLAG in VARCHAR2,
198 X_EFFECTIVE_END_DATE in DATE,
199 X_APPROVAL_ID in NUMBER,
200 X_PARENT_SUBSCRIPTION_ID in NUMBER,
201 X_AVAILABILITY_CODE in VARCHAR2,
202 X_LOGON_DISPLAY_FREQUENCY in NUMBER,
203 X_SUBSCRIPTION_NAME in VARCHAR2,
204 X_DESCRIPTION in VARCHAR2,
205 X_LAST_UPDATE_DATE in DATE,
206 X_LAST_UPDATED_BY in NUMBER,
207 X_LAST_UPDATE_LOGIN in NUMBER,
208 X_AUTH_DELEGATION_ROLE_ID in NUMBER
209 ) is
210 begin
211 update JTF_UM_SUBSCRIPTIONS_B set
212 APPLICATION_ID = X_APPLICATION_ID,
213 SUBSCRIPTION_KEY = X_SUBSCRIPTION_KEY,
214 ENABLED_FLAG = X_ENABLED_FLAG,
215 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
216 APPROVAL_ID = X_APPROVAL_ID,
217 PARENT_SUBSCRIPTION_ID = X_PARENT_SUBSCRIPTION_ID,
218 AVAILABILITY_CODE = X_AVAILABILITY_CODE,
219 LOGON_DISPLAY_FREQUENCY = X_LOGON_DISPLAY_FREQUENCY,
220 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
223 AUTH_DELEGATION_ROLE_ID = X_AUTH_DELEGATION_ROLE_ID
224 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229
230 update JTF_UM_SUBSCRIPTIONS_TL set
231 SUBSCRIPTION_NAME = X_SUBSCRIPTION_NAME,
232 DESCRIPTION = X_DESCRIPTION,
233 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
234 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
235 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
236 SOURCE_LANG = userenv('LANG')
237 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
238 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243 end UPDATE_ROW;
244
245 procedure DELETE_ROW (
246 X_SUBSCRIPTION_ID in NUMBER
247 ) is
248 begin
249 delete from JTF_UM_SUBSCRIPTIONS_TL
250 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
251
252 if (sql%notfound) then
253 raise no_data_found;
254 end if;
255
256 delete from JTF_UM_SUBSCRIPTIONS_B
257 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
258
259 if (sql%notfound) then
260 raise no_data_found;
261 end if;
262 end DELETE_ROW;
263
264 procedure ADD_LANGUAGE
265 is
266 begin
267 delete from JTF_UM_SUBSCRIPTIONS_TL T
268 where not exists
269 (select NULL
270 from JTF_UM_SUBSCRIPTIONS_B B
271 where B.SUBSCRIPTION_ID = T.SUBSCRIPTION_ID
272 );
273
274 update JTF_UM_SUBSCRIPTIONS_TL T set (
275 SUBSCRIPTION_NAME,
276 DESCRIPTION
277 ) = (select
278 B.SUBSCRIPTION_NAME,
279 B.DESCRIPTION
280 from JTF_UM_SUBSCRIPTIONS_TL B
281 where B.SUBSCRIPTION_ID = T.SUBSCRIPTION_ID
282 and B.LANGUAGE = T.SOURCE_LANG)
283 where (
284 T.SUBSCRIPTION_ID,
285 T.LANGUAGE
286 ) in (select
287 SUBT.SUBSCRIPTION_ID,
288 SUBT.LANGUAGE
289 from JTF_UM_SUBSCRIPTIONS_TL SUBB, JTF_UM_SUBSCRIPTIONS_TL SUBT
290 where SUBB.SUBSCRIPTION_ID = SUBT.SUBSCRIPTION_ID
291 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
292 and (SUBB.SUBSCRIPTION_NAME <> SUBT.SUBSCRIPTION_NAME
293 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
294 ));
295
296 insert into JTF_UM_SUBSCRIPTIONS_TL (
297 LAST_UPDATED_BY,
298 CREATION_DATE,
299 CREATED_BY,
300 LAST_UPDATE_LOGIN,
301 LAST_UPDATE_DATE,
302 DESCRIPTION,
303 APPLICATION_ID,
304 SUBSCRIPTION_ID,
305 SUBSCRIPTION_NAME,
306 LANGUAGE,
307 SOURCE_LANG
308 ) select /*+ ORDERED */
309 B.LAST_UPDATED_BY,
310 B.CREATION_DATE,
311 B.CREATED_BY,
312 B.LAST_UPDATE_LOGIN,
313 B.LAST_UPDATE_DATE,
314 B.DESCRIPTION,
315 B.APPLICATION_ID,
316 B.SUBSCRIPTION_ID,
317 B.SUBSCRIPTION_NAME,
318 L.LANGUAGE_CODE,
319 B.SOURCE_LANG
320 from JTF_UM_SUBSCRIPTIONS_TL B, FND_LANGUAGES L
321 where L.INSTALLED_FLAG in ('I', 'B')
322 and B.LANGUAGE = userenv('LANG')
323 and not exists
324 (select NULL
325 from JTF_UM_SUBSCRIPTIONS_TL T
326 where T.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
327 and T.LANGUAGE = L.LANGUAGE_CODE);
328 end ADD_LANGUAGE;
329
330
331 --For this procedure, if SUBSCRIPTION_ID passed as input is NULL, then create a new record
332 -- otherwise, modify the existing record.
333
334 procedure LOAD_ROW (
335 X_SUBSCRIPTION_ID IN NUMBER,
336 X_EFFECTIVE_START_DATE IN DATE,
337 X_EFFECTIVE_END_DATE IN DATE,
338 X_OWNER IN VARCHAR2,
339 X_APPROVAL_ID IN NUMBER,
340 X_APPLICATION_ID IN NUMBER,
341 X_ENABLED_FLAG IN VARCHAR2,
342 X_PARENT_SUBSCRIPTION_ID IN NUMBER,
343 X_AVAILABILITY_CODE IN VARCHAR2,
344 X_LOGON_DISPLAY_FREQUENCY IN NUMBER,
345 X_SUBSCRIPTION_KEY IN VARCHAR2,
346 X_SUBSCRIPTION_NAME IN VARCHAR2,
347 X_DESCRIPTION IN VARCHAR2,
348 X_AUTH_DELEGATION_ROLE_ID IN NUMBER,
349 x_last_update_date in varchar2 default NULL,
350 X_CUSTOM_MODE in varchar2 default NULL
351 ) is
352 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
353 l_subscription_id NUMBER := 0;
354 f_luby number; -- entity owner in file
355 f_ludate date; -- entity update date in file
356 db_luby number; -- entity owner in db
357 db_ludate date; -- entity update date in db
358
359 begin
360 -- if (x_owner = 'SEED') then
361 -- l_user_id := 1;
362 -- end if;
363
364
365 -- Translate owner to file_last_updated_by
366 f_luby := fnd_load_util.owner_id(x_owner);
367
368 -- Translate char last_update_date to date
369 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
370
371 -- If SUBSCRIPTION_ID passed in NULL, insert the record
372 if ( X_SUBSCRIPTION_ID is NULL ) THEN
373 INSERT_ROW(
374 X_SUBSCRIPTION_ID => l_subscription_id,
375 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
376 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
377 X_APPROVAL_ID => X_APPROVAL_ID,
378 X_APPLICATION_ID => X_APPLICATION_ID,
379 X_ENABLED_FLAG => X_ENABLED_FLAG,
380 X_PARENT_SUBSCRIPTION_ID => X_PARENT_SUBSCRIPTION_ID,
381 X_AVAILABILITY_CODE => X_AVAILABILITY_CODE,
382 X_LOGON_DISPLAY_FREQUENCY => X_LOGON_DISPLAY_FREQUENCY,
383 X_SUBSCRIPTION_KEY => X_SUBSCRIPTION_KEY,
384 X_SUBSCRIPTION_NAME => X_SUBSCRIPTION_NAME,
385 X_DESCRIPTION => X_DESCRIPTION,
386 X_CREATION_DATE => f_ludate,
387 X_CREATED_BY => f_luby,
388 X_LAST_UPDATE_DATE => f_ludate,
389 X_LAST_UPDATED_BY => f_luby,
390 X_LAST_UPDATE_LOGIN => l_user_id,
391 X_AUTH_DELEGATION_ROLE_ID => X_AUTH_DELEGATION_ROLE_ID
392 );
393 else
394 -- This select stmnt also checks if
395 -- there is a row for this app_id and this app_short_name
396 -- Exception is thrown otherwise.
397 select LAST_UPDATED_BY, LAST_UPDATE_DATE
398 into db_luby, db_ludate
399 FROM JTF_UM_SUBSCRIPTIONS_B
400 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
401
402 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
403 db_ludate, X_CUSTOM_MODE)) then
404
405 UPDATE_ROW(
406 X_SUBSCRIPTION_ID => X_SUBSCRIPTION_ID,
407 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
408 X_APPROVAL_ID => X_APPROVAL_ID,
409 X_APPLICATION_ID => X_APPLICATION_ID,
410 X_ENABLED_FLAG => X_ENABLED_FLAG,
411 X_PARENT_SUBSCRIPTION_ID => X_PARENT_SUBSCRIPTION_ID,
412 X_AVAILABILITY_CODE => X_AVAILABILITY_CODE,
413 X_LOGON_DISPLAY_FREQUENCY => X_LOGON_DISPLAY_FREQUENCY,
414 X_SUBSCRIPTION_KEY => X_SUBSCRIPTION_KEY,
415 X_SUBSCRIPTION_NAME => X_SUBSCRIPTION_NAME,
416 X_DESCRIPTION => X_DESCRIPTION,
417 X_LAST_UPDATE_DATE => f_ludate,
418 X_LAST_UPDATED_BY => f_luby,
419 X_LAST_UPDATE_LOGIN => l_user_id,
420 X_AUTH_DELEGATION_ROLE_ID => X_AUTH_DELEGATION_ROLE_ID
421 );
422
423 end if;
424 end if;
425
426 end LOAD_ROW;
427
428 procedure TRANSLATE_ROW (
429 X_SUBSCRIPTION_ID in NUMBER, -- key field
430 X_SUBSCRIPTION_NAME in VARCHAR2, -- translated name
431 X_DESCRIPTION in VARCHAR2, -- translated description
432 X_OWNER in VARCHAR2, -- owner field
433 x_last_update_date in varchar2 default NULL,
434 X_CUSTOM_MODE in varchar2 default NULL
435 ) is
436 f_luby number; -- entity owner in file
437 f_ludate date; -- entity update date in file
438 db_luby number; -- entity owner in db
439 db_ludate date; -- entity update date in db
440 begin
441
442 -- Translate owner to file_last_updated_by
443 f_luby := fnd_load_util.owner_id(x_owner);
444
445 -- Translate char last_update_date to date
446 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
447
448 -- This select stmnt also checks if
449 -- there is a row for this app_id and this app_short_name
450 -- Exception is thrown otherwise.
451 select LAST_UPDATED_BY, LAST_UPDATE_DATE
452 into db_luby, db_ludate
453 FROM JTF_UM_SUBSCRIPTIONS_TL
454 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
455 and SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
456
457 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
458 db_ludate, X_CUSTOM_MODE)) then
459 update JTF_UM_SUBSCRIPTIONS_TL set
460 SUBSCRIPTION_NAME = X_SUBSCRIPTION_NAME,
461 DESCRIPTION = X_DESCRIPTION,
462 LAST_UPDATE_DATE = f_ludate,
463 LAST_UPDATED_BY = f_luby,
464 LAST_UPDATE_LOGIN = 0,
465 SOURCE_LANG = userenv('LANG')
466 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
467 and SUBSCRIPTION_ID = X_SUBSCRIPTION_ID;
468 end if;
469
470 end TRANSLATE_ROW;
471
472 FUNCTION IS_TEMPLATE_ASSIGNED(X_SUBSCRIPTION_ID NUMBER, X_TEMPLATE_ID NUMBER) RETURN BOOLEAN IS
473 l_dummy NUMBER;
474 CURSOR C IS SELECT SUBSCRIPTION_ID FROM JTF_UM_SUBSCRIPTION_TMPL WHERE SUBSCRIPTION_ID = X_SUBSCRIPTION_ID AND TEMPLATE_ID = X_TEMPLATE_ID AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE);
475 begin
476 open c;
477 fetch c into l_dummy;
478 if(c%NOTFOUND) then
479 return (false);
480 else
481 return (true);
482 end if;
483 close c;
484 end IS_TEMPLATE_ASSIGNED;
485
486
487 procedure REMOVE_TEMPLATE_ASSIGNMENT(
488 X_SUBSCRIPTION_ID IN NUMBER
489 ) is
490 begin
491
492 UPDATE JTF_UM_SUBSCRIPTION_TMPL SET
493 EFFECTIVE_END_DATE = SYSDATE,
494 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
495 LAST_UPDATE_DATE= SYSDATE
496 WHERE SUBSCRIPTION_ID = X_SUBSCRIPTION_ID ;
497
498 end REMOVE_TEMPLATE_ASSIGNMENT;
499
500 procedure CREATE_TEMPLATE_ASSIGNMENT(
501 X_SUBSCRIPTION_ID IN NUMBER,
502 X_TEMPLATE_ID IN NUMBER,
503 X_EFFECTIVE_START_DATE IN DATE DEFAULT SYSDATE,
504 X_EFFECTIVE_END_DATE IN DATE DEFAULT NULL,
505 X_CREATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID,
506 X_LAST_UPDATED_BY IN NUMBER DEFAULT FND_GLOBAL.USER_ID
507 ) is
508 begin
509
510 INSERT INTO JTF_UM_SUBSCRIPTION_TMPL(
511 SUBSCRIPTION_ID,
512 TEMPLATE_ID,
513 EFFECTIVE_START_DATE,
514 EFFECTIVE_END_DATE,
515 CREATED_BY,
516 CREATION_DATE,
517 LAST_UPDATED_BY,
518 LAST_UPDATE_DATE)
519 VALUES(
520 X_SUBSCRIPTION_ID,
521 X_TEMPLATE_ID,
522 X_EFFECTIVE_START_DATE,
523 X_EFFECTIVE_END_DATE,
524 X_CREATED_BY,
525 SYSDATE,
526 X_LAST_UPDATED_BY,
527 SYSDATE
528 );
529 end CREATE_TEMPLATE_ASSIGNMENT;
530
531 procedure ASSOCIATE_TEMPLATE(
532 X_SUBSCRIPTION_ID IN NUMBER,
533 X_TEMPLATE_ID IN NUMBER
534 ) is
535 begin
536
537 IF NOT IS_TEMPLATE_ASSIGNED(X_SUBSCRIPTION_ID, X_TEMPLATE_ID) THEN
538 REMOVE_TEMPLATE_ASSIGNMENT(X_SUBSCRIPTION_ID);
539 CREATE_TEMPLATE_ASSIGNMENT(X_SUBSCRIPTION_ID, X_TEMPLATE_ID);
540 END IF;
541
542
543 end ASSOCIATE_TEMPLATE;
544
545 procedure UPDATE_TEMPLATE_ASSIGNMENT(
546 X_SUBSCRIPTION_ID IN NUMBER,
547 X_TEMPLATE_ID IN NUMBER,
548 X_EFFECTIVE_START_DATE IN DATE,
549 X_EFFECTIVE_END_DATE IN DATE,
550 X_LAST_UPDATE_DATE IN DATE,
551 X_LAST_UPDATED_BY IN NUMBER,
552 X_LAST_UPDATE_LOGIN IN NUMBER
553 ) is
554 begin
555 update JTF_UM_SUBSCRIPTION_TMPL
556 set EFFECTIVE_END_DATE=X_EFFECTIVE_END_DATE,
557 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
558 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
559 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
560 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
561 and TEMPLATE_ID = X_TEMPLATE_ID
562 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
563
564 end UPDATE_TEMPLATE_ASSIGNMENT;
565
566 procedure LOAD_SUBSCRIPTION_TMPL_ROW(
567 X_SUBSCRIPTION_ID IN NUMBER,
568 X_TEMPLATE_ID IN NUMBER,
569 X_EFFECTIVE_START_DATE IN DATE,
570 X_EFFECTIVE_END_DATE IN DATE,
571 X_OWNER IN VARCHAR2,
572 x_last_update_date in varchar2 default NULL,
573 X_CUSTOM_MODE in varchar2 default NULL
574 )
575 is
576 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
577 h_record_exists NUMBER := 0;
578 f_luby number; -- entity owner in file
579 f_ludate date; -- entity update date in file
580 db_luby number; -- entity owner in db
581 db_ludate date; -- entity update date in db
582
583 begin
584 -- if (x_owner = 'SEED') then
585 -- l_user_id := 1;
586 -- end if;
587
588 select count(*)
589 into h_record_exists
590 from jtf_UM_SUBSCRIPTION_TMPL
591 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
592 and TEMPLATE_ID = X_TEMPLATE_ID
593 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
594
595 -- Translate owner to file_last_updated_by
596 f_luby := fnd_load_util.owner_id(x_owner);
597
598 -- Translate char last_update_date to date
599 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
600
601
602 -- TRY update, and if it fails, insert
603
604 if ( h_record_exists = 0 ) then
605 CREATE_TEMPLATE_ASSIGNMENT(
606 X_SUBSCRIPTION_ID => X_SUBSCRIPTION_ID,
607 X_TEMPLATE_ID => X_TEMPLATE_ID,
608 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
609 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
610 X_CREATED_BY => f_luby,
611 X_LAST_UPDATED_BY => f_luby
612 );
613 else
614 -- This select stmnt also checks if
615 -- there is a row for this app_id and this app_short_name
616 -- Exception is thrown otherwise.
617 select LAST_UPDATED_BY, LAST_UPDATE_DATE
618 into db_luby, db_ludate
619 FROM JTF_UM_SUBSCRIPTION_TMPL
620 where SUBSCRIPTION_ID = X_SUBSCRIPTION_ID
621 and TEMPLATE_ID = X_TEMPLATE_ID
622 and EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE;
623
624 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
625 db_ludate, X_CUSTOM_MODE)) then
626
627 UPDATE_TEMPLATE_ASSIGNMENT(
628 X_SUBSCRIPTION_ID => X_SUBSCRIPTION_ID,
629 X_TEMPLATE_ID => X_TEMPLATE_ID,
630 X_EFFECTIVE_START_DATE => X_EFFECTIVE_START_DATE,
631 X_EFFECTIVE_END_DATE => X_EFFECTIVE_END_DATE,
632 X_LAST_UPDATE_DATE => f_ludate,
633 X_LAST_UPDATED_BY => f_luby,
634 X_LAST_UPDATE_LOGIN => l_user_id
635 );
636 end if;
637 end if;
638
639 end LOAD_SUBSCRIPTION_TMPL_ROW;
640
641
642
643 procedure INSERT_SUBREG_ROW (
644 X_SUBSCRIPTION_ID in NUMBER,
645 X_LAST_APPROVER_COMMENT in VARCHAR2,
646 X_APPROVER_USER_ID in NUMBER,
647 X_EFFECTIVE_END_DATE in DATE,
648 X_WF_ITEM_TYPE in VARCHAR2,
649 X_EFFECTIVE_START_DATE in DATE,
650 X_SUBSCRIPTION_REG_ID out NOCOPY NUMBER,
651 X_USER_ID in NUMBER,
652 X_STATUS_CODE in VARCHAR2,
653 X_CREATION_DATE in DATE,
654 X_CREATED_BY in NUMBER,
655 X_LAST_UPDATE_DATE in DATE,
656 X_LAST_UPDATED_BY in NUMBER,
657 X_LAST_UPDATE_LOGIN in NUMBER,
658 X_GRANT_DELEGATION_FLAG in VARCHAR2
659
660 ) is
661 begin
662 insert into JTF_UM_SUBSCRIPTION_REG (
663 LAST_APPROVER_COMMENT,
664 APPROVER_USER_ID,
665 EFFECTIVE_END_DATE,
666 WF_ITEM_TYPE,
667 EFFECTIVE_START_DATE,
668 SUBSCRIPTION_REG_ID,
669 SUBSCRIPTION_ID,
670 USER_ID,
671 STATUS_CODE,
672 CREATION_DATE,
673 CREATED_BY,
674 LAST_UPDATE_DATE,
675 LAST_UPDATED_BY,
676 LAST_UPDATE_LOGIN,
677 GRANT_DELEGATION_FLAG
678 ) values (
679 X_LAST_APPROVER_COMMENT,
680 X_APPROVER_USER_ID,
681 X_EFFECTIVE_END_DATE,
682 X_WF_ITEM_TYPE,
683 X_EFFECTIVE_START_DATE,
684 JTF_UM_UT_SUBSC_REG_S.NEXTVAL,
685 X_SUBSCRIPTION_ID,
686 X_USER_ID,
687 X_STATUS_CODE,
688 X_CREATION_DATE,
689 X_CREATED_BY,
690 X_LAST_UPDATE_DATE,
691 X_LAST_UPDATED_BY,
692 X_LAST_UPDATE_LOGIN,
693 X_GRANT_DELEGATION_FLAG
694 ) RETURNING SUBSCRIPTION_REG_ID INTO X_SUBSCRIPTION_REG_ID;
695 end INSERT_SUBREG_ROW;
696
697
698 /*
699 * Name : update_grant_delegation_flag
700 * Pre_reqs : None
701 * Description : Will update the information of the grant_delegation_flag
702 * Parameters :
703 * input parameters
704 * @param p_subscription_reg_id
705 * description: The subscription_reg_id of an enrollment
706 * required : Y
707 * validation : Must be a valid subscription_id. The procedure will not do
708 * any explicit validation.
709 * p_grant_delegation_flag:
710 * description: The Boolean value of the grant_delegation_flag
711 * required : Y
712 * validation : Should be true or false. The procedure will default it to
713 * false, if null value is passed
714 *
715 * output parameters
716 * None
717 *
718 * Notes:
719 *
720 * The procedure will try to update the grant_delegation_flag based on the input values.
721 * If a procedure can not find any matching row, then it will not raise any exception
722 * but will not update any rows. It is caller's responsibility to make sure that
723 * the correct parameters are passed
724 */
725 procedure update_grant_delegation_flag (
726 p_subscription_reg_id in number,
727 p_grant_delegation_flag in boolean
728 ) is
729
730 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
731 l_flag_value VARCHAR2(1) := 'N';
732
733 begin
734
735 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
736 p_message => l_procedure_name
737 );
738
739 if l_is_debug_parameter_on then
740 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
741 p_message => 'p_subscription_reg_id:' || p_subscription_reg_id || '+' || 'p_grant_delegation_flag:' || JTF_DBSTRING_UTILS.getBooleanString(p_grant_delegation_flag)
742 );
743 end if;
744
745
746 if p_grant_delegation_flag then
747
748 l_flag_value := 'Y';
749
750 end if;
751
752 UPDATE JTF_UM_SUBSCRIPTION_REG SET GRANT_DELEGATION_FLAG = l_flag_value
753 WHERE SUBSCRIPTION_REG_ID = p_subscription_reg_id ;
754
755 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
756 p_message => l_procedure_name
757 );
758
759 end update_grant_delegation_flag;
760
761 /*
762 * Name : update_grant_delegation_flag
763 * Pre_reqs : None
764 * Description : Will update the information of the grant_delegation_flag
765 * Parameters :
766 * input parameters
767 * @param p_subscription_reg_id
768 * description: The subscription_reg_id of an enrollment
769 * required : Y
770 * validation : Must be a valid subscription_id. The procedure will not do
771 * any explicit validation.
772 * p_grant_delegation_flag:
773 * description: The Boolean equivallent int value of the grant_delegation_flag
774 * required : Y
775 * validation : Should be 0 or 1. The procedure will default it to
776 * 0, if null value is passed
777 * p_grant_delegation_role:
778 * description: The Boolean equivallent int value of the decision
779 * whether to grant delegation role or not
780 * required : Y
781 * validation : Should be 0 or 1. The procedure will default it to
782 * 0, if null value is passed
783 *
784 * output parameters
785 * None
786 *
787 * Notes:
788 *
789 * This procedure is create as wrapper procedure to pass boolean
790 * values, as JDBC cannot handle boolean !!!!!
791 */
792 procedure update_grant_delegation_flag (
793 p_subscription_reg_id in number,
794 p_grant_delegation_flag in number,
795 p_grant_delegation_role in number
796 ) IS
797
798 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
799 l_flag_value VARCHAR2(1) := 'N';
800
801 CURSOR FIND_PRINCIPAL_NAME IS SELECT FU.USER_NAME, SUBREG.SUBSCRIPTION_ID
802 FROM FND_USER FU, JTF_UM_SUBSCRIPTION_REG SUBREG
803 WHERE FU.USER_ID = SUBREG.USER_ID
804 AND SUBREG.SUBSCRIPTION_REG_ID = p_subscription_reg_id;
805
806 l_principal_name FND_USER.USER_NAME%TYPE;
807 l_subscription_id NUMBER;
808 l_role_id NUMBER;
809
810 begin
811
812 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
813 p_message => l_procedure_name
814 );
815
816 if l_is_debug_parameter_on then
817 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
818 p_message => 'p_subscription_reg_id:' || p_subscription_reg_id || '+' || 'p_grant_delegation_flag:'
819 || p_grant_delegation_flag || '+' || 'p_grant_delegation_role:' || p_grant_delegation_role
820 );
821 end if;
822
823
824 if p_grant_delegation_flag = 1 then
825
826 l_flag_value := 'Y';
827
828 end if;
829
830 UPDATE JTF_UM_SUBSCRIPTION_REG SET GRANT_DELEGATION_FLAG = l_flag_value
831 WHERE SUBSCRIPTION_REG_ID = p_subscription_reg_id ;
832
833 -- Grant the delegation role, if required
834 IF p_grant_delegation_role = 1 AND p_grant_delegation_flag = 1 THEN
835
836 OPEN FIND_PRINCIPAL_NAME;
837 FETCH FIND_PRINCIPAL_NAME INTO l_principal_name,l_subscription_id;
838 CLOSE FIND_PRINCIPAL_NAME;
839
840 JTF_UM_SUBSCRIPTIONS_PKG.get_delegation_role(
841 p_subscription_id => l_subscription_id,
842 x_delegation_role => l_role_id
843 );
844
845
846 IF l_role_id IS NOT NULL AND l_principal_name IS NOT NULL THEN
847
848 -- Grant delegation role to a user
849 JTF_UM_UTIL_PVT.GRANT_ROLES(
850 p_user_name => l_principal_name,
851 p_role_id => l_role_id,
852 p_source_name => 'JTF_UM_SUBSCRIPTIONS_B',
853 p_source_id => l_subscription_id
854 );
855
856 -- Assign the deleagtion access role
857
858 JTF_AUTH_BULKLOAD_PKG.ASSIGN_ROLE
859 ( USER_NAME => l_principal_name,
860 ROLE_NAME => 'JTA_UM_DELEGATION_ACCESS',
861 OWNERTABLE_NAME => 'JTF_UM_SUBSCRIPTIONS_B',
862 OWNERTABLE_KEY => l_subscription_id);
863 END IF;
864
865 END IF;
866
867 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
868 p_message => l_procedure_name
869 );
870
871 END update_grant_delegation_flag;
872
873
874
875 /*
876 * Name : update_grant_delegation_flag
877 * Pre_reqs : None
878 * Description : Will update the information of the grant_delegation_flag
879 * Parameters :
880 * input parameters
881 * @param p_subscription_reg_id
882 * description: The subscription_reg_id of an enrollment
883 * required : Y
884 * validation : Must be a valid subscription_id. The procedure will not do
885 * any explicit validation.
886 * p_grant_delegation_flag:
887 * description: The Boolean equivallent int value of the grant_delegation_flag
888 * required : Y
889 * validation : Should be 0 or 1. The procedure will default it to
890 * 0, if null value is passed
891 *
892 * output parameters
893 * None
894 *
895 * Notes:
896 *
897 * This procedure is create as wrapper procedure to pass boolean
898 * values, as JDBC cannot handle boolean !!!!!
899 */
900 procedure update_grant_delegation_flag (
901 p_subscription_reg_id in number,
902 p_grant_delegation_flag in number
903 ) IS
904
905 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
906 l_grant_delegation_flag boolean := false;
907
908 begin
909
910 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
911 p_message => l_procedure_name
912 );
913
914 if l_is_debug_parameter_on then
915 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
916 p_message => 'p_subscription_reg_id:' || p_subscription_reg_id || '+' || 'p_grant_delegation_flag:' || p_grant_delegation_flag
917 );
918 end if;
919
920
921 if p_grant_delegation_flag = 1 then
922 l_grant_delegation_flag := true;
923 end if;
924
925 update_grant_delegation_flag (
926 p_subscription_reg_id => p_subscription_reg_id,
927 p_grant_delegation_flag => l_grant_delegation_flag
928 );
929
930 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
931 p_message => l_procedure_name
932 );
933
934 end update_grant_delegation_flag;
935
936
937 /*
938 * Name : update_grant_delegation_flag
939 * Pre_reqs : None
940 * Description : Will update the information of the grant_delegation_flag
941 * Parameters :
942 * input parameters
943 * @param p_subscription_id
944 * description: The subscription_id of an enrollment
945 * required : Y
946 * validation : Must be a valid subscription_id. The procedure will not do
947 * any explicit validation.
948 * p_user_name:
949 * description: The user_name of a user
950 * required : Y
951 * validation : Must be a valid user_name.The procedure will not do
952 * any explicit validation.
953 * p_grant_delegation_flag:
954 * description: The Boolean value of the grant_delegation_flag
955 * required : Y
956 * validation : Should be true or false. The procedure will default it to
957 * false, if null value is passed
958 *
959 * output parameters
960 * None
961 *
962 * Notes:
963 *
964 * The procedure will try to update the grant_delegation_flag based on the input values.
965 * If a procedure can not find any matching row, then it will not raise any exception
966 * but will not update any rows. It is caller's responsibility to make sure that
967 * the correct parameters are passed
968 */
969 procedure update_grant_delegation_flag (
970 p_subscription_id in number,
971 p_user_name in varchar2,
972 p_grant_delegation_flag in boolean
973 ) is
974
975 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
976
977 CURSOR FIND_REG_ID IS SELECT SUBSCRIPTION_REG_ID
978 FROM JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU
979 WHERE SUBSCRIPTION_ID = p_subscription_id
980 AND SUBREG.USER_ID = FU.USER_ID
981 AND FU.USER_NAME = p_user_name
982 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE;
983
984 l_subscription_reg_id JTF_UM_SUBSCRIPTION_REG.SUBSCRIPTION_REG_ID%TYPE;
985
986 begin
987
988 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
989 p_message => l_procedure_name
990 );
991
992 if l_is_debug_parameter_on then
993 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
994 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_user_name:' || p_user_name || '+' || 'p_grant_delegation_flag:' || JTF_DBSTRING_UTILS.getBooleanString(p_grant_delegation_flag)
995 );
996 end if;
997
998
999 OPEN FIND_REG_ID;
1000 FETCH FIND_REG_ID INTO l_subscription_reg_id;
1001
1002 IF FIND_REG_ID%FOUND THEN
1003 update_grant_delegation_flag (
1004 p_subscription_reg_id => l_subscription_reg_id,
1005 p_grant_delegation_flag => p_grant_delegation_flag
1006 );
1007
1008 END IF;
1009 CLOSE FIND_REG_ID;
1010
1011 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1012 p_message => l_procedure_name
1013 );
1014
1015 end update_grant_delegation_flag;
1016
1017 /*
1018 * Name : update_grant_delegation_flag
1019 * Pre_reqs : None
1020 * Description : Will update the information of the grant_delegation_flag
1021 * Parameters :
1022 * input parameters
1023 * @param p_subscription_id
1024 * description: The subscription_id of an enrollment
1025 * required : Y
1026 * validation : Must be a valid subscription_id. The procedure will not do
1027 * any explicit validation.
1028 * p_user_name:
1029 * description: The user_name of a user
1030 * required : Y
1031 * validation : Must be a valid user_name.The procedure will not do
1032 * any explicit validation.
1033 * p_grant_delegation_flag:
1034 * description: The Boolean equivallent int value of the grant_delegation_flag
1035 * required : Y
1036 * validation : Should be 0 or 1. The procedure will default it to
1037 * 0, if null value is passed
1038 *
1039 * output parameters
1040 * None
1041 *
1042 * Notes:
1043 *
1044 * This procedure is create as wrapper procedure to pass boolean
1045 * values, as JDBC cannot handle boolean !!!!!
1046 */
1047 procedure update_grant_delegation_flag (
1048 p_subscription_id in number,
1049 p_user_name in varchar2,
1050 p_grant_delegation_flag in number
1051 ) IS
1052
1053 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
1054 l_grant_delegation_flag boolean := false;
1055
1056 begin
1057
1058 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1059 p_message => l_procedure_name
1060 );
1061
1062 if l_is_debug_parameter_on then
1063 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1064 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_user_name:' || p_user_name || '+' || 'p_grant_delegation_flag:' || p_grant_delegation_flag
1065 );
1066 end if;
1067
1068
1069 if p_grant_delegation_flag = 1 then
1070 l_grant_delegation_flag := true;
1071 end if;
1072
1073 update_grant_delegation_flag (
1074 p_subscription_id => p_subscription_id,
1075 p_user_name => p_user_name,
1076 p_grant_delegation_flag => l_grant_delegation_flag
1077 );
1078
1079 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1080 p_message => l_procedure_name
1081 );
1082
1083
1084 end update_grant_delegation_flag;
1085
1086
1087
1088 /*
1089 * Name : update_grant_delegation_flag
1090 * Pre_reqs : None
1091 * Description : Will update the information of the grant_delegation_flag
1092 * Parameters :
1093 * input parameters
1094 * @param p_subscription_id
1095 * description: The subscription_id of an enrollment
1096 * required : Y
1097 * validation : Must be a valid subscription_id. The procedure will not do
1098 * any explicit validation.
1099 * p_user_id:
1100 * description: The user_id of a user
1101 * required : Y
1102 * validation : Must be a valid user_id.The procedure will not do
1103 * any explicit validation.
1104 * p_grant_delegation_flag:
1105 * description: The Boolean value of the grant_delegation_flag
1106 * required : Y
1107 * validation : Should be true or false. The procedure will default it to
1108 * false, if null value is passed
1109 *
1110 * output parameters
1111 * None
1112 *
1113 * Notes:
1114 *
1115 * The procedure will try to update the grant_delegation_flag based on the input values.
1116 * If a procedure can not find any matching row, then it will not raise any exception
1117 * but will not update any rows. It is caller's responsibility to make sure that
1118 * the correct parameters are passed
1119 */
1120 procedure update_grant_delegation_flag (
1121 p_subscription_id in number,
1122 p_user_id in number,
1123 p_grant_delegation_flag in boolean
1124 ) is
1125
1126 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
1127 CURSOR FIND_REG_ID IS SELECT SUBSCRIPTION_REG_ID
1128 FROM JTF_UM_SUBSCRIPTION_REG SUBREG, FND_USER FU
1129 WHERE SUBSCRIPTION_ID = p_subscription_id
1130 AND SUBREG.USER_ID = FU.USER_ID
1131 AND FU.USER_ID = p_user_id
1132 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE;
1133
1134 l_subscription_reg_id JTF_UM_SUBSCRIPTION_REG.SUBSCRIPTION_REG_ID%TYPE;
1135
1136 begin
1137
1138 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1139 p_message => l_procedure_name
1140 );
1141
1142 if l_is_debug_parameter_on then
1143 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1144 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_user_id:' || p_user_id || '+' || 'p_grant_delegation_flag:' || JTF_DBSTRING_UTILS.getBooleanString(p_grant_delegation_flag)
1145
1146 );
1147 end if;
1148
1149 OPEN FIND_REG_ID;
1150 FETCH FIND_REG_ID INTO l_subscription_reg_id;
1151
1152 IF FIND_REG_ID%FOUND THEN
1153 update_grant_delegation_flag (
1154 p_subscription_reg_id => l_subscription_reg_id,
1155 p_grant_delegation_flag => p_grant_delegation_flag
1156 );
1157
1158 END IF;
1159 CLOSE FIND_REG_ID;
1160
1161 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1162 p_message => l_procedure_name
1163 );
1164
1165 end update_grant_delegation_flag;
1166
1167
1168 /*
1169 * Name : update_grant_delegation_flag
1170 * Pre_reqs : None
1171 * Description : Will update the information of the grant_delegation_flag
1172 * Parameters :
1173 * input parameters
1174 * @param p_subscription_id
1175 * description: The subscription_id of an enrollment
1176 * required : Y
1177 * validation : Must be a valid subscription_id. The procedure will not do
1178 * any explicit validation.
1179 * p_user_id:
1180 * description: The user_id of a user
1181 * required : Y
1182 * validation : Must be a valid user_id.The procedure will not do
1183 * any explicit validation.
1184 * p_grant_delegation_flag:
1185 * description: The Boolean equivallent int value of the grant_delegation_flag
1186 * required : Y
1187 * validation : Should be 0 or 1. The procedure will default it to
1188 * 0, if null value is passed
1189 *
1190 * output parameters
1191 * None
1192 *
1193 * Notes:
1194 *
1195 * This procedure is create as wrapper procedure to pass boolean
1196 * values, as JDBC cannot handle boolean !!!!!
1197 */
1198
1199 procedure update_grant_delegation_flag (
1200 p_subscription_id in number,
1201 p_user_id in number,
1202 p_grant_delegation_flag in number
1203 ) IS
1204
1205 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
1206 l_grant_delegation_flag boolean := false;
1207
1208 begin
1209
1210 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1211 p_message => l_procedure_name
1212 );
1213
1214
1215 if p_grant_delegation_flag = 1 then
1216 l_grant_delegation_flag := true;
1217 end if;
1218
1219 update_grant_delegation_flag (
1220 p_subscription_id => p_subscription_id,
1221 p_user_id => p_user_id,
1222 p_grant_delegation_flag => l_grant_delegation_flag
1223 );
1224
1225 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1226 p_message => l_procedure_name
1227 );
1228
1229
1230 end update_grant_delegation_flag;
1231
1232
1233 /*
1234 * Name : get_delegation_role
1235 * Pre_reqs : None
1236 * Description : Will determine if an enrollment has a delegation role
1237 * Parameters :
1238 * input parameters
1239 * @param p_subscription_id
1240 * description: The subscription_id of an enrollment
1241 * required : Y
1242 * validation : Must be a valid subscription_id
1243 * output parameters
1244 * x_delegation_role
1245 * description: The value of the column auth_delegation_id of the table
1246 * JTF_UM_ENROLLMENTS_B. This value will be null, if no
1247 * no delegation role has been defined for this enrollment
1248 *
1249 * Note:
1250 *
1251 * This API will raise an exception if no record is found which matches
1252 * to the subscription_id being passed
1253 */
1254 procedure get_delegation_role(
1255 p_subscription_id in number,
1256 x_delegation_role out NOCOPY number
1257 ) is
1258
1259 l_procedure_name CONSTANT varchar2(30) := 'update_grant_delegation_flag';
1260
1261 CURSOR FIND_DELEGATION_ROLE IS SELECT AUTH_DELEGATION_ROLE_ID FROM JTF_UM_SUBSCRIPTIONS_B
1262 WHERE SUBSCRIPTION_ID = p_subscription_id;
1263
1264 begin
1265
1266 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1267 p_message => l_procedure_name
1268 );
1269
1270 if l_is_debug_parameter_on then
1271 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1272 p_message => 'p_subscription_id:' || p_subscription_id
1273 );
1274 end if;
1275
1276
1277 OPEN FIND_DELEGATION_ROLE;
1278 FETCH FIND_DELEGATION_ROLE INTO x_delegation_role;
1279
1280 IF FIND_DELEGATION_ROLE%NOTFOUND THEN
1281 CLOSE FIND_DELEGATION_ROLE;
1282 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
1283 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('subscription_id')
1284 );
1285 RAISE_APPLICATION_ERROR(-20000,JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('subscription_id'));
1286 END IF;
1287
1288
1289
1290 CLOSE FIND_DELEGATION_ROLE;
1291
1292 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1293 p_message => l_procedure_name
1294 );
1295
1296
1297 end get_delegation_role;
1298
1299
1300 /**
1301 * Procedure : get_grant_delegation_flag
1302 * Type : Private
1303 * Pre_reqs : None
1304 * Description : Will return the value of the column grant_delegation_flag
1305 * from the table JTF_UM_SUBSCRIPTION_REG
1306 * Parameters :
1307 * input parameters
1308 * @param p_subscription_id
1309 * description: The subscription_id of an enrollment
1310 * required : Y
1311 * validation : Must be a valid subscription_id
1312 * p_user_id:
1313 * description: The user_id of a user
1314 * required : Y
1315 * validation : Must be a valid user_id
1316 * output parameters
1317 * x_result: The Boolean value based on the column grant_delegation_flag
1318 *
1319 * Note:
1320 *
1321 * This API will raise an exception, if subscription_id or user_id is invalid
1322 * or there is no matching record in JTF_UM_SUBSCRIPTION_REG table
1323 *
1324 */
1325
1326 procedure get_grant_delegation_flag(
1327 p_subscription_id in number,
1328 p_user_id in number,
1329 x_result out NOCOPY boolean
1330 ) IS
1331
1332 l_procedure_name CONSTANT varchar2(30) := 'get_grant_delegation_flag';
1333
1334 CURSOR FIND_DELEGATION_FLAG IS SELECT GRANT_DELEGATION_FLAG FROM JTF_UM_SUBSCRIPTION_REG
1335 WHERE SUBSCRIPTION_ID = p_subscription_id AND USER_ID = p_user_id
1336 AND NVL(EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE;
1337
1338 l_flag_value varchar2(1);
1339
1340 BEGIN
1341
1342 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1343 p_message => l_procedure_name
1344 );
1345 if l_is_debug_parameter_on then
1346 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1347 p_message => 'p_subscription_id:'||p_subscription_id || '+' || 'p_user_id:' || p_user_id
1348 );
1349 end if;
1350
1351 IF NOT JTF_UM_UTIL_PVT.VALIDATE_USER_ID(p_user_id) THEN
1352 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
1353 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('user_id')
1354 );
1355 RAISE_APPLICATION_ERROR(-20000,JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('user_id'));
1356 END IF;
1357
1358 IF NOT JTF_UM_UTIL_PVT.VALIDATE_SUBSCRIPTION_ID(p_subscription_id) THEN
1359 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
1360 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('subscription_id')
1361 );
1362 RAISE_APPLICATION_ERROR(-20000,JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('subscription_id'));
1363 END IF;
1364
1365
1366 OPEN FIND_DELEGATION_FLAG;
1367
1368 FETCH FIND_DELEGATION_FLAG INTO l_flag_value;
1369
1370 IF FIND_DELEGATION_FLAG%NOTFOUND THEN
1371 CLOSE FIND_DELEGATION_FLAG;
1372 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
1373 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('JTA_UM_USER_ENROLL_NO_ASGN')
1374 );
1375
1376 RAISE_APPLICATION_ERROR(-20000, JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('JTA_UM_USER_ENROLL_NO_ASGN'));
1377 END IF;
1378
1379 CLOSE FIND_DELEGATION_FLAG;
1380
1381 IF l_flag_value = 'Y' THEN
1382 x_result := TRUE;
1383 ELSE
1384 x_result := FALSE;
1385 END IF;
1386
1387 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1388 p_message => l_procedure_name
1389 );
1390
1391
1392 END get_grant_delegation_flag;
1393
1394
1395 end JTF_UM_SUBSCRIPTIONS_PKG;
[Home] [Help]