1 PACKAGE ghr_pa_requests_pkg AUTHID CURRENT_USER AS
2 /* $Header: ghparqst.pkh 120.5.12020000.2 2012/07/05 14:41:21 amnaraya ship $ */
3
4 PROCEDURE get_process_method(
5 p_noa_family_code IN ghr_noa_fam_proc_methods.noa_family_code%TYPE
6 ,p_form_block_name IN ghr_pa_data_fields.form_block_name%TYPE
7 ,p_form_field_name IN ghr_pa_data_fields.form_field_name%TYPE
8 ,p_effective_date IN DATE
9 ,p_process_method_code OUT NOCOPY VARCHAR2
10 ,p_navigable_flag OUT NOCOPY VARCHAR2);
11 --
12 FUNCTION get_data_field_name(
13 p_form_block_name IN ghr_pa_data_fields.form_block_name%TYPE
14 ,p_form_field_name IN ghr_pa_data_fields.form_field_name%TYPE)
15 RETURN VARCHAR2;
16 --
17 PROCEDURE get_restricted_process_method(
18 p_restricted_form IN ghr_restricted_proc_methods.restricted_form%TYPE
19 ,p_form_block_name IN ghr_pa_data_fields.form_block_name%TYPE
20 ,p_form_field_name IN ghr_pa_data_fields.form_field_name%TYPE
21 ,p_restricted_proc_method OUT NOCOPY VARCHAR2);
22 --
23 FUNCTION get_lookup_meaning(
24 p_application_id NUMBER
25 ,p_lookup_type hr_lookups.lookup_type%TYPE
26 ,p_lookup_code hr_lookups.lookup_code%TYPE)
27 RETURN VARCHAR2;
28
29 -- This is very similar to the function get_lookup_meaning above except it returns the
30 -- description as opposed to the meaning, this is required in particular for Legal Authority
31 -- since menaing is forced to be unique we use the description
32 FUNCTION get_lookup_description(
33 p_application_id NUMBER
34 ,p_lookup_type hr_lookups.lookup_type%TYPE
35 ,p_lookup_code hr_lookups.lookup_code%TYPE)
36 RETURN VARCHAR2;
37 -- Removed Pragma reference, not needed in 8i.
38 -- (See bug# 1014743)
39 -- pragma restrict_references (get_lookup_description, WNDS, WNPS);
40
41 FUNCTION get_noa_family_name(
42
43 p_noa_family_code ghr_families.noa_family_code%TYPE)
44 RETURN VARCHAR2;
45
46 FUNCTION get_routing_group_name(
47 p_routing_group_id ghr_routing_groups.routing_group_id%TYPE)
48 RETURN VARCHAR2;
49
50 FUNCTION get_full_name(
51 p_person_id per_people_f.person_id%TYPE
52 ,p_effective_date date)
53 RETURN VARCHAR2;
54
55 FUNCTION get_full_name_unsecure(
56 p_person_id per_people_f.person_id%TYPE
57 ,p_effective_date date)
58 RETURN VARCHAR2;
59
60 FUNCTION get_noa_descriptor(
61 p_nature_of_action_id IN ghr_nature_of_actions.nature_of_action_id%TYPE)
62 RETURN VARCHAR2;
63 --
64 FUNCTION get_remark_descriptor(
65 p_remark_id IN ghr_remarks.remark_id%TYPE)
66 RETURN VARCHAR2;
67 -- possibly need description returned not just name?
68
69 -- Bug#5482191 Added the function get_personnel_system_indicator
70 FUNCTION get_personnel_system_indicator(
71 p_position_id hr_all_positions_f.position_id%TYPE
72 ,p_effective_date date)
73 RETURN VARCHAR2;
74
75 --Begin bug# 8653508
76 FUNCTION get_flsa_category(
77 p_position_id hr_all_positions_f.position_id%TYPE
78 ,p_effective_date date default sysdate)
79 RETURN VARCHAR2;
80
81 FUNCTION get_bargaining_unit(
82 p_position_id hr_all_positions_f.position_id%TYPE
83 ,p_effective_date date default sysdate)
84 RETURN VARCHAR2;
85
86 FUNCTION get_work_schedule(
87 p_position_id hr_all_positions_f.position_id%TYPE
88 ,p_effective_date date default sysdate)
89 RETURN VARCHAR2;
90
91 FUNCTION get_valid_grade(
92 p_position_id hr_all_positions_f.position_id%TYPE
93 ,p_effective_date date default sysdate)
94 RETURN VARCHAR2;
95
96 FUNCTION get_duty_station(
97 p_position_id hr_all_positions_f.position_id%TYPE
98 ,p_effective_date date default sysdate)
99 RETURN VARCHAR2;
100
101 FUNCTION get_occ_series(
102 p_position_id hr_all_positions_f.position_id%TYPE
103 ,p_effective_date date default sysdate,
104 p_business_group_id in per_assignments_f.business_group_id%type)
105 RETURN VARCHAR2;
106
107 FUNCTION get_appropriation_code(
108 p_position_id hr_all_positions_f.position_id%TYPE
109 ,p_effective_date date default sysdate)
110 RETURN VARCHAR2;
111
112 --Bug# 9647383
113 FUNCTION get_pos_organization( p_position_id hr_all_positions_f.position_id%TYPE
114 ,p_effective_date date default sysdate
115 ,p_business_group_id in per_assignments_f.business_group_id%type)
116 RETURN VARCHAR2;
117 --End bug# 8653508
118
119 --Begin Bug# 13684234
120 function get_pos_availability_status (p_availability_status_id number) return varchar2;
121 --End Bug# 13684234
122
123 --
124 PROCEDURE get_default_routing_group(p_user_name IN fnd_user.user_name%TYPE
125 ,p_routing_group_id IN OUT NOCOPY NUMBER
126 ,p_initiator_flag IN OUT NOCOPY VARCHAR2
127 ,p_requester_flag IN OUT NOCOPY VARCHAR2
128 ,p_authorizer_flag IN OUT NOCOPY VARCHAR2
129 ,p_personnelist_flag IN OUT NOCOPY VARCHAR2
130 ,p_approver_flag IN OUT NOCOPY VARCHAR2
131 ,p_reviewer_flag IN OUT NOCOPY VARCHAR2);
132 --
133 PROCEDURE get_last_routing_list(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
134 ,p_routing_list_id OUT NOCOPY ghr_routing_lists.routing_list_id%TYPE
135 ,p_routing_list_name OUT NOCOPY ghr_routing_lists.name%TYPE
136 ,p_next_seq_number OUT NOCOPY ghr_routing_list_members.seq_number%TYPE
137 ,p_next_user_name OUT NOCOPY ghr_routing_list_members.user_name%TYPE
138 ,p_next_groupbox_id OUT NOCOPY ghr_routing_list_members.groupbox_id%TYPE
139 ,p_broken IN OUT NOCOPY BOOLEAN);
140 --
141 PROCEDURE get_roles (p_pa_request_id in number
142 ,p_routing_group_id in number
143 ,p_user_name in varchar2 default null
144 ,p_initiator_flag in OUT NOCOPY varchar2
145 ,p_requester_flag in OUT NOCOPY varchar2
146 ,p_authorizer_flag in OUT NOCOPY varchar2
147 ,p_personnelist_flag in OUT NOCOPY varchar2
148 ,p_approver_flag in OUT NOCOPY varchar2
149 ,p_reviewer_flag in OUT NOCOPY varchar2);
150 --
151 PROCEDURE get_person_details (p_person_id IN per_people_f.person_id%TYPE
152 ,p_effective_date IN DATE
153 ,p_national_identifier IN OUT NOCOPY per_people_f.national_identifier%TYPE
154 ,p_date_of_birth IN OUT NOCOPY per_people_f.date_of_birth%TYPE
155 ,p_last_name IN OUT NOCOPY per_people_f.last_name%TYPE
156 ,p_first_name IN OUT NOCOPY per_people_f.first_name%TYPE
157 ,p_middle_names IN OUT NOCOPY per_people_f.middle_names%TYPE);
158 --
159 PROCEDURE get_duty_station_details (p_duty_station_id IN ghr_duty_stations_v.duty_station_id%TYPE
160 ,p_effective_date IN DATE
161 ,p_duty_station_code IN OUT NOCOPY ghr_duty_stations_v.duty_station_code%TYPE
162 ,p_duty_station_desc IN OUT NOCOPY ghr_duty_stations_v.duty_station_desc%TYPE);
163 --
164 PROCEDURE get_SF52_person_ddf_details (p_person_id IN per_people_f.person_id%TYPE
165 ,p_date_effective IN date default sysdate
166 ,p_citizenship OUT NOCOPY varchar2
167 ,p_veterans_preference OUT NOCOPY varchar2
168 ,p_veterans_pref_for_rif OUT NOCOPY varchar2
169 ,p_veterans_status OUT NOCOPY varchar2
170 ,p_scd_leave OUT NOCOPY varchar2);
171 --
172 PROCEDURE get_SF52_asg_ddf_details (p_assignment_id IN per_assignments_f.assignment_id%TYPE
173 ,p_date_effective IN date default sysdate
174 ,p_tenure OUT NOCOPY varchar2
175 ,p_annuitant_indicator OUT NOCOPY varchar2
176 ,p_pay_rate_determinant OUT NOCOPY varchar2
177 ,p_work_schedule OUT NOCOPY varchar2
178 ,p_part_time_hours OUT NOCOPY varchar2);
179 --
180 PROCEDURE get_SF52_pos_ddf_details (p_position_id IN hr_all_positions_f.position_id%TYPE
181 ,p_date_effective IN date default sysdate
182 ,p_flsa_category OUT NOCOPY varchar2
183 ,p_bargaining_unit_status OUT NOCOPY varchar2
184 ,p_work_schedule OUT NOCOPY varchar2
185 ,p_functional_class OUT NOCOPY varchar2
186 ,p_supervisory_status OUT NOCOPY varchar2
187 ,p_position_occupied OUT NOCOPY varchar2
188 ,p_appropriation_code1 OUT NOCOPY varchar2
189 ,p_appropriation_code2 OUT NOCOPY varchar2
190 ,p_personnel_office_id OUT NOCOPY varchar2
191 ,p_office_symbol OUT NOCOPY varchar2
192 ,p_part_time_hours OUT NOCOPY number);
193 --
194 PROCEDURE get_SF52_loc_ddf_details (p_location_id IN hr_locations.location_id%TYPE
195 ,p_duty_station_id OUT NOCOPY varchar2);
196 --
197 PROCEDURE get_address_details (p_person_id IN per_addresses.person_id%TYPE
198 ,p_effective_date IN DATE
199 ,p_address_line1 OUT NOCOPY per_addresses.address_line1%TYPE
200 ,p_address_line2 OUT NOCOPY per_addresses.address_line2%TYPE
201 ,p_address_line3 OUT NOCOPY per_addresses.address_line3%TYPE
202 ,p_town_or_city OUT NOCOPY per_addresses.town_or_city%TYPE
203 ,p_region_2 OUT NOCOPY per_addresses.region_2%TYPE
204 ,p_postal_code OUT NOCOPY per_addresses.postal_code%TYPE
205 ,p_country OUT NOCOPY per_addresses.country%TYPE
206 ,p_territory_short_name OUT NOCOPY varchar2);
207 --
208 PROCEDURE get_SF52_to_data_elements
209 (p_position_id IN hr_all_positions_f.position_id%TYPE
210 ,p_effective_date IN date default sysdate
211 ,p_prd IN ghr_pa_requests.pay_rate_determinant%TYPE
212 ,p_grade_id IN OUT NOCOPY number
213 ,p_job_id IN OUT NOCOPY number
214 ,p_organization_id IN OUT NOCOPY number
215 ,p_location_id IN OUT NOCOPY number
216 ,p_pay_plan OUT NOCOPY varchar2
217 ,p_occ_code OUT NOCOPY varchar2
218 ,p_grade_or_level OUT NOCOPY varchar2
219 ,p_pay_basis OUT NOCOPY varchar2
220 ,p_position_org_line1 OUT NOCOPY varchar2
221 ,p_position_org_line2 OUT NOCOPY varchar2
222 ,p_position_org_line3 OUT NOCOPY varchar2
223 ,p_position_org_line4 OUT NOCOPY varchar2
224 ,p_position_org_line5 OUT NOCOPY varchar2
225 ,p_position_org_line6 OUT NOCOPY varchar2
226 ,p_duty_station_id OUT NOCOPY number
227 );
228
229 -- This procedure only really needs to be called for realignment. For this NOA the 6 'address' lines seen
230 -- on the to side should come from the 'position organization' on the PAR extra info (if given)
231 --
232 PROCEDURE get_rei_org_lines (p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
233 ,p_organization_id IN OUT NOCOPY VARCHAR2
234 ,p_position_org_line1 OUT NOCOPY varchar2
235 ,p_position_org_line2 OUT NOCOPY varchar2
236 ,p_position_org_line3 OUT NOCOPY varchar2
237 ,p_position_org_line4 OUT NOCOPY varchar2
238 ,p_position_org_line5 OUT NOCOPY varchar2
239 ,p_position_org_line6 OUT NOCOPY varchar2);
240
241 -- This function checks to see if the given DF and context value has any segemnts
242 -- defined
243 FUNCTION segments_defined (p_flexfield_name IN VARCHAR2
244 ,p_context_code IN VARCHAR2)
245 RETURN BOOLEAN;
246
247 --
248 -- This function simply returns the required flag (either Y or N) for a given remark id and NOAC
249 -- It is used on the post-query in the SF52 form to set the required indicator on the remarks block
250 FUNCTION get_noac_remark_req (p_first_noa_id IN ghr_noac_remarks.nature_of_action_id%TYPE
251 ,p_second_noa_id IN ghr_noac_remarks.nature_of_action_id%TYPE
252 ,p_remark_id IN ghr_noac_remarks.nature_of_action_id%TYPE
253 ,p_effective_date IN DATE)
254 RETURN VARCHAR2;
255
256 -- This function simply returns the person_id for the given username
257 FUNCTION get_user_person_id (p_user_name IN VARCHAR2)
258 RETURN NUMBER;
259
260 -- This procedure will return the noac id, code and description if there is only one noac in the given
261 -- family, otherwise it returns null
262 PROCEDURE get_single_noac_for_fam (p_noa_family_code IN ghr_noa_families.noa_family_code%TYPE
263 ,p_effective_date IN DATE
264 ,p_nature_of_action_id IN OUT NOCOPY ghr_nature_of_actions.nature_of_action_id%TYPE
265 ,p_code IN OUT NOCOPY ghr_nature_of_actions.code%TYPE
266 ,p_description IN OUT NOCOPY ghr_nature_of_actions.description%TYPE);
267 --
268 -- This procedure will return the Legal Authority Code and Description if there is only one for the given
269 -- NOAC, otherwise it returns null
270 PROCEDURE get_single_lac_for_noac (p_nature_of_action_id IN ghr_noac_las.nature_of_action_id%TYPE
271 ,p_effective_date IN DATE
272 ,p_lac_code IN OUT NOCOPY ghr_noac_las.lac_lookup_code%TYPE
273 ,p_description IN OUT NOCOPY VARCHAR2);
274 --
275 -- This function simply returns the restricted form (if any) for the given person
276 FUNCTION get_restricted_form (p_person_id IN NUMBER)
277 RETURN VARCHAR2;
278 --
279 -- Given a noa of action id return the processing method family it is in
280 FUNCTION get_noa_pm_family (p_nature_of_action_id IN ghr_noa_families.nature_of_action_id%TYPE)
281 RETURN VARCHAR2;
282 pragma restrict_references (get_noa_pm_family, WNDS, WNPS);
283 --
284 -- Bug#3941541 Overloaded function with effective date as another parameter
285 FUNCTION get_noa_pm_family (p_nature_of_action_id IN ghr_noa_families.nature_of_action_id%TYPE,
286 p_effective_date IN DATE)
287 RETURN VARCHAR2;
288 pragma restrict_references (get_noa_pm_family, WNDS, WNPS);
289 --
290 -- As above except pass in a noa code and it returns the family it is in
291 FUNCTION get_noa_pm_family (p_noa_code IN ghr_nature_of_actions.code%TYPE)
292 RETURN VARCHAR2;
293 pragma restrict_references (get_noa_pm_family, WNDS, WNPS);
294 --
295 -- Given a position_id and a date check to see if anybody has been assigned
296 -- that position at the date and return 'TRUE' if they have
297 FUNCTION position_assigned (p_position_id IN NUMBER
298 ,p_effective_date IN DATE)
299 RETURN VARCHAR2;
300 pragma restrict_references (position_assigned, WNDS, WNPS);
301 --
302 -- This function looks at the AOL table FND_CONCURRENT_PROGRAMS to return the defualt printer for the
303 -- given concurrent program , Doesn't pass in application ID as 8301 is assumed
304 FUNCTION get_default_printer (p_concurrent_program_name IN VARCHAR2)
305 RETURN VARCHAR2;
306 --
307 -- This function returns TRUE if the PA Request passed in has an SF50 produced
308 FUNCTION SF50_produced (p_pa_request_id IN NUMBER)
309 RETURN BOOLEAN;
310 --
311 -- This function returns TRUE if the person id passed in is valid for the given date
312 -- The noa_family_code determines what is a valid person on the SF52, i.e for APP
313 -- family they must be Applicant otherwise they must be Employees.
314 -- The select statements need to be the same as on the SF52 as this is only
315 -- checking the person is still valid in case the user alters the effective
316 -- date after they used the LOV in the form to pick up a person!
317 FUNCTION check_person_id_SF52 (p_person_id IN NUMBER
318 ,p_effective_date IN DATE
319 ,p_business_group_id IN NUMBER
320 ,p_user_person_id IN NUMBER
321 ,p_noa_family_code IN VARCHAR2
322 ,p_second_noa_family_code IN VARCHAR2)
323 RETURN BOOLEAN;
324 --
325 -- This procedure gets the amounts that are not displayed in a correction form that
326 -- are needed to do an other pay totals
327
328 FUNCTION check_valid_person_id (p_person_id IN NUMBER
329 ,p_effective_date IN DATE
330 ,p_business_group_id IN NUMBER
331 ,p_user_person_id IN NUMBER
332 ,p_noa_family_code IN VARCHAR2
333 ,p_second_noa_family_code IN VARCHAR2)
334 RETURN VARCHAR2;
335 --
336 PROCEDURE get_corr_other_pay(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
337 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
338 ,p_to_basic_pay OUT NOCOPY NUMBER
339 ,p_to_adj_basic_pay OUT NOCOPY NUMBER
340 ,p_to_auo_ppi OUT NOCOPY VARCHAR2
341 ,p_to_auo OUT NOCOPY NUMBER
342 ,p_to_ap_ppi OUT NOCOPY VARCHAR2
343 ,p_to_ap OUT NOCOPY NUMBER
344 ,p_to_retention_allowance OUT NOCOPY NUMBER
345 ,p_to_supervisory_differential OUT NOCOPY NUMBER
346 ,p_to_staffing_differential OUT NOCOPY NUMBER
347 ,p_to_pay_basis OUT NOCOPY VARCHAR2
348 -- Corr Warn
349 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
350 ,p_pay_plan OUT NOCOPY VARCHAR2
351 ,p_to_position_id OUT NOCOPY NUMBER
352 ,p_person_id OUT NOCOPY NUMBER
353 ,p_locality_adj OUT NOCOPY NUMBER
354 -- Corr Warn
355 );
356
357 PROCEDURE get_corr_rpa_other_pay(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
358 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
359 ,p_from_basic_pay OUT NOCOPY NUMBER
360 ,p_to_basic_pay OUT NOCOPY NUMBER
361 ,p_to_adj_basic_pay OUT NOCOPY NUMBER
362 ,p_to_auo_ppi OUT NOCOPY VARCHAR2
363 ,p_to_auo OUT NOCOPY NUMBER
364 ,p_to_ap_ppi OUT NOCOPY VARCHAR2
365 ,p_to_ap OUT NOCOPY NUMBER
366 ,p_to_retention_allowance OUT NOCOPY NUMBER
367 ,p_to_supervisory_differential OUT NOCOPY NUMBER
368 ,p_to_staffing_differential OUT NOCOPY NUMBER
369 ,p_to_pay_basis OUT NOCOPY VARCHAR2
370 -- Corr Warn
371 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
372 ,p_pay_plan OUT NOCOPY VARCHAR2
373 ,p_to_position_id OUT NOCOPY NUMBER
374 ,p_person_id OUT NOCOPY NUMBER
375 ,p_locality_adj OUT NOCOPY NUMBER
376 ,p_from_step_or_rate OUT NOCOPY VARCHAR2
377 ,p_to_step_or_rate OUT NOCOPY VARCHAR2
378 -- Corr Warn
379 );
380
381 --
382 -- This procedure gets the amounts that are not displayed in a correction form that
383 -- are needed to do an award
384 PROCEDURE get_corr_award (p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
385 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
386 ,p_from_basic_pay OUT NOCOPY NUMBER
387 ,p_from_pay_basis OUT NOCOPY VARCHAR2
388 );
389 --
390 -- The following Function returns the position_working_title of the person , for the position
391 -- on his primary Assignment
392 FUNCTION get_position_work_title(p_position_id IN number,
393 p_effective_date IN date default trunc(sysdate))
394 RETURN varchar2;
395 FUNCTION get_position_work_title(p_person_id IN varchar2,
396 p_effective_date IN date default trunc(sysdate))
397 RETURN varchar2;
398 --
399
400 -- This Function returns fullname in the format (fml) i.e <First_name> <Middle_name>.<Last Name>
401 FUNCTION get_full_name_fml(p_person_id IN varchar2,
402 p_effective_date IN date default trunc(sysdate))
403 RETURN varchar2;
404 --
405 FUNCTION get_upd34_pay_basis (p_person_id IN per_people_f.person_id%TYPE
406 ,p_position_id IN per_positions.position_id%type
407 ,p_prd IN ghr_pa_requests.pay_rate_determinant%TYPE
408 ,p_noa_code IN varchar2 DEFAULT NULL
409 ,p_pa_request_id IN NUMBER DEFAULT NULL
410 ,p_effective_date IN DATE)
411 RETURN VARCHAR2;
412 --
413 PROCEDURE update34_implement_cancel (p_person_id IN NUMBER
414 ,p_assignment_id IN NUMBER
415 ,p_date IN DATE
416 ,p_altered_pa_request_id in NUMBER);
417 --
418 --
419 FUNCTION temp_step_true (p_pa_request_id IN ghr_pa_requests.pa_request_id%type)
420 RETURN BOOLEAN;
421 --
422 --
423 END ghr_pa_requests_pkg;
424
[Home] [Help]