[Home] [Help]
PACKAGE BODY: APPS.GHR_GHRWS52L
Source
1 package body GHR_GHRWS52L AS
2 /* $Header: ghrws52l.pkb 120.13.12000000.3 2007/03/05 05:14:15 vmididho ship $ */
3
4
5
6
7 procedure GHRWS52L
8 (
9 p_pa_request_rec in ghr_pa_requests%rowtype
10 ,p_per_group1 in ghr_api.per_group1_type
11 ,p_per_retained_grade in ghr_api.per_retained_grade_type
12 ,p_per_sep_retire in ghr_api.per_sep_retire_type
13 ,p_per_conversions in ghr_api.per_conversions_type
14 ,p_per_uniformed_services in ghr_api.per_uniformed_services_type
15 ,p_pos_grp1 in ghr_api.pos_grp1_type
16 ,p_pos_valid_grade in ghr_api.pos_valid_grade_type
17 ,p_loc_info in ghr_api.loc_info_type
18 ,p_sf52_from_data in ghr_api.prior_sf52_data_type
19 ,p_personal_info in ghr_api.personal_info_type
20 ,p_agency_code in varchar2
21 ,p_gov_awards_type in ghr_api.government_awards_type
22 ,p_perf_appraisal_type in ghr_api.performance_appraisal_type
23 ,p_health_plan in varchar2
24 ,p_race_ethnic_info in ghr_api.per_race_ethnic_type -- Bug 4724337 Race or National Origin changes
25 )
26 is
27 --
28 l_proc varchar2(61) := 'GHR_GHRWS52L.GHRWS52';
29 l_academic_discipline ghr_pa_requests.ACADEMIC_DISCIPLINE%Type;
30 l_Adj_Base_Pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
31 l_Agency_Code varchar2(30);
32 -- l_Agency varchar2(30);
33 l_agency_subelement varchar2(30);
34 l_as_of_date date;
35 l_Benefit_Amount ghr_pa_request_extra_info.rei_information7%type;
36 l_bargaining_unit_status_code ghr_pa_requests.BARGAINING_UNIT_STATUS%type;
37 l_citizenship ghr_pa_requests.CITIZENSHIP%type;
38 l_credit_mil_svc per_people_extra_info.pei_information5%type;
39 l_Cur_Appt_Auth_1 per_people_extra_info.pei_information8%type;
40 l_Cur_Appt_Auth_2 per_people_extra_info.pei_information9%type;
41 l_Duty_Station_ID hr_location_extra_info.lei_information3%type;
42 l_Duty_Station_Lookup_Code ghr_duty_stations_f.Duty_Station_Code%type;
43 l_education_level ghr_pa_requests.EDUCATION_LEVEL%type;
44 l_effective_date ghr_pa_requests.EFFECTIVE_DATE%type;
45 l_Employee_Date_of_Birth per_people_f.date_of_birth%type;
46 l_Employee_First_Name ghr_pa_requests.EMPLOYEE_FIRST_NAME%type;
47 l_Employee_Last_Name ghr_pa_requests.EMPLOYEE_LAST_NAME%type;
48 l_employee_National_ID per_people_f.national_identifier%type;
49 l_fegli_code ghr_pa_requests.FEGLI%type;
50 l_fers_coverage per_people_extra_info.pei_information3%type;
51 l_First_Action_NOA_LA_Code1 ghr_pa_requests.FIRST_ACTION_LA_CODE1%type;
52 l_First_Action_NOA_LA_Code2 ghr_pa_requests.FIRST_ACTION_LA_CODE2%type;
53 l_First_NOAC_Lookup_Code ghr_pa_requests.FIRST_NOA_CODE%type;
54 -- Bug#4486823 RRR Changes
55 l_First_NOAC_Lookup_Desc ghr_pa_requests.FIRST_NOA_DESC%type;
56 l_flsa_category ghr_pa_requests.FLSA_CATEGORY%type;
57 l_functional_class ghr_pa_requests.FUNCTIONAL_CLASS%type;
58 l_health_plan varchar2(30);
59 l_Handicap per_people_extra_info.pei_information11%type;
60 l_Indiv_Award ghr_pa_request_extra_info.rei_information6%type;
61 --l_locality_pay_area hr_location_extra_info.lei_information4%type;
62 l_locality_pay_area ghr_locality_pay_areas_f.Locality_Pay_Area_Code%type;
63 l_Occupation_code ghr_pa_requests.to_occ_code%type;
64 -- l_Occupation_code per_position_extra_info.poei_information6%type;
65 l_One_Time_Payment_Amount ghr_pa_requests.AWARD_AMOUNT%type;
66 l_Organ_Component per_position_extra_info.poei_information5%type;
67
68 l_pay_rate_determinant_code ghr_pa_requests.PAY_RATE_DETERMINANT%type;
69 l_Personnel_Officer_ID per_position_extra_info.poei_information3%type;
70 l_Position_Occ_Code ghr_pa_requests.POSITION_OCCUPIED%type;
71 l_Prior_Basic_Pay varchar2(30); -- ghr_pa_requests.FROM_BASIC_PAY%type;
72 -- l_prior_location_id hr_locations.location_id%type
73 l_prior_duty_station ghr_duty_stations_f.Duty_Station_Code%type;
74 l_Prior_Grade_Or_Level per_grade_definitions.segment2%type;
75 l_Prior_Locality_Adj varchar2(30); --ghr_pa_requests.FROM_LOCALITY_ADJ%type;
76 l_prior_locality_pay_area ghr_locality_pay_areas_f.Locality_Pay_Area_Code%type;
77 l_Prior_Occupation_code per_job_definitions.segment1%type;
78 l_Prior_Pay_Basis per_position_extra_info.poei_information6%type;
79 l_Prior_Pay_Plan per_grade_definitions.segment1%type;
80 l_Prior_Pay_Rate_Det_Code per_assignment_extra_info.aei_information6%type;
81 l_Prior_Step_Or_Rate per_assignment_extra_info.aei_information3%type;
82 l_prior_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
83 l_Production_Date date;
84 l_Race_National_Region per_people_extra_info.pei_information5%type;
85 l_rating_of_record_level ghr_pa_request_extra_info.rei_information5%type;
86 l_rating_of_record_pattern ghr_pa_request_extra_info.rei_information4%type;
87 l_rating_of_record_period ghr_pa_request_extra_info.rei_information6%type;
88 l_rating_of_record_per_starts ghr_pa_request_extra_info.rei_information19%type; --4753117
89 l_retain_grade per_people_extra_info.pei_information3%type;
90 l_retain_pay_plan per_people_extra_info.pei_information5%type;
91 l_retain_step per_people_extra_info.pei_information4%type;
92 l_temp_step per_people_extra_info.pei_information9%type;
93 l_Retirement_Plan_Code ghr_pa_requests.RETIREMENT_PLAN%type;
94 l_Retention_Allowance varchar2(30); -- ghr_pa_requests.TO_RETENTION_ALLOWANCE%type; --number(15,2)
95 l_Second_NOAC_Lookup_code ghr_pa_requests.SECOND_NOA_CODE%type;
96 l_Service_Computation_Date ghr_pa_requests.SERVICE_COMP_DATE%type;
97 l_Sex per_people_f.sex%type;
98 l_special_pay_table_id per_position_extra_info.poei_information5%type;
99 l_staffing_differential varchar2(30); -- ghr_pa_requests.TO_STAFFING_DIFFERENTIAL%type; --number(15,2)
100 l_submission_date date;
101 l_supervisory_differential varchar2(30); -- ghr_pa_requests.TO_SUPERVISORY_DIFFERENTIAL%type; --number(15,2)
102 l_Supervisory_Status_Code ghr_pa_requests.SUPERVISORY_STATUS%type;
103 l_Tenure_Group_Code ghr_pa_requests.TENURE%type;
104 l_To_Basic_Pay varchar2(30); -- ghr_pa_requests.TO_BASIC_PAY%type;
105 l_To_Grade_Or_Level ghr_pa_requests.TO_GRADE_OR_LEVEL%type;
106 l_To_Locality_Adj varchar2(30); -- ghr_pa_requests.TO_LOCALITY_ADJ%type;
107 l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
108 l_To_Pay_Plan ghr_pa_requests.TO_PAY_PLAN%type;
109 l_To_Pay_Status varchar2(30);
110 l_To_Position_ID ghr_pa_requests.TO_Position_ID%type;
111 l_To_Step_Or_Rate ghr_pa_requests.TO_STEP_OR_RATE%type;
112 l_rpa_Step_Or_Rate ghr_pa_requests.TO_STEP_OR_RATE%type;
113 l_Veterans_Preference_Code ghr_pa_requests.VETERANS_PREFERENCE%type;
114 l_Veterans_Status_Code ghr_pa_requests.VETERANS_STATUS%type;
115 l_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
116 l_year_degree_attained ghr_pa_requests.YEAR_DEGREE_ATTAINED%type;
117 l_prior_effective_date ghr_pa_requests.effective_date%type;
118 l_prior_loc_adj_effective_date ghr_pa_requests.effective_date%type;
119 l_session ghr_history_api.g_session_var_type;
120 l_element_entry_value_id pay_element_entry_values_f.element_entry_value_id%type;
121 l_prior_ds_effective_date ghr_pa_requests.effective_date%type;
122 ---Bug 5855843
123 l_prior_ds_effective_date_flag BOOLEAN;
124 ---Bug 5855843
125 l_org_structure_id per_position_extra_info.poei_information5%type;
126 l_update34_date date;
127 l_race_ethnic_info varchar2(30); -- Bug 4724337 Race or National Origin changes
128
129
130
131 CURSOR cur_dutystation IS
132 select dst.duty_station_code
133 , lpa.locality_pay_area_code
134 from ghr_duty_stations_f dst
135 , ghr_locality_pay_areas_f lpa
136 where dst.LOCALITY_PAY_AREA_ID = lpa.LOCALITY_PAY_AREA_ID
137 and dst.DUTY_STATION_ID = p_loc_info.duty_station_id
138 and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
139 between dst.effective_start_date and dst.effective_end_date
140 and nvl(p_pa_request_rec.effective_date,trunc(sysdate))
141 between lpa.effective_start_date and lpa.effective_end_date;
142
143 -----Bug 5855843 Start
144 CURSOR cur_ds_prior_date IS
145 select effective_date
146 from ghr_pa_requests
147 where pa_notification_id is not null
148 and person_id = p_pa_request_rec.person_id
149 and effective_date <= p_pa_request_rec.effective_date
150 order by effective_date desc;
151 -----Bug 5855843 end
152
153 CURSOR cur_prior_dutystation IS
154 select dst.duty_station_code
155 , lpa.locality_pay_area_code
156 from ghr_duty_stations_f dst
157 , ghr_locality_pay_areas_f lpa
158 , hr_locations loc
159 , hr_location_extra_info lei
160 where
161 loc.location_id = p_sf52_from_data.duty_station_location_id
162 and lei.location_id = loc.location_id
163 and lei.information_type = 'GHR_US_LOC_INFORMATION'
164 and dst.duty_station_id = lei.lei_information3
165 and lpa.LOCALITY_PAY_AREA_ID = dst.LOCALITY_PAY_AREA_ID
166 and nvl(l_prior_ds_effective_date,trunc(sysdate))
167 between dst.effective_start_date and dst.effective_end_date
168 and nvl(l_prior_ds_effective_date,trunc(sysdate))
169 between lpa.effective_start_date and lpa.effective_end_date;
170
171
172 -- cursor added by skutteti to fetch the prior effective date, used for prior salary checks.
173 -------------------------------------------------------------------------------
174 --
175 -- Cursor modified for Payroll Integration
176 --
177 Cursor c_eev_id (p_element_name varchar2,
178 p_input_value_name varchar2,
179 p_bg_id NUMBER) is
180 SELECT eev.element_entry_value_id,
181 eev.effective_start_date
182 FROM pay_element_types_f elt,
183 pay_input_values_f ipv,
184 pay_element_links_f eli,
185 pay_element_entries_f ele,
186 pay_element_entry_values_f eev
187 WHERE trunc(p_pa_request_rec.effective_date) between elt.effective_start_date
188 and elt.effective_end_date
189 and trunc(p_pa_request_rec.effective_date) between ipv.effective_start_date
190 and ipv.effective_end_date
191 and trunc(p_pa_request_rec.effective_date) between eli.effective_start_date
192 and eli.effective_end_date
193 and trunc(p_pa_request_rec.effective_date) between ele.effective_start_date
194 and ele.effective_end_date
195 and trunc(p_pa_request_rec.effective_date) between eev.effective_start_date
196 and eev.effective_end_date
197 and elt.element_type_id = ipv.element_type_id
198 --and elt.element_type_id = eli.element_type_id + 0 --commented for bug 5208846
199 and elt.element_type_id = eli.element_type_id
200 -- and upper(elt.element_name) = upper('Basic Salary Rate')
201 and upper(elt.element_name) = upper(p_element_name)
202 and ipv.input_value_id = eev.input_value_id
203 and ele.assignment_id = p_pa_request_rec.employee_assignment_id
204 -- and ele.element_entry_id + 0 = eev.element_entry_id --Commented for bug 5208846
205 and ele.element_entry_id = eev.element_entry_id
206 -- and upper(ipv.name) = upper('Salary');
207 and upper(ipv.name) = upper(p_input_value_name)
208 -- and NVL(elt.business_group_id,0)=NVL(ipv.business_group_id,0)
209 and (elt.business_group_id is NULL or elt.business_group_id=p_bg_id);
210
211 -- Bug 2589851
212 -- Changed the c_prior_date cursor to filter out the subsequent actions
213 -- processed after the effective date
214 -- Bug 2897202
215 -- condition (pa_request_id < p_pa_request_rec.altered_pa_request_id) removed.
216 -- Eliminate the original and correction actions.
217
218 -- Bug#3278827 Peformance Issue.
219 -- Modified the query. Changed the table from ghr_element_entry_values_h_v to ghr_pa_hisotry
220
221 CURSOR c_prior_date (p_element_entry_value_id varchar2) is
222 SELECT FND_DATE.CANONICAL_TO_DATE(INFORMATION2) effective_start_date
223 FROM ghr_pa_history -- ghr_element_entry_values_h_v
224 WHERE TABLE_NAME = 'PAY_ELEMENT_ENTRY_VALUES_F'
225 AND INFORMATION1 = p_element_entry_value_id -- information1 holds element_entry_value_id
226 AND effective_date <= l_effective_date
227 AND pa_request_id not in
228 (SELECT pa_request_id
229 FROM ghr_pa_requests par
230 WHERE ((par.first_noa_id = l_session.noa_id_correct)
231 OR
232 (par.first_noa_code = '002' and par.second_noa_id = l_session.noa_id_correct))
233 CONNECT BY par.pa_request_id = prior par.altered_pa_request_id
234 START WITH par.pa_request_id = p_pa_request_rec.altered_pa_request_id )
235 ORDER BY 1 desc;
236 --
237 -- Cursor to get prior effective date for fetching the from duty station
238 -- Bug#3278827 Peformance Issue.
239 -- Modified the query. Changed the table from ghr_assignments_h_v to ghr_pa_hisotry
240
241 CURSOR c_prior_ds_date (p_assignment_id in number) is
242 SELECT FND_DATE.CANONICAL_TO_DATE(INFORMATION2) effective_start_date
243 FROM ghr_pa_history -- ghr_assignments_h_v
244 WHERE TABLE_NAME = 'PER_ASSIGNMENTS_F'
245 AND INFORMATION1 = to_Char(p_assignment_id) -- information1 holds assignment_id
246 AND pa_request_id not in
247 (SELECT pa_request_id
248 FROM ghr_pa_history his
249 CONNECT BY his.pa_request_id = prior his.altered_pa_request_id
250 START WITH his.pa_request_id = p_pa_request_rec.altered_pa_request_id )
251 ORDER BY 1 desc;
252 -- New cursor for non correction actions
253 cursor c_prior_ds_date_non_correct(p_assignment_id in number) is
254 select effective_start_date
255 from per_all_assignments_f
256 where assignment_id = p_assignment_id
257 and p_pa_request_rec.effective_date between
258 effective_start_date and nvl(effective_end_date,trunc(sysdate))
259 order by effective_start_date desc;
260
261 -- cursor added by skutteti to identify whether the user entered retained pay table is a special table.
262 --
263 CURSOR c_special_pay_table (pay_table_id varchar2) is
264 SELECT user_table_id
265 FROM pay_user_tables
266 WHERE user_table_id = pay_table_id
267 AND upper(user_table_name) like upper('%SPECIAL%RATE%');
268 -- cursor added by vravikan for converting basic pay for pay plans having equivalent pay plan as 'FW'
269 -- Bug# 963123
270 cursor c_fw_pay_plans( p_pay_plan varchar2) is
271 SELECT 'X'
272 FROM ghr_pay_plans
273 WHERE equivalent_pay_plan = 'FW'
274 AND pay_plan = p_pay_plan;
275 cursor c_740_rei is
276 select count(rei_information3) rei_count
277 from ghr_pa_request_extra_info
278 where pa_request_id = p_pa_request_rec.pa_request_id
279 and information_type = 'GHR_US_PAR_TERM_RG_POSN_CHG'
280 and rei_information5 = 'Y'
281 and nvl(rei_information30,hr_api.g_varchar2) <> 'Original RPA';
282
283 Cursor c_rg_pei is
284 SELECT count(person_extra_info_id) pei_count
285 FROM per_people_extra_info pei
286 WHERE pei.person_id = p_pa_request_rec.person_id
287 AND pei.information_type = 'GHR_US_RETAINED_GRADE'
288 AND l_effective_date
289 BETWEEN NVL(fnd_date.canonical_to_date(pei.pei_information1) ,l_effective_date)
290 AND NVL(fnd_date.canonical_to_date(pei.pei_information2) ,l_effective_date);
291
292 ------------------ cursor created to handle Null Org Struct id for MRE Correction
293
294 Cursor c_pei_null_OPM(p_position_id number) is
295 select poei_information5 l_org_structure_id
296 from per_position_extra_info
297 where information_type='GHR_US_POS_GRP1' and position_id=p_position_id;
298
299 ----------------------------------- cursor to handle changes to LAC codes for Correction to Apptmt action 1274541
300 Cursor c_Corr_LAC_Codes(p_pa_request_id number) is
301 select second_action_la_code1,second_action_la_code2,first_noa_code,second_noa_code
302 from ghr_pa_requests
303 where pa_request_id=p_pa_request_id and first_noa_code='002';
304
305
306 Cursor fam_code(p_second_noa_id number) is
307 select noa_family_code from ghr_noa_families
308 where nature_of_action_id=p_second_noa_id and noa_family_code='APP'
309 AND
310 nature_of_action_id not in (select nature_of_action_id from ghr_noa_families
311 where noa_family_code='APPT_TRANS');
312
313 CURSOR cur_temp_step
314 IS
315 SELECT rei_information3 temp_step
316 FROM ghr_pa_request_extra_info
317 WHERE pa_request_id = p_pa_request_rec.pa_request_id
318 AND information_type = 'GHR_US_PAR_RG_TEMP_PROMO';
319
320
321 ---------------------------------------------------------- added 2 cursors for 1274541
322 l_first_noa_code ghr_pa_requests.first_noa_code%type;
323 l_second_noa_code ghr_pa_requests.second_noa_code%type;
324 l_fam_code ghr_pa_requests.noa_family_code%type;
325
326 l_pei_count number;
327 l_rei_count number;
328 -- Payroll Integration
329 -- Pick BG id
330 Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
331 Select distinct business_group_id bg
332 from per_assignments_f
333 where assignment_id = p_assignment_id
334 and p_eff_date between effective_start_Date
335 and effective_end_date;
336
337 ll_bg_id NUMBER;
338 ll_pay_basis VARCHAR2(80);
339 ll_effective_date DATE;
340 l_new_element_name VARCHAR2(80);
341 -- for bug 3191704
342 --
343 CURSOR cur_rei_poi(p_par_id in NUMBER)
344 IS
345 SELECT rei_information5
346 FROM ghr_pa_request_extra_info
347 WHERE pa_request_id=p_par_id
348 AND information_type='GHR_US_PAR_REALIGNMENT';
349
350 target_poi ghr_pa_requests.personnel_office_id%type;
351 --
352 -- for bug 3191704
353
354 --
355 Begin
356 -- Initialize the global variables
357 g_temp_step := NULL;
358 g_fw_annualize := NULL;
359 if g_bypass_cpdf <> TRUE then
360 --
361 hr_utility.set_location('entering'||l_proc,10);
362 --
363 -- Initialization
364 -- Pick the business group id and also pay basis for later use
365 ll_effective_date := p_pa_request_rec.effective_date;
366 For BG_rec in Cur_BG(p_pa_request_rec.employee_assignment_id,ll_effective_date)
367 Loop
368 ll_bg_id:=BG_rec.bg;
369 End Loop;
370
371 -- Picking the Pay basis from the RPA
372
373 If (p_pa_request_rec.from_pay_basis is NULL and
374 p_pa_request_rec.to_pay_basis is not NULL) then
375 ll_pay_basis:=p_pa_request_rec.to_pay_basis;
376
377 elsif (p_pa_request_rec.from_pay_basis is NOT NULL and
378 p_pa_request_rec.to_pay_basis is NULL) then
379 ll_pay_basis:=p_pa_request_rec.from_pay_basis;
380
381 elsif (p_pa_request_rec.from_pay_basis is NOT NULL and
382 p_pa_request_rec.to_pay_basis is NOT NULL) then
383 ll_pay_basis:=p_pa_request_rec.to_pay_basis;
384
385 elsif (p_pa_request_rec.from_pay_basis is NULL and
386 p_pa_request_rec.to_pay_basis is NULL) then
387 ll_pay_basis:='PA';
388 End If;
389 --
390 l_academic_discipline :=p_pa_request_rec.ACADEMIC_DISCIPLINE;
391 l_Adj_Base_Pay :=p_pa_request_rec.TO_ADJ_BASIC_PAY;
392 l_agency_subelement := p_agency_code;
393 l_Agency_Code := substr(l_agency_subelement,1,2);
394 l_as_of_date :=Null;
395 l_Benefit_Amount :=p_gov_awards_type.tangible_benefit_dollars;
396 l_bargaining_unit_status_code :=p_pa_request_rec.BARGAINING_UNIT_STATUS;
397 l_citizenship :=p_pa_request_rec.CITIZENSHIP;
398 l_credit_mil_svc :=p_per_uniformed_services.creditable_military_service;
399 -------------------------------------------------------------------------------------- code added for 1274541
400 FOR corr_lac IN c_Corr_LAC_Codes(p_pa_request_rec.pa_request_id) LOOP
401 l_first_noa_code := corr_lac.first_noa_code;
402 l_second_noa_code:= corr_lac.second_noa_code;
403 END LOOP;
404 IF l_first_noa_code = '002' then
405 If l_second_noa_code = p_pa_request_rec.first_noa_code then
406 FOR fam_code_rec IN fam_code(p_pa_request_rec.first_noa_id)
407 LOOP
408 l_fam_code := fam_code_rec.noa_family_code;
409 END LOOP;
410 end if;
411 end if;
412
413
414 IF (l_first_noa_code='002' AND l_fam_code='APP') THEN
415 FOR corr_lac_rec in c_Corr_LAC_Codes(p_pa_request_rec.pa_request_id)
416 LOOP
417
418 l_Cur_Appt_Auth_1 :=corr_lac_rec.second_action_la_code1;
419 l_Cur_Appt_Auth_2 :=corr_lac_rec.second_action_la_code2;
420
421 END LOOP;
422
423 ELSE
424 l_Cur_Appt_Auth_1 :=p_per_group1.org_appointment_auth_code1;
425 l_Cur_Appt_Auth_2 :=p_per_group1.org_appointment_auth_code2;
426 END IF;
427
428 ---------------------------------------------------------------------------------- code added for 1274541
429
430 --
431 hr_utility.set_location('Before dutystation cur rec '|| l_proc, 20);
432 --
433 FOR cur_dutystation_rec IN cur_dutystation LOOP
434 l_Duty_Station_Lookup_Code := cur_dutystation_rec.duty_station_code;
435 l_locality_pay_area := cur_dutystation_rec.locality_pay_area_code;
436 END LOOP;
437
438 l_education_level :=p_pa_request_rec.EDUCATION_LEVEL;
439 l_effective_date :=p_pa_request_rec.EFFECTIVE_DATE;
440 l_Employee_Date_of_Birth :=p_personal_info.p_date_of_birth;
441 l_Employee_First_Name :=p_pa_request_rec.EMPLOYEE_FIRST_NAME;
442 l_Employee_Last_Name :=p_pa_request_rec.EMPLOYEE_LAST_NAME;
443 l_employee_National_ID :=p_personal_info.p_national_identifier;
444 l_fegli_code :=p_pa_request_rec.FEGLI;
445 l_fers_coverage :=p_per_sep_retire.fers_coverage;
446 l_First_Action_NOA_LA_Code1 :=p_pa_request_rec.FIRST_ACTION_LA_CODE1;
447 l_First_Action_NOA_LA_Code2 :=p_pa_request_rec.FIRST_ACTION_LA_CODE2;
448
449 l_First_NOAC_Lookup_Code :=p_pa_request_rec.FIRST_NOA_CODE;
450 -- Bug#4486823 RRR Changes.
451 l_First_NOAC_Lookup_desc :=p_pa_request_rec.FIRST_NOA_DESC;
452 l_flsa_category :=p_pa_request_rec.FLSA_CATEGORY;
453 l_functional_class :=p_pa_request_rec.FUNCTIONAL_CLASS;
454 l_health_plan :=p_health_plan;
455 l_Handicap :=p_per_group1.handicap_code;
456 l_Indiv_Award :=p_gov_awards_type.group_award;
457 l_One_Time_Payment_Amount :=p_pa_request_rec.AWARD_AMOUNT;
458 l_Organ_Component :=p_pos_grp1.organization_structure_id;
459
460 l_Position_Occ_Code :=p_pa_request_rec.POSITION_OCCUPIED;
461
462 -------------------------- 2623692 for MRE correction
463 if( p_pos_grp1.organization_structure_id is null) then
464
465 FOR OPM_CUR IN c_pei_null_OPM(p_pa_request_rec.from_position_id) LOOP
466 l_Organ_Component :=OPM_CUR.l_org_structure_id;
467 END LOOP;
468 else
469 l_Organ_Component :=p_pos_grp1.organization_structure_id;
470 end if;
471
472
473 if (p_pa_request_rec.FIRST_NOA_CODE='790') then
474
475 --l_Personnel_Officer_ID :=p_pa_request_rec.personnel_office_id;
476 -- for bug 3191704
477 FOR poi_rec IN cur_rei_poi(p_pa_request_rec.pa_request_id)
478 LOOP
479 target_poi := poi_rec.rei_information5;
480 END LOOP;
481 --
482 IF target_poi IS NOT NULL THEN
483 l_Personnel_Officer_ID := target_poi;
484 ELSE
485 l_Personnel_Officer_ID := p_pa_request_rec.personnel_office_id;
486 END IF;
487 -- IF target POI is not null check
488 else
489
490 l_Personnel_Officer_ID :=p_pos_grp1.personnel_office_id;
491 end if;
492
493
494 -------- bug#2623692
495 --
496 -- fetch the prior date for the locality adj element
497 -- this is used for the prior locality adj edits
498
499 -- Payroll Integration
500 -- New element name is picked for Adj Basic pay
501 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name(
502 p_fedhr_element_name => 'Adjusted Basic Pay',
503 p_business_group_id => ll_bg_id,
504 p_effective_date => ll_effective_date,
505 p_pay_basis => NULL);
506
507 -- Processing Adjusted basic pay
508 -- NAME DATE BUG COMMENTS
509 -- Ashley 17-JUL-03 Payroll Intg Modified the Input Value name
510 -- Changes from Adjusted Pay -> Amount
511
512 for eev_id in c_eev_id (p_element_name => l_new_element_name,
513 p_input_value_name => 'Amount',
514 p_bg_id => ll_bg_id)
515 loop
516 l_prior_loc_adj_effective_date := eev_id.effective_start_date;
517 l_element_entry_value_id := eev_id.element_entry_value_id;
518 end loop;
519 if l_session.noa_id_correct is not null then -- for correction
520 for prior_date in c_prior_date (p_element_entry_value_id => l_element_entry_value_id )
521 loop
522 l_prior_loc_adj_effective_date := prior_date.effective_start_date;
523 exit;
524 end loop;
525 end if;
526 --
527 hr_utility.set_location('Before dutystation prior rec '|| l_proc, 30);
528 --
529 ghr_history_api.get_g_session_var(l_session);
530 If l_session.noa_id_correct is not null then
531 For prior_ds_date_rec in c_prior_ds_date(p_pa_request_rec.employee_assignment_id) loop
532 l_prior_ds_effective_date := prior_ds_date_rec.effective_start_date;
533 exit;
534 End loop;
535 else
536 -- Start Bug 1676026
537 -- Prior Locality Adjustment effective date is used rather than using the current
538 -- Assignment effective start date since current assignment effective start date
539 -- fetching wrong locality pay area
540 /*
541 For prior_ds_date_rec in c_prior_ds_date_non_correct(p_pa_request_rec.employee_assignment_id) loop
542 l_prior_ds_effective_date := prior_ds_date_rec.effective_start_date;
543 exit;
544 End loop;
545 */
546 l_prior_ds_effective_date := l_prior_loc_adj_effective_date;
547 -- End Bug 1676026
548
549 end if;
550 hr_utility.set_location('prior ds effective date is '||to_char(l_prior_ds_effective_date,'dd-mon-yyyy')||l_proc, 30);
551
552 l_prior_ds_effective_date_flag := FALSE;
553 hr_utility.set_location('l_prior_ds_effective_date_flag is FALSE' || l_proc,32);
554 FOR cur_prior_dutystation_rec IN cur_prior_dutystation LOOP
555 l_prior_ds_effective_date_flag := TRUE;
556 hr_utility.set_location('l_prior_ds_effective_date_flag is TRUE' || l_proc,34);
557 l_prior_duty_station := cur_prior_dutystation_rec.duty_station_code;
558 l_prior_locality_pay_area := cur_prior_dutystation_rec.locality_pay_area_code;
559 END LOOP;
560
561 ---Bug 5855843 End
562 if not l_prior_ds_effective_date_flag then
563 FOR cur_ds_prior_date_rec IN cur_ds_prior_date LOOP
564 l_prior_ds_effective_date := cur_ds_prior_date_rec.effective_date;
565 hr_utility.set_location('l_prior_ds_effective_date ' || to_char(l_prior_ds_effective_date,'dd-mon-yy') || l_proc,36);
566 exit;
567 END LOOP;
568 FOR cur_prior_dutystation_rec IN cur_prior_dutystation LOOP
569 l_prior_duty_station := cur_prior_dutystation_rec.duty_station_code;
570 l_prior_locality_pay_area := cur_prior_dutystation_rec.locality_pay_area_code;
571 hr_utility.set_location('l_prior_ds_code ' || l_prior_duty_station || l_proc,38);
572 END LOOP;
573
574 l_prior_ds_effective_date := l_prior_loc_adj_effective_date;
575 end if;
576 ---Bug 5855843 End
577 l_Prior_Basic_Pay :=to_char(p_sf52_from_data.basic_pay);
578 l_Prior_Grade_Or_Level :=p_sf52_from_data.grade_or_level;
579 l_Prior_Locality_Adj :=to_char(p_sf52_from_data.locality_adj);
580 l_Prior_Occupation_code :=p_sf52_from_data.occ_code;
581 l_Prior_Pay_Basis :=p_sf52_from_data.pay_basis;
582 l_Prior_Pay_Plan :=p_sf52_from_data.pay_plan;
583 l_prior_work_schedule_code :=p_sf52_from_data.WORK_SCHEDULE;
584 l_Production_Date :=Null;
585 l_Race_National_Region :=p_per_group1.race_national_origin;
586 l_rating_of_record_level :=p_perf_appraisal_type.rating_rec_level;
587 l_rating_of_record_pattern :=p_perf_appraisal_type.rating_rec_pattern;
588 l_rating_of_record_period :=p_perf_appraisal_type.date_appr_ends;
589 --Bug# 4753117 28-Feb-07 Veeramani adding Appraisal start date
590 l_rating_of_record_per_starts :=p_perf_appraisal_type.date_appr_starts;
591 -- End of Bug#4753117
592
593 for rei_rec in c_740_rei loop
594 l_rei_count := rei_rec.rei_count;
595 end loop;
596 for pei_rec in c_rg_pei loop
597 l_pei_count := pei_rec.pei_count;
598 end loop;
599 hr_utility.set_location('l_rei_count is '||l_rei_count,1);
600 hr_utility.set_location('l_pei_count is '||l_pei_count,2);
601 IF p_pa_request_rec.first_noa_code in ('703','866') THEN
602 FOR cur_temp_step_rec IN cur_temp_step LOOP
603 l_temp_step := cur_temp_step_rec.temp_step;
604 END LOOP;
605 hr_utility.set_location('l_temp_step value is '|| l_temp_step, 31);
606 ELSIF p_per_retained_grade.temp_step is not null THEN
607 If l_first_noac_lookup_code in ('867','892','893') then
608 If l_session.noa_id_correct is null then
609 l_temp_step := p_per_retained_grade.temp_step + 1;
610 IF l_temp_step < 10 then
611 l_temp_step := '0'||trim(l_temp_step);
612 END IF;
613 Else
614 l_temp_step := p_per_retained_grade.temp_step;
615 End if;
616 Else
617 l_temp_step := p_per_retained_grade.temp_step;
618 End if;
619 END IF;
620 hr_utility.set_location('l_temp_step value is '|| l_temp_step, 32);
621 ghr_history_api.get_g_session_var(l_session);
622 IF l_temp_step is NULL then
623 l_Prior_Pay_Rate_Det_Code :=p_sf52_from_data.pay_rate_determinant;
624 l_Prior_Step_Or_Rate :=p_sf52_from_data.step_or_rate;
625 ELSE
626 IF l_first_noac_lookup_code in ('703') THEN
627 l_Prior_Pay_Rate_Det_Code :=p_sf52_from_data.pay_rate_determinant;
628 l_Prior_Step_Or_Rate :=p_sf52_from_data.step_or_rate;
629 ELSE
630 -- 4701896. To Bypass edit 570.50.2.
631 -- Bug# 5195807 NVL condition added
632 l_Prior_Pay_Rate_Det_Code := NVL(p_pa_request_rec.input_pay_rate_determinant,p_sf52_from_data.pay_rate_determinant);
633 /* for special_pay_table in
634 c_special_pay_table (p_pos_valid_grade.pay_table_id)loop
635 l_Prior_Pay_Rate_Det_Code := '6';
636 end loop; */
637 If l_first_noac_lookup_code in ('867','892','893') then
638 -- Bug 3021003 Commented the code as both normal and correction
639 -- actions should have the same step.
640 -- If l_session.noa_id_correct is null then
641 -- Bug#4716290 For A,B,E,F,U,V prior prd should be "0".
642 IF l_prior_pay_rate_det_code IN ('A', 'B', 'E', 'F', 'U', 'V') THEN
643 l_Prior_Step_Or_Rate := '00';
644 ELSE
645 l_Prior_Step_Or_Rate := to_number(l_temp_step) - 1;
646 END IF;
647 -- Else
648 -- l_Prior_Step_Or_Rate := l_temp_step;
649 ELSE
650 -- 4701896. To Bypass edit 580.19.2.
651 IF l_prior_pay_rate_det_code IN ('A', 'B', 'E', 'F', 'U', 'V') THEN
652 l_Prior_Step_Or_Rate := '00';
653 ELSE
654 l_Prior_Step_Or_Rate := l_temp_step;
655 END IF;
656 End if;
657 END IF;
658 END IF;
659 hr_utility.set_location('l_temp_step value is '|| l_temp_step, 33);
660 IF l_temp_step is NULL then
661 IF p_pa_request_rec.first_noa_code = '702' and
662 p_pa_request_rec.pay_rate_determinant not in
663 ('A','B','E','F','M','U','V') THEN
664 l_retain_grade := null;
665 ELSIF p_pa_request_rec.first_noa_code = '740' and
666 p_pa_request_rec.pay_rate_determinant not in
667 ('A','B','E','F','M','U','V') and
668 l_rei_count = l_pei_count THEN
669 hr_utility.set_location('l_rei_count is '||l_rei_count,3);
670 hr_utility.set_location('l_pei_count is '||l_pei_count,4);
671 l_retain_grade := null;
672 ELSE
673 l_retain_grade :=p_per_retained_grade.retain_grade;
674 END IF;
675 l_retain_pay_plan :=p_per_retained_grade.retain_pay_plan;
676
677 hr_utility.set_location('RG temp step value is '|| p_per_retained_grade.temp_step, 30);
678 hr_utility.set_location('RG step value is '|| p_per_retained_grade.retain_step_or_rate, 30);
679 ghr_history_api.get_g_session_var(l_session);
680 If l_first_noac_lookup_code in ('867','892','893') then
681 If l_session.noa_id_correct is null then
682 if p_per_retained_grade.retain_step_or_rate <> '10' then
683 l_retain_step := '0' ||(p_per_retained_grade.retain_step_or_rate + 1 );
684 else
685 l_retain_step := p_per_retained_grade.retain_step_or_rate + 1;
686 end if;
687 Else
688 l_retain_step :=p_per_retained_grade.retain_step_or_rate;
689 End if;
690 Else
691 l_retain_step :=p_per_retained_grade.retain_step_or_rate;
692 End if;
693 ELSE
694 l_retain_grade := null;
695 End if;
696
697 hr_utility.set_location('retain step value is '|| l_retain_step, 30);
698
699 l_Retirement_Plan_Code :=p_pa_request_rec.RETIREMENT_PLAN;
700 l_Retention_Allowance :=p_pa_request_rec.TO_RETENTION_ALLOWANCE;
701 --
702 hr_utility.set_location('After some assignments '|| l_proc, 30);
703 --
704
705 l_Second_NOAC_Lookup_code :=p_pa_request_rec.SECOND_NOA_CODE;
706 l_Service_Computation_Date :=p_pa_request_rec.SERVICE_COMP_DATE;
707 l_Sex :=p_personal_info.p_sex;
708 -- l_special_pay_table_id :=p_pos_valid_grade.pay_table_id; -- commented by skuttei on 17-apr-98
709 l_staffing_differential :=p_pa_request_rec.TO_STAFFING_DIFFERENTIAL;
710 l_submission_date :=Null;
711 l_supervisory_differential :=p_pa_request_rec.TO_SUPERVISORY_DIFFERENTIAL;
712 l_Supervisory_Status_Code :=p_pa_request_rec.SUPERVISORY_STATUS;
713 l_Tenure_Group_Code :=p_pa_request_rec.TENURE;
714 l_To_Pay_Status :=Null;
715 l_Veterans_Preference_Code :=p_pa_request_rec.VETERANS_PREFERENCE;
716 l_Veterans_Status_Code :=p_pa_request_rec.VETERANS_STATUS;
717 l_year_degree_attained :=to_char(p_pa_request_rec.YEAR_DEGREE_ATTAINED);
718
719 -- added by skutteti on 17-apr-98 to get the special pay table id for the cpdf checks.
720 if p_per_retained_grade.retain_grade is not null then
721 for special_pay_table in c_special_pay_table (p_per_retained_grade.retain_pay_table_id)loop
722 l_special_pay_table_id := special_pay_table.user_table_id;
723 end loop;
724 else
725 for special_pay_table in c_special_pay_table (p_pos_valid_grade.pay_table_id)loop
726 l_special_pay_table_id := special_pay_table.user_table_id;
727 end loop;
728 end if;
729
730
731 --
732 -- if to_position_id is null, assign 'from' data to 'to' data.
733 --
734 l_To_Position_ID := p_pa_request_rec.TO_Position_ID;
735 --
736 hr_utility.set_location('After some more assignments '|| l_proc, 40);
737 --
738
739 if l_To_Position_ID is not null then
740
741 -- l_Occupation_code :=p_pos_grp1.occupation_category_code;
742 l_Occupation_code :=p_pa_request_rec.to_occ_code;
743 l_To_Pay_Basis :=p_pa_request_rec.TO_PAY_BASIS;
744 l_To_Grade_Or_Level :=p_pa_request_rec.TO_GRADE_OR_LEVEL;
745 l_To_Pay_Plan :=p_pa_request_rec.TO_PAY_PLAN;
746 l_To_Locality_Adj := to_char(p_pa_request_rec.TO_LOCALITY_ADJ);
747 l_To_Basic_Pay := to_char(p_pa_request_rec.TO_BASIC_PAY);
748 l_pay_rate_determinant_code :=p_pa_request_rec.PAY_RATE_DETERMINANT;
749 IF l_temp_step IS NULL THEN
750 l_To_Step_Or_Rate :=p_pa_request_rec.TO_STEP_OR_RATE;
751 l_pay_rate_determinant_code :=p_pa_request_rec.PAY_RATE_DETERMINANT;
752 ELSE
753 l_To_Step_Or_Rate := l_temp_step;
754 l_RPA_Step_Or_Rate := p_pa_request_rec.TO_STEP_OR_RATE;
755 l_pay_rate_determinant_code := '0';
756 -- Bug#4657737 Commented the following code. Why is this resetting of PRD required?
757 /* FOR special_pay_table in c_special_pay_table (p_pos_valid_grade.pay_table_id)loop
758 l_Prior_Pay_Rate_Det_Code := '6';
759 END LOOP;*/
760 END IF;
761 l_work_schedule_code :=p_pa_request_rec.WORK_SCHEDULE;
762 /*
763 else
764 l_To_Basic_Pay :=to_char(p_sf52_from_data.basic_pay);
765 l_To_Grade_Or_Level :=p_sf52_from_data.grade_or_level;
766 l_To_Locality_Adj :=to_char(p_sf52_from_data.locality_adj);
767 l_Occupation_code :=p_sf52_from_data.occ_code;
768 l_To_Pay_Basis :=p_sf52_from_data.pay_basis;
769 l_To_Pay_Plan :=p_sf52_from_data.pay_plan;
770 l_pay_rate_determinant_code :=p_sf52_from_data.pay_rate_determinant;
771 l_To_Step_Or_Rate :=p_sf52_from_data.step_or_rate;
772 l_work_schedule_code :=p_sf52_from_data.WORK_SCHEDULE;
773 */
774 -- Adding for SLR Bug 3536448
775 IF (p_pa_request_rec.noa_family_code='GHR_STUDENT_LOAN') THEN
776 l_to_basic_pay := p_pa_request_rec.from_basic_pay;
777 l_to_pay_basis := p_pa_request_rec.from_pay_basis;
778 END IF;
779 end if;
780 --
781 hr_utility.set_location('After Position assignments '|| l_proc, 50);
782 --
783 l_update34_date := ghr_pay_caps.update34_implemented_date(p_pa_request_rec.person_id);
784 If (l_update34_date is null
785 OR
786 nvl(p_pa_request_rec.effective_date,sysdate ) < l_update34_date)
787 -- and (p_pa_request_rec.noa_family_code<>'GHR_STUDENT_LOAN')
788 THEN
789 for pay_plan_rec in c_fw_pay_plans(l_to_pay_plan) loop
790 l_to_Basic_Pay := ghr_pay_calc.convert_amount(l_to_basic_pay,
791 l_to_pay_basis,
792 'PA');
793 g_fw_annualize := 'Y';
794 end loop;
795 for prior_pay_plan_rec in c_fw_pay_plans(l_prior_pay_plan) loop
796 l_Prior_Basic_Pay := ghr_pay_calc.convert_amount(l_prior_basic_pay,
797 l_prior_pay_basis,
798 'PA');
799 end loop;
800 elsif nvl(p_pa_request_rec.effective_date,sysdate ) >= l_update34_date THEN
801 for pay_plan_rec in c_fw_pay_plans(l_to_pay_plan) loop
802 l_to_Basic_Pay := ghr_pay_calc.convert_amount(l_to_basic_pay,
803 l_to_pay_basis,
804 'PA');
805 g_fw_annualize := 'Y';
806 end loop;
807 for prior_pay_plan_rec in c_fw_pay_plans(l_prior_pay_plan) loop
808 l_Prior_Basic_Pay := ghr_pay_calc.convert_amount(l_prior_basic_pay,
809 l_prior_pay_basis,
810 'PA');
811 end loop;
812 end if;
813
814
815 -- need to get the effective_date of the prior pay table
816 -- Say for instance, the employee had a previous change in his basic pay on the 30-DEC-97 based on
817 -- the pay table effective then
818 -- If then there is a subsequent action on the 13-MAR-98, (assuming the new pay tables are
819 -- effective as of the 04-JAN-98' , then we should be getting the values from
820 -- the pay table effective as of his prior pay change to do any check on his prior pay basis
821
822 -- Payroll Integration
823 -- Changes needed here -Madhuri.
824 l_new_element_name := pqp_fedhr_uspay_int_utils.return_new_element_name
825 (p_fedhr_element_name => 'Basic Salary Rate',
826 p_business_group_id => ll_bg_id,
827 p_effective_date => ll_effective_date,
828 p_pay_basis => ll_pay_basis);
829
830 for eev_id in c_eev_id (p_element_name => l_new_element_name,
831 p_input_value_name => 'Rate',
832 p_bg_id => ll_bg_id)
833 loop
834 l_prior_effective_date := eev_id.effective_start_date;
835 l_element_entry_value_id := eev_id.element_entry_value_id;
836 end loop;
837
838 hr_utility.set_location('l_prior_effective date is '|| l_prior_effective_date, 60);
839 hr_utility.set_location('l_element_entry_value_id is '|| l_element_entry_value_id, 60);
840 ghr_history_api.get_g_session_var(l_session);
841 hr_utility.set_location('l_session.noa_id_correct is '|| l_session.noa_id_correct, 60);
842 if l_session.noa_id_correct is not null then -- for correction
843 for prior_date in c_prior_date (p_element_entry_value_id => l_element_entry_value_id )
844
845 loop
846 l_prior_effective_date := prior_date.effective_start_date;
847 hr_utility.set_location('l_prior_effective date is '|| l_prior_effective_date, 61);
848 hr_utility.set_location('l_element_entry_value_id is '|| l_element_entry_value_id, 61);
849 exit;
850 end loop;
851 end if;
852
853 -- Bug 4724337 Race or National Origin changes
854 IF p_race_ethnic_info.p_hispanic IS NOT NULL OR
855 p_race_ethnic_info.p_american_indian IS NOT NULL OR
856 p_race_ethnic_info.p_asian IS NOT NULL OR
857 p_race_ethnic_info.p_black_afr_american IS NOT NULL OR
858 p_race_ethnic_info.p_hawaiian_pacific IS NOT NULL OR
859 p_race_ethnic_info.p_white IS NOT NULL THEN
860
861 l_race_ethnic_info := NVL(p_race_ethnic_info.p_hispanic,' ') || NVL(p_race_ethnic_info.p_american_indian,' ') || NVL(p_race_ethnic_info.p_asian,' ') ||
862 NVL(p_race_ethnic_info.p_black_afr_american,' ') || NVL(p_race_ethnic_info.p_hawaiian_pacific,' ') || NVL(p_race_ethnic_info.p_white,' ');
863 hr_utility.set_location('Ethnicity value is ' || l_race_ethnic_info,1234);
864 END IF;
865 -- End Bug 4724337 Race or National Origin changes
866
867
868 g_academic_discipline := l_academic_discipline;
869 g_Adj_Base_Pay := l_Adj_Base_Pay;
870 g_Agency_Code := l_Agency_Code;
871 g_agency_subelement := l_agency_subelement;
872 g_as_of_date := l_as_of_date;
873 g_Benefit_Amount := l_Benefit_Amount;
874 g_bargaining_unit_status_code := l_bargaining_unit_status_code;
875 g_citizenship := l_citizenship;
876 g_credit_mil_svc := l_credit_mil_svc;
877 g_Cur_Appt_Auth_1 := l_Cur_Appt_Auth_1;
878 g_Cur_Appt_Auth_2 := l_Cur_Appt_Auth_2;
879 g_Duty_Station_ID := l_Duty_Station_ID;
880 g_Duty_Station_Lookup_Code := l_Duty_Station_Lookup_Code;
881 g_education_level := l_education_level;
882 g_effective_date := l_effective_date;
883 g_Employee_Date_of_Birth := l_Employee_Date_of_Birth;
884 g_Employee_First_Name := l_Employee_First_Name;
885 g_Employee_Last_Name := l_Employee_Last_Name;
886 g_employee_National_ID := l_employee_National_ID;
887 g_fegli_code := l_fegli_code;
888 g_fers_coverage := l_fers_coverage;
889 g_First_Action_NOA_LA_Code1 := l_First_Action_NOA_LA_Code1;
890 g_First_Action_NOA_LA_Code2 := l_First_Action_NOA_LA_Code2;
891 g_First_NOAC_Lookup_Code := l_First_NOAC_Lookup_Code;
892 g_flsa_category := l_flsa_category;
893 g_functional_class := l_functional_class;
894 g_health_plan := l_health_plan;
895 g_Handicap := l_Handicap;
896 g_Indiv_Award := l_Indiv_Award;
897 g_locality_pay_area := l_locality_pay_area;
898 g_Occupation_code := l_Occupation_code;
899 g_One_Time_Payment_Amount := l_One_Time_Payment_Amount;
900 g_Organ_Component := l_Organ_Component;
901 g_pay_rate_determinant_code := l_pay_rate_determinant_code;
902 g_Personnel_Officer_ID := l_Personnel_Officer_ID;
903 g_Position_Occ_Code := l_Position_Occ_Code;
904 g_Prior_Basic_Pay := l_Prior_Basic_Pay;
905 g_prior_duty_station := l_prior_duty_station;
906 g_Prior_Grade_Or_Level := l_prior_Grade_Or_Level;
907 g_Prior_Locality_Adj := l_Prior_Locality_Adj;
908 g_prior_locality_pay_area := l_prior_locality_pay_area;
909 g_Prior_Occupation_code := l_Prior_Occupation_code;
910 g_Prior_Pay_Basis := l_Prior_Pay_Basis;
911 g_Prior_Pay_Plan := l_Prior_Pay_Plan;
912 g_Prior_Pay_Rate_Det_Code := l_Prior_Pay_Rate_Det_Code;
913 g_Prior_Step_Or_Rate := l_Prior_Step_Or_Rate;
914 g_prior_work_schedule_code := l_prior_work_schedule_code;
915 g_Production_Date := l_Production_Date;
916 g_Race_National_Region := l_Race_National_Region;
917 g_rating_of_record_level := l_rating_of_record_level;
918 g_rating_of_record_pattern := l_rating_of_record_pattern;
919 g_rating_of_record_period := l_rating_of_record_period;
920 g_retain_grade := l_retain_grade;
921 g_retain_pay_plan := l_retain_pay_plan;
922 g_retain_step := l_retain_step;
923 g_temp_step := l_temp_step;
924 g_Retirement_Plan_Code := l_Retirement_Plan_Code;
925 g_Retention_Allowance := l_Retention_Allowance;
926 g_Second_NOAC_Lookup_code := l_Second_NOAC_Lookup_code;
927 g_Service_Computation_Date := l_Service_Computation_Date;
928 g_Sex := l_Sex;
929 g_special_pay_table_id := l_special_pay_table_id;
930 g_staffing_differential := l_staffing_differential;
931 g_submission_date := l_submission_date;
932 g_supervisory_differential := l_supervisory_differential;
933 g_Supervisory_Status_Code := l_Supervisory_Status_Code;
934 g_Tenure_Group_Code := l_Tenure_Group_Code;
935 g_To_Basic_Pay := l_To_Basic_Pay;
936 g_To_Grade_Or_Level := l_To_Grade_Or_Level;
937 g_To_Locality_Adj := l_To_Locality_Adj;
938 g_To_Pay_Basis := l_To_Pay_Basis;
939 g_To_Pay_Plan := l_To_Pay_Plan;
940 g_To_Pay_Status := l_To_Pay_Status;
941 g_To_Position_ID := l_To_Position_ID;
942 g_To_Step_Or_Rate := l_To_Step_Or_Rate;
943 g_Veterans_Preference_Code := l_Veterans_Preference_Code;
944 g_Veterans_Status_Code := l_Veterans_Status_Code;
945 g_work_schedule_code := l_work_schedule_code;
946 g_year_degree_attained := l_year_degree_attained;
947 g_race_ethnic_info := l_race_ethnic_info;
948
949 --
950 --
951 hr_utility.set_location('Calling CPDF Check'|| l_proc, 60);
952 --
953
954 GHR_CPDF_CHECK.call_CPDF_Check
955 (
956
957 p_academic_discipline => l_academic_discipline
958 ,p_Adj_Base_Pay => l_Adj_Base_Pay
959 ,p_agency => l_Agency_Code
960 ,p_agency_subelement => l_agency_subelement
961 ,p_as_of_date => l_as_of_date
962 ,p_Benefit_Amount => l_Benefit_Amount
963 ,p_bargaining_unit_status_code => l_bargaining_unit_status_code
964 ,p_citizenship => l_citizenship
965 ,p_credit_mil_svc => l_credit_mil_svc
966 ,p_Cur_Appt_Auth_1 => l_Cur_Appt_Auth_1
967 ,p_Cur_Appt_Auth_2 => l_Cur_Appt_Auth_2
968 ,p_Duty_Station_Lookup_Code => l_Duty_Station_Lookup_Code
969 ,p_education_level => l_education_level
970 ,p_effective_date => l_effective_date
971 ,p_Employee_Date_of_Birth => l_Employee_Date_of_Birth
972 ,p_Employee_First_Name => l_Employee_First_Name
973 ,p_Employee_Last_Name => l_Employee_last_Name
974 ,p_employee_National_ID => l_employee_National_ID
975 ,p_fegli_code => l_fegli_code
976 ,p_fers_coverage => l_fers_coverage
977 ,p_First_Action_NOA_LA_Code1 => l_First_Action_NOA_LA_Code1
978 ,p_First_Action_NOA_LA_Code2 => l_First_Action_NOA_LA_Code2
979 ,p_First_NOAC_Lookup_Code => l_First_NOAC_Lookup_Code
980 --Bug#4486823 RRR Changes
981 ,p_First_NOAC_Lookup_desc => l_First_NOAC_Lookup_desc
982 ,p_flsa_category => l_flsa_category
983 ,p_functional_class => l_functional_class
984 ,p_health_plan => l_health_plan
985 ,p_handicap => l_Handicap
986 ,p_Indiv_Award => l_Indiv_Award
987 ,p_locality_pay_area => l_locality_pay_area
988 ,p_occupation_code => l_Occupation_code
989 ,p_One_Time_Payment_Amount => l_One_Time_Payment_Amount
990 ,p_Organ_Component => l_Organ_Component
991 ,p_pay_rate_determinant_code => l_pay_rate_determinant_code
992 ,p_Personnel_Officer_ID => l_Personnel_Officer_ID
993 ,p_Position_Occ_Code => l_Position_Occ_Code
994 ,p_Prior_Basic_Pay => l_Prior_Basic_Pay
995 ,p_prior_duty_station => l_prior_duty_station
996 ,p_Prior_Grade_Or_Level => l_Prior_Grade_Or_Level
997 ,p_Prior_Locality_Adj => l_Prior_Locality_Adj
998 ,p_prior_locality_pay_area => l_prior_locality_pay_area
999 ,p_Prior_Occupation_code => l_prior_occupation_code
1000 ,p_Prior_Pay_Basis => l_Prior_Pay_Basis
1001 ,p_Prior_Pay_Plan => l_Prior_Pay_Plan
1002 ,p_Prior_Pay_Rate_Det_Code => l_Prior_Pay_Rate_Det_Code
1003 ,p_Prior_Step_Or_Rate => l_Prior_Step_Or_Rate
1004 ,p_prior_work_schedule_code => l_prior_work_schedule_code
1005 ,p_Production_Date => l_Production_Date
1006 ,p_Race_National_Region => l_Race_National_Region
1007 ,p_rating_of_record_level => l_rating_of_record_level
1008 ,p_rating_of_record_pattern => l_rating_of_record_pattern
1009 ,p_rating_of_record_period => l_rating_of_record_period
1010 --Bug# 4753117 28-Feb-07 Veeramani adding Appraisal start date as a parameter to the procedure
1011 ,p_rating_of_record_per_starts => l_rating_of_record_per_starts
1012 ,p_retain_grade => l_retain_grade
1013 ,p_retain_pay_plan => l_retain_pay_plan
1014 ,p_retain_step => l_retain_step
1015 ,p_Retirement_Plan_Code => l_retirement_plan_code
1016 ,p_Retention_Allowance => l_Retention_Allowance
1017 ,p_Second_NOAC_Lookup_code => l_Second_NOAC_Lookup_code
1018 ,p_Service_Computation_Date => l_Service_Computation_Date
1019 ,p_Sex => l_sex
1020 ,p_special_pay_table_id => l_special_pay_table_id
1021 ,p_staffing_differential => l_staffing_differential
1022 ,p_submission_date => l_submission_date
1023 ,p_supervisory_differential => l_supervisory_differential
1024 ,p_Supervisory_Status_Code => l_Supervisory_Status_Code
1025 ,p_Tenure_Group_Code => l_Tenure_Group_Code
1026 ,p_To_Basic_Pay => l_To_Basic_Pay
1027 ,p_To_Grade_Or_Level => l_To_grade_or_level
1028 ,p_To_Locality_Adj => l_To_locality_adj
1029 ,p_To_Pay_Basis => l_To_pay_basis
1030 ,p_To_Pay_Plan => l_To_pay_plan
1031 ,p_to_pay_status => l_To_pay_status
1032 ,p_To_Step_Or_Rate => l_To_step_or_rate
1033 ,p_Veterans_Preference_Code => l_veterans_preference_code
1034 ,p_Veterans_Status_Code => l_Veterans_Status_Code
1035 ,p_work_schedule_code => l_work_schedule_code
1036 ,p_year_degree_attained => l_year_degree_attained
1037 ,p_assignment_id => p_pa_request_rec.employee_assignment_id
1038 ,p_noa_family_code => p_pa_request_rec.noa_family_code
1039 ,p_prior_effective_date => l_prior_effective_date
1040 ,p_prior_loc_adj_effective_date => l_prior_loc_adj_effective_date
1041 ,p_rpa_step_or_rate => l_rpa_step_or_rate
1042 ,p_ethnic_race_info => l_race_ethnic_info
1043 );
1044 --
1045 hr_utility.set_location('After Calling CPDF Check'|| l_proc, 70);
1046 --
1047
1048 end if;
1049 --
1050 hr_utility.set_location('Calling Agency Check'|| l_proc, 80);
1051 --
1052 --
1053 hr_utility.set_location('Calling Validate Check'|| l_proc, 90);
1054 --
1055
1056 end GHRWS52L;
1057 --
1058 procedure CPDF_Parameter_Check is
1059 --
1060 --
1061 begin
1062 --
1063 null;
1064
1065 hr_utility.set_location('p_academic_discipline '||g_academic_discipline,1);
1066 hr_utility.set_location('p_Adj_Base_Pay '||g_Adj_Base_Pay,2);
1067 hr_utility.set_location('p_Agency_Code '||g_Agency_Code,3);
1068 hr_utility.set_location('p_agency_subelement '||g_agency_subelement,4);
1069 hr_utility.set_location('p_as_of_date '||g_as_of_date,5);
1070 hr_utility.set_location('p_Benefit_Amount '||g_Benefit_Amount,6);
1071 hr_utility.set_location('p_bargaining_unit_status_code '||g_bargaining_unit_status_code,7);
1072 hr_utility.set_location('p_citizenship '||g_citizenship,8);
1073 hr_utility.set_location('p_credit_mil_svc '||g_credit_mil_svc,9);
1074 hr_utility.set_location('p_Cur_Appt_Auth_1 '||g_Cur_Appt_Auth_1,10);
1075 hr_utility.set_location('p_Cur_Appt_Auth_2 '||g_Cur_Appt_Auth_2,11);
1076 hr_utility.set_location('p_Duty_Station_ID '||g_Duty_Station_ID,12);
1077 hr_utility.set_location('p_Duty_Station_Lookup_Code '||g_Duty_Station_Lookup_Code,13);
1078 hr_utility.set_location('p_education_level '||g_education_level,14);
1079 hr_utility.set_location('p_effective_date '||g_effective_date,15);
1080 hr_utility.set_location('p_Employee_Date_of_Birth '||g_Employee_Date_of_Birth,16);
1081 hr_utility.set_location('p_Employee_First_Name '||g_Employee_First_name,17);
1082 hr_utility.set_location('p_Employee_Last_Name '||g_Employee_Last_name,18);
1083 hr_utility.set_location('p_employee_National_ID '||g_employee_National_ID,19);
1084 hr_utility.set_location('p_fegli_code '||g_fegli_code,20);
1085 hr_utility.set_location('p_fers_coverage '||g_fers_coverage,21);
1086 hr_utility.set_location('p_First_Action_NOA_LA_Code1 '||g_First_Action_NOA_LA_Code1,22);
1087 hr_utility.set_location('p_First_Action_NOA_LA_Code2 '||g_First_Action_NOA_LA_Code2,23);
1088 hr_utility.set_location('p_First_NOAC_Lookup_Code '||g_First_NOAC_Lookup_Code,24);
1089 -- Bug#4486823 RRR Changes
1090 hr_utility.set_location('p_First_NOAC_Lookup_desc '||g_First_NOAC_Lookup_desc,24);
1091 hr_utility.set_location('p_flsa_category '||g_flsa_category,25);
1092 hr_utility.set_location('p_functional_class '||g_functional_class,26);
1093 hr_utility.set_location('p_health_plan '||g_health_plan,27);
1094 hr_utility.set_location('p_Handicap '||g_Handicap,28);
1095 hr_utility.set_location('p_Indiv_Award '||g_Indiv_Award,29);
1096 hr_utility.set_location('p_locality_pay_area '||g_locality_pay_area,30);
1097 hr_utility.set_location('p_Occupation_code '||g_Occupation_code,31);
1098 hr_utility.set_location('p_One_Time_Payment_Amount '||g_One_Time_Payment_Amount,32);
1099 hr_utility.set_location('p_Organ_Component '||g_Organ_Component,33);
1100 hr_utility.set_location('p_pay_rate_determinant_code '||g_pay_rate_determinant_code,34);
1101 hr_utility.set_location('p_Personnel_Officer_ID '||g_Personnel_Officer_ID,35);
1102 hr_utility.set_location('p_Position_Occ_Code '||g_Position_Occ_Code,36);
1103 hr_utility.set_location('p_Prior_Basic_Pay '||g_Prior_Basic_Pay,37);
1104 hr_utility.set_location('p_prior_duty_station '||g_prior_duty_station,38);
1105 hr_utility.set_location('p_Prior_Grade_Or_Level '||g_Prior_Grade_Or_Level,39);
1106 hr_utility.set_location('p_Prior_Locality_Adj '||g_Prior_Locality_Adj,40);
1107 hr_utility.set_location('p_prior_locality_pay_area '||g_prior_locality_pay_area,41);
1108 hr_utility.set_location('p_Prior_Occupation_code '||g_Prior_Occupation_code,42);
1109 hr_utility.set_location('p_Prior_Pay_Basis '||g_Prior_Pay_Basis,43);
1110 hr_utility.set_location('p_Prior_Pay_Plan '||g_Prior_Pay_Plan,44);
1111 hr_utility.set_location('p_Prior_Pay_Rate_Det_Code '||g_Prior_Pay_Rate_Det_Code,45);
1112 hr_utility.set_location('p_Prior_Step_Or_Rate '||g_Prior_Step_Or_Rate,46);
1113 hr_utility.set_location('p_prior_work_schedule_code '||g_prior_work_schedule_code,47);
1114 hr_utility.set_location('p_Production_Date '||g_Production_Date,48);
1115 hr_utility.set_location('p_Race_National_Region '||g_Race_National_Region,49);
1116 hr_utility.set_location('p_rating_of_record_level '||g_rating_of_record_level,50);
1117 hr_utility.set_location('p_rating_of_record_pattern '||g_rating_of_record_pattern,51);
1118 hr_utility.set_location('p_rating_of_record_period '||g_rating_of_record_period,52);
1119 hr_utility.set_location('p_retain_grade '||g_retain_grade,53);
1120 hr_utility.set_location('p_retain_pay_plan '||g_retain_pay_plan,54);
1121 hr_utility.set_location('p_retain_step '||g_retain_step,55);
1122 hr_utility.set_location('p_Retirement_Plan_Code '||g_Retirement_Plan_Code,56);
1123 hr_utility.set_location('p_Retention_Allowance '||g_Retention_Allowance,57);
1124 hr_utility.set_location('p_Second_NOAC_Lookup_code '||g_Second_NOAC_Lookup_code,58);
1125 hr_utility.set_location('p_Service_Computation_Date '||g_Service_Computation_Date,59);
1126 hr_utility.set_location('p_Sex '||g_Sex,60);
1127 hr_utility.set_location('p_special_pay_table_id '||g_special_pay_table_id,61);
1128 hr_utility.set_location('p_staffing_differential '||g_staffing_differential,62);
1129 hr_utility.set_location('p_submission_date '||g_submission_date,63);
1130 hr_utility.set_location('p_supervisory_differential '||g_supervisory_differential,64);
1131 hr_utility.set_location('p_Supervisory_Status_Code '||g_Supervisory_Status_Code,65);
1132 hr_utility.set_location('p_Tenure_Group_Code '||g_Tenure_Group_Code,66);
1133 hr_utility.set_location('p_To_Basic_Pay '||g_To_Basic_Pay,67);
1134 hr_utility.set_location('p_To_Grade_Or_Level '||g_To_Grade_Or_Level,68);
1135 hr_utility.set_location('p_To_Locality_Adj '||g_To_Locality_Adj,69);
1136 hr_utility.set_location('p_To_Pay_Basis '||g_To_Pay_Basis,70);
1137 hr_utility.set_location('p_To_Pay_Plan '||g_To_Pay_Plan,71);
1138 hr_utility.set_location('p_to_pay_status '||g_to_pay_status,72);
1139 hr_utility.set_location('p_To_Step_Or_Rate '||g_To_Step_Or_Rate,73);
1140 hr_utility.set_location('p_Veterans_Preference_Code '||g_Veterans_Preference_Code,74);
1141 hr_utility.set_location('p_Veterans_Status_Code '||g_Veterans_Status_Code,75);
1142 hr_utility.set_location('p_work_schedule_code '||g_work_schedule_code,76);
1143 hr_utility.set_location('p_year_degree_attained '||g_year_degree_attained,77);
1144 hr_utility.set_location('p_race_ethnic_info '||g_race_ethnic_info,77);
1145
1146 --
1147 end CPDF_Parameter_Check;
1148 --
1149 end GHR_GHRWS52L;