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.
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:
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);
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: --
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;
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:
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: ------------------------------------------------------------------+
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);
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: --
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;
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:
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);
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);
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:
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:
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
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);
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;
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
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
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: --
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;
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:
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);
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);
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);
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);
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);
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);
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('**************************************************','********************');
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;
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:
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:
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
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
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);
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('**************************************************','********************');
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;
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: --
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;
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
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;
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: --
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;
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);
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:
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: --
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
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);
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;
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:
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);
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:
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:
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
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: --
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;
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: --
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);
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:
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:
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
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
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
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:
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: --
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
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;
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: --
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;
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('**************************************************','********************');
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);
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('**************************************************','********************');
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;
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:
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
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: (
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: --
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;
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: --
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);
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
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
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(
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,
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);
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: --
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;
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:
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);
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);
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);
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);
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);
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);
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;
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:
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);
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;
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;
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',
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 */
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 +
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);
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
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);
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: --
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;
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: --------------------------------------------------------------------------
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:
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);
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);
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('**************************************************','********************');
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;
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:
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:
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;
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
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;
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;
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
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: );
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
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: --
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: (
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;
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;
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);
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:
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
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
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
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
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: --
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;
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:
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);
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('**************************************************','********************');
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;
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:
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:
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
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);
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);
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;
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:
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:
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);
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:
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:
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;
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;
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:
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);
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:
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
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
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 ;
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
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;
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;
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;
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;
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;
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
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;
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');
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;
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');
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;
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,
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:
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:
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;
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:
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
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'
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:
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);
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:
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;
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:
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);
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);
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);
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;
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:
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:
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
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
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;
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;
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
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);
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;
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);
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
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
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
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;
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;
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: --
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;
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,
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');
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,
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,
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:
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
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:
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(
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
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;
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;
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:
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
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 ---------
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
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 ;
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
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
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:
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);
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;
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:
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: --------------------------------------------------------------------------
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;
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);
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
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
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);
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;
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
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
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;
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
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;
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;
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
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);
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:
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:
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);
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:
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;
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:
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);
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);
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);
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;
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:
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:
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:
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
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
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;
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');
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:
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
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;
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:
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
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(
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;
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(
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;
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;
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;
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);
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);
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;
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';
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);
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:
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;
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:
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);
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:
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');
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);
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);
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'
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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:
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;
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:
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);
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);
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;
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:
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;
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
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);
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:
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:
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);
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);
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 );
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;
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:
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
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
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
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
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:
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
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:
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
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);
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);
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);
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;
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:
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);
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;
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;
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
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:
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
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
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:
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:
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:
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:
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:
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: --
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:
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: --
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');
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:
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:
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:
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
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;
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: --
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;