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