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