[Home] [Help]
PACKAGE BODY: APPS.JTF_PREFAB_POLICIES_PKG
Source
1 package body JTF_PREFAB_POLICIES_PKG as
2 /* $Header: jtfprepolicytb.pls 120.2 2005/10/28 00:26:16 emekala noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_POLICY_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_SECURITY_GROUP_ID in NUMBER,
8 X_POLICY_NAME in VARCHAR2,
9 X_PRIORITY in NUMBER,
10 X_ENABLED_FLAG in VARCHAR2,
11 X_APPLICATION_ID in NUMBER,
12 X_ALL_APPLICATIONS_FLAG in VARCHAR2,
13 X_DEPTH in NUMBER,
14 X_ALL_RESPONSIBILITIES_FLAG in VARCHAR2,
15 X_ALL_USERS_FLAG in VARCHAR2,
16 X_REFRESH_INTERVAL in NUMBER,
17 X_INTERVAL_UNIT in VARCHAR2,
18 X_START_TIME in NUMBER,
19 X_END_TIME in NUMBER,
20 X_RUN_ALWAYS_FLAG in VARCHAR2,
21 X_DESCRIPTION in VARCHAR2,
22 X_CREATION_DATE in DATE,
23 X_CREATED_BY in NUMBER,
24 X_LAST_UPDATE_DATE in DATE,
25 X_LAST_UPDATED_BY in NUMBER,
26 X_LAST_UPDATE_LOGIN in NUMBER
27 ) is
28 cursor C is select ROWID from JTF_PREFAB_POLICIES_B
29 where POLICY_ID = X_POLICY_ID
30 ;
31 begin
32 insert into JTF_PREFAB_POLICIES_B (
33 POLICY_ID,
34 OBJECT_VERSION_NUMBER,
35 -- SECURITY_GROUP_ID,
36 POLICY_NAME,
37 PRIORITY,
38 ENABLED_FLAG,
39 APPLICATION_ID,
40 ALL_APPLICATIONS_FLAG,
41 DEPTH,
42 ALL_RESPONSIBILITIES_FLAG,
43 ALL_USERS_FLAG,
44 REFRESH_INTERVAL,
45 INTERVAL_UNIT,
46 START_TIME,
47 END_TIME,
48 RUN_ALWAYS_FLAG,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN
54 ) values (
55 X_POLICY_ID,
56 X_OBJECT_VERSION_NUMBER,
57 -- X_SECURITY_GROUP_ID,
58 X_POLICY_NAME,
59 X_PRIORITY,
60 X_ENABLED_FLAG,
61 X_APPLICATION_ID,
62 X_ALL_APPLICATIONS_FLAG,
63 X_DEPTH,
64 X_ALL_RESPONSIBILITIES_FLAG,
65 X_ALL_USERS_FLAG,
66 X_REFRESH_INTERVAL,
67 X_INTERVAL_UNIT,
68 X_START_TIME,
69 X_END_TIME,
70 X_RUN_ALWAYS_FLAG,
71 X_CREATION_DATE,
72 X_CREATED_BY,
73 X_LAST_UPDATE_DATE,
74 X_LAST_UPDATED_BY,
75 X_LAST_UPDATE_LOGIN
76 );
77
78 insert into JTF_PREFAB_POLICIES_TL (
79 POLICY_ID,
80 DESCRIPTION,
81 CREATED_BY,
82 CREATION_DATE,
83 LAST_UPDATED_BY,
84 LAST_UPDATE_DATE,
85 LAST_UPDATE_LOGIN,
86 -- SECURITY_GROUP_ID,
87 LANGUAGE,
88 SOURCE_LANG
89 ) select
90 X_POLICY_ID,
91 X_DESCRIPTION,
92 X_CREATED_BY,
93 X_CREATION_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_DATE,
96 X_LAST_UPDATE_LOGIN,
97 -- X_SECURITY_GROUP_ID,
98 L.LANGUAGE_CODE,
99 userenv('LANG')
100 from FND_LANGUAGES L
101 where L.INSTALLED_FLAG in ('I', 'B')
102 and not exists
103 (select NULL
104 from JTF_PREFAB_POLICIES_TL T
105 where T.POLICY_ID = X_POLICY_ID
106 and T.LANGUAGE = L.LANGUAGE_CODE);
107
108 open c;
109 fetch c into X_ROWID;
110 if (c%notfound) then
111 close c;
112 raise no_data_found;
113 end if;
114 close c;
115
116 end INSERT_ROW;
117
118 procedure LOCK_ROW (
119 X_POLICY_ID in NUMBER,
120 X_OBJECT_VERSION_NUMBER in NUMBER,
121 X_SECURITY_GROUP_ID in NUMBER,
122 X_POLICY_NAME in VARCHAR2,
123 X_PRIORITY in NUMBER,
124 X_ENABLED_FLAG in VARCHAR2,
125 X_APPLICATION_ID in NUMBER,
126 X_ALL_APPLICATIONS_FLAG in VARCHAR2,
127 X_DEPTH in NUMBER,
128 X_ALL_RESPONSIBILITIES_FLAG in VARCHAR2,
129 X_ALL_USERS_FLAG in VARCHAR2,
130 X_REFRESH_INTERVAL in NUMBER,
131 X_INTERVAL_UNIT in VARCHAR2,
132 X_START_TIME in NUMBER,
133 X_END_TIME in NUMBER,
134 X_RUN_ALWAYS_FLAG in VARCHAR2,
135 X_DESCRIPTION in VARCHAR2
136 ) is
137 cursor c is select
138 OBJECT_VERSION_NUMBER,
139 SECURITY_GROUP_ID,
140 POLICY_NAME,
141 PRIORITY,
142 ENABLED_FLAG,
143 APPLICATION_ID,
144 ALL_APPLICATIONS_FLAG,
145 DEPTH,
146 ALL_RESPONSIBILITIES_FLAG,
147 ALL_USERS_FLAG,
148 REFRESH_INTERVAL,
149 INTERVAL_UNIT,
150 START_TIME,
151 END_TIME,
152 RUN_ALWAYS_FLAG
153 from JTF_PREFAB_POLICIES_B
154 where POLICY_ID = X_POLICY_ID
155 for update of POLICY_ID nowait;
156 recinfo c%rowtype;
157
158 cursor c1 is select
159 DESCRIPTION,
160 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
161 from JTF_PREFAB_POLICIES_TL
162 where POLICY_ID = X_POLICY_ID
163 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
164 for update of POLICY_ID nowait;
165 begin
166 open c;
167 fetch c into recinfo;
168 if (c%notfound) then
169 close c;
170 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
171 app_exception.raise_exception;
172 end if;
173 close c;
174 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
175 -- AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
176 -- OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
177 AND (recinfo.POLICY_NAME = X_POLICY_NAME)
178 AND (recinfo.PRIORITY = X_PRIORITY)
179 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
180 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
181 AND (recinfo.ALL_APPLICATIONS_FLAG = X_ALL_APPLICATIONS_FLAG)
182 AND (recinfo.DEPTH = X_DEPTH)
183 AND (recinfo.ALL_RESPONSIBILITIES_FLAG = X_ALL_RESPONSIBILITIES_FLAG)
184 AND (recinfo.ALL_USERS_FLAG = X_ALL_USERS_FLAG)
185 AND (recinfo.REFRESH_INTERVAL = X_REFRESH_INTERVAL)
186 AND (recinfo.INTERVAL_UNIT = X_INTERVAL_UNIT)
187 AND (recinfo.START_TIME = X_START_TIME)
188 AND (recinfo.END_TIME = X_END_TIME)
189 AND (recinfo.RUN_ALWAYS_FLAG = X_RUN_ALWAYS_FLAG)
190 ) then
191 null;
192 else
193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
194 app_exception.raise_exception;
195 end if;
196
197 for tlinfo in c1 loop
198 if (tlinfo.BASELANG = 'Y') then
199 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
200 ) then
201 null;
202 else
203 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
204 app_exception.raise_exception;
205 end if;
206 end if;
207 end loop;
208 return;
209 end LOCK_ROW;
210
211 procedure UPDATE_ROW (
212 X_POLICY_ID in NUMBER,
213 X_OBJECT_VERSION_NUMBER in NUMBER,
214 X_SECURITY_GROUP_ID in NUMBER,
215 X_POLICY_NAME in VARCHAR2,
216 X_PRIORITY in NUMBER,
217 X_ENABLED_FLAG in VARCHAR2,
218 X_APPLICATION_ID in NUMBER,
219 X_ALL_APPLICATIONS_FLAG in VARCHAR2,
220 X_DEPTH in NUMBER,
221 X_ALL_RESPONSIBILITIES_FLAG in VARCHAR2,
222 X_ALL_USERS_FLAG in VARCHAR2,
223 X_REFRESH_INTERVAL in NUMBER,
224 X_INTERVAL_UNIT in VARCHAR2,
225 X_START_TIME in NUMBER,
226 X_END_TIME in NUMBER,
227 X_RUN_ALWAYS_FLAG in VARCHAR2,
228 X_DESCRIPTION in VARCHAR2,
229 X_LAST_UPDATE_DATE in DATE,
230 X_LAST_UPDATED_BY in NUMBER,
231 X_LAST_UPDATE_LOGIN in NUMBER
232 ) is
233 begin
234 update JTF_PREFAB_POLICIES_B set
235 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
236 -- SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
237 POLICY_NAME = X_POLICY_NAME,
238 PRIORITY = X_PRIORITY,
239 ENABLED_FLAG = X_ENABLED_FLAG,
240 APPLICATION_ID = X_APPLICATION_ID,
241 ALL_APPLICATIONS_FLAG = X_ALL_APPLICATIONS_FLAG,
242 DEPTH = X_DEPTH,
243 ALL_RESPONSIBILITIES_FLAG = X_ALL_RESPONSIBILITIES_FLAG,
244 ALL_USERS_FLAG = X_ALL_USERS_FLAG,
245 REFRESH_INTERVAL = X_REFRESH_INTERVAL,
246 INTERVAL_UNIT = X_INTERVAL_UNIT,
247 START_TIME = X_START_TIME,
248 END_TIME = X_END_TIME,
249 RUN_ALWAYS_FLAG = X_RUN_ALWAYS_FLAG,
250 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
251 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
252 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
253 where POLICY_ID = X_POLICY_ID;
254
255 if (sql%notfound) then
256 raise no_data_found;
257 end if;
258
259 update JTF_PREFAB_POLICIES_TL set
260 DESCRIPTION = X_DESCRIPTION,
261 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
262 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
264 SOURCE_LANG = userenv('LANG')
265 where POLICY_ID = X_POLICY_ID
266 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
267
268 if (sql%notfound) then
269 raise no_data_found;
270 end if;
271 end UPDATE_ROW;
272
273 procedure DELETE_ROW (
274 X_POLICY_ID in NUMBER
275 ) is
276 begin
277 delete from JTF_PREFAB_POLICIES_TL
278 where POLICY_ID = X_POLICY_ID;
279
280 if (sql%notfound) then
281 raise no_data_found;
282 end if;
283
284 delete from JTF_PREFAB_POLICIES_B
285 where POLICY_ID = X_POLICY_ID;
286
287 if (sql%notfound) then
288 raise no_data_found;
289 end if;
290 end DELETE_ROW;
291
292 procedure ADD_LANGUAGE
293 is
294 begin
295 delete from JTF_PREFAB_POLICIES_TL T
296 where not exists
297 (select NULL
298 from JTF_PREFAB_POLICIES_B B
299 where B.POLICY_ID = T.POLICY_ID
300 );
301
302 update JTF_PREFAB_POLICIES_TL T set (
303 DESCRIPTION
304 ) = (select
305 B.DESCRIPTION
306 from JTF_PREFAB_POLICIES_TL B
307 where B.POLICY_ID = T.POLICY_ID
308 and B.LANGUAGE = T.SOURCE_LANG)
309 where (
310 T.POLICY_ID,
311 T.LANGUAGE
312 ) in (select
313 SUBT.POLICY_ID,
314 SUBT.LANGUAGE
315 from JTF_PREFAB_POLICIES_TL SUBB, JTF_PREFAB_POLICIES_TL SUBT
316 where SUBB.POLICY_ID = SUBT.POLICY_ID
317 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
318 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
319 ));
320
321 insert into JTF_PREFAB_POLICIES_TL (
322 POLICY_ID,
323 DESCRIPTION,
324 CREATED_BY,
325 CREATION_DATE,
326 LAST_UPDATED_BY,
327 LAST_UPDATE_DATE,
328 LAST_UPDATE_LOGIN,
329 -- SECURITY_GROUP_ID,
330 LANGUAGE,
331 SOURCE_LANG
332 ) select
333 B.POLICY_ID,
334 B.DESCRIPTION,
335 B.CREATED_BY,
336 B.CREATION_DATE,
337 B.LAST_UPDATED_BY,
338 B.LAST_UPDATE_DATE,
339 B.LAST_UPDATE_LOGIN,
340 -- B.SECURITY_GROUP_ID,
341 L.LANGUAGE_CODE,
342 B.SOURCE_LANG
343 from JTF_PREFAB_POLICIES_TL B, FND_LANGUAGES L
344 where L.INSTALLED_FLAG in ('I', 'B')
345 and B.LANGUAGE = userenv('LANG')
346 and not exists
347 (select NULL
348 from JTF_PREFAB_POLICIES_TL T
349 where T.POLICY_ID = B.POLICY_ID
350 and T.LANGUAGE = L.LANGUAGE_CODE);
351 end ADD_LANGUAGE;
352
353 procedure LOAD_ROW (
354 X_OBJECT_VERSION_NUMBER in NUMBER,
355 X_SECURITY_GROUP_ID in NUMBER,
356 X_POLICY_NAME in VARCHAR2,
357 X_PRIORITY in NUMBER,
358 X_ENABLED_FLAG in VARCHAR2,
359 X_APPLICATION_ID in NUMBER,
360 X_ALL_APPLICATIONS_FLAG in VARCHAR2,
361 X_DEPTH in NUMBER,
362 X_ALL_RESPONSIBILITIES_FLAG in VARCHAR2,
363 X_ALL_USERS_FLAG in VARCHAR2,
364 X_REFRESH_INTERVAL in NUMBER,
365 X_INTERVAL_UNIT in VARCHAR2,
366 X_START_TIME in NUMBER,
367 X_END_TIME in NUMBER,
368 X_RUN_ALWAYS_FLAG in VARCHAR2,
369 X_DESCRIPTION in VARCHAR2,
370 X_LAST_UPDATE_DATE in DATE,
371 X_OWNER in VARCHAR2,
372 X_CUSTOM_MODE in VARCHAR2
373 ) is
374 --****** local variables ******
375 l_row_id VARCHAR2(255);
376
377 f_luby NUMBER;
378 f_ludate DATE;
379 db_luby NUMBER;
380 db_ludate DATE;
381 l_policy_id NUMBER;
382
383 cursor c is select nvl(max(POLICY_ID), 0) from jtf_prefab_policies_b where POLICY_ID < 10000;
384 l_pseudo_seq NUMBER := NULL;
385 begin
386
387 if (X_OWNER = 'SEED') then
388 f_luby := 1;
389 else
390 f_luby := 0;
391 end if;
392
393 f_ludate := X_LAST_UPDATE_DATE;
394
395 begin
396 SELECT POLICY_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
397 INTO l_policy_id, db_luby, db_ludate
398 FROM JTF_PREFAB_POLICIES_B
399 WHERE APPLICATION_ID = X_APPLICATION_ID AND
400 POLICY_NAME = X_POLICY_NAME;
401
402 -- **** Entry is there, check if it's legal to update ****
403 IF ((X_CUSTOM_MODE = 'FORCE') OR
404 ((f_luby = 0) AND (db_luby = 1)) OR
405 ((f_luby = db_luby) AND (f_ludate > db_ludate))
406 )
407 then
408 -- **** call Update row ****
409 JTF_PREFAB_POLICIES_PKG.UPDATE_ROW (
410 X_POLICY_ID => l_policy_id,
411 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
412 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
413 X_POLICY_NAME => X_POLICY_NAME,
414 X_PRIORITY => X_PRIORITY,
415 X_ENABLED_FLAG => X_ENABLED_FLAG,
416 X_APPLICATION_ID => X_APPLICATION_ID,
417 X_ALL_APPLICATIONS_FLAG => X_ALL_APPLICATIONS_FLAG,
418 X_DEPTH => X_DEPTH,
419 X_ALL_RESPONSIBILITIES_FLAG => X_ALL_RESPONSIBILITIES_FLAG,
420 X_ALL_USERS_FLAG => X_ALL_USERS_FLAG,
421 X_REFRESH_INTERVAL => X_REFRESH_INTERVAL,
422 X_INTERVAL_UNIT => X_INTERVAL_UNIT,
423 X_START_TIME => X_START_TIME,
424 X_END_TIME => X_END_TIME,
425 X_RUN_ALWAYS_FLAG => X_RUN_ALWAYS_FLAG,
426 X_DESCRIPTION => X_DESCRIPTION,
427 X_LAST_UPDATE_DATE => f_ludate,
428 X_LAST_UPDATED_BY => f_luby,
429 X_LAST_UPDATE_LOGIN => 0);
430
431 -- **** delete all the child entries ****
432 DELETE FROM jtf_prefab_ur_policies
433 WHERE policy_id = l_policy_id;
434 end if;
435 exception
436 when no_data_found then
437 -- **** generate pseudo sequence ***
438 OPEN c;
439 FETCH c INTO l_pseudo_seq;
440 CLOSE c;
441
442 JTF_PREFAB_POLICIES_PKG.INSERT_ROW (
443 X_ROWID => l_row_id,
444 X_POLICY_ID => (l_pseudo_seq + 1),
445 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
446 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
447 X_POLICY_NAME => X_POLICY_NAME,
448 X_PRIORITY => X_PRIORITY,
449 X_ENABLED_FLAG => X_ENABLED_FLAG,
450 X_APPLICATION_ID => X_APPLICATION_ID,
451 X_ALL_APPLICATIONS_FLAG => X_ALL_APPLICATIONS_FLAG,
452 X_DEPTH => X_DEPTH,
453 X_ALL_RESPONSIBILITIES_FLAG => X_ALL_RESPONSIBILITIES_FLAG,
454 X_ALL_USERS_FLAG => X_ALL_USERS_FLAG,
458 X_END_TIME => X_END_TIME,
455 X_REFRESH_INTERVAL => X_REFRESH_INTERVAL,
456 X_INTERVAL_UNIT => X_INTERVAL_UNIT,
457 X_START_TIME => X_START_TIME,
459 X_RUN_ALWAYS_FLAG => X_RUN_ALWAYS_FLAG,
460 X_DESCRIPTION => X_DESCRIPTION,
461 X_CREATION_DATE => f_ludate,
462 X_CREATED_BY => f_luby,
463 X_LAST_UPDATE_DATE => f_ludate,
464 X_LAST_UPDATED_BY => f_luby,
465 X_LAST_UPDATE_LOGIN => 0);
466 end;
467
468 end LOAD_ROW;
469
470 procedure TRANSLATE_ROW (
471 X_POLICY_NAME in VARCHAR2,
472 X_APPLICATION_ID in NUMBER,
473 X_DESCRIPTION in VARCHAR2,
474 X_LAST_UPDATE_DATE in DATE,
475 X_OWNER in VARCHAR2,
476 X_CUSTOM_MODE in VARCHAR2
477 ) is
478 -- **** local variables *****
479 f_luby NUMBER;
480 f_ludate DATE;
481 db_luby NUMBER;
482 db_ludate DATE;
483 l_policy_id NUMBER;
484 begin
485
486 if (X_OWNER = 'SEED') then
487 f_luby := 1;
488 else
489 f_luby := 0;
490 end if;
491
492 f_ludate := X_LAST_UPDATE_DATE;
493
494 begin
495 SELECT tl.POLICY_ID, tl.LAST_UPDATED_BY, tl.LAST_UPDATE_DATE
496 INTO l_policy_id, db_luby, db_ludate
497 FROM JTF_PREFAB_POLICIES_B b, JTF_PREFAB_POLICIES_TL tl
498 WHERE b.POLICY_ID = tl.POLICY_ID AND
499 b.APPLICATION_ID = X_APPLICATION_ID AND
500 b.POLICY_NAME = X_POLICY_NAME AND
501 tl.LANGUAGE = userenv('LANG');
502
503 if ((X_CUSTOM_MODE = 'FORCE') OR
504 ((f_luby = 0) AND (db_luby = 1)) OR
505 ((f_luby = db_luby) AND (f_ludate > db_ludate))
506 )
507 then
508 update JTF_PREFAB_POLICIES_TL set
509 DESCRIPTION = nvl(X_DESCRIPTION, DESCRIPTION),
510 LAST_UPDATE_DATE = f_ludate,
511 LAST_UPDATED_BY = f_luby,
512 LAST_UPDATE_LOGIN = 0,
513 SOURCE_LANG = userenv('LANG')
514 where userenv('LANG') in (LANGUAGE, SOURCE_LANG) and
515 POLICY_ID = l_policy_id;
516 end if;
517 exception
518 when no_data_found then null;
519 end;
520 end TRANSLATE_ROW;
521
522 procedure LOAD_UR_ROW (
523 X_OBJECT_VERSION_NUMBER in NUMBER,
524 X_SECURITY_GROUP_ID in NUMBER,
525 X_APPLICATION_ID in NUMBER,
526 X_POLICY_NAME in VARCHAR2,
527 X_USERRESP_ID in NUMBER,
528 X_USERRESP_TYPE in VARCHAR2,
529 X_LAST_UPDATE_DATE in DATE,
530 X_OWNER in VARCHAR2,
531 X_CUSTOM_MODE in VARCHAR2
532 ) is
533 --****** local variables ******
534 l_row_id VARCHAR2(255);
535
536 f_luby NUMBER;
537 f_ludate DATE;
538 db_luby NUMBER;
539 db_ludate DATE;
540 l_policy_id NUMBER;
541
542 cursor c is select nvl(max(UR_POLICY_ID), 0) from jtf_prefab_ur_policies where UR_POLICY_ID < 10000;
543 l_pseudo_seq NUMBER := NULL;
544 begin
545
546 if (X_OWNER = 'SEED') then
547 f_luby := 1;
548 else
549 f_luby := 0;
550 end if;
551
552 f_ludate := X_LAST_UPDATE_DATE;
553
554 begin
555 SELECT po.POLICY_ID, ur.LAST_UPDATED_BY, ur.LAST_UPDATE_DATE
556 INTO l_policy_id, db_luby, db_ludate
557 FROM JTF_PREFAB_UR_POLICIES ur, JTF_PREFAB_POLICIES_B po
558 WHERE po.POLICY_ID = ur.POLICY_ID AND
559 po.APPLICATION_ID = X_APPLICATION_ID AND
560 po.POLICY_NAME = X_POLICY_NAME AND
561 ur.USERRESP_ID = X_USERRESP_ID AND
562 ur.USERRESP_TYPE = X_USERRESP_TYPE;
563
564 -- **** Entry is there, check if it's legal to update ****
565 /*
566 IF ((X_CUSTOM_MODE = 'FORCE') OR
567 ((f_luby = 0) AND (db_luby = 1)) OR
568 ((f_luby = db_luby) AND (f_ludate > db_ludate))
569 )
570 then
571 -- **** do nothing ****
572 end if;
573 */
574 exception
575 when no_data_found then
576 -- **** generate pseudo sequence ***
577 OPEN c;
578 FETCH c INTO l_pseudo_seq;
579 CLOSE c;
580
581 -- **** get policy id ***
582 SELECT POLICY_ID
583 INTO l_policy_id
584 FROM JTF_PREFAB_POLICIES_B
585 WHERE APPLICATION_ID = X_APPLICATION_ID
586 AND POLICY_NAME = X_POLICY_NAME;
587
588 INSERT INTO jtf_prefab_ur_policies (ur_policy_id,
589 object_version_number,
590 created_by,
591 creation_date,
592 last_updated_by,
593 last_update_date,
594 last_update_login,
595 -- security_group_id,
596 policy_id,
597 userresp_id,
598 userresp_type)
599 VALUES ((l_pseudo_seq + 1),
600 X_OBJECT_VERSION_NUMBER,
601 f_luby,
602 f_ludate,
603 f_luby,
604 f_ludate,
605 0,
606 -- X_SECURITY_GROUP_ID,
607 l_policy_id,
608 X_USERRESP_ID,
609 X_USERRESP_TYPE);
610 end;
611
612 end LOAD_UR_ROW;
613
614 procedure LOAD_SYS_ROW (
615 X_OBJECT_VERSION_NUMBER in NUMBER,
616 X_SECURITY_GROUP_ID in NUMBER,
617 X_START_FLAG in VARCHAR2,
618 X_CPU in NUMBER,
619 X_MEMORY in NUMBER,
620 X_DISK_LOCATION in VARCHAR2,
621 X_MAX_CONCURRENCY in NUMBER,
622 X_USE_LOAD_BALANCER_FLAG in VARCHAR2,
623 X_LOAD_BALANCER_URL in VARCHAR2,
624 X_REFRESH_FLAG in VARCHAR2,
625 X_INTERCEPTOR_ENABLED_FLAG in VARCHAR2,
626 X_CACHE_MEMORY in NUMBER,
627 X_LAST_UPDATE_DATE in DATE,
628 X_OWNER in VARCHAR2,
629 X_CUSTOM_MODE in VARCHAR2
630 ) is
631 --****** local variables ******
632 l_row_id VARCHAR2(255);
633
634 f_luby NUMBER;
635 f_ludate DATE;
636 db_luby NUMBER;
637 db_ludate DATE;
638 begin
639
640 if (X_OWNER = 'SEED') then
641 f_luby := 1;
642 else
643 f_luby := 0;
644 end if;
645
646 f_ludate := X_LAST_UPDATE_DATE;
647
648 begin
649 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
650 INTO db_luby, db_ludate
651 FROM JTF_PREFAB_SYS_POLICIES;
652
653 -- **** Entry is there, check if it's legal to update ****
654 IF ((X_CUSTOM_MODE = 'FORCE') OR
655 ((f_luby = 0) AND (db_luby = 1)) OR
656 ((f_luby = db_luby) AND (f_ludate > db_ludate))
657 )
658 then
659 -- **** update ****
660 UPDATE jtf_prefab_sys_policies
661 SET object_version_number = X_OBJECT_VERSION_NUMBER,
665 start_flag = X_START_FLAG,
662 last_updated_by = f_luby,
663 last_update_date = f_ludate,
664 last_update_login = 0,
666 cpu = X_CPU,
667 memory = X_MEMORY,
668 disk_location = X_DISK_LOCATION,
669 max_concurrency = X_MAX_CONCURRENCY,
670 use_load_balancer_flag = X_USE_LOAD_BALANCER_FLAG,
671 load_balancer_url = X_LOAD_BALANCER_URL,
672 refresh_flag = X_REFRESH_FLAG,
673 interceptor_enabled_flag = X_INTERCEPTOR_ENABLED_FLAG,
674 cache_memory = X_CACHE_MEMORY;
675 end if;
676 exception
677 when no_data_found then
678 INSERT INTO jtf_prefab_sys_policies (sys_policy_id,
679 object_version_number,
680 created_by,
681 creation_date,
682 last_updated_by,
683 last_update_date,
684 last_update_login,
685 -- security_group_id,
686 start_flag,
687 cpu,
688 memory,
689 disk_location,
690 max_concurrency,
691 use_load_balancer_flag,
692 load_balancer_url,
693 refresh_flag,
694 interceptor_enabled_flag,
695 cache_memory)
696 VALUES (1,
697 X_OBJECT_VERSION_NUMBER,
698 f_luby,
699 f_ludate,
700 f_luby,
701 f_ludate,
702 0,
703 -- X_SECURITY_GROUP_ID,
704 X_START_FLAG,
705 X_CPU,
706 X_MEMORY,
707 X_DISK_LOCATION,
708 X_MAX_CONCURRENCY,
709 X_USE_LOAD_BALANCER_FLAG,
710 X_LOAD_BALANCER_URL,
711 X_REFRESH_FLAG,
712 X_INTERCEPTOR_ENABLED_FLAG,
713 X_CACHE_MEMORY);
714 end;
715
716 end LOAD_SYS_ROW;
717
718 end JTF_PREFAB_POLICIES_PKG;