[Home] [Help]
PACKAGE BODY: APPS.ZPB_AW_STATUS
Source
1 PACKAGE BODY ZPB_AW_STATUS AS
2 /* $Header: zpb_aw_status.plb 120.6 2007/12/04 14:40:40 mbhat ship $ */
3 G_PERSONAL_ALIAS_FLAG VARCHAR2(1) := 'N';
4
5 ------------------------------------------------------------------------------
6 -- RUN_QUERY (private)
7 --
8 -- Runs the actual query (p_sql) and sets the last query valuset (p_dimVset)
9 ------------------------------------------------------------------------------
10 PROCEDURE RUN_QUERY (p_sql IN CLOB,
11 p_dimVset IN VARCHAR2)
12 IS
13 l_text DBMS_SQL.VARCHAR2S;
14 l_cursor INTEGER;
15 l_member VARCHAR2(60);
16 l_members VARCHAR2(4000);
17 l_ignore1 INTEGER;
18 l_ignore2 INTEGER;
19 l_pos INTEGER;
20 l_length INTEGER;
21 l_count INTEGER;
22 BEGIN
23 l_length := DBMS_LOB.GETLENGTH(p_sql);
24 l_pos := 1;
25 l_count := 0;
26 l_members := null;
27 while (l_pos <= l_length) loop
28 l_text(l_count) := DBMS_LOB.SUBSTR(p_sql, 150, l_pos);
29 l_count := l_count+1;
30 l_pos := l_pos+150;
31 end loop;
32
33 l_cursor := DBMS_SQL.OPEN_CURSOR;
34 DBMS_SQL.PARSE(l_cursor, l_text, l_text.FIRST, l_text.LAST, FALSE,
35 DBMS_SQL.NATIVE);
36 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_member, 60);
37 DBMS_SQL.DEFINE_COLUMN(l_cursor, 2, l_ignore1);
38 DBMS_SQL.DEFINE_COLUMN(l_cursor, 3, l_ignore2);
39 l_ignore1 := DBMS_SQL.EXECUTE(l_cursor);
40 loop
41 exit when (DBMS_SQL.FETCH_ROWS(l_cursor) = 0);
42 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_member);
43 l_members := l_members||' '''||l_member||'''';
44 if (length(l_members) > 3900) then
45 zpb_aw.execute ('lmt '||p_dimVset||' add '||l_members);
46 l_members := null;
47 end if;
48 end loop;
49 if (l_members is not null) then
50 zpb_aw.execute ('lmt '||p_dimVset||' add '||l_members);
51 end if;
52 DBMS_SQL.CLOSE_CURSOR(l_cursor);
53 END RUN_QUERY;
54
55 ------------------------------------------------------------------------------
56 -- GET_STATUS
57 --
58 -- Takes a query defined in ZPB_SQL_STATUS and sets the status of the
59 -- LASTQUERYVS for each dimension defined in that query
60 --
61 -- IN: p_aw - The AW the query is defined on
62 -- p_query - The query name
63 ------------------------------------------------------------------------------
64 PROCEDURE GET_STATUS (p_aw IN VARCHAR2,
65 p_query IN VARCHAR2)
66 IS
67 l_sql_statement CLOB;
68 l_dimension VARCHAR2(30);
69 l_ecmDim VARCHAR2(30);
70 l_aw VARCHAR2(30);
71 l_awQual VARCHAR2(30);
72 l_member VARCHAR2(60);
73 l_pers VARCHAR2(30);
74 l_value VARCHAR2(30);
75 l_value2 VARCHAR2(30);
76 l_members VARCHAR2(4000);
77 l_global_ecm zpb_ecm.global_ecm;
78 l_dim_ecm zpb_ecm.dimension_ecm;
79
80
81 TYPE QueryCursType IS REF CURSOR;
82 queryCurs QueryCursType;
83
84 CURSOR queries IS
85 SELECT status_sql_id, status_sql, dimension_name, hierarchy_name
86 FROM zpb_status_sql
87 WHERE (query_path = p_query)
88 ORDER BY DIMENSION_NAME, ROW_NUM;
89 BEGIN
90 if (instr (upper(p_aw), upper(zpb_aw.get_schema)||'.') = 0 and
91 instr (upper(p_aw), 'ZPB') > 0) then
92 l_aw := zpb_aw.get_schema||'.'||p_aw;
93 else
94 l_aw := p_aw;
95 end if;
96
97 --
98 -- Store the personal AW and reset it if the AW is different
99 --
100 l_pers := null;
101 if (zpb_aw.interpbool('shw aw(attached ''PERSONAL'')')
102 and instr(l_aw, '.')>0 and instr(l_aw, 'DATA')=0) then
103 l_value := zpb_aw.eval_text('aw(name ''PERSONAL'')');
104 if (l_value <> upper(l_aw)) then
105 l_pers := l_value;
106 if (zpb_aw.interpbool('shw aw(attached ''' || l_pers || ''')')) then
107 zpb_aw.execute ('aw aliaslist '||l_pers||' unalias PERSONAL');
108 end if;
109 if (zpb_aw.interpbool('shw aw(attached ''' || l_aw || ''')')) then
110 zpb_aw.execute ('aw aliaslist '||l_aw||' alias PERSONAL');
111 end if;
112 zpb_aw_status.set_personal_alias_flag();
113 -- setting this flag so as aw.initialize is not called in get_limitmap
114
115 end if;
116 end if;
117
118 l_awQual := l_aw||'!';
119 l_global_ecm := zpb_ecm.get_global_ecm (l_aw);
120 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
121 ' remove all');
122 l_dimension := '<NULL>';
123 l_sql_statement := ' ';
124 for each in queries loop
125 if (l_dimension <> each.dimension_name and l_dimension <> '<NULL>') then
126 l_ecmDim := zpb_aw.eval_text('lmt('||l_awQual||l_global_ecm.DimDim||
127 ' to '||l_awQual||l_global_ecm.ExpObjVar||
128 ' eq '''||l_dimension||''')');
129 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_ecmDim, l_aw);
130 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
131 ' add '''||l_ecmDim||'''');
132
133 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LastQueryVS||
134 ' remove all');
135
136 RUN_QUERY(l_sql_statement, l_awQual||l_dim_ecm.LastQueryVS);
137
138 l_dimension := each.dimension_name;
139 l_sql_statement := each.status_sql;
140 else
141 l_dimension := each.dimension_name;
142 DBMS_LOB.WRITEAPPEND(l_sql_statement,
143 length(each.status_sql),
144 each.status_sql);
145 end if;
146 end loop;
147
148 --
149 -- Run last query:
150 --
151 --zpb_aw.execute ('CALL MD.VIEW.CMD.SET ('''||l_dimension||
152 -- ''' '''||l_aw||''')');
153 if (l_dimension <> '<NULL>') then
154 l_ecmDim := zpb_aw.eval_text('lmt('||l_awQual||l_global_ecm.DimDim||
155 ' to '||l_awQual||l_global_ecm.ExpObjVar||
156 ' eq '''||l_dimension||''')');
157 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_ecmDim, l_aw);
158 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
159 ' add '''||l_ecmDim||'''');
160
161 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LastQueryVS||' remove all');
162
163 RUN_QUERY(l_sql_statement, l_awQual||l_dim_ecm.LastQueryVS);
164 else
165 ZPB_LOG.WRITE_EVENT ('zpb_aw_status.get_status',
166 'Could not find query: '||p_query);
167 end if;
168 --zpb_aw.execute ('CALL MD.VIEW.CMD.CLEANUP');
169 if (l_pers is not null) then
170 if (zpb_aw.interpbool('shw aw(attached ''' || l_aw || ''')')) then
171 zpb_aw.execute ('aw aliaslist '||l_aw||' unalias PERSONAL');
172 end if;
173 if (zpb_aw.interpbool('shw aw(attached ''' || l_pers || ''')')) then
174 zpb_aw.execute ('aw aliaslist '||l_pers||' alias PERSONAL');
175 end if;
176 zpb_aw_status.RESET_PERSONAL_ALIAS_FLAG();
177
178 end if;
179
180 exception
181 --
182 -- Must cleanup and reset PERSONAL alias in case of crash
183 --
184 when others then
185 if (l_pers is not null) then
186 if (zpb_aw.interpbool('shw aw(attached ''' || l_aw || ''')')) then
187 zpb_aw.execute ('aw aliaslist '||l_aw||' unalias PERSONAL');
188 end if;
189 if (zpb_aw.interpbool('shw aw(attached ''' || l_pers || ''')')) then
190 zpb_aw.execute ('aw aliaslist '||l_pers||' alias PERSONAL');
191 end if;
192 zpb_aw_status.RESET_PERSONAL_ALIAS_FLAG();
193 end if;
194
195 ZPB_LOG.LOG_PLSQL_EXCEPTION('zpb_aw_status', 'get_status');
196 RAISE;
197
198 END GET_STATUS;
199
200 ------------------------------------------------------------------------------
201 -- REPLACE_EXCEPTION_OBJS
202 --
203 -- Function used by GET_EXCEPTION_STATUS to change the table and column names
204 -- in the query to the correct table (from the default exception view and
205 -- column names)
206 --
207 -- IN: p_sql The SQL to convert
208 -- p_newView The view to point the SQL against
209 -- p_newCol The column in the view to use
210 --
211 ------------------------------------------------------------------------------
212 PROCEDURE REPLACE_EXCEPTION_OBJS (p_sql IN OUT NOCOPY CLOB,
213 p_sharedAW IN VARCHAR2,
214 p_newView IN VARCHAR2,
215 p_newCol IN VARCHAR2)
216 is
217 l_excView VARCHAR2(30);
218 l_excCol VARCHAR2(30);
219 l_excViewLen number;
220 l_excColLen number;
221 i number;
222 begin
223 l_excView := zpb_metadata_names.get_exception_check_tbl(p_sharedAw);
224 l_excCol := zpb_metadata_names.get_exception_column;
225 l_excViewLen := length(l_excView);
226 l_excColLen := length(l_excCol);
227
228 i := DBMS_LOB.INSTR(p_sql, l_excView);
229 loop
230 exit when i = 0;
231 p_sql := DBMS_LOB.SUBSTR(p_sql, 1, i-1)||p_newView||DBMS_LOB.SUBSTR(p_sql, i+l_excViewLen);
232 i := DBMS_LOB.INSTR(p_sql, l_excView, i);
233 end loop;
234
235 i:= DBMS_LOB.INSTR(p_sql, l_excCol);
236 loop
237 exit when i = 0;
238 p_sql := DBMS_LOB.SUBSTR(p_sql, 1, i-1)||p_newCol||DBMS_LOB.SUBSTR(p_sql, i+l_excColLen);
239 i := DBMS_LOB.INSTR(p_sql, l_excCol, i);
240 end loop;
241
242 end REPLACE_EXCEPTION_OBJS;
243
244 ------------------------------------------------------------------------------
245 -- GET_EXCEPTION_STATUS
246 --
247 -- Takes a query defined in ZPB_SQL_STATUS and sets the status of the
248 -- LASTQUERYVS for each dimension defined in that query. Also sets the
249 -- LASTQUERYDIMVS structure for all dimensions affected by the query
250 --
251 -- This query is expected to be defined on the exception table. The instance
252 -- passed in will modify the SQL to be executed on the current instance view
253 -- of that instance/BP
254 --
255 -- IN: p_aw - The AW the query is defined on
256 -- p_query - The query name
257 ------------------------------------------------------------------------------
258 PROCEDURE GET_EXCEPTION_STATUS (p_user_id IN VARCHAR2,
259 p_query IN VARCHAR2,
260 p_instance IN VARCHAR2)
261 is
262 l_sql_statement CLOB;
263 l_dimension VARCHAR2(30);
264 l_ecmDim VARCHAR2(30);
265 l_aw VARCHAR2(30);
266 l_schema VARCHAR2(6);
267 l_sharedAw VARCHAR2(30);
268 l_awQual VARCHAR2(30);
269 l_member VARCHAR2(60);
270 l_meas VARCHAR2(30);
271 l_newView VARCHAR2(30);
272 l_newCol VARCHAR2(30);
273 l_value VARCHAR2(30);
274 l_value2 VARCHAR2(30);
275 l_members VARCHAR2(4000);
276 l_global_ecm zpb_ecm.global_ecm;
277 l_dim_ecm zpb_ecm.dimension_ecm;
278
279 TYPE QueryCursType IS REF CURSOR;
280 queryCurs QueryCursType;
281
282 CURSOR queries IS
283 SELECT status_sql_id, status_sql, dimension_name, hierarchy_name
284 FROM zpb_status_sql
285 WHERE (query_path = p_query)
286 ORDER BY DIMENSION_NAME, ROW_NUM;
287 BEGIN
288 l_aw := zpb_aw.get_personal_aw(p_user_id);
289 l_schema := zpb_aw.get_schema||'.';
290 l_sharedAw := l_schema||zpb_aw.get_shared_aw;
291 l_awQual := l_schema||l_aw||'!';
292 l_global_ecm := zpb_ecm.get_global_ecm (l_aw);
293
294 l_meas := zpb_aw.eval_text('CM.GETINSTOBJECT ('''||p_instance||
295 ''' ''SHARED DATA OBJECT ID''');
296 l_newCol := zpb_aw.eval_text('SHARED!'||l_global_ecm.MeasColVar||
297 ' (SHARED!MEASURE '''||l_meas||''')');
298 l_newView := zpb_aw.eval_text('SHARED!'||l_global_ecm.MeasViewRel||
299 ' (SHARED!MEASURE '''||l_meas||''')');
300
301 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
302 ' remove all');
303
304 l_dimension := '<NULL>';
305 l_sql_statement := ' ';
306 for each in queries loop
307 if (l_dimension <> each.dimension_name and l_dimension <> '<NULL>') then
308
309 l_ecmDim := zpb_aw.eval_text('lmt('||l_awQual||l_global_ecm.DimDim||
310 ' to '||l_awQual||l_global_ecm.ExpObjVar||
311 ' eq '''||l_dimension||''')');
312 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_ecmDim, l_aw);
313
314 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
315 ' add '''||l_ecmDim||'''');
316 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LastQueryVS||
317 ' remove all');
318
319 REPLACE_EXCEPTION_OBJS(l_sql_statement,l_sharedAW,l_newView,l_newCol);
320 RUN_QUERY(l_sql_statement, l_awQual||l_dim_ecm.LastQueryVS);
321
322 l_dimension := each.dimension_name;
323 l_sql_statement := each.status_sql;
324 else
325 l_dimension := each.dimension_name;
326 DBMS_LOB.WRITEAPPEND(l_sql_statement,
327 length(each.status_sql),
328 each.status_sql);
329 end if;
330 end loop;
331
332 --
333 -- Run last query:
334 --
335 if (l_dimension <> '<NULL>') then
336 l_ecmDim := zpb_aw.eval_text ('lmt('||l_awQual||l_global_ecm.DimDim||
337 ' to '||l_awQual||l_global_ecm.ExpObjVar||
338 ' eq '''||l_dimension||''')');
339 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_ecmDim, l_aw);
340 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
341 ' add '''||l_ecmDim||'''');
342 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LastQueryVS||' remove all');
343
344 REPLACE_EXCEPTION_OBJS(l_sql_statement, l_sharedAW, l_newView, l_newCol);
345 RUN_QUERY(l_sql_statement, l_awQual||l_dim_ecm.LastQueryVS);
346 else
347 ZPB_LOG.WRITE_EVENT ('zpb_aw_status.get_exception_status',
348 'Could not find query: '||p_query);
349 end if;
350 end GET_EXCEPTION_STATUS;
351
352 ------------------------------------------------------------------------------
353 -- GET_QUERY_DIMS
354 --
355 -- Sets the LastQueryDimVS for the dimensions that are part of the query
356 ------------------------------------------------------------------------------
357 PROCEDURE GET_QUERY_DIMS (p_aw IN VARCHAR2,
358 p_query IN VARCHAR2)
359 is
360 l_ecmDim VARCHAR2(30);
361 l_aw VARCHAR2(30);
362 l_awQual VARCHAR2(30);
363 l_global_ecm zpb_ecm.global_ecm;
364
365 cursor dimensions is
366 select distinct DIMENSION_NAME
367 from ZPB_STATUS_SQL
368 where (query_path = p_query);
369 begin
370
371 if (instr (upper(p_aw), upper(zpb_aw.get_schema)||'.') = 0 and
372 instr (upper(p_aw), 'ZPB') > 0) then
373 l_aw := zpb_aw.get_schema||'.'||p_aw;
374 else
375 l_aw := p_aw;
376 end if;
377
378 l_awQual := l_aw||'!';
379 l_global_ecm := zpb_ecm.get_global_ecm (l_aw);
383 for each in dimensions loop
380 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
381 ' remove all');
382
384 l_ecmDim := zpb_aw.eval_text('lmt('||l_awQual||l_global_ecm.DimDim||
385 ' to '||l_awQual||l_global_ecm.ExpObjVar||
386 ' eq '''||each.DIMENSION_NAME||''')');
387 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LastQueryDimsVS||
388 ' add '''||l_ecmDim||'''');
389 end loop;
390
391 end GET_QUERY_DIMS;
392
393 ------------------------------------------------------------------------------
394 -- GET_STATUS_COUNT
395 --
396 -- Returns the # of dimension members that are part of a particular query and
397 -- dimension. p_dimension is the AW name of the dimension, like CCTR_ORGS
398 ------------------------------------------------------------------------------
399 FUNCTION GET_STATUS_COUNT (p_aw IN VARCHAR2,
400 p_query IN VARCHAR2,
401 p_dimension IN VARCHAR2)
402 return NUMBER
403 is
404 l_sql_statement VARCHAR2(32767);
405 l_aw VARCHAR2(30);
406 l_pers VARCHAR2(30);
407 l_value VARCHAR2(30);
408 l_count NUMBER;
409
410 TYPE QueryCursType IS REF CURSOR;
411 queryCurs QueryCursType;
412
413 CURSOR queries IS
414 SELECT status_sql
415 FROM zpb_status_sql
416 WHERE (query_path = p_query)
417 AND DIMENSION_NAME = p_dimension
418 ORDER BY ROW_NUM;
419 BEGIN
420 dbms_aw.execute ('call cm.log(''moo'' 2 '''||p_aw||': '||p_query||': '||p_dimension||''')');
421 dbms_aw.execute ('call cm.log(''moo'' 2 '''||sys_context('ZPB_CONTEXT', 'business_area_id')||''')');
422 if (instr (upper(p_aw), upper(zpb_aw.get_schema)||'.') = 0 and
423 instr (upper(p_aw), 'ZPB') > 0) then
424 l_aw := zpb_aw.get_schema||'.'||p_aw;
425 else
426 l_aw := p_aw;
427 end if;
428
429 --
430 -- Store the personal AW and reset it if the AW is different
431 --
432 l_pers := null;
433 if (zpb_aw.interpbool('shw aw(attached ''PERSONAL'')')
434 and instr(l_aw, '.')>0 and instr(l_aw, 'DATA')=0) then
435 l_value := zpb_aw.eval_text('aw(name ''PERSONAL'')');
436 if (l_value <> upper(l_aw)) then
437 l_pers := l_value;
438 if (zpb_aw.interpbool('shw aw(attached '''||l_pers||''')')) then
439 zpb_aw.execute ('aw aliaslist '||l_pers||' unalias PERSONAL');
440 end if;
441 if (zpb_aw.interpbool('shw aw(attached '''||l_aw||''')')) then
442 zpb_aw.execute ('aw aliaslist '||l_aw||' alias PERSONAL');
443 end if;
444 zpb_aw_status.set_personal_alias_flag();
445 -- setting this flag so as aw.initialize is not called in get_limitmap
446
447 end if;
448 end if;
449
450 l_count := 0;
451 l_sql_statement := 'select count(*) from (';
452 for each in queries loop
453 l_count := l_count + 1;
454 l_sql_statement := l_sql_statement||each.status_sql;
455 end loop;
456
457 if (l_count > 0) then
458 open queryCurs for l_sql_statement||')';
459 fetch queryCurs into l_count;
460 close queryCurs;
461 end if;
462
463 --zpb_aw.execute ('CALL MD.VIEW.CMD.CLEANUP');
464 if (l_pers is not null) then
465 if (zpb_aw.interpbool('shw aw(attached '''||l_aw||''')')) then
466 zpb_aw.execute ('aw aliaslist '||l_aw||' unalias PERSONAL');
467 end if;
468 if (zpb_aw.interpbool('shw aw(attached '''||l_pers||''')')) then
469 zpb_aw.execute ('aw aliaslist '||l_pers||' alias PERSONAL');
470 end if;
471 zpb_aw_status.RESET_PERSONAL_ALIAS_FLAG();
472 end if;
473
474 return l_count;
475
476 exception
477 --
478 -- Must cleanup and reset PERSONAL alias in case of crash
479 --
480 when others then
481 if (l_pers is not null) then
482 if (zpb_aw.interpbool('shw aw(attached '''||l_aw||''')')) then
483 zpb_aw.execute ('aw aliaslist '||l_aw||' unalias PERSONAL');
484 end if;
485 if (zpb_aw.interpbool('shw aw(attached '''||l_pers||''')')) then
486 zpb_aw.execute ('aw aliaslist '||l_pers||' alias PERSONAL');
487 end if;
488 zpb_aw_status.RESET_PERSONAL_ALIAS_FLAG();
489 end if;
490
491 ZPB_LOG.LOG_PLSQL_EXCEPTION('zpb_aw_status', 'get_status');
492 RAISE;
493 return -1;
494
495 end GET_STATUS_COUNT;
496
497 PROCEDURE SET_PERSONAL_ALIAS_FLAG
498 is
499 begin
500 G_PERSONAL_ALIAS_FLAG := 'Y';
501 end SET_PERSONAL_ALIAS_FLAG;
502
503 PROCEDURE RESET_PERSONAL_ALIAS_FLAG
504 is
505 begin
506 G_PERSONAL_ALIAS_FLAG := 'N';
507 end RESET_PERSONAL_ALIAS_FLAG;
508
509
510 FUNCTION GET_PERSONAL_ALIAS_FLAG return VARCHAR2
511 as
512 begin
513 RETURN G_PERSONAL_ALIAS_FLAG ;
514 end GET_PERSONAL_ALIAS_FLAG;
515
516
517
518 END ZPB_AW_STATUS;