[Home] [Help]
82:
83: -- Cursor to check if the name alreadt exists
84: cursor c_get_name_exists(c_ele_name varchar2) IS
85: select 'Exists'
86: from pay_element_types_f
87: where (upper(element_name) like upper(c_ele_name) OR
88: upper(element_name) like upper(c_ele_name || ' Calculator'))
89: and business_group_id = p_business_grp_id;
90:
278: processing_priority,
279: standard_link_flag,
280: element_information3 processing_runtype,
281: post_termination_rule
282: from pay_element_types_f
283: where element_type_id = p_old_ele_type_id
284: and business_group_id = p_business_grp_id
285: and cp_eff_date between effective_start_date
286: and effective_end_date
292: element_information5, -- Calculator Element
293: element_information8, -- Voluntary Deductions
294: element_information19, -- Special Features
295: element_information20 -- Verifier
296: from pay_element_types_f
297: where element_type_id = cp_ele_type_id
298: and business_group_id = p_business_grp_id;
299:
300:
500: ,l_vol_dedn
501: ,l_spec_feat
502: ,l_verifier;
503: close c_get_new_ele_det;
504: update pay_element_types_f
505: set formula_id = l_skip_rule
506: ,element_information2 = l_elem_inf2
507: ,element_information5 = l_cal_elem
508: ,element_information8 = l_vol_dedn
554: formula_id,
555: processing_rule
556: from pay_status_processing_rules_f
557: where element_type_id in (select element_type_id
558: from pay_element_types_f
559: where element_type_id = cp_ele_type_id
560: and business_group_id = cp_bg_grp_id);
561:
562: -- Get the formula result rules that need to be copied
572: pfrrf.result_rule_type,
573: pfrrf.severity_level,
574: pfrrf.element_type_id
575: from pay_status_processing_rules_f psprf,
576: pay_element_types_f petf,
577: pay_formula_result_rules_f pfrrf
578: where petf.element_type_id = cp_ele_type_id
579: and petf.business_group_id = cp_bg_grp_id
580: and psprf.element_type_id = petf.element_type_id
604: */
605: delete from pay_formula_result_rules_f
606: where status_processing_rule_id in (select distinct status_processing_rule_id
607: from pay_status_processing_rules_f psprf,
608: pay_element_types_f petf
609: where petf.element_type_id = p_old_ele_type_id
610: and psprf.element_type_id = petf.element_type_id
611: and petf.business_group_id = p_business_grp_id);
612: delete from pay_status_processing_rules_f
610: and psprf.element_type_id = petf.element_type_id
611: and petf.business_group_id = p_business_grp_id);
612: delete from pay_status_processing_rules_f
613: where element_type_id in (select distinct element_type_id
614: from pay_element_types_f
615: where element_type_id = p_old_ele_type_id
616: and business_group_id = p_business_grp_id);
617:
618: open c_get_stat_proc_rules(p_new_ele_type_id,
926: cp_bg_grp_id number) IS
927: select status_processing_rule_id
928: from pay_status_processing_rules_f
929: where element_type_id in (select element_type_id
930: from pay_element_types_f
931: where element_name = cp_ele_name
932: and business_group_id = cp_bg_grp_id);
933:
934: -- Cursor to get the input value id of the Any input Value
937: ,cp_business_group_id number
938: ) IS
939: select petf.element_type_id,
940: pivf.input_value_id
941: from pay_element_types_f petf,
942: pay_input_values_f pivf
943: where petf.element_name like cp_ele_name
944: and petf.business_group_id = cp_business_group_id
945: and pivf.element_type_id = petf.element_type_id
1037: IS
1038:
1039: CURSOR c_element_name(cp_element_name varchar2) IS
1040: SELECT element_Type_id
1041: FROM pay_element_Types_f
1042: WHERE element_name = cp_element_name
1043: AND business_group_id = p_business_grp_id;
1044:
1045: l_element_type_id number;
1080: /*
1081: * Deleted the base element
1082: */
1083: DELETE
1084: FROM pay_element_types_f
1085: WHERE element_Type_id = l_element_type_id;
1086:
1087:
1088: -- hr_utility.trace_on(NULL, 'Dedn');
1116: */
1117: update pay_input_values_f pivf
1118: set mandatory_flag = 'X'
1119: where pivf.element_type_id in (select distinct element_type_id
1120: from pay_element_types_f
1121: where element_name = p_ele_name
1122: and business_group_id = p_business_grp_id)
1123: and pivf.name in ('Allowances')
1124: and pivf.business_group_id = p_business_grp_id;
1130: */
1131: update pay_input_values_f pivf
1132: set mandatory_flag = 'X'
1133: where pivf.element_type_id in (select distinct element_type_id
1134: from pay_element_types_f
1135: where element_name = p_ele_name
1136: and business_group_id = p_business_grp_id)
1137: and pivf.name in ('Dedns at Time of Writ',
1138: 'Allowances',
1146: */
1147: update pay_input_values_f pivf
1148: set mandatory_flag = 'X'
1149: where pivf.element_type_id in (select distinct element_type_id
1150: from pay_element_types_f
1151: where element_name = p_ele_name
1152: and business_group_id = p_business_grp_id)
1153: and pivf.name in ('Dedns at Time of Writ',
1154: 'Allowances',
1162: */
1163: update pay_input_values_f pivf
1164: set mandatory_flag = 'X'
1165: where pivf.element_type_id in (select distinct element_type_id
1166: from pay_element_types_f
1167: where element_name = p_ele_name
1168: and business_group_id = p_business_grp_id)
1169: and pivf.name in ('Dedns at Time of Writ',
1170: 'Filing Status',
1180: */
1181: update pay_input_values_f pivf
1182: set mandatory_flag = 'X'
1183: where pivf.element_type_id in (select distinct element_type_id
1184: from pay_element_types_f
1185: where element_name = p_ele_name
1186: and business_group_id = p_business_grp_id)
1187: and pivf.name in ('Percentage',
1188: 'Num Dependents',
1196: */
1197: update pay_input_values_f pivf
1198: set mandatory_flag = 'X'
1199: where pivf.element_type_id in (select distinct element_type_id
1200: from pay_element_types_f
1201: where element_name = p_ele_name
1202: and business_group_id = p_business_grp_id)
1203: and pivf.name in ('Dedns at Time of Writ',
1204: 'Allowances',
1228: -- to it as a parameter. This method is used to multi-thread the whole process
1229: cursor c_get_mig_elem_bg(cp_ele_type_id number) IS
1230: select /*+ Choose */
1231: petf.element_type_id, min(petf.effective_start_date), max(petf.effective_end_date)
1232: from pay_element_types_f petf
1233: where petf.element_type_id = p_elem_type_id
1234: and petf.element_information4 is NULL
1235: group by petf.element_type_id;
1236:
1240: select element_name,
1241: element_information1 element_category,
1242: element_information5 calc_ele_type_id,
1243: element_information18 spl_inp_ele_type_id -- Added for #8972240
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: -- Get the element name
1248: -- Get the element name
1249: cursor c_get_ele_name (cp_ele_type_id number,
1250: cp_business_grp_id number) IS
1251: select element_name
1252: from pay_element_types_f
1253: where element_type_id = cp_ele_type_id
1254: and business_group_id = cp_business_grp_id;
1255:
1256: -- Local Variable Declaration
1349:
1350: /* #8972240 Also updated the special input element type id value
1351: * in the element_information18 column of the Old Base element.
1352: */
1353: update pay_element_types_f
1354: set element_information4 = l_calc_ele_type_id,
1355: element_information18 = l_spl_inp_ele_type_id -- Added for #8972240
1356: where element_type_id = l_old_ele_type_id;
1357:
1458: cursor c_get_mig_elem_bg(cp_ele_type_id number,
1459: cp_mig_date date) IS
1460: select /*+ Choose */
1461: 'Upgrade'
1462: from pay_element_types_f petf,
1463: pay_element_classifications pec
1464: where petf.element_type_id = cp_ele_type_id
1465: and petf.legislation_code is NULL
1466: and petf.effective_end_date > cp_mig_date
1474: and petf.element_name not like '%Priority'
1475: and petf.element_information4 is NULL
1476: and petf.element_information1 in ('CS', 'SS', 'AY', 'EL', 'ER', 'BO', 'CD', 'G', 'TL')
1477: and exists (select 'Exists'
1478: from pay_element_types_f petfi
1479: where petfi.element_name like petf.element_name || ' Verifier'
1480: and petfi.business_group_id = petf.business_group_id
1481: and petfi.legislation_code is NULL);
1482: