[Home] [Help]
PACKAGE BODY: APPS.ZPB_METADATA_PKG
Source
1 package body ZPB_METADATA_PKG as
2 /* $Header: ZPBMDPKB.pls 120.31 2007/12/04 14:35:35 mbhat noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(16) := 'ZPB_METADATA_PKG';
5
6 -------------------------------------------------------------------------------
7 -- insertDimensionRecord - Private function that inserts a
8 -- zpb_md_records.dimensions_entry into zpb_dimensions and
9 -- returns the primary key of the newly created entry
10 --
11 -- if p_primary_key_provided is true, then the primary key for the
12 -- dimension to be inserted is provided and we do not select it from sequence
13 -------------------------------------------------------------------------------
14 function insertDimensionRecord(p_dimension_rec in zpb_md_records.DIMENSIONS_ENTRY,
15 p_primary_key_provided in boolean default false)
16 return number is
17
18 l_dimension_rec zpb_md_records.dimensions_entry;
19 bus_area_id_num number;
20 insert_flag boolean;
21 begin
22
23 l_dimension_rec := p_dimension_rec;
24
25 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
26 insert_flag := p_primary_key_provided;
27
28 if insert_flag = false then
29
30 begin
31
32 select dimension_id into l_dimension_rec.DimensionId
33 from zpb_dimensions
34 where bus_area_id = bus_area_id_num and
35 epb_id = l_dimension_rec.EpbId;
36
37 update zpb_dimensions
38 set
39 AW_NAME = l_dimension_rec.AwName,
40 DEFAULT_HIER = l_dimension_rec.DefaultHier,
41 DEFAULT_MEMBER = l_dimension_rec.DefaultMember,
42 DIM_CODE = l_dimension_rec.DimCode,
43 DIM_TYPE = l_dimension_rec.DimType,
44 IS_CURRENCY_DIM = l_dimension_rec.IsCurrencyDim,
45 IS_DATA_DIM = l_dimension_rec.IsDataDim,
46 IS_OWNER_DIM = l_dimension_rec.IsOwnerDim,
47 PERS_CWM_NAME = l_dimension_rec.PersCWMName,
48 PERS_TABLE_ID = l_dimension_rec.PersTableId,
49 SHAR_CWM_NAME = l_dimension_rec.SharCWMName,
50 SHAR_TABLE_ID = l_dimension_rec.SharTableId,
51 ANNOTATION_DIM = l_dimension_rec.AnnotationDim,
52 CREATED_BY = FND_GLOBAL.USER_ID,
53 CREATION_DATE = sysdate,
54 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
55 LAST_UPDATE_DATE = sysdate,
56 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
57
58 where dimension_id = l_dimension_rec.DimensionId;
59
60
61 exception
62 when NO_DATA_FOUND then
63 SELECT zpb_dimensions_seq.NEXTVAL INTO l_dimension_rec.DimensionId FROM DUAL;
64 insert_flag := true;
65 end;
66 end if;
67
68 if insert_flag=true then
69
70 insert into zpb_dimensions
71 (
72 AW_NAME,
73 BUS_AREA_ID,
74 DEFAULT_HIER,
75 DEFAULT_MEMBER,
76 DIMENSION_ID,
77 DIM_CODE,
78 DIM_TYPE,
79 EPB_ID,
80 IS_CURRENCY_DIM,
81 IS_DATA_DIM,
82 IS_OWNER_DIM,
83 PERS_CWM_NAME,
84 PERS_TABLE_ID,
85 SHAR_CWM_NAME,
86 SHAR_TABLE_ID,
87 ANNOTATION_DIM,
88 CREATED_BY,
89 CREATION_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATE_LOGIN
93 )
94 VALUES
95 (
96 l_dimension_rec.AwName,
97 bus_area_id_num,
98 l_dimension_rec.DefaultHier,
99 l_dimension_rec.DefaultMember,
100 l_dimension_rec.DimensionId,
101 l_dimension_rec.DimCode,
102 l_dimension_rec.DimType,
103 l_dimension_rec.EpbId,
104 l_dimension_rec.IsCurrencyDim,
105 l_dimension_rec.IsDataDim,
106 l_dimension_rec.IsOwnerDim,
107 l_dimension_rec.PersCWMName,
108 l_dimension_rec.PersTableId,
109 l_dimension_rec.SharCWMName,
110 l_dimension_rec.SharTableId,
111 l_dimension_rec.AnnotationDim,
112 FND_GLOBAL.USER_ID,
113 sysdate,
114 FND_GLOBAL.USER_ID,
115 sysdate,
116 FND_GLOBAL.LOGIN_ID
117 );
118
119 end if;
120
121 return l_dimension_rec.DimensionId;
122 end insertDimensionRecord;
123
124 -------------------------------------------------------------------------------
125 -- insertTableRecord - Private function that inserts a
126 -- zpb_md_records.tables_entry into zpb_tables and
127 -- returns the primary key of the newly created entry
128 --
129 -- if p_primary_key_provided is true, then the primary key for the
130 -- table to be inserted is provided and we do not select it from sequence
131 -------------------------------------------------------------------------------
132 function insertTableRecord(p_table_rec in zpb_md_records.TABLES_ENTRY,
133 p_primary_key_provided in boolean default false)
134 return number is
135 l_table_rec zpb_md_records.tables_entry;
136 bus_area_id_num number;
137 insert_flag boolean;
138
139 begin
140
141 l_table_rec := p_table_rec;
142 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
143 insert_flag := p_primary_key_provided;
144
145 if p_primary_key_provided = false then
146
147 begin
148
149 select table_id into l_table_rec.TableId
150 from zpb_tables
151 where bus_area_id = bus_area_id_num and
152 table_name = l_table_rec.TableName;
153
154 update zpb_tables
155 set
156 AW_NAME = l_table_rec.AwName,
157 TABLE_TYPE = l_table_rec.TableType,
158 CREATED_BY = FND_GLOBAL.USER_ID,
159 CREATION_DATE = sysdate,
160 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
161 LAST_UPDATE_DATE = sysdate,
162 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
163 where table_id = l_table_rec.TableId;
164
165 exception
166 when NO_DATA_FOUND then
167 SELECT zpb_tables_seq.NEXTVAL INTO l_table_rec.TableId FROM DUAL;
168 insert_flag := true;
169 end;
170 end if;
171
172 if insert_flag = true then
173
174 insert into zpb_tables
175 (
176 AW_NAME,
177 BUS_AREA_ID,
178 TABLE_ID,
179 TABLE_NAME,
180 TABLE_TYPE,
181 CREATED_BY,
182 CREATION_DATE,
183 LAST_UPDATED_BY,
184 LAST_UPDATE_DATE,
185 LAST_UPDATE_LOGIN
186 )
187 VALUES
188 (
189 l_table_rec.AwName,
190 bus_area_id_num,
191 l_table_rec.TableId,
192 l_table_rec.TableName,
193 l_table_rec.TableType,
194 FND_GLOBAL.USER_ID,
195 sysdate,
196 FND_GLOBAL.USER_ID,
197 sysdate,
198 FND_GLOBAL.LOGIN_ID
199 );
200
201 end if;
202
203 return l_table_rec.TableId;
204 end insertTableRecord;
205
206
207 -------------------------------------------------------------------------------
208 -- insertColumnRecord - Private function that inserts a
209 -- zpb_md_records.columns_entry into zpb_columns and
210 -- returns the primary key of the newly created entry
211 -------------------------------------------------------------------------------
212 function insertColumnRecord(p_col_rec in zpb_md_records.columns_entry)
213 return number is
214 l_col_rec zpb_md_records.columns_entry;
215 bus_area_id_num number;
216
217 begin
218
219 l_col_rec := p_col_rec;
220
221 begin
222
223 select column_id into l_col_rec.ColumnId
224 from zpb_columns
225 where table_id = l_col_rec.TableId and
226 column_name = l_col_rec.ColumnName;
227
228 update zpb_columns
229 set
230 AW_NAME = l_col_rec.AwName,
231 COLUMN_NAME = l_col_rec.ColumnName,
232 COLUMN_TYPE = l_col_rec.ColumnType,
233 TABLE_ID = l_col_rec.TableId,
234 CREATED_BY = FND_GLOBAL.USER_ID,
235 CREATION_DATE = sysdate,
236 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
237 LAST_UPDATE_DATE = sysdate,
238 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
239 where column_id = l_col_rec.ColumnId;
240
241
242 exception
243 when NO_DATA_FOUND then
244 SELECT zpb_columns_seq.NEXTVAL INTO l_col_rec.ColumnId FROM DUAL;
245
246 insert into zpb_columns
247 (
248 COLUMN_ID,
249 AW_NAME,
250 COLUMN_NAME,
251 COLUMN_TYPE,
252 TABLE_ID,
253 CREATED_BY,
254 CREATION_DATE,
255 LAST_UPDATED_BY,
256 LAST_UPDATE_DATE,
257 LAST_UPDATE_LOGIN
258 )
259 VALUES
260 (
261 l_col_rec.ColumnId,
262 l_col_rec.AwName,
263 l_col_rec.ColumnName,
264 l_col_rec.ColumnType,
265 l_col_rec.TableId,
266 FND_GLOBAL.USER_ID,
267 sysdate,
268 FND_GLOBAL.USER_ID,
269 sysdate,
270 FND_GLOBAL.LOGIN_ID
271 );
272 end;
273
274 return l_col_rec.ColumnId;
275 end insertColumnRecord;
276
277 -------------------------------------------------------------------------------
278 -- insertLevelRecord - Private function that inserts a
279 -- zpb_md_records.levels_entry into zpb_levels and
280 -- returns the primary key of the newly created entry
281 -------------------------------------------------------------------------------
282 function insertLevelRecord(p_level_rec zpb_md_records.levels_entry)
283 return number is
284
285 l_level_rec zpb_md_records.levels_entry;
286
287 begin
288
289 l_level_rec := p_level_rec;
290
291 begin
292
293 select level_id into l_level_rec.LevelId
294 from zpb_levels
295 where dimension_id = l_level_rec.DimensionId and
296 pers_cwm_name = l_level_rec.PersCWMName;
297
298 update zpb_levels
299 set
300
301 PERS_CWM_NAME = l_level_rec.PersCWMName,
302 DIMENSION_ID = l_level_rec.DimensionId,
303 EPB_ID = l_level_rec.EpbId,
304 SHAR_CWM_NAME = l_level_rec.SharCWMName,
305 PERS_LEVEL_FLAG = l_level_rec.PersLevelFlag,
306 CREATED_BY = FND_GLOBAL.USER_ID,
307 CREATION_DATE = sysdate,
308 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
309 LAST_UPDATE_DATE = sysdate,
310 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
311 where level_id = l_level_rec.LevelId;
312
313 exception
314 when NO_DATA_FOUND then
315 SELECT zpb_levels_seq.NEXTVAL INTO l_level_rec.LevelId FROM DUAL;
316
317 insert into zpb_levels
318 (
319 PERS_CWM_NAME,
320 DIMENSION_ID,
321 EPB_ID,
322 LEVEL_ID,
323 SHAR_CWM_NAME,
324 PERS_LEVEL_FLAG,
325 CREATED_BY,
326 CREATION_DATE,
327 LAST_UPDATED_BY,
328 LAST_UPDATE_DATE,
329 LAST_UPDATE_LOGIN
330 )
331 VALUES
332 (
333 l_level_rec.PersCWMName,
334 l_level_rec.DimensionId,
335 l_level_rec.EpbId,
336 l_level_rec.LevelId,
337 l_level_rec.SharCWMName,
338 l_level_rec.PersLevelFlag,
339 FND_GLOBAL.USER_ID,
340 sysdate,
341 FND_GLOBAL.USER_ID,
342 sysdate,
343 FND_GLOBAL.LOGIN_ID
344 );
345 end;
346
347 return l_level_rec.LevelId;
348 end insertLevelRecord;
349
350 -------------------------------------------------------------------------------
351 -- insertHierLevelRecord - Private function that inserts a
352 -- zpb_md_records.hier_level_entry into zpb_hier_levels and
353 -- returns the primary key of the newly created entry
354 -------------------------------------------------------------------------------
355 function insertHierLevelRecord(p_hier_level_rec zpb_md_records.hier_level_entry)
356 return number is
357
358 l_hier_level_rec zpb_md_records.hier_level_entry;
359
360 begin
361
362 l_hier_level_rec := p_hier_level_rec;
363
364 begin
365
366 select relation_id into l_hier_level_rec.RelationId
367 from zpb_hier_level
368 where level_id = l_hier_level_rec.LevelId and
369 hier_id = l_hier_level_rec.HierId;
370
371 update zpb_hier_level
372 set
373
374 LEVEL_ORDER = l_hier_level_rec.LevelOrder,
375 PERS_COL_ID = l_hier_level_rec.PersColId,
376 SHAR_COL_ID = l_hier_level_rec.SharColId,
377 CREATED_BY = FND_GLOBAL.USER_ID,
378 CREATION_DATE = sysdate,
379 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
380 LAST_UPDATE_DATE = sysdate,
381 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
382 where relation_id = l_hier_level_rec.RelationId;
383
384 exception
385 when NO_DATA_FOUND then
386 SELECT zpb_hier_level_seq.NEXTVAL INTO l_hier_level_rec.RelationId FROM DUAL;
387
388 insert into zpb_hier_level
389 (
390 HIER_ID,
391 LEVEL_ID,
392 LEVEL_ORDER,
393 PERS_COL_ID,
394 RELATION_ID,
395 SHAR_COL_ID,
396 CREATED_BY,
397 CREATION_DATE,
398 LAST_UPDATED_BY,
399 LAST_UPDATE_DATE,
400 LAST_UPDATE_LOGIN
401 )
402 VALUES
403 (
404 l_hier_level_rec.HierId,
405 l_hier_level_rec.LevelId,
406 l_hier_level_rec.LevelOrder,
407 l_hier_level_rec.PersColId,
408 l_hier_level_rec.RelationId,
409 l_hier_level_rec.SharColId,
410 FND_GLOBAL.USER_ID,
411 sysdate,
412 FND_GLOBAL.USER_ID,
413 sysdate,
414 FND_GLOBAL.LOGIN_ID
415 );
416
417 end;
418
419 return l_hier_level_rec.RelationId;
420
421 end insertHierLevelRecord;
422
423 -------------------------------------------------------------------------------
424 -- inserAttributeRecord - Private function that inserts a
425 -- zpb_md_records.attributes_entry into zpb_attributes and
426 -- returns the primary key of the newly created entry
427 -------------------------------------------------------------------------------
428 function insertAttributeRecord(p_attr_rec zpb_md_records.attributes_entry)
429 return number is
430
431 l_attr_rec zpb_md_records.attributes_entry;
432
433 begin
434
435 l_attr_rec := p_attr_rec;
436
437 begin
438
439 select attribute_id into l_attr_rec.AttributeId
440 from zpb_attributes
441 where dimension_id = l_attr_rec.DimensionId and
442 pers_cwm_name = l_attr_rec.PersCWMName;
443
444 update zpb_attributes
445 set
446 DIMENSION_ID = l_attr_rec.DimensionId,
447 EPB_ID = l_attr_rec.EpbId,
448 LABEL = l_attr_rec.Label,
449 RANGE_DIM_ID = l_attr_rec.RangeDimId,
450 SHAR_CWM_NAME = l_attr_rec.SharCWMName,
451 TYPE = l_attr_rec.Type,
452 PERS_CWM_NAME = l_attr_rec.PersCWMName,
453 FEM_ATTRIBUTE_ID = l_attr_rec.FEMAttrId,
454 CREATED_BY = FND_GLOBAL.USER_ID,
455 CREATION_DATE = sysdate,
456 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
457 LAST_UPDATE_DATE = sysdate,
458 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
459 where attribute_id = l_attr_rec.AttributeId;
460
461 exception
462 when NO_DATA_FOUND then
463 SELECT zpb_attributes_seq.NEXTVAL INTO l_attr_rec.AttributeId FROM DUAL;
464
465 insert into zpb_attributes
466 (
467
468 ATTRIBUTE_ID,
469 DIMENSION_ID,
470 EPB_ID,
471 LABEL,
472 RANGE_DIM_ID,
473 SHAR_CWM_NAME,
474 TYPE,
475 PERS_CWM_NAME,
476 FEM_ATTRIBUTE_ID,
477 CREATED_BY,
478 CREATION_DATE,
479 LAST_UPDATED_BY,
480 LAST_UPDATE_DATE,
481 LAST_UPDATE_LOGIN
482 )
483 VALUES
484 (
485 l_attr_rec.AttributeId,
486 l_attr_rec.DimensionId,
487 l_attr_rec.EpbId,
488 l_attr_rec.Label,
489 l_attr_rec.RangeDimId,
490 l_attr_rec.SharCWMName,
491 l_attr_rec.Type,
492 l_attr_rec.PersCWMName,
493 l_attr_rec.FEMAttrId,
494 FND_GLOBAL.USER_ID,
495 sysdate,
496 FND_GLOBAL.USER_ID,
497 sysdate,
498 FND_GLOBAL.LOGIN_ID
499 );
500
501 end;
502
503 return l_attr_rec.AttributeId;
504
505 end insertAttributeRecord;
506
507
508 -------------------------------------------------------------------------------
509 -- insertAttrTableColRecord - Private function that inserts a
510 -- zpb_md_records.attr_table_col_entry into zpb_attr_table_col and
511 -- returns the primary key of the newly created entry
512 -------------------------------------------------------------------------------
513 function insertAttrTableColRecord(p_attr_table_col_rec zpb_md_records.attr_table_col_entry)
514 return number is
515
516 l_attr_table_col_rec zpb_md_records.attr_table_col_entry;
517
518 begin
519
520 l_attr_table_col_rec := p_attr_table_col_rec;
521
522 begin
523
524 select relation_id into l_attr_table_col_rec.RelationId
525 from zpb_attr_table_col
526 where attribute_id = l_attr_table_col_rec.AttributeId and
527 table_id = l_attr_table_col_rec.TableId and
528 column_id = l_attr_table_col_rec.ColumnId;
529
530 update zpb_attr_table_col
531 set
532
533 CREATED_BY = FND_GLOBAL.USER_ID,
534 CREATION_DATE = sysdate,
535 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
536 LAST_UPDATE_DATE = sysdate,
537 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
538 where relation_id = l_attr_table_col_rec.RelationId;
539
540 exception
541 when NO_DATA_FOUND then
542 SELECT zpb_attr_table_col_seq.NEXTVAL INTO l_attr_table_col_rec.RelationId FROM DUAL;
543
544 insert into zpb_attr_table_col
545 (
546 ATTRIBUTE_ID,
547 COLUMN_ID,
548 RELATION_ID,
549 TABLE_ID,
550 CREATED_BY,
551 CREATION_DATE,
552 LAST_UPDATED_BY,
553 LAST_UPDATE_DATE,
554 LAST_UPDATE_LOGIN
555 )
556 VALUES
557 (
558 l_attr_table_col_rec.AttributeId,
559 l_attr_table_col_rec.ColumnId,
560 l_attr_table_col_rec.RelationId,
561 l_attr_table_col_rec.TableId,
562 FND_GLOBAL.USER_ID,
563 sysdate,
564 FND_GLOBAL.USER_ID,
565 sysdate,
566 FND_GLOBAL.LOGIN_ID
567 );
568
569 end;
570
571 return l_attr_table_col_rec.RelationId;
572
573 end insertAttrTableColRecord;
574
575
576 -------------------------------------------------------------------------------
577 -- insertCubeDimsRecord - Private function that inserts a
578 -- zpb_md_records.cube_dims_entry into zpb_cube_dims and
579 -- returns the primary key of the newly created entry
580 -------------------------------------------------------------------------------
581 function insertCubeDimsRecord(p_cube_dims_rec zpb_md_records.cube_dims_entry)
582 return number is
583
584 l_cube_dims_rec zpb_md_records.cube_dims_entry;
585
586 begin
587
588 l_cube_dims_rec := p_cube_dims_rec;
589
590 begin
591
592 select relation_id into l_cube_dims_rec.RelationId
593 from zpb_cube_dims
594 where cube_id = l_cube_dims_rec.CubeId and
595 dimension_id = l_cube_dims_rec.DimensionId;
596
597 update zpb_cube_dims
598 set
599
600 COLUMN_ID = l_cube_dims_rec.ColumnId,
601 CREATED_BY = FND_GLOBAL.USER_ID,
602 CREATION_DATE = sysdate,
603 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
604 LAST_UPDATE_DATE = sysdate,
605 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
606 where relation_id = l_cube_dims_rec.RelationId;
607
608 exception
609 when NO_DATA_FOUND then
610 SELECT zpb_cube_dims_seq.NEXTVAL INTO l_cube_dims_rec.RelationId FROM DUAL;
611
612 insert into zpb_cube_dims
613 (
614
615 COLUMN_ID,
616 CUBE_ID,
617 DIMENSION_ID,
618 RELATION_ID,
619 CREATED_BY,
620 CREATION_DATE,
621 LAST_UPDATED_BY,
622 LAST_UPDATE_DATE,
623 LAST_UPDATE_LOGIN
624 )
625 VALUES
626 (
627 l_cube_dims_rec.ColumnId,
628 l_cube_dims_rec.CubeId,
629 l_cube_dims_rec.DimensionId,
630 l_cube_dims_rec.RelationId,
631 FND_GLOBAL.USER_ID,
632 sysdate,
633 FND_GLOBAL.USER_ID,
634 sysdate,
635 FND_GLOBAL.LOGIN_ID
636 );
637
638 end;
639
640 return l_cube_dims_rec.RelationId;
641
642 end insertCubeDimsRecord;
643
644 -------------------------------------------------------------------------------
645 -- insertCubeHierRecord - Private function that inserts a
646 -- zpb_md_records.cube_hier_entry into zpb_cube_hier and
647 -- returns the primary key of the newly created entry
648 -------------------------------------------------------------------------------
649 function insertCubeHierRecord(p_cube_hier_rec zpb_md_records.cube_hier_entry)
650 return number is
651
652 l_cube_hier_rec zpb_md_records.cube_hier_entry;
653
654 begin
655
656 l_cube_hier_rec := p_cube_hier_rec;
657
658 begin
659
660 select relation_id into l_cube_hier_rec.RelationId
661 from zpb_cube_hier
662 where cube_id = l_cube_hier_rec.CubeId and
663 hierarchy_id = l_cube_hier_rec.HierarchyId;
664
665 update zpb_cube_hier
666 set
667
668 COLUMN_ID = l_cube_hier_rec.ColumnId,
669 CREATED_BY = FND_GLOBAL.USER_ID,
670 CREATION_DATE = sysdate,
671 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
672 LAST_UPDATE_DATE = sysdate,
673 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
674 where relation_id = l_cube_hier_rec.RelationId;
675
676 exception
677 when NO_DATA_FOUND then
678 SELECT zpb_cube_hier_seq.NEXTVAL INTO l_cube_hier_rec.RelationId FROM DUAL;
679
680 insert into zpb_cube_hier
681 (
682 COLUMN_ID,
683 CUBE_ID,
684 HIERARCHY_ID,
685 RELATION_ID,
686 CREATED_BY,
687 CREATION_DATE,
688 LAST_UPDATED_BY,
689 LAST_UPDATE_DATE,
690 LAST_UPDATE_LOGIN
691 )
692 VALUES
693 (
694 l_cube_hier_rec.ColumnId,
695 l_cube_hier_rec.CubeId,
696 l_cube_hier_rec.HierarchyId,
697 l_cube_hier_rec.RelationId,
698 FND_GLOBAL.USER_ID,
699 sysdate,
700 FND_GLOBAL.USER_ID,
701 sysdate,
702 FND_GLOBAL.LOGIN_ID
703 );
704
705 end;
706
707 return l_cube_hier_rec.RelationId;
708
709 end insertCubeHierRecord;
710
711 -------------------------------------------------------------------------------
712 -- insertMeasureRecord - Private function that inserts a
713 -- zpb_md_records.measures_entry into zpb_measures and
714 -- returns the primary key of the newly created entry
715 -------------------------------------------------------------------------------
716 function insertMeasureRecord(p_measure_rec zpb_md_records.measures_entry)
717 return number is
718
719 l_measure_rec zpb_md_records.measures_entry;
720
721 begin
722
723 l_measure_rec := p_measure_rec;
724
725 -- dbms_output.put_line('inserting measure: ' || l_measure_rec.CWMName || ' into cube ' || l_measure_rec.CubeId );
726
727 begin
728 select measure_id into l_measure_rec.MeasureId
729 from zpb_measures
730 where cube_id = l_measure_rec.CubeId and
731 cwm_name = l_measure_rec.CWMName;
732
733 -- dbms_output.put_line('UPDATING');
734
735 update zpb_measures
736 set
737 AW_NAME = l_measure_rec.AwName,
738 COLUMN_ID = l_measure_rec.ColumnId,
739 CURRENCY_TYPE = l_measure_rec.CurrencyType,
740 CURR_INST_FLAG = l_measure_rec.CurrInstFlag,
741 EPB_ID = l_measure_rec.EpbId,
742 INSTANCE_ID = l_measure_rec.InstanceId,
743 TEMPLATE_ID = l_measure_rec.TemplateId,
744 APPROVEE_ID = l_measure_rec.ApproveeId,
745 TYPE = l_measure_rec.Type,
746 SELECTED_CUR = l_measure_rec.SelectedCur,
747 NAME = l_measure_rec.Name,
748 CURRENCY_REL = l_measure_rec.CurrencyRel,
749 CPR_MEASURE = l_measure_rec.CPRMeasure,
750 CREATED_BY = FND_GLOBAL.USER_ID,
751 CREATION_DATE = sysdate,
752 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
753 LAST_UPDATE_DATE = sysdate,
754 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
755 where measure_id = l_measure_rec.MeasureId;
756
757 exception
758 when NO_DATA_FOUND then
759 SELECT zpb_measures_seq.NEXTVAL INTO l_measure_rec.MeasureId FROM DUAL;
760
761 -- dbms_output.put_line('INSERTING');
762
763 insert into zpb_measures
764 (
765 AW_NAME,
766 COLUMN_ID,
767 CUBE_ID,
768 CURRENCY_TYPE,
769 CURR_INST_FLAG,
770 CWM_NAME,
771 EPB_ID,
772 INSTANCE_ID,
773 MEASURE_ID,
774 TEMPLATE_ID,
775 APPROVEE_ID,
776 TYPE,
777 SELECTED_CUR,
778 NAME,
779 CURRENCY_REL,
780 CPR_MEASURE,
781 CREATED_BY,
782 CREATION_DATE,
783 LAST_UPDATED_BY,
784 LAST_UPDATE_DATE,
785 LAST_UPDATE_LOGIN
786 )
787 VALUES
788 (
789 l_measure_rec.AwName,
790 l_measure_rec.ColumnId,
791 l_measure_rec.CubeId,
792 l_measure_rec.CurrencyType,
793 l_measure_rec.CurrInstFlag,
794 l_measure_rec.CwmName,
795 l_measure_rec.EpbId,
796 l_measure_rec.InstanceId,
797 l_measure_rec.MeasureId,
798 l_measure_rec.TemplateId,
799 l_measure_rec.ApproveeId,
800 l_measure_rec.Type,
801 l_measure_rec.SelectedCur,
802 l_measure_rec.Name,
803 l_measure_rec.CurrencyRel,
804 l_measure_rec.CPRMeasure,
805 FND_GLOBAL.USER_ID,
806 sysdate,
807 FND_GLOBAL.USER_ID,
808 sysdate,
809 FND_GLOBAL.LOGIN_ID
810 );
811 end;
812
813 return l_measure_rec.MeasureId;
814
815 end insertMeasureRecord;
816
817 -------------------------------------------------------------------------------
818 -- insertAttributeTLRecord - Private procedure that inserts a
819 -- zpb_md_records.attributes_tl_entry into zpb_attributes_tl
820 -------------------------------------------------------------------------------
821 procedure insertAttributesTLRecord(p_attributes_tl_rec zpb_md_records.attributes_tl_entry)
822 is
823 l_attributes_tl_rec zpb_md_records.attributes_tl_entry;
824 begin
825
826 l_attributes_tl_rec := p_attributes_tl_rec;
827
828 delete zpb_attributes_tl
829 where attribute_id = l_attributes_tl_rec.AttributeId and
830 language = l_attributes_tl_rec.Language;
831
832 insert into zpb_attributes_tl
833 (
834 ATTRIBUTE_ID,
835 LANGUAGE,
836 LONG_NAME,
837 NAME,
838 PLURAL_LONG_NAME,
839 PLURAL_NAME,
840 CREATED_BY,
841 CREATION_DATE,
842 LAST_UPDATED_BY,
843 LAST_UPDATE_DATE,
844 LAST_UPDATE_LOGIN
845 )
846 VALUES
847 (
848 l_attributes_tl_rec.AttributeId,
849 l_attributes_tl_rec.Language,
850 l_attributes_tl_rec.LongName,
851 l_attributes_tl_rec.Name,
852 l_attributes_tl_rec.PluralLongName,
853 l_attributes_tl_rec.PluralName,
854 FND_GLOBAL.USER_ID,
855 sysdate,
856 FND_GLOBAL.USER_ID,
857 sysdate,
858 FND_GLOBAL.LOGIN_ID
859 );
860
861 end insertAttributesTLRecord;
862
863
864 -------------------------------------------------------------------------------
865 -- insertDimensionsTLRecord - Private procedure that inserts a
866 -- zpb_md_records.dimensions_tl_entry into zpb_dimensions_tl
867 -------------------------------------------------------------------------------
868 procedure insertDimensionsTLRecord(p_dimensions_tl_rec zpb_md_records.dimensions_tl_entry)
869 is
870 l_dimensions_tl_rec zpb_md_records.dimensions_tl_entry;
871 begin
872
873 l_dimensions_tl_rec := p_dimensions_tl_rec;
874
875 delete zpb_dimensions_tl
876 where dimension_id = l_dimensions_tl_rec.DimensionId and
877 language = l_dimensions_tl_rec.Language;
878
879 insert into zpb_dimensions_tl
880 (
881 DIMENSION_ID,
882 LANGUAGE,
883 LONG_NAME,
884 NAME,
885 PLURAL_LONG_NAME,
886 PLURAL_NAME,
887 CREATED_BY,
888 CREATION_DATE,
889 LAST_UPDATED_BY,
890 LAST_UPDATE_DATE,
891 LAST_UPDATE_LOGIN
892 )
893 VALUES
894 (
895 l_dimensions_tl_rec.DimensionId,
896 l_dimensions_tl_rec.Language,
897 l_dimensions_tl_rec.LongName,
898 l_dimensions_tl_rec.Name,
899 l_dimensions_tl_rec.PluralLongName,
900 l_dimensions_tl_rec.PluralName,
901 FND_GLOBAL.USER_ID,
902 sysdate,
903 FND_GLOBAL.USER_ID,
904 sysdate,
905 FND_GLOBAL.LOGIN_ID
906 );
907
908 end insertDimensionsTLRecord;
909
910 -------------------------------------------------------------------------------
911 -- insertHierarchyRecord - Private function that inserts a
912 -- zpb_md_records.hierarchies_entry into zpb_hierarchies and
913 -- returns the primary key of the newly created entry
914 -------------------------------------------------------------------------------
915 function insertHierarchyRecord(p_hierarchies_rec in zpb_md_records.hierarchies_entry)
916 return number is
917 l_hierarchies_rec zpb_md_records.hierarchies_entry;
918
919 begin
920
921 l_hierarchies_rec := p_hierarchies_rec;
922
923 begin
924
925 select hierarchy_id into l_hierarchies_rec.HierarchyId
926 from zpb_hierarchies
927 where dimension_id = l_hierarchies_rec.DimensionId and
928 pers_cwm_name = l_hierarchies_rec.PersCWMName;
929
930 update zpb_hierarchies
931 set
932 EPB_ID = l_hierarchies_rec.EpbId,
933 HIER_TYPE = l_hierarchies_rec.HierType,
934 PERS_TABLE_ID = l_hierarchies_rec.PersTableId,
935 SHAR_CWM_NAME = l_hierarchies_rec.SharCWMName,
936 SHAR_TABLE_ID = l_hierarchies_rec.SharTableId,
937 CREATED_BY = FND_GLOBAL.USER_ID,
938 CREATION_DATE = sysdate,
939 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
940 LAST_UPDATE_DATE = sysdate,
941 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
942 where hierarchy_id = l_hierarchies_rec.HierarchyId;
943
944 exception
945 when NO_DATA_FOUND then
946 SELECT zpb_hierarchies_seq.NEXTVAL INTO l_hierarchies_rec.HierarchyId FROM DUAL;
947
948 insert into zpb_hierarchies
949 (
950 DIMENSION_ID,
951 EPB_ID,
952 HIERARCHY_ID,
953 HIER_TYPE,
954 PERS_CWM_NAME,
955 PERS_TABLE_ID,
956 SHAR_CWM_NAME,
957 SHAR_TABLE_ID,
958 CREATED_BY,
959 CREATION_DATE,
960 LAST_UPDATED_BY,
961 LAST_UPDATE_DATE,
962 LAST_UPDATE_LOGIN
963
964 )
965 VALUES
966 (
967 l_hierarchies_rec.DimensionId,
968 l_hierarchies_rec.EpbId,
969 l_hierarchies_rec.HierarchyId,
970 l_hierarchies_rec.HierType,
971 l_hierarchies_rec.PersCWMName,
972 l_hierarchies_rec.PersTableId,
973 l_hierarchies_rec.SharCWMName,
974 l_hierarchies_rec.SharTableId,
975 FND_GLOBAL.USER_ID,
976 sysdate,
977 FND_GLOBAL.USER_ID,
978 sysdate,
979 FND_GLOBAL.LOGIN_ID
980 );
981
982 end;
983
984 return l_hierarchies_rec.HierarchyId;
985 end insertHierarchyRecord;
986
987 -------------------------------------------------------------------------------
988 -- insertHierarchyTLRecord - Private procedure that inserts a
989 -- zpb_md_records.hierarchies_tl_entry into zpb_hierarchies_tl and
990 -------------------------------------------------------------------------------
991 procedure insertHierarchyTLRecord(p_hierarchies_tl_rec in zpb_md_records.hierarchies_tl_entry)
992 is
993 l_hierarchies_tl_rec zpb_md_records.hierarchies_tl_entry;
994 l_record_count number;
995
996 begin
997
998 l_hierarchies_tl_rec := p_hierarchies_tl_rec;
999
1000 select count(*) into l_record_count
1001 from zpb_hierarchies_tl
1002 where hierarchy_id = l_hierarchies_tl_rec.HierarchyId and
1003 language = l_hierarchies_tl_rec.Language;
1004
1005 --Make sure do not try to insert hierarchy entry for same language twice
1006 if l_record_count = 0 then
1007
1008 insert into zpb_hierarchies_tl
1009 (
1010 HIERARCHY_ID,
1011 LANGUAGE,
1012 LONG_NAME,
1013 NAME,
1014 PLURAL_LONG_NAME,
1015 PLURAL_NAME,
1016 CREATED_BY,
1017 CREATION_DATE,
1018 LAST_UPDATED_BY,
1019 LAST_UPDATE_DATE,
1020 LAST_UPDATE_LOGIN
1021
1022 )
1023 VALUES
1024 (
1025 l_hierarchies_tl_rec.HierarchyId,
1026 l_hierarchies_tl_rec.Language,
1027 l_hierarchies_tl_rec.LongName,
1028 l_hierarchies_tl_rec.Name,
1029 l_hierarchies_tl_rec.PluralLongName,
1030 l_hierarchies_tl_rec.PluralName,
1031 FND_GLOBAL.USER_ID,
1032 sysdate,
1033 FND_GLOBAL.USER_ID,
1034 sysdate,
1035 FND_GLOBAL.LOGIN_ID
1036 );
1037
1038 else
1039
1040 update zpb_hierarchies_tl
1041 set
1042
1043 LONG_NAME = l_hierarchies_tl_rec.LongName,
1044 NAME = l_hierarchies_tl_rec.Name,
1045 PLURAL_LONG_NAME = l_hierarchies_tl_rec.PluralLongName,
1046 PLURAL_NAME = l_hierarchies_tl_rec.PluralName,
1047 CREATED_BY = FND_GLOBAL.USER_ID,
1048 CREATION_DATE = sysdate,
1049 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1050 LAST_UPDATE_DATE = sysdate,
1051 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1052 where hierarchy_id = l_hierarchies_tl_rec.HierarchyId and
1053 language = l_hierarchies_tl_rec.Language;
1054
1055 end if;
1056
1057 end insertHierarchyTLRecord;
1058
1059 -------------------------------------------------------------------------------
1060 -- insertLevelTLRecord - Private procedure that inserts a
1061 -- zpb_md_records.levels_tl_entry into zpb_levels_tl and
1062 -------------------------------------------------------------------------------
1063 procedure insertLevelTLRecord(p_levels_tl_rec in zpb_md_records.levels_tl_entry)
1064 is
1065 l_levels_tl_rec zpb_md_records.levels_tl_entry;
1066 l_record_count number;
1067
1068 begin
1069
1070 l_levels_tl_rec := p_levels_tl_rec;
1071
1072 select count(*) into l_record_count
1073 from zpb_levels_tl
1074 where level_id = l_levels_tl_rec.LevelId and
1075 language = l_levels_tl_rec.Language;
1076
1077 if l_record_count = 1 then
1078
1079 update zpb_levels_tl
1080 set
1081 LONG_NAME = l_levels_tl_rec.LongName,
1082 NAME = l_levels_tl_rec.Name,
1083 PLURAL_LONG_NAME = l_levels_tl_rec.PluralLongName,
1084 PLURAL_NAME = l_levels_tl_rec.PluralName,
1085 CREATED_BY = FND_GLOBAL.USER_ID,
1086 CREATION_DATE = sysdate,
1087 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1088 LAST_UPDATE_DATE = sysdate,
1089 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1090 where level_id = l_levels_tl_rec.LevelId and
1091 language = l_levels_tl_rec.Language;
1092
1093 else
1094
1095 insert into zpb_levels_tl
1096 (
1097 LEVEL_ID,
1098 LANGUAGE,
1099 LONG_NAME,
1100 NAME,
1101 PLURAL_LONG_NAME,
1102 PLURAL_NAME,
1103 CREATED_BY,
1104 CREATION_DATE,
1105 LAST_UPDATED_BY,
1106 LAST_UPDATE_DATE,
1107 LAST_UPDATE_LOGIN
1108 )
1109 VALUES
1110 (
1111 l_levels_tl_rec.LevelId,
1112 l_levels_tl_rec.Language,
1113 l_levels_tl_rec.LongName,
1114 l_levels_tl_rec.Name,
1115 l_levels_tl_rec.PluralLongName,
1116 l_levels_tl_rec.PluralName,
1117 FND_GLOBAL.USER_ID,
1118 sysdate,
1119 FND_GLOBAL.USER_ID,
1120 sysdate,
1121 FND_GLOBAL.LOGIN_ID
1122 );
1123 end if;
1124
1125 end insertLevelTLRecord;
1126
1127
1128 -------------------------------------------------------------------------------
1129 -- deleteCubeRecord - Private procedure that deletes all md records for a
1130 -- particular cube.
1131 -------------------------------------------------------------------------------
1132 procedure deleteCubeRecord(p_cube_id in number) is
1133
1134 begin
1135
1136 -- zpb_log.write_error('zpb_metadata_pkg.deleteCubeRecord',
1137 -- 'Deleteing Cube '||p_cube_id);
1138
1139 -- delete cube measures and measure scoping entries
1140
1141 delete zpb_measures
1142 where cube_id = p_cube_id;
1143
1144 -- delete table and column entries for cube
1145 delete zpb_columns
1146 where table_id=(select table_id
1147 from zpb_cubes
1148 where cube_id = p_cube_id);
1149
1150 delete zpb_tables
1151 where table_id = (select table_id
1152 from zpb_cubes
1153 where cube_id = p_cube_id);
1154
1155 -- delete cube mapping entries (to dims and hierarchies)
1156 delete zpb_cube_dims
1157 where cube_id = p_cube_id;
1158
1159 delete zpb_cube_hier
1160 where cube_id = p_cube_id;
1161
1162 -- finally delete cube entry
1163 delete zpb_cubes
1164 where cube_id = p_cube_id;
1165
1166 end deleteCubeRecord;
1167
1168 -------------------------------------------------------------------------------
1169 -- insertCubeRecord - Private function that inserts a
1170 -- zpb_md_records.cubes_entry into zpb_cubes and
1171 -- returns the primary key of the newly created entry
1172 --
1173 -- if p_primary_key_provided is true, then the primary key for the
1174 -- cube to be inserted is provided and we do not select it from sequence
1175 -------------------------------------------------------------------------------
1176 function insertCubeRecord(p_cube_rec in zpb_md_records.CUBES_ENTRY,
1177 p_primary_key_provided in boolean default false)
1178 return number is
1179 l_cube_rec zpb_md_records.cubes_entry;
1180 bus_area_id_num number;
1181 insert_flag boolean;
1182
1183 begin
1184
1185 l_cube_rec := p_cube_rec;
1186 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
1187 insert_flag := p_primary_key_provided;
1188
1189 if insert_flag = false then
1190
1191 begin
1192
1193 select cube_id into l_cube_rec.CubeId
1194 from zpb_cubes
1195 where name = l_cube_rec.Name and
1196 bus_area_id = bus_area_id_num;
1197
1198 update zpb_cubes
1199 set
1200 EPB_ID = l_cube_rec.EpbId,
1201 TABLE_ID = l_cube_rec.TableId,
1202 TYPE = l_cube_rec.Type,
1203 CREATED_BY = FND_GLOBAL.USER_ID,
1204 CREATION_DATE = sysdate,
1205 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1206 LAST_UPDATE_DATE = sysdate,
1207 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1208 where cube_id = l_cube_rec.CubeId;
1209
1210 exception
1211 when NO_DATA_FOUND then
1212 SELECT zpb_cubes_seq.NEXTVAL INTO l_cube_rec.CubeId FROM DUAL;
1213 insert_flag:=true;
1214 end;
1215
1216 end if;
1217
1218 if insert_flag = true then
1219
1220 insert into zpb_cubes
1221 (
1222 BUS_AREA_ID,
1223 CUBE_ID,
1224 EPB_ID,
1225 NAME,
1226 TABLE_ID,
1227 TYPE,
1228 CREATED_BY,
1229 CREATION_DATE,
1230 LAST_UPDATED_BY,
1231 LAST_UPDATE_DATE,
1232 LAST_UPDATE_LOGIN
1233 )
1234 VALUES
1235 (
1236 -- l_cube_rec.BusAreaId,
1237 bus_area_id_num,
1238 l_cube_rec.CubeId,
1239 l_cube_rec.EpbId,
1240 l_cube_rec.Name,
1241 l_cube_rec.TableId,
1242 l_cube_rec.Type,
1243 FND_GLOBAL.USER_ID,
1244 sysdate,
1245 FND_GLOBAL.USER_ID,
1246 sysdate,
1247 FND_GLOBAL.LOGIN_ID
1248 );
1249
1250 end if;
1251
1252 return l_cube_rec.CubeId;
1253 end insertCubeRecord;
1254
1255
1256
1257 -------------------------------------------------------------------------------
1258 -- insertHierScopeRecord - Private function that inserts a
1259 -- zpb_md_records.hier_scope_entry into zpb_hier_scope and
1260 -- returns the primary key of the newly created entry
1261 -------------------------------------------------------------------------------
1262 function insertHierScopeRecord(p_hier_scope_rec zpb_md_records.hier_scope_entry)
1263 return number is
1264
1265 l_hier_scope_rec zpb_md_records.hier_scope_entry;
1266
1267 begin
1268
1269 l_hier_scope_rec := p_hier_scope_rec;
1270
1271 begin
1272
1273 select scope_id into l_hier_scope_rec.ScopeId
1274 from zpb_hier_scope
1275 where hierarchy_id = l_hier_scope_rec.HierarchyId and
1276 user_id = l_hier_scope_rec.UserId and
1277 resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID);
1278
1279 update zpb_hier_scope
1280 set
1281
1282 PERS_TABLE_ID = l_hier_scope_rec.PersTableId,
1283 CREATED_BY = FND_GLOBAL.USER_ID,
1284 CREATION_DATE = sysdate,
1285 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1286 LAST_UPDATE_DATE = sysdate,
1287 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1288 where scope_id = l_hier_scope_rec.ScopeId;
1289
1290 exception
1291 when NO_DATA_FOUND then
1292 SELECT zpb_hier_scope_seq.NEXTVAL INTO l_hier_scope_rec.ScopeId FROM DUAL;
1293
1294 insert into zpb_hier_scope
1295 (
1296 END_DATE,
1297 HIERARCHY_ID,
1298 SCOPE_ID,
1299 START_DATE,
1300 USER_ID,
1301 RESP_ID,
1302 PERS_TABLE_ID,
1303 CREATED_BY,
1304 CREATION_DATE,
1305 LAST_UPDATED_BY,
1306 LAST_UPDATE_DATE,
1307 LAST_UPDATE_LOGIN
1308 )
1309 VALUES
1310 (
1311 l_hier_scope_rec.EndDate,
1312 l_hier_scope_rec.HierarchyId,
1313 l_hier_scope_rec.ScopeId,
1314 l_hier_scope_rec.StartDate,
1315 l_hier_scope_rec.UserId,
1316 nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID),
1317 l_hier_scope_rec.PersTableId,
1318 FND_GLOBAL.USER_ID,
1319 sysdate,
1320 FND_GLOBAL.USER_ID,
1321 sysdate,
1322 FND_GLOBAL.LOGIN_ID
1323 );
1324
1325 end;
1326
1327 return l_hier_scope_rec.ScopeId;
1328
1329 end insertHierScopeRecord;
1330
1331 -------------------------------------------------------------------------------
1332 -- insertHierlevelscopeRecord - Private function that inserts a
1333 -- zpb_md_records.hier_level_scope_entry into zpb_hier_level_scope and
1334 -- returns the primary key of the newly created entry
1335 -------------------------------------------------------------------------------
1336 function insertHierlevelscopeRecord(p_hier_level_scope_rec zpb_md_records.hier_level_scope_entry)
1337 return number is
1338
1339 l_hier_level_scope_rec zpb_md_records.hier_level_scope_entry;
1340
1341 begin
1342
1343 l_hier_level_scope_rec := p_hier_level_scope_rec;
1344
1345 begin
1346
1347 select scope_id into l_hier_level_scope_rec.ScopeId
1348 from zpb_hier_level_scope
1349 where hier_id = l_hier_level_scope_rec.HierId and
1350 level_id = l_hier_level_scope_rec.LevelId and
1351 user_id = l_hier_level_scope_rec.UserId and
1352 resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID);
1353
1354 update zpb_hier_level_scope
1355 set
1356
1357 PERS_COL_ID = l_hier_level_scope_rec.PersColId,
1358 CREATED_BY = FND_GLOBAL.USER_ID,
1359 CREATION_DATE = sysdate,
1360 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1361 LAST_UPDATE_DATE = sysdate,
1362 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1363 where scope_id = l_hier_level_scope_rec.ScopeId;
1364
1365 exception
1366 when NO_DATA_FOUND then
1367 SELECT zpb_hier_level_scope_seq.NEXTVAL INTO l_hier_level_scope_rec.ScopeId FROM DUAL;
1368
1369 insert into zpb_hier_level_scope
1370 (
1371 LEVEL_ID,
1372 HIER_ID,
1373 SCOPE_ID,
1374 USER_ID,
1375 RESP_ID,
1376 PERS_COL_ID,
1377 CREATED_BY,
1378 CREATION_DATE,
1379 LAST_UPDATED_BY,
1380 LAST_UPDATE_DATE,
1381 LAST_UPDATE_LOGIN
1382 )
1383 VALUES
1384 (
1385 l_hier_level_scope_rec.LevelId,
1386 l_hier_level_scope_rec.HierId,
1387 l_hier_level_scope_rec.ScopeId,
1388 l_hier_level_scope_rec.UserId,
1389 nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID),
1390 l_hier_level_scope_rec.PersColId,
1391 FND_GLOBAL.USER_ID,
1392 sysdate,
1393 FND_GLOBAL.USER_ID,
1394 sysdate,
1395 FND_GLOBAL.LOGIN_ID
1396 );
1397
1398 end;
1399
1400 return l_hier_level_scope_rec.ScopeId;
1401
1402 end insertHierlevelscopeRecord;
1403
1404
1405 -------------------------------------------------------------------------------
1406 -- insertAttributescopeRecord - Private function that inserts a
1407 -- zpb_md_records.attribute_scope_entry into zpb_attribute_scope and
1408 -- returns the primary key of the newly created entry
1409 -------------------------------------------------------------------------------
1410 function insertAttributescopeRecord(p_attribute_scope_rec zpb_md_records.attribute_scope_entry)
1411 return number is
1412
1413 l_attribute_scope_rec zpb_md_records.attribute_scope_entry;
1414
1415 begin
1416
1417 l_attribute_scope_rec := p_attribute_scope_rec;
1418
1419 begin
1420
1421 select scope_id into l_attribute_scope_rec.ScopeId
1422 from zpb_attribute_scope
1423 where attribute_id = l_attribute_scope_rec.AttributeId and
1424 user_id = l_attribute_scope_rec.UserId and
1425 resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID);
1426
1427 update zpb_attribute_scope
1428 set
1429
1430 CREATED_BY = FND_GLOBAL.USER_ID,
1431 CREATION_DATE = sysdate,
1432 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1433 LAST_UPDATE_DATE = sysdate,
1434 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1435 where scope_id = l_attribute_scope_rec.ScopeId;
1436
1437 exception
1438 when NO_DATA_FOUND then
1439 SELECT zpb_attribute_scope_seq.NEXTVAL INTO l_attribute_scope_rec.ScopeId FROM DUAL;
1440
1441 insert into zpb_attribute_scope
1442 (
1443 SCOPE_ID,
1444 ATTRIBUTE_ID,
1445 USER_ID,
1446 RESP_ID,
1447 END_DATE,
1448 START_DATE,
1449 CREATED_BY,
1450 CREATION_DATE,
1451 LAST_UPDATED_BY,
1452 LAST_UPDATE_DATE,
1453 LAST_UPDATE_LOGIN
1454 )
1455 VALUES
1456 (
1457 l_attribute_scope_rec.ScopeId,
1458 l_attribute_scope_rec.AttributeId,
1459 l_attribute_scope_rec.UserId,
1460 nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID),
1461 l_attribute_scope_rec.EndDate,
1462 l_attribute_scope_rec.StartDate,
1463 FND_GLOBAL.USER_ID,
1464 sysdate,
1465 FND_GLOBAL.USER_ID,
1466 sysdate,
1467 FND_GLOBAL.LOGIN_ID
1468 );
1469
1470 end;
1471
1472 return l_attribute_scope_rec.ScopeId;
1473
1474 end insertAttributescopeRecord;
1475
1476 -------------------------------------------------------------------------------
1477 -- deleteDimensionRecord - Private procedure that deletes all md records for a
1478 -- particular dimension.
1479 -------------------------------------------------------------------------------
1480 procedure deleteDimensionRecord(p_dimension_id in number) is
1481
1482 begin
1483
1484 -- delete dimensions_tl
1485 delete zpb_dimensions_tl
1486 where dimension_id = p_dimension_id;
1487
1488 -- delete attribute_scope
1489 delete zpb_attribute_scope
1490 where attribute_id in (select attribute_id
1491 from zpb_attributes
1492 where dimension_id = p_dimension_id);
1493
1494 -- delete attr_table_col
1495 delete zpb_attr_table_col
1496 where attribute_id in (select attribute_id
1497 from zpb_attributes
1498 where dimension_id = p_dimension_id);
1499
1500 -- delete attributes_tl
1501 delete zpb_attributes_tl
1502 where attribute_id in (select attribute_id
1503 from zpb_attributes
1504 where dimension_id = p_dimension_id);
1505
1506 -- Finally delete attributes
1507 delete zpb_attributes
1508 where dimension_id = p_dimension_id;
1509
1510 -- delete hierarchies_tl
1511 delete zpb_hierarchies_tl
1512 where hierarchy_id in (select hierarchy_id
1513 from zpb_hierarchies
1514 where dimension_id = p_dimension_id);
1515
1516 -- delete hier_level
1517 delete zpb_hier_level
1518 where hier_id in (select hierarchy_id
1519 from zpb_hierarchies
1520 where dimension_id = p_dimension_id);
1521
1522 -- delete hier_level_scope
1523 delete zpb_hier_level_scope
1524 where hier_id in (select hierarchy_id
1525 from zpb_hierarchies
1526 where dimension_id = p_dimension_id);
1527
1528 -- delete hier_scope
1529 delete zpb_hier_scope
1530 where hierarchy_id in (select hierarchy_id
1531 from zpb_hierarchies
1532 where dimension_id = p_dimension_id);
1533
1534 -- delete hierarchy zpb_tables and zpb_column entries
1535 delete zpb_tables
1536 where table_id in (select pers_table_id from zpb_hierarchies
1537 where dimension_id = p_dimension_id);
1538
1539 delete zpb_columns
1540 where table_id in (select pers_table_id from zpb_hierarchies
1541 where dimension_id = p_dimension_id);
1542
1543 delete zpb_tables
1544 where table_id in (select shar_table_id from zpb_hierarchies
1545 where dimension_id = p_dimension_id);
1546
1547 delete zpb_columns
1548 where table_id in (select shar_table_id from zpb_hierarchies
1549 where dimension_id = p_dimension_id);
1550
1551 -- Finally delete hierarchies entry
1552 delete zpb_hierarchies
1553 where dimension_id = p_dimension_id;
1554
1555 -- delete levels_tl
1556 delete zpb_levels_tl
1557 where level_id in (select level_id
1558 from zpb_levels
1559 where dimension_id = p_dimension_id);
1560
1561 -- Finally delete levels
1562 delete zpb_levels
1563 where dimension_id = p_dimension_id;
1564
1565
1566 -- Delete zpb_tables entries for dimension
1567 delete zpb_tables where table_id = (select pers_table_id from zpb_dimensions where dimension_id = p_dimension_id);
1568 delete zpb_columns where table_id = (select pers_table_id from zpb_dimensions where dimension_id = p_dimension_id);
1569
1570 delete zpb_tables where table_id = (select shar_table_id from zpb_dimensions where dimension_id = p_dimension_id);
1571 delete zpb_columns where table_id = (select shar_table_id from zpb_dimensions where dimension_id = p_dimension_id);
1572
1573 -- Finally delete dimension
1574 delete zpb_dimensions
1575 where dimension_id = p_dimension_id;
1576
1577 end deleteDimensionRecord;
1578
1579 -------------------------------------------------------------------------------
1580 -- deleteAttributeRecord - Private procedure that deletes all md records for a
1581 -- particular attribute.
1582 -------------------------------------------------------------------------------
1583 procedure deleteAttributeRecord(p_attribute_id in number) is
1584
1585 begin
1586
1587 -- delete attribute_scope
1588 delete zpb_attribute_scope
1589 where attribute_id = p_attribute_id;
1590
1591 -- delete attr_table_col
1592 delete zpb_attr_table_col
1593 where attribute_id = p_attribute_id;
1594
1595 -- delete attributes_tl
1596 delete zpb_attributes_tl
1597 where attribute_id = p_attribute_id;
1598
1599 -- detele zpb_attributes
1600 delete zpb_attributes
1601 where attribute_id = p_attribute_id;
1602
1603 end deleteAttributeRecord;
1604
1605 -------------------------------------------------------------------------------
1606 -- deleteLevelRecord - Private procedure that deletes all md records for a
1607 -- particular level.
1608 -------------------------------------------------------------------------------
1609 procedure deleteLevelRecord(p_level_id in number) is
1610
1611 begin
1612
1613 -- delete levels_tl
1614 delete zpb_levels_tl
1615 where level_id = p_level_id;
1616
1617 -- Finally delete levels
1618 delete zpb_levels
1619 where level_id = p_level_id;
1620
1621 end deleteLevelRecord;
1622
1623 -------------------------------------------------------------------------------
1624 -- deleteHierarchyRecord - Private procedure that deletes all md records for a
1625 -- particular hierarchy.
1626 -------------------------------------------------------------------------------
1627 procedure deleteHierarchyRecord(p_hierarchy_id in number) is
1628
1629 begin
1630
1631 -- delete hierarchies_tl
1632 delete zpb_hierarchies_tl
1633 where hierarchy_id = p_hierarchy_id;
1634
1635 -- delete hier_level
1636 delete zpb_hier_level
1637 where hier_id = p_hierarchy_id;
1638
1639 -- delete hier_level_scope
1640 delete zpb_hier_level_scope
1641 where hier_id = p_hierarchy_id;
1642
1643 -- delete hier_scope
1644 delete zpb_hier_scope
1645 where hierarchy_id = p_hierarchy_id;
1646
1647 -- delete zpb_tables for hierarchy
1648 delete zpb_tables where table_id = (select pers_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
1649 delete zpb_columns where table_id = (select pers_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
1650
1651 delete zpb_tables where table_id = (select shar_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
1652 delete zpb_columns where table_id = (select shar_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
1653
1654 -- Finally delete hierarchies entry
1655 delete zpb_hierarchies
1656 where hierarchy_id = p_hierarchy_id;
1657
1658 end deleteHierarchyRecord;
1659 -------------------------------------------------------------------------------
1660 -- cleancleanOldEntries - Private procedure that deletes all md records that
1661 -- have not been updated during a universe refresh and
1662 -- thus no longer exist
1663 -------------------------------------------------------------------------------
1664 procedure cleanOldEntries(p_start_time date) is
1665
1666 bus_area_id_num number;
1667
1668 CURSOR c_dimensions is
1669 select dimension_id
1670 from zpb_dimensions
1671 where bus_area_id = bus_area_id_num and
1672 last_update_date < p_start_time;
1673
1674 v_dim c_dimensions%ROWTYPE;
1675
1676 CURSOR c_attributes is
1677 select attribute_id
1678 from zpb_attributes
1679 where last_update_date < p_start_time and
1680 dimension_id in (select dimension_id
1681 from zpb_dimensions
1682 where bus_area_id = bus_area_id_num);
1683
1684 v_attribute c_attributes%ROWTYPE;
1685
1686 CURSOR c_hierarchies is
1687 select hierarchy_id
1688 from zpb_hierarchies
1689 where last_update_date < p_start_time and
1690 dimension_id in (select dimension_id
1691 from zpb_dimensions
1692 where bus_area_id = bus_area_id_num);
1693
1694 v_hierarchy c_hierarchies%ROWTYPE;
1695
1696 CURSOR c_levels is
1697 select level_id
1698 from zpb_levels
1699 where last_update_date < p_start_time and
1700 dimension_id in (select dimension_id
1701 from zpb_dimensions
1702 where bus_area_id = bus_area_id_num);
1703
1704 v_level c_levels%ROWTYPE;
1705
1706 begin
1707
1708 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
1709
1710 for v_dim in c_dimensions loop
1711 deleteDimensionRecord(v_dim.dimension_id);
1712 end loop;
1713
1714 for v_attribute in c_attributes loop
1715 deleteAttributeRecord(v_attribute.attribute_id);
1716 end loop;
1717
1718 for v_hierarchy in c_hierarchies loop
1719 deleteHierarchyRecord(v_hierarchy.hierarchy_id);
1720 end loop;
1721
1722 for v_level in c_levels loop
1723 deleteLevelRecord(v_level.level_id);
1724 end loop;
1725
1726 delete zpb_attr_table_col
1727 where last_update_date < p_start_time and
1728 table_id in (select table_id
1729 from zpb_tables
1730 where bus_area_id = bus_area_id_num);
1731
1732 delete zpb_hier_level
1733 where last_update_date < p_start_time
1734 and hier_id in (select hier.hierarchy_id
1735 from zpb_hierarchies hier,
1736 zpb_dimensions dim
1737 where hier.dimension_id = dim.dimension_id and
1738 dim.bus_area_id = bus_area_id_num);
1739
1740 end cleanOldEntries;
1741
1742 -------------------------------------------------------------------------------
1743 -- BUILD_DIMS - Exposes metadata for dimensions and objects associated with them
1744 -- hierarchies, attributes, levels
1745 --
1746 -- IN: p_aw - The AW to build
1747 -- p_sharedAW - The shared AW (may be the same as p_aw)
1748 -- p_type - The AW type (PERSONAL or SHARED)
1749 -- p_dims - Space separated list of dim ID's
1750 -------------------------------------------------------------------------------
1751 procedure BUILD_DIMS(p_aw in varchar2,
1752 p_sharedAw in varchar2,
1753 p_type in varchar2,
1754 p_dims in varchar2)
1755 is
1756 l_hiers varchar2(500);
1757 l_levels varchar2(500);
1758 l_attrs varchar2(500);
1759 l_attrId varchar2(30);
1760 l_dim varchar2(32);
1761 l_hier varchar2(32);
1762 l_level varchar2(32);
1763 l_lvlhier varchar2(32);
1764 l_persLvl boolean;
1765 l_attr varchar2(32);
1766 l_aw varchar2(32);
1767 l_viewAw varchar2(32);
1768 i number;
1769 j number;
1770 hi number;
1771 hj number;
1772 li number;
1773 lj number;
1774 ai number;
1775 aj number;
1776 l_length number;
1777 done boolean;
1778 nl varchar2(1) := fnd_global.local_chr(10);
1779
1780 l_global_ecm zpb_ecm.global_ecm;
1781 l_dim_data zpb_ecm.dimension_data;
1782 l_range_dim_data zpb_ecm.dimension_data;
1783 l_dim_ecm zpb_ecm.dimension_ecm;
1784 l_dim_time_ecm zpb_ecm.dimension_time_ecm;
1785 l_dim_line_ecm zpb_ecm.dimension_line_ecm;
1786 l_global_attr_ecm zpb_ecm.global_attr_ecm;
1787 l_attr_ecm zpb_ecm.attr_ecm;
1788 l_attr_nameFrag varchar2(500);
1789
1790 m_dimension_en zpb_md_records.dimensions_entry;
1791 m_dimension_tl_en zpb_md_records.dimensions_tl_entry;
1792 m_pers_table_en zpb_md_records.tables_entry;
1793 m_shar_table_en zpb_md_records.tables_entry;
1794 m_pers_column_en zpb_md_records.columns_entry;
1795 m_shar_column_en zpb_md_records.columns_entry;
1796
1797 m_hier_en zpb_md_records.hierarchies_entry;
1798 m_hier_tl_en zpb_md_records.hierarchies_tl_entry;
1799 m_level_en zpb_md_records.levels_entry;
1800 m_level_tl_en zpb_md_records.levels_tl_entry;
1801 m_hier_level_en zpb_md_records.hier_level_entry;
1802 m_hr_shar_table_en zpb_md_records.tables_entry;
1803 m_hr_pers_table_en zpb_md_records.tables_entry;
1804
1805 -- static hierarchy table columns
1806 m_hr_pers_col_memCol zpb_md_records.columns_entry;
1807 m_hr_shar_col_memCol zpb_md_records.columns_entry;
1808 m_hr_pers_col_gidCol zpb_md_records.columns_entry;
1809 m_hr_shar_col_gidCol zpb_md_records.columns_entry;
1810 m_hr_shar_col_parentCol zpb_md_records.columns_entry;
1811 m_hr_pers_col_parentCol zpb_md_records.columns_entry;
1812 m_hr_shar_col_pgidCol zpb_md_records.columns_entry;
1813 m_hr_pers_col_pgidCol zpb_md_records.columns_entry;
1814 m_hr_pers_col_orderCol zpb_md_records.columns_entry;
1815 m_hr_shar_col_orderCol zpb_md_records.columns_entry;
1816
1817 m_level_order number;
1818
1819 m_table_id number;
1820 m_hierarchy_id number;
1821 m_column_id number;
1822 m_level_id number;
1823 m_hier_level_id number;
1824 m_attr_table_col_id number;
1825 m_dummy_num number;
1826
1827 -- attribute
1828 m_attr_en zpb_md_records.attributes_entry;
1829 m_attr_table_col_en zpb_md_records.attr_table_col_entry;
1830
1831 -- attribute range dimension
1832 m_attr_rangedim_en zpb_md_records.dimensions_entry;
1833 m_attr_rangedim_tl_en zpb_md_records.dimensions_tl_entry;
1834 m_attr_rangehier_en zpb_md_records.hierarchies_entry;
1835 m_attr_rangetbl_en zpb_md_records.tables_entry;
1836 m_attr_rangedimCount number;
1837 m_attr_rangecol_en zpb_md_records.columns_entry;
1838 m_attr_rangehl_en zpb_md_records.hier_level_entry;
1839 m_attr_rangelev_en zpb_md_records.levels_entry;
1840 m_attr_rangeattr_en zpb_md_records.attributes_entry;
1841 m_attr_rangeatc_en zpb_md_records.attr_table_col_entry;
1842
1843 -- shared attribute range dimension
1844 m_attr_range_sh_tbl_en zpb_md_records.tables_entry;
1845 m_attr_range_sh_col_en zpb_md_records.columns_entry;
1846 m_attr_sh_rangeatc_en zpb_md_records.attr_table_col_entry;
1847
1848 m_attr_tl_en zpb_md_records.attributes_tl_entry;
1849
1850 bus_area_id_num number;
1851
1852 -- primary keys found flags
1853 m_dimension_exists boolean;
1854 m_pers_table_exists boolean;
1855 m_shar_table_exists boolean;
1856
1857 -- language looping
1858 l_langs varchar2(500);
1859 l_lang FND_LANGUAGES.LANGUAGE_CODE%type;
1860 htld_i number;
1861 htld_j number;
1862
1863 -- personal-personal hierarchy table MD
1864 l_pp_hiert_start_time date;
1865
1866 begin
1867 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
1868 l_global_ecm := zpb_ecm.get_global_ecm (p_aw);
1869 l_global_attr_ecm := zpb_ecm.get_global_attr_ecm(p_aw);
1870
1871 l_langs :=zpb_aw.interp
1872 ('shw CM.GETDIMVALUES('''||l_aw||l_global_ecm.LangDim||''')');
1873
1874 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
1875
1876 zpb_aw.execute('push oknullstatus');
1877 zpb_aw.execute('oknullstatus = yes');
1878
1879 -- dbms_output.put_line('Building dims: ' || p_dims);
1880
1881 -- Loop pver all dimensions
1882 i := 1;
1883 loop
1884 j := instr (p_dims, ' ', i);
1885 if (j = 0) then
1886 l_dim := substr (p_dims, i);
1887 else
1888 l_dim := substr (p_dims, i, j-i);
1889 i := j+1;
1890 end if;
1891
1892 m_dimension_en.EPBId := l_dim;
1893 -- Get primary key for newly created dimension entry
1894 begin
1895
1896 select dimension_id into m_dimension_en.DimensionId
1897 from zpb_dimensions
1898 where bus_area_id = bus_area_id_num and
1899 epb_id = m_dimension_en.EPBId;
1900
1901 m_dimension_exists := false;
1902
1903 exception
1904 when NO_DATA_FOUND then
1905 SELECT zpb_dimensions_seq.NEXTVAL INTO m_dimension_en.DimensionId FROM DUAL;
1906 m_dimension_exists := true;
1907 end;
1908
1909
1910 l_dim_data := zpb_ecm.get_dimension_data (l_dim, p_aw);
1911 l_dim_ecm := zpb_ecm.get_dimension_ecm (l_dim, p_aw);
1912
1913 zpb_aw.execute('push '||l_aw||l_dim_data.ExpObj);
1914
1915 m_dimension_en.AWName := l_dim_data.ExpObj;
1916
1917 -- Dimension Default Hierarchy
1918 m_dimension_en.DefaultHier := l_dim_ecm.HierDefault;
1919
1920 -- get both the personal and shared CWM dimension names
1921 m_dimension_en.PersCWMName := zpb_metadata_names.get_dimension_cwm2_name('PRS', l_dim);
1922 m_dimension_en.SharCWMName := zpb_metadata_names.get_dimension_cwm2_name(p_sharedAw, l_dim);
1923
1924 m_dimension_en.DimType := l_dim_data.Type;
1925 m_dimension_en.AnnotationDim := l_dim_ecm.AnnDim ;
1926 m_dimension_en.DefaultMember := l_dim_ecm.DefaultMember;
1927 m_dimension_en.IsOwnerDim := l_dim_data.IsOwnerDim;
1928 m_dimension_en.IsDataDim := l_dim_data.IsDataDim;
1929
1930 -- get the dimension table names for personal and shared
1931 m_shar_table_en.TableName:= zpb_metadata_names.get_dimension_view
1932 (p_sharedAW, 'SHARED', l_dim);
1933 m_pers_table_en.TableName:=zpb_metadata_names.get_dimension_view
1934 (p_sharedAW, 'PERSONAL', l_dim);
1935
1936 m_shar_table_en.TableType := 'DIMENSION';
1937 m_pers_table_en.TableType := 'DIMENSION';
1938
1939 m_shar_table_en.AWName := p_sharedAw;
1940 m_pers_table_en.AWName := p_sharedAw;
1941
1942 -- insert dimension table records
1943 m_dimension_en.SharTableId := insertTableRecord(m_shar_table_en);
1944 m_dimension_en.PersTableId := insertTableRecord(m_pers_table_en);
1945
1946 -- set the table id for the Columns Below
1947 m_pers_column_en.TableId := m_dimension_en.PersTableId;
1948 m_shar_column_en.TableId := m_dimension_en.SharTableId;
1949
1950 --TABLE COLUMNS
1951 -- Create MEMBER_COLUMN's for the tables
1952 m_pers_column_en.columnType := 'MEMBER_COLUMN';
1953 m_shar_column_en.columnType := 'MEMBER_COLUMN';
1954
1955 m_pers_column_en.columnName := zpb_metadata_names.get_dimension_column(l_dim);
1956 m_shar_column_en.columnName := zpb_metadata_names.get_dimension_column(l_dim);
1957
1958 m_pers_column_en.AWName := l_dim_data.ExpObj;
1959 m_shar_column_en.AWName := l_dim_data.ExpObj;
1960
1961 -- insert column records, we dont care about their primary keys here
1962 m_column_id := insertColumnRecord(m_pers_column_en);
1963 m_column_id := insertColumnRecord(m_shar_column_en);
1964
1965 -- Creat LONG NAME column for the tables
1966 m_pers_column_en.columnType := 'LNAME_COLUMN';
1967 m_shar_column_en.columnType := 'LNAME_COLUMN';
1968
1969 m_pers_column_en.columnName := zpb_metadata_names.get_dim_long_name_column(l_dim);
1970 m_shar_column_en.columnName := zpb_metadata_names.get_dim_long_name_column(l_dim);
1971
1972 m_pers_column_en.AWName := l_dim_ecm.LdscVar;
1973 m_shar_column_en.AWName := l_dim_ecm.LdscVar;
1974
1975 -- insert column records, we dont care about their primary keys here
1976 m_column_id := insertColumnRecord(m_pers_column_en);
1977 m_column_id := insertColumnRecord(m_shar_column_en);
1978
1979 -- Create SHORT NAME columns for the tables
1980 m_pers_column_en.columnType := 'SNAME_COLUMN';
1981 m_shar_column_en.columnType := 'SNAME_COLUMN';
1982
1983 m_pers_column_en.columnName := zpb_metadata_names.get_dim_short_name_column(l_dim);
1984 m_shar_column_en.columnName := zpb_metadata_names.get_dim_short_name_column(l_dim);
1985
1986 m_pers_column_en.AWName := l_dim_ecm.MdscVar;
1987 m_shar_column_en.AWName := l_dim_ecm.MdscVar;
1988
1989 -- insert column records, we dont care about their primary keys here
1990 m_column_id := insertColumnRecord(m_pers_column_en);
1991 m_column_id := insertColumnRecord(m_shar_column_en);
1992
1993 -- Create CODE columns for the tables
1994 m_pers_column_en.columnType := 'CODE_COLUMN';
1995 m_shar_column_en.columnType := 'CODE_COLUMN';
1996
1997 m_pers_column_en.columnName := zpb_metadata_names.get_dim_code_column(l_dim);
1998 m_shar_column_en.columnName := zpb_metadata_names.get_dim_code_column(l_dim);
1999
2000 m_pers_column_en.AWName := l_dim_ecm.SdscVar;
2001 m_shar_column_en.AWName := l_dim_ecm.SdscVar;
2002
2003 -- insert column records, we dont care about their primary keys here
2004 m_column_id := insertColumnRecord(m_pers_column_en);
2005 m_column_id := insertColumnRecord(m_shar_column_en);
2006
2007 -- Create GID columns for the tables
2008 m_pers_column_en.columnType := 'GID_COLUMN';
2009 m_shar_column_en.columnType := 'GID_COLUMN';
2010
2011 m_pers_column_en.columnName := zpb_metadata_names.get_dim_gid_column(l_dim);
2012 m_shar_column_en.columnName := zpb_metadata_names.get_dim_gid_column(l_dim);
2013
2014 m_pers_column_en.AWName := l_dim_ecm.GID;
2015 m_shar_column_en.AWName := l_dim_ecm.GID;
2016
2017 -- insert column records, we dont care about their primary keys here
2018 m_column_id :=insertColumnRecord(m_pers_column_en);
2019 m_column_id :=insertColumnRecord(m_shar_column_en);
2020
2021 --Prepare columns common to all Hierarchy Table Records
2022 m_hr_pers_table_en.AWName:= p_sharedAw;
2023 m_hr_shar_table_en.AWName:= p_sharedAw;
2024
2025 -- MEMBER COLUMN
2026 m_hr_pers_col_memCol.columnType := 'MEMBER_COLUMN';
2027 m_hr_shar_col_memCol.columnType := 'MEMBER_COLUMN';
2028
2029 m_hr_pers_col_memCol.columnName := zpb_metadata_names.get_dimension_column(l_dim);
2030 m_hr_shar_col_memCol.columnName := zpb_metadata_names.get_dimension_column(l_dim);
2031
2032 m_hr_pers_col_memCol.AWName := l_dim_data.ExpObj;
2033 m_hr_shar_col_memCol.AWName := l_dim_data.ExpObj;
2034
2035 -- GID COLUMNS
2036 m_hr_pers_col_gidCol.columnType := 'GID_COLUMN';
2037 m_hr_shar_col_gidCol.columnType := 'GID_COLUMN';
2038
2039 m_hr_pers_col_gidCol.columnName := zpb_metadata_names.get_dim_gid_column(l_dim);
2040 m_hr_shar_col_gidCol.columnName := zpb_metadata_names.get_dim_gid_column(l_dim);
2041
2042 m_hr_pers_col_gidCol.AWName := l_dim_ecm.GID;
2043 m_hr_shar_col_gidCol.AWName := l_dim_ecm.GID;
2044
2045 -- PGID COLUMNS
2046 m_hr_pers_col_pgidCol.columnType := 'PGID_COLUMN';
2047 m_hr_shar_col_pgidCol.columnType := 'PGID_COLUMN';
2048
2049 m_hr_pers_col_pgidCol.columnName := zpb_metadata_names.get_dim_pgid_column(l_dim);
2050 m_hr_shar_col_pgidCol.columnName := zpb_metadata_names.get_dim_pgid_column(l_dim);
2051
2052 m_hr_pers_col_pgidCol.AWName := l_dim_ecm.GID;
2053 m_hr_shar_col_pgidCol.AWName := l_dim_ecm.GID;
2054
2055 -- PARENT COLUMNS
2056 m_hr_pers_col_parentCol.columnType := 'PARENT_COLUMN';
2057 m_hr_shar_col_parentCol.columnType := 'PARENT_COLUMN';
2058
2059 m_hr_pers_col_parentCol.columnName := zpb_metadata_names.get_dim_parent_column(l_dim);
2060 m_hr_shar_col_parentCol.columnName := zpb_metadata_names.get_dim_parent_column(l_dim);
2061
2062 m_hr_pers_col_parentCol.AWName := l_dim_ecm.ParentRel;
2063 m_hr_shar_col_parentCol.AWName := l_dim_ecm.ParentRel;
2064
2065 -- ORDER COLUMNS
2066 m_hr_pers_col_orderCol.ColumnType := 'ORDER_COLUMN';
2067 m_hr_shar_col_orderCol.ColumnType := 'ORDER_COLUMN';
2068
2069 m_hr_pers_col_orderCol.ColumnName := zpb_metadata_names.get_dim_order_column(l_dim);
2070 m_hr_shar_col_orderCol.ColumnName := zpb_metadata_names.get_dim_order_column(l_dim);
2071
2072 m_hr_pers_col_orderCol.AWName := l_dim_ecm.FullOrderVar;
2073 m_hr_shar_col_orderCol.AWName := l_dim_ecm.FullOrderVar;
2074
2075
2076 --
2077 -- HierDim can be valid with nothing in status, if scoping rules
2078 -- out all possible hierarchies for the user:
2079 --
2080 done := false;
2081 if (zpb_aw.interp('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||
2082 ''')') <> '0') then
2083 hi := 1;
2084 l_hiers :=zpb_aw.interp
2085 ('shw CM.GETDIMVALUES('''||l_aw||l_dim_ecm.HierDim||''')');
2086
2087 zpb_aw.execute ('push '||l_aw||l_dim_ecm.LevelDim);
2088 zpb_aw.execute ('push '||l_aw||l_dim_ecm.HierDim);
2089 loop -- LOOP OVER ALL HIERARCHIES
2090 hj := instr (l_hiers, ' ', hi);
2091 if (hj = 0) then
2092 l_hier := substr (l_hiers, hi);
2093 else
2094 l_hier := substr (l_hiers, hi, hj-hi);
2095 hi := hj+1;
2096 end if;
2097
2098 -- dbms_output.put_line('Looping for hierarchy ' || l_hier);
2099
2100 zpb_aw.execute('lmt '||l_aw||l_dim_ecm.HierDim||' to '''||
2101 l_hier||'''');
2102
2103 zpb_aw.execute('lmt '||l_aw||l_dim_data.ExpObj||' to '||l_aw||
2104 l_dim_ecm.HOrderVS);
2105
2106 if (zpb_aw.interp('shw convert(statlen('||l_aw||l_dim_data.ExpObj||
2107 ') text 0 no no)') <> 0) then
2108
2109 -- if this hierarchy has members, limit the level dimension to its levels
2110 -- and and sort it according to leveldepthvar
2111
2112 zpb_aw.execute('lmt '||l_aw||l_dim_ecm.HierDim||' to '''||
2113 l_hier||'''');
2114
2115 zpb_aw.execute('lmt '||l_aw||l_dim_ecm.LevelDim||
2116 ' to &joinchars ('''||l_aw||''' obj(property '''
2117 ||'HIERLEVELVS'' '''||l_dim_ecm.HierDim||'''))');
2118
2119 zpb_aw.execute('sort '||l_aw||l_dim_ecm.levelDim||
2120 ' a &joinchars ('''||l_aw||''' obj(property '''
2121 ||'LEVELDEPTHVAR'' '''||l_dim_ecm.HierDim||'''))');
2122
2123
2124 -- if we are in personal mode and user has a personal level in this hierarchy, build
2125 -- metadata for the personal-personal hierarchy table (this table has the user's id in its name)
2126 if (p_type='PERSONAL' and zpb_aw.interp('shw convert(statlen(limit(' || l_aw||l_dim_ecm.levelDim||
2127 ' to &joinchars ('''||l_aw||''' obj(property '''
2128 ||'PERSONALVAR'' '''||l_dim_ecm.LevelDim||''')))) text 0 no no)') > 0) then
2129
2130 l_persLvl := true;
2131 m_hr_pers_table_en.TableName := zpb_metadata_names.get_dimension_view
2132 (p_aw, 'PERSONAL', l_dim, l_hier);
2133 m_hr_pers_table_en.AWName:= p_aw;
2134
2135 select sysdate into l_pp_hiert_start_time from dual;
2136 else
2137 l_persLvl := false;
2138 end if;
2139
2140
2141 -- Hierarchy Entry
2142 m_hier_en.DimensionId := m_dimension_en.DimensionId;
2143 m_hier_en.EPBId := l_hier;
2144 m_hier_en.HierType := zpb_aw.interp ('shw '||l_dim_ecm.HierTypeRel);
2145
2146 m_hier_en.PersCWMName := zpb_metadata_names.get_hierarchy_cwm2_name('PRS', l_dim, l_hier);
2147 m_hier_en.SharCWMName := zpb_metadata_names.get_hierarchy_cwm2_name(p_sharedAw, l_dim, l_hier);
2148
2149 -- Hierarchy Tables
2150 -- Have to get the Primary Key before we insert the entry because sub-entries
2151 -- need to reference entry
2152 begin
2153 select table_id into m_hr_shar_table_en.tableId
2154 from zpb_tables
2155 where table_id = (select shar_table_id
2156 from zpb_hierarchies
2157 where dimension_id = m_dimension_en.DimensionId and
2158 pers_cwm_name = m_hier_en.PersCWMName);
2159 m_shar_table_exists := false;
2160
2161 exception
2162 when NO_DATA_FOUND then
2163 SELECT zpb_tables_seq.NEXTVAL INTO m_hr_shar_table_en.tableId FROM DUAL;
2164
2165 m_shar_table_exists := true;
2166 end;
2167
2168 begin
2169
2170 -- if the hierarchy has a personal level, the table name will be different
2171 if l_persLvl = true then
2172
2173 select table_id into m_hr_pers_table_en.tableId
2174 from zpb_tables
2175 where table_name = m_hr_pers_table_en.TableName and
2176 bus_area_id = bus_area_id_num;
2177
2178 else
2179 select table_id into m_hr_pers_table_en.tableId
2180 from zpb_tables
2181 where table_id = (select pers_table_id
2182 from zpb_hierarchies
2183 where dimension_id = m_dimension_en.DimensionId and
2184 pers_cwm_name = m_hier_en.PersCWMName);
2185 end if;
2186
2187 m_pers_table_exists :=false;
2188
2189 exception
2190 when NO_DATA_FOUND then
2191 SELECT zpb_tables_seq.NEXTVAL INTO m_hr_pers_table_en.tableId FROM DUAL;
2192 m_pers_table_exists :=true;
2193 end;
2194
2195 -- dbms_output.put_line('Hier Shar Table ID: ' || m_hr_shar_table_en.tableId );
2196 -- dbms_output.put_line('Hier Pers Table ID: ' || m_hr_pers_table_en.tableId );
2197
2198 m_hier_en.SharTableId := m_hr_shar_table_en.tableId;
2199 m_hier_en.PersTableId := m_hr_pers_table_en.tableId;
2200
2201 -- If we are building MD for a personal-personal hierarchy, do not update
2202 -- zpb_hierarchies as we do not want to store personal-personal table id there
2203 if p_type='PERSONAL' then
2204 select hierarchy_id into m_hierarchy_id
2205 from zpb_hierarchies
2206 where dimension_id = m_dimension_en.DimensionId and
2207 pers_cwm_name = m_hier_en.PersCWMName;
2208 else
2209 m_hierarchy_id := insertHierarchyRecord(m_hier_en);
2210 end if;
2211
2212
2213 m_hier_tl_en.HierarchyId := m_hierarchy_id;
2214 m_hier_tl_en.Language := 'US';
2215
2216 -- Insert Hierarchy TL Entry
2217 htld_i := 1;
2218
2219 zpb_aw.execute ('push '||l_aw||l_global_ecm.LangDim);
2220
2221 loop -- LOOP OVER ALL LANGUAGES
2222
2223 htld_j := instr (l_langs, ' ', htld_i);
2224 if (htld_j = 0) then
2225 l_lang := substr (l_langs, htld_i);
2226 else
2227 l_lang := substr (l_langs, htld_i, htld_j-htld_i);
2228 htld_i := htld_j+1;
2229 end if;
2230
2231 zpb_aw.execute('lmt '||l_aw||l_global_ecm.LangDim||' to '''||l_lang||'''');
2232
2233 if (zpb_aw.interpbool('shw exists ('''||l_aw||l_dim_ecm.HierVersLdscVar||''')')) then
2234 m_hier_tl_en.Name := zpb_aw.interp('shw '||l_aw||l_dim_ecm.HierVersLdscVar);
2235 if (m_hier_tl_en.Name is null or m_hier_tl_en.Name = 'NA') then
2236 m_hier_tl_en.Name := zpb_aw.interp('shw '||l_aw||l_dim_ecm.HierLdscVar);
2237 else
2238 m_hier_tl_en.Name := zpb_aw.interp('shw joinchars('||l_aw||l_dim_ecm.HierLdscVar||
2239 ' '': '' '||l_dim_ecm.HierVersLdscVar||')');
2240 end if;
2241 else
2242 m_hier_tl_en.Name := zpb_aw.interp('shw '||l_aw||l_dim_ecm.HierLdscVar);
2243 end if;
2244
2245 m_hier_tl_en.LongName :=m_hier_tl_en.Name;
2246 m_hier_tl_en.PluralName :=m_hier_tl_en.Name;
2247 m_hier_tl_en.PluralLongName :=m_hier_tl_en.Name;
2248 m_hier_tl_en.Language:=l_lang;
2249
2250 insertHierarchyTLRecord(m_hier_tl_en);
2251
2252 exit when htld_j = 0;
2253 end loop; -- End looping over Languages
2254
2255 zpb_aw.execute ('pop '||l_aw||l_global_ecm.LangDim);
2256
2257 -- Set Hierarchy Table Types
2258 m_hr_pers_table_en.tableType:='HIERARCHY';
2259 m_hr_shar_table_en.tableType:='HIERARCHY';
2260
2261 l_lvlhier := l_hier;
2262
2263
2264 --
2265 -- Get the Levels:
2266 --
2267 li := 1;
2268 l_levels := zpb_aw.interp('shw CM.GETDIMVALUES('''||l_aw||
2269 l_dim_ecm.LevelDim||''', YES)');
2270 -- initialize level order , used for parentage
2271 m_level_order := 0;
2272 loop -- Loop over all levels for hierarchy
2273 lj := instr (l_levels, ' ', li);
2274 if (lj = 0) then
2275 l_level := substr (l_levels, li);
2276 else
2277 l_level := substr (l_levels, li, lj-li);
2278 li := lj+1;
2279 end if;
2280
2281 -- dbms_output.put_line('Looping for level ' || l_level);
2282
2283 m_level_order := m_level_order + 1;
2284
2285 zpb_aw.execute('lmt '||l_aw||l_dim_ecm.LevelDim||' to '''||
2286 l_level||'''');
2287
2288 --
2289 -- Check to see if any members are at this level:
2290 --
2291 zpb_aw.execute('lmt '||l_aw||l_dim_data.ExpObj||' to '||
2292 l_aw||l_dim_ecm.HOrderVS);
2293
2294 zpb_aw.execute ('lmt '||l_aw||l_dim_data.ExpObj||' keep '||
2295 l_aw||l_dim_ecm.LevelRel);
2296 l_length := to_number(zpb_aw.interp('shw convert(statlen ('||
2297 l_aw||l_dim_data.ExpObj||') text 0 no no)'));
2298 if (l_length > 0) then
2299
2300 -- initialize Level Entry
2301 m_level_en.DimensionId := m_dimension_en.DimensionId;
2302 m_level_en.EPBId := l_level;
2303
2304 m_level_en.PersCWMName := zpb_metadata_names.get_level_cwm2_name('PRS', l_dim, l_lvlHier, l_level);
2305 m_level_en.SharCWMName := zpb_metadata_names.get_level_cwm2_name(p_sharedAw, l_dim, l_lvlHier, l_level);
2306
2307 -- Level Column Entries
2308 m_pers_column_en.TableId := m_hr_pers_table_en.TableId;
2309 m_shar_column_en.TableId := m_hr_shar_table_en.TableId;
2310
2311 m_pers_column_en.columnType :='LEVEL_COLUMN';
2312 m_shar_column_en.columnType :='LEVEL_COLUMN';
2313
2314 m_pers_column_en.columnName := zpb_metadata_names.get_level_column(l_dim, l_level);
2315 m_shar_column_en.columnName := zpb_metadata_names.get_level_column(l_dim, l_level);
2316
2317 -- Initialize Hierarchy To Level Mapping Entry
2318 m_hier_level_en.SharColId := insertColumnRecord(m_shar_column_en);
2319 m_hier_level_en.PersColId := insertColumnRecord(m_pers_column_en);
2320
2321 -- Personal Level Check
2322 if (p_type = 'PERSONAL' and upper(zpb_aw.interp('shw '||
2323 l_aw||l_dim_ecm.LevelPersVar)) = 'YES') then
2324 l_persLvl := true;
2325 m_level_en.PersLevelFlag := 'Y';
2326 else
2327 m_level_en.PersLevelFlag := 'N';
2328 end if;
2329
2330 -- insert level entry
2331 -- dbms_output.put_line('Inserting level ' || m_level_en.EPBId);
2332 m_level_id := insertLevelRecord(m_level_en);
2333
2334 -- insert level TL entry
2335 m_level_tl_en.LevelId := m_level_id;
2336
2337 zpb_aw.execute ('push '||l_aw||l_global_ecm.LangDim);
2338 htld_i := 1;
2339
2340 loop -- LOOP OVER ALL LANGUAGES
2341 htld_j := instr (l_langs, ' ', htld_i);
2342 if (htld_j = 0) then
2343 l_lang := substr (l_langs, htld_i);
2344 else
2345 l_lang := substr (l_langs, htld_i, htld_j-htld_i);
2346 htld_i := htld_j+1;
2347 end if;
2348
2349 zpb_aw.execute('lmt '||l_aw||l_global_ecm.LangDim||' to '''||
2350 l_lang||'''');
2351
2352 -- Initialize Level TL Entry
2353 m_level_tl_en.Name := zpb_aw.interp('shw '||l_aw||l_dim_ecm.LevelLdscVar);
2354 m_level_tl_en.PluralLongName := zpb_aw.interp('shw '||l_aw||l_dim_ecm.LevelPlLdscVar);
2355 m_level_tl_en.LongName := m_level_tl_en.Name;
2356 m_level_tl_en.PluralName := m_level_tl_en.PluralLongName;
2357 m_level_tl_en.Language := l_lang;
2358 insertLevelTLRecord(m_level_tl_en);
2359
2360 exit when htld_j = 0;
2361 end loop; -- End looping over Languages
2362
2363 zpb_aw.execute ('pop '||l_aw||l_global_ecm.LangDim);
2364
2365 m_hier_level_en.HierId := m_hierarchy_id;
2366 m_hier_level_en.LevelId := m_level_id;
2367
2368 -- insert hier-level entry
2369 m_hier_level_en.LevelOrder := m_level_order;
2370
2371 -- If we are building MD for a personal-personal hierarchy, do not update
2372 -- zpb_hier_levels as we do not want to store personal-personal table column id there
2373 if (p_type='PERSONAL' and m_level_en.PersLevelFlag='Y') or p_type='SHARED' then
2374 m_hier_level_id := insertHierLevelRecord(m_hier_level_en);
2375 end if;
2376
2377 end if; -- end of members at level if
2378
2379 exit when lj = 0;
2380 end loop; -- End level loop
2381
2382 --
2383 -- Check if there are personal levels. If so, go against
2384 -- the personal hierarchy view:
2385 --
2386
2387 -- this is the name of the hierarchy table
2388
2389 if (l_persLvl) then
2390 m_hr_pers_table_en.TableName := zpb_metadata_names.get_dimension_view
2391 (p_aw, 'PERSONAL', l_dim, l_hier);
2392 m_hr_shar_table_en.TableName := zpb_metadata_names.get_dimension_view
2393 (p_sharedAW, 'PERSONAL', l_dim, l_hier);
2394
2395 -- clean up personal-personal hierarchy table columns that have not been updated here
2396 -- these must be removed personal levels
2397 delete zpb_columns
2398 where table_id = m_hr_pers_table_en.TableId and
2399 last_update_date < l_pp_hiert_start_time;
2400
2401 else
2402 m_hr_pers_table_en.TableName := zpb_metadata_names.get_dimension_view
2403 (p_sharedAW, 'PERSONAL', l_dim, l_hier);
2404 m_hr_shar_table_en.TableName := zpb_metadata_names.get_dimension_view
2405 (p_sharedAW, 'SHARED', l_dim, l_hier);
2406 end if;
2407
2408 -- Insert Table Record For Hierarchy
2409 m_table_id := insertTableRecord(m_hr_pers_table_en, m_pers_table_exists);
2410 m_table_id := insertTableRecord(m_hr_shar_table_en, m_shar_table_exists);
2411
2412 --insert static hierarchy table columns defined above
2413 m_hr_pers_col_memCol.TableId :=m_hr_pers_table_en.tableId;
2414 m_hr_shar_col_memCol.TableId :=m_hr_shar_table_en.tableId;
2415 m_hr_pers_col_gidCol.TableId :=m_hr_pers_table_en.tableId;
2416 m_hr_shar_col_gidCol.TableId :=m_hr_shar_table_en.tableId;
2417 m_hr_shar_col_parentCol.TableId :=m_hr_shar_table_en.tableId;
2418 m_hr_pers_col_parentCol.TableId :=m_hr_pers_table_en.tableId;
2419 m_hr_shar_col_pgidCol.TableId :=m_hr_shar_table_en.tableId;
2420 m_hr_pers_col_pgidCol.TableId :=m_hr_pers_table_en.tableId;
2421 m_hr_pers_col_orderCol.TableId :=m_hr_pers_table_en.tableId;
2422 m_hr_shar_col_orderCol.TableId :=m_hr_shar_table_en.tableId;
2423
2424 m_column_id:=insertColumnRecord(m_hr_pers_col_memCol);
2425 m_column_id:=insertColumnRecord(m_hr_shar_col_memCol);
2426
2427 m_column_id:=insertColumnRecord(m_hr_pers_col_gidCol);
2428 m_column_id:=insertColumnRecord(m_hr_shar_col_gidCol);
2429
2430 m_column_id:=insertColumnRecord(m_hr_shar_col_parentCol);
2431 m_column_id:=insertColumnRecord(m_hr_pers_col_parentCol);
2432
2433 m_column_id:=insertColumnRecord(m_hr_shar_col_pgidCol);
2434 m_column_id:=insertColumnRecord(m_hr_pers_col_pgidCol);
2435
2436 m_column_id:=insertColumnRecord(m_hr_pers_col_orderCol);
2437 m_column_id:=insertColumnRecord(m_hr_shar_col_orderCol);
2438
2439 done := true;
2440 end if; -- Does this hierarchy have members check
2441
2442 exit when hj = 0;
2443 end loop; -- End looping over Hierarchies
2444
2445 zpb_aw.execute ('pop '||l_aw||l_dim_ecm.LevelDim);
2446 zpb_aw.execute ('pop '||l_aw||l_dim_ecm.HierDim);
2447
2448 end if;
2449
2450
2451 if (done = false) then
2452 -- No Hierarchies exist for Dimension,
2453 -- Create Null hierarchy:
2454
2455 m_hier_en.DimensionId := m_dimension_en.DimensionId;
2456 m_hier_en.EPBId := 'NULL_GID';
2457 m_hier_en.HierType := 'NULL';
2458 m_hier_en.PersCWMName := zpb_metadata_names.get_hierarchy_cwm2_name('PRS', l_dim);
2459 m_hier_en.SharCWMName := zpb_metadata_names.get_hierarchy_cwm2_name(p_sharedAw, l_dim);
2460
2461 -- Null Hierarchy Table Entry
2462 m_hr_shar_table_en.TableName := zpb_metadata_names.get_dimension_view(p_sharedAW, 'SHARED', l_dim);
2463 m_hr_pers_table_en.TableName := zpb_metadata_names.get_dimension_view(p_sharedAW, 'PERSONAL', l_dim);
2464
2465 m_hr_shar_table_en.AWName := p_sharedAw;
2466 m_hr_pers_table_en.AWName := p_sharedAw;
2467
2468 m_hr_shar_table_en.TableType := 'HIERARCHY';
2469 m_hr_pers_table_en.TableType := 'HIERARCHY';
2470
2471 -- if DIMENSION table already exists for this dimension, just point the null hierarchy table to this table
2472 -- otherwise create the new null hierarchy table of type HIERARCHY
2473 begin
2474
2475 select table_id into m_hier_en.SharTableId
2476 from zpb_tables
2477 where table_name = m_hr_shar_table_en.TableName and
2478 bus_area_id = bus_area_id_num and
2479 table_type = 'DIMENSION';
2480
2481 exception
2482 when NO_DATA_FOUND then
2483 m_hier_en.SharTableId := insertTableRecord(m_hr_shar_table_en);
2484 end;
2485
2486 begin
2487
2488 select table_id into m_hier_en.PersTableId
2489 from zpb_tables
2490 where table_name = m_hr_pers_table_en.TableName and
2491 bus_area_id = bus_area_id_num and
2492 table_type = 'DIMENSION';
2493 exception
2494 when NO_DATA_FOUND then
2495 m_hier_en.PersTableId := insertTableRecord(m_hr_pers_table_en);
2496 end;
2497
2498 -- Insert Null Hierarchy Record
2499 m_hierarchy_id := insertHierarchyRecord(m_hier_en);
2500
2501 -- Insert Null Hierarchy TL Record
2502 m_hier_tl_en.HierarchyId := m_hierarchy_id;
2503
2504
2505 zpb_aw.execute ('push '||l_aw||l_global_ecm.LangDim);
2506 htld_i := 1;
2507
2508 loop -- LOOP OVER ALL LANGUAGES
2509 htld_j := instr (l_langs, ' ', htld_i);
2510 if (htld_j = 0) then
2511 l_lang := substr (l_langs, htld_i);
2512 else
2513 l_lang := substr (l_langs, htld_i, htld_j-htld_i);
2514 htld_i := htld_j+1;
2515 end if;
2516
2517 zpb_aw.execute('lmt '||l_aw||l_global_ecm.LangDim||' to '''||
2518 l_lang||'''');
2519
2520 m_hier_tl_en.Name := 'Null Hierarchy';
2521 m_hier_tl_en.LongName :=m_hier_tl_en.Name;
2522 m_hier_tl_en.PluralName :=m_hier_tl_en.Name;
2523 m_hier_tl_en.PluralLongName :=m_hier_tl_en.Name;
2524 m_hier_tl_en.Language:=l_lang;
2525
2526 insertHierarchyTLRecord(m_hier_tl_en);
2527
2528
2529 exit when htld_j = 0;
2530 end loop; -- End looping over Languages
2531 zpb_aw.execute ('pop '||l_aw||l_global_ecm.LangDim);
2532
2533 -- insert static hierarchy columns for null hierarchy
2534
2535 m_hr_pers_col_memCol.TableId :=m_hier_en.PersTableId;
2536 m_hr_shar_col_memCol.TableId :=m_hier_en.SharTableId;
2537 m_hr_pers_col_gidCol.TableId :=m_hier_en.PersTableId;
2538 m_hr_shar_col_gidCol.TableId :=m_hier_en.SharTableId;
2539 m_hr_shar_col_parentCol.TableId :=m_hier_en.SharTableId;
2540 m_hr_pers_col_parentCol.TableId :=m_hier_en.PersTableId;
2541 m_hr_shar_col_pgidCol.TableId :=m_hier_en.SharTableId;
2542 m_hr_pers_col_pgidCol.TableId :=m_hier_en.PersTableId;
2543
2544 m_column_id:=insertColumnRecord(m_hr_pers_col_memCol);
2545 m_column_id:=insertColumnRecord(m_hr_shar_col_memCol);
2546
2547 m_column_id:=insertColumnRecord(m_hr_pers_col_gidCol);
2548 m_column_id:=insertColumnRecord(m_hr_shar_col_gidCol);
2549
2550 m_column_id:=insertColumnRecord(m_hr_shar_col_parentCol);
2551 m_column_id:=insertColumnRecord(m_hr_pers_col_parentCol);
2552
2553 m_column_id:=insertColumnRecord(m_hr_shar_col_pgidCol);
2554 m_column_id:=insertColumnRecord(m_hr_pers_col_pgidCol);
2555
2556 -- initialize Level entry
2557 m_level_en.DimensionId := m_dimension_en.DimensionId;
2558 m_level_en.EPBId := '0';
2559 m_level_en.PersCWMName := zpb_metadata_names.get_level_cwm2_name('PRS', l_dim);
2560 m_level_en.SharCWMName := zpb_metadata_names.get_level_cwm2_name(p_sharedAw, l_dim);
2561 m_level_en.PersLevelFlag :='N';
2562
2563 -- initialize Level TL entry
2564 m_level_tl_en.Name := 'NULL_HIER_LEVEL';
2565 m_level_tl_en.LongName :=m_level_tl_en.Name;
2566 m_level_tl_en.PluralName :=m_level_tl_en.Name;
2567 m_level_tl_en.PluralLongName :=m_level_tl_en.Name;
2568
2569 -- Level Column Entries
2570 m_pers_column_en.TableId := m_hier_en.PersTableId;
2571 m_shar_column_en.TableId := m_hier_en.SharTableId;
2572
2573 m_pers_column_en.columnType := 'LEVEL_COLUMN';
2574 m_shar_column_en.columnType := 'LEVEL_COLUMN';
2575
2576 m_pers_column_en.columnName := zpb_metadata_names.get_level_column(l_dim, null);
2577 m_shar_column_en.columnName := zpb_metadata_names.get_level_column(l_dim, null);
2578
2579 -- Insert Column Entries
2580 m_hier_level_en.SharColId := insertColumnRecord(m_shar_column_en);
2581 m_hier_level_en.PersColId := insertColumnRecord(m_pers_column_en);
2582
2583 -- Insert Level Entry
2584 m_level_id := insertLevelRecord(m_level_en);
2585
2586 -- Insert Level TL Entry
2587 m_level_tl_en.LevelId := m_level_id;
2588 m_level_tl_en.Language :='US';
2589 insertLevelTLRecord(m_level_tl_en);
2590
2591 -- Hierarchy Level Mapping Entry
2592 m_hier_level_en.LevelId := m_level_id;
2593 m_hier_level_en.HierId := m_hierarchy_id;
2594
2595 -- insert hier-level entry
2596 m_hier_level_en.LevelOrder:=1;
2597 m_hier_level_id := insertHierLevelRecord(m_hier_level_en);
2598 end if;
2599
2600 -- DIMENSION ATTRIBUTES
2601 zpb_aw.execute('lmt '||l_aw||l_global_ecm.AttrDim||' to '||l_aw||
2602 l_global_attr_ecm.DomainDimRel||' eq lmt ('||l_aw||
2603 l_global_ecm.DimDim||' to '''||l_dim||''')');
2604
2605 l_attrs := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_aw||
2606 l_global_ecm.AttrDim||''' YES)');
2607
2608 if (l_attrs <> 'NA') then
2609 ai := 1;
2610 loop -- Loop over Attributes of the Dimension
2611 aj := instr (l_attrs, ' ', ai);
2612 if (aj = 0) then
2613 l_attr := substr (l_attrs, ai);
2614 else
2615 l_attr := substr (l_attrs, ai, aj-ai);
2616 ai := aj+1;
2617 end if;
2618
2619 l_attr_ecm := zpb_ecm.get_attr_ecm(l_attr, l_global_attr_ecm, p_aw);
2620
2621 zpb_aw.execute ('lmt '||l_aw||l_global_ecm.AttrDim||' to '''|| l_attr||'''');
2622
2623 -- explicitly exclude timespan and non-displayable attributes
2624 if (instr (l_attr, 'TIMESPAN') = 0 and (zpb_aw.interpbool('shw exists(''' || l_aw || l_global_attr_ecm.AttrDisplayVar || ''')')
2625 and zpb_aw.interpbool('shw '||l_aw|| l_global_attr_ecm.AttrDisplayVar))) then
2626
2627 l_attrId :=zpb_aw.interp('shw '||l_aw||
2628 l_global_attr_ecm.RangeDimRel);
2629
2630 -- create attribute entry
2631 m_attr_en.DimensionId := m_dimension_en.DimensionId;
2632 m_attr_en.EPBId := l_attr;
2633 m_attr_en.Type :='DIMENSION_ATTRIBUTE';
2634 m_attr_en.PersCWMName := zpb_metadata_names.get_attribute_cwm2_name('PRS',l_dim,l_attr);
2635 m_attr_en.SharCWMName := l_attr;
2636 m_attr_en.Label := zpb_aw.interp('shw '||l_aw||'ATTRLABEL');
2637 m_attr_en.FEMAttrId := null;
2638
2639 -- get the FEM attribute label from the namefragment
2640 -- if run into parsing problem, give up
2641 begin
2642 l_attr_nameFrag := zpb_aw.interp('shw '||l_aw|| l_global_attr_ecm.NameFragVar);
2643 m_attr_en.FEMAttrId := to_number(substr(l_attr_nameFrag, 2));
2644 exception
2645 when others then
2646 null;
2647 end;
2648 -- Attribute Column
2649 m_pers_column_en.TableId := m_dimension_en.PersTableId;
2650 m_shar_column_en.TableId := m_dimension_en.SharTableId;
2651
2652 m_pers_column_en.columnType := 'ATTRIBUTE_COLUMN';
2653 m_shar_column_en.columnType := 'ATTRIBUTE_COLUMN';
2654
2655 m_pers_column_en.columnName := zpb_metadata_names.get_attribute_column(l_dim, l_attr);
2656 m_shar_column_en.columnName := zpb_metadata_names.get_attribute_column(l_dim, l_attr);
2657
2658 m_pers_column_en.AWName := l_attr_ecm.LdscFrm;
2659 m_shar_column_en.AWNAme := l_attr_ecm.LdscFrm;
2660
2661 -- process attribute range dimension
2662 -- insert it into dimension table if it does not already exist there
2663
2664 m_attr_rangedim_en.EPBId := l_attrId;
2665
2666
2667
2668 -- begin
2669
2670 -- select dimension_id into m_attr_rangedim_en.DimensionId
2671 -- from zpb_dimensions
2672 -- where bus_area_id = bus_area_id_num and
2673 -- epb_id = m_attr_rangedim_en.EPBId;
2674
2675 -- exception
2676 -- when NO_DATA_FOUND then
2677 -- SELECT zpb_dimensions_seq.NEXTVAL INTO m_attr_rangedim_en.DimensionId FROM DUAL;
2678 -- end;
2679
2680 l_range_dim_data := zpb_ecm.get_dimension_data (m_attr_rangedim_en.EPBId, p_aw);
2681
2682 m_attr_rangedim_en.PersCWMName := zpb_metadata_names.get_dimension_cwm2_name('PRS', l_attrId);
2683 m_attr_rangedim_en.SharCWMName := zpb_metadata_names.get_dimension_cwm2_name(p_sharedAw, l_attrId);
2684 m_attr_rangedim_en.SharTableId :=0;
2685 m_attr_rangedim_en.AWName := l_range_dim_data.ExpObj;
2686 m_attr_rangedim_en.IsDatadim :='N';
2687
2688 -- Attribute Range Dimension Table
2689 -- Feb 02 change from l_attrId to l_attr per Greg
2690 m_attr_rangetbl_en.TableName := zpb_metadata_names.get_dimension_view
2691 (p_sharedAW, 'PERSONAL', l_attr);
2692 m_attr_rangetbl_en.tableType := 'ATTRIBUTE';
2693 m_attr_rangetbl_en.AWName := p_aw;
2694
2695 -- shared attribute range dimension table
2696 m_attr_range_sh_tbl_en.TableName := zpb_metadata_names.get_dimension_view(p_sharedAW, 'SHARED', l_attr);
2697 m_attr_range_sh_tbl_en.tableType := 'ATTRIBUTE';
2698 m_attr_range_sh_tbl_en.AWName := p_sharedAW;
2699
2700 m_attr_rangecol_en.TableId := insertTableRecord(m_attr_rangetbl_en);
2701
2702 m_attr_range_sh_col_en.TableId := insertTableRecord(m_attr_range_sh_tbl_en);
2703
2704 m_attr_rangedim_en.PersTableId := m_attr_rangecol_en.TableId;
2705
2706 m_attr_rangedim_en.SharTableId := m_attr_range_sh_col_en.TableId;
2707
2708 -- Insert Attribute Range Dimension Entry
2709 m_attr_rangedim_en.DimensionId := insertDimensionRecord(m_attr_rangedim_en);
2710 m_attr_rangedim_tl_en.DimensionId := m_attr_rangedim_en.DimensionId;
2711 m_attr_en.RangeDimId := m_attr_rangedim_en.DimensionId;
2712 m_attr_rangehier_en.DimensionId := m_attr_rangedim_en.DimensionId;
2713
2714 -- Attribute Range Dimension Hierarchy
2715 m_attr_rangehier_en.EPBID :='0';
2716 m_attr_rangehier_en.PersCWMName := zpb_metadata_names.get_hierarchy_cwm2_name('PRS', l_attr);
2717 m_attr_rangehier_en.SharCWMName := zpb_metadata_names.get_hierarchy_cwm2_name(p_sharedAw, l_attr);
2718 m_attr_rangehier_en.HierType := 'NULL';
2719
2720 m_attr_rangehier_en.SharTableId := m_attr_range_sh_col_en.TableId;
2721 m_attr_rangehier_en.PersTableId := m_attr_rangecol_en.TableId;
2722
2723 -- Attribute Range Dimension Column - MEMBER
2724 m_attr_rangecol_en.columnType := 'MEMBER_COLUMN';
2725 m_attr_rangecol_en.columnName := zpb_metadata_names.get_dimension_column(l_attr);
2726 m_attr_rangecol_en.AWName := 'NA';
2727 m_column_id := insertColumnRecord(m_attr_rangecol_en);
2728
2729 m_attr_range_sh_col_en.columnType := 'MEMBER_COLUMN';
2730 m_attr_range_sh_col_en.columnName := zpb_metadata_names.get_dimension_column(l_attr);
2731 m_attr_range_sh_col_en.AWName := 'NA';
2732 m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
2733
2734
2735 -- Attribute Range Dimension Column - GID
2736 m_attr_rangecol_en.columnType := 'GID_COLUMN';
2737 m_attr_rangecol_en.columnName := zpb_metadata_names.get_dim_gid_column(l_attr);
2738 m_column_id := insertColumnRecord(m_attr_rangecol_en);
2739
2740 m_attr_range_sh_col_en.columnType := 'GID_COLUMN';
2741 m_attr_range_sh_col_en.columnName := zpb_metadata_names.get_dim_gid_column(l_attr);
2742 m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
2743
2744 -- Attribute Range Dimension Column - PGID
2745 m_attr_rangecol_en.columnType := 'PGID_COLUMN';
2746 m_attr_rangecol_en.columnName := zpb_metadata_names.get_dim_pgid_column(l_attr);
2747 m_column_id := insertColumnRecord(m_attr_rangecol_en);
2748
2749 m_attr_range_sh_col_en.columnType := 'PGID_COLUMN';
2750 m_attr_range_sh_col_en.columnName := zpb_metadata_names.get_dim_pgid_column(l_attr);
2751 m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
2752
2753 -- Attribute Range Dimension Column - PARENT
2754 m_attr_rangecol_en.columnType := 'PARENT_COLUMN';
2755 m_attr_rangecol_en.columnName := zpb_metadata_names.get_dim_parent_column(l_attr);
2756 m_column_id := insertColumnRecord(m_attr_rangecol_en);
2757
2758 m_attr_range_sh_col_en.columnType := 'PARENT_COLUMN';
2759 m_attr_range_sh_col_en.columnName :=
2760 zpb_metadata_names.get_dim_parent_column(l_attr);
2761 m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
2762
2763 -- Attribute Range Dimension Level
2764 m_attr_rangelev_en.DimensionId :=m_attr_rangedim_en.DimensionId;
2765
2766 m_attr_rangelev_en.EPBId :='0';
2767 m_attr_rangelev_en.PersCWMName := zpb_metadata_names.get_level_cwm2_name('PRS', l_attr);
2768 m_attr_rangelev_en.SharCWMName := zpb_metadata_names.get_level_cwm2_name(p_sharedAw, l_attr);
2769 m_attr_rangelev_en.PersLevelFlag :='N';
2770
2771 -- Attribute Range Dimension Hierarchy to Level Mapping
2772 m_attr_rangehl_en.LevelId := insertLevelRecord(m_attr_rangelev_en);
2773
2774 -- Attribute Range Dimension Hiearchy Level
2775 m_attr_rangecol_en.columnName := zpb_metadata_names.get_level_column(l_attr, null);
2776 m_attr_rangecol_en.columnType := 'LEVEL_COLUMN';
2777 m_attr_rangecol_en.AWName :='';
2778 m_attr_rangehl_en.LevelOrder := 1;
2779 m_attr_rangehl_en.PersColId := insertColumnRecord(m_attr_rangecol_en);
2780
2781 m_attr_range_sh_col_en.columnName := zpb_metadata_names.get_level_column(l_attr, null);
2782 m_attr_range_sh_col_en.columnType := 'LEVEL_COLUMN';
2783 m_attr_range_sh_col_en.AWName :='';
2784 m_attr_rangehl_en.LevelOrder := 1;
2785 m_attr_rangehl_en.SharColId := insertColumnRecord(m_attr_range_sh_col_en);
2786
2787 -- attribute column goes into the same hierarchy table
2788
2789 m_attr_rangeattr_en.DimensionId := m_attr_rangedim_en.DimensionId;
2790 m_attr_rangeattr_en.EPBId :='';
2791 m_attr_rangeattr_en.Type := 'VALUE_NAME_ATTRIBUTE';
2792 m_attr_rangeattr_en.PersCWMName := zpb_metadata_names.get_dim_long_name_cwm2('PRS',l_attr);
2793 m_attr_rangeattr_en.SharCWMName := zpb_metadata_names.get_dim_long_name_cwm2(p_sharedAw,l_attr);
2794
2795 m_attr_rangeattr_en.FEMAttrId := null;
2796 m_attr_rangeattr_en.Label :=' ';
2797
2798 m_attr_rangeatc_en.AttributeId := insertAttributeRecord(m_attr_rangeattr_en);
2799 m_attr_rangeatc_en.TableId := m_attr_rangehier_en.PersTableId;
2800
2801 m_attr_sh_rangeatc_en.AttributeId := m_attr_rangeatc_en.AttributeId;
2802 m_attr_sh_rangeatc_en.TableId := m_attr_rangehier_en.SharTableId;
2803
2804 m_attr_rangecol_en.columnType := 'LNAME_COLUMN';
2805 m_attr_rangecol_en.columnName := zpb_metadata_names.get_dim_long_name_column(l_attr);
2806 m_attr_rangeatc_en.ColumnId := insertColumnRecord(m_attr_rangecol_en);
2807
2808 m_attr_range_sh_col_en.columnType := 'LNAME_COLUMN';
2809 m_attr_range_sh_col_en.columnName := zpb_metadata_names.get_dim_long_name_column(l_attr);
2810 m_attr_sh_rangeatc_en.ColumnId := insertColumnRecord(m_attr_range_sh_col_en);
2811
2812 m_attr_table_col_id := insertAttrTableColRecord(m_attr_rangeatc_en);
2813
2814 m_attr_table_col_id := insertAttrTableColRecord(m_attr_sh_rangeatc_en);
2815
2816 m_attr_rangeattr_en.DimensionId := m_attr_rangedim_en.DimensionId;
2817 m_attr_rangeattr_en.EPBId :='';
2818 m_attr_rangeattr_en.Type := 'SHORT_VALUE_NAME_ATTRIBUTE';
2819 m_attr_rangeattr_en.FEMAttrId := null;
2820 m_attr_rangeattr_en.Label :=' ';
2821
2822 -- previously we had no shared attribute range dimensions and the personal names for their attributes
2823 -- was actually using the shared convention. Now that we do have shared attribute range dimensions,
2824 -- personal and shared names will use their appropriate conventions.
2825 m_attr_rangeattr_en.PersCWMName := zpb_metadata_names.get_dim_short_name_cwm2('PRS',l_attr);
2826 m_attr_rangeattr_en.SharCWMName := zpb_metadata_names.get_dim_short_name_cwm2(p_sharedAW,l_attr);
2827
2828
2829 m_attr_rangeatc_en.AttributeId := insertAttributeRecord(m_attr_rangeattr_en);
2830 m_attr_rangeatc_en.TableId := m_attr_rangehier_en.PersTableId;
2831
2832 m_attr_sh_rangeatc_en.AttributeId := m_attr_rangeatc_en.AttributeId;
2833 m_attr_sh_rangeatc_en.TableId := m_attr_rangehier_en.SharTableId;
2834
2835 m_attr_rangecol_en.columnType := 'SNAME_COLUMN';
2836 m_attr_rangecol_en.columnName := zpb_metadata_names.get_dim_short_name_column(l_attr);
2837 m_attr_rangeatc_en.ColumnId := insertColumnRecord(m_attr_rangecol_en);
2838
2839 m_attr_range_sh_col_en.columnType := 'SNAME_COLUMN';
2840 m_attr_range_sh_col_en.columnName := zpb_metadata_names.get_dim_short_name_column(l_attr);
2841 m_attr_sh_rangeatc_en.ColumnId := insertColumnRecord(m_attr_range_sh_col_en);
2842
2843 m_attr_table_col_id := insertAttrTableColRecord(m_attr_rangeatc_en);
2844
2845 m_attr_table_col_id := insertAttrTableColRecord(m_attr_sh_rangeatc_en);
2846
2847 -- Insert dimension language entries
2848
2849 htld_i := 1;
2850
2851 zpb_aw.execute ('push '||l_aw||l_global_ecm.LangDim);
2852
2853 loop -- LOOP OVER ALL LANGUAGES
2854 htld_j := instr (l_langs, ' ', htld_i);
2855 if (htld_j = 0) then
2856 l_lang := substr (l_langs, htld_i);
2857 else
2858 l_lang := substr (l_langs, htld_i, htld_j-htld_i);
2859 htld_i := htld_j+1;
2860 end if;
2861
2862 zpb_aw.execute('lmt '||l_aw||l_global_ecm.LangDim||' to '''||
2863 l_lang||'''');
2864
2865 l_range_dim_data := zpb_ecm.get_dimension_data (m_attr_rangedim_en.EPBId, p_aw);
2866
2867 m_attr_rangedim_tl_en.Name := l_range_dim_data.Sdsc;
2868 m_attr_rangedim_tl_en.LongName := l_range_dim_data.Ldsc;
2869 m_attr_rangedim_tl_en.PluralName := l_range_dim_data.PlSdsc;
2870 m_attr_rangedim_tl_en.PluralLongName := l_range_dim_data.PlLdsc;
2871 m_attr_rangedim_tl_en.Language:=l_lang;
2872 insertDimensionsTLRecord(m_attr_rangedim_tl_en);
2873
2874 exit when htld_j = 0;
2875 end loop; -- End looping over Languages
2876 zpb_aw.execute ('pop '||l_aw||l_global_ecm.LangDim);
2877
2878 -- Insert Attribute Range Dimension Hierarchy Entry
2879 m_attr_rangehl_en.HierId := insertHierarchyRecord(m_attr_rangehier_en);
2880
2881 -- Insert Attribute Range Dimension Hierarchy to Level Mapping Entry
2882 m_hier_level_id := insertHierLevelRecord(m_attr_rangehl_en);
2883
2884 -- insert attribute, column, and attr_table_col records
2885 m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
2886
2887 -- create attribute tl entry
2888 m_attr_tl_en.AttributeId := m_attr_table_col_en.attributeId;
2889
2890 htld_i := 1;
2891
2892 zpb_aw.execute ('push '||l_aw||l_global_ecm.LangDim);
2893
2894 loop -- LOOP OVER ALL LANGUAGES
2895 htld_j := instr (l_langs, ' ', htld_i);
2896 if (htld_j = 0) then
2897 l_lang := substr (l_langs, htld_i);
2898 else
2899 l_lang := substr (l_langs, htld_i, htld_j-htld_i);
2900 htld_i := htld_j+1;
2901 end if;
2902
2903 zpb_aw.execute('lmt '||l_aw||l_global_ecm.LangDim||' to '''||
2904 l_lang||'''');
2905
2906 m_attr_tl_en.Name := zpb_aw.interp('shw '||l_aw||l_global_attr_ecm.LdscVar);
2907 m_attr_tl_en.LongName :=m_attr_tl_en.Name;
2908 m_attr_tl_en.PluralName :=m_attr_tl_en.Name;
2909 m_attr_tl_en.PluralLongName :=m_attr_tl_en.Name;
2910 m_attr_tl_en.Language := l_lang;
2911
2912 insertAttributesTLRecord(m_attr_tl_en);
2913
2914 exit when htld_j = 0;
2915 end loop; -- End looping over Languages
2916 zpb_aw.execute ('pop '||l_aw||l_global_ecm.LangDim);
2917
2918 -- pers table relation
2919 m_attr_table_col_en.tableId := m_dimension_en.PersTableId;
2920 m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
2921
2922 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
2923
2924 -- shar table relation
2925 m_attr_table_col_en.tableId := m_dimension_en.SharTableId;
2926 m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
2927
2928 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
2929 end if;
2930
2931 exit when aj = 0;
2932 end loop; -- End Looping Over Attributes
2933 end if;
2934
2935 -- Long Name Attribute for Dimension
2936
2937 m_attr_en.DimensionId := m_dimension_en.DimensionId;
2938 m_attr_en.FEMAttrId :=null;
2939 m_attr_en.RangeDimId := null;
2940 m_attr_en.EPBId := '';
2941 m_attr_en.PersCWMName := zpb_metadata_names.get_dim_long_name_cwm2('PERSONAL', l_dim);
2942 m_attr_en.SharCWMName := zpb_metadata_names.get_dim_long_name_cwm2(p_sharedAw, l_dim);
2943 m_attr_en.Label := ' ';
2944 m_attr_en.Type := 'VALUE_NAME_ATTRIBUTE';
2945
2946 m_pers_column_en.TableId := m_dimension_en.PersTableId;
2947 m_shar_column_en.TableId := m_dimension_en.SharTableId;
2948
2949 m_shar_column_en.columnType := 'LNAME_COLUMN';
2950 m_pers_column_en.columnType := 'LNAME_COLUMN';
2951
2952 m_shar_column_en.columnName := zpb_metadata_names.get_dim_long_name_column(l_dim);
2953 m_pers_column_en.columnName := zpb_metadata_names.get_dim_long_name_column(l_dim);
2954
2955 m_shar_column_en.AWName := l_dim_ecm.LdscVar;
2956 m_pers_column_en.AWName := l_dim_ecm.LdscVar;
2957
2958 m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
2959
2960 -- pers table relation
2961 m_attr_table_col_en.tableId := m_dimension_en.PersTableId;
2962 m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
2963
2964 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
2965
2966 -- shar table relation
2967 m_attr_table_col_en.tableId := m_dimension_en.SharTableId;
2968 m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
2969
2970 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
2971
2972 m_attr_en.DimensionId := m_dimension_en.DimensionId;
2973 m_attr_en.RangeDimId := null;
2974 m_attr_en.EPBId := '';
2975 m_attr_en.PersCWMName := zpb_metadata_names.get_dim_short_name_cwm2('PERSONAL', l_dim);
2976 m_attr_en.SharCWMName := zpb_metadata_names.get_dim_short_name_cwm2(p_sharedAw, l_dim);
2977 m_attr_en.Type := 'SHORT_VALUE_NAME_ATTRIBUTE';
2978 m_attr_en.Label := ' ';
2979
2980 m_pers_column_en.TableId := m_dimension_en.PersTableId;
2981 m_shar_column_en.TableId := m_dimension_en.SharTableId;
2982
2983 m_shar_column_en.columnType := 'SNAME_COLUMN';
2984 m_pers_column_en.columnType := 'SNAME_COLUMN';
2985
2986 m_shar_column_en.columnName := zpb_metadata_names.get_dim_short_name_column(l_dim);
2987 m_pers_column_en.columnName := zpb_metadata_names.get_dim_short_name_column(l_dim);
2988
2989 m_shar_column_en.AWName := l_dim_ecm.MdscVar;
2990 m_pers_column_en.AWName := l_dim_ecm.MdscVar;
2991
2992 m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
2993
2994 -- pers table relation
2995 m_attr_table_col_en.tableId := m_dimension_en.PersTableId;
2996 m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
2997
2998 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
2999
3000 -- shar table relation
3001 m_attr_table_col_en.tableId := m_dimension_en.SharTableId;
3002 m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
3003
3004 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3005
3006
3007 if (l_dim_data.Type = 'TIME') then
3008 l_dim_time_ecm := zpb_ecm.get_dimension_time_ecm(l_dim, p_aw);
3009
3010 m_attr_en.DimensionId := m_dimension_en.DimensionId;
3011 m_attr_en.FEMAttrId := null;
3012 m_attr_en.RangeDimId := null;
3013 m_attr_en.EPBId := 'ENDDATE';
3014 m_attr_en.PersCWMName := zpb_metadata_names.get_attribute_cwm2_name('PRS',l_dim,'ENDDATE');
3015 m_attr_en.SharCWMName := 'END DATE';
3016 m_attr_en.Type := 'ENDDATE_ATTRIBUTE';
3017 m_attr_en.Label := ' ';
3018
3019 m_pers_column_en.TableId := m_dimension_en.PersTableId;
3020 m_shar_column_en.TableId := m_dimension_en.SharTableId;
3021
3022 m_shar_column_en.columnType := 'ENDDATE_COLUMN';
3023 m_pers_column_en.columnType := 'ENDDATE_COLUMN';
3024
3025 m_shar_column_en.columnName := zpb_metadata_names.get_dim_enddate_column(l_dim);
3026 m_pers_column_en.columnName := zpb_metadata_names.get_dim_enddate_column(l_dim);
3027
3028 m_shar_column_en.AWName := l_dim_time_ecm.EndDateVar;
3029 m_pers_column_en.AWName := l_dim_time_ecm.EndDateVar;
3030
3031 m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
3032
3033 -- pers table relation
3034 m_attr_table_col_en.tableId := m_dimension_en.PersTableId;
3035 m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
3036
3037 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3038
3039 -- shar table relation
3040 m_attr_table_col_en.tableId := m_dimension_en.SharTableId;
3041 m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
3042
3043 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3044
3045 m_attr_en.DimensionId := m_dimension_en.DimensionId;
3046 m_attr_en.FEMAttrId := null;
3047 m_attr_en.RangeDimId := null;
3048 m_attr_en.EPBId := 'TIMESPAN';
3049 m_attr_en.PersCWMName := zpb_metadata_names.get_attribute_cwm2_name('PRS',l_dim,'TIMESPAN');
3050 m_attr_en.SharCWMName := 'TIME SPAN';
3051 m_attr_en.Type := 'TIMESPAN_ATTRIBUTE';
3052 m_attr_en.Label := ' ';
3053
3054 m_pers_column_en.TableId := m_dimension_en.PersTableId;
3055 m_shar_column_en.TableId := m_dimension_en.SharTableId;
3056
3057 m_shar_column_en.columnType := 'TIMESPAN_COLUMN';
3058 m_pers_column_en.columnType := 'TIMESPAN_COLUMN';
3059
3060 m_shar_column_en.columnName := zpb_metadata_names.get_dim_timespan_column(l_dim);
3061 m_pers_column_en.columnName := zpb_metadata_names.get_dim_timespan_column(l_dim);
3062
3063 m_shar_column_en.AWName := l_dim_time_ecm.TimeSpanVar;
3064 m_pers_column_en.AWName := l_dim_time_ecm.TimeSpanVar;
3065
3066 m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
3067
3068 -- pers table relation
3069 m_attr_table_col_en.tableId := m_dimension_en.PersTableId;
3070 m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
3071
3072 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3073
3074 -- shar table relation
3075 m_attr_table_col_en.tableId := m_dimension_en.SharTableId;
3076 m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
3077
3078 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3079
3080 end if; -- done special attributes for time dimension
3081
3082 -- build special attributes for line dimension
3083 if (l_dim_data.Type = 'LINE') then
3084 l_dim_line_ecm := zpb_ecm.get_dimension_line_ecm(l_dim, p_aw);
3085
3086 m_attr_en.DimensionId := m_dimension_en.DimensionId;
3087 m_attr_en.RangeDimId := null;
3088 m_attr_en.FEMAttrId := null;
3089 m_attr_en.EPBId := 'LINEAGGTIME';
3090 m_attr_en.PersCWMName := zpb_metadata_names.get_attribute_cwm2_name('PRS',l_dim,'LINEAGGTIME');
3091 m_attr_en.SharCWMName := zpb_metadata_names.get_attribute_cwm2_name(p_sharedAw,l_dim,'LINEAGGTIME');
3092 m_attr_en.Type := 'LINEAGG_ATTRIBUTE';
3093 m_attr_en.Label := ' ';
3094
3095 m_pers_column_en.TableId := m_dimension_en.PersTableId;
3096 m_shar_column_en.TableId := m_dimension_en.SharTableId;
3097
3098 m_shar_column_en.columnType := 'AGGTIME_COLUMN';
3099 m_pers_column_en.columnType := 'AGGTIME_COLUMN';
3100
3101 m_shar_column_en.columnName := zpb_metadata_names.get_dim_aggtime_column(l_dim);
3102 m_pers_column_en.columnName := zpb_metadata_names.get_dim_aggtime_column(l_dim);
3103
3104 m_shar_column_en.AWName := 'LINEAGGTIME.DL';
3105 m_pers_column_en.AWName := 'LINEAGGTIME.DL';
3106
3107 m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
3108
3109 -- pers table relation
3110 m_attr_table_col_en.tableId := m_dimension_en.PersTableId;
3111 m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
3112
3113 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3114
3115 -- shar table relation
3116 m_attr_table_col_en.tableId := m_dimension_en.SharTableId;
3117 m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
3118
3119 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3120
3121 m_attr_en.DimensionId := m_dimension_en.DimensionId;
3122 m_attr_en.RangeDimId := null;
3123 m_attr_en.FEMAttrId := null;
3124 m_attr_en.EPBId := 'LINEAGGOTHER';
3125 m_attr_en.PersCWMName := zpb_metadata_names.get_attribute_cwm2_name('PRS',l_dim,'LINEAGGOTHER');
3126 m_attr_en.SharCWMName := zpb_metadata_names.get_attribute_cwm2_name(p_sharedAw,l_dim,'LINEAGGOTHER');
3127 m_attr_en.Type := 'LINEAGG_ATTRIBUTE';
3128 m_attr_en.Label := ' ';
3129
3130 m_pers_column_en.TableId := m_dimension_en.PersTableId;
3131 m_shar_column_en.TableId := m_dimension_en.SharTableId;
3132
3133 m_shar_column_en.columnType := 'AGGOTHER_COLUMN';
3134 m_pers_column_en.columnType := 'AGGOTHER_COLUMN';
3135
3136 m_shar_column_en.columnName := zpb_metadata_names.get_dim_aggother_column(l_dim);
3137 m_pers_column_en.columnName := zpb_metadata_names.get_dim_aggother_column(l_dim);
3138
3139 m_shar_column_en.AWName := 'LINEAGGOTHER.DL';
3140 m_pers_column_en.AWName := 'LINEAGGOTHER.DL';
3141
3142 m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
3143
3144 -- pers table relation
3145 m_attr_table_col_en.tableId := m_dimension_en.PersTableId;
3146 m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
3147
3148 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3149
3150 -- shar table relation
3151 m_attr_table_col_en.tableId := m_dimension_en.SharTableId;
3152 m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
3153
3154 m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
3155 end if; -- done special attributes for line dimension
3156
3157 zpb_aw.execute('pop '||l_aw||l_dim_data.ExpObj);
3158
3159 -- Insert Dimension Entry
3160 m_dummy_num := insertDimensionRecord(m_dimension_en, m_dimension_exists);
3161
3162 -- Insert dimension language entries
3163
3164 htld_i := 1;
3165
3166 zpb_aw.execute ('push '||l_aw||l_global_ecm.LangDim);
3167
3168 loop -- LOOP OVER ALL LANGUAGES
3169 htld_j := instr (l_langs, ' ', htld_i);
3170 if (htld_j = 0) then
3171 l_lang := substr (l_langs, htld_i);
3172 else
3173 l_lang := substr (l_langs, htld_i, htld_j-htld_i);
3174 htld_i := htld_j+1;
3175 end if;
3176
3177 zpb_aw.execute('lmt '||l_aw||l_global_ecm.LangDim||' to '''||
3178 l_lang||'''');
3179
3180 l_dim_data := zpb_ecm.get_dimension_data (l_dim, p_aw);
3181 m_dimension_tl_en.Name := l_dim_data.Sdsc;
3182 m_dimension_tl_en.LongName := l_dim_data.Ldsc;
3183 m_dimension_tl_en.PluralName := l_dim_data.PlSdsc;
3184 m_dimension_tl_en.PluralLongName := l_dim_data.PlLdsc;
3185 m_dimension_tl_en.Language:=l_lang;
3186 m_dimension_tl_en.DimensionId :=m_dimension_en.DimensionId;
3187 insertDimensionsTLRecord(m_dimension_tl_en);
3188
3189 exit when htld_j = 0;
3190 end loop; -- End looping over Languages
3191 zpb_aw.execute ('pop '||l_aw||l_global_ecm.LangDim);
3192
3193 exit when j=0;
3194 end loop;
3195
3196 zpb_aw.execute('pop oknullstatus');
3197
3198 end BUILD_DIMS;
3199
3200 -------------------------------------------------------------------------------
3201 -- BUILD_CUBE -Exposes metadata for a cube (zpb_cubes), its dimensionality
3202 -- (zpb_cube_dims) and its hierarchies (zpb_cube_hier)
3203 --
3204 -- IN: p_aw - The AW
3205 -- p_cubeView - The name of the cube
3206 -- p_dims - Dimensionality of cube
3207 --
3208 -------------------------------------------------------------------------------
3209 procedure BUILD_CUBE(p_aw in varchar2,
3210 p_cubeView in varchar2,
3211 p_dims in varchar2,
3212 p_cube_type in varchar2)
3213 is
3214
3215 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_CUBE';
3216
3217 l_dim varchar2(30);
3218 l_aw varchar2(30);
3219 i number;
3220 j number;
3221 hi number;
3222 hj number;
3223 l_gid boolean;
3224 l_hiers varchar2(1000);
3225 l_hier varchar2(30);
3226 nl varchar2(1) := fnd_global.local_chr(10);
3227 l_dim_ecm zpb_ecm.dimension_ecm;
3228
3229 -- interface layer table entrieds
3230 m_cube_en zpb_md_records.cubes_entry;
3231 m_table_en zpb_md_records.tables_entry;
3232 m_cube_dims_en zpb_md_records.cube_dims_entry;
3233 m_column_en zpb_md_records.columns_entry;
3234 m_cube_hier_en zpb_md_records.cube_hier_entry;
3235
3236 m_relation_id number;
3237 m_table_id number;
3238
3239 m_cube_view varchar2(128);
3240 m_object_name varchar(128);
3241 m_cube_num number;
3242
3243 bus_area_id_num number;
3244
3245 begin
3246
3247 zpb_log.write('zpb_metadata_pkg.build_cube.begin',
3248 'Creating metadata in ' || p_aw ||
3249 ' for cube ' || p_cubeView ||
3250 ' of type '|| p_cube_type);
3251 l_gid := false;
3252 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
3253
3254 -- dbms_output.put_line('building cube ' || p_cubeView);
3255 -- dbms_output.put_line('with dims ' || p_dims);
3256
3257 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
3258
3259 if p_cube_type = 'SEC_CUBE' then
3260
3261 m_cube_view := zpb_metadata_names.get_security_cwm2_cube(p_aw);
3262 m_object_name := 'SEC_CUBE';
3263 m_cube_en.Type := 'MEASCUBE';
3264 else
3265 m_cube_view := p_cubeView;
3266 m_object_name :=p_cubeView;
3267
3268 if instr(m_cube_view, '_PRS') > 0 or instr(m_cube_view, 'ZPBDATA')=0 then
3269 m_cube_en.Type := 'PRSMEASCUBE';
3270 else
3271 m_cube_en.Type := 'MEASCUBE';
3272 end if;
3273 end if;
3274
3275 -- initialize cube entry record
3276 m_cube_en.name := m_cube_view;
3277
3278 -- initialize table entry
3279 m_table_en.tableName := p_cubeView;
3280
3281 m_table_en.AWName:= 'NA';
3282 m_table_en.tableType := 'MEASURE';
3283
3284 m_table_id := insertTableRecord(m_table_en);
3285
3286 m_cube_en.tableId := m_table_id;
3287 m_cube_en.EpbId := 'NA';
3288
3289 m_cube_num := insertCubeRecord(m_cube_en);
3290
3291 i := 1;
3292 loop
3293 j := instr (p_dims, ' ', i);
3294 if (j = 0) then
3295 l_dim := substr (p_dims, i);
3296 else
3297 l_dim := substr (p_dims, i, j-i);
3298 i := j+1;
3299 end if;
3300
3301 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_dim, p_aw);
3302
3303 -- initialize cube_dim entry
3304 m_cube_dims_en.cubeId := m_cube_num;
3305
3306 -- find appropriate dimension in zpb_dimensions
3307 begin
3308
3309 select dimension_id into m_cube_dims_en.dimensionId
3310 from zpb_dimensions
3311 where epb_id = l_dim and bus_area_id = bus_area_id_num;
3312
3313 exception
3314 when no_data_found then
3315 zpb_log.write_event('zpb_metadata.build_cube.error',
3316 'No metadata for dimension ' || l_dim ||
3317 ' of cube ' || p_cubeView);
3318 end;
3319
3320 m_column_en.tableId:= m_table_id;
3321 m_column_en.columnName := zpb_metadata_names.get_dimension_column(l_dim);
3322 m_column_en.columnType := 'MEMBER_COLUMN';
3323 m_column_en.AWName := 'NA';
3324
3325 m_cube_dims_en.columnId := insertColumnRecord(m_column_en);
3326
3327 m_relation_id := insertCubeDimsRecord(m_cube_dims_en);
3328
3329 if (zpb_aw.interp('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||''')') <> '0')
3330 then
3331 zpb_aw.execute ('lmt '||l_aw||l_dim_Ecm.HierDim||' to all');
3332 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_aw||
3333 l_dim_Ecm.HierDim||''' yes)');
3334 hi := 1;
3335 loop
3336 hj := instr (l_hiers, ' ', hi);
3337 if (hj = 0) then
3338 l_hier := substr (l_hiers, hi);
3339 else
3340 l_hier := substr (l_hiers, hi, hj-hi);
3341 hi := hj+1;
3342 end if;
3343
3344 -- initialize cube_hier entry record
3345 m_cube_hier_en.cubeId := m_cube_num;
3346
3347 -- find MD for this hierarchy
3348 begin
3349
3350 select hierarchy_id into m_cube_hier_en.hierarchyId
3351 from zpb_hierarchies
3352 where epb_id = l_hier and
3353 dimension_id = m_cube_dims_en.dimensionId;
3354
3355 exception
3356 when no_data_found then
3357
3358 zpb_log.write_event('zpb_metadata.build_cube.error',
3359 'No metadata for hierarchy ' || l_hier ||
3360 ' of dimension ' || m_cube_dims_en.dimensionId);
3361 end;
3362
3363 m_column_en.tableId := m_table_id;
3364 m_column_en.columnName := zpb_metadata_names.get_dim_gid_column(l_dim, l_hier);
3365 m_column_en.columnType := 'GID_COLUMN';
3366 m_column_en.AWName := 'NA';
3367
3368 m_cube_hier_en.columnId := insertColumnRecord(m_column_en);
3369
3370 m_relation_id := insertCubeHierRecord(m_cube_hier_en);
3371
3372 exit when hj=0;
3373 end loop;
3374 elsif (l_gid = false) then
3375 -- no hierarchies case = null hiearchy
3376
3377 m_column_en.tableId := m_table_id;
3378 m_column_en.columnName := zpb_metadata_names.get_dim_gid_column;
3379 m_column_en.columnType := 'GID_COLUMN';
3380 m_column_en.AWName :='NA';
3381
3382 m_cube_hier_en.cubeId := m_cube_num;
3383
3384 begin
3385
3386 select hierarchy_id into m_cube_hier_en.hierarchyId
3387 from zpb_hierarchies
3388 where dimension_id = m_cube_dims_en.dimensionId and
3389 hier_type = 'NULL';
3390
3391 exception
3392 when no_data_found then
3393
3394 zpb_log.write_event('zpb_metadata.build_cube.error',
3395 'No metadata for null hierarchy ' ||
3396 ' of dimension ' || m_cube_dims_en.dimensionId);
3397 end;
3398
3399 m_cube_hier_en.columnId := insertColumnRecord(m_column_en);
3400
3401 m_relation_id := insertCubeHierRecord(m_cube_hier_en);
3402
3403 l_gid := true;
3404 end if;
3405 exit when j=0;
3406 end loop;
3407
3408 zpb_log.write('zpb_metadata_pkg.build_instance.end',
3409 'succesfull completion');
3410
3411 EXCEPTION
3412 WHEN OTHERS THEN
3413 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
3414 return;
3415
3416
3417 end BUILD_CUBE;
3418
3419 -------------------------------------------------------------------------------
3420 -- BUILD_INSTANCE - Expose metadata for all measures of an instance by calling
3421 -- BUILD_MEASURE. If it has not been done yet, call
3422 -- BUILD_CUBE to expose metadat for the containing cube
3423 --
3424 -- IN: p_aw - The AW
3425 -- p_instance - The ID of the instance
3426 -- p_type - The type of the instance (PERSONAL, SHARED_VIEW, etc)
3427 -- p_approver - The approvee ID. Null is not applicable
3428 --
3429 -------------------------------------------------------------------------------
3430 procedure BUILD_INSTANCE(p_aw in varchar2,
3431 p_instance in varchar2,
3432 p_type in varchar2,
3433 p_template in varchar2,
3434 p_approvee in varchar2)
3435 is
3436
3437 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_INSTANCE';
3438
3439 l_instType varchar2(30);
3440 l_shrdInstType varchar2(30);
3441 l_shrdMeas varchar2(30);
3442 l_cube varchar2(30);
3443 l_column varchar2(30);
3444 l_meas varchar2(30);
3445 l_measAw varchar2(30);
3446 l_awQual varchar2(30);
3447 l_objName varchar2(60);
3448 l_dims varchar2(500);
3449 l_count number;
3450 l_global_ecm zpb_ecm.global_ecm;
3451 hi number;
3452 hj number;
3453 l_curMeasures varchar2(4000);
3454 l_curMeas varchar2(64);
3455 l_curRel varchar2(16);
3456 l_write_sec_type varchar2(64);
3457
3458
3459 l_baseType varchar2(30);
3460
3461 bus_area_id_num number;
3462 begin
3463
3464
3465
3466 zpb_log.write('zpb_metadata_pkg.build_instance.begin',
3467 'Creating metadata in ' || p_aw ||
3468 ' of type ' || p_type ||
3469 ' for instance '|| p_instance ||
3470 ' and template ' || p_template ||
3471 ' and approvee ' || p_approvee);
3472
3473 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
3474
3475 l_awQual := zpb_aw.get_schema||'.'||p_aw||'!';
3476
3477 l_global_ecm := zpb_ecm.get_global_ecm (p_aw);
3478
3479 l_instType := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||
3480 ''' ''TYPE'')');
3481
3482 -- If we are building md for personal shared measures, get the SHARED measure here. It will be
3483 -- renamed correctly (in accordance to PERSONAL measure naming in BUILD_MEASURE
3484 if p_type = 'SHARED_VIEW' then
3485 l_baseType :='SHARED';
3486 else
3487 l_baseType :=p_type;
3488 end if;
3489
3490 if (instr (l_instType, 'CALC') > 0) then
3491 l_instType := l_baseType||' CALC';
3492 l_shrdInstType := 'SHARED CALC';
3493 else
3494 l_instType := l_baseType;
3495 l_shrdInstType := 'SHARED';
3496 end if;
3497
3498 l_objName := 'OBJECT ID'' NA ';
3499 if (p_approvee is not null) then
3500 l_objName := l_objName||''''||p_approvee||''' ';
3501 else
3502 l_objName := l_objName||'NA ';
3503 end if;
3504 if (p_template is not null) then
3505 l_objName := l_objName||''''||p_template||'''';
3506 end if;
3507
3508 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
3509 l_instType||' DATA '||l_objName||'''');
3510
3511 if (l_meas = 'NA')
3512 then return;
3513 end if;
3514
3515 if (p_type = 'SHARED_VIEW') then
3516 l_measAw := 'SHARED!';
3517 l_shrdMeas := zpb_aw.interp('shw CM.GETINSTOBJECT ('''||p_instance||
3518 ''' '''||l_shrdInstType||' DATA OBJECT ID'')');
3519 else
3520 l_measAW := l_awQual;
3521 l_shrdMeas := l_meas;
3522 end if;
3523
3524 if (l_shrdMeas = 'NA')
3525 then return;
3526 end if;
3527
3528 zpb_aw.execute ('push '||l_measAw||'MEASURE;'||
3529 ' lmt '||l_measAw||'MEASURE to '''||l_shrdMeas||'''');
3530
3531 l_cube := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasViewRel);
3532
3533 -- Append _PRS to personal cube names
3534 if p_type = 'SHARED_VIEW' then
3535 l_cube := l_cube || '_PRS';
3536 end if;
3537
3538 l_column := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasColVar);
3539
3540 select count(*)
3541 into l_count
3542 from zpb_cubes
3543 where name = l_cube and
3544 bus_area_id = bus_area_id_num;
3545
3546 if (l_count = 0) then
3547 l_dims := zpb_aw.interp
3548 ('shw joinchars(joincols(lmt ('||l_awQual||l_global_ecm.DimDim||
3549 ' to '||l_awQual||l_global_ecm.MeasDimVar||' ('||l_awQual||
3550 'MEASURE '''||l_meas||''') eq yes) '' ''))');
3551 l_dims := substr(l_dims, 1, length(l_dims)-1);
3552 BUILD_CUBE (p_aw, l_cube, l_dims);
3553 end if;
3554
3555 if (zpb_aw.interpbool ('shw isValue('||l_awQual||'MEASURE '''||
3556 l_meas||''')')) then
3557 BUILD_MEASURE(p_aw, p_instance, l_meas, l_cube, l_column,
3558 p_template, p_approvee);
3559 end if;
3560
3561 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
3562 l_instType||' ANNOTATION '||l_objName||'''');
3563
3564 if (p_type = 'SHARED_VIEW') then
3565 l_measAw := 'SHARED!';
3566 l_shrdMeas := zpb_aw.interp('shw CM.GETINSTOBJECT ('''||p_instance||
3567 ''' '''||l_shrdInstType||' ANNOTATION OBJECT ID'')');
3568 else
3569 l_measAW := l_awQual;
3570 l_shrdMeas := l_meas;
3571 end if;
3572
3573 if (zpb_aw.interpbool ('shw isValue('||l_awQual||'MEASURE '''||
3574 l_meas||''')')) then
3575 zpb_aw.execute ('lmt '||l_measAw||'MEASURE to '''||l_shrdMeas||'''');
3576 l_cube := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasViewRel);
3577
3578 -- Append _PRS to personal cube names
3579 if p_type = 'SHARED_VIEW' then
3580 l_cube := l_cube || '_PRS';
3581 end if;
3582
3583 l_column := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasColVar);
3584 BUILD_MEASURE(p_aw, p_instance, l_meas, l_cube, l_column,
3585 p_template, p_approvee);
3586 end if;
3587
3588 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
3589 l_instType||' FORMAT '||l_objName||'''');
3590 if (p_type = 'SHARED_VIEW') then
3591 l_measAw := 'SHARED!';
3592 l_shrdMeas := zpb_aw.interp('shw CM.GETINSTOBJECT ('''||p_instance||
3593 ''' '''||l_shrdInstType||' FORMAT OBJECT ID'')');
3594 else
3595 l_measAW := l_awQual;
3596 l_shrdMeas := l_meas;
3597 end if;
3598
3599 if (zpb_aw.interpbool ('shw isValue('||l_awQual||'MEASURE '''||
3600 l_meas||''')')) then
3601 zpb_aw.execute ('lmt '||l_measAw||'MEASURE to '''||l_shrdMeas||'''');
3602 l_cube := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasViewRel);
3603
3604 -- Append _PRS to personal cube names
3605 if p_type = 'SHARED_VIEW' then
3606 l_cube := l_cube || '_PRS';
3607 end if;
3608
3609 l_column := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasColVar);
3610 BUILD_MEASURE(p_aw, p_instance, l_meas, l_cube, l_column,
3611 p_template, p_approvee);
3612 end if;
3613
3614
3615 if (p_type = 'PERSONAL') then
3616 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
3617 l_instType||' TARGET '||l_objName||'''');
3618
3619 if (zpb_aw.interpbool ('shw isValue('||l_awQual||'MEASURE '''||
3620 l_meas||''')')) then
3621 zpb_aw.execute ('lmt '||l_awQual||'MEASURE to '''||l_meas||'''');
3622 l_cube := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasViewRel);
3623 l_column := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasColVar);
3624 BUILD_MEASURE(p_aw, p_instance, l_meas, l_cube, l_column,
3625 p_template, p_approvee);
3626 end if;
3627
3628 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
3629 l_instType||' TARGET TYPE '||l_objName||'''');
3630
3631 if (zpb_aw.interpbool ('shw isValue('||l_awQual||'MEASURE '''||
3632 l_meas||''')')) then
3633 zpb_aw.execute ('lmt '||l_awQual||'MEASURE to '''||l_meas||'''');
3634 l_cube := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasViewRel);
3635
3636 l_column := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasColVar);
3637 BUILD_MEASURE(p_aw, p_instance, l_meas, l_cube, l_column,
3638 p_template, p_approvee);
3639 end if;
3640
3641 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
3642 l_instType||' INPUT LEVEL OBJECT ID''');
3643
3644 if (zpb_aw.interpbool ('shw isValue('||l_awQual||'MEASURE '''||
3645 l_meas||''')')) then
3646
3647 zpb_aw.execute ('lmt '||l_measAw||'MEASURE to '''||l_meas||'''');
3648
3649 l_cube := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasViewRel);
3650
3651 -- expose MD for cube if has not been done so yet
3652 select count(*)
3653 into l_count
3654 from zpb_cubes
3655 where name = l_cube and
3656 bus_area_id = bus_area_id_num;
3657
3658 if (l_count = 0) then
3659 l_dims := zpb_aw.interp
3660 ('shw joinchars(joincols(lmt ('||l_awQual||l_global_ecm.DimDim||
3661 ' to '||l_awQual||l_global_ecm.MeasDimVar||' ('||l_awQual||
3662 'MEASURE '''||l_meas||''') eq yes) '' ''))');
3663 l_dims := substr(l_dims, 1, length(l_dims)-1);
3664 BUILD_CUBE (p_aw, l_cube, l_dims);
3665 end if;
3666
3667 l_column := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasColVar);
3668 BUILD_MEASURE(p_aw, p_instance, l_meas, l_cube, l_column,
3669 p_template, p_approvee);
3670
3671 end if; -- input level MD
3672
3673 -- expose write security of appropriate currency type
3674 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
3675 l_instType||' WRITE SECURITY '||l_objName||'''');
3676
3677 if (zpb_aw.interpbool ('shw isValue('||l_awQual||'MEASURE '''||
3678 l_meas||''')')) then
3679
3680 zpb_aw.execute ('lmt '||l_measAw||'MEASURE to '''||l_meas||'''');
3681 -- if MD for write security of this currency type for this instance has already been
3682 -- created, no need to update now
3683 if(instr(l_meas, 'PEWSEC')) > 0 then
3684 l_write_sec_type := 'PERSONAL_ENTERED_WRITE_SECURITY';
3685 else
3686 l_write_sec_type := 'PERSONAL_WRITE_SECURITY';
3687 end if;
3688
3689 l_cube := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasViewRel);
3690
3691 -- expose MD for cube if it has not been done so yet
3692 select count(*)
3693 into l_count
3694 from zpb_cubes
3695 where name = l_cube and
3696 bus_area_id = bus_area_id_num;
3697
3698 if (l_count = 0) then
3699
3700 l_dims := zpb_aw.interp
3701 ('shw joinchars(joincols(lmt ('||l_awQual||l_global_ecm.DimDim||
3702 ' to '||l_awQual||l_global_ecm.MeasDimVar||' ('||l_awQual||
3703 'MEASURE '''||l_meas||''') eq yes) '' ''))');
3704 l_dims := substr(l_dims, 1, length(l_dims)-1);
3705 BUILD_CUBE (p_aw, l_cube, l_dims);
3706 end if;
3707
3708 l_column := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasColVar);
3709 BUILD_MEASURE(p_aw, p_instance, l_meas, l_cube, l_column,
3710 p_template, p_approvee);
3711
3712 end if; -- write sec block
3713 end if; -- PERSONAL block
3714
3715 -- expose translated and entered MD
3716 if p_type = 'SHARED' or p_type = 'SHARED_VIEW' then
3717
3718 zpb_aw.execute('push oknullstatus');
3719 zpb_aw.execute('oknullstatus = yes');
3720
3721 zpb_aw.execute ('lmt '||l_awQual||'MEASURE to findchars(' || l_awQual || 'MEASURE '''|| p_instance || ''') gt 0');
3722 zpb_aw.execute ('lmt '||l_awQual||'MEASURE keep findchars(' || l_awQual || 'MEASURE ''.E.'') gt 0');
3723
3724 l_measAW := l_awQual;
3725
3726 if (zpb_aw.interp('shw convert(statlen('||l_awQual||'MEASURE'||
3727 ') text 0 no no)') <> 0) then
3728
3729 hi := 1;
3730 l_curMeasures :=zpb_aw.interp
3731 ('shw CM.GETDIMVALUES('''||l_awQual||'MEASURE'||''', ''YES'')');
3732
3733 loop -- LOOP OVER ALL ENTERED MEASURES
3734 hj := instr (l_curMeasures, ' ', hi);
3735 if (hj = 0) then
3736 l_curMeas := substr (l_curMeasures, hi);
3737 else
3738 l_curMeas := substr (l_curMeasures, hi, hj-hi);
3739 hi := hj+1;
3740 end if;
3741
3742 zpb_aw.execute ('lmt '||l_awQual||'MEASURE to '''||l_curMeas||'''');
3743 l_cube := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasViewRel);
3744
3745 -- Append _PRS to personal cube names
3746 if p_type = 'SHARED_VIEW' then
3747 l_cube := l_cube || '_PRS';
3748 end if;
3749
3750 if l_cube <>'NA' then
3751
3752 select count(*)
3753 into l_count
3754 from zpb_cubes
3755 where name = l_cube and
3756 bus_area_id = bus_area_id_num;
3757
3758 if (l_count = 0) then
3759 l_dims := zpb_aw.interp
3760 ('shw joinchars(joincols(lmt ('||l_awQual||l_global_ecm.DimDim||
3761 ' to '||l_awQual||l_global_ecm.MeasDimVar||' ('||l_awQual||
3762 'MEASURE '''||l_curMeas||''') eq yes) '' ''))');
3763 l_dims := substr(l_dims, 1, length(l_dims)-1);
3764 BUILD_CUBE (p_aw, l_cube, l_dims);
3765 end if;
3766
3767 l_column := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasColVar);
3768
3769 -- l_curRel := zpb_aw.interp('shw &joinchars('''||l_awQual||
3770 -- ''' obj(property ''MEASCURRENCYREL'' ''MEASURE''))');
3771
3772 l_curRel := zpb_aw.interp('shw '||l_awQual||'MEASCURRENCY');
3773
3774 BUILD_MEASURE(p_aw, p_instance, l_curMeas, l_cube, l_column,
3775 p_template, p_approvee, l_curRel);
3776
3777 end if; -- cube is NA
3778
3779 exit when hj = 0;
3780 end loop; -- End looping over ENTERED MEASURES
3781
3782 end if; -- if measure dim has any members
3783
3784
3785 zpb_aw.execute ('lmt '||l_awQual||'MEASURE to findchars(' || l_awQual || 'MEASURE '''|| p_instance || ''') gt 0');
3786 zpb_aw.execute ('lmt '||l_awQual||'MEASURE keep findchars(' || l_awQual || 'MEASURE ''.T.'') gt 0');
3787
3788 if (zpb_aw.interp('shw convert(statlen('||l_awQual||'MEASURE'||
3789 ') text 0 no no)') <> 0) then
3790
3791 hi := 1;
3792 l_curMeasures :=zpb_aw.interp
3793 ('shw CM.GETDIMVALUES('''||l_awQual||'MEASURE'||''', ''YES'')');
3794 -- dbms_output.put_line('l_curMeasures: ' || l_curMeasures);
3795
3796 loop -- LOOP OVER ALL CURRENCY TRANSLATED MEASURES
3797 hj := instr (l_curMeasures, ' ', hi);
3798 if (hj = 0) then
3799 l_curMeas := substr (l_curMeasures, hi);
3800 else
3801 l_curMeas := substr (l_curMeasures, hi, hj-hi);
3802 hi := hj+1;
3803 end if;
3804
3805 zpb_aw.execute ('lmt '||l_awQual||'MEASURE to '''||l_curMeas||'''');
3806
3807 --dbms_output.put_line('l_curMeas: ' || l_curMeas);
3808
3809 l_cube := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasViewRel);
3810
3811 -- Append _PRS to personal cube names
3812 if p_type = 'SHARED_VIEW' then
3813 l_cube := l_cube || '_PRS';
3814 end if;
3815
3816 --dbms_output.put_line('l_cube: ' || l_cube);
3817
3818 if l_cube <>'NA' then
3819
3820 select count(*)
3821 into l_count
3822 from zpb_cubes
3823 where name = l_cube and
3824 bus_area_id = bus_area_id_num;
3825
3826 --dbms_output.put_line('l_count: ' || l_count);
3827
3828 if (l_count = 0) then
3829 l_dims := zpb_aw.interp
3830 ('shw joinchars(joincols(lmt ('||l_awQual||l_global_ecm.DimDim||
3831 ' to '||l_awQual||l_global_ecm.MeasDimVar||' ('||l_awQual||
3832 'MEASURE '''||l_curMeas||''') eq yes) '' ''))');
3833 l_dims := substr(l_dims, 1, length(l_dims)-1);
3834 BUILD_CUBE (p_aw, l_cube, l_dims);
3835 end if;
3836
3837 l_column := zpb_aw.interp('shw '||l_measAw||l_global_ecm.MeasColVar);
3838
3839 -- l_curRel := zpb_aw.interp('shw &joinchars('''||l_awQual||
3840 -- ''' obj(property ''MEASCURRENCYREL'' ''MEASURE''))');
3841
3842 l_curRel := zpb_aw.interp('shw '||l_awQual||'MEASCURRENCY');
3843
3844 BUILD_MEASURE(p_aw, p_instance, l_curMeas, l_cube, l_column,
3845 p_template, p_approvee, l_curRel);
3846
3847 end if; --cube is NA
3848
3849 exit when hj = 0;
3850 end loop; -- End looping over CURRENCY TRANSLATED MEASURES
3851
3852 end if; -- if measure dim has any members
3853
3854 zpb_aw.execute('pop oknullstatus');
3855
3856 end if; -- shared or shared view for currency building
3857 zpb_aw.execute ('pop '||l_measAw||'MEASURE');
3858
3859 zpb_log.write('zpb_metadata_pkg.build_instance.end',
3860 'succesfull completion');
3861
3862 EXCEPTION
3863 WHEN OTHERS THEN
3864 -- dbms_output.put_line('OTHERS : ' || substr(sqlerrm,1,90));
3865 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
3866 return;
3867
3868 end BUILD_INSTANCE;
3869
3870 -------------------------------------------------------------------------------
3871 -- BUILD_MEASURE - Exposes metadata for a measure
3872 --
3873 -- IN: p_aw - The AW
3874 -- p_instance - The ID of the measure
3875 -------------------------------------------------------------------------------
3876 procedure BUILD_MEASURE(p_aw in varchar2,
3877 p_instance in varchar2,
3878 p_meas in varchar2,
3879 p_cube in varchar2,
3880 p_column in varchar2,
3881 p_template in varchar2,
3882 p_approvee in varchar2,
3883 p_currencyRel in varchar2)
3884 is
3885
3886 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_MEASURE';
3887
3888 l_measFrm varchar2(30);
3889 l_measType varchar2(64);
3890 l_measName varchar2(200);
3891 l_awQual varchar2(30);
3892 l_subMap varchar2(4000);
3893 l_schema varchar2(30);
3894 l_type varchar2(30);
3895 done boolean;
3896 i number;
3897 j number;
3898 entry number;
3899 nl varchar2(1) := fnd_global.local_chr(10);
3900 l_global_ecm zpb_ecm.global_ecm;
3901
3902 -- measure table entry
3903 m_meas_en zpb_md_records.measures_entry;
3904 m_meas_id number;
3905 m_cwm_name varchar2(128);
3906 m_column_en zpb_md_records.columns_entry;
3907
3908 l_insert_meas_flag boolean;
3909
3910 -- string manipulations for personal measure MD
3911 m_string1 varchar2(30);
3912 m_string2 varchar2(30);
3913 m_stringMid number;
3914 m_stringRepl varchar2(8);
3915
3916 -- AW param
3917 p_awParam varchar2(30);
3918
3919 -- check to avoid duplicate measure md
3920 m_countMeas number;
3921
3922 bus_area_id_num number;
3923
3924 begin
3925
3926 zpb_log.write('zpb_metadata_pkg.build_measure.begin',
3927 'Creating metadata in ' || p_aw ||
3928 ' in cube ' || p_cube ||
3929 ' for measure ' || p_meas ||
3930 ' for instance '|| p_instance ||
3931 ' and template ' || p_template ||
3932 ' and approvee ' || p_approvee);
3933
3934
3935 l_schema := zpb_aw.get_schema||'.';
3936 l_awQual := l_schema||p_aw||'!';
3937
3938 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
3939
3940 l_global_ecm := zpb_ecm.get_global_ecm(p_aw);
3941 zpb_aw.execute ('push '||l_awQual||'MEASURE');
3942 zpb_aw.execute ('lmt '||l_awQual||'MEASURE to '''||p_meas||'''');
3943
3944 l_measType := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasTypeRel);
3945
3946
3947 -- update for personal measure MD
3948
3949 if instr (p_cube, '_PRS') > 0 then
3950 m_stringMid := 7;
3951 m_string1 := substr(l_measType, 1 , m_stringMid);
3952 m_string2 := substr(l_measType, m_stringMid + 1, length(l_measType));
3953 l_measType := m_string1 || 'VIEW_' || m_string2 ;
3954 -- dbms_output.put_line('l_measType after :' || l_measType);
3955 end if;
3956
3957 l_measFrm := zpb_aw.interp('shw '||l_awQual||l_global_ecm.MeasExpObjVar);
3958
3959 -- dbms_output.put_line('l_measFrm before :' || l_measFrm);
3960 -- AWName FRM.FM8713 -> FRM.SHFM8713
3961 if instr (p_cube, '_PRS') > 0 then
3962 if (instr (l_measType, '_CALC')) > 0 then
3963 m_stringMid := instr(l_measFrm, '.');
3964 m_string1 := substr(l_measFrm, 1 , m_stringMid); -- FRM.
3965 m_string2 := substr(l_measFrm, m_stringMid+1, length(l_measFrm)); -- FM.8713
3966 m_string2 := replace(m_string2, 'FM', 'FMT');
3967 l_measFrm := m_string1 || 'CALC.' || m_string2;
3968 else
3969 m_stringRepl:='SH';
3970 if(instr(l_measType, 'DATA_ENTERED')> 0 or instr(l_measType, 'DATA_TRANSLATED')>0) then
3971 m_stringRepl := 'SH.';
3972 end if;
3973 m_stringMid := instr(l_measFrm, '.');
3974 m_string1 := substr(l_measFrm, 1 , m_stringMid); -- FRM.
3975 m_string2 := substr(l_measFrm, m_stringMid+1, length(l_measFrm)); -- FM8713
3976 m_string2 := replace(m_string2, 'WSEC', 'WS');
3977 m_string2 := replace(m_string2, 'AN', 'ANN');
3978 l_measFrm := m_string1 || m_stringRepl || m_string2;
3979 end if;
3980 -- dbms_output.put_line('l_measFrm after :' || l_measFrm);
3981 end if;
3982
3983 l_measName := zpb_aw.interp('shw &joinchars('''||l_awQual||
3984 ''' obj(property ''LDSCVAR'' ''MEASURE''))');
3985
3986 m_meas_en.name := l_measName;
3987 m_meas_en.AWName := l_measFrm;
3988 m_meas_en.EPBId := p_meas;
3989
3990 m_meas_en.CurrencyRel := p_currencyRel;
3991
3992 -- dbms_output.put_line('m_meas_en.EPBId before :' || m_meas_en.EPBId);
3993
3994 -- DF8713 -> DF.SH8713 or FM8713 -> FMT.SH8713
3995 if instr (p_cube, '_PRS') > 0 then
3996 -- for entered and translated mid point is first period
3997 if(instr(p_meas, '.') > 0) then
3998 m_stringMid := instr(p_meas, '.');
3999 else
4000 m_stringMid := instr(p_meas, p_instance);
4001 end if;
4002 m_string1 := substr(p_meas, 1 , m_stringMid-1); -- DF or FM
4003 m_string2 := substr(p_meas, m_stringMid, length(p_meas)); -- 8713
4004 m_string1 := replace(m_string1, 'FM', 'FMT');
4005 m_string1 := replace(m_string1, 'DA', 'AN');
4006 m_string1 := replace(m_string1, 'CALC', 'DF'); -- calcs
4007 m_meas_en.EPBId := m_string1 || '.SH' || m_string2;
4008 -- dbms_output.put_line('m_meas_en.EPBId after :' || m_meas_en.EPBId);
4009 end if;
4010
4011 m_meas_en.Type := l_measType;
4012 m_meas_en.InstanceId := p_instance;
4013
4014 /* Bug#5766644, commented and replaced for if-else-if logic
4015 m_meas_en.CurrInstFlag := upper(zpb_aw.interp('shw '||l_awQual||l_global_ecm.IsCurrInstVar)); */
4016 if zpb_aw.interpbool ('shw '||l_awQual||l_global_ecm.IsCurrInstVar) then
4017 m_meas_en.CurrInstFlag := 'YES';
4018 else
4019 m_meas_en.CurrInstFlag := 'NO';
4020 end if;
4021
4022 if (p_template is not null and l_measType <> 'PERSONAL_INPUT_LEVEL' and
4023 l_measType <> 'PERSONAL_WRITE_SECURITY' and l_measType <> 'PERSONAL_ENTERED_WRITE_SECURITY') then
4024 m_meas_en.TemplateId := to_number(p_template);
4025 end if;
4026
4027 if (p_approvee is not null) then
4028 m_meas_en.ApproveeId := p_approvee;
4029 end if;
4030
4031 if (instr (l_measType, 'SHARED_VIEW') > 0) then
4032 l_type := 'SHARED_VIEW';
4033 elsif (instr (l_measType, 'SHARED') > 0) then
4034 l_type := 'SHARED';
4035 elsif (instr (l_measType, 'PERSONAL') > 0) then
4036 l_type := 'PERSONAL';
4037 elsif (instr (l_measType, 'APPROVER') > 0) then
4038 l_type := 'APPROVER';
4039 else
4040 l_type := l_measType;
4041 end if;
4042
4043 --
4044 -- slight HACK: changing XXX_CALC to XXX_CALC_DATA to make the
4045 -- following algorithm work
4046 --
4047 if (instr (l_measType, '_CALC') = length(l_measType) - 4) then
4048 l_measType := l_measType || '_DATA';
4049 end if;
4050
4051 if instr (p_cube, '_PRS') > 0 then
4052 p_awParam := 'PRS';
4053 else
4054 p_awParam :=p_aw;
4055 end if;
4056
4057 if (instr (l_measType, '_DATA') > 0) then
4058 m_cwm_name := zpb_metadata_names.get_measure_cwm2
4059 (p_awParam,p_instance,l_measType,p_template,p_approvee, p_currencyRel);
4060 elsif (instr (l_measType, '_ANNOTATION') > 0) then
4061 m_cwm_name := zpb_metadata_names.get_measure_annot_cwm2
4062 (p_awParam,p_instance,l_measType,p_template,p_approvee, p_currencyRel);
4063 elsif (instr (l_measType, '_TARGET_TYPE') > 0) then
4064 m_cwm_name := zpb_metadata_names.get_measure_targ_type_cwm2
4065 (p_awParam,p_instance,l_measType,p_template,p_approvee);
4066 elsif (instr (l_measType, '_TARGET') > 0) then
4067 m_cwm_name := zpb_metadata_names.get_measure_target_cwm2
4068 (p_awParam,p_instance,l_measType,p_template,p_approvee);
4069 elsif (instr (l_measType, '_INPUT_LEVEL') > 0) then
4070 m_cwm_name := zpb_metadata_names.get_measure_input_lvl_cwm2
4071 (p_awParam,p_instance,l_measType,p_template,p_approvee);
4072 elsif (instr (l_measType, '_WRITE_SECURITY') > 0) then
4073
4074 if(instr (l_measType, 'PERSONAL_ENTERED') > 0) then
4075 m_cwm_name := zpb_metadata_names.get_measure_cur_write_sec_cwm2
4076 (p_awParam,p_instance,l_measType,p_template,p_approvee);
4077 else
4078 m_cwm_name := zpb_metadata_names.get_measure_write_sec_cwm2
4079 (p_awParam,p_instance,l_measType,p_template,p_approvee);
4080 end if;
4081
4082 elsif (instr (l_measType, '_FORMAT') > 0) then
4083 m_cwm_name := zpb_metadata_names.get_measure_format_cwm2
4084 (p_awParam,p_instance,l_measType,p_template,p_approvee, p_currencyRel);
4085 end if;
4086
4087 m_meas_en.CWMName := m_cwm_name;
4088
4089 -- dbms_output.put_line('m_cwm_name :' || m_cwm_name);
4090 -- dbms_output.put_line('p_cube :' || p_cube);
4091
4092 -- if no metadata exists for this measures cube then something is wrong
4093 -- with this measures measviewrel - most likely its set to a different
4094 -- cube than the instance's data measure's measviewrel
4095
4096 begin
4097
4098 select cube_id, table_id into m_meas_en.cubeId, m_column_en.tableId
4099 from zpb_cubes
4100 where name = p_cube and
4101 bus_area_id = bus_area_id_num;
4102
4103 l_insert_meas_flag := true;
4104
4105 exception
4106 when others then
4107 l_insert_meas_flag := false;
4108
4109 end;
4110
4111 if l_insert_meas_flag = true then
4112
4113 m_column_en.columnName := p_column;
4114 m_column_en.columnType := 'MEASURE_COLUMN';
4115 m_column_en.AWName := '';
4116
4117 m_meas_en.columnId := InsertColumnRecord(m_column_en);
4118
4119 -- populate currency fields if this is a currency-enabled BA
4120
4121 m_meas_en.SelectedCur :='NA';
4122 m_meas_en.CurrencyType := 'NA';
4123
4124 -- if clause will be true if this is a currency-enabled business area
4125 -- only need to expose these currency fields for shared AWs (open-sql access)
4126 if (zpb_aw.interp('shw statlen(limit(SHARED!' || l_global_ecm.DimDim ||
4127 ' to SHARED!' || l_global_ecm.DimTypeRel ||
4128 ' eq ''FROM_CURRENCY''))') <> 0 and instr(p_cube, 'ZPBDATA') > 0) then
4129
4130 m_meas_en.CurrencyType := zpb_aw.interp ('shw shared!instance.currency.type (shared!instance ''' || p_instance || ''')');
4131
4132 -- only set selected currency field if the currency type is SPECIFIED
4133 if m_meas_en.CurrencyType = 'SPECIFIED' then
4134 m_meas_en.SelectedCur := zpb_aw.interp ('shw shared!instance.currency (shared!instance ''' || p_instance || ''')');
4135 end if;
4136 end if;
4137
4138 m_meas_en.CPRMeasure :='NO';
4139 -- set cpr measure flag if it is available
4140 if(l_type='PERSONAL' and zpb_aw.interpbool('shw exists(''' || l_awQual || 'CPRFLAG.DM'')')) then
4141 if zpb_aw.interpbool('shw '||l_awQual||'CPRFLAG.DM') then
4142 m_meas_en.CPRMeasure :='YES';
4143 end if;
4144 end if;
4145
4146 m_meas_id := InsertMeasureRecord(m_meas_en);
4147
4148 else
4149 zpb_log.write_event('zpb_metadata_pkg.build_measure.error',
4150 'No metadata created because no metadata for' ||
4151 'cube ' || p_cube);
4152
4153 end if;
4154
4155
4156 zpb_aw.execute ('pop '||l_awQual||'MEASURE');
4157
4158 zpb_log.write('zpb_metadata.build_measure.end',
4159 'succesfull completion');
4160
4161 EXCEPTION
4162 WHEN OTHERS THEN
4163 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
4164 return;
4165
4166 end BUILD_MEASURE;
4167
4168 -------------------------------------------------------------------------------
4169 -- BUILD - Exposes metadata for an AW
4170 --
4171 -- IN: p_aw - The AW to build the map
4172 -- p_sharedAW - The name of the shared AW. May be the same as p_aw.
4173 -- p_type - Either PERSONAL or SHARED
4174 -- p_doMeas - True if measures should be (re)built
4175 -------------------------------------------------------------------------------
4176 procedure BUILD(p_aw in varchar2,
4177 p_sharedAW in varchar2,
4178 p_type in varchar2,
4179 p_doMeas in varchar2,
4180 p_onlySec in varchar2)
4181 is
4182
4183 l_api_name CONSTANT VARCHAR2(30) := 'BUILD';
4184
4185 l_dims varchar2(500);
4186 l_dim varchar2(32);
4187 l_instances varchar2(16000);
4188 l_instance varchar2(32);
4189 l_measures varchar2(32);
4190 l_meas varchar2(32);
4191 l_templ varchar2(32);
4192 l_approv varchar2(32);
4193 l_aw varchar2(32);
4194 l_subMap varchar2(4000);
4195 l_pos number;
4196 l_pos2 number;
4197 i number;
4198 j number;
4199 hi number;
4200 hj number;
4201 l_gid boolean;
4202 l_hiers varchar2(1000);
4203 l_hier varchar2(30);
4204 l_value varchar2(30);
4205 nl varchar2(1) := fnd_global.local_chr(10);
4206 ai number;
4207 aj number;
4208 done boolean;
4209
4210 l_dim_ecm zpb_ecm.dimension_ecm;
4211 l_global_ecm zpb_ecm.global_ecm;
4212
4213 -- measure entry
4214 m_meas_en zpb_md_records.measures_entry;
4215 m_meas_id number;
4216 m_meas_cube_name varchar2(128);
4217 m_meas_col_name varchar2(128);
4218 m_column_en zpb_md_records.columns_entry;
4219
4220 -- store start time. On completion of refresh all entries
4221 -- with lsat_updated before start date will be removed
4222 m_start_time date;
4223 bus_area_id_num number;
4224
4225 -- "special" table entries
4226 m_table_en zpb_md_records.tables_entry;
4227 m_column_id_num number;
4228
4229 begin
4230
4231 zpb_log.write('zpb_metadata_pkg.build.begin',
4232 'Creating metadata map for '||p_aw);
4233 -- dbms_output.put_line('Creating metadata map for '||p_aw);
4234
4235 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
4236
4237 select sysdate into m_start_time from dual;
4238
4239 zpb_aw.execute ('aw attach '||zpb_aw.get_schema||'.'||p_aw||' first');
4240
4241 l_gid := false;
4242 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
4243 l_global_ecm := zpb_ecm.get_global_ecm(p_aw);
4244
4245 zpb_aw.execute('push oknullstatus '||l_global_ecm.AttrDim);
4246 zpb_aw.execute('oknullstatus = yes');
4247
4248 if p_onlySec='N' then
4249
4250 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
4251
4252 BUILD_DIMS (p_aw, p_sharedAW, p_type, l_dims);
4253
4254 l_instances := zpb_aw.interp('shw CM.GETDIMVALUES('''||l_aw||'INSTANCE'')');
4255 if (p_doMeas = 'Y' and l_instances <> 'NA') then
4256 if(p_type = 'SHARED') then
4257 delete_shared_cubes(p_sharedAW);
4258 end if;
4259 i := 1;
4260 loop
4261 j := instr (l_instances, ' ', i);
4262 if (j = 0) then
4263 l_instance := substr (l_instances, i);
4264 else
4265 l_instance := substr (l_instances, i, j-i);
4266 i := j+1;
4267 end if;
4268
4269 if (p_type = 'SHARED') then
4270 BUILD_INSTANCE (p_aw, l_instance, 'SHARED');
4271 BUILD_INSTANCE (p_aw, l_instance, 'SHARED_VIEW');
4272 else
4273 l_value := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||l_instance||
4274 ''' ''TYPE'')');
4275 if (l_value = 'ANALYST_CALC') then
4276 BUILD_INSTANCE (p_aw, l_instance, 'PERSONAL');
4277 else
4278 --BUILD_INSTANCE (p_aw, l_instance, 'SHARED_VIEW');
4279 zpb_aw.execute ('lmt '||l_aw||'MEASURE to findchars('||l_aw||
4280 'MEASURE ''DF'||l_instance||'.'') eq 1');
4281 l_measures := zpb_aw.interp ('shw CM.GETDIMVALUES ('''||l_aw||
4282 'MEASURE'' yes)');
4283 if (l_measures <> 'NA') then
4284 hi := 1;
4285 loop
4286 hj := instr (l_measures, ' ', hi);
4287 if (hj = 0) then
4288 l_meas := substr (l_measures, hi);
4289 else
4290 l_meas := substr (l_measures, hi, hj-hi);
4291 hi := hj+1;
4292 end if;
4293
4294 l_templ := substr(l_meas, instr(l_meas, '.') + 1);
4295 BUILD_INSTANCE (p_aw, l_instance, 'PERSONAL', l_templ);
4296 exit when hj=0;
4297 end loop;
4298 end if;
4299
4300 zpb_aw.execute ('lmt '||l_aw||'MEASURE to findchars('||l_aw||
4301 'MEASURE ''APPDF'||l_instance||'.'') eq 1');
4302 l_measures := zpb_aw.interp ('shw CM.GETDIMVALUES ('''||l_aw||
4303 'MEASURE'' yes)');
4304 if (l_measures <> 'NA') then
4305 hi := 1;
4306 loop
4307 hj := instr (l_measures, ' ', hi);
4308 if (hj = 0) then
4309 l_meas := substr (l_measures, hi);
4310 else
4311 l_meas := substr (l_measures, hi, hj-hi);
4312 hi := hj+1;
4313 end if;
4314
4315 l_pos := instr(l_meas, '.');
4316 l_pos2 := instr(l_meas, '.', l_pos+1);
4317 l_approv := substr(l_meas, l_pos+1, l_pos2-l_pos-1);
4318 l_templ := substr(l_meas, l_pos2+1);
4319 BUILD_INSTANCE (p_aw, l_instance, 'APPROVER',
4320 l_templ, l_approv);
4321 exit when hj=0;
4322 end loop;
4323 end if;
4324 end if;
4325 end if;
4326 exit when j=0;
4327 end loop;
4328 end if;
4329 cleanOldEntries(m_start_time);
4330 end if; -- only do security flag
4331
4332 --
4333 -- Do the security measures:
4334 --
4335
4336 if (p_type = 'SHARED') then
4337 --
4338 -- Generate the dimension up front, since they are the same for all meas
4339 --
4340 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
4341
4342 BUILD_CUBE(p_aw, zpb_metadata_names.get_security_view(p_aw), l_dims, 'SEC_CUBE');
4343
4344
4345 --
4346 -- Full scope:
4347 --
4348
4349 m_meas_en.name := 'FULL_SCOPE';
4350 m_meas_en.type := 'FULL_SCOPE';
4351 m_meas_en.CWMName := zpb_metadata_names.get_full_scope_cwm2_name(p_aw);
4352 m_meas_cube_name := zpb_metadata_names.get_security_cwm2_cube(p_aw);
4353
4354 m_column_en.columnName := zpb_metadata_names.get_full_scope_column;
4355 m_column_en.columnType := 'FULLSCOPE';
4356 m_column_en.AWName := '';
4357
4358 m_meas_en.AWName :='NA';
4359 m_meas_en.EPBId :='NA';
4360 m_meas_en.InstanceId :=0;
4361 m_meas_en.CurrInstFlag :='NO';
4362 m_meas_en.CurrencyType := 'TempNA';
4363 m_meas_en.SelectedCur := 'TempNA';
4364
4365 select cube_id, table_id into m_meas_en.cubeId, m_column_en.TableId
4366 from zpb_cubes
4367 where name = m_meas_cube_name and
4368 bus_area_id = bus_area_id_num;
4369
4370 m_meas_en.ColumnId := InsertColumnRecord(m_column_en);
4371
4372 m_meas_id := InsertMeasureRecord(m_meas_en);
4373 --
4374 -- Writemap:
4375 --
4376 -- m_meas_en.name := 'WRITEMAP';
4377 -- m_meas_en.type := 'WRITEMAP';
4378 -- m_meas_en.CWMName := zpb_metadata_names.get_writemap_cwm2_name(p_aw);
4379 -- m_meas_cube_name := zpb_metadata_names.get_security_cwm2_cube(p_aw);
4380 --
4381 -- m_column_en.columnName := zpb_metadata_names.get_writemap_column;
4382 -- m_column_en.columnType := 'WRITEMAP';
4383 -- m_column_en.AWName := 'TempNA';
4384
4385 -- m_meas_en.AWName :='NA';
4386 -- m_meas_en.EPBId :='NA';
4387 -- m_meas_en.InstanceId :=0;
4388 -- m_meas_en.CurrInstFlag :='NO';
4389 -- m_meas_en.CurrencyType :='TempNA';
4390 -- m_meas_en.SelectedCur := 'TempNA';
4391
4392 -- select cube_id, table_id into m_meas_en.cubeId, m_column_en.TableId
4393 -- from zpb_cubes
4394 -- where name = m_meas_cube_name and
4395 -- bus_area_id = bus_area_id_num;
4396
4397 -- m_meas_en.columnId := InsertColumnRecord(m_column_en);
4398
4399 -- m_meas_id := InsertMeasureRecord(m_meas_en);
4400
4401 --
4402 -- Redo OWNERMAP:
4403 --
4404 zpb_aw.execute ('oknullstatus=yes');
4405 zpb_aw.execute ('lmt '||l_aw||l_global_ecm.DimDim||' to '||
4406 l_aw||l_global_ecm.IsOwnerDim||' eq yes');
4407 l_dims := zpb_aw.interp ('shw joinchars(joincols(values('||
4408 l_aw||l_global_ecm.DimDim||') '' ''))');
4409 if (l_dims <> 'NA' and l_dims <> ' ') then
4410 l_dims := substr (l_dims, 1, length(l_dims) - 1);
4411 BUILD_OWNERMAP_MEASURE (p_aw, l_dims);
4412 end if;
4413
4414 --
4415 -- Do the Relation view/non-cwm structures
4416 --
4417 -- All annotations table:
4418 --
4419
4420 -- initialize ALL_ANNOTATIONS table
4421 m_table_en.TableName := zpb_metadata_names.get_all_annotations_view(p_aw);
4422 m_table_en.TableType := 'ALL_ANNOTATIONS';
4423
4424 -- initialize column entry for table
4425 m_column_en.TableId := insertTableRecord(m_table_en);
4426
4427 -- ENTRY_COLUMN
4428 m_column_en.ColumnType := 'ENTRY_COLUMN';
4429 m_column_en.ColumnName := 'ENTRY';
4430 m_column_en.AWName := 'ANNENTRY';
4431 m_column_id_num := insertColumnRecord(m_column_en);
4432
4433 -- ANUSER_COLUMN
4434 m_column_en.ColumnType := 'ANUSER_COLUMN';
4435 m_column_en.ColumnName := 'ANUSER';
4436 m_column_en.AWName := 'ANNUSER';
4437 m_column_id_num := insertColumnRecord(m_column_en);
4438
4439 -- TITLE_COLUMN
4440 m_column_en.ColumnType := 'TITLE_COLUMN';
4441 m_column_en.ColumnName := 'TITLE';
4442 m_column_en.AWName := 'TITLE.ANNOT.F';
4443 m_column_id_num := insertColumnRecord(m_column_en);
4444
4445 -- DESCRIPTION_COLUMN
4446 m_column_en.ColumnType := 'DESCRIPTION_COLUMN';
4447 m_column_en.ColumnName := 'DESCRIPTION';
4448 m_column_en.AWName := 'DESC.ANNOT.F';
4449 m_column_id_num := insertColumnRecord(m_column_en);
4450
4451 -- DATA_COLUMN
4452 m_column_en.ColumnType := 'DATA_COLUMN';
4453 m_column_en.ColumnName := 'DATA';
4454 m_column_en.AWName := 'CURRDATA.ANNOT.F';
4455 m_column_id_num := insertColumnRecord(m_column_en);
4456
4457 -- ANDATE_COLUMN
4458 m_column_en.ColumnType := 'ANDATE_COLUMN';
4459 m_column_en.ColumnName := 'ANDATE';
4460 m_column_en.AWName := 'ANNDATE';
4461 m_column_id_num := insertColumnRecord(m_column_en);
4462
4463 -- initialize ALL_PERS_ANNOTATIONS table
4464 m_table_en.TableName := zpb_metadata_names.get_all_annot_pers_view(p_aw);
4465 m_table_en.TableType := 'ALL_PERS_ANNOTATIONS';
4466
4467 -- this table and ALL_ANNOTATIONS table have same columns
4468
4469 -- initialize column entry for table
4470 m_column_en.TableId := insertTableRecord(m_table_en);
4471
4472 -- ENTRY_COLUMN
4473 m_column_en.ColumnType := 'ENTRY_COLUMN';
4474 m_column_en.ColumnName := 'ENTRY';
4475 m_column_en.AWName := 'ANNENTRY';
4476 m_column_id_num := insertColumnRecord(m_column_en);
4477
4478 -- ANUSER_COLUMN
4479 m_column_en.ColumnType := 'ANUSER_COLUMN';
4480 m_column_en.ColumnName := 'ANUSER';
4481 m_column_en.AWName := 'ANNUSER';
4482 m_column_id_num := insertColumnRecord(m_column_en);
4483
4484 -- TITLE_COLUMN
4485 m_column_en.ColumnType := 'TITLE_COLUMN';
4486 m_column_en.ColumnName := 'TITLE';
4487 m_column_en.AWName := 'TITLE.ANNOT.F';
4488 m_column_id_num := insertColumnRecord(m_column_en);
4489
4490 -- DESCRIPTION_COLUMN
4491 m_column_en.ColumnType := 'DESCRIPTION_COLUMN';
4492 m_column_en.ColumnName := 'DESCRIPTION';
4493 m_column_en.AWName := 'DESC.ANNOT.F';
4494 m_column_id_num := insertColumnRecord(m_column_en);
4495
4496 -- DATA_COLUMN
4497 m_column_en.ColumnType := 'DATA_COLUMN';
4498 m_column_en.ColumnName := 'DATA';
4499 m_column_en.AWName := 'CURRDATA.ANNOT.F';
4500 m_column_id_num := insertColumnRecord(m_column_en);
4501
4502 -- ANDATE_COLUMN
4503 m_column_en.ColumnType := 'ANDATE_COLUMN';
4504 m_column_en.ColumnName := 'ANDATE';
4505 m_column_en.AWName := 'ANNDATE';
4506 m_column_id_num := insertColumnRecord(m_column_en);
4507
4508 --
4509 -- Security scope status table
4510 --
4511
4512 m_table_en.TableName := zpb_metadata_names.get_scope_status_view(p_aw);
4513 m_table_en.TableType := 'SCOPE_STATUS';
4514
4515 -- initiaize columns for SCOPE_STATUS table
4516 m_column_en.TableId := insertTableRecord(m_table_en);
4517
4518 -- SECENTITY_COLUMN
4519 m_column_en.ColumnType := 'SECENTITY_COLUMN';
4520 m_column_en.ColumnName := 'SECENTITY';
4521 m_column_en.AWName := l_global_ecm.SecEntityDim;
4522 m_column_id_num := insertColumnRecord(m_column_en);
4523
4524 -- DIMDIM_COLUMN
4525 m_column_en.ColumnType := 'DIMDIM_COLUMN';
4526 m_column_en.ColumnName := 'DIMDIM';
4527 m_column_en.AWName := l_global_ecm.DimDim;
4528 m_column_id_num := insertColumnRecord(m_column_en);
4529
4530 -- SCOPESTAT_COLUMN
4531 m_column_en.ColumnType := 'SCOPESTAT_COLUMN';
4532 m_column_en.ColumnName := 'SCOPESTAT';
4533 m_column_en.AWName := 'SECSCPSTAT.F';
4534 m_column_id_num := insertColumnRecord(m_column_en);
4535
4536 --
4537 -- Data Exception table
4538 --
4539
4540 m_table_en.TableName := zpb_metadata_names.get_data_exception_view(p_aw);
4541 m_table_en.TableType := 'DATA_EXCEPTION';
4542
4543 -- initiaize columns for DATA_EXCEPTION table : currently no columns
4544 m_column_en.TableId := insertTableRecord(m_table_en);
4545
4546 --
4547 -- Solve Input/Output level table
4548 --
4549
4550 m_table_en.TableName := zpb_metadata_names.get_solve_level_table(p_aw);
4551 m_table_en.TableType := 'SOLVE_LEVEL';
4552
4553 -- initiaize columns for SOLVE_LEVEL table : currently no columns
4554 m_column_en.TableId := insertTableRecord(m_table_en);
4555
4556 --
4557 -- To Currency Tables
4558 --
4559
4560 m_table_en.TableName := zpb_metadata_names.get_to_currency_view(p_aw);
4561 m_table_en.TableType := 'TO_CURRENCY';
4562
4563 -- initiaize columns for to currency table
4564 m_column_en.TableId := insertTableRecord(m_table_en);
4565
4566 -- MEMBER_ID COLUMN
4567 m_column_en.ColumnType := 'MEMBER_ID_COLUMN';
4568 m_column_en.ColumnName := 'MEMBER_ID';
4569 m_column_en.AWName := 'NA';
4570 m_column_id_num := insertColumnRecord(m_column_en);
4571
4572 -- MEMBER_CODE COLUMN
4573 m_column_en.ColumnType := 'MEMBER_CODE_COLUMN';
4574 m_column_en.ColumnName := 'MEMBER_CODE';
4575 m_column_en.AWName := 'NA';
4576 m_column_id_num := insertColumnRecord(m_column_en);
4577
4578 -- MEMBER_NAME COLUMN
4579 m_column_en.ColumnType := 'MEMBER_NAME_COLUMN';
4580 m_column_en.ColumnName := 'MEMBER_NAME';
4581 m_column_en.AWName := 'NA';
4582 m_column_id_num := insertColumnRecord(m_column_en);
4583
4584 -- MEMBER_DESC COLUMN
4585 m_column_en.ColumnType := 'MEMBER_DESC_COLUMN';
4586 m_column_en.ColumnName := 'MEMBER_DESC';
4587 m_column_en.AWName := 'NA';
4588 m_column_id_num := insertColumnRecord(m_column_en);
4589
4590 --
4591 -- Exchange Rates View
4592 --
4593
4594 m_table_en.TableName := zpb_metadata_names.get_exch_rates_view(p_aw);
4595 m_table_en.TableType := 'EXCH_RATES';
4596
4597 -- initiaize columns for to currency table
4598 m_column_en.TableId := insertTableRecord(m_table_en);
4599
4600 -- MEMBER_ID COLUMN
4601 m_column_en.ColumnType := 'MEMBER_ID_COLUMN';
4602 m_column_en.ColumnName := 'MEMBER_ID';
4603 m_column_en.AWName := 'NA';
4604 m_column_id_num := insertColumnRecord(m_column_en);
4605
4606 -- MEMBER_CODE COLUMN
4607 m_column_en.ColumnType := 'MEMBER_CODE_COLUMN';
4608 m_column_en.ColumnName := 'MEMBER_CODE';
4609 m_column_en.AWName := 'NA';
4610 m_column_id_num := insertColumnRecord(m_column_en);
4611
4612 -- MEMBER_NAME COLUMN
4613 m_column_en.ColumnType := 'MEMBER_NAME_COLUMN';
4614 m_column_en.ColumnName := 'MEMBER_NAME';
4615 m_column_en.AWName := 'NA';
4616 m_column_id_num := insertColumnRecord(m_column_en);
4617
4618 -- MEMBER_DESC COLUMN
4619 m_column_en.ColumnType := 'MEMBER_DESC_COLUMN';
4620 m_column_en.ColumnName := 'MEMBER_DESC';
4621 m_column_en.AWName := 'NA';
4622 m_column_id_num := insertColumnRecord(m_column_en);
4623
4624 --
4625 -- Exchange Scenario
4626 --
4627
4628 m_table_en.TableName := zpb_metadata_names.get_exch_scenario_view(p_aw);
4629 m_table_en.TableType := 'EXCH_SCENARIO';
4630
4631 -- initiaize columns for to currency table
4632 m_column_en.TableId := insertTableRecord(m_table_en);
4633
4634 -- MEMBER_ID COLUMN
4635 m_column_en.ColumnType := 'MEMBER_ID_COLUMN';
4636 m_column_en.ColumnName := 'MEMBER_ID';
4637 m_column_en.AWName := 'NA';
4638 m_column_id_num := insertColumnRecord(m_column_en);
4639
4640 -- MEMBER_CODE COLUMN
4641 m_column_en.ColumnType := 'MEMBER_CODE_COLUMN';
4642 m_column_en.ColumnName := 'MEMBER_CODE';
4643 m_column_en.AWName := 'NA';
4644 m_column_id_num := insertColumnRecord(m_column_en);
4645
4646 -- MEMBER_NAME COLUMN
4647 m_column_en.ColumnType := 'MEMBER_NAME_COLUMN';
4648 m_column_en.ColumnName := 'MEMBER_NAME';
4649 m_column_en.AWName := 'NA';
4650 m_column_id_num := insertColumnRecord(m_column_en);
4651
4652 -- MEMBER_DESC COLUMN
4653 m_column_en.ColumnType := 'MEMBER_DESC_COLUMN';
4654 m_column_en.ColumnName := 'MEMBER_DESC';
4655 m_column_en.AWName := 'NA';
4656 m_column_id_num := insertColumnRecord(m_column_en);
4657
4658 end if;
4659
4660 zpb_aw.execute('pop oknullstatus '||l_global_ecm.AttrDim);
4661
4662 zpb_log.write('zpb_metadata_pkg.build.end',
4663 'Created metadata map for '||p_aw);
4664
4665 EXCEPTION
4666 WHEN OTHERS THEN
4667 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
4668 return;
4669
4670 end BUILD;
4671
4672 -------------------------------------------------------------------------------
4673 -- BUILD_EXCEPTION_INST - Returns the instance_id for the "fake" excpetion
4674 -- instance. The cube and measure information for
4675 -- this fake instance is never committed to DB but
4676 -- used only in the session in which its created.
4677 --
4678 -- IN: p_sharedAw - The shared AW
4679 -- p_persAw - The personal AW
4680 -- p_instance - The ID of the instance
4681 -- p_name - The name of the measures to be created
4682 -------------------------------------------------------------------------------
4683 procedure BUILD_EXCEPTION_INST(p_sharedAw in varchar2,
4684 p_persAw in varchar2,
4685 p_instance in varchar2,
4686 p_name in varchar2,
4687 p_user_id in varchar2,
4688 p_bus_area_id in varchar2,
4689 p_fake_flag in boolean,
4690 p_start_up_flag in boolean)
4691 is
4692 l_dim varchar2(30);
4693 l_cube varchar2(60);
4694 l_currInst number;
4695 l_hiers varchar2(1000);
4696 l_hier varchar2(30);
4697 hi number;
4698 hj number;
4699 i number;
4700 j number;
4701 l_gid boolean;
4702 l_aw varchar2(30);
4703 l_global_ecm zpb_ecm.global_ecm;
4704 l_dim_ecm zpb_ecm.dimension_ecm;
4705 nl varchar2(1) := fnd_global.local_chr(10);
4706 l_cpr_meas_cube varchar2(64);
4707 l_frmcpr varchar2(16);
4708
4709 -- md table entries
4710 m_cube_en zpb_md_records.cubes_entry;
4711 m_table_en zpb_md_records.tables_entry;
4712 m_cube_dims_en zpb_md_records.cube_dims_entry;
4713 m_column_en zpb_md_records.columns_entry;
4714 m_cube_hier_en zpb_md_records.cube_hier_entry;
4715 m_meas_en zpb_md_records.measures_entry;
4716
4717 m_cube_id number;
4718 m_relation_id number;
4719 m_meas_id number;
4720
4721 x_return_status varchar2(4000);
4722 x_msg_count number;
4723 x_msg_data varchar2(4000);
4724
4725 RETCODE varchar2(4000);
4726 ERRBUF varchar2(4000);
4727 l_msg_count number;
4728
4729 cursor dims is
4730 select DIMENSION_NAME
4731 from ZPB_CYCLE_MODEL_DIMENSIONS
4732 where ANALYSIS_CYCLE_ID = p_instance and
4733 nvl(REMOVE_DIMENSION_FLAG, 'N') <> 'Y'
4734 UNION select 'CAL_PERIODS' from dual;
4735
4736 bus_area_id_num number;
4737
4738 begin
4739
4740 bus_area_id_num := p_bus_area_id;
4741
4742 -- need to do this to avoid a GCC hard-coded schema warning
4743 l_frmcpr := 'FRM';
4744 l_frmcpr := l_frmcpr || '.';
4745 l_frmcpr := l_frmcpr || 'CPR';
4746
4747 -- called at definition time - need to initialize connection as running on
4748 -- AM connection
4749 if p_fake_flag then
4750
4751 ZPB_AW.INITIALIZE_USER (P_API_VERSION => 1.0,
4752 P_INIT_MSG_LIST => FND_API.G_FALSE,
4753 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4754 X_RETURN_STATUS => RETCODE,
4755 X_MSG_COUNT => l_msg_count,
4756 X_MSG_DATA => ERRBUF,
4757 P_USER => to_number(p_user_id),
4758 P_BUSINESS_AREA_ID => bus_area_id_num,
4759 p_attach_readwrite => FND_API.G_FALSE,
4760 p_sync_shared => FND_API.G_FALSE);
4761 end if;
4762
4763 -- called at status-sql generation time - need to run full start-up as running
4764 -- on AM connection and shared personal instances may need to be merged
4765 if p_start_up_flag then
4766
4767 fnd_global.apps_initialize(p_user_id, p_user_id, fnd_global.RESP_APPL_ID);
4768 zpb_security_context.initcontext(p_user_id, p_user_id, sys_context('ZPB_CONTEXT', 'resp_id'),
4769 sys_context('ZPB_CONTEXT', 'session_id'), to_number(p_bus_area_id));
4770
4771 ZPB_PERSONAL_AW.STARTUP(p_api_version=> 1.0,
4772 p_init_msg_list=> FND_API.G_FALSE,
4773 p_commit => FND_API.G_FALSE,
4774 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4775 x_return_status => x_return_status,
4776 x_msg_count => x_msg_count,
4777 x_msg_data => x_msg_data,
4778 p_user => p_user_id,
4779 p_read_only => FND_API.G_TRUE);
4780 end if;
4781
4782
4783 l_global_ecm := zpb_ecm.get_global_ecm (p_persAw);
4784 l_aw := zpb_aw.get_schema||'.'||p_persAw||'!';
4785 l_gid := false;
4786
4787
4788 select CURRENT_INSTANCE_ID
4789 into l_currInst
4790 from ZPB_ANALYSIS_CYCLES
4791 where ANALYSIS_CYCLE_ID = p_instance;
4792
4793 l_cube := zpb_metadata_names.get_exception_meas_cube(p_sharedAw,l_currInst);
4794
4795 m_cube_en.Name := l_cube;
4796 m_cube_en.Type := 'PRSMEASCUBE';
4797 m_cube_en.EpbId :='NA';
4798
4799
4800 -- if called at generate-sql or evaluation time, need to update FRM.CPR formula
4801 -- make sure middle-tier cube points to view of actual processing instance for
4802 -- status-sql generation
4803 if p_fake_flag = false then
4804 -- update FRM.CPR formula
4805 zpb_aw.execute('call SC.EXCEPCPRMOD(''' || p_instance || ''')');
4806
4807 l_cpr_meas_cube := zpb_aw.interp ('shw ' || l_aw || 'measviewrel (' || l_aw || 'measure ''' || l_frmcpr || ''')');
4808 l_cpr_meas_cube := l_cpr_meas_cube || '_PRS';
4809
4810 -- cube table
4811 select table_name into m_table_en.TableName
4812 from zpb_cubes, zpb_tables
4813 where zpb_cubes.name = l_cpr_meas_cube and
4814 zpb_cubes.bus_area_id = bus_area_id_num and
4815 zpb_cubes.table_id = zpb_tables.table_id;
4816
4817 else
4818
4819 m_table_en.TableName := zpb_metadata_names.get_exception_check_tbl(p_sharedAw);
4820
4821 end if;
4822
4823 m_table_en.AwName :='NA';
4824 m_table_en.TableType := 'MEASURE';
4825
4826 m_cube_en.TableId := insertTableRecord(m_table_en);
4827 m_cube_id := insertCubeRecord(m_cube_en);
4828
4829 -- initialize cube-dim relation entry and cube-hier relation entry
4830 m_cube_dims_en.CubeId := m_cube_id;
4831 m_cube_hier_en.CubeId := m_cube_id;
4832
4833 -- initialize column entry for cube table
4834 m_column_en.TableId :=m_cube_en.TableId;
4835
4836 for each in dims loop
4837 l_dim := zpb_aw.interp ('shw lmt('||l_aw||l_global_ecm.DimDim||' to '||
4838 l_aw||l_global_ecm.ExpObjVar||' eq '''||
4839 each.DIMENSION_NAME||''')');
4840
4841 -- set dimension id for cube-dims relation
4842 select dimension_id into m_cube_dims_en.dimensionId
4843 from zpb_dimensions
4844 where epb_id = l_dim and
4845 bus_area_id = bus_area_id_num;
4846
4847 m_column_en.ColumnType :='MEMBER_COLUMN';
4848 m_column_en.ColumnName := zpb_metadata_names.get_dimension_column(l_dim);
4849 m_column_en.AwName := 'NA';
4850
4851 -- insert Column and Reference column_id in cube-dim relation
4852 m_cube_dims_en.ColumnId := insertColumnRecord(m_column_en);
4853
4854 m_relation_id := insertCubeDimsRecord(m_cube_dims_en);
4855
4856 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_dim, p_sharedAw);
4857
4858 if (zpb_aw.interp('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||
4859 ''')') <> '0') then
4860 zpb_aw.execute ('lmt '||l_aw||l_dim_Ecm.HierDim||' to all');
4861 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_aw||
4862 l_dim_Ecm.HierDim||''' yes)');
4863 hi := 1;
4864 loop
4865 hj := instr (l_hiers, ' ', hi);
4866 if (hj = 0) then
4867 l_hier := substr (l_hiers, hi);
4868 else
4869 l_hier := substr (l_hiers, hi, hj-hi);
4870 hi := hj+1;
4871 end if;
4872
4873 -- insert hierarchy columns into cube table
4874 m_column_en.ColumnType :='GID_COLUMN';
4875 m_column_en.ColumnName := zpb_metadata_names.get_dim_gid_column(l_dim, l_hier);
4876 m_column_en.AwName := 'NA';
4877
4878 -- insert Column and Reference column_id in cube-hier relation
4879 m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
4880
4881 -- set hierarchy id for cube-hier relation entry
4882 select hierarchy_id into m_cube_hier_en.hierarchyId
4883 from zpb_hierarchies
4884 where epb_id = l_hier and
4885 dimension_id = m_cube_dims_en.dimensionId;
4886
4887 m_relation_id := insertCubeHierRecord(m_cube_hier_en);
4888
4889 exit when hj=0;
4890 end loop;
4891 elsif (l_gid = false) then
4892 l_gid := true;
4893
4894 m_column_en.ColumnType :='GID_COLUMN';
4895 m_column_en.ColumnName := zpb_metadata_names.get_dim_gid_column;
4896 m_column_en.AwName := 'NA';
4897
4898 -- insert Column and Reference column_id in cube-hier relation
4899 m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
4900
4901 select hierarchy_id into m_cube_hier_en.hierarchyId
4902 from zpb_hierarchies
4903 where dimension_id = m_cube_dims_en.dimensionId and
4904 hier_type = 'NULL';
4905
4906 m_relation_id := insertCubeHierRecord(m_cube_hier_en);
4907
4908 end if;
4909 exit when j=0;
4910 end loop;
4911
4912 -- initialize measure entry
4913 m_meas_en.CubeId := m_cube_id;
4914 m_meas_en.Name := p_name;
4915 m_meas_en.CwmName := zpb_metadata_names.get_exception_meas_cwm2(p_persAw, l_currInst);
4916 m_meas_en.Type := 'EXCEPTION_DATA';
4917 m_meas_en.InstanceId := l_currInst;
4918
4919 m_meas_en.AwName := l_frmcpr;
4920 m_meas_en.CurrencyType :='NA';
4921 m_meas_en.SelectedCur :='NA';
4922 m_meas_en.CurrInstFlag := 'N';
4923 m_meas_en.CPRMeasure :='YES';
4924
4925 -- insert column entry for measure col of cube table
4926 m_column_en.ColumnType :='EXCEPTION_COLUMN';
4927
4928 if p_fake_flag = false then
4929 m_column_en.ColumnName := 'COL_DF_CPR';
4930 else
4931 m_column_en.ColumnName := zpb_metadata_names.get_exception_column;
4932 end if;
4933
4934 m_column_en.AwName := 'NA';
4935
4936 m_meas_en.ColumnId := insertColumnRecord(m_column_en);
4937
4938 -- insert Measure Entry
4939 m_meas_id := insertMeasureRecord(m_meas_en);
4940 end BUILD_EXCEPTION_INST;
4941
4942 -------------------------------------------------------------------------------
4943 -- BUILD_OWNERMAP_MEASURE - Exposes MD for containing cube and ownermap measure
4944 --
4945 -- IN: p_aw - The AW of the ownermap measure
4946 -- p_dims - The dimensions of the ownermap measure
4947 -------------------------------------------------------------------------------
4948 procedure BUILD_OWNERMAP_MEASURE(p_aw in varchar2,
4949 p_dims in varchar2)
4950 is
4951 i number;
4952 j number;
4953 hi number;
4954 hj number;
4955 l_gid boolean;
4956 l_aw varchar2(30);
4957 l_hiers varchar2(1000);
4958 l_hier varchar2(30);
4959 l_dim varchar2(30);
4960 nl varchar2(1) := fnd_global.local_chr(10);
4961 l_dim_ecm zpb_ecm.dimension_ecm;
4962
4963 -- md table entries
4964 m_cube_en zpb_md_records.cubes_entry;
4965 m_table_en zpb_md_records.tables_entry;
4966 m_cube_dims_en zpb_md_records.cube_dims_entry;
4967 m_column_en zpb_md_records.columns_entry;
4968 m_cube_hier_en zpb_md_records.cube_hier_entry;
4969 m_meas_en zpb_md_records.measures_entry;
4970
4971 m_cube_id number;
4972 m_relation_id number;
4973 m_meas_id number;
4974 bus_area_id_num number;
4975 existing_cube number;
4976
4977 begin
4978
4979 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
4980
4981 -- remove existing ownermap cube
4982 begin
4983
4984 select cube_id into existing_cube
4985 from zpb_cubes
4986 where bus_area_id = bus_area_id_num and
4987 name like '%OWNERMAP%';
4988
4989 deleteCubeRecord(existing_cube);
4990
4991 exception
4992 when NO_DATA_FOUND then
4993 null;
4994 end;
4995
4996 l_gid := false;
4997 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
4998 i := 1;
4999
5000 -- initialize cube entry
5001 m_cube_en.Name := zpb_metadata_names.get_ownermap_cwm2_cube(p_aw);
5002 m_cube_en.Type := 'MEASCUBE';
5003 m_cube_en.EpbID :='NA';
5004
5005 -- cube table
5006 m_table_en.TableName := zpb_metadata_names.get_ownermap_view(p_aw);
5007 m_table_en.AwName :='NA';
5008 m_table_en.TableType := 'MEASURE';
5009
5010 m_cube_en.TableId := insertTableRecord(m_table_en);
5011 m_cube_id := insertCubeRecord(m_cube_en);
5012
5013 -- initialize cube-dim relation entry and cube-hier relation entry
5014 m_cube_dims_en.CubeId := m_cube_id;
5015 m_cube_hier_en.CubeId := m_cube_id;
5016
5017 -- initialize column entry for cube table
5018 m_column_en.TableId :=m_cube_en.TableId;
5019
5020 loop
5021 j := instr (p_dims, ' ', i);
5022 if (j = 0) then
5023 l_dim := substr (p_dims, i);
5024 else
5025 l_dim := substr (p_dims, i, j-i);
5026 i := j+1;
5027 end if;
5028 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_dim, p_aw);
5029
5030 -- initialize dimension entry
5031 m_column_en.ColumnName := zpb_metadata_names.get_dimension_column(l_dim);
5032 m_column_en.ColumnType := 'MEMBER_COLUMN';
5033 m_column_en.AWName :='NA';
5034
5035 -- set dimension id for cube-dims relation
5036 select dimension_id into m_cube_dims_en.dimensionId
5037 from zpb_dimensions
5038 where epb_id = l_dim and
5039 bus_area_id = bus_area_id_num;
5040
5041 m_cube_dims_en.ColumnId := insertColumnRecord(m_column_en);
5042
5043 m_relation_id := insertCubeDimsRecord(m_cube_dims_en);
5044
5045 if (zpb_aw.interp('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||''')')
5046 <> '0') then
5047 zpb_aw.execute ('lmt '||l_aw||l_dim_Ecm.HierDim||' to all');
5048 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_aw||
5049 l_dim_Ecm.HierDim||''' yes)');
5050 hi := 1;
5051 loop
5052 hj := instr (l_hiers, ' ', hi);
5053 if (hj = 0) then
5054 l_hier := substr (l_hiers, hi);
5055 else
5056 l_hier := substr (l_hiers, hi, hj-hi);
5057 hi := hj+1;
5058 end if;
5059
5060 -- insert hierarchy columns into cube table
5061 m_column_en.ColumnType :='GID_COLUMN';
5062 m_column_en.ColumnName := zpb_metadata_names.get_dim_gid_column(l_dim, l_hier);
5063 m_column_en.AwName := 'NA';
5064
5065 -- insert Column and Reference column_id in cube-hier relation
5066 m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
5067
5068 -- set hierarchy id for cube-hier relation entry
5069 select hierarchy_id into m_cube_hier_en.hierarchyId
5070 from zpb_hierarchies
5071 where epb_id = l_hier and
5072 dimension_id = m_cube_dims_en.dimensionId;
5073
5074 m_relation_id := insertCubeHierRecord(m_cube_hier_en);
5075 exit when hj=0;
5076 end loop;
5077 elsif (l_gid = false) then
5078 l_gid := true;
5079
5080 m_column_en.ColumnType :='GID_COLUMN';
5081 m_column_en.ColumnName := zpb_metadata_names.get_dim_gid_column;
5082 m_column_en.AwName := 'NA';
5083
5084 -- insert Column and Reference column_id in cube-hier relation
5085 m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
5086
5087 select hierarchy_id into m_cube_hier_en.hierarchyId
5088 from zpb_hierarchies
5089 where dimension_id = m_cube_dims_en.dimensionId and
5090 hier_type = 'NULL';
5091
5092 m_relation_id := insertCubeHierRecord(m_cube_hier_en);
5093
5094 end if;
5095 exit when j=0;
5096 end loop;
5097
5098 -- initialize measure entry
5099 m_meas_en.CubeId := m_cube_id;
5100 m_meas_en.Name := 'OWNERMAP';
5101 m_meas_en.CwmName := zpb_metadata_names.get_ownermap_cwm2_name(p_aw);
5102 m_meas_en.Type := 'OWNERMAP';
5103
5104 m_meas_en.AwName :='NA';
5105 m_meas_en.CurrencyType :='NA';
5106 m_meas_en.SelectedCur :='NA';
5107 m_meas_en.CurrInstFlag := 'N';
5108
5109 -- insert column entry for measure col of cube table
5110 m_column_en.ColumnType :='OWNERMAP';
5111 m_column_en.ColumnName := zpb_metadata_names.get_ownermap_column;
5112 m_column_en.AwName := 'NA';
5113
5114 m_meas_en.ColumnId := insertColumnRecord(m_column_en);
5115
5116 -- insert Measure Entry
5117 m_meas_id := insertMeasureRecord(m_meas_en);
5118
5119 end BUILD_OWNERMAP_MEASURE;
5120
5121 -------------------------------------------------------------------------------
5122 -- REMOVE_INSTANCE - Removes the metadata for a given instance
5123 --
5124 -- IN: p_aw - The AW to build on
5125 -- p_instance - The ID of the instance
5126 -- p_type - The type of the instance (PERSONAL, SHARED_VOEW, etc)
5127 -- p_template - The template ID. Null is not applicable
5128 -- p_approvee - The approvee ID. Null is not applicable
5129 -------------------------------------------------------------------------------
5130 procedure REMOVE_INSTANCE(p_aw in varchar2,
5131 p_instance in varchar2,
5132 p_type in varchar2,
5133 p_template in varchar2,
5134 p_approvee in varchar2)
5135 is
5136 l_instType varchar2(60);
5137 l_objName varchar2(60);
5138 l_meas varchar2(30);
5139 l_names varchar2(500);
5140 l_instance number;
5141 l_count number;
5142 l_dltWriteSec boolean;
5143 begin
5144
5145 -- right now, only delete MD for personal measures, as SHARED_VIEW
5146 -- MD is shared between all users
5147 if(instr(p_type, 'PERSONAL') > 0 or instr(p_type, 'APPROVER') > 0) then
5148
5149 l_instType := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||
5150 ''' ''TYPE'')');
5151 if (instr (l_instType, 'CALC') > 0) then
5152 l_instType := p_type||' CALC';
5153 else
5154 l_instType := p_type;
5155 end if;
5156
5157 l_objName := 'OBJECT ID'' NA ';
5158 if (p_approvee is not null) then
5159 l_objName := l_objName||''''||p_approvee||''' ';
5160 else
5161 l_objName := l_objName||'NA ';
5162 end if;
5163 if (p_template is not null) then
5164 l_objName := l_objName||''''||p_template||'''';
5165 end if;
5166
5167 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
5168 l_instType||' DATA '||l_objName||'''');
5169
5170 delete from zpb_measures
5171 where measure_id in
5172 (select measure_id from zpb_measures, zpb_cubes
5173 where zpb_measures.cube_id = zpb_cubes.cube_id and
5174 zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
5175 zpb_measures.epb_id= l_meas);
5176
5177 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
5178 l_instType||' ANNOTATION '||l_objName||'''');
5179
5180 delete from zpb_measures
5181 where measure_id in
5182 (select measure_id from zpb_measures, zpb_cubes
5183 where zpb_measures.cube_id = zpb_cubes.cube_id and
5184 zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
5185 zpb_measures.epb_id= l_meas);
5186
5187 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
5188 l_instType||' FORMAT '||l_objName||'''');
5189 delete from zpb_measures
5190 where measure_id in
5191 (select measure_id from zpb_measures, zpb_cubes
5192 where zpb_measures.cube_id = zpb_cubes.cube_id and
5193 zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
5194 zpb_measures.epb_id= l_meas);
5195
5196 if (p_type = 'PERSONAL') then
5197 l_instance := to_number(zpb_aw.interp('shw CM.GETPHYSICALINSTANCE('''||
5198 p_instance||''')'));
5199 select count(*)
5200 into l_count
5201 from ZPB_MEASURES
5202 where INSTANCE_ID = l_instance
5203 and TYPE = 'PERSONAL_DATA'
5204 and CUBE_ID in (select CUBE_ID from ZPB_CUBES
5205 where NAME like '%'||p_aw||'/_%' escape '/');
5206 if (l_count > 0) then
5207 l_dltWriteSec := false;
5208 else
5209 l_dltWriteSec := true;
5210 end if;
5211 else
5212 l_dltWriteSec := true;
5213 end if;
5214
5215 if (l_dltWriteSec) then
5216 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
5217 l_instType||' WRITE SECURITY '||l_objName||'''');
5218 delete from zpb_measures
5219 where measure_id in
5220 (select measure_id from zpb_measures, zpb_cubes
5221 where zpb_measures.cube_id = zpb_cubes.cube_id and
5222 zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
5223 zpb_measures.epb_id= l_meas);
5224 end if;
5225
5226 if (p_type = 'PERSONAL' or p_type = 'SHARED') then
5227 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
5228 l_instType||' TARGET '||l_objName||'''');
5229 delete from zpb_measures
5230 where measure_id in
5231 (select measure_id from zpb_measures, zpb_cubes
5232 where zpb_measures.cube_id = zpb_cubes.cube_id and
5233 zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
5234 zpb_measures.epb_id= l_meas);
5235
5236 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
5237 l_instType||' TARGET TYPE '||l_objName||'''');
5238 delete from zpb_measures
5239 where measure_id in
5240 (select measure_id from zpb_measures, zpb_cubes
5241 where zpb_measures.cube_id = zpb_cubes.cube_id and
5242 zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
5243 zpb_measures.epb_id= l_meas);
5244
5245 if (l_dltWriteSec) then
5246 l_meas := zpb_aw.interp ('shw CM.GETINSTOBJECT('''||p_instance||''' '''||
5247 l_instType||' INPUT LEVEL OBJECT ID''');
5248 delete from zpb_measures
5249 where measure_id in
5250 (select measure_id from zpb_measures, zpb_cubes
5251 where zpb_measures.cube_id = zpb_cubes.cube_id and
5252 zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
5253 zpb_measures.epb_id= l_meas);
5254 end if;
5255 end if;
5256 end if;
5257
5258 end REMOVE_INSTANCE;
5259
5260 -------------------------------------------------------------------------------
5261 -- delete_shared_cubes - Procedure deletes all shared and shared "personal" cubes and the
5262 -- contained measures for the specified business area
5263 -------------------------------------------------------------------------------
5264 procedure delete_shared_cubes(p_sharaw in varchar2) is
5265
5266 bus_area_id_num number;
5267
5268 cursor cubes_cursor is
5269 select cube_id
5270 from zpb_cubes
5271 where bus_area_id = bus_area_id_num and
5272 name like p_sharaw || '%';
5273
5274 begin
5275
5276 -- zpb_log.write_error('zpb_metadata_pkg.delete_user',
5277 -- 'Deleteing User '||p_user);
5278
5279 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
5280
5281 for cube in cubes_cursor loop
5282 deleteCubeRecord(cube.cube_id);
5283 end loop;
5284
5285 end delete_shared_cubes;
5286 -------------------------------------------------------------------------------
5287 -- delete_user - Procedure deletes all personal "personal" cubes and the
5288 -- contained measures for the specified user
5289 -------------------------------------------------------------------------------
5290 procedure delete_user(p_user varchar2) is
5291
5292 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_USER';
5293 bus_area_id_num number;
5294
5295 cursor cubes_cursor is
5296 select cube_id
5297 from zpb_cubes
5298 where bus_area_id = bus_area_id_num and
5299 name like p_user || '/_%' escape '/';
5300
5301 begin
5302
5303 -- zpb_log.write_error('zpb_metadata_pkg.delete_user',
5304 -- 'Deleteing User '||p_user);
5305
5306 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
5307
5308 if (p_user is not null) then
5309 for cube in cubes_cursor loop
5310 deleteCubeRecord(cube.cube_id);
5311 end loop;
5312
5313 -- clean user scope tables
5314 delete zpb_hier_scope
5315 where user_id = to_number(p_user) and
5316 hierarchy_id in (select hierarchy_id
5317 from zpb_hierarchies hier,
5318 zpb_dimensions dims
5319 where hier.dimension_id = dims.dimension_id and
5320 dims.bus_area_id = bus_area_id_num);
5321
5322 delete zpb_hier_level_scope
5323 where user_id = to_number(p_user) and
5324 hier_id in (select hierarchy_id
5325 from zpb_hierarchies hier,
5326 zpb_dimensions dims
5327 where hier.dimension_id = dims.dimension_id and
5328 dims.bus_area_id = bus_area_id_num);
5329
5330 delete zpb_attribute_scope
5331 where user_id = to_number(p_user) and
5332 attribute_id in (select attribute_id
5333 from zpb_attributes attr,
5334 zpb_dimensions dims
5335 where attr.dimension_id = dims.dimension_id and
5336 dims.bus_area_id = bus_area_id_num);
5337
5338 end if;
5339
5340 EXCEPTION
5341 WHEN OTHERS THEN
5342 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
5343 return;
5344
5345 end delete_user;
5346
5347 -------------------------------------------------------------------------------
5348 -- BUILD_PERSONAL_DIMS - Updates user's personal scoping for hierarchies, levels,
5349 -- and attributes. Updates user's personal levels.
5350 --
5351 -- IN: p_aw - The AW to build
5352 -- p_sharedAW - The shared AW (may be the same as p_aw)
5353 -- p_type - The AW type (PERSONAL or SHARED)
5354 -- p_dims - Space separated list of dim ID's
5355 -------------------------------------------------------------------------------
5356 procedure BUILD_PERSONAL_DIMS(p_aw in varchar2,
5357 p_sharedAw in varchar2,
5358 p_type in varchar2,
5359 p_dims in varchar2)
5360 is
5361 l_api_name CONSTANT VARCHAR2(30) := 'BUILD_INSTANCE';
5362
5363 l_hiers varchar2(500);
5364 l_levels varchar2(500);
5365 l_attrs varchar2(500);
5366 l_attrId varchar2(30);
5367 l_dim varchar2(32);
5368 l_hier varchar2(32);
5369 l_level varchar2(32);
5370 l_lvlhier varchar2(32);
5371 l_persLvl boolean;
5372 l_attr varchar2(32);
5373 l_aw varchar2(32);
5374 l_viewAw varchar2(32);
5375 i number;
5376 j number;
5377 hi number;
5378 hj number;
5379 li number;
5380 lj number;
5381 ai number;
5382 aj number;
5383 l_length number;
5384 nl varchar2(1) := fnd_global.local_chr(10);
5385
5386 l_global_ecm zpb_ecm.global_ecm;
5387 l_dim_data zpb_ecm.dimension_data;
5388 l_range_dim_data zpb_ecm.dimension_data;
5389 l_dim_ecm zpb_ecm.dimension_ecm;
5390 l_dim_time_ecm zpb_ecm.dimension_time_ecm;
5391 l_global_attr_ecm zpb_ecm.global_attr_ecm;
5392 l_attr_ecm zpb_ecm.attr_ecm;
5393
5394 bus_area_id_num number;
5395
5396 -- language looping
5397 l_langs varchar2(500);
5398 l_lang FND_LANGUAGES.LANGUAGE_CODE%type;
5399 htld_i number;
5400 htld_j number;
5401
5402 l_dimension_id number;
5403
5404 l_user_id number;
5405 l_hier_scope_en zpb_md_records.hier_scope_entry;
5406 l_hier_perscwm varchar2(60);
5407
5408 l_hl_scope_en zpb_md_records.hier_level_scope_entry;
5409
5410 l_level_perscwm varchar2(60);
5411 l_attr_perscwm varchar2(60);
5412
5413 l_pers_hier_table_name varchar2(60);
5414
5415 l_attr_scope_en zpb_md_records.attribute_scope_entry;
5416
5417 l_dummy_num number;
5418
5419 l_hr_pers_table_en zpb_md_records.tables_entry;
5420 l_level_en zpb_md_records.levels_entry;
5421 l_pers_column_en zpb_md_records.columns_entry;
5422 l_level_tl_en zpb_md_records.levels_tl_entry;
5423 l_level_or_str varchar2(16);
5424
5425 l_start_time date;
5426 l_accessToAHier boolean;
5427 l_nullHierCnt number;
5428
5429 -- cursor for scoping special attributes
5430 cursor c_special_attrs is
5431 select attribute_id
5432 from zpb_attributes
5433 where dimension_id = l_dimension_id and
5434 type in ('TIMESPAN_ATTRIBUTE', 'ENDDATE_ATTRIBUTE', 'SHORT_VALUE_NAME_ATTRIBUTE', 'VALUE_NAME_ATTRIBUTE');
5435
5436 -- cursor for scoping null hierarchies of range dimensions
5437 cursor c_range_dim_null_hiers is
5438 select hier.hierarchy_id, hier.pers_table_id
5439 from zpb_hierarchies hier,
5440 zpb_dimensions dims,
5441 zpb_attributes attr
5442 where dims.bus_area_id = bus_area_id_num and
5443 dims.dimension_id = attr.range_dim_id and
5444 dims.dimension_id = hier.dimension_id;
5445
5446 -- cursor for scoping level of null hierarchies of range dimensions
5447 cursor c_range_dim_null_hier_levels is
5448 select hl.hier_id, hl.level_id, hl.pers_col_id
5449 from zpb_hier_level hl,
5450 zpb_dimensions dims,
5451 zpb_attributes attr,
5452 zpb_hierarchies hier
5453 where dims.bus_area_id = bus_area_id_num and
5454 dims.dimension_id = attr.range_dim_id and
5455 dims.dimension_id = hier.dimension_id and
5456 hl.hier_id = hier.hierarchy_id;
5457
5458 -- cursor for scoping attributes of personal range dimensions
5459 cursor c_range_dim_attrs is
5460 select attr2.attribute_id
5461 from zpb_dimensions dims,
5462 zpb_attributes attr,
5463 zpb_attributes attr2
5464 where dims.bus_area_id = bus_area_id_num and
5465 dims.dimension_id = attr.range_dim_id and
5466 dims.dimension_id = attr2.dimension_id;
5467
5468 begin
5469 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
5470 l_global_ecm := zpb_ecm.get_global_ecm (p_aw);
5471 l_global_attr_ecm := zpb_ecm.get_global_attr_ecm(p_aw);
5472
5473 -- save start time. At the end we will remove all scoping entries that were
5474 -- created prior to this time, as they are no longer valid
5475 select sysdate into l_start_time from dual;
5476
5477 l_user_id := to_number(sys_context('ZPB_CONTEXT', 'shadow_id'));
5478
5479 bus_area_id_num := sys_context('ZPB_CONTEXT', 'business_area_id');
5480
5481 zpb_aw.execute('push oknullstatus');
5482 zpb_aw.execute('oknullstatus = yes');
5483
5484 -- Loop over all dimensions
5485 i := 1;
5486 loop
5487 j := instr (p_dims, ' ', i);
5488 if (j = 0) then
5489 l_dim := substr (p_dims, i);
5490 else
5491 l_dim := substr (p_dims, i, j-i);
5492 i := j+1;
5493 end if;
5494
5495
5496 -- flag tracks whether user has access to at least one hierarchy for each dimension
5497 l_accessToAHier:= false;
5498
5499 select dimension_id into l_dimension_id
5500 from zpb_dimensions
5501 where epb_id = l_dim and
5502 bus_area_id = bus_area_id_num;
5503
5504 -- Reset all of the personal personal hierarchy table id's to the personal
5505 -- hierarchy tables. In the case that the user still has personal levels
5506 -- they will be set appropriately back to the personal personal hierarchy tables below.
5507 update zpb_hier_scope hscope
5508 set hscope.pers_table_id = (select pers_table_id
5509 from zpb_hierarchies hier
5510 where hscope.hierarchy_id = hier.hierarchy_id)
5511 where hscope.user_id = l_user_id and
5512 hscope.user_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
5513 hscope.hierarchy_id in (select hierarchy_id
5514 from zpb_hierarchies hier,
5515 zpb_dimensions dims
5516 where hier.dimension_id = dims.dimension_id and
5517 dims.bus_area_id = bus_area_id_num and
5518 dims.dimension_id = l_dimension_id);
5519
5520 l_dim_data := zpb_ecm.get_dimension_data (l_dim, p_aw);
5521 l_dim_ecm := zpb_ecm.get_dimension_ecm (l_dim, p_aw);
5522
5523 -- see if we need to later create a scope entry for null hierarchy of this dimension
5524 select count(hierarchy_id) into l_nullHierCnt
5525 from zpb_hierarchies
5526 where dimension_id=l_dimension_id and hier_type='NULL';
5527
5528 zpb_aw.execute('push '||l_aw||l_dim_data.ExpObj);
5529
5530 if (zpb_aw.interp('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||
5531 ''')') <> '0') then
5532 hi := 1;
5533 l_hiers :=zpb_aw.interp
5534 ('shw CM.GETDIMVALUES('''||l_aw||l_dim_ecm.HierDim||''')');
5535
5536 zpb_aw.execute ('push '||l_aw||l_dim_ecm.LevelDim);
5537 zpb_aw.execute ('push '||l_aw||l_dim_ecm.HierDim);
5538 loop -- LOOP OVER ALL HIERARCHIES
5539 hj := instr (l_hiers, ' ', hi);
5540 if (hj = 0) then
5541 l_hier := substr (l_hiers, hi);
5542 else
5543 l_hier := substr (l_hiers, hi, hj-hi);
5544 hi := hj+1;
5545 end if;
5546
5547 l_persLvl:=false;
5548
5549 zpb_aw.execute('lmt '||l_aw||l_dim_ecm.HierDim||' to '''||
5550 l_hier||'''');
5551 -- dbms_output.put_line('looking at hierarchy ' || l_hier);
5552
5553 zpb_aw.execute('lmt '||l_aw||l_dim_data.ExpObj||' to '||l_aw||
5554 l_dim_ecm.HOrderVS);
5555 if (zpb_aw.interp('shw convert(statlen('||l_aw||l_dim_data.ExpObj||
5556 ') text 0 no no)') <> 0) then
5557
5558 l_accessToAHier:=true;
5559
5560 -- add scope for this hierarchy
5561 l_hier_scope_en.UserId := l_user_id;
5562 l_hier_perscwm := zpb_metadata_names.get_hierarchy_cwm2_name('PRS', l_dim, l_hier);
5563
5564 select hierarchy_id, pers_table_id
5565 into l_hier_scope_en.HierarchyId, l_hier_scope_en.PersTableId
5566 from zpb_hierarchies
5567 where dimension_id = l_dimension_id and
5568 pers_cwm_name = l_hier_perscwm;
5569
5570 l_dummy_num := insertHierScopeRecord(l_hier_scope_en);
5571
5572 zpb_aw.execute('lmt '||l_aw||l_dim_ecm.LevelDim||
5573 ' to &joinchars ('''||l_aw||''' obj(property '''
5574 ||'HIERLEVELVS'' '''||l_dim_ecm.HierDim||'''))');
5575 zpb_aw.execute('sort '||l_aw||l_dim_ecm.levelDim||
5576 ' a &joinchars ('''||l_aw||''' obj(property '''
5577 ||'LEVELDEPTHVAR'' '''||l_dim_ecm.HierDim||'''))');
5578
5579 --
5580 -- Get the Levels:
5581 --
5582 li := 1;
5583 l_levels := zpb_aw.interp('shw CM.GETDIMVALUES('''||l_aw||
5584 l_dim_ecm.LevelDim||''', YES)');
5585
5586 loop -- Loop over all levels for hierarchy
5587 lj := instr (l_levels, ' ', li);
5588 if (lj = 0) then
5589 l_level := substr (l_levels, li);
5590 else
5591 l_level := substr (l_levels, li, lj-li);
5592 li := lj+1;
5593 end if;
5594
5595 zpb_aw.execute('lmt '||l_aw||l_dim_ecm.LevelDim||' to '''||
5596 l_level||'''');
5597
5598 -- dbms_output.put_line('Looking at level: ' || l_level);
5599
5600 --
5601 -- Check to see if any members are at this level:
5602 --
5603 zpb_aw.execute('lmt '||l_aw||l_dim_data.ExpObj||' to '||
5604 l_aw||l_dim_ecm.HOrderVS);
5605
5606 zpb_aw.execute ('lmt '||l_aw||l_dim_data.ExpObj||' keep '||
5607 l_aw||l_dim_ecm.LevelRel);
5608 l_length := to_number(zpb_aw.interp('shw convert(statlen ('||
5609 l_aw||l_dim_data.ExpObj||') text 0 no no)'));
5610 if (l_length > 0) then
5611
5612 -- dbms_output.put_line('Level has members');
5613
5614 l_lvlHier := l_hier;
5615 l_level_perscwm := zpb_metadata_names.get_level_cwm2_name('PRS', l_dim, l_lvlHier, l_level);
5616
5617 -- Although we are calling the insert commands for the personal personal table and
5618 -- the personal column records, we expect them to already exist, and are just using
5619 -- the insert helper functions to get information back (ids of the table and columns)
5620 l_hr_pers_table_en.TableName := zpb_metadata_names.get_dimension_view
5621 (p_aw, 'PERSONAL', l_dim, l_hier);
5622 l_hr_pers_table_en.AwName := p_aw;
5623 l_hr_pers_table_en.BusAreaId := bus_area_id_num;
5624 l_hr_pers_table_en.TableType :='HIERARCHY';
5625 l_pers_column_en.TableId:= insertTableRecord(l_hr_pers_table_en);
5626
5627 l_pers_column_en.columnType :='LEVEL_COLUMN';
5628 l_pers_column_en.columnName := zpb_metadata_names.get_level_column(l_dim, l_level);
5629
5630 l_hl_scope_en.PersColId := insertColumnRecord(l_pers_column_en);
5631
5632 -- dbms_output.put_line('About to check personal');
5633 -- Personal Level Check
5634 if zpb_aw.interpbool('shw '|| l_aw||l_dim_ecm.LevelPersVar) then
5635 -- dbms_output.put_line('Pers Level');
5636 -- personal level, need to create MD for the level itself and for the personal personal hierarchy table
5637 l_persLvl :=true;
5638
5639 -- update the zpb_hier_scope entry for this hierarchy to use pesonal personal hierarchy table
5640 update zpb_hier_scope
5641 set pers_table_id = l_pers_column_en.TableId
5642 where user_id = l_user_id and
5643 resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
5644 hierarchy_id = l_hier_scope_en.HierarchyId;
5645
5646 end if;
5647
5648 -- add a scoping entry
5649 l_hl_scope_en.HierId := l_hier_scope_en.HierarchyId;
5650
5651 select level_id into l_hl_scope_en.LevelId
5652 from zpb_levels
5653 where pers_cwm_name = l_level_perscwm and
5654 dimension_id = l_dimension_id;
5655
5656 l_hl_scope_en.UserId := l_user_id;
5657 l_dummy_num := insertHierlevelScopeRecord(l_hl_scope_en);
5658
5659 end if; -- end of members at level if
5660
5661 exit when lj = 0;
5662 end loop; -- End level loop
5663
5664 -- Run Query that will update zpb_hier_level_scope pers_col_id entries
5665 -- for all levels of hierarchy for this user back to the pers_col_id column
5666 -- of zpb_hier_level if user had no personal levels for this hierarchy
5667 if l_persLvl = false then
5668
5669 update zpb_hier_level_scope hlevscope
5670 set hlevscope.pers_col_id = (select pers_col_id from zpb_hier_level hlev
5671 where hlevscope.hier_id = hlev.hier_id and
5672 hlevscope.level_id = hlev.level_id)
5673 where hlevscope.user_id = l_user_id and
5674 hlevscope.resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
5675 hlevscope.hier_id = l_hier_scope_en.HierarchyId;
5676 end if;
5677
5678 end if; -- Does this hierarchy have members check
5679
5680 exit when hj = 0;
5681 end loop; -- End looping over Hierarchies
5682
5683 zpb_aw.execute ('pop '||l_aw||l_dim_ecm.LevelDim);
5684 zpb_aw.execute ('pop '||l_aw||l_dim_ecm.HierDim);
5685
5686 end if;
5687
5688 -- create scope entry for null hierarchy when one exists
5689 if l_nullHierCnt=1 then
5690 zpb_aw.execute('lmt '||l_aw||l_dim_data.ExpObj||' to all');
5691 if (zpb_aw.interp('shw convert(statlen('||l_aw||l_dim_data.ExpObj||
5692 ') text 0 no no)') <> 0) then
5693
5694 select hierarchy_id, pers_table_id
5695 into l_hier_scope_en.HierarchyId, l_hier_scope_en.PersTableId
5696 from zpb_hierarchies
5697 where dimension_id = l_dimension_id and
5698 hier_type='NULL';
5699
5700 l_hier_scope_en.UserId := l_user_id;
5701
5702 l_dummy_num := insertHierScopeRecord(l_hier_scope_en);
5703
5704 select hier_id, level_id, pers_col_id
5705 into l_hl_scope_en.HierId, l_hl_scope_en.LevelId, l_hl_scope_en.PersColId
5706 from zpb_hier_level
5707 where hier_id = l_hier_scope_en.HierarchyId;
5708
5709 l_hl_scope_en.UserId := l_user_id;
5710
5711 l_dummy_num := insertHierlevelScopeRecord(l_hl_scope_en);
5712
5713 l_accessToAHier := true;
5714
5715 end if; -- any members for dim?
5716
5717 end if; -- need for null hier
5718
5719 -- access to at least one hierarchy
5720 if l_accessToAHier = false then
5721 ZPB_ERROR_HANDLER.REGISTER_WARNING
5722 (G_PKG_NAME,
5723 l_api_name,
5724 'ZPB_STARTUP_NOHIER_WARN', 'DIM', l_dim_data.Sdsc );
5725 -- dbms_output.put_line('ZPB_STARTUP_NOHIER_WARN : ' || l_dim_data.Sdsc);
5726 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name, 'ZPB_STARTUP_NOHIER_WARN : ' || l_dim_data.Sdsc);
5727 end if;
5728
5729
5730 -- give scope to user to the null hierarchies of all attribute range dimensions
5731 for hier in c_range_dim_null_hiers loop
5732 l_hier_scope_en.UserId := l_user_id;
5733 l_hier_scope_en.HierarchyId := hier.hierarchy_id;
5734 l_hier_scope_en.PersTableId := hier.pers_table_id;
5735 l_dummy_num := insertHierScopeRecord(l_hier_scope_en);
5736 end loop;
5737
5738 -- give scope to user to the levels of null hierarchies of all attribute range dimensions
5739 for hierlev in c_range_dim_null_hier_levels loop
5740 l_hl_scope_en.UserId := l_user_id;
5741 l_hl_scope_en.HierId := hierlev.hier_id;
5742 l_hl_scope_en.LevelId := hierlev.level_id;
5743 l_hl_scope_en.PersColId := hierlev.pers_col_id;
5744 l_dummy_num := insertHierlevelScopeRecord(l_hl_scope_en);
5745 end loop;
5746
5747 -- DIMENSION ATTRIBUTES
5748 zpb_aw.execute('lmt '||l_aw||l_global_ecm.AttrDim||' to '||l_aw||
5749 l_global_attr_ecm.DomainDimRel||' eq lmt ('||l_aw||
5750 l_global_ecm.DimDim||' to '''||l_dim||''')');
5751
5752 l_attrs := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_aw||
5753 l_global_ecm.AttrDim||''' YES)');
5754
5755 if (l_attrs <> 'NA') then
5756 ai := 1;
5757 loop -- Loop over Attributes of the Dimension
5758 aj := instr (l_attrs, ' ', ai);
5759 if (aj = 0) then
5760 l_attr := substr (l_attrs, ai);
5761 else
5762 l_attr := substr (l_attrs, ai, aj-ai);
5763 ai := aj+1;
5764 end if;
5765
5766 zpb_aw.execute ('lmt '||l_aw||l_global_ecm.AttrDim||' to '''|| l_attr||'''');
5767 -- explicitly exclude timespan and non displayed attrs
5768 if (instr (l_attr, 'TIMESPAN') = 0 and (zpb_aw.interpbool('shw exists(''' || l_aw || l_global_attr_ecm.AttrDisplayVar || ''')')
5769 and zpb_aw.interpbool('shw '||l_aw|| l_global_attr_ecm.AttrDisplayVar))) then
5770
5771 l_attr_perscwm := zpb_metadata_names.get_attribute_cwm2_name('PRS',l_dim,l_attr);
5772
5773 select attribute_id into l_attr_scope_en.AttributeId
5774 from zpb_attributes
5775 where dimension_id = l_dimension_id and
5776 pers_cwm_name = l_attr_perscwm;
5777
5778 l_attr_scope_en.UserId := l_user_id;
5779 l_dummy_num := insertAttributeScopeRecord(l_attr_scope_en);
5780
5781 end if;
5782
5783 exit when aj = 0;
5784 end loop; -- End Looping Over Attributes
5785 end if;
5786
5787 -- handle special attributes
5788 for attr in c_special_attrs loop
5789 l_attr_scope_en.UserId := l_user_id;
5790 l_attr_scope_en.AttributeId := attr.attribute_id;
5791 l_dummy_num := insertAttributeScopeRecord(l_attr_scope_en);
5792 end loop;
5793
5794 for attr in c_range_dim_attrs loop
5795 l_attr_scope_en.UserId := l_user_id;
5796 l_attr_scope_en.AttributeId := attr.attribute_id;
5797 l_dummy_num := insertAttributeScopeRecord(l_attr_scope_en);
5798 end loop;
5799
5800 -- delete all scoping entries for this user that have not been updated during the above looping
5801 -- they must have been created previously and are no longer valid
5802 delete zpb_hier_scope
5803 where user_id = l_user_id and
5804 resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
5805 last_update_date < l_start_time and
5806 hierarchy_id in (select hierarchy_id
5807 from zpb_hierarchies hier,
5808 zpb_dimensions dims
5809 where hier.dimension_id = dims.dimension_id and
5810 dims.bus_area_id = bus_area_id_num and
5811 dims.dimension_id = l_dimension_id);
5812
5813 delete zpb_hier_level_scope
5814 where user_id = l_user_id and
5815 resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
5816 last_update_date < l_start_time and
5817 hier_id in (select hierarchy_id
5818 from zpb_hierarchies hier,
5819 zpb_dimensions dims
5820 where hier.dimension_id = dims.dimension_id and
5821 dims.bus_area_id = bus_area_id_num and
5822 dims.dimension_id = l_dimension_id);
5823
5824 delete zpb_attribute_scope
5825 where user_id = l_user_id and
5826 resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
5827 last_update_date < l_start_time and
5828 attribute_id in (select attribute_id
5829 from zpb_attributes attr,
5830 zpb_dimensions dims
5831 where attr.dimension_id = dims.dimension_id and
5832 dims.bus_area_id = bus_area_id_num and
5833 dims.dimension_id = l_dimension_id);
5834
5835 exit when j=0;
5836 end loop;
5837
5838 zpb_aw.execute('pop oknullstatus');
5839
5840 EXCEPTION
5841 WHEN OTHERS THEN
5842 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
5843 return;
5844
5845 end BUILD_PERSONAL_DIMS;
5846
5847 -------------------------------------------------------------------------------
5848 -- cleanOutBusinessArea - Procedure that deletes all md records for a
5849 -- particular business area. Done before a universe
5850 -- refresh.
5851 -------------------------------------------------------------------------------
5852 procedure cleanBusArea(p_bus_area_id in number) is
5853
5854 CURSOR c_dimensions is
5855 select dimension_id
5856 from zpb_dimensions
5857 where bus_area_id = p_bus_area_id;
5858
5859 v_dim c_dimensions%ROWTYPE;
5860
5861 CURSOR c_tables is
5862 select table_id
5863 from zpb_tables
5864 where bus_area_id = p_bus_area_id;
5865
5866 v_table c_tables%ROWTYPE;
5867
5868 CURSOR c_cubes is
5869 select cube_id
5870 from zpb_cubes
5871 where bus_area_id = p_bus_area_id;
5872
5873 v_cube c_cubes%ROWTYPE;
5874
5875 begin
5876
5877 -- loop over dimensions
5878 for v_dim in c_dimensions loop
5879
5880 -- delete dimensions_tl
5881 delete zpb_dimensions_tl
5882 where dimension_id = v_dim.dimension_id;
5883
5884 -- delete attribute_scope
5885 delete zpb_attribute_scope
5886 where attribute_id in (select attribute_id
5887 from zpb_attributes
5888 where dimension_id = v_dim.dimension_id);
5889
5890 -- delete attr_table_col
5891 delete zpb_attr_table_col
5892 where attribute_id in (select attribute_id
5893 from zpb_attributes
5894 where dimension_id = v_dim.dimension_id);
5895
5896 -- delete attributes_tl
5897 delete zpb_attributes_tl
5898 where attribute_id in (select attribute_id
5899 from zpb_attributes
5900 where dimension_id = v_dim.dimension_id);
5901
5902 -- Finally delete attributes
5903 delete zpb_attributes
5904 where dimension_id = v_dim.dimension_id;
5905
5906 -- delete hierarchies_tl
5907 delete zpb_hierarchies_tl
5908 where hierarchy_id in (select hierarchy_id
5909 from zpb_hierarchies
5910 where dimension_id = v_dim.dimension_id);
5911
5912 -- delete hier_level
5913 delete zpb_hier_level
5914 where hier_id in (select hierarchy_id
5915 from zpb_hierarchies
5916 where dimension_id = v_dim.dimension_id);
5917
5918 -- delete hier_level_scope
5919 delete zpb_hier_level_scope
5920 where hier_id in (select hierarchy_id
5921 from zpb_hierarchies
5922 where dimension_id = v_dim.dimension_id);
5923
5924 -- delete hier_scope
5925 delete zpb_hier_scope
5926 where hierarchy_id in (select hierarchy_id
5927 from zpb_hierarchies
5928 where dimension_id = v_dim.dimension_id);
5929
5930 -- Finally delete hierarchies entry
5931 delete zpb_hierarchies
5932 where dimension_id = v_dim.dimension_id;
5933
5934 -- delete levels_tl
5935 delete zpb_levels_tl
5936 where level_id in (select level_id
5937 from zpb_levels
5938 where dimension_id = v_dim.dimension_id);
5939
5940 -- Finally delete levels
5941 delete zpb_levels
5942 where dimension_id = v_dim.dimension_id;
5943
5944 -- Finally delete dimension
5945 delete zpb_dimensions
5946 where dimension_id = v_dim.dimension_id;
5947 end loop;
5948
5949 for v_table in c_tables loop
5950
5951 -- delete columns
5952 delete zpb_columns
5953 where table_id = v_table.table_id;
5954
5955 -- delete tables
5956 delete zpb_tables
5957 where table_id = v_table.table_id;
5958
5959 end loop;
5960
5961 for v_cube in c_cubes loop
5962
5963 -- delete cube and its child entries
5964 deleteCubeRecord(v_cube.cube_id);
5965
5966 end loop;
5967
5968
5969 end cleanBusArea;
5970
5971 end ZPB_METADATA_PKG;