[Home] [Help]
PACKAGE BODY: APPS.HR_WPM_NTF_UTIL
Source
1 PACKAGE BODY hr_wpm_ntf_util AS
2 /* $Header: hrwpmntf.pkb 120.22.12020000.4 2012/11/09 06:21:50 sussuman ship $*/
3
4 FUNCTION send_notification (
5 action_type VARCHAR2,
6 score_card_id per_personal_scorecards.scorecard_id%TYPE,
7 to_or_from_mgr_ntf VARCHAR2,
8 reason VARCHAR2
9 )
10 RETURN NUMBER
11 IS
12 -- to be clarified for orig_system
13 CURSOR get_role (person_id per_all_people_f.person_id%TYPE)
14 IS
15 SELECT wf.NAME role_name
16 FROM wf_roles wf
17 WHERE wf.orig_system = 'PER' AND wf.orig_system_id = person_id;
18
19 CURSOR get_global_name (p_person_id per_all_people_f.person_id%TYPE)
20 IS
21 SELECT NVL (GLOBAL_NAME, first_name || ', ' || last_name)
22 FROM per_all_people_f
23 WHERE person_id = p_person_id
24 AND TRUNC (SYSDATE) BETWEEN effective_start_date AND effective_end_date;
25
26 CURSOR get_scorecard_info (p_scorecard_id per_personal_scorecards.scorecard_id%TYPE)
27 IS
28 SELECT *
29 FROM per_personal_scorecards
30 WHERE scorecard_id = p_scorecard_id;
31
32 CURSOR get_sc_manager_person_id (p_sc_id NUMBER)
33 IS
34 SELECT paf.supervisor_id
35 FROM per_all_assignments_f paf, per_personal_scorecards sc
36 WHERE sc.scorecard_id = p_sc_id
37 AND sc.assignment_id = paf.assignment_id
38 AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
39 AND paf.assignment_type IN ('E', 'C');
40
41 CURSOR get_sc_person_id (p_scorecard_id per_personal_scorecards.scorecard_id%TYPE)
42 IS
43 SELECT person_id
44 FROM per_personal_scorecards
45 WHERE scorecard_id = p_scorecard_id;
46
47 CURSOR get_sc_plan_info (p_sc_id per_perf_mgmt_plans.plan_id%TYPE)
48 IS
49 SELECT PLAN.hierarchy_type_code, PLAN.supervisor_id, PLAN.supervisor_assignment_id
50 FROM per_personal_scorecards sc, per_perf_mgmt_plans PLAN
51 WHERE sc.scorecard_id = p_sc_id AND sc.plan_id = PLAN.plan_id;
52
53 CURSOR get_pos_manager_id (p_sc_id per_personal_scorecards.scorecard_id%TYPE)
54 IS
55 SELECT sup.person_id
56 FROM per_personal_scorecards sc,
57 per_all_assignments_f wrk,
58 per_pos_structure_elements ppse,
59 per_all_assignments_f sup,
60 per_perf_mgmt_plans plans
61 WHERE sc.scorecard_id = p_sc_id
62 AND plans.plan_id = sc.plan_id
63 AND sc.assignment_id = wrk.assignment_id
64 AND TRUNC (SYSDATE) BETWEEN wrk.effective_start_date AND wrk.effective_end_date
65 AND wrk.position_id = ppse.subordinate_position_id
66 AND ppse.pos_structure_version_id = plans.pos_structure_version_id
67 AND ppse.business_group_id = sup.business_group_id
68 AND ppse.parent_position_id = sup.position_id
69 AND TRUNC (SYSDATE) BETWEEN sup.effective_start_date AND sup.effective_end_date
70 AND ( ( plans.assignment_types_code IN ('E', 'C')
71 AND sup.assignment_type = plans.assignment_types_code
72 )
73 OR ( plans.assignment_types_code = 'EC' AND sup.assignment_type IN ('E', 'C')
74 ) )
75 AND EXISTS (
76 SELECT 'x'
77 FROM per_person_type_usages_f ptu, per_person_types ppt
78 WHERE ptu.person_id = sup.person_id
79 AND TRUNC (SYSDATE) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
80 AND ptu.person_type_id = ppt.person_type_id
81 AND ppt.system_person_type IN ('EMP', 'CWK', 'EMP_APL', 'CWK_APL'));
82
83 ln_notification_id NUMBER;
84 from_role wf_local_roles.NAME%TYPE DEFAULT NULL;
85 to_role wf_local_roles.NAME%TYPE DEFAULT NULL;
86 from_name per_all_people_f.GLOBAL_NAME%TYPE;
87 to_name per_all_people_f.GLOBAL_NAME%TYPE;
88 from_role_not_exists EXCEPTION;
89 to_role_not_exists EXCEPTION;
90 l_sc_info per_personal_scorecards%ROWTYPE;
91 no_score_card_with_this_id EXCEPTION;
92 lv_subject VARCHAR2 (200) DEFAULT NULL;
93 tlnt_mgmt_rel_apps_fn VARCHAR2 (200) DEFAULT NULL;
94 mgr_person_id per_all_people_f.person_id%TYPE DEFAULT NULL;
95 sc_person_id per_personal_scorecards.person_id%TYPE DEFAULT NULL;
96 from_person_id per_all_people_f.person_id%TYPE DEFAULT NULL;
100 l_top_supervisor_id per_perf_mgmt_plans.supervisor_id%TYPE;
97 to_person_id per_all_people_f.person_id%TYPE DEFAULT NULL;
98 mesg_name fnd_new_messages.message_text%TYPE DEFAULT NULL;
99 l_hier_code per_perf_mgmt_plans.hierarchy_type_code%TYPE;
101 l_top_sup_asg_id per_perf_mgmt_plans.supervisor_assignment_id%TYPE;
102 BEGIN
103
104 /* In case Employee to Manager we need to get the Manager's Person Id */
105 OPEN get_sc_plan_info (score_card_id);
106
107 FETCH get_sc_plan_info
108 INTO l_hier_code,l_top_supervisor_id,l_top_sup_asg_id ;
109
110 CLOSE get_sc_plan_info;
111
112 IF (l_hier_code = 'ORG')
113 THEN
114 l_hier_code := 'SUP';
115 END IF;
116
117 OPEN get_scorecard_info (score_card_id);
118
119 FETCH get_scorecard_info
120 INTO l_sc_info;
121
122 CLOSE get_scorecard_info;
123
124 IF (l_sc_info.scorecard_name IS NULL)
125 THEN
126 RAISE no_score_card_with_this_id;
127 END IF;
128 -- normally this procedure is invoked only when there is supervisor and employee
129 -- in case of top employee this is not called.
130 --- Added for bug#10246515 DO NOT SEND NOTIFICATION FOR THE TOP SUPERVISOR
131 IF (l_sc_info.person_id = NVL(l_top_supervisor_id,-1) OR l_sc_info.assignment_id = NVL(l_top_sup_asg_id,-1) ) AND to_or_from_mgr_ntf = 'EMP_TO_MGR' THEN
132 UPDATE per_personal_scorecards
133 SET status_code = 'PUBLISHED'
134 WHERE scorecard_id = score_card_id;
135 COMMIT;
136 RETURN success;
137 ELSE
138 IF (l_hier_code = 'SUP' OR l_hier_code = 'SUP_ASG')
139 THEN
140 OPEN get_sc_manager_person_id (score_card_id);
141
142 FETCH get_sc_manager_person_id
143 INTO mgr_person_id;
144
145 CLOSE get_sc_manager_person_id;
146 ELSIF (l_hier_code = 'POS')
147 THEN
148 OPEN get_pos_manager_id (score_card_id);
149
150 FETCH get_pos_manager_id
151 INTO mgr_person_id;
152
153 CLOSE get_pos_manager_id;
154 END IF;
155
156 OPEN get_sc_person_id (score_card_id);
157
158 FETCH get_sc_person_id
159 INTO sc_person_id;
160
161 CLOSE get_sc_person_id;
162
163 IF (to_or_from_mgr_ntf = 'MGR_TO_EMP')
164 THEN
165 from_person_id := mgr_person_id;
166 to_person_id := sc_person_id;
167 ELSIF (to_or_from_mgr_ntf = 'EMP_TO_MGR')
168 THEN
169 from_person_id := sc_person_id;
170 to_person_id := mgr_person_id;
171 END IF;
172
173 OPEN get_role (from_person_id);
174
175 FETCH get_role
176 INTO from_role;
177
178 CLOSE get_role;
179
180 OPEN get_global_name (from_person_id);
181
182 FETCH get_global_name
183 INTO from_name;
184
185 CLOSE get_global_name;
186
187 IF (from_role IS NULL)
188 THEN
189 RAISE from_role_not_exists;
190 END IF;
191
192 OPEN get_role (to_person_id);
193
194 FETCH get_role
195 INTO to_role;
196
197 CLOSE get_role;
198
199 OPEN get_global_name (to_person_id);
200
201 FETCH get_global_name
202 INTO to_name;
203
204 CLOSE get_global_name;
205
206 IF (to_role IS NULL)
207 THEN
208 RAISE to_role_not_exists;
209 END IF;
210
211
212
213 /*
214 * removing out the send notification of the generic message.
215 * Bug 8730795 - schowdhu - 07-Aug-2009
216 */
217 IF (to_or_from_mgr_ntf = 'MGR_TO_EMP')
218 THEN
219 IF (action_type = 'ApproveFinish' OR action_type = 'Finish')
220 THEN
221
222 ln_notification_id :=
223 wf_notification.send (ROLE => to_role,
224 msg_type => 'HRWPM',
225 msg_name => 'HR_WPM_MGR_TO_EMP_APPROVE',
226 callback => NULL,
227 CONTEXT => NULL,
228 send_comment => NULL,
229 priority => 50
230 );
231 wf_notification.setattrtext (ln_notification_id, 'MGR_NAME', from_name);
232 wf_notification.setattrtext (ln_notification_id, 'SCORECARD_NAME',
233 l_sc_info.scorecard_name);
234
235 ELSIF action_type = 'Reject'
236 THEN
237
238 ln_notification_id :=
239 wf_notification.send (ROLE => to_role,
240 msg_type => 'HRWPM',
241 msg_name => 'HR_WPM_MGR_TO_EMP_REJECT',
242 callback => NULL,
243 CONTEXT => NULL,
244 send_comment => NULL,
245 priority => 50
246 );
247 wf_notification.setattrtext (ln_notification_id, 'MGR_NAME', from_name);
248 wf_notification.setattrtext (ln_notification_id, 'SCORECARD_NAME',
252 ELSIF action_type = 'Transfer'
249 l_sc_info.scorecard_name);
250
251 -- 5570064 Changes Transfer from Java Layer Start
253 THEN
254
255 -- NULL;
256 -- code to send the notification to be plugged here
257 -- need to seed a new message in hrwpm.wft for single transfer .
258 -- differing the notification changes till then
259 -- the mass ntf message cannot be used here as it uses different attributes
260
261 --Block uncommented for Bug#13881019
262
263 ln_notification_id :=
264 wf_notification.send (ROLE => to_role,
265 msg_type => 'HRWPM',
266 msg_name => 'HR_WPM_MGR_TO_EMP_TRNSF',
267 callback => NULL,
268 CONTEXT => NULL,
269 send_comment => NULL,
270 priority => 50
271 );
272 wf_notification.setattrtext (ln_notification_id, 'MGR_NAME', from_name);
273 wf_notification.setattrtext (ln_notification_id, 'SCORECARD_NAME',
274 l_sc_info.scorecard_name);
275
276
277 -- 5570064 Changes Transfer from Java Layer End
278
279 ELSIF action_type = 'RequestAction'
280 THEN
281
282 ln_notification_id :=
283 wf_notification.send (ROLE => to_role,
284 msg_type => 'HRWPM',
285 msg_name => 'HR_WPM_NTF_REQUEST_ACTION',
286 callback => NULL,
287 CONTEXT => NULL,
288 send_comment => NULL,
289 priority => 50
290 );
291 wf_notification.setattrtext (ln_notification_id, 'MGR_NAME', from_name);
292 wf_notification.setattrtext (ln_notification_id, 'SCORECARD_NAME',
293 l_sc_info.scorecard_name);
294 END IF;
295
296 tlnt_mgmt_rel_apps_fn := 'HR_WPM_MGR_REL_APPS_SS';
297 ELSIF (to_or_from_mgr_ntf = 'EMP_TO_MGR')
298 THEN
299 ln_notification_id :=
300 wf_notification.send (ROLE => to_role,
301 msg_type => 'HRWPM',
302 msg_name => 'HR_WPM_EMP_TO_MGR_APR',
303 callback => NULL,
304 CONTEXT => NULL,
305 send_comment => NULL,
306 priority => 50
307 );
308 wf_notification.setattrtext (ln_notification_id, 'EMP_NAME', from_name);
309 wf_notification.setattrtext (ln_notification_id, 'SCORECARD_NAME',
310 l_sc_info.scorecard_name);
311 tlnt_mgmt_rel_apps_fn := 'HR_WPM_EMP_REL_APPS_SS';
312 END IF;
313
314 /* IF (action_type = 'Transfer' AND to_or_from_mgr_ntf = 'MGR_TO_EMP' )
315 THEN
316 -- NULL;
317 -- need to seed a new message in hrwpm.wft for single transfer .
318 -- differing the notification changes till then
319 -- the mass ntf message cannot be used here as it uses different attributes
320 ELSE
321 Condition check on notification attributes removed to send notification while
322 transfering scorecard using Set Objectives Action -- Bug#13881019
323 */
324
325 wf_notification.setattrtext(ln_notification_id,'HR_WPM_SC_REASON', reason);
326
327 wf_notification.setattrtext (ln_notification_id, '#FROM_ROLE', from_role);
328 --wf_notification.setattrtext (ln_notification_id, '#WFM_FROM', from_name);
329 wf_notification.setattrtext (ln_notification_id, 'HR_WPM_SC_ID', score_card_id);
330 wf_notification.setattrtext (ln_notification_id, 'WHICH_NTF', to_or_from_mgr_ntf);
331 wf_notification.setattrtext (ln_notification_id, 'HR_FROM_WF', 'WF');
332 wf_notification.setattrtext (ln_notification_id, 'HR_PLAN_ID', l_sc_info.plan_id);
333 wf_notification.setattrtext (ln_notification_id, 'HR_ACTION_TYPE', to_or_from_mgr_ntf);
334
335 -- END IF;
336 END IF;
337 -- Bug 7580480 Fix start
338 BEGIN
339 IF (to_or_from_mgr_ntf = 'EMP_TO_MGR')
340 THEN
341 UPDATE per_personal_scorecards
342 SET status_code = 'APPROVAL'
343 WHERE scorecard_id = score_card_id;
344
345 COMMIT;
346 END IF;
347 EXCEPTION
348 WHEN OTHERS
349 THEN
350 RAISE;
351 END;
352
353 -- Bug Fix 7580480 End
354 RETURN success;
355 EXCEPTION
356 WHEN from_role_not_exists
357 THEN
358 RETURN 2; -- No role exists for employee
359 WHEN to_role_not_exists
360 THEN
361 RETURN 3; -- No role exists for manager
362 WHEN no_score_card_with_this_id
363 THEN
364 RETURN 4; -- No scorecard exists for employee
365 WHEN OTHERS
366 THEN
367 RAISE;
368 END;
369 END hr_wpm_ntf_util; -- Package spec