[Home] [Help]
PACKAGE: APPS.GHR_API
Source
1 PACKAGE ghr_api AUTHID CURRENT_USER AS
2 /* $Header: ghapiapi.pkh 120.16.12020000.7 2012/12/20 06:30:23 utokachi ship $ */
3 --
4 -- Internal ghr_api globals
5 --
6 g_package constant varchar2(33) := ' ghr_api.';
7 g_api_dml boolean := false; -- Global api dml status
8
9 -- Record Types
10 -- Provided by Levin Mootoosamy
11 --
12 type extra_info_rec_type is record
13 (l_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type
14 ,l_object_version_number per_assignment_extra_info.object_version_number%type
15 ,l_information1 per_assignment_extra_info.aei_information1%type
16 ,l_information2 per_assignment_extra_info.aei_information2%type
17 ,l_information3 per_assignment_extra_info.aei_information3%type
18 ,l_information4 per_assignment_extra_info.aei_information4%type
19 ,l_information5 per_assignment_extra_info.aei_information5%type
20 ,l_information6 per_assignment_extra_info.aei_information6%type
21 ,l_information7 per_assignment_extra_info.aei_information7%type
22 ,l_information8 per_assignment_extra_info.aei_information8%type
23 ,l_information9 per_assignment_extra_info.aei_information9%type
24 ,l_information10 per_assignment_extra_info.aei_information10%type
25 ,l_information11 per_assignment_extra_info.aei_information11%type
26 ,l_information12 per_assignment_extra_info.aei_information12%type
27 ,l_information13 per_assignment_extra_info.aei_information13%type
28 ,l_information14 per_assignment_extra_info.aei_information14%type
29 ,l_information15 per_assignment_extra_info.aei_information15%type
30 ,l_information16 per_assignment_extra_info.aei_information16%type
31 ,l_information17 per_assignment_extra_info.aei_information17%type
32 ,l_information18 per_assignment_extra_info.aei_information18%type
33 ,l_information19 per_assignment_extra_info.aei_information19%type
34 ,l_information20 per_assignment_extra_info.aei_information20%type
35 ,l_information21 per_assignment_extra_info.aei_information21%type
36 ,l_information22 per_assignment_extra_info.aei_information22%type
37 ,l_information23 per_assignment_extra_info.aei_information23%type
38 ,l_information24 per_assignment_extra_info.aei_information24%type
39 ,l_information25 per_assignment_extra_info.aei_information25%type
40 ,l_information26 per_assignment_extra_info.aei_information26%type
41 ,l_information27 per_assignment_extra_info.aei_information27%type
42 ,l_information28 per_assignment_extra_info.aei_information28%type
43 ,l_information29 per_assignment_extra_info.aei_information29%type
44 ,l_information30 per_assignment_extra_info.aei_information30%type);
45 --
46 type generic_extra_info_rec_type is record
47 (l_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type
48 ,l_object_version_number per_assignment_extra_info.object_version_number%type
49 ,l_information1 per_assignment_extra_info.aei_information1%type
50 ,l_information2 per_assignment_extra_info.aei_information2%type
51 ,l_information3 per_assignment_extra_info.aei_information3%type
52 ,l_information4 per_assignment_extra_info.aei_information4%type
53 ,l_information5 per_assignment_extra_info.aei_information5%type
54 ,l_information6 per_assignment_extra_info.aei_information6%type
55 ,l_information7 per_assignment_extra_info.aei_information7%type
56 ,l_information8 per_assignment_extra_info.aei_information8%type
57 ,l_information9 per_assignment_extra_info.aei_information9%type
58 ,l_information10 per_assignment_extra_info.aei_information10%type
59 ,l_information11 per_assignment_extra_info.aei_information11%type
60 ,l_information12 per_assignment_extra_info.aei_information12%type
61 ,l_information13 per_assignment_extra_info.aei_information13%type
62 ,l_information14 per_assignment_extra_info.aei_information14%type
63 ,l_information15 per_assignment_extra_info.aei_information15%type
64 ,l_information16 per_assignment_extra_info.aei_information16%type
65 ,l_information17 per_assignment_extra_info.aei_information17%type
66 ,l_information18 per_assignment_extra_info.aei_information18%type
67 ,l_information19 per_assignment_extra_info.aei_information19%type
68 ,l_information20 per_assignment_extra_info.aei_information20%type
69 ,l_information21 per_assignment_extra_info.aei_information21%type
70 ,l_information22 per_assignment_extra_info.aei_information22%type
71 ,l_information23 per_assignment_extra_info.aei_information23%type
72 ,l_information24 per_assignment_extra_info.aei_information24%type
73 ,l_information25 per_assignment_extra_info.aei_information25%type
74 ,l_information26 per_assignment_extra_info.aei_information26%type
75 ,l_information27 per_assignment_extra_info.aei_information27%type
76 ,l_information28 per_assignment_extra_info.aei_information28%type
77 ,l_information29 per_assignment_extra_info.aei_information29%type
78 ,l_information30 per_assignment_extra_info.aei_information30%type);
79 --
80 type asg_sf52_type is record
81 (asg_sf52_flag varchar2(1)
82 ,assignment_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type
83 ,object_version_number per_assignment_extra_info.object_version_number%type
84 ,step_or_rate per_assignment_extra_info.aei_information3%type
85 ,tenure per_assignment_extra_info.aei_information4%type
86 ,annuitant_indicator per_assignment_extra_info.aei_information5%type
87 ,pay_rate_determinant per_assignment_extra_info.aei_information6%type
88 ,work_schedule per_assignment_extra_info.aei_information7%type
89 ,part_time_hours per_assignment_extra_info.aei_information8%type
90 -- FWFA Changes Bug#4444609
91 ,calc_pay_table per_assignment_extra_info.aei_information9%type
92 -- FWFA Changes
93 );
94 --
95 -- Bug 2431934
96 type ghr_gbx_tab_type is table of ghr_groupboxes%rowtype
97 index by binary_integer;
98 ghr_gbx_tab ghr_gbx_tab_type;
99 --BUG # 14479249 added to store old group box details
100 ghr_gbx_old_tab ghr_gbx_tab_type;
101 ghr_gbx_index binary_integer;
102 type ghr_gbx_user_tab_type is table of ghr_groupbox_users%rowtype
103 index by binary_integer;
104 ghr_gbx_user_new_tab ghr_gbx_user_tab_type;
105 ghr_gbx_user_old_tab ghr_gbx_user_tab_type;
106 ghr_gbx_user_index binary_integer;
107 -- Bug 2431934
108
109 type asg_non_sf52_type is record
110 (asg_non_sf52_flag varchar2(1)
111 ,assignment_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type
112 ,object_version_number per_assignment_extra_info.object_version_number%type
113 ,date_arr_personnel_office per_assignment_extra_info.aei_information3%type
114 ,duty_status per_assignment_extra_info.aei_information4%type
115 ,key_emer_essential_empl per_assignment_extra_info.aei_information5%type
116 ,non_disc_agmt_status per_assignment_extra_info.aei_information6%type
117 ,date_wtop_exemp_expires per_assignment_extra_info.aei_information7%type
118 ,parttime_indicator per_assignment_extra_info.aei_information8%type
119 ,qualification_standard_waiver per_assignment_extra_info.aei_information9%type
120 ,trainee_promotion_id per_assignment_extra_info.aei_information10%type
121 ,date_trainee_promotion_expt per_assignment_extra_info.aei_information11%type
122 --Bug # 12652438 added new segment pay user ra status
123 ,pay_status_userra_status per_assignment_extra_info.aei_information14%type);
124 --
125 type asg_nte_dates_type is record
126 (asg_nte_dates_flag varchar2(1)
127 ,assignment_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type
128 ,object_version_number per_assignment_extra_info.object_version_number%type
129 ,asg_nte_start_date per_assignment_extra_info.aei_information3%type
130 ,assignment_nte per_assignment_extra_info.aei_information4%type
131 ,lwop_nte_start_date per_assignment_extra_info.aei_information5%type
132 ,lwop_nte per_assignment_extra_info.aei_information6%type
133 ,suspension_nte_start_date per_assignment_extra_info.aei_information7%type
134 ,suspension_nte per_assignment_extra_info.aei_information8%type
135 ,furlough_nte_Start_date per_assignment_extra_info.aei_information9%type
136 ,furlough_nte per_assignment_extra_info.aei_information10%type
137 ,lwp_nte_start_date per_assignment_extra_info.aei_information11%type
138 ,lwp_nte per_assignment_extra_info.aei_information12%type
139 ,sabatical_nte per_assignment_extra_info.aei_information13%type
140 ,sabatical_nte_start_date per_assignment_extra_info.aei_information14%type
141 ,assignment_number per_assignment_extra_info.aei_information15%type
142 ,position_change_nte per_assignment_extra_info.aei_information16%type);
143
144 --Begin Bug# 8653508
145 type asg_detail_info_type is record
146 (asg_detail_info_flag varchar2(1)
147 ,assignment_extra_info_id per_assignment_extra_info.assignment_extra_info_id%type
148 ,object_version_number per_assignment_extra_info.object_version_number%type
149 ,detail_begin_date per_assignment_extra_info.aei_information1%type
150 ,detail_end_date per_assignment_extra_info.aei_information2%type
151 ,detail_type per_assignment_extra_info.aei_information3%type
152 ,unclss_duty_ind per_assignment_extra_info.aei_information4%type
153 ,position_name per_assignment_extra_info.aei_information5%type
154 ,valid_grade per_assignment_extra_info.aei_information6%type
155 ,occ_series per_assignment_extra_info.aei_information7%type
156 ,appropriation_code per_assignment_extra_info.aei_information8%type
157 ,flsa_category per_assignment_extra_info.aei_information9%type
158 ,bargaining_unit_status per_assignment_extra_info.aei_information10%type
159 ,work_schedule per_assignment_extra_info.aei_information11%type
160 ,duty_location per_assignment_extra_info.aei_information12%type
161 ,organization per_assignment_extra_info.aei_information13%type
162 );
163 --End Bug# 8653508
164 --
165 type loc_info_type is record
166 (loc_info_flag varchar2(1)
167 ,location_extra_info_id hr_location_extra_info.location_extra_info_id%type
168 ,object_version_number hr_location_extra_info.object_version_number%type
169 ,duty_station_id hr_location_extra_info.lei_information3%type
170 );
171 --
172 type per_sf52_type is record
173 (per_sf52_flag varchar2(1)
174 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
175 ,object_version_number per_people_extra_info.object_version_number%type
176 ,citizenship per_people_extra_info.pei_information3%type
177 ,veterans_preference per_people_extra_info.pei_information4%type
178 ,veterans_preference_for_rif per_people_extra_info.pei_information5%type
179 ,veterans_status per_people_extra_info.pei_information6%type);
180 --
181 -- Bug#4486823 added retention_incentive_review_date
182 -- --Bug# 4941984(AFHR2) added columns org_appointment_desc1,org_appointment_desc2
183 type per_group1_type is record
184 ( per_group1_flag varchar2(1)
185 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
186 ,object_version_number per_people_extra_info.object_version_number%type
187 ,appointment_type per_people_extra_info.pei_information3%type
188 ,type_of_employment per_people_extra_info.pei_information4%type
189 ,race_national_origin per_people_extra_info.pei_information5%type
190 ,date_last_promotion per_people_extra_info.pei_information6%type
191 ,promotion_eligibility_date per_people_extra_info.pei_information29%type --Bug# 13942237,16023085
192 ,agency_code_transfer_from per_people_extra_info.pei_information7%type
193 ,org_appointment_auth_code1 per_people_extra_info.pei_information8%type
194 ,org_appointment_desc1 per_people_extra_info.pei_information22%type
195 ,org_appointment_auth_code2 per_people_extra_info.pei_information9%type
196 ,org_appointment_desc2 per_people_extra_info.pei_information23%type
197 ,country_world_citizenship per_people_extra_info.pei_information10%type
198 ,handicap_code per_people_extra_info.pei_information11%type
199 ,consent_id per_people_extra_info.pei_information12%type
200 ,date_fehb_eligibility_expires per_people_extra_info.pei_information13%type
201 ,date_temp_eligibility_fehb per_people_extra_info.pei_information14%type
202 ,date_febh_dependent_cert_exp per_people_extra_info.pei_information15%type
203 ,family_member_emp_pref per_people_extra_info.pei_information16%type
204 ,family_member_status per_people_extra_info.pei_information17%type
205 ,retention_inc_review_date per_people_extra_info.pei_information21%type);
206 --
207
208 --BUG #14276354 added pathways segments
209 type per_group2_type is record
210 (per_group2_flag varchar2(1)
211 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
212 ,object_version_number per_people_extra_info.object_version_number%type
213 ,obligated_position_number per_people_extra_info.pei_information3%type
214 ,obligated_position_type per_people_extra_info.pei_information4%type
215 ,date_overseas_tour_expires per_people_extra_info.pei_information5%type
216 ,date_return_rights_expires per_people_extra_info.pei_information6%type
217 ,date_stat_return_rights_expir per_people_extra_info.pei_information7%type
218 ,civilian_duty_stat_contigency per_people_extra_info.pei_information8%type
219 ,date_travel_agmt_pcs_expires per_people_extra_info.pei_information9%type
220 ,draw_down_action_id per_people_extra_info.pei_information10%type
221 ,pathways_program_start_date per_people_extra_info.pei_information11%type
222 ,pathways_program_extn_date per_people_extra_info.pei_information12%type
223 ,pathways_program_end_date per_people_extra_info.pei_information13%type);
224 --
225 -- 4352589 BEN_EIT Created new benefits EIT Type
226 type per_benefit_info_type is record
227 ( per_benefit_info_flag varchar2(1)
228 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
229 ,object_version_number per_people_extra_info.object_version_number%type
230 ,FEGLI_Date_Eligibility_Expires per_people_extra_info.pei_information3%type
231 ,FEHB_Date_Eligibility_expires per_people_extra_info.pei_information4%type
232 ,FEHB_Date_temp_eligibility per_people_extra_info.pei_information5%type
233 ,FEHB_Date_dependent_cert_expir per_people_extra_info.pei_information6%type
234 ,FEHB_LWOP_contingency_st_date per_people_extra_info.pei_information7%type
235 ,FEHB_LWOP_contingency_end_date per_people_extra_info.pei_information8%type
236 ,FEHB_Child_equiry_court_date per_people_extra_info.pei_information10%type
237 ,FERS_Date_eligibility_expires per_people_extra_info.pei_information11%type
238 ,FERS_Election_Date per_people_extra_info.pei_information12%type
239 ,FERS_Election_Indicator per_people_extra_info.pei_information13%type
240 ,TSP_Agncy_Contrib_Elig_date per_people_extra_info.pei_information14%type
241 ,TSP_Emp_Contrib_Elig_date per_people_extra_info.pei_information15%type
242 -- 6312144 addition of new segments related to per_benefit_info
243 ,FEGLI_Assignment_Ind per_people_extra_info.pei_information16%type
244 ,FEGLI_Post_Elec_Basic_Ins_Amt per_people_extra_info.pei_information17%type
245 ,FEGLI_Court_Order_Ind per_people_extra_info.pei_information18%type
246 ,Desg_FEGLI_Benf_Ind per_people_extra_info.pei_information19%type
247 ,FEHB_Event_Code per_people_extra_info.pei_information20%type);
248 --
249 type per_scd_info_type is record
250 (per_scd_info_flag varchar2(1)
251 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
252 ,object_version_number per_people_extra_info.object_version_number%type
253 ,scd_leave per_people_extra_info.pei_information3%type
254 ,scd_civilian per_people_extra_info.pei_information4%type
255 ,scd_rif per_people_extra_info.pei_information5%type
256 ,scd_tsp per_people_extra_info.pei_information6%type
257 ,scd_retirement per_people_extra_info.pei_information7%type
258 -- Bug 4164083 eHRI New Attributions
259 ,scd_ses per_people_extra_info.pei_information10%type
260 ,scd_spl_retirement per_people_extra_info.pei_information11%type
261 -- End Bug 4164083
262 --Bug 4443968
263 ,scd_creditable_svc_annl_leave per_people_extra_info.pei_information12%type
264 ,scd_Length_of_Service per_people_extra_info.pei_information13%type --Bug# 14514445
265 );
266 --
267 type per_probations_type is record
268 (per_probation_flag varchar2(1)
269 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
270 ,object_version_number per_people_extra_info.object_version_number%type
271 ,date_prob_trial_period_begin per_people_extra_info.pei_information3%type
272 ,date_prob_trial_period_ends per_people_extra_info.pei_information4%type
273 ,date_spvr_mgr_prob_begins per_people_extra_info.pei_information8%type--Bug# 4588575
274 ,date_spvr_mgr_prob_ends per_people_extra_info.pei_information5%type
275 ,spvr_mgr_prob_completion per_people_extra_info.pei_information6%type
276 ,date_ses_prob_expires per_people_extra_info.pei_information7%type);
277 --
278 type per_retained_grade_type is record
279 (per_retained_grade_flag varchar2(1)
280 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
281 ,object_version_number per_people_extra_info.object_version_number%type
282 ,date_from per_people_extra_info.pei_information1%type
283 ,date_to per_people_extra_info.pei_information2%type
284 ,retain_grade per_people_extra_info.pei_information3%type
285 ,retain_step_or_rate per_people_extra_info.pei_information4%type
286 ,retain_pay_plan per_people_extra_info.pei_information5%type
287 ,retain_pay_table_id per_people_extra_info.pei_information6%type
288 ,retain_locality_percent per_people_extra_info.pei_information7%type
289 ,retain_pay_basis per_people_extra_info.pei_information8%type
290 ,temp_step per_people_extra_info.pei_information9%type
291 );
292 --
293 type per_sep_retire_type is record
294 (per_sep_retire_flag varchar2(1)
295 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
296 ,object_version_number per_people_extra_info.object_version_number%type
297 ,fers_coverage per_people_extra_info.pei_information3%type
298 ,prev_retirement_coverage per_people_extra_info.pei_information4%type
299 ,frozen_service per_people_extra_info.pei_information5%type
300 ,naf_retirement_indicator per_people_extra_info.pei_information6%type
301 ,reason_for_separation per_people_extra_info.pei_information7%type
302 ,agency_code_transfer_to per_people_extra_info.pei_information8%type
303 ,date_projected_retirement per_people_extra_info.pei_information9%type
304 ,mandatory_retirement_date per_people_extra_info.pei_information10%type
305 ,separate_pkg_status_indicator per_people_extra_info.pei_information11%type
306 ,separate_pkg_register_number per_people_extra_info.pei_information12%type
307 ,separate_pkg_pay_office_id per_people_extra_info.pei_information13%type
308 ,date_ret_appl_received per_people_extra_info.pei_information14%type
309 ,date_ret_pkg_sent_to_payroll per_people_extra_info.pei_information15%type
310 ,date_ret_pkg_recv_payroll per_people_extra_info.pei_information16%type
311 ,date_ret_pkg_to_opm per_people_extra_info.pei_information17%type);
312 --
313 type per_security_type is record
314 (per_security_flag varchar2(1)
315 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
316 ,object_version_number per_people_extra_info.object_version_number%type
317 ,sec_investigation_basis per_people_extra_info.pei_information3%type
318 ,type_of_sec_investigation per_people_extra_info.pei_information4%type
319 ,date_sec_invest_required per_people_extra_info.pei_information5%type
320 ,date_sec_invest_completed per_people_extra_info.pei_information6%type
321 ,personnel_sec_clearance per_people_extra_info.pei_information7%type
322 ,sec_clearance_eligilb_date per_people_extra_info.pei_information8%type
323 ,prp_sci_status_employment per_people_extra_info.pei_information9%type);
324 --
325 -- Bug#4486823 RRR Changes -- Added per_service_oblig_type record
326 type per_service_oblig_type is record
327 (per_service_oblig_flag varchar2(1)
328 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
329 ,object_version_number per_people_extra_info.object_version_number%type
330 ,service_oblig_type_code per_people_extra_info.pei_information3%type
331 ,service_oblig_start_date per_people_extra_info.pei_information4%type
332 ,service_oblig_end_date per_people_extra_info.pei_information5%type);
333 --
334 -- modified 10/24/97
335 type per_conversions_type is record
336 (per_conversions_flag varchar2(1)
337 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
338 ,object_version_number per_people_extra_info.object_version_number%type
339 ,date_conv_career_begins per_people_extra_info.pei_information3%type
340 ,date_conv_career_due per_people_extra_info.pei_information4%type
341 ,date_recmd_conv_begins per_people_extra_info.pei_information5%type
342 ,date_recmd_conv_due per_people_extra_info.pei_information7%type
343 ,date_vra_conv_due per_people_extra_info.pei_information6%type);
344 --Bug# 4588575 Segments are wrongly defined. modified the same
345
346 --
347 type per_uniformed_services_type is record
348 (per_uniformed_services_flag varchar2(1)
349 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
350 ,object_version_number per_people_extra_info.object_version_number%type
351 ,reserve_category per_people_extra_info.pei_information3%type
352 ,military_recall_status per_people_extra_info.pei_information4%type
353 ,creditable_military_service per_people_extra_info.pei_information5%type
354 ,date_retired_uniform_service per_people_extra_info.pei_information6%type
355 ,uniform_service_component per_people_extra_info.pei_information7%type
356 ,uniform_service_designation per_people_extra_info.pei_information8%type
357 ,retirement_grade per_people_extra_info.pei_information9%type
358 ,military_retire_waiver_ind per_people_extra_info.pei_information10%type
359 ,exception_retire_pay_ind per_people_extra_info.pei_information11%type);
360 --
361 type pos_valid_grade_type is record
362 (pos_valid_grade_flag varchar2(1)
363 ,position_extra_info_id per_position_extra_info.position_extra_info_id%type
364 ,object_version_number per_position_extra_info.object_version_number%type
365 ,valid_grade per_position_extra_info.poei_information3%type
366 ,target_grade per_position_extra_info.poei_information4%type
367 ,pay_table_id per_position_extra_info.poei_information5%type
368 ,pay_basis per_position_extra_info.poei_information6%type
369 ,employment_category_group per_position_extra_info.poei_information7%type);
370 --
371 type pos_grp1_type is record
372 (pos_grp1_flag varchar2(1)
373 ,position_extra_info_id per_position_extra_info.position_extra_info_id%type
374 ,object_version_number per_position_extra_info.object_version_number%type
375 ,personnel_office_id per_position_extra_info.poei_information3%type
376 ,office_symbol per_position_extra_info.poei_information4%type
377 ,organization_structure_id per_position_extra_info.poei_information5%type
378 ,occupation_category_code per_position_extra_info.poei_information6%type
379 ,flsa_category per_position_extra_info.poei_information7%type
380 ,bargaining_unit_status per_position_extra_info.poei_information8%type
381 ,competitive_level per_position_extra_info.poei_information9%type
382 ,work_schedule per_position_extra_info.poei_information10%type
383 ,functional_class per_position_extra_info.poei_information11%type
384 ,position_working_title per_position_extra_info.poei_information12%type
385 ,position_sensitivity per_position_extra_info.poei_information13%type
386 ,security_access per_position_extra_info.poei_information14%type
387 ,prp_sci per_position_extra_info.poei_information15%type
388 ,supervisory_status per_position_extra_info.poei_information16%type
389 ,type_employee_supervised per_position_extra_info.poei_information17%type
390 ,payroll_office_id per_position_extra_info.poei_information18%type
391 ,timekeeper per_position_extra_info.poei_information19%type
392 ,competitive_area per_position_extra_info.poei_information20%type
393 ,positions_organization per_position_extra_info.poei_information21%type
394 ,oct_report_flag per_position_extra_info.poei_information22%type
395 ,part_time_hours per_position_extra_info.poei_information23%type);
396 --
397 type pos_grp2_type is record
398 (pos_grp2_flag varchar2(1)
399 ,position_extra_info_id per_position_extra_info.position_extra_info_id%type
400 ,object_version_number per_position_extra_info.object_version_number%type
401 ,position_occupied per_position_extra_info.poei_information3%type
402 ,organization_function_code per_position_extra_info.poei_information4%type
403 ,date_position_classified per_position_extra_info.poei_information5%type
404 ,date_last_position_audit per_position_extra_info.poei_information6%type
405 ,classification_official per_position_extra_info.poei_information7%type
406 ,language_required per_position_extra_info.poei_information8%type
407 ,drug_test per_position_extra_info.poei_information9%type
408 ,financial_statement per_position_extra_info.poei_information10%type
409 ,training_program_id per_position_extra_info.poei_information11%type
410 ,key_emergency_essential per_position_extra_info.poei_information12%type
411 ,appropriation_code1 per_position_extra_info.poei_information13%type
412 ,appropriation_code2 per_position_extra_info.poei_information14%type
413 ,intelligence_position_ind per_position_extra_info.poei_information15%type
414 ,leo_position_indicator per_position_extra_info.poei_information16%type);
415 --
416 -- type pos_of8_type is record
417 -- (pos_of8_flag varchar2(1)
418 -- ,position_extra_info_id per_position_extra_info.position_extra_info_id%type
419 -- ,object_version_number per_position_extra_info.object_version_number%type
420 -- ,reason_for_submission per_position_extra_info.poei_information3%type
421 -- ,service_location per_position_extra_info.poei_information4%type
422 -- ,cs_certification_number per_position_extra_info.poei_information5%type
423 -- ,classified_grade_by per_position_extra_info.poei_information6%type
424 -- ,classification_standard_used per_position_extra_info.poei_information7%type
425 -- ,classification_standard_date per_position_extra_info.poei_information8%type
426 -- ,subject_to_ia_action per_position_extra_info.poei_information9%type);
427 --
428 type pos_oblig_type is record
429 (pos_oblig_flag varchar2(1)
430 ,position_extra_info_id per_position_extra_info.position_extra_info_id%type
431 ,object_version_number per_position_extra_info.object_version_number%type
432 ,expiration_date per_position_extra_info.poei_information3%type
433 ,obligation_type per_position_extra_info.poei_information4%type
434 ,employee_ssn per_position_extra_info.poei_information5%type);
435 --
436 type pos_car_prog_type is record
437 (pos_car_prog_flag varchar2(1)
438 ,position_extra_info_id per_position_extra_info.position_extra_info_id%type
439 ,object_version_number per_position_extra_info.object_version_number%type
440 ,career_program_id per_position_extra_info.poei_information3%type
441 ,career_program_type per_position_extra_info.poei_information4%type
442 ,change_reasons per_position_extra_info.poei_information5%type
443 ,career_field_id per_position_extra_info.poei_information6%type
444 ,career_program_code per_position_extra_info.poei_information7%type
445 ,acteds_key_position per_position_extra_info.poei_information8%type);
446 --
447 type personal_info_type is record
448 (p_national_identifier per_people_f.national_identifier%type
449 ,p_date_of_birth per_people_f.date_of_birth%type
450 ,p_sex per_people_f.sex%type);
451 --
452 type prior_sf52_data_type is record
453 (position_title per_position_definitions.segment1%type
454 ,position_number per_position_definitions.segment2%type
455 ,position_seq_no per_position_definitions.segment3%type
456 ,pay_plan per_grade_definitions.segment1%type
457 ,occ_code per_job_definitions.segment1%type
458 ,grade_or_level per_grade_definitions.segment2%type
459 ,step_or_rate per_assignment_extra_info.aei_information3%type
460 ,total_salary number
461 ,pay_basis per_position_extra_info.poei_information6%type
462 ,basic_pay number
463 ,locality_adj number
464 ,adj_basic_pay number
465 ,other_pay number
466 ,position_org_line1 ghr_pa_requests.from_position_org_line1%type
467 ,position_org_line2 ghr_pa_requests.from_position_org_line2%type
468 ,position_org_line3 ghr_pa_requests.from_position_org_line3%type
469 ,position_org_line4 ghr_pa_requests.from_position_org_line4%type
470 ,position_org_line5 ghr_pa_requests.from_position_org_line5%type
471 ,position_org_line6 ghr_pa_requests.from_position_org_line6%type
472 ,position_id per_all_positions.position_id%type
473 ,duty_station_location_id hr_locations.location_id%type
474 , work_schedule ghr_pa_requests.work_schedule%type
475 ,pay_rate_determinant per_assignment_extra_info.aei_information6%type);
476 --
477 type recruitment_bonus_type is record
478 (p_recruitment_bonus_flag varchar2(1)
479 ,p_recruitment_bonus ghr_pa_request_extra_info.rei_information1%type
480 ,p_date_recruit_exp ghr_pa_request_extra_info.rei_information2%type
481 ,p_percentage ghr_pa_request_extra_info.rei_information3%type);
482 --
483 type relocation_bonus_type is record
484 (p_relocation_bonus_flag varchar2(1)
485 ,p_relocation_bonus ghr_pa_request_extra_info.rei_information1%type
486 ,p_date_reloc_exp ghr_pa_request_extra_info.rei_information2%type
487 ,p_percentage ghr_pa_request_extra_info.rei_information3%type);
488 --
489 type agency_sf52_type is record
490 (agency_flag VARCHAR2(1)
491 ,agency_extra_info_id ghr_pa_request_extra_info.pa_request_extra_info_id%type
492 ,object_version_number ghr_pa_request_extra_info.object_version_number%type
493 ,agency_use_block_25 ghr_pa_request_extra_info.rei_information3%type
494 ,agency_data_block_40 ghr_pa_request_extra_info.rei_information4%type
495 ,agency_data_block_41 ghr_pa_request_extra_info.rei_information5%type
496 ,agency_data_block_42 ghr_pa_request_extra_info.rei_information6%type
497 ,agency_data_block_43 ghr_pa_request_extra_info.rei_information7%type
498 ,agency_data_block_44 ghr_pa_request_extra_info.rei_information8%type);
499 ----
500
501 type within_grade_increase_type is record
502 (p_wgi_flag varchar2(1)
503 ,p_wgi_status ghr_pa_request_extra_info.rei_information1%type
504 ,p_date_wgi_due ghr_pa_request_extra_info.rei_information2%type
505 ,p_wgi_pay_date ghr_pa_request_extra_info.rei_information3%type
506 ,p_date_wgi_postpone_effective ghr_pa_request_extra_info.rei_information3%type
507 ,p_date_wgi_postpone_detail_due ghr_pa_request_extra_info.rei_information4%type
508 ,p_date_wgi_due_temp_prom ghr_pa_request_extra_info.rei_information5%type
509 ,p_last_equi_incr ghr_pa_request_extra_info.rei_information5%type);
510
511 -- Added 14-AUG-97
512 type government_awards_type is record
513 (award_flag varchar2(1)
514 ,award_agency ghr_pa_request_extra_info.rei_information3%type
515 ,award_type ghr_pa_request_extra_info.rei_information4%type
516 ,percentage ghr_pa_request_extra_info.rei_information5%type
517 ,group_award ghr_pa_request_extra_info.rei_information6%type
518 ,tangible_benefit_dollars ghr_pa_request_extra_info.rei_information7%type
519 ,award_payment ghr_pa_request_extra_info.rei_information8%type
520 ,date_award_earned ghr_pa_request_extra_info.rei_information9%type
521 ,award_appropriation_code ghr_pa_request_extra_info.rei_information10%type
522 ,date_exemp_award ghr_pa_request_extra_info.rei_information11%type );
523 -- award_payment - 10/24/97
524 -- date_award_earned - 02/16/98
525 -- award_appropriation_code - 02/29/00
526 type government_payroll_type is record
527 (payroll_type ghr_pa_request_extra_info.rei_information3%type);
528
529 type retention_allow_review_type is record
530 ( retention_allow_review_flag varchar2(1)
531 ,review_date ghr_pa_request_extra_info.rei_information3%type);
532 -- added date_init_appr_due on 5-oct-98 by skutteti
533 type performance_appraisal_type is record
534 (perf_appr_flag varchar2(1)
535 ,rating_rec ghr_pa_request_extra_info.rei_information3%type
536 ,rating_rec_pattern ghr_pa_request_extra_info.rei_information4%type
537 ,rating_rec_level ghr_pa_request_extra_info.rei_information5%type
538 ,date_appr_starts ghr_pa_request_extra_info.rei_information19%type --Bug#4753117
539 ,date_appr_ends ghr_pa_request_extra_info.rei_information6%type
540 ,unit ghr_pa_request_extra_info.rei_information7%type
541 ,org_structure_id ghr_pa_request_extra_info.rei_information8%type
542 ,office_symbol ghr_pa_request_extra_info.rei_information9%type
543 ,pay_plan ghr_pa_request_extra_info.rei_information10%type
544 ,grade ghr_pa_request_extra_info.rei_information11%type
545 ,date_due ghr_pa_request_extra_info.rei_information12%type
546 ,appraisal_system_identifier ghr_pa_request_extra_info.rei_information13%type
547 ,appraisal_type ghr_pa_request_extra_info.rei_information14%type
548 ,date_effective ghr_pa_request_extra_info.rei_information15%type
549 ,date_init_appr_due ghr_pa_request_extra_info.rei_information16%type
550 ,optional_information ghr_pa_request_extra_info.rei_information17%type
551 ,person_analysis_id per_person_analyses.person_analysis_id%type
552 ,object_version_number per_person_analyses.object_version_number%type
553 --Added for Bug 3636398
554 ,performance_rating_points ghr_pa_request_extra_info.rei_information18%type);
555
556 -- Added 10/24/97
557 type conduct_performance_type is record
558 ( cond_perf_flag varchar2(1)
559 ,type_of_employment ghr_pa_request_extra_info.rei_information3%type
560 ,adverse_action_noac ghr_pa_request_extra_info.rei_information4%type
561 ,cause_of_disc_action ghr_pa_request_extra_info.rei_information5%type
562 ,date_of_adverse_action ghr_pa_request_extra_info.rei_information6%type
563 ,days_suspended ghr_pa_request_extra_info.rei_information7%type
564 ,date_suspension_over_30 ghr_pa_request_extra_info.rei_information8%type
565 ,date_suspension_under_30 ghr_pa_request_extra_info.rei_information9%type
566 ,pip_action_taken ghr_pa_request_extra_info.rei_information10%type
567 ,pip_begin_date ghr_pa_request_extra_info.rei_information11%type
568 ,pip_end_date ghr_pa_request_extra_info.rei_information12%type
569 ,pip_extensions ghr_pa_request_extra_info.rei_information13%type
570 ,pip_length ghr_pa_request_extra_info.rei_information14%type
571 ,date_reprimand_expires ghr_pa_request_extra_info.rei_information15%type
572 ,person_analysis_id per_person_analyses.person_analysis_id%type
573 ,object_version_number per_person_analyses.object_version_number%type);
574
575
576 -- Rohini 07-AUG-1972
577
578 type special_information_type is record
579 (segment1 per_analysis_criteria.segment1%type
580 ,segment2 per_analysis_criteria.segment2%type
581 ,segment3 per_analysis_criteria.segment3%type
582 ,segment4 per_analysis_criteria.segment4%type
583 ,segment5 per_analysis_criteria.segment5%type
584 ,segment6 per_analysis_criteria.segment6%type
585 ,segment7 per_analysis_criteria.segment7%type
586 ,segment8 per_analysis_criteria.segment8%type
587 ,segment9 per_analysis_criteria.segment9%type
588 ,segment10 per_analysis_criteria.segment10%type
589 ,segment11 per_analysis_criteria.segment11%type
590 ,segment12 per_analysis_criteria.segment12%type
591 ,segment13 per_analysis_criteria.segment13%type
592 ,segment14 per_analysis_criteria.segment14%type
593 ,segment15 per_analysis_criteria.segment15%type
594 ,segment16 per_analysis_criteria.segment16%type
595 ,segment17 per_analysis_criteria.segment17%type
596 ,segment18 per_analysis_criteria.segment18%type
597 ,segment19 per_analysis_criteria.segment19%type
598 ,segment20 per_analysis_criteria.segment20%type
599 ,segment21 per_analysis_criteria.segment21%type
600 ,segment22 per_analysis_criteria.segment22%type
601 ,segment23 per_analysis_criteria.segment23%type
602 ,segment24 per_analysis_criteria.segment24%type
603 ,segment25 per_analysis_criteria.segment25%type
604 ,segment26 per_analysis_criteria.segment26%type
605 ,segment27 per_analysis_criteria.segment27%type
606 ,segment28 per_analysis_criteria.segment28%type
607 ,segment29 per_analysis_criteria.segment29%type
608 ,segment30 per_analysis_criteria.segment30%type
609 ,person_analysis_id per_person_analyses.person_analysis_id%type
610 ,object_version_number per_person_analyses.object_version_number%type );
611
612
613 type civ_position_acquisition is record
614 ( career_category per_position_extra_info.poei_information8%type
615 ,critical_position per_position_extra_info.poei_information5%type
616 ,civ_func_acct_shred per_position_extra_info.poei_information7%type
617 ,psn_status_ind per_position_extra_info.poei_information4%type
618 );
619
620 type civ_position_army_appr_ln is record
621 (
622 amscd per_position_extra_info.poei_information11%type
623 ,ar_tda_line_num per_position_extra_info.poei_information3%type
624 ,competitive_area per_position_extra_info.poei_information4%type
625 ,duty_stn_st per_position_extra_info.poei_information16%type
626 ,func_desc per_position_extra_info.poei_information2%type
627 ,tda_para_num per_position_extra_info.poei_information5%type
628 );
629
630 type civ_position_naf is record
631 (
632 med_ind per_position_extra_info.poei_information12%type
633 ,nafi_psn_num per_position_extra_info.poei_information10%type
634 ,svc_offc_symbol per_position_extra_info.poei_information4%type
635 );
636
637 -- added by skutteti on 3-mar-98
638 type par_term_retained_grade_type is record
639 (par_term_retained_grade_flag varchar2(1)
640 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
641 ,object_version_number per_people_extra_info.object_version_number%type
642 );
643 --
644 type entitlement_type is record
645 (entitlement_flag varchar2(1)
646 ,entitlement_code ghr_pa_request_extra_info.rei_information3%type
647 ,entitlement_amt_percent ghr_pa_request_extra_info.rei_information4%type
648 );
649 -- Bug#2759379 Created FEGLI Record type
650 type fegli_type is record
651 (fegli_flag varchar2(1)
652 ,eligibility_expiration ghr_pa_request_extra_info.rei_information1%type
653 );
654 --
655 type foreign_lang_prof_pay_type is record
656 (for_lang_flag varchar2(1)
657 ,certification_date ghr_pa_request_extra_info.rei_information3%type
658 ,pay_level_or_rate ghr_pa_request_extra_info.rei_information4%type
659 );
660 --
661 -- Bug#3385386 Added record type FTA
662 type fta_type is record
663 (fta_flag varchar2(1)
664 ,last_action_code ghr_pa_request_extra_info.rei_information3%type
665 ,Number_family_members ghr_pa_request_extra_info.rei_information4%type
666 ,Miscellaneous_Expense ghr_pa_request_extra_info.rei_information5%type
667 ,Wardrobe_Expense ghr_pa_request_extra_info.rei_information6%type
668 ,Pre_Departure_Subs_Expense ghr_pa_request_extra_info.rei_information7%type
669 ,Lease_Penalty_Expense ghr_pa_request_extra_info.rei_information8%type
670 ,Amount ghr_pa_request_extra_info.rei_information9%type
671 );
672 --
673 type edp_pay_type is record
674 (edp_flag varchar2(1)
675 ,premium_pay_indicator ghr_pa_request_extra_info.rei_information3%type
676 ,edp_type ghr_pa_request_extra_info.rei_information4%type
677 );
678 --
679 type hazard_pay_type is record
680 (hazard_flag varchar2(1)
681 ,premium_pay_indicator ghr_pa_request_extra_info.rei_information3%type
682 ,hazard_type ghr_pa_request_extra_info.rei_information4%type
683 );
684 --
685 type health_benefits_type is record
686 (health_benefits_flag varchar2(1)
687 ,enrollment ghr_pa_request_extra_info.rei_information3%type
688 ,health_plan ghr_pa_request_extra_info.rei_information4%type
689 ,temps_total_cost ghr_pa_request_extra_info.rei_information5%type
690 ,pre_tax_waiver ghr_pa_request_extra_info.rei_information6%type
691 );
692 --
693 --
694 type health_ben_pre_tax_type is record
695 (health_ben_pre_tax_flag varchar2(1)
696 ,enrollment ghr_pa_request_extra_info.rei_information3%type
697 ,health_plan ghr_pa_request_extra_info.rei_information4%type
698 ,temps_total_cost ghr_pa_request_extra_info.rei_information5%type
699 );
700 --
701 type danger_pay_type is record
702 (danger_flag varchar2(1)
703 ,last_action_code ghr_pa_request_extra_info.rei_information3%type
704 ,location ghr_pa_request_extra_info.rei_information4%type
705 );
706 --
707 type imminent_danger_pay_type is record
708 (imminent_danger_flag varchar2(1)
709 ,amount ghr_pa_request_extra_info.rei_information3%type
710 ,location ghr_pa_request_extra_info.rei_information4%type
711 ,last_action_code ghr_pa_request_extra_info.rei_information5%type
712 );
713 --
714 type living_quarters_allow_type is record
715 (living_quarters_allow_flag varchar2(1)
716 ,purchase_amount ghr_pa_request_extra_info.rei_information3%type
717 ,purchase_date ghr_pa_request_extra_info.rei_information4%type
718 ,rent_amount ghr_pa_request_extra_info.rei_information5%type
719 ,utility_amount ghr_pa_request_extra_info.rei_information6%type
720 ,last_action_code ghr_pa_request_extra_info.rei_information7%type
721 ,location ghr_pa_request_extra_info.rei_information8%type
722 ,quarters_type ghr_pa_request_extra_info.rei_information9%type
723 ,shared_percent ghr_pa_request_extra_info.rei_information10%type
724 ,no_of_family_members ghr_pa_request_extra_info.rei_information11%type
725 ,summer_record_ind ghr_pa_request_extra_info.rei_information12%type
726 ,quarters_group ghr_pa_request_extra_info.rei_information13%type
727 ,currency ghr_pa_request_extra_info.rei_information14%type
728 );
729 --
730 type post_diff_amt_type is record
731 (post_diff_amt_flag varchar2(1)
732 ,amount ghr_pa_request_extra_info.rei_information3%type
733 ,last_action_code ghr_pa_request_extra_info.rei_information4%type
734 ,location ghr_pa_request_extra_info.rei_information5%type
735 ,no_of_family_members ghr_pa_request_extra_info.rei_information6%type
736 );
737 --
738 type post_diff_percent_type is record
739 (post_diff_percent_flag varchar2(1)
740 ,percent ghr_pa_request_extra_info.rei_information3%type
741 ,last_action_code ghr_pa_request_extra_info.rei_information4%type
742 ,location ghr_pa_request_extra_info.rei_information5%type
743 );
744 --
745 type sep_maintenance_allow_type is record
746 (sep_maint_allow_flag varchar2(1)
747 ,amount ghr_pa_request_extra_info.rei_information3%type
748 ,last_action_code ghr_pa_request_extra_info.rei_information4%type
749 ,category ghr_pa_request_extra_info.rei_information5%type
750 );
751 --
752 type supplemental_post_allow_type is record
753 (sup_post_allow_flag varchar2(1)
754 ,amount ghr_pa_request_extra_info.rei_information3%type
755 );
756 --
757 type temp_lodge_allow_type is record
758 (temp_lodge_allow_flag varchar2(1)
759 ,allowance_type ghr_pa_request_extra_info.rei_information3%type
760 ,daily_rate ghr_pa_request_extra_info.rei_information4%type
761 );
762 --
763 type premium_pay_type is record
764 (premium_pay_flag varchar2(1)
765 ,premium_pay_ind ghr_pa_request_extra_info.rei_information3%type
766 ,amount ghr_pa_request_extra_info.rei_information4%type
767 );
768 --
769 type retirement_annuity_type is record
770 (retirement_annuity_flag varchar2(1)
771 ,annuity_sum ghr_pa_request_extra_info.rei_information3%type
772 ,eligibility_expires ghr_pa_request_extra_info.rei_information4%type
773 );
774 --
775 type severance_pay_type is record
776 (severance_pay_flag varchar2(1)
777 ,amount ghr_pa_request_extra_info.rei_information3%type
778 ,total_entitlement_weeks ghr_pa_request_extra_info.rei_information4%type
779 ,number_weeks_paid ghr_pa_request_extra_info.rei_information5%type
780 ,weekly_amount ghr_pa_request_extra_info.rei_information6%type
781 );
782 --
783 type thrift_saving_plan is record
784 (tsp_flag varchar2(1)
785 ,amount ghr_pa_request_extra_info.rei_information3%type
786 ,rate ghr_pa_request_extra_info.rei_information4%type
787 ,g_fund ghr_pa_request_extra_info.rei_information5%type
788 ,f_fund ghr_pa_request_extra_info.rei_information6%type
789 ,c_fund ghr_pa_request_extra_info.rei_information7%type
790 ,status ghr_pa_request_extra_info.rei_information8%type
791 ,status_date ghr_pa_request_extra_info.rei_information9%type
792 ,agncy_contrib_elig_date ghr_pa_request_extra_info.rei_information10%type
793 ,emp_contrib_elig_date ghr_pa_request_extra_info.rei_information11%type
794 );
795
796 --BUG # 13571004 Added for TSP Roth Elements
797 type tsp_roth_type is record
798 (tsp_roth_flag varchar2(1)
799 ,tsp_roth_401k_amount ghr_pa_request_extra_info.rei_information19%type
800 ,tsp_roth_401k_rate ghr_pa_request_extra_info.rei_information20%type
801 );
802 --
803 --Pradeep.
804 type mddds_special_pay_type is record
805 ( mddds_special_pay_flag varchar2(1)
806 ,Full_Time_Status ghr_pa_request_extra_info.rei_information9%type
807 ,Length_of_Service ghr_pa_request_extra_info.rei_information10%type
808 ,Scarce_Specialty ghr_pa_request_extra_info.rei_information3%type
809 ,Specialty_or_Board_Cert ghr_pa_request_extra_info.rei_information4%type
810 ,Geographic_Location ghr_pa_request_extra_info.rei_information5%type
811 ,Exceptional_Qualifications ghr_pa_request_extra_info.rei_information6%type
812 ,Executive_Position ghr_pa_request_extra_info.rei_information7%type
813 ,Dentist_Post_Graduate_Training ghr_pa_request_extra_info.rei_information8%type
814 ,Amount ghr_pa_request_extra_info.rei_information11%type
815 ,mddds_special_pay_date ghr_pa_request_extra_info.rei_information12%type
816 ,premium_pay_ind ghr_pa_request_extra_info.rei_information13%type
817 );
818 --
819 --Bug#3585473
820 type premium_pay_ind_type is record
821 (
822 premium_pay_ind_flag varchar2(1)
823 ,premium_pay_ind ghr_pa_request_extra_info.rei_information3%type
824 );
825
826 -- Student Loan Repayment changes bug 3494728
827 type student_loan_repay_type is record
828 (p_student_loan_flag varchar2(1)
829 ,p_repay_schedule varchar2(2)
830 ,p_amount ghr_pa_request_extra_info.rei_information1%type
831 ,p_review_date ghr_pa_request_extra_info.rei_information2%type);
832
833 -- Student Loan Repayment changes
834
835 -- Bug 4724337 Race or National Origin changes
836 type per_race_ethnic_type is record
837 ( p_race_ethnic_info_flag varchar2(1),
838 person_extra_info_id per_people_extra_info.person_extra_info_id%type,
839 object_version_number per_people_extra_info.object_version_number%type,
840 p_hispanic varchar2(1),
841 p_american_indian varchar2(1),
842 p_asian varchar2(1),
843 p_black_afr_american varchar2(1),
844 p_hawaiian_pacific varchar2(1),
845 p_white varchar2(1)
846 );
847 -- End Race or National Origin changes
848
849 -- 6312144 IPA Benefits Continuation EIT changes
850 type per_ipa_ben_cont_info_type is record
851 ( per_ben_cont_info_flag varchar2(1)
852 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
853 ,object_version_number per_people_extra_info.object_version_number%type
854 ,FEGLI_Indicator per_people_extra_info.pei_information1%type
855 ,FEGLI_Election_Date per_people_extra_info.pei_information2%type
856 ,FEGLI_Elec_Not_Date per_people_extra_info.pei_information3%type
857 ,FEHB_Indicator per_people_extra_info.pei_information4%type
858 ,FEHB_Election_Date per_people_extra_info.pei_information5%type
859 ,FEHB_Elec_Notf_Date per_people_extra_info.pei_information6%type
860 ,Retirement_Indicator per_people_extra_info.pei_information7%type
861 ,Retirement_Elec_Date per_people_extra_info.pei_information12%type
862 ,Retirement_Elec_Notf_Date per_people_extra_info.pei_information8%type
863 ,Cont_Term_Insuff_Pay_Elec_Date per_people_extra_info.pei_information9%type
864 ,Cont_Term_Insuff_Pay_Notf_Date per_people_extra_info.pei_information10%type
865 ,Cont_Term_Insuff_Pmt_Type_Code per_people_extra_info.pei_information11%type);
866
867 -- 6312144 Retirement System information
868 type per_retirement_info_type is record
869 (per_retirement_info_flag varchar2(1)
870 ,person_extra_info_id per_people_extra_info.person_extra_info_id%type
871 ,object_version_number per_people_extra_info.object_version_number%type
872 ,special_population_code per_people_extra_info.pei_information1%type
873 ,App_Exc_CSRS_Ind per_people_extra_info.pei_information2%type
874 ,App_Exc_FERS_Ind per_people_extra_info.pei_information3%type
875 ,FICA_Coverage_Ind1 per_people_extra_info.pei_information4%type
876 ,FICA_Coverage_Ind2 per_people_extra_info.pei_information5%type);
877
878
879
880
881
882 -- ---------------------------------------------------------------------------
883 -- |-------------------< retrieve_element_entry_value >----------------------|
884 -- ---------------------------------------------------------------------------
885 -- {Start of Comments}
886 --
887 -- Description:
888 -- Retrieve element entry value
889 --
890 -- Prerequisites:
891 --
892 -- In Parameters:
893 -- p_element_name
894 -- p_input_value_name
895 -- p_assignment_id
896 -- p_effective_date
897 --
898 -- Out Parameters:
899 -- p_value
900 -- p_multiple_error_flag
901 --
902 -- Post Success:
903 -- Processing continues.
904 --
905 -- Post Failure:
906 -- An application error will be raised and processing is terminated.
907 -- If an employee has more than one of the same element, this procedure
908 -- will set p_multiple_error flag to TRUE and return NULL to p_value.
909 -- Otherwise, p_multiple_error flag will be set to FALSE and return
910 -- the entry value.
911 --
912 -- Developer Implementation Notes:
913 -- None.
914 --
915 -- Access Status:
916 -- Internal Use Only.
917 --
918 -- {End of Comments}
919 -- ---------------------------------------------------------------------------
920 procedure retrieve_element_entry_value
921 (p_element_name in pay_element_types_f.element_name%type
922 ,p_input_value_name in pay_input_values_f.name%type
923 ,p_assignment_id in pay_element_entries_f.assignment_id%type
924 ,p_effective_date in date
925 ,p_value OUT NOCOPY varchar2
926 ,p_multiple_error_flag OUT NOCOPY boolean);
927
928 --
929 -- ---------------------------------------------------------------------------
930 -- |--------------------------< get_position_title >--------------------------|
931 -- ---------------------------------------------------------------------------
932 -- {Start of Comments}
933 --
934 -- Description:
935 -- Retrieve position title.
936 --
937 -- Prerequisites:
938 -- Either p_persion_id or p_assignment_id must be provided.
939 --
940 -- In Parameters:
941 -- p_person_id
942 -- The default is NULL.
943 -- p_assignment_id
944 -- The default is NULL.
945 -- p_effective_date
946 -- The default is sysdate.
947 --
948 -- Post Success:
949 -- Processing continues.
950 --
951 -- Post Failure:
952 -- An application error will be raised and processing is terminated.
953 --
954 -- Developer Implementation Notes:
955 -- Calling hr_utility procedures are commented out due to these procedures
956 -- update the database which violates the function requirement.
957 --
958 -- Access Status:
959 -- Internal Table Handler Use Only.
960 --
961 -- {End of Comments}
962 -- ---------------------------------------------------------------------------
963 function get_position_title
964 (p_person_id in per_people_f.person_id%type default NULL
965 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
966 ,p_effective_date in date default sysdate ) return varchar2;
967 pragma restrict_references (get_position_title, WNDS, WNPS);
968 --
969 -- ---------------------------------------------------------------------------
970 -- |--------------------------< get_position_title_pos >----------------------|
971 -- ---------------------------------------------------------------------------
972 -- {Start of Comments}
973 --
974 -- Description:
975 -- Retrieve position title. Very similar to get_position_title except it takes position_id and
976 -- its associated business group as in parameters
977 --
978 -- Prerequisites:
979 -- p_position_id and p_business_group_id must be provided.
980 --
981 -- In Parameters:
982 -- p_position_id
983 -- p_business_group_id
984 --
985 -- Post Success:
986 -- Processing continues.
987 --
988 -- Post Failure:
989 -- An application error will be raised and processing is terminated.
990 --
991 -- Developer Implementation Notes:
992 -- Calling hr_utility procedures are commented out due to these procedures
993 -- update the database which violates the function requirement.
994 --
995 -- Access Status:
996 -- Internal Row Handler Use Only.
997 --
998 -- {End of Comments}
999 -- ---------------------------------------------------------------------------
1000 function get_position_title_pos
1001 (p_position_id in per_all_positions.position_id%type
1002 ,p_business_group_id in per_all_positions.business_group_id%type
1003 ,p_effective_date in date default sysdate
1004 ) return varchar2;
1005 pragma restrict_references (get_position_title_pos, WNDS, WNPS);
1006 --
1007 -- ---------------------------------------------------------------------------
1008 -- |---------------------< get_position_description_no >---------------------|
1009 -- ---------------------------------------------------------------------------
1010 -- {Start of Comments}
1011 --
1012 -- Description:
1013 -- Retrieve position description no (PD#).
1014 --
1015 -- Prerequisites:
1016 -- Either p_persion_id or p_assignment_id must be provided.
1017 --
1018 -- In Parameters:
1019 -- p_person_id
1020 -- The default is NULL.
1021 -- p_assignment_id
1022 -- The default is NULL.
1023 -- p_effective_date
1024 -- The default is sysdate.
1025 --
1026 -- Post Success:
1027 -- Processing continues.
1028 --
1029 -- Post Failure:
1030 -- An application error will be raised and processing is terminated.
1031 --
1032 -- Developer Implementation Notes:
1033 -- Calling hr_utility procedures are commented out due to these procedures
1034 -- update the database which violates the function requirement.
1035 --
1036 -- Access Status:
1037 -- All.
1038 --
1039 -- {End of Comments}
1040 -- ---------------------------------------------------------------------------
1041 function get_position_description_no
1042 (p_person_id in per_people_f.person_id%type default NULL
1043 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
1044 ,p_effective_date in date default sysdate
1045 ) return varchar2;
1046 pragma restrict_references (get_position_description_no, WNDS, WNPS);
1047 --
1048 -- ---------------------------------------------------------------------------
1049 -- |--------------------------< get_position_desc_no_pos >--------------------|
1050 -- ---------------------------------------------------------------------------
1051 -- {Start of Comments}
1052 --
1053 -- Description:
1054 -- Retrieve position description no (PD#). Very similar to get_position_description_no
1055 -- except it takes position_id and its associated business group as in parameters
1056 --
1057 -- Prerequisites:
1058 -- p_position_id and p_business_group_id must be provided.
1059 --
1060 -- In Parameters:
1061 -- p_position_id
1062 -- p_business_group_id
1063 --
1064 -- Post Success:
1065 -- Processing continues.
1066 --
1067 -- Post Failure:
1068 -- An application error will be raised and processing is terminated.
1069 --
1070 -- Developer Implementation Notes:
1071 -- Calling hr_utility procedures are commented out due to these procedures
1072 -- update the database which violates the function requirement.
1073 --
1074 -- Access Status:
1075 -- Internal Row Handler Use Only.
1076 --
1077 -- {End of Comments}
1078 -- ---------------------------------------------------------------------------
1079 function get_position_desc_no_pos
1080 (p_position_id in per_all_positions.position_id%type
1081 ,p_business_group_id in per_all_positions.business_group_id%type
1082 ,p_effective_date in date default sysdate
1083 ) return varchar2;
1084 pragma restrict_references (get_position_desc_no_pos, WNDS, WNPS);
1085 --
1086 -- ---------------------------------------------------------------------------
1087 -- |----------------------< get_position_sequence_no >-----------------------|
1088 -- ---------------------------------------------------------------------------
1089 -- {Start of Comments}
1090 --
1091 -- Description:
1092 -- Retrieve position sequence no.
1093 --
1094 -- Prerequisites:
1095 -- Either p_persion_id or p_assignment_id must be provided.
1096 --
1097 -- In Parameters:
1098 -- p_person_id
1099 -- The default is NULL.
1100 -- p_assignment_id
1101 -- The default is NULL.
1102 -- p_effective_date
1103 -- The default is sysdate.
1104 --
1105 -- Post Success:
1106 -- Processing continues.
1107 --
1108 -- Post Failure:
1109 -- An application error will be raised and processing is terminated.
1110 --
1111 -- Developer Implementation Notes:
1112 -- Calling hr_utility procedures are commented out due to these procedures
1113 -- update the database which violates the function requirement.
1114 --
1115 -- Access Status:
1116 -- All.
1117 --
1118 -- {End of Comments}
1119 -- ---------------------------------------------------------------------------
1120 function get_position_sequence_no
1121 (p_person_id in per_people_f.person_id%type default NULL
1122 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
1123 ,p_effective_date in date default sysdate
1124 ) return varchar2;
1125 pragma restrict_references (get_position_sequence_no, WNDS, WNPS);
1126 --
1127 -- ---------------------------------------------------------------------------
1128 -- |--------------------------< get_position_sequence_no_pos >----------------|
1129 -- ---------------------------------------------------------------------------
1130 -- {Start of Comments}
1131 --
1132 -- Description:
1133 -- Retrieve position sequence no. Very similar to get_position_sequence_no
1134 -- except it takes position_id and its associated business group as in parameters
1135 --
1136 -- Prerequisites:
1137 -- p_position_id and p_business_group_id must be provided.
1138 --
1139 -- In Parameters:
1140 -- p_position_id
1141 -- p_business_group_id
1142 --
1143 -- Post Success:
1144 -- Processing continues.
1145 --
1146 -- Post Failure:
1147 -- An application error will be raised and processing is terminated.
1148 --
1149 -- Developer Implementation Notes:
1150 -- Calling hr_utility procedures are commented out due to these procedures
1151 -- update the database which violates the function requirement.
1152 --
1153 -- Access Status:
1154 -- Internal Row Handler Use Only.
1155 --
1156 -- {End of Comments}
1157 -- ---------------------------------------------------------------------------
1158 function get_position_sequence_no_pos
1159 (p_position_id in per_all_positions.position_id%type
1160 ,p_business_group_id in per_all_positions.business_group_id%type
1161 ,p_effective_date in date default sysdate
1162 ) return varchar2;
1163 pragma restrict_references (get_position_sequence_no_pos, WNDS, WNPS);
1164 --
1165
1166 -- ---------------------------------------------------------------------------
1167 -- |----------------------< get_position_agency_code>-------------------------|
1168 -- ---------------------------------------------------------------------------
1169 -- {Start of Comments}
1170 --
1171 -- Description:
1172 -- Retrieve position agency code. Similar to get_position_title,
1173 -- get_position_description_no and get_position_sequence_no
1174 --
1175 -- Prerequisites:
1176 -- Either p_persion_id or p_assignment_id must be provided.
1177 --
1178 -- In Parameters:
1179 -- p_person_id
1180 -- The default is NULL.
1181 -- p_assignment_id
1182 -- The default is NULL.
1183 -- p_effective_date
1184 -- The default is sysdate.
1185 --
1186 -- Post Success:
1187 -- Processing continues.
1188 --
1189 -- Post Failure:
1190 -- An application error will be raised and processing is terminated.
1191 --
1192 -- Developer Implementation Notes:
1193 -- Calling hr_utility procedures are commented out due to these procedures
1194 -- update the database which violates the function requirement.
1195 --
1196 -- Access Status:
1197 -- All.
1198 --
1199 -- {End of Comments}
1200 -- ---------------------------------------------------------------------------
1201 function get_position_agency_code
1202 (p_person_id in per_people_f.person_id%type default NULL
1203 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
1204 ,p_effective_date in date default sysdate
1205 ) return varchar2;
1206 pragma restrict_references (get_position_agency_code, WNDS, WNPS);
1207 --
1208 -- ---------------------------------------------------------------------------
1209 -- |--------------------------< get_position_agency_code_pos >----------------|
1210 -- ---------------------------------------------------------------------------
1211 -- {Start of Comments}
1212 --
1213 -- Description:
1214 -- Retrieve position agency_ code. Very similar to get_position_agency_code
1215 -- except it takes position_id and its associated business group as in parameters
1216 --
1217 -- Prerequisites:
1218 -- p_position_id and p_business_group_id must be provided.
1219 --
1220 -- In Parameters:
1221 -- p_position_id
1222 -- p_business_group_id
1223 --
1224 -- Post Success:
1225 -- Processing continues.
1226 --
1227 -- Post Failure:
1228 -- An application error will be raised and processing is terminated.
1229 --
1230 -- Developer Implementation Notes:
1231 -- Calling hr_utility procedures are commented out due to these procedures
1232 -- update the database which violates the function requirement.
1233 --
1234 -- Access Status:
1235 -- Internal Row Handler Use Only.
1236 --
1237 -- {End of Comments}
1238 -- ---------------------------------------------------------------------------
1239 function get_position_agency_code_pos
1240 (p_position_id in per_all_positions.position_id%type
1241 ,p_business_group_id in per_all_positions.business_group_id%type
1242 ,p_effective_date in date default sysdate
1243 ) return varchar2;
1244 pragma restrict_references (get_position_agency_code_pos, WNDS, WNPS);
1245 --
1246 -- ---------------------------------------------------------------------------
1247 -- |--------------------< get_job_occupational_series >----------------------|
1248 -- ---------------------------------------------------------------------------
1249 -- {Start of Comments}
1250 --
1251 -- Description:
1252 -- Retrieve job occupational series.
1253 --
1254 -- Prerequisites:
1255 -- Either p_persion_id or p_assignment_id must be provided.
1256 --
1257 -- In Parameters:
1258 -- p_person_id
1259 -- The default is NULL.
1260 -- p_assignment_id
1261 -- The default is NULL.
1262 -- p_effective_date
1263 -- The default is sysdate.
1264 --
1265 -- Post Success:
1266 -- Processing continues.
1267 --
1268 -- Post Failure:
1269 -- An application error will be raised and processing is terminated.
1270 --
1271 -- Developer Implementation Notes:
1272 -- None.
1273 --
1274 -- Access Status:
1275 -- All.
1276 --
1277 -- {End of Comments}
1278 -- ---------------------------------------------------------------------------
1279 --
1280 function get_job_occupational_series
1281 (p_person_id in per_people_f.person_id%type default NULL
1282 ,p_assignment_id in per_assignments_f.assignment_id%type default NULL
1283 ,p_effective_date in date default sysdate
1284 ) return varchar2;
1285 pragma restrict_references (get_job_occupational_series, WNDS, WNPS);
1286 --
1287 -- ---------------------------------------------------------------------------
1288 -- |--------------------< get_job_occ_series_job >----------------------------|
1289 -- ---------------------------------------------------------------------------
1290 -- {Start of Comments}
1291 --
1292 -- Description:
1293 -- Retrieve job occupational series. Very similar to get_job_occupational_series
1294 -- except it takes job_id and its associated business group as in parameters
1295 --
1296 -- Prerequisites:
1297 -- p_job_id and p_business_group_id must be provided.
1298 --
1299 -- In Parameters:
1300 -- p_job_id
1301 -- p_business_group_id
1302 --
1303 -- Post Success:
1304 -- Processing continues.
1305 --
1306 -- Post Failure:
1307 -- An application error will be raised and processing is terminated.
1308 --
1309 -- Developer Implementation Notes:
1310 -- None.
1311 --
1312 -- Access Status:
1313 -- All.
1314 --
1315 -- {End of Comments}
1316 -- ---------------------------------------------------------------------------
1317 --
1318 function get_job_occ_series_job
1319 (p_job_id in per_jobs.job_id%type
1320 ,p_business_group_id in per_all_positions.business_group_id%type
1321 ) return varchar2;
1322 pragma restrict_references (get_job_occ_series_job, WNDS, WNPS);
1323 --
1324 -- ---------------------------------------------------------------------------
1325 -- |----------------------< sf52_from_by_assignment >------------------------|
1326 -- ---------------------------------------------------------------------------
1327 -- {Start of Comments}
1328 --
1329 -- Description:
1330 -- SF52 From (from block 7 throught 14) by assignment ID.
1331 --
1332 -- Prerequisites:
1333 --
1334 -- In Parameters:
1335 -- p_assignment_id
1336 -- p_effective_date
1337 -- The default is sysdate.
1338 --
1339 -- Post Success:
1340 -- Processing continues.
1341 --
1342 -- Post Failure:
1343 -- An application error will be raised and processing is terminated.
1344 --
1345 -- Developer Implementation Notes:
1346 -- None.
1347 --
1348 -- Access Status:
1349 -- All.
1350 --
1351 -- {End of Comments}
1352 -- ---------------------------------------------------------------------------
1353 --
1354 procedure sf52_from_data_elements
1355 (p_person_id in per_people_f.person_id%type default NULL
1356 ,p_assignment_id IN OUT NOCOPY per_assignments_f.assignment_id%type
1357 ,p_effective_date in date default sysdate
1358 ,p_altered_pa_request_id in number
1359 ,p_noa_id_corrected in number
1360 ,p_pa_history_id in number
1361 ,p_position_title OUT NOCOPY varchar2
1362 ,p_position_number OUT NOCOPY varchar2
1363 ,p_position_seq_no OUT NOCOPY number
1364 ,p_pay_plan OUT NOCOPY varchar2
1365 ,p_job_id OUT NOCOPY number
1366 ,p_occ_code OUT NOCOPY varchar2
1367 ,p_grade_id OUT NOCOPY number
1368 ,p_grade_or_level OUT NOCOPY varchar2
1369 ,p_step_or_rate OUT NOCOPY varchar2
1370 ,p_total_salary OUT NOCOPY number
1371 ,p_pay_basis OUT NOCOPY varchar2
1372 -- FWFA Changes Bug#4444609
1373 ,p_pay_table_identifier OUT NOCOPY number
1374 -- FWFA Changes
1375 ,p_basic_pay OUT NOCOPY number
1376 ,p_locality_adj OUT NOCOPY number
1377 ,p_adj_basic_pay OUT NOCOPY number
1378 ,p_other_pay OUT NOCOPY number
1379 ,p_au_overtime OUT NOCOPY NUMBER
1380 ,p_auo_premium_pay_indicator OUT NOCOPY VARCHAR2
1381 ,p_availability_pay OUT NOCOPY NUMBER
1382 ,p_ap_premium_pay_indicator OUT NOCOPY VARCHAR2
1383 ,p_retention_allowance OUT NOCOPY NUMBER
1384 ,p_retention_allow_percentage OUT NOCOPY NUMBER
1385 ,p_supervisory_differential OUT NOCOPY NUMBER
1386 ,p_supervisory_diff_percentage OUT NOCOPY NUMBER
1387 ,p_staffing_differential OUT NOCOPY NUMBER
1388 ,p_staffing_diff_percentage OUT NOCOPY NUMBER
1389 ,p_organization_id OUT NOCOPY number
1390 ,p_position_org_line1 OUT NOCOPY varchar2 -- Position_org_line1 .. 6
1391 ,p_position_org_line2 OUT NOCOPY varchar2
1392 ,p_position_org_line3 OUT NOCOPY varchar2
1393 ,p_position_org_line4 OUT NOCOPY varchar2
1394 ,p_position_org_line5 OUT NOCOPY varchar2
1395 ,p_position_org_line6 OUT NOCOPY varchar2
1396 ,p_position_id OUT NOCOPY per_all_positions.position_id%type
1397 ,p_duty_station_location_id OUT NOCOPY hr_locations.location_id%type -- duty_station_location_id
1398 ,p_pay_rate_determinant OUT NOCOPY varchar2
1399 ,p_work_schedule OUT NOCOPY varchar2
1400 );
1401 --
1402 --
1403 -- ---------------------------------------------------------------------------
1404 -- |--------------------------< return_upd_hr_dml_status >----------------|
1405 -- ---------------------------------------------------------------------------
1406 -- Description:
1407 -- This function will return the current g_api_dml private global
1408 -- boolean status.
1409 -- The g_api_dml status determines if at the time of the function
1410 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
1411 -- is being issued from within an api.
1412 -- If the status is TRUE then a dml statement is being issued from
1413 -- within this entity api.
1414 -- This function is primarily to support database triggers which
1415 -- need to maintain the object_version_number for non-supported
1416 -- dml statements (i.e. dml statement issued outside of the api layer).
1417 --
1418 -- Pre Conditions:
1419
1420 -- None.
1421 --
1422 -- In Arguments:
1423 -- None.
1424 --
1425 -- Post Success:
1426 -- Processing continues.
1427 -- If the function returns a TRUE value then, dml is being executed from
1428 -- within this api.
1429 --
1430 -- Post Failure:
1431 -- None.
1432 --
1433 -- Access Status:
1434 -- Internal Table Handler Use Only.
1435 --
1436 -- {End Of Comments}
1437
1438 function return_upd_hr_dml_status return boolean;
1439
1440 -- pragma restrict_references (return_upd_hr_dml_status, WNDS, WNPS);
1441
1442
1443 -- ---------------------------------------------------------------------------
1444 -- |----------------------< return_special_information >------------------------|
1445 -- ---------------------------------------------------------------------------
1446 -- {Start of Comments}
1447 --
1448 -- Description:
1449 -- Special Information for the Person
1450
1451 -- Prerequisites:
1452 --
1453 -- In Parameters:
1454 --
1455 -- p_structure_name
1456 -- p_person_id
1457 -- p_effective_date
1458 -- The default is sysdate.
1459 --
1460 -- Post Success:
1461 -- Processing continues.
1462 --
1463 -- Post Failure:
1464 -- An application error will be raised and processing is terminated.
1465 --
1466 -- Developer Implementation Notes:
1467 -- None.
1468 --
1469 -- Access Status:
1470 -- All.
1471 --
1472 -- {End of Comments}
1473
1474 Procedure return_special_information
1475 (p_person_id in number
1476 ,p_structure_name in varchar2
1477 ,p_effective_date in date
1478 ,p_special_info OUT NOCOPY ghr_api.special_information_type
1479 );
1480
1481 -- ---------------------------------------------------------------------------
1482 -- |--------------------------< return_education_Details >----------------|
1483 -- --------------------------------------------------------------------------
1484
1485 Procedure return_education_Details
1486 (p_person_id in per_people_f.person_id%type,
1487 p_effective_date in date,
1488 p_education_level OUT NOCOPY per_analysis_criteria.segment1%type,
1489 p_academic_discipline OUT NOCOPY per_analysis_criteria.segment2%type,
1490 p_year_degree_attained OUT NOCOPY per_analysis_criteria.segment3%type
1491 );
1492
1493 ------------------------------------------------------------------------------
1494
1495 Procedure call_workflow
1496 (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
1497 p_action_taken in ghr_pa_routing_history.action_taken%type,
1498 p_old_action_taken in ghr_pa_routing_history.action_taken%type default null,
1499 p_error in varchar2 default null
1500 );
1501
1502 FUNCTION restricted_attribute (
1503 p_user_name in VARCHAR2
1504 , p_attribute in VARCHAR2
1505 )
1506 RETURN BOOLEAN;
1507
1508 end ghr_api;
1509
1510 ----------------------------------------------------------------------------
1511