[Home] [Help]
221:
222: 01/22/2002 ahanda 115.6 Added _ASG_PAYMENTS dimension
223: 25-Mar-2002 ekim 115.8 2276457 Added p_termination_rule to
224: ins_deduction_template and update of
225: pay_element_types_f for
226: termination rule update
227: 24-Jul-2002 ekim 115.9 changed v_bg_name to
228: per_business_groups.name%TYPE
229: 23-DEC-2002 tclewis 115.10 11.5.9. performance changes.
245: 20-MAY-2004 meshah 115.18 removed the logic not required for
246: Non-Recurring elements. Like creation of
247: additional and replacement defined balances.
248: 21-MAY-2004 meshah 115.19 fixed gscc error File.Sql.2 and File.Sql.17
249: 21-JUL-2004 schauhan 115.20 3613575 Added a table pay_element_types_f to a query
250: in procedure do_deletions to remove Merge Join Cartesian.
251: 22-JUL-2004 schauhan 115.21 3613575 Added rownum<2 condition to the query modified in previous
252: version.
253: 18-AUG-2004 sdhole 115.22 3651755 Removed balance category parameter for the
896: -- Set SCL:
897: -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
898: -- Populate segments 1,2,3 w/Category, PayTab, PayTab Row.
899: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
900: UPDATE pay_element_types_f
901: SET element_information_category = g_ele_info_cat,
902: element_information1 = p_ele_cat,
903: element_information2 = p_partial_dedn,
904: element_information3 = p_proc_runtype,
907: WHERE element_type_id = p_ele_id
908: AND business_group_id + 0 = p_bg_id;
909:
910: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
911: UPDATE pay_element_types_f
912: SET element_information_category = g_ele_info_cat,
913: element_information1 = p_ele_cat,
914: element_information2 = p_partial_dedn,
915: element_information3 = p_proc_runtype
919:
920: If p_ele_proc_type = 'R' then /* Not required for NR Elements */
921:
922: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
923: UPDATE pay_element_types_f
924: SET element_information_category = g_ele_info_cat,
925: element_information1 = p_ele_cat,
926: element_information2 = p_partial_dedn,
927: element_information3 = p_proc_runtype
1125: --
1126: -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
1127: -- Populate segments 1,2,3 w/Category, PayTab, PayTab Row.
1128: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1129: UPDATE pay_element_types_f
1130: SET element_information_category = g_ele_info_cat,
1131: element_information1 = p_ele_cat,
1132: element_information2 = p_partial_dedn,
1133: element_information3 = p_proc_runtype,
1138: WHERE element_type_id = p_ele_id
1139: AND business_group_id + 0 = p_bg_id;
1140:
1141: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1142: UPDATE pay_element_types_f
1143: SET element_information_category = g_ele_info_cat,
1144: element_information1 = p_ele_cat,
1145: element_information2 = p_partial_dedn,
1146: element_information3 = p_proc_runtype,
1152:
1153: If p_ele_proc_type = 'R' Then /* Not required for NR Elements */
1154:
1155: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1156: UPDATE pay_element_types_f
1157: SET element_information_category = g_ele_info_cat,
1158: element_information1 = p_ele_cat,
1159: element_information2 = p_partial_dedn,
1160: element_information3 = p_proc_runtype,
1417: -- Set SCL:
1418: -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
1419: -- Populate segments 1 w/Category
1420: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1421: UPDATE pay_element_types_f
1422: SET element_information_category = g_ele_info_cat,
1423: element_information1 = p_ele_cat,
1424: element_information2 = p_partial_dedn,
1425: element_information3 = p_proc_runtype,
1428: WHERE element_type_id = p_ele_id
1429: AND business_group_id + 0 = p_bg_id;
1430:
1431: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',83);
1432: UPDATE pay_element_types_f
1433: SET element_information_category = g_ele_info_cat,
1434: element_information1 = p_ele_cat,
1435: element_information2 = p_partial_dedn,
1436: element_information3 = p_proc_runtype
1439:
1440: If p_ele_proc_type = 'R' then /* Not required for NR Elements */
1441:
1442: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',83);
1443: UPDATE pay_element_types_f
1444: SET element_information_category = g_ele_info_cat,
1445: element_information1 = p_ele_cat,
1446: element_information2 = p_partial_dedn,
1447: element_information3 = p_proc_runtype
1784: --
1785: -- Set DDF Segment values:
1786: --
1787: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1788: UPDATE pay_element_types_f
1789: SET element_information_category = g_ele_info_cat,
1790: element_information1 = p_ele_cat,
1791: element_information2 = p_partial_dedn,
1792: element_information3 = p_proc_runtype,
1795: WHERE element_type_id = p_ele_id
1796: AND business_group_id + 0 = p_bg_id;
1797:
1798: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1799: UPDATE pay_element_types_f
1800: SET element_information_category = g_ele_info_cat,
1801: element_information1 = p_ele_cat,
1802: element_information2 = p_partial_dedn,
1803: element_information3 = p_proc_runtype
1807:
1808: If p_ele_proc_type = 'R' then /* Not required for NR Elements */
1809:
1810: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1811: UPDATE pay_element_types_f
1812: SET element_information_category = g_ele_info_cat,
1813: element_information1 = p_ele_cat,
1814: element_information2 = p_partial_dedn,
1815: element_information3 = p_proc_runtype
4251: hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',53);
4252:
4253: select distinct(classification_id)
4254: into v_primary_class_id
4255: from pay_element_types_f
4256: where element_type_id = v_ele_type_id;
4257: --
4258: -- Need to update termination rule.(bug 2276457)
4259: --
4256: where element_type_id = v_ele_type_id;
4257: --
4258: -- Need to update termination rule.(bug 2276457)
4259: --
4260: UPDATE pay_element_types_f
4261: SET post_termination_rule = p_termination_rule
4262: WHERE element_type_id = v_ele_type_id;
4263: --
4264: hr_utility.set_location('pyusuiet',130);
4297:
4298: --
4299: -- Need to update termination rule.(bug 2276457)
4300: --
4301: UPDATE pay_element_types_f
4302: SET post_termination_rule = p_termination_rule
4303: WHERE element_type_id = v_inputs_ele_type_id;
4304:
4305: End if; /* Not required for NR Elements */
4329:
4330: --
4331: -- Need to update termination rule.(bug 2276457)
4332: --
4333: UPDATE pay_element_types_f
4334: SET post_termination_rule = p_termination_rule
4335: WHERE element_type_id = v_shadow_ele_type_id;
4336:
4337: --
4426:
4427: ---Inserted by lwthomps for bug 345102. Associate Primary balance with
4428: ---Employer Liabiliity type benefits.
4429:
4430: UPDATE pay_element_types_f
4431: SET ELEMENT_INFORMATION10 = v_er_charge_baltype_id
4432: WHERE element_type_id = v_er_charge_eletype_id;
4433:
4434: -- Bug Fix 5763867
4431: SET ELEMENT_INFORMATION10 = v_er_charge_baltype_id
4432: WHERE element_type_id = v_er_charge_eletype_id;
4433:
4434: -- Bug Fix 5763867
4435: UPDATE pay_element_types_f
4436: SET element_information_category = 'US_EMPLOYER LIABILITIES',
4437: element_information1 = 'B'
4438: WHERE element_type_id = v_er_charge_eletype_id
4439: AND business_group_id + 0 = p_bg_id;
4482: p_effective_date => g_eff_start_date);
4483:
4484: -- Update ele type DDF for primary associated balance.
4485:
4486: UPDATE pay_element_types_f
4487: SET ELEMENT_INFORMATION10 = v_eerefund_baltype_id
4488: WHERE element_type_id = v_eerefund_eletype_id;
4489:
4490: do_defined_balances( p_bal_name => v_balance_name,
4825: -- Other associated balances:
4826: --
4827:
4828: if p_ele_processing_type = 'R' then /* Not required for NR Elements */
4829: UPDATE pay_element_types_f
4830: SET element_information10 = v_bal_type_id,
4831: element_information11 = v_totowed_bal_type_id,
4832: element_information12 = v_arrears_bal_type_id,
4833: element_information13 = v_notaken_bal_type_id,
4840: AND business_group_id + 0 = p_bg_id;
4841:
4842: else /* Not required for NR Elements */
4843:
4844: UPDATE pay_element_types_f
4845: SET element_information10 = v_bal_type_id,
4846: element_information13 = v_notaken_bal_type_id,
4847: element_information19 = v_shadow_ele_type_id
4848: WHERE element_type_id = v_ele_type_id
5038:
5039: v_spr_id number(9);
5040: v_assoc_ele_priority number(9);
5041: v_refund_bal_id pay_balance_types.balance_type_id%TYPE;
5042: v_refund_ele_id pay_element_types_f.element_type_id%TYPE;
5043:
5044: CURSOR get_freqrule IS
5045: SELECT ele_payroll_freq_rule_id
5046: FROM pay_ele_payroll_freq_rules
5136: l_num_assoc_eles := l_num_assoc_eles + 1;
5137:
5138: select element_type_id
5139: into v_employer_match_id
5140: from pay_element_types_f
5141: where element_name like p_ele_name||' ER'
5142: and v_del_sess_date >= effective_start_date
5143: and v_del_sess_date <= effective_end_date
5144: and business_group_id + 0 = p_business_group_id ;
5150: l_num_assoc_eles := l_num_assoc_eles + 1;
5151:
5152: select element_type_id
5153: into v_after_tax_id
5154: from pay_element_types_f
5155: where element_name like p_ele_name||' AT'
5156: AND v_del_sess_date >= effective_start_date
5157: AND v_del_sess_date <= effective_end_date
5158: and business_group_id + 0 = p_business_group_id;
5162: l_num_assoc_eles := l_num_assoc_eles + 1;
5163:
5164: select element_type_id
5165: into v_after_tax_si_id
5166: from pay_element_types_f
5167: where element_name like p_ele_name||' AT Special Inputs'
5168: AND v_del_sess_date >= effective_start_date
5169: AND v_del_sess_date <= effective_end_date
5170: and business_group_id + 0 = p_business_group_id;
5174: l_num_assoc_eles := l_num_assoc_eles + 1;
5175:
5176: select element_type_id
5177: into v_after_tax_sf_id
5178: from pay_element_types_f
5179: where element_name like p_ele_name||' AT Special Features'
5180: AND v_del_sess_date >= effective_start_date
5181: AND v_del_sess_date <= effective_end_date
5182: and business_group_id + 0 = p_business_group_id;
5189: additional_baltype_id, replacement_baltype_id
5190: into v_at_bal_id, v_at_accr_id, v_at_arr_id, v_at_not_taken_id,
5191: v_at_to_bond_id, v_at_addl_id, v_at_repl_id
5192: from pay_all_deduction_types_v padt,
5193: pay_element_types_f pet
5194: WHERE pet.element_type_id = padt.element_type_id
5195: AND padt.element_name like p_ele_name||' AT'
5196: AND v_del_sess_date >= padt.effective_start_date
5197: AND v_del_sess_date <= padt.effective_end_date
5234: l_num_assoc_eles := l_num_assoc_eles + 1;
5235:
5236: select element_type_id
5237: into v_at_er_id
5238: from pay_element_types_f
5239: where element_name like p_ele_name||' AT ER'
5240: AND v_del_sess_date >= effective_start_date
5241: AND v_del_sess_date <= effective_end_date
5242: and business_group_id + 0 = p_business_group_id ;
5288:
5289: BEGIN
5290: SELECT element_type_id
5291: INTO v_refund_ele_id
5292: FROM pay_element_types_f
5293: WHERE element_name like p_ele_name||' Refund'
5294: AND v_del_sess_date >= effective_start_date
5295: AND v_del_sess_date <= effective_end_date
5296: AND business_group_id + 0 = p_business_group_id;
5516: IF assoc_eles(j) IS NOT NULL THEN
5517:
5518: select processing_priority
5519: into v_assoc_ele_priority
5520: from pay_element_types_f
5521: where element_type_id = assoc_eles(j)
5522: and v_del_sess_date between effective_start_date
5523: and effective_end_date;
5524:
5543: -- Delete element type record:
5544: -- Remember, we're 'ZAP'ing, no need to worry about date-effective delete.
5545: --
5546: hr_utility.set_location('hr_us_garn_gen.delete_dedn', 35);
5547: delete from PAY_ELEMENT_TYPES_F
5548: where element_type_id = assoc_eles(j);
5549:
5550: END IF;
5551:
5595: element name will fetch more than one row and raise error. **/
5596:
5597: SELECT DISTINCT element_type_id
5598: INTO v_eletype_id
5599: FROM pay_element_types_f
5600: WHERE element_name = p_ele_name || ' ER' --Bug 3349594
5601: AND v_del_sess_date >= effective_start_date
5602: AND v_del_sess_date <= effective_end_date
5603: and business_group_id + 0 = p_business_group_id ;
5623: -- Delete element type record:
5624: -- Remember, we're 'ZAP'ing, so no need to worry about date-effective delete.
5625: --
5626: hr_utility.set_location('hr_user_init_dedn.do_deletions', 35);
5627: delete from PAY_ELEMENT_TYPES_F
5628: where element_type_id = v_eletype_id;
5629:
5630: exception when NO_DATA_FOUND then
5631: null;
5665:
5666: hr_utility.set_location('hr_user_init_dedn.do_deletions', 17);
5667: SELECT DISTINCT element_type_id
5668: INTO v_eletype_id
5669: FROM pay_element_types_f
5670: WHERE element_name = p_ele_name || ' REFUND' --Bug 3349594
5671: AND v_del_sess_date >= effective_start_date
5672: AND v_del_sess_date <= effective_end_date
5673: and business_group_id + 0 = p_business_group_id ;
5690: p_val_end_date => v_del_val_end,
5691: p_startup_mode => v_startup_mode);
5692:
5693: hr_utility.set_location('hr_user_init_dedn.do_deletions', 35);
5694: delete from PAY_ELEMENT_TYPES_F
5695: where element_type_id = v_eletype_id;
5696:
5697: exception when NO_DATA_FOUND then
5698: null;