eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ITA_NOTIFICATION_PKG

Source


1 package body ITA_NOTIFICATION_PKG as
2 /* $Header: itapnotb.pls 120.13 2006/08/11 20:42:24 cpetriuc noship $ */
3 
4 
5 
6 
7 function GET_ORG_CONNECTIONS_STRING return VARCHAR2 is
8 begin return
9 '
10 (
11 select ORGANIZATION_ID_PARENT, ORGANIZATION_ID_CHILD
12 from PER_ORG_STRUCTURE_ELEMENTS
13 where ORG_STRUCTURE_VERSION_ID =
14 (
15 select ORG_STRUCTURE_VERSION_ID
16 from PER_ORG_STRUCTURE_VERSIONS
17 where
18 DATE_TO is null and
19 ORGANIZATION_STRUCTURE_ID =
20 (
21 select ORGANIZATION_STRUCTURE_ID
22 from PER_ORGANIZATION_STRUCTURES
23 where NAME = FND_PROFILE.VALUE(''AMW_ORG_SECURITY_HIERARCHY'')
24 )
25 )
26 )
27 ';
28 /*
29 The profile value above might not be set, so the inner-most query could return no rows.
30 */
31 end GET_ORG_CONNECTIONS_STRING;
32 
33 
34 
35 
36 function GET_AUDIT_UNITS_STRING(p_org_id NUMBER) return VARCHAR2 is
37 begin return
38 '
39 (
40 select distinct ORGANIZATION_ID
41 from AMW_AUDIT_UNITS_V
42 where
43 ORGANIZATION_ID = ' || to_char(p_org_id) || '
44 )
45 ';
46 /*
47 ORGANIZATION_ID in
48 (
49 select ORGANIZATION_ID_PARENT from ' || GET_ORG_CONNECTIONS_STRING() || '
50 union
51 select ORGANIZATION_ID_CHILD from ' || GET_ORG_CONNECTIONS_STRING() || '
52 start with ORGANIZATION_ID_PARENT = ' || to_char(p_org_id) || '
53 connect by ORGANIZATION_ID_PARENT = prior ORGANIZATION_ID_CHILD
54 )
55 */
56 end GET_AUDIT_UNITS_STRING;
57 
58 
59 
60 
61 function GET_HIERARCHY_ENTITIES_STRING(p_org_type_code VARCHAR2, p_org_id NUMBER, p_control_id NUMBER) return VARCHAR2 is
62 begin
63 if p_org_id is null or (p_org_type_code <> 'OPERATING_UNIT' and p_org_type_code <> 'INV')
64 then return
65 '
66 select distinct OBJECT_TYPE, PK1, PK2, PK3, PK4
67 from AMW_CONTROL_ASSOCIATIONS
68 where
69 CONTROL_ID = ' || to_char(p_control_id) || ' and
70 APPROVAL_DATE is not null and
71 DELETION_APPROVAL_DATE is null and
72 (OBJECT_TYPE = ''RISK_ORG'' or OBJECT_TYPE = ''RISK'' or OBJECT_TYPE = ''ENTITY_CONTROL'')
73 ';
74 else return
75 '
76 select distinct OBJECT_TYPE, PK1, PK2, PK3, PK4
77 from AMW_CONTROL_ASSOCIATIONS
78 where
79 CONTROL_ID = ' || to_char(p_control_id) || ' and
80 APPROVAL_DATE is not null and
81 DELETION_APPROVAL_DATE is null and
82 (
83 (
84 OBJECT_TYPE = ''RISK_ORG'' and
85 PK1 = ' || to_char(p_org_id) || '
86 )
87 or
88 (
89 OBJECT_TYPE = ''RISK'' and
90 PK1 in
91 (
92 select distinct PROCESS_ID
93 from AMW_PROCESS_ORGANIZATION
94 where ORGANIZATION_ID = ' || to_char(p_org_id) || '
95 )
96 )
97 or
98 (
99 OBJECT_TYPE = ''ENTITY_CONTROL'' and
100 PK1 = ' || to_char(p_org_id) || '
101 )
102 )
103 ';
104 /*
105 (
106 (
107 OBJECT_TYPE = ''RISK_ORG'' and
108 PK1 in ' || GET_AUDIT_UNITS_STRING(p_org_id) || '
109 )
110 or
111 (
112 OBJECT_TYPE = ''RISK'' and
113 PK1 in
114 (
115 select distinct PROCESS_ID
116 from AMW_PROCESS_ORGANIZATION
117 where ORGANIZATION_ID in ' || GET_AUDIT_UNITS_STRING(p_org_id) || '
118 )
119 )
120 or
121 (
122 OBJECT_TYPE = ''ENTITY_CONTROL'' and
123 PK1 in ' || GET_AUDIT_UNITS_STRING(p_org_id) || '
124 )
125 )
126 */
127 end if;
128 end GET_HIERARCHY_ENTITIES_STRING;
129 
130 
131 
132 
133 procedure SEND_NOTIFICATION_TO_ALL(p_change_id NUMBER) is
134 
135 type DUMMY_TYPE is REF CURSOR;
136 m_parameter_code VARCHAR2(111);
137 m_parameter_name VARCHAR2(240);
138 m_setup_group_code VARCHAR2(81);
139 m_setup_group_name VARCHAR2(240);
140 m_application VARCHAR2(240);
141 m_pk1_value VARCHAR2(3000);
142 m_org_type_code VARCHAR2(30);
143 m_org_type VARCHAR2(240);
144 m_org_type_test VARCHAR2(240);
145 m_org_name VARCHAR2(3000);
146 m_org_id NUMBER;
147 m_rec_value VARCHAR2(3000);
148 m_prior_value VARCHAR2(3000);
149 m_current_value VARCHAR2(3000);
150 m_updated_by VARCHAR2(100);
151 m_updated_on DATE;
152 m_is_audit_unit NUMBER;
153 m_query VARCHAR2(10000);
154 m_entities_cursor DUMMY_TYPE;
155 m_object_type VARCHAR2(30);
156 m_pk1 NUMBER;
157 m_pk2 NUMBER;
158 m_pk3 NUMBER;
159 m_pk4 NUMBER;
160 m_return_status VARCHAR2(10);
161 
162 
163 cursor GET_CONTROLS(p_parameter_code VARCHAR2) is
164 select distinct CONTROL_ID, NAME, SOURCE
165 from AMW_CONTROLS_ALL_VL
166 where
167 SOURCE = p_parameter_code or
168 SOURCE in
169 (
170 select PARAMETER_CODE
171 from ITA_PARAMETER_HIERARCHY
172 start with OVERRIDE_PARAMETER_CODE = p_parameter_code
173 connect by prior PARAMETER_CODE = OVERRIDE_PARAMETER_CODE
174 );
175 
176 cursor GET_OWNER_RISK_ORG(p_org_id NUMBER, p_process_id NUMBER) is
177 select distinct to_number(replace(grants.GRANTEE_KEY, 'HZ_PARTY:', '')) owner_id
178 from
179 FND_GRANTS grants,
180 FND_OBJECTS objects,
181 FND_MENUS menus
182 where
183 objects.OBJ_NAME = 'AMW_PROCESS_ORGANIZATION' and
184 grants.OBJECT_ID = objects.OBJECT_ID and
185 grants.GRANTEE_TYPE = 'USER' and
186 grants.INSTANCE_TYPE = 'INSTANCE' and
187 grants.INSTANCE_PK1_VALUE = to_char(p_org_id) and
188 grants.INSTANCE_PK2_VALUE = to_char(p_process_id) and
189 grants.GRANTEE_KEY like 'HZ_PARTY%' and
190 nvl(grants.END_DATE, SYSDATE + 1) >= trunc(sysdate) and
191 grants.MENU_ID = menus.MENU_ID and
192 menus.MENU_NAME = 'AMW_ORG_PROC_OWNER_ROLE';
193 
194 cursor GET_OWNER_RISK(p_process_id NUMBER) is
195 select distinct to_number(replace(grants.GRANTEE_KEY, 'HZ_PARTY:', '')) owner_id
196 from
197 FND_GRANTS grants,
198 FND_OBJECTS objects,
199 FND_MENUS menus
200 where
201 objects.OBJ_NAME = 'AMW_PROCESS_APPR_ETTY' and
202 grants.OBJECT_ID = objects.OBJECT_ID and
203 grants.GRANTEE_TYPE = 'USER' and
204 grants.INSTANCE_TYPE = 'INSTANCE' and
205 grants.INSTANCE_PK1_VALUE = to_char(p_process_id) and
206 grants.GRANTEE_KEY like 'HZ_PARTY%' and
207 nvl(grants.END_DATE, SYSDATE + 1) >= trunc(sysdate) and
208 grants.MENU_ID = menus.MENU_ID and
209 menus.MENU_NAME = 'AMW_RL_PROC_OWNER_ROLE';
210 
211 cursor GET_OWNER_ORG(p_org_id NUMBER) is
212 select distinct to_number(replace(grants.GRANTEE_KEY, 'HZ_PARTY:', '')) owner_id
213 from
214 FND_GRANTS grants,
215 FND_OBJECTS objects,
216 FND_MENUS menus
217 where
218 objects.OBJ_NAME = 'AMW_ORGANIZATION' and
219 grants.OBJECT_ID = objects.OBJECT_ID and
220 grants.GRANTEE_TYPE = 'USER' and
221 grants.INSTANCE_TYPE = 'INSTANCE' and
222 grants.INSTANCE_PK1_VALUE = to_char(p_org_id) and
223 grants.GRANTEE_KEY like 'HZ_PARTY%' and
224 nvl(grants.END_DATE, SYSDATE + 1) >= trunc(sysdate) and
225 grants.MENU_ID = menus.MENU_ID and
226 menus.MENU_NAME = 'AMW_ORG_MANAGER_ROLE';
227 
228 
229 begin
230 
231 select
232 change.PARAMETER_CODE,
233 (
234 select PARAMETER_NAME
235 from ITA_SETUP_PARAMETERS_VL
236 where PARAMETER_CODE = change.PARAMETER_CODE
237 ),
238 change.SETUP_GROUP_CODE,
239 change.PK1_VALUE,
240 decode(change.SETUP_GROUP_CODE, 'FND.FND_PROFILE_OPTION_VALUES', nvl(change.PK6_VALUE, ' '), nvl(change.PK1_VALUE, ' ')),
241 change.PK2_VALUE,
242 change.RECOMMENDED_VALUE,
243 change.PRIOR_VALUE,
244 change.CURRENT_VALUE,
245 change.CHANGE_AUTHOR,
246 change.CHANGE_DATE
247 into
248 m_parameter_code,
249 m_parameter_name,
250 m_setup_group_code,
251 m_pk1_value,
252 m_org_name,
253 m_org_id,
254 m_rec_value,
255 m_prior_value,
256 m_current_value,
257 m_updated_by,
258 m_updated_on
259 from ITA_SETUP_CHANGE_HISTORY change
260 where
261 change.INSTANCE_CODE = 'CURRENT' and
262 change.CHANGE_ID = p_change_id;
263 
264 select
265 setup_gp.SETUP_GROUP_NAME,
266 (
267 select APPLICATION_NAME
268 from FND_APPLICATION_VL
269 where APPLICATION_ID = setup_gp.TABLE_APP_ID
270 ),
271 setup_gp.HIERARCHY_LEVEL,
272 decode(setup_gp.SETUP_GROUP_CODE, 'FND.FND_PROFILE_OPTION_VALUES',
273 (
274 select MEANING
275 from FND_LOOKUP_VALUES
276 where
277 LANGUAGE = USERENV('LANG') and
278 VIEW_APPLICATION_ID = 438 and
279 LOOKUP_TYPE = 'ITA_PROFILE_LEVEL_ID' and
280 LOOKUP_CODE = m_pk1_value
281 ),
282 (
283 select HIERARCHY_LEVEL_NAME
284 from ITA_SETUP_HIERARCHY_VL
285 where HIERARCHY_LEVEL_CODE = setup_gp.HIERARCHY_LEVEL
286 )
287 )
288 into
289 m_setup_group_name,
290 m_application,
291 m_org_type_code,
292 m_org_type
293 from ITA_SETUP_GROUPS_VL setup_gp
294 where
295 setup_gp.SETUP_GROUP_CODE = m_setup_group_code;
296 
297 
298 -- Ignore changes that did not occur within the context of an auditable unit.
299 m_is_audit_unit := 0;
300 
301 begin
302 select 1 into m_is_audit_unit
303 from HR_ORGANIZATION_INFORMATION org_info
304 where
305 org_info.ORGANIZATION_ID = m_org_id and
306 org_info.ORG_INFORMATION_CONTEXT = 'CLASS' and
307 org_info.ORG_INFORMATION1 = 'AMW_AUDIT_UNIT';
308 exception
309 when NO_DATA_FOUND then null;
310 end;
311 
312 if m_is_audit_unit = 0 and (m_org_type_code = 'OPERATING_UNIT' or m_org_type_code = 'INV') then
313 return;
314 end if;
315 
316 
317 -- Also, ignore profile option changes at levels other than Site.
318 select MEANING into m_org_type_test
319 from FND_LOOKUP_VALUES
320 where
321 LANGUAGE = USERENV('LANG') and
322 VIEW_APPLICATION_ID = 438 and
323 LOOKUP_TYPE = 'ITA_PROFILE_LEVEL_ID' and
324 LOOKUP_CODE = '10001';
325 
326 if m_org_type_code = 'PROFILE_OPTION' and m_org_type <> m_org_type_test then
327 return;
328 end if;
329 
330 
331 if m_org_type_code = 'OPERATING_UNIT' or m_org_type_code = 'INV' then
332 for owner in GET_OWNER_ORG(m_org_id) loop
333 SEND_NOTIFICATION_TO_OWNER(
334 0, '', '', owner.OWNER_ID, m_return_status,
335 m_setup_group_name, m_application, m_org_type, m_org_name, m_parameter_name,
336 m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);
337 end loop;
338 end if;
339 
340 
341 for control in GET_CONTROLS(m_parameter_code) loop
342 m_query := GET_HIERARCHY_ENTITIES_STRING(m_org_type_code, m_org_id, control.CONTROL_ID);
343 open m_entities_cursor for m_query;
344 
345 loop
346 fetch m_entities_cursor into m_object_type, m_pk1, m_pk2, m_pk3, m_pk4;
347 exit when m_entities_cursor%NOTFOUND;
348 
349 if m_object_type = 'RISK_ORG' then
350 for owner in GET_OWNER_RISK_ORG(m_pk1, m_pk2) loop
351 SEND_NOTIFICATION_TO_OWNER(
352 control.CONTROL_ID, control.NAME, control.SOURCE, owner.OWNER_ID, m_return_status,
353 m_setup_group_name, m_application, m_org_type, m_org_name, m_parameter_name,
354 m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);
355 end loop;
356 for owner in GET_OWNER_ORG(m_pk1) loop
357 SEND_NOTIFICATION_TO_OWNER(
358 control.CONTROL_ID, control.NAME, control.SOURCE, owner.OWNER_ID, m_return_status,
359 m_setup_group_name, m_application, m_org_type, m_org_name, m_parameter_name,
360 m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);
361 end loop;
362 end if;
363 
364 if m_object_type = 'RISK' then
365 for owner in GET_OWNER_RISK(m_pk1) loop
366 SEND_NOTIFICATION_TO_OWNER(
367 control.CONTROL_ID, control.NAME, control.SOURCE, owner.OWNER_ID, m_return_status,
368 m_setup_group_name, m_application, m_org_type, m_org_name, m_parameter_name,
369 m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);
370 end loop;
371 end if;
372 
373 if m_object_type = 'ENTITY_CONTROL' then
374 for owner in GET_OWNER_ORG(m_pk1) loop
375 SEND_NOTIFICATION_TO_OWNER(
376 control.CONTROL_ID, control.NAME, control.SOURCE, owner.OWNER_ID, m_return_status,
377 m_setup_group_name, m_application, m_org_type, m_org_name, m_parameter_name,
378 m_rec_value, m_prior_value, m_current_value, m_updated_on, m_updated_by);
379 end loop;
380 end if;
381 
382 end loop;
383 
384 end loop;
385 
386 
387 commit;
388 end SEND_NOTIFICATION_TO_ALL;
389 
390 
391 
392 
393 procedure SEND_NOTIFICATION_TO_OWNER(
394 p_control_id IN NUMBER,
395 p_control_name IN VARCHAR2,
396 p_source IN VARCHAR2,
397 p_process_owner_id IN NUMBER,
398 p_return_status OUT NOCOPY VARCHAR2,
399 p_setup_group IN VARCHAR2,
400 p_application IN VARCHAR2,
401 p_org_type IN VARCHAR2,
402 p_org IN VARCHAR2,
403 p_setup_parameter IN VARCHAR2,
404 p_rec_value IN VARCHAR2,
405 p_prior_value IN VARCHAR2,
406 p_current_value IN VARCHAR2,
407 p_updated_on IN DATE,
408 p_updated_by IN VARCHAR2) is
409 
410 m_message_subject VARCHAR2(1000);
411 m_message_body VARCHAR2(30000);
412 m_notification_id NUMBER;
413 m_return_status VARCHAR2(10);
414 m_process_owner_emp_id NUMBER;
415 m_role_name VARCHAR2(100);
416 m_role_display_name VARCHAR2(240);
417 
418 
419 cursor GET_WF_ROLES(p_orig_system_id NUMBER) is
420 select NAME, substrb(DISPLAY_NAME, 1, 360) display_name
421 from WF_ROLES
422 where
423 ORIG_SYSTEM = 'PER' and
424 ORIG_SYSTEM_ID = p_orig_system_id
425 order by STATUS, START_DATE;
426 
427 
428 begin
429 
430 FND_MESSAGE.SET_NAME('ITA', 'ITA_OWNER_NOTIFICATION_SUBJECT');
431 FND_MESSAGE.SET_TOKEN('SETUP_GROUP', p_setup_group, TRUE);
432 FND_MESSAGE.SET_TOKEN('SETUP_PARAMETER', p_setup_parameter, TRUE);
433 FND_MSG_PUB.ADD;
434 m_message_subject := FND_MSG_PUB.GET(
435 p_msg_index => FND_MSG_PUB.G_LAST,
436 p_encoded => FND_API.G_FALSE);
437 
438 FND_MESSAGE.SET_NAME('ITA', 'ITA_OWNER_NOTIFICATION_BODY');
439 FND_MESSAGE.SET_TOKEN('CONTROL', p_control_name, TRUE);
440 FND_MESSAGE.SET_TOKEN('SETUP_GROUP', p_setup_group, TRUE);
441 FND_MESSAGE.SET_TOKEN('APPLICATION', p_application, TRUE);
442 FND_MESSAGE.SET_TOKEN('ORG_TYPE', p_org_type, TRUE);
443 FND_MESSAGE.SET_TOKEN('ORG', p_org, TRUE);
444 FND_MESSAGE.SET_TOKEN('SETUP_PARAMETER', p_setup_parameter, TRUE);
445 FND_MESSAGE.SET_TOKEN('REC_VALUE', p_rec_value, TRUE);
446 FND_MESSAGE.SET_TOKEN('PRIOR_VALUE', p_prior_value, TRUE);
447 FND_MESSAGE.SET_TOKEN('CURRENT_VALUE', p_current_value, TRUE);
448 FND_MESSAGE.SET_TOKEN('UPDATED_ON', to_char(p_updated_on), TRUE);
449 FND_MESSAGE.SET_TOKEN('UPDATED_BY', p_updated_by, TRUE);
450 FND_MSG_PUB.ADD;
451 m_message_body := FND_MSG_PUB.GET(
452 p_msg_index => FND_MSG_PUB.G_LAST,
453 p_encoded => FND_API.G_FALSE);
454 
455 m_return_status := FND_API.G_RET_STS_SUCCESS;
456 
457 select EMPLOYEE_ID into m_process_owner_emp_id
458 from AMW_EMPLOYEES_CURRENT_V
459 where PARTY_ID = p_process_owner_id;
460 
461 /*
462 WF_DIRECTORY.GetRoleName(
463 p_orig_system => 'PER',
464 p_orig_system_id => m_process_owner_emp_id,
465 p_name => m_role_name,
466 p_display_name => m_role_display_name);
467 */
468 for role in GET_WF_ROLES(m_process_owner_emp_id) loop
469 m_role_name := role.NAME;
470 m_role_display_name := role.DISPLAY_NAME;
471 
472 if m_role_name is null then
473 p_return_status := FND_API.G_RET_STS_ERROR;
474 FND_MESSAGE.SET_NAME('AMW','AMW_APPR_INVALID_ROLE');
475 FND_MSG_PUB.ADD;
476 return;
477 end if;
478 
479 m_notification_id := WF_NOTIFICATION.Send(
480 role => m_role_name,
481 msg_type => 'AMWGUTIL',
482 msg_name => 'ITA_MESG');
483 
484 WF_NOTIFICATION.SetAttrText(m_notification_id, 'GEN_MSG_SUBJECT', m_message_subject);
485 WF_NOTIFICATION.SetAttrText(m_notification_id, 'GEN_MSG_BODY', m_message_body);
486 WF_NOTIFICATION.SetAttrText(m_notification_id, 'GEN_MSG_SEND_TO', m_role_name);
487 WF_NOTIFICATION.SetAttrText(m_notification_id, 'SETUP_GROUP', p_setup_group);
488 WF_NOTIFICATION.SetAttrText(m_notification_id, 'APPLICATION', p_application);
489 WF_NOTIFICATION.SetAttrText(m_notification_id, 'ORG_TYPE', p_org_type);
490 WF_NOTIFICATION.SetAttrText(m_notification_id, 'ORG', p_org);
491 WF_NOTIFICATION.SetAttrText(m_notification_id, 'SETUP_PARAMETER', p_setup_parameter);
492 WF_NOTIFICATION.SetAttrText(m_notification_id, 'REC_VALUE', p_rec_value);
493 WF_NOTIFICATION.SetAttrText(m_notification_id, 'PRIOR_VALUE', p_prior_value);
494 WF_NOTIFICATION.SetAttrText(m_notification_id, 'CURRENT_VALUE', p_current_value);
495 WF_NOTIFICATION.SetAttrText(m_notification_id, 'UPDATED_ON', p_updated_on);
496 WF_NOTIFICATION.SetAttrText(m_notification_id, 'UPDATED_BY', p_updated_by);
497 
498 WF_NOTIFICATION.Denormalize_Notification(m_notification_id);
499 
500 end loop;
501 
502 p_return_status := m_return_status;
503 
504 
505 end SEND_NOTIFICATION_TO_OWNER;
506 
507 
508 
509 
510 end ITA_NOTIFICATION_PKG;