[Home] [Help]
408:
409: l_defined_balance_id pay_defined_balances.defined_balance_id%type;
410:
411: BEGIN
412: --hr_utility.trace('p_db_item_name is '||p_db_item_name);
413:
414: open csr_defined_balance;
415: fetch csr_defined_balance into l_defined_balance_id;
416: if csr_defined_balance%notfound then
414: open csr_defined_balance;
415: fetch csr_defined_balance into l_defined_balance_id;
416: if csr_defined_balance%notfound then
417: close csr_defined_balance;
418: raise hr_utility.hr_error;
419: else
420: close csr_defined_balance;
421: end if;
422:
419: else
420: close csr_defined_balance;
421: end if;
422:
423: --hr_utility.trace('l_defined_balance_id is '||to_char(l_defined_balance_id));
424: return (l_defined_balance_id);
425:
426: END bal_db_item;
427:
472: ln_year NUMBER := 0;
473: ln_creation_date DATE;
474:
475: BEGIN
476: hr_utility.trace('Entered get_payroll_action_info');
477: open c_payroll_action_info(p_payroll_action_id);
478: hr_utility.trace('Opened c_payroll_action_info ');
479:
480: fetch c_payroll_action_info into ln_tax_unit_id,
474:
475: BEGIN
476: hr_utility.trace('Entered get_payroll_action_info');
477: open c_payroll_action_info(p_payroll_action_id);
478: hr_utility.trace('Opened c_payroll_action_info ');
479:
480: fetch c_payroll_action_info into ln_tax_unit_id,
481: ln_person_id,
482: ln_ssn,
485: ld_end_date,
486: ld_start_date,
487: ln_business_group_id,
488: ln_creation_date;
489: hr_utility.trace('Fetched c_payroll_action_info ');
490:
491: close c_payroll_action_info;
492:
493: hr_utility.trace('Closed c_payroll_action_info ');
489: hr_utility.trace('Fetched c_payroll_action_info ');
490:
491: close c_payroll_action_info;
492:
493: hr_utility.trace('Closed c_payroll_action_info ');
494: p_end_date := ld_end_date;
495: p_start_date := ld_start_date;
496: p_business_group_id := ln_business_group_id;
497: p_tax_unit_id := ln_tax_unit_id;
501: p_year := ln_year;
502: p_creation_date := fnd_date.canonical_to_date(
503: substr(fnd_date.date_to_canonical(ln_creation_date),1,10));
504:
505: hr_utility.trace('ld_end_date = ' ||
506: to_char(ld_end_date));
507: hr_utility.trace('ld_start_date = ' ||
508: to_char(ld_start_date));
509: hr_utility.trace('ln_tax_unit_id = ' ||
503: substr(fnd_date.date_to_canonical(ln_creation_date),1,10));
504:
505: hr_utility.trace('ld_end_date = ' ||
506: to_char(ld_end_date));
507: hr_utility.trace('ld_start_date = ' ||
508: to_char(ld_start_date));
509: hr_utility.trace('ln_tax_unit_id = ' ||
510: to_char(ln_tax_unit_id));
511: hr_utility.trace('ln_person_id = ' ||
505: hr_utility.trace('ld_end_date = ' ||
506: to_char(ld_end_date));
507: hr_utility.trace('ld_start_date = ' ||
508: to_char(ld_start_date));
509: hr_utility.trace('ln_tax_unit_id = ' ||
510: to_char(ln_tax_unit_id));
511: hr_utility.trace('ln_person_id = ' ||
512: to_char(ln_person_id));
513: hr_utility.trace('ln_ssn = ' ||
507: hr_utility.trace('ld_start_date = ' ||
508: to_char(ld_start_date));
509: hr_utility.trace('ln_tax_unit_id = ' ||
510: to_char(ln_tax_unit_id));
511: hr_utility.trace('ln_person_id = ' ||
512: to_char(ln_person_id));
513: hr_utility.trace('ln_ssn = ' ||
514: ln_ssn);
515: hr_utility.trace('ln_asg_set = ' ||
509: hr_utility.trace('ln_tax_unit_id = ' ||
510: to_char(ln_tax_unit_id));
511: hr_utility.trace('ln_person_id = ' ||
512: to_char(ln_person_id));
513: hr_utility.trace('ln_ssn = ' ||
514: ln_ssn);
515: hr_utility.trace('ln_asg_set = ' ||
516: to_char(ln_asg_set));
517: hr_utility.trace('ln_year = ' ||
511: hr_utility.trace('ln_person_id = ' ||
512: to_char(ln_person_id));
513: hr_utility.trace('ln_ssn = ' ||
514: ln_ssn);
515: hr_utility.trace('ln_asg_set = ' ||
516: to_char(ln_asg_set));
517: hr_utility.trace('ln_year = ' ||
518: to_char(ln_year));
519: hr_utility.trace('ln_creation_date = ' ||
513: hr_utility.trace('ln_ssn = ' ||
514: ln_ssn);
515: hr_utility.trace('ln_asg_set = ' ||
516: to_char(ln_asg_set));
517: hr_utility.trace('ln_year = ' ||
518: to_char(ln_year));
519: hr_utility.trace('ln_creation_date = ' ||
520: to_char(ln_creation_date));
521:
515: hr_utility.trace('ln_asg_set = ' ||
516: to_char(ln_asg_set));
517: hr_utility.trace('ln_year = ' ||
518: to_char(ln_year));
519: hr_utility.trace('ln_creation_date = ' ||
520: to_char(ln_creation_date));
521:
522: hr_utility.trace('Leaving get_payroll_action_info');
523: EXCEPTION
518: to_char(ln_year));
519: hr_utility.trace('ln_creation_date = ' ||
520: to_char(ln_creation_date));
521:
522: hr_utility.trace('Leaving get_payroll_action_info');
523: EXCEPTION
524: when others then
525: hr_utility.trace('Error in ' ||
526: to_char(sqlcode) || '-' || sqlerrm);
521:
522: hr_utility.trace('Leaving get_payroll_action_info');
523: EXCEPTION
524: when others then
525: hr_utility.trace('Error in ' ||
526: to_char(sqlcode) || '-' || sqlerrm);
527: raise hr_utility.hr_error;
528:
529: END get_payroll_action_info;
523: EXCEPTION
524: when others then
525: hr_utility.trace('Error in ' ||
526: to_char(sqlcode) || '-' || sqlerrm);
527: raise hr_utility.hr_error;
528:
529: END get_payroll_action_info;
530:
531: /*********************************************************************
548:
549:
550: BEGIN
551:
552: hr_utility.trace('g_report_type before call ='||g_report_type);
553:
554: open c_get_report_type;
555: fetch c_get_report_type into g_report_type;
556:
557: if c_get_report_type%NOTFOUND then
558: raise_application_error(-20001,'get_report_type: Payroll Action data not found');
559: end if;
560: close c_get_report_type;
561: hr_utility.trace('g_report_type after call ='||g_report_type);
562:
563: RETURN (g_report_type);
564:
565: END get_report_type;
640:
641: BEGIN
642:
643: l_step := 19;
644: hr_utility.trace('Checking for Puerto Rico GRE');
645:
646: open c_puerto_rico_gre_info(p_tax_unit_id);
647: fetch c_puerto_rico_gre_info into g_puerto_rico_gre;
648: hr_utility.trace('g_puerto_rico_gre = '||g_puerto_rico_gre);
644: hr_utility.trace('Checking for Puerto Rico GRE');
645:
646: open c_puerto_rico_gre_info(p_tax_unit_id);
647: fetch c_puerto_rico_gre_info into g_puerto_rico_gre;
648: hr_utility.trace('g_puerto_rico_gre = '||g_puerto_rico_gre);
649:
650: if c_puerto_rico_gre_info%NOTFOUND then
651: g_puerto_rico_gre := 'N';
652: else
672: /* Bug 13449377 - Code modified to archive following new balances. */
673: pay_us_archive.ltr_pr_balances(7).balance_name := 'PR W2 Charitable Contributions' ;
674: pay_us_archive.ltr_pr_balances(8).balance_name := 'PR W2 Contr To Save and Double Money' ;
675:
676: hr_utility.trace('Opening pay_us_payroll_utils.c_get_defined_balance_id');
677:
678: loop
679: ln_count := ln_count + 1;
680: /* Bug 13449377 - increased from 6 to 8 to accomodate the new archive balances for PuertoRico */
694: fetch pay_us_payroll_utils.c_get_defined_balance_id
695: into ln_defined_balance_id,
696: lv_user_entity_name;
697:
698: hr_utility.trace('Fetched pay_us_payroll_utils.c_get_defined_balance_id '
699: ||lv_balance_name);
700:
701: if pay_us_payroll_utils.c_get_defined_balance_id%NOTFOUND then
702: hr_utility.trace('Going to exit' );
698: hr_utility.trace('Fetched pay_us_payroll_utils.c_get_defined_balance_id '
699: ||lv_balance_name);
700:
701: if pay_us_payroll_utils.c_get_defined_balance_id%NOTFOUND then
702: hr_utility.trace('Going to exit' );
703: exit;
704: end if;
705:
706: l_step := 19.1;
706: l_step := 19.1;
707:
708: lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
709:
710: hr_utility.trace('lv_arch_user_entity_name = '
711: ||lv_arch_user_entity_name);
712: l_step := 19.2;
713: open c_get_arch_user_entity(lv_arch_user_entity_name);
714:
714:
715: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
716:
717: if c_get_arch_user_entity%notfound then
718: hr_utility.trace('Archived user_entity_id not found');
719: hr_utility.raise_error;
720: end if;
721: close c_get_arch_user_entity;
722: hr_utility.trace('ln_arch_user_entity_id = ' ||
715: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
716:
717: if c_get_arch_user_entity%notfound then
718: hr_utility.trace('Archived user_entity_id not found');
719: hr_utility.raise_error;
720: end if;
721: close c_get_arch_user_entity;
722: hr_utility.trace('ln_arch_user_entity_id = ' ||
723: to_char(ln_arch_user_entity_id));
718: hr_utility.trace('Archived user_entity_id not found');
719: hr_utility.raise_error;
720: end if;
721: close c_get_arch_user_entity;
722: hr_utility.trace('ln_arch_user_entity_id = ' ||
723: to_char(ln_arch_user_entity_id));
724: hr_utility.trace('ln_defined_balance_id = ' ||
725: to_char(ln_defined_balance_id));
726:
720: end if;
721: close c_get_arch_user_entity;
722: hr_utility.trace('ln_arch_user_entity_id = ' ||
723: to_char(ln_arch_user_entity_id));
724: hr_utility.trace('ln_defined_balance_id = ' ||
725: to_char(ln_defined_balance_id));
726:
727: l_step := 19.3;
728:
731: pay_us_archive.ltr_pr_balances(ln_count).user_entity_id := ln_arch_user_entity_id ;
732: close pay_us_payroll_utils.c_get_defined_balance_id;
733: end loop;
734:
735: hr_utility.trace('Closed cursor');
736:
737: l_step := 19.4;
738: END; /* Building Puerto Rico user entities */
739:
838: BEGIN
839:
840: pay_us_archive.ltr_1099_bal.delete;
841: l_step := 21;
842: hr_utility.trace('Checking for 1099R GRE');
843:
844: open c_1099_gre_info(p_tax_unit_id);
845:
846: fetch c_1099_gre_info into g_1099R_transmitter_code;
844: open c_1099_gre_info(p_tax_unit_id);
845:
846: fetch c_1099_gre_info into g_1099R_transmitter_code;
847:
848: hr_utility.trace('g_1099R_transmitter_code = '||g_1099R_transmitter_code);
849:
850: if c_1099_gre_info%rowcount >0 then --skchalla for the Bug 11693528
851: g_1099r_gre := TRUE;
852: end if;
873: ln_defined_balance_id := 0 ;
874:
875: lv_balance_dimension := '_PER_GRE_YTD';
876:
877: hr_utility.trace('Opening pay_us_payroll_utils.c_get_defined_balance_id');
878:
879: pay_us_archive.ltr_1099_bal(1).balance_name := 'Capital Gain' ;
880: pay_us_archive.ltr_1099_bal(2).balance_name := 'EE Contributions Or Premiums' ;
881: pay_us_archive.ltr_1099_bal(3).balance_name := 'Other EE Annuity Contract Amt' ;
903: fetch pay_us_payroll_utils.c_get_defined_balance_id
904: into ln_defined_balance_id
905: ,lv_user_entity_name;
906:
907: hr_utility.trace('Fetched pay_us_payroll_utils.c_get_defined_balance_id '
908: ||lv_balance_name);
909:
910: if pay_us_payroll_utils.c_get_defined_balance_id%NOTFOUND then
911: exit;
914: l_step := 21.1;
915:
916: lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
917:
918: hr_utility.trace('lv_arch_user_entity_name = '
919: ||lv_arch_user_entity_name);
920: l_step := 21.2;
921: open c_get_arch_user_entity(lv_arch_user_entity_name);
922:
922:
923: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
924:
925: if c_get_arch_user_entity%notfound then
926: hr_utility.trace('Archived user_entity_id not found');
927: hr_utility.raise_error;
928: end if;
929: close c_get_arch_user_entity;
930: hr_utility.trace('ln_arch_user_entity_id = ' ||
923: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
924:
925: if c_get_arch_user_entity%notfound then
926: hr_utility.trace('Archived user_entity_id not found');
927: hr_utility.raise_error;
928: end if;
929: close c_get_arch_user_entity;
930: hr_utility.trace('ln_arch_user_entity_id = ' ||
931: to_char(ln_arch_user_entity_id));
926: hr_utility.trace('Archived user_entity_id not found');
927: hr_utility.raise_error;
928: end if;
929: close c_get_arch_user_entity;
930: hr_utility.trace('ln_arch_user_entity_id = ' ||
931: to_char(ln_arch_user_entity_id));
932: hr_utility.trace('ln_defined_balance_id = ' ||
933: to_char(ln_defined_balance_id));
934:
928: end if;
929: close c_get_arch_user_entity;
930: hr_utility.trace('ln_arch_user_entity_id = ' ||
931: to_char(ln_arch_user_entity_id));
932: hr_utility.trace('ln_defined_balance_id = ' ||
933: to_char(ln_defined_balance_id));
934:
935: l_step := 21.3;
936:
939: pay_us_archive.ltr_1099_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
940: close pay_us_payroll_utils.c_get_defined_balance_id;
941:
942: end loop;
943: hr_utility.trace('Closed cursor');
944:
945: l_step := 21.4;
946:
947: END; /* Building 1099R user entities */
959:
960: fetch c_balance_feed_info into lv_feed_flag;
961:
962: l_step := 21.6;
963: hr_utility.trace('lv_feed_flag = '||lv_feed_flag);
964:
965: if c_balance_feed_info%NOTFOUND then
966: lv_feed_flag := 'N';
967: l_step := 21.7;
1065:
1066: BEGIN
1067: pay_us_archive.ltr_pre_tax_bal.delete;
1068:
1069: hr_utility.trace('p_business_group_id = '||to_char(p_business_group_id));
1070: hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
1071: BEGIN
1072:
1073: l_step := 14;
1066: BEGIN
1067: pay_us_archive.ltr_pre_tax_bal.delete;
1068:
1069: hr_utility.trace('p_business_group_id = '||to_char(p_business_group_id));
1070: hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
1071: BEGIN
1072:
1073: l_step := 14;
1074:
1071: BEGIN
1072:
1073: l_step := 14;
1074:
1075: hr_utility.trace('Opening c_balance cursor to get lookup codes');
1076:
1077: lv_balance_dimension := '_PER_GRE_YTD';
1078: g_pre_tax_info := 'Y' ;
1079: open c_balance;
1083: lv_user_entity_name := '';
1084: ln_defined_balance_id := 0 ;
1085:
1086: fetch c_balance into lv_balance_name ;
1087: hr_utility.trace('Fetched c_balance '||lv_balance_name);
1088: if c_balance%NOTFOUND then
1089: hr_utility.trace('Going to exit' );
1090: exit;
1091: end if;
1085:
1086: fetch c_balance into lv_balance_name ;
1087: hr_utility.trace('Fetched c_balance '||lv_balance_name);
1088: if c_balance%NOTFOUND then
1089: hr_utility.trace('Going to exit' );
1090: exit;
1091: end if;
1092:
1093: l_step := 15;
1100: lv_user_entity_name;
1101:
1102: lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
1103:
1104: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1105:
1106: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1107:
1108: hr_utility.trace('Into found loop of Box 12 ');
1104: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1105:
1106: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1107:
1108: hr_utility.trace('Into found loop of Box 12 ');
1109: hr_utility.trace('ln_defined_balance_id = '||to_char(ln_defined_balance_id));
1110:
1111: l_step := 16;
1112: open c_get_arch_user_entity(lv_arch_user_entity_name);
1105:
1106: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1107:
1108: hr_utility.trace('Into found loop of Box 12 ');
1109: hr_utility.trace('ln_defined_balance_id = '||to_char(ln_defined_balance_id));
1110:
1111: l_step := 16;
1112: open c_get_arch_user_entity(lv_arch_user_entity_name);
1113:
1113:
1114: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
1115:
1116: if c_get_arch_user_entity%notfound then
1117: hr_utility.trace('Archived user_entity_id not found');
1118: hr_utility.raise_error;
1119: end if;
1120: close c_get_arch_user_entity;
1121: hr_utility.trace('ln_arch_user_entity_id = ' ||
1114: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
1115:
1116: if c_get_arch_user_entity%notfound then
1117: hr_utility.trace('Archived user_entity_id not found');
1118: hr_utility.raise_error;
1119: end if;
1120: close c_get_arch_user_entity;
1121: hr_utility.trace('ln_arch_user_entity_id = ' ||
1122: to_char(ln_arch_user_entity_id));
1117: hr_utility.trace('Archived user_entity_id not found');
1118: hr_utility.raise_error;
1119: end if;
1120: close c_get_arch_user_entity;
1121: hr_utility.trace('ln_arch_user_entity_id = ' ||
1122: to_char(ln_arch_user_entity_id));
1123: hr_utility.trace('ln_defined_balance_id = ' ||
1124: to_char(ln_defined_balance_id));
1125:
1119: end if;
1120: close c_get_arch_user_entity;
1121: hr_utility.trace('ln_arch_user_entity_id = ' ||
1122: to_char(ln_arch_user_entity_id));
1123: hr_utility.trace('ln_defined_balance_id = ' ||
1124: to_char(ln_defined_balance_id));
1125:
1126: l_step := 17;
1127: ln_count := ln_count + 1;
1136:
1137: end loop;
1138: close c_balance;
1139:
1140: hr_utility.trace('Closed cursor');
1141:
1142: l_step := 18;
1143: END; /* Box 12 and PSD balances */
1144:
1202:
1203: BEGIN
1204: pay_us_archive.ltr_psd_tax_bal.delete;
1205:
1206: hr_utility.trace('p_business_group_id = '||to_char(p_business_group_id));
1207: hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
1208: BEGIN
1209:
1210: l_step := 1;
1203: BEGIN
1204: pay_us_archive.ltr_psd_tax_bal.delete;
1205:
1206: hr_utility.trace('p_business_group_id = '||to_char(p_business_group_id));
1207: hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
1208: BEGIN
1209:
1210: l_step := 1;
1211:
1208: BEGIN
1209:
1210: l_step := 1;
1211:
1212: hr_utility.trace('Opening c_balance cursor to get lookup codes');
1213:
1214: lv_balance_dimension := '_PER_GRE_YTD';
1215: g_psd_tax_info := 'Y' ;
1216:
1221: lv_user_entity_name := '';
1222: ln_defined_balance_id := 0 ;
1223:
1224: fetch c_get_psd_balance into lv_balance_name ;
1225: hr_utility.trace('Fetched c_balance '||lv_balance_name);
1226: if c_get_psd_balance%NOTFOUND then
1227: hr_utility.trace('Going to exit' );
1228: exit;
1229: end if;
1223:
1224: fetch c_get_psd_balance into lv_balance_name ;
1225: hr_utility.trace('Fetched c_balance '||lv_balance_name);
1226: if c_get_psd_balance%NOTFOUND then
1227: hr_utility.trace('Going to exit' );
1228: exit;
1229: end if;
1230:
1231: l_step := 2;
1238: lv_user_entity_name;
1239:
1240: lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
1241:
1242: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1243: hr_utility.trace('lv_balance_name = '||lv_balance_name);
1244: hr_utility.trace('lv_balance_dimension = '||lv_balance_dimension);
1245: hr_utility.trace('p_business_group_id = '||p_business_group_id);
1246:
1239:
1240: lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
1241:
1242: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1243: hr_utility.trace('lv_balance_name = '||lv_balance_name);
1244: hr_utility.trace('lv_balance_dimension = '||lv_balance_dimension);
1245: hr_utility.trace('p_business_group_id = '||p_business_group_id);
1246:
1247: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1240: lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
1241:
1242: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1243: hr_utility.trace('lv_balance_name = '||lv_balance_name);
1244: hr_utility.trace('lv_balance_dimension = '||lv_balance_dimension);
1245: hr_utility.trace('p_business_group_id = '||p_business_group_id);
1246:
1247: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1248:
1241:
1242: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1243: hr_utility.trace('lv_balance_name = '||lv_balance_name);
1244: hr_utility.trace('lv_balance_dimension = '||lv_balance_dimension);
1245: hr_utility.trace('p_business_group_id = '||p_business_group_id);
1246:
1247: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1248:
1249: hr_utility.trace('Into found loop of PSD balances ');
1245: hr_utility.trace('p_business_group_id = '||p_business_group_id);
1246:
1247: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1248:
1249: hr_utility.trace('Into found loop of PSD balances ');
1250: hr_utility.trace('ln_defined_balance_id = '||to_char(ln_defined_balance_id));
1251:
1252: l_step := 3;
1253: open c_get_arch_user_entity(lv_arch_user_entity_name);
1246:
1247: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1248:
1249: hr_utility.trace('Into found loop of PSD balances ');
1250: hr_utility.trace('ln_defined_balance_id = '||to_char(ln_defined_balance_id));
1251:
1252: l_step := 3;
1253: open c_get_arch_user_entity(lv_arch_user_entity_name);
1254:
1254:
1255: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
1256:
1257: if c_get_arch_user_entity%notfound then
1258: hr_utility.trace('Archived user_entity_id not found');
1259: hr_utility.raise_error;
1260: end if;
1261: close c_get_arch_user_entity;
1262: hr_utility.trace('ln_arch_user_entity_id = ' ||
1255: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
1256:
1257: if c_get_arch_user_entity%notfound then
1258: hr_utility.trace('Archived user_entity_id not found');
1259: hr_utility.raise_error;
1260: end if;
1261: close c_get_arch_user_entity;
1262: hr_utility.trace('ln_arch_user_entity_id = ' ||
1263: to_char(ln_arch_user_entity_id));
1258: hr_utility.trace('Archived user_entity_id not found');
1259: hr_utility.raise_error;
1260: end if;
1261: close c_get_arch_user_entity;
1262: hr_utility.trace('ln_arch_user_entity_id = ' ||
1263: to_char(ln_arch_user_entity_id));
1264: hr_utility.trace('ln_defined_balance_id = ' ||
1265: to_char(ln_defined_balance_id));
1266:
1260: end if;
1261: close c_get_arch_user_entity;
1262: hr_utility.trace('ln_arch_user_entity_id = ' ||
1263: to_char(ln_arch_user_entity_id));
1264: hr_utility.trace('ln_defined_balance_id = ' ||
1265: to_char(ln_defined_balance_id));
1266:
1267: l_step := 4;
1268: psd_count := psd_count + 1;
1277:
1278: end loop;
1279: close c_get_psd_balance;
1280:
1281: hr_utility.trace('Closed cursor');
1282:
1283: l_step := 5;
1284: END; /*PSD balances */
1285:
1331:
1332: BEGIN
1333: pay_us_archive.ltr_w2_box_bal.delete;
1334:
1335: hr_utility.trace('p_business_group_id = '||to_char(p_business_group_id));
1336: hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
1337: BEGIN
1338:
1339: l_step := 1;
1332: BEGIN
1333: pay_us_archive.ltr_w2_box_bal.delete;
1334:
1335: hr_utility.trace('p_business_group_id = '||to_char(p_business_group_id));
1336: hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
1337: BEGIN
1338:
1339: l_step := 1;
1340:
1337: BEGIN
1338:
1339: l_step := 1;
1340:
1341: hr_utility.trace('Opening c_balance cursor to get lookup codes');
1342:
1343: lv_balance_dimension := '_PER_GRE_YTD';
1344: g_w2_box_bal_info := 'Y' ;
1345: open c_balance;
1349: lv_user_entity_name := '';
1350: ln_defined_balance_id := 0 ;
1351:
1352: fetch c_balance into lv_balance_name ;
1353: hr_utility.trace('Fetched c_balance '||lv_balance_name);
1354: exit when c_balance%notfound;
1355: l_step := 2;
1356: open pay_us_payroll_utils.c_get_defined_balance_id(lv_balance_name,
1357: lv_balance_dimension,
1362: lv_user_entity_name;
1363:
1364: lv_arch_user_entity_name := 'A_'||lv_user_entity_name;
1365:
1366: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1367:
1368: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1369:
1370: hr_utility.trace('Into found loop of Box 12 ');
1366: hr_utility.trace('lv_arch_user_entity_name = '||lv_arch_user_entity_name);
1367:
1368: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1369:
1370: hr_utility.trace('Into found loop of Box 12 ');
1371: hr_utility.trace('ln_defined_balance_id = '||to_char(ln_defined_balance_id));
1372:
1373: l_step := 3;
1374: open c_get_arch_user_entity(lv_arch_user_entity_name);
1367:
1368: if pay_us_payroll_utils.c_get_defined_balance_id%FOUND then
1369:
1370: hr_utility.trace('Into found loop of Box 12 ');
1371: hr_utility.trace('ln_defined_balance_id = '||to_char(ln_defined_balance_id));
1372:
1373: l_step := 3;
1374: open c_get_arch_user_entity(lv_arch_user_entity_name);
1375:
1375:
1376: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
1377:
1378: if c_get_arch_user_entity%notfound then
1379: hr_utility.trace('Archived user_entity_id not found');
1380: hr_utility.raise_error;
1381: end if;
1382: close c_get_arch_user_entity;
1383: hr_utility.trace('ln_arch_user_entity_id = ' ||
1376: fetch c_get_arch_user_entity into ln_arch_user_entity_id;
1377:
1378: if c_get_arch_user_entity%notfound then
1379: hr_utility.trace('Archived user_entity_id not found');
1380: hr_utility.raise_error;
1381: end if;
1382: close c_get_arch_user_entity;
1383: hr_utility.trace('ln_arch_user_entity_id = ' ||
1384: to_char(ln_arch_user_entity_id));
1379: hr_utility.trace('Archived user_entity_id not found');
1380: hr_utility.raise_error;
1381: end if;
1382: close c_get_arch_user_entity;
1383: hr_utility.trace('ln_arch_user_entity_id = ' ||
1384: to_char(ln_arch_user_entity_id));
1385: hr_utility.trace('ln_defined_balance_id = ' ||
1386: to_char(ln_defined_balance_id));
1387:
1381: end if;
1382: close c_get_arch_user_entity;
1383: hr_utility.trace('ln_arch_user_entity_id = ' ||
1384: to_char(ln_arch_user_entity_id));
1385: hr_utility.trace('ln_defined_balance_id = ' ||
1386: to_char(ln_defined_balance_id));
1387:
1388: l_step := 4;
1389: ln_count := ln_count + 1;
1398:
1399: end loop;
1400: close c_balance;
1401:
1402: hr_utility.trace('Closed cursor');
1403:
1404: l_step := 5;
1405: END; /* W2 Box Info */
1406:
1579: BEGIN
1580:
1581: /* Get the report type, report qualifier, business group id and the
1582: gre for which the archiving has to be done */
1583: -- hr_utility.trace_on(null,'yepp');
1584:
1585: hr_utility.trace('In eoy_action_creation');
1586: hr_utility.trace('getting payroll action data');
1587:
1581: /* Get the report type, report qualifier, business group id and the
1582: gre for which the archiving has to be done */
1583: -- hr_utility.trace_on(null,'yepp');
1584:
1585: hr_utility.trace('In eoy_action_creation');
1586: hr_utility.trace('getting payroll action data');
1587:
1588: select effective_date,
1589: business_group_id,
1582: gre for which the archiving has to be done */
1583: -- hr_utility.trace_on(null,'yepp');
1584:
1585: hr_utility.trace('In eoy_action_creation');
1586: hr_utility.trace('getting payroll action data');
1587:
1588: select effective_date,
1589: business_group_id,
1590: to_number(substr(legislative_parameters,
1597: where payroll_action_id = pactid;
1598:
1599: l_year_start := trunc(l_effective_date, 'Y');
1600: l_year_end := add_months(trunc(l_effective_date, 'Y'),12) -1;
1601: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1602: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1603:
1604: l_range_person := pay_ac_utility.range_person_on(
1605: p_report_type => 'YREND'
1598:
1599: l_year_start := trunc(l_effective_date, 'Y');
1600: l_year_end := add_months(trunc(l_effective_date, 'Y'),12) -1;
1601: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1602: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1603:
1604: l_range_person := pay_ac_utility.range_person_on(
1605: p_report_type => 'YREND'
1606: ,p_report_format => 'YEPARCH'
1626:
1627: loop
1628: if l_range_person then
1629: fetch c_eoy_gre_person_on into l_person_id;
1630: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1631: exit when c_eoy_gre_person_on%NOTFOUND;
1632: else
1633: fetch c_eoy_gre into l_person_id;
1634: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1630: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1631: exit when c_eoy_gre_person_on%NOTFOUND;
1632: else
1633: fetch c_eoy_gre into l_person_id;
1634: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1635: exit when c_eoy_gre%NOTFOUND;
1636: end if;
1637:
1638: l_tax_unit_id := l_eoy_tax_unit_id;
1639:
1640: /* If the new row is the same as the previous row according to the way
1641: the rows are grouped then discard the row ie. grouping by GRE
1642: requires a single row for each person / GRE combination. */
1643: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
1644: hr_utility.trace('previous tax unit id is '||
1645: to_char(l_prev_tax_unit_id));
1646:
1647: if (l_person_id = l_prev_person_id and
1640: /* If the new row is the same as the previous row according to the way
1641: the rows are grouped then discard the row ie. grouping by GRE
1642: requires a single row for each person / GRE combination. */
1643: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
1644: hr_utility.trace('previous tax unit id is '||
1645: to_char(l_prev_tax_unit_id));
1646:
1647: if (l_person_id = l_prev_person_id and
1648: l_tax_unit_id = l_prev_tax_unit_id) then
1647: if (l_person_id = l_prev_person_id and
1648: l_tax_unit_id = l_prev_tax_unit_id) then
1649: null;
1650: else
1651: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1652: hr_utility.trace('person is '|| to_char(l_person_id));
1653:
1654: /* Have a new unique row according to the way the rows are grouped.
1655: The inclusion of the person is dependent on having a non zero
1648: l_tax_unit_id = l_prev_tax_unit_id) then
1649: null;
1650: else
1651: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1652: hr_utility.trace('person is '|| to_char(l_person_id));
1653:
1654: /* Have a new unique row according to the way the rows are grouped.
1655: The inclusion of the person is dependent on having a non zero
1656: balance.
1668: raise_application_error(-20001,'Balance Assignment Action ' ||
1669: 'does not exist for : ' ||
1670: to_char(l_person_id));
1671: end if;
1672: hr_utility.trace('l_bal_aaid in action creation code'||to_char(l_bal_aaid));
1673: if c_get_latest_asg%ISOPEN then
1674: close c_get_latest_asg;
1675: end if;
1676:
1675: end if;
1676:
1677: if l_bal_aaid <> -9999 then /* Assignment action in year */
1678: /* Set up the context of tax unit id */
1679: hr_utility.trace('Setting context');
1680: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1681:
1682: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1683: hr_utility.trace('defined_balance_id = '||
1678: /* Set up the context of tax unit id */
1679: hr_utility.trace('Setting context');
1680: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1681:
1682: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1683: hr_utility.trace('defined_balance_id = '||
1684: to_char(bal_db_item('GROSS_EARNINGS_PER_GRE_YTD')));
1685:
1686: l_value := nvl(pay_balance_pkg.get_value
1679: hr_utility.trace('Setting context');
1680: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1681:
1682: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1683: hr_utility.trace('defined_balance_id = '||
1684: to_char(bal_db_item('GROSS_EARNINGS_PER_GRE_YTD')));
1685:
1686: l_value := nvl(pay_balance_pkg.get_value
1687: (p_defined_balance_id
1688: => bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'),
1689: p_assignment_action_id => l_bal_aaid),0);
1690:
1691: if l_value = 0 then
1692: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1693: hr_utility.trace('defined_balance_id = '||
1694: to_char(bal_db_item('W2_NONTAX_SICK_PER_GRE_YTD')));
1695:
1696: l_value := nvl(pay_balance_pkg.get_value
1689: p_assignment_action_id => l_bal_aaid),0);
1690:
1691: if l_value = 0 then
1692: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1693: hr_utility.trace('defined_balance_id = '||
1694: to_char(bal_db_item('W2_NONTAX_SICK_PER_GRE_YTD')));
1695:
1696: l_value := nvl(pay_balance_pkg.get_value
1697: (p_defined_balance_id
1698: => bal_db_item('W2_NONTAX_SICK_PER_GRE_YTD'),
1699: p_assignment_action_id => l_bal_aaid),0);
1700:
1701: if l_value = 0 then
1702: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1703: hr_utility.trace('defined_balance_id = '||
1704: to_char(bal_db_item('W2_EXPENSE_REIMB_PER_GRE_YTD')));
1705:
1706: l_value := nvl(pay_balance_pkg.get_value
1699: p_assignment_action_id => l_bal_aaid),0);
1700:
1701: if l_value = 0 then
1702: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1703: hr_utility.trace('defined_balance_id = '||
1704: to_char(bal_db_item('W2_EXPENSE_REIMB_PER_GRE_YTD')));
1705:
1706: l_value := nvl(pay_balance_pkg.get_value
1707: (p_defined_balance_id
1708: => bal_db_item('W2_EXPENSE_REIMB_PER_GRE_YTD'),
1709: p_assignment_action_id => l_bal_aaid),0);
1710:
1711: if l_value = 0 then
1712: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1713: hr_utility.trace('defined_balance_id = '||
1714: to_char(bal_db_item('W2_QUAL_MOVE_PER_GRE_YTD')));
1715:
1716: l_value := nvl(pay_balance_pkg.get_value
1709: p_assignment_action_id => l_bal_aaid),0);
1710:
1711: if l_value = 0 then
1712: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1713: hr_utility.trace('defined_balance_id = '||
1714: to_char(bal_db_item('W2_QUAL_MOVE_PER_GRE_YTD')));
1715:
1716: l_value := nvl(pay_balance_pkg.get_value
1717: (p_defined_balance_id
1718: => bal_db_item('W2_QUAL_MOVE_PER_GRE_YTD'),
1719: p_assignment_action_id => l_bal_aaid),0);
1720:
1721: if l_value = 0 then
1722: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1723: hr_utility.trace('defined_balance_id = '||
1724: to_char(bal_db_item('W2_NO_GROSS_EARNINGS_PER_GRE_YTD')));
1725:
1726: l_value := nvl(pay_balance_pkg.get_value
1719: p_assignment_action_id => l_bal_aaid),0);
1720:
1721: if l_value = 0 then
1722: hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
1723: hr_utility.trace('defined_balance_id = '||
1724: to_char(bal_db_item('W2_NO_GROSS_EARNINGS_PER_GRE_YTD')));
1725:
1726: l_value := nvl(pay_balance_pkg.get_value
1727: (p_defined_balance_id
1760: into lockingactid
1761: from dual;
1762:
1763: /* Insert into pay_assignment_actions. */
1764: hr_utility.trace('creating asg action');
1765:
1766: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1767: pactid,chunk,l_tax_unit_id);
1768:
1769: /* Update the serial number column with the person id
1770: so that the mag routine and the W2 view will not have
1771: to do an additional checking against the assignment
1772: table */
1773: hr_utility.trace('updating asg action');
1774:
1775: update pay_assignment_actions aa
1776: set aa.serial_number = to_char(l_person_id)
1777: where aa.assignment_action_id = lockingactid;
1803: return number is
1804: l_user_entity_id number;
1805:
1806: begin
1807: --hr_utility.trace('p_dbi_name is '||p_dbi_name);
1808:
1809: select fdi.user_entity_id
1810: into l_user_entity_id
1811: from ff_database_items fdi,
1813: where fdi.user_name = p_dbi_name
1814: and fue.user_entity_id = fdi.user_entity_id
1815: and fue.legislation_code = 'US';
1816:
1817: --hr_utility.trace('user_entity_id is '||to_char(l_user_entity_id));
1818: return l_user_entity_id;
1819:
1820: exception
1821: when others then
1899: l_rowid_found := null;
1900: l_old_value := Null;
1901: end;
1902:
1903: hr_utility.trace('l_old_value = '||l_old_value);
1904:
1905: IF l_rowid_found IS NOT NULL THEN
1906:
1907: IF ( l_rearch
1908: AND (nvl( p_value(j),'-*9999999') <> nvl(l_old_value ,'-*9999999')
1909: )) THEN
1910:
1911: BEGIN
1912: hr_utility.trace('B4 update of value ');
1913: update ff_archive_items
1914: set value = p_value(j)
1915: where rowid = l_rowid_found;
1916:
1914: set value = p_value(j)
1915: where rowid = l_rowid_found;
1916:
1917: EXCEPTION WHEN OTHERS THEN
1918: hr_utility.trace('In others error for update -200 ');
1919: END;
1920:
1921: l_new_value := p_value(j);
1922: hr_utility.trace('Updating Non null value in re-arch with new value = '
1918: hr_utility.trace('In others error for update -200 ');
1919: END;
1920:
1921: l_new_value := p_value(j);
1922: hr_utility.trace('Updating Non null value in re-arch with new value = '
1923: ||p_value(j));
1924:
1925: /* calling the print procedure only if we have not null update */
1926:
1943: update ff_archive_items
1944: set value = p_value(j)
1945: where rowid = l_rowid_found;
1946: EXCEPTION WHEN OTHERS THEN
1947: hr_utility.trace('In others error for update -210 ');
1948: END;
1949:
1950: l_new_value := p_value(j);
1951: hr_utility.trace('Updating for other process new value = '||p_value(j));
1947: hr_utility.trace('In others error for update -210 ');
1948: END;
1949:
1950: l_new_value := p_value(j);
1951: hr_utility.trace('Updating for other process new value = '||p_value(j));
1952:
1953: END IF; /* smart archive call */
1954:
1955: END IF; /* End l_rearch */
1955: END IF; /* End l_rearch */
1956:
1957: ELSE /* Archive row does not exist */
1958:
1959: hr_utility.trace('No rowid found ');
1960: insert into ff_archive_items
1961: (ARCHIVE_ITEM_ID,
1962: USER_ENTITY_ID,
1963: CONTEXT1,
1992: /* Re-intializing value to null */
1993:
1994: l_old_value := null;
1995: l_new_value := null;
1996: hr_utility.trace('assigned null values before loop end');
1997:
1998: END LOOP; /* for j in p_user_entity_id.firs */
1999:
2000: ELSE /* State Level Employer Archive */
2129: end loop;
2130: end if; /* p_archive_level is EE */
2131: exception
2132: when others then
2133: hr_utility.trace('Error in create archive at step '||to_char(l_step)||' - '
2134: || to_char(sqlcode));
2135: raise_application_error(-20001,'Error in create archive at step '
2136: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2137: end create_archive;
2254: ,p_year => ln_year
2255: ,p_creation_date => g_pact_creation_date);
2256:
2257:
2258: hr_utility.trace('ln_year '||to_char(ld_end_date,'YYYY'));
2259:
2260: l_user_entity_id_tab(1) := get_user_entity_id('A_VIEW_ONLINE_W2');
2261: l_w2_profile_option := fnd_profile.value('HR_VIEW_ONLINE_W2');
2262: IF l_w2_profile_option IS NOT NULL THEN
2296: ,p_year => ln_year
2297: ,p_creation_date => g_pact_creation_date);
2298:
2299:
2300: hr_utility.trace('ln_year '||to_char(ld_end_date,'YYYY'));
2301:
2302: l_user_entity_id_tab(1) := get_user_entity_id('A_HR_ORIG_1099R_BEF');
2303: l_1099r_profile_option := fnd_profile.value('HR_ORIG_1099R_BEF');
2304: IF l_1099r_profile_option IS NOT NULL THEN
2343: exception
2344: when no_data_found then
2345: l_value1 := null;
2346: l_value2 := null;
2347: hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2348: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2349: end;
2350:
2351: /* Initialise the PL/SQL tables */
2416: when no_data_found then
2417: l_value1 := null;
2418: l_value2 := null;
2419: l_value3 := null;
2420: hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2421: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2422: end;
2423:
2424: /* Initialise the PL/SQL tables */
2481: exception
2482: when no_data_found then
2483: l_value1 := null;
2484: l_value2 := null;
2485: hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2486: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2487: end;
2488:
2489: /* Initialise the PL/SQL tables */
2505: l_context_val_tab(1) := p_tax_unit_id;
2506:
2507: l_arch_gre_step := 120;
2508:
2509: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2510: hr_utility.trace('value = '||l_value1);
2511: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2512: hr_utility.trace('value = '||l_value2);
2513:
2506:
2507: l_arch_gre_step := 120;
2508:
2509: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2510: hr_utility.trace('value = '||l_value1);
2511: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2512: hr_utility.trace('value = '||l_value2);
2513:
2514: create_archive (p_user_entity_id => l_user_entity_id_tab,
2507: l_arch_gre_step := 120;
2508:
2509: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2510: hr_utility.trace('value = '||l_value1);
2511: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2512: hr_utility.trace('value = '||l_value2);
2513:
2514: create_archive (p_user_entity_id => l_user_entity_id_tab,
2515: p_context1 => p_payroll_action_id,
2508:
2509: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2510: hr_utility.trace('value = '||l_value1);
2511: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2512: hr_utility.trace('value = '||l_value2);
2513:
2514: create_archive (p_user_entity_id => l_user_entity_id_tab,
2515: p_context1 => p_payroll_action_id,
2516: p_value => l_value_tab,
2537: exception
2538: when no_data_found then
2539: l_value1 := null;
2540: l_value2 := null;
2541: hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2542: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2543: when too_many_rows then
2544: l_value1 := null;
2545: l_value2 := null;
2566: l_context_val_tab(1) := p_tax_unit_id;
2567:
2568: l_arch_gre_step := 150;
2569:
2570: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2571: hr_utility.trace('value = '||l_value1);
2572: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2573: hr_utility.trace('value = '||l_value2);
2574:
2567:
2568: l_arch_gre_step := 150;
2569:
2570: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2571: hr_utility.trace('value = '||l_value1);
2572: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2573: hr_utility.trace('value = '||l_value2);
2574:
2575: create_archive (p_user_entity_id => l_user_entity_id_tab,
2568: l_arch_gre_step := 150;
2569:
2570: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2571: hr_utility.trace('value = '||l_value1);
2572: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2573: hr_utility.trace('value = '||l_value2);
2574:
2575: create_archive (p_user_entity_id => l_user_entity_id_tab,
2576: p_context1 => p_payroll_action_id,
2569:
2570: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(1));
2571: hr_utility.trace('value = '||l_value1);
2572: hr_utility.trace('l_user_entity_name = '||l_user_entity_id_tab(2));
2573: hr_utility.trace('value = '||l_value2);
2574:
2575: create_archive (p_user_entity_id => l_user_entity_id_tab,
2576: p_context1 => p_payroll_action_id,
2577: p_value => l_value_tab,
2660: --l_value15 := null; /* Job Development Fee (AL) */
2661: l_value16 := null;
2662: l_value19 := null; -- Bug 6928011 access code ( PR)
2663: l_value20 := null;
2664: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2665: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2666: end;
2667:
2668:
2783: and hoi.org_information_context || '' = 'W2 Reporting Rules 1';
2784: exception
2785: when no_data_found then
2786: l_value1 := null;
2787: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2788: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2789: end;
2790:
2791:
2846:
2847: if c_get_psd_rules%notfound then
2848: l_value1 := null;
2849: l_value2 := null;
2850: hr_utility.trace('Error in archive_gre_data at step :' ||
2851: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2852: end if;
2853:
2854:
2915: exception
2916: when no_data_found then
2917: l_value1 := null;
2918: l_value2 := null;
2919: hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
2920: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2921: end;
2922:
2923: l_user_entity_id_tab.delete;
2938: l_arch_gre_step := 201;
2939: l_user_entity_id_tab(2) := get_user_entity_id('A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER');
2940: l_value_tab(2) := l_value2;
2941:
2942: hr_utility.trace('A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER '||l_user_entity_id_tab(2));
2943: hr_utility.trace('Value for A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER '||l_value_tab(2));
2944:
2945: l_arch_gre_step := 210;
2946: create_archive (p_user_entity_id => l_user_entity_id_tab,
2939: l_user_entity_id_tab(2) := get_user_entity_id('A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER');
2940: l_value_tab(2) := l_value2;
2941:
2942: hr_utility.trace('A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER '||l_user_entity_id_tab(2));
2943: hr_utility.trace('Value for A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER '||l_value_tab(2));
2944:
2945: l_arch_gre_step := 210;
2946: create_archive (p_user_entity_id => l_user_entity_id_tab,
2947: p_context1 => p_payroll_action_id,
3012: when no_data_found then
3013: l_value1 := null;
3014: l_value2 := null;
3015: l_value3 := null;
3016: hr_utility.trace('Error in eoy_archive_gre_data at step : ' ||
3017: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
3018: end;
3019:
3020: l_user_entity_id_tab.delete;
3077: from ff_archive_items fai
3078: where fai.context1 = p_payroll_action_id);
3079: begin
3080:
3081: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
3082:
3083: if g_archive_flag = 'Y' then
3084: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
3085: return (TRUE);
3080:
3081: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
3082:
3083: if g_archive_flag = 'Y' then
3084: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
3085: return (TRUE);
3086: else
3087:
3088: hr_utility.trace('chk_gre_archive - opening cursor');
3084: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
3085: return (TRUE);
3086: else
3087:
3088: hr_utility.trace('chk_gre_archive - opening cursor');
3089:
3090: open c_chk_payroll_action;
3091: fetch c_chk_payroll_action into l_flag;
3092: if c_chk_payroll_action%FOUND then
3089:
3090: open c_chk_payroll_action;
3091: fetch c_chk_payroll_action into l_flag;
3092: if c_chk_payroll_action%FOUND then
3093: hr_utility.trace('chk_gre_archive - found in cursor');
3094: g_archive_flag := 'Y';
3095: else
3096: hr_utility.trace('chk_gre_archive - not found in cursor');
3097: g_archive_flag := 'N';
3092: if c_chk_payroll_action%FOUND then
3093: hr_utility.trace('chk_gre_archive - found in cursor');
3094: g_archive_flag := 'Y';
3095: else
3096: hr_utility.trace('chk_gre_archive - not found in cursor');
3097: g_archive_flag := 'N';
3098: end if;
3099:
3100: hr_utility.trace('chk_gre_archive - closing cursor');
3096: hr_utility.trace('chk_gre_archive - not found in cursor');
3097: g_archive_flag := 'N';
3098: end if;
3099:
3100: hr_utility.trace('chk_gre_archive - closing cursor');
3101: close c_chk_payroll_action;
3102: if g_archive_flag = 'Y' then
3103: hr_utility.trace('chk_gre_archive - returning true');
3104: return (TRUE);
3099:
3100: hr_utility.trace('chk_gre_archive - closing cursor');
3101: close c_chk_payroll_action;
3102: if g_archive_flag = 'Y' then
3103: hr_utility.trace('chk_gre_archive - returning true');
3104: return (TRUE);
3105: else
3106: hr_utility.trace('chk_gre_archive - returning false');
3107: return(FALSE);
3102: if g_archive_flag = 'Y' then
3103: hr_utility.trace('chk_gre_archive - returning true');
3104: return (TRUE);
3105: else
3106: hr_utility.trace('chk_gre_archive - returning false');
3107: return(FALSE);
3108: end if;
3109: end if;
3110: end chk_gre_archive;
3138:
3139:
3140: begin
3141:
3142: --hr_utility.trace_on(null,'PYUSARCH_DBG');
3143:
3144: hr_utility.set_location ('eoy_archinit',1);
3145:
3146:
3140: begin
3141:
3142: --hr_utility.trace_on(null,'PYUSARCH_DBG');
3143:
3144: hr_utility.set_location ('eoy_archinit',1);
3145:
3146:
3147: l_jurisdiction_code := '99-999-9999';
3148:
3217:
3218: l_step := 6;
3219: pay_balance_pkg.set_context ('JURISDICTION_CODE',l_jurisdiction_code);
3220:
3221: hr_utility.set_location ('eoy_archinit',2);
3222:
3223: /* Get the tax unit id and set it up as the context */
3224: l_step := 7;
3225: /*
3262: ,p_year => ln_year
3263: ,p_creation_date => g_pact_creation_date);
3264:
3265:
3266: hr_utility.set_location ('eoy_archinit',3);
3267:
3268: l_step := 8;
3269: pay_balance_pkg.set_context ('TAX_UNIT_ID',l_tax_unit_id);
3270:
3268: l_step := 8;
3269: pay_balance_pkg.set_context ('TAX_UNIT_ID',l_tax_unit_id);
3270:
3271: l_step := 9;
3272: hr_utility.set_location ('eoy_archinit getting min chunk number',10);
3273:
3274: open c_get_min_chunk;
3275: fetch c_get_min_chunk into g_min_chunk;
3276: l_step := 10;
3279: raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
3280: end if;
3281: close c_get_min_chunk;
3282:
3283: hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
3284: l_step := 11;
3285: l_archive := chk_gre_archive(p_payroll_action_id);
3286:
3287: l_step := 12;
3284: l_step := 11;
3285: l_archive := chk_gre_archive(p_payroll_action_id);
3286:
3287: l_step := 12;
3288: hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
3289:
3290: pay_us_archive.g_report_type := pay_us_archive.get_report_type(p_payroll_action_id);
3291: pay_us_archive.g_puerto_rico_gre := pay_us_archive.get_puerto_rico_info(l_tax_unit_id) ;
3292: pay_us_archive.g_1099R_transmitter_code := pay_us_archive.get_1099r_info(l_tax_unit_id) ;
3426:
3427: l_archive_itemid NUMBER;
3428:
3429: BEGIN
3430: hr_utility.trace('p_archive_name '||p_archive_name);
3431: hr_utility.trace('context1 '||p_context);
3432:
3433: OPEN c_get_ff_arch;
3434: FETCH c_get_ff_arch INTO l_archive_itemid;
3427: l_archive_itemid NUMBER;
3428:
3429: BEGIN
3430: hr_utility.trace('p_archive_name '||p_archive_name);
3431: hr_utility.trace('context1 '||p_context);
3432:
3433: OPEN c_get_ff_arch;
3434: FETCH c_get_ff_arch INTO l_archive_itemid;
3435: CLOSE c_get_ff_arch;
3434: FETCH c_get_ff_arch INTO l_archive_itemid;
3435: CLOSE c_get_ff_arch;
3436:
3437:
3438: hr_utility.trace('l_archive_itemid '||l_archive_itemid);
3439:
3440: delete from ff_archive_item_contexts
3441: where archive_item_id = l_archive_itemid;
3442:
3460: BEGIN
3461:
3462: l_corrected_date := fnd_date.canonical_to_date(
3463: substr(fnd_date.date_to_canonical(sysdate),1,10));
3464: hr_utility.trace('Archive Date : ' || l_corrected_date);
3465:
3466: l_profile_date := fnd_date.canonical_to_date(
3467: pay_us_archive_util.get_archive_value(l_payroll_action_id,
3468: 'A_VIEW_ONLINE_W2',
3473: 'A_ADD_ARCHIVE',
3474: l_taxunitid);
3475:
3476:
3477: hr_utility.trace('View Online W2 Profile date'||l_profile_date);
3478: hr_utility.trace('l_add_archive '||l_add_archive);
3479:
3480: IF nvl(l_add_archive,'N') = 'Y' THEN
3481: delete_ff_archive(p_assactid,'A_ADD_ARCHIVE');
3474: l_taxunitid);
3475:
3476:
3477: hr_utility.trace('View Online W2 Profile date'||l_profile_date);
3478: hr_utility.trace('l_add_archive '||l_add_archive);
3479:
3480: IF nvl(l_add_archive,'N') = 'Y' THEN
3481: delete_ff_archive(p_assactid,'A_ADD_ARCHIVE');
3482: return 'N';
3504: BEGIN
3505:
3506: l_corrected_date := fnd_date.canonical_to_date(
3507: substr(fnd_date.date_to_canonical(sysdate),1,10));
3508: hr_utility.trace('Archive Date : ' || l_corrected_date);
3509:
3510: l_profile_date := fnd_date.canonical_to_date(
3511: pay_us_archive_util.get_archive_value(l_payroll_action_id,
3512: 'A_HR_ORIG_1099R_BEF',
3517: 'A_ADD_ARCHIVE',
3518: l_taxunitid);
3519:
3520:
3521: hr_utility.trace('View Online W2 Profile date'||l_profile_date);
3522: hr_utility.trace('l_add_archive '||l_add_archive);
3523:
3524: IF nvl(l_add_archive,'N') = 'Y' THEN
3525: delete_ff_archive(p_assactid,'A_ADD_ARCHIVE');
3518: l_taxunitid);
3519:
3520:
3521: hr_utility.trace('View Online W2 Profile date'||l_profile_date);
3522: hr_utility.trace('l_add_archive '||l_add_archive);
3523:
3524: IF nvl(l_add_archive,'N') = 'Y' THEN
3525: delete_ff_archive(p_assactid,'A_ADD_ARCHIVE');
3526: return 'N';
3906: and paa.assignment_action_id = cp_assignment_action_id;
3907:
3908: begin
3909:
3910: --hr_utility.trace_on(null,'yepp');
3911:
3912: hr_utility.trace('.....AAID is ' || to_char(p_assactid));
3913:
3914: hr_utility.trace('Archive Data');
3908: begin
3909:
3910: --hr_utility.trace_on(null,'yepp');
3911:
3912: hr_utility.trace('.....AAID is ' || to_char(p_assactid));
3913:
3914: hr_utility.trace('Archive Data');
3915: hr_utility.set_location ('archive_data',1);
3916: hr_utility.trace('getting assignment');
3910: --hr_utility.trace_on(null,'yepp');
3911:
3912: hr_utility.trace('.....AAID is ' || to_char(p_assactid));
3913:
3914: hr_utility.trace('Archive Data');
3915: hr_utility.set_location ('archive_data',1);
3916: hr_utility.trace('getting assignment');
3917:
3918: l_step := 1;
3911:
3912: hr_utility.trace('.....AAID is ' || to_char(p_assactid));
3913:
3914: hr_utility.trace('Archive Data');
3915: hr_utility.set_location ('archive_data',1);
3916: hr_utility.trace('getting assignment');
3917:
3918: l_step := 1;
3919:
3912: hr_utility.trace('.....AAID is ' || to_char(p_assactid));
3913:
3914: hr_utility.trace('Archive Data');
3915: hr_utility.set_location ('archive_data',1);
3916: hr_utility.trace('getting assignment');
3917:
3918: l_step := 1;
3919:
3920: SELECT aa.assignment_id,
3937: archived then archive the gre data i.e. the employer data */
3938:
3939: l_step := 2;
3940:
3941: hr_utility.trace('Chunk Number is : ' || to_char(l_chunk));
3942:
3943: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3944:
3945: l_step := 3;
3942:
3943: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3944:
3945: l_step := 3;
3946: hr_utility.trace('eoy_archive_data archiving employer data');
3947:
3948: if g_report_type <> 'W2C_PRE_PROCESS' then
3949:
3950: eoy_archive_gre_data(
3956: g_archive_flag := 'Y';
3957: end if ;
3958:
3959: l_step := 4;
3960: hr_utility.trace('eoy_archive_data archived employer data');
3961: end if;
3962:
3963: hr_utility.set_location ('archive_data',2);
3964:
3959: l_step := 4;
3960: hr_utility.trace('eoy_archive_data archived employer data');
3961: end if;
3962:
3963: hr_utility.set_location ('archive_data',2);
3964:
3965: hr_utility.trace('assignment '|| to_char(l_asgid));
3966: hr_utility.trace('person id '|| to_char(l_person_id));
3967: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3961: end if;
3962:
3963: hr_utility.set_location ('archive_data',2);
3964:
3965: hr_utility.trace('assignment '|| to_char(l_asgid));
3966: hr_utility.trace('person id '|| to_char(l_person_id));
3967: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3968: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
3969:
3962:
3963: hr_utility.set_location ('archive_data',2);
3964:
3965: hr_utility.trace('assignment '|| to_char(l_asgid));
3966: hr_utility.trace('person id '|| to_char(l_person_id));
3967: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3968: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
3969:
3970: /* Derive the beginning and end of the effective year */
3963: hr_utility.set_location ('archive_data',2);
3964:
3965: hr_utility.trace('assignment '|| to_char(l_asgid));
3966: hr_utility.trace('person id '|| to_char(l_person_id));
3967: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3968: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
3969:
3970: /* Derive the beginning and end of the effective year */
3971:
3964:
3965: hr_utility.trace('assignment '|| to_char(l_asgid));
3966: hr_utility.trace('person id '|| to_char(l_person_id));
3967: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3968: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
3969:
3970: /* Derive the beginning and end of the effective year */
3971:
3972: hr_utility.trace('getting begin and end dates');
3968: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
3969:
3970: /* Derive the beginning and end of the effective year */
3971:
3972: hr_utility.trace('getting begin and end dates');
3973:
3974: l_step := 5;
3975:
3976: l_year_start := trunc(p_effective_date, 'Y');
3975:
3976: l_year_start := trunc(p_effective_date, 'Y');
3977: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3978:
3979: hr_utility.trace('year start '|| to_char(l_year_start));
3980: hr_utility.trace('year end '|| to_char(l_year_end));
3981:
3982: open c_get_latest_asg(l_person_id );
3983: fetch c_get_latest_asg into l_aaid;
3976: l_year_start := trunc(p_effective_date, 'Y');
3977: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3978:
3979: hr_utility.trace('year start '|| to_char(l_year_start));
3980: hr_utility.trace('year end '|| to_char(l_year_end));
3981:
3982: open c_get_latest_asg(l_person_id );
3983: fetch c_get_latest_asg into l_aaid;
3984: hr_utility.trace('l_aaid in archive code '||to_char(l_aaid));
3980: hr_utility.trace('year end '|| to_char(l_year_end));
3981:
3982: open c_get_latest_asg(l_person_id );
3983: fetch c_get_latest_asg into l_aaid;
3984: hr_utility.trace('l_aaid in archive code '||to_char(l_aaid));
3985: close c_get_latest_asg;
3986:
3987:
3988: /* Initialise the PL/SQL table before populating it */
3986:
3987:
3988: /* Initialise the PL/SQL table before populating it */
3989:
3990: hr_utility.trace('Initialising Pl/SQL table');
3991:
3992: l_step := 6;
3993:
3994: for i in 1..l_context_no loop
4013: l_count := l_count + 1;
4014: pay_archive.g_context_values.name(l_count) := 'TAX_UNIT_ID';
4015: pay_archive.g_context_values.value(l_count) := l_taxunitid;
4016:
4017: hr_utility.trace('Initialised Pl/SQL table');
4018:
4019: /* Get the context_id for 'Jurisdiction' from ff_contexts */
4020:
4021: l_step := 8;
4062: l_true := 'N';
4063: open c_get_city;
4064: loop
4065:
4066: hr_utility.trace('In city loop ');
4067:
4068: l_step := 15;
4069:
4070: fetch c_get_city into l_jurisdiction;
4069:
4070: fetch c_get_city into l_jurisdiction;
4071: exit when c_get_city%NOTFOUND;
4072:
4073: hr_utility.trace('assignment '|| to_char(l_asgid));
4074: hr_utility.trace('City JD is ' || l_jurisdiction);
4075:
4076: l_step := 16;
4077:
4070: fetch c_get_city into l_jurisdiction;
4071: exit when c_get_city%NOTFOUND;
4072:
4073: hr_utility.trace('assignment '|| to_char(l_asgid));
4074: hr_utility.trace('City JD is ' || l_jurisdiction);
4075:
4076: l_step := 16;
4077:
4078: l_count := l_count + 1;
4088:
4089: if l_jd_name_done_tab.exists(l_jd_index) then
4090:
4091: l_city_name := l_jd_name_done_tab(l_jd_index).jd_name;
4092: hr_utility.trace('Getting City Name from cache '|| l_city_name);
4093:
4094: else
4095:
4096: hr_utility.trace('Getting City Name from DB');
4092: hr_utility.trace('Getting City Name from cache '|| l_city_name);
4093:
4094: else
4095:
4096: hr_utility.trace('Getting City Name from DB');
4097:
4098: select city_name
4099: into l_city_name
4100: from pay_us_city_names pcn
4106: l_jd_name_done_tab(l_jd_index).jd_name := l_city_name;
4107:
4108: end if;
4109:
4110: hr_utility.trace('Archiving the city ' || l_jurisdiction);
4111:
4112: l_balance_feed_tab.delete;
4113: l_defined_balance_id_tab.delete;
4114: l_user_entity_id_tab.delete;
4139:
4140: end loop;
4141: close c_get_city;
4142:
4143: hr_utility.trace('Out of city loop ');
4144:
4145: l_step := 19;
4146: open c_get_county;
4147: loop
4145: l_step := 19;
4146: open c_get_county;
4147: loop
4148:
4149: hr_utility.trace('In county loop ');
4150:
4151: l_step := 20;
4152:
4153: fetch c_get_county into l_jurisdiction;
4152:
4153: fetch c_get_county into l_jurisdiction;
4154: exit when c_get_county%NOTFOUND;
4155:
4156: hr_utility.trace('assignment '|| to_char(l_asgid));
4157: hr_utility.trace('County JD is ' || l_jurisdiction);
4158:
4159: l_jd_index := replace(l_jurisdiction,'-');
4160:
4153: fetch c_get_county into l_jurisdiction;
4154: exit when c_get_county%NOTFOUND;
4155:
4156: hr_utility.trace('assignment '|| to_char(l_asgid));
4157: hr_utility.trace('County JD is ' || l_jurisdiction);
4158:
4159: l_jd_index := replace(l_jurisdiction,'-');
4160:
4161: l_step := 21;
4193:
4194: if l_jd_name_done_tab.exists(l_jd_index) then
4195:
4196: l_county_name := l_jd_name_done_tab(l_jd_index).jd_name;
4197: hr_utility.trace('Getting County Name from cache '|| l_county_name);
4198:
4199: else
4200:
4201: hr_utility.trace('Getting County Name from DB');
4197: hr_utility.trace('Getting County Name from cache '|| l_county_name);
4198:
4199: else
4200:
4201: hr_utility.trace('Getting County Name from DB');
4202:
4203: select county_name
4204: into l_county_name
4205: from pay_us_counties puc
4214:
4215:
4216: l_step := 23;
4217:
4218: hr_utility.trace('Archive county '||substr(l_jurisdiction,1,7)||'0000');
4219:
4220: l_user_entity_id_tab.delete;
4221: l_value_tab.delete;
4222: l_seq_tab.delete;
4244:
4245: end loop;
4246: close c_get_county;
4247:
4248: hr_utility.trace('Out of county loop ');
4249:
4250: l_step := 25;
4251:
4252: open c_get_state;
4251:
4252: open c_get_state;
4253: loop
4254:
4255: hr_utility.trace('In state loop ');
4256: fetch c_get_state into l_jurisdiction;
4257: exit when c_get_state%NOTFOUND;
4258:
4259: hr_utility.trace('assignment '|| to_char(l_asgid));
4255: hr_utility.trace('In state loop ');
4256: fetch c_get_state into l_jurisdiction;
4257: exit when c_get_state%NOTFOUND;
4258:
4259: hr_utility.trace('assignment '|| to_char(l_asgid));
4260: hr_utility.trace('State JD is ' || l_jurisdiction);
4261:
4262: l_jd_index := replace(l_jurisdiction,'-');
4263:
4256: fetch c_get_state into l_jurisdiction;
4257: exit when c_get_state%NOTFOUND;
4258:
4259: hr_utility.trace('assignment '|| to_char(l_asgid));
4260: hr_utility.trace('State JD is ' || l_jurisdiction);
4261:
4262: l_jd_index := replace(l_jurisdiction,'-');
4263:
4264: l_true := 'N';
4292:
4293: if l_jd_name_done_tab.exists(l_jd_index) then
4294:
4295: l_state_abbrev := l_jd_name_done_tab(l_jd_index).jd_name;
4296: hr_utility.trace('Getting State Abbrev from cache '|| l_state_abbrev);
4297:
4298: else
4299:
4300: hr_utility.trace('Getting State Abbrev from DB');
4296: hr_utility.trace('Getting State Abbrev from cache '|| l_state_abbrev);
4297:
4298: else
4299:
4300: hr_utility.trace('Getting State Abbrev from DB');
4301:
4302: l_step := 26;
4303:
4304: select state_abbrev
4313: end if; /* l_chk_state_archive = 'N' */
4314:
4315: l_step := 27;
4316:
4317: hr_utility.trace('Archive state' ||l_jurisdiction);
4318:
4319: l_user_entity_id_tab.delete;
4320: l_value_tab.delete;
4321: l_seq_tab.delete;
4325: /* Assign values to the PL/SQL tables */
4326:
4327: l_step := 28;
4328:
4329: hr_utility.trace('Value of g_state_uei is : ' || to_char(g_state_uei));
4330:
4331: l_user_entity_id_tab(1) := g_state_uei;
4332: l_value_tab(1) := l_state_abbrev;
4333: l_seq_tab(1) := 1;
4345:
4346: end loop;
4347: close c_get_state;
4348:
4349: hr_utility.trace('Out of state loop ');
4350:
4351: l_step := 39;
4352:
4353: open c_get_cnt_sd;
4352:
4353: open c_get_cnt_sd;
4354: loop
4355: l_step := 40;
4356: hr_utility.trace('In sd loop ');
4357: fetch c_get_cnt_sd into l_jurisdiction,l_county_code;
4358: exit when c_get_cnt_sd%NOTFOUND;
4359:
4360: l_step := 41;
4421:
4422: end loop;
4423: close c_get_cnt_sd;
4424:
4425: hr_utility.trace('Out of cnt_sd loop ');
4426:
4427: l_step := 45;
4428:
4429: open c_get_cty_sd;
4428:
4429: open c_get_cty_sd;
4430: loop
4431:
4432: hr_utility.trace('In cty_sd loop ');
4433:
4434: l_step := 46;
4435:
4436: fetch c_get_cty_sd into l_jurisdiction,l_county_code, l_city_code;
4502: end if;
4503: end loop;
4504: close c_get_cty_sd;
4505:
4506: hr_utility.trace('Out of cty_sd loop ');
4507:
4508: l_step := 51;
4509:
4510: /* Set the no. of contexts */
4512:
4513: if l_count = 1 then
4514: -- pay_balance_pkg.set_context ('JURISDICTION_CODE',lt_jursd_context(1));
4515: -- lt_jursd_context(1) := NULL;
4516: hr_utility.trace('One context only name : ' || pay_archive.g_context_values.name(1));
4517: hr_utility.trace('One context only value : ' || pay_archive.g_context_values.value(1));
4518: else
4519: for i in 1..l_count loop
4520: hr_utility.trace('Multiple context name : ' || pay_archive.g_context_values.name(i));
4513: if l_count = 1 then
4514: -- pay_balance_pkg.set_context ('JURISDICTION_CODE',lt_jursd_context(1));
4515: -- lt_jursd_context(1) := NULL;
4516: hr_utility.trace('One context only name : ' || pay_archive.g_context_values.name(1));
4517: hr_utility.trace('One context only value : ' || pay_archive.g_context_values.value(1));
4518: else
4519: for i in 1..l_count loop
4520: hr_utility.trace('Multiple context name : ' || pay_archive.g_context_values.name(i));
4521: hr_utility.trace('Multiple context value : ' || pay_archive.g_context_values.value(i));
4516: hr_utility.trace('One context only name : ' || pay_archive.g_context_values.name(1));
4517: hr_utility.trace('One context only value : ' || pay_archive.g_context_values.value(1));
4518: else
4519: for i in 1..l_count loop
4520: hr_utility.trace('Multiple context name : ' || pay_archive.g_context_values.name(i));
4521: hr_utility.trace('Multiple context value : ' || pay_archive.g_context_values.value(i));
4522: -- hr_utility.trace('Multiple context ('|| to_char(i)||') : ' || lt_jursd_context(i));
4523: end loop;
4524: end if;
4517: hr_utility.trace('One context only value : ' || pay_archive.g_context_values.value(1));
4518: else
4519: for i in 1..l_count loop
4520: hr_utility.trace('Multiple context name : ' || pay_archive.g_context_values.name(i));
4521: hr_utility.trace('Multiple context value : ' || pay_archive.g_context_values.value(i));
4522: -- hr_utility.trace('Multiple context ('|| to_char(i)||') : ' || lt_jursd_context(i));
4523: end loop;
4524: end if;
4525:
4518: else
4519: for i in 1..l_count loop
4520: hr_utility.trace('Multiple context name : ' || pay_archive.g_context_values.name(i));
4521: hr_utility.trace('Multiple context value : ' || pay_archive.g_context_values.value(i));
4522: -- hr_utility.trace('Multiple context ('|| to_char(i)||') : ' || lt_jursd_context(i));
4523: end loop;
4524: end if;
4525:
4526: hr_utility.trace('g_context_values.sz : ' || pay_archive.g_context_values.sz);
4522: -- hr_utility.trace('Multiple context ('|| to_char(i)||') : ' || lt_jursd_context(i));
4523: end loop;
4524: end if;
4525:
4526: hr_utility.trace('g_context_values.sz : ' || pay_archive.g_context_values.sz);
4527:
4528: /* Flush all jurisdiction contexts */
4529:
4530: hr_utility.trace('l_jd_done_tab....first : '|| l_jd_done_tab.first);
4526: hr_utility.trace('g_context_values.sz : ' || pay_archive.g_context_values.sz);
4527:
4528: /* Flush all jurisdiction contexts */
4529:
4530: hr_utility.trace('l_jd_done_tab....first : '|| l_jd_done_tab.first);
4531: hr_utility.trace('l_jd_done_tab....last : '|| l_jd_done_tab.last);
4532:
4533: if l_jd_done_tab.count > 0 then
4534:
4527:
4528: /* Flush all jurisdiction contexts */
4529:
4530: hr_utility.trace('l_jd_done_tab....first : '|| l_jd_done_tab.first);
4531: hr_utility.trace('l_jd_done_tab....last : '|| l_jd_done_tab.last);
4532:
4533: if l_jd_done_tab.count > 0 then
4534:
4535: for i in l_jd_done_tab.first .. l_jd_done_tab.last loop
4533: if l_jd_done_tab.count > 0 then
4534:
4535: for i in l_jd_done_tab.first .. l_jd_done_tab.last loop
4536:
4537: hr_utility.trace('l_jd_done_tab.... value of : '|| i ||' is '|| l_jd_done_tab(i) );
4538:
4539: end loop;
4540:
4541: end if;
4558: (p_defined_balance_id => pay_us_archive.ltr_pre_tax_bal(ln_count).defined_balance,
4559: p_assignment_action_id => l_aaid),0);
4560:
4561: l_step := 54;
4562: hr_utility.trace('lv_value is '||lv_value);
4563:
4564: ff_archive_api.create_archive_item(
4565: p_archive_item_id => l_archive_item_id,
4566: p_user_entity_id => pay_us_archive.ltr_pre_tax_bal(ln_count).user_entity_id,
4590: (p_defined_balance_id => pay_us_archive.ltr_w2_box_bal(ln_count).defined_balance,
4591: p_assignment_action_id => l_aaid),0);
4592:
4593: l_step := 55.2;
4594: hr_utility.trace('lv_value is '||lv_value);
4595:
4596: ff_archive_api.create_archive_item(
4597: p_archive_item_id => l_archive_item_id,
4598: p_user_entity_id => pay_us_archive.ltr_w2_box_bal(ln_count).user_entity_id,
4616: /* Puerto Rico Specific Archive */
4617:
4618: if g_puerto_rico_gre = 'Y' then
4619:
4620: hr_utility.trace('Entered Puerto Rico GRE ');
4621:
4622: l_step := 57;
4623: l_user_entity_id_tab.delete;
4624: l_value_tab.delete;
4668: exception when no_data_found then
4669: l_con_national_identifier := null;
4670: end;
4671:
4672: hr_utility.trace('Maritial Status = '||l_marital_status);
4673: hr_utility.trace('Contact National Identifier = '||l_con_national_identifier);
4674:
4675: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4676:
4669: l_con_national_identifier := null;
4670: end;
4671:
4672: hr_utility.trace('Maritial Status = '||l_marital_status);
4673: hr_utility.trace('Contact National Identifier = '||l_con_national_identifier);
4674:
4675: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4676:
4677: l_step := 58;
4730:
4731: -- if g_1099R_transmitter_code is not null then --by skchalla for the Bug 11693528
4732:
4733: if(g_1099r_gre) then --skchalla for the Bug 11693528
4734: hr_utility.trace('1099 reporting rules exists for the GRE');
4735:
4736: l_step := 76;
4737:
4738: l_user_entity_id_tab.delete;
4743: l_context_id_tab.delete;
4744: l_context_val_tab.delete;
4745: l_index := 0;
4746:
4747: hr_utility.trace('Deleted plsql tables ');
4748:
4749: begin
4750:
4751: open c_get_1099_eit_info(l_asgid);
4748:
4749: begin
4750:
4751: open c_get_1099_eit_info(l_asgid);
4752: hr_utility.trace('Opened c_get_1099_eit_info ');
4753: fetch c_get_1099_eit_info into l_taxable_amount_unknown
4754: ,l_total_distributions
4755: ,l_ee_distribution_percent
4756: ,l_total_distribution_percent;
4768: exception when no_data_found then
4769: l_marital_status := null;
4770: end;
4771:
4772: hr_utility.trace('l_taxable_amount_unknown = '||l_taxable_amount_unknown);
4773: hr_utility.trace('l_total_distributions = '||l_total_distributions);
4774: hr_utility.trace('l_ee_distribution_percent='||l_ee_distribution_percent);
4775: hr_utility.trace('l_total_distribution_percent = '||l_total_distribution_percent);
4776:
4769: l_marital_status := null;
4770: end;
4771:
4772: hr_utility.trace('l_taxable_amount_unknown = '||l_taxable_amount_unknown);
4773: hr_utility.trace('l_total_distributions = '||l_total_distributions);
4774: hr_utility.trace('l_ee_distribution_percent='||l_ee_distribution_percent);
4775: hr_utility.trace('l_total_distribution_percent = '||l_total_distribution_percent);
4776:
4777:
4770: end;
4771:
4772: hr_utility.trace('l_taxable_amount_unknown = '||l_taxable_amount_unknown);
4773: hr_utility.trace('l_total_distributions = '||l_total_distributions);
4774: hr_utility.trace('l_ee_distribution_percent='||l_ee_distribution_percent);
4775: hr_utility.trace('l_total_distribution_percent = '||l_total_distribution_percent);
4776:
4777:
4778: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4771:
4772: hr_utility.trace('l_taxable_amount_unknown = '||l_taxable_amount_unknown);
4773: hr_utility.trace('l_total_distributions = '||l_total_distributions);
4774: hr_utility.trace('l_ee_distribution_percent='||l_ee_distribution_percent);
4775: hr_utility.trace('l_total_distribution_percent = '||l_total_distribution_percent);
4776:
4777:
4778: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4779:
4801:
4802: l_step := 78;
4803:
4804: open c_get_1099_distribution_info(l_person_id,l_taxunitid);
4805: hr_utility.trace('Opened c_get_1099_distribution_info ');
4806:
4807: l_step := 79;
4808:
4809: fetch c_get_1099_distribution_info into l_distribution_code;
4813: elsif c_get_1099_distribution_info%ROWCOUNT > 1 then
4814: l_mesg :='Person id '||to_char(l_person_id)||' has multiple distribution code for one GRE';
4815: pay_core_utils.push_message(801,'PAY_EXCEPTION','A');
4816: pay_core_utils.push_token('description',substr(l_mesg,1,50));
4817: hr_utility.raise_error;
4818: end if;
4819:
4820: close c_get_1099_distribution_info;
4821: --
4884: end if; /* Special archiving for 1099R GRE */
4885:
4886: l_step := 94;
4887:
4888: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
4889: pay_balance_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
4890: pay_archive.balance_aa := l_aaid;
4891:
4892: l_step := 95;
4910: for ln_count in 1..4 loop
4911:
4912: l_step := 96;
4913:
4914: hr_utility.trace('l_user_entity_tab('||ln_count||')is '||l_user_entity_tab(ln_count));
4915: hr_utility.trace('l_user_entity_id_tab('||ln_count||')is '||l_user_entity_id_tab(ln_count));
4916:
4917: l_value_tab(ln_count) := nvl(pay_balance_pkg.get_value
4918: (p_defined_balance_id =>bal_db_item(l_user_entity_tab(ln_count)),
4911:
4912: l_step := 96;
4913:
4914: hr_utility.trace('l_user_entity_tab('||ln_count||')is '||l_user_entity_tab(ln_count));
4915: hr_utility.trace('l_user_entity_id_tab('||ln_count||')is '||l_user_entity_id_tab(ln_count));
4916:
4917: l_value_tab(ln_count) := nvl(pay_balance_pkg.get_value
4918: (p_defined_balance_id =>bal_db_item(l_user_entity_tab(ln_count)),
4919: p_assignment_action_id => l_aaid),0);
4918: (p_defined_balance_id =>bal_db_item(l_user_entity_tab(ln_count)),
4919: p_assignment_action_id => l_aaid),0);
4920:
4921: l_step := 97;
4922: hr_utility.trace('lv_value is '||l_value_tab(ln_count));
4923:
4924: ff_archive_api.create_archive_item(
4925: p_archive_item_id => l_archive_item_id,
4926: p_user_entity_id => l_user_entity_id_tab(ln_count),
4932: p_some_warning => l_some_warning,
4933: p_context_name1 => 'TAX_UNIT_ID',
4934: p_context1 => l_taxunitid);
4935:
4936: hr_utility.trace('l_archive_item_id is '||to_char(l_archive_item_id));
4937:
4938: l_step := 98;
4939:
4940: end loop;
4942: l_step := 99;
4943:
4944:
4945: lv_medicare_withheld := l_value_tab(4) ;
4946: hr_utility.trace('lv_medicare_withheld is '||lv_medicare_withheld);
4947:
4948: l_step := 100;
4949:
4950: lv_ss_withheld := l_value_tab(2) ;
4947:
4948: l_step := 100;
4949:
4950: lv_ss_withheld := l_value_tab(2) ;
4951: hr_utility.trace('lv_ss_withheld is '||lv_ss_withheld);
4952:
4953: l_step := 101;
4954:
4955: l_user_entity_id_tab.delete;
4959: l_context_val_tab.delete;
4960:
4961: if pay_us_archive.g_govt_employer = 'Y' then
4962:
4963: hr_utility.trace('Goverment employer is ');
4964: l_step := 102;
4965: l_value_tab(1) := pay_us_sqwl_udf.get_employment_code(
4966: p_medicare_wh => lv_medicare_withheld,
4967: p_ss_wh => lv_ss_withheld);
4964: l_step := 102;
4965: l_value_tab(1) := pay_us_sqwl_udf.get_employment_code(
4966: p_medicare_wh => lv_medicare_withheld,
4967: p_ss_wh => lv_ss_withheld);
4968: hr_utility.trace('lv_value is '||l_value_tab(1));
4969:
4970: l_step := 103;
4971: else
4972: l_step := 103.1;
4975:
4976: l_user_entity_id_tab(1) := get_user_entity_id('A_ASG_GRE_EMPLOYMENT_TYPE_CODE');
4977:
4978: l_step := 104;
4979: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
4980: l_seq_tab(1) := 1;
4981: --l_context_id_tab(1) := l_tax_unit_context_id;
4982: l_context_id_tab(1) := g_tax_unit_context_id;
4983: l_context_val_tab(1) := l_taxunitid;
5001: l_step := 107;
5002:
5003: -- Bug 4544792 : Removed the cursor c_get_disability_plan_eit_info
5004: open c_get_disability_plan_scl_info(l_asgid,l_taxunitid);
5005: hr_utility.trace('Opened c_get_disability_plan_scl_info ');
5006: fetch c_get_disability_plan_scl_info
5007: into l_disability_plan_id;
5008:
5009: /*
5007: into l_disability_plan_id;
5008:
5009: /*
5010: open c_get_disability_plan_eit_info(l_asgid);
5011: hr_utility.trace('Opened c_get_disability_plan_eit_info ');
5012: fetch c_get_disability_plan_eit_info
5013: into l_disability_plan_id;
5014: */
5015: if c_get_disability_plan_scl_info%NOTFOUND then
5016: l_disability_plan_id := null;
5017: end if;
5018: close c_get_disability_plan_scl_info;
5019:
5020: hr_utility.trace('l_disability_plan_id = '||l_disability_plan_id);
5021: l_user_entity_id_tab(1) := g_disability_plan_id;
5022: l_value_tab(1) := l_disability_plan_id;
5023:
5024: l_step := 108;
5021: l_user_entity_id_tab(1) := g_disability_plan_id;
5022: l_value_tab(1) := l_disability_plan_id;
5023:
5024: l_step := 108;
5025: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5026: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5027: l_seq_tab(1) := 1;
5028: --l_context_id_tab(1) := l_tax_unit_context_id;
5029: l_context_id_tab(1) := g_tax_unit_context_id;
5022: l_value_tab(1) := l_disability_plan_id;
5023:
5024: l_step := 108;
5025: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5026: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5027: l_seq_tab(1) := 1;
5028: --l_context_id_tab(1) := l_tax_unit_context_id;
5029: l_context_id_tab(1) := g_tax_unit_context_id;
5030: l_context_val_tab(1) := l_taxunitid;
5045: l_context_id_tab.delete;
5046: l_context_val_tab.delete;
5047:
5048: open c_get_flipp_scl_info(l_asgid,l_taxunitid);
5049: hr_utility.trace('Opened c_get_flipp_scl_info ');
5050: fetch c_get_flipp_scl_info
5051: into l_nj_flipp_id;
5052:
5053: if c_get_flipp_scl_info%NOTFOUND then
5053: if c_get_flipp_scl_info%NOTFOUND then
5054: l_nj_flipp_id := null;
5055: end if;
5056: close c_get_flipp_scl_info;
5057: hr_utility.trace('l_nj_flipp_id = '||l_nj_flipp_id);
5058: l_user_entity_id_tab(1) := g_nj_flipp_id;
5059: l_value_tab(1) := l_nj_flipp_id;
5060:
5061: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5057: hr_utility.trace('l_nj_flipp_id = '||l_nj_flipp_id);
5058: l_user_entity_id_tab(1) := g_nj_flipp_id;
5059: l_value_tab(1) := l_nj_flipp_id;
5060:
5061: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5062: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5063: l_seq_tab(1) := 1;
5064: --l_context_id_tab(1) := l_tax_unit_context_id;
5065: l_context_id_tab(1) := g_tax_unit_context_id;
5058: l_user_entity_id_tab(1) := g_nj_flipp_id;
5059: l_value_tab(1) := l_nj_flipp_id;
5060:
5061: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5062: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5063: l_seq_tab(1) := 1;
5064: --l_context_id_tab(1) := l_tax_unit_context_id;
5065: l_context_id_tab(1) := g_tax_unit_context_id;
5066: l_context_val_tab(1) := l_taxunitid;
5087: /* Bug# 4137906 - Time Info not required for the Date. So suppressing it */
5088: l_value_tab(1) := substr(fnd_date.date_to_canonical(sysdate),1,10);
5089:
5090: l_step := 110;
5091: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5092: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5093: l_seq_tab(1) := 1;
5094: --l_context_id_tab(1) := l_tax_unit_context_id;
5095: l_context_id_tab(1) := g_tax_unit_context_id;
5088: l_value_tab(1) := substr(fnd_date.date_to_canonical(sysdate),1,10);
5089:
5090: l_step := 110;
5091: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5092: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5093: l_seq_tab(1) := 1;
5094: --l_context_id_tab(1) := l_tax_unit_context_id;
5095: l_context_id_tab(1) := g_tax_unit_context_id;
5096: l_context_val_tab(1) := l_taxunitid;
5118: l_taxunitid);
5119: END IF;
5120:
5121: l_step := 112;
5122: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5123: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5124: l_seq_tab(1) := 1;
5125: l_context_id_tab(1) := g_tax_unit_context_id;
5126: l_context_val_tab(1) := l_taxunitid;
5119: END IF;
5120:
5121: l_step := 112;
5122: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5123: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5124: l_seq_tab(1) := 1;
5125: l_context_id_tab(1) := g_tax_unit_context_id;
5126: l_context_val_tab(1) := l_taxunitid;
5127:
5155: l_taxunitid);
5156: END IF;
5157:
5158: l_step := 115;
5159: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5160: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5161: l_seq_tab(1) := 1;
5162: l_context_id_tab(1) := g_tax_unit_context_id;
5163: l_context_val_tab(1) := l_taxunitid;
5156: END IF;
5157:
5158: l_step := 115;
5159: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5160: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5161: l_seq_tab(1) := 1;
5162: l_context_id_tab(1) := g_tax_unit_context_id;
5163: l_context_val_tab(1) := l_taxunitid;
5164:
5183: fetch c_get_business_group_id into ln_business_group_id;
5184: close c_get_business_group_id;
5185:
5186: open c_get_jurisdiction(l_person_id,l_taxunitid); --Midifed for the Bug 15944697
5187: hr_utility.trace('Opened c_get_jurisdiction ');
5188: loop
5189: fetch c_get_jurisdiction
5190: into l_psd_jurisdiction;
5191: exit when c_get_jurisdiction%notfound;
5193: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
5194:
5195: pay_balance_pkg.set_context ('JURISDICTION_CODE',l_psd_jurisdiction);
5196:
5197: hr_utility.trace('l_psd_jurisdiction '|| to_char(l_psd_jurisdiction));
5198:
5199: --Archiving this balance to get the value of PSD Withheld
5200:
5201: --check if PSD Withheld balances are <>0
5213:
5214: l_psd_balance_value := fnd_number.number_to_canonical(
5215: nvl(pay_balance_pkg.get_value(p_defined_balance_id => l_psd_def_bal_id
5216: ,p_assignment_action_id => l_aaid),0));
5217: hr_utility.trace('l_psd_bal_value in archive data: '||l_psd_balance_value);
5218:
5219: if (l_psd_balance_value<>0) then
5220:
5221: if pay_us_archive.ltr_psd_tax_bal.count > 0 then
5234: ln_bal_value := fnd_number.number_to_canonical(
5235: nvl(pay_balance_pkg.get_value(p_defined_balance_id => ln_psd_def_bal_id
5236: ,p_assignment_action_id => l_aaid),0));
5237:
5238: hr_utility.trace('ln_bal_value = '|| ln_bal_value);
5239:
5240: l_user_entity_id_tab(1) := pay_us_archive.ltr_psd_tax_bal(k).user_entity_id;
5241: l_value_tab(1) := ln_bal_value;
5242:
5240: l_user_entity_id_tab(1) := pay_us_archive.ltr_psd_tax_bal(k).user_entity_id;
5241: l_value_tab(1) := ln_bal_value;
5242:
5243: l_step := 117;
5244: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5245: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5246:
5247: l_seq_tab(1) := 1;
5248: l_context_id_tab(1) := g_tax_unit_context_id;
5241: l_value_tab(1) := ln_bal_value;
5242:
5243: l_step := 117;
5244: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5245: hr_utility.trace(' l_value_tab is '||l_value_tab(1));
5246:
5247: l_seq_tab(1) := 1;
5248: l_context_id_tab(1) := g_tax_unit_context_id;
5249: l_context_val_tab(1) := l_taxunitid;
5280: l_jd_done_tab.delete;
5281: l_jd_name_done_tab.delete;
5282:
5283:
5284: --hr_utility.trace_off;
5285:
5286: exception when others then
5287:
5288: raise_application_error(-20001,'Error in eoy_archive_data at step : '
5313:
5314: BEGIN
5315:
5316: l_step := 1;
5317: hr_utility.trace('In eoy_range_cursor');
5318:
5319: eoy_gre_range := 'SELECT distinct ASG.person_id
5320: FROM per_all_assignments_f ASG,
5321: pay_us_asg_reporting puar,
5336: into l_eoy_tax_unit_id, l_eoy_bg_id, l_start_date
5337: from pay_payroll_actions
5338: where payroll_action_id = pactid;
5339:
5340: hr_utility.trace('TUI is : ' || to_char(l_eoy_tax_unit_id));
5341: hr_utility.trace('BG is : ' || to_char(l_eoy_bg_id));
5342: hr_utility.trace('Start Date is : ' || to_char(l_start_date));
5343:
5344: /* for bug 2149544 */
5337: from pay_payroll_actions
5338: where payroll_action_id = pactid;
5339:
5340: hr_utility.trace('TUI is : ' || to_char(l_eoy_tax_unit_id));
5341: hr_utility.trace('BG is : ' || to_char(l_eoy_bg_id));
5342: hr_utility.trace('Start Date is : ' || to_char(l_start_date));
5343:
5344: /* for bug 2149544 */
5345: begin
5338: where payroll_action_id = pactid;
5339:
5340: hr_utility.trace('TUI is : ' || to_char(l_eoy_tax_unit_id));
5341: hr_utility.trace('BG is : ' || to_char(l_eoy_bg_id));
5342: hr_utility.trace('Start Date is : ' || to_char(l_start_date));
5343:
5344: /* for bug 2149544 */
5345: begin
5346: l_processed := 'Z';
5363: AND to_char(l_eoy_tax_unit_id) =
5364: substr(ltrim(rtrim( ppa1.legislative_parameters)),
5365: instr(ppa1.legislative_parameters,'TRANSFER_GRE=')+ length('TRANSFER_GRE='));
5366:
5367: hr_utility.trace('Value of l_processed is : ' || l_processed);
5368:
5369:
5370: if l_processed = 'X' then
5371: hr_utility.trace('Value of l_processed is : ' || l_processed);
5367: hr_utility.trace('Value of l_processed is : ' || l_processed);
5368:
5369:
5370: if l_processed = 'X' then
5371: hr_utility.trace('Value of l_processed is : ' || l_processed);
5372: l_mesg :='Error : GRE '||''''|| l_gre_name|| ''''||' has already been archived';
5373: pay_core_utils.push_message(801,'PAY_EXCEPTION_ERROR','P');
5374: pay_core_utils.push_token('description',l_mesg);
5375: hr_utility.raise_error;
5371: hr_utility.trace('Value of l_processed is : ' || l_processed);
5372: l_mesg :='Error : GRE '||''''|| l_gre_name|| ''''||' has already been archived';
5373: pay_core_utils.push_message(801,'PAY_EXCEPTION_ERROR','P');
5374: pay_core_utils.push_token('description',l_mesg);
5375: hr_utility.raise_error;
5376: end if;
5377:
5378: exception
5379: when no_data_found then
5382: when too_many_rows then
5383: l_mesg :='Error : GRE '||''''|| l_gre_name|| ''''||' has already been archived';
5384: pay_core_utils.push_message(801,'PAY_EXCEPTION_ERROR','P');
5385: pay_core_utils.push_token('description',l_mesg);
5386: hr_utility.raise_error;
5387:
5388: end;
5389:
5390:
5397: l_step := 5;
5398:
5399: if g_archive_flag = 'N' then
5400: l_step := 6;
5401: hr_utility.trace('eoy_range_cursor archiving employer data');
5402: eoy_archive_gre_data(p_payroll_action_id => pactid,
5403: p_tax_unit_id => l_eoy_tax_unit_id,
5404: p_jd_type => 'ALL',
5405: p_state_code => 'ALL');
5403: p_tax_unit_id => l_eoy_tax_unit_id,
5404: p_jd_type => 'ALL',
5405: p_state_code => 'ALL');
5406: l_step := 7;
5407: hr_utility.trace('eoy_range_cursor archiving employer data');
5408: end if;
5409: else
5410: l_step := 8;
5411: sqlstr := eoy_all_range;
5412: l_step := 9;
5413: end if;
5414:
5415: exception when others then
5416: hr_utility.trace('eoy_range_cursor at : '
5417: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
5418: END eoy_range_cursor;
5419:
5420:
5430:
5431: END eoy_deinit;
5432: --begin
5433:
5434: --hr_utility.trace_on(null,'pyusarch');
5435:
5436: END pay_us_archive;