[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_EERR_STATUS_CONTROL
Source
1 package body pay_no_eerr_status_control as
2 /* $Header: pynoeers.pkb 120.0.12020000.2 2012/08/21 09:03:23 abraghun ship $ */
3 --------------------------------------------------------------------------------
4 -- Global Variables
5 --------------------------------------------------------------------------------
6 --
7
8 TYPE t_detailed_output_tab_rec IS RECORD
9 (
10 dated_table_id pay_dated_tables.dated_table_id%TYPE ,
11 datetracked_event pay_datetracked_events.datetracked_event_id%TYPE ,
12 update_type pay_datetracked_events.update_type%TYPE ,
13 surrogate_key pay_process_events.surrogate_key%type ,
14 column_name pay_event_updates.column_name%TYPE ,
15 effective_date date,
16 creation_date date,
17 old_value varchar2(2000),
18 new_value varchar2(2000),
19 change_values varchar2(2000),
20 proration_type varchar2(10),
21 change_mode pay_process_events.change_type%type,--'DATE_PROCESSED' etc
22 element_entry_id pay_element_entries_f.element_entry_id%type,
23 next_ee number ,
24 assignment_id per_all_Assignments_f.assignment_id%type
25 );
26
27 TYPE l_detailed_output_table_type IS TABLE OF t_detailed_output_tab_rec
28 INDEX BY BINARY_INTEGER ;
29
30 g_debug boolean := hr_utility.debug_enabled;
31 g_package varchar2 (33)
32 := 'PAY_NO_EERR_STATUS_CONTROL.';
33 g_business_group_id number;
34 g_legal_employer_id number;
35 g_effective_date date;
36 g_start_date date;
37 g_end_date date;
38 g_archive varchar2 (50);
39 g_payroll_action_id number;
40 g_err_num number;
41 g_errm varchar2 (150);
42 g_min_avg_weekly_hours number := 0;
43 g_hour_change_limit number := 0;
44 g_absence_termination_limit number := 0;
45 g_report_mode varchar2 (80);
46 g_effective_start_date date;
47 g_legal_employer_name hr_all_organization_units.name%type;
48 g_legal_employer_org_no hr_organization_information.org_information1%type;
49 g_no_hours_change_weeks number := 0;
50 /* GET PARAMETER */
51 function get_parameter (
52 p_parameter_string in varchar2,
53 p_token in varchar2,
54 p_segment_number in number default null
55 )
56 return varchar2 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 if g_debug then
63 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
64 end if;
65
66 l_start_pos := instr (
67 ' ' || p_parameter_string,
68 l_delimiter || p_token || '='
69 );
70
71 --
72 if l_start_pos = 0 then
73 l_delimiter := '|';
74 l_start_pos := instr (
75 ' ' || p_parameter_string,
76 l_delimiter || p_token || '='
77 );
78 end if;
79
80 if l_start_pos <> 0 then
81 l_start_pos := l_start_pos + length (p_token || '=');
82 l_parameter := substr (
83 p_parameter_string,
84 l_start_pos,
85 instr (
86 p_parameter_string || ' ',
87 l_delimiter,
88 l_start_pos
89 )
90 - l_start_pos
91 );
92
93 if p_segment_number is not null then
94 l_parameter := ':' || l_parameter || ':';
95 l_parameter := substr (
96 l_parameter,
97 instr (l_parameter, ':', 1, p_segment_number) + 1,
98 instr (
99 l_parameter,
100 ':',
101 1,
102 p_segment_number + 1
103 )
104 - 1 - instr (
105 l_parameter,
106 ':',
107 1,
108 p_segment_number
109 )
110 );
111 end if;
112 end if;
113
114 --
115 if g_debug then
116 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
117 end if;
118
119 return l_parameter;
120 end;
121 /* GET ALL PARAMETERS */
122 procedure get_all_parameters (
123 p_payroll_action_id in number,
124 p_business_group_id out nocopy number,
125 p_legal_employer_id out nocopy number,
126 p_archive out nocopy varchar2,
127 p_start_date out nocopy date, --14507939
128 p_effective_date out nocopy date
129 ) is
130 cursor csr_parameter_info (
131 p_payroll_action_id number
132 ) is
133 select pay_no_eerr_status_control.get_parameter (
134 legislative_parameters,
135 'LEGAL_EMPLOYER'
136 ),
137 pay_no_eerr_status_control.get_parameter (
138 legislative_parameters,
139 'ARCHIVE'
140 ),
141 start_date, --14507939
142 effective_date, business_group_id
143 from pay_payroll_actions
144 where payroll_action_id = p_payroll_action_id;
145
146 l_proc varchar2 (240) := g_package || ' GET_ALL_PARAMETERS ';
147 --
148 begin
149 fnd_file.put_line (fnd_file.log, 'Entering Get all Parameters');
150 open csr_parameter_info (p_payroll_action_id);
151 fetch csr_parameter_info into p_legal_employer_id,
152 p_archive,
153 p_start_date, --14507939
154 p_effective_date,
155 p_business_group_id;
156 close csr_parameter_info;
157
158 --
159 if g_debug then
160 hr_utility.set_location (
161 ' Leaving Procedure GET_ALL_PARAMETERS',
162 30
163 );
164 end if;
165 end get_all_parameters;
166 /* RANGE CODE */
167 procedure range_code (
168 p_payroll_action_id in number,
169 p_sql out nocopy varchar2
170 ) is
171 l_action_info_id number;
172 l_ovn number;
173
174 cursor csr_legal_employers (
175 p_legal_employer_id in number
176 ) is
177 select org.organization_id legal_employer_id,
178 org.name
179 legal_employer_name, org.location_id,
180 hoi1.org_information1
181 legal_employer_org_no
182 from hr_all_organization_units org,
183 hr_organization_information hoi1
184 where org.organization_id = p_legal_employer_id
185 and hoi1.organization_id(+) = org.organization_id
186 and hoi1.org_information_context(+) = 'NO_LEGAL_EMPLOYER_DETAILS';
187
188 l_legal_employer_rec csr_legal_employers%rowtype;
189
190 cursor csr_all_local_unit_details (
191 csr_v_legal_employer_id hr_organization_information.organization_id%type
192 ) is
193 select hoi_le.org_information1 local_unit_id,
194 hou_lu.name
195 local_unit_name,
196 hoi_lu.org_information1
197 local_unit_org_no, hou_lu.location_id
198 from hr_all_organization_units hou_le,
199 hr_organization_information hoi_le,
200 hr_all_organization_units hou_lu,
201 hr_organization_information hoi_lu
202 where hoi_le.organization_id = hou_le.organization_id
203 and hou_le.organization_id = csr_v_legal_employer_id
204 and hoi_le.org_information_context = 'NO_LOCAL_UNITS'
205 and hou_lu.organization_id = hoi_le.org_information1
206 and hou_lu.organization_id = hoi_lu.organization_id
207 and hoi_lu.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
208
209 begin
210
211 if g_debug then
212 hr_utility.set_location (' Entering Procedure RANGE_CODE', 10);
213 end if;
214
215 p_sql :=
216 'SELECT DISTINCT person_id
217 FROM per_people_f ppf
218 ,pay_payroll_actions ppa
219 WHERE ppa.payroll_action_id = :payroll_action_id
220 AND ppa.business_group_id = ppf.business_group_id
221 ORDER BY ppf.person_id';
222 --
223 --
224 /* Get the Parameters'value */
225 pay_no_eerr_status_control.get_all_parameters (
226 p_payroll_action_id,
227 g_business_group_id,
228 g_legal_employer_id,
229 g_archive,
230 g_effective_start_date,--14507939
231 g_effective_date
232 );
233
234 --
235 --
236 if g_archive = 'Y' then
237 --g_effective_start_date := add_months (g_effective_date, -12) + 1; --14507939
238 /* Get the Legal Employer Details */
239 open csr_legal_employers (g_legal_employer_id);
240 fetch csr_legal_employers into l_legal_employer_rec;
241 close csr_legal_employers;
242 --
243 --
244 g_legal_employer_name := l_legal_employer_rec.legal_employer_name;
245 g_legal_employer_org_no := l_legal_employer_rec.legal_employer_org_no;
246 --
247 --
248 pay_action_information_api.create_action_information (
249 p_action_information_id => l_action_info_id,
250 p_action_context_id => p_payroll_action_id,
251 p_action_context_type => 'PA',
252 p_object_version_number => l_ovn,
253 p_effective_date => g_effective_date,
254 p_source_id => null,
255 p_source_text => null,
256 p_action_information_category => 'EMEA REPORT DETAILS',
257 p_action_information1 => 'PYNOEERSTC',
258 p_action_information2 => g_legal_employer_id,
259 p_action_information3 => g_effective_date,
260 p_action_information4 => g_legal_employer_name
261 );
262 --
263 --
264 for i in csr_all_local_unit_details (g_legal_employer_id)
265 loop
266 pay_action_information_api.create_action_information (
267 p_action_information_id => l_action_info_id,
268 p_action_context_id => p_payroll_action_id,
269 p_action_context_type => 'PA',
270 p_object_version_number => l_ovn,
271 p_effective_date => g_effective_date,
272 p_source_id => null,
273 p_source_text => null,
274 p_action_information_category => 'EMEA REPORT INFORMATION',
275 p_action_information1 => 'PYNOEERSTC',
276 p_action_information2 => g_business_group_id,
277 p_action_information3 => g_legal_employer_id,
278 p_action_information4 => g_legal_employer_name, -- Legal Employer Name
279 p_action_information5 => g_legal_employer_org_no, -- Legal Employer Org No
280 p_action_information6 => i.local_unit_id, -- Local Unit Id
281 p_action_information7 => i.local_unit_name, -- Local Unit Name
282 p_action_information8 => i.local_unit_org_no
283 );
284 --
285 --
286 end loop;
287 end if;
288
289 end range_code;
290
291 procedure assignment_action_code (
292 p_payroll_action_id in number,
293 p_start_person in number,
294 p_end_person in number,
295 p_chunk in number
296 ) is
297
298
299 cursor get_global_value (
300 p_global_name varchar2,
301 p_effective_date date
302 ) is
303 select nvl(fnd_number.canonical_to_number (global_value),0)
304 from ff_globals_f
305 where legislation_code = 'NO' and global_name = p_global_name
306 and p_effective_date between effective_start_date and effective_end_date ;
307
308 /* Cursor to get Local Unit Details based on the Legal Employers */
309 cursor csr_all_local_unit_details (
310 csr_v_legal_employer_id hr_organization_information.organization_id%type
311 ) is
312 select hoi_le.org_information1 local_unit_id,
313 hou_lu.name
314 local_unit_name,
315 hoi_lu.org_information1
316 local_unit_org_no, hou_lu.location_id
317 from hr_all_organization_units hou_le,
318 hr_organization_information hoi_le,
319 hr_all_organization_units hou_lu,
320 hr_organization_information hoi_lu
321 where hoi_le.organization_id = hou_le.organization_id
322 and hou_le.organization_id = csr_v_legal_employer_id
323 and hoi_le.org_information_context = 'NO_LOCAL_UNITS'
324 and hou_lu.organization_id = hoi_le.org_information1
325 and hou_lu.organization_id = hoi_lu.organization_id
326 and hoi_lu.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
327
328 --
329 --
330 /* Cursor to get Employee Details based on the Local Unit , Start Date
331 and End Date*/
332 cursor csr_employee_details (
333 p_local_unit hr_all_organization_units.organization_id%type,
334 p_start_date date,
335 p_end_date date
336 ) is
337 select papf.person_id person_id, paaf.assignment_id,
338 papf.effective_start_date, null effective_end_date,
339 national_identifier, full_name, employee_number, normal_hours,
340 hourly_salaried_code, hsc.segment3 position_code, frequency
341 from per_all_people_f papf,
342 per_all_assignments_f paaf,
343 hr_soft_coding_keyflex hsc,
344 per_assignment_status_types past
345 where papf.person_id between p_start_person and p_end_person
346 and paaf.person_id = papf.person_id
347 and paaf.business_group_id = papf.business_group_id
348 -- and paaf.primary_flag = 'Y'
349 and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
350 and hsc.segment2 = to_char (p_local_unit)
351 and paaf.assignment_status_type_id =
352 past.assignment_status_type_id
353 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
354 and paaf.assignment_id = (select min (assignment_id)
355 from per_all_assignments_f asg,
356 hr_soft_coding_keyflex hsck
357 where person_id = papf.person_id
358 and hsck.soft_coding_keyflex_id =
359 asg.soft_coding_keyflex_id
360 and hsck.segment2 =
361 to_char(p_local_unit))
362 and p_end_date between paaf.effective_start_date
363 and paaf.effective_end_date
364 and p_end_date between papf.effective_start_date
365 and papf.effective_end_date
366 and not exists (select actual_termination_date
367 from per_periods_of_service
368 where actual_termination_date =
369 paaf.effective_end_date
370 and person_id = papf.person_id
371 and actual_termination_date = nvl(final_process_date,actual_termination_date )
372 and p_end_date >= actual_termination_date
373 )
374
375 --14507939 Start1
376 AND NOT EXISTS
377 (
378 SELECT 1
379 FROM per_person_type_usages_f pptuf
380 WHERE pptuf.person_id = papf.person_id
381 AND p_end_date BETWEEN pptuf.effective_start_date
382 AND pptuf.effective_end_date
383 AND pptuf.person_type_id IN
384 (
385 SELECT pptt.person_type_id
386 FROM pay_user_tables put
387 ,pay_user_tables_tl putt
388 ,pay_user_columns puc
389 ,pay_user_columns_tl puct
390 ,pay_user_rows_f purf
391 ,pay_user_column_instances_f pucif
392 ,per_person_types_tl pptt
393 WHERE put.business_group_id = papf.business_group_id
394 AND put.user_table_id = putt.user_table_id
395 AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
396 AND puc.user_column_id = puct.user_column_id
397 AND puc.business_group_id = papf.business_group_id
398 AND lower (trim (puct.user_column_name)) = lower (trim ('Person Type'))
399 AND puc.user_table_id = put.user_table_id
400 AND purf.user_table_id = put.user_table_id
401 AND purf.business_group_id = papf.business_group_id
402 AND p_end_date BETWEEN purf.effective_start_date
403 AND purf.effective_end_date
404 AND pucif.user_row_id = purf.user_row_id
405 AND pucif.user_column_id = puc.user_column_id
406 AND pucif.business_group_id = papf.business_group_id
407 AND p_end_date BETWEEN pucif.effective_start_date
408 AND pucif.effective_end_date
409 AND lower (trim (pptt.user_person_type)) = lower (trim (pucif.value))
410 )
411 )
412 --14507939 End 1
413 union
414 select papf.person_id person_id, paaf.assignment_id,
415 papf.effective_start_date, paaf.effective_end_date,
416 national_identifier, full_name, employee_number, normal_hours,
417 hourly_salaried_code, hsc.segment3 position_code, frequency
418 from per_all_people_f papf,
419 per_all_assignments_f paaf,
420 hr_soft_coding_keyflex hsc,
421 per_assignment_status_types past
422 where paaf.person_id = papf.person_id
423 and papf.person_id between p_start_person and p_end_person
424 and paaf.business_group_id = papf.business_group_id
425 --and paaf.primary_flag = 'Y'
426 and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
427 and hsc.segment2 = to_char (p_local_unit)
428 and paaf.assignment_status_type_id =
429 past.assignment_status_type_id
430 and paaf.assignment_id = (select min (assignment_id)
431 from per_all_assignments_f asg,
432 hr_soft_coding_keyflex hsck
433 where person_id = papf.person_id
434 and hsck.soft_coding_keyflex_id =
435 asg.soft_coding_keyflex_id
436 and hsck.segment2 =
437 to_char (p_local_unit))
438 --and past.PER_SYSTEM_STATUS = 'TERM_ASSIGN'
439 and ( ( papf.effective_end_date between p_start_date
440 and p_end_date
441 --and paaf.effective_end_date between p_start_date and p_end_date
442 and exists (select actual_termination_date
443 from per_periods_of_service
444 where actual_termination_date =
445 paaf.effective_end_date
446 and person_id = papf.person_id
447 and actual_termination_date = nvl(final_process_date,actual_termination_date ))
448 )
449 or ( paaf.effective_start_date between p_start_date
450 and p_end_date
451 and past.per_system_status = 'TERM_ASSIGN'
452 and papf.effective_end_date between p_start_date
453 and p_end_date
454 )
455 )
456
457 --14507939 Start2
458 AND NOT EXISTS
459 (
460 SELECT 1
461 FROM per_person_type_usages_f pptuf
462 WHERE pptuf.person_id = papf.person_id
463 AND p_end_date BETWEEN pptuf.effective_start_date
464 AND pptuf.effective_end_date
465 AND pptuf.person_type_id IN
466 (
467 SELECT pptt.person_type_id
468 FROM pay_user_tables put
469 ,pay_user_tables_tl putt
470 ,pay_user_columns puc
471 ,pay_user_columns_tl puct
472 ,pay_user_rows_f purf
473 ,pay_user_column_instances_f pucif
474 ,per_person_types_tl pptt
475 WHERE put.business_group_id = papf.business_group_id
476 AND put.user_table_id = putt.user_table_id
477 AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
478 AND puc.user_column_id = puct.user_column_id
479 AND puc.business_group_id = papf.business_group_id
480 AND lower (trim (puct.user_column_name)) = lower (trim ('Person Type'))
481 AND puc.user_table_id = put.user_table_id
482 AND purf.user_table_id = put.user_table_id
483 AND purf.business_group_id = papf.business_group_id
484 AND p_end_date BETWEEN purf.effective_start_date
485 AND purf.effective_end_date
486 AND pucif.user_row_id = purf.user_row_id
487 AND pucif.user_column_id = puc.user_column_id
488 AND pucif.business_group_id = papf.business_group_id
489 AND p_end_date BETWEEN pucif.effective_start_date
490 AND pucif.effective_end_date
491 AND lower (trim (pptt.user_person_type)) = lower (trim (pucif.value))
492 )
493 )
494 --14507939 End 2
495 ;
496
497 --
498 --
499 /* Cursor to get the Start Date of the Assignment */
500 cursor csr_start_date (
501 p_assignment_id per_all_assignments_f.assignment_id%type
502 ) is
503 select min (effective_start_date)
504 from per_all_assignments_f paaf, per_assignment_status_types past
505 where assignment_id = p_assignment_id
506 and paaf.assignment_status_type_id =
507 past.assignment_status_type_id
508 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
509
510 --
511 --
512 /* Cursor to get the Absence Start Date and End Date when employee is on
513 absence for more than 14 days
514 cursor csr_absence_days (
515 p_person_id per_all_people_f.person_id%type
516 ) is
517 select paa.date_start, paa.date_end
518 from per_absence_attendances paa, per_absence_attendance_types paat
519 where paat.absence_attendance_type_id =
520 paa.absence_attendance_type_id
521 and paa.person_id = p_person_id
522 and paa.date_end - paa.date_start > g_absence_termination_limit
523 and paa.date_start between g_effective_start_date
524 and g_effective_date
525 and paat.absence_category not in
526 ('S', 'PTM', 'PTS', 'PTP', 'PTA', 'VAC', 'MRE', 'CMS');*/
527
528 /* Cursor to get the Absence Start Date and End Date when employee is on
529 absence for more than 14 days */
530 cursor csr_absence_start_days (
531 p_person_id per_all_people_f.person_id%type
532 ) is
533 select paa.date_start, paa.date_end
534 from per_absence_attendances paa, per_absence_attendance_types paat
535 where paat.absence_attendance_type_id =
536 paa.absence_attendance_type_id
537 and paa.person_id = p_person_id
538 and paa.date_start between g_effective_start_date and g_effective_date
539 /* and paat.absence_category not in
540 ('S', 'PTM', 'PTS', 'PTP', 'PTA', 'VAC', 'MRE'); -- 5520062 */
541 --14507939 Start3
542 AND
543 (
544 paat.absence_category IN ('IE_AL', 'M', 'PTA', 'PTM', 'D')
545 OR
546 (
547 paat.absence_category IN ('UN', 'UL', 'OTH', 'PERS',
548 'REP', 'PTP', 'PA', 'H', 'OR')
549 AND nvl(paa.date_end,g_end_date) - paa.date_start >=
550 g_absence_termination_limit
551 )
552 )
553 AND paat.absence_attendance_type_id NOT IN
554 (
555 SELECT paatt.absence_attendance_type_id
556 FROM pay_user_tables put
557 ,pay_user_tables_tl putt
558 ,pay_user_columns puc
559 ,pay_user_columns_tl puct
560 ,pay_user_rows_f purf
561 ,pay_user_column_instances_f pucif
562 ,per_absence_attendance_types paat
563 ,per_abs_attendance_types_tl paatt
564 WHERE put.business_group_id = paa.business_group_id
565 AND put.user_table_id = putt.user_table_id
566 AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
567 AND puc.user_column_id = puct.user_column_id
568 AND puc.business_group_id = paa.business_group_id
569 AND lower (trim (puct.user_column_name)) = lower (trim ('Absence Type'))
570 AND puc.user_table_id = put.user_table_id
571 AND purf.user_table_id = put.user_table_id
572 AND purf.business_group_id = paa.business_group_id
573 AND g_effective_date BETWEEN purf.effective_start_date
574 AND purf.effective_end_date
575 AND pucif.user_row_id = purf.user_row_id
576 AND pucif.user_column_id = puc.user_column_id
577 AND pucif.business_group_id = paa.business_group_id
578 AND g_effective_date BETWEEN pucif.effective_start_date
579 AND pucif.effective_end_date
580 AND paat.business_group_id = paa.business_group_id
581 AND paatt.absence_attendance_type_id = paat.absence_attendance_type_id
582 AND lower (trim (paatt.name)) = lower (trim (pucif.value))
583 );
584 --14507939 End 3
585
586
587
588
589
590 cursor csr_absence_end_days (
591 p_person_id per_all_people_f.person_id%type
592 ,p_prev_last_date date
593 ) is
594 select paa.date_start, paa.date_end
595 from per_absence_attendances paa, per_absence_attendance_types paat
596 where paat.absence_attendance_type_id =
597 paa.absence_attendance_type_id
598 and paa.person_id = p_person_id
599 and paa.date_end between p_prev_last_date and g_effective_date
600 /* and paa.date_end - paa.date_start >= g_absence_termination_limit
601 and paat.absence_category not in
602 ('S', 'PTM', 'PTS', 'PTP', 'PTA', 'VAC', 'MRE');
603 */
604 --14507939 Start4
605 AND
606 (
607 paat.absence_category IN ('IE_AL', 'M', 'PTA', 'PTM', 'D')
608 OR
609 (
610 paat.absence_category IN ('UN', 'UL', 'OTH', 'PERS',
611 'REP', 'PTP', 'PA', 'H', 'OR')
612 AND paa.date_end - paa.date_start >= g_absence_termination_limit
613 )
614 )
615 AND paat.absence_attendance_type_id NOT IN
616 (
617 SELECT paatt.absence_attendance_type_id
618 FROM pay_user_tables put
619 ,pay_user_tables_tl putt
620 ,pay_user_columns puc
621 ,pay_user_columns_tl puct
622 ,pay_user_rows_f purf
623 ,pay_user_column_instances_f pucif
624 ,per_absence_attendance_types paat
625 ,per_abs_attendance_types_tl paatt
626 WHERE put.business_group_id = paa.business_group_id
627 AND put.user_table_id = putt.user_table_id
628 AND lower (trim (putt.user_table_name)) = lower (trim ('Norwegian Employers Employees Register Exclusion'))
629 AND puc.user_column_id = puct.user_column_id
630 AND puc.business_group_id = paa.business_group_id
631 AND lower (trim (puct.user_column_name)) = lower (trim ('Absence Type'))
632 AND puc.user_table_id = put.user_table_id
633 AND purf.user_table_id = put.user_table_id
634 AND purf.business_group_id = paa.business_group_id
635 AND g_effective_date BETWEEN purf.effective_start_date
636 AND purf.effective_end_date
637 AND pucif.user_row_id = purf.user_row_id
638 AND pucif.user_column_id = puc.user_column_id
639 AND pucif.business_group_id = paa.business_group_id
640 AND g_effective_date BETWEEN pucif.effective_start_date
641 AND pucif.effective_end_date
642 AND paat.business_group_id = paa.business_group_id
643 AND paatt.absence_attendance_type_id = paat.absence_attendance_type_id
644 AND lower (trim (paatt.name)) = lower (trim (pucif.value))
645 );
646 --14507939 End 4
647
648
649 --
650 --
651
652 --
653 --
654 /* Cursor to get Event Group Details */
655 cursor csr_event_group_details (
656 p_event_group_name varchar2,
657 p_business_group_id number
658 ) is
659 select event_group_id
660 from pay_event_groups
661 where event_group_name = p_event_group_name
662 and nvl (business_group_id, p_business_group_id) =
663 p_business_group_id;
664
665 --
666 --
667
668 /* Cursor to get the Organization No for the Local Unit based on the soft
669 coding keyflex id */
670 cursor csr_get_org_no (
671 p_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type
672 ) is
673 select org_information1
674 from hr_organization_information hoi, hr_soft_coding_keyflex hsc
675 where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
676 and hsc.segment2 = organization_id
677 and soft_coding_keyflex_id = p_soft_coding_keyflex_id;
678
679 --
680 --
681
682 /* Cursor to get the SSB Position Code based on the soft coding keyflex id */
683 cursor csr_get_job_position_code (
684 p_assignment_id number,
685 p_effective_date date,
686 p_job_id number
687 ) is
688 select segment3
689 from hr_soft_coding_keyflex hsc, per_all_assignments_f paaf
690 where paaf.job_id = p_job_id
691 and assignment_id = p_assignment_id
692 and p_effective_date between paaf.effective_start_date
693 and paaf.effective_end_date
694 and paaf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id;
695
696 --
697 --
698 /* Cursor to get Assignment Status */
699 cursor csr_get_assignment_status (
700 p_assignment_status_type_id per_assignment_status_types.assignment_status_type_id%type
701 ) is
702 select per_system_status
703 from per_assignment_status_types
704 where assignment_status_type_id = p_assignment_status_type_id;
705
706 --
707 --
708
709 /* Cursor to get the Element Entries Id for the Element Type */
710 cursor csr_get_element_entries (
711 c_assignment_id number,
712 c_eff_date date,
713 c_element_name varchar2
714 ) is
715 select peef.element_entry_id
716 from pay_element_entries_f peef, pay_element_types_f pet
717 where pet.element_name = c_element_name
718 and pet.legislation_code = 'NO'
719 and peef.assignment_id = c_assignment_id
720 and peef.element_type_id = pet.element_type_id
721 and c_eff_date between peef.effective_start_date
722 and peef.effective_end_date
723 and c_eff_date between pet.effective_start_date
724 and pet.effective_end_date;
725
726 --
727 --
728 /* Cursor to get the Local Unit Id for the passed soft coding keyflex id */
729 cursor csr_get_lu_scl (
730 p_soft_coding_keyflex_id number
731 ) is
732 select nvl (segment2, '0')
733 from hr_soft_coding_keyflex
734 where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
735
736 --
737 --
738 /* Cursor to get the Position Code for the passed soft coding keyflex id */
739 cursor csr_get_pos_scl (
740 p_soft_coding_keyflex_id number
741 ) is
742 select nvl (segment3, 0)
743 from hr_soft_coding_keyflex
744 where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
745
746 --
747 --
748 /* Cursor to get Eleent Entry ID */
749 cursor csr_get_element_entry (
750 c_assignment_id number,
751 c_eff_date date,
752 c_element_name varchar2
753 ) is
754 select peef.element_entry_id
755 from pay_element_entries_f peef, pay_element_types_f pet
756 where pet.element_name = c_element_name
757 and pet.legislation_code = 'NO'
758 and peef.assignment_id = c_assignment_id
759 and peef.element_type_id = pet.element_type_id
760 and c_eff_date between peef.effective_start_date
761 and peef.effective_end_date
762 and c_eff_date between pet.effective_start_date
763 and pet.effective_end_date;
764
765 cursor csr_get_sick_unpaid_entry (
766 p_assignment_id number,
767 p_start_date date,
768 p_end_date date,
769 p_element_name varchar2
770 ) is
771 select peef.element_entry_id
772 from pay_element_entries_f peef, pay_element_types_f pet
773 where pet.element_name = p_element_name
774 and pet.legislation_code = 'NO'
775 and peef.assignment_id = p_assignment_id
776 and peef.element_type_id = pet.element_type_id
777 and peef.effective_start_date between p_start_date
778 and p_end_date ;
779
780
781 --
782 --
783 /* Cursor to get the Element Details */
784 cursor csr_get_element_det (
785 c_element_name varchar2,
786 c_input_val_name varchar2,
787 c_assignment_id number,
788 c_eff_date date
789 ) is
790 select fnd_date.canonical_to_date (peev.screen_entry_value)
791 from pay_element_types_f pet,
792 pay_input_values_f piv,
793 pay_element_entries_f peef,
794 pay_element_entry_values_f peev
795 where pet.element_name = c_element_name
796 and pet.element_type_id = piv.element_type_id
797 and piv.name = c_input_val_name
798 and pet.legislation_code = 'NO'
799 and piv.legislation_code = 'NO'
800 and peef.assignment_id = c_assignment_id
801 and peef.element_entry_id = peev.element_entry_id
802 and peef.element_type_id = pet.element_type_id
803 and peev.input_value_id = piv.input_value_id
804 and c_eff_date between piv.effective_start_date
805 and piv.effective_end_date
806 and c_eff_date between pet.effective_start_date
807 and pet.effective_end_date
808 and c_eff_date between peev.effective_start_date
809 and peev.effective_end_date
810 and c_eff_date between peef.effective_start_date
811 and peef.effective_end_date;
812
813 --
814 --
815 /* Cursor to get the Dated Table ID */
816 cursor csr_get_table_id (
817 c_table_name varchar2
818 ) is
819 select dated_table_id
820 from pay_dated_tables
821 where table_name = c_table_name;
822
823 --
824 --
825 /* Cursor to get the Element Value for Hours */
826 cursor csr_get_element_value (
827 c_element_entry_id number,
828 c_eff_start_date date,
829 c_eff_end_date date
830 ) is
831 select effective_start_date,
832 fnd_number.canonical_to_number (screen_entry_value) entry_value
833 from pay_element_entry_values_f peev
834 where element_entry_id = c_element_entry_id
835 and effective_start_date between c_eff_start_date and c_eff_end_date
836 and screen_entry_value is not null
837 and effective_start_date =
838 (select max (effective_start_date)
839 from pay_element_entry_values_f peevf
840 where element_entry_id = c_element_entry_id
841 and effective_start_date between c_eff_start_date
842 and c_eff_end_date
843 -- and peevf.effective_start_date = peev.effective_start_date
844 and to_char (peev.effective_start_date, 'MM') =
845 to_char (peevf.effective_start_date, 'MM'));
846
847 /* Cursor to get the current element value */
848 cursor csr_get_curr_element_value (
849 c_element_entry_id number,
850 c_effective_date date
851 ) is
852 select fnd_number.canonical_to_number (screen_entry_value) entry_value
853 from pay_element_entry_values_f
854 where element_entry_id = c_element_entry_id
855 and c_effective_date between effective_start_date
856 and effective_end_date
857 and screen_entry_value is not null;
858
859 /* Cursor to get all the assignment for the person except the given assignment*/
860 cursor csr_get_all_assignments
861 (p_person_id per_all_people_f.person_id%type,
862 p_assignment_id per_all_assignments_f.assignment_id%type,
863 p_local_unit hr_all_organization_units.organization_id%type)
864 is
865 select assignment_id
866 from per_all_assignments_f paaf ,hr_soft_coding_keyflex hsck
867 where person_id = p_person_id
868 and assignment_id <> p_assignment_id
869 and hsck.segment2 = to_char (p_local_unit)
870 and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
871
872
873 Cursor csr_get_assignment_details
874 (p_effective_date date,
875 p_assignment_id per_all_assignments_f.assignment_id%type,
876 p_local_unit hr_all_organization_units.organization_id%type)
877 is
878 select normal_hours,
879 hourly_salaried_code, hsc.segment3 position_code, frequency
880 from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
881 where paaf.assignment_id = p_assignment_id
882 and hsc.segment2 = to_char (p_local_unit)
883 and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
884 and p_effective_date between paaf.effective_start_date and paaf.effective_End_date;
885
886 rl_assignment_details csr_get_assignment_details%rowtype;
887
888 cursor previous_hour_value (
889 p_assignment_id per_all_assignments_f.assignment_id%type,
890 p_effective_date date
891 ) is
892 select normal_hours , effective_start_date
893 from per_all_assignments_f
894 where assignment_id = p_assignment_id
895 and effective_start_date < p_effective_date
896 order by effective_start_date desc ;
897
898 /* Declaration for Local Variables */
899 l_ovn number;
900 l_action_info_id number;
901 l_legal_employer_id hr_organization_units.organization_id%type;
902 l_business_group_id hr_all_organization_units.business_group_id%type;
903 l_start_date date;
904 l_end_date date;
905 l_legal_employer_id hr_organization_units.organization_id%type;
906 l_effective_date date;
907 l_emp_start_date date;
908 l_emp_end_date date;
909 l_person_id per_all_people_f.person_id%type;
910 l_event_group_id pay_event_groups.event_group_id%type;
911 l_detailed_output l_detailed_output_table_type; -- pay_interpreter_pkg.t_detailed_output_table_type;
912 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
913 l_detail_tab pay_interpreter_pkg.t_detailed_output_table_type;
914 l_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
915 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
916 l_total_hours number := 0;
917 l_total_hours_all number := 0;
918 l_frequency per_all_assignments_f.frequency%type;
919 l_hour_effective_end_date date;
920 l_hour_value varchar2 (100);
921 l_hour_value1 varchar2 (100);
922 l_job_value varchar2 (100);
923 l_local_unit_value varchar2 (100);
924 l_hour_change_effective_date date;
925 l_job_change_effective_date date;
926 l_lu_change_effective_date date;
927 y number := 1;
928 l_assact_id number;
929 l_status_type varchar2 (2);
930 l_effective_start_date date;
931 l_lu_change_effective_date1 date;
932 l_job_change_effective_date1 date;
933 l_hour_value_reported number := 0;
934 l_event_group_details csr_event_group_details%rowtype;
935 l_user_status per_assignment_status_types.per_system_status%type;
936 l_old_user_status per_assignment_status_types.per_system_status%type;
937 l_last_update_date date;
938 l_alter_change char (1);
939 l_lu_org_no hr_organization_information.org_information1%type;
940 l_hour_element_entry_id number;
941 l_new_job_value varchar2 (100);
942 l_old_job_value varchar2 (100);
943 l_normal_hours number;
944 l_table1 pay_dated_tables.dated_table_id%type;
945 l_table2 pay_dated_tables.dated_table_id%type;
946 l_table3 pay_dated_tables.dated_table_id%type;
947 l_element_entry_id pay_element_entries_f.element_entry_id%type;
948 l_defined_balance_id number;
949 l_get_prev_mon_bal_value number;
950 l_get_current_mon_bal_value number;
951 l_abs_start_date date;
952 l_abs_end_date date;
953 l_hour_date_reported date;
954 l_hour_value_primary number;
955 l_houry_change_flag char (1) := 'N';
956 l_job_id number;
957 l_empl_start_date date;
958 l_old_scl varchar2 (30);
959 l_new_scl varchar2 (30);
960 l_new_lu hr_soft_coding_keyflex.segment3%type;
961 l_old_lu hr_soft_coding_keyflex.segment3%type;
962 l_sickness_unpaid_start date;
963 l_sickness_unpaid_end date;
964 l_prev_hour_flag char (1);
965 /* Flag to set whether the hours have got changed during the period or not*/
966 l_hour_year_change_flag char (1);
967 l_national_identifier per_all_people_f.national_identifier%type;
968 l_detailed_output1 pay_interpreter_pkg.t_detailed_output_table_type;
969 l_detailed_output2 pay_interpreter_pkg.t_detailed_output_table_type;
970 l_detailed_output3 pay_interpreter_pkg.t_detailed_output_table_type;
971 l_empty_detailed_output l_detailed_output_table_type;
972 merge_cnt number ;
973 l_hour_old_value number;
974 l_prev_hour_value_primary number;
975 l_prev_hour_eff_date date;
976 l_schedule cac_avlblty_time_varray;
977 l_schedule_source VARCHAR2(10);
978 l_return_status VARCHAR2(1);
979 l_return_message VARCHAR2(2000);
980 l_days_or_hours Varchar2(10) := 'D';
981 l_include_event Varchar2(10) := 'Y';
982 l_start_time_char Varchar2(10) := '0';
983 l_end_time_char Varchar2(10) := '23.59';
984 l_duration Number;
985 l_wrk_schd_return Number;
986 l_prev_last_date date;
987 l_abs_emp_start_date date;
988 l_new_hour number;
989 --
990 --
991
992 procedure copy1 (
993 p_copy_from in out nocopy l_detailed_output_table_type,
994 p_from in number,
995 p_copy_to in out nocopy l_detailed_output_table_type,
996 p_to in number
997 ) is
998 begin
999 --
1000 p_copy_to (p_to).dated_table_id := p_copy_from (p_from).dated_table_id;
1001 p_copy_to (p_to).datetracked_event :=
1002 p_copy_from (p_from).datetracked_event;
1003 p_copy_to (p_to).surrogate_key := p_copy_from (p_from).surrogate_key;
1004 p_copy_to (p_to).update_type := p_copy_from (p_from).update_type;
1005 p_copy_to (p_to).column_name := p_copy_from (p_from).column_name;
1006 p_copy_to (p_to).effective_date := p_copy_from (p_from).effective_date;
1007 p_copy_to (p_to).old_value := p_copy_from (p_from).old_value;
1008 p_copy_to (p_to).new_value := p_copy_from (p_from).new_value;
1009 p_copy_to (p_to).change_values := p_copy_from (p_from).change_values;
1010 p_copy_to (p_to).proration_type := p_copy_from (p_from).proration_type;
1011 p_copy_to (p_to).change_mode := p_copy_from (p_from).change_mode;
1012 p_copy_to (p_to).creation_date := p_copy_from (p_from).creation_date;
1013 p_copy_to (p_to).element_entry_id :=
1014 p_copy_from (p_from).element_entry_id;
1015 p_copy_to (p_to).assignment_id :=
1016 p_copy_from (p_from).assignment_id;
1017 --
1018 end copy1;
1019
1020 --
1021 --------------------------------------------------------------------------------
1022 -- SORT_CHANGES
1023 --------------------------------------------------------------------------------
1024 procedure sort_changes1 (
1025 p_detail_tab in out nocopy l_detailed_output_table_type
1026 ) is
1027 --
1028 l_temp_table l_detailed_output_table_type;
1029 --**x NUMBER;
1030 --
1031 begin
1032 if p_detail_tab.count > 0 then
1033 for i in p_detail_tab.first .. p_detail_tab.last
1034 loop
1035 --x := i + 1;
1036 for j in i + 1 .. p_detail_tab.last
1037 loop
1038 if p_detail_tab (j).effective_date <
1039 p_detail_tab (i).effective_date then
1040 copy1 (p_detail_tab, j, l_temp_table, 1);
1041 copy1 (p_detail_tab, i, p_detail_tab, j);
1042 copy1 (l_temp_table, 1, p_detail_tab, i);
1043 elsif p_detail_tab (j).effective_date =
1044 p_detail_tab (i).effective_date and p_detail_tab (j).creation_date <
1045 p_detail_tab (i).creation_date then
1046 copy1 (p_detail_tab, j, l_temp_table, 1);
1047 copy1 (p_detail_tab, i, p_detail_tab, j);
1048 copy1 (l_temp_table, 1, p_detail_tab, i);
1049 end if;
1050 end loop;
1051 end loop;
1052 end if;
1053 --
1054
1055 --
1056 end sort_changes1;
1057
1058
1059
1060 begin
1061
1062
1063 fnd_file.put_line (fnd_file.log, 'Entering Assignment Action Code');
1064 /* Get the Parameters'value */
1065 pay_no_eerr_status_control.get_all_parameters (
1066 p_payroll_action_id,
1067 g_business_group_id,
1068 g_legal_employer_id,
1069 g_archive,
1070 g_effective_start_date, --14507939
1071 g_effective_date
1072 );
1073 -- g_effective_start_date := add_months (g_effective_date, -12) + 1; --14507939
1074
1075 --
1076 --
1077 /* Get the Absence Days after which the employee should be shown
1078 terminated */
1079
1080
1081 open get_global_value ('NO_ABSENCE_OTHERS_TERMINATION_LIMIT',g_effective_date);
1082 fetch get_global_value into g_absence_termination_limit;
1083 close get_global_value;
1084 --
1085 --
1086 /* Get the Hour Change Limit that should be igmored while showing the
1087 change in hours */
1088
1089 open get_global_value ('NO_HOUR_CHANGE_LIMIT',g_effective_date);
1090 fetch get_global_value into g_hour_change_limit;
1091 close get_global_value;
1092 --
1093 --
1094 /* Get the Min Average Weekly Hours below which the employee should
1095 be shown terminated */
1096 open get_global_value ('NO_MIN_AVG_WEEKLY_HOURS',g_effective_date);
1097 fetch get_global_value into g_min_avg_weekly_hours;
1098 close get_global_value;
1099 --
1100 --
1101 /* get the No of weeks after which the employee shoud be shown as
1102 terminated if the Average weekly hours continues to be less than
1103 Min Average Weekly Hours*/
1104 open get_global_value ('NO_HOURS_CHANGE_WEEKS',g_effective_date);
1105 fetch get_global_value into g_no_hours_change_weeks;
1106 g_no_hours_change_weeks := g_no_hours_change_weeks * 7;
1107 close get_global_value;
1108
1109 --
1110 --
1111
1112 if g_archive = 'Y' then
1113 open csr_get_table_id ('PAY_ELEMENT_ENTRIES_F');
1114 fetch csr_get_table_id into l_table1;
1115 close csr_get_table_id;
1116 --
1117 --
1118 open csr_get_table_id ('PAY_ELEMENT_ENTRY_VALUES_F');
1119 fetch csr_get_table_id into l_table2;
1120 close csr_get_table_id;
1121 --
1122 --
1123 open csr_get_table_id ('PER_ALL_ASSIGNMENTS_F');
1124 fetch csr_get_table_id into l_table3;
1125 close csr_get_table_id;
1126 --
1127 --
1128 open csr_event_group_details (
1129 'NO_REGISTER_REPORT_EVG',
1130 g_business_group_id
1131 );
1132 fetch csr_event_group_details into l_event_group_id;
1133 close csr_event_group_details;
1134
1135 --
1136 --
1137
1138 for i in csr_all_local_unit_details (g_legal_employer_id)
1139 loop
1140 for j in csr_employee_details (
1141 i.local_unit_id,
1142 g_effective_start_date,
1143 g_effective_date
1144 )
1145 loop
1146 l_national_identifier := pay_no_eerr_status_control.check_national_identifier (
1147 j.national_identifier
1148 );
1149
1150 if l_national_identifier <> 'INVALID_ID' then
1151 --
1152 --
1153 /* Initialize the variables */
1154 l_lu_org_no := i.local_unit_org_no;
1155 l_local_unit_value := null;
1156 l_lu_change_effective_date := null;
1157 l_lu_change_effective_date1 := null;
1158 l_job_change_effective_date := null;
1159 l_job_change_effective_date1 := null;
1160 l_hour_element_entry_id := null;
1161 l_hour_year_change_flag := 'N';
1162 l_hour_value := null;
1163 l_hour_change_effective_date := null;
1164 l_hour_value_reported := null;
1165 l_element_entry_id := null;
1166 l_houry_change_flag := 'N';
1167 l_sickness_unpaid_end := null;
1168 l_sickness_unpaid_start := null;
1169 l_empl_start_date := null;
1170 l_emp_start_date := null;
1171 l_emp_end_date := null;
1172 l_abs_start_date := null;
1173 l_abs_end_date := null;
1174 l_element_entry_id := null;
1175 l_job_value := j.position_code;
1176 l_prev_hour_flag := 'Y';
1177 l_abs_emp_start_date := null;
1178 l_prev_hour_flag := 'Y';
1179 --
1180 --
1181 /* Get the Start Date */
1182 open csr_start_date (j.assignment_id);
1183 fetch csr_start_date into l_emp_start_date;
1184 close csr_start_date;
1185 l_empl_start_date := l_emp_start_date;
1186 l_emp_end_date := j.effective_end_date;
1187
1188 /* for k in csr_absence_days (j.person_id)
1189 loop
1190 -- l_emp_end_date := k.date_start - 1;
1191 l_emp_start_date := k.date_end + 1;
1192 l_abs_start_date := k.date_start;
1193 l_abs_end_date := k.date_end;
1194 end loop;*/
1195
1196
1197 for k in csr_absence_start_days (j.person_id)
1198 loop
1199
1200 l_emp_end_date := k.date_start - 1;
1201 loop
1202 hr_wrk_sch_pkg.get_per_asg_schedule (
1203 p_person_assignment_id => j.assignment_id,
1204 p_period_start_date => l_emp_end_date,
1205 p_period_end_date => l_emp_end_date + 1,
1206 p_schedule_category => null,
1207 p_include_exceptions => 'Y',
1208 p_busy_tentative_as => 'FREE',
1209 x_schedule_source => l_schedule_source,
1210 x_schedule => l_schedule,
1211 x_return_status => l_return_status,
1212 x_return_message => l_return_message
1213 );
1214
1215
1216 if l_schedule_source in ('PER_ASG', 'BUS_GRP', 'HR_ORG', 'JOB', 'POS', 'LOC') then
1217
1218 -- hr_utility.trace('Source Found :- '||l_schedule_source);
1219 l_wrk_schd_return :=
1220 hr_loc_work_schedule.calc_sch_based_dur (
1221 j.assignment_id,
1222 l_days_or_hours,
1223 l_include_event,
1224 l_emp_end_date,
1225 l_emp_end_date,
1226 l_start_time_char,
1227 l_end_time_char,
1228 l_duration
1229 );
1230 if l_duration = 1 then
1231 exit;
1232 end if;
1233 l_emp_end_date := l_emp_end_date - 1;
1234 else
1235 exit ;
1236 end if;
1237 end loop;
1238 end loop; /* csr_absence_start_days */
1239
1240 l_prev_last_date := g_effective_start_date - 1;
1241 loop /* 5648385 Find the last working day of the previous period */
1242
1243 hr_wrk_sch_pkg.get_per_asg_schedule (
1244 p_person_assignment_id => j.assignment_id,
1245 p_period_start_date => l_prev_last_date,
1246 p_period_end_date => l_prev_last_date + 1,
1247 p_schedule_category => null,
1248 p_include_exceptions => 'Y',
1249 p_busy_tentative_as => 'FREE',
1250 x_schedule_source => l_schedule_source,
1251 x_schedule => l_schedule,
1252 x_return_status => l_return_status,
1253 x_return_message => l_return_message
1254 );
1255
1256 if l_schedule_source in ('PER_ASG', 'BUS_GRP', 'HR_ORG', 'JOB', 'POS', 'LOC') then
1257 l_wrk_schd_return := hr_loc_work_schedule.calc_sch_based_dur
1258 ( j.assignment_id, l_days_or_hours, l_include_event,
1259 l_prev_last_date, l_prev_last_date, l_start_time_char,
1260 l_end_time_char, l_duration
1261 );
1262
1263 IF l_duration = 1 THEN
1264 exit;
1265 END IF;
1266
1267 l_prev_last_date := l_prev_last_date - 1;
1268 else
1269 exit;
1270 end if;
1271 end loop;
1272 l_abs_emp_start_date := l_emp_start_date;
1273
1274 for k in csr_absence_end_days (j.person_id,l_prev_last_date)
1275 loop
1276 l_emp_start_date := k.date_end + 1;
1277
1278
1279 loop /* 5525977 Find the week ends and public holidays */
1280
1281 hr_wrk_sch_pkg.get_per_asg_schedule (
1282 p_person_assignment_id => j.assignment_id,
1283 p_period_start_date => l_emp_start_date -1 ,
1284 p_period_end_date => l_emp_start_date,
1285 p_schedule_category => null,
1286 p_include_exceptions => 'Y',
1287 p_busy_tentative_as => 'FREE',
1288 x_schedule_source => l_schedule_source,
1289 x_schedule => l_schedule,
1290 x_return_status => l_return_status,
1291 x_return_message => l_return_message
1292 );
1293
1294 if l_schedule_source in ('PER_ASG', 'BUS_GRP', 'HR_ORG', 'JOB', 'POS', 'LOC') then
1295 l_wrk_schd_return :=
1296 hr_loc_work_schedule.calc_sch_based_dur (
1297 j.assignment_id,
1298 l_days_or_hours,
1299 l_include_event,
1300 l_emp_start_date,
1301 l_emp_start_date,
1302 l_start_time_char,
1303 l_end_time_char,
1304 l_duration
1305 );
1306
1307 if l_duration = 1 then
1308 exit;
1309 end if;
1310 --l_emp_start_date := k.date_end + 1;
1311 l_emp_start_date := l_emp_start_date + 1;
1312 else
1313 exit;
1314 end if;
1315
1316 end loop;
1317 if l_emp_start_date > g_effective_Date then
1318 l_emp_start_date := l_abs_emp_start_date;
1319 end if;
1320 end loop; /* csr_absence_start_days */
1321
1322
1323 if j.hourly_salaried_code = 'H' then
1324 --l_prev_hour_flag := 'N';
1325 l_prev_hour_flag := 'Y';
1326 open csr_get_element_entry (
1327 j.assignment_id,
1328 g_effective_date,
1329 'Average Weekly Hours'
1330 );
1331 fetch csr_get_element_entry into l_hour_element_entry_id;
1332 close csr_get_element_entry;
1333
1334 if l_hour_element_entry_id is null then
1335 /* Set the Flag to 'Y' when no hours change during the year*/
1336 l_houry_change_flag := 'Y';
1337 else
1338 l_hour_year_change_flag := 'Y';
1339 open csr_get_curr_element_value (
1340 l_hour_element_entry_id,
1341 g_effective_date
1342 );
1343 fetch csr_get_curr_element_value into l_hour_value;
1344 close csr_get_curr_element_value;
1345 /*end if;
1346 for i in csr_get_element_value (
1347 l_hour_element_entry_id,
1348 g_effective_start_date,
1349 g_effective_date
1350 )
1351 loop
1352 if i.entry_value < g_min_avg_weekly_hours then
1353 -- l_emp_start_date := null;
1354
1355
1356 l_emp_end_date :=
1357 /add_months (
1358 last_day (i.effective_start_date),
1359 -1
1360 );/
1361 trunc(i.effective_start_date,'MM') -1 ;
1362
1363 l_hour_value := null;
1364 l_hour_change_effective_date := null;
1365 l_prev_hour_flag := 'Y';
1366 else
1367 l_hour_value := i.entry_value;
1368
1369 if l_prev_hour_flag = 'Y' then
1370 l_emp_start_date :=
1371
1372
1373 trunc(i.effective_start_date,'MM');
1374 /add_months (
1375 last_day (i.effective_start_date),
1376 -1
1377 )
1378 + 1;/
1379 l_hour_change_effective_date :=
1380 i.effective_start_date;
1381 else
1382 l_hour_change_effective_date :=
1383 i.effective_start_date;
1384 l_prev_hour_flag := 'N';
1385 end if;
1386 end if;
1387 end loop;*/
1388
1389 for i in csr_get_element_value (l_hour_element_entry_id,g_effective_start_date, g_effective_date )
1390 loop
1391 l_hour_value := i.entry_value;
1392 l_hour_old_value := 0;
1393 open csr_get_curr_element_value (l_hour_element_entry_id,trunc(i.effective_start_date,'MM') - 1);
1394 fetch csr_get_curr_element_value into l_hour_old_value;
1395 close csr_get_curr_element_value;
1396
1397 if trunc(l_empl_start_date,'MM') <> trunc(i.effective_start_date,'MM') then
1398 /* if hourly value is < avg and the old value is > avg then populate termination record */
1399 if nvl(l_hour_old_value,0) >= g_min_avg_weekly_hours
1400 and nvl(l_hour_value,0) < g_min_avg_weekly_hours then
1401 l_emp_end_date := trunc(i.effective_start_date,'MM') - 1;
1402 l_hour_value := null;
1403 l_hour_change_effective_date := null;
1404 l_prev_hour_flag := 'Y';
1405 end if;
1406 if nvl(l_hour_old_value,0) < g_min_avg_weekly_hours
1407 and nvl(l_hour_value,0) >= g_min_avg_weekly_hours then
1408 l_emp_start_date := trunc(i.effective_start_date,'MM') ;
1409 /* if hourly value is less then avarage, should not populate Modified record */
1410 elsif nvl(l_hour_value,0) >= g_min_avg_weekly_hours then
1411 l_hour_change_effective_date := i.effective_start_date ;
1412 end if;
1413 else
1414 /* if hourly value is less then avarage, should not populate 8I record */
1415 if l_hour_value >= g_min_avg_weekly_hours then
1416 l_emp_start_date := l_empl_start_date ;
1417 end if;
1418 end if;
1419 end loop;
1420 end if; /* l_hour_element_entry_id not null */
1421 end if;
1422
1423 open csr_get_sick_unpaid_entry (
1424 j.assignment_id,
1425 g_effective_start_date,
1426 g_effective_date,
1427 'Sickness Unpaid'
1428 );
1429 fetch csr_get_sick_unpaid_entry into l_element_entry_id;
1430 close csr_get_sick_unpaid_entry;
1431
1432 begin
1433 pay_interpreter_pkg.entry_affected (
1434 p_element_entry_id => l_element_entry_id,
1435 p_assignment_action_id => null,
1436 p_assignment_id => j.assignment_id,
1437 p_mode => 'DATE_EARNED',
1438 p_process => 'U',
1439 p_event_group_id => l_event_group_id,
1440 p_process_mode => 'ENTRY_EFFECTIVE_DATE' --ENTRY_CREATION_DATE
1441 ,
1442 p_start_date => g_effective_start_date -1 ,
1443 p_end_date => g_effective_date,
1444 t_detailed_output => l_detail_tab,
1445 t_proration_dates => l_proration_dates,
1446 t_proration_change_type => l_proration_changes,
1447 t_proration_type => l_pro_type_tab
1448 );
1449 exception
1450 when no_data_found then
1451 l_detail_tab.delete;
1452 when others then
1453 l_detail_tab.delete;
1454 end;
1455
1456 sort_changes (l_detail_tab);
1457
1458 if l_detail_tab.count <> 0 then /* Start If for count check */
1459 for cnt in l_detail_tab.first .. l_detail_tab.last
1460 loop
1461 /*begin
1462 if (l_detail_tab (cnt).dated_table_id = l_table1)
1463 or (l_detail_tab (cnt).dated_table_id = l_table2) then
1464 if csr_get_element_det%isopen then
1465 close csr_get_element_det;
1466 end if;
1467
1468 open csr_get_element_det (
1469 'Sickness Unpaid',
1470 'Start Date',
1471 j.assignment_id,
1472 l_detail_tab (cnt).effective_date
1473 );
1474 fetch csr_get_element_det into l_sickness_unpaid_start;
1475 close csr_get_element_det;
1476
1477 if csr_get_element_det%isopen then
1478 close csr_get_element_det;
1479 end if;
1480
1481 open csr_get_element_det (
1482 'Sickness Unpaid',
1483 'End Date',
1484 j.assignment_id,
1485 l_detailed_output (cnt).effective_date
1486 );
1487 fetch csr_get_element_det into l_sickness_unpaid_end;
1488 close csr_get_element_det;
1489 end if;
1490 end;*/
1491 if l_detail_tab (cnt).dated_table_id = l_table1 then
1492 l_sickness_unpaid_start := l_detail_tab (cnt).effective_date ;
1493 l_emp_end_date := l_sickness_unpaid_start - 1;
1494 end if;
1495 end loop;
1496 end if;
1497
1498
1499 /* if l_sickness_unpaid_start is not null then
1500 l_emp_end_date := l_sickness_unpaid_start;
1501
1502 if l_sickness_unpaid_end >= g_effective_date then
1503 l_emp_start_date := null;
1504 else
1505 l_emp_start_date := l_sickness_unpaid_end + 1;
1506 end if;
1507 end if;*/
1508
1509 begin
1510 pay_interpreter_pkg.entry_affected (
1511 p_element_entry_id => null,
1512 p_assignment_action_id => null,
1513 p_assignment_id => j.assignment_id,
1514 p_mode => 'DATE_PROCESSED',
1515 p_process => 'U',
1516 p_event_group_id => l_event_group_id,
1517 p_process_mode => 'ENTRY_EFFECTIVE_DATE' --ENTRY_CREATION_DATE
1518 ,
1519 p_start_date => g_effective_start_date
1520 - 1,
1521 p_end_date => g_effective_date,
1522 t_detailed_output => l_detailed_output1,
1523 t_proration_dates => l_proration_dates,
1524 t_proration_change_type => l_proration_changes,
1525 t_proration_type => l_pro_type_tab
1526 );
1527 exception
1528 when no_data_found then
1529 l_detailed_output1.delete;
1530 when others then
1531 l_detailed_output1.delete;
1532 end;
1533
1534 merge_cnt := 1 ;
1535 l_detailed_output := l_empty_detailed_output;
1536 if l_detailed_output1.count <> 0 then
1537 for i in l_detailed_output1.first .. l_detailed_output1.last
1538 loop
1539 l_detailed_output(merge_cnt).effective_date := l_detailed_output1(i).effective_date;
1540 l_detailed_output(merge_cnt).creation_date := l_detailed_output1(i).creation_date ;
1541 l_detailed_output(merge_cnt).column_name := l_detailed_output1(i).column_name;
1542 l_detailed_output(merge_cnt).new_value := l_detailed_output1(i).new_value;
1543 l_detailed_output(merge_cnt).change_values := l_detailed_output1(i).change_values;
1544 l_detailed_output(merge_cnt).old_value := l_detailed_output1(i).old_value;
1545 l_detailed_output(merge_cnt).dated_table_id := l_detailed_output1(i).dated_table_id ;
1546 l_detailed_output(merge_cnt).datetracked_event := l_detailed_output1(i).datetracked_event;
1547 l_detailed_output(merge_cnt).surrogate_key := l_detailed_output1(i).surrogate_key ;
1548 l_detailed_output(merge_cnt).update_type := l_detailed_output1(i).update_type ;
1549 l_detailed_output(merge_cnt).proration_type := l_detailed_output1(i).proration_type;
1550 l_detailed_output(merge_cnt).change_mode := l_detailed_output1(i).change_mode;
1551 l_detailed_output(merge_cnt).element_entry_id := l_detailed_output1(i).element_entry_id;
1552 l_detailed_output(merge_cnt).assignment_id := j.assignment_id;
1553 merge_cnt := merge_cnt + 1;
1554 end loop;
1555 end if;
1556
1557 for l_get_all_assignments in csr_get_all_assignments (j.person_id,
1558 j.assignment_id,
1559 i.local_unit_id)
1560 loop
1561 begin
1562 pay_interpreter_pkg.entry_affected (
1563 p_element_entry_id => null,
1564 p_assignment_action_id => null,
1565 p_assignment_id => l_get_all_assignments.assignment_id,
1566 p_mode => 'DATE_PROCESSED',
1567 p_process => 'U',
1568 p_event_group_id => l_event_group_id,
1569 p_process_mode => 'ENTRY_EFFECTIVE_DATE',
1570 p_start_date => g_effective_start_date
1571 - 1, /* 5496538 */
1572 p_end_date => g_effective_date,
1573 t_detailed_output => l_detailed_output3,
1574 t_proration_dates => l_proration_dates,
1575 t_proration_change_type => l_proration_changes,
1576 t_proration_type => l_pro_type_tab
1577 );
1578 exception
1579 when no_data_found then
1580 l_detailed_output3.delete;
1581 when others then
1582 l_detailed_output3.delete;
1583 end;
1584 if l_detailed_output3.count <> 0 then
1585 for i in l_detailed_output3.first .. l_detailed_output3.last
1586 loop
1587 if l_detailed_output3(i).column_name = 'NORMAL_HOURS' OR l_detailed_output3(i).column_name = 'ASSIGNMENT_STATUS_TYPE_ID' OR (l_detailed_output3(i).dated_table_id = l_table3 and l_detailed_output3(i).update_type = 'I' )then
1588 l_detailed_output(merge_cnt).effective_date := l_detailed_output3(i).effective_date;
1589 l_detailed_output(merge_cnt).creation_date := l_detailed_output3(i).creation_date ;
1590 l_detailed_output(merge_cnt).column_name := l_detailed_output3(i).column_name;
1591 l_detailed_output(merge_cnt).new_value := l_detailed_output3(i).new_value;
1592 l_detailed_output(merge_cnt).change_values := l_detailed_output3(i).change_values;
1593 l_detailed_output(merge_cnt).old_value := l_detailed_output3(i).old_value;
1594 l_detailed_output(merge_cnt).dated_table_id := l_detailed_output3(i).dated_table_id ;
1595 l_detailed_output(merge_cnt).datetracked_event := l_detailed_output3(i).datetracked_event;
1596 l_detailed_output(merge_cnt).surrogate_key := l_detailed_output3(i).surrogate_key ;
1597 l_detailed_output(merge_cnt).update_type := l_detailed_output3(i).update_type ;
1598 l_detailed_output(merge_cnt).proration_type := l_detailed_output3(i).proration_type;
1599 l_detailed_output(merge_cnt).change_mode := l_detailed_output3(i).change_mode;
1600 l_detailed_output(merge_cnt).element_entry_id := l_detailed_output3(i).element_entry_id;
1601 l_detailed_output(merge_cnt).assignment_id := l_get_all_assignments.assignment_id;
1602 merge_cnt := merge_cnt + 1;
1603 end if;
1604
1605 end loop;
1606 end if;
1607 end loop;
1608
1609 sort_changes1 (l_detailed_output);
1610
1611
1612 if l_detailed_output.count <> 0 then
1613
1614
1615 /* Start If for count check */
1616 for cnt in
1617 l_detailed_output.first .. l_detailed_output.last
1618 loop
1619
1620
1621
1622 /* Start loop for Column Check*/
1623 l_hour_effective_end_date := null;
1624 l_new_scl := null;
1625 l_old_scl := null;
1626 l_new_lu := null;
1627 l_old_lu := null;
1628 l_old_job_value := null;
1629 l_new_job_value := null;
1630
1631 if l_detailed_output(cnt).dated_table_id = l_table3 and l_detailed_output (cnt).update_type = 'I' then
1632
1633 rl_assignment_details.normal_hours := 0;
1634 rl_assignment_details.hourly_salaried_code := null;
1635 rl_assignment_details.position_code := null;
1636 rl_assignment_details.frequency := null;
1637
1638 open csr_get_assignment_details(l_detailed_output(cnt).effective_Date,
1639 l_detailed_output (cnt).assignment_id,-- j.assignment_id,
1640 i.local_unit_id);
1641 fetch csr_get_assignment_details into rl_assignment_details;
1642 close csr_get_assignment_details;
1643
1644 --
1645 if rl_assignment_details.normal_hours is not null and ( j.hourly_salaried_code = 'S'
1646 or l_houry_change_flag = 'Y') then
1647
1648 l_hour_value := get_assignment_all_hours (
1649 l_detailed_output (cnt).assignment_id,
1650 j.person_id,
1651 l_detailed_output (cnt).effective_date,
1652 rl_assignment_details.normal_hours,
1653 i.local_unit_id
1654 );
1655
1656 l_hour_change_effective_date :=
1657 l_detailed_output (cnt).effective_date;
1658 end if;
1659 -- end if;
1660 elsif l_detailed_output (cnt).column_name =
1661 'NORMAL_HOURS'
1662 and ( j.hourly_salaried_code = 'S'
1663 or l_houry_change_flag = 'Y'
1664 ) then
1665
1666 l_hour_year_change_flag := 'Y';
1667
1668 begin
1669 l_hour_value_primary := fnd_number.canonical_to_number (
1670 nvl (
1671 l_detailed_output (
1672 cnt
1673 ).new_value,
1674 substr (
1675 l_detailed_output (
1676 cnt
1677 ).change_values,
1678 instr (
1679 l_detailed_output (
1680 cnt
1681 ).change_values,
1682 '->'
1683 )
1684 + 3
1685 )
1686 )
1687 );
1688
1689
1690 --fnd_file.put_line (fnd_file.log, 'l_hour_value_primary :-'||l_hour_value_primary);
1691 --fnd_file.put_line (fnd_file.log, 'j.assignment_id :-'||l_hour_value_primary);
1692
1693 exception
1694 when value_error then
1695 l_hour_value_primary := 0;
1696 end;
1697
1698
1699 l_hour_change_effective_date :=
1700 l_detailed_output (cnt).effective_date;
1701 --
1702 l_hour_value := get_assignment_all_hours (
1703 l_detailed_output (cnt).assignment_id,
1704 j.person_id,
1705 l_detailed_output (cnt).effective_date,
1706 l_hour_value_primary,
1707 i.local_unit_id
1708 );
1709 l_hour_old_value := 0;
1710 l_prev_hour_value_primary := 0 ;
1711 l_prev_hour_eff_date := null ;
1712 for i in previous_hour_value (l_detailed_output (cnt).assignment_id, l_hour_change_effective_date)
1713 loop
1714 if i.normal_hours <> l_hour_value_primary then
1715 l_prev_hour_value_primary := i.normal_hours ;
1716 l_prev_hour_eff_date := i.effective_start_date ;
1717 exit;
1718 end if;
1719 end loop;
1720 l_hour_old_value := get_assignment_all_hours (
1721 l_detailed_output (cnt).assignment_id,
1722 j.person_id,
1723 l_prev_hour_eff_date,
1724 l_prev_hour_value_primary,
1725 i.local_unit_id
1726 );
1727 /* IF ends for When Column = NORMAL_HOURS*/
1728 if nvl (l_hour_value, 0) < g_min_avg_weekly_hours then
1729 for cnt1 in
1730 l_detailed_output.first .. l_detailed_output.last
1731 loop
1732 begin
1733 l_new_hour := 0;
1734 l_new_hour := fnd_number.canonical_to_number (
1735 nvl (
1736 l_detailed_output (
1737 cnt1
1738 ).new_value,
1739 substr (
1740 l_detailed_output (
1741 cnt1
1742 ).change_values,
1743 instr (
1744 l_detailed_output (
1745 cnt1
1746 ).change_values,
1747 '->'
1748 )
1749 + 3
1750 )
1751 )
1752 );
1753 exception
1754 when value_error then
1755 l_new_hour := 0;
1756 end;
1757
1758 if l_detailed_output (cnt1).column_name =
1759 'NORMAL_HOURS'
1760 and l_detailed_output (cnt1).effective_date >
1761 l_hour_change_effective_date and nvl(l_new_hour,0) >= g_min_avg_weekly_hours then
1762 l_hour_effective_end_date :=
1763 l_detailed_output (cnt1).effective_date;
1764 exit;
1765 end if;
1766 end loop;
1767
1768
1769 if nvl (l_hour_effective_end_date, g_effective_date)
1770 - nvl (
1771 l_hour_change_effective_date,
1772 g_effective_start_date
1773 ) > g_no_hours_change_weeks then
1774 -- l_hour_value := null;
1775
1776 if l_emp_start_date <> l_hour_change_effective_date
1777 and nvl(l_hour_old_value,0) >= g_min_avg_weekly_hours then
1778
1779 -- l_emp_start_date := null;
1780 l_emp_end_date :=
1781 l_hour_change_effective_date - 1;
1782 l_prev_hour_flag := 'Y';
1783 /* else
1784 if l_emp_start_date is null then
1785 l_emp_start_date := l_hour_change_effective_date;
1786 end if; /* End if of Emp Start Date Null*/
1787
1788 end if;
1789
1790 end if;
1791 /* End if of when min hours remain more than 2 weeks*/
1792 else
1793 /* to check if changes are more than the min limint for Hour change*/
1794 if abs (
1795 nvl (l_hour_value, 0)
1796 - nvl (l_hour_old_value, 0)
1797 ) >= g_hour_change_limit then
1798 /*if l_emp_start_date is null then*/
1799 if l_prev_hour_flag = 'Y' or
1800 (nvl (l_hour_old_value, 0) < g_min_avg_weekly_hours) /* 5512163 */
1801 then
1802 --or nvl (l_hour_value_reported, 0) = 0 then /* 5498504 */
1803 l_emp_start_date :=
1804 l_hour_change_effective_date;
1805
1806 end if;
1807 l_prev_hour_flag := 'N';
1808 end if;
1809
1810
1811 end if;
1812 elsif l_detailed_output (cnt).column_name = 'JOB_ID' then
1813 if l_detailed_output (cnt).effective_date >
1814 l_empl_start_date then
1815 l_job_change_effective_date :=
1816 l_detailed_output (cnt).effective_date;
1817 end if;
1818
1819 l_job_id :=
1820 fnd_number.canonical_to_number (
1821 nvl (
1822 l_detailed_output (cnt).new_value,
1823 fnd_number.canonical_to_number (
1824 substr (
1825 l_detailed_output (cnt).change_values,
1826 instr (
1827 l_detailed_output (cnt).change_values,
1828 '->'
1829 )
1830 + 3
1831 )
1832 )
1833 )
1834 );
1835 open csr_get_job_position_code (
1836 j.assignment_id,
1837 l_detailed_output (cnt).effective_date,
1838 l_job_id
1839 );
1840 fetch csr_get_job_position_code into l_job_value;
1841 close csr_get_job_position_code;
1842 elsif l_detailed_output (cnt).column_name =
1843 'SOFT_CODING_KEYFLEX_ID' then
1844 /* if l_detailed_output (cnt).effective_date >
1845 l_empl_start_date then
1846 l_lu_change_effective_date :=
1847 l_detailed_output (cnt).effective_date;
1848 end if;*/
1849 begin
1850 l_local_unit_value := nvl (
1851 l_detailed_output (
1852 cnt
1853 ).new_value,
1854 fnd_number.canonical_to_number (
1855 substr (
1856 l_detailed_output (
1857 cnt
1858 ).change_values,
1859 instr (
1860 l_detailed_output (
1861 cnt
1862 ).change_values,
1863 '->'
1864 )
1865 + 3
1866 )
1867 )
1868 );
1869 /* open csr_get_org_no (fnd_number.canonical_to_number (l_local_unit_value));
1870 fetch csr_get_org_no into l_lu_org_no;
1871 close csr_get_org_no;*/
1872 l_old_scl := substr (
1873 l_detailed_output (cnt).change_values,
1874 0,
1875 instr (
1876 l_detailed_output (cnt).change_values,
1877 '->'
1878 )
1879 - 1
1880 );
1881 l_new_scl := substr (
1882 l_detailed_output (cnt).change_values,
1883 instr (
1884 l_detailed_output (cnt).change_values,
1885 '->'
1886 )
1887 + 3
1888 );
1889
1890 if l_old_scl = '<null> ' then
1891 l_old_scl := '0';
1892 l_local_unit_value := l_new_scl;
1893 open csr_get_pos_scl (fnd_number.canonical_to_number (l_new_scl));
1894 fetch csr_get_pos_scl into l_job_value;
1895 close csr_get_pos_scl;
1896
1897 if l_detailed_output (cnt).effective_date >
1898 l_empl_start_date then
1899 l_lu_change_effective_date :=
1900 l_detailed_output (cnt).effective_date;
1901
1902 if l_job_value is not null then
1903 l_job_change_effective_date := l_detailed_output (
1904 cnt
1905 ).effective_date;
1906 end if;
1907 end if;
1908
1909 /* l_local_unit_value := nvl (
1910 l_detailed_output (cnt).new_value,
1911 fnd_number.canonical_to_number (
1912 substr (
1913 l_detailed_output (cnt).change_values,
1914 instr (
1915 l_detailed_output (cnt).change_values,
1916 '->'
1917 )
1918 + 3
1919 )
1920 )
1921 );*/
1922 open csr_get_org_no (fnd_number.canonical_to_number (l_new_scl));
1923 fetch csr_get_org_no into l_lu_org_no;
1924 close csr_get_org_no;
1925 else
1926 /* Code for Local Unit */
1927 open csr_get_lu_scl (fnd_number.canonical_to_number (l_new_scl));
1928 fetch csr_get_lu_scl into l_new_lu;
1929 close csr_get_lu_scl;
1930
1931 open csr_get_lu_scl (fnd_number.canonical_to_number (l_old_scl));
1932 fetch csr_get_lu_scl into l_old_lu;
1933 close csr_get_lu_scl;
1934
1935 if l_old_lu <> l_new_lu then
1936 if l_detailed_output (cnt).effective_date >
1937 l_empl_start_date then
1938 l_lu_change_effective_date := l_detailed_output (
1939 cnt
1940 ).effective_date;
1941 end if;
1942
1943 open csr_get_org_no (
1944 fnd_number.canonical_to_number (l_new_scl)
1945 );
1946 fetch csr_get_org_no into l_lu_org_no;
1947 close csr_get_org_no;
1948 end if;
1949
1950 /* End Code for Local Unit */
1951
1952 /* Code for Position Code */
1953 open csr_get_pos_scl (fnd_number.canonical_to_number (l_new_scl));
1954 fetch csr_get_pos_scl into l_new_job_value;
1955 close csr_get_pos_scl;
1956
1957 open csr_get_pos_scl (fnd_number.canonical_to_number (l_old_scl));
1958 fetch csr_get_pos_scl into l_old_job_value;
1959 close csr_get_pos_scl;
1960
1961 if l_new_job_value <> l_old_job_value then
1962 if l_detailed_output (cnt).effective_date >
1963 l_empl_start_date then
1964 l_job_change_effective_date := l_detailed_output (
1965 cnt
1966 ).effective_date;
1967 end if;
1968
1969 -- begin
1970 if l_new_job_value <> '0' then
1971 l_job_value :=
1972 to_char (l_new_job_value);
1973 else
1974 l_job_value := null;
1975 end if;
1976 /* exception
1977 when others then
1978 hr_utility.trace('Step 12');*/
1979 -- end;
1980 end if;
1981 /* End Code for Position Code */
1982 end if;
1983
1984 if l_new_job_value <> 0 then
1985 l_job_value := l_new_job_value;
1986 else
1987 l_job_value := null;
1988 end if;
1989 exception
1990 when others then
1991 fnd_file.put_line (
1992 fnd_file.log,
1993 'Exception'
1994 );
1995 end;
1996 elsif l_detailed_output (cnt).column_name =
1997 'ASSIGNMENT_STATUS_TYPE_ID' then
1998
1999 open csr_get_assignment_status (
2000 fnd_number.canonical_to_number (l_detailed_output (cnt).new_value)
2001 );
2002 fetch csr_get_assignment_status into l_user_status;
2003 close csr_get_assignment_status;
2004
2005 open csr_get_assignment_status (
2006 fnd_number.canonical_to_number (l_detailed_output (cnt).old_value)
2007 );
2008 fetch csr_get_assignment_status into l_old_user_status;
2009 close csr_get_assignment_status;
2010
2011 -- if l_user_status in ('TERM_ASSIGN', 'SUSP_ASSIGN') then
2012 if l_user_status = 'TERM_ASSIGN' then
2013
2014 if j.assignment_id <> l_detailed_output (cnt).assignment_id then
2015 if ( j.hourly_salaried_code = 'S'
2016 or l_houry_change_flag = 'Y') then
2017
2018 l_hour_value := get_assignment_all_hours (
2019 l_detailed_output (cnt).assignment_id,
2020 j.person_id,
2021 l_detailed_output (cnt).effective_date,
2022 0,
2023 i.local_unit_id
2024 );
2025
2026 l_hour_change_effective_date := l_detailed_output (cnt).effective_date;
2027
2028 end if;
2029 else
2030 l_emp_end_date :=
2031 l_detailed_output (cnt).effective_date;
2032 end if;
2033 elsif l_user_status = 'ACTIVE_ASSIGN' and l_old_user_status <> 'SUSP_ASSIGN' then
2034 l_emp_start_date :=
2035 l_detailed_output (cnt).effective_date;
2036 end if;
2037 end if;
2038 --l_hour_value := 6;
2039
2040 /* if l_empl_start_date = l_emp_start_date
2041 and nvl (l_hour_value, 0) = 0 then
2042 l_emp_start_date := null;
2043 end if;*/
2044 end loop; /* End loop for Column Check */
2045 end if;
2046
2047 if (l_hour_year_change_flag = 'N' and j.hourly_salaried_code = 'S') or
2048 (l_hour_year_change_flag = 'N' and j.hourly_salaried_code = 'H' and l_houry_change_flag = 'Y' ) then
2049
2050 l_hour_value := get_assignment_all_hours (
2051 j.assignment_id,
2052 j.person_id,
2053 g_effective_Date,
2054 j.normal_hours,
2055 i.local_unit_id
2056 );
2057 /* l_hour_value := find_total_hour (
2058 j.normal_hours,
2059 j.frequency
2060 );*/
2061 end if;
2062
2063 if l_emp_start_date is not null
2064 and ( nvl (l_hour_value, 0) >= g_min_avg_weekly_hours or l_emp_end_date is not null )
2065 and l_lu_org_no is not null then
2066 if l_hour_change_effective_date <= l_emp_start_date then
2067 l_hour_change_effective_date := null;
2068 end if;
2069 if l_lu_change_effective_date <= l_emp_start_date then
2070 l_lu_change_effective_date := null;
2071 end if;
2072 if l_job_change_effective_date <= l_emp_start_date then
2073 l_job_change_effective_date := null;
2074 end if;
2075
2076 /*if l_abs_end_date is not null
2077 and l_abs_end_date > l_emp_start_date then
2078 l_emp_start_date := l_abs_end_date + 1;
2079 end if;*/
2080
2081 /* if l_abs_START_date is not null and l_abs_START_date > l_emp_end_Date then
2082 l_emp_end_Date := l_abs_start_date - 1;
2083 end if;*/
2084 /* 5676928 start */
2085 if l_emp_end_date is not null and l_emp_start_date > l_emp_end_date then
2086 l_emp_end_date := null ;
2087 end if;
2088
2089 if l_emp_end_date is not null and l_emp_start_date < l_emp_end_date
2090 and l_hour_change_effective_date = l_emp_end_date + 1 then
2091 l_hour_change_effective_date := null ;
2092 l_hour_value := null;
2093 end if;
2094
2095 if l_hour_change_effective_date > l_emp_end_date then
2096 l_hour_change_effective_date := null;
2097 l_hour_value := null;
2098 end if;
2099
2100 if l_emp_end_date is not null and l_emp_end_date > l_emp_start_date and l_hour_value is not null then
2101 l_hour_value := null;
2102 end if;
2103
2104 /* 5676928 End */
2105 -- end loop;
2106 select pay_assignment_actions_s.nextval
2107 into l_assact_id
2108 from dual;
2109
2110 hr_nonrun_asact.insact (
2111 l_assact_id,
2112 j.assignment_id,
2113 p_payroll_action_id,
2114 20, --P_chunk,
2115 null
2116 ); --
2117 --
2118 -- Create assignment action archive information :-
2119 --
2120 --
2121
2122 /* End loop for for cnt in 1 .. l_detailed_output.count*/
2123
2124
2125 pay_action_information_api.create_action_information (
2126 p_action_information_id => l_action_info_id,
2127 p_action_context_id => l_assact_id,
2128 p_action_context_type => 'AAP',
2129 p_object_version_number => l_ovn,
2130 p_effective_date => g_effective_date,
2131 p_source_id => null,
2132 p_source_text => null,
2133 p_action_information_category => 'EMEA REPORT INFORMATION',
2134 p_action_information1 => 'PYNOEERSTC',
2135 p_action_information2 => g_business_group_id -- Business Group id
2136 ,
2137 p_action_information3 => g_legal_employer_id -- Legal Employer Org ID
2138 ,
2139 p_action_information4 => g_legal_employer_org_no -- Legal Employer Org ID
2140 ,
2141 p_action_information5 => i.local_unit_id,
2142 p_action_information6 => i.local_unit_org_no,
2143 p_action_information7 => j.person_id -- Person id
2144 ,
2145 p_action_information8 => j.national_identifier -- National Identifier
2146 ,
2147 p_action_information9 => j.full_name -- Full Name
2148 ,
2149 p_action_information10 => j.employee_number -- Employee Number
2150 ,
2151 p_action_information11 => fnd_date.date_to_canonical (
2152 l_emp_start_date
2153 ) -- Employment Start Date
2154 --,p_action_information16 => p_time_period_id
2155 ,
2156 p_action_information12 => fnd_number.number_to_canonical(l_hour_value) -- Weekly Working Hours
2157 ,
2158 p_action_information13 => fnd_date.date_to_canonical (
2159 l_hour_change_effective_date
2160 ) -- Date of change of hours
2161 ,
2162 p_action_information14 => fnd_date.date_to_canonical (
2163 l_emp_end_date
2164 ) -- Employment Termination Date
2165 ,
2166 p_action_information15 => l_lu_org_no -- Local Unit Org No
2167 ,
2168 p_action_information16 => fnd_date.date_to_canonical (
2169 l_lu_change_effective_date
2170 ) -- Local Unit Change Date
2171 ,
2172 p_action_information17 => j.position_code -- Occupation
2173 ,
2174 p_action_information18 => fnd_date.date_to_canonical (
2175 l_job_change_effective_date
2176 ) -- Occupation change date
2177 ,
2178 p_action_information19 => fnd_date.date_to_canonical (
2179 l_abs_start_date
2180 ) -- Occupation change date
2181 ,
2182 p_action_information20 => fnd_date.date_to_canonical (
2183 l_abs_end_date
2184 ),
2185 p_assignment_id => j.assignment_id
2186 );
2187 end if;
2188 end if; /* End if for NI check */
2189 end loop; /* End loop for Employee Details*/
2190 end loop; /* End loop for Local Units */
2191 end if; /* End if for Archive */
2192 end;
2193 /* INITIALIZATION CODE */
2194 procedure initialization_code (
2195 p_payroll_action_id in number
2196 ) is
2197 begin
2198 fnd_file.put_line (fnd_file.log, 'Entering Initialization Code');
2199 end initialization_code;
2200 /* ARCHIVE CODE */
2201 procedure archive_code (
2202 p_assignment_action_id in number,
2203 p_effective_date in date
2204 ) is
2205 begin
2206 fnd_file.put_line (fnd_file.log, 'entering archive code');
2207 end archive_code;
2208
2209 --------------------------------------------------------------------------------
2210 -- COPY
2211 --------------------------------------------------------------------------------
2212 procedure copy (
2213 p_copy_from in out nocopy pay_interpreter_pkg.t_detailed_output_table_type,
2214 p_from in number,
2215 p_copy_to in out nocopy pay_interpreter_pkg.t_detailed_output_table_type,
2216 p_to in number
2217 ) is
2218 begin
2219 --
2220 p_copy_to (p_to).dated_table_id := p_copy_from (p_from).dated_table_id;
2221 p_copy_to (p_to).datetracked_event :=
2222 p_copy_from (p_from).datetracked_event;
2223 p_copy_to (p_to).surrogate_key := p_copy_from (p_from).surrogate_key;
2224 p_copy_to (p_to).update_type := p_copy_from (p_from).update_type;
2225 p_copy_to (p_to).column_name := p_copy_from (p_from).column_name;
2226 p_copy_to (p_to).effective_date := p_copy_from (p_from).effective_date;
2227 p_copy_to (p_to).old_value := p_copy_from (p_from).old_value;
2228 p_copy_to (p_to).new_value := p_copy_from (p_from).new_value;
2229 p_copy_to (p_to).change_values := p_copy_from (p_from).change_values;
2230 p_copy_to (p_to).proration_type := p_copy_from (p_from).proration_type;
2231 p_copy_to (p_to).change_mode := p_copy_from (p_from).change_mode;
2232 p_copy_to (p_to).element_entry_id :=
2233 p_copy_from (p_from).element_entry_id;
2234 p_copy_to (p_to).creation_date := p_copy_from (p_from).creation_date;
2235 --
2236 end copy;
2237
2238 --
2239 --------------------------------------------------------------------------------
2240 -- SORT_CHANGES
2241 --------------------------------------------------------------------------------
2242 procedure sort_changes (
2243 p_detail_tab in out nocopy pay_interpreter_pkg.t_detailed_output_table_type
2244 ) is
2245 --
2246 l_temp_table pay_interpreter_pkg.t_detailed_output_table_type;
2247 --**x NUMBER;
2248 --
2249 begin
2250 if p_detail_tab.count > 0 then
2251 for i in p_detail_tab.first .. p_detail_tab.last
2252 loop
2253 --x := i + 1;
2254 for j in i + 1 .. p_detail_tab.last
2255 loop
2256 if p_detail_tab (j).effective_date <
2257 p_detail_tab (i).effective_date then
2258 copy (p_detail_tab, j, l_temp_table, 1);
2259 copy (p_detail_tab, i, p_detail_tab, j);
2260 copy (l_temp_table, 1, p_detail_tab, i);
2261 end if;
2262 end loop;
2263 end loop;
2264 end if;
2265 --
2266
2267 --
2268 end sort_changes;
2269 --
2270
2271 --------------------------------------------------------------------------------
2272
2273
2274 --
2275 --------------------------------------------------------------------------------
2276
2277
2278 procedure populate_details (
2279 p_business_group_id in number,
2280 p_payroll_action_id in varchar2,
2281 p_template_name in varchar2,
2282 p_xml out nocopy clob
2283 ) is
2284 --
2285 --
2286 /* Cursor to fetch Header Information */
2287 cursor csr_get_hdr_info (
2288 p_payroll_action_id number
2289 ) is
2290 select action_information1, action_information2 business_group_id,
2291 action_information3
2292 legal_employer_id,
2293 action_information4
2294 legal_employer_name,
2295 action_information5
2296 legal_employer_org_no,
2297 action_information6 local_unit_id,
2298 action_information7
2299 local_unit_name,
2300 action_information8 local_unit_org_no, effective_date
2301 from pay_action_information pai
2302 where action_context_type = 'PA'
2303 and action_context_id = p_payroll_action_id
2304 and action_information_category = 'EMEA REPORT INFORMATION'
2305 and action_information1 = 'PYNOEERSTC';
2306
2307 --
2308 --
2309 /* Cursor to fetch Detail Information */
2310 --
2311 --
2312 cursor csr_get_detail_info (
2313 p_payroll_action_id varchar2,
2314 p_legal_employer varchar2,
2315 p_local_unit_id varchar2
2316 ) is
2317 select action_information2, action_information3, action_information4,
2318 action_information5, action_information6, action_information7,
2319 action_information8, action_information9, action_information10,
2320 fnd_date.canonical_to_date (action_information11)
2321 action_information11,
2322 action_information12,
2323 fnd_date.canonical_to_date (action_information13)
2324 action_information13,
2325 fnd_date.canonical_to_date (action_information14)
2326 action_information14,
2327 action_information15,
2328 fnd_date.canonical_to_date (action_information16)
2329 action_information16,
2330 action_information17,
2331 fnd_date.canonical_to_date (action_information18)
2332 action_information18,
2333 fnd_date.canonical_to_date (action_information19)
2334 action_information19,
2335 fnd_date.canonical_to_date (action_information20)
2336 action_information20
2337 from pay_payroll_actions paa,
2338 pay_assignment_actions assg,
2339 pay_action_information pai
2340 where paa.payroll_action_id = p_payroll_action_id
2341 and assg.payroll_action_id = paa.payroll_action_id
2342 and pai.action_context_id = assg.assignment_action_id
2343 and pai.action_context_type = 'AAP'
2344 and pai.action_information_category = 'EMEA REPORT INFORMATION'
2345 and pai.action_information1 = 'PYNOEERSTC'
2346 and pai.action_information3 = p_legal_employer
2347 and pai.action_information5 = p_local_unit_id;
2348
2349 --
2350 --
2351 cursor cst_get_emp_count (
2352 p_payroll_action_id varchar2,
2353 p_legal_employer varchar2,
2354 p_local_unit_id varchar2
2355 ) is
2356 select count (*)
2357 from pay_payroll_actions paa,
2358 pay_assignment_actions assg,
2359 pay_action_information pai
2360 where paa.payroll_action_id = p_payroll_action_id
2361 and assg.payroll_action_id = paa.payroll_action_id
2362 and pai.action_context_id = assg.assignment_action_id
2363 and pai.action_context_type = 'AAP'
2364 and pai.action_information_category = 'EMEA REPORT INFORMATION'
2365 and pai.action_information1 = 'PYNOEERSTC'
2366 and pai.action_information3 = p_legal_employer
2367 and pai.action_information5 = p_local_unit_id;
2368
2369 --
2370 --
2371 l_counter number := 0;
2372 l_count number := 0;
2373 l_payroll_action_id number;
2374 l_prev_cost_seg varchar2 (80) := ' ';
2375 l_prev_eoy_code varchar2 (80) := ' ';
2376 l_total_cost_credit number := 0;
2377 l_total_cost_debit number := 0;
2378 xml_ctr number;
2379 l_legal_employer number;
2380 l_value_flag char (1) := 'Y';
2381 l_total_count number;
2382 begin
2383 if p_payroll_action_id is null then
2384 begin
2385 select payroll_action_id
2386 into l_payroll_action_id
2387 from pay_payroll_actions ppa,
2388 fnd_conc_req_summary_v fcrs,
2389 fnd_conc_req_summary_v fcrs1
2390 where fcrs.request_id = fnd_global.conc_request_id
2391 and fcrs.priority_request_id = fcrs1.priority_request_id
2392 and ppa.request_id between fcrs1.request_id and fcrs.request_id
2393 and ppa.request_id = fcrs1.request_id;
2394 exception
2395 when others then
2396 null;
2397 end;
2398 else
2399 l_payroll_action_id := p_payroll_action_id;
2400 end if;
2401
2402 for i in csr_get_hdr_info (l_payroll_action_id)
2403 loop
2404 l_total_count := 0;
2405 open cst_get_emp_count (
2406 to_char (l_payroll_action_id),
2407 i.legal_employer_id,
2408 i.local_unit_id
2409 );
2410 fetch cst_get_emp_count into l_total_count;
2411 close cst_get_emp_count;
2412
2413 if l_total_count > 0 then
2414 xml_tab (l_counter).tagname := 'LEGAL_EMPLOYER_NAME';
2415 xml_tab (l_counter).tagvalue := i.legal_employer_name;
2416 l_counter := l_counter + 1;
2417 --
2418 --
2419 xml_tab (l_counter).tagname := 'LEGAL_EMPLOYER_ORG_NO';
2420 xml_tab (l_counter).tagvalue := i.legal_employer_org_no;
2421 l_counter := l_counter + 1;
2422 --
2423 --
2424 xml_tab (l_counter).tagname := 'EFFECTIVE_DATE';
2425 xml_tab (l_counter).tagvalue :=
2426 to_char (i.effective_date, 'DD-Mon-YYYY');
2427 l_counter := l_counter + 1;
2428 --
2429 --
2430 xml_tab (l_counter).tagname := 'LU_ORG_NO';
2431 xml_tab (l_counter).tagvalue := i.local_unit_org_no;
2432 l_counter := l_counter + 1;
2433
2434 for j in csr_get_detail_info (
2435 to_char (l_payroll_action_id),
2436 i.legal_employer_id,
2437 i.local_unit_id
2438 )
2439 loop
2440 /* Counter to count records fetched */
2441 l_count := l_count + 1;
2442 xml_tab (l_counter).tagname := 'EMPLOYEE_NUMBER';
2443 xml_tab (l_counter).tagvalue := j.action_information10;
2444 l_counter := l_counter + 1;
2445 --
2446 --
2447 xml_tab (l_counter).tagname := 'LEGAL_EMPL_ORG_NO';
2448 xml_tab (l_counter).tagvalue := i.legal_employer_org_no;
2449 l_counter := l_counter + 1;
2450 --
2451 --
2452 xml_tab (l_counter).tagname := 'LU_ORG_NUMBER';
2453 xml_tab (l_counter).tagvalue := i.local_unit_org_no;
2454 l_counter := l_counter + 1;
2455 --
2456 --
2457 xml_tab (l_counter).tagname := 'STATEMENT_TYPE';
2458 xml_tab (l_counter).tagvalue := '88';
2459 l_counter := l_counter + 1;
2460 --
2461 --
2462 xml_tab (l_counter).tagname := 'EFFECTIVE_DT';
2463 xml_tab (l_counter).tagvalue := i.effective_date;
2464 l_counter := l_counter + 1;
2465 --
2466 --
2467 xml_tab (l_counter).tagname := 'EFFECTIVE_E_DT';
2468 xml_tab (l_counter).tagvalue :=
2469 to_char (i.effective_date, 'DDMMRRRR');
2470 l_counter := l_counter + 1;
2471 --
2472 --
2473 xml_tab (l_counter).tagname := 'FULL_NAME';
2474 xml_tab (l_counter).tagvalue := j.action_information9;
2475 l_counter := l_counter + 1;
2476 --
2477 --
2478 xml_tab (l_counter).tagname := 'NI_NUMBER';
2479 xml_tab (l_counter).tagvalue := j.action_information8;
2480 l_counter := l_counter + 1;
2481 --
2482 --
2483 xml_tab (l_counter).tagname := 'NI_E_NUMBER';
2484 xml_tab (l_counter).tagvalue :=
2485 substr (
2486 j.action_information8,
2487 1,
2488 instr (j.action_information8, '-') - 1
2489 )
2490 || substr (
2491 j.action_information8,
2492 instr (j.action_information8, '-') + 1
2493 );
2494 l_counter := l_counter + 1;
2495 --
2496 --
2497 xml_tab (l_counter).tagname := 'EMP_START_DATE';
2498 xml_tab (l_counter).tagvalue :=
2499 to_char (j.action_information11, 'DD-MON-YYYY');
2500 l_counter := l_counter + 1;
2501 --
2502 --
2503 xml_tab (l_counter).tagname := 'EMP_START_E_DATE';
2504 xml_tab (l_counter).tagvalue :=
2505 to_char (j.action_information11, 'DDMMRRRR');
2506 l_counter := l_counter + 1;
2507 xml_tab (l_counter).tagname := 'WORKING_HOURS';
2508 xml_tab (l_counter).tagvalue :=
2509 -- round (fnd_number.canonical_to_number (j.action_information12));--14507939
2510 to_char(fnd_number.canonical_to_number (j.action_information12)*100,'fm0000');
2511 l_counter := l_counter + 1;
2512 --
2513 --
2514 xml_tab (l_counter).tagname := 'HOUR_CHANGE_DATE';
2515 xml_tab (l_counter).tagvalue := j.action_information13;
2516 l_counter := l_counter + 1;
2517 --
2518 --
2519 xml_tab (l_counter).tagname := 'HOUR_CHANGE_E_DATE';
2520 xml_tab (l_counter).tagvalue :=
2521 to_char (j.action_information13, 'DDMMRRRR');
2522 l_counter := l_counter + 1;
2523 --
2524 --
2525 xml_tab (l_counter).tagname := 'EMP_END_DATE';
2526 xml_tab (l_counter).tagvalue := j.action_information14;
2527 l_counter := l_counter + 1;
2528 --
2529 --
2530 xml_tab (l_counter).tagname := 'EMP_END_E_DATE';
2531 xml_tab (l_counter).tagvalue :=
2532 to_char (j.action_information14, 'DDMMRRRR');
2533 l_counter := l_counter + 1;
2534 --
2535 --
2536 xml_tab (l_counter).tagname := 'LU_CHANGE_DATE';
2537 xml_tab (l_counter).tagvalue := j.action_information16;
2538 l_counter := l_counter + 1;
2539 --
2540 --
2541 xml_tab (l_counter).tagname := 'LU_CHANGE_E_DATE';
2542 xml_tab (l_counter).tagvalue :=
2543 to_char (j.action_information16, 'DDMMRRRR');
2544 l_counter := l_counter + 1;
2545 --
2546 --
2547 xml_tab (l_counter).tagname := 'JOB_CODE';
2548 xml_tab (l_counter).tagvalue := j.action_information17;
2549 l_counter := l_counter + 1;
2550 --
2551 --
2552 xml_tab (l_counter).tagname := 'JOB_CHANGE_E_DATE';
2553 xml_tab (l_counter).tagvalue :=
2554 to_char (j.action_information18, 'DDMMRRRR');
2555 l_counter := l_counter + 1;
2556 --
2557 --
2558 xml_tab (l_counter).tagname := 'JOB_CHANGE_DATE';
2559 xml_tab (l_counter).tagvalue := j.action_information18;
2560 l_counter := l_counter + 1;
2561 --
2562 --
2563 end loop;
2564 end if;
2565 end loop;
2566
2567 writetoclob (p_xml);
2568 exception
2569 when no_data_found then
2570 hr_utility.set_location ('Entered 7 EXP ', 10);
2571 end populate_details;
2572
2573 procedure writetoclob (
2574 p_xfdf_clob out nocopy clob
2575 ) is
2576 l_xfdf_string clob;
2577 l_str1 varchar2 (1000);
2578 l_str2 varchar2 (20);
2579 l_str3 varchar2 (20);
2580 l_str4 varchar2 (20);
2581 l_str5 varchar2 (20);
2582 l_str6 varchar2 (30);
2583 l_str7 varchar2 (1000);
2584 l_str8 varchar2 (240);
2585 l_str9 varchar2 (240);
2586 l_str10 varchar2 (20);
2587 l_str11 varchar2 (20);
2588 l_str12 varchar2 (30);
2589 l_str13 varchar2 (30);
2590 l_str14 varchar2 (30);
2591 l_str15 varchar2 (30);
2592 l_str16 varchar2 (30);
2593 l_str17 varchar2 (30);
2594 l_iana_charset varchar2 (50);
2595 current_index pls_integer;
2596 begin
2597 hr_utility.set_location ('Entering WritetoCLOB ', 10);
2598 l_iana_charset := hr_no_utility.get_iana_charset;
2599 l_str1 := '<?xml version="1.0" encoding="' || l_iana_charset
2600 || '"?> <ROOT><PAACR>';
2601 l_str2 := '<';
2602 l_str3 := '>';
2603 l_str4 := '</';
2604 l_str5 := '>';
2605 l_str6 := '</PAACR></ROOT>';
2606 l_str7 := '<?xml version="1.0" encoding="' || l_iana_charset
2607 || '"?> <ROOT></ROOT>';
2608 l_str10 := '<PAACR>';
2609 l_str11 := '</PAACR>';
2610 l_str12 := '<FILE_HEADER_START>';
2611 l_str13 := '</FILE_HEADER_START>';
2612 l_str14 := '<Fields>';
2613 l_str15 := '</Fields>';
2614 l_str16 := '<EMP_RECORD>';
2615 l_str17 := '</EMP_RECORD>';
2616 dbms_lob.createtemporary (l_xfdf_string, false , dbms_lob.call);
2617 dbms_lob.open (l_xfdf_string, dbms_lob.lob_readwrite);
2618 current_index := 0;
2619
2620 if xml_tab.count > 0 then
2621 dbms_lob.writeappend (l_xfdf_string, length (l_str1), l_str1);
2622 dbms_lob.writeappend (l_xfdf_string, length (l_str12), l_str12);
2623
2624 for table_counter in xml_tab.first .. xml_tab.last
2625 loop
2626 l_str8 := xml_tab (table_counter).tagname;
2627 l_str9 := xml_tab (table_counter).tagvalue;
2628
2629 if l_str8 = 'LEGAL_EMPLOYER_NAME' then
2630 dbms_lob.writeappend (
2631 l_xfdf_string,
2632 length (l_str14),
2633 l_str14
2634 );
2635 elsif l_str8 = 'EMPLOYEE_NUMBER' then
2636 dbms_lob.writeappend (
2637 l_xfdf_string,
2638 length (l_str16),
2639 l_str16
2640 );
2641 end if;
2642
2643 if l_str9 is not null then
2644 dbms_lob.writeappend (l_xfdf_string, length (l_str2), l_str2);
2645 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
2646 dbms_lob.writeappend (l_xfdf_string, length (l_str3), l_str3);
2647 dbms_lob.writeappend (l_xfdf_string, length (l_str9), l_str9);
2648 dbms_lob.writeappend (l_xfdf_string, length (l_str4), l_str4);
2649 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
2650 dbms_lob.writeappend (l_xfdf_string, length (l_str5), l_str5);
2651 else
2652 dbms_lob.writeappend (l_xfdf_string, length (l_str2), l_str2);
2653 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
2654 dbms_lob.writeappend (l_xfdf_string, length (l_str3), l_str3);
2655 dbms_lob.writeappend (l_xfdf_string, length (l_str4), l_str4);
2656 dbms_lob.writeappend (l_xfdf_string, length (l_str8), l_str8);
2657 dbms_lob.writeappend (l_xfdf_string, length (l_str5), l_str5);
2658 end if;
2659
2660 if l_str8 = 'JOB_CHANGE_DATE' then
2661 dbms_lob.writeappend (
2662 l_xfdf_string,
2663 length (l_str17),
2664 l_str17
2665 );
2666
2667 if xml_tab.last = table_counter
2668 or xml_tab (table_counter + 1).tagname <> 'EMPLOYEE_NUMBER' then
2669 dbms_lob.writeappend (
2670 l_xfdf_string,
2671 length (l_str15),
2672 l_str15
2673 );
2674 end if;
2675 end if;
2676 end loop;
2677
2678 dbms_lob.writeappend (l_xfdf_string, length (l_str13), l_str13);
2679 dbms_lob.writeappend (l_xfdf_string, length (l_str6), l_str6);
2680 else
2681 dbms_lob.writeappend (l_xfdf_string, length (l_str7), l_str7);
2682 end if;
2683
2684 p_xfdf_clob := l_xfdf_string;
2685 hr_utility.set_location ('Leaving WritetoCLOB ', 20);
2686 exception
2687 when others then
2688 hr_utility.raise_error;
2689 end writetoclob;
2690
2691 function get_assignment_all_hours (
2692 p_assignment_id in per_all_assignments_f.assignment_id%type,
2693 p_person_id in per_all_people_f.person_id%type,
2694 p_effective_date in date,
2695 p_primary_hour_value number,
2696 p_local_unit number
2697 )
2698 return number is
2699 cursor csr_hour_frequency (
2700 p_assignment_id per_all_assignments_f.assignment_id%type,
2701 p_effective_date date
2702 ) is
2703 select frequency
2704 from per_all_assignments_f
2705 where assignment_id = p_assignment_id
2706 and p_effective_date between effective_start_date
2707 and effective_end_date;
2708
2709 cursor csr_all_assignments_hours (
2710 p_person_id per_all_people_f.person_id%type,
2711 p_assignment_id per_all_assignments_f.assignment_id%type,
2712 p_effective_date date,
2713 p_local_unit number
2714 ) is
2715 select normal_hours, frequency
2716 from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
2717 where paaf.person_id = p_person_id
2718 and paaf.assignment_id <> p_assignment_id
2719 and paaf.normal_hours is not null
2720 and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
2721 and hsc.segment2 = to_char (p_local_unit)
2722 and hourly_salaried_code = 'S'
2723 and p_effective_date between paaf.effective_start_date
2724 and paaf.effective_end_date;
2725
2726 l_frequency per_all_assignments_f.frequency%type;
2727 l_total_hours number := 0;
2728 l_total_hours_all number := 0;
2729 begin
2730 open csr_hour_frequency (p_assignment_id, p_effective_date);
2731 fetch csr_hour_frequency into l_frequency;
2732 close csr_hour_frequency;
2733 --
2734 --
2735
2736 l_total_hours := find_total_hour (p_primary_hour_value, l_frequency);
2737 l_total_hours_all := l_total_hours;
2738
2739 for m in csr_all_assignments_hours (
2740 p_person_id,
2741 p_assignment_id,
2742 p_effective_date,
2743 p_local_unit
2744 )
2745 loop
2746 l_total_hours_all := l_total_hours_all
2747 + find_total_hour (m.normal_hours, m.frequency);
2748 end loop;
2749
2750 return l_total_hours_all;
2751 end;
2752
2753 function find_total_hour (
2754 p_hours in number,
2755 p_frequency in varchar2
2756 )
2757 return number is
2758 p_total_hours number := 0;
2759 begin
2760 if p_frequency = 'W' then
2761 p_total_hours := round (p_hours, 2);
2762 elsif p_frequency = 'D' then
2763 p_total_hours := round (p_hours * 5, 2);
2764 elsif p_frequency = 'M' then
2765 p_total_hours := round (p_hours * 12 / 52, 2);
2766 elsif p_frequency = 'Y' then
2767 p_total_hours := round (p_hours / 52, 2);
2768 end if;
2769
2770 return p_total_hours;
2771 end;
2772
2773 function check_national_identifier (
2774 p_national_identifier varchar2
2775 )
2776 return varchar2 is
2777 l_return_value per_all_people_f.national_identifier%type;
2778 l_check_value number;
2779 d1 number;
2780 d2 number;
2781 m1 number;
2782 m2 number;
2783 y1 number;
2784 y2 number;
2785 i1 number;
2786 i2 number;
2787 i3 number;
2788 c1 number;
2789 c2 number;
2790 v1 number;
2791 v2 number;
2792 l_remainder number;
2793 l_check number;
2794 begin
2795 l_return_value := hr_ni_chk_pkg.chk_nat_id_format (
2796 p_national_identifier,
2797 'DDDDDD-DDDDD'
2798 );
2799
2800 if l_return_value <> '0' then
2801 l_check_value := hr_no_utility.chk_valid_date (l_return_value);
2802
2803 if l_check_value <> 0 then
2804 /* Valid Birthdate */
2805 d1 := fnd_number.canonical_to_number (substr (l_return_value, 1, 1));
2806 d2 := fnd_number.canonical_to_number (substr (l_return_value, 2, 1));
2807 m1 := fnd_number.canonical_to_number (substr (l_return_value, 3, 1));
2808 m2 := fnd_number.canonical_to_number (substr (l_return_value, 4, 1));
2809 y1 := fnd_number.canonical_to_number (substr (l_return_value, 5, 1));
2810 y2 := fnd_number.canonical_to_number (substr (l_return_value, 6, 1));
2811 i1 := fnd_number.canonical_to_number (substr (l_return_value, 8, 1));
2812 i2 := fnd_number.canonical_to_number (substr (l_return_value, 9, 1));
2813 i3 := fnd_number.canonical_to_number (substr (l_return_value, 10, 1));
2814 c1 := fnd_number.canonical_to_number (substr (l_return_value, 11, 1));
2815 c2 := fnd_number.canonical_to_number (substr (l_return_value, 12, 1));
2816 v1 := 3 * d1 + 7 * d2 + 6 * m1 + m2 + 8 * y1 + 9 * y2 + 4 * i1 + 5 * i2 + 2 * i3;
2817
2818
2819 l_remainder := mod (v1, 11);
2820
2821 if l_remainder = 0 then
2822 l_check := 0;
2823 else
2824 l_check := (11 - l_remainder);
2825 end if;
2826
2827 if l_check <> c1 then
2828 l_return_value := 'INVALID_ID';
2829 else
2830 v2 := 5 * d1 + 4 * d2 + 3 * m1 + 2 * m2 + 7 * y1 + 6 * y2 + 5 * i1 + 4 * i2 + 3 * i3 + 2 * c1;
2831
2832 l_remainder := mod (v2, 11);
2833
2834 if l_remainder = 0 then
2835 l_check := 0;
2836 else
2837 l_check := (11 - l_remainder);
2838 end if;
2839
2840 if l_check <> c2 then
2841 l_return_value := 'INVALID_ID';
2842 end if;
2843 end if;
2844 else
2845 l_return_value := 'INVALID_ID';
2846 end if;
2847 else
2848 l_return_value := 'INVALID_ID';
2849 end if;
2850
2851 return l_return_value;
2852 end;
2853 end pay_no_eerr_status_control;