[Home] [Help]
PACKAGE BODY: APPS.HRBEEBC
Source
1 PACKAGE BODY hrbeebc AS
2 /* $Header: pebenebc.pkb 115.0 99/07/17 18:46:33 porting ship $ */
3 --
4 --
5 -- Procedure/Function Definitions
6 --
7 --
8 -- Name
9 --
10 -- Purpose
11 --
12 -- This procedure raise an error if the current element
13 -- is referenced by COBRA
14 --
15 -- Arguments
16 --
17 -- p_business_group_id
18 -- p_element_type_id
19 -- p_coverage_type
20 --
21 -- Example
22 --
23 -- Notes
24 --
25 FUNCTION hr_ben_chk_cobra_reference (p_business_group_id NUMBER,
26 p_element_type_id NUMBER,
27 p_coverage_type VARCHAR2) RETURN BOOLEAN IS
28 --
29 -- declare local variables
30 --
31 l_coverage_exists varchar2(1) := 'N';
32 --
33 -- declare cursor for cobra check
34 --
35 CURSOR chk_cobra_ref_exists IS
36 SELECT 'Y'
37 FROM per_cobra_coverage_benefits ccb
38 WHERE ccb.business_group_id + 0 = p_business_group_id
39 AND ccb.element_type_id = p_element_type_id
40 AND ccb.coverage_type = p_coverage_type
41 AND ccb.accept_reject_flag = 'ACC';
42 --
43 BEGIN
44 --
45 -- hr_utility.trace_on;
46 --
47 hr_utility.set_location('hr_ben_chk_cobra_reference', 0);
48 --
49 --
50 -- execute cursor
51 --
52 OPEN chk_cobra_ref_exists;
53 --
54 hr_utility.set_location('hr_ben_chk_cobra_reference', 1);
55 --
56 FETCH chk_cobra_ref_exists INTO l_coverage_exists;
57 --
58 hr_utility.set_location('hr_ben_chk_cobra_reference', 2);
59 --
60 CLOSE chk_cobra_ref_exists;
61 --
62 hr_utility.set_location('hr_ben_chk_cobra_reference', 3);
63 --
64 --
65 -- chk to see if element referenced
66 --
67 IF(l_coverage_exists = 'Y')
68 THEN
69 --
70 hr_utility.set_location('hr_ben_chk_cobra_reference', 4);
71 --
72 -- return true if referenced
73 RETURN TRUE;
74 ELSE
75 --
76 hr_utility.set_location('hr_ben_chk_cobra_reference', 5);
77 --
78 -- return false as it is not referenced
79 RETURN FALSE;
80 END IF;
81 --
82 END hr_ben_chk_cobra_reference;
83 --
84 --
85 --
86 -- Name hr_ben_bc_pre_insert
87 --
88 -- Purpose
89 --
90 -- This is the pre-insert handler for the form
91 -- when inserting into PER_BENEFIT_CONTRIBUTIONS
92 --
93 -- Arguments
94 --
95 -- p_benefit_contribution_id
96 -- p_element_type_id NUMBER,
97 -- p_coverage_type VARCHAR,
98 -- p_effective_start_date DATE,
99 -- p_effective_end_date DATE,
100 -- p_business_group_id NUMBER
101 --
102 --
103 PROCEDURE hr_ben_bc_pre_insert (p_benefit_contribution_id IN OUT NUMBER,
104 p_element_type_id NUMBER,
105 p_coverage_type VARCHAR,
106 p_effective_start_date DATE,
107 p_effective_end_date DATE,
108 p_business_group_id NUMBER ) IS
109 --
110 --
111 BEGIN
112 --
113 -- Call date track functions
114 --
115 -- Call to check for duplicate contributions
116 --
117 hr_ben_benefit_contributions.hr_ben_chk_duplicate_cont
118 ( p_benefit_contribution_id,
119 p_element_type_id,
120 p_coverage_type,
121 p_effective_start_date,
122 p_effective_end_date,
123 p_business_group_id );
124 --
125 -- Call procedure to get surrogate key value for
126 -- per_benefit_contributions
127 --
128 hr_ben_benefit_contributions.hr_ben_benefit_contribution_id ( p_benefit_contribution_id );
129 --
130 -- end of procedure/function definitions
131 --
132 END hr_ben_bc_pre_insert;
133 --
134 --
135 --
136 -- Name hr_ben_bc_pre_update
137 --
138 -- Purpose
139 --
140 -- This is the pre-update handler for the form
141 -- when updating BEN_BENEFIT_CONTRIBUTIONS
142 --
143 -- Arguments
144 --
145 -- p_benefit_contribution_id
146 -- p_element_type_id NUMBER,
147 -- p_coverage_type VARCHAR,
148 -- p_effective_start_date DATE,
149 -- p_effective_end_date DATE,
150 -- p_business_group_id NUMBER
151 --
152 --
153 PROCEDURE hr_ben_bc_pre_update (p_benefit_contribution_id IN OUT NUMBER,
154 p_element_type_id NUMBER,
155 p_coverage_type VARCHAR,
156 p_effective_start_date DATE,
157 p_effective_end_date DATE,
158 p_business_group_id NUMBER ) IS
159 --
160 --
161 local_warning exception;
162 BEGIN
163 --
164 hr_utility.set_location('hr_bc_pre_update', 1);
165 --
166 hr_ben_benefit_contributions.hr_ben_chk_duplicate_cont
167 ( p_benefit_contribution_id,
168 p_element_type_id,
169 p_coverage_type,
170 p_effective_start_date,
171 p_effective_end_date,
172 p_business_group_id );
173 --
174 -- Call chk for COBRA reference
175 --
176 hr_utility.set_location('hr_bc_pre_update', 2);
177 --
178 IF(hrbeebc.hr_ben_chk_cobra_reference(p_business_group_id,
179 p_element_type_id,
180 p_coverage_type))
181 THEN
182 --
183 hr_utility.set_location('hr_bc_pre_update', 3);
184 --
185 -- raise warning
186 hr_utility.set_message(801, 'HR_BEN_COBRA_REFERENCE');
187 raise local_warning;
188 --
189 END IF;
190 --
191 hr_utility.set_location('hr_bc_pre_update', 4);
192 --
193 exception
194 when local_warning then
195 hr_utility.set_warning;
196 --
197 hr_utility.set_location('hr_bc_pre_update', 5);
198 --
199 END hr_ben_bc_pre_update;
200 --
201 --
202 --
203 -- Name hr_ben_bc_pre_delete
204 --
205 -- Purpose
206 --
207 -- This is the pre-delete handler for the form
208 -- when deleting into BEN_BENEFIT_CONTRIBUTIONS
209 --
210 -- Arguments
211 --
212 PROCEDURE hr_ben_bc_pre_delete (p_business_group_id NUMBER,
213 p_benefit_contribution_id NUMBER,
214 p_element_type_id NUMBER,
215 p_iv_er_id NUMBER,
216 p_coverage_type VARCHAR2,
217 p_effective_end_date DATE,
218 p_session_date DATE,
219 p_dt_delete_mode VARCHAR2,
220 p_validation_start_date DATE,
221 p_validation_end_date DATE,
222 p_element_effective_start_date DATE) IS
223 BEGIN
224 --
225 -- hr_utility.trace_on;
226 --
227 -- Call referential integrity checks
228 --
229 hr_utility.set_location('hr_ben_bc_pre_delete', 0);
230 --
231 hr_ben_benefit_contributions.hr_ben_ref_chk
232 ( p_element_type_id,
233 p_iv_er_id,
234 p_session_date,
235 p_coverage_type,
236 p_dt_delete_mode,
237 p_validation_start_date,
238 p_validation_end_date,
239 p_element_effective_start_date);
240 --
241 hr_utility.set_location('hr_ben_bc_pre_delete', 2);
242 --
243 -- end of procedure/function definitions
244 --
245 END hr_ben_bc_pre_delete;
246 --
247 --
248 --
249 -- Name hr_ben_get_coverage
250 --
251 -- Purpose
252 --
253 -- Retrieves the meaning of the coverage type
254 --
255 -- Arguments
256 --
257 -- p_coverage_type
258 --
259 -- Notes
260 --
261 -- Called from post-change of coverage_type
262 --
263 --
264 FUNCTION hr_ben_get_coverage ( p_coverage_type IN VARCHAR2 ) RETURN VARCHAR2 IS
265 --
266 -- declare local variables
267 --
268 l_coverage_type_meaning VARCHAR2(80);
269 --
270 CURSOR coverage_type_meaning IS
271 SELECT l.meaning
272 FROM hr_lookups l
273 WHERE l.lookup_type = 'US_BENEFIT_COVERAGE'
274 AND l.lookup_code = p_coverage_type;
275 --
276 BEGIN
277 --
278 -- execute cursor
279 OPEN coverage_type_meaning;
280 FETCH coverage_type_meaning INTO l_coverage_type_meaning;
281 CLOSE coverage_type_meaning;
282 --
283 RETURN l_coverage_type_meaning;
284 --
285 END hr_ben_get_coverage;
286 --
287 --
288 --
289 END hrbeebc;