[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIG_SCRE_WTG_API
Source
1 package body BEN_ELIG_SCRE_WTG_API as
2 /* $Header: beeswapi.pkb 120.2 2005/06/23 00:16:44 abparekh noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(50):= 'BEN_ELIG_SCRE_WTG_API';
7
8 procedure create_perf_score_weight
9 ( p_validate in boolean default false
10 ,p_elig_scre_wtg_id out nocopy number
11 ,p_effective_date in date
12 ,p_elig_per_id in number default null
13 ,p_elig_per_opt_id in number default null
14 ,p_elig_rslt_id in number default null
15 ,p_per_in_ler_id in number default null
16 ,p_eligy_prfl_id in number
17 ,p_crit_tab_short_name in varchar2
18 ,p_crit_tab_pk_id in number
19 ,p_computed_score in number default null
20 ,p_benefit_action_id in number default null
21 ,p_effective_start_date out nocopy date
22 ,p_effective_end_date out nocopy date
23 ,p_object_version_number out nocopy number
24 ) is
25
26 l_proc varchar2(72) := g_package||'create_perf_score_weight';
27 --
28 -- Declare cursors and local variables
29 --
30 l_object_version_number ben_elig_scre_wtg_f.object_version_number%TYPE;
31 l_elig_scre_wtg_id ben_elig_scre_wtg_f.elig_per_opt_id%TYPE;
32 l_effective_start_date ben_elig_scre_wtg_f.effective_start_date%TYPE;
33 l_effective_end_date ben_elig_scre_wtg_f.effective_end_date%TYPE;
34 --
35 l_created_by ben_elig_scre_wtg_f.created_by%TYPE;
36 l_creation_date ben_elig_scre_wtg_f.creation_date%TYPE;
37 l_last_update_date ben_elig_scre_wtg_f.last_update_date%TYPE;
38 l_last_updated_by ben_elig_scre_wtg_f.last_updated_by%TYPE;
39 --
40 Cursor C_Sel1 is select ben_elig_per_opt_f_s.nextval from sys.dual;
41
42 begin
43 --
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Issue a savepoint if operating in validation only mode
47 --
48 savepoint create_perf_score_weight;
49 --
50 -- Derive maximum start and end dates
51 --
52 l_effective_start_date := p_effective_date;
53 l_effective_end_date := hr_api.g_eot;
54 --
55 -- Insert the row
56 --
57 -- Set the object version number for the insert
58 --
59 l_object_version_number := 1;
60 --
61 ben_esw_shd.g_api_dml := true; -- Set the api dml status
65 Open C_Sel1;
62 --
63 -- Select the next sequence number
64 --
66 Fetch C_Sel1 Into l_elig_scre_wtg_id;
67 Close C_Sel1;
68 --
69 -- Insert the row into: ben_elig_per_f
70 --
71 hr_utility.set_location('Insert: '||l_proc, 5);
72 insert into ben_elig_scre_wtg_f
73 (elig_scre_wtg_id
74 ,elig_per_id
75 ,elig_per_opt_id
76 ,elig_rslt_id
77 ,per_in_ler_id
78 ,effective_start_date
79 ,effective_end_date
80 ,object_version_number
81 ,eligy_prfl_id
82 ,crit_tab_short_name
83 ,crit_tab_pk_id
84 ,computed_score
85 ,benefit_action_id
86 ,created_by
87 ,creation_date
88 ,last_update_date
89 ,last_updated_by
90 )
91 Values
92 (l_elig_scre_wtg_id
93 ,p_elig_per_id
94 ,p_elig_per_opt_id
95 ,p_elig_rslt_id
96 ,p_per_in_ler_id
97 ,l_effective_start_date
98 ,l_effective_end_date
99 ,l_object_version_number
100 ,p_eligy_prfl_id
101 ,p_crit_tab_short_name
102 ,p_crit_tab_pk_id
103 ,p_computed_score
104 ,p_benefit_action_id
105 ,l_created_by
106 ,l_creation_date
107 ,l_last_update_date
108 ,l_last_updated_by
109 );
110 hr_utility.set_location('Dn Insert: '||l_proc, 5);
111 --
112 ben_esw_shd.g_api_dml := false; -- Unset the api dml status
113 --
114 -- When in validation only mode raise the Validate_Enabled exception
115 --
116 if p_validate then
117 raise hr_api.validate_enabled;
118 end if;
119 --
120 -- Set all output arguments
121 --
122 p_elig_scre_wtg_id := l_elig_scre_wtg_id;
123 p_effective_start_date := l_effective_start_date;
124 p_effective_end_date := l_effective_end_date;
125 p_object_version_number := l_object_version_number;
126 --
127 hr_utility.set_location(' Leaving:'||l_proc, 70);
128 --
129 exception
130 --
131 when hr_api.validate_enabled then
132 --
133 -- As the Validate_Enabled exception has been raised
134 -- we must rollback to the savepoint
135 --
136 ROLLBACK TO create_perf_score_weight;
137 --
138 -- Only set output warning arguments
139 -- (Any key or derived arguments must be set to null
140 -- when validation only mode is being used.)
141 --
142 p_elig_scre_wtg_id := null;
143 p_effective_start_date := null;
144 p_effective_end_date := null;
145 p_object_version_number := null;
146 hr_utility.set_location(' Leaving:'||l_proc, 80);
147 --
148 when others then
149 --
150 -- A validation or unexpected error has occured
151 --
152 ROLLBACK TO create_perf_score_weight;
153 --
154 p_elig_scre_wtg_id := null;
155 p_effective_start_date := null;
156 p_effective_end_date := null;
157 p_object_version_number := null;
158 hr_utility.set_location(' Leaving:'||l_proc, 80);
159 --
160 raise;
161 --
162 end create_perf_score_weight;
163
164 procedure update_perf_score_weight
165 ( p_validate in boolean default false
166 ,p_elig_scre_wtg_id in number
167 ,p_effective_date in date
168 ,p_datetrack_mode in varchar2
169 ,p_benefit_action_id in number default hr_api.g_number
170 ,p_computed_score in number default hr_api.g_number
171 ,p_effective_start_date out nocopy date
172 ,p_effective_end_date out nocopy date
173 ,p_object_version_number in out nocopy number
174 ) is
175 --
176 -- Declare cursors and local variables
177 --
178 l_proc varchar2(72) := g_package||'update_score_weight';
179 l_object_version_number ben_elig_scre_wtg_f.object_version_number%TYPE;
180 l_effective_start_date ben_elig_scre_wtg_f.effective_start_date%TYPE;
181 l_effective_end_date ben_elig_scre_wtg_f.effective_end_date%TYPE;
182 --
183 begin
184 --
185 hr_utility.set_location('Entering:'|| l_proc, 10);
186 --
187 -- Issue a savepoint if operating in validation only mode
188 --
189 savepoint update_score_weight;
190 --
191 hr_utility.set_location(l_proc, 20);
192 --
193 -- Process Logic
194 --
195 l_object_version_number := p_object_version_number;
196 --
197 ben_esw_upd.upd
198 (
199 p_elig_scre_wtg_id => p_elig_scre_wtg_id
200 ,p_computed_score => p_computed_score
201 ,p_benefit_action_id => p_benefit_action_id
202 ,p_effective_start_date => l_effective_start_date
203 ,p_effective_end_date => l_effective_end_date
204 ,p_object_version_number => l_object_version_number
205 ,p_effective_date => p_effective_date
206 ,p_datetrack_mode => p_datetrack_mode
207 );
208 --
209 hr_utility.set_location(l_proc, 60);
210 --
211 -- When in validation only mode raise the Validate_Enabled exception
212 --
213 if p_validate then
214 raise hr_api.validate_enabled;
215 end if;
216 --
217 -- Set all output arguments
218 --
219 p_object_version_number := l_object_version_number;
220 p_effective_start_date := l_effective_start_date;
221 p_effective_end_date := l_effective_end_date;
222 --
223 hr_utility.set_location(' Leaving:'||l_proc, 70);
224 --
225 exception
226 --
227 when hr_api.validate_enabled then
228 --
229 -- As the Validate_Enabled exception has been raised
230 -- we must rollback to the savepoint
231 --
232 ROLLBACK TO update_score_weight;
233 --
234 -- Only set output warning arguments
235 -- (Any key or derived arguments must be set to null
236 -- when validation only mode is being used.)
237 --
238 hr_utility.set_location(' Leaving:'||l_proc, 80);
239 --
240 when others then
241 --
242 -- A validation or unexpected error has occured
243 --
244 p_effective_start_date := null;
245 p_effective_end_date := null;
246 p_object_version_number := l_object_version_number;
247
248 ROLLBACK TO update_score_weight;
249 raise;
250 --
251 end update_perf_score_weight;
252
253 procedure delete_perf_score_weight
254 ( p_validate in boolean default false
255 ,p_elig_scre_wtg_id in number
256 ,p_effective_date in date
257 ,p_datetrack_mode in varchar2
258 ,p_object_version_number in out nocopy number
259 ) is
260
261 --
262 -- Declare cursors and local variables
263 --
264 l_proc varchar2(72) := g_package||'delete_score_weight';
265 l_object_version_number ben_elig_scre_wtg_f.object_version_number%TYPE;
266 l_effective_start_date ben_elig_scre_wtg_f.effective_start_date%TYPE;
267 l_effective_end_date ben_elig_scre_wtg_f.effective_end_date%TYPE;
268 --
269 begin
270 hr_utility.set_location('Entering:'|| l_proc, 10);
271 --
272 -- Issue a savepoint if operating in validation only mode
273 --
274 savepoint delete_score_weight;
275 --
276 l_object_version_number := p_object_version_number;
277 --
278 ben_esw_del.del
279 (
280 p_elig_scre_wtg_id => p_elig_scre_wtg_id
281 ,p_effective_start_date => l_effective_start_date
282 ,p_effective_end_date => l_effective_end_date
283 ,p_object_version_number => l_object_version_number
284 ,p_effective_date => p_effective_date
285 ,p_datetrack_mode => p_datetrack_mode
286 );
287 --
288 hr_utility.set_location(l_proc, 60);
289 --
290 -- When in validation only mode raise the Validate_Enabled exception
291 --
292 if p_validate then
293 raise hr_api.validate_enabled;
294 end if;
295 --
296 p_object_version_number := l_object_version_number;
297 hr_utility.set_location(' Leaving:'||l_proc, 70);
298 --
299 exception
300 --
301 when hr_api.validate_enabled then
302 --
303 -- As the Validate_Enabled exception has been raised
304 -- we must rollback to the savepoint
305 --
306 ROLLBACK TO delete_score_weight;
307 --
308 -- Only set output warning arguments
309 -- (Any key or derived arguments must be set to null
310 -- when validation only mode is being used.)
311 --
312 -- uncommented for the nocopy
313 --
314 when others then
315 --
316 -- A validation or unexpected error has occured
317 --
318 p_object_version_number := l_object_version_number;
319 --
320 ROLLBACK TO delete_score_weight;
321 raise;
322 --
323 end delete_perf_score_weight;
324
325 procedure load_score_weight
326 ( p_validate in boolean default false
327 ,p_score_tab in ben_evaluate_elig_profiles.scoreTab
328 ,p_elig_per_id in number default null
329 ,p_elig_per_opt_id in number default null
330 ,p_elig_rslt_id in number default null
331 ,p_per_in_ler_id in number default null
332 ,p_effective_date in date
333 ) is
334
335 cursor c_sc_wg(p_eligy_prfl_id number,
336 p_elig_per_id number,
337 p_elig_per_opt_id number,
338 p_crit_tab_short_name varchar2,
339 p_crit_tab_pk_id number,
340 p_effective_date date) is
341 select *
342 from ben_elig_scre_wtg_f
343 where eligy_prfl_id = p_eligy_prfl_id
344 and crit_tab_short_name = p_crit_tab_short_name
345 and crit_tab_pk_id = p_crit_tab_pk_id
346 and nvl(elig_per_id,-1) = nvl(p_elig_per_id,-1)
347 and nvl(elig_per_opt_id,-1) = nvl(p_elig_per_opt_id,-1)
348 and nvl(elig_rslt_id,-1) = nvl(p_elig_rslt_id,-1)
349 and p_effective_date between effective_start_date
350 and effective_end_date;
351 --
352 l_sc_wg c_sc_wg%rowtype;
353 --
354 l_object_version_number number;
355 l_effective_start_date date;
356 l_effective_end_date date;
357 l_datetrack_mode varchar2(100);
358 l_correction boolean;
359 l_update boolean;
360 l_update_override boolean;
361 l_update_change_insert boolean;
362 l_elig_scre_wtg_id number;
363 i int;
364 l_proc varchar2(80) := g_package||'.load_score_weight';
365
366 begin
367
368 hr_utility.set_location('Entering : ' || l_proc, 10);
369 --
370 -- Bug 4438430
371 -- P_ELIG_PER_ID will be null for BEN_ELIG_PER_F records at PLIP level.
372 -- In BEN_ELIGIBLE_PERSON_PERF_API we defer creation of BEN_ELIG_PER_F records
373 -- at PLIP level until creation at PLN level. So for this case we will store
374 -- P_SCORE_TAB into global table BEN_ELIGIBLE_PERSON_PERF_API.G_PEPINSPLIP
375 -- In BEN_ELIGIBLE_PERSON_PERF_API.CREATE_PERF_ELIGIBLE_PERSON, we create
376 -- BEN_ELIG_SCRE_WRTG_F record after creating corresponding BEN_ELIG_PER_F record
377 --
378 if p_elig_per_id is null
379 then
380 --
381 BEN_ELIGIBLE_PERSON_PERF_API.g_pepinsplip_score_tab := p_score_tab; /* Bug 4449745 */
382 hr_utility.set_location('Defer creation of ESW Record', 9898);
383 return;
384 end if;
385 --
386 if p_score_tab.count > 0 then
387 hr_utility.set_location('wsr',11);
388 for i in 1..p_score_tab.count
389 loop
390 l_sc_wg := null;
391 open c_sc_wg(p_score_tab(i).eligy_prfl_id,
392 p_elig_per_id,
393 p_elig_per_opt_id,
394 p_score_tab(i).crit_tab_short_name,
395 p_score_tab(i).crit_tab_pk_id,
396 p_effective_date);
397 fetch c_sc_wg into l_sc_wg;
398 close c_sc_wg;
399 l_elig_scre_wtg_id := l_sc_wg.elig_scre_wtg_id;
400 hr_utility.set_location('elig_scre_wtg_id '||l_elig_scre_wtg_id,5.7);
401
402 if l_elig_scre_wtg_id is null then
403 ben_elig_scre_wtg_api.create_perf_score_weight
404 (p_validate => false
405 ,p_elig_scre_wtg_id => l_elig_scre_wtg_id
406 ,p_effective_date => p_effective_date
407 ,p_elig_per_id => p_elig_per_id
408 ,p_elig_per_opt_id => p_elig_per_opt_id
409 ,p_per_in_ler_id => p_per_in_ler_id
410 ,p_eligy_prfl_id => p_score_tab(i).eligy_prfl_id
411 ,p_crit_tab_short_name=> p_score_tab(i).crit_tab_short_name
412 ,p_crit_tab_pk_id => p_score_tab(i).crit_tab_pk_id
413 ,p_computed_score => p_score_tab(i).computed_score
414 ,p_benefit_action_id => p_score_tab(i).benefit_action_id
415 ,p_effective_start_date => l_effective_start_date
416 ,p_effective_end_date => l_effective_end_date
417 ,p_object_version_number => l_object_version_number);
418 else
419 dt_api.find_dt_upd_modes
420 (p_effective_date => p_effective_date,
421 p_base_table_name => 'BEN_ELIG_SCRE_WTG_F',
422 p_base_key_column => 'elig_scre_wtg_id',
423 p_base_key_value => l_elig_scre_wtg_id,
424 p_correction => l_correction,
425 p_update => l_update,
426 p_update_override => l_update_override,
427 p_update_change_insert => l_update_change_insert);
428
429 if l_update_override then
430 l_datetrack_mode := hr_api.g_update_override;
431 elsif l_update then
432 l_datetrack_mode := hr_api.g_update;
433 else
434 l_datetrack_mode := hr_api.g_correction;
435 end if;
436
437 ben_elig_scre_wtg_api.update_perf_score_weight
438 (p_validate => false
439 ,p_elig_scre_wtg_id => l_elig_scre_wtg_id
440 ,p_effective_date => p_effective_date
441 ,p_datetrack_mode => l_datetrack_mode
442 ,p_computed_score => p_score_tab(i).computed_score
443 ,p_benefit_action_id => p_score_tab(i).benefit_action_id
444 ,p_effective_start_date => l_effective_start_date
445 ,p_effective_end_date => l_effective_end_date
446 ,p_object_version_number => l_sc_wg.object_version_number);
447 end if;
448 end loop;
449 end if;
450 hr_utility.set_location('Leaving : ' || l_proc, 10);
451 end load_score_weight;
452
453 end BEN_ELIG_SCRE_WTG_API;