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