1 PACKAGE BODY hri_bpl_fast_formula AS
2 /* $Header: hribffl.pkb 120.0 2005/05/29 06:52:57 appldev noship $ */
3 -- ----------------------------------------------------------------------------
4 -- PURPOSE OF PACKAGE
5 -- ~~~~~~~~~~~~~~~~~~
6 -- This a generic fast formula package created in support of HRI and HRI DBI
7 --
8 -- The urpose of the package is to either generate or compile specific fast
9 -- formulas required by HRI.
10 --
11 -- CURRENT STATUS
12 -- ~~~~~~~~~~~~~~
13 -- Currently this patch only supports the fast formula
14 -- 'NORMALIZE_APPRAISAL_RATING', which is implimented in HRI DBI 6.0H.
15 -- However the intention is that in the future it will be extended to either:
16 -- + Compile fast formulas required by HRI if they exist, but are not
17 -- compiled.
18 -- + Generate fast formulas required by HRI, if they do not exist, and then
19 -- compile them.
20 --
21 -- ----------------------------------------------------------------------------
22 -- DEFINE GLOBALS
23 --
24 g_debug_flag VARCHAR2(1) := NVL(fnd_profile.value('HRI_ENBL_DTL_LOG'),'N');
25 --
26 -- Global used to store the previous rating scale id used.
27 --
28 g_rating_scale_id NUMBER;
29 --
30 -- Global used to store the number of ratings in the scale g_rating_scale_name
31 --
32 g_rating_scales NUMBER;
33 --
34 -- Global variable used to store the formula type id for the QuickPaint
35 -- type of fastformula.
36 --
37 g_formula_type_id ff_formula_types.formula_type_id%TYPE;
38 --
39 g_msg_sub_group VARCHAR2(400) := '';
40 --
41 -- END DEFINE GLOBALS
42 --
43 -- -----------------------------------------------------------------------------
44 --
45 -- DEFINE GLOBAL CONSTANTS
46 --
47 c_OUTPUT_LINE_LENGTH CONSTANT NUMBER := 254;
48 --
49 -- RETURN Character
50 -- NOTE: please note this MUST be on 2 lines of code i.e.:
51 -- c_RETURN VARCHAR2(1) DEFAULT '
52 -- ';
53 --
54 c_RETURN CONSTANT VARCHAR2(1) := '
55 ';
56 --
57 -- Standard performance fast formula introduction text
58 --
59 c_Perf_FF_intro_text CONSTANT VARCHAR2(250) :=
60 'INPUTS are APPRAISAL_TEMPLATE_NAME (Text),
61 RATING
62 DEFAULT for APPRAISAL_TEMPLATE_NAME is ''###''
63 DEFAULT for RATING is 0
64 SKIP_REVIEW = ''Y''
65 ';
66 --
67 -- Standard performance fast formula closing text
68 --
69 c_Perf_FF_clsng_text CONSTANT VARCHAR2(250) :=
70 'RETURN SKIP_REVIEW, NORMALIZED_RATING';
71 --
72 -- The performance appraisal fastformula name
73 --
74 c_prf_aprsl_ff_name CONSTANT VARCHAR2(30) := 'NORMALIZE_APPRAISAL_RATING';
75 --
76 --
77 -- END DEFINE GLOBAL CONSTANTS
78 --
79 -- --------------------------------------------------------------
80 -- Procedure msg logs a message, either using fnd_file, or
81 -- hr_utility.trace
82 --
83 PROCEDURE msg(p_text IN VARCHAR2) IS
84 --
85 BEGIN
86 --
87 -- Bug 4105868: Collection Diagnostics
88 --
89 HRI_BPL_CONC_LOG.output(p_text);
90 --
91 END msg;
92 --
93 -- -----------------------------------------------------------------------------
94 -- Inserts row into concurrent program log if debugging is enabled
95 -- -----------------------------------------------------------------------------
96 --
97 PROCEDURE dbg(p_text VARCHAR2) IS
98 --
99 BEGIN
100 --
101 -- Bug 4105868: Collection Diagnostics
102 --
103 HRI_BPL_CONC_LOG.dbg(p_text);
104 --
105 END dbg;
106 --
107 -- -----------------------------------------------------------------------------
108 -- Procedure design to faithfully display the text passed to it
109 -- without corrupting the layout. This procedure may be slow,
110 -- and should only be used where specifically required e.g. to display
111 -- dynamically generated code.
112 --
113 --
114 PROCEDURE display (p_text IN VARCHAR2 DEFAULT NULL)
115 IS
116 --
117 -- Counter used by loop
118 --
119 i NUMBER;
120 j NUMBER;
121 --
122 -- The length of the input parameter p_text
123 --
124 l_text_len NUMBER := LENGTH (p_text);
125 --
126 -- The current character being processed
127 --
128 c VARCHAR2(1);
129 --
130 -- The text string being generated.
131 --
132 l_text VARCHAR2(78);
133 --
134 -- The maximum length of the text before displaying it.
135 --
136 l_max_line_len NUMBER DEFAULT 78;
137 --
138 BEGIN
139 --
140 -- If debugging is not turned on, exit the procedure
141 --
142 IF g_debug_flag <> 'Y'
143 THEN
144 --
145 RETURN;
146 --
147 END IF;
148 --
149 l_text := '';
150 j := 0;
151 --
152 FOR i IN 1 .. l_text_len
153 LOOP
154 --
155 c := SUBSTR (p_text, i, 1); -- Fetch current character
156 --
157 -- If a return character is found display text so far
158 -- and re-set line length counter
159 --
160 IF c = c_RETURN
161 THEN
162 --
163 fnd_file.put_line(fnd_file.LOG,l_text);
164 l_text := '';
165 j := 0;
166 --
167 --
168 -- If the maximum line length is reached, display the text so far,
169 -- and re-set line length counter
170 --
171 ELSIF j = l_max_line_len
172 THEN
173 --
174 fnd_file.put_line(fnd_file.LOG,l_text);
175 l_text := '';
176 j := 0;
177 --
178 ELSE
179 --
180 -- Add the current char to the text string to display and
181 -- increment the line length counter 'j'
182 --
183 l_text := l_text||c;
184 j := j + 1;
185 --
186 END IF;
187 --
188 END LOOP;
189 --
190 -- If there is any left over text that is undisplayed then output it.
191 --
192 IF j > 0
193 THEN
194 --
195 fnd_file.put_line(fnd_file.LOG,l_text);
196 --
197 END iF;
198 --
199 END display;
200 --
201 -- -----------------------------------------------------------------------------
202 -- Procedure to generate the standard error text for the main entry point
203 -- procedures.
204 --
205 PROCEDURE failure_exit_message IS
206 --
207 l_message fnd_new_messages.message_text%TYPE;
208 --
209 BEGIN
210 --
211 -- Bug 4105868: Collection Diagnostics
212 --
213 fnd_message.set_name('HRI', 'HRI_407290_FF_FLD_CMPL');
214 --
215 l_message := fnd_message.get;
216 --
217 hri_bpl_conc_log.log_process_info
218 (p_msg_type => 'ERROR'
219 ,p_note => l_message
220 ,p_package_name => 'HRI_BPL_FAST_FORMULA'
221 ,p_msg_sub_group => 'FAILURE_EXIT_MESSAGE'
222 ,p_sql_err_code => SQLCODE
223 ,p_msg_group => 'FST_FTML_CHCK'
224 );
225 --
226 msg(l_message);
227 --
228 END;
229 --
230 -- -----------------------------------------------------------------------------
231 -- Procedure used by the EXCEPTION statements in the main entry point
232 -- procedures called from DBI request sets.
233 --
234 -- This procedure will output a standard error message and set the status of
235 -- the process to 'WARNING' rather than error, to prevent fastformula issues
236 -- halting the request set.
237 --
238 --
239 PROCEDURE handle_exit_exception IS
240 --
241 -- Return value from call to fnd_concurrent.set_completion_status
242 --
243 l_success BOOLEAN DEFAULT FALSE;
244 --
245 BEGIN
246 --
247 -- Output standard failure message.
248 --
249 failure_exit_message;
250 --
251 -- Set process status to 'WARNING' if there has been an error
252 -- rather than 'ERROR', so that this process failing does not stop
253 -- the entire request set.
254 --
255 l_success := fnd_concurrent.set_completion_status (
256 'WARNING'
257 ,NULL);
258 --
259 END handle_exit_exception;
260 --
261 -- ----------------------------------------------------------------------------
262 -- This function returns true if the env is a shared HR env, or force
263 -- foundation profile option has been set to Yes.
264 --
265 FUNCTION check_if_shared_hr RETURN BOOLEAN
266 IS
267 --
268 -- Variable used to store whether or not shared HR is installed.
269 --
270 l_hr_installed VARCHAR2(30); -- Stores HR installed or not
271 --
272 -- Varibale stores whether shared HR mode has been forced or not.
273 --
274 l_frc_shrd_hr_prfl_val VARCHAR2(30); -- Variable to store value for
275 -- Profile HRI:DBI Force Foundation HR Processes
276 --
277 BEGIN
278 --
279 -- Check if this we are on a shared HR environment
280 --
281 l_frc_shrd_hr_prfl_val := NVL(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N');
282 l_hr_installed := hr_general.chk_product_installed(800);
283 --
284 -- If we are in a shared HR environment, then fast formula are not required
285 -- and the process should do nothing.
286 --
287 IF l_hr_installed = 'FALSE'
288 OR l_frc_shrd_hr_prfl_val = 'Y'
289 THEN
290 --
291 -- Separation of the full stop (period) '.' for GSCC standards.
292 --
293 dbg('This system has been configured for Shared HRMS'||'.');
294 dbg('FastFormula are not required in a shared HR environment.');
295 dbg('No further processing required.');
296 --
297 -- Shared HR mode is set so return true.
298 --
299 RETURN TRUE;
300 --
301 END IF;
302 --
303 -- Shared HR mode is not set, so return FALSE
304 --
305 dbg('Shared HR mode not detected');
306 --
307 RETURN FALSE;
308 --
309 END check_if_shared_hr;
310 --
311 -- ----------------------------------------------------------------------------
312 -- Compile all fast formulas with a given name.
313 --
314 PROCEDURE compile_fast_formula (p_fast_formula_name IN VARCHAR2)
315 IS
316 --
317 -- The request id of the sub process submitted to compile the fastformula
318 --
319 l_request_id NUMBER;
320 --
321 BEGIN
322 --
323 dbg('Attempting to compile fastformula '||p_fast_formula_name||'.');
324 --
325 l_request_id :=
326 fnd_request.submit_request
327 (
328 application => 'FF' -- Fast Formula application code
329 ,program => 'SINGLECOMPILE' -- Name of concurrent process
330 ,sub_request => TRUE -- Indicates that the request should be
331 -- executed as a sub process.
332 ,argument1 => 'QuickPaint' -- FastFormula Type
333 ,argument2 => p_fast_formula_name -- Name of fast formula to compile
334 );
335 --
336 dbg('Called sub process to compile fastformula '||p_fast_formula_name||'.');
337 --
338 -- Tell the process to pause awaiting sub process completion.
339 --
340 dbg('Tell process to wait for sub process to compile fastformula '||
341 p_fast_formula_name||'.');
342 --
343 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
344 request_data=> TO_CHAR(l_request_id));
345 --
346 dbg('Waiting for sub process to compile fastformula '||
347 p_fast_formula_name||'.');
348 --
349 RETURN;
350 --
351 EXCEPTION
352 --
353 WHEN OTHERS
354 THEN
355 --
356 dbg('An error occurred while trying to launch a slave process to '||
357 'compile all performance appraisal fastformulas.');
358 --
359 --
360 END compile_fast_formula;
361 --
362 --
363 -- ----------------------------------------------------------------------------
364 -- Compile all performance appraisal (Self Service) fast formulas.
365 --
366 PROCEDURE compile_prfrmnc_apprsl_ff
367 IS
368 --
369 BEGIN
370 --
371 compile_fast_formula (c_prf_aprsl_ff_name);
372 --
373 RETURN;
374 --
375 END compile_prfrmnc_apprsl_ff;
376 --
377 -- ----------------------------------------------------------------------------
378 -- This function returns true if the program is re-entering after having
379 -- suspended for a sub-process to complete.
380 --
381 FUNCTION check_if_re_entered RETURN BOOLEAN
382 IS
383 --
384 -- Used to store the return value of fnd_conc_global.request_data. If
385 -- it is non null then this indicates that the process has returned
386 -- from a paused state.
387 --
388 l_request_data VARCHAR2(240);
389 --
390 -- Store the request id of the sub process launched to run PYUGEN.
391 --
392 l_request_id NUMBER;
393 --
394 -- Local variables used to store details of successfully completed
395 -- sub processes.
396 --
397 l_phase VARCHAR2(240); -- Dummy output variable that is ignored.
398 l_status VARCHAR2(240); -- Dummy output variable that is ignored.
399 l_dev_phase VARCHAR2(240); -- Dummy output variable that is ignored.
400 l_dev_status VARCHAR2(240); -- Set to NORMAL if the sub process ended
401 -- successfully.
402 l_message VARCHAR2(240); -- Dummy output variable that is ignored.
403 l_success BOOLEAN;
404 --
405 BEGIN
406 --
407 -- Call fnd_conc_global.request_data, to see if this program is re-entering
408 -- after being paused.
409 --
410 l_request_data := fnd_conc_global.request_data;
411 --
412 -- NOTE!!! THE FOLLOWING CODE WITHIN THE CONDITION:
413 -- 'IF l_request_data IS NOT NULL', is only run after re-entering the
414 -- package when sub processes have completed.
415 --
416 IF l_request_data IS NOT NULL
417 THEN
418 --
419 dbg('Re-starting after sub-process completion ......');
420 --
421 -- Get the request_id of the sub process previously executed so that we
422 -- can check it's status.
423 --
424 l_request_id := TO_NUMBER(l_request_data);
425 --
426 -- Check whether the sub process finished successfully.
427 --
428 l_success := fnd_concurrent.get_request_status
429 (
430 request_id => l_request_id
431 ,appl_shortname => NULL
432 ,program => NULL
433 ,phase => l_phase
434 ,status => l_status
435 ,dev_phase => l_dev_phase
436 ,dev_status => l_dev_status
437 ,message => l_message
438 );
439 --
440 -- Set Varchar2 equivalent (l_success_chr) of l_success
441 --
442 IF l_success
443 THEN
444 --
445 -- Debug info
446 --
447 dbg('Sub process finished with status '||l_dev_status||'.');
448 --
449 -- If l_dev_status 'NORMAL', then that means the sub process was
450 -- successful.
451 --
452 IF l_dev_status <> 'NORMAL'
453 THEN
454 --
455 -- The sub process failed so raise an exception
456 --
457 dbg('The FastFormula compilation sub process failed. Raising an exception.');
458 --
459 RAISE sub_process_failed;
460 --
461 ELSE
462 --
463 -- The sub process completed successfully so end.
464 --
465 dbg('The FastFormula compilation sub process ended successfully.');
466 --
467 RETURN TRUE;
468 --
469 END IF;
470 --
471 ELSE
472 --
473 -- Details of the sub process can not be found for some reason, so
474 -- raise an exception.
475 --
476 dbg('Cannot identify completion status for FastFormula compilation sub process');
477 --
478 RAISE sub_process_not_found;
479 --
480 END IF;
481 --
482 --
483 -- If we are not returning from a sub process, then return FALSE.
484 --
485 ELSE
486 --
487 RETURN FALSE;
488 --
489 END IF; -- End process re-entered logic.
490 --
491 END check_if_re_entered;
492 --
493 -- ----------------------------------------------------------------------------
494 -- Function that returns the default percentile value to use, for a given
495 -- step on the rating scale.
496 --
497 FUNCTION get_percentile_value (p_scale_cnt NUMBER) RETURN NUMBER
498 IS
499 --
500 -- Stores the percentile value to use for the current step on the rating
501 -- scale.
502 --
503 l_pcntl_value NUMBER;
504 --
505 BEGIN
506 --
507 -- If this is the first step in the scale, return 1 as the percventile
508 -- value.
509 --
510 IF p_scale_cnt = 1
511 THEN
512 --
513 l_pcntl_value := 1;
514 --
515 --
516 -- If the step is in not the top or the bottom of the scale
517 -- then calculate the percentile value, based on the current step
518 -- and the total number of steps.
519 --
520 ELSIF p_scale_cnt > 1 AND
521 p_scale_cnt < g_rating_scales
522 THEN
523 --
524 l_pcntl_value := ROUND(((100/(g_rating_scales - 1))*(p_scale_cnt - 1)),0);
525 --
526 ELSIF p_scale_cnt = g_rating_scales
527 THEN
528 --
529 l_pcntl_value := 100;
530 --
531 END IF;
532 --
533 -- Return the percentile value
534 --
535 RETURN l_pcntl_value;
536 --
537 END get_percentile_value;
538 --
539 -- ----------------------------------------------------------------------------
540 --
541 -- Get the QuickPaint formula_type_id
542 --
543 PROCEDURE set_quick_paint_ff_type_id
544 IS
545 --
546 -- Cursor to find out the formula type id of the QuickPaint fast formula
547 -- type.
548 --
549 CURSOR c_ff_type_id IS
550 SELECT formula_type_id
551 FROM ff_formula_types
552 WHERE formula_type_name = 'QuickPaint';
553 --
554 BEGIN
555 --
556 OPEN c_ff_type_id;
557 FETCH c_ff_type_id INTO g_formula_type_id;
558 CLOSE c_ff_type_id;
559 --
560 END set_quick_paint_ff_type_id;
561 --
562 --
563 -- ----------------------------------------------------------------------------
564 --
565 -- Check if a fast formula with a given name exists in a business group
566 --
567 FUNCTION check_fast_formula_exists
568 (
569 p_business_group_id NUMBER
570 ,p_formula_name VARCHAR2
571 )
572 RETURN BOOLEAN
573 IS
574 --
575 -- Cursor to find out the formula type id of the QuickPaint fast formula
576 -- type.
577 --
578 CURSOR csr_formula_exists (cp_business_group_id NUMBER
579 ,cp_formula_name VARCHAR2)
580 IS
581 SELECT 'x' l_exists
582 FROM ff_formulas_f
583 WHERE business_group_id = cp_business_group_id
584 AND formula_name = cp_formula_name;
585
586 t_rec csr_formula_exists%rowtype;
587 --
588 -- Dummy variable to store the value returned by cursor c_formula_exists.
589 --
590 l_formula_exists VARCHAR2(100);
591 --
592 -- Stores the number of rows returned
593 --
594 l_row_count NUMBER DEFAULT 0;
595 --
596 BEGIN
597 --
598 dbg('Checking formula exists ...');
599 dbg('Business Group Id: '||TO_CHAR(p_business_group_id));
600 dbg('Formula: '||p_formula_name);
601 --
602 -- Look for fast formula. If it is found set l_row_count to 1
603 --
604 FOR t_rec IN csr_formula_exists (p_business_group_id, p_formula_name)
605 LOOP
606 --
607 l_row_count := 1;
608 --
609 END LOOP;
610 --
611 dbg('Checked whether formula exists ...');
612 --
613 -- IF a row has been found return TRUE, otherwise return FALSE
614 --
615 IF l_row_count = 1
616 THEN
617 --
618 dbg('Returning fastformula exists ...');
619 --
620 RETURN TRUE;
621 --
622 ELSE
623 --
624 dbg('Returning fastformula does not exist ...');
625 --
626 RETURN FALSE;
627 --
628 END IF;
629 --
630 EXCEPTION
631 --
632 WHEN OTHERS THEN
633 --
634 dbg('Exception occurred while checking if fastformula exists ...');
635 --
636 g_msg_sub_group := NVL(g_msg_sub_group, 'CHECK_FAST_FORMULA_EXISTS');
637 --
638 -- A row has not been found, so return false.
639 --
640 RAISE;
641 --
642 END check_fast_formula_exists;
643 --
644 --
645 -- ----------------------------------------------------------------------------
646 --
647 -- Check if a fast formula with a given name exists in a business group
648 --
649 FUNCTION check_fast_formula_cmpld
650 (
651 p_business_group_id NUMBER
652 ,p_formula_name VARCHAR2
653 )
654 RETURN BOOLEAN
655 IS
656 --
657 -- Cursor to find out if a given fast formula is compiled for a specific
658 -- business group.
659 --
660 CURSOR csr_formula_compiled (cp_business_group_id NUMBER
661 ,cp_formula_name VARCHAR2)
662 IS
663 --
664 SELECT 'x' l_exists
665 FROM
666 ff_formulas_f frm
667 ,ff_compiled_info_f fcp
668 WHERE frm.formula_id = fcp.formula_id
669 AND frm.business_group_id = cp_business_group_id
670 AND frm.formula_name = cp_formula_name;
671 --
672 t_rec csr_formula_compiled%rowtype;
673 --
674 -- Dummy variable to store the value returned by cursor c_formula_exists.
675 --
676 l_formula_compiled VARCHAR2(100);
677 --
678 -- Stores the number of rows returned
679 --
680 l_row_count NUMBER DEFAULT 0;
681 --
682 BEGIN
683 --
684 dbg('Checking whether an existing FastFormula is compiled.');
685 --
686 dbg('Business Group Id: '||TO_CHAR(p_business_group_id));
687 dbg('Fast Formula Name: '||p_formula_name);
688 --
689 -- Look for fast formula. If it is found set l_row_count to 1
690 --
691 FOR t_rec IN csr_formula_compiled (p_business_group_id, p_formula_name)
692 LOOP
693 --
694 l_row_count := 1;
695 --
696 END LOOP;
697 --
698 dbg('Checked whether FastFormula compiled ...');
699 --
700 -- IF a row has been found return TRUE, otherwise return FALSE
701 --
702 IF l_row_count = 1
703 THEN
704 --
705 dbg('The FastFormula was already compiled ...');
706 --
707 RETURN TRUE;
708 --
709 ELSE
710 --
711 dbg('The FastFormula requires compilation ...');
712 --
713 RETURN FALSE;
714 --
715 END IF;
716 --
717 EXCEPTION
718 --
719 WHEN OTHERS THEN
720 --
721 dbg('Exception occurred while checking if FastFormula compiled ...');
722 --
723 g_msg_sub_group := NVL(g_msg_sub_group, 'CHECK_FAST_FORMULA_CMPLD');
724 --
725 -- A row has not been found, so return false.
726 --
727 RAISE;
728 --
729 END check_fast_formula_cmpld;
730 --
731 -- ----------------------------------------------------------------------------
732 -- This procedure adds the static initialization and end code to
733 -- the fast formula, and creates the first formula for the relevant business
734 -- group.
735 --
736 PROCEDURE generate_performance_formula(p_business_group_id IN NUMBER
737 ,p_formula_text IN OUT NOCOPY VARCHAR)
738 IS
739 --
740 -- Stores the last_update_date returned by ff_formulas_f_pkg.insert_row
741 --
742 l_last_update_date DATE;
743 --
744 -- Stores the rowid returned by ff_formulas_f_pkg.insert_row
745 --
746 l_Rowid VARCHAR2(240);
747 --
748 -- End of time
749 --
750 l_end_date DATE DEFAULT hr_general.end_of_time;
751 --
752 -- Stores the formula_id returned by ff_formulas_f_pkg.insert_row
753 --
754 l_Formula_Id ff_formulas_f.formula_id%TYPE DEFAULT NULL;
755 --
756 -- Stores the name of the fast formula that we are trying to save.
757 --
758 l_formula_name ff_formulas_f.formula_name%TYPE DEFAULT c_prf_aprsl_ff_name;
759 --
760 -- Stores the legislation code for the current business group
761 --
762 l_legislation_code per_business_groups.legislation_code%TYPE;
763 --
764 BEGIN
765 --
766 -- Create the FF text for the current business group.
767 --
768 p_formula_text :=
769 c_Perf_FF_intro_text
770 ||
771 p_formula_text
772 ||
773 c_Perf_FF_clsng_text;
774 --
775 -- Output final formula text
776 --
777 dbg('---------------------------------------------------------------------');
778 dbg('p_formula_text:');
779 display(p_formula_text);
780 dbg('---------------------------------------------------------------------');
781 --
782 -- Insert the formula into the base table.
783 --
784 BEGIN
785 --
786 -- 4106225 The legislation code for the formula should not be set. It is set
787 -- only for global legislative formulas that are shipped out of the
788 -- the box. Any user created formula should not have this set, or else the
789 -- formula will not show up on the PUI if the bg's legislation is changed
790 -- l_legislation_code is set to null by default
791 --
792 ff_formulas_f_pkg.Insert_Row
793 (
794 X_Rowid => l_rowid
795 ,X_Formula_Id => l_formula_id
796 ,X_Effective_Start_Date => hr_general.start_of_time
797 ,X_Effective_End_Date => l_end_date
798 ,X_Business_Group_Id => p_business_group_id
799 ,X_Legislation_Code => l_legislation_code
800 ,X_Formula_Type_Id => g_Formula_Type_Id
801 ,X_Formula_Name => l_formula_name
802 ,X_Description => l_formula_name
803 ,X_Formula_Text => p_formula_text
804 ,X_Sticky_Flag => NULL
805 ,X_Last_Update_Date => l_last_update_date
806 );
807 EXCEPTION
808 --
809 WHEN OTHERS THEN
810 dbg('An error was encountered while inserting the generated fastformula');
811 msg(fnd_message.get);
812 --
813 g_msg_sub_group := NVL(g_msg_sub_group, 'GENERATE_PERFORMANCE_FORMULA');
814 --
815 RAISE;
816 --
817 END;
818 --
819 -- COMMIT the formula that has been created to the database.
820 --
821 COMMIT;
822 --
823 dbg('Generated formula has been successfully created ...');
824 --
825 --
826 END;
827 --
828 -- ----------------------------------------------------------------------------
829 -- This FUNCTION returns the number of levels in a particular rating scale
830 --
831 FUNCTION get_number_of_levels (p_rating_scale_id IN NUMBER) RETURN NUMBER
832 IS
833 --
834 CURSOR csr_rating_template_scales (cp_rating_scale_id NUMBER) IS
835 SELECT COUNT(step_value)
836 FROM per_rating_levels
837 WHERE rating_scale_id = cp_rating_scale_id;
838 --
839 -- Used to store the number of levels in the rating scale.
840 --
841 l_rating_levels NUMBER;
842 --
843 BEGIN
844 --
845 -- If the rating scale name is the same as the one previously used,
846 -- then return the previous rating scale value
847 --
848 IF p_rating_scale_id = g_rating_scale_id
849 THEN
850 --
851 RETURN g_rating_scales;
852 --
853 END IF;
854 --
855 -- get the number of ratings in the scale as we don't have it cached.
856 --
857 OPEN csr_rating_template_scales (p_rating_scale_id);
858 FETCH csr_rating_template_scales INTO l_rating_levels;
859 CLOSE csr_rating_template_scales;
860 --
861 -- Store the rating scale id and the number of levels for later use
862 --
863 g_rating_scale_id := p_rating_scale_id;
864 g_rating_scales := l_rating_levels;
865 --
866 RETURN g_rating_scales;
867 --
868 END get_number_of_levels;
869 --
870 -- ----------------------------------------------------------------------------
871 -- This procedure generates fast formulas where none exist for performance
872 -- appraisals in either a specific business group, or all business groups
873 --
874 -- The following code, is an example of the text that should be
875 -- generated for a given business groups fast formula.
876 --
877 -- INPUTS are APPRAISAL_TEMPLATE_NAME (Text),
878 -- RATING_LEVEL_CODE,
879 -- DEFAULT for APPRAISL_TEMPLATE_NAME is 'SKIPPP'
880 -- SKIP_REVIEW = 'YES'
881 -- IF APPRAISAL_TEMPLATE_NAME = 'Annual Template'
882 -- THEN
883 -- (
884 -- SKIP_REVIEW = 'NO'
885 -- IF RATING_LEVEL_CODE = 1 then
886 -- NORMALIZED_RATING = 1
887 -- IF RATING_LEVEL_CODE = 2 then
888 -- NORMALIZED_RATING = 50
889 -- IF RATING_LEVEL_CODE = 3 then
890 -- NORMALIZED_RATING = 100
891 -- )
892 -- IF APPRAISAL_TEMPLATE_NAME = 'Annual Template for developers'
893 -- THEN
894 -- (
895 -- SKIP_REVIEW = 'NO'
896 -- IF RATING_LEVEL_CODE = 5 then
897 -- NORMALIZED_RATING = 1
898 -- IF RATING_LEVEL_CODE = 6 then
899 -- NORMALIZED_RATING = 25
900 -- IF RATING_LEVEL_CODE = 7 then
901 -- NORMALIZED_RATING = 50
902 -- IF RATING_LEVEL_CODE = 8 then
903 -- NORMALIZED_RATING = 75
904 -- IF RATING_LEVEL_CODE = 9 then
905 -- NORMALIZED_RATING = 100
906 -- )
907 -- RETURN SKIP_REVIEW, NORMALIZED_RATING
908 --
909 --
910 PROCEDURE gnrt_bg_ss_prfrmnce_apprsl_ff (p_business_group_id IN NUMBER)
911 IS
912 --
913 -- Cursor returning all of the appraisal templates, and their step values
914 -- for all business groups.
915 --
916 -- This cursor is used to drive the generation of fast formula for
917 -- each business group that requires a formula.
918 --
919 CURSOR csr_rating_template_scales (cp_business_group_id NUMBER) IS
920 SELECT pat.business_group_id
921 ,pat.name rating_template_name
922 ,prs.name rating_scale_name
923 ,prs.rating_scale_id
924 ,prl.step_value
925 FROM per_appraisal_templates pat
926 ,per_rating_scales prs
927 ,per_rating_levels prl
928 WHERE pat.business_group_id = cp_business_group_id
929 AND pat.rating_scale_id = prs.rating_scale_id
930 AND pat.rating_scale_id = prl.rating_scale_id
931 ORDER BY business_group_id
932 , prs.rating_scale_id
933 , rating_template_name
934 , step_value;
935 --
936 -- Declare record based on the csr_rating_template_scales cursor
937 --
938 rating_template_scales_rec csr_rating_template_scales%ROWTYPE;
939 --
940 -- Declare local variables to store the latest business group and template
941 --
942 l_business_group_id NUMBER DEFAULT NULL;
943 l_rating_template_name per_appraisal_templates.name%TYPE DEFAULT NULL;
944 --
945 -- The string l_formula_text stores the code that has been generated for
946 -- a given business group.
947 --
948 l_formula_text VARCHAR2(32767);
949 --
950 -- Used to store the number of scales there are for a particular
951 -- rating scale.
952 --
953 l_rating_levels NUMBER;
954 --
955 -- Counter to indicate at what point on the rating scale you are at.
956 --
957 l_scale_cnt NUMBER;
958 --
959 -- Stores the percentile default value for the current step on
960 -- the rating scale.
961 --
962 l_scale_pcnt_value NUMBER;
963 --
964 BEGIN
965 --
966 dbg('Starting to create a FastFormula for the business group.');
967 --
968 dbg('Starting to initialize variables ...');
969 --
970 -- A fastformula does not exist for this business group, so we
971 -- need to start the process of creating one.
972 --
973 ------------------------------------------------------------------------
974 --
975 -- START Initialize variables variables
976
977 -- Reset varibales affected by change in business group.
978 --
979 g_rating_scale_id := NULL;
980 --
981 g_rating_scales := NULL;
982 --
983 -- Make sure that the formula text is empty
984 --
985 l_formula_text := '';
986 --
987 -- Set the current rating template to NULL
988 --
989 l_rating_template_name := NULL;
990 --
991 dbg('Finished initializing variables ...');
992 --
993 -- END Initialize variables variables
994 ------------------------------------------------------------------------
995 --
996 -- For each template and rating scale step, loop through
997 -- creating appropriate fastformula code.
998 --
999 FOR rating_template_scales_rec IN
1000 csr_rating_template_scales(p_business_group_id)
1001 LOOP
1002 --
1003 dbg('Looping for ....');
1004 dbg('business_group_id: '||TO_CHAR(rating_template_scales_rec.business_group_id));
1005 dbg('rating_template_name: '||rating_template_scales_rec.rating_template_name);
1006 dbg('rating_scale_name: '||rating_template_scales_rec.rating_scale_name);
1007 dbg('step_value: '||rating_template_scales_rec.step_value);
1008 --
1009 -- Check to see if the template has changed, or this is the first
1010 -- time through the loop.
1011 --
1012 IF rating_template_scales_rec.rating_template_name <>
1013 NVL(l_rating_template_name,-1)
1014 THEN
1015 --
1016 -- RESET the scale count
1017 --
1018 l_scale_cnt := 1;
1019 --
1020 -- Get the number of scales in the current rating template's
1021 -- rating scale.
1022 --
1023 l_rating_levels :=
1024 get_number_of_levels(rating_template_scales_rec.rating_scale_id);
1025 --
1026 dbg('Rating template has changed ...');
1027 --
1028 -- IF this is not the very first template for the business group.
1029 -- then put the end bracket on the current condition.
1030 --
1031 IF l_rating_template_name IS NOT NULL
1032 THEN
1033 --
1034 dbg('Closing previous template condition ...');
1035 --
1036 l_formula_text := l_formula_text||
1037 ')';
1038 --
1039 END IF;
1040 --
1041 l_formula_text := l_formula_text||c_RETURN||
1042 'IF APPRAISAL_TEMPLATE_NAME = '||c_RETURN||
1043 ' '''||rating_template_scales_rec.rating_template_name||'''
1044 THEN
1045 (
1046 SKIP_REVIEW = ''N'' '||c_RETURN;
1047 --
1048 -- Set the current template that is being processed
1049 --
1050 l_rating_template_name
1051 := rating_template_scales_rec.rating_template_name;
1052 --
1053 END IF;
1054 --
1055 -- Get the percentile value (l_scale_pcnt_value) for the current
1056 -- step on the rating scale (l_scale_cnt).
1057 --
1058 l_scale_pcnt_value := get_percentile_value(l_scale_cnt);
1059 --
1060 -- If this is simply another level in the rating scale, create a new
1061 -- condition.
1062 --
1063 l_formula_text := l_formula_text||
1064 ' IF RATING = '
1065 ||rating_template_scales_rec.step_value||c_RETURN||
1066 ' THEN
1067 ( NORMALIZED_RATING = '||l_scale_pcnt_value||' ) '||c_RETURN;
1068 --
1069 -- Increment the scale level count
1070 --
1071 l_scale_cnt := l_scale_cnt + 1;
1072 --
1073 dbg(rating_template_scales_rec.step_value);
1074 --
1075 END LOOP;
1076 --
1077 -- Finished generating the main body text for the business group.
1078 -- Now process text ...
1079 --
1080 -- Providing we have generated some fastformula text generate the
1081 -- fast formula for the business group.
1082 --
1083 IF LENGTH(l_formula_text) > 0
1084 THEN
1085 --
1086 dbg('Generate formula for previous BG ....');
1087 --
1088 -- Cose the condition for the current template
1089 --
1090 l_formula_text := l_formula_text||')'||c_RETURN;
1091 --
1092 -- Call procedure to complete / generate the fast formula for the
1093 -- current business group.
1094 --
1095 generate_performance_formula(p_business_group_id
1096 ,l_formula_text
1097 );
1098 --
1099 END IF;
1100 --
1101 dbg('Successfully created a FastFormula for the business group.');
1102 --
1103 EXCEPTION
1104 --
1105 WHEN OTHERS
1106 THEN
1107 --
1108 dbg('An error occurred while create a FastFormula for the business group.');
1109 --
1110 g_msg_sub_group := NVL(g_msg_sub_group, 'GNRT_BG_SS_PRFRMNCE_APPRSL_FF');
1111 --
1112 RAISE;
1113 --
1114 --
1115 END gnrt_bg_ss_prfrmnce_apprsl_ff;
1116 --
1117 -- ----------------------------------------------------------------------------
1118 -- Entry point for the generation of performance appraisal fastformula for a
1119 -- specific business group OR all business groups.
1120 --
1121 FUNCTION gnrt_ss_prfrmnce_apprsl_ff
1122 (
1123 p_business_group_id IN NUMBER DEFAULT NULL
1124 )
1125 RETURN BOOLEAN -- Indicates if a new formula was created
1126 IS
1127 --
1128 -- Select all of the business groups, that have appraisal templates,
1129 -- that have associated rating scales.
1130 --
1131 CURSOR csr_rtng_tmplt_bsnss_grps IS
1132 SELECT DISTINCT pat.business_group_id business_group_id
1133 FROM per_appraisal_templates pat
1134 WHERE rating_scale_id IS NOT NULL
1135 ORDER BY business_group_id;
1136 --
1137 -- Declare record based on the csr_rtng_tmplt_bsnss_grps cursor
1138 --
1139 l_rtng_tmplt_bsnss_grps csr_rtng_tmplt_bsnss_grps%ROWTYPE;
1140 --
1141 -- Boolean variable used to indicate a performance appraisal fastformula
1142 -- exists for a business group.
1143 --
1144 l_formula_exists BOOLEAN DEFAULT FALSE;
1145 --
1146 -- Indicates if a fastformula compilation is required
1147 --
1148 l_formula_cmpl_rqd BOOLEAN DEFAULT FALSE;
1149 --
1150 -- Indicates if a fastformula has been compiled
1151 --
1152 l_formula_cmpld BOOLEAN DEFAULT TRUE;
1153 --
1154 BEGIN
1155 --
1156 dbg('Starting to process Self Service Performance Appraisal FastFormula(s) ...');
1157 --
1158 -- Get the ff type id for the formula type QuickPaint
1159 --
1160 set_quick_paint_ff_type_id;
1161 --
1162 -- If a specific business group has been passed in, call
1163 -- gnrt_bg_ss_prfrmnce_apprsl_ff directly for that business group.
1164 --
1165 IF p_business_group_id IS NOT NULL
1166 THEN
1167 --
1168 dbg('Checking the Self Service Performance Appraisal FastFormulas for a single business groups.');
1169 --
1170 dbg('Processing business group id '||p_business_group_id||'.');
1171 --
1172 -- Check whether a fastformula already exists for the business_group.
1173 -- If the formula does exist then bail out.
1174 --
1175 l_formula_exists := check_fast_formula_exists
1176 (
1177 p_business_group_id
1178 ,c_prf_aprsl_ff_name
1179 );
1180 --
1181 dbg('If formula exixts then do nothing further for this business group');
1182 --
1183 IF l_formula_exists
1184 THEN
1185 --
1186 dbg('A FastFormula already exists for this business group.');
1187 --
1188 -- If the FastFormula exists already, then check if the formula is
1189 -- compiled. If the formula is not compiled, set l_formula_cmpl_rqd
1190 -- to TRUE, to indicate a FastFormula compilation is required.
1191 --
1192 l_formula_cmpld := check_fast_formula_cmpld
1193 (
1194 p_business_group_id
1195 ,c_prf_aprsl_ff_name
1196 );
1197 --
1198 -- If the formula is not compiled then set l_formula_cmpl_rqd to
1199 -- TRUE to indicate FastFormula compilation is required.
1200 --
1201 l_formula_cmpl_rqd := NOT(l_formula_cmpld);
1202
1203 -- Return False to indicate that the no fast formula has been
1204 -- created.
1205 --
1206 RETURN l_formula_cmpl_rqd;
1207 --
1208 END IF;
1209 --
1210 -- Call gnrt_bg_ss_prfrmnce_apprsl_ff to generate the appraisal
1211 -- fastformula for the business group.
1212 --
1213 dbg('A FastFormula needs to be generated for the business group.');
1214 --
1215 gnrt_bg_ss_prfrmnce_apprsl_ff(p_business_group_id);
1216 --
1217 RETURN TRUE;
1218 --
1219 END IF;
1220 --
1221 dbg('Checking the Self Service Performance Appraisal FastFormulas for all relevant business groups.');
1222 --
1223 -- If no business group is specified, try to generate fast formula
1224 -- for all business groups that have appraisal templates.
1225 --
1226 FOR l_rtng_tmplt_bsnss_grps IN csr_rtng_tmplt_bsnss_grps
1227 LOOP
1228 --
1229 -- Check whether a performance appraisal fast formula already
1230 -- exists for the business group.
1231 --
1232 --
1233 dbg('Checking if a FastFormula has been defined for business group id '
1234 || l_rtng_tmplt_bsnss_grps.business_group_id||'.');
1235 --
1236 l_formula_exists := check_fast_formula_exists
1237 (
1238 l_rtng_tmplt_bsnss_grps.business_group_id
1239 ,c_prf_aprsl_ff_name
1240 );
1241 --
1242 IF l_formula_exists = TRUE
1243 THEN
1244 --
1245 dbg('A formula already exists for this business group.');
1246 --
1247 -- If the FastFormula exists already, and we do not have any other
1248 -- reason yet identified to compile the FastFormula (across business
1249 -- groups) then check if the formula is compiled. If the formula is not
1250 -- compiled, set l_formula_cmpl_rqd to TRUE, to indicate a FastFormula
1251 -- compilation is required.
1252 --
1253 IF NOT l_formula_cmpl_rqd
1254 THEN
1255 --
1256 -- Check if the fast formula is compiled for this business group.
1257 --
1258 l_formula_cmpld := check_fast_formula_cmpld
1259 (
1260 l_rtng_tmplt_bsnss_grps.business_group_id
1261 ,c_prf_aprsl_ff_name
1262 );
1263 --
1264 -- If the formula is not compiled then set l_formula_cmpl_rqd to
1265 -- TRU to indicate FastFormula compilation is required.
1266 --
1267 l_formula_cmpl_rqd := NOT(l_formula_cmpld);
1268 --
1269 END IF;
1270 --
1271 --
1272 -- A formula does not exist and we need to create one
1273 --
1274 ELSE
1275 --
1276 dbg('A formula needs to be generated for the business group.');
1277 --
1278 gnrt_bg_ss_prfrmnce_apprsl_ff(l_rtng_tmplt_bsnss_grps.business_group_id);
1279 --
1280 l_formula_cmpl_rqd := TRUE;
1281 --
1282 END IF;
1283 --
1284 END LOOP;
1285 --
1286 dbg('Finished processing Self Service Performance Appraisal FastFormula(s) ...');
1287 --
1288 RETURN l_formula_cmpl_rqd;
1289 --
1290 END gnrt_ss_prfrmnce_apprsl_ff;
1291 --
1292 -- ----------------------------------------------------------------------------
1293 -- Standard Entry point to be called from standalone concurrent process, where
1294 -- a user can generate performance apraisal fastformula for 1 or a number of
1295 -- business groups.
1296 --
1297 PROCEDURE gnrt_ss_prfrmnce_apprsl_ff
1298 (
1299 errbuf OUT NOCOPY VARCHAR2
1300 ,retcode OUT NOCOPY NUMBER
1301 ,p_business_group_id IN NUMBER DEFAULT NULL
1302 )
1303 IS
1304 --
1305 -- Indocates if any formulas have been created.
1306 --
1307 l_formula_created BOOLEAN DEFAULT FALSE;
1308 --
1309 BEGIN
1310 --
1311 dbg('Starting FastFormula check (Ad Hoc).');
1312 --
1313 -- Debugging on the process is enabled by profile HRI:Enable Detailed Logging
1314 --
1315 g_debug_flag := NVL(fnd_profile.value('HRI_ENBL_DTL_LOG'),'N');
1316 --
1317 dbg('profile HRI:Enable Detailed Logging = '|| g_debug_flag);
1318 --
1319 -- If foundation HR mode is detected then do nothing.
1320 --
1321 dbg('Checking if foundation HR mode is selected.');
1322 --
1323 IF check_if_shared_hr
1324 THEN
1325 --
1326 dbg('Foundation HR mode is selected.');
1327 --
1328 RETURN;
1329 --
1330 END iF;
1331 --
1332 -- Check if process is re-entering after being suspended
1333 --
1334 dbg('Checking if process has been re-entered.');
1335 --
1336 IF check_if_re_entered
1337 THEN
1338 --
1339 -- If the process has re-entered with no error the exit.
1340 --
1341 dbg('Process has been re-entered.');
1342 --
1343 RETURN;
1344 --
1345 END IF;
1346 --
1347 -- Call function to generate the fast formula(s)
1348 --
1349 dbg('Checking / building Performance Appraisal FastFormulas.');
1350 --
1351 l_formula_created := gnrt_ss_prfrmnce_apprsl_ff(p_business_group_id);
1352 --
1353 -- If formula(s) have been created, then compile them.
1354 --
1355 dbg('If any Performance Appraisal FastFormulas have been created or are un-compiled then compile them.');
1356 --
1357 IF l_formula_created
1358 THEN
1359 --
1360 dbg('Request Performance Appraisal FastFormula compilation.');
1361 --
1362 compile_prfrmnc_apprsl_ff;
1363 --
1364 dbg('FastFormula compilation requested.');
1365 --
1366 END IF;
1367 --
1368 dbg('Finished FastFormula check (Ad Hoc).');
1369 --
1370 EXCEPTION
1371 --
1372 WHEN OTHERS
1373 THEN
1374 --
1375 dbg('Execption has occurred in gnrt_ss_prfrmnce_apprsl_ff.');
1376 --
1377 failure_exit_message;
1378 --
1379 errbuf := SQLERRM;
1380 retcode := SQLCODE;
1381 --
1382 g_msg_sub_group := NVL(g_msg_sub_group, 'GNRT_SS_PRFRMNCE_APPRSL_FF');
1383 --
1384 RAISE;
1385 --
1386 --
1387 END gnrt_ss_prfrmnce_apprsl_ff;
1388 --
1389 -- ----------------------------------------------------------------------------
1390 -- This procedure deletes performance appraisal fastformulas for a specific
1391 -- business group.
1392 --
1393 PROCEDURE delete_performance_formula(p_business_group_id IN NUMBER)
1394 IS
1395 --
1396 -- find the rowid of the fast_formula that we want to delete
1397 --
1398 CURSOR csr_formula_rowid (cp_business_group_id NUMBER) IS
1399 SELECT rowid
1400 FROM ff_formulas_f
1401 WHERE formula_name = c_prf_aprsl_ff_name
1402 AND business_group_id = cp_business_group_id;
1403 --
1404 l_row_id VARCHAR2(30);
1405 --
1406 BEGIN
1407 --
1408 -- Find the rowid for the fastformula
1409 --
1410 OPEN csr_formula_rowid (p_business_group_id);
1411 FETCH csr_formula_rowid INTO l_row_id;
1412 CLOSE csr_formula_rowid;
1413 --
1414 -- call API to delete the fast formula
1415 --
1416 ff_formulas_f_pkg.Delete_Row
1417 (
1418 X_Rowid => l_row_id
1419 ,X_Formula_Id => NULL
1420 ,X_Dt_Delete_Mode => 'DELETE'
1421 ,X_Validation_Start_Date => NULL
1422 ,X_Validation_End_Date => NULL
1423 );
1424 --
1425 -- COMMIT the formula delete to the database.
1426 --
1427 COMMIT;
1428 --
1429 dbg('Formula has been successfully deleted ...');
1430 --
1431 EXCEPTION
1432 --
1433 WHEN OTHERS
1434 THEN
1435 --
1436 msg(fnd_message.get);
1437 dbg('An error was encountered while attempting to delete a fastformula');
1438 --
1439 g_msg_sub_group := NVL(g_msg_sub_group, 'DELETE_PERFORMANCE_FORMULA');
1440 --
1441 RAISE;
1442 --
1443 END delete_performance_formula;
1444 --
1445 -- ----------------------------------------------------------------------------
1446 -- This procedure deletes all performance appraisal fastformulas
1447 --
1448 PROCEDURE delete_all_prfrmnc_formulas
1449 IS
1450 --
1451 -- find all performance appraisal fast formulas
1452 --
1453 CURSOR csr_all_prfrmnc_formulas IS
1454 SELECT rowid
1455 FROM ff_formulas_f
1456 WHERE formula_name = c_prf_aprsl_ff_name;
1457 --
1458 l_all_prfrmnc_formulas csr_all_prfrmnc_formulas%ROWTYPE;
1459 --
1460 l_row_id VARCHAR2(30);
1461 --
1462 BEGIN
1463 --
1464 FOR l_all_prfrmnc_formulas IN csr_all_prfrmnc_formulas
1465 LOOP
1466 --
1467 ff_formulas_f_pkg.Delete_Row
1468 (
1469 X_Rowid => l_all_prfrmnc_formulas.rowid
1470 ,X_Formula_Id => NULL
1471 ,X_Dt_Delete_Mode => 'DELETE'
1472 ,X_Validation_Start_Date => NULL
1473 ,X_Validation_End_Date => NULL
1474 );
1475 --
1476 END LOOP;
1477 --
1478 -- COMMIT the formula deletes to the database.
1479 --
1480 COMMIT;
1481 --
1482 dbg('Formulas have been successfully deleted ...');
1483 --
1484 EXCEPTION
1485 --
1486 WHEN OTHERS
1487 THEN
1488 --
1489 msg(fnd_message.get);
1490 dbg('An error was encountered while attempting to delete fastformulas');
1491 --
1492 g_msg_sub_group := NVL(g_msg_sub_group, 'DELETE_ALL_PRFRMNC_FORMULAS');
1493 --
1494 RAISE;
1495 --
1496 END delete_all_prfrmnc_formulas;
1497 --
1498 -- ----------------------------------------------------------------------------
1499 -- This is a standard entry point.
1500 -- This procedure controls the dynamic creation of DBI related fastformulas.
1501 -- This process is designed to be called from a full refresh request set.
1502 --
1503 -- The steps it follows are:
1504 --
1505 -- 1. For all business groups that have appraisal templates, generate a
1506 -- default performance appraisal template.
1507 --
1508 -- 2. If no PUI appraisal formula exists, create a default formula, where
1509 -- all ratings are set to 50% [NOT YET CODED]
1510 --
1511 --
1512 PROCEDURE fastformula_check_full
1513 (
1514 errbuf OUT NOCOPY VARCHAR2
1515 ,retcode OUT NOCOPY NUMBER
1516 )
1517 IS
1518 --
1519 -- Indocates if any formulas have been created.
1520 --
1521 l_compile_reqd BOOLEAN DEFAULT FALSE;
1522 --
1523 BEGIN
1524 --
1525 dbg('Starting FastFormula check (Full Refresh).');
1526 --
1527 hri_bpl_conc_log.record_process_start('HRI_BPL_FAST_FORMULA');
1528 --
1529 dbg('profile HRI:Enable Detailed Logging = '|| g_debug_flag);
1530 --
1531 -- If foundation HR mode is detected then do nothing.
1532 --
1533 dbg('Checking if foundation HR mode is selected.');
1534 --
1535 IF check_if_shared_hr
1536 THEN
1537 --
1538 dbg('Foundation HR mode is selected.');
1539 --
1540 RETURN;
1541 --
1542 END iF;
1543 --
1544 -- Check if process is re-entering after being suspended
1545 --
1546 dbg('Checking if process has been re-entered.');
1547 --
1548 IF check_if_re_entered
1549 THEN
1550 --
1551 -- If the process has re-entered with no error the exit.
1552 --
1553 dbg('Process has been re-entered.');
1554 --
1555 RETURN;
1556 --
1557 END IF;
1558 --
1559 -- Call function to generate the fast formula(s). Pass NULL in so that
1560 -- it will run for all formulas.
1561 --
1562 dbg('Checking / building Performance Appraisal FastFormulas.');
1563 --
1564 l_compile_reqd := gnrt_ss_prfrmnce_apprsl_ff(NULL);
1565 --
1566 -- If formula(s) have been created, then compile them.
1567 --
1568 dbg('If any Performance Appraisal FastFormulas have been created or are un-compiled then compile them.');
1569 --
1570 IF l_compile_reqd
1571 THEN
1572 --
1573 dbg('Request Performance Appraisal FastFormula compilation.');
1574 --
1575 compile_prfrmnc_apprsl_ff;
1576 --
1577 dbg(' Performance Appraisal FastFormula compilation requested.');
1578 --
1579 END IF;
1580 --
1581 dbg('Finished FastFormula check (Full Refresh).');
1582 --
1583 -- Bug 4105868: Collection Diagnostics
1584 --
1585 hri_bpl_conc_log.log_process_end
1586 (p_status => TRUE
1587 ,p_period_from => hr_general.start_of_time
1588 ,p_period_to => hr_general.end_of_time
1589 ,p_attribute1 => 'Y'
1590 );
1591 EXCEPTION
1592 --
1593 WHEN OTHERS THEN
1594 --
1595 dbg('Execption has occurred in fastformula_check_full.');
1596 --
1597 handle_exit_exception;
1598 --
1599 errbuf := SQLERRM;
1600 retcode := SQLCODE;
1601 --
1602 -- Bug 4105868: Collection Diagnostics
1603 --
1604 g_msg_sub_group := NVL(g_msg_sub_group, 'FASTFORMULA_CHECK_FULL');
1605 --
1606 hri_bpl_conc_log.log_process_info
1607 (p_package_name => 'HRI_BPL_FAST_FORMULA'
1608 ,p_msg_type => 'ERROR'
1609 ,p_msg_group => 'FST_FTML_CHCK'
1610 ,p_msg_sub_group => g_msg_sub_group
1611 ,p_sql_err_code => SQLCODE
1612 ,p_note => SQLERRM);
1613 --
1614 hri_bpl_conc_log.log_process_end
1615 (p_status => FALSE
1616 ,p_period_from => hr_general.start_of_time
1617 ,p_period_to => hr_general.end_of_time
1618 ,p_attribute1 => 'Y'
1619 );
1620 --
1621 RAISE;
1622 --
1623 --
1624 END fastformula_check_full;
1625 --
1626 -- ----------------------------------------------------------------------------
1627 -- This is a standard entry point:
1628 -- This procedure controls the dynamic creation of DBI related fastformulas.
1629 --
1630 -- This process is designed to be called from an incremental refresh request
1631 -- set.
1632 --
1633 -- The steps it follows are:
1634 --
1635 -- 1. For all business groups that have appraisal templates, generate a
1636 -- default performance appraisal template.
1637 --
1638 -- 2. If no PUI appraisal formula exists, create a default formula, where
1639 -- all ratings are set to 50% [NOT YET CODED]
1640 --
1641 PROCEDURE fastformula_check_incr
1642 (
1643 errbuf OUT NOCOPY VARCHAR2
1644 ,retcode OUT NOCOPY NUMBER
1645 )
1646 IS
1647 --
1648 -- Indocates if any formulas have been created.
1649 --
1650 l_compile_reqd BOOLEAN DEFAULT FALSE;
1651 --
1652 BEGIN
1653 --
1654 dbg('Starting FastFormula check (Incremental Refresh).');
1655 --
1656 hri_bpl_conc_log.record_process_start('HRI_BPL_FAST_FORMULA');
1657 --
1658 -- If foundation HR mode is detected then do nothing.
1659 --
1660 dbg('Checking if foundation HR mode is selected.');
1661 --
1662 IF check_if_shared_hr
1663 THEN
1664 --
1665 dbg('Foundation HR mode is selected.');
1666 --
1667 RETURN;
1668 --
1669 END iF;
1670 --
1671 -- Check if process is re-entering after being suspended
1672 --
1673 dbg('Checking if process has been re-entered.');
1674 --
1675 IF check_if_re_entered
1676 THEN
1677 --
1678 -- If the process has re-entered with no error the exit.
1679 --
1680 dbg('Process has been re-entered.');
1681 --
1682 RETURN;
1683 --
1684 END IF;
1685 --
1686 -- Call function to generate the fast formula(s). Pass NULL in so that
1687 -- it will run for all formulas.
1688 --
1689 dbg('Checking / building Performance Appraisal FastFormulas.');
1690 --
1691 l_compile_reqd := gnrt_ss_prfrmnce_apprsl_ff(NULL);
1692 --
1693 -- If formula(s) have been created, then compile them.
1694 --
1695 dbg('If any Performance Appraisal FastFormulas have been created or are un-compiled then compile them.');
1696 --
1697 IF l_compile_reqd
1698 THEN
1699 --
1700 dbg('Request Performance Appraisal FastFormula compilation.');
1701 --
1702 compile_prfrmnc_apprsl_ff;
1703 --
1704 dbg('FastFormula compilation requested.');
1705 --
1706 END IF;
1707 --
1708 dbg('Finished FastFormula check (Incremental Refresh).');
1709 --
1710 -- Bug 4105868: Collection Diagnostics
1711 --
1712 hri_bpl_conc_log.log_process_end
1713 (p_status => TRUE
1714 ,p_period_from => hr_general.start_of_time
1715 ,p_period_to => hr_general.end_of_time
1716 ,p_attribute1 => 'N'
1717 );
1718 --
1719 EXCEPTION
1720 --
1721 WHEN OTHERS
1722 THEN
1723 --
1724 dbg('Execption has occurred in fastformula_check_incr.');
1725 --
1726 handle_exit_exception;
1727 --
1728 errbuf := SQLERRM;
1729 retcode := SQLCODE;
1730 --
1731 -- Bug 4105868: Collection Diagnostics
1732 --
1733 g_msg_sub_group := NVL(g_msg_sub_group, 'FASTFORMULA_CHECK_INCR');
1734 --
1735 hri_bpl_conc_log.log_process_info
1736 (p_package_name => 'HRI_BPL_FAST_FORMULA'
1737 ,p_msg_type => 'ERROR'
1738 ,p_msg_group => 'FST_FTML_CHCK'
1739 ,p_msg_sub_group => g_msg_sub_group
1740 ,p_sql_err_code => SQLCODE
1741 ,p_note => SQLERRM);
1742 --
1743 hri_bpl_conc_log.log_process_end
1744 (p_status => FALSE
1745 ,p_period_from => hr_general.start_of_time
1746 ,p_period_to => hr_general.end_of_time
1747 ,p_attribute1 => 'N'
1748 );
1749 --
1750 RAISE;
1751 --
1752 --
1753 END fastformula_check_incr;
1754 --
1755 END hri_bpl_fast_formula;
[Home] [Help]