[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ROLES_PUB
Source
1 PACKAGE BODY ENG_CHANGE_ROLES_PUB AS
2 /* $Header: ENGCMRLB.pls 120.1 2005/11/28 02:32:15 sdarbha noship $ */
3
4 -------------------------------------------
5 -- Type : Private
6 -- Procedure to get the Default access. Returns default access as follows
7 -- Administrator => Edit and View privileges
8 -- Reader => View privilege
9 -- Discoverer => None among Edit or View privileges
10
11 PROCEDURE Get_Default_Access(p_menu_id IN NUMBER, p_default_access OUT NOCOPY VARCHAR2)
12 IS
13 v_edit_priv NUMBER;
14 v_view_priv NUMBER;
15 BEGIN
16 v_edit_priv := 0;
17 v_view_priv := 0;
18
19 SELECT count(function_name) into v_edit_priv
20 FROM fnd_form_functions
21 WHERE function_name='ENG_EDIT_CHANGE' AND
22 function_id IN (SELECT function_id FROM fnd_menu_entries WHERE menu_id = p_menu_id);
23
24 SELECT count(function_name) into v_view_priv
25 FROM fnd_form_functions
26 WHERE function_name='ENG_VIEW_CHANGE' AND
27 function_id IN (SELECT function_id FROM fnd_menu_entries WHERE menu_id = p_menu_id);
28
29 -- If the role has Edit privilege then return Administrator
30 IF v_edit_priv > 0 OR (v_edit_priv > 0 AND v_view_priv > 0) THEN
31 p_default_access := 'Administrator';
32 END IF;
33
34 -- If the role has View Privilege then return Reader
35 IF v_view_priv > 0 and v_edit_priv = 0 THEN
36 p_default_access := 'Reader';
37 END IF;
38
39 -- If the role has none then return Discoverer
40 IF v_view_priv = 0 and v_edit_priv = 0 THEN
41 p_default_access := 'Discoverer';
42 END IF;
43
44 END Get_Default_Access;
45 -------------------------------------------
46 /*
47 -- Procedure to print a big line which exceeds 255 chars.
48 -- It breaks up the line and displays.
49 procedure p( p_string in varchar2 )
50 is
51 l_string long default p_string;
52 begin
53 loop
54 exit when l_string is null;
55 dbms_output.put_line( substr( l_string, 1, 250 ) );
56 l_string := substr( l_string, 251 );
57 end loop;
58 end p;
59 */
60 --------------------------------------------------------
61
62
63
64 -- API name : Get_Change_Users
65 -- Type : Public
66 -- Pre-reqs : None
67 -- Function : Gets list of users who has some role on change object.
68 -- If no role is passed i.e if p_role_name is null
69 -- then the API returns list of roles available on the item
70 -- with the set of users who bear each role.
71 -- If some role is mentioned, then the list of user who bear
72 -- this role on the item are displayed.
73 -- IN : p_api_version IN NUMBER Required
74 -- p_entity_name IN VARCHAR2 Required
75 -- Version: Current Version 1.0
76 -- Previous Version : 1.0
77 -- Notes :
78
79 PROCEDURE Get_Change_Users
80 (
81 p_api_version IN NUMBER,
82 p_entity_name IN VARCHAR2,
83 p_pk1_value IN VARCHAR2,
84 p_pk2_value IN VARCHAR2,
85 p_pk3_value IN VARCHAR2,
86 p_pk4_value IN VARCHAR2,
87 p_pk5_value IN VARCHAR2,
88 p_role_name IN VARCHAR2 DEFAULT NULL,
89 x_grantee_names OUT NOCOPY FND_TABLE_OF_VARCHAR2_120,
90 x_grantee_types OUT NOCOPY FND_TABLE_OF_VARCHAR2_30,
91 x_role_names OUT NOCOPY FND_TABLE_OF_VARCHAR2_30,
92 x_role_display_names OUT NOCOPY FND_TABLE_OF_VARCHAR2_120,
93 x_default_access OUT NOCOPY FND_TABLE_OF_VARCHAR2_30,
94 x_return_status OUT NOCOPY VARCHAR2
95 )
96 AS
97
98 l_entity_name varchar2(30);
99 l_pk1_value varchar2(50);
100 l_pk2_value varchar2(50);
101 l_pk3_value varchar2(50);
102 l_pk4_value varchar2(50);
103 l_pk5_value varchar2(50);
104 l_role_name varchar2(100);
105
106 l_inst_set_ids varchar2(32767);
107 l_obj_ids varchar2(32767);
108 query_to_exec varchar2(32767);
109 cursor_select NUMBER;
110 cursor_execute NUMBER;
111 all_roles_count NUMBER;
112 l_change_has_items BOOLEAN;
113
114 -- Cursor to get the direct roles on the change object
115 CURSOR change_users_cur (cp_entity_name VARCHAR2,
116 cp_pk1_value VARCHAR2,
117 cp_pk2_value VARCHAR2,
118 cp_pk3_value VARCHAR2,
119 cp_pk4_value VARCHAR2,
120 cp_pk5_value VARCHAR2,
121 cp_role_name VARCHAR2)
122 IS
123 SELECT
124 parties.party_name grantee_name,
125 grantee_type,
126 role_name,
127 role_display_name,
128 menu_id
129 FROM hz_parties parties,
130 (
131 SELECT DISTINCT
132 GRANTS.grantee_orig_system_id grantee_orig_system_id,
133 DECODE(grants.grantee_key,'GLOBAL','HZ_GLOBAL', SUBSTR(grants.grantee_key,0, INSTR(grants.grantee_key,':',1,1)-1)) grantee_orig_system,
134 GRANTS.grantee_type grantee_type,
135 menus.Menu_name role_name,
136 menus.user_menu_name role_display_name,
137 grants.grantee_key,
138 menus.menu_id menu_id
139 FROM fnd_grants GRANTS,
140 fnd_objects OBJECTS,
141 fnd_menus_vl MENUS
142 WHERE OBJECTS.object_id = GRANTS.object_id
143 AND OBJECTS.obj_name=cp_entity_name
144 AND GRANTS.instance_type='INSTANCE'
145 AND GRANTS.instance_pk1_value=cp_pk1_value
146 AND
147 (( grants.instance_pk2_value = cp_pk2_value
148 ) OR (( grants.instance_pk2_value = '*NULL*')
149 AND ( cp_pk2_value is NULL)
150 ))
151 AND
152 (( grants.instance_pk3_value = cp_pk3_value
153 ) OR (( grants.instance_pk3_value = '*NULL*')
154 AND ( cp_pk3_value is NULL)
155 ))
156 AND
157 (( grants.instance_pk4_value = cp_pk4_value
158 ) OR (( grants.instance_pk4_value = '*NULL*')
159 AND ( cp_pk4_value is NULL)
160 ))
161 AND
162 (( grants.instance_pk5_value = cp_pk5_value
163 ) OR (( grants.instance_pk5_value = '*NULL*')
164 AND ( cp_pk5_value is NULL)
165 ))
166 AND GRANTS.menu_id = MENUS.menu_id
167 AND
168 (( cp_role_name is null AND menus.menu_name like '%'
169 ) OR ( MENUS.MENU_NAME in (cp_role_name)
170 ))
171 AND GRANTS.start_date <= sysdate
172 AND
173 (
174 GRANTS.end_date is null
175 OR grants.end_date >= SYSDATE
176 )
177 ) grants -- End of First From clause
178 WHERE grantee_type in ('USER','GROUP') --,'COMPANY','GLOBAL')
179 AND grantee_orig_system in ('HZ_PARTY','HZ_GROUP') --,'HZ_COMPANY', 'HZ_GLOBAL')
180 AND parties.party_id=DECODE(grants.grantee_key,'GLOBAL',-1000, SUBSTR(grants.grantee_key, INSTR(grants.grantee_key,':',1,1)+1));
181
182 CURSOR change_subjects_cur is
183 SELECT pk1_value, pk2_value FROM eng_change_subjects where change_id = p_pk1_value;
184
185 CURSOR change_rev_items_cur is
186 SELECT revised_item_id, organization_id FROM eng_revised_items WHERE change_id = p_pk1_value; -- p_pk1_value = change_id
187
188 l_grantee_list GRANTEES_TBL_TYPE;
189 l_index NUMBER;
190 l_revised_item_id NUMBER;
191 l_organization_id NUMBER;
192 l_temp_menu_id NUMBER;
193 l_default_access VARCHAR2(30);
194
195 l_item_id_and_org_id_sub change_subjects_cur%ROWTYPE;
196 l_item_id_and_org_id_rev change_rev_items_cur%ROWTYPE;
197
198
199 BEGIN
200 --dbms_output.enable(1000000);
201 --dbms_output.put_line('** sameer ** inside ENG_CHANGE_ROLES_PUB.Get_Change_Users ...');
202
203 l_revised_item_id := 0;
204 l_organization_id := 0;
205 l_index := 0;
206 l_change_has_items := FALSE;
207 l_temp_menu_id := 0;
208 l_default_access := 'Administrator';
209
210 -- check whether there are any items in subjects or revised items
211 OPEN change_subjects_cur;
212 LOOP
213 FETCH change_subjects_cur into l_item_id_and_org_id_sub;
214 EXIT WHEN change_subjects_cur%NOTFOUND;
215 --dbms_output.put_line('** sameer ** change_subjects_cursor returned : ' || l_item_id_and_org_id_sub.pk1_value || ' , '
216 -- || l_item_id_and_org_id_sub.pk2_value);
217
218 l_revised_item_id := l_item_id_and_org_id_sub.pk1_value;
219 l_organization_id := l_item_id_and_org_id_sub.pk2_value;
220 END LOOP;
221 CLOSE change_subjects_cur;
222
223 IF l_revised_item_id = 0 OR l_revised_item_id is null then
224 OPEN change_rev_items_cur;
225 LOOP
226 FETCH change_rev_items_cur into l_item_id_and_org_id_rev;
227 EXIT WHEN change_rev_items_cur%NOTFOUND;
228 --dbms_output.put_line('** sameer ** change_rev_items_currsor returned : ' || l_item_id_and_org_id_rev.revised_item_id || ' , '
229 -- || l_item_id_and_org_id_rev.organization_id);
230
231 l_revised_item_id := l_item_id_and_org_id_rev.revised_item_id;
232 l_organization_id := l_item_id_and_org_id_rev.organization_id;
233 END LOOP;
234 CLOSE change_rev_items_cur;
235 END IF;
236
237 --l_obj_ids := p_pk1_value ;--|| ',' || p_pk2_value;
238 l_obj_ids := l_revised_item_id || ',' || l_organization_id;
239
240 IF l_revised_item_id is not null and l_organization_id is not null THEN
241 l_change_has_items := TRUE; -- there is an item associated with this change at subject or revised item level.
242 END IF;
243
244 --dbms_output.put_line('** sameer ** l_obj_ids after direct sqls .. : ' || l_obj_ids);
245
246 x_return_status:='T';
247 l_entity_name:=p_entity_name;
248 l_pk1_value:=p_pk1_value;
249 l_pk2_value:=p_pk2_value;
250 l_pk3_value:=p_pk3_value;
251 l_pk4_value:=p_pk4_value;
252 l_pk5_value:=p_pk5_value;
253 l_role_name:=p_role_name;
254 all_roles_count := 0;
255
256 --dbms_output.put_line('** sameer ** looping change_users_cur ...');
257
258 -- First, fetch the direct roles on the change object
259 FOR rec IN change_users_cur(
260 l_entity_name,
261 l_pk1_value,
262 l_pk2_value,
263 l_pk3_value,
264 l_pk4_value,
265 l_pk5_value,
266 l_role_name)
267 LOOP
268 --dbms_output.put_line('** sameer ** in loop of change_users_cur ...' || rec.grantee_name);
269 l_grantee_list(l_index).grantee_name:=rec.grantee_name;
270 l_grantee_list(l_index).grantee_type:=rec.grantee_type;
271 l_grantee_list(l_index).role_name:=rec.role_name;
272 l_grantee_list(l_index).role_display_name:=rec.role_display_name;
273 l_temp_menu_id := rec.menu_id;
274
275 -- Get the default access for this role
276 Get_Default_Access(p_menu_id => l_temp_menu_id,
277 p_default_access => l_default_access
278 );
279
280 l_grantee_list(l_index).default_access := l_default_access;
281 l_index:=l_index+1;
282 END LOOP;
283
284 x_grantee_names := FND_TABLE_OF_VARCHAR2_120();
285 x_grantee_types := FND_TABLE_OF_VARCHAR2_30();
286 x_role_names := FND_TABLE_OF_VARCHAR2_30();
287 x_role_display_names := FND_TABLE_OF_VARCHAR2_120();
288 x_default_access := FND_TABLE_OF_VARCHAR2_30();
289
290 IF( l_grantee_list.count>0) THEN
291 x_grantee_names.extend(l_grantee_list.count);
292 x_grantee_types.extend(l_grantee_list.count);
293 x_role_names.extend(l_grantee_list.count);
294 x_role_display_names.extend(l_grantee_list.count);
295 x_default_access.extend(l_grantee_list.count);
296
297 FOR i in l_grantee_list.first .. l_grantee_list.last LOOP
298 all_roles_count := all_roles_count + 1;
299
300 x_grantee_names(all_roles_count):=l_grantee_list(i).grantee_name;
301 x_grantee_types(all_roles_count):=l_grantee_list(i).grantee_type;
302 x_role_names(all_roles_count):=l_grantee_list(i).role_name;
303 x_role_display_names(all_roles_count):=l_grantee_list(i).role_display_name;
304 x_default_access(all_roles_count):=l_grantee_list(i).default_access;
305
306 END LOOP;
307
308 END IF;
309
310 --dbms_output.put_line('** sameer ** Preparing SQL ot get the inherited Change Roles....');
311
312 -----Get all the Change Management inherited roles including workflow assignees etc.
313 query_to_exec :=
314 'SELECT DISTINCT ' ||
315 'granted_menu_data.menu_name role_name, ' ||
316 'granted_menu.user_menu_name role_display_name, ' ||
317 '''USER'' grantee_type, ' ||
318 'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
319 'granted_menu_data.menu_id menu_id ' ||
320 'FROM fnd_menus_tl granted_menu, ' ||
321 'fnd_menus granted_menu_data, ' ||
322 'HZ_PARTIES hzei, ' ||
323 'HZ_PARTIES hzci, ' ||
324 'HZ_PARTIES hzsi, ' ||
325 'HZ_RELATIONSHIPS hzr, ' ||
326 'HZ_PARTIES hzc, ' ||
327 '( ' ||
328 'SELECT ' ||
329 'C.CHANGE_ID, ' ||
330 'P.ASSIGNEE_ID, ' ||
331 'C.CREATION_DATE ' ||
332 'FROM WF_ACTIVITIES WA, ' ||
333 'WF_ACTIVITY_ATTRIBUTES WAA, ' ||
334 'ENG_CHANGE_ROUTE_STEPS S, ' ||
335 'ENG_CHANGE_ROUTE_PEOPLE P, ' ||
336 'ENG_CHANGE_ROUTES R, ' ||
337 'ENG_ENGINEERING_CHANGES C ' ||
338 'WHERE WAA.TEXT_DEFAULT = ''ENG_CHANGE_WF_APPROVERS'' ' ||
339 'AND WAA.NAME = ''DEFAULT_CHANGE_ROLE'' ' ||
340 'AND WAA.ACTIVITY_VERSION = WA.VERSION ' ||
341 'AND WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE ' ||
342 'AND WAA.ACTIVITY_NAME = WA.NAME ' ||
343 'AND WA.TYPE = ''PROCESS'' ' ||
344 'AND WA.BEGIN_DATE <= SYSDATE ' ||
345 'AND ' ||
346 '( ' ||
347 'WA.END_DATE >= SYSDATE ' ||
348 'OR WA.END_DATE IS NULL ' ||
349 ') ' ||
350 'AND WA.ITEM_TYPE = S.WF_ITEM_TYPE ' ||
351 'AND WA.NAME = S.WF_PROCESS_NAME ' ||
352 'AND P.ASSIGNEE_ID <> -1 ' ||
353 'AND P.STEP_ID = S.STEP_ID ' ||
354 'AND S.ROUTE_ID = R.ROUTE_ID ' ||
355 'AND R.CLASSIFICATION_CODE = TO_CHAR(C.STATUS_CODE) ' ||
356 'AND R.OBJECT_ID1 = C.CHANGE_ID ' ||
357 'AND R.OBJECT_NAME = ''ENG_CHANGE'' ' ||
358 'AND R.TEMPLATE_FLAG = ''N'' ' ||
359 ') ' ||
360 'child_table_name ' ||
361 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
362 'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
363 'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
364 'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
365 'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
366 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
367 'AND hzei.party_type=''PERSON'' ' ||
368 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
369 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
370 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
371 'AND granted_menu_data.menu_name = ''ENG_CHANGE_APPROVER'' ' ||
372 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
373 'AND ' ||
374 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
375 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
376 ')) ' ||
377 'AND granted_menu.language= userenv(''LANG'') ' ||
378 'UNION ' ||
379 'SELECT DISTINCT ' ||
380 'granted_menu_data.menu_name role_name, ' ||
381 'granted_menu.user_menu_name role_display_name, ' ||
382 '''USER'' grantee_type, ' ||
383 'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
384 'granted_menu_data.menu_id menu_id ' ||
385 'FROM fnd_menus_tl granted_menu, ' ||
386 'fnd_menus granted_menu_data, ' ||
387 'HZ_PARTIES hzei, ' ||
388 'HZ_PARTIES hzci, ' ||
389 'HZ_PARTIES hzsi, ' ||
390 'HZ_RELATIONSHIPS hzr, ' ||
391 'HZ_PARTIES hzc, ' ||
392 'FND_USER fuser, ' ||
393 'FND_USER fpuser, ' ||
394 'ENG_ENGINEERING_CHANGES child_table_name ' ||
395 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
396 'AND child_table_name.CREATED_BY = fuser.user_id ' ||
397 'AND child_table_name.CREATED_BY = fpuser.user_id ' ||
398 'AND TO_CHAR(fuser.employee_id) = hzei.person_identifier(+) ' ||
399 'AND fuser.customer_id = hzci.party_id(+) ' ||
400 'AND fuser.supplier_id = hzsi.party_id(+) ' ||
401 'AND hzr.SUBJECT_ID(+) = hzei.party_id ' ||
402 'AND hzei.party_type=''PERSON'' ' ||
403 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
404 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
405 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
406 'AND granted_menu_data.menu_name = ''ENG_CHANGE_CREATOR'' ' ||
407 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
408 'AND ' ||
409 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
410 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
411 ')) ' ||
412 'AND granted_menu.language= userenv(''LANG'') ' ||
413 'UNION ' ||
414 'SELECT DISTINCT ' ||
415 'granted_menu_data.menu_name role_name, ' ||
416 'granted_menu.user_menu_name role_display_name, ' ||
417 '''USER'' grantee_type, ' ||
418 'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
419 'granted_menu_data.menu_id menu_id ' ||
420 'FROM fnd_menus_tl granted_menu, ' ||
421 'fnd_menus granted_menu_data, ' ||
422 'HZ_PARTIES hzei, ' ||
423 'HZ_PARTIES hzci, ' ||
424 'HZ_PARTIES hzsi, ' ||
425 'HZ_RELATIONSHIPS hzr, ' ||
426 'HZ_PARTIES hzc, ' ||
427 'ENG_ENGINEERING_CHANGES child_table_name ' ||
428 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
429 'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
430 'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
431 'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
432 'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
433 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
434 'AND hzei.party_type=''PERSON'' ' ||
435 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
436 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
437 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
438 'AND granted_menu_data.menu_name = ''ENG_CHANGE_ASSIGNEE'' ' ||
439 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
440 'AND ' ||
441 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
442 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
443 ')) ' ||
444 'AND granted_menu.language= userenv(''LANG'') ' ||
445 'UNION ' ||
446 'SELECT DISTINCT ' ||
447 'granted_menu_data.menu_name role_name, ' ||
448 'granted_menu.user_menu_name role_display_name, ' ||
449 '''GROUP'' grantee_type, ' ||
450 'grantee_group.party_name grantee_name, ' ||
451 'granted_menu_data.menu_id menu_id ' ||
452 'FROM fnd_menus_tl granted_menu, ' ||
453 'fnd_menus granted_menu_data, ' ||
454 'HZ_PARTIES grantee_group, ' ||
455 'ENG_ENGINEERING_CHANGES child_table_name ' ||
456 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
457 'AND child_table_name.ASSIGNEE_ID = grantee_group.party_id(+) ' ||
458 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
459 'AND grantee_group.party_type = ''GROUP'' ' ||
460 'AND granted_menu_data.menu_name = ''ENG_CHANGE_ASSIGNEE'' ' ||
461 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
462 'AND ' ||
463 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
464 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
465 ')) ' ||
466 'AND granted_menu.language= userenv(''LANG'') ' ||
467 'UNION ' ||
468 'SELECT DISTINCT ' ||
469 'granted_menu_data.menu_name role_name, ' ||
470 'granted_menu.user_menu_name role_display_name, ' ||
471 '''USER'' grantee_type, ' ||
472 'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
473 'granted_menu_data.menu_id menu_id ' ||
474 'FROM fnd_menus_tl granted_menu, ' ||
475 'fnd_menus granted_menu_data, ' ||
476 'HZ_PARTIES hzei, ' ||
477 'HZ_PARTIES hzci, ' ||
478 'HZ_PARTIES hzsi, ' ||
479 'HZ_RELATIONSHIPS hzr, ' ||
480 'HZ_PARTIES hzc, ' ||
481 'ENG_ENGINEERING_CHANGES child_table_name ' ||
482 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
483 'AND child_table_name.REQUESTOR_ID = hzei.party_id(+) ' ||
484 'AND child_table_name.REQUESTOR_ID = hzci.party_id(+) ' ||
485 'AND child_table_name.REQUESTOR_ID = hzsi.party_id(+) ' ||
486 'AND hzr.SUBJECT_ID(+) = child_table_name.REQUESTOR_ID ' ||
487 'AND child_table_name.REQUESTOR_ID IS NOT NULL ' ||
488 'AND hzei.party_type=''PERSON'' ' ||
489 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
490 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
491 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
492 'AND granted_menu_data.menu_name = ''ENG_CHANGE_REQUESTOR'' ' ||
493 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
494 'AND ' ||
495 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
496 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
497 ')) ' ||
498 'AND granted_menu.language= userenv(''LANG'') ' ||
499 'UNION ' ||
500 'SELECT DISTINCT ' ||
501 'granted_menu_data.menu_name role_name, ' ||
502 'granted_menu.user_menu_name role_display_name, ' ||
503 '''GROUP'' grantee_type, ' ||
504 'grantee_group.party_name grantee_name, ' ||
505 'granted_menu_data.menu_id menu_id ' ||
506 'FROM fnd_menus_tl granted_menu, ' ||
507 'fnd_menus granted_menu_data, ' ||
508 'HZ_PARTIES grantee_group, ' ||
509 'ENG_ENGINEERING_CHANGES child_table_name ' ||
510 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
511 'AND child_table_name.REQUESTOR_ID = grantee_group.party_id(+) ' ||
512 'AND child_table_name.REQUESTOR_ID IS NOT NULL ' ||
513 'AND grantee_group.party_type = ''GROUP'' ' ||
514 'AND granted_menu_data.menu_name = ''ENG_CHANGE_REQUESTOR'' ' ||
515 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
516 'AND ' ||
517 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
518 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
519 ')) ' ||
520 'AND granted_menu.language= userenv(''LANG'') ' ||
521 'UNION ' ||
522 'SELECT DISTINCT ' ||
523 'granted_menu_data.menu_name role_name, ' ||
524 'granted_menu.user_menu_name role_display_name, ' ||
525 '''USER'' grantee_type, ' ||
526 'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
527 'granted_menu_data.menu_id menu_id ' ||
528 'FROM fnd_menus_tl granted_menu, ' ||
529 'fnd_menus granted_menu_data, ' ||
530 'HZ_PARTIES hzei, ' ||
531 'HZ_PARTIES hzci, ' ||
532 'HZ_PARTIES hzsi, ' ||
533 'HZ_RELATIONSHIPS hzr, ' ||
534 'HZ_PARTIES hzc, ' ||
535 '( ' ||
536 'SELECT ' ||
537 'C.CHANGE_ID, ' ||
538 'P.ASSIGNEE_ID, ' ||
539 'C.CREATION_DATE ' ||
540 'FROM WF_ACTIVITIES WA, ' ||
541 'WF_ACTIVITY_ATTRIBUTES WAA, ' ||
542 'ENG_CHANGE_ROUTE_STEPS S, ' ||
543 'ENG_CHANGE_ROUTE_PEOPLE P, ' ||
544 'ENG_CHANGE_ROUTES R, ' ||
545 'ENG_ENGINEERING_CHANGES C ' ||
546 'WHERE WAA.TEXT_DEFAULT = ''ENG_CHANGE_WF_REVIEWERS'' ' ||
547 'AND WAA.NAME = ''DEFAULT_CHANGE_ROLE'' ' ||
548 'AND WAA.ACTIVITY_VERSION = WA.VERSION ' ||
549 'AND WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE ' ||
550 'AND WAA.ACTIVITY_NAME = WA.NAME ' ||
551 'AND WA.TYPE = ''PROCESS'' ' ||
552 'AND WA.BEGIN_DATE <= SYSDATE ' ||
553 'AND ' ||
554 '( ' ||
555 ' WA.END_DATE >= SYSDATE ' ||
556 ' OR WA.END_DATE IS NULL ' ||
557 ') ' ||
558 'AND WA.ITEM_TYPE = S.WF_ITEM_TYPE ' ||
559 'AND WA.NAME = S.WF_PROCESS_NAME ' ||
560 'AND P.ASSIGNEE_ID <> -1 ' ||
561 'AND P.STEP_ID = S.STEP_ID ' ||
562 'AND S.ROUTE_ID = R.ROUTE_ID ' ||
563 'AND R.CLASSIFICATION_CODE = TO_CHAR(C.STATUS_CODE) ' ||
564 'AND R.OBJECT_ID1 = C.CHANGE_ID ' ||
565 'AND R.OBJECT_NAME = ''ENG_CHANGE'' ' ||
566 'AND R.TEMPLATE_FLAG = ''N'' ' ||
567 ') ' ||
568 'child_table_name ' ||
569 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
570 'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
571 'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
572 'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
573 'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
574 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
575 'AND hzei.party_type=''PERSON'' ' ||
576 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
577 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
578 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
579 'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
580 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
581 'AND ' ||
582 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
583 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
584 ')) ' ||
585 'AND granted_menu.language= userenv(''LANG'') ' ||
586 'UNION ' ||
587 'SELECT DISTINCT ' ||
588 'granted_menu_data.menu_name role_name, ' ||
589 'granted_menu.user_menu_name role_display_name, ' ||
590 '''USER'' grantee_type, ' ||
591 'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
592 'granted_menu_data.menu_id menu_id ' ||
593 'FROM fnd_menus_tl granted_menu, ' ||
594 'fnd_menus granted_menu_data, ' ||
595 'HZ_PARTIES hzei, ' ||
596 'HZ_PARTIES hzci, ' ||
597 'HZ_PARTIES hzsi, ' ||
598 'HZ_RELATIONSHIPS hzr, ' ||
599 'HZ_PARTIES hzc, ' ||
600 'ENG_CHANGE_LINES child_table_name ' ||
601 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
602 'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
603 'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
604 'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
605 'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
606 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
607 'AND hzei.party_type=''PERSON'' ' ||
608 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
609 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
610 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
611 'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
612 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
613 'AND ' ||
614 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
615 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
616 ')) ' ||
617 'AND granted_menu.language= userenv(''LANG'') ' ||
618 'UNION ' ||
619 'SELECT DISTINCT ' ||
620 'granted_menu_data.menu_name role_name, ' ||
621 'granted_menu.user_menu_name role_display_name, ' ||
622 '''GROUP'' grantee_type, ' ||
623 'grantee_group.party_name grantee_name, ' ||
624 'granted_menu_data.menu_id menu_id ' ||
625 'FROM fnd_menus_tl granted_menu, ' ||
626 'fnd_menus granted_menu_data, ' ||
627 'HZ_PARTIES grantee_group, ' ||
628 'ENG_CHANGE_LINES child_table_name ' ||
629 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
630 'AND child_table_name.ASSIGNEE_ID = grantee_group.party_id(+) ' ||
631 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
632 'AND grantee_group.party_type = ''GROUP'' ' ||
633 'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
634 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
635 'AND ' ||
636 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
637 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
638 ')) ' ||
639 'AND granted_menu.language= userenv(''LANG'') ' ||
640 'UNION ' ||
641 'SELECT DISTINCT ' ||
642 'granted_menu_data.menu_name role_name, ' ||
643 'granted_menu.user_menu_name role_display_name, ' ||
644 '''USER'' grantee_type, ' ||
645 'ltrim(nvl( hzei.party_name,nvl( hzci.party_name,nvl( hzsi.party_name,null))),''* '') grantee_name, ' ||
646 'granted_menu_data.menu_id menu_id ' ||
647 'FROM fnd_menus_tl granted_menu, ' ||
648 'fnd_menus granted_menu_data, ' ||
649 'HZ_PARTIES hzei, ' ||
650 'HZ_PARTIES hzci, ' ||
651 'HZ_PARTIES hzsi, ' ||
652 'HZ_RELATIONSHIPS hzr, ' ||
653 'HZ_PARTIES hzc, ' ||
654 'ENG_ENGINEERING_CHANGES child_table_name ' ||
655 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
656 'AND child_table_name.ASSIGNEE_ID = hzei.party_id(+) ' ||
657 'AND child_table_name.ASSIGNEE_ID = hzci.party_id(+) ' ||
658 'AND child_table_name.ASSIGNEE_ID = hzsi.party_id(+) ' ||
659 'AND hzr.SUBJECT_ID(+) = child_table_name.ASSIGNEE_ID ' ||
660 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
661 'AND hzei.party_type=''PERSON'' ' ||
662 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
663 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
664 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
665 'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
666 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
667 'AND ' ||
668 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
669 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
670 ')) ' ||
671 'AND granted_menu.language= userenv(''LANG'') ' ||
672 'UNION ' ||
673 'SELECT DISTINCT ' ||
674 'granted_menu_data.menu_name role_name, ' ||
675 'granted_menu.user_menu_name role_display_name, ' ||
676 '''GROUP'' grantee_type, ' ||
677 'grantee_group.party_name grantee_name, ' ||
678 'granted_menu_data.menu_id menu_id ' ||
679 'FROM fnd_menus_tl granted_menu, ' ||
680 'fnd_menus granted_menu_data, ' ||
681 'HZ_PARTIES grantee_group, ' ||
682 'ENG_ENGINEERING_CHANGES child_table_name ' ||
683 'WHERE CHANGE_ID = ' || l_pk1_value || ' ' ||
684 'AND child_table_name.ASSIGNEE_ID = grantee_group.party_id(+) ' ||
685 'AND child_table_name.ASSIGNEE_ID IS NOT NULL ' ||
686 'AND grantee_group.party_type = ''GROUP'' ' ||
687 'AND granted_menu_data.menu_name = ''ENG_CHANGE_REVIEWER'' ' ||
688 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
689 'AND ' ||
690 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
691 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
692 ')) ' ||
693 'AND granted_menu.language= userenv(''LANG'') '; /* || -- Removed since There will be no roles for
694 -- COMPANY and ALL_USERS in DOM
695 'UNION ' ||
696 'SELECT ' ||
697 'granted_menu_data.menu_name role_name, ' ||
698 'granted_menu.user_menu_name role_display_name, ' ||
699 '''COMPANY'' grantee_type, ' ||
700 'internal_company.company_name grantee_name, ' ||
701 'granted_menu_data.menu_id menu_id ' ||
702 'FROM hz_parties grantee_global, ' ||
703 'fnd_menus_tl granted_menu, ' ||
704 'fnd_menus profile_menu_data, ' ||
705 'fnd_menus granted_menu_data, ' ||
706 'ego_obj_role_mappings mapping, ' ||
707 'fnd_objects obj, ' ||
708 'FND_PROFILE_OPTIONS profile, ' ||
709 'FND_PROFILE_OPTION_VALUES profile_value, ' ||
710 'EGO_INTERNAL_COMPANY_V internal_company ' ||
711 'WHERE profile.profile_option_id = profile_value.profile_option_id ' ||
712 'AND profile.profile_option_name in (''EGO_INTERNAL_USER_DEFAULT_ROLE'', ''ENG_INTERNAL_USER_DEFAULT_ROLE'') ' ||
713 'AND obj.obj_name = ''EGO_ITEM'' ' ||
714 'AND grantee_global.party_id = -1000 ' ||
715 'AND mapping.parent_object_id = obj.object_id ' ||
716 'AND profile_menu_data.menu_name = profile_value.profile_option_value ' ||
717 'AND mapping.parent_role_id = profile_menu_data.menu_id ' ||
718 'AND granted_menu.menu_id = mapping.child_role_id ' ||
719 'AND ' ||
720 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
721 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
722 ')) ' ||
723 'AND granted_menu.language= userenv(''LANG'') ' ||
724 'AND granted_menu_data.menu_id = granted_menu.menu_id ' ||
725 'AND EXISTS ' ||
726 '( ' ||
727 'SELECT ' ||
728 'pk1_value, ' ||
729 'pk2_value ' ||
730 'FROM eng_change_subjects_v ' ||
731 'WHERE ' ||
732 '( ' ||
733 ' OBJECT_NAME=''EGO_ITEM'' ' ||
734 ' OR OBJECT_NAME=''EGO_ITEM_REVISION'' ' ||
735 ') ' ||
736 'AND pk1_value is NOT NULL ' ||
737 'AND change_id = ' || l_pk1_value || ' ' ||
738 ') ' ||
739 'UNION ' ||
740 'SELECT ' ||
741 'granted_menu_data.menu_name role_name, ' ||
742 'granted_menu.user_menu_name role_display_name, ' ||
743 '''COMPANY'' grantee_type, ' ||
744 'internal_company.company_name grantee_name, ' ||
745 'granted_menu_data.menu_id menu_id ' ||
746 'FROM hz_parties grantee_global, ' ||
747 'fnd_menus_tl granted_menu, ' ||
748 'fnd_menus profile_menu_data, ' ||
749 'fnd_menus granted_menu_data, ' ||
750 'fnd_objects obj, ' ||
751 'FND_PROFILE_OPTIONS profile, ' ||
752 'FND_PROFILE_OPTION_VALUES profile_value, ' ||
753 'EGO_INTERNAL_COMPANY_V internal_company ' ||
754 'WHERE profile.profile_option_id = profile_value.profile_option_id ' ||
755 'AND profile.profile_option_name in (''EGO_INTERNAL_USER_DEFAULT_ROLE'', ''ENG_INTERNAL_USER_DEFAULT_ROLE'') ' ||
756 'AND obj.obj_name = ''ENG_CHANGE'' ' ||
757 'AND obj.APPLICATION_ID = profile.APPLICATION_ID ' ||
758 'AND grantee_global.party_id = -1000 ' ||
759 'AND profile_menu_data.menu_name = profile_value.profile_option_value ' ||
760 'AND granted_menu.menu_id = profile_menu_data.menu_id ' ||
761 'AND ' ||
762 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
763 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
764 ')) ' ||
765 'AND granted_menu.language= userenv(''LANG'') ' ||
766 'AND granted_menu_data.menu_id = granted_menu.menu_id ' ; */
767
768 --dbms_output.put_line('** sameer ** Prepared SQL ot get the inherited Change Roles....');
769
770 IF l_change_has_items = TRUE THEN -- If there are any items in subjects or Revised items only then get these roles
771
772 -----Get the Instance set Ids for the Item for all 'PERSON's.
773 Get_Valid_Instance_Set_Ids
774 (
775 p_obj_name => 'EGO_ITEM' ,
776 p_grantee_type => 'USER' ,
777 p_parent_obj_sql => null ,
778 p_bind1 => null ,
779 p_bind2 => null ,
780 p_bind3 => null ,
781 p_bind4 => null ,
782 p_bind5 => null ,
783 p_obj_ids => l_obj_ids ,
784 x_inst_set_ids => l_inst_set_ids
785 );
786
787 --dbms_output.put_line('** sameer ** Instance set Ids for Inherited Persons : ' || l_inst_set_ids);
788
789 IF( length(l_inst_set_ids) > 0) THEN -- 'PERSON's
790 query_to_exec := query_to_exec || ' UNION ' ||
791 'SELECT ' ||
792 'granted_menu_data.menu_name internal_role_name, ' ||
793 'granted_menu.user_menu_name role_name, ' ||
794 'grants.grantee_type grantee_type, ' ||
795 'hzsi.party_name grantee_name, ' ||
796 'granted_menu_data.menu_id menu_id ' ||
797 'FROM fnd_grants grants, ' ||
798 'HZ_PARTIES hzsi, ' ||
799 'HZ_RELATIONSHIPS hzr, ' ||
800 'HZ_PARTIES hzc, ' ||
801 'fnd_menus_tl granted_menu, ' ||
802 'fnd_menus granted_menu_data, ' ||
803 'ego_obj_role_mappings mapping, ' ||
804 'fnd_objects obj,' ||
805 'eng_engineering_changes changes,' ||
806 'eng_change_subjects subjects,' ||
807 'eng_revised_items rev_items ' ||
808 'WHERE grants.grantee_type = ''USER'' ' ||
809 'AND grants.object_id = obj.object_id ' ||
810 'AND mapping.parent_object_id = grants.object_id ' ||
811 'AND mapping.parent_role_id = grants.menu_id ' ||
812 'AND SUBSTR(grants.grantee_key, 1, INSTR(grants.grantee_key, '':'')-1) =''HZ_PARTY'' ' ||
813 'AND TO_NUMBER(REPLACE(grants.grantee_key,''HZ_PARTY:'','''')) = hzsi.party_id(+) ' ||
814 'AND hzsi.party_type=''PERSON'' ' ||
815 'AND hzr.SUBJECT_ID(+) = TO_NUMBER(REPLACE(grants.grantee_key,''HZ_PARTY:'','''')) ' ||
816 'AND hzr.RELATIONSHIP_CODE(+) = ''EMPLOYEE_OF'' ' ||
817 'AND hzc.PARTY_ID(+) = hzr.OBJECT_ID ' ||
818 'AND hzc.PARTY_TYPE(+) = ''ORGANIZATION'' ' ||
819 'AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)' ||
820 'AND granted_menu.menu_id = mapping.child_role_id ' ||
821 'AND ' ||
822 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
823 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
824 ')) ' ||
825 'AND granted_menu.LANGUAGE= USERENV(''LANG'') ' ||
826 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
827 'AND grants.instance_type = ''SET'' ' ||
828 'AND ' ||
829 '( ( obj.obj_name = ''EGO_ITEM'' ' ||
830 ' AND ( grants.instance_set_id in (' || l_inst_set_ids || ')' ||
831 ' ) ' ||
832 ')) ' ||
833 'AND changes.change_id = ' || l_pk1_value || ' ' ||
834 'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
835 ' OR ( changes.change_id = REV_ITEMS.change_id )) ' ;
836
837 END IF;
838 ---------End of Instance set Ids for the Item for all 'PERSON's.
839
840 -----Get the Instance set Ids for the Item for all 'GROUPS's.
841 Get_Valid_Instance_Set_Ids
842 (
843 p_obj_name => 'EGO_ITEM' ,
844 p_grantee_type => 'GROUP' ,
845 p_parent_obj_sql => null ,
846 p_bind1 => null ,
847 p_bind2 => null ,
848 p_bind3 => null ,
849 p_bind4 => null ,
850 p_bind5 => null ,
851 p_obj_ids => l_obj_ids ,
852 x_inst_set_ids => l_inst_set_ids
853 );
854
855 --dbms_output.put_line('** sameer ** Instance set Ids for Inherited Groups : ' || l_inst_set_ids);
856
857 IF( length(l_inst_set_ids) > 0) THEN -- 'COMPANY's
858 query_to_exec := query_to_exec || ' UNION ' ||
859 'SELECT ' ||
860 'granted_menu_data.menu_name internal_role_name, ' ||
861 'granted_menu.user_menu_name role_name, ' ||
862 'grants.grantee_type grantee_type, ' ||
863 'grantee_group.party_name grantee_name, ' ||
864 'granted_menu_data.menu_id menu_id ' ||
865 'FROM fnd_grants grants, ' ||
866 'HZ_PARTIES grantee_group, ' ||
867 'fnd_menus_tl granted_menu, ' ||
868 'fnd_menus granted_menu_data, ' ||
869 'ego_obj_role_mappings mapping, ' ||
870 'fnd_objects obj , ' ||
871 'eng_engineering_changes changes, ' ||
872 'eng_change_subjects subjects, ' ||
873 'eng_revised_ITEMS REV_ITEMS ' ||
874 'WHERE grants.grantee_type = ''GROUP'' ' ||
875 'AND grants.object_id = obj.object_id ' ||
876 'AND mapping.parent_object_id = grants.object_id ' ||
877 'AND mapping.parent_role_id = grants.menu_id ' ||
878 'AND grantee_group.party_type = ''GROUP'' ' ||
879 'AND SUBSTR(grants.grantee_key, 1, INSTR(grants.grantee_key, '':'')-1) =''HZ_GROUP'' ' ||
880 'AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE) ' ||
881 'AND TO_NUMBER(REPLACE(grants.grantee_key,''HZ_GROUP:'','''')) = grantee_group.party_id ' ||
882 'AND granted_menu.menu_id = mapping.child_role_id ' ||
883 'AND ' ||
884 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
885 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
886 ')) ' ||
887 'AND granted_menu.LANGUAGE= USERENV(''LANG'') ' ||
888 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
889 'AND grants.instance_type = ''SET'' ' ||
890 'AND ' ||
891 '( ( obj.obj_name = ''EGO_ITEM'' AND ' ||
892 ' grants.instance_set_id in ( ' || l_inst_set_ids || ' ) ' ||
893 ') ) ' ||
894 'AND changes.change_id = ' || l_pk1_value || ' ' ||
895 'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
896 ' OR ( changes.change_id = REV_ITEMS.change_id )) ';
897 END IF;
898 ---------End of Instance set Ids for the Item for all 'GROUP's.
899
900 /*
901 -----Get the Instance set Ids for the Item for all 'COMPANY's.
902 Get_Valid_Instance_Set_Ids
903 (
904 p_obj_name => 'EGO_ITEM' ,
905 p_grantee_type => 'COMPANY' ,
906 p_parent_obj_sql => null ,
907 p_bind1 => null ,
908 p_bind2 => null ,
909 p_bind3 => null ,
910 p_bind4 => null ,
911 p_bind5 => null ,
912 p_obj_ids => l_obj_ids ,
913 x_inst_set_ids => l_inst_set_ids
914 );
915
916 --dbms_output.put_line('** sameer ** Instance set Ids for Inherited Companys : ' || l_inst_set_ids);
917
918 IF( length(l_inst_set_ids) > 0) THEN -- 'COMPANY's
919 query_to_exec := query_to_exec || ' UNION ' ||
920 'SELECT ' ||
921 'granted_menu_data.menu_name internal_role_name, ' ||
922 'granted_menu.user_menu_name role_name, ' ||
923 'grants.grantee_type grantee_type, ' ||
924 'grantee_company.party_name grantee_name, ' ||
925 'granted_menu_data.menu_id menu_id ' ||
926 'FROM fnd_grants grants, ' ||
927 'hz_parties grantee_company, ' ||
928 'fnd_menus_tl granted_menu, ' ||
929 'fnd_menus granted_menu_data, ' ||
930 'ego_obj_role_mappings mapping, ' ||
931 'fnd_objects obj, ' ||
932 'eng_engineering_changes changes, ' ||
933 'eng_change_subjects subjects, ' ||
934 'eng_revised_items rev_items ' ||
935 'WHERE grants.grantee_type = ''COMPANY'' ' ||
936 'AND grants.object_id = obj.object_id ' ||
937 'AND grantee_company.party_type = ''ORGANIZATION'' ' ||
938 'AND mapping.parent_object_id = grants.object_id ' ||
939 'AND mapping.parent_role_id = grants.menu_id ' ||
940 'AND to_number(replace(grants.grantee_key,''HZ_COMPANY:'','''')) = grantee_company.party_id ' ||
941 'AND substr(grants.grantee_key, 1, instr(grants.grantee_key, '':'')-1) =''HZ_COMPANY'' ' ||
942 'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
943 'AND granted_menu.menu_id = mapping.child_role_id ' ||
944 'AND ' ||
945 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
946 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
947 ')) ' ||
948 'AND granted_menu.language= userenv(''LANG'') ' ||
949 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
950 'AND grants.instance_type = ''SET'' ' ||
951 'AND ' ||
952 '( ( obj.obj_name = ''EGO_ITEM'' ' ||
953 ' AND ( grants.instance_set_id in ( ' || l_inst_set_ids || ' ) )' ||
954 ') ) ' ||
955 'AND changes.change_id = ' || l_pk1_value || ' ' ||
956 'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
957 ' OR ( changes.change_id = REV_ITEMS.change_id ))';
958
959
960 END IF;
961 ---------End of Instance set Ids for the Item for all 'COMPANY's.
962 */
963
964 /*
965 -----Get the Instance set Ids for the Item for all 'GLOBAL's.
966 Get_Valid_Instance_Set_Ids
967 (
968 p_obj_name => 'EGO_ITEM' ,
969 p_grantee_type => 'GLOBAL' ,
970 p_parent_obj_sql => null ,
971 p_bind1 => null ,
972 p_bind2 => null ,
973 p_bind3 => null ,
974 p_bind4 => null ,
975 p_bind5 => null ,
976 p_obj_ids => l_obj_ids ,
977 x_inst_set_ids => l_inst_set_ids
978 );
979
980 --dbms_output.put_line('** sameer ** Instance set Ids for Inherited All users : ' || l_inst_set_ids);
981
982 IF( length(l_inst_set_ids) > 0) THEN -- 'GLOBAL's
983 query_to_exec := query_to_exec || ' UNION ' ||
984 'SELECT ' ||
985 'granted_menu_data.menu_name internal_role_name, ' ||
986 'granted_menu.user_menu_name role_name, ' ||
987 'grants.grantee_type grantee_type, ' ||
988 'grantee_global.party_name grantee_name, ' ||
989 'granted_menu_data.menu_id menu_id ' ||
990 'FROM fnd_grants grants, ' ||
991 'hz_parties grantee_global, ' ||
992 'fnd_menus_tl granted_menu, ' ||
993 'fnd_menus granted_menu_data, ' ||
994 'ego_obj_role_mappings mapping, ' ||
995 'fnd_objects obj, ' ||
996 'eng_engineering_changes changes, ' ||
997 'eng_change_subjects subjects, ' ||
998 'eng_revised_items rev_items ' ||
999 'WHERE grants.grantee_type = ''GLOBAL'' ' ||
1000 'AND grants.object_id = obj.object_id ' ||
1001 'AND mapping.parent_object_id = grants.object_id ' ||
1002 'AND mapping.parent_role_id = grants.menu_id ' ||
1003 'AND grantee_global.party_type = ''GLOBAL'' ' ||
1004 'AND grantee_global.party_id = -1000 ' ||
1005 'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
1006 'AND granted_menu.menu_id = mapping.child_role_id ' ||
1007 'AND ' ||
1008 '(( ''' || p_role_name || ''' is null AND granted_menu_data.menu_name like ''%'' ' ||
1009 ' ) OR ( granted_menu_data.MENU_NAME in ( ''' || p_role_name || ''' ) ' ||
1010 ')) ' ||
1011 'AND granted_menu.language= userenv(''LANG'') ' ||
1012 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
1013 'AND grants.instance_type = ''SET'' ' ||
1014 'AND ' ||
1015 '( ( ' ||
1016 ' obj.obj_name = ''EGO_ITEM'' AND ' ||
1017 ' ( grants.instance_set_id in (' || l_inst_set_ids || ' ) ' ||
1018 ' ) ' ||
1019 ') ) ' ||
1020 'AND changes.change_id = ' || l_pk1_value || ' ' ||
1021 'AND (( changes.change_id = subjects.change_id AND subjects.pk1_value IS NOT NULL ) ' ||
1022 ' OR ( changes.change_id = REV_ITEMS.change_id )) ';
1023 END IF;
1024 ---------End of Instance set Ids for the Item for all 'GLOBAL's.
1025 */
1026
1027 END IF; -- If there are any items in subjects or Revised items only then get these roles
1028
1029 l_index := 0;
1030 --dbms_output.put_line('** sameer ** trying to execute the dynamic SQL for fetching the Inherited roles ... starting l_index from : ' || l_index);
1031 cursor_select := DBMS_SQL.OPEN_CURSOR;
1032 --dbms_output.put_line('** sameer ** opened implicit cursor');
1033 --dbms_output.put_line('** sameer ** query is as follows ....');
1034
1035 --dbms_output.put_line(query_to_exec);
1036 --p(query_to_exec);
1037 --utl_file_test_write('/home/sdarbha/Enhancements/Sep12', 'querytolog.txt', query_to_exec );
1038 DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
1039 --dbms_output.put_line('** sameer ** parsed implicit cursor');
1040
1041 --dbms_output.put_line('** sameer ** defining columns ......');
1042
1043 dbms_sql.DEFINE_COLUMN(cursor_select, 1, '', 30);
1044 dbms_sql.DEFINE_COLUMN(cursor_select, 2, '', 120);
1045 dbms_sql.DEFINE_COLUMN(cursor_select, 3, '', 30);
1046 dbms_sql.DEFINE_COLUMN(cursor_select, 4, '', 120);
1047 dbms_sql.DEFINE_COLUMN(cursor_select, 5, l_temp_menu_id);
1048
1049 --dbms_output.put_line('** sameer ** defined columns ......');
1050
1051 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
1052
1053 LOOP
1054 IF dbms_sql.fetch_rows(cursor_select) > 0 THEN
1055 DBMS_SQL.COLUMN_VALUE(cursor_select, 1, l_grantee_list(l_index).role_name);
1056 DBMS_SQL.COLUMN_VALUE(cursor_select, 2, l_grantee_list(l_index).role_display_name);
1057 DBMS_SQL.COLUMN_VALUE(cursor_select, 3, l_grantee_list(l_index).grantee_type);
1058 DBMS_SQL.COLUMN_VALUE(cursor_select, 4, l_grantee_list(l_index).grantee_name);
1059 DBMS_SQL.COLUMN_VALUE(cursor_select, 5, l_temp_menu_id);
1060
1061 Get_Default_Access(p_menu_id => l_temp_menu_id,
1062 p_default_access => l_default_access
1063 );
1064 l_grantee_list(l_index).default_access := l_default_access;
1065
1066 l_index:=l_index+1;
1067 ELSE
1068 EXIT;
1069 END IF;
1070 END LOOP;
1071
1072 IF( l_grantee_list.count>0) THEN
1073 x_grantee_names.extend(l_grantee_list.count);
1074 x_grantee_types.extend(l_grantee_list.count);
1075 x_role_names.extend(l_grantee_list.count);
1076 x_role_display_names.extend(l_grantee_list.count);
1077 x_default_access.extend(l_grantee_list.count);
1078 /*
1079 dbms_output.put_line('** sameer ** continuing to add to the return list from .. : ' || l_grantee_list.count || ' **** ' || l_index);
1080 dbms_output.put_line('** sameer ** all_roles_count .. : ' || all_roles_count );
1081 dbms_output.put_line('** sameer ** l_grantee_list.first and l_grantee_list.last .. : ' || l_grantee_list.first || ' <> ' || l_grantee_list.last );
1082 */
1083 FOR i in l_grantee_list.first .. l_grantee_list.last LOOP
1084 all_roles_count := all_roles_count + 1;
1085
1086 x_grantee_names(all_roles_count):=l_grantee_list(i).grantee_name;
1087 x_grantee_types(all_roles_count):=l_grantee_list(i).grantee_type;
1088 x_role_names(all_roles_count):=l_grantee_list(i).role_name;
1089 x_role_display_names(all_roles_count):=l_grantee_list(i).role_display_name;
1090 x_default_access(all_roles_count):=l_grantee_list(i).default_access;
1091 END LOOP;
1092 END IF;
1093
1094 DBMS_SQL.CLOSE_CURSOR(cursor_select);
1095
1096 --dbms_output.put_line('** sameer ** completed successfully all inherited and direct roles on change.' );
1097
1098 END Get_Change_Users;
1099
1100 ----------------------------------------------------------------------
1101 PROCEDURE Get_Valid_Instance_Set_Ids
1102 (
1103 p_obj_name IN VARCHAR2,
1104 p_grantee_type IN VARCHAR2,
1105 p_parent_obj_sql IN VARCHAR2,
1106 p_bind1 IN VARCHAR2,
1107 p_bind2 IN VARCHAR2,
1108 p_bind3 IN VARCHAR2,
1109 p_bind4 IN VARCHAR2,
1110 p_bind5 IN VARCHAR2,
1111 p_obj_ids IN VARCHAR2,
1112 x_inst_set_ids OUT NOCOPY VARCHAR2
1113 )
1114 IS
1115 CURSOR inst_set_preds IS
1116 SELECT DISTINCT
1117 sets.instance_set_id instance_set_id ,
1118 sets.instance_set_name instance_set_name,
1119 sets.predicate predicate
1120 FROM fnd_grants grants,
1121 fnd_object_instance_sets sets,
1122 fnd_objects obj
1123 WHERE obj.obj_name = p_obj_name
1124 AND grants.object_id = obj.object_id
1125 AND grants.instance_type='SET'
1126 AND grants.parameter1 is null
1127 AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate)
1128 AND grants.grantee_type = p_grantee_type
1129 AND sets.instance_set_id = grants.instance_set_id
1130 ORDER BY instance_set_name;
1131
1132 CURSOR obj_meta_data IS
1133 SELECT
1134 DATABASE_OBJECT_NAME,
1135 PK1_COLUMN_NAME,
1136 PK2_COLUMN_NAME,
1137 PK3_COLUMN_NAME,
1138 PK4_COLUMN_NAME,
1139 PK5_COLUMN_NAME
1140 FROM fnd_objects
1141 WHERE OBJ_NAME = p_obj_name;
1142
1143 obj_meta_data_rec obj_meta_data%ROWTYPE;
1144 i NUMBER := 1;
1145 query_to_exec VARCHAR2(32767);
1146 obj_std_pkq VARCHAR2(32767);
1147 prim_key_str VARCHAR2(32767);
1148 inst_set_ids VARCHAR2(32767);
1149 cursor_select NUMBER;
1150 cursor_execute NUMBER;
1151 BEGIN
1152 OPEN obj_meta_data;
1153 FETCH obj_meta_data INTO obj_meta_data_rec;
1154 obj_std_pkq := 'SELECT ' || obj_meta_data_rec.PK1_COLUMN_NAME;
1155 prim_key_str := obj_meta_data_rec.PK1_COLUMN_NAME;
1156 IF obj_meta_data_rec.PK2_COLUMN_NAME IS NOT NULL THEN
1157 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
1158 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
1159 END IF;
1160 IF obj_meta_data_rec.PK3_COLUMN_NAME IS NOT NULL THEN
1161 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
1162 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
1163 END IF;
1164 IF obj_meta_data_rec.PK4_COLUMN_NAME IS NOT NULL THEN
1165 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
1166 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
1167 END IF;
1168 IF obj_meta_data_rec.PK5_COLUMN_NAME IS NOT NULL THEN
1169 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
1170 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
1171 END IF;
1172 obj_std_pkq := obj_std_pkq || ' FROM ' || obj_meta_data_rec.DATABASE_OBJECT_NAME;
1173 CLOSE obj_meta_data;
1174 --dbms_output.put_line('** sameer ** Get_Valid_Instance_Set_Ids ... inst_set_preds_rec for loop staring');
1175
1176 FOR inst_set_preds_rec IN inst_set_preds
1177 LOOP
1178 --dbms_output.put_line('** sameer ** ... inst_set_preds_rec inside for loop : =>' || p_obj_ids);
1179 IF p_obj_ids IS NOT NULL THEN
1180 query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
1181 query_to_exec := query_to_exec || ' WHERE ' || inst_set_preds_rec.predicate || ' )';
1182 ELSIF p_parent_obj_sql IS NOT NULL THEN
1183 query_to_exec := 'SELECT 1 from dual WHERE EXISTS( ' || obj_std_pkq || ' WHERE ';
1184 query_to_exec := query_to_exec || inst_set_preds_rec.predicate || ' AND (';
1185 query_to_exec := query_to_exec || prim_key_str || ') IN (' || p_parent_obj_sql || '))';
1186 END IF;
1187 --dbms_output.put_line('** sameer ** end if ...>>>>: ');
1188 --p(query_to_exec);
1189 cursor_select := DBMS_SQL.OPEN_CURSOR;
1190 --dbms_output.put_line('** sameer ** DBMS_SQL.OPEN_CURSOR ');
1191 DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
1192 --dbms_output.put_line('** sameer ** Get_Valid_Instance_Set_Ids ... parsed .: ');
1193 IF p_bind1 IS NOT NULL THEN
1194 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id1', p_bind1);
1195 --dbms_output.put_line('** sameer ** binded id1 ... ');
1196 END IF;
1197 IF p_bind2 IS NOT NULL THEN
1198 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id2', p_bind2);
1199 END IF;
1200 IF p_bind3 IS NOT NULL THEN
1201 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id3', p_bind3);
1202 END IF;
1203 IF p_bind4 IS NOT NULL THEN
1204 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id4', p_bind4);
1205 END IF;
1206 IF p_bind5 IS NOT NULL THEN
1207 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id5', p_bind5);
1208 END IF;
1209
1210 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
1211 IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
1212 IF i = 1 THEN
1213 inst_set_ids := to_char(inst_set_preds_rec.instance_set_id);
1214 i := 2;
1215 ELSE
1216 inst_set_ids := inst_set_ids || ',' || inst_set_preds_rec.instance_set_id;
1217 END IF;
1218 END IF;
1219 DBMS_SQL.CLOSE_CURSOR(cursor_select);
1220 END LOOP;
1221 IF inst_set_ids IS NOT NULL THEN
1222 x_inst_set_ids := inst_set_ids; /**** list of valid inst_set_ids ****/
1223 ELSE
1224 x_inst_set_ids := '-1';
1225 END IF;
1226 END Get_Valid_Instance_Set_Ids;
1227 ----------------------------------------------------------
1228
1229 END ENG_CHANGE_ROLES_PUB;
1230