4 -- |--< comments >------------------------------------------------------------|
1 PACKAGE HR_GENERAL_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: hrgenutw.pkh 115.13 2002/12/11 11:35:14 hjonnala ship $*/
3 -- ----------------------------------------------------------------------------
5 -- ----------------------------------------------------------------------------
6 -- NOTE:
10 -- function (or the parent of that package if it is a child).
7 -- This package must not have any print calls (to htp.p or procedures that
8 -- contain it, or anything else, such as javascript alert);
9 -- errors (as exceptions) must be handled in the packages which called the
11 -- package prepared in part from: (comments and dates remain in the code for
12 -- cross-referencing)
13 -- hr_util_web Header: hrutlweb.pkb 110.16 97/12/05
14 -- per_cm_util_web Header: pecmuweb.pkb 110.19 97/11/19
15 -- ----------------------------------------------------------------------------
16 -- |--< Types >---------------------------------------------------------------|
17 -- ----------------------------------------------------------------------------
18 TYPE g_varchar2_tab_type
19 IS TABLE OF
20 VARCHAR2 (2000)
21 INDEX BY BINARY_INTEGER;
22 --
23 TYPE g_vc32k_tab_type
24 IS TABLE OF
25 VARCHAR2 (32000)
26 INDEX BY BINARY_INTEGER;
27 --
28 TYPE g_number_tab_type
29 IS TABLE OF
30 NUMBER
31 INDEX BY BINARY_INTEGER;
32 --
33 TYPE r_column_data_rec
34 IS RECORD
35 ( f_precision NUMBER
36 , f_datatype VARCHAR2 (200)
37 );
38 --
39 TYPE g_lookup_values_rec_type
40 IS RECORD
41 ( lookup_type VARCHAR2 (100)
42 , lookup_code VARCHAR2 (200)
43 , meaning VARCHAR2 (2000)
44 );
45 --
46 TYPE g_person_details_rec_type
47 IS RECORD
48 ( last_name per_all_people_f.last_name%TYPE
49 , first_name per_all_people_f.first_name%TYPE
50 , full_name per_all_people_f.full_name%TYPE
51 , middle_names per_all_people_f.middle_names%TYPE
52 , previous_last_name per_all_people_f.previous_last_name%TYPE
53 , suffix per_all_people_f.suffix%TYPE
54 , title per_all_people_f.title%TYPE
55 , business_group_id per_all_people_f.business_group_id%TYPE
56 );
57 --
58 TYPE g_lookup_values_tab_type
59 IS TABLE OF
60 g_lookup_values_rec_type
61 INDEX BY BINARY_INTEGER;
62 --
63 TYPE r_error_msg_txt_number_rec
64 IS RECORD
65 ( error_text VARCHAR2 (2000)
66 , error_number NUMBER
67 );
68 --
69 -- ----------------------------------------------------------------------------
70 -- |--< GLOBALS >-------------------------------------------------------------|
71 -- ----------------------------------------------------------------------------
72 g_package VARCHAR2 (200) := 'HR_GENERAL_UTILITIES';
73 g_separator VARCHAR2 (2) := '!#';
74 g_sysdate_char VARCHAR2(200) :=
75 to_char(trunc(sysdate), 'YYYY-MM-DD');
76 g_current_yr_char VARCHAR2(4) := substr(g_sysdate_char, 1, 4);
77 g_sample_date_char VARCHAR2(200) := g_current_yr_char || '-12-31';
78 g_sample_date DATE :=
79 to_date(g_sample_date_char, 'YYYY-MM-DD');
80 g_date_format VARCHAR2 (200)
81 := hr_session_utilities.get_user_date_format;
82 g_attribute_application_id NUMBER := 800;
83 d_varchar2_tab_type g_varchar2_tab_type;
84 d_number_tab_type g_number_tab_type;
85 --
86 -- ----------------------------------------------------------------------------
87 -- |--< reset_globals >-------------------------------------------------------|
88 -- | This procedure will be called at the end of rendering the work space |
89 -- | frame so that the global variables will have the initialized values in |
90 -- | WebDB stateful connection. |
91 -- ----------------------------------------------------------------------------
92 PROCEDURE reset_globals;
93 --
94 -- ----------------------------------------------------------------------------
95 -- |--< Get_Person_Record >---------------------------------------------------|
96 -- ----------------------------------------------------------------------------
97 -- {Start Of Comments}
98 --
99 -- Description:
100 -- get the person record at the given date. This method uses the secured view
101 -- per_people_f which requires us to do an insert and delete of sessionId in
102 -- FND_SESSIONS
103 --
104 -- Prerequisites:
105 -- person must exist
106 --
107 -- Post Success:
108 -- the person record is returned
109 --
110 -- Post Failure:
111 -- an error is raised
112 --
113 -- Access Status:
117 -- ----------------------------------------------------------------------------
114 -- Public
115 --
116 -- {End Of Comments}
118 FUNCTION Get_Person_Record
119 ( p_person_id IN per_people_f.person_id%TYPE
120 , p_effective_date IN DATE DEFAULT SYSDATE
121 )
122 RETURN per_people_f%ROWTYPE;
123 -- ----------------------------------------------------------------------------
124 -- |--< Get_Person_Details >---------------------------------------------------|
125 -- ----------------------------------------------------------------------------
126 -- {Start Of Comments}
127 --
128 -- Description:
129 -- get the person details record at the given date. we go to the base table
130 -- per_all_people_f to get the information. This method is faster than using
131 -- Get_person_record which uses the secured view per_people_f which also has
132 -- an overhead of inserting and deleting row from FND_SESSIONS
133 --
134 -- Prerequisites:
135 -- person must exist
136 --
137 -- Post Success:
138 -- the person details record is returned
139 --
140 -- Post Failure:
141 -- an error is raised
142 --
143 -- Access Status:
144 -- Public
145 --
146 -- {End Of Comments}
147 -- ----------------------------------------------------------------------------
148 FUNCTION Get_Person_Details
149 ( p_person_id IN per_all_people_f.person_id%TYPE
150 , p_effective_date IN DATE DEFAULT SYSDATE
151 )
152 RETURN g_person_details_rec_type;
153
154 -- ----------------------------------------------------------------------------
155 -- |--< Get_Business_Group >--------------------------------------------------|
156 -- ----------------------------------------------------------------------------
157 -- {Start Of Comments}
158 --
159 -- Description:
160 -- returns the business group of the currently logged in user (the session
161 -- business group)
162 --
163 -- Prerequisites:
164 -- person must exist and be logged in
165 --
166 -- Post Success:
167 -- the business group id is returned
168 --
169 -- Post Failure:
170 -- an error is raised
171 --
172 -- Access Status:
173 -- Public
174 --
175 -- {End Of Comments}
176 -- ----------------------------------------------------------------------------
177 FUNCTION Get_Business_Group
178 RETURN per_people_f.business_group_id%TYPE;
179 -- ----------------------------------------------------------------------------
180 -- |--< Use_Message >---------------------------------------------------------|
181 -- ----------------------------------------------------------------------------
182 -- {Start Of Comments}
183 --
184 -- Description:
185 -- sets a message on the stack and retrieves it. requires the FND message_name
186 --
187 -- Prerequisites:
188 --
189 --
190 -- Post Success:
191 -- the text of the message is returned
192 --
193 -- Post Failure:
194 -- the message name is returned
195 --
196 -- Access Status:
197 -- Public
198 --
199 -- {End Of Comments}
200 -- ----------------------------------------------------------------------------
201 FUNCTION Use_Message
202 ( p_message_name IN VARCHAR2
203 , p_application_id IN VARCHAR2 DEFAULT 'PER'
204 )
205 RETURN VARCHAR2;
206 -- ----------------------------------------------------------------------------
207 -- |--< IFNOTNULL >-----------------------------------------------------------|
208 -- ----------------------------------------------------------------------------
209 -- {Start Of Comments}
210 --
211 -- Description:
212 -- if str1 is null returns str2, else str1 (from htf)
213 --
214 -- Prerequisites:
215 --
216 --
217 -- Post Success:
218 --
219 --
220 -- Post Failure:
221 --
222 --
223 -- Access Status:
224 -- Public
225 --
226 -- {End Of Comments}
227 -- ----------------------------------------------------------------------------
228 FUNCTION IFNOTNULL
229 ( str1 IN VARCHAR2
230 , str2 IN VARCHAR2)
231 RETURN VARCHAR2;
232 -- ----------------------------------------------------------------------------
233 -- |--< Substitute_Value >----------------------------------------------------|
234 -- ----------------------------------------------------------------------------
235 -- {Start Of Comments}
236 --
237 -- Description:
238 -- substitutes a value if the new value is not null compared to a not null
239 -- original value
240 --
241 -- Prerequisites:
242 -- none
243 --
244 -- Post Success:
245 -- the new value is substituted for the old value if the new value was not
246 -- null
247 --
248 -- Post Failure:
249 -- n/a
250 --
251 -- Access Status:
252 -- Public
253 --
254 -- {End Of Comments}
255 -- ----------------------------------------------------------------------------
256 PROCEDURE Substitute_Value
257 ( p_new IN VARCHAR2 DEFAULT NULL
258 , p_current IN OUT NOCOPY VARCHAR2
259 , p_force IN BOOLEAN DEFAULT FALSE
260 );
261 -- |--< overloaded >----------------------------------------------------------|
262 PROCEDURE Substitute_Value
263 ( p_new IN NUMBER DEFAULT NULL
264 , p_current IN OUT NOCOPY NUMBER
265 , p_force IN BOOLEAN DEFAULT FALSE
266 );
267 -- |--< overloaded >----------------------------------------------------------|
268 FUNCTION Substitute_Value
269 ( p_new IN VARCHAR2 DEFAULT NULL
270 , p_current IN VARCHAR2 DEFAULT NULL
271 , p_force IN BOOLEAN DEFAULT FALSE
272 )
273 RETURN VARCHAR2;
274 -- |--< overloaded >----------------------------------------------------------|
275 FUNCTION Substitute_Value
276 ( p_new IN BOOLEAN DEFAULT NULL
280 RETURN BOOLEAN;
277 , p_current IN BOOLEAN DEFAULT NULL
278 , p_force IN BOOLEAN DEFAULT FALSE
279 )
281 -- ----------------------------------------------------------------------------
282 -- |--< date2char >-----------------------------------------------------------|
283 -- ----------------------------------------------------------------------------
284 -- {Start Of Comments}
285 --
286 -- Description:
287 -- converts a date to character representation, using the default date format
288 -- if none specified
289 --
290 -- Prerequisites:
291 -- none
292 --
293 -- Post Success:
294 -- the date is formatted
295 --
296 -- Post Failure:
297 -- n/a
298 --
299 -- Access Status:
300 -- Public
301 --
302 -- {End Of Comments}
303 -- ----------------------------------------------------------------------------
304 FUNCTION date2char
305 ( p_date IN DATE
306 , p_date_format IN VARCHAR2 DEFAULT g_date_format
307 )
308 RETURN VARCHAR2;
309 -- ----------------------------------------------------------------------------
310 -- |--< char2date >-----------------------------------------------------------|
311 -- ----------------------------------------------------------------------------
312 -- {Start Of Comments}
313 --
314 -- Description:
315 -- converts text date in the given date format to date datatype
316 --
317 -- Prerequisites:
318 -- none
319 --
320 -- Post Success:
321 -- the text is converted to date datatype
322 --
323 -- Post Failure:
324 -- an error is raised
325 --
326 -- Access Status:
327 -- Public
328 --
329 -- {End Of Comments}
330 -- ----------------------------------------------------------------------------
331 FUNCTION char2date
332 ( p_char_date IN VARCHAR2
333 , p_date_format IN VARCHAR2
334 )
335 RETURN date;
336 -- ----------------------------------------------------------------------------
337 -- |--< IsDateValid >---------------------------------------------------------|
338 -- ----------------------------------------------------------------------------
339 -- {Start Of Comments}
340 --
341 -- Description:
342 -- tests whether a character format of a date can be converted to date
343 -- datatype (using the default date format)
344 --
345 -- Prerequisites:
346 -- none
347 --
348 -- Post Success:
349 -- returns true
350 --
351 -- Post Failure:
352 -- returns false
353 --
354 -- Access Status:
355 -- Public
356 --
357 -- {End Of Comments}
358 -- ----------------------------------------------------------------------------
359 FUNCTION IsDateValid
360 ( p_string IN VARCHAR2
361 )
362 RETURN BOOLEAN;
363 -- ----------------------------------------------------------------------------
364 -- |--< Validate_Between_Dates >----------------------------------------------|
365 -- ----------------------------------------------------------------------------
366 -- {Start Of Comments}
367 --
368 -- Description:
369 -- determines whether date 1 is lower than date 2
370 --
371 -- Prerequisites:
372 -- none
373 --
374 -- Post Success:
375 -- returns true if date 1 < date 2
376 --
377 -- Post Failure:
378 -- returns false
379 --
380 -- Access Status:
381 -- Public
382 --
383 -- {End Of Comments}
384 -- ----------------------------------------------------------------------------
385 FUNCTION Validate_Between_Dates
386 ( p_date1 IN DATE
387 , p_date2 IN DATE
388 )
389 RETURN BOOLEAN;
390 -- ----------------------------------------------------------------------------
391 -- |--< Force_Date_Format >---------------------------------------------------|
392 -- ----------------------------------------------------------------------------
393 -- {Start Of Comments}
394 --
395 -- Description:
396 -- forces a valid date as text string (checks if valid date) to the current
397 -- g_date_format (as text_string)
398 --
399 -- Prerequisites:
400 -- none
401 --
402 -- Post Success:
403 --
404 --
405 -- Post Failure:
406 --
407 --
408 -- Access Status:
409 -- Public
410 --
411 -- {End Of Comments}
412 -- ----------------------------------------------------------------------------
413 FUNCTION Force_Date_Format
414 ( p_char_date IN VARCHAR2
415 )
416 RETURN VARCHAR2;
417 -- ----------------------------------------------------------------------------
418 -- |--< Get_Column_Length >---------------------------------------------------|
419 -- ----------------------------------------------------------------------------
420 -- {Start Of Comments}
421 --
422 -- Description:
423 -- returns the column length available for the given column / table
424 --
425 -- Prerequisites:
426 -- table / column must exist
427 --
428 -- Post Success:
429 -- a value is returned
430 --
431 -- Post Failure:
432 -- an error is raised
433 --
434 -- Access Status:
435 -- Public
436 --
437 -- {End Of Comments}
438 -- ----------------------------------------------------------------------------
439 FUNCTION Get_Column_Data
440 ( p_table_name VARCHAR2
441 , p_column_name VARCHAR2
442 )
443 RETURN r_column_data_rec;
444 -- ----------------------------------------------------------------------------
445 -- |--< Get_lookup_Meaning >--------------------------------------------------|
446 -- ----------------------------------------------------------------------------
447 -- {Start Of Comments}
448 --
449 -- Description:
450 -- returns a lookup meaning for the given type / code
451 --
455 -- Post Success:
452 -- Prerequisites:
453 --
454 --
456 -- a value is returned
457 --
458 -- Post Failure:
459 -- no value is returned
460 --
461 -- Access Status:
462 -- Public
463 --
464 -- {End Of Comments}
465 -- ----------------------------------------------------------------------------
466 FUNCTION Get_lookup_Meaning
467 ( p_lookup_type IN VARCHAR2
468 , p_lookup_code IN VARCHAR2
469 , p_schema IN VARCHAR2 DEFAULT 'HR'
470 )
471 RETURN VARCHAR2;
472 -- ----------------------------------------------------------------------------
473 -- |--< Get_lookup_values >---------------------------------------------------|
474 -- ----------------------------------------------------------------------------
475 -- {Start Of Comments}
476 --
477 -- Description:
478 -- returns the lookup codes for the given lookup type
479 --
480 -- Prerequisites:
481 --
482 --
483 -- Post Success:
484 -- values are returned
485 --
486 -- Post Failure:
487 -- no value are returned
488 --
489 -- Access Status:
490 -- Public
491 --
492 -- {End Of Comments}
493 -- ----------------------------------------------------------------------------
494 FUNCTION Get_lookup_values
495 ( p_lookup_type IN VARCHAR2
496 , p_schema IN VARCHAR2 DEFAULT 'HR'
497 )
498 RETURN g_lookup_values_tab_type;
499 -- ----------------------------------------------------------------------------
500 -- |--< DoLookupsExist >------------------------------------------------------|
501 -- ----------------------------------------------------------------------------
502 -- {Start Of Comments}
503 --
504 -- Description:
505 -- tests whether a lookup type exists
506 --
507 -- Prerequisites:
508 --
509 --
510 -- Post Success:
511 -- returns true
512 --
513 -- Post Failure:
514 -- returns false
515 --
516 -- Access Status:
517 -- Public
518 --
519 -- {End Of Comments}
520 -- ----------------------------------------------------------------------------
521 FUNCTION DoLookupsExist
522 ( p_lookup_type IN VARCHAR2
523 , p_schema IN VARCHAR2 DEFAULT 'HR'
524 )
525 RETURN BOOLEAN;
526 -- ----------------------------------------------------------------------------
527 -- |--< ScriptOpen >----------------------------------------------------------|
528 -- ----------------------------------------------------------------------------
529 -- {Start Of Comments}
530 --
531 -- Description:
532 -- writes out the html tag <SCRIPT>
533 --
534 -- Prerequisites:
535 --
536 --
537 -- Post Success:
538 -- tag is printed
539 --
540 -- Post Failure:
541 --
542 --
543 -- Access Status:
544 -- Public
545 --
546 -- {End Of Comments}
547 -- ----------------------------------------------------------------------------
548 PROCEDURE ScriptOpen
549 ( p_js_library IN VARCHAR2 DEFAULT NULL
550 );
551 -- ----------------------------------------------------------------------------
552 -- |--< ScriptOpen >----------------------------------------------------------|
553 -- ----------------------------------------------------------------------------
554 -- {Start Of Comments}
555 --
556 -- Description:
557 -- writes out the html tag </SCRIPT>
558 --
559 -- Prerequisites:
560 --
561 --
562 -- Post Success:
563 -- tag is printed
564 --
565 -- Post Failure:
566 --
567 --
568 -- Access Status:
569 -- Public
570 --
571 -- {End Of Comments}
572 -- ----------------------------------------------------------------------------
573 PROCEDURE ScriptClose;
574 -- ----------------------------------------------------------------------------
575 -- |--< Add_Separators >------------------------------------------------------|
576 -- ----------------------------------------------------------------------------
577 -- {Start Of Comments}
578 --
579 -- Description:
580 -- adds separators to a string to create e.g. !#12345!#. Note an error is not
581 -- raised if the string is the empty string. However, this may raise an error
582 -- later if trying extract an item from a separated string that has successive
583 -- separators (e.g. !#!#)
584 --
585 -- Prerequisites:
586 -- separator must be defined
587 --
588 -- Post Success:
589 -- separator is appended to the start (p_start is true) or to the end of
590 -- the string
591 --
592 -- Post Failure:
593 --
594 --
595 -- Access Status:
596 -- Public
597 --
598 -- {End Of Comments}
599 -- ----------------------------------------------------------------------------
600 FUNCTION Add_Separators
601 ( p_instring IN VARCHAR2
602 , p_start IN BOOLEAN DEFAULT FALSE
603 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
604 )
605 RETURN VARCHAR2;
606 -- ----------------------------------------------------------------------------
607 -- |--< Locate_Item_In_Separated_Str >----------------------------------------|
608 -- ----------------------------------------------------------------------------
609 -- {Start Of Comments}
610 --
611 -- Description:
612 -- returns the character position of an item in a separated string
613 --
614 -- Prerequisites:
615 --
616 --
617 -- Post Success:
618 -- the character position is returned
619 --
620 -- Post Failure:
621 --
622 --
623 -- Access Status:
624 -- Public
625 --
626 -- {End Of Comments}
627 -- ----------------------------------------------------------------------------
628 FUNCTION Locate_Item_In_Separated_Str
632 )
629 ( p_string IN VARCHAR2
630 , p_item IN NUMBER
631 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
633 RETURN NUMBER;
634 -- ----------------------------------------------------------------------------
635 -- |--< Find_Item_In_String >-------------------------------------------------|
636 -- ----------------------------------------------------------------------------
637 -- {Start Of Comments}
638 --
639 -- Description:
640 -- returns the contents of an item in a separated string e.g. !#12345!#
641 -- returns 12345
642 --
643 -- Prerequisites:
644 --
645 --
646 -- Post Success:
647 -- the string is returned
648 --
649 -- Post Failure:
650 -- returns null
651 --
652 -- Access Status:
653 -- Public
654 --
655 -- {End Of Comments}
656 -- ----------------------------------------------------------------------------
657 FUNCTION Find_Item_In_String
658 ( p_item IN NUMBER
659 , p_string IN VARCHAR2
660 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
661 )
662 RETURN VARCHAR2;
663 -- ----------------------------------------------------------------------------
664 -- |--< Trim_Separator >------------------------------------------------------|
665 -- ----------------------------------------------------------------------------
666 -- {Start Of Comments}
667 --
668 -- Description:
669 -- trims a separator from a given string
670 --
671 -- Prerequisites:
672 --
673 --
674 -- Post Success:
675 -- the string is returned without the separator
676 --
677 -- Post Failure:
678 -- returns null
679 --
680 -- Access Status:
681 -- Public
682 --
683 -- {End Of Comments}
684 -- ----------------------------------------------------------------------------
685 FUNCTION Trim_Separator
686 ( p_string IN VARCHAR2
687 , p_end IN VARCHAR2 DEFAULT 'RIGHT'
688 , p_separator IN VARCHAR2 DEFAULT hr_general_utilities.g_separator
689 )
690 RETURN VARCHAR2;
691 -- ----------------------------------------------------------------------------
692 -- |--< BPFD >----------------------------------------------------------------|
693 -- ----------------------------------------------------------------------------
694 -- {Start Of Comments}
695 --
696 -- Description:
697 -- (build parameters from decryption) takes a separated string and extracts
698 -- the items to create a dynamic sql executable string
699 --
700 -- Prerequisites:
701 --
702 --
703 -- Post Success:
704 -- the string is converted to a string which can be executed in dynamic sql
705 --
706 -- Post Failure:
707 -- returns null
708 --
709 -- Access Status:
710 -- Public
711 --
712 -- {End Of Comments}
713 -- ----------------------------------------------------------------------------
714 FUNCTION BPFD
715 ( p_string IN VARCHAR2
716 )
717 RETURN VARCHAR2;
718 --
719 -- ----------------------------------------------------------------------------
720 -- |--< BPFD >-----------------------------------------------------------------|
721 -- | This function is overloaded. We keep the same name as BPFD because we |
722 -- | want to make it easier to associate the code to the original BPFD. |
723 -- | However, this function will replace all hard-coded literals in the parm |
724 -- | values with bind variables for scalability. The bind values are stored in|
725 -- | in the output parameter p_bind_values_tab of this overloaded function. |
726 -- | So, the procedure invocation will look like this: |
727 -- | per_appraisal_display_web.aprp01(parm1=> :1, parm2 => :2, ....); |
728 -- ----------------------------------------------------------------------------
729 FUNCTION BPFD
730 ( p_string IN VARCHAR2
731 ,p_bind_values_tab out nocopy hr_general_utilities.g_vc32k_tab_type
732 ,p_use_bind_values out nocopy boolean
733 )
734 RETURN VARCHAR2;
735 --
736 -- ----------------------------------------------------------------------------
737 -- |--< Get_Date_Hint >-------------------------------------------------------|
738 -- ----------------------------------------------------------------------------
739 -- {Start Of Comments}
740 --
741 -- Description:
742 -- returns the date hint (e.g. 31-DEC-1998) in user_date format
743 --
744 -- Prerequisites:
745 --
746 --
747 -- Post Success:
748 -- the date is returned in the correct format
749 --
750 -- Post Failure:
751 --
752 --
753 -- Access Status:
754 -- Public
755 --
756 -- {End Of Comments}
757 -- ----------------------------------------------------------------------------
758 FUNCTION Get_Date_Hint
759 RETURN VARCHAR2;
760 -- ----------------------------------------------------------------------------
761 -- |--< ASEI >----------------------------------------------------------------|
762 -- ----------------------------------------------------------------------------
763 -- {Start Of Comments}
764 --
765 -- Description:
766 -- (add to sql execution index) adds an index to the stack of dynamic sql
767 -- indices (encrypted handles) which need to be executed. i.e. is a container
768 -- array of pointers to encrypted sql strings
769 --
770 -- Prerequisites:
771 --
772 --
773 -- Post Success:
774 --
775 --
776 -- Post Failure:
777 --
778 --
779 -- Access Status:
780 -- Public
781 --
782 -- {End Of Comments}
783 -- ----------------------------------------------------------------------------
784 PROCEDURE ASEI
785 ( p_text_id IN VARCHAR2
786 );
787 -- ----------------------------------------------------------------------------
791 --
788 -- |--< CCEI >----------------------------------------------------------------|
789 -- ----------------------------------------------------------------------------
790 -- {Start Of Comments}
792 -- Description:
793 -- (combine execution indices (encrypted handles)) combines the encryption
794 -- handles of all execution statements on the stack into one index. This takes
795 -- all of the indices created by ASEI and combines them into one encryption id;
796 -- subsequent decoding of this id will eventually lead to the execution of all
797 -- the combined sql statements
798 --
799 -- Prerequisites:
800 --
801 --
802 -- Post Success:
803 --
804 --
805 -- Post Failure:
806 --
807 --
808 -- Access Status:
809 -- Public
810 --
811 -- {End Of Comments}
812 -- ----------------------------------------------------------------------------
813 FUNCTION CCEI
814 RETURN VARCHAR2;
815 -- ----------------------------------------------------------------------------
816 -- |--< EPFS >----------------------------------------------------------------|
817 -- ----------------------------------------------------------------------------
818 -- {Start Of Comments}
819 --
820 -- Description:
821 -- (encrypt parameters for storage [originally]) : encrypts a string, using
822 -- multiple encryption handles if the text length is > than 2000 (max length in
823 -- icx_text); encoding types are :
824 -- S - eventually will be executed (when decrypted)
825 -- G - eventually will be placed in the global cache (when decrypted)
826 --
827 -- Prerequisites:
828 --
829 --
830 -- Post Success:
831 --
832 --
833 -- Post Failure:
834 --
835 --
836 -- Access Status:
837 -- Public
838 --
839 -- {End Of Comments}
840 -- ----------------------------------------------------------------------------
841 FUNCTION EPFS
842 ( p_string IN VARCHAR2
843 , p_type IN VARCHAR2 DEFAULT 'S'
844 )
845 RETURN VARCHAR2;
846 -- ----------------------------------------------------------------------------
847 -- |--< DExL >----------------------------------------------------------------|
848 -- ----------------------------------------------------------------------------
849 -- {Start Of Comments}
850 --
851 -- Description:
852 -- (decrypt and execute link [originally]) decrypts an encryption handle and
853 -- determines what to do with the returned string
854 -- C - indicates that there is a combination of events (created with CCEI) and
855 -- DEXL must deal with these one at a time.
856 -- for each string in a string (including C string from CCEI) the types are :
857 -- S - the string is passed to dynamic sql and executed
858 -- G - the string is placed on the stack
859 --
860 -- Prerequisites:
861 -- the encryption link must be in a recognizable form
862 --
863 -- Post Success:
864 --
865 -- Post Failure:
866 -- n/a
867 --
868 -- Access Status:
869 -- Public
870 --
871 -- {End Of Comments}
872 -- ----------------------------------------------------------------------------
873 PROCEDURE DExL
874 ( i IN VARCHAR2
875 );
876 -- ----------------------------------------------------------------------------
877 -- |--< SDER >----------------------------------------------------------------|
878 -- ----------------------------------------------------------------------------
879 -- {Start Of Comments}
880 --
881 -- Description:
882 -- (standard decrypt execute route) returns the value
883 -- 'hr_general_utilities.dexl?i='
884 --
885 -- Prerequisites:
886 --
887 -- Post Success:
888 --
889 -- Post Failure:
890 -- n/a
891 --
892 -- Access Status:
893 -- Public
894 --
895 -- {End Of Comments}
896 -- ----------------------------------------------------------------------------
897 FUNCTION SDER
898 RETURN VARCHAR2;
899 -- ----------------------------------------------------------------------------
900 -- |--< EXPD >----------------------------------------------------------------|
901 -- ----------------------------------------------------------------------------
902 -- {Start Of Comments}
903 --
904 -- Description:
905 -- (expand parameters from decrypt [originally]) rebuilds strings which needed
906 -- to be broken dowm into strings of 2000 in length. dexl (see above) may
907 -- encounter the S command followed by the number 3 followed by three numbers;
908 -- dexl knows that the eventual string needs to be executed dynamically (S),
909 -- and that the string needs to be rebuilt from 3 encrypted strings. EXPD
910 -- decrypts and concatenates the three numbers (text ids in icx_text) to
911 -- generate the complete string to pass to the dexl execution routine
912 --
913 -- Prerequisites:
914 --
915 -- Post Success:
916 --
917 -- Post Failure:
918 -- n/a
919 --
920 -- Access Status:
921 -- Public
922 --
923 -- {End Of Comments}
924 -- ----------------------------------------------------------------------------
925 FUNCTION EXPD
926 ( p_id IN VARCHAR2 DEFAULT NULL
927 , p_string IN VARCHAR2 DEFAULT NULL
928 )
929 RETURN VARCHAR2;
930 -- ----------------------------------------------------------------------------
931 -- |--< REGS >----------------------------------------------------------------|
932 -- ----------------------------------------------------------------------------
933 -- {Start Of Comments}
934 --
935 -- Description:
936 -- (retrieve from global stack) retrieves the p_item item from the global stack
937 -- items are placed onto a global stack in the order received via one or more
938 -- dexl executions (i.e. when dexl encounters G). REGS just retrieves the
939 -- string from the cache. The stack is NOT cleared. It is the
943 -- may not be as expected)
940 -- responsibility of the coder to know the order the strings were placed onto
941 -- the cache. It is advised to clear the stack (e.g. this may not be the first
942 -- call to dexl [which adds to the stack], so the current indexing sequence
944 --
945 -- Prerequisites:
946 --
947 -- Post Success:
948 --
949 -- Post Failure:
950 -- n/a
951 --
952 -- Access Status:
953 -- Public
954 --
955 -- {End Of Comments}
956 -- ----------------------------------------------------------------------------
957 FUNCTION REGS
958 ( p_index IN NUMBER
959 )
960 RETURN VARCHAR2;
961 -- ----------------------------------------------------------------------------
962 -- |--< Reset_G_Cache >-------------------------------------------------------|
963 -- ----------------------------------------------------------------------------
964 -- {Start Of Comments}
965 --
966 -- Description:
967 -- clears the global stack
968 --
969 -- Prerequisites:
970 --
971 -- Post Success:
972 --
973 -- Post Failure:
974 -- n/a
975 --
976 -- Access Status:
977 -- Public
978 --
979 -- {End Of Comments}
980 -- ----------------------------------------------------------------------------
981 PROCEDURE Reset_G_Cache;
982 -- ----------------------------------------------------------------------------
983 -- |--< Locate_Text >---------------------------------------------------------|
984 -- ----------------------------------------------------------------------------
985 -- {Start Of Comments}
986 --
987 -- Description:
988 -- locates the starting position of the text according to the criteria
989 -- requested
990 --
991 -- Prerequisites:
992 --
993 -- Post Success:
994 --
995 -- Post Failure:
996 -- n/a
997 --
998 -- Access Status:
999 -- Public
1000 --
1001 -- {End Of Comments}
1002 -- ----------------------------------------------------------------------------
1003 FUNCTION Locate_Text
1004 ( p_search_in IN VARCHAR2
1005 , p_search_for IN VARCHAR2
1006 , p_search_after IN NUMBER DEFAULT 1
1007 , p_second_instance IN BOOLEAN DEFAULT FALSE
1008 , p_end_position IN BOOLEAN DEFAULT FALSE
1009 , p_ignore_case IN BOOLEAN DEFAULT TRUE
1010 , p_reverse IN BOOLEAN DEFAULT FALSE
1011 )
1012 RETURN NUMBER;
1013 -- ----------------------------------------------------------------------------
1014 -- |--< Count_Instances >-----------------------------------------------------|
1015 -- ----------------------------------------------------------------------------
1016 -- {Start Of Comments}
1017 --
1018 -- Description:
1019 -- counts the number of instances of a string within a string; note, in a
1020 -- spearated string, the number of items the string contains is
1021 -- count_instances - 1.
1022 --
1023 -- Prerequisites:
1024 --
1025 -- Post Success:
1026 --
1027 -- Post Failure:
1028 -- n/a
1029 --
1030 -- Access Status:
1031 -- Public
1032 --
1033 -- {End Of Comments}
1034 -- ----------------------------------------------------------------------------
1035 FUNCTION Count_Instances
1036 ( p_search_in IN VARCHAR2
1037 , p_search_for IN VARCHAR2
1038 , p_ignore_case IN BOOLEAN DEFAULT TRUE
1039 )
1040 RETURN NUMBER;
1041 -- ----------------------------------------------------------------------------
1042 -- |--< Execute_Dynamic_SQL >-------------------------------------------------|
1043 -- ----------------------------------------------------------------------------
1044 -- {Start Of Comments}
1045 --
1046 -- Description:
1047 -- parses and executes the string passed in
1048 --
1049 -- Prerequisites:
1050 --
1051 -- Post Success:
1052 --
1053 -- Post Failure:
1054 -- raises an exception
1055 --
1056 -- Access Status:
1057 -- Public
1058 --
1059 -- {End Of Comments}
1060 -- ----------------------------------------------------------------------------
1061 PROCEDURE Execute_Dynamic_SQL
1062 ( p_sql_string IN VARCHAR2
1063 );
1064 -- ----------------------------------------------------------------------------
1065 -- |--< Set_Message_Txt_And_Number >------------------------------------------|
1066 -- ----------------------------------------------------------------------------
1067 -- {Start Of Comments}
1068 --
1069 -- Description:
1070 -- can be used either to set a known message on the stack and retrieve the
1071 -- error message text and number into the record data structure
1072 -- OR can be used to retrieve an existing message on the stack and set
1073 -- the message text and number
1074 --
1075 -- Prerequisites:
1076 --
1077 -- Post Success:
1078 --
1079 -- Post Failure:
1080 -- raises an exception
1081 --
1082 -- Access Status:
1083 -- Public
1084 --
1085 -- {End Of Comments}
1086 -- ----------------------------------------------------------------------------
1087 FUNCTION Set_Message_Txt_And_Number
1088 ( p_application_id IN VARCHAR2 DEFAULT 'PER'
1089 , p_message_name IN VARCHAR2 DEFAULT NULL
1090 )
1091 RETURN r_error_msg_txt_number_rec;
1092 -- ----------------------------------------------------------------------------
1093 -- |--< Set_Workflow_Section_Attribute >--------------------------------------|
1094 -- ----------------------------------------------------------------------------
1095 -- {Start Of Comments}
1096 --
1097 -- Description:
1098 -- returns the set value for the web section (e.g. PERFORMANCE_DETAILS) e.g.
1099 -- HIDE / VIEW
1100 --
1101 -- Prerequisites:
1102 --
1103 -- Post Success:
1104 --
1105 -- Post Failure:
1106 -- raises an exception
1107 --
1108 -- Access Status:
1109 -- Public
1110 --
1111 -- {End Of Comments}
1112 -- ----------------------------------------------------------------------------
1113 FUNCTION Set_Workflow_Section_Attribute
1114 ( p_item_type IN wf_items.item_type%TYPE DEFAULT NULL
1115 , p_item_key IN wf_items.item_key%TYPE DEFAULT NULL
1116 , p_actid IN NUMBER DEFAULT NULL
1117 , p_web_page_section_code IN VARCHAR2
1118 )
1119 RETURN VARCHAR2;
1120
1121 END HR_GENERAL_UTILITIES;