1 PACKAGE BODY per_cagr_evaluation_pkg AS
2 /* $Header: pecgrevl.pkb 120.0.12000000.2 2007/05/28 11:24:53 ande ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Package Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8
9 TYPE eligibility_rec IS RECORD (batch_elig_id number(15)
10 ,benefit_action_id number(15)
11 ,person_id number(15)
12 ,pgm_id number(15)
13 ,pl_id number(15)
14 ,oipl_id number(15)
15 ,elig_flag varchar2(30));
16
17 TYPE asg_rec IS RECORD (assignment_id number(15)
18 ,grade_id number(15)
19 ,person_id number(15));
20
21 TYPE cagr_asg_rec IS RECORD (collective_agreement_id number(15)
22 ,assignment_id number(15)
23 ,grade_id number(15)
24 ,person_id number(15));
25
26 TYPE chosen_rec IS RECORD (cagr_entitlement_item_id number(15)
27 ,cagr_entitlement_id number(15)
28 ,cagr_entitlement_line_id number(15)
29 ,value VARCHAR2(240)
30 ,grade_spine_id NUMBER(15)
31 ,parent_spine_id NUMBER(15)
32 ,step_id NUMBER(15));
33
34 TYPE eligibility_table IS TABLE OF eligibility_rec
35 INDEX BY BINARY_INTEGER;
36
37 TYPE results_table IS TABLE OF PER_CAGR_ENTITLEMENT_RESULTS%ROWTYPE
38 INDEX BY BINARY_INTEGER;
39
40 TYPE cagr_asg_table IS TABLE OF cagr_asg_rec
41 INDEX BY BINARY_INTEGER;
42
43 TYPE entitlement_items IS TABLE OF per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
44 INDEX BY BINARY_INTEGER;
45
46 TYPE assignment_table IS TABLE OF asg_rec
47 INDEX BY BINARY_INTEGER;
48
49 TYPE chosen_table IS TABLE OF chosen_rec
50 INDEX BY BINARY_INTEGER;
51 --
52 -- ----------------------------------------------------------------------------
53 -- | Package Variables (globals)
54 -- ----------------------------------------------------------------------------
55 --
56
57
58 -- define pkg pl/sql table to store cagr_entitlement_item_ids,
59 -- populated by core_process and read by new_etitlement function, called from SQL.
60 g_entitlement_items entitlement_items;
61
62 g_params control_structure;
63 g_output_structure cagr_SE_record;
64 g_pkg constant varchar2(25) := 'PER_CAGR_EVALUATION_PKG';
65 g_record_error exception;
66
67
68 --
69 -- ----------------------------------------------------------------------------
70 -- |------------------------< get_entitlement_value >--------------------------|
71 -- ----------------------------------------------------------------------------
72 --
73 -- Wrapper procedure to call initialise in single entitlement mode, returning
74 -- entitlement value in output structure. (Allows commit or rollback)
75 -- Note: The p_collective_agreement_id, p_collective_agreement_set_id are reserved for
76 -- future use - currently drives off assignment_id.
77 --
78 PROCEDURE get_entitlement_value (p_process_date in date
79 ,p_business_group_id in number
80 ,p_assignment_id in number
81 ,p_entitlement_item_id in number
82 ,p_collective_agreement_id in number default null
83 ,p_collective_agreement_set_id in number default null
84 ,p_commit_flag in varchar2 default 'N'
85 ,p_output_structure out nocopy per_cagr_evaluation_pkg.cagr_SE_record) IS
86
87 l_proc constant varchar2(80) := g_pkg || '.get_entitlement_value';
88 l_cagr_request_id number(15);
89
90 BEGIN
91 hr_utility.set_location('Entering:'||l_proc, 5);
92 -- nullify global structure
93 g_output_structure := NULL;
94
95 initialise (p_process_date => p_process_date
96 ,p_operation_mode => 'SE'
97 ,p_business_group_id => p_business_group_id
98 ,p_assignment_id => p_assignment_id
99 ,p_collective_agreement_id => NULL
100 ,p_collective_agreement_set_id => NULL
101 ,p_entitlement_item_id => p_entitlement_item_id
102 ,p_commit_flag => p_commit_flag
103 ,p_cagr_request_id => l_cagr_request_id);
104
105 -- return contents of package global structure, set by initialise
106 p_output_structure := g_output_structure;
107 p_output_structure.request_id := l_cagr_request_id;
108 --
109 hr_utility.set_location('Leaving:'||l_proc, 20);
110 --
111 END get_entitlement_value;
112
113 -- ----------------------------------------------------------------------------
114 -- |------------------------< get_mass_entitlement >--------------------------|
115 -- ----------------------------------------------------------------------------
116 --
117 -- Wrapper procedure to call initialise in batch entitlement mode (BE), returning
118 -- a pl/sql table of eligible people (and their entitlement results) for an
119 -- entitlement item within a cagr or across all cagrs.
120
121 --
122 -- Note: The param p_collective_agreement_set_id is reserved for future use
123 --
124 PROCEDURE get_mass_entitlement (p_process_date in date
125 ,p_business_group_id in number
126 ,p_entitlement_item_id in number
127 ,p_value in varchar2 default null
128 ,p_step_id in number default null
129 ,p_collective_agreement_id in number default null
130 ,p_collective_agreement_set_id in number default null
131 ,p_commit_flag in varchar2 default 'N'
132 ,p_output_structure out nocopy per_cagr_evaluation_pkg.cagr_BE_table
133 ,p_cagr_request_id out nocopy number) IS
134
135 -- return set of results for an item
136 CURSOR csr_get_results IS
137 SELECT asg.COLLECTIVE_AGREEMENT_ID
138 ,asg.ASSIGNMENT_ID
139 ,asg.PERSON_ID
140 ,res.VALUE
141 ,res.RANGE_FROM
142 ,res.RANGE_TO
143 ,res.GRADE_SPINE_ID
144 ,res.PARENT_SPINE_ID
145 ,res.STEP_ID
146 ,res.FROM_STEP_ID
147 ,res.TO_STEP_ID
148 ,res.CHOSEN_FLAG
149 ,res.BENEFICIAL_FLAG
150 FROM per_all_assignments_f asg, per_cagr_entitlement_results res
151 WHERE asg.assignment_id = res.assignment_id
152 AND asg.primary_flag = 'Y'
153 AND p_process_date between asg.effective_start_date and asg.effective_end_date
154 AND res.cagr_entitlement_item_id = p_entitlement_item_id
155 AND p_process_date between res.start_date and nvl(res.end_date, hr_general.end_of_time)
156 AND (p_collective_agreement_id is null or asg.collective_agreement_id = p_collective_agreement_id)
157 AND (p_value is null or res.value = p_value)
158 AND (p_step_id is null or res.step_id = p_step_id)
159 order by asg.PERSON_ID;
160
161 l_proc constant varchar2(80) := g_pkg || '.get_mass_entitlement';
162 l_cagr_request_id number(15);
163 l_counter number(15) := 0;
164 l_rec cagr_BE_record;
165
166 BEGIN
167 hr_utility.set_location('Entering:'||l_proc, 5);
168
169 initialise (p_process_date => p_process_date
170 ,p_operation_mode => 'BE'
171 ,p_business_group_id => p_business_group_id
172 ,p_entitlement_item_id => p_entitlement_item_id
173 ,p_collective_agreement_id => p_collective_agreement_id
174 ,p_collective_agreement_set_id => NULL
175 ,p_commit_flag => p_commit_flag
176 ,p_cagr_request_id => l_cagr_request_id);
177
178 hr_utility.set_location('Entering:'||l_proc, 10);
179
180 --
181 -- return request_id and pl/sql table of required results
182 --
183 p_cagr_request_id := l_cagr_request_id;
184 open csr_get_results;
185 loop
186 fetch csr_get_results into l_rec;
187 exit when csr_get_results%notfound;
188 l_counter := l_counter+1;
189 p_output_structure(l_counter) := l_rec;
190 end loop;
191 close csr_get_results;
192 --
193 hr_utility.set_location('Leaving:'||l_proc, 20);
194 --
195 END get_mass_entitlement;
196
197 --
198 -- ----------------------------------------------------------------------------
199 -- |--------------------------< evaluation_process >--------------------------|
200 -- ----------------------------------------------------------------------------
201 --
202 PROCEDURE evaluation_process (p_params IN OUT NOCOPY control_structure
203 ,p_SE_rec OUT NOCOPY cagr_SE_record) IS
204
205
206 --
207 -- BE Cursors
208 --
209
210 CURSOR csr_BE_drive_benmngle IS
211 SELECT item.opt_id
212 FROM per_cagr_entitlement_items item
213 WHERE item.cagr_entitlement_item_id = p_params.entitlement_item_id
214 AND exists (select 'x'
215 from per_collective_agreements cagr, per_all_assignments_f asg
216 where cagr.status = 'A'
217 and p_params.effective_date >= cagr.start_date
218 and cagr.collective_agreement_id in (select distinct(pce.collective_agreement_id)
219 from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
220 where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
221 and pce.STATUS = 'A'
222 and p_params.effective_date between pce.start_date
223 and nvl(pce.end_date,hr_general.end_of_time)
224 and pcel.STATUS = 'A'
225 and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0
226 and p_params.effective_date between pcel.effective_start_date
227 and pcel.effective_end_date
228 and pce.cagr_entitlement_item_id = p_params.entitlement_item_id
229 and (p_params.collective_agreement_id is null or
230 pce.collective_agreement_id = p_params.collective_agreement_id))
231 and cagr.collective_agreement_id = asg.collective_agreement_id
232 and asg.PRIMARY_FLAG = 'Y'
233 and p_params.effective_date BETWEEN asg.effective_start_date
234 AND asg.effective_end_date);
235
236 CURSOR csr_BE_plan is
237 SELECT agr.pl_id
238 FROM per_collective_agreements agr
239 WHERE agr.collective_agreement_id = p_params.collective_agreement_id;
240
241
242
243 CURSOR csr_BE_assignments_to_process IS
244 SELECT cagr.collective_agreement_id, asg.assignment_id, asg.grade_id, asg.person_id
245 FROM per_collective_agreements cagr, per_all_assignments_f asg
246 WHERE cagr.status = 'A'
247 AND p_params.effective_date >= cagr.start_date
248 AND cagr.collective_agreement_id in (select distinct(pce.collective_agreement_id)
249 from per_cagr_entitlements pce
250 where pce.cagr_entitlement_item_id = p_params.entitlement_item_id
251 and pce.STATUS = 'A'
252 and p_params.effective_date between pce.start_date
253 and nvl(pce.end_date,hr_general.end_of_time)
254 and (p_params.collective_agreement_id is null or
255 pce.collective_agreement_id = p_params.collective_agreement_id))
256 AND cagr.collective_agreement_id = asg.collective_agreement_id
257 AND asg.PRIMARY_FLAG = 'Y'
258 AND p_params.effective_date BETWEEN asg.effective_start_date
259 AND asg.effective_end_date
260 ORDER BY cagr.collective_agreement_id;
261
262 --
263 -- SC Cursors
264 --
265
266 CURSOR csr_SC_drive_benmngle IS
267 SELECT pl_id
268 FROM per_collective_agreements cagr
269 WHERE cagr.COLLECTIVE_AGREEMENT_ID = p_params.COLLECTIVE_AGREEMENT_ID
270 AND p_params.effective_date BETWEEN cagr.START_DATE
271 AND nvl(cagr.END_DATE, hr_general.end_of_time)
272 AND cagr.STATUS = 'A'
273 AND exists (select 'x'
274 from per_all_assignments_f asg
275 where p_params.effective_date BETWEEN asg.effective_start_date
276 AND asg.effective_end_date
277 and asg.COLLECTIVE_AGREEMENT_ID = p_params.COLLECTIVE_AGREEMENT_ID
278 and asg.PRIMARY_FLAG = 'Y')
279 AND exists (select 'x'
280 from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
281 where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
282 and pce.STATUS = 'A'
283 and p_params.effective_date between pce.start_date
284 and nvl(pce.end_date,hr_general.end_of_time)
285 and pcel.STATUS = 'A'
286 and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0
287 and p_params.effective_date between pcel.effective_start_date and pcel.effective_end_date
288 and pce.collective_agreement_id = p_params.COLLECTIVE_AGREEMENT_ID);
289
290
291 CURSOR csr_assignments_to_process IS
292 SELECT assignment_id, grade_id, person_id
293 FROM per_all_assignments_f asg
294 WHERE collective_agreement_id = p_params.collective_agreement_id
295 AND p_params.effective_date BETWEEN asg.effective_start_date
296 AND asg.effective_end_date
297 AND asg.PRIMARY_FLAG = 'Y';
298
299
300 CURSOR csr_SC_cagr_details IS
301 SELECT cagr.NAME,
302 cagr.PL_ID,
303 pce.CAGR_ENTITLEMENT_ITEM_ID,
304 pce.CAGR_ENTITLEMENT_ID,
305 pce.FORMULA_CRITERIA,
306 pce.FORMULA_ID,
307 pcei.ITEM_NAME,
308 pcei.BUSINESS_GROUP_ID,
309 pcei.FLEX_VALUE_SET_ID,
310 pcei.BENEFICIAL_RULE,
311 pcei.UOM "UNITS_OF_MEASURE",
312 pcei.CAGR_API_ID,
313 pcei.CAGR_API_PARAM_ID,
314 pcei.ELEMENT_TYPE_ID,
315 pcei.INPUT_VALUE_ID,
316 pcei.CATEGORY_NAME,
317 pcei.COLUMN_TYPE,
318 pcei.COLUMN_SIZE,
319 pcei.MULTIPLE_ENTRIES_ALLOWED_FLAG,
320 pcei.BENEFICIAL_RULE_VALUE_SET_ID,
321 pcel.CAGR_ENTITLEMENT_LINE_ID,
322 pcel.VALUE,
323 pcel.RANGE_FROM,
324 pcel.RANGE_TO,
325 pcel.GRADE_SPINE_ID,
326 pcel.PARENT_SPINE_ID,
327 pcel.STEP_ID,
328 pcel.FROM_STEP_ID,
329 pcel.TO_STEP_ID,
330 pcel.OIPL_ID,
331 pcel.ELIGY_PRFL_ID -- BEN elig profile
332 FROM per_collective_agreements cagr,
333 per_cagr_entitlements pce,
334 per_cagr_entitlement_items pcei,
335 per_cagr_entitlement_lines_f pcel
336 WHERE cagr.COLLECTIVE_AGREEMENT_ID = p_params.collective_agreement_id
337 AND p_params.effective_date BETWEEN cagr.START_DATE
338 AND nvl(cagr.END_DATE, hr_general.end_of_time)
339 AND cagr.STATUS = 'A'
340 AND cagr.COLLECTIVE_AGREEMENT_ID = pce.COLLECTIVE_AGREEMENT_ID
341 AND pce.STATUS = 'A'
342 AND p_params.effective_date BETWEEN pce.START_DATE
343 AND nvl(pce.END_DATE,hr_general.end_of_time)
344 AND pce.CAGR_ENTITLEMENT_ITEM_ID = pcei.CAGR_ENTITLEMENT_ITEM_ID
345 AND pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID (+)
346 AND ((pcel.CAGR_ENTITLEMENT_ID IS NOT NULL
347 AND p_params.effective_date BETWEEN pcel.effective_start_date
348 AND pcel.effective_end_date
349 AND pcel.STATUS = 'A'
350 OR pcel.CAGR_ENTITLEMENT_ID IS NULL))
351 ORDER BY pce.CAGR_ENTITLEMENT_ITEM_ID;
352
353
354
355 --
356 -- SA Cursors
357 --
358
359 CURSOR csr_SA_drive_benmngle IS
360 SELECT asg.COLLECTIVE_AGREEMENT_ID,
361 asg.PERSON_ID,
362 cagr.PL_ID
363 FROM per_all_assignments_f asg, per_collective_agreements cagr
364 WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
365 AND p_params.effective_date BETWEEN asg.effective_start_date
366 AND asg.effective_end_date
367 AND asg.PRIMARY_FLAG = 'Y'
368 AND asg.COLLECTIVE_AGREEMENT_ID = cagr.COLLECTIVE_AGREEMENT_ID
369 AND p_params.effective_date BETWEEN cagr.START_DATE
370 AND nvl(cagr.END_DATE, hr_general.end_of_time)
371 AND cagr.STATUS = 'A'
372 AND exists (select 'x'
373 from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
374 where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
375 and pce.STATUS = 'A'
376 and p_params.effective_date between pce.start_date
377 and nvl(pce.end_date,hr_general.end_of_time)
378 and pcel.STATUS = 'A'
379 and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0
380 and p_params.effective_date between pcel.effective_start_date and pcel.effective_end_date
381 and pce.collective_agreement_id = asg.COLLECTIVE_AGREEMENT_ID);
382
383 --
384 -- cursor to return entitlements for a given
385 -- assignment_id on the effective_date inc. default eligibility lines
386 -- note: will be converted to dynamic sql to exec diffent cursors
387 --
388 CURSOR csr_SA_cagr_ents IS
389 SELECT asg.COLLECTIVE_AGREEMENT_ID,
390 asg.GRADE_ID, -- for PYS eligibility
391 cagr.NAME,
392 cagr.PL_ID, -- BEN comp obj
393 pce.CAGR_ENTITLEMENT_ITEM_ID,
394 pce.CAGR_ENTITLEMENT_ID,
395 pce.FORMULA_CRITERIA,
396 pce.FORMULA_ID,
397 pcei.ITEM_NAME,
398 pcei.BUSINESS_GROUP_ID,
399 pcei.FLEX_VALUE_SET_ID,
400 pcei.BENEFICIAL_RULE,
401 pcei.UOM "UNITS_OF_MEASURE",
402 pcei.CAGR_API_ID, -- set for denorm item
403 pcei.CAGR_API_PARAM_ID,
404 pcei.ELEMENT_TYPE_ID,
405 pcei.INPUT_VALUE_ID,
406 pcei.CATEGORY_NAME,
407 pcei.COLUMN_TYPE,
408 pcei.COLUMN_SIZE,
409 pcei.MULTIPLE_ENTRIES_ALLOWED_FLAG,
410 pcei.BENEFICIAL_RULE_VALUE_SET_ID,
411 pcel.CAGR_ENTITLEMENT_LINE_ID,
412 pcel.VALUE,
413 pcel.RANGE_FROM,
414 pcel.RANGE_TO,
415 pcel.GRADE_SPINE_ID,
416 pcel.PARENT_SPINE_ID,
417 pcel.STEP_ID,
418 pcel.FROM_STEP_ID,
419 pcel.TO_STEP_ID,
420 pcel.OIPL_ID, -- BEN comp obj
421 pcel.ELIGY_PRFL_ID -- BEN elig profile
422 FROM per_all_assignments_f asg,
423 per_collective_agreements cagr,
424 per_cagr_entitlements pce,
425 per_cagr_entitlement_items pcei,
426 per_cagr_entitlement_lines_f pcel
427 WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
428 AND p_params.effective_date BETWEEN asg.effective_start_date
429 AND asg.effective_end_date
430 AND asg.PRIMARY_FLAG = 'Y'
431 AND asg.COLLECTIVE_AGREEMENT_ID = cagr.COLLECTIVE_AGREEMENT_ID
432 AND p_params.effective_date BETWEEN cagr.START_DATE
433 AND nvl(cagr.END_DATE, hr_general.end_of_time)
434 AND cagr.STATUS = 'A'
435 AND asg.COLLECTIVE_AGREEMENT_ID = pce.COLLECTIVE_AGREEMENT_ID
436 AND pce.STATUS = 'A'
437 AND p_params.effective_date BETWEEN pce.START_DATE
438 AND nvl(pce.END_DATE,hr_general.end_of_time)
439 AND pce.CAGR_ENTITLEMENT_ITEM_ID = pcei.CAGR_ENTITLEMENT_ITEM_ID
440 AND pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID (+)
441 AND ((pcel.CAGR_ENTITLEMENT_ID IS NOT NULL
442 AND p_params.effective_date BETWEEN pcel.effective_start_date
443 AND pcel.effective_end_date
444 AND pcel.STATUS = 'A'
445 OR pcel.CAGR_ENTITLEMENT_ID IS NULL))
446 ORDER BY pce.CAGR_ENTITLEMENT_ITEM_ID;
447
448 --
449 -- SE Cursors
450 --
451
452 CURSOR csr_SE_drive_benmngle IS
453 SELECT asg.COLLECTIVE_AGREEMENT_ID,
454 asg.PERSON_ID,
455 cagr.PL_ID
456 FROM per_all_assignments_f asg, per_collective_agreements cagr
457 WHERE asg.assignment_id = p_params.assignment_id
458 AND p_params.effective_date BETWEEN asg.effective_start_date
459 AND asg.effective_end_date
460 AND asg.PRIMARY_FLAG = 'Y'
461 AND asg.collective_agreement_id = cagr.collective_agreement_id
462 AND p_params.effective_date BETWEEN cagr.start_date
463 AND nvl(cagr.end_date, hr_general.end_of_time)
464 AND cagr.status = 'A'
465 AND exists (select 'x'
466 from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
467 where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
468 and pce.CAGR_ENTITLEMENT_ITEM_ID = p_params.entitlement_item_id -- only 1 entitlement
469 and pce.STATUS = 'A'
470 and p_params.effective_date between pce.start_date
471 and nvl(pce.end_date,hr_general.end_of_time)
472 and pcel.STATUS = 'A'
473 and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0 -- ignore default elig lines
474 and p_params.effective_date between pcel.effective_start_date
475 and pcel.effective_end_date);
476
477
478 -- Get entitlements for a given assignment_id
479 -- and entitlement_item_id on the effective_date inc. default eligibility lines
480 --
481 CURSOR csr_SE_cagr_ents IS
482 SELECT asg.COLLECTIVE_AGREEMENT_ID,
483 asg.GRADE_ID, -- for PYS eligibility
484 cagr.NAME,
485 cagr.PL_ID, -- BEN comp obj
486 pce.CAGR_ENTITLEMENT_ITEM_ID,
487 pce.CAGR_ENTITLEMENT_ID,
488 pce.FORMULA_CRITERIA,
489 pce.FORMULA_ID,
490 pcei.ITEM_NAME,
491 pcei.BUSINESS_GROUP_ID,
492 pcei.FLEX_VALUE_SET_ID,
493 pcei.BENEFICIAL_RULE,
494 pcei.UOM "UNITS_OF_MEASURE",
495 pcei.CAGR_API_ID, -- set for denorm item
496 pcei.CAGR_API_PARAM_ID,
497 pcei.ELEMENT_TYPE_ID,
498 pcei.INPUT_VALUE_ID,
499 pcei.CATEGORY_NAME,
500 pcei.COLUMN_TYPE,
501 pcei.COLUMN_SIZE,
502 pcei.MULTIPLE_ENTRIES_ALLOWED_FLAG,
503 pcei.BENEFICIAL_RULE_VALUE_SET_ID,
504 pcel.CAGR_ENTITLEMENT_LINE_ID,
505 pcel.VALUE,
506 pcel.RANGE_FROM,
507 pcel.RANGE_TO,
508 pcel.GRADE_SPINE_ID,
509 pcel.PARENT_SPINE_ID,
510 pcel.STEP_ID,
511 pcel.FROM_STEP_ID,
512 pcel.TO_STEP_ID,
513 pcel.OIPL_ID, -- BEN comp obj
514 pcel.ELIGY_PRFL_ID -- BEN elig profile
515 FROM per_all_assignments_f asg,
516 per_collective_agreements cagr,
517 per_cagr_entitlements pce,
518 per_cagr_entitlement_items pcei,
519 per_cagr_entitlement_lines_f pcel
520 WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
521 AND p_params.effective_date BETWEEN asg.effective_start_date
522 AND asg.effective_end_date
523 AND asg.PRIMARY_FLAG = 'Y'
524 AND asg.COLLECTIVE_AGREEMENT_ID = cagr.COLLECTIVE_AGREEMENT_ID
525 AND p_params.effective_date BETWEEN cagr.START_DATE
526 AND nvl(cagr.END_DATE, hr_general.end_of_time)
527 AND cagr.STATUS = 'A'
528 AND asg.COLLECTIVE_AGREEMENT_ID = pce.COLLECTIVE_AGREEMENT_ID
529 AND pce.STATUS = 'A'
530 AND pce.CAGR_ENTITLEMENT_ITEM_ID = p_params.entitlement_item_id -- only 1 entitlement
531 AND p_params.effective_date BETWEEN pce.START_DATE
532 AND nvl(pce.END_DATE,hr_general.end_of_time)
533 AND pce.CAGR_ENTITLEMENT_ITEM_ID = pcei.CAGR_ENTITLEMENT_ITEM_ID
534 AND pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID (+)
535 AND ((pcel.CAGR_ENTITLEMENT_ID IS NOT NULL
536 AND p_params.effective_date BETWEEN pcel.effective_start_date
537 AND pcel.effective_end_date
538 AND pcel.STATUS = 'A'
539 OR pcel.CAGR_ENTITLEMENT_ID IS NULL))
540 ORDER BY pce.CAGR_ENTITLEMENT_ITEM_ID;
541
542 -- SE mode check cursor
543 CURSOR csr_primary_asg is
544 SELECT 'X'
545 FROM per_all_assignments_f asg
546 WHERE asg.assignment_id = p_params.assignment_id
547 AND p_params.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
548 AND asg.primary_flag = 'Y';
549
550
551 l_cagr_FF_record hr_cagr_ff_pkg.cagr_FF_record;
552 t_eligibility_table eligibility_table;
553 v_eligibility_counter NUMBER(10) := 0;
554 t_assignments_table assignment_table; -- holds all asg ids for SC mode process
555 t_cagr_assignments_table cagr_asg_table; -- holds all cagr and asg ids for BE mode process
556 t_results_table results_table;
557 t_chosen_table chosen_table;
558 l_outputs ff_exec.outputs_t;
559 v_line_formula_id hr_assignment_sets.formula_id%TYPE;
560 v_SA_drive_benmngle csr_SA_drive_benmngle%ROWTYPE;
561 v_SE_drive_benmngle csr_SE_drive_benmngle%ROWTYPE;
562 v_counter NUMBER(10) := 0;
563 v_benefit_action_id NUMBER(15) := NULL;
564 v_ent_count NUMBER(10) := 0;
565 v_last_dataitem_id NUMBER(10) := NULL;
566 v_ben_row NUMBER(10) := 0;
567 v_beneficial_value VARCHAR2(30) := NULL;
568 v_beneficial_rule per_cagr_entitlement_items.beneficial_rule%TYPE;
569 v_beneficial_rule_vs_id per_cagr_entitlement_items.beneficial_rule_value_set_id%TYPE;
570 v_value per_cagr_entitlement_lines_f.value%TYPE;
571 v_range_from per_cagr_entitlement_lines_f.range_from%TYPE;
572 v_range_to per_cagr_entitlement_lines_f.range_to%TYPE;
573 v_grade_spine_id per_cagr_entitlement_lines_f.grade_spine_id%TYPE;
574 v_parent_spine_id per_cagr_entitlement_lines_f.parent_spine_id%TYPE;
575 v_step_id per_cagr_entitlement_lines_f.step_id%TYPE;
576 v_from_step_id per_cagr_entitlement_lines_f.from_step_id%TYPE;
577 v_to_step_id per_cagr_entitlement_lines_f.to_step_id%TYPE;
578 v_rule_inconclusive BOOLEAN := FALSE;
579 v_return BOOLEAN := FALSE;
580 v_write_flag BOOLEAN := FALSE;
581 v_primary_flag BOOLEAN := FALSE;
582 l_evaluate BOOLEAN := FALSE;
583 l_cache_checked BOOLEAN := FALSE;
584 l_update_cache BOOLEAN := FALSE;
585 l_source_name VARCHAR2(200) := NULL;
586 v_dummy VARCHAR2(1) := NULL;
587 l_last_cagr_id NUMBER(15) := -99999;
588 l_parent_request_id NUMBER(15) := NULL;
589 l_pl_id NUMBER(15) := NULL;
590 l_opt_id NUMBER(15) := NULL;
591
592
593 l_proc constant VARCHAR2(61) := g_pkg || '.evaluation_process';
594
595 resource_busy EXCEPTION;
596 pragma exception_init(resource_busy,-54);
597
598 -- ================================================================================================
599 -- == **************** STORE_CHOSEN_RESULTS ***************** ==
600 -- ================================================================================================
601 FUNCTION store_chosen_results (p_assignment_id in number
602 ,p_effective_date in date) return chosen_table IS
603 --
604 -- Populates a pl/sql table with any chosen results for all items for an assignment on the
605 -- effective_date. These results will be used to identify which of the new results for should
606 -- an item should be marked as chosen. Called before results cache is wiped in a run, if we are
607 -- committing changes.
608 --
609
610 CURSOR csr_chosen_results IS
611 SELECT cagr_entitlement_item_id,
612 cagr_entitlement_id,
613 cagr_entitlement_line_id,
614 value,
615 grade_spine_id,
616 parent_spine_id,
617 step_id
618 FROM per_cagr_entitlement_results res
619 WHERE res.assignment_id = p_assignment_id
620 AND p_effective_date between res.START_DATE and nvl(res.END_DATE, hr_general.end_of_time)
621 AND chosen_flag = 'Y';
622
623 l_proc constant VARCHAR2(81) := g_pkg || '.store_chosen_results';
624 t_chosen_table chosen_table;
625
626 BEGIN
627 hr_utility.set_location('Entering:'||l_proc, 10);
628
629 -- load index by table.
630 FOR v_chosen IN csr_chosen_results LOOP
631 t_chosen_table(csr_chosen_results%rowcount) := v_chosen;
632 END LOOP;
633
634 per_cagr_utility_pkg.put_log(' Stored '||t_chosen_table.count||' chosen results');
635
636 hr_utility.set_location('Leaving:'||l_proc, 40);
637 RETURN t_chosen_table;
638
639 END store_chosen_results;
640
641
642 -- ================================================================================================
643 -- == **************** APPLY_CHOSEN_RESULT ***************** ==
644 -- ================================================================================================
645 PROCEDURE apply_chosen_result (p_results IN OUT NOCOPY results_table
646 ,p_chosen_results IN chosen_table
647 ,p_commit_flag IN varchar2) IS
648 --
649 -- Accepts the pl/sql table of new results for an item, the pl/sql table of chosen results
650 -- and marks a new result as chosen, if previously generated and chosen and the value matches.
651 -- Called immediately before new eligibility results are written to cache table.
652 -- Note: Just exits if not committing changes.
653 --
654 l_proc constant VARCHAR2(81) := g_pkg || '.apply_chosen_result';
655 l_chosen number(10) := NULL;
656 l_chosen_rec chosen_rec;
657
658 BEGIN
659 hr_utility.set_location('Entering:'||l_proc, 10);
660
661 if p_commit_flag = 'Y' and p_results.count > 0 and p_chosen_results.count > 0 then
662
663 FOR j in p_chosen_results.first .. p_chosen_results.last LOOP
664 -- check the current entitlement_item had a previous chosen result and store it.
665 if p_chosen_results(j).cagr_entitlement_item_id = p_results(1).cagr_entitlement_item_id then
666 l_chosen_rec := p_chosen_results(j);
667 exit;
668 end if;
669 END LOOP;
670
671 hr_utility.set_location(l_proc, 20);
672
673 if l_chosen_rec.cagr_entitlement_item_id is not null then
674 -- look for a matching entitlement
675 FOR i in p_results.FIRST .. p_results.LAST LOOP
676 if p_results(i).cagr_entitlement_id = l_chosen_rec.cagr_entitlement_id then
677 -- found the chosen entitlement
678 if l_chosen_rec.cagr_entitlement_line_id is not null
679 and l_chosen_rec.cagr_entitlement_line_id = p_results(i).cagr_entitlement_line_id then
680 -- look for the exact line
681 if (p_results(i).category_name in ('ABS','PAY','ASG')
682 and p_results(i).VALUE = l_chosen_rec.VALUE)
683 or (p_results(i).category_name = 'PYS'
684 and p_results(i).GRADE_SPINE_ID = l_chosen_rec.GRADE_SPINE_ID
685 and p_results(i).PARENT_SPINE_ID = l_chosen_rec.PARENT_SPINE_ID
686 and p_results(i).STEP_ID = l_chosen_rec.STEP_ID) then
687 l_chosen := i;
688 exit;
689 end if;
690 elsif l_chosen_rec.cagr_entitlement_line_id is null then
691 -- no lines (and only one entitlement for the item may be chosen)
692 if (p_results(i).category_name in ('ABS','PAY','ASG')
693 and p_results(i).VALUE = l_chosen_rec.VALUE)
694 or (p_results(i).category_name = 'PYS'
695 and p_results(i).GRADE_SPINE_ID = l_chosen_rec.GRADE_SPINE_ID
696 and p_results(i).PARENT_SPINE_ID = l_chosen_rec.PARENT_SPINE_ID
697 and p_results(i).STEP_ID = l_chosen_rec.STEP_ID) then
698 l_chosen := i;
699 end if;
700 exit; -- exit loop anyway, whether matched or not as only one ent for the item may be chosen
701 end if;
702 end if;
703 END LOOP;
704
705 if l_chosen is not null then
706 p_results(l_chosen).chosen_flag := 'Y';
707 end if;
708
709 end if;
710 end if;
711
712 hr_utility.set_location('Leaving:'||l_proc, 40);
713
714 END apply_chosen_result;
715
716
717 -- ================================================================================================
718 -- == **************** CHECK_CACHE ***************** ==
719 -- ================================================================================================
720 FUNCTION check_cache (p_assignment_id in number
721 ,p_cagr_id in number
722 ,p_entitlement_item_id in number
723 ,p_effective_date in date) return cagr_SE_record IS
724 --
725 -- Determines if any entitlement result records exist in the cache for the item/cagr/asg/date
726 -- combination, and returns a structure holding the most beneficial or error string in structure
727 -- If results are found, but none is marked 'most beneficial', return error 'HR_289578_CAGR_NO_BENEFICIAL'
728 -- If no results are found, return error 'HR_289577_CAGR_NO_DATA_FOUND'
729 --
730
731 -- get beneficial result from cache for the asg - cagr - item - date
732 CURSOR csr_get_results IS
733 SELECT erl.VALUE
734 ,erl.RANGE_FROM
735 ,erl.RANGE_TO
736 ,erl.GRADE_SPINE_ID
737 ,erl.PARENT_SPINE_ID
738 ,erl.STEP_ID
739 ,erl.FROM_STEP_ID
740 ,erl.TO_STEP_ID
741 ,erl.BENEFICIAL_FLAG
742 FROM per_cagr_entitlement_results erl
743 WHERE erl.ASSIGNMENT_ID = p_assignment_id
744 AND erl.COLLECTIVE_AGREEMENT_ID = p_cagr_id
745 AND erl.CAGR_ENTITLEMENT_ITEM_ID = p_entitlement_item_id
746 AND p_effective_date BETWEEN erl.START_DATE
747 AND nvl(erl.END_DATE,hr_general.end_of_time);
748
749 l_rec cagr_SE_record;
750 l_beneficial_flag per_cagr_entitlement_results.beneficial_flag%TYPE;
751 l_no_results BOOLEAN := TRUE;
752 l_proc constant VARCHAR2(61) := g_pkg || '.check_cache';
753
754 BEGIN
755
756 hr_utility.set_location('Entering:'||l_proc, 10);
757
758 -- query result record from cache for the entitlement item
759 -- into the return SE structure
760 open csr_get_results;
761 loop
762 fetch csr_get_results into l_rec.VALUE,
763 l_rec.RANGE_FROM,
764 l_rec.RANGE_TO,
765 l_rec.GRADE_SPINE_ID,
766 l_rec.PARENT_SPINE_ID,
767 l_rec.STEP_ID,
768 l_rec.FROM_STEP_ID,
769 l_rec.TO_STEP_ID,
770 l_beneficial_flag;
771 exit when csr_get_results%notfound;
772 if l_beneficial_flag = 'Y' then
773 exit;
774 end if;
775 end loop;
776 if csr_get_results%rowcount > 0 then
777 l_no_results := FALSE; -- we found result lines (even if none are beneficial)
778 end if;
779 close csr_get_results;
780
781 if l_no_results then
782 l_rec.error := 'HR_289577_CAGR_NO_DATA_FOUND';
783 else
784 if l_beneficial_flag is NULL then
785 l_rec := NULL; -- clear out result
786 l_rec.error := 'HR_289578_CAGR_NO_BENEFICIAL';
787 end if;
788 end if;
789 per_cagr_utility_pkg.put_log(' check_cache reports: '||nvl(l_rec.error,'RESULTS EXIST'));
790
791 hr_utility.set_location('Leaving:'||l_proc, 50);
792 RETURN l_rec;
793
794 END check_cache;
795
796 -- ================================================================================================
797 -- == **************** get_PYS_grade_id ***************** ==
798 -- ================================================================================================
799
800 FUNCTION get_PYS_grade_id (p_grade_spine_id in NUMBER
801 ,p_effective_date in DATE) return NUMBER IS
802
803 -- Accept grade_spine_id and effective date and return the grade_id. Used for PYS category
804 -- criteria eligibility determination in addition to satisfying eligibility profile.
805
806 CURSOR csr_grade_spines IS
807 SELECT gs.grade_id
808 FROM per_grade_spines_f gs
809 WHERE gs.grade_spine_id = p_grade_spine_id
810 AND p_effective_date between gs.effective_start_date and gs.effective_end_date;
811
812 l_proc constant VARCHAR2(60) := g_pkg || '.get_PYS_grade_id';
813 l_grade_id per_grade_spines_f.grade_id%TYPE := NULL;
814
815 BEGIN
816
817 hr_utility.set_location('Entering:'||l_proc, 10);
818 open csr_grade_spines;
819 fetch csr_grade_spines into l_grade_id;
820 close csr_grade_spines;
821
822 hr_utility.set_location('Leaving:'||l_proc, 50);
823 RETURN l_grade_id;
824
825 END get_PYS_grade_id;
826
827 -- ================================================================================================
828 -- == **************** WRITE_RESULTS ***************** ==
829 -- ================================================================================================
830
831 PROCEDURE write_results (p_structure IN results_table
832 ,p_cagr_request_id IN NUMBER
833 ,p_effective_date IN DATE
834 ,p_end_date IN DATE) IS
835
836 -- Accept a structure containing processed entitlement results and
837 -- loop through it writing each record to the PER_CAGR_ENTITLEMENT_RESULTS table
838 -- inserting a new key value for each record from sequence.
839 -- (could use bulk binding of index-by table of records in 9i, to aid performance).
840 -- NOTE: called from insert_result_set, update_result_set
841
842 l_proc constant VARCHAR2(61) := g_pkg || '.write_result_records';
843 l_num NUMBER(15) := 0;
844 l_ovn NUMBER(11) := 1; -- default to 1
845
846 BEGIN
847
848 hr_utility.set_location('Entering:'||l_proc, 10);
849
850 FOR i in p_structure.FIRST..p_structure.LAST LOOP
851 -- write detail records to table
852 INSERT INTO per_cagr_entitlement_results(CAGR_ENTITLEMENT_RESULT_ID
853 ,CAGR_REQUEST_ID
854 ,START_DATE
855 ,END_DATE
856 ,COLLECTIVE_AGREEMENT_ID
857 ,CAGR_ENTITLEMENT_ITEM_ID
858 ,ELEMENT_TYPE_ID
859 ,INPUT_VALUE_ID
860 ,CAGR_API_ID
861 ,CAGR_API_PARAM_ID
862 ,CATEGORY_NAME
863 ,CAGR_ENTITLEMENT_ID
864 ,CAGR_ENTITLEMENT_LINE_ID
865 ,ASSIGNMENT_ID
866 ,VALUE
867 ,UNITS_OF_MEASURE
868 ,RANGE_FROM
869 ,RANGE_TO
870 ,GRADE_SPINE_ID
871 ,PARENT_SPINE_ID
872 ,STEP_ID
873 ,FROM_STEP_ID
874 ,TO_STEP_ID
875 ,BENEFICIAL_FLAG
876 ,OIPL_ID
877 ,ELIGY_PRFL_ID
878 ,FORMULA_ID
879 ,CHOSEN_FLAG
880 ,COLUMN_TYPE
881 ,COLUMN_SIZE
882 ,MULTIPLE_ENTRIES_ALLOWED_FLAG
883 ,BUSINESS_GROUP_ID
884 ,FLEX_VALUE_SET_ID
885 ,RETAINED_ENT_RESULT_ID
886 ,OBJECT_VERSION_NUMBER)
887 VALUES (PER_CAGR_ENTITLEMENT_RESULTS_S.nextval
888 ,p_cagr_request_id
889 ,p_effective_date
890 ,p_end_date
891 ,p_structure(i).COLLECTIVE_AGREEMENT_ID
892 ,p_structure(i).CAGR_ENTITLEMENT_ITEM_ID
893 ,p_structure(i).ELEMENT_TYPE_ID
894 ,p_structure(i).INPUT_VALUE_ID
895 ,p_structure(i).CAGR_API_ID
896 ,p_structure(i).CAGR_API_PARAM_ID
897 ,p_structure(i).CATEGORY_NAME
898 ,p_structure(i).CAGR_ENTITLEMENT_ID
899 ,p_structure(i).CAGR_ENTITLEMENT_LINE_ID
900 ,p_structure(i).ASSIGNMENT_ID
901 ,p_structure(i).VALUE
902 ,p_structure(i).UNITS_OF_MEASURE
903 ,p_structure(i).RANGE_FROM
904 ,p_structure(i).RANGE_TO
905 ,p_structure(i).GRADE_SPINE_ID
906 ,p_structure(i).PARENT_SPINE_ID
907 ,p_structure(i).STEP_ID
908 ,p_structure(i).FROM_STEP_ID
909 ,p_structure(i).TO_STEP_ID
910 ,p_structure(i).BENEFICIAL_FLAG
911 ,p_structure(i).OIPL_ID
912 ,p_structure(i).ELIGY_PRFL_ID
913 ,p_structure(i).FORMULA_ID
914 ,p_structure(i).CHOSEN_FLAG
915 ,p_structure(i).COLUMN_TYPE
916 ,p_structure(i).COLUMN_SIZE
917 ,p_structure(i).MULTIPLE_ENTRIES_ALLOWED_FLAG
918 ,p_structure(i).BUSINESS_GROUP_ID
919 ,p_structure(i).FLEX_VALUE_SET_ID
920 ,p_structure(i).RETAINED_ENT_RESULT_ID
921 ,l_ovn);
922 l_num := l_num +1;
923 END LOOP;
924 per_cagr_utility_pkg.put_log(' Created '||l_num||' entitlement result records for the item ',1);
925 per_cagr_utility_pkg.put_log(' item_id : ' ||p_structure(1).CAGR_ENTITLEMENT_ITEM_ID);
926
927 hr_utility.set_location('Leaving:'||l_proc, 30);
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 per_cagr_utility_pkg.put_log(' Failed to write result record',1);
932 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
933
934
935 END write_results;
936
937 -- ================================================================================================
938 -- == **************** insert_result_set ***************** ==
939 -- ================================================================================================
940 PROCEDURE insert_result_set (p_structure IN results_table
941 ,p_params IN control_structure) IS
942
943 --
944 -- Accept table of result records for an item and insert new records into the cache table
945 -- starting on the effctive date (end_date may be EOT or start_date -1 of any future result)
946 --
947 -- (Calls write_results to insert new set as no results exist for the item-asg-date combination.)
948 --
949
950 -- test if any future result(s) exists for the item
951 -- and return the start_date of earliest future result set
952 CURSOR csr_future_results IS
953 SELECT min(er.start_date)
954 FROM per_cagr_entitlement_results er
955 WHERE er.cagr_entitlement_item_id = p_params.entitlement_item_id
956 AND er.assignment_id = p_params.assignment_id
957 AND p_params.effective_date < er.START_DATE;
958
959 l_proc constant VARCHAR2(61) := g_pkg || '.insert_result_set';
960 l_num NUMBER(11) := 0;
961 v_future_start_date DATE := NULL;
962 v_end_date DATE := NULL;
963
964
965 BEGIN
966 hr_utility.set_location('Entering:'||l_proc, 10);
967 per_cagr_utility_pkg.put_log(' Executing insert_result_set');
968
969 open csr_future_results;
970 fetch csr_future_results into v_future_start_date;
971 close csr_future_results;
972 if v_future_start_date is not null then
973 v_end_date := v_future_start_date -1;
974 end if;
975
976 per_cagr_utility_pkg.put_log(' Start Date: '||p_params.effective_date||', End Date: '||v_end_date);
977
978 write_results(p_structure,p_params.cagr_request_id,p_params.effective_date,v_end_date);
979
980 per_cagr_utility_pkg.put_log(' Completed insert_result_set');
981 hr_utility.set_location('Leaving:'||l_proc, 30);
982
983 END insert_result_set;
984
985 -- ================================================================================================
986 -- == **************** update_result_set ***************** ==
987 -- ================================================================================================
988
989 PROCEDURE update_result_set (p_structure IN results_table
990 ,p_params in control_structure
991 ,p_switch IN VARCHAR2) IS
992 --
993 -- This routine performs two distinct functions, controlled by params supplied:
994 -- 1) 'Clean' results cache by end_dating (to eff_date - 1) all item results for the asg that are
995 -- found on effective_date. This is used prior to starting a new engine run (except SE or BE)
996 -- or when cagr has been removed (nullified) from the asg.
997 -- 2) 'Write' calls write_results to write new records when 'updating' existing records for an item.
998 -- This is used by all modes (but check_cache determines whether called by SE or BE).
999 --
1000
1001 -- update all results for any item found in cache
1002 -- taking exclusive lock out, with nowait, will hang until rows is freed,
1003 -- but this is NOT used by 'SE' mode
1004 CURSOR csr_all_results (v_assignment_id in number) IS
1005 SELECT er.start_date, er.cagr_request_id
1006 FROM per_cagr_entitlement_results er
1007 WHERE er.assignment_id = v_assignment_id
1008 AND p_params.effective_date BETWEEN er.START_DATE AND nvl(er.END_DATE,hr_general.END_OF_TIME)
1009 ORDER BY er.cagr_request_id
1010 FOR UPDATE OF END_DATE NOWAIT;
1011
1012 -- update all results for a specific item found in cache
1013 -- taking exclusive lock out - used by SE mode only, so uses param and nowait option
1014 CURSOR csr_item_results IS
1015 SELECT er.start_date, er.cagr_request_id
1016 FROM per_cagr_entitlement_results er
1017 WHERE er.assignment_id = p_params.assignment_id
1018 AND er.cagr_entitlement_item_id = p_params.entitlement_item_id
1019 AND p_params.effective_date BETWEEN er.START_DATE AND nvl(er.END_DATE,hr_general.END_OF_TIME)
1020 FOR UPDATE OF END_DATE NOWAIT;
1021
1022 -- test if any future result(s) exists for the item and asg
1023 -- and return the start_date of earliest future result set
1024 CURSOR csr_future_results (v_entitlement_item_id in NUMBER
1025 ,v_assignment_id in NUMBER) IS
1026 SELECT min(er.start_date)
1027 FROM per_cagr_entitlement_results er
1028 WHERE er.cagr_entitlement_item_id = v_entitlement_item_id
1029 AND er.assignment_id = v_assignment_id
1030 AND p_params.effective_date < er.START_DATE;
1031
1032 TYPE request_table IS TABLE OF per_cagr_entitlement_results.cagr_request_id%TYPE INDEX BY BINARY_INTEGER;
1033
1034 e_resource_busy exception;
1035 pragma exception_init(e_resource_busy,-00054);
1036
1037 l_proc constant VARCHAR2(61) := g_pkg || '.update_result_set';
1038 v_future_start_date DATE := NULL;
1039 v_end_date DATE := NULL;
1040 v_start_date DATE := NULL;
1041 v_assignment_id NUMBER(15);
1042 v_cagr_request_id NUMBER(15);
1043 v_delete_cagr_request_id NUMBER(15);
1044 i NUMBER(11) := 0;
1045 t_cagr_request request_table;
1046
1047 BEGIN
1048 hr_utility.set_location('Entering:'||l_proc, 10);
1049 per_cagr_utility_pkg.put_log(' Preparing results cache',2);
1050
1051 if p_switch = 'W' then
1052 -- we are 'updating' results for a specific item:
1053 -- previous results have been cleaned by csr_all_results, below (except SE mode which we do here)
1054 -- insert a new set of lines having start_date = effective_date, and end_date = EOT or
1055 -- future updates.start_date -1 if future updates exist
1056
1057 if p_params.operation_mode in ('SE','BE') then
1058 -- first tidy up existing records for a specific item for 'SE' or 'BE' mode
1059 -- as these modes do not automatically clean all results at startup, unlike 'SA' or 'SC'
1060
1061 open csr_item_results;
1062 fetch csr_item_results into v_start_date, v_delete_cagr_request_id;
1063 if v_start_date < p_params.effective_date then
1064 -- end date the record, and all others
1065 update per_cagr_entitlement_results set end_date = p_params.effective_date -1
1066 where current of csr_item_results;
1067 loop
1068 fetch csr_item_results into v_start_date, v_cagr_request_id;
1069 exit when csr_item_results%notfound;
1070 update per_cagr_entitlement_results set end_date = p_params.effective_date -1
1071 where current of csr_item_results;
1072 end loop;
1073 elsif v_start_date = p_params.effective_date then
1074 -- delete records which started today
1075 delete from per_cagr_entitlement_results
1076 where current of csr_item_results;
1077 loop
1078 fetch csr_item_results into v_start_date, v_cagr_request_id;
1079 exit when csr_item_results%notfound;
1080
1081 delete from per_cagr_entitlement_results
1082 where current of csr_item_results;
1083 end loop;
1084 -- as we have deleted an entitlement result, also delete any
1085 -- log entries, for the results request_id.
1086 -- (we use the first fetched request_id, as it doesn't change).
1087 per_cagr_utility_pkg.remove_log_entries(v_delete_cagr_request_id);
1088 end if;
1089 close csr_item_results;
1090 end if;
1091
1092 open csr_future_results(p_structure(1).cagr_entitlement_item_id,
1093 p_structure(1).assignment_id);
1094 fetch csr_future_results into v_future_start_date;
1095 close csr_future_results;
1096 if v_future_start_date is not null then
1097 v_end_date := v_future_start_date -1;
1098 end if;
1099
1100 per_cagr_utility_pkg.put_log(' New results have Start Date: '||p_params.effective_date||', End Date: '||v_end_date,2);
1101 -- now insert new set of results for the item
1102 write_results(p_structure,p_params.cagr_request_id,p_params.effective_date,v_end_date);
1103
1104 elsif p_switch = 'C' then
1105 -- clean results cache for all items for the asg, when starting processing for a cagr or when cagr removed from asg:
1106 -- delete any existing result records that started on effective_date, and call remove_log_entries
1107 -- end date any result records that started before effective_date (not used by SE mode)
1108
1109 if p_params.operation_mode in ('SA','SC') then
1110 -- use asg id param, as this is only asg processed in this mode
1111 v_assignment_id := p_params.assignment_id;
1112 elsif p_params.operation_mode = 'BA' then
1113 -- i.e. take the asg_id for the current item set
1114 -- which may change as we process different assignments
1115 v_assignment_id := p_structure(1).assignment_id;
1116 end if;
1117 per_cagr_utility_pkg.put_log(' Cleaning previous cache results found for asg id: '||v_assignment_id,2);
1118
1119 open csr_all_results(v_assignment_id);
1120 loop
1121 fetch csr_all_results into v_start_date, v_cagr_request_id;
1122 exit when csr_all_results%notfound;
1123 if v_start_date < p_params.effective_date then
1124 -- end date the record, and all others
1125 update per_cagr_entitlement_results set end_date = p_params.effective_date -1
1126 where current of csr_all_results;
1127 elsif v_start_date = p_params.effective_date then
1128 -- delete records which started today, and store the request_id
1129 delete from per_cagr_entitlement_results
1130 where current of csr_all_results;
1131 if v_delete_cagr_request_id is null then
1132 v_delete_cagr_request_id := v_cagr_request_id;
1133 i := i + 1;
1134 t_cagr_request(i) := v_delete_cagr_request_id;
1135 elsif v_delete_cagr_request_id <> v_cagr_request_id then
1136 v_delete_cagr_request_id := v_cagr_request_id;
1137 i := i + 1;
1138 t_cagr_request(i) := v_delete_cagr_request_id;
1139 end if;
1140 end if;
1141 end loop;
1142 close csr_all_results;
1143
1144 if t_cagr_request.count > 0 then
1145 for j in 1 .. t_cagr_request.last loop
1146 -- as we have deleted an entitlement result, also delete any log entries, for the result's request_id.
1147 per_cagr_utility_pkg.remove_log_entries(t_cagr_request(j));
1148 end loop;
1149 end if;
1150
1151 end if;
1152 per_cagr_utility_pkg.put_log(' Completed preparing results cache.',2);
1153
1154 hr_utility.set_location('Leaving:'||l_proc, 50);
1155
1156 EXCEPTION
1157 WHEN e_resource_busy THEN
1158 -- raise resource busy message.
1159 per_cagr_utility_pkg.put_log(' ERROR: Another user is updating the entitlement results for the assignment.',1);
1160 per_cagr_utility_pkg.put_log(' Unable to lock result records exclusively. Please try again later.',1);
1161 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1162 fnd_message.set_token('TABLE_NAME', 'per_cagr_entitlement_results');
1163 fnd_message.raise_error;
1164
1165 END update_result_set;
1166
1167
1168 -- ================================================================================================
1169 -- == **************** PROCESS_ENTITLEMENT_LINES ***************** ==
1170 -- ================================================================================================
1171 PROCEDURE process_entitlement_lines (p_pl_id IN NUMBER
1172 ,p_opt_id IN NUMBER
1173 ,p_person_id IN NUMBER
1174 ,p_benefit_action_id OUT NOCOPY NUMBER
1175 ,p_effective_date IN DATE
1176 ,p_bg_id IN NUMBER) IS
1177 -- Either:
1178 -- Accept a person_id, collective_agreement_id (plan_id) and invoke benmngle
1179 -- to evaluate all eligibility profiles for the single the entitlement_line (option).
1180 -- or
1181 -- Accept a person_id, collective_agreement_id (plan_id) and entitlement_line_id (option) and
1182 -- invoke benmngle to evaluate all eligibility profiles for the single the entitlement_line (option).
1183 -- (Assuming its faster to evaluate specific ent lines (options) individually for one entitlement,
1184 -- during Single Entitlement mode, when we do not need all lines for all entitlements to be processed.)
1185
1186 --
1187 -- p_benefit_action_id is set upon successful completion.
1188 --
1189
1190 -- Note: person_id restricts benmngle to eval the options eligibility for the current person only,
1191 -- else all eligibilities for current plan, for all people, will be evaluated if person_id is null.
1192
1193 l_proc constant VARCHAR2(61) := g_pkg || '.' || 'process_entitlement_lines';
1194
1195 l_errbuf varchar2(80);
1196 l_retcode number;
1197 l_validate_flag ben_benefit_actions.validate_flag%TYPE := 'Y';
1198 l_derivable_factors_flag ben_benefit_actions.derivable_factors_flag%TYPE := 'ASC';
1199 l_mode ben_benefit_actions.mode_cd%TYPE := 'A';
1200 l_benefit_action_id ben_benefit_actions.benefit_action_id%TYPE := NULL;
1201
1202 l_ben_count NUMBER;
1203
1204 pragma autonomous_transaction;
1205
1206 BEGIN
1207 hr_utility.set_location('Entering:'||l_proc, 10);
1208
1209 per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1210 per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1211 per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212 per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213 per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214 per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215 per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216 per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1217 per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1218 per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1219
1220 ben_manage_life_events.internal_process
1221 (errbuf => l_errbuf,
1222 retcode => l_retcode,
1223 p_benefit_action_id => l_benefit_action_id,
1224 p_effective_date => fnd_date.date_to_canonical(p_effective_date),
1225 p_mode => l_mode,
1226 p_derivable_factors => l_derivable_factors_flag,
1227 p_validate => l_validate_flag,
1228 p_person_id => p_person_id,
1229 p_business_group_id => p_bg_id,
1230 p_pl_id => p_pl_id,
1231 p_opt_id => p_opt_id,
1232 p_cagr_id => p_params.collective_agreement_id, -- Bug # 5391298
1233 p_commit_data => 'Y',
1234 p_audit_log_flag => 'Y');
1235
1236 Commit;
1237
1238 per_cagr_utility_pkg.put_log('Completed benmngle at: '||
1239 fnd_date.date_to_canonical(sysdate)||' return code is :'||to_char(l_retcode));
1240 per_cagr_utility_pkg.put_log('benefit_action_id: '|| to_char(l_benefit_action_id));
1241 p_benefit_action_id := l_benefit_action_id;
1242
1243 hr_utility.set_location('Leaving:'||l_proc, 30);
1244
1245 EXCEPTION
1246 when others then
1247 Rollback;
1248 hr_utility.set_location('Fatal Error: '||l_proc, 20);
1249 per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1250 per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1251 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1252 raise;
1253
1254 END process_entitlement_lines;
1255
1256 -- ================================================================================================
1257 -- == **************** GET_BEN_ELIGIBILITY_INFO ***************** ==
1258 -- ================================================================================================
1259 PROCEDURE get_BEN_eligibility_info (p_benefit_action_id IN NUMBER
1260 ,p_eligibility_table OUT NOCOPY eligibility_table
1261 ,p_counter OUT NOCOPY NUMBER) IS
1262
1263 -- Retrieve the processed eligibility data from BEN table, and translate it into
1264 -- structure of type eligibility_table for use in CAGR engine.
1265 -- rec structure is: BEN_ACTION_ID | PERSON_ID | PGM_ID | PL_ID | OIPL_ID | ELIG_FLAG
1266 -- (Called immediately after ben completes).
1267
1268 CURSOR csr_get_elig_ent_lines is
1269 SELECT batch_elig_id,
1270 BENEFIT_ACTION_ID,
1271 PERSON_ID,
1272 PGM_ID,
1273 PL_ID,
1274 OIPL_ID,
1275 ELIG_FLAG
1276 from ben_batch_elig_info bbe
1277 where bbe.BENEFIT_ACTION_ID = p_benefit_action_id
1278 and bbe.OIPL_ID is not null;
1279
1280 l_proc constant VARCHAR2(61) := g_pkg || '.' || 'get_BEN_eligibility_info';
1281
1282 BEGIN
1283 hr_utility.set_location('Entering:'||l_proc, 10);
1284 p_counter := 0;
1285 if p_benefit_action_id is not null then
1286 -- create the structure which will be used by main code, to show eligibility result for
1287 -- an entitlement line...
1288 for v_elig_lines in csr_get_elig_ent_lines loop
1289 p_counter := p_counter +1;
1290 p_eligibility_table(p_counter).BATCH_ELIG_ID := v_elig_lines.BATCH_ELIG_ID;
1291 p_eligibility_table(p_counter).BENEFIT_ACTION_ID := v_elig_lines.BENEFIT_ACTION_ID;
1292 p_eligibility_table(p_counter).PERSON_ID := v_elig_lines.PERSON_ID;
1293 p_eligibility_table(p_counter).PGM_ID := v_elig_lines.PGM_ID;
1294 p_eligibility_table(p_counter).PL_ID := v_elig_lines.PL_ID;
1295 p_eligibility_table(p_counter).OIPL_ID := v_elig_lines.OIPL_ID;
1296 p_eligibility_table(p_counter).ELIG_FLAG := v_elig_lines.ELIG_FLAG;
1297 end loop;
1298 end if;
1299 per_cagr_utility_pkg.put_log('Benmngle created '||to_char(p_counter)||' positive eligibility records');
1300
1301 hr_utility.set_location('Leaving:'||l_proc, 30);
1302
1303 END get_BEN_eligibility_info;
1304
1305
1306 -- ================================================================================================
1307 -- == **************** check_entitlement_eligible ***************** ==
1308 -- ================================================================================================
1309 FUNCTION check_entitlement_eligible (p_person_id in NUMBER default NULL
1310 ,p_oipl_id in NUMBER
1311 ,p_eligibility_table in eligibility_table) RETURN BOOLEAN IS
1312
1313 -- Loop through the BEN eligibility pl/sql table to identify the eligibility result for a
1314 -- specific entitlement_line (option in plan) for a person.
1315 -- Returns: TRUE if eligible, otherwise false.
1316
1317 -- (Raise error if a result for p_oipl_id is not found in pl/sql table, as
1318 -- this may indicate possible data setup or benmngle problem).
1319
1320 v_found BOOLEAN := FALSE;
1321 l_proc constant VARCHAR2(61) := g_pkg || '.' || 'check_entitlement_eligible';
1322
1323 BEGIN
1324 hr_utility.set_location('Entering:'||l_proc, 10);
1325
1326 if p_oipl_id is null then
1327 per_cagr_utility_pkg.put_log(' ERROR: Option in plan id is null for criteria line ',1);
1328 hr_utility.set_message(800, 'HR_289415_CAGR_OIPL_NULL');
1329 hr_utility.raise_error;
1330 end if;
1331
1332 if p_person_id is null then
1333 -- reading table for SE or SA mode
1334 for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1335 if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1336 v_found := TRUE;
1337 if p_eligibility_table(i).ELIG_FLAG = 'Y' then
1338 per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1339 return TRUE;
1340 end if;
1341 end if;
1342 end loop;
1343 else
1344 -- reading table for SC or BE mode, using person context
1345 for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1346 if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1347 v_found := TRUE;
1348 if p_eligibility_table(i).PERSON_ID = p_person_id and p_eligibility_table(i).ELIG_FLAG = 'Y' then
1349 per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1350 return TRUE;
1351 end if;
1352 end if;
1353 end loop;
1354 end if;
1355
1356 if v_found = FALSE then
1357 per_cagr_utility_pkg.put_log(' ERROR: Option in plan id does not exist ',1);
1358 hr_utility.set_message(800, 'HR_289416_CAGR_OIPL_NOT_FOUND');
1359 hr_utility.raise_error;
1360 end if;
1361
1362 per_cagr_utility_pkg.put_log(' The criteria eligibility profile is not satisfied.',1);
1363 hr_utility.set_location('Leaving:'||l_proc, 50);
1364 return FALSE;
1365
1366 END check_entitlement_eligible;
1367
1368
1369 -- ================================================================================================
1370 -- == **************** SET_BENEFICIAL_VALUE ***************** ==
1371 -- ================================================================================================
1372
1373 PROCEDURE set_beneficial_value (p_effective_date in DATE
1374 ,p_results_table in out NOCOPY results_table
1375 ,p_ben_rule in VARCHAR2 -- hi/lo/accumulate
1376 ,p_ben_rule_vs_id in NUMBER
1377 ,p_ben_value out nocopy NUMBER -- return value
1378 ,p_ben_row out nocopy NUMBER -- index
1379 ,p_rule_inconclusive out nocopy BOOLEAN) IS
1380
1381 -- Accepts table of result records for an item, identifies (and sets) the most beneficial record
1382 -- based on allowed behaviour for the category of the item, and the item's hi-lo rule.
1383 -- Returns beneficial_value, beneficial_row, rule_inconclusive flag, and sets most beneficial record.
1384 -- Supports PAY, ASG, PYS, ABS data categories only. (PAY,ASG,ABS use value, PYS uses step_id).
1385 -- Value data may be numeric, varchar, date types.
1386 -- If p_beneficial_rule_vs_id is not null then beneficial rule is applied to the corresponding values
1387 -- returned by the data column for the result ids, not the result ids (values) themselves.
1388 -- If a data setup error occurs v_rule_inconclusive is set to TRUE.
1389
1390 TYPE dyn_rec IS RECORD (char_col varchar2(30)
1391 ,num_col number(15)
1392 ,date_col date);
1393
1394 TYPE dyn_rec_table IS TABLE OF dyn_rec INDEX BY BINARY_INTEGER;
1395 TYPE dyn_csr IS REF CURSOR; -- define cursor ref type
1396
1397 -- get value set data column type
1398 CURSOR csr_data (vs_id NUMBER) IS
1399 SELECT value_column_type
1400 FROM fnd_flex_validation_tables
1401 WHERE flex_value_set_id = vs_id;
1402
1403 l_dyn_csr dyn_csr;
1404 l_dyn_csr_table dyn_rec_table;
1405 l_proc constant VARCHAR2(80) := g_pkg || '.set_beneficial_value';
1406 l_category VARCHAR2(30);
1407 l_sql VARCHAR(2000) := NULL;
1408 l_ben_row NUMBER := NULL;
1409 l_sequence NUMBER := 0;
1410 l_id NUMBER := 0;
1411 l_num NUMBER := 0;
1412 l_list_str VARCHAR(2000) := NULL;
1413 l_ben_field VARCHAR2(30);
1414 l_char VARCHAR2(30);
1415 l_date DATE;
1416 l_error BOOLEAN := FALSE;
1417 l_col_data_type VARCHAR2(30);
1418
1419 --
1420 PROCEDURE do_date_beneficial (p_input_table in dyn_rec_table
1421 ,p_rule in varchar2
1422 ,p_row out nocopy number) IS
1423 l_proc constant VARCHAR2(80) := g_pkg || '.do_date_beneficial';
1424 l_ben_val date;
1425 BEGIN
1426 hr_utility.set_location('Entering:'||l_proc, 10);
1427 for i in p_input_table.first..p_input_table.last loop
1428 if i = 1 then
1429 l_ben_val := p_input_table(i).date_col;
1430 p_row := i;
1431 elsif i > 1 then
1432 if p_rule = 'HI' then
1433 if p_input_table(i).date_col > l_ben_val then
1434 l_ben_val := p_input_table(i).date_col;
1435 p_row := i;
1436 elsif p_input_table(i).date_col = l_ben_val then
1437 p_row := NULL;
1438 end if;
1439 elsif p_rule = 'LO' then
1440 if p_input_table(i).date_col < l_ben_val then
1441 l_ben_val := p_input_table(i).date_col;
1442 p_row := i;
1443 elsif p_input_table(i).date_col = l_ben_val then
1444 p_row := NULL;
1445 end if;
1446 end if;
1447 end if;
1448 end loop;
1449 hr_utility.set_location('Leaving:'||l_proc, 20);
1450 END do_date_beneficial;
1451 --
1452 PROCEDURE do_num_beneficial (p_input_table in dyn_rec_table
1453 ,p_rule in varchar2
1454 ,p_row out nocopy number) IS
1455 l_proc constant VARCHAR2(80) := g_pkg || '.do_num_beneficial';
1456 l_ben_val number;
1457 BEGIN
1458 hr_utility.set_location('Entering:'||l_proc, 10);
1459 for i in p_input_table.first..p_input_table.last loop
1460 if i = 1 then
1461 l_ben_val := p_input_table(i).num_col;
1462 p_row := i;
1463 elsif i > 1 then
1464 if p_rule = 'HI' then
1465 if p_input_table(i).num_col > l_ben_val then
1466 l_ben_val := p_input_table(i).num_col;
1467 p_row := i;
1468 elsif p_input_table(i).num_col = l_ben_val then
1469 p_row := NULL;
1470 end if;
1471 elsif p_rule = 'LO' then
1472 if p_input_table(i).num_col < l_ben_val then
1473 l_ben_val := p_input_table(i).num_col;
1474 p_row := i;
1475 elsif p_input_table(i).num_col = l_ben_val then
1476 p_row := NULL;
1477 end if;
1478 end if;
1479 end if;
1480 end loop;
1481 hr_utility.set_location('Leaving:'||l_proc, 20);
1482 END do_num_beneficial;
1483 --
1484 PROCEDURE do_char_beneficial (p_input_table in dyn_rec_table
1485 ,p_rule in varchar2
1486 ,p_row out nocopy number) IS
1487 l_proc constant VARCHAR2(80) := g_pkg || '.do_char_beneficial';
1488 l_ben_val varchar2(30);
1489 BEGIN
1490 hr_utility.set_location('Entering:'||l_proc, 10);
1491 for i in p_input_table.first..p_input_table.last loop
1492 if i = 1 then
1493 l_ben_val := p_input_table(i).char_col;
1494 p_row := i;
1495 elsif i > 1 then
1496 if p_rule = 'HI' then
1497 if p_input_table(i).char_col > l_ben_val then
1498 l_ben_val := p_input_table(i).char_col;
1499 p_row := i;
1500 elsif p_input_table(i).char_col = l_ben_val then
1501 p_row := NULL;
1502 end if;
1503 elsif p_rule = 'LO' then
1504 if p_input_table(i).char_col < l_ben_val then
1505 l_ben_val := p_input_table(i).char_col;
1506 p_row := i;
1507 elsif p_input_table(i).char_col = l_ben_val then
1508 p_row := NULL;
1509 end if;
1510 end if;
1511 end if;
1512 end loop;
1513 hr_utility.set_location('Leaving:'||l_proc, 20);
1514 END do_char_beneficial;
1515
1516 BEGIN
1517
1518 hr_utility.set_location('Entering:'||l_proc, 10);
1519 l_category := p_results_table(1).category_name;
1520 if p_ben_rule is not null then
1521 per_cagr_utility_pkg.put_log(' Evaluating '||p_ben_rule||' beneficial rule on '||p_results_table.last||' results for this entitlement item',1);
1522 else
1523 per_cagr_utility_pkg.put_log(' No beneficial rule is defined for this entitlement item',1);
1524 end if;
1525
1526 -- first test to see whether we are processing value or step_id field, in result records
1527 if p_results_table(1).value is not null and p_results_table(1).step_id is null then
1528 l_ben_field := 'VALUE';
1529 elsif p_results_table(1).value is null and p_results_table(1).step_id is not null then
1530 l_ben_field := 'STEP_ID';
1531 else
1532 per_cagr_utility_pkg.put_log('Cannot determine either of value or step_id to process');
1533 p_rule_inconclusive := TRUE;
1534 goto end_of_procedure; -- don't raise an exception
1535 end if;
1536
1537 per_cagr_utility_pkg.put_log(' Beneficial field is: '||l_ben_field);
1538
1539 -- if only one record in the input table, default and skip processing
1540 if p_results_table.count = 1 then
1541 if l_ben_field = 'VALUE' then
1542 l_ben_row := 1;
1543 else
1544 l_ben_row := 1;
1545 end if;
1546 elsif p_ben_rule is not null then
1547 -- start processing the results as > 1 record in table
1548 -- and ben_rule is set for the item.
1549
1550 if p_ben_rule_vs_id is not null then
1551 per_cagr_utility_pkg.put_log(' Beneficial rule uses ValueSet id: '||p_ben_rule_vs_id);
1552 -- we are using a data column so build list of id's from results for sql
1553 if l_ben_field = 'VALUE' then
1554 for i in p_results_table.first .. p_results_table.last loop
1555 l_list_str := l_list_str || p_results_table(i).VALUE ||',';
1556 end loop;
1557 else -- l_ben_field = 'STEP_ID'
1558 for i in p_results_table.first .. p_results_table.last loop
1559 l_list_str := l_list_str || p_results_table(i).STEP_ID ||',';
1560 end loop;
1561 end if;
1562 l_list_str := substr(l_list_str,1,(length(l_list_str) -1));
1563 l_list_str := '('||l_list_str||')';
1564 -- get the sql to be used to get the data column from value set
1565 l_sql := per_cagr_utility_pkg.get_sql_from_vset_id(p_ben_rule_vs_id);
1566 if l_sql is null then
1567 per_cagr_utility_pkg.put_log(' Could not determine SQL for ValueSet id');
1568 p_rule_inconclusive := TRUE;
1569 else
1570 -- replace BG_ID, and insert list of ids into the sql statement
1571 l_sql := replace(l_sql,':$PROFILES$.PER_BUSINESS_GROUP_ID',p_params.business_group_id);
1572 l_sql := replace(l_sql,'()',l_list_str);
1573 per_cagr_utility_pkg.put_log(l_sql);
1574
1575 -- determine datatype of value set data column
1576 open csr_data(p_ben_rule_vs_id);
1577 fetch csr_data into l_col_data_type;
1578 if csr_data%notfound then
1579 close csr_data;
1580 per_cagr_utility_pkg.put_log(' ValueSet column type not found');
1581 p_rule_inconclusive := TRUE;
1582 goto end_of_procedure;
1583 end if;
1584 per_cagr_utility_pkg.put_log(' Datatype of ValueSet data column is: '||l_col_data_type);
1585
1586 -- dynamic sql to get the value set data column values for list of id's
1587 -- and call relevant ben function for the datatype
1588 open l_dyn_csr for l_sql;
1589 if l_col_data_type = 'V' then
1590 loop
1591 fetch l_dyn_csr into l_id, l_char;
1592 exit when l_dyn_csr%notfound;
1593 l_dyn_csr_table(l_dyn_csr%rowcount).char_col := l_char;
1594 end loop;
1595 close l_dyn_csr;
1596 do_char_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1597 elsif l_col_data_type = 'N' then
1598 loop
1599 fetch l_dyn_csr into l_id, l_num;
1600 exit when l_dyn_csr%notfound;
1601 l_dyn_csr_table(l_dyn_csr%rowcount).num_col := l_num;
1602 end loop;
1603 close l_dyn_csr;
1604 do_num_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1605 elsif l_col_data_type = 'D' then
1606 loop
1607 fetch l_dyn_csr into l_id, l_date;
1608 exit when l_dyn_csr%notfound;
1609 l_dyn_csr_table(l_dyn_csr%rowcount).date_col := l_date;
1610 end loop;
1611 close l_dyn_csr;
1612 do_date_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1613 end if;
1614 end if;
1615
1616 else -- do regular processing on actual cagr data, using value only, and the
1617 -- value which is not treated as an ID (user must set up value set for ids
1618 -- within value or step_id).
1619 per_cagr_utility_pkg.put_log(' Ben rule uses cagr value column');
1620 per_cagr_utility_pkg.put_log(' Datatype of cagr column is: '||p_results_table(1).column_type);
1621
1622 BEGIN
1623 if p_results_table(1).column_type = 'VAR' then
1624 for i in p_results_table.first..p_results_table.last loop
1625 l_dyn_csr_table(i).char_col := p_results_table(i).value;
1626 end loop;
1627 do_char_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1628 elsif p_results_table(1).column_type = 'NUM' then
1629 for i in p_results_table.first..p_results_table.last loop
1630 l_dyn_csr_table(i).num_col := to_number(p_results_table(i).value);
1631 end loop;
1632 do_num_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1633 elsif p_results_table(1).column_type = 'DATE' then
1634 for i in p_results_table.first..p_results_table.last loop
1635 l_dyn_csr_table(i).date_col := trunc(fnd_date.canonical_to_date(p_results_table(i).value));
1636 end loop;
1637 do_date_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1638 end if;
1639 EXCEPTION
1640 WHEN OTHERS THEN -- trap any value conversion exceptions
1641 per_cagr_utility_pkg.put_log(' Beneficial Rule evaluation error',1);
1642 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1643 p_rule_inconclusive := TRUE;
1644 goto end_of_procedure;
1645 END;
1646 end if;
1647 l_dyn_csr_table.delete; -- clear pl/sql table
1648 end if;
1649
1650 if l_ben_row is not null then
1651 -- mark the beneficial record and log the value
1652 p_results_table(l_ben_row).BENEFICIAL_FLAG := 'Y';
1653 p_ben_row := l_ben_row;
1654 if l_ben_field = 'VALUE' then
1655 per_cagr_utility_pkg.put_log(' Beneficial value is: '||p_results_table(l_ben_row).value,1);
1656 elsif l_ben_field = 'STEP_ID' then
1657 per_cagr_utility_pkg.put_log(' Beneficial step_id is: '||p_results_table(l_ben_row).step_id,1);
1658 end if;
1659 else
1660 if p_ben_rule is not null then
1661 p_rule_inconclusive := TRUE;
1662 end if;
1663 end if;
1664
1665 <<end_of_procedure>>
1666 hr_utility.set_location('Leaving:'||l_proc, 50);
1667 END set_beneficial_value;
1668
1669 -- ================================================================================================
1670 -- == **************** ADD_RELATED_RETAINED_RIGHTS ***************** ==
1671 -- ================================================================================================
1672
1673 PROCEDURE add_related_ret_rights (p_assignment_id IN NUMBER
1674 ,p_cagr_entitlement_item_id IN NUMBER
1675 ,p_effective_date IN DATE
1676 ,p_structure IN OUT NOCOPY results_table
1677 ,p_counter IN OUT NOCOPY NUMBER) IS
1678
1679 -- Accept a structure containing the current process set of entitlements for a dataitem
1680 -- and add in any retained entitlements that are eligible, for the dataitem
1681 -- (Retained rights records may be for item or line level and may or may not be frozen)
1682 -- Next apply the beneficial rule processing for the retained rights entitlements
1683 -- (same as for current entitlements processed in Main block)
1684 -- Thus return the completed set of entitlement records (and counter) for the current dataitem,
1685 -- with benficial row identified.
1686
1687 --
1688 -- Cursor to return retained rights for the current assignment and dataitem
1689 -- on the effective_date, checking the cagr, entitlement and line are still active
1690 -- and current on the effective_date.
1691 --
1692 CURSOR csr_cagr_retained_rights IS
1693 SELECT * from per_cagr_retained_rights pcrr
1694 WHERE pcrr.assignment_id = p_assignment_id
1695 AND cagr_entitlement_item_id = p_cagr_entitlement_item_id
1696 AND p_params.effective_date BETWEEN pcrr.START_DATE AND nvl(pcrr.END_DATE,hr_general.end_of_time)
1697 AND EXISTS (select 'x'
1698 from per_collective_agreements pca
1699 where pca.collective_agreement_id = pcrr.collective_agreement_id
1700 and pca.STATUS = 'A'
1701 and p_params.effective_date >= pca.START_DATE)
1702 AND EXISTS (select 'x'
1703 from per_cagr_entitlements pce
1704 where pce.cagr_entitlement_id = pcrr.cagr_entitlement_id
1705 and pce.STATUS = 'A'
1706 and p_params.effective_date between pce.START_DATE and nvl(pce.END_DATE,hr_general.end_of_time))
1707 AND ((pcrr.cagr_entitlement_line_id is not null
1708 and EXISTS (select 'x'
1709 from per_cagr_entitlement_lines_f pcel
1710 where pcel.cagr_entitlement_line_id = pcrr.cagr_entitlement_line_id
1711 and pcel.STATUS = 'A'
1712 and p_params.effective_date between pcel.effective_start_date
1713 and pcel.effective_end_date))
1714 OR pcrr.cagr_entitlement_line_id is null);
1715 --
1716 -- Cursor to return entitlement values (status and date are done checked driving cursor above)
1717 --
1718 CURSOR csr_cagr_ents (v_ent_id in NUMBER) IS
1719 SELECT *
1720 FROM per_cagr_entitlements pce
1721 WHERE pce.CAGR_ENTITLEMENT_ID = v_ent_id;
1722 --
1723 -- Cursor to return active entitlement line values on the effective_date.
1724 -- (status and date are done checked driving cursor above)
1725 -- used for un-frozen retained right ent lines
1726 --
1727 CURSOR csr_cagr_lines (v_line_id in NUMBER, v_cagr_id IN NUMBER) IS
1728 SELECT *
1729 FROM per_cagr_entitlement_lines_f pcel
1730 WHERE pcel.CAGR_ENTITLEMENT_LINE_ID = v_line_id
1731 AND p_effective_date BETWEEN pcel.EFFECTIVE_START_DATE
1732 AND nvl(pcel.EFFECTIVE_END_DATE,hr_general.end_of_time);
1733 --
1734 -- Cursor to return entitlement line values on the effective_date
1735 --
1736 v_csr_rr_rec csr_cagr_retained_rights%ROWTYPE;
1737 v_cagr_ents_rec csr_cagr_ents%ROWTYPE;
1738 v_cagr_lines_rec csr_cagr_lines%ROWTYPE;
1739 v_local_counter NUMBER(10);
1740 v_dataitem_id NUMBER(10);
1741 v_write_flag BOOLEAN := FALSE;
1742 v_value per_cagr_retained_rights.value%TYPE;
1743 v_range_from per_cagr_retained_rights.range_from%TYPE;
1744 v_range_to per_cagr_retained_rights.range_to%TYPE;
1745 v_units_of_measure per_cagr_retained_rights.units_of_measure%TYPE;
1746 v_grade_spine_id per_cagr_retained_rights.grade_spine_id%TYPE;
1747 v_parent_spine_id per_cagr_retained_rights.parent_spine_id%TYPE;
1748 v_step_id per_cagr_retained_rights.step_id%TYPE;
1749 v_from_step_id per_cagr_retained_rights.from_step_id%TYPE;
1750 v_to_step_id per_cagr_retained_rights.to_step_id%TYPE;
1751 l_cagr_FF_record hr_cagr_ff_pkg.cagr_FF_record;
1752 l_source_name varchar2(200) := NULL;
1753 v_counter NUMBER(15) := NULL;
1754 v_dup_record NUMBER(15) := null;
1755 l_proc constant VARCHAR2(80) := g_pkg || '.' || 'add_related_ret_rights';
1756
1757 BEGIN
1758
1759 hr_utility.set_location('Entering:'||l_proc, 10);
1760 per_cagr_utility_pkg.put_log(' Evaluating related Retained Rights for the item ',1);
1761 -- We are processing RR for a dataitem for an ASG
1762 open csr_cagr_retained_rights;
1763 LOOP
1764 fetch csr_cagr_retained_rights into v_csr_rr_rec;
1765 exit when csr_cagr_retained_rights%notfound;
1766
1767 if v_csr_rr_rec.OIPL_ID <> 0 and v_csr_rr_rec.eligy_prfl_id <> 0 then
1768 l_source_name := per_cagr_utility_pkg.get_elig_source(v_csr_rr_rec.eligy_prfl_id
1769 ,v_csr_rr_rec.formula_id
1770 ,p_params.effective_date);
1771 else
1772 l_source_name := '*** Default ***';
1773 end if;
1774
1775
1776 -- reset flag
1777 v_write_flag := FALSE;
1778 v_value := NULL; -- clear result variables before eval
1779 v_range_from := NULL;
1780 v_range_to := NULL;
1781 v_grade_spine_id := NULL;
1782 v_parent_spine_id := NULL;
1783 v_step_id := NULL;
1784 v_from_step_id := NULL;
1785 v_to_step_id := NULL;
1786
1787 if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
1788 per_cagr_utility_pkg.put_log(' found retained entitlement: '||l_source_name,1);
1789 v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
1790
1791 per_cagr_utility_pkg.put_log(' Retained Right is for entitlement: '|| v_csr_rr_rec.cagr_entitlement_id);
1792
1793 if v_csr_rr_rec.freeze_flag = 'N' then
1794 -- not frozen, so get the latest value from the latest formula_id
1795 -- for the retained entitlement
1796 per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1797 open csr_cagr_ents(v_csr_rr_rec.CAGR_ENTITLEMENT_ID);
1798 fetch csr_cagr_ents into v_cagr_ents_rec;
1799 if csr_cagr_ents%found then
1800 v_units_of_measure := v_cagr_ents_rec.UNITS_OF_MEASURE;
1801
1802 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_cagr_ents_rec.FORMULA_ID
1803 ,p_effective_date => p_effective_date
1804 ,p_assignment_id => p_assignment_id
1805 ,p_category_name => v_csr_rr_rec.category_name
1806 ,p_out_rec => l_cagr_FF_record);
1807
1808 -- assign FF return values to local vars if set
1809 if v_csr_rr_rec.category_name in ('ASG','PAY','ABS') then
1810 if l_cagr_FF_record.value is not null then
1811 v_value := l_cagr_FF_record.value;
1812 v_range_from := l_cagr_FF_record.range_from;
1813 v_range_to := l_cagr_FF_record.range_to;
1814 v_write_flag := TRUE;
1815 else
1816 -- log message as the formula evaluated to null and continue with next entitlement record
1817 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
1818 v_write_flag := FALSE;
1819 end if;
1820 elsif v_csr_rr_rec.category_name = 'PYS' then
1821 if l_cagr_FF_record.grade_spine_id is not null
1822 and l_cagr_FF_record.parent_spine_id is not null
1823 and l_cagr_FF_record.step_id is not null then
1824 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
1825 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
1826 v_step_id := l_cagr_FF_record.step_id;
1827 v_from_step_id := l_cagr_FF_record.from_step_id;
1828 v_to_step_id := l_cagr_FF_record.to_step_id;
1829 v_write_flag := TRUE;
1830 else
1831 -- log error as the formula didn't evaluate and continue with next entitlement record
1832 per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
1833 v_write_flag := FALSE;
1834 end if;
1835 end if;
1836 end if;
1837 close csr_cagr_ents;
1838
1839 elsif v_csr_rr_rec.freeze_flag = 'Y' then
1840 -- frozen, so assign use the frozen value (instead of re-evaluating formula)
1841 -- and trigger this retained right entitlement result to be added to the process set.
1842 per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1843 v_value := v_csr_rr_rec.value;
1844 v_range_from := v_csr_rr_rec.range_from;
1845 v_range_to := v_csr_rr_rec.range_to;
1846 v_units_of_measure := v_csr_rr_rec.units_of_measure;
1847 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
1848 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
1849 v_step_id := v_csr_rr_rec.step_id;
1850 v_from_step_id := v_csr_rr_rec.from_step_id;
1851 v_to_step_id := v_csr_rr_rec.to_step_id;
1852 v_write_flag := TRUE;
1853 end if;
1854
1855 else -- ent line retained right
1856 per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1857 per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1858
1859 if v_csr_rr_rec.freeze_flag = 'N' then
1860 per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1861 -- not frozen, so get the latest values for the item line
1862 open csr_cagr_lines(v_csr_rr_rec.cagr_entitlement_line_id, v_csr_rr_rec.collective_agreement_id);
1863 fetch csr_cagr_lines into v_cagr_lines_rec;
1864 if csr_cagr_lines%found then
1865 v_value := v_cagr_lines_rec.value;
1866 v_range_from := v_cagr_lines_rec.range_from;
1867 v_range_to := v_cagr_lines_rec.range_to;
1868 v_units_of_measure := v_csr_rr_rec.units_of_measure; -- i.e. use ent item uom for line
1869 v_grade_spine_id := v_cagr_lines_rec.grade_spine_id;
1870 v_parent_spine_id := v_cagr_lines_rec.parent_spine_id;
1871 v_step_id := v_cagr_lines_rec.step_id;
1872 v_from_step_id := v_cagr_lines_rec.from_step_id;
1873 v_to_step_id := v_cagr_lines_rec.to_step_id;
1874 close csr_cagr_lines;
1875 v_write_flag := TRUE;
1876 else
1877 close csr_cagr_lines;
1878 end if;
1879 elsif v_csr_rr_rec.freeze_flag = 'Y' then
1880 per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1881 -- frozen, so use the values that was saved
1882 -- on the retained right start date.
1883 v_value := v_csr_rr_rec.value;
1884 v_range_from := v_csr_rr_rec.range_from;
1885 v_range_to := v_csr_rr_rec.range_to;
1886 v_units_of_measure := v_csr_rr_rec.units_of_measure;
1887 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
1888 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
1889 v_step_id := v_csr_rr_rec.step_id;
1890 v_from_step_id := v_csr_rr_rec.from_step_id;
1891 v_to_step_id := v_csr_rr_rec.to_step_id;
1892 v_write_flag := TRUE;
1893 end if;
1894 end if;
1895
1896 if v_write_flag then
1897
1898
1899 -- Prevent duplicate results from occurring where a an eligible entitlement / entitlement line
1900 -- exists and it has also been retained (but the values have not changed. In this scenario
1901 -- delete the new result and just produce the retained result for the item...
1902 -- * Add support for validation fields at a later date *
1903 v_counter := null;
1904 v_dup_record := null;
1905
1906 If p_counter > 0 then
1907 If v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is not null then -- entitlement line
1908
1909 If v_csr_rr_rec.CATEGORY_NAME in ('ASG','ABS','PAY') then
1910 For y in p_structure.first .. p_structure.last loop
1911 If (v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID = p_structure(y).CAGR_ENTITLEMENT_LINE_ID
1912 and p_structure(y).VALUE = v_value) then
1913 v_dup_record := y; -- found a duplicate to the retained right
1914 exit;
1915 End if;
1916 End Loop;
1917
1918 Elsif v_csr_rr_rec.CATEGORY_NAME = 'PYS' then
1919 For y in p_structure.first .. p_structure.last loop
1920 If (v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID = p_structure(y).CAGR_ENTITLEMENT_LINE_ID
1921 and p_structure(y).GRADE_SPINE_ID = v_grade_spine_id
1922 and p_structure(y).PARENT_SPINE_ID = v_parent_spine_id
1923 and p_structure(y).STEP_ID = v_step_id) then
1924 v_dup_record := y; -- found a duplicate to the retained right
1925 exit;
1926 End if;
1927 End Loop;
1928 End if;
1929
1930 Else -- entitlement only
1931
1932 If v_csr_rr_rec.CATEGORY_NAME in ('ASG','ABS','PAY') then
1933 For y in p_structure.first .. p_structure.last loop
1934 If (v_csr_rr_rec.CAGR_ENTITLEMENT_ID = p_structure(y).CAGR_ENTITLEMENT_ID
1935 and p_structure(y).VALUE = v_value) then
1936 v_dup_record := y; -- found a duplicate to the retained right
1937 exit;
1938 End if;
1939 End Loop;
1940
1941 Elsif v_csr_rr_rec.CATEGORY_NAME = 'PYS' then
1942 For y in p_structure.first .. p_structure.last loop
1943 If (v_csr_rr_rec.CAGR_ENTITLEMENT_ID = p_structure(y).CAGR_ENTITLEMENT_ID
1944 and p_structure(y).GRADE_SPINE_ID = v_grade_spine_id
1945 and p_structure(y).PARENT_SPINE_ID = v_parent_spine_id
1946 and p_structure(y).STEP_ID = v_step_id) then
1947 v_dup_record := y; -- found a duplicate to the retained right
1948 exit;
1949 End if;
1950 End Loop;
1951 End if;
1952
1953 End if;
1954 End if;
1955
1956
1957 If v_dup_record is not null then
1958 -- delete the duplicate result and put RR in its place.
1959 p_structure.delete(v_dup_record);
1960 v_counter := v_dup_record;
1961 per_cagr_utility_pkg.put_log(' Removed duplicate result for this retained right.');
1962 End If;
1963 If v_counter is null then
1964 p_counter := p_counter +1;
1965 v_counter := p_counter;
1966 End if;
1967 --
1968 -- Assign the retained right entitlement into the plsql table
1969 -- holding the current entitlements process set for the dataitem.
1970 --
1971 p_structure(v_counter).COLLECTIVE_AGREEMENT_ID := v_csr_rr_rec.COLLECTIVE_AGREEMENT_ID;
1972 p_structure(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
1973 p_structure(v_counter).ELEMENT_TYPE_ID := v_csr_rr_rec.ELEMENT_TYPE_ID;
1974 p_structure(v_counter).INPUT_VALUE_ID := v_csr_rr_rec.INPUT_VALUE_ID;
1975 p_structure(v_counter).CAGR_API_ID := v_csr_rr_rec.CAGR_API_ID;
1976 p_structure(v_counter).CAGR_API_PARAM_ID := v_csr_rr_rec.CAGR_API_PARAM_ID;
1977 p_structure(v_counter).CATEGORY_NAME := v_csr_rr_rec.CATEGORY_NAME;
1978 p_structure(v_counter).CAGR_ENTITLEMENT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ID;
1979 p_structure(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID;
1980 p_structure(v_counter).ASSIGNMENT_ID := v_csr_rr_rec.ASSIGNMENT_ID;
1981 p_structure(v_counter).OIPL_ID := v_csr_rr_rec.OIPL_ID;
1982 p_structure(v_counter).ELIGY_PRFL_ID := v_csr_rr_rec.ELIGY_PRFL_ID;
1983 p_structure(v_counter).FORMULA_ID := v_csr_rr_rec.FORMULA_ID;
1984 p_structure(v_counter).VALUE := v_value;
1985 p_structure(v_counter).RANGE_FROM := v_range_from;
1986 p_structure(v_counter).RANGE_TO := v_range_to;
1987 p_structure(v_counter).UNITS_OF_MEASURE := v_units_of_measure;
1988 p_structure(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
1989 p_structure(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
1990 p_structure(v_counter).STEP_ID := v_step_id;
1991 p_structure(v_counter).FROM_STEP_ID := v_from_step_id;
1992 p_structure(v_counter).TO_STEP_ID := v_to_step_id;
1993 p_structure(v_counter).COLUMN_TYPE := v_csr_rr_rec.COLUMN_TYPE;
1994 p_structure(v_counter).COLUMN_SIZE := v_csr_rr_rec.COLUMN_SIZE;
1995 p_structure(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_csr_rr_rec.MULTIPLE_ENTRIES_ALLOWED_FLAG;
1996 p_structure(v_counter).BUSINESS_GROUP_ID := v_csr_rr_rec.BUSINESS_GROUP_ID;
1997 p_structure(v_counter).FLEX_VALUE_SET_ID := v_csr_rr_rec.FLEX_VALUE_SET_ID;
1998 p_structure(v_counter).RETAINED_ENT_RESULT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_RESULT_ID;
1999 end if;
2000 END LOOP;
2001 close csr_cagr_retained_rights;
2002 per_cagr_utility_pkg.put_log(' Completed related Retained Rights.',1);
2003 hr_utility.set_location('Leaving:'||l_proc, 50);
2004
2005 END add_related_ret_rights;
2006
2007 -- ================================================================================================
2008 -- == **************** ADD_OTHER_RETAINED_RIGHTS ***************** ==
2009 -- ================================================================================================
2010
2011 PROCEDURE add_other_ret_rights (p_params IN control_structure) IS
2012
2013 -- Returns any retained rights that exist for an assignment that are not for dataitems for
2014 -- which entitlements have been returned by the cursor in the main block. This mode uses a
2015 -- temporary table populated by the main routine holding the distinct dataitem_ids that have
2016 -- been returned by the main cursor (and so have already been evaluated by the above mode)
2017 -- to ensure that they are not duplicated again.
2018
2019 -- Note: Retained rights that are not frozen may be negated by having the entitlementl or line or even
2020 -- a parent entitlement or collective agreement end-dated before the effective date, or by having
2021 -- the status of the entitlement or line or parent entitlement or collective agreement set to status
2022 -- of inactive or pending.
2023
2024 --
2025 -- Cursor to return retained rights that are for entitlement items other than
2026 -- those returned by the main block (held in temp table), that are for active
2027 -- cagr, ent and lines (possibly) on the effective date. (And assuming the asg is primary)
2028 --
2029 CURSOR csr_cagr_other_ret_rights IS
2030 SELECT *
2031 FROM per_cagr_retained_rights pcrr
2032 WHERE pcrr.assignment_id = p_params.assignment_id
2033 AND p_params.effective_date BETWEEN pcrr.START_DATE
2034 AND nvl(pcrr.END_DATE,hr_general.end_of_time)
2035 AND EXISTS (select 'X' from per_all_assignments_f asg
2036 where asg.assignment_id = p_params.assignment_id
2037 and p_params.effective_date BETWEEN asg.effective_start_date
2038 AND asg.effective_end_date
2039 and asg.PRIMARY_FLAG = 'Y')
2040 AND EXISTS (select 'x'
2041 from per_collective_agreements pca
2042 where pca.collective_agreement_id = pcrr.collective_agreement_id
2043 and pca.STATUS = 'A'
2044 and p_params.effective_date >= pca.START_DATE)
2045 AND EXISTS (select 'x'
2046 from per_cagr_entitlements pce
2047 where pce.cagr_entitlement_id = pcrr.cagr_entitlement_id
2048 and pce.STATUS = 'A'
2049 and p_params.effective_date BETWEEN pce.START_DATE
2050 AND nvl(pce.END_DATE,hr_general.end_of_time))
2051 AND ((pcrr.cagr_entitlement_line_id is not null
2052 and EXISTS (select 'x'
2053 from per_cagr_entitlement_lines_f pcel
2054 where pcel.cagr_entitlement_line_id = pcrr.cagr_entitlement_line_id
2055 and pcel.STATUS = 'A'
2056 and p_params.effective_date between pcel.effective_start_date
2057 and pcel.effective_end_date))
2058 OR pcrr.cagr_entitlement_line_id is null)
2059 AND 'N' = per_cagr_evaluation_pkg.new_entitlement(pcrr.cagr_entitlement_item_id)
2060 ORDER BY pcrr.cagr_entitlement_item_id;
2061 --
2062 -- Cursor to return entitlement values (driving cursor checks date and status)
2063 --
2064 CURSOR csr_cagr_ents (v_ent_id in NUMBER) IS
2065 SELECT *
2066 FROM per_cagr_entitlements pce
2067 WHERE pce.CAGR_ENTITLEMENT_ID = v_ent_id
2068 AND p_params.effective_date BETWEEN pce.START_DATE
2069 AND nvl(pce.END_DATE,hr_general.end_of_time);
2070 --
2071 -- Cursor to return active entitlement line values on the effective_date
2072 --
2073 CURSOR csr_cagr_lines (v_line_id in NUMBER, v_cagr_id IN NUMBER) IS
2074 SELECT *
2075 FROM per_cagr_entitlement_lines_f pcel
2076 WHERE pcel.CAGR_ENTITLEMENT_LINE_ID = v_line_id
2077 AND p_params.effective_date BETWEEN pcel.EFFECTIVE_START_DATE
2078 AND nvl(pcel.EFFECTIVE_END_DATE,hr_general.end_of_time);
2079 --
2080 -- Cursor to get the beneficial rule info for specific entitlement_item
2081 --
2082 cursor csr_ben_rule (l_cagr_entitlement_item_id IN NUMBER) is
2083 SELECT beneficial_rule, beneficial_rule_value_set_id
2084 from per_cagr_entitlement_items pcei
2085 where pcei.cagr_entitlement_item_id = l_cagr_entitlement_item_id;
2086 --
2087 --
2088 --
2089 t_results_table results_table;
2090 l_outputs ff_exec.outputs_t;
2091 v_csr_rr_rec csr_cagr_other_ret_rights%ROWTYPE;
2092 v_cagr_ents_rec csr_cagr_ents%ROWTYPE;
2093 v_cagr_lines_rec csr_cagr_lines%ROWTYPE;
2094 v_ben_rule csr_ben_rule%ROWTYPE;
2095 v_counter NUMBER(10) := 0;
2096 v_beneficial_rule VARCHAR2(30);
2097 v_beneficial_value VARCHAR2(240);
2098 v_beneficial_rule_vs_id NUMBER(15);
2099 v_ben_row NUMBER(10);
2100 v_rule_inconclusive BOOLEAN := FALSE;
2101 v_dataitem_id NUMBER(10);
2102 v_write_flag BOOLEAN := FALSE;
2103 v_value per_cagr_retained_rights.VALUE%TYPE;
2104 v_range_from per_cagr_retained_rights.RANGE_FROM%TYPE;
2105 v_range_to per_cagr_retained_rights.RANGE_TO%TYPE;
2106 v_units_of_measure per_cagr_retained_rights.UNITS_OF_MEASURE%TYPE;
2107 v_grade_spine_id per_cagr_retained_rights.GRADE_SPINE_ID%TYPE;
2108 v_parent_spine_id per_cagr_retained_rights.PARENT_SPINE_ID%TYPE;
2109 v_step_id per_cagr_retained_rights.STEP_ID%TYPE;
2110 v_from_step_id per_cagr_retained_rights.FROM_STEP_ID%TYPE;
2111 v_to_step_id per_cagr_retained_rights.TO_STEP_ID%TYPE;
2112 l_cagr_FF_record hr_cagr_ff_pkg.cagr_FF_record;
2113
2114 l_proc constant VARCHAR2(80) := g_pkg || '.' || 'add_other_ret_rights';
2115
2116 BEGIN
2117
2118 hr_utility.set_location('Entering:'||l_proc, 10);
2119 per_cagr_utility_pkg.put_log(' Evaluating Retained Rights for other items',1);
2120 open csr_cagr_other_ret_rights;
2121 LOOP
2122 fetch csr_cagr_other_ret_rights into v_csr_rr_rec;
2123 exit when csr_cagr_other_ret_rights%notfound;
2124
2125 -- iterate through retained entitlements for each dataitem
2126 -- processing as if we were processing the main block
2127
2128 v_write_flag := FALSE;
2129 v_value := NULL; -- clear result variables before eval
2130 v_range_from := NULL;
2131 v_range_to := NULL;
2132 v_grade_spine_id := NULL;
2133 v_parent_spine_id := NULL;
2134 v_step_id := NULL;
2135 v_from_step_id := NULL;
2136 v_to_step_id := NULL;
2137
2138
2139 if v_last_dataitem_id is not null then
2140 if (v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID <> v_last_dataitem_id) then
2141 -- The dataitem that is being processed has changed so....
2142 -- (Note: this block gets invoked for dataitem rr entitlement set #2 thru to penultimate,
2143 -- where there are > 1 rr dataitem entitlement sets)
2144
2145 -- write any valid entitlement results for the previous dataitem,
2146 -- if beneficial rule has identified a preferred entitlement and clear the plsql table.
2147 if v_counter > 0 then
2148 -- determine and set most beneficial value for retained right results set
2149 set_beneficial_value(p_effective_date => p_params.effective_date
2150 ,p_results_table => t_results_table
2151 ,p_ben_rule => v_beneficial_rule
2152 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2153 ,p_ben_value => v_beneficial_value
2154 ,p_ben_row => v_ben_row
2155 ,p_rule_inconclusive => v_rule_inconclusive);
2156
2157 if v_rule_inconclusive then
2158 -- output warning message that beneficial could not be chosen
2159 -- and write results anyway, if profile option allows
2160 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2161 end if;
2162 update_result_set(t_results_table,p_params,'W');
2163 v_counter := 0;
2164 t_results_table.delete;
2165 end if;
2166 -- store new dataitem_id
2167 v_last_dataitem_id := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
2168 -- store new beneficial_rule
2169 open csr_ben_rule(v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID);
2170 fetch csr_ben_rule into v_ben_rule;
2171 if csr_ben_rule%found then
2172 close csr_ben_rule;
2173 v_beneficial_rule := v_ben_rule.beneficial_rule;
2174 v_beneficial_rule_vs_id := v_ben_rule.beneficial_rule_value_set_id;
2175 else
2176 close csr_ben_rule;
2177 end if;
2178 end if;
2179 else -- set the dataitem and beneficial rule on the first iteration
2180 v_last_dataitem_id := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
2181 open csr_ben_rule(v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID);
2182 fetch csr_ben_rule into v_ben_rule;
2183 if csr_ben_rule%found then
2184 close csr_ben_rule;
2185 v_beneficial_rule := v_ben_rule.beneficial_rule;
2186 v_beneficial_rule_vs_id := v_ben_rule.beneficial_rule_value_set_id;
2187 else
2188 close csr_ben_rule;
2189 end if;
2190 end if;
2191
2192
2193 -- determine whether current record is just entitlement item
2194 -- or entitlement line, and exec ff accordingly...
2195 if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
2196 v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
2197
2198 if v_csr_rr_rec.freeze_flag = 'N' then
2199 -- not frozen, so get the latest value of the formula_id, UOM for the entitlement
2200 open csr_cagr_ents(v_csr_rr_rec.CAGR_ENTITLEMENT_ID);
2201 fetch csr_cagr_ents into v_cagr_ents_rec;
2202 if csr_cagr_ents%found then
2203 v_units_of_measure := v_cagr_ents_rec.UNITS_OF_MEASURE;
2204
2205 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_cagr_ents_rec.FORMULA_ID
2206 ,p_effective_date => p_params.effective_date
2207 ,p_assignment_id => p_params.assignment_id
2208 ,p_category_name => v_csr_rr_rec.category_name
2209 ,p_out_rec => l_cagr_FF_record);
2210
2211 -- assign FF return values to local vars if set
2212 if v_csr_rr_rec.category_name in ('ASG','PAY','ABS') then
2213 if l_cagr_FF_record.value is not null then
2214 v_value := l_cagr_FF_record.value;
2215 v_range_from := l_cagr_FF_record.range_from;
2216 v_range_to := l_cagr_FF_record.range_to;
2217 v_write_flag := TRUE;
2218 else
2219 -- log message as the formula evaluated to null and continue with next entitlement record
2220 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2221 v_write_flag := FALSE;
2222 end if;
2223 elsif v_csr_rr_rec.category_name = 'PYS' then
2224 if l_cagr_FF_record.grade_spine_id is not null
2225 and l_cagr_FF_record.parent_spine_id is not null
2226 and l_cagr_FF_record.step_id is not null then
2227 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
2228 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
2229 v_step_id := l_cagr_FF_record.step_id;
2230 v_from_step_id := l_cagr_FF_record.from_step_id;
2231 v_to_step_id := l_cagr_FF_record.to_step_id;
2232 v_write_flag := TRUE;
2233 else
2234 -- log error as the formula didn't evaluate and continue with next entitlement record
2235 per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
2236 v_write_flag := FALSE;
2237 end if;
2238 end if;
2239 end if;
2240
2241 elsif v_csr_rr_rec.freeze_flag = 'Y' then
2242 -- frozen, so assign use the frozen value (instead of re-evaluating formula)
2243 -- and trigger this retained right entitlement result to be added to the process set.
2244 v_value := v_csr_rr_rec.value;
2245 v_range_from := v_csr_rr_rec.range_from;
2246 v_range_to := v_csr_rr_rec.range_to;
2247 v_units_of_measure := v_csr_rr_rec.units_of_measure;
2248 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
2249 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
2250 v_step_id := v_csr_rr_rec.step_id;
2251 v_from_step_id := v_csr_rr_rec.from_step_id;
2252 v_to_step_id := v_csr_rr_rec.to_step_id;
2253 v_write_flag := TRUE;
2254 end if;
2255
2256 else -- ent line retained right
2257 if v_csr_rr_rec.freeze_flag is null then
2258 -- not frozen, so get the latest values for the item line
2259 -- (but we do not execute the criteria line formula)
2260 open csr_cagr_lines(v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID, v_csr_rr_rec.COLLECTIVE_AGREEMENT_ID);
2261 fetch csr_cagr_lines into v_cagr_lines_rec;
2262 if csr_cagr_lines%found then
2263 v_value := v_cagr_lines_rec.value;
2264 v_range_from := v_cagr_lines_rec.range_from;
2265 v_range_to := v_cagr_lines_rec.range_to;
2266 v_units_of_measure := v_csr_rr_rec.units_of_measure; -- i.e. use ent item uom for line
2267 v_grade_spine_id := v_cagr_lines_rec.grade_spine_id;
2268 v_parent_spine_id := v_cagr_lines_rec.parent_spine_id;
2269 v_step_id := v_cagr_lines_rec.step_id;
2270 v_from_step_id := v_cagr_lines_rec.from_step_id;
2271 v_to_step_id := v_cagr_lines_rec.to_step_id;
2272
2273 close csr_cagr_lines;
2274 v_write_flag := TRUE;
2275 else
2276 close csr_cagr_lines;
2277 end if;
2278 elsif v_csr_rr_rec.freeze_flag = 'Y' then
2279 -- frozen, so use the values that was saved
2280 -- on the retained right start date.
2281 v_value := v_csr_rr_rec.value;
2282 v_range_from := v_csr_rr_rec.range_from;
2283 v_range_to := v_csr_rr_rec.range_to;
2284 v_units_of_measure := v_csr_rr_rec.units_of_measure;
2285 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
2286 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
2287 v_step_id := v_csr_rr_rec.step_id;
2288 v_from_step_id := v_csr_rr_rec.from_step_id;
2289 v_to_step_id := v_csr_rr_rec.to_step_id;
2290
2291 v_write_flag := TRUE;
2292 end if;
2293 end if;
2294
2295 if v_write_flag then
2296 --
2297 -- Assign the retained right entitlement into the plsql table
2298 -- holding the current entitlements process set for the dataitem.
2299 --
2300 v_counter := v_counter + 1;
2301
2302 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_csr_rr_rec.COLLECTIVE_AGREEMENT_ID;
2303 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
2304 t_results_table(v_counter).ELEMENT_TYPE_ID := v_csr_rr_rec.ELEMENT_TYPE_ID;
2305 t_results_table(v_counter).INPUT_VALUE_ID := v_csr_rr_rec.INPUT_VALUE_ID;
2306 t_results_table(v_counter).CAGR_API_ID := v_csr_rr_rec.CAGR_API_ID;
2307 t_results_table(v_counter).CAGR_API_PARAM_ID := v_csr_rr_rec.CAGR_API_PARAM_ID;
2308 t_results_table(v_counter).CATEGORY_NAME := v_csr_rr_rec.CATEGORY_NAME;
2309 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ID;
2310 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID;
2311 t_results_table(v_counter).ASSIGNMENT_ID := v_csr_rr_rec.ASSIGNMENT_ID;
2312 t_results_table(v_counter).OIPL_ID := v_csr_rr_rec.OIPL_ID;
2313 t_results_table(v_counter).ELIGY_PRFL_ID := v_csr_rr_rec.ELIGY_PRFL_ID;
2314 t_results_table(v_counter).FORMULA_ID := v_csr_rr_rec.FORMULA_ID;
2315 t_results_table(v_counter).VALUE := v_value;
2316 t_results_table(v_counter).RANGE_FROM := v_range_from;
2317 t_results_table(v_counter).RANGE_TO := v_range_to;
2318 t_results_table(v_counter).UNITS_OF_MEASURE := v_units_of_measure;
2319 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
2320 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
2321 t_results_table(v_counter).STEP_ID := v_step_id;
2322 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
2323 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
2324 t_results_table(v_counter).COLUMN_TYPE := v_csr_rr_rec.COLUMN_TYPE;
2325 t_results_table(v_counter).COLUMN_SIZE := v_csr_rr_rec.COLUMN_SIZE;
2326 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_csr_rr_rec.MULTIPLE_ENTRIES_ALLOWED_FLAG;
2327 t_results_table(v_counter).BUSINESS_GROUP_ID := v_csr_rr_rec.BUSINESS_GROUP_ID;
2328 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_csr_rr_rec.FLEX_VALUE_SET_ID;
2329 t_results_table(v_counter).RETAINED_ENT_RESULT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_RESULT_ID;
2330 end if;
2331 END LOOP;
2332 close csr_cagr_other_ret_rights;
2333
2334
2335 if v_counter > 0 then
2336 -- determine and set most beneficial value for retained right results set
2337 set_beneficial_value(p_effective_date => p_params.effective_date
2338 ,p_results_table => t_results_table
2339 ,p_ben_rule => v_beneficial_rule
2340 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2341 ,p_ben_value => v_beneficial_value
2342 ,p_ben_row => v_ben_row
2343 ,p_rule_inconclusive => v_rule_inconclusive);
2344
2345 -- write any valid entitlement results for the first (if there was only 1 datatem)
2346 -- or last dataitem retrieved by the cursor,
2347 -- if beneficial rule has identified a preferred entitlement
2348 -- and clear the plsql table.
2349 if v_rule_inconclusive then
2350 -- output warning message that beneficial could not be chosen
2351 -- and write results anyway..
2352 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2353 end if;
2354 update_result_set(t_results_table,p_params,'W');
2355 t_results_table.delete;
2356 v_counter := 0;
2357 end if;
2358 per_cagr_utility_pkg.put_log(' Completed Retained Rights for other items.',1);
2359 hr_utility.set_location('Leaving:'||l_proc, 50);
2360
2361 END add_other_ret_rights;
2362
2363 -- ================================================================================================
2364 -- == **************** MAIN BLOCK ***************** ==
2365 -- ================================================================================================
2366
2367 BEGIN
2368
2369 hr_utility.set_location('Entering:'||l_proc, 5);
2370 per_cagr_utility_pkg.put_log(g_separator,1);
2371 per_cagr_utility_pkg.put_log('Starting Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
2372 --
2373 -- choose which cursor to open,
2374 -- depending upon operation mode
2375 --
2376 If p_params.operation_mode = 'SA' then
2377 --
2378 -- ****** Single Assignment mode *******
2379 --
2380 If p_params.commit_flag = 'Y' then
2381 -- first populate pl/sql table with chosen results from cache, if committing changes.
2382 t_chosen_table := store_chosen_results(p_params.assignment_id
2383 ,p_params.effective_date);
2384 end if;
2385
2386 -- clean the cache of existing records for all items on this asg - effective date comb
2387 update_result_set(t_results_table,p_params,'C');
2388
2389 -- Invoke benmngle to process all entitlements (options) for the CAGR_ID (plan)
2390 -- on this assignment, if we determine that there are entitlement_lines in existence
2391 -- for any items on the cagr, on the effective_date. (not inc. default elig lines)
2392
2393 open csr_SA_drive_benmngle;
2394 fetch csr_SA_drive_benmngle into v_SA_drive_benmngle;
2395 if csr_SA_drive_benmngle%found then
2396 close csr_SA_drive_benmngle;
2397 -- start benmngle
2398 process_entitlement_lines(p_pl_id => v_SA_drive_benmngle.pl_id
2399 ,p_opt_id => NULL -- running at plan level here
2400 ,p_person_id => v_SA_drive_benmngle.person_id
2401 ,p_benefit_action_id => v_benefit_action_id
2402 ,p_effective_date => p_params.effective_date
2403 ,p_bg_id => p_params.business_group_id);
2404
2405 -- read BEN eligibility output into structure
2406 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
2407 ,p_eligibility_table => t_eligibility_table
2408 ,p_counter => v_eligibility_counter);
2409
2410 else
2411 per_cagr_utility_pkg.put_log(' No active criteria lines found for the collective agreement.',1);
2412 close csr_SA_drive_benmngle;
2413 end if;
2414
2415 -- open the cursor, to get current entitlements
2416 FOR v_ents IN csr_SA_cagr_ents LOOP
2417 --dbms_output.put_line('loop number '||csr_SA_cagr_ents%rowcount);
2418 -- reset flag
2419 v_write_flag := FALSE;
2420
2421 if v_last_dataitem_id is not null then
2422 if (v_ents.CAGR_ENTITLEMENT_ITEM_ID <> v_last_dataitem_id) then
2423 --dbms_output.put_line('changing dataitem');
2424 -- The dataitem that is being processed has changed so....
2425 -- (Note: this block gets invoked for dataitem entitlement set #2 thru to penultimate,
2426 -- where a cagr returns > 1 dataitem entitlement set)
2427
2428 -- Call routine to add any retained rights records for the last dataitem
2429 -- to the process set, evaluate their beneficial rule, and
2430 -- return the completed process set, ready for writing.
2431 add_related_ret_rights(p_params.assignment_id
2432 ,v_last_dataitem_id
2433 ,p_params.effective_date
2434 ,t_results_table
2435 ,v_counter);
2436
2437 -- insert a record into the global pl/sql table for the entitlement item
2438 -- so that add_other_ret_rights does not also process the rr.
2439 v_ent_count := v_ent_count + 1;
2440 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
2441
2442 -- apply beneficial rule and write any valid entitlement results for the
2443 -- previous dataitem, and clear the plsql table.
2444 if v_counter > 0 then
2445 -- determine and set most beneficial value for results set
2446 set_beneficial_value(p_effective_date => p_params.effective_date
2447 ,p_results_table => t_results_table
2448 ,p_ben_rule => v_beneficial_rule
2449 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2450 ,p_ben_value => v_beneficial_value
2451 ,p_ben_row => v_ben_row
2452 ,p_rule_inconclusive => v_rule_inconclusive);
2453
2454 if v_rule_inconclusive then
2455 -- output warning message that beneficial could not be chosen
2456 -- and write results anyway..
2457 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2458 end if;
2459 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2460 update_result_set(t_results_table,p_params,'W');
2461 v_counter := 0;
2462 t_results_table.delete;
2463 end if;
2464 -- store new dataitem_id
2465 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2466 -- store new beneficial_rule
2467 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2468 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2469 per_cagr_utility_pkg.put_log(' ',1);
2470 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2471 end if;
2472 else
2473 --dbms_output.put_line('first dataitem');
2474 -- set dataitem and beneficial rule value on first iteration
2475 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2476 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2477 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2478 per_cagr_utility_pkg.put_log(' ',1);
2479 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2480 end if;
2481
2482 v_value := NULL; -- clear result variables before eval
2483 v_range_from := NULL;
2484 v_range_to := NULL;
2485 v_grade_spine_id := NULL;
2486 v_parent_spine_id := NULL;
2487 v_step_id := NULL;
2488 v_from_step_id := NULL;
2489 v_to_step_id := NULL;
2490
2491 -- determine whether current record is just entitlement item
2492 -- or entitlement line, and exec ff accordingly...
2493 if v_ents.formula_criteria = 'C' then -- ent line record
2494 if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
2495 l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
2496 ,NULL
2497 ,p_params.effective_date);
2498 else
2499 l_source_name := '*** Default ***';
2500 end if;
2501 per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
2502 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '||v_ents.cagr_entitlement_line_id);
2503
2504 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2505 -- write the record as this is default elig line
2506 v_value := v_ents.value;
2507 v_range_from := v_ents.range_from;
2508 v_range_to := v_ents.range_to;
2509 v_grade_spine_id := v_ents.grade_spine_id;
2510 v_parent_spine_id := v_ents.parent_spine_id;
2511 v_step_id := v_ents.step_id;
2512 v_from_step_id := v_ents.from_step_id;
2513 v_to_step_id := v_ents.to_step_id;
2514 v_write_flag := TRUE;
2515 else -- regular eligbility line
2516 if v_eligibility_counter <> 0 then -- we ran benmngle so
2517 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
2518 -- has a valid eligibility
2519 if check_entitlement_eligible(p_OIPL_ID => v_ents.OIPL_ID
2520 ,p_eligibility_table => t_eligibility_table) then
2521 -- entitlement_line is eligible so assign its value mark record for writing
2522 v_value := v_ents.value;
2523 v_range_from := v_ents.range_from;
2524 v_range_to := v_ents.range_to;
2525 v_grade_spine_id := v_ents.grade_spine_id;
2526 v_parent_spine_id := v_ents.parent_spine_id;
2527 v_step_id := v_ents.step_id;
2528 v_from_step_id := v_ents.from_step_id;
2529 v_to_step_id := v_ents.to_step_id;
2530 v_write_flag := TRUE;
2531 end if;
2532 else
2533 -- log error that there are no BEN eligibility result records returned
2534 -- from benmngle, for this compensation_object
2535 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2536 end if;
2537 end if;
2538
2539 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2540 -- check the asg grade matches the grade_spine grade, as well as elig profile
2541 -- being satisfied, in order to be eligible for this PYS criteria.
2542 if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2543 ,p_params.effective_date),-1) then
2544 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2545 v_write_flag := FALSE;
2546 end if;
2547 end if;
2548
2549 elsif v_ents.formula_criteria = 'F' then -- ent record
2550 if v_ents.FORMULA_ID is not null then
2551 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2552 l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2553 ,v_ents.FORMULA_ID
2554 ,p_params.effective_date);
2555 per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2556
2557
2558 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2559 ,p_effective_date => p_params.effective_date
2560 ,p_assignment_id => p_params.assignment_id
2561 ,p_category_name => v_ents.category_name
2562 ,p_out_rec => l_cagr_FF_record);
2563
2564 -- assign FF return values to local vars if set
2565 if v_ents.category_name in ('ASG','PAY','ABS') then
2566 if l_cagr_FF_record.value is not null then
2567 v_value := l_cagr_FF_record.value;
2568 v_range_from := l_cagr_FF_record.range_from;
2569 v_range_to := l_cagr_FF_record.range_to;
2570 v_write_flag := TRUE;
2571 else
2572 -- log message as the formula evaluated to null and continue with next entitlement record
2573 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2574 v_write_flag := FALSE;
2575 end if;
2576 elsif v_ents.category_name = 'PYS' then
2577 if l_cagr_FF_record.grade_spine_id is not null
2578 and l_cagr_FF_record.parent_spine_id is not null
2579 and l_cagr_FF_record.step_id is not null then
2580 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
2581 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
2582 v_step_id := l_cagr_FF_record.step_id;
2583 v_from_step_id := l_cagr_FF_record.from_step_id;
2584 v_to_step_id := l_cagr_FF_record.to_step_id;
2585 v_write_flag := TRUE;
2586 else
2587 -- log message as the formula didn't evaluated to null and continue with next entitlement record
2588 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2589 end if;
2590 end if;
2591 end if;
2592 end if;
2593
2594 if v_write_flag = TRUE then
2595 -- Assign the successfully evaluated entitlement into the plsql table.
2596 v_counter := v_counter + 1;
2597
2598 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_ents.COLLECTIVE_AGREEMENT_ID;
2599 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2600 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
2601 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
2602 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
2603 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
2604 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
2605 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
2606 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
2607 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
2608 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
2609 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
2610 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
2611 t_results_table(v_counter).VALUE := v_value;
2612 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
2613 t_results_table(v_counter).RANGE_FROM := v_range_from;
2614 t_results_table(v_counter).RANGE_TO := v_range_to;
2615 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
2616 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
2617 t_results_table(v_counter).STEP_ID := v_step_id;
2618 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
2619 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
2620 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
2621 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
2622 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
2623 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
2624 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
2625
2626
2627
2628 end if;
2629 END LOOP;
2630
2631
2632 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
2633 -- returned by the above cursor, which could also be the first
2634 if v_last_dataitem_id is not null then
2635 -- Call routine to add any retained rights records for the last dataitem
2636 -- to the process set, evaluate their beneficial rule, and
2637 -- return the completed process set, ready for writing.
2638 add_related_ret_rights(p_params.assignment_id
2639 ,v_last_dataitem_id
2640 ,p_params.effective_date
2641 ,t_results_table
2642 ,v_counter);
2643
2644
2645 -- insert a record into the ent_item pl/sql table for the entitlement item
2646 -- so that add_other_ret_rights does not also process the rr.
2647 v_ent_count := v_ent_count + 1;
2648 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
2649
2650 -- apply beneficial rule and write any valid entitlement results for the
2651 -- previous dataitem, and clear the plsql table.
2652 if v_counter > 0 then
2653 -- determine and set most beneficial value for results set
2654 set_beneficial_value(p_effective_date => p_params.effective_date
2655 ,p_results_table => t_results_table
2656 ,p_ben_rule => v_beneficial_rule
2657 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2658 ,p_ben_value => v_beneficial_value
2659 ,p_ben_row => v_ben_row
2660 ,p_rule_inconclusive => v_rule_inconclusive);
2661
2662 if v_rule_inconclusive then
2663 -- output warning message that beneficial could not be chosen
2664 -- and write results anyway..
2665 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2666 end if;
2667 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2668 update_result_set(t_results_table,p_params,'W');
2669 t_results_table.delete;
2670 v_counter := 0;
2671 end if;
2672 else
2673 per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
2674 end if;
2675
2676 -- when not in SE mode, also need to add in any other retained rights for dataitems
2677 -- that are not related to the dataitems returned by the current entitlements
2678 -- set above. This could process multiple entitlements for multiple dataitems
2679 add_other_ret_rights(p_params);
2680
2681 -- clear out the global items table and chosen results table
2682 g_entitlement_items.DELETE;
2683 t_chosen_table.DELETE;
2684
2685 elsif p_params.operation_mode = 'SE' then
2686 --
2687 -- ****** Single Entitlement mode *******
2688 --
2689 -- This mode differs in behaviour from Single Assignment as follows:
2690 -- Only regenerates or returns results for 1 entitlement on the cagr.
2691 -- Returns result directly to calling code, via structure, or an HR error number.
2692 -- Cached results are not automatically wiped and replaced:
2693 -- if check_cache = 'Y' and result found then no re-evaluation, and cached result is returned
2694 -- if check_cache = 'N' or result not found then re-evaluates results, but these are
2695 -- only written to cache if p_commit_flag = 'Y'. (If p_commit_flag = N, new result returned only).
2696 -- When regenerating results, if the process is unable to lock the cache record to be refreshed then refresh will
2697 -- not be attempted and the existing beneficial value should be returned, if any exists.
2698 -- Only processes related retained rights, not other retained rights.
2699 -- Errors are: HR_289577_CAGR_NO_DATA_FOUND - no entitlement result exists for the entitlement_id
2700 -- HR_289578_CAGR_NO_BENEFICIAL - no beneficial rule or rule was inconclusive
2701 -- HR_289579_CAGR_SECONDARY_ASG - secondary assignment
2702
2703
2704 -- check asg is primary
2705 open csr_primary_asg;
2706 fetch csr_primary_asg into v_dummy;
2707 if csr_primary_asg%found then
2708 v_primary_flag := TRUE;
2709 end if;
2710 close csr_primary_asg;
2711
2712 If p_params.commit_flag = 'Y' then
2713 -- first populate pl/sql table with chosen results from cache for the assignment, if committing.
2714 t_chosen_table := store_chosen_results(p_params.assignment_id
2715 ,p_params.effective_date);
2716 end if;
2717
2718 if nvl(fnd_profile.value('PER_CHECK_ENTITLEMENT_CACHE'),'N') = 'Y' and v_primary_flag then
2719 per_cagr_utility_pkg.put_log(' Profile value set to check entitlement cache before evaluating');
2720
2721 -- check the cache
2722 p_SE_rec := check_cache(p_params.assignment_id
2723 ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2724 ,p_params.entitlement_item_id
2725 ,p_params.effective_date);
2726
2727 l_cache_checked := TRUE;
2728 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND'
2729 or p_SE_rec.error = 'HR_289578_CAGR_NO_BENEFICIAL' then
2730 -- not found in cache so we will need to re-evaluate
2731 l_evaluate := TRUE;
2732 end if;
2733 else
2734 p_SE_rec.error := 'HR_289577_CAGR_NO_DATA_FOUND';
2735 per_cagr_utility_pkg.put_log(' Profile value set to always re-evaluate');
2736 l_evaluate := TRUE;
2737 end if;
2738
2739
2740 if l_evaluate and v_primary_flag then
2741
2742 -- Invoke benmngle to process all entitlements (options) for the CAGR_ID (plan)
2743 -- on this assignment, if we determine that there are entitlement_lines in existence
2744 -- for the single entitlement_item on the cagr on the effective_date.
2745
2746 open csr_SE_drive_benmngle;
2747 fetch csr_SE_drive_benmngle into v_SE_drive_benmngle;
2748 if csr_SE_drive_benmngle%found then
2749 close csr_SE_drive_benmngle;
2750 -- start benmngle, for all entitlements, but may be quicker to call it once for each
2751 -- option_in_plan for the single entitlement?
2752 --
2753 process_entitlement_lines(p_pl_id => v_SE_drive_benmngle.pl_id
2754 ,p_opt_id => NULL -- still run for all entitlement items
2755 ,p_person_id => v_SE_drive_benmngle.person_id
2756 ,p_benefit_action_id => v_benefit_action_id
2757 ,p_effective_date => p_params.effective_date
2758 ,p_bg_id => p_params.business_group_id);
2759
2760 -- read BEN eligibility output into structure
2761 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
2762 ,p_eligibility_table => t_eligibility_table
2763 ,p_counter => v_eligibility_counter);
2764
2765 else
2766 per_cagr_utility_pkg.put_log(' No active entitlement lines exist for collective agreement');
2767 close csr_SE_drive_benmngle;
2768 end if;
2769
2770 -- open the cursor, to get single entitlement data
2771 FOR v_ents IN csr_SE_cagr_ents LOOP
2772 -- set the beneficial rule for later use...
2773 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2774 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2775
2776 v_last_dataitem_id := v_ents.cagr_entitlement_item_id;
2777 v_write_flag := FALSE;
2778
2779 v_value := NULL; -- clear result variables before eval
2780 v_range_from := NULL;
2781 v_range_to := NULL;
2782 v_grade_spine_id := NULL;
2783 v_parent_spine_id := NULL;
2784 v_step_id := NULL;
2785 v_from_step_id := NULL;
2786 v_to_step_id := NULL;
2787
2788 -- determine whether current record is entitlement item (so run ff) or entitlement line
2789 if v_ents.formula_criteria = 'C' then -- line item record
2790 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
2791 ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
2792
2793 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2794 -- write the record as this is default elig line
2795 v_value := v_ents.value;
2796 v_range_from := v_ents.range_from;
2797 v_range_to := v_ents.range_to;
2798 v_grade_spine_id := v_ents.grade_spine_id;
2799 v_parent_spine_id := v_ents.parent_spine_id;
2800 v_step_id := v_ents.step_id;
2801 v_from_step_id := v_ents.from_step_id;
2802 v_to_step_id := v_ents.to_step_id;
2803 v_write_flag := TRUE;
2804 else -- regular eligibility line
2805 if v_eligibility_counter <> 0 then -- we ran benmngle
2806 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
2807 -- has a valid eligibility
2808 if check_entitlement_eligible(p_OIPL_ID => v_ents.OIPL_ID
2809 ,p_eligibility_table => t_eligibility_table) then
2810 -- entitlement_line is eligible so assign its value mark record for writing
2811 v_value := v_ents.value;
2812 v_range_from := v_ents.range_from;
2813 v_range_to := v_ents.range_to;
2814 v_grade_spine_id := v_ents.grade_spine_id;
2815 v_parent_spine_id := v_ents.parent_spine_id;
2816 v_step_id := v_ents.step_id;
2817 v_from_step_id := v_ents.from_step_id;
2818 v_to_step_id := v_ents.to_step_id;
2819 v_write_flag := TRUE;
2820 end if;
2821 else
2822 -- log error that there are no BEN eligibility result records returned by benmngle
2823 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2824 end if;
2825 end if;
2826
2827 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2828 -- check the asg grade matches the grade_spine grade, as well as elig profile
2829 -- being satisfied, in order to be eligible for this PYS criteria.
2830 if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2831 ,p_params.effective_date),-1) then
2832 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2833 v_write_flag := FALSE;
2834 end if;
2835 end if;
2836
2837 elsif v_ents.formula_criteria = 'F' then -- item record
2838 if v_ents.FORMULA_ID is not null then
2839 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
2840 ||v_ents.item_name||', calling ff');
2841
2842 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2843 ,p_effective_date => p_params.effective_date
2844 ,p_assignment_id => p_params.assignment_id
2845 ,p_category_name => v_ents.category_name
2846 ,p_out_rec => l_cagr_FF_record);
2847
2848 -- assign FF return values to local vars if set
2849 if v_ents.category_name in ('ASG','PAY','ABS') then
2850 if l_cagr_FF_record.value is not null then
2851 v_value := l_cagr_FF_record.value;
2852 v_range_from := l_cagr_FF_record.range_from;
2853 v_range_to := l_cagr_FF_record.range_to;
2854 v_write_flag := TRUE;
2855 else
2856 -- log message as the formula evaluated to null and continue with next entitlement record
2857 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2858 v_write_flag := FALSE;
2859 end if;
2860 elsif v_ents.category_name = 'PYS' then
2861 if l_cagr_FF_record.grade_spine_id is not null
2862 and l_cagr_FF_record.parent_spine_id is not null
2863 and l_cagr_FF_record.step_id is not null then
2864 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
2865 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
2866 v_step_id := l_cagr_FF_record.step_id;
2867 v_from_step_id := l_cagr_FF_record.from_step_id;
2868 v_to_step_id := l_cagr_FF_record.to_step_id;
2869 v_write_flag := TRUE;
2870 else
2871 -- log message as the formula didn't evaluated to null and continue with next entitlement record
2872 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2873 end if;
2874 end if;
2875 end if;
2876 end if;
2877
2878 if v_write_flag = TRUE then
2879 -- Assign the successfully evaluated entitlement into the plsql table.
2880 v_counter := v_counter + 1;
2881
2882 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_ents.COLLECTIVE_AGREEMENT_ID;
2883 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2884 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
2885 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
2886 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
2887 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
2888 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
2889 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
2890 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
2891 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
2892 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
2893 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
2894 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
2895 t_results_table(v_counter).VALUE := v_value;
2896 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
2897 t_results_table(v_counter).RANGE_FROM := v_range_from;
2898 t_results_table(v_counter).RANGE_TO := v_range_to;
2899 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
2900 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
2901 t_results_table(v_counter).STEP_ID := v_step_id;
2902 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
2903 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
2904 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
2905 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
2906 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
2907 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
2908 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
2909
2910 end if;
2911 END LOOP;
2912
2913 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
2914 -- returned by the above cursor, which could also be the first
2915 if v_last_dataitem_id is not null then
2916 -- Call routine to add any retained rights records for the last dataitem
2917 -- to the process set, evaluate their beneficial rule, and
2918 -- return the completed process set, ready for writing.
2919 add_related_ret_rights(p_params.assignment_id
2920 ,v_last_dataitem_id
2921 ,p_params.effective_date
2922 ,t_results_table
2923 ,v_counter);
2924
2925 -- apply beneficial rule and write any valid entitlement results for the
2926 -- previous dataitem, and clear the plsql table.
2927 if v_counter > 0 then
2928 -- determine and set most beneficial value for results set
2929 set_beneficial_value(p_effective_date => p_params.effective_date
2930 ,p_results_table => t_results_table
2931 ,p_ben_rule => v_beneficial_rule
2932 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2933 ,p_ben_value => v_beneficial_value
2934 ,p_ben_row => v_ben_row
2935 ,p_rule_inconclusive => v_rule_inconclusive);
2936
2937 if not(l_cache_checked) then
2938 -- check the cache, if it wasn't done above
2939 p_SE_rec := check_cache(p_params.assignment_id
2940 ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2941 ,p_params.entitlement_item_id
2942 ,p_params.effective_date);
2943 end if;
2944
2945
2946 -- eval commit param to see if we should write results to cache before returning structure.
2947 if p_params.commit_flag = 'Y' then
2948 l_update_cache := TRUE;
2949 else
2950 l_update_cache := FALSE;
2951 end if;
2952
2953 if l_update_cache then
2954 -- only write new results to cache if p_commit_flag allows
2955 BEGIN
2956 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' then
2957 -- write new result set to cache, as none was found
2958 insert_result_set(t_results_table, p_params);
2959 elsif p_SE_rec.error is NULL or p_SE_rec.error = 'HR_289578_CAGR_NO_BENEFICIAL' then
2960 -- 'update' cache with results from re-evaluations (whether they differ or not)
2961 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2962 update_result_set(t_results_table,p_params,'W');
2963 p_SE_rec.ERROR := NULL; -- do not return this error
2964 end if;
2965 EXCEPTION
2966 when resource_busy then
2967 per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on per_cagr_entitlement_results');
2968 per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
2969 -- but this is not fatal in this mode, so continue and pass out results
2970 END;
2971 else -- not updating the cache, but we have regenerated data so nullify this error now
2972 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' and t_results_table.count > 0 then
2973 p_SE_rec.ERROR := NULL; -- do not return this error
2974 end if;
2975 end if;
2976
2977 if v_rule_inconclusive then
2978 -- output warning message that ben rule failed to id a beneficial result
2979 p_SE_rec.ERROR := 'HR_289417_CAGR_BENRULE_FAIL';
2980 else
2981 if v_ben_row > 0 then
2982 -- populate output structure, with beneficial entitlement value or error code
2983 p_SE_rec.VALUE := t_results_table(v_ben_row).VALUE;
2984 p_SE_rec.RANGE_FROM := t_results_table(v_ben_row).RANGE_FROM;
2985 p_SE_rec.RANGE_TO := t_results_table(v_ben_row).RANGE_TO;
2986 p_SE_rec.GRADE_SPINE_ID := t_results_table(v_ben_row).GRADE_SPINE_ID;
2987 p_SE_rec.PARENT_SPINE_ID := t_results_table(v_ben_row).PARENT_SPINE_ID;
2988 p_SE_rec.STEP_ID := t_results_table(v_ben_row).STEP_ID;
2989 p_SE_rec.FROM_STEP_ID := t_results_table(v_ben_row).FROM_STEP_ID;
2990 p_SE_rec.TO_STEP_ID := t_results_table(v_ben_row).TO_STEP_ID;
2991 else
2992 p_SE_rec.ERROR := 'HR_289578_CAGR_NO_BENEFICIAL';
2993 end if;
2994 end if;
2995 end if;
2996 else
2997 -- no active ents matched the entitlement id
2998 p_SE_rec.error := 'HR_289577_CAGR_NO_DATA_FOUND';
2999 end if;
3000 -- clear out the global items table
3001 g_entitlement_items.DELETE;
3002 end if;
3003
3004 if p_params.commit_flag = 'Y' and t_chosen_table.count <> 0 then
3005 t_chosen_table.DELETE;
3006 end if;
3007 t_results_table.DELETE;
3008 v_counter := 0;
3009
3010 -- return error if called for secondary asg
3011 if not(v_primary_flag) then
3012 p_SE_rec.ERROR := 'HR_289579_CAGR_SECONDARY_ASG';
3013 end if;
3014 per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3015
3016 -- log return values...
3017 per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3018 per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3019 per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020 per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021 per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022 per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023 per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3024 per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3025 per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3026 --
3027 --
3028 elsif p_params.operation_mode = 'SC' then
3029 --
3030 -- ****** Single Collective Agreement mode *******
3031 --
3032 -- features of this mode:
3033 -- 1) it processes all assignments found for the cagr on the effective_date
3034 -- 2) benmngle runs at pl level (processes all people) if there are asgs on the cagr, and it has lines
3035 -- 3) each assignment has a new request / separate logs, under the parent request
3036 -- 4) when run from CM, all logs are output to O/S file, under the parent_request_id
3037 -- 5) Conditionally calls apply process once all evaluation is complete (with null assignment_id)
3038
3039 -- Invoke benmngle to process all entitlements (options) for all people on the CAGR_ID (plan)
3040 -- if we determine that there are entitlement_lines in existence
3041 -- for any items on the cagr, on the effective_date (not inc. default elig lines),
3042 -- and that there are assignments on the cagr on that date.
3043
3044
3045 open csr_SC_drive_benmngle;
3046 fetch csr_SC_drive_benmngle into l_pl_id;
3047 if csr_SC_drive_benmngle%found then
3048 close csr_SC_drive_benmngle;
3049 -- start benmngle
3050 process_entitlement_lines(p_pl_id => l_pl_id
3051 ,p_opt_id => NULL -- running at plan level here
3052 ,p_person_id => NULL -- for all people
3053 ,p_benefit_action_id => v_benefit_action_id
3054 ,p_effective_date => p_params.effective_date
3055 ,p_bg_id => p_params.business_group_id);
3056
3057 -- read BEN eligibility output into structure (for all people) on the cagr
3058 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
3059 ,p_eligibility_table => t_eligibility_table
3060 ,p_counter => v_eligibility_counter);
3061
3062 else
3063 per_cagr_utility_pkg.put_log(' No assignments use the collective agreement',1);
3064 per_cagr_utility_pkg.put_log(' or no active criteria lines found for the collective agreement.',1);
3065 close csr_SC_drive_benmngle;
3066 end if;
3067
3068 per_cagr_utility_pkg.put_log('Processing the following assignments on the collective agreement: ',1);
3069 --
3070 -- load all the assignment ids to be processed into pl/sql table.
3071 --
3072 open csr_assignments_to_process;
3073 loop
3074 v_counter := v_counter+1;
3075 fetch csr_assignments_to_process into t_assignments_table(v_counter);
3076 exit when csr_assignments_to_process%notfound;
3077 per_cagr_utility_pkg.put_log(' '||t_assignments_table(v_counter).assignment_id,1);
3078 end loop;
3079 close csr_assignments_to_process;
3080 v_counter := 0;
3081
3082 -- write the log out and save the request_id
3083 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3084 l_parent_request_id := p_params.cagr_request_id;
3085
3086 -- could now break pl/sql table into varray subsets, ready for multiple threads
3087
3088 -- loop through assignment_id table
3089 if t_assignments_table.count <> 0 then
3090 FOR k in t_assignments_table.first .. t_assignments_table.last LOOP
3091 Begin
3092
3093 p_params.assignment_id := t_assignments_table(k).assignment_id;
3094
3095 -- for each asg on the cagr on the effective_date:
3096 -- 1) create a request_id (for asg logging)
3097 -- 2) clean results cache
3098 -- 2) do SA style cursor processing and logging (in a function for the SA code)
3099 -- 3) commit AFTER each assignment
3100
3101 per_cagr_utility_pkg.create_cagr_request(p_process_date => p_params.effective_date
3102 ,p_operation_mode => 'SA'
3103 ,p_business_group_id => p_params.business_group_id
3104 ,p_assignment_id => t_assignments_table(k).assignment_id
3105 ,p_assignment_set_id => NULL
3106 ,p_collective_agreement_id => p_params.collective_agreement_id
3107 ,p_collective_agreement_set_id => NULL
3108 ,p_payroll_id => NULL
3109 ,p_person_id => NULL
3110 ,p_entitlement_item_id => NULL
3111 ,p_parent_request_id => l_parent_request_id
3112 ,p_commit_flag => p_params.commit_flag
3113 ,p_denormalise_flag => p_params.denormalise_flag
3114 ,p_cagr_request_id => p_params.cagr_request_id);
3115 -- output log header
3116 per_cagr_utility_pkg.put_log(g_head_separator,1);
3117 per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3118 per_cagr_utility_pkg.put_log(g_head_separator,1);
3119 per_cagr_utility_pkg.put_log(' ',1);
3120 per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121 ' during Single Collective Agreement mode.',1);
3122 per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3123 per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3124 per_cagr_utility_pkg.put_log(' ',1);
3125
3126 If p_params.commit_flag = 'Y' then
3127 -- first create pl/sql table with chosen results for all items for the asg from cache, if committing.
3128 t_chosen_table := store_chosen_results(p_params.assignment_id
3129 ,p_params.effective_date);
3130 end if;
3131
3132 -- clean the cache of existing records for all items on this asg - effective date comb
3133 --
3134 update_result_set(t_results_table,p_params,'C');
3135
3136 --
3137 -- start cursor processing for the current asg
3138 --
3139
3140 FOR v_ents IN csr_SC_cagr_details LOOP
3141 -- reset flag
3142 v_write_flag := FALSE;
3143
3144 if v_last_dataitem_id is not null then
3145 if (v_ents.CAGR_ENTITLEMENT_ITEM_ID <> v_last_dataitem_id) then
3146 --dbms_output.put_line('changing dataitem');
3147 -- The dataitem that is being processed has changed so....
3148 -- (Note: this block gets invoked for dataitem entitlement set #2 thru to penultimate,
3149 -- where a cagr returns > 1 dataitem entitlement set)
3150
3151 -- Call routine to add any retained rights records for the last dataitem
3152 -- to the process set, evaluate their beneficial rule, and
3153 -- return the completed process set, ready for writing.
3154 add_related_ret_rights(p_params.assignment_id
3155 ,v_last_dataitem_id
3156 ,p_params.effective_date
3157 ,t_results_table
3158 ,v_counter);
3159
3160 -- insert a record into the global pl/sql table for the entitlement item
3161 -- so that add_other_ret_rights does not also process the rr.
3162 v_ent_count := v_ent_count + 1;
3163 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
3164
3165 -- apply beneficial rule and write any valid entitlement results for the
3166 -- previous dataitem, and clear the plsql table.
3167 if v_counter > 0 then
3168 -- determine and set most beneficial value for results set
3169 set_beneficial_value(p_effective_date => p_params.effective_date
3170 ,p_results_table => t_results_table
3171 ,p_ben_rule => v_beneficial_rule
3172 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
3173 ,p_ben_value => v_beneficial_value
3174 ,p_ben_row => v_ben_row
3175 ,p_rule_inconclusive => v_rule_inconclusive);
3176
3177 if v_rule_inconclusive then
3178 -- output warning message that beneficial could not be chosen
3179 -- and write results anyway..
3180 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3181 end if;
3182 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3183 update_result_set(t_results_table,p_params,'W');
3184 v_counter := 0;
3185 t_results_table.delete;
3186 end if;
3187 -- store new dataitem_id
3188 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3189 -- store new beneficial_rule
3190 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3191 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3192 per_cagr_utility_pkg.put_log(' ',1);
3193 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3194 end if;
3195 else
3196 --dbms_output.put_line('first dataitem');
3197 -- set dataitem and beneficial rule value on first iteration
3198 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3199 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3200 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3201 per_cagr_utility_pkg.put_log(' ',1);
3202 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3203 end if;
3204
3205 v_value := NULL; -- clear result variables before eval
3206 v_range_from := NULL;
3207 v_range_to := NULL;
3208 v_grade_spine_id := NULL;
3209 v_parent_spine_id := NULL;
3210 v_step_id := NULL;
3211 v_from_step_id := NULL;
3212 v_to_step_id := NULL;
3213
3214 -- determine whether current record is just entitlement item
3215 -- or entitlement line, and exec ff accordingly...
3216 if v_ents.formula_criteria = 'C' then -- ent line record
3217 if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
3218 l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
3219 ,NULL
3220 ,p_params.effective_date);
3221 else
3222 l_source_name := '*** Default ***';
3223 end if;
3224 per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
3225 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '
3226 ||v_ents.cagr_entitlement_line_id);
3227
3228 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3229 -- write the record as this is default elig line
3230 v_value := v_ents.value;
3231 v_range_from := v_ents.range_from;
3232 v_range_to := v_ents.range_to;
3233 v_grade_spine_id := v_ents.grade_spine_id;
3234 v_parent_spine_id := v_ents.parent_spine_id;
3235 v_step_id := v_ents.step_id;
3236 v_from_step_id := v_ents.from_step_id;
3237 v_to_step_id := v_ents.to_step_id;
3238 v_write_flag := TRUE;
3239 else -- regular eligbility line
3240 if v_eligibility_counter <> 0 then -- we ran benmngle so
3241 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
3242 -- has a valid eligibility
3243 if check_entitlement_eligible(p_person_id => t_assignments_table(k).person_id
3244 ,p_OIPL_ID => v_ents.OIPL_ID
3245 ,p_eligibility_table => t_eligibility_table) then
3246 -- entitlement_line is eligible so assign its value mark record for writing
3247 v_value := v_ents.value;
3248 v_range_from := v_ents.range_from;
3249 v_range_to := v_ents.range_to;
3250 v_grade_spine_id := v_ents.grade_spine_id;
3251 v_parent_spine_id := v_ents.parent_spine_id;
3252 v_step_id := v_ents.step_id;
3253 v_from_step_id := v_ents.from_step_id;
3254 v_to_step_id := v_ents.to_step_id;
3255 v_write_flag := TRUE;
3256 end if;
3257 else
3258 -- log error that there are no BEN eligibility result records returned
3259 -- from benmngle, for this compensation_object
3260 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3261 end if;
3262 end if;
3263 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3264 -- check the asg grade matches the grade_spine grade, as well as elig profile
3265 -- being satisfied, in order to be eligible for this PYS criteria.
3266 if nvl(t_assignments_table(k).grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3267 ,p_params.effective_date),-1) then
3268 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spi
3269 ne. ',1);
3270 v_write_flag := FALSE;
3271 end if;
3272 end if;
3273
3274 elsif v_ents.formula_criteria = 'F' then -- ent record
3275 if v_ents.FORMULA_ID is not null then
3276 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3277 l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3278 ,v_ents.FORMULA_ID
3279 ,p_params.effective_date);
3280 per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3281
3282
3283 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3284 ,p_effective_date => p_params.effective_date
3285 ,p_assignment_id => p_params.assignment_id
3286 ,p_category_name => v_ents.category_name
3287 ,p_out_rec => l_cagr_FF_record);
3288
3289 -- assign FF return values to local vars if set
3290 if v_ents.category_name in ('ASG','PAY','ABS') then
3291 if l_cagr_FF_record.value is not null then
3292 v_value := l_cagr_FF_record.value;
3293 v_range_from := l_cagr_FF_record.range_from;
3294 v_range_to := l_cagr_FF_record.range_to;
3295 v_write_flag := TRUE;
3296 else
3297 -- log message as the formula evaluated to null and continue with next entitlement record
3298 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3299 v_write_flag := FALSE;
3300 end if;
3301 elsif v_ents.category_name = 'PYS' then
3302 if l_cagr_FF_record.grade_spine_id is not null
3303 and l_cagr_FF_record.parent_spine_id is not null
3304 and l_cagr_FF_record.step_id is not null then
3305 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
3306 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
3307 v_step_id := l_cagr_FF_record.step_id;
3308 v_from_step_id := l_cagr_FF_record.from_step_id;
3309 v_to_step_id := l_cagr_FF_record.to_step_id;
3310 v_write_flag := TRUE;
3311 else
3312 -- log message as the formula didn't evaluated to null and continue with next entitlement record
3313 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3314 end if;
3315 end if;
3316 end if;
3317 end if;
3318
3319 if v_write_flag = TRUE then
3320 -- Assign the successfully evaluated entitlement into the plsql table.
3321 v_counter := v_counter + 1;
3322
3323 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := p_params.COLLECTIVE_AGREEMENT_ID;
3324 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3325 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
3326 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
3327 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
3328 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
3329 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
3330 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
3331 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
3332 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
3333 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
3334 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
3335 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
3336 t_results_table(v_counter).VALUE := v_value;
3337 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
3338 t_results_table(v_counter).RANGE_FROM := v_range_from;
3339 t_results_table(v_counter).RANGE_TO := v_range_to;
3340 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
3341 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
3342 t_results_table(v_counter).STEP_ID := v_step_id;
3343 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
3344 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
3345 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
3346 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
3347 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
3348 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
3349 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
3350
3351
3352
3353 end if;
3354 END LOOP; -- csr_SC_cagr_datails
3355
3356
3357 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
3358 -- returned by the above cursor, which could also be the first
3359 if v_last_dataitem_id is not null then
3360 -- Call routine to add any retained rights records for the last dataitem
3361 -- to the process set, evaluate their beneficial rule, and
3362 -- return the completed process set, ready for writing.
3363 add_related_ret_rights(p_params.assignment_id
3364 ,v_last_dataitem_id
3365 ,p_params.effective_date
3366 ,t_results_table
3367 ,v_counter);
3368
3369 -- insert a record into the ent_item pl/sql table for the entitlement item
3370 -- so that add_other_ret_rights does not also process the rr.
3371 v_ent_count := v_ent_count + 1;
3372 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
3373
3374 -- apply beneficial rule and write any valid entitlement results for the
3375 -- previous dataitem, and clear the plsql table.
3376 if v_counter > 0 then
3377 -- determine and set most beneficial value for results set
3378 set_beneficial_value(p_effective_date => p_params.effective_date
3379 ,p_results_table => t_results_table
3380 ,p_ben_rule => v_beneficial_rule
3381 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
3382 ,p_ben_value => v_beneficial_value
3383 ,p_ben_row => v_ben_row
3384 ,p_rule_inconclusive => v_rule_inconclusive);
3385
3386 if v_rule_inconclusive then
3387 -- output warning message that beneficial could not be chosen
3388 -- and write results anyway..
3389 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3390 end if;
3391 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3392 update_result_set(t_results_table,p_params,'W');
3393 t_results_table.delete;
3394 v_counter := 0;
3395 end if;
3396 else
3397 per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
3398 end if;
3399
3400 -- add in any other retained rights for dataitems
3401 -- that are not related to the dataitems returned by the current entitlements
3402 -- set above. This could process multiple entitlements for multiple dataitems
3403 add_other_ret_rights(p_params);
3404
3405 -- clear out the global items table and chosen results table.
3406 g_entitlement_items.DELETE;
3407 t_chosen_table.DELETE;
3408
3409 -- reset write header flag
3410
3411 per_cagr_utility_pkg.put_log(' ',1);
3412 per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3413 per_cagr_utility_pkg.put_log(' ',1);
3414
3415 --
3416 -- Commit, if required, after every assignment.
3417 --
3418 if p_params.commit_flag = 'Y' then
3419 commit;
3420 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3421 elsif p_params.commit_flag = 'N' then
3422 rollback;
3423 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3424 end if;
3425
3426 -- write the log file for this assignment
3427 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3428
3429 Exception
3430 WHEN OTHERS THEN
3431 -- write the log file for this assignment
3432 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3433 -- reset to the parent request id and assignment
3434 p_params.cagr_request_id := l_parent_request_id;
3435 p_params.assignment_id := NULL;
3436 rollback;
3437 raise;
3438 End;
3439
3440 END LOOP; -- csr_assignments_to_process
3441 end if;
3442
3443 -- reset to the parent request id and assignment
3444 p_params.cagr_request_id := l_parent_request_id;
3445 p_params.assignment_id := NULL;
3446 --
3447 --
3448 elsif p_params.operation_mode = 'BE' then
3449 --
3450 -- ****** Batch Entitlement Item mode *******
3451 --
3452 -- features of this mode:
3453 -- 1) it processes eligibility for a single entitlement item across one or all cagrs (and their assignments)
3454 -- on an effective date, for that item only. (Does not use add_other retained_rights).
3455 -- 2) benmngle is run at PL + OPTION level or OPTION level only, (processing all people on those comp objects)
3456 -- if there are any lines that use the item on any/the cagr
3457 -- 3) Although many asgs are processed, unlike SC mode, each asg is worked under the main request id
3458 -- so the resulting log entries are only visible from CM window - not in the View Log window of PERWSCAR.
3459 -- 4) Populates a pl/sql table with a set of eligible people and assignments, ordered by person_id.
3460 -- Note: Not available from conc current program. Does not call the apply process.
3461
3462 open csr_BE_drive_benmngle;
3463 fetch csr_BE_drive_benmngle into l_opt_id;
3464 close csr_BE_drive_benmngle;
3465
3466 open csr_BE_plan;
3467 fetch csr_BE_plan into l_pl_id;
3468 close csr_BE_plan;
3469
3470 If l_opt_id is not null then
3471 -- start benmngle
3472 per_cagr_utility_pkg.put_log('Starting benmngle');
3473 process_entitlement_lines(p_pl_id => l_pl_id -- may run for a cagr
3474 ,p_opt_id => l_opt_id -- always run for an iteme
3475 ,p_person_id => NULL -- for all people on the cagr(s)
3476 ,p_benefit_action_id => v_benefit_action_id
3477 ,p_effective_date => p_params.effective_date
3478 ,p_bg_id => p_params.business_group_id);
3479
3480 -- read BEN eligibility output into structure (for all people) on the cagr
3481 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
3482 ,p_eligibility_table => t_eligibility_table
3483 ,p_counter => v_eligibility_counter);
3484
3485 else
3486 per_cagr_utility_pkg.put_log(' No active criteria lines found for the item on any collective agreement.',1);
3487 end if;
3488 per_cagr_utility_pkg.put_log('Processing the following collective agreements that have entitlements using the item: ',1);
3489
3490 --
3491 -- load all the cagrs and their assignments ids to be processed into pl/sql table.
3492 --
3493 open csr_BE_assignments_to_process;
3494 loop
3495 v_counter := v_counter+1;
3496 fetch csr_BE_assignments_to_process into t_cagr_assignments_table(v_counter);
3497 exit when csr_BE_assignments_to_process%notfound;
3498 If t_cagr_assignments_table(v_counter).collective_agreement_id <> l_last_cagr_id then
3499 l_last_cagr_id := t_cagr_assignments_table(v_counter).collective_agreement_id;
3500 per_cagr_utility_pkg.put_log(' '||t_cagr_assignments_table(v_counter).collective_agreement_id,1);
3501 End If;
3502 end loop;
3503 close csr_BE_assignments_to_process;
3504 v_counter := 0;
3505
3506 -- write the log out
3507 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3508
3509 -- could now break pl/sql table into varray subsets, ready for multiple threads
3510
3511 -- loop through table of assignment_id for each cagr
3512 If t_cagr_assignments_table.count <> 0 then
3513
3514 FOR k in t_cagr_assignments_table.first .. t_cagr_assignments_table.last LOOP
3515 BEGIN
3516
3517 -- 1) Do SE type processing for the specific entitlement item
3518 -- 2) check cache for a result for the item for asg
3519 -- 3) clean results cache/write result for the specific entitlement item for the asg
3520 -- 4) commit AFTER each assignment
3521
3522 p_params.assignment_id := t_cagr_assignments_table(k).assignment_id;
3523
3524 per_cagr_utility_pkg.put_log(' ',1);
3525 per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3526 ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3527 ' during Batch Entitlement mode.',1);
3528 per_cagr_utility_pkg.put_log(' ',1);
3529
3530
3531 -- for each asg on the cagr on the effective_date:
3532
3533 FOR v_ents IN csr_SE_cagr_ents LOOP
3534 -- set the beneficial rule for later use...
3535 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3536 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3537
3538 v_last_dataitem_id := v_ents.cagr_entitlement_item_id;
3539 v_write_flag := FALSE;
3540
3541 v_value := NULL; -- clear result variables before eval
3542 v_range_from := NULL;
3543 v_range_to := NULL;
3544 v_grade_spine_id := NULL;
3545 v_parent_spine_id := NULL;
3546 v_step_id := NULL;
3547 v_from_step_id := NULL;
3548 v_to_step_id := NULL;
3549
3550
3551 -- determine whether current record is entitlement item (so run ff) or entitlement line
3552 if v_ents.formula_criteria = 'C' then -- line item record
3553 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
3554 ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
3555
3556 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3557 -- write the record as this is default elig line
3558 v_value := v_ents.value;
3559 v_range_from := v_ents.range_from;
3560 v_range_to := v_ents.range_to;
3561 v_grade_spine_id := v_ents.grade_spine_id;
3562 v_parent_spine_id := v_ents.parent_spine_id;
3563 v_step_id := v_ents.step_id;
3564 v_from_step_id := v_ents.from_step_id;
3565 v_to_step_id := v_ents.to_step_id;
3566 v_write_flag := TRUE;
3567 else -- regular eligibility line
3568 if v_eligibility_counter <> 0 then -- we ran benmngle
3569 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
3570 -- has a valid eligibility
3571 if check_entitlement_eligible(p_person_id => t_cagr_assignments_table(k).person_id
3572 ,p_OIPL_ID => v_ents.OIPL_ID
3573 ,p_eligibility_table => t_eligibility_table) then
3574 -- entitlement_line is eligible so assign its value mark record for writing
3575 v_value := v_ents.value;
3576 v_range_from := v_ents.range_from;
3577 v_range_to := v_ents.range_to;
3578 v_grade_spine_id := v_ents.grade_spine_id;
3579 v_parent_spine_id := v_ents.parent_spine_id;
3580 v_step_id := v_ents.step_id;
3581 v_from_step_id := v_ents.from_step_id;
3582 v_to_step_id := v_ents.to_step_id;
3583 v_write_flag := TRUE;
3584 end if;
3585 else
3586 -- log error that there are no BEN eligibility result records returned by benmngle
3587 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3588 end if;
3589 end if;
3590
3591 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3592 -- check the asg grade matches the grade_spine grade, as well as elig profile
3593 -- being satisfied, in order to be eligible for this PYS criteria.
3594 if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3595 ,p_params.effective_date),-1) then
3596 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
3597 v_write_flag := FALSE;
3598 end if;
3599 end if;
3600
3601 elsif v_ents.formula_criteria = 'F' then -- item record
3602 if v_ents.FORMULA_ID is not null then
3603 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
3604 ||v_ents.item_name||', calling ff');
3605
3606 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3607 ,p_effective_date => p_params.effective_date
3608 ,p_assignment_id => p_params.assignment_id
3609 ,p_category_name => v_ents.category_name
3610 ,p_out_rec => l_cagr_FF_record);
3611
3612 -- assign FF return values to local vars if set
3613 if v_ents.category_name in ('ASG','PAY','ABS') then
3614 if l_cagr_FF_record.value is not null then
3615 v_value := l_cagr_FF_record.value;
3616 v_range_from := l_cagr_FF_record.range_from;
3617 v_range_to := l_cagr_FF_record.range_to;
3618 v_write_flag := TRUE;
3619 else
3620 -- log message as the formula evaluated to null and continue with next entitlement record
3621 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3622 v_write_flag := FALSE;
3623 end if;
3624 elsif v_ents.category_name = 'PYS' then
3625 if l_cagr_FF_record.grade_spine_id is not null
3626 and l_cagr_FF_record.parent_spine_id is not null
3627 and l_cagr_FF_record.step_id is not null then
3628 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
3629 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
3630 v_step_id := l_cagr_FF_record.step_id;
3631 v_from_step_id := l_cagr_FF_record.from_step_id;
3632 v_to_step_id := l_cagr_FF_record.to_step_id;
3633 v_write_flag := TRUE;
3634 else
3635 -- log message as the formula didn't evaluated to null and continue with next entitlement record
3636 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3637 end if;
3638 end if;
3639 end if;
3640 end if;
3641
3642 if v_write_flag = TRUE then
3643 -- Assign the successfully evaluated entitlement into the plsql table.
3644 v_counter := v_counter + 1;
3645
3646 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_ents.COLLECTIVE_AGREEMENT_ID;
3647 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3648 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
3649 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
3650 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
3651 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
3652 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
3653 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
3654 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
3655 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
3656 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
3657 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
3658 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
3659 t_results_table(v_counter).VALUE := v_value;
3660 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
3661 t_results_table(v_counter).RANGE_FROM := v_range_from;
3662 t_results_table(v_counter).RANGE_TO := v_range_to;
3663 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
3664 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
3665 t_results_table(v_counter).STEP_ID := v_step_id;
3666 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
3667 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
3668 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
3669 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
3670 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
3671 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
3672 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
3673 end if;
3674 END LOOP;
3675
3676 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
3677 -- returned by the above cursor, which could also be the first
3678 if v_last_dataitem_id is not null then
3679 -- Call routine to add any retained rights records for the last dataitem
3680 -- to the process set, evaluate their beneficial rule, and
3681 -- return the completed process set, ready for writing.
3682 add_related_ret_rights(p_params.assignment_id
3683 ,v_last_dataitem_id
3684 ,p_params.effective_date
3685 ,t_results_table
3686 ,v_counter);
3687
3688 -- apply beneficial rule and write any valid entitlement results for the
3689 -- previous dataitem, and clear the plsql table.
3690 if v_counter > 0 then
3691 -- determine and set most beneficial value for results set
3692 set_beneficial_value(p_effective_date => p_params.effective_date
3693 ,p_results_table => t_results_table
3694 ,p_ben_rule => v_beneficial_rule
3695 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
3696 ,p_ben_value => v_beneficial_value
3697 ,p_ben_row => v_ben_row
3698 ,p_rule_inconclusive => v_rule_inconclusive);
3699
3700
3701
3702
3703 if p_params.commit_flag = 'Y' then
3704 -- first populate pl/sql table with chosen results from cache for the assignment, if committing.
3705 t_chosen_table := store_chosen_results(p_params.assignment_id
3706 ,p_params.effective_date);
3707 BEGIN
3708 -- check the cache, for an existing result(s) for the item
3709 p_SE_rec := check_cache(p_params.assignment_id
3710 ,t_cagr_assignments_table(k).collective_agreement_id
3711 ,p_params.entitlement_item_id
3712 ,p_params.effective_date);
3713
3714
3715 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' then
3716 -- write new result set to cache, as none was found
3717 insert_result_set(t_results_table, p_params);
3718 t_results_table.DELETE;
3719 v_counter := 0;
3720 elsif p_SE_rec.error is NULL or p_SE_rec.error = 'HR_289578_CAGR_NO_BENEFICIAL' then
3721 -- 'update' cache with results from re-evaluations (whether they differ or not)
3722 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3723 update_result_set(t_results_table,p_params,'W');
3724 t_results_table.DELETE;
3725 v_counter := 0;
3726 p_SE_rec.ERROR := NULL; -- do not return this error
3727 end if;
3728 EXCEPTION
3729 WHEN RESOURCE_BUSY THEN
3730 per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on result for assignment:'
3731 ||p_params.assignment_id);
3732 per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3733 END;
3734 end if;
3735 end if;
3736 end if;
3737
3738 if p_params.commit_flag = 'Y' and t_chosen_table.count <> 0 then
3739 -- clear out the chosen and results tables.
3740 t_chosen_table.DELETE;
3741 end if;
3742
3743
3744 -- reset write header flag
3745
3746 per_cagr_utility_pkg.put_log(' ',1);
3747 per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3748 per_cagr_utility_pkg.put_log(' ',1);
3749
3750 --
3751 -- Commit, if required, after every assignment.
3752 --
3753 if p_params.commit_flag = 'Y' then
3754 commit;
3755 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3756 elsif p_params.commit_flag = 'N' then
3757 rollback;
3758 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3759 end if;
3760
3761 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3762
3763 EXCEPTION
3764 WHEN OTHERS THEN
3765 -- write the log file for this assignment
3766 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3767 -- reset to the parent request id and assignment
3768 p_params.assignment_id := NULL;
3769 rollback;
3770 raise;
3771 END;
3772 END LOOP; -- outer assignment loop
3773 end if;
3774
3775 -- reset values
3776 p_params.assignment_id := NULL;
3777
3778 else -- Other modes....
3779 null;
3780 end if;
3781
3782 per_cagr_utility_pkg.put_log(' ',1);
3783 per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3784 per_cagr_utility_pkg.put_log(' ',1);
3785 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3786 hr_utility.set_location('Leaving:'||l_proc, 100);
3787
3788
3789 EXCEPTION
3790 WHEN OTHERS THEN
3791 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3792 raise;
3793
3794 END evaluation_process;
3795
3796 --
3797 -- ----------------------------------------------------------------------------
3798 -- |------------------------------< initialise >-----------------------------|
3799 -- ----------------------------------------------------------------------------
3800 --
3801 PROCEDURE initialise
3802 (p_process_date in date
3803 ,p_operation_mode in varchar2
3804 ,p_business_group_id in number
3805 ,p_assignment_id in number default null
3806 ,p_assignment_set_id in number default null
3807 ,p_collective_agreement_id in number default null
3808 ,p_collective_agreement_set_id in number default null
3809 ,p_payroll_id in number default null
3810 ,p_person_id in number default null
3811 ,p_entitlement_item_id in number default null
3812 ,p_commit_flag in varchar2 default 'N'
3813 ,p_apply_results_flag in varchar2 default 'N'
3814 ,p_cagr_request_id out nocopy number) IS
3815 --
3816 l_proc constant varchar2(61) := g_pkg || '.initialise';
3817 p_params PER_CAGR_EVALUATION_PKG.control_structure;
3818 l_se_rec PER_CAGR_EVALUATION_PKG.cagr_SE_record;
3819 --
3820 BEGIN
3821 hr_utility.set_location('Entering:'||l_proc, 5);
3822
3823 --
3824 -- store params and cagr return cagr_request_id for use in this run
3825 --
3826 per_cagr_utility_pkg.create_cagr_request(p_process_date => p_process_date
3827 ,p_operation_mode => p_operation_mode
3828 ,p_business_group_id => p_business_group_id
3829 ,p_assignment_id => p_assignment_id
3830 ,p_assignment_set_id => p_assignment_set_id
3831 ,p_collective_agreement_id => p_collective_agreement_id
3832 ,p_collective_agreement_set_id => p_collective_agreement_set_id
3833 ,p_payroll_id => p_payroll_id
3834 ,p_person_id => p_person_id
3835 ,p_entitlement_item_id => p_entitlement_item_id
3836 ,p_parent_request_id => NULL
3837 ,p_commit_flag => p_commit_flag
3838 ,p_denormalise_flag => p_apply_results_flag
3839 ,p_cagr_request_id => p_cagr_request_id);
3840
3841 --
3842 -- Output log header
3843 --
3844 per_cagr_utility_pkg.put_log(g_head_separator,1);
3845 per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3846 --
3847 -- Ensure that all the mandatory arguments are not null
3848 --
3849 hr_api.mandatory_arg_error(p_api_name => l_proc
3850 ,p_argument => 'process_date'
3851 ,p_argument_value => p_process_date);
3852 hr_api.mandatory_arg_error(p_api_name => l_proc
3853 ,p_argument => 'business_group_id'
3854 ,p_argument_value => p_business_group_id);
3855 hr_api.mandatory_arg_error(p_api_name => l_proc
3856 ,p_argument => 'operation_mode'
3857 ,p_argument_value => p_operation_mode);
3858
3859
3860 -- test for required params for modes
3861
3862 if not(p_operation_mode in ('SA','SE','SC','BE')) then
3863 per_cagr_utility_pkg.log_and_raise_error('HR_289420_CAGR_INV_MODE'
3864 ,p_cagr_request_id);
3865 end if;
3866 if (p_operation_mode = 'SA' and p_assignment_id is null) then
3867 per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3868 ,p_cagr_request_id);
3869 elsif (p_operation_mode = 'SE') and
3870 (p_entitlement_item_id is null or p_assignment_id is null) then
3871 per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3872 ,p_cagr_request_id);
3873 elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3874 per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3875 ,p_cagr_request_id);
3876 elsif (p_operation_mode = 'SC' and p_collective_agreement_id is null) then
3877 per_cagr_utility_pkg.log_and_raise_error('HR_289597_INV_SC_PARAM'
3878 ,p_cagr_request_id);
3879 end if;
3880
3881 --
3882 -- test for invalid params and values for modes
3883 --
3884 if not (p_apply_results_flag in('N','Y')) then
3885 per_cagr_utility_pkg.log_and_raise_error('HR_289418_CAGR_INV_DFLAG'
3886 ,p_cagr_request_id);
3887 elsif not (p_commit_flag in ('N','Y')) then
3888 per_cagr_utility_pkg.log_and_raise_error('HR_289419_CAGR_INV_CFLAG'
3889 ,p_cagr_request_id);
3890 end if;
3891
3892 if (p_assignment_id is not null and not(p_operation_mode in ('SE','SA'))) or
3893 (p_assignment_set_id is not null) or
3894 (p_payroll_id is not null) or
3895 (p_person_id is not null) or
3896 (p_entitlement_item_id is not null and not(p_operation_mode in ('BE','SE'))) or
3897 (p_collective_agreement_id is not null and not(p_operation_mode in ('SC','BE'))) or
3898 (p_apply_results_flag <> 'N' and p_operation_mode in ('SE','BE')) or
3899 (p_collective_agreement_set_id is not null)
3900 then
3901 per_cagr_utility_pkg.log_and_raise_error('HR_289708_UNEXPECTED_PARAM',p_cagr_request_id);
3902 end if;
3903
3904
3905 --
3906 -- populate the parameter record structure
3907 --
3908 p_params.effective_date := trunc(p_process_date);
3909 p_params.operation_mode := p_operation_mode;
3910 p_params.business_group_id := p_business_group_id;
3911 p_params.assignment_id := p_assignment_id;
3912 p_params.assignment_set_id := p_assignment_set_id;
3913 p_params.collective_agreement_id := p_collective_agreement_id;
3914 p_params.cagr_set_id := p_collective_agreement_set_id;
3915 p_params.cagr_request_id := p_cagr_request_id;
3916 p_params.payroll_id := p_payroll_id;
3917 p_params.person_id := p_person_id;
3918 p_params.entitlement_item_id := p_entitlement_item_id;
3919
3920 p_params.commit_flag := p_commit_flag;
3921 p_params.denormalise_flag := p_apply_results_flag;
3922
3923
3924 --
3925 -- Output parameter values to log
3926 --
3927 per_cagr_utility_pkg.put_log(g_head_separator,1);
3928 per_cagr_utility_pkg.put_log(' ',1);
3929 per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
3930 per_cagr_utility_pkg.put_log(' ',1);
3931 if p_params.operation_mode = 'SA' then
3932 per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3933 elsif p_params.operation_mode = 'SE' then
3934 per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
3935 elsif p_params.operation_mode = 'BE' then
3936 per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
3937 elsif p_params.operation_mode = 'SC' then
3938 per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
3939 end if;
3940 per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3941 per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942 per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943 per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944 per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945 per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946 per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947 per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948 per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3949 per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3950 if p_params.denormalise_flag = 'Y' then
3951 per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
3952 elsif p_params.denormalise_flag = 'N' then
3953 per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
3954 end if;
3955 if p_params.commit_flag = 'Y' then
3956 per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
3957 elsif p_params.commit_flag = 'N' then
3958 per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
3959 end if;
3960 per_cagr_utility_pkg.put_log(' ',1);
3961
3962 -- ****** This needs to be converted to a parameter passed to create_request,
3963 -- rather than relying on a public package variable *******
3964
3965 if fnd_global.conc_request_id <> -1 then
3966 per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
3967 else
3968 per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
3969 end if;
3970 per_cagr_utility_pkg.put_log(' ');
3971 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3972
3973 --
3974 -- invoke evaluation processing;
3975 --
3976 per_cagr_evaluation_pkg.evaluation_process(p_params => p_params
3977 ,p_SE_rec => g_output_structure);
3978
3979 --
3980 -- populate eligible results to HRMS dependent upon mode and param
3981 --
3982 if p_params.operation_mode in ('SA','SC') and p_params.denormalise_flag = 'Y' then
3983 per_cagr_apply_results_pkg.initialise(p_params);
3984 end if;
3985
3986 --
3987 -- Commit, if required.
3988 --
3989 if p_params.commit_flag = 'Y' then
3990 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3991 commit;
3992 elsif p_params.commit_flag = 'N' then
3993 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3994 rollback;
3995 end if;
3996
3997 -- complete logging
3998 per_cagr_utility_pkg.put_log(g_separator,1);
3999 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4000
4001 hr_utility.set_location('Leaving:'||l_proc, 50);
4002
4003 END initialise;
4004
4005
4006 -- ================================================================================================
4007 -- == **************** new_entitlement ***************** ==
4008 -- ================================================================================================
4009
4010 FUNCTION new_entitlement (p_ent_id IN NUMBER) RETURN VARCHAR2 IS
4011
4012 -- Accept cagr_entitlement_item_id. Loop through global pl/sql table containing the entitlement_item_ids
4013 -- processed by the main block so far. Returns TRUE if the entitlement exists otherwise FALSE.
4014 -- This routine is called by add_other_ret_rights, to ensure that retained rights for
4015 -- entitlement_items that have already been processed are not duplicated
4016
4017 l_found BOOLEAN := FALSE;
4018
4019 BEGIN
4020
4021 IF g_entitlement_items.count <> 0 then
4022 FOR i IN g_entitlement_items.first..g_entitlement_items.last LOOP
4023 IF g_entitlement_items(i) = p_ent_id THEN
4024 l_found := TRUE;
4025 EXIT;
4026 END IF;
4027 END LOOP;
4028 END IF;
4029
4030 IF l_found THEN
4031 Return 'Y';
4032 ELSE
4033 Return 'N';
4034 END IF;
4035
4036 END new_entitlement;
4037
4038
4039
4040 END per_cagr_evaluation_pkg;
[Home] [Help]