[Home] [Help]
92: p_effective_date date) RETURN BOOLEAN
93: IS
94: l_new_element_name varchar2(250);
95: l_business_group_id per_all_people_f.business_group_id%type;
96: l_element_type_id pay_element_types_f.element_type_id%type;
97: l_exists BOOLEAN;
98:
99: CURSOR c_element_exist(c_assignment_id per_all_assignments_f.assignment_id%type,
100: c_effective_date per_all_assignments_f.effective_start_date%type,
97: l_exists BOOLEAN;
98:
99: CURSOR c_element_exist(c_assignment_id per_all_assignments_f.assignment_id%type,
100: c_effective_date per_all_assignments_f.effective_start_date%type,
101: c_element_type_id pay_element_types_f.element_type_id%type)
102: IS
103: SELECT 1
104: FROM pay_element_entries_f ele
105: WHERE ele.assignment_id = c_assignment_id
105: WHERE ele.assignment_id = c_assignment_id
106: AND c_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
107: AND element_type_id = c_element_type_id;
108:
109: CURSOR c_element_type_id (c_element_name pay_element_types_f.element_name%type,
110: c_business_group_id pay_element_links_f.business_group_id%type,
111: c_effective_date per_all_assignments_f.effective_start_date%type)
112: IS
113: SELECT elt.element_type_id
110: c_business_group_id pay_element_links_f.business_group_id%type,
111: c_effective_date per_all_assignments_f.effective_start_date%type)
112: IS
113: SELECT elt.element_type_id
114: FROM pay_element_links eli, pay_element_types_f elt
115: WHERE elt.element_type_id = eli.element_type_id
116: AND eli.business_group_id = c_business_group_id
117: AND elt.element_name = c_element_name;
118: BEGIN
763: ) IS
764:
765:
766: --Bug #12553476 Modified c_element cursor to map the effective date comparison
767: CURSOR c_element(c_element_name pay_element_types_f.element_name%type,
768: c_assignment_id per_all_assignments_f.assignment_id%type,
769: c_effective_date pay_element_entries_f.effective_start_date%type,
770: c_business_group_id pay_element_links_f.business_group_id%type
771: ) is
778: e.assignment_id assignment_id,
779: e.object_version_number object_version_number,
780: f.element_entry_value_id element_entry_value_id,
781: f.screen_entry_value screen_entry_value
782: FROM pay_element_types_f a,
783: pay_input_values_f b,
784: pay_element_entries_f e,
785: pay_element_entry_values_f f,
786: pay_element_links_f g
811: CURSOR c_element_name(c_element_link_id pay_element_links_f.element_link_id%type,
812: c_effective_date pay_element_links_f.effective_start_date%type,
813: c_business_group_id pay_element_links_f.business_group_id%type) IS
814: SELECT element_name
815: FROM pay_element_types_f pet, pay_element_links_f pel
816: where pet.element_type_id = pel.element_type_id
817: and c_effective_date between pel.effective_start_date and pel.effective_end_date
818: and pel.business_group_id = c_business_group_id
819: and pel.element_link_id = c_element_link_id;
831: l_pre_tax_waiver varchar2(20);
832: l_premium_rate varchar2(20);
833: l_per_benefit_info per_people_extra_info%rowtype;
834: l_person_id per_all_people_f.person_id%type;
835: l_new_element_name pay_element_types_f.element_name%type;
836: l_element_name pay_element_types_f.element_name%type;
837: l_ret_plan_name varchar2(200);
838: l_retirement_plan ghr_pa_requests.retirement_plan%type;
839: l_debug_mode BOOLEAN;
832: l_premium_rate varchar2(20);
833: l_per_benefit_info per_people_extra_info%rowtype;
834: l_person_id per_all_people_f.person_id%type;
835: l_new_element_name pay_element_types_f.element_name%type;
836: l_element_name pay_element_types_f.element_name%type;
837: l_ret_plan_name varchar2(200);
838: l_retirement_plan ghr_pa_requests.retirement_plan%type;
839: l_debug_mode BOOLEAN;
840:
850: p_bg_id in number)
851: IS
852: select ele.element_entry_id,
853: ele.object_version_number
854: from pay_element_types_f elt,
855: pay_element_links_f eli,
856: pay_element_entries_f ele
857: where ele.assignment_id = p_assignment_id
858: and upper(elt.element_name) = upper('TSP Roth 401K')