1 PACKAGE BODY per_pmp_mass_notification AS
2 /* $Header: pepmpmas.pkb 120.0.12010000.4 2008/11/18 14:09:18 kgowripe noship $ */
3 -- Declare Global variables
4 g_package varchar2(40) := 'per_pmp_mass_notification.';
5 g_userdtls t_userdtls;
6 g_reqid NUMBER;
7 ----
8 PROCEDURE write(p_text IN VARCHAR2) IS
9 BEGIN
10 IF NVL(g_reqid,-1) = -1 THEN
11 hr_utility.trace(SUBSTR(p_text,1,240));
12 ELSE
13 fnd_file.put_line(FND_FILE.log,p_text);
14 END IF;
15 END write;
16 --
17 --
18 PROCEDURE get_user_details(p_plan_id NUMBER
19 ,p_effective_date DATE
20 ,p_target_population VARCHAR2
21 ,p_target_person_id NUMBER) IS
22 -- target person details
23 CURSOR csr_person_dtls(p_person_id NUMBER,
24 p_effective_date DATE) IS
25 SELECT ppf.person_id,
26 usr.name user_name,
27 ppf.full_name
28 FROM per_people_f ppf
29 ,wf_roles usr
30 WHERE ppf.person_id = p_person_id
31 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
32 AND ppf.person_id = usr.orig_system_id(+)
33 AND usr.orig_system(+) = 'PER';
34
35 --
36 -- All Eligible Workers of the plan
37 CURSOR csr_all_workers(p_plan_id IN NUMBER,
38 p_effective_date DATE) IS
39 SELECT distinct ppf.person_id,
40 usr.name user_name,
41 ppf.full_name
42 FROM per_personal_scorecards sc
43 ,per_assignments_f paf
44 ,per_people_f ppf
45 ,wf_roles usr
46 WHERE sc.plan_id = p_plan_id
47 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
48 AND sc.assignment_id = paf.assignment_id
49 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
50 AND paf.person_id = ppf.person_id
51 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
52 AND ppf.person_id = usr.orig_system_id(+)
53 AND usr.orig_system(+) = 'PER';
54 ---
55 --- All managers with atleast one eligible worker
56 CURSOR csr_all_mgrs(p_plan_id IN NUMBER,
57 p_effective_date DATE) IS
58 SELECT DISTINCT ppf.person_id,
59 usr.name user_name,
60 ppf.full_name
61 FROM per_personal_scorecards sc
62 ,per_assignments_f paf
63 ,per_people_f ppf
64 ,wf_roles usr
65 WHERE sc.plan_id = p_plan_id
66 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
67 AND sc.assignment_id = paf.assignment_id
68 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
69 AND paf.supervisor_id = ppf.person_id
70 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
71 AND paf.supervisor_id = usr.orig_system_id(+)
72 AND usr.orig_system(+) = 'PER';
73 --
74 --
75 -- All managers with scorecards with a particular status
76 CURSOR csr_mgr_scs(p_plan_id IN NUMBER
77 ,p_effective_date IN DATE
78 ,p_status IN VARCHAR2) IS
79 SELECT distinct ppf.person_id
80 ,usr.name user_name
81 ,ppf.full_name
82 FROM per_personal_scorecards sc
83 ,per_assignments_f paf
84 ,per_people_f ppf
85 ,wf_roles usr
86 WHERE sc.plan_id = p_plan_id
87 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
88 AND sc.status_code = p_status
89 AND sc.assignment_id = paf.assignment_id
90 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
91 AND paf.supervisor_id = ppf.person_id
92 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
93 AND paf.supervisor_id = usr.orig_system_id(+)
94 AND usr.orig_system(+) = 'PER';
95 --
96 -- All workers with scorecard with a particular status
97 --
98 CURSOR csr_wkr_scs(p_plan_id IN NUMBER
99 ,p_effective_date IN DATE
100 ,p_status IN VARCHAR2) IS
101 SELECT distinct ppf.person_id
102 ,usr.name user_name
103 ,ppf.full_name
104 FROM per_personal_scorecards sc
105 ,per_assignments_f paf
106 ,per_people_f ppf
107 ,wf_roles usr
108 WHERE sc.plan_id = p_plan_id
109 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
110 AND sc.status_code = p_status
111 AND sc.assignment_id = paf.assignment_id
112 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
113 AND paf.person_id = ppf.person_id
114 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
115 AND paf.person_id = usr.orig_system_id(+)
116 AND usr.orig_system(+) = 'PER';
117 --
118 -- All workers who have atleast one incomplete scorecard
119 CURSOR csr_wkr_incomplete(p_plan_id IN NUMBER
120 ,p_effective_date IN DATE) IS
121 SELECT DISTINCT ppf.person_id
122 ,usr.name user_name
123 ,ppf.full_name
124 FROM per_assignments_f paf
125 ,per_personal_scorecards sc
126 ,per_people_f ppf
127 ,wf_roles usr
128 WHERE sc.plan_id = p_plan_id
129 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
130 AND sc.status_code <> 'PUBLISHED'
131 AND sc.assignment_id = paf.assignment_id
132 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
133 AND paf.person_id = ppf.person_id
134 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
135 AND paf.person_id = usr.orig_system_id(+)
136 AND usr.orig_system(+) = 'PER';
137 --
138 -- All managers who have atleast one incomplete scorecard
139 CURSOR csr_mgr_incomplete(p_plan_id IN NUMBER
140 ,p_effective_date IN DATE) IS
141 SELECT DISTINCT ppf.person_id
142 ,usr.name user_name
143 ,ppf.full_name
144 FROM per_assignments_f paf
145 ,per_personal_scorecards sc
146 ,per_people_f ppf
147 ,wf_roles usr
148 WHERE sc.plan_id = p_plan_id
149 AND p_effective_date BETWEEN sc.start_date AND sc.end_date
150 AND sc.status_code <> 'PUBLISHED'
151 AND sc.assignment_id = paf.assignment_id
152 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
153 AND paf.supervisor_id = ppf.person_id
154 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
155 AND paf.supervisor_id = usr.orig_system_id(+)
156 AND usr.orig_system(+) = 'PER';
157 --
158 -- All workers who have their appraisals as ongoing
159 CURSOR csr_appr_wkr(p_plan_id IN NUMBER
160 ,p_status_code IN VARCHAR2) IS
161 SELECT DISTINCT paf.person_id
162 ,usr.name user_name
163 ,ppf.full_name
164 FROM per_assignments_f paf
165 ,per_appraisals apr
166 ,per_people_f ppf
167 ,wf_roles usr
168 WHERE apr.plan_id = p_plan_id
169 AND apr.appraisal_system_status = p_status_code
170 AND apr.appraisee_person_id = paf.person_id
171 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
172 AND paf.person_id = ppf.person_id
173 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
174 AND paf.person_id = usr.orig_system_id(+)
175 AND usr.orig_system(+) = 'PER';
176 --
177 -- All managers who have their appraisals as ongoing
178 CURSOR csr_appr_mgr(p_plan_id IN NUMBER
179 ,p_status_code IN VARCHAR2) IS
180 SELECT DISTINCT ppf.person_id
181 ,usr.name user_name
182 ,ppf.full_name
183 FROM per_assignments_f paf
184 ,per_appraisals apr
185 ,per_people_f ppf
186 ,wf_roles usr
187 WHERE apr.plan_id = p_plan_id
188 AND apr.appraisal_system_status = p_status_code
189 AND apr.main_appraiser_id = paf.person_id
190 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
191 AND paf.person_id = ppf.person_id
192 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
193 AND paf.person_id = usr.orig_system_id(+)
194 AND usr.orig_system(+) = 'PER';
195
196 BEGIN
197 --
198 IF p_target_person_id IS NOT NULL THEN
199 OPEN csr_person_dtls(p_target_person_id,p_effective_date);
200 FETCH csr_person_dtls BULK COLLECT INTO g_userdtls;
201 CLOSE csr_person_dtls;
202 RETURN;
203 END IF;
204 --
205 IF p_target_population = 'ALL_WRKS' THEN
206 OPEN csr_all_workers(p_plan_id,p_effective_date);
207 FETCH csr_all_workers BULK COLLECT INTO g_userdtls;
208 CLOSE csr_all_workers;
209 ELSIF p_target_population = 'ALL_MGRS' THEN
210 OPEN csr_all_mgrs(p_plan_id,p_effective_date);
211 FETCH csr_all_mgrs BULK COLLECT INTO g_userdtls;
212 CLOSE csr_all_mgrs;
213 -- all managers with atleast one scorecard pending for review (MGR status)
214 ELSIF p_target_population = 'SC_MGR_MGR' THEN
215 open csr_mgr_scs(p_plan_id,p_effective_date,'MGR');
216 FETCH csr_mgr_scs BULK COLLECT INTO g_userdtls;
217 CLOSE csr_mgr_scs;
218 -- All managers with atleast one scorecard not started with manager status
219 ELSIF p_target_population = 'SC_MGR_NOT_STRTD' THEN
220 open csr_mgr_scs(p_plan_id,p_effective_date,'NOT_STARTED_WITH_MGR');
221 FETCH csr_mgr_scs BULK COLLECT INTO g_userdtls;
222 CLOSE csr_mgr_scs;
223 --All managers with atleast one scorecard struck in transfer status
224 ELSIF p_target_population = 'SC_MGR_TRANSFER' THEN
225 open csr_mgr_scs(p_plan_id,p_effective_date,'TRANSFER');
226 FETCH csr_mgr_scs BULK COLLECT INTO g_userdtls;
227 CLOSE csr_mgr_scs;
228 --All managers with atleast one scorecard in transfer-out status
229 ELSIF p_target_population = 'SC_MGR_TRANSFER_OUT' THEN
230 open csr_mgr_scs(p_plan_id,p_effective_date,'TRANSFER_OUT');
231 FETCH csr_mgr_scs BULK COLLECT INTO g_userdtls;
232 CLOSE csr_mgr_scs;
233 --All managers who have atleast one incomplete scorecard
234 ELSIF p_target_population = 'SC_MGR_INCOMPLETE' THEN
235 open csr_mgr_incomplete(p_plan_id,p_effective_date);
236 FETCH csr_mgr_incomplete BULK COLLECT INTO g_userdtls;
237 CLOSE csr_mgr_incomplete;
238 -- all workers with scorecard pending for review (MGR status)
239 ELSIF p_target_population = 'SC_WKR_MGR' THEN
240 open csr_wkr_scs(p_plan_id,p_effective_date,'MGR');
241 FETCH csr_wkr_scs BULK COLLECT INTO g_userdtls;
242 CLOSE csr_wkr_scs;
243 -- all workers with scorecard pending with worker (WKR)
244 ELSIF p_target_population = 'SC_WKR_WKR' THEN
245 open csr_wkr_scs(p_plan_id,p_effective_date,'WKR');
246 FETCH csr_wkr_scs BULK COLLECT INTO g_userdtls;
247 CLOSE csr_wkr_scs;
248 -- all workers with scorecard not started with worker (WKR)
249 ELSIF p_target_population = 'SC_WKR_NOT_STRTD' THEN
250 open csr_wkr_scs(p_plan_id,p_effective_date,'NOT_STARTED_WITH_WKR');
251 FETCH csr_wkr_scs BULK COLLECT INTO g_userdtls;
252 CLOSE csr_wkr_scs;
253 --All workers with atleast one scorecard struck in transfer status
254 ELSIF p_target_population = 'SC_WKR_TRANSFER' THEN
255 open csr_wkr_scs(p_plan_id,p_effective_date,'TRANSFER');
256 FETCH csr_wkr_scs BULK COLLECT INTO g_userdtls;
257 CLOSE csr_wkr_scs;
258 --All managers with atleast one scorecard transfered out
259 ELSIF p_target_population = 'SC_WKR_TRANSFER_OUT' THEN
260 open csr_wkr_scs(p_plan_id,p_effective_date,'TRANSFER_OUT');
261 FETCH csr_wkr_scs BULK COLLECT INTO g_userdtls;
262 CLOSE csr_wkr_scs;
263 --All workers who have atleast one incomplete scorecard
264 ELSIF p_target_population = 'SC_WKR_INCOMPLETE' THEN
265 open csr_wkr_incomplete(p_plan_id,p_effective_date);
266 FETCH csr_wkr_incomplete BULK COLLECT INTO g_userdtls;
267 CLOSE csr_wkr_incomplete;
268 --All managers who have atleast one ongoing appraisal
269 ELSIF p_target_population = 'APR_MGR_ONGOING' THEN
270 OPEN csr_appr_mgr(p_plan_id,'ONGOING');
271 FETCH csr_appr_mgr BULK COLLECT INTO g_userdtls;
272 CLOSE csr_appr_mgr;
273 --All workers who have atleast one ongoing appraisal
274 ELSIF p_target_population = 'APR_WKR_ONGOING' THEN
275 OPEN csr_appr_wkr(p_plan_id,'ONGOING');
276 FETCH csr_appr_wkr BULK COLLECT INTO g_userdtls;
277 CLOSE csr_appr_wkr;
278 --All workers who have atleast one ongoing appraisal
279 ELSIF p_target_population = 'APR_WKR_FEEDBACK' THEN
280 OPEN csr_appr_wkr(p_plan_id,'APPRFEEDBACK');
281 FETCH csr_appr_wkr BULK COLLECT INTO g_userdtls;
282 CLOSE csr_appr_wkr;
283 END IF;
284 --
285 END get_user_details;
286 --
287 --
288 PROCEDURE notify(p_plan_id IN NUMBER
289 ,p_effective_date IN DATE
290 ,p_message_subject IN VARCHAR2
291 ,p_message_body IN VARCHAR2) IS
292 item_key wf_notifications.item_key%TYPE;
293 --item_type wf_notifications.item_type%type default 'HRWPM';
294 l_administrator_role wf_local_roles.name%TYPE;
295 l_admin_person_id per_perf_mgmt_plans.ADMINISTRATOR_PERSON_ID%TYPE;
296 to_role wf_local_roles.name%TYPE;
297 l_message_body VARCHAR2(4000);
298 cursor get_wf_role(p_person_id per_all_people_f.person_id%TYPE)
299 is
300 select name from wf_roles
301 where orig_system_id = p_person_id
302 and orig_system = 'PER';
303 CURSOR csr_appl_name(p_message_name VARCHAR2) IS
304 SELECT a.application_short_name
305 FROM fnd_new_messages m
306 ,fnd_application a
307 WHERE m.message_name = p_message_name
308 AND m.language_code = USERENV('LANG')
309 AND m.application_id = a.application_id;
310 l_appl_short_name varchar2(30);
311
312 BEGIN
313 WRITE('Inside Notify');
314 -- get administrartor role as notification sender
315 SELECT ADMINISTRATOR_PERSON_ID INTO l_admin_person_id FROM per_perf_mgmt_plans WHERE plan_id =p_plan_id;
316 OPEN get_wf_role(l_admin_person_id);
317 fetch get_wf_role into l_administrator_role;
318 close get_wf_role;
319 WRITE('admin role (From Role) : ' || l_administrator_role);
320 IF l_administrator_role IS NULL THEN
321 WRITE('Plan Administrator''s role is not defined. Cannot proceed further');
322 RETURN;
323 END IF;
324 --
325 FOR i IN g_userdtls.FIRST .. g_userdtls.LAST
326 LOOP
327
328 IF g_userdtls(i).user_name IS NULL THEN
329 WRITE('Could not send notification for:'||g_userdtls(i).user_display_name||' as no user exists');
330 ELSE
331 BEGIN
332 WRITE('sending notification for '||g_userdtls(i).user_display_name||' :user_name: '||g_userdtls(i).user_name || 'person_id : '||g_userdtls(i).person_id);
333
334 to_role := g_userdtls(i).user_name;
335 WRITE('got role to send: ' || to_role);
336 -- initiallize the wf process
337 SELECT HR_workflow_item_key_s.NEXTVAL
338 INTO item_key
339 FROM dual;
340 --
341 OPEN csr_appl_name(p_message_body);
342 FETCH csr_appl_name INTO l_appl_short_name;
343 CLOSE csr_appl_name;
344 fnd_message.set_name(l_appl_short_name,p_message_body);
345 l_message_body := fnd_message.get;
346 IF l_message_body IS NULL THEN
347 l_message_body := p_message_body;
348 END IF;
349 wf_engine.CreateProcess (itemtype => 'HRWPM',
350 itemkey => item_key,
351 process => 'PER_PERF_MGMT_NTF_POPULATION',
352 user_key => item_key,
353 owner_role => l_administrator_role);
354 -- set the required attributes
355 wf_engine.setitemattrtext('HRWPM',item_key,'MASS_NTF_FROM_ROLE',l_administrator_role);
356 wf_engine.setitemattrtext('HRWPM',item_key,'MASS_NTF_TO_ROLE',to_role);
357 wf_engine.setitemattrtext('HRWPM',item_key,'MASS_NTF_MSG_TEXT',l_message_body);
358 wf_engine.setitemattrtext('HRWPM',item_key,'MASS_NTF_MSG_HEADER',p_message_subject);
359 wf_engine.StartProcess(itemtype => 'HRWPM', itemkey => item_key );
360 WRITE ( 'Started process with item_key: '||item_key||' for person '||g_userdtls(i).user_display_name);
361 EXCEPTION
362 WHEN others THEN
363 WRITE('Failed to send ntf for user '||g_userdtls(i).user_display_name || substr(sqlerrm,0,200));
364 END;
365 END IF; -- user_name not null
366 --
367 END LOOP;
368 --
369 END notify;
370 --
371 --
372 PROCEDURE mass_notify(errbuf out nocopy varchar2
373 ,retcode out nocopy number
374 ,p_plan_id IN NUMBER
375 ,p_effective_date IN Varchar2
376 ,p_message_subject IN VARCHAR2
377 ,p_message_body IN VARCHAR2
378 ,p_target_population IN VARCHAR2 Default NULL
379 ,p_target_person_id IN NUMBER DEFAULT NULL
380 ,p_person_selection_rule IN NUMBER DEFAULT NULL) IS
381 l_effective_date DATE;
382 l_proc varchar2(80) := 'mass_notify'||g_package;
383 l_message Varchar2(2000);
384 BEGIN
385 g_reqid := FND_GLOBAL.conc_request_id;
386 WRITE('Entering : '||l_proc);
387 WRITE('Parameter values:');
388 WRITE('-----------------');
389 WRITE('p_plan_id : '||p_plan_id);
390 WRITE('p_target_population : '||p_target_population);
391 WRITE('p_target_person_id : '||p_target_person_id);
392 WRITE('-----------------');
393 hr_api.mandatory_arg_error(p_api_name => 'per_pmp_mass_notification.mass_notify'
394 ,p_argument =>'p_plan_id'
395 ,p_argument_value => p_plan_id);
396 hr_api.mandatory_arg_error(p_api_name => 'per_pmp_mass_notification.mass_notify'
397 ,p_argument =>'p_effective_date'
398 ,p_argument_value => p_effective_date);
399 hr_api.mandatory_arg_error(p_api_name => 'per_pmp_mass_notification.mass_notify'
400 ,p_argument =>'p_message_subject'
401 ,p_argument_value => p_message_subject);
402 hr_api.mandatory_arg_error(p_api_name => 'per_pmp_mass_notification.mass_notify'
403 ,p_argument =>'p_message_body'
404 ,p_argument_value => p_message_body);
405 --
406 IF p_target_population IS NULL AND p_target_person_id IS NULL AND p_person_selection_rule IS NULL THEN
407 fnd_message.set_name('PER','PER_NO_POPULATION_TO_NOTIFY');
408 l_message := fnd_message.get;
409 write(l_message);
410 fnd_message.raise_error;
411 END IF;
412 --
413 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
414 --
415 IF hr_api.not_exists_in_hr_lookups
416 (l_effective_date, 'PER_PERF_MGMT_NTF_POPULATION',p_target_population) THEN
417 fnd_message.set_name('PER','PER_PMP_INVALID_POPULATION');
418 fnd_message.raise_error;
419 END IF;
420 --
421 g_userdtls.DELETE;
422 write('calling get_user_details');
423 --
424 get_user_details(p_plan_id => p_plan_id
425 ,p_effective_date => L_effective_date
426 ,p_target_population => p_target_population
427 ,p_target_person_id => p_target_person_id);
428 write('No of users to be notified:'||g_userdtls.COUNT);
429 IF g_userdtls.COUNT > 0 THEN
430 notify(p_plan_id => p_plan_id
431 ,p_effective_date => l_effective_date
432 ,p_message_subject => p_message_subject
433 ,p_message_body => p_message_body);
434 END IF;
435 EXCEPTION
436 WHEN OTHERS THEN
437 errbuf := SQLERRM;
438 retcode := 2;
439 Write(errbuf);
440 RAISE;
441 END mass_notify;
442 --
443 END per_pmp_mass_notification;
[Home] [Help]