[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fois.instance_set_id
FROM fnd_object_instance_sets fois,
fnd_objects fo
WHERE fo.obj_name = l_obj_name
AND fo.object_id = fois.object_id
AND fois.predicate LIKE '%''' || p_dss_group_code || '''%';
SELECT object_id
FROM fnd_objects
WHERE obj_name = p_obj_name;
SELECT fnd_object_instance_sets_s.NEXTVAL INTO l_instance_set_id FROM DUAL;
fnd_object_instance_sets_pkg.insert_row(
x_rowid => l_rowid,
x_instance_set_id => l_instance_set_id,
x_instance_set_name => 'HZ_DSS_BASE_' || l_instance_set_id,
x_object_id => l_object_id,
x_predicate => 'DSS_GROUP_CODE = ''' || p_dss_group_code ||'''',
x_display_name => 'HZ_DSS_BASE_' || l_instance_set_id,
x_description => 'HZ_DSS_BASE_' || l_instance_set_id,
x_creation_date => hz_utility_v2pub.creation_date,
x_created_by => hz_utility_v2pub.created_by,
x_last_update_date => hz_utility_v2pub.last_update_date,
x_last_updated_by => hz_utility_v2pub.last_updated_by,
x_last_update_login => hz_utility_v2pub.last_update_login
);
SELECT dse.dss_instance_set_id,
fo.obj_name,
dse.status
FROM hz_dss_secured_entities dse,
fnd_object_instance_sets fois,
fnd_objects fo
WHERE dse.dss_group_code = p_dss_group_code
AND fois.instance_set_id = dse.dss_instance_set_id
AND fo.object_id = fois.object_id ;
SELECT 1
INTO l_grantee_key_cnt
FROM fnd_user
WHERE user_name = l_fnd_grantee_key
AND (start_date IS NULL OR start_date < SYSDATE)
AND (end_date IS NULL OR end_date > SYSDATE)
AND ROWNUM = 1;
SELECT 1
INTO l_grantee_key_cnt
FROM wf_roles
WHERE name = l_fnd_grantee_key
AND orig_system LIKE 'FND_RESP%'
AND ROWNUM = 1;
SELECT grant_guid
FROM fnd_grants grants,
fnd_menus menu
WHERE grants.menu_id = menu.menu_id
AND menu.menu_name = 'HZ_DSS_'||p_data_operation_code
AND program_tag = p_dss_group_code
AND grantee_type = p_dss_grantee_type
AND (p_dss_grantee_type = 'GLOBAL' OR
p_dss_grantee_type <> 'GLOBAL' AND
grantee_key = p_dss_grantee_key);
p_insert_flag IN VARCHAR2,
p_update_flag IN VARCHAR2,
p_delete_flag IN VARCHAR2,
p_admin_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
G_PROCEDURE_NAME VARCHAR2(30) := 'CREATE_GRANT';
SELECT status
FROM hz_dss_groups_b
WHERE dss_group_code = p_dss_group_code;
p_data_operation_code => 'SELECT',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
IF p_insert_flag = 'Y' THEN
do_create_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'INSERT',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
IF p_update_flag = 'Y' THEN
do_create_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'UPDATE',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
IF p_delete_flag = 'Y' THEN
do_create_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'DELETE',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
SELECT dsg.status
FROM hz_dss_grants_v grants,
hz_dss_groups_b dsg
WHERE dsg.dss_group_code = p_dss_group_code
AND grants.dss_group_code = p_dss_group_code
AND ROWNUM = 1;
SELECT obj_name
FROM fnd_objects obj,
fnd_object_instance_sets ins
WHERE instance_set_id = p_dss_instance_set_id
AND ins.object_id = obj.object_id;
SELECT *
FROM hz_dss_grants_v
WHERE dss_group_code = p_dss_group_code;
l_menu_name := 'SELECT';
ELSIF i = 2 AND c_grants_rec.insert_flag = 'Y' THEN
l_menu_name := 'INSERT';
ELSIF i = 3 AND c_grants_rec.update_flag = 'Y' THEN
l_menu_name := 'UPDATE';
ELSIF i = 4 AND c_grants_rec.delete_flag = 'Y' THEN
l_menu_name := 'DELETE';
* PROCEDURE update_grant
*
* DESCRIPTION
*
* Updates a set of Grants against a Data Sharing Group.
* This signature matches the UI and corresponds to a "UI Grant Update".
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
*
* NOTES
*
* MODIFICATION HISTORY
*
* 09-03-2002 Chris Saulit o Created.
*
*/
PROCEDURE update_grant (
p_init_msg_list IN VARCHAR2,
p_dss_group_code IN VARCHAR2,
p_dss_grantee_type IN VARCHAR2,
p_dss_grantee_key IN VARCHAR2,
p_view_flag IN VARCHAR2,
p_insert_flag IN VARCHAR2,
p_update_flag IN VARCHAR2,
p_delete_flag IN VARCHAR2,
p_admin_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_grant IS
SELECT NVL(view_flag,'N'), NVL(insert_flag,'N'),
NVL(update_flag,'N'), NVL(delete_flag,'N'),
dsg.status
FROM hz_dss_grants_v grants, hz_dss_groups_b dsg
WHERE grants.dss_group_code = p_dss_group_code
AND dss_grantee_type = p_dss_grantee_type
AND (p_dss_grantee_type = 'GLOBAL' OR
p_dss_grantee_type <> 'GLOBAL' AND dss_grantee_key = p_dss_grantee_key)
AND dsg.dss_group_code = p_dss_group_code;
l_db_insert_flag VARCHAR2(1);
l_db_update_flag VARCHAR2(1);
l_db_delete_flag VARCHAR2(1);
SAVEPOINT update_grant;
l_db_view_flag, l_db_insert_flag,
l_db_update_flag, l_db_delete_flag,
l_dsg_status;
p_data_operation_code => 'SELECT',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
p_data_operation_code => 'SELECT',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key
);
IF NVL(p_insert_flag, 'N') = 'Y' AND NVL(l_db_insert_flag, 'N') = 'N' THEN
--
-- create grant
--
do_create_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'INSERT',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
ELSIF NVL(p_insert_flag, 'N') = 'N' AND NVL(l_db_insert_flag, 'N') = 'Y' THEN
--
-- revoke grant
--
do_revoke_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'INSERT',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key
);
IF NVL(p_update_flag, 'N') = 'Y' AND NVL(l_db_update_flag, 'N') = 'N' THEN
--
-- create grant
--
do_create_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'UPDATE',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
ELSIF NVL(p_update_flag, 'N') = 'N' AND NVL(l_db_update_flag, 'N') = 'Y' THEN
--
-- revoke grant
--
do_revoke_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'UPDATE',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key
);
IF NVL(p_delete_flag, 'N') = 'Y' AND NVL(l_db_delete_flag, 'N') = 'N' THEN
--
-- create grant
--
do_create_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'DELETE',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key,
p_grant_start_date => SYSDATE,
p_grant_end_date => l_end_date
);
ELSIF NVL(p_delete_flag, 'N') = 'N' AND NVL(l_db_delete_flag, 'N') = 'Y' THEN
--
-- revoke grant
--
do_revoke_fnd_grant (
p_dss_group_code => p_dss_group_code,
p_data_operation_code => 'DELETE',
p_dss_grantee_type => p_dss_grantee_type,
p_dss_grantee_key => p_dss_grantee_key
);
ROLLBACK TO update_grant ;
ROLLBACK TO update_grant ;
ROLLBACK TO update_grant ;
END update_grant;
* PROCEDURE update_grant
*
* DESCRIPTION
*
* Updates a set of Grants against a Data Sharing Group.
* This procedure is called when a whole DSS group is
* disabled/enabled.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
*
* NOTES
*
* MODIFICATION HISTORY
*
* 06-29-2004 Jianying Huang o Created.
*
*/
PROCEDURE update_grant (
p_init_msg_list IN VARCHAR2,
p_dss_group_code IN VARCHAR2,
p_dss_group_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_grants IS
SELECT grants.grant_guid,
grants.start_date,
dse.status
FROM fnd_grants grants,
fnd_object_instance_sets ins,
hz_dss_secured_entities dse
WHERE grants.program_name = G_API_NAME
AND grants.program_tag = p_dss_group_code
AND grants.instance_set_id = ins.instance_set_id
AND ins.instance_set_name NOT LIKE 'HZ_DSS_BASE_%'
AND ins.instance_set_id = dse.dss_instance_set_id;
SAVEPOINT update_grant;
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_fnd_grant_guid,
p_start_date => l_start_date,
p_end_date => l_end_date,
x_success => l_fnd_success
);
ROLLBACK TO update_grant ;
ROLLBACK TO update_grant ;
ROLLBACK TO update_grant ;
END update_grant;
* PROCEDURE update_grant
*
* DESCRIPTION
*
* Updates a set of Grants against a Data Sharing Group.
* This procedure is called when an entity inside a DSS group
* is disabled/enabled.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
*
* NOTES
*
* MODIFICATION HISTORY
*
* 06-29-2004 Jianying Huang o Created.
*
*/
PROCEDURE update_grant (
p_init_msg_list IN VARCHAR2,
p_dss_group_code IN VARCHAR2,
p_dss_instance_set_id IN NUMBER,
p_secured_entity_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR c_dss_groups IS
SELECT status
FROM hz_dss_groups_b
WHERE dss_group_code = p_dss_group_code;
SELECT grants.grant_guid,
grants.start_date
FROM fnd_grants grants
WHERE grants.program_name = G_API_NAME
AND grants.program_tag = p_dss_group_code
AND grants.instance_set_id = p_dss_instance_set_id;
SAVEPOINT update_grant;
fnd_grants_pkg.update_grant (
p_api_version => 1,
p_grant_guid => l_fnd_grant_guid,
p_start_date => l_start_date,
p_end_date => l_end_date,
x_success => l_fnd_success
);
ROLLBACK TO update_grant ;
ROLLBACK TO update_grant ;
ROLLBACK TO update_grant ;
END update_grant;
(g_dss_admin_create, g_dss_admin_update, g_dss_admin_grant)
THEN
FND_MESSAGE.SET_NAME('FND','FND_GENERIC_MESSAGE');
g_dss_admin_create ||', '|| g_dss_admin_update||', '||g_dss_admin_grant
); -- this is a developer error, not a user-facing error
SELECT responsibility_id INTO G_DSS_RESPONSIBILITY_ID
FROM fnd_responsibility r
WHERE r.responsibility_key = 'HZ_DSS_ADMIN'
AND r.application_id = 222;
SELECT 1
INTO l_resp_cnt
FROM fnd_user_resp_groups rg
WHERE rg.user_id = fnd_global.user_id
AND rg.responsibility_id = G_DSS_RESPONSIBILITY_ID
AND rg.responsibility_application_id = 222
AND (rg.end_date IS NULL OR rg.end_date > SYSDATE)
AND (rg.start_date IS NULL OR rg.start_date < SYSDATE);