[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
884: -- Set SCL:
885: -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
886: -- Populate segments 1,2,3 w/Category, PayTab, PayTab Row.
887: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
888: UPDATE pay_element_types_f
889: SET element_information_category = g_ele_info_cat,
890: element_information1 = p_ele_cat,
891: element_information2 = p_partial_dedn,
892: element_information3 = p_proc_runtype,
895: WHERE element_type_id = p_ele_id
896: AND business_group_id + 0 = p_bg_id;
897:
898: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',05);
899: UPDATE pay_element_types_f
900: SET element_information_category = g_ele_info_cat,
901: element_information1 = p_ele_cat,
902: element_information2 = p_partial_dedn,
903: element_information3 = p_proc_runtype
907:
908: If p_ele_proc_type = 'R' then /* Not required for NR Elements */
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
1113: --
1114: -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
1115: -- Populate segments 1,2,3 w/Category, PayTab, PayTab Row.
1116: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1117: UPDATE pay_element_types_f
1118: SET element_information_category = g_ele_info_cat,
1119: element_information1 = p_ele_cat,
1120: element_information2 = p_partial_dedn,
1121: element_information3 = p_proc_runtype,
1126: WHERE element_type_id = p_ele_id
1127: AND business_group_id + 0 = p_bg_id;
1128:
1129: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1130: UPDATE pay_element_types_f
1131: SET element_information_category = g_ele_info_cat,
1132: element_information1 = p_ele_cat,
1133: element_information2 = p_partial_dedn,
1134: element_information3 = p_proc_runtype,
1140:
1141: If p_ele_proc_type = 'R' Then /* Not required for NR Elements */
1142:
1143: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',57);
1144: UPDATE pay_element_types_f
1145: SET element_information_category = g_ele_info_cat,
1146: element_information1 = p_ele_cat,
1147: element_information2 = p_partial_dedn,
1148: element_information3 = p_proc_runtype,
1405: -- Set SCL:
1406: -- IF UPPER(p_ele_class_name) = 'INVOLUNTARY DEDUCTIONS' THEN
1407: -- Populate segments 1 w/Category
1408: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1409: UPDATE pay_element_types_f
1410: SET element_information_category = g_ele_info_cat,
1411: element_information1 = p_ele_cat,
1412: element_information2 = p_partial_dedn,
1413: element_information3 = p_proc_runtype,
1416: WHERE element_type_id = p_ele_id
1417: AND business_group_id + 0 = p_bg_id;
1418:
1419: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',83);
1420: UPDATE pay_element_types_f
1421: SET element_information_category = g_ele_info_cat,
1422: element_information1 = p_ele_cat,
1423: element_information2 = p_partial_dedn,
1424: element_information3 = p_proc_runtype
1427:
1428: If p_ele_proc_type = 'R' then /* Not required for NR Elements */
1429:
1430: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',83);
1431: UPDATE pay_element_types_f
1432: SET element_information_category = g_ele_info_cat,
1433: element_information1 = p_ele_cat,
1434: element_information2 = p_partial_dedn,
1435: element_information3 = p_proc_runtype
1772: --
1773: -- Set DDF Segment values:
1774: --
1775: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1776: UPDATE pay_element_types_f
1777: SET element_information_category = g_ele_info_cat,
1778: element_information1 = p_ele_cat,
1779: element_information2 = p_partial_dedn,
1780: element_information3 = p_proc_runtype,
1783: WHERE element_type_id = p_ele_id
1784: AND business_group_id + 0 = p_bg_id;
1785:
1786: hr_utility.set_location('hr_user_init_dedn.ins_dedn_formula_processing',81);
1787: UPDATE pay_element_types_f
1788: SET element_information_category = g_ele_info_cat,
1789: element_information1 = p_ele_cat,
1790: element_information2 = p_partial_dedn,
1791: element_information3 = p_proc_runtype
1795:
1796: If p_ele_proc_type = 'R' then /* Not required for NR Elements */
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
4143: hr_utility.set_location('hr_user_init_dedn.ins_deduction_template',53);
4144:
4145: select distinct(classification_id)
4146: into v_primary_class_id
4147: from pay_element_types_f
4148: where element_type_id = v_ele_type_id;
4149: --
4150: -- Need to update termination rule.(bug 2276457)
4151: --
4148: where element_type_id = v_ele_type_id;
4149: --
4150: -- Need to update termination rule.(bug 2276457)
4151: --
4152: UPDATE pay_element_types_f
4153: SET post_termination_rule = p_termination_rule
4154: WHERE element_type_id = v_ele_type_id;
4155: --
4156: hr_utility.set_location('pyusuiet',130);
4189:
4190: --
4191: -- Need to update termination rule.(bug 2276457)
4192: --
4193: UPDATE pay_element_types_f
4194: SET post_termination_rule = p_termination_rule
4195: WHERE element_type_id = v_inputs_ele_type_id;
4196:
4197: End if; /* Not required for NR Elements */
4221:
4222: --
4223: -- Need to update termination rule.(bug 2276457)
4224: --
4225: UPDATE pay_element_types_f
4226: SET post_termination_rule = p_termination_rule
4227: WHERE element_type_id = v_shadow_ele_type_id;
4228:
4229: --
4318:
4319: ---Inserted by lwthomps for bug 345102. Associate Primary balance with
4320: ---Employer Liabiliity type benefits.
4321:
4322: UPDATE pay_element_types_f
4323: SET ELEMENT_INFORMATION10 = v_er_charge_baltype_id
4324: WHERE element_type_id = v_er_charge_eletype_id;
4325:
4326: -- Bug Fix 5763867
4323: SET ELEMENT_INFORMATION10 = v_er_charge_baltype_id
4324: WHERE element_type_id = v_er_charge_eletype_id;
4325:
4326: -- Bug Fix 5763867
4327: UPDATE pay_element_types_f
4328: SET element_information_category = 'US_EMPLOYER LIABILITIES',
4329: element_information1 = 'B'
4330: WHERE element_type_id = v_er_charge_eletype_id
4331: AND business_group_id + 0 = p_bg_id;
4374: p_effective_date => g_eff_start_date);
4375:
4376: -- Update ele type DDF for primary associated balance.
4377:
4378: UPDATE pay_element_types_f
4379: SET ELEMENT_INFORMATION10 = v_eerefund_baltype_id
4380: WHERE element_type_id = v_eerefund_eletype_id;
4381:
4382: do_defined_balances( p_bal_name => v_balance_name,
4701: -- Other associated balances:
4702: --
4703:
4704: if p_ele_processing_type = 'R' then /* Not required for NR Elements */
4705: UPDATE pay_element_types_f
4706: SET element_information10 = v_bal_type_id,
4707: element_information11 = v_totowed_bal_type_id,
4708: element_information12 = v_arrears_bal_type_id,
4709: element_information13 = v_notaken_bal_type_id,
4716: AND business_group_id + 0 = p_bg_id;
4717:
4718: else /* Not required for NR Elements */
4719:
4720: UPDATE pay_element_types_f
4721: SET element_information10 = v_bal_type_id,
4722: element_information13 = v_notaken_bal_type_id,
4723: element_information19 = v_shadow_ele_type_id
4724: WHERE element_type_id = v_ele_type_id
5010: l_num_assoc_eles := l_num_assoc_eles + 1;
5011:
5012: select element_type_id
5013: into v_employer_match_id
5014: from pay_element_types_f
5015: where element_name like p_ele_name||' ER'
5016: and v_del_sess_date >= effective_start_date
5017: and v_del_sess_date <= effective_end_date
5018: and business_group_id + 0 = p_business_group_id ;
5024: l_num_assoc_eles := l_num_assoc_eles + 1;
5025:
5026: select element_type_id
5027: into v_after_tax_id
5028: from pay_element_types_f
5029: where element_name like p_ele_name||' AT'
5030: AND v_del_sess_date >= effective_start_date
5031: AND v_del_sess_date <= effective_end_date
5032: and business_group_id + 0 = p_business_group_id;
5036: l_num_assoc_eles := l_num_assoc_eles + 1;
5037:
5038: select element_type_id
5039: into v_after_tax_si_id
5040: from pay_element_types_f
5041: where element_name like p_ele_name||' AT Special Inputs'
5042: AND v_del_sess_date >= effective_start_date
5043: AND v_del_sess_date <= effective_end_date
5044: and business_group_id + 0 = p_business_group_id;
5048: l_num_assoc_eles := l_num_assoc_eles + 1;
5049:
5050: select element_type_id
5051: into v_after_tax_sf_id
5052: from pay_element_types_f
5053: where element_name like p_ele_name||' AT Special Features'
5054: AND v_del_sess_date >= effective_start_date
5055: AND v_del_sess_date <= effective_end_date
5056: and business_group_id + 0 = p_business_group_id;
5063: additional_baltype_id, replacement_baltype_id
5064: into v_at_bal_id, v_at_accr_id, v_at_arr_id, v_at_not_taken_id,
5065: v_at_to_bond_id, v_at_addl_id, v_at_repl_id
5066: from pay_all_deduction_types_v padt,
5067: pay_element_types_f pet
5068: WHERE pet.element_type_id = padt.element_type_id
5069: AND padt.element_name like p_ele_name||' AT'
5070: AND v_del_sess_date >= padt.effective_start_date
5071: AND v_del_sess_date <= padt.effective_end_date
5108: l_num_assoc_eles := l_num_assoc_eles + 1;
5109:
5110: select element_type_id
5111: into v_at_er_id
5112: from pay_element_types_f
5113: where element_name like p_ele_name||' AT ER'
5114: AND v_del_sess_date >= effective_start_date
5115: AND v_del_sess_date <= effective_end_date
5116: and business_group_id + 0 = p_business_group_id ;
5357: IF assoc_eles(j) IS NOT NULL THEN
5358:
5359: select processing_priority
5360: into v_assoc_ele_priority
5361: from pay_element_types_f
5362: where element_type_id = assoc_eles(j)
5363: and v_del_sess_date between effective_start_date
5364: and effective_end_date;
5365:
5384: -- Delete element type record:
5385: -- Remember, we're 'ZAP'ing, no need to worry about date-effective delete.
5386: --
5387: hr_utility.set_location('hr_us_garn_gen.delete_dedn', 35);
5388: delete from PAY_ELEMENT_TYPES_F
5389: where element_type_id = assoc_eles(j);
5390:
5391: END IF;
5392:
5436: element name will fetch more than one row and raise error. **/
5437:
5438: SELECT DISTINCT element_type_id
5439: INTO v_eletype_id
5440: FROM pay_element_types_f
5441: WHERE element_name = p_ele_name || ' ER' --Bug 3349594
5442: AND v_del_sess_date >= effective_start_date
5443: AND v_del_sess_date <= effective_end_date
5444: and business_group_id + 0 = p_business_group_id ;
5464: -- Delete element type record:
5465: -- Remember, we're 'ZAP'ing, so no need to worry about date-effective delete.
5466: --
5467: hr_utility.set_location('hr_user_init_dedn.do_deletions', 35);
5468: delete from PAY_ELEMENT_TYPES_F
5469: where element_type_id = v_eletype_id;
5470:
5471: exception when NO_DATA_FOUND then
5472: null;
5506:
5507: hr_utility.set_location('hr_user_init_dedn.do_deletions', 17);
5508: SELECT DISTINCT element_type_id
5509: INTO v_eletype_id
5510: FROM pay_element_types_f
5511: WHERE element_name = p_ele_name || ' REFUND' --Bug 3349594
5512: AND v_del_sess_date >= effective_start_date
5513: AND v_del_sess_date <= effective_end_date
5514: and business_group_id + 0 = p_business_group_id ;
5531: p_val_end_date => v_del_val_end,
5532: p_startup_mode => v_startup_mode);
5533:
5534: hr_utility.set_location('hr_user_init_dedn.do_deletions', 35);
5535: delete from PAY_ELEMENT_TYPES_F
5536: where element_type_id = v_eletype_id;
5537:
5538: exception when NO_DATA_FOUND then
5539: null;
5549: ,p_bg_id in NUMBER) RETURN varchar2 IS
5550:
5551: CURSOR csr_sfx IS
5552: SELECT decode(x.element_name,null,'N','Y')
5553: FROM pay_element_types x, pay_element_types b
5554: WHERE b.element_type_id = p_ele_type_id
5555: AND b.business_group_id + 0 = p_bg_id
5556: AND p_eff_start_date between b.effective_start_date
5557: AND b.effective_end_date