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