1 PACKAGE ghr_pa_requests_pkg AS
2 /* $Header: ghparqst.pkh 120.2.12010000.1 2008/07/28 10:35:30 appldev 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 --
76 PROCEDURE get_default_routing_group(p_user_name IN fnd_user.user_name%TYPE
77 ,p_routing_group_id IN OUT NOCOPY NUMBER
78 ,p_initiator_flag IN OUT NOCOPY VARCHAR2
79 ,p_requester_flag IN OUT NOCOPY VARCHAR2
80 ,p_authorizer_flag IN OUT NOCOPY VARCHAR2
81 ,p_personnelist_flag IN OUT NOCOPY VARCHAR2
82 ,p_approver_flag IN OUT NOCOPY VARCHAR2
83 ,p_reviewer_flag IN OUT NOCOPY VARCHAR2);
84 --
85 PROCEDURE get_last_routing_list(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
86 ,p_routing_list_id OUT NOCOPY ghr_routing_lists.routing_list_id%TYPE
87 ,p_routing_list_name OUT NOCOPY ghr_routing_lists.name%TYPE
88 ,p_next_seq_number OUT NOCOPY ghr_routing_list_members.seq_number%TYPE
89 ,p_next_user_name OUT NOCOPY ghr_routing_list_members.user_name%TYPE
90 ,p_next_groupbox_id OUT NOCOPY ghr_routing_list_members.groupbox_id%TYPE
91 ,p_broken IN OUT NOCOPY BOOLEAN);
92 --
93 PROCEDURE get_roles (p_pa_request_id in number
94 ,p_routing_group_id in number
95 ,p_user_name in varchar2 default null
96 ,p_initiator_flag in OUT NOCOPY varchar2
97 ,p_requester_flag in OUT NOCOPY varchar2
98 ,p_authorizer_flag in OUT NOCOPY varchar2
99 ,p_personnelist_flag in OUT NOCOPY varchar2
100 ,p_approver_flag in OUT NOCOPY varchar2
101 ,p_reviewer_flag in OUT NOCOPY varchar2);
102 --
103 PROCEDURE get_person_details (p_person_id IN per_people_f.person_id%TYPE
104 ,p_effective_date IN DATE
105 ,p_national_identifier IN OUT NOCOPY per_people_f.national_identifier%TYPE
106 ,p_date_of_birth IN OUT NOCOPY per_people_f.date_of_birth%TYPE
107 ,p_last_name IN OUT NOCOPY per_people_f.last_name%TYPE
108 ,p_first_name IN OUT NOCOPY per_people_f.first_name%TYPE
109 ,p_middle_names IN OUT NOCOPY per_people_f.middle_names%TYPE);
110 --
111 PROCEDURE get_duty_station_details (p_duty_station_id IN ghr_duty_stations_v.duty_station_id%TYPE
112 ,p_effective_date IN DATE
113 ,p_duty_station_code IN OUT NOCOPY ghr_duty_stations_v.duty_station_code%TYPE
114 ,p_duty_station_desc IN OUT NOCOPY ghr_duty_stations_v.duty_station_desc%TYPE);
115 --
116 PROCEDURE get_SF52_person_ddf_details (p_person_id IN per_people_f.person_id%TYPE
117 ,p_date_effective IN date default sysdate
118 ,p_citizenship OUT NOCOPY varchar2
119 ,p_veterans_preference OUT NOCOPY varchar2
120 ,p_veterans_pref_for_rif OUT NOCOPY varchar2
121 ,p_veterans_status OUT NOCOPY varchar2
122 ,p_scd_leave OUT NOCOPY varchar2);
123 --
124 PROCEDURE get_SF52_asg_ddf_details (p_assignment_id IN per_assignments_f.assignment_id%TYPE
125 ,p_date_effective IN date default sysdate
126 ,p_tenure OUT NOCOPY varchar2
127 ,p_annuitant_indicator OUT NOCOPY varchar2
128 ,p_pay_rate_determinant OUT NOCOPY varchar2
129 ,p_work_schedule OUT NOCOPY varchar2
130 ,p_part_time_hours OUT NOCOPY varchar2);
131 --
132 PROCEDURE get_SF52_pos_ddf_details (p_position_id IN hr_all_positions_f.position_id%TYPE
133 ,p_date_effective IN date default sysdate
134 ,p_flsa_category OUT NOCOPY varchar2
135 ,p_bargaining_unit_status OUT NOCOPY varchar2
136 ,p_work_schedule OUT NOCOPY varchar2
137 ,p_functional_class OUT NOCOPY varchar2
138 ,p_supervisory_status OUT NOCOPY varchar2
139 ,p_position_occupied OUT NOCOPY varchar2
140 ,p_appropriation_code1 OUT NOCOPY varchar2
141 ,p_appropriation_code2 OUT NOCOPY varchar2
142 ,p_personnel_office_id OUT NOCOPY varchar2
143 ,p_office_symbol OUT NOCOPY varchar2
144 ,p_part_time_hours OUT NOCOPY number);
145 --
146 PROCEDURE get_SF52_loc_ddf_details (p_location_id IN hr_locations.location_id%TYPE
147 ,p_duty_station_id OUT NOCOPY varchar2);
148 --
149 PROCEDURE get_address_details (p_person_id IN per_addresses.person_id%TYPE
150 ,p_effective_date IN DATE
151 ,p_address_line1 OUT NOCOPY per_addresses.address_line1%TYPE
152 ,p_address_line2 OUT NOCOPY per_addresses.address_line2%TYPE
153 ,p_address_line3 OUT NOCOPY per_addresses.address_line3%TYPE
154 ,p_town_or_city OUT NOCOPY per_addresses.town_or_city%TYPE
155 ,p_region_2 OUT NOCOPY per_addresses.region_2%TYPE
156 ,p_postal_code OUT NOCOPY per_addresses.postal_code%TYPE
157 ,p_country OUT NOCOPY per_addresses.country%TYPE
158 ,p_territory_short_name OUT NOCOPY varchar2);
159 --
160 PROCEDURE get_SF52_to_data_elements
161 (p_position_id IN hr_all_positions_f.position_id%TYPE
162 ,p_effective_date IN date default sysdate
163 ,p_prd IN ghr_pa_requests.pay_rate_determinant%TYPE
164 ,p_grade_id IN OUT NOCOPY number
165 ,p_job_id IN OUT NOCOPY number
166 ,p_organization_id IN OUT NOCOPY number
167 ,p_location_id IN OUT NOCOPY number
168 ,p_pay_plan OUT NOCOPY varchar2
169 ,p_occ_code OUT NOCOPY varchar2
170 ,p_grade_or_level OUT NOCOPY varchar2
171 ,p_pay_basis OUT NOCOPY varchar2
172 ,p_position_org_line1 OUT NOCOPY varchar2
173 ,p_position_org_line2 OUT NOCOPY varchar2
174 ,p_position_org_line3 OUT NOCOPY varchar2
175 ,p_position_org_line4 OUT NOCOPY varchar2
176 ,p_position_org_line5 OUT NOCOPY varchar2
177 ,p_position_org_line6 OUT NOCOPY varchar2
178 ,p_duty_station_id OUT NOCOPY number
179 );
180
181 -- This procedure only really needs to be called for realignment. For this NOA the 6 'address' lines seen
182 -- on the to side should come from the 'position organization' on the PAR extra info (if given)
183 --
184 PROCEDURE get_rei_org_lines (p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
185 ,p_organization_id IN OUT NOCOPY VARCHAR2
186 ,p_position_org_line1 OUT NOCOPY varchar2
187 ,p_position_org_line2 OUT NOCOPY varchar2
188 ,p_position_org_line3 OUT NOCOPY varchar2
189 ,p_position_org_line4 OUT NOCOPY varchar2
190 ,p_position_org_line5 OUT NOCOPY varchar2
191 ,p_position_org_line6 OUT NOCOPY varchar2);
192
193 -- This function checks to see if the given DF and context value has any segemnts
194 -- defined
195 FUNCTION segments_defined (p_flexfield_name IN VARCHAR2
196 ,p_context_code IN VARCHAR2)
197 RETURN BOOLEAN;
198
199 --
200 -- This function simply returns the required flag (either Y or N) for a given remark id and NOAC
201 -- It is used on the post-query in the SF52 form to set the required indicator on the remarks block
202 FUNCTION get_noac_remark_req (p_first_noa_id IN ghr_noac_remarks.nature_of_action_id%TYPE
203 ,p_second_noa_id IN ghr_noac_remarks.nature_of_action_id%TYPE
204 ,p_remark_id IN ghr_noac_remarks.nature_of_action_id%TYPE
205 ,p_effective_date IN DATE)
206 RETURN VARCHAR2;
207
208 -- This function simply returns the person_id for the given username
209 FUNCTION get_user_person_id (p_user_name IN VARCHAR2)
210 RETURN NUMBER;
211
212 -- This procedure will return the noac id, code and description if there is only one noac in the given
213 -- family, otherwise it returns null
214 PROCEDURE get_single_noac_for_fam (p_noa_family_code IN ghr_noa_families.noa_family_code%TYPE
215 ,p_effective_date IN DATE
216 ,p_nature_of_action_id IN OUT NOCOPY ghr_nature_of_actions.nature_of_action_id%TYPE
217 ,p_code IN OUT NOCOPY ghr_nature_of_actions.code%TYPE
218 ,p_description IN OUT NOCOPY ghr_nature_of_actions.description%TYPE);
219 --
220 -- This procedure will return the Legal Authority Code and Description if there is only one for the given
221 -- NOAC, otherwise it returns null
222 PROCEDURE get_single_lac_for_noac (p_nature_of_action_id IN ghr_noac_las.nature_of_action_id%TYPE
223 ,p_effective_date IN DATE
224 ,p_lac_code IN OUT NOCOPY ghr_noac_las.lac_lookup_code%TYPE
225 ,p_description IN OUT NOCOPY VARCHAR2);
226 --
227 -- This function simply returns the restricted form (if any) for the given person
228 FUNCTION get_restricted_form (p_person_id IN NUMBER)
229 RETURN VARCHAR2;
230 --
231 -- Given a noa of action id return the processing method family it is in
232 FUNCTION get_noa_pm_family (p_nature_of_action_id IN ghr_noa_families.nature_of_action_id%TYPE)
233 RETURN VARCHAR2;
234 pragma restrict_references (get_noa_pm_family, WNDS, WNPS);
235 --
236 -- Bug#3941541 Overloaded function with effective date as another parameter
237 FUNCTION get_noa_pm_family (p_nature_of_action_id IN ghr_noa_families.nature_of_action_id%TYPE,
238 p_effective_date IN DATE)
239 RETURN VARCHAR2;
240 pragma restrict_references (get_noa_pm_family, WNDS, WNPS);
241 --
242 -- As above except pass in a noa code and it returns the family it is in
243 FUNCTION get_noa_pm_family (p_noa_code IN ghr_nature_of_actions.code%TYPE)
244 RETURN VARCHAR2;
245 pragma restrict_references (get_noa_pm_family, WNDS, WNPS);
246 --
247 -- Given a position_id and a date check to see if anybody has been assigned
248 -- that position at the date and return 'TRUE' if they have
249 FUNCTION position_assigned (p_position_id IN NUMBER
250 ,p_effective_date IN DATE)
251 RETURN VARCHAR2;
252 pragma restrict_references (position_assigned, WNDS, WNPS);
253 --
254 -- This function looks at the AOL table FND_CONCURRENT_PROGRAMS to return the defualt printer for the
255 -- given concurrent program , Doesn't pass in application ID as 8301 is assumed
256 FUNCTION get_default_printer (p_concurrent_program_name IN VARCHAR2)
257 RETURN VARCHAR2;
258 --
259 -- This function returns TRUE if the PA Request passed in has an SF50 produced
260 FUNCTION SF50_produced (p_pa_request_id IN NUMBER)
261 RETURN BOOLEAN;
262 --
263 -- This function returns TRUE if the person id passed in is valid for the given date
264 -- The noa_family_code determines what is a valid person on the SF52, i.e for APP
265 -- family they must be Applicant otherwise they must be Employees.
266 -- The select statements need to be the same as on the SF52 as this is only
267 -- checking the person is still valid in case the user alters the effective
268 -- date after they used the LOV in the form to pick up a person!
269 FUNCTION check_person_id_SF52 (p_person_id IN NUMBER
270 ,p_effective_date IN DATE
271 ,p_business_group_id IN NUMBER
272 ,p_user_person_id IN NUMBER
273 ,p_noa_family_code IN VARCHAR2
274 ,p_second_noa_family_code IN VARCHAR2)
275 RETURN BOOLEAN;
276 --
277 -- This procedure gets the amounts that are not displayed in a correction form that
278 -- are needed to do an other pay totals
279
280 FUNCTION check_valid_person_id (p_person_id IN NUMBER
281 ,p_effective_date IN DATE
282 ,p_business_group_id IN NUMBER
283 ,p_user_person_id IN NUMBER
284 ,p_noa_family_code IN VARCHAR2
285 ,p_second_noa_family_code IN VARCHAR2)
286 RETURN VARCHAR2;
287 --
288 PROCEDURE get_corr_other_pay(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
289 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
290 ,p_to_basic_pay OUT NOCOPY NUMBER
291 ,p_to_adj_basic_pay OUT NOCOPY NUMBER
292 ,p_to_auo_ppi OUT NOCOPY VARCHAR2
293 ,p_to_auo OUT NOCOPY NUMBER
294 ,p_to_ap_ppi OUT NOCOPY VARCHAR2
295 ,p_to_ap OUT NOCOPY NUMBER
296 ,p_to_retention_allowance OUT NOCOPY NUMBER
297 ,p_to_supervisory_differential OUT NOCOPY NUMBER
298 ,p_to_staffing_differential OUT NOCOPY NUMBER
299 ,p_to_pay_basis OUT NOCOPY VARCHAR2
300 -- Corr Warn
301 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
302 ,p_pay_plan OUT NOCOPY VARCHAR2
303 ,p_to_position_id OUT NOCOPY NUMBER
304 ,p_person_id OUT NOCOPY NUMBER
305 ,p_locality_adj OUT NOCOPY NUMBER
306 -- Corr Warn
307 );
308
309 PROCEDURE get_corr_rpa_other_pay(p_pa_request_id IN ghr_pa_requests.pa_request_id%TYPE
310 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
311 ,p_from_basic_pay OUT NOCOPY NUMBER
312 ,p_to_basic_pay OUT NOCOPY NUMBER
313 ,p_to_adj_basic_pay OUT NOCOPY NUMBER
314 ,p_to_auo_ppi OUT NOCOPY VARCHAR2
315 ,p_to_auo OUT NOCOPY NUMBER
316 ,p_to_ap_ppi OUT NOCOPY VARCHAR2
317 ,p_to_ap OUT NOCOPY NUMBER
318 ,p_to_retention_allowance OUT NOCOPY NUMBER
319 ,p_to_supervisory_differential OUT NOCOPY NUMBER
320 ,p_to_staffing_differential OUT NOCOPY NUMBER
321 ,p_to_pay_basis OUT NOCOPY VARCHAR2
322 -- Corr Warn
323 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
324 ,p_pay_plan OUT NOCOPY VARCHAR2
325 ,p_to_position_id OUT NOCOPY NUMBER
326 ,p_person_id OUT NOCOPY NUMBER
327 ,p_locality_adj OUT NOCOPY NUMBER
328 ,p_from_step_or_rate OUT NOCOPY VARCHAR2
329 ,p_to_step_or_rate OUT NOCOPY VARCHAR2
330 -- Corr Warn
331 );
332
333 --
334 -- This procedure gets the amounts that are not displayed in a correction form that
335 -- are needed to do an award
336 PROCEDURE get_corr_award (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_from_basic_pay OUT NOCOPY NUMBER
339 ,p_from_pay_basis OUT NOCOPY VARCHAR2
340 );
341 --
342 -- The following Function returns the position_working_title of the person , for the position
343 -- on his primary Assignment
344 FUNCTION get_position_work_title(p_position_id IN number,
345 p_effective_date IN date default trunc(sysdate))
346 RETURN varchar2;
347 FUNCTION get_position_work_title(p_person_id IN varchar2,
348 p_effective_date IN date default trunc(sysdate))
349 RETURN varchar2;
350 --
351
352 -- This Function returns fullname in the format (fml) i.e <First_name> <Middle_name>.<Last Name>
353 FUNCTION get_full_name_fml(p_person_id IN varchar2,
354 p_effective_date IN date default trunc(sysdate))
355 RETURN varchar2;
356 --
357 FUNCTION get_upd34_pay_basis (p_person_id IN per_people_f.person_id%TYPE
358 ,p_position_id IN per_positions.position_id%type
359 ,p_prd IN ghr_pa_requests.pay_rate_determinant%TYPE
360 ,p_noa_code IN varchar2 DEFAULT NULL
361 ,p_pa_request_id IN NUMBER DEFAULT NULL
362 ,p_effective_date IN DATE)
363 RETURN VARCHAR2;
364 --
365 PROCEDURE update34_implement_cancel (p_person_id IN NUMBER
366 ,p_assignment_id IN NUMBER
367 ,p_date IN DATE
368 ,p_altered_pa_request_id in NUMBER);
369 --
370 --
371 FUNCTION temp_step_true (p_pa_request_id IN ghr_pa_requests.pa_request_id%type)
372 RETURN BOOLEAN;
373 --
374 --
375 END ghr_pa_requests_pkg;
376
[Home] [Help]