[Home] [Help]
PACKAGE BODY: APPS.ZPB_DEBUG
Source
1 package body ZPB_DEBUG as
2 /* $Header: ZPBVDBGB.pls 120.0.12010.2 2006/08/03 12:03:42 appldev noship $ */
3
4 procedure INIT(p_user IN NUMBER,
5 p_business_area IN NUMBER)
6 is
7 begin
8 fnd_global.apps_initialize(p_user, 1, 210);
9 zpb_busarea_maint.login(p_business_area);
10 commit;
11 zpb_security_context.initcontext(p_user, p_user, 1, 1, p_business_area);
12 end INIT;
13
14 procedure SETUP(p_user IN NUMBER,
15 p_business_area IN NUMBER)
16 is
17 l_codeAW varchar2(30);
18 l_annotAW varchar2(30);
19
20 begin
21 INIT(p_user, p_business_area);
22 l_codeAW := zpb_aw.get_schema||'.'||zpb_aw.get_code_aw(p_user);
23 l_annotAW := zpb_aw.get_schema||'.'||zpb_aw.get_annotation_aw;
24
25 dbms_aw.execute ('aw attach '||l_codeAW||' ro');
26 dbms_aw.execute ('aw attach '||l_annotAW||' ro');
27 dbms_aw.execute ('call pa.attach.shared');
28 dbms_aw.execute ('call pa.attach.personal');
29
30 end SETUP;
31
32 procedure STARTUP(p_user IN NUMBER,
33 p_business_area IN NUMBER)
34 is
35 ttype varchar2(8);
36 pname varchar2(64);
37 msgCnt number;
38 msgData varchar2(2000);
39 retcode varchar2(2000);
40 begin
41 fnd_global.apps_initialize(p_user, 1, 210);
42 zpb_busarea_maint.login(p_business_area);
43 commit;
44 zpb_security_context.initcontext(p_user, p_user, 1, 1, p_business_area);
45 zpb_personal_aw.startup(1.0, FND_API.G_FALSE, FND_API.G_TRUE,
46 FND_API.G_VALID_LEVEL_FULL,
47 retcode, msgCnt, msgData,
48 to_char(p_user), FND_API.G_FALSE);
49
50 dbms_output.put_line ('Error Buffer: '||substr (msgData, 1, 240));
51 dbms_output.put_line ('Error Buffer: '||substr (msgData, 240, 240));
52 dbms_output.put_line ('Retcode: '||retcode);
53
54 end STARTUP;
55
56 procedure STARTUPRO(p_user IN NUMBER,
57 p_business_area IN NUMBER)
58 is
59 ttype varchar2(8);
60 pname varchar2(64);
61 msgCnt number;
62 msgData varchar2(2000);
63 retcode varchar2(2000);
64 begin
65 fnd_global.apps_initialize(p_user, 1, 210);
66 zpb_busarea_maint.login(p_business_area);
67 commit;
68 zpb_security_context.initcontext(p_user, p_user, 1, 1, p_business_area);
69 zpb_personal_aw.startup(1.0, FND_API.G_FALSE, FND_API.G_TRUE,
70 FND_API.G_VALID_LEVEL_FULL,
71 retcode, msgCnt, msgData,
72 to_char(p_user), FND_API.G_TRUE);
73
74 dbms_output.put_line ('Error Buffer: '||substr (msgData, 1, 240));
75 dbms_output.put_line ('Error Buffer: '||substr (msgData, 240, 240));
76 dbms_output.put_line ('Retcode: '||retcode);
77 end STARTUPRO;
78
79 --
80 -- Same as calling Refresh on a BA, run from the backend
81 --
82 procedure REFRESH_BA(p_user IN NUMBER,
83 p_business_area IN NUMBER)
84 is
85 errbuf varchar2(4000);
86 retcode varchar2(4000);
87 begin
88 fnd_global.apps_initialize(p_user, 24138, 210);
89 zpb_build_metadata.build_metadata (errbuf, retcode, p_business_area);
90 dbms_output.put_line ('Retcode: '||retcode);
91 dbms_output.put_line ('ErrBuf: '||errbuf);
92 end REFRESH_BA;
93
94 -- prints metadata info for user and ba to the screen
95 -- initialize the session with, prior to calling:
96 --set lines 1000
97 --set pages 1000
98 --set feedback off
99 --set heading off
100 --set serveroutput on size 1000000
101 procedure MDSCREEN(p_user_id in number, p_bus_area_id in number)
102
103 is
104 var1 number;
105
106 CURSOR c_dimensions is
107 select dvl.name,
108 dvl.is_owner_dim,
109 dvl.pers_cwm_name,
110 dvl.shar_cwm_name,
111 dvl.aw_name,
112 dvl.dimension_id,
113 (select count(*) from zpb_hierarchies where dimension_id = dvl.dimension_id and hier_type<>'NULL') hier_cnt,
114 dvl.epb_id
115 from zpb_dimensions_vl dvl
116 where bus_area_id = p_bus_area_id and
117 is_data_dim='YES'
118 order by is_owner_dim desc,
119 name;
120
121 v_dim c_dimensions%ROWTYPE;
122 l_dim_id number;
123
124 CURSOR c_hierarchies is
125 select name,
126 hier_type,
127 epb_id,
128 tab1.table_name pers_table_name,
129 tab2.table_name shar_table_name,
130 hierarchy_id
131 from zpb_hierarchies_vl hier,
132 zpb_tables tab1,
133 zpb_tables tab2
134 where hier.dimension_id = l_dim_id and
135 hier.pers_table_id = tab1.table_id and
136 hier.shar_table_id = tab2.table_id;
137
138
139 v_hier c_hierarchies%ROWTYPE;
140 l_hier_id number;
141
142 CURSOR c_levels is
143 select levs.name,
144 levs.epb_id,
145 levs.pers_cwm_name,
146 levs.shar_cwm_name
147 from zpb_levels_vl levs,
148 zpb_hier_level hrlv
149 where hrlv.level_id = levs.level_id and
150 hrlv.hier_id = l_hier_id
151 order by hrlv.level_order;
152
153
154 v_level c_levels%ROWTYPE;
155
156 CURSOR c_attributes is
157 select attrs.name,
158 attrs.type,
159 attrs.label,
160 dims.aw_name,
161 attrs.pers_cwm_name
162 from zpb_attributes_vl attrs,
163 zpb_dimensions dims
164 where attrs.dimension_id = l_dim_id and
165 dims.dimension_id = attrs.range_dim_id;
166
167 v_attr c_attributes%ROWTYPE;
168
169 l_meas_type varchar2(32);
170
171 CURSOR c_bp_instances is
172
173 select meas.name meas,
174 meas.instance_id,
175 cubs.name cube,
176 cols.column_name,
177 meas.cube_id
178
179 from zpb_measures meas,
180 zpb_columns cols,
181 zpb_cubes cubs
182
183 where meas.cube_id = cubs.cube_id and
184 meas.column_id = cols.column_id and
185 cubs.bus_area_id = p_bus_area_id and
186 meas.type = l_meas_type
187 order by meas.name;
188
189 v_bp_inst c_bp_instances%ROWTYPE;
190
191 CURSOR c_prs_instances is
192
193 select meas.name meas,
194 meas.instance_id,
195 cubs.name cube,
196 cols.column_name,
197 meas.cube_id
198
199 from zpb_measures meas,
200 zpb_columns cols,
201 zpb_cubes cubs
202
203 where meas.cube_id = cubs.cube_id and
204 meas.column_id = cols.column_id and
205 cubs.bus_area_id = p_bus_area_id and
206 meas.type = l_meas_type and
207 cubs.name like 'ZPB' || to_char(p_user_id) || 'A' || to_char(p_bus_area_id) || '/_%' escape '/'
208 order by meas.name;
209
210 v_prs_inst c_prs_instances%ROWTYPE;
211
212 l_cube_id number;
213
214 CURSOR c_cube_dims is
215
216 select dims.epb_id,
217 dims.aw_name
218 from zpb_cube_dims cds,
219 zpb_dimensions dims
220 where cds.dimension_id = dims.dimension_id
221 and cds.cube_id = l_cube_id;
222
223 v_cube_dim c_cube_dims%ROWTYPE;
224
225 l_dimensionality varchar2(100);
226
227 L_BLANK CONSTANT VARCHAR2(50):= substr('. . . . . . . . . . . ', 1, 50);
228 L_BREAK CONSTANT VARCHAR2(150):= substr('-------------------------------------------------------------------------------------------------------------------------------------------------------------', 1, 150);
229
230 L_COL_DIM_NAME CONSTANT VARCHAR2(30):= 'Dimension Name';
231 L_COL_DIM_OWN CONSTANT VARCHAR2(10):= 'Ownership';
232 L_COL_DIM_PCWM CONSTANT VARCHAR2(30):= 'Personal Beans Name';
233 L_COL_DIM_SCWM CONSTANT VARCHAR2(30):= 'Shared Beans Name';
234 L_COL_DIM_AWNM CONSTANT VARCHAR2(20):= 'AW Name';
235 L_COL_DIM_HRCN CONSTANT VARCHAR2(10):= 'Hier Count';
236 L_COL_DIM_EPBID CONSTANT VARCHAR2(10):= 'EPB ID';
237
238 L_COL_HIER_NAME CONSTANT VARCHAR2(30):= 'Hierarchy Name';
239 L_COL_HIER_TYPE CONSTANT VARCHAR2(20):= 'Type';
240 L_COL_HIER_EPB CONSTANT VARCHAR2(30):= 'EPB ID';
241 L_COL_HIER_SHRT CONSTANT VARCHAR2(30):= 'Shared View Name';
242 L_COL_HIER_PRST CONSTANT VARCHAR2(30):= 'Personal View Name';
243
244 L_COL_LEVL_NAME CONSTANT VARCHAR2(30):= 'Level Name';
245 L_COL_LEVL_EPB CONSTANT VARCHAR2(30):= 'EPB ID';
246 L_COL_LEVL_SHRB CONSTANT VARCHAR2(30):= 'Shared Beans Name';
247 L_COL_LEVL_PRSB CONSTANT VARCHAR2(30):= 'Personal Beans Name';
248
249 L_COL_ATTR_NAME CONSTANT VARCHAR2(50):= 'Attribute Name';
250 L_COL_ATTR_TYPE CONSTANT VARCHAR2(15):= 'Type';
251 L_COL_ATTR_LABL CONSTANT VARCHAR2(15):= 'Label';
252 L_COL_ATTR_CWMP CONSTANT VARCHAR2(30):= 'Personal Beans Name';
253 L_COL_ATTR_RNGD CONSTANT VARCHAR2(30):= 'Range Dimension AW Name';
254
255 L_COL_INST_NAME CONSTANT VARCHAR2(50):= 'Name';
256 L_COL_INST_ID CONSTANT VARCHAR2(10):= 'ID';
257 L_COL_INST_VIEW CONSTANT VARCHAR2(30):= 'Exposed Through View';
258 L_COL_INST_CCOL CONSTANT VARCHAR2(20):= 'Column In View';
259 L_COL_INST_DIMS CONSTANT VARCHAR2(30):= 'Dimensionality';
260
261
262 BEGIN
263
264 dbms_output.put_line( 'ALL DIMENSIONS REPORT');
265 dbms_output.put_line( substr(L_COL_DIM_NAME || L_BLANK, 1, 30) || '-' ||
266 substr(L_COL_DIM_OWN || L_BLANK, 1, 10) || '-' ||
267 substr(L_COL_DIM_PCWM || L_BLANK, 1, 30) || '-' ||
268 substr(L_COL_DIM_SCWM || L_BLANK, 1, 30) || '-' ||
269 substr(L_COL_DIM_AWNM || L_BLANK, 1, 20) || '-' ||
270 substr(L_COL_DIM_HRCN || L_BLANK, 1, 10) || '-' ||
271 substr(L_COL_DIM_EPBID || L_BLANK, 1 , 10));
272 dbms_output.put_line( L_BREAK);
273
274 for v_dim in c_dimensions loop
275 dbms_output.put_line( substr(v_dim.name || L_BLANK, 1, 30) || ' ' ||
276 substr(v_dim.is_owner_dim || L_BLANK, 1, 10) || ' ' ||
277 substr(v_dim.pers_cwm_name || L_BLANK, 1, 30) || ' ' ||
278 substr(v_dim.shar_cwm_name || L_BLANK, 1, 30) || ' ' ||
279 substr(v_dim.aw_name || L_BLANK, 1, 20) || ' ' ||
280 substr(v_dim.hier_cnt || L_BLANK, 1, 10) || ' ' ||
281 substr(v_dim.epb_id || L_BLANK, 1, 10));
282 end loop;
283
284 dbms_output.put_line( L_BLANK);
285 dbms_output.put_line( L_BLANK);
286 -- hierarchies for each dim
287 for v_dim in c_dimensions loop
288 l_dim_id := v_dim.dimension_id;
289 dbms_output.put_line( 'DIMENSION REPORT FOR ' || v_dim.name);
290 dbms_output.put_line( ' HIERARCHIES REPORT for dimension ' || v_dim.name);
291
292 dbms_output.put_line( substr(L_COL_HIER_NAME || L_BLANK, 1, 30) || '-' ||
293 substr(L_COL_HIER_TYPE || L_BLANK, 1, 20) || '-' ||
294 substr(L_COL_HIER_EPB || L_BLANK, 1, 30) || '-' ||
295 substr(L_COL_HIER_SHRT || L_BLANK, 1, 30) || '-' ||
296 substr(L_COL_HIER_PRST || L_BLANK, 1, 30));
297 dbms_output.put_line( L_BREAK);
298
299 for v_hier in c_hierarchies loop
300 dbms_output.put_line( substr(v_hier.name || L_BLANK, 1, 30) || ' ' ||
301 substr(v_hier.hier_type || L_BLANK, 1, 20) || ' ' ||
302 substr(v_hier.epb_id || L_BLANK, 1, 30) || ' ' ||
303 substr(v_hier.shar_table_name || L_BLANK, 1, 30) || ' ' ||
304 substr(v_hier.pers_table_name || L_BLANK, 1, 30));
305 end loop;
306
307 dbms_output.put_line( L_BLANK);
308 dbms_output.put_line( L_BLANK);
309 for v_hier in c_hierarchies loop
310 l_hier_id := v_hier.hierarchy_id;
311 dbms_output.put_line( ' LEVELS REPORT for hierarchy ' || v_hier.name || ' of dimension ' || v_dim.name);
312 dbms_output.put_line( substr(L_COL_LEVL_NAME || L_BLANK, 1, 30) || '-' ||
313 substr(L_COL_LEVL_EPB || L_BLANK, 1, 30) || '-' ||
314 substr(L_COL_LEVL_SHRB || L_BLANK, 1, 30) || '-' ||
315 substr(L_COL_LEVL_PRSB || L_BLANK, 1, 30));
316 dbms_output.put_line( L_BREAK);
317
318 for v_level in c_levels loop
319 dbms_output.put_line(substr(v_level.name || L_BLANK, 1, 30) || ' ' ||
320 substr(v_level.epb_id || L_BLANK, 1, 30) || ' ' ||
321 substr(v_level.shar_cwm_name || L_BLANK, 1, 30) || ' ' ||
322 substr(v_level.pers_cwm_name || L_BLANK, 1, 30));
323 end loop;
324 end loop;
325
326 dbms_output.put_line( L_BLANK);
327 dbms_output.put_line( L_BLANK);
328 dbms_output.put_line( ' ATTRIBUTES REPORT for dimension ' || v_dim.name);
329 dbms_output.put_line( substr(L_COL_ATTR_NAME || L_BLANK, 1, 50) || '-' ||
330 -- substr(L_COL_ATTR_TYPE || L_BLANK, 1, 15) || '-' ||
331 substr(L_COL_ATTR_LABL || L_BLANK, 1, 15) || '-' ||
332 substr(L_COL_ATTR_CWMP || L_BLANK, 1, 30) || '-' ||
333 substr(L_COL_ATTR_RNGD || L_BLANK, 1, 30));
334 dbms_output.put_line( L_BREAK);
335
336 for v_attr in c_attributes loop
337 dbms_output.put_line( substr(v_attr.name || L_BLANK, 1, 50) || ' ' ||
338 -- substr(v_attr.type || L_BLANK, 1, 15) || ' ' ||
339 substr(v_attr.label || L_BLANK, 1, 15) || ' ' ||
340 substr(v_attr.pers_cwm_name || L_BLANK, 1, 30) || ' ' ||
341 substr(v_attr.aw_name || L_BLANK, 1, 30));
342 end loop;
343
344 dbms_output.put_line( L_BLANK);
345 dbms_output.put_line( L_BLANK);
346 end loop;
347
348 dbms_output.put_line( L_BLANK);
349 dbms_output.put_line( L_BLANK);
350 dbms_output.put_line( 'BP INSTANCES REPORT');
351 dbms_output.put_line( substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
352 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
353 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
354 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
355 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
356 dbms_output.put_line( L_BREAK);
357
358 l_meas_type := 'SHARED_VIEW_DATA';
359 for v_bp_inst in c_bp_instances loop
360 l_cube_id := v_bp_inst.cube_id;
361 l_dimensionality := '';
362 for v_cube_dim in c_cube_dims loop
363 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
364 end loop;
365
366
367 dbms_output.put_line( substr(v_bp_inst.meas || L_BLANK, 1, 50) || ' ' ||
368 substr(v_bp_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
369 substr(v_bp_inst.cube || L_BLANK, 1, 30) || ' ' ||
370 substr(v_bp_inst.column_name || L_BLANK, 1, 20) || ' ' ||
371 substr(l_dimensionality || L_BLANK, 1, 30));
372 end loop;
373
374 dbms_output.put_line( L_BLANK);
375 dbms_output.put_line( L_BLANK);
376 dbms_output.put_line( 'CONTROLLED CALCS REPORT');
377 dbms_output.put_line( substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
378 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
379 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
380 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
381 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
382 dbms_output.put_line( L_BREAK);
383 l_meas_type := 'SHARED_VIEW_CALC';
384 for v_bp_inst in c_bp_instances loop
385 l_cube_id := v_bp_inst.cube_id;
386 l_dimensionality := '';
387 for v_cube_dim in c_cube_dims loop
388 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
389 end loop;
390
391
392 dbms_output.put_line( substr(v_bp_inst.meas || L_BLANK, 1, 50) || ' ' ||
393 substr(v_bp_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
394 substr(v_bp_inst.cube || L_BLANK, 1, 30) || ' ' ||
395 substr(v_bp_inst.column_name || L_BLANK, 1, 20) || ' ' ||
396 substr(l_dimensionality || L_BLANK, 1, 30));
397 end loop;
398
399 -- User Specific Reports
400 if p_user_id <> 0 then
401
402 dbms_output.put_line( L_BLANK);
403 dbms_output.put_line( L_BLANK);
404 dbms_output.put_line( 'ANALYST CALCS REPORT');
405 dbms_output.put_line( substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
406 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
407 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
408 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
409 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
410 dbms_output.put_line( L_BREAK);
411 l_meas_type := 'PERSONAL_CALC';
412 for v_prs_inst in c_prs_instances loop
413 l_cube_id := v_prs_inst.cube_id;
414 l_dimensionality := '';
415 for v_cube_dim in c_cube_dims loop
416 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
417 end loop;
418
419
420 dbms_output.put_line( substr(v_prs_inst.meas || L_BLANK, 1, 50) || ' ' ||
421 substr(v_prs_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
422 substr(v_prs_inst.cube || L_BLANK, 1, 30) || ' ' ||
423 substr(v_prs_inst.column_name || L_BLANK, 1, 20) || ' ' ||
424 substr(l_dimensionality || L_BLANK, 1, 30));
425 end loop;
426
427 dbms_output.put_line( L_BLANK);
428 dbms_output.put_line( L_BLANK);
429 dbms_output.put_line( 'WORKSHEETS REPORT (only those worksheets that have been opened at least once will appear)');
430 dbms_output.put_line( substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
431 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
432 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
433 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
434 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
435 dbms_output.put_line( L_BREAK);
436 l_meas_type := 'PERSONAL_DATA';
437 for v_prs_inst in c_prs_instances loop
438 l_cube_id := v_prs_inst.cube_id;
439 l_dimensionality := '';
440 for v_cube_dim in c_cube_dims loop
441 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
442 end loop;
443
444
445 dbms_output.put_line( substr(v_prs_inst.meas || L_BLANK, 1, 50) || ' ' ||
446 substr(v_prs_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
447 substr(v_prs_inst.cube || L_BLANK, 1, 30) || ' ' ||
448 substr(v_prs_inst.column_name || L_BLANK, 1, 20) || ' ' ||
449 substr(l_dimensionality || L_BLANK, 1, 30));
450 end loop;
451
452 dbms_output.put_line( L_BLANK);
453 dbms_output.put_line( L_BLANK);
454 dbms_output.put_line( 'APPROVER WORKSHEETS REPORT (only those worksheets that have been opened at least once will appear)');
455 dbms_output.put_line( substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
456 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
457 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
458 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
459 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
460 dbms_output.put_line( L_BREAK);
461 l_meas_type := 'APPROVER_DATA';
462 for v_prs_inst in c_prs_instances loop
463 l_cube_id := v_prs_inst.cube_id;
464 l_dimensionality := '';
465 for v_cube_dim in c_cube_dims loop
466 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
467 end loop;
468
469
470 dbms_output.put_line( substr(v_prs_inst.meas || L_BLANK, 1, 50) || ' ' ||
471 substr(v_prs_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
472 substr(v_prs_inst.cube || L_BLANK, 1, 30) || ' ' ||
473 substr(v_prs_inst.column_name || L_BLANK, 1, 20) || ' ' ||
474 substr(l_dimensionality || L_BLANK, 1, 30));
475 end loop;
476
477 end if;
478
479 END MDSCREEN;
480
481 procedure MDFILE(p_bus_area_id in number,
482 p_user_id in number,
483 p_file_dir in varchar2,
484 p_file_name in varchar2)
485 is
486 var1 number;
487 file1 utl_file.file_type;
488 file2 utl_file.file_type;
489
490 CURSOR c_dimensions is
491 select dvl.name,
492 dvl.is_owner_dim,
493 dvl.pers_cwm_name,
494 dvl.shar_cwm_name,
495 dvl.aw_name,
496 dvl.dimension_id,
497 (select count(*) from zpb_hierarchies where dimension_id = dvl.dimension_id and hier_type<>'NULL') hier_cnt,
498 dvl.epb_id
499 from zpb_dimensions_vl dvl
500 where bus_area_id = p_bus_area_id and
501 is_data_dim='YES'
502 order by is_owner_dim desc,
503 name;
504
505 v_dim c_dimensions%ROWTYPE;
506 l_dim_id number;
507
508 CURSOR c_hierarchies is
509 select name,
510 hier_type,
511 epb_id,
512 tab1.table_name pers_table_name,
513 tab2.table_name shar_table_name,
514 hierarchy_id
515 from zpb_hierarchies_vl hier,
516 zpb_tables tab1,
517 zpb_tables tab2
518 where hier.dimension_id = l_dim_id and
519 hier.pers_table_id = tab1.table_id and
520 hier.shar_table_id = tab2.table_id;
521
522
523 v_hier c_hierarchies%ROWTYPE;
524 l_hier_id number;
525
526 CURSOR c_levels is
527 select levs.name,
528 levs.epb_id,
529 levs.pers_cwm_name,
530 levs.shar_cwm_name
531 from zpb_levels_vl levs,
532 zpb_hier_level hrlv
533 where hrlv.level_id = levs.level_id and
534 hrlv.hier_id = l_hier_id
535 order by hrlv.level_order;
536
537
538 v_level c_levels%ROWTYPE;
539
540 CURSOR c_attributes is
541 select attrs.name,
542 attrs.type,
543 attrs.label,
544 dims.aw_name,
545 attrs.pers_cwm_name
546 from zpb_attributes_vl attrs,
547 zpb_dimensions dims
548 where attrs.dimension_id = l_dim_id and
549 dims.dimension_id = attrs.range_dim_id;
550
551 v_attr c_attributes%ROWTYPE;
552
553 l_meas_type varchar2(32);
554
555 CURSOR c_bp_instances is
556
557 select meas.name meas,
558 meas.instance_id,
559 cubs.name cube,
560 cols.column_name,
561 meas.cube_id
562
563 from zpb_measures meas,
564 zpb_columns cols,
565 zpb_cubes cubs
566
567 where meas.cube_id = cubs.cube_id and
568 meas.column_id = cols.column_id and
569 cubs.bus_area_id = p_bus_area_id and
570 meas.type = l_meas_type
571 order by meas.name;
572
573 v_bp_inst c_bp_instances%ROWTYPE;
574
575 CURSOR c_prs_instances is
576
577 select meas.name meas,
578 meas.instance_id,
579 cubs.name cube,
580 cols.column_name,
581 meas.cube_id
582
583 from zpb_measures meas,
584 zpb_columns cols,
585 zpb_cubes cubs
586
587 where meas.cube_id = cubs.cube_id and
588 meas.column_id = cols.column_id and
589 cubs.bus_area_id = p_bus_area_id and
590 meas.type = l_meas_type and
591 cubs.name like 'ZPB' || to_char(p_user_id) || 'A' || to_char(p_bus_area_id) || '/_%' escape '/'
592 order by meas.name;
593
594 v_prs_inst c_prs_instances%ROWTYPE;
595
596 l_cube_id number;
597
598 CURSOR c_cube_dims is
599
600 select dims.epb_id,
601 dims.aw_name
602 from zpb_cube_dims cds,
603 zpb_dimensions dims
604 where cds.dimension_id = dims.dimension_id
605 and cds.cube_id = l_cube_id;
606
607 v_cube_dim c_cube_dims%ROWTYPE;
608
609 l_dimensionality varchar2(100);
610
611 L_BLANK CONSTANT VARCHAR2(50):= substr(' ', 1, 50);
612 L_BREAK CONSTANT VARCHAR2(150):= substr('---------------------------------------------------------------------------------------------------------------------------------------------------------------------------', 1, 150);
613
614 L_COL_DIM_NAME CONSTANT VARCHAR2(30):= 'Dimension Name';
615 L_COL_DIM_OWN CONSTANT VARCHAR2(10):= 'Ownership';
616 L_COL_DIM_PCWM CONSTANT VARCHAR2(30):= 'Personal Beans Name';
617 L_COL_DIM_SCWM CONSTANT VARCHAR2(30):= 'Shared Beans Name';
618 L_COL_DIM_AWNM CONSTANT VARCHAR2(20):= 'AW Name';
619 L_COL_DIM_HRCN CONSTANT VARCHAR2(10):= 'Hier Count';
620 L_COL_DIM_EPBID CONSTANT VARCHAR2(10):= 'EPB ID';
621
622 L_COL_HIER_NAME CONSTANT VARCHAR2(30):= 'Hierarchy Name';
623 L_COL_HIER_TYPE CONSTANT VARCHAR2(20):= 'Type';
624 L_COL_HIER_EPB CONSTANT VARCHAR2(30):= 'EPB ID';
625 L_COL_HIER_SHRT CONSTANT VARCHAR2(30):= 'Shared View Name';
626 L_COL_HIER_PRST CONSTANT VARCHAR2(30):= 'Personal View Name';
627
628 L_COL_LEVL_NAME CONSTANT VARCHAR2(30):= 'Level Name';
629 L_COL_LEVL_EPB CONSTANT VARCHAR2(30):= 'EPB ID';
630 L_COL_LEVL_SHRB CONSTANT VARCHAR2(30):= 'Shared Beans Name';
631 L_COL_LEVL_PRSB CONSTANT VARCHAR2(30):= 'Personal Beans Name';
632
633 L_COL_ATTR_NAME CONSTANT VARCHAR2(50):= 'Attribute Name';
634 L_COL_ATTR_TYPE CONSTANT VARCHAR2(15):= 'Type';
635 L_COL_ATTR_LABL CONSTANT VARCHAR2(15):= 'Label';
636 L_COL_ATTR_CWMP CONSTANT VARCHAR2(30):= 'Personal Beans Name';
637 L_COL_ATTR_RNGD CONSTANT VARCHAR2(30):= 'Range Dimension AW Name';
638
639 L_COL_INST_NAME CONSTANT VARCHAR2(50):= 'Name';
640 L_COL_INST_ID CONSTANT VARCHAR2(10):= 'ID';
641 L_COL_INST_VIEW CONSTANT VARCHAR2(30):= 'Exposed Through View';
642 L_COL_INST_CCOL CONSTANT VARCHAR2(20):= 'Column In View';
643 L_COL_INST_DIMS CONSTANT VARCHAR2(30):= 'Dimensionality';
644
645
646 BEGIN
647
648 file1 := utl_file.fopen(p_file_dir, p_file_name, 'w');
649
650 UTL_FILE.PUT_LINE(file1, 'ALL DIMENSIONS REPORT');
651 UTL_FILE.PUT_LINE(file1, substr(L_COL_DIM_NAME || L_BLANK, 1, 30) || '-' ||
652 substr(L_COL_DIM_OWN || L_BLANK, 1, 10) || '-' ||
653 substr(L_COL_DIM_PCWM || L_BLANK, 1, 30) || '-' ||
654 substr(L_COL_DIM_SCWM || L_BLANK, 1, 30) || '-' ||
655 substr(L_COL_DIM_AWNM || L_BLANK, 1, 20) || '-' ||
656 substr(L_COL_DIM_HRCN || L_BLANK, 1, 10) || '-' ||
657 substr(L_COL_DIM_EPBID || L_BLANK, 1 , 10));
658 UTL_FILE.PUT_LINE(file1, L_BREAK);
659
660 for v_dim in c_dimensions loop
661 UTL_FILE.PUT_LINE(file1, substr(v_dim.name || L_BLANK, 1, 30) || ' ' ||
662 substr(v_dim.is_owner_dim || L_BLANK, 1, 10) || ' ' ||
663 substr(v_dim.pers_cwm_name || L_BLANK, 1, 30) || ' ' ||
664 substr(v_dim.shar_cwm_name || L_BLANK, 1, 30) || ' ' ||
665 substr(v_dim.aw_name || L_BLANK, 1, 20) || ' ' ||
666 substr(v_dim.hier_cnt || L_BLANK, 1, 10) || ' ' ||
667 substr(v_dim.epb_id || L_BLANK, 1, 10));
668 end loop;
669
670 UTL_FILE.PUT_LINE(file1, L_BLANK);
671 UTL_FILE.PUT_LINE(file1, L_BLANK);
672 -- hierarchies for each dim
673 for v_dim in c_dimensions loop
674 l_dim_id := v_dim.dimension_id;
675 UTL_FILE.PUT_LINE(file1, 'DIMENSION REPORT FOR ' || v_dim.name);
676 UTL_FILE.PUT_LINE(file1, ' HIERARCHIES REPORT for dimension ' || v_dim.name);
677
678 UTL_FILE.PUT_LINE(file1, substr(L_COL_HIER_NAME || L_BLANK, 1, 30) || '-' ||
679 substr(L_COL_HIER_TYPE || L_BLANK, 1, 20) || '-' ||
680 substr(L_COL_HIER_EPB || L_BLANK, 1, 30) || '-' ||
681 substr(L_COL_HIER_SHRT || L_BLANK, 1, 30) || '-' ||
682 substr(L_COL_HIER_PRST || L_BLANK, 1, 30));
683 UTL_FILE.PUT_LINE(file1, L_BREAK);
684
685 for v_hier in c_hierarchies loop
686 UTL_FILE.PUT_LINE(file1, substr(v_hier.name || L_BLANK, 1, 30) || ' ' ||
687 substr(v_hier.hier_type || L_BLANK, 1, 20) || ' ' ||
688 substr(v_hier.epb_id || L_BLANK, 1, 30) || ' ' ||
689 substr(v_hier.shar_table_name || L_BLANK, 1, 30) || ' ' ||
690 substr(v_hier.pers_table_name || L_BLANK, 1, 30));
691 end loop;
692
693 UTL_FILE.PUT_LINE(file1, L_BLANK);
694 UTL_FILE.PUT_LINE(file1, L_BLANK);
695 for v_hier in c_hierarchies loop
696 l_hier_id := v_hier.hierarchy_id;
697 UTL_FILE.PUT_LINE(file1, ' LEVELS REPORT for hierarchy ' || v_hier.name || ' of dimension ' || v_dim.name);
698 UTL_FILE.PUT_LINE(file1, substr(L_COL_LEVL_NAME || L_BLANK, 1, 30) || '-' ||
699 substr(L_COL_LEVL_EPB || L_BLANK, 1, 30) || '-' ||
700 substr(L_COL_LEVL_SHRB || L_BLANK, 1, 30) || '-' ||
701 substr(L_COL_LEVL_PRSB || L_BLANK, 1, 30));
702 UTL_FILE.PUT_LINE(file1, L_BREAK);
703
704 for v_level in c_levels loop
705 UTL_FILE.PUT_LINE(file1,substr(v_level.name || L_BLANK, 1, 30) || ' ' ||
706 substr(v_level.epb_id || L_BLANK, 1, 30) || ' ' ||
707 substr(v_level.shar_cwm_name || L_BLANK, 1, 30) || ' ' ||
708 substr(v_level.pers_cwm_name || L_BLANK, 1, 30));
709 end loop;
710 end loop;
711
712 UTL_FILE.PUT_LINE(file1, L_BLANK);
713 UTL_FILE.PUT_LINE(file1, L_BLANK);
714 UTL_FILE.PUT_LINE(file1, ' ATTRIBUTES REPORT for dimension ' || v_dim.name);
715 UTL_FILE.PUT_LINE(file1, substr(L_COL_ATTR_NAME || L_BLANK, 1, 50) || '-' ||
716 -- substr(L_COL_ATTR_TYPE || L_BLANK, 1, 15) || '-' ||
717 substr(L_COL_ATTR_LABL || L_BLANK, 1, 15) || '-' ||
718 substr(L_COL_ATTR_CWMP || L_BLANK, 1, 30) || '-' ||
719 substr(L_COL_ATTR_RNGD || L_BLANK, 1, 30));
720 UTL_FILE.PUT_LINE(file1, L_BREAK);
721
722 for v_attr in c_attributes loop
723 UTL_FILE.PUT_LINE(file1, substr(v_attr.name || L_BLANK, 1, 50) || ' ' ||
724 -- substr(v_attr.type || L_BLANK, 1, 15) || ' ' ||
725 substr(v_attr.label || L_BLANK, 1, 15) || ' ' ||
726 substr(v_attr.pers_cwm_name || L_BLANK, 1, 30) || ' ' ||
727 substr(v_attr.aw_name || L_BLANK, 1, 30));
728 end loop;
729
730 UTL_FILE.PUT_LINE(file1, L_BLANK);
731 UTL_FILE.PUT_LINE(file1, L_BLANK);
732 end loop;
733
734 UTL_FILE.PUT_LINE(file1, L_BLANK);
735 UTL_FILE.PUT_LINE(file1, L_BLANK);
736 UTL_FILE.PUT_LINE(file1, 'BP INSTANCES REPORT');
737 UTL_FILE.PUT_LINE(file1, substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
738 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
739 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
740 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
741 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
742 UTL_FILE.PUT_LINE(file1, L_BREAK);
743
744 l_meas_type := 'SHARED_VIEW_DATA';
745 for v_bp_inst in c_bp_instances loop
746 l_cube_id := v_bp_inst.cube_id;
747 l_dimensionality := '';
748 for v_cube_dim in c_cube_dims loop
749 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
750 end loop;
751
752
753 UTL_FILE.PUT_LINE(file1, substr(v_bp_inst.meas || L_BLANK, 1, 50) || ' ' ||
754 substr(v_bp_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
755 substr(v_bp_inst.cube || L_BLANK, 1, 30) || ' ' ||
756 substr(v_bp_inst.column_name || L_BLANK, 1, 20) || ' ' ||
757 substr(l_dimensionality || L_BLANK, 1, 30));
758 end loop;
759
760 UTL_FILE.PUT_LINE(file1, L_BLANK);
761 UTL_FILE.PUT_LINE(file1, L_BLANK);
762 UTL_FILE.PUT_LINE(file1, 'CONTROLLED CALCS REPORT');
763 UTL_FILE.PUT_LINE(file1, substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
764 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
765 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
766 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
767 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
768 UTL_FILE.PUT_LINE(file1, L_BREAK);
769 l_meas_type := 'SHARED_VIEW_CALC';
770 for v_bp_inst in c_bp_instances loop
771 l_cube_id := v_bp_inst.cube_id;
772 l_dimensionality := '';
773 for v_cube_dim in c_cube_dims loop
774 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
775 end loop;
776
777
778 UTL_FILE.PUT_LINE(file1, substr(v_bp_inst.meas || L_BLANK, 1, 50) || ' ' ||
779 substr(v_bp_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
780 substr(v_bp_inst.cube || L_BLANK, 1, 30) || ' ' ||
781 substr(v_bp_inst.column_name || L_BLANK, 1, 20) || ' ' ||
782 substr(l_dimensionality || L_BLANK, 1, 30));
783 end loop;
784
785 -- User Specific Reports
786 if p_user_id <> 0 then
787
788 UTL_FILE.PUT_LINE(file1, L_BLANK);
789 UTL_FILE.PUT_LINE(file1, L_BLANK);
790 UTL_FILE.PUT_LINE(file1, 'ANALYST CALCS REPORT');
791 UTL_FILE.PUT_LINE(file1, substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
792 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
793 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
794 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
795 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
796 UTL_FILE.PUT_LINE(file1, L_BREAK);
797 l_meas_type := 'PERSONAL_CALC';
798 for v_prs_inst in c_prs_instances loop
799 l_cube_id := v_prs_inst.cube_id;
800 l_dimensionality := '';
801 for v_cube_dim in c_cube_dims loop
802 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
803 end loop;
804
805
806 UTL_FILE.PUT_LINE(file1, substr(v_prs_inst.meas || L_BLANK, 1, 50) || ' ' ||
807 substr(v_prs_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
808 substr(v_prs_inst.cube || L_BLANK, 1, 30) || ' ' ||
809 substr(v_prs_inst.column_name || L_BLANK, 1, 20) || ' ' ||
810 substr(l_dimensionality || L_BLANK, 1, 30));
811 end loop;
812
813 UTL_FILE.PUT_LINE(file1, L_BLANK);
814 UTL_FILE.PUT_LINE(file1, L_BLANK);
815 UTL_FILE.PUT_LINE(file1, 'WORKSHEETS REPORT (only those worksheets that have been opened at least once will appear)');
816 UTL_FILE.PUT_LINE(file1, substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
817 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
818 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
819 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
820 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
821 UTL_FILE.PUT_LINE(file1, L_BREAK);
822 l_meas_type := 'PERSONAL_DATA';
823 for v_prs_inst in c_prs_instances loop
824 l_cube_id := v_prs_inst.cube_id;
825 l_dimensionality := '';
826 for v_cube_dim in c_cube_dims loop
827 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
828 end loop;
829
830
831 UTL_FILE.PUT_LINE(file1, substr(v_prs_inst.meas || L_BLANK, 1, 50) || ' ' ||
832 substr(v_prs_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
833 substr(v_prs_inst.cube || L_BLANK, 1, 30) || ' ' ||
834 substr(v_prs_inst.column_name || L_BLANK, 1, 20) || ' ' ||
835 substr(l_dimensionality || L_BLANK, 1, 30));
836 end loop;
837
838 UTL_FILE.PUT_LINE(file1, L_BLANK);
839 UTL_FILE.PUT_LINE(file1, L_BLANK);
840 UTL_FILE.PUT_LINE(file1, 'APPROVER WORKSHEETS REPORT (only those worksheets that have been opened at least once will appear)');
841 UTL_FILE.PUT_LINE(file1, substr(L_COL_INST_NAME || L_BLANK, 1, 50) || ' ' ||
842 substr(L_COL_INST_ID || L_BLANK, 1, 10) || ' ' ||
843 substr(L_COL_INST_VIEW || L_BLANK, 1, 30) || ' ' ||
844 substr(L_COL_INST_CCOL || L_BLANK, 1, 20) || ' ' ||
845 substr(L_COL_INST_DIMS || L_BLANK, 1, 30));
846 UTL_FILE.PUT_LINE(file1, L_BREAK);
847 l_meas_type := 'APPROVER_DATA';
848 for v_prs_inst in c_prs_instances loop
849 l_cube_id := v_prs_inst.cube_id;
850 l_dimensionality := '';
851 for v_cube_dim in c_cube_dims loop
852 l_dimensionality := l_dimensionality || v_cube_dim.epb_id || ' ';
853 end loop;
854
855
856 UTL_FILE.PUT_LINE(file1, substr(v_prs_inst.meas || L_BLANK, 1, 50) || ' ' ||
857 substr(v_prs_inst.instance_id || L_BLANK, 1, 10) || ' ' ||
858 substr(v_prs_inst.cube || L_BLANK, 1, 30) || ' ' ||
859 substr(v_prs_inst.column_name || L_BLANK, 1, 20) || ' ' ||
860 substr(l_dimensionality || L_BLANK, 1, 30));
861 end loop;
862
863 end if;
864
865
866 UTL_FILE.FFLUSH (file1);
867 UTL_FILE.FCLOSE(file1);
868
869 end MDFILE;
870
871 procedure REBUILD_MD(p_business_area IN NUMBER)
872 is
873
874 l_data_aw varchar2(64);
875 l_user number;
876 begin
877
878 select min(USER_ID)
879 into l_user
880 from ZPB_USERS
881 where BUSINESS_AREA_ID = p_business_area;
882
883 select data_aw
884 into l_data_aw
885 from zpb_business_areas
886 where BUSINESS_AREA_ID = p_business_area;
887
888 zpb_debug.init(l_user, p_business_area);
889
890 dbms_aw.execute('aw attach ZPB.ZPBCODE');
891 dbms_aw.execute('aw attach ZPB.' || l_data_aw);
892 dbms_aw.execute('aw aliaslist ZPB.' || l_data_aw || ' alias SHARED');
893
894 zpb_metadata_pkg.build(l_data_aw, l_data_aw, 'SHARED', 'Y');
895 commit;
896 dbms_aw.execute('aw detach ZPB.ZPBCODE');
897 dbms_aw.execute('aw detach ZPB.' || l_data_aw);
898
899 end REBUILD_MD;
900
901 end ZPB_DEBUG;