[Home] [Help]
PACKAGE BODY: APPS.EGO_VALID_INSTANCE_SET_GRANTS
Source
1 PACKAGE BODY EGO_VALID_INSTANCE_SET_GRANTS AS
2 /* $Header: EGOISGRB.pls 120.4 2010/09/10 07:29:48 geguo ship $ */
3 /*************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EGOISGRB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EGO_VALID_INSTANCE_SET_GRANTS
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 31-MAR-03 Deepak Jebar Initial Creation
21 -- 09-APR-07 pfarkade R12C Security Related Changes
22 **************************************************************************/
23
24 PROCEDURE GET_VALID_INSTANCE_SETS(p_obj_name IN VARCHAR2,
25 p_grantee_type IN VARCHAR2,
26 p_parent_obj_sql IN VARCHAR2,
27 p_bind1 IN VARCHAR2,
28 p_bind2 IN VARCHAR2,
29 p_bind3 IN VARCHAR2,
30 p_bind4 IN VARCHAR2,
31 p_bind5 IN VARCHAR2,
32 p_obj_ids IN VARCHAR2,
33 x_inst_set_ids OUT NOCOPY VARCHAR2) IS
34 CURSOR inst_set_preds IS
35 select DISTINCT sets.instance_set_id instance_set_id , sets.instance_set_name instance_set_name,
36 sets.predicate predicate
37 from
38 fnd_grants grants,
39 fnd_object_instance_sets sets,
40 fnd_objects obj
41 where obj.obj_name = p_obj_name
42 AND grants.object_id = obj.object_id
43 AND grants.instance_type='SET'
44 AND grants.parameter1 is null
45 AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate)
46 AND grants.grantee_type = p_grantee_type
47 AND sets.instance_set_id = grants.instance_set_id
48 order by instance_set_name;
49 CURSOR obj_meta_data IS
50 select DATABASE_OBJECT_NAME,
51 PK1_COLUMN_NAME,PK2_COLUMN_NAME,
52 PK3_COLUMN_NAME,PK4_COLUMN_NAME,
53 PK5_COLUMN_NAME from fnd_objects where OBJ_NAME = p_obj_name;
54 obj_meta_data_rec obj_meta_data%ROWTYPE;
55 i NUMBER := 1;
56 -- bug 3748547 setting varchar2 fields to maximum size
57 query_to_exec VARCHAR2(32767);
58 obj_std_pkq VARCHAR2(32767);
59 prim_key_str VARCHAR2(32767);
60 inst_set_ids VARCHAR2(32767);
61 cursor_select INTEGER;
62 cursor_execute INTEGER;
63 BEGIN
64 OPEN obj_meta_data;
65 FETCH obj_meta_data INTO obj_meta_data_rec;
66 obj_std_pkq := 'SELECT ' || obj_meta_data_rec.PK1_COLUMN_NAME;
67 prim_key_str := obj_meta_data_rec.PK1_COLUMN_NAME;
68 IF obj_meta_data_rec.PK2_COLUMN_NAME IS NOT NULL THEN
69 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
70 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
71 END IF;
72 IF obj_meta_data_rec.PK3_COLUMN_NAME IS NOT NULL THEN
73 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
74 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
75 END IF;
76 IF obj_meta_data_rec.PK4_COLUMN_NAME IS NOT NULL THEN
77 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
78 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
79 END IF;
80 IF obj_meta_data_rec.PK5_COLUMN_NAME IS NOT NULL THEN
81 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
82 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
83 END IF;
84 -- R12C Security Changes
85 /*obj_std_pkq := obj_std_pkq || ' FROM ' || obj_meta_data_rec.DATABASE_OBJECT_NAME;*/
86 IF (p_obj_name = 'EGO_ITEM') THEN
87 obj_std_pkq := obj_std_pkq || ' FROM ' || obj_meta_data_rec.DATABASE_OBJECT_NAME ||', ego_item_cat_denorm_hier cathier';
88 ELSE
89 obj_std_pkq := obj_std_pkq || ' FROM ' || obj_meta_data_rec.DATABASE_OBJECT_NAME;
90 END IF;
91 -- R12C Security Changes
92 CLOSE obj_meta_data;
93
94 FOR inst_set_preds_rec IN inst_set_preds
95 LOOP
96 -- R12C Security Changes
97 /*IF p_obj_ids IS NOT NULL THEN
98 query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
99 query_to_exec := query_to_exec || ' WHERE ' || inst_set_preds_rec.predicate || ' )';
100 ELSIF p_parent_obj_sql IS NOT NULL THEN
101 query_to_exec := 'SELECT 1 from dual WHERE EXISTS( ' || obj_std_pkq || ' WHERE ';
102 query_to_exec := query_to_exec || inst_set_preds_rec.predicate || ' AND (';
103 query_to_exec := query_to_exec || prim_key_str || ') IN (' || p_parent_obj_sql || '))';
104 END IF;*/
105 IF (p_obj_name = 'EGO_ITEM') THEN
106 IF p_obj_ids IS NOT NULL THEN
107 query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
108 query_to_exec := query_to_exec || ' WHERE ' || inst_set_preds_rec.predicate || 'AND item_catalog_group_id = cathier.child_catalog_group_id(+) )';
109 ELSIF p_parent_obj_sql IS NOT NULL THEN
110 query_to_exec := 'SELECT 1 from dual WHERE EXISTS( ' || obj_std_pkq || ' WHERE ';
111 query_to_exec := query_to_exec || inst_set_preds_rec.predicate || 'AND item_catalog_group_id = cathier.child_catalog_group_id AND (';
112 query_to_exec := query_to_exec || prim_key_str || ') IN (' || p_parent_obj_sql || '))';
113 END IF;
114 ELSE
115 IF p_obj_ids IS NOT NULL THEN
116 query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
117 query_to_exec := query_to_exec || ' WHERE ' || inst_set_preds_rec.predicate || ' )';
118 ELSIF p_parent_obj_sql IS NOT NULL THEN
119 query_to_exec := 'SELECT 1 from dual WHERE EXISTS( ' || obj_std_pkq || ' WHERE ';
120 query_to_exec := query_to_exec || inst_set_preds_rec.predicate || ' AND (';
121 query_to_exec := query_to_exec || prim_key_str || ') IN (' || p_parent_obj_sql || '))';
122 END IF;
123 END IF;
124 -- R12C Security Changes
125 cursor_select := DBMS_SQL.OPEN_CURSOR;
126 DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
127 IF p_bind1 IS NOT NULL THEN
128 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id1', p_bind1);
129 END IF;
130 IF p_bind2 IS NOT NULL THEN
131 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id2', p_bind2);
132 END IF;
133 IF p_bind3 IS NOT NULL THEN
134 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id3', p_bind3);
135 END IF;
136 IF p_bind4 IS NOT NULL THEN
137 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id4', p_bind4);
138 END IF;
139 IF p_bind5 IS NOT NULL THEN
140 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id5', p_bind5);
141 END IF;
142 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
143 IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
144 IF i = 1 THEN
145 inst_set_ids := to_char(inst_set_preds_rec.instance_set_id);
146 i := 2;
147 ELSE
148 inst_set_ids := inst_set_ids || ',' || inst_set_preds_rec.instance_set_id;
149 END IF;
150 END IF;
151 DBMS_SQL.CLOSE_CURSOR(cursor_select);
152 END LOOP;
153 IF inst_set_ids IS NOT NULL THEN
154 x_inst_set_ids := inst_set_ids; /**** list of valid inst_set_ids ****/
155 ELSE
156 x_inst_set_ids := '-1';
157 END IF;
158 END;
159 END;