[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_ARCHIVE_CWCA
Source
1 PACKAGE BODY PAY_SE_ARCHIVE_CWCA AS
2 /* $Header: pysecwca.pkb 120.0.12010000.4 2010/01/12 10:04:12 vijranga ship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 g_index NUMBER := -1;
6 g_index_assact NUMBER := -1;
7 g_index_bal NUMBER := -1;
8 g_package VARCHAR2 (240) := 'PAY_SE_ARCHIVE_CWCA.';
9 g_payroll_action_id NUMBER;
10 g_arc_payroll_action_id NUMBER;
11
12 -- Globals to pick up all th parameter
13 g_business_group_id NUMBER;
14 g_effective_date DATE;
15 g_person_id NUMBER;
16 g_assignment_id NUMBER;
17 g_still_employed VARCHAR2 (10);
18 g_report_start_year VARCHAR2 (10);
19 g_report_start_month VARCHAR2 (10);
20
21 --End of Globals to pick up all the parameter
22 g_format_mask VARCHAR2 (50);
23 g_err_num NUMBER;
24 g_errm VARCHAR2 (150);
25
26 /* GET PARAMETER */
27 FUNCTION get_parameter (
28 p_parameter_string IN VARCHAR2
29 ,p_token IN VARCHAR2
30 ,p_segment_number IN NUMBER DEFAULT NULL
31 )
32 RETURN VARCHAR2
33 IS
34 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
35 l_start_pos NUMBER;
36 l_delimiter VARCHAR2 (1) := ' ';
37 l_proc VARCHAR2 (240) := g_package || ' get parameter ';
38 BEGIN
39 --
40 IF g_debug
41 THEN
42 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
43 END IF;
44
45 l_start_pos :=
46 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
47
48 --
49 IF l_start_pos = 0
50 THEN
51 l_delimiter := '|';
52 l_start_pos :=
53 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
54 END IF;
55
56 IF l_start_pos <> 0
57 THEN
58 l_start_pos := l_start_pos + LENGTH (p_token || '=');
59 l_parameter :=
60 SUBSTR (p_parameter_string
61 ,l_start_pos
62 , INSTR (p_parameter_string || ' '
63 ,l_delimiter
64 ,l_start_pos
65 )
66 - (l_start_pos)
67 );
68
69 IF p_segment_number IS NOT NULL
70 THEN
71 l_parameter := ':' || l_parameter || ':';
72 l_parameter :=
73 SUBSTR (l_parameter
74 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
75 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
76 - 1
77 - INSTR (l_parameter, ':', 1, p_segment_number)
78 );
79 END IF;
80 END IF;
81
82 --
83 IF g_debug
84 THEN
85 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
86 END IF;
87
88 RETURN l_parameter;
89 END;
90
91 /* GET ALL PARAMETERS */
92 PROCEDURE get_all_parameters (
93 p_payroll_action_id IN NUMBER -- In parameter
94 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
95 ,p_effective_date OUT NOCOPY DATE -- Core parameter
96 ,p_person_id OUT NOCOPY NUMBER -- User parameter
97 ,p_assignment_id OUT NOCOPY VARCHAR2 -- User parameter
98 ,p_still_employed OUT NOCOPY VARCHAR2 -- User parameter
99 ,p_report_start_year OUT NOCOPY VARCHAR2 -- User parameter
100 ,p_report_start_month OUT NOCOPY VARCHAR2 -- User parameter
101 )
102 IS
103 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
104 IS
105 SELECT (PAY_SE_ARCHIVE_CWCA.get_parameter
106 (legislative_parameters
107 ,'PERSON_ID'
108 )
109 ) person_id
110 , (PAY_SE_ARCHIVE_CWCA.get_parameter
111 (legislative_parameters
112 ,'ASSIGNMENT_ID'
113 )
114 ) assignment_id
115 , (PAY_SE_ARCHIVE_CWCA.get_parameter
116 (legislative_parameters
117 ,'STILL_EMPLOYED'
118 )
119 ) still_employed
120 , (PAY_SE_ARCHIVE_CWCA.get_parameter
121 (legislative_parameters
122 ,'REPORT_YEAR'
123 )
124 ) report_year
125 , (PAY_SE_ARCHIVE_CWCA.get_parameter
126 (legislative_parameters
127 ,'REPORT_MONTH'
128 )
129 ) report_month
130 ,effective_date effective_date
131 ,business_group_id bg_id
132 FROM pay_payroll_actions
133 WHERE payroll_action_id = p_payroll_action_id;
134
135 lr_parameter_info csr_parameter_info%ROWTYPE;
136 l_proc VARCHAR2 (240)
137 := g_package || ' GET_ALL_PARAMETERS ';
138 BEGIN
139 --logger ('Entering ', l_proc);
140 --logger ('p_payroll_action_id ', p_payroll_action_id);
141
142 OPEN csr_parameter_info (p_payroll_action_id);
143
144 --FETCH csr_parameter_info into lr_parameter_info;
145 FETCH csr_parameter_info
146 INTO lr_parameter_info;
147
148 CLOSE csr_parameter_info;
149
150 fnd_file.put_line (fnd_file.LOG
151 , 'lr_parameter_info.STILL_EMPLOYED '
152 || lr_parameter_info.still_employed
153 );
154 --logger ('Entering ', l_proc);
155 p_person_id := lr_parameter_info.person_id;
156 --logger ('lr_parameter_info.PERSON_ID ', lr_parameter_info.person_id);
157 p_assignment_id := lr_parameter_info.assignment_id;
158 --logger ('lr_parameter_info.ASSIGNMENT_ID ' ,lr_parameter_info.assignment_id );
159 p_still_employed := lr_parameter_info.still_employed;
160 --logger ('lr_parameter_info.still_employed ' ,lr_parameter_info.still_employed );
161 p_report_start_year := lr_parameter_info.report_year;
162 --logger ('lr_parameter_info.report_year ' ,lr_parameter_info.report_year );
163 p_report_start_month := lr_parameter_info.report_month;
164 --logger ('lr_parameter_info.report_month ' ,lr_parameter_info.report_month );
165 p_effective_date := lr_parameter_info.effective_date;
166 --logger ('lr_parameter_info.effective_date ' ,lr_parameter_info.effective_date );
167 p_business_group_id := lr_parameter_info.bg_id;
168 --logger ('lr_parameter_info.bg_id ', lr_parameter_info.bg_id);
169 --logger ('LEAVING ', l_proc);
170
171 IF g_debug
172 THEN
173 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
174 ,30);
175 END IF;
176 END get_all_parameters;
177
178 -- *****************************************************************************
179 /* RANGE CODE */
180 -- *****************************************************************************
181 PROCEDURE range_code (
182 p_payroll_action_id IN NUMBER
183 ,p_sql OUT NOCOPY VARCHAR2
184 )
185 IS
186 l_action_info_id NUMBER;
187 l_ovn NUMBER;
188 l_business_group_id NUMBER;
189 l_start_date VARCHAR2 (30);
190 l_end_date VARCHAR2 (30);
191 l_assignment_id NUMBER;
192 -- *****************************************************************************
193 -- Variable Required
194 l_person_number VARCHAR2 (100);
195 l_last_name per_all_people_f.last_name%TYPE;
196 l_first_name per_all_people_f.first_name%TYPE;
197
198 l_local_unit_id NUMBER;
199 l_legal_employer_name VARCHAR2 (100);
200 l_org_number VARCHAR2 (100);
201 l_location_id VARCHAR2 (100);
202 l_phone_number VARCHAR2 (100);
203 l_location_code VARCHAR2 (100);
204 l_address_line_1 VARCHAR2 (100);
205 l_address_line_2 VARCHAR2 (100);
206 l_address_line_3 VARCHAR2 (100);
207 l_postal_code VARCHAR2 (100);
208 l_town_or_city VARCHAR2 (100);
209 l_region_1 VARCHAR2 (100);
210 l_region_2 VARCHAR2 (100);
211 l_territory_short_name VARCHAR2 (100);
212 l_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE;
213 l_oth_comp NUMBER;
214 bname VARCHAR2 (100);
215 l_month VARCHAR2 (2);
216 l_year VARCHAR2 (4);
217 l_reporting_date DATE;
218 l_tot_addl_time_hw NUMBER;
219 l_tot_relief_duty_hours NUMBER;
220 l_tot_relief_duty_hw NUMBER;
221 l_tot_overtime_hours NUMBER;
222 l_tot_overtime_hw NUMBER;
223 l_tot_addl_time_hours NUMBER;
224 l_addl_time_hw NUMBER;
225 l_relief_duty_hours NUMBER;
226 l_relief_duty_hw NUMBER;
227 l_overtime_hours NUMBER;
228 l_overtime_hw NUMBER;
229 l_addl_time_hours NUMBER;
230 l_legal_employer_id NUMBER;
231 l_dimension VARCHAR2 (100);
232 l_report_start_date DATE;
233 l_report_end_date DATE;
234 l_asg_effective_start_date DATE;
235 l_asg_effective_end_date DATE;
236
237
238 l_get_defined_balance_id NUMBER;
239 l_count NUMBER;
240 l_hourly_pay_variable VARCHAR(4); -- EOY 2008
241 l_other_tax_compensation VARCHAR(4); -- EOY 2008
242 l_overtime_mw NUMBER; -- EOY 2008
243 l_addl_time_mw NUMBER; -- EOY 2008
244 l_relief_duty_mw NUMBER; -- EOY 2008
245 l_tot_relief_duty_mw NUMBER; -- EOY 2008
246 count_months NUMBER; -- EOY 2008
247 l_days_worked VARCHAR(10); -- EOY 2008
248 l_artistic_work VARCHAR(10); -- EOY 2008
249
250 l_sick_pay_hours NUMBER; -- Bug# 9222739 fix
251 l_sick_pay NUMBER; -- Bug# 9222739 fix
252
253 -- *****************************************************************************
254 -- CURSOR
255
256 /* Cursor to retrieve Balance Types having a particular Balance Category */
257
258 CURSOR csr_asg_effective_date (
259 p_asg_id number,
260 p_end_date date,
261 p_start_date date,
262 p_business_group_id number
263 ) IS
264 SELECT MAX (effective_end_date) effective_date
265 FROM per_all_assignments_f paa
266 WHERE assignment_id = p_asg_id
267 AND paa.effective_start_date <= p_end_date
268 AND paa.effective_end_date > = p_start_date
269 AND assignment_status_type_id
270 IN
271 (SELECT assignment_status_type_id
272 FROM per_assignment_status_types
273 WHERE per_system_status = 'ACTIVE_ASSIGN'
274 AND active_flag = 'Y'
275 AND ((legislation_code is null and business_group_id is null)
276 OR (business_group_id = p_business_group_id)
277 ));
278
279 lr_asg_effective_date csr_asg_effective_date%ROWTYPE;
280
281 CURSOR csr_balance
282 (p_balance_category_name VARCHAR2
283 ,p_business_group_id NUMBER)
284 IS
285 SELECT REPLACE(UPPER(pbt.balance_name),' ' ,'_') balance_name , pbt.balance_name bname
286 FROM pay_balance_types pbt , pay_balance_categories_f pbc
287 WHERE pbc.legislation_code='SE'
288 AND pbt.business_group_id =p_business_group_id
289 AND pbt.balance_category_id = pbc.balance_category_id
290 AND pbc.category_name = p_balance_category_name ;
291
292
293 /* Cursor to retrieve Defined Balance Id */
294 Cursor csr_bg_get_defined_balance_id
295 (csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE
296 ,p_business_group_id NUMBER)
297 IS
298 SELECT ue.creator_id
299 FROM ff_user_entities ue,
300 ff_database_items di
301 WHERE di.user_name = csr_v_Balance_Name
302 AND ue.user_entity_id = di.user_entity_id
303 AND ue.legislation_code is NULL
304 AND ue.business_group_id = p_business_group_id
305 AND ue.creator_type = 'B';
306
307 rg_csr_bg_get_defined_bal_id csr_bg_get_defined_balance_id%rowtype;
308
309 CURSOR csr_address_details (
310 csr_v_location_id hr_locations.location_id%TYPE
311 )
312 IS
313 SELECT hl.location_code
314 ,hl.description
315 ,hl.address_line_1
316 ,hl.address_line_2
317 ,hl.address_line_3
318 ,hl.postal_code
319 ,hl.town_or_city
320 ,hl.region_1
321 ,hl.region_2
322 ,ft.territory_short_name
323 FROM hr_organization_units hou
324 ,hr_locations hl
325 ,fnd_territories_vl ft
326 WHERE hl.location_id = csr_v_location_id
327 AND hl.country = ft.territory_code;
328
329 lr_address_details csr_address_details%ROWTYPE;
330
331 CURSOR csr_legal_employer_details (
332 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
333 )
334 IS
335 SELECT o.NAME
336 ,hoi3.org_information2 "ORG_NUMBER"
337 ,o.location_id
338 ,o.organization_id
339 FROM hr_all_organization_units o
340 ,hr_organization_information hoi1
341 ,hr_organization_information hoi2
342 ,hr_organization_information hoi3
343 WHERE o.business_group_id = g_business_group_id
344 AND hoi1.organization_id = o.organization_id
345 AND hoi1.org_information_context = 'CLASS'
346 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
347 AND hoi2.organization_id = hoi1.organization_id
348 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
349 AND hoi2.org_information1 = csr_v_local_unit_id
350 AND o.organization_id = hoi3.organization_id
351 AND hoi3.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
352
353 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
354
355 CURSOR csr_contact_details (
356 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
357 )
358 IS
359 SELECT hoi4.org_information3
360 FROM hr_organization_information hoi4
361 WHERE hoi4.organization_id = csr_v_legal_employer_id
362 AND hoi4.org_information_context = 'SE_ORG_CONTACT_DETAILS'
363 AND hoi4.org_information_id =
364 (SELECT MIN (org_information_id)
365 FROM hr_organization_information
366 WHERE organization_id = csr_v_legal_employer_id
367 AND org_information_context = 'SE_ORG_CONTACT_DETAILS'
368 AND org_information1 = 'PHONE');
369
370 lr_contact_details csr_contact_details%ROWTYPE;
371
372 CURSOR csr_person_info (
373 csr_v_person_id per_all_people_f.person_id%TYPE
374 ,csr_v_effective_date per_all_people_f.effective_start_date%TYPE
375 )
376 IS
377 SELECT *
378 FROM per_all_people_f p
379 WHERE p.business_group_id = g_business_group_id
380 AND p.person_id = csr_v_person_id
381 AND csr_v_effective_date BETWEEN p.effective_start_date
382 AND p.effective_end_date;
383
384 lr_person_info csr_person_info%ROWTYPE;
385
386 CURSOR csr_assignment_info (
387 csr_v_person_id per_all_people_f.person_id%TYPE
388 ,csr_v_assignment_id per_all_assignments_f.person_id%TYPE
389 ,csr_v_effective_date per_all_assignments_f.effective_start_date%TYPE
390 )
391 IS
392 SELECT *
393 FROM per_all_assignments_f p
394 WHERE p.business_group_id = g_business_group_id
395 AND p.assignment_id = csr_v_assignment_id
396 AND p.person_id = csr_v_person_id
397 AND csr_v_effective_date BETWEEN p.effective_start_date
398 AND p.effective_end_date;
399
400 lr_assignment_info csr_assignment_info%ROWTYPE;
401
402
403 CURSOR csr_soft_coded_keyflex_info (
404 csr_v_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE
405 )
406 IS
407 SELECT *
408 FROM hr_soft_coding_keyflex
409 WHERE soft_coding_keyflex_id = csr_v_soft_coding_keyflex_id;
410
411 lr_soft_coded_keyflex_info csr_soft_coded_keyflex_info%ROWTYPE;
412
413
414
415 -- Cursor to extract the hourly pay variables in the Completion Working report
416
417
418 CURSOR csr_extra_assignment_info (
419 csr_v_assignment_id per_all_assignments_f.person_id%TYPE
420 ,csr_v_information_type per_assignment_extra_info.information_type%TYPE
421 )
422 IS
423 SELECT *
424 FROM per_assignment_extra_info
425 WHERE assignment_id = csr_v_assignment_id
426 AND information_type = csr_v_information_type;
427
428 lr_extra_assignment_info csr_extra_assignment_info%ROWTYPE;
429
430
431 CURSOR csr_se_wtc_time_worked_info (
432 csr_v_assignment_id per_all_assignments_f.person_id%TYPE
433 ,csr_v_year per_assignment_extra_info.aei_information2%TYPE
434 ,csr_v_month per_assignment_extra_info.aei_information3%TYPE
435 )
436 IS
437 SELECT *
438 FROM per_assignment_extra_info
439 WHERE assignment_id = csr_v_assignment_id
440 AND information_type = 'SE_WTC_TIME_WORKED_INFO'
441 AND aei_information1 = csr_v_year
442 AND aei_information2 = csr_v_month;
443
444 lr_se_wtc_time_worked_info csr_se_wtc_time_worked_info%ROWTYPE;
445 -- Archiving the data , as this will fire once
446 -- *****************************************************************************
447 -- *****************************************************************************
448 BEGIN
449
450
451 -- *****************************************************************************
452 IF g_debug THEN
453 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
454 END IF;
455
456 -- *****************************************************************************
457
458
459 g_payroll_action_id := p_payroll_action_id;
460 g_business_group_id := NULL;
461 g_effective_date := NULL;
462 g_person_id := NULL;
463 g_assignment_id := NULL;
464
465 PAY_SE_ARCHIVE_CWCA.get_all_parameters (p_payroll_action_id
466 ,g_business_group_id
467 ,g_effective_date
468 ,g_person_id
469 ,g_assignment_id
470 ,g_still_employed
471 ,g_report_start_year
472 ,g_report_start_month
473 );
474 p_sql :=
475 'SELECT DISTINCT person_id
476 FROM per_people_f ppf
477 ,pay_payroll_actions ppa
478 WHERE ppa.payroll_action_id = :payroll_action_id
479 AND ppa.business_group_id = ppf.business_group_id
480 AND ppf.person_id = '''
481 || g_person_id
482 || '''
483 ORDER BY ppf.person_id';
484
485
486 -- *****************************************************************************
487 --START OF PICKING UP DATA
488
489 l_report_start_date := TO_DATE('01/'||g_report_start_month||'/'||g_report_start_year,'DD/MM/YYYY');
490
491 fnd_file.put_line (fnd_file.LOG, 'g_assignment_id'||g_assignment_id);
492 fnd_file.put_line (fnd_file.LOG, 'l_report_end_date'||to_char(l_report_end_date));
493 fnd_file.put_line (fnd_file.LOG, 'l_report_start_date'||to_char(l_report_start_date));
494 fnd_file.put_line (fnd_file.LOG, 'g_business_group_id'||to_char(g_business_group_id));
495
496
497 OPEN csr_asg_effective_date ( g_assignment_id, g_effective_date , l_report_start_date, g_business_group_id);
498 FETCH csr_asg_effective_date INTO lr_asg_effective_date;
499 CLOSE csr_asg_effective_date;
500
501 l_asg_effective_end_date := lr_asg_effective_date.effective_date;
502
503 IF l_asg_effective_end_date <= l_report_end_date THEN
504
505 SELECT LAST_DAY(l_asg_effective_end_date)
506 INTO l_report_end_date
507 FROM DUAL;
508 ELSE
509
510 SELECT LAST_DAY(g_effective_date)
511 INTO l_report_end_date
512 FROM DUAL;
513
514 END IF;
515
516 fnd_file.put_line (fnd_file.LOG, 'l_report_end_date'||to_char(l_report_end_date));
517
518 IF lr_asg_effective_date.effective_date IS NOT NULL THEN
519
520
521 l_tot_addl_time_hw :=0;
522 l_tot_relief_duty_hours :=0;
523 l_tot_relief_duty_hw :=0;
524 l_tot_overtime_hours :=0;
525 l_tot_overtime_hw :=0;
526 l_tot_addl_time_hours :=0;
527 l_addl_time_hw :=0;
528 l_relief_duty_hours :=0;
529 l_relief_duty_hw :=0;
530 l_overtime_hours :=0;
531 l_overtime_hw :=0;
532 l_addl_time_hours :=0;
533 l_relief_duty_mw :=0;
534 l_tot_relief_duty_mw :=0;
535
536 l_sick_pay_hours := 0; -- Bug# 9222739 fix
537 l_sick_pay := 0; -- Bug# 9222739 fix
538
539
540
541 -- Insert the report Parameters
542 pay_action_information_api.create_action_information
543 (p_action_information_id => l_action_info_id
544 ,p_action_context_id => p_payroll_action_id
545 ,p_action_context_type => 'PA'
546 ,p_object_version_number => l_ovn
547 ,p_effective_date => g_effective_date
548 ,p_source_id => NULL
549 ,p_source_text => NULL
550 ,p_action_information_category => 'EMEA REPORT DETAILS'
551 ,p_action_information1 => 'PYSECWCA'
552 ,p_action_information2 => g_person_id
553 ,p_action_information3 => g_assignment_id
554 ,p_action_information4 => g_still_employed
555 ,p_action_information5 => g_business_group_id
556 ,p_action_information6 => g_report_start_year
557 ,p_action_information7 => g_report_start_month
558 ,p_action_information8 => NULL
559 ,p_action_information9 => NULL
560 ,p_action_information10 => NULL
561 );
562
563
564 OPEN csr_person_info (g_person_id, lr_asg_effective_date.effective_date);
565 FETCH csr_person_info INTO lr_person_info;
566 CLOSE csr_person_info;
567
568 l_person_number := lr_person_info.national_identifier;
569 l_last_name := lr_person_info.last_name;
570 l_first_name := lr_person_info.first_name;
571
572 OPEN csr_assignment_info (g_person_id, g_assignment_id, lr_asg_effective_date.effective_date);
573 FETCH csr_assignment_info INTO lr_assignment_info;
574 CLOSE csr_assignment_info;
575 l_soft_coding_keyflex_id := lr_assignment_info.soft_coding_keyflex_id;
576
577 l_asg_effective_start_date := lr_assignment_info.effective_start_date;
578
579 -- *****************************************************************************
580 -- SOFT CODED FLEX
581 OPEN csr_soft_coded_keyflex_info (l_soft_coding_keyflex_id);
582 FETCH csr_soft_coded_keyflex_info INTO lr_soft_coded_keyflex_info;
583 CLOSE csr_soft_coded_keyflex_info;
584
585 l_local_unit_id := lr_soft_coded_keyflex_info.segment2;
586 -- *****************************************************************************
587 -- *****************************************************************************
588 -- Legal Employer Details
589 OPEN csr_legal_employer_details (l_local_unit_id);
590 FETCH csr_legal_employer_details INTO lr_legal_employer_details;
591 CLOSE csr_legal_employer_details;
592
593 l_legal_employer_name := lr_legal_employer_details.NAME;
594 l_org_number := lr_legal_employer_details.org_number;
595 l_location_id := lr_legal_employer_details.location_id;
596 l_legal_employer_id := lr_legal_employer_details.organization_id;
597
598 -- Employer and Signature
599 OPEN csr_contact_details (lr_legal_employer_details.organization_id);
600 FETCH csr_contact_details INTO lr_contact_details;
601 CLOSE csr_contact_details;
602
603 l_phone_number := lr_contact_details.org_information3;
604
605 OPEN csr_address_details (l_location_id);
606 FETCH csr_address_details INTO lr_address_details;
607 CLOSE csr_address_details;
608
609 l_location_code := lr_address_details.location_code;
610 l_address_line_1 := lr_address_details.address_line_1;
611 l_address_line_2 := lr_address_details.address_line_2;
612 l_address_line_3 := lr_address_details.address_line_3;
613 l_postal_code := lr_address_details.postal_code;
614 -- Bug#8849455 fix Added space between 3 and 4 digits in postal code
615 l_postal_code := substr(l_postal_code,1,3)||' '||substr(l_postal_code,4,2);
616 l_town_or_city := lr_address_details.town_or_city;
617 l_region_1 := lr_address_details.region_1;
618 l_region_2 := lr_address_details.region_2;
619 l_territory_short_name := lr_address_details.territory_short_name;
620
621 pay_action_information_api.create_action_information
622 (p_action_information_id => l_action_info_id
623 ,p_action_context_id => p_payroll_action_id
624 ,p_action_context_type => 'PA'
625 ,p_object_version_number => l_ovn
626 ,p_effective_date => g_effective_date
627 ,p_source_id => NULL
628 ,p_source_text => NULL
629 ,p_action_information_category => 'EMEA REPORT INFORMATION'
630 ,p_action_information1 => 'PYSECWCA'
631 ,p_action_information2 => 'CWC1'
632 ,p_action_information3 => l_person_number
633 ,p_action_information4 => l_last_name
634 ,p_action_information5 => l_first_name
635 ,p_action_information6 => l_legal_employer_name
636 ,p_action_information7 => l_org_number
637 ,p_action_information8 => l_location_code
638 ,p_action_information9 => l_address_line_1
639 ,p_action_information10 => l_address_line_2
640 ,p_action_information11 => l_address_line_3
641 ,p_action_information12 => l_postal_code
642 ,p_action_information13 => l_town_or_city
643 ,p_action_information14 => l_region_1
644 ,p_action_information15 => l_region_2
645 ,p_action_information16 => l_territory_short_name
646 ,p_action_information17 => l_phone_number
647 ,p_action_information18 => NULL
648 ,p_action_information19 => NULL
649 ,p_action_information20 => NULL
650 ,p_action_information21 => NULL
651 ,p_action_information22 => NULL
652 ,p_action_information23 => NULL
653 ,p_action_information24 => NULL
654 ,p_action_information25 => NULL
655 ,p_action_information26 => NULL
656 ,p_action_information27 => NULL
657 ,p_action_information28 => NULL
658 ,p_action_information29 => NULL
659 ,p_action_information30 => g_person_id
660 ,p_assignment_id => g_assignment_id
661 );
662
663 l_dimension:='_ASG_LE_MONTH';
664
665
666 /* Setting Context */
667 BEGIN
668 pay_balance_pkg.set_context('ASSIGNMENT_ID',g_assignment_id);
669 pay_balance_pkg.set_context('TAX_UNIT_ID',l_legal_employer_id);
670 END;
671
672
673 --- Check for Artistic Work
674
675 lr_extra_assignment_info := NULL;
676
677 OPEN csr_extra_assignment_info (g_assignment_id
678 ,'SE_WTC_TIME_WORKED_HEADER'
679 );
680
681 FETCH csr_extra_assignment_info
682 INTO lr_extra_assignment_info;
683
684 CLOSE csr_extra_assignment_info;
685
686 l_artistic_work := lr_extra_assignment_info.aei_information6;
687
688
689 --- Display only 26 months
690
691 count_months := 1;
692
693 WHILE l_report_start_date <= l_report_end_date AND count_months < 27
694
695 --WHILE l_report_start_date <= l_report_end_date
696
697 --FOR i IN 1..12
698 LOOP
699
700 --SELECT last_day(l_report_start_date), LPAD(TO_CHAR(l_report_start_date,'MM'), 2,'0'),TO_CHAR(l_report_start_date,'YYYY')
701 --INTO l_reporting_date, l_month, l_year
702 --FROM DUAL;
703
704 SELECT last_day(l_report_end_date), LPAD(TO_CHAR(l_report_end_date,'MM'), 2,'0'),TO_CHAR(l_report_end_date,'YYYY')
705 INTO l_reporting_date, l_month, l_year
706 FROM DUAL;
707
708 IF l_artistic_work = 'Y'
709 THEN
710 OPEN csr_se_wtc_time_worked_info (g_assignment_id,l_year,l_month);
711 FETCH csr_se_wtc_time_worked_info INTO lr_se_wtc_time_worked_info;
712 CLOSE csr_se_wtc_time_worked_info;
713 l_days_worked := lr_se_wtc_time_worked_info.aei_information3;
714 END IF;
715
716 -- fnd_file.put_line (fnd_file.LOG, 'l_days_worked'||l_days_worked);
717
718 BEGIN
719 l_addl_time_hours :=0;
720 l_tot_addl_time_hours:=0;
721 FOR balance_rec IN csr_balance('Additional Time - Hours' , g_business_group_id)
722 LOOP
723
724 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
725 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
726 CLOSE csr_bg_Get_Defined_Balance_Id;
727
728
729
730 IF csr_balance%FOUND THEN
731
732 l_addl_time_hours :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
733 l_tot_addl_time_hours := l_tot_addl_time_hours + nvl(l_addl_time_hours,0);
734 END IF;
735 END LOOP ;
736
737 EXCEPTION
738 WHEN others THEN
739 fnd_file.put_line (fnd_file.LOG, 'Error'||substr(sqlerrm,1,30));
740 null;
741 END;
742 l_addl_time_hw :=0;
743 l_tot_addl_time_hw :=0;
744 BEGIN
745 FOR balance_rec IN csr_balance('Additional Time - Hourly Wages' , g_business_group_id)
746 LOOP
747 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
748 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
749 CLOSE csr_bg_Get_Defined_Balance_Id;
750 IF csr_balance%FOUND THEN
751 l_addl_time_hw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
752 l_tot_addl_time_hw := l_tot_addl_time_hw + nvl(l_addl_time_hw,0);
753 END IF;
754 END LOOP ;
755
756 EXCEPTION
757 WHEN others THEN
758 null;
759 END;
760 l_relief_duty_hours :=0;
761 l_tot_relief_duty_hours :=0;
762 BEGIN
763 FOR balance_rec IN csr_balance('Relief/ Duty - Hours' , g_business_group_id)
764 LOOP
765 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
766 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
767 CLOSE csr_bg_Get_Defined_Balance_Id;
768 IF csr_balance%FOUND THEN
769 l_relief_duty_hours :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
770 l_tot_relief_duty_hours := l_tot_relief_duty_hours + nvl(l_relief_duty_hours,0);
771 END IF;
772 END LOOP ;
773
774 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_relief_duty_hours'||l_tot_relief_duty_hours);
775
776 EXCEPTION
777 WHEN others THEN
778 null;
779 END;
780
781 l_relief_duty_hw :=0;
782 l_tot_relief_duty_hw :=0;
783 BEGIN
784 FOR balance_rec IN csr_balance('Relief/ Duty - Hourly Wages' , g_business_group_id)
785 LOOP
786 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
787 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
788 CLOSE csr_bg_Get_Defined_Balance_Id;
789 IF csr_balance%FOUND THEN
790 l_relief_duty_hw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
791 l_tot_relief_duty_hw := l_tot_relief_duty_hw + nvl(l_relief_duty_hw,0);
792 END IF;
793 END LOOP ;
794
795 EXCEPTION
796 WHEN others THEN
797 null;
798 END;
799
800
801 l_relief_duty_mw :=0;
802 l_tot_relief_duty_mw :=0;
803 BEGIN
804 FOR balance_rec IN csr_balance('Relief/ Duty - Monthly Wages' , g_business_group_id)
805 LOOP
806 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
807 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
808 CLOSE csr_bg_Get_Defined_Balance_Id;
809 IF csr_balance%FOUND THEN
810 l_relief_duty_mw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
811 l_tot_relief_duty_mw := l_tot_relief_duty_mw + nvl(l_relief_duty_mw,0);
812 END IF;
813 END LOOP ;
814
815 EXCEPTION
816 WHEN others THEN
817 null;
818 END;
819
820 l_overtime_hours :=0;
821 l_tot_overtime_hours :=0;
822 BEGIN
823 FOR balance_rec IN csr_balance('Overtime - Hours' , g_business_group_id)
824 LOOP
825 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
826 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
827 CLOSE csr_bg_Get_Defined_Balance_Id;
828 IF csr_balance%FOUND THEN
829 l_overtime_hours :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
830 l_tot_overtime_hours := l_tot_overtime_hours + nvl(l_overtime_hours,0);
831 END IF;
832 END LOOP ;
833
834 EXCEPTION
835 WHEN others THEN
836 null;
837 END;
838 l_overtime_hw :=0;
839 l_tot_overtime_hw:=0;
840 BEGIN
841 FOR balance_rec IN csr_balance('Overtime - Hourly Wages' , g_business_group_id)
842 LOOP
843 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
844 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
845 CLOSE csr_bg_Get_Defined_Balance_Id;
846 IF csr_balance%FOUND THEN
847 l_overtime_hw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
848 l_tot_overtime_hw := l_tot_overtime_hw + nvl(l_overtime_hw,0);
849 END IF;
850 END LOOP ;
851
852 EXCEPTION
853 WHEN others THEN
854 null;
855 END;
856
857 pay_action_information_api.create_action_information
858 (p_action_information_id => l_action_info_id
859 ,p_action_context_id => p_payroll_action_id
860 ,p_action_context_type => 'PA'
861 ,p_object_version_number => l_ovn
862 ,p_effective_date => g_effective_date
863 ,p_source_id => NULL
864 ,p_source_text => NULL
865 ,p_action_information_category => 'EMEA REPORT INFORMATION'
866 ,p_action_information1 => 'PYSECWCA'
867 ,p_action_information2 => 'CWC2'
868 ,p_action_information3 => l_month
869 ,p_action_information4 => l_tot_addl_time_hw
870 ,p_action_information5 => l_tot_relief_duty_hours
871 ,p_action_information6 => l_tot_relief_duty_hw
872 ,p_action_information7 => l_tot_overtime_hours
873 ,p_action_information8 => l_tot_overtime_hw
874 ,p_action_information9 => l_tot_addl_time_hours
875 ,p_action_information10 => l_year
876 ,p_action_information11 => l_tot_relief_duty_mw -- EOY 2008
877 ,p_action_information12 => l_days_worked -- EOY 2008
878 ,p_action_information13 => NULL
879 ,p_action_information14 => NULL
880 ,p_action_information15 => NULL
881 ,p_action_information16 => NULL
882 ,p_action_information17 => NULL
883 ,p_action_information18 => NULL
884 ,p_action_information19 => NULL
885 ,p_action_information20 => NULL
886 ,p_action_information21 => NULL
887 ,p_action_information22 => NULL
888 ,p_action_information23 => NULL
889 ,p_action_information24 => NULL
890 ,p_action_information25 => NULL
891 ,p_action_information26 => NULL
892 ,p_action_information27 => NULL
893 ,p_action_information28 => NULL
894 ,p_action_information29 => NULL
895 ,p_action_information30 => g_person_id
896 ,p_assignment_id => g_assignment_id
897 );
898
899
900 l_overtime_hw :=0;
901
902 ---- Check for the Other Taxable Compensation Variable in the Working Time Certificate Report
903
904
905 -- Salary Information in Working Time Certificate of Section 12
906 -- has vaiable Other Compensation
907 -- Assignment EIT of Income Info has Other Compensation marked as 'YES'
908
909 OPEN csr_extra_assignment_info (g_assignment_id, 'SE_WTC_INCOME_INFO');
910
911
912
913 FETCH csr_extra_assignment_info
914 INTO lr_extra_assignment_info;
915
916 CLOSE csr_extra_assignment_info;
917
918 l_hourly_pay_variable := lr_extra_assignment_info.aei_information9;
919 l_other_tax_compensation := lr_extra_assignment_info.aei_information12;
920
921 -- fnd_file.put_line (fnd_file.LOG, 'l_other_tax_compensation'||l_other_tax_compensation);
922 --fnd_file.put_line (fnd_file.LOG, 'l_reporting_date'||l_reporting_date);
923
924
925 IF l_other_tax_compensation ='Y'
926 THEN
927 l_oth_comp :=0;
928
929 BEGIN
930 FOR balance_rec IN csr_balance('Other Compensation' , g_business_group_id)
931 LOOP
932
933 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
934 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
935 CLOSE csr_bg_Get_Defined_Balance_Id;
936
937 IF csr_balance%FOUND THEN
938
939 l_oth_comp :=0;
940 l_oth_comp :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
941 --fnd_file.put_line (fnd_file.LOG, 'l_oth_comp' ||l_oth_comp);
942
943 IF l_oth_comp > 0 THEN
944
945 pay_action_information_api.create_action_information
946 (p_action_information_id => l_action_info_id
947 ,p_action_context_id => p_payroll_action_id
948 ,p_action_context_type => 'PA'
949 ,p_object_version_number => l_ovn
950 ,p_effective_date => g_effective_date
951 ,p_source_id => NULL
952 ,p_source_text => NULL
953 ,p_action_information_category => 'EMEA REPORT INFORMATION'
954 ,p_action_information1 => 'PYSECWCA'
955 ,p_action_information2 => 'CWC3'
956 ,p_action_information3 => l_month
957 ,p_action_information4 => balance_rec.bname
958 ,p_action_information5 => l_oth_comp
959 ,p_action_information6 => NULL
960 ,p_action_information7 => NULL
961 ,p_action_information8 => NULL
962 ,p_action_information9 => NULL
963 ,p_action_information10 => l_year
964 ,p_action_information11 => NULL
965 ,p_action_information12 => NULL
966 ,p_action_information13 => NULL
967 ,p_action_information14 => NULL
968 ,p_action_information15 => NULL
969 ,p_action_information16 => NULL
970 ,p_action_information17 => NULL
971 ,p_action_information18 => NULL
972 ,p_action_information19 => NULL
973 ,p_action_information20 => NULL
974 ,p_action_information21 => NULL
975 ,p_action_information22 => NULL
976 ,p_action_information23 => NULL
977 ,p_action_information24 => NULL
978 ,p_action_information25 => NULL
979 ,p_action_information26 => NULL
980 ,p_action_information27 => NULL
981 ,p_action_information28 => NULL
982 ,p_action_information29 => NULL
983 ,p_action_information30 => g_person_id
984 ,p_assignment_id => g_assignment_id
985 );
986
987 END IF;
988
989 END IF;
990 END LOOP ;
991
992 -- Bug# 9222739 fix starts
993
994 fnd_file.put_line (fnd_file.LOG, '$$$ l_reporting_date'||l_reporting_date);
995 fnd_file.put_line (fnd_file.LOG, '$$$ l_month'||l_month);
996
997 l_sick_pay_hours := GET_DEFINED_BALANCE_VALUE(g_assignment_id, 'Total Sick Pay Hours','_ASG_LE_MONTH',l_reporting_date);
998 l_sick_pay := GET_DEFINED_BALANCE_VALUE(g_assignment_id, 'Total Sick Pay','_ASG_LE_MONTH',l_reporting_date);
999 fnd_file.put_line (fnd_file.LOG, '$$$ l_sick_pay_hours'||l_sick_pay_hours);
1000 fnd_file.put_line (fnd_file.LOG, '$$$ l_sick_pay'||l_sick_pay);
1001
1002 IF l_sick_pay_hours > 0 THEN
1003 pay_action_information_api.create_action_information
1004 (p_action_information_id => l_action_info_id
1005 ,p_action_context_id => p_payroll_action_id
1006 ,p_action_context_type => 'PA'
1007 ,p_object_version_number => l_ovn
1008 ,p_effective_date => g_effective_date
1009 ,p_source_id => NULL
1010 ,p_source_text => NULL
1011 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1012 ,p_action_information1 => 'PYSECWCA'
1013 ,p_action_information2 => 'CWC6'
1014 ,p_action_information3 => l_month
1015 ,p_action_information4 => l_sick_pay_hours
1016 ,p_action_information5 => l_sick_pay
1017 ,p_action_information6 => NULL
1018 ,p_action_information7 => NULL
1019 ,p_action_information8 => NULL
1020 ,p_action_information9 => NULL
1021 ,p_action_information10 => l_year
1022 ,p_action_information11 => NULL
1023 ,p_action_information12 => NULL
1024 ,p_action_information13 => NULL
1025 ,p_action_information14 => NULL
1026 ,p_action_information15 => NULL
1027 ,p_action_information16 => NULL
1028 ,p_action_information17 => NULL
1029 ,p_action_information18 => NULL
1030 ,p_action_information19 => NULL
1031 ,p_action_information20 => NULL
1032 ,p_action_information21 => NULL
1033 ,p_action_information22 => NULL
1034 ,p_action_information23 => NULL
1035 ,p_action_information24 => NULL
1036 ,p_action_information25 => NULL
1037 ,p_action_information26 => NULL
1038 ,p_action_information27 => NULL
1039 ,p_action_information28 => NULL
1040 ,p_action_information29 => NULL
1041 ,p_action_information30 => g_person_id
1042 ,p_assignment_id => g_assignment_id
1043 );
1044
1045 END IF;
1046 -- Bug# 9222739 fix ends
1047
1048 EXCEPTION
1049 WHEN others THEN
1050 fnd_file.put_line (fnd_file.LOG, 'Error in Other Compensation'||substr(sqlerrm,1,30));
1051 END;
1052
1053 END IF; --- End of Validation for Other Tax Compensation in Assignment EIT
1054
1055
1056
1057 ----- If the Over Time and Additional Pay are Variables then we insert the following cases...
1058
1059
1060 -- Salary Information in Working Time Certificate of Section 12
1061 -- has vaiable Additional and Overtime Pay
1062 -- Assignment EIT of Income Info has Hourly Pay Variable marked as 'YES'
1063
1064 --fnd_file.put_line (fnd_file.LOG, 'l_hourly_pay_variable'||l_hourly_pay_variable);
1065
1066 IF l_hourly_pay_variable = 'Y'
1067 THEN
1068
1069 l_overtime_mw :=0;
1070
1071 BEGIN
1072
1073 FOR balance_rec IN csr_balance('Overtime - Monthly Wages' , g_business_group_id)
1074 LOOP
1075
1076 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1077 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1078 CLOSE csr_bg_Get_Defined_Balance_Id;
1079
1080 IF csr_balance%FOUND THEN
1081 l_overtime_mw := 0;
1082 l_overtime_mw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
1083 --fnd_file.put_line (fnd_file.LOG, 'l_overtime_mw' ||l_overtime_mw);
1084 IF l_overtime_mw > 0 THEN
1085
1086 pay_action_information_api.create_action_information
1087 (p_action_information_id => l_action_info_id
1088 ,p_action_context_id => p_payroll_action_id
1089 ,p_action_context_type => 'PA'
1090 ,p_object_version_number => l_ovn
1091 ,p_effective_date => g_effective_date
1092 ,p_source_id => NULL
1093 ,p_source_text => NULL
1094 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1095 ,p_action_information1 => 'PYSECWCA'
1096 ,p_action_information2 => 'CWC4'
1097 ,p_action_information3 => l_month
1098 ,p_action_information4 => balance_rec.bname
1099 ,p_action_information5 => l_overtime_mw
1100 ,p_action_information6 => NULL
1101 ,p_action_information7 => NULL
1102 ,p_action_information8 => NULL
1103 ,p_action_information9 => NULL
1104 ,p_action_information10 => l_year
1105 ,p_action_information11 => NULL
1106 ,p_action_information12 => NULL
1107 ,p_action_information13 => NULL
1108 ,p_action_information14 => NULL
1109 ,p_action_information15 => NULL
1110 ,p_action_information16 => NULL
1111 ,p_action_information17 => NULL
1112 ,p_action_information18 => NULL
1113 ,p_action_information19 => NULL
1114 ,p_action_information20 => NULL
1115 ,p_action_information21 => NULL
1116 ,p_action_information22 => NULL
1117 ,p_action_information23 => NULL
1118 ,p_action_information24 => NULL
1119 ,p_action_information25 => NULL
1120 ,p_action_information26 => NULL
1121 ,p_action_information27 => NULL
1122 ,p_action_information28 => NULL
1123 ,p_action_information29 => NULL
1124 ,p_action_information30 => g_person_id
1125 ,p_assignment_id => g_assignment_id
1126 );
1127
1128 END IF;
1129
1130 END IF;
1131 END LOOP ;
1132
1133
1134 EXCEPTION
1135 WHEN others THEN
1136 fnd_file.put_line (fnd_file.LOG, 'Error in Overtime'||substr(sqlerrm,1,30));
1137 END; -- End of Begin for Overtime Variable Pay
1138
1139
1140 ------------------------------------Additional/Supplementary hours - if variable ---
1141
1142
1143
1144
1145 BEGIN
1146 l_addl_time_mw :=0;
1147
1148 FOR balance_rec IN csr_balance('Additional Time -Monthly Wages' , g_business_group_id)
1149 LOOP
1150
1151 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1152 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1153 CLOSE csr_bg_Get_Defined_Balance_Id;
1154 --fnd_file.put_line (fnd_file.LOG, 'check_addtional' ||l_addl_time_mw);
1155
1156 IF csr_balance%FOUND THEN
1157
1158 l_addl_time_mw :=0;
1159 l_addl_time_mw :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>rg_csr_bg_get_defined_bal_id.creator_id, P_ASSIGNMENT_ID =>g_assignment_id, P_VIRTUAL_DATE =>l_reporting_date ) ;
1160 --fnd_file.put_line (fnd_file.LOG, 'l_addl_time_mw' ||l_addl_time_mw);
1161 IF l_addl_time_mw > 0 THEN
1162
1163 pay_action_information_api.create_action_information
1164 (p_action_information_id => l_action_info_id
1165 ,p_action_context_id => p_payroll_action_id
1166 ,p_action_context_type => 'PA'
1167 ,p_object_version_number => l_ovn
1168 ,p_effective_date => g_effective_date
1169 ,p_source_id => NULL
1170 ,p_source_text => NULL
1171 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1172 ,p_action_information1 => 'PYSECWCA'
1173 ,p_action_information2 => 'CWC5'
1174 ,p_action_information3 => l_month
1175 ,p_action_information4 => balance_rec.bname
1176 ,p_action_information5 => l_addl_time_mw
1177 ,p_action_information6 => NULL
1178 ,p_action_information7 => NULL
1179 ,p_action_information8 => NULL
1180 ,p_action_information9 => NULL
1181 ,p_action_information10 => l_year
1182 ,p_action_information11 => NULL
1183 ,p_action_information12 => NULL
1184 ,p_action_information13 => NULL
1185 ,p_action_information14 => NULL
1186 ,p_action_information15 => NULL
1187 ,p_action_information16 => NULL
1188 ,p_action_information17 => NULL
1189 ,p_action_information18 => NULL
1190 ,p_action_information19 => NULL
1191 ,p_action_information20 => NULL
1192 ,p_action_information21 => NULL
1193 ,p_action_information22 => NULL
1194 ,p_action_information23 => NULL
1195 ,p_action_information24 => NULL
1196 ,p_action_information25 => NULL
1197 ,p_action_information26 => NULL
1198 ,p_action_information27 => NULL
1199 ,p_action_information28 => NULL
1200 ,p_action_information29 => NULL
1201 ,p_action_information30 => g_person_id
1202 ,p_assignment_id => g_assignment_id
1203 );
1204
1205 END IF;
1206
1207 END IF;
1208 END LOOP ;
1209
1210
1211 EXCEPTION
1212 WHEN others THEN
1213 fnd_file.put_line (fnd_file.LOG, 'Error in Additional'||substr(sqlerrm,1,30));
1214 END; -- End of Begin for Additional Time monthly Wage
1215
1216 END IF; --- End of IF condition for hourly pay variable
1217
1218 --l_report_start_date := ADD_MONTHS(l_report_start_date,1);
1219 l_report_end_date := ADD_MONTHS(l_report_end_date,-1);
1220 count_months := count_months + 1;
1221
1222 END LOOP;
1223
1224 END IF;
1225
1226 IF g_debug THEN
1227 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
1228 END IF;
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 -- Return cursor that selects no rows
1232 p_sql :=
1233 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1234 END range_code;
1235
1236 /* ASSIGNMENT ACTION CODE */
1237 PROCEDURE assignment_action_code (
1238 p_payroll_action_id IN NUMBER
1239 ,p_start_person IN NUMBER
1240 ,p_end_person IN NUMBER
1241 ,p_chunk IN NUMBER
1242 )
1243 IS
1244 -- End of User pARAMETERS needed
1245 BEGIN
1246 NULL;
1247 END assignment_action_code;
1248
1249 /*fffffffffffffffffffffffffff*/
1250
1251 /* INITIALIZATION CODE */
1252 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
1253 IS
1254 l_action_info_id NUMBER;
1255 l_ovn NUMBER;
1256 l_count NUMBER := 0;
1257 l_business_group_id NUMBER;
1258 l_start_date VARCHAR2 (20);
1259 l_end_date VARCHAR2 (20);
1260 l_effective_date DATE;
1261 l_payroll_id NUMBER;
1262 l_consolidation_set NUMBER;
1263 l_prev_prepay NUMBER := 0;
1264 BEGIN
1265
1266 IF g_debug THEN
1267 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
1268 END IF;
1269
1270 IF g_debug THEN
1271 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
1272 END IF;
1273
1274 EXCEPTION
1275 WHEN OTHERS
1276 THEN
1277 g_err_num := SQLCODE;
1278
1279 IF g_debug
1280 THEN
1281 hr_utility.set_location ( 'ORA_ERR: '
1282 || g_err_num
1283 || 'In INITIALIZATION_CODE'
1284 ,180
1285 );
1286 END IF;
1287
1288
1289 END initialization_code;
1290
1291
1292 /* ARCHIVE CODE */
1293 PROCEDURE archive_code (
1294 p_assignment_action_id IN NUMBER
1295 ,p_effective_date IN DATE
1296 )
1297 IS
1298 -- End of place for Cursor which fetches the values to be archived
1299 BEGIN
1300 NULL;
1301 END archive_code;
1302
1303 PROCEDURE DEINITIALIZATION_CODE
1304 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
1305
1306 BEGIN
1307 IF g_debug THEN
1308 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
1309 END IF;
1310
1311 IF g_debug THEN
1312 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
1313 END IF;
1314
1315 EXCEPTION
1316 WHEN others THEN
1317 IF g_debug THEN
1318 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
1319 END if;
1320 RAISE;
1321 END;
1322
1323 -- Bug# 9222739 fix starts
1324 FUNCTION GET_DEFINED_BALANCE_VALUE
1325 (p_assignment_id IN NUMBER
1326 ,p_balance_name IN VARCHAR2
1327 ,p_balance_dim IN VARCHAR2
1328 ,p_virtual_date IN DATE) RETURN NUMBER IS
1329
1330 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
1331 l_value NUMBER;
1332
1333
1334 CURSOR get_dbal_id(p_balance_name VARCHAR2 , p_balance_dim VARCHAR2) IS
1335 SELECT pdb.defined_balance_id
1336 FROM pay_defined_balances pdb
1337 ,pay_balance_types pbt
1338 ,pay_balance_dimensions pbd
1339 WHERE pbt.legislation_code='SE'
1340 AND pbt.balance_name = p_balance_name
1341 AND pbd.legislation_code = 'SE'
1342 AND pbd.database_item_suffix = p_balance_dim
1343 AND pdb.balance_type_id = pbt.balance_type_id
1344 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
1345
1346 BEGIN
1347
1348 OPEN get_dbal_id(p_balance_name, p_balance_dim);
1349 FETCH get_dbal_id INTO l_context1;
1350 CLOSE get_dbal_id;
1351
1352 l_value := nvl(pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date), 0);
1353
1354 RETURN l_value;
1355
1356 END GET_DEFINED_BALANCE_VALUE ;
1357
1358 -- Bug# 9222739 fix ends
1359
1360 END PAY_SE_ARCHIVE_CWCA;