1 PACKAGE BODY BIS_PMF_UTILITIES_PUB AS
2 /* $Header: BISPPMUB.pls 115.4 99/10/11 15:03:52 porting sh $ */
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 | BISPPMUB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Public API for PMF utilities |
14 REM | |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 10-AUG-99 surao Creation |
19 REM | |
20 REM +=======================================================================+
21 */
22 --
23 --
24 --
25 FUNCTION Get_LEVEL_ID
26 ( p_Level_Short_Name IN VARCHAR2
27 )
28 RETURN NUMBER IS
29 --
30 l_level_id NUMBER;
31 --
32 BEGIN
33 IF p_Level_Short_Name IS NULL THEN
34 RETURN NULL;
35 ELSE
36 SELECT LEVEL_ID
37 INTO l_level_id
38 FROM BIS_LEVELS
39 WHERE SHORT_NAME = p_Level_Short_Name;
40 --
41 RETURN l_level_id ;
42 END IF ;
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETURN NULL;
46 END Get_Level_ID ;
47 --
48 --
49 FUNCTION Get_Target_For_Target_Level
50 ( p_TARGET_LEVEL_SHORT_NAME IN VARCHAR2
51 , p_ORG_LEVEL_VALUE IN VARCHAR2
52 , p_TIME_LEVEL_VALUE IN VARCHAR2
53 , p_DIMENSION1_LEVEL_VALUE IN VARCHAR2
54 , p_DIMENSION2_LEVEL_VALUE IN VARCHAR2
55 , p_DIMENSION3_LEVEL_VALUE IN VARCHAR2
56 , p_DIMENSION4_LEVEL_VALUE IN VARCHAR2
57 , p_DIMENSION5_LEVEL_VALUE IN VARCHAR2
58 , p_PLAN_NAME IN VARCHAR2
59 )
60 RETURN NUMBER IS
61 --
62 l_target_level NUMBER;
63 l_target NUMBER;
64 --
65 BEGIN
66 SELECT TARGET_LEVEL_ID
67 INTO l_target_level
68 FROM BIS_TARGET_LEVELS L
69 WHERE L.SHORT_NAME = p_TARGET_LEVEL_SHORT_NAME;
70 --
71 l_target := Get_Target_Value( l_target_level
72 , p_ORG_LEVEL_VALUE
73 , p_TIME_LEVEL_VALUE
74 , p_DIMENSION1_LEVEL_VALUE
75 , p_DIMENSION2_LEVEL_VALUE
76 , p_DIMENSION3_LEVEL_VALUE
77 , p_DIMENSION4_LEVEL_VALUE
78 , p_DIMENSION5_LEVEL_VALUE
79 , p_PLAN_NAME
80 ) ;
81 RETURN l_target ;
82 --
83 EXCEPTION
84 WHEN OTHERS THEN
85 RETURN NULL ;
86 END Get_Target_For_Target_Level;
87 --
88 --
89 FUNCTION Get_Target_For_Level_ID
90 ( p_MEASURE_SHORT_NAME IN VARCHAR2
91 , p_ORG_LEVEL IN NUMBER
92 , p_TIME_LEVEL IN NUMBER
93 , p_DIMENSION1_LEVEL IN NUMBER
94 , p_DIMENSION2_LEVEL IN NUMBER
95 , p_DIMENSION3_LEVEL IN NUMBER
96 , p_DIMENSION4_LEVEL IN NUMBER
97 , p_DIMENSION5_LEVEL IN NUMBER
98 , p_ORG_LEVEL_VALUE IN VARCHAR2
99 , p_TIME_LEVEL_VALUE IN VARCHAR2
100 , p_DIMENSION1_LEVEL_VALUE IN VARCHAR2
101 , p_DIMENSION2_LEVEL_VALUE IN VARCHAR2
102 , p_DIMENSION3_LEVEL_VALUE IN VARCHAR2
103 , p_DIMENSION4_LEVEL_VALUE IN VARCHAR2
104 , p_DIMENSION5_LEVEL_VALUE IN VARCHAR2
105 , p_PLAN_NAME IN VARCHAR2
106 )
107 RETURN NUMBER IS
108 --
109 l_target_level NUMBER;
110 l_target NUMBER;
111 --
112 BEGIN
113 SELECT TARGET_LEVEL_ID
114 INTO l_target_level
115 FROM
116 BIS_TARGET_LEVELS L
117 , BIS_INDICATORS I
118 WHERE I.INDICATOR_ID = L.INDICATOR_ID
119 AND I.SHORT_NAME = p_MEASURE_SHORT_NAME
120 AND L.ORG_LEVEL_ID = p_ORG_LEVEL
121 AND L.TIME_LEVEL_ID = p_TIME_LEVEL
122 AND ( ( p_DIMENSION1_LEVEL IS NOT NULL
123 AND L.DIMENSION1_LEVEL_ID = p_DIMENSION1_LEVEL
124 )
125 OR
126 ( p_DIMENSION1_LEVEL IS NULL
127 )
128 )
129 AND ( ( p_DIMENSION2_LEVEL IS NOT NULL
130 AND L.DIMENSION2_LEVEL_ID = p_DIMENSION2_LEVEL
131 )
132 OR
133 ( p_DIMENSION2_LEVEL IS NULL
134 )
135 )
136 AND ( ( p_DIMENSION3_LEVEL IS NOT NULL
137 AND L.DIMENSION3_LEVEL_ID = p_DIMENSION3_LEVEL
138 )
139 OR
140 ( p_DIMENSION3_LEVEL IS NULL
141 )
142 )
143 AND ( ( p_DIMENSION4_LEVEL IS NOT NULL
144 AND L.DIMENSION4_LEVEL_ID = p_DIMENSION4_LEVEL
145 )
146 OR
147 ( p_DIMENSION4_LEVEL IS NULL
148 )
149 )
150 AND ( ( p_DIMENSION5_LEVEL IS NOT NULL
151 AND L.DIMENSION5_LEVEL_ID = p_DIMENSION5_LEVEL
152 )
153 OR
154 ( p_DIMENSION5_LEVEL IS NULL
155 )
156 );
157 --
158 l_target := Get_Target_Value( l_TARGET_LEVEL
159 , p_ORG_LEVEL_VALUE
160 , p_TIME_LEVEL_VALUE
161 , p_DIMENSION1_LEVEL_VALUE
162 , p_DIMENSION2_LEVEL_VALUE
163 , p_DIMENSION3_LEVEL_VALUE
164 , p_DIMENSION4_LEVEL_VALUE
165 , p_DIMENSION5_LEVEL_VALUE
166 , p_PLAN_NAME
167 );
168 RETURN l_target;
169 --
170 EXCEPTION
171 WHEN OTHERS THEN
172 -- DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
173 -- DBMS_OUTPUT.PUT_LINE('Measure short name = '||p_MEASURE_SHORT_NAME);
174 RETURN NULL ;
175 END Get_Target_For_Level_ID;
176 --
177 --
178 FUNCTION Get_Target_For_Level
179 ( p_MEASURE_SHORT_NAME IN VARCHAR2
180 , p_ORG_LEVEL IN VARCHAR2
181 , p_TIME_LEVEL IN VARCHAR2
182 , p_DIMENSION1_LEVEL IN VARCHAR2
183 , p_DIMENSION2_LEVEL IN VARCHAR2
184 , p_DIMENSION3_LEVEL IN VARCHAR2
185 , p_DIMENSION4_LEVEL IN VARCHAR2
186 , p_DIMENSION5_LEVEL IN VARCHAR2
187 , p_ORG_LEVEL_VALUE IN VARCHAR2
188 , p_TIME_LEVEL_VALUE IN VARCHAR2
189 , p_DIMENSION1_LEVEL_VALUE IN VARCHAR2
190 , p_DIMENSION2_LEVEL_VALUE IN VARCHAR2
191 , p_DIMENSION3_LEVEL_VALUE IN VARCHAR2
192 , p_DIMENSION4_LEVEL_VALUE IN VARCHAR2
193 , p_DIMENSION5_LEVEL_VALUE IN VARCHAR2
194 , p_PLAN_NAME IN VARCHAR2
195 )
196 RETURN NUMBER IS
197 --
198 l_target_level NUMBER;
199 l_target NUMBER;
200 --
201 l_ORG_LEVEL_ID NUMBER;
202 l_TIME_LEVEL_ID NUMBER;
203 l_DIMENSION1_LEVEL_ID NUMBER;
204 l_DIMENSION2_LEVEL_ID NUMBER;
205 l_DIMENSION3_LEVEL_ID NUMBER;
206 l_DIMENSION4_LEVEL_ID NUMBER;
207 l_DIMENSION5_LEVEL_ID NUMBER;
208 --
209 BEGIN
210 l_ORG_LEVEL_ID := Get_level_ID ( p_ORG_LEVEL );
211 l_TIME_LEVEL_ID := Get_level_ID ( p_TIME_LEVEL );
212 l_DIMENSION1_LEVEL_ID := Get_level_ID ( p_DIMENSION1_LEVEL );
213 l_DIMENSION2_LEVEL_ID := Get_level_ID ( p_DIMENSION2_LEVEL );
214 l_DIMENSION3_LEVEL_ID := Get_level_ID ( p_DIMENSION3_LEVEL );
215 l_DIMENSION4_LEVEL_ID := Get_level_ID ( p_DIMENSION4_LEVEL );
216 l_DIMENSION5_LEVEL_ID := Get_level_ID ( p_DIMENSION5_LEVEL );
217 --
218 SELECT TARGET_LEVEL_ID
219 INTO l_target_level
220 FROM
221 BIS_TARGET_LEVELS L
222 , BIS_INDICATORS I
223 WHERE I.INDICATOR_ID = L.INDICATOR_ID
224 AND I.SHORT_NAME = p_MEASURE_SHORT_NAME
225 AND L.ORG_LEVEL_ID = l_ORG_LEVEL_ID
226 AND L.TIME_LEVEL_ID = l_TIME_LEVEL_ID
227 AND ((l_DIMENSION1_LEVEL_ID IS NOT NULL
228 AND L.DIMENSION1_LEVEL_ID = l_DIMENSION1_LEVEL_ID)
229 OR (l_DIMENSION1_LEVEL_ID IS NULL))
230 AND ((l_DIMENSION2_LEVEL_ID IS NOT NULL
231 AND L.DIMENSION2_LEVEL_ID = l_DIMENSION2_LEVEL_ID)
232 OR (l_DIMENSION2_LEVEL_ID IS NULL))
233 AND ((l_DIMENSION3_LEVEL_ID IS NOT NULL
234 AND L.DIMENSION3_LEVEL_ID = l_DIMENSION3_LEVEL_ID)
235 OR (l_DIMENSION3_LEVEL_ID IS NULL))
236 AND ((l_DIMENSION4_LEVEL_ID IS NOT NULL
237 AND L.DIMENSION4_LEVEL_ID = l_DIMENSION4_LEVEL_ID)
238 OR (l_DIMENSION4_LEVEL_ID IS NULL))
239 AND ((l_DIMENSION5_LEVEL_ID IS NOT NULL
240 AND L.DIMENSION5_LEVEL_ID = l_DIMENSION5_LEVEL_ID)
241 OR (l_DIMENSION5_LEVEL_ID IS NULL));
242 --
243 l_target := Get_Target_Value( l_TARGET_LEVEL
244 , p_ORG_LEVEL_VALUE
245 , p_TIME_LEVEL_VALUE
246 , p_DIMENSION1_LEVEL_VALUE
247 , p_DIMENSION2_LEVEL_VALUE
248 , p_DIMENSION3_LEVEL_VALUE
249 , p_DIMENSION4_LEVEL_VALUE
250 , p_DIMENSION5_LEVEL_VALUE
251 , p_PLAN_NAME
252 );
253 --
254 RETURN l_target ;
255 EXCEPTION
256 WHEN OTHERS THEN
257 -- DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
258 -- DBMS_OUTPUT.PUT_LINE('Measure short name = '||p_MEASURE_SHORT_NAME);
259 RETURN NULL ;
260 END Get_Target_For_Level;
261 --
262 --
263 FUNCTION Get_Target_Value
264 ( p_TARGET_LEVEL_ID IN NUMBER
265 , p_ORG_LEVEL_VALUE IN VARCHAR2
266 , p_TIME_LEVEL_VALUE IN VARCHAR2
267 , p_DIMENSION1_LEVEL_VALUE IN VARCHAR2
268 , p_DIMENSION2_LEVEL_VALUE IN VARCHAR2
269 , p_DIMENSION3_LEVEL_VALUE IN VARCHAR2
270 , p_DIMENSION4_LEVEL_VALUE IN VARCHAR2
271 , p_DIMENSION5_LEVEL_VALUE IN VARCHAR2
272 , p_PLAN_NAME IN VARCHAR2
273 )
274 RETURN NUMBER IS
275 --
276 l_target NUMBER;
277 l_plan_id NUMBER;
278 --
279 BEGIN
280 SELECT PLAN_ID
281 INTO l_plan_id
282 FROM BIS_BUSINESS_PLANS_VL
283 WHERE NAME = p_PLAN_NAME ;
284
285 SELECT TARGET
286 INTO l_target
287 FROM BIS_TARGET_VALUES
288 WHERE TARGET_LEVEL_ID = P_TARGET_LEVEL_ID
289 AND PLAN_ID = l_plan_id
290 AND ORG_LEVEL_VALUE = P_ORG_LEVEL_VALUE
291 AND UPPER(TIME_LEVEL_VALUE) = UPPER(p_TIME_LEVEL_VALUE)
292 AND ((p_DIMENSION1_LEVEL_VALUE IS NOT NULL
293 AND DIMENSION1_LEVEL_VALUE = p_DIMENSION1_LEVEL_VALUE )
294 OR (p_DIMENSION1_LEVEL_VALUE IS NULL))
295 AND ((p_DIMENSION2_LEVEL_VALUE IS NOT NULL
296 AND DIMENSION2_LEVEL_VALUE = p_DIMENSION2_LEVEL_VALUE )
297 OR (p_DIMENSION2_LEVEL_VALUE IS NULL))
298 AND ((p_DIMENSION3_LEVEL_VALUE IS NOT NULL
299 AND DIMENSION3_LEVEL_VALUE = p_DIMENSION3_LEVEL_VALUE )
300 OR (p_DIMENSION3_LEVEL_VALUE IS NULL))
301 AND ((p_DIMENSION4_LEVEL_VALUE IS NOT NULL
302 AND DIMENSION4_LEVEL_VALUE = p_DIMENSION4_LEVEL_VALUE )
303 OR (p_DIMENSION4_LEVEL_VALUE IS NULL))
304 AND ((p_DIMENSION5_LEVEL_VALUE IS NOT NULL
305 AND DIMENSION5_LEVEL_VALUE = p_DIMENSION5_LEVEL_VALUE )
306 OR (p_DIMENSION5_LEVEL_VALUE IS NULL)) ;
307 --
308 RETURN l_target;
309 --
310 EXCEPTION
311 WHEN OTHERS THEN
312 -- DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
313 -- DBMS_OUTPUT.PUT_LINE('p_TIME_LEVEL_VALUE = '||p_TIME_LEVEL_VALUE);
314 -- DBMS_OUTPUT.PUT_LINE('p_ORG_LEVEL_VALUE = '||p_ORG_LEVEL_VALUE);
315 -- DBMS_OUTPUT.PUT_LINE('p_DIMENSION1_LEVEL_VALUE = '
316 -- ||p_DIMENSION1_LEVEL_VALUE);
317 RETURN NULL;
318 END Get_Target_Value;
319 --
320 --
321 END BIS_PMF_UTILITIES_PUB;
[Home] [Help]