[Home] [Help]
1: PACKAGE BODY hr_generate_pretax AS
2: /* $Header: pygenptx.pkb 115.16 2002/12/28 01:15:16 meshah ship $ */
3:
4: /*
5: +======================================================================+
7: | Redwood Shores, California, USA |
8: | All rights reserved. |
9: +======================================================================+
10:
11: Name : hr_generate_pretax
12: Filename : pygenptx.pkb
13:
14: Change List
15: -----------
405: v_business_group_id number;
406:
407: BEGIN
408:
409: hr_utility.set_location('hr_generate_pretax.do_defined_balances ',10);
410:
411: suffixes(1) := '_ASG_RUN';
412: suffixes(2) := '_ASG_PTD';
413: suffixes(3) := '_ASG_MONTH';
465: /* WWBug 133133, 350540 finish */
466:
467: for i in 1..num_suffixes loop
468:
469: hr_utility.set_location('hr_generate_pretax.do_defined_balances ',20);
470:
471: select dimension_name, balance_dimension_id
472: into dim_name, dim_id
473: from pay_balance_dimensions
474: where database_item_suffix = suffixes(i)
475: and legislation_code = g_template_leg_code
476: and business_group_id IS NULL;
477:
478: hr_utility.set_location('hr_generate_pretax.do_defined_balances ',30);
479:
480: /* added line to include business_group_id in the where clause of the select
481: statement below. So that it checkes the existence of data for a the given
482: business_group_id Bug No: 502307.
493: if (already_exists = 0) then
494:
495: IF p_no_payments = TRUE and suffixes(i) = '_PAYMENTS' THEN
496:
497: hr_utility.set_location('hr_generate_pretax.do_defined_balances ',40);
498:
499: NULL;
500:
501: ELSE
499: NULL;
500:
501: ELSE
502:
503: hr_utility.set_location('hr_generate_pretax.do_defined_balances ',50);
504:
505: pay_db_pay_setup.create_defined_balance(
506: p_balance_name => p_bal_name,
507: p_balance_dimension => dim_name,
513: end if;
514:
515: end loop;
516:
517: hr_utility.set_location('hr_generate_pretax.do_defined_balances ',60);
518:
519: END do_defined_balances;
520:
521: --
548: already_exists number;
549:
550: BEGIN
551:
552: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',10);
553:
554: IF p_ele_processing_type = 'N' THEN
555:
556: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',20);
552: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',10);
553:
554: IF p_ele_processing_type = 'N' THEN
555:
556: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',20);
557:
558: v_mult_entries_allowed := 'Y';
559:
560: END IF;
558: v_mult_entries_allowed := 'Y';
559:
560: END IF;
561:
562: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',50);
563:
564: already_exists := hr_template_existence.ele_exists(
565: p_ele_name => p_ele_name,
566: p_bg_id => p_bg_id,
567: p_eff_date => p_ele_eff_start_date);
568:
569: if already_exists = 0 then
570:
571: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',55);
572:
573: ret := pay_db_pay_setup.create_element(
574: p_element_name => p_ele_name,
575: p_description => p_ele_description,
587: p_effective_end_date => p_ele_eff_end_date,
588: p_legislation_code => NULL,
589: p_third_party_pay_only => v_third_ppm);
590:
591: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',80);
592:
593: else
594:
595: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',90);
591: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',80);
592:
593: else
594:
595: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',90);
596:
597: if p_ele_name = dedn_ele_names(1) then
598:
599: -- Base element already exists, this MUST be called via upgrade mechanism.
607: ret := already_exists;
608:
609: end if;
610:
611: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',100);
612:
613: RETURN ret;
614:
615: EXCEPTION WHEN NO_DATA_FOUND THEN
613: RETURN ret;
614:
615: EXCEPTION WHEN NO_DATA_FOUND THEN
616:
617: hr_utility.set_location('hr_generate_pretax.ins_dedn_ele_type',999);
618:
619: RETURN ret;
620:
621: END ins_dedn_ele_type;
651: already_exists number;
652:
653: BEGIN
654:
655: hr_utility.set_location('hr_generate_pretax.ins_base_formula',10);
656:
657: SELECT FF.formula_text,
658: FF.formula_type_id
659: INTO v_skeleton_formula_text,
666: AND g_eff_start_date <= FF.effective_end_date;
667:
668: -- Replace element name placeholders with current element name:
669:
670: hr_utility.set_location('hr_generate_pretax.ins_base_formula',15);
671:
672: l_placehold_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
673: v_new_ele_formula_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
674: v_new_ele_formula_text := REPLACE( v_skeleton_formula_text,
681: -- Call function to check existence of formula to get id.
682: -- Get original formula id, name, and text for this element currently,
683: -- ie. before putting in new ff text.
684:
685: hr_utility.set_location('hr_generate_pretax.ins_base_formula',20);
686:
687: already_exists := hr_template_existence.ele_ff_exists(
688: p_ele_name => p_spr_ele_name,
689: p_bg_id => p_ff_bg_id,
697: -- there is no formula for this element currently.
698: --
699: -- Get new id for formula
700:
701: hr_utility.set_location('hr_generate_pretax.ins_base_formula',30);
702:
703: SELECT ff_formulas_s.nextval
704: INTO v_new_ele_formula_id
705: FROM sys.dual;
703: SELECT ff_formulas_s.nextval
704: INTO v_new_ele_formula_id
705: FROM sys.dual;
706:
707: hr_utility.set_location('hr_generate_pretax.ins_base_formula',40);
708:
709: -- hr_utility.trace('Inserting ff '||v_new_ele_formula_name||' for ele '||p_ff_ele_name);
710:
711: INSERT INTO ff_formulas_f ( FORMULA_ID,
745: -- Element already has formula attached via stat proc rule...
746: -- original formula name and text have been populated as outputs
747: -- from check for existence.
748:
749: hr_utility.set_location('hr_generate_pretax.ins_base_formula',50);
750:
751: v_new_ele_formula_id := already_exists;
752:
753: -- Update existing formula with new ff name and text.
754:
755: -- hr_utility.trace('existing FF '||v_new_ele_formula_id||' being updated');
756: -- hr_utility.trace(v_new_ele_formula_name);
757:
758: hr_utility.set_location('hr_generate_pretax.ins_base_formula',70);
759:
760: /*
761: UPDATE ff_formulas_f
762: SET formula_name = v_new_ele_formula_name,
783: from ff_formulas_f
784: where upper(formula_name) like upper('%'||l_placehold_ele_name||'%');
785:
786:
787: hr_utility.set_location('hr_generate_pretax.ins_base_formula',35);
788:
789: -- hr_utility.trace('Preserving text for '||v_orig_ele_formula_name);
790:
791: v_orig_ele_formula_name := 'OLD'||l_count_already||'_'||v_orig_ele_formula_name;
792: v_orig_ele_formula_name := substr(v_orig_ele_formula_name,1,80);
793:
794: -- hr_utility.trace('Renamed ff name is '||v_orig_ele_formula_name);
795:
796: hr_utility.set_location('hr_generate_pretax.ins_base_formula',30);
797:
798: SELECT ff_formulas_s.nextval
799: INTO v_orig_ele_formula_id
800: FROM sys.dual;
798: SELECT ff_formulas_s.nextval
799: INTO v_orig_ele_formula_id
800: FROM sys.dual;
801:
802: hr_utility.set_location('hr_generate_pretax.ins_base_formula',40);
803:
804: INSERT INTO ff_formulas_f ( FORMULA_ID,
805: EFFECTIVE_START_DATE,
806: EFFECTIVE_END_DATE,
881: already_exists number;
882:
883: BEGIN
884:
885: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',10);
886:
887: SELECT FF.formula_text,
888: FF.formula_type_id
889: INTO v_skeleton_formula_text,
896: AND g_eff_start_date <= FF.effective_end_date;
897:
898: -- Replace element name placeholders with current element name:
899:
900: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',15);
901:
902: l_placehold_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
903: v_new_ele_formula_name := REPLACE(LTRIM(RTRIM(UPPER(p_ff_ele_name))),' ','_');
904: v_new_ele_formula_text := REPLACE( v_skeleton_formula_text,
924: IF p_amt_rule = 'PT' THEN
925:
926: IF p_row_type NOT IN ('Salary Range', 'Age Range') THEN
927:
928: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',17);
929:
930: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
931: '
932: REPLACE(LTRIM(RTRIM(p_row_type)),' ','_'));
930: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
931: '
932: REPLACE(LTRIM(RTRIM(p_row_type)),' ','_'));
933:
934: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',19);
935:
936: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
937: '
938: 'default for ' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text) is ''NOT ENTERED''');
936: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
937: '
938: 'default for ' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text) is ''NOT ENTERED''');
939:
940: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',21);
941:
942: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
943: '
944: ',' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text)');
944: ',' || REPLACE(LTRIM(RTRIM(p_row_type)),' ','_') || ' (text)');
945:
946: ELSE
947:
948: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',20);
949:
950: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
951: '
952: 'To_Char(PER_AGE)');
950: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
951: '
952: 'To_Char(PER_AGE)');
953:
954: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',22);
955:
956: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
957: '
958: ' ');
956: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
957: '
958: ' ');
959:
960: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',24);
961:
962: v_new_ele_formula_text := REPLACE(v_new_ele_formula_text,
963: '
964: ' ');
1057: -- Call function to check existence of formula to get id.
1058: -- Get original formula id, name, and text for this element currently,
1059: -- ie. before putting in new ff text.
1060:
1061: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',20);
1062:
1063: already_exists := hr_template_existence.ele_ff_exists(
1064: p_ele_name => p_spr_ele_name,
1065: p_bg_id => p_ff_bg_id,
1073: -- there is no formula for this element currently.
1074: --
1075: -- Get new id for formula
1076:
1077: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',30);
1078:
1079: SELECT ff_formulas_s.nextval
1080: INTO v_new_ele_formula_id
1081: FROM sys.dual;
1079: SELECT ff_formulas_s.nextval
1080: INTO v_new_ele_formula_id
1081: FROM sys.dual;
1082:
1083: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',40);
1084:
1085: INSERT INTO ff_formulas_f ( FORMULA_ID,
1086: EFFECTIVE_START_DATE,
1087: EFFECTIVE_END_DATE,
1119: -- Element already has formula attached via stat proc rule...
1120: -- original formula name and text have been populated as outputs
1121: -- from check for existence.
1122:
1123: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',50);
1124:
1125: v_new_ele_formula_id := already_exists;
1126:
1127: -- Update existing formula with new ff name and text.
1125: v_new_ele_formula_id := already_exists;
1126:
1127: -- Update existing formula with new ff name and text.
1128:
1129: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',70);
1130:
1131: -- hr_utility.trace('existing FF '||v_new_ele_formula_id||' being updated');
1132: -- hr_utility.trace(v_new_ele_formula_name);
1133:
1159: where upper(formula_name) like upper('%'||l_placehold_ele_name||'%');
1160:
1161: -- hr_utility.trace('Preserving text for '||v_orig_ele_formula_name);
1162:
1163: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',80);
1164:
1165: v_orig_ele_formula_name := 'OLD'||l_count_already||'_'||v_orig_ele_formula_name;
1166: v_orig_ele_formula_name := substr(v_orig_ele_formula_name,1,80);
1167:
1166: v_orig_ele_formula_name := substr(v_orig_ele_formula_name,1,80);
1167:
1168: -- hr_utility.trace('Original formula now in ff called '||v_orig_ele_formula_name);
1169:
1170: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',90);
1171:
1172: SELECT ff_formulas_s.nextval
1173: INTO v_orig_ele_formula_id
1174: FROM sys.dual;
1172: SELECT ff_formulas_s.nextval
1173: INTO v_orig_ele_formula_id
1174: FROM sys.dual;
1175:
1176: hr_utility.set_location('hr_generate_pretax.ins_calc_formula',100);
1177:
1178: INSERT INTO ff_formulas_f ( FORMULA_ID,
1179: EFFECTIVE_START_DATE,
1180: EFFECTIVE_END_DATE,
1316: IF UPPER(p_amount_rule) = 'PE' THEN
1317:
1318: -- Set DDF segments
1319:
1320: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',05);
1321:
1322: -- For CALCULATOR ele, set mix flag and ben class if approp...
1323: UPDATE pay_element_types_f
1324: SET element_information_category = g_ele_info_cat,
1339: benefit_classification_id = NULL
1340: WHERE element_type_id = dedn_ele_ids(2)
1341: AND business_group_id + 0 = p_bg_id;
1342:
1343: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',05);
1344:
1345: UPDATE pay_element_types_f
1346: SET element_information_category = g_ele_info_cat,
1347: element_information1 = p_ele_cat,
1349: element_information3 = p_proc_runtype
1350: WHERE element_type_id = dedn_ele_ids(4)
1351: AND business_group_id + 0 = p_bg_id;
1352:
1353: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',05);
1354:
1355: UPDATE pay_element_types_f
1356: SET element_information_category = g_ele_info_cat,
1357: element_information1 = p_ele_cat,
1359: element_information3 = p_proc_runtype
1360: WHERE element_type_id = dedn_ele_ids(3)
1361: AND business_group_id + 0 = p_bg_id;
1362:
1363: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',15);
1364:
1365: -- Check for payroll table amount rule...
1366: ELSIF UPPER(p_amount_rule) = 'PT' THEN
1367:
1366: ELSIF UPPER(p_amount_rule) = 'PT' THEN
1367:
1368: -- Find table id
1369:
1370: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',53);
1371:
1372: SELECT user_table_id
1373: INTO v_paytab_id
1374: FROM pay_user_tables
1376: AND NVL(business_group_id, p_bg_id) = p_bg_id;
1377:
1378: -- Find row code
1379:
1380: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',55);
1381:
1382: SELECT lookup_code
1383: INTO v_row_code
1384: FROM hr_lookups
1384: FROM hr_lookups
1385: WHERE UPPER(meaning) = UPPER(p_paytab_row_type)
1386: AND lookup_type = 'US_TABLE_ROW_TYPES';
1387:
1388: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',57);
1389:
1390: -- For CALCULATOR, set mix flag and ben class...
1391: UPDATE pay_element_types_f
1392: SET element_information_category = g_ele_info_cat,
1411: benefit_classification_id = NULL
1412: WHERE element_type_id = dedn_ele_ids(2)
1413: AND business_group_id + 0 = p_bg_id;
1414:
1415: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',57);
1416:
1417:
1418: UPDATE pay_element_types_f
1419: SET element_information_category = g_ele_info_cat,
1424: element_information7 = v_row_code
1425: WHERE element_type_id = dedn_ele_ids(4)
1426: AND business_group_id + 0 = p_bg_id;
1427:
1428: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',57);
1429:
1430:
1431: UPDATE pay_element_types_f
1432: SET element_information_category = g_ele_info_cat,
1437: element_information7 = v_row_code
1438: WHERE element_type_id = dedn_ele_ids(3)
1439: AND business_group_id + 0 = p_bg_id;
1440:
1441: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',59);
1442:
1443:
1444: -- Check for Benefits Table amount rule...
1445: ELSIF UPPER(p_amount_rule) = 'BT' THEN
1465: WHERE element_type_id = dedn_ele_ids(2)
1466: AND business_group_id + 0 = p_bg_id;
1467:
1468:
1469: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',83);
1470:
1471:
1472: UPDATE pay_element_types_f
1473: SET element_information_category = g_ele_info_cat,
1477: WHERE element_type_id = dedn_ele_ids(4)
1478: AND business_group_id + 0 = p_bg_id;
1479:
1480:
1481: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',83);
1482:
1483: UPDATE pay_element_types_f
1484: SET element_information_category = g_ele_info_cat,
1485: element_information1 = p_ele_cat,
1488: WHERE element_type_id = dedn_ele_ids(3)
1489: AND business_group_id + 0 = p_bg_id;
1490:
1491:
1492: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',85);
1493:
1494: ELSE
1495: --
1496: -- Default to Flat Amount processing of deduction.
1496: -- Default to Flat Amount processing of deduction.
1497: -- Set DDF Segment values:
1498: --
1499:
1500: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',81);
1501:
1502: -- Set mix flag and ben class on CALCULATOR element...
1503: UPDATE pay_element_types_f
1504: SET element_information_category = g_ele_info_cat,
1520: WHERE element_type_id = dedn_ele_ids(2)
1521: AND business_group_id + 0 = p_bg_id;
1522:
1523:
1524: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',81);
1525:
1526:
1527: UPDATE pay_element_types_f
1528: SET element_information_category = g_ele_info_cat,
1531: element_information3 = p_proc_runtype
1532: WHERE element_type_id = dedn_ele_ids(4)
1533: AND business_group_id + 0 = p_bg_id;
1534:
1535: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',81);
1536:
1537:
1538: UPDATE pay_element_types_f
1539: SET element_information_category = g_ele_info_cat,
1542: element_information3 = p_proc_runtype
1543: WHERE element_type_id = dedn_ele_ids(3)
1544: AND business_group_id + 0 = p_bg_id;
1545:
1546: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',15);
1547:
1548: END IF; -- Amount rule checks for formula insertion...
1549:
1550:
1577:
1578: l_ff_suffix := '_PERCENTAGE_PTX';
1579: l_ff_desc := 'Percentage calculation for deductions.';
1580:
1581: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',17);
1582:
1583: ELSIF UPPER(p_amount_rule) = 'PT' THEN
1584:
1585: l_iv_defaults_ff_text := ' default for '||l_placeholder_ele_name||'_TABLE_COLUMN_ENTRY_VALUE IS ''NOT ENTERED'' ';
1585: l_iv_defaults_ff_text := ' default for '||l_placeholder_ele_name||'_TABLE_COLUMN_ENTRY_VALUE IS ''NOT ENTERED'' ';
1586:
1587: IF p_paytab_row_type NOT IN ('Salary Range', 'Age Range') THEN
1588:
1589: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',75);
1590: l_iv_defaults_ff_text := l_iv_defaults_ff_text||' default for ' || REPLACE(LTRIM(RTRIM(p_paytab_row_type)),' ','_') || ' (text) is ''NOT ENTERED'' ';
1591:
1592: END IF;
1593:
1649:
1650: l_ff_suffix := '_FLAT_PTX';
1651: l_ff_desc := 'Flat Amount calculation for deductions.';
1652:
1653: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',119);
1654:
1655: END IF;
1656:
1657:
1675: p_row_type => NULL,
1676: p_iv_dflts_text => l_iv_defaults_ff_text,
1677: p_calc_dedn_text => l_calc_dedn_ff_text );
1678:
1679: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',87);
1680:
1681: --
1682: -- Now setup withholding formula on WITHHOLDING element...
1683: --
1689:
1690: --
1691: -- check for existence of status proc rule...function returns id if it does...
1692: --
1693: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',119);
1694:
1695: -- hr_utility.trace('spr exists checking...');
1696: -- hr_utility.trace(dedn_ele_ids(1)||' '||v_calc_rule_formula_id);
1697: -- hr_utility.trace(p_bg_id||' '||p_eff_start_date);
1703: p_val_date => p_eff_start_date);
1704:
1705: if already_exists = 0 then
1706:
1707: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',121);
1708: v_stat_proc_rule_id :=
1709: pay_formula_results.ins_stat_proc_rule (
1710: p_business_group_id => p_bg_id,
1711: p_legislation_code => NULL,
1722: else
1723:
1724: -- Statproc rule already exists for calculator element.
1725:
1726: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',123);
1727:
1728: v_stat_proc_rule_id := already_exists;
1729: dedn_statproc_rule_id(1) := v_stat_proc_rule_id;
1730:
1755: p_val_date => p_eff_start_date);
1756:
1757: if already_exists = 0 then
1758:
1759: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',125);
1760: v_stat_proc_rule_id :=
1761: pay_formula_results.ins_stat_proc_rule (
1762: p_business_group_id => p_bg_id,
1763: p_legislation_code => NULL,
1774: else
1775:
1776: -- Statproc rule already exists for calculator element.
1777:
1778: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',127);
1779:
1780: v_stat_proc_rule_id := already_exists;
1781: dedn_statproc_rule_id(2) := v_stat_proc_rule_id;
1782:
1806: -- formula result rules...
1807:
1808: IF UPPER(p_amount_rule) = 'PE' THEN
1809:
1810: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1811:
1812: dedn_base_iv_names(1) := 'Percentage';
1813: dedn_base_iv_uom(1) := 'Number';
1814: dedn_base_iv_mand(1) := 'N';
1831: -- the SCL and formula picks it up from there. This will amount
1832: -- to an input value required when the user enters a value OTHER
1833: -- then "Salary Range" or "Age Range" in the Row Type field.
1834:
1835: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1836:
1837: dedn_base_iv_names(1) := 'Table Column';
1838: dedn_base_iv_uom(1) := 'Character';
1839: dedn_base_iv_mand(1) := 'N';
1845: dedn_base_iv_names(3) := NULL;
1846:
1847: l_num_base_ivs := 3;
1848:
1849: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1850:
1851: -- Place logic determining to create or not create additional input value here:
1852: -- 1) If p_paytab_row_type = 'Age Range' or 'Salary Range' then DO NOT create
1853: -- addl inpval;
1858: --
1859:
1860: IF p_paytab_row_type NOT IN ('Salary Range', 'Age Range') THEN
1861:
1862: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',75);
1863:
1864: dedn_base_iv_names(2) := p_paytab_row_type;
1865: dedn_base_iv_uom(2) := 'Character';
1866: dedn_base_iv_mand(2) := 'N';
1875: END IF; -- rowtype = dbi name check.
1876:
1877: ELSIF UPPER(p_amount_rule) = 'BT' THEN
1878:
1879: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',65);
1880:
1881: dedn_base_iv_names(1) := 'Coverage';
1882: dedn_base_iv_uom(1) := 'Character';
1883: dedn_base_iv_mand(1) := 'N';
3226: dedn_calc_frr_severity(3) := 'W';
3227:
3228: l_num_calc_resrules := 3;
3229:
3230: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',47);
3231:
3232: IF UPPER(p_amount_rule) = 'BT' THEN
3233:
3234: --
3236: -- this benefit, we must find the input_value_id for the pay_value of the
3237: -- employer charge element.
3238: --
3239:
3240: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',115);
3241:
3242: dedn_calc_frr_name(4) := 'BENE_ER_CONTR';
3243: dedn_calc_frr_type(4) := 'I';
3244: dedn_calc_frr_ele_id(4) := dedn_ele_ids(5);
3256: END IF;
3257:
3258: IF p_ele_proc_type = 'R' THEN
3259:
3260: hr_utility.set_location('hr_generate_pretax.ins_uie_inp_values',90);
3261:
3262: dedn_calc_frr_name(5) := 'CLEAR_REPL_AMT';
3263: dedn_calc_frr_type(5) := 'I';
3264: dedn_calc_frr_ele_id(5) := dedn_ele_ids(4);
3420:
3421: -- create formula result rule for:
3422: -- (*) to_arrears --> Indirect to
3423:
3424: hr_utility.set_location('hr_generate_pretax.ins_dedn_formula_processing',450);
3425:
3426: dedn_wh_frr_name(9) := 'TO_ARREARS';
3427: dedn_wh_frr_type(9) := 'I';
3428: dedn_wh_frr_ele_id(9) := dedn_ele_ids(4);
3701:
3702: --
3703: -- Set session date
3704:
3705: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',10);
3706:
3707: pay_db_pay_setup.set_session_date(nvl(p_ele_eff_start_date, sysdate));
3708: g_eff_start_date := nvl(p_ele_eff_start_date, sysdate);
3709: g_eff_end_date := nvl(p_ele_eff_end_date, c_end_of_time);
3709: g_eff_end_date := nvl(p_ele_eff_end_date, c_end_of_time);
3710:
3711: -- Set "globals": v_bg_name
3712:
3713: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',20);
3714: select name
3715: into v_bg_name
3716: from per_business_groups
3717: where business_group_id = p_bg_id;
3886: -- Need to determine and get skip rule formula id and pass it
3887: -- create_element.
3888: --
3889:
3890: hr_utility.set_location('hr_generate_pretax.upgrade_template',10);
3891:
3892: IF UPPER(p_ele_start_rule) = 'CHAINED' THEN
3893:
3894: hr_utility.set_location('hr_generate_pretax.upgrade_template',15);
3890: hr_utility.set_location('hr_generate_pretax.upgrade_template',10);
3891:
3892: IF UPPER(p_ele_start_rule) = 'CHAINED' THEN
3893:
3894: hr_utility.set_location('hr_generate_pretax.upgrade_template',15);
3895:
3896: SELECT FF.formula_id
3897: INTO v_skip_formula_id
3898: FROM ff_formulas_f FF
3903: AND p_ele_eff_start_date <= FF.effective_end_date;
3904:
3905: ELSIF UPPER(p_ele_start_rule) = 'ET' THEN
3906:
3907: hr_utility.set_location('hr_generate_pretax.upgrade_template',20);
3908: SELECT FF.formula_id
3909: INTO v_skip_formula_id
3910: FROM ff_formulas_f FF
3911: WHERE FF.formula_name = 'THRESHOLD_SKIP_FORMULA'
3915: AND p_ele_eff_start_date <= FF.effective_end_date;
3916:
3917: ELSE -- Just check skip rule and separate check flag.
3918:
3919: hr_utility.set_location('hr_generate_pretax.upgrade_template',25);
3920:
3921: SELECT FF.formula_id
3922: INTO v_skip_formula_id
3923: FROM ff_formulas_f FF
3947: -- Need to find PRIMARY_CLASSIFICATION_ID of element type.
3948: -- For future calls to various API.
3949: --
3950:
3951: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',53);
3952:
3953: begin
3954:
3955: select distinct(classification_id)
4080: l_num_eles := 4;
4081:
4082: IF p_ele_amount_rule = 'BT' or p_ele_er_match = 'Y' THEN
4083:
4084: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',55);
4085:
4086: select default_priority
4087: into v_emp_liab_dflt_prio
4088: from pay_element_classifications
4116: for x in 1..l_num_eles LOOP
4117:
4118: IF dedn_ele_names(x) IS NOT NULL THEN
4119:
4120: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',51);
4121:
4122: v_ele_type_id := ins_dedn_ele_type ( dedn_ele_names(x),
4123: dedn_ele_repnames(x),
4124: dedn_ele_desc(x),
4140: -- Make pay value non enterable.
4141:
4142: v_pay_value_name := hr_input_values.get_pay_value_name(g_template_leg_code);
4143:
4144: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',53);
4145:
4146: -- hr_utility.trace('Updating '||v_pay_value_name||' for '||dedn_ele_names(x));
4147:
4148: UPDATE pay_input_values_f
4167: END IF;
4168:
4169: END LOOP;
4170:
4171: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',57);
4172:
4173: IF p_ele_amount_rule = 'BT' or p_ele_er_match = 'Y' THEN
4174: --
4175: -- Create "Primary" balance for ER Liab and "associate" appropriately.
4175: -- Create "Primary" balance for ER Liab and "associate" appropriately.
4176: -- Is there any way to table these optional objects that go with optional elements?
4177: --
4178:
4179: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',58);
4180:
4181: v_balance_name := dedn_ele_names(5);
4182: v_bal_rpt_name := dedn_ele_repnames(5);
4183:
4226: p_eff_date => g_eff_start_date );
4227:
4228: if ALREADY_EXISTS = 0 then
4229:
4230: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',90);
4231:
4232: hr_balances.ins_balance_feed(
4233: p_option => 'INS_MANUAL_FEED',
4234: p_input_value_id => dedn_payval_id(5),
4250: --
4251: -------------------------- Insert Formula Processing records -------------
4252: --
4253:
4254: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',68);
4255:
4256: ins_dedn_formula_processing ( dedn_ele_names(1),
4257: v_primary_class_id,
4258: p_ele_classification,
4275: g_eff_start_date,
4276: g_eff_end_date,
4277: v_bg_name);
4278:
4279: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',69);
4280:
4281: --
4282: ------------------------ Insert Balance Feeds -------------------------
4283: --
4299: p_element_type_id => dedn_ele_ids(1),
4300: p_date => g_eff_start_date);
4301:
4302:
4303: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',70);
4304:
4305: /* *** WITHHOLDING AND CALCULATOR FEEDS SECTION BEGIN *** */
4306:
4307: dedn_base_feed_iv_id(1) := dedn_payval_id(1); -- Base Payval to
4407: p_eff_date => g_eff_start_date );
4408:
4409: if ALREADY_EXISTS = 0 then
4410:
4411: hr_utility.set_location('hr_generate_pretax.ins_deduction_template',90);
4412:
4413: hr_balances.ins_balance_feed(
4414: p_option => 'INS_MANUAL_FEED',
4415: p_input_value_id => dedn_base_feed_iv_id(y),
4520: RETURN dedn_ele_ids(1);
4521:
4522: END pretax_deduction_template;
4523:
4524: END hr_generate_pretax;