[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_user_attrib(
p_user_id IN NUMBER ,
p_party_id IN NUMBER ,
p_user_attrib_id IN NUMBER ,
p_user_attrib_name IN VARCHAR2,
p_user_attrib_type IN VARCHAR2,
p_static_type IN VARCHAR2,
p_select_text IN VARCHAR2
);
p_select_text VARCHAR2)
RETURN BOOLEAN IS
l_api_name CONSTANT VARCHAR2(30) := 'CHECK_GRANT_TEXT';
l_select_text VARCHAR(32000);
l_debug_str := 'Table Name: '||p_table_name||','||'Select Text: '||p_select_text;
l_select_text := replace_string(ltrim(p_select_text),':PARTY_ID','igs_sc_vars.get_partyid');
l_select_text := replace_string(ltrim(l_select_text),':USER_ID','igs_sc_vars.get_userid');
l_select_text := replace_string(ltrim(l_select_text),':TBL_ALIAS','tstal');
l_debug_str := 'Final Select: '||'SELECT 1 FROM ('||' SELECT 1 FROM '||p_table_name||' WHERE '||l_select_text||' )';
EXECUTE IMMEDIATE 'SELECT 1 FROM ('||' SELECT 1 FROM '||p_table_name||' WHERE '||l_select_text||' )' ;
SELECT sco.obj_group_id,
sco.object_id,
fnd.obj_name,
grp.default_policy_type,
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
FROM igs_sc_objects sco,
fnd_objects fnd,
igs_sc_grants grn,
igs_sc_obj_groups grp
WHERE (application_id IN (8405,8406))
AND fnd.object_id = sco.object_id
AND sco.obj_group_id = grn.obj_group_id
AND grn.grant_id = cp_grant_id
AND grp.obj_group_id = grn.obj_group_id
AND sco.active_flag = 'Y';
SELECT grant_id,
grant_text
FROM igs_sc_grants gr
WHERE grant_id = p_grant_id;
l_attr_select VARCHAR2(4000);
l_usr_select VARCHAR2(4000);
l_obj_select VARCHAR2(4000);
SELECT grant_where
FROM igs_sc_obj_grants
WHERE object_id = s_obj_id
AND grant_id = s_grant_id;
SELECT grant_id,
grant_cond_num,
obj_attrib_id,
user_attrib_id,
condition,
text_value
FROM igs_sc_grant_conds
WHERE grant_id = s_grant_id;
SELECT *
FROM igs_sc_usr_attribs
WHERE user_attrib_id = s_attrib_id;
SELECT *
FROM igs_sc_obj_att_mths
WHERE obj_attrib_id = s_attrib_id
AND object_id = s_object_id;
l_obj_select := '';
l_usr_select := '';
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_obj_attrib.select_text||' ';
l_obj_const := l_obj_attrib.select_text;
l_obj_select := replace_string(ltrim(l_obj_attrib.select_text),':TBL_ALIAS',l_obj_alias);
l_part_grant2 := ') IN ( SELECT '||l_alias_name||'.pk1_value';
l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');
l_attr_select := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');
l_usr_select := replace_string(ltrim(l_attr_select),':TBL_ALIAS',l_usr_alias);
l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');
l_usr_const := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');
l_usr_select :='SELECT '||l_usr_alias||'.attr_value FROM igs_sc_usr_att_vals '||l_usr_alias||' WHERE '||l_usr_alias||'.user_id=igs_sc_vars.get_userid AND '
||l_usr_alias||'.user_attrib_id='||c_grant_cond_rec.user_attrib_id;
IF l_obj_select IS NULL THEN
-- --code added my mmkumar
IF l_obj_attrib.NULL_ALLOW_FLAG = 'Z' THEN
--IF isSingleGrantCond(p_grants_rec.grant_text) THEN
IF onlyZTypeAttributes THEN
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' 1=1 ';
l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_1';
l_debug_str := ' Object select NULL, and Z type attrib. Condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
IF l_obj_const IS NOT NULL AND l_usr_select IS NOT NULL THEN
-- User attribute is select of any kind and object attribute is not select
-- User Select = Obj CONST
l_found_pos := INSTR(UPPER(ltrim(l_usr_select)),'FROM',1,1);
l_column_name := substr(ltrim(l_usr_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'
||' EXISTS ('||l_usr_select||' AND '||l_column_name||' '||check_operation(l_grant_cond(c_grant_cond_rec.grant_cond_num).condition)||' '||l_obj_const||' )';
ELSIF l_obj_const IS NULL AND l_usr_select IS NOT NULL THEN
-- Colunmn name = User Select
IF l_obj_attrib.NULL_ALLOW_FLAG = 'Y' THEN -- Nullable column
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := '('||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
l_grant_cond(c_grant_cond_rec.grant_cond_num).condition ||' (' ||l_usr_select||') OR '||l_table_column ||' IS NULL) '||l_post_grant;
l_grant_cond(c_grant_cond_rec.grant_cond_num).condition ||' (' ||l_usr_select||')'||l_post_grant;
ELSIF l_obj_const IS NULL AND l_usr_select IS NULL THEN
-- Column name = User CONST
IF l_obj_attrib.NULL_ALLOW_FLAG = 'Y' THEN -- Nullable column
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := '('||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
l_obj_const||' '||l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const||' OR '||l_table_column ||' IS NULL)'||l_post_grant;
l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_2';
l_debug_str := ' Object select is NULL. Non Z. Grant condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
ELSE --Object select is not null
-- we need to change select so we'll have 2 select statments
--find the name of the select coulmn for attribute
l_found_pos := INSTR(UPPER(ltrim(l_obj_select)),'FROM',1,1);
l_column_name := substr(ltrim(l_obj_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'
l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_3';
l_debug_str := ' Object select NOT NULL, and Z type attrib. Condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
IF l_usr_select IS NOT NULL THEN
--Add user select
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' EXISTS ('||l_obj_select||' AND '||l_column_name||' '||
l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' ('||l_usr_select||' )';
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_obj_select;
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' EXISTS ('||l_obj_select||' AND '||l_column_name||' '||
l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const;
l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' OR NOT EXISTS (' ||l_obj_select||'))';
l_label := 'igs.plsql.igs_sc_grants_pvt.build_grant.Obj_select_4';
l_debug_str := ' Object select NOT NULL, and Non Z type attrib. Condition text '||l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text ;
DELETE FROM igs_sc_obj_grants
WHERE object_id = p_group_rec.object_id AND grant_id = p_grants_rec.grant_id;
INSERT INTO igs_sc_obj_grants (
grant_id,
object_id,
grant_where,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
p_grants_rec.grant_id,
p_group_rec.object_id,
l_statment,
SYSDATE,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
SYSDATE,
NVL(FND_GLOBAL.login_id, -1) ) ;
SELECT sco.obj_group_id,
sco.object_id,
fnd.obj_name,
grp.default_policy_type,
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
FROM igs_sc_objects sco,
fnd_objects fnd,
igs_sc_obj_groups grp
WHERE application_id IN (8405,8406)
AND fnd.obj_name = cp_obj_name
AND grp.obj_group_id = sco.obj_group_id
AND fnd.object_id = sco.object_id;
SELECT gr.grant_id,
objgr.grant_where
FROM igs_sc_grants gr,
igs_sc_obj_grants objgr,
wf_local_user_roles rls
WHERE rls.user_orig_system =g_user_orig_system
AND rls.user_orig_system_id = s_user_id
AND rls.role_orig_system = 'IGS'
AND rls.role_orig_system_id = gr.user_group_id
AND objgr.object_id = s_object_id
AND objgr.grant_id = gr.grant_id
AND SYSDATE BETWEEN NVL(rls.start_date,sysdate-1) AND NVL(rls.expiration_date,sysdate+1)
AND gr.obj_group_id = s_group_id
AND gr.locked_flag ='Y'
AND decode(p_function_type,'S',gr.grant_select_flag,'I',gr.grant_insert_flag,'D',gr.grant_delete_flag,'U',gr.grant_update_flag,'N')='Y';
SELECT objgr.grant_where
FROM igs_sc_grants gr,
igs_sc_obj_grants objgr
WHERE objgr.object_id = s_object_id
AND objgr.grant_id = gr.grant_id
AND gr.obj_group_id = s_group_id
AND gr.locked_flag ='Y'
AND upper(gr.grant_name)='DEFAULT';
||l_user_id||','||'Final Select: '||l_statment;
SELECT user_attrib_id ,
user_attrib_name ,
user_attrib_type ,
static_type ,
select_text
FROM igs_sc_usr_attribs
WHERE user_attrib_id = p_attrib_id
OR ( p_attrib_id IS NULL AND
(static_type = 'S'
OR (static_type = 'C' AND p_all_attribs = 'Y') )
)
ORDER BY user_attrib_id;
SELECT user_attrib_id ,
user_attrib_name ,
user_attrib_type ,
static_type ,
select_text
FROM igs_sc_usr_attribs
WHERE user_attrib_id = p_attrib_id;
SELECT user_orig_system_id user_id
FROM wf_local_user_roles rls
WHERE rls.user_orig_system = 'FND_USR'
AND rls.role_orig_system = 'IGS'
AND (rls.EXPIRATION_DATE IS NULL OR rls.EXPIRATION_DATE > SYSDATE)
AND rls.parent_orig_system = 'IGS'
AND rls.parent_orig_system_id = rls.role_orig_system_id
AND rls.partition_id = 0;
SELECT attr_value
FROM igs_sc_usr_att_vals
WHERE user_attrib_id = 1
AND user_id = s_user_id;
insert_user_attrib(
p_user_id => l_user_id ,
p_party_id => l_party_id ,
p_user_attrib_id => c_attrib_rec.user_attrib_id ,
p_user_attrib_name => c_attrib_rec.user_attrib_name ,
p_user_attrib_type => c_attrib_rec.user_attrib_type ,
p_static_type => c_attrib_rec.static_type ,
p_select_text => c_attrib_rec.select_text );
PROCEDURE insert_user_attrib(
p_user_id IN NUMBER ,
p_party_id IN NUMBER ,
p_user_attrib_id IN NUMBER ,
p_user_attrib_name IN VARCHAR2,
p_user_attrib_type IN VARCHAR2,
p_static_type IN VARCHAR2,
p_select_text IN VARCHAR2
) IS
l_select VARCHAR2(32000);
l_select_text VARCHAR2(2000);
DELETE FROM igs_sc_usr_att_vals
WHERE user_id = p_user_id
AND user_attrib_id = p_user_attrib_id;
l_select := 'INSERT INTO igs_sc_usr_att_vals (USER_ID,USER_ATTRIB_ID,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,ATTR_VALUE )';
l_select_text := replace_string(ltrim(p_select_text),':PARTY_ID','IGS_SC_GRANTS_PVT.get_current_party');
l_select_text := replace_string(ltrim(l_select_text),':USER_ID','IGS_SC_GRANTS_PVT.get_current_user');
l_select_text := replace_string(ltrim(l_select_text),':TBL_ALIAS','sc'||p_user_attrib_id||'u');
l_select := l_select||' SELECT :V1, :V2, sysdate , -1 , -1, sysdate ,'||l_select_text||' FROM dual ';
ELSE --Select of M-valued
-- Remove all spaces from text and remove SELECT word from a text
l_select := l_select||' SELECT :V1, :V2, sysdate , -1 , -1, sysdate ,'||substr(l_select_text,7,32000);
Put_Log_Msg ('Executed text is: '||IGS_SC_GRANTS_PVT.get_current_user||' : '||IGS_SC_GRANTS_PVT.get_current_party||':'|| l_select ,0);
EXECUTE IMMEDIATE l_select USING p_user_id,p_user_attrib_id;
END insert_user_attrib;
SELECT grant_id
FROM igs_sc_grants
WHERE ( obj_group_id = p_obj_group_id OR p_obj_group_id IS NULL)
AND locked_flag ='Y';
UPDATE igs_sc_obj_groups
SET default_policy_type = 'G';
SELECT grant_id
FROM igs_sc_grants
WHERE ( obj_group_id = p_obj_group_id OR p_obj_group_id IS NULL)
AND locked_flag = 'N';
p_select_text VARCHAR2,
p_obj_attrib_type VARCHAR2 )
RETURN BOOLEAN IS
l_select_text VARCHAR2(32000);
l_debug_str := 'Table Name: '||p_table_name||','||'Select Text: '||p_select_text||','||'Object Attribute Type: '||p_obj_attrib_type;
l_select_text := ' EXISTS ( SELECT '||p_select_text||' FROM DUAL ) ';
l_select_text := p_select_text||' IS NOT NULL ';
l_select_text := ' EXISTS ( '||p_select_text||' )';
RETURN check_grant_text ( p_table_name, l_select_text );