[Home] [Help]
307:
308: lv_procedure_name varchar2(30) := 'load_ye_balance';
309:
310: begin
311: hr_utility.set_location(gv_package || lv_procedure_name, 10);
312: -- Clearing the global tables before initializing ..
313: g_archive_item_details.DELETE;
314: g_ptu_bal_details.DELETE;
315: g_ptu_balance_value_tab.DELETE;
316: g_ye_balance_value_tab.DELETE;
317:
318: /*This code will be move to script and mapped to this file if needed*/
319: pay_mx_archive_dbi_pkg.create_archive_routes;
320: hr_utility.trace('Called from initialization_code and initialized for whole process');
321: hr_utility.trace('Getting the year end balances ');
322: OPEN c_get_balances ;
323: FETCH c_get_balances BULK COLLECT INTO g_archive_item_details;
324: CLOSE c_get_balances;
317:
318: /*This code will be move to script and mapped to this file if needed*/
319: pay_mx_archive_dbi_pkg.create_archive_routes;
320: hr_utility.trace('Called from initialization_code and initialized for whole process');
321: hr_utility.trace('Getting the year end balances ');
322: OPEN c_get_balances ;
323: FETCH c_get_balances BULK COLLECT INTO g_archive_item_details;
324: CLOSE c_get_balances;
325: hr_utility.trace('Getting the PTU year end balances ');
321: hr_utility.trace('Getting the year end balances ');
322: OPEN c_get_balances ;
323: FETCH c_get_balances BULK COLLECT INTO g_archive_item_details;
324: CLOSE c_get_balances;
325: hr_utility.trace('Getting the PTU year end balances ');
326: OPEN c_get_PTU_balances;
327: FETCH c_get_PTU_balances BULK COLLECT INTO g_ptu_bal_details;
328: CLOSE c_get_PTU_balances;
329: /*This code will be move to script and mapped to this file if needed*/
389: BEGIN
390:
391: lv_procedure_name := '.load_gre';
392:
393: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
394:
395: hr_utility.trace ('parameters ...');
396: hr_utility.trace ('p_business_group_id = '||p_business_group_id);
397: hr_utility.trace ('p_le_id = '||p_le_id);
391: lv_procedure_name := '.load_gre';
392:
393: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
394:
395: hr_utility.trace ('parameters ...');
396: hr_utility.trace ('p_business_group_id = '||p_business_group_id);
397: hr_utility.trace ('p_le_id = '||p_le_id);
398: hr_utility.trace ('p_effective_date = '||p_effective_date);
399:
392:
393: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
394:
395: hr_utility.trace ('parameters ...');
396: hr_utility.trace ('p_business_group_id = '||p_business_group_id);
397: hr_utility.trace ('p_le_id = '||p_le_id);
398: hr_utility.trace ('p_effective_date = '||p_effective_date);
399:
400: g_gre_tab.delete();
393: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
394:
395: hr_utility.trace ('parameters ...');
396: hr_utility.trace ('p_business_group_id = '||p_business_group_id);
397: hr_utility.trace ('p_le_id = '||p_le_id);
398: hr_utility.trace ('p_effective_date = '||p_effective_date);
399:
400: g_gre_tab.delete();
401: OPEN csr_get_gres;
394:
395: hr_utility.trace ('parameters ...');
396: hr_utility.trace ('p_business_group_id = '||p_business_group_id);
397: hr_utility.trace ('p_le_id = '||p_le_id);
398: hr_utility.trace ('p_effective_date = '||p_effective_date);
399:
400: g_gre_tab.delete();
401: OPEN csr_get_gres;
402: LOOP
406: END LOOP;
407: CLOSE csr_get_gres;
408:
409: IF g_gre_tab.count() > 0 THEN
410: hr_utility.trace('List of GREs ...');
411: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
412: hr_utility.trace(g_gre_tab(cntr_gre));
413: END LOOP;
414: ELSE
408:
409: IF g_gre_tab.count() > 0 THEN
410: hr_utility.trace('List of GREs ...');
411: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
412: hr_utility.trace(g_gre_tab(cntr_gre));
413: END LOOP;
414: ELSE
415: hr_utility.trace('No GREs found.');
416: END IF;
411: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
412: hr_utility.trace(g_gre_tab(cntr_gre));
413: END LOOP;
414: ELSE
415: hr_utility.trace('No GREs found.');
416: END IF;
417:
418: hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
419: END load_gre;
414: ELSE
415: hr_utility.trace('No GREs found.');
416: END IF;
417:
418: hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
419: END load_gre;
420:
421: --------------------------------------------------------------------------
422: -- --
437: lv_procedure_name VARCHAR2(100);
438:
439: BEGIN
440: lv_procedure_name := '.gre_exists';
441: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
442: hr_utility.trace('p_gre_id = ' || p_gre_id);
443:
444: IF g_gre_tab.count() <> 0 THEN
445: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
438:
439: BEGIN
440: lv_procedure_name := '.gre_exists';
441: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
442: hr_utility.trace('p_gre_id = ' || p_gre_id);
443:
444: IF g_gre_tab.count() <> 0 THEN
445: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
446: IF g_gre_tab (cntr_gre) = p_gre_id THEN
443:
444: IF g_gre_tab.count() <> 0 THEN
445: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
446: IF g_gre_tab (cntr_gre) = p_gre_id THEN
447: hr_utility.trace ('GRE exists');
448: hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
449: RETURN 1;
450: END IF;
451: END LOOP;
444: IF g_gre_tab.count() <> 0 THEN
445: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
446: IF g_gre_tab (cntr_gre) = p_gre_id THEN
447: hr_utility.trace ('GRE exists');
448: hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
449: RETURN 1;
450: END IF;
451: END LOOP;
452: END IF;
450: END IF;
451: END LOOP;
452: END IF;
453:
454: hr_utility.trace ('GRE does not exist');
455: hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
456: RETURN 0;
457: END gre_exists;
458:
451: END LOOP;
452: END IF;
453:
454: hr_utility.trace ('GRE does not exist');
455: hr_utility.trace('Leaving '|| gv_package || lv_procedure_name);
456: RETURN 0;
457: END gre_exists;
458:
459: /******************************************************************************
495:
496: BEGIN
497: lv_procedure_name := '.get_payroll_action_info';
498:
499: hr_utility.set_location(gv_package || lv_procedure_name, 10);
500: ln_step := 1;
501: OPEN c_payroll_action_info(p_payroll_action_id);
502: FETCH c_payroll_action_info INTO ld_end_date,
503: ln_business_group_id,
504: ln_legal_er_id,
505: ln_asg_set_id;
506: CLOSE c_payroll_action_info;
507:
508: hr_utility.set_location(gv_package || lv_procedure_name, 30);
509:
510: p_end_date := ld_end_date;
511: p_business_group_id := ln_business_group_id;
512: p_legal_employer_id := ln_legal_er_id;
511: p_business_group_id := ln_business_group_id;
512: p_legal_employer_id := ln_legal_er_id;
513: p_asg_set_id := ln_asg_set_id;
514:
515: hr_utility.set_location(gv_package || lv_procedure_name, 50);
516: ln_step := 2;
517:
518: EXCEPTION
519: WHEN OTHERS THEN
519: WHEN OTHERS THEN
520: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
521: gv_package || lv_procedure_name;
522:
523: hr_utility.trace(lv_error_message || '-' || SQLERRM);
524:
525: lv_error_message :=
526: pay_emp_action_arch.set_error_message(lv_error_message);
527:
524:
525: lv_error_message :=
526: pay_emp_action_arch.set_error_message(lv_error_message);
527:
528: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
529: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
530: hr_utility.raise_error;
531:
532: END get_payroll_action_info;
525: lv_error_message :=
526: pay_emp_action_arch.set_error_message(lv_error_message);
527:
528: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
529: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
530: hr_utility.raise_error;
531:
532: END get_payroll_action_info;
533:
526: pay_emp_action_arch.set_error_message(lv_error_message);
527:
528: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
529: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
530: hr_utility.raise_error;
531:
532: END get_payroll_action_info;
533:
534: /******************************************************************************
576:
577: BEGIN
578: -- Creating the archiver item code.
579: lv_procedure_name := '.populate_balances';
580: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
581: hr_utility.set_location(gv_package || lv_procedure_name, 10);
582:
583: ln_value := p_balance_value;
584: OPEN c_get_arch_ue_id('A_' || p_user_entity_name);
577: BEGIN
578: -- Creating the archiver item code.
579: lv_procedure_name := '.populate_balances';
580: hr_utility.trace('Entering '|| gv_package || lv_procedure_name);
581: hr_utility.set_location(gv_package || lv_procedure_name, 10);
582:
583: ln_value := p_balance_value;
584: OPEN c_get_arch_ue_id('A_' || p_user_entity_name);
585: FETCH c_get_arch_ue_id INTO ln_arch_user_entity_id;
583: ln_value := p_balance_value;
584: OPEN c_get_arch_ue_id('A_' || p_user_entity_name);
585: FETCH c_get_arch_ue_id INTO ln_arch_user_entity_id;
586: CLOSE c_get_arch_ue_id;
587: hr_utility.set_location(gv_package || lv_procedure_name, 20);
588: hr_utility.trace('Archive User Entity ID: '|| ln_arch_user_entity_id);
589: hr_utility.trace('Item Name ' || p_user_entity_name);
590: hr_utility.trace('Value: ' || ln_value);
591:
584: OPEN c_get_arch_ue_id('A_' || p_user_entity_name);
585: FETCH c_get_arch_ue_id INTO ln_arch_user_entity_id;
586: CLOSE c_get_arch_ue_id;
587: hr_utility.set_location(gv_package || lv_procedure_name, 20);
588: hr_utility.trace('Archive User Entity ID: '|| ln_arch_user_entity_id);
589: hr_utility.trace('Item Name ' || p_user_entity_name);
590: hr_utility.trace('Value: ' || ln_value);
591:
592: IF p_prev_archiver_exists = 'Y' AND p_user_entity_name = 'ISR_WITHHELD_PER_PDS_GRE_YTD' THEN
585: FETCH c_get_arch_ue_id INTO ln_arch_user_entity_id;
586: CLOSE c_get_arch_ue_id;
587: hr_utility.set_location(gv_package || lv_procedure_name, 20);
588: hr_utility.trace('Archive User Entity ID: '|| ln_arch_user_entity_id);
589: hr_utility.trace('Item Name ' || p_user_entity_name);
590: hr_utility.trace('Value: ' || ln_value);
591:
592: IF p_prev_archiver_exists = 'Y' AND p_user_entity_name = 'ISR_WITHHELD_PER_PDS_GRE_YTD' THEN
593:
586: CLOSE c_get_arch_ue_id;
587: hr_utility.set_location(gv_package || lv_procedure_name, 20);
588: hr_utility.trace('Archive User Entity ID: '|| ln_arch_user_entity_id);
589: hr_utility.trace('Item Name ' || p_user_entity_name);
590: hr_utility.trace('Value: ' || ln_value);
591:
592: IF p_prev_archiver_exists = 'Y' AND p_user_entity_name = 'ISR_WITHHELD_PER_PDS_GRE_YTD' THEN
593:
594: /**************************************************************
596: ** ISR Withheld YTD - ISR Withheld of previous archived value
597: ** Whenevere there is previous archiver is run.
598: **************************************************************/
599: begin
600: hr_utility.set_location(gv_package || lv_procedure_name, 30);
601: SELECT fai.value
602: INTO ln_prev_isr_whld_value
603: FROM ff_archive_items fai
604: WHERE fai.context1 = gn_prev_asg_act_id
602: INTO ln_prev_isr_whld_value
603: FROM ff_archive_items fai
604: WHERE fai.context1 = gn_prev_asg_act_id
605: AND fai.user_entity_id = ln_arch_user_entity_id;
606: hr_utility.set_location(gv_package || lv_procedure_name, 40);
607: exception
608: when no_data_found then
609: ln_prev_isr_whld_value := 0;
610: end;
612:
613: ln_value := ln_value - ln_prev_isr_whld_value;
614:
615: END IF;
616: hr_utility.set_location(gv_package || lv_procedure_name, 50);
617: select count(1)
618: into ln_arc_item
619: from ff_archive_items fai,
620: ff_archive_item_contexts faic
638: p_context_name1 => 'TAX_UNIT_ID',
639: p_context1 => p_tax_unit_id);
640: end if;
641:
642: hr_utility.set_location(gv_package || lv_procedure_name, 60);
643: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
644: END create_archive_item;
645:
646:
639: p_context1 => p_tax_unit_id);
640: end if;
641:
642: hr_utility.set_location(gv_package || lv_procedure_name, 60);
643: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
644: END create_archive_item;
645:
646:
647: /************************************************************
694: lt_ptu_bal_result_tab.DELETE;
695:
696: lt_ye_bal_context_tab(1).tax_unit_id := p_tax_unit_id;
697:
698: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
699: --
700: -- pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
701:
702: -- Create the Archive DBI routes if they don't exist
700: -- pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
701:
702: -- Create the Archive DBI routes if they don't exist
703: --
704: hr_utility.set_location(gv_package || lv_procedure_name, 10);
705: /* pay_mx_archive_dbi_pkg.create_archive_routes;*/
706: hr_utility.trace('p_prev_archiver_exists ' || p_prev_archiver_exists);
707: hr_utility.trace('p_ytd_action_id ' || p_ytd_action_id);
708: /*Modified the code logic, by using BBR for bug 7529502*/
702: -- Create the Archive DBI routes if they don't exist
703: --
704: hr_utility.set_location(gv_package || lv_procedure_name, 10);
705: /* pay_mx_archive_dbi_pkg.create_archive_routes;*/
706: hr_utility.trace('p_prev_archiver_exists ' || p_prev_archiver_exists);
707: hr_utility.trace('p_ytd_action_id ' || p_ytd_action_id);
708: /*Modified the code logic, by using BBR for bug 7529502*/
709: IF p_prev_archiver_exists = 'Y' THEN
710:
703: --
704: hr_utility.set_location(gv_package || lv_procedure_name, 10);
705: /* pay_mx_archive_dbi_pkg.create_archive_routes;*/
706: hr_utility.trace('p_prev_archiver_exists ' || p_prev_archiver_exists);
707: hr_utility.trace('p_ytd_action_id ' || p_ytd_action_id);
708: /*Modified the code logic, by using BBR for bug 7529502*/
709: IF p_prev_archiver_exists = 'Y' THEN
710:
711: pay_balance_pkg.get_value
716: );
717:
718: FOR j IN g_ptu_bal_details.first..g_ptu_bal_details.last
719: LOOP
720: hr_utility.trace('About populate ptu balance');
721: hr_utility.trace('Def Bal Id....' ||g_ptu_bal_details(j).def_bal_id);
722: hr_utility.trace('lt_ptu_bal_result_tab Id....' ||lt_ptu_bal_result_tab(j).defined_balance_id);
723: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name);
724: ln_count := ln_count +1;
717:
718: FOR j IN g_ptu_bal_details.first..g_ptu_bal_details.last
719: LOOP
720: hr_utility.trace('About populate ptu balance');
721: hr_utility.trace('Def Bal Id....' ||g_ptu_bal_details(j).def_bal_id);
722: hr_utility.trace('lt_ptu_bal_result_tab Id....' ||lt_ptu_bal_result_tab(j).defined_balance_id);
723: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name);
724: ln_count := ln_count +1;
725: g_ptu_bal_details(j).bal_value := nvl(lt_ptu_bal_result_tab(j).balance_value,0);
718: FOR j IN g_ptu_bal_details.first..g_ptu_bal_details.last
719: LOOP
720: hr_utility.trace('About populate ptu balance');
721: hr_utility.trace('Def Bal Id....' ||g_ptu_bal_details(j).def_bal_id);
722: hr_utility.trace('lt_ptu_bal_result_tab Id....' ||lt_ptu_bal_result_tab(j).defined_balance_id);
723: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name);
724: ln_count := ln_count +1;
725: g_ptu_bal_details(j).bal_value := nvl(lt_ptu_bal_result_tab(j).balance_value,0);
726: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name||' bal value' || g_ptu_bal_details(j).bal_value);
719: LOOP
720: hr_utility.trace('About populate ptu balance');
721: hr_utility.trace('Def Bal Id....' ||g_ptu_bal_details(j).def_bal_id);
722: hr_utility.trace('lt_ptu_bal_result_tab Id....' ||lt_ptu_bal_result_tab(j).defined_balance_id);
723: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name);
724: ln_count := ln_count +1;
725: g_ptu_bal_details(j).bal_value := nvl(lt_ptu_bal_result_tab(j).balance_value,0);
726: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name||' bal value' || g_ptu_bal_details(j).bal_value);
727: ltab_entity_det(ln_count) := g_ptu_bal_details(j);
722: hr_utility.trace('lt_ptu_bal_result_tab Id....' ||lt_ptu_bal_result_tab(j).defined_balance_id);
723: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name);
724: ln_count := ln_count +1;
725: g_ptu_bal_details(j).bal_value := nvl(lt_ptu_bal_result_tab(j).balance_value,0);
726: hr_utility.trace('Item Name ....' ||g_ptu_bal_details(j).user_entity_name||' bal value' || g_ptu_bal_details(j).bal_value);
727: ltab_entity_det(ln_count) := g_ptu_bal_details(j);
728: -- creating archive item for the assignment action
729: create_archive_item(p_user_entity_name => g_ptu_bal_details(j).user_entity_name
730: ,p_balance_value => g_ptu_bal_details(j).bal_value
743: );
744:
745: FOR i IN g_archive_item_details.first..g_archive_item_details.last
746: LOOP
747: hr_utility.trace('About populate other balance');
748: hr_utility.trace('Def Bal Id....' ||g_archive_item_details(i).def_bal_id);
749: hr_utility.trace('lt_ye_bal_result_tab Id....' ||lt_ye_bal_result_tab(i).defined_balance_id);
750: hr_utility.trace('Item Name ....' ||g_archive_item_details(i).user_entity_name);
751: ln_count := ln_count +1;
744:
745: FOR i IN g_archive_item_details.first..g_archive_item_details.last
746: LOOP
747: hr_utility.trace('About populate other balance');
748: hr_utility.trace('Def Bal Id....' ||g_archive_item_details(i).def_bal_id);
749: hr_utility.trace('lt_ye_bal_result_tab Id....' ||lt_ye_bal_result_tab(i).defined_balance_id);
750: hr_utility.trace('Item Name ....' ||g_archive_item_details(i).user_entity_name);
751: ln_count := ln_count +1;
752: g_archive_item_details(i).bal_value := nvl(lt_ye_bal_result_tab(i).balance_value,0);
745: FOR i IN g_archive_item_details.first..g_archive_item_details.last
746: LOOP
747: hr_utility.trace('About populate other balance');
748: hr_utility.trace('Def Bal Id....' ||g_archive_item_details(i).def_bal_id);
749: hr_utility.trace('lt_ye_bal_result_tab Id....' ||lt_ye_bal_result_tab(i).defined_balance_id);
750: hr_utility.trace('Item Name ....' ||g_archive_item_details(i).user_entity_name);
751: ln_count := ln_count +1;
752: g_archive_item_details(i).bal_value := nvl(lt_ye_bal_result_tab(i).balance_value,0);
753: IF (g_archive_item_details(i).user_entity_name = 'ISR_TAX_TO_CHARGE_PER_PDS_GRE_YTD') THEN
746: LOOP
747: hr_utility.trace('About populate other balance');
748: hr_utility.trace('Def Bal Id....' ||g_archive_item_details(i).def_bal_id);
749: hr_utility.trace('lt_ye_bal_result_tab Id....' ||lt_ye_bal_result_tab(i).defined_balance_id);
750: hr_utility.trace('Item Name ....' ||g_archive_item_details(i).user_entity_name);
751: ln_count := ln_count +1;
752: g_archive_item_details(i).bal_value := nvl(lt_ye_bal_result_tab(i).balance_value,0);
753: IF (g_archive_item_details(i).user_entity_name = 'ISR_TAX_TO_CHARGE_PER_PDS_GRE_YTD') THEN
754: ln_tax_to_charge_val := g_archive_item_details(i).bal_value;
752: g_archive_item_details(i).bal_value := nvl(lt_ye_bal_result_tab(i).balance_value,0);
753: IF (g_archive_item_details(i).user_entity_name = 'ISR_TAX_TO_CHARGE_PER_PDS_GRE_YTD') THEN
754: ln_tax_to_charge_val := g_archive_item_details(i).bal_value;
755: END IF;
756: hr_utility.trace('Item Name ....' ||g_archive_item_details(i).user_entity_name||' bal value' || g_archive_item_details(i).bal_value);
757: ltab_entity_det(ln_count) := g_archive_item_details(i);
758:
759: -- creating archive item for the assignment action
760: IF (g_archive_item_details(i).user_entity_name <> 'ISR_TAX_TO_CHARGE_PER_PDS_GRE_YTD') THEN
787: ,p_archive_action_id => p_archive_action_id );
788:
789: END IF;
790:
791: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
792: --
793: END populate_balances;
794:
795: /******************************************************************
816:
817: BEGIN
818: lv_procedure_name := '.range_code';
819:
820: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
821: hr_utility.set_location(gv_package || lv_procedure_name, 10);
822: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
823: ,p_end_date => ld_end_date
824: ,p_business_group_id => ln_business_group_id
817: BEGIN
818: lv_procedure_name := '.range_code';
819:
820: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
821: hr_utility.set_location(gv_package || lv_procedure_name, 10);
822: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
823: ,p_end_date => ld_end_date
824: ,p_business_group_id => ln_business_group_id
825: ,p_legal_employer_id => ln_legal_employer_id
823: ,p_end_date => ld_end_date
824: ,p_business_group_id => ln_business_group_id
825: ,p_legal_employer_id => ln_legal_employer_id
826: ,p_asg_set_id => ln_asg_set_id);
827: hr_utility.set_location(gv_package || lv_procedure_name, 20);
828:
829: load_gre (ln_business_group_id,
830: ln_legal_employer_id,
831: ld_end_date);
888: ORDER BY paf.person_id';
889:
890: END IF; -- ln_asg_set_id is null
891:
892: hr_utility.set_location(gv_package || lv_procedure_name, 30);
893: p_sqlstr := lv_sql_string;
894: hr_utility.trace ('SQL string :' ||p_sqlstr);
895: hr_utility.set_location(gv_package || lv_procedure_name, 50);
896: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
890: END IF; -- ln_asg_set_id is null
891:
892: hr_utility.set_location(gv_package || lv_procedure_name, 30);
893: p_sqlstr := lv_sql_string;
894: hr_utility.trace ('SQL string :' ||p_sqlstr);
895: hr_utility.set_location(gv_package || lv_procedure_name, 50);
896: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
897:
898: END range_code;
891:
892: hr_utility.set_location(gv_package || lv_procedure_name, 30);
893: p_sqlstr := lv_sql_string;
894: hr_utility.trace ('SQL string :' ||p_sqlstr);
895: hr_utility.set_location(gv_package || lv_procedure_name, 50);
896: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
897:
898: END range_code;
899:
892: hr_utility.set_location(gv_package || lv_procedure_name, 30);
893: p_sqlstr := lv_sql_string;
894: hr_utility.trace ('SQL string :' ||p_sqlstr);
895: hr_utility.set_location(gv_package || lv_procedure_name, 50);
896: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
897:
898: END range_code;
899:
900: /************************************************************
1064: ld_prev_arch_eff_date DATE;
1065:
1066: BEGIN
1067: lv_procedure_name := '.assignment_action_code';
1068: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1069:
1070: ln_pos_id := -1;
1071: ln_prev_pos_id := -1;
1072: lv_excl_flag := '-1';
1071: ln_prev_pos_id := -1;
1072: lv_excl_flag := '-1';
1073: lv_run_exists := 'N';
1074:
1075: hr_utility.trace('p_payroll_action_id = '|| p_payroll_action_id);
1076: hr_utility.trace('p_start_person_id = '|| p_start_person_id);
1077: hr_utility.trace('p_end_person_id = '|| p_end_person_id);
1078: hr_utility.trace('p_chunk = '|| p_chunk);
1079:
1072: lv_excl_flag := '-1';
1073: lv_run_exists := 'N';
1074:
1075: hr_utility.trace('p_payroll_action_id = '|| p_payroll_action_id);
1076: hr_utility.trace('p_start_person_id = '|| p_start_person_id);
1077: hr_utility.trace('p_end_person_id = '|| p_end_person_id);
1078: hr_utility.trace('p_chunk = '|| p_chunk);
1079:
1080: ln_step := 1;
1073: lv_run_exists := 'N';
1074:
1075: hr_utility.trace('p_payroll_action_id = '|| p_payroll_action_id);
1076: hr_utility.trace('p_start_person_id = '|| p_start_person_id);
1077: hr_utility.trace('p_end_person_id = '|| p_end_person_id);
1078: hr_utility.trace('p_chunk = '|| p_chunk);
1079:
1080: ln_step := 1;
1081: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1074:
1075: hr_utility.trace('p_payroll_action_id = '|| p_payroll_action_id);
1076: hr_utility.trace('p_start_person_id = '|| p_start_person_id);
1077: hr_utility.trace('p_end_person_id = '|| p_end_person_id);
1078: hr_utility.trace('p_chunk = '|| p_chunk);
1079:
1080: ln_step := 1;
1081: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1082:
1077: hr_utility.trace('p_end_person_id = '|| p_end_person_id);
1078: hr_utility.trace('p_chunk = '|| p_chunk);
1079:
1080: ln_step := 1;
1081: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1082:
1083: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1084: ,p_end_date => ld_end_date
1085: ,p_business_group_id => ln_business_group_id
1085: ,p_business_group_id => ln_business_group_id
1086: ,p_legal_employer_id => ln_legal_employer_id
1087: ,p_asg_set_id => ln_asg_set_id);
1088:
1089: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1090: hr_utility.trace('ld_end_date: ' || ld_end_date);
1091: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1092: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1093: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1086: ,p_legal_employer_id => ln_legal_employer_id
1087: ,p_asg_set_id => ln_asg_set_id);
1088:
1089: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1090: hr_utility.trace('ld_end_date: ' || ld_end_date);
1091: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1092: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1093: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1094:
1087: ,p_asg_set_id => ln_asg_set_id);
1088:
1089: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1090: hr_utility.trace('ld_end_date: ' || ld_end_date);
1091: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1092: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1093: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1094:
1095: ld_start_date := TRUNC(ld_end_date, 'Y');
1088:
1089: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1090: hr_utility.trace('ld_end_date: ' || ld_end_date);
1091: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1092: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1093: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1094:
1095: ld_start_date := TRUNC(ld_end_date, 'Y');
1096:
1089: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1090: hr_utility.trace('ld_end_date: ' || ld_end_date);
1091: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1092: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1093: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1094:
1095: ld_start_date := TRUNC(ld_end_date, 'Y');
1096:
1097: hr_utility.trace('ld_start_date: ' || ld_start_date);
1093: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1094:
1095: ld_start_date := TRUNC(ld_end_date, 'Y');
1096:
1097: hr_utility.trace('ld_start_date: ' || ld_start_date);
1098:
1099: IF g_gre_tab.count() = 0 THEN
1100:
1101: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1097: hr_utility.trace('ld_start_date: ' || ld_start_date);
1098:
1099: IF g_gre_tab.count() = 0 THEN
1100:
1101: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1102:
1103: load_gre (ln_business_group_id,
1104: ln_legal_employer_id,
1105: ld_end_date);
1112: ,p_report_category => 'ARCHIVE');
1113:
1114: FOR cntr_gre IN g_gre_tab.first()..g_gre_tab.last() LOOP
1115: IF lb_range_person THEN
1116: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1117:
1118: OPEN c_get_emp_asg_range(g_gre_tab(cntr_gre),
1119: ld_start_date,
1120: ld_end_date);
1118: OPEN c_get_emp_asg_range(g_gre_tab(cntr_gre),
1119: ld_start_date,
1120: ld_end_date);
1121: ELSE
1122: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1123:
1124: OPEN c_get_emp_asg (g_gre_tab(cntr_gre),
1125: ln_business_group_id,
1126: ld_start_date,
1129:
1130: LOOP
1131: IF lb_range_person THEN
1132:
1133: hr_utility.trace('lb_range_person');
1134:
1135: FETCH c_get_emp_asg_range INTO ln_assignment_id,
1136: ln_person_id,
1137: ln_pos_id;
1142: ln_pos_id;
1143: EXIT WHEN c_get_emp_asg%NOTFOUND;
1144: END IF;
1145:
1146: hr_utility.trace('Previous period of service = ' || ln_prev_pos_id);
1147: hr_utility.trace('Current period of service = ' || ln_pos_id);
1148: hr_utility.trace('Person ID= ' || ln_person_id);
1149: hr_utility.trace('Assignment ID= ' || ln_assignment_id);
1150:
1143: EXIT WHEN c_get_emp_asg%NOTFOUND;
1144: END IF;
1145:
1146: hr_utility.trace('Previous period of service = ' || ln_prev_pos_id);
1147: hr_utility.trace('Current period of service = ' || ln_pos_id);
1148: hr_utility.trace('Person ID= ' || ln_person_id);
1149: hr_utility.trace('Assignment ID= ' || ln_assignment_id);
1150:
1151: IF ln_pos_id <> ln_prev_pos_id THEN
1144: END IF;
1145:
1146: hr_utility.trace('Previous period of service = ' || ln_prev_pos_id);
1147: hr_utility.trace('Current period of service = ' || ln_pos_id);
1148: hr_utility.trace('Person ID= ' || ln_person_id);
1149: hr_utility.trace('Assignment ID= ' || ln_assignment_id);
1150:
1151: IF ln_pos_id <> ln_prev_pos_id THEN
1152:
1145:
1146: hr_utility.trace('Previous period of service = ' || ln_prev_pos_id);
1147: hr_utility.trace('Current period of service = ' || ln_pos_id);
1148: hr_utility.trace('Person ID= ' || ln_person_id);
1149: hr_utility.trace('Assignment ID= ' || ln_assignment_id);
1150:
1151: IF ln_pos_id <> ln_prev_pos_id THEN
1152:
1153: ln_prev_pos_id := ln_pos_id;
1153: ln_prev_pos_id := ln_pos_id;
1154:
1155: IF ln_asg_set_id IS NOT NULL THEN
1156:
1157: hr_utility.set_location(gv_package || lv_procedure_name,60);
1158: hr_utility.trace('Assignment SET ID FOUND');
1159:
1160: lv_excl_flag := '-1';
1161: OPEN c_chk_asg (ln_asg_set_id, ln_assignment_id);
1154:
1155: IF ln_asg_set_id IS NOT NULL THEN
1156:
1157: hr_utility.set_location(gv_package || lv_procedure_name,60);
1158: hr_utility.trace('Assignment SET ID FOUND');
1159:
1160: lv_excl_flag := '-1';
1161: OPEN c_chk_asg (ln_asg_set_id, ln_assignment_id);
1162: FETCH c_chk_asg INTO lv_excl_flag;
1164:
1165:
1166: END IF;
1167:
1168: hr_utility.trace('lv_excl_flag: '||lv_excl_flag);
1169:
1170: IF lv_excl_flag <> 'X' THEN
1171:
1172: ld_prev_arch_eff_date := NULL;
1171:
1172: ld_prev_arch_eff_date := NULL;
1173: ln_prev_arch_aaid := NULL;
1174:
1175: hr_utility.set_location(gv_package || lv_procedure_name,70);
1176: /*bug:11715919 */
1177: OPEN c_chk_last_archiver(ln_pos_id,
1178: ld_start_date,
1179: ld_end_date,
1187: -- A previous Year End Archiver run exists for the person's
1188: -- period of service.
1189: --
1190:
1191: hr_utility.trace('Prev Arch Effective Date = ' ||
1192: fnd_date.date_to_canonical(ld_prev_arch_eff_date));
1193: hr_utility.trace('Prev Arch Asg Action ID = ' ||
1194: ln_prev_arch_aaid);
1195: hr_utility.set_location(gv_package ||
1189: --
1190:
1191: hr_utility.trace('Prev Arch Effective Date = ' ||
1192: fnd_date.date_to_canonical(ld_prev_arch_eff_date));
1193: hr_utility.trace('Prev Arch Asg Action ID = ' ||
1194: ln_prev_arch_aaid);
1195: hr_utility.set_location(gv_package ||
1196: lv_procedure_name, 80);
1197:
1191: hr_utility.trace('Prev Arch Effective Date = ' ||
1192: fnd_date.date_to_canonical(ld_prev_arch_eff_date));
1193: hr_utility.trace('Prev Arch Asg Action ID = ' ||
1194: ln_prev_arch_aaid);
1195: hr_utility.set_location(gv_package ||
1196: lv_procedure_name, 80);
1197:
1198: lv_run_exists := 'N';
1199:
1206: ld_end_date);
1207: FETCH c_chk_non_arch_runs INTO lv_run_exists;
1208: CLOSE c_chk_non_arch_runs;
1209:
1210: hr_utility.trace('lv_run_exists: '||lv_run_exists);
1211:
1212: IF lv_run_exists = 'Y' THEN
1213:
1214: -- The person has had a SEQUENCED action since the last
1215: -- archiver run and should therefore be archived. The
1216: -- last archiver action will be locked by this new
1217: -- action.
1218:
1219: hr_utility.set_location(gv_package ||
1220: lv_procedure_name, 90);
1221:
1222: SELECT pay_assignment_actions_s.NEXTVAL
1223: INTO ln_yrend_action_id
1231: NULL,
1232: 'U',
1233: NULL);
1234:
1235: hr_utility.set_location(gv_package ||
1236: lv_procedure_name, 100);
1237:
1238: UPDATE pay_assignment_actions
1239: SET serial_number = ln_person_id
1238: UPDATE pay_assignment_actions
1239: SET serial_number = ln_person_id
1240: WHERE assignment_action_id = ln_yrend_action_id;
1241:
1242: hr_utility.trace('Archiver asg action ' ||
1243: ln_yrend_action_id || ' created.');
1244:
1245: -- insert an interlock to this action
1246: hr_utility.trace('Locking Action = ' ||
1242: hr_utility.trace('Archiver asg action ' ||
1243: ln_yrend_action_id || ' created.');
1244:
1245: -- insert an interlock to this action
1246: hr_utility.trace('Locking Action = ' ||
1247: ln_yrend_action_id);
1248: hr_utility.trace('Locked Action = ' ||
1249: ln_prev_arch_aaid);
1250: hr_nonrun_asact.insint(ln_yrend_action_id,
1244:
1245: -- insert an interlock to this action
1246: hr_utility.trace('Locking Action = ' ||
1247: ln_yrend_action_id);
1248: hr_utility.trace('Locked Action = ' ||
1249: ln_prev_arch_aaid);
1250: hr_nonrun_asact.insint(ln_yrend_action_id,
1251: ln_prev_arch_aaid);
1252:
1249: ln_prev_arch_aaid);
1250: hr_nonrun_asact.insint(ln_yrend_action_id,
1251: ln_prev_arch_aaid);
1252:
1253: hr_utility.set_location(gv_package ||
1254: lv_procedure_name, 110);
1255:
1256: ELSE
1257:
1254: lv_procedure_name, 110);
1255:
1256: ELSE
1257:
1258: hr_utility.set_location(gv_package ||
1259: lv_procedure_name, 120);
1260:
1261: hr_utility.trace('The person has not been paid ' ||
1262: 'since the last archiver and is therefore skipped.');
1257:
1258: hr_utility.set_location(gv_package ||
1259: lv_procedure_name, 120);
1260:
1261: hr_utility.trace('The person has not been paid ' ||
1262: 'since the last archiver and is therefore skipped.');
1263:
1264: END IF;
1265:
1268: -- No previous archiver run exists for this person.
1269: -- New assignment action would still be created, but no
1270: -- interlocks inserted.
1271:
1272: hr_utility.set_location(gv_package ||
1273: lv_procedure_name, 130);
1274:
1275: SELECT pay_assignment_actions_s.NEXTVAL
1276: INTO ln_yrend_action_id
1275: SELECT pay_assignment_actions_s.NEXTVAL
1276: INTO ln_yrend_action_id
1277: FROM dual;
1278:
1279: hr_utility.set_location(gv_package ||
1280: lv_procedure_name, 140);
1281:
1282: hr_nonrun_asact.insact(ln_yrend_action_id,
1283: ln_assignment_id,
1287: NULL,
1288: 'U',
1289: NULL);
1290:
1291: hr_utility.set_location(gv_package ||
1292: lv_procedure_name, 150);
1293:
1294: UPDATE pay_assignment_actions
1295: SET serial_number = ln_person_id
1294: UPDATE pay_assignment_actions
1295: SET serial_number = ln_person_id
1296: WHERE assignment_action_id = ln_yrend_action_id;
1297:
1298: hr_utility.trace('Archiver asg action ' ||
1299: ln_yrend_action_id || ' created.');
1300:
1301: END IF;
1302:
1300:
1301: END IF;
1302:
1303: ELSE
1304: hr_utility.trace('Assignment is excluded in asg set.');
1305: lv_excl_flag := '-1';
1306: END IF;
1307: ELSE
1308: hr_utility.trace ('Assignment skipped.');
1304: hr_utility.trace('Assignment is excluded in asg set.');
1305: lv_excl_flag := '-1';
1306: END IF;
1307: ELSE
1308: hr_utility.trace ('Assignment skipped.');
1309: END IF;
1310: END LOOP;
1311:
1312: IF lb_range_person THEN
1315: CLOSE c_get_emp_asg;
1316: END IF;
1317: END LOOP;
1318:
1319: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
1320:
1321: EXCEPTION
1322: WHEN OTHERS THEN
1323: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1322: WHEN OTHERS THEN
1323: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1324: gv_package || lv_procedure_name;
1325:
1326: hr_utility.trace(lv_error_message || '-' || SQLERRM);
1327:
1328: lv_error_message :=
1329: pay_emp_action_arch.set_error_message(lv_error_message);
1330:
1327:
1328: lv_error_message :=
1329: pay_emp_action_arch.set_error_message(lv_error_message);
1330:
1331: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1332: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1333: hr_utility.raise_error;
1334:
1335: END assignment_action_code;
1328: lv_error_message :=
1329: pay_emp_action_arch.set_error_message(lv_error_message);
1330:
1331: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1332: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1333: hr_utility.raise_error;
1334:
1335: END assignment_action_code;
1336:
1329: pay_emp_action_arch.set_error_message(lv_error_message);
1330:
1331: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1332: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1333: hr_utility.raise_error;
1334:
1335: END assignment_action_code;
1336:
1337: /************************************************************
1373: lv_procedure_name := '.initialization_code';
1374:
1375: ln_step := 1;
1376:
1377: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1378:
1379: get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
1380: ,p_end_date => ld_end_date
1381: ,p_business_group_id => ln_business_group_id
1383: ,p_asg_set_id => ln_asg_set_id);
1384:
1385: g_payroll_action_id := p_payroll_action_id;
1386:
1387: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1388:
1389: ln_step := 2;
1390:
1391: OPEN c_get_legal_er_info(ln_legal_employer_id,
1396: g_ER_legal_rep_RFC,
1397: g_ER_legal_rep_CURP;
1398: CLOSE c_get_legal_er_info;
1399:
1400: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1401:
1402: ln_step := 3;
1403:
1404: SELECT TO_CHAR(ld_end_date, 'YYYY')
1404: SELECT TO_CHAR(ld_end_date, 'YYYY')
1405: INTO g_fiscal_year
1406: FROM dual;
1407:
1408: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1409: hr_utility.trace('About to initialize the route and database items');
1410: /*Added for bug 7529502*/
1411: load_ye_balance;
1412: hr_utility.trace('Global data for archive items are set ' ||g_archive_item_details.count);
1405: INTO g_fiscal_year
1406: FROM dual;
1407:
1408: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1409: hr_utility.trace('About to initialize the route and database items');
1410: /*Added for bug 7529502*/
1411: load_ye_balance;
1412: hr_utility.trace('Global data for archive items are set ' ||g_archive_item_details.count);
1413: hr_utility.trace('Global data for PTU balance are set ' ||g_ptu_bal_details.count);
1408: hr_utility.set_location(gv_package || lv_procedure_name, 40);
1409: hr_utility.trace('About to initialize the route and database items');
1410: /*Added for bug 7529502*/
1411: load_ye_balance;
1412: hr_utility.trace('Global data for archive items are set ' ||g_archive_item_details.count);
1413: hr_utility.trace('Global data for PTU balance are set ' ||g_ptu_bal_details.count);
1414: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1415: EXCEPTION
1416: WHEN OTHERS THEN
1409: hr_utility.trace('About to initialize the route and database items');
1410: /*Added for bug 7529502*/
1411: load_ye_balance;
1412: hr_utility.trace('Global data for archive items are set ' ||g_archive_item_details.count);
1413: hr_utility.trace('Global data for PTU balance are set ' ||g_ptu_bal_details.count);
1414: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1415: EXCEPTION
1416: WHEN OTHERS THEN
1417: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1410: /*Added for bug 7529502*/
1411: load_ye_balance;
1412: hr_utility.trace('Global data for archive items are set ' ||g_archive_item_details.count);
1413: hr_utility.trace('Global data for PTU balance are set ' ||g_ptu_bal_details.count);
1414: hr_utility.set_location(gv_package || lv_procedure_name, 50);
1415: EXCEPTION
1416: WHEN OTHERS THEN
1417: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1418: gv_package || lv_procedure_name;
1416: WHEN OTHERS THEN
1417: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
1418: gv_package || lv_procedure_name;
1419:
1420: hr_utility.trace(lv_error_message || '-' || SQLERRM);
1421:
1422: lv_error_message :=
1423: pay_emp_action_arch.set_error_message(lv_error_message);
1424:
1421:
1422: lv_error_message :=
1423: pay_emp_action_arch.set_error_message(lv_error_message);
1424:
1425: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1426: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1427: hr_utility.raise_error;
1428:
1429: END initialization_code;
1422: lv_error_message :=
1423: pay_emp_action_arch.set_error_message(lv_error_message);
1424:
1425: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1426: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1427: hr_utility.raise_error;
1428:
1429: END initialization_code;
1430:
1423: pay_emp_action_arch.set_error_message(lv_error_message);
1424:
1425: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1426: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1427: hr_utility.raise_error;
1428:
1429: END initialization_code;
1430:
1431: /************************************************************
1955:
1956:
1957: BEGIN
1958: lv_procedure_name := '.archive_code';
1959: --hr_utility.trace_on(null,'MX_NR');
1960: lv_prev_arch_exists := 'N';
1961: lv_arch_for_ptu_only := 'N';
1962: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1963:
1958: lv_procedure_name := '.archive_code';
1959: --hr_utility.trace_on(null,'MX_NR');
1960: lv_prev_arch_exists := 'N';
1961: lv_arch_for_ptu_only := 'N';
1962: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1963:
1964: hr_utility.trace('p_archive_action_id: ' ||p_archive_action_id);
1965: hr_utility.trace('p_effective_date: ' ||p_effective_date);
1966:
1960: lv_prev_arch_exists := 'N';
1961: lv_arch_for_ptu_only := 'N';
1962: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1963:
1964: hr_utility.trace('p_archive_action_id: ' ||p_archive_action_id);
1965: hr_utility.trace('p_effective_date: ' ||p_effective_date);
1966:
1967: ln_step := 1;
1968: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1961: lv_arch_for_ptu_only := 'N';
1962: hr_utility.trace('Entering ' || gv_package || lv_procedure_name);
1963:
1964: hr_utility.trace('p_archive_action_id: ' ||p_archive_action_id);
1965: hr_utility.trace('p_effective_date: ' ||p_effective_date);
1966:
1967: ln_step := 1;
1968: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1969:
1964: hr_utility.trace('p_archive_action_id: ' ||p_archive_action_id);
1965: hr_utility.trace('p_effective_date: ' ||p_effective_date);
1966:
1967: ln_step := 1;
1968: hr_utility.set_location(gv_package || lv_procedure_name, 10);
1969:
1970: -- Load GRE cache
1971: IF g_gre_tab.count() = 0 THEN
1972:
1969:
1970: -- Load GRE cache
1971: IF g_gre_tab.count() = 0 THEN
1972:
1973: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1974:
1975: get_payroll_action_info(p_payroll_action_id => g_payroll_action_id
1976: ,p_end_date => ld_end_date
1977: ,p_business_group_id => ln_business_group_id
1977: ,p_business_group_id => ln_business_group_id
1978: ,p_legal_employer_id => ln_legal_employer_id
1979: ,p_asg_set_id => ln_asg_set_id);
1980:
1981: hr_utility.trace('ld_end_date: ' || ld_end_date);
1982: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1983: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1984: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1985:
1978: ,p_legal_employer_id => ln_legal_employer_id
1979: ,p_asg_set_id => ln_asg_set_id);
1980:
1981: hr_utility.trace('ld_end_date: ' || ld_end_date);
1982: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1983: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1984: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1985:
1986: load_gre (ln_business_group_id,
1979: ,p_asg_set_id => ln_asg_set_id);
1980:
1981: hr_utility.trace('ld_end_date: ' || ld_end_date);
1982: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1983: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1984: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1985:
1986: load_gre (ln_business_group_id,
1987: ln_legal_employer_id,
1980:
1981: hr_utility.trace('ld_end_date: ' || ld_end_date);
1982: hr_utility.trace('ln_business_group_id: ' || ln_business_group_id);
1983: hr_utility.trace('ln_legal_employer_id: ' || ln_legal_employer_id);
1984: hr_utility.trace('ln_asg_set_id: ' || ln_asg_set_id);
1985:
1986: load_gre (ln_business_group_id,
1987: ln_legal_employer_id,
1988: ld_end_date);
1987: ln_legal_employer_id,
1988: ld_end_date);
1989: END IF;
1990:
1991: hr_utility.set_location(gv_package || lv_procedure_name, 20);
1992: OPEN c_asg_action_details;
1993: FETCH c_asg_action_details INTO ln_assignment_id,ln_person_id,ln_tax_unit_id;
1994: CLOSE c_asg_action_details;
1995:
1992: OPEN c_asg_action_details;
1993: FETCH c_asg_action_details INTO ln_assignment_id,ln_person_id,ln_tax_unit_id;
1994: CLOSE c_asg_action_details;
1995:
1996: hr_utility.set_location(gv_package || lv_procedure_name, 30);
1997:
1998: OPEN c_get_eff_date;
1999: FETCH c_get_eff_date INTO ld_act_term_date,ld_effective_date, ld_arch_end_date;
2000: CLOSE c_get_eff_date;
1998: OPEN c_get_eff_date;
1999: FETCH c_get_eff_date INTO ld_act_term_date,ld_effective_date, ld_arch_end_date;
2000: CLOSE c_get_eff_date;
2001:
2002: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2003: hr_utility.trace('ld_effective_date: '||ld_effective_date);
2004: hr_utility.trace('ld_arch_end_date: '||ld_arch_end_date);
2005:
2006: IF ld_arch_end_date <> fnd_date.canonical_to_date(g_fiscal_year ||
1999: FETCH c_get_eff_date INTO ld_act_term_date,ld_effective_date, ld_arch_end_date;
2000: CLOSE c_get_eff_date;
2001:
2002: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2003: hr_utility.trace('ld_effective_date: '||ld_effective_date);
2004: hr_utility.trace('ld_arch_end_date: '||ld_arch_end_date);
2005:
2006: IF ld_arch_end_date <> fnd_date.canonical_to_date(g_fiscal_year ||
2007: '/12/31') THEN
2000: CLOSE c_get_eff_date;
2001:
2002: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2003: hr_utility.trace('ld_effective_date: '||ld_effective_date);
2004: hr_utility.trace('ld_arch_end_date: '||ld_arch_end_date);
2005:
2006: IF ld_arch_end_date <> fnd_date.canonical_to_date(g_fiscal_year ||
2007: '/12/31') THEN
2008:
2005:
2006: IF ld_arch_end_date <> fnd_date.canonical_to_date(g_fiscal_year ||
2007: '/12/31') THEN
2008:
2009: hr_utility.set_location(gv_package || lv_procedure_name, 40);
2010: lb_is_term_ee := TRUE;
2011: hr_utility.trace('lb_is_term_ee= TRUE');
2012: ELSIF (ld_act_term_date IS NOT NULL) AND (ld_act_term_date = fnd_date.canonical_to_date
2013: (g_fiscal_year ||'/12/31')) THEN
2007: '/12/31') THEN
2008:
2009: hr_utility.set_location(gv_package || lv_procedure_name, 40);
2010: lb_is_term_ee := TRUE;
2011: hr_utility.trace('lb_is_term_ee= TRUE');
2012: ELSIF (ld_act_term_date IS NOT NULL) AND (ld_act_term_date = fnd_date.canonical_to_date
2013: (g_fiscal_year ||'/12/31')) THEN
2014: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2015: hr_utility.set_location(gv_package || lv_procedure_name, 45);
2010: lb_is_term_ee := TRUE;
2011: hr_utility.trace('lb_is_term_ee= TRUE');
2012: ELSIF (ld_act_term_date IS NOT NULL) AND (ld_act_term_date = fnd_date.canonical_to_date
2013: (g_fiscal_year ||'/12/31')) THEN
2014: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2015: hr_utility.set_location(gv_package || lv_procedure_name, 45);
2016: hr_utility.trace('Emp is terminated on last pay period');
2017: lb_is_term_ee := TRUE;
2018: hr_utility.trace('lb_is_term_ee= TRUE');
2011: hr_utility.trace('lb_is_term_ee= TRUE');
2012: ELSIF (ld_act_term_date IS NOT NULL) AND (ld_act_term_date = fnd_date.canonical_to_date
2013: (g_fiscal_year ||'/12/31')) THEN
2014: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2015: hr_utility.set_location(gv_package || lv_procedure_name, 45);
2016: hr_utility.trace('Emp is terminated on last pay period');
2017: lb_is_term_ee := TRUE;
2018: hr_utility.trace('lb_is_term_ee= TRUE');
2019: ELSE
2012: ELSIF (ld_act_term_date IS NOT NULL) AND (ld_act_term_date = fnd_date.canonical_to_date
2013: (g_fiscal_year ||'/12/31')) THEN
2014: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2015: hr_utility.set_location(gv_package || lv_procedure_name, 45);
2016: hr_utility.trace('Emp is terminated on last pay period');
2017: lb_is_term_ee := TRUE;
2018: hr_utility.trace('lb_is_term_ee= TRUE');
2019: ELSE
2020: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2014: hr_utility.trace('ld_act_term_date: '||ld_act_term_date);
2015: hr_utility.set_location(gv_package || lv_procedure_name, 45);
2016: hr_utility.trace('Emp is terminated on last pay period');
2017: lb_is_term_ee := TRUE;
2018: hr_utility.trace('lb_is_term_ee= TRUE');
2019: ELSE
2020: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2021: lb_is_term_ee := FALSE;
2022: hr_utility.trace('lb_is_term_ee= FALSE');
2016: hr_utility.trace('Emp is terminated on last pay period');
2017: lb_is_term_ee := TRUE;
2018: hr_utility.trace('lb_is_term_ee= TRUE');
2019: ELSE
2020: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2021: lb_is_term_ee := FALSE;
2022: hr_utility.trace('lb_is_term_ee= FALSE');
2023: END IF;
2024:
2018: hr_utility.trace('lb_is_term_ee= TRUE');
2019: ELSE
2020: hr_utility.set_location(gv_package || lv_procedure_name, 50);
2021: lb_is_term_ee := FALSE;
2022: hr_utility.trace('lb_is_term_ee= FALSE');
2023: END IF;
2024:
2025: ln_step := 2;
2026: hr_utility.trace('Effective Date of archiver for the person: ' ||
2022: hr_utility.trace('lb_is_term_ee= FALSE');
2023: END IF;
2024:
2025: ln_step := 2;
2026: hr_utility.trace('Effective Date of archiver for the person: ' ||
2027: fnd_date.date_to_canonical(p_effective_date));
2028:
2029:
2030: ln_step := 3;
2029:
2030: ln_step := 3;
2031: ld_start_date := fnd_date.canonical_to_date(g_fiscal_year || '/01/01');
2032:
2033: hr_utility.set_location(gv_package || lv_procedure_name, 60);
2034:
2035: -- Fetch the YTD Assignment Action ID.
2036: --
2037: ln_ytd_aa_id := NULL;
2042: FETCH c_get_ytd_aaid INTO ln_ytd_aa_id;
2043:
2044: IF c_get_ytd_aaid%NOTFOUND THEN
2045:
2046: hr_utility.trace('No assignment action found for the person''s ' ||
2047: 'period of service within the GRE!');
2048: hr_utility.raise_error;
2049:
2050: ELSE
2044: IF c_get_ytd_aaid%NOTFOUND THEN
2045:
2046: hr_utility.trace('No assignment action found for the person''s ' ||
2047: 'period of service within the GRE!');
2048: hr_utility.raise_error;
2049:
2050: ELSE
2051:
2052: hr_utility.trace('YTD Assactid: '|| ln_ytd_aa_id);
2048: hr_utility.raise_error;
2049:
2050: ELSE
2051:
2052: hr_utility.trace('YTD Assactid: '|| ln_ytd_aa_id);
2053:
2054: END IF;
2055:
2056: CLOSE c_get_ytd_aaid;
2057:
2058: pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2059:
2060: ln_step := 5;
2061: hr_utility.set_location(gv_package || lv_procedure_name, 80);
2062:
2063:
2064: OPEN c_chk_last_archiver(ln_assignment_id,
2065: ld_start_date,
2068: FETCH c_chk_last_archiver INTO gn_prev_asg_act_id
2069: ,lv_prev_arch_exists;
2070: CLOSE c_chk_last_archiver;
2071:
2072: hr_utility.trace('ln_person_id: '|| ln_person_id);
2073: hr_utility.trace('ln_assignment_id: '|| ln_assignment_id);
2074: hr_utility.trace('gn_prev_asg_act_id: '|| gn_prev_asg_act_id);
2075: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2076:
2069: ,lv_prev_arch_exists;
2070: CLOSE c_chk_last_archiver;
2071:
2072: hr_utility.trace('ln_person_id: '|| ln_person_id);
2073: hr_utility.trace('ln_assignment_id: '|| ln_assignment_id);
2074: hr_utility.trace('gn_prev_asg_act_id: '|| gn_prev_asg_act_id);
2075: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2076:
2077: IF ( lv_prev_arch_exists = 'Y' OR lb_is_term_ee ) THEN
2070: CLOSE c_chk_last_archiver;
2071:
2072: hr_utility.trace('ln_person_id: '|| ln_person_id);
2073: hr_utility.trace('ln_assignment_id: '|| ln_assignment_id);
2074: hr_utility.trace('gn_prev_asg_act_id: '|| gn_prev_asg_act_id);
2075: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2076:
2077: IF ( lv_prev_arch_exists = 'Y' OR lb_is_term_ee ) THEN
2078:
2071:
2072: hr_utility.trace('ln_person_id: '|| ln_person_id);
2073: hr_utility.trace('ln_assignment_id: '|| ln_assignment_id);
2074: hr_utility.trace('gn_prev_asg_act_id: '|| gn_prev_asg_act_id);
2075: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2076:
2077: IF ( lv_prev_arch_exists = 'Y' OR lb_is_term_ee ) THEN
2078:
2079: hr_utility.set_location(gv_package || lv_procedure_name, 90);
2075: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2076:
2077: IF ( lv_prev_arch_exists = 'Y' OR lb_is_term_ee ) THEN
2078:
2079: hr_utility.set_location(gv_package || lv_procedure_name, 90);
2080:
2081: ld_PTU_date := NULL;
2082:
2083:
2093: OPEN c_get_max_arch_end_date(ln_person_id);
2094: FETCH c_get_max_arch_end_date INTO ld_max_arch_end_date;
2095: CLOSE c_get_max_arch_end_date;
2096:
2097: hr_utility.trace('ld_PTU_date: = '|| ld_PTU_date);
2098: hr_utility.trace('ld_max_arch_end_date: ='|| ld_max_arch_end_date);
2099:
2100: IF ld_max_arch_end_date IS NULL THEN
2101: ld_max_arch_end_date := fnd_date.canonical_to_date(g_fiscal_year || '/12/31');
2094: FETCH c_get_max_arch_end_date INTO ld_max_arch_end_date;
2095: CLOSE c_get_max_arch_end_date;
2096:
2097: hr_utility.trace('ld_PTU_date: = '|| ld_PTU_date);
2098: hr_utility.trace('ld_max_arch_end_date: ='|| ld_max_arch_end_date);
2099:
2100: IF ld_max_arch_end_date IS NULL THEN
2101: ld_max_arch_end_date := fnd_date.canonical_to_date(g_fiscal_year || '/12/31');
2102: END IF;
2100: IF ld_max_arch_end_date IS NULL THEN
2101: ld_max_arch_end_date := fnd_date.canonical_to_date(g_fiscal_year || '/12/31');
2102: END IF;
2103:
2104: hr_utility.trace('ld_max_arch_end_date: '||ld_max_arch_end_date);
2105:
2106: IF ld_max_arch_end_date = ld_arch_end_date THEN
2107: hr_utility.trace('lb_latest_assignment is set as TRUE');
2108: lb_latest_assignment := TRUE; --Bug 11738478
2103:
2104: hr_utility.trace('ld_max_arch_end_date: '||ld_max_arch_end_date);
2105:
2106: IF ld_max_arch_end_date = ld_arch_end_date THEN
2107: hr_utility.trace('lb_latest_assignment is set as TRUE');
2108: lb_latest_assignment := TRUE; --Bug 11738478
2109: END IF;
2110:
2111: IF ld_PTU_date IS NOT NULL
2118: IF ld_max_arch_end_date = ld_arch_end_date THEN
2119: ld_arch_end_date := ADD_MONTHS(ld_PTU_date, 1) - 1;
2120: END IF;
2121:
2122: hr_utility.trace('PTU ld_arch_start_date: '||ld_PTU_date);
2123: hr_utility.trace('PTU ld_arch_end_date: '||ld_arch_end_date);
2124: hr_utility.trace('ld_PTU_date,YYYY: '||to_char(ld_PTU_date,'YYYY'));
2125: hr_utility.trace('ld_act_term_date: '||to_char(ld_act_term_date,'YYYY'));
2126: /*For seniority computation 8402464*/
2119: ld_arch_end_date := ADD_MONTHS(ld_PTU_date, 1) - 1;
2120: END IF;
2121:
2122: hr_utility.trace('PTU ld_arch_start_date: '||ld_PTU_date);
2123: hr_utility.trace('PTU ld_arch_end_date: '||ld_arch_end_date);
2124: hr_utility.trace('ld_PTU_date,YYYY: '||to_char(ld_PTU_date,'YYYY'));
2125: hr_utility.trace('ld_act_term_date: '||to_char(ld_act_term_date,'YYYY'));
2126: /*For seniority computation 8402464*/
2127: IF lv_prev_arch_exists = 'N'
2120: END IF;
2121:
2122: hr_utility.trace('PTU ld_arch_start_date: '||ld_PTU_date);
2123: hr_utility.trace('PTU ld_arch_end_date: '||ld_arch_end_date);
2124: hr_utility.trace('ld_PTU_date,YYYY: '||to_char(ld_PTU_date,'YYYY'));
2125: hr_utility.trace('ld_act_term_date: '||to_char(ld_act_term_date,'YYYY'));
2126: /*For seniority computation 8402464*/
2127: IF lv_prev_arch_exists = 'N'
2128: AND (TO_CHAR (ld_PTU_date,'YYYY') = TO_CHAR(ld_act_term_date,'YYYY'))
2121:
2122: hr_utility.trace('PTU ld_arch_start_date: '||ld_PTU_date);
2123: hr_utility.trace('PTU ld_arch_end_date: '||ld_arch_end_date);
2124: hr_utility.trace('ld_PTU_date,YYYY: '||to_char(ld_PTU_date,'YYYY'));
2125: hr_utility.trace('ld_act_term_date: '||to_char(ld_act_term_date,'YYYY'));
2126: /*For seniority computation 8402464*/
2127: IF lv_prev_arch_exists = 'N'
2128: AND (TO_CHAR (ld_PTU_date,'YYYY') = TO_CHAR(ld_act_term_date,'YYYY'))
2129: THEN
2135:
2136: END IF;
2137:
2138: ln_step := 6;
2139: hr_utility.set_location(gv_package || lv_procedure_name, 100);
2140:
2141: /*OPEN c_get_subsidy(ln_ytd_aa_id);
2142: FETCH c_get_subsidy INTO ln_cred_subsidy,
2143: ln_non_cred_subsidy;
2155: FETCH c_get_nonsubsidy INTO ln_non_cred_subsidy;
2156: CLOSE c_get_nonsubsidy;
2157:
2158: ln_step := 7;
2159: hr_utility.set_location(gv_package || lv_procedure_name, 105);
2160: -- Initialise the variables
2161: --
2162: ln_total_subsidy := ln_cred_subsidy + ln_non_cred_subsidy;
2163: ln_total_cred_subsidy := ln_cred_subsidy;
2162: ln_total_subsidy := ln_cred_subsidy + ln_non_cred_subsidy;
2163: ln_total_cred_subsidy := ln_cred_subsidy;
2164: i := 0;
2165: --
2166: hr_utility.set_location(gv_package || lv_procedure_name, 110);
2167:
2168: -- EE details need to be fetched as on the Actual
2169: -- Termination Date, or p_effective_date, in that order.
2170:
2204:
2205: ln_curr_tax_unit_id := ln_tax_unit_id;
2206:
2207: ln_step := 4;
2208: hr_utility.set_location(gv_package || lv_procedure_name, 70);
2209: hr_utility.trace('ld_start_date: ' || ld_start_date);
2210: hr_utility.trace('p_effective_date: ' || p_effective_date);
2211: hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212: hr_utility.trace('ln_person_id: ' || ln_person_id);
2205: ln_curr_tax_unit_id := ln_tax_unit_id;
2206:
2207: ln_step := 4;
2208: hr_utility.set_location(gv_package || lv_procedure_name, 70);
2209: hr_utility.trace('ld_start_date: ' || ld_start_date);
2210: hr_utility.trace('p_effective_date: ' || p_effective_date);
2211: hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212: hr_utility.trace('ln_person_id: ' || ln_person_id);
2213: hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
2206:
2207: ln_step := 4;
2208: hr_utility.set_location(gv_package || lv_procedure_name, 70);
2209: hr_utility.trace('ld_start_date: ' || ld_start_date);
2210: hr_utility.trace('p_effective_date: ' || p_effective_date);
2211: hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212: hr_utility.trace('ln_person_id: ' || ln_person_id);
2213: hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
2214: hr_utility.trace('ln_row_count: ' || ln_row_count);
2207: ln_step := 4;
2208: hr_utility.set_location(gv_package || lv_procedure_name, 70);
2209: hr_utility.trace('ld_start_date: ' || ld_start_date);
2210: hr_utility.trace('p_effective_date: ' || p_effective_date);
2211: hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212: hr_utility.trace('ln_person_id: ' || ln_person_id);
2213: hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
2214: hr_utility.trace('ln_row_count: ' || ln_row_count);
2215:
2208: hr_utility.set_location(gv_package || lv_procedure_name, 70);
2209: hr_utility.trace('ld_start_date: ' || ld_start_date);
2210: hr_utility.trace('p_effective_date: ' || p_effective_date);
2211: hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212: hr_utility.trace('ln_person_id: ' || ln_person_id);
2213: hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
2214: hr_utility.trace('ln_row_count: ' || ln_row_count);
2215:
2216: /*FOR c_rec IN c_get_other_er_info(ln_person_id,
2209: hr_utility.trace('ld_start_date: ' || ld_start_date);
2210: hr_utility.trace('p_effective_date: ' || p_effective_date);
2211: hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212: hr_utility.trace('ln_person_id: ' || ln_person_id);
2213: hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
2214: hr_utility.trace('ln_row_count: ' || ln_row_count);
2215:
2216: /*FOR c_rec IN c_get_other_er_info(ln_person_id,
2217: p_effective_date)
2210: hr_utility.trace('p_effective_date: ' || p_effective_date);
2211: hr_utility.trace('ln_tax_unit_id: ' || ln_tax_unit_id);
2212: hr_utility.trace('ln_person_id: ' || ln_person_id);
2213: hr_utility.trace('ln_assignment_id: ' || ln_assignment_id);
2214: hr_utility.trace('ln_row_count: ' || ln_row_count);
2215:
2216: /*FOR c_rec IN c_get_other_er_info(ln_person_id,
2217: p_effective_date)
2218: LOOP
2230:
2231: -- RFC Validation to be performed
2232: -- Hyphens are stripped and RFC is then validated.
2233: --
2234: hr_utility.set_location(gv_package || lv_procedure_name, 120);
2235: lv_plain_rfc :=
2236: TRANSLATE(c_rec.RFC, 'A !"$%^&*()-_+=`[]{};''#:@~<>?','A');
2237:
2238: l_valid_rfc := hr_ni_chk_pkg.chk_nat_id_format(lv_plain_rfc,
2237:
2238: l_valid_rfc := hr_ni_chk_pkg.chk_nat_id_format(lv_plain_rfc,
2239: 'AAADDDDDDXXX');
2240: IF l_valid_rfc = '0' THEN
2241: hr_utility.set_location(gv_package || lv_procedure_name, 130);
2242: RAISE INVALID_RFC;
2243: ELSE
2244: PEI(i).RFC := l_valid_rfc;
2245: END IF;
2261: OPEN c_get_hire_date(ln_person_id,ld_effective_date); --Re-hire bug
2262: FETCH c_get_hire_date INTO ld_hire_date;
2263: CLOSE c_get_hire_date;
2264:
2265: hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2266: hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2267: hr_utility.trace('ld_hire_date: '||ld_hire_date);
2268:
2269: IF lv_arch_for_ptu_only = 'Y' THEN
2262: FETCH c_get_hire_date INTO ld_hire_date;
2263: CLOSE c_get_hire_date;
2264:
2265: hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2266: hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2267: hr_utility.trace('ld_hire_date: '||ld_hire_date);
2268:
2269: IF lv_arch_for_ptu_only = 'Y' THEN
2270: ld_arch_start_date := ld_PTU_date;
2263: CLOSE c_get_hire_date;
2264:
2265: hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2266: hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2267: hr_utility.trace('ld_hire_date: '||ld_hire_date);
2268:
2269: IF lv_arch_for_ptu_only = 'Y' THEN
2270: ld_arch_start_date := ld_PTU_date;
2271: END IF ;
2268:
2269: IF lv_arch_for_ptu_only = 'Y' THEN
2270: ld_arch_start_date := ld_PTU_date;
2271: END IF ;
2272: hr_utility.trace('After PTU check ld_arch_start_date: '||ld_arch_start_date);
2273: ld_arch_start_date := GREATEST( ld_arch_start_date, ld_hire_date );
2274:
2275: IF TRUNC( ld_arch_end_date, 'Y' ) = TRUNC( p_effective_date, 'Y' ) THEN
2276:
2281: ld_arch_end_date := p_effective_date;
2282:
2283: END IF;
2284:
2285: hr_utility.trace('AFTER ld_arch_start_date: '||ld_arch_start_date);
2286: hr_utility.trace('AFTER ld_arch_end_date: '||ld_arch_end_date);*/
2287:
2288: IF ln_curr_tax_unit_id <> ln_prev_tax_unit_id THEN
2289: OPEN c_get_ytd_aaid(ld_start_date,
2282:
2283: END IF;
2284:
2285: hr_utility.trace('AFTER ld_arch_start_date: '||ld_arch_start_date);
2286: hr_utility.trace('AFTER ld_arch_end_date: '||ld_arch_end_date);*/
2287:
2288: IF ln_curr_tax_unit_id <> ln_prev_tax_unit_id THEN
2289: OPEN c_get_ytd_aaid(ld_start_date,
2290: p_effective_date,
2290: p_effective_date,
2291: ln_tax_unit_id);
2292: FETCH c_get_ytd_aaid INTO ln_ytd_aa_id;
2293: IF c_get_ytd_aaid%NOTFOUND THEN
2294: hr_utility.trace('No assignment action found for the person''s ' ||
2295: 'period of service within the GRE!');
2296: hr_utility.raise_error;
2297:
2298: ELSE
2292: FETCH c_get_ytd_aaid INTO ln_ytd_aa_id;
2293: IF c_get_ytd_aaid%NOTFOUND THEN
2294: hr_utility.trace('No assignment action found for the person''s ' ||
2295: 'period of service within the GRE!');
2296: hr_utility.raise_error;
2297:
2298: ELSE
2299: hr_utility.trace('YTD Assactid: '|| ln_ytd_aa_id);
2300:
2295: 'period of service within the GRE!');
2296: hr_utility.raise_error;
2297:
2298: ELSE
2299: hr_utility.trace('YTD Assactid: '|| ln_ytd_aa_id);
2300:
2301: END IF;
2302: CLOSE c_get_ytd_aaid;
2303:
2300:
2301: END IF;
2302: CLOSE c_get_ytd_aaid;
2303:
2304: hr_utility.trace('--Before calling populate_balances--');
2305: hr_utility.trace('--TAX_UNIT_ID--'||ln_tax_unit_id);
2306: hr_utility.trace('--YTD ASSACT ID--'||ln_ytd_aa_id);
2307: pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2308:
2301: END IF;
2302: CLOSE c_get_ytd_aaid;
2303:
2304: hr_utility.trace('--Before calling populate_balances--');
2305: hr_utility.trace('--TAX_UNIT_ID--'||ln_tax_unit_id);
2306: hr_utility.trace('--YTD ASSACT ID--'||ln_ytd_aa_id);
2307: pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2308:
2309: populate_balances(p_archive_action_id => p_archive_action_id
2302: CLOSE c_get_ytd_aaid;
2303:
2304: hr_utility.trace('--Before calling populate_balances--');
2305: hr_utility.trace('--TAX_UNIT_ID--'||ln_tax_unit_id);
2306: hr_utility.trace('--YTD ASSACT ID--'||ln_ytd_aa_id);
2307: pay_balance_pkg.set_context('TAX_UNIT_ID', ln_tax_unit_id);
2308:
2309: populate_balances(p_archive_action_id => p_archive_action_id
2310: ,p_ytd_action_id => ln_ytd_aa_id
2336:
2337: -- RFC Validation to be performed
2338: -- Hyphens are stripped and RFC is then validated.
2339: --
2340: hr_utility.set_location(gv_package || lv_procedure_name, 120);
2341: lv_plain_rfc :=
2342: TRANSLATE(c_rec.RFC, 'A !"$%^&*()-_+=`[]{};''#:@~<>?','A');
2343:
2344: l_valid_rfc := hr_ni_chk_pkg.chk_nat_id_format(lv_plain_rfc,
2343:
2344: l_valid_rfc := hr_ni_chk_pkg.chk_nat_id_format(lv_plain_rfc,
2345: 'AAADDDDDDXXX');
2346: IF l_valid_rfc = '0' THEN
2347: hr_utility.set_location(gv_package || lv_procedure_name, 130);
2348: RAISE INVALID_RFC;
2349: ELSE
2350: PEI(i).RFC := l_valid_rfc;
2351: END IF;
2367: OPEN c_get_amends_amount;
2368: FETCH c_get_amends_amount INTO lv_amends_amount;
2369: CLOSE c_get_amends_amount;
2370:
2371: hr_utility.trace('lv_amends_amount: '||to_char(lv_amends_amount));
2372:
2373: hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2374: hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2375: hr_utility.trace('ld_hire_date: '||ld_hire_date);
2369: CLOSE c_get_amends_amount;
2370:
2371: hr_utility.trace('lv_amends_amount: '||to_char(lv_amends_amount));
2372:
2373: hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2374: hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2375: hr_utility.trace('ld_hire_date: '||ld_hire_date);
2376:
2377: IF lv_arch_for_ptu_only = 'Y' THEN
2370:
2371: hr_utility.trace('lv_amends_amount: '||to_char(lv_amends_amount));
2372:
2373: hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2374: hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2375: hr_utility.trace('ld_hire_date: '||ld_hire_date);
2376:
2377: IF lv_arch_for_ptu_only = 'Y' THEN
2378: ld_arch_start_date := ld_PTU_date;
2371: hr_utility.trace('lv_amends_amount: '||to_char(lv_amends_amount));
2372:
2373: hr_utility.trace('B4 ld_arch_start_date: '||ld_arch_start_date);
2374: hr_utility.trace('B4 ld_arch_end_date: '||ld_arch_end_date);
2375: hr_utility.trace('ld_hire_date: '||ld_hire_date);
2376:
2377: IF lv_arch_for_ptu_only = 'Y' THEN
2378: ld_arch_start_date := ld_PTU_date;
2379: END IF ;
2376:
2377: IF lv_arch_for_ptu_only = 'Y' THEN
2378: ld_arch_start_date := ld_PTU_date;
2379: END IF ;
2380: hr_utility.trace('After PTU check ld_arch_start_date: '||ld_arch_start_date);
2381: ld_arch_start_date := GREATEST( ld_arch_start_date, ld_hire_date );
2382:
2383: IF TRUNC( ld_arch_end_date, 'Y' ) = TRUNC( p_effective_date, 'Y' ) THEN
2384:
2389: ld_arch_end_date := p_effective_date;
2390:
2391: END IF;
2392:
2393: hr_utility.trace('AFTER ld_arch_start_date: '||ld_arch_start_date);
2394: hr_utility.trace('AFTER ld_arch_end_date: '||ld_arch_end_date);
2395:
2396: ln_index := pai_tab.count;
2397:
2390:
2391: END IF;
2392:
2393: hr_utility.trace('AFTER ld_arch_start_date: '||ld_arch_start_date);
2394: hr_utility.trace('AFTER ld_arch_end_date: '||ld_arch_end_date);
2395:
2396: ln_index := pai_tab.count;
2397:
2398: pai_tab(ln_index).action_info_category := 'MX YREND EE DETAILS';
2408: fnd_date.date_to_canonical(ld_arch_start_date);
2409: pai_tab(ln_index).act_info7 :=
2410: fnd_date.date_to_canonical(ld_arch_end_date);
2411:
2412: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2413: hr_utility.trace('lv_arch_for_ptu_only: '|| lv_arch_for_ptu_only);
2414: hr_utility.trace('lv_amends_amount: '|| lv_amends_amount);
2415:
2416: IF lb_is_term_ee AND lv_prev_arch_exists = 'N' AND
2409: pai_tab(ln_index).act_info7 :=
2410: fnd_date.date_to_canonical(ld_arch_end_date);
2411:
2412: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2413: hr_utility.trace('lv_arch_for_ptu_only: '|| lv_arch_for_ptu_only);
2414: hr_utility.trace('lv_amends_amount: '|| lv_amends_amount);
2415:
2416: IF lb_is_term_ee AND lv_prev_arch_exists = 'N' AND
2417: lv_arch_for_ptu_only = 'N' AND lb_latest_assignment AND lv_amends_amount > 0 THEN -- Bug 11738478, Bug 13093752
2410: fnd_date.date_to_canonical(ld_arch_end_date);
2411:
2412: hr_utility.trace('lv_prev_arch_exists: '|| lv_prev_arch_exists);
2413: hr_utility.trace('lv_arch_for_ptu_only: '|| lv_arch_for_ptu_only);
2414: hr_utility.trace('lv_amends_amount: '|| lv_amends_amount);
2415:
2416: IF lb_is_term_ee AND lv_prev_arch_exists = 'N' AND
2417: lv_arch_for_ptu_only = 'N' AND lb_latest_assignment AND lv_amends_amount > 0 THEN -- Bug 11738478, Bug 13093752
2418:
2415:
2416: IF lb_is_term_ee AND lv_prev_arch_exists = 'N' AND
2417: lv_arch_for_ptu_only = 'N' AND lb_latest_assignment AND lv_amends_amount > 0 THEN -- Bug 11738478, Bug 13093752
2418:
2419: hr_utility.trace('Setting the seniority');
2420: pai_tab(ln_index).act_info8 := ln_seniority;
2421:
2422: ELSE
2423:
2430: pai_tab(ln_index).act_info9 := lv_ann_adj;
2431: pai_tab(ln_index).act_info10 := lv_is_union_worker;
2432: pai_tab(ln_index).act_info11 := lv_economic_zone;
2433:
2434: hr_utility.set_location(gv_package || lv_procedure_name, 140);
2435: --
2436: IF ln_total_subsidy > 0 THEN
2437:
2438: pai_tab(ln_index).act_info12 := ln_tax_subsidy_prop;
2446: -- and a different subsidy proportion has been used.
2447: --
2448: IF ln_total_subsidy > 0 AND lv_ann_adj = 'Y' THEN
2449:
2450: hr_utility.set_location(gv_package || lv_procedure_name, 150);
2451: pai_tab(ln_index).act_info13 :=
2452: ROUND(ln_total_cred_subsidy / ln_total_subsidy, 4);
2453:
2454: IF ln_tax_subsidy_prop <> pai_tab(ln_index).act_info13 THEN
2452: ROUND(ln_total_cred_subsidy / ln_total_subsidy, 4);
2453:
2454: IF ln_tax_subsidy_prop <> pai_tab(ln_index).act_info13 THEN
2455:
2456: hr_utility.set_location(gv_package || lv_procedure_name, 160);
2457: pai_tab(ln_index).act_info13 := NULL;
2458:
2459: END IF;
2460:
2459: END IF;
2460:
2461: ELSE
2462:
2463: hr_utility.set_location(gv_package || lv_procedure_name, 170);
2464: pai_tab(ln_index).act_info13 := NULL;
2465:
2466: END IF;
2467:
2464: pai_tab(ln_index).act_info13 := NULL;
2465:
2466: END IF;
2467:
2468: hr_utility.set_location(gv_package || lv_procedure_name, 180);
2469: --
2470: IF PEI.EXISTS(1) THEN
2471: pai_tab(ln_index).act_info14 := PEI(1).RFC;
2472: pai_tab(ln_index).act_info24 := PEI(1).total_earnings;
2588:
2589: END IF;
2590:
2591: ln_step := 8;
2592: hr_utility.set_location(gv_package || lv_procedure_name, 190);
2593: pay_emp_action_arch.insert_rows_thro_api_process(
2594: p_action_context_id => p_archive_action_id
2595: ,p_action_context_type => 'AAP'
2596: ,p_assignment_id => ln_assignment_id
2600: );
2601: pai_tab.delete;
2602:
2603: ln_step := 9;
2604: hr_utility.set_location(gv_package || lv_procedure_name, 200);
2605: OPEN c_check_pay_action(g_payroll_action_id);
2606: FETCH c_check_pay_action INTO ln_pay_action_count;
2607: CLOSE c_check_pay_action;
2608:
2608:
2609: ln_step := 10;
2610: IF ln_pay_action_count = 0 THEN
2611:
2612: hr_utility.set_location(gv_package || lv_procedure_name, 210);
2613: IF ln_row_count = 1 THEN
2614:
2615: ln_step := 11;
2616: ln_index := pai_tab.count;
2625: pai_tab(ln_index).act_info4 := replace(pay_mx_rules.strip_spl_chars (g_ER_legal_rep_name),'/','N');
2626: pai_tab(ln_index).act_info5 := g_ER_legal_rep_RFC;
2627: pai_tab(ln_index).act_info6 := g_ER_legal_rep_CURP;
2628:
2629: hr_utility.set_location(gv_package || lv_procedure_name, 220);
2630:
2631: OPEN c_get_gen_hier_details(ln_business_group_id,
2632: p_effective_date);
2633: LOOP
2632: p_effective_date);
2633: LOOP
2634:
2635: ln_step := 12;
2636: hr_utility.set_location(gv_package || lv_procedure_name, 230);
2637:
2638: FETCH c_get_gen_hier_details INTO ln_legal_er_id,
2639: ln_gre_id;
2640: EXIT WHEN c_get_gen_hier_details%NOTFOUND;
2640: EXIT WHEN c_get_gen_hier_details%NOTFOUND;
2641:
2642: ln_index := pai_tab.count;
2643:
2644: hr_utility.set_location(gv_package || lv_procedure_name, 240);
2645:
2646: pai_tab(ln_index).action_info_category := 'MX GENERIC ' ||
2647: 'HIERARCHY DETAILS';
2648: pai_tab(ln_index).jurisdiction_code := NULL;
2652:
2653: END LOOP;
2654:
2655: ln_step := 13;
2656: hr_utility.set_location(gv_package || lv_procedure_name, 250);
2657:
2658: pay_emp_action_arch.insert_rows_thro_api_process(
2659: p_action_context_id => g_payroll_action_id
2660: ,p_action_context_type => 'PA'
2662: ,p_tax_unit_id => NULL
2663: ,p_curr_pymt_eff_date => p_effective_date
2664: ,p_tab_rec_data => pai_tab);
2665:
2666: hr_utility.set_location(gv_package || lv_procedure_name, 260);
2667: pai_tab.delete;
2668:
2669: END IF;
2670:
2672: end if;
2673:
2674:
2675: ln_step := 14;
2676: hr_utility.set_location(gv_package || lv_procedure_name, 270);
2677:
2678: end loop;
2679: Close c_emp_details;
2680: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
2676: hr_utility.set_location(gv_package || lv_procedure_name, 270);
2677:
2678: end loop;
2679: Close c_emp_details;
2680: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
2681:
2682: EXCEPTION
2683: WHEN INVALID_RFC THEN
2684: hr_utility.set_message(800, 'HR_MX_INVALID_ER_RFC');
2680: hr_utility.trace('Leaving ' || gv_package || lv_procedure_name);
2681:
2682: EXCEPTION
2683: WHEN INVALID_RFC THEN
2684: hr_utility.set_message(800, 'HR_MX_INVALID_ER_RFC');
2685: hr_utility.raise_error;
2686:
2687: WHEN OTHERS THEN
2688: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
2681:
2682: EXCEPTION
2683: WHEN INVALID_RFC THEN
2684: hr_utility.set_message(800, 'HR_MX_INVALID_ER_RFC');
2685: hr_utility.raise_error;
2686:
2687: WHEN OTHERS THEN
2688: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
2689: gv_package || lv_procedure_name;
2687: WHEN OTHERS THEN
2688: lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
2689: gv_package || lv_procedure_name;
2690:
2691: hr_utility.trace(lv_error_message || '-' || SQLERRM);
2692:
2693: lv_error_message :=
2694: pay_emp_action_arch.set_error_message(lv_error_message);
2695:
2692:
2693: lv_error_message :=
2694: pay_emp_action_arch.set_error_message(lv_error_message);
2695:
2696: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2697: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2698: hr_utility.raise_error;
2699:
2700: END archive_code;
2693: lv_error_message :=
2694: pay_emp_action_arch.set_error_message(lv_error_message);
2695:
2696: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2697: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2698: hr_utility.raise_error;
2699:
2700: END archive_code;
2701:
2694: pay_emp_action_arch.set_error_message(lv_error_message);
2695:
2696: hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2697: hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2698: hr_utility.raise_error;
2699:
2700: END archive_code;
2701:
2702: BEGIN
2699:
2700: END archive_code;
2701:
2702: BEGIN
2703: --hr_utility.trace_on (NULL, 'MX_IDC');
2704: gv_package := 'pay_mx_yrend_arch';
2705: END pay_mx_yrend_arch;