[Home] [Help]
PACKAGE BODY: APPS.HR_WPM_NTF_UTIL
Source
1 PACKAGE BODY HR_WPM_NTF_UTIL AS
2 /* $Header: hrwpmntf.pkb 120.3.12010000.2 2008/08/06 08:51:09 ubhat ship $*/
3
4
5 FUNCTION send_notification
6 ( action_type varchar2,
7 score_card_id per_personal_scorecards.scorecard_id%TYPE,
8 to_or_from_mgr_ntf varchar2,
9 reason varchar2)
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'
18 and wf.orig_system_id = person_id;
19
20 cursor get_global_name(p_person_id per_all_people_f.person_id%TYPE)
21 is
22 select global_name 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 * from per_personal_scorecards where scorecard_id = p_scorecard_id;
29
30 CURSOR get_sc_manager_person_id(p_sc_id NUMBER) IS
31 SELECT paf.supervisor_id
32 FROM per_all_assignments_f paf
33 ,per_personal_scorecards sc
34 WHERE sc.scorecard_id = p_sc_id
35 AND sc.assignment_id = paf.assignment_id
36 AND trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
37 AND paf.assignment_type in ('E','C');
38
39 cursor get_sc_person_id (p_scorecard_id per_personal_scorecards.scorecard_id%TYPE)
40 is
41 select person_id from per_personal_scorecards
42 where scorecard_id = p_scorecard_id;
43
44 cursor get_sc_plan_info(p_sc_id per_perf_mgmt_plans.plan_id%TYPE)
45 is
46 select HIERARCHY_TYPE_CODE from per_personal_scorecards sc, per_perf_mgmt_plans plan
47 where sc.scorecard_id = p_sc_id
48 and sc.plan_id = plan.plan_id;
49
50 cursor get_pos_manager_id(p_sc_id per_personal_scorecards.scorecard_id%TYPE)
51 is
52 select sup.person_id
53 from per_personal_scorecards sc, per_assignments_f wrk,
54 per_pos_structure_elements ppse, per_assignments_f sup, per_perf_mgmt_plans plans
55 where sc.scorecard_id = p_sc_id
56 and plans.plan_id = sc.plan_id
57 and sc.assignment_id = wrk.assignment_id
58 and trunc(sysdate) between wrk.effective_start_date and wrk.effective_end_date
59 and wrk.position_id = ppse.subordinate_position_id
60 and ppse.pos_structure_version_id = plans.pos_structure_version_id
61 and ppse.business_group_id = sup.business_group_id
62 and ppse.parent_position_id = sup.position_id;
63
64 ln_notification_id number;
65 from_role wf_local_roles.name%TYPE default null;
66 to_role wf_local_roles.name%TYPE default null;
67 from_name per_all_people_f.global_name%TYPE;
68 to_name per_all_people_f.global_name%TYPE;
69 FROM_ROLE_NOT_EXISTS exception;
70 TO_ROLE_NOT_EXISTS exception;
71 l_sc_info per_personal_scorecards%ROWTYPE;
72 NO_SCORE_CARD_WITH_THIS_ID exception;
73 lv_subject varchar2(200) default null;
74 tlnt_mgmt_rel_apps_fn varchar2(200) default null;
75 mgr_person_id per_all_people_f.person_id%TYPE default null;
76 sc_person_id per_personal_scorecards.person_id%TYPE default null;
77 from_person_id per_all_people_f.person_id%TYPE default null;
78 to_person_id per_all_people_f.person_id%TYPE default null;
79 mesg_name varchar2(100) default null;
80 l_hier_code per_perf_mgmt_plans.hierarchy_type_code%TYPE;
81
82 BEGIN
83 /* In case Employee to Manager we need to get the Manager's Person Id */
84
85 open get_sc_plan_info(score_card_id);
86 fetch get_sc_plan_info into l_hier_code;
87 close get_sc_plan_info;
88
89 if(l_hier_code = 'ORG') then
90 l_hier_code := 'SUP';
91 end if;
92
93 -- normally this procedure is invoked only when there is supervisor and employee
94 -- in case of top employee this is not called.
95 if(l_hier_code = 'SUP' or l_hier_code = 'SUP_ASG') then
96 open get_sc_manager_person_id(score_card_id);
97 fetch get_sc_manager_person_id into mgr_person_id;
98 close get_sc_manager_person_id;
99 elsif(l_hier_code = 'POS') then
100 open get_pos_manager_id(score_card_id);
101 fetch get_pos_manager_id into mgr_person_id;
102 close get_pos_manager_id;
103 end if;
104
105 --dbms_output.put_line(' mgr_person_id ' || mgr_person_id);
106
107 open get_sc_person_id(score_card_id);
108 fetch get_sc_person_id into sc_person_id;
109 close get_sc_person_id;
110
111 --dbms_output.put_line(' sc_person_id ' || sc_person_id);
112
113
114 if( to_or_from_mgr_ntf = 'MGR_TO_EMP' ) then
115 from_person_id := mgr_person_id;
116 to_person_id := sc_person_id;
117 elsif ( to_or_from_mgr_ntf = 'EMP_TO_MGR' ) then
118 from_person_id := sc_person_id;
119 to_person_id := mgr_person_id;
120 end if;
121
122 open get_role(from_person_id);
123 fetch get_role into from_role;
124 close get_role;
125
126 open get_global_name(from_person_id);
127 fetch get_global_name into from_name;
128 close get_global_name;
129
130 --dbms_output.put_line(' from_role ' || from_role);
131 --dbms_output.put_line(' from_name ' || from_name);
132
133 if( from_role is null ) then
134 raise FROM_ROLE_NOT_EXISTS;
135 end if;
136
137 open get_role(to_person_id);
138 fetch get_role into to_role;
139 close get_role;
140
141 open get_global_name(to_person_id);
142 fetch get_global_name into to_name;
143 close get_global_name;
144
145 --dbms_output.put_line(' to_role ' || to_role);
146 --dbms_output.put_line(' to_name ' || to_name);
147
148 if( to_role is null ) then
149 raise TO_ROLE_NOT_EXISTS;
150 end if;
151
152 open get_scorecard_info(score_card_id);
153 fetch get_scorecard_info into l_sc_info;
154 close get_scorecard_info;
155
156 if(l_sc_info.scorecard_name is null) then
157 raise NO_SCORE_CARD_WITH_THIS_ID;
158 end if;
159
160 --from_role := 'DCHHABRA';
161 --from_name := 'Chhabra, Deepjot';
162 --to_role := 'SATISH';
163 --to_name := 'Ramasamy, Satish';
164
165 ln_notification_id := wf_notification.send(role => to_role,
166 msg_type => 'HRWPM' ,
167 msg_name => 'HR_WPM_GENERIC_NTF_MSG',
168 callback => null,
169 context => null,
170 send_comment => null,
171 priority => 50) ;
172
173 --dbms_output.put_line(' NTF ID ' || ln_notification_id);
174
175 if( to_or_from_mgr_ntf = 'MGR_TO_EMP' ) then
176
177 if action_type = 'ApproveFinish' then
178 mesg_name := hr_util_misc_web.return_msg_text(
179 p_message_name =>'HR_WPM_NTF_APPROV_FINISH',
180 p_Application_id =>'PER');
181 elsif action_type = 'Reject' then
182 mesg_name := hr_util_misc_web.return_msg_text(
183 p_message_name =>'HR_WPM_NTF_REJECT',
184 p_Application_id =>'PER');
185 elsif action_type = 'RequestAction' then
186 mesg_name := hr_util_misc_web.return_msg_text(
187 p_message_name =>'HR_WPM_NTF_REQUEST_ACTION',
188 p_Application_id =>'PER');
189 end if;
190 fnd_message.set_name('PER','HR_WPM_GEN_MSG_MGR_TO_EMP');
191 fnd_message.set_token('MGR_NAME',from_name);
192 fnd_message.set_token('SCORECARD_NAME',l_sc_info.scorecard_name);
193 fnd_message.set_token('STATUS',mesg_name);
194
195 tlnt_mgmt_rel_apps_fn := 'HR_WPM_MGR_REL_APPS_SS';
196 elsif ( to_or_from_mgr_ntf = 'EMP_TO_MGR' ) then
197 fnd_message.set_name('PER','HR_WPM_GEN_MSF_EMP_TO_MGR');
198 fnd_message.set_token('EMP_NAME',from_name);
199 fnd_message.set_token('SCORECARD_NAME',l_sc_info.scorecard_name);
200 tlnt_mgmt_rel_apps_fn := 'HR_WPM_EMP_REL_APPS_SS';
201 end if;
202
203 lv_subject := fnd_message.get;
204
205 wf_notification.setattrtext(ln_notification_id,'#FROM_ROLE',from_role);
206 wf_notification.setattrtext(ln_notification_id,'HR_WPM_GENERIC_SUBJECT',lv_subject);
207 wf_notification.setattrtext(ln_notification_id,'#WFM_FROM', from_name);
208 wf_notification.setattrtext(ln_notification_id,'HR_WPM_SC_ID', score_card_id);
209 wf_notification.setattrtext(ln_notification_id,'HR_WPM_SC_REASON', reason);
210 wf_notification.setattrtext(ln_notification_id,'WHICH_NTF', to_or_from_mgr_ntf);
211 wf_notification.setattrtext(ln_notification_id,'HR_FROM_WF', 'WF');
212 wf_notification.setattrtext(ln_notification_id,'HR_PLAN_ID', l_sc_info.plan_id);
213 wf_notification.setattrtext(ln_notification_id,'HR_ACTION_TYPE', to_or_from_mgr_ntf);
214
215
216 return SUCCESS;
217
218 EXCEPTION WHEN OTHERS THEN
219 RAISE;
220 END;
221
222
223
224
225
226 END HR_WPM_NTF_UTIL; -- Package spec
227