1 PACKAGE BODY per_cagr_evaluation_pkg AS
2 /* $Header: pecgrevl.pkb 120.2.12020000.4 2012/12/05 20:39:13 srannama 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 -- Bug 12621959
1002 l_element_entry_id pay_element_entries_f.element_entry_id%type;
1003 l_effective_start_date date;
1004 l_effective_end_date date;
1005 l_delete_warning boolean;
1006 l_ovn number;
1007
1008
1009 -- Bug 14096456
1010 CURSOR csr_invalid_element_entries(p_assignment_id NUMBER, p_element_type_id NUMBER) IS
1011 SELECT ee.element_entry_id,ee.object_version_number
1012 FROM pay_element_entries_f ee,per_cagr_entitlement_results cer
1013 WHERE ee.assignment_id = p_assignment_id
1014 AND ee.assignment_id = cer.assignment_id
1015 AND ee.element_type_id = cer.element_type_id
1016 AND ee.effective_start_date = p_params.effective_date
1017 AND ee.element_type_id = p_element_type_id;
1018
1019 -- update all results for any item found in cache
1020 -- taking exclusive lock out, with nowait, will hang until rows is freed,
1021 -- but this is NOT used by 'SE' mode
1022 CURSOR csr_all_results (v_assignment_id in number) IS
1023 SELECT er.start_date, er.cagr_request_id, er.element_type_id
1024 FROM per_cagr_entitlement_results er
1025 WHERE er.assignment_id = v_assignment_id
1026 AND p_params.effective_date BETWEEN er.START_DATE AND nvl(er.END_DATE,hr_general.END_OF_TIME)
1027 ORDER BY er.cagr_request_id
1028 FOR UPDATE OF END_DATE NOWAIT;
1029
1030 -- update all results for a specific item found in cache
1031 -- taking exclusive lock out - used by SE mode only, so uses param and nowait option
1032 CURSOR csr_item_results IS
1033 SELECT er.start_date, er.cagr_request_id
1034 FROM per_cagr_entitlement_results er
1035 WHERE er.assignment_id = p_params.assignment_id
1036 AND er.cagr_entitlement_item_id = p_params.entitlement_item_id
1037 AND p_params.effective_date BETWEEN er.START_DATE AND nvl(er.END_DATE,hr_general.END_OF_TIME)
1038 FOR UPDATE OF END_DATE NOWAIT;
1039
1040 -- test if any future result(s) exists for the item and asg
1041 -- and return the start_date of earliest future result set
1042 CURSOR csr_future_results (v_entitlement_item_id in NUMBER
1043 ,v_assignment_id in NUMBER) IS
1044 SELECT min(er.start_date)
1045 FROM per_cagr_entitlement_results er
1046 WHERE er.cagr_entitlement_item_id = v_entitlement_item_id
1047 AND er.assignment_id = v_assignment_id
1048 AND p_params.effective_date < er.START_DATE;
1049
1050 TYPE request_table IS TABLE OF per_cagr_entitlement_results.cagr_request_id%TYPE INDEX BY BINARY_INTEGER;
1051
1052 e_resource_busy exception;
1053 pragma exception_init(e_resource_busy,-00054);
1054
1055 l_proc constant VARCHAR2(61) := g_pkg || '.update_result_set';
1056 v_future_start_date DATE := NULL;
1057 v_end_date DATE := NULL;
1058 v_start_date DATE := NULL;
1059 v_element_type_id NUMBER(15); -- Bug 14096456
1060 v_assignment_id NUMBER(15);
1061 v_cagr_request_id NUMBER(15);
1062 v_delete_cagr_request_id NUMBER(15);
1063 i NUMBER(11) := 0;
1064 t_cagr_request request_table;
1065
1066 BEGIN
1067 hr_utility.set_location('Entering:'||l_proc, 10);
1068 per_cagr_utility_pkg.put_log(' Preparing results cache',2);
1069
1070 if p_switch = 'W' then
1071 -- we are 'updating' results for a specific item:
1072 -- previous results have been cleaned by csr_all_results, below (except SE mode which we do here)
1073 -- insert a new set of lines having start_date = effective_date, and end_date = EOT or
1074 -- future updates.start_date -1 if future updates exist
1075
1076 if p_params.operation_mode in ('SE','BE') then
1077 -- first tidy up existing records for a specific item for 'SE' or 'BE' mode
1078 -- as these modes do not automatically clean all results at startup, unlike 'SA' or 'SC'
1079
1080 open csr_item_results;
1081 fetch csr_item_results into v_start_date, v_delete_cagr_request_id;
1082 if v_start_date < p_params.effective_date then
1083 -- end date the record, and all others
1084 update per_cagr_entitlement_results set end_date = p_params.effective_date -1
1085 where current of csr_item_results;
1086 loop
1087 fetch csr_item_results into v_start_date, v_cagr_request_id;
1088 exit when csr_item_results%notfound;
1089 update per_cagr_entitlement_results set end_date = p_params.effective_date -1
1090 where current of csr_item_results;
1091 end loop;
1092 elsif v_start_date = p_params.effective_date then
1093 -- delete records which started today
1094 delete from per_cagr_entitlement_results
1095 where current of csr_item_results;
1096 loop
1097 fetch csr_item_results into v_start_date, v_cagr_request_id;
1098 exit when csr_item_results%notfound;
1099
1100 delete from per_cagr_entitlement_results
1101 where current of csr_item_results;
1102 end loop;
1103 -- as we have deleted an entitlement result, also delete any
1104 -- log entries, for the results request_id.
1105 -- (we use the first fetched request_id, as it doesn't change).
1106 per_cagr_utility_pkg.remove_log_entries(v_delete_cagr_request_id);
1107 end if;
1108 close csr_item_results;
1109 end if;
1110
1111 open csr_future_results(p_structure(1).cagr_entitlement_item_id,
1112 p_structure(1).assignment_id);
1113 fetch csr_future_results into v_future_start_date;
1114 close csr_future_results;
1115 if v_future_start_date is not null then
1116 v_end_date := v_future_start_date -1;
1117 end if;
1118
1119 per_cagr_utility_pkg.put_log(' New results have Start Date: '||p_params.effective_date||', End Date: '||v_end_date,2);
1120 -- now insert new set of results for the item
1121 write_results(p_structure,p_params.cagr_request_id,p_params.effective_date,v_end_date);
1122
1123 elsif p_switch = 'C' then
1124 -- clean results cache for all items for the asg, when starting processing for a cagr or when cagr removed from asg:
1125 -- delete any existing result records that started on effective_date, and call remove_log_entries
1126 -- end date any result records that started before effective_date (not used by SE mode)
1127
1128 if p_params.operation_mode in ('SA','SC') then
1129 -- use asg id param, as this is only asg processed in this mode
1130 v_assignment_id := p_params.assignment_id;
1131 elsif p_params.operation_mode = 'BA' then
1132 -- i.e. take the asg_id for the current item set
1133 -- which may change as we process different assignments
1134 v_assignment_id := p_structure(1).assignment_id;
1135 end if;
1136 per_cagr_utility_pkg.put_log(' Cleaning previous cache results found for asg id: '||v_assignment_id,2);
1137
1138 open csr_all_results(v_assignment_id);
1139 loop
1140 fetch csr_all_results into v_start_date, v_cagr_request_id, v_element_type_id;
1141 exit when csr_all_results%notfound;
1142 if v_start_date < p_params.effective_date then
1143 -- end date the record, and all others
1144 update per_cagr_entitlement_results set end_date = p_params.effective_date -1
1145 where current of csr_all_results;
1146 elsif v_start_date = p_params.effective_date then
1147
1148 -- Bug 12621959
1149 open csr_invalid_element_entries(v_assignment_id, v_element_type_id);
1150 fetch csr_invalid_element_entries into l_element_entry_id,l_ovn;
1151 if csr_invalid_element_entries%found then
1152 pay_element_entry_api.delete_element_entry
1153 ( p_datetrack_delete_mode => 'ZAP'
1154 ,p_effective_date => p_params.effective_date
1155 ,p_element_entry_id => l_element_entry_id
1156 ,p_object_version_number => l_ovn
1157 ,p_effective_start_date => l_effective_start_date
1158 ,p_effective_end_date => l_effective_end_date
1159 ,p_delete_warning => l_delete_warning);
1160 end if;
1161 close csr_invalid_element_entries;
1162
1163 -- delete records which started today, and store the request_id
1164 delete from per_cagr_entitlement_results
1165 where current of csr_all_results;
1166 if v_delete_cagr_request_id is null then
1167 v_delete_cagr_request_id := v_cagr_request_id;
1168 i := i + 1;
1169 t_cagr_request(i) := v_delete_cagr_request_id;
1170 elsif v_delete_cagr_request_id <> v_cagr_request_id then
1171 v_delete_cagr_request_id := v_cagr_request_id;
1172 i := i + 1;
1173 t_cagr_request(i) := v_delete_cagr_request_id;
1174 end if;
1175 end if;
1176 end loop;
1177 close csr_all_results;
1178
1179 if t_cagr_request.count > 0 then
1180 for j in 1 .. t_cagr_request.last loop
1181 -- as we have deleted an entitlement result, also delete any log entries, for the result's request_id.
1182 per_cagr_utility_pkg.remove_log_entries(t_cagr_request(j));
1183 end loop;
1184 end if;
1185
1186 end if;
1187 per_cagr_utility_pkg.put_log(' Completed preparing results cache.',2);
1188
1189 hr_utility.set_location('Leaving:'||l_proc, 50);
1190
1191 EXCEPTION
1192 WHEN e_resource_busy THEN
1193 -- raise resource busy message.
1194 per_cagr_utility_pkg.put_log(' ERROR: Another user is updating the entitlement results for the assignment.',1);
1195 per_cagr_utility_pkg.put_log(' Unable to lock result records exclusively. Please try again later.',1);
1196 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1197 fnd_message.set_token('TABLE_NAME', 'per_cagr_entitlement_results');
1198 fnd_message.raise_error;
1199
1200 END update_result_set;
1201
1202
1203 -- ================================================================================================
1204 -- == **************** PROCESS_ENTITLEMENT_LINES ***************** ==
1205 -- ================================================================================================
1206 PROCEDURE process_entitlement_lines (p_pl_id IN NUMBER
1207 ,p_opt_id IN NUMBER
1208 ,p_person_id IN NUMBER
1209 ,p_benefit_action_id OUT NOCOPY NUMBER
1210 ,p_effective_date IN DATE
1211 ,p_bg_id IN NUMBER) IS
1212 -- Either:
1213 -- Accept a person_id, collective_agreement_id (plan_id) and invoke benmngle
1214 -- to evaluate all eligibility profiles for the single the entitlement_line (option).
1215 -- or
1216 -- Accept a person_id, collective_agreement_id (plan_id) and entitlement_line_id (option) and
1217 -- invoke benmngle to evaluate all eligibility profiles for the single the entitlement_line (option).
1218 -- (Assuming its faster to evaluate specific ent lines (options) individually for one entitlement,
1219 -- during Single Entitlement mode, when we do not need all lines for all entitlements to be processed.)
1220
1221 --
1222 -- p_benefit_action_id is set upon successful completion.
1223 --
1224
1225 -- Note: person_id restricts benmngle to eval the options eligibility for the current person only,
1226 -- else all eligibilities for current plan, for all people, will be evaluated if person_id is null.
1227
1228 l_proc constant VARCHAR2(61) := g_pkg || '.' || 'process_entitlement_lines';
1229
1230 l_errbuf varchar2(80);
1231 l_retcode number;
1232 l_validate_flag ben_benefit_actions.validate_flag%TYPE := 'Y';
1233 l_derivable_factors_flag ben_benefit_actions.derivable_factors_flag%TYPE := 'ASC';
1234 l_mode ben_benefit_actions.mode_cd%TYPE := 'A';
1235 l_benefit_action_id ben_benefit_actions.benefit_action_id%TYPE := NULL;
1236
1237 l_ben_count NUMBER;
1238
1239 pragma autonomous_transaction;
1240
1241 BEGIN
1242 hr_utility.set_location('Entering:'||l_proc, 10);
1243
1244 per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1245 per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1246 per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247 per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248 per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249 per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250 per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251 per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1252 per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1253 per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1254
1255 ben_manage_life_events.internal_process
1256 (errbuf => l_errbuf,
1257 retcode => l_retcode,
1258 p_benefit_action_id => l_benefit_action_id,
1259 p_effective_date => fnd_date.date_to_canonical(p_effective_date),
1260 p_mode => l_mode,
1261 p_derivable_factors => l_derivable_factors_flag,
1262 p_validate => l_validate_flag,
1263 p_person_id => p_person_id,
1264 p_business_group_id => p_bg_id,
1265 p_pl_id => p_pl_id,
1266 p_opt_id => p_opt_id,
1267 p_cagr_id => p_params.collective_agreement_id, -- Bug # 5391298
1268 p_commit_data => 'Y',
1269 p_audit_log_flag => 'Y');
1270
1271 Commit;
1272
1273 per_cagr_utility_pkg.put_log('Completed benmngle at: '||
1274 fnd_date.date_to_canonical(sysdate)||' return code is :'||to_char(l_retcode));
1275 per_cagr_utility_pkg.put_log('benefit_action_id: '|| to_char(l_benefit_action_id));
1276 p_benefit_action_id := l_benefit_action_id;
1277
1278 hr_utility.set_location('Leaving:'||l_proc, 30);
1279
1280 EXCEPTION
1281 when others then
1282 Rollback;
1283 hr_utility.set_location('Fatal Error: '||l_proc, 20);
1284 per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1285 per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1286 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1287 raise;
1288
1289 END process_entitlement_lines;
1290
1291 -- ================================================================================================
1292 -- == **************** GET_BEN_ELIGIBILITY_INFO ***************** ==
1293 -- ================================================================================================
1294 PROCEDURE get_BEN_eligibility_info (p_benefit_action_id IN NUMBER
1295 ,p_eligibility_table OUT NOCOPY eligibility_table
1296 ,p_counter OUT NOCOPY NUMBER) IS
1297
1298 -- Retrieve the processed eligibility data from BEN table, and translate it into
1299 -- structure of type eligibility_table for use in CAGR engine.
1300 -- rec structure is: BEN_ACTION_ID | PERSON_ID | PGM_ID | PL_ID | OIPL_ID | ELIG_FLAG
1301 -- (Called immediately after ben completes).
1302
1303 CURSOR csr_get_elig_ent_lines is
1304 SELECT batch_elig_id,
1305 BENEFIT_ACTION_ID,
1306 PERSON_ID,
1307 PGM_ID,
1308 PL_ID,
1309 OIPL_ID,
1310 ELIG_FLAG
1311 from ben_batch_elig_info bbe
1312 where bbe.BENEFIT_ACTION_ID = p_benefit_action_id
1313 and bbe.OIPL_ID is not null;
1314
1315 l_proc constant VARCHAR2(61) := g_pkg || '.' || 'get_BEN_eligibility_info';
1316
1317 BEGIN
1318 hr_utility.set_location('Entering:'||l_proc, 10);
1319 p_counter := 0;
1320 if p_benefit_action_id is not null then
1321 -- create the structure which will be used by main code, to show eligibility result for
1322 -- an entitlement line...
1323 for v_elig_lines in csr_get_elig_ent_lines loop
1324 p_counter := p_counter +1;
1325 p_eligibility_table(p_counter).BATCH_ELIG_ID := v_elig_lines.BATCH_ELIG_ID;
1326 p_eligibility_table(p_counter).BENEFIT_ACTION_ID := v_elig_lines.BENEFIT_ACTION_ID;
1327 p_eligibility_table(p_counter).PERSON_ID := v_elig_lines.PERSON_ID;
1328 p_eligibility_table(p_counter).PGM_ID := v_elig_lines.PGM_ID;
1329 p_eligibility_table(p_counter).PL_ID := v_elig_lines.PL_ID;
1330 p_eligibility_table(p_counter).OIPL_ID := v_elig_lines.OIPL_ID;
1331 p_eligibility_table(p_counter).ELIG_FLAG := v_elig_lines.ELIG_FLAG;
1332 end loop;
1333 end if;
1334 per_cagr_utility_pkg.put_log('Benmngle created '||to_char(p_counter)||' positive eligibility records');
1335
1336 hr_utility.set_location('Leaving:'||l_proc, 30);
1337
1338 END get_BEN_eligibility_info;
1339
1340
1341 -- ================================================================================================
1342 -- == **************** check_entitlement_eligible ***************** ==
1343 -- ================================================================================================
1344 FUNCTION check_entitlement_eligible (p_person_id in NUMBER default NULL
1345 ,p_oipl_id in NUMBER
1346 ,p_eligibility_table in eligibility_table) RETURN BOOLEAN IS
1347
1348 -- Loop through the BEN eligibility pl/sql table to identify the eligibility result for a
1349 -- specific entitlement_line (option in plan) for a person.
1350 -- Returns: TRUE if eligible, otherwise false.
1351
1352 -- (Raise error if a result for p_oipl_id is not found in pl/sql table, as
1353 -- this may indicate possible data setup or benmngle problem).
1354
1355 v_found BOOLEAN := FALSE;
1356 l_proc constant VARCHAR2(61) := g_pkg || '.' || 'check_entitlement_eligible';
1357
1358 -- Bug 13703461
1359 l_person_id per_all_people_f.person_id%TYPE;
1360 CURSOR csr_check_elig IS
1361 SELECT person_id
1362 FROM ben_batch_elig_info bbe
1363 WHERE bbe.benefit_action_id = g_benefit_action_id
1364 AND oipl_id = p_oipl_id
1365 AND person_id = p_person_id
1366 AND elig_flag = 'Y'
1367 AND oipl_id IS NOT NULL;
1368
1369 l_oipl_id ben_batch_elig_info.oipl_id%TYPE;
1370 CURSOR csr_check_oipl IS
1371 SELECT oipl_id
1372 FROM ben_batch_elig_info bbe
1373 WHERE bbe.benefit_action_id = g_benefit_action_id
1374 AND oipl_id = p_oipl_id
1375 AND oipl_id IS NOT NULL;
1376
1377 BEGIN
1378 hr_utility.set_location('Entering:'||l_proc, 10);
1379
1380 if p_oipl_id is null then
1381 per_cagr_utility_pkg.put_log(' ERROR: Option in plan id is null for criteria line ',1);
1382 hr_utility.set_message(800, 'HR_289415_CAGR_OIPL_NULL');
1383 hr_utility.raise_error;
1384 end if;
1385
1386 if p_person_id is null then
1387 -- reading table for SE or SA mode
1388 for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1389 if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1390 v_found := TRUE;
1391 if p_eligibility_table(i).ELIG_FLAG = 'Y' then
1392 per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1393 return TRUE;
1394 end if;
1395 end if;
1396 end loop;
1397 else
1398 -- reading table for SC or BE mode, using person context
1399 -- Bug 13703461
1400 OPEN csr_check_elig;
1401 FETCH csr_check_elig INTO l_person_id;
1402 IF csr_check_elig%FOUND THEN
1403 CLOSE csr_check_elig;
1404 per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1405 return TRUE;
1406 END IF;
1407 CLOSE csr_check_elig;
1408 OPEN csr_check_oipl;
1409 FETCH csr_check_oipl INTO l_oipl_id;
1410 IF csr_check_oipl%FOUND THEN
1411 v_found := TRUE;
1412 ELSE
1413 v_found := FALSE;
1414 END IF;
1415 CLOSE csr_check_oipl;
1416
1417 /* for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1418 if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1419 v_found := TRUE;
1420 if p_eligibility_table(i).PERSON_ID = p_person_id and p_eligibility_table(i).ELIG_FLAG = 'Y' then
1421 per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1422 return TRUE;
1423 end if;
1424 end if;
1425 end loop; */
1426 end if;
1427
1428 if v_found = FALSE then
1429 per_cagr_utility_pkg.put_log(' ERROR: Option in plan id does not exist ',1);
1430 hr_utility.set_message(800, 'HR_289416_CAGR_OIPL_NOT_FOUND');
1431 hr_utility.raise_error;
1432 end if;
1433
1434 per_cagr_utility_pkg.put_log(' The criteria eligibility profile is not satisfied.',1);
1435 hr_utility.set_location('Leaving:'||l_proc, 50);
1436 return FALSE;
1437
1438 END check_entitlement_eligible;
1439
1440
1441 -- ================================================================================================
1442 -- == **************** SET_BENEFICIAL_VALUE ***************** ==
1443 -- ================================================================================================
1444
1445 PROCEDURE set_beneficial_value (p_effective_date in DATE
1446 ,p_results_table in out NOCOPY results_table
1447 ,p_ben_rule in VARCHAR2 -- hi/lo/accumulate
1448 ,p_ben_rule_vs_id in NUMBER
1449 ,p_ben_value out nocopy NUMBER -- return value
1450 ,p_ben_row out nocopy NUMBER -- index
1451 ,p_rule_inconclusive out nocopy BOOLEAN) IS
1452
1453 -- Accepts table of result records for an item, identifies (and sets) the most beneficial record
1454 -- based on allowed behaviour for the category of the item, and the item's hi-lo rule.
1455 -- Returns beneficial_value, beneficial_row, rule_inconclusive flag, and sets most beneficial record.
1456 -- Supports PAY, ASG, PYS, ABS data categories only. (PAY,ASG,ABS use value, PYS uses step_id).
1457 -- Value data may be numeric, varchar, date types.
1458 -- If p_beneficial_rule_vs_id is not null then beneficial rule is applied to the corresponding values
1459 -- returned by the data column for the result ids, not the result ids (values) themselves.
1460 -- If a data setup error occurs v_rule_inconclusive is set to TRUE.
1461
1462 TYPE dyn_rec IS RECORD (char_col varchar2(30)
1463 ,num_col number(15)
1464 ,date_col date);
1465
1466 TYPE dyn_rec_table IS TABLE OF dyn_rec INDEX BY BINARY_INTEGER;
1467 TYPE dyn_csr IS REF CURSOR; -- define cursor ref type
1468
1469 -- get value set data column type
1470 CURSOR csr_data (vs_id NUMBER) IS
1471 SELECT value_column_type
1472 FROM fnd_flex_validation_tables
1473 WHERE flex_value_set_id = vs_id;
1474
1475 l_dyn_csr dyn_csr;
1476 l_dyn_csr_table dyn_rec_table;
1477 l_proc constant VARCHAR2(80) := g_pkg || '.set_beneficial_value';
1478 l_category VARCHAR2(30);
1479 l_sql VARCHAR(2000) := NULL;
1480 l_ben_row NUMBER := NULL;
1481 l_sequence NUMBER := 0;
1482 l_id NUMBER := 0;
1483 l_num NUMBER := 0;
1484 l_list_str VARCHAR(2000) := NULL;
1485 l_ben_field VARCHAR2(30);
1486 l_char VARCHAR2(30);
1487 l_date DATE;
1488 l_error BOOLEAN := FALSE;
1489 l_col_data_type VARCHAR2(30);
1490
1491 --
1492 PROCEDURE do_date_beneficial (p_input_table in dyn_rec_table
1493 ,p_rule in varchar2
1494 ,p_row out nocopy number) IS
1495 l_proc constant VARCHAR2(80) := g_pkg || '.do_date_beneficial';
1496 l_ben_val date;
1497 BEGIN
1498 hr_utility.set_location('Entering:'||l_proc, 10);
1499 for i in p_input_table.first..p_input_table.last loop
1500 if i = 1 then
1501 l_ben_val := p_input_table(i).date_col;
1502 p_row := i;
1503 elsif i > 1 then
1504 if p_rule = 'HI' then
1505 if p_input_table(i).date_col > l_ben_val then
1506 l_ben_val := p_input_table(i).date_col;
1507 p_row := i;
1508 elsif p_input_table(i).date_col = l_ben_val then
1509 p_row := NULL;
1510 end if;
1511 elsif p_rule = 'LO' then
1512 if p_input_table(i).date_col < l_ben_val then
1513 l_ben_val := p_input_table(i).date_col;
1514 p_row := i;
1515 elsif p_input_table(i).date_col = l_ben_val then
1516 p_row := NULL;
1517 end if;
1518 end if;
1519 end if;
1520 end loop;
1521 hr_utility.set_location('Leaving:'||l_proc, 20);
1522 END do_date_beneficial;
1523 --
1524 PROCEDURE do_num_beneficial (p_input_table in dyn_rec_table
1525 ,p_rule in varchar2
1526 ,p_row out nocopy number) IS
1527 l_proc constant VARCHAR2(80) := g_pkg || '.do_num_beneficial';
1528 l_ben_val number;
1529 BEGIN
1530 hr_utility.set_location('Entering:'||l_proc, 10);
1531 for i in p_input_table.first..p_input_table.last loop
1532 if i = 1 then
1533 l_ben_val := p_input_table(i).num_col;
1534 p_row := i;
1535 elsif i > 1 then
1536 if p_rule = 'HI' then
1537 if p_input_table(i).num_col > l_ben_val then
1538 l_ben_val := p_input_table(i).num_col;
1539 p_row := i;
1540 elsif p_input_table(i).num_col = l_ben_val then
1541 p_row := NULL;
1542 end if;
1543 elsif p_rule = 'LO' then
1544 if p_input_table(i).num_col < l_ben_val then
1545 l_ben_val := p_input_table(i).num_col;
1546 p_row := i;
1547 elsif p_input_table(i).num_col = l_ben_val then
1548 p_row := NULL;
1549 end if;
1550 end if;
1551 end if;
1552 end loop;
1553 hr_utility.set_location('Leaving:'||l_proc, 20);
1554 END do_num_beneficial;
1555 --
1556 PROCEDURE do_char_beneficial (p_input_table in dyn_rec_table
1557 ,p_rule in varchar2
1558 ,p_row out nocopy number) IS
1559 l_proc constant VARCHAR2(80) := g_pkg || '.do_char_beneficial';
1560 l_ben_val varchar2(30);
1561 BEGIN
1562 hr_utility.set_location('Entering:'||l_proc, 10);
1563 for i in p_input_table.first..p_input_table.last loop
1564 if i = 1 then
1565 l_ben_val := p_input_table(i).char_col;
1566 p_row := i;
1567 elsif i > 1 then
1568 if p_rule = 'HI' then
1569 if p_input_table(i).char_col > l_ben_val then
1570 l_ben_val := p_input_table(i).char_col;
1571 p_row := i;
1572 elsif p_input_table(i).char_col = l_ben_val then
1573 p_row := NULL;
1574 end if;
1575 elsif p_rule = 'LO' then
1576 if p_input_table(i).char_col < l_ben_val then
1577 l_ben_val := p_input_table(i).char_col;
1578 p_row := i;
1579 elsif p_input_table(i).char_col = l_ben_val then
1580 p_row := NULL;
1581 end if;
1582 end if;
1583 end if;
1584 end loop;
1585 hr_utility.set_location('Leaving:'||l_proc, 20);
1586 END do_char_beneficial;
1587
1588 BEGIN
1589
1590 hr_utility.set_location('Entering:'||l_proc, 10);
1591 l_category := p_results_table(1).category_name;
1592 if p_ben_rule is not null then
1593 per_cagr_utility_pkg.put_log(' Evaluating '||p_ben_rule||' beneficial rule on '||p_results_table.last||' results for this entitlement item',1);
1594 else
1595 per_cagr_utility_pkg.put_log(' No beneficial rule is defined for this entitlement item',1);
1596 end if;
1597
1598 -- first test to see whether we are processing value or step_id field, in result records
1599 if p_results_table(1).value is not null and p_results_table(1).step_id is null then
1600 l_ben_field := 'VALUE';
1601 elsif p_results_table(1).value is null and p_results_table(1).step_id is not null then
1602 l_ben_field := 'STEP_ID';
1603 else
1604 per_cagr_utility_pkg.put_log('Cannot determine either of value or step_id to process');
1605 p_rule_inconclusive := TRUE;
1606 goto end_of_procedure; -- don't raise an exception
1607 end if;
1608
1609 per_cagr_utility_pkg.put_log(' Beneficial field is: '||l_ben_field);
1610
1611 -- if only one record in the input table, default and skip processing
1612 if p_results_table.count = 1 then
1613 if l_ben_field = 'VALUE' then
1614 l_ben_row := 1;
1615 else
1616 l_ben_row := 1;
1617 end if;
1618 elsif p_ben_rule is not null then
1619 -- start processing the results as > 1 record in table
1620 -- and ben_rule is set for the item.
1621
1622 if p_ben_rule_vs_id is not null then
1623 per_cagr_utility_pkg.put_log(' Beneficial rule uses ValueSet id: '||p_ben_rule_vs_id);
1624 -- we are using a data column so build list of id's from results for sql
1625 if l_ben_field = 'VALUE' then
1626 for i in p_results_table.first .. p_results_table.last loop
1627 l_list_str := l_list_str || p_results_table(i).VALUE ||',';
1628 end loop;
1629 else -- l_ben_field = 'STEP_ID'
1630 for i in p_results_table.first .. p_results_table.last loop
1631 l_list_str := l_list_str || p_results_table(i).STEP_ID ||',';
1632 end loop;
1633 end if;
1634 l_list_str := substr(l_list_str,1,(length(l_list_str) -1));
1635 l_list_str := '('||l_list_str||')';
1636 -- get the sql to be used to get the data column from value set
1637 l_sql := per_cagr_utility_pkg.get_sql_from_vset_id(p_ben_rule_vs_id);
1638 if l_sql is null then
1639 per_cagr_utility_pkg.put_log(' Could not determine SQL for ValueSet id');
1640 p_rule_inconclusive := TRUE;
1641 else
1642 -- replace BG_ID, and insert list of ids into the sql statement
1643 l_sql := replace(l_sql,':$PROFILES$.PER_BUSINESS_GROUP_ID',p_params.business_group_id);
1644 l_sql := replace(l_sql,'()',l_list_str);
1645 per_cagr_utility_pkg.put_log(l_sql);
1646
1647 -- determine datatype of value set data column
1648 open csr_data(p_ben_rule_vs_id);
1649 fetch csr_data into l_col_data_type;
1650 if csr_data%notfound then
1651 close csr_data;
1652 per_cagr_utility_pkg.put_log(' ValueSet column type not found');
1653 p_rule_inconclusive := TRUE;
1654 goto end_of_procedure;
1655 end if;
1656 per_cagr_utility_pkg.put_log(' Datatype of ValueSet data column is: '||l_col_data_type);
1657
1658 -- dynamic sql to get the value set data column values for list of id's
1659 -- and call relevant ben function for the datatype
1660 open l_dyn_csr for l_sql;
1661 if l_col_data_type = 'V' then
1662 loop
1663 fetch l_dyn_csr into l_id, l_char;
1664 exit when l_dyn_csr%notfound;
1665 l_dyn_csr_table(l_dyn_csr%rowcount).char_col := l_char;
1666 end loop;
1667 close l_dyn_csr;
1668 do_char_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1669 elsif l_col_data_type = 'N' then
1670 loop
1671 fetch l_dyn_csr into l_id, l_num;
1672 exit when l_dyn_csr%notfound;
1673 l_dyn_csr_table(l_dyn_csr%rowcount).num_col := l_num;
1674 end loop;
1675 close l_dyn_csr;
1676 do_num_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1677 elsif l_col_data_type = 'D' then
1678 loop
1679 fetch l_dyn_csr into l_id, l_date;
1680 exit when l_dyn_csr%notfound;
1681 l_dyn_csr_table(l_dyn_csr%rowcount).date_col := l_date;
1682 end loop;
1683 close l_dyn_csr;
1684 do_date_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1685 end if;
1686 end if;
1687
1688 else -- do regular processing on actual cagr data, using value only, and the
1689 -- value which is not treated as an ID (user must set up value set for ids
1690 -- within value or step_id).
1691 per_cagr_utility_pkg.put_log(' Ben rule uses cagr value column');
1692 per_cagr_utility_pkg.put_log(' Datatype of cagr column is: '||p_results_table(1).column_type);
1693
1694 BEGIN
1695 if p_results_table(1).column_type = 'VAR' then
1696 for i in p_results_table.first..p_results_table.last loop
1697 l_dyn_csr_table(i).char_col := p_results_table(i).value;
1698 end loop;
1699 do_char_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1700 elsif p_results_table(1).column_type = 'NUM' then
1701 for i in p_results_table.first..p_results_table.last loop
1702 l_dyn_csr_table(i).num_col := to_number(p_results_table(i).value);
1703 end loop;
1704 do_num_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1705 elsif p_results_table(1).column_type = 'DATE' then
1706 for i in p_results_table.first..p_results_table.last loop
1707 l_dyn_csr_table(i).date_col := trunc(fnd_date.canonical_to_date(p_results_table(i).value));
1708 end loop;
1709 do_date_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1710 end if;
1711 EXCEPTION
1712 WHEN OTHERS THEN -- trap any value conversion exceptions
1713 per_cagr_utility_pkg.put_log(' Beneficial Rule evaluation error',1);
1714 per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1715 p_rule_inconclusive := TRUE;
1716 goto end_of_procedure;
1717 END;
1718 end if;
1719 l_dyn_csr_table.delete; -- clear pl/sql table
1720 end if;
1721
1722 if l_ben_row is not null then
1723 -- mark the beneficial record and log the value
1724 p_results_table(l_ben_row).BENEFICIAL_FLAG := 'Y';
1725 p_ben_row := l_ben_row;
1726 if l_ben_field = 'VALUE' then
1727 per_cagr_utility_pkg.put_log(' Beneficial value is: '||p_results_table(l_ben_row).value,1);
1728 elsif l_ben_field = 'STEP_ID' then
1729 per_cagr_utility_pkg.put_log(' Beneficial step_id is: '||p_results_table(l_ben_row).step_id,1);
1730 end if;
1731 else
1732 if p_ben_rule is not null then
1733 p_rule_inconclusive := TRUE;
1734 end if;
1735 end if;
1736
1737 <<end_of_procedure>>
1738 hr_utility.set_location('Leaving:'||l_proc, 50);
1739 END set_beneficial_value;
1740
1741 -- ================================================================================================
1742 -- == **************** ADD_RELATED_RETAINED_RIGHTS ***************** ==
1743 -- ================================================================================================
1744
1745 PROCEDURE add_related_ret_rights (p_assignment_id IN NUMBER
1746 ,p_cagr_entitlement_item_id IN NUMBER
1747 ,p_effective_date IN DATE
1748 ,p_structure IN OUT NOCOPY results_table
1749 ,p_counter IN OUT NOCOPY NUMBER) IS
1750
1751 -- Accept a structure containing the current process set of entitlements for a dataitem
1752 -- and add in any retained entitlements that are eligible, for the dataitem
1753 -- (Retained rights records may be for item or line level and may or may not be frozen)
1754 -- Next apply the beneficial rule processing for the retained rights entitlements
1755 -- (same as for current entitlements processed in Main block)
1756 -- Thus return the completed set of entitlement records (and counter) for the current dataitem,
1757 -- with benficial row identified.
1758
1759 --
1760 -- Cursor to return retained rights for the current assignment and dataitem
1761 -- on the effective_date, checking the cagr, entitlement and line are still active
1762 -- and current on the effective_date.
1763 --
1764 CURSOR csr_cagr_retained_rights IS
1765 SELECT * from per_cagr_retained_rights pcrr
1766 WHERE pcrr.assignment_id = p_assignment_id
1767 AND cagr_entitlement_item_id = p_cagr_entitlement_item_id
1768 AND p_params.effective_date BETWEEN pcrr.START_DATE AND nvl(pcrr.END_DATE,hr_general.end_of_time)
1769 AND EXISTS (select 'x'
1770 from per_collective_agreements pca
1771 where pca.collective_agreement_id = pcrr.collective_agreement_id
1772 and pca.STATUS = 'A'
1773 and p_params.effective_date >= pca.START_DATE)
1774 AND EXISTS (select 'x'
1775 from per_cagr_entitlements pce
1776 where pce.cagr_entitlement_id = pcrr.cagr_entitlement_id
1777 and pce.STATUS = 'A'
1778 and p_params.effective_date between pce.START_DATE and nvl(pce.END_DATE,hr_general.end_of_time))
1779 AND ((pcrr.cagr_entitlement_line_id is not null
1780 and EXISTS (select 'x'
1781 from per_cagr_entitlement_lines_f pcel
1782 where pcel.cagr_entitlement_line_id = pcrr.cagr_entitlement_line_id
1783 and pcel.STATUS = 'A'
1784 and p_params.effective_date between pcel.effective_start_date
1785 and pcel.effective_end_date))
1786 OR pcrr.cagr_entitlement_line_id is null);
1787 --
1788 -- Cursor to return entitlement values (status and date are done checked driving cursor above)
1789 --
1790 CURSOR csr_cagr_ents (v_ent_id in NUMBER) IS
1791 SELECT *
1792 FROM per_cagr_entitlements pce
1793 WHERE pce.CAGR_ENTITLEMENT_ID = v_ent_id;
1794 --
1795 -- Cursor to return active entitlement line values on the effective_date.
1796 -- (status and date are done checked driving cursor above)
1797 -- used for un-frozen retained right ent lines
1798 --
1799 CURSOR csr_cagr_lines (v_line_id in NUMBER, v_cagr_id IN NUMBER) IS
1800 SELECT *
1801 FROM per_cagr_entitlement_lines_f pcel
1802 WHERE pcel.CAGR_ENTITLEMENT_LINE_ID = v_line_id
1803 AND p_effective_date BETWEEN pcel.EFFECTIVE_START_DATE
1804 AND nvl(pcel.EFFECTIVE_END_DATE,hr_general.end_of_time);
1805 --
1806 -- Cursor to return entitlement line values on the effective_date
1807 --
1808 v_csr_rr_rec csr_cagr_retained_rights%ROWTYPE;
1809 v_cagr_ents_rec csr_cagr_ents%ROWTYPE;
1810 v_cagr_lines_rec csr_cagr_lines%ROWTYPE;
1811 v_local_counter NUMBER(10);
1812 v_dataitem_id NUMBER(10);
1813 v_write_flag BOOLEAN := FALSE;
1814 v_value per_cagr_retained_rights.value%TYPE;
1815 v_range_from per_cagr_retained_rights.range_from%TYPE;
1816 v_range_to per_cagr_retained_rights.range_to%TYPE;
1817 v_units_of_measure per_cagr_retained_rights.units_of_measure%TYPE;
1818 v_grade_spine_id per_cagr_retained_rights.grade_spine_id%TYPE;
1819 v_parent_spine_id per_cagr_retained_rights.parent_spine_id%TYPE;
1820 v_step_id per_cagr_retained_rights.step_id%TYPE;
1821 v_from_step_id per_cagr_retained_rights.from_step_id%TYPE;
1822 v_to_step_id per_cagr_retained_rights.to_step_id%TYPE;
1823 l_cagr_FF_record hr_cagr_ff_pkg.cagr_FF_record;
1824 l_source_name varchar2(200) := NULL;
1825 v_counter NUMBER(15) := NULL;
1826 v_dup_record NUMBER(15) := null;
1827 l_proc constant VARCHAR2(80) := g_pkg || '.' || 'add_related_ret_rights';
1828
1829 BEGIN
1830
1831 hr_utility.set_location('Entering:'||l_proc, 10);
1832 per_cagr_utility_pkg.put_log(' Evaluating related Retained Rights for the item ',1);
1833 -- We are processing RR for a dataitem for an ASG
1834 open csr_cagr_retained_rights;
1835 LOOP
1836 fetch csr_cagr_retained_rights into v_csr_rr_rec;
1837 exit when csr_cagr_retained_rights%notfound;
1838
1839 if v_csr_rr_rec.OIPL_ID <> 0 and v_csr_rr_rec.eligy_prfl_id <> 0 then
1840 l_source_name := per_cagr_utility_pkg.get_elig_source(v_csr_rr_rec.eligy_prfl_id
1841 ,v_csr_rr_rec.formula_id
1842 ,p_params.effective_date);
1843 else
1844 l_source_name := '*** Default ***';
1845 end if;
1846
1847
1848 -- reset flag
1849 v_write_flag := FALSE;
1850 v_value := NULL; -- clear result variables before eval
1851 v_range_from := NULL;
1852 v_range_to := NULL;
1853 v_grade_spine_id := NULL;
1854 v_parent_spine_id := NULL;
1855 v_step_id := NULL;
1856 v_from_step_id := NULL;
1857 v_to_step_id := NULL;
1858
1859 if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
1860 per_cagr_utility_pkg.put_log(' found retained entitlement: '||l_source_name,1);
1861 v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
1862
1863 per_cagr_utility_pkg.put_log(' Retained Right is for entitlement: '|| v_csr_rr_rec.cagr_entitlement_id);
1864
1865 if v_csr_rr_rec.freeze_flag = 'N' then
1866 -- not frozen, so get the latest value from the latest formula_id
1867 -- for the retained entitlement
1868 per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1869 open csr_cagr_ents(v_csr_rr_rec.CAGR_ENTITLEMENT_ID);
1870 fetch csr_cagr_ents into v_cagr_ents_rec;
1871 if csr_cagr_ents%found then
1872 v_units_of_measure := v_cagr_ents_rec.UNITS_OF_MEASURE;
1873
1874 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_cagr_ents_rec.FORMULA_ID
1875 ,p_effective_date => p_effective_date
1876 ,p_assignment_id => p_assignment_id
1877 ,p_category_name => v_csr_rr_rec.category_name
1878 ,p_out_rec => l_cagr_FF_record);
1879
1880 -- assign FF return values to local vars if set
1881 if v_csr_rr_rec.category_name in ('ASG','PAY','ABS') then
1882 if l_cagr_FF_record.value is not null then
1883 v_value := l_cagr_FF_record.value;
1884 v_range_from := l_cagr_FF_record.range_from;
1885 v_range_to := l_cagr_FF_record.range_to;
1886 v_write_flag := TRUE;
1887 else
1888 -- log message as the formula evaluated to null and continue with next entitlement record
1889 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
1890 v_write_flag := FALSE;
1891 end if;
1892 elsif v_csr_rr_rec.category_name = 'PYS' then
1893 if l_cagr_FF_record.grade_spine_id is not null
1894 and l_cagr_FF_record.parent_spine_id is not null
1895 and l_cagr_FF_record.step_id is not null then
1896 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
1897 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
1898 v_step_id := l_cagr_FF_record.step_id;
1899 v_from_step_id := l_cagr_FF_record.from_step_id;
1900 v_to_step_id := l_cagr_FF_record.to_step_id;
1901 v_write_flag := TRUE;
1902 else
1903 -- log error as the formula didn't evaluate and continue with next entitlement record
1904 per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
1905 v_write_flag := FALSE;
1906 end if;
1907 end if;
1908 end if;
1909 close csr_cagr_ents;
1910
1911 elsif v_csr_rr_rec.freeze_flag = 'Y' then
1912 -- frozen, so assign use the frozen value (instead of re-evaluating formula)
1913 -- and trigger this retained right entitlement result to be added to the process set.
1914 per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1915 v_value := v_csr_rr_rec.value;
1916 v_range_from := v_csr_rr_rec.range_from;
1917 v_range_to := v_csr_rr_rec.range_to;
1918 v_units_of_measure := v_csr_rr_rec.units_of_measure;
1919 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
1920 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
1921 v_step_id := v_csr_rr_rec.step_id;
1922 v_from_step_id := v_csr_rr_rec.from_step_id;
1923 v_to_step_id := v_csr_rr_rec.to_step_id;
1924 v_write_flag := TRUE;
1925 end if;
1926
1927 else -- ent line retained right
1928 per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1929 per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1930
1931 if v_csr_rr_rec.freeze_flag = 'N' then
1932 per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1933 -- not frozen, so get the latest values for the item line
1934 open csr_cagr_lines(v_csr_rr_rec.cagr_entitlement_line_id, v_csr_rr_rec.collective_agreement_id);
1935 fetch csr_cagr_lines into v_cagr_lines_rec;
1936 if csr_cagr_lines%found then
1937 v_value := v_cagr_lines_rec.value;
1938 v_range_from := v_cagr_lines_rec.range_from;
1939 v_range_to := v_cagr_lines_rec.range_to;
1940 v_units_of_measure := v_csr_rr_rec.units_of_measure; -- i.e. use ent item uom for line
1941 v_grade_spine_id := v_cagr_lines_rec.grade_spine_id;
1942 v_parent_spine_id := v_cagr_lines_rec.parent_spine_id;
1943 v_step_id := v_cagr_lines_rec.step_id;
1944 v_from_step_id := v_cagr_lines_rec.from_step_id;
1945 v_to_step_id := v_cagr_lines_rec.to_step_id;
1946 close csr_cagr_lines;
1947 v_write_flag := TRUE;
1948 else
1949 close csr_cagr_lines;
1950 end if;
1951 elsif v_csr_rr_rec.freeze_flag = 'Y' then
1952 per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1953 -- frozen, so use the values that was saved
1954 -- on the retained right start date.
1955 v_value := v_csr_rr_rec.value;
1956 v_range_from := v_csr_rr_rec.range_from;
1957 v_range_to := v_csr_rr_rec.range_to;
1958 v_units_of_measure := v_csr_rr_rec.units_of_measure;
1959 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
1960 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
1961 v_step_id := v_csr_rr_rec.step_id;
1962 v_from_step_id := v_csr_rr_rec.from_step_id;
1963 v_to_step_id := v_csr_rr_rec.to_step_id;
1964 v_write_flag := TRUE;
1965 end if;
1966 end if;
1967
1968 if v_write_flag then
1969
1970
1971 -- Prevent duplicate results from occurring where a an eligible entitlement / entitlement line
1972 -- exists and it has also been retained (but the values have not changed. In this scenario
1973 -- delete the new result and just produce the retained result for the item...
1974 -- * Add support for validation fields at a later date *
1975 v_counter := null;
1976 v_dup_record := null;
1977
1978 If p_counter > 0 then
1979 If v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is not null then -- entitlement line
1980
1981 If v_csr_rr_rec.CATEGORY_NAME in ('ASG','ABS','PAY') then
1982 For y in p_structure.first .. p_structure.last loop
1983 If (v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID = p_structure(y).CAGR_ENTITLEMENT_LINE_ID
1984 and p_structure(y).VALUE = v_value) then
1985 v_dup_record := y; -- found a duplicate to the retained right
1986 exit;
1987 End if;
1988 End Loop;
1989
1990 Elsif v_csr_rr_rec.CATEGORY_NAME = 'PYS' then
1991 For y in p_structure.first .. p_structure.last loop
1992 If (v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID = p_structure(y).CAGR_ENTITLEMENT_LINE_ID
1993 and p_structure(y).GRADE_SPINE_ID = v_grade_spine_id
1994 and p_structure(y).PARENT_SPINE_ID = v_parent_spine_id
1995 and p_structure(y).STEP_ID = v_step_id) then
1996 v_dup_record := y; -- found a duplicate to the retained right
1997 exit;
1998 End if;
1999 End Loop;
2000 End if;
2001
2002 Else -- entitlement only
2003
2004 If v_csr_rr_rec.CATEGORY_NAME in ('ASG','ABS','PAY') then
2005 For y in p_structure.first .. p_structure.last loop
2006 If (v_csr_rr_rec.CAGR_ENTITLEMENT_ID = p_structure(y).CAGR_ENTITLEMENT_ID
2007 and p_structure(y).VALUE = v_value) then
2008 v_dup_record := y; -- found a duplicate to the retained right
2009 exit;
2010 End if;
2011 End Loop;
2012
2013 Elsif v_csr_rr_rec.CATEGORY_NAME = 'PYS' then
2014 For y in p_structure.first .. p_structure.last loop
2015 If (v_csr_rr_rec.CAGR_ENTITLEMENT_ID = p_structure(y).CAGR_ENTITLEMENT_ID
2016 and p_structure(y).GRADE_SPINE_ID = v_grade_spine_id
2017 and p_structure(y).PARENT_SPINE_ID = v_parent_spine_id
2018 and p_structure(y).STEP_ID = v_step_id) then
2019 v_dup_record := y; -- found a duplicate to the retained right
2020 exit;
2021 End if;
2022 End Loop;
2023 End if;
2024
2025 End if;
2026 End if;
2027
2028
2029 If v_dup_record is not null then
2030 -- delete the duplicate result and put RR in its place.
2031 p_structure.delete(v_dup_record);
2032 v_counter := v_dup_record;
2033 per_cagr_utility_pkg.put_log(' Removed duplicate result for this retained right.');
2034 End If;
2035 If v_counter is null then
2036 p_counter := p_counter +1;
2037 v_counter := p_counter;
2038 End if;
2039 --
2040 -- Assign the retained right entitlement into the plsql table
2041 -- holding the current entitlements process set for the dataitem.
2042 --
2043 p_structure(v_counter).COLLECTIVE_AGREEMENT_ID := v_csr_rr_rec.COLLECTIVE_AGREEMENT_ID;
2044 p_structure(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
2045 p_structure(v_counter).ELEMENT_TYPE_ID := v_csr_rr_rec.ELEMENT_TYPE_ID;
2046 p_structure(v_counter).INPUT_VALUE_ID := v_csr_rr_rec.INPUT_VALUE_ID;
2047 p_structure(v_counter).CAGR_API_ID := v_csr_rr_rec.CAGR_API_ID;
2048 p_structure(v_counter).CAGR_API_PARAM_ID := v_csr_rr_rec.CAGR_API_PARAM_ID;
2049 p_structure(v_counter).CATEGORY_NAME := v_csr_rr_rec.CATEGORY_NAME;
2050 p_structure(v_counter).CAGR_ENTITLEMENT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ID;
2051 p_structure(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID;
2052 p_structure(v_counter).ASSIGNMENT_ID := v_csr_rr_rec.ASSIGNMENT_ID;
2053 p_structure(v_counter).OIPL_ID := v_csr_rr_rec.OIPL_ID;
2054 p_structure(v_counter).ELIGY_PRFL_ID := v_csr_rr_rec.ELIGY_PRFL_ID;
2055 p_structure(v_counter).FORMULA_ID := v_csr_rr_rec.FORMULA_ID;
2056 p_structure(v_counter).VALUE := v_value;
2057 p_structure(v_counter).RANGE_FROM := v_range_from;
2058 p_structure(v_counter).RANGE_TO := v_range_to;
2059 p_structure(v_counter).UNITS_OF_MEASURE := v_units_of_measure;
2060 p_structure(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
2061 p_structure(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
2062 p_structure(v_counter).STEP_ID := v_step_id;
2063 p_structure(v_counter).FROM_STEP_ID := v_from_step_id;
2064 p_structure(v_counter).TO_STEP_ID := v_to_step_id;
2065 p_structure(v_counter).COLUMN_TYPE := v_csr_rr_rec.COLUMN_TYPE;
2066 p_structure(v_counter).COLUMN_SIZE := v_csr_rr_rec.COLUMN_SIZE;
2067 p_structure(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_csr_rr_rec.MULTIPLE_ENTRIES_ALLOWED_FLAG;
2068 p_structure(v_counter).BUSINESS_GROUP_ID := v_csr_rr_rec.BUSINESS_GROUP_ID;
2069 p_structure(v_counter).FLEX_VALUE_SET_ID := v_csr_rr_rec.FLEX_VALUE_SET_ID;
2070 p_structure(v_counter).RETAINED_ENT_RESULT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_RESULT_ID;
2071 end if;
2072 END LOOP;
2073 close csr_cagr_retained_rights;
2074 per_cagr_utility_pkg.put_log(' Completed related Retained Rights.',1);
2075 hr_utility.set_location('Leaving:'||l_proc, 50);
2076
2077 END add_related_ret_rights;
2078
2079 -- ================================================================================================
2080 -- == **************** ADD_OTHER_RETAINED_RIGHTS ***************** ==
2081 -- ================================================================================================
2082
2083 PROCEDURE add_other_ret_rights (p_params IN control_structure) IS
2084
2085 -- Returns any retained rights that exist for an assignment that are not for dataitems for
2086 -- which entitlements have been returned by the cursor in the main block. This mode uses a
2087 -- temporary table populated by the main routine holding the distinct dataitem_ids that have
2088 -- been returned by the main cursor (and so have already been evaluated by the above mode)
2089 -- to ensure that they are not duplicated again.
2090
2091 -- Note: Retained rights that are not frozen may be negated by having the entitlementl or line or even
2092 -- a parent entitlement or collective agreement end-dated before the effective date, or by having
2093 -- the status of the entitlement or line or parent entitlement or collective agreement set to status
2094 -- of inactive or pending.
2095
2096 --
2097 -- Cursor to return retained rights that are for entitlement items other than
2098 -- those returned by the main block (held in temp table), that are for active
2099 -- cagr, ent and lines (possibly) on the effective date. (And assuming the asg is primary)
2100 --
2101 CURSOR csr_cagr_other_ret_rights IS
2102 SELECT *
2103 FROM per_cagr_retained_rights pcrr
2104 WHERE pcrr.assignment_id = p_params.assignment_id
2105 AND p_params.effective_date BETWEEN pcrr.START_DATE
2106 AND nvl(pcrr.END_DATE,hr_general.end_of_time)
2107 AND EXISTS (select 'X' from per_all_assignments_f asg
2108 where asg.assignment_id = p_params.assignment_id
2109 and p_params.effective_date BETWEEN asg.effective_start_date
2110 AND asg.effective_end_date
2111 and asg.PRIMARY_FLAG = 'Y')
2112 AND EXISTS (select 'x'
2113 from per_collective_agreements pca
2114 where pca.collective_agreement_id = pcrr.collective_agreement_id
2115 and pca.STATUS = 'A'
2116 and p_params.effective_date >= pca.START_DATE)
2117 AND EXISTS (select 'x'
2118 from per_cagr_entitlements pce
2119 where pce.cagr_entitlement_id = pcrr.cagr_entitlement_id
2120 and pce.STATUS = 'A'
2121 and p_params.effective_date BETWEEN pce.START_DATE
2122 AND nvl(pce.END_DATE,hr_general.end_of_time))
2123 AND ((pcrr.cagr_entitlement_line_id is not null
2124 and EXISTS (select 'x'
2125 from per_cagr_entitlement_lines_f pcel
2126 where pcel.cagr_entitlement_line_id = pcrr.cagr_entitlement_line_id
2127 and pcel.STATUS = 'A'
2128 and p_params.effective_date between pcel.effective_start_date
2129 and pcel.effective_end_date))
2130 OR pcrr.cagr_entitlement_line_id is null)
2131 AND 'N' = per_cagr_evaluation_pkg.new_entitlement(pcrr.cagr_entitlement_item_id)
2132 ORDER BY pcrr.cagr_entitlement_item_id;
2133 --
2134 -- Cursor to return entitlement values (driving cursor checks date and status)
2135 --
2136 CURSOR csr_cagr_ents (v_ent_id in NUMBER) IS
2137 SELECT *
2138 FROM per_cagr_entitlements pce
2139 WHERE pce.CAGR_ENTITLEMENT_ID = v_ent_id
2140 AND p_params.effective_date BETWEEN pce.START_DATE
2141 AND nvl(pce.END_DATE,hr_general.end_of_time);
2142 --
2143 -- Cursor to return active entitlement line values on the effective_date
2144 --
2145 CURSOR csr_cagr_lines (v_line_id in NUMBER, v_cagr_id IN NUMBER) IS
2146 SELECT *
2147 FROM per_cagr_entitlement_lines_f pcel
2148 WHERE pcel.CAGR_ENTITLEMENT_LINE_ID = v_line_id
2149 AND p_params.effective_date BETWEEN pcel.EFFECTIVE_START_DATE
2150 AND nvl(pcel.EFFECTIVE_END_DATE,hr_general.end_of_time);
2151 --
2152 -- Cursor to get the beneficial rule info for specific entitlement_item
2153 --
2154 cursor csr_ben_rule (l_cagr_entitlement_item_id IN NUMBER) is
2155 SELECT beneficial_rule, beneficial_rule_value_set_id
2156 from per_cagr_entitlement_items pcei
2157 where pcei.cagr_entitlement_item_id = l_cagr_entitlement_item_id;
2158 --
2159 --
2160 --
2161 t_results_table results_table;
2162 l_outputs ff_exec.outputs_t;
2163 v_csr_rr_rec csr_cagr_other_ret_rights%ROWTYPE;
2164 v_cagr_ents_rec csr_cagr_ents%ROWTYPE;
2165 v_cagr_lines_rec csr_cagr_lines%ROWTYPE;
2166 v_ben_rule csr_ben_rule%ROWTYPE;
2167 v_counter NUMBER(10) := 0;
2168 v_beneficial_rule VARCHAR2(30);
2169 v_beneficial_value VARCHAR2(240);
2170 v_beneficial_rule_vs_id NUMBER(15);
2171 v_ben_row NUMBER(10);
2172 v_rule_inconclusive BOOLEAN := FALSE;
2173 v_dataitem_id NUMBER(10);
2174 v_write_flag BOOLEAN := FALSE;
2175 v_value per_cagr_retained_rights.VALUE%TYPE;
2176 v_range_from per_cagr_retained_rights.RANGE_FROM%TYPE;
2177 v_range_to per_cagr_retained_rights.RANGE_TO%TYPE;
2178 v_units_of_measure per_cagr_retained_rights.UNITS_OF_MEASURE%TYPE;
2179 v_grade_spine_id per_cagr_retained_rights.GRADE_SPINE_ID%TYPE;
2180 v_parent_spine_id per_cagr_retained_rights.PARENT_SPINE_ID%TYPE;
2181 v_step_id per_cagr_retained_rights.STEP_ID%TYPE;
2182 v_from_step_id per_cagr_retained_rights.FROM_STEP_ID%TYPE;
2183 v_to_step_id per_cagr_retained_rights.TO_STEP_ID%TYPE;
2184 l_cagr_FF_record hr_cagr_ff_pkg.cagr_FF_record;
2185
2186 l_proc constant VARCHAR2(80) := g_pkg || '.' || 'add_other_ret_rights';
2187
2188 BEGIN
2189
2190 hr_utility.set_location('Entering:'||l_proc, 10);
2191 per_cagr_utility_pkg.put_log(' Evaluating Retained Rights for other items',1);
2192 open csr_cagr_other_ret_rights;
2193 LOOP
2194 fetch csr_cagr_other_ret_rights into v_csr_rr_rec;
2195 exit when csr_cagr_other_ret_rights%notfound;
2196
2197 -- iterate through retained entitlements for each dataitem
2198 -- processing as if we were processing the main block
2199
2200 v_write_flag := FALSE;
2201 v_value := NULL; -- clear result variables before eval
2202 v_range_from := NULL;
2203 v_range_to := NULL;
2204 v_grade_spine_id := NULL;
2205 v_parent_spine_id := NULL;
2206 v_step_id := NULL;
2207 v_from_step_id := NULL;
2208 v_to_step_id := NULL;
2209
2210
2211 if v_last_dataitem_id is not null then
2212 if (v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID <> v_last_dataitem_id) then
2213 -- The dataitem that is being processed has changed so....
2214 -- (Note: this block gets invoked for dataitem rr entitlement set #2 thru to penultimate,
2215 -- where there are > 1 rr dataitem entitlement sets)
2216
2217 -- write any valid entitlement results for the previous dataitem,
2218 -- if beneficial rule has identified a preferred entitlement and clear the plsql table.
2219 if v_counter > 0 then
2220 -- determine and set most beneficial value for retained right results set
2221 set_beneficial_value(p_effective_date => p_params.effective_date
2222 ,p_results_table => t_results_table
2223 ,p_ben_rule => v_beneficial_rule
2224 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2225 ,p_ben_value => v_beneficial_value
2226 ,p_ben_row => v_ben_row
2227 ,p_rule_inconclusive => v_rule_inconclusive);
2228
2229 if v_rule_inconclusive then
2230 -- output warning message that beneficial could not be chosen
2231 -- and write results anyway, if profile option allows
2232 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2233 end if;
2234 update_result_set(t_results_table,p_params,'W');
2235 v_counter := 0;
2236 t_results_table.delete;
2237 end if;
2238 -- store new dataitem_id
2239 v_last_dataitem_id := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
2240 -- store new beneficial_rule
2241 open csr_ben_rule(v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID);
2242 fetch csr_ben_rule into v_ben_rule;
2243 if csr_ben_rule%found then
2244 close csr_ben_rule;
2245 v_beneficial_rule := v_ben_rule.beneficial_rule;
2246 v_beneficial_rule_vs_id := v_ben_rule.beneficial_rule_value_set_id;
2247 else
2248 close csr_ben_rule;
2249 end if;
2250 end if;
2251 else -- set the dataitem and beneficial rule on the first iteration
2252 v_last_dataitem_id := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
2253 open csr_ben_rule(v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID);
2254 fetch csr_ben_rule into v_ben_rule;
2255 if csr_ben_rule%found then
2256 close csr_ben_rule;
2257 v_beneficial_rule := v_ben_rule.beneficial_rule;
2258 v_beneficial_rule_vs_id := v_ben_rule.beneficial_rule_value_set_id;
2259 else
2260 close csr_ben_rule;
2261 end if;
2262 end if;
2263
2264
2265 -- determine whether current record is just entitlement item
2266 -- or entitlement line, and exec ff accordingly...
2267 if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
2268 v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
2269
2270 if v_csr_rr_rec.freeze_flag = 'N' then
2271 -- not frozen, so get the latest value of the formula_id, UOM for the entitlement
2272 open csr_cagr_ents(v_csr_rr_rec.CAGR_ENTITLEMENT_ID);
2273 fetch csr_cagr_ents into v_cagr_ents_rec;
2274 if csr_cagr_ents%found then
2275 v_units_of_measure := v_cagr_ents_rec.UNITS_OF_MEASURE;
2276
2277 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_cagr_ents_rec.FORMULA_ID
2278 ,p_effective_date => p_params.effective_date
2279 ,p_assignment_id => p_params.assignment_id
2280 ,p_category_name => v_csr_rr_rec.category_name
2281 ,p_out_rec => l_cagr_FF_record);
2282
2283 -- assign FF return values to local vars if set
2284 if v_csr_rr_rec.category_name in ('ASG','PAY','ABS') then
2285 if l_cagr_FF_record.value is not null then
2286 v_value := l_cagr_FF_record.value;
2287 v_range_from := l_cagr_FF_record.range_from;
2288 v_range_to := l_cagr_FF_record.range_to;
2289 v_write_flag := TRUE;
2290 else
2291 -- log message as the formula evaluated to null and continue with next entitlement record
2292 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2293 v_write_flag := FALSE;
2294 end if;
2295 elsif v_csr_rr_rec.category_name = 'PYS' then
2296 if l_cagr_FF_record.grade_spine_id is not null
2297 and l_cagr_FF_record.parent_spine_id is not null
2298 and l_cagr_FF_record.step_id is not null then
2299 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
2300 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
2301 v_step_id := l_cagr_FF_record.step_id;
2302 v_from_step_id := l_cagr_FF_record.from_step_id;
2303 v_to_step_id := l_cagr_FF_record.to_step_id;
2304 v_write_flag := TRUE;
2305 else
2306 -- log error as the formula didn't evaluate and continue with next entitlement record
2307 per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
2308 v_write_flag := FALSE;
2309 end if;
2310 end if;
2311 end if;
2312
2313 elsif v_csr_rr_rec.freeze_flag = 'Y' then
2314 -- frozen, so assign use the frozen value (instead of re-evaluating formula)
2315 -- and trigger this retained right entitlement result to be added to the process set.
2316 v_value := v_csr_rr_rec.value;
2317 v_range_from := v_csr_rr_rec.range_from;
2318 v_range_to := v_csr_rr_rec.range_to;
2319 v_units_of_measure := v_csr_rr_rec.units_of_measure;
2320 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
2321 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
2322 v_step_id := v_csr_rr_rec.step_id;
2323 v_from_step_id := v_csr_rr_rec.from_step_id;
2324 v_to_step_id := v_csr_rr_rec.to_step_id;
2325 v_write_flag := TRUE;
2326 end if;
2327
2328 else -- ent line retained right
2329 if v_csr_rr_rec.freeze_flag is null then
2330 -- not frozen, so get the latest values for the item line
2331 -- (but we do not execute the criteria line formula)
2332 open csr_cagr_lines(v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID, v_csr_rr_rec.COLLECTIVE_AGREEMENT_ID);
2333 fetch csr_cagr_lines into v_cagr_lines_rec;
2334 if csr_cagr_lines%found then
2335 v_value := v_cagr_lines_rec.value;
2336 v_range_from := v_cagr_lines_rec.range_from;
2337 v_range_to := v_cagr_lines_rec.range_to;
2338 v_units_of_measure := v_csr_rr_rec.units_of_measure; -- i.e. use ent item uom for line
2339 v_grade_spine_id := v_cagr_lines_rec.grade_spine_id;
2340 v_parent_spine_id := v_cagr_lines_rec.parent_spine_id;
2341 v_step_id := v_cagr_lines_rec.step_id;
2342 v_from_step_id := v_cagr_lines_rec.from_step_id;
2343 v_to_step_id := v_cagr_lines_rec.to_step_id;
2344
2345 close csr_cagr_lines;
2346 v_write_flag := TRUE;
2347 else
2348 close csr_cagr_lines;
2349 end if;
2350 elsif v_csr_rr_rec.freeze_flag = 'Y' then
2351 -- frozen, so use the values that was saved
2352 -- on the retained right start date.
2353 v_value := v_csr_rr_rec.value;
2354 v_range_from := v_csr_rr_rec.range_from;
2355 v_range_to := v_csr_rr_rec.range_to;
2356 v_units_of_measure := v_csr_rr_rec.units_of_measure;
2357 v_grade_spine_id := v_csr_rr_rec.grade_spine_id;
2358 v_parent_spine_id := v_csr_rr_rec.parent_spine_id;
2359 v_step_id := v_csr_rr_rec.step_id;
2360 v_from_step_id := v_csr_rr_rec.from_step_id;
2361 v_to_step_id := v_csr_rr_rec.to_step_id;
2362
2363 v_write_flag := TRUE;
2364 end if;
2365 end if;
2366
2367 if v_write_flag then
2368 --
2369 -- Assign the retained right entitlement into the plsql table
2370 -- holding the current entitlements process set for the dataitem.
2371 --
2372 v_counter := v_counter + 1;
2373
2374 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_csr_rr_rec.COLLECTIVE_AGREEMENT_ID;
2375 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ITEM_ID;
2376 t_results_table(v_counter).ELEMENT_TYPE_ID := v_csr_rr_rec.ELEMENT_TYPE_ID;
2377 t_results_table(v_counter).INPUT_VALUE_ID := v_csr_rr_rec.INPUT_VALUE_ID;
2378 t_results_table(v_counter).CAGR_API_ID := v_csr_rr_rec.CAGR_API_ID;
2379 t_results_table(v_counter).CAGR_API_PARAM_ID := v_csr_rr_rec.CAGR_API_PARAM_ID;
2380 t_results_table(v_counter).CATEGORY_NAME := v_csr_rr_rec.CATEGORY_NAME;
2381 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_ID;
2382 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID;
2383 t_results_table(v_counter).ASSIGNMENT_ID := v_csr_rr_rec.ASSIGNMENT_ID;
2384 t_results_table(v_counter).OIPL_ID := v_csr_rr_rec.OIPL_ID;
2385 t_results_table(v_counter).ELIGY_PRFL_ID := v_csr_rr_rec.ELIGY_PRFL_ID;
2386 t_results_table(v_counter).FORMULA_ID := v_csr_rr_rec.FORMULA_ID;
2387 t_results_table(v_counter).VALUE := v_value;
2388 t_results_table(v_counter).RANGE_FROM := v_range_from;
2389 t_results_table(v_counter).RANGE_TO := v_range_to;
2390 t_results_table(v_counter).UNITS_OF_MEASURE := v_units_of_measure;
2391 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
2392 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
2393 t_results_table(v_counter).STEP_ID := v_step_id;
2394 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
2395 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
2396 t_results_table(v_counter).COLUMN_TYPE := v_csr_rr_rec.COLUMN_TYPE;
2397 t_results_table(v_counter).COLUMN_SIZE := v_csr_rr_rec.COLUMN_SIZE;
2398 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_csr_rr_rec.MULTIPLE_ENTRIES_ALLOWED_FLAG;
2399 t_results_table(v_counter).BUSINESS_GROUP_ID := v_csr_rr_rec.BUSINESS_GROUP_ID;
2400 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_csr_rr_rec.FLEX_VALUE_SET_ID;
2401 t_results_table(v_counter).RETAINED_ENT_RESULT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_RESULT_ID;
2402 end if;
2403 END LOOP;
2404 close csr_cagr_other_ret_rights;
2405
2406
2407 if v_counter > 0 then
2408 -- determine and set most beneficial value for retained right results set
2409 set_beneficial_value(p_effective_date => p_params.effective_date
2410 ,p_results_table => t_results_table
2411 ,p_ben_rule => v_beneficial_rule
2412 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2413 ,p_ben_value => v_beneficial_value
2414 ,p_ben_row => v_ben_row
2415 ,p_rule_inconclusive => v_rule_inconclusive);
2416
2417 -- write any valid entitlement results for the first (if there was only 1 datatem)
2418 -- or last dataitem retrieved by the cursor,
2419 -- if beneficial rule has identified a preferred entitlement
2420 -- and clear the plsql table.
2421 if v_rule_inconclusive then
2422 -- output warning message that beneficial could not be chosen
2423 -- and write results anyway..
2424 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2425 end if;
2426 update_result_set(t_results_table,p_params,'W');
2427 t_results_table.delete;
2428 v_counter := 0;
2429 end if;
2430 per_cagr_utility_pkg.put_log(' Completed Retained Rights for other items.',1);
2431 hr_utility.set_location('Leaving:'||l_proc, 50);
2432
2433 END add_other_ret_rights;
2434
2435 -- ================================================================================================
2436 -- == **************** MAIN BLOCK ***************** ==
2437 -- ================================================================================================
2438
2439 BEGIN
2440
2441 hr_utility.set_location('Entering:'||l_proc, 5);
2442 per_cagr_utility_pkg.put_log(g_separator,1);
2443 per_cagr_utility_pkg.put_log('Starting Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
2444 --
2445 -- choose which cursor to open,
2446 -- depending upon operation mode
2447 --
2448 If p_params.operation_mode = 'SA' then
2449 --
2450 -- ****** Single Assignment mode *******
2451 --
2452 If p_params.commit_flag = 'Y' then
2453 -- first populate pl/sql table with chosen results from cache, if committing changes.
2454 t_chosen_table := store_chosen_results(p_params.assignment_id
2455 ,p_params.effective_date);
2456 end if;
2457
2458 -- clean the cache of existing records for all items on this asg - effective date comb
2459 update_result_set(t_results_table,p_params,'C');
2460
2461 -- Invoke benmngle to process all entitlements (options) for the CAGR_ID (plan)
2462 -- on this assignment, if we determine that there are entitlement_lines in existence
2463 -- for any items on the cagr, on the effective_date. (not inc. default elig lines)
2464
2465 open csr_SA_drive_benmngle;
2466 fetch csr_SA_drive_benmngle into v_SA_drive_benmngle;
2467 if csr_SA_drive_benmngle%found then
2468 close csr_SA_drive_benmngle;
2469 -- start benmngle
2470 process_entitlement_lines(p_pl_id => v_SA_drive_benmngle.pl_id
2471 ,p_opt_id => NULL -- running at plan level here
2472 ,p_person_id => v_SA_drive_benmngle.person_id
2473 ,p_benefit_action_id => v_benefit_action_id
2474 ,p_effective_date => p_params.effective_date
2475 ,p_bg_id => p_params.business_group_id);
2476
2477 -- read BEN eligibility output into structure
2478 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
2479 ,p_eligibility_table => t_eligibility_table
2480 ,p_counter => v_eligibility_counter);
2481
2482 else
2483 per_cagr_utility_pkg.put_log(' No active criteria lines found for the collective agreement.',1);
2484 close csr_SA_drive_benmngle;
2485 end if;
2486
2487 -- open the cursor, to get current entitlements
2488 FOR v_ents IN csr_SA_cagr_ents LOOP
2489 --dbms_output.put_line('loop number '||csr_SA_cagr_ents%rowcount);
2490 -- reset flag
2491 v_write_flag := FALSE;
2492
2493 if v_last_dataitem_id is not null then
2494 if (v_ents.CAGR_ENTITLEMENT_ITEM_ID <> v_last_dataitem_id) then
2495 --dbms_output.put_line('changing dataitem');
2496 -- The dataitem that is being processed has changed so....
2497 -- (Note: this block gets invoked for dataitem entitlement set #2 thru to penultimate,
2498 -- where a cagr returns > 1 dataitem entitlement set)
2499
2500 -- Call routine to add any retained rights records for the last dataitem
2501 -- to the process set, evaluate their beneficial rule, and
2502 -- return the completed process set, ready for writing.
2503 add_related_ret_rights(p_params.assignment_id
2504 ,v_last_dataitem_id
2505 ,p_params.effective_date
2506 ,t_results_table
2507 ,v_counter);
2508
2509 -- insert a record into the global pl/sql table for the entitlement item
2510 -- so that add_other_ret_rights does not also process the rr.
2511 v_ent_count := v_ent_count + 1;
2512 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
2513
2514 -- apply beneficial rule and write any valid entitlement results for the
2515 -- previous dataitem, and clear the plsql table.
2516 if v_counter > 0 then
2517 -- determine and set most beneficial value for results set
2518 set_beneficial_value(p_effective_date => p_params.effective_date
2519 ,p_results_table => t_results_table
2520 ,p_ben_rule => v_beneficial_rule
2521 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2522 ,p_ben_value => v_beneficial_value
2523 ,p_ben_row => v_ben_row
2524 ,p_rule_inconclusive => v_rule_inconclusive);
2525
2526 if v_rule_inconclusive then
2527 -- output warning message that beneficial could not be chosen
2528 -- and write results anyway..
2529 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2530 end if;
2531 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2532 update_result_set(t_results_table,p_params,'W');
2533 v_counter := 0;
2534 t_results_table.delete;
2535 end if;
2536 -- store new dataitem_id
2537 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2538 -- store new beneficial_rule
2539 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2540 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2541 per_cagr_utility_pkg.put_log(' ',1);
2542 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2543 end if;
2544 else
2545 --dbms_output.put_line('first dataitem');
2546 -- set dataitem and beneficial rule value on first iteration
2547 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2548 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2549 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2550 per_cagr_utility_pkg.put_log(' ',1);
2551 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2552 end if;
2553
2554 v_value := NULL; -- clear result variables before eval
2555 v_range_from := NULL;
2556 v_range_to := NULL;
2557 v_grade_spine_id := NULL;
2558 v_parent_spine_id := NULL;
2559 v_step_id := NULL;
2560 v_from_step_id := NULL;
2561 v_to_step_id := NULL;
2562
2563 -- determine whether current record is just entitlement item
2564 -- or entitlement line, and exec ff accordingly...
2565 if v_ents.formula_criteria = 'C' then -- ent line record
2566 if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
2567 l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
2568 ,NULL
2569 ,p_params.effective_date);
2570 else
2571 l_source_name := '*** Default ***';
2572 end if;
2573 per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
2574 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '||v_ents.cagr_entitlement_line_id);
2575
2576 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2577 -- write the record as this is default elig line
2578 v_value := v_ents.value;
2579 v_range_from := v_ents.range_from;
2580 v_range_to := v_ents.range_to;
2581 v_grade_spine_id := v_ents.grade_spine_id;
2582 v_parent_spine_id := v_ents.parent_spine_id;
2583 v_step_id := v_ents.step_id;
2584 v_from_step_id := v_ents.from_step_id;
2585 v_to_step_id := v_ents.to_step_id;
2586 v_write_flag := TRUE;
2587 else -- regular eligbility line
2588 if v_eligibility_counter <> 0 then -- we ran benmngle so
2589 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
2590 -- has a valid eligibility
2591 if check_entitlement_eligible(p_OIPL_ID => v_ents.OIPL_ID
2592 ,p_eligibility_table => t_eligibility_table) then
2593 -- entitlement_line is eligible so assign its value mark record for writing
2594 v_value := v_ents.value;
2595 v_range_from := v_ents.range_from;
2596 v_range_to := v_ents.range_to;
2597 v_grade_spine_id := v_ents.grade_spine_id;
2598 v_parent_spine_id := v_ents.parent_spine_id;
2599 v_step_id := v_ents.step_id;
2600 v_from_step_id := v_ents.from_step_id;
2601 v_to_step_id := v_ents.to_step_id;
2602 v_write_flag := TRUE;
2603 end if;
2604 else
2605 -- log error that there are no BEN eligibility result records returned
2606 -- from benmngle, for this compensation_object
2607 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2608 end if;
2609 end if;
2610
2611 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2612 -- check the asg grade matches the grade_spine grade, as well as elig profile
2613 -- being satisfied, in order to be eligible for this PYS criteria.
2614 if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2615 ,p_params.effective_date),-1) then
2616 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2617 v_write_flag := FALSE;
2618 end if;
2619 end if;
2620
2621 elsif v_ents.formula_criteria = 'F' then -- ent record
2622 if v_ents.FORMULA_ID is not null then
2623 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2624 l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2625 ,v_ents.FORMULA_ID
2626 ,p_params.effective_date);
2627 per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2628
2629
2630 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2631 ,p_effective_date => p_params.effective_date
2632 ,p_assignment_id => p_params.assignment_id
2633 ,p_category_name => v_ents.category_name
2634 ,p_out_rec => l_cagr_FF_record);
2635
2636 -- assign FF return values to local vars if set
2637 if v_ents.category_name in ('ASG','PAY','ABS') then
2638 if l_cagr_FF_record.value is not null then
2639 v_value := l_cagr_FF_record.value;
2640 v_range_from := l_cagr_FF_record.range_from;
2641 v_range_to := l_cagr_FF_record.range_to;
2642 v_write_flag := TRUE;
2643 else
2644 -- log message as the formula evaluated to null and continue with next entitlement record
2645 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2646 v_write_flag := FALSE;
2647 end if;
2648 elsif v_ents.category_name = 'PYS' then
2649 if l_cagr_FF_record.grade_spine_id is not null
2650 and l_cagr_FF_record.parent_spine_id is not null
2651 and l_cagr_FF_record.step_id is not null then
2652 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
2653 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
2654 v_step_id := l_cagr_FF_record.step_id;
2655 v_from_step_id := l_cagr_FF_record.from_step_id;
2656 v_to_step_id := l_cagr_FF_record.to_step_id;
2657 v_write_flag := TRUE;
2658 else
2659 -- log message as the formula didn't evaluated to null and continue with next entitlement record
2660 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2661 end if;
2662 end if;
2663 end if;
2664 end if;
2665
2666 if v_write_flag = TRUE then
2667 -- Assign the successfully evaluated entitlement into the plsql table.
2668 v_counter := v_counter + 1;
2669
2670 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_ents.COLLECTIVE_AGREEMENT_ID;
2671 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2672 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
2673 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
2674 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
2675 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
2676 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
2677 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
2678 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
2679 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
2680 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
2681 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
2682 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
2683 t_results_table(v_counter).VALUE := v_value;
2684 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
2685 t_results_table(v_counter).RANGE_FROM := v_range_from;
2686 t_results_table(v_counter).RANGE_TO := v_range_to;
2687 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
2688 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
2689 t_results_table(v_counter).STEP_ID := v_step_id;
2690 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
2691 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
2692 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
2693 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
2694 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
2695 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
2696 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
2697
2698
2699
2700 end if;
2701 END LOOP;
2702
2703
2704 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
2705 -- returned by the above cursor, which could also be the first
2706 if v_last_dataitem_id is not null then
2707 -- Call routine to add any retained rights records for the last dataitem
2708 -- to the process set, evaluate their beneficial rule, and
2709 -- return the completed process set, ready for writing.
2710 add_related_ret_rights(p_params.assignment_id
2711 ,v_last_dataitem_id
2712 ,p_params.effective_date
2713 ,t_results_table
2714 ,v_counter);
2715
2716
2717 -- insert a record into the ent_item pl/sql table for the entitlement item
2718 -- so that add_other_ret_rights does not also process the rr.
2719 v_ent_count := v_ent_count + 1;
2720 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
2721
2722 -- apply beneficial rule and write any valid entitlement results for the
2723 -- previous dataitem, and clear the plsql table.
2724 if v_counter > 0 then
2725 -- determine and set most beneficial value for results set
2726 set_beneficial_value(p_effective_date => p_params.effective_date
2727 ,p_results_table => t_results_table
2728 ,p_ben_rule => v_beneficial_rule
2729 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
2730 ,p_ben_value => v_beneficial_value
2731 ,p_ben_row => v_ben_row
2732 ,p_rule_inconclusive => v_rule_inconclusive);
2733
2734 if v_rule_inconclusive then
2735 -- output warning message that beneficial could not be chosen
2736 -- and write results anyway..
2737 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2738 end if;
2739 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2740 update_result_set(t_results_table,p_params,'W');
2741 t_results_table.delete;
2742 v_counter := 0;
2743 end if;
2744 else
2745 per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
2746 end if;
2747
2748 -- when not in SE mode, also need to add in any other retained rights for dataitems
2749 -- that are not related to the dataitems returned by the current entitlements
2750 -- set above. This could process multiple entitlements for multiple dataitems
2751 add_other_ret_rights(p_params);
2752
2753 -- clear out the global items table and chosen results table
2754 g_entitlement_items.DELETE;
2755 t_chosen_table.DELETE;
2756
2757 elsif p_params.operation_mode = 'SE' then
2758 --
2759 -- ****** Single Entitlement mode *******
2760 --
2761 -- This mode differs in behaviour from Single Assignment as follows:
2762 -- Only regenerates or returns results for 1 entitlement on the cagr.
2763 -- Returns result directly to calling code, via structure, or an HR error number.
2764 -- Cached results are not automatically wiped and replaced:
2765 -- if check_cache = 'Y' and result found then no re-evaluation, and cached result is returned
2766 -- if check_cache = 'N' or result not found then re-evaluates results, but these are
2767 -- only written to cache if p_commit_flag = 'Y'. (If p_commit_flag = N, new result returned only).
2768 -- When regenerating results, if the process is unable to lock the cache record to be refreshed then refresh will
2769 -- not be attempted and the existing beneficial value should be returned, if any exists.
2770 -- Only processes related retained rights, not other retained rights.
2771 -- Errors are: HR_289577_CAGR_NO_DATA_FOUND - no entitlement result exists for the entitlement_id
2772 -- HR_289578_CAGR_NO_BENEFICIAL - no beneficial rule or rule was inconclusive
2773 -- HR_289579_CAGR_SECONDARY_ASG - secondary assignment
2774
2775
2776 -- check asg is primary
2777 open csr_primary_asg;
2778 fetch csr_primary_asg into v_dummy;
2779 if csr_primary_asg%found then
2780 v_primary_flag := TRUE;
2781 end if;
2782 close csr_primary_asg;
2783
2784 If p_params.commit_flag = 'Y' then
2785 -- first populate pl/sql table with chosen results from cache for the assignment, if committing.
2786 t_chosen_table := store_chosen_results(p_params.assignment_id
2787 ,p_params.effective_date);
2788 end if;
2789
2790 if nvl(fnd_profile.value('PER_CHECK_ENTITLEMENT_CACHE'),'N') = 'Y' and v_primary_flag then
2791 per_cagr_utility_pkg.put_log(' Profile value set to check entitlement cache before evaluating');
2792
2793 -- check the cache
2794 p_SE_rec := check_cache(p_params.assignment_id
2795 ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2796 ,p_params.entitlement_item_id
2797 ,p_params.effective_date);
2798
2799 l_cache_checked := TRUE;
2800 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND'
2801 or p_SE_rec.error = 'HR_289578_CAGR_NO_BENEFICIAL' then
2802 -- not found in cache so we will need to re-evaluate
2803 l_evaluate := TRUE;
2804 end if;
2805 else
2806 p_SE_rec.error := 'HR_289577_CAGR_NO_DATA_FOUND';
2807 per_cagr_utility_pkg.put_log(' Profile value set to always re-evaluate');
2808 l_evaluate := TRUE;
2809 end if;
2810
2811
2812 if l_evaluate and v_primary_flag then
2813
2814 -- Invoke benmngle to process all entitlements (options) for the CAGR_ID (plan)
2815 -- on this assignment, if we determine that there are entitlement_lines in existence
2816 -- for the single entitlement_item on the cagr on the effective_date.
2817
2818 open csr_SE_drive_benmngle;
2819 fetch csr_SE_drive_benmngle into v_SE_drive_benmngle;
2820 if csr_SE_drive_benmngle%found then
2821 close csr_SE_drive_benmngle;
2822 -- start benmngle, for all entitlements, but may be quicker to call it once for each
2823 -- option_in_plan for the single entitlement?
2824 --
2825 process_entitlement_lines(p_pl_id => v_SE_drive_benmngle.pl_id
2826 ,p_opt_id => NULL -- still run for all entitlement items
2827 ,p_person_id => v_SE_drive_benmngle.person_id
2828 ,p_benefit_action_id => v_benefit_action_id
2829 ,p_effective_date => p_params.effective_date
2830 ,p_bg_id => p_params.business_group_id);
2831
2832 -- read BEN eligibility output into structure
2833 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
2834 ,p_eligibility_table => t_eligibility_table
2835 ,p_counter => v_eligibility_counter);
2836
2837 else
2838 per_cagr_utility_pkg.put_log(' No active entitlement lines exist for collective agreement');
2839 close csr_SE_drive_benmngle;
2840 end if;
2841
2842 -- open the cursor, to get single entitlement data
2843 FOR v_ents IN csr_SE_cagr_ents LOOP
2844 -- set the beneficial rule for later use...
2845 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2846 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2847
2848 v_last_dataitem_id := v_ents.cagr_entitlement_item_id;
2849 v_write_flag := FALSE;
2850
2851 v_value := NULL; -- clear result variables before eval
2852 v_range_from := NULL;
2853 v_range_to := NULL;
2854 v_grade_spine_id := NULL;
2855 v_parent_spine_id := NULL;
2856 v_step_id := NULL;
2857 v_from_step_id := NULL;
2858 v_to_step_id := NULL;
2859
2860 -- determine whether current record is entitlement item (so run ff) or entitlement line
2861 if v_ents.formula_criteria = 'C' then -- line item record
2862 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
2863 ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
2864
2865 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2866 -- write the record as this is default elig line
2867 v_value := v_ents.value;
2868 v_range_from := v_ents.range_from;
2869 v_range_to := v_ents.range_to;
2870 v_grade_spine_id := v_ents.grade_spine_id;
2871 v_parent_spine_id := v_ents.parent_spine_id;
2872 v_step_id := v_ents.step_id;
2873 v_from_step_id := v_ents.from_step_id;
2874 v_to_step_id := v_ents.to_step_id;
2875 v_write_flag := TRUE;
2876 else -- regular eligibility line
2877 if v_eligibility_counter <> 0 then -- we ran benmngle
2878 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
2879 -- has a valid eligibility
2880 if check_entitlement_eligible(p_OIPL_ID => v_ents.OIPL_ID
2881 ,p_eligibility_table => t_eligibility_table) then
2882 -- entitlement_line is eligible so assign its value mark record for writing
2883 v_value := v_ents.value;
2884 v_range_from := v_ents.range_from;
2885 v_range_to := v_ents.range_to;
2886 v_grade_spine_id := v_ents.grade_spine_id;
2887 v_parent_spine_id := v_ents.parent_spine_id;
2888 v_step_id := v_ents.step_id;
2889 v_from_step_id := v_ents.from_step_id;
2890 v_to_step_id := v_ents.to_step_id;
2891 v_write_flag := TRUE;
2892 end if;
2893 else
2894 -- log error that there are no BEN eligibility result records returned by benmngle
2895 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2896 end if;
2897 end if;
2898
2899 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2900 -- check the asg grade matches the grade_spine grade, as well as elig profile
2901 -- being satisfied, in order to be eligible for this PYS criteria.
2902 if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2903 ,p_params.effective_date),-1) then
2904 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2905 v_write_flag := FALSE;
2906 end if;
2907 end if;
2908
2909 elsif v_ents.formula_criteria = 'F' then -- item record
2910 if v_ents.FORMULA_ID is not null then
2911 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
2912 ||v_ents.item_name||', calling ff');
2913
2914 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2915 ,p_effective_date => p_params.effective_date
2916 ,p_assignment_id => p_params.assignment_id
2917 ,p_category_name => v_ents.category_name
2918 ,p_out_rec => l_cagr_FF_record);
2919
2920 -- assign FF return values to local vars if set
2921 if v_ents.category_name in ('ASG','PAY','ABS') then
2922 if l_cagr_FF_record.value is not null then
2923 v_value := l_cagr_FF_record.value;
2924 v_range_from := l_cagr_FF_record.range_from;
2925 v_range_to := l_cagr_FF_record.range_to;
2926 v_write_flag := TRUE;
2927 else
2928 -- log message as the formula evaluated to null and continue with next entitlement record
2929 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2930 v_write_flag := FALSE;
2931 end if;
2932 elsif v_ents.category_name = 'PYS' then
2933 if l_cagr_FF_record.grade_spine_id is not null
2934 and l_cagr_FF_record.parent_spine_id is not null
2935 and l_cagr_FF_record.step_id is not null then
2936 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
2937 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
2938 v_step_id := l_cagr_FF_record.step_id;
2939 v_from_step_id := l_cagr_FF_record.from_step_id;
2940 v_to_step_id := l_cagr_FF_record.to_step_id;
2941 v_write_flag := TRUE;
2942 else
2943 -- log message as the formula didn't evaluated to null and continue with next entitlement record
2944 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2945 end if;
2946 end if;
2947 end if;
2948 end if;
2949
2950 if v_write_flag = TRUE then
2951 -- Assign the successfully evaluated entitlement into the plsql table.
2952 v_counter := v_counter + 1;
2953
2954 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_ents.COLLECTIVE_AGREEMENT_ID;
2955 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2956 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
2957 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
2958 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
2959 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
2960 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
2961 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
2962 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
2963 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
2964 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
2965 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
2966 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
2967 t_results_table(v_counter).VALUE := v_value;
2968 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
2969 t_results_table(v_counter).RANGE_FROM := v_range_from;
2970 t_results_table(v_counter).RANGE_TO := v_range_to;
2971 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
2972 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
2973 t_results_table(v_counter).STEP_ID := v_step_id;
2974 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
2975 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
2976 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
2977 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
2978 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
2979 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
2980 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
2981
2982 end if;
2983 END LOOP;
2984
2985 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
2986 -- returned by the above cursor, which could also be the first
2987 if v_last_dataitem_id is not null then
2988 -- Call routine to add any retained rights records for the last dataitem
2989 -- to the process set, evaluate their beneficial rule, and
2990 -- return the completed process set, ready for writing.
2991 add_related_ret_rights(p_params.assignment_id
2992 ,v_last_dataitem_id
2993 ,p_params.effective_date
2994 ,t_results_table
2995 ,v_counter);
2996
2997 -- apply beneficial rule and write any valid entitlement results for the
2998 -- previous dataitem, and clear the plsql table.
2999 if v_counter > 0 then
3000 -- determine and set most beneficial value for results set
3001 set_beneficial_value(p_effective_date => p_params.effective_date
3002 ,p_results_table => t_results_table
3003 ,p_ben_rule => v_beneficial_rule
3004 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
3005 ,p_ben_value => v_beneficial_value
3006 ,p_ben_row => v_ben_row
3007 ,p_rule_inconclusive => v_rule_inconclusive);
3008
3009 if not(l_cache_checked) then
3010 -- check the cache, if it wasn't done above
3011 p_SE_rec := check_cache(p_params.assignment_id
3012 ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
3013 ,p_params.entitlement_item_id
3014 ,p_params.effective_date);
3015 end if;
3016
3017
3018 -- eval commit param to see if we should write results to cache before returning structure.
3019 if p_params.commit_flag = 'Y' then
3020 l_update_cache := TRUE;
3021 else
3022 l_update_cache := FALSE;
3023 end if;
3024
3025 if l_update_cache then
3026 -- only write new results to cache if p_commit_flag allows
3027 BEGIN
3028 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' then
3029 -- write new result set to cache, as none was found
3030 insert_result_set(t_results_table, p_params);
3031 elsif p_SE_rec.error is NULL or p_SE_rec.error = 'HR_289578_CAGR_NO_BENEFICIAL' then
3032 -- 'update' cache with results from re-evaluations (whether they differ or not)
3033 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3034 update_result_set(t_results_table,p_params,'W');
3035 p_SE_rec.ERROR := NULL; -- do not return this error
3036 end if;
3037 EXCEPTION
3038 when resource_busy then
3039 per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on per_cagr_entitlement_results');
3040 per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3041 -- but this is not fatal in this mode, so continue and pass out results
3042 END;
3043 else -- not updating the cache, but we have regenerated data so nullify this error now
3044 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' and t_results_table.count > 0 then
3045 p_SE_rec.ERROR := NULL; -- do not return this error
3046 end if;
3047 end if;
3048
3049 if v_rule_inconclusive then
3050 -- output warning message that ben rule failed to id a beneficial result
3051 p_SE_rec.ERROR := 'HR_289417_CAGR_BENRULE_FAIL';
3052 else
3053 if v_ben_row > 0 then
3054 -- populate output structure, with beneficial entitlement value or error code
3055 p_SE_rec.VALUE := t_results_table(v_ben_row).VALUE;
3056 p_SE_rec.RANGE_FROM := t_results_table(v_ben_row).RANGE_FROM;
3057 p_SE_rec.RANGE_TO := t_results_table(v_ben_row).RANGE_TO;
3058 p_SE_rec.GRADE_SPINE_ID := t_results_table(v_ben_row).GRADE_SPINE_ID;
3059 p_SE_rec.PARENT_SPINE_ID := t_results_table(v_ben_row).PARENT_SPINE_ID;
3060 p_SE_rec.STEP_ID := t_results_table(v_ben_row).STEP_ID;
3061 p_SE_rec.FROM_STEP_ID := t_results_table(v_ben_row).FROM_STEP_ID;
3062 p_SE_rec.TO_STEP_ID := t_results_table(v_ben_row).TO_STEP_ID;
3063 else
3064 p_SE_rec.ERROR := 'HR_289578_CAGR_NO_BENEFICIAL';
3065 end if;
3066 end if;
3067 end if;
3068 else
3069 -- no active ents matched the entitlement id
3070 p_SE_rec.error := 'HR_289577_CAGR_NO_DATA_FOUND';
3071 end if;
3072 -- clear out the global items table
3073 g_entitlement_items.DELETE;
3074 end if;
3075
3076 if p_params.commit_flag = 'Y' and t_chosen_table.count <> 0 then
3077 t_chosen_table.DELETE;
3078 end if;
3079 t_results_table.DELETE;
3080 v_counter := 0;
3081
3082 -- return error if called for secondary asg
3083 if not(v_primary_flag) then
3084 p_SE_rec.ERROR := 'HR_289579_CAGR_SECONDARY_ASG';
3085 end if;
3086 per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3087
3088 -- log return values...
3089 per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3090 per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3091 per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092 per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093 per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094 per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095 per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3096 per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3097 per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3098 --
3099 --
3100 elsif p_params.operation_mode = 'SC' then
3101 --
3102 -- ****** Single Collective Agreement mode *******
3103 --
3104 -- features of this mode:
3105 -- 1) it processes all assignments found for the cagr on the effective_date
3106 -- 2) benmngle runs at pl level (processes all people) if there are asgs on the cagr, and it has lines
3107 -- 3) each assignment has a new request / separate logs, under the parent request
3108 -- 4) when run from CM, all logs are output to O/S file, under the parent_request_id
3109 -- 5) Conditionally calls apply process once all evaluation is complete (with null assignment_id)
3110
3111 -- Invoke benmngle to process all entitlements (options) for all people on the CAGR_ID (plan)
3112 -- if we determine that there are entitlement_lines in existence
3113 -- for any items on the cagr, on the effective_date (not inc. default elig lines),
3114 -- and that there are assignments on the cagr on that date.
3115
3116
3117 open csr_SC_drive_benmngle;
3118 fetch csr_SC_drive_benmngle into l_pl_id;
3119 if csr_SC_drive_benmngle%found then
3120 close csr_SC_drive_benmngle;
3121 -- start benmngle
3122 process_entitlement_lines(p_pl_id => l_pl_id
3123 ,p_opt_id => NULL -- running at plan level here
3124 ,p_person_id => NULL -- for all people
3125 ,p_benefit_action_id => v_benefit_action_id
3126 ,p_effective_date => p_params.effective_date
3127 ,p_bg_id => p_params.business_group_id);
3128
3129 -- read BEN eligibility output into structure (for all people) on the cagr
3130 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
3131 ,p_eligibility_table => t_eligibility_table
3132 ,p_counter => v_eligibility_counter);
3133
3134 else
3135 per_cagr_utility_pkg.put_log(' No assignments use the collective agreement',1);
3136 per_cagr_utility_pkg.put_log(' or no active criteria lines found for the collective agreement.',1);
3137 close csr_SC_drive_benmngle;
3138 end if;
3139 g_benefit_action_id := v_benefit_action_id; -- Bug 13703461
3140
3141 per_cagr_utility_pkg.put_log('Processing the following assignments on the collective agreement: ',1);
3142 --
3143 -- load all the assignment ids to be processed into pl/sql table.
3144 --
3145 open csr_assignments_to_process;
3146 loop
3147 v_counter := v_counter+1;
3148 fetch csr_assignments_to_process into t_assignments_table(v_counter);
3149 exit when csr_assignments_to_process%notfound;
3150 per_cagr_utility_pkg.put_log(' '||t_assignments_table(v_counter).assignment_id,1);
3151 end loop;
3152 close csr_assignments_to_process;
3153 v_counter := 0;
3154
3155 -- write the log out and save the request_id
3156 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3157 l_parent_request_id := p_params.cagr_request_id;
3158
3159 -- could now break pl/sql table into varray subsets, ready for multiple threads
3160
3161 -- loop through assignment_id table
3162 if t_assignments_table.count <> 0 then
3163 FOR k in t_assignments_table.first .. t_assignments_table.last LOOP
3164 Begin
3165
3166 p_params.assignment_id := t_assignments_table(k).assignment_id;
3167
3168 -- for each asg on the cagr on the effective_date:
3169 -- 1) create a request_id (for asg logging)
3170 -- 2) clean results cache
3171 -- 2) do SA style cursor processing and logging (in a function for the SA code)
3172 -- 3) commit AFTER each assignment
3173
3174 per_cagr_utility_pkg.create_cagr_request(p_process_date => p_params.effective_date
3175 ,p_operation_mode => 'SA'
3176 ,p_business_group_id => p_params.business_group_id
3177 ,p_assignment_id => t_assignments_table(k).assignment_id
3178 ,p_assignment_set_id => NULL
3179 ,p_collective_agreement_id => p_params.collective_agreement_id
3180 ,p_collective_agreement_set_id => NULL
3181 ,p_payroll_id => NULL
3182 ,p_person_id => NULL
3183 ,p_entitlement_item_id => NULL
3184 ,p_parent_request_id => l_parent_request_id
3185 ,p_commit_flag => p_params.commit_flag
3186 ,p_denormalise_flag => p_params.denormalise_flag
3187 ,p_cagr_request_id => p_params.cagr_request_id);
3188 -- output log header
3189 per_cagr_utility_pkg.put_log(g_head_separator,1);
3190 per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3191 per_cagr_utility_pkg.put_log(g_head_separator,1);
3192 per_cagr_utility_pkg.put_log(' ',1);
3193 per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194 ' during Single Collective Agreement mode.',1);
3195 per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3196 per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3197 per_cagr_utility_pkg.put_log(' ',1);
3198
3199 If p_params.commit_flag = 'Y' then
3200 -- first create pl/sql table with chosen results for all items for the asg from cache, if committing.
3201 t_chosen_table := store_chosen_results(p_params.assignment_id
3202 ,p_params.effective_date);
3203 end if;
3204
3205 -- clean the cache of existing records for all items on this asg - effective date comb
3206 --
3207 update_result_set(t_results_table,p_params,'C');
3208
3209 --
3210 -- start cursor processing for the current asg
3211 --
3212
3213 FOR v_ents IN csr_SC_cagr_details LOOP
3214 -- reset flag
3215 v_write_flag := FALSE;
3216
3217 if v_last_dataitem_id is not null then
3218 if (v_ents.CAGR_ENTITLEMENT_ITEM_ID <> v_last_dataitem_id) then
3219 --dbms_output.put_line('changing dataitem');
3220 -- The dataitem that is being processed has changed so....
3221 -- (Note: this block gets invoked for dataitem entitlement set #2 thru to penultimate,
3222 -- where a cagr returns > 1 dataitem entitlement set)
3223
3224 -- Call routine to add any retained rights records for the last dataitem
3225 -- to the process set, evaluate their beneficial rule, and
3226 -- return the completed process set, ready for writing.
3227 add_related_ret_rights(p_params.assignment_id
3228 ,v_last_dataitem_id
3229 ,p_params.effective_date
3230 ,t_results_table
3231 ,v_counter);
3232
3233 -- insert a record into the global pl/sql table for the entitlement item
3234 -- so that add_other_ret_rights does not also process the rr.
3235 v_ent_count := v_ent_count + 1;
3236 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
3237
3238 -- apply beneficial rule and write any valid entitlement results for the
3239 -- previous dataitem, and clear the plsql table.
3240 if v_counter > 0 then
3241 -- determine and set most beneficial value for results set
3242 set_beneficial_value(p_effective_date => p_params.effective_date
3243 ,p_results_table => t_results_table
3244 ,p_ben_rule => v_beneficial_rule
3245 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
3246 ,p_ben_value => v_beneficial_value
3247 ,p_ben_row => v_ben_row
3248 ,p_rule_inconclusive => v_rule_inconclusive);
3249
3250 if v_rule_inconclusive then
3251 -- output warning message that beneficial could not be chosen
3252 -- and write results anyway..
3253 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3254 end if;
3255 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3256 update_result_set(t_results_table,p_params,'W');
3257 v_counter := 0;
3258 t_results_table.delete;
3259 end if;
3260 -- store new dataitem_id
3261 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3262 -- store new beneficial_rule
3263 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3264 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3265 per_cagr_utility_pkg.put_log(' ',1);
3266 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3267 end if;
3268 else
3269 --dbms_output.put_line('first dataitem');
3270 -- set dataitem and beneficial rule value on first iteration
3271 v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3272 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3273 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3274 per_cagr_utility_pkg.put_log(' ',1);
3275 per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3276 end if;
3277
3278 v_value := NULL; -- clear result variables before eval
3279 v_range_from := NULL;
3280 v_range_to := NULL;
3281 v_grade_spine_id := NULL;
3282 v_parent_spine_id := NULL;
3283 v_step_id := NULL;
3284 v_from_step_id := NULL;
3285 v_to_step_id := NULL;
3286
3287 -- determine whether current record is just entitlement item
3288 -- or entitlement line, and exec ff accordingly...
3289 if v_ents.formula_criteria = 'C' then -- ent line record
3290 if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
3291 l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
3292 ,NULL
3293 ,p_params.effective_date);
3294 else
3295 l_source_name := '*** Default ***';
3296 end if;
3297 per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
3298 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '
3299 ||v_ents.cagr_entitlement_line_id);
3300
3301 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3302 -- write the record as this is default elig line
3303 v_value := v_ents.value;
3304 v_range_from := v_ents.range_from;
3305 v_range_to := v_ents.range_to;
3306 v_grade_spine_id := v_ents.grade_spine_id;
3307 v_parent_spine_id := v_ents.parent_spine_id;
3308 v_step_id := v_ents.step_id;
3309 v_from_step_id := v_ents.from_step_id;
3310 v_to_step_id := v_ents.to_step_id;
3311 v_write_flag := TRUE;
3312 else -- regular eligbility line
3313 if v_eligibility_counter <> 0 then -- we ran benmngle so
3314 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
3315 -- has a valid eligibility
3316 if check_entitlement_eligible(p_person_id => t_assignments_table(k).person_id
3317 ,p_OIPL_ID => v_ents.OIPL_ID
3318 ,p_eligibility_table => t_eligibility_table) then
3319 -- entitlement_line is eligible so assign its value mark record for writing
3320 v_value := v_ents.value;
3321 v_range_from := v_ents.range_from;
3322 v_range_to := v_ents.range_to;
3323 v_grade_spine_id := v_ents.grade_spine_id;
3324 v_parent_spine_id := v_ents.parent_spine_id;
3325 v_step_id := v_ents.step_id;
3326 v_from_step_id := v_ents.from_step_id;
3327 v_to_step_id := v_ents.to_step_id;
3328 v_write_flag := TRUE;
3329 end if;
3330 else
3331 -- log error that there are no BEN eligibility result records returned
3332 -- from benmngle, for this compensation_object
3333 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3334 end if;
3335 end if;
3336 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3337 -- check the asg grade matches the grade_spine grade, as well as elig profile
3338 -- being satisfied, in order to be eligible for this PYS criteria.
3339 if nvl(t_assignments_table(k).grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3340 ,p_params.effective_date),-1) then
3341 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spi
3342 ne. ',1);
3343 v_write_flag := FALSE;
3344 end if;
3345 end if;
3346
3347 elsif v_ents.formula_criteria = 'F' then -- ent record
3348 if v_ents.FORMULA_ID is not null then
3349 per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3350 l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3351 ,v_ents.FORMULA_ID
3352 ,p_params.effective_date);
3353 per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3354
3355
3356 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3357 ,p_effective_date => p_params.effective_date
3358 ,p_assignment_id => p_params.assignment_id
3359 ,p_category_name => v_ents.category_name
3360 ,p_out_rec => l_cagr_FF_record);
3361
3362 -- assign FF return values to local vars if set
3363 if v_ents.category_name in ('ASG','PAY','ABS') then
3364 if l_cagr_FF_record.value is not null then
3365 v_value := l_cagr_FF_record.value;
3366 v_range_from := l_cagr_FF_record.range_from;
3367 v_range_to := l_cagr_FF_record.range_to;
3368 v_write_flag := TRUE;
3369 else
3370 -- log message as the formula evaluated to null and continue with next entitlement record
3371 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3372 v_write_flag := FALSE;
3373 end if;
3374 elsif v_ents.category_name = 'PYS' then
3375 if l_cagr_FF_record.grade_spine_id is not null
3376 and l_cagr_FF_record.parent_spine_id is not null
3377 and l_cagr_FF_record.step_id is not null then
3378 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
3379 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
3380 v_step_id := l_cagr_FF_record.step_id;
3381 v_from_step_id := l_cagr_FF_record.from_step_id;
3382 v_to_step_id := l_cagr_FF_record.to_step_id;
3383 v_write_flag := TRUE;
3384 else
3385 -- log message as the formula didn't evaluated to null and continue with next entitlement record
3386 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3387 end if;
3388 end if;
3389 end if;
3390 end if;
3391
3392 if v_write_flag = TRUE then
3393 -- Assign the successfully evaluated entitlement into the plsql table.
3394 v_counter := v_counter + 1;
3395
3396 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := p_params.COLLECTIVE_AGREEMENT_ID;
3397 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3398 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
3399 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
3400 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
3401 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
3402 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
3403 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
3404 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
3405 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
3406 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
3407 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
3408 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
3409 t_results_table(v_counter).VALUE := v_value;
3410 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
3411 t_results_table(v_counter).RANGE_FROM := v_range_from;
3412 t_results_table(v_counter).RANGE_TO := v_range_to;
3413 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
3414 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
3415 t_results_table(v_counter).STEP_ID := v_step_id;
3416 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
3417 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
3418 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
3419 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
3420 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
3421 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
3422 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
3423
3424
3425
3426 end if;
3427 END LOOP; -- csr_SC_cagr_datails
3428
3429
3430 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
3431 -- returned by the above cursor, which could also be the first
3432 if v_last_dataitem_id is not null then
3433 -- Call routine to add any retained rights records for the last dataitem
3434 -- to the process set, evaluate their beneficial rule, and
3435 -- return the completed process set, ready for writing.
3436 add_related_ret_rights(p_params.assignment_id
3437 ,v_last_dataitem_id
3438 ,p_params.effective_date
3439 ,t_results_table
3440 ,v_counter);
3441
3442 -- insert a record into the ent_item pl/sql table for the entitlement item
3443 -- so that add_other_ret_rights does not also process the rr.
3444 v_ent_count := v_ent_count + 1;
3445 g_entitlement_items(v_ent_count) := v_last_dataitem_id;
3446
3447 -- apply beneficial rule and write any valid entitlement results for the
3448 -- previous dataitem, and clear the plsql table.
3449 if v_counter > 0 then
3450 -- determine and set most beneficial value for results set
3451 set_beneficial_value(p_effective_date => p_params.effective_date
3452 ,p_results_table => t_results_table
3453 ,p_ben_rule => v_beneficial_rule
3454 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
3455 ,p_ben_value => v_beneficial_value
3456 ,p_ben_row => v_ben_row
3457 ,p_rule_inconclusive => v_rule_inconclusive);
3458
3459 if v_rule_inconclusive then
3460 -- output warning message that beneficial could not be chosen
3461 -- and write results anyway..
3462 per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3463 end if;
3464 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3465 update_result_set(t_results_table,p_params,'W');
3466 t_results_table.delete;
3467 v_counter := 0;
3468 end if;
3469 else
3470 per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
3471 end if;
3472
3473 -- add in any other retained rights for dataitems
3474 -- that are not related to the dataitems returned by the current entitlements
3475 -- set above. This could process multiple entitlements for multiple dataitems
3476 add_other_ret_rights(p_params);
3477
3478 -- clear out the global items table and chosen results table.
3479 g_entitlement_items.DELETE;
3480 t_chosen_table.DELETE;
3481
3482 -- reset write header flag
3483
3484 per_cagr_utility_pkg.put_log(' ',1);
3485 per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3486 per_cagr_utility_pkg.put_log(' ',1);
3487
3488 --
3489 -- Commit, if required, after every assignment.
3490 --
3491 if p_params.commit_flag = 'Y' then
3492 commit;
3493 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3494 elsif p_params.commit_flag = 'N' then
3495 rollback;
3496 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3497 end if;
3498
3499 -- write the log file for this assignment
3500 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3501
3502 Exception
3503 WHEN OTHERS THEN
3504 -- write the log file for this assignment
3505 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3506 -- reset to the parent request id and assignment
3507 p_params.cagr_request_id := l_parent_request_id;
3508 p_params.assignment_id := NULL;
3509 rollback;
3510 raise;
3511 End;
3512
3513 END LOOP; -- csr_assignments_to_process
3514 end if;
3515
3516 -- reset to the parent request id and assignment
3517 p_params.cagr_request_id := l_parent_request_id;
3518 p_params.assignment_id := NULL;
3519 --
3520 --
3521 elsif p_params.operation_mode = 'BE' then
3522 --
3523 -- ****** Batch Entitlement Item mode *******
3524 --
3525 -- features of this mode:
3526 -- 1) it processes eligibility for a single entitlement item across one or all cagrs (and their assignments)
3527 -- on an effective date, for that item only. (Does not use add_other retained_rights).
3528 -- 2) benmngle is run at PL + OPTION level or OPTION level only, (processing all people on those comp objects)
3529 -- if there are any lines that use the item on any/the cagr
3530 -- 3) Although many asgs are processed, unlike SC mode, each asg is worked under the main request id
3531 -- so the resulting log entries are only visible from CM window - not in the View Log window of PERWSCAR.
3532 -- 4) Populates a pl/sql table with a set of eligible people and assignments, ordered by person_id.
3533 -- Note: Not available from conc current program. Does not call the apply process.
3534
3535 open csr_BE_drive_benmngle;
3536 fetch csr_BE_drive_benmngle into l_opt_id;
3537 close csr_BE_drive_benmngle;
3538
3539 open csr_BE_plan;
3540 fetch csr_BE_plan into l_pl_id;
3541 close csr_BE_plan;
3542
3543 If l_opt_id is not null then
3544 -- start benmngle
3545 per_cagr_utility_pkg.put_log('Starting benmngle');
3546 process_entitlement_lines(p_pl_id => l_pl_id -- may run for a cagr
3547 ,p_opt_id => l_opt_id -- always run for an iteme
3548 ,p_person_id => NULL -- for all people on the cagr(s)
3549 ,p_benefit_action_id => v_benefit_action_id
3550 ,p_effective_date => p_params.effective_date
3551 ,p_bg_id => p_params.business_group_id);
3552
3553 -- read BEN eligibility output into structure (for all people) on the cagr
3554 get_BEN_eligibility_info(p_benefit_action_id => v_benefit_action_id
3555 ,p_eligibility_table => t_eligibility_table
3556 ,p_counter => v_eligibility_counter);
3557
3558 else
3559 per_cagr_utility_pkg.put_log(' No active criteria lines found for the item on any collective agreement.',1);
3560 end if;
3561 per_cagr_utility_pkg.put_log('Processing the following collective agreements that have entitlements using the item: ',1);
3562
3563 --
3564 -- load all the cagrs and their assignments ids to be processed into pl/sql table.
3565 --
3566 open csr_BE_assignments_to_process;
3567 loop
3568 v_counter := v_counter+1;
3569 fetch csr_BE_assignments_to_process into t_cagr_assignments_table(v_counter);
3570 exit when csr_BE_assignments_to_process%notfound;
3571 If t_cagr_assignments_table(v_counter).collective_agreement_id <> l_last_cagr_id then
3572 l_last_cagr_id := t_cagr_assignments_table(v_counter).collective_agreement_id;
3573 per_cagr_utility_pkg.put_log(' '||t_cagr_assignments_table(v_counter).collective_agreement_id,1);
3574 End If;
3575 end loop;
3576 close csr_BE_assignments_to_process;
3577 v_counter := 0;
3578
3579 -- write the log out
3580 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3581
3582 -- could now break pl/sql table into varray subsets, ready for multiple threads
3583
3584 -- loop through table of assignment_id for each cagr
3585 If t_cagr_assignments_table.count <> 0 then
3586
3587 FOR k in t_cagr_assignments_table.first .. t_cagr_assignments_table.last LOOP
3588 BEGIN
3589
3590 -- 1) Do SE type processing for the specific entitlement item
3591 -- 2) check cache for a result for the item for asg
3592 -- 3) clean results cache/write result for the specific entitlement item for the asg
3593 -- 4) commit AFTER each assignment
3594
3595 p_params.assignment_id := t_cagr_assignments_table(k).assignment_id;
3596
3597 per_cagr_utility_pkg.put_log(' ',1);
3598 per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3599 ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3600 ' during Batch Entitlement mode.',1);
3601 per_cagr_utility_pkg.put_log(' ',1);
3602
3603
3604 -- for each asg on the cagr on the effective_date:
3605
3606 FOR v_ents IN csr_SE_cagr_ents LOOP
3607 -- set the beneficial rule for later use...
3608 v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3609 v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3610
3611 v_last_dataitem_id := v_ents.cagr_entitlement_item_id;
3612 v_write_flag := FALSE;
3613
3614 v_value := NULL; -- clear result variables before eval
3615 v_range_from := NULL;
3616 v_range_to := NULL;
3617 v_grade_spine_id := NULL;
3618 v_parent_spine_id := NULL;
3619 v_step_id := NULL;
3620 v_from_step_id := NULL;
3621 v_to_step_id := NULL;
3622
3623
3624 -- determine whether current record is entitlement item (so run ff) or entitlement line
3625 if v_ents.formula_criteria = 'C' then -- line item record
3626 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
3627 ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
3628
3629 if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3630 -- write the record as this is default elig line
3631 v_value := v_ents.value;
3632 v_range_from := v_ents.range_from;
3633 v_range_to := v_ents.range_to;
3634 v_grade_spine_id := v_ents.grade_spine_id;
3635 v_parent_spine_id := v_ents.parent_spine_id;
3636 v_step_id := v_ents.step_id;
3637 v_from_step_id := v_ents.from_step_id;
3638 v_to_step_id := v_ents.to_step_id;
3639 v_write_flag := TRUE;
3640 else -- regular eligibility line
3641 if v_eligibility_counter <> 0 then -- we ran benmngle
3642 -- read the ben eligibility pl/sql table to see if the cagr_entitlement_line
3643 -- has a valid eligibility
3644 if check_entitlement_eligible(p_person_id => t_cagr_assignments_table(k).person_id
3645 ,p_OIPL_ID => v_ents.OIPL_ID
3646 ,p_eligibility_table => t_eligibility_table) then
3647 -- entitlement_line is eligible so assign its value mark record for writing
3648 v_value := v_ents.value;
3649 v_range_from := v_ents.range_from;
3650 v_range_to := v_ents.range_to;
3651 v_grade_spine_id := v_ents.grade_spine_id;
3652 v_parent_spine_id := v_ents.parent_spine_id;
3653 v_step_id := v_ents.step_id;
3654 v_from_step_id := v_ents.from_step_id;
3655 v_to_step_id := v_ents.to_step_id;
3656 v_write_flag := TRUE;
3657 end if;
3658 else
3659 -- log error that there are no BEN eligibility result records returned by benmngle
3660 per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3661 end if;
3662 end if;
3663
3664 if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3665 -- check the asg grade matches the grade_spine grade, as well as elig profile
3666 -- being satisfied, in order to be eligible for this PYS criteria.
3667 if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3668 ,p_params.effective_date),-1) then
3669 per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
3670 v_write_flag := FALSE;
3671 end if;
3672 end if;
3673
3674 elsif v_ents.formula_criteria = 'F' then -- item record
3675 if v_ents.FORMULA_ID is not null then
3676 per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
3677 ||v_ents.item_name||', calling ff');
3678
3679 hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3680 ,p_effective_date => p_params.effective_date
3681 ,p_assignment_id => p_params.assignment_id
3682 ,p_category_name => v_ents.category_name
3683 ,p_out_rec => l_cagr_FF_record);
3684
3685 -- assign FF return values to local vars if set
3686 if v_ents.category_name in ('ASG','PAY','ABS') then
3687 if l_cagr_FF_record.value is not null then
3688 v_value := l_cagr_FF_record.value;
3689 v_range_from := l_cagr_FF_record.range_from;
3690 v_range_to := l_cagr_FF_record.range_to;
3691 v_write_flag := TRUE;
3692 else
3693 -- log message as the formula evaluated to null and continue with next entitlement record
3694 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3695 v_write_flag := FALSE;
3696 end if;
3697 elsif v_ents.category_name = 'PYS' then
3698 if l_cagr_FF_record.grade_spine_id is not null
3699 and l_cagr_FF_record.parent_spine_id is not null
3700 and l_cagr_FF_record.step_id is not null then
3701 v_grade_spine_id := l_cagr_FF_record.grade_spine_id;
3702 v_parent_spine_id := l_cagr_FF_record.parent_spine_id;
3703 v_step_id := l_cagr_FF_record.step_id;
3704 v_from_step_id := l_cagr_FF_record.from_step_id;
3705 v_to_step_id := l_cagr_FF_record.to_step_id;
3706 v_write_flag := TRUE;
3707 else
3708 -- log message as the formula didn't evaluated to null and continue with next entitlement record
3709 per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3710 end if;
3711 end if;
3712 end if;
3713 end if;
3714
3715 if v_write_flag = TRUE then
3716 -- Assign the successfully evaluated entitlement into the plsql table.
3717 v_counter := v_counter + 1;
3718
3719 t_results_table(v_counter).COLLECTIVE_AGREEMENT_ID := v_ents.COLLECTIVE_AGREEMENT_ID;
3720 t_results_table(v_counter).CAGR_ENTITLEMENT_ITEM_ID := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3721 t_results_table(v_counter).ELEMENT_TYPE_ID := v_ents.ELEMENT_TYPE_ID;
3722 t_results_table(v_counter).INPUT_VALUE_ID := v_ents.INPUT_VALUE_ID;
3723 t_results_table(v_counter).CAGR_API_ID := v_ents.CAGR_API_ID;
3724 t_results_table(v_counter).CAGR_API_PARAM_ID := v_ents.CAGR_API_PARAM_ID;
3725 t_results_table(v_counter).CATEGORY_NAME := v_ents.CATEGORY_NAME;
3726 t_results_table(v_counter).CAGR_ENTITLEMENT_ID := v_ents.CAGR_ENTITLEMENT_ID;
3727 t_results_table(v_counter).CAGR_ENTITLEMENT_LINE_ID := v_ents.CAGR_ENTITLEMENT_LINE_ID;
3728 t_results_table(v_counter).ASSIGNMENT_ID := p_params.ASSIGNMENT_ID;
3729 t_results_table(v_counter).OIPL_ID := v_ents.OIPL_ID;
3730 t_results_table(v_counter).ELIGY_PRFL_ID := v_ents.ELIGY_PRFL_ID;
3731 t_results_table(v_counter).FORMULA_ID := v_ents.FORMULA_ID;
3732 t_results_table(v_counter).VALUE := v_value;
3733 t_results_table(v_counter).UNITS_OF_MEASURE := v_ents.UNITS_OF_MEASURE;
3734 t_results_table(v_counter).RANGE_FROM := v_range_from;
3735 t_results_table(v_counter).RANGE_TO := v_range_to;
3736 t_results_table(v_counter).GRADE_SPINE_ID := v_grade_spine_id;
3737 t_results_table(v_counter).PARENT_SPINE_ID := v_parent_spine_id;
3738 t_results_table(v_counter).STEP_ID := v_step_id;
3739 t_results_table(v_counter).FROM_STEP_ID := v_from_step_id;
3740 t_results_table(v_counter).TO_STEP_ID := v_to_step_id;
3741 t_results_table(v_counter).COLUMN_TYPE := v_ents.COLUMN_TYPE;
3742 t_results_table(v_counter).COLUMN_SIZE := v_ents.COLUMN_SIZE;
3743 t_results_table(v_counter).MULTIPLE_ENTRIES_ALLOWED_FLAG := v_ents.MULTIPLE_ENTRIES_ALLOWED_FLAG;
3744 t_results_table(v_counter).BUSINESS_GROUP_ID := v_ents.BUSINESS_GROUP_ID;
3745 t_results_table(v_counter).FLEX_VALUE_SET_ID := v_ents.FLEX_VALUE_SET_ID;
3746 end if;
3747 END LOOP;
3748
3749 -- (Note: the following code gets invoked to complete processing of the last dataitem entitlement set
3750 -- returned by the above cursor, which could also be the first
3751 if v_last_dataitem_id is not null then
3752 -- Call routine to add any retained rights records for the last dataitem
3753 -- to the process set, evaluate their beneficial rule, and
3754 -- return the completed process set, ready for writing.
3755 add_related_ret_rights(p_params.assignment_id
3756 ,v_last_dataitem_id
3757 ,p_params.effective_date
3758 ,t_results_table
3759 ,v_counter);
3760
3761 -- apply beneficial rule and write any valid entitlement results for the
3762 -- previous dataitem, and clear the plsql table.
3763 if v_counter > 0 then
3764 -- determine and set most beneficial value for results set
3765 set_beneficial_value(p_effective_date => p_params.effective_date
3766 ,p_results_table => t_results_table
3767 ,p_ben_rule => v_beneficial_rule
3768 ,p_ben_rule_vs_id => v_beneficial_rule_vs_id
3769 ,p_ben_value => v_beneficial_value
3770 ,p_ben_row => v_ben_row
3771 ,p_rule_inconclusive => v_rule_inconclusive);
3772
3773
3774
3775
3776 if p_params.commit_flag = 'Y' then
3777 -- first populate pl/sql table with chosen results from cache for the assignment, if committing.
3778 t_chosen_table := store_chosen_results(p_params.assignment_id
3779 ,p_params.effective_date);
3780 BEGIN
3781 -- check the cache, for an existing result(s) for the item
3782 p_SE_rec := check_cache(p_params.assignment_id
3783 ,t_cagr_assignments_table(k).collective_agreement_id
3784 ,p_params.entitlement_item_id
3785 ,p_params.effective_date);
3786
3787
3788 if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' then
3789 -- write new result set to cache, as none was found
3790 insert_result_set(t_results_table, p_params);
3791 t_results_table.DELETE;
3792 v_counter := 0;
3793 elsif p_SE_rec.error is NULL or p_SE_rec.error = 'HR_289578_CAGR_NO_BENEFICIAL' then
3794 -- 'update' cache with results from re-evaluations (whether they differ or not)
3795 apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3796 update_result_set(t_results_table,p_params,'W');
3797 t_results_table.DELETE;
3798 v_counter := 0;
3799 p_SE_rec.ERROR := NULL; -- do not return this error
3800 end if;
3801 EXCEPTION
3802 WHEN RESOURCE_BUSY THEN
3803 per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on result for assignment:'
3804 ||p_params.assignment_id);
3805 per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3806 END;
3807 end if;
3808 end if;
3809 end if;
3810
3811 if p_params.commit_flag = 'Y' and t_chosen_table.count <> 0 then
3812 -- clear out the chosen and results tables.
3813 t_chosen_table.DELETE;
3814 end if;
3815
3816
3817 -- reset write header flag
3818
3819 per_cagr_utility_pkg.put_log(' ',1);
3820 per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3821 per_cagr_utility_pkg.put_log(' ',1);
3822
3823 --
3824 -- Commit, if required, after every assignment.
3825 --
3826 if p_params.commit_flag = 'Y' then
3827 commit;
3828 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3829 elsif p_params.commit_flag = 'N' then
3830 rollback;
3831 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3832 end if;
3833
3834 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3835
3836 EXCEPTION
3837 WHEN OTHERS THEN
3838 -- write the log file for this assignment
3839 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3840 -- reset to the parent request id and assignment
3841 p_params.assignment_id := NULL;
3842 rollback;
3843 raise;
3844 END;
3845 END LOOP; -- outer assignment loop
3846 end if;
3847
3848 -- reset values
3849 p_params.assignment_id := NULL;
3850
3851 else -- Other modes....
3852 null;
3853 end if;
3854
3855 per_cagr_utility_pkg.put_log(' ',1);
3856 per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3857 per_cagr_utility_pkg.put_log(' ',1);
3858 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3859 hr_utility.set_location('Leaving:'||l_proc, 100);
3860
3861
3862 EXCEPTION
3863 WHEN OTHERS THEN
3864 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3865 raise;
3866
3867 END evaluation_process;
3868
3869 --
3870 -- ----------------------------------------------------------------------------
3871 -- |------------------------------< initialise >-----------------------------|
3872 -- ----------------------------------------------------------------------------
3873 --
3874 PROCEDURE initialise
3875 (p_process_date in date
3876 ,p_operation_mode in varchar2
3877 ,p_business_group_id in number
3878 ,p_assignment_id in number default null
3879 ,p_assignment_set_id in number default null
3880 ,p_collective_agreement_id in number default null
3881 ,p_collective_agreement_set_id in number default null
3882 ,p_payroll_id in number default null
3883 ,p_person_id in number default null
3884 ,p_entitlement_item_id in number default null
3885 ,p_commit_flag in varchar2 default 'N'
3886 ,p_apply_results_flag in varchar2 default 'N'
3887 ,p_cagr_request_id out nocopy number) IS
3888 --
3889 l_proc constant varchar2(61) := g_pkg || '.initialise';
3890 p_params PER_CAGR_EVALUATION_PKG.control_structure;
3891 l_se_rec PER_CAGR_EVALUATION_PKG.cagr_SE_record;
3892 --
3893 BEGIN
3894 hr_utility.set_location('Entering:'||l_proc, 5);
3895
3896 --
3897 -- store params and cagr return cagr_request_id for use in this run
3898 --
3899 per_cagr_utility_pkg.create_cagr_request(p_process_date => p_process_date
3900 ,p_operation_mode => p_operation_mode
3901 ,p_business_group_id => p_business_group_id
3902 ,p_assignment_id => p_assignment_id
3903 ,p_assignment_set_id => p_assignment_set_id
3904 ,p_collective_agreement_id => p_collective_agreement_id
3905 ,p_collective_agreement_set_id => p_collective_agreement_set_id
3906 ,p_payroll_id => p_payroll_id
3907 ,p_person_id => p_person_id
3908 ,p_entitlement_item_id => p_entitlement_item_id
3909 ,p_parent_request_id => NULL
3910 ,p_commit_flag => p_commit_flag
3911 ,p_denormalise_flag => p_apply_results_flag
3912 ,p_cagr_request_id => p_cagr_request_id);
3913
3914 --
3915 -- Output log header
3916 --
3917 per_cagr_utility_pkg.put_log(g_head_separator,1);
3918 per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3919 --
3920 -- Ensure that all the mandatory arguments are not null
3921 --
3922 hr_api.mandatory_arg_error(p_api_name => l_proc
3923 ,p_argument => 'process_date'
3924 ,p_argument_value => p_process_date);
3925 hr_api.mandatory_arg_error(p_api_name => l_proc
3926 ,p_argument => 'business_group_id'
3927 ,p_argument_value => p_business_group_id);
3928 hr_api.mandatory_arg_error(p_api_name => l_proc
3929 ,p_argument => 'operation_mode'
3930 ,p_argument_value => p_operation_mode);
3931
3932
3933 -- test for required params for modes
3934
3935 if not(p_operation_mode in ('SA','SE','SC','BE')) then
3936 per_cagr_utility_pkg.log_and_raise_error('HR_289420_CAGR_INV_MODE'
3937 ,p_cagr_request_id);
3938 end if;
3939 if (p_operation_mode = 'SA' and p_assignment_id is null) then
3940 per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3941 ,p_cagr_request_id);
3942 elsif (p_operation_mode = 'SE') and
3943 (p_entitlement_item_id is null or p_assignment_id is null) then
3944 per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3945 ,p_cagr_request_id);
3946 elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3947 per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3948 ,p_cagr_request_id);
3949 elsif (p_operation_mode = 'SC' and p_collective_agreement_id is null) then
3950 per_cagr_utility_pkg.log_and_raise_error('HR_289597_INV_SC_PARAM'
3951 ,p_cagr_request_id);
3952 end if;
3953
3954 --
3955 -- test for invalid params and values for modes
3956 --
3957 if not (p_apply_results_flag in('N','Y')) then
3958 per_cagr_utility_pkg.log_and_raise_error('HR_289418_CAGR_INV_DFLAG'
3959 ,p_cagr_request_id);
3960 elsif not (p_commit_flag in ('N','Y')) then
3961 per_cagr_utility_pkg.log_and_raise_error('HR_289419_CAGR_INV_CFLAG'
3962 ,p_cagr_request_id);
3963 end if;
3964
3965 if (p_assignment_id is not null and not(p_operation_mode in ('SE','SA'))) or
3966 (p_assignment_set_id is not null) or
3967 (p_payroll_id is not null) or
3968 (p_person_id is not null) or
3969 (p_entitlement_item_id is not null and not(p_operation_mode in ('BE','SE'))) or
3970 (p_collective_agreement_id is not null and not(p_operation_mode in ('SC','BE'))) or
3971 (p_apply_results_flag <> 'N' and p_operation_mode in ('SE','BE')) or
3972 (p_collective_agreement_set_id is not null)
3973 then
3974 per_cagr_utility_pkg.log_and_raise_error('HR_289708_UNEXPECTED_PARAM',p_cagr_request_id);
3975 end if;
3976
3977
3978 --
3979 -- populate the parameter record structure
3980 --
3981 p_params.effective_date := trunc(p_process_date);
3982 p_params.operation_mode := p_operation_mode;
3983 p_params.business_group_id := p_business_group_id;
3984 p_params.assignment_id := p_assignment_id;
3985 p_params.assignment_set_id := p_assignment_set_id;
3986 p_params.collective_agreement_id := p_collective_agreement_id;
3987 p_params.cagr_set_id := p_collective_agreement_set_id;
3988 p_params.cagr_request_id := p_cagr_request_id;
3989 p_params.payroll_id := p_payroll_id;
3990 p_params.person_id := p_person_id;
3991 p_params.entitlement_item_id := p_entitlement_item_id;
3992
3993 p_params.commit_flag := p_commit_flag;
3994 p_params.denormalise_flag := p_apply_results_flag;
3995
3996
3997 --
3998 -- Output parameter values to log
3999 --
4000 per_cagr_utility_pkg.put_log(g_head_separator,1);
4001 per_cagr_utility_pkg.put_log(' ',1);
4002 per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
4003 per_cagr_utility_pkg.put_log(' ',1);
4004 if p_params.operation_mode = 'SA' then
4005 per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
4006 elsif p_params.operation_mode = 'SE' then
4007 per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
4008 elsif p_params.operation_mode = 'BE' then
4009 per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
4010 elsif p_params.operation_mode = 'SC' then
4011 per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
4012 end if;
4013 per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4014 per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015 per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016 per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017 per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018 per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019 per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020 per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021 per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4022 per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4023 if p_params.denormalise_flag = 'Y' then
4024 per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
4025 elsif p_params.denormalise_flag = 'N' then
4026 per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
4027 end if;
4028 if p_params.commit_flag = 'Y' then
4029 per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
4030 elsif p_params.commit_flag = 'N' then
4031 per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
4032 end if;
4033 per_cagr_utility_pkg.put_log(' ',1);
4034
4035 -- ****** This needs to be converted to a parameter passed to create_request,
4036 -- rather than relying on a public package variable *******
4037
4038 if fnd_global.conc_request_id <> -1 then
4039 per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
4040 else
4041 per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
4042 end if;
4043 per_cagr_utility_pkg.put_log(' ');
4044 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4045
4046 --
4047 -- invoke evaluation processing;
4048 --
4049 per_cagr_evaluation_pkg.evaluation_process(p_params => p_params
4050 ,p_SE_rec => g_output_structure);
4051
4052 --
4053 -- populate eligible results to HRMS dependent upon mode and param
4054 --
4055 if p_params.operation_mode in ('SA','SC') and p_params.denormalise_flag = 'Y' then
4056 per_cagr_apply_results_pkg.initialise(p_params);
4057 end if;
4058
4059 --
4060 -- Commit, if required.
4061 --
4062 if p_params.commit_flag = 'Y' then
4063 per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
4064 commit;
4065 elsif p_params.commit_flag = 'N' then
4066 per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
4067 rollback;
4068 end if;
4069
4070 -- complete logging
4071 per_cagr_utility_pkg.put_log(g_separator,1);
4072 per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4073
4074 hr_utility.set_location('Leaving:'||l_proc, 50);
4075
4076 END initialise;
4077
4078
4079 -- ================================================================================================
4080 -- == **************** new_entitlement ***************** ==
4081 -- ================================================================================================
4082
4083 FUNCTION new_entitlement (p_ent_id IN NUMBER) RETURN VARCHAR2 IS
4084
4085 -- Accept cagr_entitlement_item_id. Loop through global pl/sql table containing the entitlement_item_ids
4086 -- processed by the main block so far. Returns TRUE if the entitlement exists otherwise FALSE.
4087 -- This routine is called by add_other_ret_rights, to ensure that retained rights for
4088 -- entitlement_items that have already been processed are not duplicated
4089
4090 l_found BOOLEAN := FALSE;
4091
4092 BEGIN
4093
4094 IF g_entitlement_items.count <> 0 then
4095 FOR i IN g_entitlement_items.first..g_entitlement_items.last LOOP
4096 IF g_entitlement_items(i) = p_ent_id THEN
4097 l_found := TRUE;
4098 EXIT;
4099 END IF;
4100 END LOOP;
4101 END IF;
4102
4103 IF l_found THEN
4104 Return 'Y';
4105 ELSE
4106 Return 'N';
4107 END IF;
4108
4109 END new_entitlement;
4110
4111
4112
4113 END per_cagr_evaluation_pkg;
[Home] [Help]