eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GHR_CPDF_CHECK

Source


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