[Home] [Help]
PACKAGE BODY: APPS.PER_PMP_MASS_NOTIFICATION
Source
4 g_package VARCHAR2 (40) := 'per_pmp_mass_notification.';
1 PACKAGE BODY per_pmp_mass_notification AS
2 /* $Header: pepmpmas.pkb 120.7 2011/12/28 06:23:43 kgowripe ship $ */
3 -- Declare Global variables
5 g_userdtls t_userdtls;
6 g_reqid NUMBER;
7
8 ----
9 PROCEDURE WRITE (p_text IN VARCHAR2)
10 IS
11 BEGIN
12 IF NVL (g_reqid, -1) = -1
13 THEN
14 hr_utility.TRACE (SUBSTR (p_text, 1, 240));
15 ELSE
16 fnd_file.put_line (fnd_file.LOG, p_text);
17 END IF;
18 END WRITE;
19
20 --
21 --
22 PROCEDURE get_user_details (
23 p_plan_id NUMBER,
24 p_effective_date DATE,
25 p_target_population VARCHAR2,
26 p_target_person_id NUMBER
27 )
28 IS
29 cnt NUMBER;
30
31 -- target person details
32 CURSOR csr_person_dtls (p_person_id NUMBER, p_effective_date DATE)
33 IS
34 SELECT ppf.person_id,
35 usr.NAME user_name,
36 ppf.full_name
37 FROM per_people_f ppf, wf_roles usr
38 WHERE ppf.person_id = p_person_id
39 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
40 AND ppf.person_id = usr.orig_system_id(+)
41 AND usr.orig_system(+) = 'PER';
42
43 --
44 -- All Eligible Workers of the plan
45 CURSOR csr_all_workers (p_plan_id IN NUMBER, p_effective_date DATE)
46 IS
47 SELECT DISTINCT ppf.person_id,
48 usr.NAME user_name,
49 ppf.full_name
50 FROM per_personal_scorecards sc,
51 per_assignments_f paf,
52 per_people_f ppf,
53 wf_roles usr
54 WHERE sc.plan_id = p_plan_id
55 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
56 AND sc.assignment_id = paf.assignment_id
57 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
58 AND paf.person_id = ppf.person_id
59 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
60 AND ppf.person_id = usr.orig_system_id(+)
61 AND usr.orig_system(+) = 'PER';
62
63 ---
64 --- All managers with atleast one eligible worker
65 --- Bug 9002011 person should be within plan population
66 CURSOR csr_all_mgrs (p_plan_id IN NUMBER, p_effective_date DATE)
67 IS
68 SELECT DISTINCT ppf.person_id,
69 usr.NAME user_name,
70 ppf.full_name
71 FROM per_personal_scorecards sc,
72 per_assignments_f paf,
73 per_people_f ppf,
74 wf_roles usr
75 WHERE sc.plan_id = p_plan_id
76 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
77 AND sc.assignment_id = paf.assignment_id
78 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
79 AND paf.supervisor_id = ppf.person_id
80 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
81 AND paf.supervisor_id = usr.orig_system_id(+)
82 AND usr.orig_system(+) = 'PER'
83 AND paf.supervisor_id IN (SELECT person_id
84 FROM per_personal_scorecards
85 WHERE plan_id = p_plan_id);
86
87 --
88 --
89 -- All managers with scorecards with a particular status
90 CURSOR csr_mgr_scs (p_plan_id IN NUMBER, p_effective_date IN DATE, p_status IN VARCHAR2)
91 IS
92 SELECT DISTINCT ppf.person_id,
93 usr.NAME user_name,
94 ppf.full_name
95 FROM per_personal_scorecards sc,
96 per_assignments_f paf,
97 per_people_f ppf,
98 wf_roles usr
99 WHERE sc.plan_id = p_plan_id
100 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
101 AND sc.status_code = p_status
102 AND sc.assignment_id = paf.assignment_id
103 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
104 AND paf.supervisor_id = ppf.person_id
105 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
106 AND paf.supervisor_id = usr.orig_system_id(+)
107 AND usr.orig_system(+) = 'PER';
108
109 --
110 -- All workers with scorecard with a particular status
111 --
115 usr.NAME user_name,
112 CURSOR csr_wkr_scs (p_plan_id IN NUMBER, p_effective_date IN DATE, p_status IN VARCHAR2)
113 IS
114 SELECT DISTINCT ppf.person_id,
116 ppf.full_name
117 FROM per_personal_scorecards sc,
118 per_assignments_f paf,
119 per_people_f ppf,
120 wf_roles usr
121 WHERE sc.plan_id = p_plan_id
122 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
123 AND sc.status_code = p_status
124 AND sc.assignment_id = paf.assignment_id
125 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
126 AND paf.person_id = ppf.person_id
127 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
128 AND paf.person_id = usr.orig_system_id(+)
129 AND usr.orig_system(+) = 'PER';
130
131 --
132 -- All workers who have atleast one incomplete scorecard
133 CURSOR csr_wkr_incomplete (p_plan_id IN NUMBER, p_effective_date IN DATE)
134 IS
135 SELECT DISTINCT ppf.person_id,
136 usr.NAME user_name,
137 ppf.full_name
138 FROM per_assignments_f paf,
139 per_personal_scorecards sc,
140 per_people_f ppf,
141 wf_roles usr
142 WHERE sc.plan_id = p_plan_id
143 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
144 AND sc.status_code <> 'PUBLISHED'
145 AND sc.status_code <> 'TRANSFER_OUT'
146 AND sc.assignment_id = paf.assignment_id
147 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
148 AND paf.person_id = ppf.person_id
149 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
150 AND paf.person_id = usr.orig_system_id(+)
151 AND usr.orig_system(+) = 'PER';
152
153 --
154 -- All managers who have atleast one incomplete scorecard
155 CURSOR csr_mgr_incomplete (p_plan_id IN NUMBER, p_effective_date IN DATE)
156 IS
157 SELECT DISTINCT ppf.person_id,
158 usr.NAME user_name,
159 ppf.full_name
160 FROM per_assignments_f paf,
161 per_personal_scorecards sc,
162 per_people_f ppf,
163 wf_roles usr
164 WHERE sc.plan_id = p_plan_id
165 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
166 AND sc.status_code <> 'PUBLISHED'
167 AND sc.status_code <> 'TRANSFER_OUT'
168 AND sc.assignment_id = paf.assignment_id
169 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
170 AND paf.supervisor_id = ppf.person_id
171 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
172 AND paf.supervisor_id = usr.orig_system_id(+)
173 AND usr.orig_system(+) = 'PER';
174
175 --
176 -- All workers who have their appraisals as ongoing
177 CURSOR csr_appr_wkr (p_plan_id IN NUMBER, p_status_code IN VARCHAR2)
178 IS
179 SELECT DISTINCT paf.person_id,
180 usr.NAME user_name,
181 ppf.full_name
182 FROM per_assignments_f paf, per_appraisals apr, per_people_f ppf, wf_roles usr
183 WHERE apr.plan_id = p_plan_id
184 AND apr.appraisal_system_status = p_status_code
185 AND apr.appraisee_person_id = paf.person_id
186 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
187 AND paf.person_id = ppf.person_id
188 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
189 AND paf.person_id = usr.orig_system_id(+)
190 AND usr.orig_system(+) = 'PER';
191
192 --
193 -- All managers who have their appraisals as ongoing
194 CURSOR csr_appr_mgr (p_plan_id IN NUMBER, p_status_code IN VARCHAR2)
195 IS
196 SELECT DISTINCT ppf.person_id,
197 usr.NAME user_name,
198 ppf.full_name
199 FROM per_assignments_f paf, per_appraisals apr, per_people_f ppf, wf_roles usr
200 WHERE apr.plan_id = p_plan_id
201 AND apr.appraisal_system_status = p_status_code
202 AND apr.main_appraiser_id = paf.person_id
203 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
204 AND paf.person_id = ppf.person_id
205 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
206 AND paf.person_id = usr.orig_system_id(+)
207 AND usr.orig_system(+) = 'PER';
208 BEGIN
209 --
210 IF p_target_person_id IS NOT NULL
211 THEN
212 OPEN csr_person_dtls (p_target_person_id, p_effective_date);
213
214 FETCH csr_person_dtls
215 BULK COLLECT INTO g_userdtls;
216
217 CLOSE csr_person_dtls;
218
219 RETURN;
220 END IF;
224 THEN
221
222 --
223 IF p_target_population = 'ALL_WRKS'
225 OPEN csr_all_workers (p_plan_id, p_effective_date);
226
227 FETCH csr_all_workers
228 BULK COLLECT INTO g_userdtls;
229
230 CLOSE csr_all_workers;
231 ELSIF p_target_population = 'ALL_MGRS'
232 THEN
233 OPEN csr_all_mgrs (p_plan_id, p_effective_date);
234
235 FETCH csr_all_mgrs
236 BULK COLLECT INTO g_userdtls;
237
238 CLOSE csr_all_mgrs;
239 -- all managers with atleast one scorecard pending for review (MGR status)
240 ELSIF p_target_population = 'SC_MGR_MGR'
241 THEN
242 OPEN csr_mgr_scs (p_plan_id, p_effective_date, 'MGR');
243
244 FETCH csr_mgr_scs
245 BULK COLLECT INTO g_userdtls;
246
247 CLOSE csr_mgr_scs;
248
249 cnt := g_userdtls.COUNT + 1;
250
251 FOR i IN csr_mgr_scs (p_plan_id, p_effective_date, 'APPROVAL')
252 LOOP
253 g_userdtls (cnt) := i;
254 cnt := cnt + 1;
255 END LOOP;
256 -- All managers with atleast one scorecard not started with manager status
257 ELSIF p_target_population = 'SC_MGR_NOT_STRTD'
258 THEN
259 OPEN csr_mgr_scs (p_plan_id, p_effective_date, 'NOT_STARTED_WITH_MGR');
260
261 FETCH csr_mgr_scs
262 BULK COLLECT INTO g_userdtls;
263
264 CLOSE csr_mgr_scs;
265 --All managers with atleast one scorecard struck in transfer status
266 ELSIF p_target_population = 'SC_MGR_TRANSFER'
267 THEN
268 OPEN csr_mgr_scs (p_plan_id, p_effective_date, 'TRANSFER');
269
270 FETCH csr_mgr_scs
271 BULK COLLECT INTO g_userdtls;
272
273 CLOSE csr_mgr_scs;
274 --All managers with atleast one scorecard in transfer-out status
275 ELSIF p_target_population = 'SC_MGR_TRANSFER_OUT'
276 THEN
277 OPEN csr_mgr_scs (p_plan_id, p_effective_date, 'TRANSFER_OUT');
278
279 FETCH csr_mgr_scs
280 BULK COLLECT INTO g_userdtls;
281
282 CLOSE csr_mgr_scs;
283 --All managers who have atleast one incomplete scorecard
284 ELSIF p_target_population = 'SC_MGR_INCOMPLETE'
285 THEN
286 OPEN csr_mgr_incomplete (p_plan_id, p_effective_date);
287
288 FETCH csr_mgr_incomplete
289 BULK COLLECT INTO g_userdtls;
290
291 CLOSE csr_mgr_incomplete;
292 -- all workers with scorecard pending for review (MGR status)
293 ELSIF p_target_population = 'SC_WKR_MGR'
294 THEN
295 OPEN csr_wkr_scs (p_plan_id, p_effective_date, 'MGR');
296
297 FETCH csr_wkr_scs
298 BULK COLLECT INTO g_userdtls;
299
300 CLOSE csr_wkr_scs;
301
302 cnt := g_userdtls.COUNT + 1;
303
304 FOR i IN csr_wkr_scs (p_plan_id, p_effective_date, 'APPROVAL')
305 LOOP
306 g_userdtls (cnt) := i;
307 cnt := cnt + 1;
308 END LOOP;
309 -- all workers with scorecard pending with worker (WKR)
310 ELSIF p_target_population = 'SC_WKR_WKR'
311 THEN
312 OPEN csr_wkr_scs (p_plan_id, p_effective_date, 'WKR');
313
314 FETCH csr_wkr_scs
315 BULK COLLECT INTO g_userdtls;
316
317 CLOSE csr_wkr_scs;
318 -- all workers with scorecard not started with worker (WKR)
319 ELSIF p_target_population = 'SC_WKR_NOT_STRTD'
320 THEN
321 OPEN csr_wkr_scs (p_plan_id, p_effective_date, 'NOT_STARTED_WITH_WKR');
322
323 FETCH csr_wkr_scs
324 BULK COLLECT INTO g_userdtls;
325
326 CLOSE csr_wkr_scs;
327 --All workers with atleast one scorecard struck in transfer status
328 ELSIF p_target_population = 'SC_WKR_TRANSFER'
329 THEN
330 OPEN csr_wkr_scs (p_plan_id, p_effective_date, 'TRANSFER');
331
332 FETCH csr_wkr_scs
333 BULK COLLECT INTO g_userdtls;
334
335 CLOSE csr_wkr_scs;
336 --All managers with atleast one scorecard transfered out
337 ELSIF p_target_population = 'SC_WKR_TRANSFER_OUT'
338 THEN
339 OPEN csr_wkr_scs (p_plan_id, p_effective_date, 'TRANSFER_OUT');
340
341 FETCH csr_wkr_scs
342 BULK COLLECT INTO g_userdtls;
343
344 CLOSE csr_wkr_scs;
345 --All workers who have atleast one incomplete scorecard
346 ELSIF p_target_population = 'SC_WKR_INCOMPLETE'
347 THEN
348 OPEN csr_wkr_incomplete (p_plan_id, p_effective_date);
349
350 FETCH csr_wkr_incomplete
351 BULK COLLECT INTO g_userdtls;
352
353 CLOSE csr_wkr_incomplete;
354 --All managers who have atleast one ongoing appraisal
355 ELSIF p_target_population = 'APR_MGR_ONGOING'
356 THEN
357 OPEN csr_appr_mgr (p_plan_id, 'ONGOING');
358
359 FETCH csr_appr_mgr
360 BULK COLLECT INTO g_userdtls;
361
362 CLOSE csr_appr_mgr;
363 --All workers who have atleast one ongoing appraisal
364 ELSIF p_target_population = 'APR_WKR_ONGOING'
365 THEN
366 OPEN csr_appr_wkr (p_plan_id, 'ONGOING');
367
368 FETCH csr_appr_wkr
369 BULK COLLECT INTO g_userdtls;
370
371 CLOSE csr_appr_wkr;
372 --All workers who have atleast one ongoing appraisal
376
373 ELSIF p_target_population = 'APR_WKR_FEEDBACK'
374 THEN
375 OPEN csr_appr_wkr (p_plan_id, 'APPRFEEDBACK');
377 FETCH csr_appr_wkr
378 BULK COLLECT INTO g_userdtls;
379
380 CLOSE csr_appr_wkr;
381 END IF;
382 --
383 END get_user_details;
384
385 --
386 --
387 PROCEDURE notify (
388 p_plan_id IN NUMBER,
389 p_effective_date IN DATE,
390 p_message_subject IN VARCHAR2,
391 p_message_body IN VARCHAR2
392 )
393 IS
394 item_key wf_notifications.item_key%TYPE;
395 --item_type wf_notifications.item_type%type default 'HRWPM';
396 l_administrator_role wf_local_roles.NAME%TYPE;
397 l_admin_person_id per_perf_mgmt_plans.administrator_person_id%TYPE;
398 to_role wf_local_roles.NAME%TYPE;
399 l_message_body VARCHAR2 (4000);
400
401 CURSOR get_wf_role (p_person_id per_all_people_f.person_id%TYPE)
402 IS
403 SELECT NAME
404 FROM wf_roles
405 WHERE orig_system_id = p_person_id AND orig_system = 'PER';
406
407 CURSOR csr_appl_name (p_message_name VARCHAR2)
408 IS
409 SELECT a.application_short_name
410 FROM fnd_new_messages m, fnd_application a
411 WHERE m.message_name = p_message_name
412 AND m.language_code = USERENV ('LANG')
413 AND m.application_id = a.application_id;
414
415 l_appl_short_name VARCHAR2 (30);
416 BEGIN
417 WRITE ('Inside Notify');
418
419 -- get administrartor role as notification sender
420 SELECT administrator_person_id
421 INTO l_admin_person_id
422 FROM per_perf_mgmt_plans
423 WHERE plan_id = p_plan_id;
424
425 OPEN get_wf_role (l_admin_person_id);
426
427 FETCH get_wf_role
428 INTO l_administrator_role;
429
430 CLOSE get_wf_role;
431
432 WRITE ('admin role (From Role) : ' || l_administrator_role);
433
434 IF l_administrator_role IS NULL
435 THEN
436 WRITE ('Plan Administrator''s role is not defined. Cannot proceed further');
437 RETURN;
438 END IF;
439
440 --
441 FOR i IN g_userdtls.FIRST .. g_userdtls.LAST
442 LOOP
443 IF g_userdtls (i).user_name IS NULL
444 THEN
445 WRITE ( 'Could not send notification for:'
446 || g_userdtls (i).user_display_name
447 || ' as no user exists'
448 );
449 ELSE
450 BEGIN
451 WRITE ( 'sending notification for '
452 || g_userdtls (i).user_display_name
453 || ' :user_name: '
454 || g_userdtls (i).user_name
455 || 'person_id : '
456 || g_userdtls (i).person_id
457 );
458 to_role := g_userdtls (i).user_name;
459 WRITE ('got role to send: ' || to_role);
460
461 -- initiallize the wf process
462 SELECT hr_workflow_item_key_s.NEXTVAL
463 INTO item_key
464 FROM DUAL;
465
466 --
467 OPEN csr_appl_name (p_message_body);
468
469 FETCH csr_appl_name
470 INTO l_appl_short_name;
471
472 CLOSE csr_appl_name;
473
474 fnd_message.set_name (l_appl_short_name, p_message_body);
475 l_message_body := fnd_message.get;
476
477 IF l_message_body IS NULL
478 THEN
479 l_message_body := p_message_body;
480 END IF;
481
482 wf_engine.createprocess (itemtype => 'HRWPM',
483 itemkey => item_key,
484 process => 'PER_PERF_MGMT_NTF_POPULATION',
485 user_key => item_key,
486 owner_role => l_administrator_role
487 );
488 -- set the required attributes
489 wf_engine.setitemattrtext ('HRWPM',
490 item_key,
491 'MASS_NTF_FROM_ROLE',
492 l_administrator_role
493 );
494 wf_engine.setitemattrtext ('HRWPM', item_key, 'MASS_NTF_TO_ROLE', to_role);
495 wf_engine.setitemattrtext ('HRWPM', item_key, 'MASS_NTF_MSG_TEXT', l_message_body);
496 wf_engine.setitemattrtext ('HRWPM',
497 item_key,
498 'MASS_NTF_MSG_HEADER',
499 p_message_subject
500 );
501 wf_engine.startprocess (itemtype => 'HRWPM', itemkey => item_key);
502 WRITE ( 'Started process with item_key: '
503 || item_key
504 || ' for person '
505 || g_userdtls (i).user_display_name
506 );
507 EXCEPTION
508 WHEN OTHERS
509 THEN
513 );
510 WRITE ( 'Failed to send ntf for user '
511 || g_userdtls (i).user_display_name
512 || SUBSTR (SQLERRM, 0, 200)
514 END;
515 END IF; -- user_name not null
516 --
517 END LOOP;
518 --
519 END notify;
520
521 --
522 --
523 PROCEDURE mass_notify (
524 errbuf OUT NOCOPY VARCHAR2,
525 retcode OUT NOCOPY NUMBER,
526 p_plan_id IN NUMBER,
527 p_effective_date IN VARCHAR2,
528 p_message_subject IN VARCHAR2,
529 p_message_body IN VARCHAR2,
530 p_target_population IN VARCHAR2 DEFAULT NULL,
531 p_target_person_id IN NUMBER DEFAULT NULL,
532 p_person_selection_rule IN NUMBER DEFAULT NULL
533 )
534 IS
535 l_effective_date DATE;
536 l_proc VARCHAR2 (80) := 'mass_notify' || g_package;
537 l_message VARCHAR2 (2000);
538 BEGIN
539 g_reqid := fnd_global.conc_request_id;
540 WRITE ('Entering : ' || l_proc);
541 WRITE ('Parameter values:');
542 WRITE ('-----------------');
543 WRITE ('p_plan_id : ' || p_plan_id);
544 WRITE ('p_target_population : ' || p_target_population);
545 WRITE ('p_target_person_id : ' || p_target_person_id);
546 WRITE ('-----------------');
547 hr_api.mandatory_arg_error (p_api_name => 'per_pmp_mass_notification.mass_notify',
548 p_argument => 'p_plan_id',
549 p_argument_value => p_plan_id
550 );
551 hr_api.mandatory_arg_error (p_api_name => 'per_pmp_mass_notification.mass_notify',
552 p_argument => 'p_effective_date',
553 p_argument_value => p_effective_date
554 );
555 hr_api.mandatory_arg_error (p_api_name => 'per_pmp_mass_notification.mass_notify',
556 p_argument => 'p_message_subject',
557 p_argument_value => p_message_subject
558 );
559 hr_api.mandatory_arg_error (p_api_name => 'per_pmp_mass_notification.mass_notify',
560 p_argument => 'p_message_body',
561 p_argument_value => p_message_body
562 );
563
564 --
565 IF p_target_population IS NULL
566 AND p_target_person_id IS NULL
567 AND p_person_selection_rule IS NULL
568 THEN
569 fnd_message.set_name ('PER', 'PER_NO_POPULATION_TO_NOTIFY');
570 l_message := fnd_message.get;
571 WRITE (l_message);
572 fnd_message.raise_error;
573 END IF;
574
575 --
576 l_effective_date := fnd_date.canonical_to_date (p_effective_date);
577
578 --
579 IF p_target_population IS NOT NULL AND hr_api.not_exists_in_hr_lookups (l_effective_date,
580 'PER_PERF_MGMT_NTF_POPULATION',
581 p_target_population
582 )
583 THEN
584 fnd_message.set_name ('PER', 'PER_PMP_INVALID_POPULATION');
585 fnd_message.raise_error;
586 END IF;
587
588 --
589 g_userdtls.DELETE;
590 WRITE ('calling get_user_details');
591 --
592 get_user_details (p_plan_id => p_plan_id,
593 p_effective_date => l_effective_date,
594 p_target_population => p_target_population,
595 p_target_person_id => p_target_person_id
596 );
597 WRITE ('No of users to be notified:' || g_userdtls.COUNT);
598
599 IF g_userdtls.COUNT > 0
600 THEN
601 notify (p_plan_id => p_plan_id,
602 p_effective_date => l_effective_date,
603 p_message_subject => p_message_subject,
604 p_message_body => p_message_body
605 );
606 END IF;
607 EXCEPTION
608 WHEN OTHERS
609 THEN
610 errbuf := SQLERRM;
611 retcode := 2;
612 WRITE (errbuf);
613 RAISE;
614 END mass_notify;
615 --
616 END per_pmp_mass_notification;