[Home] [Help]
PACKAGE BODY: APPS.PER_CAGR_UTILITY_PKG
Source
1 PACKAGE BODY per_cagr_utility_pkg AS
2 /* $Header: pecgrutl.pkb 120.0 2005/05/31 06:40:05 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Package Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8
9 TYPE cagr_log_text_table IS TABLE OF varchar2(2000)
10 INDEX BY BINARY_INTEGER;
11
12 TYPE cagr_log_priority_table IS TABLE OF number(9)
13 INDEX BY BINARY_INTEGER;
14 --
15 -- ----------------------------------------------------------------------------
16 -- | Package Variables (globals) |
17 -- ----------------------------------------------------------------------------
18 --
19 g_log_text_table cagr_log_text_table;
20 g_log_priority_table cagr_log_priority_table;
21 g_pkg constant varchar2(25) := 'PER_CAGR_UTILITY_PKG.';
22 g_log_counter NUMBER := 0;
23 --
24 -- ----------------------------------------------------------------------------
25 -- |--------------------< convert_uom_to_data_type >--------------------------|
26 -- ----------------------------------------------------------------------------
27 --
28 FUNCTION convert_uom_to_data_type
29 (p_uom IN per_cagr_entitlement_items.uom%TYPE) RETURN CHAR IS
30 --
31 -- Delcare Local Variables
32 --
33 l_proc VARCHAR2(72) := g_pkg||'convert_uom_to_data_type';
34 l_data_type per_cagr_entitlement_items.column_type%TYPE;
35 --
36 BEGIN
37 --
38 hr_utility.set_location('Entering '||l_proc||'/'||p_uom,10);
39 --
40 IF p_uom = 'C' THEN
41 --
42 hr_utility.set_location(l_proc,20);
43 --
44 l_data_type := 'VAR';
45 --
46 ELSIF p_uom IN ('H_DECIMAL1','H_DECIMAL2','H_DECIMAL3'
47 ,'H_HH','I','M','N','ND') THEN
48 --
49 hr_utility.set_location(l_proc,30);
50 --
51 l_data_type := 'NUM';
52 --
53 ELSIF p_uom IN ('D','H_HHMM','H_HHMMSS','T') THEN
54 --
55 hr_utility.set_location(l_proc,40);
56 --
57 l_data_type := 'DATE';
58 --
59 ELSE
60 --
61 hr_utility.set_location(l_proc,50);
62 --
63 l_data_type := 'VAR';
64 --
65 END IF;
66 --
67 hr_utility.set_location('Leaving '||l_proc,999);
68 --
69 RETURN(l_data_type);
70 --
71 END convert_uom_to_data_type;
72 --
73 -- ----------------------------------------------------------------------------
74 -- |---------------------------< chk_sql_statement >--------------------------|
75 -- ----------------------------------------------------------------------------
76 --
77 PROCEDURE chk_sql_statement(p_sql_statement IN VARCHAR2) IS
78 --
79 -- Delcare Local Variables
80 --
81 l_proc VARCHAR2(72) := g_pkg||'chk_sql_statement';
82 l_value_id VARCHAR2(200);
83 l_name VARCHAR2(2000);
84 --
85 BEGIN
86 --
87 hr_utility.set_location('Entering '||l_proc,10);
88 --
89 EXECUTE IMMEDIATE p_sql_statement INTO l_value_id, l_name;
90 --
91 hr_utility.set_location('Leaving '||l_proc,997);
92 --
93 EXCEPTION
94 --
95 WHEN TOO_MANY_ROWS THEN
96 --
97 hr_utility.set_location('Leaving '||l_proc,998);
98 --
99 WHEN NO_DATA_FOUND THEN
100 --
101 -- If no data was found then ignore this message
102 --
103 NULL;
104 --
105 WHEN OTHERS THEN
106 --
107 hr_utility.set_location(l_proc||substr(sqlerrm,1,50),999);
108 hr_utility.set_message(800,'HR_289399_INVALID_VALUE_SET');
109 hr_utility.raise_error;
110 --
111 END;
112 --
113 -- ----------------------------------------------------------------------------
114 -- |----------------------------< get_elig_source >---------------------------|
115 -- ----------------------------------------------------------------------------
116 --
117 FUNCTION get_elig_source(p_eligy_prfl_id in NUMBER
118 ,p_formula_id in NUMBER
119 ,p_effective_date in DATE) return VARCHAR2 is
120 --
121 -- Returns the name of the eligibility profile for the criteria line
122 -- or the name of the ff for the entitlement
123 --
124
125 CURSOR csr_elig IS
126 select name
127 from ben_eligy_prfl_f
128 where ELIGY_PRFL_ID = p_eligy_prfl_id
129 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
130
131 CURSOR csr_ff IS
132 select formula_name
133 from FF_FORMULAS_F
134 where FORMULA_ID = p_formula_id
135 and p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
136
137 l_proc VARCHAR2(72) := g_pkg||'get_elig_source';
138 l_name VARCHAR2(200);
139 --
140 BEGIN
141 --
142 hr_utility.set_location('Entering '||l_proc,10);
143 --
144 if p_eligy_prfl_id is not null then
145 open csr_elig;
146 fetch csr_elig into l_name;
147 close csr_elig;
148 elsif p_formula_id is not null then
149 open csr_ff;
150 fetch csr_ff into l_name;
151 close csr_ff;
152 end if;
153 --
154 hr_utility.set_location('Leaving '||l_proc,50);
155 RETURN l_name;
156 --
157 END get_elig_source;
158
159 --
160 -- ----------------------------------------------------------------------------
161 -- |----------------------< multiple_entries_allowed >------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 FUNCTION multiple_entries_allowed
165 (p_element_type_id IN pay_element_types_f.element_type_id%TYPE
166 ,p_effective_date IN DATE) RETURN BOOLEAN IS
167 --
168 -- Delcare Local Variables
169 --
170 l_proc VARCHAR2(72) := g_pkg||'mulitple_entries_allowed';
171 l_return_value BOOLEAN;
172 l_flag VARCHAR2(2);
173 --
174 CURSOR csr_multiple_entries_allowed IS
175 SELECT 'X'
176 FROM pay_element_types_f p
177 WHERE p.element_type_id = p_element_type_id
178 AND p.multiple_entries_allowed_flag = 'Y'
179 AND p_effective_date BETWEEN p.effective_start_date
180 AND p.effective_end_date;
181 BEGIN
182 --
183 hr_utility.set_location('Entering '||l_proc,10);
184 --
185 OPEN csr_multiple_entries_allowed;
186 FETCH csr_multiple_entries_allowed INTO l_flag;
187 --
188 IF csr_multiple_entries_allowed%NOTFOUND THEN
189 --
190 hr_utility.set_location(l_proc,20);
191 --
192 CLOSE csr_multiple_entries_allowed;
193 --
194 l_return_value := FALSE;
195 --
196 ELSIF csr_multiple_entries_allowed%FOUND THEN
197 --
198 hr_utility.set_location(l_proc,30);
199 --
200 CLOSE csr_multiple_entries_allowed;
201 --
202 l_return_value := TRUE;
203 --
204 END IF;
205 --
206 hr_utility.set_location('Leaving '||l_proc,999);
207 --
208 RETURN(l_return_value);
209 --
210 END multiple_entries_allowed;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |------------------------< get_cagr_request_id >---------------------------|
214 -- ----------------------------------------------------------------------------
215 --
216 FUNCTION get_cagr_request_id (p_assignment_id in NUMBER
217 ,p_effective_date in DATE) RETURN NUMBER IS
218 --
219 -- Attemnpts to return the latest cagr_request_id for an assignment on or before
220 -- the effective date so that the user may view logs that relate to the run
221 -- which failed to return results. As there could be multiple requests generated
222 -- on a particular date this function returns the id of the latest request,
223 -- which represents the most recent run on or before the session date.
224 -- Called from PERWSCAR.fmb View_Log.
225 -- Note: The norm is that an CAGR evaluation run produces a result, giving
226 -- a handle on the cagr_request_id. When the user taskflows to the form,
227 -- when there are no results, then if they do not wish
228 -- to refresh the results to get the new cagr_request_id returned to the
229 -- results window, (even if no results), this function
230 -- may be used to get the request_id of the previous run that failed to
231 -- produce entitlement results, so that the log can still be displayed.
232 --
233
234 l_effective_date DATE := NULL;
235
236 CURSOR csr_requests IS
237 select max(cr.cagr_request_id)
238 from per_cagr_requests cr
239 where cr.assignment_id = p_assignment_id
240 and cr.process_date = (select max(cr1.process_date)
241 from per_cagr_requests cr1
242 where cr1.assignment_id = p_assignment_id
243 and l_effective_date >= cr1.process_date);
244
245 l_proc VARCHAR2(72) := g_pkg||'get_cagr_request_id';
246 l_request_id NUMBER(20) := NULL;
247
248 --
249 BEGIN
250 --
251 hr_utility.set_location('Entering:'|| l_proc, 10);
252
253 --
254 If p_assignment_id is not null and p_effective_date is not null then
255 --
256 l_effective_date := trunc(p_effective_date);
257 --
258 OPEN csr_requests;
259 FETCH csr_requests INTO l_request_id;
260 CLOSE csr_requests;
261 End If;
262 --
263 hr_utility.set_location('Leaving :'|| l_proc, 20);
264 RETURN l_request_id;
265 --
266 END get_cagr_request_id;
267
268 --
269 -- ----------------------------------------------------------------------------
270 -- |---------------------< set_mode_from_node_name >---------------------------|
271 -- ----------------------------------------------------------------------------
272 --
273 FUNCTION set_mode_from_node_name (p_nav_node_usage_id in NUMBER) RETURN VARCHAR2 IS
274 --
275 -- Returns the mode the form should run in based on node name
276 -- (as taskflow doesn't support additional form parameters).
277 --
278 CURSOR csr_nodes IS
279 select n.name
280 from hr_navigation_nodes n, hr_navigation_node_usages nu
281 where n.NAV_NODE_ID = nu.NAV_NODE_ID
282 and nu.NAV_NODE_USAGE_ID = p_nav_node_usage_id;
283
284 --
285 l_proc VARCHAR2(72) := g_pkg||'set_mode_from_node_name';
286 l_name hr_navigation_nodes.name%TYPE;
287 l_return VARCHAR2(20);
288 --
289 BEGIN
290 --
291 hr_utility.set_location('Entering:'|| l_proc, 10);
292 --
293 OPEN csr_nodes;
294 FETCH csr_nodes INTO l_name;
295 CLOSE csr_nodes;
296 --
297 IF l_name = 'PERWSCAR' then
298 l_return := 'NORMAL';
299 elsif l_name = 'PERWSCAR_RETAINED' then
300 l_return := 'RETAINED';
301 end if;
302 --
303 hr_utility.set_location('Leaving :'|| l_proc, 999);
304 --
305 RETURN (l_return);
306 --
307 END set_mode_from_node_name;
308
309 --
310 -- ----------------------------------------------------------------------------
311 -- |------------------------------< plan_name >-------------------------------|
312 -- ----------------------------------------------------------------------------
313 --
314 FUNCTION plan_name RETURN VARCHAR2 IS
315 --
316 -- Declare Sequence Number
317 --
318 CURSOR csr_seq_number IS
319 SELECT ben_pl_f_s.NEXTVAL
320 FROM sys.dual;
321 --
322 -- Declare Local Variables
323 --
324 l_proc VARCHAR2(72) := g_pkg||'plan_name';
325 l_seq_number NUMBER;
326 l_plan_name ben_pl_f.name%TYPE;
327 --
328 BEGIN
329 --
330 hr_utility.set_location('Entering:'|| l_proc, 10);
331 --
332 OPEN csr_seq_number;
333 FETCH csr_seq_number INTO l_seq_number;
334 --
335 CLOSE csr_seq_number;
336 --
337 l_plan_name := 'CAGR_PLAN_'||l_seq_number;
338 --
339 hr_utility.set_location('Leaving :'|| l_proc, 999);
340 --
341 RETURN (l_plan_name);
342 --
343 END plan_name;
344 --
345 -- ----------------------------------------------------------------------------
346 -- |------------------------------< option_name >-----------------------------|
347 -- ----------------------------------------------------------------------------
348 --
349 FUNCTION option_name RETURN VARCHAR IS
350 --
351 -- Declare Cursors
352 --
353 CURSOR csr_next_sequence IS
354 SELECT ben_opt_f_s.NEXTVAL
355 FROM sys.dual;
356 --
357 -- Declare Local Variables
358 --
359 l_proc VARCHAR2(72) := g_pkg||'option_name';
360 l_seq_number NUMBER;
361 l_option_name ben_opt_f.name%TYPE;
362 --
363 BEGIN
364 --
365 hr_utility.set_location('Entering:'|| l_proc, 10);
366 --
367 OPEN csr_next_sequence;
368 FETCH csr_next_sequence INTO l_seq_number;
369 --
370 CLOSE csr_next_sequence;
371 --
372 l_option_name := 'CAGR_OPTION'||l_seq_number;
373 --
374 hr_utility.set_location('Leaving :'|| l_proc, 999);
375 --
376 RETURN(l_option_name);
377 --
378 END option_name;
379 --
380 -- ----------------------------------------------------------------------------
381 -- |--------------------------< get_next_order_number >-----------------------|
382 -- ----------------------------------------------------------------------------
383 --
384 FUNCTION get_next_order_number(p_pl_id IN ben_oipl_f.pl_id%TYPE) RETURN NUMBER IS
385 --
386 -- Declare Cursors
387 --
388 CURSOR csr_order_number IS
389 SELECT MAX(b.ordr_num)+10
390 FROM ben_oipl_f b
391 WHERE b.pl_id = p_pl_id;
392 --
393 -- Declare Local Variables
394 --
395 l_proc VARCHAR2(72) := g_pkg||'get_order_number';
396 l_order_number ben_oipl_f.ordr_num%TYPE;
397 --
398 BEGIN
399 --
400 hr_utility.set_location('Entering:'|| l_proc, 10);
401 --
402 OPEN csr_order_number;
403 FETCH csr_order_number INTO l_order_number;
404 --
405 CLOSE csr_order_number;
406 --
407 -- If l_order_number is null because its
408 -- the first opition in a plan to be created
409 -- for the plan then set it to 10
410 --
411 IF l_order_number IS NULL THEN
412 --
413 l_order_number := 10;
414 --
415 END IF;
416 --
417 hr_utility.set_location('Leaving:'|| l_proc, 999);
418 --
419 RETURN(l_order_number);
420 --
421 EXCEPTION
422 WHEN OTHERS THEN
423 --
424 fnd_message.set_name('PER', 'HR_289334_GET_ORDR_NUM_ERROR');
425 fnd_message.raise_error;
426 --
427 END get_next_order_number;
428 --
429 -- ----------------------------------------------------------------------------
430 -- |----------------------------< get_column_type >---------------------------|
431 -- ----------------------------------------------------------------------------
432 --
433 FUNCTION get_column_type
434 (p_cagr_entitlement_item_id IN NUMBER
435 ,p_effective_date IN DATE) RETURN VARCHAR2 IS
436 --
437 -- Delcare Local Variables
438 --
439 l_return_value per_cagr_entitlement_items.column_type%TYPE;
440 l_proc VARCHAR2(72) := g_pkg||'get_column_type';
441 l_item_category per_cagr_entitlement_items.category_name%TYPE;
442 l_input_value_id pay_input_values.input_value_id%TYPE;
443 l_uom per_cagr_entitlement_items.uom%TYPE;
444 l_column_type per_cagr_entitlement_items.column_type%TYPE;
445 l_uom_lookup per_cagr_api_parameters.uom_lookup%TYPE;
446 --
447 CURSOR csr_get_uom_details IS
448 SELECT cei.uom,
449 cei.column_type,
450 p.uom_lookup
451 FROM per_cagr_entitlement_items cei,
452 per_cagr_api_parameters p
453 WHERE cei.cagr_entitlement_item_id = p_cagr_entitlement_item_id
454 AND p.cagr_api_param_id (+) = cei.cagr_api_param_id;
455 --
456 CURSOR c_item_category IS
457 SELECT pce.category_name,
458 pce.input_value_id
459 FROM per_cagr_entitlement_items pce
460 WHERE pce.cagr_entitlement_item_id = p_cagr_entitlement_item_id;
461 --
462 CURSOR c_pay_item_type IS
463 SELECT piv.uom
464 FROM pay_input_values_f piv
465 WHERE piv.input_value_id = l_input_value_id
466 AND p_effective_date BETWEEN piv.effective_start_date
467 AND piv.effective_end_date;
468 --
469 CURSOR c_item_type IS
470 SELECT pce.column_type
471 FROM per_cagr_entitlement_items pce
472 WHERE pce.cagr_entitlement_item_id = p_cagr_entitlement_item_id;
473 --
474 BEGIN
475 --
476 hr_utility.set_location('Entering:'|| l_proc, 10);
477 --
478 OPEN csr_get_uom_details;
479 FETCH csr_get_uom_details INTO l_uom,l_column_type,l_uom_lookup;
480 CLOSE csr_get_uom_details;
481 --
482 IF l_uom IS NULL AND l_uom_lookup IS NULL THEN
483 --
484 hr_utility.set_location(l_proc, 20);
485 --
486 l_return_value := l_column_type;
487 --
488 ELSIF l_uom IS NOT NULL AND
489 (l_uom_lookup IS NULL OR
490 l_uom_lookup = 'UNITS') THEN
491 --
492 hr_utility.set_location(l_proc, 30);
493 --
494 l_return_value := l_uom;
495 --
496 ELSE
497 --
498 hr_utility.set_location(l_proc, 40);
499 --
500 l_return_value := l_column_type;
501 --
502 END IF;
503 --
504 /*
505 OPEN c_item_category;
506 FETCH c_item_category INTO l_item_category, l_input_value_id;
507 --
508 IF c_item_category%NOTFOUND THEN
509 --
510 CLOSE c_item_category;
511 --
512 fnd_message.set_name('PER', 'HR_289329_ITEM_TYPE_ERROR');
513 fnd_message.raise_error;
514 --
515 END IF;
516 --
517 hr_utility.set_location(l_proc, 20);
518 --
519 IF l_item_category = 'PAY' THEN
520 --
521 hr_utility.set_location(l_proc, 30);
522 --
523 OPEN c_pay_item_type;
524 FETCH c_pay_item_type INTO l_column_type;
525 CLOSE c_pay_item_type;
526 --
527 ELSE
528 --
529 hr_utility.set_location(l_proc, 40);
530 --
531 OPEN c_item_type;
532 FETCH c_item_type INTO l_column_type;
533 --
534 IF c_item_type%FOUND THEN
535 --
536 hr_utility.set_location(l_proc, 50);
537 --
538 CLOSE c_item_type;
539 --
540 ELSE
541 --
542 hr_utility.set_location(l_proc, 60);
543 --
544 CLOSE c_item_type;
545 --
546 -- There has been an error in retrieving the
547 -- column type therefore we must error
548 --
549 fnd_message.set_name('PER', 'HR_289329_ITEM_TYPE_ERROR');
550 fnd_message.raise_error;
551 --
552 END IF;
553 --
554 END IF;
555 --
556 */
557 hr_utility.set_location('Leaving:'|| l_proc, 99);
558 --
559 RETURN(l_return_value);
560 --
561 END get_column_type;
562 --
563 -- ----------------------------------------------------------------------------
564 -- |--------------------------< get_sql_from_vset_id >------------------------|
565 -- ----------------------------------------------------------------------------
566 --
567 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER) RETURN VARCHAR2 IS
568 --
569 l_v_r fnd_vset.valueset_r;
570 l_v_dr fnd_vset.valueset_dr;
571 l_str varchar2(4000);
572 l_whr varchar2(4000);
573 l_ord varchar2(4000);
574 l_col varchar2(4000);
575 --
576 BEGIN
577 --
578 fnd_vset.get_valueset(valueset_id => p_vset_id ,
579 valueset => l_v_r,
580 format => l_v_dr);
581 --
582 IF l_v_r.table_info.table_name IS NULL THEN
583 --
584 l_str := '';
585 --
586 END IF;
587 --
588 IF l_v_r.table_info.id_column_name IS NULL THEN
589 --
590 l_str := '';
591 --
592 END IF;
593 --
594 IF l_v_r.table_info.value_column_name IS NULL THEN
595 --
596 l_str := '';
597 --
598 END IF;
599 --
600 l_whr := l_v_r.table_info.where_clause ;
601 l_str := 'select '||substr(l_v_r.table_info.id_column_name,1,instr(l_v_r.table_info.id_column_name||' ',' '))||','
602 ||substr(l_v_r.table_info.value_column_name,1,instr(l_v_r.table_info.value_column_name||' ',' '))
603 ||' from '
604 ||l_v_r.table_info.table_name||' '||l_whr;
605 --
606 RETURN (l_str);
607 --
608 END get_sql_from_vset_id;
609 --
610 -- ----------------------------------------------------------------------------
611 -- |-----------------------< get_name_from_value_set >------------------------|
612 -- ----------------------------------------------------------------------------
613 --
614 FUNCTION get_name_from_value_set
615 (p_flex_value_set_id IN NUMBER
616 ,p_business_group_id IN NUMBER
617 ,p_value IN CHAR) RETURN VARCHAR2 IS
618 --
619 -- Overload of get_name_from_value_set to accept vs_id and bg and do less work!
620 -- Called from per_cagr_entitlement_results_v view
621 --
622 -- Delcare Local Variables
623 --
624 l_sql_statement VARCHAR2(2000);
625 l_value_id VARCHAR2(10);
626 l_id_column VARCHAR2(200);
627 l_name VARCHAR2(2000);
628 l_proc varchar2(90) := g_pkg||'get_name_from_value_set overload';
629 --
630 BEGIN
631 --
632 hr_utility.set_location('Entering:'|| l_proc, 10);
633 --
634 --
635 IF p_flex_value_set_id IS NOT NULL and p_business_group_id is NOT NULL and p_value IS NOT NULL THEN
636 --
637 hr_utility.set_location(l_proc, 30);
638 --
639 l_sql_statement := get_sql_from_vset_id(p_vset_id => p_flex_value_set_id);
640 --
641 l_sql_statement := REPLACE(l_sql_statement
642 ,':$PROFILES$.PER_BUSINESS_GROUP_ID'
643 ,p_business_group_id);
644 --
645 l_id_column := SUBSTR(l_sql_statement,(INSTR(UPPER(l_sql_statement),'SELECT') +7)
646 ,INSTR(UPPER(l_sql_statement),',') -
647 (INSTR(UPPER(l_sql_statement),'SELECT')+ 7));
648
649 l_sql_statement := l_sql_statement||' and '||l_id_column||' = :id';
650 --
651 hr_utility.set_location(l_proc, 40);
652 --
653 BEGIN
654 --
655 EXECUTE IMMEDIATE l_sql_statement INTO l_value_id, l_name USING p_value;
656 --
657 EXCEPTION
658 --
659 WHEN OTHERS THEN
660 hr_utility.set_message(800,'HR_289399_INVALID_VALUE_SET');
661 hr_utility.raise_error;
662 --
663 END;
664 hr_utility.set_location(l_proc, 50);
665 --
666 END IF;
667 --
668 hr_utility.set_location('Leaving :'||l_proc, 60);
669 --
670 RETURN(l_name);
671 --
672 END get_name_from_value_set;
673 --
674 -- ----------------------------------------------------------------------------
675 -- |--------------- -------< get_name_from_value_set >------------------------|
676 -- ----------------------------------------------------------------------------
677 --
678 FUNCTION get_name_from_value_set
679 (p_cagr_entitlement_id IN NUMBER
680 ,p_value IN CHAR) RETURN VARCHAR2 IS
681 --
682 -- Declare Cursors
683 --
684 CURSOR csr_entitlement_details IS
685 SELECT pci.flex_value_set_id,
686 pci.business_group_id
687 FROM per_cagr_entitlements pce,
688 per_cagr_entitlement_items pci
689 WHERE pci.cagr_entitlement_item_id = pce.cagr_entitlement_item_id
690 AND pce.cagr_entitlement_id = p_cagr_entitlement_id;
691 --
692 -- Delcare Local Variables
693 --
694 l_flex_value_set_id per_cagr_entitlement_items.flex_value_set_id%TYPE;
695 l_business_group_id per_cagr_entitlement_items.business_group_id%TYPE;
696 l_sql_statement VARCHAR2(2000);
697 l_value_id VARCHAR2(10);
698 l_id_column VARCHAR2(200);
699 l_name VARCHAR2(2000);
700 l_proc varchar2(72) := g_pkg||'get_name_from_value_set';
701 --
702 BEGIN
703 --
704 hr_utility.set_location('Entering:'||l_proc||'/'||
705 p_cagr_entitlement_id||'/'||
706 p_value, 10);
707 --
708 --
709 OPEN csr_entitlement_details;
710 FETCH csr_entitlement_details INTO l_flex_value_set_id, l_business_group_id;
711 --
712 hr_utility.set_location(l_proc, 20);
713 --
714 CLOSE csr_entitlement_details;
715 --
716 IF l_flex_value_set_id IS NOT NULL AND p_value IS NOT NULL THEN
717 --
718 hr_utility.set_location(l_proc, 30);
719 --
720 l_sql_statement := get_sql_from_vset_id(p_vset_id => l_flex_value_set_id);
721 --
722 l_sql_statement := REPLACE(l_sql_statement
723 ,':$PROFILES$.PER_BUSINESS_GROUP_ID'
724 ,l_business_group_id);
725 --
726 l_id_column := SUBSTR(l_sql_statement,(INSTR(UPPER(l_sql_statement),'SELECT') +7)
727 ,INSTR(UPPER(l_sql_statement),',') -
728 (INSTR(UPPER(l_sql_statement),'SELECT')+ 7));
729 --
730 l_sql_statement := l_sql_statement||' and '||l_id_column||' = :id';
731 --
732 hr_utility.set_location(l_proc, 40);
733 --
734 BEGIN
735 --
736 EXECUTE IMMEDIATE l_sql_statement INTO l_value_id, l_name USING p_value;
737 --
738 EXCEPTION
739 --
740 WHEN OTHERS THEN
741 hr_utility.set_location(l_proc||substr(sqlerrm,1,50),999);
742 hr_utility.set_message(800,'HR_289399_INVALID_VALUE_SET');
743 hr_utility.raise_error;
744 --
745 END;
746 --
747 hr_utility.set_location(l_proc, 50);
748 --
749 END IF;
750 --
751 hr_utility.set_location('Leaving :'||l_proc, 999);
752 --
753 RETURN(l_name);
754 --
755 END get_name_from_value_set;
756 --
757 -- ----------------------------------------------------------------------------
758 -- |-----------------------< create_cagr_request >-----------------------------|
759 -- ----------------------------------------------------------------------------
760 --
761 PROCEDURE create_cagr_request (p_process_date IN DATE
762 ,p_operation_mode IN VARCHAR2
763 ,p_business_group_id IN NUMBER
764 ,p_assignment_id IN NUMBER
765 ,p_assignment_set_id IN NUMBER
766 ,p_collective_agreement_id IN NUMBER
767 ,p_collective_agreement_set_id IN NUMBER
768 ,p_payroll_id IN NUMBER
769 ,p_person_id IN NUMBER
770 ,p_entitlement_item_id IN NUMBER
771 ,p_parent_request_id IN NUMBER
772 ,p_commit_flag IN VARCHAR2
773 ,p_denormalise_flag IN VARCHAR2
774 ,p_cagr_request_id OUT NOCOPY NUMBER) IS
775 --
776 -- Create a per_cagr_request record and return the id to stripe all result and log
777 -- records by.
778 --
779 pragma autonomous_transaction;
780
781 BEGIN
782 insert into per_cagr_requests (cagr_request_id
783 ,process_date
784 ,operation_mode
785 ,business_group_id
786 ,assignment_id
787 ,assignment_set_id
788 ,collective_agreement_id
789 ,collective_agreement_set_id
790 ,payroll_id
791 ,person_id
792 ,cagr_entitlement_item_id
793 ,parent_request_id
794 ,commit_flag
795 ,denormalise_flag)
796 values (PER_CAGR_REQUESTS_S.nextval
797 ,trunc(p_process_date)
798 ,p_operation_mode
799 ,p_business_group_id
800 ,p_assignment_id
801 ,p_assignment_set_id
802 ,p_collective_agreement_id
803 ,p_collective_agreement_set_id
804 ,p_payroll_id
805 ,p_person_id
806 ,p_entitlement_item_id
807 ,p_parent_request_id
808 ,p_commit_flag
809 ,p_denormalise_flag) RETURNING cagr_request_id
810 INTO p_cagr_request_id;
811
812 commit;
813
814 END create_cagr_request;
815 --
816 -- ----------------------------------------------------------------------------
817 -- |--------------------------------< put_log >-------------------------------|
818 -- ----------------------------------------------------------------------------
819 --
820 PROCEDURE put_log (p_text IN VARCHAR2
821 ,p_priority IN NUMBER default 2) IS
822 --
823 -- Place text in new record within pl/sql log tables
824 --
825
826 BEGIN
827 -- insert new row in the pl/sql log table
828 g_log_text_table(g_log_counter) := p_text;
829 g_log_priority_table(g_log_counter) := p_priority;
830 g_log_counter := g_log_counter + 1;
831
832 END put_log;
833
834 --
835 -- ----------------------------------------------------------------------------
836 -- |---------------------------< write_log_file >-----------------------------|
837 -- ----------------------------------------------------------------------------
838 --
839 PROCEDURE write_log_file (p_cagr_request_id IN NUMBER)IS
840
841 -- Writes text held in pl/sql log table to PER_CAGR_LOG table,
842 -- and also to host file system via FND_FILE, if run from SRS.
843
844 l_proc constant varchar2(61) := g_pkg || '.write_log_file';
845
846 --
847 -- |------------------------------< write_log >-------------------------------|
848 --
849
850 PROCEDURE write_log (p_cagr_request_id in NUMBER) IS
851 --
852 -- Writes text held in pl/sql log table to PER_CAGR_LOG table, AUTONOMOUSLY.
853 --
854 pragma autonomous_transaction;
855
856 BEGIN
857 -- bulk bind pl/sql table to PER_CAGR_LOG.
858 forall l_count in g_log_text_table.first .. g_log_text_table.last
859 insert into PER_CAGR_LOG
860 (LOG_ID
861 ,CAGR_REQUEST_ID
862 ,TEXT
863 ,PRIORITY)
864 values
865 (PER_CAGR_LOG_S.nextval
866 ,p_cagr_request_id
867 ,g_log_text_table(l_count)
868 ,g_log_priority_table(l_count));
869
870 COMMIT;
871
872 END write_log;
873
874 BEGIN -- Write_Log_File
875
876 hr_utility.set_location('Entering:'||l_proc, 10);
877
878 -- always populate the cagr log table
879 if g_log_text_table.count > 0 then
880 write_log(p_cagr_request_id);
881
882 hr_utility.set_location(l_proc, 20);
883
884 if fnd_global.conc_request_id <> -1 then
885 -- log is additionally written out via FND_FILE,
886 -- for visibility from view SRS window.
887
888 for i in g_log_text_table.first .. g_log_text_table.last loop
889 fnd_file.put_line(which => fnd_file.log, -- Bug 2719987
890 buff => g_log_text_table(i));
891 end loop;
892 end if;
893
894 -- remove log entries
895 g_log_text_table.delete;
896 g_log_priority_table.delete;
897 end if;
898 hr_utility.set_location('Leaving:'||l_proc, 30);
899
900 END write_log_file;
901
902 --
903 -- ----------------------------------------------------------------------------
904 -- |---------------------< log_and_raise_error >----------------------|
905 -- ----------------------------------------------------------------------------
906 --
907 PROCEDURE log_and_raise_error (p_error IN VARCHAR2
908 ,p_cagr_request_id IN NUMBER) IS
909 --
910 -- Accept an error code, log the error message, and raise the error to the
911 -- calling code.
912 --
913 -- Used for errors which should be both logged in per_cagr_log table and
914 -- raised to calling APPS code, via fnd_message.
915 --
916 BEGIN
917 --
918 fnd_message.set_name('PER',p_error);
919 put_log(fnd_message.get,1);
920 write_log_file(p_cagr_request_id);
921 fnd_message.raise_error;
922 --
923 END log_and_raise_error;
924
925 --
926 -- ----------------------------------------------------------------------------
927 -- |---------------------< create_formatted_log_file >----------------------|
928 -- ----------------------------------------------------------------------------
929 --
930 PROCEDURE create_formatted_log_file (p_cagr_request_id IN NUMBER
931 ,p_filepath OUT NOCOPY VARCHAR2) IS
932 --
933 -- Accept cagr_request_id to query entries from per_cagr_log table and write a
934 -- log file to the file system. Log must have been written in SA mode, so updates
935 -- from SE, BE mode are not visible. (SC runs dummys as SA)
936 --
937 -- Used to create a file that may be viewed in FNDCPVWR.fmb (which is called
938 -- from PERWSCAR.fmb)
939
940 TYPE logTab IS TABLE OF per_cagr_log.text%TYPE INDEX BY BINARY_INTEGER;
941
942 CURSOR csr_filepath IS
943 SELECT decode(substr(value,1,INSTR(value,',')-1),
944 NULL, value, substr(value,1,INSTR(value,',')-1)) "filepath"
945 FROM v$parameter
946 WHERE name = 'utl_file_dir';
947
948 CURSOR csr_log(l_level in NUMBER) IS
949 SELECT text
950 FROM per_cagr_log
951 WHERE cagr_request_id = p_cagr_request_id
952 AND priority <= l_level
953 AND exists (select 'X' from per_cagr_requests req
954 where req.cagr_request_id = p_cagr_request_id
955 and req.operation_mode = 'SA')
956 ORDER BY log_id;
957
958 l_proc constant varchar2(60) := g_pkg || '.create_formatted_log_file';
959
960 l_cagr_log_table logTab;
961 l_filepath varchar2(255);
962 l_name varchar2(20);
963 l_log_detail varchar2(30);
964 l_fileh utl_file.file_type;
965
966 BEGIN
967 --
968 hr_utility.set_location('Entering:'||l_proc, 10);
969 --
970 open csr_filepath;
971 fetch csr_filepath into l_filepath;
972 if csr_filepath%notfound then
973 close csr_filepath;
974 fnd_message.set_name('PER', 'HR_289832_CAGR_NO_FILEPATH');
975 fnd_message.raise_error;
976 end if;
977 close csr_filepath;
978
979 l_name := p_cagr_request_id||'.txt';
980
981 begin
982
983 hr_utility.set_location(l_proc, 10);
984 --
985 -- bug 2461389, raise error if filepath is not set
986 -- we have not been able to set a path, so raise an error
987 -- path is either full value or first string before a comma
988 if l_filepath is null then
989 fnd_message.set_name('PER', 'HR_289832_CAGR_NO_FILEPATH');
990 fnd_message.raise_error;
991 end if;
992 --
993 -- check if the file alrteady exists, else
994 -- attempt to open a file for write
995 l_fileh := utl_file.fopen(l_filepath,l_name,'w');
996 hr_utility.set_location(l_proc, 20);
997
998 -- get log entries restricted to certain levels
999 l_log_detail := fnd_profile.value('PER_CAGR_LOG_DETAIL');
1000 if nvl(l_log_detail,'H') = 'H' then
1001 -- default to showing everything
1002 -- when profile not set.
1003 open csr_log(2);
1004 else
1005 open csr_log(1);
1006 end if;
1007 fetch csr_log BULK COLLECT into l_cagr_log_table;
1008 close csr_log;
1009 If l_cagr_log_table.count > 0 then
1010 for i in l_cagr_log_table.first .. l_cagr_log_table.last loop
1011 utl_file.put_line(l_fileh,l_cagr_log_table(i));
1012 end loop;
1013 end if;
1014
1015 -- close file
1016 utl_file.fclose(l_fileh);
1017
1018 exception
1019 when UTL_FILE.INVALID_PATH then
1020 fnd_message.set_name('PER', 'HR_289832_CAGR_NO_FILEPATH');
1021 fnd_message.raise_error;
1022 when UTL_FILE.WRITE_ERROR then
1023 fnd_message.set_name('PER', 'HR_289833_CAGR_FILE_WRITE_ERR');
1024 fnd_message.raise_error;
1025 end;
1026
1027 -- id the directory separator?
1028
1029 -- pass back the concatenated path and file name
1030 p_filepath := l_filepath||'/'||l_name;
1031
1032 --
1033 hr_utility.set_location('Leaving:'||l_proc, 40);
1034
1035 --
1036 END create_formatted_log_file;
1037
1038 --
1039 -- ----------------------------------------------------------------------------
1040 -- |-------------------------< remove_log_entries >---------------------------|
1041 -- ----------------------------------------------------------------------------
1042 --
1043 PROCEDURE remove_log_entries (p_cagr_request_id IN NUMBER) IS
1044 --
1045 -- Accept cagr_request_id, if there are no more results existing for the request_id
1046 -- then delete all records in the per_cagr_log table for that request_id.
1047 --
1048 -- Called from the engine after removing a result set for a request_id.
1049 --
1050
1051 CURSOR csr_more_results IS
1052 SELECT 'X'
1053 FROM per_cagr_entitlement_results
1054 WHERE cagr_request_id = p_cagr_request_id
1055 AND rownum = 1;
1056
1057 l_proc constant varchar2(60) := g_pkg || '.remove_log_entries';
1058 l_dummy varchar2(1);
1059
1060 BEGIN
1061 hr_utility.set_location('Entering:'||l_proc, 10);
1062
1063 open csr_more_results;
1064 fetch csr_more_results into l_dummy;
1065 if csr_more_results%notfound then
1066 close csr_more_results;
1067 begin
1068 hr_utility.set_location(l_proc, 20);
1069
1070 DELETE FROM per_cagr_log
1071 WHERE cagr_request_id = p_cagr_request_id;
1072
1073 per_cagr_utility_pkg.put_log(' last result deleted for cagr_request_id '
1074 ||p_cagr_request_id||', log entries deleted');
1075
1076 exception
1077 when no_data_found then
1078 null;
1079 end;
1080 else
1081 close csr_more_results;
1082 end if;
1083
1084
1085 hr_utility.set_location('Leaving:'||l_proc, 30);
1086
1087 END remove_log_entries;
1088
1089
1090 --
1091 -- ----------------------------------------------------------------------------
1092 -- |---------------------< get_collective_agreement_id >----------------------|
1093 -- ----------------------------------------------------------------------------
1094 --
1095
1096 FUNCTION get_collective_agreement_id(p_assignment_id IN NUMBER
1097 ,p_effective_date IN DATE) RETURN NUMBER IS
1098
1099 CURSOR csr_get_cagr is
1100 select collective_agreement_id
1101 from per_all_assignments_f
1102 where assignment_id = p_assignment_id
1103 and p_effective_date between effective_start_date
1104 and nvl(effective_end_date,hr_general.end_of_time);
1105
1106 l_id per_all_assignments_f.collective_agreement_id%TYPE;
1107 BEGIN
1108
1109 open csr_get_cagr;
1110 fetch csr_get_cagr into l_id;
1111 close csr_get_cagr;
1112
1113 return l_id;
1114
1115 END get_collective_agreement_id;
1116 -- ----------------------------------------------------------------------------
1117 -- |---------------------< populate_current_asg >-----------------------------|
1118 -- ----------------------------------------------------------------------------
1119 --
1120 procedure populate_current_asg(p_assignment_id IN NUMBER
1121 ,p_sess IN date
1122 ,p_grade_ladder_name IN OUT nocopy varchar2
1123 ,p_grade_name IN OUT nocopy varchar2
1124 ,p_step IN OUT nocopy varchar2
1125 ,p_salary IN OUT nocopy varchar2
1126 ) IS
1127 --
1128 -- Fix for bug 3648748. Removed per_grades table from the following curosr.
1129 --
1130 CURSOR csr_get_current_asg is
1131 select pgm.name
1132 ,grdtl.name
1133 ,pspp.step_id
1134 ,pqh_gsp_utility.get_cur_sal(p_assignment_id
1135 ,p_sess)
1136 ,psps.spinal_point_id
1137 ,psps.grade_spine_id
1138 from ben_pgm_f pgm
1139 ,per_grades_tl grdtl
1140 ,per_all_assignments_f paaf
1141 ,per_spinal_point_placements_f pspp
1142 ,per_spinal_point_steps_f psps
1143 where
1144 paaf.assignment_id = p_assignment_id
1145 and p_sess between
1146 paaf.effective_start_date and paaf.effective_end_date
1147 and paaf.grade_ladder_pgm_id = pgm.pgm_id
1148 and p_sess
1149 between pgm.effective_start_date and pgm.effective_end_date
1150 and grdtl.grade_id = paaf.grade_id
1151 and grdtl.language = userenv('LANG')
1152 and pspp.assignment_id (+)= paaf.assignment_id
1153 and p_sess
1154 between pspp.effective_start_date(+)
1155 and pspp.effective_end_date(+)
1156 and pspp.step_id = psps.step_id(+)
1157 and p_sess
1158 between psps.effective_start_date(+)
1159 and psps.effective_end_date(+)
1160 ;
1161
1162 cursor csr_get_current_gsp is
1163 select distinct current_grade_name
1164 ,current_grade_ladder_name
1165 ,current_step_name
1166 ,current_sal
1167 ,currency_code
1168 ,currency_name
1169 from pqh_gsp_electable_choice_v
1170 where assignment_id = p_assignment_id
1171 and rownum <= 1;
1172
1173 l_proc VARCHAR2(72) := g_pkg||'populate_current_asg';
1174 l_grade varchar2(240) := null;
1175 l_grade_ladder varchar2(240) := null;
1176 l_step varchar2(240) := null;
1177 l_step_name varchar2(240) := null;
1178 l_salary varchar2(100) := null;
1179 l_currency_code varchar2(50) := null;
1180 l_currency_name varchar2(50) := null;
1181 l_step_id number;
1182 l_spinal_point_id number;
1183 l_grade_spine_id number;
1184
1185 BEGIN
1186
1187 hr_utility.set_location('Entering:' || l_proc,10);
1188 /*
1189 --
1190 -- Comment out for BUG3282957
1191 --
1192 open csr_get_current_gsp;
1193 fetch csr_get_current_gsp into l_grade,l_grade_ladder,l_step,l_salary
1194 ,l_currency_code,l_currency_name;
1195 if csr_get_current_gsp%NOTFOUND then
1196 hr_utility.set_location(l_proc,20);
1197 close csr_get_current_gsp;
1198 else
1199 hr_utility.set_location(l_proc,30);
1200 close csr_get_current_gsp;
1201 p_grade_name := l_grade;
1202 p_grade_ladder_name := l_grade_ladder;
1203 p_step := l_step;
1204 p_salary := l_salary;
1205 end if;
1206 */
1207 --
1208 -- BUG3282957
1209 -- Using csr_get_current_asg intead of csr_get_current_gsp
1210 --
1211 open csr_get_current_asg;
1212 fetch csr_get_current_asg into l_grade_ladder,l_grade,l_step,l_salary
1213 ,l_spinal_point_id,l_grade_spine_id;
1214 if csr_get_current_asg%NOTFOUND then
1215 hr_utility.set_location(l_proc,20);
1216 close csr_get_current_asg;
1217 else
1218 hr_utility.set_location(l_proc,30);
1219 close csr_get_current_asg;
1220 p_grade_name := l_grade;
1221 p_grade_ladder_name := l_grade_ladder;
1222 p_salary := l_salary;
1223
1224 per_spinal_point_steps_pkg.pop_flds(l_Step_name,
1225 p_sess,
1226 l_spinal_point_id,
1227 l_grade_spine_id);
1228
1229 hr_utility.trace('step :' || l_step_name);
1230
1231 p_step := l_step_name;
1232 end if;
1233
1234 hr_utility.trace('grade_name :' || p_grade_name);
1235 hr_utility.trace('grade_ladder_name :' || p_grade_ladder_name);
1236 hr_utility.trace('step :' || p_step);
1237 hr_utility.trace('salary :' || p_salary);
1238 hr_utility.trace('currency_code :' || l_currency_code);
1239 hr_utility.trace('currency_name :' || l_currency_name);
1240
1241 hr_utility.set_location(' Leaving:' || l_proc,40);
1242 END populate_current_asg;
1243
1244 END per_cagr_utility_pkg;