1 PACKAGE BIS_PMF_MIGRATION_PVT AS
2 /* $Header: BISVMIGS.pls 120.0 2005/06/01 15:41:00 appldev noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISPMIGS.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Package Spec for Migration of PMF data .
14 REM | Please refer to the datamodel for the changes.
15 REM | |
16 REM | NOTES |
17 REM | |
18 REM | HISTORY |
19 REM | 21-July-2000 amkulkar Creation |
20 REM | 03-JUN-2004 ankgoel Modified for bug#3583357. Added procedures for |
21 REM | re-sequencing dimensions in |
22 REM | bis_indicator_dimensions using the dim level |
23 REM | order in bis_target_levels. |
24 REM +=======================================================================+
25 */
26 --
27 --
28 /*Record type for all the dimension level ids.
29 This record type may already be there but did not have time to dig thru
30 */
31 TYPE target_level_rec IS RECORD(
32 measure_id BIS_INDICATORS.INDICATOR_ID%TYPE
33 ,target_level_id BIS_TARGET_LEVELS.TARGET_LEVEL_ID%TYPE
34 ,org_levelid BIS_TARGET_LEVELS.ORG_LEVEL_ID%TYPE
35 ,time_levelid BIS_TARGET_LEVELS.TIME_LEVEL_ID%TYPE
36 ,dimension1_levelid BIS_TARGET_LEVELS.DIMENSION1_LEVEL_ID%TYPE
37 ,dimension2_levelid BIS_TARGET_LEVELS.DIMENSION2_LEVEL_ID%TYPE
38 ,dimension3_levelid BIS_TARGET_LEVELS.DIMENSION3_LEVEL_ID%TYPE
39 ,dimension4_levelid BIS_TARGET_LEVELS.DIMENSION4_LEVEL_ID%TYPE
40 ,dimension5_levelid BIS_TARGET_LEVELS.DIMENSION5_LEVEL_ID%TYPE
41 );
42 --
43 TYPE dim_values_rec IS RECORD (
44 measure_id BIS_INDICATORS.INDICATOR_ID%TYPE
45 ,target_level_id BIS_TARGET_LEVELS.TARGET_LEVEL_ID%TYPE
46 ,target_id BIS_TARGET_VALUES.TARGET_ID%TYPE
47 ,org_level_value BIS_TARGET_VALUES.ORG_LEVEL_VALUE%TYPE
48 ,time_level_value BIS_TARGET_VALUES.TIME_LEVEL_VALUE%TYPE
49 ,dimension1_level_value BIS_TARGET_VALUES.DIMENSION1_LEVEL_VALUE%TYPE
50 ,dimension2_level_value BIS_TARGET_VALUES.DIMENSION2_LEVEL_VALUE%TYPE
51 ,dimension3_level_value BIS_TARGET_VALUES.DIMENSION3_LEVEL_VALUE%TYPE
52 ,dimension4_level_value BIS_TARGET_VALUES.DIMENSION4_LEVEL_VALUE%TYPE
53 ,dimension5_level_value BIS_TARGET_VALUES.DIMENSION5_LEVEL_VALUE%TYPE
54 );
55 --
56 TYPE resequenced_dimensions IS RECORD (
57 dim_level_col VARCHAR2(20000)
58 ,dim_level_col_val NUMBER
59 ,dim_id NUMBER
60 ,seq_no NUMBER
61 );
62 --
63 TYPE resequenced_dimensions_array IS TABLE OF
64 BIS_PMF_MIGRATION_PVT.resequenced_dimensions
65 INDEX BY BINARY_INTEGER;
66 --
67 TYPE reseq_dim_values IS RECORD (
68 dim_level_name VARCHAR2(20000)
69 ,dim_level_value VARCHAR2(2000)
70 );
71 --
72 TYPE reseq_dim_values_arr IS TABLE OF
73 BIS_PMF_MIGRATION_PVT.reseq_dim_values
74 INDEX BY BINARY_INTEGER;
75 --
76 PROCEDURE MIGRATE_PERFORMANCE_MEASURES
77 (
78 x_return_status OUT NOCOPY VARCHAR2
79 ,x_return_code OUT NOCOPY NUMBER
80 )
81 ;
82 --
83 PROCEDURE migrate_indicator_dimensions(
84 x_return_status OUT NOCOPY VARCHAR2
85 ,x_return_code OUT NOCOPY NUMBER
86 );
87 --
88 PROCEDURE RESEQUENCE_DIMENSION_LEVELS(
89 p_target_level_rec IN BIS_PMF_MIGRATION_PVT.target_level_rec
90 ,x_resequenced_dimensions OUT NOCOPY BIS_PMF_MIGRATION_PVT.resequenced_dimensions_array
91 ,x_dim_count OUT NOCOPY NUMBER
92 ,x_return_status OUT NOCOPY NUMBER
93 );
94 --
95 PROCEDURE RESEQUENCE_DIM_LEVEL_VALUES(
96 p_dimvalues_rec IN BIS_PMF_MIGRATION_PVT.dim_values_rec
97 ,p_dim_count IN NUMBER
98 ,x_reseq_dim_values OUT NOCOPY BIS_PMF_MIGRATION_PVT.reseq_dim_values_arr
99 ,x_return_status OUT NOCOPY NUMBER
100 );
101 --
102 PROCEDURE RESEQUENCE_IND_LEVEL_VALUES(
103 p_dimvalues_rec IN BIS_PMF_MIGRATION_PVT.dim_values_Rec
104 ,p_dim_count IN NUMBER
105 ,x_reseq_dim_values OUT NOCOPY BIS_PMF_MIGRATION_PVT.reseq_dim_values_arr
106 ,x_return_status OUT NOCOPY NUMBER
107 );
108 --
109 PROCEDURE update_bis_indicators (
110 p_indicator_id IN NUMBER
111 ,p_dim_level_id IN NUMBER
112 ,p_sequence_no IN NUMBER
113 );
114 --
115 PROCEDURE resequence_ind_dimensions(
116 p_indicator_id IN NUMBER
117 );
118 --
119 FUNCTION need_resequence (
120 p_indicator_id IN NUMBER
121 ,p_dim_level_id IN NUMBER
122 ,p_sequence_no IN NUMBER
123 )
124 RETURN BOOLEAN;
125 --
126 FUNCTION GET_DIMENSION_ID
127 (p_dimension_level_id IN NUMBER
128 )
129 RETURN NUMBER;
130 FUNCTION NEEDS_MIGRaTION
131 (p_target_level_rec IN BIS_TARGET_LEVEL_PUB.TARGET_LEVEl_REC_TYPE
132 )
133 RETURN BOOLEAN;
134 END BIS_PMF_MIGRATION_PVT;
135 -- SHOW ERRORS