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