1 PACKAGE AMW_VIOLATION_PVT AS
2 /* $Header: amwvvlas.pls 120.13.12000000.1 2007/01/16 20:45:53 appldev ship $ */
3
4
5 -- ===============================================================
6 -- Package name
7 -- AMW_VIOLATION_PVT
8 -- Purpose
9 --
10 -- History
11 -- 06/01/2005 tsho Create
12 -- ===============================================================
13
14 TYPE G_NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
15 TYPE G_VARCHAR2_LONG_TABLE is table of VARCHAR2(320) INDEX BY BINARY_INTEGER;
16 TYPE G_VARCHAR2_CODE_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
17 TYPE G_VARCHAR2_HASHTABLE IS TABLE OF VARCHAR2(400) INDEX BY VARCHAR2(64);
18
19 -- FND_GLOBAL
20 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
21 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
22
23 -- AMW table/view name
24 G_AMW_USER CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_USER');
25 G_AMW_USER_RESP_GROUPS CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_USER_RESP_GROUPS');
26 G_AMW_RESPONSIBILITY CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_RESPONSIBILITY');
27 G_AMW_RESP_FUNCTIONS CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_RESP_FUNCTIONS');
28 G_AMW_MENUS CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_MENUS');
29 G_AMW_MENU_ENTRIES CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_MENU_ENTRIES');
30 G_AMW_FORM_FUNCTIONS_VL CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_FORM_FUNCTIONS_VL');
31 G_AMW_COMPILED_MENU_FUNCTIONS CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_COMPILED_MENU_FUNCTIONS');
32 G_AMW_GRANTS CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_GRANTS');
33 G_AMW_USER_ROLES CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_USER_ROLES');
34 G_AMW_USER_ROLE_ASSIGNMENTS CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_USER_ROLE_ASSIGNMENTS');
35 G_AMW_REQUEST_GROUP_UNITS CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_REQUEST_GROUP_UNITS');
36 G_AMW_CONCURRENT_PROGRAMS_VL CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_CONCURRENT_PROGRAMS_VL');
37 G_AMW_ALL_ROLES_VL CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_ALL_ROLES_VL');
38 G_AMW_RESPONSIBILITY_VL CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_RESPONSIBILITY_VL');
39 G_AMW_MENUS_VL CONSTANT VARCHAR2(30) := FND_PROFILE.VALUE('AMW_MENUS_VL');
40 G_AMW_USER_ROLE_ASSIGNMENTS_V CONSTANT VARCHAR2(30) := 'WF_USER_ROLE_ASSIGNMENTS_V';
41
42 -- ===============================================================
43 -- Function name
44 -- Is_ICM_Installed
45 --
46 -- Purpose
47 -- check to see if ICM is installed or not
48 -- Params
49 --
50 -- Return
51 -- 'Y' := ICM is installed
52 -- 'N' := ICM is not installed
53 -- History
54 -- 07/19/2005 tsho Create
55 -- ===============================================================
56 Function Is_ICM_Installed
57 RETURN VARCHAR2;
58
59
60 -- ===============================================================
61 -- Procedure name
62 -- Has_Violations
63 --
64 -- Purpose
65 -- check for OICM SOD constriants that will be violated
66 -- if the user is assigned these additional roles as well as inherited roles
67 -- Params
68 -- p_user_id := input fnd user_id
69 -- p_role_names := input a list of new roles
70 -- p_revoked_role_names := input a list of revoked roles
71 -- p_mode := input check mode ('ADMIN', 'APPROVE', 'SUBS')
72 -- x_violat_region := output full path dialog region name to display potential violation detials.
73 -- (ie. /oracle/apps/amw/audit/duty/webui/....RN)
74 -- x_violat_btn_region := output full path dialog button region name to display page level buttons.
75 -- (ie. /oracle/apps/amw/audit/duty/webui/....RN)
76 -- this button region is different depending on the override privilege of Administrator
77 -- x_has_violation := output 'Y' if this user will have violations with the new roles assigned; output 'N' otherwise.
78 --
79 -- History
80 -- 06/01/2005 tsho Create
81 -- 08/03/2005 tsho Consider User Waivers
82 -- 08/01/2006 dliao Support revoked roles
83 -- ===============================================================
84 Procedure Has_Violations (
85 p_user_id IN NUMBER,
86 p_role_names IN JTF_VARCHAR2_TABLE_400,
87 p_revoked_role_names IN JTF_VARCHAR2_TABLE_400,
88 p_mode IN VARCHAR2,
89 x_violat_region OUT NOCOPY VARCHAR2,
90 x_violat_btn_region OUT NOCOPY VARCHAR2,
91 x_has_violation OUT NOCOPY VARCHAR2,
92 x_new_resp_name OUT NOCOPY VARCHAR2,
93 x_existing_resp_name OUT NOCOPY VARCHAR2,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2
97 );
98
99
100
101 -- ===============================================================
102 -- Procedure name
103 -- Has_Violations
104 -- Obsolated due to the bug 5407266
105 -- History
106 -- 06/01/2005 tsho Create
107 -- 08/03/2005 tsho Consider User Waivers
108 -- 08/01/2006 dliao obsolated
109 -- ===============================================================
110 Procedure Has_Violations (
111 p_user_id IN NUMBER,
112 p_role_names IN JTF_VARCHAR2_TABLE_400,
113 p_mode IN VARCHAR2,
114 x_violat_region OUT NOCOPY VARCHAR2,
115 x_violat_btn_region OUT NOCOPY VARCHAR2,
116 x_has_violation OUT NOCOPY VARCHAR2,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2
120 );
121
122
123 -- ===============================================================
124 -- Procedure name
125 -- Has_Violations_For_Mode
126 --
127 -- Purpose
128 -- check for OICM SOD constriants that will be violated
129 -- if the user is assigned these additional roles as well as inherited roles
130 -- Params
131 -- p_user_id := input fnd user_id
132 -- p_role_names := input a list of new roles
133 -- p_mode := input check mode ('ADMIN', 'APPROVE', 'SUBS')
134 -- x_violat_hashtable := This API will put return parameters in a Associate Table format,
135 -- it at least contains the following key/value pairs:
136 -- HasViolation : 'Y' or 'N' to indicate if introducing violations when trying to add those roles to the user
137 -- ViolationDetail : the OAFunc/Region containing violation details for the user , mainly used for Notification.
138 --
139 -- History
140 -- 07/19/2005 tsho Create
141 -- ===============================================================
142 Procedure Has_Violations_For_Mode (
143 p_user_id IN NUMBER,
144 p_role_names IN JTF_VARCHAR2_TABLE_400,
145 p_mode IN VARCHAR2,
146 x_violat_hashtable OUT NOCOPY G_VARCHAR2_HASHTABLE
147 );
148
149
150 -- ===============================================================
151 -- Function name
152 -- Has_Violation_Due_To_Resp
153 --
154 -- Purpose
155 -- check for OICM SOD constriants that will be violated
156 -- if the user is assigned the additional responsibility
157 -- Params
158 -- p_user_id := input fnd user_id
159 -- p_responsibility_id := input fnd responsibility_id
160 -- Return
161 -- 'N' := if no SOD violation found.
162 -- 'Y' := if SOD violation exists.
163 -- The SOD violation should NOT be restricted to
164 -- only the new responsiblity.
165 -- If the existing responsibilities have any violations,
166 -- the function should return 'Y' as well.
167 --
168 -- History
169 -- 07/13/2005 tsho Create
170 -- 08/03/2005 tsho Consider User Waivers
171 -- ===============================================================
172 Function Has_Violation_Due_To_Resp (
173 p_user_id IN NUMBER,
174 p_responsibility_id IN NUMBER
175 ) return VARCHAR2;
176
177
178 -- ===============================================================
179 -- Procedure name
180 -- Update_Role_Constraint_Denorm
181 --
182 -- Purpose
183 -- populate AMW_ROLE_CONSTRAINT_DENORM table
184 -- Params
185 -- p_constraint_rev_id := input constraint_rev_id (Default is NULL)
186 -- if p_constraint_rev_id is specified, only update/create
187 -- the corresponding role/resp with that constraint.
188 --
189 -- History
190 -- 07/14/2005 tsho Create
191 -- 08/03/2005 tsho Consider Responsibility Waivers, leave User Waiver check to the run-time
192 -- ===============================================================
193 Procedure Update_Role_Constraint_Denorm (
194 errbuf OUT NOCOPY VARCHAR2,
195 retcode OUT NOCOPY VARCHAR2,
196 p_constraint_rev_id IN NUMBER := NULL
197 );
198
199
200
201 -- ===============================================================
202 -- Function name
203 -- Get_Violat_New_Role_List
204 --
205 -- Purpose
206 -- get a flat string list of new role display name, which together with this user's
207 -- exisiting role/resp , or together with those new assigned role(among p_role_names_string)
208 -- may violate the specified constraint
209 --
210 -- Params
211 -- p_user_id := input fnd user_id
212 -- p_constraint_rev_id := input constraint_rev_id
213 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
214 -- p_new_role_names_string := input a string list of new roles assigning to this user,
215 -- the role_name is seperated by ','
216 --
217 -- Return
218 -- a string list of role display names which violates the specified constraint,
219 -- each display name is seperated by ','
220 --
221 -- History
222 -- 07/27/2005 tsho Create
223 -- ===============================================================
224 Function Get_Violat_New_Role_List (
225 p_user_id IN NUMBER,
226 p_constraint_rev_id IN NUMBER,
227 p_constraint_type_code IN VARCHAR2,
228 p_new_role_names_string IN VARCHAR2
229 ) RETURN VARCHAR2;
230
231
232 -- ===============================================================
233 -- Function name
234 -- Get_Violat_Existing_Role_List
235 --
236 -- Purpose
237 -- get a flat string list of this user's existing role display name, together with those new assigned role(among p_role_names_string)
238 -- may violate the specified constraint
239 --
240 -- Params
241 -- p_user_id := input fnd user_id
242 -- p_constraint_rev_id := input constraint_rev_id
243 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
244 --
245 -- Return
246 -- a string list of role display names which violates the specified constraint,
247 -- each display name is seperated by ','
248 --
249 -- History
250 -- 07/27/2005 tsho Create
251 -- ===============================================================
252 Function Get_Violat_Existing_Role_List (
253 p_user_id IN NUMBER,
254 p_constraint_rev_id IN NUMBER,
255 p_constraint_type_code IN VARCHAR2
256 ) RETURN VARCHAR2;
257
258
259 -- ===============================================================
260 -- Function name
261 -- Get_Violat_Existing_Resp_List
262 --
263 -- Purpose
264 -- get a flat string list of this user's existing responsibility display name, together with those new assigned role(among p_role_names_string)
265 -- may violate the specified constraint
266 --
267 -- Params
268 -- p_user_id := input fnd user_id
269 -- p_constraint_rev_id := input constraint_rev_id
270 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
271 --
272 -- Return
273 -- a string list of role display names which violates the specified constraint,
274 -- each display name is seperated by ','
275 --
276 -- History
277 -- 07/27/2005 tsho Create
278 -- ===============================================================
279 Function Get_Violat_Existing_Resp_List (
280 p_user_id IN NUMBER,
281 p_constraint_rev_id IN NUMBER,
282 p_constraint_type_code IN VARCHAR2
283 ) RETURN VARCHAR2;
284
285
286 -- ===============================================================
287 -- Function name
288 -- Get_Violat_Existing_Menu_List
289 --
290 -- Purpose
291 -- get a flat string list of this user's existing permission set(menu) display name, ]
292 -- together with those new assigned role(among p_role_names_string)
293 -- may violate the specified constraint
294 --
295 -- Params
296 -- p_user_id := input fnd user_id
297 -- p_constraint_rev_id := input constraint_rev_id
298 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
299 --
300 -- Return
301 -- a string list of role display names which violates the specified constraint,
302 -- each display name is seperated by ','
303 --
304 -- History
305 -- 07/27/2005 tsho Create
306 -- ===============================================================
307 Function Get_Violat_Existing_Menu_List (
308 p_user_id IN NUMBER,
309 p_constraint_rev_id IN NUMBER,
310 p_constraint_type_code IN VARCHAR2
311 ) RETURN VARCHAR2;
312
313
314 -- ===============================================================
315 -- Function name
316 -- Get_Violat_Comments
320 --
317 --
318 -- Purpose
319 -- get comments(instruction) for specified constraint_rev_id
321 -- Params
322 -- p_constraint_rev_id := input constraint_rev_id
323 -- p_constraint_type_code := input constraint type for p_constraint_rev_id
324 --
325 -- Return
326 -- a seeded mesg
327 --
328 -- History
329 -- 07/27/2005 tsho Create
330 -- ===============================================================
331 Function Get_Violat_Comments (
332 p_constraint_rev_id IN NUMBER,
333 p_constraint_type_code IN VARCHAR2
334 ) RETURN VARCHAR2;
335
336
337
338 -- ===============================================================
339 -- Function name
340 -- Do_On_Role_Assigned
341 --
342 -- Purpose
343 -- listen to the worflow business event(mainly uses for oracle.apps.fnd.wf.ds.userRole.created)
344 -- and do corresponding actions
345 --
346 -- Params
347 -- p_subscription_guid
348 -- p_event
349 --
350 -- Return
351 -- 'SUCCESS' | 'ERROR'
352 --
353 -- History
354 -- 07/29/2005 tsho Create
355 -- ===============================================================
356 FUNCTION Do_On_Role_Assigned (
357 p_subscription_guid in raw,
358 p_event in out NOCOPY wf_event_t
359 ) return VARCHAR2;
360
361
362 -- ===============================================================
363 -- Procedure name
364 -- Send_Notif_To_Affected_Process
365 --
366 -- Purpose
367 -- send violation notification to affected process owners
368 -- it'll find which constraints have been violated due to the user role assignment
369 -- and send notification to each process owner of those constraints
370 -- Params
371 -- p_item_type := worflow template (default : AMWNOTIF)
372 -- p_message_name := workflow mesg template (default : MWVIOLATUSERROLENOTIF)
373 -- p_user_name := the user who got the role assigned
374 -- p_role_name := the new role which is assigned to this user
375 -- p_assigned_date := the assigned date
376 -- p_assigned_by_id := the role is assigned by which user (user_id)
377 --
378 -- History
379 -- 07/29/2005 tsho Create
380 -- 02/23/2006 psomanat removied the parameter p_assigned_date
381 -- 02/23/2006 psomanat removied the parameter p_user_name
382 -- 02/23/2006 psomanat added the parameter p_user_id
383 -- ============================================================================
384 Procedure Send_Notif_To_Affected_Process(
385 p_item_type IN VARCHAR2 := 'AMWNOTIF',
386 p_message_name IN VARCHAR2 := 'VIOLATIONNOTIF',
387 p_user_id IN NUMBER,
388 p_role_name IN VARCHAR2,
389 p_assigned_by_id IN NUMBER
390 );
391
392 -- ===============================================================
393 -- Procedure name
394 -- Send_Notif_To_Process_Owner
395 --
396 -- Purpose
397 -- send violation notification to specified process owner
398 -- Params
399 -- p_item_type := worflow template (default : AMWNOTIF)
400 -- p_message_name := workflow mesg template (default : MWVIOLATUSERROLENOTIF)
401 -- p_user_name := the user who got the role assigned
402 -- p_role_name := the new role which is assigned to this user
403 -- p_assigned_date := the assigned date
404 -- p_assigned_by_id := the role is assigned by which user (user_id)
405 -- p_constraint_rev_id := which constraint has been violated
406 -- p_process_owner_id := the process owner of that constraint, to whom this notif will be sent
407 --
408 -- History
409 -- 07/29/2005 tsho Create
410 -- 02/23/2006 psomanat removied the parameter p_assigned_date
411 -- 02/23/2006 psomanat removied the parameter p_user_name
412 -- 02/23/2006 psomanat added the parameter p_user_id
413 -- ===============================================================
414 Procedure Send_Notif_To_Process_Owner(
415 p_item_type IN VARCHAR2 := 'AMWNOTIF',
416 p_message_name IN VARCHAR2 := 'VIOLATIONNOTIF',
417 p_user_id IN NUMBER,
418 p_role_name IN VARCHAR2,
419 p_assigned_by_id IN NUMBER,
420 p_constraint_rev_id IN NUMBER,
421 p_process_owner_id IN NUMBER,
422 x_return_status OUT NOCOPY VARCHAR2
423 );
424
425
426 -- ===============================================================
427 -- Function name
428 -- Violation_Detail_Due_To_Resp
429 --
430 -- Purpose
431 -- check for OICM SOD constriants that will be violated
432 -- if the user is assigned the additional responsibility
433 -- Params
434 -- p_user_id := input fnd user_id
435 -- p_responsibility_id := input fnd responsibility_id
436 -- Return
437 -- 'N' := if no SOD violation found.
438 -- 'Resp_name1, Resp_name2...' := if SOD violation exists.
439 -- The SOD violation should NOT be restricted to
440 -- only the new responsiblity.
444 -- History
441 -- If the existing responsibilities have any violations,
442 -- the function should return 'Y' as well.
443 --
445 -- 08/01/2005 tsho Create
446 -- 08/03/2005 tsho Consider User Waivers
447 -- ===============================================================
448 Function Violation_Detail_Due_To_Resp (
449 p_user_id IN NUMBER,
450 p_responsibility_id IN NUMBER
451 ) return VARCHAR2;
452
453
454 /*
455 * cpetriuc
456 * -------------
457 * MENU_VIOLATES
458 * -------------
459 * Checks if the menu provided as argument violates any SOD (Segregation of Duties)
460 * constraints. If a constraint is violated, the function returns an error message
461 * containing the name of the violated constraint together with the list of functions
462 * that define the constraint. Otherwise, the function returns 'N'.
463 */
464 function MENU_VIOLATES(p_menu_id NUMBER) return VARCHAR2;
465
466
467 /*
468 * cpetriuc
469 * ----------------------
470 * FUNCTION_VIOLATES_MENU
471 * ----------------------
472 * Checks if any SOD (Segregation of Duties) constraints would be violated if the
473 * argument function would be added to the menu provided as argument. If a constraint
474 * would be violated, the function returns an error message containing the name of the
475 * potentially violated constraint together with the list of functions that define the
476 * constraint. Otherwise, the function returns 'N'.
477 */
478 function FUNCTION_VIOLATES_MENU(p_menu_id NUMBER, p_function_id NUMBER) return VARCHAR2;
479
480
481 -- ----------------------------------------------------------------------
482 END AMW_VIOLATION_PVT;
[Home] [Help]