[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_DSCRAM
Source
1 PACKAGE BODY FND_OAM_DSCRAM AS
2 /* $Header: AFOAMDSB.pls 120.6.12000000.3 2007/04/21 02:55:34 ssuprasa ship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(30) := 'FND_OAM_DSCRAM.';
8
9 -- text entered in the dscram_level of every attribute/col_priv to identify that it's from our UI.
10 B_DSCRAM_LEVEL_USER_DEFINED CONSTANT VARCHAR2(30) := 'USER_DEFINED';
11
12 ----------------------------------------
13 -- Public/Private Procedures/Functions
14 ----------------------------------------
15
16 /* insert a new data scrambling policy */
17 procedure insert_policy
18 (
19 policyid OUT NOCOPY NUMBER,
20 pname in varchar2,
21 l_description IN VARCHAR2 DEFAULT NULL,
22 l_created_by IN NUMBER,
23 l_last_updated_by IN NUMBER,
24 l_last_update_login IN NUMBER
25 ) is
26
27 begin
28
29 /* Get new ID */
30 select FND_OAM_DS_POLICIES_S.nextval
31 into policyid
32 from sys.dual;
33
34 /*insert into the base table*/
35
36 insert into FND_OAM_DS_POLICIES_B (
37 policy_id,
38 CREATED_BY,
39 CREATION_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_DATE,
42 LAST_UPDATE_LOGIN)
43 values
44 (policyid, l_created_by, sysdate,
45 l_last_updated_by, sysdate, l_last_update_login);
46
47 /*insert into the TL table*/
48
49 insert into FND_OAM_DS_POLICIES_TL (
50 policy_id,
51 policy_name,
52 description,
53 CREATED_BY,
54 CREATION_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATE_LOGIN,
58 language,
59 source_lang
60 )
61 select
62 policyid,insert_policy.pname,insert_policy.l_description, insert_policy.l_created_by,
63 sysdate, insert_policy.l_last_updated_by, sysdate, insert_policy.l_last_update_login,
64 l.language_code, userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from FND_OAM_DS_POLICIES_TL T
70 where T.POLICY_ID = policyid
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 commit;
74
75 EXCEPTION
76 when others then
77 rollback;
78 raise;
79
80 end insert_policy;
81
82 /* update a data scrambling policy */
83
84 procedure update_policy
85 (
86 policyid in number,
87 pname in varchar2,
88 l_description IN VARCHAR2 DEFAULT NULL,
89 l_last_updated_by IN NUMBER,
90 l_last_update_login IN NUMBER
91 ) is
92
93 begin
94
95 /*update*/
96
97 update FND_OAM_DS_POLICIES_B
98 set LAST_UPDATED_BY = l_last_updated_by,
99 LAST_UPDATE_DATE = sysdate,
100 LAST_UPDATE_LOGIN = l_last_update_login
101 where policy_id = policyid;
102
103
104 /* Make sure no bad data in tl table */
105 delete from FND_OAM_DS_POLICIES_TL
106 where policy_id = policyid
107 and language in (select l.language_code
108 from fnd_languages l
109 where l.installed_flag in ('I', 'B'));
110
111
112 insert into FND_OAM_DS_POLICIES_TL (
113 policy_id,
114 policy_name,
115 description,
116 CREATED_BY,
117 CREATION_DATE,
118 LAST_UPDATED_BY,
119 LAST_UPDATE_DATE,
120 LAST_UPDATE_LOGIN,
121 language,
122 source_lang
123 )
124 select
125 update_policy.policyid,update_policy.pname,update_policy.l_description, update_policy.l_last_update_login,
126 sysdate, update_policy.l_last_updated_by, sysdate, update_policy.l_last_update_login,
127 l.language_code, userenv('LANG')
128 from FND_LANGUAGES L
129 where L.INSTALLED_FLAG in ('I', 'B')
130 and not exists
131 (select NULL
132 from FND_OAM_DS_POLICIES_TL T
133 where T.POLICY_ID = policyid
134 and T.LANGUAGE = L.LANGUAGE_CODE);
135
136 /* Delete all policy elements for this policy. */
137
138 delete from FND_OAM_DS_POLICY_ELMNTS where policy_id = policyid;
139 commit;
140
141 EXCEPTION
142 when others then
143 rollback;
144 raise;
145
146 end update_policy;
147
148
149 /* insert a new data scrambling policy set */
150
151
152 procedure insert_policyset
153 (
154 psetid OUT NOCOPY NUMBER,
155 psetname in varchar2,
156 l_description IN VARCHAR2 DEFAULT NULL,
157 l_created_by IN NUMBER,
158 l_last_updated_by IN NUMBER,
159 l_last_update_login IN NUMBER
160 ) is
161
162 begin
163
164 /* Get new ID */
165 select FND_OAM_DS_PSETS_S.nextval
166 into psetid
167 from sys.dual;
168
169 /*insert*/
170
171 insert into FND_OAM_DS_PSETS_B (
172 policyset_id,
173 CREATED_BY,
174 CREATION_DATE,
175 LAST_UPDATED_BY,
176 LAST_UPDATE_DATE,
177 LAST_UPDATE_LOGIN)
178 values
179 (psetid, l_created_by, sysdate,
180 l_last_updated_by, sysdate, l_last_update_login);
181
182
183 insert into FND_OAM_DS_PSETS_TL (
184 policyset_id,
185 policyset_name,
186 description,
187 CREATED_BY,
188 CREATION_DATE,
189 LAST_UPDATED_BY,
190 LAST_UPDATE_DATE,
191 LAST_UPDATE_LOGIN,
192 language,
193 source_lang
194 )
195 select
196 psetid,insert_policyset.psetname,insert_policyset.l_description,
197 insert_policyset.l_created_by,
198 sysdate, insert_policyset.l_last_updated_by, sysdate,
199 insert_policyset.l_last_update_login,
200 l.language_code, userenv('LANG')
201 from FND_LANGUAGES L
202 where L.INSTALLED_FLAG in ('I', 'B')
203 and not exists
204 (select NULL
205 from FND_OAM_DS_PSETS_TL T
206 where T.POLICYSET_ID = psetid
207 and T.LANGUAGE = L.LANGUAGE_CODE);
208
209 commit;
210 EXCEPTION
211 when others then
212 rollback;
213 raise;
214
215 end insert_policyset;
216
217 /* update a data scrambling policy set */
218
219 procedure update_policyset
220 (
221 psetid in number,
222 psetname in varchar2,
223 l_description IN VARCHAR2 DEFAULT NULL,
224 l_last_updated_by IN NUMBER,
225 l_last_update_login IN NUMBER
226 ) is
227
228 begin
229
230 /*update*/
231
232 update FND_OAM_DS_PSETS_B
233 set LAST_UPDATED_BY = l_last_updated_by,
234 LAST_UPDATE_DATE = sysdate,
235 LAST_UPDATE_LOGIN = l_last_update_login
236 where policyset_id = psetid;
237
238
239 /* Make sure no bad data in tl table */
240 delete from FND_OAM_DS_PSETS_TL
241 where policyset_id = psetid
242 and language in (select l.language_code
243 from fnd_languages l
244 where l.installed_flag in ('I', 'B'));
245
246
247 insert into FND_OAM_DS_PSETS_TL (
248 policyset_id,
249 policyset_name,
250 description,
251 CREATED_BY,
252 CREATION_DATE,
253 LAST_UPDATED_BY,
254 LAST_UPDATE_DATE,
255 LAST_UPDATE_LOGIN,
256 language,
257 source_lang
258 )
259 select
260 update_policyset.psetid,update_policyset.psetname,update_policyset.l_description,
261 update_policyset.l_last_update_login,
262 sysdate, update_policyset.l_last_updated_by, sysdate,
263 update_policyset.l_last_update_login,
264 l.language_code, userenv('LANG')
265 from FND_LANGUAGES L
266 where L.INSTALLED_FLAG in ('I', 'B')
267 and not exists
268 (select NULL
269 from FND_OAM_DS_PSETS_TL T
270 where T.POLICYSET_ID = psetid
271 and T.LANGUAGE = L.LANGUAGE_CODE);
272
273 /* Delete all policy set elements for this policy set. */
274
275 delete from FND_OAM_DS_PSET_ELMNTS where policyset_id = psetid;
276
277 commit;
278 EXCEPTION
279 when others then
280 rollback;
281 raise;
282
283 end update_policyset;
284
285 /* add a new privacy attribute for a policy with policyid*/
286
287 procedure add_policy_attri_element
288 (
289 policyid in number,
290 attribute_code IN VARCHAR2,
291 l_created_by IN NUMBER,
292 l_last_updated_by IN NUMBER,
293 l_last_update_login IN NUMBER
294 ) is
295
296 elementid number;
297
298 begin
299
300 /* Get new ID */
301 select FND_OAM_DS_POLICY_ELMNTS_S.nextval
302 into elementid
303 from sys.dual;
304
305 /*insert*/
306
307 insert into FND_OAM_DS_POLICY_ELMNTS(
308 policy_rel_id,
309 policy_id,
310 element_type,
311 privacy_attribute_code,
312 CREATED_BY,
313 CREATION_DATE,
314 LAST_UPDATED_BY,
315 LAST_UPDATE_DATE,
316 LAST_UPDATE_LOGIN)
317 values
318 (elementid, policyid, 'PII_ATTRIBUTE', attribute_code, l_created_by, sysdate,
319 l_last_updated_by, sysdate, l_last_update_login);
320
321 commit;
322 EXCEPTION
323 when others then
324 rollback;
325 raise;
326
327 end add_policy_attri_element;
328
329 /* remove all policy elements for a policy with policyid */
330
331 procedure remove_policy_elements
332 (
333 policyid in number
334 ) is
335
336
337 begin
338
339 delete from FND_OAM_DS_POLICY_ELMNTS where policy_id = policyid;
340
341 commit;
342 EXCEPTION
343 when others then
344 rollback;
345 raise;
346
347 end remove_policy_elements;
348
349
350 /* add a new delete element for a policy with policyid*/
351
352 procedure add_policy_del_element
353 (
354 policyid in number,
355 deleteid IN NUMBER,
356 l_created_by IN NUMBER,
357 l_last_updated_by IN NUMBER,
358 l_last_update_login IN NUMBER
359 ) is
360
361 elementid number;
362
363 begin
364
365 /* Get new ID */
366 select FND_OAM_DS_POLICY_ELMNTS_S.nextval
367 into elementid
368 from sys.dual;
369
370 /*insert*/
371
372 insert into FND_OAM_DS_POLICY_ELMNTS(
373 policy_rel_id,
374 policy_id,
375 element_type,
376 delete_id,
377 CREATED_BY,
378 CREATION_DATE,
379 LAST_UPDATED_BY,
380 LAST_UPDATE_DATE,
381 LAST_UPDATE_LOGIN)
382 values
383 (elementid, policyid, 'DML_DELETE', deleteid, l_created_by, sysdate,
384 l_last_updated_by, sysdate, l_last_update_login);
385
386 commit;
387 EXCEPTION
388 when others then
389 rollback;
390 raise;
391
392 end add_policy_del_element;
393
394
395 /* add a new policy into a policy set with psetid*/
396
397 procedure add_pset_element
398 (
399 psetid in number,
400 policyid in number,
401 l_created_by IN NUMBER,
402 l_last_updated_by IN NUMBER,
403 l_last_update_login IN NUMBER
404 ) is
405
406 elementid number;
407
408 begin
409
410 /* Get new ID */
411 select FND_OAM_DS_PSET_ELMNTS_S.nextval
412 into elementid
413 from sys.dual;
414
415 /*insert*/
416
417 insert into FND_OAM_DS_PSET_ELMNTS(
418 policyset_rel_id,
419 policyset_id,
420 policy_id,
421 CREATED_BY,
422 CREATION_DATE,
423 LAST_UPDATED_BY,
424 LAST_UPDATE_DATE,
425 LAST_UPDATE_LOGIN)
426 values
427 (elementid, psetid, policyid, l_created_by, sysdate,
428 l_last_updated_by, sysdate, l_last_update_login);
429
430 commit;
431 EXCEPTION
432 when others then
433 rollback;
434 raise;
435
436 end add_pset_element;
437
438 /* remove all elements for a policy set with psetid*/
439
440 procedure remove_pset_elements
441 (
442 psetid in number
443 ) is
444
445
446 begin
447
448 delete from FND_OAM_DS_PSET_ELMNTS where policyset_id = psetid;
449
450 commit;
451 EXCEPTION
452 when others then
453 rollback;
454 raise;
455
456 end remove_pset_elements;
457
458
459 /* add a new delete entry into FND_OAM_DS_DELETES */
460
461 procedure add_delete
462 (
463 l_table_name IN VARCHAR2,
464 l_owner IN VARCHAR2 DEFAULT NULL,
465 l_where_clause IN VARCHAR2 DEFAULT NULL,
466 l_use_truncate_flag IN VARCHAR2 DEFAULT NULL,
467 l_created_by IN NUMBER,
468 l_last_updated_by IN NUMBER,
469 l_last_update_login IN NUMBER
470 ) is
471
472 deleteid number;
473 v_use_truncate_flag varchar2(3);
474 v_owner varchar2(30);
475 v_count number;
476
477 begin
478
479 /* Get new ID */
480 select FND_OAM_DS_DELETES_S.nextval
481 into deleteid
482 from sys.dual;
483
484 --truncate flag, value can be "T" or "F". the default is "F"
485 v_use_truncate_flag := NVL(l_use_truncate_flag, 'F');
486
487 v_owner := l_owner;
488
489 if l_owner is null then
490 --find owner based on table_name.
491 select ou.oracle_username into v_owner
492 from fnd_tables t,
493 fnd_product_installations pi,
494 fnd_oracle_userid ou
495 where t.table_name = upper(l_table_name)
496 and t.application_id = pi.application_id
497 and pi.oracle_id = ou.oracle_id;
498 end if;
499
500 --before insert, check there is an existing row in fnd_oam_ds_deletes for
501 -- (owner, table_name, where_cluase, use_truncate_flag)
502
503 select count(*) into v_count from fnd_oam_ds_deletes
504 where owner = v_owner
505 and table_name = l_table_name
506 and where_clause = l_where_clause
507 and use_truncate_flag = v_use_truncate_flag;
508
509 --insert when there is not an existing entry.
510 if v_count = 0 then
511 insert into FND_OAM_DS_DELETES(
512 delete_id,
513 table_name,
514 owner,
515 where_clause,
516 use_truncate_flag,
517 CREATED_BY,
518 CREATION_DATE,
519 LAST_UPDATED_BY,
520 LAST_UPDATE_DATE,
521 LAST_UPDATE_LOGIN)
522 values
523 (deleteid, l_table_name, v_owner, l_where_clause, v_use_truncate_flag, l_created_by, sysdate,
524 l_last_updated_by, sysdate, l_last_update_login);
525 end if;
526
527 commit;
528 EXCEPTION
529 when others then
530 rollback;
531 raise;
532
533 end add_delete;
534
535
536
537
538 /* add a new delete entry into FND_OAM_DS_DELETES */
539
540 procedure update_delete
541 (
542 l_delete_id IN VARCHAR2,
543 l_where_clause IN VARCHAR2 DEFAULT NULL,
544 l_use_truncate_flag IN VARCHAR2 DEFAULT NULL,
545 l_last_updated_by IN NUMBER
546 ) is
547
548 deleteid number;
549 v_use_truncate_flag varchar2(3);
550 v_owner varchar2(30);
551 v_count number;
552
553 begin
554
555
556 --truncate flag, value can be "T" or "F". the default is "F"
557 v_use_truncate_flag := NVL(l_use_truncate_flag, 'F');
558
559 select count(*) into v_count from fnd_oam_ds_deletes
560 where delete_id=l_delete_id;
561
562 --insert when there is not an existing entry.
563 if v_count > 0 then
564 update FND_OAM_DS_DELETES
565 set where_clause=l_where_clause, use_truncate_flag = l_use_truncate_flag,
566 last_updated_by=l_last_updated_by ,last_update_date=sysdate
567 where delete_id=l_delete_id;
568 end if;
569
570 commit;
571 EXCEPTION
572 when others then
573 rollback;
574 raise;
575
576 end update_delete;
577
578
579
580
581
582
583 /* remove a delete entry with deleteid from FND_OAM_DS_DELETES */
584
585 procedure remove_delete
586 (
587 deleteid in number
588 ) is
589
590
591 begin
592
593 delete from FND_OAM_DS_DELETES where delete_id = deleteid;
594
595 commit;
596 EXCEPTION
597 when others then
598 rollback;
599 raise;
600
601 end remove_delete;
602
603
604 --syschange
605 /* remove an attribute */
606
607 procedure delete_pii_attribute
608 (
609 attribute_code in varchar2
610 ) is
611
612 begin
613
614 delete from FND_PRIVACY_ATTRIBUTES_B where privacy_attribute_code = attribute_code;
615 delete from FND_PRIVACY_ATTRIBUTES_TL where privacy_attribute_code = attribute_code;
616 delete from FND_OAM_DS_POLICY_ELMNTS where privacy_attribute_code = attribute_code;
617 delete from FND_COL_PRIV_ATTRIBUTES_B where privacy_attribute_code = attribute_code;
618 delete from FND_OAM_DS_PII_EXTENSIONS where privacy_attribute_code = attribute_code;
619 commit;
620 EXCEPTION
621 when others then
622 rollback;
623 raise;
624
625 end delete_pii_attribute;
626
627
628 /* remove an policy */
629 --FIXME
630 procedure delete_policy
631 (
632 p_policy_id in NUMBER
633 ) is
634
635 begin
636
637 delete from FND_OAM_DS_POLICIES_B where policy_id = p_policy_id;
638 delete from FND_OAM_DS_POLICIES_TL where policy_id = p_policy_id;
639 delete from FND_OAM_DS_PSET_ELMNTS where policy_id = p_policy_id;
640 delete from FND_OAM_DS_POLICY_ELMNTS where policy_id = p_policy_id;
641
642 commit;
643 EXCEPTION
644 when others then
645 rollback;
646 raise;
647
648 end delete_policy;
649
650 /* remove the policy set*/
651 --FIXME
652 procedure delete_pset
653 (
654 pset_id in NUMBER
655 ) is
656
657 begin
658
659 delete from FND_OAM_DS_PSETS_B where policyset_id = pset_id;
660 delete from FND_OAM_DS_PSETS_TL where policyset_id = pset_id;
661 delete from FND_OAM_DS_PSET_ELMNTS where policyset_id = pset_id;
662
663 commit;
664 EXCEPTION
665 when others then
666 rollback;
667 raise;
668
669 end delete_pset;
670
671
672
673 /* remove an policy */
674 --FIXME
675 procedure delete_tbl_to_purge
676 (
677 deleteid in NUMBER
678 ) is
679
680 begin
681
682 delete from FND_OAM_DS_DELETES where delete_id = deleteid;
683 delete from FND_OAM_DS_POLICY_ELMNTS where delete_id = deleteid;
684 commit;
685 EXCEPTION
686 when others then
687 rollback;
688 raise;
689
690 end delete_tbl_to_purge;
691
692
693 /* insert a new PII privacy attribute */
694
695 procedure insert_pii_attribute
696 (
697 attribute_code OUT NOCOPY VARCHAR2,
698 attribute_name IN VARCHAR2,
699 l_algorithm IN VARCHAR2 DEFAULT NULL,
700 l_description IN VARCHAR2 DEFAULT NULL,
701 l_created_by IN NUMBER,
702 l_last_updated_by IN NUMBER,
703 l_last_update_login IN NUMBER
704 ) is
705 aid number;
706 algoid number := NULL;
707
708 begin
709
710
711 /*get algorithm id */
712 IF l_algorithm IS NOT NULL THEN
713 algoid := FND_OAM_DS_ALGOS_PKG.GET_ALGO_ID(l_algorithm);
714 END IF;
715
716 /* Get new ID */
717 select FND_OAM_DS_ATTRI_S.nextval
718 into aid
719 from sys.dual;
720
721 /*construct the attribute code */
722 attribute_code := 'DSCRAM_'||to_char(aid);
723
724
725 --create the attribute
726 insert into FND_PRIVACY_ATTRIBUTES_B (PRIVACY_ATTRIBUTE_CODE,
727 PRIVACY_ATTRIBUTE_TYPE,
728 SENSITIVITY,
729 PII_FLAG,
730 LOCKED_FLAG,
731 OBJECT_VERSION_NUMBER,
732 DSCRAM_LEVEL,
733 DSCRAM_ALGO_ID,
734 CREATED_BY,
735 CREATION_DATE,
736 LAST_UPDATED_BY,
737 LAST_UPDATE_DATE,
738 LAST_UPDATE_LOGIN)
739 VALUES (attribute_code,
740 'Base',
741 'Private',
742 'N',
743 'N',
744 0,
745 B_DSCRAM_LEVEL_USER_DEFINED,
746 algoid,
747 l_created_by,
748 sysdate,
749 l_last_updated_by,
750 sysdate,
751 l_last_update_login);
752
753 insert into FND_PRIVACY_ATTRIBUTES_TL (PRIVACY_ATTRIBUTE_CODE,
754 PRIVACY_ATTRIBUTE_NAME,
755 DESCRIPTION,
756 CREATED_BY,
757 CREATION_DATE,
758 LAST_UPDATED_BY,
759 LAST_UPDATE_DATE,
760 LAST_UPDATE_LOGIN,
761 language,
762 source_lang
763 )
764 select
765 attribute_code,
766 insert_pii_attribute.attribute_name,
767 insert_pii_attribute.l_description,
768 insert_pii_attribute.l_created_by,
769 sysdate,
770 insert_pii_attribute.l_last_updated_by,
771 sysdate,
772 insert_pii_attribute.l_last_update_login,
773 l.language_code,
774 userenv('LANG')
775 from FND_LANGUAGES L
776 where L.INSTALLED_FLAG in ('I', 'B')
777 and not exists
778 (select NULL
779 from FND_PRIVACY_ATTRIBUTES_TL T
780 where T.PRIVACY_ATTRIBUTE_CODE = attribute_code
781 and T.LANGUAGE = L.LANGUAGE_CODE);
782
783 commit;
784 EXCEPTION
785 when others then
786 rollback;
787 raise;
788
789 end insert_pii_attribute;
790
791
792
793
794 procedure update_pii_attribute
795 (
796 attribute_code IN VARCHAR2,
797 attribute_name IN VARCHAR2,
798 l_algorithm IN VARCHAR2 DEFAULT NULL,
799 l_description IN VARCHAR2 DEFAULT NULL,
800 l_created_by IN NUMBER,
801 l_last_updated_by IN NUMBER,
802 l_last_update_login IN NUMBER
803 ) is
804 aid number;
805 algoid number := NULL;
806
807 begin
808
809
810 /*get algorithm id */
811 IF l_algorithm IS NOT NULL THEN
812 algoid := FND_OAM_DS_ALGOS_PKG.GET_ALGO_ID(l_algorithm);
813 END IF;
814
815
816 --create the attribute
817
818 UPDATE FND_PRIVACY_ATTRIBUTES_B
819 SET DSCRAM_ALGO_ID = algoid,
820 LAST_UPDATED_BY = l_last_updated_by,
821 LAST_UPDATE_DATE = sysdate
822 where PRIVACY_ATTRIBUTE_CODE = attribute_code ;
823
824
825
826
827 /* insert into FND_PRIVACY_ATTRIBUTES_B (PRIVACY_ATTRIBUTE_CODE,
828 PRIVACY_ATTRIBUTE_TYPE,
829 SENSITIVITY,
830 PII_FLAG,
831 LOCKED_FLAG,
832 OBJECT_VERSION_NUMBER,
833 DSCRAM_LEVEL,
834 DSCRAM_ALGO_ID,
835 CREATED_BY,
836 CREATION_DATE,
837 LAST_UPDATED_BY,
838 LAST_UPDATE_DATE,
839 LAST_UPDATE_LOGIN)
840 VALUES (attribute_code,
841 'Base',
842 'Private',
843 'N',
844 'N',
845 0,
846 B_DSCRAM_LEVEL_USER_DEFINED,
847 algoid,
848 l_created_by,
849 sysdate,
850 l_last_updated_by,
851 sysdate,
852 l_last_update_login); */
853
854 insert into FND_PRIVACY_ATTRIBUTES_TL (PRIVACY_ATTRIBUTE_CODE,
855 PRIVACY_ATTRIBUTE_NAME,
856 DESCRIPTION,
857 CREATED_BY,
858 CREATION_DATE,
859 LAST_UPDATED_BY,
860 LAST_UPDATE_DATE,
861 LAST_UPDATE_LOGIN,
862 language,
863 source_lang
864 )
865 select
866 attribute_code,
867 update_pii_attribute.attribute_name,
868 update_pii_attribute.l_description,
869 update_pii_attribute.l_created_by,
870 sysdate,
871 update_pii_attribute.l_last_updated_by,
872 sysdate,
873 update_pii_attribute.l_last_update_login,
874 l.language_code,
875 userenv('LANG')
876 from FND_LANGUAGES L
877 where L.INSTALLED_FLAG in ('I', 'B')
878 and not exists
879 (select NULL
880 from FND_PRIVACY_ATTRIBUTES_TL T
881 where T.PRIVACY_ATTRIBUTE_CODE = attribute_code
882 and T.LANGUAGE = L.LANGUAGE_CODE);
883
884 commit;
885 EXCEPTION
886 when others then
887 rollback;
888 raise;
889
890 end update_pii_attribute;
891
892
893
894 /* update a PII privacy attribute */
895
896 procedure pre_update_pii_attribute
897 (
898 attribute_code IN VARCHAR2
899 ) is
900 algoid number := NULL;
901
902 begin
903
904 --delete from FND_PRIVACY_ATTRIBUTES_B where privacy_attribute_code = attribute_code;
905 delete from FND_PRIVACY_ATTRIBUTES_TL where privacy_attribute_code = attribute_code;
906 delete from FND_COL_PRIV_ATTRIBUTES_B where privacy_attribute_code = attribute_code;
907 delete from FND_OAM_DS_PII_EXTENSIONS where privacy_attribute_code = attribute_code;
908 EXCEPTION
909 when others then
910 rollback;
911 raise;
912
913 end pre_update_pii_attribute;
914
915
916 /* add a new PII privacy attribute column for a privacy attribute with attribute_code*/
917
918 procedure add_attribute_col
919 (
920 attribute_code IN VARCHAR2,
921 l_table_name IN VARCHAR2,
922 l_column_name IN VARCHAR2,
923 l_where_clause IN VARCHAR2 DEFAULT NULL,
924 l_algorithm IN VARCHAR2 DEFAULT NULL,
925 l_created_by IN NUMBER,
926 l_last_updated_by IN NUMBER,
927 l_last_update_login IN NUMBER
928 ) is
929
930 l_algo_id NUMBER := NULL;
931 l_application_id NUMBER;
932 l_table_id NUMBER;
933 l_column_id NUMBER;
934 l_column_seq NUMBER;
935
936 l_extension_id NUMBER;
937 BEGIN
938 --if the user specified an algorithm, get its ID
939 IF l_algorithm IS NOT NULL THEN
940 l_algo_id := FND_OAM_DS_ALGOS_PKG.GET_ALGO_ID(l_algorithm);
941 END IF;
942
943 --query the application_id, table_id and column_id once so we don't have to do the
944 --ugly fnd_columns full table scan twice and we can check for a single entry
945 SELECT T.application_id, T.table_id, C.column_id, C.column_sequence
946 INTO l_application_id, l_table_id, l_column_id, l_column_seq
947 FROM FND_TABLES T, FND_COLUMNS C
948 WHERE T.TABLE_NAME = upper(l_table_name)
949 AND T.TABLE_ID = C.TABLE_ID
950 AND C.COLUMN_NAME = upper(l_column_name);
951
952 --insert into fnd_col_priv_attributes_b
953 INSERT INTO FND_COL_PRIV_ATTRIBUTES_B (PRIVACY_ATTRIBUTE_CODE,
954 APPLICATION_ID,
955 TABLE_ID,
956 COLUMN_ID,
957 COLUMN_SEQUENCE,
958 OBJECT_VERSION_NUMBER,
959 DSCRAM_LEVEL,
960 DSCRAM_ALGO_ID,
961 CREATED_BY,
962 CREATION_DATE,
963 LAST_UPDATED_BY,
964 LAST_UPDATE_DATE,
965 LAST_UPDATE_LOGIN
966 )
967 VALUES (attribute_code,
968 l_application_id,
969 l_table_id,
970 l_column_id,
971 l_column_seq,
972 0,
973 B_DSCRAM_LEVEL_USER_DEFINED,
974 l_algo_id,
975 l_created_by,
976 SYSDATE,
977 l_last_updated_by,
978 SYSDATE,
979 l_last_update_login);
980
981 --if it has a where clause, insert a pii_extensions row
982 IF l_where_clause IS NOT NULL THEN
983 INSERT INTO FND_OAM_DS_PII_EXTENSIONS (PII_EXTENSION_ID,
984 PRIVACY_ATTRIBUTE_CODE,
985 APPLICATION_ID,
986 TABLE_ID,
987 COLUMN_ID,
988 WHERE_CLAUSE,
989 CREATED_BY,
990 CREATION_DATE,
991 LAST_UPDATED_BY,
992 LAST_UPDATE_DATE,
993 LAST_UPDATE_LOGIN
994 )
995 VALUES (FND_OAM_DS_PII_EXTENSIONS_S.NEXTVAL,
996 attribute_code,
997 l_application_id,
998 l_table_id,
999 l_column_id,
1000 l_where_clause,
1001 l_created_by,
1002 SYSDATE,
1003 l_last_updated_by,
1004 SYSDATE,
1005 l_last_update_login)
1006 RETURNING pii_extension_id INTO l_extension_id;
1007 END IF;
1008
1009 --submit the changes
1010 COMMIT;
1011
1012 EXCEPTION
1013 when others then
1014 rollback;
1015 raise;
1016 end add_attribute_col;
1017
1018
1019 /*
1020 -- remove a PII privacy attribute column for a privacy attribute with attribute_code
1021 procedure remove_attribute_col
1022 (
1023 attribute_code IN VARCHAR2,
1024 l_table_name IN VARCHAR2,
1025 l_column_name IN VARCHAR2
1026 ) is
1027 begin
1028
1029 --FIXME: this incurs an unnecessary full table scan of fnd_columns when we should be deleting using the
1030 --known attribute_code, application_id, table_id, column_id to get the index.
1031 delete from FND_COL_PRIV_ATTRIBUTES_B where
1032 PRIVACY_ATTRIBUTE_CODE = attribute_code
1033 and table_id = (select table_id from fnd_tables
1034 where table_name = upper(l_table_name))
1035 and column_id = (select column_id from fnd_columns c, fnd_tables t where
1036 c.table_id = t.table_id
1037 and t.table_name = upper(l_table_name)
1038 and c.column_name = upper(l_column_name));
1039
1040 commit;
1041 EXCEPTION
1042 when others then
1043 rollback;
1044 raise;
1045
1046 end remove_attribute_col;
1047 */
1048
1049 /*
1050 --ilawler: Removed these procedures because enabling will occur via a sql script shipped to
1051 --customers.
1052
1053 -- This procedure enables the data scrambling configuration UI display.
1054 -- It changes profile OAM_DSCRAM_ENABLED value to be 'YES'
1055
1056 procedure enable_dscram_ui
1057 is
1058 begin
1059
1060 --incurs full table scan without application_id also in where clause
1061 update fnd_profile_option_values set profile_option_value = 'YES'
1062 where profile_option_id =
1063 (select profile_option_id from fnd_profile_options f, fnd_application a
1064 where f.application_id=a.application_id
1065 and a.application_short_name='FND'
1066 and f.profile_option_name='OAM_DSCRAM_ENABLED');
1067
1068 commit;
1069 EXCEPTION
1070 when others then
1071 rollback;
1072 raise;
1073
1074 end enable_dscram_ui;
1075
1076
1077 -- This procedure disables the data scrambling configuration UI display.
1078 -- It changes profile OAM_DSCRAM_ENABLED value to be 'NO'
1079
1080 procedure disable_dscram_ui
1081 is
1082 begin
1083
1084 update fnd_profile_option_values set profile_option_value = 'NO'
1085 where profile_option_id =
1086 (select profile_option_id from fnd_profile_options f, fnd_application a
1087 where f.application_id=a.application_id
1088 and a.application_short_name='FND'
1089 and f.profile_option_name='OAM_DSCRAM_ENABLED');
1090
1091 commit;
1092 EXCEPTION
1093 when others then
1094 rollback;
1095 raise;
1096
1097 end disable_dscram_ui;
1098 */
1099
1100 -- Public
1101 PROCEDURE IMPORT_POLICY_SET_TO_DSCFG
1102 IS
1103 l_ctxt VARCHAR2(60) := PKG_NAME||'IMPORT_POLICY_SET_TO_DSCFG';
1104
1105 --cursor to query policies for a policy set
1106 CURSOR pset_policy_c (v_psetid fnd_oam_ds_psets_b.policyset_id%TYPE) IS
1107 select policy_id
1108 from fnd_oam_ds_pset_elmnts
1109 where policyset_id = v_psetid;
1110 policy_rec pset_policy_c%ROWTYPE;
1111
1112 --CURSOR policy_c (v_policyid fnd_oam_ds_policies_b.policy_id%TYPE) IS
1113 -- select element_type, PRIVACY_ATTRIBUTE_CODE, DELETE_ID
1114 -- from fnd_oam_ds_policy_elmnts
1115 -- where policy_id = v_policyid;
1116
1117 --cursor to query pii attributes for a policy, assumes that the pii_extensions table
1118 --has only 0 or 1 corresponding rows for an attribute.
1119 CURSOR policy_attri_c (v_policyid fnd_oam_ds_policies_b.policy_id%TYPE) IS
1120 select pe.privacy_attribute_code privacy_attribute_code,
1121 ou.oracle_username owner,
1122 t.table_name table_name,
1123 c.column_name column_name,
1124 decode(c.column_type,
1125 'V', 'VARCHAR2',
1126 'D', 'DATE',
1127 'N', 'NUMBER', c.column_type) column_type,
1128 pa.dscram_algo_id attri_algo,
1129 pc.dscram_algo_id col_algo,
1130 dpe2.algo_id ext_attri_algo,
1131 dpe.algo_id ext_col_algo,
1132 dpe.where_clause where_clause
1133 from fnd_oam_ds_policy_elmnts pe,
1134 fnd_privacy_attributes_b pa,
1135 fnd_col_priv_attributes_b pc,
1136 fnd_tables t,
1137 fnd_columns c,
1138 fnd_oam_ds_pii_extensions dpe,
1139 fnd_oam_ds_pii_extensions dpe2,
1140 fnd_product_installations pi,
1141 fnd_oracle_userid ou
1142 where pe.policy_id = v_policyid
1143 and pe.element_type = 'PII_ATTRIBUTE'
1144 and pe.privacy_attribute_code = pa.privacy_attribute_code
1145 and pa.privacy_attribute_code = pc.privacy_attribute_code
1146 and pc.application_id = t.application_id
1147 and pc.table_id = t.table_id
1148 and pc.application_id = c.application_id
1149 and pc.table_id = c.table_id
1150 and pc.column_id = c.column_id
1151 and pc.privacy_attribute_code = dpe.privacy_attribute_code(+)
1152 and pc.application_id = dpe.application_id(+)
1153 and pc.table_id = dpe.table_id(+)
1154 and pc.column_id = dpe.column_id(+)
1155 and pc.privacy_attribute_code = dpe2.privacy_attribute_code(+)
1156 and dpe2.table_id(+) IS NULL
1157 and pc.application_id = pi.application_id
1158 and pi.oracle_id = ou.oracle_id;
1159 attri_rec policy_attri_c%ROWTYPE;
1160
1161 --cursor to query deletes/truncates for a policy
1162 CURSOR policy_delete_c (v_policyid fnd_oam_ds_policies_b.policy_id%TYPE) IS
1163 select d.delete_id, d.owner, d.table_name, d.where_clause, d.use_truncate_flag
1164 from fnd_oam_ds_deletes d, fnd_oam_ds_policy_elmnts pe
1165 where pe.policy_id = v_policyid
1166 and upper(pe.element_type) = 'DML_DELETE'
1167 and d.delete_id = pe.delete_id;
1168 delete_rec policy_delete_c%ROWTYPE;
1169
1170 l_pset_id NUMBER;
1171 l_algo_id NUMBER;
1172 l_algo_text VARCHAR2(4000);
1173 l_algo_weight NUMBER;
1174 l_object_id NUMBER;
1175 begin
1176
1177 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1178
1179 -- pset_id := 3022;
1180 -- OPEN pset_policy_c(pset_id);
1181
1182 fnd_oam_debug.log(1, l_ctxt, 'Obtaining Policy Set ID...');
1183 l_pset_id := FND_OAM_DSCFG_API_PKG.GET_CURRENT_POLICYSET_ID;
1184
1185 fnd_oam_debug.log(1, l_ctxt, 'Querying Policies for Policy Set ID:'||l_pset_id);
1186 OPEN pset_policy_c(l_pset_id);
1187
1188 --loop over the policies of the policy set
1189 LOOP
1190 FETCH pset_policy_c INTO policy_rec;
1191 EXIT WHEN pset_policy_c%NOTFOUND;
1192 fnd_oam_debug.log(1, l_ctxt, 'Processing Policy ID: '||policy_rec.policy_id);
1193
1194 --fetch the PII_ATTRIBUTEs of the policy
1195 OPEN policy_attri_c(policy_rec.policy_id);
1196
1197 LOOP
1198 --for each PII_ATTRIBUTE element,
1199 --if there is no attribute for this policy, exit this loop
1200 FETCH policy_attri_c INTO attri_rec;
1201 EXIT WHEN policy_attri_c%NOTFOUND;
1202 fnd_oam_debug.log(1, l_ctxt, 'Processing Attribute Code: '||attri_rec.privacy_attribute_code);
1203
1204 --algo_id resolution is in the following order, higher is more specific:
1205 -- 1)fnd_oam_ds_pii_extensions for col_priv
1206 -- 2)fnd_col_priv_attributes_b
1207 -- 3)fnd_oam_ds_pii_extensions for attri (privacy_attribute_code set, table_id NULL)
1208 -- 3)fnd_privacy_attributes_b
1209 l_algo_id := NVL(attri_rec.ext_col_algo, NVL(attri_rec.col_algo, NVL(attri_rec.ext_attri_algo, attri_rec.attri_algo)));
1210
1211 --if there is no algo id specified, use the default algo for this datatype
1212 IF l_algo_id IS NULL THEN
1213 fnd_oam_debug.log(1, l_ctxt, 'Fetching default algo for datatype: '||attri_rec.column_type);
1214 FND_OAM_DS_ALGOS_PKG.GET_DEFAULT_ALGO_FOR_DATATYPE(P_DATATYPE => attri_rec.column_type,
1215 X_ALGO_ID => l_algo_id);
1216 END IF;
1217
1218 --convert the algo_id to the new_column_value sql
1219 fnd_oam_debug.log(1, l_ctxt, 'Resolving Algo ID: '||l_algo_id);
1220 FND_OAM_DS_ALGOS_PKG.RESOLVE_ALGO_ID(p_algo_id => l_algo_id,
1221 p_table_owner => attri_rec.owner,
1222 p_table_name => attri_rec.table_name,
1223 p_column_name => attri_rec.column_name,
1224 x_new_column_value => l_algo_text,
1225 X_WEIGHT_MODIFIER => l_algo_weight);
1226
1227 --add a dml_update_segment object to the intermediate config
1228 FND_OAM_DSCFG_API_PKG.ADD_DML_UPDATE_SEGMENT(P_TABLE_OWNER => attri_rec.owner,
1229 P_TABLE_NAME => attri_rec.table_name,
1230 P_COLUMN_NAME => attri_rec.column_name,
1231 P_NEW_COLUMN_VALUE => l_algo_text,
1232 P_WHERE_CLAUSE => attri_rec.where_clause,
1233 P_WEIGHT_MODIFIER => l_algo_weight,
1234 X_OBJECT_ID => l_object_id);
1235
1236 END LOOP;
1237 CLOSE policy_attri_c;
1238
1239 -- query out the delete/truncate statements for the policy
1240 fnd_oam_debug.log(1, l_ctxt, 'Querying Deletes/Truncates for Policy...');
1241 OPEN policy_delete_c(policy_rec.policy_id);
1242
1243 LOOP
1244 --for each DML_DELETE element
1245 --if there is no deletes for this policy, exit this loop
1246 FETCH policy_delete_c INTO delete_rec;
1247 EXIT WHEN policy_delete_c%NOTFOUND;
1248 fnd_oam_debug.log(1, l_ctxt, 'Processing Delete ID: '||delete_rec.delete_id);
1249 --see if the delete's a truncate
1250 IF delete_rec.use_truncate_flag IS NOT NULL AND delete_rec.USE_TRUNCATE_FLAG = FND_API.G_TRUE THEN
1251 FND_OAM_DSCFG_API_PKG.ADD_DML_TRUNCATE_STMT(P_TABLE_OWNER => delete_rec.owner,
1252 P_TABLE_NAME => delete_rec.table_name,
1253 x_object_id => l_object_id);
1254 ELSE
1255 FND_OAM_DSCFG_API_PKG.ADD_DML_DELETE_STMT(P_TABLE_OWNER => delete_rec.owner,
1256 P_TABLE_NAME => delete_rec.table_name,
1257 P_WHERE_CLAUSE => delete_rec.where_clause,
1258 x_object_id => l_object_id);
1259 END IF;
1260
1261 END LOOP;
1262 CLOSE policy_delete_c;
1263
1264 --commit after every policy
1265 COMMIT;
1266
1267 END LOOP;
1268 CLOSE pset_policy_c;
1269
1270 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1271 EXCEPTION
1272 WHEN NO_DATA_FOUND THEN
1273 ROLLBACK;
1274 fnd_oam_debug.log(6, l_ctxt, 'Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1275 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1276 RAISE;
1277 WHEN OTHERS THEN
1278 ROLLBACK;
1279 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1280 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1281 RAISE;
1282 end;
1283
1284 end fnd_oam_dscram;