[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Uma Maheswari 13-Apr-2004 Bug 4068422 : Modified c_obj cursor to select objeect_id based on obj_name instead of datbase_object_name.
As obj_name and data_base_object_name will be same in security module, this change is done for using the index on the table.
mmkumar 28-Jun-2005 Bug 4431768 : Added a paqrameter for overwrite
Inside Update_Grant_Cond, removed code for cursor c_get_grant_cond.
prbhardw 18-Jul-2005 Inside Update_Grant_Cond, modified code to update condition number
mmkumar 21-JUL-2005 Closed the cursors whereever appropriate
gmaheswa 26-Jul-2005 Fnd Logging
pkpatel 10-Mar-2006 Bug 5081932 (Used wf_local_synch instead of private API wf_directory)
gmaheswa 19-Apr-2006 Bug: 4587521: Modified Modify_Policy to add long_predicate parameter to add_policy. this parameter allows max predicate where clause lenght to 32K
******************************************************************/
-- -----------------------------------------------------------------
-- Define the global variables to be used in this package.
-- -----------------------------------------------------------------
g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_SC_DATA_SEC_APIS_PKG';
SELECT database_object_name
FROM fnd_objects
WHERE object_id = v_object_id;
PROCEDURE Get_Valid_Grant_Vals (p_grant_select_flag IN VARCHAR2,
p_grant_insert_flag IN VARCHAR2,
p_grant_delete_flag IN VARCHAR2,
p_grant_update_flag IN VARCHAR2,
x_grant_select_flag OUT NOCOPY VARCHAR2,
x_grant_insert_flag OUT NOCOPY VARCHAR2,
x_grant_delete_flag OUT NOCOPY VARCHAR2,
x_grant_update_flag OUT NOCOPY VARCHAR2
);
p_select_text VARCHAR2,
p_obj_attrib_type VARCHAR2 )
RETURN BOOLEAN IS
BEGIN
RETURN IGS_SC_GRANTS_PVT.check_attrib_text ( p_table_name , p_select_text , p_obj_attrib_type );
data insertion into the igs_sc_grants table.
Validation is performed on the function_id,
user_group_id, and obj_group_id that these IDs
are currently in the IGS Security structure.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Insert_Grant (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN OUT NOCOPY igs_sc_grants.grant_id%TYPE,
p_function_id IN igs_sc_grants.function_id%TYPE,
p_user_group_id IN igs_sc_grants.user_group_id%TYPE,
p_obj_group_id IN igs_sc_grants.obj_group_id%TYPE,
p_grant_name IN igs_sc_grants.grant_name%TYPE,
p_grant_text IN igs_sc_grants.grant_text%TYPE,
p_grant_select_flag IN igs_sc_grants.grant_select_flag%TYPE DEFAULT 'N',
p_grant_insert_flag IN igs_sc_grants.grant_insert_flag%TYPE DEFAULT 'N',
p_grant_update_flag IN igs_sc_grants.grant_update_flag%TYPE DEFAULT 'N',
p_grant_delete_flag IN igs_sc_grants.grant_delete_flag%TYPE DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
)
IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Grant';
l_grant_select_flag igs_sc_grants.grant_select_flag%TYPE;
l_grant_delete_flag igs_sc_grants.grant_delete_flag%TYPE;
l_grant_update_flag igs_sc_grants.grant_update_flag%TYPE;
l_grant_insert_flag igs_sc_grants.grant_insert_flag%TYPE;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant';
||','||' Grant Name: '||p_grant_name||','||' Grant Text: '||p_grant_text||','||'Select Flag: '||p_grant_select_flag
||','||' Insert Flag: '||p_grant_insert_flag||','||' Update Flag: '||p_grant_update_flag||','||' Delete Flag: '||p_grant_delete_flag;
SAVEPOINT Insert_Grant_SP;
Get_Valid_Grant_Vals (p_grant_select_flag => p_grant_select_flag,
p_grant_insert_flag => p_grant_insert_flag,
p_grant_delete_flag => p_grant_delete_flag,
p_grant_update_flag => p_grant_update_flag,
x_grant_select_flag => l_grant_select_flag,
x_grant_insert_flag => l_grant_insert_flag,
x_grant_delete_flag => l_grant_delete_flag,
x_grant_update_flag => l_grant_update_flag);
INSERT
INTO igs_sc_grants
(grant_id,
function_id,
user_group_id,
obj_group_id,
grant_name,
grant_text,
grant_select_flag,
grant_insert_flag,
grant_update_flag,
grant_delete_flag,
locked_flag,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (NVL(p_grant_id,igs_sc_grants_s.nextval),
l_function_id,
l_user_group_id,
l_obj_group_id,
p_grant_name,
p_grant_text,
p_grant_select_flag,
l_grant_insert_flag,
l_grant_update_flag,
l_grant_delete_flag,
l_locked_flag,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1)
)
RETURNING grant_id INTO p_grant_id;
ROLLBACK TO Insert_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant.Ex_UN';
ROLLBACK TO Insert_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant.Ex_error';
ROLLBACK TO Insert_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant.Ex_others';
END Insert_Grant;
handling on inserting data into igs_sc_grant_conds
table. There is validation on the grant_id,
obj_attrib_id, and user_attrib_id values received
to ensure that these records have been inserted
into the OSS security data model.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Insert_Grant_Cond (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN igs_sc_grant_conds.grant_id%TYPE,
p_obj_attrib_id IN igs_sc_grant_conds.obj_attrib_id%TYPE,
p_user_attrib_id IN igs_sc_grant_conds.user_attrib_id%TYPE,
p_condition IN igs_sc_grant_conds.condition%TYPE,
p_text_value IN igs_sc_grant_conds.text_value%TYPE,
p_grant_cond_num IN igs_sc_grant_conds.grant_cond_num%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
)
IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Grant_Cond';
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant_Cond';
SAVEPOINT Insert_Grant_Cond_SP;
INSERT
INTO igs_sc_grant_conds
(grant_id,
grant_cond_num,
obj_attrib_id,
user_attrib_id,
condition,
text_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (l_grant_id,
p_grant_cond_num,
l_obj_attr_id,
l_user_attr_id,
p_condition,
p_text_value,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1)
);
ROLLBACK TO Insert_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant_Cond.Ex_error';
ROLLBACK TO Insert_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant_Cond.Ex_un';
ROLLBACK TO Insert_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Grant_Cond.Ex_others';
END Insert_Grant_Cond;
of the data insertion into igs_sc_obj_groups
table. There is validation to ensure that
the group_name is not null and that the value
for the default_policy_type is either 'G'- Global
or 'R' - Restricted.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Insert_Object_Group (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_obj_group_id IN OUT NOCOPY igs_sc_obj_groups.obj_group_id%TYPE,
p_obj_group_name IN igs_sc_obj_groups.obj_group_name%TYPE ,
p_default_policy_type IN igs_sc_obj_groups.default_policy_type%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Object_Group';
SAVEPOINT Insert_Object_Group_SP;
INSERT
INTO igs_sc_obj_groups
(obj_group_id,
obj_group_name,
default_policy_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (NVL(p_obj_group_id,igs_sc_obj_groups_s.nextval),
p_obj_group_name,
l_default_policy_type,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1)
)
RETURNING obj_group_id INTO p_obj_group_id;
ROLLBACK TO Insert_Object_Group_SP;
ROLLBACK TO Insert_Object_Group_SP;
ROLLBACK TO Insert_Object_Group_SP;
END Insert_Object_Group;
on insertion of data into igs_sc_obj_attribs table.
There is validation performed on the obj_group_id
and obj_attrib_id provided to ensure that the
records are present in the OSS data structure.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Insert_Object_Attr (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_obj_attrib_id IN OUT NOCOPY igs_sc_obj_attribs.obj_attrib_id%TYPE,
p_obj_group_id IN igs_sc_obj_attribs.obj_group_id%TYPE,
p_obj_attrib_name IN igs_sc_obj_attribs.obj_attrib_name%TYPE,
p_active_flag IN VARCHAR2 DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Object_Attr';
SAVEPOINT Insert_Object_Attr_SP;
INSERT
INTO igs_sc_obj_attribs
(obj_group_id,
obj_attrib_id,
obj_attrib_name,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
active_flag
)
VALUES (l_obj_group_id,
NVL(p_obj_attrib_id,igs_sc_obj_attribs_s.nextval),
p_obj_attrib_name,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1),
p_active_flag
)
RETURNING obj_attrib_id INTO p_obj_attrib_id;
ROLLBACK TO Insert_Object_Attr_SP;
ROLLBACK TO Insert_Object_Attr_SP;
ROLLBACK TO Insert_Object_Attr_SP;
END Insert_Object_Attr;
of data insertion into the igs_sc_obj_att_mths
table. There is validation performed on the
object_id, obj_attrib_id, obj_attrib_type, and
static_type to ensure that the values given are
in the OSS data model or have valid values expected
for the types.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Insert_Object_Attr_Method (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_object_id IN igs_sc_obj_att_mths.object_id%TYPE,
p_obj_attrib_id IN igs_sc_obj_att_mths.obj_attrib_id%TYPE,
p_obj_attrib_type IN igs_sc_obj_att_mths.obj_attrib_type%TYPE,
p_static_type IN igs_sc_obj_att_mths.static_type%TYPE,
p_select_text IN igs_sc_obj_att_mths.select_text%TYPE,
p_null_allow_flag IN VARCHAR2 DEFAULT 'N',
p_call_from_lct IN VARCHAR2 DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Object_Attr_Method';
SAVEPOINT Insert_Object_Attr_Method_SP;
IF NOT check_attrib_text ( l_object_name, p_select_text,l_obj_attrib_type) THEN
FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_GRNT_TXT_ERR');
FND_MESSAGE.SET_TOKEN('GRNT_TEXT', p_select_text);
INSERT
INTO igs_sc_obj_att_mths
(object_id,
obj_attrib_id,
obj_attrib_type,
static_type,
select_text,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
null_allow_flag
)
VALUES (l_object_id,
l_obj_attrib_id,
l_obj_attrib_type,
l_static_type,
p_select_text,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1),
p_null_allow_flag
);
ROLLBACK TO Insert_Object_Attr_Method_SP;
ROLLBACK TO Insert_Object_Attr_Method_SP;
ROLLBACK TO Insert_Object_Attr_Method_SP;
END Insert_Object_Attr_Method;
handling of the inserting of data into the
table igs_sc_obj_functns. There is validation
performed on the obj_group_id to ensure that
the value given is present in the OSS data
model.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Insert_Object_Func (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_function_id IN OUT NOCOPY igs_sc_obj_functns.function_id%TYPE,
p_obj_group_id IN igs_sc_obj_functns.obj_group_id%TYPE,
p_function_name IN igs_sc_obj_functns.function_name%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Object_Func';
SAVEPOINT Insert_Object_Func_SP;
INSERT
INTO igs_sc_obj_functns
(function_id,
obj_group_id,
function_name,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (NVL(igs_sc_obj_functns_s.nextval,p_function_id),
l_obj_group_id,
p_function_name,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1)
)
RETURNING function_id INTO p_function_id;
ROLLBACK TO Insert_Object_Func_SP;
ROLLBACK TO Insert_Object_Func_SP;
ROLLBACK TO Insert_Object_Func_SP;
END Insert_Object_Func;
the insertion of the data into igs_sc_objects
table. This ensures a link between the object
groups and the fnd objects resident in the system.
There is validation to ensure that the object_id,
and the obj_group_id are valid and present in the
data model.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Insert_Object (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_object_id IN OUT NOCOPY igs_sc_objects.object_id%TYPE,
p_obj_group_id IN igs_sc_objects.obj_group_id%TYPE,
p_obj_name IN fnd_objects.obj_name%TYPE ,
p_database_object_name IN fnd_objects.database_object_name%TYPE ,
p_pk1_column_name IN fnd_objects.pk1_column_name%TYPE ,
p_pk2_column_name IN fnd_objects.pk2_column_name%TYPE ,
p_pk3_column_name IN fnd_objects.pk3_column_name%TYPE ,
p_pk4_column_name IN fnd_objects.pk4_column_name%TYPE ,
p_pk5_column_name IN fnd_objects.pk5_column_name%TYPE ,
p_pk1_column_type IN fnd_objects.pk1_column_type%TYPE ,
p_pk2_column_type IN fnd_objects.pk2_column_type%TYPE ,
p_pk3_column_type IN fnd_objects.pk3_column_type%TYPE ,
p_pk4_column_type IN fnd_objects.pk4_column_type%TYPE ,
p_pk5_column_type IN fnd_objects.pk5_column_type%TYPE ,
p_select_flag IN VARCHAR2 DEFAULT 'Y',
p_insert_flag IN VARCHAR2 DEFAULT 'Y',
p_update_flag IN VARCHAR2 DEFAULT 'Y',
p_delete_flag IN VARCHAR2 DEFAULT 'Y',
p_enforce_par_sec_flag IN VARCHAR2 DEFAULT 'N',
p_active_flag IN VARCHAR2 DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
)
IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Object';
SELECT object_id
FROM fnd_objects
WHERE obj_name = p_obj_name;
SAVEPOINT Insert_Object_SP;
SELECT fnd_objects_s.nextval INTO l_object_id FROM DUAL;
FND_OBJECTS_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_object_id =>l_object_id,
x_obj_name => p_obj_name,
x_pk1_column_name => p_pk1_column_name,
x_pk2_column_name => p_pk2_column_name,
x_pk3_column_name => p_pk3_column_name,
x_pk4_column_name => p_pk4_column_name,
x_pk5_column_name => p_pk5_column_name,
x_pk1_column_type => p_pk1_column_type,
x_pk2_column_type => p_pk2_column_type,
x_pk3_column_type => p_pk3_column_type,
x_pk4_column_type => p_pk4_column_type,
x_pk5_column_type => p_pk5_column_type,
x_application_id => l_application_id,
x_database_object_name => p_database_object_name,
x_display_name => p_obj_name,
x_description => p_obj_name,
x_creation_date => SYSDATE,
x_created_by => NVL(fnd_global.user_id,-1),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_global.user_id,-1),
x_last_update_login => NVL(fnd_global.login_id, -1));
INSERT
INTO igs_sc_objects
(object_id,
obj_group_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
select_flag,
insert_flag,
update_flag,
delete_flag,
enforce_par_sec_flag,
active_flag
)
VALUES (l_object_id,
l_obj_group_id,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1),
p_select_flag,
p_insert_flag,
p_update_flag,
p_delete_flag,
p_enforce_par_sec_flag,
p_active_flag
);
ROLLBACK TO Insert_Object_SP;
ROLLBACK TO Insert_Object_SP;
ROLLBACK TO Insert_Object_SP;
END Insert_Object;
the insertion of data into the table
igs_sc_usr_attribs. There is validation
to ensure that the static_type and
user_attrib_type are valid values.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
*****************************************************************/
PROCEDURE Insert_User_Attr (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_user_attrib_id IN OUT NOCOPY igs_sc_usr_attribs.user_attrib_id%TYPE,
p_user_attrib_name IN igs_sc_usr_attribs.user_attrib_name%TYPE,
p_user_attrib_type IN igs_sc_usr_attribs.user_attrib_type%TYPE,
p_static_type IN igs_sc_usr_attribs.static_type%TYPE,
p_select_text IN igs_sc_usr_attribs.select_text%TYPE,
p_active_flag IN VARCHAR2 DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_User_Attr';
SAVEPOINT Insert_User_Attr_SP;
IF (p_select_text IS NULL) THEN
FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_INV_SELECT_TEXT');
IF NOT check_attrib_text ( 'DUAL', p_select_text,l_user_attrib_type) THEN
FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_GRNT_TXT_ERR');
FND_MESSAGE.SET_TOKEN('GRNT_TEXT', p_select_text);
INSERT
INTO igs_sc_usr_attribs
(user_attrib_id,
user_attrib_name,
user_attrib_type,
static_type,
select_text,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
active_flag
)
VALUES ( NVL(p_user_attrib_id,igs_sc_usr_attribs_s.nextval),
p_user_attrib_name,
l_user_attrib_type,
l_static_type,
p_select_text,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1),
p_active_flag
)
RETURNING user_attrib_id INTO p_user_attrib_id;
ROLLBACK TO Insert_User_Attr_SP;
ROLLBACK TO Insert_User_Attr_SP;
ROLLBACK TO Insert_User_Attr_SP;
END Insert_User_Attr;
PROCEDURE Insert_Local_Role (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_role_name IN VARCHAR2,
p_role_display_name IN VARCHAR2,
p_orig_system IN VARCHAR2,
p_orig_system_id IN NUMBER,
p_language IN VARCHAR2 DEFAULT NULL,
p_territory IN VARCHAR2 DEFAULT NULL,
p_role_description IN VARCHAR2 DEFAULT NULL,
p_notification_preference IN VARCHAR2 DEFAULT 'MAILHTML',
p_email_address IN VARCHAR2 DEFAULT NULL,
p_fax IN VARCHAR2 DEFAULT NULL,
p_status IN VARCHAR2 DEFAULT 'ACTIVE',
p_expiration_date IN DATE DEFAULT NULL,
p_start_date IN DATE DEFAULT SYSDATE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Local_Role';
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_Role';
SAVEPOINT Insert_Local_Role_SP;
ROLLBACK TO Insert_Local_Role_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_Role.Ex_UN';
ROLLBACK TO Insert_Local_Role_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_Role.Ex_Error';
ROLLBACK TO Insert_Local_Role_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_Role.Ex_others';
END Insert_Local_Role;
PROCEDURE Insert_Local_User_Role (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_user_name IN wf_local_user_roles.user_name%TYPE,
p_role_name IN wf_local_user_roles.role_name%TYPE,
p_user_orig_system IN wf_local_user_roles.user_orig_system%TYPE,
p_user_orig_system_id IN wf_local_user_roles.user_orig_system_id%TYPE,
p_role_orig_system IN wf_local_user_roles.role_orig_system%TYPE,
p_role_orig_system_id IN wf_local_user_roles.role_orig_system_id%TYPE,
p_start_date IN wf_local_user_roles.start_date%TYPE,
p_expiration_date IN wf_local_user_roles.expiration_date%TYPE,
p_security_group_id IN wf_local_user_roles.security_group_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
)
IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Local_User_Role';
SELECT 'X'
FROM fnd_user
WHERE user_name = p_user_name
AND user_id = p_user_orig_system_id;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_User_Role';
SAVEPOINT Insert_Local_User_Role_SP;
ROLLBACK TO Insert_Local_User_Role_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_User_Role.Ex_UN';
ROLLBACK TO Insert_Local_User_Role_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_User_Role.Ex_error';
ROLLBACK TO Insert_Local_User_Role_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Insert_Local_User_Role.Ex_others';
END Insert_Local_User_Role;
the insertion of the data into igs_sc_objects
table. This ensures a link between the object
groups and the fnd objects resident in the system.
There is validation to ensure that the object_id,
and the obj_group_id are valid and present in the
data model.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Update_Object (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_object_id IN igs_sc_objects.object_id%TYPE,
p_obj_group_id IN igs_sc_objects.obj_group_id%TYPE,
p_obj_name IN fnd_objects.obj_name%TYPE ,
p_database_object_name IN fnd_objects.database_object_name%TYPE ,
p_pk1_column_name IN fnd_objects.pk1_column_name%TYPE ,
p_pk2_column_name IN fnd_objects.pk2_column_name%TYPE ,
p_pk3_column_name IN fnd_objects.pk3_column_name%TYPE ,
p_pk4_column_name IN fnd_objects.pk4_column_name%TYPE ,
p_pk5_column_name IN fnd_objects.pk5_column_name%TYPE ,
p_pk1_column_type IN fnd_objects.pk1_column_type%TYPE ,
p_pk2_column_type IN fnd_objects.pk2_column_type%TYPE ,
p_pk3_column_type IN fnd_objects.pk3_column_type%TYPE ,
p_pk4_column_type IN fnd_objects.pk4_column_type%TYPE ,
p_pk5_column_type IN fnd_objects.pk5_column_type%TYPE ,
p_select_flag IN VARCHAR2 DEFAULT 'Y',
p_insert_flag IN VARCHAR2 DEFAULT 'Y',
p_update_flag IN VARCHAR2 DEFAULT 'Y',
p_delete_flag IN VARCHAR2 DEFAULT 'Y',
p_enforce_par_sec_flag IN VARCHAR2 DEFAULT 'N',
p_active_flag IN VARCHAR2 DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
)
IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Object';
SAVEPOINT Update_Object_SP;
FND_OBJECTS_PKG.UPDATE_ROW (
x_object_id =>p_object_id,
x_obj_name => p_obj_name,
x_pk1_column_name => p_pk1_column_name,
x_pk2_column_name => p_pk2_column_name,
x_pk3_column_name => p_pk3_column_name,
x_pk4_column_name => p_pk4_column_name,
x_pk5_column_name => p_pk5_column_name,
x_pk1_column_type => p_pk1_column_type,
x_pk2_column_type => p_pk2_column_type,
x_pk3_column_type => p_pk3_column_type,
x_pk4_column_type => p_pk4_column_type,
x_pk5_column_type => p_pk5_column_type,
x_application_id => 8405,
x_database_object_name => p_database_object_name,
x_display_name => p_obj_name,
x_description => p_obj_name,
x_last_update_date => sysdate,
x_last_updated_by => nvl(fnd_global.user_id,-1),
x_last_update_login => nvl(fnd_global.login_id, -1));
UPDATE IGS_SC_OBJECTS SET
LAST_UPDATED_BY = NVL(FND_GLOBAL.user_id,-1),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = NVL(FND_GLOBAL.login_id, -1),
SELECT_FLAG = P_SELECT_FLAG,
INSERT_FLAG = P_INSERT_FLAG,
UPDATE_FLAG = P_UPDATE_FLAG,
DELETE_FLAG = P_DELETE_FLAG,
ENFORCE_PAR_SEC_FLAG = P_ENFORCE_PAR_SEC_FLAG,
ACTIVE_FLAG = P_ACTIVE_FLAG
WHERE OBJECT_ID = P_OBJECT_ID
AND OBJ_GROUP_ID = P_OBJ_GROUP_ID;
ROLLBACK TO Update_Object_SP;
ROLLBACK TO Update_Object_SP;
ROLLBACK TO Update_Object_SP;
END Update_Object;
PROCEDURE Update_Local_Role (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_role_name IN VARCHAR2,
p_role_display_name IN VARCHAR2,
p_orig_system IN VARCHAR2,
p_orig_system_id IN NUMBER,
p_language IN VARCHAR2 DEFAULT NULL,
p_territory IN VARCHAR2 DEFAULT NULL,
p_role_description IN VARCHAR2 DEFAULT NULL,
p_notification_preference IN VARCHAR2 DEFAULT 'MAILHTML',
p_email_address IN VARCHAR2 DEFAULT NULL,
p_fax IN VARCHAR2 DEFAULT NULL,
p_status IN VARCHAR2 DEFAULT 'ACTIVE',
p_expiration_date IN DATE DEFAULT NULL,
p_start_date IN DATE DEFAULT SYSDATE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Local_Role';
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_Role';
SAVEPOINT Update_Local_Roles_SP;
wf_event.addparametertolist('UPDATEONLY','TRUE',l_parameters);
ROLLBACK TO Update_Local_Roles_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_Role.Ex_error';
ROLLBACK TO Update_Local_Roles_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_Role.Ex_un';
ROLLBACK TO Update_Local_Roles_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_Role.Ex_others';
END Update_Local_Role;
PROCEDURE Update_Local_User_Role (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_user_name IN wf_local_user_roles.user_name%TYPE,
p_role_name IN wf_local_user_roles.role_name%TYPE,
p_user_orig_system IN wf_local_user_roles.user_orig_system%TYPE,
p_user_orig_system_id IN wf_local_user_roles.user_orig_system_id%TYPE,
p_role_orig_system IN wf_local_user_roles.role_orig_system%TYPE,
p_role_orig_system_id IN wf_local_user_roles.role_orig_system_id%TYPE,
p_start_date IN wf_local_user_roles.start_date%TYPE,
p_expiration_date IN wf_local_user_roles.expiration_date%TYPE,
p_security_group_id IN wf_local_user_roles.security_group_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Local_User_Role';
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_User_Role';
SAVEPOINT Update_Local_User_Roles_SP;
ROLLBACK TO Update_Local_User_Roles_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_User_Role.Ex_un';
ROLLBACK TO Update_Local_User_Roles_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_User_Role.Ex_error';
ROLLBACK TO Update_Local_User_Roles_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Local_User_Role.Ex_others';
END Update_Local_User_Role;
PROCEDURE Update_Grant (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN igs_sc_grants.grant_id%TYPE,
p_function_id IN igs_sc_grants.function_id%TYPE,
p_user_group_id IN igs_sc_grants.user_group_id%TYPE,
p_grant_name IN igs_sc_grants.grant_name%TYPE,
p_grant_text IN igs_sc_grants.grant_text%TYPE,
p_grant_select_flag IN igs_sc_grants.grant_select_flag%TYPE DEFAULT 'N',
p_grant_insert_flag IN igs_sc_grants.grant_insert_flag%TYPE DEFAULT 'N',
p_grant_update_flag IN igs_sc_grants.grant_update_flag%TYPE DEFAULT 'N',
p_grant_delete_flag IN igs_sc_grants.grant_delete_flag%TYPE DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Grant';
l_update_oper CONSTANT VARCHAR2(30) := 'UPDATE';
l_grant_select_flag igs_sc_grants.grant_select_flag%TYPE;
l_grant_delete_flag igs_sc_grants.grant_delete_flag%TYPE;
l_grant_update_flag igs_sc_grants.grant_update_flag%TYPE;
l_grant_insert_flag igs_sc_grants.grant_insert_flag%TYPE;
SELECT grant_name,
grant_text,
function_id,
grant_select_flag,
grant_delete_flag,
grant_update_flag,
grant_insert_flag,
locked_flag
FROM igs_sc_grants
WHERE grant_id = p_grant_id;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant';
||','||' Grant Name: '||p_grant_name||','||' Grant Text: '||p_grant_text||','||'Select Flag: '||p_grant_select_flag
||','||' Insert Flag: '||p_grant_insert_flag||','||' Update Flag: '||p_grant_update_flag||','||' Delete Flag: '||p_grant_delete_flag;
SAVEPOINT Update_Grant_SP;
l_grant_select_flag,
l_grant_delete_flag,
l_grant_update_flag,
l_grant_insert_flag,
l_locked_flag;
Get_Valid_Grant_Vals (p_grant_select_flag => p_grant_select_flag,
p_grant_insert_flag => p_grant_insert_flag,
p_grant_delete_flag => p_grant_delete_flag,
p_grant_update_flag => p_grant_update_flag,
x_grant_select_flag => l_grant_select_flag,
x_grant_insert_flag => l_grant_insert_flag,
x_grant_delete_flag => l_grant_delete_flag,
x_grant_update_flag => l_grant_update_flag
);
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_update_oper);
UPDATE igs_sc_grants grts
SET grts.grant_name = p_grant_name,
grts.grant_text = p_grant_text,
grts.function_id = l_function_id,
grts.user_group_id = p_user_group_id,
grts.grant_select_flag = l_grant_select_flag,
grts.grant_delete_flag = l_grant_delete_flag,
grts.grant_update_flag = l_grant_update_flag,
grts.grant_insert_flag = l_grant_insert_flag,
grts.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
grts.last_update_date = SYSDATE,
grts.last_update_login = NVL(FND_GLOBAL.login_id, -1)
WHERE grts.grant_id = p_grant_id;
ROLLBACK TO Update_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant.Ex_error';
ROLLBACK TO Update_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant.Ex_un';
ROLLBACK TO Update_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant.Ex_others';
END Update_Grant;
the updates on the igs_sc_grant_conds table.
There are validations to ensure that the IDs
provided are valid.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
mmkumar Jun 28, 2005 Removed code for opening cursor c_get_grant_cond
prbhardw Jul 18, 2005 Modified code to update condition number
******************************************************************/
PROCEDURE Update_Grant_Cond (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN igs_sc_grant_conds.grant_id%TYPE,
p_obj_attrib_id IN igs_sc_grant_conds.obj_attrib_id%TYPE,
p_user_attrib_id IN igs_sc_grant_conds.user_attrib_id%TYPE,
p_condition IN igs_sc_grant_conds.condition%TYPE,
p_text_value IN igs_sc_grant_conds.text_value%TYPE,
p_grant_cond_num IN igs_sc_grant_conds.grant_cond_num%TYPE,
p_old_grant_cond_num IN igs_sc_grant_conds.grant_cond_num%TYPE DEFAULT 0,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Grant_Cond';
l_update_oper CONSTANT VARCHAR2(30) := 'UPDATE';
SELECT cnds.obj_attrib_id,
cnds.user_attrib_id
FROM igs_sc_grant_conds cnds
WHERE cnds.grant_id = p_grant_id
AND cnds.grant_cond_num = p_grant_cond_num;
SELECT grt.grant_name
FROM igs_sc_grants grt
WHERE grt.grant_id = p_grant_id;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant_Cond';
SAVEPOINT Update_Grant_Cond_SP;
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_update_oper);
UPDATE igs_sc_grant_conds cnds
SET cnds.obj_attrib_id = l_obj_attrib_id,
cnds.user_attrib_id = l_user_attrib_id,
cnds.condition = p_condition,
cnds.text_value = p_text_value,
cnds.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
cnds.last_update_date = SYSDATE,
cnds.last_update_login = NVL(FND_GLOBAL.login_id, -1),
cnds.grant_cond_num = p_grant_cond_num
WHERE cnds.grant_id = p_grant_id
AND cnds.grant_cond_num = l_old_grant_cond_num;
ROLLBACK TO Update_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant_Cond.Ex_un';
ROLLBACK TO Update_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant_Cond.Ex_error';
ROLLBACK TO Update_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Update_Grant_Cond.Ex_others';
END Update_Grant_Cond;
that the appropriate values are updated.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Update_Object_Group (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_obj_group_id IN igs_sc_obj_groups.obj_group_id%TYPE,
p_obj_group_name IN igs_sc_obj_groups.obj_group_name%TYPE ,
p_default_policy_type IN igs_sc_obj_groups.default_policy_type%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Object_Group';
l_update_oper CONSTANT VARCHAR2(30) := 'UPDATE';
SELECT grps.obj_group_name,
grps.default_policy_type
FROM igs_sc_obj_groups grps
WHERE grps.obj_group_id = p_obj_group_id;
SELECT grt.grant_name
FROM igs_sc_grants grt
WHERE grt.grant_id = l_grant_id;
SELECT grt.grant_id,
grt.grant_name
FROM igs_sc_grants grt
WHERE grt.obj_group_id = p_obj_group_id;
SAVEPOINT Update_Object_Group_SP;
UPDATE igs_sc_obj_groups objs
SET objs.default_policy_type = l_default_policy_type,
objs.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
objs.last_update_date = SYSDATE,
objs.last_update_login = NVL(FND_GLOBAL.login_id, -1)
WHERE objs.obj_group_id = p_obj_group_id;
UPDATE igs_sc_obj_groups objs
SET objs.obj_group_name = l_obj_group_name,
objs.default_policy_type = l_default_policy_type,
objs.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
objs.last_update_date = SYSDATE,
objs.last_update_login = NVL(FND_GLOBAL.login_id, -1)
WHERE objs.obj_group_id = p_obj_group_id;
ROLLBACK TO Update_Object_Group_SP;
ROLLBACK TO Update_Object_Group_SP;
ROLLBACK TO Update_Object_Group_SP;
END Update_Object_Group;
the updates made to igs_sc_obj_att_mths is perform
with the proper validation. The IDs are validated
to ensure they exist in the OSS data model.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Update_Object_Attr_Method (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_object_id IN igs_sc_obj_att_mths.object_id%TYPE,
p_obj_attrib_id IN igs_sc_obj_att_mths.obj_attrib_id%TYPE,
p_obj_attrib_type IN igs_sc_obj_att_mths.obj_attrib_type%TYPE,
p_static_type IN igs_sc_obj_att_mths.static_type%TYPE,
p_select_text IN igs_sc_obj_att_mths.select_text%TYPE,
p_null_allow_flag IN VARCHAR2 DEFAULT 'N',
p_call_from_lct IN VARCHAR2 DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Object_Attr_Method';
l_update_oper CONSTANT VARCHAR2(30) := 'UPDATE';
l_select_text igs_sc_obj_att_mths.select_text%TYPE;
SELECT mthd.obj_attrib_type,
mthd.static_type,
mthd.select_text
FROM igs_sc_obj_att_mths mthd
WHERE mthd.object_id = p_object_id
AND mthd.obj_attrib_id = p_obj_attrib_id;
SELECT cond.grant_id,
grt.grant_name
FROM igs_sc_grant_conds cond,
igs_sc_grants grt
WHERE cond.obj_attrib_id = p_obj_attrib_id
AND grt.grant_id = cond.grant_id;
SAVEPOINT Update_Object_Attr_Method_SP;
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_update_oper);
l_select_text;
Insert_Object_Attr_Method (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_object_id => p_object_id,
p_obj_attrib_id => p_obj_attrib_id,
p_obj_attrib_type => p_obj_attrib_type,
p_static_type => p_static_type,
p_select_text => p_select_text,
p_null_allow_flag=> p_null_allow_flag,
x_return_status => x_return_status,
x_return_message => x_return_message );
IF ((p_select_text is NOT NULL) ) THEN
l_select_text := p_select_text;
IF NOT check_attrib_text ( l_object_name, l_select_text,l_obj_attrib_type) THEN
FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_GRNT_TXT_ERR');
FND_MESSAGE.SET_TOKEN('GRNT_TEXT', p_select_text);
UPDATE igs_sc_obj_att_mths mthd
SET mthd.obj_attrib_type = l_obj_attrib_type,
mthd.static_type = l_static_type,
mthd.select_text = l_select_text,
mthd.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
mthd.last_update_date = SYSDATE,
mthd.last_update_login = NVL(FND_GLOBAL.login_id, -1),
mthd.null_allow_flag = p_null_allow_flag
WHERE mthd.object_id = p_object_id
AND mthd.obj_attrib_id = p_obj_attrib_id;
ROLLBACK TO Update_Object_Attr_Method_SP;
ROLLBACK TO Update_Object_Attr_Method_SP;
ROLLBACK TO Update_Object_Attr_Method_SP;
END Update_Object_Attr_Method;
Purpose : This procedure is designed for the updates
to be made to the table igs_sc_obj_functns.
There are validations performed on the IDs that
are provided to ensure they exist in the OSS
data model.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Update_Object_Func (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_function_id IN igs_sc_obj_functns.function_id%TYPE,
p_obj_group_id IN igs_sc_obj_functns.obj_group_id%TYPE,
p_function_name IN igs_sc_obj_functns.function_name%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Object_Func';
l_update_oper CONSTANT VARCHAR2(30) := 'UPDATE';
SELECT fnc.obj_group_id,
fnc.function_name
FROM igs_sc_obj_functns fnc
WHERE fnc.function_id = p_function_id;
SELECT grt.grant_id,
grt.grant_name
FROM igs_sc_grants grt
WHERE grt.function_id = p_function_id;
SAVEPOINT Update_Object_Func_SP;
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_update_oper);
UPDATE igs_sc_obj_functns fnct
SET fnct.function_name = p_function_name,
fnct.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
fnct.last_update_date = SYSDATE,
fnct.last_update_login = NVL(FND_GLOBAL.login_id, -1)
WHERE fnct.function_id = p_function_id;
ROLLBACK TO Update_Object_Func_SP;
ROLLBACK TO Update_Object_Func_SP;
ROLLBACK TO Update_Object_Func_SP;
END Update_Object_Func;
Purpose : The purpose of this procedure is for the updates
to be made to data present in igs_sc_obj_attribs.
The IDs that are provided are validated to ensure
data present in the OSS data model.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Update_Object_Attr (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_obj_attrib_id IN igs_sc_obj_attribs.obj_attrib_id%TYPE,
p_obj_group_id IN igs_sc_obj_attribs.obj_group_id%TYPE,
p_obj_attrib_name IN igs_sc_obj_attribs.obj_attrib_name%TYPE,
p_active_flag IN VARCHAR2 DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_Object_Attr';
l_update_oper CONSTANT VARCHAR2(30) := 'UPDATE';
SELECT attr.obj_group_id,
attr.obj_attrib_name
FROM igs_sc_obj_attribs attr
WHERE attr.obj_attrib_id = p_obj_attrib_id;
SELECT cond.grant_id,
grt.grant_name
FROM igs_sc_grant_conds cond,
igs_sc_grants grt
WHERE cond.obj_attrib_id = p_obj_attrib_id
AND grt.grant_id = cond.grant_id;
SAVEPOINT Update_Object_Attr_SP;
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_update_oper);
UPDATE igs_sc_obj_attribs attr
SET attr.obj_attrib_name = p_obj_attrib_name,
attr.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
attr.last_update_date = SYSDATE,
attr.last_update_login = NVL(FND_GLOBAL.login_id, -1),
attr.active_flag = p_active_flag
WHERE attr.obj_attrib_id = p_obj_attrib_id;
ROLLBACK TO Update_Object_Attr_SP;
ROLLBACK TO Update_Object_Attr_SP;
ROLLBACK TO Update_Object_Attr_SP;
END Update_Object_Attr;
Purpose : The purpose of this procedure is for the updates
that are required to the table igs_sc_usr_attribs.
There is validation on the types and the IDs to
ensure data is in OSS data model and for integrity.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Update_User_Attr (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_user_attrib_id IN igs_sc_usr_attribs.user_attrib_id%TYPE,
p_user_attrib_name IN igs_sc_usr_attribs.user_attrib_name%TYPE,
p_user_attrib_type IN igs_sc_usr_attribs.user_attrib_type%TYPE,
p_static_type IN igs_sc_usr_attribs.static_type%TYPE,
p_select_text IN igs_sc_usr_attribs.select_text%TYPE,
p_active_flag IN igs_sc_usr_attribs.active_flag%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Update_User_Attr';
l_update_oper CONSTANT VARCHAR2(30) := 'UPDATE';
l_select_text igs_sc_usr_attribs.select_text%TYPE;
SELECT attr.user_attrib_name,
attr.user_attrib_type,
attr.static_type,
attr.select_text
FROM igs_sc_usr_attribs attr
WHERE attr.user_attrib_id = p_user_attrib_id;
SELECT cond.grant_id,
grt.grant_name
FROM igs_sc_grant_conds cond,
igs_sc_grants grt
WHERE cond.user_attrib_id = p_user_attrib_id
AND grt.grant_id = cond.grant_id;
SAVEPOINT Update_User_Attr_SP;
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_update_oper);
l_select_text;
IF ((p_select_text IS NOT NULL) ) THEN
l_select_text := p_select_text;
IF NOT check_attrib_text ( 'DUAL', l_select_text,l_user_attrib_type) THEN
FND_MESSAGE.SET_NAME('IGS', 'IGS_SC_GRNT_TXT_ERR');
FND_MESSAGE.SET_TOKEN('GRNT_TEXT', l_select_text);
UPDATE igs_sc_usr_attribs attr
SET attr.user_attrib_name = l_user_attrib_name,
attr.user_attrib_type = l_user_attrib_type,
attr.static_type = l_static_type,
attr.select_text = l_select_text,
attr.last_updated_by = NVL(FND_GLOBAL.user_id,-1),
attr.last_update_date = SYSDATE,
attr.last_update_login = NVL(FND_GLOBAL.login_id, -1),
attr.active_flag = p_active_flag
WHERE attr.user_attrib_id = p_user_attrib_id;
Delete_User_Attr_Val (p_api_version => l_api_version,
p_user_attrib_id => p_user_attrib_id,
p_user_id => NULL,
x_return_status => l_return_status,
x_return_message => l_return_message
);
ROLLBACK TO Update_User_Attr_SP;
ROLLBACK TO Update_User_Attr_SP;
ROLLBACK TO Update_User_Attr_SP;
END Update_User_Attr;
PROCEDURE Delete_Object_Group (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_obj_group_id IN igs_sc_obj_groups.obj_group_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Object_Group';
SELECT objs.object_id
FROM igs_sc_objects objs
WHERE objs.obj_group_id = p_obj_group_id;
SELECT funcs.function_id
FROM igs_sc_obj_functns funcs
WHERE funcs.obj_group_id = p_obj_group_id;
SELECT grts.grant_id
FROM igs_sc_grants grts
WHERE grts.obj_group_id = p_obj_group_id;
SELECT attrs.obj_attrib_id
FROM igs_sc_obj_attribs attrs
WHERE attrs.obj_group_id = p_obj_group_id;
SAVEPOINT Delete_Object_Group_SP;
Delete_Grant (p_api_version => l_api_version,
p_grant_id => l_grant_id,
x_return_status => l_return_status,
x_return_message => l_return_message
);
Delete_Object_Attr (p_api_version => l_api_version,
p_obj_attrib_id => l_obj_attrib_id,
x_return_status => l_return_status,
x_return_message => l_return_message
);
Delete_Object_Func (p_api_version => l_api_version,
p_function_id => l_function_id,
x_return_status => l_return_status,
x_return_message => l_return_message
);
Delete_Object (p_api_version => l_api_version,
p_obj_group_id => p_obj_group_id,
p_object_id => l_object_id,
x_return_status => l_return_status,
x_return_message => l_return_message
);
DELETE
FROM igs_sc_obj_groups grps
WHERE grps.obj_group_id = p_obj_group_id;
ROLLBACK TO Delete_Object_Group_SP;
ROLLBACK TO Delete_Object_Group_SP;
ROLLBACK TO Delete_Object_Group_SP;
END Delete_Object_Group;
PROCEDURE Delete_Object_Attr (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_obj_attrib_id IN igs_sc_obj_attribs.obj_attrib_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Object_Attr';
SELECT objs.object_id
FROM igs_sc_obj_att_mths objs
WHERE objs.obj_attrib_id = p_obj_attrib_id;
SELECT attrs.object_id
FROM igs_sc_obj_att_vals attrs
WHERE attrs.obj_attrib_id = p_obj_attrib_id;
SAVEPOINT Delete_Object_Attr_SP;
Delete_Object_Attr_Val (p_api_version => l_api_version,
p_obj_attrib_id => p_obj_attrib_id,
p_object_id => l_object_id_val,
x_return_status => l_return_status,
x_return_message => l_return_message
);
Delete_Object_Attr_Method (p_api_version => l_api_version,
p_object_id => l_object_id_mthd,
p_obj_attrib_id => p_obj_attrib_id,
x_return_status => l_return_status,
x_return_message => l_return_message
);
DELETE
FROM igs_sc_obj_attribs attrs
WHERE attrs.obj_attrib_id = p_obj_attrib_id;
ROLLBACK TO Delete_Object_Attr_SP;
ROLLBACK TO Delete_Object_Attr_SP;
ROLLBACK TO Delete_Object_Attr_SP;
END Delete_Object_Attr;
PROCEDURE Delete_Object_Attr_Method (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_object_id IN igs_sc_obj_att_mths.object_id%TYPE,
p_obj_attrib_id IN igs_sc_obj_att_mths.obj_attrib_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Object_Attr_Method';
SAVEPOINT Delete_Object_Attr_Method_SP;
DELETE
FROM igs_sc_obj_att_mths mtds
WHERE mtds.object_id = p_object_id
AND mtds.obj_attrib_id = p_obj_attrib_id;
ROLLBACK TO Delete_Object_Attr_Method_SP;
ROLLBACK TO Delete_Object_Attr_Method_SP;
ROLLBACK TO Delete_Object_Attr_Method_SP;
END Delete_Object_Attr_Method;
PROCEDURE Delete_Object_Func (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_function_id IN igs_sc_obj_functns.function_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Object_Func';
SELECT grt.grant_id
FROM igs_sc_grants grt,
igs_sc_obj_functns fnc
WHERE fnc.function_id = p_function_id
AND grt.function_id = fnc.function_id
AND grt.obj_group_id = fnc.obj_group_id;
SAVEPOINT Delete_Object_Func_SP;
Delete_Grant (p_api_version => l_api_version,
p_grant_id => l_grant_id,
x_return_status => l_return_status,
x_return_message => l_return_message
);
DELETE
FROM igs_sc_obj_functns fctn
WHERE fctn.function_id = p_function_id;
ROLLBACK TO Delete_Object_Func_SP;
ROLLBACK TO Delete_Object_Func_SP;
ROLLBACK TO Delete_Object_Func_SP;
END Delete_Object_Func;
PROCEDURE Delete_Object_Attr_Val (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_object_id IN igs_sc_obj_att_mths.object_id%TYPE,
p_obj_attrib_id IN igs_sc_obj_att_mths.obj_attrib_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Object_Attr_Val';
SAVEPOINT Delete_Object_Attr_Val_SP;
DELETE
FROM igs_sc_obj_att_vals oval
WHERE oval.obj_attrib_id = p_obj_attrib_id
AND oval.object_id = p_object_id;
ROLLBACK TO Delete_Object_Attr_Val_SP;
ROLLBACK TO Delete_Object_Attr_Val_SP;
ROLLBACK TO Delete_Object_Attr_Val_SP;
END Delete_Object_Attr_Val;
PROCEDURE Delete_Object (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_obj_group_id IN igs_sc_objects.obj_group_id%TYPE,
p_object_id IN igs_sc_objects.object_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Object';
SELECT database_object_name
FROM fnd_objects
WHERE object_id = p_object_id;
SAVEPOINT Delete_Object_SP;
DELETE
FROM igs_sc_objects objs
WHERE objs.obj_group_id = p_obj_group_id
AND objs.object_id = p_object_id;
ROLLBACK TO Delete_Object_SP;
ROLLBACK TO Delete_Object_SP;
ROLLBACK TO Delete_Object_SP;
END Delete_Object;
PROCEDURE Delete_User_Attr (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_user_attrib_id IN igs_sc_usr_attribs.user_attrib_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_User_Attr';
SELECT grts.grant_id,
grts.grant_cond_num,
gnt.grant_name
FROM igs_sc_grant_conds grts,
igs_sc_grants gnt
WHERE grts.user_attrib_id = p_user_attrib_id
AND gnt.grant_id = grts.grant_id;
SELECT uval.user_id
FROM igs_sc_usr_att_vals uval
WHERE uval.user_attrib_id = p_user_attrib_id;
SAVEPOINT Delete_User_Attr_SP;
Delete_User_Attr_Val (p_api_version => l_api_version,
p_user_attrib_id => p_user_attrib_id,
p_user_id => NULL,
x_return_status => l_return_status,
x_return_message => l_return_message
);
DELETE
FROM igs_sc_usr_attribs attr
WHERE attr.user_attrib_id = p_user_attrib_id;
ROLLBACK TO Delete_User_Attr_SP;
ROLLBACK TO Delete_User_Attr_SP;
ROLLBACK TO Delete_User_Attr_SP;
END Delete_User_Attr;
model. There are no child tables to be updated
due to the removal of the value.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Delete_User_Attr_Val (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_user_attrib_id IN igs_sc_usr_attribs.user_attrib_id%TYPE,
p_user_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_User_Attr_Val';
SAVEPOINT Delete_User_Attr_Val_SP;
DELETE
FROM igs_sc_usr_att_vals atvl
WHERE ( atvl.user_id = p_user_id OR p_user_id IS NULL )
AND atvl.user_attrib_id = p_user_attrib_id;
ROLLBACK TO Delete_User_Attr_Val_SP;
ROLLBACK TO Delete_User_Attr_Val_SP;
ROLLBACK TO Delete_User_Attr_Val_SP;
END Delete_User_Attr_Val;
PROCEDURE Delete_Grant (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN igs_sc_grants.grant_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Grant';
l_delete_oper CONSTANT VARCHAR2(30) := 'DELETE';
SELECT grts.grant_cond_num
FROM igs_sc_grant_conds grts
WHERE grts.grant_id = p_grant_id;
SELECT grts.grant_name
FROM igs_sc_grants grts
WHERE grts.grant_id = p_grant_id;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant';
SAVEPOINT Delete_Grant_SP;
Delete_Grant_Cond (p_api_version => l_api_version,
p_grant_id => p_grant_id,
p_grant_cond_num => l_grant_cond_num,
x_return_status => l_return_status,
x_return_message => l_return_message
);
DELETE
FROM igs_sc_grants grts
WHERE grts.grant_id = p_grant_id;
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_delete_oper);
ROLLBACK TO Delete_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant.Ex_error';
ROLLBACK TO Delete_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant.Ex_un';
ROLLBACK TO Delete_Grant_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant.Ex_others';
END Delete_Grant;
PROCEDURE Delete_Grant_Cond (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN igs_sc_grant_conds.grant_id%TYPE,
p_grant_cond_num IN igs_sc_grant_conds.grant_cond_num%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Grant_Cond';
l_delete_oper CONSTANT VARCHAR2(30) := 'DELETE';
SELECT grts.grant_name
FROM igs_sc_grants grts
WHERE grts.grant_id = p_grant_id;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant_Cond';
SAVEPOINT Delete_Grant_Cond_SP;
DELETE
FROM igs_sc_grant_conds conds
WHERE conds.grant_id = p_grant_id
AND conds.grant_cond_num = p_grant_cond_num;
FND_MESSAGE.SET_TOKEN('OPS_TYPE', l_delete_oper);
ROLLBACK TO Delete_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant_Cond.Ex_un';
ROLLBACK TO Delete_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant_Cond.Ex_error';
ROLLBACK TO Delete_Grant_Cond_SP;
l_label := 'igs.plsql.igs_sc_data_sec_apis_pkg.Delete_Grant_Cond.Ex_others';
END Delete_Grant_Cond;
PROCEDURE Delete_Local_Role (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Local_Role';
SAVEPOINT Delete_Local_Role_SP;
ROLLBACK TO Delete_Local_Role_SP;
ROLLBACK TO Delete_Local_Role_SP;
ROLLBACK TO Delete_Local_Role_SP;
END Delete_Local_Role;
PROCEDURE Delete_Local_User_Role (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Local_User_Role';
SAVEPOINT Delete_Local_User_Role_SP;
ROLLBACK TO Delete_Local_User_Role_SP;
ROLLBACK TO Delete_Local_User_Role_SP;
ROLLBACK TO Delete_Local_User_Role_SP;
END Delete_Local_User_Role;
SELECT grnt.locked_flag
FROM igs_sc_grants grnt
WHERE grnt.grant_id = p_grant_id;
grant so that it can not be deleted and start
the use of the grant into the system.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Lock_Grant (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN igs_sc_grants.grant_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Lock_Grant';
SELECT grts.grant_id
FROM igs_sc_grants grts
WHERE grts.grant_id = p_grant_id;
UPDATE igs_sc_grants grts
SET grts.locked_flag = 'Y'
WHERE grts.grant_id = p_grant_id;
grant so that it can be deleted.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Unlock_Grant (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_grant_id IN igs_sc_grants.grant_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Unlock_Grant';
SELECT grts.grant_id
FROM igs_sc_grants grts
WHERE grts.grant_id = p_grant_id;
DELETE
FROM igs_sc_obj_grants objs
WHERE objs.grant_id = p_grant_id;
UPDATE igs_sc_grants grts
SET grts.locked_flag = l_locked_flag_value
WHERE grts.grant_id = p_grant_id;
the values that are being inserted or updated
are either Y or N.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
PROCEDURE Get_Valid_Grant_Vals (p_grant_select_flag IN VARCHAR2,
p_grant_insert_flag IN VARCHAR2,
p_grant_delete_flag IN VARCHAR2,
p_grant_update_flag IN VARCHAR2,
x_grant_select_flag OUT NOCOPY VARCHAR2,
x_grant_insert_flag OUT NOCOPY VARCHAR2,
x_grant_delete_flag OUT NOCOPY VARCHAR2,
x_grant_update_flag OUT NOCOPY VARCHAR2
) IS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Get_Valid_Grant_Vals';
IF ((UPPER(p_grant_select_flag) = 'Y') OR (UPPER(p_grant_select_flag) = 'N')) THEN
x_grant_select_flag := UPPER(p_grant_select_flag);
x_grant_select_flag := 'N';
IF ((UPPER(p_grant_update_flag) = 'Y') OR (UPPER(p_grant_select_flag) = 'N')) THEN
x_grant_update_flag := UPPER(p_grant_update_flag);
x_grant_update_flag := 'N';
IF ((UPPER(p_grant_delete_flag) = 'Y') OR (UPPER(p_grant_delete_flag) = 'N')) THEN
x_grant_delete_flag := UPPER(p_grant_delete_flag);
x_grant_delete_flag := 'N';
IF ((UPPER(p_grant_insert_flag) = 'Y') OR (UPPER(p_grant_insert_flag) = 'N')) THEN
x_grant_insert_flag := UPPER(p_grant_insert_flag);
x_grant_insert_flag := 'N';
for insert or update is available in the system.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_Function_ID (
p_function_id IN NUMBER
) RETURN NUMBER AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Function_ID';
SELECT fnc.function_id
FROM igs_sc_obj_functns fnc
WHERE fnc.function_id = p_function_id;
before insert or update of records.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_Obj_Grp_ID (
p_group_id IN NUMBER
) RETURN NUMBER AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Obj_Grp_ID';
SELECT grps.obj_group_id
FROM igs_sc_obj_groups grps
WHERE grps.obj_group_id = p_group_id;
the object attribute being inserted or updated
is already present in the system.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_Obj_Attr_ID (
p_obj_attr_id IN NUMBER
) RETURN NUMBER AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Obj_Attr_ID';
SELECT attr.obj_attrib_id
FROM igs_sc_obj_attribs attr
WHERE attr.obj_attrib_id = p_obj_attr_id;
to insert or updated. The user group ID is
associated to the wf_local_roles record being present.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_User_Grp_ID (
p_user_group_id IN NUMBER
) RETURN NUMBER AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_User_Grp_ID';
SELECT wflr.orig_system_id
FROM wf_local_roles wflr
WHERE wflr.ORIG_SYSTEM = 'IGS'
AND wflr.orig_system_id = p_user_group_id;
the grant ID that is to be inserted or updated
is present in the system.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_Grant_ID (
p_grant_id IN NUMBER
) RETURN NUMBER AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Grant_ID';
SELECT grt.grant_id
FROM igs_sc_grants grt
WHERE grt.grant_id = p_grant_id;
the object that is being inserted or updated
is present in the system.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_Object_ID (
p_object_id IN NUMBER
) RETURN NUMBER AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Object_ID';
SELECT objs.object_id
FROM fnd_objects objs
WHERE objs.object_id = p_object_id;
the user attribute being inserted or updated is
present in the system.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_User_Attr_ID (
p_user_attr_id IN NUMBER
) RETURN NUMBER AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_User_Attr_ID';
SELECT attr.user_attrib_id
FROM igs_sc_usr_attribs attr
WHERE attr.user_attrib_id = p_user_attr_id;
the value being inserted or updated for the static
type falls into the values that are expected.
Remarks :
Change History
Who When What
------------------------------------------------------------------------
Don Shellito April 23, 2003 New Procedure Created.
******************************************************************/
FUNCTION Validate_Static_Type (
p_static_type IN VARCHAR2
) RETURN VARCHAR2 AS
-- -----------------------------------------------------------------
-- Define local variables to be used.
-- -----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Static_Type';
L_SELECT_FLAG VARCHAR2(1) := NULL;
L_UPDATE_FLAG VARCHAR2(1) := NULL;
L_DELETE_FLAG VARCHAR2(1):= NULL;
L_INSERT_FLAG VARCHAR2(1):= NULL;
SELECT POLICY_NAME,
OBJECT_OWNER
FROM DBA_POLICIES
WHERE object_name = p_database_object_name
AND object_owner = l_owner;
SELECT DECODE(SELECT_FLAG, 'Y','S',NULL) SELECT_FLAG,
DECODE(UPDATE_FLAG, 'Y','U',NULL) UPDATE_FLAG,
DECODE(DELETE_FLAG, 'Y','D',NULL) DELETE_FLAG,
DECODE(INSERT_FLAG, 'Y','I',NULL) INSERT_FLAG,
DECODE(ENFORCE_PAR_SEC_FLAG,'Y','Y',NULL) ENFORCE_PAR_SEC_FLAG
FROM IGS_SC_OBJECTS SC,
FND_OBJECTS FND
WHERE FND.OBJ_NAME = p_database_object_name
AND SC.OBJECT_ID = FND.OBJECT_ID
AND SC.ACTIVE_FLAG = 'Y';
SELECT SUBSTR(version,1,INSTR(version,'.')-1)
FROM v$instance;
FETCH C_DEFAULT_SECURITY INTO L_SELECT_FLAG,L_UPDATE_FLAG,L_DELETE_FLAG,L_INSERT_FLAG,L_PAR_SECURITY;
IF L_INSERT_FLAG IS NOT NULL THEN
IF L_PAR_SECURITY IS NOT NULL THEN
IF (l_db_version >= 10) THEN
l_stmt:= 'BEGIN dbms_rls.add_policy (
object_schema => :1,
object_name => :2,
policy_name => :3,
function_schema => :4,
policy_function => :5,
statement_types => :6,
update_check => TRUE,
long_predicate => TRUE); END;';
l_apps_schema,'IGS_SC_GRANTS_TBL_PVT.UPDATE_ROW','insert';
policy_function => 'IGS_SC_GRANTS_TBL_PVT.UPDATE_ROW',
statement_types => 'insert',
update_check => TRUE );
update_check => TRUE,
long_predicate => TRUE); END;';
l_apps_schema,'IGS_SC_GRANTS_TBL_PVT.INSERT_ROW','insert';
policy_function => 'IGS_SC_GRANTS_TBL_PVT.INSERT_ROW',
statement_types => 'insert',
update_check => TRUE );
IF L_UPDATE_FLAG IS NOT NULL THEN
IF (l_db_version >= 10) THEN
l_stmt:= 'BEGIN dbms_rls.add_policy (
object_schema => :1,
object_name => :2,
policy_name => :3,
function_schema => :4,
policy_function => :5,
statement_types => :6,
update_check => TRUE,
long_predicate => TRUE); END;';
l_apps_schema,'IGS_SC_GRANTS_TBL_PVT.UPDATE_ROW','update';
policy_function => 'IGS_SC_GRANTS_TBL_PVT.UPDATE_ROW',
statement_types => 'update',
update_check => TRUE );
IF L_SELECT_FLAG IS NOT NULL THEN
IF (l_db_version >= 10) THEN
l_stmt:= 'BEGIN dbms_rls.add_policy (
object_schema => :1,
object_name => :2,
policy_name => :3,
function_schema => :4,
policy_function => :5,
statement_types => :6,
update_check => TRUE,
long_predicate => TRUE); END;';
l_apps_schema,'IGS_SC_GRANTS_TBL_PVT.SELECT_ROW','select';
policy_function => 'IGS_SC_GRANTS_TBL_PVT.SELECT_ROW',
statement_types => 'select',
update_check => TRUE );
IF L_DELETE_FLAG IS NOT NULL THEN
IF L_PAR_SECURITY IS NOT NULL THEN
IF (l_db_version >= 10) THEN
l_stmt:= 'BEGIN dbms_rls.add_policy (
object_schema => :1,
object_name => :2,
policy_name => :3,
function_schema => :4,
policy_function => :5,
statement_types => :6,
update_check => TRUE,
long_predicate => TRUE); END;';
l_apps_schema,'IGS_SC_GRANTS_TBL_PVT.UPDATE_ROW','delete';
policy_function => 'IGS_SC_GRANTS_TBL_PVT.UPDATE_ROW',
statement_types => 'delete',
update_check => TRUE );
update_check => TRUE,
long_predicate => TRUE); END;';
l_apps_schema,'IGS_SC_GRANTS_TBL_PVT.DELETE_ROW','delete';
policy_function => 'IGS_SC_GRANTS_TBL_PVT.DELETE_ROW',
statement_types => 'delete',
update_check => TRUE );
SELECT grant_id
FROM igs_sc_grants
ORDER BY grant_id
FOR UPDATE OF grant_id;
SELECT user_attrib_id
FROM igs_sc_usr_attribs
ORDER BY user_attrib_id
FOR UPDATE OF user_attrib_id;
SELECT obj_group_id
FROM igs_sc_obj_groups
ORDER BY obj_group_id
FOR UPDATE OF obj_group_id;
SELECT function_id
FROM igs_sc_obj_functns
ORDER BY function_id
FOR UPDATE OF function_id;
SELECT obj_attrib_id
FROM igs_sc_obj_attribs
ORDER BY obj_attrib_id
FOR UPDATE OF obj_attrib_id;
UPDATE IGS_SC_OBJ_GRANTS SET grant_id = l_current_seq WHERE grant_id = igs_sc_grants_rec.grant_id;
UPDATE IGS_SC_GRANT_CONDS SET grant_id = l_current_seq WHERE grant_id = igs_sc_grants_rec.grant_id;
UPDATE igs_sc_grants SET grant_id = l_current_seq WHERE CURRENT OF igs_sc_grants_c;
UPDATE IGS_SC_GRANT_CONDS SET user_attrib_id = l_current_seq WHERE user_attrib_id = igs_sc_usr_attribs_rec.user_attrib_id;
UPDATE IGS_SC_USR_ATT_VALS SET user_attrib_id = l_current_seq WHERE user_attrib_id = igs_sc_usr_attribs_rec.user_attrib_id;
UPDATE igs_sc_usr_attribs SET user_attrib_id = l_current_seq WHERE CURRENT OF igs_sc_usr_attribs_c;
UPDATE IGS_SC_OBJECTS SET obj_group_id = l_current_seq WHERE obj_group_id = igs_sc_obj_groups_rec.obj_group_id;
UPDATE IGS_SC_OBJ_FUNCTNS SET obj_group_id = l_current_seq WHERE obj_group_id = igs_sc_obj_groups_rec.obj_group_id;
UPDATE IGS_SC_OBJ_ATTRIBS SET obj_group_id = l_current_seq WHERE obj_group_id = igs_sc_obj_groups_rec.obj_group_id;
UPDATE IGS_SC_GRANTS SET obj_group_id = l_current_seq WHERE obj_group_id = igs_sc_obj_groups_rec.obj_group_id;
UPDATE igs_sc_obj_groups SET obj_group_id = l_current_seq WHERE CURRENT OF igs_sc_obj_groups_c;
UPDATE IGS_SC_GRANTS SET function_id = l_current_seq WHERE function_id = igs_sc_obj_functns_rec.function_id;
UPDATE igs_sc_obj_functns SET function_id = l_current_seq WHERE CURRENT OF igs_sc_obj_functns_c;
UPDATE IGS_SC_OBJ_ATT_MTHS SET obj_attrib_id = l_current_seq WHERE obj_attrib_id = igs_sc_obj_attribs_rec.obj_attrib_id;
UPDATE IGS_SC_OBJ_ATT_VALS SET obj_attrib_id = l_current_seq WHERE obj_attrib_id = igs_sc_obj_attribs_rec.obj_attrib_id;
UPDATE IGS_SC_GRANT_CONDS SET obj_attrib_id = l_current_seq WHERE obj_attrib_id = igs_sc_obj_attribs_rec.obj_attrib_id;
UPDATE igs_sc_obj_attribs SET obj_attrib_id = l_current_seq WHERE CURRENT OF igs_sc_obj_attribs_c;
SELECT DATABASE_OBJECT_NAME
FROM FND_OBJECTS
WHERE OBJECT_ID = p_obj_id;
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME=p_table_name
AND OWNER = l_owner
ORDER BY COLUMN_NAME;
ELSIF c_cols_rec.column_name IN ('CREATION_DATE','START_DATE','LAST_UPDATE_DATE') THEN
l_concat_col_list := l_concat_col_list||',sysdate';
l_query_str := 'SELECT '||l_concat_col_list||' FROM '||p_table_name||' WHERE '||p_where_clause;
l_str := 'INSERT INTO '||p_table_name||' ('||l_col_list||') VALUES ( ';
SELECT DATABASE_OBJECT_NAME,
PK1_COLUMN_NAME ,
PK2_COLUMN_NAME ,
PK3_COLUMN_NAME ,
PK4_COLUMN_NAME ,
PK5_COLUMN_NAME ,
PK1_COLUMN_TYPE ,
PK2_COLUMN_TYPE ,
PK3_COLUMN_TYPE ,
PK4_COLUMN_TYPE ,
PK5_COLUMN_TYPE ,
APPLICATION_ID ,
OBJ_NAME
FROM FND_OBJECTS
WHERE OBJ_NAME LIKE 'IGS%'
ORDER BY OBJ_NAME;
UTL_FILE.PUT_LINE ( p_file_ptr,' SELECT object_id');
UTL_FILE.PUT_LINE ( p_file_ptr,' SELECT fnd_objects_s.nextval INTO l_object_id FROM DUAL;');
UTL_FILE.PUT_LINE ( p_file_ptr,' FND_OBJECTS_PKG.INSERT_ROW (');
UTL_FILE.PUT_LINE ( p_file_ptr,' x_last_update_date => sysdate,');
UTL_FILE.PUT_LINE ( p_file_ptr,' x_last_updated_by => nvl(fnd_global.user_id,-1),');
UTL_FILE.PUT_LINE ( p_file_ptr,' x_last_update_login => nvl(fnd_global.login_id, -1));');
UTL_FILE.PUT_LINE ( p_file_ptr,' FND_OBJECTS_PKG.UPDATE_ROW (');
UTL_FILE.PUT_LINE ( p_file_ptr,' x_last_update_date => sysdate,');
UTL_FILE.PUT_LINE ( p_file_ptr,' x_last_updated_by => nvl(fnd_global.user_id,-1),');
UTL_FILE.PUT_LINE ( p_file_ptr,' x_last_update_login => nvl(fnd_global.login_id, -1));');
UTL_FILE.PUT_LINE ( l_out_file_ptr, 'DELETE FROM '||ltrim(rtrim(l_line))||' WHERE '||replace_string(l_line2,':1','1')||';' );