[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_ABP_ARCHIVE
Source
1 PACKAGE BODY PAY_NL_ABP_ARCHIVE as
2 /* $Header: pynlabpar.pkb 120.0.12020000.32 2013/03/29 07:46:20 sgmaram noship $ */
3
4
5 EOL VARCHAR2(5) := FND_GLOBAL.NEWLINE();
6 g_valid_start_date DATE := to_date('01012013','DDMMYYYY');
7
8 g_payroll_action_id number; --14793405
9
10 -- =============================================================================
11 -- Cursor to get payroll_id of an assignment
12 -- =============================================================================
13 CURSOR csr_get_payroll_id(c_assignment_id IN NUMBER) IS
14 SELECT payroll_id
15 FROM per_all_assignments_f
16 WHERE assignment_id = c_assignment_id;
17 -- =============================================================================
18 -- Cursor to get all assig.actions for a given assig. within a date range
19 -- =============================================================================
20 CURSOR csr_asg_act (c_assignment_id IN Number
21 ,c_payroll_id IN Number
22 ,c_con_set_id IN Number
23 ,c_start_date IN Date
24 ,c_end_date IN Date
25 ) IS
26 SELECT paa.assignment_action_id
27 ,ppa.effective_date
28 ,ppa.action_type
29 ,ppa.date_earned
30 FROM pay_assignment_actions paa
31 ,pay_payroll_actions ppa
32 WHERE paa.assignment_id = c_assignment_id
33 AND ppa.action_status = 'C'
34 AND paa.action_status IN ('C','S')
35 AND ppa.action_type IN ('Q','R')
36 AND paa.payroll_action_id = ppa.payroll_action_id
37 AND ppa.payroll_id = nvl (c_payroll_id
38 ,ppa.payroll_id)
39 AND ppa.consolidation_set_id = nvl (c_con_set_id
40 ,ppa.consolidation_set_id)
41 AND ppa.effective_date BETWEEN c_start_date
42 AND c_end_date
43 AND source_action_id IS NOT NULL
44 ORDER BY ppa.effective_date;
45
46 -- =============================================================================
47 -- Cursor to get max assig.action for a given assig. within a data range
48 -- =============================================================================
49 CURSOR csr_asg_act1 (c_assignment_id IN Number
50 ,c_payroll_id IN Number
51 ,c_con_set_id IN Number
52 ,c_start_date IN Date
53 ,c_end_date IN Date
54 ) IS
55 SELECT max (paa.assignment_action_id)
56 FROM pay_assignment_actions paa
57 ,pay_payroll_actions ppa
58 WHERE paa.assignment_id = c_assignment_id
59 AND ppa.action_status = 'C'
60 AND ppa.action_type IN ('Q','R')
61 AND paa.action_status IN ('C','S')
62 AND paa.payroll_action_id = ppa.payroll_action_id
63 AND ppa.payroll_id = nvl (c_payroll_id
64 ,ppa.payroll_id)
65 AND ppa.consolidation_set_id = nvl (c_con_set_id
66 ,ppa.consolidation_set_id)
67 AND source_action_id IS NOT NULL
68 AND ppa.effective_date BETWEEN c_start_date
69 AND c_end_date;
70 -- =============================================================================
71 -- Cursor to get the defined balance id for a given balance and dimension
72 -- =============================================================================
73 CURSOR csr_defined_bal (c_balance_name IN Varchar2
74 ,c_dimension_name IN Varchar2
75 ,c_bg_id IN Number) IS
76 SELECT db.defined_balance_id
77 FROM pay_balance_types pbt
78 ,pay_defined_balances db
79 ,pay_balance_dimensions bd
80 WHERE pbt.balance_name = c_balance_name
81 AND pbt.balance_type_id = db.balance_type_id
82 AND bd.balance_dimension_id = db.balance_dimension_id
83 AND bd.dimension_name = c_dimension_name
84 AND (
85 pbt.business_group_id = c_bg_id
86 OR pbt.legislation_code = 'NL'
87 )
88 AND (
89 db.business_group_id = pbt.business_group_id
90 OR db.legislation_code = 'NL'
91 );
92
93 -- =============================================================================
94 -- Cursor to get the defined balance id for a given balance type id and dimension
95 -- =============================================================================
96 CURSOR csr_defined_bal1 (c_balance_type_id IN Number
97 ,c_dimension_name IN Varchar2
98 ,c_bg_id IN Number) IS
99 SELECT db.defined_balance_id
100 FROM pay_defined_balances db
101 ,pay_balance_dimensions bd
102 WHERE db.balance_type_id = c_balance_type_id
103 AND bd.balance_dimension_id = db.balance_dimension_id
104 AND bd.dimension_name = c_dimension_name
105 AND (
106 db.business_group_id = c_bg_id
107 OR db.legislation_code = 'NL'
108 );
109
110 --=========================================================================
111 CURSOR csr_get_payroll_period(c_payroll_id IN NUMBER
112 ,c_start_date IN DATE
113 ,c_end_date IN DATE)IS
114 SELECT start_date
115 ,end_date
116 FROM per_time_periods
117 WHERE payroll_id = c_payroll_id
118 AND end_date BETWEEN c_start_date
119 AND c_end_date;
120 --=========================================================================
121 CURSOR csr_get_period(c_payroll_id IN NUMBER
122 ,c_effective_date IN DATE)IS
123 SELECT start_date
124 ,end_date
125 FROM per_time_periods
126 WHERE payroll_id = c_payroll_id
127 AND c_effective_date BETWEEN start_date
128 AND end_date;
129
130 -- =============================================================================
131 -- Cursor to check if there is a change in hire date. The change may be in the
132 -- future or in the past with or without payroll runs
133 -- =============================================================================
134 CURSOR csr_hire_dt_chg(c_person_id IN NUMBER
135 ,c_start_date IN DATE
136 ,c_end_date IN DATE) IS
137 SELECT old_val1 old_date
138 ,new_val1 new_date
139 FROM ben_ext_chg_evt_log
140 WHERE person_id = c_person_id
141 AND chg_evt_cd = 'COPOS'
142 AND fnd_date.canonical_to_date (prmtr_09) BETWEEN c_start_date
143 AND c_end_date
144 ORDER BY ext_chg_evt_log_id DESC;
145
146 -- =============================================================================
147 -- Cursor to fetch the Contribution Base for Pension Types OPNP and AAOP
148 -- =============================================================================
149 CURSOR csr_contr_basis(c_bg_id IN NUMBER
150 ,c_effective_date IN DATE) IS
151 SELECT pet.element_type_id
152 ,pei.eei_information9
153 || ' Employee Pension Basis' bal_name
154 ,pei.eei_information12 pension_type
155 ,- 1 defined_bal_id
156 ,pei.eei_information18 cy_retro_element_id
157 ,
158 (
159 SELECT retro_element_type_id
160 FROM pay_element_span_usages pesu
161 ,pay_retro_component_usages prcu
162 WHERE prcu.retro_component_usage_id = pesu.retro_component_usage_id
163 AND retro_component_id =
164 (
165 SELECT retro_component_id
166 FROM pay_retro_components
167 WHERE legislation_code = 'NL'
168 AND short_name = 'Adjustment'
169 AND component_name = 'Adjustment'
170 )
171 AND creator_type = 'ET'
172 AND creator_id = pet.element_type_id
173 ) py_cy_adj_retro_element_id
174 FROM pay_element_type_extra_info pei
175 ,pay_element_types_f pet
176 WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
177 AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
178 AND c_effective_date BETWEEN pet.effective_start_date
179 AND pet.effective_end_date
180 AND pet.element_type_id = pei.element_type_id
181 AND pet.business_group_id = c_bg_id
182 AND pei.eei_information12 IN ('OPNP','AAOP');
183
184 -- =============================================================================
185 -- Cursor to fetch the Contribution Amount for Pension Types
186 -- OPNP_65,OPNP_W25,OPNP_W50,PPP,FPU_B
187 -- =============================================================================
188 CURSOR csr_contr_amount(c_bg_id IN NUMBER
189 ,c_effective_date IN DATE
190 ,c_asg_id IN NUMBER) IS
191 --Retro entries
192 SELECT pei.eei_information12 sub_cat
193 ,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
194 ,decode (pei.eei_information12,'FPU_B','VPL','PPP','PPP-I','OPNP-I') pension_type
195 ,decode (pei.eei_information12,'OPNP_65','A','OPNP_W25','B','OPNP_W50','C') pension_type_variant
196 ,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) contr_amt
197 ,pty.ee_contribution_bal_type_id
198 ,pty.er_contribution_bal_type_id
199 FROM pay_element_type_extra_info pei,
200 pay_element_types_f pet,
201 pay_element_entries_f peef,
202 pay_element_links_f pelf,
203 pay_element_entry_values_f peev,
204 pay_input_values_f pivf,
205 pqp_pension_types_f pty
206 WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
207 AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
208 AND c_effective_date BETWEEN to_date(pei.eei_information10,'DD/MM/RRRR') AND
209 to_date(pei.eei_information11,'DD/MM/RRRR')
210 AND c_effective_date BETWEEN pet.effective_start_date AND
211 pet.effective_end_date
212 AND c_effective_date BETWEEN peef.effective_start_date AND
213 peef.effective_end_date
214 AND c_effective_date BETWEEN pelf.effective_start_date AND
215 pelf.effective_end_date
216 AND c_effective_date BETWEEN peev.effective_start_date AND
217 peev.effective_end_date
218 AND c_effective_date BETWEEN pty.effective_start_date AND
219 pty.effective_end_date
220 AND c_effective_date BETWEEN pivf.effective_start_date AND
221 pivf.effective_end_date
222 AND (to_number(pei.eei_information18) = pet.element_type_id
223 OR to_number(pei.eei_information19) = pet.element_type_id
224 OR to_number(pei.eei_information20) = pet.element_type_id
225 OR to_number(pei.eei_information21) = pet.element_type_id
226 OR pet.element_type_id IN (SELECT retro_element_type_id
227 FROM pay_element_span_usages pesu,
228 pay_retro_component_usages prcu
229 WHERE prcu.retro_component_usage_id = pesu.retro_component_usage_id
230 AND retro_component_id = ( SELECT retro_component_id
231 FROM pay_retro_components
232 WHERE legislation_code = 'NL'
233 AND short_name = 'Adjustment'
234 AND component_name = 'Adjustment')
235 AND creator_type = 'ET'
236 AND (creator_id = pei.element_type_id OR
237 creator_id IN (SELECT element_type_id
238 FROM pay_element_types_f pet1
239 WHERE pet1.element_name = pei.eei_information9 || ' ABP Employer Pension Contribution'
240 AND pet1.business_group_id = pet.business_group_id))) )
241 AND pelf.element_type_id = pet.element_type_id
242 AND pivf.element_type_id = pet.element_type_id
243 AND pivf.name = 'Pay Value'
244 AND peef.element_link_id = pelf.element_link_id
245 AND peev.input_value_id = pivf.input_value_id
246 AND peev.element_entry_id = peef.element_entry_id
247 AND pet.business_group_id = c_bg_id
248 AND peef.assignment_id = c_asg_id
249 AND pei.eei_information12 IN ('OPNP_65','OPNP_W25','OPNP_W50','PPP','FPU_B')
250 AND pty.pension_type_id = to_number(pei.eei_information2)
251 AND pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) >= g_valid_start_date
252 GROUP BY pei.eei_information12,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id)
253 ,pty.ee_contribution_bal_type_id,pty.er_contribution_bal_type_id
254 UNION
255 --Normal entries
256 SELECT pension_sub_category sub_cat
257 ,c_effective_date date_earned
258 ,decode (pension_sub_category,'FPU_B','VPL','PPP','PPP-I','OPNP-I') pension_type
259 ,decode (pension_sub_category,'OPNP_65','A','OPNP_W25','B','OPNP_W50','C') pension_type_variant
260 ,-999999 contr_amt
261 ,ee_contribution_bal_type_id
262 ,er_contribution_bal_type_id
263 FROM pqp_pension_types_f pty
264 WHERE pension_sub_category IN ('OPNP_65','OPNP_W25','OPNP_W50','PPP','FPU_B')
265 AND business_group_id = c_bg_id
266 AND c_effective_date BETWEEN pty.effective_start_date AND
267 pty.effective_end_date
268 GROUP BY pension_sub_category,c_effective_date
269 ,ee_contribution_bal_type_id
270 ,er_contribution_bal_type_id;
271
272 -- ==============================================================
273 -- Cursor to get the prev reported contr base value for a period
274 -- ==============================================================
275 CURSOR csr_prev_basis_amt (c_assignment_id IN NUMBER
276 ,c_effective_date IN DATE
277 ,c_pension_type IN VARCHAR2) IS
278 SELECT fnd_number.canonical_to_number(pai.action_information7) contr_base
279 FROM pay_action_information pai
280 WHERE pai.action_information_category = 'NL_ABP_PENSION_INFO'
281 AND pai.action_context_type = 'AAP'
282 AND pai.assignment_id = c_assignment_id
283 AND pai.action_information1 = c_pension_type
284 AND c_effective_date = fnd_date.canonical_to_date(pai.action_information4)
285 ORDER BY pai.action_context_id DESC;
286
287 -- =====================================================
288 -- Cursor to get the prev reported hire info
289 -- =====================================================
290 CURSOR csr_get_prev_hire_info(c_asg_id IN NUMBER
291 ,c_effective_date IN DATE)
292 IS
293 SELECT 1
294 FROM pay_action_information pai
295 WHERE pai.assignment_id = c_asg_id
296 AND pai.action_information_category = 'NL_ABP_ASG_INFO'
297 AND pai.action_context_type = 'AAP'
298 AND c_effective_date = fnd_date.canonical_to_date(pai.action_information3);
299
300
301 --BUG 14793405
302 -- =====================================================
303 -- Procedure to archive technical exceptions
304 -- =====================================================
305 PROCEDURE log_tech_exception
306 (p_action_context_id IN number
307 , p_err_msg IN varchar2) IS
308
309 l_ovnum number;
310 l_action_infor_id number;
311 BEGIN
312 pay_action_information_api.create_action_information
313 (p_action_information_id => l_action_infor_id
314 , p_action_context_id => p_action_context_id
315 , p_action_context_type => 'PA'
316 , p_object_version_number => l_ovnum
317 , p_action_information_category => 'NL_ABP_TECH_EXCEPTION_REPORT'
318 , p_action_information4 => p_err_msg);
319 END log_tech_exception;
320
321 -- ==============================================================================
322 -- Funtion to get the parameters of the archive process
323 -- ==============================================================================
324 FUNCTION get_parameter (p_parameter_string IN VARCHAR2
325 ,p_token IN VARCHAR2
326 ,p_segment_number IN NUMBER DEFAULT NULL )
327 RETURN VARCHAR2 IS
328
329 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
330 l_start_pos NUMBER;
331 l_delimiter varchar2(1):=' ';
332
333 BEGIN
334 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
335 --
336 IF l_start_pos = 0 THEN
337 l_delimiter := '|';
338 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
339 end if;
340
341 IF l_start_pos <> 0 THEN
342 l_start_pos := l_start_pos + length(p_token||'=');
343 l_parameter := substr(p_parameter_string,
344 l_start_pos,
345 instr(p_parameter_string||' ',
346 l_delimiter,l_start_pos)
347 - l_start_pos);
348 IF p_segment_number IS NOT NULL THEN
349 l_parameter := ':'||l_parameter||':';
350 l_parameter := substr(l_parameter,
351 instr(l_parameter,':',1,p_segment_number)+1,
352 instr(l_parameter,':',1,p_segment_number+1) -1
353 - instr(l_parameter,':',1,p_segment_number));
354 END IF;
355 END IF;
356 RETURN l_parameter;
357 END get_parameter;
358
359 -- ==============================================================================
360 -- Procedure which returns all the parameters of the archive process
361 -- ==============================================================================
362 PROCEDURE get_all_parameters (p_payroll_action_id IN NUMBER
363 ,p_org_struct_id OUT NOCOPY NUMBER
364 ,p_org_id OUT NOCOPY NUMBER
365 ,p_report_type OUT NOCOPY VARCHAR2
366 ,p_seq_num OUT NOCOPY NUMBER
367 ,p_bg_id OUT NOCOPY NUMBER
368 ,p_start_date OUT NOCOPY DATE
369 ,p_end_date OUT NOCOPY DATE) IS
370
371 CURSOR csr_parameter_info(c_payroll_action_id NUMBER) IS
372 SELECT to_number (get_parameter (legislative_parameters
373 ,'ORG_STRUCT_ID')) org_struct_id
374 ,to_number (get_parameter (legislative_parameters
375 ,'Employer')) org_id
376 ,get_parameter (legislative_parameters
377 ,'Report_Type') report_type
378 ,to_number (get_parameter (legislative_parameters
379 ,'Sequence_Number')) seq_num
380 ,business_group_id bg_id
381 ,start_date
382 ,effective_date
383 FROM pay_payroll_actions
384 WHERE payroll_action_id = c_payroll_action_id;
385
386 l_param csr_parameter_info%ROWTYPE;
387
388 BEGIN
389
390 OPEN csr_parameter_info (p_payroll_action_id);
391 FETCH csr_parameter_info INTO l_param;
392 CLOSE csr_parameter_info;
393
394 p_org_struct_id := l_param.org_struct_id;
395 p_org_id := l_param.org_id;
396 p_report_type := l_param.report_type;
397 p_seq_num := l_param.seq_num;
398 p_bg_id := l_param.bg_id;
399 p_start_date := l_param.start_date;
400 p_end_date := l_param.effective_date;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 hr_utility.trace('Exception in get_all_parameters SQLERRM: '||SQLERRM);
405 log_tech_exception(p_action_context_id => p_payroll_action_id,
406 p_err_msg => 'get_all_parameters : '|| substr (sqlerrm, 1, 180)); --14793405
407 END get_all_parameters;
408
409 -- =============================================================================
410 -- Function to convert the date to the appropriate value
411 -- since the ben logs contain dates in the NL Language -- 31-MEI-13
412 -- 1-OKT-13 etc
413 -- =============================================================================
414 FUNCTION to_nl_date (p_date_value IN VARCHAR2,
415 p_date_format IN VARCHAR2)
416 RETURN DATE IS
417
418 BEGIN
419
420 IF length(p_date_value) = 9 THEN
421 RETURN to_date(p_date_value,p_date_format,'NLS_DATE_LANGUAGE = ''DUTCH''');
422 ELSE
423 RETURN to_date(p_date_value,p_date_format);
424 END IF;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 RETURN to_date(p_date_value,p_date_format,'NLS_DATE_LANGUAGE = ''AMERICAN''');
429
430 END to_nl_date;
431
432 --=============================================================================
433 -- Function to get the age of a person given the effective date
434 --=============================================================================
435 FUNCTION get_age
436 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
437 ,p_effective_date IN DATE)
438 RETURN NUMBER IS
439
440 CURSOR csr_get_dob IS
441 SELECT trunc (date_of_birth)
442 FROM per_all_people_f per
443 ,per_all_assignments_f paf
444 WHERE per.person_id = paf.person_id
445 AND paf.assignment_id = p_assignment_id
446 AND p_effective_date BETWEEN per.effective_start_date
447 AND per.effective_end_date
448 AND p_effective_date BETWEEN paf.effective_start_date
449 AND paf.effective_end_date;
450
451 l_age NUMBER;
452 l_dob DATE;
453
454 BEGIN
455 --
456 --Fetch the date of birth
457 --
458 OPEN csr_get_dob;
459 FETCH csr_get_dob INTO l_dob;
460 CLOSE csr_get_dob;
461
462 l_dob := NVL(l_dob,p_effective_date);
463
464 RETURN (TRUNC(MONTHS_BETWEEN(p_effective_date,l_dob)/12,2));
465
466 END get_age;
467
468 -- =========================================================================
469 -- Function to check if Run Results exist for an ass act/element combination
470 -- =========================================================================
471 FUNCTION chk_rr_exist (p_ass_act_id IN NUMBER
472 ,p_element_type_id IN NUMBER ) RETURN BOOLEAN IS
473
474 CURSOR csr_ass_act IS
475 SELECT 1
476 FROM pay_run_results
477 WHERE assignment_action_id = p_ass_act_id
478 AND element_type_id = p_element_type_id;
479
480 l_dummy NUMBER;
481
482
483 BEGIN
484 hr_utility.trace('+====chk_rr_exist============================================+');
485 hr_utility.trace('Ass Act is : '||p_ass_act_id);
486 hr_utility.trace('Element Type is : '||p_element_type_id);
487
488 OPEN csr_ass_act;
489 FETCH csr_ass_act INTO l_dummy;
490 IF csr_ass_act%FOUND THEN
491 hr_utility.trace('Run Results found : ');
492 CLOSE csr_ass_act;
493 RETURN TRUE;
494 ELSE
495 hr_utility.trace('Run Results not found : ');
496 CLOSE csr_ass_act;
497 RETURN FALSE;
498 END IF;
499
500 hr_utility.trace('X====chk_rr_exist============================================X');
501 EXCEPTION
502 WHEN OTHERS THEN
503 hr_utility.trace('Exception in chk_rr_exist '||SQLERRM);
504 log_tech_exception(p_action_context_id => g_payroll_action_id,
505 p_err_msg => 'chk_rr_exist : '|| substr (sqlerrm, 1, 180)); --14793405
506 RETURN FALSE;
507 END chk_rr_exist;
508 --=========================================================================
509
510 FUNCTION chk_asg_term_py (p_assignment_id IN NUMBER,
511 p_ext_st IN DATE)
512 RETURN BOOLEAN IS
513
514 l_asg_term_dt DATE;
515 --
516 -- Cursor to fetch the termination date of a terminated or
517 -- ended assignment.
518 --
519 CURSOR csr_get_term_date IS
520 SELECT min (effective_start_date) - 1 term_date
521 FROM per_all_assignments_f asg
522 WHERE assignment_id = p_assignment_id
523 AND assignment_status_type_id IN
524 (
525 SELECT assignment_status_type_id
526 FROM per_assignment_status_types
527 WHERE per_system_status = 'TERM_ASSIGN'
528 AND active_flag = 'Y'
529 )
530 UNION
531 --
532 -- Get the dates for any ended assignments. Note that this is for sec
533 -- assignments only.
534 --
535 SELECT max (effective_end_date)
536 FROM per_all_assignments_f asg
537 WHERE assignment_id = p_assignment_id
538 AND asg.primary_flag = 'N'
539 AND NOT EXISTS
540 (
541 SELECT 1
542 FROM per_all_assignments_f asg1
543 WHERE asg1.assignment_id = p_assignment_id
544 AND asg1.effective_start_date = asg.effective_end_date + 1
545 AND asg.assignment_id = asg1.assignment_id
546 )
547 AND NOT EXISTS
548 (
549 SELECT 1
550 FROM per_all_assignments_f asg1
551 WHERE asg1.assignment_id = p_assignment_id
552 AND asg1.effective_start_date > asg.effective_start_date
553 AND asg.assignment_id = asg1.assignment_id
554 AND asg1.assignment_status_type_id IN
555 (
556 SELECT assignment_status_type_id
557 FROM per_assignment_status_types
558 WHERE per_system_status = 'TERM_ASSIGN'
559 AND active_flag = 'Y'
560 )
561 );
562
563 BEGIN
564 hr_utility.trace('+====chk_asg_term_py============================================+');
565 hr_utility.trace('...Assignment Id is : '||p_assignment_id);
566 hr_utility.trace('...Value of p_ext_st is : '||p_ext_st);
567
568 OPEN csr_get_term_date;
569 FETCH csr_get_term_date INTO l_asg_term_dt;
570 IF csr_get_term_date%NOTFOUND THEN
571 hr_utility.trace('...Termination Date Not found : ');
572 CLOSE csr_get_term_date;
573 RETURN FALSE;
574 ELSE
575 CLOSE csr_get_term_date;
576 fnd_file.put_line(fnd_file.log,'...Termination Date found : ');
577 IF to_number(to_char(nvl(l_asg_term_dt,p_ext_st),'YYYY')) <
578 to_number(to_char(p_ext_st,'YYYY')) THEN
579 hr_utility.trace('...Condition met return TRUE : ');
580 RETURN TRUE;
581 ELSE
582 hr_utility.trace('...Condition not met return FALSE : ');
583 RETURN FALSE;
584 END IF;
585
586 END IF;
587 hr_utility.trace('X====chk_asg_term_py============================================X');
588 EXCEPTION
589 WHEN OTHERS THEN
590 hr_utility.trace('Exception in chk_asg_term_py '||SQLERRM);
591 log_tech_exception(p_action_context_id => g_payroll_action_id,
592 p_err_msg => 'chk_asg_term_py : '|| substr (sqlerrm, 1, 180)); --14793405
593 RETURN FALSE;
594
595 END chk_asg_term_py;
596
597 FUNCTION get_balance_value
598 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
599 ,p_bg_id IN per_all_assignments_f.business_group_id%TYPE
600 ,p_payroll_id IN NUMBER
601 ,p_start_date IN DATE
602 ,p_end_date IN DATE
603 ,p_balance_name IN pay_balance_types.balance_name%TYPE
604 ,p_dim_name IN VARCHAR2) RETURN NUMBER IS
605
606 l_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
607 l_balance_id pay_balance_types.balance_type_id%TYPE;
608 l_balance_amount NUMBER := 0;
609 l_dim_name VARCHAR2(100);
610
611 --14839169
612 --=========================================================================
613 -- Cursor to get max assig.action for a given assig. within a data range
614 -- Balance adjustment and Reversal Included
615 -- ========================================================================
616 CURSOR csr_asg_act2 (c_assignment_id IN Number
617 ,c_balance_id in Number
618 ,c_start_date IN Date
619 ,c_end_date IN Date
620 ) IS
621 SELECT to_number (substr (max (lpad (paa.action_sequence, 15
622 , '0')
623 || lpad (paa.assignment_action_id, 15
624 , '0')), 16
625 , 15))
626 FROM pay_assignment_actions paa
627 , pay_payroll_actions ppa
628 WHERE paa.assignment_id = c_assignment_id
629 AND ppa.action_status = 'C'
630 AND paa.action_status IN ('C', 'S')
631 AND paa.payroll_action_id = ppa.payroll_action_id
632 --AND ppa.effective_date
633 AND ppa.date_earned --Bug 16301893
634 BETWEEN c_start_date
635 AND c_end_date
636 AND (
637 ppa.action_type IN ('Q', 'R', 'I')
638 OR (
639 ppa.action_type = 'B'
640 AND EXISTS
641 (
642 SELECT 1
643 FROM pay_run_results prr
644 , pay_run_result_values prv
645 , pay_balance_feeds_f pbf
646 , pay_defined_balances db
647 WHERE prr.assignment_action_id = paa.assignment_action_id
648 AND prv.run_result_id = prr.run_result_id
649 AND pbf.input_value_id = prv.input_value_id
650 AND prv.result_value IS NOT NULL
651 AND pbf.balance_type_id = db.balance_type_id
652 AND db.defined_balance_id = c_balance_id
653 AND ppa.effective_date
654 BETWEEN pbf.effective_start_date
655 AND pbf.effective_end_date
656 )
657 )
658 );
659
660 BEGIN
661
662 hr_utility.trace('+====get_balance_value============================================+');
663
664 OPEN csr_defined_bal(p_balance_name,p_dim_name,p_bg_id);
665 FETCH csr_defined_bal INTO l_balance_id;
666 CLOSE csr_defined_bal;
667
668 IF l_balance_id IS NOT NULL THEN
669 OPEN csr_asg_act2 (
670 c_assignment_id => p_assignment_id
671 ,c_balance_id => l_balance_id
672 ,c_start_date => p_start_date
673 ,c_end_date => p_end_date);
674 FETCH csr_asg_act2 INTO l_asg_action_id;
675 CLOSE csr_asg_act2;
676
677 IF l_asg_action_id IS NOT NULL THEN
678 IF p_dim_name LIKE '%Balance Date%' THEN --Bug 16301893
679 l_balance_amount := pay_balance_pkg.get_value (l_balance_id, l_asg_action_id
680 , NULL, NULL
681 , NULL, NULL
682 , NULL, NULL
683 , NULL, NULL
684 , NULL, NULL
685 , NULL, p_end_date
686 , NULL, NULL
687 , NULL, NULL
688 , NULL);
689 l_balance_amount := nvl(l_balance_amount,0);
690 ELSE
691 l_balance_amount := pay_balance_pkg.get_value
692 (p_defined_balance_id => l_balance_id
693 ,p_assignment_action_id => l_asg_action_id);
694 l_balance_amount := nvl(l_balance_amount,0);
695 END IF;
696 END IF;
697 END IF;
698
699 hr_utility.trace('X====get_balance_value============================================X');
700
701 RETURN l_balance_amount;
702
703 EXCEPTION
704 WHEN OTHERS THEN
705 hr_utility.trace('Exception in get_balance_value '||SQLERRM);
706 log_tech_exception(p_action_context_id => g_payroll_action_id,
707 p_err_msg => 'get_balance_value : '|| substr (sqlerrm, 1, 180)); --14793405
708 RETURN -1;
709
710 END get_balance_value;
711
712 -- =========================================================================
713 -- Function to get average part time percentage for a period of an asg
714 -- =========================================================================
715 FUNCTION get_avg_ptp(p_assignment_id IN NUMBER
716 ,p_period_start_date IN DATE
717 ,p_period_end_date IN DATE)
718 RETURN NUMBER IS
719
720 CURSOR csr_get_asg_start_date(c_assignment_id IN NUMBER
721 ,c_period_start_date IN DATE
722 ,c_period_end_date IN DATE) IS
723 SELECT min (asg.effective_start_date)
724 FROM per_assignments_f asg
725 ,per_assignment_status_types past
726 WHERE asg.assignment_status_type_id = past.assignment_status_type_id
727 AND past.per_system_status = 'ACTIVE_ASSIGN'
728 AND asg.effective_start_date <= trunc (c_period_end_date)
729 AND nvl (asg.effective_end_date
730 ,trunc (c_period_end_date)) >= trunc (c_period_start_date)
731 AND asg.assignment_id = c_assignment_id
732 GROUP BY asg.assignment_id;
733 --=========================================================================
734 CURSOR csr_ptp_chg (c_assignment_id IN NUMBER
735 ,c_effective_date IN DATE
736 ,c_period_end_date IN DATE) IS
737 SELECT asg.effective_start_date start_date
738 ,decode (asg.effective_end_date
739 ,hr_general.end_of_time
740 ,trunc (c_period_end_date)
741 ,asg.effective_end_date) end_date
742 ,fnd_number.canonical_to_number (nvl (target.segment29
743 ,'100')) ptp
744 FROM per_assignments_f asg
745 ,per_assignment_status_types past
746 ,hr_soft_coding_keyflex target
747 WHERE asg.assignment_status_type_id = past.assignment_status_type_id
748 AND past.per_system_status = 'ACTIVE_ASSIGN'
749 AND asg.effective_end_date >= c_effective_date
750 AND asg.assignment_id = c_assignment_id
751 AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
752 AND target.enabled_flag = 'Y';
753 --=========================================================================
754
755 l_effective_date DATE;
756 l_completed VARCHAR2(1);
757 l_days_in_pp NUMBER;
758 l_pt_perc NUMBER;
759 l_end_date DATE;
760 l_start_date DATE;
761 l_hook_ptp NUMBER;
762 l_min_start_date DATE;
763 l_max_end_date DATE;
764 l_count NUMBER := 0;
765 l_ret_val NUMBER := 0;
766 l_avg_ptp NUMBER;
767
768 BEGIN
769
770 hr_utility.trace('+====get_avg_ptp============================================+');
771
772 OPEN csr_get_asg_start_date(p_assignment_id,p_period_start_date,p_period_end_date);
773 FETCH csr_get_asg_start_date INTO l_effective_date;
774 CLOSE csr_get_asg_start_date;
775
776 l_completed := 'N';
777 l_pt_perc := 0;
778 l_effective_date := GREATEST(l_effective_date,trunc(p_period_start_date));
779
780 l_days_in_pp := (trunc(p_period_end_date)
781 - trunc(p_period_start_date)) + 1;
782
783 FOR temp_rec in csr_ptp_chg (p_assignment_id,trunc(l_effective_date),p_period_end_date)
784
785 LOOP
786
787 IF l_completed = 'N' THEN
788
789 IF temp_rec.end_date >= trunc(p_period_end_date) THEN
790 l_end_date := trunc(p_period_end_date);
791 l_completed := 'Y';
792 ELSE
793 l_end_date := temp_rec.end_date;
794 END IF;
795
796 IF temp_rec.start_date < trunc(p_period_start_date) THEN
797 l_start_date := trunc(p_period_start_date);
798 ELSE
799 l_start_date := temp_rec.start_date;
800 END IF;
801
802 IF l_count = 0 THEN
803 l_min_start_date := l_start_date;
804 l_max_end_date := l_end_date;
805 ELSE
806 IF l_start_date < l_min_start_date THEN
807 l_min_start_date := l_start_date;
808 END IF;
809 IF l_end_date > l_max_end_date THEN
810 l_max_end_date := l_end_date;
811 END IF;
812 END IF;
813
814 l_count := l_count + 1;
815
816 l_pt_perc := l_pt_perc + temp_rec.ptp * ((trunc(l_end_date) -
817 trunc(l_start_date)) + 1);
818 END IF;
819
820 END LOOP;
821
822 --find the number of days the assignments has been effective in the
823 --current period
824 l_days_in_pp := nvl(l_max_end_date,trunc(p_period_end_date))
825 - nvl(l_min_start_date,trunc(p_period_start_date))
826 + 1;
827
828 --find the average part time percentage value
829 l_pt_perc := l_pt_perc/l_days_in_pp;
830
831 l_avg_ptp := LEAST(l_pt_perc,125);
832
833 hr_utility.trace('X====get_avg_ptp============================================X');
834
835 RETURN l_avg_ptp;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 hr_utility.trace('Exception in get_retro_avg_ptp SQL-ERRM : '||SQLERRM);
840 log_tech_exception(p_action_context_id => g_payroll_action_id,
841 p_err_msg => 'get_avg_ptp : '|| substr (sqlerrm, 1, 180)); --14793405
842 RETURN -1;
843 END get_avg_ptp;
844
845 -- =============================================================================
846 -- Function Chk_Asg_late_hire to check if an EE assignment is a late hire.
847 -- For ABP Pensions, an EE assignment is considered as late hire if the
848 -- EE crosses tax years. For e.g. hired in 2012 but the first payroll is run
849 -- in 2013. During payroll processing, late hire indicator is stored in a
850 -- balance -- ABP Late Hire. If the YTD value of this balance is <> 0 then the
851 -- EE assignment is considered as a late hire.
852 -- RETURNS TRUE if EE asg is late hire.
853 -- =============================================================================
854 FUNCTION chk_asg_late_hire (p_assignment_id IN NUMBER
855 ,p_bg_id IN NUMBER
856 ,p_end_date IN DATE)
857
858 RETURN BOOLEAN IS
859
860 l_late_hire_ind NUMBER;
861 l_def_bal_id NUMBER;
862
863 BEGIN
864
865 hr_utility.trace('+====chk_asg_late_hire============================================+');
866
867 l_late_hire_ind := 0;
868 l_def_bal_id := -1;
869
870 OPEN csr_defined_bal (c_balance_name => 'ABP Late Hire'
871 ,c_dimension_name => 'Assignment Year To Date'
872 ,c_bg_id => p_bg_id);
873 FETCH csr_defined_bal INTO l_def_bal_id;
874
875 IF csr_defined_bal%NOTFOUND THEN
876 l_def_bal_id := -1;
877 END IF;
878 CLOSE csr_defined_bal;
879
880 IF l_def_bal_id <> -1 THEN
881 --
882 -- Derive the late hire indicator value from the balance
883 --
884 l_late_hire_ind := pay_balance_pkg.get_value( p_assignment_id => p_assignment_id
885 ,p_defined_balance_id => l_def_bal_id
886 ,p_virtual_date => p_end_date);
887 END IF;
888
889 hr_utility.trace('X====chk_asg_late_hire============================================X');
890
891 IF l_late_hire_ind <> 0 THEN
892 RETURN TRUE;
893 ELSE
894 RETURN FALSE;
895 END IF;
896
897 EXCEPTION
898 WHEN OTHERS THEN
899 hr_utility.trace('X====Exception in chk_asg_late_hire===================X'||SQLERRM);
900 log_tech_exception(p_action_context_id => g_payroll_action_id,
901 p_err_msg => 'chk_asg_late_hire : '|| substr (sqlerrm, 1, 180)); --14793405
902 RETURN FALSE;
903 END chk_asg_late_hire;
904
905 -- ==============================================================
906 -- Get_Pension_Salary -- Function to derive pension salary value
907 -- ==============================================================
908 FUNCTION get_pension_salary
909 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
910 ,p_bg_id IN per_all_assignments_f.business_group_id%TYPE
911 ,p_payroll_id IN NUMBER
912 ,p_start_date IN DATE
913 ,p_end_date IN DATE
914 ,p_asg_act IN NUMBER)
915 RETURN NUMBER IS
916 --
917 -- Cursor to get the override ABP Pension Salary
918 --
919 CURSOR csr_get_override_salary(c_assignment_id IN NUMBER
920 ,c_effective_date IN DATE) IS
921 SELECT nvl (aei_information6
922 ,'0')
923 FROM per_assignment_extra_info
924 WHERE assignment_id = c_assignment_id
925 AND information_type = 'NL_ABP_PAR_INFO'
926 AND aei_information_category = 'NL_ABP_PAR_INFO'
927 AND c_effective_date BETWEEN trunc (fnd_date.canonical_to_date (aei_information1))
928 AND trunc (nvl (fnd_date.canonical_to_date (aei_information2)
929 ,hr_general.end_of_time))
930 AND aei_information6 IS NOT NULL;
931
932 l_override_value NUMBER := 0;
933 l_dim_name VARCHAR2(100);
934 l_pension_salary NUMBER := 0;
935
936 BEGIN
937
938 hr_utility.trace('+====Get_Pension_Salary==========================================+');
939 hr_utility.trace('p_end_date '||p_end_date);
940 --
941 -- Fetch the overridden value if there is any override at the ASG EIT
942 --
943 OPEN csr_get_override_salary(p_assignment_id,p_end_date);
944 FETCH csr_get_override_salary INTO l_override_value;
945 IF csr_get_override_salary%FOUND THEN
946 CLOSE csr_get_override_salary;
947 RETURN l_override_value;
948 ELSE
949 CLOSE csr_get_override_salary;
950 END IF;
951
952 --
953 -- Check if the EE assignment is a late hire. Use appropriate dimension
954 -- if the EE is a late hire. Normal ASG_YTD otherwise.
955 --
956 IF chk_asg_late_hire (p_assignment_id => p_assignment_id
957 ,p_bg_id => p_bg_id
958 ,p_end_date => p_end_date) THEN
959 l_dim_name := 'NL Assignment ABP Year To Date Dimension';
960 ELSE
961 l_dim_name := 'Assignment Year To Date';
962 END IF;
963
964 l_pension_salary := get_balance_value(p_assignment_id => p_assignment_id
965 ,p_bg_id => p_bg_id
966 ,p_payroll_id => p_payroll_id
967 ,p_start_date => p_start_date
968 ,p_end_date => p_end_date
969 ,p_balance_name => 'ABP Pension Salary'
970 ,p_dim_name => l_dim_name);
971
972 hr_utility.trace('X====Get_Pension_Salary==========================================X');
973
974 RETURN l_pension_salary;
975
976 EXCEPTION
977 WHEN Others THEN
978 hr_utility.trace('X====Exception in get_pension_salary========================X'||SQLERRM);
979 log_tech_exception(p_action_context_id => g_payroll_action_id,
980 p_err_msg => 'get_pension_salary : '|| substr (sqlerrm, 1, 180)); --14793405
981 RETURN -1;
982
983 END get_pension_salary;
984
985 -- ==============================================================
986 -- Function to derive current period part time percentage
987 -- ==============================================================
988 FUNCTION get_curr_ptp
989 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
990 ,p_bg_id IN per_all_assignments_f.business_group_id%TYPE
991 ,p_payroll_id IN NUMBER
992 ,p_start_date IN DATE
993 ,p_end_date IN DATE
994 ,p_asg_act IN NUMBER) RETURN NUMBER IS
995
996 CURSOR csr_get_reg_working_hours(c_assignment_id IN NUMBER
997 ,c_end_date IN DATE) IS
998 SELECT normal_hours
999 FROM per_all_assignments_f paaf
1000 WHERE paaf.assignment_id = c_assignment_id
1001 AND c_end_date BETWEEN paaf.effective_start_date
1002 AND paaf.effective_end_date;
1003
1004 l_ptp NUMBER := 0;
1005 l_extra_hours NUMBER := 0;
1006 l_reg_working_hours NUMBER := 0;
1007 l_extra_hours_pct NUMBER := 0;
1008 l_curr_ptp NUMBER :=0;
1009 BEGIN
1010
1011 hr_utility.trace('+====get_curr_ptp======================================+');
1012
1013 l_ptp:= get_balance_value(p_assignment_id => p_assignment_id
1014 ,p_bg_id => p_bg_id
1015 ,p_payroll_id => p_payroll_id
1016 ,p_start_date => p_start_date
1017 ,p_end_date => p_end_date
1018 ,p_balance_name => 'ABP Average Part Time Percentage'
1019 --,p_dim_name => 'Assignment Run');
1020 -- ,p_dim_name => 'Assignment Period To Date'); --14839169
1021 ,p_dim_name => 'Assignment Period To Date using Balance Date'); --Bug 16301893
1022
1023 l_extra_hours := get_balance_value(p_assignment_id => p_assignment_id
1024 ,p_bg_id => p_bg_id
1025 ,p_payroll_id => p_payroll_id
1026 ,p_start_date => p_start_date
1027 ,p_end_date => p_end_date
1028 ,p_balance_name => 'ABP Extra Hours Worked'
1029 ,p_dim_name => 'Assignment Period To Date');
1030
1031 hr_utility.trace('l_ptp '||l_ptp);
1032 hr_utility.trace('l_extra_hours '||l_extra_hours);
1033
1034 OPEN csr_get_reg_working_hours(p_assignment_id,p_end_date);
1035 FETCH csr_get_reg_working_hours INTO l_reg_working_hours;
1036 CLOSE csr_get_reg_working_hours;
1037
1038 IF nvl(l_reg_working_hours,0) <> 0 AND l_extra_hours IS NOT NULL THEN
1039 l_extra_hours_pct := (l_extra_hours/l_reg_working_hours) * 100;
1040 l_ptp := l_ptp + l_extra_hours_pct;
1041 END IF;
1042
1043 l_curr_ptp := least(l_ptp,125);
1044
1045 hr_utility.trace('X====get_curr_ptp======================================X');
1046
1047 RETURN l_curr_ptp;
1048
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051 hr_utility.trace('X====Exception in get_curr_ptp=====================X'||SQLERRM);
1052 log_tech_exception(p_action_context_id => g_payroll_action_id,
1053 p_err_msg => 'get_curr_ptp : '|| substr (sqlerrm, 1, 180)); --14793405
1054 RETURN -1;
1055 END get_curr_ptp;
1056
1057 -- ==============================================================
1058 -- Procedure to derive retro period part time percentage details
1059 -- ==============================================================
1060 PROCEDURE get_retro_ptp
1061 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1062 ,p_start_date IN DATE
1063 ,p_effective_date IN DATE
1064 ,p_bg_id IN per_all_assignments_f.business_group_id%TYPE
1065 ,p_payroll_id IN NUMBER
1066 ,retro_ptp_data OUT NOCOPY tPTPData) IS
1067
1068 -- Previously reported ptp to ABP
1069 CURSOR csr_prior_hourly_ee_ptp(c_asg_id IN NUMBER
1070 ,c_effective_date IN DATE
1071 ,c_orig_st_date IN DATE
1072 ,c_orig_ed_date IN DATE
1073 ,c_ele_type_id IN NUMBER
1074 ,c_input_val_id IN NUMBER ) IS
1075 SELECT nvl (sum (round (fnd_number.canonical_to_number (peev.screen_entry_value)
1076 ,2))
1077 ,0) prior_ptp
1078 FROM pay_element_entries_f peef
1079 ,pay_element_links_f pelf
1080 ,pay_element_entry_values_f peev
1081 WHERE peef.effective_start_date > c_effective_date
1082 AND c_effective_date BETWEEN pelf.effective_start_date
1083 AND pelf.effective_end_date
1084 AND peev.effective_start_date > c_effective_date
1085 AND peef.element_link_id = pelf.element_link_id
1086 AND peev.element_entry_id = peef.element_entry_id
1087 AND pelf.element_type_id = c_ele_type_id
1088 AND peev.input_value_id = c_input_val_id
1089 AND peef.assignment_id = c_asg_id
1090 AND peev.screen_entry_value IS NOT NULL
1091 AND pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id) BETWEEN c_orig_st_date
1092 AND c_orig_ed_date;
1093 --=========================================================================
1094 CURSOR csr_get_hire_date(c_asg_id IN NUMBER) IS
1095 SELECT min (effective_start_date) hire_date
1096 FROM per_all_assignments_f asg
1097 WHERE assignment_id = c_asg_id
1098 AND assignment_status_type_id IN
1099 (
1100 SELECT assignment_status_type_id
1101 FROM per_assignment_status_types
1102 WHERE per_system_status = 'ACTIVE_ASSIGN'
1103 AND active_flag = 'Y'
1104 )
1105 AND assignment_type = 'E';
1106 --=========================================================================
1107 CURSOR csr_get_term_date(c_asg_id IN NUMBER) IS
1108 SELECT min (effective_start_date) - 1 term_date
1109 FROM per_all_assignments_f asg
1110 WHERE assignment_id = c_asg_id
1111 AND assignment_status_type_id IN
1112 (
1113 SELECT assignment_status_type_id
1114 FROM per_assignment_status_types
1115 WHERE per_system_status = 'TERM_ASSIGN'
1116 AND active_flag = 'Y'
1117 )
1118 AND assignment_type = 'E';
1119 --=========================================================================
1120 CURSOR csr_ptp_chg_exist(c_asg_id IN NUMBER
1121 ,c_effective_date IN DATE
1122 ,c_ele_type_id IN NUMBER
1123 ,c_input_val_id IN NUMBER ) IS
1124
1125 SELECT 1
1126 FROM pay_element_entries_f peef
1127 ,pay_element_links_f pelf
1128 ,pay_element_entry_values_f peev
1129 WHERE c_effective_date BETWEEN peef.effective_start_date
1130 AND peef.effective_end_date
1131 AND c_effective_date BETWEEN pelf.effective_start_date
1132 AND pelf.effective_end_date
1133 AND c_effective_date BETWEEN peev.effective_start_date
1134 AND peev.effective_end_date
1135 AND peef.element_link_id = pelf.element_link_id
1136 AND peev.element_entry_id = peef.element_entry_id
1137 AND pelf.element_type_id = c_ele_type_id
1138 AND peev.input_value_id = c_input_val_id
1139 AND peef.assignment_id = c_asg_id
1140 AND peev.screen_entry_value IS NOT NULL;
1141 --=========================================================================
1142 -- User Defined Element used to force report the ptp changes.
1143 CURSOR csr_ude_ele_iv_id(c_bg_id IN NUMBER
1144 ,c_effective_date IN DATE) IS
1145 SELECT piv.input_value_id start_dt_id
1146 ,piv1.input_value_id end_dt_id
1147 ,pet.element_type_id
1148 FROM pay_input_values_f piv
1149 ,pay_input_values_f piv1
1150 ,pay_element_types_f pet
1151 WHERE piv.name = 'Start Date'
1152 AND piv.element_type_id = pet.element_type_id
1153 AND piv1.name = 'End Date'
1154 AND piv1.element_type_id = pet.element_type_id
1155 AND pet.element_name = 'ABP Record 05 Reporting'
1156 AND pet.legislation_code IS NULL
1157 AND pet.business_group_id = c_bg_id
1158 AND c_effective_date BETWEEN pet.effective_start_date
1159 AND pet.effective_end_date
1160 AND c_effective_date BETWEEN piv.effective_start_date
1161 AND piv.effective_end_date
1162 AND c_effective_date BETWEEN piv1.effective_start_date
1163 AND piv1.effective_end_date;
1164 --=========================================================================
1165 CURSOR csr_ude_rec(c_effective_date DATE
1166 ,c_ele_type_id NUMBER
1167 ,c_start_dt_id NUMBER
1168 ,c_end_dt_id NUMBER
1169 ,c_asg_id NUMBER) IS
1170 SELECT fnd_date.canonical_to_date (peev.screen_entry_value) start_date
1171 ,fnd_date.canonical_to_date (peev1.screen_entry_value) end_date
1172 FROM pay_element_entries_f peef
1173 ,pay_element_links_f pelf
1174 ,pay_element_entry_values_f peev
1175 ,pay_element_entry_values_f peev1
1176 WHERE c_effective_date BETWEEN peef.effective_start_date
1177 AND peef.effective_end_date
1178 AND c_effective_date BETWEEN pelf.effective_start_date
1179 AND pelf.effective_end_date
1180 AND c_effective_date BETWEEN peev.effective_start_date
1181 AND peev.effective_end_date
1182 AND peef.element_link_id = pelf.element_link_id
1183 AND peev.element_entry_id = peef.element_entry_id
1184 AND pelf.element_type_id = c_ele_type_id
1185 AND peev.input_value_id = c_start_dt_id
1186 AND peef.assignment_id = c_asg_id
1187 AND c_effective_date BETWEEN peev1.effective_start_date
1188 AND peev1.effective_end_date
1189 AND peev1.element_entry_id = peef.element_entry_id
1190 AND peev1.input_value_id = c_end_dt_id;
1191 --=========================================================================
1192 CURSOR csr_ude_rec_ptp(c_asg_id IN NUMBER
1193 ,c_start_date IN DATE
1194 ,c_end_date IN DATE) IS
1195 SELECT DISTINCT
1196 asg.effective_start_date start_date
1197 ,asg.effective_end_date end_date
1198 ,least(fnd_number.canonical_to_number (nvl (target.segment29
1199 ,'100')),125) ptp
1200 FROM per_assignments_f asg
1201 ,hr_soft_coding_keyflex target
1202 WHERE asg.assignment_id = c_asg_id
1203 AND asg.effective_start_date BETWEEN c_start_date
1204 AND nvl (c_end_date
1205 ,hr_general.end_of_time)
1206 AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1207 AND target.enabled_flag = 'Y'
1208 ORDER BY start_date;
1209 --=========================================================================
1210 CURSOR csr_get_orig_dt_earned(c_asg_id IN NUMBER
1211 ,c_effective_date IN DATE
1212 ,c_ele_type_id IN NUMBER
1213 ,c_input_val_id IN NUMBER ) IS
1214
1215 SELECT min (pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id)) start_date
1216 ,max (pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id)) end_date
1217 FROM pay_element_entries_f peef
1218 ,pay_element_links_f pelf
1219 ,pay_element_entry_values_f peev
1220 WHERE c_effective_date BETWEEN peef.effective_start_date
1221 AND peef.effective_end_date
1222 AND c_effective_date BETWEEN pelf.effective_start_date
1223 AND pelf.effective_end_date
1224 AND c_effective_date BETWEEN peev.effective_start_date
1225 AND peev.effective_end_date
1226 AND peef.element_link_id = pelf.element_link_id
1227 AND peev.element_entry_id = peef.element_entry_id
1228 AND pelf.element_type_id = c_ele_type_id
1229 AND peev.input_value_id = c_input_val_id
1230 AND peef.assignment_id = c_asg_id
1231 AND peev.screen_entry_value IS NOT NULL;
1232 --=========================================================================
1233 CURSOR csr_get_retro_ele(c_element_name IN VARCHAR2
1234 ,c_input_value_name IN VARCHAR2) IS
1235 SELECT piv.input_value_id
1236 ,pet.element_type_id
1237 FROM pay_input_values_f piv
1238 ,pay_element_types_f pet
1239 WHERE piv.name = c_input_value_name
1240 AND piv.element_type_id = pet.element_type_id
1241 AND pet.element_name = c_element_name;
1242 --=========================================================================
1243 CURSOR csr_cur_ptp(c_effective_date IN DATE
1244 ,c_asg_id IN NUMBER) IS
1245 SELECT least (fnd_number.canonical_to_number (nvl (target.segment29
1246 ,'100'))
1247 ,125) ptp
1248 FROM per_assignments_f asg
1249 ,hr_soft_coding_keyflex target
1250 WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1251 AND asg.assignment_id = c_asg_id
1252 AND target.enabled_flag = 'Y'
1253 AND trunc (c_effective_date) BETWEEN asg.effective_start_date
1254 AND asg.effective_end_date;
1255 --=========================================================================
1256 CURSOR csr_cur_sal_hour (c_effective_date IN DATE
1257 ,c_asg_id IN NUMBER) IS
1258 SELECT nvl (hourly_salaried_code
1259 ,'H') hourly_salaried_code
1260 FROM per_assignments_f asg
1261 ,hr_soft_coding_keyflex target
1262 WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1263 AND asg.assignment_id = c_asg_id
1264 AND target.enabled_flag = 'Y'
1265 AND trunc (c_effective_date) BETWEEN asg.effective_start_date
1266 AND asg.effective_end_date;
1267 --=========================================================================
1268 CURSOR csr_get_retro_ptp(c_asg_id IN NUMBER
1269 ,c_effective_date IN DATE
1270 ,c_ele_type_id IN NUMBER
1271 ,c_input_val_id IN NUMBER ) IS
1272 SELECT pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id) start_date
1273 ,pay_paywsmee_pkg.get_original_date_earned (peef.element_entry_id) end_date
1274 ,fnd_number.canonical_to_number (peev.screen_entry_value) ptp
1275 FROM pay_element_entries_f peef
1276 ,pay_element_links_f pelf
1277 ,pay_element_entry_values_f peev
1278 WHERE c_effective_date BETWEEN peef.effective_start_date
1279 AND peef.effective_end_date
1280 AND c_effective_date BETWEEN pelf.effective_start_date
1281 AND pelf.effective_end_date
1282 AND c_effective_date BETWEEN peev.effective_start_date
1283 AND peev.effective_end_date
1284 AND peef.element_link_id = pelf.element_link_id
1285 AND peev.element_entry_id = peef.element_entry_id
1286 AND pelf.element_type_id = c_ele_type_id
1287 AND peev.input_value_id = c_input_val_id
1288 AND peef.assignment_id = c_asg_id
1289 AND peev.screen_entry_value IS NOT NULL
1290 ORDER BY start_date;
1291 --=========================================================================
1292 l_retro_pv_iv_id NUMBER;
1293 l_retro_ptp_element_id NUMBER;
1294 l_hourly_salaried_code per_assignments_f.hourly_salaried_code%TYPE;
1295 l_asg_hire_date DATE;
1296 l_asg_term_date DATE;
1297 l_ptp_chg_exist NUMBER;
1298 l_orig_dt_earned csr_get_orig_dt_earned%ROWTYPE;
1299 l_count NUMBER:=0;
1300 l_sd_input_val_id NUMBER;
1301 l_ed_input_val_id NUMBER;
1302 l_ude_ele_type_id NUMBER;
1303 l_ude_rec csr_ude_rec%ROWTYPE;
1304 l_cur_ptp NUMBER;
1305 l_retro_ptp_count NUMBER:=0;
1306 l_payroll_period csr_get_payroll_period%ROWTYPE;
1307 l_prior_hourly_ee_ptp NUMBER;
1308 l_avg_ptp NUMBER;
1309
1310 BEGIN
1311 hr_utility.trace('+====get_retro_ptp======================================+');
1312 -- ============================================================================
1313 -- BEGIN Populate Retro PTP change information
1314 -- ============================================================================
1315
1316 OPEN csr_get_retro_ele('Retro ABP Pensions Part Time Percentage'
1317 ,'Part Time Percentage');
1318 FETCH csr_get_retro_ele INTO l_retro_pv_iv_id,l_retro_ptp_element_id;
1319 CLOSE csr_get_retro_ele;
1320
1321
1322 -- Derive the current ptp to check if the EE is
1323 -- Hourly or a regular EE
1324 --
1325 FOR cur_ptp_rec IN csr_cur_ptp(p_effective_date,p_assignment_id) LOOP
1326 l_cur_ptp := cur_ptp_rec.ptp;
1327 END LOOP;
1328
1329 -- Fetching the hourly / salaried indicator.
1330 OPEN csr_cur_sal_hour(p_effective_date,p_assignment_id);
1331 FETCH csr_cur_sal_hour INTO l_hourly_salaried_code;
1332 CLOSE csr_cur_sal_hour;
1333
1334 --
1335 -- Derive the hire and termination dates
1336 --
1337 OPEN csr_get_hire_date(p_assignment_id);
1338 FETCH csr_get_hire_date INTO l_asg_hire_date;
1339 CLOSE csr_get_hire_date;
1340
1341 OPEN csr_get_term_date(p_assignment_id);
1342 FETCH csr_get_term_date INTO l_asg_term_date;
1343 IF csr_get_term_date%NOTFOUND THEN
1344 l_asg_term_date := NULL;
1345 END IF;
1346 CLOSE csr_get_term_date;
1347
1348 --check if ptp change exist for the assignment
1349 OPEN csr_ptp_chg_exist (c_asg_id => p_assignment_id
1350 ,c_effective_date => p_start_date
1351 ,c_ele_type_id => l_retro_ptp_element_id
1352 ,c_input_val_id => l_retro_pv_iv_id);
1353 FETCH csr_ptp_chg_exist INTO l_ptp_chg_exist;
1354
1355 IF csr_ptp_chg_exist%FOUND THEN
1356
1357 IF l_cur_ptp <> 0 THEN --Regular EE
1358
1359 OPEN csr_get_orig_dt_earned(p_assignment_id, p_start_date, l_retro_ptp_element_id, l_retro_pv_iv_id);
1360 FETCH csr_get_orig_dt_earned INTO l_orig_dt_earned;
1361 CLOSE csr_get_orig_dt_earned;
1362
1363 IF l_orig_dt_earned.start_date >= g_valid_start_date AND l_orig_dt_earned.end_date >= g_valid_start_date THEN
1364 FOR l_payroll_period IN csr_get_payroll_period(p_payroll_id,l_orig_dt_earned.start_date,l_orig_dt_earned.end_date)
1365 LOOP
1366 l_avg_ptp := get_avg_ptp(p_assignment_id,l_payroll_period.start_date,l_payroll_period.end_date);
1367 l_retro_ptp_count := l_retro_ptp_count + 1;
1368 retro_ptp_data(l_retro_ptp_count).start_date := l_payroll_period.start_date;
1369 retro_ptp_data(l_retro_ptp_count).end_date := l_payroll_period.end_date;
1370 retro_ptp_data(l_retro_ptp_count).ptp := l_avg_ptp;
1371 END LOOP;
1372 END IF;
1373
1374 ELSIF l_cur_ptp = 0 AND l_hourly_salaried_code <> 'S' THEN -- Hourly EE
1375
1376 FOR retro_rec IN csr_get_retro_ptp(c_asg_id => p_assignment_id
1377 ,c_effective_date => p_start_date
1378 ,c_ele_type_id => l_retro_ptp_element_id
1379 ,c_input_val_id => l_retro_pv_iv_id)
1380 LOOP
1381 IF retro_rec.start_date >= g_valid_start_date THEN
1382 OPEN csr_get_period(p_payroll_id,retro_rec.start_date);
1383 FETCH csr_get_period INTO retro_rec.start_date,retro_rec.end_date;
1384 CLOSE csr_get_period;
1385
1386 l_retro_ptp_count := l_retro_ptp_count + 1;
1387 IF to_char(l_asg_hire_date,'MM/YYYY') = to_char(retro_rec.start_date,'MM/YYYY') THEN
1388 -- EE was hired in the same month as we are trying to report.
1389 retro_ptp_data(l_retro_ptp_count).start_date := l_asg_hire_date;
1390 ELSE
1391 -- EE was hired in a different month
1392 retro_ptp_data(l_retro_ptp_count).start_date := retro_rec.start_date;
1393 END IF;
1394
1395 IF l_asg_term_date IS NOT NULL THEN
1396 IF to_char(l_asg_term_date,'MM/YYYY') = to_char(retro_rec.end_date,'MM/YYYY') THEN
1397 -- EE was terminated in the same month as we are trying to report.
1398 retro_ptp_data(l_retro_ptp_count).end_date := l_asg_term_date;
1399 ELSE
1400 -- EE was terminated in a different month
1401 retro_ptp_data(l_retro_ptp_count).end_date := retro_rec.end_date;
1402 END IF;
1403 ELSE
1404 retro_ptp_data(l_retro_ptp_count).end_date := retro_rec.end_date;
1405 END IF;
1406
1407 -- Derive the retro hourly ptp reported earlier to ABP
1408 l_prior_hourly_ee_ptp := 0;
1409
1410 OPEN csr_prior_hourly_ee_ptp
1411 (c_asg_id => p_assignment_id
1412 ,c_effective_date => p_start_date
1413 ,c_orig_st_date => retro_rec.start_date
1414 ,c_orig_ed_date => retro_rec.end_date
1415 ,c_ele_type_id => l_retro_ptp_element_id
1416 ,c_input_val_id => l_retro_pv_iv_id);
1417 FETCH csr_prior_hourly_ee_ptp INTO l_prior_hourly_ee_ptp;
1418 CLOSE csr_prior_hourly_ee_ptp;
1419
1420 retro_ptp_data(l_retro_ptp_count).ptp :=
1421 (retro_rec.ptp +
1422 l_prior_hourly_ee_ptp);
1423
1424 IF (LEAST(retro_rec.end_date,nvl(l_asg_term_date,retro_rec.end_date)) - GREATEST(l_asg_hire_date,retro_rec.start_date)+1) > 0
1425 THEN
1426 retro_ptp_data(l_retro_ptp_count).ptp :=
1427 retro_ptp_data(l_retro_ptp_count).ptp *
1428 (
1429 ((retro_rec.end_date - retro_rec.start_date)+1)
1430 /(LEAST(retro_rec.end_date,nvl(l_asg_term_date,retro_rec.end_date)) -
1431 GREATEST(l_asg_hire_date,retro_rec.start_date)+1)
1432 );
1433 END IF;
1434 END IF;
1435 END LOOP;
1436
1437 END IF;
1438
1439 END IF;
1440
1441 CLOSE csr_ptp_chg_exist;
1442
1443 --7361922
1444 OPEN csr_ude_ele_iv_id(p_bg_id, p_start_date) ;
1445 FETCH csr_ude_ele_iv_id INTO l_sd_input_val_id, l_ed_input_val_id, l_ude_ele_type_id;
1446 CLOSE csr_ude_ele_iv_id;
1447
1448 OPEN csr_ude_rec(p_start_date
1449 ,l_ude_ele_type_id
1450 ,l_sd_input_val_id
1451 ,l_ed_input_val_id
1452 ,p_assignment_id);
1453 FETCH csr_ude_rec INTO l_ude_rec;
1454 IF csr_ude_rec%FOUND THEN
1455 FOR rec in csr_ude_rec_ptp(p_assignment_id, l_ude_rec.start_date, l_ude_rec.end_date)
1456 LOOP
1457 l_retro_ptp_count := l_retro_ptp_count + 1;
1458 retro_ptp_data(l_retro_ptp_count).start_date := rec.start_date;
1459 retro_ptp_data(l_retro_ptp_count).end_date := rec.end_date;
1460 retro_ptp_data(l_retro_ptp_count).ptp := rec.ptp;
1461 END LOOP;
1462 CLOSE csr_ude_rec;
1463 ELSE
1464 CLOSE csr_ude_rec;
1465 END IF;
1466 --7361922
1467
1468 hr_utility.trace('X====get_retro_ptp======================================X');
1469 EXCEPTION
1470 WHEN OTHERS THEN
1471 hr_utility.trace('Exception in get_retro_ptp'||SQLERRM);
1472 log_tech_exception(p_action_context_id => g_payroll_action_id,
1473 p_err_msg => 'get_retro_ptp : '|| substr (sqlerrm, 1, 180));
1474 END get_retro_ptp;
1475
1476 -- ===========================================================================
1477 -- Procedure to derive the Contribution Base for Pension Types
1478 -- OPNP,AAOP
1479 -- ===========================================================================
1480 PROCEDURE pension_basis(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1481 ,p_person_id IN NUMBER
1482 ,p_bg_id IN per_all_assignments_f.business_group_id%TYPE
1483 ,p_payroll_id IN NUMBER
1484 ,p_start_date IN DATE
1485 ,p_end_date IN DATE
1486 ,p_contr_basis_values IN OUT NOCOPY tPENSIONData)IS
1487
1488 -- ============================================================================
1489 -- Cursor to get the element entry ids for the Retro Pension Deduction elements
1490 -- ============================================================================
1491 CURSOR csr_get_retro_entry(c_element_type_id in number
1492 ,c_assignment_action_id in number) IS
1493 SELECT element_entry_id
1494 FROM pay_run_results prr
1495 WHERE prr.assignment_action_id = c_assignment_action_id
1496 AND prr.element_type_id = c_element_type_id
1497 ORDER BY element_entry_id;
1498
1499 -- ============================================================================
1500 -- Cursor to get the input value id for the pension basis input value
1501 -- ============================================================================
1502 CURSOR csr_get_iv_id(c_element_type_id in number) IS
1503 SELECT input_value_id
1504 FROM pay_input_values_f
1505 WHERE element_type_id = c_element_type_id
1506 AND name = 'ABP Employee Pension Basis';
1507
1508 -- ============================================================================
1509 -- Cursor to get the entry value for the given element entry id and input value
1510 -- (for all numeric input values)
1511 -- ============================================================================
1512 CURSOR csr_get_retro_num_value(c_element_entry_id in number
1513 ,c_input_value_id in number) IS
1514 SELECT fnd_number.canonical_to_number (nvl (screen_entry_value
1515 ,'0')) amt
1516 ,pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id) date_earned
1517 FROM pay_element_entry_values_f
1518 WHERE element_entry_id = c_element_entry_id
1519 AND input_value_id = c_input_value_id;
1520 --=========================================================================
1521
1522 TYPE t_basis_rec IS TABLE OF csr_contr_basis%ROWTYPE INDEX BY Binary_Integer;
1523
1524 l_contr_basis t_basis_rec;
1525 l_retro_contr_basis csr_get_retro_num_value%ROWTYPE;
1526 l_basis_count NUMBER := 0;
1527 k NUMBER := 0;
1528 l_basis_iv_id NUMBER;
1529 l_date_earned DATE;
1530 l_date_earned2 DATE;
1531 l_rr_exists NUMBER;
1532 l_new_start DATE;
1533 l_old_start DATE;
1534 l_old_start_can DATE;
1535 l_new_start_can DATE;
1536 l_beg_new_st DATE;
1537 l_beg_new_st2 DATE;
1538 l_end_new_st DATE;
1539 l_loop_end_date DATE;
1540 l_asg_act_xst NUMBER:=0;
1541 l_prev_basis_amt NUMBER;
1542 l_chg_rep csr_get_prev_hire_info%ROWTYPE;
1543
1544 BEGIN
1545 hr_utility.trace('+====pension_basis======================================+');
1546
1547 -- ==============================================================================
1548 -- BEGIN Populate Contribution Basis details for Current period,Retro current year
1549 -- ==============================================================================
1550
1551 --Populate the defined balance ids for the balance names pertaining
1552 --to OPNP and AAOP Pension types
1553 FOR temp_rec IN csr_contr_basis(c_bg_id => p_bg_id
1554 ,c_effective_date => p_end_date ) LOOP
1555 l_basis_count := l_basis_count + 1;
1556 l_contr_basis(l_basis_count) := temp_rec;
1557 FOR temp_rec1 IN csr_defined_bal(c_balance_name => l_contr_basis(l_basis_count).bal_name
1558 ,c_dimension_name => 'Assignment Run'
1559 ,c_bg_id => p_bg_id)
1560 LOOP
1561 l_contr_basis(l_basis_count).defined_bal_id :=
1562 temp_rec1.defined_balance_id;
1563 END LOOP;
1564 END LOOP;
1565
1566 IF l_contr_basis.count > 0 THEN
1567 k := 1;
1568 FOR i IN l_contr_basis.FIRST..l_contr_basis.LAST
1569 LOOP
1570 l_rr_exists := 0;
1571
1572 hr_utility.trace('Current element : '||l_contr_basis(i).element_type_id);
1573 hr_utility.trace('Assignment Id : '||p_assignment_id);
1574 hr_utility.trace('Payroll id : '||p_payroll_id);
1575 hr_utility.trace('Start date : '||p_start_date);
1576 hr_utility.trace('End date : '||p_end_date);
1577
1578 FOR act_rec IN csr_asg_act (
1579 c_assignment_id => p_assignment_id
1580 ,c_payroll_id => p_payroll_id
1581 ,c_con_set_id => NULL
1582 ,c_start_date => p_start_date
1583 ,c_end_date => p_end_date)
1584 LOOP
1585 hr_utility.trace('Asg act id : '||act_rec.assignment_action_id);
1586 --
1587 -- Current Year Retro
1588 --
1589 FOR temp_rec IN csr_get_retro_entry
1590 (c_element_type_id => fnd_number.canonical_to_number(
1591 l_contr_basis(i).cy_retro_element_id)
1592 ,c_assignment_action_id => act_rec.assignment_action_id)
1593 LOOP
1594 hr_utility.trace('Found current year retro entries - Contr Basis');
1595 --
1596 -- Fetch the input value id for ABP Employee Pension Basis input value
1597 --
1598 OPEN csr_get_iv_id(c_element_type_id =>
1599 fnd_number.canonical_to_number(l_contr_basis(i).cy_retro_element_id));
1600 FETCH csr_get_iv_id INTO l_basis_iv_id;
1601 CLOSE csr_get_iv_id;
1602
1603 --
1604 -- Fetch the pension basis retro value for this current
1605 -- element entry id
1606 --
1607 OPEN csr_get_retro_num_value(c_element_entry_id => temp_rec.element_entry_id
1608 ,c_input_value_id => l_basis_iv_id);
1609 FETCH csr_get_retro_num_value INTO l_retro_contr_basis;
1610 CLOSE csr_get_retro_num_value;
1611
1612 IF l_retro_contr_basis.date_earned IS NOT NULL THEN
1613 OPEN csr_get_period(p_payroll_id,l_retro_contr_basis.date_earned);
1614 FETCH csr_get_period INTO l_date_earned,l_date_earned2;
1615 CLOSE csr_get_period;
1616 END IF;
1617
1618 IF l_retro_contr_basis.amt <> 0 THEN
1619 OPEN csr_prev_basis_amt (p_assignment_id,l_date_earned,l_contr_basis(i).pension_type);
1620 FETCH csr_prev_basis_amt INTO l_prev_basis_amt;
1621 IF csr_prev_basis_amt%FOUND THEN
1622 l_retro_contr_basis.amt := l_retro_contr_basis.amt + nvl(l_prev_basis_amt,0);
1623 END IF;
1624 CLOSE csr_prev_basis_amt;
1625
1626 p_contr_basis_values(k).pension_type := l_contr_basis(i).pension_type;
1627 p_contr_basis_values(k).pension_type_variant := NULL; --No pension type variant for AAOP/OPNP
1628 p_contr_basis_values(k).contr_base := l_retro_contr_basis.amt;
1629 p_contr_basis_values(k).contr_amt := NULL;
1630 p_contr_basis_values(k).date_earned := trunc(pay_paywsmee_pkg.get_original_date_earned(temp_rec.element_entry_id),'MM'); --l_date_earned; --Bug 16301893
1631
1632 k := k + 1;
1633
1634 END IF;
1635 END LOOP;
1636
1637 IF NOT chk_asg_term_py (p_assignment_id => p_assignment_id
1638 ,p_ext_st => p_start_date) THEN
1639 --
1640 -- Check if Run Results exist for this element/ass act
1641 --
1642 IF chk_rr_exist (p_ass_act_id => act_rec.assignment_action_id
1643 ,p_element_type_id => l_contr_basis(i).element_type_id ) THEN
1644 -- Call pay_balance_pkg
1645 hr_utility.trace('Run results exist for current period - Contr Basis');
1646
1647 IF l_contr_basis(i).defined_bal_id <> -1 THEN
1648 p_contr_basis_values(k).pension_type := l_contr_basis(i).pension_type;
1649 p_contr_basis_values(k).pension_type_variant := NULL;
1650 p_contr_basis_values(k).contr_base :=
1651 pay_balance_pkg.get_value
1652 (p_defined_balance_id => l_contr_basis(i).defined_bal_id
1653 ,p_assignment_action_id => act_rec.assignment_action_id);
1654
1655 hr_utility.trace('Defined bal id used :'||l_contr_basis(i).defined_bal_id);
1656
1657 p_contr_basis_values(k).date_earned := trunc(act_rec.date_earned,'MM'); --p_start_date;--Bug 16301893
1658
1659 k := k + 1;
1660 END IF;--end of defined bal check
1661
1662 END IF;-- End of rr check
1663 END IF; -- Check asg term in prev year
1664
1665 END LOOP; -- Asg Acts
1666 END LOOP; -- Elements
1667 END IF;
1668 -- ==============================================================================
1669 -- END Populate Contribution Basis details for Current period,Retro current year
1670 -- ==============================================================================
1671
1672 --
1673 -- Change in hire date
1674 --
1675 OPEN csr_hire_dt_chg(c_person_id => p_person_id
1676 ,c_start_date => p_start_date
1677 ,c_end_date => p_end_date);
1678 FETCH csr_hire_dt_chg INTO l_old_start_can,l_new_start_can;
1679 IF csr_hire_dt_chg%NOTFOUND THEN
1680 l_new_start := NULL;
1681 l_old_start := NULL;
1682 ELSIF csr_hire_dt_chg%FOUND THEN
1683 l_new_start := to_date(l_new_start_can,'DD-MM-RRRR');
1684 l_old_start := to_date(l_old_start_can,'DD-MM-RRRR');
1685 END IF;
1686 CLOSE csr_hire_dt_chg;
1687
1688 IF l_old_start IS NOT NULL AND l_new_start IS NOT NULL THEN
1689
1690 --
1691 -- Hire Date is changed to the past
1692 --
1693 IF trunc(l_new_start) < trunc(l_old_start) THEN
1694 --
1695 -- Derive the beginning date
1696 --
1697 OPEN csr_get_prev_hire_info(p_assignment_id,l_new_start);
1698 FETCH csr_get_prev_hire_info INTO l_chg_rep;
1699 IF csr_get_prev_hire_info%NOTFOUND THEN
1700
1701 l_new_start := greatest(l_new_start,g_valid_start_date);
1702
1703 OPEN csr_get_period(p_payroll_id,l_new_start);
1704 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
1705 CLOSE csr_get_period;
1706
1707 --
1708 -- If the beginning date is less then the current start date
1709 -- Loop through the assignment actions to derive contrib amounts
1710 -- of that period
1711 --
1712 WHILE trunc(l_beg_new_st) < trunc(p_start_date)
1713 LOOP
1714
1715 IF l_contr_basis.count > 0 THEN
1716 FOR i IN l_contr_basis.FIRST..l_contr_basis.LAST
1717 LOOP
1718 l_rr_exists := 0;
1719 hr_utility.trace('current element : '||l_contr_basis(i).element_type_id);
1720 hr_utility.trace('asg id : '||p_assignment_id);
1721
1722 FOR act_rec IN csr_asg_act (
1723 c_assignment_id => p_assignment_id
1724 ,c_payroll_id => p_payroll_id
1725 ,c_con_set_id => NULL
1726 ,c_start_date => l_beg_new_st
1727 ,c_end_date => l_end_new_st)
1728 LOOP
1729
1730 -- Check if Run Results exist for this element/ass act
1731 IF chk_rr_exist (p_ass_act_id => act_rec.assignment_action_id
1732 ,p_element_type_id => l_contr_basis(i).element_type_id ) THEN
1733 -- Call pay_balance_pkg
1734
1735 IF l_contr_basis(i).defined_bal_id <> -1 THEN
1736 p_contr_basis_values(k).pension_type := l_contr_basis(i).pension_type;
1737 p_contr_basis_values(k).pension_type_variant := NULL;
1738 p_contr_basis_values(k).contr_base :=
1739 pay_balance_pkg.get_value
1740 (p_defined_balance_id => l_contr_basis(i).defined_bal_id
1741 ,p_assignment_action_id => act_rec.assignment_action_id);
1742 hr_utility.trace('defined bal id used :'||l_contr_basis(i).defined_bal_id);
1743
1744 p_contr_basis_values(k).date_earned := l_beg_new_st;
1745
1746 k := k + 1;
1747 END IF;-- Defined bal check
1748 END IF;-- RR exist check
1749 END LOOP; -- Ass acts
1750 END LOOP; -- All elements
1751 END IF; -- Elements exist
1752
1753 OPEN csr_get_period(p_payroll_id,(l_end_new_st + 1));
1754 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
1755 CLOSE csr_get_period;
1756
1757 END LOOP; -- Loop through the period
1758 END IF;
1759 CLOSE csr_get_prev_hire_info;
1760 ELSIF trunc(l_new_start) > trunc(l_old_start) AND l_new_start >= g_valid_start_date THEN
1761 --
1762 -- Derive the beginning date
1763 --
1764
1765 l_old_start := greatest(l_old_start,g_valid_start_date);
1766
1767 OPEN csr_get_period(p_payroll_id,l_old_start);
1768 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
1769 CLOSE csr_get_period;
1770
1771 OPEN csr_get_period(p_payroll_id,l_new_start);
1772 FETCH csr_get_period INTO l_beg_new_st2,l_loop_end_date;
1773 CLOSE csr_get_period;
1774
1775 l_loop_end_date := least ( p_start_date -1,l_loop_end_date);
1776 --
1777 --
1778 -- Loop through the dates to derive data to be reported to ABP
1779 -- this might include ony the differences of that period or the entire amount
1780 -- for the month
1781 --
1782 WHILE trunc(l_beg_new_st) < l_loop_end_date
1783 LOOP
1784
1785 l_asg_act_xst := 0;
1786
1787 IF l_contr_basis.count > 0 THEN
1788 FOR i IN l_contr_basis.FIRST..l_contr_basis.LAST
1789 LOOP
1790 l_rr_exists := 0;
1791 hr_utility.trace('current element : '||l_contr_basis(i).element_type_id);
1792 hr_utility.trace('asg id : '||p_assignment_id);
1793
1794 FOR act_rec IN csr_asg_act (
1795 c_assignment_id => p_assignment_id
1796 ,c_payroll_id => p_payroll_id
1797 ,c_con_set_id => NULL
1798 ,c_start_date => l_beg_new_st
1799 ,c_end_date => l_end_new_st)
1800 LOOP
1801
1802 -- Check if Run Results exist for this element/ass act
1803 IF chk_rr_exist (p_ass_act_id => act_rec.assignment_action_id
1804 ,p_element_type_id => l_contr_basis(i).element_type_id ) THEN
1805 -- Call pay_balance_pkg
1806
1807 IF l_contr_basis(i).defined_bal_id <> -1 THEN
1808 p_contr_basis_values(k).pension_type := l_contr_basis(i).pension_type;
1809 p_contr_basis_values(k).pension_type_variant := NULL;
1810 p_contr_basis_values(k).contr_base :=
1811 pay_balance_pkg.get_value
1812 (p_defined_balance_id => l_contr_basis(i).defined_bal_id
1813 ,p_assignment_action_id => act_rec.assignment_action_id);
1814 hr_utility.trace('defined bal id used :'||l_contr_basis(i).defined_bal_id);
1815
1816 p_contr_basis_values(k).date_earned := l_beg_new_st;
1817 -- substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
1818
1819 OPEN csr_prev_basis_amt (p_assignment_id,l_beg_new_st,l_contr_basis(i).pension_type);
1820 FETCH csr_prev_basis_amt INTO l_prev_basis_amt;
1821 IF csr_prev_basis_amt%FOUND THEN
1822 p_contr_basis_values(k).contr_base := p_contr_basis_values(k).contr_base
1823 - l_prev_basis_amt;
1824 END IF;
1825 CLOSE csr_prev_basis_amt;
1826
1827 l_asg_act_xst := 1;
1828 k := k + 1;
1829 END IF;-- Defined bal check
1830 END IF;-- RR exist check
1831
1832 END LOOP; -- Ass acts
1833 END LOOP; -- All elements
1834 END IF; -- Elements exist
1835
1836
1837 IF l_contr_basis.count > 0 AND l_asg_act_xst = 0 THEN
1838 FOR i IN l_contr_basis.FIRST..l_contr_basis.LAST
1839 LOOP
1840 OPEN csr_prev_basis_amt (p_assignment_id,l_beg_new_st,l_contr_basis(i).pension_type);
1841 FETCH csr_prev_basis_amt INTO l_prev_basis_amt;
1842 IF csr_prev_basis_amt%FOUND THEN
1843 p_contr_basis_values(k).pension_type := l_contr_basis(i).pension_type;
1844 p_contr_basis_values(k).pension_type_variant := NULL;
1845 p_contr_basis_values(k).contr_base := -1 * l_prev_basis_amt;
1846 p_contr_basis_values(k).date_earned := l_beg_new_st;
1847 -- substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
1848 k := k + 1;
1849 END IF;
1850 CLOSE csr_prev_basis_amt;
1851 END LOOP;
1852 END IF;
1853
1854 OPEN csr_get_period(p_payroll_id,(l_end_new_st + 1));
1855 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
1856 CLOSE csr_get_period;
1857
1858 END LOOP; -- Loop through the period
1859 END IF; -- new start date < old start dt
1860 END IF; -- dates are not null
1861
1862 hr_utility.trace('X====pension_basis======================================X');
1863
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 hr_utility.trace('X====Exception in pension_basis========X'||SQLERRM);
1867 log_tech_exception(p_action_context_id => g_payroll_action_id,
1868 p_err_msg => 'pension_basis : '|| substr (sqlerrm, 1, 180)); --14793405
1869 END pension_basis;
1870
1871 -- ===================================================================================
1872 -- Procedure to derive the Contribution Amount for Pension Types
1873 -- OPNP_65,OPNP_W25,OPNP_W50,PPP,FPU_B
1874 -- ===================================================================================
1875 PROCEDURE pension_contr(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1876 ,p_person_id IN NUMBER
1877 ,p_bg_id IN per_all_assignments_f.business_group_id%TYPE
1878 ,p_payroll_id IN NUMBER
1879 ,p_start_date IN DATE
1880 ,p_end_date IN DATE
1881 ,p_contr_amt_values IN OUT NOCOPY tPENSIONData)IS
1882
1883 -- ==============================================================
1884 -- Cursor to get the prev reported contr amt value for a period
1885 -- ==============================================================
1886 CURSOR csr_prev_contr_amt (c_assignment_id IN NUMBER
1887 ,c_effective_date IN DATE
1888 ,c_pension_type IN VARCHAR2
1889 ,c_pension_type_variant IN VARCHAR2) IS
1890 SELECT fnd_number.canonical_to_number(pai.action_information8) contr_amt
1891 FROM pay_action_information pai
1892 WHERE pai.action_information_category = 'NL_ABP_PENSION_INFO'
1893 AND pai.action_context_type = 'AAP'
1894 AND pai.assignment_id = c_assignment_id
1895 AND pai.action_information1 = c_pension_type
1896 AND pai.action_information2 = c_pension_type_variant
1897 AND c_effective_date = fnd_date.canonical_to_date(pai.action_information4)
1898 ORDER BY pai.action_context_id DESC;
1899 --=========================================================================
1900
1901 l_new_start DATE;
1902 l_old_start DATE;
1903 l_old_start_can DATE;
1904 l_new_start_can DATE;
1905 l_asg_act_id NUMBER;
1906 l_contr_count NUMBER:=0;
1907 l_contr_amt NUMBER;
1908 l_def_bal_id NUMBER;
1909 l_date_earned DATE;
1910 l_date_earned2 DATE;
1911 l_beg_new_st DATE;
1912 l_beg_new_st2 DATE;
1913 l_end_new_st DATE;
1914 l_loop_end_date DATE;
1915 l_asg_act_xst NUMBER:=0;
1916 l_prev_contr_amt NUMBER;
1917 l_chg_rep csr_get_prev_hire_info%ROWTYPE;
1918
1919 BEGIN
1920 hr_utility.trace('+====pension_contr======================================+');
1921
1922 OPEN csr_hire_dt_chg(c_person_id => p_person_id
1923 ,c_start_date => p_start_date
1924 ,c_end_date => p_end_date);
1925 FETCH csr_hire_dt_chg INTO l_old_start_can,l_new_start_can;
1926 IF csr_hire_dt_chg%NOTFOUND THEN
1927 l_new_start := NULL;
1928 l_old_start := NULL;
1929 ELSIF csr_hire_dt_chg%FOUND THEN
1930 l_new_start := to_date(l_new_start_can,'DD-MM-RRRR');
1931 l_old_start := to_date(l_old_start_can,'DD-MM-RRRR');
1932 END IF;
1933 CLOSE csr_hire_dt_chg;
1934
1935 --first fetch the maximum assignment action id
1936 OPEN csr_asg_act1 (
1937 c_assignment_id => p_assignment_id
1938 ,c_payroll_id => p_payroll_id
1939 ,c_con_set_id => NULL
1940 ,c_start_date => p_start_date
1941 ,c_end_date => p_end_date);
1942 FETCH csr_asg_act1 INTO l_asg_act_id;
1943 IF csr_asg_act1%FOUND THEN
1944 CLOSE csr_asg_act1;
1945 l_contr_count := p_contr_amt_values.count+1;
1946
1947 FOR v_csr_asg_act IN csr_asg_act (
1948 c_assignment_id => p_assignment_id
1949 ,c_payroll_id => p_payroll_id
1950 ,c_con_set_id => NULL
1951 ,c_start_date => p_start_date
1952 ,c_end_date => p_end_date)
1953 LOOP
1954
1955 --loop through the retro and normal deduction amount rows
1956 FOR temp_rec IN csr_contr_amount(c_bg_id => p_bg_id
1957 ,c_effective_date => v_csr_asg_act.date_earned
1958 ,c_asg_id => p_assignment_id)
1959 LOOP
1960
1961 IF temp_rec.date_earned IS NOT NULL THEN
1962 OPEN csr_get_period(p_payroll_id,temp_rec.date_earned);
1963 FETCH csr_get_period INTO l_date_earned,l_date_earned2;
1964 CLOSE csr_get_period;
1965 END IF;
1966 --if the amount is -999999 then fetch the balance value
1967 IF temp_rec.contr_amt = -999999 THEN
1968 l_contr_amt := 0;
1969 OPEN csr_defined_bal1(c_balance_type_id => temp_rec.ee_contribution_bal_type_id
1970 ,c_dimension_name => 'Assignment Run'
1971 ,c_bg_id => p_bg_id
1972 );
1973 FETCH csr_defined_bal1 INTO l_def_bal_id;
1974 IF csr_defined_bal1%FOUND THEN
1975 CLOSE csr_defined_bal1;
1976 l_contr_amt := pay_balance_pkg.get_value(p_defined_balance_id => l_def_bal_id
1977 ,p_assignment_action_id => v_csr_asg_act.assignment_action_id
1978 );
1979 ELSE
1980 CLOSE csr_defined_bal1;
1981 END IF;
1982
1983 OPEN csr_defined_bal1(c_balance_type_id => temp_rec.er_contribution_bal_type_id
1984 ,c_dimension_name => 'Assignment Run'
1985 ,c_bg_id => p_bg_id
1986 );
1987 FETCH csr_defined_bal1 INTO l_def_bal_id;
1988 IF csr_defined_bal1%FOUND THEN
1989 CLOSE csr_defined_bal1;
1990 l_contr_amt := l_contr_amt +
1991 pay_balance_pkg.get_value(p_defined_balance_id => l_def_bal_id
1992 ,p_assignment_action_id => v_csr_asg_act.assignment_action_id
1993 );
1994 ELSE
1995 CLOSE csr_defined_bal1;
1996 END IF;
1997
1998 IF l_contr_amt <> 0 THEN
1999 p_contr_amt_values(l_contr_count).pension_type := temp_rec.pension_type;
2000 p_contr_amt_values(l_contr_count).pension_type_variant := temp_rec.pension_type_variant;
2001 p_contr_amt_values(l_contr_count).contr_amt := l_contr_amt;
2002 p_contr_amt_values(l_contr_count).date_earned := l_date_earned;
2003 l_contr_count := l_contr_count + 1;
2004 END IF;
2005 ELSE
2006 IF temp_rec.contr_amt <> 0 THEN
2007
2008 OPEN csr_prev_contr_amt(p_assignment_id,l_date_earned,temp_rec.pension_type,temp_rec.pension_type_variant);
2009 FETCH csr_prev_contr_amt INTO l_prev_contr_amt;
2010 IF csr_prev_contr_amt%FOUND THEN
2011 p_contr_amt_values(l_contr_count).contr_amt := p_contr_amt_values(l_contr_count).contr_amt
2012 + nvl(l_prev_contr_amt,0);
2013 END IF;
2014 CLOSE csr_prev_contr_amt;
2015
2016 p_contr_amt_values(l_contr_count).pension_type := temp_rec.pension_type;
2017 p_contr_amt_values(l_contr_count).pension_type_variant := temp_rec.pension_type_variant;
2018 p_contr_amt_values(l_contr_count).contr_amt := temp_rec.contr_amt;
2019 p_contr_amt_values(l_contr_count).date_earned := l_date_earned;
2020 l_contr_count := l_contr_count + 1;
2021 END IF;
2022 END IF;
2023
2024 END LOOP;
2025 END LOOP;
2026
2027 ELSE
2028 CLOSE csr_asg_act1;
2029 END IF;
2030
2031 -- ============================================================================
2032 -- BEGIN Populating Contribution Amount for change in hire dates
2033 -- ============================================================================
2034 -- Populate the PL SQL table with values from the
2035 -- previous runs if there has been a change in hire date
2036 --
2037
2038 IF l_old_start IS NOT NULL AND l_new_start IS NOT NULL THEN
2039 --
2040 -- Hire Date is changed to the past
2041 --
2042 IF trunc(l_new_start) < trunc(l_old_start) THEN
2043 --
2044 -- Derive the beginning date
2045 --
2046 OPEN csr_get_prev_hire_info(p_assignment_id,l_new_start);
2047 FETCH csr_get_prev_hire_info INTO l_chg_rep;
2048 IF csr_get_prev_hire_info%NOTFOUND THEN
2049
2050 l_new_start := greatest(l_new_start,g_valid_start_date);
2051
2052 OPEN csr_get_period(p_payroll_id,l_new_start);
2053 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
2054 CLOSE csr_get_period;
2055 --
2056 -- If the beginning date is less then the current extract start
2057 -- Loop through the assignment actions to derive contrib amounts
2058 -- of that period
2059 --
2060 WHILE trunc(l_beg_new_st) < trunc(p_start_date)
2061 LOOP
2062
2063 --
2064 -- First fetch the maximum assignment action id
2065 --
2066 OPEN csr_asg_act1 (
2067 c_assignment_id => p_assignment_id
2068 ,c_payroll_id => p_payroll_id
2069 ,c_con_set_id => NULL
2070 ,c_start_date => l_beg_new_st
2071 ,c_end_date => l_end_new_st );
2072 FETCH csr_asg_act1 INTO l_asg_act_id;
2073 IF csr_asg_act1%FOUND THEN
2074 --
2075 -- Loop through the retro and normal deduction amount rows
2076 --
2077 FOR temp_rec IN csr_contr_amount(c_bg_id => p_bg_id
2078 ,c_effective_date => l_end_new_st
2079 ,c_asg_id => p_assignment_id)
2080 LOOP
2081
2082 --if the amount is -999999 then fetch the balance value
2083 IF temp_rec.contr_amt = -999999 THEN
2084 l_contr_amt := 0;
2085 OPEN csr_defined_bal1(c_balance_type_id => temp_rec.ee_contribution_bal_type_id
2086 ,c_dimension_name => 'Assignment Run'
2087 ,c_bg_id => p_bg_id);
2088 FETCH csr_defined_bal1 INTO l_def_bal_id;
2089 IF csr_defined_bal1%FOUND THEN
2090 CLOSE csr_defined_bal1;
2091 l_contr_amt := pay_balance_pkg.get_value(p_defined_balance_id => l_def_bal_id
2092 ,p_assignment_action_id => l_asg_act_id);
2093 ELSE
2094 CLOSE csr_defined_bal1;
2095 END IF;
2096
2097 OPEN csr_defined_bal1(c_balance_type_id => temp_rec.er_contribution_bal_type_id
2098 ,c_dimension_name => 'Assignment Run'
2099 ,c_bg_id => p_bg_id
2100 );
2101 FETCH csr_defined_bal1 INTO l_def_bal_id;
2102 IF csr_defined_bal1%FOUND THEN
2103 CLOSE csr_defined_bal1;
2104 l_contr_amt := l_contr_amt +
2105 pay_balance_pkg.get_value(p_defined_balance_id => l_def_bal_id
2106 ,p_assignment_action_id => l_asg_act_id);
2107 ELSE
2108 CLOSE csr_defined_bal1;
2109 END IF;
2110
2111 IF l_contr_amt <> 0 THEN
2112 p_contr_amt_values(l_contr_count).pension_type := temp_rec.pension_type;
2113 p_contr_amt_values(l_contr_count).pension_type_variant := temp_rec.pension_type_variant;
2114 p_contr_amt_values(l_contr_count).contr_amt := l_contr_amt;
2115 p_contr_amt_values(l_contr_count).date_earned := l_beg_new_st;
2116 l_contr_count := l_contr_count + 1;
2117 END IF;
2118 END IF; -- amount is -9999
2119 END LOOP;
2120
2121 END IF; -- Ass acts are found
2122 CLOSE csr_asg_act1;
2123
2124 OPEN csr_get_period(p_payroll_id,(l_end_new_st + 1));
2125 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
2126 CLOSE csr_get_period;
2127
2128 END LOOP; -- Loop through the period
2129 END IF;
2130 CLOSE csr_get_prev_hire_info;
2131 ELSIF trunc(l_new_start) > trunc(l_old_start) AND l_new_start >= g_valid_start_date THEN
2132 --
2133 -- Derive the beginning date
2134 --
2135 l_old_start := greatest(l_old_start,g_valid_start_date);
2136
2137 OPEN csr_get_period(p_payroll_id,l_old_start);
2138 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
2139 CLOSE csr_get_period;
2140
2141 OPEN csr_get_period(p_payroll_id,l_new_start);
2142 FETCH csr_get_period INTO l_beg_new_st2,l_loop_end_date;
2143 CLOSE csr_get_period;
2144
2145 l_loop_end_date := least(p_start_date -1,l_loop_end_date);
2146 --
2147 -- Loop through the dates to derive data to be reported to ABP
2148 -- this might include ony the differences of that period or the entire amount
2149 -- for the month
2150 --
2151 WHILE trunc(l_beg_new_st) < l_loop_end_date
2152 LOOP
2153
2154 fnd_file.put_line(fnd_file.log,'l_beg_new_st is '||l_beg_new_st);
2155 fnd_file.put_line(fnd_file.log,'l_end_new_st is '||l_end_new_st);
2156 --
2157 -- First fetch the maximum assignment action id
2158 --
2159 OPEN csr_asg_act1 (
2160 c_assignment_id => p_assignment_id
2161 ,c_payroll_id => p_payroll_id
2162 ,c_con_set_id => NULL
2163 ,c_start_date => l_beg_new_st
2164 ,c_end_date => l_end_new_st );
2165 FETCH csr_asg_act1 INTO l_asg_act_id;
2166 CLOSE csr_asg_act1;
2167
2168 IF l_asg_act_id IS NOT NULL THEN
2169 --
2170 -- Loop through the normal deduction amount rows
2171 --
2172 FOR temp_rec IN csr_contr_amount(c_bg_id => p_bg_id
2173 ,c_effective_date => l_end_new_st
2174 ,c_asg_id => p_assignment_id)
2175 LOOP
2176
2177 --if the amount is -999999 then fetch the balance value
2178 IF temp_rec.contr_amt = -999999 THEN
2179 l_contr_amt := 0;
2180 OPEN csr_defined_bal1(c_balance_type_id => temp_rec.ee_contribution_bal_type_id
2181 ,c_dimension_name => 'Assignment Run'
2182 ,c_bg_id => p_bg_id);
2183 FETCH csr_defined_bal1 INTO l_def_bal_id;
2184 IF csr_defined_bal1%FOUND THEN
2185 CLOSE csr_defined_bal1;
2186 l_contr_amt := pay_balance_pkg.get_value(p_defined_balance_id => l_def_bal_id
2187 ,p_assignment_action_id => l_asg_act_id);
2188 ELSE
2189 CLOSE csr_defined_bal1;
2190 END IF;
2191
2192 OPEN csr_defined_bal1(c_balance_type_id => temp_rec.er_contribution_bal_type_id
2193 ,c_dimension_name => 'Assignment Run'
2194 ,c_bg_id => p_bg_id
2195 );
2196 FETCH csr_defined_bal1 INTO l_def_bal_id;
2197 IF csr_defined_bal1%FOUND THEN
2198 CLOSE csr_defined_bal1;
2199 l_contr_amt := l_contr_amt +
2200 pay_balance_pkg.get_value(p_defined_balance_id => l_def_bal_id
2201 ,p_assignment_action_id => l_asg_act_id);
2202 ELSE
2203 CLOSE csr_defined_bal1;
2204 END IF;
2205
2206 IF l_contr_amt <> 0 THEN
2207 p_contr_amt_values(l_contr_count).pension_type := temp_rec.pension_type;
2208 p_contr_amt_values(l_contr_count).pension_type_variant := temp_rec.pension_type_variant;
2209 p_contr_amt_values(l_contr_count).contr_amt := l_contr_amt;
2210 p_contr_amt_values(l_contr_count).date_earned := l_beg_new_st;
2211
2212 OPEN csr_prev_contr_amt(p_assignment_id,l_beg_new_st,temp_rec.pension_type,temp_rec.pension_type_variant);
2213 FETCH csr_prev_contr_amt INTO l_prev_contr_amt;
2214 IF csr_prev_contr_amt%FOUND THEN
2215 p_contr_amt_values(l_contr_count).contr_amt := p_contr_amt_values(l_contr_count).contr_amt
2216 - l_prev_contr_amt;
2217 END IF;
2218 CLOSE csr_prev_contr_amt;
2219 l_contr_count := l_contr_count + 1;
2220
2221 END IF;
2222 END IF;
2223 END LOOP;
2224
2225 ELSIF l_asg_act_id IS NULL THEN
2226
2227 FOR temp_rec IN csr_contr_amount(c_bg_id => p_bg_id
2228 ,c_effective_date => l_end_new_st
2229 ,c_asg_id => p_assignment_id)
2230 LOOP
2231
2232 --if the amount is -999999 then fetch the balance value
2233 IF temp_rec.contr_amt = -999999 THEN
2234
2235 OPEN csr_prev_contr_amt (p_assignment_id,l_beg_new_st,temp_rec.pension_type,temp_rec.pension_type_variant);
2236 FETCH csr_prev_contr_amt INTO l_prev_contr_amt;
2237 IF csr_prev_contr_amt%FOUND THEN
2238 p_contr_amt_values(l_contr_count).pension_type := temp_rec.pension_type;
2239 p_contr_amt_values(l_contr_count).pension_type_variant := temp_rec.pension_type_variant;
2240 p_contr_amt_values(l_contr_count).contr_amt := -1 * l_prev_contr_amt;
2241 p_contr_amt_values(l_contr_count).date_earned := l_beg_new_st;
2242 l_contr_count := l_contr_count + 1;
2243
2244 END IF;
2245 CLOSE csr_prev_contr_amt;
2246 END IF;
2247 END LOOP;
2248
2249 END IF;
2250
2251 OPEN csr_get_period(p_payroll_id,(l_end_new_st + 1));
2252 FETCH csr_get_period INTO l_beg_new_st,l_end_new_st;
2253 CLOSE csr_get_period;
2254
2255 END LOOP; -- Loop through the months
2256
2257 END IF; -- new start date < old start dt
2258 END IF; -- dates are not null
2259 -- ============================================================================
2260 -- END Populating Contribution Amount for change in hire dates
2261 -- ============================================================================
2262
2263 hr_utility.trace('X====pension_contr======================================X');
2264
2265 EXCEPTION
2266 WHEN OTHERS THEN
2267 hr_utility.trace('X====Exception in pension_contr========X'||SQLERRM);
2268 log_tech_exception(p_action_context_id => g_payroll_action_id,
2269 p_err_msg => 'pension_contr : '|| substr (sqlerrm, 1, 180)); --14793405
2270 END pension_contr;
2271
2272 -- =============================================================================
2273 -- RANGE_CODE - This procedure returns an sql string to select a range
2274 -- of assignments eligible for reporting
2275 -- =============================================================================
2276 PROCEDURE range_code (pactid IN NUMBER
2277 ,sqlstr OUT NOCOPY VARCHAR2) is
2278 BEGIN
2279 hr_utility.trace('+====range_code============================================+');
2280
2281 g_payroll_action_id := pactid; --14793405
2282
2283 sqlstr := 'SELECT DISTINCT person_id
2284 FROM per_all_people_f pap
2285 ,pay_payroll_actions ppa
2286 WHERE ppa.payroll_action_id = :payroll_action_id
2287 AND ppa.business_group_id = pap.business_group_id
2288 ORDER BY pap.person_id';
2289
2290 hr_utility.trace('X====range_code============================================X');
2291
2292 EXCEPTION
2293 WHEN OTHERS THEN
2294 -- Return cursor that selects no rows
2295 hr_utility.trace('Exception in range_code SQL-ERRM : '||SQLERRM);
2296 log_tech_exception(p_action_context_id => g_payroll_action_id,
2297 p_err_msg => 'range_code : '|| substr (sqlerrm, 1, 180)); --14793405
2298 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
2299 END range_code;
2300 --=========================================================================
2301
2302 PROCEDURE initialization_code(p_action_context_id IN NUMBER) IS
2303
2304 CURSOR csr_usr_tab_col IS
2305 SELECT put.user_table_id
2306 ,puc.user_column_id
2307 FROM pay_user_tables put
2308 ,pay_user_columns puc
2309 WHERE put.user_table_id = puc.user_table_id
2310 AND put.legislation_code = puc.legislation_code
2311 AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
2312 AND put.legislation_code = 'NL';
2313 --=========================================================================
2314 CURSOR csr_src_dest_str(p_user_column_id IN NUMBER
2315 , p_user_table_id IN NUMBER
2316 , p_business_group_id IN NUMBER
2317 , p_start_date IN DATE) IS
2318 SELECT DISTINCT
2319 upper (purf.row_low_range_or_name) source
2320 ,upper (pucif.value) target
2321 FROM pay_user_column_instances_f pucif
2322 ,pay_user_rows_f purf
2323 WHERE pucif.user_column_id = p_user_column_id
2324 AND purf.user_table_id = p_user_table_id
2325 AND pucif.user_row_id = purf.user_row_id
2326 AND pucif.business_group_id = purf.business_group_id
2327 AND pucif.business_group_id = p_business_group_id
2328 AND p_start_date BETWEEN pucif.effective_start_date
2329 AND pucif.effective_end_date
2330 AND p_start_date BETWEEN purf.effective_start_date
2331 AND purf.effective_end_date;
2332 --=========================================================================
2333 CURSOR csr_bg_stdate(p_payroll_action_id IN NUMBER) IS
2334 SELECT business_group_id bg_id
2335 ,start_date start_date
2336 FROM pay_payroll_actions
2337 WHERE payroll_action_id = p_payroll_action_id;
2338 --=========================================================================
2339 l_user_table_id NUMBER;
2340 l_user_column_id NUMBER;
2341 l_bg_id NUMBER;
2342 l_start_date DATE;
2343
2344 BEGIN
2345 hr_utility.trace('+====initialization_code===================================+');
2346
2347 hr_utility.trace('X====initialization_code===================================X');
2348 EXCEPTION
2349 WHEN OTHERS THEN
2350 hr_utility.trace('Exception in initialization_code SQL-ERRM : '||SQLERRM);
2351 log_tech_exception(p_action_context_id => g_payroll_action_id,
2352 p_err_msg => 'initialization_code : '|| substr (sqlerrm, 1, 180)); --14793405
2353 END initialization_code;
2354
2355 -- =============================================================================
2356 -- ASSIGNMENT_ACTION_CODE - This procedure further filters which assignments are
2357 -- eligible for reporting
2358 -- =============================================================================
2359 PROCEDURE assignment_action_code (p_payroll_action_id IN NUMBER
2360 ,p_start_person_id IN NUMBER
2361 ,p_end_person_id IN NUMBER
2362 ,p_chunk IN NUMBER) IS
2363
2364 CURSOR csr_get_asg_org (c_org_id IN NUMBER
2365 ,c_org_struct_id IN NUMBER
2366 ,c_start_person_id IN NUMBER
2367 ,c_end_person_id IN NUMBER
2368 ,c_payroll_action_id IN NUMBER
2369 ,c_start_date IN DATE
2370 ,c_end_date IN DATE) IS
2371 SELECT DISTINCT
2372 paa.assignment_id assignment_id
2373 ,paa.person_id
2374 FROM pay_payroll_actions ppa
2375 ,per_org_structure_versions posv
2376 ,per_all_assignments_f paa
2377 ,per_all_people_f pap
2378 WHERE posv.organization_structure_id = c_org_struct_id
2379 AND posv.date_from <= c_end_date
2380 AND nvl (posv.date_to
2381 ,hr_general.end_of_time) >= c_start_date
2382 AND (
2383 paa.organization_id IN
2384 (
2385 (
2386 SELECT pose.organization_id_child
2387 FROM per_org_structure_elements pose
2388 WHERE pose.org_structure_version_id = posv.org_structure_version_id
2389 CONNECT BY NOCYCLE PRIOR pose.organization_id_child = pose.organization_id_parent
2390 START WITH pose.organization_id_parent = c_org_id
2391 )
2392 UNION
2393 (
2394 SELECT c_org_id
2395 FROM dual
2396 )
2397 )
2398 OR nvl (paa.establishment_id
2399 ,- 1) = c_org_id
2400 )
2401 AND nvl(pap.current_applicant_flag,'N') <> 'Y'
2402 AND pap.person_id = paa.person_id
2403 AND paa.person_id BETWEEN c_start_person_id
2404 AND c_end_person_id
2405 AND paa.effective_start_date <= c_end_date
2406 AND paa.business_group_id = pap.business_group_id
2407 AND pap.business_group_id = ppa.business_group_id
2408 AND ppa.payroll_action_id = c_payroll_action_id;
2409
2410 --=========================================================================
2411 --hired and terminated on the same date and then again re-hired
2412 CURSOR csr_rehire_chk(c_per_id IN NUMBER
2413 ,c_end_date IN DATE)IS
2414 SELECT 1
2415 FROM per_periods_of_service ppos1
2416 WHERE ppos1.person_id = c_per_id
2417 AND trunc (ppos1.actual_termination_date) = trunc (ppos1.date_start)
2418 AND trunc (ppos1.date_start) <= c_end_date
2419 AND NOT EXISTS
2420 (
2421 SELECT 1
2422 FROM per_periods_of_service ppos2
2423 WHERE ppos2.person_id = c_per_id
2424 AND ppos1.period_of_service_id <> ppos2.period_of_service_id
2425 AND trunc (ppos2.date_start) > trunc (ppos1.date_start)
2426 AND c_end_date >= trunc (ppos2.date_start)
2427 AND ppos2.actual_termination_date IS NULL
2428 AND ppos2.final_process_date IS NULL
2429 AND ppos2.last_standard_process_date IS NULL
2430 );
2431 --=========================================================================
2432 --terminated with final_process_date before effective_date
2433 CURSOR csr_term_chk(c_asg_id IN NUMBER
2434 ,c_bg_id IN NUMBER
2435 ,c_start_date IN DATE) IS
2436 SELECT 1
2437 FROM per_periods_of_service pps
2438 ,per_all_assignments_f asg
2439 WHERE asg.assignment_id = c_asg_id
2440 --AND c_effective_date BETWEEN asg.effective_start_date
2441 -- AND asg.effective_end_date
2442 AND asg.period_of_service_id = pps.period_of_service_id
2443 AND pps.actual_termination_date IS NOT NULL
2444 AND pps.final_process_date IS NOT NULL
2445 AND pps.final_process_date < c_start_date
2446 UNION
2447 --secondary asgs
2448 SELECT 1
2449 FROM per_all_assignments_f asg
2450 WHERE assignment_id = c_asg_id
2451 AND business_group_id = c_bg_id
2452 AND effective_start_date < c_start_date
2453 AND asg.primary_flag = 'N'
2454 AND assignment_status_type_id IN
2455 (
2456 SELECT assignment_status_type_id
2457 FROM per_assignment_status_types
2458 WHERE per_system_status = 'TERM_ASSIGN'
2459 AND active_flag = 'Y'
2460 )
2461 GROUP BY period_of_service_id;
2462 --=========================================================================
2463 CURSOR csr_is_payroll_run(c_asg_id IN NUMBER
2464 ,c_bg_id IN NUMBER
2465 ,c_payroll_id IN NUMBER
2466 ,c_cons_id IN NUMBER
2467 ,c_start_date IN DATE
2468 ,c_end_date IN DATE)
2469 IS
2470 SELECT 1
2471 FROM pay_assignment_actions paa
2472 ,pay_payroll_actions ppa
2473 WHERE paa.assignment_id = c_asg_id
2474 AND ppa.BUSINESS_GROUP_ID = c_bg_id
2475 AND ppa.action_status = 'C'
2476 AND ppa.action_type IN ('Q','R')
2477 AND paa.action_status = 'C'
2478 AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
2479 AND ppa.consolidation_set_id = Nvl(c_cons_id,ppa.consolidation_set_id)
2480 AND paa.payroll_action_id = ppa.payroll_action_id
2481 AND ppa.effective_date BETWEEN c_start_date and c_end_date;
2482 --=========================================================================
2483 CURSOR csr_ele_entries(c_asg_id IN NUMBER
2484 ,c_start_date IN DATE
2485 ,c_end_date IN DATE)
2486 IS
2487 SELECT 1
2488 FROM pay_element_entries_f peef
2489 WHERE peef.assignment_id = c_asg_id
2490 AND (
2491 c_start_date BETWEEN peef.effective_start_date
2492 AND peef.effective_end_date
2493 OR c_end_date BETWEEN peef.effective_start_date
2494 AND peef.effective_end_date
2495 )
2496 AND EXISTS
2497 (
2498 SELECT 1
2499 FROM pay_element_types_f petf
2500 WHERE peef.element_type_id = petf.element_type_id
2501 AND upper (element_name) LIKE '%RETRO%ABP%'
2502 AND (
2503 c_start_date BETWEEN petf.effective_start_date
2504 AND petf.effective_end_date
2505 OR c_end_date BETWEEN petf.effective_start_date
2506 AND petf.effective_end_date
2507 )
2508 );
2509 --=========================================================================
2510 CURSOR csr_find_parent_id(c_org_id IN NUMBER
2511 ,c_version_id IN NUMBER
2512 ,c_bg_id IN NUMBER) IS
2513 SELECT organization_id_parent
2514 FROM per_org_structure_elements
2515 WHERE organization_id_child = c_org_id
2516 AND org_structure_version_id = c_version_id
2517 AND business_group_id = c_bg_id;
2518 --=========================================================================
2519 -- Cursor to check if ABP employer details are present in current Org
2520 CURSOR csr_chk_er_details(c_org_id IN Number) IS
2521 SELECT 1
2522 FROM hr_organization_information
2523 WHERE org_information_context = 'PQP_ABP_PROVIDER'
2524 AND organization_id = c_org_id;
2525 --=========================================================================
2526 -- Cursor to fetch ABP employer payroll. Within one employer it is not allowed
2527 -- to report more than one payroll frequency
2528 CURSOR csr_get_er_pyrls(c_tax_unit_id IN NUMBER
2529 ,c_bg_id IN NUMBER
2530 ,c_start_date IN DATE
2531 ,c_abp_org_id IN NUMBER)
2532 IS
2533 SELECT ppf.payroll_id
2534 FROM pay_payrolls_f ppf
2535 ,hr_organization_information hoi
2536 WHERE hoi.org_information_context = 'PQP_ABP_PROVIDER'
2537 AND hoi.organization_id = c_abp_org_id
2538 AND ppf.prl_information1 = to_char (c_tax_unit_id)
2539 AND ppf.business_group_id = c_bg_id
2540 AND ppf.period_type = decode (hoi.org_information5
2541 ,'MONTH'
2542 ,'Calendar Month'
2543 ,'LMONTH'
2544 ,'Lunar Month')
2545 AND c_start_date BETWEEN ppf.effective_start_date
2546 AND ppf.effective_end_date;
2547 --=========================================================================
2548
2549 l_org_struct_id NUMBER :=NULL;
2550 l_person_id NUMBER :=NULL;
2551 l_org_id NUMBER :=NULL;
2552 abp_org_id NUMBER :=NULL;
2553 l_bg_id NUMBER;
2554 l_asg_act_id NUMBER;
2555 l_start_date DATE;
2556 l_end_date DATE;
2557 l_payroll_id NUMBER;
2558 l_report_type VARCHAR2(30);
2559 l_seq_num NUMBER;
2560 l_process CHAR := 'Y';
2561 l_chk NUMBER := 0;
2562 abp_provider_found VARCHAR2(3);
2563 l_loop_again NUMBER;
2564 l_chk_er_details csr_chk_er_details%ROWTYPE;
2565 l_version_id per_org_structure_versions_v.org_structure_version_id%TYPE DEFAULT NULL;
2566
2567 --=========================================================================
2568 PROCEDURE ins_assg_act (p_assignid IN NUMBER
2569 ,p_pactid IN NUMBER
2570 ,p_chunk IN NUMBER
2571 ,p_org_id IN NUMBER)
2572 IS
2573 l_asg_act_id NUMBER;
2574 BEGIN
2575 SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_act_id FROM dual;
2576 hr_nonrun_asact.insact(
2577 lockingactid => l_asg_act_id
2578 ,assignid => p_assignid
2579 ,pactid => p_pactid
2580 ,chunk => p_chunk
2581 ,greid => p_org_id);
2582 EXCEPTION
2583 WHEN others THEN
2584 hr_utility.trace('Exception in ins_assg_act SQL-ERRM : '||SQLERRM);
2585 log_tech_exception(p_action_context_id => g_payroll_action_id,
2586 p_err_msg => 'ins_assg_act : '|| substr (sqlerrm, 1, 180)); --14793405
2587 END ins_assg_act;
2588 --=========================================================================
2589
2590 BEGIN
2591 g_payroll_action_id := p_payroll_action_id;
2592
2593 hr_utility.trace('+====assignment_action_code================================+');
2594 hr_utility.trace('Payroll_action_id/Chunk: '||p_payroll_action_id||' / '||p_chunk );
2595 hr_utility.trace('Start/End Person ID : '||p_start_person_id||' / '||p_end_person_id );
2596
2597 get_all_parameters (
2598 p_payroll_action_id => p_payroll_action_id
2599 ,p_org_struct_id => l_org_struct_id
2600 ,p_org_id => l_org_id
2601 ,p_report_type => l_report_type
2602 ,p_seq_num => l_seq_num
2603 ,p_bg_id => l_bg_id
2604 ,p_start_date => l_start_date
2605 ,p_end_date => l_end_date);
2606
2607 hr_utility.trace('Start Date : ' ||l_start_date);
2608 hr_utility.trace('End Date : ' ||l_end_date);
2609 hr_utility.trace('Valid Start Date : ' ||g_valid_start_date);
2610
2611 abp_org_id := l_org_id;
2612 OPEN csr_chk_er_details(abp_org_id);
2613 FETCH csr_chk_er_details INTO l_chk_er_details;
2614
2615 IF csr_chk_er_details%FOUND THEN
2616 abp_provider_found := 'Yes';
2617 CLOSE csr_chk_er_details;
2618 ELSE
2619 CLOSE csr_chk_er_details;
2620 -- ABP Employer details not found at this org level,traverse up the
2621 -- org hierarchy to find a value at the parent level
2622 hr_utility.trace('....No value found at HR org level,searching up the tree');
2623
2624 l_version_id := pqp_pension_functions.get_version_id(l_bg_id,l_end_date);
2625 hr_utility.trace(' l_version_id '||l_version_id);
2626
2627 IF l_version_id = -99 THEN
2628 abp_provider_found := 'No';
2629 END IF;
2630
2631 -- Loop through the org hierarchy to find the values
2632 -- at this org level or its parents
2633 l_loop_again := 1;
2634 WHILE (l_loop_again = 1)
2635 LOOP
2636 -- Find the parent of this org
2637 OPEN csr_find_parent_id(abp_org_id,l_version_id,l_bg_id);
2638 FETCH csr_find_parent_id INTO abp_org_id;
2639 IF csr_find_parent_id%FOUND THEN
2640 hr_utility.trace('searching at parent : '||abp_org_id);
2641 CLOSE csr_find_parent_id;
2642 OPEN csr_chk_er_details(abp_org_id);
2643 FETCH csr_chk_er_details INTO l_chk_er_details;
2644 IF csr_chk_er_details%FOUND THEN
2645 abp_provider_found := 'Yes';
2646 hr_utility.trace('ABP Employer details found at : '||abp_org_id);
2647 CLOSE csr_chk_er_details;
2648 l_loop_again := 0;
2649 ELSE
2650 CLOSE csr_chk_er_details;
2651 END IF;
2652 ELSE
2653 CLOSE csr_find_parent_id;
2654 abp_provider_found := 'No';
2655 END IF;
2656 END LOOP;
2657 END IF;
2658
2659 IF abp_provider_found = 'Yes' AND l_start_date >= g_valid_start_date AND l_org_id is not NULL THEN
2660 FOR v_csr_get_asg_org IN
2661 csr_get_asg_org(l_org_id, l_org_struct_id
2662 , p_start_person_id, p_end_person_id, p_payroll_action_id
2663 , l_start_date, l_end_date)
2664 LOOP
2665 l_process := 'Y';
2666 hr_utility.trace('Assignment Id : ' ||v_csr_get_asg_org.assignment_id);
2667
2668 OPEN csr_get_payroll_id(v_csr_get_asg_org.assignment_id);
2669 FETCH csr_get_payroll_id INTO l_payroll_id;
2670 CLOSE csr_get_payroll_id;
2671
2672 OPEN csr_term_chk(v_csr_get_asg_org.assignment_id,l_bg_id,l_start_date);
2673 FETCH csr_term_chk INTO l_chk;
2674 IF csr_term_chk%FOUND THEN
2675 hr_utility.trace('Termination Details found : ');
2676 l_process := 'N';
2677 END IF;
2678 CLOSE csr_term_chk;
2679
2680 IF l_process = 'Y' THEN
2681 OPEN csr_rehire_chk(v_csr_get_asg_org.person_id,l_end_date);
2682 FETCH csr_rehire_chk INTO l_chk;
2683 IF csr_rehire_chk%FOUND THEN
2684 hr_utility.trace('Rehire Details found : ');
2685 l_process := 'N';
2686 OPEN csr_is_payroll_run(v_csr_get_asg_org.assignment_id, l_bg_id, l_payroll_id,NULL,l_start_date,l_end_date);
2687 FETCH csr_is_payroll_run INTO l_chk;
2688 IF csr_is_payroll_run%FOUND THEN
2689 OPEN csr_ele_entries(v_csr_get_asg_org.assignment_id,l_start_date,l_end_date);
2690 FETCH csr_ele_entries INTO l_chk;
2691 IF csr_ele_entries%FOUND THEN
2692 hr_utility.trace('Payroll Details found : ');
2693 l_process := 'Y';
2694 END IF;
2695 CLOSE csr_ele_entries;
2696 END IF;
2697 CLOSE csr_is_payroll_run;
2698 ELSE
2699 hr_utility.trace('Rehire Details not found : ');
2700 l_process:= 'Y';
2701 END IF;
2702 CLOSE csr_rehire_chk;
2703 END IF;
2704
2705 IF l_process = 'Y' THEN
2706 FOR v_csr_get_er_pyrls IN csr_get_er_pyrls(l_org_id,l_bg_id,l_start_date,abp_org_id)
2707 LOOP
2708 IF v_csr_get_er_pyrls.payroll_id = l_payroll_id THEN
2709 hr_utility.trace('Assignment id '||v_csr_get_asg_org.assignment_id);
2710 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk,abp_org_id);
2711 EXIT;
2712 END IF;
2713 END LOOP;
2714 END IF;
2715 END LOOP;
2716 END IF;
2717 hr_utility.trace('X====assignment_action_code================================X');
2718 EXCEPTION
2719 WHEN others THEN
2720 hr_utility.trace('Exception in assignment_action_code SQL-ERRM : '||SQLERRM);
2721 log_tech_exception(p_action_context_id => p_payroll_action_id,
2722 p_err_msg => 'assignment_action_code : '|| substr (sqlerrm, 1, 180)); --14793405
2723 END assignment_action_code;
2724
2725 -- =============================================================================
2726 -- ARCHIVE_CODE - Archives information
2727 -- =============================================================================
2728 PROCEDURE archive_code (p_assignment_action_id IN NUMBER
2729 ,p_effective_date IN DATE) IS
2730
2731 CURSOR csr_get_pact_chunk(c_assignment_action_id IN NUMBER ) IS
2732 SELECT ppa.payroll_action_id
2733 ,paa.chunk_number
2734 FROM pay_payroll_actions ppa
2735 ,pay_assignment_actions paa
2736 WHERE paa.payroll_action_id = ppa.payroll_action_id
2737 AND paa.assignment_action_id = c_assignment_action_id;
2738 --=========================================================================
2739 -- Assignment Sequence
2740 CURSOR csr_get_asg_seq (c_assignment_id IN NUMBER
2741 ,c_effective_date IN DATE) IS
2742 SELECT to_char(lpad(pas.assignment_sequence,2,0)) asg_seq
2743 ,pas.assignment_number
2744 FROM per_all_assignments_f pas
2745 WHERE pas.assignment_id = c_assignment_id
2746 AND c_effective_date BETWEEN pas.effective_start_date
2747 AND pas.effective_end_date;
2748 --=========================================================================
2749 -- Assignment Period
2750 CURSOR csr_get_asg_period(c_assignment_id IN NUMBER) IS
2751 SELECT min (pas.effective_start_date) asg_start_date
2752 ,decode (max (pas.effective_end_date)
2753 ,hr_general.end_of_time
2754 ,to_date (NULL)
2755 ,max (pas.effective_end_date)) asg_end_date
2756 FROM per_all_assignments_f pas
2757 WHERE pas.assignment_id = c_assignment_id;
2758 --=========================================================================
2759 -- Assignment Id
2760 CURSOR csr_asg_id (c_asg_action_id IN NUMBER
2761 ,c_payroll_action_id IN NUMBER) IS
2762 SELECT DISTINCT
2763 paa.assignment_id
2764 FROM per_all_assignments_f paaf
2765 ,pay_assignment_actions paa
2766 WHERE paa.payroll_action_id = c_payroll_action_id
2767 AND paa.assignment_action_id = c_asg_action_id
2768 AND paaf.assignment_id = paa.assignment_id;
2769 --=========================================================================
2770 -- Person Id
2771 CURSOR csr_get_person_id(c_assignment_id IN NUMBER) IS
2772 SELECT person_id
2773 FROM per_all_assignments_f
2774 WHERE assignment_id = c_assignment_id;
2775 -- AND ROWNUM < 2;
2776 --=========================================================================
2777 -- Employee Number Override - New Data
2778 CURSOR csr_get_emp_num_override(c_org_id IN NUMBER
2779 ,c_bg_id IN NUMBER) IS
2780 SELECT nvl (org_information4
2781 ,0) override
2782 ,nvl (org_information5
2783 ,1) start_pos
2784 FROM hr_organization_units hou
2785 ,hr_organization_information hoi
2786 WHERE hou.organization_id = c_org_id
2787 AND hou.business_group_id = c_bg_id
2788 AND hoi.organization_id = hou.organization_id
2789 AND org_information_context = 'PQP_NL_ABP_PTP_METHOD';
2790 --=========================================================================
2791 -- Participation Details
2792 /*CURSOR csr_part_details(c_assignment_id IN NUMBER
2793 ,c_effective_date IN DATE) IS
2794 SELECT aei_information4 kop
2795 ,least (fnd_number.canonical_to_number (aei_information5)
2796 ,1) * 100 vop
2797 FROM per_assignment_extra_info
2798 WHERE information_type = 'NL_ABP_PAR_INFO'
2799 AND aei_information_category = 'NL_ABP_PAR_INFO'
2800 AND assignment_id = c_assignment_id
2801 AND c_effective_date BETWEEN fnd_date.canonical_to_date (aei_information1)
2802 AND nvl (fnd_date.canonical_to_date(aei_information2)
2803 ,hr_general.end_of_time);*/
2804
2805 CURSOR csr_part_details(c_assignment_id IN NUMBER
2806 ,c_eff_start_date IN DATE
2807 ,c_eff_end_date IN DATE) IS
2808 SELECT aei_information4 kop
2809 , least (fnd_number.canonical_to_number (aei_information5), 1) * 100 vop
2810 , fnd_date.canonical_to_date (aei_information1) kop_start_date
2811 , nvl (fnd_date.canonical_to_date (aei_information2), c_eff_end_date) kop_end_date
2812 FROM per_assignment_extra_info
2813 WHERE information_type = 'NL_ABP_PAR_INFO'
2814 AND aei_information_category = 'NL_ABP_PAR_INFO'
2815 AND assignment_id = c_assignment_id
2816 AND fnd_date.canonical_to_date (aei_information1) <= c_eff_end_date
2817 AND nvl (fnd_date.canonical_to_date (aei_information2), hr_general.end_of_time) >= c_eff_start_date
2818 ORDER BY fnd_date.canonical_to_date (kop_start_date);
2819 --=========================================================================
2820 --All Participation Details
2821 CURSOR csr_all_part_details(c_ass_act_id IN NUMBER
2822 ,c_assignment_id IN NUMBER) IS
2823 SELECT *
2824 FROM (
2825 SELECT pai.action_information1 start_date
2826 , least (fnd_number.canonical_to_number (paei.aei_information5), 1) * 100 vop
2827 FROM pay_action_information pai
2828 , per_assignment_extra_info paei
2829 WHERE paei.information_type = 'NL_ABP_PAR_INFO'
2830 AND paei.aei_information_category = 'NL_ABP_PAR_INFO'
2831 AND paei.assignment_id = pai.assignment_id
2832 AND pai.action_context_id = c_ass_act_id
2833 AND pai.action_information_category = 'NL_ABP_PAY_PERIOD_INFO'
2834 AND pai.assignment_id = c_assignment_id
2835 -- AND pai.action_information1 = paei.aei_information1
2836 AND pai.action_information3 <> 'WNE'
2837 UNION
2838 SELECT pai.action_information1 start_date
2839 , NULL vop
2840 FROM pay_action_information pai
2841 WHERE pai.action_context_id = c_ass_act_id
2842 AND pai.action_information_category = 'NL_ABP_PAY_PERIOD_INFO'
2843 AND pai.assignment_id = c_assignment_id
2844 AND action_information3 = 'WNE'
2845 )
2846 ORDER BY fnd_date.canonical_to_date (start_date);
2847 --=========================================================================
2848 -- Employee Data
2849 CURSOR csr_get_person_info(c_person_id IN NUMBER
2850 ,c_bg_id IN NUMBER
2851 ,c_effective_date IN DATE) IS
2852 SELECT --lpad(pap.employee_number, 35, 0) employee_number
2853 translate (pap.employee_number
2854 ,'x'
2855 || translate (pap.employee_number
2856 ,'x1234567890'
2857 ,'x')
2858 ,'0'
2859 || trim (rpad (' '
2860 ,nvl (length (translate (pap.employee_number
2861 ,'x1234567890'
2862 ,'x'))
2863 ,0)
2864 ,'0'))) employee_number
2865 ,pap.national_identifier sofi_number
2866 ,substr(pap.last_name,1,200) last_name
2867 ,replace(replace(pap.per_information1,'.',''),' ','') init
2868 ,substr(pap.pre_name_adjunct, 1, 10) prefix
2869 ,pap.date_of_birth date_of_birth
2870 ,nationality
2871 ,decode (pap.sex
2872 ,'M'
2873 ,'1'
2874 ,'F'
2875 ,'2'
2876 ,'N'
2877 ,'9'
2878 ,'0') gender
2879 ,pap.date_of_death date_of_death
2880 FROM per_all_people_f pap
2881 WHERE pap.person_id = c_person_id
2882 AND business_group_id = c_bg_id
2883 AND c_effective_date BETWEEN pap.effective_start_date
2884 AND pap.effective_end_date;
2885 --=========================================================================
2886 -- Nationality Code
2887 CURSOR csr_get_shared_types(c_code VARCHAR2
2888 ,c_bg_id NUMBER
2889 ,c_lookup VARCHAR2) IS
2890 SELECT system_type_cd
2891 FROM per_shared_types
2892 WHERE lookup_type = c_lookup --'NL_NATIONALITY'
2893 AND information1 = c_code
2894 AND (business_group_id = c_bg_id
2895 OR business_group_id is NULL)
2896 ORDER BY 1;
2897 --=========================================================================
2898 -- Foreign Country
2899 CURSOR csr_get_foreign_coun(c_person_id IN NUMBER
2900 ,c_effective_date IN DATE) IS
2901 SELECT decode (country
2902 ,'NL'
2903 ,'Y'
2904 ,'N')
2905 FROM per_addresses_v
2906 WHERE person_id = c_person_id
2907 AND c_effective_date BETWEEN date_from
2908 AND nvl (date_to
2909 ,hr_api.g_eot)
2910 AND style IN ('NL','NL_GLB')
2911 AND primary_flag = 'Y';
2912 --=========================================================================
2913 -- Marital Status details
2914 CURSOR csr_get_marital_status(c_person_id IN NUMBER
2915 ,c_bg_id IN NUMBER
2916 ,c_effective_date IN DATE) IS
2917 SELECT DECODE(marital_status,'S',1,
2918 'M',2,
2919 'D',3,
2920 'W',4,
2921 'DP',0,
2922 'L',3,
2923 'BE_LIV_TOG',1,
2924 'REG_PART',1,
2925 'BE_WID_PENS',4,
2926 NULL) ms_code
2927 FROM per_all_people_f pap
2928 WHERE pap.person_id = c_person_id
2929 AND business_group_id = c_bg_id
2930 AND c_effective_date BETWEEN pap.effective_start_date
2931 AND pap.effective_end_date;
2932 --=========================================================================
2933 -- Marital Status Start Date
2934 CURSOR csr_get_marital_start(c_person_id IN NUMBER
2935 ,c_bg_id IN NUMBER
2936 ,c_start_date IN DATE
2937 ,c_end_date IN DATE) IS
2938 SELECT date_start
2939 FROM per_contact_relationships
2940 WHERE person_id = c_person_id
2941 AND business_group_id = c_bg_id
2942 AND contact_type = 'S'
2943 AND c_end_date BETWEEN nvl (date_start
2944 ,c_start_date)
2945 AND nvl (date_end
2946 ,c_end_date);
2947 --=========================================================================
2948 -- Partner Details
2949 CURSOR csr_get_partner_details(c_person_id IN NUMBER
2950 ,c_bg_id IN NUMBER
2951 ,c_start_date IN DATE
2952 ,c_end_date IN DATE) IS
2953 SELECT substr(last_name, 1, 200) last_name
2954 ,substr(pre_name_adjunct, 1, 10) prefix
2955 ,substr(replace(replace(per_information1,'.',''),' ',''), 1, 6) init
2956 FROM per_all_people_f
2957 WHERE person_id IN
2958 (
2959 SELECT contact_person_id
2960 FROM per_contact_relationships
2961 WHERE person_id = c_person_id
2962 AND business_group_id = c_bg_id
2963 AND contact_type IN ('S','D')
2964 AND c_end_date BETWEEN nvl (date_start
2965 ,c_start_date)
2966 AND nvl (date_end
2967 ,c_end_date)
2968 )
2969 AND business_group_id = c_bg_id
2970 AND c_end_date BETWEEN effective_start_date
2971 AND effective_end_date;
2972 --=========================================================================
2973 -- Person Address
2974 CURSOR csr_get_person_addr(c_person_id IN NUMBER
2975 ,c_effective_date IN DATE) IS
2976 SELECT pad.style style
2977 ,pad.region_1 region --NL street, NL_GLB region
2978 ,substr(pad.address_line1, 1, 24) glb_street --NL_GLB street
2979 ,trim(substr(NVL(pad.add_information13, 0), 1, 5)) nl_hno --NL House Number --15859347
2980 ,trim(substr(NVL(pad.address_line2, 0), 1, 9)) glb_hno --NL_GLB House Number --15859347
2981 ,substr(pad.add_information14, 1, 4) nl_hnoa --NL House Number Addition
2982 ,trim(substr(pad.address_line1||' '||pad.region_3,1,35)) nl_location --NL Location
2983 ,substr(pad.address_line3, 1, 35) glb_location --NL_GLB Location
2984 ,upper(pad.postal_code) postal --Postal Code
2985 ,pad.town_or_city city --City
2986 ,pad.add_information16 iso --NL_GLB ISO Country code
2987 FROM per_addresses_v pad
2988 WHERE pad.person_id = c_person_id
2989 AND pad.primary_flag = 'Y'
2990 AND c_effective_date BETWEEN pad.date_from
2991 AND nvl (pad.date_to
2992 ,hr_general.end_of_time)
2993 ORDER BY 1 DESC;
2994 --=========================================================================
2995 -- Individual Exemption
2996 CURSOR csr_prin_obj(c_assignment_id IN NUMBER
2997 ,c_effective_date IN DATE) IS
2998 SELECT decode (aei_information5
2999 ,'Y'
3000 ,'G'
3001 ,NULL) obj_cd
3002 FROM per_assignment_extra_info
3003 WHERE assignment_id = c_assignment_id
3004 AND information_type = 'NL_USZO_INFO'
3005 AND trunc (c_effective_date) BETWEEN fnd_date.canonical_to_date (aei_information1)
3006 AND nvl (fnd_date.canonical_to_date (aei_information2)
3007 ,hr_general.end_of_time)
3008 AND rownum = 1; -- Added in case of multiple valid rows
3009 --=========================================================================
3010 -- Income Code
3011 CURSOR csr_income_code(c_assignment_id IN NUMBER
3012 ,c_effective_date IN DATE) IS
3013 SELECT hscf.segment8
3014 FROM hr_soft_coding_keyflex hscf
3015 ,per_all_assignments_f paaf
3016 WHERE paaf.assignment_id = c_assignment_id
3017 AND c_effective_date BETWEEN paaf.effective_start_date
3018 AND paaf.effective_end_date
3019 AND paaf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id;
3020 --=========================================================================
3021 -- Termination Date
3022 CURSOR csr_get_asg_term_date (c_bg_id IN NUMBER
3023 ,c_end_date IN DATE
3024 ,c_assignment_id IN NUMBER) IS
3025 SELECT min (effective_start_date) - 1 term_date
3026 ,period_of_service_id
3027 FROM per_all_assignments_f asg
3028 WHERE assignment_id = c_assignment_id
3029 AND business_group_id = c_bg_id
3030 AND effective_start_date <= c_end_date
3031 AND assignment_status_type_id IN
3032 (
3033 SELECT assignment_status_type_id
3034 FROM per_assignment_status_types
3035 WHERE per_system_status = 'TERM_ASSIGN'
3036 AND active_flag = 'Y'
3037 )
3038 GROUP BY period_of_service_id
3039 UNION
3040 -- Get the dates for any ended assignments. Note that this is for sec
3041 -- assignments only.
3042 SELECT max (effective_end_date)
3043 ,period_of_service_id
3044 FROM per_all_assignments_f asg
3045 WHERE assignment_id = c_assignment_id
3046 AND business_group_id = c_bg_id
3047 AND asg.primary_flag = 'N'
3048 AND effective_end_date <= c_end_date
3049 AND NOT EXISTS
3050 (
3051 SELECT 1
3052 FROM per_all_assignments_f asg1
3053 WHERE asg1.assignment_id = c_assignment_id
3054 AND asg1.effective_start_date = asg.effective_end_date + 1
3055 AND asg.assignment_id = asg1.assignment_id
3056 )
3057 AND NOT EXISTS
3058 (
3059 SELECT 1
3060 FROM per_all_assignments_f asg1
3061 WHERE asg1.assignment_id = c_assignment_id
3062 AND asg1.effective_start_date > asg.effective_start_date
3063 AND asg.assignment_id = asg1.assignment_id
3064 AND asg1.assignment_status_type_id IN
3065 (
3066 SELECT assignment_status_type_id
3067 FROM per_assignment_status_types
3068 WHERE per_system_status = 'TERM_ASSIGN'
3069 AND active_flag = 'Y'
3070 )
3071 )
3072 GROUP BY period_of_service_id;
3073
3074 --15859347
3075 CURSOR c_term_date(p_asg_id number, p_person_id number, p_bg_id number)
3076 IS
3077 SELECT max(paaf.effective_end_date)
3078 FROM per_all_assignments_f paaf
3079 WHERE
3080 paaf.assignment_id = p_asg_id
3081 AND paaf.person_id = p_person_id
3082 AND paaf.business_group_id = p_bg_id
3083 AND paaf.assignment_status_type_id in
3084 (SELECT ast.assignment_status_type_id
3085 FROM per_assignment_status_types ast
3086 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
3087 );
3088 --15859347
3089 --=========================================================================
3090 --Termination Reason
3091 CURSOR csr_get_end_reason(c_assignment_id IN NUMBER
3092 ,c_effective_date IN DATE) IS
3093 SELECT decode(nvl(leaving_reason ,'R')
3094 ,'D' ,'O'
3095 ,'B' ,'A'
3096 ,'RESIGNATION' ,'W'
3097 ,'EARLY_RETIREMENT' ,'V'
3098 ,'R') term_reas
3099 FROM per_periods_of_service pps
3100 ,per_all_assignments_f paaf
3101 WHERE paaf.period_of_service_id = pps.period_of_service_id
3102 AND paaf.assignment_id = c_assignment_id
3103 AND c_effective_date BETWEEN paaf.effective_start_date
3104 AND paaf.effective_end_date;
3105 --=========================================================================
3106 -- Withdrawal Info
3107 CURSOR csr_get_withdrawal_flag(c_asg_id IN NUMBER) IS
3108 SELECT pei.aei_information24 withdr_flag
3109 FROM per_assignment_extra_info pei
3110 WHERE pei.information_type = 'NL_ABP_PI'
3111 AND pei.aei_information_category = 'NL_ABP_PI'
3112 AND pei.assignment_id = c_asg_id;
3113 --=========================================================================
3114 --Assignment already withdrawn
3115 CURSOR csr_prev_withdrawn(c_asg_id IN NUMBER) IS
3116 SELECT 1
3117 FROM pay_action_information pai
3118 WHERE pai.action_information_category = 'NL_ABP_COMP_WITHDRAWAL_INFO'
3119 AND pai.action_context_type = 'AAP'
3120 AND pai.assignment_id = c_asg_id;
3121 --=========================================================================
3122 --Assignment Termination Details previously reported
3123 --current term date less than previous
3124 CURSOR csr_get_prev_asg_info(c_asg_id IN NUMBER)
3125 IS
3126 SELECT distinct fnd_date.canonical_to_date(pai1.action_information7) start_date
3127 ,fnd_date.canonical_to_date(pai1.action_information4) term_date
3128 FROM pay_action_information pai1
3129 WHERE pai1.assignment_id = c_asg_id
3130 AND pai1.action_information_category = 'NL_ABP_ASG_INFO'
3131 AND pai1.action_context_type = 'AAP'
3132 --AND pai1.action_information4 IS NOT NULL
3133 ORDER BY fnd_date.canonical_to_date(pai1.action_information7) DESC;
3134 --=========================================================================
3135 --Assignment Termination Details previously reported
3136 --current term date greater than previous
3137 --or termination reversed (term date is NULL)
3138 CURSOR csr_get_prev_asg_info2(c_asg_id IN NUMBER)
3139 IS
3140 SELECT distinct fnd_date.canonical_to_date(pai1.action_information7) start_date
3141 ,fnd_date.canonical_to_date(pai1.action_information4) term_date
3142 FROM pay_action_information pai1
3143 WHERE pai1.assignment_id = c_asg_id
3144 AND pai1.action_information_category = 'NL_ABP_ASG_INFO'
3145 AND pai1.action_context_type = 'AAP'
3146 AND pai1.action_information4 IS NOT NULL
3147 ORDER BY fnd_date.canonical_to_date(pai1.action_information7) DESC;
3148 --=========================================================================
3149 --Assignment Reversal Termination Details previously reported
3150 CURSOR csr_get_prev_asg_info3(c_asg_id IN NUMBER)
3151 IS
3152 SELECT DISTINCT fnd_date.canonical_to_date (pai.action_information7) start_date
3153 , fnd_date.canonical_to_date (pai.action_information4) term_date
3154 FROM pay_action_information pai
3155 WHERE pai.assignment_id = c_asg_id
3156 AND pai.action_information_category = 'NL_ABP_ASG_INFO'
3157 AND pai.action_context_type = 'AAP'
3158 AND pai.action_information4 IS NOT NULL
3159 AND NOT EXISTS
3160 (
3161 SELECT 1
3162 FROM pay_action_information pai1
3163 WHERE pai.assignment_id = pai1.assignment_id
3164 AND fnd_date.canonical_to_date (pai1.action_information3) = fnd_date.canonical_to_date (pai.action_information3)
3165 AND pai1.action_information_category = 'NL_ABP_ASG_INFO'
3166 AND pai1.action_context_type = 'AAP'
3167 AND pai1.action_information4 IS NULL
3168 )
3169 ORDER BY fnd_date.canonical_to_date (pai.action_information7) DESC;
3170 --=========================================================================
3171 --Bug 16301893
3172 -- Cursor to get the pay periods
3173 CURSOR csr_pay_periods (c_assignment_id IN Number
3174 ,c_person_id IN Number
3175 ,c_payroll_id IN Number
3176 ,c_start_date IN Date
3177 ,c_end_date IN Date
3178 ) IS
3179 SELECT DISTINCT
3180 last_day (date_earned) date_earned
3181 FROM (
3182 -- default current period
3183 SELECT c_end_date date_earned
3184 FROM dual
3185 UNION
3186 --to find the late hire scenarios paid in current period
3187 SELECT ppa.date_earned
3188 FROM pay_assignment_actions paa
3189 , pay_payroll_actions ppa
3190 WHERE paa.assignment_id = c_assignment_id
3191 AND ppa.action_status = 'C'
3192 AND paa.action_status IN ('C', 'S')
3193 AND ppa.action_type IN ('Q', 'R')
3194 AND paa.payroll_action_id = ppa.payroll_action_id
3195 AND ppa.payroll_id = nvl (c_payroll_id, ppa.payroll_id)
3196 AND ppa.consolidation_set_id = ppa.consolidation_set_id
3197 AND ppa.effective_date
3198 BETWEEN c_start_date
3199 AND last_day (c_end_date)
3200 AND source_action_id IS NOT NULL
3201 UNION
3202 -- to find Kind of Participation changes for previous periods
3203 SELECT distinct last_day (add_months (start_date, level-1)) date_earned
3204 FROM (
3205 SELECT fnd_date.canonical_to_date (evt.prmtr_01) start_date
3206 , fnd_date.canonical_to_date (NVL(evt.prmtr_02,evt.prmtr_09)) end_date
3207 FROM ben_ext_chg_evt_log evt
3208 WHERE evt.prmtr_10 = to_char (c_assignment_id)
3209 AND evt.person_id = c_person_id
3210 AND evt.chg_evt_cd = 'COAPKOP'
3211 AND fnd_date.canonical_to_date (evt.prmtr_09)
3212 BETWEEN c_start_date
3213 AND last_day (c_end_date)
3214 )
3215 CONNECT BY level <= months_between (trunc (end_date, 'MM'), trunc (start_date, 'MM'))+1
3216 UNION
3217 -- to find change in ABP Annual Pension Salary
3218 SELECT DISTINCT last_day (add_months (start_date, level - 1)) date_earned
3219 FROM (
3220 SELECT fnd_date.canonical_to_date (evt.prmtr_02) start_date
3221 , last_day (c_end_date) end_date
3222 FROM ben_ext_chg_evt_log evt
3223 WHERE evt.ext_chg_evt_log_id =
3224 (
3225 SELECT max (ext_chg_evt_log_id)
3226 FROM ben_ext_chg_evt_log evt
3227 WHERE evt.prmtr_01 = to_char (c_assignment_id)
3228 AND evt.person_id = c_person_id
3229 AND evt.chg_evt_cd = 'COAPS'
3230 AND fnd_date.canonical_to_date (evt.prmtr_02) <= last_day (c_end_date)
3231 AND trunc (evt.chg_actl_dt, 'MM') = trunc (c_end_date,'MM')
3232 )
3233 )
3234 CONNECT BY level <= months_between (trunc (end_date, 'MM'), trunc (start_date, 'MM')) + 1
3235 )
3236 ORDER BY last_day (date_earned);
3237 -- ========================================================================
3238 CURSOR csr_hire_dt_chg_wth(c_person_id IN NUMBER
3239 ,c_start_date IN DATE
3240 ,c_end_date IN DATE) IS
3241 SELECT DISTINCT
3242 trunc (add_months (start_date, level - 1), 'MM') date_earned
3243 FROM (
3244 SELECT evt.old_val1 start_date
3245 , evt1.new_val1 end_date
3246 FROM ben_ext_chg_evt_log evt
3247 , ben_ext_chg_evt_log evt1
3248 ,
3249 (
3250 SELECT min (ext_chg_evt_log_id) min_chg_evt_log_id
3251 , max (ext_chg_evt_log_id) max_chg_evt_log_id
3252 FROM ben_ext_chg_evt_log evt
3253 WHERE evt.person_id = c_person_id
3254 AND evt.chg_evt_cd = 'COPOS'
3255 AND fnd_date.canonical_to_date (evt.prmtr_09)
3256 BETWEEN c_start_date
3257 AND last_day (c_end_date)
3258 ) evt2
3259 WHERE evt.ext_chg_evt_log_id = evt2.min_chg_evt_log_id
3260 AND evt1.ext_chg_evt_log_id = evt2.max_chg_evt_log_id
3261 )
3262 WHERE trunc (to_date (end_date), 'MM') <> trunc (to_date (start_date), 'MM')
3263 CONNECT BY level <= months_between (trunc (to_date (end_date), 'MM'), trunc (to_date (start_date), 'MM'));
3264 -- ========================================================================
3265 TYPE tDateTable IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3266 TYPE tPersonTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3267 TYPE tOrgList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3268 pay_period_data tDateTable;
3269 l_temp_dates tDateTable;
3270 rPTPdata tPTPData;
3271 person_list tPersonTable;
3272 l_contr_amt_values tPENSIONData;
3273 l_all_contr_amt_values tPENSIONData;
3274 l_org_struct_id NUMBER :=NULL;
3275 l_person_id NUMBER :=NULL;
3276 l_org_id NUMBER :=NULL;
3277 l_bg_id NUMBER;
3278 l_payroll_id NUMBER;
3279 l_report_type VARCHAR2(30);
3280 l_seq_num NUMBER;
3281 l_child_aa_id NUMBER;
3282 l_start_date DATE;
3283 l_end_date DATE;
3284 l_payroll_action_id NUMBER;
3285 l_assignment_id NUMBER :=NULL;
3286 l_action_info_id NUMBER;
3287 l_ovn NUMBER;
3288 l_chunk_number NUMBER;
3289 l_person_addr csr_get_person_addr%ROWTYPE;
3290 l_asg_seq VARCHAR2(4);
3291 l_person_info csr_get_person_info%ROWTYPE;
3292 l_native VARCHAR2(1) := 'N';
3293 l_nationality VARCHAR2(10);
3294 l_partner_details csr_get_partner_details%ROWTYPE;
3295 l_marital_status NUMBER:=NULL;
3296 l_start_date_ms DATE:=NULL;
3297 l_individual_exem VARCHAR2(1) := NULL;
3298 l_kind_emp VARCHAR2(2) := 'WN';
3299 l_income_code VARCHAR2(2) := NULL;
3300
3301 l_street per_addresses.address_line1%TYPE := NULL;
3302 l_house_number per_addresses.address_line2%TYPE := NULL;
3303 l_house_number_addition per_addresses.add_information14%TYPE := NULL;
3304 l_location per_addresses.address_line3%TYPE := NULL;
3305 l_postal_code per_addresses.postal_code%TYPE := NULL;
3306 l_city per_addresses.town_or_city%TYPE := NULL;
3307 l_region per_addresses.address_line1%TYPE := NULL;
3308 l_iso per_addresses.add_information15%TYPE := NULL;
3309
3310 l_asg_start_date DATE;
3311 l_asg_end_date DATE;
3312 l_asg_term_date DATE:=NULL;
3313 l_term_pos_id NUMBER:=NULL;
3314 l_term_reason per_periods_of_service.leaving_reason%TYPE;
3315 l_pension_salary NUMBER := 0;
3316 l_ptp NUMBER := 0;
3317 l_empty_pay_period VARCHAR2(3):='ja';
3318 l_part_details csr_part_details%ROWTYPE;
3319 l_all_part_details csr_all_part_details%ROWTYPE;
3320 l_emp_num_override csr_get_emp_num_override%ROWTYPE;
3321 x NUMBER:=0;
3322 y NUMBER:=0;
3323 l_prev_withdrawn csr_prev_withdrawn%ROWTYPE;
3324 l_code_withdrawal VARCHAR2(3) := NULL;
3325 l_withdrawal_flag VARCHAR2(1);
3326 l_flag NUMBER := 0;
3327 l_pay_period_det_cnt NUMBER;
3328 l_person_data VARCHAR2(1) := 'Y';
3329 l_person_count NUMBER := 1;
3330 l_prev_asg_info csr_get_prev_asg_info%ROWTYPE;
3331 l_pay_period_count NUMBER:=1;
3332 l_prev_start_date DATE := hr_general.end_of_time;
3333 l_fetch VARCHAR2(1):='Y';
3334 l_temp NUMBER := 0;
3335 l_asg_num per_all_assignments_f.assignment_number%TYPE;
3336 l_kop_report VARCHAR2(1) := 'Y';
3337 l_kop_invalid VARCHAR2(1) := 'Y';
3338
3339 l_wne_start_date DATE := NULL;
3340 l_wne_end_date DATE := NULL;
3341 l_kop_start_date DATE;
3342 l_kop_end_date DATE;
3343 l_temp_date DATE;
3344
3345 lv_start_date DATE;
3346 lv_end_date DATE;
3347 lv_ptp NUMBER := 0;
3348 lv_spl_date DATE;
3349 j NUMBER := 0;
3350
3351 rec_wth_old_start csr_hire_dt_chg_wth%ROWTYPE;
3352 l_wth_prd_start_date DATE;
3353 l_wth_prd_end_date DATE;
3354 --=========================================================================
3355 PROCEDURE pay_period_archive (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3356 , p_person_id IN NUMBER
3357 , p_bg_id IN per_all_assignments_f.business_group_id%TYPE
3358 , p_payroll_id IN NUMBER
3359 , p_start_date IN DATE
3360 , p_end_date IN DATE
3361 , p_term_flag IN VARCHAR)
3362 IS
3363
3364 l_assignment_id per_all_assignments_f.assignment_id%TYPE := p_assignment_id;
3365 l_person_id NUMBER := p_person_id;
3366 l_bg_id per_all_assignments_f.business_group_id%TYPE := p_bg_id;
3367 l_payroll_id NUMBER := p_payroll_id;
3368 l_start_date DATE := p_start_date;
3369 l_end_date DATE := p_end_date;
3370 l_pension_st_date DATE;
3371
3372 BEGIN
3373 l_kop_report := 'Y';
3374 l_wne_start_date := NULL;
3375 l_wne_end_date := NULL;
3376 l_kop_end_date := NULL;
3377 l_kop_start_date := NULL;
3378 lv_start_date := NULL;
3379 lv_end_date := NULL;
3380 lv_ptp := NULL;
3381 l_pension_st_date:= NULL;
3382
3383
3384 hr_utility.trace('+====pay_period_archive==========================================+');
3385
3386 hr_utility.trace('l_assignment_id: '|| l_assignment_id);
3387 hr_utility.trace('l_person_id: '|| l_person_id);
3388 hr_utility.trace('l_bg_id: '|| l_bg_id);
3389 hr_utility.trace('l_payroll_id: '|| l_payroll_id);
3390 hr_utility.trace('l_start_date: '|| l_start_date);
3391 hr_utility.trace('l_end_date: '|| l_end_date);
3392 --Bug 16301893
3393 FOR rec_pay_periods IN csr_pay_periods (
3394 c_assignment_id => l_assignment_id
3395 ,c_person_id => l_person_id
3396 ,c_payroll_id => l_payroll_id
3397 ,c_start_date => l_start_date
3398 ,c_end_date => l_end_date)
3399 LOOP
3400
3401 IF TO_CHAR(rec_pay_periods.date_earned,'MM-YYYY') = TO_CHAR(l_asg_start_date,'MM-YYYY') THEN
3402 l_start_date := l_asg_start_date;
3403 ELSE
3404 l_start_date := TRUNC(rec_pay_periods.date_earned,'MM');
3405 END IF;
3406
3407 IF TO_CHAR(rec_pay_periods.date_earned,'MM-YYYY') = TO_CHAR(l_asg_end_date,'MM-YYYY') THEN
3408 l_end_date := l_asg_end_date;
3409 ELSE
3410 l_end_date := rec_pay_periods.date_earned;
3411 END IF;
3412 l_pension_salary := get_pension_salary(p_assignment_id => l_assignment_id
3413 ,p_bg_id => l_bg_id
3414 ,p_payroll_id => l_payroll_id
3415 ,p_start_date => l_start_date
3416 ,p_end_date => l_end_date
3417 ,p_asg_act => p_assignment_action_id);
3418 hr_utility.trace('Pension Salary : '||l_pension_salary);
3419
3420 l_ptp:=get_curr_ptp(p_assignment_id => l_assignment_id
3421 ,p_bg_id => l_bg_id
3422 ,p_payroll_id => l_payroll_id
3423 ,p_start_date => l_start_date
3424 ,p_end_date => last_day(l_end_date)
3425 ,p_asg_act => p_assignment_action_id);
3426 hr_utility.trace('PTP : '||l_ptp);
3427
3428 get_retro_ptp(p_assignment_id => l_assignment_id
3429 ,p_start_date => l_start_date
3430 ,p_effective_date => l_start_date
3431 ,p_bg_id => l_bg_id
3432 ,p_payroll_id => l_payroll_id
3433 ,retro_ptp_data => rPTPData);
3434
3435 hr_utility.trace('Pension Basis: ');
3436
3437 FOR i in 1..rPTPData.count + 1 LOOP
3438 l_empty_pay_period := 'ja';
3439
3440 IF i<=rPTPData.count THEN
3441 lv_start_date := rPTPData(i).start_date;
3442 lv_end_date := rPTPData(i).end_date;
3443 lv_ptp := rPTPData(i).ptp;
3444 ELSE
3445 lv_start_date := l_start_date;
3446 lv_end_date := l_end_date;
3447 lv_ptp := l_ptp;
3448 END IF;
3449
3450 IF lv_end_date = l_asg_end_date THEN
3451 lv_end_date := last_day(lv_end_date);
3452 END IF;
3453
3454 pension_basis(p_assignment_id => l_assignment_id
3455 ,p_person_id => l_person_id
3456 ,p_bg_id => l_bg_id
3457 ,p_payroll_id => l_payroll_id
3458 ,p_start_date => lv_start_date
3459 ,p_end_date => lv_end_date
3460 ,p_contr_basis_values => l_contr_amt_values);
3461
3462 hr_utility.trace('Pension count 1: '||l_contr_amt_values.count);
3463
3464 hr_utility.trace('Pension Contribution : ');
3465 pension_contr(p_assignment_id => l_assignment_id
3466 ,p_person_id => l_person_id
3467 ,p_bg_id => l_bg_id
3468 ,p_payroll_id => l_payroll_id
3469 ,p_start_date => lv_start_date
3470 ,p_end_date => lv_end_date
3471 ,p_contr_amt_values => l_contr_amt_values);
3472 hr_utility.trace('Pension count 2: '||l_contr_amt_values.count);
3473
3474 hr_utility.trace('Pay Period Details ');
3475 OPEN csr_income_code(l_assignment_id,greatest(lv_start_date,l_asg_start_date));
3476 FETCH csr_income_code INTO l_income_code;
3477 CLOSE csr_income_code;
3478
3479 l_temp_date := greatest(lv_start_date,l_asg_start_date);
3480
3481 OPEN csr_part_details(l_assignment_id,greatest(lv_start_date,l_asg_start_date),least(lv_end_date,nvl(l_asg_end_date,hr_general.end_of_time)));
3482 LOOP
3483 FETCH csr_part_details INTO l_part_details;
3484 IF csr_part_details%FOUND THEN
3485 IF l_kop_report = 'Y' AND l_part_details.kop IS NULL THEN
3486 l_kop_report := 'N';
3487 fnd_message.set_name('PER','HR_NL_373556_DEFAULTED');
3488 fnd_message.set_token('VALUE','WNE');
3489 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','PARTICIPATION_KIND'));
3490 pay_action_information_api.create_action_information
3491 (
3492 p_action_information_id => l_action_info_id
3493 , p_action_context_id => l_child_aa_id
3494 , p_action_context_type => 'AAP'
3495 , p_object_version_number => l_ovn
3496 , p_assignment_id => l_assignment_id
3497 , p_effective_date => p_effective_date
3498 , p_source_id => NULL
3499 , p_source_text => NULL
3500 , p_tax_unit_id => l_org_id
3501 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3502 , p_action_information4 => fnd_message.get
3503 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3504 , p_action_information6 => 'Kind of Participation is null'
3505 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
3506 , p_action_information8 => to_char(l_asg_num));
3507 ELSIF l_kop_invalid = 'Y' AND l_part_details.kop IN ('VLF','WVP') THEN
3508 l_kop_invalid := 'N';
3509 fnd_message.set_name('PER','HR_374602_INVALID_VALUE');
3510 fnd_message.set_token('VALUE',l_part_details.kop);
3511 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','PARTICIPATION_KIND'));
3512 pay_action_information_api.create_action_information
3513 (
3514 p_action_information_id => l_action_info_id
3515 , p_action_context_id => l_child_aa_id
3516 , p_action_context_type => 'AAP'
3517 , p_object_version_number => l_ovn
3518 , p_assignment_id => l_assignment_id
3519 , p_effective_date => p_effective_date
3520 , p_source_id => NULL
3521 , p_source_text => NULL
3522 , p_tax_unit_id => l_org_id
3523 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3524 , p_action_information4 => fnd_message.get
3525 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3526 , p_action_information6 => 'Invalid Kind of Participation'
3527 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
3528 , p_action_information8 => to_char(l_asg_num));
3529 END IF;
3530
3531 l_wne_start_date:= NULL;
3532 l_wne_end_date:= NULL;
3533
3534 l_kop_start_date := l_part_details.kop_start_date;
3535 IF l_part_details.kop_end_date > last_day(l_part_details.kop_start_date) THEN
3536 l_kop_end_date := last_day(l_part_details.kop_start_date);
3537 ELSE
3538 l_kop_end_date := NVL(l_part_details.kop_end_date,least(l_end_date,nvl(l_asg_end_date,hr_general.end_of_time)));
3539 END IF;
3540
3541 IF l_temp_date > l_kop_start_date THEN
3542 l_kop_start_date := l_temp_date;
3543 END IF;
3544
3545 IF l_kop_end_date < l_kop_start_date THEN
3546 l_kop_end_date := least(l_end_date,nvl(l_asg_end_date,hr_general.end_of_time),l_part_details.kop_end_date);
3547 END IF;
3548
3549 IF l_kop_start_date <> l_start_date AND l_kop_start_date <> l_temp_date THEN
3550 l_wne_start_date:= l_temp_date;
3551 l_wne_end_date:= l_kop_start_date - 1;
3552 ELSE
3553 l_wne_start_date:= l_kop_end_date + 1;
3554 END IF;
3555
3556 l_temp_date := l_kop_end_date + 1;
3557
3558 IF lv_ptp = 0 THEN
3559 l_empty_pay_period := 'nee';
3560 END IF;
3561
3562 IF l_wne_end_date >= last_day(l_end_date) THEN
3563 l_wne_end_date := NULL;
3564 ELSIF l_kop_end_date = l_end_date THEN
3565 l_kop_end_date := NULL;
3566 END IF;
3567
3568 IF l_wne_start_date IS NOT NULL AND l_wne_end_date IS NOT NULL AND l_wne_start_date < l_end_date THEN
3569 hr_utility.trace('1.Archiving Pay Period Details-WNE');
3570 pay_action_information_api.create_action_information
3571 (
3572 p_action_information_id => l_action_info_id
3573 , p_action_context_id => l_child_aa_id
3574 , p_action_context_type => 'AAP'
3575 , p_object_version_number => l_ovn
3576 , p_assignment_id => l_assignment_id
3577 , p_effective_date => p_effective_date
3578 , p_source_id => NULL
3579 , p_source_text => NULL
3580 , p_action_information_category => 'NL_ABP_PAY_PERIOD_INFO'
3581 , p_action_information1 => fnd_date.date_to_canonical(l_wne_start_date)
3582 , p_action_information2 => fnd_date.date_to_canonical(l_wne_end_date)
3583 , p_action_information3 => 'WNE'
3584 , p_action_information4 => l_income_code
3585 , p_action_information5 => fnd_number.number_to_canonical(round(lv_ptp,2)/100)
3586 , p_action_information6 => l_empty_pay_period
3587 );
3588 hr_utility.trace('l_kop_report: '||l_kop_report);
3589 IF l_kop_report = 'Y' THEN
3590 l_kop_report := 'N';
3591 fnd_message.set_name('PER','HR_NL_373556_DEFAULTED');
3592 fnd_message.set_token('VALUE','WNE');
3593 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','PARTICIPATION_KIND'));
3594 pay_action_information_api.create_action_information
3595 (
3596 p_action_information_id => l_action_info_id
3597 , p_action_context_id => l_child_aa_id
3598 , p_action_context_type => 'AAP'
3599 , p_object_version_number => l_ovn
3600 , p_assignment_id => l_assignment_id
3601 , p_effective_date => p_effective_date
3602 , p_source_id => NULL
3603 , p_source_text => NULL
3604 , p_tax_unit_id => l_org_id
3605 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3606 , p_action_information4 => fnd_message.get
3607 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3608 , p_action_information6 => 'Kind of Participation is null'
3609 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
3610 , p_action_information8 => to_char(l_asg_num));
3611 END IF;
3612 END IF;
3613
3614 IF i<=rPTPData.count THEN
3615 IF p_term_flag='Y' THEN
3616 lv_spl_date := least(lv_end_date,l_end_date,l_wne_end_date);
3617 ELSE
3618 lv_spl_date := least(lv_end_date,l_end_date,l_kop_end_date);
3619 END IF;
3620 ELSE
3621 IF p_term_flag='Y' THEN
3622 lv_spl_date := least(lv_end_date,l_end_date,l_kop_end_date);
3623 ELSE
3624 lv_spl_date := l_kop_end_date;
3625 END IF;
3626 END IF;
3627
3628 hr_utility.trace('1.Archiving Pay Period Details-!WNE');
3629 pay_action_information_api.create_action_information
3630 (
3631 p_action_information_id => l_action_info_id
3632 , p_action_context_id => l_child_aa_id
3633 , p_action_context_type => 'AAP'
3634 , p_object_version_number => l_ovn
3635 , p_assignment_id => l_assignment_id
3636 , p_effective_date => p_effective_date
3637 , p_source_id => NULL
3638 , p_source_text => NULL
3639 , p_action_information_category => 'NL_ABP_PAY_PERIOD_INFO'
3640 , p_action_information1 => fnd_date.date_to_canonical(greatest(lv_start_date,l_asg_start_date,l_kop_start_date))
3641 , p_action_information2 => fnd_date.date_to_canonical(lv_spl_date)
3642 , p_action_information3 => nvl(l_part_details.kop,'WNE')
3643 , p_action_information4 => l_income_code
3644 , p_action_information5 => fnd_number.number_to_canonical(round(lv_ptp,2)/100)
3645 , p_action_information6 => l_empty_pay_period
3646 );
3647
3648 IF lv_spl_date IS NULL THEN
3649 l_kop_report := 'N';
3650 END IF;
3651 ELSE
3652 --
3653 IF l_kop_report = 'Y' THEN
3654 l_kop_report := 'N';
3655 fnd_message.set_name('PER','HR_NL_373556_DEFAULTED');
3656 fnd_message.set_token('VALUE','WNE');
3657 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','PARTICIPATION_KIND'));
3658 pay_action_information_api.create_action_information
3659 (
3660 p_action_information_id => l_action_info_id
3661 , p_action_context_id => l_child_aa_id
3662 , p_action_context_type => 'AAP'
3663 , p_object_version_number => l_ovn
3664 , p_assignment_id => l_assignment_id
3665 , p_effective_date => p_effective_date
3666 , p_source_id => NULL
3667 , p_source_text => NULL
3668 , p_tax_unit_id => l_org_id
3669 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3670 , p_action_information4 => fnd_message.get
3671 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3672 , p_action_information6 => 'Kind of Participation is null'
3673 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
3674 , p_action_information8 => to_char(l_asg_num));
3675 END IF;
3676
3677 IF lv_ptp = 0 THEN
3678 l_empty_pay_period := 'nee';
3679 END IF;
3680
3681 IF l_temp_date < l_end_date THEN
3682 IF l_asg_end_date <= l_end_date THEN
3683 l_wne_end_date := l_asg_end_date;
3684 ELSE
3685 l_wne_end_date := NULL;
3686 END IF;
3687 hr_utility.trace('2.Archiving Pay Period Details- WNE');
3688 pay_action_information_api.create_action_information
3689 (
3690 p_action_information_id => l_action_info_id
3691 , p_action_context_id => l_child_aa_id
3692 , p_action_context_type => 'AAP'
3693 , p_object_version_number => l_ovn
3694 , p_assignment_id => l_assignment_id
3695 , p_effective_date => p_effective_date
3696 , p_source_id => NULL
3697 , p_source_text => NULL
3698 , p_action_information_category => 'NL_ABP_PAY_PERIOD_INFO'
3699 , p_action_information1 => fnd_date.date_to_canonical(greatest(lv_start_date,l_asg_start_date,l_temp_date))
3700 , p_action_information2 => fnd_date.date_to_canonical(l_wne_end_date)
3701 , p_action_information3 => 'WNE'
3702 , p_action_information4 => l_income_code
3703 , p_action_information5 => fnd_number.number_to_canonical(round(lv_ptp,2)/100)
3704 , p_action_information6 => l_empty_pay_period
3705 );
3706 END IF;
3707 EXIT;
3708 END IF;
3709 END LOOP;
3710 CLOSE csr_part_details;
3711 IF l_contr_amt_values.count > 0 THEN
3712 FOR i in l_contr_amt_values.FIRST..l_contr_amt_values.LAST LOOP
3713 j := j + 1;
3714 l_all_contr_amt_values(j).pension_type := l_contr_amt_values(i).pension_type;
3715 l_all_contr_amt_values(j).pension_type_variant := l_contr_amt_values(i).pension_type_variant;
3716 l_all_contr_amt_values(j).date_earned := l_contr_amt_values(i).date_earned;
3717 l_all_contr_amt_values(j).contr_base := l_contr_amt_values(i).contr_base;
3718 l_all_contr_amt_values(j).contr_amt := l_contr_amt_values(i).contr_amt;
3719 END LOOP;
3720 l_contr_amt_values.delete;
3721 END IF;
3722 END LOOP;
3723 END LOOP;--csr_late_hir_prd
3724
3725 IF l_all_contr_amt_values.count > 0 THEN
3726 FOR i in l_all_contr_amt_values.FIRST..l_all_contr_amt_values.LAST LOOP
3727 FOR l_all_part_details IN csr_all_part_details(l_child_aa_id,l_assignment_id) LOOP
3728 IF TRUNC(l_all_contr_amt_values(i).date_earned,'MM')=TRUNC(fnd_date.canonical_to_date(l_all_part_details.start_date),'MM') THEN
3729
3730 IF TRUNC(l_all_contr_amt_values(i).date_earned,'MM')=TRUNC(l_asg_start_date,'MM') THEN
3731 l_pension_st_date := l_asg_start_date;
3732 ELSE
3733 l_pension_st_date := l_all_contr_amt_values(i).date_earned;
3734 END IF;
3735
3736 pay_action_information_api.create_action_information
3737 (
3738 p_action_information_id => l_action_info_id
3739 , p_action_context_id => l_child_aa_id
3740 , p_action_context_type => 'AAP'
3741 , p_object_version_number => l_ovn
3742 , p_assignment_id => l_assignment_id
3743 , p_effective_date => p_effective_date
3744 , p_source_id => NULL
3745 , p_source_text => NULL
3746 , p_action_information_category => 'NL_ABP_PENSION_INFO'
3747 , p_action_information1 => l_all_contr_amt_values(i).pension_type
3748 , p_action_information2 => l_all_contr_amt_values(i).pension_type_variant
3749 , p_action_information3 => fnd_date.date_to_canonical(l_pension_st_date)
3750 , p_action_information4 => fnd_date.date_to_canonical(l_all_contr_amt_values(i).date_earned)
3751 , p_action_information5 => 'R'
3752 , p_action_information6 => fnd_number.number_to_canonical(round(l_pension_salary,2))
3753 , p_action_information7 => fnd_number.number_to_canonical(round(l_all_contr_amt_values(i).contr_base,2))
3754 , p_action_information8 => fnd_number.number_to_canonical(round(l_all_contr_amt_values(i).contr_amt,2))
3755 , p_action_information9 => l_all_part_details.vop
3756 , p_action_information10 => l_all_part_details.start_date
3757 , p_action_information11 => fnd_date.date_to_canonical(l_asg_end_date)
3758 );
3759 END IF;
3760 END LOOP;
3761 END LOOP;
3762 END IF;
3763
3764 FOR rec_wth_old_start IN csr_hire_dt_chg_wth(c_person_id => p_person_id
3765 ,c_start_date => p_start_date
3766 ,c_end_date => p_end_date) LOOP
3767 hr_utility.trace('X====NL_ABP_WITHDRAWAL_INFO=======================X: '||p_person_id);
3768 hr_utility.trace('lv_wth_old_start: '||rec_wth_old_start.date_earned);
3769 pay_action_information_api.create_action_information
3770 (
3771 p_action_information_id => l_action_info_id
3772 , p_action_context_id => l_child_aa_id
3773 , p_action_context_type => 'AAP'
3774 , p_object_version_number => l_ovn
3775 , p_assignment_id => l_assignment_id
3776 , p_effective_date => p_effective_date
3777 , p_source_id => NULL
3778 , p_source_text => NULL
3779 , p_action_information_category => 'NL_ABP_WITHDRAWAL_INFO'
3780 , p_action_information1 => l_person_info.employee_number
3781 , p_action_information2 => to_char(l_asg_seq)
3782 , p_action_information3 => fnd_date.date_to_canonical(rec_wth_old_start.date_earned)
3783 , p_action_information4 => l_person_info.sofi_number
3784 , p_action_information5 => l_code_withdrawal
3785 );
3786 END LOOP;
3787
3788 hr_utility.trace('X====pay_period_archive==========================================X');
3789 EXCEPTION
3790 WHEN others THEN
3791 hr_utility.trace('Exception in pay_period_archive SQL-ERRM : '||SQLERRM);
3792 log_tech_exception(p_action_context_id => g_payroll_action_id,
3793 p_err_msg => 'pay_period_archive : '|| substr (sqlerrm, 1, 180));
3794 END pay_period_archive;
3795 --=========================================================================
3796
3797 BEGIN
3798 hr_utility.trace('+====archive_code==========================================+');
3799 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
3800 hr_utility.trace('p_effective_date : '||p_effective_date);
3801
3802 l_contr_amt_values.delete;
3803 l_all_contr_amt_values.delete;
3804
3805 OPEN csr_get_pact_chunk(p_assignment_action_id);
3806 FETCH csr_get_pact_chunk INTO l_payroll_action_id, l_chunk_number;
3807 CLOSE csr_get_pact_chunk;
3808
3809 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
3810 hr_utility.trace('l_chunk_number : '||l_chunk_number);
3811
3812 get_all_parameters (
3813 p_payroll_action_id => l_payroll_action_id
3814 ,p_org_struct_id => l_org_struct_id
3815 ,p_org_id => l_org_id
3816 ,p_report_type => l_report_type
3817 ,p_seq_num => l_seq_num
3818 ,p_bg_id => l_bg_id
3819 ,p_start_date => l_start_date
3820 ,p_end_date => l_end_date);
3821
3822 hr_utility.trace('Org Struct Id : ' ||l_org_struct_id);
3823 hr_utility.trace('Org Id : ' ||l_org_id);
3824 hr_utility.trace('Report Type : ' ||l_report_type);
3825 hr_utility.trace('Business Group Id : '||l_bg_id);
3826 hr_utility.trace('Start Date : ' ||l_start_date);
3827 hr_utility.trace('End Date : ' ||l_end_date);
3828
3829 OPEN csr_asg_id(p_assignment_action_id,l_payroll_action_id);
3830 FETCH csr_asg_id into l_assignment_id;
3831 CLOSE csr_asg_id;
3832
3833 hr_utility.trace('Assignment Id : ' ||l_assignment_id);
3834
3835 OPEN csr_get_person_id(l_assignment_id);
3836 FETCH csr_get_person_id into l_person_id;
3837 CLOSE csr_get_person_id;
3838
3839 hr_utility.trace('Person Id : ' ||l_person_id);
3840
3841 IF l_payroll_id IS NULL THEN
3842 OPEN csr_get_payroll_id(l_assignment_id);
3843 FETCH csr_get_payroll_id INTO l_payroll_id;
3844 CLOSE csr_get_payroll_id;
3845 END IF;
3846
3847 hr_utility.trace('Payroll Id : ' ||l_payroll_id);
3848
3849 SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
3850
3851 hr_nonrun_asact.insact(
3852 lockingactid => l_child_aa_id
3853 , assignid => l_assignment_id
3854 , pactid => l_payroll_action_id
3855 , chunk => l_chunk_number
3856 , status => 'C'
3857 , source_act => p_assignment_action_id);
3858
3859 hr_utility.trace('Child Asg Action Id : ' ||l_child_aa_id);
3860
3861 OPEN csr_get_asg_period(l_assignment_id);
3862 FETCH csr_get_asg_period into l_asg_start_date,l_asg_end_date;
3863 CLOSE csr_get_asg_period;
3864
3865 hr_utility.trace('Asg Start date '||l_asg_start_date);
3866 hr_utility.trace('Asg End date '||l_asg_end_date);
3867
3868 /* --15859347
3869 OPEN csr_get_asg_term_date(l_bg_id,l_end_date,l_assignment_id);
3870 FETCH csr_get_asg_term_date INTO l_asg_term_date,l_term_pos_id;
3871 IF csr_get_asg_term_date%FOUND THEN
3872 CLOSE csr_get_asg_term_date;
3873 l_asg_end_date := l_asg_term_date;
3874 ELSE
3875 CLOSE csr_get_asg_term_date;
3876 END IF;
3877 */ --15859347
3878
3879 --15859347
3880 l_asg_term_date := NULL;
3881 OPEN c_term_date(l_assignment_id, l_person_id, l_bg_id);
3882 FETCH c_term_date INTO l_asg_term_date;
3883 CLOSE c_term_date;
3884
3885 IF NVL(l_asg_term_date,hr_general.end_of_time) <> hr_general.end_of_time
3886 THEN
3887 l_asg_end_date := l_asg_term_date;
3888 END IF;
3889 --15859347
3890
3891 hr_utility.trace('Asg End date '||l_asg_end_date);
3892
3893 IF l_asg_end_date IS NOT NULL THEN
3894 --asg is terminated. Get the termination reason
3895 OPEN csr_get_end_reason(l_assignment_id,l_asg_end_date);
3896 FETCH csr_get_end_reason INTO l_term_reason;
3897 CLOSE csr_get_end_reason;
3898 IF l_asg_end_date > l_start_date THEN
3899 l_end_date := least(l_end_date,l_asg_end_date);
3900 END IF;
3901 END IF;
3902
3903 hr_utility.trace('End Date : '||l_end_date);
3904
3905 OPEN csr_get_person_info(l_person_id,l_bg_id,nvl(l_asg_end_date,l_end_date));
3906 FETCH csr_get_person_info into l_person_info;
3907 CLOSE csr_get_person_info;
3908
3909 OPEN csr_get_asg_seq(l_assignment_id,nvl(l_asg_end_date,l_end_date));
3910 FETCH csr_get_asg_seq INTO l_asg_seq,l_asg_num;
3911 CLOSE csr_get_asg_seq;
3912
3913 OPEN csr_get_withdrawal_flag(l_assignment_id);
3914 FETCH csr_get_withdrawal_flag INTO l_withdrawal_flag;
3915 CLOSE csr_get_withdrawal_flag;
3916
3917 --
3918 IF l_person_info.init = '' OR l_person_info.init IS NULL THEN
3919 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
3920 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','INITIALS'));
3921 pay_action_information_api.create_action_information
3922 (
3923 p_action_information_id => l_action_info_id
3924 , p_action_context_id => l_child_aa_id
3925 , p_action_context_type => 'AAP'
3926 , p_object_version_number => l_ovn
3927 , p_assignment_id => l_assignment_id
3928 , p_effective_date => p_effective_date
3929 , p_source_id => NULL
3930 , p_source_text => NULL
3931 , p_tax_unit_id => l_org_id
3932 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3933 , p_action_information4 => fnd_message.get
3934 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3935 , p_action_information6 => 'Invalid Initials for the employee'
3936 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
3937 , p_action_information8 => to_char(l_asg_num));
3938 ELSIF length(l_person_info.init) > 6 THEN
3939 fnd_message.set_name('PER','HR_374602_INVALID_VALUE');
3940 fnd_message.set_token('VALUE',l_person_info.init);
3941 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','INITIALS'));
3942 l_person_info.init := substr(l_person_info.init, 1, 6);
3943 pay_action_information_api.create_action_information
3944 (
3945 p_action_information_id => l_action_info_id
3946 , p_action_context_id => l_child_aa_id
3947 , p_action_context_type => 'AAP'
3948 , p_object_version_number => l_ovn
3949 , p_assignment_id => l_assignment_id
3950 , p_effective_date => p_effective_date
3951 , p_source_id => NULL
3952 , p_source_text => NULL
3953 , p_tax_unit_id => l_org_id
3954 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3955 , p_action_information4 => fnd_message.get
3956 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3957 , p_action_information6 => 'Invalid Employee Number'
3958 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
3959 , p_action_information8 => to_char(l_asg_num));
3960 END IF;
3961 --
3962 IF l_person_info.employee_number IS NULL THEN
3963 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
3964 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','EMPLOYEE_NUMBER'));
3965 pay_action_information_api.create_action_information
3966 (
3967 p_action_information_id => l_action_info_id
3968 , p_action_context_id => l_child_aa_id
3969 , p_action_context_type => 'AAP'
3970 , p_object_version_number => l_ovn
3971 , p_assignment_id => l_assignment_id
3972 , p_effective_date => p_effective_date
3973 , p_source_id => NULL
3974 , p_source_text => NULL
3975 , p_tax_unit_id => l_org_id
3976 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3977 , p_action_information4 => fnd_message.get
3978 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3979 , p_action_information6 => 'Employee Number missing'
3980 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
3981 , p_action_information8 => to_char(l_asg_num));
3982 ELSIF length(l_person_info.employee_number) > 15 THEN
3983 fnd_message.set_name('PER','HR_374602_INVALID_VALUE');
3984 fnd_message.set_token('VALUE',l_person_info.employee_number);
3985 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','EMPLOYEE_NUMBER'));
3986 l_person_info.employee_number := substr(l_person_info.employee_number,-15); --fetch last fifteen digits
3987 pay_action_information_api.create_action_information
3988 (
3989 p_action_information_id => l_action_info_id
3990 , p_action_context_id => l_child_aa_id
3991 , p_action_context_type => 'AAP'
3992 , p_object_version_number => l_ovn
3993 , p_assignment_id => l_assignment_id
3994 , p_effective_date => p_effective_date
3995 , p_source_id => NULL
3996 , p_source_text => NULL
3997 , p_tax_unit_id => l_org_id
3998 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
3999 , p_action_information4 => fnd_message.get
4000 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4001 , p_action_information6 => 'Invalid Employee Number'
4002 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4003 , p_action_information8 => to_char(l_asg_num));
4004 ELSE
4005 l_person_info.employee_number := lpad(l_person_info.employee_number, 15, 0);
4006 END IF;
4007 --
4008 IF l_person_info.sofi_number IS NULL THEN
4009 fnd_message.set_name('PER','HR_NL_373556_DEFAULTED');
4010 fnd_message.set_token('VALUE','000000000');
4011 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','BSN'));
4012 l_person_info.sofi_number := '000000000';
4013 pay_action_information_api.create_action_information
4014 (
4015 p_action_information_id => l_action_info_id
4016 , p_action_context_id => l_child_aa_id
4017 , p_action_context_type => 'AAP'
4018 , p_object_version_number => l_ovn
4019 , p_assignment_id => l_assignment_id
4020 , p_effective_date => p_effective_date
4021 , p_source_id => NULL
4022 , p_source_text => NULL
4023 , p_tax_unit_id => l_org_id
4024 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4025 , p_action_information4 => fnd_message.get
4026 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4027 , p_action_information6 => 'BSN/Sofi Number missing'
4028 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4029 , p_action_information8 => to_char(l_asg_num));
4030 ELSIF length(l_person_info.sofi_number) > 9 THEN
4031 fnd_message.set_name('PER','HR_374602_INVALID_VALUE');
4032 fnd_message.set_token('VALUE',l_person_info.sofi_number);
4033 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','BSN'));
4034 l_person_info.sofi_number := substr(l_person_info.sofi_number, 1, 9);
4035 pay_action_information_api.create_action_information
4036 (
4037 p_action_information_id => l_action_info_id
4038 , p_action_context_id => l_child_aa_id
4039 , p_action_context_type => 'AAP'
4040 , p_object_version_number => l_ovn
4041 , p_assignment_id => l_assignment_id
4042 , p_effective_date => p_effective_date
4043 , p_source_id => NULL
4044 , p_source_text => NULL
4045 , p_tax_unit_id => l_org_id
4046 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4047 , p_action_information4 => fnd_message.get
4048 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4049 , p_action_information6 => 'Invalid BSN/Sofi Number'
4050 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4051 , p_action_information8 => to_char(l_asg_num));
4052 END IF;
4053
4054 OPEN csr_get_emp_num_override(l_org_id,l_bg_id);
4055 FETCH csr_get_emp_num_override INTO l_emp_num_override;
4056 CLOSE csr_get_emp_num_override;
4057
4058
4059 IF l_emp_num_override.override <> 0 THEN
4060 x:= greatest(l_emp_num_override.start_pos,1) - 1;
4061 y:= length(l_emp_num_override.override)+1;
4062 IF x+y <= 16 THEN
4063 l_person_info.employee_number := substr(l_person_info.employee_number,1,x)
4064 ||l_emp_num_override.override
4065 ||substr(l_person_info.employee_number,x+y);
4066 END IF;
4067 END IF;
4068
4069 --Assignment withdrawal flag not set
4070 IF nvl(l_withdrawal_flag,'N') = 'N' THEN
4071 --
4072
4073 IF l_asg_end_date IS NOT NULL THEN
4074 FOR l_prev_asg_info IN csr_get_prev_asg_info(l_assignment_id) LOOP
4075 IF l_asg_end_date < nvl(l_prev_asg_info.term_date,l_prev_asg_info.start_date) THEN
4076 -- Termination date is less than the prev reported value.
4077 -- Withdrawal Pay period to be reported.
4078
4079 hr_utility.trace('Termination Date has changed - Less' );
4080 hr_utility.trace('Prev Period Start Date ' ||l_prev_asg_info.start_date);
4081 hr_utility.trace('Prev Termination Date ' ||l_prev_asg_info.term_date);
4082 hr_utility.trace('End Date ' ||l_asg_end_date);
4083
4084 pay_action_information_api.create_action_information
4085 (
4086 p_action_information_id => l_action_info_id
4087 , p_action_context_id => l_child_aa_id
4088 , p_action_context_type => 'AAP'
4089 , p_object_version_number => l_ovn
4090 , p_assignment_id => l_assignment_id
4091 , p_effective_date => p_effective_date
4092 , p_source_id => NULL
4093 , p_source_text => NULL
4094 , p_action_information_category => 'NL_ABP_WITHDRAWAL_INFO'
4095 , p_action_information1 => l_person_info.employee_number
4096 , p_action_information2 => to_char(l_asg_seq)
4097 , p_action_information3 => fnd_date.date_to_canonical(l_prev_asg_info.start_date)
4098 , p_action_information4 => l_person_info.sofi_number
4099 , p_action_information5 => fnd_date.date_to_canonical(l_start_date)
4100 , p_action_information6 => fnd_date.date_to_canonical(l_end_date)
4101 );
4102 IF l_prev_asg_info.start_date <= l_asg_end_date THEN
4103 hr_utility.trace('Termination Less : Entry for '||l_prev_asg_info.start_date);
4104 pay_period_data(l_pay_period_count) := l_prev_asg_info.start_date;
4105 l_pay_period_count := l_pay_period_count + 1;
4106 END IF;
4107 l_flag:=1;
4108 END IF;
4109 END LOOP;
4110
4111 FOR l_prev_asg_info2 IN csr_get_prev_asg_info2(l_assignment_id) LOOP
4112 IF l_asg_end_date > l_prev_asg_info2.term_date THEN
4113 hr_utility.trace('Termination Date has changed - Greater' );
4114 hr_utility.trace('Termination Greater : Entry for '||l_prev_asg_info2.start_date);
4115 -- Termination date is greater than the prev reported value.
4116 pay_period_data(l_pay_period_count) := l_prev_asg_info2.start_date;
4117 l_pay_period_count := l_pay_period_count + 1;
4118 l_flag:=1;
4119 END IF;
4120 END LOOP;
4121
4122 IF (l_flag = 1 OR l_asg_end_date < l_start_date ) AND l_asg_end_date >= g_valid_start_date THEN
4123 --Either termination date is less or greater than current value
4124 hr_utility.trace('Termination Date Change - Either : Entry for '||l_asg_end_date);
4125 pay_period_data(l_pay_period_count) := l_asg_end_date;
4126 l_pay_period_count := l_pay_period_count + 1;
4127 END IF;
4128
4129 ELSE
4130 --Asg end date is null.
4131 --The following check is done to see if termination has been reversed.
4132 --Fetch all periods with incorrect term date
4133 FOR l_prev_asg_info3 IN csr_get_prev_asg_info3(l_assignment_id) LOOP
4134 hr_utility.trace('Termination Reversal : Entry for '||l_prev_asg_info3.start_date);
4135 pay_period_data(l_pay_period_count) := l_prev_asg_info3.start_date;
4136 l_pay_period_count := l_pay_period_count + 1;
4137 END LOOP;
4138
4139 IF l_pay_period_count > 1 THEN
4140 hr_utility.trace('Termination Reversal - Current: Entry for '||l_start_date);
4141 pay_period_data(l_pay_period_count) := l_start_date;
4142 l_pay_period_count := l_pay_period_count + 1;
4143 END IF;
4144
4145 END IF;
4146
4147 /*hr_utility.trace('Employee Number/Asg Num/Asg Start Date/Asg End Date/Termination Reason/Emp Kind: '
4148 ||l_person_info.employee_number||' '
4149 ||nvl(l_numiv_override,l_asg_seq)||' '
4150 ||l_asg_start_date||' '
4151 ||l_asg_end_date||' '
4152 ||l_term_reason||' '
4153 ||l_kind_emp);*/
4154
4155 hr_utility.trace('Archiving Assignment Info : ');
4156
4157 pay_action_information_api.create_action_information
4158 (
4159 p_action_information_id => l_action_info_id
4160 , p_action_context_id => l_child_aa_id
4161 , p_action_context_type => 'AAP'
4162 , p_object_version_number => l_ovn
4163 , p_assignment_id => l_assignment_id
4164 , p_effective_date => p_effective_date
4165 , p_source_id => NULL
4166 , p_source_text => NULL
4167 , p_action_information_category => 'NL_ABP_ASG_INFO'
4168 , p_action_information1 => l_person_info.employee_number
4169 , p_action_information2 => to_char(l_asg_seq)
4170 , p_action_information3 => fnd_date.date_to_canonical(l_asg_start_date)
4171 , p_action_information4 => fnd_date.date_to_canonical(l_asg_end_date)
4172 , p_action_information5 => l_term_reason
4173 , p_action_information6 => l_kind_emp
4174 , p_action_information7 => fnd_date.date_to_canonical(l_start_date)
4175 , p_action_information8 => fnd_date.date_to_canonical(l_end_date)
4176 );
4177
4178 --to determine if person data to be reported.
4179 --In case of multiple asgs, person data required only once.
4180 l_person_data := 'Y';
4181 IF person_list.count > 0 THEN
4182 FOR i in person_list.FIRST..person_list.LAST LOOP
4183 IF l_person_id = person_list(i) THEN
4184 l_person_data := 'N';
4185 EXIT;
4186 END IF;
4187 END LOOP;
4188 END IF;
4189 IF l_person_data = 'Y' THEN
4190 person_list(l_person_count) := l_person_id;
4191 l_person_count := l_person_count + 1;
4192
4193 OPEN csr_get_shared_types(l_person_info.nationality,l_bg_id,'NL_NATIONALITY');
4194 FETCH csr_get_shared_types INTO l_nationality;
4195 CLOSE csr_get_shared_types;
4196
4197 OPEN csr_get_foreign_coun(l_person_id,nvl(l_asg_end_date,l_end_date));
4198 FETCH csr_get_foreign_coun INTO l_native;
4199 CLOSE csr_get_foreign_coun;
4200
4201 IF l_native = 'N' THEN
4202 OPEN csr_get_marital_status(l_person_id,l_bg_id,nvl(l_asg_end_date,l_end_date));
4203 FETCH csr_get_marital_status INTO l_marital_status;
4204 CLOSE csr_get_marital_status;
4205
4206 OPEN csr_get_marital_start(l_person_id,l_bg_id,l_start_date,nvl(l_asg_end_date,l_end_date));
4207 FETCH csr_get_marital_start INTO l_start_date_ms;
4208 CLOSE csr_get_marital_start;
4209
4210 OPEN csr_get_partner_details(l_person_id,l_bg_id,l_start_date,nvl(l_asg_end_date,l_end_date));
4211 FETCH csr_get_partner_details INTO l_partner_details;
4212 CLOSE csr_get_partner_details;
4213 END IF;
4214
4215 OPEN csr_get_person_addr(l_person_id,nvl(l_asg_end_date,l_end_date));
4216 FETCH csr_get_person_addr INTO l_person_addr;
4217 IF csr_get_person_addr%FOUND THEN
4218 IF l_person_addr.style = 'NL' THEN
4219 hr_utility.trace('Dutch Address Found : ');
4220 l_street := substr(l_person_addr.region, 1, 24);
4221 l_house_number := l_person_addr.nl_hno;
4222 l_house_number_addition := l_person_addr.nl_hnoa;
4223 l_location := l_person_addr.nl_location;
4224 l_postal_code := REPLACE(l_person_addr.postal,' ','');
4225 l_city := l_person_addr.city;
4226 ELSIF l_person_addr.style = 'NL_GLB' THEN
4227 hr_utility.trace('Foreign Address Found : ');
4228 l_street := l_person_addr.glb_street;
4229 l_house_number := l_person_addr.glb_hno;
4230 l_location := l_person_addr.glb_location;
4231 l_postal_code := substr(l_person_addr.postal, 1, 9);
4232 l_city := l_person_addr.city;
4233 l_region := substr(l_person_addr.region, 1, 24);
4234 l_iso := l_person_addr.iso;
4235 END IF;
4236
4237 IF l_street IS NULL THEN
4238 pay_action_information_api.create_action_information
4239 (
4240 p_action_information_id => l_action_info_id
4241 , p_action_context_id => l_child_aa_id
4242 , p_action_context_type => 'AAP'
4243 , p_object_version_number => l_ovn
4244 , p_assignment_id => l_assignment_id
4245 , p_effective_date => p_effective_date
4246 , p_source_id => NULL
4247 , p_source_text => NULL
4248 , p_tax_unit_id => l_org_id
4249 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4250 , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_STREET')
4251 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4252 , p_action_information6 => 'Street name missing in the address'
4253 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4254 , p_action_information8 => to_char(l_asg_num));
4255 END IF;
4256 --
4257 /* --15859347
4258 IF l_house_number IS NULL THEN
4259 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4260 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','HOUSE_NUMBER'));
4261 pay_action_information_api.create_action_information
4262 (
4263 p_action_information_id => l_action_info_id
4264 , p_action_context_id => l_child_aa_id
4265 , p_action_context_type => 'AAP'
4266 , p_object_version_number => l_ovn
4267 , p_assignment_id => l_assignment_id
4268 , p_effective_date => p_effective_date
4269 , p_source_id => NULL
4270 , p_source_text => NULL
4271 , p_tax_unit_id => l_org_id
4272 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4273 , p_action_information4 => fnd_message.get
4274 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4275 , p_action_information6 => 'House Number missing in address'
4276 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4277 , p_action_information8 => to_char(l_asg_num));
4278 END IF;
4279 */ --15859347
4280 --
4281 IF l_city IS NULL THEN
4282 pay_action_information_api.create_action_information
4283 (
4284 p_action_information_id => l_action_info_id
4285 , p_action_context_id => l_child_aa_id
4286 , p_action_context_type => 'AAP'
4287 , p_object_version_number => l_ovn
4288 , p_assignment_id => l_assignment_id
4289 , p_effective_date => p_effective_date
4290 , p_source_id => NULL
4291 , p_source_text => NULL
4292 , p_tax_unit_id => l_org_id
4293 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4294 , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_CITY')
4295 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4296 , p_action_information6 => 'City name missing in the address'
4297 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4298 , p_action_information8 => to_char(l_asg_num));
4299 END IF;
4300 --
4301 IF l_person_addr.style = 'NL' AND l_postal_code IS NULL THEN
4302 fnd_message.set_name('PER','HR_NL_373556_DEFAULTED');
4303 fnd_message.set_token('VALUE','0000AA');
4304 fnd_message.set_token('FIELD',hr_general.decode_lookup('NL_FORM_LABELS','POSTAL_CODE'));
4305 l_postal_code := '0000AA';
4306 pay_action_information_api.create_action_information
4307 (
4308 p_action_information_id => l_action_info_id
4309 , p_action_context_id => l_child_aa_id
4310 , p_action_context_type => 'AAP'
4311 , p_object_version_number => l_ovn
4312 , p_assignment_id => l_assignment_id
4313 , p_effective_date => p_effective_date
4314 , p_source_id => NULL
4315 , p_source_text => NULL
4316 , p_tax_unit_id => l_org_id
4317 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4318 , p_action_information4 => fnd_message.get
4319 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4320 , p_action_information6 => 'Postal Code missing in address'
4321 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4322 , p_action_information8 => to_char(l_asg_num));
4323 END IF;
4324 --
4325 IF l_person_addr.style = 'NL_GLB' AND l_iso IS NULL THEN
4326 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4327 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','ISO_CODE'));
4328 pay_action_information_api.create_action_information
4329 (
4330 p_action_information_id => l_action_info_id
4331 , p_action_context_id => l_child_aa_id
4332 , p_action_context_type => 'AAP'
4333 , p_object_version_number => l_ovn
4334 , p_assignment_id => l_assignment_id
4335 , p_effective_date => p_effective_date
4336 , p_source_id => NULL
4337 , p_source_text => NULL
4338 , p_tax_unit_id => l_org_id
4339 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4340 , p_action_information4 => fnd_message.get
4341 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4342 , p_action_information6 => 'ISO Country code missing in address'
4343 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4344 , p_action_information8 => to_char(l_asg_num));
4345 END IF;
4346 --
4347 END IF;--csr_get_person_addr%FOUND
4348 CLOSE csr_get_person_addr;
4349
4350 IF l_nationality IS NULL THEN
4351 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4352 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','NATIONALITY'));
4353 pay_action_information_api.create_action_information
4354 (
4355 p_action_information_id => l_action_info_id
4356 , p_action_context_id => l_child_aa_id
4357 , p_action_context_type => 'AAP'
4358 , p_object_version_number => l_ovn
4359 , p_assignment_id => l_assignment_id
4360 , p_effective_date => p_effective_date
4361 , p_source_id => NULL
4362 , p_source_text => NULL
4363 , p_tax_unit_id => l_org_id
4364 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4365 , p_action_information4 => fnd_message.get
4366 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4367 , p_action_information6 => 'Invalid Nationality for the employee'
4368 , p_action_information7 => l_person_info.prefix ||' '|| l_person_info.last_name ||' '|| l_person_info.init
4369 , p_action_information8 => to_char(l_asg_num));
4370 END IF;
4371 --
4372
4373 OPEN csr_prin_obj(l_assignment_id,nvl(l_asg_end_date,l_end_date));
4374 FETCH csr_prin_obj into l_individual_exem;
4375 CLOSE csr_prin_obj;
4376
4377 hr_utility.trace('Archiving Person Data : ');
4378
4379 pay_action_information_api.create_action_information
4380 (
4381 p_action_information_id => l_action_info_id
4382 , p_action_context_id => l_child_aa_id
4383 , p_action_context_type => 'AAP'
4384 , p_object_version_number => l_ovn
4385 , p_assignment_id => l_assignment_id
4386 , p_effective_date => p_effective_date
4387 , p_source_id => NULL
4388 , p_source_text => NULL
4389 , p_action_information_category => 'NL_ABP_EMP_INFO'
4390 , p_action_information1 => l_person_id
4391 , p_action_information2 => l_person_info.employee_number
4392 , p_action_information3 => l_person_info.sofi_number
4393 , p_action_information4 => l_person_info.last_name
4394 , p_action_information5 => l_person_info.init
4395 , p_action_information6 => l_person_info.prefix
4396 , p_action_information7 => fnd_date.date_to_canonical(l_person_info.date_of_birth)
4397 , p_action_information8 => l_nationality
4398 , p_action_information9 => l_person_info.gender
4399 , p_action_information10 => fnd_date.date_to_canonical(l_person_info.date_of_death)
4400 , p_action_information11 => l_partner_details.last_name
4401 , p_action_information12 => l_partner_details.prefix
4402 , p_action_information13 => l_partner_details.init
4403 , p_action_information14 => l_street
4404 , p_action_information15 => l_house_number
4405 , p_action_information16 => l_house_number_addition
4406 , p_action_information17 => l_location
4407 , p_action_information18 => l_postal_code
4408 , p_action_information19 => l_city
4409 , p_action_information20 => l_region
4410 , p_action_information21 => l_iso
4411 , p_action_information22 => l_person_addr.style
4412 , p_action_information23 => l_marital_status
4413 , p_action_information24 => fnd_date.date_to_canonical(l_start_date_ms)
4414 , p_action_information25 => l_individual_exem
4415 , p_action_information26 => fnd_date.date_to_canonical(l_start_date)
4416 , p_action_information27 => fnd_date.date_to_canonical(nvl(l_asg_end_date,l_end_date))
4417 , p_action_information28 => l_person_data
4418 );
4419
4420
4421 hr_utility.trace('Pay Period Count '||pay_period_data.count);
4422 l_kop_report := 'Y';
4423 IF pay_period_data.count > 0 THEN
4424 l_temp_dates.delete;
4425 l_temp := 0;
4426 --Termination Date has changed
4427 FOR i IN REVERSE pay_period_data.FIRST..pay_period_data.LAST LOOP
4428 l_contr_amt_values.delete;
4429 l_all_contr_amt_values.delete;
4430
4431 OPEN csr_get_period(l_payroll_id,pay_period_data(i));
4432 FETCH csr_get_period INTO l_start_date,l_end_date;
4433 CLOSE csr_get_period;
4434
4435 IF l_asg_end_date > l_start_date THEN
4436 l_end_date := least(l_asg_end_date,l_end_date);
4437 END IF;
4438
4439 hr_utility.trace('Pay period start date : '||l_start_date);
4440 hr_utility.trace('Pay period end date : '||l_end_date);
4441
4442 l_fetch := 'Y';
4443 IF l_temp_dates.count > 0 THEN
4444 FOR l IN l_temp_dates.FIRST..l_temp_dates.LAST LOOP
4445 IF l_temp_dates(l) = l_start_date THEN
4446 l_fetch := 'N';
4447 END IF;
4448 END LOOP;
4449 END IF;
4450
4451 hr_utility.trace('l_fetch : '||l_fetch);
4452
4453 IF l_fetch = 'Y' AND (l_asg_end_date IS NULL OR l_asg_end_date > l_start_date) THEN
4454 l_temp_dates(l_temp) := l_start_date;
4455 l_temp := l_temp + 1;
4456
4457 pay_period_archive (p_assignment_id => l_assignment_id
4458 , p_person_id => l_person_id
4459 , p_bg_id => l_bg_id
4460 , p_payroll_id => l_payroll_id
4461 , p_start_date => l_start_date
4462 , p_end_date => l_end_date
4463 , p_term_flag => 'Y');
4464 END IF;
4465 END LOOP;
4466
4467 ELSIF l_asg_end_date IS NULL OR l_asg_end_date>=g_valid_start_date THEN
4468 pay_period_archive (p_assignment_id => l_assignment_id
4469 , p_person_id => l_person_id
4470 , p_bg_id => l_bg_id
4471 , p_payroll_id => l_payroll_id
4472 , p_start_date => l_start_date
4473 , p_end_date => l_end_date
4474 , p_term_flag => 'N');
4475 END IF;
4476 END IF;--l_person_data = 'Y'
4477
4478 ELSE --Assignment Withdrawal flag set
4479 hr_utility.trace('Assignment Withdrawal flag set: '||l_assignment_id);
4480 --check if previously withdrawn details sent
4481 OPEN csr_prev_withdrawn(l_assignment_id);
4482 FETCH csr_prev_withdrawn INTO l_prev_withdrawn;
4483 IF csr_prev_withdrawn%NOTFOUND THEN
4484 CLOSE csr_prev_withdrawn;
4485
4486 l_pay_period_det_cnt := months_between(trunc(l_end_date,'MM'),trunc(l_asg_start_date,'MM'));
4487 l_wth_prd_start_date := greatest(g_valid_start_date,trunc(l_asg_start_date,'MM'));
4488 l_wth_prd_end_date := last_day(l_end_date);
4489
4490 WHILE l_wth_prd_start_date <= l_wth_prd_end_date
4491 LOOP
4492 hr_utility.trace('months_between(l_wth_prd_start_date,l_asg_start_date) : '||months_between(l_wth_prd_start_date,l_asg_start_date));
4493 IF l_pay_period_det_cnt = months_between(trunc(l_wth_prd_start_date,'MM'),trunc(l_asg_start_date,'MM')) THEN
4494 l_code_withdrawal := 'ja';
4495 END IF;
4496 pay_action_information_api.create_action_information
4497 (
4498 p_action_information_id => l_action_info_id
4499 , p_action_context_id => l_child_aa_id
4500 , p_action_context_type => 'AAP'
4501 , p_object_version_number => l_ovn
4502 , p_assignment_id => l_assignment_id
4503 , p_effective_date => p_effective_date
4504 , p_source_id => NULL
4505 , p_source_text => NULL
4506 , p_action_information_category => 'NL_ABP_COMP_WITHDRAWAL_INFO'
4507 , p_action_information1 => l_person_info.employee_number
4508 , p_action_information2 => to_char(l_asg_seq)
4509 , p_action_information3 => fnd_date.date_to_canonical(l_wth_prd_start_date)
4510 , p_action_information4 => l_person_info.sofi_number
4511 , p_action_information5 => l_code_withdrawal
4512 );
4513 l_wth_prd_start_date := add_months(l_wth_prd_start_date,1);
4514 END LOOP;
4515 END IF;
4516 CLOSE csr_prev_withdrawn;
4517 END IF;
4518 hr_utility.trace('X====archive_code==========================================X');
4519 EXCEPTION
4520
4521 WHEN TOO_MANY_ROWS THEN
4522 hr_utility.trace('Too Many Rows Exception in archive_code');
4523 log_tech_exception(p_action_context_id => g_payroll_action_id,
4524 p_err_msg => 'archive_code : '|| substr (sqlerrm, 1, 180)); --14793405
4525
4526 WHEN NO_DATA_FOUND THEN
4527 hr_utility.trace('No Data Found Exception in archive_code');
4528 log_tech_exception(p_action_context_id => g_payroll_action_id,
4529 p_err_msg => 'archive_code : '|| substr (sqlerrm, 1, 180)); --14793405
4530
4531 WHEN others THEN
4532 hr_utility.trace('Exception in archive_code SQL-ERRM : '||SQLERRM);
4533 log_tech_exception(p_action_context_id => g_payroll_action_id,
4534 p_err_msg => 'archive_code : '|| substr (sqlerrm, 1, 180)); --14793405
4535
4536 END archive_code;
4537
4538 -- =============================================================================
4539 -- deinitialization_code - Archive at File and Employer level
4540 -- =============================================================================
4541 PROCEDURE deinitialization_code(p_payroll_action_id IN NUMBER)
4542 IS
4543
4544 -- Cursor to fetch ABP Sender details
4545 CURSOR csr_get_sender_details(c_org_id IN NUMBER
4546 ,c_bg_id IN NUMBER) IS
4547 SELECT org_information1 reg_num_sender
4548 ,decode(org_information2,'Y','T','N','P') prod_test
4549 ,substr(org_information3,1,45) name_sender
4550 FROM hr_organization_units hou
4551 ,hr_organization_information hoi
4552 WHERE hou.organization_id = nvl (c_org_id
4553 ,c_bg_id)
4554 AND hou.business_group_id = c_bg_id
4555 AND hoi.organization_id = hou.organization_id
4556 AND org_information_context = 'NL_ABP_SENDER_DETAILS';
4557 --=========================================================================
4558 CURSOR csr_app_version IS
4559 SELECT release_name
4560 FROM fnd_product_groups;
4561 --=========================================================================
4562 CURSOR csr_find_parent_id(c_org_id IN NUMBER
4563 ,c_version_id IN NUMBER
4564 ,c_bg_id IN NUMBER) IS
4565 SELECT organization_id_parent
4566 FROM per_org_structure_elements
4567 WHERE organization_id_child = c_org_id
4568 AND org_structure_version_id = c_version_id
4569 AND business_group_id = c_bg_id;
4570 --=========================================================================
4571 -- Cursor to fetch ABP employer details
4572 CURSOR csr_get_er_details(c_org_id IN Number) IS
4573 SELECT lpad(org_information2,9,'0') er_num
4574 ,decode (org_information5
4575 ,'MONTH'
4576 ,'MND'
4577 ,'LMONTH'
4578 ,'VWK') freq
4579 ,substr (org_information6
4580 ,1
4581 ,10) ref_er
4582 FROM hr_organization_information
4583 WHERE org_information_context = 'PQP_ABP_PROVIDER'
4584 AND organization_id = c_org_id;
4585 --=========================================================================
4586 CURSOR csr_get_employer(p_org_id IN NUMBER) IS
4587 SELECT name
4588 FROM hr_organization_units
4589 WHERE organization_id = p_org_id;
4590 --=========================================================================
4591 CURSOR csr_get_PA_exception_info(p_payroll_action_id IN NUMBER) IS
4592 SELECT pai_p.action_information4 Message
4593 ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
4594 ,pai_p.action_information6 Description
4595 ,substr(pai_p.action_information7,1,30) E_Name
4596 ,pai_p.action_context_type cxt
4597 FROM pay_action_information pai_p
4598 WHERE pai_p.action_context_id = p_payroll_action_id
4599 AND pai_p.action_information_category = 'NL_ABP_EXCEPTION_REPORT'
4600 AND pai_p.action_context_type = 'PA';
4601 --=========================================================================
4602 CURSOR csr_get_AAP_exception_info(p_payroll_action_id IN NUMBER) IS
4603 SELECT pai_p.action_information4 Message
4604 ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
4605 ,pai_p.action_information6 Description
4606 ,substr(pai_p.action_information7,1,45) E_Name
4607 ,substr(pai_p.action_information8,1,30) E_Number
4608 ,pai_p.action_context_type cxt
4609 FROM pay_assignment_actions paa
4610 ,pay_action_information pai_p
4611 WHERE paa.payroll_action_id = p_payroll_action_id
4612 AND pai_p.action_context_id = paa.assignment_action_id
4613 AND pai_p.action_information_category = 'NL_ABP_EXCEPTION_REPORT'
4614 AND pai_p.action_context_type = 'AAP'
4615 ORDER BY pai_p.action_information8 asc;
4616 --=========================================================================
4617 -- Bug 14793405
4618 CURSOR csr_get_tech_exception_info(p_payroll_action_id IN number) IS
4619 SELECT pai_p.action_information4 message
4620 ,fnd_date.date_to_displaydate (fnd_date.canonical_to_date (pai_p.action_information5)) dt
4621 ,pai_p.action_information6 description
4622 ,substr (pai_p.action_information7, 1, 30) e_name
4623 ,pai_p.action_context_type cxt
4624 FROM pay_action_information pai_p
4625 WHERE pai_p.action_context_id = p_payroll_action_id
4626 AND pai_p.action_information_category = 'NL_ABP_TECH_EXCEPTION_REPORT';
4627 --=========================================================================
4628
4629 l_seq_num NUMBER;
4630 l_get_app_ver csr_app_version%ROWTYPE;
4631 l_sender_details csr_get_sender_details%ROWTYPE;
4632 l_org_struct_id NUMBER :=NULL;
4633 l_payroll_id NUMBER :=NULL;
4634 l_org_id NUMBER :=NULL;
4635 l_bg_id NUMBER;
4636 l_report_type VARCHAR2(10);
4637 l_start_date DATE :=NULL;
4638 l_end_date DATE :=NULL;
4639 l_action_info_id NUMBER;
4640 l_ovn NUMBER;
4641 l_er_num VARCHAR2(10);
4642 l_freq VARCHAR2(10);
4643 l_ref_er VARCHAR2(10);
4644 l_version_id per_org_structure_versions_v.org_structure_version_id%TYPE DEFAULT NULL;
4645 l_loop_again NUMBER;
4646 l_er_name hr_organization_units.name%TYPE;
4647 empr_flag VARCHAR2(1);
4648 empe_flag VARCHAR2(1);
4649 l_exception_flag VARCHAR2(1);
4650
4651 BEGIN
4652 fnd_file.put_line(fnd_file.log,'+====deinitialization_code====+ '|| p_payroll_action_id);
4653 --
4654 empr_flag := 'N';
4655 empe_flag := 'N';
4656 l_exception_flag := 'N';
4657 --
4658
4659 get_all_parameters (
4660 p_payroll_action_id => p_payroll_action_id
4661 ,p_org_struct_id => l_org_struct_id
4662 ,p_org_id => l_org_id
4663 ,p_report_type => l_report_type
4664 ,p_seq_num => l_seq_num
4665 ,p_bg_id => l_bg_id
4666 ,p_start_date => l_start_date
4667 ,p_end_date => l_end_date);
4668
4669 IF l_org_id IS NOT NULL THEN
4670 OPEN csr_get_employer(l_org_id);
4671 FETCH csr_get_employer INTO l_er_name;
4672 CLOSE csr_get_employer;
4673 END IF;
4674
4675 OPEN csr_get_sender_details(l_org_id,l_bg_id);
4676 FETCH csr_get_sender_details into l_sender_details;
4677 IF csr_get_sender_details%FOUND THEN
4678 IF l_sender_details.reg_num_sender IS NULL THEN
4679 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4680 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','ABP_SENDER'));
4681 pay_action_information_api.create_action_information
4682 (
4683 p_action_information_id => l_action_info_id
4684 , p_action_context_id => p_payroll_action_id
4685 , p_action_context_type => 'PA'
4686 , p_object_version_number => l_ovn
4687 , p_assignment_id => NULL
4688 , p_effective_date => l_end_date
4689 , p_source_id => NULL
4690 , p_source_text => NULL
4691 , p_tax_unit_id => l_org_id
4692 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4693 , p_action_information4 => fnd_message.get
4694 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4695 , p_action_information6 => 'ABP Registration Number Sender is null'
4696 , p_action_information7 => substr(l_er_name,1,200));
4697 END IF;
4698 IF l_sender_details.prod_test IS NULL THEN
4699 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4700 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','TEST_FLAG'));
4701 pay_action_information_api.create_action_information
4702 (
4703 p_action_information_id => l_action_info_id
4704 , p_action_context_id => p_payroll_action_id
4705 , p_action_context_type => 'PA'
4706 , p_object_version_number => l_ovn
4707 , p_assignment_id => NULL
4708 , p_effective_date => l_end_date
4709 , p_source_id => NULL
4710 , p_source_text => NULL
4711 , p_tax_unit_id => l_org_id
4712 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4713 , p_action_information4 => fnd_message.get
4714 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4715 , p_action_information6 => 'Test Environment Flag is null'
4716 , p_action_information7 => substr(l_er_name,1,200));
4717 END IF;
4718 ELSE
4719 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4720 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','ABP_SENDER'));
4721 pay_action_information_api.create_action_information
4722 (
4723 p_action_information_id => l_action_info_id
4724 , p_action_context_id => p_payroll_action_id
4725 , p_action_context_type => 'PA'
4726 , p_object_version_number => l_ovn
4727 , p_assignment_id => NULL
4728 , p_effective_date => l_end_date
4729 , p_source_id => NULL
4730 , p_source_text => NULL
4731 , p_tax_unit_id => l_org_id
4732 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4733 , p_action_information4 => fnd_message.get
4734 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4735 , p_action_information6 => 'ABP Registration Number Sender is null'
4736 , p_action_information7 => substr(l_er_name,1,200));
4737
4738 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4739 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','TEST_FLAG'));
4740 pay_action_information_api.create_action_information
4741 (
4742 p_action_information_id => l_action_info_id
4743 , p_action_context_id => p_payroll_action_id
4744 , p_action_context_type => 'PA'
4745 , p_object_version_number => l_ovn
4746 , p_assignment_id => NULL
4747 , p_effective_date => l_end_date
4748 , p_source_id => NULL
4749 , p_source_text => NULL
4750 , p_tax_unit_id => l_org_id
4751 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4752 , p_action_information4 => fnd_message.get
4753 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4754 , p_action_information6 => 'Test Environment Flag is null'
4755 , p_action_information7 => substr(l_er_name,1,200));
4756 END IF;
4757 CLOSE csr_get_sender_details;
4758
4759 OPEN csr_app_version;
4760 FETCH csr_app_version into l_get_app_ver;
4761 CLOSE csr_app_version;
4762
4763 pay_action_information_api.create_action_information
4764 (
4765 p_action_information_id => l_action_info_id
4766 , p_action_context_id => p_payroll_action_id
4767 , p_action_context_type => 'PA'
4768 , p_object_version_number => l_ovn
4769 , p_assignment_id => NULL
4770 , p_effective_date => l_end_date
4771 , p_source_id => NULL
4772 , p_source_text => NULL
4773 , p_action_information_category => 'NL_ABP_SENDER_INFO'
4774 , p_action_information1 => l_sender_details.reg_num_sender
4775 , p_action_information2 => fnd_date.date_to_canonical(l_start_date)
4776 , p_action_information3 => nvl(l_seq_num,1)
4777 , p_action_information4 => 'APG02.01.01'
4778 , p_action_information5 => to_char (systimestamp,'RRRR-MM-DD"T"HH24:MI:SSTZR')
4779 , p_action_information6 => l_report_type
4780 , p_action_information7 => l_sender_details.prod_test
4781 , p_action_information8 => l_sender_details.name_sender
4782 , p_action_information9 => l_get_app_ver.release_name
4783 , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
4784 );
4785
4786 OPEN csr_get_er_details(l_org_id);
4787 FETCH csr_get_er_details INTO l_er_num,l_freq,l_ref_er;
4788 IF csr_get_er_details%FOUND THEN
4789 CLOSE csr_get_er_details;
4790 --
4791 ELSE
4792 --
4793 CLOSE csr_get_er_details;
4794 -- Value not found at this org level,traverse up the
4795 -- org hierarchy to find a value at the parent level
4796 fnd_file.put_line(fnd_file.log,'....No value found at HR org level,searching up the tree');
4797
4798 l_version_id := pqp_pension_functions.get_version_id(l_bg_id,l_end_date);
4799
4800 fnd_file.put_line(fnd_file.log,' l_version_id '||l_version_id);
4801
4802 IF l_version_id = -99 THEN
4803 l_er_num := '000000000';
4804 END IF;
4805 -- Loop through the org hierarchy to find the values
4806 -- at this org level or its parents
4807 l_loop_again := 1;
4808 WHILE (l_loop_again = 1)
4809 LOOP
4810 -- Find the parent of this org
4811 OPEN csr_find_parent_id(l_org_id,l_version_id,l_bg_id);
4812 FETCH csr_find_parent_id INTO l_org_id;
4813 IF csr_find_parent_id%FOUND THEN
4814 fnd_file.put_line(fnd_file.log,'searching at parent : '||l_org_id);
4815 CLOSE csr_find_parent_id;
4816 OPEN csr_get_er_details(l_org_id);
4817 FETCH csr_get_er_details INTO l_er_num,l_freq,l_ref_er;
4818 IF csr_get_er_details%FOUND THEN
4819 CLOSE csr_get_er_details;
4820 l_loop_again := 0;
4821 ELSE
4822 CLOSE csr_get_er_details;
4823 END IF;
4824 ELSE
4825 -- No parent found, so return 0
4826 CLOSE csr_find_parent_id;
4827 l_er_num := '000000000';
4828 END IF;
4829 END LOOP;
4830 END IF;
4831
4832 IF l_er_num IS NULL THEN
4833 pay_action_information_api.create_action_information
4834 (
4835 p_action_information_id => l_action_info_id
4836 , p_action_context_id => p_payroll_action_id
4837 , p_action_context_type => 'PA'
4838 , p_object_version_number => l_ovn
4839 , p_assignment_id => NULL
4840 , p_effective_date => l_end_date
4841 , p_source_id => NULL
4842 , p_source_text => NULL
4843 , p_tax_unit_id => l_org_id
4844 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4845 , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TAX_NO')
4846 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4847 , p_action_information6 => 'Tax Registration Number is null'
4848 , p_action_information7 => substr(l_er_name,1,200)
4849 , p_action_information8 => substr(l_er_num,1,12));
4850 END IF;
4851
4852 IF l_freq IS NULL THEN
4853 fnd_message.set_name('PER','HR_6001_ALL_MANDATORY_FIELD');
4854 fnd_message.set_token('MISSING_FIELD',hr_general.decode_lookup('NL_FORM_LABELS','FREQUENCY'));
4855 pay_action_information_api.create_action_information
4856 (
4857 p_action_information_id => l_action_info_id
4858 , p_action_context_id => p_payroll_action_id
4859 , p_action_context_type => 'PA'
4860 , p_object_version_number => l_ovn
4861 , p_assignment_id => NULL
4862 , p_effective_date => l_end_date
4863 , p_source_id => NULL
4864 , p_source_text => NULL
4865 , p_tax_unit_id => l_org_id
4866 , p_action_information_category => 'NL_ABP_EXCEPTION_REPORT'
4867 , p_action_information4 => fnd_message.get
4868 , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
4869 , p_action_information6 => 'Frequency is null'
4870 , p_action_information7 => substr(l_er_name,1,200)
4871 , p_action_information8 => substr(l_er_num,1,12));
4872 END IF;
4873
4874 pay_action_information_api.create_action_information
4875 (
4876 p_action_information_id => l_action_info_id
4877 , p_action_context_id => p_payroll_action_id
4878 , p_action_context_type => 'PA'
4879 , p_object_version_number => l_ovn
4880 , p_assignment_id => NULL
4881 , p_effective_date => l_end_date
4882 , p_source_id => NULL
4883 , p_source_text => NULL
4884 , p_action_information_category => 'NL_ABP_EMPLOYER_INFO'
4885 , p_action_information1 => l_er_num
4886 , p_action_information2 => l_freq
4887 , p_action_information3 => fnd_date.date_to_canonical(l_start_date)
4888 , p_action_information4 => fnd_date.date_to_canonical(l_end_date)
4889 , p_action_information5 => l_ref_er
4890 , p_action_information6 => l_org_id
4891 );
4892
4893 --# Exception Report
4894
4895 -- Bug 14793405
4896 FOR csr_exception_rec IN csr_get_tech_exception_info (p_payroll_action_id) LOOP
4897
4898 fnd_file.put_line (fnd_file.output, csr_exception_rec.message);
4899
4900 END LOOP;
4901 -- 14793405
4902
4903 FND_FILE.PUT_LINE(fnd_file.output,rpad('-',80,'-') || rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION_REPORT'),20,' ') || rpad('-',80,'-'));
4904 FND_FILE.PUT_LINE(fnd_file.output,rpad(' ',180,' '));
4905 --
4906 FOR csr_exception_rec IN csr_get_PA_exception_info(p_payroll_action_id) LOOP
4907 --
4908 l_exception_flag := 'Y' ;
4909 IF empr_flag = 'N' THEN
4910 --
4911 FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4912 FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EMPLOYER_NAME'),32,' ') ||
4913 rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),148,' '));
4914 FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4915 empr_flag :='Y';
4916 --
4917 END IF;
4918 --
4919 FND_FILE.PUT_LINE(fnd_file.output,rpad(csr_exception_rec.E_Name,32,' ') || csr_exception_rec.Message);
4920 --
4921
4922 END LOOP;
4923 --
4924 FOR csr_exception_rec IN csr_get_AAP_exception_info(p_payroll_action_id) LOOP
4925 --
4926 l_exception_flag := 'Y' ;
4927 --
4928 IF empe_flag = 'N' THEN
4929 --
4930 FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4931 FND_FILE.PUT_LINE(fnd_file.output,rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_ASSIGNMENT_NUMBER'),22,' ') ||
4932 rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_NAME'),45,' ') ||
4933 rpad(hr_general.decode_lookup('NL_FORM_LABELS','WR_EXCEPTION'),113,' '));
4934 FND_FILE.PUT_LINE(fnd_file.output,rpad('-',180,'-'));
4935 empe_flag := 'Y';
4936 END IF;
4937 --
4938 FND_FILE.PUT_LINE(fnd_file.output,rpad(substr(csr_exception_rec.E_Number,1,20),22,' ') || rpad(csr_exception_rec.E_name,45,' ') || csr_exception_rec.Message);
4939 --
4940 END LOOP;
4941 --
4942 IF l_exception_flag = 'N' THEN
4943 FND_FILE.PUT_LINE(fnd_file.output , hr_general.decode_lookup('NL_FORM_LABELS','WR_NO_VALIDATION_ERRORS'));
4944 END IF;
4945
4946 fnd_file.put_line(fnd_file.log,'X====deinitialization_code============X '||p_payroll_action_id);
4947
4948 EXCEPTION
4949 WHEN OTHERS THEN
4950 fnd_file.put_line(fnd_file.log,'deinitialization_code :'||SQLERRM);
4951 log_tech_exception(p_action_context_id => p_payroll_action_id,
4952 p_err_msg => 'deinitialization_code : '|| substr (sqlerrm, 1, 180)); --14793405
4953
4954 END deinitialization_code;
4955
4956 END PAY_NL_ABP_ARCHIVE;
4957
4958