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