[Home] [Help]
PACKAGE BODY: APPS.GHR_WGI_PKG
Source
1 PACKAGE BODY ghr_wgi_pkg AS
2 /* $Header: ghwgipro.pkb 120.17.12010000.2 2010/01/12 10:40:56 managarw ship $ */
3 PROCEDURE ghr_wgi_process (
4 errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_personnel_office_id IN ghr_pa_requests.personnel_office_id%TYPE
7 DEFAULT NULL,
8 p_pay_plan IN ghr_pay_plans.pay_plan%TYPE
9 DEFAULT NULL
10 )
11 IS
12 l_errbuf VARCHAR2 (2000);
13 l_retcode NUMBER := 0;
14 BEGIN
15 --
16 -- call the main procedure
17 --
18 ghr_wgi_emp (p_errbuf => l_errbuf,
19 p_retcode => l_retcode,
20 p_personnel_office_id => p_personnel_office_id,
21 p_pay_plan => p_pay_plan
22 );
23 errbuf := l_errbuf;
24 retcode := l_retcode;
25 EXCEPTION
26 WHEN OTHERS
27 THEN
28 errbuf := NULL;
29 retcode := NULL;
30 RAISE;
31 END ghr_wgi_process;
32
33 --
34 --
35 --
36 PROCEDURE ghr_wgi_emp (
37 p_effective_date IN DATE DEFAULT TRUNC (SYSDATE),
38 p_frequency IN NUMBER DEFAULT 90,
39 p_log_flag IN CHAR DEFAULT 'N',
40 p_errbuf OUT NOCOPY VARCHAR2,
41 p_retcode OUT NOCOPY NUMBER,
42 p_personnel_office_id IN ghr_pa_requests.personnel_office_id%TYPE
43 DEFAULT NULL,
44 p_pay_plan IN ghr_pay_plans.pay_plan%TYPE
45 DEFAULT NULL
46 )
47 IS
48 --
49 -- Local variables
50
51
52
53 -- Bug 4377361 included EMP_APL for person type condition
54 CURSOR per_assign_cursor (l_effective_date IN DATE)
55 IS
56 SELECT ppf.person_id, ppf.national_identifier, ppf.date_of_birth,
57 ppf.last_name, ppf.first_name, ppf.middle_names,
58 ppf.full_name, paf.position_id, paf.assignment_id,
59 paf.grade_id, paf.job_id, paf.location_id,
60 paf.organization_id
61 FROM per_assignments_f paf, per_people_f ppf,
62 per_person_types ppt
63 WHERE ppf.person_id = paf.person_id
64 AND paf.primary_flag = 'Y'
65 AND paf.assignment_type <> 'B'
66 AND l_effective_date BETWEEN paf.effective_start_date
67 AND paf.effective_end_date
68 AND ppf.person_type_id = ppt.person_type_id
69 AND ppt.system_person_type IN ('EMP','EMP_APL')
70 AND l_effective_date BETWEEN ppf.effective_start_date
71 AND ppf.effective_end_date
72 AND ret_wgi_pay_date(paf.assignment_id,l_effective_date, p_frequency) IS NOT NULL
73 AND PAF.BUSINESS_GROUP_ID >=0; --Bug #5212248
74 -- TAR 4124874.999 Above condition added to filter out the
75 -- employees not having Pay date.
76
77
78
79 --
80 -- remove person id, set default frequency to 90, uncomment element api
81 --
82 l_exists BOOLEAN;
83 l_person_id per_people_f.person_id%TYPE;
84 l_assignment_id per_assignments.assignment_id%TYPE;
85 l_national_identifier per_people_f.national_identifier%TYPE;
86 l_date_of_birth per_people_f.date_of_birth%TYPE;
87 l_last_name per_people_f.last_name%TYPE;
88 l_first_name per_people_f.first_name%TYPE;
89 l_middle_names per_people_f.middle_names%TYPE;
90 l_full_name per_people_f.full_name%TYPE;
91 l_position_id per_assignments.position_id%TYPE;
92 l_grade_id ghr_pa_requests.to_grade_id%TYPE;
93 l_job_id per_assignments.job_id%TYPE;
94 l_location_id per_assignments.location_id%TYPE;
95 l_organization_id per_assignments.organization_id%TYPE;
96 l_proc VARCHAR2 (30) := 'AWGI :';
97 l_pay_basis per_pay_bases.pay_basis%TYPE;
98 l_organization_name hr_organization_units.NAME%TYPE;
99 l_org_address_line1 hr_locations.address_line_1%TYPE;
100 l_org_address_line2 hr_locations.address_line_2%TYPE;
101 l_org_address_line3 hr_locations.address_line_3%TYPE;
102 l_org_city hr_locations.town_or_city%TYPE;
103 l_org_state hr_locations.region_2%TYPE;
104 l_org_country hr_locations.country%TYPE;
105 l_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE;
106 l_pay_table_plan_id ghr_pay_plans.pay_plan%TYPE;
107 l_wgi_pay_date DATE;
108 l_wgi_due_date DATE; -- Bug 3747024
109 l_value VARCHAR2 (30);
110 l_multiple_error_flag BOOLEAN;
111 l_pa_request_id ghr_pa_requests.pa_request_id%TYPE;
112 l_par_object_version_number ghr_pa_requests.object_version_number%TYPE;
113 l_1_pa_routing_history_id ghr_pa_routing_history.routing_seq_number%TYPE;
114 l_1_prh_object_version_number ghr_pa_requests.object_version_number%TYPE;
115 l_2_pa_routing_history_id ghr_pa_routing_history.routing_seq_number%TYPE;
116 l_2_prh_object_version_number ghr_pa_requests.object_version_number%TYPE;
117 l_routing_group_id ghr_pa_requests.routing_group_id%TYPE;
118 l_validate BOOLEAN;
119 l_u_attachment_modified_flag VARCHAR2 (30);
120 l_u_approved_flag VARCHAR2 (30);
121 l_u_user_name_acted_on ghr_pa_routing_history.user_name%TYPE;
122 l_u_action_taken ghr_pa_routing_history.action_taken%TYPE
123 := 'NOT_ROUTED';
124 l_i_user_name_routed_to ghr_pa_routing_history.user_name%TYPE;
125 l_i_groupbox_id ghr_pa_routing_history.groupbox_id%TYPE;
126 l_i_routing_list_id NUMBER;
127 l_i_routing_seq_number NUMBER;
128 l_u_prh_object_version_number NUMBER;
129 l_i_pa_routing_history_id NUMBER;
130 l_i_prh_object_version_number NUMBER;
131 l_effective_date ghr_pa_requests.effective_date%TYPE;
132 -- For Create SF52 procedure
133 l_noa_family_code ghr_pa_requests.noa_family_code%TYPE; --'SALARY_CHG';
134 l_first_noa_code ghr_pa_requests.first_noa_code%TYPE;
135 l_first_noa_desc ghr_pa_requests.first_noa_desc%TYPE;
136 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
137 l_first_action_la_desc1 ghr_pa_requests.first_action_la_desc1%TYPE;
138 l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
139 l_first_action_la_desc2 ghr_pa_requests.first_action_la_desc2%TYPE;
140 l_proposed_effective_asap_flag ghr_pa_requests.proposed_effective_asap_flag%TYPE
141 := 'N';
142 -- For Update SF52 procedure (ghr_pa_requests_pkg.get_SF52_person_ddf_details)
143 l_citizenship ghr_pa_requests.citizenship%TYPE;
144 l_veterans_preference ghr_pa_requests.veterans_preference%TYPE;
145 l_veterans_pref_for_rif ghr_pa_requests.veterans_pref_for_rif%TYPE;
146 l_veterans_status ghr_pa_requests.veterans_status%TYPE;
147 l_scd_leave VARCHAR2 (150);
148 -- For Update SF52 procedure From side data elements(ghr_pa_requests_pkg.SF52_from_data_elements)
149 l_from_adj_basic_pay ghr_pa_requests.from_adj_basic_pay%TYPE;
150 l_from_agency_code ghr_pa_requests.from_agency_code%TYPE;
151 l_from_agency_desc ghr_pa_requests.from_agency_desc%TYPE;
152 l_from_basic_pay ghr_pa_requests.from_basic_pay%TYPE;
153 l_from_grade_or_level ghr_pa_requests.from_grade_or_level%TYPE;
154 l_from_locality_adj ghr_pa_requests.from_locality_adj%TYPE;
155 l_from_occ_code ghr_pa_requests.from_occ_code%TYPE;
156 l_from_office_symbol ghr_pa_requests.from_office_symbol%TYPE;
157 l_from_other_pay_amount ghr_pa_requests.from_other_pay_amount%TYPE;
158 l_from_pay_basis ghr_pa_requests.from_pay_basis%TYPE;
159 l_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
160 l_from_position_id ghr_pa_requests.from_position_id%TYPE;
161 l_from_position_org_line1 ghr_pa_requests.from_position_org_line1%TYPE;
162 l_from_position_org_line2 ghr_pa_requests.from_position_org_line2%TYPE;
163 l_from_position_org_line3 ghr_pa_requests.from_position_org_line3%TYPE;
164 l_from_position_org_line4 ghr_pa_requests.from_position_org_line4%TYPE;
165 l_from_position_org_line5 ghr_pa_requests.from_position_org_line5%TYPE;
166 l_from_position_org_line6 ghr_pa_requests.from_position_org_line6%TYPE;
167 l_from_position_number ghr_pa_requests.from_position_number%TYPE;
168 l_from_position_seq_no ghr_pa_requests.from_position_seq_no%TYPE;
169 l_from_position_title ghr_pa_requests.from_position_title%TYPE;
170 l_from_step_or_rate ghr_pa_requests.from_step_or_rate%TYPE;
171 l_from_total_salary ghr_pa_requests.from_total_salary%TYPE;
172 l_to_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
173 l_to_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
174 l_to_availability_pay ghr_pa_requests.to_availability_pay%TYPE;
175 l_to_ap_premium_pay_indicator ghr_pa_requests.to_ap_premium_pay_indicator%TYPE;
176 l_to_retention_allowance ghr_pa_requests.to_retention_allowance%TYPE;
177 l_to_supervisory_differential ghr_pa_requests.to_supervisory_differential%TYPE;
178 l_to_staffing_differential ghr_pa_requests.to_staffing_differential%TYPE;
179 l_to_organization_id ghr_pa_requests.to_organization_id%TYPE;
180 l_to_step_or_rate ghr_pa_requests.to_step_or_rate%TYPE;
181 l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
182 l_duty_station_location_id ghr_pa_requests.duty_station_location_id%TYPE;
183 l_education_level ghr_pa_requests.education_level%TYPE;
184 l_academic_discipline ghr_pa_requests.academic_discipline%TYPE;
185 l_year_degree_attained ghr_pa_requests.year_degree_attained%TYPE;
186 --
187 l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%TYPE;
188 l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%TYPE;
189 l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%TYPE;
190 l_award_percentage ghr_pa_requests.award_percentage%TYPE;
191 --
192 -- for get_SF52_asg_ddf_details
193 l_tenure ghr_pa_requests.tenure%TYPE;
194 l_annuitant_indicator ghr_pa_requests.annuitant_indicator%TYPE;
195 l_annuitant_indicator_desc ghr_pa_requests.annuitant_indicator_desc%TYPE;
196 l_flsa_category ghr_pa_requests.flsa_category%TYPE;
197 l_bargaining_unit_status ghr_pa_requests.bargaining_unit_status%TYPE;
198 l_work_schedule ghr_pa_requests.work_schedule%TYPE;
199 l_work_schedule_desc ghr_pa_requests.work_schedule_desc%TYPE;
200 l_functional_class ghr_pa_requests.functional_class%TYPE;
201 l_supervisory_status ghr_pa_requests.supervisory_status%TYPE;
202 l_position_occupied ghr_pa_requests.position_occupied%TYPE;
203 l_appropriation_code1 ghr_pa_requests.appropriation_code1%TYPE;
204 l_appropriation_code2 ghr_pa_requests.appropriation_code2%TYPE;
205 l_part_time_hours ghr_pa_requests.part_time_hours%TYPE;
206 -- for get_duty_station_detail
207 l_duty_station_code ghr_pa_requests.duty_station_code%TYPE;
208 l_duty_station_desc ghr_pa_requests.duty_station_desc%TYPE;
209 l_custom_pay_calc_flag ghr_pa_requests.custom_pay_calc_flag%TYPE;
210 l_fegli ghr_pa_requests.fegli%TYPE;
211 l_fegli_desc ghr_pa_requests.fegli_desc%TYPE;
212 l_retirement_plan ghr_pa_requests.retirement_plan%TYPE;
213 l_retirement_plan_desc ghr_pa_requests.retirement_plan_desc%TYPE;
214 l_service_comp_date ghr_pa_requests.service_comp_date%TYPE;
215 -- for check_assignment_id
216 -- for person_in_pa_requests
217 l_days NUMBER := 350;
218 l_frequency NUMBER;
219 l_message_set BOOLEAN;
220 l_calculated BOOLEAN;
221 l_nature_of_action_id ghr_nature_of_actions.nature_of_action_id%TYPE;
222 l_retained_grade ghr_pay_calc.retained_grade_rec_type;
223 -- used for starting the wgi workflow
224 l_start_wgi_wf_flag CHAR (1) := 'N';
225 l_errbuf VARCHAR2 (2000);
226 l_retcode NUMBER := 0;
227 -- to side pay calc added for bug fix
228 l_to_basic_pay ghr_pa_requests.to_basic_pay%TYPE;
229 l_to_locality_adj ghr_pa_requests.to_locality_adj%TYPE;
230 l_to_adj_basic_pay ghr_pa_requests.to_adj_basic_pay%TYPE;
231 l_to_total_salary ghr_pa_requests.to_total_salary%TYPE;
232 l_to_other_pay_amount ghr_pa_requests.to_other_pay_amount%TYPE;
233 -- Added for WGI Custome user hook
234 l_wgi_in_rec_type ghr_wgi_pkg.wgi_in_rec_type; -- This is the IN record structure for the WGI hook
235 l_wgi_out_rec_type ghr_wgi_pkg.wgi_out_rec_type; -- This is the IN/OUT record structure for the WGI hook
236 --
237 l_open_pay_fields BOOLEAN;
238 l_personnel_office_id ghr_pa_requests.personnel_office_id%TYPE;
239 -- Added for the output parameters these values are not used anywhere
240 l_tmp_work_schedule ghr_pa_requests.work_schedule%TYPE;
241 l_tmp_part_time_hours ghr_pa_requests.part_time_hours%TYPE;
242 l_tmp_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
243 l_tmp_from_grade_or_level ghr_pa_requests.from_grade_or_level%TYPE;
244 l_tmp_from_step_or_rate ghr_pa_requests.from_step_or_rate%TYPE;
245 l_tmp_from_temp_step ghr_pa_requests.from_step_or_rate%TYPE;
246 -- Added pay calc record type in/out
247 l_pay_calc_in_rec_type ghr_pay_calc.pay_calc_in_rec_type;
248 l_pay_calc_out_rec_type ghr_pay_calc.pay_calc_out_rec_type;
249 l_req VARCHAR2 (15);
250 l_in_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
251 l_in_personnel_office_id ghr_pa_requests.personnel_office_id%TYPE;
252 -- Remarks
253 l_pa_remark_id ghr_pa_remarks.pa_remark_id%TYPE;
254 l_pre_object_version_number ghr_pa_remarks.object_version_number%TYPE;
255 l_remark_id1 ghr_pa_remarks.remark_id%TYPE := NULL;
256 l_remark_desc1 ghr_pa_remarks.description%TYPE := NULL;
257 l_remark1_info1 ghr_pa_remarks.remark_code_information1%TYPE
258 := NULL;
259 l_remark1_info2 ghr_pa_remarks.remark_code_information2%TYPE
260 := NULL;
261 l_remark1_info3 ghr_pa_remarks.remark_code_information3%TYPE
262 := NULL;
263 l_remark_id2 ghr_pa_remarks.remark_id%TYPE := NULL;
264 l_remark_desc2 ghr_pa_remarks.description%TYPE := NULL;
265 l_remark2_info1 ghr_pa_remarks.remark_code_information1%TYPE
266 := NULL;
267 l_remark2_info2 ghr_pa_remarks.remark_code_information2%TYPE
268 := NULL;
269 l_remark2_info3 ghr_pa_remarks.remark_code_information3%TYPE
270 := NULL;
271 l_commit NUMBER;
272 ------------ Bug 3680601
273 l_grp_box_id ghr_pois.groupbox_id%type;
274 l_no_groupbox_exception EXCEPTION;
275 l_from_asg_exception EXCEPTION;
276 l_retained_grade_exception EXCEPTION;
277 l_max_step_exception EXCEPTION;
278
279 CURSOR c_check_group_box(c_po_id ghr_pois.personnel_office_id%type) IS
280 SELECT groupbox_id
281 FROM ghr_pois
282 WHERE personnel_office_id = c_po_id;
283 --
284 -- Bug#5482191 Added effective date condition.
285 CURSOR get_sal_chg_fam
286 IS
287 SELECT noa_family_code
288 FROM ghr_families
289 WHERE noa_family_code IN (
290 SELECT noa_family_code
291 FROM ghr_noa_families
292 WHERE nature_of_action_id =
293 (SELECT nature_of_action_id
294 FROM ghr_nature_of_actions
295 WHERE code = l_first_noa_code
296 AND l_effective_date BETWEEN NVL(Date_from,l_effective_date)
297 AND NVL(date_to,l_effective_date)
298 )
299 )
300 AND proc_method_flag = 'Y';
301
302 CURSOR c_fnd_sessions
303 IS
304 SELECT 1
305 FROM fnd_sessions
306 WHERE session_id = USERENV ('sessionid');
307
308 CURSOR cur_wgi_no_payplan (p_grd_id per_grades.grade_id%TYPE)
309 IS
310 SELECT gdf.segment1 pay_plan
311 FROM per_grades grd, per_grade_definitions gdf
312 WHERE grd.grade_id = p_grd_id
313 AND grd.grade_definition_id = gdf.grade_definition_id;
314
315 l_ret_calc_perc NUMBER (15,3);
316 l_new_retention_allowance NUMBER;
317 l_per_pay_plan per_grade_definitions.segment1%TYPE;
318 l_from_pay_table_identifier ghr_pa_requests.from_pay_table_identifier%TYPE;
319 -- Sun 4490539
320 l_check_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
321 l_check_step_or_rate ghr_pa_requests.to_step_or_rate%type;
322 -- End Sun 4490539
323 -- vmididho 6145659
324 cursor val_pa_remarks(p_pa_request_id in number,
325 p_remark_id in number)
326 is
327 select 1
328 from ghr_pa_remarks
329 where pa_request_id = p_pa_request_id
330 and pa_remark_id = p_remark_id;
331
332 m_val_pa_remarks val_pa_remarks%rowtype;
333 -- end 6145659
334 BEGIN
335 -- Set the Frequency - This is used for triggering the WGI process 90 days in advance of the Pay Date
336 l_frequency := p_frequency;
337 -- Set Concurrent Program name and Request ID
338 l_req := fnd_profile.VALUE ('CONC_REQUEST_ID');
339 l_proc := 'AWGI :' || l_req;
340 l_in_pay_plan := p_pay_plan;
341 l_in_personnel_office_id := p_personnel_office_id;
342 l_exists := FALSE;
343
344 -- hr_utility.trace_on(null,'sundar');
345 -- Replacing the insert dml with a call to the dt_fndate.change_ses_date procedure .
346
347 dt_fndate.change_ses_date (p_ses_date => TRUNC (SYSDATE),
348 p_commit => l_commit
349 );
350 -- The previous code did not perform a commit after writing to fnd_sessions
351 -- Hence not issuing a Commit based on the value of the l_commit out variable
352
353 hr_utility.set_location('Before Check if Personnel Office ID is having group box',1234);
354 -- Bug 3649933. Check if Personnel Office ID is having group box
355 IF l_in_personnel_office_id IS NOT NULL THEN
356 FOR l_check_group_box IN c_check_group_box(l_in_personnel_office_id) LOOP
357 l_grp_box_id := l_check_group_box.groupbox_id;
358 END LOOP;
359
360 IF l_grp_box_id IS NULL THEN
361 -- If No group box is assigned, then throw an error
362 l_errbuf := 'Group Box does not exist for this Personnel office id ' || l_in_personnel_office_id;
363 RAISE l_no_groupbox_exception;
364 END IF;
365 END IF; -- If PO ID is not null
366 -- End 3649933
367
368
369 OPEN per_assign_cursor (p_effective_date);
370 LOOP
371 BEGIN
372 FETCH per_assign_cursor INTO l_person_id,
373 l_national_identifier,
374 l_date_of_birth,
375 l_last_name,
376 l_first_name,
377 l_middle_names,
378 l_full_name,
379 l_position_id,
380 l_assignment_id,
381 l_grade_id,
382 l_job_id,
383 l_location_id,
384 l_organization_id;
385 --
386 EXIT WHEN per_assign_cursor%NOTFOUND;
387 -- Initialize the PA Request ID
388 l_pa_request_id := NULL;
389 hr_utility.set_location('After fetch Person ID: ' || l_person_id,1234);
390 --
391 -- Bug 3941877
392 l_in_pay_plan := p_pay_plan;
393 l_in_personnel_office_id := p_personnel_office_id;
394 -- End Bug 3941877
395
396 IF (checkpoiparm (p_in_personnel_office_id => l_in_personnel_office_id,
397 p_position_id => l_position_id,
398 p_effective_date => p_effective_date
399 )
400 )
401 THEN
402
403 hr_utility.set_location('Checking POI',1234);
404
405 FOR wgi_no_payplan_rec IN cur_wgi_no_payplan (l_grade_id)
406 LOOP
407 l_per_pay_plan := wgi_no_payplan_rec.pay_plan;
408 END LOOP;
409
410 IF check_pay_plan (l_per_pay_plan) THEN
411 -- If the assignment ID is not present then do not process this employee
412 --
413 hr_utility.set_location('Checking Pay plan',1234);
414 IF p_log_flag = 'Y'
415 THEN
416 create_ghr_errorlog (p_program_name => l_proc,
417 p_log_text => 'Fetched Full Name : '
418 || l_full_name
419 || ' Person ID : '
420 || TO_CHAR (l_person_id
421 )
422 || ' Assignment ID : '
423 || TO_CHAR (l_assignment_id
424 )
425 || ' Pay Plan Parameter : '
426 || l_in_pay_plan
427 || ' POI Parameter : '
428 || l_in_personnel_office_id,
429 p_message_name => NULL,
430 p_log_date => SYSDATE
431 );
432 END IF;
433
434 IF l_assignment_id IS NOT NULL
435 THEN -- 1st IF of Assignment ID
436 --
437 -- Get the WGI Status
438 -- If Deny, Delay, Postpone, dont process WGI Bug 4475295
439
440 ghr_api.retrieve_element_entry_value (p_element_name => 'Within Grade Increase',
441 p_input_value_name => 'Status',
442 p_assignment_id => l_assignment_id,
443 p_effective_date => p_effective_date,
444 p_value => l_value,
445 p_multiple_error_flag => l_multiple_error_flag
446 );
447
448 IF l_value IN ('1','2','3') THEN
449 NULL; -- Dont proceed from here.
450 -- Get SF52 From side data elements and Get the Pay Plan for the WGI
451 -- End Bug 4475295
452 ELSE
453 BEGIN
454 ghr_api.sf52_from_data_elements (p_person_id => l_person_id,
455 p_assignment_id => l_assignment_id,
456 p_position_title => l_from_position_title,
457 p_position_number => l_from_position_number,
458 p_position_seq_no => l_from_position_seq_no,
459 p_pay_plan => l_from_pay_plan,
460 p_job_id => l_job_id,
461 p_occ_code => l_from_occ_code,
462 p_grade_id => l_grade_id,
463 p_grade_or_level => l_from_grade_or_level,
464 p_step_or_rate => l_from_step_or_rate,
465 p_total_salary => l_from_total_salary,
466 p_pay_basis => l_from_pay_basis,
467 -- FWFA Changes Bug#4444609
468 p_pay_table_identifier => l_from_pay_table_identifier,
469 -- FWFA Changes
470 p_basic_pay => l_from_basic_pay,
471 p_locality_adj => l_from_locality_adj,
472 p_adj_basic_pay => l_from_adj_basic_pay,
473 p_other_pay => l_from_other_pay_amount,
474 p_au_overtime => l_to_au_overtime,
475 p_auo_premium_pay_indicator => l_to_auo_premium_pay_indicator,
476 p_availability_pay => l_to_availability_pay,
477 p_ap_premium_pay_indicator => l_to_ap_premium_pay_indicator,
478 p_retention_allowance => l_to_retention_allowance,
479 p_supervisory_differential => l_to_supervisory_differential,
480 p_staffing_differential => l_to_staffing_differential,
481 p_organization_id => l_to_organization_id,
482 p_position_org_line1 => l_from_position_org_line1,
483 p_position_org_line2 => l_from_position_org_line2,
484 p_position_org_line3 => l_from_position_org_line3,
485 p_position_org_line4 => l_from_position_org_line4,
486 p_position_org_line5 => l_from_position_org_line5,
487 p_position_org_line6 => l_from_position_org_line6,
488 p_position_id => l_from_position_id,
489 p_duty_station_location_id => l_duty_station_location_id,
490 p_pay_rate_determinant => l_pay_rate_determinant,
491 p_work_schedule => l_tmp_work_schedule,
492 p_retention_allow_percentage => l_retention_allow_percentage,
493 p_supervisory_diff_percentage => l_supervisory_diff_percentage,
494 p_staffing_diff_percentage => l_staffing_diff_percentage,
495 p_altered_pa_request_id => NULL,
496 p_noa_id_corrected => NULL,
497 p_pa_history_id => NULL
498 );
499 EXCEPTION
500 WHEN OTHERS THEN
501 raise l_from_asg_exception;
502 END;
503 -- For non-retained employees
504 /*Start Bug: 9255822: rest of the code should not run for PRD Y*/
505 IF l_pay_rate_determinant <> 'Y' THEN
506
507 hr_utility.set_location('After fetching from assignments',1234);
508
509 l_check_pay_plan := l_from_pay_plan;
510 l_check_step_or_rate := l_from_step_or_rate;
511 --- Sundar 3386203
512 -- In case of PRD's A,B,E,F,U,V get pay plan from Retained grade.
513 BEGIN
514 IF l_pay_rate_determinant IN ('A','B','E','F','U','V') THEN
515 l_retained_grade :=
516 ghr_pc_basic_pay.get_retained_grade_details
517 (p_person_id => l_person_id
518 ,p_effective_date => p_effective_date);
519 -- l_from_pay_plan := l_retained_grade.pay_plan;
520
521 -- Start 3431965
522 IF l_retained_grade.temp_step IS NOT NULL THEN
523 l_check_step_or_rate := l_retained_grade.temp_step;
524 ELSE
525 l_check_step_or_rate := l_retained_grade.step_or_rate;
526 l_check_pay_plan := l_retained_grade.pay_plan;
527 END IF;
528 -- End 3431965
529 END IF;
530 EXCEPTION
531 WHEN OTHERS THEN
532 raise l_retained_grade_exception;
533 END;
534 -- End Sundar 3386203
535
536 IF p_log_flag = 'Y'
537 THEN
538 create_ghr_errorlog (p_program_name => l_proc,
539 p_log_text => 'Checking CheckPayPlanParm '
540 || 'for Full Name : '
541 || l_full_name
542 || ' Person ID : '
543 || TO_CHAR (l_person_id
544 )
545 || ' Assignment ID : '
546 || TO_CHAR (l_assignment_id
547 )
548 || ' p_in_pay_plan : '
549 || l_in_pay_plan
550 || ' p_from_pay_plan : '
551 || l_from_pay_plan,
552 p_message_name => NULL,
553 p_log_date => SYSDATE
554 );
555 END IF;
556
557 -- Check to see if WGI is to be run for One Pay Plan only (2.1 CheckPayPlanParm)
558 IF (checkpayplanparm (p_in_pay_plan => l_in_pay_plan,
559 p_from_pay_plan => l_check_pay_plan
560 )
561 )
562 THEN
563 hr_utility.set_location('After checkpayplanparm',1234);
564 hr_utility.set_location ('Pay Plan criteria satisfied ',
565 1000
566 );
567
568 IF p_log_flag = 'Y'
569 THEN
570 create_ghr_errorlog (p_program_name => l_proc,
571 p_log_text => 'Checking CheckPOIParm '
572 || 'for Full Name : '
573 || l_full_name
574 || ' Person ID : '
575 || TO_CHAR (l_person_id
576 )
577 || ' Assignment ID : '
578 || TO_CHAR (l_assignment_id
579 )
580 || ' p_in_personnel_office_id : '
581 || l_in_personnel_office_id
582 || ' p_position_id : '
583 || TO_CHAR (l_from_position_id
584 )
585 || ' p_effective_date : '
586 || fnd_date.date_to_displaydate (p_effective_date
587 ),
588 p_message_name => NULL,
589 p_log_date => SYSDATE
590 );
591 END IF;
592
593 -- Check to see if WGI is to be run for One POI only (2.2 CheckPOIParm)
594 /*IF (checkpoiparm (p_in_personnel_office_id => l_in_personnel_office_id,
595 p_position_id => l_from_position_id,
596 p_effective_date => p_effective_date
597 )
598 )
599 THEN*/
600 -- Set the tmp pay plan for checking purposes
601 hr_utility.set_location ('POI criteria satisfied ' || l_in_personnel_office_id,
602 1000
603 );
604 -- Bug 3649933. Check if Personnel Office ID is having group box
605 IF l_in_personnel_office_id IS NOT NULL THEN
606 FOR l_check_group_box IN c_check_group_box(l_in_personnel_office_id) LOOP
607 l_grp_box_id := l_check_group_box.groupbox_id;
608 END LOOP;
609 hr_utility.set_location ('l_grp_box_id ' || l_grp_box_id,
610 1000);
611 IF l_grp_box_id IS NULL THEN
612 -- If No group box is assigned, then throw an error
613 l_errbuf := 'Group Box does not exist for this Personnel office id ' || l_in_personnel_office_id;
614 hr_utility.set_location ('Error buff set',1000);
615 RAISE l_no_groupbox_exception;
616 END IF;
617 END IF; -- If PO ID is not null
618 -- End Bug 3649933.
619
620 IF p_log_flag = 'Y'
621 THEN
622 create_ghr_errorlog (p_program_name => l_proc,
623 p_log_text => 'Checking CheckIfMaxPayPlan '
624 || 'for Full Name : '
625 || l_full_name
626 || ' Person ID : '
627 || TO_CHAR (l_person_id
628 )
629 || ' Assignment ID : '
630 || TO_CHAR (l_assignment_id
631 )
632 || ' p_from_pay_plan : '
633 || l_from_pay_plan
634 || ' p_from_step_or_rate : '
635 || l_from_step_or_rate,
636 p_message_name => NULL,
637 p_log_date => SYSDATE
638 );
639 END IF;
640
641 --------------- Check WGI Pay Date if its falls in range
642 -- Bug 3747024 - Getting Due date
643 ghr_api.retrieve_element_entry_value (p_element_name => 'Within Grade Increase',
644 p_input_value_name => 'Date Due',
645 p_assignment_id => l_assignment_id,
646 p_effective_date => p_effective_date,
647 p_value => l_value,
648 p_multiple_error_flag => l_multiple_error_flag
649 );
650 IF l_value IS NOT NULL THEN
651 l_wgi_due_date := fnd_date.canonical_to_date (l_value);
652 ELSE
653 l_wgi_due_date := NULL;
654 END IF;
655 -- End Bug 3747024
656
657 ghr_api.retrieve_element_entry_value (p_element_name => 'Within Grade Increase',
658 p_input_value_name => 'Pay Date',
659 p_assignment_id => l_assignment_id,
660 p_effective_date => p_effective_date,
661 p_value => l_value,
662 p_multiple_error_flag => l_multiple_error_flag
663 );
664
665 --
666 IF l_value IS NOT NULL
667 THEN
668 l_wgi_pay_date := fnd_date.canonical_to_date (l_value);
669 ELSE
670 l_wgi_pay_date := NULL;
671 END IF;
672
673 ---
674 IF p_log_flag = 'Y'
675 THEN
676 create_ghr_errorlog (p_program_name => l_proc,
677 p_log_text => 'retrieved WGI Pay Date for Full Name : '
678 || l_full_name
679 || ' Person ID : '
680 || TO_CHAR (l_person_id
681 )
682 || ' Assignment ID : '
683 || TO_CHAR (l_assignment_id
684 )
685 || ' WGI Pay Date : '
686 || fnd_date.date_to_displaydate (l_wgi_pay_date
687 )
688 || ' WGI Due Date : '
689 || fnd_date.date_to_displaydate (l_wgi_due_date
690 ),
691 p_message_name => NULL,
692 p_log_date => SYSDATE
693 );
694 END IF;
695
696 IF l_wgi_pay_date IS NOT NULL THEN -- 2nd IF of WGI Pay Date
697 IF (p_effective_date >=
698 (l_wgi_pay_date - l_frequency
699 )
700 )
701 THEN -- 3rd IF of l_frequency
702 -- Initialize the effective date and then do not change it for this record
703 l_effective_date := l_wgi_pay_date;
704
705 IF p_log_flag = 'Y'
706 THEN
707 create_ghr_errorlog (p_program_name => l_proc,
708 p_log_text => 'Retrieving from data elements (sf52_from_data_elements) '
709 || 'for Full Name : '
710 || l_full_name
711 || ' Person ID : '
712 || TO_CHAR (l_person_id
713 )
714 || ' Assignment ID : '
715 || TO_CHAR (l_assignment_id
716 ),
717 p_message_name => NULL,
718 p_log_date => SYSDATE
719 );
720 END IF;
721
722 --------------End Checking Pay Date
723 -- Check to see if the employee has reached the max step (2.3 CheckIfMaxPayPlan)
724
725 IF (checkifmaxpayplan (p_from_pay_plan => l_check_pay_plan,
726 p_from_step_or_rate => l_check_step_or_rate
727 )
728 )
729 THEN
730 l_tmp_from_pay_plan := l_check_pay_plan;
731
732 IF p_log_flag = 'Y'
733 THEN
734 create_ghr_errorlog (p_program_name => l_proc,
735 p_log_text => 'Checking check_assignment_prd '
736 || 'for Full Name : '
737 || l_full_name
738 || ' Person ID : '
739 || TO_CHAR (l_person_id
740 )
741 || ' Assignment ID : '
742 || TO_CHAR (l_assignment_id
743 )
744 || ' p_pay_rate_determinant : '
745 || l_pay_rate_determinant,
746 p_message_name => NULL,
747 p_log_date => SYSDATE
748 );
749 END IF;
750
751 --
752 -- Check if in PA Request table if there is a record in NOA 893 from sysdate
753 IF (check_assignment_prd (p_pay_rate_determinant => l_pay_rate_determinant
754 )
755 )
756 THEN -- 4th IF of PRD
757 --
758 IF p_log_flag = 'Y'
759 THEN
760 create_ghr_errorlog (p_program_name => l_proc,
761 p_log_text => 'Checking CheckIfFWPayPlan '
762 || 'for Full Name : '
763 || l_full_name
764 || ' Person ID : '
765 || TO_CHAR (l_person_id
766 )
767 || ' Assignment ID : '
768 || TO_CHAR (l_assignment_id
769 )
770 || ' p_from_pay_plan : '
771 || l_from_pay_plan,
772 p_message_name => NULL,
773 p_log_date => SYSDATE
774 );
775 END IF;
776
777 -- Derive l_days Wage Grade employees minimum time is 165 days and other is 350
778 IF (checkiffwpayplan (p_from_pay_plan => l_check_pay_plan
779 )
780 )
781 THEN
782 l_days := 165;
783 ELSE
784 l_days := 350;
785 END IF;
786
787 -- Then Get reatined pay plan in l_from_pay_plan
788 /* RP 10/4 l_retained_grade :=
789 ghr_pc_basic_pay.get_retained_grade_details
790 (p_person_id => l_person_id
791 ,p_effective_date => l_effective_date);
792 l_tmp_from_pay_plan := l_retained_grade.pay_plan;
793 l_tmp_from_grade_or_level := l_retained_grade.grade_or_level;
794 l_tmp_from_step_or_rate := l_retained_grade.step_or_rate;
795 */
796 --RP 04/10
797 -- Derive NOA code
798 IF l_tmp_from_pay_plan IN ('GM','GR') THEN --Bug# 6603968
799 -- Bug#5482191 Process 893 for GM Employees after 07-JAN-2007
800 IF l_effective_date < to_date('07/01/2007','DD/MM/YYYY') THEN
801 l_first_noa_code := '891';
802 ELSE
803 l_first_noa_code := '893';
804 END IF;
805 ELSE
806 l_first_noa_code := '893';
807 END IF;
808 ELSE
809 IF l_check_pay_plan IN ('GM','GR') THEN --Bug# 6603968
810 -- Bug#5482191 Process 893 for GM Employees after 07-JAN-2007
811 IF l_effective_date < to_date('07/01/2007','DD/MM/YYYY') THEN
812 l_first_noa_code := '891';
813 hr_utility.set_location ('WGI noa code 891 ', 1 );
814 ELSE
815 l_first_noa_code := '893';
816 END IF;
817 -- bug# 5725910 code commented as this is not necessary..891 should not be assigned to first noa code irrespective of the effective date.
818 -- l_first_noa_code := '891';
819
820 ELSE
821 l_first_noa_code := '893';
822 END IF;
823 END IF; -- IF (check_assignment_prd
824
825 IF p_log_flag = 'Y'
826 THEN
827 create_ghr_errorlog (p_program_name => l_proc,
828 p_log_text => 'Checking person_in_pa_requests '
829 || 'for Full Name : '
830 || l_full_name
831 || ' Person ID : '
832 || TO_CHAR (l_person_id
833 )
834 || ' Assignment ID : '
835 || TO_CHAR (l_assignment_id
836 )
837 || ' p_first_noa_code : '
838 || l_first_noa_code,
839 p_message_name => NULL,
840 p_log_date => SYSDATE
841 );
842 END IF;
843
844 --
845 -- Check in PA Reqest table if there is a NOA of this person is existing already
846 hr_utility.set_location ('Check in PA Reqest table if there is a NOA of this person ',
847 1000
848 );
849
850 IF (person_in_pa_requests (p_person_id => l_person_id,
851 p_effective_date => l_effective_date,
852 p_first_noa_code => l_first_noa_code,
853 p_days => l_days
854 )
855 )
856 THEN -- 5th IF of PA Request
857 --
858 IF p_log_flag = 'Y'
859 THEN
860 create_ghr_errorlog (p_program_name => l_proc,
861 p_log_text => 'No records found in person_in_pa_requests '
862 || 'for Full Name : '
863 || l_full_name
864 || ' Person ID : '
865 || TO_CHAR (l_person_id
866 )
867 || ' p_effective_date : '
868 || fnd_date.date_to_displaydate (l_effective_date
869 )
870 || ' p_first_noa_code : '
871 || l_first_noa_code,
872 p_message_name => NULL,
873 p_log_date => SYSDATE
874 );
875 END IF;
876
877 IF l_pay_rate_determinant IN
878 ('A', 'B', 'E', 'F')
879 THEN
880 IF p_log_flag = 'Y'
881 THEN
882 create_ghr_errorlog (p_program_name => l_proc,
883 p_log_text => 'Getting Retained Pay Plan as PRD in A/B/E/F '
884 || 'for Full Name : '
885 || l_full_name
886 || ' Person ID : '
887 || TO_CHAR (l_person_id
888 )
889 || ' p_effective_date : '
890 || fnd_date.date_to_displaydate (l_effective_date
891 )
892 || ' p_first_noa_code : '
893 || l_first_noa_code,
894 p_message_name => NULL,
895 p_log_date => SYSDATE
896 );
897 END IF;
898
899 --
900 -- Then Get reatined pay plan in l_from_pay_plan
901 l_retained_grade :=
902 ghr_pc_basic_pay.get_retained_grade_details (p_person_id => l_person_id,
903 p_effective_date => l_effective_date
904 );
905 l_tmp_from_pay_plan :=
906 l_retained_grade.pay_plan;
907 --bug#5171417
908 l_tmp_from_grade_or_level := l_retained_grade.grade_or_level;
909 l_tmp_from_step_or_rate := l_retained_grade.step_or_rate;
910 l_tmp_from_temp_step := l_retained_grade.temp_step;
911
912 END IF;
913
914 IF p_log_flag = 'Y'
915 THEN
916 create_ghr_errorlog (p_program_name => l_proc,
917 p_log_text => 'Checking Pay Plan check_pay_plan : '
918 || 'for Full Name : '
919 || l_full_name
920 || ' Person ID : '
921 || TO_CHAR (l_person_id
922 )
923 || ' p_pay_plan : '
924 || l_tmp_from_pay_plan,
925 p_message_name => NULL,
926 p_log_date => SYSDATE
927 );
928 END IF;
929
930 -- Check if the Pay Plan is a valid pay plan for WGI
931 IF (check_pay_plan (p_pay_plan => l_tmp_from_pay_plan
932 )
933 )
934 THEN -- 6th If for Check Pay Plan
935 hr_utility.set_location ( 'Pay Plan valid l_person_id'
936 || l_person_id,
937 1000
938 );
939
940 --
941 -- Get first_noa_desc description
942 --
943 IF p_log_flag = 'Y'
944 THEN
945 create_ghr_errorlog (p_program_name => l_proc,
946 p_log_text => 'Pay Plan is valid for WGI : '
947 || 'for Full Name : '
948 || l_full_name
949 || ' Person ID : '
950 || TO_CHAR (l_person_id
951 )
952 || ' p_pay_plan : '
953 || l_tmp_from_pay_plan,
954 p_message_name => NULL,
955 p_log_date => SYSDATE
956 );
957 END IF;
958
959 get_noa_code_desc (p_code => l_first_noa_code,
960 p_effective_date => l_effective_date,
961 p_nature_of_action_id => l_nature_of_action_id,
962 p_description => l_first_noa_desc
963 );
964
965 IF p_log_flag = 'Y'
966 THEN
967 create_ghr_errorlog (p_program_name => l_proc,
968 p_log_text => 'NOA code desc get_noa_code_desc for WGI : '
969 || ' for Full Name : '
970 || l_full_name
971 || ' Person ID : '
972 || TO_CHAR (l_person_id
973 )
974 || ' p_description : '
975 || l_first_noa_desc,
976 p_message_name => NULL,
977 p_log_date => SYSDATE
978 );
979 END IF;
980
981 --
982 -- Get Legal Authority Codes and Description (GHR_US_LEGAL_AUTHORITY)
983 -- Bug#5058116 Commented this code and added it just before update_sf52.
984 /* derive_legal_auth_cd_remarks (
985 p_first_noa_code => l_first_noa_code,
986 p_pay_rate_determinant => l_pay_rate_determinant,
987 p_from_pay_plan => l_check_pay_plan,
988 p_grade_or_level => l_from_grade_or_level,
989 p_step_or_rate => l_from_step_or_rate,
990 p_retained_pay_plan => l_tmp_from_pay_plan,
991 p_retained_grade_or_level => l_tmp_from_grade_or_level,
992 p_retained_step_or_rate => l_tmp_from_step_or_rate,
993 p_temp_step => l_tmp_from_temp_step,
994 p_effective_date => l_effective_date,
995 p_first_action_la_code1 => l_first_action_la_code1,
996 p_first_action_la_desc1 => l_first_action_la_desc1,
997 p_first_action_la_code2 => l_first_action_la_code2,
998 p_first_action_la_desc2 => l_first_action_la_desc2,
999 p_remark_id1 => l_remark_id1,
1000 p_remark_desc1 => l_remark_desc1,
1001 p_remark1_info1 => l_remark1_info1,
1002 p_remark1_info2 => l_remark1_info2,
1003 p_remark1_info3 => l_remark1_info3,
1004 p_remark_id2 => l_remark_id2,
1005 p_remark_desc2 => l_remark_desc2,
1006 p_remark2_info1 => l_remark2_info1,
1007 p_remark2_info2 => l_remark2_info2,
1008 p_remark2_info3 => l_remark2_info3
1009 );
1010
1011 --
1012 -- Calling create_sf52 with miminum parameters.
1013 --
1014 IF p_log_flag = 'Y'
1015 THEN
1016 create_ghr_errorlog (p_program_name => l_proc,
1017 p_log_text => 'NOA code desc derive_legal_auth_cd for WGI : '
1018 || ' for Full Name : '
1019 || l_full_name
1020 || ' Person ID : '
1021 || TO_CHAR (l_person_id
1022 )
1023 || ' p_first_action_la_code1 : '
1024 || l_first_action_la_code1
1025 || ' p_first_action_la_code2 : '
1026 || l_first_action_la_code2,
1027 p_message_name => NULL,
1028 p_log_date => SYSDATE
1029 );
1030 END IF;*/
1031
1032 -- Set values for the WGI Custom Hook for additional validations for
1033 -- person being selected for auto WGI
1034 l_wgi_in_rec_type.person_id :=
1035 l_person_id;
1036 l_wgi_in_rec_type.assignment_id :=
1037 l_assignment_id;
1038 l_wgi_in_rec_type.position_id :=
1039 l_from_position_id;
1040 l_wgi_in_rec_type.effective_date :=
1041 l_effective_date;
1042 --
1043 -- Out record type initialzed to TRUE which means the
1044 -- person will be processed for WGI
1045 -- Call to the Custom WGI hook can override it to
1046 -- be FALSE.
1047 l_wgi_out_rec_type.process_person :=
1048 TRUE;
1049 -- Call WGI Custom Hook for additional validations for
1050 -- person being selected for auto WGI
1051 --
1052 ghr_custom_wgi_validation.custom_wgi_criteria (l_wgi_in_rec_type,
1053 l_wgi_out_rec_type
1054 );
1055
1056 IF l_wgi_out_rec_type.process_person
1057 THEN
1058 IF p_log_flag = 'Y'
1059 THEN
1060 create_ghr_errorlog (p_program_name => l_proc,
1061 p_log_text => 'Custom WGI Hook (custom_wgi_criteria) '
1062 || ' return value is TRUE for : '
1063 || ' for Full Name : '
1064 || l_full_name
1065 || ' Person ID : '
1066 || TO_CHAR (l_person_id
1067 ),
1068 p_message_name => NULL,
1069 p_log_date => SYSDATE
1070 );
1071 END IF;
1072 ELSE
1073 IF p_log_flag = 'Y'
1074 THEN
1075 create_ghr_errorlog (p_program_name => l_proc,
1076 p_log_text => 'Custom WGI Hook (custom_wgi_criteria) '
1077 || ' return value is FALSE for : '
1078 || ' for Full Name : '
1079 || l_full_name
1080 || ' Person ID : '
1081 || TO_CHAR (l_person_id
1082 ),
1083 p_message_name => NULL,
1084 p_log_date => SYSDATE
1085 );
1086 END IF;
1087 END IF;
1088
1089 -- If Custom hook returns true process the WGI
1090 IF l_wgi_out_rec_type.process_person
1091 THEN -- 7th If for Custom Hook
1092 -- Set PA Request ID to null so that the shadow record is not created
1093 l_pa_request_id := NULL;
1094
1095 IF p_log_flag = 'Y'
1096 THEN
1097 create_ghr_errorlog (p_program_name => l_proc,
1098 p_log_text => 'Creating AWGI PA request for : '
1099 || l_full_name
1100 || ' Person ID : '
1101 || TO_CHAR (l_person_id
1102 ),
1103 p_message_name => NULL,
1104 p_log_date => SYSDATE
1105 );
1106 END IF;
1107
1108 OPEN get_sal_chg_fam;
1109 FETCH get_sal_chg_fam INTO l_noa_family_code;
1110 CLOSE get_sal_chg_fam;
1111 --
1112 -- Create the WGI SF52
1113 --
1114 ghr_sf52_api.create_sf52 (
1115 p_noa_family_code => l_noa_family_code,
1116 p_person_id => l_person_id,
1117 p_effective_date => l_effective_date,
1118 p_first_noa_code => l_first_noa_code,
1119 p_first_noa_desc => l_first_noa_desc,
1120 p_first_action_la_code1 => l_first_action_la_code1,
1121 p_first_action_la_desc1 => l_first_action_la_desc1,
1122 p_first_action_la_code2 => l_first_action_la_code2,
1123 p_first_action_la_desc2 => l_first_action_la_desc2,
1124 p_proposed_effective_asap_flag => l_proposed_effective_asap_flag,
1125 p_from_adj_basic_pay => l_from_adj_basic_pay,
1126 p_from_basic_pay => l_from_basic_pay,
1127 p_from_grade_or_level => l_from_grade_or_level,
1128 p_from_locality_adj => l_from_locality_adj,
1129 p_from_occ_code => l_from_occ_code,
1130 p_from_other_pay_amount => l_from_other_pay_amount,
1131 p_from_pay_basis => l_from_pay_basis,
1132 p_from_pay_plan => l_from_pay_plan,
1133 p_employee_assignment_id => l_assignment_id,
1134 p_employee_date_of_birth => l_date_of_birth,
1135 p_employee_first_name => l_first_name,
1136 p_employee_last_name => l_last_name,
1137 p_employee_middle_names => l_middle_names,
1138 p_employee_national_identifier => l_national_identifier,
1139 p_from_position_id => l_from_position_id,
1140 p_from_position_org_line1 => l_from_position_org_line1,
1141 p_from_position_org_line2 => l_from_position_org_line2,
1142 p_from_position_org_line3 => l_from_position_org_line3,
1143 p_from_position_org_line4 => l_from_position_org_line4,
1144 p_from_position_org_line5 => l_from_position_org_line5,
1145 p_from_position_org_line6 => l_from_position_org_line6,
1146 p_from_position_number => l_from_position_number,
1147 p_from_position_seq_no => l_from_position_seq_no,
1148 p_from_position_title => l_from_position_title,
1149 p_from_step_or_rate => l_from_step_or_rate,
1150 p_from_total_salary => l_from_total_salary,
1151 p_pay_rate_determinant => l_pay_rate_determinant,
1152 p_first_noa_id => l_nature_of_action_id,
1153 p_1_user_name_acted_on => NULL,
1154 p_pa_request_id => l_pa_request_id,
1155 p_par_object_version_number => l_par_object_version_number,
1156 p_1_pa_routing_history_id => l_1_pa_routing_history_id,
1157 p_1_prh_object_version_number => l_1_prh_object_version_number,
1158 p_1_action_taken => l_u_action_taken,
1159 p_2_pa_routing_history_id => l_2_pa_routing_history_id,
1160 p_2_prh_object_version_number => l_2_prh_object_version_number,
1161 -- FWFA Changes Bug#4444609
1162 p_from_pay_table_identifier => l_from_pay_table_identifier
1163 -- FWFA Changes
1164 );
1165 COMMIT;
1166 --
1167 -- set the start wgi flag
1168 --
1169 hr_utility.set_location ( 'Create SF52 l_person_id'
1170 || l_person_id,
1171 1000
1172 );
1173 l_start_wgi_wf_flag := 'Y';
1174
1175 IF p_log_flag = 'Y'
1176 THEN
1177 create_ghr_errorlog (p_program_name => l_proc,
1178 p_log_text => 'Created AWGI SF52 / PA Request ID : '
1179 || TO_CHAR (l_pa_request_id
1180 )
1181 || ' for : '
1182 || l_full_name
1183 || ' Person ID : '
1184 || TO_CHAR (l_person_id
1185 ),
1186 p_message_name => NULL,
1187 p_log_date => SYSDATE
1188 );
1189 END IF;
1190
1191 --
1192 -- Get SF52 Person DDF details
1193 --
1194 ghr_pa_requests_pkg.get_sf52_person_ddf_details (
1195 p_person_id => l_person_id,
1196 p_date_effective => l_effective_date,
1197 p_citizenship => l_citizenship,
1198 p_veterans_preference => l_veterans_preference,
1199 p_veterans_pref_for_rif => l_veterans_pref_for_rif,
1200 p_veterans_status => l_veterans_status,
1201 p_scd_leave => l_scd_leave
1202 );
1203 --
1204 -- populate service comp date
1205 --
1206 l_service_comp_date :=
1207 fnd_date.canonical_to_date (l_scd_leave
1208 );
1209
1210 IF p_log_flag = 'Y'
1211 THEN
1212 create_ghr_errorlog (p_program_name => l_proc,
1213 p_log_text => 'After get_SF52_person_ddf_details for : '
1214 || l_full_name
1215 || ' Person ID : '
1216 || TO_CHAR (l_person_id
1217 )
1218 || ' service_comp_date : '
1219 || fnd_date.date_to_displaydate (l_service_comp_date
1220 ),
1221 p_message_name => NULL,
1222 p_log_date => SYSDATE
1223 );
1224 END IF;
1225
1226 --
1227 -- Get education details
1228 --
1229 ghr_api.return_education_details (
1230 p_person_id => l_person_id,
1231 p_effective_date => l_effective_date,
1232 p_education_level => l_education_level,
1233 p_academic_discipline => l_academic_discipline,
1234 p_year_degree_attained => l_year_degree_attained
1235 );
1236
1237 IF p_log_flag = 'Y'
1238 THEN
1239 create_ghr_errorlog (p_program_name => l_proc,
1240 p_log_text => 'After return_education_details for : '
1241 || l_full_name
1242 || ' Person ID : '
1243 || TO_CHAR (l_person_id
1244 ),
1245 p_message_name => NULL,
1246 p_log_date => SYSDATE
1247 );
1248 END IF;
1249
1250 --
1251 -- Get assignment details
1252 --
1253 ghr_pa_requests_pkg.get_sf52_asg_ddf_details (
1254 p_assignment_id => l_assignment_id,
1255 p_date_effective => l_effective_date,
1256 p_tenure => l_tenure,
1257 p_annuitant_indicator => l_annuitant_indicator,
1258 p_pay_rate_determinant => l_pay_rate_determinant,
1259 p_work_schedule => l_work_schedule,
1260 p_part_time_hours => l_part_time_hours
1261 );
1262 --
1263 -- get fegli desc
1264 --
1265 l_annuitant_indicator_desc :=
1266 ghr_pa_requests_pkg.get_lookup_meaning (800,
1267 'GHR_US_ANNUITANT INDICATOR',
1268 l_annuitant_indicator
1269 );
1270 --
1271 -- populate work schedule desc
1272 --
1273 l_work_schedule_desc :=
1274 ghr_pa_requests_pkg.get_lookup_meaning (800,
1275 'GHR_US_WORK_SCHEDULE',
1276 l_work_schedule
1277 );
1278 --
1279 -- Get position details
1280 --
1281 ghr_pa_requests_pkg.get_sf52_pos_ddf_details (
1282 p_position_id => l_from_position_id,
1283 p_date_effective => l_effective_date,
1284 p_flsa_category => l_flsa_category,
1285 p_bargaining_unit_status => l_bargaining_unit_status,
1286 p_work_schedule => l_work_schedule,
1287 p_functional_class => l_functional_class,
1288 p_supervisory_status => l_supervisory_status,
1289 p_position_occupied => l_position_occupied,
1290 p_appropriation_code1 => l_appropriation_code1,
1291 p_appropriation_code2 => l_appropriation_code2,
1292 p_personnel_office_id => l_personnel_office_id,
1293 p_office_symbol => l_from_office_symbol,
1294 p_part_time_hours => l_tmp_part_time_hours
1295 );
1296
1297 IF p_log_flag = 'Y'
1298 THEN
1299 create_ghr_errorlog (p_program_name => l_proc,
1300 p_log_text => 'After get_SF52_pos_ddf_details for : '
1301 || l_full_name
1302 || ' Person ID : '
1303 || TO_CHAR (l_person_id
1304 ),
1305 p_message_name => NULL,
1306 p_log_date => SYSDATE
1307 );
1308 END IF;
1309
1310 --
1311 -- Get location details
1312 --
1313 ghr_pa_requests_pkg.get_sf52_loc_ddf_details (
1314 p_location_id => l_location_id,
1315 p_duty_station_id => l_duty_station_id
1316 );
1317
1318 --
1319 IF p_log_flag = 'Y'
1320 THEN
1321 create_ghr_errorlog (p_program_name => l_proc,
1322 p_log_text => 'After get_SF52_loc_ddf_details for : '
1323 || l_full_name
1324 || ' Person ID : '
1325 || TO_CHAR (l_person_id
1326 ),
1327 p_message_name => NULL,
1328 p_log_date => SYSDATE
1329 );
1330 END IF;
1331
1332 --
1333 -- Get duty station details
1334 --
1335 ghr_pa_requests_pkg.get_duty_station_details (
1336 p_duty_station_id => l_duty_station_id,
1337 p_effective_date => l_effective_date,
1338 p_duty_station_code => l_duty_station_code,
1339 p_duty_station_desc => l_duty_station_desc
1340 );
1341
1342 -- fetch FEGLI code and description
1343 IF p_log_flag = 'Y'
1344 THEN
1345 create_ghr_errorlog (p_program_name => l_proc,
1346 p_log_text => 'After get_duty_station_details for : '
1347 || l_full_name
1348 || ' Person ID : '
1349 || TO_CHAR (l_person_id
1350 ),
1351 p_message_name => NULL,
1352 p_log_date => SYSDATE
1353 );
1354 END IF;
1355
1356 ghr_api.retrieve_element_entry_value (
1357 p_element_name => 'FEGLI',
1358 p_input_value_name => 'FEGLI',
1359 p_assignment_id => l_assignment_id,
1360 p_effective_date => l_effective_date,
1361 p_value => l_fegli,
1362 p_multiple_error_flag => l_multiple_error_flag
1363 );
1364 l_fegli_desc :=
1365 ghr_pa_requests_pkg.get_lookup_meaning (800,
1366 'GHR_US_FEGLI',
1367 l_fegli
1368 );
1369
1370 IF p_log_flag = 'Y'
1371 THEN
1372 create_ghr_errorlog (p_program_name => l_proc,
1373 p_log_text => 'After retrieve_element_entry_value for : '
1374 || l_full_name
1375 || ' Person ID : '
1376 || TO_CHAR (l_person_id
1377 ),
1378 p_message_name => NULL,
1379 p_log_date => SYSDATE
1380 );
1381 END IF;
1382
1383 -- Set the IN record structure values
1384 l_pay_calc_in_rec_type.person_id :=
1385 l_person_id;
1386 l_pay_calc_in_rec_type.position_id :=
1387 l_from_position_id;
1388 l_pay_calc_in_rec_type.noa_family_code :=
1389 l_noa_family_code;
1390 l_pay_calc_in_rec_type.noa_code :=
1391 l_first_noa_code;
1392 l_pay_calc_in_rec_type.second_noa_code :=
1393 NULL;
1394 l_pay_calc_in_rec_type.effective_date :=
1395 l_effective_date;
1396 l_pay_calc_in_rec_type.pay_rate_determinant :=
1397 l_pay_rate_determinant;
1398 l_pay_calc_in_rec_type.pay_plan :=
1399 l_from_pay_plan;
1400 l_pay_calc_in_rec_type.grade_or_level :=
1401 l_from_grade_or_level;
1402 l_pay_calc_in_rec_type.step_or_rate :=
1403 l_from_step_or_rate;
1404 l_pay_calc_in_rec_type.pay_basis :=
1405 l_from_pay_basis;
1406 l_pay_calc_in_rec_type.user_table_id :=
1407 NULL;
1408 l_pay_calc_in_rec_type.duty_station_id :=
1409 l_duty_station_id;
1410 l_pay_calc_in_rec_type.auo_premium_pay_indicator :=
1411 l_to_auo_premium_pay_indicator;
1412 l_pay_calc_in_rec_type.ap_premium_pay_indicator :=
1413 l_to_ap_premium_pay_indicator;
1414 l_pay_calc_in_rec_type.retention_allowance :=
1415 l_to_retention_allowance;
1416 l_pay_calc_in_rec_type.to_ret_allow_percentage :=
1417 l_retention_allow_percentage;
1418 l_pay_calc_in_rec_type.supervisory_differential :=
1419 NULL;
1420 l_pay_calc_in_rec_type.staffing_differential :=
1421 l_to_staffing_differential;
1422 l_pay_calc_in_rec_type.current_basic_pay :=
1423 l_from_basic_pay;
1424 l_pay_calc_in_rec_type.current_adj_basic_pay :=
1425 l_from_adj_basic_pay;
1426 l_pay_calc_in_rec_type.current_step_or_rate :=
1427 l_from_step_or_rate;
1428 l_pay_calc_in_rec_type.pa_request_id :=
1429 NULL;
1430 --Bug# 6340691
1431 l_pay_calc_in_rec_type.open_out_locality_adj := l_from_locality_adj;
1432 -- Call the pay calc
1433 ghr_pay_calc.sql_main_pay_calc (p_pay_calc_data => l_pay_calc_in_rec_type,
1434 p_pay_calc_out_data => l_pay_calc_out_rec_type,
1435 p_message_set => l_message_set,
1436 p_calculated => l_calculated
1437 );
1438 -- Set the out records
1439 l_to_basic_pay :=
1440 l_pay_calc_out_rec_type.basic_pay;
1441 l_to_locality_adj :=
1442 l_pay_calc_out_rec_type.locality_adj;
1443 l_to_adj_basic_pay :=
1444 l_pay_calc_out_rec_type.adj_basic_pay;
1445 l_to_total_salary :=
1446 l_pay_calc_out_rec_type.total_salary;
1447 l_to_other_pay_amount :=
1448 l_pay_calc_out_rec_type.other_pay_amount;
1449 l_to_au_overtime :=
1450 l_pay_calc_out_rec_type.au_overtime;
1451 l_to_availability_pay :=
1452 l_pay_calc_out_rec_type.availability_pay;
1453 l_to_step_or_rate :=
1454 l_pay_calc_out_rec_type.out_step_or_rate;
1455 l_to_retention_allowance :=
1456 l_pay_calc_out_rec_type.retention_allowance;
1457 hr_utility.set_location ( 'retention_allowance = '
1458 || TO_CHAR (l_to_retention_allowance
1459 ),
1460 10
1461 );
1462 hr_utility.set_location ( 'Supervisory Diff Amount = '
1463 || TO_CHAR (l_to_supervisory_differential
1464 ),
1465 10
1466 );
1467
1468 /****************
1469 ------Other Pay new requirement
1470 l_new_retention_allowance := NULL;
1471 l_ret_calc_perc := 0;
1472
1473 hr_utility.set_location('retention_allowance = ' || to_char(l_to_retention_allowance),10);
1474 hr_utility.set_location('Supervisory Diff Amount = ' || to_char(l_to_supervisory_differential),10);
1475
1476 if l_to_retention_allowance is not null then
1477 if l_from_basic_pay <> l_to_basic_pay then
1478 if l_retention_allow_percentage is null then
1479 l_ret_calc_perc := (l_to_retention_allowance / l_from_basic_pay) * 100;
1480 l_new_retention_allowance :=
1481 ROUND(ghr_pay_calc.convert_amount(l_to_basic_pay,l_from_pay_basis,'PA')
1482 * l_ret_calc_perc / 100 ,0);
1483 else
1484 l_new_retention_allowance :=
1485 ROUND(ghr_pay_calc.convert_amount(l_to_basic_pay,l_from_pay_basis,'PA')
1486 * l_retention_allow_percentage / 100 ,0);
1487 end if;
1488 else
1489 l_new_retention_allowance := l_to_retention_allowance;
1490 end if;
1491 end if;
1492
1493 l_to_retention_allowance := l_new_retention_allowance;
1494
1495 hr_utility.set_location('retention_allowance = ' || to_char(l_to_retention_allowance),12);
1496 hr_utility.set_location('Supervisory Diff Amount = ' || to_char(l_to_supervisory_differential),12);
1497
1498 l_to_other_pay_amount := nvl(l_to_au_overtime,0)
1499 + nvl(l_to_availability_pay,0)
1500 + nvl(l_to_retention_allowance,0)
1501 + nvl(l_to_supervisory_differential,0)
1502 + nvl(l_to_staffing_differential,0);
1503 if l_to_other_pay_amount = 0 then
1504 l_to_other_pay_amount := null;
1505 end if;
1506 l_pay_calc_out_rec_type.other_pay_amount := l_to_other_pay_amount;
1507
1508 l_to_total_salary := NVL(l_to_adj_basic_pay,0)
1509 + ghr_pay_calc.convert_amount(NVL(l_to_other_pay_amount,0),
1510 'PA',
1511 l_from_pay_basis);
1512
1513 l_pay_calc_out_rec_type.total_salary := l_to_total_salary;
1514
1515 ------Other Pay new requirement end
1516 ***********/
1517 IF l_calculated
1518 THEN
1519 l_custom_pay_calc_flag := 'N';
1520 ELSE
1521 l_custom_pay_calc_flag := 'Y';
1522 END IF;
1523
1524 IF p_log_flag = 'Y'
1525 THEN
1526 create_ghr_errorlog (p_program_name => l_proc,
1527 p_log_text => 'After sql_main_pay_calc for : '
1528 || l_full_name
1529 || ' Person ID : '
1530 || TO_CHAR (l_person_id
1531 ),
1532 p_message_name => NULL,
1533 p_log_date => SYSDATE
1534 );
1535 END IF;
1536
1537 -- Populate all the parameters for update_sf52
1538 l_validate := FALSE;
1539 --
1540 --
1541 -- fetch retirement plan and desc
1542 --
1543 ghr_api.retrieve_element_entry_value (
1544 p_element_name => 'Retirement Plan',
1545 p_input_value_name => 'Plan',
1546 p_assignment_id => l_assignment_id,
1547 p_effective_date => l_effective_date,
1548 p_value => l_retirement_plan,
1549 p_multiple_error_flag => l_multiple_error_flag
1550 );
1551
1552 IF p_log_flag = 'Y'
1553 THEN
1554 create_ghr_errorlog (p_program_name => l_proc,
1555 p_log_text => 'After retrieve_element_entry_value for : '
1556 || l_full_name
1557 || ' Person ID : '
1558 || TO_CHAR (l_person_id
1559 )
1560 || ' l_retirement_plan : '
1561 || l_retirement_plan,
1562 p_message_name => NULL,
1563 p_log_date => SYSDATE
1564 );
1565 END IF;
1566
1567 IF l_retirement_plan IS NOT NULL
1568 THEN
1569 l_retirement_plan_desc :=
1570 ghr_pa_requests_pkg.get_lookup_meaning (800,
1571 'GHR_US_RETIREMENT_PLAN',
1572 l_retirement_plan
1573 );
1574 END IF;
1575
1576 --
1577 OPEN get_sal_chg_fam;
1578 FETCH get_sal_chg_fam INTO l_noa_family_code;
1579 CLOSE get_sal_chg_fam;
1580
1581 -- Bug#5058116 Added derive_legal_auth_cd_remarks call before update_sf52 call.
1582 -- Get Legal Authority Codes and Description (GHR_US_LEGAL_AUTHORITY)
1583 --
1584 derive_legal_auth_cd_remarks (
1585 p_first_noa_code => l_first_noa_code,
1586 p_pay_rate_determinant => NVL(l_pay_calc_out_rec_type.out_pay_rate_determinant,
1587 l_pay_rate_determinant),
1588 p_from_pay_plan => l_check_pay_plan,
1589 p_grade_or_level => l_from_grade_or_level,
1590 p_step_or_rate => l_from_step_or_rate,
1591 p_retained_pay_plan => l_tmp_from_pay_plan,
1592 p_retained_grade_or_level => l_tmp_from_grade_or_level,
1593 p_retained_step_or_rate => l_tmp_from_step_or_rate,
1594 p_temp_step => l_tmp_from_temp_step,
1595 p_effective_date => l_effective_date,
1596 p_first_action_la_code1 => l_first_action_la_code1,
1597 p_first_action_la_desc1 => l_first_action_la_desc1,
1598 p_first_action_la_code2 => l_first_action_la_code2,
1599 p_first_action_la_desc2 => l_first_action_la_desc2,
1600 p_remark_id1 => l_remark_id1,
1601 p_remark_desc1 => l_remark_desc1,
1602 p_remark1_info1 => l_remark1_info1,
1603 p_remark1_info2 => l_remark1_info2,
1604 p_remark1_info3 => l_remark1_info3,
1605 p_remark_id2 => l_remark_id2,
1606 p_remark_desc2 => l_remark_desc2,
1607 p_remark2_info1 => l_remark2_info1,
1608 p_remark2_info2 => l_remark2_info2,
1609 p_remark2_info3 => l_remark2_info3
1610 );
1611
1612 --
1613 -- Calling create_sf52 with miminum parameters.
1614 --
1615 IF p_log_flag = 'Y' THEN
1616 create_ghr_errorlog (p_program_name => l_proc,
1617 p_log_text => 'NOA code desc derive_legal_auth_cd for WGI : '
1618 || ' for Full Name : '
1619 || l_full_name
1620 || ' Person ID : '
1621 || TO_CHAR (l_person_id
1622 )
1623 || ' p_first_action_la_code1 : '
1624 || l_first_action_la_code1
1625 || ' p_first_action_la_code2 : '
1626 || l_first_action_la_code2,
1627 p_message_name => NULL,
1628 p_log_date => SYSDATE
1629 );
1630 END IF;
1631
1632 ghr_sf52_api.update_sf52 (
1633 p_validate => l_validate,
1634 p_pa_request_id => l_pa_request_id,
1635 p_noa_family_code => l_noa_family_code,
1636 p_routing_group_id => l_routing_group_id,
1637 p_par_object_version_number => l_par_object_version_number,
1638 p_proposed_effective_asap_flag => l_proposed_effective_asap_flag,
1639 p_academic_discipline => l_academic_discipline,
1640 p_additional_info_person_id => NULL,
1641 p_additional_info_tel_number => NULL,
1642 p_altered_pa_request_id => NULL,
1643 p_annuitant_indicator => l_annuitant_indicator,
1644 p_annuitant_indicator_desc => NULL,
1645 p_appropriation_code1 => l_appropriation_code1,
1646 p_appropriation_code2 => l_appropriation_code2,
1647 p_authorized_by_person_id => NULL,
1648 p_authorized_by_title => NULL,
1649 p_award_amount => NULL,
1650 p_award_uom => NULL,
1651 p_bargaining_unit_status => l_bargaining_unit_status,
1652 p_citizenship => l_citizenship,
1653 p_concurrence_date => NULL,
1654 p_custom_pay_calc_flag => l_custom_pay_calc_flag,
1655 p_duty_station_code => l_duty_station_code,
1656 p_duty_station_desc => l_duty_station_desc,
1657 p_duty_station_id => l_duty_station_id,
1658 p_duty_station_location_id => l_duty_station_location_id,
1659 p_education_level => l_education_level,
1660 p_effective_date => l_effective_date,
1661 p_employee_assignment_id => l_assignment_id,
1662 p_employee_date_of_birth => l_date_of_birth,
1663 p_employee_first_name => l_first_name,
1664 p_employee_last_name => l_last_name,
1665 p_employee_middle_names => l_middle_names,
1666 p_employee_national_identifier => l_national_identifier,
1667 p_fegli => l_fegli,
1668 p_fegli_desc => l_fegli_desc,
1669 p_first_action_la_code1 => l_first_action_la_code1,
1670 p_first_action_la_code2 => l_first_action_la_code2,
1671 p_first_action_la_desc1 => l_first_action_la_desc1,
1672 p_first_action_la_desc2 => l_first_action_la_desc2,
1673 p_first_noa_cancel_or_correct => NULL,
1674 p_first_noa_code => l_first_noa_code,
1675 p_first_noa_desc => l_first_noa_desc,
1676 p_first_noa_id => l_nature_of_action_id,
1677 p_first_noa_pa_request_id => NULL,
1678 p_flsa_category => l_flsa_category,
1679 p_forwarding_address_line1 => NULL,
1680 p_forwarding_address_line2 => NULL,
1681 p_forwarding_address_line3 => NULL,
1682 p_forwarding_country => NULL,
1683 p_forwarding_postal_code => NULL,
1684 p_forwarding_region_2 => NULL,
1685 p_forwarding_town_or_city => NULL,
1686 p_from_adj_basic_pay => l_from_adj_basic_pay,
1687 p_from_basic_pay => l_from_basic_pay,
1688 p_from_grade_or_level => l_from_grade_or_level,
1689 p_from_locality_adj => l_from_locality_adj,
1690 p_from_occ_code => l_from_occ_code,
1691 p_from_other_pay_amount => l_from_other_pay_amount,
1692 p_from_pay_basis => l_from_pay_basis,
1693 p_from_pay_plan => l_from_pay_plan,
1694 p_from_position_id => l_from_position_id,
1695 p_from_position_org_line1 => l_from_position_org_line1,
1696 p_from_position_org_line2 => l_from_position_org_line2,
1697 p_from_position_org_line3 => l_from_position_org_line3,
1698 p_from_position_org_line4 => l_from_position_org_line4,
1699 p_from_position_org_line5 => l_from_position_org_line5,
1700 p_from_position_org_line6 => l_from_position_org_line6,
1701 p_from_position_number => l_from_position_number,
1702 p_from_position_seq_no => l_from_position_seq_no,
1703 p_from_position_title => l_from_position_title,
1704 p_from_step_or_rate => l_from_step_or_rate,
1705 p_from_total_salary => l_from_total_salary,
1706 p_functional_class => l_functional_class,
1707 p_notepad => NULL,
1708 p_part_time_hours => l_part_time_hours,
1709 -- FWFA Changes Bug#4444609 Added NVL Condition
1710 p_pay_rate_determinant => NVL(l_pay_calc_out_rec_type.out_pay_rate_determinant,
1711 l_pay_rate_determinant),
1712 -- FWFA Changes
1713 p_person_id => l_person_id,
1714 p_position_occupied => l_position_occupied,
1715 p_proposed_effective_date => NULL,
1716 p_requested_by_person_id => NULL,
1717 p_requested_by_title => NULL,
1718 p_requested_date => NULL,
1719 p_requesting_office_remarks_de => NULL,
1720 p_requesting_office_remarks_fl => NULL,
1721 p_request_number => 'WGI:'
1722 || l_pa_request_id,
1723 p_resign_and_retire_reason_des => NULL,
1724 p_retirement_plan => l_retirement_plan,
1725 p_retirement_plan_desc => l_retirement_plan_desc,
1726 p_second_action_la_code1 => NULL,
1727 p_second_action_la_code2 => NULL,
1728 p_second_action_la_desc1 => NULL,
1729 p_second_action_la_desc2 => NULL,
1730 p_second_noa_cancel_or_correct => NULL,
1731 p_second_noa_code => NULL,
1732 p_second_noa_desc => NULL,
1733 p_second_noa_id => NULL,
1734 p_second_noa_pa_request_id => NULL,
1735 p_service_comp_date => l_service_comp_date,
1736 p_supervisory_status => l_supervisory_status,
1737 p_tenure => l_tenure,
1738 p_to_adj_basic_pay => l_to_adj_basic_pay,
1739 p_to_basic_pay => l_to_basic_pay,
1740 p_to_grade_id => l_grade_id,
1741 p_to_grade_or_level => l_from_grade_or_level,
1742 p_to_job_id => l_job_id,
1743 p_to_locality_adj => l_to_locality_adj,
1744 p_to_occ_code => l_from_occ_code,
1745 p_to_organization_id => l_to_organization_id,
1746 p_to_other_pay_amount => l_to_other_pay_amount,
1747 p_to_au_overtime => l_to_au_overtime,
1748 p_to_auo_premium_pay_indicator => l_to_auo_premium_pay_indicator,
1749 p_to_availability_pay => l_to_availability_pay,
1750 p_to_ap_premium_pay_indicator => l_to_ap_premium_pay_indicator,
1751 p_to_retention_allowance => l_to_retention_allowance,
1752 p_to_supervisory_differential => l_to_supervisory_differential,
1753 p_to_staffing_differential => l_to_staffing_differential,
1754 p_to_pay_basis => l_from_pay_basis,
1755 p_to_pay_plan => l_from_pay_plan,
1756 p_to_position_id => l_from_position_id,
1757 p_to_position_org_line1 => l_from_position_org_line1,
1758 p_to_position_org_line2 => l_from_position_org_line2,
1759 p_to_position_org_line3 => l_from_position_org_line3,
1760 p_to_position_org_line4 => l_from_position_org_line4,
1761 p_to_position_org_line5 => l_from_position_org_line5,
1762 p_to_position_org_line6 => l_from_position_org_line6,
1763 p_to_position_number => l_from_position_number,
1764 p_to_position_seq_no => l_from_position_seq_no,
1765 p_to_position_title => l_from_position_title,
1766 p_to_step_or_rate => l_to_step_or_rate,
1767 p_to_total_salary => l_to_total_salary,
1768 p_veterans_preference => l_veterans_preference,
1769 p_veterans_pref_for_rif => l_veterans_pref_for_rif,
1770 p_veterans_status => l_veterans_status,
1771 p_work_schedule => l_work_schedule,
1772 p_work_schedule_desc => l_work_schedule_desc,
1773 p_year_degree_attained => l_year_degree_attained,
1774 p_first_noa_information1 => NULL,
1775 p_first_noa_information2 => NULL,
1776 p_first_noa_information3 => NULL,
1777 p_first_noa_information4 => NULL,
1778 p_first_noa_information5 => NULL,
1779 p_second_lac1_information1 => NULL,
1780 p_second_lac1_information2 => NULL,
1781 p_second_lac1_information3 => NULL,
1782 p_second_lac1_information4 => NULL,
1783 p_second_lac1_information5 => NULL,
1784 p_second_lac2_information1 => NULL,
1785 p_second_lac2_information2 => NULL,
1786 p_second_lac2_information3 => NULL,
1787 p_second_lac2_information4 => NULL,
1788 p_second_lac2_information5 => NULL,
1789 p_second_noa_information1 => NULL,
1790 p_second_noa_information2 => NULL,
1791 p_second_noa_information3 => NULL,
1792 p_second_noa_information4 => NULL,
1793 p_second_noa_information5 => NULL,
1794 p_first_lac1_information1 => NULL,
1795 p_first_lac1_information2 => NULL,
1796 p_first_lac1_information3 => NULL,
1797 p_first_lac1_information4 => NULL,
1798 p_first_lac1_information5 => NULL,
1799 p_first_lac2_information1 => NULL,
1800 p_first_lac2_information2 => NULL,
1801 p_first_lac2_information3 => NULL,
1802 p_first_lac2_information4 => NULL,
1803 p_first_lac2_information5 => NULL,
1804 p_u_attachment_modified_flag => l_u_attachment_modified_flag,
1805 p_u_approved_flag => l_u_approved_flag,
1806 p_u_user_name_acted_on => l_u_user_name_acted_on,
1807 p_u_action_taken => l_u_action_taken,
1808 p_i_user_name_routed_to => l_i_user_name_routed_to,
1809 p_i_groupbox_id => l_i_groupbox_id,
1810 p_i_routing_list_id => l_i_routing_list_id,
1811 p_i_routing_seq_number => l_i_routing_seq_number,
1812 p_u_prh_object_version_number => l_u_prh_object_version_number,
1813 p_i_pa_routing_history_id => l_i_pa_routing_history_id,
1814 p_i_prh_object_version_number => l_i_prh_object_version_number,
1815 p_to_retention_allow_percentag => l_retention_allow_percentage,
1816 p_to_supervisory_diff_percenta => l_supervisory_diff_percentage,
1817 p_to_staffing_diff_percentage => l_staffing_diff_percentage,
1818 p_award_percentage => l_award_percentage,
1819 -- FWFA Changes
1820 p_input_pay_rate_determinant => l_pay_rate_determinant,
1821 p_from_pay_table_identifier => l_pay_calc_out_rec_type.pay_table_id,
1822 p_to_pay_table_identifier => l_pay_calc_out_rec_type.calculation_pay_table_id
1823 -- FWFA Changes
1824 );
1825
1826 -- Create Remarks
1827 IF l_remark_id1 IS NOT NULL
1828 THEN
1829 IF val_pa_remarks%isopen then
1830 close val_pa_remarks;
1831 END IF;
1832
1833 OPEN val_pa_remarks(p_pa_request_id => l_pa_request_id,
1834 p_remark_id => l_remark_id1);
1835 FETCH val_pa_remarks into m_val_pa_remarks;
1836 IF val_pa_remarks%NOTFOUND THEN
1837 ghr_pa_remarks_api.create_pa_remarks (
1838 p_pa_request_id => l_pa_request_id,
1839 p_remark_id => l_remark_id1,
1840 p_description => l_remark_desc1,
1841 p_remark_code_information1 => l_remark1_info1,
1842 p_remark_code_information2 => l_remark1_info2,
1843 p_remark_code_information3 => l_remark1_info3,
1844 p_pa_remark_id => l_pa_remark_id,
1845 p_object_version_number => l_pre_object_version_number
1846 );
1847 END IF;
1848 CLOSE val_pa_remarks;
1849 END IF;
1850
1851 IF l_remark_id2 IS NOT NULL
1852 THEN
1853 IF val_pa_remarks%isopen then
1854 close val_pa_remarks;
1855 END IF;
1856
1857 OPEN val_pa_remarks(p_pa_request_id => l_pa_request_id,
1858 p_remark_id => l_remark_id2);
1859 FETCH val_pa_remarks into m_val_pa_remarks;
1860 IF val_pa_remarks%NOTFOUND THEN
1861 ghr_pa_remarks_api.create_pa_remarks (
1862 p_pa_request_id => l_pa_request_id,
1863 p_remark_id => l_remark_id2,
1864 p_description => l_remark_desc2,
1865 p_remark_code_information1 => l_remark2_info1,
1866 p_remark_code_information2 => l_remark2_info2,
1867 p_remark_code_information3 => l_remark2_info3,
1868 p_pa_remark_id => l_pa_remark_id,
1869 p_object_version_number => l_pre_object_version_number
1870 );
1871 END IF;
1872 CLOSE val_pa_remarks;
1873 END IF;
1874
1875 --
1876 COMMIT;
1877
1878 --
1879 IF p_log_flag = 'Y'
1880 THEN
1881 create_ghr_errorlog (p_program_name => l_proc,
1882 p_log_text => 'After update_sf52 for : '
1883 || l_full_name
1884 || ' Person ID : '
1885 || TO_CHAR (l_person_id
1886 )
1887 || ' l_retirement_plan : '
1888 || l_retirement_plan,
1889 p_message_name => NULL,
1890 p_log_date => SYSDATE
1891 );
1892 END IF;
1893
1894 --
1895 -- start the wgi workflow.
1896 --
1897 ghr_wf_wgi_pkg.startwgiprocess (p_pa_request_id => l_pa_request_id,
1898 p_full_name => l_full_name
1899 );
1900 create_ghr_errorlog (p_program_name => l_proc,
1901 p_log_text => 'Started Auto WGI Workflow process for : '
1902 || l_full_name
1903 || ' Person ID : '
1904 || TO_CHAR (l_person_id
1905 )
1906 || ' Assignment ID : '
1907 || TO_CHAR (l_assignment_id
1908 )
1909 || ' PA Request ID : '
1910 || TO_CHAR (l_pa_request_id
1911 ),
1912 p_message_name => NULL,
1913 p_log_date => SYSDATE
1914 );
1915 COMMIT;
1916 --
1917 -- reset the flag
1918 l_start_wgi_wf_flag := 'N';
1919 --
1920 END IF; -- if l_wgi_out_rec_type.process_person
1921 END IF; --if (check_pay_plan(p_pay_plan => l_tmp_from_pay_plan))
1922 END IF; -- IF person_in_pa_requests
1923 --END IF; -- IF check_assignment_prd
1924 ELSE
1925 RAISE l_max_step_exception;
1926 END IF; -- (IF checkIfMaxPayPlan)
1927 END IF; -- IF ( p_effective_date >= (l_wgi_pay_dat
1928 ELSE -- Add for Bug 3035967 fix.
1929 hr_utility.set_location('error in pay date',2500) ;
1930 IF l_wgi_due_date IS NOT NULL THEN
1931 -- Check if max step is reached. If it has, then ignore this employee
1932 IF (checkifmaxpayplan (p_from_pay_plan => l_from_pay_plan,
1933 p_from_step_or_rate => l_from_step_or_rate
1934 )
1935 )
1936 THEN
1937 create_ghr_errorlog (p_program_name => l_proc,
1938 p_log_text => 'ERROR encountered in processing Auto WGI for : '
1939 || l_full_name
1940 || ' Error Message :'
1941 || ' The employee does not have a WGI Pay Date.'
1942 || ' Cause: The employee does not have a WGI Pay Date in their Within Grade '
1943 || ' Increase element entry. The employee cannot be selected for a WGI without a '
1944 || ' Pay Date. '
1945 || ' Action: You must enter a Pay Date in the employee''s Within Grade Increase '
1946 || ' element entry. Person ID : '
1947 || TO_CHAR (l_person_id
1948 )
1949 || ' Assignment ID : '
1950 || TO_CHAR (l_assignment_id
1951 )
1952 || ' PA Request ID : '
1953 || TO_CHAR (l_pa_request_id
1954 ),
1955 p_message_name => 'Pay Date not entered',
1956 p_log_date => SYSDATE
1957 );
1958 l_retcode := 1;
1959 COMMIT;
1960 END IF; -- IF (checkifmaxpayplan if wgiduedate is not null
1961 END IF; -- IF l_wgi_due_date IS NULL
1962 END IF; -- IF l_wgi_pay_date IS NOT NULL THEN
1963 END IF; -- IF ( CheckPayPlanParm
1964 END IF; -- l_pay_rate_determinant <> 'Y'
1965 /*End Bug:9255822*/
1966 END IF; -- IF l_value IN ('1','2','3')
1967 END IF; -- IF l_assignment_id IS NOT NULL THEN
1968 END IF; --- IF check_pay_plan(l_per_pay_plan)
1969 --
1970 -- log message and continue processing next record
1971 --
1972 END IF; -- IF ( CheckPOIParm - Moved outside
1973 EXCEPTION
1974 -- Bug 3649933 To raise error when group box does not exist.
1975
1976 WHEN l_no_groupbox_exception THEN
1977 hr_utility.set_location('error in group box',2500) ;
1978 create_ghr_errorlog (p_program_name => l_proc,
1979 p_log_text => 'ERROR encountered in processing Auto WGI for : '
1980 || l_full_name
1981 || ' ** Error Message ** : '
1982 || ' Group Box doesnt exist for Personnel Office ID for '
1983 || ' Person ID : '
1984 || TO_CHAR (l_person_id
1985 )
1986 ,
1987 p_message_name => 'No Group Box',
1988 p_log_date => SYSDATE
1989 );
1990 l_retcode := 1;
1991 WHEN l_from_asg_exception THEN
1992 hr_utility.set_location('error in fetch from asg',2500) ;
1993 create_ghr_errorlog (p_program_name => l_proc,
1994 p_log_text => 'ERROR encountered in processing Auto WGI for : '
1995 || l_full_name
1996 || ' ** Error Message ** : '
1997 || ' Error in fetching values from Assignment for '
1998 || ' Person ID : '
1999 || TO_CHAR (l_person_id
2000 )
2001 || ' Assignment ID : '
2002 || TO_CHAR (l_assignment_id
2003 )
2004 || ' PA Request ID : '
2005 || TO_CHAR (l_pa_request_id
2006 ),
2007 p_message_name => 'Fetch values from Assignment',
2008 p_log_date => SYSDATE
2009 );
2010
2011 l_retcode := 1;
2012 WHEN l_retained_grade_exception THEN
2013 hr_utility.set_location('error in l_retained_grade_exception',2500) ;
2014 create_ghr_errorlog (p_program_name => l_proc,
2015 p_log_text => 'ERROR encountered in processing Auto WGI for : '
2016 || l_full_name
2017 || ' ** Error Message ** : '
2018 || ' Error in getting Retained Grade details for '
2019 || ' Person ID : '
2020 || TO_CHAR (l_person_id
2021 )
2022 || ' Assignment ID : '
2023 || TO_CHAR (l_assignment_id
2024 )
2025 || ' PA Request ID : '
2026 || TO_CHAR (l_pa_request_id
2027 ),
2028 p_message_name => 'Get Retained Grade details',
2029 p_log_date => SYSDATE
2030 );
2031
2032 l_retcode := 1;
2033 WHEN l_max_step_exception THEN
2034 hr_utility.set_location('error in l_retained_grade_exception',2500) ;
2035 create_ghr_errorlog (p_program_name => l_proc,
2036 p_log_text => 'ERROR encountered in processing Auto WGI for : '
2037 || l_full_name
2038 || ' Error Message :'
2039 || ' The employee has reached Maximum Step assigned for the Pay plan'
2040 || ' Person ID : '
2041 || TO_CHAR (l_person_id
2042 )
2043 || ' Assignment ID : '
2044 || TO_CHAR (l_assignment_id
2045 )
2046 || ' PA Request ID : '
2047 || TO_CHAR (l_pa_request_id
2048 ),
2049 p_message_name => 'Maximum Step Reached',
2050 p_log_date => SYSDATE
2051 );
2052 l_retcode := 1;
2053 WHEN OTHERS
2054 THEN
2055 --bug# 5685874 reassigning l_errbuf to null so that buffer overflow does not occur.
2056 l_errbuf := NULL;
2057
2058 hr_utility.set_location('error in OTHERS' || SQLCODE ,2500) ;
2059 create_ghr_errorlog (p_program_name => l_proc,
2060 p_log_text => 'ERROR encountered in processing Auto WGI for : '
2061 || l_full_name
2062 || ' ** Error Message ** : '
2063 || SUBSTR (SQLERRM,
2064 1,
2065 1000
2066 )
2067 || ' Person ID : '
2068 || TO_CHAR (l_person_id
2069 )
2070 || ' Assignment ID : '
2071 || TO_CHAR (l_assignment_id
2072 )
2073 || ' PA Request ID : '
2074 || TO_CHAR (l_pa_request_id
2075 ),
2076 p_message_name => to_char(SQLCODE),
2077 p_log_date => SYSDATE
2078 );
2079
2080 IF l_start_wgi_wf_flag = 'Y'
2081 THEN
2082 ghr_wf_wgi_pkg.startwgiprocess (p_pa_request_id => l_pa_request_id,
2083 p_full_name => l_full_name
2084 );
2085 create_ghr_errorlog (p_program_name => l_proc,
2086 p_log_text => 'Started Auto WGI Workflow process for : '
2087 || l_full_name
2088 || ' Person ID : '
2089 || TO_CHAR (l_person_id
2090 )
2091 || ' Assignment ID : '
2092 || TO_CHAR (l_assignment_id
2093 )
2094 || ' PA Request ID : '
2095 || TO_CHAR (l_pa_request_id
2096 ),
2097 p_message_name => NULL,
2098 p_log_date => SYSDATE
2099 );
2100 COMMIT;
2101 --
2102 -- reset the flag
2103 l_start_wgi_wf_flag := 'N';
2104 --
2105 ELSE
2106 l_retcode := 1;
2107
2108
2109
2110 IF l_errbuf IS NULL
2111 THEN
2112 l_errbuf :=
2113 'The following PA Request could not be processed : '
2114 || l_full_name
2115 || ' PA Request id : '
2116 || TO_CHAR (l_pa_request_id);
2117 ELSE
2118 l_errbuf :=
2119 l_errbuf
2120 || ', '
2121 || TO_CHAR (l_person_id)
2122 || ':'
2123 || TO_CHAR (l_pa_request_id);
2124 END IF;
2125
2126
2127 END IF;
2128 END;
2129 END LOOP;
2130 p_retcode := l_retcode;
2131 p_errbuf := l_errbuf;
2132 -- hr_utility.trace_off;
2133 CLOSE per_assign_cursor;
2134 EXCEPTION
2135 -- Bug 3649933 To raise error when group box does not exist.
2136 WHEN l_no_groupbox_exception THEN
2137 p_retcode := 2;
2138 p_errbuf := 'Group Box does not exist for this Personnel office id :' || l_in_personnel_office_id;
2139 --RAISE;
2140 WHEN OTHERS
2141 THEN
2142 p_errbuf := NULL;
2143 p_retcode := NULL;
2144 RAISE;
2145 END ghr_wgi_emp;
2146
2147 --
2148 --
2149 PROCEDURE create_ghr_errorlog (
2150 p_program_name IN ghr_process_log.program_name%TYPE,
2151 p_log_text IN ghr_process_log.log_text%TYPE,
2152 p_message_name IN ghr_process_log.message_name%TYPE,
2153 p_log_date IN ghr_process_log.log_date%TYPE
2154 )
2155 IS
2156 --
2157 l_message_name ghr_process_log.message_name%TYPE;
2158 BEGIN
2159 --
2160 l_message_name := NVL (p_message_name, 'Please see log text');
2161
2162 INSERT INTO ghr_process_log
2163 (process_log_id, program_name, log_text,
2164 message_name, log_date
2165 )
2166 VALUES (ghr_process_log_s.NEXTVAL, p_program_name, p_log_text,
2167 l_message_name, p_log_date
2168 );
2169 EXCEPTION
2170 WHEN NO_DATA_FOUND
2171 THEN
2172 NULL;
2173 END create_ghr_errorlog;
2174
2175 --
2176 --
2177 PROCEDURE get_noa_code_desc (
2178 p_code IN ghr_nature_of_actions.code%TYPE,
2179 p_effective_date IN DATE DEFAULT SYSDATE,
2180 p_nature_of_action_id OUT NOCOPY ghr_nature_of_actions.nature_of_action_id%TYPE,
2181 p_description OUT NOCOPY ghr_nature_of_actions.description%TYPE
2182 )
2183 IS
2184 --
2185 -- local variables
2186 --
2187 l_effective_date DATE;
2188 l_code ghr_nature_of_actions.code%TYPE;
2189 l_description ghr_nature_of_actions.description%TYPE;
2190 l_nature_of_action_id ghr_nature_of_actions.nature_of_action_id%TYPE;
2191
2192 --
2193 CURSOR csr_noa
2194 IS
2195 SELECT noa.nature_of_action_id, noa.description
2196 FROM ghr_nature_of_actions noa
2197 WHERE noa.code = l_code
2198 AND noa.enabled_flag = 'Y'
2199 AND NVL (l_effective_date, TRUNC (SYSDATE)) BETWEEN noa.date_from
2200 AND NVL (noa.date_to,
2201 NVL (l_effective_date,
2202 TRUNC (SYSDATE
2203 )
2204 )
2205 );
2206 --
2207 BEGIN
2208 --
2209 l_code := p_code;
2210
2211 IF (p_effective_date IS NOT NULL)
2212 THEN
2213 l_effective_date := p_effective_date;
2214 END IF;
2215
2216 OPEN csr_noa;
2217 FETCH csr_noa INTO l_nature_of_action_id, l_description;
2218
2219 IF csr_noa%NOTFOUND
2220 THEN
2221 -- if the cursor does not return a row then we must set the out
2222 -- parameter to null
2223 p_description := NULL;
2224 p_nature_of_action_id := NULL;
2225 ELSE
2226 p_description := l_description;
2227 p_nature_of_action_id := l_nature_of_action_id;
2228 END IF;
2229
2230 -- close the cursor
2231 CLOSE csr_noa;
2232 --
2233 --
2234 EXCEPTION
2235 WHEN NO_DATA_FOUND
2236 THEN
2237 NULL;
2238 WHEN OTHERS
2239 THEN
2240 p_nature_of_action_id := NULL;
2241 p_description := NULL;
2242 RAISE;
2243 END get_noa_code_desc;
2244
2245 --
2246 --
2247 FUNCTION get_fnd_lookup_meaning (
2248 p_lookup_type IN hr_lookups.lookup_type%TYPE,
2249 p_lookup_code IN hr_lookups.lookup_code%TYPE,
2250 p_effective_date IN DATE DEFAULT SYSDATE
2251 )
2252 RETURN hr_lookups.meaning%TYPE
2253 IS
2254 --
2255 -- Local variables
2256 -- This function returns description and not meaning as is used to fetch only type GHR_US_LEGAL_AUTHORITY
2257 --
2258 l_effective_date ghr_pa_requests.effective_date%TYPE;
2259 l_lookup_type hr_lookups.lookup_type%TYPE;
2260 l_lookup_code hr_lookups.lookup_code%TYPE;
2261 l_meaning hr_lookups.meaning%TYPE;
2262
2263 --
2264 --
2265 CURSOR csr_lkp_code
2266 IS
2267 SELECT fcl.description
2268 FROM hr_lookups fcl
2269 WHERE fcl.lookup_type = l_lookup_type
2270 AND fcl.lookup_code = l_lookup_code
2271 AND fcl.enabled_flag = 'Y'
2272 AND NVL (l_effective_date, TRUNC (SYSDATE))
2273 BETWEEN NVL (fcl.start_date_active,
2274 NVL (l_effective_date, TRUNC (SYSDATE))
2275 )
2276 AND NVL (fcl.end_date_active,
2277 NVL (l_effective_date, TRUNC (SYSDATE))
2278 );
2279 --
2280 BEGIN
2281 --
2282 l_lookup_type := p_lookup_type;
2283 l_lookup_code := p_lookup_code;
2284
2285 IF p_effective_date IS NOT NULL
2286 THEN
2287 l_effective_date := p_effective_date;
2288 ELSE
2289 l_effective_date := SYSDATE;
2290 END IF;
2291
2292 -- Open Lookup cursor
2293 OPEN csr_lkp_code;
2294 FETCH csr_lkp_code INTO l_meaning;
2295
2296 IF csr_lkp_code%NOTFOUND
2297 THEN
2298 l_meaning := NULL;
2299 ELSE
2300 l_meaning := l_meaning;
2301 END IF;
2302
2303 CLOSE csr_lkp_code;
2304 RETURN l_meaning;
2305 --
2306 END get_fnd_lookup_meaning;
2307
2308 --
2309 -- Check assignment id
2310 --
2311 FUNCTION check_assignment_prd (
2312 p_pay_rate_determinant IN ghr_pa_requests.pay_rate_determinant%TYPE
2313 )
2314 RETURN BOOLEAN
2315 IS
2316 --
2317 BEGIN
2318 IF p_pay_rate_determinant IN
2319 ('0', '5', '6', '7', 'M', 'A', 'B', 'E', 'F')
2320 THEN
2321 RETURN TRUE;
2322 ELSE
2323 RETURN FALSE;
2324 END IF;
2325 END check_assignment_prd;
2326
2327 --
2328 -- Verify person in PA requests
2329 --
2330 FUNCTION person_in_pa_requests (
2331 p_person_id IN ghr_pa_requests.person_id%TYPE,
2332 p_effective_date IN ghr_pa_requests.effective_date%TYPE,
2333 p_first_noa_code IN ghr_pa_requests.first_noa_code%TYPE,
2334 p_days IN NUMBER
2335 )
2336 RETURN BOOLEAN
2337 IS
2338 --
2339 CURSOR csr_action_taken
2340 IS
2341 SELECT pr.pa_request_id,
2342 MAX (pa_routing_history_id) pa_routing_history_id
2343 FROM ghr_pa_requests pr, ghr_pa_routing_history prh
2344 WHERE pr.pa_request_id = prh.pa_request_id
2345 AND person_id = p_person_id
2346 AND (first_noa_code = p_first_noa_code OR first_noa_code = '888'
2347 )
2348 AND effective_date >= (p_effective_date - p_days)
2349 AND NVL (pr.first_noa_cancel_or_correct, 'X') <>
2350 ghr_history_api.g_cancel
2351 -- vtakru Apr 17, 98 vtakru
2352 -- and nvl(pr.first_noa_cancel_or_correct,'X') <> 'CANCELED'
2353 GROUP BY pr.pa_request_id;
2354
2355 l_action_taken ghr_pa_routing_history.action_taken%TYPE;
2356 BEGIN
2357 FOR v_action_taken IN csr_action_taken
2358 LOOP
2359 SELECT NVL (action_taken, ' ')
2360 INTO l_action_taken
2361 FROM ghr_pa_routing_history
2362 WHERE pa_routing_history_id = v_action_taken.pa_routing_history_id;
2363
2364 IF l_action_taken <> 'CANCELED'
2365 THEN
2366 RETURN FALSE;
2367 END IF;
2368 END LOOP;
2369
2370 RETURN TRUE;
2371 END person_in_pa_requests;
2372
2373 --
2374 --
2375 FUNCTION check_pay_plan (p_pay_plan IN VARCHAR2)
2376 RETURN BOOLEAN
2377 IS
2378 --
2379 l_exists VARCHAR2 (1);
2380 l_pay_plan VARCHAR2(200);
2381
2382 CURSOR csr_pay_plan (p_pay_plan ghr_pay_plans.pay_plan%TYPE)
2383 IS
2384 SELECT 'X'
2385 FROM ghr_pay_plans
2386 WHERE pay_plan = l_pay_plan AND wgi_enabled_flag = 'Y';
2387 --
2388 BEGIN
2389 l_pay_plan := p_pay_plan;
2390 OPEN csr_pay_plan (l_pay_plan);
2391 FETCH csr_pay_plan INTO l_exists;
2392
2393 IF csr_pay_plan%NOTFOUND
2394 THEN
2395 CLOSE csr_pay_plan;
2396 RETURN FALSE;
2397 ELSE
2398 CLOSE csr_pay_plan;
2399 RETURN TRUE;
2400 END IF;
2401 END check_pay_plan;
2402
2403 --
2404 --
2405 --
2406 --
2407 -- Derive Legal Authority code and Remarks
2408 --
2409 PROCEDURE derive_legal_auth_cd_remarks (
2410 p_first_noa_code IN ghr_pa_requests.first_noa_code%TYPE,
2411 p_pay_rate_determinant IN ghr_pa_requests.pay_rate_determinant%TYPE,
2412 p_from_pay_plan IN ghr_pa_requests.from_pay_plan%TYPE,
2413 p_grade_or_level IN ghr_pa_requests.from_grade_or_level%TYPE,
2414 p_step_or_rate IN ghr_pa_requests.from_step_or_rate%TYPE,
2415 p_retained_pay_plan IN ghr_pa_requests.from_pay_plan%TYPE
2416 DEFAULT NULL,
2417 p_retained_grade_or_level IN ghr_pa_requests.from_grade_or_level%TYPE
2418 DEFAULT NULL,
2419 p_retained_step_or_rate IN ghr_pa_requests.from_step_or_rate%TYPE
2420 DEFAULT NULL,
2421 -- Bug#5204589
2422 p_temp_step IN ghr_pa_requests.from_step_or_rate%TYPE default null,
2423 p_effective_date IN ghr_pa_requests.effective_date%TYPE,
2424 p_first_action_la_code1 IN OUT NOCOPY ghr_pa_requests.first_action_la_code1%TYPE,
2425 p_first_action_la_desc1 IN OUT NOCOPY ghr_pa_requests.first_action_la_desc1%TYPE,
2426 p_first_action_la_code2 IN OUT NOCOPY ghr_pa_requests.first_action_la_code2%TYPE,
2427 p_first_action_la_desc2 IN OUT NOCOPY ghr_pa_requests.first_action_la_desc2%TYPE,
2428 p_remark_id1 OUT NOCOPY ghr_pa_remarks.remark_id%TYPE,
2429 p_remark_desc1 OUT NOCOPY ghr_pa_remarks.description%TYPE,
2430 p_remark1_info1 OUT NOCOPY ghr_pa_remarks.remark_code_information1%TYPE,
2431 p_remark1_info2 OUT NOCOPY ghr_pa_remarks.remark_code_information2%TYPE,
2432 p_remark1_info3 OUT NOCOPY ghr_pa_remarks.remark_code_information3%TYPE,
2433 p_remark_id2 OUT NOCOPY ghr_pa_remarks.remark_id%TYPE,
2434 p_remark_desc2 OUT NOCOPY ghr_pa_remarks.description%TYPE,
2435 p_remark2_info1 OUT NOCOPY ghr_pa_remarks.remark_code_information1%TYPE,
2436 p_remark2_info2 OUT NOCOPY ghr_pa_remarks.remark_code_information2%TYPE,
2437 p_remark2_info3 OUT NOCOPY ghr_pa_remarks.remark_code_information3%TYPE
2438 )
2439 IS
2440 l_eq_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
2441 l_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
2442 l_remark_id1 ghr_remarks.remark_id%TYPE;
2443 l_remark_code1 ghr_remarks.code%TYPE := NULL;
2444 l_remark_desc1 ghr_pa_remarks.description%TYPE := NULL;
2445 l_remark_desc1_out ghr_pa_remarks.description%TYPE;
2446 l_remark_code2 ghr_remarks.code%TYPE := NULL;
2447 l_remark_desc2 ghr_pa_remarks.description%TYPE := NULL;
2448 l_remark_desc2_out ghr_pa_remarks.description%TYPE;
2449 l_remark1_ins1 VARCHAR2 (150) := NULL;
2450 l_remark1_ins2 VARCHAR2 (150) := NULL;
2451 l_remark1_ins3 VARCHAR2 (150) := NULL;
2452 l_remark2_ins1 VARCHAR2 (150) := NULL;
2453 l_remark2_ins2 VARCHAR2 (150) := NULL;
2454 l_remark2_ins3 VARCHAR2 (150) := NULL;
2455 l_first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
2456 l_first_action_la_desc1 ghr_pa_requests.first_action_la_desc1%TYPE;
2457 l_first_action_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
2458 l_first_action_la_desc2 ghr_pa_requests.first_action_la_desc2%TYPE;
2459
2460 CURSOR c_eq_pay_plan
2461 IS
2462 SELECT gpp.equivalent_pay_plan
2463 FROM ghr_pay_plans gpp
2464 WHERE gpp.pay_plan = l_from_pay_plan;
2465 --
2466 --
2467 BEGIN
2468 l_first_action_la_code1 := p_first_action_la_code1;
2469 l_first_action_la_desc1 := p_first_action_la_desc1;
2470 l_first_action_la_code2 := p_first_action_la_code2;
2471 l_first_action_la_desc2 := p_first_action_la_desc2;
2472 p_first_action_la_code1 := NULL;
2473 p_first_action_la_desc1 := NULL;
2474 p_first_action_la_code2 := NULL;
2475 p_first_action_la_desc2 := NULL;
2476 p_remark_id1 := NULL;
2477 p_remark_id2 := NULL;
2478 p_remark_desc1 := NULL;
2479 p_remark_desc2 := NULL;
2480
2481 IF p_first_noa_code = '893'
2482 THEN
2483 IF p_pay_rate_determinant IN ('A', 'B', 'E', 'F') AND p_temp_step is NULL
2484 THEN
2485 -- GEt the equivalent pay plan for the retained pay plan
2486 l_from_pay_plan := p_retained_pay_plan;
2487
2488 FOR eq_pay_plan_rec IN c_eq_pay_plan
2489 LOOP
2490 l_eq_pay_plan := eq_pay_plan_rec.equivalent_pay_plan;
2491 END LOOP;
2492
2493 IF l_eq_pay_plan = 'FW'
2494 THEN
2495 p_first_action_la_code1 := 'VUL';
2496 p_first_action_la_code2 := 'VLJ';
2497 l_remark_code1 := 'P14'; -- Bug
2498 l_remark_code2 := 'X46';
2499 -- Bug# 5204589 Modified the variable from l_remark1_ins1 to l_remark2_ins1.
2500 l_remark2_ins1 := TO_CHAR (TO_NUMBER (p_retained_step_or_rate) + 1);
2501 l_remark2_ins1 := LPAD(l_remark2_ins1,2,0);
2502 l_remark2_ins2 :=
2503 p_retained_pay_plan || '-'
2504 || p_retained_grade_or_level;
2505 ELSIF l_eq_pay_plan = 'GS' AND l_from_pay_plan <> 'GG'
2506 THEN
2507 p_first_action_la_code1 := 'Q7M';
2508 p_first_action_la_code2 := 'VLJ';
2509 l_remark_code1 := 'P14';
2510 l_remark_code2 := 'X46';
2511 l_remark2_ins1 := TO_CHAR (TO_NUMBER (p_retained_step_or_rate) + 1);
2512 l_remark2_ins1 := LPAD(l_remark2_ins1,2,0);
2513 l_remark2_ins2 :=
2514 p_retained_pay_plan || '-'
2515 || p_retained_grade_or_level;
2516 ELSIF l_eq_pay_plan = 'GS' AND l_from_pay_plan = 'GG'
2517 THEN
2518 p_first_action_la_code1 := 'UAM';
2519 p_first_action_la_code2 := NULL;
2520 l_remark_code1 := 'X44';
2521 l_remark_code2 := NULL;
2522 l_remark1_ins1 :=
2523 TO_CHAR (TO_NUMBER (p_retained_step_or_rate) + 1);
2524 l_remark1_ins1 := LPAD(l_remark1_ins1,2,0);
2525 l_remark1_ins2 :=
2526 p_retained_pay_plan || '-'
2527 || p_retained_grade_or_level;
2528 END IF;
2529 ELSE --IF p_pay_rate_determinant NOT IN ('A', 'B', 'E', 'F') THEN
2530 l_from_pay_plan := p_from_pay_plan;
2531
2532 -- GEt the equivalent pay plan for the from_pay_plan
2533 FOR eq_pay_plan_rec IN c_eq_pay_plan
2534 LOOP
2535 l_eq_pay_plan := eq_pay_plan_rec.equivalent_pay_plan;
2536 END LOOP;
2537
2538 IF l_eq_pay_plan = 'FW'
2539 THEN
2540 p_first_action_la_code1 := 'VUL';
2541 l_remark_code1 := 'P14'; -- Bug 5090440
2542 ------Added Z2P for GM pay plan as a part of GPPA Requirement
2543 ELSIF l_eq_pay_plan = 'GS' AND l_from_pay_plan = 'GM'
2544 THEN
2545 p_first_action_la_code1 := 'Z2P';
2546 l_remark_code1 := 'P14';
2547 -----GPPA End
2548 ELSIF l_eq_pay_plan = 'GS'
2549 THEN
2550 p_first_action_la_code1 := 'Q7M';
2551 l_remark_code1 := 'P14';
2552 END IF;
2553
2554 IF l_from_pay_plan = 'GG'
2555 THEN
2556 p_first_action_la_code1 := 'UAM';
2557
2558 IF p_pay_rate_determinant IN
2559 ('J', 'K', 'R', 'S', 'U', 'V', '3')
2560 THEN
2561 l_remark_code1 := 'X40';
2562 END IF;
2563 END IF;
2564 END IF; -- PRD
2565
2566 IF p_pay_rate_determinant IN ('6', 'E', 'F')
2567 THEN
2568 IF l_remark_code1 IS NULL
2569 THEN
2570 l_remark_code1 := 'P05';
2571 ELSE
2572 l_remark_code2 := 'P05';
2573 END IF;
2574 END IF;
2575 END IF; -- 893;
2576
2577 -- If NOA code is 891
2578 -- not including the check for pay plan = 'GM' because the very fact that this is
2579 -- a 891 NOA implies that.
2580
2581 IF p_first_noa_code = '891' THEN
2582 IF p_pay_rate_determinant IN ('A', 'B', 'E', 'F') AND p_temp_step IS NULL THEN
2583 p_first_action_la_code1 := 'Z2P';
2584 p_first_action_la_code2 := 'VLJ';
2585 l_remark_code1 := 'P14';
2586 l_remark_code2 := 'X62';
2587 l_remark2_ins1 :=
2588 p_retained_pay_plan || '-'
2589 || p_retained_grade_or_level;
2590 ELSE
2591 p_first_action_la_code1 := 'Z2P';
2592 p_first_action_la_code2 := NULL;
2593 l_remark_code1 := 'P14';
2594 END IF;
2595 END IF;
2596
2597 -- If NOA code is 888
2598 IF p_first_noa_code = '888' THEN
2599
2600 IF p_pay_rate_determinant IN ('A', 'B', 'E', 'F') AND p_temp_step IS NULL THEN
2601
2602 IF p_from_pay_plan = 'GM' THEN
2603 p_first_action_la_code1 := 'Z2P';
2604 p_first_action_la_code2 := 'VLJ';
2605 l_remark_code1 := 'P91';
2606 l_remark_code2 := 'X63';
2607 l_remark2_ins1 :=
2608 p_retained_pay_plan || '-'
2609 || p_retained_grade_or_level;
2610 ELSE
2611 p_first_action_la_code1 := 'Q5M';
2612 p_first_action_la_code2 := 'VLJ';
2613 l_remark_code1 := 'P15';
2614 l_remark1_ins1 := TO_CHAR (TO_NUMBER (p_retained_step_or_rate) + 1);
2615 l_remark1_ins1 := LPAD(l_remark1_ins1,2,0);
2616 l_remark1_ins2 := p_grade_or_level;
2617 l_remark1_ins3 := p_step_or_rate; -- ?? Retained Step
2618 l_remark_code2 := 'X47';
2619 l_remark2_ins1 := TO_CHAR (TO_NUMBER (p_retained_step_or_rate) + 1);
2620 l_remark2_ins1 := LPAD(l_remark2_ins1,2,0);
2621 END IF;
2622
2623 ELSE
2624
2625 IF p_from_pay_plan = 'GM' THEN
2626 p_first_action_la_code1 := 'Z2P';
2627 p_first_action_la_code2 := NULL;
2628 l_remark_code1 := 'P91';
2629 ELSE
2630 p_first_action_la_code1 := 'Q5M';
2631 p_first_action_la_code2 := NULL;
2632 l_remark_code1 := 'P15';
2633 l_remark1_ins1 := TO_CHAR (TO_NUMBER (p_step_or_rate) + 1);
2634 l_remark1_ins1 := LPAD(l_remark1_ins1,2,0);
2635 l_remark1_ins2 := p_grade_or_level;
2636 l_remark1_ins3 := p_step_or_rate;
2637
2638 END IF;
2639
2640 END IF;
2641 END IF;
2642
2643 IF p_first_action_la_code1 IS NOT NULL
2644 THEN
2645 p_first_action_la_desc1 :=
2646 get_fnd_lookup_meaning (p_lookup_type => 'GHR_US_LEGAL_AUTHORITY',
2647 p_lookup_code => p_first_action_la_code1,
2648 p_effective_date => p_effective_date
2649 );
2650 END IF;
2651
2652 IF p_first_action_la_code2 IS NOT NULL
2653 THEN
2654 p_first_action_la_desc2 :=
2655 get_fnd_lookup_meaning (p_lookup_type => 'GHR_US_LEGAL_AUTHORITY',
2656 p_lookup_code => p_first_action_la_code2,
2657 p_effective_date => p_effective_date
2658 );
2659 END IF;
2660
2661 IF l_remark_code1 IS NOT NULL
2662 THEN
2663 ghr_mass_actions_pkg.get_remark_id_desc (p_remark_code => l_remark_code1,
2664 p_effective_date => p_effective_date,
2665 p_remark_id => l_remark_id1,
2666 p_remark_desc => l_remark_desc1
2667 );
2668
2669 -- Insertion values. ghr_mass_actions_pkg.replace_insertion_values
2670 IF l_remark1_ins1 IS NOT NULL
2671 OR l_remark1_ins2 IS NOT NULL
2672 OR l_remark1_ins3 IS NOT NULL
2673 THEN
2674 -- Bug#4256022 Passed the variable l_remark_desc1_out and assigned
2675 -- the value back to l_remark_desc1 to avoid NOCOPY related problems.
2676 ghr_mass_actions_pkg.replace_insertion_values (p_desc => l_remark_desc1,
2677 p_information1 => l_remark1_ins1,
2678 p_information2 => l_remark1_ins2,
2679 p_information3 => l_remark1_ins3,
2680 p_desc_out => l_remark_desc1_out
2681 );
2682 l_remark_desc1 := l_remark_desc1_out;
2683 END IF;
2684 END IF;
2685
2686 IF l_remark_code2 IS NOT NULL
2687 THEN
2688 ghr_mass_actions_pkg.get_remark_id_desc (p_remark_code => l_remark_code2,
2689 p_effective_date => p_effective_date,
2690 p_remark_id => p_remark_id2,
2691 p_remark_desc => l_remark_desc2
2692 );
2693
2694 -- Insertion values. ghr_mass_actions_pkg.replace_insertion_values
2695 IF l_remark2_ins1 IS NOT NULL
2696 OR l_remark2_ins2 IS NOT NULL
2697 OR l_remark2_ins3 IS NOT NULL
2698 THEN
2699 -- Bug#4256022 Passed the variable l_remark_desc2_out and assigned
2700 -- the value back to l_remark_desc2 to avoid NOCOPY related problems.
2701 ghr_mass_actions_pkg.replace_insertion_values (p_desc => l_remark_desc2,
2702 p_information1 => l_remark2_ins1,
2703 p_information2 => l_remark2_ins2,
2704 p_information3 => l_remark2_ins3,
2705 p_desc_out => l_remark_desc2_out
2706 );
2707 l_remark_desc2 := l_remark_desc2_out;
2708 END IF;
2709 END IF;
2710
2711 p_remark_id1 := l_remark_id1;
2712 p_remark_desc1 := l_remark_desc1;
2713 p_remark1_info1 := l_remark1_ins1;
2714 p_remark1_info2 := l_remark1_ins2;
2715 p_remark1_info3 := l_remark1_ins3;
2716 p_remark2_info1 := l_remark2_ins1;
2717 p_remark_desc2 := l_remark_desc2;
2718 p_remark2_info2 := l_remark2_ins2;
2719 p_remark2_info3 := l_remark2_ins3;
2720 --
2721 --
2722 EXCEPTION
2723 WHEN OTHERS
2724 THEN
2725 p_first_action_la_code1 := l_first_action_la_code1;
2726 p_first_action_la_desc1 := l_first_action_la_desc1;
2727 p_first_action_la_code2 := l_first_action_la_code2;
2728 p_first_action_la_desc2 := l_first_action_la_desc2;
2729 p_remark_id1 := NULL;
2730 p_remark_desc1 := NULL;
2731 p_remark1_info1 := NULL;
2732 p_remark1_info2 := NULL;
2733 p_remark1_info3 := NULL;
2734 p_remark_id2 := NULL;
2735 p_remark_desc2 := NULL;
2736 p_remark2_info1 := NULL;
2737 p_remark2_info2 := NULL;
2738 p_remark2_info3 := NULL;
2739 RAISE;
2740 END derive_legal_auth_cd_remarks;
2741
2742 --
2743 --
2744 --
2745 FUNCTION checkiffwpayplan (
2746 p_from_pay_plan IN ghr_pa_requests.from_pay_plan%TYPE
2747 )
2748 RETURN BOOLEAN
2749 IS
2750 --
2751 -- Local variables
2752 l_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
2753
2754 -- This function checks if the Pay plan is FW equivalent. If its the it returns TRUE otherwise FALSE
2755 --
2756 --
2757 CURSOR csr_chk_fw_pp
2758 IS
2759 SELECT pay_plan
2760 FROM ghr_pay_plans
2761 WHERE pay_plan = p_from_pay_plan AND equivalent_pay_plan = 'FW';
2762 --
2763 BEGIN
2764 --
2765 OPEN csr_chk_fw_pp;
2766 FETCH csr_chk_fw_pp INTO l_from_pay_plan;
2767
2768 IF csr_chk_fw_pp%NOTFOUND
2769 THEN
2770 CLOSE csr_chk_fw_pp;
2771 RETURN FALSE;
2772 ELSE
2773 CLOSE csr_chk_fw_pp;
2774 RETURN TRUE;
2775 END IF;
2776 --
2777 --
2778 END checkiffwpayplan;
2779
2780 --
2781 --
2782 FUNCTION checkpayplanparm (
2783 p_in_pay_plan IN ghr_pa_requests.from_pay_plan%TYPE,
2784 p_from_pay_plan IN ghr_pa_requests.from_pay_plan%TYPE
2785 )
2786 RETURN BOOLEAN
2787 IS
2788 --
2789 --
2790 BEGIN
2791 --
2792 IF p_in_pay_plan IS NOT NULL
2793 THEN
2794 IF p_in_pay_plan = p_from_pay_plan
2795 THEN
2796 RETURN TRUE;
2797 ELSE
2798 RETURN FALSE;
2799 END IF;
2800 ELSE
2801 RETURN TRUE;
2802 END IF;
2803 --
2804 --
2805 END checkpayplanparm;
2806
2807 --
2808 --
2809 FUNCTION checkpoiparm (
2810 p_in_personnel_office_id IN OUT NOCOPY ghr_pa_requests.personnel_office_id%TYPE,
2811 p_position_id IN per_assignments.position_id%TYPE,
2812 p_effective_date IN ghr_pa_requests.effective_date%TYPE
2813 )
2814 RETURN BOOLEAN
2815 IS
2816 --
2817 l_flsa_category ghr_pa_requests.flsa_category%TYPE;
2818 l_bargaining_unit_status ghr_pa_requests.bargaining_unit_status%TYPE;
2819 l_work_schedule ghr_pa_requests.work_schedule%TYPE;
2820 l_work_schedule_desc ghr_pa_requests.work_schedule_desc%TYPE;
2821 l_functional_class ghr_pa_requests.functional_class%TYPE;
2822 l_supervisory_status ghr_pa_requests.supervisory_status%TYPE;
2823 l_position_occupied ghr_pa_requests.position_occupied%TYPE;
2824 l_appropriation_code1 ghr_pa_requests.appropriation_code1%TYPE;
2825 l_appropriation_code2 ghr_pa_requests.appropriation_code2%TYPE;
2826 l_personnel_office_id ghr_pa_requests.personnel_office_id%TYPE;
2827 l_from_office_symbol ghr_pa_requests.from_office_symbol%TYPE;
2828 l_part_time_hours ghr_pa_requests.part_time_hours%TYPE;
2829 l_po_id ghr_pa_requests.personnel_office_id%TYPE;
2830 --
2831 --
2832 BEGIN
2833 --
2834 l_po_id := p_in_personnel_office_id;
2835 IF p_in_personnel_office_id IS NOT NULL
2836 THEN
2837 ghr_pa_requests_pkg.get_sf52_pos_ddf_details (p_position_id => p_position_id,
2838 p_date_effective => p_effective_date,
2839 p_flsa_category => l_flsa_category,
2840 p_bargaining_unit_status => l_bargaining_unit_status,
2841 p_work_schedule => l_work_schedule,
2842 p_functional_class => l_functional_class,
2843 p_supervisory_status => l_supervisory_status,
2844 p_position_occupied => l_position_occupied,
2845 p_appropriation_code1 => l_appropriation_code1,
2846 p_appropriation_code2 => l_appropriation_code2,
2847 p_personnel_office_id => l_personnel_office_id,
2848 p_office_symbol => l_from_office_symbol,
2849 p_part_time_hours => l_part_time_hours
2850 );
2851
2852 IF l_personnel_office_id = p_in_personnel_office_id
2853 THEN
2854 RETURN TRUE;
2855 ELSE
2856 RETURN FALSE;
2857 END IF;
2858 ELSE
2859 ----------------Return PO ID also
2860 ghr_pa_requests_pkg.get_sf52_pos_ddf_details (p_position_id => p_position_id,
2861 p_date_effective => p_effective_date,
2862 p_flsa_category => l_flsa_category,
2863 p_bargaining_unit_status => l_bargaining_unit_status,
2864 p_work_schedule => l_work_schedule,
2865 p_functional_class => l_functional_class,
2866 p_supervisory_status => l_supervisory_status,
2867 p_position_occupied => l_position_occupied,
2868 p_appropriation_code1 => l_appropriation_code1,
2869 p_appropriation_code2 => l_appropriation_code2,
2870 p_personnel_office_id => l_personnel_office_id,
2871 p_office_symbol => l_from_office_symbol,
2872 p_part_time_hours => l_part_time_hours
2873 );
2874 p_in_personnel_office_id := l_personnel_office_id;
2875 ----------------
2876 RETURN TRUE;
2877 END IF;
2878 --
2879 --
2880 EXCEPTION
2881 WHEN OTHERS THEN
2882 p_in_personnel_office_id := l_po_id;
2883 END checkpoiparm;
2884
2885 --
2886 --
2887 FUNCTION checkifmaxpayplan (
2888 p_from_pay_plan IN ghr_pa_requests.from_pay_plan%TYPE,
2889 p_from_step_or_rate IN ghr_pa_requests.from_step_or_rate%TYPE
2890 )
2891 RETURN BOOLEAN
2892 IS
2893 --
2894 -- Local variables
2895 l_from_pay_plan ghr_pa_requests.from_pay_plan%TYPE;
2896 l_maximum_step ghr_pay_plans.maximum_step%TYPE;
2897
2898 --
2899 -- This function checks if the Pay plan is FW equivalent. If its the it returns TRUE otherwise FALSE
2900 --
2901 --
2902 CURSOR csr_chk_max_pp
2903 IS
2904 SELECT maximum_step
2905 FROM ghr_pay_plans
2906 WHERE pay_plan = l_from_pay_plan;
2907 --
2908 BEGIN
2909 --
2910 IF p_from_pay_plan IN ('GM','GR') --Bug# 6603968
2911 THEN
2912 l_from_pay_plan := 'GS';
2913 ELSE
2914 l_from_pay_plan := p_from_pay_plan;
2915 END IF;
2916
2917 OPEN csr_chk_max_pp;
2918 FETCH csr_chk_max_pp INTO l_maximum_step;
2919
2920 IF csr_chk_max_pp%NOTFOUND
2921 THEN
2922 CLOSE csr_chk_max_pp;
2923 RETURN FALSE;
2924 ELSE
2925 IF (TO_NUMBER (p_from_step_or_rate) <= (l_maximum_step - 1))
2926 THEN
2927 CLOSE csr_chk_max_pp;
2928 RETURN TRUE;
2929 END IF;
2930 END IF;
2931
2932 RETURN FALSE;
2933 --
2934 --
2935 END checkifmaxpayplan;
2936 --
2937 --
2938 FUNCTION ret_wgi_pay_date (
2939 p_assignment_id IN per_all_assignments_f.assignment_id%type,
2940 p_effective_date IN per_all_assignments_f.effective_start_date%type,
2941 p_frequency IN NUMBER
2942 )
2943 RETURN VARCHAR2
2944 IS
2945 l_value VARCHAR2(30);
2946 l_multiple_error_flag BOOLEAN;
2947 l_wgi_pay_date DATE;
2948 p_ret_value VARCHAR2(30);
2949 BEGIN
2950 --
2951 l_value := NULL;
2952 p_ret_value := NULL;
2953
2954 BEGIN
2955 ghr_api.retrieve_element_entry_value
2956 (p_element_name => 'Within Grade Increase',
2957 p_input_value_name => 'Pay Date',
2958 p_assignment_id => p_assignment_id,
2959 p_effective_date => p_effective_date,
2960 p_value => l_value,
2961 p_multiple_error_flag => l_multiple_error_flag
2962 );
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 l_value := NULL;
2966 p_ret_value := NULL;
2967 END;
2968
2969 IF l_value IS NOT NULL THEN
2970 l_wgi_pay_date := fnd_date.canonical_to_date (l_value);
2971 IF (p_effective_date >= (l_wgi_pay_date - p_frequency)) THEN
2972 p_ret_value := l_value;
2973 END IF;
2974 END IF;
2975
2976 RETURN p_ret_value;
2977
2978 END ret_wgi_pay_date;
2979
2980 END ghr_wgi_pkg;