1 package pqh_generic_hierarchy_package AUTHID CURRENT_USER as
2 /* $Header: pqghrpkg.pkh 120.0 2005/05/29 01:56:16 appldev noship $ */
3 -- ----------------------------------------------------------------------------
4 -- |---------------------------< chk_type_context >---------------------------|
5 -- ----------------------------------------------------------------------------
6 --
7 -- {Start Of Comments}
8 --
9 -- Description: Checks whether a context value is defined in the Context Field
10 -- values for a descriptive flexfield.
11 --
12 -- Prerequisites:
13 -- None
14 --
15 -- In Parameters:
16 -- Name Reqd Type Description
17 -- p_type Y varchar2 Context to be checked
18 -- p_flexfield_name Y varchar2 Flexfield Name
19 --
20 -- Post Success:
21 -- Returns 'Y' or 'N' based on whether the context exists or not.
22 --
23 -- Post Failure:
24 --
25 --
26 -- Access Status:
27 -- Public.
28 --
29 -- {End Of Comments}
30 --
31 function chk_type_context(p_type varchar2,
32 p_flexfield_name varchar2)
33 return varchar2;
34 --
35
36 -- ----------------------------------------------------------------------------
37 -- |-------------------------< chk_if_parent_node_type >----------------------|
38 -- ----------------------------------------------------------------------------
39 --
40 -- {Start Of Comments}
41 --
42 -- Description:Checks if a node type is a parent of any other node type
43 -- in the hierarchy type structure.
44 --
45 -- Prerequisites:
46 -- None
47 --
48 -- In Parameters:
49 -- Name Reqd Type Description
50 -- p_node_type Y varchar2 Node Type to be checked
51 -- p_hierarchy_type Y varchar2 Hierarchy Type of Node Type
52 --
53 --
54 -- Post Success:
55 -- Returns 'Y' or 'N' based on whether the node_type is a parent or not.
56 --
57 -- Post Failure:
58 --
59 --
60 -- Access Status:
61 -- Public.
62 --
63 -- {End Of Comments}
64 --
65 function chk_if_parent_node_type(p_node_type in varchar2,
66 p_hierarchy_type in varchar2)
67 return varchar2;
68 --
69 -- ----------------------------------------------------------------------------
70 -- |------------------------< node_value_set_dyn_query >----------------------|
71 -- ----------------------------------------------------------------------------
72 --
73 -- {Start Of Comments}
74 --
75 -- Description: Returns the value set query for a level in the hierarchy
76 -- based on the parent_node_id and child_node_type from the entry in the
77 -- PER_GEN_HIER_NODE_TYPES table for the hierarchy type.
78 -- Prerequisites:
79 -- None
80 --
81 -- In Parameters:
82 -- Name Reqd Type Description
83 -- p_child_node_type Y varchar2 Node type selected for
84 -- the level.
85 -- p_parent_node_id Y number Id of the parent node.
86 -- p_hierarchy_type Y varchar2 Type of the hierarchy.
87 --
88 --
89 -- Post Success:
90 -- Returns the value set query if the level in the hierarchy has validation
91 -- and the value set is found.If the value set is not found, 'INVALID' is
92 -- returned. If there is no validation, 'NULL' is returned.
93 --
94 -- Post Failure:
95 --
96 --
97 -- Access Status:
98 -- Public.
99 --
100 -- {End Of Comments}
101 --
102 Function Node_Value_Set_Dyn_Query(p_child_node_type IN Varchar2,
103 p_parent_node_id IN Number,
104 p_hierarchy_type IN Varchar2)
105 Return Varchar2;
106 --
107 -- ----------------------------------------------------------------------------
108 -- |----------------------------< get_node_value >----------------------------|
109 -- ----------------------------------------------------------------------------
110 --
111 -- {Start Of Comments}
112 --
113 -- Description: Gets the actual node value stored as entity id.
114 --
115 --
116 -- Prerequisites:
117 -- None
118 --
119 -- In Parameters:
120 -- Name Reqd Type Description
121 -- p_enity_id Y varchar2 Entity id of the node.
122 -- p_parent_node_id Y number Id of the parent node.
123 -- p_child_node_id Y number Id of the node.
124 --
125 --
126 -- Post Success:
127 -- Returns the node value stored.
128 --
129 -- Post Failure:
130 --
131 --
132 -- Access Status:
133 -- Public.
134 --
135 -- {End Of Comments}
136 --
137 --
138 Function Get_Node_Value (p_entity_id IN varchar2,
139 p_parent_node_id IN Number,
140 p_child_node_id IN Number)
141 Return varchar2;
142 --
143 -- ----------------------------------------------------------------------------
144 -- |-------------------------------< get_node_type >--------------------------|
145 -- ----------------------------------------------------------------------------
146 --
147 -- {Start Of Comments}
148 --
149 -- Description:Returns the node_type of a hierarchy version node provided
150 -- the hierarchy_node_id.
151 --
152 -- Prerequisites:
153 -- None
154 --
155 -- In Parameters:
156 -- Name Reqd Type Description
157 -- p_hierarchy_node_id Y number Id of the node.
158 --
159 --
160 -- Post Success:Returns the node_type of the stored node.
161 --
162 --
163 -- Post Failure:
164 --
165 --
166 -- Access Status:
167 -- Public.
168 --
169 -- {End Of Comments}
170 --
171 Function Get_Node_Type(p_hierarchy_node_id IN Number)
172 Return Varchar2;
173 --
174 -- ----------------------------------------------------------------------------
175 -- |---------------------------< get_hierarchy_type >-------------------------|
176 -- ----------------------------------------------------------------------------
177 --
178 -- {Start Of Comments}
179 --
180 -- Description:Returns the hierarchy type of the hierarchy to which a node
181 -- belongs.
182 --
183 -- Prerequisites:
184 -- None
185 --
186 -- In Parameters:
187 -- Name Reqd Type Description
188 -- p_hirarchy_node_id Y number Id of the node.
189 --
190 --
191 -- Post Success:Returns the hierarchy type for the node.
192 --
193 --
194 -- Post Failure:
195 --
196 --
197 -- Access Status:
198 -- Public.
199 --
200 -- {End Of Comments}
201 --
202 Function Get_Hierarchy_Type(p_hierarchy_node_id IN Number)
203 Return Varchar2;
204 --
205 -- ----------------------------------------------------------------------------
206 -- |--------------------------< get_value_set_id >----------------------------|
207 -- ----------------------------------------------------------------------------
208 --
209 -- {Start Of Comments}
210 --
211 -- Description:This function retrieves the value set for a level in a
212 -- hierarchy. It does this by identifying the entry for the level in the table
213 -- PER_GEN_HIER_NODE_TYPES.
214 --
215 --
216 -- Prerequisites:
217 -- None
218 --
219 -- In Parameters:
220 -- Name Reqd Type Description
221 -- p_child_node_type Y varchar2 Type of the node at current
222 -- level.
223 -- p_parent_node_id Y number Id of the parent node to
224 -- identify its type.
225 -- p_hirarchy_type Y varchar2 Hierarchy Type.
226 --
227 --
228 -- Post Success: Returns value set id for the level.If the value set is not
229 -- found, it returns -1. If the value set is invalid it returns -2.
230 --
231 --
232 -- Post Failure:
233 --
234 --
235 -- Access Status:
236 -- Public.
237 --
238 -- {End Of Comments}
239 --
240
241 Function Get_Value_Set_Id (p_child_node_type IN Varchar2,
242 p_parent_node_id IN Number,
243 p_hierarchy_type IN Varchar2 )
244 Return Number;
245 --
246 -- ----------------------------------------------------------------------------
247 -- |---------------------------< get_sql_from_vset_id >-----------------------|
248 -- ----------------------------------------------------------------------------
249 --
250 -- {Start Of Comments}
251 --
252 -- Description: Provided the id of a value set, this function returns the
253 -- associated query.
254 --
255 -- Prerequisites:
256 -- None
257 --
258 -- In Parameters:
259 -- Name Reqd Type Description
260 -- p_vset_id Y number Value set id.
261 --
262 --
263 -- Post Success:
264 -- Returns the value set query.
265 --
266 -- Post Failure:
267 --
268 --
269 -- Access Status:
270 -- Public.
271 --
272 -- {End Of Comments}
273 --
274 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER)
275 RETURN VARCHAR2;
276 --
277 -- ----------------------------------------------------------------------------
278 -- |------------------------< chk_if_structure_exists >-----------------------|
279 -- ----------------------------------------------------------------------------
280 --
281 -- {Start Of Comments}
282 --
283 -- Description: This function checks whether there is any entry in the table
284 -- PER_GEN_HIER_NODE_TYPES for a given HIERARCHY_TYPE lookup.
285 --
286 -- Prerequisites:
287 -- None
288 --
289 -- In Parameters:
290 -- Name Reqd Type Description
291 -- p_hirarchy_type Y varchar2 The HIERARCHY_TYPE
292 -- lookup_code.
293 --
294 -- Post Success: Returns 'Y' if structure exists for the HIERARCHY_TYPE.
295 -- Else returns 'N'.
296 --
297 -- Post Failure:
298 --
299 --
300 -- Access Status:
301 -- Public.
302 --
303 -- {End Of Comments}
304 --
305 Function chk_if_structure_exists(p_hierarchy_type IN varchar2)
306 Return Varchar2;
307 --
308 --
309 -- ----------------------------------------------------------------------------
310 -- |--------------------------< chk_version_exists >--------------------------|
311 -- ----------------------------------------------------------------------------
312 --
313 --
314 -- {Start Of Comments}
315 --
316 -- Description:
317 -- This procedure checks if there is any version for a hierarchy as on an
318 -- Effective date.
319 --
320 -- Prerequisites:
321 -- None
322 --
323 -- In Parameters:
324 -- Name Reqd Type Description
325 -- p_hierarchy_id Y number Id of the hierarchy.
326 -- p_effective_date Y date Effective date.
327 --
328 --
329 -- Post Success:
330 -- Returns 'Y' if there is any matching version, else returns 'N'
331 --
332 -- Post Failure:
333 --
334 -- Access Status:
335 -- Public.
336 --
337 -- {End Of Comments}
338 --
339 --
340 Procedure chk_version_exists(p_hierarchy_id in Number,
341 p_effective_date in Date);
342
343 --
344 --
345 -- ----------------------------------------------------------------------------
346 -- |--------------------------< create_lookup_value >-------------------------|
347 -- ----------------------------------------------------------------------------
348 --
349 -- {Start Of Comments}
350 --
351 -- Description:
352 -- This procedure HIERARCHY_TYPE or HIERARCHY_NODE_TYPE lookup values
353 --
354 -- Prerequisites:
355 -- None
356 --
357 -- In Parameters:
358 -- Name Reqd Type Description
359 -- p_lookup_type Y varchar2 Lookup Type(Either HIERARCHY_TYPE
360 -- or HIERARCHY_NODE_TYPE) for which
361 -- the new entry is being created.
362 -- p_lookup_code Y varchar2 The new code to be inserted.
363 -- p_meaning Y varchar2 Meaning for the new code.
364 -- p_description N varchar2 Description for the new code.
365 --
366 -- Out parameters:
367 -- Name Type Description
368 -- p_return_status varchar2 Flag to identify if the procedure
369 -- succeeded or not.
370 --
371 -- Post Success:
372 -- The lookup entry is created. p_return_status holds 'Y'.
373 --
374 -- Post Failure:
375 -- The procedure does not create the lookup entry and raises an error.
376 -- p_return_status holds 'N'.
377 --
378 -- Access Status:
379 -- Public.
380 --
381 -- {End Of Comments}
382 --
383 Procedure create_lookup_value
384 ( p_lookup_type in varchar2
385 ,p_lookup_code in varchar2
386 ,p_meaning in varchar2
387 ,p_description in varchar2 default null
388 ,p_return_status out NOCOPY varchar2
389 );
390
391 Procedure update_lookup_value
392 ( p_lookup_type in varchar2
393 ,p_lookup_code in varchar2
394 ,p_meaning in varchar2
395 ,p_description in varchar2
396 );
397
398 --
399 --
400 -- ----------------------------------------------------------------------------
401 -- |--------------------------< create_shared_type >--------------------------|
402 -- ----------------------------------------------------------------------------
403 --
404 -- {Start Of Comments}
405 --
406 -- Description: Creates an entry in the shared types for a HIERARCHY_TYPE
407 -- lookup, with system_type_cd and shared_type_code values as the
408 -- HIERARCHY_TYPE lookup_code
409 --
410 --
411 -- Prerequisites:
412 -- None
413 --
414 -- In Parameters:
415 -- Name Reqd Type Description
416 -- p_lookup_code Y varchar2 Lookup_code of the
417 -- HIERARCHY_TYPE lookup.
418 -- p_meaning Y varchar2 Meaning of the
419 -- HIERARCHY_TYPE lookup.
420 --
421 --
422 -- Post Success: Creates a new entry in shared types.
423 --
424 --
425 -- Post Failure: Does not create any entry in shared types.
426 --
427 --
428 -- Access Status:
429 -- Public.
430 --
431 -- {End Of Comments}
432 --
433 Procedure create_shared_type
434 (p_lookup_code in varchar2,
435 p_meaning in varchar2
436 );
437 --
438 --
439 -- ----------------------------------------------------------------------------
440 -- |--------------------------< update_shared_type >--------------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 -- {Start Of Comments}
444 --
445 -- Description:Updates allow multiple versions and allow duplicate name flags
449 -- Prerequisites:
446 -- for a HIERARCHY_TYPE lookup in per_shared_types.
447 --
448 --
450 -- None
451 --
452 -- In Parameters:
453 -- Name Reqd Type Description
454 -- p_lookup_code Y varchar2 Lookup_code of the
455 -- HIERARCHY_TYPE.
456 -- p_information2 Y varchar2 Allow multiple version flag.
457 -- p_information3 Y varchar2 Allow duplicate name flag.
458 --
459 --
460 -- Post Success: Updates the shared types entry.
461 --
462 --
463 -- Post Failure: Does not update the shared types entry.
464 --
465 --
466 -- Access Status:
467 -- Public.
468 --
469 -- {End Of Comments}
470 --
471 --
472 Procedure update_shared_type
473 (p_lookup_code in varchar2,
474 p_information2 in varchar2,
475 p_information3 in varchar2
476 ) ;
477 --
478 --
479 -- ----------------------------------------------------------------------------
480 -- |----------------------< create_lookup_and_shared_type>--------------------|
481 -- ----------------------------------------------------------------------------
482 --
483 -- {Start Of Comments}
484 --
485 -- Description: This procedure creates a new HIERARCHY_TYPE lookup and also
486 -- creates an entry for it in the PER_SHARED_TYPES table.
487 --
488 --
489 -- Prerequisites:
490 -- None
491 --
492 -- In Parameters:
493 -- Name Reqd Type Description
494 -- p_lookup_code Y varchar2
495 -- p_meaning Y varchar2
496 -- p_description N varchar2
497 --
498 -- Post Success:
499 --
500 --
501 -- Post Failure:
502 --
503 --
504 -- Access Status:
505 -- Public.
506 --
507 -- {End Of Comments}
508 --
509 Procedure create_lookup_and_shared_type
510 ( p_lookup_code in varchar2
511 ,p_meaning in varchar2
512 ,p_description in varchar2 default null
513 );
514 --
515 --
516 -- ----------------------------------------------------------------------------
517 -- |--------------------------< create_node_type >---------------------------|
518 -- ----------------------------------------------------------------------------
519 --
520 --
521 -- {Start Of Comments}
522 --
523 -- Description:
524 -- This procedure creates a row in the table PER_GEN_HIER_NODE_TYPES.
525 --
526 -- Prerequisites:
527 -- None
528 --
529 -- In Parameters:
530 -- Name Reqd Type Description
531 -- p_hierarchy_type Y varchar2 Hierarchy type code.
532 -- p_child_value_set Y varchar2 Value set name.
533 -- p_child_node_type Y varchar2 Child node type code.
534 -- p_parent_node_type N varchar2 Parent Node Type code.
535 --
536 --
537 -- Post Success:
538 -- The new row is inserted into PER_GEN_HIER_NODE_TYPES.
539 --
540 -- Post Failure:
541 -- The procedure does not create the new row.
542 --
543 -- Access Status:
544 -- Public.
545 --
546 -- {End Of Comments}
547 --
548 Procedure create_node_type (p_hierarchy_type in varchar2,
549 p_child_value_set in varchar2,
550 p_child_node_type in varchar2,
551 p_parent_node_type in varchar2 default null);
552 --
553 --
554 -- ----------------------------------------------------------------------------
555 -- |--------------------------< update_node_type >---------------------------|
556 -- ----------------------------------------------------------------------------
557 --
558 --
559 --
560 -- {Start Of Comments}
561 --
562 -- Description:
563 -- This procedure updates a row in the table PER_GEN_HIER_NODE_TYPES.
564 --
565 -- Prerequisites:
566 -- None
567 --
568 -- In Parameters:
569 -- Name Reqd Type Description
570 -- p_hierarchy_type Y varchar2 Hierarchy type code.
571 -- p_child_value_set Y varchar2 Value set name.
572 -- p_child_node_type Y varchar2 Child node type code.
573 -- p_parent_node_type N varchar2 Parent Node Type code.
574 --
575 -- In out Parameters:
576 -- Name Reqd Type Description
577 -- p_object_version_number Y number Object version number.
578 --
579 -- Post Success:
580 -- The row is updated in PER_GEN_HIER_NODE_TYPES.
581 --
582 -- Post Failure:
583 -- The procedure does not update the row.
584 --
585 -- Access Status:
586 -- Public.
587 --
588 -- {End Of Comments}
589 --
590 Procedure update_node_type (p_hierarchy_type in varchar2,
591 p_child_value_set in varchar2,
592 p_child_node_type in varchar2,
593 p_parent_node_type in varchar2 default null,
597 -- |--------------------------< delete_node_type >---------------------------|
594 p_object_version_number in out NOCOPY number);
595
596 -- ----------------------------------------------------------------------------
598 -- ----------------------------------------------------------------------------
599 --
600 --
601 --
602 -- {Start Of Comments}
603 --
604 -- Description:
605 -- This procedure deletes a row in the table PER_GEN_HIER_NODE_TYPES.
606 --
607 -- Prerequisites:
608 -- None
609 --
610 -- In Parameters:
611 -- Name Reqd Type Description
612 -- p_hierarchy_type Y varchar2 Hierarchy type code.
613 -- p_child_node_type Y varchar2 Child node type code.
614 -- p_parent_node_type N varchar2 Parent Node Type code.
615 --
616 --
617 -- Post Success:
618 -- The row is deleted from PER_GEN_HIER_NODE_TYPES.
619 --
620 -- Post Failure:
621 -- The row is not deleted.
622 --
623 -- Access Status:
624 -- Public.
625 --
626 -- {End Of Comments}
627
628 Procedure delete_node_type (p_hierarchy_type in varchar2,
629 p_child_node_type in varchar2,
630 p_parent_node_type in varchar2 default null);
631
632 --
633 --
634 -- ----------------------------------------------------------------------------
635 -- |--------------------------< delete_type_structure >-----------------------|
636 -- ----------------------------------------------------------------------------
637 --
638 -- {Start Of Comments}
639 --
640 -- Description:
641 -- This procedure deletes all rows for a HIERARCHY_TYPE from the table
642 -- PER_GEN_HIER_NODE_TYPES.
643 --
644 -- Prerequisites:
645 -- None
646 --
647 -- In Parameters:
648 -- Name Reqd Type Description
649 -- p_hierarchy_type Y varchar2 Hierarchy type code.
650 --
651 --
652 -- Post Success:
653 -- The rows for the HIERARCHY_TYPE are deleted.
654 --
655 -- Post Failure:
656 -- The rows are not deleted.
657 --
658 -- Access Status:
659 -- Public.
660 --
661 -- {End Of Comments}
662
663 Procedure delete_type_structure (p_hierarchy_type in varchar2);
664 --
665 --
666 -- ----------------------------------------------------------------------------
667 -- |--------------------------< copy_hierarchy_version >----------------------|
668 -- ----------------------------------------------------------------------------
669 --
670 --
671 -- {Start Of Comments}
672 --
673 -- Description:
674 -- This procedure is a wrapper over PQH_DE_OPR_GRP.copy_hierarchy_version
675 -- to copy a hierarchy and/or hierarchy version into another.
676 --
677 -- Prerequisites:
678 -- None
679 --
680 -- In Parameters:
681 -- Name Reqd Type Description
682 -- p_type Y varchar2 Identifier Flag.
683 -- Pass 'H' to copy hierarchy
684 -- and version, 'V' for version
685 -- alone.
686 -- p_name N varchar2 Name of new hierarchy.
687 -- p_hierarchy_id N number Hierarchy Id
688 -- p_hierarchy_version_id N number Hierarchy Version Id
689 -- p_version_number N number Hierarchy Version Number
690 -- p_date_from N date Start date of hierarchy
691 -- version
692 -- p_date_to N date End Date of hierarchy
693 -- version
694 -- p_business_group_id Y number Business Group Id.
695 --
696 -- Out Parameters:
697 -- Name Type Description
698 -- p_new_hierarchy_id number New hierarchy Id.
699 -- p_new_hierarchy_version_id number New hierarchy version id.
700 --
701 --
702 -- Post Success:
703 -- The hierarchy and/or hierarchy version is copied.
704 --
705 -- Post Failure:
706 -- The copy operation is not performed.
707 --
708 -- Access Status:
709 -- Public.
710 --
711 -- {End Of Comments}
712 --
713 Procedure copy_hierarchy_version ( p_type in varchar2
714 ,p_name in varchar2 Default null
715 ,p_hierarchy_id in Number Default null
716 ,p_hierarchy_version_id in Number Default null
717 ,p_version_number in Number Default null
718 ,p_date_from in Date Default null
719 ,p_date_to in Date Default null
720 ,p_business_group_id in Number
721 ,p_effective_date in Date
722 ,p_new_hierarchy_id out NOCOPY Number
723 ,p_new_hierarchy_version_id out NOCOPY Number);
724 --
725 --
726 --
727 -- ----------------------------------------------------------------------------
728 -- |------------------------------< is_valid_sql >----------------------------|
732 -- {Start Of Comments}
729 -- ----------------------------------------------------------------------------
730 --
731 --
733 --
734 -- Description:
735 -- This function identifies if a value set query is valid or not.
736 --
737 -- Prerequisites:
738 -- None
739 --
740 -- In Parameters:
741 -- Name Reqd Type Description
742 -- p_sql Y varchar2 Value set query.
743 --
744 -- Post Success:
745 -- Returns 'Y' if the sql is valid. Else, Returns 'N'.
746 --
747 -- Post Failure:
748 --
749 --
750 -- Access Status:
751 -- Public.
752 --
753 -- {End Of Comments}
754 --
755
756 Function is_valid_sql (p_sql in varchar2)
757 Return Varchar2;
758 --
759 -- ----------------------------------------------------------------------------
760 -- |--------------------------< validate_vets_hierarchy >---------------------|
761 -- ----------------------------------------------------------------------------
762 --
763 --
764 -- {Start Of Comments}
765 --
766 -- Description:
767 -- This function validates Vets hierarchies for reports.
768 --
769 -- Prerequisites:
770 -- None
771 --
772 -- In Parameters:
773 -- Name Reqd Type Description
774 -- p_hierarchy_version_id Y Number Id of the hierarchy version.
775 --
776 -- Post Success:
777 -- Returns 'Y' if the node type in the first level is 'PAR', second level
778 -- is 'EST', third level is 'LOC', and if the number of levels in the
779 -- hierarchy version is either 2 or 3.Else returns 'N'
780 --
781 -- Post Failure:
782 --
783 --
784 -- Access Status:
785 -- Public.
786 --
787 -- {End Of Comments}
788 --
789 Function validate_vets_hierarchy(p_hierarchy_version_id in Number)
790 Return Varchar2;
791 --
792 -- ----------------------------------------------------------------------------
793 -- |---------------------------< get_display_value >--------------------------|
794 -- ----------------------------------------------------------------------------
795 --
796 --
797 -- {Start Of Comments}
798 --
799 -- Description:
800 -- This function gets the display value for nodes.
801 --
802 -- Prerequisites:
803 -- None
804 --
805 -- In Parameters:
806 -- Name Reqd Type Description
807 -- p_entity_id Y varchar2 Entity id of the node.
808 -- p_node_type_id Y number id of the row in
809 -- PER_GEN_HIER_NODE_TYPES
810 -- corresponding to the node.
811 --
812 -- Post Success:
813 -- Returns the value of the node if it has validation or the entity id itself.
814 --
815 -- Post Failure:
816 --
817 --
818 -- Access Status:
819 -- Public.
820 --
821 -- {End Of Comments}
822 --
823
824 Function get_display_value(p_entity_id IN VARCHAR2,
825 p_node_type_id IN NUMBER)
826 Return Varchar2;
827 --
828 --
829 -- ----------------------------------------------------------------------------
830 -- |------------------------------< gen_hier_exists >-------------------------|
831 -- ----------------------------------------------------------------------------
832 --
833 --
834 -- {Start Of Comments}
835 --
836 -- Description:
837 -- This function identifies if any hierarchy exists of a hierarchy type.
838 --
839 -- Prerequisites:
840 -- None
841 --
842 -- In Parameters:
843 -- Name Reqd Type Description
844 -- p_hierarchy_type Y varchar2 Hierarchy Type
845 --
846 -- Post Success:
847 -- Returns 'Y' if any hierarchy exists of the given type.Else 'N'.
848 --
849 -- Post Failure:
850 --
851 --
852 -- Access Status:
853 -- Public.
854 --
855 -- {End Of Comments}
856 --
857
858 --
859 Function gen_hier_exists(p_hierarchy_type in Varchar2)
860 Return Varchar2;
861 --
862 --
863 -- ----------------------------------------------------------------------------
864 -- |-------------------------< chk_multiple_versions >------------------------|
865 -- ----------------------------------------------------------------------------
866 --
867 --
868 -- {Start Of Comments}
869 --
870 -- Description:
871 -- This function identifies if there are more than one version for a
872 -- hierarchy.
873 --
874 -- Prerequisites:
875 -- None
876 --
877 -- In Parameters:
878 -- Name Reqd Type Description
879 -- p_hierarchy_id Y number Hierarchy Id.
880 --
881 -- Post Success:
882 -- Returns 'Y' if more than one version exists for the hierarchy.
883 -- Else, Returns 'N'.
884 --
885 -- Post Failure:
886 --
887 --
888 -- Access Status:
889 -- Public.
890 --
891 -- {End Of Comments}
892 --
893
894 Function chk_multiple_versions (p_hierarchy_id in Number)
895 Return Varchar2 ;
896
900
897 Function Node_Sequence(P_Hierarchy_version_id IN Number,
898 P_Parent_Hierarchy_Id IN Number)
899 Return Number;
901 Procedure copy_Hierarchy
902 (P_Hierarchy_version_id IN Number,
903 P_Parent_Hierarchy_id IN Number,
904 P_Hierarchy_Id IN Number,
905 p_Business_group_Id IN Number,
906 p_Effective_Date IN Date);
907
908 Procedure Main
909 (P_Type IN Varchar2,
910 P_Trntype IN Varchar2,
911 P_Code IN Varchar2 Default NULL,
912 P_Description IN Varchar2 Default NULL,
913 p_Code_Id IN Number Default NULL,
914 P_Hierarchy_version_id IN Number Default NULL,
915 P_Parent_Hierarchy_id IN Number Default NULL,
916 P_Hierarchy_Id IN Number Default NULL,
917 p_Object_Version_Number IN Number Default NULL,
918 p_Business_group_Id IN Number ,
919 p_Effective_Date IN Date);
920
921 End;
[Home] [Help]