1: package body pay_us_archive as
2: /* $Header: pyusarch.pkb 120.23.12020000.8 2012/12/11 19:12:18 skchalla ship $ */
3:
4: /*
5: Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
14: 22-Nov-2012 skchalla 115.115 14799833 Modified for the PSD PWL Yearly reporting.
15: 26-Sep-2012 skchalla 115.114 13724610 Modified c_get_jurisdiction cursor to pick up only
16: PA Jurisdiction codes.
17: 08-Sep-2012 skchalla 115.113 7456296 Archiving the Box 14 userdefined balances only if
18: pay_us_archive.ltr_w2_box_bal.count is > 0, to aovid
19: the yepp failure.
20: 06-Sep-2012 skchalla 115.112 7456296 Added new function get_w2_box_bal_info to handle the
21: W2 BOX balances archiving.
22: 06-Sep-2012 skchalla 115.111 13724610 Added new function get_psd_tax_info to handle the psd
635: or (fue.legislation_code is null and
636: fue.business_group_id is not null)) ;
637:
638: BEGIN
639: pay_us_archive.ltr_pr_balances.delete;
640:
641: BEGIN
642:
643: l_step := 19;
662: ln_count := 0 ;
663:
664: lv_balance_dimension := '_PER_GRE_YTD';
665:
666: pay_us_archive.ltr_pr_balances(1).balance_name := 'Territory Pension Annuity' ;
667: pay_us_archive.ltr_pr_balances(2).balance_name := 'Territory Reimb Expenses' ;
668: pay_us_archive.ltr_pr_balances(3).balance_name := 'Territory Taxable Comm' ;
669: pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
663:
664: lv_balance_dimension := '_PER_GRE_YTD';
665:
666: pay_us_archive.ltr_pr_balances(1).balance_name := 'Territory Pension Annuity' ;
667: pay_us_archive.ltr_pr_balances(2).balance_name := 'Territory Reimb Expenses' ;
668: pay_us_archive.ltr_pr_balances(3).balance_name := 'Territory Taxable Comm' ;
669: pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
671: pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
664: lv_balance_dimension := '_PER_GRE_YTD';
665:
666: pay_us_archive.ltr_pr_balances(1).balance_name := 'Territory Pension Annuity' ;
667: pay_us_archive.ltr_pr_balances(2).balance_name := 'Territory Reimb Expenses' ;
668: pay_us_archive.ltr_pr_balances(3).balance_name := 'Territory Taxable Comm' ;
669: pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
671: pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
672: /* Bug 13449377 - Code modified to archive following new balances. */
665:
666: pay_us_archive.ltr_pr_balances(1).balance_name := 'Territory Pension Annuity' ;
667: pay_us_archive.ltr_pr_balances(2).balance_name := 'Territory Reimb Expenses' ;
668: pay_us_archive.ltr_pr_balances(3).balance_name := 'Territory Taxable Comm' ;
669: pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
671: pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
672: /* Bug 13449377 - Code modified to archive following new balances. */
673: pay_us_archive.ltr_pr_balances(7).balance_name := 'PR W2 Charitable Contributions' ;
666: pay_us_archive.ltr_pr_balances(1).balance_name := 'Territory Pension Annuity' ;
667: pay_us_archive.ltr_pr_balances(2).balance_name := 'Territory Reimb Expenses' ;
668: pay_us_archive.ltr_pr_balances(3).balance_name := 'Territory Taxable Comm' ;
669: pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
671: pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
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' ;
667: pay_us_archive.ltr_pr_balances(2).balance_name := 'Territory Reimb Expenses' ;
668: pay_us_archive.ltr_pr_balances(3).balance_name := 'Territory Taxable Comm' ;
669: pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
671: pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
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:
669: pay_us_archive.ltr_pr_balances(4).balance_name := 'Territory Taxable Allow' ;
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
671: pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
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:
670: pay_us_archive.ltr_pr_balances(5).balance_name := 'Territory Taxable TIPS' ;
671: pay_us_archive.ltr_pr_balances(6).balance_name := 'Territory Retire Contrib' ;
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
680: /* Bug 13449377 - increased from 6 to 8 to accomodate the new archive balances for PuertoRico */
681: IF ln_count > 8 THEN
682: exit;
683: END IF;
684: open pay_us_payroll_utils.c_get_defined_balance_id(pay_us_archive.ltr_pr_balances(ln_count).balance_name,
685: lv_balance_dimension,
686: NULL);
687: -- open c_get_puerto_rico_bal;
688:
725: to_char(ln_defined_balance_id));
726:
727: l_step := 19.3;
728:
729: -- pay_us_archive.ltr_pr_balances(ln_count).balance_name := lv_balance_name ;
730: pay_us_archive.ltr_pr_balances(ln_count).defined_balance := ln_defined_balance_id ;
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;
726:
727: l_step := 19.3;
728:
729: -- pay_us_archive.ltr_pr_balances(ln_count).balance_name := lv_balance_name ;
730: pay_us_archive.ltr_pr_balances(ln_count).defined_balance := ln_defined_balance_id ;
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:
727: l_step := 19.3;
728:
729: -- pay_us_archive.ltr_pr_balances(ln_count).balance_name := lv_balance_name ;
730: pay_us_archive.ltr_pr_balances(ln_count).defined_balance := ln_defined_balance_id ;
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');
836: BEGIN
837:
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);
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' ;
882: pay_us_archive.ltr_1099_bal(4).balance_name := 'Total EE Contributions' ;
883: pay_us_archive.ltr_1099_bal(5).balance_name := 'Unrealized Net ER Sec Apprec' ;
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' ;
882: pay_us_archive.ltr_1099_bal(4).balance_name := 'Total EE Contributions' ;
883: pay_us_archive.ltr_1099_bal(5).balance_name := 'Unrealized Net ER Sec Apprec' ;
884: pay_us_archive.ltr_1099_bal(6).balance_name := 'Box 10 1099R IRR amount' ;
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' ;
882: pay_us_archive.ltr_1099_bal(4).balance_name := 'Total EE Contributions' ;
883: pay_us_archive.ltr_1099_bal(5).balance_name := 'Unrealized Net ER Sec Apprec' ;
884: pay_us_archive.ltr_1099_bal(6).balance_name := 'Box 10 1099R IRR amount' ;
885:
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' ;
882: pay_us_archive.ltr_1099_bal(4).balance_name := 'Total EE Contributions' ;
883: pay_us_archive.ltr_1099_bal(5).balance_name := 'Unrealized Net ER Sec Apprec' ;
884: pay_us_archive.ltr_1099_bal(6).balance_name := 'Box 10 1099R IRR amount' ;
885:
886:
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' ;
882: pay_us_archive.ltr_1099_bal(4).balance_name := 'Total EE Contributions' ;
883: pay_us_archive.ltr_1099_bal(5).balance_name := 'Unrealized Net ER Sec Apprec' ;
884: pay_us_archive.ltr_1099_bal(6).balance_name := 'Box 10 1099R IRR amount' ;
885:
886:
887: loop
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' ;
882: pay_us_archive.ltr_1099_bal(4).balance_name := 'Total EE Contributions' ;
883: pay_us_archive.ltr_1099_bal(5).balance_name := 'Unrealized Net ER Sec Apprec' ;
884: pay_us_archive.ltr_1099_bal(6).balance_name := 'Box 10 1099R IRR amount' ;
885:
886:
887: loop
888: ln_count := ln_count + 1;
888: ln_count := ln_count + 1;
889: IF ln_count > 6 THEN
890: exit;
891: END IF;
892: open pay_us_payroll_utils.c_get_defined_balance_id(pay_us_archive.ltr_1099_bal(ln_count).balance_name,
893: lv_balance_dimension,
894: NULL);
895: -- open c_get_1099r_bal;
896:
933: to_char(ln_defined_balance_id));
934:
935: l_step := 21.3;
936:
937: -- pay_us_archive.ltr_1099_bal(ln_count).balance_name := lv_balance_name ;
938: pay_us_archive.ltr_1099_bal(ln_count).defined_balance := ln_defined_balance_id ;
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:
934:
935: l_step := 21.3;
936:
937: -- pay_us_archive.ltr_1099_bal(ln_count).balance_name := lv_balance_name ;
938: pay_us_archive.ltr_1099_bal(ln_count).defined_balance := ln_defined_balance_id ;
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;
935: l_step := 21.3;
936:
937: -- pay_us_archive.ltr_1099_bal(ln_count).balance_name := lv_balance_name ;
938: pay_us_archive.ltr_1099_bal(ln_count).defined_balance := ln_defined_balance_id ;
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');
948:
949:
950: /* check whether these balances have been fed or not */
951:
952: for j in pay_us_archive.ltr_1099_bal.first ..
953: pay_us_archive.ltr_1099_bal.last loop
954:
955: lv_feed_flag := 'N';
956:
949:
950: /* check whether these balances have been fed or not */
951:
952: for j in pay_us_archive.ltr_1099_bal.first ..
953: pay_us_archive.ltr_1099_bal.last loop
954:
955: lv_feed_flag := 'N';
956:
957: l_step := 21.5;
954:
955: lv_feed_flag := 'N';
956:
957: l_step := 21.5;
958: open c_balance_feed_info(pay_us_archive.ltr_1099_bal(j).balance_name);
959:
960: fetch c_balance_feed_info into lv_feed_flag;
961:
962: l_step := 21.6;
964:
965: if c_balance_feed_info%NOTFOUND then
966: lv_feed_flag := 'N';
967: l_step := 21.7;
968: pay_us_archive.ltr_1099_bal(j).feed_info := 'N';
969:
970: else
971:
972: l_step := 21.8;
969:
970: else
971:
972: l_step := 21.8;
973: pay_us_archive.ltr_1099_bal(j).feed_info := lv_feed_flag;
974: end if;
975: close c_balance_feed_info ;
976:
977: l_step := 21.9;
1063: or (fue.legislation_code is null and
1064: fue.business_group_id is not null)) ;
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
1126: l_step := 17;
1127: ln_count := ln_count + 1;
1128:
1129:
1130: pay_us_archive.ltr_pre_tax_bal(ln_count).balance_name := lv_balance_name ;
1131: pay_us_archive.ltr_pre_tax_bal(ln_count).defined_balance := ln_defined_balance_id ;
1132: pay_us_archive.ltr_pre_tax_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
1133:
1134: end if;
1127: ln_count := ln_count + 1;
1128:
1129:
1130: pay_us_archive.ltr_pre_tax_bal(ln_count).balance_name := lv_balance_name ;
1131: pay_us_archive.ltr_pre_tax_bal(ln_count).defined_balance := ln_defined_balance_id ;
1132: pay_us_archive.ltr_pre_tax_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
1133:
1134: end if;
1135: close pay_us_payroll_utils.c_get_defined_balance_id;
1128:
1129:
1130: pay_us_archive.ltr_pre_tax_bal(ln_count).balance_name := lv_balance_name ;
1131: pay_us_archive.ltr_pre_tax_bal(ln_count).defined_balance := ln_defined_balance_id ;
1132: pay_us_archive.ltr_pre_tax_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
1133:
1134: end if;
1135: close pay_us_payroll_utils.c_get_defined_balance_id;
1136:
1200: and flv.enabled_flag = 'Y'
1201: and flv.language = 'US';
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
1267: l_step := 4;
1268: psd_count := psd_count + 1;
1269:
1270:
1271: pay_us_archive.ltr_psd_tax_bal(psd_count).balance_name := lv_balance_name ;
1272: pay_us_archive.ltr_psd_tax_bal(psd_count).defined_balance := ln_defined_balance_id ;
1273: pay_us_archive.ltr_psd_tax_bal(psd_count).user_entity_id := ln_arch_user_entity_id ;
1274:
1275: end if;
1268: psd_count := psd_count + 1;
1269:
1270:
1271: pay_us_archive.ltr_psd_tax_bal(psd_count).balance_name := lv_balance_name ;
1272: pay_us_archive.ltr_psd_tax_bal(psd_count).defined_balance := ln_defined_balance_id ;
1273: pay_us_archive.ltr_psd_tax_bal(psd_count).user_entity_id := ln_arch_user_entity_id ;
1274:
1275: end if;
1276: close pay_us_payroll_utils.c_get_defined_balance_id;
1269:
1270:
1271: pay_us_archive.ltr_psd_tax_bal(psd_count).balance_name := lv_balance_name ;
1272: pay_us_archive.ltr_psd_tax_bal(psd_count).defined_balance := ln_defined_balance_id ;
1273: pay_us_archive.ltr_psd_tax_bal(psd_count).user_entity_id := ln_arch_user_entity_id ;
1274:
1275: end if;
1276: close pay_us_payroll_utils.c_get_defined_balance_id;
1277:
1329: or (fue.legislation_code is null and
1330: fue.business_group_id is not null)) ;
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
1388: l_step := 4;
1389: ln_count := ln_count + 1;
1390:
1391:
1392: pay_us_archive.ltr_w2_box_bal(ln_count).balance_name := lv_balance_name ;
1393: pay_us_archive.ltr_w2_box_bal(ln_count).defined_balance := ln_defined_balance_id ;
1394: pay_us_archive.ltr_w2_box_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
1395:
1396: end if;
1389: ln_count := ln_count + 1;
1390:
1391:
1392: pay_us_archive.ltr_w2_box_bal(ln_count).balance_name := lv_balance_name ;
1393: pay_us_archive.ltr_w2_box_bal(ln_count).defined_balance := ln_defined_balance_id ;
1394: pay_us_archive.ltr_w2_box_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
1395:
1396: end if;
1397: close pay_us_payroll_utils.c_get_defined_balance_id;
1390:
1391:
1392: pay_us_archive.ltr_w2_box_bal(ln_count).balance_name := lv_balance_name ;
1393: pay_us_archive.ltr_w2_box_bal(ln_count).defined_balance := ln_defined_balance_id ;
1394: pay_us_archive.ltr_w2_box_bal(ln_count).user_entity_id := ln_arch_user_entity_id ;
1395:
1396: end if;
1397: close pay_us_payroll_utils.c_get_defined_balance_id;
1398:
1822: raise_application_error(-20001,'Error getting user_entity_id for DBI : '
1823: ||p_dbi_name||' - '||to_char(sqlcode) || '-' || sqlerrm);
1824: end get_user_entity_id;
1825:
1826: procedure create_archive (p_user_entity_id in pay_us_archive.number_data_type_table,
1827: p_context1 in number,
1828: p_value in pay_us_archive.char240_data_type_table,
1829: p_sequence in pay_us_archive.number_data_type_table,
1830: p_context in pay_us_archive.char240_data_type_table,
1824: end get_user_entity_id;
1825:
1826: procedure create_archive (p_user_entity_id in pay_us_archive.number_data_type_table,
1827: p_context1 in number,
1828: p_value in pay_us_archive.char240_data_type_table,
1829: p_sequence in pay_us_archive.number_data_type_table,
1830: p_context in pay_us_archive.char240_data_type_table,
1831: p_context_id in pay_us_archive.number_data_type_table,
1832: p_archive_level in varchar2 default 'EE') is
1825:
1826: procedure create_archive (p_user_entity_id in pay_us_archive.number_data_type_table,
1827: p_context1 in number,
1828: p_value in pay_us_archive.char240_data_type_table,
1829: p_sequence in pay_us_archive.number_data_type_table,
1830: p_context in pay_us_archive.char240_data_type_table,
1831: p_context_id in pay_us_archive.number_data_type_table,
1832: p_archive_level in varchar2 default 'EE') is
1833:
1826: procedure create_archive (p_user_entity_id in pay_us_archive.number_data_type_table,
1827: p_context1 in number,
1828: p_value in pay_us_archive.char240_data_type_table,
1829: p_sequence in pay_us_archive.number_data_type_table,
1830: p_context in pay_us_archive.char240_data_type_table,
1831: p_context_id in pay_us_archive.number_data_type_table,
1832: p_archive_level in varchar2 default 'EE') is
1833:
1834: l_step number := 0;
1827: p_context1 in number,
1828: p_value in pay_us_archive.char240_data_type_table,
1829: p_sequence in pay_us_archive.number_data_type_table,
1830: p_context in pay_us_archive.char240_data_type_table,
1831: p_context_id in pay_us_archive.number_data_type_table,
1832: p_archive_level in varchar2 default 'EE') is
1833:
1834: l_step number := 0;
1835: l_tax_context_id number := 0;
2147: p_jd_type in varchar2 default 'ALL',
2148: p_state_code in varchar2 default 'ALL')
2149: is
2150:
2151: l_user_entity_id_tab pay_us_archive.number_data_type_table;
2152: l_tax_context_id number;
2153: l_jursd_context_id number;
2154: l_value1 varchar2(240);
2155: l_value2 varchar2(240);
2170: l_value17 varchar2(240);
2171: l_value18 varchar2(240);
2172: l_value19 varchar2(240);
2173: l_value20 varchar2(240);
2174: l_value_tab pay_us_archive.char240_data_type_table;
2175: l_sit_uid number;
2176: l_sui_uid number;
2177: l_fips_uid number;
2178: l_seq_tab pay_us_archive.number_data_type_table;
2174: l_value_tab pay_us_archive.char240_data_type_table;
2175: l_sit_uid number;
2176: l_sui_uid number;
2177: l_fips_uid number;
2178: l_seq_tab pay_us_archive.number_data_type_table;
2179: l_context_id_tab pay_us_archive.number_data_type_table;
2180: l_context_val_tab pay_us_archive.char240_data_type_table;
2181: l_arch_gre_step number := 0;
2182: l_archive_level varchar2(240);
2175: l_sit_uid number;
2176: l_sui_uid number;
2177: l_fips_uid number;
2178: l_seq_tab pay_us_archive.number_data_type_table;
2179: l_context_id_tab pay_us_archive.number_data_type_table;
2180: l_context_val_tab pay_us_archive.char240_data_type_table;
2181: l_arch_gre_step number := 0;
2182: l_archive_level varchar2(240);
2183:
2176: l_sui_uid number;
2177: l_fips_uid number;
2178: l_seq_tab pay_us_archive.number_data_type_table;
2179: l_context_id_tab pay_us_archive.number_data_type_table;
2180: l_context_val_tab pay_us_archive.char240_data_type_table;
2181: l_arch_gre_step number := 0;
2182: l_archive_level varchar2(240);
2183:
2184: ld_end_date DATE;
3222:
3223: /* Get the tax unit id and set it up as the context */
3224: l_step := 7;
3225: /*
3226: pay_us_archive.g_report_type := pay_us_archive.get_report_type(p_payroll_action_id);
3227: if g_report_type <> 'W2C_PRE_PROCESS' then
3228:
3229: select to_number(substr(legislative_parameters,
3230: instr(legislative_parameters,'TRANSFER_GRE=')+ length('TRANSFER_GRE='))),
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) ;
3293: pay_us_archive.g_pre_tax_info := pay_us_archive.get_pre_tax_info(l_tax_unit_id,
3294: ln_business_group_id) ;
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) ;
3293: pay_us_archive.g_pre_tax_info := pay_us_archive.get_pre_tax_info(l_tax_unit_id,
3294: ln_business_group_id) ;
3295: --Added below code to intialize the pl sql tables for the psd balances.--Bug 13724610
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) ;
3293: pay_us_archive.g_pre_tax_info := pay_us_archive.get_pre_tax_info(l_tax_unit_id,
3294: ln_business_group_id) ;
3295: --Added below code to intialize the pl sql tables for the psd balances.--Bug 13724610
3296: pay_us_archive.g_psd_tax_info := pay_us_archive.get_psd_tax_info(l_tax_unit_id,
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) ;
3293: pay_us_archive.g_pre_tax_info := pay_us_archive.get_pre_tax_info(l_tax_unit_id,
3294: ln_business_group_id) ;
3295: --Added below code to intialize the pl sql tables for the psd balances.--Bug 13724610
3296: pay_us_archive.g_psd_tax_info := pay_us_archive.get_psd_tax_info(l_tax_unit_id,
3297: ln_business_group_id) ;
3292: pay_us_archive.g_1099R_transmitter_code := pay_us_archive.get_1099r_info(l_tax_unit_id) ;
3293: pay_us_archive.g_pre_tax_info := pay_us_archive.get_pre_tax_info(l_tax_unit_id,
3294: ln_business_group_id) ;
3295: --Added below code to intialize the pl sql tables for the psd balances.--Bug 13724610
3296: pay_us_archive.g_psd_tax_info := pay_us_archive.get_psd_tax_info(l_tax_unit_id,
3297: ln_business_group_id) ;
3298: --Added below code to intialize the pl sql tables for the W2 box balances.--Bug 7456296
3299: pay_us_archive.g_w2_box_bal_info := pay_us_archive.get_w2_box_bal_info(l_tax_unit_id,
3300: ln_business_group_id) ;
3295: --Added below code to intialize the pl sql tables for the psd balances.--Bug 13724610
3296: pay_us_archive.g_psd_tax_info := pay_us_archive.get_psd_tax_info(l_tax_unit_id,
3297: ln_business_group_id) ;
3298: --Added below code to intialize the pl sql tables for the W2 box balances.--Bug 7456296
3299: pay_us_archive.g_w2_box_bal_info := pay_us_archive.get_w2_box_bal_info(l_tax_unit_id,
3300: ln_business_group_id) ;
3301:
3302: if pay_us_sqwl_udf.chk_govt_employer(p_tax_unit_id =>l_tax_unit_id) then
3303: pay_us_archive.g_govt_employer := 'Y';
3299: pay_us_archive.g_w2_box_bal_info := pay_us_archive.get_w2_box_bal_info(l_tax_unit_id,
3300: ln_business_group_id) ;
3301:
3302: if pay_us_sqwl_udf.chk_govt_employer(p_tax_unit_id =>l_tax_unit_id) then
3303: pay_us_archive.g_govt_employer := 'Y';
3304: else
3305: pay_us_archive.g_govt_employer := 'N';
3306: end if;
3307:
3301:
3302: if pay_us_sqwl_udf.chk_govt_employer(p_tax_unit_id =>l_tax_unit_id) then
3303: pay_us_archive.g_govt_employer := 'Y';
3304: else
3305: pay_us_archive.g_govt_employer := 'N';
3306: end if;
3307:
3308:
3309: l_step := 13;
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',
3469: l_taxunitid));
3470:
3471:
3468: 'A_VIEW_ONLINE_W2',
3469: l_taxunitid));
3470:
3471:
3472: l_add_archive := pay_us_archive_util.get_archive_value(p_assactid,
3473: 'A_ADD_ARCHIVE',
3474: l_taxunitid);
3475:
3476:
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',
3513: l_taxunitid));
3514:
3515:
3512: 'A_HR_ORIG_1099R_BEF',
3513: l_taxunitid));
3514:
3515:
3516: l_add_archive := pay_us_archive_util.get_archive_value(p_assactid,
3517: 'A_ADD_ARCHIVE',
3518: l_taxunitid);
3519:
3520:
3567: l_county_code varchar2(3);
3568: l_city_code varchar2(4);
3569: l_person_id per_people_f.person_id%type;
3570: l_jursd_context_id ff_contexts.context_id%type;
3571: l_user_entity_id_tab pay_us_archive.number_data_type_table;
3572: l_user_entity_tab pay_us_archive.char240_data_type_table;
3573: l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3568: l_city_code varchar2(4);
3569: l_person_id per_people_f.person_id%type;
3570: l_jursd_context_id ff_contexts.context_id%type;
3571: l_user_entity_id_tab pay_us_archive.number_data_type_table;
3572: l_user_entity_tab pay_us_archive.char240_data_type_table;
3573: l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3569: l_person_id per_people_f.person_id%type;
3570: l_jursd_context_id ff_contexts.context_id%type;
3571: l_user_entity_id_tab pay_us_archive.number_data_type_table;
3572: l_user_entity_tab pay_us_archive.char240_data_type_table;
3573: l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3570: l_jursd_context_id ff_contexts.context_id%type;
3571: l_user_entity_id_tab pay_us_archive.number_data_type_table;
3572: l_user_entity_tab pay_us_archive.char240_data_type_table;
3573: l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3578: l_context_val_tab pay_us_archive.char240_data_type_table;
3571: l_user_entity_id_tab pay_us_archive.number_data_type_table;
3572: l_user_entity_tab pay_us_archive.char240_data_type_table;
3573: l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3578: l_context_val_tab pay_us_archive.char240_data_type_table;
3579: --l_jd_done_tab pay_us_archive.char240_data_type_table;
3572: l_user_entity_tab pay_us_archive.char240_data_type_table;
3573: l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3578: l_context_val_tab pay_us_archive.char240_data_type_table;
3579: --l_jd_done_tab pay_us_archive.char240_data_type_table;
3580: --l_jd_name_done_tab pay_us_archive.char240_data_type_table;
3573: l_defined_balance_id_tab pay_us_archive.number_data_type_table;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3578: l_context_val_tab pay_us_archive.char240_data_type_table;
3579: --l_jd_done_tab pay_us_archive.char240_data_type_table;
3580: --l_jd_name_done_tab pay_us_archive.char240_data_type_table;
3581: l_chunk number;
3574: l_value_tab pay_us_archive.char240_data_type_table;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3578: l_context_val_tab pay_us_archive.char240_data_type_table;
3579: --l_jd_done_tab pay_us_archive.char240_data_type_table;
3580: --l_jd_name_done_tab pay_us_archive.char240_data_type_table;
3581: l_chunk number;
3582: l_payroll_action_id number;
3575: l_balance_feed_tab pay_us_archive.char240_data_type_table;
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3578: l_context_val_tab pay_us_archive.char240_data_type_table;
3579: --l_jd_done_tab pay_us_archive.char240_data_type_table;
3580: --l_jd_name_done_tab pay_us_archive.char240_data_type_table;
3581: l_chunk number;
3582: l_payroll_action_id number;
3583: l_chk_state_archive varchar2(1);
3576: l_seq_tab pay_us_archive.number_data_type_table;
3577: l_context_id_tab pay_us_archive.number_data_type_table;
3578: l_context_val_tab pay_us_archive.char240_data_type_table;
3579: --l_jd_done_tab pay_us_archive.char240_data_type_table;
3580: --l_jd_name_done_tab pay_us_archive.char240_data_type_table;
3581: l_chunk number;
3582: l_payroll_action_id number;
3583: l_chk_state_archive varchar2(1);
3584: l_chk_county_archive varchar2(1);
4548: l_jd_done_tab.delete;
4549:
4550: l_step := 52;
4551:
4552: for ln_count in pay_us_archive.ltr_pre_tax_bal.first ..
4553: pay_us_archive.ltr_pre_tax_bal.last loop
4554:
4555: l_step := 53;
4556:
4549:
4550: l_step := 52;
4551:
4552: for ln_count in pay_us_archive.ltr_pre_tax_bal.first ..
4553: pay_us_archive.ltr_pre_tax_bal.last loop
4554:
4555: l_step := 53;
4556:
4557: lv_value := nvl(pay_balance_pkg.get_value
4554:
4555: l_step := 53;
4556:
4557: lv_value := nvl(pay_balance_pkg.get_value
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);
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,
4567: p_archive_value => lv_value,
4568: p_archive_type => '',
4569: p_action_id => p_assactid,
4570: p_legislation_code => 'US',
4578: end loop;
4579:
4580: --Bug 7456296 added the below code to archive the w2 box balances
4581:
4582: if pay_us_archive.ltr_w2_box_bal.count > 0 then
4583:
4584: for ln_count in pay_us_archive.ltr_w2_box_bal.first ..
4585: pay_us_archive.ltr_w2_box_bal.last loop
4586:
4580: --Bug 7456296 added the below code to archive the w2 box balances
4581:
4582: if pay_us_archive.ltr_w2_box_bal.count > 0 then
4583:
4584: for ln_count in pay_us_archive.ltr_w2_box_bal.first ..
4585: pay_us_archive.ltr_w2_box_bal.last loop
4586:
4587: l_step := 55.1;
4588:
4581:
4582: if pay_us_archive.ltr_w2_box_bal.count > 0 then
4583:
4584: for ln_count in pay_us_archive.ltr_w2_box_bal.first ..
4585: pay_us_archive.ltr_w2_box_bal.last loop
4586:
4587: l_step := 55.1;
4588:
4589: lv_value := nvl(pay_balance_pkg.get_value
4586:
4587: l_step := 55.1;
4588:
4589: lv_value := nvl(pay_balance_pkg.get_value
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);
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,
4599: p_archive_value => lv_value,
4600: p_archive_type => '',
4601: p_action_id => p_assactid,
4602: p_legislation_code => 'US',
4675: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4676:
4677: l_step := 58;
4678:
4679: for k in pay_us_archive.ltr_pr_balances.first ..
4680: pay_us_archive.ltr_pr_balances.last loop
4681:
4682: l_index := l_user_entity_id_tab.count + 1;
4683:
4676:
4677: l_step := 58;
4678:
4679: for k in pay_us_archive.ltr_pr_balances.first ..
4680: pay_us_archive.ltr_pr_balances.last loop
4681:
4682: l_index := l_user_entity_id_tab.count + 1;
4683:
4684: l_user_entity_id_tab(l_index) := pay_us_archive.ltr_pr_balances(k).user_entity_id;
4680: pay_us_archive.ltr_pr_balances.last loop
4681:
4682: l_index := l_user_entity_id_tab.count + 1;
4683:
4684: l_user_entity_id_tab(l_index) := pay_us_archive.ltr_pr_balances(k).user_entity_id;
4685: l_defined_balance_id_tab(l_index) := pay_us_archive.ltr_pr_balances(k).defined_balance;
4686: l_value_tab(l_index) := nvl(pay_balance_pkg.get_value
4687: (p_defined_balance_id =>l_defined_balance_id_tab(l_index) ,
4688: p_assignment_action_id => l_aaid),0);
4681:
4682: l_index := l_user_entity_id_tab.count + 1;
4683:
4684: l_user_entity_id_tab(l_index) := pay_us_archive.ltr_pr_balances(k).user_entity_id;
4685: l_defined_balance_id_tab(l_index) := pay_us_archive.ltr_pr_balances(k).defined_balance;
4686: l_value_tab(l_index) := nvl(pay_balance_pkg.get_value
4687: (p_defined_balance_id =>l_defined_balance_id_tab(l_index) ,
4688: p_assignment_action_id => l_aaid),0);
4689:
4778: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
4779:
4780: l_step := 77;
4781:
4782: for m in pay_us_archive.ltr_1099_bal.first ..
4783: pay_us_archive.ltr_1099_bal.last loop
4784:
4785: l_index := l_user_entity_id_tab.count + 1;
4786:
4779:
4780: l_step := 77;
4781:
4782: for m in pay_us_archive.ltr_1099_bal.first ..
4783: pay_us_archive.ltr_1099_bal.last loop
4784:
4785: l_index := l_user_entity_id_tab.count + 1;
4786:
4787: l_user_entity_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).user_entity_id;
4783: pay_us_archive.ltr_1099_bal.last loop
4784:
4785: l_index := l_user_entity_id_tab.count + 1;
4786:
4787: l_user_entity_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).user_entity_id;
4788: l_defined_balance_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).defined_balance;
4789: l_balance_feed_tab(l_index) := pay_us_archive.ltr_1099_bal(m).feed_info;
4790:
4791: if l_balance_feed_tab(l_index) = 'Y' then
4784:
4785: l_index := l_user_entity_id_tab.count + 1;
4786:
4787: l_user_entity_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).user_entity_id;
4788: l_defined_balance_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).defined_balance;
4789: l_balance_feed_tab(l_index) := pay_us_archive.ltr_1099_bal(m).feed_info;
4790:
4791: if l_balance_feed_tab(l_index) = 'Y' then
4792:
4785: l_index := l_user_entity_id_tab.count + 1;
4786:
4787: l_user_entity_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).user_entity_id;
4788: l_defined_balance_id_tab(l_index) := pay_us_archive.ltr_1099_bal(m).defined_balance;
4789: l_balance_feed_tab(l_index) := pay_us_archive.ltr_1099_bal(m).feed_info;
4790:
4791: if l_balance_feed_tab(l_index) = 'Y' then
4792:
4793: l_value_tab(l_index) := nvl(pay_balance_pkg.get_value
4825: if c_get_first_yr_roth_contrib%NOTFOUND then
4826: l_first_yr_roth_contrib := NULL;
4827: end if;
4828: --increased l_user_entity_id_tab count by 1 for the below balance as a new balance is added for
4829: --the bug 11906843. The below code is written with an assumption that pay_us_archive.ltr_1099_bal has 5 balances.
4830: l_step := 83;
4831:
4832: l_user_entity_id_tab(7) := g_taxable_amount_unknown;
4833:
4957: l_seq_tab.delete;
4958: l_context_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(
5105:
5106: l_step := 111;
5107:
5108: l_user_entity_id_tab(1) := g_w2_corrected;
5109: l_add_archive :=pay_us_archive_util.get_archive_value(p_assactid,
5110: 'A_ADD_ARCHIVE',
5111: l_taxunitid);
5112:
5113: IF l_add_archive = 'Y' THEN
5142: /* Added for #8239671 Start */
5143: l_step := 114;
5144:
5145: l_user_entity_id_tab(1) := g_1099r_corrected;
5146: l_add_archive :=pay_us_archive_util.get_archive_value(p_assactid,
5147: 'A_ADD_ARCHIVE',
5148: l_taxunitid);
5149:
5150: IF l_add_archive = 'Y' THEN
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
5222:
5223:
5224: for k in pay_us_archive.ltr_psd_tax_bal.first..
5225: pay_us_archive.ltr_psd_tax_bal.last loop
5220:
5221: if pay_us_archive.ltr_psd_tax_bal.count > 0 then
5222:
5223:
5224: for k in pay_us_archive.ltr_psd_tax_bal.first..
5225: pay_us_archive.ltr_psd_tax_bal.last loop
5226:
5227: lv_balance_name :=
5228: pay_us_archive.ltr_psd_tax_bal(k).balance_name;
5221: if pay_us_archive.ltr_psd_tax_bal.count > 0 then
5222:
5223:
5224: for k in pay_us_archive.ltr_psd_tax_bal.first..
5225: pay_us_archive.ltr_psd_tax_bal.last loop
5226:
5227: lv_balance_name :=
5228: pay_us_archive.ltr_psd_tax_bal(k).balance_name;
5229:
5224: for k in pay_us_archive.ltr_psd_tax_bal.first..
5225: pay_us_archive.ltr_psd_tax_bal.last loop
5226:
5227: lv_balance_name :=
5228: pay_us_archive.ltr_psd_tax_bal(k).balance_name;
5229:
5230: ln_psd_def_bal_id :=
5231: pay_us_archive.ltr_psd_tax_bal(k).defined_balance;
5232:
5227: lv_balance_name :=
5228: pay_us_archive.ltr_psd_tax_bal(k).balance_name;
5229:
5230: ln_psd_def_bal_id :=
5231: pay_us_archive.ltr_psd_tax_bal(k).defined_balance;
5232:
5233:
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:
5243: l_step := 117;
5244: hr_utility.trace('l_user_entity_id_tab is '||l_user_entity_id_tab(1));
5259: p_sequence => l_seq_tab,
5260: p_context => l_context_val_tab,
5261: p_context_id => l_context_id_tab);
5262: end loop;
5263: end if; --pay_us_archive.ltr_psd_tax_bal.count
5264: end if;--l_psd_balance_value
5265: end loop;
5266: close c_get_jurisdiction;
5267:
5432: --begin
5433:
5434: --hr_utility.trace_on(null,'pyusarch');
5435:
5436: END pay_us_archive;