1 package edw_alter_indexes AUTHID CURRENT_USER AS
2 /* $Header: EDWINDXS.pls 115.5 2002/12/05 23:01:36 arsantha noship $*/
3
4
5 Procedure alterIndexes( errbuf in varchar2, retcode in number, p_fact_name in varchar2 default null);
6
7 TYPE indexInfo is RECORD(
8 columnName VARCHAR2(50),
9 indexName VARCHAR2(50));
10 TYPE tab_indexes is TABLE of indexInfo
11 INDEX BY BINARY_INTEGER;
12
13 CURSOR c_fact_fks(p_fact_name varchar2) IS
14 select
15 'N' name,
16 'N' skip
17 from dual;
18
19 /*
20 CURSOR c_fact_fks(p_fact_name varchar2) IS
21 select
22 item.name,
23 'N' skip
24 from
25 cmpwbcube_v fact,
26 cmpforeignkey_v fk,
27 cmpwbitemsetusage_v isu,
28 cmpitem_v item
29 where
30 fact.name=p_fact_name
31 and fk.owningrelation=fact.elementid
32 and isu.itemset=fk.elementid
33 and item.elementid=isu.attribute
34 and not exists
35 (select 1
36 from cmpitemset_v sis,
37 cmpwbitemsetusage_v pisu
38 where sis.owningrelation=fact.elementid
39 and sis.name='SKIP_LOAD_SET'
40 and sis.disabled=0
41 and pisu.itemset=sis.elementid
42 and item.elementid=pisu.attribute)
43 UNION ALL
44 select item.name,
45 'Y' skip
46 from cmpitemset_v sis,
47 cmpwbitemsetusage_v isu,
48 cmpitem_v item,
49 cmpwbcube_v rel
50 where rel.name=p_fact_name
51 and sis.owningrelation=rel.elementid
52 and sis.name='SKIP_LOAD_SET'
53 and sis.disabled=0
54 and isu.itemset=sis.elementid
55 and item.elementid=isu.attribute;
56 */
57
58 TYPE tab_fact_fks is TABLE of c_fact_fks%ROWTYPE
59 INDEX BY BINARY_INTEGER;
60
61 g_indexes tab_indexes ;
62
63 end edw_alter_indexes;