[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_in_payslip_archive AS
2 /* $Header: pyinparc.pkb 120.43.12020000.7 2013/02/06 09:24:05 pthummal ship $ */
3
4 ----------------------------------------------------------------------+
5 -- This is a global variable used to store Archive assignment action id
6 ----------------------------------------------------------------------+
7
8 g_archive_pact NUMBER;
9 g_package CONSTANT VARCHAR2(100) := 'pay_in_payslip_archive.';
10 g_debug BOOLEAN;
11 TYPE pf_org IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
12 TYPE pt_state IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
13
14 g_pf_org_id pf_org;
15 g_pa_act_id pf_org;
16 g_cnt_pf NUMBER;
17
18 g_esi_org_id pf_org;
19 g_esi_act_id pf_org;
20 g_cnt_esi NUMBER;
21
22 g_pt_org_id pf_org;
23 g_pt_act_id pf_org;
24 g_pt_jur_code pt_state;
25 g_cnt_pt NUMBER;
26
27 --------------------------------------------------------------------------
28 -- --
29 -- Name : RANGE_CODE --
30 -- Type : PROCEDURE --
31 -- Access : Public --
32 -- Description : This procedure returns a sql string to select a --
33 -- range of assignments eligible for archival. --
34 -- It calls pay_apac_payslip_archive.range_code that --
35 -- archives the EIT definition and payroll level data --
36 -- (Messages, employer address details etc) --
37 -- --
38 -- Parameters : --
39 -- IN : p_payroll_action_id NUMBER --
40 -- OUT : p_sql VARCHAR2 --
41 -- --
42 -- Change History : --
43 --------------------------------------------------------------------------
44 -- Rev# Date Userid Description --
45 --------------------------------------------------------------------------
46 -- 115.0 04-NOV-2004 bramajey Initial Version --
47 --------------------------------------------------------------------------
48 --
49
50 PROCEDURE range_code(
51 p_payroll_action_id IN NUMBER
52 ,p_sql OUT NOCOPY VARCHAR2
53 )
54 IS
55 --
56
57 l_procedure VARCHAR2(100);
58 l_message VARCHAR2(255);
59 --
60 BEGIN
61 --
62
63 l_procedure := g_package || 'range_code';
64 g_debug := hr_utility.debug_enabled;
65 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
66
67 --------------------------------------------------------------------------------+
68 -- Call to range_code from common apac package 'pay_apac_payslip_archive'
69 -- to archive the payroll action level data and EIT defintions.
70 --------------------------------------------------------------------------------+
71
72 pay_apac_payslip_archive.range_code
73 (
74 p_payroll_action_id => p_payroll_action_id
75 );
76
77 -- Call core package to return SQL string to SELECT a range
78 -- of assignments eligible for archival
79 --
80 pay_in_utils.set_location(g_debug,l_procedure,20);
81
82 pay_core_payslip_utils.range_cursor(p_payroll_action_id
83 ,p_sql);
84
85 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
86 --
87 EXCEPTION
88 WHEN OTHERS THEN
89 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
90 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
91 pay_in_utils.trace(l_message,l_procedure);
92 RAISE;
93 --
94 END range_code;
95
96
97 /*--------------------------------------------------------------------------
98 -- Name : GET_PARAMETER --
99 -- Type : FUNCTION --
100 -- Access : Public --
101 -- Description : This function returns the payroll_id for the --
102 -- payroll_action --
103 -- Parameters : --
104 -- IN : p_name VARCHAR2 --
105 p_leg_parameters VARCHAR2 --
106 -- Returns: VARCHAR2 --
107 -------------------------------------------------------------------------- */
108
109 FUNCTION get_parameter
110 (
111 p_name IN VARCHAR2,
112 p_leg_parameters IN VARCHAR2
113 ) RETURN VARCHAR2 IS
114
115 start_ptr NUMBER;
116 end_ptr NUMBER;
117 token_val pay_payroll_actions.legislative_parameters%TYPE;
118 par_value pay_payroll_actions.legislative_parameters%TYPE;
119
120 BEGIN
121
122 token_val := p_name || '=';
123
124 start_ptr := instr(p_leg_parameters, token_val) + length(token_val);
125 end_ptr := instr(p_leg_parameters, ' ', start_ptr);
126
127 /* if there is no spaces, then use the length of the string */
128 IF end_ptr = 0 THEN
129 end_ptr := length(p_leg_parameters) + 1;
130 END IF;
131
132 IF instr(p_leg_parameters, token_val) = 0 THEN
133 par_value := NULL;
134 ELSE
135 par_value := substr(p_leg_parameters, start_ptr, end_ptr - start_ptr);
136 END IF;
137
138 RETURN par_value;
139
140 END get_parameter;
141
142 --------------------------------------------------------------------------
143 -- --
144 -- Name : INITIALIZATION_CODE --
145 -- Type : PROCEDURE --
146 -- Access : Public --
147 -- Description : This procedure is used to set global contexts. --
148 -- The globals used are PL/SQL tables --
149 -- i.e.(g_user_balance_table and g_element_table) --
150 -- It calls the procedure --
151 -- pay_apac_archive.initialization_code that actially --
152 -- sets the global variables and populates the global --
153 -- tables. --
154 -- --
155 -- Parameters : --
156 -- IN : p_payroll_action_id NUMBER --
157 -- OUT : N/A --
158 -- --
159 -- Change History : --
160 --------------------------------------------------------------------------
161 -- Rev# Date Userid Description --
162 --------------------------------------------------------------------------
163 -- 115.0 04-NOV-2004 bramajey Initial Version --
164 --------------------------------------------------------------------------
165 --
166
167
168 PROCEDURE initialization_code (
169 p_payroll_action_id IN NUMBER
170 )
171 IS
172 --
173 l_procedure VARCHAR2(100) ;
174 l_message VARCHAR2(255);
175 --
176 l_payroll_id NUMBER;
177 leg_param pay_payroll_actions.legislative_parameters%TYPE;
178 l_ppa_payroll_id pay_payroll_actions.payroll_id%TYPE;
179 l_pactid pay_payroll_actions.payroll_action_id%TYPE;
180 --
181 BEGIN
182 --
183 l_procedure := g_package || 'initialization_code';
184
185 g_debug := hr_utility.debug_enabled;
186 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
187
188 --------------------------------------------------------------------------
189 -- Code to update the payroll_id in the pay_payroll_actions tabel.
190 --------------------------------------------------------------------------
191
192 SELECT legislative_parameters,payroll_id
193 INTO leg_param,l_ppa_payroll_id
194 FROM pay_payroll_actions
195 WHERE payroll_action_id = p_payroll_action_id ;
196
197 l_payroll_id := get_parameter('PAYROLL', leg_param);
198
199 -- Update the Payroll Action with the Payroll ID
200
201 IF l_ppa_payroll_id IS NULL THEN
202
203 UPDATE pay_payroll_actions
204 SET payroll_id = l_payroll_id
205 WHERE payroll_action_id = p_payroll_action_id;
206
207 END IF;
208
209 g_archive_pact := p_payroll_action_id;
210
211 ------------------------------------------------------------------+
212 -- Call to common package procedure pay_apac_payslip_archive.
213 -- initialization_code to to set the global tables for EIT
214 -- that will be used by each thread in multi-threading.
215 ------------------------------------------------------------------+
216
217 pay_apac_payslip_archive.initialization_code(
218 p_payroll_action_id => p_payroll_action_id
219 );
220
221 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
222 --
223 EXCEPTION
224 WHEN OTHERS THEN
225 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
226 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
227 pay_in_utils.trace(l_message,l_procedure);
228 RAISE;
229 --
230 END initialization_code;
231
232 --------------------------------------------------------------------------
233 -- --
234 -- Name : GET_PARAMETERS --
235 -- Type : PROCEDURE --
236 -- Access : Public --
237 -- Description : This procedure determines the globals applicable --
238 -- through out the tenure of the process --
239 -- Parameters : --
240 -- IN : --
241 -- OUT : N/A --
242 -- --
243 -- Change History : --
244 --------------------------------------------------------------------------
245 -- Rev# Date Userid Description --
246 --------------------------------------------------------------------------
247 -- 115.0 14-Feb-2006 lnagaraj Initial Version --
248 --------------------------------------------------------------------------
249
250 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
251 p_token_name IN VARCHAR2,
252 p_token_value OUT NOCOPY VARCHAR2) IS
253
254 CURSOR csr_parameter_info(p_pact_id NUMBER,
255 p_token CHAR) IS
256 SELECT SUBSTR(legislative_parameters,
257 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
258 INSTR(legislative_parameters,' ',
259 INSTR(legislative_parameters,p_token))
260 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
261 business_group_id
262 FROM pay_payroll_actions
263 WHERE payroll_action_id = p_pact_id;
264
265 l_business_group_id VARCHAR2(20);
266 l_token_value VARCHAR2(50);
267
268 l_procedure VARCHAR2(50);
269
270 BEGIN
271
272 l_procedure := g_package || 'get_parameters';
273
274 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
275
276
277 OPEN csr_parameter_info(p_payroll_action_id,
278 p_token_name);
279 FETCH csr_parameter_info INTO l_token_value,
280 l_business_group_id;
281 CLOSE csr_parameter_info;
282
283 IF p_token_name = 'BG_ID'
284 THEN
285 p_token_value := l_business_group_id;
286 ELSE
287 p_token_value := l_token_value;
288 END IF;
289
290 IF g_debug THEN
291 pay_in_utils.trace('Token Name ',p_token_name);
292 pay_in_utils.trace('Token Value ',p_token_value);
293 END IF;
294
295 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
296
297
298 END get_parameters;
299
300
301 --------------------------------------------------------------------------
302 -- --
303 -- Name : ASSIGNMENT_ACTION_CODE --
304 -- Type : PROCEDURE --
305 -- Access : Public --
306 -- Description : This procedure further restricts the assignment_id's--
307 -- returned by range_code. --
308 -- It filters the assignments selected by range_code --
309 -- procedure. --
310 -- Since the Payslip is given for each prepayment,the --
311 -- data should be archived for each prepayment. --
312 -- So,the successfully completed prepayments are --
313 -- selected and locked by the archival action --
314 -- All the successfully completed prepayments are --
315 -- selected and locked by archival to make the core --
316 -- 'Choose Payslip' work for IN. --
317 -- The archive will not pickup already archived --
318 -- prepayments --
319 -- --
320 -- Parameters : --
321 -- IN : p_payroll_action_id NUMBER --
322 -- p_start_person NUMBER --
323 -- p_end_person NUMBER --
324 -- p_chunk NUMBER --
325 -- OUT : N/A --
326 -- --
327 -- Change History : --
328 --------------------------------------------------------------------------
329 -- Rev# Date Userid Description --
330 --------------------------------------------------------------------------
331 -- 115.0 04-NOV-2004 bramajey Initial Version --
332 --------------------------------------------------------------------------
333 --
334
335 PROCEDURE assignment_action_code (
336 p_payroll_action_id IN NUMBER
337 ,p_start_person IN NUMBER
338 ,p_end_person IN NUMBER
339 ,p_chunk IN NUMBER
340 )
341 IS
342 --
343 l_procedure VARCHAR2(100);
344 l_actid NUMBER;
345 l_payroll_id NUMBER;
346 l_bg_id NUMBER;
347 l_end_date VARCHAR2(20);
348 l_start_date VARCHAR2(20);
349 l_consolidation_set VARCHAR2(30);
350 l_canonical_end_date DATE;
351 l_canonical_start_date DATE;
352 l_message VARCHAR2(255);
353
354 CURSOR csr_bal_init(p_payroll_id NUMBER,
355 p_start_date DATE,
356 p_end_date DATE,
357 p_consolidation_set_id NUMBER) IS
358 SELECT paa_init.assignment_id,
359 paa_init.assignment_action_id
360 FROM pay_assignment_actions paa_init,
361 pay_payroll_actions ppa_init,
362 per_all_assignments_f paf
363 WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
364 AND ppa_init.action_type='I'
365 AND ppa_init.business_group_id = l_bg_id
366 AND paf.business_group_id = l_bg_id
367 AND (paf.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
368 AND ppa_init.consolidation_set_id = p_consolidation_set_id
369 AND paf.person_id BETWEEN
370 p_start_person AND p_end_person
371 AND paf.assignment_id = paa_init.assignment_id
372 AND ppa_init.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
373 AND ppa_init.effective_date BETWEEN p_start_date AND p_end_date
374 AND paa_init.action_sequence = pay_in_utils.get_max_act_sequence(paa_init.assignment_id
375 ,'I'
376 ,ppa_init.effective_date
377 )
378 AND NOT EXISTS (SELECT NULL
379 FROM pay_assignment_actions paa_arch
380 ,pay_payroll_actions ppa_arch
381 ,pay_action_interlocks intk
382 WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
383 AND intk.locked_action_id = paa_init.assignment_action_id
384 AND intk.locking_action_id = paa_arch.assignment_action_id
385 AND paf.assignment_id = paa_arch.assignment_id
386 AND ppa_arch.action_type = 'X'
387 AND ppa_arch.report_type ='IN_PAYSLIP_ARCHIVE'
388 AND ppa_arch.report_qualifier='IN')
389 ORDER BY paa_init.assignment_id,paa_init.assignment_action_id;
390 --
391 BEGIN
392 --
393
394 l_procedure := g_package || 'assignment_action_code';
395
396 g_debug := hr_utility.debug_enabled;
397
398 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
399
400 get_parameters( p_payroll_action_id => p_payroll_action_id
401 , p_token_name => 'PAYROLL'
402 , p_token_value => l_payroll_id);
403
404 get_parameters( p_payroll_action_id => p_payroll_action_id
405 , p_token_name => 'BG_ID'
406 , p_token_value => l_bg_id);
407
408 get_parameters ( p_payroll_action_id => p_payroll_action_id
409 , p_token_name => 'START_DATE'
410 , p_token_value => l_start_date);
411
412 get_parameters ( p_payroll_action_id => p_payroll_action_id
413 , p_token_name => 'END_DATE'
414 , p_token_value => l_end_date);
415
416 get_parameters ( p_payroll_action_id => p_payroll_action_id
417 , p_token_name => 'CONSOLIDATION'
418 , p_token_value => l_consolidation_set);
419
420 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
421 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
422
423 pay_in_utils.set_location(g_debug,l_procedure,20);
424 -- Call core package to create assignment actions
425 pay_core_payslip_utils.action_creation (
426 p_payroll_action_id
427 ,p_start_person
428 ,p_end_person
429 ,p_chunk
430 ,'IN_PAYSLIP_ARCHIVE'
431 ,'IN');
432 pay_in_utils.set_location(g_debug,l_procedure,30);
433
434 IF g_debug THEN
435 pay_in_utils.trace('Canonical Start and End Date ',l_canonical_start_date||' '||l_canonical_end_date );
436 END IF;
437
438 FOR i in csr_bal_init (l_payroll_id,l_canonical_start_date,l_canonical_end_date,l_consolidation_set)
439 LOOP
440 SELECT pay_assignment_actions_s.NEXTVAL
441 INTO l_actid
442 FROM dual;
443
444 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE 'I' ASSIGNMENT ACTION
445
446 hr_nonrun_asact.insact(l_actid,i.assignment_id,p_payroll_action_id,p_chunk,NULL);
447
448 -- CREATE THE ARCHIVE ACTION TO 'I' interlock
449 hr_nonrun_asact.insint(l_actid,i.assignment_action_id);
450
451 END LOOP;
452 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
453 --
454 EXCEPTION
455 --
456 WHEN OTHERS THEN
457 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
458 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
459 pay_in_utils.trace(l_message,l_procedure);
460 RAISE;
461 --
462 END assignment_action_code;
463
464 --------------------------------------------------------------------------
465 -- --
466 -- Name : ARCHIVE_ACCRUAL_DETAILS --
467 -- Type : PROCEDURE --
468 -- Access : Private --
469 -- Description : This procedure is used to archive accrual details --
470 -- for a given assignment_action_id. --
471 -- It calls per_accrual_calc_functions.get_net_accrual --
472 -- to get the net_accrual for the given assignment_id --
473 -- --
474 -- Parameters : --
475 -- IN : p_payroll_action_id NUMBER --
476 -- p_time_period_id NUMBER --
477 -- p_assignment_id NUMBER --
478 -- p_date_earned DATE --
479 -- p_effective_date DATE --
480 -- p_assact_id NUMBER --
481 -- p_assignment_action_id NUMBER --
482 -- p_period_end_date DATE --
483 -- p_period_start_date DATE --
484 -- --
485 -- OUT : N/A --
486 -- --
487 -- Change History : --
488 --------------------------------------------------------------------------
489 -- Rev# Date Userid Description --
490 --------------------------------------------------------------------------
491 -- 115.0 04-NOV-2004 bramajey Initial Version --
492 --------------------------------------------------------------------------
493 --
494
495 PROCEDURE archive_accrual_details (
496 p_payroll_action_id IN NUMBER
497 ,p_time_period_id IN NUMBER
498 ,p_assignment_id IN NUMBER
499 ,p_date_earned IN DATE
500 ,p_effective_date IN DATE
501 ,p_assact_id IN NUMBER
502 ,p_assignment_action_id IN NUMBER
503 ,p_period_end_date IN DATE
504 ,p_period_start_date IN DATE
505 )
506 IS
507 --
508
509 -- Cursor to get the Leave Balance Details .
510
511 CURSOR csr_leave_balance
512 IS
513 --
514 SELECT pap.accrual_plan_name accrual_plan_name
515 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category) accrual_category
516 ,pap.accrual_units_of_measure accrual_uom
517 ,ppa.payroll_id payroll_id
518 ,pap.business_group_id business_group_id
519 ,pap.accrual_plan_id accrual_plan_id
520 FROM pay_accrual_plans pap
521 ,pay_element_types_f pet
522 ,pay_element_links_f pel
523 ,pay_element_entries_f pee
524 ,pay_assignment_actions paa
525 ,pay_payroll_actions ppa
526 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
527 AND pel.element_type_id = pet.element_type_id
528 AND pee.element_link_id = pel.element_link_id
529 AND paa.assignment_id = pee.assignment_id
530 AND ppa.payroll_action_id = paa.payroll_action_id
531 AND ppa.action_type IN ('R','Q')
532 AND ppa.action_status = 'C'
533 AND ppa.date_earned BETWEEN pet.effective_start_date
534 AND pet.effective_end_date
535 AND ppa.date_earned BETWEEN pel.effective_start_date
536 AND pel.effective_end_date
537 AND ppa.date_earned BETWEEN pee.effective_start_date
538 AND pee.effective_end_date
539 AND paa.assignment_id = p_assignment_id
540 AND paa.assignment_action_id = p_assignment_action_id;
541 --
542
543 l_action_info_id NUMBER;
544 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
545 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
546 l_accrual_category pay_accrual_plans.accrual_category%type;
547 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
548 l_payroll_id pay_payrolls_f.payroll_id%type;
549 l_business_group_id NUMBER;
550 l_effective_date DATE;
551 l_annual_leave_balance NUMBER;
552 l_ovn NUMBER;
553 l_leave_taken NUMBER;
554 l_start_date DATE;
555 l_end_date DATE;
556 l_accrual_end_date DATE;
557 l_accrual NUMBER;
558 l_total_leave_taken NUMBER;
559 l_procedure VARCHAR2(100);
560 l_message VARCHAR2(255);
561 --
562 BEGIN
563 --
564
565 l_procedure := g_package || 'archive_accrual_details';
566 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
567
568 IF g_debug THEN
569 pay_in_utils.trace('**************************************************','********************');
570 pay_in_utils.trace('Payroll Action id ',p_payroll_action_id);
571 pay_in_utils.trace('Time Period id ',p_time_period_id);
572 pay_in_utils.trace('Assignment id ',p_assignment_id);
573 pay_in_utils.trace('Date Earned ',p_date_earned);
574 pay_in_utils.trace('Action Context id ',p_assact_id);
575 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
576 pay_in_utils.trace('Period End Date ',p_period_end_date);
577 pay_in_utils.trace('Period Start Date ',p_period_start_date);
578 pay_in_utils.trace('**************************************************','********************');
579 END IF;
580
581
582 FOR rec IN csr_leave_balance
583 LOOP
584 --
585 -- Call to get annual leave balance
586
587 pay_in_utils.set_location(g_debug,l_procedure, 20);
588
589 per_accrual_calc_functions.get_net_accrual
590 (
591 p_assignment_id => p_assignment_id -- number in
592 ,p_plan_id => rec.accrual_plan_id -- number in
593 ,p_payroll_id => rec.payroll_id -- number in
594 ,p_business_group_id => rec.business_group_id -- number in
595 ,p_calculation_date => p_date_earned -- date in
596 ,p_start_date => l_start_date -- date out
597 ,p_end_date => l_end_date -- date out
598 ,p_accrual_end_date => l_accrual_end_date -- date out
599 ,p_accrual => l_accrual -- number out
600 ,p_net_entitlement => l_annual_leave_balance -- number out
601 );
602
603
604 IF l_annual_leave_balance IS NULL THEN
605 --
606 l_annual_leave_balance := 0;
607 --
608 END IF;
609
610
611 pay_in_utils.set_location(g_debug,l_procedure, 30);
612
613 l_leave_taken := per_accrual_calc_functions.get_absence
614 (
615 p_assignment_id
616 ,rec.accrual_plan_id
617 ,p_period_end_date
618 ,p_period_start_date
619 );
620 l_ovn :=1;
621
622 IF rec.accrual_plan_name IS NOT NULL THEN
623 --
624
625 pay_action_information_api.create_action_information
626 (
627 p_action_information_id => l_action_info_id
628 ,p_action_context_id => p_assact_id
629 ,p_action_context_type => 'AAP'
630 ,p_object_version_number => l_ovn
631 ,p_effective_date => p_effective_date
632 ,p_source_id => NULL
633 ,p_source_text => NULL
634 ,p_action_information_category => 'APAC ACCRUALS'
635 ,p_action_information1 => rec.accrual_plan_name
636 ,p_action_information2 => rec.accrual_category
637 ,p_action_information4 => fnd_number.number_to_canonical(l_annual_leave_balance)
638 ,p_action_information5 => rec.accrual_uom
639 );
640 IF g_debug THEN
641 pay_in_utils.trace('**************************************************','********************');
642 pay_in_utils.trace('Accrual Plan Name ',rec.accrual_plan_name);
643 pay_in_utils.trace('Accrual Category ',rec.accrual_category);
644 pay_in_utils.trace('Annual Leave Balance ',fnd_number.number_to_canonical(l_annual_leave_balance));
645 pay_in_utils.trace('Accrual UOM ',rec.accrual_uom);
646 pay_in_utils.trace('**************************************************','********************');
647 END IF;
648 --
649 END IF;
650 --
651 --
652 END LOOP;
653 --
654
655 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
656
657 --
658 EXCEPTION
659 WHEN OTHERS THEN
660 IF csr_leave_balance%ISOPEN THEN
661 --
662 CLOSE csr_leave_balance;
663 --
664 END IF;
665 --
666 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
667 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
668 pay_in_utils.trace(l_message,l_procedure);
669
670 RAISE;
671 --
672 END archive_accrual_details;
673
674 --------------------------------------------------------------------------
675 -- --
676 -- Name : ARCHIVE_ABSENCES --
677 -- Type : PROCEDURE --
678 -- Access : Private --
679 -- Description : This procedure archives Absences for the employee --
680 -- based on Payroll Assignment_action_id --
681 -- --
682 -- Parameters : --
683 -- IN : p_arch_action_id NUMBER --
684 -- p_assg_act_id NUMBER --
685 -- p_pre_effective_date DATE --
686 -- --
687 -- OUT : N/A --
688 -- --
689 -- Change History : --
690 --------------------------------------------------------------------------
691 -- Rev# Date Userid Description --
692 --------------------------------------------------------------------------
693 -- 115.0 04-NOV-2004 bramajey Initial Version --
694 --------------------------------------------------------------------------
695
696
697 PROCEDURE archive_absences (
698 p_arch_act_id IN NUMBER
699 ,p_assg_act_id IN NUMBER
700 ,p_pre_effective_date IN DATE
701 )
702 --
703 IS
704 --
705 -- Cursor to fetch absence details for the Assignment
706 --
707 CURSOR csr_asg_absences
708 IS
709 --
710 SELECT pat.name absence_type
711 ,pet.reporting_name reporting_name
712 ,decode(pet.processing_type,'R',greatest(pab.date_start,PTP.START_DATE),pab.date_start) start_date
713 ,decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) end_date
714 ,decode(pet.processing_type,'R',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours)) absence_days
715 FROM pay_assignment_actions paa
716 ,pay_payroll_actions ppa
717 ,pay_run_results prr
718 ,pay_run_result_values prrv
719 ,per_time_periods ptp
720 ,pay_element_types_f pet
721 ,pay_input_values_f piv
722 ,pay_element_entries_f pee
723 ,per_absence_attendance_types pat
724 ,per_absence_attendances pab
725 WHERE paa.assignment_action_id = p_assg_act_id
726 AND ppa.payroll_action_id = paa.payroll_action_id
727 AND ppa.action_type IN ('Q','R')
728 AND ptp.time_period_id = ppa.time_period_id
729 AND paa.assignment_action_id = prr.assignment_action_id
730 AND pet.element_type_id = prr.element_type_id
731 AND pet.element_type_id = piv.element_type_id
732 AND piv.input_value_id = pat.input_value_id
733 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
734 AND pab.absence_attendance_id = pee.creator_id
735 AND pee.creator_type = 'A'
736 AND pee.assignment_id = paa.assignment_id
737 AND pee.element_entry_id = prr.source_id
738 AND piv.input_value_id = prrv.input_value_id
739 AND prr.run_result_id = prrv.run_result_id
740 AND ppa.effective_date BETWEEN pet.effective_start_date
741 AND pet.effective_end_date
742 AND ppa.effective_date BETWEEN pee.effective_start_date
743 AND pee.effective_end_date
744 AND ppa.effective_date BETWEEN piv.effective_start_date
745 AND piv.effective_end_date;
746
747 l_procedure VARCHAR2(200);
748 l_start_date VARCHAR2(20);
749 l_end_date VARCHAR2(20);
750 l_ovn NUMBER;
751 l_action_info_id NUMBER;
752 l_message VARCHAR2(255);
753 --
754 --
755 BEGIN
756 --
757 l_procedure := g_package || 'archive_absences';
758
759 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
760
761 IF g_debug THEN
762 pay_in_utils.trace('Archive Action ID ',p_arch_act_id);
763 pay_in_utils.trace('Assignment Action ID ',p_assg_act_id);
764 pay_in_utils.trace('Effective Date ',p_pre_effective_date);
765 END IF;
766
767 --
768 FOR csr_rec in csr_asg_absences
769 LOOP
770 --
771 IF g_debug THEN
772 pay_in_utils.trace('Absence Type ',csr_rec.absence_type);
773 pay_in_utils.trace('Element Reporting Name ',csr_rec.reporting_name);
774 pay_in_utils.trace('Start Date ',to_char(csr_rec.start_date,'DD-MON-YYYY'));
775 pay_in_utils.trace('End Date ',to_char(csr_rec.end_date,'DD-MON-YYYY'));
776 pay_in_utils.trace('Absence Days ',csr_rec.absence_days);
777 END IF;
778
779 pay_in_utils.set_location(g_debug,l_procedure, 20);
780
781 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date);
782 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
783
784 l_ovn := 1;
785
786 pay_action_information_api.create_action_information
787 (
788 p_action_information_id => l_action_info_id
789 ,p_action_context_id => p_arch_act_id
790 ,p_action_context_type => 'AAP'
791 ,p_object_version_number => l_ovn
792 ,p_effective_date => p_pre_effective_date
793 ,p_source_id => NULL
794 ,p_source_text => NULL
795 ,p_action_information_category => 'APAC ABSENCES'
796 ,p_action_information1 => csr_rec.absence_type
797 ,p_action_information2 => csr_rec.reporting_name
798 ,p_action_information3 => NULL
799 ,p_action_information4 => l_start_date
800 ,p_action_information5 => l_end_date
801 ,p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_days) -- Bug 3604206
802 ,p_action_information7 => NULL
803 );
804 --
805 END LOOP;
806 --
807 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
808 --
809 EXCEPTION
810 --
811 WHEN others THEN
812 IF csr_asg_absences%ISOPEN THEN
813 CLOSE csr_asg_absences;
814 END IF;
815 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
816 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
817 pay_in_utils.trace(l_message,l_procedure);
818 RAISE;
819 --
820 END archive_absences;
821 --
822
823
824 --------------------------------------------------------------------------
825 -- --
826 -- Name : ARCHIVE_STAT_ELEMENTS --
827 -- Type : PROCEDURE --
828 -- Access : Private --
829 -- Description : This procedure archives the elements and --
830 -- run result values. It uses view --
831 -- PAY_in_ASG_ELEMENTS_V to get the elements and --
832 -- correspoding payments. --
833 -- --
834 -- Parameters : --
835 -- IN : p_assignment_action_id NUMBER --
836 -- p_effective_date DATE --
837 -- p_assact_id NUMBER --
838 -- --
839 -- OUT : N/A --
840 -- --
841 -- Change History : --
842 --------------------------------------------------------------------------
843 -- Rev# Date Userid Description --
844 --------------------------------------------------------------------------
845 -- 115.0 04-NOV-2004 bramajey Initial Version --
846 -- 115.1 03-May-2006 lnagaraj Archived 'Employer Excess PF --
847 -- Contribution' under Employer Charges --
848 --------------------------------------------------------------------------
849 --
850
851 PROCEDURE archive_stat_elements(
852 p_assignment_action_id IN NUMBER
853 ,p_effective_date IN DATE
854 ,p_assact_id IN NUMBER
855 )
856 IS
857 --
858 -- Cursor to get all the elements processed for the assignment in the
859 -- prepayment.
860
861 CURSOR csr_std_elements
862 IS
863 SELECT element_reporting_name
864 ,classification_name
865 ,amount
866 ,foreign_currency_code
867 ,exchange_rate
868 FROM pay_in_asg_elements_v
869 WHERE assignment_action_id = p_assignment_action_id;
870
871
872 --
873
874 l_action_info_id NUMBER;
875 l_ovn NUMBER;
876 l_foreign_currency_amount NUMBER;
877 l_rate NUMBER;
878 l_procedure VARCHAR2(100);
879 l_message VARCHAR2(255);
880 l_def_bal_id NUMBER;
881 l_excess_pf NUMBER;
882 l_no_value_archived VARCHAR2(255);
883 --
884 --
885 BEGIN
886 --
887
888 l_procedure := g_package ||'archive_stat_elements';
889 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
890
891 IF g_debug THEN
892 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
893 pay_in_utils.trace('Effective Date ',p_effective_date);
894 pay_in_utils.trace('Assact ID ',p_assact_id);
895 END IF;
896
897
898 FOR csr_rec IN csr_std_elements
899 LOOP
900 --
901 pay_in_utils.set_location(g_debug,l_procedure, 20);
902
903 IF nvl(csr_rec.exchange_rate,0) <> 0 THEN
904 l_foreign_currency_amount := csr_rec.amount / csr_rec.exchange_rate;
905 ELSE
906 l_foreign_currency_amount := NULL;
907 END IF;
908
909 IF ( csr_rec.amount IS NOT NULL) THEN
910 IF ((csr_rec.classification_name IN ('Advances','Fringe Benefits')) AND csr_rec.amount = 0) THEN
911 ---Do not archive any value--
912 l_no_value_archived :='Yes';
913 ELSE
914 pay_in_utils.set_location(g_debug,l_procedure, 30);
915
916 pay_action_information_api.create_action_information
917 (
918 p_action_information_id => l_action_info_id
919 ,p_action_context_id => p_assact_id
920 ,p_action_context_type => 'AAP'
921 ,p_object_version_number => l_ovn
922 ,p_effective_date => p_effective_date
923 ,p_source_id => NULL
924 ,p_source_text => NULL
925 ,p_action_information_category => 'APAC ELEMENTS'
926 ,p_action_information1 => csr_rec.element_reporting_name
927 ,p_action_information2 => NULL
928 ,p_action_information3 => NULL
929 ,p_action_information4 => csr_rec.classification_name
930 ,p_action_information5 => fnd_number.number_to_canonical(csr_rec.amount)
931 ,p_action_information10 => fnd_number.number_to_canonical(csr_rec.exchange_rate)
932 ,p_action_information11 => fnd_number.number_to_canonical(l_foreign_currency_amount)
933 ,p_action_information12 => csr_rec.foreign_currency_code
934 );
935 IF g_debug THEN
936 pay_in_utils.trace('Element Name ',csr_rec.element_reporting_name);
937 pay_in_utils.trace('Amount ',fnd_number.number_to_canonical(csr_rec.amount));
938 END IF;
939 END IF;
940
941 --
942 END IF;
943 --
944 --
945 END LOOP;
946
947 l_def_bal_id := pay_in_tax_utils.get_defined_balance('Excess Interest Amount','_ASG_DE_PTD');
948 l_excess_pf := pay_balance_pkg.get_value(l_def_bal_id,p_assignment_action_id);
949
950 l_def_bal_id := pay_in_tax_utils.get_defined_balance('Excess PF Amount','_ASG_DE_PTD');
951 l_excess_pf := l_excess_pf + pay_balance_pkg.get_value(l_def_bal_id,p_assignment_action_id);
952
953
954
955 IF l_excess_pf <> 0 THEN
956 pay_action_information_api.create_action_information
957 (
958 p_action_information_id => l_action_info_id
959 ,p_action_context_id => p_assact_id
960 ,p_action_context_type => 'AAP'
961 ,p_object_version_number => l_ovn
962 ,p_effective_date => p_effective_date
963 ,p_source_id => NULL
964 ,p_source_text => NULL
965 ,p_action_information_category => 'APAC ELEMENTS'
966 ,p_action_information1 => 'Employer Excess PF Contribution'
967 ,p_action_information2 => NULL
968 ,p_action_information3 => NULL
969 ,p_action_information4 => 'Employer Charges'
970 ,p_action_information5 => fnd_number.number_to_canonical(l_excess_pf)
971 ,p_action_information10 => NULL /* Balance fed by a seeded element, whose input and output currency is INR */
972 ,p_action_information11 => NULL
973 ,p_action_information12 => NULL
974 );
975 END IF;
976
977 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
978 --
979 EXCEPTION
980 --
981 WHEN OTHERS THEN
982 IF csr_std_elements%ISOPEN THEN
983 --
984 CLOSE csr_std_elements;
985 --
986 END IF;
987 --
988 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
989 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
990 pay_in_utils.trace(l_message,l_procedure);
991
992 RAISE;
993 --
994 END archive_stat_elements;
995
996
997 --------------------------------------------------------------------------
998 -- --
999 -- Name : ARCHIVE_BALANCES --
1000 -- Type : PROCEDURE --
1001 -- Access : Private --
1002 -- Description : This procedure archives the given balance,its --
1003 -- current and YTD value. --
1004 -- --
1005 -- Parameters : --
1006 -- IN : p_effective_date DATE --
1007 -- p_assact_id NUMBER --
1008 -- p_narraive VARCHAR2 --
1009 -- p_value_curr NUMBER --
1010 -- p_value_ytd NUMBER --
1011 -- --
1012 -- OUT : N/A --
1013 -- --
1014 -- Change History : --
1015 --------------------------------------------------------------------------
1016 -- Rev# Date Userid Description --
1017 --------------------------------------------------------------------------
1018 -- 115.0 04-NOV-2004 bramajey Initial Version --
1019 --------------------------------------------------------------------------
1020 --
1021
1022 PROCEDURE archive_balances(
1023 p_effective_date IN DATE
1024 ,p_assact_id IN NUMBER
1025 ,p_narrative IN VARCHAR2
1026 ,p_value_ytd IN NUMBER
1027 )
1028 IS
1029 --
1030 l_action_info_id NUMBER;
1031 l_ovn NUMBER;
1032 l_procedure VARCHAR2(80);
1033 l_message VARCHAR2(255);
1034 --
1035 BEGIN
1036 --
1037 l_procedure := g_package || 'archive_balances';
1038
1039 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1040
1041 -- Archive Statutory balances
1042 IF g_debug THEN
1043 pay_in_utils.trace('**************************************************','********************');
1044 pay_in_utils.trace('Narrative ',p_narrative);
1045 pay_in_utils.trace('Action Context ID ',p_assact_id);
1046 pay_in_utils.trace('Balance value ',p_value_ytd);
1047 pay_in_utils.trace('Effective Date ',p_effective_date);
1048 pay_in_utils.trace('**************************************************','********************');
1049 END IF;
1050
1051 pay_action_information_api.create_action_information
1052 (
1053 p_action_information_id => l_action_info_id
1054 ,p_action_context_id => p_assact_id
1055 ,p_action_context_type => 'AAP'
1056 ,p_object_version_number => l_ovn
1057 ,p_effective_date => p_effective_date
1058 ,p_source_id => NULL
1059 ,p_source_text => p_narrative
1060 ,p_action_information_category => 'APAC BALANCES'
1061 ,p_action_information1 => p_narrative
1062 ,p_action_information2 => NULL
1063 ,p_action_information3 => NULL
1064 ,p_action_information4 => fnd_number.number_to_canonical(p_value_ytd)
1065 );
1066
1067 hr_utility.set_location('Leaving ' || l_procedure,30);
1068 --
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1072 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1073 pay_in_utils.trace(l_message,l_procedure);
1074 RAISE;
1075 --
1076 END archive_balances;
1077 --
1078
1079 --------------------------------------------------------------------------
1080 -- --
1081 -- Name : ARCHIVE_STAT_BALANCES --
1082 -- Type : PROCEDURE --
1083 -- Access : Private --
1084 -- Description : This procedure calls pay_in_payslip.balance_totals --
1085 -- to get the current and YTD values of the following --
1086 -- balances --
1087 -- 1. Taxable Earnings --
1088 -- 2. Non Taxable Earnings --
1089 -- 3. Statutory Deductions --
1090 -- 4. Voluntary Deductions --
1091 -- It then calls ARCHIVE_BALANCES to archive --
1092 -- individual balances --
1093 -- --
1094 -- Parameters : --
1095 -- IN : p_assignment_action_id NUMBER --
1096 -- p_assignment_id NUMBER --
1097 -- p_date_earned DATE --
1098 -- p_effective_date DATE --
1099 -- p_assact_id NUMBER --
1100 -- --
1101 -- OUT : N/A --
1102 -- --
1103 -- Change History : --
1104 --------------------------------------------------------------------------
1105 -- Rev# Date Userid Description --
1106 --------------------------------------------------------------------------
1107 -- 115.0 21-SEP-2004 bramajey Initial Version --
1108 --------------------------------------------------------------------------
1109 --
1110
1111 PROCEDURE archive_stat_balances(
1112 p_assignment_action_id IN NUMBER
1113 ,p_assignment_id IN NUMBER
1114 ,p_date_earned IN DATE
1115 ,p_effective_date IN DATE
1116 ,p_assact_id IN NUMBER
1117 )
1118 IS
1119
1120 l_value NUMBER;
1121 l_procedure VARCHAR2(100);
1122 l_message VARCHAR2(255);
1123 TYPE t_balance_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
1124 g_bal_name t_balance_name;
1125
1126 BEGIN
1127 --
1128 g_bal_name(1) := 'F16 Salary Under Section 17';
1129 g_bal_name(2) := 'F16 Value of Perquisites';
1130 g_bal_name(3) := 'F16 Gross Salary';
1131 g_bal_name(4) := 'F16 Allowances Exempt';
1132 g_bal_name(5) := 'F16 Deductions under Sec 16';
1133 g_bal_name(6) := 'F16 Total Chapter VI A Deductions';
1134 g_bal_name(7) := 'F16 Total Income';
1135 g_bal_name(8) := 'F16 Tax on Total Income';
1136 g_bal_name(9) := 'F16 Total Tax payable';
1137
1138 l_procedure := g_package || 'archive_stat_balances';
1139
1140 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1141
1142 IF g_debug THEN
1143 pay_in_utils.trace('**************************************************','********************');
1144 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
1145 END IF;
1146
1147 FOR i IN 1..9
1148 LOOP
1149 l_value := pay_in_tax_utils.get_balance_value(p_assignment_action_id => p_assignment_action_id,
1150 p_balance_name => g_bal_name(i),
1151 p_dimension_name => '_ASG_DE_PTD',
1152 p_context_name => 'NULL',
1153 p_context_value => 'NULL'
1154 );
1155 IF (i =4) THEN
1156 g_bal_name(i) := 'F16 Allowances Exempted u/s 10';
1157 END IF;
1158
1159 IF g_debug THEN
1160 pay_in_utils.trace('Balance Name ',g_bal_name(i));
1161 pay_in_utils.trace('Balance Value ',l_value);
1162 END IF;
1163
1164 archive_balances(
1165 p_effective_date => p_effective_date,
1166 p_assact_id => p_assact_id,
1167 p_narrative => SUBSTR(g_bal_name(i),5),
1168 p_value_ytd => l_value
1169 );
1170
1171 END LOOP;
1172
1173 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1174 --
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1178 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
1179 pay_in_utils.trace(l_message,l_procedure);
1180 RAISE;
1181 --
1182 END archive_stat_balances;
1183
1184 --------------------------------------------------------------------------
1185 -- --
1186 -- Name : ARCHIVE_FORM24Q_BALANCES --
1187 -- Type : PROCEDURE --
1188 -- Access : Private --
1189 -- Description : This procedure calls pay_in_tax_utils. --
1190 -- get_balance_value to archive individual balances for --
1191 -- the following balances --
1192 -- --
1193 -- 1. Net Pay --
1194 -- 2. Income Tax This Pay --
1195 -- 3. TDS on Direct Payments --
1196 -- 4. Surcharge This Pay --
1197 -- 5. Education Cess This Pay --
1198 -- It then calls pay_action_information_api. --
1199 -- create_action_information to archive individual balances --
1200 -- --
1201 -- Parameters : --
1202 -- IN : p_assignment_action_id NUMBER --
1203 -- p_assignment_id NUMBER --
1204 -- p_date_earned DATE --
1205 -- p_effective_date DATE --
1206 -- p_assact_id NUMBER --
1207 -- p_payroll_action_id NUMBER --
1208 -- p_run_payroll_action_id NUMBER --
1209 -- p_pre_assact_id NUMBER --
1210 -- --
1211 -- OUT : N/A --
1212 -- --
1213 -- Change History : --
1214 --------------------------------------------------------------------------
1215 -- Rev# Date Userid Description --
1216 --------------------------------------------------------------------------
1217 -- 115.0 02-APR-2007 sivanara Initial Version --
1218 -- 115.1 30-APR-2007 sivanara The Balance value are been calculate --
1219 -- using dimension _ASG_RUN --
1220 -- 115.2 10-MAY-2007 RSAHARAY Changed cursor c_multi_records --
1221 -- 115.3 16-Jun-2008 lnagaraj Archived Gross Pay instead of Net Pay--
1222 -- 115.4 07-DEC-2012 pthummal Modified procedure --
1223 -- archive_Form24Q_balances to hide --
1224 -- Medical Reimbursement Amount in Taxable Income --
1225 --------------------------------------------------------------------------
1226 PROCEDURE archive_Form24Q_balances(
1227 p_assignment_action_id IN NUMBER
1228 ,p_assignment_id IN NUMBER
1229 ,p_date_earned IN DATE
1230 ,p_effective_date IN DATE
1231 ,p_assact_id IN NUMBER
1232 ,p_payroll_action_id IN NUMBER
1233 ,p_run_payroll_action_id IN NUMBER
1234 ,p_pre_assact_id IN NUMBER
1235 )
1236 IS
1237
1238 /*Cursor for selecting assignment actions in case Single pre payment has been done for multi payroll runs*/
1239 CURSOR c_multi_rec_count(p_prepayment_lcking_id NUMBER
1240 )
1241 IS
1242 select count(paa.assignment_action_id)
1243 from pay_payroll_actions ppa
1244 ,pay_assignment_actions paa
1245 ,pay_action_interlocks pal
1246 where pal.locking_action_id=p_prepayment_lcking_id
1247 and paa.assignment_action_id=pal.locked_action_id
1248 and ppa.payroll_action_id=paa.payroll_action_id
1249 and ppa.action_type in ('Q','R')
1250 and ppa.action_status='C'
1251 and paa.action_status='C'
1252 and paa.source_action_id is not null;
1253
1254 CURSOR c_multi_records(p_prepayment_lcking_id NUMBER)
1255 IS
1256 select paa.assignment_action_id assignment_action_id
1257 ,ppa.date_earned date_earned
1258 ,ppa.effective_date effective_date
1259 from pay_payroll_actions ppa
1260 ,pay_assignment_actions paa
1261 ,pay_action_interlocks pal
1262 where pal.locking_action_id=p_prepayment_lcking_id
1263 and paa.assignment_action_id=pal.locked_action_id
1264 and ppa.payroll_action_id=paa.payroll_action_id
1265 and ppa.action_type in ('Q','R')
1266 and ppa.action_status='C'
1267 and paa.action_status='C'
1268 and paa.source_action_id is not null
1269 ORDER BY TO_NUMBER(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id);
1270
1271 /*Cursor for selecting payroll for the given payroll_action_id*/
1272 CURSOR c_payroll_id(p_payroll_action_id NUMBER) IS
1273 SELECT payroll.payroll_id
1274 , payroll.payroll_name
1275 FROM pay_payrolls_f payroll
1276 ,pay_payroll_actions ppa
1277 WHERE ppa.payroll_action_id = p_run_payroll_action_id
1278 AND ppa.payroll_id = payroll.payroll_id;
1279
1280 /*Added CURSOR c_med_added_to_net_pay for Bug 15951974 */
1281 CURSOR c_med_added_to_net_pay IS
1282 SELECT PRV2.RESULT_VALUE
1283 FROM PAY_ELEMENT_TYPES_F PET,
1284 PAY_ELEMENT_CLASSIFICATIONS PEC,
1285 PAY_INPUT_VALUES_F PIV,
1286 PAY_INPUT_VALUES_F PIV2,
1287 PAY_RUN_RESULTS PRR,
1288 PAY_RUN_RESULT_VALUES PRV,
1289 PAY_RUN_RESULT_VALUES PRV2
1290 WHERE PEC.LEGISLATION_CODE='IN'
1291 AND PEC.CLASSIFICATION_NAME='Fringe Benefits'
1292 AND PEC.CLASSIFICATION_ID=PET.CLASSIFICATION_ID
1293 AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
1294 AND PIV.NAME = 'Component Name'
1295 AND PET.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID
1296 AND PIV2.NAME = 'Add to Net Pay'
1297 AND PRR.ASSIGNMENT_ACTION_ID = p_assignment_action_id
1298 AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
1299 AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
1300 AND PRV.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
1301 AND PRV.RESULT_VALUE= 'Employees Welfare Expense'
1302 AND PRR.RUN_RESULT_ID = PRV2.RUN_RESULT_ID
1303 AND PRV2.INPUT_VALUE_ID=PIV2.INPUT_VALUE_ID
1304 AND P_EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
1305 AND P_EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
1306 AND P_EFFECTIVE_DATE BETWEEN PIV2.EFFECTIVE_START_DATE AND PIV2.EFFECTIVE_END_DATE;
1307
1308 l_action_info_id NUMBER;
1309 l_count NUMBER;
1310 l_asg_id NUMBER;
1311 l_date DATE;
1312 l_eff_date DATE;
1313 l_ovn NUMBER;
1314 l_value NUMBER;
1315 l_multirec_value NUMBER;
1316 l_procedure VARCHAR2(100);
1317 l_message VARCHAR2(255);
1318 l_assessment_year VARCHAR2(20);
1319 l_next_year VARCHAR2(20);
1320 l_period NUMBER;
1321 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
1322 l_payroll_id NUMBER;
1323 l_tan hr_organization_information.org_information1%TYPE;
1324 TYPE r_balance_name_val IS RECORD(l_balance_name pay_balance_types.balance_name%type,
1325 l_balance_val NUMBER);
1326 TYPE t_balance IS TABLE OF r_balance_name_val INDEX BY PLS_INTEGER;
1327 l_bal_name_val t_balance;
1328 l_add_to_net_flag VARCHAR2(10) := NULL;
1329
1330 BEGIN
1331
1332 l_bal_name_val(1).l_balance_name := 'Net Pay';
1333 l_bal_name_val(2).l_balance_name := 'Income Tax This Pay';
1334 l_bal_name_val(3).l_balance_name := 'TDS on Direct Payments';
1335 l_bal_name_val(4).l_balance_name := 'Surcharge This Pay';
1336 l_bal_name_val(5).l_balance_name := 'Education Cess This Pay';
1337 l_bal_name_val(6).l_balance_name := 'Sec and HE Cess This Pay';
1338 l_bal_name_val(7).l_balance_name := 'Involuntary Deductions';
1339 l_bal_name_val(8).l_balance_name := 'Pre Tax Deductions';
1340 l_bal_name_val(9).l_balance_name := 'Tax Deductions';
1341 l_bal_name_val(10).l_balance_name := 'Voluntary Deductions';
1342 l_bal_name_val(11).l_balance_name := 'Direct Payments'; /* Added l_bal_name_val(11) for Bug 13986012 */
1343
1344 /* Added l_bal_name_val(12) for Bug 15951974 */
1345 l_bal_name_val(12).l_balance_name := 'Medical Reimbursement Amount';
1346
1347 l_procedure := g_package || 'archive_form24q_balances';
1348
1349
1350 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1351 IF g_debug THEN
1352 pay_in_utils.trace('**************************************************','********************');
1353 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
1354 pay_in_utils.trace('Assignment id ',p_assignment_id);
1355 pay_in_utils.trace('Earned date ',p_date_earned);
1356 pay_in_utils.trace('Effective Date ',p_effective_date);
1357 pay_in_utils.trace('Assignment action id ',p_assact_id);
1358 pay_in_utils.trace('Payroll action id ',p_payroll_action_id);
1359 pay_in_utils.trace('Run Payroll action id ',p_run_payroll_action_id);
1360 pay_in_utils.trace('Prepayment assignmentaction id ',p_pre_assact_id);
1361 END IF;
1362
1363 OPEN c_multi_rec_count(p_pre_assact_id);
1364 FETCH c_multi_rec_count INTO l_count;
1365 CLOSE c_multi_rec_count;
1366
1367 l_date := p_date_earned;
1368 l_eff_date := p_effective_date;
1369 FOR i IN l_bal_name_val.first..l_bal_name_val.last
1370 LOOP
1371 IF l_count > 1 THEN
1372 l_multirec_value := 0;
1373 pay_in_utils.set_location(g_debug,l_procedure, 20);
1374
1375 FOR rec_multi IN c_multi_records( p_pre_assact_id) LOOP
1376 l_date :=rec_multi.date_earned;
1377 l_eff_date := rec_multi.effective_date;
1378 pay_in_utils.set_location(g_debug,l_procedure, 30);
1379 l_multirec_value := l_multirec_value + pay_in_tax_utils.get_balance_value(p_assignment_action_id => rec_multi.assignment_action_id,
1380 p_balance_name => l_bal_name_val(i).l_balance_name,
1381 p_dimension_name => '_ASG_RUN',
1382 p_context_name => 'NULL',
1383 p_context_value => 'NULL');
1384
1385 END LOOP;
1386 l_bal_name_val(i).l_balance_val := l_multirec_value;
1387 l_multirec_value :=0;
1388
1389 ELSE
1390 l_bal_name_val(i).l_balance_val := pay_in_tax_utils.get_balance_value(p_assignment_action_id => p_assignment_action_id,
1391 p_balance_name => l_bal_name_val(i).l_balance_name,
1392 p_dimension_name => '_ASG_RUN',
1393 p_context_name => 'NULL',
1394 p_context_value => 'NULL'
1395 );
1396 END IF;
1397
1398 IF g_debug THEN
1399 pay_in_utils.trace('Balance Name ',l_bal_name_val(i).l_balance_name);
1400 pay_in_utils.trace('Balance Value ',l_bal_name_val(i).l_balance_val);
1401 END IF;
1402 END LOOP;
1403 /* Bug 7165051 Start */
1404 l_bal_name_val(1).l_balance_val := l_bal_name_val(1).l_balance_val +
1405 (l_bal_name_val(7).l_balance_val +
1406 l_bal_name_val(8).l_balance_val +
1407 l_bal_name_val(9).l_balance_val +
1408 l_bal_name_val(10).l_balance_val+
1409 l_bal_name_val(11).l_balance_val ); /* Added l_bal_name_val(11) for Bug 13986012 */
1410 /* Bug 7165051 End */
1411
1412 /* Added l_bal_name_val(12) for Bug 15951974 */
1413 OPEN c_med_added_to_net_pay;
1414 FETCH c_med_added_to_net_pay INTO l_add_to_net_flag;
1415 IF l_add_to_net_flag ='Y' THEN
1416 l_bal_name_val(1).l_balance_val :=l_bal_name_val(1).l_balance_val - l_bal_name_val(12).l_balance_val;
1417
1418 IF g_debug THEN
1419 pay_in_utils.trace('Balance Name ',l_bal_name_val(1).l_balance_name);
1420 pay_in_utils.trace('Balance Value ',l_bal_name_val(1).l_balance_val);
1421 pay_in_utils.trace('Balance Name ',l_bal_name_val(12).l_balance_name);
1422 pay_in_utils.trace('Balance Value ',l_bal_name_val(12).l_balance_val);
1423 END IF;
1424
1425 END IF;
1426 CLOSE c_med_added_to_net_pay;
1427 /* Bug 15951974 ends */
1428
1429
1430 OPEN c_payroll_id(p_run_payroll_action_id);
1431 FETCH c_payroll_id INTO l_payroll_id,l_payroll_name;
1432 CLOSE c_payroll_id;
1433 /* Bug 12660389 */
1434 l_next_year := to_char(pay_in_utils.next_tax_year(p_effective_date),'YYYY');
1435 l_assessment_year := l_next_year || '-' || to_char(to_number(l_next_year)+1);
1436 l_tan := pay_in_form_24q_web_adi.get_tan_number(p_assignment_id, nvl(l_date, p_effective_date));
1437 l_period := pay_in_tax_utils.get_period_number(l_payroll_id, nvl(TRUNC(l_date), p_effective_date));
1438 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 35);
1439
1440 pay_action_information_api.create_action_information
1441 (
1442 p_action_information_id => l_action_info_id
1443 ,p_action_context_id => p_assact_id
1444 ,p_assignment_id => p_assignment_id
1445 ,p_action_context_type => 'AAP'
1446 ,p_object_version_number => l_ovn
1447 ,p_effective_date => l_eff_date
1448 ,p_source_id => p_assignment_action_id
1449 ,p_source_text => NULL
1450 ,p_action_information_category => 'IN_TAX_BALANCES'
1451 ,p_action_information1 => l_assessment_year
1452 ,p_action_information2 => fnd_number.number_to_canonical(l_period)
1453 ,p_action_information3 => l_tan
1454 ,p_action_information4 => fnd_number.number_to_canonical(l_bal_name_val(1).l_balance_val)
1455 ,p_action_information5 => fnd_number.number_to_canonical(l_bal_name_val(2).l_balance_val)
1456 ,p_action_information6 => fnd_number.number_to_canonical(l_bal_name_val(3).l_balance_val)
1457 ,p_action_information7 => fnd_number.number_to_canonical(l_bal_name_val(4).l_balance_val)
1458 ,p_action_information8 => fnd_number.number_to_canonical(l_bal_name_val(5).l_balance_val)
1459 ,p_action_information9 => fnd_number.number_to_canonical(l_bal_name_val(6).l_balance_val)
1460 ,p_action_information10 => to_char(l_date,'DD/MM/YYYY')
1461 ,p_action_information11 => p_payroll_action_id
1462 ,p_action_information12 => p_pre_assact_id
1463 ,p_action_information13 => l_payroll_name);
1464
1465 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
1466 l_bal_name_val.delete;
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1470 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1471 pay_in_utils.trace(l_message,l_procedure);
1472 RAISE;
1473 --
1474 END archive_Form24Q_balances;
1475 --------------------------------------------------------------------------
1476 -- --
1477 -- Name : ARCHIVE_EMPLOYEE_DETAILS --
1478 -- Type : PROCEDURE --
1479 -- Access : Private --
1480 -- Description : This procedure calls --
1481 -- 'pay_emp_action_arch.get_personal_information' that --
1482 -- actually archives the employee details,employee --
1483 -- ddress details, Employer Address Details --
1484 -- and Net Pay Distribution information. Procedure --
1485 -- 'get_personal_information' is passed tax_unit_id --
1486 -- to make core provided 'Choose Payslip' work for IN. --
1487 -- The action DF structures used are --
1488 -- ADDRESS DETAILS --
1489 -- EMPLOYEE DETAILS --
1490 -- EMPLOYEE NET PAY DISTRIBUTION --
1491 -- EMPLOYEE OTHER INFORMATION --
1492 -- After core procedure completes the archival, the --
1493 -- information stored for category --
1494 -- EMPLOYEE_NET_PAY_DISTRIBUTION is updated with --
1495 -- Bank_name,Bank Branch,Account Number,percentage --
1496 -- and currency code. --
1497 -- Then EMPLOYEE DETAILS is updated with the --
1498 -- payroll_location available in SOFT_CODING_KEY_FLEX --
1499 -- --
1500 -- Parameters : --
1501 -- IN : p_payroll_action_id NUMBER --
1502 -- p_pay_assignment_action_id NUMBER --
1503 -- p_assact_id NUMBER --
1504 -- p_assignment_id NUMBER --
1505 -- p_curr_pymt_ass_act_id NUMBER --
1506 -- p_date_earned DATE --
1507 -- p_latest_period_payment_date DATE --
1508 -- p_run_effective_date DATE --
1509 -- p_time_period_id NUMBER --
1510 -- p_pre_effective_date DATE --
1511 -- --
1512 -- OUT : N/A --
1513 -- --
1514 -- Change History : --
1515 --------------------------------------------------------------------------
1516 -- Rev# Date Userid Description --
1517 --------------------------------------------------------------------------
1518 -- 115.0 21-SEP-2004 bramajey Initial Version --
1519 -- 115.1 27-Dec-2004 aaagarwa Corrected variable types --
1520 -- 115.2 15-Mar-2005 aaagarwa Corrected variable types --
1521 --------------------------------------------------------------------------
1522 --
1523
1524 PROCEDURE archive_employee_details (
1525 p_payroll_action_id IN NUMBER
1526 ,p_pay_assignment_action_id IN NUMBER
1527 ,p_assactid IN NUMBER
1528 ,p_assignment_id IN NUMBER
1529 ,p_curr_pymt_ass_act_id IN NUMBER
1530 ,p_date_earned IN DATE
1531 ,p_latest_period_payment_date IN DATE
1532 ,p_run_effective_date IN DATE
1533 ,p_time_period_id IN NUMBER
1534 ,p_pre_effective_date IN DATE
1535 )
1536 IS
1537 --
1538 -- Cursor to select the archived information for category 'EMPLOYEE NET PAY DISTRIBUTION'
1539 -- by core package.
1540
1541 CURSOR csr_net_pay_action_info_id
1542 IS
1543 SELECT action_information_id
1544 ,action_information1
1545 ,action_information2
1546 FROM pay_action_information
1547 WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
1548 AND action_context_id = p_assactid
1549 AND action_context_type = 'AAP';
1550 --
1551
1552
1553 -- Cursor to select the archived information for category 'EMPLOYEE DETAILS'
1554 -- by core package.
1555
1556 CURSOR csr_emp_det_action_info_id
1557 IS
1558 SELECT action_information_id
1559 FROM pay_action_information
1560 WHERE action_information_category = 'EMPLOYEE DETAILS'
1561 AND action_context_id = p_assactid
1562 AND action_context_type = 'AAP';
1563
1564 CURSOR csr_person_id
1565 IS
1566 SELECT paa.person_id
1567 FROM per_assignments_f paa
1568 WHERE paa.assignment_id = p_assignment_id
1569 AND p_date_earned BETWEEN paa.effective_start_date
1570 AND paa.effective_end_date;
1571 --
1572 CURSOR csr_person_details (p_person_id IN NUMBER)
1573 IS
1574 SELECT fnd_date.date_to_canonical(pap.date_of_birth) dob
1575 ,pap.per_information8 pf_number
1576 ,pap.per_information9 esi_number
1577 ,pap.per_information4 pan
1578 ,pap.per_information10 superannuation_number
1579 ,hr_in_utility.per_in_full_name(pap.first_name,pap.middle_names,pap.last_name,pap.title)
1580 ,pap.email_address
1581 FROM per_people_f pap
1582 WHERE pap.person_id = p_person_id
1583 AND p_date_earned BETWEEN pap.effective_start_date
1584 AND pap.effective_end_date;
1585
1586
1587 -- Cursor to get Professinal Tax Number
1588 CURSOR csr_prof_tax_number
1589 IS
1590 --
1591 SELECT hoi.org_information1
1592 FROM hr_soft_coding_keyflex hsck
1593 ,hr_organization_information hoi
1594 ,per_assignments_f paaf
1595 ,pay_assignment_actions paa
1596 ,pay_payroll_actions ppa
1597 WHERE paa.assignment_action_id = p_assactid
1598 AND paa.payroll_action_id = ppa.payroll_action_id
1599 AND paa.assignment_id = paaf.assignment_id
1600 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
1601 AND hsck.segment3 = hoi.organization_id
1602 AND hoi.org_information_context = 'PER_IN_PROF_TAX_DF'
1603 AND ppa.effective_date BETWEEN paaf.effective_start_date
1604 AND paaf.effective_end_date;
1605 --
1606
1607 -- Cursor to select the tax_unit_id of the prepayment needed for archival
1608
1609 CURSOR csr_tax_unit_id
1610 IS
1611 SELECT tax_unit_id
1612 FROM pay_assignment_actions
1613 WHERE assignment_action_id = p_curr_pymt_ass_act_id;
1614 --
1615
1616
1617 -- Cursor to get the bank name,percentage and currency code
1618
1619 CURSOR csr_bank_details(
1620 p_personal_payment_method_id NUMBER
1621 ,p_org_payment_method_id NUMBER
1622 )
1623 IS
1624 SELECT pea.segment3 bank_name
1625 ,pea.segment4 bank_branch
1626 ,pea.segment1 account_number
1627 ,ppm.percentage percentage
1628 ,pop.currency_code
1629 FROM pay_external_accounts pea
1630 ,pay_pre_payments ppp
1631 ,pay_org_payment_methods_f pop
1632 ,pay_personal_payment_methods_f ppm
1633 WHERE ppp.assignment_action_id = p_curr_pymt_ass_act_id
1634 AND nvl(ppp.personal_payment_method_id,0) = nvl(p_personal_payment_method_id,0)
1635 AND ppp.org_payment_method_id = p_org_payment_method_id
1636 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id (+)
1637 AND ppp.org_payment_method_id = pop.org_payment_method_id
1638 AND ppm.external_account_id = pea.external_account_id (+)
1639 AND p_pre_effective_date BETWEEN pop.effective_start_date
1640 AND pop.effective_end_date
1641 AND p_pre_effective_date BETWEEN nvl(ppm.effective_start_date,p_pre_effective_date)
1642 AND nvl(ppm.effective_end_date,p_pre_effective_date);
1643 /* Bug 4159662*/
1644 -- Cursor to select the archived information for category 'EMPLOYEE DETAILS'
1645 CURSOR csr_emp_details
1646 IS
1647 SELECT action_information_id
1648 FROM pay_action_information
1649 WHERE action_information_category = 'EMPLOYEE DETAILS'
1650 AND action_context_id = p_assactid
1651 AND action_context_type = 'AAP';
1652
1653 -- Cursor to get the Registered Name of the GRE/Legal Entity
1654 Cursor c_reg_name
1655 IS
1656 SELECT hou.name
1657 FROM per_assignments_f peaf
1658 ,hr_soft_coding_keyflex hrscf
1659 ,hr_organization_information hoi
1660 ,hr_organization_units hou
1661 WHERE peaf.assignment_id=p_assignment_id
1662 AND peaf.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
1663 AND hoi.organization_id=hrscf.segment1
1664 AND hoi.org_information_context='PER_IN_INCOME_TAX_DF'
1665 AND hou.organization_id=hoi.org_information4
1666 AND p_date_earned between peaf.effective_start_date and peaf.effective_end_date;
1667 --
1668 --
1669 l_person_id per_assignments_f.person_id%TYPE;
1670
1671 l_action_info_id NUMBER;
1672 l_ovn NUMBER;
1673 l_tax_unit_id NUMBER;
1674 l_procedure VARCHAR2(80);
1675 /*4229709*/
1676 l_bank_name pay_external_accounts.segment3%TYPE;
1677 l_bank_branch pay_external_accounts.segment4%TYPE;
1678 l_bank VARCHAR2(310);
1679 l_account_number pay_external_accounts.segment1%TYPE;
1680 l_percentage pay_personal_payment_methods_f.percentage%TYPE;
1681 l_currency_code pay_org_payment_methods_f.currency_code%TYPE;
1682 l_prof_tax_number hr_organization_information.org_information1%TYPE;
1683 /*4229709*/
1684 l_emp_det_act_info_id NUMBER;
1685 l_tax_area VARCHAR2(10);
1686 l_dob VARCHAR2(30);
1687 /* Bug 4089704*/
1688 l_pf_number per_people_f.per_information8%TYPE;
1689 l_esi_number per_people_f.per_information9%TYPE;
1690 l_pan per_people_f.per_information4%TYPE;
1691 l_superannuation_number per_people_f.per_information10%TYPE;
1692 /* Bug 4089704*/
1693
1694 l_month VARCHAR2(30);
1695 l_year VARCHAR2(30);
1696 /* Bug 4159662*/
1697 l_reg_name hr_organization_units.name%TYPE;
1698 l_act_inf_id pay_action_information.action_information_id%TYPE;
1699 l_message VARCHAR2(255);
1700 l_full_name per_people_f.full_name%TYPE;
1701 l_email_address per_people_f.email_address%TYPE;
1702
1703 --
1704 BEGIN
1705 --
1706 l_procedure := g_package || 'archive_employee_details';
1707
1708 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1709
1710 -- call generic procedure to retrieve and archive all data for
1711 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
1712
1713 IF g_debug THEN
1714 pay_in_utils.trace('**************************************************','********************');
1715 pay_in_utils.trace('Assignment id ',p_assignment_id);
1716 pay_in_utils.trace('Archive Action id ',p_assactid);
1717 pay_in_utils.trace('Tax unit id ',l_tax_unit_id);
1718 pay_in_utils.trace('Prepayment Assignment Action id ',p_curr_pymt_ass_act_id);
1719 pay_in_utils.trace('Run Effective Date ',p_run_effective_date);
1720 pay_in_utils.trace('**************************************************','********************');
1721 END IF;
1722
1723
1724 OPEN csr_tax_unit_id;
1725 FETCH csr_tax_unit_id INTO l_tax_unit_id;
1726 CLOSE csr_tax_unit_id;
1727
1728 pay_in_utils.set_location(g_debug,l_procedure, 20);
1729
1730 pay_emp_action_arch.get_personal_information
1731 (
1732 p_payroll_action_id => p_payroll_action_id -- archive payroll_action_id
1733 ,p_assactid => p_assactid -- archive assignment_action_id
1734 ,p_assignment_id => p_assignment_id -- current assignment_id
1735 ,p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
1736 ,p_curr_eff_date => p_run_effective_date -- run effective_date
1737 ,p_date_earned => p_date_earned -- payroll date_earned
1738 ,p_curr_pymt_eff_date => p_latest_period_payment_date -- latest payment date
1739 ,p_tax_unit_id => l_tax_unit_id -- tax_unit_id needed for Choose Payslip region.
1740 ,p_time_period_id => p_time_period_id -- time_period_id from per_time_periods
1741 ,p_ppp_source_action_id => NULL
1742 ,p_run_action_id => p_pay_assignment_action_id
1743 );
1744 pay_in_utils.set_location(g_debug,l_procedure, 30);
1745
1746 /* Bug 4159662*/
1747 --Find the Registered Name of GRE
1748 OPEN c_reg_name;
1749 FETCH c_reg_name INTO l_reg_name;
1750 CLOSE c_reg_name;
1751
1752 pay_in_utils.set_location(g_debug,l_procedure, 40);
1753
1754 --Update the name in archived data
1755 OPEN csr_emp_details;
1756 FETCH csr_emp_details INTO l_act_inf_id;
1757 CLOSE csr_emp_details;
1758
1759 pay_action_information_api.update_action_information
1760 (
1761 p_action_information_id => l_act_inf_id
1762 ,p_object_version_number => l_ovn
1763 ,p_action_information18 => l_reg_name
1764 );
1765 pay_in_utils.set_location(g_debug,l_procedure, 50);
1766
1767 FOR net_pay_rec in csr_net_pay_action_info_id
1768
1769 LOOP
1770 --
1771 pay_in_utils.set_location(g_debug,l_procedure, 60);
1772 OPEN csr_bank_details(
1773 net_pay_rec.action_information2
1774 ,net_pay_rec.action_information1
1775 );
1776
1777 FETCH csr_bank_details INTO l_bank_name
1778 ,l_bank_branch
1779 ,l_account_number
1780 ,l_percentage
1781 ,l_currency_code;
1782 CLOSE csr_bank_details;
1783
1784 IF g_debug THEN
1785 pay_in_utils.trace('Bank Name ',l_bank_name);
1786 pay_in_utils.trace('Bank branch ',l_bank_branch);
1787 END IF;
1788
1789 IF (l_bank_branch IS NULL) OR (l_bank_name IS NULL) THEN
1790 --
1791 l_bank := NULL;
1792 --
1793 ELSE
1794 --
1795
1796 l_bank :=
1797 hr_general.decode_lookup('IN_BANK',l_bank_name)||','||
1798 hr_general.decode_lookup('IN_BANK_BRANCH',l_bank_branch);
1799 --
1800 END IF;
1801 pay_in_utils.set_location(g_debug,l_procedure, 70);
1802 l_ovn := 1;
1803
1804 pay_action_information_api.update_action_information
1805 (
1806 p_action_information_id => net_pay_rec.action_information_id
1807 ,p_object_version_number => l_ovn
1808 ,p_action_information5 => l_bank
1809 ,p_action_information7 => l_account_number
1810 ,p_action_information12 => l_percentage
1811 ,p_action_information13 => l_currency_code
1812 );
1813 --
1814 END LOOP;
1815
1816 pay_in_utils.set_location(g_debug,l_procedure, 80);
1817
1818 OPEN csr_emp_det_action_info_id;
1819 FETCH csr_emp_det_action_info_id INTO l_emp_det_act_info_id;
1820 CLOSE csr_emp_det_action_info_id;
1821
1822 pay_in_utils.set_location(g_debug,l_procedure, 90);
1823
1824 -- Bug 3139966 starts
1825 -- Added code to archive Expatriate Indicator, Passport
1826 OPEN csr_person_id;
1827 FETCH csr_person_id
1828 INTO l_person_id;
1829 CLOSE csr_person_id;
1830
1831 IF g_debug THEN
1832 pay_in_utils.trace('Person ID ',l_person_id);
1833 pay_in_utils.trace('Effective Date ',p_pre_effective_date);
1834 END IF;
1835
1836 pay_in_utils.set_location(g_debug,l_procedure, 100);
1837
1838 OPEN csr_person_details (l_person_id);
1839 FETCH csr_person_details
1840 INTO l_dob
1841 ,l_pf_number
1842 ,l_esi_number
1843 ,l_pan
1844 ,l_superannuation_number
1845 ,l_full_name
1846 ,l_email_address;
1847 CLOSE csr_person_details;
1848
1849 IF l_email_address IS NOT NULL
1850 THEN
1851 pay_in_utils.set_location(g_debug,l_procedure, 105);
1852 pay_action_information_api.create_action_information
1853 (p_action_context_id => p_assactid
1854 ,p_action_context_type => 'AAP'
1855 ,p_action_information_category => 'IN_EMPLOYEE_DETAILS'
1856 ,p_effective_date => p_pre_effective_date
1857 ,p_assignment_id => p_assignment_id
1858 ,p_action_information1 => l_email_address
1859 ,p_action_information_id => l_action_info_id --OUT Parameters
1860 ,p_object_version_number => l_ovn --OUT Parameters
1861 );
1862 END IF;
1863
1864 l_month := TRIM(TO_CHAR(p_pre_effective_date,'Month'));
1865 l_year := TO_CHAR(p_pre_effective_date,'YYYY');
1866
1867 pay_in_utils.set_location(g_debug,l_procedure, 110);
1868 -- Fetch Professinal tax Number
1869 --
1870 OPEN csr_prof_tax_number;
1871 FETCH csr_prof_tax_number
1872 INTO l_prof_tax_number;
1873 IF csr_prof_tax_number%NOTFOUND THEN
1874 --
1875 l_prof_tax_number := NULL;
1876 --
1877 END IF;
1878 CLOSE csr_prof_tax_number;
1879
1880 -- Update Payroll Location,Tax Area, Passport and Expatriate Indicator
1881 --
1882
1883 pay_in_utils.set_location(g_debug,l_procedure, 120);
1884 l_ovn := 1;
1885 pay_action_information_api.update_action_information
1886 (
1887 p_action_information_id => l_emp_det_act_info_id
1888 ,p_object_version_number => l_ovn
1889 ,p_action_information1 => l_full_name
1890 ,p_action_information6 => l_esi_number
1891 ,p_action_information8 => l_prof_tax_number
1892 ,p_action_information13 => l_dob
1893 ,p_action_information23 => l_month||','||l_year
1894 ,p_action_information24 => l_pf_number
1895 ,p_action_information25 => l_pan
1896 ,p_action_information27 => l_superannuation_number
1897 );
1898
1899 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 130);
1900 --
1901 EXCEPTION
1902 --
1903 WHEN OTHERS THEN
1904 IF csr_bank_details%ISOPEN THEN
1905 CLOSE csr_bank_details;
1906 END IF;
1907 IF csr_tax_unit_id%ISOPEN THEN
1908 CLOSE csr_tax_unit_id;
1909 END IF;
1910 IF csr_net_pay_action_info_id%ISOPEN THEN
1911 CLOSE csr_net_pay_action_info_id;
1912 END IF;
1913 IF csr_emp_det_action_info_id%ISOPEN THEN
1914 CLOSE csr_emp_det_action_info_id;
1915 END IF;
1916 IF csr_person_id%ISOPEN THEN
1917 CLOSE csr_person_id;
1918 END IF;
1919 IF csr_person_details%ISOPEN THEN
1920 CLOSE csr_person_details;
1921 END IF;
1922 IF csr_prof_tax_number%ISOPEN THEN
1923 CLOSE csr_prof_tax_number;
1924 END IF;
1925 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1926 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 140);
1927 pay_in_utils.trace(l_message,l_procedure);
1928 RAISE;
1929 --
1930 END archive_employee_details;
1931
1932 --------------------------------------------------------------------------
1933 -- --
1934 -- Name : ARCHIVE_FORM_DATA --
1935 -- Type : PROCEDURE --
1936 -- Access : Public --
1937 -- Description : This procedure archives the data required for form --
1938 -- 3A and form 6A --
1939 -- Parameters : --
1940 -- IN : p_assignment_action_id NUMBER --
1941 -- p_payroll_action_id NUMBER --
1942 -- p_run_payroll_action_id NUMBER --
1943 -- p_archive_action_id NUMBER --
1944 -- p_assignment_id NUMBER --
1945 -- p_payroll_date DATE --
1946 -- p_prepayment_date DATE --
1947 -- --
1948 -- --
1949 -- OUT : N/A --
1950 -- --
1951 -- Change History : --
1952 --------------------------------------------------------------------------
1953 -- Rev# Date Userid Description --
1954 --------------------------------------------------------------------------
1955 -- 115.0 01-Jan-2005 aaagarwa Initial Version --
1956 -- 115.1 01-Mar-2005 aaagarwa Changes done for incorporating PA data-
1957 -- 115.2 08-Mar-2005 lnagaraj Archived data needed for Form 7 --
1958 -- 115.3 01-Apr-2005 abhjain Removing the archival of remarks --
1959 -- 115.4 01-Apr-2005 lnagaraj Added p_run_payroll_action_id --
1960 --------------------------------------------------------------------------
1961 --
1962
1963 PROCEDURE archive_form_data
1964 (
1965 p_assignment_action_id IN NUMBER
1966 ,p_payroll_action_id IN NUMBER
1967 ,p_run_payroll_action_id IN NUMBER
1968 ,p_archive_action_id IN NUMBER
1969 ,p_assignment_id IN NUMBER
1970 ,p_payroll_date IN DATE
1971 ,p_prepayment_date IN DATE
1972 )
1973 IS
1974
1975 -- Cursor to find Employee details
1976 CURSOR c_name_pfno_fh_name(p_assignment_action_id NUMBER)
1977 IS
1978 SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
1979 ,pep.per_information8 PF_Number
1980 ,pep.per_information13 Pension_Number
1981 ,pep.person_id person_id
1982 ,con.contact_person_id contact
1983 ,con.contact_type -- added for ECR
1984 ,pep.sex -- added for ECR
1985 ,pep.date_of_birth -- added for ECR
1986 FROM per_assignments_f asg
1987 ,per_people_f pep
1988 ,pay_assignment_actions paa
1989 ,per_contact_relationships con
1990 WHERE asg.PERSON_ID=pep.person_id
1991 AND asg.assignment_id=paa.assignment_id
1992 AND paa.assignment_action_id= p_assignment_action_id
1993 AND con.person_id(+) = pep.person_id
1994 AND con.contact_type(+) = decode(pep.sex,'M','JP_FT','F',decode(pep.marital_status,'M','S','JP_FT'))
1995 AND p_payroll_date between pep.effective_start_date and pep.effective_end_date
1996 AND p_payroll_date between asg.effective_start_date and asg.effective_end_date;
1997
1998 CURSOR csr_contact_details(p_contact_person_id NUMBER)
1999 IS
2000 SELECT hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title) Father_Husbannd
2001 FROM per_people_f pea
2002 WHERE pea.person_id = p_contact_person_id
2003 AND p_payroll_date between pea.effective_start_date and pea.effective_end_date;
2004
2005 -- Cursor to find PF Organization Id
2006 CURSOR c_pf_org_id(p_assignment_action_id NUMBER)
2007 IS
2008 select DISTINCT hoi.organization_id source_id
2009 from hr_organization_units hoi
2010 ,hr_soft_coding_keyflex scf
2011 ,per_assignments_f asg
2012 ,pay_assignment_actions paa
2013 where asg.assignment_id=paa.assignment_id
2014 and paa.assignment_action_id=p_assignment_action_id
2015 and asg.SOFT_CODING_KEYFLEX_ID=scf.SOFT_CODING_KEYFLEX_ID
2016 and hoi.ORGANIZATION_ID=scf.segment2
2017 and (to_char(asg.effective_start_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
2018 or to_char(asg.effective_end_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
2019 or p_payroll_date between asg.effective_start_date and asg.effective_end_date
2020 );
2021
2022 --Cursor to find balance value
2023 Cursor c_defined_balance_id(p_balance_name VARCHAR2
2024 ,p_dimension VARCHAR2)
2025 IS
2026 select pdb.defined_balance_id
2027 from pay_balance_types pbt
2028 ,pay_balance_dimensions pbd
2029 ,pay_defined_balances pdb
2030 where pbt.balance_name=p_balance_name
2031 and pbd.dimension_name=p_dimension
2032 and pbt.legislation_code = 'IN'
2033 and pbd.legislation_code = 'IN'
2034 and pbt.balance_type_id = pdb.balance_type_id
2035 and pbd.balance_dimension_id = pdb.balance_dimension_id;
2036
2037 --Cursor to find the PF Organization Name
2038 Cursor c_pf_name(p_organization_id NUMBER
2039 ,p_effective_date DATE)
2040 IS
2041 SELECT hou.name
2042 FROM hr_organization_units hou
2043 WHERE hou.organization_id=p_organization_id
2044 AND p_effective_date BETWEEN hou.date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
2045
2046 -- Bug 4033745 Start
2047 /* In case of transfer with Pension number change, we need to archive the PF org id with the correct
2048 pension number.The data as on date earned of payroll cant be used in this case*/
2049 -- Get the latest date in current pay period on which the assignment was attached to this PF Org
2050 CURSOR csr_asg_effective_date(p_assignment_id NUMBER
2051 ,p_source_id NUMBER
2052 ,p_pay_start DATE
2053 ,p_pay_end DATE)
2054 IS
2055 SELECT MAX(paf.effective_end_date)
2056 FROM per_assignments_f paf
2057 ,hr_soft_coding_keyflex scl
2058 WHERE paf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
2059 AND paf.assignment_id=p_assignment_id
2060 AND scl.segment2 = p_source_id
2061 AND paf.effective_start_date <= p_pay_end
2062 AND paf.effective_end_date >= p_pay_start;
2063
2064 -- Cursor to find the Pension Number as on a given date. We cannot use the date earned of payroll run
2065 -- Modified this cursor to include the PF Number also
2066 CURSOR csr_pension_number(p_assigment_id NUMBER
2067 ,p_date DATE)
2068 IS
2069 SELECT ppf.per_information13 pension_num
2070 ,ppf.per_information8 PF_Number
2071 FROM per_people_f ppf
2072 ,per_assignments_f paf
2073 WHERE ppf.person_id = paf.person_id
2074 AND paf.assignment_id = p_assignment_id
2075 AND p_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2076 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
2077
2078
2079 /* Find the earliest date on which the person was attached to this PF Organization */
2080 CURSOR csr_asg_pforg_start(l_person_id NUMBER
2081 ,l_source_id NUMBER)
2082 IS
2083 SELECT min(paf.effective_start_date)
2084 FROM per_people_f ppf
2085 ,per_assignments_f paf
2086 ,hr_soft_coding_keyflex scl
2087 WHERE ppf.person_id = l_person_id
2088 AND paf.person_id =ppf.person_id
2089 AND paf.soft_coding_keyflex_id =scl.soft_coding_keyflex_id
2090 AND scl.segment2 = l_source_id;
2091
2092 /* Find if the person has been transferred out from some other PF Organziation */
2093 CURSOR csr_chk_trsfr_pforg(l_person_id NUMBER
2094 ,l_source_id NUMBER
2095 ,l_asg_start_date varchar2)
2096 IS
2097 SELECT '1'
2098 FROM per_people_f ppf
2099 ,per_assignments_f paf
2100 ,hr_soft_coding_keyflex scl
2101 WHERE ppf.person_id = l_person_id
2102 AND paf.person_id =ppf.person_id
2103 AND paf.soft_coding_keyflex_id =scl.soft_coding_keyflex_id
2104 and scl.segment2 IS NOT NULL
2105 AND scl.segment2 <> l_source_id
2106 AND paf.effective_end_date < l_asg_start_date
2107 AND ROWNUM <2;
2108
2109 /*In case of transfer out from some other organization,find out the pension number on the date
2110 just before transfer.*/
2111 CURSOR csr_chk_pension_number_change(l_person_id NUMBER
2112 ,l_asg_start_date varchar2)
2113 IS
2114 SELECT ppf.per_information13
2115 FROM per_people_f ppf
2116 WHERE ppf.person_id =l_person_id
2117 AND ppf.per_information13 IS NOT NULL
2118 AND ppf.effective_start_date < l_asg_start_date
2119 ORDER BY ppf.effective_start_date desc;
2120
2121 /*Find hire or rehire date based on whether pension number was changed during rehire or not */
2122 CURSOR csr_hire_date(p_person_id NUMBER
2123 ,p_pension_number VARCHAR2)
2124 IS
2125 SELECT MAX(pos.date_start)
2126 FROM per_periods_of_service pos
2127 WHERE pos.person_id = p_person_id
2128 AND pos.date_start <= (SELECT MIN(effective_start_date)
2129 FROM per_people_f ppf
2130 WHERE ppf.person_id = p_person_id
2131 AND ppf.per_information13 = p_pension_number);
2132
2133 -- Bug 4033745 End
2134
2135 /*added 14784847*/
2136 CURSOR csr_pf_people (l_source_id NUMBER
2137 ,p_assignment_id NUMBER)
2138 IS
2139 SELECT
2140 distinct
2141 pps.actual_termination_date term_date, -- Termination Date
2142 pps.pds_information3 efile -- Termination Reason(EFile)
2143 FROM per_people_f ppf,
2144 per_assignments_f paf,
2145 per_periods_of_service pps,
2146 hr_soft_coding_keyflex scl
2147 WHERE paf.person_id = ppf.person_id
2148 AND paf.period_of_service_id = pps.period_of_service_id
2149 AND paf.assignment_id = p_assignment_id
2150 AND scl.segment2 = l_source_id
2151 AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
2152 AND scl.enabled_flag = 'Y'
2153 AND paf.effective_end_date between ppf.effective_start_date and ppf.effective_end_date;
2154
2155
2156
2157
2158 l_action_info_id NUMBER;
2159 l_ovn NUMBER;
2160 l_asg_id NUMBER;
2161 l_balance_defined_id NUMBER;
2162 l_VPF_value NUMBER;
2163 l_APF_value NUMBER;
2164 wage_value NUMBER;
2165 employee_pf_value NUMBER;
2166 pension_fund NUMBER;
2167 pf_sal NUMBER;
2168 l_epf_diff NUMBER;
2169 l_contribution_rate NUMBER;
2170 l_absence NUMBER;
2171 l_contr_period VARCHAR2(10);
2172 l_full_name per_people_f.full_name%TYPE;
2173 l_pf_number per_people_f.per_information8%TYPE;
2174 l_fh_hus per_people_f.full_name%TYPE;
2175 l_source_id hr_organization_units.organization_id%TYPE;
2176 l_pf_org_name hr_organization_units.name%TYPE;
2177 l_pension_num per_people_f.per_information13%TYPE;
2178 flag BOOLEAN;
2179 l_start_date DATE;
2180 l_end_date DATE;
2181 l_person_id NUMBER;
2182 l_asg_start_date VARCHAR2(30);
2183 l_exists per_people_f.per_information13%TYPE;
2184 l_pf_comp_salary NUMBER;
2185 l_pf_ytd NUMBER;
2186 l_excluded_employee_status NUMBER;
2187 l_contact_id NUMBER;
2188 l_procedure VARCHAR2(100);
2189 l_message VARCHAR2(255);
2190 l_contact_type per_contact_relationships.contact_type%TYPE;
2191 l_sex per_people_f.sex%TYPE;
2192 l_dob VARCHAR2(30);
2193 l_eps_wages NUMBER;
2194 l_term_date VARCHAR2(30);
2195 l_reason VARCHAR2(30);
2196 l_start_date_1 DATE;
2197 l_end_date_1 DATE;
2198 l_contr_period_1 VARCHAR2(10);
2199
2200
2201
2202
2203 BEGIN
2204
2205
2206 g_debug := hr_utility.debug_enabled;
2207 l_procedure := g_package ||'archive_form_data';
2208 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2209
2210 IF g_debug THEN
2211 pay_in_utils.trace('**************************************************','********************');
2212 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
2213 pay_in_utils.trace('Assignment id ',p_assignment_id);
2214 pay_in_utils.trace('Payroll Date ',p_payroll_date);
2215 pay_in_utils.trace('**************************************************','********************');
2216 END IF;
2217
2218
2219 --V
2220 FOR c_rec IN c_pf_org_id(p_assignment_action_id)
2221 LOOP
2222 pay_in_utils.set_location(g_debug,l_procedure, 20);
2223 l_source_id:=c_rec.source_id;
2224
2225 IF g_debug THEN
2226 pay_in_utils.trace('Source id ',l_source_id);
2227 END IF;
2228
2229 --A,B,C
2230 OPEN c_name_pfno_fh_name(p_assignment_action_id);
2231 FETCH c_name_pfno_fh_name INTO l_full_name,l_pf_number,l_pension_num,l_person_id,l_contact_id
2232 ,l_contact_type,l_sex,l_dob; ---added 14784847
2233 CLOSE c_name_pfno_fh_name;
2234
2235 OPEN csr_pf_people(l_source_id,p_assignment_id); ---added 14784847
2236 FETCH csr_pf_people INTO l_term_date,l_reason;
2237 CLOSE csr_pf_people;
2238
2239 IF g_debug THEN
2240 pay_in_utils.trace('l_term_date ',l_term_date);
2241 pay_in_utils.trace('l_reason ',l_reason);
2242 END IF;
2243
2244 pay_in_utils.set_location(g_debug,l_procedure, 30);
2245
2246 OPEN csr_contact_details(l_contact_id);
2247 FETCH csr_contact_details INTO l_fh_hus;
2248 CLOSE csr_contact_details;
2249
2250 IF g_debug THEN
2251 pay_in_utils.trace('Person id ',l_person_id);
2252 pay_in_utils.trace('Full Name ',l_full_name);
2253 pay_in_utils.trace('Contact id ',l_contact_id);
2254 END IF;
2255
2256
2257 -- Bug 4033745
2258 l_start_date := trunc(p_payroll_date,'MM');
2259 l_end_date := ADD_MONTHS(l_start_date,1) - 1;
2260
2261 IF g_debug THEN
2262 pay_in_utils.trace('Start Date ',l_start_date);
2263 pay_in_utils.trace('End Date ',l_end_date);
2264 END IF;
2265
2266 pay_in_utils.set_location(g_debug,l_procedure, 40);
2267
2268 OPEN csr_asg_effective_date(p_assignment_id,l_source_id,l_start_date,l_end_date);
2269 FETCH csr_asg_effective_date INTO l_asg_start_date;
2270 CLOSE csr_asg_effective_date;
2271
2272 l_asg_start_date := LEAST(l_asg_start_date,l_end_date);
2273
2274 IF g_debug THEN
2275 pay_in_utils.trace('Assignment Start Date ',l_asg_start_date);
2276 END IF;
2277
2278 pay_in_utils.set_location(g_debug,l_procedure, 50);
2279
2280 OPEN csr_pension_number(p_assignment_id,l_asg_start_date);
2281 FETCH csr_pension_number INTO l_pension_num,l_pf_number;
2282 CLOSE csr_pension_number;
2283
2284 -- Bug 4033745
2285 pay_in_utils.set_location(g_debug,l_procedure, 60);
2286
2287 IF (l_pf_number IS NULL) AND (l_pension_num IS NULL) THEN
2288 RETURN;
2289 END IF;
2290
2291 IF g_debug THEN
2292 pay_in_utils.trace('PF Number ',l_pf_number);
2293 pay_in_utils.trace('Pension Number ',l_pension_num);
2294 END IF;
2295
2296
2297 -- Hire date is dependent on pension number change and PF organization change. So, we will archive it only
2298 -- when pension number is entered.In case of PF org change followed by pension number change, the transfer-in date
2299 -- will be archived. Otherwise hire/rehire date will be used
2300
2301 --Pension Start
2302 pay_in_utils.set_location(g_debug,l_procedure, 70);
2303
2304 IF (l_pension_num IS NOT NULL AND l_source_id IS NOT NULL) THEN
2305 pay_in_utils.set_location(g_debug,l_procedure, 80);
2306 OPEN csr_asg_pforg_start(l_person_id,l_source_id);
2307 FETCH csr_asg_pforg_start INTO l_asg_start_date;
2308 CLOSE csr_asg_pforg_start;
2309
2310 IF g_debug THEN
2311 pay_in_utils.trace('Asg Earliest Start Date ',l_asg_start_date);
2312 END IF;
2313
2314
2315 OPEN csr_chk_trsfr_pforg(l_person_id
2316 ,l_source_id
2317 ,l_asg_start_date);
2318 FETCH csr_chk_trsfr_pforg INTO l_exists;
2319 CLOSE csr_chk_trsfr_pforg;
2320
2321 pay_in_utils.set_location(g_debug,l_procedure, 90);
2322
2323 IF l_exists IS NOT NULL THEN
2324 pay_in_utils.set_location(g_debug,l_procedure, 100);
2325 OPEN csr_chk_pension_number_change(l_person_id
2326 ,l_asg_start_date);
2327 FETCH csr_chk_pension_number_change INTO l_exists;
2328 CLOSE csr_chk_pension_number_change ;
2329 END IF;
2330
2331 IF g_debug THEN
2332 pay_in_utils.trace('Pension Change Exists ',l_exists);
2333 END IF;
2334
2335 pay_in_utils.set_location(g_debug,l_procedure, 120);
2336 IF (nvl(l_exists,l_pension_num) = l_pension_num) THEN
2337 OPEN csr_hire_date(l_person_id
2338 ,l_pension_num);
2339 FETCH csr_hire_date INTO l_asg_start_date;
2340 CLOSE csr_hire_date;
2341
2342 pay_in_utils.set_location(g_debug,l_procedure,130);
2343
2344 END IF;
2345
2346 END IF;
2347
2348 -- Bugfix 4270904
2349 l_excluded_employee_status := pay_in_ff_pkg.check_retainer(p_assignment_id,p_run_payroll_action_id);
2350
2351
2352 -- Pension End
2353 --
2354
2355 -- Populating the PL/SQL Tables
2356 flag:=TRUE;
2357 FOR i IN 1..g_cnt_pf LOOP
2358 IF g_pf_org_id(i)=l_source_id THEN
2359 flag:=FALSE;
2360 EXIT;
2361 END IF;
2362 END LOOP;
2363
2364 IF flag THEN
2365 g_cnt_pf := g_cnt_pf+1;
2366 g_pf_org_id(g_cnt_pf):=l_source_id;
2367 g_pa_act_id(g_cnt_pf):=p_payroll_action_id;
2368 END IF;
2369 --
2370 l_balance_defined_id :=0;
2371 --
2372 pay_in_utils.set_location(g_debug,l_procedure, 140);
2373 --F
2374 OPEN c_defined_balance_id('PF Actual Salary','_ASG_ORG_MAR_FEB_YTD');
2375 FETCH c_defined_balance_id INTO l_balance_defined_id;
2376 CLOSE c_defined_balance_id;
2377
2378 l_APF_value:=pay_balance_pkg.get_value(
2379 p_defined_balance_id =>l_balance_defined_id,
2380 p_assignment_action_id =>p_assignment_action_id,
2381 p_tax_unit_id => null,
2382 p_jurisdiction_code => null,
2383 p_source_id =>l_source_id,
2384 p_tax_group =>null,
2385 p_date_earned =>null);
2386
2387 pay_in_utils.set_location(g_debug,l_procedure, 150);
2388
2389 OPEN c_defined_balance_id('Employee Voluntary PF Contribution','_ASG_ORG_MAR_FEB_YTD');
2390 FETCH c_defined_balance_id INTO l_balance_defined_id;
2391 CLOSE c_defined_balance_id;
2392
2393 l_VPF_value:=pay_balance_pkg.get_value(
2394 p_defined_balance_id =>l_balance_defined_id,
2395 p_assignment_action_id =>p_assignment_action_id,
2396 p_tax_unit_id => null,
2397 p_jurisdiction_code => null,
2398 p_source_id =>l_source_id,
2399 p_tax_group =>null,
2400 p_date_earned =>null);
2401
2402 IF l_APF_value = 0 THEN
2403 l_contribution_rate:=0;
2404 ELSE
2405 l_contribution_rate:=round((l_VPF_value/l_APF_value)*100,2);
2406 END IF;
2407 pay_in_utils.set_location(g_debug,l_procedure, 160);
2408
2409 --H
2410 OPEN c_defined_balance_id('PF Actual Salary','_ASG_ORG_DE_PTD');
2411 FETCH c_defined_balance_id INTO l_balance_defined_id;
2412 CLOSE c_defined_balance_id;
2413
2414 wage_value :=pay_balance_pkg.get_value(
2415 p_defined_balance_id =>l_balance_defined_id,
2416 p_assignment_action_id =>p_assignment_action_id,
2417 p_tax_unit_id =>null,
2418 p_jurisdiction_code =>null,
2419 p_source_id =>l_source_id,
2420 p_tax_group =>null,
2421 p_date_earned =>null);
2422
2423 IF g_debug THEN
2424 pay_in_utils.trace('PF Actual Salary PTD ',wage_value);
2425 END IF;
2426 pay_in_utils.set_location(g_debug,l_procedure, 170);
2427
2428 --K
2429 OPEN c_defined_balance_id('EPS Contribution','_ASG_ORG_DE_PTD');
2430 FETCH c_defined_balance_id INTO l_balance_defined_id;
2431 CLOSE c_defined_balance_id;
2432
2433 pension_fund:=pay_balance_pkg.get_value(
2434 p_defined_balance_id =>l_balance_defined_id,
2435 p_assignment_action_id =>p_assignment_action_id,
2436 p_tax_unit_id =>null,
2437 p_jurisdiction_code =>null,
2438 p_source_id =>l_source_id,
2439 p_tax_group =>null,
2440 p_date_earned =>null);
2441
2442 IF g_debug THEN
2443 pay_in_utils.trace('PF Actual Salary PTD ',wage_value);
2444 END IF;
2445 pay_in_utils.set_location(g_debug,l_procedure, 180);
2446 --I,J
2447 OPEN c_defined_balance_id('Employee Statutory PF Contribution','_ASG_ORG_DE_PTD');
2448 FETCH c_defined_balance_id INTO l_balance_defined_id;
2449 CLOSE c_defined_balance_id;
2450
2451 employee_pf_value := pay_balance_pkg.get_value(
2452 p_defined_balance_id =>l_balance_defined_id,
2453 p_assignment_action_id =>p_assignment_action_id,
2454 p_tax_unit_id =>null,
2455 p_jurisdiction_code =>null,
2456 p_source_id =>l_source_id,
2457 p_tax_group =>null,
2458 p_date_earned =>null);
2459
2460 l_epf_diff:=employee_pf_value-pension_fund;
2461
2462 IF g_debug THEN
2463 pay_in_utils.trace('Employee Statutory PF Contribution PTD ',employee_pf_value);
2464 END IF;
2465 pay_in_utils.set_location(g_debug,l_procedure, 190);
2466
2467 OPEN c_defined_balance_id('Employee Voluntary PF Contribution','_ASG_ORG_DE_PTD');
2468 FETCH c_defined_balance_id INTO l_balance_defined_id;
2469 CLOSE c_defined_balance_id;
2470 pay_in_utils.set_location(g_debug,l_procedure, 200);
2471 employee_pf_value := employee_pf_value+pay_balance_pkg.get_value(
2472 p_defined_balance_id =>l_balance_defined_id,
2473 p_assignment_action_id =>p_assignment_action_id,
2474 p_tax_unit_id =>null,
2475 p_jurisdiction_code =>null,
2476 p_source_id =>l_source_id,
2477 p_tax_group =>null,
2478 p_date_earned =>null);
2479
2480 IF g_debug THEN
2481 pay_in_utils.trace('Employee Voluntary PF Contribution PTD ',employee_pf_value);
2482 END IF;
2483
2484
2485
2486 --M
2487 OPEN c_defined_balance_id('Non Contributory Period','_ASG_DE_PTD');
2488 FETCH c_defined_balance_id INTO l_balance_defined_id;
2489 CLOSE c_defined_balance_id;
2490 pay_in_utils.set_location(g_debug,l_procedure, 210);
2491 l_absence := pay_balance_pkg.get_value(
2492 p_defined_balance_id =>l_balance_defined_id,
2493 p_assignment_action_id =>p_assignment_action_id);
2494
2495 IF g_debug THEN
2496 pay_in_utils.trace('Absence ',l_absence);
2497 END IF;
2498
2499 /*added 14784847 start*/
2500 OPEN c_defined_balance_id('EPS Wages','_ASG_DE_PTD');
2501 FETCH c_defined_balance_id INTO l_balance_defined_id;
2502 CLOSE c_defined_balance_id;
2503
2504 IF g_debug THEN
2505 pay_in_utils.trace('EPS Wages l_balance_defined_id ',l_balance_defined_id);
2506 END IF;
2507
2508 pay_in_utils.set_location(g_debug,l_procedure, 215);
2509 l_eps_wages := pay_balance_pkg.get_value(
2510 p_defined_balance_id =>l_balance_defined_id,
2511 p_assignment_action_id =>p_assignment_action_id);
2512
2513 IF g_debug THEN
2514 pay_in_utils.trace('EPS Wages ',l_eps_wages);
2515 END IF;
2516 /*14784847 end*/
2517
2518 --N
2519
2520 l_asg_id:=p_assignment_id;
2521
2522 --PF Organization Name
2523 OPEN c_pf_name(l_source_id,p_payroll_date);
2524 FETCH c_pf_name INTO l_pf_org_name;
2525 CLOSE c_pf_name;
2526
2527 pay_in_utils.set_location(g_debug,l_procedure, 220);
2528
2529 --Contribution Period
2530 l_start_date := p_payroll_date;
2531 l_end_date := p_payroll_date;
2532 l_start_date_1 := p_payroll_date;
2533 l_end_date_1 := p_payroll_date;
2534 IF(to_number(to_char(l_start_date,'MM'))) = 3 THEN
2535 l_start_date:=add_months(l_start_date,1);
2536 l_end_date :=l_start_date;
2537 END IF;
2538
2539 l_contr_period:=to_char(pay_in_tax_utils.get_financial_year_start(l_start_date),'YYYY')||'-'||to_char(pay_in_tax_utils.get_financial_year_end(l_end_date),'YYYY');
2540 l_contr_period_1:=to_char(pay_in_tax_utils.get_financial_year_start(l_start_date_1),'YYYY')||'-'||to_char(pay_in_tax_utils.get_financial_year_end(l_end_date_1),'YYYY'); -- 14784847
2541
2542
2543 OPEN c_defined_balance_id('PF Computation Salary','_ASG_DE_PTD');
2544 FETCH c_defined_balance_id INTO l_balance_defined_id;
2545 CLOSE c_defined_balance_id;
2546
2547 pay_in_utils.set_location(g_debug,l_procedure, 230);
2548
2549 l_pf_comp_salary :=pay_balance_pkg.get_value(p_defined_balance_id =>l_balance_defined_id,
2550 p_assignment_action_id =>p_assignment_action_id);
2551
2552 IF g_debug THEN
2553 pay_in_utils.trace('PF Computation Salary PTD ',l_pf_comp_salary);
2554 END IF;
2555 pay_in_utils.set_location(g_debug,l_procedure, 240);
2556
2557 pay_action_information_api.create_action_information
2558 (p_action_context_id => p_archive_action_id --Archive Action id
2559 ,p_action_context_type => 'AAP'
2560 ,p_action_information_category => 'IN_PF_ASG'
2561 ,p_tax_unit_id => null
2562 ,p_jurisdiction_code => null
2563 ,p_source_id => null
2564 ,p_source_text => null
2565 ,p_tax_group => null
2566 ,p_effective_date => p_prepayment_date --Prepayment Effective Date
2567 ,p_assignment_id => l_asg_id --Asg Id
2568 ,p_action_information1 => l_contr_period --Contribution Period
2569 ,p_action_information2 => l_source_id --PF Organization
2570 ,p_action_information3 => l_pf_number --PF Number
2571 ,p_action_information4 => l_full_name --Full Name
2572 ,p_action_information5 => l_fh_hus --Father/Husband Name
2573 ,p_action_information6 => l_contribution_rate --Voluntary Higher Contr Rate
2574 ,p_action_information7 => wage_value --PF Salary _ASG_ORG_DE_PTD
2575 ,p_action_information8 => employee_pf_value --Total Employee Contr
2576 ,p_action_information9 => l_epf_diff --Employer Contr towards PF
2577 ,p_action_information10 => pension_fund --Employer Contr towards Pension
2578 ,p_action_information11 => l_absence --Absence
2579 -- ,p_action_information12 => l_remarks --Remarks
2580 ,p_action_information13 => p_payroll_date --Payroll Date
2581 ,p_action_information14 => l_pf_org_name --PF Org Name
2582 ,p_action_information15 => l_pension_num --Pension Number
2583 ,p_action_information16 => l_asg_start_date --Hire Date
2584 ,p_action_information17 => l_pf_comp_salary --PF Computation Salary
2585 ,p_action_information18 => l_excluded_employee_status -- Excluded Employee status
2586 ,p_action_information19 => null
2587 ,p_action_information20 => null
2588 ,p_action_information21 => null
2589 ,p_action_information22 => null
2590 ,p_action_information23 => null
2591 ,p_action_information24 => l_contr_period_1 -- ECR Contribution period
2592 ,p_action_information25 => l_term_date -- Termination Date
2593 ,p_action_information26 => l_reason -- Termination Reason
2594 ,p_action_information27 => l_eps_wages -- EPS Wages
2595 ,p_action_information28 => l_dob -- Date of birth
2596 ,p_action_information29 => l_contact_type -- Contact Type
2597 ,p_action_information30 => l_sex -- Gender
2598 ,p_action_information_id => l_action_info_id --OUT Parameters
2599 ,p_object_version_number => l_ovn --OUT Parameters
2600 );
2601 pay_in_utils.set_location(g_debug,l_procedure, 250);
2602 END LOOP;
2603
2604 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 260);
2605
2606 EXCEPTION
2607 WHEN OTHERS THEN
2608 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2609 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 270);
2610 pay_in_utils.trace(l_message,l_procedure);
2611 RAISE;
2612
2613 END archive_form_data;
2614
2615 ---------------------------------------------------------------------------
2616 -- --
2617 -- Name : ARCHIVE_ESI_DATA --
2618 -- Type : PROCEDURE --
2619 -- Access : Public --
2620 -- Description : This procedure archives the data required for form 6--
2621 -- Parameters : --
2622 -- IN : p_assignment_action_id NUMBER --
2623 -- p_payroll_action_id NUMBER --
2624 -- p_archive_action_id NUMBER --
2625 -- p_assignment_id NUMBER --
2626 -- p_payroll_date DATE --
2627 -- p_prepayment_date DATE --
2628 -- --
2629 -- --
2630 -- OUT : N/A --
2631 -- --
2632 -- Change History : --
2633 --------------------------------------------------------------------------
2634 -- Rev# Date Userid Description --
2635 --------------------------------------------------------------------------
2636 -- 115.0 10-Mar-2005 aaagarwa Initial Version --
2637 -- 115.1 01-Apr-2005 abhjain Removed the archival of remarks --
2638 -- 115.2 04-Sep-2008 lnagaraj Added for disabled employee --
2639 --------------------------------------------------------------------------
2640 --
2641 PROCEDURE archive_esi_data
2642 (
2643 p_assignment_action_id IN NUMBER
2644 ,p_payroll_action_id IN NUMBER
2645 ,p_archive_action_id IN NUMBER
2646 ,p_assignment_id IN NUMBER
2647 ,p_payroll_date IN DATE
2648 ,p_prepayment_date IN DATE
2649 )
2650 IS
2651 -- Cursor to find ESI Organization Id
2652 CURSOR c_esi_org_id(p_assignment_action_id NUMBER)
2653 IS
2654 SELECT DISTINCT hoi.organization_id source_id
2655 FROM hr_organization_units hoi
2656 ,hr_soft_coding_keyflex scf
2657 ,per_assignments_f asg
2658 ,pay_assignment_actions paa
2659 WHERE asg.assignment_id=paa.assignment_id
2660 AND paa.assignment_action_id=p_assignment_action_id
2661 AND asg.SOFT_CODING_KEYFLEX_ID=scf.SOFT_CODING_KEYFLEX_ID
2662 AND hoi.ORGANIZATION_ID=scf.segment4
2663 AND (to_char(asg.effective_start_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
2664 OR to_char(asg.effective_end_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
2665 OR p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2666 );
2667
2668 CURSOR csr_hire_date
2669 IS
2670 SELECT SERVICE.date_start
2671 FROM
2672 /* Person current period of service date details */
2673 per_all_assignments_f ASSIGN
2674 , per_periods_of_service SERVICE
2675 WHERE p_payroll_date BETWEEN ASSIGN.effective_start_date
2676 AND ASSIGN.effective_end_date
2677 AND ASSIGN.assignment_id = p_assignment_id
2678 AND SERVICE.period_of_Service_id = ASSIGN.period_of_service_id;
2679
2680 -- Cursor to find full name, ESI number, Person Id
2681 CURSOR c_name_esino_fh_name(p_assignment_action_id NUMBER)
2682 IS
2683 SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
2684 ,pep.per_information9 ESI_Number
2685 ,pep.person_id person_id
2686 FROM per_assignments_f asg
2687 ,per_people_f pep
2688 ,pay_assignment_actions paa
2689 WHERE asg.PERSON_ID=pep.person_id
2690 AND asg.assignment_id=paa.assignment_id
2691 AND paa.assignment_action_id= p_assignment_action_id
2692 AND p_payroll_date BETWEEN pep.effective_start_date AND pep.effective_end_date
2693 AND p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
2694
2695 --Cursor to find balance value
2696 CURSOR c_defined_balance_id(p_balance_name VARCHAR2
2697 ,p_dimension VARCHAR2)
2698 IS
2699 SELECT pdb.defined_balance_id
2700 FROM pay_balance_types pbt
2701 ,pay_balance_dimensions pbd
2702 ,pay_defined_balances pdb
2703 WHERE pbt.balance_name=p_balance_name
2704 AND pbd.dimension_name=p_dimension
2705 AND pbt.legislation_code = 'IN'
2706 AND pbd.legislation_code = 'IN'
2707 AND pbt.balance_type_id = pdb.balance_type_id
2708 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
2709
2710
2711 --Cursor to find the remarks entered by the user in that payroll run
2712
2713 --Find the element type id
2714 CURSOR c_element_type_id
2715 IS
2716 SELECT element_type_id
2717 FROM pay_element_types_f
2718 WHERE element_name='ESI Information'
2719 AND legislation_code = 'IN'
2720 AND p_payroll_date between effective_start_date and effective_end_date;
2721
2722 --Find the input value id for Reason for Exemption, Organization and Remarks
2723 CURSOR c_iv_id(p_element_type_id NUMBER,p_name VARCHAR2)
2724 IS
2725 SELECT input_value_id
2726 FROM pay_input_values_f
2727 WHERE element_type_id = p_element_type_id
2728 AND name = p_name
2729 AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
2730
2731 --Find the run result id
2732 CURSOR c_run_result_id(p_element_type_id NUMBER)
2733 IS
2734 SELECT run_result_id
2735 FROM pay_run_results
2736 WHERE assignment_action_id=p_assignment_action_id
2737 AND element_type_id=p_element_type_id;
2738
2739 --Find the actual remarks
2740 CURSOR c_remarks(p_run_result_id NUMBER,p_org_iv_id NUMBER,p_rem_iv_id NUMBER,p_org_id NUMBER)
2741 IS
2742 SELECT prr2.result_value
2743 FROM pay_run_result_values prr1
2744 ,pay_run_result_values prr2
2745 WHERE prr1.run_result_id = p_run_result_id
2746 AND prr1.input_value_id = p_org_iv_id
2747 AND prr2.run_result_id = prr1.run_result_id
2748 AND prr2.input_value_id = p_rem_iv_id
2749 AND prr1.result_value = p_org_id;
2750
2751 --Cursor to find the ESI Organization Name
2752 Cursor c_esi_name(p_organization_id NUMBER
2753 ,p_effective_date DATE)
2754 IS
2755 SELECT hou.name
2756 FROM hr_organization_units hou
2757 WHERE hou.organization_id=p_organization_id
2758 AND p_effective_date BETWEEN hou.date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
2759
2760 --Cursor to find date of death
2761 CURSOR c_death_date(p_person_id NUMBER
2762 ,p_effective_date DATE)
2763 IS
2764 SELECT '1'
2765 FROM per_people_f
2766 WHERE person_id= p_person_id
2767 AND date_of_death <= p_effective_date ;
2768
2769 --Cursor to find termination status
2770 CURSOR c_term_check(p_person_id NUMBER
2771 ,p_effective_date DATE)
2772 IS
2773 SELECT '1'
2774 FROM per_periods_of_service
2775 WHERE actual_termination_date <= p_effective_date
2776 AND person_id = p_person_id
2777 AND date_start = (SELECT MAX(TO_DATE(date_start,'DD-MM-YY'))
2778 FROM per_periods_of_service
2779 WHERE person_id = p_person_id
2780 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2781 );
2782
2783 --Cursor to find the asg end date
2784 CURSOR csr_asg_effective_date(p_assignment_id NUMBER
2785 ,p_source_id NUMBER
2786 ,p_pay_start DATE
2787 ,p_pay_end DATE)
2788 IS
2789 SELECT MAX(paf.effective_end_date)
2790 FROM per_assignments_f paf
2791 ,hr_soft_coding_keyflex scl
2792 WHERE paf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
2793 AND paf.assignment_id=p_assignment_id
2794 AND scl.segment4 = p_source_id
2795 AND paf.effective_start_date <= p_pay_end
2796 AND paf.effective_end_date >= p_pay_start;
2797
2798 --Cursor to find ESI Number at asg date
2799 CURSOR csr_esi_number(p_assigment_id NUMBER
2800 ,p_date DATE)
2801 IS
2802 SELECT ppf.per_information9 ESI_Number
2803 FROM per_people_f ppf
2804 ,per_assignments_f paf
2805 WHERE ppf.person_id = paf.person_id
2806 AND paf.assignment_id = p_assignment_id
2807 AND p_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2808 AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
2809
2810 --------Cursor ---to get the ceiling value-------
2811 CURSOR c_esi_ceiling_value(c_global_value VARCHAR2)
2812 IS
2813 SELECT global_value
2814 FROM ff_globals_f
2815 WHERE legislation_code='IN'
2816 AND global_name = c_global_value
2817 AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
2818
2819
2820 CURSOR csr_emplr_class IS
2821 SELECT target.org_information3 FROM
2822 per_all_assignments_f assign,
2823 hr_soft_coding_keyflex scl,
2824 hr_organization_information target
2825 WHERE assign.assignment_id = p_assignment_id
2826 AND p_payroll_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
2827 AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2828 AND scl.segment1 = target.organization_id
2829 AND target.org_information_context = 'PER_IN_INCOME_TAX_DF';
2830
2831
2832
2833
2834 l_action_info_id NUMBER;
2835 l_ovn NUMBER;
2836 l_asg_id NUMBER;
2837 l_balance_defined_id NUMBER;
2838 wage_value NUMBER;
2839 l_esi_ytd NUMBER;
2840 l_employee_contribution NUMBER;
2841 l_employer_contribution NUMBER;
2842 l_absence NUMBER;
2843 l_contr_period VARCHAR2(30);
2844 l_full_name per_people_f.full_name%TYPE;
2845 l_person_id per_people_f.person_id%TYPE;
2846 l_esi_number per_people_f.per_information9%TYPE;
2847 l_source_id hr_organization_units.organization_id%TYPE;
2848 l_esi_org_name hr_organization_units.name%TYPE;
2849 flag BOOLEAN;
2850 l_reason_for_exem VARCHAR2(60);
2851 l_exempted_frm_esi VARCHAR2(5);
2852 l_temp VARCHAR2(5);
2853 l_element_type_id NUMBER;
2854 l_org_iv_id NUMBER;
2855 l_reason_iv_id NUMBER;
2856 l_start_date DATE;
2857 l_end_date DATE;
2858 l_asg_start_date DATE;
2859 l_eligible_salary NUMBER ;
2860 l_esi_ceiling_value NUMBER ;
2861 l_esi_con_salary NUMBER ;
2862 l_hire_date DATE;
2863 l_disable_proof VARCHAR2(10);
2864 l_emplr_class hr_organization_information.org_information3%TYPE;
2865 l_global_value ff_globals_f.global_name%TYPE;
2866 l_dummy NUMBER;
2867
2868 l_message VARCHAR2(255);
2869 l_procedure VARCHAR2(100);
2870
2871 BEGIN
2872
2873 g_debug := hr_utility.debug_enabled;
2874 l_procedure := g_package ||'archive_esi_data';
2875 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2876
2877
2878 IF g_debug THEN
2879 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
2880 pay_in_utils.trace('Payroll Action id ',p_payroll_action_id);
2881 pay_in_utils.trace('Archive Action id ',p_archive_action_id);
2882 pay_in_utils.trace('Assignment id ',p_assignment_id);
2883 pay_in_utils.trace('Payroll Date ',p_payroll_date);
2884 pay_in_utils.trace('Prepayment_date ',p_prepayment_date);
2885 END IF;
2886
2887
2888 --Determine the distinct ESI Organizations for that assignment in that payroll run
2889 FOR c_rec IN c_esi_org_id(p_assignment_action_id)
2890 LOOP
2891
2892 l_source_id:=c_rec.source_id;
2893 IF g_debug THEN
2894 pay_in_utils.trace('ESI Organization id ',l_source_id);
2895 END IF;
2896 pay_in_utils.set_location(g_debug,l_procedure, 20);
2897
2898 --Find the assignments's full name and esi number
2899 OPEN c_name_esino_fh_name(p_assignment_action_id);
2900 FETCH c_name_esino_fh_name INTO l_full_name,l_esi_number,l_person_id;
2901 CLOSE c_name_esino_fh_name;
2902
2903 l_start_date := TRUNC(p_payroll_date,'MM');
2904 l_end_date := ADD_MONTHS(l_start_date,1) - 1;
2905
2906 pay_in_utils.set_location(g_debug,l_procedure, 20);
2907
2908 OPEN csr_asg_effective_date(p_assignment_id,l_source_id,l_start_date,l_end_date);
2909 FETCH csr_asg_effective_date INTO l_asg_start_date;
2910 CLOSE csr_asg_effective_date;
2911
2912 pay_in_utils.set_location(g_debug,l_procedure, 30);
2913
2914 l_asg_start_date := LEAST(l_asg_start_date,l_end_date);
2915
2916 IF g_debug THEN
2917 pay_in_utils.trace('Assignment Start Date ',l_asg_start_date);
2918 pay_in_utils.trace('End Date ',l_end_date);
2919 END IF;
2920
2921 OPEN csr_esi_number(p_assignment_id,l_asg_start_date);
2922 FETCH csr_esi_number INTO l_esi_number;
2923 CLOSE csr_esi_number;
2924
2925 pay_in_utils.set_location(g_debug,l_procedure, 40);
2926
2927
2928 IF (l_esi_number IS NULL)THEN
2929 pay_in_utils.set_location(g_debug,l_procedure, 50);
2930 RETURN;
2931 END IF;
2932
2933 IF g_debug THEN
2934 pay_in_utils.trace('ESI Number ',l_esi_number);
2935 END IF;
2936
2937
2938 -- Populating the PL/SQL Tables with ESI Number and corresponding payroll action id
2939 flag:=TRUE;
2940
2941 pay_in_utils.set_location(g_debug,l_procedure, 60);
2942
2943 FOR i IN 1..g_cnt_esi LOOP
2944 pay_in_utils.set_location(g_debug,l_procedure, 70);
2945 IF g_esi_org_id(i)=l_source_id THEN
2946 flag:=FALSE;
2947 EXIT;
2948 END IF;
2949 END LOOP;
2950 pay_in_utils.set_location(g_debug,l_procedure, 80);
2951 IF flag THEN
2952 g_cnt_esi:=g_cnt_esi+1;
2953 g_esi_org_id(g_cnt_esi):=l_source_id;
2954 g_esi_act_id(g_cnt_esi):=p_payroll_action_id;
2955
2956 IF g_debug THEN
2957 pay_in_utils.trace('g_esi_org_id table ',g_esi_org_id(g_cnt_esi));
2958 END IF;
2959
2960 END IF;
2961 --
2962 l_balance_defined_id :=0;
2963 --
2964 pay_in_utils.set_location(g_debug,l_procedure, 90);
2965 --ESI Actual Salary for dimension _ASG_ORG_HYTD
2966 OPEN c_defined_balance_id('ESI Actual Salary','_ASG_ORG_HYTD');
2967 FETCH c_defined_balance_id INTO l_balance_defined_id;
2968 CLOSE c_defined_balance_id;
2969 pay_in_utils.set_location(g_debug,l_procedure, 90);
2970
2971 wage_value := pay_balance_pkg.get_value(
2972 p_defined_balance_id => l_balance_defined_id,
2973 p_assignment_action_id => p_assignment_action_id,
2974 p_tax_unit_id => null,
2975 p_jurisdiction_code => null,
2976 p_source_id => l_source_id,
2977 p_tax_group => null,
2978 p_date_earned => null
2979 );
2980
2981 IF g_debug THEN
2982 pay_in_utils.trace('ESI Wages ',wage_value);
2983 END IF;
2984
2985 --Employee Contribution
2986 OPEN c_defined_balance_id('Employee ESI Contribution','_ASG_ORG_HYTD');
2987 FETCH c_defined_balance_id INTO l_balance_defined_id;
2988 CLOSE c_defined_balance_id;
2989
2990 pay_in_utils.set_location(g_debug,l_procedure, 100);
2991
2992 l_employee_contribution :=pay_balance_pkg.get_value(
2993 p_defined_balance_id =>l_balance_defined_id,
2994 p_assignment_action_id =>p_assignment_action_id,
2995 p_tax_unit_id => null,
2996 p_jurisdiction_code => null,
2997 p_source_id =>l_source_id,
2998 p_tax_group =>null,
2999 p_date_earned =>null);
3000
3001 IF g_debug THEN
3002 pay_in_utils.trace('ESI Employee Contribution ',l_employee_contribution);
3003 END IF;
3004
3005
3006 --Employer Contribution
3007 OPEN c_defined_balance_id('Employer ESI Contribution','_ASG_ORG_HYTD');
3008 FETCH c_defined_balance_id INTO l_balance_defined_id;
3009 CLOSE c_defined_balance_id;
3010
3011 pay_in_utils.set_location(g_debug,l_procedure, 110);
3012
3013 l_employer_contribution :=pay_balance_pkg.get_value(
3014 p_defined_balance_id =>l_balance_defined_id,
3015 p_assignment_action_id =>p_assignment_action_id,
3016 p_tax_unit_id =>null,
3017 p_jurisdiction_code =>null,
3018 p_source_id =>l_source_id,
3019 p_tax_group =>null,
3020 p_date_earned =>null);
3021 IF g_debug THEN
3022 pay_in_utils.trace('ESI Employer Contribution ',l_employer_contribution);
3023 END IF;
3024
3025
3026
3027 --Absence
3028 OPEN c_defined_balance_id('Non Contributory Period','_ASG_RUN');
3029 FETCH c_defined_balance_id INTO l_balance_defined_id;
3030 CLOSE c_defined_balance_id;
3031
3032 pay_in_utils.set_location(g_debug,l_procedure, 100);
3033
3034 l_absence := pay_balance_pkg.get_value(
3035 p_defined_balance_id =>l_balance_defined_id,
3036 p_assignment_action_id =>p_assignment_action_id
3037 );
3038
3039 IF g_debug THEN
3040 pay_in_utils.trace('Employee absence ',l_absence);
3041 END IF;
3042
3043
3044
3045 ----------get ESI Eligible salary-------------
3046
3047 OPEN c_defined_balance_id('ESI Eligible Salary','_ASG_DE_PTD');
3048 FETCH c_defined_balance_id INTO l_balance_defined_id;
3049 CLOSE c_defined_balance_id;
3050
3051 pay_in_utils.set_location(g_debug,l_procedure, 120);
3052
3053
3054
3055 l_eligible_salary := pay_balance_pkg.get_value(
3056 p_defined_balance_id => l_balance_defined_id,
3057 p_assignment_action_id => p_assignment_action_id,
3058 p_tax_unit_id => null,
3059 p_jurisdiction_code => null,
3060 p_source_id => null,
3061 p_tax_group => null,
3062 p_date_earned => null
3063 );
3064
3065 IF g_debug THEN
3066 pay_in_utils.trace('Esi Eligible Salary ',l_eligible_salary);
3067 END IF;
3068
3069
3070 --Reason for Exemption, Remarks and ESI Coverage Status
3071 l_temp := NULL;
3072 OPEN c_death_date(l_person_id,p_payroll_date);
3073 FETCH c_death_date INTO l_temp;
3074 CLOSE c_death_date;
3075
3076 pay_in_utils.set_location(g_debug,l_procedure, 130);
3077
3078 OPEN c_term_check(l_person_id,p_payroll_date);
3079 FETCH c_term_check INTO l_temp;
3080 CLOSE c_term_check;
3081
3082 pay_in_utils.set_location(g_debug,l_procedure, 140);
3083
3084 l_asg_id := p_assignment_id;
3085 -- l_remarks := NULL;
3086 l_reason_for_exem := NULL;
3087
3088 OPEN c_element_type_id;
3089 FETCH c_element_type_id INTO l_element_type_id;
3090 CLOSE c_element_type_id;
3091
3092 OPEN c_iv_id(l_element_type_id,'Organization');
3093 FETCH c_iv_id INTO l_org_iv_id;
3094 CLOSE c_iv_id;
3095
3096 OPEN c_iv_id(l_element_type_id,'Reason for Exemption');
3097 FETCH c_iv_id INTO l_reason_iv_id;
3098 CLOSE c_iv_id;
3099
3100
3101 pay_in_utils.set_location(g_debug,l_procedure, 150);
3102
3103 FOR c_rec IN c_run_result_id(l_element_type_id)
3104 LOOP
3105
3106 pay_in_utils.set_location(g_debug,l_procedure, 160);
3107
3108 OPEN c_remarks(c_rec.run_result_id,l_org_iv_id,l_reason_iv_id,l_source_id);
3109 FETCH c_remarks INTO l_reason_for_exem;
3110 CLOSE c_remarks;
3111
3112 END LOOP;
3113
3114 IF g_debug THEN
3115 pay_in_utils.trace('ESI Reason for Exemption ',l_reason_for_exem);
3116 END IF;
3117
3118 IF (l_reason_for_exem IS NOT NULL)
3119 OR
3120 (l_employer_contribution = 0 AND l_employee_contribution = 0 )
3121 OR
3122 (l_temp IS NOT NULL )
3123 THEN
3124 l_exempted_frm_esi:='Yes';
3125 ELSE
3126 l_exempted_frm_esi:='No';
3127 END IF;
3128
3129 --ESI Organization Name
3130 OPEN c_esi_name(l_source_id,p_payroll_date);
3131 FETCH c_esi_name INTO l_esi_org_name;
3132 CLOSE c_esi_name;
3133 pay_in_utils.set_location(g_debug,l_procedure, 170);
3134
3135
3136
3137 -----get salary in a specified contribution period ---------
3138
3139 l_esi_con_salary := pay_in_ff_pkg.get_esi_cont_amt(p_assignment_action_id =>p_assignment_action_id
3140 ,p_assignment_id =>p_assignment_id
3141 ,p_date_earned =>p_payroll_date
3142 ,p_eligible_amt =>l_eligible_salary
3143 );
3144
3145 IF g_debug THEN
3146 pay_in_utils.trace('Eligibility Salary in Contribution Period ',l_esi_con_salary);
3147 END IF;
3148
3149 l_dummy := pay_in_ff_pkg.get_esi_disability_details(p_assignment_id => p_assignment_id
3150 ,p_date_earned => p_payroll_date
3151 ,p_disable_proof => l_disable_proof);
3152
3153 --Esi Ceiling Value--
3154 ------ESI eligible salary------
3155
3156
3157
3158
3159 OPEN csr_hire_date;
3160 FETCH csr_hire_date INTO l_hire_date;
3161 CLOSE csr_hire_date;
3162
3163 OPEN csr_emplr_class;
3164 FETCH csr_emplr_class INTO l_emplr_class;
3165 CLOSE csr_emplr_class;
3166
3167
3168
3169 IF(l_hire_date >= to_date('01-04-2008','DD-mm-yyyy') and
3170 l_emplr_class IN('NSCG' , 'FIRM' , 'OTHR') and
3171 l_disable_proof = 'Y') THEN
3172 l_global_value := 'IN_ESI_DISABLED_WAGE_CEILING';
3173 ELSE
3174 l_global_value := 'IN_ESI_ELIGIBILITY_WAGE_CEILING';
3175
3176 END IF;
3177
3178
3179 OPEN c_esi_ceiling_value(l_global_value);
3180 FETCH c_esi_ceiling_value INTO l_esi_ceiling_value;
3181 CLOSE c_esi_ceiling_value;
3182
3183
3184 pay_in_utils.set_location(g_debug,l_procedure, 180);
3185
3186 IF (l_esi_con_salary > l_esi_ceiling_value)THEN
3187 RETURN ;
3188 END IF ;
3189
3190 pay_in_utils.set_location(g_debug,l_procedure, 190);
3191
3192
3193 --Contribution Period
3194 IF(to_number(to_char(p_payroll_date,'MM'))) > 3 AND (to_number(to_char(p_payroll_date,'MM'))) < 10 THEN
3195 l_contr_period :='Apr-'||to_char(p_payroll_date,'YYYY')||' - '||'Sep-'||to_char(p_payroll_date,'YYYY');
3196 ELSE
3197 l_contr_period :='Oct-'||to_char(pay_in_tax_utils.get_financial_year_start(p_payroll_date),'YYYY')||' - '||'Mar-'||to_char(pay_in_tax_utils.get_financial_year_end(p_payroll_date),'YYYY');
3198 END IF;
3199 --ESI Coverage
3200
3201 IF g_debug THEN
3202 pay_in_utils.trace('Contribution Period End ',l_contr_period);
3203 END IF;
3204
3205
3206 pay_action_information_api.create_action_information
3207 (p_action_context_id => p_archive_action_id --Archive Action id
3208 ,p_action_context_type => 'AAP'
3209 ,p_action_information_category => 'IN_ESI_ASG'
3210 ,p_tax_unit_id => null
3211 ,p_jurisdiction_code => null
3212 ,p_source_id => null
3213 ,p_source_text => null
3214 ,p_tax_group => null
3215 ,p_effective_date => p_prepayment_date --Prepayment Effective Date
3216 ,p_assignment_id => l_asg_id --Asg Id
3217 ,p_action_information1 => l_contr_period --Contribution Period
3218 ,p_action_information2 => l_source_id --ESI Organization
3219 ,p_action_information3 => l_esi_number --ESI Number
3220 ,p_action_information4 => l_full_name --Full Name
3221 ,p_action_information5 => l_absence --Absence
3222 ,p_action_information6 => wage_value --ESI Salary _ASG_ORG_DE_PTD
3223 ,p_action_information7 => l_employee_contribution--Employee Contribution
3224 ,p_action_information8 => l_employer_contribution--Employer Contribution
3225 ,p_action_information9 => l_exempted_frm_esi --ESI Coverage
3226 -- ,p_action_information10 => l_remarks --Remarks
3227 ,p_action_information11 => p_payroll_date --Payroll Date
3228 ,p_action_information12 => null
3229 ,p_action_information13 => l_esi_org_name --Local ESI Office
3230 ,p_action_information14 => null
3231 ,p_action_information15 => null
3232 ,p_action_information16 => null
3233 ,p_action_information17 => null
3234 ,p_action_information18 => null
3235 ,p_action_information19 => null
3236 ,p_action_information20 => null
3237 ,p_action_information21 => null
3238 ,p_action_information22 => null
3239 ,p_action_information23 => null
3240 ,p_action_information24 => null
3241 ,p_action_information25 => null
3242 ,p_action_information26 => null
3243 ,p_action_information27 => null
3244 ,p_action_information28 => null
3245 ,p_action_information29 => null
3246 ,p_action_information30 => null
3247 ,p_action_information_id => l_action_info_id --OUT Parameters
3248 ,p_object_version_number => l_ovn --OUT Parameters
3249 );
3250 pay_in_utils.set_location(g_debug,l_procedure, 200);
3251
3252 END LOOP;
3253 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 210);
3254
3255 EXCEPTION
3256 WHEN OTHERS THEN
3257 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3258 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 220);
3259 pay_in_utils.trace(l_message,l_procedure);
3260 RAISE;
3261 END archive_esi_data;
3262
3263 --------------------------------------------------------------------------
3264 -- --
3265 -- Name : ARCHIVE_PAYROLL_DATA --
3266 -- Type : PROCEDURE --
3267 -- Access : Public --
3268 -- Description : This procedure archives the data required for PF form-
3269 -- 3A,6A and ESI Form 6 data at PA Level --
3270 -- Parameters : --
3271 -- IN : p_context VARCHAR2 --
3272 -- --
3273 -- OUT : N/A --
3274 -- --
3275 -- Change History : --
3276 --------------------------------------------------------------------------
3277 -- Rev# Date Userid Description --
3278 --------------------------------------------------------------------------
3279 -- 115.0 01-Mar-2005 aaagarwa Initial Version --
3280 -- 115.1 07-Mar-2005 aaagarwa Modified it to handle ESI Org's also --
3281 -- 115.2 22-Apr-2005 sukukuma Added new join condition to the --
3282 -- cursor c_rep_adr to show --
3283 -- resedential address --
3284 -- 115.3 25-Sep-2007 rsaharay Modified c_rep_pos --
3285 --------------------------------------------------------------------------
3286 PROCEDURE archive_payroll_data(p_context IN VARCHAR2)
3287 IS
3288 --Cursor to find the effective date
3289 CURSOR c_effective_date(p_payroll_action_id NUMBER)
3290 IS
3291 SELECT effective_date
3292 FROM pay_payroll_actions
3293 WHERE payroll_action_id=p_payroll_action_id;
3294
3295 --Cursor to find the PF/ESI Organization Name
3296 Cursor c_org_name(p_organization_id NUMBER
3297 ,p_effective_date DATE)
3298 IS
3299 SELECT hou.name
3300 FROM hr_organization_units hou
3301 WHERE hou.organization_id=p_organization_id
3302 AND p_effective_date BETWEEN hou.date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
3303
3304 --Cursor to find the Registered name, Code of the PF/ESI Organization , Classification and its address
3305 CURSOR c_registered_name(p_organization_id NUMBER
3306 ,p_effective_date DATE)
3307 IS
3308 SELECT hou.name
3309 ,hoi.org_information1
3310 ,hoi.org_information3
3311 ,substr(
3312 hla.address_line_1||
3313 decode(hla.address_line_2,null,null,','||hla.address_line_2)||
3314 decode(hla.address_line_3,null,null,','||hla.address_line_3)||
3315 decode(hla.loc_information14,null,null,','||hla.loc_information14)||
3316 decode(hla.loc_information15,null,null,','||hla.loc_information15)||
3317 decode(hr_general.decode_lookup('IN_STATES',hla.loc_information16),null,null,','||hr_general.decode_lookup('IN_STATES',hla.loc_information16))||
3318 decode(hla.postal_code,null,null,','||hla.postal_code)
3319 ,1,240)
3320 FROM hr_organization_information hoi
3321 ,hr_organization_units hou
3322 ,hr_organization_units hou1
3323 ,hr_locations_all hla
3324 WHERE hoi.organization_id=p_organization_id
3325 AND hoi.org_information_context = DECODE(p_context,'PF','PER_IN_PF_DF','ESI','PER_IN_ESI_DF')
3326 AND hou.organization_id = DECODE (p_context,'PF',hoi.org_information8,'ESI',hoi.org_information2)
3327 AND hla.location_id=hou1.location_id
3328 AND hou.organization_id=hou1.organization_id
3329 AND p_effective_date BETWEEN hou.date_from AND nvl(hou.date_to,to_date('31-12-4712','DD-MM-YYYY'));
3330
3331 --Cursor to find the PF/ESI Representative Name
3332 CURSOR c_rep_name(p_pf_org_id NUMBER
3333 ,p_effective_date DATE)
3334 IS
3335 SELECT DISTINCT hr_in_utility.per_in_full_name(peap.first_name,peap.middle_names,peap.last_name,peap.title) rep_name
3336 ,peap.person_id person_id
3337 FROM hr_organization_information hoi
3338 ,per_people_f peap
3339 WHERE hoi.ORGANIZATION_ID=p_pf_org_id
3340 AND hoi.ORG_INFORMATION_CONTEXT = DECODE (p_context,'PF','PER_IN_PF_REP_DF','ESI','PER_IN_ESI_REP_DF')
3341 AND peap.person_id=hoi.ORG_INFORMATION1
3342 AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
3343 AND NVL(fnd_date.canonical_to_date(hoi.org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'));
3344
3345 -- Cursor to find ESI Rep's Address
3346 CURSOR c_rep_adr(p_person_id NUMBER
3347 ,p_effective_date DATE)
3348 IS
3349 SELECT SUBSTR(
3350 address_line1||
3351 DECODE(address_line2,NULL,NULL,','||address_line2)||
3352 DECODE(address_line3,NULL,NULL,','||address_line3)||
3353 DECODE(add_information13,NULL,NULL,','||add_information13)||
3354 DECODE(add_information14,NULL,NULL,','||add_information14)||
3355 DECODE(hr_general.decode_lookup('IN_STATES',add_information15),NULL,NULL,','||hr_general.decode_lookup('IN_STATES',add_information15))||
3356 DECODE(postal_code,NULL,NULL,','||postal_code)
3357 ,1,240) address
3358 FROM per_addresses
3359 WHERE person_id=p_person_id
3360 AND address_type='HK_R'
3361 AND p_effective_date BETWEEN date_from AND nvl(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
3362
3363 --Cursor to find the Position of ESI Rep
3364 /*Bug 4278673*/
3365 CURSOR c_rep_pos(p_person_id NUMBER
3366 ,p_effective_date DATE)
3367 IS
3368 SELECT nvl(pos.name,job.name) name
3369 FROM per_positions pos
3370 ,per_assignments_f asg
3371 ,per_jobs job
3372 WHERE asg.position_id=pos.position_id(+)
3373 AND asg.job_id=job.job_id(+)
3374 AND asg.person_id = p_person_id
3375 AND asg.primary_flag = 'Y'
3376 AND p_effective_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
3377 AND p_effective_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
3378 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
3379
3380 --Cursor to find the PA level data presence
3381 CURSOR c_pa_data(p_context VARCHAR2
3382 ,p_organization_id NUMBER
3383 ,p_payroll_action_id NUMBER
3384 ,p_contribution_period VARCHAR2
3385 )
3386 IS
3387 SELECT 1
3388 FROM pay_action_information
3389 WHERE action_context_id = p_payroll_action_id
3390 AND action_information_category = 'IN_'||p_context||'_PAY'
3391 AND action_information1 = p_contribution_period
3392 AND action_information2 = p_organization_id
3393 AND action_context_type = 'PA';
3394
3395 l_org_rep_name per_people_f.full_name%TYPE;
3396 l_reg_name hr_organization_units.name%TYPE;
3397 l_code hr_organization_information.org_information1%TYPE;
3398 l_org_name hr_organization_units.name%TYPE;
3399 l_address VARCHAR2(240);
3400 l_contr_period VARCHAR2(35);
3401 l_class VARCHAR2(10);
3402 l_action_info_id NUMBER;
3403 l_ovn NUMBER;
3404 l_rep_person_id NUMBER;
3405 l_rep_addr VARCHAR2(240);
3406 l_rep_pos PER_ALL_POSITIONS.NAME%TYPE;
3407 l_effective_date DATE;
3408 l_start_date DATE;
3409 l_end_date DATE;
3410 l_count NUMBER;
3411 l_org_id NUMBER;
3412 l_act_id NUMBER;
3413 l_act_inf_cat VARCHAR2(30);
3414 l_context VARCHAR2(240);
3415 l_flag NUMBER := 0;
3416
3417 l_message VARCHAR2(255);
3418 l_procedure VARCHAR2(100);
3419
3420 l_start_date_1 DATE;
3421 l_end_date_1 DATE;
3422 l_contr_period_1 VARCHAR2(10); -- added 14784847
3423
3424 BEGIN
3425
3426 l_procedure := g_package ||'archive_payroll_data';
3427 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3428
3429 l_count := 0;
3430 IF p_context = 'PF' THEN
3431 l_count:= g_cnt_pf;
3432 l_act_inf_cat:='IN_PF_PAY';
3433 ELSE
3434 l_count:=g_cnt_esi;
3435 l_act_inf_cat:='IN_ESI_PAY';
3436 END IF;
3437
3438 IF g_debug THEN
3439 pay_in_utils.trace('Context ',p_context);
3440 END IF;
3441
3442 FOR i IN 1..l_count LOOP
3443 pay_in_utils.set_location(g_debug,l_procedure, 20);
3444 IF p_context = 'PF' THEN
3445 l_act_id:=g_pa_act_id(i);
3446 l_org_id:=g_pf_org_id(i);
3447 ELSE
3448 l_act_id:=g_esi_act_id(i);
3449 l_org_id:=g_esi_org_id(i);
3450 END IF;
3451
3452 IF g_debug THEN
3453 pay_in_utils.trace('Payroll_Level Asg ID ',l_act_id);
3454 pay_in_utils.trace('Payroll_Level Org ID ',l_org_id);
3455 END IF;
3456
3457 --Effective Date
3458 OPEN c_effective_date(l_act_id);
3459 FETCH c_effective_date INTO l_effective_date;
3460 CLOSE c_effective_date;
3461
3462 pay_in_utils.set_location(g_debug,l_procedure, 30);
3463
3464 IF g_debug THEN
3465 pay_in_utils.trace('Effective Date ',l_effective_date);
3466 END IF;
3467
3468
3469 --Contribution Period
3470 IF p_context = 'PF' THEN
3471 pay_in_utils.set_location(g_debug,l_procedure, 40);
3472 l_start_date := l_effective_date;
3473 l_end_date := l_effective_date;
3474 l_start_date_1 := l_effective_date;
3475 l_end_date_1 := l_effective_date;
3476
3477 IF(TO_NUMBER(TO_CHAR(l_start_date,'MM'))) = 3 THEN
3478 l_start_date:=add_months(l_start_date,1);
3479 l_end_date :=l_start_date;
3480 END IF;
3481
3482 l_contr_period := TO_CHAR(pay_in_tax_utils.get_financial_year_start(l_start_date),'YYYY')||'-'||to_char(pay_in_tax_utils.get_financial_year_end(l_end_date),'YYYY');
3483 l_contr_period_1:=to_char(pay_in_tax_utils.get_financial_year_start(l_start_date_1),'YYYY')||'-'||to_char(pay_in_tax_utils.get_financial_year_end(l_end_date_1),'YYYY');
3484 ELSIF p_context = 'ESI' THEN
3485 pay_in_utils.set_location(g_debug,l_procedure, 50);
3486 IF(TO_NUMBER(TO_CHAR(l_effective_date,'MM'))) > 3 AND (TO_NUMBER(TO_CHAR(l_effective_date,'MM'))) < 10 THEN
3487 l_contr_period:='Apr-'||to_char(l_effective_date,'YYYY')||' - '||'Sep-'||to_char(l_effective_date,'YYYY');
3488 ELSE
3489 l_contr_period:='Oct-'||to_char(pay_in_tax_utils.get_financial_year_start(l_effective_date),'YYYY')||' - '||'Mar-'||to_char(pay_in_tax_utils.get_financial_year_end(l_effective_date),'YYYY');
3490
3491 END IF;
3492 END IF;
3493
3494 pay_in_utils.set_location(g_debug,l_procedure, 50);
3495
3496 --Checking for PA level Data's presence in the already archived data
3497 OPEN c_pa_data(p_context,l_org_id,l_act_id,l_contr_period);
3498 FETCH c_pa_data INTO l_flag;
3499 CLOSE c_pa_data;
3500
3501 pay_in_utils.set_location(g_debug,l_procedure, 60);
3502
3503 /* If l_flag is 0 then archive else skip this org as its already there*/
3504 IF (l_flag = 0)
3505 THEN
3506 pay_in_utils.set_location(g_debug,l_procedure, 70);
3507 --Registered Name
3508 OPEN c_registered_name(l_org_id,l_effective_date);
3509 FETCH c_registered_name INTO l_reg_name,l_code,l_class,l_address;
3510 CLOSE c_registered_name;
3511
3512 --Representative Name
3513 OPEN c_rep_name(l_org_id,l_effective_date);
3514 FETCH c_rep_name INTO l_org_rep_name,l_rep_person_id;
3515 CLOSE c_rep_name;
3516
3517 --PF/ESI Org Name
3518 OPEN c_org_name(l_org_id,l_effective_date);
3519 FETCH c_org_name INTO l_org_name;
3520 CLOSE c_org_name;
3521
3522 pay_in_utils.set_location(g_debug,l_procedure, 60);
3523 IF g_debug THEN
3524 pay_in_utils.trace('Registered Name ',l_reg_name);
3525 pay_in_utils.trace('Representative Name ',l_org_rep_name);
3526 END IF;
3527
3528 IF p_context = 'ESI' THEN
3529 pay_in_utils.set_location(g_debug,l_procedure, 70);
3530 OPEN c_rep_adr(l_rep_person_id,l_effective_date);
3531 FETCH c_rep_adr INTO l_rep_addr;
3532 CLOSE c_rep_adr;
3533
3534 OPEN c_rep_pos(l_rep_person_id,l_effective_date);
3535 FETCH c_rep_pos INTO l_rep_pos;
3536 CLOSE c_rep_pos;
3537
3538 l_class := NULL;
3539 ELSE
3540 pay_in_utils.set_location(g_debug,l_procedure, 80);
3541 l_rep_pos := NULL;
3542 l_rep_addr:= NULL;
3543 END IF;
3544
3545 SELECT DECODE(p_context,'PF',l_class,l_rep_addr) INTO l_context
3546 FROM dual;--case p_context when 'PF' then l_class else l_rep_addr end --PF CLass or ESI Org Rep Addr
3547
3548 pay_action_information_api.create_action_information
3549 (p_action_context_id => l_act_id --Payroll Action id
3550 ,p_action_context_type => 'PA'
3551 ,p_action_information_category => l_act_inf_cat
3552 ,p_tax_unit_id => null
3553 ,p_jurisdiction_code => null
3554 ,p_source_id => null
3555 ,p_source_text => null
3556 ,p_tax_group => null
3557 ,p_effective_date => l_effective_date --Prepayment Effective Date
3558 ,p_assignment_id => null
3559 ,p_action_information1 => l_contr_period --Contribution Period
3560 ,p_action_information2 => l_org_id --PF/ESI Org ID
3561 ,p_action_information3 => l_reg_name --Registered Name
3562 ,p_action_information4 => l_org_rep_name --Representative Name
3563 ,p_action_information5 => l_address --Address
3564 ,p_action_information6 => l_code --Code
3565 ,p_action_information7 => l_context
3566 ,p_action_information8 => l_org_name --PF/ESI Org Name
3567 ,p_action_information9 => l_rep_pos --ESI Org Rep Pos
3568 ,p_action_information24 => l_contr_period_1 -- ECR Contribution Period
3569 ,p_action_information_id => l_action_info_id --OUT Parameters
3570 ,p_object_version_number => l_ovn --OUT Parameters
3571 );
3572 END IF;
3573 END LOOP;
3574
3575 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
3576 EXCEPTION
3577 WHEN OTHERS THEN
3578 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3579 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
3580 pay_in_utils.trace(l_message,l_procedure);
3581 RAISE;
3582
3583 END archive_payroll_data;
3584
3585
3586 --------------------------------------------------------------------------
3587 -- --
3588 -- Name : ARCHIVE_PT_DATA --
3589 -- Type : PROCEDURE --
3590 -- Access : Private --
3591 -- Description : This procedure archives the data required for PT --
3592 -- Form III --
3593 -- Parameters : --
3594 -- IN : p_assignment_action_id NUMBER --
3595 -- p_payroll_action_id NUMBER --
3596 -- p_archive_action_id NUMBER --
3597 -- p_assignment_id NUMBER --
3598 -- p_payroll_date DATE --
3599 -- p_prepayment_date DATE --
3600 -- --
3601 -- --
3602 -- OUT : N/A --
3603 -- --
3604 -- Change History : --
3605 --------------------------------------------------------------------------
3606 -- Rev# Date Userid Description --
3607 --------------------------------------------------------------------------
3608 -- 115.0 12-May-2005 abhjain Created --
3609 --------------------------------------------------------------------------
3610 --
3611 PROCEDURE archive_pt_data
3612 (
3613 p_assignment_action_id IN NUMBER
3614 ,p_payroll_action_id IN NUMBER
3615 ,p_archive_action_id IN NUMBER
3616 ,p_assignment_id IN NUMBER
3617 ,p_payroll_date IN DATE
3618 ,p_prepayment_date IN DATE
3619 )
3620 IS
3621
3622 -- Cursor to find PT Organization State
3623 CURSOR c_pt_state(p_assignment_action_id NUMBER)
3624 IS
3625 SELECT DISTINCT pay_in_prof_tax_pkg.get_state(hoi.organization_id) jurisdiction_code
3626 FROM hr_organization_units hoi
3627 ,hr_soft_coding_keyflex scf
3628 ,per_assignments_f asg
3629 ,pay_assignment_actions paa
3630 WHERE asg.assignment_id = paa.assignment_id
3631 AND paa.assignment_action_id = p_assignment_action_id
3632 AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
3633 AND hoi.organization_id = scf.segment3
3634 AND (TO_CHAR(asg.effective_start_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
3635 OR TO_CHAR(asg.effective_end_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
3636 OR p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date);
3637
3638 -- Cursor to find PT Organization Id and Name
3639 CURSOR c_pt_org_id(p_assignment_action_id NUMBER
3640 ,p_jur_code VARCHAR2)
3641 IS/* Modified as per bug 4774108. Reduced share memory from 1,108,508 to 389,825.
3642 SELECT source_id
3643 ,name
3644 FROM ( */SELECT hoi.organization_id source_id
3645 ,hoi.name name
3646 ,asg.effective_end_date
3647 FROM hr_organization_units hoi
3648 ,hr_soft_coding_keyflex scf
3649 ,per_assignments_f asg
3650 ,pay_assignment_actions paa
3651 WHERE asg.assignment_id = paa.assignment_id
3652 AND paa.assignment_action_id = p_assignment_action_id
3653 AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
3654 AND hoi.organization_id = scf.segment3
3655 AND (TO_CHAR(asg.effective_start_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
3656 OR TO_CHAR(asg.effective_end_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
3657 OR p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
3658 AND pay_in_prof_tax_pkg.get_state(hoi.organization_id) = p_jur_code
3659 ORDER BY asg.effective_end_date DESC;/*)
3660 WHERE ROWNUM = 1;*/
3661
3662 -- Cursor to find full name, Person Id
3663 CURSOR c_person_full_name(p_assignment_action_id NUMBER)
3664 IS
3665 SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
3666 ,pep.person_id person_id
3667 FROM per_assignments_f asg
3668 ,per_people_f pep
3669 ,pay_assignment_actions paa
3670 WHERE asg.person_id = pep.person_id
3671 AND asg.assignment_id = paa.assignment_id
3672 AND paa.assignment_action_id = p_assignment_action_id
3673 AND p_payroll_date BETWEEN pep.effective_start_date AND pep.effective_end_date
3674 AND p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
3675
3676
3677 --Cursors to find the Exemption Reason entered by the user in that payroll run
3678
3679 --Find the element type id
3680 CURSOR c_element_type_id
3681 IS
3682 SELECT element_type_id
3683 FROM pay_element_types_f
3684 WHERE element_name ='Professional Tax Information'
3685 AND legislation_code = 'IN'
3686 AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
3687
3688 --Find the input value id for Reason for Exemption, Organization, State
3689 CURSOR c_iv_id(p_element_type_id NUMBER
3690 ,p_name VARCHAR2)
3691 IS
3692 SELECT input_value_id
3693 FROM pay_input_values_f
3694 WHERE element_type_id = p_element_type_id
3695 AND name = p_name
3696 AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
3697
3698 --Find the run result id
3699 CURSOR c_run_result_id(p_element_type_id NUMBER)
3700 IS
3701 SELECT run_result_id
3702 FROM pay_run_results
3703 WHERE assignment_action_id = p_assignment_action_id
3704 AND element_type_id = p_element_type_id;
3705
3706 --Find the actual Exemption Reason
3707 CURSOR c_exempt_reason(p_run_result_id NUMBER
3708 ,p_state_iv_id VARCHAR2
3709 ,p_rem_iv_id NUMBER
3710 ,p_jur_code VARCHAR2)
3711 IS
3712 SELECT prr2.result_value
3713 FROM pay_run_result_values prr1
3714 ,pay_run_result_values prr2
3715 WHERE prr1.run_result_id = p_run_result_id
3716 AND prr1.input_value_id = p_state_iv_id
3717 AND prr2.run_result_id = prr1.run_result_id
3718 AND prr2.input_value_id = p_rem_iv_id
3719 AND prr1.result_value = p_jur_code;
3720
3721
3722 l_action_info_id NUMBER;
3723 l_ovn NUMBER;
3724 pt_salary NUMBER;
3725 pt NUMBER;
3726 l_reason_for_exem VARCHAR2(60);
3727 l_exempted_frm_pt VARCHAR2(10);
3728 l_jur_code VARCHAR2(10);
3729 l_contrib_month VARCHAR2(20);
3730 l_contrib_year VARCHAR2(20);
3731 l_start_date DATE;
3732 l_end_date DATE;
3733 l_full_name per_people_f.full_name%TYPE;
3734 l_person_id per_people_f.person_id%TYPE;
3735 l_source_id hr_organization_units.organization_id%TYPE;
3736 l_pt_org_name hr_organization_units.name%TYPE;
3737 l_element_type_id pay_element_types_f.element_type_id%TYPE;
3738 l_state_iv_id pay_input_values_f.input_value_id%TYPE;
3739 l_reason_iv_id pay_input_values_f.input_value_id%TYPE;
3740 l_asg_id per_assignments_f.assignment_id%TYPE;
3741 l_message VARCHAR2(255);
3742 l_procedure VARCHAR2(100);
3743
3744 -- Added as a part of bug fix 4774108.
3745 l_asg_end_date DATE;
3746 BEGIN
3747
3748 l_procedure := g_package ||'archive_pt_data';
3749 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3750
3751 IF g_debug THEN
3752 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
3753 pay_in_utils.trace('Payroll Action id ',p_payroll_action_id);
3754 pay_in_utils.trace('Archive Action id ',p_archive_action_id);
3755 pay_in_utils.trace('Assignment id ',p_assignment_id);
3756 pay_in_utils.trace('Payroll Date ',p_payroll_date);
3757 pay_in_utils.trace('Prepayment Date ',p_prepayment_date);
3758 END IF;
3759
3760
3761
3762 --For each PT State for that assignment in that payroll run
3763 FOR c_rec_state IN c_pt_state(p_assignment_action_id)
3764 LOOP
3765 pay_in_utils.set_location(g_debug,l_procedure, 20);
3766
3767 l_jur_code := c_rec_state.jurisdiction_code;
3768
3769 IF g_debug THEN
3770 pay_in_utils.trace('Jurisdiction Code ',l_jur_code);
3771 END IF;
3772
3773 /* Removed this as a part of bug fix 4774108
3774 FOR c_rec IN c_pt_org_id(p_assignment_action_id
3775 ,l_jur_code)
3776 LOOP
3777 l_source_id := c_rec.source_id;
3778 hr_utility.set_location('PT-III Source Id: '||l_source_id, 12);
3779 l_pt_org_name := c_rec.name;
3780 hr_utility.set_location('PT-III Org Name: '||l_pt_org_name, 12);
3781 END LOOP;
3782 */
3783 -- Added as a part of bug fix 4774108.
3784 OPEN c_pt_org_id(p_assignment_action_id,l_jur_code);
3785 FETCH c_pt_org_id INTO l_source_id,l_pt_org_name,l_asg_end_date;
3786 CLOSE c_pt_org_id;
3787
3788 pay_in_utils.set_location(g_debug,l_procedure, 20);
3789
3790 --Find the assignments's full name
3791 OPEN c_person_full_name(p_assignment_action_id);
3792 FETCH c_person_full_name INTO l_full_name, l_person_id;
3793 CLOSE c_person_full_name;
3794
3795 IF g_debug THEN
3796 pay_in_utils.trace('Jurisdiction Code ',l_jur_code);
3797 END IF;
3798
3799
3800 l_start_date := TRUNC(p_payroll_date, 'MM');
3801 l_end_date := ADD_MONTHS(l_start_date, 1) - 1;
3802
3803 IF g_debug THEN
3804 pay_in_utils.trace('PT-III l_start_date ',l_start_date);
3805 pay_in_utils.trace('PT-III l_end_date ',l_end_date);
3806 END IF;
3807
3808
3809 -- Populating the PL/SQL Tables with State and payroll action id
3810
3811 pay_in_utils.set_location(g_debug,l_procedure, 30);
3812
3813 g_cnt_pt := g_cnt_pt + 1;
3814
3815 g_pt_org_id(g_cnt_pt) := l_source_id;
3816
3817 g_pt_act_id(g_cnt_pt) := p_payroll_action_id;
3818
3819 g_pt_jur_code(g_cnt_pt) := l_jur_code;
3820
3821
3822 IF g_debug THEN
3823 pay_in_utils.trace('PT-III g_pt_org_id ',g_pt_org_id(g_cnt_pt));
3824 pay_in_utils.trace('PT-III g_pt_org_id ',g_pt_act_id(g_cnt_pt));
3825 pay_in_utils.trace('PT-III g_pt_jur_code ',g_pt_jur_code(g_cnt_pt));
3826 END IF;
3827
3828 --PT Salary
3829 pt_salary := pay_in_tax_utils.get_balance_value(
3830 p_assignment_action_id => p_assignment_action_id
3831 ,p_balance_name => 'PT Actual Salary'
3832 ,p_dimension_name => '_ASG_STATE_DE_PTD'
3833 ,p_context_name => 'JURISDICTION_CODE'
3834 ,p_context_value => l_jur_code
3835 );
3836 pay_in_utils.set_location(g_debug,l_procedure, 20);
3837
3838 IF g_debug THEN
3839 pay_in_utils.trace('PT Salary ',pt_salary);
3840 END IF;
3841
3842 --Professional Tax
3843 pt := pay_in_tax_utils.get_balance_value(
3844 p_assignment_action_id => p_assignment_action_id
3845 ,p_balance_name => 'Professional Tax'
3846 ,p_dimension_name => '_ASG_STATE_DE_PTD'
3847 ,p_context_name => 'JURISDICTION_CODE'
3848 ,p_context_value => l_jur_code
3849 );
3850 pay_in_utils.set_location(g_debug,l_procedure, 20);
3851
3852 IF g_debug THEN
3853 pay_in_utils.trace('PT ',pt);
3854 END IF;
3855
3856 l_asg_id := p_assignment_id;
3857 l_reason_for_exem := NULL;
3858
3859 OPEN c_element_type_id;
3860 FETCH c_element_type_id INTO l_element_type_id;
3861 CLOSE c_element_type_id;
3862
3863
3864
3865 OPEN c_iv_id(l_element_type_id, 'State');
3866 FETCH c_iv_id INTO l_state_iv_id;
3867 CLOSE c_iv_id;
3868
3869 pay_in_utils.set_location(g_debug,l_procedure, 20);
3870
3871 OPEN c_iv_id(l_element_type_id, 'Exemption Reason');
3872 FETCH c_iv_id INTO l_reason_iv_id;
3873 CLOSE c_iv_id;
3874
3875 pay_in_utils.set_location(g_debug,l_procedure, 20);
3876
3877 FOR c_rec IN c_run_result_id(l_element_type_id)
3878 LOOP
3879 pay_in_utils.set_location(g_debug,l_procedure, 20);
3880 OPEN c_exempt_reason(c_rec.run_result_id
3881 ,l_state_iv_id
3882 ,l_reason_iv_id
3883 ,l_jur_code);
3884 FETCH c_exempt_reason INTO l_reason_for_exem;
3885 CLOSE c_exempt_reason;
3886
3887 END LOOP;
3888
3889 pay_in_utils.set_location(g_debug,l_procedure, 20);
3890
3891 IF g_debug THEN
3892 pay_in_utils.trace('PT Exemption Reason ',l_reason_for_exem);
3893 END IF;
3894
3895 IF (l_reason_for_exem IS NOT NULL) THEN
3896 l_exempted_frm_pt := 'Yes';
3897 ELSE
3898 l_exempted_frm_pt := 'No';
3899 END IF;
3900
3901 l_contrib_month := TO_CHAR(ADD_MONTHS(p_payroll_date, -3), 'MM');
3902 l_contrib_year := TO_CHAR(pay_in_tax_utils.get_financial_year_start(p_payroll_date), 'YYYY')||'-'|| TO_CHAR(pay_in_tax_utils.get_financial_year_end(p_payroll_date), 'YYYY');
3903
3904 pay_action_information_api.create_action_information
3905 (p_action_context_id => p_archive_action_id --Archive Action id
3906 ,p_action_context_type => 'AAP'
3907 ,p_action_information_category => 'IN_PT_ASG'
3908 ,p_tax_unit_id => null
3909 ,p_jurisdiction_code => l_jur_code --Jur Code (PT Org State)
3910 ,p_source_id => l_source_id --Source Id (PT Org Id)
3911 ,p_source_text => null
3912 ,p_tax_group => null
3913 ,p_effective_date => p_prepayment_date --Prepayment Effective Date
3914 ,p_assignment_id => l_asg_id --Asg Id
3915 ,p_action_information1 => l_contrib_year --Financial Year
3916 ,p_action_information2 => l_contrib_month --Month of the Financial Year
3917 ,p_action_information3 => l_full_name --Full Name
3918 ,p_action_information4 => pt_salary --PT Salary _ASG_STATE_DE_PTD
3919 ,p_action_information5 => pt --PT
3920 ,p_action_information6 => l_exempted_frm_pt --Exempted Flag
3921 ,p_action_information7 => l_reason_for_exem --Exemption Reason
3922 ,p_action_information8 => to_char(p_payroll_date, 'DD-MM-YYYY') --Payroll Date
3923 ,p_action_information9 => l_pt_org_name --Local PT Office
3924 ,p_action_information10 => null
3925 ,p_action_information11 => null
3926 ,p_action_information12 => null
3927 ,p_action_information13 => null
3928 ,p_action_information14 => null
3929 ,p_action_information15 => null
3930 ,p_action_information16 => null
3931 ,p_action_information17 => null
3932 ,p_action_information18 => null
3933 ,p_action_information19 => null
3934 ,p_action_information20 => null
3935 ,p_action_information21 => null
3936 ,p_action_information22 => null
3937 ,p_action_information23 => null
3938 ,p_action_information24 => null
3939 ,p_action_information25 => null
3940 ,p_action_information26 => null
3941 ,p_action_information27 => null
3942 ,p_action_information28 => null
3943 ,p_action_information29 => null
3944 ,p_action_information30 => null
3945 ,p_action_information_id => l_action_info_id --OUT Parameters
3946 ,p_object_version_number => l_ovn --OUT Parameters
3947 );
3948
3949 END LOOP;
3950 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
3951 EXCEPTION
3952 WHEN OTHERS THEN
3953 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3954 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
3955 pay_in_utils.trace(l_message,l_procedure);
3956 RAISE;
3957
3958 END archive_pt_data;
3959
3960 --------------------------------------------------------------------------
3961 -- --
3962 -- Name : ARCHIVE_PT_PAYROLL_DATA --
3963 -- Type : PROCEDURE --
3964 -- Access : Public --
3965 -- Description : This procedure archives the data required for PT form-
3966 -- III data at PA Level --
3967 -- Parameters : --
3968 -- IN : --
3969 -- --
3970 -- OUT : N/A --
3971 -- --
3972 -- Change History : --
3973 --------------------------------------------------------------------------
3974 -- Rev# Date Userid Description --
3975 --------------------------------------------------------------------------
3976 -- 115.0 12-May-2005 abhjain Created --
3977 -- 115.1 25-Sep-2007 rsaharay Modified c_rep_pos --
3978 -------------------------------------------------------------------------
3979 PROCEDURE archive_pt_payroll_data
3980 IS
3981 --Cursor to find the effective date
3982 CURSOR c_effective_date(p_payroll_action_id NUMBER)
3983 IS
3984 SELECT effective_date
3985 FROM pay_payroll_actions
3986 WHERE payroll_action_id = p_payroll_action_id;
3987
3988 --Cursor to find the PT Organization Name
3989 Cursor c_org_name(p_organization_id NUMBER
3990 ,p_effective_date DATE)
3991 IS
3992 SELECT hou.name
3993 FROM hr_organization_units hou
3994 WHERE hou.organization_id = p_organization_id
3995 AND p_effective_date BETWEEN hou.date_from AND NVL(date_to, TO_DATE('31-12-4712', 'DD-MM-YYYY'));
3996
3997 --Cursor to find the Registered name, Code of the PF/ESI Organization , Classification and its address
3998 CURSOR c_registered_name(p_organization_id NUMBER
3999 ,p_effective_date DATE)
4000 IS
4001 SELECT hou.name
4002 ,hoi.org_information1
4003 ,hoi.org_information3
4004 ,SUBSTR(
4005 hla.address_line_1||
4006 DECODE(hla.address_line_2, null, null, ',' || hla.address_line_2)||
4007 DECODE(hla.address_line_3, null, null, ',' || hla.address_line_3)||
4008 DECODE(hla.loc_information14, null, null, ',' || hla.loc_information14)||
4009 DECODE(hla.loc_information15, null, null, ',' || hla.loc_information15)||
4010 DECODE(hr_general.decode_lookup('IN_STATES', hla.loc_information16)
4011 , null, null, ',' || hr_general.decode_lookup('IN_STATES', hla.loc_information16))||
4012 DECODE(hla.postal_code, null, null, ','||hla.postal_code)
4013 ,1,240)
4014 FROM hr_organization_information hoi
4015 ,hr_organization_units hou
4016 ,hr_organization_units hou1
4017 ,hr_locations_all hla
4018 WHERE hoi.organization_id = p_organization_id
4019 AND hoi.org_information_context = 'PER_IN_PROF_TAX_DF'
4020 AND hou.organization_id = hoi.org_information2
4021 AND hla.location_id = hou1.location_id
4022 AND hou.organization_id = hou1.organization_id
4023 AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712', 'DD-MM-YYYY'));
4024
4025 --Cursor to find the PF/ESI Representative Name
4026 CURSOR c_rep_name(p_pf_org_id NUMBER
4027 ,p_effective_date DATE)
4028 IS
4029 SELECT DISTINCT hr_in_utility.per_in_full_name(peap.first_name,peap.middle_names,peap.last_name,peap.title) rep_name
4030 ,peap.person_id person_id
4031 FROM hr_organization_information hoi
4032 ,per_people_f peap
4033 WHERE hoi.organization_id = p_pf_org_id
4034 AND hoi.org_information_context = 'PER_IN_PROF_TAX_REP_DF'
4035 AND peap.person_id = hoi.org_information1
4036 AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
4037 AND NVL(fnd_date.canonical_to_date(hoi.org_information3)
4038 , TO_DATE('31-12-4712', 'DD-MM-YYYY'));
4039
4040 --Cursor to find the Position of PT Rep
4041 CURSOR c_rep_pos(p_person_id NUMBER
4042 ,p_effective_date DATE)
4043 IS
4044 SELECT nvl(pos.name,job.name) name
4045 FROM per_positions pos
4046 ,per_assignments_f asg
4047 ,per_jobs job
4048 WHERE asg.position_id=pos.position_id(+)
4049 AND asg.job_id=job.job_id(+)
4050 AND asg.person_id = p_person_id
4051 AND asg.primary_flag = 'Y'
4052 AND p_effective_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
4053 AND p_effective_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
4054 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
4055
4056
4057 l_count NUMBER;
4058 l_act_id NUMBER;
4059 l_action_info_id NUMBER;
4060 l_ovn NUMBER;
4061 l_jur_code VARCHAR2(10);
4062 l_contr_month VARCHAR2(10);
4063 l_bsrtc_no VARCHAR2(50);
4064 l_address VARCHAR2(240);
4065 l_contr_year VARCHAR2(10);
4066 l_rep_pos PER_ALL_POSITIONS.NAME%TYPE;
4067 l_act_inf_cat VARCHAR2(30);
4068 l_effective_date DATE;
4069 l_reg_name hr_organization_units.name%TYPE;
4070 l_reg_no hr_organization_information.org_information1%TYPE;
4071 l_org_name hr_organization_units.name%TYPE;
4072 l_org_rep_name per_people_f.full_name%TYPE;
4073 l_rep_person_id per_people_f.person_id%TYPE;
4074 l_org_id hr_organization_units.organization_id%TYPE;
4075 l_source_id hr_organization_units.organization_id%TYPE;
4076 l_message VARCHAR2(255);
4077 l_procedure VARCHAR2(100);
4078
4079
4080 BEGIN
4081
4082 l_count := 0;
4083 l_count := g_cnt_pt;
4084 l_act_inf_cat := 'IN_PT_PAY';
4085
4086
4087 l_procedure := g_package ||'archive_pt_payroll_data';
4088 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4089
4090
4091 FOR i IN 1..l_count LOOP
4092 pay_in_utils.set_location(g_debug,l_procedure, 20);
4093
4094 -- Getting Action Id, Organization Id, Jurisdiction Code
4095 l_act_id := g_pt_act_id(i);
4096 l_org_id := g_pt_org_id(i);
4097 l_jur_code := g_pt_jur_code(i);
4098 l_source_id := l_org_id;
4099
4100 IF g_debug THEN
4101 pay_in_utils.trace('PT-III Payroll_Level Org ID ',l_org_id);
4102 END IF;
4103
4104
4105
4106 --Effective Date
4107 OPEN c_effective_date(l_act_id);
4108 FETCH c_effective_date INTO l_effective_date;
4109 CLOSE c_effective_date;
4110
4111 pay_in_utils.set_location(g_debug,l_procedure, 30);
4112
4113
4114 --Contribution Period
4115 l_contr_year := TO_CHAR(pay_in_tax_utils.get_financial_year_start(l_effective_date), 'YYYY')||'-'||to_char(pay_in_tax_utils.get_financial_year_end(l_effective_date), 'YYYY');
4116 l_contr_month := TO_CHAR(ADD_MONTHS(l_effective_date, -3), 'MM');
4117
4118 --Registered Name
4119 OPEN c_registered_name(l_org_id
4120 ,l_effective_date);
4121 FETCH c_registered_name INTO l_reg_name
4122 ,l_reg_no
4123 ,l_bsrtc_no
4124 ,l_address;
4125 CLOSE c_registered_name;
4126
4127 pay_in_utils.set_location(g_debug,l_procedure, 40);
4128
4129 --PT Org Name
4130 OPEN c_org_name(l_org_id
4131 ,l_effective_date);
4132 FETCH c_org_name INTO l_org_name;
4133 CLOSE c_org_name;
4134
4135
4136 --Representative Name
4137 OPEN c_rep_name(l_org_id
4138 ,l_effective_date);
4139 FETCH c_rep_name INTO l_org_rep_name
4140 , l_rep_person_id;
4141 CLOSE c_rep_name;
4142
4143 pay_in_utils.set_location(g_debug,l_procedure, 50);
4144
4145 -- Representative Designation
4146 OPEN c_rep_pos(l_rep_person_id
4147 ,l_effective_date);
4148 FETCH c_rep_pos INTO l_rep_pos;
4149 CLOSE c_rep_pos;
4150
4151 pay_in_utils.set_location(g_debug,l_procedure, 60);
4152
4153 pay_action_information_api.create_action_information
4154 (p_action_context_id => l_act_id --Payroll Action id
4155 ,p_action_context_type => 'PA'
4156 ,p_action_information_category => l_act_inf_cat
4157 ,p_tax_unit_id => null
4158 ,p_jurisdiction_code => l_jur_code
4159 ,p_source_id => l_source_id
4160 ,p_source_text => null
4161 ,p_tax_group => null
4162 ,p_effective_date => l_effective_date --Prepayment Effective Date
4163 ,p_assignment_id => null
4164 ,p_action_information1 => l_contr_year --Financial Year
4165 ,p_action_information2 => l_contr_month --Month of the Financial Year
4166 ,p_action_information3 => l_bsrtc_no --BSRTC No
4167 ,p_action_information4 => l_reg_name --Registered Name
4168 ,p_action_information5 => l_org_rep_name --Representative Name
4169 ,p_action_information6 => l_address --Address
4170 ,p_action_information7 => l_reg_no --Registration No
4171 ,p_action_information8 => l_org_name --PT Org Name
4172 ,p_action_information9 => l_rep_pos --PT Org Rep Pos
4173 ,p_action_information_id => l_action_info_id --OUT Parameters
4174 ,p_object_version_number => l_ovn --OUT Parameters
4175 );
4176 END LOOP;
4177
4178 IF g_debug THEN
4179 pay_in_utils.trace('Payroll Action id ',l_act_id);
4180 pay_in_utils.trace('Action Information Category ',l_act_inf_cat);
4181 pay_in_utils.trace('Jurisdiction Code ',l_jur_code);
4182 pay_in_utils.trace('Source ID ',l_source_id);
4183 pay_in_utils.trace('Effective Date ',l_effective_date);
4184 pay_in_utils.trace('Contribution year ',l_contr_year);
4185 pay_in_utils.trace('Contribution Month ',l_contr_month);
4186 pay_in_utils.trace('bsrtc number ',l_bsrtc_no);
4187 pay_in_utils.trace('Registered name ',l_reg_name);
4188 pay_in_utils.trace('Representative Name ',l_org_rep_name);
4189 pay_in_utils.trace('Address ',l_address);
4190 pay_in_utils.trace('Registration No ',l_reg_no);
4191 pay_in_utils.trace('PT Org Name ',l_org_name);
4192 pay_in_utils.trace('PT Org Rep Pos ',l_rep_pos);
4193 pay_in_utils.trace('Action information id ',l_action_info_id);
4194 pay_in_utils.trace('Next Period Date ',l_ovn);
4195
4196 END IF;
4197
4198 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 70);
4199 EXCEPTION
4200 WHEN OTHERS THEN
4201 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
4202 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 80);
4203 pay_in_utils.trace(l_message,l_procedure);
4204
4205 RAISE;
4206
4207 END archive_pt_payroll_data;
4208
4209
4210 --------------------------------------------------------------------------
4211 -- --
4212 -- Name : ARCHIVE_CODE --
4213 -- Type : PROCEDURE --
4214 -- Access : Public --
4215 -- Description : Procedure to call the internal procedures to --
4216 -- actually the archive the data. The procedure --
4217 -- called are --
4218 -- pay_apac_payslip_archive.archive_user_balances --
4219 -- pay_apac_payslip_archive.archive_user_elements --
4220 -- archive_stat_balances --
4221 -- archive_stat_elements --
4222 -- archive_employee_details --
4223 -- archive_accrual_details --
4224 -- archive_absences --
4225 -- --
4226 -- Parameters : --
4227 -- IN : p_assignment_action_id NUMBER --
4228 -- p_effective_date DATE --
4229 -- --
4230 -- OUT : N/A --
4231 -- --
4232 -- Change History : --
4233 --------------------------------------------------------------------------
4234 -- Rev# Date Userid Description --
4235 --------------------------------------------------------------------------
4236 -- 115.0 04-NOV-2004 bramajey Initial Version --
4237 -- 115.1 11-JAN-2005 aaagarwa Added Code for archiving Form 3A --
4238 -- and 6A data --
4239 -- 115.2 1-MAR-2005 aaagarwa Added Code for archiving Form 3A --
4240 -- and 6A data at Pyaroll level --
4241 --------------------------------------------------------------------------
4242 --
4243
4244 PROCEDURE archive_code (
4245 p_assignment_action_id IN NUMBER
4246 ,p_effective_date IN DATE
4247 )
4248 IS
4249 --
4250
4251 CURSOR get_bal_init_aa(p_init_arch_action_id IN NUMBER)
4252 IS
4253 SELECT paa_arch.assignment_action_id arch_assignment_action_id
4254 ,paa_arch.payroll_action_id arch_payroll_action_id
4255 ,paa_init.assignment_action_id init_assignment_action_id
4256 ,ppa_init.payroll_action_id init_payroll_action_id
4257 ,ppa_init.effective_date init_effective_date
4258 ,paa_arch.assignment_id
4259 FROM pay_assignment_actions paa_arch
4260 ,pay_action_interlocks intk
4261 ,pay_assignment_actions paa_init
4262 ,pay_payroll_actions ppa_init
4263 WHERE paa_arch.assignment_action_id = p_init_arch_action_id
4264 AND intk.locking_action_id = paa_arch.assignment_action_id
4265 AND intk.locked_action_id = paa_init.assignment_action_id
4266 AND paa_init.payroll_action_id = ppa_init.payroll_action_id
4267 AND ppa_init.action_type ='I';
4268
4269 -- Cursor to select all the locked prepayment and payrolls by the archive
4270 -- assignment action. The records are ordered descending as we only need
4271 -- latest payroll run in the prepayment.
4272
4273 CURSOR get_payslip_aa(p_master_aa_id NUMBER)
4274 IS
4275 SELECT /*+ ORDERED */
4276 paa_arch_chd.assignment_action_id chld_arc_assignment_action_id
4277 ,paa_arch_chd.payroll_action_id arc_payroll_action_id
4278 ,paa_pre.assignment_action_id pre_assignment_action_id
4279 ,paa_run.assignment_action_id run_assignment_action_id
4280 ,paa_run.payroll_action_id run_payroll_action_id
4281 ,ppa_pre.effective_date pre_effective_date
4282 ,paa_arch_chd.assignment_id
4283 ,ppa_run.effective_date run_effective_date
4284 ,ppa_run.date_earned run_date_earned
4285 ,ptp.end_date period_end_date
4286 ,ptp.time_period_id
4287 ,ptp.start_date period_start_date
4288 ,ptp.regular_payment_date
4289 FROM pay_assignment_actions paa_arch_mst
4290 ,pay_assignment_actions paa_arch_chd
4291 ,pay_action_interlocks pai_pre
4292 ,pay_assignment_actions paa_pre
4293 ,pay_payroll_actions ppa_pre
4294 ,per_business_groups pbg
4295 ,pay_action_interlocks pai_run
4296 ,pay_assignment_actions paa_run
4297 ,pay_payroll_actions ppa_run
4298 ,per_time_periods ptp
4299 WHERE paa_arch_mst.assignment_action_id = p_master_aa_id
4300 AND paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
4301 AND paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
4302 AND ppa_pre.business_group_id = pbg.business_group_id
4303 AND pbg.business_group_id = ppa_run.business_group_id
4304 AND ppa_pre.payroll_id = ppa_run.payroll_id
4305 AND paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
4306 AND pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
4307 AND pai_pre.locked_action_id = paa_pre.assignment_action_id
4308 AND pai_run.locking_action_id = paa_arch_chd.assignment_action_id
4309 AND pai_run.locked_action_id = paa_run.assignment_action_id
4310 /* Asg start */
4311 and paa_run.assignment_id = paa_arch_chd.assignment_id
4312 and paa_pre.assignment_id = paa_arch_mst.assignment_id
4313 /* Asg end */
4314 AND ppa_pre.payroll_action_id = paa_pre.payroll_action_id
4315 AND ppa_pre.action_type IN ('P','U')
4316 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
4317 AND ppa_run.action_type IN ('R','Q')
4318 AND ptp.payroll_id = ppa_run.payroll_id
4319 AND ppa_run.date_earned BETWEEN ptp.start_date
4320 AND ptp.end_date
4321 -- Get the highest in sequence for this payslip
4322 AND paa_run.action_sequence =
4323 (
4324 SELECT MAX(paa_run2.action_sequence)
4325 FROM pay_assignment_actions paa_run2
4326 ,pay_action_interlocks pai_run2
4327 WHERE pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
4328 AND pai_run2.locked_action_id = paa_run2.assignment_action_id
4329 );
4330
4331 --
4332 /*Cursor for selecting assignment actions in case Single pre payment has been done for multi payroll runs*/
4333 CURSOR c_multi_rec_count(p_prepayment_lcking_id NUMBER
4334 )
4335 IS
4336 select count(paa.assignment_action_id)
4337 from pay_payroll_actions ppa
4338 ,pay_assignment_actions paa
4339 ,pay_action_interlocks pal
4340 where pal.locking_action_id=p_prepayment_lcking_id
4341 and paa.assignment_action_id=pal.locked_action_id
4342 and ppa.payroll_action_id=paa.payroll_action_id
4343 and ppa.action_type in ('Q','R')
4344 and ppa.action_status='C'
4345 and paa.action_status='C'
4346 and paa.source_action_id is not null;
4347
4348 CURSOR c_multi_records(p_prepayment_lcking_id NUMBER
4349 ,p_date_earned DATE
4350 ,i NUMBER
4351 )
4352 IS
4353 select paa.assignment_action_id
4354 ,ppa.date_earned
4355 from pay_payroll_actions ppa
4356 ,pay_assignment_actions paa
4357 ,pay_action_interlocks pal
4358 where pal.locking_action_id=p_prepayment_lcking_id
4359 and paa.assignment_action_id=pal.locked_action_id
4360 and ppa.payroll_action_id=paa.payroll_action_id
4361 and ppa.action_type in ('Q','R')
4362 and ppa.action_status='C'
4363 and paa.action_status='C'
4364 and paa.source_action_id is not null
4365 and to_char(ppa.date_earned,'MM-YYYY')=to_char(add_months(p_date_earned,-i),'MM-YYYY')
4366 order by paa.assignment_action_id desc;
4367
4368 CURSOR get_rvsl_records(c_assignment_action_id number
4369 ,c_assignment_id NUMBER
4370 ,c_payroll_date date)
4371 IS
4372 SELECT paa.assignment_action_id
4373 FROM pay_payroll_actions ppa,pay_assignment_actions paa,
4374 pay_action_interlocks pai1,pay_action_interlocks pai2
4375
4376 WHERE ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
4377 AND paa.ASSIGNMENT_ID = c_assignment_id
4378 AND paa.payroll_action_id = ppa.payroll_action_id
4379 AND paa.action_status = 'C'
4380 AND ACTION_TYPE = 'V'
4381 AND ppa.action_status = 'C'
4382 AND pai1.LOCKING_ACTION_ID = c_assignment_action_id
4383 AND pai2.LOCKING_ACTION_ID = paa.assignment_action_id
4384 AND pai1.locked_action_id = pai2.locked_action_id
4385 AND TO_CHAR(ppa.date_earned ,'MM-YYYY') = to_char(c_payroll_date,'MM-YYYY');
4386
4387
4388 /* Bug No:5593925
4389 This cursor returns actual termination date if it falls in the pay period */
4390
4391 CURSOR csr_payment_date(p_assignment_action_id NUMBER)
4392 IS
4393 SELECT pps.actual_termination_date
4394 FROM pay_payroll_actions ppa,
4395 pay_assignment_actions paa,
4396 per_time_periods ptp,
4397 per_all_assignments_f paf,
4398 per_periods_of_service pps
4399 WHERE paa.assignment_action_id = p_assignment_action_id
4400 AND ppa.payroll_action_id = paa.payroll_action_id
4401 AND ptp.payroll_id = ppa.payroll_id
4402 AND paf.assignment_id = paa.assignment_id
4403 AND pps.period_of_service_id = paf.period_of_service_id
4404 AND ppa.date_earned between ptp.start_date AND ptp.end_date
4405 AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
4406
4407
4408
4409 l_procedure VARCHAR2(100);
4410 l_asg_id NUMBER;
4411 l_date DATE;
4412 l_count NUMBER;
4413 l_init_exists NUMBER;
4414 l_message VARCHAR2(255);
4415 l_reversal_asg_action_id NUMBER;
4416 l_payment_date DATE :=NULL;
4417
4418 --
4419 BEGIN
4420 --
4421 g_debug := hr_utility.debug_enabled;
4422 l_procedure := g_package || '.archive_code';
4423 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4424
4425 pay_in_utils.set_location(g_debug,l_procedure, 20);
4426
4427 IF g_debug THEN
4428
4429 pay_in_utils.trace('Assignment Action id ',p_assignment_action_id);
4430 pay_in_utils.trace('Effective Date ',p_effective_date);
4431
4432 END IF;
4433
4434
4435 g_cnt_pf := 0;
4436 g_cnt_esi := 0;
4437 g_cnt_pt := 0;
4438
4439 l_init_exists := 0;
4440 -- use cursor suggested by core
4441 FOR csr_init_rec IN get_bal_init_aa(p_assignment_action_id)
4442 LOOP
4443 pay_in_utils.set_location(g_debug,l_procedure, 30);
4444
4445 IF g_debug THEN
4446
4447 pay_in_utils.trace('p_assignment_action_id ',csr_init_rec.init_assignment_action_id);
4448 pay_in_utils.trace('p_archive_action_id ',csr_init_rec.arch_assignment_action_id);
4449 pay_in_utils.trace('p_assignment_id ',csr_init_rec.assignment_id);
4450 pay_in_utils.trace('p_payroll_date ',csr_init_rec.init_effective_date);
4451 pay_in_utils.trace('p_payroll_action_id ',csr_init_rec.arch_payroll_action_id);
4452 pay_in_utils.trace('p_run_payroll_action_id ',csr_init_rec.init_payroll_action_id );
4453 END IF;
4454
4455 archive_form_data( p_assignment_action_id => csr_init_rec.init_assignment_action_id
4456 ,p_archive_action_id => csr_init_rec.arch_assignment_action_id
4457 ,p_assignment_id => csr_init_rec.assignment_id
4458 ,p_payroll_date => csr_init_rec.init_effective_date
4459 ,p_prepayment_date => csr_init_rec.init_effective_date
4460 ,p_payroll_action_id => csr_init_rec.arch_payroll_action_id
4461 ,p_run_payroll_action_id => csr_init_rec.init_payroll_action_id
4462 );
4463
4464 pay_in_utils.set_location(g_debug,l_procedure, 40);
4465
4466 archive_esi_data( p_assignment_action_id => csr_init_rec.init_assignment_action_id
4467 ,p_archive_action_id => csr_init_rec.arch_assignment_action_id
4468 ,p_assignment_id => csr_init_rec.assignment_id
4469 ,p_payroll_date => csr_init_rec.init_effective_date
4470 ,p_prepayment_date => csr_init_rec.init_effective_date
4471 ,p_payroll_action_id => csr_init_rec.arch_payroll_action_id
4472 );
4473
4474 pay_in_utils.set_location(g_debug,l_procedure, 50);
4475
4476 archive_pt_data( p_assignment_action_id => csr_init_rec.init_assignment_action_id
4477 ,p_archive_action_id => csr_init_rec.arch_assignment_action_id
4478 ,p_assignment_id => csr_init_rec.assignment_id
4479 ,p_payroll_date => csr_init_rec.init_effective_date
4480 ,p_prepayment_date => csr_init_rec.init_effective_date
4481 ,p_payroll_action_id => csr_init_rec.arch_payroll_action_id
4482 );
4483 pay_in_utils.set_location(g_debug,l_procedure, 60);
4484
4485 archive_form24q_balances
4486 (
4487 p_assignment_action_id => csr_init_rec.init_assignment_action_id
4488 ,p_assignment_id => csr_init_rec.assignment_id
4489 ,p_date_earned => csr_init_rec.init_effective_date
4490 ,p_effective_date => csr_init_rec.init_effective_date
4491 ,p_assact_id => csr_init_rec.arch_assignment_action_id
4492 ,p_payroll_action_id => csr_init_rec.arch_payroll_action_id
4493 ,p_run_payroll_action_id => csr_init_rec.init_payroll_action_id
4494 ,p_pre_assact_id => csr_init_rec.init_assignment_action_id
4495 );
4496 pay_in_utils.set_location(g_debug,l_procedure, 70);
4497
4498 l_init_exists := 1;
4499 END LOOP;
4500
4501
4502
4503 pay_in_utils.set_location(g_debug,l_procedure, 80);
4504
4505 IF l_init_exists = 0 THEN
4506 pay_in_utils.set_location(g_debug,l_procedure, 90);
4507 -- Create Child Assignment Actions
4508 pay_core_payslip_utils.generate_child_actions(p_assignment_action_id
4509 ,p_effective_date);
4510
4511 FOR csr_rec IN get_payslip_aa(p_assignment_action_id)
4512 LOOP
4513 --
4514 pay_in_utils.set_location(g_debug,l_procedure, 100);
4515 -- Added for bug 5593925
4516 open csr_payment_date(csr_rec.run_assignment_action_id);
4517 fetch csr_payment_date into l_payment_date;
4518 if csr_payment_date%NOTFOUND then
4519 l_payment_date := csr_rec.regular_payment_date;
4520 end if;
4521 close csr_payment_date;
4522
4523 IF g_debug THEN
4524 pay_in_utils.trace('Pre assignment_action_id ',csr_rec.pre_assignment_action_id);
4525 pay_in_utils.trace('Run Date Earned ',csr_rec.run_date_earned);
4526 pay_in_utils.trace('Child assignment_action_id ',csr_rec.chld_arc_assignment_action_id);
4527 pay_in_utils.trace('Assignment id ',csr_rec.assignment_id);
4528 pay_in_utils.trace('Pre Effective Date ',csr_rec.pre_effective_date);
4529 pay_in_utils.trace('Arc payroll action_id ',csr_rec.arc_payroll_action_id);
4530 pay_in_utils.trace('Run Payroll action_id ',csr_rec.run_payroll_action_id);
4531 END IF;
4532
4533 OPEN c_multi_rec_count(csr_rec.pre_assignment_action_id);
4534 FETCH c_multi_rec_count INTO l_count;
4535 CLOSE c_multi_rec_count;
4536 FOR i IN 0..l_count
4537 LOOP
4538 pay_in_utils.set_location(g_debug,l_procedure, 110);
4539 l_asg_id:=NULL;
4540 l_date:=NULL;
4541 OPEN c_multi_records(csr_rec.pre_assignment_action_id,csr_rec.run_date_earned,i);
4542 FETCH c_multi_records INTO l_asg_id,l_date;
4543 CLOSE c_multi_records;
4544
4545 OPEN get_rvsl_records(csr_rec.chld_arc_assignment_action_id,csr_rec.assignment_id,csr_rec.run_date_earned);
4546 FETCH get_rvsl_records INTO l_reversal_asg_action_id;
4547 CLOSE get_rvsl_records;
4548
4549 IF (l_asg_id IS NOT NULL)AND(l_date IS NOT NULL) THEN
4550 pay_in_utils.set_location(g_debug,l_procedure, 120);
4551 archive_form_data(
4552 p_assignment_action_id => nvl(l_reversal_asg_action_id,l_asg_id)
4553 ,p_archive_action_id => csr_rec.chld_arc_assignment_action_id
4554 ,p_assignment_id => csr_rec.assignment_id
4555 ,p_payroll_date => l_date
4556 ,p_prepayment_date => csr_rec.pre_effective_date
4557 ,p_payroll_action_id => csr_rec.arc_payroll_action_id
4558 ,p_run_payroll_action_id => csr_rec.run_payroll_action_id
4559 );
4560
4561
4562
4563
4564 IF g_debug THEN
4565 pay_in_utils.trace('l_date ',l_date);
4566 END IF;
4567
4568 pay_in_utils.set_location(g_debug,l_procedure, 130);
4569
4570 archive_esi_data(
4571 p_assignment_action_id =>nvl(l_reversal_asg_action_id,l_asg_id)
4572 ,p_archive_action_id =>csr_rec.chld_arc_assignment_action_id
4573 ,p_assignment_id =>csr_rec.assignment_id
4574 ,p_payroll_date =>l_date
4575 ,p_prepayment_date =>csr_rec.pre_effective_date
4576 ,p_payroll_action_id =>csr_rec.arc_payroll_action_id
4577 );
4578 pay_in_utils.set_location(g_debug,l_procedure, 140);
4579
4580 archive_pt_data(
4581 p_assignment_action_id =>nvl(l_reversal_asg_action_id,l_asg_id)
4582 ,p_archive_action_id =>csr_rec.chld_arc_assignment_action_id
4583 ,p_assignment_id =>csr_rec.assignment_id
4584 ,p_payroll_date =>l_date
4585 ,p_prepayment_date =>csr_rec.pre_effective_date
4586 ,p_payroll_action_id =>csr_rec.arc_payroll_action_id
4587 );
4588 ELSE
4589 EXIT;
4590 END IF;
4591 END LOOP;
4592 pay_in_utils.set_location(g_debug,l_procedure, 150);
4593 --
4594 -- Call to procedure to archive User Configurable Balances
4595 --
4596
4597 pay_apac_payslip_archive.archive_user_balances
4598 (
4599 p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
4600 ,p_run_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action id
4601 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effecive date
4602 );
4603
4604 pay_in_utils.set_location(g_debug,l_procedure, 160);
4605 --
4606 -- Call to procedure to archive User Configurable Elements
4607 --
4608
4609 pay_apac_payslip_archive.archive_user_elements
4610 (
4611 p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action
4612 ,p_pre_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
4613 ,p_latest_run_assact_id => csr_rec.run_assignment_action_id -- payroll assignment action id
4614 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
4615 );
4616
4617 pay_in_utils.set_location(g_debug,l_procedure, 170);
4618 --
4619 -- Call to procedure to archive Statutory Elements
4620 --
4621
4622 archive_stat_elements
4623 (
4624 p_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
4625 ,p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
4626 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
4627 );
4628
4629 pay_in_utils.set_location(g_debug,l_procedure, 180);
4630 --
4631 -- Call to procedure to archive Statutory Balances
4632 --
4633
4634 archive_stat_balances
4635 (
4636 p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action
4637 ,p_assignment_id => csr_rec.assignment_id -- assignment id
4638 ,p_date_earned => csr_rec.run_date_earned -- payroll date earned
4639 ,p_effective_date => csr_rec.pre_effective_date -- prepayments effective date
4640 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
4641 );
4642
4643 pay_in_utils.set_location(g_debug,l_procedure, 190);
4644 --
4645 -- Call to procedure to archive Employee Details
4646 --
4647
4648 archive_employee_details
4649 (
4650 p_payroll_action_id => csr_rec.arc_payroll_action_id -- archive payroll action id
4651 ,p_assactid => csr_rec.chld_arc_assignment_action_id -- archive action id
4652 ,p_pay_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
4653 ,p_assignment_id => csr_rec.assignment_id -- assignment_id
4654 ,p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
4655 ,p_date_earned => csr_rec.run_date_earned -- payroll date_earned
4656 ,p_latest_period_payment_date => l_payment_date -- latest payment date
4657 ,p_run_effective_date => csr_rec.run_effective_date -- run effective Date
4658 ,p_time_period_id => csr_rec.time_period_id -- time_period_id from per_time_periods
4659 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
4660 );
4661 pay_in_utils.set_location(g_debug,l_procedure, 200);
4662
4663 --
4664 -- Call to procedure to archive accrual details
4665 --
4666
4667 archive_accrual_details
4668 (
4669 p_payroll_action_id => csr_rec.run_payroll_action_id -- latest payroll action id
4670 ,p_time_period_id => csr_rec.time_period_id -- latest period time period id
4671 ,p_assignment_id => csr_rec.assignment_id -- assignment id
4672 ,p_date_earned => csr_rec.run_date_earned -- latest payroll date earned
4673 ,p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
4674 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
4675 ,p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
4676 ,p_period_end_date => csr_rec.period_end_date -- latest period end date
4677 ,p_period_start_date => csr_rec.period_start_date -- latest period start date
4678 );
4679
4680 pay_in_utils.set_location(g_debug,l_procedure, 210);
4681 --
4682 -- Call to procedure to archive absences
4683 --
4684
4685 archive_absences
4686 (
4687 p_arch_act_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
4688 ,p_assg_act_id => csr_rec.run_assignment_action_id -- payroll run action id
4689 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
4690 );
4691 pay_in_utils.set_location(g_debug,l_procedure, 220);
4692
4693 --
4694 -- Call to procedure to archive form24 tax Balances
4695 --
4696 archive_form24q_balances
4697 (
4698 p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action
4699 ,p_assignment_id => csr_rec.assignment_id -- assignment id
4700 ,p_date_earned => csr_rec.run_date_earned -- payroll date earned
4701 ,p_effective_date =>csr_rec.run_effective_date -- pre effective date
4702 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
4703 ,p_payroll_action_id => csr_rec.arc_payroll_action_id
4704 ,p_run_payroll_action_id =>csr_rec.run_payroll_action_id
4705 ,p_pre_assact_id => csr_rec.pre_assignment_action_id
4706 );
4707 pay_in_utils.set_location(g_debug,l_procedure, 230);
4708 --
4709 END LOOP;
4710 END IF;
4711 archive_payroll_data('PF');
4712 pay_in_utils.set_location(g_debug,l_procedure, 240);
4713
4714 archive_payroll_data('ESI');
4715 pay_in_utils.set_location(g_debug,l_procedure, 250);
4716
4717 archive_pt_payroll_data;
4718 pay_in_utils.set_location(g_debug,l_procedure, 260);
4719
4720 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 270);
4721 --
4722
4723 EXCEPTION
4724 WHEN OTHERS THEN
4725 IF get_payslip_aa%ISOPEN THEN
4726 CLOSE get_payslip_aa;
4727 END IF;
4728 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
4729 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 280);
4730 pay_in_utils.trace(l_message,l_procedure);
4731
4732 RAISE;
4733 --
4734 END archive_code;
4735 --
4736 END pay_in_payslip_archive;