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