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