1 package ghr_formula_functions as
2 /* $Header: ghforfun.pkh 120.2.12010000.3 2008/11/05 11:26:29 vmididho ship $*/
3
4 function get_plan_eligibility( p_business_group_id in Number
5 ,p_asg_id in Number
6 ,p_effective_date in Date
7 ,p_pl_id in Number)
8 RETURN VARCHAR2;
9
10 function get_plan_short_code ( p_business_group_id in Number
11 ,p_effective_date in Date
12 ,p_pl_id in Number)
13 RETURN VARCHAR2;
14
15
16 function get_option_short_code ( p_business_group_id in Number
17 ,p_effective_date in Date
18 ,p_opt_id in Number)
19 RETURN VARCHAR2;
20
21 function chk_person_type(p_business_group_id in Number,
22 p_assignment_id in number)
23 RETURN VARCHAR2;
24
25 function check_if_emp_csrs( p_business_group_id in Number
26 ,p_asg_id in Number
27 ,p_effective_date in Date )
28 RETURN VARCHAR2;
29
30 function get_retirement_plan( p_business_group_id in Number
31 ,p_asg_id in Number
32 ,p_effective_date in Date )
33 RETURN VARCHAR2;
34
35 function get_employee_tsp_eligibility( p_business_group_id in Number
36 ,p_asg_id in Number
37 ,p_effective_date in Date )
38 RETURN VARCHAR2;
39
40 Function get_emp_annual_salary(p_assignment_id in Number,
41 p_effective_date in Date
42 )
43 return Number;
44
45 FUNCTION ghr_tsp_amount_validation(
46 p_business_group_id in number
47 ,p_asg_id in number
48 ,p_effective_date in date
49 ,p_pgm_id in number
50 ,p_pl_id in number
51 )
52 RETURN varchar2;
53
54
55 FUNCTION ghr_tsp_percentage_validation(
56 p_business_group_id in number
57 ,p_asg_id in number
58 ,p_effective_date in date
59 ,p_pgm_id in number
60 ,p_pl_id in number
61 )
62 RETURN varchar2;
63
64
65 Function tsp_open_season_effective_dt (p_business_group_id in Number
66 ,p_asg_id in Number
67 ,p_effective_date in Date
68 ,p_pgm_id in Number)
69 RETURN Date;
70
71 Function get_tsp_status (p_business_group_id in Number
72 ,p_effective_date in Date
73 ,p_opt_id in Number
74 ,p_asg_id in Number)
75 Return Varchar2;
76
77 Function fn_effective_date (p_effective_date in Date)
78 Return Date;
79
80 Function get_emp_elig_date (p_business_group_id in Number
81 ,p_effective_date in Date
82 ,p_asg_id in Number
83 ,p_pgm_id in Number
84 ,p_opt_id in Number
85 )
86 Return Varchar2 ;
87
88
89
90 Function tsp_plan_electble( p_business_group_id in Number
91 ,p_asg_id in Number
92 ,p_pgm_id in Number
93 ,p_pl_id in Number
94 ,p_ler_id in Number
95 ,p_effective_date in Date
96 ,p_opt_id in Number )
97 RETURN VARCHAR2;
98
99 /* ------------------- Tsp catch Up Procedures ------------*/
100 Function get_emp_tsp_catchup_elig( p_business_group_id in Number
101 ,p_asg_id in Number
102 ,p_pgm_id in Number
103 ,p_effective_date in Date )
104
105 Return Varchar2;
106
107 /* Functions added to FEHB deliverables in June 2005. */
108
109 function get_fehb_pgm_eligibility( p_business_group_id in Number
110 ,p_asg_id in Number
111 ,p_effective_date in Date )
112
113 RETURN VARCHAR2;
114
115 FUNCTION get_temps_total_cost( p_business_group_id in Number
116 ,p_asg_id in Number
117 ,p_effective_date in Date )
118 RETURN VARCHAR2;
119
120 Function fehb_plan_electable( p_business_group_id in Number
121 ,p_asg_id in Number
122 ,p_pgm_id in Number
123 ,p_pl_id in Number
124 ,p_ler_id in Number
125 ,p_effective_date in Date
126 ,p_opt_id in Number)
127 RETURN VARCHAR2;
128
129 Function get_agency_contrib_date (p_asg_id in Number
130 ,p_effective_date in Date)
131 RETURN DATE;
132
133 Function get_emp_contrib_date (p_asg_id in Number
134 ,p_effective_date in Date)
135 RETURN DATE;
136
137 Function get_coe_date (p_asg_id in Number
138 ,p_effective_date in Date)
139 Return Date;
140
141 Function get_tsp_status_date (p_asg_id in Number
142 ,p_effective_date in Date)
143 Return Date;
144
145 Function tsp_cvg_and_rate_start_date (p_business_group_id in Number
146 ,p_asg_id in Number
147 ,p_effective_date in Date)
148 Return Date;
149
150 FUNCTION ghr_tsp_cu_amount_validation(
151 p_business_group_id in number
152 ,p_asg_id in number
153 ,p_effective_date in date
154 ,p_pgm_id in number
155 ,p_pl_id in number
156 )
157 RETURN varchar2;
158
159 -- Parameter p_payroll_period_start_date addded. This date must be the start date
160 -- of the payroll period in which election occurs.
161 FUNCTION chk_if_ee_is_50 (p_person_id in Number,
162 p_asg_id in Number,
163 p_effective_date in date,
164 p_payroll_period_start_date in date)
165 RETURN varchar2;
166 End;