[Home] [Help]
PACKAGE BODY: APPS.GHR_MASS_ACTIONS_PKG
Source
1 PACKAGE BODY GHR_MASS_ACTIONS_PKG AS
2 /* $Header: ghmasact.pkb 120.1.12000000.1 2007/03/27 10:07:50 managarw noship $ */
3
4 g_package varchar2(32) := ' GHR_MASS_ACTIONS_PKG.' ;
5
6
7 procedure get_noa_id_desc
8 (
9 p_noa_code in ghr_nature_of_actions.code%type,
10 p_effective_date in date default trunc(sysdate),
11 p_noa_id out nocopy ghr_nature_of_actions.nature_of_action_id%type,
12 p_noa_desc out nocopy ghr_nature_of_actions.description%type
13 )
14 is
15
16 --
17 -- local variables
18 --
19
20 l_proc varchar2(72) := g_package || 'get_noa_id_desc';
21
22 cursor c_noa is
23 select noa.nature_of_action_id, noa.description
24 from ghr_nature_of_actions noa
25 where noa.code = p_noa_code
26 and noa.enabled_flag = 'Y'
27 and nvl(p_effective_date,trunc(sysdate))
28 between noa.date_from
29 and nvl(noa.date_to,nvl(p_effective_date,trunc(sysdate))) ;
30 --
31 begin
32 --
33 hr_utility.set_location('Entering ' || l_proc,5);
34 p_noa_id := Null;
35 p_noa_desc := Null;
36
37 for noa_id_desc in c_noa loop
38 hr_utility.set_location( l_proc,10);
39 p_noa_id := noa_id_desc.nature_of_action_id;
40 p_noa_desc := noa_id_desc.description;
41 end loop;
42 hr_utility.set_location('Leaving ' || l_proc,15);
43 -- Nocopy Changes
44 Exception
45 When others then
46 p_noa_id := null;
47 p_noa_desc := Null;
48 RAISE;
49 end get_noa_id_desc ;
50
51
52 procedure get_remark_id_desc
53 (
54 p_remark_code in ghr_nature_of_actions.code%type,
55 p_effective_date in date default trunc(sysdate),
56 p_remark_id out nocopy ghr_nature_of_actions.nature_of_action_id%type,
57 p_remark_desc out nocopy ghr_nature_of_actions.description%type
58 )
59 is
60
61 --
62 -- local variables
63
64 l_proc varchar2(72) := g_package || 'get_remark_id_desc';
65
66 Cursor c_rem_desc is
67 select rem.remark_id,
68 rem.description
69 from ghr_remarks rem
70 where rem.code = p_remark_code
71 and rem.enabled_flag = 'Y'
72 and nvl(p_effective_date,sysdate)
73 between rem.date_from and nvl(rem.date_to,nvl(p_effective_date, trunc(sysdate)));
74
75 begin
76 --
77 hr_utility.set_location('Entering ' || l_proc,5);
78 p_remark_id := Null;
79 p_remark_desc := Null;
80
81 for rem_id_desc in c_rem_desc loop
82 hr_utility.set_location( l_proc,10);
83 p_remark_id := rem_id_desc.remark_id;
84 p_remark_desc := rem_id_desc.description;
85 end loop;
86 hr_utility.set_location('Leaving ' || l_proc,15);
87 -- Nocopy Changes
88 Exception
89 When others then
90 p_remark_id := Null;
91 p_remark_desc := Null;
92 RAISE;
93 end get_remark_id_desc ;
94
95
96 procedure emp_rec_to_sf52_rec
97 (p_emp_rec in ghr_mass_actions_pkg.emp_rec_type,
98 p_sf52_rec in out nocopy ghr_pa_requests%rowtype
99 )
100 is
101 l_proc varchar2(72) := g_package || 'emp_rec_to_sf52_rec';
102 l_sf52_rec ghr_pa_requests%rowtype;
103
104 begin
105 -- Nocopy Changes
106 l_sf52_rec := p_sf52_rec;
107 --
108 p_sf52_rec.person_id := p_emp_rec.person_id;
109 p_sf52_rec.employee_first_name := p_emp_rec.first_name;
110 p_sf52_rec.employee_last_name := p_emp_rec.last_name;
111 p_sf52_rec.employee_middle_names := p_emp_rec.middle_names;
112 p_sf52_rec.employee_national_identifier := p_emp_rec.national_identifier;
113 p_Sf52_rec.employee_date_of_birth := p_emp_rec.date_of_birth;
114 p_sf52_rec.employee_assignment_id := p_emp_rec.assignment_id;
115
116 hr_utility.set_location('Leaving ' || l_proc,10);
117 -- Nocopy Changes
118 Exception
119 When others then
120 p_sf52_rec := l_sf52_rec;
121 RAISE;
122 end emp_rec_to_sf52_rec;
123
124
125 procedure asg_sf52_rec_to_sf52_rec
126 (p_asg_sf52_rec in ghr_api.asg_sf52_type,
127 p_sf52_rec in out nocopy ghr_pa_requests%rowtype
128 )
129 is
130
131 l_proc varchar2(72) := g_package || 'asg_sf52_rec_to_sf52_rec';
132 l_sf52_rec ghr_pa_requests%rowtype;
133 begin
134 -- Nocopy Changes
135 l_sf52_rec := p_sf52_rec;
136 --
137 hr_utility.set_location('Entering ' || l_proc,5);
138
139 p_sf52_rec.tenure := p_asg_sf52_rec.tenure;
140 p_sf52_rec.to_step_or_rate := p_asg_sf52_rec.step_or_rate;
141 p_sf52_rec.annuitant_indicator := p_asg_sf52_rec.annuitant_indicator;
142 p_sf52_rec.pay_rate_determinant := p_asg_sf52_rec.pay_rate_determinant;
143 p_sf52_rec.work_schedule := p_asg_sf52_rec.work_schedule;
144 p_sf52_rec.part_time_hours := p_asg_sf52_rec.part_time_hours;
145
146
147
148 hr_utility.set_location('Leaving ' || l_proc,10);
149 -- Nocopy Changes
150 Exception
151 When others then
152 p_sf52_rec := l_sf52_rec;
153 RAISE;
154 end asg_sf52_rec_to_sf52_rec;
155
156
157 procedure pos_grp1_rec_to_sf52_rec
158 (p_pos_grp1_rec in ghr_api.pos_grp1_type,
159 p_sf52_rec in out nocopy ghr_pa_requests%rowtype
160 )
161 is
162
163 l_proc varchar2(72) := g_package || 'pos_grp1_rec_to_sf52_rec';
164 l_sf52_rec ghr_pa_requests%rowtype;
165
166 begin
167 -- Nocopy Changes
168 l_sf52_rec := p_sf52_rec;
169 --
170 hr_utility.set_location('Entering ' || l_proc,5);
171
172 p_sf52_rec.flsa_category := p_pos_grp1_rec.flsa_category;
173 p_sf52_rec.bargaining_unit_status := p_pos_grp1_rec.bargaining_unit_status;
174 p_sf52_rec.functional_class := p_pos_grp1_rec.functional_class;
175 p_sf52_rec.supervisory_status := p_pos_grp1_rec.supervisory_status;
176
177 hr_utility.set_location('Leaving ' || l_proc,10);
178 -- Nocopy Changes
179 Exception
180 When others then
181 p_sf52_rec := l_sf52_rec;
182 RAISE;
183 end pos_grp1_rec_to_sf52_rec;
184
185 procedure pay_calc_rec_to_sf52_rec
186 (p_pay_calc_rec in ghr_pay_calc.pay_calc_out_rec_type,
187 p_sf52_rec in out nocopy ghr_pa_requests%rowtype
188 )
189 is
190
191 l_proc varchar2(72) := g_package || 'pos_grp1_rec_to_sf52_rec';
192 l_sf52_rec ghr_pa_requests%rowtype;
193
194 begin
195 -- Nocopy Changes
196 l_sf52_rec := p_sf52_rec;
197 --
198 hr_utility.set_location('Entering ' || l_proc,5);
199 p_sf52_rec.to_basic_pay := p_pay_calc_rec.basic_pay;
200 p_sf52_rec.to_locality_adj := p_pay_calc_rec.locality_adj;
201 p_sf52_rec.to_adj_basic_pay := p_pay_calc_rec.adj_basic_pay;
202 p_sf52_rec.to_total_salary := p_pay_calc_rec.total_salary;
203 p_sf52_rec.to_other_pay_amount := p_pay_calc_rec.other_pay_amount;
204 p_sf52_rec.to_au_overtime := p_pay_calc_rec.au_overtime;
205 p_sf52_rec.to_availability_pay := p_pay_calc_rec.availability_pay;
206
207 -- l_sf52_rec.to_step_or_rate /-- Can we store the new step / rate in this case since it is automated, or should we leave it to update_hr process
208 -- l_sf52_rec.pay_rate_determinant := --/
209
210 -- Should error out if pt_eff_start_date is not the same as the eff_date of the new pay table
211 -- This should have been taken care even before this stage, I guess
212
213 -- If open_pay_fields then custom_pay_calc_flag should be set to 'Y' . Should we also consider the assignment of the
214 -- above pay elements based on the the boolean out parameter. ????
215
216 hr_utility.set_location('Leaving ' || l_proc,10);
217
218 -- Nocopy Changes
219 Exception
220 When others then
221 p_sf52_rec := l_sf52_rec;
222 RAISE;
223 end pay_calc_rec_to_sf52_rec;
224
225 procedure duty_station_rec_to_sf52_rec
226 (p_duty_station_rec in ghr_mass_actions_pkg.duty_station_rec_type,
227 p_sf52_rec in out nocopy ghr_pa_requests%rowtype
228 )
229 is
230 l_proc varchar2(72) := g_package || 'duty_station_rec_to_sf52_rec';
231 l_sf52_rec ghr_pa_requests%rowtype;
232
233 begin
234 -- Nocopy Changes
235 l_sf52_rec := p_sf52_rec;
236 --
237 p_sf52_rec.duty_station_id := p_duty_station_rec.duty_station_id;
238 p_sf52_rec.duty_station_code := p_duty_station_rec.duty_station_code;
239 p_sf52_rec.duty_station_desc := p_duty_station_rec.duty_station_desc;
240
241 hr_utility.set_location('Leaving ' || l_proc,10);
242 -- Nocopy Changes
243 Exception
244 When others then
245 p_sf52_rec := l_sf52_rec;
246 RAISE;
247 end duty_station_rec_to_sf52_rec;
248
249
250 procedure replace_insertion_values
251 (p_desc in varchar2,
252 p_information1 in varchar2 default null,
253 p_information2 in varchar2 default null,
254 p_information3 in varchar2 default null,
255 p_information4 in varchar2 default null,
256 p_information5 in varchar2 default null,
257 p_desc_out out nocopy varchar2
258 )
259 is
260
261 -- assuming that this procedure would ever be called if there is an insertion value for the desc.
262
263 l_ins_count number := 0;
264 l_desc varchar2(2000);
265 l_length number;
266 l_i number;
267 l_count_of_dashes number := 0;
268 l_val_to_be_repl varchar2(30);
269 begin
270
271 l_length := length(p_desc);
272 l_i := 1;
273 l_ins_count := 0;
274
275 while l_i <= l_length loop
276 if nvl(substr(p_desc,l_i,1),hr_api.g_varchar2) <> '_' then
277 l_desc := l_desc || nvl(substr(p_desc,l_i,1),' ');
278 l_i := l_i + 1;
279 else
280 l_ins_count := l_ins_count + 1;
281 l_count_of_dashes := 0;
282 l_val_to_be_repl := null;
283 --l_i := l_i + 1;
284 --l_count_of_dashes := l_count_of_dashes + 1;
285
286 while nvl(substr(p_desc,l_i,1),hr_api.g_varchar2) = '_' loop
287 -- dbms_output.put_line('l_i is ' || to_char(l_i));
288 l_desc := l_desc || nvl(substr(p_desc,l_i,1),' ');
289 l_count_of_dashes := l_count_of_dashes + 1;
290 --if nvl(substr(p_desc,l_i,1),hr_api.g_varchar2) <> '_' then
291 -- exit;
292 --end if;
293 l_i := l_i + 1;
294 end loop;
295
296 for i in 1..l_count_of_dashes loop
297 -- dbms_output.put_line(to_char(l_ins_count) || l_val_to_be_repl);
298 l_val_to_be_repl := l_val_to_be_repl || '_';
299 end loop;
300
301 -- dbms_output.put_line(to_char(l_ins_count) || ' ' || to_char(l_count_of_dashes) || l_val_to_be_repl);
302 if l_ins_count = 1 then
303 l_desc := replace(l_desc,l_val_to_be_repl,p_information1);
304 elsif l_ins_count = 2 then
305 l_desc := replace(l_desc,l_val_to_be_repl,p_information2);
306 elsif l_ins_count = 3 then
307 l_desc := replace(l_desc,l_val_to_be_repl,p_information3);
308 end if;
309 end if;
310 end loop;
311 p_desc_out := l_Desc;
312 -- NOCOPY CHANGES
313 EXCEPTION
314 when others then
315 p_desc_out := NULL;
316 RAISE;
317 end replace_insertion_values;
318
319
320 Procedure get_personnel_off_groupbox
321 (p_position_id in ghr_pa_requests.from_position_id%type,
322 p_effective_date in date default trunc(sysdate),
323 p_groupbox_id out nocopy ghr_groupboxes.groupbox_id%type,
324 p_routing_group_id out nocopy ghr_routing_groups.routing_group_id%type
325 )
326 is
327
328 l_proc varchar2(72) := g_package || 'get_personnel_off_groupbox';
329 l_pos_ei_data per_position_extra_info%rowtype;
330 l_groupbox_id ghr_groupboxes.groupbox_id%type;
331 l_routing_group_id ghr_routing_groups.routing_group_id%type;
332 l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
333 l_log_text varchar2(2000);
334 l_count number;
335
336 Cursor c_gbx is
337 select gbx.groupbox_id gpid, gbx.routing_group_id rgpid
338 from ghr_pois gpoi,
339 ghr_groupboxes gbx,
340 ghr_routing_groups rgp
341 where gbx.groupbox_id = gpoi.groupbox_id
342 and gpoi.personnel_office_id = l_personnel_office_id
343 and gbx.routing_group_id = rgp.routing_group_id;
344
345 Cursor c_gpbox_users is
346 select count(*) cnt
347 from ghr_groupbox_users gbu
348 where gbu.groupbox_id = l_groupbox_id;
349
350
351 begin
352 savepoint get_personnel_off_groupbox;
353 hr_utility.set_location('Entering ' || l_proc,5);
354
355 -- Find the groupbox of the personnelist, update ghr_pa_routing_history and then call work_flow
356
357
358 l_log_text := 'Error while getting the groupbox of the personnel';
359
360 -- get the personnel offfice id
361 ghr_history_fetch.fetch_positionei
362 (p_position_id => p_position_id ,
363 p_information_type => 'GHR_US_POS_GRP1' ,
364 p_date_effective => trunc(nvl(p_effective_date,sysdate)),
365 p_pos_ei_data => l_pos_ei_data
366 );
367
368 l_personnel_office_id := l_pos_ei_data.poei_information3;
369 l_pos_ei_data := null;
370
371 for rout_det in c_gbx loop
372 l_groupbox_id := rout_det.gpid;
373 l_routing_group_id := rout_det.rgpid;
374 end loop;
375
376 -- fetch groupbox_id as well as other routing group details
377
378 if l_groupbox_id is null then
379 -- Remember to create a new message and update the message number
380 hr_utility.set_message(8301,'GHR_38479_INV_GBOX_FOR_PER_OFF');
381 hr_utility.raise_error;
382 else
383 p_groupbox_id := l_groupbox_id;
384 p_routing_group_id := l_routing_group_id;
385 -- Make sure that this is a valid groupbox with atleast a single user
386 l_count := 0;
387 for gbusers in C_gpbox_users loop
388 l_count := gbusers.cnt;
389 end loop;
390 If nvl(l_count,0) = 0 then
391 -- Remember to create a new message and update the message number
392 hr_utility.set_message(8301,'GHR_38480_NO_GBOX_USERS');
393 hr_utility.raise_error;
394 End if;
395 end if;
396 -- NOCOPY CHANGES
397 EXCEPTION
398 when others then
399 p_groupbox_id := NULL;
400 p_routing_group_id := NULL;
401 RAISE;
402 end get_personnel_off_groupbox;
403
404 Procedure get_personnel_officer_name
405 (p_personnel_office_id in ghr_pa_requests.personnel_office_id%TYPE,
406 p_person_full_name out nocopy varchar2,
407 p_approving_off_work_title out nocopy varchar2)
408 IS
409
410 CURSOR c_get_person_id(c_personnel_office_id varchar2)
411 IS
412 SELECT person_id
413 FROM ghr_pois
414 WHERE personnel_office_id = c_personnel_office_id;
415
416 l_person_id per_people_f.person_id%type;
417
418 BEGIN
419
420 for c_get_person_rec in c_get_person_id(p_personnel_office_id) LOOP
421
422 l_person_id := c_get_person_rec.person_id;
423
424 END LOOP;
425
426 p_approving_off_work_title :=
427 ghr_pa_requests_pkg.get_position_work_title
428 (p_person_id => l_person_id);
429
430 p_person_full_name := ghr_pa_requests_pkg.get_full_name_fml
431 (l_person_id);
432 EXCEPTION
433 WHEN OTHERS THEN
434 p_person_full_name := NULL;
435 p_approving_off_work_title := NULL;
436 RAISE;
437 END;
438
439 end ghr_mass_actions_pkg;