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