[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_WORK_TIME_CERTIFICATE
Source
1 PACKAGE BODY pay_se_work_time_certificate AS
2 /* $Header: pysewtcr.pkb 120.0.12010000.2 2008/08/06 08:18:54 ubhat ship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER
7 );
8
9 TYPE lock_table IS TABLE OF lock_rec
10 INDEX BY BINARY_INTEGER;
11
12 g_lock_table lock_table;
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2 (240) := 'PAY_SE_WORK_TIME_CERTIFICATE.';
17 g_payroll_action_id NUMBER;
18 g_arc_payroll_action_id NUMBER;
19
20 -- TYPE Month_value IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
21 TYPE month_value IS TABLE OF NUMBER
22 INDEX BY BINARY_INTEGER;
23
24 TYPE absval IS RECORD (
25 each_month_days month_value
26 ,each_month_hours month_value
27 ,YEAR VARCHAR2 (240)
28 ,tot_addl_time_hours month_value
29 ,tot_overtime_hours month_value
30 ,tot_absence_hours month_value
31 );
32
33 TYPE val IS TABLE OF absval
34 INDEX BY BINARY_INTEGER;
35
36 value_month_year val;
37 -- Globals to pick up all th parameter
38 g_business_group_id NUMBER;
39 g_effective_date DATE;
40 g_person_id NUMBER;
41 g_assignment_id NUMBER;
42 g_still_employed VARCHAR2 (10);
43 g_income_salary_year VARCHAR2 (10);
44 --End of Globals to pick up all the parameter
45 g_format_mask VARCHAR2 (50);
46 g_err_num NUMBER;
47 g_errm VARCHAR2 (150);
48
49 /* GET PARAMETER */
50 FUNCTION get_parameter (
51 p_parameter_string IN VARCHAR2
52 ,p_token IN VARCHAR2
53 ,p_segment_number IN NUMBER DEFAULT NULL
54 )
55 RETURN VARCHAR2
56 IS
57 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
58 l_start_pos NUMBER;
59 l_delimiter VARCHAR2 (1) := ' ';
60 l_proc VARCHAR2 (240) := g_package || ' get parameter ';
61 BEGIN
62 --
63 IF g_debug
64 THEN
65 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
66 END IF;
67
68 l_start_pos :=
69 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
70
71 --
72 IF l_start_pos = 0
73 THEN
74 l_delimiter := '|';
75 l_start_pos :=
76 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
77 END IF;
78
79 IF l_start_pos <> 0
80 THEN
81 l_start_pos := l_start_pos + LENGTH (p_token || '=');
82 l_parameter :=
83 SUBSTR (p_parameter_string
84 ,l_start_pos
85 , INSTR (p_parameter_string || ' '
86 ,l_delimiter
87 ,l_start_pos
88 )
89 - (l_start_pos)
90 );
91
92 IF p_segment_number IS NOT NULL
93 THEN
94 l_parameter := ':' || l_parameter || ':';
95 l_parameter :=
96 SUBSTR (l_parameter
97 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
98 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
99 - 1
100 - INSTR (l_parameter, ':', 1, p_segment_number)
101 );
102 END IF;
103 END IF;
104
105 --
106 IF g_debug
107 THEN
108 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
109 END IF;
110
111 RETURN l_parameter;
112 END;
113
114 /* GET ALL PARAMETERS */
115 PROCEDURE get_all_parameters (
116 p_payroll_action_id IN NUMBER -- In parameter
117 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
118 ,p_effective_date OUT NOCOPY DATE -- Core parameter
119 ,p_person_id OUT NOCOPY NUMBER -- User parameter
120 ,p_assignment_id OUT NOCOPY VARCHAR2 -- User parameter
121 ,p_still_employed OUT NOCOPY VARCHAR2 -- User parameter
122 ,p_income_salary_year OUT NOCOPY VARCHAR2 -- User parameter
123 )
124 IS
125 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
126 IS
127 SELECT (pay_se_work_time_certificate.get_parameter
128 (legislative_parameters
129 ,'PERSON_ID'
130 )
131 ) person_id
132 , (pay_se_work_time_certificate.get_parameter
133 (legislative_parameters
134 ,'ASSIGNMENT_ID'
135 )
136 ) assignment_id
137 , (pay_se_work_time_certificate.get_parameter
138 (legislative_parameters
139 ,'STILL_EMPLOYED'
140 )
141 ) still_employed
142 , (pay_se_work_time_certificate.get_parameter
143 (legislative_parameters
144 ,'SALARY_YEAR'
145 )
146 ) income_salary_year
147 ,effective_date effective_date
148 ,business_group_id bg_id
149 FROM pay_payroll_actions
150 WHERE payroll_action_id = p_payroll_action_id;
151
152 lr_parameter_info csr_parameter_info%ROWTYPE;
153 l_proc VARCHAR2 (240)
154 := g_package || ' GET_ALL_PARAMETERS ';
155 BEGIN
156 --logger ('Entering ', l_proc);
157 --logger ('p_payroll_action_id ', p_payroll_action_id);
158
159 OPEN csr_parameter_info (p_payroll_action_id);
160
161 --FETCH csr_parameter_info into lr_parameter_info;
162 FETCH csr_parameter_info
163 INTO lr_parameter_info;
164
165 CLOSE csr_parameter_info;
166
167 fnd_file.put_line (fnd_file.LOG
168 , 'lr_parameter_info.STILL_EMPLOYED '
169 || lr_parameter_info.still_employed
170 );
171 --logger ('Entering ', l_proc);
172 p_person_id := lr_parameter_info.person_id;
173 --logger ('lr_parameter_info.PERSON_ID ', lr_parameter_info.person_id);
174 p_assignment_id := lr_parameter_info.assignment_id;
175 --logger ('lr_parameter_info.ASSIGNMENT_ID ' ,lr_parameter_info.assignment_id );
176 p_still_employed := lr_parameter_info.still_employed;
177 --logger ('lr_parameter_info.still_employed ' ,lr_parameter_info.still_employed );
178 p_income_salary_year := lr_parameter_info.income_salary_year;
179 --logger ('lr_parameter_info.income_salary_year ' ,lr_parameter_info.income_salary_year );
180 p_effective_date := lr_parameter_info.effective_date;
181 --logger ('lr_parameter_info.effective_date ' ,lr_parameter_info.effective_date );
182 p_business_group_id := lr_parameter_info.bg_id;
183 --logger ('lr_parameter_info.bg_id ', lr_parameter_info.bg_id);
184 --logger ('LEAVING ', l_proc);
185
186
187
188 IF g_debug
189 THEN
190 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
191 ,30);
192 END IF;
193 END get_all_parameters;
194
195 -- *****************************************************************************
196 /* RANGE CODE */
197 -- *****************************************************************************
198 PROCEDURE range_code (
199 p_payroll_action_id IN NUMBER
200 ,p_sql OUT NOCOPY VARCHAR2
201 )
202 IS
203 l_action_info_id NUMBER;
204 l_ovn NUMBER;
205 l_business_group_id NUMBER;
206 l_start_date VARCHAR2 (30);
207 l_end_date VARCHAR2 (30);
208 l_assignment_id NUMBER;
209 -- *****************************************************************************
210 -- Variable Required
211 l_set NUMBER;
212 l_report_effective_date DATE;
213 l_person_number VARCHAR2 (100);
214 l_last_name per_all_people_f.last_name%TYPE;
215 l_first_name per_all_people_f.first_name%TYPE;
216 l_hired_from DATE;
217 l_hired_to DATE;
218 l_still_employed VARCHAR2 (10);
219 l_absence_from VARCHAR2 (100);
220 l_absence_to VARCHAR2 (100);
221 l_form_of_employment VARCHAR2 (100);
222 l_intermittent_employee VARCHAR2 (100); --EOY 2008
223 l_work_tasks VARCHAR2 (240);
224 l_emp_at_temp_agency VARCHAR2 (5);
225 l_emp_temp_work VARCHAR2 (5);
226 l_ending_assignment_by VARCHAR2 (100);
227 l_reason VARCHAR2 (100);
228 l_notification_date VARCHAR2 (100);
229 l_termination_reason VARCHAR2 (100); --EOY 2008
230 l_continuous_offer VARCHAR2 (100);
231 l_permanent_date_from VARCHAR2 (100);
232 l_permanent_date_to VARCHAR2 (100); --EOY 2008
233 l_permanent_check_box VARCHAR2 (100);
234 l_time_limited_from VARCHAR2 (100);
235 l_time_limited_to VARCHAR2 (100);
236 l_time_limited_check_box VARCHAR2 (100);
237 l_other VARCHAR2 (100);
238 l_other_check_box VARCHAR2 (100);
239 l_full_time VARCHAR2 (100);
240 l_full_time_check_box VARCHAR2 (100);
241 l_part_time VARCHAR2 (100);
242 l_part_time_check_box VARCHAR2 (100);
243 l_working_percentage VARCHAR2 (100);
244 l_various_working_time VARCHAR2 (100);
245 l_offer_accepted VARCHAR2 (100);
246 l_decline_date VARCHAR2 (100);
247 l_aggrmnt_of_compn_signed VARCHAR2 (100); --EOY 2008
248 l_time_worked_from VARCHAR2 (100);
249 l_time_worked_to VARCHAR2 (100);
250 l_total_worked_hours NUMBER;
251 l_paid_sick_leave_days NUMBER;
252 l_teaching_load VARCHAR2 (100);
253 l_teaching_load_check_box VARCHAR2 (100); --EOY 2008
254 l_assign_hours_week VARCHAR2 (100);
255 l_assign_frequency VARCHAR2 (100);
256 l_assign_various_work_time VARCHAR2 (100);
257 l_assign_working_percentage NUMBER;
258 l_assign_full_time VARCHAR2 (100);
259 l_assign_part_time VARCHAR2 (100);
260 l_local_unit_id NUMBER;
261 l_salary_year VARCHAR2 (100);
262 l_assign_salary_paid_out VARCHAR2 (100);
263 l_salary_amount NUMBER(10,2);
264 --l_assign_working_percentage varchar2(100);
265 l_school_holiday_pay_amount VARCHAR2 (100);
266 l_holiday_pay_amount VARCHAR2 (100); -- EOY 2008
267 l_school_holiday_pay_box VARCHAR2 (100);
268 l_emp_with_holiday_pay VARCHAR2 (100);
269 l_no_of_paid_holiday_days VARCHAR2 (100);
270 l_paid_days_off_duty_time VARCHAR2 (100);
271 l_employed_educational_assoc VARCHAR2 (100);
272 l_holiday_duty VARCHAR2 (100);
273 l_lay_off_period_paid_days VARCHAR2 (100);
274 l_holiday_laid_off VARCHAR2 (100);
275 l_lay_off_from VARCHAR2 (100);
276 l_lay_off_to VARCHAR2 (100);
277 l_other_information VARCHAR2 (100);
278 l_legal_employer_name VARCHAR2 (100);
279 l_org_number VARCHAR2 (100);
280 l_location_id VARCHAR2 (100);
281 l_phone_number VARCHAR2 (100);
282 l_location_code VARCHAR2 (100);
283 l_address_line_1 VARCHAR2 (100);
284 l_address_line_2 VARCHAR2 (100);
285 l_address_line_3 VARCHAR2 (100);
286 l_postal_code VARCHAR2 (100);
287 l_town_or_city VARCHAR2 (100);
288 l_region_1 VARCHAR2 (100);
289 l_region_2 VARCHAR2 (100);
290 l_territory_short_name VARCHAR2 (100);
291 l_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE;
292 l_one_year_date DATE;
293 l_temp_start_date DATE;
294 l_temp_end_date DATE;
295 l_temp_date DATE;
296 l_seven_year_end_date DATE;
297 l_absence_per_month NUMBER;
298 l_vacation_absence NUMBER;
299 l_legal_employer_id NUMBER;
300 l_end_loop NUMBER;
301 l_temp_counter VARCHAR2 (20);
302 l_total_absence_days NUMBER;
303 l_total_absence_hours NUMBER;
304 l_total_working_days NUMBER;
305 l_total_working_hours NUMBER;
306 l_previousyear NUMBER;
307 l_currentyear NUMBER;
308 l_currentmonth VARCHAR2 (15);
309 l_count_year NUMBER := 0;
310 l_annual_salary NUMBER;
311 l_return NUMBER;
312 l_days_wth_public NUMBER;
313 l_hours_wth_public NUMBER;
314 l_each_absence NUMBER;
315 l_first_year NUMBER;
316 l_second_year NUMBER;
317 l_all_sick_absence_days NUMBER;
318 l_all_worked_hours NUMBER;
319 l_start_date_for_salary DATE;
320 l_end_date_for_salary DATE;
321 l_month_between_salary_year NUMBER;
322 l_get_defined_balance_id NUMBER;
323 l_get_salary_date DATE;
324 l_curr_month_start DATE;
325 l_curr_month_end DATE;
326 l_start_assign_date DATE;
327 l_count NUMBER;
328 l_month_start DATE;
329 l_month_end DATE;
330 l_salary_year_hours NUMBER;
331 l_salary_year_hours_worked NUMBER;
332 l_job_name VARCHAR2 (100);
333 l_position_name VARCHAR2 (100);
334 l_start_time_char varchar2(5) := '00:00';
335 l_end_time_char varchar2(5) := '23:59';
336 l_hourly_pay_variable VARCHAR2 (100); --EOY 2008
337 l_hourly_overtime_rate VARCHAR2 (100); --EOY 2008
338 l_hourly_addl_suppl_time VARCHAR2 (100); --EOY 2008
339 l_other_taxable_compensation VARCHAR2 (100); --EOY 2008
340 l_report_start_date DATE; --EOY 2008
341 l_dimension VARCHAR2 (100); --EOY 2008
342 l_tot_overtime_hours NUMBER; --EOY 2008
343 l_overtime_hours NUMBER; --EOY 2008
344 l_addl_time_hours NUMBER; --EOY 2008
345 l_tot_addl_time_hours NUMBER; --EOY 2008
346 l_reporting_date DATE ; --EOY 2008
347 l_absence_percentage VARCHAR(100); --EOY 2008
348 l_employment_end_date VARCHAR(100); --EOY 2008
349 l_absence_hours NUMBER; --EOY 2008
350 l_tot_absence_hours NUMBER; --EOY 2008
351
352 -- *****************************************************************************
353 -- CURSOR
354 CURSOR csr_absence_details (
355 csr_v_person_id NUMBER
356 ,csr_v_start_date DATE
357 ,csr_v_end_date DATE
358 )
359 IS
360 SELECT paa.absence_attendance_id
361 -- ,paa.date_start
362 , GREATEST (paa.date_start, csr_v_start_date) startdate
363 ,paa.time_start
364 -- ,paa.date_end
365 , LEAST (paa.date_end, csr_v_end_date) enddate
366 ,paa.time_end
367 FROM per_absence_attendances paa
368 ,per_absence_attendance_types pat
369 WHERE paa.person_id = csr_v_person_id
370 AND ( paa.date_start BETWEEN csr_v_start_date AND csr_v_end_date
371 OR paa.date_end BETWEEN csr_v_start_date AND csr_v_end_date
372 )
373 /* (
374 (paa.date_start >=CSR_V_start_date AND ( nvl(paa.date_end,CSR_V_end_date)<=CSR_V_end_date) or (nvl(paa.date_end,CSR_V_end_date)>=CSR_V_end_date) )
375 or
376 ( (paa.date_start < CSR_V_start_date ) and nvl(paa.date_end,CSR_V_end_date)<=CSR_V_end_date)
377 )
378 */
379 AND paa.absence_attendance_type_id =
380 pat.absence_attendance_type_id
381 AND pat.absence_category NOT IN ('V')
382 ORDER BY paa.date_start;
383
384 CURSOR csr_address_details (
385 csr_v_location_id hr_locations.location_id%TYPE
386 )
387 IS
388 SELECT hl.location_code
389 ,hl.description
390 ,hl.address_line_1
391 ,hl.address_line_2
392 ,hl.address_line_3
393 ,hl.postal_code
394 ,hl.town_or_city
395 ,hl.region_1
396 ,hl.region_2
397 ,ft.territory_short_name
398 FROM hr_organization_units hou
399 ,hr_locations hl
400 ,fnd_territories_vl ft
401 WHERE hl.location_id = csr_v_location_id
402 AND hl.country = ft.territory_code;
403
404 lr_address_details csr_address_details%ROWTYPE;
405
406 CURSOR csr_legal_employer_details (
407 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
408 )
409 IS
410 SELECT o.NAME
411 ,hoi3.org_information2 "ORG_NUMBER"
412 ,o.location_id
413 ,o.organization_id
414 FROM hr_all_organization_units o
415 ,hr_organization_information hoi1
416 ,hr_organization_information hoi2
417 ,hr_organization_information hoi3
418 WHERE o.business_group_id = g_business_group_id
419 AND hoi1.organization_id = o.organization_id
420 AND hoi1.org_information_context = 'CLASS'
421 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
422 AND hoi2.organization_id = hoi1.organization_id
423 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
424 AND hoi2.org_information1 = csr_v_local_unit_id
425 AND o.organization_id = hoi3.organization_id
426 AND hoi3.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
427
428 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
429
430 CURSOR csr_contact_details (
431 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
432 )
433 IS
434 SELECT hoi4.org_information3
435 FROM hr_organization_information hoi4
436 WHERE hoi4.organization_id = csr_v_legal_employer_id
437 AND hoi4.org_information_context = 'SE_ORG_CONTACT_DETAILS'
438 AND hoi4.org_information_id =
439 (SELECT MIN (org_information_id)
440 FROM hr_organization_information
441 WHERE organization_id = csr_v_legal_employer_id
442 AND org_information_context = 'SE_ORG_CONTACT_DETAILS'
443 AND org_information1 = 'PHONE');
444
445 lr_contact_details csr_contact_details%ROWTYPE;
446
447 -- To CHECK ACTIVE ASSIGNMENT OR TERMINATE
448 CURSOR csr_assign_status (
449 csr_v_person_id per_all_assignments_f.person_id%TYPE
450 ,csr_v_assignment_id per_all_assignments_f.assignment_id%TYPE
451 )
452 IS
453 SELECT past.per_system_status
454 ,p.effective_start_date
455 ,p.effective_end_date
456 FROM per_all_assignments_f p
457 ,per_assignment_status_types past
458 WHERE p.business_group_id = g_business_group_id
459 AND p.effective_start_date =
460 (SELECT MAX (p1.effective_start_date)
461 FROM per_all_assignments_f p1
462 ,per_assignment_status_types past1
463 WHERE p1.assignment_id = p.assignment_id
464 AND past1.assignment_status_type_id = p1.assignment_status_type_id
465 AND past1.per_system_status IN ('ACTIVE_ASSIGN' )
466 )
467 AND p.person_id = csr_v_person_id
468 AND p.assignment_id = csr_v_assignment_id
469 AND past.assignment_status_type_id = p.assignment_status_type_id
470 AND past.per_system_status IN ('ACTIVE_ASSIGN', 'TERM_ASSIGN');
471
472 lr_assign_status csr_assign_status%ROWTYPE;
473
474 CURSOR csr_get_assign_min_start_date (
475 csr_v_person_id per_all_assignments_f.person_id%TYPE
476 ,csr_v_assignment_id per_all_assignments_f.assignment_id%TYPE
477 )
478 IS
479 SELECT MIN (p.effective_start_date) min_date
480 FROM per_all_assignments_f p
481 WHERE p.business_group_id = g_business_group_id
482 AND p.person_id = csr_v_person_id
483 AND p.assignment_id = csr_v_assignment_id;
484
485 lr_get_assign_min_start_date csr_get_assign_min_start_date%ROWTYPE;
486
487 CURSOR csr_person_info (
488 csr_v_person_id per_all_people_f.person_id%TYPE
489 ,csr_v_effective_date per_all_people_f.effective_start_date%TYPE
490 )
491 IS
492 SELECT *
493 FROM per_all_people_f p
494 WHERE p.business_group_id = g_business_group_id
495 AND p.person_id = csr_v_person_id
496 AND csr_v_effective_date BETWEEN p.effective_start_date
497 AND p.effective_end_date;
498
499 lr_person_info csr_person_info%ROWTYPE;
500
501 CURSOR csr_assignment_info (
502 csr_v_person_id per_all_people_f.person_id%TYPE
503 ,csr_v_assignment_id per_all_assignments_f.person_id%TYPE
504 ,csr_v_effective_date per_all_assignments_f.effective_start_date%TYPE
505 )
506 IS
507 SELECT *
508 FROM per_all_assignments_f p
509 WHERE p.business_group_id = g_business_group_id
510 AND p.assignment_id = csr_v_assignment_id
511 AND p.person_id = csr_v_person_id
512 AND csr_v_effective_date BETWEEN p.effective_start_date
513 AND p.effective_end_date;
514
515 lr_assignment_info csr_assignment_info%ROWTYPE;
516
517 CURSOR csr_extra_assignment_info (
518 csr_v_assignment_id per_all_assignments_f.person_id%TYPE
519 ,csr_v_information_type per_assignment_extra_info.information_type%TYPE
520 )
521 IS
522 SELECT *
523 FROM per_assignment_extra_info
524 WHERE assignment_id = csr_v_assignment_id
525 AND information_type = csr_v_information_type;
526
527 lr_extra_assignment_info csr_extra_assignment_info%ROWTYPE;
528
529 CURSOR csr_se_wtc_time_worked_info (
530 csr_v_assignment_id per_all_assignments_f.person_id%TYPE
531 ,csr_v_year per_assignment_extra_info.aei_information1%TYPE
532 )
533 IS
534 SELECT *
535 FROM per_assignment_extra_info
536 WHERE assignment_id = csr_v_assignment_id
537 AND information_type = 'SE_WTC_TIME_WORKED_INFO'
538 AND aei_information1 = csr_v_year;
539
540 CURSOR csr_soft_coded_keyflex_info (
541 csr_v_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE
542 )
543 IS
544 SELECT *
545 FROM hr_soft_coding_keyflex
546 WHERE soft_coding_keyflex_id = csr_v_soft_coding_keyflex_id;
547
548 lr_soft_coded_keyflex_info csr_soft_coded_keyflex_info%ROWTYPE;
549 --****************************************************************************************
550
551 --**********************************************************************************
552 /* Cursor for Additional/Supplementary and Overtime Hours */
553 CURSOR csr_balance
554 (p_balance_category_name VARCHAR2
555 ,p_business_group_id NUMBER)
556 IS
557 SELECT REPLACE(UPPER(pbt.balance_name),' ' ,'_') balance_name , pbt.balance_name bname
558 FROM pay_balance_types pbt , pay_balance_categories_f pbc
559 WHERE pbc.legislation_code='SE'
560 AND pbt.business_group_id =p_business_group_id
561 AND pbt.balance_category_id = pbc.balance_category_id
562 AND pbc.category_name = p_balance_category_name ;
563
564
565 /* Cursor to retrieve Defined Balance Id */
566 Cursor csr_bg_get_defined_balance_id
567 (csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE
568 ,p_business_group_id NUMBER)
569 IS
570 SELECT ue.creator_id
571 FROM ff_user_entities ue,
572 ff_database_items di
573 WHERE di.user_name = csr_v_Balance_Name
574 AND ue.user_entity_id = di.user_entity_id
575 AND ue.legislation_code is NULL
576 AND ue.business_group_id = p_business_group_id
577 AND ue.creator_type = 'B';
578
579 rg_csr_bg_get_defined_bal_id csr_bg_get_defined_balance_id%rowtype;
580
581 --***********************************************************************************
582
583
584
585
586 -- *****************************************************************************
587 /* Proc to Add the tag value and Name */
588 FUNCTION check_nvl2 (
589 p_value IN VARCHAR2
590 ,p_not_null_value IN VARCHAR2
591 ,p_null_value IN VARCHAR2
592 )
593 RETURN VARCHAR2
594 IS
595 BEGIN
596 IF p_value IS NOT NULL
597 THEN
598 RETURN p_not_null_value;
599 ELSE
600 RETURN p_null_value;
601 END IF;
602 END check_nvl2;
603 /* End of Proc to Add the tag value and Name */
604
605 -- Archiving the data , as this will fire once
606 -- *****************************************************************************
607 -- *****************************************************************************
608 BEGIN
609 -- *****************************************************************************
610 fnd_file.put_line (fnd_file.LOG, 'In RANGE_CODE 0');
611 g_payroll_action_id := p_payroll_action_id;
612 g_business_group_id := NULL;
613 g_effective_date := NULL;
614 g_person_id := NULL;
615 g_assignment_id := NULL;
616 pay_se_work_time_certificate.get_all_parameters (p_payroll_action_id
617 ,g_business_group_id
618 ,g_effective_date
619 ,g_person_id
620 ,g_assignment_id
621 ,g_still_employed
622 ,g_income_salary_year
623 );
624 --logger ('Range Code g_person_id', g_person_id);
625 --logger ('g_assignment_id', g_assignment_id);
626 --logger ('g_effective_date', g_effective_date);
627 --logger ('g_business_group_id', g_business_group_id);
628
629 -- *****************************************************************************
630 --START OF PICKING UP DATA
631 -- TO pick up the PIN
632 OPEN csr_assign_status (g_person_id, g_assignment_id);
633
634 FETCH csr_assign_status
635 INTO lr_assign_status;
636
637 CLOSE csr_assign_status;
638
639 IF lr_assign_status.per_system_status = 'ACTIVE_ASSIGN'
640 -- AND g_still_employed = 'Y'
641 and (to_char (lr_assign_status.effective_end_date, 'DD-MM-YYYY') ='31-12-4712')
642 THEN
643 l_report_effective_date := g_effective_date;
644 ELSE
645 l_report_effective_date := lr_assign_status.effective_end_date;
646 END IF;
647
648 --logger ('lr_ASSIGN_STATUS.PER_SYSTEM_STATUS' ,lr_assign_status.per_system_status );
649 --logger ('l_report_effective_date', l_report_effective_date);
650
651 OPEN csr_person_info (g_person_id, l_report_effective_date);
652
653 FETCH csr_person_info
654 INTO lr_person_info;
655
656 CLOSE csr_person_info;
657
658 l_person_number := lr_person_info.national_identifier;
659 l_last_name := lr_person_info.last_name;
660 l_first_name := lr_person_info.first_name;
661 --logger ('l_person_number', l_person_number);
662 --logger ('l_last_name', l_last_name);
663 --logger ('l_first_name', l_first_name);
664
665 OPEN csr_assignment_info (g_person_id
666 ,g_assignment_id
667 ,l_report_effective_date
668 );
669
670 FETCH csr_assignment_info
671 INTO lr_assignment_info;
672
673 CLOSE csr_assignment_info;
674
675 l_hired_from := lr_assignment_info.effective_start_date;
676 --logger ('condition l_hired_to', to_char (lr_assignment_info.effective_end_date, 'DD-MM-YYYY'));
677 IF (to_char (lr_assignment_info.effective_end_date, 'DD-MM-YYYY') ='31-12-4712')
678 THEN
679 l_hired_to := NULL;
680 --logger ('Nulling l_hired_to', l_hired_to);
681 ELSE
682 l_hired_to := lr_assignment_info.effective_end_date;
683 --logger ('Passing l_hired_to', l_hired_to);
684 END IF;
685 /*
686 IF (TO_CHAR (lr_assignment_info.effective_end_date, 'DD-MM-YYYY') =TO_DATE('31-12-4712','DD-MM-YYYY'))
687 THEN
688 l_hired_to := NULL;
689 ELSE
690 l_hired_to := lr_assignment_info.effective_end_date;
691 END IF;
692 */
693 --l_still_employed := check_NVL2(lr_assignment_info.effective_end_date,'N','Y');
694 l_still_employed := g_still_employed;
695 l_soft_coding_keyflex_id := lr_assignment_info.soft_coding_keyflex_id;
696 --logger ('l_hired_from', l_hired_from);
697 --logger ('l_hired_to', l_hired_to);
698 --logger ('l_SOFT_CODING_KEYFLEX_ID', l_soft_coding_keyflex_id);
699 lr_extra_assignment_info := NULL;
700
701 OPEN csr_extra_assignment_info (g_assignment_id, 'SE_WTC_EMPLOYEE_INFO');
702
703 FETCH csr_extra_assignment_info
704 INTO lr_extra_assignment_info;
705
706 CLOSE csr_extra_assignment_info;
707
708 l_absence_from := lr_extra_assignment_info.aei_information1;
709 l_absence_to := lr_extra_assignment_info.aei_information2;
710 l_absence_percentage := lr_extra_assignment_info.aei_information3; -- EOY 2008
711 --logger ('l_absence_from', l_absence_from);
712 --logger ('l_absence_to', l_absence_to);
713 --logger (' l_absence_percentage', l_absence_percentage);
714 -- *****************************************************************************
715 l_job_name := hr_general.decode_job (lr_assignment_info.job_id);
716 l_position_name :=
717 hr_general.decode_position_latest_name
718 (lr_assignment_info.position_id
719 ,lr_assignment_info.effective_start_date
720 );
721 l_form_of_employment := lr_assignment_info.EMPLOYMENT_CATEGORY;
722 l_work_tasks := l_job_name || '-' || l_position_name;
723 --logger ('l_form_of_employment', l_form_of_employment);
724 --logger ('l_work_tasks', l_work_tasks);
725 --l_emp_at_temp_agency := ;
726 lr_extra_assignment_info := NULL;
727
728 OPEN csr_extra_assignment_info (g_assignment_id
729 ,'SE_WTC_ASSIGNMENT_INFO'
730 );
731
732 FETCH csr_extra_assignment_info
733 INTO lr_extra_assignment_info;
734
735 CLOSE csr_extra_assignment_info;
736
737 l_emp_at_temp_agency := lr_extra_assignment_info.aei_information1;
738 l_emp_temp_work := lr_extra_assignment_info.aei_information2;
739 l_ending_assignment_by := lr_extra_assignment_info.aei_information3;
740 --logger ('l_emp_at_temp_agency', l_emp_at_temp_agency);
741 --logger ('l_emp_temp_work', l_emp_temp_work);
742 --logger ('l_ending_assignment_by', l_ending_assignment_by);
743
744 -- *****************************************************************************
745 -- SOFT CODED FLEX
746 OPEN csr_soft_coded_keyflex_info (l_soft_coding_keyflex_id);
747
748 FETCH csr_soft_coded_keyflex_info
749 INTO lr_soft_coded_keyflex_info;
750
751 CLOSE csr_soft_coded_keyflex_info;
752
753 l_local_unit_id := lr_soft_coded_keyflex_info.segment2;
754 l_reason :=
755 hr_general.decode_lookup ('LEAV_REAS'
756 ,lr_soft_coded_keyflex_info.segment7
757 );
758 l_notification_date := lr_soft_coded_keyflex_info.segment5;
759 l_termination_reason := lr_soft_coded_keyflex_info.segment7;
760
761 --Added the conversion from number to cannonical
762 l_assign_working_percentage := fnd_number.canonical_to_number(lr_soft_coded_keyflex_info.segment9);
763 --logger ('l_local_unit_id', l_local_unit_id);
764 --logger ('l_reason', l_reason);
765 --logger ('l_notification_date', l_notification_date);
766 --logger (' l_termination_reason', l_termination_reason);
767 --logger ('l_assign_working_percentage', l_assign_working_percentage);
768 -- *****************************************************************************
769 lr_extra_assignment_info := NULL;
770
771 OPEN csr_extra_assignment_info (g_assignment_id
772 ,'SE_WTC_EMPLOYMENT_INFO'
773 );
774
775 FETCH csr_extra_assignment_info
776 INTO lr_extra_assignment_info;
777
778 CLOSE csr_extra_assignment_info;
779
780 l_continuous_offer := lr_extra_assignment_info.aei_information1;
781 l_permanent_date_from := lr_extra_assignment_info.aei_information2;
782 l_permanent_date_to := lr_extra_assignment_info.aei_information12;
783 l_permanent_check_box :=
784 check_nvl2 (lr_extra_assignment_info.aei_information2, 'Y', 'N');
785 l_time_limited_from := lr_extra_assignment_info.aei_information3;
786 l_time_limited_to := lr_extra_assignment_info.aei_information4;
787 l_time_limited_check_box :=
788 check_nvl2 (lr_extra_assignment_info.aei_information3, 'Y', 'N');
789 l_other := lr_extra_assignment_info.aei_information5;
790 l_other_check_box :=
791 check_nvl2 (lr_extra_assignment_info.aei_information5, 'Y', 'N');
792 l_full_time := lr_extra_assignment_info.aei_information6;
793 l_full_time_check_box :=
794 check_nvl2 (lr_extra_assignment_info.aei_information6, 'Y', 'N');
795 l_part_time := lr_extra_assignment_info.aei_information7;
796 l_part_time_check_box :=
797 check_nvl2 (lr_extra_assignment_info.aei_information7, 'Y', 'N');
798 l_working_percentage := lr_extra_assignment_info.aei_information8;
799 IF l_full_time IS NOT NULL and l_part_time IS NOT NULL
800 THEN
801 l_various_working_time := 'Y';
802 ELSE
803 l_various_working_time := 'N';
804 END IF;
805
806
807 l_offer_accepted := lr_extra_assignment_info.aei_information9;
808 l_decline_date := lr_extra_assignment_info.aei_information10;
809
810 l_aggrmnt_of_compn_signed := lr_extra_assignment_info.aei_information11;
811
812 --logger ('l_continuous_offer', l_continuous_offer);
813 --logger ('l_permanent_date_from', l_permanent_date_from);
814 --logger ('l_permanent_date_to', l_permanent_date_to);
815 --logger ('l_permanent_check_box', l_permanent_check_box);
816 --logger ('l_time_limited_from', l_time_limited_from);
817 --logger ('l_time_limited_to', l_time_limited_to);
818 --logger ('l_time_limited_check_box', l_time_limited_check_box);
819 --logger ('l_other', l_other);
820 --logger ('l_other_check_box', l_other_check_box);
821 --logger ('l_full_time', l_full_time);
822 --logger ('l_full_time_check_box', l_full_time_check_box);
823 --logger ('l_part_time', l_part_time);
824 --logger ('l_part_time_check_box', l_part_time_check_box);
825 --logger ('l_working_percentage', l_working_percentage);
826 --logger ('l_various_working_time', l_various_working_time);
827 --logger ('l_offer_accepted', l_offer_accepted);
828 --logger ('l_decline_date', l_decline_date);
829 -- *****************************************************************************
830 -- TIME WORKED SECTION
831 lr_extra_assignment_info := NULL;
832
833 OPEN csr_extra_assignment_info (g_assignment_id
834 ,'SE_WTC_TIME_WORKED_HEADER'
835 );
836
837 FETCH csr_extra_assignment_info
838 INTO lr_extra_assignment_info;
839
840 CLOSE csr_extra_assignment_info;
841
842 l_time_worked_from := lr_extra_assignment_info.aei_information1;
843 l_time_worked_to := lr_extra_assignment_info.aei_information2;
844 l_total_worked_hours := lr_extra_assignment_info.aei_information3;
845 l_paid_sick_leave_days := lr_extra_assignment_info.aei_information4;
846 l_teaching_load := lr_extra_assignment_info.aei_information5;
847 l_teaching_load_check_box :=
848 check_nvl2 (lr_extra_assignment_info.aei_information5, 'Y', 'N'); -- EOY 2008
849 --logger ('l_time_worked_from', l_time_worked_from);
850 --logger ('l_time_worked_to', l_time_worked_to);
851 --logger ('l_total_worked_hours', l_total_worked_hours);
852 --logger ('l_paid_sick_leave_days', l_paid_sick_leave_days);
853 --logger ('l_teaching_load', l_teaching_load);
854
855 -- Legal Employer Details
856 OPEN csr_legal_employer_details (l_local_unit_id);
857
858 FETCH csr_legal_employer_details
859 INTO lr_legal_employer_details;
860
861 CLOSE csr_legal_employer_details;
862
863 l_legal_employer_name := lr_legal_employer_details.NAME;
864 l_org_number := lr_legal_employer_details.org_number;
865 l_location_id := lr_legal_employer_details.location_id;
866 l_legal_employer_id := lr_legal_employer_details.organization_id;
867 --logger ('l_legal_employer_name', l_legal_employer_name);
868 --logger ('l_org_number', l_org_number);
869 --logger ('l_location_id', l_location_id);
870 l_one_year_date := ADD_MONTHS (l_report_effective_date, -12);
871
872 fnd_file.put_line (fnd_file.LOG, 'l_legal_employer_id'||l_legal_employer_id);
873
874 /*
875 three conditions
876 one if the date is greater than the reporting date, pass reporting date
877 two if the date is lesser than one year reporting date, pass one year date
878 third if the date is in between the one year and reporting date , pass
879 last_day of the month to get balance.
880 */
881 -- *****************************************************************************
882 -- *****************************************************************************
883 l_second_year := TO_CHAR (l_report_effective_date, 'YYYY');
884 l_first_year := l_second_year - 1;
885 l_previousyear := TO_CHAR (l_report_effective_date, 'YYYY');
886 --logger ('l_second_year ', l_second_year);
887 --logger ('l_first_year ', l_first_year);
888 l_temp_start_date := l_report_effective_date;
889 l_temp_end_date := ADD_MONTHS (l_report_effective_date, -12);
890 l_seven_year_end_date := ADD_MONTHS (l_report_effective_date, -84);
891 --logger ('l_temp_start_date == ', l_temp_start_date);
892 --logger ('l_temp_end_date == ', l_temp_end_date);
893 --logger ('l_seven_year_end_date == ', l_seven_year_end_date);
894 l_temp_date := l_report_effective_date;
895
896 -- *****************************************************************************
897 -- SET if in EIT the values are not given
898 -- *****************************************************************************
899
900 IF l_time_worked_from IS NULL
901 THEN
902 l_time_worked_from := fnd_date.date_to_canonical(GREATEST(l_temp_end_date,l_hired_from));
903 END IF;
904
905 IF l_time_worked_to IS NULL
906 THEN
907 l_time_worked_to := fnd_date.date_to_canonical(l_temp_start_date);
908 END IF;
909
910 -- *****************************************************************************
911 --logger ('g_assignment_id ', g_assignment_id);
912 --logger ('l_legal_employer_id ', l_legal_employer_id);
913 --logger ('l_local_unit_id ', l_local_unit_id);
914
915 OPEN csr_get_assign_min_start_date (g_person_id, g_assignment_id);
916
917 FETCH csr_get_assign_min_start_date
918 INTO lr_get_assign_min_start_date;
919
920 CLOSE csr_get_assign_min_start_date;
921
922 --logger ('lr_get_assign_min_start_date.MIN_DATE' ,lr_get_assign_min_start_date.min_date );
923 -- *****************************************************************************
924 -- RESET THESE TO ZERO
925 -- *****************************************************************************
926 l_end_loop := 0;
927 l_all_sick_absence_days := 0;
928 l_all_worked_hours := 0;
929 l_start_assign_date := lr_get_assign_min_start_date.min_date;
930 l_temp_date := TRUNC (l_temp_start_date, 'MM');
931 l_month_start := l_temp_start_date;
932 l_month_end := l_temp_start_date;
933 l_count := 0;
934
935 -- *****************************************************************************
936 WHILE ((l_end_loop <> 1) AND (l_month_start <> l_temp_end_date))
937 LOOP
938 --logger ('l_temp_date ', l_temp_date);
939 --logger ('l_count ', l_count);
940 l_month_end := LEAST (LAST_DAY (l_temp_date), l_temp_start_date);
941 l_month_start :=
942 GREATEST (TRUNC (l_temp_date, 'MM')
943 ,TRUNC (l_temp_end_date, 'MM')
944 ,l_start_assign_date
945 ,l_seven_year_end_date
946 );
947 --logger ('l_month_start ', l_month_start);
948 --logger ('l_month_end ', l_month_end);
949
950 IF ( (TRUNC (l_seven_year_end_date, 'MM') =
951 TRUNC (l_temp_date, 'MM')
952 )
953 )
954 THEN
955 l_end_loop := 1;
956 --logger ('Endign Loop for ', l_end_loop);
957 --logger ('TRUNC (l_temp_date, MM) ', TRUNC (l_temp_date, 'MM'));
958 --logger ('TRUNC (l_seven_year_end_date, MM)', TRUNC (l_seven_year_end_date, 'MM'));
959 END IF;
960
961 /*IF
962 THEN
963 END IF;
964 */
965 -- *****************************************************************************
966 /*
967 IF l_temp_date >= l_temp_start_date
968 THEN
969 l_temp_date := l_temp_start_date;
970 logger('l_temp_date 1 ',l_temp_date);
971 --l_temp_date := add_months(l_temp_date,-1);
972
973 ELSIF ( (l_temp_date < l_temp_start_date) and
974 (l_temp_date > l_temp_end_date) and
975 (l_temp_date <> l_seven_year_end_date) and
976 (l_temp_date <> last_day(lr_get_assign_min_start_date.MIN_DATE))
977 )
978 THEN
979 l_temp_date := last_day(l_temp_date);
980 logger('l_temp_date 2 ',l_temp_date);
981 --l_temp_date := add_months(l_temp_date,-1);
982 ELSIF ((l_temp_date = l_seven_year_end_date) or
983 (l_temp_date <= l_temp_end_date ) or
984 (l_temp_date = last_day(lr_get_assign_min_start_date.MIN_DATE))
985 )
986 THEN
987 l_temp_date := last_day(l_temp_end_date);
988 logger('l_temp_date 3 ',l_temp_date);
989 l_end_loop :=1;
990 END IF;
991 */
992
993 -- *****************************************************************************
994 IF l_end_loop <> 1
995 THEN
996 --logger('l_temp_end_date == ',l_temp_end_date);
997 --logger('l_end ************ ',last_day(l_temp_date));
998 --logger('l_start ************ ',trunc(l_temp_date,'MM'));
999 -- *****************************************************************************
1000 -- FIND FOR THE WHOLE MONTH in DAYS
1001 -- *****************************************************************************
1002 l_return :=
1003 hr_loc_work_schedule.calc_sch_based_dur (g_assignment_id
1004 ,'D'
1005 ,'Y'
1006 ,l_month_start
1007 ,l_month_end
1008 ,NULL
1009 ,NULL
1010 ,l_days_wth_public
1011 );
1012 -- logger('l_DAYS_WTH_PUBLIC $$$$$$$$$$$$$$$$$$$$$$ ',l_DAYS_WTH_PUBLIC);
1013 l_total_absence_days := 0;
1014 l_each_absence := 0;
1015
1016 -- *****************************************************************************
1017 -- FIND FOR EACH ABSENCE FOR THIS MONTH IN DAYS
1018 -- *****************************************************************************
1019 FOR lr_abs IN csr_absence_details (g_person_id
1020 ,l_month_start
1021 ,l_month_end
1022 )
1023 LOOP
1024 --logger ('lr_abs.STARTDATE ', lr_abs.startdate);
1025 --logger ('lr_abs.ENDDATE ', lr_abs.enddate);
1026 l_return :=
1027 hr_loc_work_schedule.calc_sch_based_dur (g_assignment_id
1028 ,'D'
1029 ,'Y'
1030 ,lr_abs.startdate
1031 ,lr_abs.enddate
1032 ,replace(nvl(lr_abs.time_start,l_start_time_char),':','.')
1033 ,replace(nvl(lr_abs.time_end,l_end_time_char),':','.')
1034 ,l_each_absence
1035 );
1036 --logger ('l_each_absence_days @@@@@@@@@@@@@@@@@@@@@@ ' ,l_each_absence );
1037 l_total_absence_days := l_each_absence + l_total_absence_days;
1038 END LOOP;
1039
1040 -- logger('FOR MONTH * '||to_char(l_temp_date,'MON')||' l_total_absence_days ',l_total_absence_days);
1041 -- logger('FOR MONTH * '||to_char(l_temp_date,'MON')||' l_total_working ',l_DAYS_WTH_PUBLIC-l_total_absence_days);
1042
1043 -- *****************************************************************************
1044 -- DAYS OVER
1045 -- FIND FOR WHOLE MONTH IN HOURS
1046 -- *****************************************************************************
1047 l_return :=
1048 hr_loc_work_schedule.calc_sch_based_dur (g_assignment_id
1049 ,'H'
1050 ,'Y'
1051 ,l_month_start
1052 ,l_month_end
1053 ,NULL
1054 ,NULL
1055 ,l_hours_wth_public
1056 );
1057 -- logger('l_HOURS_WTH_PUBLIC $$$$$$$$$$$$$$$$$$$$$$ ',l_HOURS_WTH_PUBLIC);
1058 l_total_absence_hours := 0;
1059 l_each_absence := 0;
1060
1061 -- *****************************************************************************
1062 -- FIND FOR THIS MONTH FOR ALL ABSENCES IN HOURS
1063 -- *****************************************************************************
1064 FOR lr_abs IN csr_absence_details (g_person_id
1065 ,l_month_start
1066 ,l_month_end
1067 )
1068 LOOP
1069 --logger ('lr_abs.STARTDATE ', lr_abs.startdate);
1070 --logger ('lr_abs.ENDDATE ', lr_abs.enddate);
1071 l_return :=
1072 hr_loc_work_schedule.calc_sch_based_dur (g_assignment_id
1073 ,'H'
1074 ,'Y'
1075 ,lr_abs.startdate
1076 ,lr_abs.enddate
1077 ,replace(nvl(lr_abs.time_start,l_start_time_char),':','.')
1078 ,replace(nvl(lr_abs.time_end,l_end_time_char),':','.')
1079 ,l_each_absence
1080 );
1081 --logger ('l_each_absence_hours @@@@@@@@@@@@@@@@@@@@@@ ' ,l_each_absence );
1082 l_total_absence_hours := l_each_absence + l_total_absence_hours;
1083 END LOOP;
1084
1085 -- logger('FOR MONTH * '||to_char(l_temp_date,'MON')||' l_total_absence_hours ',l_total_absence_hours);
1086 -- logger('FOR MONTH * '||to_char(l_temp_date,'MON')||' l_total_working ',l_HOURS_WTH_PUBLIC-l_total_absence_hours);
1087
1088 -- *****************************************************************************
1089 -- *****************************************************************************
1090 -- RESET COUNTER FOR THE YEAR
1091 -- *****************************************************************************
1092 l_currentyear := TO_CHAR (l_month_start, 'YYYY');
1093
1094 IF l_currentyear <> l_previousyear
1095 THEN
1096 l_count_year := l_count_year + 1;
1097 l_previousyear := l_currentyear;
1098 END IF;
1099
1100 --logger ('l_count_year ******l_count_year****** ' ,l_count_year );
1101 -- *****************************************************************************
1102 -- DISPLAY ALL VALUES and STORE IN RECORD
1103 -- *****************************************************************************
1104 l_currentmonth := TO_CHAR (l_temp_date, 'MM');
1105 --logger ('l_DAYS_WTH_PUBLIC $$$$$$$$$$$$$$$$$$$$$$ ' ,l_days_wth_public );
1106 --logger ( 'FOR MONTH * ' || l_currentmonth || ' l_total_absence_days ' ,l_total_absence_days );
1107 --logger ( 'FOR MONTH * ' || l_currentmonth || ' l_total_working_days ' , l_days_wth_public - l_total_absence_days );
1108 l_total_working_days := l_days_wth_public - l_total_absence_days;
1109 --logger ('l_HOURS_WTH_PUBLIC $$$$$$$$$$$$$$$$$$$$$$ ' ,l_hours_wth_public );
1110 --logger ( 'FOR MONTH * ' || l_currentmonth || ' l_total_absence_hours ' ,l_total_absence_hours );
1111 --logger ( 'FOR MONTH * ' || l_currentmonth || ' l_total_working_hours ' , l_hours_wth_public - l_total_absence_hours );
1112 l_total_working_hours :=
1113 l_hours_wth_public - l_total_absence_hours;
1114 value_month_year (l_count_year).YEAR := l_currentyear;
1115 value_month_year (l_count_year).each_month_days (l_currentmonth) :=
1116 l_total_working_days;
1117 value_month_year (l_count_year).each_month_hours (l_currentmonth) :=
1118 l_total_working_hours;
1119
1120
1121 l_dimension:='_ASG_LE_MONTH'; --EOY 2008
1122 ------------------------------------------------------
1123
1124 pay_balance_pkg.set_context ('TAX_UNIT_ID', l_legal_employer_id);
1125
1126 fnd_file.put_line (fnd_file.LOG, 'set Tax unit');
1127
1128 pay_balance_pkg.set_context ('LOCAL_UNIT_ID', l_local_unit_id);
1129
1130
1131 l_report_start_date := TO_DATE('01/'||l_currentmonth||'/'||l_currentyear,'DD/MM/YYYY');
1132
1133 SELECT last_day(l_report_start_date)
1134 INTO l_reporting_date
1135 FROM DUAL;
1136
1137 -- fnd_file.put_line (fnd_file.LOG, 'l_reporting_date' ||l_reporting_date);
1138
1139 l_overtime_hours :=0;
1140 l_tot_overtime_hours :=0;
1141
1142 BEGIN
1143 FOR balance_rec IN csr_balance('Overtime - Hours' , g_business_group_id)
1144 LOOP
1145 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1146 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1147 CLOSE csr_bg_Get_Defined_Balance_Id;
1148 IF csr_balance%FOUND THEN
1149 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 ) ;
1150 l_tot_overtime_hours := l_tot_overtime_hours + nvl(l_overtime_hours,0);
1151 END IF;
1152 END LOOP ;
1153
1154 value_month_year(l_count_year).tot_overtime_hours(l_currentmonth) := l_tot_overtime_hours;
1155
1156 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_overtime_hours - First' ||l_tot_overtime_hours);
1157
1158 EXCEPTION
1159 WHEN others THEN
1160 fnd_file.put_line (fnd_file.LOG, 'Error for overtime First'||substr(sqlerrm,1,30));
1161 null;
1162 END;
1163
1164
1165
1166
1167
1168 BEGIN
1169 l_addl_time_hours :=0;
1170 l_tot_addl_time_hours:=0;
1171
1172 FOR balance_rec IN csr_balance('Additional Time - Hours' , g_business_group_id)
1173 LOOP
1174
1175 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1176 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1177 CLOSE csr_bg_Get_Defined_Balance_Id;
1178
1179 IF csr_balance%FOUND THEN
1180
1181 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 ) ;
1182 l_tot_addl_time_hours := l_tot_addl_time_hours + nvl(l_addl_time_hours,0);
1183 END IF;
1184 END LOOP ;
1185
1186 value_month_year(l_count_year).tot_addl_time_hours(l_currentmonth) := l_tot_addl_time_hours ;
1187
1188 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_addl_time_hours - First' ||l_tot_addl_time_hours);
1189
1190 EXCEPTION
1191 WHEN others THEN
1192 fnd_file.put_line (fnd_file.LOG, 'Error'||substr(sqlerrm,1,30));
1193 null;
1194 END;
1195
1196
1197 l_absence_hours :=0;
1198 l_tot_absence_hours :=0;
1199 value_month_year(l_count_year).tot_absence_hours(l_currentmonth) := l_tot_absence_hours;
1200 BEGIN
1201 FOR balance_rec IN csr_balance('UnPaid Absence - Hours' , g_business_group_id)
1202 LOOP
1203 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1204 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1205 CLOSE csr_bg_Get_Defined_Balance_Id;
1206 IF csr_balance%FOUND THEN
1207 l_absence_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 ) ;
1208 l_tot_absence_hours := l_tot_absence_hours + nvl(l_absence_hours,0);
1209 END IF;
1210 END LOOP ;
1211
1212 value_month_year(l_count_year).tot_absence_hours(l_currentmonth) := l_tot_absence_hours;
1213
1214 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_absence_hours - First' ||l_tot_absence_hours);
1215
1216 EXCEPTION
1217 WHEN others THEN
1218 fnd_file.put_line (fnd_file.LOG, 'Error for absence'||substr(sqlerrm,1,30));
1219 null;
1220 END;
1221
1222
1223 --------------------------------------------------
1224 l_all_sick_absence_days :=
1225 l_all_sick_absence_days + l_total_absence_days;
1226 l_all_worked_hours := l_all_worked_hours + l_total_working_hours;
1227
1228 -- *****************************************************************************
1229 -- MOVING THE START DATE BACK ONE MONTH
1230 -- AS HOURS IS LESS THAN 70 HOURS
1231 -- *****************************************************************************
1232 IF l_total_working_hours < 70
1233 THEN
1234 l_temp_end_date := ADD_MONTHS (l_temp_end_date, -1);
1235 --logger ('LESS THAN 70 HOURS FOR THE MONTH ' || l_currentmonth ,l_temp_end_date );
1236 ELSE
1237 l_count := l_count + 1;
1238 END IF;
1239
1240 -- *****************************************************************************
1241
1242 -- *****************************************************************************
1243
1244 IF ( (TRUNC (l_start_assign_date, 'MM') =
1245 TRUNC (l_temp_date, 'MM')
1246 )
1247 OR (TRUNC (l_seven_year_end_date, 'MM') =
1248 TRUNC (l_temp_date, 'MM')
1249 )
1250 OR
1251 ( l_count >= 12
1252 )
1253 )
1254 THEN
1255 l_end_loop := 1;
1256 --logger ('Endign Loop for ', l_end_loop);
1257 --logger ('TRUNC (l_temp_date, MM) ', TRUNC (l_temp_date, 'MM'));
1258 --logger ('TRUNC (l_start_assign_date, MM) ', TRUNC (l_start_assign_date, 'MM'));
1259 --logger ('TRUNC (l_seven_year_end_date, MM)', TRUNC (l_seven_year_end_date, 'MM'));
1260
1261 END IF;
1262
1263 -- MOVING A MONTH BACK
1264 l_temp_date := ADD_MONTHS (l_temp_date, -1);
1265 -- l_count := l_count + 1;
1266 -- *********END*****************************************************************
1267 END IF;
1268 END LOOP;
1269
1270 -- *****************************************************************************
1271 -- SET THE VALUE CALCULATED TO THESE VARIABLES
1272 -- AS IN THE EIT USER NOT ENTERED THE VALUES MANUALLY
1273 --
1274 -- *****************************************************************************
1275 IF l_total_worked_hours IS NULL
1276 THEN
1277 l_total_worked_hours := l_all_worked_hours;
1278 END IF;
1279 /*-- use balance to get the paid sick leaves
1280 IF l_paid_sick_leave_days IS NULL
1281 THEN
1282 l_paid_sick_leave_days := l_all_sick_absence_days;
1283 END IF;
1284 */
1285 -- *****************************************************************************
1286 -- *****************************************************************************
1287 -- *****************************************************************************
1288 FOR i IN value_month_year.FIRST .. value_month_year.LAST
1289 LOOP
1290 --logger ('value_month_year ', value_month_year (i).YEAR);
1291 -- logger('value_month_year ' ,value_month_year(i).year ) ;
1292 --logger (' FIRST MONTH ' ,value_month_year (i).each_month_days.FIRST );
1293 --logger (' LAST MONTH ' ,value_month_year (i).each_month_days.LAST);
1294 --logger ('MONTH' || ' DAYS', 'HOURS');
1295 l_temp_counter := value_month_year (i).each_month_days.FIRST;
1296
1297 FOR i_i IN 01 .. 12
1298 LOOP
1299 IF value_month_year (i).each_month_days.EXISTS (i_i) = FALSE
1300 THEN
1301 value_month_year (i).each_month_days (i_i) := NULL;
1302 value_month_year (i).each_month_hours (i_i) := NULL;
1303 value_month_year (i).tot_addl_time_hours(i_i) := NULL;
1304 value_month_year (i).tot_overtime_hours(i_i) := NULL;
1305 value_month_year (i).tot_absence_hours(i_i) := NULL;
1306 END IF;
1307 END LOOP;
1308
1309 WHILE l_temp_counter IS NOT NULL
1310 LOOP
1311 --logger ( l_temp_counter || ' ' || value_month_year (i).each_month_days (l_temp_counter) ,value_month_year (i).each_month_hours (l_temp_counter) );
1312 --logger( 'DAYS MONTH '||l_TEMP_COUNTER ,to_char(value_month_year(i).EACH_MONTH_DAYS(l_TEMP_COUNTER) )) ;
1313 --logger( 'HOURS MONTH '||l_TEMP_COUNTER ,to_char(value_month_year(i).EACH_MONTH_HOURS(l_TEMP_COUNTER) )) ;
1314 l_temp_counter :=
1315 value_month_year (i).each_month_days.NEXT (l_temp_counter);
1316 END LOOP;
1317 /*
1318 FOR l_test IN value_month_year(i).EACH_MONTH_DAYS.first .. value_month_year(i).EACH_MONTH_DAYS.LAST
1319 LOOP
1320 logger('value_month_year MONTH ' ,value_month_year(i).EACH_MONTH_DAYS(l_test) ) ;
1321 l_test := value_month_year(i).EACH_MONTH_DAYS.next(l_test);
1322 END LOOP;
1323 */
1324 END LOOP;
1325
1326 -- *****************************************************************************
1327 -- WORKING TIME
1328 /*
1329 IF lr_assignment_info.EMPLOYMENT_CATEGORY in ('FR','FT','SE_FS')
1330 THEN
1331 l_assign_full_time := lr_assignment_info.EMPLOYMENT_CATEGORY ;
1332 ELSE
1333 l_assign_full_time := NULL;
1334 END IF;
1335
1336 IF lr_assignment_info.EMPLOYMENT_CATEGORY in ('PR','PT','SE_PS')
1337 THEN
1338 l_assign_part_time := lr_assignment_info.EMPLOYMENT_CATEGORY ;
1339 ELSE
1340 l_assign_part_time := NULL;
1341 END IF;
1342 */
1343 l_assign_hours_week := lr_assignment_info.normal_hours;
1344 l_assign_frequency := lr_assignment_info.frequency;
1345 l_assign_various_work_time :=
1346 check_nvl2 (l_assign_working_percentage, 'Y', 'N');
1347
1348 IF l_assign_working_percentage = 100 and l_assign_hours_week IS NOT NULL
1349 THEN
1350 l_assign_full_time := 'Y';
1351 l_assign_part_time := 'N';
1352 l_assign_various_work_time := 'N';
1353 ELSIF ( l_assign_working_percentage >= 0
1354 AND l_assign_working_percentage < 100
1355 ) and l_assign_hours_week IS NOT NULL
1356 THEN
1357 l_assign_full_time := 'N';
1358 l_assign_part_time := 'Y';
1359 l_assign_various_work_time := 'N';
1360 ELSIF l_assign_hours_week IS NULL
1361 THEN
1362 l_assign_full_time := 'N';
1363 l_assign_part_time := 'N';
1364 l_assign_various_work_time := 'Y';
1365 END IF;
1366
1367 IF l_assign_frequency = 'M'
1368 THEN
1369 l_assign_hours_week := ((l_assign_hours_week * 12) / 52);
1370 END IF;
1371
1372 -- At present the variable of intermittent employee is not used
1373 -- The check in the template is on the form of employment.
1374 -- Any employee who is not a full-time or part-time and dont have hours/week
1375 -- is an intermittent employee.
1376
1377 IF l_assign_various_work_time = 'Y'
1378 THEN
1379 l_intermittent_employee := 'Y';
1380 l_form_of_employment := 'INTMT';
1381 ELSE
1382 l_intermittent_employee := 'N';
1383 END IF;
1384
1385 --logger ('l_assign_full_time', l_assign_full_time);
1386 --logger ('l_assign_part_time', l_assign_part_time);
1387 --logger ('l_assign_hours_week', l_assign_hours_week);
1388 --logger ('l_assign_FREQUENCY', l_assign_frequency);
1389 --logger ('l_assign_various_work_time', l_assign_various_work_time);
1390
1391 -- Code to populate the End Date of Probationary and Time- Limited Employment EOY 2008
1392
1393
1394
1395 l_employment_end_date := lr_assign_status.effective_end_date;
1396
1397
1398 --logger ('l_employment_end_date', l_employment_end_date);
1399
1400
1401
1402
1403 -- *****************************************************************************
1404 -- Income salry section
1405 l_salary_year := g_income_salary_year;
1406 l_assign_salary_paid_out := lr_assignment_info.hourly_salaried_code;
1407 -- l_salary_amount := 1000;
1408 -- *****************************************************************************
1409 -- *****************************************************************************
1410 --logger ('lr_get_assign_min_start_date.MIN_DATE' ,lr_get_assign_min_start_date.min_date );
1411
1412 IF g_income_salary_year >=
1413 TO_CHAR (lr_get_assign_min_start_date.min_date, 'YYYY')
1414 THEN
1415 l_start_date_for_salary :=
1416 GREATEST (TO_DATE ('01-01-' || g_income_salary_year, 'DD-MM-YYYY')
1417 ,lr_get_assign_min_start_date.min_date
1418 );
1419 l_end_date_for_salary :=
1420 LEAST (TO_DATE ('31-12-' || g_income_salary_year, 'DD-MM-YYYY')
1421 ,l_report_effective_date
1422 );
1423 --l_start_date_for_salary := trunc(l_end_date_for_salary,'YYYY');
1424 --logger ('*********************', '*********************');
1425 --logger ('l_start_date_for_salary', l_start_date_for_salary);
1426 --logger ('l_end_date_for_salary', l_end_date_for_salary);
1427 --logger ('*********************', '*********************');
1428 --l_get_defined_balance_id := get_defined_balance_id('GROSS_PAY_ASG_LE_YTD');
1429 --LOGGER('l_get_defined_balance_id',l_get_defined_balance_id);
1430 --l_temp_date := l_start_date_for_salary;
1431 l_annual_salary :=
1432 TO_CHAR (get_defined_balance_value ('GROSS_PAY_ASG_LE_YTD'
1433 ,g_assignment_id
1434 ,l_end_date_for_salary
1435 ,l_legal_employer_id
1436 ,l_local_unit_id
1437 )
1438 ,'999999999D99'
1439 );
1440 --logger ('l_annual_salary', l_annual_salary);
1441
1442 IF l_paid_sick_leave_days IS NULL
1443 THEN
1444 l_paid_sick_leave_days := TO_CHAR (get_defined_balance_value ('TOTAL_SICK_PAY_DAYS_1_TO_14_DAYS_ASG_LE_YTD'
1445 ,g_assignment_id
1446 ,l_end_date_for_salary
1447 ,l_legal_employer_id
1448 ,l_local_unit_id
1449 )
1450 ,'999999999D99'
1451 );
1452 --logger ('In Null l_paid_sick_leave_days', l_paid_sick_leave_days);
1453 END IF;
1454
1455 /*
1456 WHILE l_temp_date <> l_end_date_for_salary
1457 LOOP
1458 LOGGER('l_temp_date',l_temp_date);
1459 l_get_salary_date := least(last_day(l_temp_date),l_end_date_for_salary);
1460 LOGGER('l_get_salary_date',l_get_salary_date);
1461 l_temp_date := LEAST(add_months(l_temp_date,1),l_end_date_for_salary);
1462 END LOOP;
1463 */
1464 l_salary_year_hours := 0;
1465
1466 -- *****************************************************************************
1467 IF l_assign_salary_paid_out = 'S' OR l_assign_salary_paid_out IS NULL
1468 THEN
1469 --logger ('*********************', '*********************');
1470 l_month_between_salary_year :=
1471 TO_CHAR (l_end_date_for_salary, 'MM')
1472 - TO_CHAR (l_start_date_for_salary, 'MM')
1473 + 1;
1474 --logger ('l_month_between_salary_year' ,l_month_between_salary_year);
1475
1476 IF l_annual_salary <> 0
1477 THEN
1478 l_salary_amount :=
1479 l_annual_salary / l_month_between_salary_year;
1480 END IF;
1481 ELSIF l_assign_salary_paid_out = 'H'
1482 THEN
1483 --logger ('*********************', '*********************');
1484 l_temp_date := TRUNC (l_start_date_for_salary, 'MM');
1485 l_curr_month_start := l_start_date_for_salary;
1486 l_curr_month_end := l_start_date_for_salary;
1487
1488 WHILE l_curr_month_end <> l_end_date_for_salary
1489 LOOP
1490 --logger ('l_temp_date', l_temp_date);
1491 l_curr_month_start :=
1492 GREATEST (TRUNC (l_temp_date), l_start_date_for_salary);
1493 l_curr_month_end :=
1494 LEAST (LAST_DAY (l_temp_date), l_end_date_for_salary);
1495 --logger ('l_curr_month_start', l_curr_month_start);
1496 --logger ('l_curr_month_end', l_curr_month_end);
1497 -- *****************************************************************************
1498 -- FIND FOR WHOLE MONTH IN HOURS
1499 -- *****************************************************************************
1500 l_return :=
1501 hr_loc_work_schedule.calc_sch_based_dur
1502 (g_assignment_id
1503 ,'H'
1504 ,'Y'
1505 ,l_curr_month_start
1506 ,l_curr_month_end
1507 ,NULL
1508 ,NULL
1509 ,l_hours_wth_public
1510 );
1511 --logger ('l_HOURS_WTH_PUBLIC $$$$$$$$$$$$$$$$$$$$$$ ' ,l_hours_wth_public );
1512 l_salary_year_hours := l_salary_year_hours + l_hours_wth_public;
1513 -- l_total_absence_hours := 0;
1514 l_each_absence := 0;
1515
1516 -- *****************************************************************************
1517 -- FIND FOR THIS MONTH FOR ALL ABSENCES IN HOURS
1518 -- *****************************************************************************
1519 FOR lr_abs IN csr_absence_details (g_person_id
1520 ,l_curr_month_start
1521 ,l_curr_month_end
1522 )
1523 LOOP
1524 --logger ('lr_abs.STARTDATE ', lr_abs.startdate);
1525 --logger ('lr_abs.ENDDATE ', lr_abs.enddate);
1526 l_return :=
1527 hr_loc_work_schedule.calc_sch_based_dur
1528 (g_assignment_id
1529 ,'H'
1530 ,'Y'
1531 ,lr_abs.startdate
1532 ,lr_abs.enddate
1533 ,replace(nvl(lr_abs.time_start,l_start_time_char),':','.')
1534 ,replace(nvl(lr_abs.time_end,l_end_time_char),':','.')
1535 ,l_each_absence
1536 );
1537 --logger ('l_each_absence_hours @@@@@@@@@@@@@@@@@@@@@@ ' ,l_each_absence );
1538 l_total_absence_hours :=
1539 l_each_absence + l_total_absence_hours;
1540 END LOOP;
1541
1542 -- *****************************************************************************
1543 -- *****************************************************************************
1544 l_temp_date := ADD_MONTHS (l_temp_date, 1);
1545 END LOOP;
1546
1547 --logger ('l_total_absence_hours *********** ' ,l_total_absence_hours );
1548 --logger ('l_salary_year_hours *********** ', l_salary_year_hours);
1549 l_salary_year_hours_worked :=
1550 l_salary_year_hours - l_total_absence_hours;
1551
1552 -- *****************************************************************************
1553 -- got teh Hours absence and Total Hours to be worked
1554 -- subtract , will get teh total worked hours
1555 -- divide the annual salary by this hours
1556 -- *****************************************************************************
1557 IF l_annual_salary <> 0 AND l_salary_year_hours_worked <> 0
1558 THEN
1559 l_salary_amount :=
1560 l_annual_salary / l_salary_year_hours_worked;
1561 END IF;
1562 -- *****************************************************************************
1563 END IF;
1564 -- *****************************************************************************
1565 END IF;
1566
1567 --logger ('l_salary_year', l_salary_year);
1568 --logger ('l_assign_salary_paid_out', l_assign_salary_paid_out);
1569 --logger ('l_salary_amount', l_salary_amount);
1570 lr_extra_assignment_info := NULL;
1571
1572 OPEN csr_extra_assignment_info (g_assignment_id, 'SE_WTC_INCOME_INFO');
1573
1574 FETCH csr_extra_assignment_info
1575 INTO lr_extra_assignment_info;
1576
1577 CLOSE csr_extra_assignment_info;
1578
1579 l_school_holiday_pay_amount := lr_extra_assignment_info.aei_information1;
1580 l_school_holiday_pay_box :=
1581 check_nvl2 (lr_extra_assignment_info.aei_information1, 'Y', 'N');
1582 l_no_of_paid_holiday_days := lr_extra_assignment_info.aei_information2;
1583 l_emp_with_holiday_pay :=
1584 check_nvl2 (lr_extra_assignment_info.aei_information2, 'Y', 'N');
1585 l_paid_days_off_duty_time := lr_extra_assignment_info.aei_information3;
1586 l_employed_educational_assoc :=
1587 check_nvl2 (lr_extra_assignment_info.aei_information3, 'Y', 'N');
1588 l_holiday_duty := lr_extra_assignment_info.aei_information4;
1589 l_lay_off_period_paid_days := lr_extra_assignment_info.aei_information5;
1590 l_holiday_laid_off :=
1591 check_nvl2 (lr_extra_assignment_info.aei_information5, 'Y', 'N');
1592 l_lay_off_from := lr_extra_assignment_info.aei_information6;
1593 l_lay_off_to := lr_extra_assignment_info.aei_information7;
1594 l_other_information := lr_extra_assignment_info.aei_information8;
1595
1596
1597 ----- The variables below are added w.r.t EOY changes 2008
1598
1599 l_hourly_pay_variable := lr_extra_assignment_info.aei_information9;
1600 l_hourly_overtime_rate := lr_extra_assignment_info.aei_information10;
1601 l_hourly_addl_suppl_time := lr_extra_assignment_info.aei_information11;
1602 l_other_taxable_compensation := lr_extra_assignment_info.aei_information12;
1603 l_holiday_pay_amount := lr_extra_assignment_info.aei_information13;
1604
1605 --logger ('l_School_Holiday_Pay_Amount', l_school_holiday_pay_amount);
1606 --logger ('l_School_Holiday_Pay_box', l_school_holiday_pay_box);
1607 --logger ('l_emp_with_holiday_pay', l_emp_with_holiday_pay);
1608 --logger ('l_no_of_paid_holiday_days', l_no_of_paid_holiday_days);
1609 --logger ('l_Paid_Days_Off_Duty_Time', l_paid_days_off_duty_time);
1610 --logger ('l_employed_educational_assoc', l_employed_educational_assoc);
1611 --logger ('l_Holiday_Duty', l_holiday_duty);
1612 --logger ('l_Lay_Off_Period_Paid_Days', l_lay_off_period_paid_days);
1613 --logger ('l_holiday_laid_off', l_holiday_laid_off);
1614 --logger ('l_Lay_Off_From', l_lay_off_from);
1615 --logger ('l_Lay_Off_To', l_lay_off_to);
1616 --logger ('l_Other_Information', l_other_information);
1617 --logger ('l_hourly_pay_variable', l_hourly_pay_variable);
1618 --logger ('l_hourly_overtime_rate', l_hourly_overtime_rate);
1619 --logger ('l_hourly_addl_suppl_time', l_hourly_addl_suppl_time);
1620 --logger ('l_other_taxable_compensation', l_other_taxable_compensation);
1621 --logger ('l_holiday_pay_amountt', l_holiday_pay_amount);
1622
1623 -- *****************************************************************************
1624 -- EMployer and Signs
1625
1626 --fnd_file.put_line (fnd_file.LOG, 'l_local_unit_id'||l_local_unit_id);
1627 --fnd_file.put_line (fnd_file.LOG, 'l_legal_employer_id'||l_legal_employer_id);
1628
1629 OPEN csr_contact_details (l_legal_employer_id);
1630
1631 FETCH csr_contact_details
1632 INTO lr_contact_details;
1633
1634 CLOSE csr_contact_details;
1635
1636
1637 l_phone_number := lr_contact_details.org_information3;
1638 --logger ('l_phone_number', l_phone_number);
1639 --fnd_file.put_line (fnd_file.LOG, 'l_phone_number'||l_phone_number);
1640
1641 OPEN csr_address_details (l_location_id);
1642
1643 FETCH csr_address_details
1644 INTO lr_address_details;
1645
1646 CLOSE csr_address_details;
1647
1648 l_location_code := lr_address_details.location_code;
1649 l_address_line_1 := lr_address_details.address_line_1;
1650 l_address_line_2 := lr_address_details.address_line_2;
1651 l_address_line_3 := lr_address_details.address_line_3;
1652 l_postal_code := lr_address_details.postal_code;
1653 l_town_or_city := lr_address_details.town_or_city;
1654 l_region_1 := lr_address_details.region_1;
1655 l_region_2 := lr_address_details.region_2;
1656 l_territory_short_name := lr_address_details.territory_short_name;
1657 --logger ('l_location_code', l_location_code);
1658 --logger ('l_address_line_1', l_address_line_1);
1659 --logger ('l_address_line_2', l_address_line_2);
1660 --logger ('l_address_line_3', l_address_line_3);
1661 --logger ('l_postal_code', l_postal_code);
1662 --logger ('l_TOWN_OR_CITY', l_town_or_city);
1663 --logger ('l_REGION_1', l_region_1);
1664 --logger ('l_REGION_2', l_region_2);
1665 --logger ('l_TERRITORY_SHORT_NAME', l_territory_short_name);
1666 -- *****************************************************************************
1667 -- *****************************************************************************
1668 -- *****************************************************************************
1669
1670 -- Insert the report Parameters
1671 pay_action_information_api.create_action_information
1672 (p_action_information_id => l_action_info_id
1673 ,p_action_context_id => p_payroll_action_id
1674 ,p_action_context_type => 'PA'
1675 ,p_object_version_number => l_ovn
1676 ,p_effective_date => g_effective_date
1677 ,p_source_id => NULL
1678 ,p_source_text => NULL
1679 ,p_action_information_category => 'EMEA REPORT DETAILS'
1680 ,p_action_information1 => 'PYSEWTCA'
1681 ,p_action_information2 => g_person_id
1682 ,p_action_information3 => g_assignment_id
1683 ,p_action_information4 => g_still_employed
1684 ,p_action_information5 => g_business_group_id
1685 ,p_action_information6 => NULL
1686 ,p_action_information7 => NULL
1687 ,p_action_information8 => NULL
1688 ,p_action_information9 => NULL
1689 ,p_action_information10 => NULL
1690 );
1691 pay_action_information_api.create_action_information
1692 (p_action_information_id => l_action_info_id
1693 ,p_action_context_id => p_payroll_action_id
1694 ,p_action_context_type => 'PA'
1695 ,p_object_version_number => l_ovn
1696 ,p_effective_date => g_effective_date
1697 ,p_source_id => NULL
1698 ,p_source_text => NULL
1699 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1700 ,p_action_information1 => 'PYSEWTCA'
1701 ,p_action_information2 => 'WTC_PERSON1'
1702 ,p_action_information3 => l_person_number
1703 ,p_action_information4 => l_last_name
1704 ,p_action_information5 => l_first_name
1705 ,p_action_information6 => fnd_date.date_to_canonical
1706 (l_hired_from)
1707 ,p_action_information7 => fnd_date.date_to_canonical
1708 (l_hired_to)
1709 ,p_action_information8 => l_still_employed
1710 ,p_action_information9 => l_absence_from
1711 ,p_action_information10 => l_absence_to
1712 ,p_action_information11 => l_form_of_employment
1713 ,p_action_information12 => l_work_tasks
1714 ,p_action_information13 => l_emp_at_temp_agency
1715 ,p_action_information14 => l_emp_temp_work
1716 ,p_action_information15 => l_reason
1717 ,p_action_information16 => l_notification_date
1718 ,p_action_information17 => l_ending_assignment_by
1719 ,p_action_information18 => l_termination_reason --EOY 2008
1720 ,p_action_information19 => l_absence_percentage --EOY 2008
1721 ,p_action_information20 => l_employment_end_date --EOY 2008
1722 ,p_action_information21 => NULL
1723 ,p_action_information22 => NULL
1724 ,p_action_information23 => NULL
1725 ,p_action_information24 => NULL
1726 ,p_action_information25 => NULL
1727 ,p_action_information26 => NULL
1728 ,p_action_information27 => NULL
1729 ,p_action_information28 => NULL
1730 ,p_action_information29 => NULL
1731 ,p_action_information30 => g_person_id
1732 ,p_assignment_id => g_assignment_id
1733 );
1734 pay_action_information_api.create_action_information
1735 (p_action_information_id => l_action_info_id
1736 ,p_action_context_id => p_payroll_action_id
1737 ,p_action_context_type => 'PA'
1738 ,p_object_version_number => l_ovn
1739 ,p_effective_date => g_effective_date
1740 ,p_source_id => NULL
1741 ,p_source_text => NULL
1742 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1743 ,p_action_information1 => 'PYSEWTCA'
1744 ,p_action_information2 => 'WTC_PERSON2'
1745 ,p_action_information3 => l_continuous_offer
1746 ,p_action_information4 => l_permanent_check_box
1747 ,p_action_information5 => l_permanent_date_from
1748 --fnd_date.date_to_canonical(l_permanent_date)
1749 , p_action_information6 => l_time_limited_check_box
1750 ,p_action_information7 => l_time_limited_from
1751 --fnd_date.date_to_canonical(l_time_limited_from)
1752 , p_action_information8 => l_time_limited_to
1753 --fnd_date.date_to_canonical(l_time_limited_to)
1754 , p_action_information9 => l_other_check_box
1755 ,p_action_information10 => l_other
1756 ,p_action_information11 => l_full_time_check_box
1757 ,p_action_information12 => l_full_time
1758 ,p_action_information13 => l_part_time_check_box
1759 ,p_action_information14 => l_part_time
1760 ,p_action_information15 => l_working_percentage
1761 ,p_action_information16 => l_various_working_time
1762 ,p_action_information17 => l_offer_accepted
1763 ,p_action_information18 => l_decline_date
1764 --fnd_date.date_to_canonical(l_decline_date)
1765 , p_action_information19 => l_time_worked_from
1766 --fnd_date.date_to_canonical (l_time_worked_from)
1767 , p_action_information20 => l_time_worked_to
1768 --fnd_date.date_to_canonical (l_time_worked_to)
1769 , p_action_information21 => l_total_worked_hours
1770 ,p_action_information22 => l_paid_sick_leave_days
1771 ,p_action_information23 => l_teaching_load
1772 ,p_action_information24 => l_aggrmnt_of_compn_signed -- EOY 2008
1773 ,p_action_information25 => l_permanent_date_to -- EOY 2008
1774 ,p_action_information26 => l_teaching_load_check_box -- EOY 2008
1775 ,p_action_information27 => NULL
1776 ,p_action_information28 => NULL
1777 ,p_action_information29 => NULL
1778 ,p_action_information30 => g_person_id
1779 ,p_assignment_id => g_assignment_id
1780 );
1781 pay_action_information_api.create_action_information
1782 (p_action_information_id => l_action_info_id
1783 ,p_action_context_id => p_payroll_action_id
1784 ,p_action_context_type => 'PA'
1785 ,p_object_version_number => l_ovn
1786 ,p_effective_date => g_effective_date
1787 ,p_source_id => NULL
1788 ,p_source_text => NULL
1789 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1790 ,p_action_information1 => 'PYSEWTCA'
1791 ,p_action_information2 => 'WTC_PERSON3'
1792 ,p_action_information3 => l_assign_full_time
1793 ,p_action_information4 => l_assign_hours_week
1794 ,p_action_information5 => l_assign_frequency
1795 ,p_action_information6 => l_assign_part_time
1796 ,p_action_information7 => l_assign_working_percentage
1797 ,p_action_information8 => l_assign_various_work_time
1798 ,p_action_information9 => l_salary_year
1799 ,p_action_information10 => l_assign_salary_paid_out
1800 ,p_action_information11 => l_salary_amount
1801 ,p_action_information12 => l_school_holiday_pay_amount
1802 ,p_action_information13 => l_school_holiday_pay_box
1803 ,p_action_information14 => l_no_of_paid_holiday_days
1804 ,p_action_information15 => l_emp_with_holiday_pay
1805 ,p_action_information16 => l_paid_days_off_duty_time
1806 ,p_action_information17 => l_employed_educational_assoc
1807 ,p_action_information18 => l_holiday_pay_amount -- EOY 2008
1808 ,p_action_information19 => NULL
1809 ,p_action_information20 => NULL
1810 ,p_action_information21 => NULL
1811 ,p_action_information22 => NULL
1812 ,p_action_information23 => NULL
1813 ,p_action_information24 => NULL
1814 ,p_action_information25 => NULL
1815 ,p_action_information26 => NULL
1816 ,p_action_information27 => NULL
1817 ,p_action_information28 => NULL
1818 ,p_action_information29 => NULL
1819 ,p_action_information30 => g_person_id
1820 ,p_assignment_id => g_assignment_id
1821 );
1822 pay_action_information_api.create_action_information
1823 (p_action_information_id => l_action_info_id
1824 ,p_action_context_id => p_payroll_action_id
1825 ,p_action_context_type => 'PA'
1826 ,p_object_version_number => l_ovn
1827 ,p_effective_date => g_effective_date
1828 ,p_source_id => NULL
1829 ,p_source_text => NULL
1830 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1831 ,p_action_information1 => 'PYSEWTCA'
1832 ,p_action_information2 => 'WTC_PERSON4'
1833 ,p_action_information3 => l_holiday_duty
1834 ,p_action_information4 => l_lay_off_period_paid_days
1835 ,p_action_information5 => l_holiday_laid_off
1836 ,p_action_information6 => l_lay_off_from
1837 ,p_action_information7 => l_lay_off_to
1838 ,p_action_information8 => l_other_information
1839 ,p_action_information9 => l_legal_employer_name
1840 ,p_action_information10 => l_org_number
1841 ,p_action_information11 => l_phone_number
1842 ,p_action_information12 => l_location_code
1843 ,p_action_information13 => l_address_line_1
1844 ,p_action_information14 => l_address_line_2
1845 ,p_action_information15 => l_address_line_3
1846 ,p_action_information16 => l_postal_code
1847 ,p_action_information17 => l_town_or_city
1848 ,p_action_information18 => l_region_1
1849 ,p_action_information19 => l_region_2
1850 ,p_action_information20 => l_territory_short_name
1851 ,p_action_information21 => l_hourly_pay_variable -- EOY 2008
1852 ,p_action_information22 => l_hourly_overtime_rate -- EOY 2008
1853 ,p_action_information23 => l_hourly_addl_suppl_time -- EOY 2008
1854 ,p_action_information24 => l_other_taxable_compensation --EOY 2008
1855 ,p_action_information25 => NULL
1856 ,p_action_information26 => NULL
1857 ,p_action_information27 => NULL
1858 ,p_action_information28 => NULL
1859 ,p_action_information29 => NULL
1860 ,p_action_information30 => g_person_id
1861 ,p_assignment_id => g_assignment_id
1862 );
1863 l_set := 0;
1864
1865 FOR i IN value_month_year.FIRST .. value_month_year.LAST
1866 LOOP
1867 --logger ('value_month_year ', value_month_year (i).YEAR);
1868 ----logger (' 1 MONTH ', value_month_year (i).each_month_days ('01'));
1869 --logger (' 2 MONTH ' ,value_month_year (i).each_month_days ('02'));
1870 --logger (' 3 MONTH ' ,value_month_year (i).each_month_days ('03'));
1871 --logger (' 4 MONTH ' ,value_month_year (i).each_month_days ('04'));
1872 --logger (' 5 MONTH ' ,value_month_year (i).each_month_days ('05'));
1873 --logger (' 6 MONTH ' ,value_month_year (i).each_month_days ('06'));
1874 --logger (' 7 MONTH ' ,value_month_year (i).each_month_days ('07'));
1875 --logger (' 8 MONTH ' ,value_month_year (i).each_month_days ('08'));
1876 --logger (' 9 MONTH ' ,value_month_year (i).each_month_days ('09'));
1877 --logger (' 10 MONTH ' ,value_month_year (i).each_month_days ('10') );
1878 --logger (' 11 MONTH ' ,value_month_year (i).each_month_days ('11'));
1879 --logger (' 12 MONTH ' ,value_month_year (i).each_month_days ('12'));
1880 --logger (' value in eit for ', value_month_year (i).YEAR);
1881
1882
1883 FOR lr_se_wtc_time_worked_info IN
1884 csr_se_wtc_time_worked_info (g_assignment_id
1885 ,value_month_year (i).YEAR
1886 )
1887 LOOP
1888 --logger (' Year ', lr_se_wtc_time_worked_info.aei_information1);
1889 --logger (' MONT ', lr_se_wtc_time_worked_info.aei_information2);
1890 --logger (' DAYS ', lr_se_wtc_time_worked_info.aei_information3);
1891 ----logger (' HOUR ', lr_se_wtc_time_worked_info.aei_information4);
1892 --logger (' value in PLtable was ' ,value_month_year (i).each_month_days (lr_se_wtc_time_worked_info.aei_information2) );
1893 value_month_year (i).each_month_days
1894 (lr_se_wtc_time_worked_info.aei_information2) :=
1895 lr_se_wtc_time_worked_info.aei_information3;
1896
1897
1898
1899 --logger (' value in PLtable IS ' ,value_month_year (i).each_month_days (lr_se_wtc_time_worked_info.aei_information2) );
1900 --logger (' value in PLtable was ' ,value_month_year (i).each_month_hours (lr_se_wtc_time_worked_info.aei_information2) );
1901 value_month_year (i).each_month_hours
1902 (lr_se_wtc_time_worked_info.aei_information2) :=
1903 lr_se_wtc_time_worked_info.aei_information4;
1904 --logger (' value in PLtable IS ' ,value_month_year (i).each_month_hours (lr_se_wtc_time_worked_info.aei_information2) );
1905
1906
1907
1908 ---------------------------------------------------------------------------------------------------------------------
1909 --Added the code for the Overtime and the Addl/Suppl Time Hours -- EOY 2008
1910 -------------------------------------------------------------------------------------------------------------------
1911
1912
1913
1914 l_report_start_date := TO_DATE('01/'||lr_se_wtc_time_worked_info.aei_information2||'/'||lr_se_wtc_time_worked_info.aei_information1,'DD/MM/YYYY');
1915
1916 SELECT last_day(l_report_start_date)
1917 INTO l_reporting_date
1918 FROM DUAL;
1919
1920 fnd_file.put_line (fnd_file.LOG, 'l_reporting_date'||l_reporting_date);
1921
1922 l_overtime_hours :=0;
1923 l_tot_overtime_hours :=0;
1924 value_month_year(i).tot_overtime_hours(lr_se_wtc_time_worked_info.aei_information2) := l_tot_overtime_hours;
1925 BEGIN
1926 FOR balance_rec IN csr_balance('Overtime - Hours' , g_business_group_id)
1927 LOOP
1928 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1929 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1930 CLOSE csr_bg_Get_Defined_Balance_Id;
1931 IF csr_balance%FOUND THEN
1932 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 ) ;
1933 l_tot_overtime_hours := l_tot_overtime_hours + nvl(l_overtime_hours,0);
1934 END IF;
1935 END LOOP ;
1936
1937 value_month_year(i).tot_overtime_hours(lr_se_wtc_time_worked_info.aei_information2) := l_tot_overtime_hours;
1938
1939 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_overtime_hours' ||l_tot_overtime_hours);
1940
1941 EXCEPTION
1942 WHEN others THEN
1943 fnd_file.put_line (fnd_file.LOG, 'Error for overtime'||substr(sqlerrm,1,30));
1944 null;
1945 END;
1946
1947
1948
1949 BEGIN
1950 l_addl_time_hours :=0;
1951 l_tot_addl_time_hours:=0;
1952 value_month_year(i).tot_addl_time_hours(lr_se_wtc_time_worked_info.aei_information2) := l_tot_addl_time_hours ;
1953 FOR balance_rec IN csr_balance('Additional Time - Hours' , g_business_group_id)
1954 LOOP
1955
1956 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1957 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1958 CLOSE csr_bg_Get_Defined_Balance_Id;
1959
1960 IF csr_balance%FOUND THEN
1961
1962 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 ) ;
1963 l_tot_addl_time_hours := l_tot_addl_time_hours + nvl(l_addl_time_hours,0);
1964 END IF;
1965 END LOOP ;
1966
1967 value_month_year(i).tot_addl_time_hours(lr_se_wtc_time_worked_info.aei_information2) := l_tot_addl_time_hours ;
1968
1969
1970 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_addl_time_hours' ||l_tot_addl_time_hours);
1971 EXCEPTION
1972 WHEN others THEN
1973 fnd_file.put_line (fnd_file.LOG, 'Error'||substr(sqlerrm,1,30));
1974 null;
1975 END;
1976
1977
1978 l_absence_hours :=0;
1979 l_tot_absence_hours :=0;
1980 value_month_year(i).tot_absence_hours(lr_se_wtc_time_worked_info.aei_information2) := l_tot_absence_hours;
1981 BEGIN
1982 FOR balance_rec IN csr_balance('UnPaid Absence - Hours' , g_business_group_id)
1983 LOOP
1984 OPEN csr_bg_Get_Defined_Balance_Id( balance_rec.balance_name||l_dimension,g_business_group_id);
1985 FETCH csr_bg_Get_Defined_Balance_Id INTO rg_csr_bg_get_defined_bal_id;
1986 CLOSE csr_bg_Get_Defined_Balance_Id;
1987 IF csr_balance%FOUND THEN
1988 l_absence_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 ) ;
1989 l_tot_absence_hours := l_tot_absence_hours + nvl(l_absence_hours,0);
1990 END IF;
1991 END LOOP ;
1992
1993 value_month_year(i).tot_absence_hours(lr_se_wtc_time_worked_info.aei_information2) := l_tot_absence_hours;
1994
1995 -- fnd_file.put_line (fnd_file.LOG, 'l_tot_absence_hours' ||l_tot_absence_hours);
1996
1997 EXCEPTION
1998 WHEN others THEN
1999 fnd_file.put_line (fnd_file.LOG, 'Error for absence'||substr(sqlerrm,1,30));
2000 null;
2001 END;
2002
2003
2004
2005 --------------------------------------------------------------------------------------------------------------------
2006
2007 END LOOP;
2008
2009 IF (MOD (i, 2) = 0)
2010 THEN
2011 l_set := l_set + 1;
2012 END IF;
2013
2014
2015 pay_action_information_api.create_action_information
2016 (p_action_information_id => l_action_info_id
2017 ,p_action_context_id => p_payroll_action_id
2018 ,p_action_context_type => 'PA'
2019 ,p_object_version_number => l_ovn
2020 ,p_effective_date => g_effective_date
2021 ,p_source_id => NULL
2022 ,p_source_text => NULL
2023 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2024 ,p_action_information1 => 'PYSEWTCA'
2025 ,p_action_information2 => 'WTC_PERSON5'
2026 ,p_action_information3 => value_month_year (i).each_month_days
2027 ('01')
2028 ,p_action_information4 => value_month_year (i).each_month_days
2029 ('02')
2030 ,p_action_information5 => value_month_year (i).each_month_days
2031 ('03')
2032 ,p_action_information6 => value_month_year (i).each_month_days
2033 ('04')
2034 ,p_action_information7 => value_month_year (i).each_month_days
2035 ('05')
2036 ,p_action_information8 => value_month_year (i).each_month_days
2037 ('06')
2038 ,p_action_information9 => value_month_year (i).each_month_days
2039 ('07')
2040 ,p_action_information10 => value_month_year (i).each_month_days
2041 ('08')
2042 ,p_action_information11 => value_month_year (i).each_month_days
2043 ('09')
2044 ,p_action_information12 => value_month_year (i).each_month_days
2045 ('10')
2046 ,p_action_information13 => value_month_year (i).each_month_days
2047 ('11')
2048 ,p_action_information14 => value_month_year (i).each_month_days
2049 ('12')
2050 ,p_action_information15 => value_month_year (i).YEAR
2051 ,p_action_information16 => value_month_year (i).each_month_hours
2052 ('01')
2053 ,p_action_information17 => value_month_year (i).each_month_hours
2054 ('02')
2055 ,p_action_information18 => value_month_year (i).each_month_hours
2056 ('03')
2057 ,p_action_information19 => value_month_year (i).each_month_hours
2058 ('04')
2059 ,p_action_information20 => value_month_year (i).each_month_hours
2060 ('05')
2061 ,p_action_information21 => value_month_year (i).each_month_hours
2062 ('06')
2063 ,p_action_information22 => value_month_year (i).each_month_hours
2064 ('07')
2065 ,p_action_information23 => value_month_year (i).each_month_hours
2066 ('08')
2067 ,p_action_information24 => value_month_year (i).each_month_hours
2068 ('09')
2069 ,p_action_information25 => value_month_year (i).each_month_hours
2070 ('10')
2071 ,p_action_information26 => value_month_year (i).each_month_hours
2072 ('11')
2073 ,p_action_information27 => value_month_year (i).each_month_hours
2074 ('12')
2075 ,p_action_information28 => l_set
2076 ,p_action_information29 => NULL
2077 ,p_action_information30 => g_person_id
2078 ,p_assignment_id => g_assignment_id
2079 );
2080
2081
2082
2083 ------------------------------ Archive for Addl/Suplementary Hrs and Overtime Hours --------------
2084 pay_action_information_api.create_action_information
2085 (p_action_information_id => l_action_info_id
2086 ,p_action_context_id => p_payroll_action_id
2087 ,p_action_context_type => 'PA'
2088 ,p_object_version_number => l_ovn
2089 ,p_effective_date => g_effective_date
2090 ,p_source_id => NULL
2091 ,p_source_text => NULL
2092 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2093 ,p_action_information1 => 'PYSEWTCA'
2094 ,p_action_information2 => 'WTC_PERSON6'
2095 ,p_action_information3 => value_month_year (i).tot_addl_time_hours('01')
2096 ,p_action_information4 => value_month_year (i).tot_addl_time_hours('02')
2097
2098 ,p_action_information5 => value_month_year (i).tot_addl_time_hours('03')
2099
2100 ,p_action_information6 => value_month_year (i).tot_addl_time_hours('04')
2101
2102 ,p_action_information7 => value_month_year (i).tot_addl_time_hours('05')
2103
2104 ,p_action_information8 => value_month_year (i).tot_addl_time_hours('06')
2105
2106 ,p_action_information9 => value_month_year (i).tot_addl_time_hours('07')
2107
2108 ,p_action_information10 => value_month_year (i).tot_addl_time_hours('08')
2109
2110 ,p_action_information11 => value_month_year (i).tot_addl_time_hours('09')
2111
2112 ,p_action_information12 => value_month_year (i).tot_addl_time_hours('10')
2113
2114 ,p_action_information13 => value_month_year (i).tot_addl_time_hours('11')
2115
2116 ,p_action_information14 => value_month_year (i).tot_addl_time_hours('12')
2117
2118 ,p_action_information15 => value_month_year (i).YEAR
2119 ,p_action_information16 => value_month_year (i).tot_overtime_hours('01')
2120
2121 ,p_action_information17 => value_month_year (i).tot_overtime_hours('02')
2122
2123 ,p_action_information18 => value_month_year (i).tot_overtime_hours('03')
2124
2125 ,p_action_information19 => value_month_year (i).tot_overtime_hours('04')
2126
2127 ,p_action_information20 => value_month_year (i).tot_overtime_hours('05')
2128
2129 ,p_action_information21 => value_month_year (i).tot_overtime_hours('06')
2130
2131 ,p_action_information22 => value_month_year (i).tot_overtime_hours('07')
2132
2133 ,p_action_information23 => value_month_year (i).tot_overtime_hours('08')
2134
2135 ,p_action_information24 => value_month_year (i).tot_overtime_hours('09')
2136
2137 ,p_action_information25 => value_month_year (i).tot_overtime_hours('10')
2138
2139 ,p_action_information26 => value_month_year (i).tot_overtime_hours('11')
2140
2141 ,p_action_information27 => value_month_year (i).tot_overtime_hours('12')
2142
2143 ,p_action_information28 => l_set
2144 ,p_action_information29 => NULL
2145 ,p_action_information30 => g_person_id
2146 ,p_assignment_id => g_assignment_id
2147 );
2148
2149
2150
2151
2152 ---------------------- Populate Absence Fields ---------------------------------------------
2153 pay_action_information_api.create_action_information
2154 (p_action_information_id => l_action_info_id
2155 ,p_action_context_id => p_payroll_action_id
2156 ,p_action_context_type => 'PA'
2157 ,p_object_version_number => l_ovn
2158 ,p_effective_date => g_effective_date
2159 ,p_source_id => NULL
2160 ,p_source_text => NULL
2161 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2162 ,p_action_information1 => 'PYSEWTCA'
2163 ,p_action_information2 => 'WTC_PERSON7'
2164 ,p_action_information3 => value_month_year (i).tot_absence_hours('01')
2165 ,p_action_information4 => value_month_year (i).tot_absence_hours('02')
2166
2167 ,p_action_information5 => value_month_year (i).tot_absence_hours('03')
2168
2169 ,p_action_information6 => value_month_year (i).tot_absence_hours('04')
2170
2171 ,p_action_information7 => value_month_year (i).tot_absence_hours('05')
2172
2173 ,p_action_information8 => value_month_year (i).tot_absence_hours('06')
2174
2175 ,p_action_information9 => value_month_year (i).tot_absence_hours('07')
2176
2177 ,p_action_information10 => value_month_year (i).tot_absence_hours('08')
2178
2179 ,p_action_information11 => value_month_year (i).tot_absence_hours('09')
2180
2181 ,p_action_information12 => value_month_year (i).tot_absence_hours('10')
2182
2183 ,p_action_information13 => value_month_year (i).tot_absence_hours('11')
2184
2185 ,p_action_information14 => value_month_year (i).tot_absence_hours('12')
2186
2187 ,p_action_information15 => value_month_year (i).YEAR
2188 ,p_action_information16 => NULL
2189
2190 ,p_action_information17 => NULL
2191
2192 ,p_action_information18 => NULL
2193
2194 ,p_action_information19 => NULL
2195
2196 ,p_action_information20 => NULL
2197
2198 ,p_action_information21 => NULL
2199
2200 ,p_action_information22 => NULL
2201
2202 ,p_action_information23 => NULL
2203
2204 ,p_action_information24 => NULL
2205
2206 ,p_action_information25 => NULL
2207
2208 ,p_action_information26 => NULL
2209
2210 ,p_action_information27 => NULL
2211
2212 ,p_action_information28 => l_set
2213 ,p_action_information29 => NULL
2214 ,p_action_information30 => g_person_id
2215 ,p_assignment_id => g_assignment_id
2216 );
2217
2218
2219
2220
2221
2222 END LOOP;
2223
2224 -- *****************************************************************************
2225 -- *****************************************************************************
2226
2227 --END OF PICKING UP DATA
2228 -- *****************************************************************************
2229 p_sql :=
2230 'SELECT DISTINCT person_id
2231 FROM per_people_f ppf
2232 ,pay_payroll_actions ppa
2233 WHERE ppa.payroll_action_id = :payroll_action_id
2234 AND ppa.business_group_id = ppf.business_group_id
2235 AND ppf.person_id = '''
2236 || g_person_id
2237 || '''
2238 ORDER BY ppf.person_id';
2239
2240 -- *****************************************************************************
2241 IF g_debug
2242 THEN
2243 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
2244 END IF;
2245 EXCEPTION
2246 WHEN others THEN
2247 fnd_file.put_line (fnd_file.LOG, 'Error for archive'||substr(sqlerrm,1,30));
2248 null;
2249 /* EXCEPTION
2250 WHEN OTHERS
2251 THEN
2252 -- Return cursor that selects no rows
2253 p_sql :=
2254 'select 1 from dual where to_char(:payroll_action_id) = dummy'; */
2255 END range_code;
2256
2257 /* ASSIGNMENT ACTION CODE */
2258 PROCEDURE assignment_action_code (
2259 p_payroll_action_id IN NUMBER
2260 ,p_start_person IN NUMBER
2261 ,p_end_person IN NUMBER
2262 ,p_chunk IN NUMBER
2263 )
2264 IS
2265 -- End of User pARAMETERS needed
2266 BEGIN
2267 NULL;
2268 END assignment_action_code;
2269
2270 /*fffffffffffffffffffffffffff*/
2271
2272 /* INITIALIZATION CODE */
2273 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
2274 IS
2275 l_action_info_id NUMBER;
2276 l_ovn NUMBER;
2277 l_count NUMBER := 0;
2278 l_business_group_id NUMBER;
2279 l_start_date VARCHAR2 (20);
2280 l_end_date VARCHAR2 (20);
2281 l_effective_date DATE;
2282 l_payroll_id NUMBER;
2283 l_consolidation_set NUMBER;
2284 l_prev_prepay NUMBER := 0;
2285 BEGIN
2286 IF g_debug
2287 THEN
2288 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE'
2289 ,80
2290 );
2291 END IF;
2292
2293 fnd_file.put_line (fnd_file.LOG, 'In INIT_CODE 0');
2294 g_payroll_action_id := p_payroll_action_id;
2295 g_business_group_id := NULL;
2296 g_effective_date := NULL;
2297 g_person_id := NULL;
2298 g_assignment_id := NULL;
2299 pay_se_work_time_certificate.get_all_parameters (p_payroll_action_id
2300 ,g_business_group_id
2301 ,g_effective_date
2302 ,g_person_id
2303 ,g_assignment_id
2304 ,g_still_employed
2305 ,g_income_salary_year
2306 );
2307 fnd_file.put_line
2308 (fnd_file.LOG
2309 ,'In the INITIALIZATION_CODE After Initiliazing the global parameter '
2310 );
2311
2312 IF g_debug
2313 THEN
2314 hr_utility.set_location (' Leaving Procedure INITIALIZATION_CODE'
2315 ,90
2316 );
2317 END IF;
2318 EXCEPTION
2319 WHEN OTHERS
2320 THEN
2321 g_err_num := SQLCODE;
2322
2323 IF g_debug
2324 THEN
2325 hr_utility.set_location ( 'ORA_ERR: '
2326 || g_err_num
2327 || 'In INITIALIZATION_CODE'
2328 ,180
2329 );
2330 END IF;
2331 END initialization_code;
2332
2333 /* GET DEFINED BALANCE ID */
2334 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
2335 RETURN NUMBER
2336 IS
2337 /* Cursor to retrieve Defined Balance Id */
2338 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
2339 IS
2340 SELECT u.creator_id
2341 FROM ff_user_entities u
2342 ,ff_database_items d
2343 WHERE d.user_name = p_user_name
2344 AND u.user_entity_id = d.user_entity_id
2345 AND (u.legislation_code = 'SE')
2346 AND (u.business_group_id IS NULL)
2347 AND u.creator_type = 'B';
2348
2349 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
2350 BEGIN
2351 IF g_debug
2352 THEN
2353 hr_utility.set_location
2354 (' Entering Function GET_DEFINED_BALANCE_ID'
2355 ,240
2356 );
2357 END IF;
2358
2359 OPEN csr_def_bal_id (p_user_name);
2360
2361 FETCH csr_def_bal_id
2362 INTO l_defined_balance_id;
2363
2364 CLOSE csr_def_bal_id;
2365
2366 RETURN l_defined_balance_id;
2367
2368 IF g_debug
2369 THEN
2370 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
2371 ,250
2372 );
2373 END IF;
2374 END get_defined_balance_id;
2375
2376 FUNCTION get_defined_balance_value (
2377 p_user_name IN VARCHAR2
2378 ,p_in_assignment_id IN NUMBER
2379 ,p_in_virtual_date IN DATE
2380 ,p_tax_unit_id IN NUMBER
2381 ,p_local_unit_id IN NUMBER
2382 )
2383 RETURN NUMBER
2384 IS
2385 /* Cursor to retrieve Defined Balance Id */
2386 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
2387 IS
2388 SELECT u.creator_id
2389 FROM ff_user_entities u
2390 ,ff_database_items d
2391 WHERE d.user_name = p_user_name
2392 AND u.user_entity_id = d.user_entity_id
2393 AND (u.legislation_code = 'SE')
2394 AND (u.business_group_id IS NULL)
2395 AND u.creator_type = 'B';
2396
2397 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
2398 l_return_balance_value NUMBER;
2399 BEGIN
2400 IF g_debug
2401 THEN
2402 hr_utility.set_location
2403 (' Entering Function GET_DEFINED_BALANCE_VALUE'
2404 ,240
2405 );
2406 END IF;
2407
2408 OPEN csr_def_bal_id (p_user_name);
2409
2410 FETCH csr_def_bal_id
2411 INTO l_defined_balance_id;
2412
2413 CLOSE csr_def_bal_id;
2414
2415 -- pay_balance_pkg.set_context ('SOURCE_TEXT', NULL);
2416 pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
2417
2418 fnd_file.put_line (fnd_file.LOG, 'p_tax_unit_id'||p_tax_unit_id);
2419
2420 pay_balance_pkg.set_context ('LOCAL_UNIT_ID', p_local_unit_id);
2421 l_return_balance_value :=
2422 TO_CHAR
2423 (pay_balance_pkg.get_value
2424 (p_defined_balance_id => l_defined_balance_id
2425 ,p_assignment_id => p_in_assignment_id
2426 ,p_virtual_date => p_in_virtual_date
2427 )
2428 ,'999999999D99'
2429 );
2430 RETURN l_return_balance_value;
2431
2432 IF g_debug
2433 THEN
2434 hr_utility.set_location
2435 (' Leaving Function GET_DEFINED_BALANCE_VALUE'
2436 ,250
2437 );
2438 END IF;
2439 END get_defined_balance_value;
2440
2441 /* ARCHIVE CODE */
2442 PROCEDURE archive_code (
2443 p_assignment_action_id IN NUMBER
2444 ,p_effective_date IN DATE
2445 )
2446 IS
2447 -- End of place for Cursor which fetches the values to be archived
2448 BEGIN
2449 NULL;
2450 END archive_code;
2451
2452 --- Report XML generating code
2453 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
2454 IS
2455 l_xfdf_string CLOB;
2456 l_str1 VARCHAR2 (1000);
2457 l_str2 VARCHAR2 (20);
2458 l_str3 VARCHAR2 (20);
2459 l_str4 VARCHAR2 (20);
2460 l_str5 VARCHAR2 (20);
2461 l_str6 VARCHAR2 (30);
2462 l_str7 VARCHAR2 (1000);
2463 l_str8 VARCHAR2 (240);
2464 l_str9 VARCHAR2 (240);
2465 l_str10 VARCHAR2 (20);
2466 l_str11 VARCHAR2 (20);
2467 current_index PLS_INTEGER;
2468 l_iana_charset VARCHAR2 (50);
2469 BEGIN
2470 l_iana_charset := hr_se_utility.get_iana_charset;
2471 hr_utility.set_location ('Entering WritetoCLOB ', 70);
2472 l_str1 :=
2473 '<?xml version="1.0" encoding="'
2474 || l_iana_charset
2475 || '"?> <ROOT><WTCR>';
2476 l_str2 := '<';
2477 l_str3 := '>';
2478 l_str4 := '</';
2479 l_str5 := '>';
2480 l_str6 := '</WTCR></ROOT>';
2481 l_str7 :=
2482 '<?xml version="1.0" encoding="'
2483 || l_iana_charset
2484 || '"?> <ROOT></ROOT>';
2485 l_str10 := '<WTCR>';
2486 l_str11 := '</WTCR>';
2487 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
2488 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
2489 current_index := 0;
2490
2491 IF gplsqltable.COUNT > 0
2492 THEN
2493 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
2494
2495 FOR table_counter IN gplsqltable.FIRST .. gplsqltable.LAST
2496 LOOP
2497 l_str8 := gplsqltable (table_counter).tagname;
2498 l_str9 := gplsqltable (table_counter).tagvalue;
2499
2500 IF l_str9 IN
2501 ('PERSON'
2502 ,'LE_ADDRESS_END'
2503 ,'PERSON_END'
2504 ,'LE_ADDRESS'
2505 ,'REPORTINGYEAR'
2506 ,'REPORTINGYEAR_END'
2507 ,'OTHERYEAR'
2508 ,'OTHERYEAR_END'
2509 ,'FIRSTYEAR'
2510 ,'FIRSTYEAR_END'
2511 ,'SECONDYEAR_END'
2512 ,'SECONDYEAR'
2513 )
2514 THEN
2515 IF l_str9 IN
2516 ('LE_ADDRESS'
2517 ,'PERSON'
2518 ,'REPORTINGYEAR'
2519 ,'OTHERYEAR'
2520 ,'FIRSTYEAR'
2521 ,'SECONDYEAR'
2522 )
2523 THEN
2524 DBMS_LOB.writeappend (l_xfdf_string
2525 ,LENGTH (l_str2)
2526 ,l_str2
2527 );
2528 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2529 ,l_str8);
2530 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
2531 ,l_str3);
2532 ELSE
2533 DBMS_LOB.writeappend (l_xfdf_string
2534 ,LENGTH (l_str4)
2535 ,l_str4
2536 );
2537 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2538 ,l_str8);
2539 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
2540 ,l_str5);
2541 END IF;
2542 ELSE
2543 IF l_str9 IS NOT NULL
2544 THEN
2545 DBMS_LOB.writeappend (l_xfdf_string
2546 ,LENGTH (l_str2)
2547 ,l_str2
2548 );
2549 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2550 ,l_str8);
2551 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
2552 ,l_str3);
2553 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
2554 ,l_str9);
2555 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
2556 ,l_str4);
2557 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2558 ,l_str8);
2559 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
2560 ,l_str5);
2561 ELSE
2562 DBMS_LOB.writeappend (l_xfdf_string
2563 ,LENGTH (l_str2)
2564 ,l_str2
2565 );
2566 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2567 ,l_str8);
2568 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
2569 ,l_str3);
2570 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
2571 ,l_str4);
2572 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
2573 ,l_str8);
2574 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
2575 ,l_str5);
2576 END IF;
2577 END IF;
2578 END LOOP;
2579
2580 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
2581 ELSE
2582 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
2583 END IF;
2584
2585 p_xfdf_clob := l_xfdf_string;
2586 hr_utility.set_location ('Leaving WritetoCLOB ', 40);
2587 EXCEPTION
2588 WHEN OTHERS
2589 THEN
2590 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
2591 hr_utility.raise_error;
2592 END writetoclob;
2593
2594 -----------------------------------------------------------------------------------------------------------------
2595 --Procedure to Break the digits of a Person Number
2596
2597 PROCEDURE get_digit_breakup(
2598 p_number IN NUMBER,
2599 p_digit1 OUT NOCOPY NUMBER,
2600 p_digit2 OUT NOCOPY NUMBER,
2601 p_digit3 OUT NOCOPY NUMBER,
2602 p_digit4 OUT NOCOPY NUMBER,
2603 p_digit5 OUT NOCOPY NUMBER,
2604 p_digit6 OUT NOCOPY NUMBER,
2605 p_digit7 OUT NOCOPY NUMBER,
2606 p_digit8 OUT NOCOPY NUMBER,
2607 p_digit9 OUT NOCOPY NUMBER,
2608 p_digit10 OUT NOCOPY NUMBER
2609 )
2610 IS
2611
2612 TYPE digits IS
2613 TABLE OF NUMBER
2614 INDEX BY BINARY_INTEGER;
2615 l_digit digits;
2616 l_count NUMBER :=1;
2617 l_number number(10);
2618 BEGIN
2619 l_number:=floor(p_number);
2620 FOR I in 1..10 loop
2621 l_digit(I):=null;
2622 END loop;
2623
2624 WHILE l_number >= 1 LOOP
2625
2626 SELECT mod(l_number,10) INTO l_digit(l_count) from dual;
2627 l_number:=floor(l_number/10);
2628 l_count:=l_count+1;
2629 END LOOP;
2630
2631 SELECT floor(l_number) INTO l_digit(l_number) from dual;
2632 p_digit1:=l_digit(1);
2633 p_digit2:=l_digit(2);
2634 p_digit3:=l_digit(3);
2635 p_digit4:=l_digit(4);
2636 p_digit5:=l_digit(5);
2637 p_digit6:=l_digit(6);
2638 p_digit7:=l_digit(7);
2639 p_digit8:=l_digit(8);
2640 p_digit9:=l_digit(9);
2641 p_digit10:=l_digit(10);
2642 END get_digit_breakup;
2643
2644 --------------------------------------------------------------------------------------------------------------------
2645
2646
2647 PROCEDURE get_xml_for_report (
2648 p_business_group_id IN NUMBER
2649 ,p_payroll_action_id IN VARCHAR2
2650 ,p_template_name IN VARCHAR2
2651 ,p_xml OUT NOCOPY CLOB
2652 )
2653 IS
2654 --Variables needed for the report
2655 l_counter NUMBER := 0;
2656 l_payroll_action_id pay_action_information.action_information1%TYPE;
2657 --- Digits added for Break-up of Person Number and Date
2658 l_digit1 NUMBER(1);
2659 l_digit2 NUMBER(1);
2660 l_digit3 NUMBER(1);
2661 l_digit4 NUMBER(1);
2662 l_digit5 NUMBER(1);
2663 l_digit6 NUMBER(1);
2664 l_digit7 NUMBER(1);
2665 l_digit8 NUMBER(1);
2666 l_digit9 NUMBER(1);
2667 l_digit10 NUMBER(1);
2668 continuous_offer_from NUMBER;
2669 continuous_offer_to NUMBER;
2670 until_further_notice VARCHAR(10) := 'N';
2671 time_limited_check_box VARCHAR(10) := 'N';
2672
2673
2674 --Cursors needed for report
2675 CURSOR csr_all_legal_employer (
2676 csr_v_pa_id pay_action_information.action_context_id%TYPE
2677 )
2678 IS
2679 SELECT action_information3
2680 ,action_information4
2681 ,action_information5
2682 FROM pay_action_information
2683 WHERE action_context_type = 'PA'
2684 AND action_context_id = csr_v_pa_id
2685 AND action_information_category = 'EMEA REPORT INFORMATION'
2686 AND action_information1 = 'PYSEWTCR'
2687 AND action_information2 = 'LE';
2688
2689 CURSOR csr_wtc_person1 (
2690 csr_v_pa_id pay_action_information.action_context_id%TYPE
2691 )
2692 IS
2693 SELECT action_information3 person_number
2694 ,action_information4 emp_last_name
2695 ,action_information5 emp_first_name
2696 -- ,ACTION_INFORMATION6 HIRED_FROM
2697 -- ,ACTION_INFORMATION7 HIRED_TO
2698 --,TO_CHAR (fnd_date.canonical_to_date (action_information6)
2699 --,'YYYY-MM-DD'
2700 -- ) hired_from
2701
2702 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information6),'YYYYMMDD')) hired_from
2703 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information7),'YYYYMMDD')) hired_to
2704
2705 -- ,TO_CHAR (fnd_date.canonical_to_date (action_information7)
2706 -- ,'YYYY-MM-DD'
2707 -- ) hired_to
2708
2709 ,action_information8 still_employed
2710
2711 --,TO_CHAR (fnd_date.canonical_to_date (action_information9)
2712 -- ,'YYYY-MM-DD'
2713 --) absence_from
2714 -- ,TO_CHAR (fnd_date.canonical_to_date (action_information10)
2715 --,'YYYY-MM-DD'
2716 --) absence_to
2717 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information9),'YYYYMMDD')) absence_from
2718 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information10),'YYYYMMDD')) absence_to
2719
2720 ,action_information11 form_of_employment
2721 ,action_information12 work_taks
2722 ,action_information13 employed_temp_agency
2723 ,action_information14 employed_temp_work
2724 ,action_information15 reason
2725
2726 --,ACTION_INFORMATION16 NOTIFICATION_DATE
2727 --,fnd_date.canonical_to_date(ACTION_INFORMATION16) NOTIFICATION_DATE
2728 --,TO_CHAR (fnd_date.canonical_to_date (action_information16)
2729 --,'YYYY-MM-DD'
2730 --) notification_date
2731
2732 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information16),'YYYYMMDD')) notification_date
2733
2734 ,action_information17 employees_request
2735 ,action_information18 termination_reason
2736 ,action_information19 absence_percentage
2737 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information20),'YYYYMMDD'))employment_end_date
2738 FROM pay_action_information
2739 WHERE action_context_type = 'PA'
2740 AND action_context_id = csr_v_pa_id
2741 AND action_information_category = 'EMEA REPORT INFORMATION'
2742 AND action_information1 = 'PYSEWTCA'
2743 AND action_information2 = 'WTC_PERSON1';
2744
2745 CURSOR csr_wtc_person2 (
2746 csr_v_pa_id pay_action_information.action_context_id%TYPE
2747 )
2748 IS
2749 SELECT action_information3 continuous_offer
2750 ,action_information4 permanent_check
2751 -- ,ACTION_INFORMATION5 PERMANENT_DATE
2752 --,TO_CHAR (fnd_date.canonical_to_date (action_information5)
2753 --,'YYYY-MM-DD'
2754 --) permanent_date
2755
2756 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information5),'YYYYMMDD')) permanent_date_from
2757 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information25),'YYYYMMDD')) permanent_date_to
2758 ,action_information6 time_limited_check
2759
2760 -- ,ACTION_INFORMATION7 TIME_LIMITED_FROM
2761 -- ,ACTION_INFORMATION8 TIME_LIMITED_TO
2762 --,TO_CHAR (fnd_date.canonical_to_date (action_information7)
2763 --,'YYYY-MM-DD'
2764 --) time_limited_from
2765 --,TO_CHAR (fnd_date.canonical_to_date (action_information8)
2766 --,'YYYY-MM-DD'
2767 --) time_limited_to
2768
2769 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information7),'YYYYMMDD')) time_limited_from
2770 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information8),'YYYYMMDD')) time_limited_to
2771
2772 ,action_information9 other_check
2773 ,action_information10 other
2774 ,action_information11 full_time_check
2775 ,action_information12 full_time
2776 ,action_information13 part_time_check
2777 ,action_information14 part_time
2778 ,action_information15 working_percentage
2779 ,action_information16 various_working_time
2780 ,action_information17 offer_accepted
2781 -- ,ACTION_INFORMATION18 DECLINE_DATE
2782 -- ,ACTION_INFORMATION19 TIME_WORKED_FROM
2783 -- ,ACTION_INFORMATION20 TIME_WORKED_TO
2784 --, TO_CHAR (fnd_date.canonical_to_date (NVL(action_information18,NULL))
2785 --,'YYYY-MM-DD'
2786 --) decline_date
2787 --,TO_CHAR (fnd_date.canonical_to_date (NVL(action_information19,NULL))
2788 --,'YYYY-MM-DD'
2789 --) time_worked_from
2790 --,TO_CHAR (fnd_date.canonical_to_date (NVL(action_information20,NULL))
2791 --,'YYYY-MM-DD'
2792 --) time_worked_to
2793
2794 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information18),'YYYYMMDD')) decline_date
2795 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information19),'YYYYMMDD')) time_worked_from
2796 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information20),'YYYYMMDD')) time_worked_to
2797
2798 ,action_information21 total_worked_hours
2799 ,action_information22 paid_sick_leave_days
2800 ,action_information23 teaching_load
2801 ,action_information24 aggrmnt_of_compn_signed --EOY 2008
2802 ,action_information26 teaching_load_check_box -- EOY 2008
2803 FROM pay_action_information
2804 WHERE action_context_type = 'PA'
2805 AND action_context_id = csr_v_pa_id
2806 AND action_information_category = 'EMEA REPORT INFORMATION'
2807 AND action_information1 = 'PYSEWTCA'
2808 AND action_information2 = 'WTC_PERSON2';
2809
2810 CURSOR csr_wtc_person3 (
2811 csr_v_pa_id pay_action_information.action_context_id%TYPE
2812 )
2813 IS
2814 SELECT action_information3 assign_full_time
2815 ,action_information4 assign_hours_week
2816 ,action_information5 assign_frequency
2817 ,action_information6 assign_part_time
2818 ,action_information7 assign_working_percentage
2819 ,action_information8 assign_various_work_time
2820 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(action_information9)) salary_year
2821 ,action_information10 assign_salary_paid_out
2822 ,action_information11 salary_amount
2823 ,action_information12 school_holiday_pay_amount
2824 ,action_information13 school_holiday_pay_box
2825 ,action_information14 no_of_paid_holiday_days
2826 ,action_information15 emp_with_holiday_pay
2827 ,action_information16 paid_days_off_duty_time
2828 ,action_information17 employed_educational_assoc
2829 ,action_information18 holiday_pay_amount
2830 FROM pay_action_information
2831 WHERE action_context_type = 'PA'
2832 AND action_context_id = csr_v_pa_id
2833 AND action_information_category = 'EMEA REPORT INFORMATION'
2834 AND action_information1 = 'PYSEWTCA'
2835 AND action_information2 = 'WTC_PERSON3';
2836
2837 CURSOR csr_wtc_person4 (
2838 csr_v_pa_id pay_action_information.action_context_id%TYPE
2839 )
2840 IS
2841 SELECT action_information3 holiday_duty
2842 ,action_information4 lay_off_period_paid_days
2843 ,action_information5 holiday_laid_off
2844
2845 --,TO_CHAR (fnd_date.canonical_to_date (action_information6)
2846 --,'YYYY-MM-DD'
2847 --) lay_off_from
2848 --,TO_CHAR (fnd_date.canonical_to_date (action_information7)
2849 --,'YYYY-MM-DD'
2850 --) lay_off_to
2851 -- ,ACTION_INFORMATION6 LAY_OFF_FROM
2852 -- ,ACTION_INFORMATION7 LAY_OFF_TO
2853
2854 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information6),'YYYYMMDD')) lay_off_from
2855 ,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information7),'YYYYMMDD')) lay_off_to
2856
2857
2858 ,action_information8 other_information
2859 ,action_information9 legal_employer_name
2860 ,action_information10 org_number
2861 ,action_information11 phone_number
2862 ,action_information12 location_code
2863 ,action_information13 address_line_1
2864 ,action_information14 address_line_2
2865 ,action_information15 address_line_3
2866 ,action_information16 postal_code
2867 ,action_information17 town_or_city
2868 ,action_information18 region_1
2869 ,action_information19 region_2
2870 ,action_information20 territory_short_name
2871 ,action_information21 hourly_pay_variable
2872 ,action_information22 hourly_overtime_rate
2873 ,action_information23 hourly_addl_suppl_time
2874 ,action_information24 other_taxable_compensation
2875 FROM pay_action_information
2876 WHERE action_context_type = 'PA'
2877 AND action_context_id = csr_v_pa_id
2878 AND action_information_category = 'EMEA REPORT INFORMATION'
2879 AND action_information1 = 'PYSEWTCA'
2880 AND action_information2 = 'WTC_PERSON4';
2881
2882 CURSOR csr_wtc_get_group (
2883 csr_v_pa_id pay_action_information.action_context_id%TYPE
2884 )
2885 IS
2886 SELECT action_information28 GROUP_ID
2887 FROM pay_action_information
2888 WHERE action_context_type = 'PA'
2889 AND action_context_id = csr_v_pa_id
2890 AND action_information_category = 'EMEA REPORT INFORMATION'
2891 AND action_information1 = 'PYSEWTCA'
2892 AND action_information2 = 'WTC_PERSON5'
2893 GROUP BY action_information28;
2894
2895 CURSOR csr_wtc_get_month_value (
2896 csr_v_pa_id pay_action_information.action_context_id%TYPE
2897 ,csr_v_group_id pay_action_information.action_information28%TYPE
2898 )
2899 IS
2900 SELECT ROWNUM
2901 ,action_information3 jan_days
2902 ,action_information4 feb_days
2903 ,action_information5 mar_days
2904 ,action_information6 apr_days
2905 ,action_information7 may_days
2906 ,action_information8 jun_days
2907 ,action_information9 jul_days
2908 ,action_information10 aug_days
2909 ,action_information11 sep_days
2910 ,action_information12 oct_days
2911 ,action_information13 nov_days
2912 ,action_information14 dec_days
2913 ,action_information15 YEAR
2914 ,action_information16 jan_hours
2915 ,action_information17 feb_hours
2916 ,action_information18 mar_hours
2917 ,action_information19 apr_hours
2918 ,action_information20 may_hours
2919 ,action_information21 jun_hours
2920 ,action_information22 jul_hours
2921 ,action_information23 aug_hours
2922 ,action_information24 sep_hours
2923 ,action_information25 oct_hours
2924 ,action_information26 nov_hours
2925 ,action_information27 dec_hours
2926 FROM pay_action_information
2927 WHERE action_context_type = 'PA'
2928 AND action_context_id = csr_v_pa_id
2929 AND action_information_category = 'EMEA REPORT INFORMATION'
2930 AND action_information1 = 'PYSEWTCA'
2931 AND action_information2 = 'WTC_PERSON5'
2932 AND action_information28 = csr_v_group_id;
2933
2934 CURSOR csr_report_details (
2935 csr_v_pa_id pay_action_information.action_context_id%TYPE
2936 )
2937 IS
2938 SELECT action_information2 rpt_person_id
2939 ,action_information3 rpt_assignment_id
2940 ,action_information4 rpt_still_employed
2941 ,action_information5 rpt_business_group_id
2942 FROM pay_action_information
2943 WHERE action_context_type = 'PA'
2944 AND action_context_id = csr_v_pa_id
2945 AND action_information_category = 'EMEA REPORT DETAILS'
2946 AND action_information1 = 'PYSEWTCA';
2947
2948 lr_report_details csr_report_details%ROWTYPE;
2949 lr_wtc_person1 csr_wtc_person1%ROWTYPE;
2950 lr_wtc_person2 csr_wtc_person2%ROWTYPE;
2951 lr_wtc_person3 csr_wtc_person3%ROWTYPE;
2952 lr_wtc_person4 csr_wtc_person4%ROWTYPE;
2953 lr_wtc_get_group csr_wtc_get_group%ROWTYPE;
2954 lr_wtc_get_month_value csr_wtc_get_month_value%ROWTYPE;
2955
2956 CURSOR csr_all_employees_under_le (
2957 csr_v_pa_id pay_action_information.action_information3%TYPE
2958 ,csr_v_le_id pay_action_information.action_information15%TYPE
2959 )
2960 IS
2961 SELECT *
2962 FROM pay_action_information
2963 WHERE action_context_type = 'AAP'
2964 AND action_information_category = 'EMEA REPORT INFORMATION'
2965 AND action_information1 = 'PYSEWTCR'
2966 AND action_information3 = csr_v_pa_id
2967 AND action_information2 = 'PER'
2968 AND action_information15 = csr_v_le_id
2969 ORDER BY action_information30;
2970
2971
2972
2973
2974
2975 -- *****************************************************************************
2976 -- Add Individual Months Overtime and Addl/Supplemtary hours
2977 -- *****************************************************************************
2978 CURSOR csr_wtc_get_ovr_addl_val (
2979 csr_v_pa_id pay_action_information.action_context_id%TYPE
2980 ,csr_v_group_id pay_action_information.action_information28%TYPE
2981 )
2982 IS
2983 SELECT ROWNUM
2984 ,action_information3 jan_addl_time
2985 ,action_information4 feb_addl_time
2986 ,action_information5 mar_addl_time
2987 ,action_information6 apr_addl_time
2988 ,action_information7 may_addl_time
2989 ,action_information8 jun_addl_time
2990 ,action_information9 jul_addl_time
2991 ,action_information10 aug_addl_time
2992 ,action_information11 sep_addl_time
2993 ,action_information12 oct_addl_time
2994 ,action_information13 nov_addl_time
2995 ,action_information14 dec_addl_time
2996 ,action_information15 YEAR
2997 ,action_information16 jan_overtime
2998 ,action_information17 feb_overtime
2999 ,action_information18 mar_overtime
3000 ,action_information19 apr_overtime
3001 ,action_information20 may_overtime
3002 ,action_information21 jun_overtime
3003 ,action_information22 jul_overtime
3004 ,action_information23 aug_overtime
3005 ,action_information24 sep_overtime
3006 ,action_information25 oct_overtime
3007 ,action_information26 nov_overtime
3008 ,action_information27 dec_overtime
3009 FROM pay_action_information
3010 WHERE action_context_type = 'PA'
3011 AND action_context_id = csr_v_pa_id
3012 AND action_information_category = 'EMEA REPORT INFORMATION'
3013 AND action_information1 = 'PYSEWTCA'
3014 AND action_information2 = 'WTC_PERSON6'
3015 AND action_information28 = csr_v_group_id;
3016
3017
3018
3019
3020 -- *****************************************************************************
3021 -- Add Individual Months Absence hours
3022 -- *****************************************************************************
3023 CURSOR csr_wtc_get_absence_val (
3024 csr_v_pa_id pay_action_information.action_context_id%TYPE
3025 ,csr_v_group_id pay_action_information.action_information28%TYPE
3026 )
3027 IS
3028 SELECT ROWNUM
3029 ,action_information3 jan_absence_time
3030 ,action_information4 feb_absence_time
3031 ,action_information5 mar_absence_time
3032 ,action_information6 apr_absence_time
3033 ,action_information7 may_absence_time
3034 ,action_information8 jun_absence_time
3035 ,action_information9 jul_absence_time
3036 ,action_information10 aug_absence_time
3037 ,action_information11 sep_absence_time
3038 ,action_information12 oct_absence_time
3039 ,action_information13 nov_absence_time
3040 ,action_information14 dec_absence_time
3041 ,action_information15 YEAR
3042 FROM pay_action_information
3043 WHERE action_context_type = 'PA'
3044 AND action_context_id = csr_v_pa_id
3045 AND action_information_category = 'EMEA REPORT INFORMATION'
3046 AND action_information1 = 'PYSEWTCA'
3047 AND action_information2 = 'WTC_PERSON7'
3048 AND action_information28 = csr_v_group_id;
3049
3050 /* End of declaration*/
3051 /* Proc to Add the tag value and Name */
3052 PROCEDURE add_tag_value (p_tag_name IN VARCHAR2, p_tag_value IN VARCHAR2)
3053 IS
3054 BEGIN
3055 gplsqltable (l_counter).tagname := p_tag_name;
3056 gplsqltable (l_counter).tagvalue := p_tag_value;
3057 l_counter := l_counter + 1;
3058 END add_tag_value;
3059 /* End of Proc to Add the tag value and Name */
3060 /* Start of GET_HPD_XML */
3061 BEGIN
3062 IF p_payroll_action_id IS NULL
3063 THEN
3064 BEGIN
3065 SELECT payroll_action_id
3066 INTO l_payroll_action_id
3067 FROM pay_payroll_actions ppa
3068 ,fnd_conc_req_summary_v fcrs
3069 ,fnd_conc_req_summary_v fcrs1
3070 WHERE fcrs.request_id = fnd_global.conc_request_id
3071 AND fcrs.priority_request_id = fcrs1.priority_request_id
3072 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
3073 AND ppa.request_id = fcrs1.request_id;
3074 EXCEPTION
3075 WHEN OTHERS
3076 THEN
3077 NULL;
3078 END;
3079 ELSE
3080 l_payroll_action_id := p_payroll_action_id;
3081 fnd_file.put_line (fnd_file.LOG, 'Entered Reporting');
3082 fnd_file.put_line (fnd_file.LOG
3083 , 'p_payroll_action_id ' || p_payroll_action_id
3084 );
3085
3086 /* Structure of Xml should look like this
3087 <LE>
3088 <DETAILS>
3089 </DETAILS>
3090 <EMPLOYEES>
3091 <PERSON>
3092 </PERSON>
3093 </EMPLOYEES>
3094 </LE>
3095 */
3096
3097 OPEN csr_report_details (l_payroll_action_id);
3098 FETCH csr_report_details
3099 INTO lr_report_details;
3100
3101 CLOSE csr_report_details;
3102
3103 --logger ('After', 'csr_REPORT_DETAILS');
3104 --logger ('lr_report_details.RPT_PERSON_ID' ,lr_report_details.rpt_person_id );
3105 --logger ('lr_report_details.RPT_ASSIGNMENT_ID' ,lr_report_details.rpt_assignment_id );
3106 --logger ('lr_report_details.RPT_STILL_EMPLOYED' ,lr_report_details.rpt_still_employed );
3107 --logger ('lr_report_details.RPT_BUSINESS_GROUP_ID' ,lr_report_details.rpt_business_group_id );
3108 add_tag_value ('PERSON', 'PERSON');
3109
3110 OPEN csr_wtc_person1 (l_payroll_action_id);
3111
3112 FETCH csr_wtc_person1
3113 INTO lr_wtc_person1;
3114
3115 CLOSE csr_wtc_person1;
3116
3117 OPEN csr_wtc_person2 (l_payroll_action_id);
3118
3119 FETCH csr_wtc_person2
3120 INTO lr_wtc_person2;
3121
3122 CLOSE csr_wtc_person2;
3123
3124 OPEN csr_wtc_person3 (l_payroll_action_id);
3125
3126 FETCH csr_wtc_person3
3127 INTO lr_wtc_person3;
3128
3129 CLOSE csr_wtc_person3;
3130
3131 fnd_file.put_line (fnd_file.LOG, 'lr_wtc_person1.person_number'||lr_wtc_person1.person_number);
3132 ---------------------------------------------------------------------------------------------------------------
3133 --New Format of Person Number (of Ten Digits)
3134 ---------------------------------------------------------------------------------------------------------------
3135 --add_tag_value ('PERSON_NUMBER', lr_wtc_person1.person_number);
3136
3137 get_digit_breakup(replace(nvl(lr_wtc_person1.person_number,0),'-',''),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3138 gplsqltable (l_counter).tagname := 'PN1';
3139 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3140 l_counter := l_counter
3141 + 1;
3142 gplsqltable (l_counter).tagname := 'PN2';
3143 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3144 l_counter := l_counter
3145 + 1;
3146 gplsqltable (l_counter).tagname := 'PN3';
3147 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3148 l_counter := l_counter
3149 + 1;
3150 gplsqltable (l_counter).tagname := 'PN4';
3151 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3152 l_counter := l_counter
3153 + 1;
3154 gplsqltable (l_counter).tagname := 'PN5';
3155 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3156 l_counter := l_counter
3157 + 1;
3158 gplsqltable (l_counter).tagname := 'PN6';
3159 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3160 l_counter := l_counter
3161 + 1;
3162 gplsqltable (l_counter).tagname := 'PN7';
3163 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3164 l_counter := l_counter
3165 + 1;
3166 gplsqltable (l_counter).tagname := 'PN8';
3167 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3168 l_counter := l_counter
3169 + 1;
3170 gplsqltable (l_counter).tagname := 'PN9';
3171 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3172 l_counter := l_counter
3173 + 1;
3174 gplsqltable (l_counter).tagname := 'PN10';
3175 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3176 l_counter := l_counter
3177 + 1;
3178 ------------------------------------------------------------------------------------------------------------------
3179
3180 add_tag_value ('EMP_LAST_NAME', lr_wtc_person1.emp_last_name);
3181 add_tag_value ('EMP_FIRST_NAME', lr_wtc_person1.emp_first_name);
3182
3183 fnd_file.put_line (fnd_file.LOG, 'lr_wtc_person1.emp_first_name'||lr_wtc_person1.emp_first_name);
3184 fnd_file.put_line (fnd_file.LOG, 'l_digit10'||l_digit10);
3185
3186 ---------------------------------------------------------------------------------------------------------------
3187 -- Change the date format for hired_from (YYYYMMDD)
3188 ----------------------------------------------------------------------------------------------------------------
3189 --add_tag_value ('HIRED_FROM', lr_wtc_person1.hired_from);
3190
3191 get_digit_breakup(nvl(lr_wtc_person1.hired_from,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3192 gplsqltable (l_counter).tagname := 'HF1';
3193 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3194 l_counter := l_counter
3195 + 1;
3196 gplsqltable (l_counter).tagname := 'HF2';
3197 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3198 l_counter := l_counter
3199 + 1;
3200 gplsqltable (l_counter).tagname := 'HF3';
3201 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3202 l_counter := l_counter
3203 + 1;
3204 gplsqltable (l_counter).tagname := 'HF4';
3205 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3206 l_counter := l_counter
3207 + 1;
3208 gplsqltable (l_counter).tagname := 'HF5';
3209 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3210 l_counter := l_counter
3211 + 1;
3212 gplsqltable (l_counter).tagname := 'HF6';
3213 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3214 l_counter := l_counter
3215 + 1;
3216 gplsqltable (l_counter).tagname := 'HF7';
3217 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3218 l_counter := l_counter
3219 + 1;
3220 gplsqltable (l_counter).tagname := 'HF8';
3221 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3222 l_counter := l_counter
3223 + 1;
3224 gplsqltable (l_counter).tagname := 'HF9';
3225 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3226 l_counter := l_counter
3227 + 1;
3228 gplsqltable (l_counter).tagname := 'HF10';
3229 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3230 l_counter := l_counter
3231 + 1;
3232
3233 fnd_file.put_line (fnd_file.LOG, 'lr_wtc_person1.hired_to'||lr_wtc_person1.hired_to);
3234
3235 ---------------------------------------------------------------------------------------------------------------
3236 -- Change the date format for hired_to (YYYYMMDD)
3237 ----------------------------------------------------------------------------------------------------------------
3238 -- add_tag_value ('HIRED_TO', lr_wtc_person1.hired_to);
3239
3240 get_digit_breakup(nvl(lr_wtc_person1.hired_to,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3241 gplsqltable (l_counter).tagname := 'HT1';
3242 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3243 l_counter := l_counter
3244 + 1;
3245 gplsqltable (l_counter).tagname := 'HT2';
3246 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3247 l_counter := l_counter
3248 + 1;
3249 gplsqltable (l_counter).tagname := 'HT3';
3250 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3251 l_counter := l_counter
3252 + 1;
3253 gplsqltable (l_counter).tagname := 'HT4';
3254 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3255 l_counter := l_counter
3256 + 1;
3257 gplsqltable (l_counter).tagname := 'HT5';
3258 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3259 l_counter := l_counter
3260 + 1;
3261 gplsqltable (l_counter).tagname := 'HT6';
3262 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3263 l_counter := l_counter
3264 + 1;
3265 gplsqltable (l_counter).tagname := 'HT7';
3266 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3267 l_counter := l_counter
3268 + 1;
3269 gplsqltable (l_counter).tagname := 'HT8';
3270 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3271 l_counter := l_counter
3272 + 1;
3273 gplsqltable (l_counter).tagname := 'HT9';
3274 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3275 l_counter := l_counter
3276 + 1;
3277 gplsqltable (l_counter).tagname := 'HT10';
3278 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3279 l_counter := l_counter
3280 + 1;
3281
3282 ------------------------------------------------------------------------------------------------------------------
3283
3284
3285 add_tag_value ('STILL_EMPLOYED', lr_wtc_person1.still_employed);
3286
3287 add_tag_value ('WORK_TAKS', lr_wtc_person1.work_taks);
3288
3289
3290 ---------------------------------------------------------------------------------------------------------------
3291 -- Change the date format for absence_from (YYYYMMDD)
3292 ----------------------------------------------------------------------------------------------------------------
3293 -- add_tag_value ('ABSENCE_FROM', lr_wtc_person1.absence_from);
3294
3295 get_digit_breakup(nvl(lr_wtc_person1.absence_from,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3296 gplsqltable (l_counter).tagname := 'AF1';
3297 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3298 l_counter := l_counter
3299 + 1;
3300 gplsqltable (l_counter).tagname := 'AF2';
3301 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3302 l_counter := l_counter
3303 + 1;
3304 gplsqltable (l_counter).tagname := 'AF3';
3305 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3306 l_counter := l_counter
3307 + 1;
3308 gplsqltable (l_counter).tagname := 'AF4';
3309 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3310 l_counter := l_counter
3311 + 1;
3312 gplsqltable (l_counter).tagname := 'AF5';
3313 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3314 l_counter := l_counter
3315 + 1;
3316 gplsqltable (l_counter).tagname := 'AF6';
3317 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3318 l_counter := l_counter
3319 + 1;
3320 gplsqltable (l_counter).tagname := 'AF7';
3321 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3322 l_counter := l_counter
3323 + 1;
3324 gplsqltable (l_counter).tagname := 'AF8';
3325 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3326 l_counter := l_counter
3327 + 1;
3328 gplsqltable (l_counter).tagname := 'AF9';
3329 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3330 l_counter := l_counter
3331 + 1;
3332 gplsqltable (l_counter).tagname := 'AF10';
3333 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3334 l_counter := l_counter
3335 + 1;
3336
3337 ------------------------------------------------------------------------------------------------------------------
3338 -- Change the date format for absence_to (YYYYMMDD)
3339 ------------------------------------------------------------------------------------------------------------------
3340 -- add_tag_value ('ABSENCE_TO', lr_wtc_person1.absence_to);
3341
3342 get_digit_breakup(nvl(lr_wtc_person1.absence_to,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3343 gplsqltable (l_counter).tagname := 'AT1';
3344 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3345 l_counter := l_counter
3346 + 1;
3347 gplsqltable (l_counter).tagname := 'AT2';
3348 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3349 l_counter := l_counter
3350 + 1;
3351 gplsqltable (l_counter).tagname := 'AT3';
3352 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3353 l_counter := l_counter
3354 + 1;
3355 gplsqltable (l_counter).tagname := 'AT4';
3356 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3357 l_counter := l_counter
3358 + 1;
3359 gplsqltable (l_counter).tagname := 'AT5';
3360 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3361 l_counter := l_counter
3362 + 1;
3363 gplsqltable (l_counter).tagname := 'AT6';
3364 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3365 l_counter := l_counter
3366 + 1;
3367 gplsqltable (l_counter).tagname := 'AT7';
3368 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3369 l_counter := l_counter
3370 + 1;
3371 gplsqltable (l_counter).tagname := 'AT8';
3372 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3373 l_counter := l_counter
3374 + 1;
3375 gplsqltable (l_counter).tagname := 'AT9';
3376 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3377 l_counter := l_counter
3378 + 1;
3379 gplsqltable (l_counter).tagname := 'AT10';
3380 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3381 l_counter := l_counter
3382 + 1;
3383
3384 ------------------------------------------------------------------------------------------------------------------
3385
3386
3387 add_tag_value ('ABSENCE_PERCENTAGE',lr_wtc_person1.absence_percentage);
3388
3389 add_tag_value ('FORM_OF_EMPLOYMENT'
3390 ,lr_wtc_person1.form_of_employment
3391 );
3392
3393
3394 IF lr_wtc_person1.form_of_employment <> 'FR' AND
3395 lr_wtc_person1.form_of_employment <> 'PR' AND
3396 lr_wtc_person1.form_of_employment <> 'SE_PE' AND
3397 lr_wtc_person1.form_of_employment <>'INTMT'
3398 THEN
3399 time_limited_check_box := 'Y';
3400 ELSE
3401 time_limited_check_box := 'N';
3402
3403 END IF ;
3404
3405
3406 fnd_file.put_line (fnd_file.LOG, 'time_limited_check_box'||time_limited_check_box);
3407 fnd_file.put_line (fnd_file.LOG, ' lr_wtc_person1.employment_end_date'|| lr_wtc_person1.employment_end_date);
3408
3409
3410 add_tag_value ('TIME_LIMITED_CHECK_BOX', time_limited_check_box);
3411
3412
3413
3414
3415 ------------------------------------------------------------------------------------------------------------------
3416 -- Change the date format for employment_end_date (YYYYMMDD)
3417 ------------------------------------------------------------------------------------------------------------------
3418 -- add_tag_value ('EMPLOYMENT_END_DATE', lr_wtc_person1.employment_end_date);
3419
3420 get_digit_breakup(nvl(lr_wtc_person1.employment_end_date,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3421 gplsqltable (l_counter).tagname := 'EED1';
3422 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3423 l_counter := l_counter
3424 + 1;
3425 gplsqltable (l_counter).tagname := 'EED2';
3426 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3427 l_counter := l_counter
3428 + 1;
3429 gplsqltable (l_counter).tagname := 'EED3';
3430 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3431 l_counter := l_counter
3432 + 1;
3433 gplsqltable (l_counter).tagname := 'EED4';
3434 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3435 l_counter := l_counter
3436 + 1;
3437 gplsqltable (l_counter).tagname := 'EED5';
3438 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3439 l_counter := l_counter
3440 + 1;
3441 gplsqltable (l_counter).tagname := 'EED6';
3442 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3443 l_counter := l_counter
3444 + 1;
3445 gplsqltable (l_counter).tagname := 'EED7';
3446 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3447 l_counter := l_counter
3448 + 1;
3449 gplsqltable (l_counter).tagname := 'EED8';
3450 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3451 l_counter := l_counter
3452 + 1;
3453 gplsqltable (l_counter).tagname := 'EED9';
3454 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3455 l_counter := l_counter
3456 + 1;
3457 gplsqltable (l_counter).tagname := 'EED10';
3458 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3459 l_counter := l_counter
3460 + 1;
3461
3462 ------------------------------------------------------------------------------------------------------------------
3463
3464
3465
3466 add_tag_value ('ASSIGN_FULL_TIME', lr_wtc_person3.assign_full_time);
3467 add_tag_value ('ASSIGN_HOURS_WEEK', lr_wtc_person3.assign_hours_week);
3468 add_tag_value ('ASSIGN_FREQUENCY', lr_wtc_person3.assign_frequency);
3469 add_tag_value ('ASSIGN_PART_TIME', lr_wtc_person3.assign_part_time);
3470 add_tag_value ('ASSIGN_WORKING_PERCENTAGE'
3471 ,lr_wtc_person3.assign_working_percentage
3472 );
3473 add_tag_value ('ASSIGN_VARIOUS_WORK_TIME'
3474 ,lr_wtc_person3.assign_various_work_time
3475 );
3476
3477
3478 add_tag_value ('EMPLOYED_TEMP_AGENCY'
3479 ,lr_wtc_person1.employed_temp_agency
3480 );
3481 add_tag_value ('EMPLOYED_TEMP_WORK'
3482 ,lr_wtc_person1.employed_temp_work
3483 );
3484
3485
3486
3487
3488
3489
3490
3491
3492 ------------------------------------------------------------------------------------------------------------------
3493 -- Change the date format for notification_date (YYYYMMDD)
3494 ------------------------------------------------------------------------------------------------------------------
3495 -- add_tag_value ('NOTIFICATION_DATE', lr_wtc_person1.notification_date);
3496
3497 get_digit_breakup(nvl(lr_wtc_person1.notification_date,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3498 gplsqltable (l_counter).tagname := 'ND1';
3499 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3500 l_counter := l_counter
3501 + 1;
3502 gplsqltable (l_counter).tagname := 'ND2';
3503 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3504 l_counter := l_counter
3505 + 1;
3506 gplsqltable (l_counter).tagname := 'ND3';
3507 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3508 l_counter := l_counter
3509 + 1;
3510 gplsqltable (l_counter).tagname := 'ND4';
3511 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3512 l_counter := l_counter
3513 + 1;
3514 gplsqltable (l_counter).tagname := 'ND5';
3515 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3516 l_counter := l_counter
3517 + 1;
3518 gplsqltable (l_counter).tagname := 'ND6';
3519 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3520 l_counter := l_counter
3521 + 1;
3522 gplsqltable (l_counter).tagname := 'ND7';
3523 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3524 l_counter := l_counter
3525 + 1;
3526 gplsqltable (l_counter).tagname := 'ND8';
3527 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3528 l_counter := l_counter
3529 + 1;
3530 gplsqltable (l_counter).tagname := 'ND9';
3531 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3532 l_counter := l_counter
3533 + 1;
3534 gplsqltable (l_counter).tagname := 'ND10';
3535 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3536 l_counter := l_counter
3537 + 1;
3538
3539 ------------------------------------------------------------------------------------------------------------------
3540
3541
3542 add_tag_value ('EMPLOYEES_REQUEST', lr_wtc_person1.employees_request);
3543
3544 add_tag_value ('TERMINATION_REASON',lr_wtc_person1.termination_reason);
3545
3546 add_tag_value ('REASON', lr_wtc_person1.reason);
3547
3548 add_tag_value ('AGG_OF_COMPENSATION_SIGNED', lr_wtc_person2.aggrmnt_of_compn_signed); -- EOY 2008
3549
3550
3551
3552
3553 --logger ('PERSON_NUMBER', lr_wtc_person1.person_number);
3554 --logger ('EMP_LAST_NAME', lr_wtc_person1.emp_last_name);
3555 --logger ('EMP_FIRST_NAME', lr_wtc_person1.emp_first_name);
3556 --logger ('HIRED_FROM', lr_wtc_person1.hired_from);
3557 --logger ('HIRED_TO', lr_wtc_person1.hired_to);
3558 --logger ('STILL_EMPLOYED', lr_wtc_person1.still_employed);
3559 --logger ('ABSENCE_FROM', lr_wtc_person1.absence_from);
3560 --logger ('ABSENCE_TO', lr_wtc_person1.absence_to);
3561 --logger ('FORM_OF_EMPLOYMENT', lr_wtc_person1.form_of_employment);
3562 --logger ('WORK_TAKS', lr_wtc_person1.work_taks);
3563 --logger ('EMPLOYED_TEMP_AGENCY', lr_wtc_person1.employed_temp_agency);
3564 --logger ('EMPLOYED_TEMP_WORK', lr_wtc_person1.employed_temp_work);
3565 --logger ('REASON', lr_wtc_person1.reason);
3566 --logger ('NOTIFICATION_DATE', lr_wtc_person1.notification_date);
3567 --logger ('EMPLOYEES_REQUEST', lr_wtc_person1.employees_request);
3568 --logger ('TERMINATION_REASON',lr_wtc_person1.termination_reason);
3569 --logger ('Before csr_wtc_person2', l_payroll_action_id);
3570
3571
3572 --logger ('after csr_wtc_person2', l_payroll_action_id);
3573 add_tag_value ('CONTINUOUS_OFFER', lr_wtc_person2.continuous_offer);
3574 add_tag_value ('PERMANENT_CHECK', lr_wtc_person2.permanent_check);
3575
3576 --------------------------------------------------------------------------------------------------------
3577 -- The code changes below are added w.r.t the change in the
3578 --format of Template from 2008
3579 --------------------------------------------------------------------------------------------------------
3580 IF lr_wtc_person2.permanent_check = 'Y'
3581 THEN
3582 continuous_offer_from := lr_wtc_person2.permanent_date_from;
3583 continuous_offer_to := lr_wtc_person2.permanent_date_to;
3584
3585 ELSIF lr_wtc_person2.time_limited_check = 'Y'
3586 THEN
3587 continuous_offer_from := lr_wtc_person2.time_limited_from ;
3588 continuous_offer_to := lr_wtc_person2.time_limited_to ;
3589 END IF;
3590
3591 IF (lr_wtc_person2.continuous_offer = 'Y') and (continuous_offer_to IS NULL)
3592 THEN
3593 until_further_notice := 'Y';
3594 ELSE
3595 until_further_notice := 'N';
3596 END IF;
3597
3598 add_tag_value ('UNTIL_FURTHER_NOTICE', until_further_notice);
3599 ------------------------------------------------------------------------------------------------------------------
3600 -- Change the date format for continuous_offer_from (YYYYMMDD)
3601 ------------------------------------------------------------------------------------------------------------------
3602
3603 get_digit_breakup(nvl(continuous_offer_from,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3604 gplsqltable (l_counter).tagname := 'COF1';
3605 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3606 l_counter := l_counter
3607 + 1;
3608 gplsqltable (l_counter).tagname := 'COF2';
3609 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3610 l_counter := l_counter
3611 + 1;
3612 gplsqltable (l_counter).tagname := 'COF3';
3613 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3614 l_counter := l_counter
3615 + 1;
3616 gplsqltable (l_counter).tagname := 'COF4';
3617 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3618 l_counter := l_counter
3619 + 1;
3620 gplsqltable (l_counter).tagname := 'COF5';
3621 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3622 l_counter := l_counter
3623 + 1;
3624 gplsqltable (l_counter).tagname := 'COF6';
3625 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3626 l_counter := l_counter
3627 + 1;
3628 gplsqltable (l_counter).tagname := 'COF7';
3629 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3630 l_counter := l_counter
3631 + 1;
3632 gplsqltable (l_counter).tagname := 'COF8';
3633 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3634 l_counter := l_counter
3635 + 1;
3636 gplsqltable (l_counter).tagname := 'COF9';
3637 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3638 l_counter := l_counter
3639 + 1;
3640 gplsqltable (l_counter).tagname := 'COF10';
3641 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3642 l_counter := l_counter
3643 + 1;
3644
3645 ------------------------------------------------------------------------------------------------------------------
3646 -- Change the date format for continuous_offer_to (YYYYMMDD)
3647 ------------------------------------------------------------------------------------------------------------------
3648
3649 get_digit_breakup(nvl(continuous_offer_to,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3650 gplsqltable (l_counter).tagname := 'COT1';
3651 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3652 l_counter := l_counter
3653 + 1;
3654 gplsqltable (l_counter).tagname := 'COT2';
3655 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3656 l_counter := l_counter
3657 + 1;
3658 gplsqltable (l_counter).tagname := 'COT3';
3659 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3660 l_counter := l_counter
3661 + 1;
3662 gplsqltable (l_counter).tagname := 'COT4';
3663 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3664 l_counter := l_counter
3665 + 1;
3666 gplsqltable (l_counter).tagname := 'COT5';
3667 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3668 l_counter := l_counter
3669 + 1;
3670 gplsqltable (l_counter).tagname := 'COT6';
3671 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3672 l_counter := l_counter
3673 + 1;
3674 gplsqltable (l_counter).tagname := 'COT7';
3675 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3676 l_counter := l_counter
3677 + 1;
3678 gplsqltable (l_counter).tagname := 'COT8';
3679 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3680 l_counter := l_counter
3681 + 1;
3682 gplsqltable (l_counter).tagname := 'COT9';
3683 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3684 l_counter := l_counter
3685 + 1;
3686 gplsqltable (l_counter).tagname := 'COT10';
3687 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3688 l_counter := l_counter
3689 + 1;
3690
3691 ------------------------------------------------------------------------------------------------------------------
3692 -- Change the date format for permanent_date_from (YYYYMMDD)
3693 ------------------------------------------------------------------------------------------------------------------
3694 -- add_tag_value ('PERMANENT_DATE', lr_wtc_person2.permanent_date_from);
3695
3696 get_digit_breakup(nvl(lr_wtc_person2.permanent_date_from,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3697 gplsqltable (l_counter).tagname := 'PDF1';
3698 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3699 l_counter := l_counter
3700 + 1;
3701 gplsqltable (l_counter).tagname := 'PDF2';
3702 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3703 l_counter := l_counter
3704 + 1;
3705 gplsqltable (l_counter).tagname := 'PDF3';
3706 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3707 l_counter := l_counter
3708 + 1;
3709 gplsqltable (l_counter).tagname := 'PDF4';
3710 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3711 l_counter := l_counter
3712 + 1;
3713 gplsqltable (l_counter).tagname := 'PDF5';
3714 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3715 l_counter := l_counter
3716 + 1;
3717 gplsqltable (l_counter).tagname := 'PDF6';
3718 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3719 l_counter := l_counter
3720 + 1;
3721 gplsqltable (l_counter).tagname := 'PDF7';
3722 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3723 l_counter := l_counter
3724 + 1;
3725 gplsqltable (l_counter).tagname := 'PDF8';
3726 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3727 l_counter := l_counter
3728 + 1;
3729 gplsqltable (l_counter).tagname := 'PDF9';
3730 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3731 l_counter := l_counter
3732 + 1;
3733 gplsqltable (l_counter).tagname := 'PDF10';
3734 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3735 l_counter := l_counter
3736 + 1;
3737
3738 ------------------------------------------------------------------------------------------------------------------
3739
3740
3741
3742 add_tag_value ('TIME_LIMITED_CHECK'
3743 ,lr_wtc_person2.time_limited_check
3744 );
3745
3746
3747
3748 ------------------------------------------------------------------------------------------------------------------
3749 -- Change the date format for time_limited_from (YYYYMMDD)
3750 ------------------------------------------------------------------------------------------------------------------
3751 -- add_tag_value ('TIME_LIMITED_FROM', lr_wtc_person2.time_limited_from);
3752
3753 get_digit_breakup(nvl(lr_wtc_person2.time_limited_from,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3754 gplsqltable (l_counter).tagname := 'TLF1';
3755 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3756 l_counter := l_counter
3757 + 1;
3758 gplsqltable (l_counter).tagname := 'TLF2';
3759 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3760 l_counter := l_counter
3761 + 1;
3762 gplsqltable (l_counter).tagname := 'TLF3';
3763 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3764 l_counter := l_counter
3765 + 1;
3766 gplsqltable (l_counter).tagname := 'TLF4';
3767 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3768 l_counter := l_counter
3769 + 1;
3770 gplsqltable (l_counter).tagname := 'TLF5';
3771 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3772 l_counter := l_counter
3773 + 1;
3774 gplsqltable (l_counter).tagname := 'TLF6';
3775 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3776 l_counter := l_counter
3777 + 1;
3778 gplsqltable (l_counter).tagname := 'TLF7';
3779 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3780 l_counter := l_counter
3781 + 1;
3782 gplsqltable (l_counter).tagname := 'TLF8';
3783 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3784 l_counter := l_counter
3785 + 1;
3786 gplsqltable (l_counter).tagname := 'TLF9';
3787 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3788 l_counter := l_counter
3789 + 1;
3790 gplsqltable (l_counter).tagname := 'TLF10';
3791 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3792 l_counter := l_counter
3793 + 1;
3794
3795 ------------------------------------------------------------------------------------------------------------------
3796 -- Change the date format for time_limited_to (YYYYMMDD)
3797 ------------------------------------------------------------------------------------------------------------------
3798 -- add_tag_value ('TIME_LIMITED_TO', lr_wtc_person2.time_limited_to);
3799
3800 get_digit_breakup(nvl(lr_wtc_person2.time_limited_to,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3801 gplsqltable (l_counter).tagname := 'TLT1';
3802 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3803 l_counter := l_counter
3804 + 1;
3805 gplsqltable (l_counter).tagname := 'TLT2';
3806 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3807 l_counter := l_counter
3808 + 1;
3809 gplsqltable (l_counter).tagname := 'TLT3';
3810 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3811 l_counter := l_counter
3812 + 1;
3813 gplsqltable (l_counter).tagname := 'TLT4';
3814 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3815 l_counter := l_counter
3816 + 1;
3817 gplsqltable (l_counter).tagname := 'TLT5';
3818 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3819 l_counter := l_counter
3820 + 1;
3821 gplsqltable (l_counter).tagname := 'TLT6';
3822 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3823 l_counter := l_counter
3824 + 1;
3825 gplsqltable (l_counter).tagname := 'TLT7';
3826 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3827 l_counter := l_counter
3828 + 1;
3829 gplsqltable (l_counter).tagname := 'TLT8';
3830 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3831 l_counter := l_counter
3832 + 1;
3833 gplsqltable (l_counter).tagname := 'TLT9';
3834 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3835 l_counter := l_counter
3836 + 1;
3837 gplsqltable (l_counter).tagname := 'TLT10';
3838 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3839 l_counter := l_counter
3840 + 1;
3841
3842 ------------------------------------------------------------------------------------------------------------------
3843
3844
3845 add_tag_value ('OTHER_CHECK', lr_wtc_person2.other_check);
3846 add_tag_value ('OTHER', lr_wtc_person2.other);
3847 add_tag_value ('FULL_TIME_CHECK', lr_wtc_person2.full_time_check);
3848 add_tag_value ('FULL_TIME', lr_wtc_person2.full_time);
3849 add_tag_value ('PART_TIME_CHECK', lr_wtc_person2.part_time_check);
3850 add_tag_value ('PART_TIME', lr_wtc_person2.part_time);
3851 add_tag_value ('WORKING_PERCENTAGE'
3852 ,lr_wtc_person2.working_percentage
3853 );
3854 add_tag_value ('VARIOUS_WORKING_TIME'
3855 ,lr_wtc_person2.various_working_time
3856 );
3857 add_tag_value ('OFFER_ACCEPTED', lr_wtc_person2.offer_accepted);
3858
3859
3860
3861 ------------------------------------------------------------------------------------------------------------------
3862 -- Change the date format for decline_date (YYYYMMDD)
3863 ------------------------------------------------------------------------------------------------------------------
3864 -- add_tag_value ('DECLINE_DATE', lr_wtc_person2.decline_date);
3865
3866 get_digit_breakup(nvl(lr_wtc_person2.decline_date,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3867 gplsqltable (l_counter).tagname := 'DD1';
3868 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3869 l_counter := l_counter
3870 + 1;
3871 gplsqltable (l_counter).tagname := 'DD2';
3872 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3873 l_counter := l_counter
3874 + 1;
3875 gplsqltable (l_counter).tagname := 'DD3';
3876 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3877 l_counter := l_counter
3878 + 1;
3879 gplsqltable (l_counter).tagname := 'DD4';
3880 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3881 l_counter := l_counter
3882 + 1;
3883 gplsqltable (l_counter).tagname := 'DD5';
3884 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3885 l_counter := l_counter
3886 + 1;
3887 gplsqltable (l_counter).tagname := 'DD6';
3888 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3889 l_counter := l_counter
3890 + 1;
3891 gplsqltable (l_counter).tagname := 'DD7';
3892 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3893 l_counter := l_counter
3894 + 1;
3895 gplsqltable (l_counter).tagname := 'DD8';
3896 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3897 l_counter := l_counter
3898 + 1;
3899 gplsqltable (l_counter).tagname := 'DD9';
3900 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3901 l_counter := l_counter
3902 + 1;
3903 gplsqltable (l_counter).tagname := 'DD10';
3904 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3905 l_counter := l_counter
3906 + 1;
3907
3908 ------------------------------------------------------------------------------------------------------------------
3909 -- Change the date format for time_worked_from (YYYYMMDD)
3910 ------------------------------------------------------------------------------------------------------------------
3911 -- add_tag_value ('TIME_WORKED_FROM', lr_wtc_person2.time_worked_from);
3912
3913 get_digit_breakup(nvl(lr_wtc_person2.time_worked_from,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3914 gplsqltable (l_counter).tagname := 'TWF1';
3915 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3916 l_counter := l_counter
3917 + 1;
3918 gplsqltable (l_counter).tagname := 'TWF2';
3919 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3920 l_counter := l_counter
3921 + 1;
3922 gplsqltable (l_counter).tagname := 'TWF3';
3923 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3924 l_counter := l_counter
3925 + 1;
3926 gplsqltable (l_counter).tagname := 'TWF4';
3927 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3928 l_counter := l_counter
3929 + 1;
3930 gplsqltable (l_counter).tagname := 'TWF5';
3931 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3932 l_counter := l_counter
3933 + 1;
3934 gplsqltable (l_counter).tagname := 'TWF6';
3935 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3936 l_counter := l_counter
3937 + 1;
3938 gplsqltable (l_counter).tagname := 'TWF7';
3939 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3940 l_counter := l_counter
3941 + 1;
3942 gplsqltable (l_counter).tagname := 'TWF8';
3943 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3944 l_counter := l_counter
3945 + 1;
3946 gplsqltable (l_counter).tagname := 'TWF9';
3947 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3948 l_counter := l_counter
3949 + 1;
3950 gplsqltable (l_counter).tagname := 'TWF10';
3951 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3952 l_counter := l_counter
3953 + 1;
3954
3955 ------------------------------------------------------------------------------------------------------------------
3956 -- Change the date format for time_worked_to (YYYYMMDD)
3957 ------------------------------------------------------------------------------------------------------------------
3958 --add_tag_value ('TIME_WORKED_TO', lr_wtc_person2.time_worked_to);
3959
3960 get_digit_breakup(nvl(lr_wtc_person2.time_worked_to,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
3961 gplsqltable (l_counter).tagname := 'TWT1';
3962 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
3963 l_counter := l_counter
3964 + 1;
3965 gplsqltable (l_counter).tagname := 'TWT2';
3966 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
3967 l_counter := l_counter
3968 + 1;
3969 gplsqltable (l_counter).tagname := 'TWT3';
3970 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
3971 l_counter := l_counter
3972 + 1;
3973 gplsqltable (l_counter).tagname := 'TWT4';
3974 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
3975 l_counter := l_counter
3976 + 1;
3977 gplsqltable (l_counter).tagname := 'TWT5';
3978 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
3979 l_counter := l_counter
3980 + 1;
3981 gplsqltable (l_counter).tagname := 'TWT6';
3982 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
3983 l_counter := l_counter
3984 + 1;
3985 gplsqltable (l_counter).tagname := 'TWT7';
3986 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
3987 l_counter := l_counter
3988 + 1;
3989 gplsqltable (l_counter).tagname := 'TWT8';
3990 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
3991 l_counter := l_counter
3992 + 1;
3993 gplsqltable (l_counter).tagname := 'TWT9';
3994 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
3995 l_counter := l_counter
3996 + 1;
3997 gplsqltable (l_counter).tagname := 'TWT10';
3998 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
3999 l_counter := l_counter
4000 + 1;
4001
4002 ------------------------------------------------------------------------------------------------------------------
4003
4004
4005
4006
4007 add_tag_value ('TOTAL_WORKED_HOURS'
4008 ,lr_wtc_person2.total_worked_hours
4009 );
4010 add_tag_value ('PAID_SICK_LEAVE_DAYS'
4011 ,lr_wtc_person2.paid_sick_leave_days
4012 );
4013 add_tag_value ('TEACHING_LOAD', lr_wtc_person2.teaching_load);
4014
4015 add_tag_value ('TEACHING_LOAD_CHECK_BOX', lr_wtc_person2.teaching_load_check_box);
4016
4017 --logger ('Before csr_wtc_person3', l_payroll_action_id);
4018
4019
4020
4021
4022
4023 ------------------------------------------------------------------------------------------------------------------
4024 -- Change the date format for salary_year (YYYYMMDD)
4025 ------------------------------------------------------------------------------------------------------------------
4026 -- add_tag_value ('SALARY_YEAR', lr_wtc_person3.salary_year);
4027
4028 get_digit_breakup(nvl(lr_wtc_person3.salary_year,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
4029 gplsqltable (l_counter).tagname := 'SY1';
4030 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
4031 l_counter := l_counter
4032 + 1;
4033 gplsqltable (l_counter).tagname := 'SY2';
4034 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
4035 l_counter := l_counter
4036 + 1;
4037 gplsqltable (l_counter).tagname := 'SY3';
4038 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
4039 l_counter := l_counter
4040 + 1;
4041 gplsqltable (l_counter).tagname := 'SY4';
4042 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
4043 l_counter := l_counter
4044 + 1;
4045
4046 ------------------------------------------------------------------------------------------------------------------
4047
4048
4049
4050 add_tag_value ('ASSIGN_SALARY_PAID_OUT'
4051 ,lr_wtc_person3.assign_salary_paid_out
4052 );
4053 add_tag_value ('SALARY_AMOUNT', lr_wtc_person3.salary_amount);
4054 add_tag_value ('SCHOOL_HOLIDAY_PAY_AMOUNT'
4055 ,lr_wtc_person3.school_holiday_pay_amount
4056 );
4057 add_tag_value ('SCHOOL_HOLIDAY_PAY_BOX'
4058 ,lr_wtc_person3.school_holiday_pay_box
4059 );
4060 add_tag_value ('NO_OF_PAID_HOLIDAY_DAYS'
4061 ,lr_wtc_person3.no_of_paid_holiday_days
4062 );
4063 add_tag_value ('EMP_WITH_HOLIDAY_PAY'
4064 ,lr_wtc_person3.emp_with_holiday_pay
4065 );
4066 add_tag_value ('PAID_DAYS_OFF_DUTY_TIME'
4067 ,lr_wtc_person3.paid_days_off_duty_time
4068 );
4069 add_tag_value ('HOLIDAY_PAY_AMOUNT'
4070 ,lr_wtc_person3.holiday_pay_amount); -- EOY 2008
4071
4072 add_tag_value ('EMPLOYED_EDUCATIONAL_ASSOC'
4073 ,lr_wtc_person3.employed_educational_assoc
4074 );
4075
4076 OPEN csr_wtc_person4 (l_payroll_action_id);
4077
4078 FETCH csr_wtc_person4
4079 INTO lr_wtc_person4;
4080
4081 CLOSE csr_wtc_person4;
4082
4083 add_tag_value ('HOLIDAY_DUTY', lr_wtc_person4.holiday_duty);
4084 add_tag_value ('LAY_OFF_PERIOD_PAID_DAYS'
4085 ,lr_wtc_person4.lay_off_period_paid_days
4086 );
4087 add_tag_value ('HOLIDAY_LAID_OFF', lr_wtc_person4.holiday_laid_off);
4088
4089
4090 ------------------------------------------------------------------------------------------------------------------
4091 -- Change the date format for lay_off_from (YYYYMMDD)
4092 ------------------------------------------------------------------------------------------------------------------
4093 --add_tag_value ('LAY_OFF_FROM', lr_wtc_person4.lay_off_from);
4094
4095 get_digit_breakup(nvl(lr_wtc_person4.lay_off_from,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
4096 gplsqltable (l_counter).tagname := 'LOF1';
4097 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
4098 l_counter := l_counter
4099 + 1;
4100 gplsqltable (l_counter).tagname := 'LOF2';
4101 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
4102 l_counter := l_counter
4103 + 1;
4104 gplsqltable (l_counter).tagname := 'LOF3';
4105 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
4106 l_counter := l_counter
4107 + 1;
4108 gplsqltable (l_counter).tagname := 'LOF4';
4109 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
4110 l_counter := l_counter
4111 + 1;
4112 gplsqltable (l_counter).tagname := 'LOF5';
4113 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
4114 l_counter := l_counter
4115 + 1;
4116 gplsqltable (l_counter).tagname := 'LOF6';
4117 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
4118 l_counter := l_counter
4119 + 1;
4120 gplsqltable (l_counter).tagname := 'LOF7';
4121 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
4122 l_counter := l_counter
4123 + 1;
4124 gplsqltable (l_counter).tagname := 'LOF8';
4125 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
4126 l_counter := l_counter
4127 + 1;
4128 gplsqltable (l_counter).tagname := 'LOF9';
4129 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
4130 l_counter := l_counter
4131 + 1;
4132 gplsqltable (l_counter).tagname := 'LOF10';
4133 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
4134 l_counter := l_counter
4135 + 1;
4136
4137
4138 ------------------------------------------------------------------------------------------------------------------
4139 -- Change the date format for lay_off_to (YYYYMMDD)
4140 ------------------------------------------------------------------------------------------------------------------
4141 --add_tag_value ('LAY_OFF_TO', lr_wtc_person4.lay_off_to);
4142
4143 get_digit_breakup(nvl(lr_wtc_person4.lay_off_to,0),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
4144 gplsqltable (l_counter).tagname := 'LOT1';
4145 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit1);
4146 l_counter := l_counter
4147 + 1;
4148 gplsqltable (l_counter).tagname := 'LOT2';
4149 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit2);
4150 l_counter := l_counter
4151 + 1;
4152 gplsqltable (l_counter).tagname := 'LOT3';
4153 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit3);
4154 l_counter := l_counter
4155 + 1;
4156 gplsqltable (l_counter).tagname := 'LOT4';
4157 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit4);
4158 l_counter := l_counter
4159 + 1;
4160 gplsqltable (l_counter).tagname := 'LOT5';
4161 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit5);
4162 l_counter := l_counter
4163 + 1;
4164 gplsqltable (l_counter).tagname := 'LOT6';
4165 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit6);
4166 l_counter := l_counter
4167 + 1;
4168 gplsqltable (l_counter).tagname := 'LOT7';
4169 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit7);
4170 l_counter := l_counter
4171 + 1;
4172 gplsqltable (l_counter).tagname := 'LOT8';
4173 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit8);
4174 l_counter := l_counter
4175 + 1;
4176 gplsqltable (l_counter).tagname := 'LOT9';
4177 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit9);
4178 l_counter := l_counter
4179 + 1;
4180 gplsqltable (l_counter).tagname := 'LOT10';
4181 gplsqltable (l_counter).tagvalue := TO_CHAR (l_digit10);
4182 l_counter := l_counter
4183 + 1;
4184 ------------------------------------------------------------------------------------------------------------------
4185
4186
4187 add_tag_value ('OTHER_INFORMATION', lr_wtc_person4.other_information);
4188
4189 add_tag_value ('HOURLY_PAY_VARIABLE', lr_wtc_person4.hourly_pay_variable); --EOY 2008
4190 add_tag_value ('HOURLY_OVERTIME_RATE', lr_wtc_person4.hourly_overtime_rate); --EOY 2008
4191 add_tag_value ('HOURLY_ADDL_SUP_TIME', lr_wtc_person4.hourly_addl_suppl_time); --EOY 2008
4192 add_tag_value ('OTHER_TAX_COMPENSATION', lr_wtc_person4.other_taxable_compensation); --EOY 2008
4193
4194 add_tag_value ('LEGAL_EMPLOYER_NAME'
4195 ,lr_wtc_person4.legal_employer_name
4196 );
4197 add_tag_value ('ORG_NUMBER', lr_wtc_person4.org_number);
4198 add_tag_value ('PHONE_NUMBER', lr_wtc_person4.phone_number);
4199 add_tag_value ('LE_ADDRESS', 'LE_ADDRESS');
4200 add_tag_value ('LOCATION_CODE', lr_wtc_person4.location_code);
4201 add_tag_value ('ADDRESS_LINE_1', lr_wtc_person4.address_line_1);
4202 add_tag_value ('ADDRESS_LINE_2', lr_wtc_person4.address_line_2);
4203 add_tag_value ('ADDRESS_LINE_3', lr_wtc_person4.address_line_3);
4204 add_tag_value ('POSTAL_CODE', lr_wtc_person4.postal_code);
4205 add_tag_value ('TOWN_OR_CITY', lr_wtc_person4.town_or_city);
4206 add_tag_value ('REGION_1', lr_wtc_person4.region_1);
4207 add_tag_value ('REGION_2', lr_wtc_person4.region_2);
4208 add_tag_value ('TERRITORY_SHORT_NAME'
4209 ,lr_wtc_person4.territory_short_name
4210 );
4211 add_tag_value ('LE_ADDRESS', 'LE_ADDRESS_END');
4212
4213 -- *****************************************************************************
4214 -- For each group we get two record
4215 -- for the first group we put differnt tag from all other groups
4216 -- *****************************************************************************
4217 FOR rec_get_group IN csr_wtc_get_group (l_payroll_action_id)
4218 LOOP
4219 -- *****************************************************************************
4220 IF rec_get_group.GROUP_ID = '1'
4221 THEN
4222 add_tag_value ('REPORTINGYEAR', 'REPORTINGYEAR');
4223 ELSE
4224 add_tag_value ('OTHERYEAR', 'OTHERYEAR');
4225 END IF;
4226
4227 -- *****************************************************************************
4228 -- Add code to put individual month value
4229 -- for each group id we will get two years value i.e.. two rows
4230 -- *****************************************************************************
4231 FOR rec_wtc_get_month_value IN
4232 csr_wtc_get_month_value (l_payroll_action_id
4233 ,rec_get_group.GROUP_ID
4234 )
4235 LOOP
4236 -- *****************************************************************************
4237 -- again to differntiate teh first year and second year condition using ROWNUM
4238 -- *****************************************************************************
4239 IF rec_wtc_get_month_value.ROWNUM = '1'
4240 THEN
4241 add_tag_value ('FIRSTYEAR', 'FIRSTYEAR');
4242 ELSE
4243 add_tag_value ('SECONDYEAR', 'SECONDYEAR');
4244 END IF;
4245
4246 -- *****************************************************************************
4247 -- Add Individual Months days and hours
4248 -- *****************************************************************************
4249 add_tag_value ('YEAR', rec_wtc_get_month_value.YEAR);
4250 add_tag_value ('JAN_DAYS', rec_wtc_get_month_value.jan_days);
4251 add_tag_value ('FEB_DAYS', rec_wtc_get_month_value.feb_days);
4252 add_tag_value ('MAR_DAYS', rec_wtc_get_month_value.mar_days);
4253 add_tag_value ('APR_DAYS', rec_wtc_get_month_value.apr_days);
4254 add_tag_value ('MAY_DAYS', rec_wtc_get_month_value.may_days);
4255 add_tag_value ('JUN_DAYS', rec_wtc_get_month_value.jun_days);
4256 add_tag_value ('JUL_DAYS', rec_wtc_get_month_value.jul_days);
4257 add_tag_value ('AUG_DAYS', rec_wtc_get_month_value.aug_days);
4258 add_tag_value ('SEP_DAYS', rec_wtc_get_month_value.sep_days);
4259 add_tag_value ('OCT_DAYS', rec_wtc_get_month_value.oct_days);
4260 add_tag_value ('NOV_DAYS', rec_wtc_get_month_value.nov_days);
4261 add_tag_value ('DEC_DAYS', rec_wtc_get_month_value.dec_days);
4262 add_tag_value ('JAN_HOURS', rec_wtc_get_month_value.jan_hours);
4263 add_tag_value ('FEB_HOURS', rec_wtc_get_month_value.feb_hours);
4264 add_tag_value ('MAR_HOURS', rec_wtc_get_month_value.mar_hours);
4265 add_tag_value ('APR_HOURS', rec_wtc_get_month_value.apr_hours);
4266 add_tag_value ('MAY_HOURS', rec_wtc_get_month_value.may_hours);
4267 add_tag_value ('JUN_HOURS', rec_wtc_get_month_value.jun_hours);
4268 add_tag_value ('JUL_HOURS', rec_wtc_get_month_value.jul_hours);
4269 add_tag_value ('AUG_HOURS', rec_wtc_get_month_value.aug_hours);
4270 add_tag_value ('SEP_HOURS', rec_wtc_get_month_value.sep_hours);
4271 add_tag_value ('OCT_HOURS', rec_wtc_get_month_value.oct_hours);
4272 add_tag_value ('NOV_HOURS', rec_wtc_get_month_value.nov_hours);
4273 add_tag_value ('DEC_HOURS', rec_wtc_get_month_value.dec_hours);
4274
4275 -- *****************************************************************************
4276 -- to differntiate teh first year and second year end case condition using ROWNUM
4277 -- *****************************************************************************
4278 IF rec_wtc_get_month_value.ROWNUM = '1'
4279 THEN
4280 add_tag_value ('FIRSTYEAR', 'FIRSTYEAR_END');
4281 ELSE
4282 add_tag_value ('SECONDYEAR', 'SECONDYEAR_END');
4283 END IF;
4284 -- *****************************************************************************
4285 END LOOP;
4286
4287 -- *****************************************************************************
4288 --------------Code for EOY 2008 changes
4289 -- *****************************************************************************
4290 -- Add code to put individual month value for absence
4291 -- for each group id we will get two years value i.e.. two rows
4292 -- *****************************************************************************
4293 FOR rec_wtc_get_ovr_addl_val IN
4294 csr_wtc_get_ovr_addl_val (l_payroll_action_id
4295 ,rec_get_group.GROUP_ID
4296 )
4297 LOOP
4298 -- *****************************************************************************
4299 -- again to differntiate the first year and second year condition using ROWNUM
4300 -- *****************************************************************************
4301 IF rec_wtc_get_ovr_addl_val.ROWNUM = '1'
4302 THEN
4303 add_tag_value ('FIRSTYEAR', 'FIRSTYEAR');
4304 ELSE
4305 add_tag_value ('SECONDYEAR', 'SECONDYEAR');
4306 END IF;
4307
4308 -- *****************************************************************************
4309 -- Add Individual Months days and hours
4310 -- *****************************************************************************
4311 add_tag_value ('YEAR', rec_wtc_get_ovr_addl_val.YEAR);
4312 add_tag_value ('JAN_ADDL', rec_wtc_get_ovr_addl_val.jan_addl_time);
4313 add_tag_value ('FEB_ADDL', rec_wtc_get_ovr_addl_val.feb_addl_time);
4314 add_tag_value ('MAR_ADDL', rec_wtc_get_ovr_addl_val.mar_addl_time);
4315 add_tag_value ('APR_ADDL', rec_wtc_get_ovr_addl_val.apr_addl_time);
4316 add_tag_value ('MAY_ADDL', rec_wtc_get_ovr_addl_val.may_addl_time);
4317 add_tag_value ('JUN_ADDL', rec_wtc_get_ovr_addl_val.jun_addl_time);
4318 add_tag_value ('JUL_ADDL', rec_wtc_get_ovr_addl_val.jul_addl_time);
4319 add_tag_value ('AUG_ADDL', rec_wtc_get_ovr_addl_val.aug_addl_time);
4320 add_tag_value ('SEP_ADDL', rec_wtc_get_ovr_addl_val.sep_addl_time);
4321 add_tag_value ('OCT_ADDL', rec_wtc_get_ovr_addl_val.oct_addl_time);
4322 add_tag_value ('NOV_ADDL', rec_wtc_get_ovr_addl_val.nov_addl_time);
4323 add_tag_value ('DEC_ADDL', rec_wtc_get_ovr_addl_val.dec_addl_time);
4324 add_tag_value ('JAN_OVERTIME', rec_wtc_get_ovr_addl_val.jan_overtime);
4325 add_tag_value ('FEB_OVERTIME', rec_wtc_get_ovr_addl_val.feb_overtime);
4326 add_tag_value ('MAR_OVERTIME', rec_wtc_get_ovr_addl_val.mar_overtime);
4327 add_tag_value ('APR_OVERTIME', rec_wtc_get_ovr_addl_val.apr_overtime);
4328 add_tag_value ('MAY_OVERTIME', rec_wtc_get_ovr_addl_val.may_overtime);
4329 add_tag_value ('JUN_OVERTIME', rec_wtc_get_ovr_addl_val.jun_overtime);
4330 add_tag_value ('JUL_OVERTIME', rec_wtc_get_ovr_addl_val.jul_overtime);
4331 add_tag_value ('AUG_OVERTIME', rec_wtc_get_ovr_addl_val.aug_overtime);
4332 add_tag_value ('SEP_OVERTIME', rec_wtc_get_ovr_addl_val.sep_overtime);
4333 add_tag_value ('OCT_OVERTIME', rec_wtc_get_ovr_addl_val.oct_overtime);
4334 add_tag_value ('NOV_OVERTIME', rec_wtc_get_ovr_addl_val.nov_overtime);
4335 add_tag_value ('DEC_OVERTIME', rec_wtc_get_ovr_addl_val.dec_overtime);
4336
4337 -- *****************************************************************************
4338 -- to differntiate teh first year and second year end case condition using ROWNUM
4339 -- *****************************************************************************
4340 IF rec_wtc_get_ovr_addl_val.ROWNUM = '1'
4341 THEN
4342 add_tag_value ('FIRSTYEAR', 'FIRSTYEAR_END');
4343 ELSE
4344 add_tag_value ('SECONDYEAR', 'SECONDYEAR_END');
4345 END IF;
4346 -- *****************************************************************************
4347 END LOOP;
4348 -- *****************************************************************************
4349 -- Add code to put individual month value
4350 -- for each group id we will get two years value i.e.. two rows
4351 -- *****************************************************************************
4352 FOR rec_wtc_get_absence_val IN
4353 csr_wtc_get_absence_val (l_payroll_action_id
4354 ,rec_get_group.GROUP_ID
4355 )
4356 LOOP
4357 -- *****************************************************************************
4358 -- again to differntiate teh first year and second year condition using ROWNUM
4359 -- *****************************************************************************
4360 IF rec_wtc_get_absence_val.ROWNUM = '1'
4361 THEN
4362 add_tag_value ('FIRSTYEAR', 'FIRSTYEAR');
4363 ELSE
4364 add_tag_value ('SECONDYEAR', 'SECONDYEAR');
4365 END IF;
4366
4367 -- *****************************************************************************
4368 -- Add Individual Months days and hours
4369 -- *****************************************************************************
4370 add_tag_value ('YEAR', rec_wtc_get_absence_val.YEAR);
4371 add_tag_value ('JAN_ABSENCE', rec_wtc_get_absence_val.jan_absence_time);
4372 add_tag_value ('FEB_ABSENCE', rec_wtc_get_absence_val.feb_absence_time);
4373 add_tag_value ('MAR_ABSENCE', rec_wtc_get_absence_val.mar_absence_time);
4374 add_tag_value ('APR_ABSENCE', rec_wtc_get_absence_val.apr_absence_time);
4375 add_tag_value ('MAY_ABSENCE', rec_wtc_get_absence_val.may_absence_time);
4376 add_tag_value ('JUN_ABSENCE', rec_wtc_get_absence_val.jun_absence_time);
4377 add_tag_value ('JUL_ABSENCE', rec_wtc_get_absence_val.jul_absence_time);
4378 add_tag_value ('AUG_ABSENCE', rec_wtc_get_absence_val.aug_absence_time);
4379 add_tag_value ('SEP_ABSENCE', rec_wtc_get_absence_val.sep_absence_time);
4380 add_tag_value ('OCT_ABSENCE', rec_wtc_get_absence_val.oct_absence_time);
4381 add_tag_value ('NOV_ABSENCE', rec_wtc_get_absence_val.nov_absence_time);
4382 add_tag_value ('DEC_ABSENCE', rec_wtc_get_absence_val.dec_absence_time);
4383
4384
4385 -- *****************************************************************************
4386 -- to differntiate teh first year and second year end case condition using ROWNUM
4387 -- *****************************************************************************
4388 IF rec_wtc_get_absence_val.ROWNUM = '1'
4389 THEN
4390 add_tag_value ('FIRSTYEAR', 'FIRSTYEAR_END');
4391 ELSE
4392 add_tag_value ('SECONDYEAR', 'SECONDYEAR_END');
4393 END IF;
4394 -- *****************************************************************************
4395 END LOOP;
4396
4397
4398 --**********************************************************************************
4399 IF rec_get_group.GROUP_ID = '1'
4400 THEN
4401 add_tag_value ('REPORTINGYEAR', 'REPORTINGYEAR_END');
4402 ELSE
4403 add_tag_value ('OTHERYEAR', 'OTHERYEAR_END');
4404 END IF;
4405 -- *****************************************************************************
4406 END LOOP;
4407
4408 add_tag_value ('PERSON', 'PERSON_END');
4409 END IF; /* for p_payroll_action_id IS NULL */
4410
4411 --logger ('Write TO clob ', 'started');
4412 writetoclob (p_xml);
4413 -- INSERT INTO clob_table VALUES (p_xml ); COMMIT;
4414 --logger ('Write TO clob ', 'complete');
4415 END get_xml_for_report;
4416
4417 -- *****************************************************************************
4418 /* Proc to Add the tag value and Name */
4419 PROCEDURE logger (p_display IN VARCHAR2, p_value IN VARCHAR2)
4420 IS
4421 BEGIN
4422 fnd_file.put_line (fnd_file.LOG
4423 , p_display || ' ==> ' || p_value
4424 );
4425 END logger;
4426 /* End of Proc to Add the tag value and Name */
4427 -- *****************************************************************************
4428 END pay_se_work_time_certificate;