1: package body GHR_Validate_CHECK AS
2: /* $Header: ghrvalid.pkb 120.31.12010000.3 2008/12/12 09:40:29 utokachi ship $ */
3:
4: procedure Validate_CHECK(p_pa_request_rec IN ghr_pa_requests%ROWTYPE
5: ,p_per_group1 IN ghr_api.per_group1_type
6: ,p_per_retained_grade IN ghr_api.per_retained_grade_type
7: ,p_per_sep_retire in ghr_api.per_sep_retire_type
8: ,p_per_conversions in ghr_api.per_conversions_type
31: l_person_usr_type per_person_types.user_person_type%type;
32: l_person_sys_type per_person_types.system_person_type%type;
33: l_psn_status per_positions.status%type;
34:
35: l_part_time_hr ghr_pa_requests.part_time_hours%type;
36: l_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
37:
38: l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
32: l_person_sys_type per_person_types.system_person_type%type;
33: l_psn_status per_positions.status%type;
34:
35: l_part_time_hr ghr_pa_requests.part_time_hours%type;
36: l_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
37:
38: l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40: l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
34:
35: l_part_time_hr ghr_pa_requests.part_time_hours%type;
36: l_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
37:
38: l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40: l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
41: l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
35: l_part_time_hr ghr_pa_requests.part_time_hours%type;
36: l_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
37:
38: l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40: l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
41: l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43: l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
36: l_work_schedule_code ghr_pa_requests.WORK_SCHEDULE%type;
37:
38: l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40: l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
41: l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43: l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
44:
37:
38: l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40: l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
41: l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43: l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
44:
45: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
38: l_To_Pay_Basis ghr_pa_requests.TO_PAY_BASIS%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40: l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
41: l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43: l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
44:
45: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
46: l_from_step_or_rate ghr_pa_requests.from_step_or_rate%type;
39: l_To_Basic_Pay ghr_pa_requests.TO_BASIC_PAY%type;
40: l_To_Locality_Adj ghr_pa_requests.TO_LOCALITY_ADJ%type;
41: l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43: l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
44:
45: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
46: l_from_step_or_rate ghr_pa_requests.from_step_or_rate%type;
47:
41: l_To_adj_basic_pay ghr_pa_requests.TO_ADJ_BASIC_PAY%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43: l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
44:
45: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
46: l_from_step_or_rate ghr_pa_requests.from_step_or_rate%type;
47:
48: l_Retention_Allowance ghr_pa_requests.TO_RETENTION_ALLOWANCE%type;
49: l_staffing_differential ghr_pa_requests.TO_STAFFING_DIFFERENTIAL%type;
42: l_To_other_pay ghr_pa_requests.TO_OTHER_PAY_AMOUNT%type;
43: l_To_total_pay ghr_pa_requests.TO_TOTAL_SALARY%type;
44:
45: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
46: l_from_step_or_rate ghr_pa_requests.from_step_or_rate%type;
47:
48: l_Retention_Allowance ghr_pa_requests.TO_RETENTION_ALLOWANCE%type;
49: l_staffing_differential ghr_pa_requests.TO_STAFFING_DIFFERENTIAL%type;
50:
44:
45: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
46: l_from_step_or_rate ghr_pa_requests.from_step_or_rate%type;
47:
48: l_Retention_Allowance ghr_pa_requests.TO_RETENTION_ALLOWANCE%type;
49: l_staffing_differential ghr_pa_requests.TO_STAFFING_DIFFERENTIAL%type;
50:
51: l_effective_date ghr_pa_requests.EFFECTIVE_DATE%type;
52:
45: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
46: l_from_step_or_rate ghr_pa_requests.from_step_or_rate%type;
47:
48: l_Retention_Allowance ghr_pa_requests.TO_RETENTION_ALLOWANCE%type;
49: l_staffing_differential ghr_pa_requests.TO_STAFFING_DIFFERENTIAL%type;
50:
51: l_effective_date ghr_pa_requests.EFFECTIVE_DATE%type;
52:
53: l_AUO varchar2(30):=null;
47:
48: l_Retention_Allowance ghr_pa_requests.TO_RETENTION_ALLOWANCE%type;
49: l_staffing_differential ghr_pa_requests.TO_STAFFING_DIFFERENTIAL%type;
50:
51: l_effective_date ghr_pa_requests.EFFECTIVE_DATE%type;
52:
53: l_AUO varchar2(30):=null;
54: l_Availablility varchar2(30):=null;
55: l_assignment_id ghr_pa_requests.EMPLOYEE_ASSIGNMENT_ID%type;
51: l_effective_date ghr_pa_requests.EFFECTIVE_DATE%type;
52:
53: l_AUO varchar2(30):=null;
54: l_Availablility varchar2(30):=null;
55: l_assignment_id ghr_pa_requests.EMPLOYEE_ASSIGNMENT_ID%type;
56: l_prem_pay varchar2(30);
57: l_amount number;
58: l_multiple_error_flag boolean;
59: l_session ghr_history_api.g_session_var_type;
70: l_auo_amount number;
71: l_exists boolean;
72: l_noa_family_code ghr_families.noa_family_code%type;
73: l_to_agency_code per_people_extra_info.pei_information8%type;
74: l_to_supervisor_diff ghr_pa_requests.TO_SUPERVISORY_DIFFERENTIAL%type ;
75: l_award_salary ghr_pa_requests.from_basic_pay%type ; -- Bug 3376761
76:
77: l_slr_recur_amount number;
78: l_slr_lumpsum number;
71: l_exists boolean;
72: l_noa_family_code ghr_families.noa_family_code%type;
73: l_to_agency_code per_people_extra_info.pei_information8%type;
74: l_to_supervisor_diff ghr_pa_requests.TO_SUPERVISORY_DIFFERENTIAL%type ;
75: l_award_salary ghr_pa_requests.from_basic_pay%type ; -- Bug 3376761
76:
77: l_slr_recur_amount number;
78: l_slr_lumpsum number;
79:
238: and ipv.input_value_id = eev.input_value_id
239: and ele.element_entry_id + 0 = eev.element_entry_id ;
240:
241: -- Sundar 3263109 To find out if any future actions exist.
242: CURSOR c_future_actions(c_person_id ghr_pa_requests.person_id%type, c_effective_date ghr_pa_requests.effective_date%type) IS
243: SELECT par.pa_request_id futr_rpa
244: FROM ghr_pa_routing_history prh
245: ,ghr_pa_requests par
246: WHERE prh.pa_request_id = par.pa_request_id
241: -- Sundar 3263109 To find out if any future actions exist.
242: CURSOR c_future_actions(c_person_id ghr_pa_requests.person_id%type, c_effective_date ghr_pa_requests.effective_date%type) IS
243: SELECT par.pa_request_id futr_rpa
244: FROM ghr_pa_routing_history prh
245: ,ghr_pa_requests par
246: WHERE prh.pa_request_id = par.pa_request_id
247: AND par.person_id = c_person_id
248: AND par.effective_date > c_effective_date
249: AND prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
265: AND NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
266: )
267: );
268:
269: l_futr_actions ghr_pa_requests.pa_request_id%type;
270:
271: --
272: -- Madhuri 3417859 Start fix of variables and cursor
273: --
307: AND information_type = 'GHR_US_PAR_PREMIUM_PAY_IND';
308:
309: CURSOR cur_job_code is
310: SELECT from_occ_code
311: FROM ghr_pa_requests
312: WHERE pa_request_id = p_pa_request_rec.pa_request_id;
313:
314: l_occ_code ghr_pa_requests.from_occ_code%TYPE;
315:
310: SELECT from_occ_code
311: FROM ghr_pa_requests
312: WHERE pa_request_id = p_pa_request_rec.pa_request_id;
313:
314: l_occ_code ghr_pa_requests.from_occ_code%TYPE;
315:
316: --
317: --
318: CURSOR cur_repay_sch IS
326: l_review_date date;
327:
328: CURSOR cur_studloan_ele_end_date(
329: p_ele_name pay_element_types_f.element_name%type,
330: p_asg_id ghr_pa_requests.employee_assignment_id%type)
331: IS
332: select ele.effective_end_date
333: from pay_element_types_f elt,
334: pay_element_links_f ell,
398: l_nte_date_flg BOOLEAN:=FALSE;
399:
400: --
401: CURSOR cur_nte_check(p_asg_id per_assignments_f.assignment_id%TYPE,
402: p_eff_date ghr_pa_requests.effective_date%TYPE)
403: IS
404: SELECT user_status
405: FROM per_assignment_status_types pst, per_assignments_f paf
406: WHERE paf.assignment_status_type_id = pst.assignment_status_type_id
496: hr_utility.set_message(8301,'GHR_38288_POSITION_INVALID');
497: hr_utility.raise_error;
498: end;
499: End If;
500: l_psi := ghr_pa_requests_pkg.get_personnel_system_indicator(p_pa_request_rec.to_position_id,
501: p_pa_request_rec.effective_date);
502:
503: /* Get Person Status and last of DDF retrivals.
504: