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