[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT object_id
INTO v_object_id
FROM FND_OBJECTS
WHERE obj_name = v_object_name and application_id=661;
SELECT function_id
INTO v_function_id
FROM FND_FORM_FUNCTIONS
WHERE function_name = p_function_name;
SELECT USER_ID
INTO G_USER_ID
FROM FND_USER
WHERE USER_NAME = l_user_name;
| QP_SECU_UPDATE
| Input: p_instance_type ->
| 'PRL' for standard pricelist
| 'MOD' for modifier
| 'AGR' for agreement pricelist
| 'FOR' for formula -- not used yet
| Input: p_instance_pk1_value -> list_header_id in qp_list_headers_b
| other primary key for formulas, qualifiers...
| p_instance_pk2_value -> optional, not used currently
| p_instance_pk3_vaue -> optional, not used currently
| Input: p_user_name -> optional, default is current user
| p_resp_id -> optional, default is current logged in responsibility
| p_org_id -> optional,default is current logged in operating unit
| Output: varchar2 ->
| G_AUTHORIZED='T': authorized
| G_DENIED='F':denied
| G_ERROR='E': error happened
+----------------------------------------------------------------------
*/
FUNCTION check_function(
p_function_name IN VARCHAR2,
p_instance_type IN VARCHAR2,
p_instance_pk1 IN NUMBER,
p_instance_pk2 IN NUMBER default null,
p_instance_pk3 IN NUMBER default null,
p_user_name IN VARCHAR2 default null,
p_resp_id IN NUMBER default null,
p_org_id IN NUMBER default null
) RETURN VARCHAR2 IS
E_ROUTINE_ERROR EXCEPTION;
SELECT 'X'
FROM qp_grants g
WHERE rownum = 1
AND g.object_id = cp_object_id
AND ( ( g.grantee_type = 'USER' AND
g.grantee_id = cp_user_id) OR
( g.grantee_type = 'RESP' AND
g.grantee_id = cp_resp_id) OR
( g.grantee_type = 'GLOBAL' AND
g.grantee_id = -1) OR
( g.grantee_type = 'OU' AND
((cp_mo_access_mode = 'S' and g.grantee_id = sys_context('multi_org2','current_org_id'))
or (cp_mo_access_mode = 'M' and mo_global.check_access(g.grantee_id) = 'Y')
or (cp_mo_access_mode = 'A')))
-- g.grantee_id = cp_org_id)
) AND
g.menu_id IN
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)
AND (G.instance_id = cp_instance_id)
AND ( g.end_date IS NULL OR g.end_date >= l_sysdate )
AND g.start_date <= l_sysdate;
| 'QP_SECU_VIEW' for view, 'QP_SECU_UPDATE' for update
| Input: p_instance_type ->
| 'PRL' for standard pricelists,
| 'MOD' for modifier,
| 'AGR' for agreement pricelists,
| 'FOR' for formulas --not used yet
| null for all list headers
| Input: p_user_name -> optional, default is current user
| p_resp_id -> optional, default is current logged in
| responsibility
| p_org_id -> optional, default is current logged in
| operating unit
| Output: system.qp_inst_pk_vals ->
| all the object ids which can be accessed with the specific
| function for current user
+----------------------------------------------------------------------
*/
FUNCTION auth_instances(
p_function_name IN VARCHAR2,
p_instance_type IN VARCHAR2 default null,
p_user_name IN VARCHAR2 default G_USER_NAME,
p_resp_id IN NUMBER default G_RESP_ID,
p_org_id IN NUMBER default G_ORG_ID
) RETURN system.qp_inst_pk_vals IS
l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
SELECT list_header_id
FROM qp_list_headers_b
WHERE list_type_code = G_PRICELIST_TYPE;
SELECT list_header_id
FROM qp_list_headers_b
WHERE list_type_code in (G_MODIFIER_SUR,G_MODIFIER_PRO,
G_MODIFIER_DLT,G_MODIFIER_DEL,G_MODIFIER_CHARGES);
SELECT list_header_id
FROM qp_list_headers_b
WHERE list_type_code = G_AGREEMENT_TYPE;
SELECT list_header_id
FROM qp_list_headers_b;
SELECT g.instance_id
FROM qp_grants g
WHERE ((g.grantee_type = 'USER' AND
g.grantee_id = cp_user_id) OR
(g.grantee_type = 'RESP' AND
g.grantee_id = cp_resp_id) OR
(g.grantee_type = 'OU' AND
g.grantee_id = cp_org_id) OR
(g.grantee_type = 'GLOBAL' AND
g.grantee_id = -1)) AND
g.object_id = cp_object_id AND
g.menu_id IN
(SELECT cmf.menu_id
FROM fnd_compiled_menu_functions cmf
WHERE cmf.function_id = cp_function_id) AND
(g.end_date IS NULL OR g.end_date >= l_sysdate) AND
g.start_date <= l_sysdate;
SELECT g.instance_id
FROM qp_grants g
WHERE ((g.grantee_type = 'USER' AND
g.grantee_id = cp_user_id) OR
(g.grantee_type = 'RESP' AND
g.grantee_id = cp_resp_id) OR
(g.grantee_type = 'OU' AND
g.grantee_id = cp_org_id) OR
(g.grantee_type = 'GLOBAL' AND
g.grantee_id = -1)) AND
g.object_id = cp_object_id AND
g.instance_type = cp_instance_type AND
g.menu_id IN
(SELECT cmf.menu_id
FROM fnd_compiled_menu_functions cmf
WHERE cmf.function_id = cp_function_id) AND
(g.end_date IS NULL OR g.end_date >= l_sysdate) AND
g.start_date <= l_sysdate;
QP_SECURITY_DEFAULT_UPDATE: NONE, OU, RESP, USER
*/
/* check 'QP_SECURITY_CONTROL' profile to see security is on/off.
In case of 'OFF', return all object ids
*/
l_security_control := nvl(FND_PROFILE.value(G_SECURITY_CONTROL_PROFILE), G_SECURITY_OFF);
SELECT database_object_name,
pk1_column_name,
pk2_column_name
INTO l_database_object_name,
l_pk1_column_name,
l_pk2_column_name
FROM fnd_objects
WHERE obj_name = l_object_name;
l_sql_stmt := 'Select ' ||
nvl(l_pk1_column_name, 'NULL') ||
' from ' || l_database_object_name ||
' where ' || l_predicate;
SELECT menu_id
INTO v_menu_id
FROM fnd_menus
WHERE menu_name = p_menu_name;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
SELECT list_type_code
FROM qp_list_headers_b
WHERE list_header_id = cp_instance_id;
SELECT 'X'
FROM qp_grants
WHERE grantee_type = cp_grantee_type AND
grantee_id = cp_grantee_id AND
instance_type = cp_instance_type AND
instance_id = cp_instance_id AND
object_id = cp_object_id ;
QP_SECURITY_DEFAULT_UPDATE: GLOBAL, OU, RESP, USER, NONE
*/
/* validate p_instance_pk1
*/
OPEN list_header_id_exist_cur(p_instance_pk1);
If already, update the privilege; otherwise, insert new.*/
l_last_updated_by := FND_GLOBAL.USER_ID;
l_created_by := l_last_updated_by;
l_last_update_login := FND_GLOBAL.LOGIN_ID ;
/*if not existing, insert new*/
OPEN grant_exist_cur(l_grantee_type, l_grantee_id,
p_instance_type, p_instance_pk1, l_object_id);
SELECT QP_GRANTS_S.nextval INTO l_grant_id FROM DUAL;
INSERT INTO qp_grants
(GRANT_ID,
OBJECT_ID,
INSTANCE_TYPE,
INSTANCE_ID,
GRANTEE_TYPE,
GRANTEE_ID,
MENU_ID,
START_DATE,
END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(l_grant_id,
l_object_id,
p_instance_type,
p_instance_pk1,
l_grantee_type,
l_grantee_id,
l_menu_id,
sysdate, --l_start_date,
null, --l_end_date,
sysdate, --l_last_update_date,
l_last_updated_by,
sysdate, --l_creation_date,
l_created_by,
l_last_update_login );
ELSE /*update the existing one*/
UPDATE qp_grants
SET MENU_ID = l_menu_id,
START_DATE = sysdate ,
END_DATE = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_created_by,
LAST_UPDATE_LOGIN = l_last_update_login
WHERE GRANT_ID = (select grant_id
FROM qp_grants
WHERE grantee_type = l_grantee_type AND
grantee_id = l_grantee_id AND
instance_type = p_instance_type AND
instance_id = p_instance_pk1 AND
object_id = l_object_id AND
ROWNUM = 1);
l_last_updated_by := FND_GLOBAL.USER_ID;
l_created_by := l_last_updated_by;
l_last_update_login := FND_GLOBAL.LOGIN_ID ;
/*if not existing, insert new*/
OPEN grant_exist_cur(l_grantee_type, l_grantee_id,
p_instance_type, p_instance_pk1, l_object_id);
SELECT QP_GRANTS_S.nextval INTO l_grant_id FROM DUAL;
INSERT INTO qp_grants
(GRANT_ID,
OBJECT_ID,
INSTANCE_TYPE,
INSTANCE_ID,
GRANTEE_TYPE,
GRANTEE_ID,
MENU_ID,
START_DATE,
END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(l_grant_id,
l_object_id,
p_instance_type,
p_instance_pk1,
l_grantee_type,
l_grantee_id,
l_menu_id,
sysdate, --l_start_date,
null, --l_end_date,
sysdate, --l_last_update_date,
l_last_updated_by,
sysdate, --l_creation_date,
l_created_by,
l_last_update_login );
ELSE /*update the existing one*/
UPDATE qp_grants
SET MENU_ID = l_menu_id,
START_DATE = sysdate ,
END_DATE = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_created_by,
LAST_UPDATE_LOGIN = l_last_update_login
WHERE GRANT_ID = (select grant_id
FROM qp_grants
WHERE grantee_type = l_grantee_type AND
grantee_id = l_grantee_id AND
instance_type = p_instance_type AND
instance_id = p_instance_pk1 AND
object_id = l_object_id AND
ROWNUM = 1);
select function_id into l_menu_id from fnd_form_functions where function_name = 'QP_SECU_UPDATE';
FUNCTION GET_UPDATE_ALLOWED (p_object_name IN VARCHAR2, p_list_header_id IN NUMBER)
RETURN VARCHAR2 IS
l_result VARCHAR2(1);
SELECT 'X'
FROM qp_grants g
WHERE rownum = 1
AND ( ( g.grantee_type = 'USER' AND
g.grantee_id = cp_user_id) OR
( g.grantee_type = 'RESP' AND
g.grantee_id = cp_resp_id) OR
( g.grantee_type = 'GLOBAL' AND
g.grantee_id = -1) OR
( g.grantee_type = 'OU' AND
(mo_global.get_access_mode = 'S' and sys_context('multi_org2', 'current_org_id') = g.grantee_id)
OR (mo_global.get_access_mode = 'A')
OR (mo_global.get_access_mode = 'M' and mo_global.check_access(g.grantee_id) = 'Y'))
) AND
g.menu_id IN
(select cmf.menu_id
from fnd_compiled_menu_functions cmf
where cmf.function_id = cp_function_id)
AND (G.instance_id = cp_instance_id)
AND ( g.end_date IS NULL OR g.end_date >= sysdate )
AND g.start_date <= sysdate;
p_function => 'QP_SECU_UPDATE',
p_object_name => p_object_name,
p_instance_pk1_value => p_list_header_id,
p_instance_pk2_value => null,
p_instance_pk3_value => null,
p_instance_pk4_value => null,
p_instance_pk5_value => null,
p_user_name => qp_SECURITY.g_user_name );
END GET_UPDATE_ALLOWED;
or EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = orig_org_id)';