[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* insert a new data scrambling policy */
procedure insert_policy
(
policyid OUT NOCOPY NUMBER,
pname in varchar2,
l_description IN VARCHAR2 DEFAULT NULL,
l_created_by IN NUMBER,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
begin
/* Get new ID */
select FND_OAM_DS_POLICIES_S.nextval
into policyid
from sys.dual;
/*insert into the base table*/
insert into FND_OAM_DS_POLICIES_B (
policy_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values
(policyid, l_created_by, sysdate,
l_last_updated_by, sysdate, l_last_update_login);
/*insert into the TL table*/
insert into FND_OAM_DS_POLICIES_TL (
policy_id,
policy_name,
description,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
language,
source_lang
)
select
policyid,insert_policy.pname,insert_policy.l_description, insert_policy.l_created_by,
sysdate, insert_policy.l_last_updated_by, sysdate, insert_policy.l_last_update_login,
l.language_code, userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_OAM_DS_POLICIES_TL T
where T.POLICY_ID = policyid
and T.LANGUAGE = L.LANGUAGE_CODE);
end insert_policy;
/* update a data scrambling policy */
procedure update_policy
(
policyid in number,
pname in varchar2,
l_description IN VARCHAR2 DEFAULT NULL,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
begin
/*update*/
update FND_OAM_DS_POLICIES_B
set LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = l_last_update_login
where policy_id = policyid;
delete from FND_OAM_DS_POLICIES_TL
where policy_id = policyid
and language in (select l.language_code
from fnd_languages l
where l.installed_flag in ('I', 'B'));
insert into FND_OAM_DS_POLICIES_TL (
policy_id,
policy_name,
description,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
language,
source_lang
)
select
update_policy.policyid,update_policy.pname,update_policy.l_description, update_policy.l_last_update_login,
sysdate, update_policy.l_last_updated_by, sysdate, update_policy.l_last_update_login,
l.language_code, userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_OAM_DS_POLICIES_TL T
where T.POLICY_ID = policyid
and T.LANGUAGE = L.LANGUAGE_CODE);
/* Delete all policy elements for this policy. */
delete from FND_OAM_DS_POLICY_ELMNTS where policy_id = policyid;
end update_policy;
/* insert a new data scrambling policy set */
procedure insert_policyset
(
psetid OUT NOCOPY NUMBER,
psetname in varchar2,
l_description IN VARCHAR2 DEFAULT NULL,
l_created_by IN NUMBER,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
begin
/* Get new ID */
select FND_OAM_DS_PSETS_S.nextval
into psetid
from sys.dual;
/*insert*/
insert into FND_OAM_DS_PSETS_B (
policyset_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values
(psetid, l_created_by, sysdate,
l_last_updated_by, sysdate, l_last_update_login);
insert into FND_OAM_DS_PSETS_TL (
policyset_id,
policyset_name,
description,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
language,
source_lang
)
select
psetid,insert_policyset.psetname,insert_policyset.l_description,
insert_policyset.l_created_by,
sysdate, insert_policyset.l_last_updated_by, sysdate,
insert_policyset.l_last_update_login,
l.language_code, userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_OAM_DS_PSETS_TL T
where T.POLICYSET_ID = psetid
and T.LANGUAGE = L.LANGUAGE_CODE);
end insert_policyset;
/* update a data scrambling policy set */
procedure update_policyset
(
psetid in number,
psetname in varchar2,
l_description IN VARCHAR2 DEFAULT NULL,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
begin
/*update*/
update FND_OAM_DS_PSETS_B
set LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = l_last_update_login
where policyset_id = psetid;
delete from FND_OAM_DS_PSETS_TL
where policyset_id = psetid
and language in (select l.language_code
from fnd_languages l
where l.installed_flag in ('I', 'B'));
insert into FND_OAM_DS_PSETS_TL (
policyset_id,
policyset_name,
description,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
language,
source_lang
)
select
update_policyset.psetid,update_policyset.psetname,update_policyset.l_description,
update_policyset.l_last_update_login,
sysdate, update_policyset.l_last_updated_by, sysdate,
update_policyset.l_last_update_login,
l.language_code, userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_OAM_DS_PSETS_TL T
where T.POLICYSET_ID = psetid
and T.LANGUAGE = L.LANGUAGE_CODE);
/* Delete all policy set elements for this policy set. */
delete from FND_OAM_DS_PSET_ELMNTS where policyset_id = psetid;
end update_policyset;
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
elementid number;
select FND_OAM_DS_POLICY_ELMNTS_S.nextval
into elementid
from sys.dual;
/*insert*/
insert into FND_OAM_DS_POLICY_ELMNTS(
policy_rel_id,
policy_id,
element_type,
privacy_attribute_code,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values
(elementid, policyid, 'PII_ATTRIBUTE', attribute_code, l_created_by, sysdate,
l_last_updated_by, sysdate, l_last_update_login);
delete from FND_OAM_DS_POLICY_ELMNTS where policy_id = policyid;
/* add a new delete element for a policy with policyid*/
procedure add_policy_del_element
(
policyid in number,
deleteid IN NUMBER,
l_created_by IN NUMBER,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
elementid number;
select FND_OAM_DS_POLICY_ELMNTS_S.nextval
into elementid
from sys.dual;
/*insert*/
insert into FND_OAM_DS_POLICY_ELMNTS(
policy_rel_id,
policy_id,
element_type,
delete_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values
(elementid, policyid, 'DML_DELETE', deleteid, l_created_by, sysdate,
l_last_updated_by, sysdate, l_last_update_login);
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
elementid number;
select FND_OAM_DS_PSET_ELMNTS_S.nextval
into elementid
from sys.dual;
/*insert*/
insert into FND_OAM_DS_PSET_ELMNTS(
policyset_rel_id,
policyset_id,
policy_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values
(elementid, psetid, policyid, l_created_by, sysdate,
l_last_updated_by, sysdate, l_last_update_login);
delete from FND_OAM_DS_PSET_ELMNTS where policyset_id = psetid;
/* add a new delete entry into FND_OAM_DS_DELETES */
procedure add_delete
(
l_table_name IN VARCHAR2,
l_owner IN VARCHAR2 DEFAULT NULL,
l_where_clause IN VARCHAR2 DEFAULT NULL,
l_use_truncate_flag IN VARCHAR2 DEFAULT NULL,
l_created_by IN NUMBER,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
deleteid number;
select FND_OAM_DS_DELETES_S.nextval
into deleteid
from sys.dual;
select ou.oracle_username into v_owner
from fnd_tables t,
fnd_product_installations pi,
fnd_oracle_userid ou
where t.table_name = upper(l_table_name)
and t.application_id = pi.application_id
and pi.oracle_id = ou.oracle_id;
select count(*) into v_count from fnd_oam_ds_deletes
where owner = v_owner
and table_name = l_table_name
and where_clause = l_where_clause
and use_truncate_flag = v_use_truncate_flag;
insert into FND_OAM_DS_DELETES(
delete_id,
table_name,
owner,
where_clause,
use_truncate_flag,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values
(deleteid, l_table_name, v_owner, l_where_clause, v_use_truncate_flag, l_created_by, sysdate,
l_last_updated_by, sysdate, l_last_update_login);
end add_delete;
/* add a new delete entry into FND_OAM_DS_DELETES */
procedure update_delete
(
l_delete_id IN VARCHAR2,
l_where_clause IN VARCHAR2 DEFAULT NULL,
l_use_truncate_flag IN VARCHAR2 DEFAULT NULL,
l_last_updated_by IN NUMBER
) is
deleteid number;
select count(*) into v_count from fnd_oam_ds_deletes
where delete_id=l_delete_id;
update FND_OAM_DS_DELETES
set where_clause=l_where_clause, use_truncate_flag = l_use_truncate_flag,
last_updated_by=l_last_updated_by ,last_update_date=sysdate
where delete_id=l_delete_id;
end update_delete;
/* remove a delete entry with deleteid from FND_OAM_DS_DELETES */
procedure remove_delete
(
deleteid in number
) is
begin
delete from FND_OAM_DS_DELETES where delete_id = deleteid;
end remove_delete;
procedure delete_pii_attribute
(
attribute_code in varchar2
) is
begin
delete from FND_PRIVACY_ATTRIBUTES_B where privacy_attribute_code = attribute_code;
delete from FND_PRIVACY_ATTRIBUTES_TL where privacy_attribute_code = attribute_code;
delete from FND_OAM_DS_POLICY_ELMNTS where privacy_attribute_code = attribute_code;
delete from FND_COL_PRIV_ATTRIBUTES_B where privacy_attribute_code = attribute_code;
delete from FND_OAM_DS_PII_EXTENSIONS where privacy_attribute_code = attribute_code;
end delete_pii_attribute;
procedure delete_policy
(
p_policy_id in NUMBER
) is
begin
delete from FND_OAM_DS_POLICIES_B where policy_id = p_policy_id;
delete from FND_OAM_DS_POLICIES_TL where policy_id = p_policy_id;
delete from FND_OAM_DS_PSET_ELMNTS where policy_id = p_policy_id;
delete from FND_OAM_DS_POLICY_ELMNTS where policy_id = p_policy_id;
end delete_policy;
procedure delete_pset
(
pset_id in NUMBER
) is
begin
delete from FND_OAM_DS_PSETS_B where policyset_id = pset_id;
delete from FND_OAM_DS_PSETS_TL where policyset_id = pset_id;
delete from FND_OAM_DS_PSET_ELMNTS where policyset_id = pset_id;
end delete_pset;
procedure delete_tbl_to_purge
(
deleteid in NUMBER
) is
begin
delete from FND_OAM_DS_DELETES where delete_id = deleteid;
delete from FND_OAM_DS_POLICY_ELMNTS where delete_id = deleteid;
end delete_tbl_to_purge;
/* insert a new PII privacy attribute */
procedure insert_pii_attribute
(
attribute_code OUT NOCOPY VARCHAR2,
attribute_name IN VARCHAR2,
l_algorithm IN VARCHAR2 DEFAULT NULL,
l_description IN VARCHAR2 DEFAULT NULL,
l_created_by IN NUMBER,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
aid number;
select FND_OAM_DS_ATTRI_S.nextval
into aid
from sys.dual;
insert into FND_PRIVACY_ATTRIBUTES_B (PRIVACY_ATTRIBUTE_CODE,
PRIVACY_ATTRIBUTE_TYPE,
SENSITIVITY,
PII_FLAG,
LOCKED_FLAG,
OBJECT_VERSION_NUMBER,
DSCRAM_LEVEL,
DSCRAM_ALGO_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (attribute_code,
'Base',
'Private',
'N',
'N',
0,
B_DSCRAM_LEVEL_USER_DEFINED,
algoid,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login);
insert into FND_PRIVACY_ATTRIBUTES_TL (PRIVACY_ATTRIBUTE_CODE,
PRIVACY_ATTRIBUTE_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
language,
source_lang
)
select
attribute_code,
insert_pii_attribute.attribute_name,
insert_pii_attribute.l_description,
insert_pii_attribute.l_created_by,
sysdate,
insert_pii_attribute.l_last_updated_by,
sysdate,
insert_pii_attribute.l_last_update_login,
l.language_code,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_PRIVACY_ATTRIBUTES_TL T
where T.PRIVACY_ATTRIBUTE_CODE = attribute_code
and T.LANGUAGE = L.LANGUAGE_CODE);
end insert_pii_attribute;
procedure update_pii_attribute
(
attribute_code IN VARCHAR2,
attribute_name IN VARCHAR2,
l_algorithm IN VARCHAR2 DEFAULT NULL,
l_description IN VARCHAR2 DEFAULT NULL,
l_created_by IN NUMBER,
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
aid number;
UPDATE FND_PRIVACY_ATTRIBUTES_B
SET DSCRAM_ALGO_ID = algoid,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_DATE = sysdate
where PRIVACY_ATTRIBUTE_CODE = attribute_code ;
/* insert into FND_PRIVACY_ATTRIBUTES_B (PRIVACY_ATTRIBUTE_CODE,
PRIVACY_ATTRIBUTE_TYPE,
SENSITIVITY,
PII_FLAG,
LOCKED_FLAG,
OBJECT_VERSION_NUMBER,
DSCRAM_LEVEL,
DSCRAM_ALGO_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (attribute_code,
'Base',
'Private',
'N',
'N',
0,
B_DSCRAM_LEVEL_USER_DEFINED,
algoid,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_last_update_login); */
insert into FND_PRIVACY_ATTRIBUTES_TL (PRIVACY_ATTRIBUTE_CODE,
PRIVACY_ATTRIBUTE_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
language,
source_lang
)
select
attribute_code,
update_pii_attribute.attribute_name,
update_pii_attribute.l_description,
update_pii_attribute.l_created_by,
sysdate,
update_pii_attribute.l_last_updated_by,
sysdate,
update_pii_attribute.l_last_update_login,
l.language_code,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_PRIVACY_ATTRIBUTES_TL T
where T.PRIVACY_ATTRIBUTE_CODE = attribute_code
and T.LANGUAGE = L.LANGUAGE_CODE);
end update_pii_attribute;
/* update a PII privacy attribute */
procedure pre_update_pii_attribute
(
attribute_code IN VARCHAR2
) is
algoid number := NULL;
delete from FND_PRIVACY_ATTRIBUTES_TL where privacy_attribute_code = attribute_code;
delete from FND_COL_PRIV_ATTRIBUTES_B where privacy_attribute_code = attribute_code;
delete from FND_OAM_DS_PII_EXTENSIONS where privacy_attribute_code = attribute_code;
end pre_update_pii_attribute;
l_last_updated_by IN NUMBER,
l_last_update_login IN NUMBER
) is
l_algo_id NUMBER := NULL;
SELECT T.application_id, T.table_id, C.column_id, C.column_sequence
INTO l_application_id, l_table_id, l_column_id, l_column_seq
FROM FND_TABLES T, FND_COLUMNS C
WHERE T.TABLE_NAME = upper(l_table_name)
AND T.TABLE_ID = C.TABLE_ID
AND C.COLUMN_NAME = upper(l_column_name);
INSERT INTO FND_COL_PRIV_ATTRIBUTES_B (PRIVACY_ATTRIBUTE_CODE,
APPLICATION_ID,
TABLE_ID,
COLUMN_ID,
COLUMN_SEQUENCE,
OBJECT_VERSION_NUMBER,
DSCRAM_LEVEL,
DSCRAM_ALGO_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (attribute_code,
l_application_id,
l_table_id,
l_column_id,
l_column_seq,
0,
B_DSCRAM_LEVEL_USER_DEFINED,
l_algo_id,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_last_update_login);
INSERT INTO FND_OAM_DS_PII_EXTENSIONS (PII_EXTENSION_ID,
PRIVACY_ATTRIBUTE_CODE,
APPLICATION_ID,
TABLE_ID,
COLUMN_ID,
WHERE_CLAUSE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (FND_OAM_DS_PII_EXTENSIONS_S.NEXTVAL,
attribute_code,
l_application_id,
l_table_id,
l_column_id,
l_where_clause,
l_created_by,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_last_update_login)
RETURNING pii_extension_id INTO l_extension_id;
delete from FND_COL_PRIV_ATTRIBUTES_B where
PRIVACY_ATTRIBUTE_CODE = attribute_code
and table_id = (select table_id from fnd_tables
where table_name = upper(l_table_name))
and column_id = (select column_id from fnd_columns c, fnd_tables t where
c.table_id = t.table_id
and t.table_name = upper(l_table_name)
and c.column_name = upper(l_column_name));
update fnd_profile_option_values set profile_option_value = 'YES'
where profile_option_id =
(select profile_option_id from fnd_profile_options f, fnd_application a
where f.application_id=a.application_id
and a.application_short_name='FND'
and f.profile_option_name='OAM_DSCRAM_ENABLED');
update fnd_profile_option_values set profile_option_value = 'NO'
where profile_option_id =
(select profile_option_id from fnd_profile_options f, fnd_application a
where f.application_id=a.application_id
and a.application_short_name='FND'
and f.profile_option_name='OAM_DSCRAM_ENABLED');
select policy_id
from fnd_oam_ds_pset_elmnts
where policyset_id = v_psetid;
select pe.privacy_attribute_code privacy_attribute_code,
ou.oracle_username owner,
t.table_name table_name,
c.column_name column_name,
decode(c.column_type,
'V', 'VARCHAR2',
'D', 'DATE',
'N', 'NUMBER', c.column_type) column_type,
pa.dscram_algo_id attri_algo,
pc.dscram_algo_id col_algo,
dpe2.algo_id ext_attri_algo,
dpe.algo_id ext_col_algo,
dpe.where_clause where_clause
from fnd_oam_ds_policy_elmnts pe,
fnd_privacy_attributes_b pa,
fnd_col_priv_attributes_b pc,
fnd_tables t,
fnd_columns c,
fnd_oam_ds_pii_extensions dpe,
fnd_oam_ds_pii_extensions dpe2,
fnd_product_installations pi,
fnd_oracle_userid ou
where pe.policy_id = v_policyid
and pe.element_type = 'PII_ATTRIBUTE'
and pe.privacy_attribute_code = pa.privacy_attribute_code
and pa.privacy_attribute_code = pc.privacy_attribute_code
and pc.application_id = t.application_id
and pc.table_id = t.table_id
and pc.application_id = c.application_id
and pc.table_id = c.table_id
and pc.column_id = c.column_id
and pc.privacy_attribute_code = dpe.privacy_attribute_code(+)
and pc.application_id = dpe.application_id(+)
and pc.table_id = dpe.table_id(+)
and pc.column_id = dpe.column_id(+)
and pc.privacy_attribute_code = dpe2.privacy_attribute_code(+)
and dpe2.table_id(+) IS NULL
and pc.application_id = pi.application_id
and pi.oracle_id = ou.oracle_id;
CURSOR policy_delete_c (v_policyid fnd_oam_ds_policies_b.policy_id%TYPE) IS
select d.delete_id, d.owner, d.table_name, d.where_clause, d.use_truncate_flag
from fnd_oam_ds_deletes d, fnd_oam_ds_policy_elmnts pe
where pe.policy_id = v_policyid
and upper(pe.element_type) = 'DML_DELETE'
and d.delete_id = pe.delete_id;
delete_rec policy_delete_c%ROWTYPE;
FND_OAM_DSCFG_API_PKG.ADD_DML_UPDATE_SEGMENT(P_TABLE_OWNER => attri_rec.owner,
P_TABLE_NAME => attri_rec.table_name,
P_COLUMN_NAME => attri_rec.column_name,
P_NEW_COLUMN_VALUE => l_algo_text,
P_WHERE_CLAUSE => attri_rec.where_clause,
P_WEIGHT_MODIFIER => l_algo_weight,
X_OBJECT_ID => l_object_id);
fnd_oam_debug.log(1, l_ctxt, 'Querying Deletes/Truncates for Policy...');
OPEN policy_delete_c(policy_rec.policy_id);
FETCH policy_delete_c INTO delete_rec;
EXIT WHEN policy_delete_c%NOTFOUND;
fnd_oam_debug.log(1, l_ctxt, 'Processing Delete ID: '||delete_rec.delete_id);
IF delete_rec.use_truncate_flag IS NOT NULL AND delete_rec.USE_TRUNCATE_FLAG = FND_API.G_TRUE THEN
FND_OAM_DSCFG_API_PKG.ADD_DML_TRUNCATE_STMT(P_TABLE_OWNER => delete_rec.owner,
P_TABLE_NAME => delete_rec.table_name,
x_object_id => l_object_id);
FND_OAM_DSCFG_API_PKG.ADD_DML_DELETE_STMT(P_TABLE_OWNER => delete_rec.owner,
P_TABLE_NAME => delete_rec.table_name,
P_WHERE_CLAUSE => delete_rec.where_clause,
x_object_id => l_object_id);
CLOSE policy_delete_c;