[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_ARC_HOLIDAY_ENTITLEMENT
Source
1 PACKAGE BODY PAY_NO_ARC_HOLIDAY_ENTITLEMENT as
2 /* $Header: pynohlea.pkb 120.0.12000000.1 2007/05/22 07:29:44 rajesrin noship $ */
3 TYPE lock_rec IS RECORD (
4 archive_assact_id NUMBER);
5 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
6 g_debug boolean := hr_utility.debug_enabled;
7 g_lock_table lock_table;
8 g_package VARCHAR2(33) := ' PAY_NO_ARC_HOLIDAY_ENTITLEMENT.';
9 g_business_group_id NUMBER;
10 g_legal_employer_id NUMBER;
11 g_effective_date DATE;
12 g_employee_id NUMBER;
13 g_assignment_set_id NUMBER;
14 g_archive VARCHAR2(50);
15 g_err_num NUMBER;
16 g_errm VARCHAR2(150);
17 /* GET PARAMETER */
18 FUNCTION GET_PARAMETER(
19 p_parameter_string IN VARCHAR2
20 ,p_token IN VARCHAR2
21 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
22 IS
23 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
24 l_start_pos NUMBER;
25 l_delimiter VARCHAR2(1):=' ';
26 l_proc VARCHAR2(240):= g_package||' get parameter ';
27 BEGIN
28
29 IF g_debug THEN
30 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
31 END IF;
32 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
33 --
34 IF l_start_pos = 0 THEN
35 l_delimiter := '|';
36 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
37 END IF;
38 IF l_start_pos <> 0 THEN
39 l_start_pos := l_start_pos + length(p_token||'=');
40 l_parameter := substr(p_parameter_string,
41 l_start_pos,
42 instr(p_parameter_string||' ',
43 l_delimiter,l_start_pos)
44 - l_start_pos);
45 IF p_segment_number IS NOT NULL THEN
46 l_parameter := ':'||l_parameter||':';
47 l_parameter := substr(l_parameter,
48 instr(l_parameter,':',1,p_segment_number)+1,
49 instr(l_parameter,':',1,p_segment_number+1) -1
50 - instr(l_parameter,':',1,p_segment_number));
51 END IF;
52 END IF;
53 --
54 IF g_debug THEN
55 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
56 END IF;
57 RETURN l_parameter;
58 END;
59 /* GET ALL PARAMETERS */
60 PROCEDURE GET_ALL_PARAMETERS(
61 p_payroll_action_id IN NUMBER
62 ,p_business_group_id OUT NOCOPY NUMBER
63 ,p_legal_employer_id OUT NOCOPY NUMBER
64 ,p_assignment_set_id OUT NOCOPY NUMBER
65 ,p_employee OUT NOCOPY NUMBER
66 ,p_archive OUT NOCOPY VARCHAR2
67 ,p_effective_date OUT NOCOPY DATE
68 ) IS
69 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
70 SELECT PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,
71 'LEGAL_EMPLOYER')
72 ,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'ARCHIVE')
73 ,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'EMPLOYEEID')
74 ,PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_PARAMETER(legislative_parameters,'ASSIGNMENTSETID')
75 ,effective_date
76 ,business_group_id
77 FROM pay_payroll_actions
78 WHERE payroll_action_id = p_payroll_action_id;
79 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
80 --
81 BEGIN
82 fnd_file.put_line (fnd_file.LOG, 'Entering Get all Parameters' );
83 OPEN csr_parameter_info (p_payroll_action_id);
84 FETCH csr_parameter_info
85 INTO p_legal_employer_id
86 ,p_archive
87 ,p_employee
88 ,p_assignment_set_id
89 ,p_effective_date
90 ,p_business_group_id;
91 CLOSE csr_parameter_info;
92 --
93 IF g_debug THEN
94 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
95 END IF;
96 END GET_ALL_PARAMETERS;
97
98 /* RANGE CODE */
99 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
100 ,p_sql OUT NOCOPY VARCHAR2)
101 IS
102
103 l_ovn NUMBER;
104 l_action_info_id NUMBER;
105
106 BEGIN
107
108
109
110 IF g_debug THEN
111 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
112 END IF;
113
114
115
116 PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_ALL_PARAMETERS( p_payroll_action_id
117 ,g_business_group_id
118 ,g_legal_employer_id
119 ,g_assignment_set_id
120 ,g_employee_id
121 ,g_archive
122 ,g_effective_date
123 );
124
125
126
127 /** Insert report parameters **/
128 pay_action_information_api.create_action_information (
129 p_action_information_id => l_action_info_id,
130 p_action_context_id => p_payroll_action_id,
131 p_action_context_type => 'PA',
132 p_object_version_number => l_ovn,
133 p_effective_date => g_effective_date,
134 p_source_id => NULL,
135 p_source_text => NULL,
136 p_action_information_category=> 'EMEA REPORT DETAILS',
137 p_action_information1 => 'PYNOHLEA',
138 p_action_information2 => g_legal_employer_id,
139 p_action_information3 => g_employee_id,
140 p_action_information4 => fnd_date.date_to_canonical(g_effective_date),
141 p_action_information5 => g_assignment_set_id);
142
143 hr_utility.set_location('Report Parameters ***********',30);
144
145 p_sql := 'SELECT DISTINCT person_id
146 FROM per_people_f ppf
147 ,pay_payroll_actions ppa
148 WHERE ppa.payroll_action_id = :payroll_action_id
149 AND ppa.business_group_id = ppf.business_group_id
150 ORDER BY ppf.person_id';
151
152 --IF g_debug THEN
153 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
154 -- END IF;
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 hr_utility.set_location('Exception****',70);
159 -- Return cursor that selects no rows
160 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
161 END RANGE_CODE;
162
163 /* ASSIGNMENT ACTION CODE PAY_NO_ARC_HOLIDAY_ENTITLEMENT.ASSIGNMENT_ACTION_CODE */
164
165 PROCEDURE ASSIGNMENT_ACTION_CODE
166 (p_payroll_action_id IN NUMBER
167 ,p_start_person IN NUMBER
168 ,p_end_person IN NUMBER
169 ,p_chunk IN NUMBER)
170 IS
171
172 l_actid number;
173 /****** Start Of Cursor ******/
174
175 CURSOR csr_action_creation(csr_v_business_group_id NUMBER,
176 csr_v_legal_employer_id NUMBER,
177 csr_v_effective_date DATE,
178 csr_v_person_id NUMBER,
179 csr_v_assignment_set_id NUMBER)
180 IS
181 SELECT paaf.assignment_id
182 FROM per_all_people_f papf,
183 per_all_assignments_f paaf,
184 per_assignment_status_types past,
185 hr_soft_coding_keyflex hsc,
186 hr_organization_information hoi
187 WHERE paaf.person_id = papf.person_id
188 AND papf.person_id = nvl(csr_v_person_id,papf.person_id)
189 AND paaf.assignment_status_type_id = past.assignment_status_type_id
190 AND papf.business_group_id = csr_v_business_group_id
191 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
192 AND hoi.org_information1 = hsc.segment2
193 AND hoi.organization_id = csr_v_legal_employer_id
194 AND hoi.org_information_context = 'NO_LOCAL_UNITS'
195 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
196 AND papf.person_id BETWEEN p_start_person and p_end_person
197 AND csr_v_effective_date BETWEEN papf.effective_start_date
198 AND papf.effective_end_date
199 AND csr_v_effective_date BETWEEN paaf.effective_start_date
200 AND paaf.effective_end_date
201 AND (csr_v_assignment_set_id is null
202 or exists(select hasav.assignment_id
203 from
204 HR_ASSIGNMENT_SET_AMENDMENTS_V hasav,
205 HR_ASSIGNMENT_SETS_V hasv
206 where
207 hasav.assignment_set_id = hasv.assignment_set_id
208 and hasv.assignment_set_id = csr_v_assignment_set_id
209 and hasav.assignment_id = paaf.assignment_id));
210
211
212
213 /**** End Of Cursor ****/
214
215 BEGIN
216
217
218 hr_utility.trace('Entering Assignment Action Code '||p_payroll_action_id);
219
220 PAY_NO_ARC_HOLIDAY_ENTITLEMENT.GET_ALL_PARAMETERS( p_payroll_action_id
221 ,g_business_group_id
222 ,g_legal_employer_id
223 ,g_assignment_set_id
224 ,g_employee_id
225 ,g_archive
226 ,g_effective_date
227 );
228
229
230 FOR csr_act IN csr_action_creation(
231 g_business_group_id,
232 g_legal_employer_id,
233 g_effective_date,
234 g_employee_id,
235 g_assignment_set_id)
236 LOOP
237
238 SELECT pay_assignment_actions_s.NEXTVAL
239 INTO l_actid
240 FROM DUAL;
241
242 hr_nonrun_asact.insact (l_actid,
243 csr_act.assignment_id,
244 p_payroll_action_id,
245 p_chunk,
246 g_business_group_id
247 );
248 END LOOP;
249
250 IF g_debug THEN
251 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
252 END IF;
253 IF g_debug THEN
254 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
255 END IF;
256 -- fnd_file.put_line (fnd_file.LOG, 'Exiting Assignment Action Code' );
257
258
259 END ASSIGNMENT_ACTION_CODE;
260 /* INITIALIZATION CODE */
261 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
262 IS
263 BEGIN
264 -- fnd_file.put_line (fnd_file.LOG, 'Entering Initialization Code' );
265 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
266
267
268
269 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
270
271 -- fnd_file.put_line (fnd_file.LOG, 'Existing Initialization Code' );
272 EXCEPTION WHEN OTHERS THEN
273 g_err_num := SQLCODE;
274 IF g_debug THEN
275 hr_utility.set_location(' Err Procedure INITIALIZATION_CODE',110);
276 hr_utility.set_location('ORA_ERR: ' || g_err_num ||
277 'In INITIALIZATION_CODE',180);
278 END IF;
279 fnd_file.put_line (fnd_file.LOG, 'Error in Initialization Code' );
280 END INITIALIZATION_CODE;
281 /* ARCHIVE CODE */
282 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
283 ,p_effective_date IN DATE)
284 IS
285
286 l_arch_ovn number;
287 l_arch_action_info_id number;
288 l_holiday_ent NUMBER;
289 l_holiday_ent_60 NUMBER;
290 l_holiday_carried_fwd NUMBER;
291 l_holiday_carried_od NUMBER;
292 l_holiday_days_taken NUMBER;
293 l_holiday_days_taken_60 NUMBER :=0;
294 l_holiday_day_remaining NUMBER;
295 l_holiday_day_remaining60 NUMBER;
296
297
298 l_holiday_ent_days NUMBER;
299 l_holiday_ent_60_days NUMBER;
300 l_holiday_carried_fwd_days NUMBER;
301 l_holiday_carried_od_days NUMBER;
302 l_holiday_taken_days NUMBER;
303 l_holiday_taken_60_days NUMBER :=0;
304 l_holiday_remaining_days NUMBER;
305 l_holiday_remaining60_days NUMBER;
306
307
308
309 /*** Start of Cursor ***/
310 Cursor csr_holiday_entitlement(csr_v_business_group_id NUMBER,
311 csr_v_legal_employer_id NUMBER,
312 csr_v_effective_date DATE,
313 csr_v_person_id NUMBER,
314 csr_v_assignment_set_id NUMBER
315 ) is
316
317 SELECT papf.person_id,
318 papf.full_name,
319 paaf.assignment_id,
320 paaf.assignment_number,
321 past.per_system_status
322 FROM per_all_people_f papf,
323 per_all_assignments_f paaf,
324 per_assignment_status_types past,
325 pay_assignment_actions paas,
326 hr_soft_coding_keyflex hsc,
327 hr_organization_information hoi
328 WHERE paas.assignment_action_id = p_assignment_action_id
329 AND papf.person_id = nvl(csr_v_person_id,papf.person_id)
330 AND paaf.person_id = papf.person_id
331 AND paaf.assignment_status_type_id = past.assignment_status_type_id
332 AND paaf.assignment_id = paas.assignment_id
333 AND papf.business_group_id = csr_v_business_group_id
334 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
335 AND hoi.org_information1 = hsc.segment2
336 AND hoi.organization_id = csr_v_legal_employer_id
337 AND hoi.org_information_context = 'NO_LOCAL_UNITS'
338 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
339 AND csr_v_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
340 AND csr_v_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
341 AND (csr_v_assignment_set_id is null
342 or exists(select hasav.assignment_id
343 from
344 HR_ASSIGNMENT_SET_AMENDMENTS_V hasav,
345 HR_ASSIGNMENT_SETS_V hasv
346 where
347 hasav.assignment_set_id = hasv.assignment_set_id
348 and hasv.assignment_set_id = csr_v_assignment_set_id
349 and hasav.assignment_id = paaf.assignment_id));
350
351 Cursor csr_defined_balance(csr_balance_name VARCHAR2) is
352 SELECT pdb.defined_balance_id
353 FROM pay_defined_balances pdb,
354 pay_balance_dimensions pbd,
355 pay_balance_types pbt
356 WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
357 AND pdb.balance_type_id = pbt.balance_type_id
358 AND pbt.legislation_code = 'NO'
359 AND pbd.database_item_suffix = '_ASG_YTD'
360 AND pbt.balance_name = csr_balance_name;
361
362 /**** End of Cursor ***/
363
364 BEGIN
365 fnd_file.put_line (fnd_file.LOG, 'entering archive code' );
366
367
368 open csr_defined_balance('Holiday Days Entitlement');
369 fetch csr_defined_balance into l_holiday_ent;
370 close csr_defined_balance;
371
372 open csr_defined_balance('Holiday Days Entitlement Over 60');
373 fetch csr_defined_balance into l_holiday_ent_60;
374 close csr_defined_balance;
375
376 open csr_defined_balance('Holiday Days Carried Forward');
377 fetch csr_defined_balance into l_holiday_carried_fwd;
378 close csr_defined_balance;
379
380 open csr_defined_balance('Holiday Carry Over Days');
381 fetch csr_defined_balance into l_holiday_carried_od;
382 close csr_defined_balance;
383
384 open csr_defined_balance('Holiday Days Taken');
385 fetch csr_defined_balance into l_holiday_days_taken;
386 close csr_defined_balance;
387
388 open csr_defined_balance('Holiday Days Taken Over 60');
389 fetch csr_defined_balance into l_holiday_days_taken_60;
390 close csr_defined_balance;
391
392 open csr_defined_balance('Holiday Days Remaining');
393 fetch csr_defined_balance into l_holiday_day_remaining;
394 close csr_defined_balance;
395
396 open csr_defined_balance('Holiday Days Over 60 Remaining');
397 fetch csr_defined_balance into l_holiday_day_remaining60;
398 close csr_defined_balance;
399
400
401 FOR csr_he IN csr_holiday_entitlement(g_business_group_id,
402 g_legal_employer_id,
403 p_effective_date,
404 g_employee_id,
405 g_assignment_set_id)
406 LOOP
407
408
409 Begin
410 l_holiday_ent_days := pay_balance_pkg.get_value(l_holiday_ent,csr_he.assignment_id,g_effective_date);
411 Exception
412 When others then
413 l_holiday_ent_days := 0;
414 END;
415
416 Begin
417 l_holiday_ent_60_days := pay_balance_pkg.get_value(l_holiday_ent_60,csr_he.assignment_id,g_effective_date);
418 Exception
419 When others then
420 l_holiday_ent_60_days := 0;
421 END;
422
423 Begin
424 l_holiday_carried_fwd_days := pay_balance_pkg.get_value(l_holiday_carried_fwd,csr_he.assignment_id,g_effective_date);
425 Exception
426 When others then
427 l_holiday_carried_fwd_days := 0;
428 END;
429
430 Begin
431 l_holiday_carried_od_days := pay_balance_pkg.get_value(l_holiday_carried_od,csr_he.assignment_id,g_effective_date);
432 Exception
433 When others then
434 l_holiday_carried_od_days := 0;
435 END;
436
437 Begin
438 l_holiday_taken_days := pay_balance_pkg.get_value(l_holiday_days_taken,csr_he.assignment_id,g_effective_date);
439 Exception
440 When others then
441 l_holiday_taken_days := 0;
442 END;
443
444 Begin
445 l_holiday_taken_60_days := pay_balance_pkg.get_value(l_holiday_days_taken_60,csr_he.assignment_id,g_effective_date);
446 Exception
447 When others then
448 l_holiday_taken_60_days := 0;
449 END;
450
451 Begin
452 l_holiday_remaining_days := pay_balance_pkg.get_value(l_holiday_day_remaining,csr_he.assignment_id,g_effective_date);
453 Exception
454 When others then
455 l_holiday_remaining_days := 0;
456 END;
457 /* Added for Bug 5391237*/
458 l_holiday_remaining_days := nvl(l_holiday_remaining_days,0) + nvl(l_holiday_carried_od_days,0);
459
460 Begin
461 l_holiday_remaining60_days := pay_balance_pkg.get_value(l_holiday_day_remaining60,csr_he.assignment_id,g_effective_date);
462 Exception
463 When others then
464 l_holiday_remaining60_days := 0;
465 END;
466
467 pay_action_information_api.create_action_information (
468 p_action_context_id=> p_assignment_action_id,
469 p_action_context_type=> 'AAP',
470 p_action_information_category=> 'EMEA REPORT INFORMATION',
471 p_assignment_id => csr_he.assignment_id,
472 p_action_information1 => 'PYNOHLEA',
473 p_action_information2 => csr_he.assignment_number,
474 p_action_information3 => csr_he.full_name,
475 p_action_information4 => l_holiday_ent_days,
476 p_action_information5 => l_holiday_ent_60_days,
477 p_action_information6 => l_holiday_carried_fwd_days,
478 p_action_information7 => l_holiday_taken_days,
479 p_action_information8 => l_holiday_taken_60_days,
480 p_action_information9 => l_holiday_remaining_days,
481 p_action_information10 => l_holiday_remaining60_days,
482 p_action_information_id=> l_arch_action_info_id,
483 p_object_version_number=> l_arch_ovn);
484 END LOOP;
485
486 IF g_debug THEN
487 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',80);
488 END IF;
489 IF g_debug THEN
490 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',90);
491 END IF;
492 fnd_file.put_line (fnd_file.LOG, 'Exiting archive code' );
493 END ARCHIVE_CODE;
494 END PAY_NO_ARC_HOLIDAY_ENTITLEMENT;