[Home] [Help]
337: where balance_name = p_jd_bal_name
338: and legislation_code = 'US';
339: begin
340: l_proc := g_proc||'.get_template_id';
341: hr_utility.set_location('Entering: '||l_proc, 5);
342: --
343: for temp_rec in c_bal loop
344: l_bal_id := temp_rec.balance_type_id;
345: end loop;
342: --
343: for temp_rec in c_bal loop
344: l_bal_id := temp_rec.balance_type_id;
345: end loop;
346: hr_utility.set_location('Leaving: '||l_proc, 10);
347: return l_bal_id;
348: --
349: end;
350: -- =============================================================================
365: and business_group_id = p_bg_id ;
366:
367: begin
368: l_proc := g_proc||'.get_template_id';
369: hr_utility.set_location('Entering: '||l_proc, 5);
370:
371: for temp_rec in c_bal loop
372: l_input_value_id := temp_rec.input_value_id;
373: end loop;
370:
371: for temp_rec in c_bal loop
372: l_input_value_id := temp_rec.input_value_id;
373: end loop;
374: hr_utility.set_location('Leaving: '||l_proc, 10);
375: return l_input_value_id;
376:
377: end;
378: -- =============================================================================
399: --
400: begin
401: --
402: l_proc := g_proc||'.get_template_id';
403: hr_utility.set_location('Entering: '||l_proc, 10);
404:
405: l_ele_category := p_ele_category;
406: if p_ele_srs_plan_type = 'C' then
407: l_ele_category := 'DCP';
473: l_iter_priority := 10;
474:
475: end if;
476: --
477: hr_utility.set_location(l_proc, 30);
478: --
479: for c4_rec in c4 loop
480: l_template_id := c4_rec.template_id;
481: l_ele_template_priority := c4_rec.base_processing_priority;
490: l_ele_template_priority := l_ele_template_priority + 50;
491:
492: end if;
493:
494: hr_utility.set_location('Leaving: '||l_proc, 50);
495: --
496: return l_template_id;
497: --
498: end get_template_id;
570: p_ele_name||' Roth Eligible Comp',
571: p_ele_name||' AT Eligible Comp');
572: begin
573: l_proc := g_proc||'.create_eligible_comp_bal_feeds';
574: hr_utility.set_location('Entering: '||l_proc, 5);
575: for c1_rec in c1_get_reg_earn_feeds loop
576: for c2_rec in c2_balance_type loop
577: pay_balance_feeds_f_pkg.insert_row
578: (x_rowid => l_row_id,
590: l_balance_feed_id := null;
591: l_row_id := null;
592: end loop;
593: end loop;
594: hr_utility.set_location('Leaving: '||l_proc, 10);
595: end create_eligible_comp_bal_feeds;
596: --
597: -- =============================================================================
598: -- get_object_id:
621: and ptco.shadow_object_id = psbt.balance_type_id;
622: --
623: begin
624: l_proc := g_proc||'.get_object_id';
625: hr_utility.set_location('Entering: '||l_proc, 10);
626: --
627: if p_object_type = 'ELE' then
628: for c2_rec in c2 (p_object_name) loop
629: l_object_id := c2_rec.element_type_id; -- element id
633: l_object_id := c3_rec.core_object_id; -- balance id
634: end loop;
635: end if;
636: --
637: hr_utility.set_location('Leaving: '||l_proc, 50);
638: --
639: return l_object_id;
640: --
641: end get_object_id;
659:
660: begin
661:
662: l_proc := g_proc||'.get_elgicomp_ipv';
663: hr_utility.set_location('Entering: '||l_proc, 5);
664:
665: for temp_rec in c_bal loop
666: l_input_value_id := temp_rec.input_value_id;
667: end loop;
664:
665: for temp_rec in c_bal loop
666: l_input_value_id := temp_rec.input_value_id;
667: end loop;
668: hr_utility.set_location('Leaving: '||l_proc, 10);
669: return l_input_value_id;
670:
671: end get_elgicomp_ipv;
672: -- =============================================================================
678: l_eligicmp_ipv_id number(9);
679: --
680: begin
681: l_proc := g_proc||'.get_element_and_bal_ids';
682: hr_utility.set_location('Entering: '||l_proc, 10);
683: --
684: l_base_element_type_id := get_object_id('ELE',p_ele_name);
685: -- Added to check the ER match element is exist for base element.
686: -- If not, then set the default_value to S_EE and lookup_type
909: end if;
910:
911: end if; --if nvl(p_roth_contribution,'N')
912: --
913: hr_utility.set_location('Leaving: '||l_proc, 100);
914: --
915: end get_element_and_bal_ids;
916: --
917: -- =============================================================================
924: l_proc varchar2(160);
925:
926: begin
927: l_proc := g_proc||'.insert_iterative_rules';
928: hr_utility.set_location('Entering: '||l_proc, 5);
929:
930: insert into pay_iterative_rules_f
931: (iterative_rule_id
932: ,element_type_id
962: ,p_ele_eff_start_date
963: ,-1
964: ,-1
965: );
966: hr_utility.set_location('Leaving: '||l_proc, 10);
967: end insert_iterative_rules;
968:
969: -- =============================================================================
970: -- Main Function
969: -- =============================================================================
970: -- Main Function
971: -- =============================================================================
972: begin
973: --hr_utility.trace_on(null,'tmehra');
974: l_proc := g_proc||'.create_user_init_template';
975: hr_utility.set_location('Entering : '||l_proc, 10);
976: --
977: -- Initialize local variables
971: -- =============================================================================
972: begin
973: --hr_utility.trace_on(null,'tmehra');
974: l_proc := g_proc||'.create_user_init_template';
975: hr_utility.set_location('Entering : '||l_proc, 10);
976: --
977: -- Initialize local variables
978: --
979: l_at_er_exclusion_rule := 'N';
995: -- Set session date
996: --
997: pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
998: --
999: hr_utility.set_location(l_proc, 20);
1000: --
1001: -- Get Source Template ID
1002: --
1003: l_source_template_id := get_template_id
1004: (p_legislation_code => 'US'
1005: ,p_ele_category => p_ele_category
1006: ,p_ele_srs_plan_type => NVL(p_ele_srs_plan_type,'NONE')
1007: );
1008: hr_utility.set_location(l_proc, 30);
1009: --
1010: /*
1011: ==============================================================================
1012: create the user structure the Configuration Flex segments for the
1215: ,p_template_id => l_template_id
1216: ,p_object_version_number => l_object_version_number
1217: );
1218: --
1219: hr_utility.set_location(l_proc, 80);
1220: -- =========================================================================
1221: -- Create Global Values: For 401k ER Match percentage and limit
1222: -- =========================================================================
1223: open c_global;
1259: else
1260: close c_global1;
1261: end if;
1262:
1263: hr_utility.set_location(l_proc, 85);
1264: -- =========================================================================
1265: -- Update Shadow Structure: Get Element Type id and update user-specified
1266: -- Classification,Category, Processing Type and Standard Link on Base Element
1267: -- =========================================================================
1302: ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1303: ,p_element_information9 => 'D'
1304: ,p_object_version_number => l_ele_obj_ver_number
1305: );
1306: hr_utility.set_location(l_proc, 90);
1307: -- SRS: State Retirement Systems
1308: -- Update Reporting Name and other details on the shadow tables.
1309: --
1310: if (p_ele_category in ('DCP', 'DBP')) or
1405: ,p_object_version_number => l_bb_ele_obj_ver_number
1406: );
1407: end loop;
1408: end loop;
1409: hr_utility.set_location(l_proc, 95);
1410: end if; -- p_ele_srs_buy_back = y
1411: end if; -- p_ele_srs_plan_type = Y
1412:
1413: hr_utility.set_location(l_proc, 96);
1409: hr_utility.set_location(l_proc, 95);
1410: end if; -- p_ele_srs_buy_back = y
1411: end if; -- p_ele_srs_plan_type = Y
1412:
1413: hr_utility.set_location(l_proc, 96);
1414:
1415: -- Update Reporting Name and other details on the shadow tables.
1416: -- for Catch-Up element if option is selected
1417: if nvl(p_catchup_processing,'NONE') <> 'NONE' then
1471: ,p_object_version_number => l_cu_ele_obj_ver_number
1472: );
1473: end loop;
1474: end loop;
1475: hr_utility.set_location(l_proc, 97);
1476: end if; -- If Catch-Up <> NONE
1477:
1478: -- Update Taxable by JD element with relevant Data.
1479: -- Modified for Garnishment rewrite
1503: ,p_object_version_number => l_jd_ele_obj_ver_number
1504: );
1505: end if;
1506:
1507: hr_utility.set_location(l_proc, 98);
1508:
1509: --
1510: -- Update user-specified Classification on Special Features Element.
1511: -- ref. bug 1559726.
1530: ,p_object_version_number => l_sf_ele_obj_ver_number
1531: );
1532: end if;
1533:
1534: hr_utility.set_location(l_proc, 99);
1535:
1536: --
1537: -- Update user-specified Classification Special Inputs if it exists.
1538: --
1558: ,p_object_version_number => l_si_ele_obj_ver_number
1559: );
1560: end if;
1561:
1562: hr_utility.set_location(l_proc, 100);
1563:
1564: -- Added for Garnishment rewrite
1565: if p_ele_classification = l_inv_ded then
1566: for c1_rec in c1 ( p_ele_name||' Calculator' ) loop
1603: ,p_object_version_number => l_fee_ele_obj_ver_number
1604: );
1605: end if;
1606:
1607: hr_utility.set_location(l_proc, 101);
1608:
1609: --
1610: -- Update user-specified details on all After-Tax Elements
1611: --
1626: );
1627: end loop;
1628: end if;
1629:
1630: hr_utility.set_location(l_proc, 102);
1631:
1632: if p_after_tax_component = 'Y' then
1633:
1634: for c1_rec in c1 ( p_ele_name||' AT' ) loop
1708: ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1709: ,p_object_version_number => l_object_version_number
1710: );
1711: end if;
1712: hr_utility.set_location(l_proc, 110);
1713: --
1714: -- Update shadow element if Roth Contribution option is selected.
1715: --
1716: if p_roth_contribution = 'Y' then
1731: l_relative_processing_priority := 1425 ;
1732: l_ele_information1 := 'R457' ;
1733: END IF ;
1734:
1735: hr_utility.set_location(l_proc, 111);
1736:
1737: pay_shadow_element_api.update_shadow_element
1738: (p_validate => false
1739: ,p_effective_date => p_ele_eff_start_date
1751: ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1752: ,p_object_version_number => l_object_version_number
1753: );
1754:
1755: hr_utility.set_location(l_proc, 112);
1756: --
1757: -- Update the Roth AT ER element
1758: --
1759: l_temp_var(1).sub_name := ' Roth ER';
1774: ,p_object_version_number => l_object_version_number
1775: );
1776:
1777: end loop;
1778: hr_utility.set_location(l_proc, 113);
1779: --
1780: -- Update the Special Inputs element for Roth 401k
1781: --
1782: if p_ele_processing_type = 'R' then
1806: ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1807: ,p_object_version_number => l_object_version_number
1808: );
1809: end if;
1810: hr_utility.set_location(l_proc, 114);
1811: --
1812: -- Update the Special Features element for Roth 401k
1813: --
1814: for c1_rec in c1 ( p_ele_name||' Roth SF' )
1838: ,p_element_information2 => nvl(p_ele_partial_deduction,hr_api.g_varchar2)
1839: ,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
1840: ,p_object_version_number => l_object_version_number
1841: );
1842: hr_utility.set_location(l_proc, 115);
1843: end if; -- if p_roth_contribution = 'Y'
1844:
1845: hr_utility.set_location(l_proc, 120);
1846:
1841: );
1842: hr_utility.set_location(l_proc, 115);
1843: end if; -- if p_roth_contribution = 'Y'
1844:
1845: hr_utility.set_location(l_proc, 120);
1846:
1847: -- ========================================================================
1848: -- Generate Core Objects
1849: -- ========================================================================
1853: ,p_hr_only => false
1854: ,p_hr_to_payroll => false
1855: ,p_template_id => l_template_id);
1856:
1857: hr_utility.set_location(l_proc, 121);
1858:
1859: -- ========================================================================
1860: -- Add logic to see generate part2 only if payroll is installed
1861: -- ========================================================================
1864: ,p_effective_date => p_ele_eff_start_date
1865: ,p_template_id => l_template_id
1866: );
1867:
1868: hr_utility.set_location(l_proc, 130);
1869:
1870: -- ========================================================================
1871: -- Get Element Type ID and Balance Id's to update the Further Information
1872: -- ========================================================================
1875:
1876: --
1877: -- get the Iterative Formula Id
1878: --
1879: hr_utility.set_location(l_proc, 135);
1880: open c_iter_formula;
1881: fetch c_iter_formula into l_iter_formula_id;
1882: close c_iter_formula;
1883: --
1880: open c_iter_formula;
1881: fetch c_iter_formula into l_iter_formula_id;
1882: close c_iter_formula;
1883: --
1884: hr_utility.set_location(l_proc, 140);
1885: --
1886: -- Added for Garnishment rewrite --
1887: if p_ele_classification = l_inv_ded then
1888: update pay_element_types_f
1928: iterative_priority = l_iter_priority
1929: where element_type_id = l_base_element_type_id
1930: and business_group_id = p_bg_id;
1931: end if;
1932: hr_utility.set_location(l_proc, 150);
1933:
1934: insert_iterative_rules(l_base_element_type_id);
1935:
1936: -- Get the _ASG_GRE_RUN dimension id
1944: where balance_type_id = l_pri_bal_id
1945: and balance_dimension_id = l_asg_gre_run_dim_id
1946: and business_group_id = p_bg_id;
1947:
1948: hr_utility.set_location(l_proc, 170);
1949: --
1950: if p_after_tax_component = 'Y' then
1951: update pay_element_types_f
1952: set element_information10 = l_at_pri_bal_id,
1966: and balance_dimension_id = l_asg_gre_run_dim_id
1967: and business_group_id = p_bg_id;
1968: end if;
1969:
1970: hr_utility.set_location(l_proc, 175);
1971: -- Update Roth element if Roth contribution is selected
1972: if p_roth_contribution = 'Y' then
1973: update pay_element_types_f
1974: set element_information10 = l_atr_pri_bal_id,
2004: end if;
2005:
2006: end if;
2007:
2008: hr_utility.set_location(l_proc, 180);
2009: -- Update the element_types_f table with the primary
2010: -- catchup balance id for the catch-up element
2011: if NVL(p_catchup_processing,'NONE') <> 'NONE' then
2012: update pay_element_types_f
2030: where balance_type_id = l_cu_pri_bal_id
2031: and balance_dimension_id = l_asg_gre_run_dim_id
2032: and business_group_id = p_bg_id;
2033: end if;
2034: hr_utility.set_location(l_proc, 190);
2035: -- Update the element_types_f table with the primary
2036: -- Buy Back balance id for the Buy-back element
2037: if NVL(p_ele_srs_buy_back,'N') <> 'N' then
2038: update pay_element_types_f
2057: where balance_type_id = l_bb_pri_bal_id
2058: and balance_dimension_id = l_asg_gre_run_dim_id
2059: and business_group_id = p_bg_id;
2060: end if;
2061: hr_utility.set_location(l_proc, 200);
2062: -- Update the element_types_f table with the primary
2063: -- ER Contribution balance id for the ER Contr element
2064: if nvl(p_ele_srs_plan_type,'N') <> 'N' then
2065: update pay_element_types_f
2067: element_information_category = 'US_EMPLOYER LIABILITIES'
2068: where element_type_id = l_er_contr_element_type_id
2069: and business_group_id = p_bg_id;
2070: end if;
2071: hr_utility.set_location(l_proc, 210);
2072: -- For SRS type Base element
2073: if nvl(p_ele_srs_plan_type,'N') <> 'N' then
2074:
2075: pay_element_extra_info_api.create_element_extra_info
2081: ,p_element_type_extra_info_id => l_srs_etei_id
2082: ,p_object_version_number => l_srs_etei_ovn
2083: );
2084: end if;
2085: hr_utility.set_location(l_proc, 220);
2086: -- For ER Contribution Element
2087: if nvl(p_ele_srs_plan_type,'N') <> 'N' then
2088:
2089: pay_element_extra_info_api.create_element_extra_info
2108: ,p_element_type_extra_info_id => l_srs_etei_id
2109: ,p_object_version_number => l_srs_etei_ovn
2110: );
2111: end if;
2112: hr_utility.set_location(l_proc, 220);
2113: --
2114: -- Update further info for the ER and AT ER elements
2115: --
2116: if p_employer_match = 'Y' then
2142: and balance_dimension_id = l_asg_gre_run_dim_id
2143: and business_group_id = p_bg_id;
2144:
2145: end if;
2146: hr_utility.set_location(l_proc, 230);
2147: --
2148: -- Create Information Type for Catch-Up Processing
2149: -- If p_catchup_processing is not null then should be checking for NONE
2150: --
2159: ,p_object_version_number => l_etei_ovn);
2160:
2161: end if;
2162: --
2163: hr_utility.set_location(l_proc, 240);
2164: if nvl(p_roth_contribution ,'N') <> 'N' and
2165: l_roth_ele_type_id is not null then
2166:
2167: pay_element_extra_info_api.create_element_extra_info
2173: ,p_element_type_extra_info_id => l_etei_id
2174: ,p_object_version_number => l_etei_ovn);
2175:
2176: end if;
2177: hr_utility.set_location(l_proc, 245);
2178:
2179: -- Make Pay Value as non user-enterable field for all the elements
2180: -- created under this pre-tax deduction plan.
2181: for i in csr_ele(c_effective_date => p_ele_eff_start_date
2192: -- ======================================================
2193:
2194: create_eligible_comp_bal_feeds;
2195: --
2196: hr_utility.set_location('Leaving: '||l_proc, 250);
2197: return l_base_element_type_id;
2198: --
2199: end create_user_init_template;
2200: --
2220: --
2221: begin
2222: --
2223: l_proc := g_proc||'.delete_user_init_template';
2224: hr_utility.set_location('Entering :'||l_proc, 10);
2225: --
2226: for c1_rec in c1 loop
2227: l_template_id := c1_rec.template_id;
2228: end loop;
2232: ,p_drop_formula_packages => true
2233: ,p_template_id => l_template_id
2234: );
2235: --
2236: hr_utility.set_location('Leaving :'||l_proc, 50);
2237: --
2238: end delete_user_init_template;
2239: --
2240: end pay_us_user_init_dedn;