1 PACKAGE GHR_MRE_PKG AUTHID CURRENT_USER AS
2 /* $Header: ghmreexe.pkh 120.1 2010/04/09 05:11:55 vmididho ship $ */
3
4 mass_error EXCEPTION;
5
6
7 PROCEDURE execute_mre (p_errbuf out NOCOPY varchar2,
8 p_retcode out NOCOPY number,
9 p_mass_realignment_id in number,
10 p_action in varchar2,
11 p_show_vacant_pos in varchar2 default 'NO');
12
13 procedure purge_processed_recs(p_session_id in number,
14 p_err_buf out NOCOPY varchar2);
15
16 --Bug # 9329643 added p_employee_number
17 procedure pop_dtls_from_pa_req(p_person_id in number,p_effective_date in date,
18 p_mass_realignment_id in number, p_employee_number in varchar2);
19
20 function check_select_flg(p_position_id in number,
21 p_action in varchar2,
22 p_effective_date in date,
23 p_mre_id in number,
24 p_sel_flg in out NOCOPY varchar2)
25 return boolean;
26
27 procedure ins_upd_pos_extra_info
28 (p_position_id in number,p_effective_date in date,
29 p_sel_flag in varchar2, p_comment in varchar2,
30 p_mre_id in number);
31
32
33 procedure purge_old_data (p_mass_session_id in number);
34
35 /*
36 PROCEDURE get_duty_station_id (p_duty_station_code IN ghr_duty_stations_v.duty_station_code%TYPE
37 ,p_effective_date IN DATE
38 ,p_duty_station_id OUT ghr_duty_stations_v.duty_station_id%TYPE);
39 */
40
41 procedure update_sel_flg (p_position_id in number,p_effective_date in date);
42
43 FUNCTION check_eligibility(p_org_structure_id in varchar2,
44 p_office_symbol in varchar2,
45 p_personnel_office_id in varchar2,
46 p_agency_sub_element_code in varchar2,
47 p_l_org_structure_id in varchar2,
48 p_l_office_symbol in varchar2,
49 p_l_personnel_office_id in varchar2,
50 p_l_agency_sub_element_code in varchar2,
51 p_person_id in number,
52 p_effective_date in date,
53 p_action in varchar2
54 )
55 return boolean;
56
57 function person_in_pa_req_1noa
58 (p_person_id in number,
59 p_effective_date in date,
60 p_first_noa_code in varchar2
61 )
62 return boolean;
63
64 function person_in_pa_req_2noa
65 (p_person_id in number,
66 p_effective_date in date,
67 p_second_noa_code in varchar2
68 )
69 return boolean;
70
71 procedure get_pos_grp1_ddf (p_position_id in per_assignments_f.position_id%type,
72 p_effective_date in date,
73 p_personnel_office_id out NOCOPY varchar2,
74 p_org_structure_id out NOCOPY varchar2,
75 p_office_symbol out NOCOPY varchar2,
76 p_position_organization out NOCOPY varchar2,
77 p_pos_ei_data OUT NOCOPY per_position_extra_info%rowtype);
78
79 procedure get_pos_grp2_ddf (p_position_id in per_assignments_f.position_id%type,
80 p_effective_date in date,
81 p_org_func_code out NOCOPY varchar2,
82 p_appropriation_code1 out NOCOPY varchar2,
83 p_appropriation_code2 out NOCOPY varchar2);
84 -- p_pos_ei_data OUT per_position_extra_info%rowtype);
85
86 procedure get_new_org_dtls( p_mass_realignment_id in number,
87 p_position_id in number,
88 p_effective_date in date,
89 p_personnel_office_id in out NOCOPY varchar2,
90 p_sub_element_code in out NOCOPY varchar2,
91 p_duty_station_id in out NOCOPY number,
92 p_duty_station_code in out NOCOPY varchar2,
93 p_duty_station_desc in out NOCOPY varchar2,
94 p_duty_station_locn_id in out NOCOPY number,
95 p_office_symbol in out NOCOPY varchar2,
96 p_payroll_office_id in out NOCOPY varchar2,
97 p_org_func_code in out NOCOPY varchar2,
98 p_appropriation_code1 in out NOCOPY varchar2,
99 p_appropriation_code2 in out NOCOPY varchar2,
100 p_position_organization in out NOCOPY varchar2);
101
102 PROCEDURE GET_FIELD_DESC (p_agency_code in varchar2,
103 p_to_agency_code in varchar2,
104 p_approp_code1 in varchar2,
105 p_approp_code2 in varchar2,
106 p_pay_plan in varchar2,
107 p_poi_code in varchar2,
108 p_to_poi_code in varchar2,
109 p_org_id in number,
110 p_to_org_id in number,
111
112 p_agency_desc out NOCOPY varchar2,
113 p_to_agency_desc out NOCOPY varchar2,
114 p_approp_code1_desc out NOCOPY varchar2,
115 p_approp_code2_desc out NOCOPY varchar2,
116 p_pay_plan_desc out NOCOPY varchar2,
117 p_poi_name out NOCOPY varchar2,
118 p_to_poi_name out NOCOPY varchar2,
119 p_org_name out NOCOPY varchar2,
120 p_to_org_name out NOCOPY varchar2);
121
122 FUNCTION GET_FND_COMMON_LOOKUP
123 (p_lookup_code in varchar2,
124 p_type in varchar2) RETURN VARCHAR2;
125
126 function get_mre_name(p_mre_id in number) return varchar2;
127
128 FUNCTION GET_PP_NAME (PP IN VARCHAR2) RETURN VARCHAR2;
129 FUNCTION GET_POI_NAME (P_POI IN VARCHAR2) RETURN VARCHAR2;
130
131 FUNCTION get_organization_name (p_org_id in number) RETURN varchar2;
132
133 PROCEDURE get_extra_info_comments
134 (p_position_id in number,
135 p_effective_date in date,
136 p_sel_flag in out NOCOPY varchar2,
137 p_comments in out NOCOPY varchar2,
138 p_mre_id in out NOCOPY number);
139
140 procedure create_mass_act_prev (
141 p_effective_date in date,
142 p_date_of_birth in date,
143 p_full_name in varchar2,
144 p_national_identifier in varchar2,
145 --Bug # 9329643
146 p_employee_number in varchar2,
147 p_duty_station_code in varchar2,
148 p_duty_station_desc in varchar2,
149 p_personnel_office_id in varchar2,
150 p_position_id in per_assignments_f.position_id%type,
151 p_position_title in varchar2,
152 p_position_number in varchar2,
153 p_position_seq_no in varchar2,
154 p_org_structure_id in varchar2,
155 p_agency_sub_element_code in varchar2,
156 p_person_id in number,
157 p_mass_realignment_id in number,
158 p_sel_flg in varchar2,
159 p_grade_or_level in varchar2,
160 p_step_or_rate in varchar2,
161 p_pay_plan in varchar2,
162 p_occ_series in varchar2,
163 p_office_symbol in varchar2,
164 p_organization_id in number,
165 p_organization_name in varchar2,
166 p_positions_organization in varchar2,
167 t_personnel_office_id in varchar2,
168 t_sub_element_code in varchar2,
169 t_duty_station_id in number,
170 t_duty_station_code in varchar2,
171 t_duty_station_desc in varchar2,
172 t_office_symbol in varchar2,
173 t_payroll_office_id in varchar2,
174 t_org_func_code in varchar2,
175 t_appropriation_code1 in varchar2,
176 t_appropriation_code2 in varchar2,
177 t_position_organization in varchar2,
178 p_action in varchar2,
179 p_assignment_id in number,
180 p_pay_rate_determinant in varchar2);
181
182
183 PROCEDURE assign_to_sf52_rec(
184 p_person_id in number,
185 p_first_name in varchar2,
186 p_last_name in varchar2,
187 p_middle_names in varchar2,
188 p_national_identifier in varchar2,
189 p_date_of_birth in date,
190 p_effective_date in date,
191 p_assignment_id in number,
192 p_tenure in varchar2,
193 p_step_or_rate in varchar2,
194 p_annuitant_indicator in varchar2,
195 p_pay_rate_determinant in varchar2,
196 p_work_schedule in varchar2,
197 p_part_time_hour in varchar2,
198 p_flsa_category in varchar2,
199 p_bargaining_unit_status in varchar2,
200 p_functional_class in varchar2,
201 p_supervisory_status in varchar2,
202 p_personnel_office_id in varchar2,
203 p_sub_element_code in varchar2,
204 p_duty_station_id in number,
205 p_duty_station_locn_id in number,
206 p_duty_station_code in ghr_pa_requests.duty_station_code%type,
207 p_duty_station_desc in ghr_pa_requests.duty_station_desc%type,
208 p_office_symbol in varchar2,
209 p_payroll_office_id in varchar2,
210 p_org_func_code in varchar2,
211 p_appropriation_code1 in varchar2,
212 p_appropriation_code2 in varchar2,
213 p_position_organization in varchar2,
214 p_lac_sf52_rec in ghr_pa_requests%rowtype,
215 p_sf52_rec out NOCOPY ghr_pa_requests%rowtype);
216
217 procedure upd_ext_info_to_null(p_position_id in number, p_effective_date in date);
218
219 PROCEDURE upd_ext_info_api (p_position_id in number,
220 info5 in varchar2,
221 info6 in varchar2,
222 info7 in varchar2,
223 info8 in varchar2,
224 info9 in varchar2,
225 info10 in varchar2,
226 info11 in varchar2,
227 info12 in varchar2,
228 info13 in varchar2,
229 info18 in varchar2,
230 p_effective_date in date);
231
232 procedure pr (msg varchar2,par1 in varchar2 default null,
233 par2 in varchar2 default null);
234
235 END GHR_MRE_PKG;
236