[Home] [Help]
75:
76: -- Cursor to check if the name alreadt exists
77: cursor c_get_name_exists(c_ele_name varchar2) IS
78: select 'Exists'
79: from pay_element_types_f
80: where (upper(element_name) like upper(c_ele_name) OR
81: upper(element_name) like upper(c_ele_name || ' Calculator'))
82: and business_group_id = p_business_grp_id;
83:
271: processing_priority,
272: standard_link_flag,
273: element_information3 processing_runtype,
274: post_termination_rule
275: from pay_element_types_f
276: where element_type_id = p_old_ele_type_id
277: and business_group_id = p_business_grp_id
278: and cp_eff_date between effective_start_date
279: and effective_end_date
285: element_information5, -- Calculator Element
286: element_information8, -- Voluntary Deductions
287: element_information19, -- Special Features
288: element_information20 -- Verifier
289: from pay_element_types_f
290: where element_type_id = cp_ele_type_id
291: and business_group_id = p_business_grp_id;
292:
293:
493: ,l_vol_dedn
494: ,l_spec_feat
495: ,l_verifier;
496: close c_get_new_ele_det;
497: update pay_element_types_f
498: set formula_id = l_skip_rule
499: ,element_information2 = l_elem_inf2
500: ,element_information5 = l_cal_elem
501: ,element_information8 = l_vol_dedn
547: formula_id,
548: processing_rule
549: from pay_status_processing_rules_f
550: where element_type_id in (select element_type_id
551: from pay_element_types_f
552: where element_type_id = cp_ele_type_id
553: and business_group_id = cp_bg_grp_id);
554:
555: -- Get the formula result rules that need to be copied
565: pfrrf.result_rule_type,
566: pfrrf.severity_level,
567: pfrrf.element_type_id
568: from pay_status_processing_rules_f psprf,
569: pay_element_types_f petf,
570: pay_formula_result_rules_f pfrrf
571: where petf.element_type_id = cp_ele_type_id
572: and petf.business_group_id = cp_bg_grp_id
573: and psprf.element_type_id = petf.element_type_id
597: */
598: delete from pay_formula_result_rules_f
599: where status_processing_rule_id in (select distinct status_processing_rule_id
600: from pay_status_processing_rules_f psprf,
601: pay_element_types_f petf
602: where petf.element_type_id = p_old_ele_type_id
603: and psprf.element_type_id = petf.element_type_id
604: and petf.business_group_id = p_business_grp_id);
605: delete from pay_status_processing_rules_f
603: and psprf.element_type_id = petf.element_type_id
604: and petf.business_group_id = p_business_grp_id);
605: delete from pay_status_processing_rules_f
606: where element_type_id in (select distinct element_type_id
607: from pay_element_types_f
608: where element_type_id = p_old_ele_type_id
609: and business_group_id = p_business_grp_id);
610:
611: open c_get_stat_proc_rules(p_new_ele_type_id,
919: cp_bg_grp_id number) IS
920: select status_processing_rule_id
921: from pay_status_processing_rules_f
922: where element_type_id in (select element_type_id
923: from pay_element_types_f
924: where element_name = cp_ele_name
925: and business_group_id = cp_bg_grp_id);
926:
927: -- Cursor to get the input value id of the Any input Value
930: ,cp_business_group_id number
931: ) IS
932: select petf.element_type_id,
933: pivf.input_value_id
934: from pay_element_types_f petf,
935: pay_input_values_f pivf
936: where petf.element_name like cp_ele_name
937: and petf.business_group_id = cp_business_group_id
938: and pivf.element_type_id = petf.element_type_id
1030: IS
1031:
1032: CURSOR c_element_name(cp_element_name varchar2) IS
1033: SELECT element_Type_id
1034: FROM pay_element_Types_f
1035: WHERE element_name = cp_element_name
1036: AND business_group_id = p_business_grp_id;
1037:
1038: l_element_type_id number;
1073: /*
1074: * Deleted the base element
1075: */
1076: DELETE
1077: FROM pay_element_types_f
1078: WHERE element_Type_id = l_element_type_id;
1079:
1080:
1081: -- hr_utility.trace_on(NULL, 'Dedn');
1109: */
1110: update pay_input_values_f pivf
1111: set mandatory_flag = 'X'
1112: where pivf.element_type_id in (select distinct element_type_id
1113: from pay_element_types_f
1114: where element_name = p_ele_name
1115: and business_group_id = p_business_grp_id)
1116: and pivf.name in ('Allowances')
1117: and pivf.business_group_id = p_business_grp_id;
1123: */
1124: update pay_input_values_f pivf
1125: set mandatory_flag = 'X'
1126: where pivf.element_type_id in (select distinct element_type_id
1127: from pay_element_types_f
1128: where element_name = p_ele_name
1129: and business_group_id = p_business_grp_id)
1130: and pivf.name in ('Dedns at Time of Writ',
1131: 'Allowances',
1139: */
1140: update pay_input_values_f pivf
1141: set mandatory_flag = 'X'
1142: where pivf.element_type_id in (select distinct element_type_id
1143: from pay_element_types_f
1144: where element_name = p_ele_name
1145: and business_group_id = p_business_grp_id)
1146: and pivf.name in ('Dedns at Time of Writ',
1147: 'Allowances',
1155: */
1156: update pay_input_values_f pivf
1157: set mandatory_flag = 'X'
1158: where pivf.element_type_id in (select distinct element_type_id
1159: from pay_element_types_f
1160: where element_name = p_ele_name
1161: and business_group_id = p_business_grp_id)
1162: and pivf.name in ('Dedns at Time of Writ',
1163: 'Filing Status',
1173: */
1174: update pay_input_values_f pivf
1175: set mandatory_flag = 'X'
1176: where pivf.element_type_id in (select distinct element_type_id
1177: from pay_element_types_f
1178: where element_name = p_ele_name
1179: and business_group_id = p_business_grp_id)
1180: and pivf.name in ('Percentage',
1181: 'Num Dependents',
1189: */
1190: update pay_input_values_f pivf
1191: set mandatory_flag = 'X'
1192: where pivf.element_type_id in (select distinct element_type_id
1193: from pay_element_types_f
1194: where element_name = p_ele_name
1195: and business_group_id = p_business_grp_id)
1196: and pivf.name in ('Dedns at Time of Writ',
1197: 'Allowances',
1221: -- to it as a parameter. This method is used to multi-thread the whole process
1222: cursor c_get_mig_elem_bg(cp_ele_type_id number) IS
1223: select /*+ Choose */
1224: petf.element_type_id, min(petf.effective_start_date), max(petf.effective_end_date)
1225: from pay_element_types_f petf
1226: where petf.element_type_id = p_elem_type_id
1227: and petf.element_information4 is NULL
1228: group by petf.element_type_id;
1229:
1232: cp_business_grp_id number) IS
1233: select element_name,
1234: element_information1 element_category,
1235: element_information5 calc_ele_type_id
1236: from pay_element_types_f
1237: where element_type_id = cp_ele_type_id
1238: and business_group_id = cp_business_grp_id;
1239:
1240: -- Get the element name
1240: -- Get the element name
1241: cursor c_get_ele_name (cp_ele_type_id number,
1242: cp_business_grp_id number) IS
1243: select element_name
1244: from pay_element_types_f
1245: where element_type_id = cp_ele_type_id
1246: and business_group_id = cp_business_grp_id;
1247:
1248: -- Local Variable Declaration
1335: /*
1336: * Store the New Calcualtor elements element_type_id value in the
1337: * element_information4 column of the Old Base element.
1338: */
1339: update pay_element_types_f
1340: set element_information4 = l_calc_ele_type_id
1341: where element_type_id = l_old_ele_type_id;
1342:
1343: /*
1443: cursor c_get_mig_elem_bg(cp_ele_type_id number,
1444: cp_mig_date date) IS
1445: select /*+ Choose */
1446: 'Upgrade'
1447: from pay_element_types_f petf,
1448: pay_element_classifications pec
1449: where petf.element_type_id = cp_ele_type_id
1450: and petf.legislation_code is NULL
1451: and petf.effective_end_date > cp_mig_date
1459: and petf.element_name not like '%Priority'
1460: and petf.element_information4 is NULL
1461: and petf.element_information1 in ('CS', 'SS', 'AY', 'EL', 'ER', 'BO', 'CD', 'G', 'TL')
1462: and exists (select 'Exists'
1463: from pay_element_types_f petfi
1464: where petfi.element_name like petf.element_name || ' Verifier'
1465: and petfi.business_group_id = petf.business_group_id
1466: and petfi.legislation_code is NULL);
1467: