[Home] [Help]
PACKAGE BODY: APPS.GMD_SECURITY_POLICY
Source
1 PACKAGE BODY gmd_security_policy AS
2 /* $Header: GMDFSPPB.pls 120.4.12010000.4 2009/12/04 09:07:31 kannavar ship $ */
3
4 -- =======================================================================================
5 -- USAGE
6 -- Used for selection from the following tables: fm_form_mst_b
7 -- HISTORY
8 -- =======================================================================================
9
10 FUNCTION secure_formula_sel (obj_schema IN VARCHAR2,
11 obj_name IN VARCHAR2) RETURN VARCHAR2 IS
12 f_predicate VARCHAR2(4000);
13 v_resp_id NUMBER;
14
15 BEGIN
16 --
17 -- Responsibility 'Product Development Security Profile Manager'
18 -- has full access to all formulas
19 --
20 SELECT responsibility_id INTO v_resp_id
21 FROM fnd_responsibility
22 WHERE responsibility_key = 'GMD_SECURITY_PROFILE_MGR';
23
24 --
25 -- Context m_fs_context, variable pc_ind with value 'Yes'
26 -- allows Planning and Costing enginees to have access to all formulas.
27 --
28 IF SYS_CONTEXT('m_fs_context','pc_ind') = 'Yes' or
29 v_resp_id = fnd_global.resp_id
30 THEN
31 f_predicate := '1=1';
32
33 ELSE
34
35 f_predicate :=
36 ' EXISTS ( SELECT 1 '||
37 ' FROM gmd_security_profiles sp '||
38 ' WHERE sp.assign_method_ind IN ( ''A'') '|| --,''M'') '|| /* Bug No.9077438 */
39 ' AND ( ( sp.user_id = fnd_profile.value(''GMD_DEFAULT_USER'') '||
40 ' OR sp.user_id = fnd_global.user_id '||
41 ' ) '||
42 ' OR ( EXISTS ( SELECT rg.responsibility_id '||
43 ' FROM FND_USER_RESP_GROUPS rg '||
44 ' WHERE rg.user_id = fnd_global.user_id '||
45 ' AND sp.responsibility_id = rg.responsibility_id '||
46 ' AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date, SYSDATE) '||
47 ' ) '||
48 ' ) '||
49 ' ) '||
50 ' AND NVL(responsibility_id,fnd_global.resp_id) = fnd_global.resp_id '|| /* Bug No.9077438 */
51 ' AND ( EXISTS ( SELECT NULL '||
52 ' FROM org_access a1 '||
53 ' WHERE ( ( sp.organization_id = a1.organization_id '||
54 ' AND sp.other_organization_id IS NULL '||
55 ' ) '||
56 ' OR sp.other_organization_id = a1.organization_id '||
57 ' ) '||
58 ' AND NVL(a1.disable_date, SYSDATE+1) >= SYSDATE '||
59 ' AND a1.resp_application_id = fnd_global.resp_appl_id '||
60 ' AND a1.responsibility_id = fnd_global.resp_id '||
61 ' ) '||
62 ' OR '||
63 ' NOT EXISTS ( SELECT NULL '||
64 ' FROM org_access a2 '||
65 ' WHERE ( ( sp.organization_id = a2.organization_id '||
66 ' AND sp.other_organization_id IS NULL '||
67 ' ) '||
68 ' OR sp.other_organization_id = a2.organization_id '||
69 ' ) '||
70 ' AND NVL(a2.disable_date, SYSDATE+1) >=SYSDATE '||
71 ' ) '||
72 ' ) '||
73 ' AND sp.organization_id = '||obj_name||'.owner_organization_id '||
74 ' ) '||
75 ' OR EXISTS ( SELECT 1 '||
76 ' FROM gmd_formula_security fs '||
77 ' WHERE ( ( fs.user_id = fnd_profile.value(''GMD_DEFAULT_USER'') '||
78 ' OR fs.user_id = fnd_global.user_id '||
79 ' ) '||
80 ' OR ( EXISTS ( SELECT rg.responsibility_id '||
81 ' FROM FND_USER_RESP_GROUPS rg '||
82 ' WHERE rg.user_id = fnd_global.user_id '||
83 ' AND fs.responsibility_id = rg.responsibility_id '||
84 ' AND SYSDATE BETWEEN rg.start_date '||
85 ' AND NVL(rg.end_date, SYSDATE) '||
86 ' ) '||
87 ' ) '||
88 ' ) '||
89 ' AND NVL(responsibility_id,fnd_global.resp_id) = fnd_global.resp_id '|| /* Bug No.9077438 */
90 ' AND (EXISTS ( SELECT NULL '||
91 ' FROM org_access ou '||
92 ' WHERE ( ( fs.organization_id = ou.organization_id '||
93 ' AND fs.other_organization_id IS NULL '||
94 ' ) '||
95 ' OR fs.other_organization_id = ou.organization_id '||
96 ' ) '||
97 ' AND NVL(ou.disable_date, SYSDATE+1) >= SYSDATE '||
98 ' AND ou.resp_application_id = fnd_global.resp_appl_id '||
99 ' AND ou.responsibility_id = fnd_global.resp_id '||
100 ' ) '||
101 ' OR '||
102 ' NOT EXISTS ( SELECT NULL '||
103 ' FROM org_access ou1 '||
104 ' WHERE ( ( ou1.organization_id = fs.organization_id '||
105 ' AND fs.other_organization_id IS NULL '||
106 ' ) '||
107 ' OR ou1.organization_id = fs.other_organization_id '||
108 ' ) '||
109 ' AND NVL(ou1.disable_date, SYSDATE+1) >=SYSDATE '||
110 ' ) '||
111 ' ) '||
112 ' AND fs.formula_id = ' ||obj_name||'.formula_id '||
113 ' ) ';
114 END IF;
115 RETURN f_predicate;
116
117 END secure_formula_sel;
118
119 -- =======================================================================================
120 -- USAGE
121 -- Used for Insert into the following tables: fm_form_mst_b
122 -- HISTORY
123 -- =======================================================================================
124 FUNCTION secure_formula_ins (obj_schema IN VARCHAR2,
125 obj_name IN VARCHAR2 )RETURN VARCHAR2 IS
126 f_predicate VARCHAR2(4000);
127 --
128 -- The Insert Predicate is based on GMD_SECURITY_PROFILES table since
129 -- a formula must exist to base access on GMD_FORMULA_SECURITY table
130 --
131
132 BEGIN
133 f_predicate :=
134 ' EXISTS ( SELECT 1 '||
135 ' FROM gmd_security_profiles sp '||
136 ' WHERE sp.access_type_ind = ''U'' '||
137 ' AND ( responsibility_id IN ( SELECT rg.responsibility_id '||
138 ' FROM FND_USER_RESP_GROUPS rg '||
139 ' WHERE rg.user_id = fnd_global.user_id '||
140 ' AND SYSDATE BETWEEN rg.start_date '||
141 ' AND NVL(rg.end_date, SYSDATE) '||
142 ' ) '||
143 ' OR ( sp.user_id = fnd_profile.value_specific(''GMD_DEFAULT_USER'') '||
144 ' OR sp.user_id = fnd_global.user_id '||
145 ' ) '||
146 ' ) '||
147 ' AND organization_id = '||obj_name||'.owner_organization_id '||
148 ' AND (other_organization_id IS NULL '||
149 ' OR EXISTS ( SELECT NULL '||
150 ' FROM org_access a3 '||
151 ' WHERE a3.organization_id = sp.other_organization_id '||
152 ' AND NVL(a3.disable_date, SYSDATE+1) >= SYSDATE '||
153 ' AND a3.resp_application_id = fnd_global.resp_appl_id '||
154 ' AND a3.responsibility_id = fnd_global.resp_id '||
155 ' ) '||
156 ' OR NOT EXISTS ( SELECT NULL '||
157 ' FROM org_access a4 '||
158 ' WHERE a4.organization_id = sp.other_organization_id '||
159 ' AND NVL(a4.disable_date, SYSDATE+1) >=SYSDATE '||
160 ' ) '||
161 ' ) '||
162 ' ) ';
163
164 RETURN f_predicate;
165 END secure_formula_ins;
166
167 -- =======================================================================================
168 -- USAGE
169 -- Used for Insert into the following tables: fm_matl_dtl
170 -- HISTORY
171 -- =======================================================================================
172 FUNCTION secure_formula_dtl_ins (obj_schema IN VARCHAR2,
173 obj_name VARCHAR2)
174 RETURN VARCHAR2 IS
175 f_predicate VARCHAR2(4000);
176 BEGIN
177 f_predicate := ' EXISTS ' ||
178 ' ( SELECT 1 '||
179 ' FROM gmd_security_profiles sp '||
180 ' WHERE sp.access_type_ind = ''U'' '||
181 ' AND ( responsibility_id IN ( SELECT rg.responsibility_id '||
182 ' FROM FND_USER_RESP_GROUPS rg '||
183 ' WHERE rg.user_id = fnd_global.user_id '||
184 ' AND SYSDATE BETWEEN rg.start_date '||
185 ' AND NVL(rg.end_date, SYSDATE) '||
186 ' ) '||
187 ' OR ( sp.user_id = fnd_profile.value_specific(''GMD_DEFAULT_USER'') '||
188 ' OR sp.user_id = fnd_global.user_id '||
189 ' ) '||
190 ' ) '||
191 ' AND organization_id = '||obj_name||'.organization_id '||
192 ' AND (other_organization_id IS NULL '||
193 ' OR EXISTS ( SELECT NULL '||
194 ' FROM org_access a3 '||
195 ' WHERE a3.organization_id = sp.other_organization_id '||
196 ' AND NVL(a3.disable_date, SYSDATE+1) >= SYSDATE '||
197 ' AND a3.resp_application_id = fnd_global.resp_appl_id '||
198 ' AND a3.responsibility_id = fnd_global.resp_id '||
199 ' ) '||
200 ' OR NOT EXISTS ( SELECT NULL '||
201 ' FROM org_access a4 '||
202 ' WHERE a4.organization_id = sp.other_organization_id '||
203 ' AND NVL(a4.disable_date, SYSDATE+1) >=SYSDATE '||
204 ' ) '||
205 ' ) '||
206 ' ) ';
207
208 RETURN f_predicate;
209 END secure_formula_dtl_ins;
210
211 -- =======================================================================================
212 -- USAGE
213 -- Used for update to the following tables: fm_form_mst_b
214 -- HISTORY
215 -- =======================================================================================
216 FUNCTION secure_formula_upd (obj_schema IN VARCHAR2,
217 obj_name IN VARCHAR2) RETURN VARCHAR2 IS
218 f_predicate VARCHAR2(4000);
219 v_resp_id NUMBER; /* Added in Bug No.8355449 */
220
221 BEGIN
222 /* Bug No.8355449 - Start*/
223 -- Responsibility 'Product Development Security Profile Manager'
224 -- has full access to all formulas
225 --
226 SELECT responsibility_id INTO v_resp_id
227 FROM fnd_responsibility
228 WHERE responsibility_key = 'GMD_SECURITY_PROFILE_MGR';
229
230 -- Context m_fs_context, variable pc_ind with value 'Yes'
231 -- allows Planning and Costing enginees to have access to all formulas.
232 --
233 IF SYS_CONTEXT('m_fs_context','pc_ind') = 'Yes' or v_resp_id = fnd_global.resp_id
234 THEN
235 f_predicate := '1=1';
236 /* Bug No.8355449 - End */
237 ELSE
238 f_predicate := ' EXISTS ' ||
239 ' ( SELECT 1 '||
240 ' FROM gmd_security_profiles sp '||
241 ' WHERE sp.assign_method_ind IN ( ''A'') '|| --,''M'') '|| /* Bug No.9077438 */
242 ' AND sp.access_type_ind = ''U'' '||
243 ' AND (responsibility_id IN ( SELECT rg.responsibility_id '||
244 ' FROM FND_USER_RESP_GROUPS rg '||
245 ' WHERE rg.user_id = fnd_global.user_id '||
246 ' AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date, SYSDATE) '||
247 ' ) '||
248 ' OR ( sp.user_id = fnd_profile.value_specific(''GMD_DEFAULT_USER'') '||
252 ' AND NVL(responsibility_id,fnd_global.resp_id) = fnd_global.resp_id '|| /* Bug No.9077438 */
249 ' OR sp.user_id = fnd_global.user_id '||
250 ' ) '||
251 ' ) '||
253 ' AND ( EXISTS ( SELECT NULL '||
254 ' FROM org_access a1 '||
255 ' WHERE ( ( sp.organization_id = a1.organization_id '||
256 ' AND sp.other_organization_id IS NULL '||
257 ' ) '||
258 ' OR sp.other_organization_id = a1.organization_id '||
259 ' ) '||
260 ' AND NVL(a1.disable_date, SYSDATE+1) >= SYSDATE '||
261 ' AND a1.resp_application_id = fnd_global.resp_appl_id '||
262 ' AND a1.responsibility_id = fnd_global.resp_id '||
263 ' ) '||
264 ' OR '||
265 ' NOT EXISTS ( SELECT NULL '||
266 ' FROM org_access a2 '||
267 ' WHERE ( ( sp.organization_id = a2.organization_id '||
268 ' AND sp.other_organization_id IS NULL '||
269 ' ) '||
270 ' OR sp.other_organization_id = a2.organization_id '||
271 ' ) '||
272 ' AND NVL(a2.disable_date, SYSDATE+1) >=SYSDATE '||
273 ' ) '||
274 ' ) '||
275 ' AND sp.organization_id = '||obj_name||'.owner_organization_id '||
276 ' ) '||
277 ' OR '||
278 ' EXISTS '||
279 ' ( SELECT 1 '||
280 ' FROM gmd_formula_security fs '||
281 ' WHERE fs.access_type_ind = ''U'' '||
282 ' AND NVL(responsibility_id,fnd_global.resp_id) = fnd_global.resp_id '|| /* Bug No.9077438 */
283 ' AND ( ( fs.user_id = fnd_profile.value(''GMD_DEFAULT_USER'') '||
284 ' OR fs.user_id = fnd_global.user_id '||
285 ' ) '||
286 ' OR ( EXISTS ( SELECT rg.responsibility_id '||
287 ' FROM FND_USER_RESP_GROUPS rg '||
288 ' WHERE rg.user_id = fnd_global.user_id '||
289 ' AND fs.responsibility_id = rg.responsibility_id '||
290 ' AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date, SYSDATE) '||
291 ' ) '||
292 ' ) '||
293 ' ) '||
294 ' AND ( EXISTS ( SELECT NULL '||
295 ' FROM org_access ou '||
296 ' WHERE ( ( fs.organization_id = ou.organization_id '||
297 ' AND fs.other_organization_id IS NULL '||
298 ' ) '||
299 ' OR fs.other_organization_id = ou.organization_id '||
300 ' ) '||
301 ' AND NVL(ou.disable_date, SYSDATE+1) >= SYSDATE '||
302 ' AND ou.resp_application_id = fnd_global.resp_appl_id '||
303 ' AND ou.responsibility_id = fnd_global.resp_id '||
304 ' ) '||
305 ' OR '||
306 ' NOT EXISTS ( SELECT NULL '||
307 ' FROM org_access ou1 '||
308 ' WHERE ( ( ou1.organization_id = fs.organization_id '||
309 ' AND fs.other_organization_id IS NULL '||
310 ' ) '||
311 ' OR ou1.organization_id = fs.other_organization_id '||
312 ' ) '||
313 ' AND NVL(ou1.disable_date, SYSDATE+1) >=SYSDATE '||
314 ' ) '||
315 ' ) '||
316 ' AND fs.formula_id = '||obj_name||'.formula_id '||
317 ' ) ';
318 END IF;
319 RETURN f_predicate;
320 END secure_formula_upd;
321
322 -- =======================================================================================
323 -- USAGE
324 -- Used for update to the following tables: fm_matl_dtl
325 -- HISTORY
326 -- =======================================================================================
327 FUNCTION secure_formula_dtl_sel (obj_schema IN VARCHAR2,
328 obj_name IN VARCHAR2) RETURN VARCHAR2 IS
329 f_predicate VARCHAR2(4000);
330 BEGIN
331 --
335 IF SYS_CONTEXT('m_fs_context','pc_ind') = 'Yes' THEN
332 -- Context m_fs_context, variable pc_ind with value 'Yes'
333 -- allows Planning and Costing enginees to have access to all formulas.
334 --
336 f_predicate := '1=1';
337 ELSE
338 f_predicate := ' EXISTS ( SELECT 1 '||
339 ' FROM fm_form_mst_b fm WHERE fm.formula_id = ' ||obj_name||'.formula_id )';
340 END IF;
341 RETURN f_predicate;
342 END secure_formula_dtl_sel;
343
344 -- =======================================================================================
345 -- USAGE
346 -- Used for selection and update of the following tables: gmd_recipe_step_materials
347 -- gmd_recipes_validity_rules
348 -- HISTORY
349 -- =======================================================================================
350
351 FUNCTION secure_recipe_sel (obj_schema IN VARCHAR2,
352 obj_name IN VARCHAR2) RETURN VARCHAR2 IS
353
354 f_predicate VARCHAR2(4000);
355 BEGIN
356 If SYS_CONTEXT('m_fs_context','pc_ind') = 'Yes'
357 THEN
358 f_predicate := '1=1';
359
360 ELSE
361 f_predicate := ' EXISTS (SELECT 1 FROM gmd_recipes_b WHERE recipe_id = '||obj_name||'.recipe_id)';
362 END IF;
363 RETURN f_predicate;
364 END secure_recipe_sel;
365
366 end gmd_security_policy;
367
368