[Home] [Help]
PACKAGE BODY: APPS.GHR_CPDF_CHECK
Source
1 package body GHR_CPDF_CHECK as
2 /* $Header: ghcpdf00.pkb 120.14.12010000.2 2008/08/05 15:02:36 ubhat ship $ */
3
4
5
6 procedure call_CPDF_Check
7 (
8 p_academic_discipline in varchar2
9 ,p_Adj_Base_Pay in number
10 ,p_Agency in varchar2 -- Non-SF52 Data Item
11 ,p_agency_subelement in varchar2 -- Non-SF52 Data Item
12 ,p_as_of_date in date -- Non-SF52 Data Item
13 ,p_Benefit_Amount in varchar2 -- Non-SF52 Data Item
14 ,p_bargaining_unit_status_code in varchar2
15 ,p_citizenship in varchar2
16 ,p_credit_mil_svc in varchar2 -- Non-SF52 Data Item
17 ,p_Cur_Appt_Auth_1 in varchar2 -- non SF52 item
18 ,p_Cur_Appt_Auth_2 in varchar2 -- non SF52 item
19 ,p_Duty_Station_Lookup_Code in varchar2
20 ,p_education_level in varchar2
21 ,p_effective_date in date
22 ,p_Employee_Date_of_Birth in date
23 ,p_Employee_First_Name in varchar2
24 ,p_Employee_Last_Name in varchar2
25 ,p_employee_National_ID in varchar2
26 ,p_fegli_code in varchar2
27 ,p_fers_coverage in varchar2 -- Non-SF52 Data Item
28 ,p_First_Action_NOA_LA_Code1 in varchar2
29 ,p_First_Action_NOA_LA_Code2 in varchar2
30 ,p_First_NOAC_Lookup_Code in varchar2
31 -- Bug#4486823 RRR Changes
32 ,p_First_NOAC_Lookup_desc in varchar2
33 ,p_flsa_category in varchar2
34 ,p_functional_class in varchar2
35 ,p_health_plan in varchar2 -- Non-SF52 Data Item
36 ,p_Handicap in varchar2 -- Non-SF52 Data Item
37 ,p_Indiv_Award in varchar2 -- Non-SF52 Data Item
38 ,p_locality_pay_area in varchar2 -- Non-SF52 Data Item
39 ,p_Occupation_code in varchar2 -- Non-SF52 Data Item
40 ,p_One_Time_Payment_Amount in number
41 ,p_Organ_Component in varchar2 -- Non-SF52 Data Item
42 ,p_pay_rate_determinant_code in varchar2
43 ,p_Personnel_Officer_ID in varchar2 -- Non-SF52 Data Item
44 ,p_Position_Occ_Code in varchar2
45 ,p_Prior_Basic_Pay in varchar2 -- Non-SF52 Data Item
46 ,p_prior_duty_station in varchar2 -- Non-SF52 Data Item
47 ,p_Prior_Grade_Or_Level in varchar2 -- Non-SF52 Data Item
48 ,p_Prior_Locality_Adj in varchar2 -- Non-SF52 Data Item
49 ,p_prior_locality_pay_area in varchar2 -- Non-SF52 Data Item
50 ,p_Prior_Occupation_code in varchar2 -- Non-SF52 Data Item
51 ,p_Prior_Pay_Basis in varchar2 -- Non-SF52 Data Item
52 ,p_Prior_Pay_Plan in varchar2 -- Non-SF52 Data Item
53 ,p_Prior_Pay_Rate_Det_Code in varchar2 -- Non-SF52 Data Item
54 ,p_Prior_Step_Or_Rate in varchar2 -- Non-SF52 Data Item
55 ,p_prior_work_schedule_code in varchar2 -- Non-SF52 Data Item
56 ,p_Production_Date in date -- Non-SF52 Data Item
57 ,p_Race_National_Region in varchar2 -- Non-SF52 Data Item
58 ,p_rating_of_record_level in varchar2 -- Non-SF52 Data Item
59 ,p_rating_of_record_pattern in varchar2 -- Non-SF52 Data Item
60 ,p_rating_of_record_period in varchar2 -- Non-SF52 Data Item
61 ,p_rating_of_record_per_starts in varchar2 -- Non-SF52 Data Item
62 ,p_retain_grade in varchar2 -- Non-SF52 Data Item
63 ,p_retain_pay_plan in varchar2
64 ,p_retain_step in varchar2 -- Non-SF52 Data Item
65 ,p_Retirement_Plan_Code in varchar2
66 ,p_Retention_Allowance in varchar2 -- Non-SF52 Data Item
67 ,p_Second_NOAC_Lookup_code in varchar2
68 ,p_Service_Computation_Date in Date
69 ,p_Sex in varchar2 -- Non-SF52 Data Item
70 ,p_special_pay_table_id in varchar2 -- Non-SF52 Data Item
71 ,p_staffing_differential in varchar2 -- Non-SF52 Data Item
72 ,p_submission_date in date -- Non-SF52 Data Item
73 ,p_supervisory_differential in varchar2 -- Non-SF52 Data Item
74 ,p_Supervisory_Status_Code in varchar2
75 ,p_Tenure_Group_Code in varchar2
76 ,p_To_Basic_Pay in varchar2
77 ,p_To_Grade_Or_Level in varchar2
78 ,p_To_Locality_Adj in varchar2
79 ,p_To_Pay_Basis in varchar2
80 ,p_To_Pay_Plan in varchar2
81 ,p_to_pay_status in varchar2 -- Non-SF52 Data Item
82 ,p_To_Step_Or_Rate in varchar2
83 ,p_Veterans_Preference_Code in varchar2
84 ,p_Veterans_Status_Code in varchar2
85 ,p_work_schedule_code in varchar2
86 ,p_year_degree_attained in varchar2
87 ,p_assignment_id in number
88 ,p_noa_family_code in varchar2
89 ,p_prior_effective_date in date
90 ,p_prior_loc_adj_effective_date in date
91 ,p_rpa_step_or_rate in varchar2
92 ,p_ethnic_race_info in varchar2 -- Bug 4724337 Race or National Origin changes
93 ) is
94 /*
95
96 Missing CPDF Data Items: 4/11/97
97
98 -- p_Agency_Code varchar2; -- Non-SF52 Data Item
99 -- p_Agency varchar2;
100 -- p_agency_subelement varchar2; -- Non-SF52 Data Item
101 -- p_as_of_date date; -- Non-SF52 Data Item
102 -- p_Benefit_Amount varchar2; -- Non-SF52 Data Item;
103 -- p_health_plan varchar2; -- Non-SF52 Data Item
104 -- p_Indiv_Award varchar2;
105 -- p_Organ_Component varchar2; -- Non-SF52 Data Item
106 -- p_to_Pay_Status varchar2; -- Non-SF52 Data Item
107 -- p_prior_duty_station varchar2; -- Non-SF52 Data Item
108 -- p_prior_locality_pay_area varchar2; -- Non-SF52 Data Item
109 -- p_prior_work_schedule_code varchar2; -- Non-SF52 Data Item
110 -- p_Production_Date date; -- Non-SF52 Data Item
111 -- p_rating_of_record_level varchar2; -- Non-SF52 Data Item
112 -- p_rating_of_record_pattern varchar2; -- Non-SF52 Data Item
113 -- p_rating_of_record_period varchar2; -- Non-SF52 Data Item
114 -- p_Retention_Allowance varchar2; -- Non-SF52 Data Item
115 -- p_staffing_differential varchar2; -- Non-SF52 Data Item
116 -- p_submission_date date; -- Non-SF52 Data Item
117 -- p_supervisory_differential varchar2; -- Non-SF52 Data Item
118
119 Missing CPDF Data Items: 9/9/97
120
121
122 -- p_as_of_date date; -- Non-SF52 Data Item
123 -- p_to_Pay_Status varchar2; -- Non-SF52 Data Item
124 -- p_Production_Date date; -- Non-SF52 Data Item
125 -- p_submission_date date; -- Non-SF52 Data Item
126 */
127
128 l_proc varchar2(61) := 'CALL_CPDF_CHECK';
129 l_session ghr_history_api.g_session_var_type;
130 l_pay_rate_determinant varchar2(30); ----Bug# 5639003
131 Begin
132
133 hr_utility.set_location('Entering ...'|| l_proc, 10);
134
135
136 hr_utility.set_location('p_academic_discipline : '||p_academic_discipline, 5);
137 hr_utility.set_location('p_Adj_Base_Pay :'||to_char(p_Adj_Base_Pay), 5);
138 hr_utility.set_location('p_Agency :'||p_Agency , 5);
139 hr_utility.set_location('p_agency_subelement :'||p_agency_subelement , 5);
140 hr_utility.set_location('p_as_of_date :'||fnd_date.date_to_chardate(p_as_of_date), 5);
141 hr_utility.set_location('p_Benefit_Amount :'||p_Benefit_Amount , 5);
142 hr_utility.set_location('p_bargaining_unit_status_code :'||p_bargaining_unit_status_code , 5);
143 hr_utility.set_location('p_citizenship :'||p_citizenship , 5);
144 hr_utility.set_location('p_Cur_Appt_Auth_1 :'||p_Cur_Appt_Auth_1 , 5);
145 hr_utility.set_location('p_Cur_Appt_Auth_2 :'||p_Cur_Appt_Auth_2 , 5);
146 hr_utility.set_location('p_Duty_Station_Lookup_Code :'||p_Duty_Station_Lookup_Code , 5);
147 hr_utility.set_location('p_education_level :'||p_education_level , 5);
148 hr_utility.set_location('p_effective_date :'||p_effective_date , 5);
149 hr_utility.set_location('p_prior_effective_date :'||fnd_date.date_to_chardate(p_prior_effective_date) , 5);
150 hr_utility.set_location('p_Employee_First_Name :'|| substr(p_Employee_First_Name,1,30) , 5);
151 hr_utility.set_location('p_Employee_Last_Name :'|| substr(p_Employee_last_Name,1,30) , 5);
152 hr_utility.set_location('p_employee_National_ID :'||p_employee_National_ID , 5);
153 hr_utility.set_location('p_fegli_code :'||p_fegli_code , 5);
154 hr_utility.set_location('p_First_Action_NOA_LA_Code1 :'||p_First_Action_NOA_LA_Code1 , 5);
155 hr_utility.set_location('p_First_Action_NOA_LA_Code2 :'||p_First_Action_NOA_LA_Code2 , 5);
156 hr_utility.set_location('p_First_NOAC_Lookup_Code :'||p_First_NOAC_Lookup_Code , 5);
157 -- Bug#4486823 RRR Changes
158 hr_utility.set_location('p_First_NOAC_Lookup_desc :'||p_First_NOAC_Lookup_desc , 5);
159 hr_utility.set_location('p_flsa_category :'||p_flsa_category , 5);
160 hr_utility.set_location('p_functional_class :'||p_functional_class , 5);
161 hr_utility.set_location('p_locality_pay_area :'||p_locality_pay_area , 5);
162 hr_utility.set_location('p_One_Time_Payment_Amount :'||p_One_Time_Payment_Amount , 5);
163 hr_utility.set_location('p_pay_rate_determinant_code :'||p_pay_rate_determinant_code, 5 );
164 hr_utility.set_location('p_Prior_Basic_Pay :'||p_Prior_Basic_Pay , 5);
165 hr_utility.set_location('p_prior_duty_station :'||p_prior_duty_station , 5);
166 hr_utility.set_location('p_Prior_Grade_Or_Level :'||p_Prior_Grade_Or_Level , 5);
167 hr_utility.set_location('p_prior_locality_pay_area :'||p_prior_locality_pay_area , 5);
168 hr_utility.set_location('p_Prior_Occupation_code :'||p_Prior_Occupation_code , 5);
169 hr_utility.set_location('p_Prior_Pay_Basis :'||p_Prior_Pay_Basis , 5);
170 hr_utility.set_location('p_Prior_Pay_Plan :'||p_Prior_Pay_Plan , 5);
171 hr_utility.set_location('p_Prior_Pay_Rate_Det_Code :'||p_Prior_Pay_Rate_Det_Code , 5);
172 hr_utility.set_location('p_Prior_Step_Or_Rate :'||p_Prior_Step_Or_Rate , 5);
173 hr_utility.set_location('p_prior_work_schedule_code :'||p_prior_work_schedule_code , 5);
174 hr_utility.set_location('p_retain_grade :'||p_retain_grade , 5);
175 hr_utility.set_location('p_retain_pay_plan :'||p_retain_pay_plan , 5);
176 hr_utility.set_location('p_retain_step :'||p_retain_step , 5);
177 hr_utility.set_location('p_special_pay_table_id :'||p_special_pay_table_id, 5);
178 hr_utility.set_location('p_To_Basic_Pay :'||p_To_Basic_Pay , 5);
179 hr_utility.set_location('p_To_Grade_Or_Level :'||p_To_Grade_Or_Level , 5);
180 hr_utility.set_location('p_To_Locality_Adj :'||p_To_Locality_Adj , 5);
181 hr_utility.set_location('p_To_Pay_Basis :'||p_To_Pay_Basis , 5);
182 hr_utility.set_location('p_To_Grade_Or_Level :'||p_To_Grade_Or_Level , 5);
183 hr_utility.set_location('p_To_Locality_Adj :'||p_To_Locality_Adj , 5);
184 hr_utility.set_location('p_To_Pay_Basis :'||p_To_Pay_Basis , 5);
185 hr_utility.set_location('p_To_Pay_Plan :'||p_To_Pay_Plan , 5);
186 hr_utility.set_location('p_to_pay_status :'||p_to_pay_status , 5);
187 hr_utility.set_location('p_work_schedule_code :'||p_work_schedule_code , 5);
188 hr_utility.set_location('p_assignment_id :'||p_assignment_id , 5);
189 hr_utility.set_location('p_Veterans_Status_Code :'||p_Veterans_Status_Code , 5);
190 hr_utility.set_location('p_noa_family_code :'||p_noa_family_code , 5);
191 --Begin Bug# 5639003
192 IF p_noa_family_code IN ('AWARD','GHR_INCENTIVE') or p_First_NOAC_Lookup_Code = '825' THEN
193 l_pay_rate_determinant := NULL;
194 ELSE
195 l_pay_rate_determinant := p_pay_rate_determinant_code;
196 END IF;
197 --End Bug# 5639003
198 /* Calling GHR_CPDF_CHECK1 */
199 --
200 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_bargaining_unit '|| l_proc, 10);
201 --
202
203 GHR_CPDF_CHECK1.chk_bargaining_unit
207 );
204 (p_to_pay_plan
205 ,p_agency_subelement
206 ,p_bargaining_unit_status_code
208
209 --
210 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_fegli '|| l_proc, 11);
211 --
212
213 GHR_CPDF_CHECK1.chk_fegli
214 (p_to_basic_pay
215 ,p_to_pay_plan
216 ,p_fegli_code
217 ,p_effective_date
218 );
219
220 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_fsla_category'|| l_proc, 12);
221
222 GHR_CPDF_CHECK1.chk_fsla_category
223 (p_duty_station_lookup_code
224 ,p_to_pay_plan
225 ,p_agency_subelement
226 ,p_flsa_category
227 ,p_to_grade_or_level
228 ,p_effective_date --Bug# 5619873
229 );
230 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_functional_classification'|| l_proc, 13);
231
232 GHR_CPDF_CHECK1.chk_functional_classification
233 (p_Occupation_code
234 ,p_functional_class
235 ,p_effective_date --Bug# 5619873
236 );
237
238 --
239 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_health_plan '|| l_proc, 14);
240 --
241
242 GHR_CPDF_CHECK1.chk_health_plan
243 (p_health_plan
244 ,p_tenure_group_code
245 ,p_work_schedule_code
246 ,p_to_pay_basis
247 ,p_to_pay_status
248 ,p_submission_date
249 ,p_Cur_Appt_Auth_1
250 ,p_Cur_Appt_Auth_2
251 );
252
253 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_retain_grade'|| l_proc, 15);
254
255 GHR_CPDF_CHECK1.chk_retain_grade
256 (p_retain_pay_plan
257 ,p_retain_grade
258 ,l_pay_rate_determinant
259 ,p_effective_date
260 );
261 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_retain_pay_plan'|| l_proc, 16);
262
263 GHR_CPDF_CHECK1.chk_retain_pay_plan
264 (p_retain_grade
265 ,p_retain_pay_plan
266 ,p_retain_step
267 ,p_to_pay_plan
268 ,l_pay_rate_determinant
269 ,p_effective_date
270
271 );
272
273
274 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_retain_step'|| l_proc, 17);
275 GHR_CPDF_CHECK1.chk_retain_step
276 (l_pay_rate_determinant
277 ,p_first_action_noa_la_code1
278 ,p_first_action_noa_la_code2
279 ,p_Cur_Appt_Auth_1
280 ,p_Cur_Appt_Auth_2
281 ,p_retain_pay_plan
282 ,p_retain_grade
283 ,p_retain_step
284 ,p_effective_date
285 );
286
287 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_retirement_plan'|| l_proc, 18);
288
289 GHR_CPDF_CHECK1.chk_retirement_plan
290 (p_retirement_plan_code
291 ,p_fers_coverage
292 );
293
294
295 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_special_pay_table_id'|| l_proc, 19);
296 GHR_CPDF_CHECK1.chk_special_pay_table_id
297 (l_pay_rate_determinant
298 ,p_to_pay_plan
299 ,p_special_pay_table_id
300 -- FWFA Changes Bug#4444609
301 ,p_effective_date
302 -- FWFA Changes
303 );
304
305
306 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_us_citizenship'|| l_proc, 20);
307 GHR_CPDF_CHECK1.chk_us_citizenship
308 (p_citizenship
309 ,p_duty_station_lookup_code
310 );
311
312 hr_utility.set_location('Calling GHR_CPDF_CHECK1.chk_century_info'|| l_proc, 37);
313 GHR_CPDF_CHECK1.chk_century_info (
314 p_date_of_birth => p_employee_date_of_birth
315 ,p_effective_date => p_effective_date
316 ,p_Service_Computation_Date => p_service_computation_date
317 ,p_year_degree_attained => p_year_degree_attained
318 ,p_rating_of_record_period => p_rating_of_record_period
319 ,p_rating_of_record_per_starts => p_rating_of_record_per_starts
320 );
321
322
323
324 /* Calling GHR_CPDF_CHECK2 */
325 --
326 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_instructional_pgm '|| l_proc, 40);
327 --
328
329 GHR_CPDF_CHECK2.chk_instructional_pgm
330 (p_education_level
331 ,p_academic_discipline
332 ,p_year_degree_attained
333 ,p_first_noac_lookup_code
334 ,p_effective_date
335 ,p_tenure_group_code
336 ,p_to_pay_plan
337 ,p_Employee_Date_of_Birth
338 );
339
340 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_award_amount '|| l_proc, 41);
341 GHR_CPDF_CHECK2.chk_Award_Amount
342 ( p_First_NOAC_Lookup_Code
343 -- Bug#4486823
344 ,p_First_NOAC_lookup_Desc
345 ,p_One_Time_Payment_Amount
346 ,p_To_Basic_Pay
347 ,p_Adj_Base_Pay
348 ,p_First_Action_NOA_LA_Code1
349 ,p_First_Action_NOA_LA_Code2
350 ,p_to_pay_plan
351 ,p_effective_date
352 );
353
354
355 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_benefit_amount '|| l_proc, 42);
356
357 GHR_CPDF_CHECK2.chk_Benefit_Amount
358 (p_First_NOAC_Lookup_Code
359 ,p_Benefit_Amount
360 ,p_effective_date
361 );
362
363
364 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_benefit_amount '|| l_proc, 43);
365
366 GHR_CPDF_CHECK2.chk_Cur_Appt_Auth
367 (p_First_Action_NOA_LA_Code1
368 ,p_First_Action_NOA_LA_Code2
369 ,p_Cur_Appt_Auth_1
370 ,p_Cur_Appt_Auth_2
371 ,p_Agency_Subelement
372 ,p_Occupation_Code
376 ,p_Handicap
373 ,p_First_NOAC_Lookup_Code
374 ,p_Position_Occ_Code
375 ,p_To_Pay_Plan
377 ,p_Tenure_Group_Code
378 ,p_To_Grade_Or_Level
379 ,p_Veterans_Preference_Code
380 ,p_Duty_Station_Lookup_Code
381 ,p_Service_Computation_Date
382 ,p_effective_date
383 );
384 --
385 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_Date_of_Birth '|| l_proc, 50);
386 --
387
388
389 GHR_CPDF_CHECK2.chk_Date_of_Birth
390 ( p_First_NOAC_Lookup_Code
391 ,p_Effective_Date
392 ,p_Employee_Date_of_Birth
393 ,p_Duty_Station_Lookup_Code
394 ,p_as_of_date
395 );
396
397 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_duty_station '|| l_proc, 51);
398
399 GHR_CPDF_CHECK2.chk_duty_station
400 (p_to_pay_plan
401 ,p_agency_subelement
402 ,p_duty_station_lookup_code
403 ,p_First_Action_NOA_LA_Code1
404 ,p_First_Action_NOA_LA_Code2
405 ,p_effective_date
406 );
407 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_education_level '|| l_proc, 52);
408
409 GHR_CPDF_CHECK2.chk_Education_Level
410 ( p_tenure_group_code
411 ,p_education_level
412 ,p_to_pay_plan
413 );
414
415
416 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_effective_date '|| l_proc, 53);
417 GHR_CPDF_CHECK2.chk_effective_date
418 ( p_First_NOAC_Lookup_Code
419 ,p_Effective_Date
420 ,p_Production_Date
421 );
422
423 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_handicap '|| l_proc, 54);
424
425 GHR_CPDF_CHECK2.chk_Handicap
426 (p_First_Action_NOA_LA_Code1
427 ,p_First_Action_NOA_LA_Code2
428 ,p_First_NOAC_Lookup_Code
429 ,p_Effective_Date
430 ,p_Handicap
431 );
432
433
434 hr_utility.set_location('Calling GHR_CPDF_CHECK2.chk_indiv_award '|| l_proc, 55);
435 GHR_CPDF_CHECK2.chk_indiv_Award
436 (p_First_NOAC_Lookup_Code
437 ,p_Indiv_Award
438 ,p_effective_date
439 );
440
441
442 /* Calling GHR_CPDF_CHECK3 */
443 --
444 hr_utility.set_location('Calling GHR_CPDF_CHECK3.chk_Nature_of_Action '|| l_proc, 60);
445 --
446
447 GHR_CPDF_CHECK3.chk_Nature_of_Action (
448 p_First_NOAC_Lookup_Code => p_First_NOAC_Lookup_Code
449 ,p_Second_NOAC_Lookup_code => p_Second_NOAC_Lookup_code
450 ,p_First_Action_NOA_Code1 => p_First_Action_NOA_LA_Code1
451 ,p_First_Action_NOA_Code2 => p_First_Action_NOA_LA_Code2
452 ,p_Cur_Appt_Auth_1 => p_Cur_Appt_Auth_1
453 ,p_Cur_Appt_Auth_2 => p_Cur_Appt_Auth_2
454 ,p_Employee_Date_of_Birth => p_Employee_Date_of_Birth
455 ,p_Duty_Station_Lookup_Code => p_Duty_Station_Lookup_Code
456 ,p_Employee_First_Name => p_Employee_First_Name
457 ,p_Employee_Last_Name => p_Employee_Last_Name
458 ,p_Handicap => p_handicap
459 ,p_Organ_Component => p_Organ_Component
460 ,p_Personal_Office_ID => p_Personnel_Officer_ID
461 ,p_Position_Occ_Code => p_Position_Occ_Code
462 ,p_Race_National_Region => p_Race_National_Region
463 ,p_Retirement_Plan_Code => p_Retirement_Plan_Code
464 ,p_Service_Computation_Date => p_Service_Computation_Date
465 ,p_Sex => p_sex
466 ,p_Supervisory_Status_Code => p_supervisory_status_code
467 ,p_Tenure_Group_Code => p_Tenure_Group_Code
468 ,p_Veterans_Pref_Code => p_Veterans_Preference_Code
469 ,p_Veterans_Status_Code => p_Veterans_status_Code
470 ,p_Occupation => p_occupation_code
471 ,p_To_Pay_Basis => p_to_pay_basis
472 ,p_To_Grade_Or_Level => p_To_Grade_Or_Level
473 ,p_To_Pay_Plan => p_To_pay_plan
474 ,p_pay_rate_determinant_code => l_pay_rate_determinant
475 ,p_To_Basic_Pay => p_to_basic_pay
476 ,p_To_Step_Or_Rate => p_To_Step_Or_Rate
477 ,p_Work_Sche_Code => p_work_schedule_code
478 ,p_Prior_Occupation => p_Prior_Occupation_code
479 ,p_Prior_To_Pay_Basis => p_Prior_Pay_Basis
480 ,p_Prior_To_Grade_Or_Level => p_Prior_Grade_Or_Level
481 ,p_Prior_To_Pay_Plan => p_Prior_Pay_Plan
482 ,p_Prior_Pay_Rate_Det_Code => p_Prior_Pay_Rate_Det_Code
483 ,p_Prior_To_Basic_Pay => p_Prior_Basic_Pay
484 ,p_Prior_To_Step_Or_Rate => p_Prior_Step_Or_Rate
485 ,p_Prior_Work_Sche_Code => p_prior_work_schedule_code
486 ,p_prior_duty_station => p_prior_duty_station
487 ,p_Retention_Allowance => p_Retention_Allowance
488 ,p_Staff_Diff => p_staffing_differential
489 ,p_Supervisory_Diff => p_supervisory_differential
490 ,p_To_Locality_Adj => p_To_Locality_Adj
491 ,p_Prior_To_Locality_Adj => p_Prior_Locality_Adj
492 ,p_noa_family_code => p_noa_family_code
493 ,p_effective_date => p_effective_date
494 ,p_agency_subelement => p_agency_subelement
495 ,p_ethnic_race_info => p_ethnic_race_info
496 );
497
498
499 hr_utility.set_location('Calling GHR_CPDF_CHECK3.chk_occupation '|| l_proc, 61);
500 GHR_CPDF_CHECK3.chk_occupation
501 (p_to_pay_plan
502 ,p_Occupation_code
506 );
503 ,p_agency_subelement
504 ,p_duty_station_lookup_code
505 ,p_effective_date
507 -- hr_utility.set_location('Calling pay_basis '||p_to_pay_basis, 62);
508 -- hr_utility.set_location('Calling PAY PLAN '||p_to_pay_plan, 62);
509 -- hr_utility.set_location('Calling PRD '||p_pay_rate_determinant_code, 62);
510 -- hr_utility.set_location('Calling basic pay '||p_to_basic_pay, 62);
511
512 --- Using NVL(p_retain_pay_plan,p_to_pay_plan) as we need to pick the retained pay plan
513 --- if any retained grade details exist for that person
514 ---
515 GHR_CPDF_CHECK3.chk_pay_basis
516 ( NVL(p_retain_pay_plan,p_to_pay_plan)
517 ,p_to_pay_basis
518 ,p_to_basic_pay
519 ,p_agency_subelement
520 ,p_Position_Occ_Code --Bug# 5745356
521 ,p_effective_date
522 ,l_pay_rate_determinant
523 );
524
525 hr_utility.set_location('Calling GHR_CPDF_CHECK3.chk_pay_grade '|| l_proc, 63);
526 GHR_CPDF_CHECK3.chk_pay_grade
527 (p_to_pay_plan
528 ,p_to_grade_or_level
529 ,l_pay_rate_determinant
530 ,p_first_action_noa_la_code1
531 ,p_first_action_noa_la_code2
532 ,p_First_NOAC_Lookup_Code
533 ,p_Second_NOAC_Lookup_code
534 ,p_effective_date
535 );
536
537 hr_utility.set_location('Calling GHR_CPDF_CHECK3.chk_pay_plan '|| l_proc, 64);
538 GHR_CPDF_CHECK3.chk_pay_plan
539 (p_to_pay_plan
540 ,p_agency_subelement
541 ,p_personnel_officer_ID
542 ,p_to_grade_or_level
543 ,p_first_action_noa_la_code1
544 ,p_first_action_noa_la_code2
545 ,p_Cur_Appt_Auth_1
546 ,p_Cur_Appt_Auth_2
547 ,p_first_NOAC_Lookup_Code
548 ,l_pay_rate_determinant
549 ,p_Effective_Date
550 ,p_prior_pay_plan
551 ,p_prior_grade_or_level
552 ,p_Agency
553 ,p_Supervisory_status_code
554 );
555
556 --
557 hr_utility.set_location('Calling GHR_CPDF_CHECK3.chk_pay_rate_determinant '|| l_proc, 70);
558 --
559
560 GHR_CPDF_CHECK3.chk_pay_rate_determinant
561 (l_pay_rate_determinant
562 ,p_prior_pay_rate_det_code
563 ,p_to_pay_plan
564 ,p_first_noac_lookup_code
565 ,p_duty_station_lookup_code
566 ,p_agency
567 ,p_effective_date
568 );
569
570 /* Calling GHR_CPDF_CHECK4 */
571 --
572 hr_utility.set_location('Calling GHR_CPDF_CHECK4.chk_Legal_Authority '|| l_proc, 80);
573 --
574
575 GHR_CPDF_CHECK4.chk_Legal_Authority
576 (p_To_Play_Plan => p_To_Pay_Plan
577 ,p_Agency_Sub_Element => p_Agency_SubElement
578 ,p_First_Action_NOA_LA_Code1 => p_First_Action_NOA_LA_Code1
579 ,p_First_Action_NOA_LA_Code2 => p_First_Action_NOA_LA_Code2
580 ,p_First_NOAC_Lookup_Code => p_First_NOAC_Lookup_Code
581 ,p_effective_date => p_effective_date
582 ,p_position_occupied_code => p_Position_Occ_Code
583 );
584
585 /* Calling GHR_CPDF_CHECK5 */
586 --
587 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_rating_of_rec '|| l_proc, 90);
588 --
589
590 GHR_CPDF_CHECK5.chk_rating_of_rec
591 (p_rating_of_record_level
592 ,p_rating_of_record_pattern
593 ,p_rating_of_record_period
594 ,p_rating_of_record_per_starts
595 ,p_first_noac_lookup_code
596 ,p_effective_date
597 ,p_submission_date
598 ,p_to_pay_plan
599 );
600
601 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_position_occupied '|| l_proc, 91);
602 GHR_CPDF_CHECK5.chk_position_occupied
603 (p_position_occ_code
604 ,p_to_pay_plan
605 ,p_first_noac_lookup_code
606 ,p_effective_date
607 );
608
609 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_prior_occupation '|| l_proc, 92);
610
611 GHR_CPDF_CHECK5.chk_prior_occupation
612 (p_prior_occupation_code
613 ,p_occupation_code
614 ,p_first_noac_lookup_code
615 ,p_prior_pay_plan
616 ,p_agency_subelement
617 ,p_effective_date
618 );
619
620 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_prior_pay_basis '|| l_proc, 93);
621
622 GHR_CPDF_CHECK5.chk_prior_pay_basis
623 (p_prior_pay_basis
624 ,p_prior_pay_plan
625 ,p_agency_subelement
626 ,p_prior_basic_pay
627 ,p_effective_date
628 ,p_prior_effective_date
629 ,p_prior_pay_rate_det_code
630 );
631
632
633 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_prior_grade '|| l_proc, 94);
634 GHR_CPDF_CHECK5.chk_prior_grade
635 (p_prior_pay_plan
636 ,p_to_grade_or_level
637 ,p_prior_grade_or_level
638 ,p_to_pay_plan
639 ,p_first_noac_lookup_code
640 ,p_prior_pay_rate_det_code
641 ,p_effective_date
642 );
643
644
645 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_prior_pay_plan '|| l_proc, 95);
646 GHR_CPDF_CHECK5.chk_prior_pay_plan
647 (p_prior_pay_plan
648 ,p_to_pay_plan
649 ,p_first_noac_lookup_code
650 --,p_prior_effective_date -- deleted Bug# 6010943
651 ,p_effective_date -- Added Bug# 6010943
652 );
653
654
655 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_prior_pay_rate_determinant '|| l_proc, 96);
656 GHR_CPDF_CHECK5.chk_prior_pay_rate_determinant
657 (p_prior_pay_rate_det_code
658 ,l_pay_rate_determinant
662 ,p_First_NOAC_Lookup_Code
659 ,p_prior_pay_plan
660 ,p_to_pay_plan
661 ,p_agency
663 ,p_prior_duty_station
664 ,p_prior_effective_date
665 ,p_effective_date
666 );
667
668 --
669 hr_utility.set_location('Calling GHR_CPDF_CHECK5.chk_prior_step_or_rate '|| l_proc, 100);
670 --
671
672
673 GHR_CPDF_CHECK5.chk_prior_step_or_rate
674 (p_prior_step_or_rate
675 ,p_first_noac_lookup_code
676 ,nvl(p_rpa_step_or_rate,p_to_step_or_rate) --Bug# 4947801
677 ,l_pay_rate_determinant
678 ,p_to_pay_plan
679 ,p_prior_pay_rate_det_code
680 ,p_prior_pay_plan
681 ,p_prior_grade_or_level
682 ,p_prior_effective_date
683 ,p_cur_appt_auth_1
684 ,p_cur_appt_auth_2
685 ,p_effective_date
686 );
687
688 /* Calling GHR_CPDF_CHECK6 */
689 --
690 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_prior_work_schedule '|| l_proc, 110);
691 --
692
693 GHR_CPDF_CHECK6.chk_prior_work_schedule
694 (p_prior_work_schedule_code
695 ,p_work_schedule_code
696 ,p_first_noac_lookup_code
697 );
698
699 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_race_or_natnl_origin '|| l_proc, 111);
700
701 GHR_CPDF_CHECK6.chk_race_or_natnl_origin
702 (p_Race_National_Region
703 ,p_duty_station_lookup_code
704 ,p_ethnic_race_info
705 ,p_First_NOAC_Lookup_Code -- Bug 4754941
706 ,p_Second_NOAC_Lookup_code
707 ,p_effective_date
708 );
709
710 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_prior_duty_station '|| l_proc, 112);
711
712
713 GHR_CPDF_CHECK6.chk_prior_duty_station
714 (p_prior_duty_station
715 ,p_agency_subelement
716 );
717
718 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_retention_allowance '|| l_proc, 113);
719
720 GHR_CPDF_CHECK6.chk_retention_allowance
721 (p_retention_allowance
722 ,p_to_pay_plan
723 ,p_to_basic_pay
724 ,p_first_noac_lookup_code
725 ,p_first_action_noa_la_code1
726 ,p_first_action_noa_la_code2
727 );
728
729
730 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_staffing_differential '|| l_proc, 114);
731
732 GHR_CPDF_CHECK6.chk_staffing_differential
733 (p_staffing_differential
734 ,p_first_noac_lookup_code
735 ,p_first_action_noa_la_code1
736 ,p_first_action_noa_la_code2
737 );
738
739
740 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_supervisory_differential '|| l_proc, 115);
741 GHR_CPDF_CHECK6.chk_supervisory_differential
742 (p_supervisory_differential
743 ,p_first_noac_lookup_code
744 ,p_first_action_noa_la_code1
745 ,p_first_action_noa_la_code2
746 ,p_effective_date
747 );
748
749
750 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_service_comp_date '|| l_proc, 116);
751
752 GHR_CPDF_CHECK6.chk_service_comp_date
753 (p_service_computation_date
754 ,p_effective_date
755 ,p_employee_date_of_birth
756 ,p_duty_station_lookup_code
757 ,p_first_noac_lookup_code
758 ,p_credit_mil_svc
759 ,p_submission_date
760 );
761
762 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_social_security '|| l_proc, 117);
763
764 -- Added the parameter p_effective_date for Bug#5487271
765 GHR_CPDF_CHECK6.chk_Social_Security
766 ( p_agency_subelement
767 ,p_employee_National_ID
768 ,p_personnel_officer_ID
769 ,p_effective_date
770 );
771
772 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_step_or_rate '|| l_proc, 118);
773
774 GHR_CPDF_CHECK6.chk_step_or_rate
775 (p_to_step_or_rate
776 ,l_pay_rate_determinant
777 ,p_to_pay_plan
778 ,p_to_grade_or_level
779 ,p_first_action_noa_la_code1
780 ,p_first_action_noa_la_code2
781 ,p_Cur_Appt_Auth_1
782 ,p_Cur_Appt_Auth_2
783 ,p_effective_date
784 ,p_rpa_step_or_rate
785 );
786
787 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_supervisory_status '|| l_proc, 119);
788 GHR_CPDF_CHECK6.chk_supervisory_status
789 (p_supervisory_status_code
790 ,p_to_pay_plan
791 ,p_effective_date
792 );
793
794 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_tenure '|| l_proc, 120);
795 GHR_CPDF_CHECK6.chk_tenure
796 (p_tenure_group_code
797 ,p_to_pay_plan
798 ,p_first_action_noa_la_code1
799 ,p_first_action_noa_la_code2
800 ,p_first_noac_lookup_code
801 ,p_Cur_Appt_Auth_1
802 ,p_Cur_Appt_Auth_2
803 ,p_effective_date
804 );
805
806 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_veterans_pref '|| l_proc, 121);
807 GHR_CPDF_CHECK6.chk_veterans_pref
808 (p_veterans_preference_code
809 ,p_first_action_noa_la_code1
810 ,p_first_action_noa_la_code2
811 );
812
813 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_veterans_status '|| l_proc, 122);
814
815 GHR_CPDF_CHECK6.chk_veterans_status
816 (p_veterans_status_code
817 ,p_veterans_preference_code
818 ,p_first_noac_lookup_code
819 ,p_agency_subelement
820 ,p_First_Action_NOA_LA_Code1
821 );
822
823 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_work_schedule '|| l_proc, 123);
824 GHR_CPDF_CHECK6.chk_work_schedule
825 (p_work_schedule_code
826 ,p_first_noac_lookup_code
827 );
828
832 ,p_year_degree_attained
829 hr_utility.set_location('Calling GHR_CPDF_CHECK6.chk_degree_attained '|| l_proc, 124);
830 GHR_CPDF_CHECK6.chk_degree_attained
831 ( p_effective_date
833 ,p_as_of_date
834 );
835
836
837 /* Calling GHR_CPDF_CHECK7 */
838 --
839 hr_utility.set_location('Calling GHR_CPDF_CHECK7.chk_prior_basic_pay '|| l_proc, 125);
840 --
841
842 GHR_CPDF_CHECK7.chk_prior_basic_pay
843 (p_prior_pay_plan
844 ,l_pay_rate_determinant
845 ,p_prior_pay_rate_det_code
846 ,p_prior_basic_pay
847 ,p_retain_pay_plan
848 ,p_retain_grade
849 ,p_retain_step
850 ,p_agency_subelement
851 ,p_prior_grade_or_level
852 ,p_prior_step_or_rate
853 ,p_prior_pay_basis
854 ,p_first_noac_lookup_code
855 ,p_to_basic_pay
856 ,p_to_pay_basis
857 ,p_effective_date
858 ,p_prior_effective_date
859 );
860
861 hr_utility.set_location('Calling GHR_CPDF_CHECK7.chk_locality_adj '|| l_proc, 126);
862 GHR_CPDF_CHECK7.chk_locality_adj
863 (p_to_pay_plan => p_to_pay_plan
864 ,p_to_basic_pay => p_to_basic_pay
865 ,p_pay_rate_determinant_code => l_pay_rate_determinant
866 ,p_retained_pay_plan => p_retain_pay_plan
867 ,p_Prior_Pay_Plan => p_prior_pay_plan
868 ,p_prior_pay_rate_det_code => p_prior_pay_rate_det_code
869 ,p_locality_pay_area => p_locality_pay_area
870 ,p_to_locality_adj => p_to_locality_adj
871 ,p_effective_date => p_effective_date
872 ,p_as_of_date => p_as_of_date
873 ,p_first_noac_lookup_code => p_first_noac_lookup_code
874 ,p_agency_subelement => p_agency_subelement
875 ,p_duty_station_Code => p_Duty_Station_Lookup_Code
876 ,p_special_pay_table_id => p_special_pay_table_id --Bug# 5745356(upd50)
877 );
878
879 --
880 -- Get the session variable to check whether the action is correction
881 -- If correction skip the prior locality edits as the prior_locality_pay_area
882 -- and prior_duty_station_id values is incorrect. In anycase the original
883 -- pa request would have passed through them.
884 -- Bug #709282
885 --
886 hr_utility.set_location('Calling GHR_CPDF_CHECK7.chk_prior_locality_adj '|| l_proc, 127);
887 ghr_history_api.get_g_session_var(l_session);
888 If l_session.noa_id_correct is null then
889 GHR_CPDF_CHECK7.chk_prior_locality_adj
890 (p_to_pay_plan => p_to_pay_plan
891 ,p_to_basic_pay => p_to_basic_pay
892 ,p_Prior_Pay_Plan => p_prior_pay_plan
893 ,p_pay_rate_determinant_code => l_pay_rate_determinant
894 ,p_retained_pay_plan => p_retain_pay_plan
895 ,p_prior_pay_rate_det_code => p_prior_pay_rate_det_code
896 ,p_locality_pay_area => p_locality_pay_area
897 ,p_prior_locality_pay_area => p_prior_locality_pay_area
898 ,p_prior_basic_pay => p_prior_basic_pay
899 ,p_to_locality_adj => p_to_locality_adj
900 ,p_prior_locality_adj => p_prior_locality_adj
901 ,p_prior_loc_adj_effective_date => p_prior_loc_adj_effective_date
902 ,p_first_noac_lookup_code => p_first_noac_lookup_code
903 ,p_as_of_date => p_as_of_date
904 ,p_agency_subelement => p_agency_subelement
905 ,p_prior_duty_station => p_prior_duty_station
906 ,p_effective_date => p_effective_date
907 );
908 end if;
909
910 /* Calling GHR_CPDF_CHECK8 */
911 --
912 hr_utility.set_location('Calling GHR_CPDF_CHECK8.basic_pay '|| l_proc, 130);
913 --
914
915 GHR_CPDF_CHECK8.basic_pay
916 (p_to_pay_plan
917 ,l_pay_rate_determinant
918 ,p_to_basic_pay
919 ,p_retain_pay_plan
920 ,p_retain_grade
921 ,p_retain_step
922 ,p_agency_subelement
923 ,p_to_grade_or_level
924 ,p_to_step_or_rate
925 ,p_to_pay_basis
926 ,p_first_action_noa_la_code1
927 ,p_first_action_noa_la_code2
928 ,p_first_noac_lookup_code
929 ,p_effective_date
930 ,p_occupation_code
931 );
932
933 end call_CPDF_Check;
934
935 --
936 --
937 function get_basic_pay
938 (table_name in varchar2
939 ,row_name in varchar2
940 ,column_name in varchar2
941 ,effective_date in date)
942 RETURN NUMBER
943 IS
944 basic_pay number(10,2);
945 BEGIN
946 SELECT to_number(value) into basic_pay
947 FROM pay_user_columns udc,
948 pay_user_rows_f udr,
949 pay_user_tables udt,
950 pay_user_column_instances_f uci
951 WHERE udc.user_column_name = column_name
952 AND effective_date between
953 uci.effective_start_date and
954 uci.effective_end_date
955 AND effective_date between
956 udr.effective_start_date and
957 udr.effective_end_date
958 AND udr.row_low_range_or_name = row_name
959 AND udt.user_table_name = table_name
960 AND udr.user_table_id = udt.user_table_id
961 AND udc.user_table_id = udt.user_table_id
962 AND uci.user_row_id = udr.user_row_id
963 AND uci.user_column_id = udc.user_column_id;
964 return basic_pay;
965
966 EXCEPTION
967 WHEN NO_DATA_FOUND
968 THEN
969 return null;
970 end get_basic_pay;
971
972
973 end GHR_CPDF_CHECK;