The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT menu_name
INTO l_role_name
FROM fnd_menus
WHERE menu_id = p_role_id;
update FND_GRANTS
set parameter1 = p_pk3_value,
parameter2 = p_pk4_value
where grant_guid = l_grant_guid;
SELECT party_type
FROM hz_parties
WHERE party_id=cp_party_id;
SELECT object_id
INTO l_object_id
FROM fnd_objects
WHERE obj_name = p_object_name;
SELECT menu_name
INTO l_role_name
FROM fnd_menus
WHERE menu_id = p_role_id;
FND_GRANTS_PKG.delete_grant(
p_grantee_type => l_grantee_type,
p_grantee_key => l_grantee_key,
p_object_name => p_object_name,
p_instance_type => 'INSTANCE',
p_instance_set_id => NULL,
p_instance_pk1_value => p_pk1_value,
p_instance_pk2_value => p_pk2_value,
p_instance_pk3_value => NULL,
p_instance_pk4_value => NULL,
p_instance_pk5_value => NULL,
p_menu_name => l_role_name,
p_program_name => NULL,
p_program_tag => NULL,
x_success => l_return_status,
x_errcode => l_error_code
);
SELECT menu_name
INTO l_role_name
FROM fnd_menus
WHERE menu_id = l_role_ids(lcount1);
FND_GRANTS_PKG.delete_grant(
p_grantee_type => l_grantee_type,
p_grantee_key => l_grantee_key,
p_object_name => p_object_name,
p_instance_type => 'INSTANCE',
p_instance_set_id => NULL,
p_instance_pk1_value => p_pk1_value,
p_instance_pk2_value => p_pk2_value,
p_instance_pk3_value => NULL,
p_instance_pk4_value => NULL,
p_instance_pk5_value => NULL,
p_menu_name => l_role_name,
p_program_name => NULL,
p_program_tag => NULL,
x_success => l_return_status,
x_errcode => l_error_code
);
l_dynamic_sql := ' SELECT media_id, dm_node, A.created_by, dm_type' ||
' FROM FND_DOCUMENTS D, FND_ATTACHED_DOCUMENTS A' ||
' WHERE A.DOCUMENT_ID = D.DOCUMENT_ID ' ||
' AND A.ENTITY_NAME = :entity_name ' ||
' AND A.PK1_VALUE = :pk1_value ';
SELECT service_url, protocol
INTO l_service_url, l_protocol
FROM DOM_REPOSITORIES WHERE id = l_node_id;
SELECT user_name INTO l_user_name
FROM FND_USER where person_party_id = p_party_ids(lcount);
SELECT party_type INTO l_party_type
FROM hz_parties
WHERE party_id = p_party_ids(lcount);
SELECT user_name INTO l_user_login
FROM fnd_user
WHERE user_id = l_created_by;
INSERT INTO DOM_FOLDER_FILE_MEMBERSHIPS
(
REPOSITORY_ID,
REPOSITORY_ITEM_ID,
REPOSITORY_ITEM_TYPE,
ENTITY_NAME,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
PARTY_TYPE,
PARTY_ID,
OFO_ROLE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_node_id, --REPOSITORY_ID
l_media_id, --REPOSITORY_ITEM_ID
l_file_type, --REPOSITORY_ITEM_TYPE
l_entity_name, --ENTITY_NAME
p_pk1_value, --PK1_VALUE
p_pk2_value, --PK2_VALUE
p_pk3_value, --PK3_VALUE
p_pk4_value, --PK4_VALUE
p_pk5_value, --PK5_VALUE
l_party_type, --PARTY_TYPE
p_party_ids(lcount), --PARTY_ID
p_ocs_role, --OFO_ROLE
NVL(p_api_caller, g_current_user_id), --CREATED_BY
SYSDATE, --CREATION_DATE
NVL(p_api_caller, g_current_user_id), --LAST_UPDATED_BY
SYSDATE, --LAST_UPDATE_DATE
g_current_login_id --LAST_UPDATE_LOGIN
);
select user_name
from fnd_user
where user_id=cp_user_id;
SELECT user_name
FROM FND_USER
where person_party_id = cp_party_id ;
SELECT service_url, protocol
INTO l_service_url, l_protocol
FROM DOM_REPOSITORIES WHERE id = p_repository_id;
l_dynamic_sql := ' SELECT media_id, dm_node, A.created_by, dm_type' ||
' FROM FND_DOCUMENTS D, FND_ATTACHED_DOCUMENTS A' ||
' WHERE A.DOCUMENT_ID = D.DOCUMENT_ID ' ||
' AND A.ENTITY_NAME = :entity_name ' ||
' AND A.PK1_VALUE = :pk1_value ';
SELECT service_url, protocol
INTO l_service_url, l_protocol
FROM DOM_REPOSITORIES WHERE id = l_node_id ;
SELECT user_name INTO l_user_name
FROM FND_USER where person_party_id = p_party_ids(lcount);
SELECT user_name INTO l_user_login
FROM fnd_user
WHERE user_id = l_created_by ;
SELECT party_type INTO l_party_type
FROM hz_parties
WHERE party_id = p_party_ids(lcount);
DELETE FROM DOM_FOLDER_FILE_MEMBERSHIPS
WHERE REPOSITORY_ID = l_node_id
AND REPOSITORY_ITEM_ID = l_media_id
AND REPOSITORY_ITEM_TYPE = l_file_type
AND ENTITY_NAME = l_entity_name
AND ( (PK1_VALUE=p_pk1_value ) OR ( (PK1_VALUE IS NULL) AND (p_pk1_value IS NULL)) )
AND ( (PK2_VALUE=p_pk2_value ) OR ( (PK2_VALUE IS NULL) AND (p_pk2_value IS NULL)))
AND ( (PK3_VALUE=p_pk3_value ) OR ( (PK3_VALUE IS NULL) AND (p_pk3_value IS NULL)) )
AND ( (PK4_VALUE=p_pk4_value ) OR ( (PK4_VALUE IS NULL) AND (p_pk4_value IS NULL)))
AND ( (PK5_VALUE=p_pk5_value ) OR ( (PK5_VALUE IS NULL) AND (p_pk5_value IS NULL)) )
AND PARTY_TYPE = l_party_type
AND PARTY_ID = p_party_ids(lcount)
AND OFO_ROLE = G_OCS_ROLE ;
SELECT grants.menu_id menu_id
FROM fnd_grants grants
WHERE grants.object_id = cp_object_id
AND ((grants.instance_pk1_value=cp_document_id )
OR((grants.instance_pk1_value = '*NULL*') AND (cp_document_id IS NULL)))
AND ((grants.instance_pk2_value=cp_revision_id )
OR((grants.instance_pk2_value = '*NULL*') AND (cp_revision_id IS NULL)))
AND ((grants.parameter1=cp_change_id )
OR((grants.parameter1 IS NULL) AND (cp_change_id IS NULL)))
AND ((grants.parameter2=cp_change_line_id )
OR((grants.parameter2 IS NULL) AND (cp_change_line_id IS NULL))
);
SELECT party_type
FROM hz_parties
WHERE party_id=cp_party_id;
SELECT party_type
FROM hz_parties
WHERE party_id=cp_party_id;
SELECT count(*) INTO l_count
FROM DOM_FOLDER_FILE_MEMBERSHIPS
WHERE REPOSITORY_ITEM_ID = p_file_id
AND REPOSITORY_ID = p_repos_id
AND PARTY_ID = p_party_id
AND PARTY_TYPE = l_party_type
AND entity_name = p_entity_name;