REM Copyright (c) 2002, 2010, Oracle and/or its affiliates. 
REM All rights reserved. 
REM
REM    NAME
REM      factview.sql
REM
REM    DESCRIPTION
REM
REM    NOTES
REM
REM    MODIFIED   (MM/DD/YY)
REM    awesley     10/05/09 - Bug 8915150 - TT11.2.0.2SQLINJ: POTENTIAL SQL INJECTIONdbms_assert.simple_sql_name

REM *************************************************************
CREATE OR REPLACE PACKAGE BODY OlapFactView AS

PROCEDURE CreateFactTab(exOwnerName IN varchar2,exCubeName IN varchar2,eOutFile IN varchar2,eOutPath IN varchar2,partitioning IN BOOLEAN,mvspcName IN varchar2 default null,indspcName IN varchar2 default null) IS

-- This it the Procedure to Create Cube MV in ROLLUP form for a Cube. This procedure is used to write to a file
-- To run it do the following


 filehandle UTL_FILE.FILE_TYPE;
    eOwnerName varchar2(30);
    eCubeName varchar2(30);
 		eMVName varchar2(30);
--		eOutPath varchar2(30);
--		eOutFile varchar2(30);
    eDimName varchar2(30);
    eHierName varchar2(30);
    eLevelName varchar2(30);
    eLevelColName varchar2(30);
    eLeavColName varchar2(30);
    eFactName varchar2(30);
    eUnitName varchar2(30);
    ePosition NUMBER;
    eTempMV varchar2(30);
    eTabName varchar2(30);
    eTableName varchar2(30);
    eparLevelName varchar2(100);
    parTabName varchar2(30);
    parColName varchar2(30);
    dimCount INTEGER;
    i INTEGER;
    j INTEGER;
    k INTEGER;
    m INTEGER;
    n INTEGER;
    o INTEGER;
    z INTEGER;
    c NUMBER;
    flag INTEGER;
    numOfMv NUMBER;
    numOfdimMV NUMBER;
    tempMVnum INTEGER;
    highLvlCount INTEGER;
    highLvlNum INTEGER;
    tableCount INTEGER;
  	tempName varchar2(30);
  	bufString varchar2(32767);
  	indexString varchar2(32767);
  	parentString varchar2(300);
  	childString varchar2(300);
  	mvString varchar2(32767);
		mvnum INTEGER;
		num_rows NUMBER;
		num_bytes NUMBER;
		partgid NUMBER;
		partnum NUMBER;
		tempnum NUMBER;
		tpnum NUMBER;
		val NUMBER;
		tkval NUMBER;
		times NUMBER;
		ntimes NUMBER;
		loopval NUMBER;
		tempval NUMBER;
		tpval NUMBER;
		query_length NUMBER;
		whereFlag BOOLEAN;
		dupmvFlag BOOLEAN;
		dupdimmvFlag BOOLEAN;
		others EXCEPTION;
    partition BOOLEAN;
    poscount NUMBER;

		TYPE dimType IS RECORD
		(dimName varchar2(30),
		 hierCount  INTEGER);

    TYPE dimHiertype IS RECORD
	   (dimName   varchar2(30),
	   hierName   varchar2(30),
	   hierNum    INTEGER,
	   dimNum     INTEGER,
	   levelCount INTEGER
       );

 		TYPE dimhierList_t is TABLE of dimHiertype;



--		TYPE levelName_t IS TABLE OF VARCHAR2(30)
--	   INDEX BY BINARY_INTEGER;

--  	TYPE levelPos_t IS TABLE OF VARCHAR2(30)
--	   INDEX BY BINARY_INTEGER;

 		TYPE hierLeveltype is RECORD
	   (dimName varchar2(30),
	    hierName varchar2(30),
	    levelName varchar2(30),
	    levelPos  varchar2(30),
	    levelNum INTEGER
	   );

 		TYPE hierLevelList_t is TABLE of hierLeveltype;



    TYPE mv_dimHier IS RECORD
	   (mvNum    INTEGER,
	    dimName  varchar2(30),
	    hierName varchar2(30),
	    lvlCount  INTEGER,
	    highestLvl BOOLEAN
	   );


		TYPE mvtablename IS RECORD
		(ownerName varchar2(30),
		 tabName varchar2(30)
		);
		
		TYPE parnumtable is RECORD
		(gidNum    NUMBER,
		ownerName  varchar2(30)
		);


		TYPE parnumtable_t is TABLE of parnumtable;
		
		TYPE mvtable_t is TABLE of mvtablename;
		
		
    TYPE mvtype_t IS TABLE of mv_dimHier;

   	TYPE dimList_t IS TABLE OF dimType;
    --    TYPE dimList IS TABLE OF ALL_OLAP_FACT_LEVEL_USES.DIMENSION_NAME%TYPE

    TYPE levList_t IS TABLE OF ALL_OLAP_DIM_LEVELS.LEVEL_NAME%TYPE;

    TYPE hierarchyList_t IS TABLE OF ALL_OLAP_DIM_HIERARCHIES.DIMENSION_NAME%TYPE;

    TYPE colLvlList_t IS TABLE OF ALL_OLAP_LEVEL_KEY_COLUMN_USES.COLUMN_NAME%TYPE;



		CURSOR curDimName IS SELECT DIMENSION_NAME
   	FROM ALL_OLAP_FACT_LEVEL_USES WHERE FACT_TABLE_NAME= eFactName AND
   	OWNER= eOwnerName AND CUBE_NAME= eCubeName;

		CURSOR curLevelName IS SELECT V.L
	   FROM ( SELECT CHILD_LEVEL_NAME L, POSITION P
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
	   UNION
	         SELECT PARENT_LEVEL_NAME L, POSITION+1
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName) V
	   ORDER BY V.P DESC;

		CURSOR curHierarchyName IS SELECT HIERARCHY_NAME
   		FROM ALL_OLAP_DIM_HIERARCHIES WHERE
				 OWNER= eOwnerName AND
				 DIMENSION_NAME= eDimName;

		CURSOR curColName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
	         AND DIMENSION_NAME= eDimName ;

  	CURSOR curTabName IS SELECT LEVEL_TABLE_NAME
	   FROM ALL_OLAP_DIM_LEVELS
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
           AND DIMENSION_NAME= eDimName;

		CURSOR curParentName IS SELECT TABLE_NAME || '.' || COLUMN_NAME, TABLE_NAME
	   FROM ALL_OLAP_JOIN_KEY_COLUMN_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
		 AND CHILD_LEVEL_NAME= eLevelName
     AND POSITION = ePosition;


    CURSOR curLeavName IS SELECT distinct a.column_name
		FROM ALL_OLAP_KEY_COLUMN_USES a, ALL_OLAP_FOREIGN_KEYS b,
     ALL_OLAP_FACT_LEVEL_USES c
		where a.table_name = b.table_name
      and a.position = ePosition
		  and   a.key_name = b.foreign_key_name
		  and   b.table_name = c.fact_table_name
		  and   b.foreign_key_name = c.foreign_key_name
		  and   c.dimension_name = eDimName
		  and   c.level_name = eLevelName
		  and   c.dimension_owner = eOwnerName
		  and   c.fact_table_owner = eOwnerName
		  and   c.fact_table_name = eFactName;

		CURSOR curFactName IS SELECT distinct c.fact_table_name
		from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
		where a.owner = b.owner
		and a.cube_name = b.cube_name
		and b.owner = c.owner
		and b.cube_name = c.cube_name
		and b.measure_name = c.measure_name
		and a.owner = eOwnerName
		and a.cube_name = eCubeName;

		CURSOR curUnitName IS SELECT distinct c.column_name
		from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
		where a.owner = b.owner
		and a.cube_name = b.cube_name
		and b.owner = c.owner
		and b.cube_name = c.cube_name
		and b.measure_name = c.measure_name
		and a.owner = eOwnerName
		and a.cube_name = eCubeName;

		CURSOR checkName IS SELECT QUERY from ALL_MVIEWS
		where owner= eOwnerName
		and QUERY_LEN= query_length;


		CURSOR curLevDimName IS SELECT LEVEL_NAME
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

	  CURSOR curColDimName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

		dimHierList dimhierList_t:= dimhierList_t();
		hierLevelList hierLevelList_t:= hierLevelList_t();
		dimList dimList_t:= dimList_t();
		levList levList_t:= levList_t();
 		hierarchyList hierarchyList_t:= hierarchyList_t();
 		colLvlList colLvlList_t:= colLvlList_t();
 		partList dimhierList_t:= dimhierList_t();
 		mvtableList mvtable_t:= mvtable_t();
		parnum parnumtable_t := parnumtable_t();


   	mvList	  	mvtype_t:= mvtype_t();
   	mvListinc		mvtype_t:= mvtype_t();
   	mvListdec		mvtype_t:= mvtype_t();
    mvdimList   mvtype_t:= mvtype_t();


		BEGIN
	--	CREATE TABLE SYSTEM.MVIEW$_ADV_INDEX (RUNID# NUMBER NOT NULL,
	--  																			RANK# NUMBER NOT NULL,
	--  																			SUMMARY_OWNER VARCHAR2(32),
	--  																			INDEX_TEXT LONG);
    numOfMV:= 1;
    numOfdimMV:= 1;
   -- eOutPath:= '/users/oracle';
    cwm2_olap_metadata_refresh.mr_refresh;
    eOwnerName:= UPPER(exOwnerName);
    eCubeName:= UPPER(exCubeName);
    IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
 			filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
    END IF;
    IF NOT curFactName%ISOPEN THEN
			OPEN curFactName;
			LOOP
				FETCH curFactName INTO eFactName;
				EXIT WHEN curFactName%NOTFOUND;
			END LOOP;
			CLOSE CurFactName;
    END IF;
    IF eFactName IS NULL THEN
      RAISE UTL_FILE.INTERNAL_ERROR;
    END IF;
    IF NOT curFactName%ISOPEN THEN
			OPEN curFactName;
			LOOP
				FETCH curFactName INTO eFactName;
				EXIT WHEN curFactName%NOTFOUND;
			END LOOP;
			CLOSE CurFactName;
    END IF;

    IF NOT curDimName%ISOPEN THEN
			OPEN curDimName;
			i:= 0;
			LOOP
				FETCH curDimName INTO tempName;
				EXIT WHEN curDimName%NOTFOUND;
				dimList.EXTEND;
			
				i:= i + 1;
					dimList(i).dimName:= tempName;
--					dimList(i):= i;
			END LOOP;
			CLOSE CurDimName;
    END IF;
    dimCount:= dimList.Count;
    j:= 0;
 		FOR i in 1..dimList.Count LOOP
			eDimName:= dimList(i).dimName;
      k:= 0;
			IF NOT curHierarchyName%ISOPEN THEN
   			OPEN curHierarchyName;
			END IF;
			LOOP
				FETCH curHierarchyName into tempName;
				EXIT WHEN curHierarchyName%NOTFOUND;
   			j:= j + 1;
   			k:= k + 1;
   			dimHierList.EXTEND;
      	dimHierList(j).dimName := eDimName;
				dimHierList(j).dimNum := i;
   			dimHierList(j).hierName:= tempName;
   			dimHierList(j).hierNum:= k;		
			END LOOP;
			CLOSE curHierarchyName;
			IF k = 0 THEN
				j:= j + 1;
   			k:= k + 1;
   			dimHierList.EXTEND;
      	dimHierList(j).dimName := eDimName;
				dimHierList(j).dimNum := i;
   			dimHierList(j).hierName:= NULL;
   			dimHierList(j).hierNum:= k;
   		END IF;
			dimList(i).hierCount:= k;
		END LOOP;
    j:= 0;
		FOR i in 1..dimHierList.Count LOOP
   		eDimName:= dimHierList(i).dimName;
   		eHierName:= dimHierList(i).hierName;	
   		k:= 0;
 			IF NOT curLevelName%ISOPEN THEN
    		OPEN curLevelName;
 			END IF;
 			LOOP
	   	 	FETCH curLevelName into tempName;
	   	 	EXIT WHEN curLevelName%NOTFOUND;
	      k:= k + 1;
	      j:= j + 1;
	      hierLevelList.EXTEND;
				hierLevelList(j).dimName:= eDimName;
				hierLevelList(j).hierName:= eHierName;
	      hierLevelList(j).levelName:= tempName;
	      hierLevelList(j).levelNum:= k;
	    --  hierLevelList(j).levelPos.EXTEND;
	    --  heirLevelList(j).levelName(k):= tempName;
 			END LOOP;
 			CLOSE curLevelName;
 			IF k= 0 THEN
 				IF NOT curLevDimName%ISOPEN THEN
    		OPEN curLevDimName;
 				END IF;
 				LOOP
	   	 		FETCH curLevDimName into tempName;
	   	 		EXIT WHEN curLevDimName%NOTFOUND;
	      	k:= k + 1;
	      	j:= j + 1;
	      	hierLevelList.EXTEND;
					hierLevelList(j).dimName:= eDimName;
					hierLevelList(j).hierName:= NULL;
	     	 	hierLevelList(j).levelName:= tempName;
	      	hierLevelList(j).levelNum:= k;
 				END LOOP;
 					CLOSE curLevDimName;
	    END IF;	
    	dimHierList(i).levelCount:= k; 
    
	 	END LOOP;
	   	
	    numOfMV:= 1;
	    FOR i in 1..dimList.Count LOOP
	  		numOfMV:= numOfMV * dimList(i).hierCount;
	    END LOOP;

			m:= 0;       
			n:= 0;
			flag:= 1;
	    FOR i in 1..dimList.Count LOOP
	   		tempMVnum:= numOfMV / dimList(i).hierCount;
	   		eDimName:= dimList(i).dimName;
	   		n:= 0;
	   		IF flag= 1 THEN
	   			FOR j in 1..dimList(i).hierCount LOOP
	 					FOR l in 1..dimHierList.Count LOOP
				 				IF dimHierList(l).dimName= eDimName AND
				 					dimHierList(l).hierNum= j THEN
				 					FOR k in 1..tempMVnum LOOP
				 						eHierName:= dimHierList(l).hierName;
				 						m:= m + 1;
				 						n:= n + 1;
				 						flag:= 0;
				 						mvList.EXTEND;
				 						mvList(m).mvNum:= n;
				 						mvList(m).dimName:= eDimName;
				 						mvList(m).hierName:= eHierName;
				 					END LOOP;  
		 						END IF;
		 					END LOOP;
		   			END LOOP;
				ELSE
						FOR k in 1..tempMVnum LOOP	
							FOR j in 1..dimList(i).hierCount LOOP
			 				FOR l in 1..dimHierList.Count LOOP		 							
		 						IF dimHierList(l).dimName= eDimName AND
		 							dimHierList(l).hierNum= j THEN 
		 							eHierName:= dimHierList(l).hierName;
		 							m:= m + 1;
		 							n:= n + 1;
		 							flag:= 1;
		 							mvList.EXTEND;
		 							mvList(m).mvNum:= n;
		 							mvList(m).dimName:= eDimName;
		 							mvList(m).hierName:= eHierName;
									END IF;
								END LOOP;
							END LOOP;
						END LOOP;
	   		END IF;
	   	END LOOP;
	 		FOR i in 1..mvList.Count LOOP
	     		eDimName:= mvList(i).dimName;
	     		eHierName:= mvList(i).hierName;
	     		FOR j in 1..dimHierList.Count LOOP
	     			IF eDimName = dimHierList(j).dimName THEN
	     				IF (eHierName = dimHierList(j).hierName OR
	     					   dimHierList(j).hierName IS NULL ) THEN
	     					mvList(i).lvlCount:= dimHierList(j).levelCount;
	     					mvList(i).highestLvl:= FALSE;
	     				END IF;
	     			END IF;
	     		END LOOP;
	    END LOOP;
			FOR i in 1..numOfMV LOOP
	   		highLvlCount:= 0;
	   		highLvlNum:= 1;
	   		FOR j in 1..mvList.Count LOOP
	   			IF mvList(j).mvNum = i AND
	   				mvList(j).lvlCount >= highLvlCount THEN
	   				highLvlCount:= mvList(j).lvlCount;
	   				highLvlNum:= j;
	   			END IF;
	   		END LOOP;
	   		mvList(highLvlNum).highestLvl:= TRUE;
	  	END LOOP;

			 m:= 0;
		--	 	highLvlCount:= 0;
		   FOR i in 1..numOfMV LOOP
			   	j:= 1;
			   	FOR j in 1..mvList.Count LOOP
			   		IF mvList(j).mvNum = i AND
			   			mvList(j).highestLvl= TRUE THEN
			   				highLvlCount:= mvList(j).LvlCount;
			   	--		EXIT LOOP;
			   		END IF;
			   	END LOOP;
	--		  END LOOP;
			--  	WHILE j <= mvList.Count AND
	    --			mvList(j).mvNum= i
	    --			LOOP
	    --				highLvlCount:= mvList(j).LvlCount;
	    --				j:= j+ 1;
	  --  			EXIT WHEN mvList(j).highestLvl = TRUE;
	    --		END LOOP;
		--	 		FOR j in 1..mvList.Count LOOP
		--	 			IF mvList(j).mvNum= i AND
		--	 				mvList(j).highestLvl= TRUE THEN
		--	 				highLvlNum:= mvList(j).lvlCount;
		--	 				EXIT;
		--	 			END IF;
		--	 		END LOOP;
			 		FOR k in REVERSE 0..highLvlCount LOOP
						FOR j in 1..mvList.Count LOOP
							IF mvList(j).mvNum = i AND
								mvList(j).lvlCount = k THEN
								m:= m + 1;
								mvListdec.EXTEND;
								mvListdec(m).mvNum:= i;
								mvListdec(m).dimName:= mvList(j).dimName;
								mvListdec(m).hierName:= mvList(j).hierName;
								mvListdec(m).lvlCount:= mvList(j).lvlCount;
								mvListdec(m).highestLvl:= mvList(j).highestLvl;
							END IF;
						END LOOP;
			 		END LOOP;
				END LOOP;


     	FOR i in 1..mvListdec.Count LOOP
        mvListdec(i).highestLvl:= FALSE;
      END LOOP;

      FOR i in 1..numOfMV LOOP
	   		highLvlCount:= 0;
	   		highLvlNum:= 1;
	   		FOR j in 1..mvListdec.Count LOOP
	   			IF mvListdec(j).mvNum = i THEN
	   				IF highLvlCount = 0 THEN
	   					highLvlCount:= mvListdec(j).lvlCount;
	   				END IF;
	   				IF mvListdec(j).lvlCount <= highLvlCount THEN
	   					highLvlCount:= mvListdec(j).lvlCount;
	   					highLvlNum:= j;
	   				END IF;
	   			END IF;
	   		END LOOP;
	   		mvListdec(highLvlNum).highestLvl:= TRUE;
	  	END LOOP;
		m:= 0;
    FOR i in 1..numOfMV LOOP
    	j:= 1;
  		FOR j in 1..mvList.Count LOOP
	   		IF mvList(j).mvNum = i AND
	   			mvList(j).highestLvl= TRUE THEN
	   				highLvlCount:= mvList(j).LvlCount;
	   	--		EXIT LOOP;
	   		END IF;
		  END LOOP; 	
    --		WHILE j <= mvList.Count AND
    --			mvList(j).mvNum= i AND
    --			mvList(j).highestLvl= TRUE
    --		LOOP
    --				highLvlCount:= mvList(j).LvlCount;
    --				j:= j+ 1;
    --		END LOOP;
    -- 		FOR j in 1..mvList.Count LOOP
    -- 			IF mvList(j).mvNum= i AND
    -- 				mvList(j).highestLvl= TRUE THEN
    -- 				highLvlNum:= mvList(j).lvlCount;
    -- 				EXIT;
    -- 			END IF;
    -- 		END LOOP;
     		FOR k in 0..highLvlCount LOOP
     			FOR j in 1..mvList.Count LOOP
     				IF mvList(j).mvNum = i AND
     					mvList(j).lvlCount = k THEN
     					m:= m + 1;
     					mvListinc.EXTEND;
     					mvListinc(m).mvNum:= i;
     					mvListinc(m).dimName:= mvList(j).dimName;
     					mvListinc(m).hierName:= mvList(j).hierName;
     					mvListinc(m).lvlCount:= mvList(j).lvlCount;
     					mvListinc(m).highestLvl:= mvList(j).highestLvl;
     				END IF;
     			END LOOP;
     		END LOOP;
    END LOOP;
  --  INSERT INTO SYSTEM.MVIEW$_ADV_LOG(RUNID#,STATUS) VALUES
 	--  														(runid,1);
  	FOR i in 1..numofMV LOOP
		  --UTL_PUT(filehandle,buffer)
		  eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
		  UTL_FILE.PUT_LINE(filehandle,'create materialized view');
		  UTL_FILE.PUT(filehandle,eMVName);
		  UTL_FILE.NEW_LINE(filehandle,1);
--		  UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
	--	  UTL_FILE.NEW_LINE(filehandle,1);
	--	  UTL_FILE.PUT_LINE(filehandle,'parallel');

			dupmvFlag:= FALSE;
      partition:= TRUE;
			IF partitioning = TRUE THEN
				partgid:= 0;
				partnum:= 0;
				o:= 0;
				indexString:= '';
				indexString:= indexString || 'partition by range (gid) (';
				UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
				FOR j in 1..mvListdec.Count LOOP
		  		IF mvListdec(j).mvNum= i THEN
	    			edimName:= mvListdec(j).dimName;
	      		ehierName:= mvListdec(j).hierName;
	      		FOR l in 1..dimHierList.Count LOOP
	      			IF eDimName = dimHierList(l).dimName AND
		     				eHierName = dimHierList(l).hierName THEN
		     				o:= o + 1;
		     				partList.EXTEND;
	      				partList(o).dimName:= dimHierList(l).dimName;
	      				partList(o).hierName:= dimHierList(l).hierName;
								partList(o).dimNum:= dimHierList(l).dimNum;
	   						partList(o).hierNum:= dimHierList(l).hierNum;
	   						partList(o).levelCount:= dimHierList(l).levelCount;
	      			ELSIF eDimName = dimHierList(l).dimName AND
		     				    dimHierList(l).hierName IS NULL THEN
	      				o:= o + 1;
		     				partList.EXTEND;
	      				partList(o).dimName:= dimHierList(l).dimName;
	      				partList(o).hierName:= dimHierList(l).hierName;
								partList(o).dimNum:= dimHierList(l).dimNum;
	   						partList(o).hierNum:= dimHierList(l).hierNum;
	   						partList(o).levelCount:= dimHierList(l).levelCount;

	      			END IF;
	      		END LOOP;
	      	END IF;
				END LOOP;
				times:= 1;
				FOR k in 1..partList.Count LOOP
		     	times:= times * partList(k).levelCount;
				END LOOP;

				FOR k in 1..times LOOP
					parnum.EXTEND;
					parnum(k).gidNum:= 0;
				END LOOP;

				val:= 0;
				ntimes:= 1;
				loopval:= 1;
				tkval:= 0;
				tpnum:= 0;
				FOR k in 1..partList.Count LOOP
					c:= 0;
					tempMVNum:= times/ partList(k).levelCount;
					IF k= 1 THEN
						FOR z in 1..tempMVNum LOOP
							FOR m in 1..partList(k).levelCount LOOP
								c:= c + 1;
								parnum(c).gidNum:= val;
								IF z <> tempMVNum THEN
									IF m= partList(k).levelCount THEN
										val:= 0;
									ELSE
										IF val= 0 THEN
											val:= 1;
										ELSE
                      val:= (val * 2) + 1;
									--		tkval:= m - 1;
									--		tpnum:= tkval * 2;
									--		val:= val + tpnum;
										END IF;
									END IF;
								ELSE
									IF m = partList(k).levelCount THEN
										tpnum:= val + 1;
										val:= tpnum * 2;
										tkval:= val;
									ELSE
										IF val= 0 THEN
											val:= 1;
										ELSE
								--			tkval:= m - 1;
								--			tpnum:= tkval * 2;
								--			val:= val + tpnum;
                      val:= (val * 2) + 1;
											tkval:= val;
										END IF;
									END IF;
								END IF;
							END LOOP;
						END LOOP;
						loopval:= loopval * partList(k).levelCount;
					ELSE
						ntimes:= 1;
						ntimes:= tempMVNum / loopval;
						val:= 0;
--						tkval:= 0;
						FOR i in 1..ntimes LOOP
							FOR j in 1..partList(k).levelCount LOOP
							  tempval:= loopval / partList(1).levelCount;
								FOR v in 1..tempval LOOP
									FOR h in 1..partList(1).levelCount LOOP
										c:= c + 1;
									 	parnum(c).gidNum:= parnum(c).gidNum + val;
									END LOOP;
								END LOOP;
								IF j = partList(k).levelCount THEN
									IF i= ntimes THEN
										tkval:= tpnum * 4;
										val:= 0;
									ELSE
										tpnum:= tkval;
										val:= 0;
									END IF;
								ELSE
									IF val= 0 THEN
										tpnum:= tkval;
										val:= val + tpnum;
									ELSE
										tpnum:= tpnum * 2;
										val:= val + tpnum;
									END IF;
								END IF;
							END LOOP;
						END LOOP;
						loopval:= loopval * partList(k).levelCount;
					END IF;
				END LOOP;
	
				FOR k in 1..times LOOP
					IF k > 1 THEN
						IF k < 800 THEN
							indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';

						END IF;
						UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
						UTL_FILE.NEW_LINE(filehandle,1);
					END IF;
				END LOOP;
 	      indexString:= indexString || ' partition values less than(MAXVALUE))';
 	      UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
	      partList.DELETE;
	      parnum.DELETE;		
      END IF;
      UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
      IF mvspcName IS NOT NULL THEN
        UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
        UTL_FILE.NEW_LINE(filehandle,1);
      END IF;
      UTL_FILE.PUT_LINE(filehandle,'build immediate');
      UTL_FILE.PUT_LINE(filehandle,'using no index');
      UTL_FILE.PUT_LINE(filehandle,'refresh force');
		  UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
		  UTL_FILE.PUT_LINE(filehandle,'AS');
		  UTL_FILE.PUT_LINE(filehandle,'SELECT');
		 	bufString:= bufString || 'SELECT ';

      UTL_FILE.PUT(filehandle,'GROUPING_ID(');
      bufString:= bufString || 'GROUPING_ID( ';
	  	FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
    			edimName:= mvListinc(j).dimName;
      		ehierName:= mvListinc(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eDimName AND
	     				hierLevelList(l).hierName= eHierName THEN
			     		eLevelName:= hierLevelList(l).levelName;
			  			IF NOT curColName%ISOPEN THEN
			  				OPEN curColName;
			  			END IF;
			  			LOOP
			  				FETCH curColName into eLevelColName,ePosition;
			  				EXIT WHEN curColName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curColName;
			  			IF NOT curTabName%ISOPEN THEN
			  				OPEN curTabName;
			  			END IF;
			  			LOOP
			  				FETCH curTabName into eTabName;
			  				EXIT WHEN curTabName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curTabName;
			  			UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
            			  --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';

			  			bufString:= bufString 
                                                            || dbms_assert.enquote_name(eTabName) 
                                                            || '.' 
                                                            || dbms_assert.enquote_name(eLevelColName) 
                                                            || ' ';

			  			IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
			  				UTL_FILE.PUT_LINE(filehandle,',');
								bufString:= bufString || ', ';
			  			END IF;

			  		ELSIF hierLevelList(l).dimName = eDimName AND
		  				hierLevelList(l).hierName IS NULL THEN
		  				eLevelName:= hierLevelList(l).levelName;
		  				IF NOT curColDimName%ISOPEN THEN
		  					OPEN curColDimName;
		  				END IF;
		  				LOOP
		  					FETCH curColDimName into eLevelColName,ePosition ;
		  					EXIT WHEN curColDimName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curColDimName;
		  				IF NOT curTabName%ISOPEN THEN
		  					OPEN curTabName;
		  				END IF;
		  				LOOP
		  					FETCH curTabName into eTabName;
		  					EXIT WHEN curTabName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curTabName;
	 	  			 	UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
		  	          --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';

		  				bufString:= bufString 
                                                            || dbms_assert.enquote_name(eTabName) 
                                                            || '.' 
                                                            || dbms_assert.enquote_name(eLevelColName) 
                                                            || ' ';

			  			IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
			  				UTL_FILE.PUT_LINE(filehandle,',');
								bufString:= bufString || ', ';
			  			END IF;
  					END IF;
      		END LOOP;
      		IF mvListinc(j).highestLvl = FALSE THEN
      			UTL_FILE.PUT_LINE(filehandle,',');
      			bufString:= bufString || ', ';
      		END IF;
	  		END IF;
	  	END LOOP;
      UTL_FILE.PUT_LINE(filehandle,') gid,');
		  bufString:= bufString || ') gid, ';
		  IF NOT curUnitName%ISOPEN THEN
			OPEN curUnitName;
			LOOP
				FETCH curUnitName INTO eUnitName;
				EXIT WHEN curUnitName%NOTFOUND;
				UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
				UTL_FILE.NEW_LINE(filehandle,1);
	 --			UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
	 --			UTL_FILE.NEW_LINE(filehandle,1);
		  --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';

				bufString:= bufString 
                                            || 'SUM(' 
                                            || dbms_assert.enquote_name(eFactName) 
                                            || '.' 
                                            || dbms_assert.enquote_name(eUnitName) 
                                            || ') ';

	          --Bug 8915150 bufString:= bufString || 'SUM_OF_' || eUnitName || ', ';

				bufString:= bufString 
                                            || 'SUM_OF_' 
                                            || dbms_assert.enquote_name(eUnitName) 
                                            || ', ';

	--			bufString:= bufString || 'COUNT(' || eFactName || '.' || eUnitName || ') ';
	--			bufString:= bufString || 'COUNT_OF_' || eUnitName || ', ';
			END LOOP;
			CLOSE CurUnitName;
    	END IF;
			UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');
			bufString:= bufString || 'COUNT(*) COUNT_OF_STAR, ';

			FOR j in 1..mvListdec.Count LOOP
  			IF mvListdec(j).mvNum= i THEN
      		edimName:= mvListdec(j).dimName;
        	ehierName:= mvListdec(j).hierName;
	     	  FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= eDimName AND
		     			hierLevelList(l).hierName= ehierName THEN
		     			eLevelName:= hierLevelList(l).levelName;
			  			IF NOT curColName%ISOPEN THEN
			  				OPEN curColName;
			  			END IF;
			  			LOOP
			  				FETCH curColName into eLevelColName,ePosition ;
			  				EXIT WHEN curColName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curColName;
			  			IF NOT curTabName%ISOPEN THEN
			  				OPEN curTabName;
			  			END IF;
			  			LOOP
			  				FETCH curTabName into eTabName;
			  				EXIT WHEN curTabName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curTabName;
			  			UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
			  --	  	UTL_FILE.NEW_LINE(filehandle,1);
			  	  --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;

			  			bufString:= bufString 
                                                            || dbms_assert.enquote_name(eTabName) 
                                                            || '.' 
                                                            || dbms_assert.enquote_name(eLevelColName) 
                                                            || ' ' 
                                                            || dbms_assert.enquote_name(eLevelColName);

              IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
			  				UTL_FILE.PUT_LINE(filehandle,',');
								bufString:= bufString || ', ';
			  			END IF;
			  		ELSIF hierLevelList(l).dimName = eDimName AND
			  				hierLevelList(l).hierName IS NULL THEN
			  				eLevelName:= hierLevelList(l).levelName;
			  				IF NOT curColDimName%ISOPEN THEN
			  					OPEN curColDimName;
			  				END IF;
			  				LOOP
			  					FETCH curColDimName into eLevelColName,ePosition ;
			  					EXIT WHEN curColDimName%NOTFOUND;
			  				END LOOP;
			  				CLOSE curColDimName;
			  				IF NOT curTabName%ISOPEN THEN
			  					OPEN curTabName;
			  				END IF;
			  				LOOP
			  					FETCH curTabName into eTabName;
			  					EXIT WHEN curTabName%NOTFOUND;
			  				END LOOP;
			  				CLOSE curTabName;
	 		  				UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
	 		--  		 		UTL_FILE.NEW_LINE(filehandle,1);
			  		  --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;

			  				bufString:= bufString 
                                                                    || dbms_assert.enquote_name(eTabName) 
                                                                    || '.' 
                                                                    || dbms_assert.enquote_name(eLevelColName) 
                                                                    || ' ' 
                                                                    || dbms_assert.enquote_name(eLevelColName);

                IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
			  				  UTL_FILE.PUT_LINE(filehandle,',');
								  bufString:= bufString || ', ';
			  			  END IF;
		       	END IF;
	     	  END LOOP;
          IF mvListdec(j).highestLvl = FALSE THEN
      			UTL_FILE.PUT_LINE(filehandle,',');
      			bufString:= bufString || ', ';
      		END IF;
  			END IF;
  		END LOOP;
      UTL_FILE.NEW_LINE(filehandle,1);
   		UTL_FILE.PUT_LINE(filehandle,'FROM');
  		o:= 0;
  		bufString:= bufString || 'FROM ';
  		FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
	      	FOR l in 1..hierLevelList.Count LOOP
	      		IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName AND
	      			mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
		  					eLevelName:=  hierLevelList(l).levelName;
			  				IF NOT curColName%ISOPEN THEN
			  					OPEN curColName;
			  				END IF;
				  			LOOP
				  				FETCH curColName into eLevelColName,ePosition;
				  				EXIT WHEN curColName%NOTFOUND;
				  			END LOOP;
				  			CLOSE curColName;
				  			IF NOT curTabName%ISOPEN THEN
				  				OPEN curTabName;
				  			END IF;
				  			LOOP
				  				FETCH curTabName into eTabName;
				  				EXIT WHEN curTabName%NOTFOUND;
				  			END LOOP;
				  			CLOSE curTabName;
				  			o:= o + 1;
				  			mvtableList.EXTEND;
				  			mvtableList(o).tabName:= eTabName;
				  			mvtableList(o).ownerName:= eOwnerName;
		--		  			UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
		--		  			UTL_FILE.PUT_LINE(filehandle,' ');
		--		  			bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
							ELSIF hierLevelList(l).dimName = eDimName AND
		  					hierLevelList(l).hierName IS NULL THEN
		  					eLevelName:= hierLevelList(l).levelName;
		  					IF NOT curColDimName%ISOPEN THEN
		  						OPEN curColDimName;
		  					END IF;
		  					LOOP
		  						FETCH curColDimName into eLevelColName,ePosition ;
		  						EXIT WHEN curColDimName%NOTFOUND;
		  					END LOOP;
		  					CLOSE curColDimName;
		  					IF NOT curTabName%ISOPEN THEN
		  						OPEN curTabName;
		  					END IF;
		  					LOOP
		  						FETCH curTabName into eTabName;
		  						EXIT WHEN curTabName%NOTFOUND;
		  					END LOOP;
		  					CLOSE curTabName;
		  					o:= o + 1;
				  			mvtableList.EXTEND;
				  			mvtableList(o).tabName:= eTabName;
				  			mvtableList(o).ownerName:= eOwnerName;
			  		END IF;
	       	END LOOP;
		   	END IF;
  		END LOOP;
  		FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
		  		FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  			--	mvnum:= mvnum + 1;
		  				IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
		  				LOOP
		  					FETCH curColName into eLevelColName,ePosition ;
		  					EXIT WHEN curColName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curColName;
		 	 				IF NOT curTabName%ISOPEN THEN
		  					OPEN curTabName;
		  				END IF;
		  				LOOP
		  					FETCH curTabName into eTabName;
		  					EXIT WHEN curTabName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curTabName;
	  					parentString:= eTabName || '.' || eLevelColName;
	  					childString:= parentString;
	  					IF NOT curParentName%ISOPEN THEN
	  						OPEN curParentName;
	  					END IF;
	  					LOOP
	  						FETCH curParentName into eparLevelName,etableName;
	  					EXIT WHEN curParentName%FOUND;
	  					END LOOP;
	  					CLOSE curParentName;
	  					childString:= eparLevelName;
	  					IF childString <> parentString THEN
	  						tableCount:= mvtableList.Count;
	  						z:= 1;
	  						whereFlag:= FALSE;
	  						WHILE z <= tableCount LOOP
	  							IF mvtableList(z).tabName = eTabName THEN
	  								whereFlag:= TRUE;
	  							END IF;
	  							EXIT WHEN mvtableList(z).tabName= eTabName;
	  							z:= z + 1;
	  						END LOOP;
	  						IF whereFlag= FALSE THEN
	  							o:= o +1;
	  							mvtableList.EXTEND;
				  				mvtableList(o).tabName:= eTabName;
				  				mvtableList(o).ownerName:= eOwnerName;
				  			END IF;
		  				--		 bufString:= bufString || ' AND ';
	  					--	UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
	  					--	bufString:= bufString || '(' || childString || '=' || parentString || ') ';
	  					END IF;
            ELSIF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= NULL THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  			--	mvnum:= mvnum + 1;
		  				IF NOT curColDimName%ISOPEN THEN
		  					OPEN curColDimName;
		  				END IF;
		  				LOOP
		  					FETCH curColDimName into eLevelColName,ePosition ;
		  					EXIT WHEN curColDimName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curColDimName;
		 	 				IF NOT curTabName%ISOPEN THEN
		  					OPEN curTabName;
		  				END IF;
		  				LOOP
		  					FETCH curTabName into eTabName;
		  					EXIT WHEN curTabName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curTabName;
	  					parentString:= eTabName || '.' || eLevelColName;
	  					childString:= parentString;
	  					IF NOT curParentName%ISOPEN THEN
	  						OPEN curParentName;
	  					END IF;
	  					LOOP
	  						FETCH curParentName into eparLevelName,etableName;
	  					EXIT WHEN curParentName%FOUND;
	  					END LOOP;
	  					CLOSE curParentName;
	  					childString:= eparLevelName;
	  					IF childString <> parentString THEN
	  						tableCount:= mvtableList.Count;
	  						z:= 1;
	  						whereFlag:= FALSE;
	  						WHILE z <= tableCount LOOP
	  							IF mvtableList(z).tabName = eTabName THEN
	  								whereFlag:= TRUE;
	  							END IF;
	  							EXIT WHEN mvtableList(z).tabName= eTabName;
	  							z:= z + 1;
	  						END LOOP;
	  						IF whereFlag= FALSE THEN
	  							o:= o +1;
	  							mvtableList.EXTEND;
				  				mvtableList(o).tabName:= eTabName;
				  				mvtableList(o).ownerName:= eOwnerName;
				  			END IF;
		  				--		 bufString:= bufString || ' AND ';
	  					--	UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
	  					--	bufString:= bufString || '(' || childString || '=' || parentString || ') ';
	  					END IF;
						END IF;
		  		END LOOP;
	  		END IF;
			END LOOP;
			FOR o in 1..mvtableList.Count LOOP
          --Bug 8915150 bufString:= bufString || mvtableList(o).ownerName || '.' || mvtableList(o).tabName || ' '
          --Bug 8915150						|| mvtableList(o).tabName || ', ';

 		  	bufString:= bufString 
                                    ||dbms_assert.enquote_name(mvtableList(o).ownerName) 
                                    || '.' 
                                    || dbms_assert.enquote_name(mvtableList(o).tabName) 
                                    || ' '
                                    || dbms_assert.enquote_name(mvtableList(o).tabName) 
                                    || ', ';
 		  	UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
 		  																				mvtableList(o).tabName);
				UTL_FILE.PUT_LINE(filehandle,' ');
			END LOOP;
			mvtableList.DELETE;
			UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
		  UTL_FILE.PUT_LINE(filehandle,' ');
			UTL_FILE.PUT_LINE(filehandle,'WHERE');
			whereFlag:= FALSE;
          --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';

			bufString:= bufString 
                                    || dbms_assert.enquote_name(eOwnerName)
                                    || '.'
                                    || dbms_assert.enquote_name(eFactName) 
                                    || ' ' 
                                    || dbms_assert.enquote_name(eFactName) 
                                    || ' ';

			bufString:= bufString || ' ' || 'WHERE ';
			mvnum:= 0;

      FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
		  		FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName AND
	      			mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  				mvnum:= mvnum + 1;
		  				IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
              poscount:= 0;
              LOOP
		  					FETCH curColName into eLevelColName,ePosition;
                EXIT WHEN curColName%NOTFOUND;
                poscount:= poscount + 1;
              END LOOP;
              CLOSE curColName;
              IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
		  				LOOP
		  					FETCH curColName into eLevelColName,ePosition;
                EXIT WHEN curColName%NOTFOUND;
	              IF NOT curTabName%ISOPEN THEN
		  					  OPEN curTabName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curTabName into eTabName;
		  					  EXIT WHEN curTabName%NOTFOUND;
		  				  END LOOP;
		  				  CLOSE curTabName;
                IF NOT curLeavName%ISOPEN THEN
		  					  OPEN curLeavName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curLeavName into eLeavColName ;
		  					  EXIT WHEN curLeavName%FOUND;
		  				  END LOOP;
		  				  CLOSE curLeavName;
                UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
  --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || '=' || eFactName || '.' || eLeavColName || ') ';

                bufString:= bufString 
                            || '(' 
                            || dbms_assert.enquote_name(eTabName) 
                            || '.' 
                            || dbms_assert.enquote_name(eLevelColName) 
                            || '=' 
                            || dbms_assert.enquote_name(eFactName) 
                            || '.' 
                            || dbms_assert.enquote_name(eLeavColName) 
                            || ') ';

                IF ePosition < poscount THEN
                  UTL_FILE.PUT_LINE(filehandle,' AND');
                  bufString:= bufString || ' AND ';
                END IF;
              END LOOP;
              CLOSE curColName;
						ELSIF hierLevelList(l).dimName = eDimName AND
	  					hierLevelList(l).hierName IS NULL THEN
	  					eLevelName:= hierLevelList(l).levelName;
              mvnum:= mvnum + 1;
	  					IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
              poscount:= 0;
              LOOP
		  					FETCH curColDimName into eLevelColName,ePosition;
                EXIT WHEN curColDimName%NOTFOUND;
                poscount:= poscount + 1;
              END LOOP;
              CLOSE curColDimName;
              IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
	  					LOOP
	  						FETCH curColDimName into eLevelColName,ePosition ;
	  						EXIT WHEN curColDimName%NOTFOUND;
                IF NOT curTabName%ISOPEN THEN
	  						  OPEN curTabName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curTabName into eTabName;
	  						  EXIT WHEN curTabName%NOTFOUND;
	  					  END LOOP;
	  					  CLOSE curTabName;
							  IF NOT curLeavName%ISOPEN THEN
		  					  OPEN curLeavName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curLeavName into eLeavColName ;
		  					  EXIT WHEN curLeavName%NOTFOUND;
		  				  END LOOP;
		  				  CLOSE curLeavName;
           --     UTL_FILE.PUT_LINE(filehandle,' AND');
                UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
  --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || '=' || eFactName || '.' || eLeavColName || ') ';

                bufString:= bufString 
                            || '(' 
                            || dbms_assert.enquote_name(eTabName) 
                            || '.' 
                            || dbms_assert.enquote_name(eLevelColName) 
                            || '=' 
                            || dbms_assert.enquote_name(eFactName) 
                            || '.' 
                            || dbms_assert.enquote_name(eLeavColName) 
                            || ') ';

                IF ePosition < poscount THEN
                  UTL_FILE.PUT_LINE(filehandle,' AND');
                  bufString:= bufString || ' AND ';
                END IF;
	  					END LOOP;
	  					CLOSE curColDimName;
		  			END IF;
		  		END LOOP;
		  		IF mvnum < dimList.Count THEN
		  			UTL_FILE.PUT_LINE(filehandle,' AND');
						bufString:= bufString || ' AND ';
	    		END IF;
	  		END IF;
			END LOOP;
			FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
		  		FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  			--	mvnum:= mvnum + 1;
		  				IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
		  				LOOP
		  					FETCH curColName into eLevelColName,ePosition ;
		  					EXIT WHEN curColName%NOTFOUND;
	              IF NOT curTabName%ISOPEN THEN
		  					  OPEN curTabName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curTabName into eTabName;
		  					  EXIT WHEN curTabName%NOTFOUND;
                END LOOP;
	              CLOSE curTabName;
	  					  parentString:= eTabName || '.' || eLevelColName;
	  					  childString:= parentString;
	  					  IF NOT curParentName%ISOPEN THEN
	  						  OPEN curParentName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curParentName into eparLevelName,etableName ;
	  					    EXIT WHEN curParentName%FOUND;
	  					  END LOOP;
	  					  CLOSE curParentName;
	  					  childString:= eparLevelName;
	  					  IF childString <> parentString THEN
		  					  bufString:= bufString || ' AND ';
		  					  UTL_FILE.PUT_LINE(filehandle,' AND');
	  						  UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
	  				    --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
	  				                  bufString:= bufString 
                                                                      || '(' 
                                                                      || dbms_assert.enquote_name(childString) 
                                                                      || '=' 
                                                                      || dbms_assert.enquote_name(parentString) 
                                                                      || ') ';

	  					  END IF;
		  				END LOOP;
		  				CLOSE curColName;
	          ELSIF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= NULL THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  			--	mvnum:= mvnum + 1;
		  				IF NOT curColDimName%ISOPEN THEN
		  					OPEN curColDimName;
		  				END IF;
		  				LOOP
		  					FETCH curColDimName into eLevelColName,ePosition ;
		  					EXIT WHEN curColDimName%NOTFOUND;
	              IF NOT curTabName%ISOPEN THEN
		  					  OPEN curTabName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curTabName into eTabName;
		  					  EXIT WHEN curTabName%NOTFOUND;
                END LOOP;
	              CLOSE curTabName;
	  					  parentString:= eTabName || '.' || eLevelColName;
	  					  childString:= parentString;
	  					  IF NOT curParentName%ISOPEN THEN
	  						  OPEN curParentName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curParentName into eparLevelName,etableName ;
	  					    EXIT WHEN curParentName%FOUND;
	  					  END LOOP;
	  					  CLOSE curParentName;
	  					  childString:= eparLevelName;
	  					  IF childString <> parentString THEN
		  					  bufString:= bufString || ' AND ';
		  					  UTL_FILE.PUT_LINE(filehandle,' AND');
	  						  UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
	  			            --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';

	  						  bufString:= bufString 
                                                                      || '(' 
                                                                      || dbms_assert.enquote_name(childString) 
                                                                      || '=' 
                                                                      || dbms_assert.enquote_name(parentString) 
                                                                      || ') ';
	  					  END IF;
		  				END LOOP;
		  				CLOSE curColDimName;
						END IF;
		  		END LOOP;
	  		END IF;
			END LOOP;

	  	UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');
	  	bufString:= bufString || 'GROUP BY ';
	  	FOR j in 1..mvListinc.Count LOOP
        IF mvListinc(j).mvNum= i THEN
        	mvnum:= 0;
  				edimName:= mvListinc(j).dimName;
    			ehierName:= mvListinc(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
      			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
		  				 eLevelName:=  hierLevelList(l).levelName;
		  				 mvnum:= mvnum + 1;
               IF NOT curColName%ISOPEN THEN
	  						OPEN curColName;
               END IF;
               poscount:= 0;
               LOOP
		  					FETCH curColName into eLevelColName,ePosition;
                EXIT WHEN curColName%NOTFOUND;
                poscount:= poscount + 1;
               END LOOP;
               CLOSE curColName;
               IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				 END IF;
	  					 LOOP
	  						FETCH curColName INTO eLevelColName,ePosition ;
	  						EXIT WHEN curColName%NOTFOUND;
                IF NOT curTabName%ISOPEN THEN
	  						  OPEN curTabName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curTabName INTO eTabName;
	  						  EXIT WHEN curTabName%NOTFOUND;
	  					  END LOOP;
                CLOSE curTabName;
                IF ePosition < poscount AND ePosition = 1 THEN
                  UTL_FILE.PUT(filehandle,'(');
                ELSIF ePosition <= poscount AND ePosition <> 1 THEN
                  UTL_FILE.PUT(filehandle,', ');
                END IF;
	  					  UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
	  			    --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';

	  					  bufString:= bufString 
                                                              || dbms_assert.enquote_name(eTabName) 
                                                              || '.' 
                                                              || dbms_assert.enquote_name(eLevelColName) 
                                                              || ' ';

	  					END LOOP;
	  					CLOSE curColName;
              IF ePosition <> 1 THEN
                UTL_FILE.PUT(filehandle,')');
              END IF;
              IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
	  						  UTL_FILE.PUT(filehandle,', ROLLUP( ');
	  						  bufString:= bufString || ', ROLLUP( ';
              ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
	  						  UTL_FILE.PUT_LINE(filehandle,', ');
	  						  bufString:= bufString || ', ';
              ELSIF mvListinc(j).lvlCount= mvnum THEN
	  						  UTL_FILE.PUT(filehandle,') ');
	  						  bufString:= bufString || ') ';
              END IF;
	  				ELSIF hierLevelList(l).dimName = eDimName AND
	  					hierLevelList(l).hierName IS NULL THEN
	  					eLevelName:= hierLevelList(l).levelName;
	  					mvnum:= mvnum + 1;
              IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
              poscount:= 0;
              LOOP
		  					FETCH curColDimName into eLevelColName,ePosition;
                EXIT WHEN curColDimName%NOTFOUND;
                poscount:= poscount + 1;
              END LOOP;
              CLOSE curColDimName;
              IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
	  					LOOP
	  						FETCH curColDimName into eLevelColName,ePosition ;
	  						EXIT WHEN curColDimName%NOTFOUND;
	  					  IF NOT curTabName%ISOPEN THEN
	  						  OPEN curTabName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curTabName into eTabName;
	  						  EXIT WHEN curTabName%FOUND;
	  					  END LOOP;
	  					  CLOSE curTabName;
                IF ePosition < poscount AND ePosition <> 1 THEN
                  UTL_FILE.PUT(filehandle,'(');
                ELSIF ePosition <= poscount THEN
                  UTL_FILE.PUT(filehandle,',');
                END IF;
	  					  UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
	  		            --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';

	  					  bufString:= bufString 
                                                              || dbms_assert.enquote_name(eTabName) 
                                                              || '.' 
                                                              || dbms_assert.enquote_name(eLevelColName) 
                                                              || ' ';

              END LOOP;
              CLOSE curColDimName;
              IF ePosition <> 1 THEN
                UTL_FILE.PUT(filehandle,' )');
              END IF;
	  				END IF;
      		END LOOP;
    			IF mvListinc(j).highestLvl = FALSE THEN
     				UTL_FILE.PUT(filehandle,', ');
     				bufString:= bufString || ', ';
  				ELSE
  					UTL_FILE.PUT(filehandle,';');
  			--		bufString:= bufString || ';';
  				END IF;
        END IF;
	  	END LOOP;
	  	eTempMV:= 'aaa';
	  	num_bytes:= 0;
	  	query_length:= LENGTH(bufString);
	  	IF NOT checkName%ISOPEN THEN
			  OPEN checkName;
			END IF;
			LOOP
				FETCH checkName into mvString;
				IF bufString = mvString THEN
					EXIT ;
				ELSE
	--			 	DBMS_SUMMARY.estimate_mview_size(eTempMV,bufString,num_rows,num_bytes);
	--				INSERT INTO SYSTEM.MVIEW$_ADV_OUTPUT(RUNID#,OUTPUT_TYPE,RANK#,SUMMARY_OWNER,
	--  									QUERY_TEXT,STORAGE_IN_BYTES,BENEFIT_TO_COST_RATIO) VALUES
	--  														(runid,0,i,eOwnerName,bufString,num_bytes,0);

	--  			INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
  --                             (runId,i,eOwnerName,indexString);
          dupmvFlag:= TRUE;

      		indexString:= '';
				END IF;
				EXIT WHEN checkName%NOTFOUND;
			END LOOP;
			CLOSE checkName;

      UTL_FILE.NEW_LINE(filehandle,2);
	  	UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
                        eOwnerName);
      UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
      UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
	  	UTL_FILE.NEW_LINE(filehandle,1);
 	    UTL_FILE.NEW_LINE(filehandle,2);
	  	UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);

	 		eTempMV:= '';
	 		bufString:= '';
	  	UTL_FILE.NEW_LINE(filehandle,1);
			mvnum:= 0;
      tpnum:= 0;
	  	FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
    			edimName:= mvListinc(j).dimName;
      		ehierName:= mvListinc(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eDimName AND
	     				hierLevelList(l).hierName= eHierName THEN
			     		eLevelName:= hierLevelList(l).levelName;
			  			IF NOT curColName%ISOPEN THEN
			  				OPEN curColName;
			  			END IF;
			  			LOOP
			  				FETCH curColName into eLevelColName,ePosition ;
			  				EXIT WHEN curColName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curColName;
			  			IF NOT curTabName%ISOPEN THEN
			  				OPEN curTabName;
			  			END IF;
			  			LOOP
			  				FETCH curTabName into eTabName;
			  				EXIT WHEN curTabName%NOTFOUND;
			  			END LOOP;
 			  			CLOSE curTabName;
              tpnum:= j * l;
              IF hierLevelList(l).levelNum <> 1 THEN
			 				  UTL_FILE.NEW_LINE(filehandle,2);
			  			  UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
	              UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
			  			  UTL_FILE.NEW_LINE(filehandle,1);
			  			  UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  			  UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
                IF indspcName IS NOT NULL THEN
                  UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
                  UTL_FILE.NEW_LINE(filehandle,1);
                END IF;
			  			  UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
			  			  UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  			  UTL_FILE.NEW_LINE(filehandle,2);
              END IF;
			  			mvnum:= mvnum + 1;
			          --Bug 8915150 bufString:= bufString || 'BMPIDX_' || eLevelColName;

			  			bufString:= bufString 
                                                            || 'BMPIDX_' 
                                                            || dbms_assert.enquote_name(eLevelColName);

			--  			bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';

			--  			bufString:= bufString || 'PARALLEL NOLOGGING;   ';
			--				IF dupmvFlag= TRUE THEN
			--					INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  	--										SUMMARY_OWNER) VALUES
	  	--									(runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
	  		--			END IF;
							bufString:= '';
						ELSIF hierLevelList(l).dimName = eDimName AND
	  					hierLevelList(l).hierName IS NULL THEN
	  					eLevelName:= hierLevelList(l).levelName;
	  					mvnum:= mvnum + 1;
	  					IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
	  					LOOP
	  						FETCH curColDimName into eLevelColName,ePosition ;
	  						EXIT WHEN curColDimName%NOTFOUND;
	  					END LOOP;
	  					CLOSE curColDimName;
	  					IF NOT curTabName%ISOPEN THEN
	  						OPEN curTabName;
	  					END IF;
	  					LOOP
	  						FETCH curTabName into eTabName;
	  						EXIT WHEN curTabName%NOTFOUND;
	  					END LOOP;
	  					CLOSE curTabName;
				          --Bug 8915150 bufString:= bufString || 'BMPIDX_' || eLevelColName;

							bufString:= bufString 
                                                                    || 'BMPIDX_' 
                                                                    || dbms_assert.enquote_name(eLevelColName);

              IF hierLevelList(l).levelNum <> 1 THEN
							  UTL_FILE.NEW_LINE(filehandle,2);
			  			  UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
			  			  UTL_FILE.NEW_LINE(filehandle,1);
			  			  UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  			  UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
                IF indspcName IS NOT NULL THEN
                  UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
                  UTL_FILE.NEW_LINE(filehandle,1);
                END IF;
			  			  UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
			  			  UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  			  UTL_FILE.NEW_LINE(filehandle,2);
              END IF;
			--  			bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
			--  			bufString:= bufString || 'PARALLEL NOLOGGING;   ';
			--				IF dupmvFlag= TRUE THEN
			--					INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  	--										SUMMARY_OWNER) VALUES
	  	--									(runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
	  	--				END IF;
							bufString:= '';
	  				END IF;
      		END LOOP;
	  		END IF;
	  	END LOOP;

	 -- 	INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
	 -- 									INDEX_TEXT) VALUES
	  --										(runid,i,eOwnerName,bufString);

	  	o:= 0;
			FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
    			edimName:= mvListinc(j).dimName;
      		ehierName:= mvListinc(j).hierName;
      		FOR l in 1..dimHierList.Count LOOP
      			IF eDimName = dimHierList(l).dimName AND
	     				eHierName = dimHierList(l).hierName THEN
	     				o:= o + 1;
	     				partList.EXTEND;
      				partList(o).dimName:= dimHierList(l).dimName;
      				partList(o).hierName:= dimHierList(l).hierName;
							partList(o).dimNum:= dimHierList(l).dimNum;
   						partList(o).hierNum:= dimHierList(l).hierNum;
   						partList(o).levelCount:= dimHierList(l).levelCount;
      			ELSIF eDimName = dimHierList(l).dimName AND
	     				    dimHierList(l).hierName IS NULL THEN
      				o:= o + 1;
	     				partList.EXTEND;
      				partList(o).dimName:= dimHierList(l).dimName;
      				partList(o).hierName:= dimHierList(l).hierName;
							partList(o).dimNum:= dimHierList(l).dimNum;
   						partList(o).hierNum:= dimHierList(l).hierNum;
   						partList(o).levelCount:= dimHierList(l).levelCount;

      			END IF;
      		END LOOP;
      	END IF;
			END LOOP;
			times:= 0;
			FOR k in 1..partList.Count LOOP
	     	times:= times + partList(k).levelCount;
			END LOOP;

			FOR j in 1..partList.Count LOOP
	  		edimName:= partList(j).dimName;
      	ehierName:= partList(j).hierName;
	      m:= 0;

        tpnum:= j * i;
     -- 	UTL_FILE.NEW_LINE(filehandle,2);
		 --		UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
		 --		UTL_FILE.NEW_LINE(filehandle,1);
		 --		UTL_FILE.PUT_LINE(filehandle,'  ( ');
		 --	  UTL_FILE.PUT_LINE(filehandle,'     bin_to_num ');
		 --	  UTL_FILE.PUT_LINE(filehandle,'     ( ');
				FOR l in 1..hierLevelList.Count LOOP
  				IF hierLevelList(l).dimName= eDimName AND
     				hierLevelList(l).hierName= eHierName THEN
		     		eLevelName:= hierLevelList(l).levelName;
		  			IF NOT curColName%ISOPEN THEN
		  				OPEN curColName;
		  			END IF;
		  			LOOP
		  				FETCH curColName into eLevelColName,ePosition ;
		  				EXIT WHEN curColName%NOTFOUND;
		  			END LOOP;
		  			CLOSE curColName;
		  			IF NOT curTabName%ISOPEN THEN
		  				OPEN curTabName;
		  			END IF;
		  			LOOP
		  				FETCH curTabName into eTabName;
		  				EXIT WHEN curTabName%NOTFOUND;
		  			END LOOP;
		  			CLOSE curTabName;
		  			times:= times - 1;
					  m:= m + 1;
			--			UTL_FILE.PUTF(filehandle,'       sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
			--		  IF m < partList(j).levelCount THEN

      --			  UTL_FILE.PUT(filehandle,',');
			--		  END IF;
		  --			UTL_FILE.PUTF(filehandle,'     /* grouping(%s) */ ', eLevelColName);
			--			UTL_FILE.NEW_LINE(filehandle,1);
          END IF;
        END LOOP;
			--  UTL_FILE.PUT_LINE(filehandle,'     ) ');
			--	UTL_FILE.PUT_LINE(filehandle,'  ) ');
  		--	UTL_FILE.PUT_LINE(filehandle,'LOCAL');
  		--	UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
      --  IF indspcName IS NOT NULL THEN
      --    UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
      --    UTL_FILE.NEW_LINE(filehandle,1);
      --  END IF;
  		--	UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
  		--	UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
  		--	UTL_FILE.NEW_LINE(filehandle,2);
			END LOOP;
			partList.DELETE;
      UTL_FILE.NEW_LINE(filehandle,2);
      UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
                        eOwnerName);
      UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
      UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'' , granularity=>''GLOBAL'') ;');
      UTL_FILE.NEW_LINE(filehandle,2);
      UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
                        eOwnerName);
      UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
      UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');
   --   UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'' , granularity=>''GLOBAL'') ;');
      UTL_FILE.NEW_LINE(filehandle,3);


	  END LOOP;


--	  commit;
	  UTL_FILE.FCLOSE(filehandle);
--	  EXCEPTION
--	 		WHEN others THEN
-- 			UTL_FILE.PUT('others');

--END ;

	  commit;
	  EXCEPTION
      WHEN UTL_FILE.INVALID_PATH THEN
        UTL_FILE.FCLOSE(filehandle);
        DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
      WHEN UTL_FILE.INVALID_OPERATION THEN
        UTL_FILE.FCLOSE(filehandle);
        DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
      WHEN UTL_FILE.INVALID_FILEHANDLE THEN
        UTL_FILE.FCLOSE(filehandle);
        DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
      WHEN UTL_FILE.WRITE_ERROR THEN
        UTL_FILE.FCLOSE(filehandle);
        DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
      WHEN UTL_FILE.INTERNAL_ERROR THEN
        UTL_FILE.FCLOSE(filehandle);
        DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
	 		WHEN others THEN
  --      UTL_FILE.FCLOSE(filehandle);
	 			RAISE;
	  END ;

PROCEDURE CreateFactTab(runId IN NUMBER,exOwnerName IN varchar2,exCubeName IN varchar2,partitioning IN BOOLEAN) IS
-- This it the Procedure to Create Cube MV in ROLLUP form for a Cube. This procedure is used to write to a table for OEM
-- Example CreateFatMVTable(89,'XADEMO','ANALYTIC_CUBE','ANALYTIC_MV');

-- filehandle UTL_FILE.FILE_TYPE;
    eOwnerName varchar2(30);
    eCubeName varchar2(30);
 		eMVName varchar2(30);
--		eOutPath varchar2(30);
--		eOutFile varchar2(30);
    eDimName varchar2(30);
    eHierName varchar2(30);
    eLevelName varchar2(30);
    eLevelColName varchar2(30);
    eLeavColName varchar2(30);
    eFactName varchar2(30);
    eUnitName varchar2(30);
    ePosition NUMBER;
    eTempMV varchar2(30);
    eTabName varchar2(30);
    eTableName varchar2(30);
    eparLevelName varchar2(100);
    parTabName varchar2(30);
    parColName varchar2(30);
    dimCount INTEGER;
    i INTEGER;
    j INTEGER;
    k INTEGER;
    m INTEGER;
    n INTEGER;
    o INTEGER;
    z INTEGER;
    c NUMBER;
    flag INTEGER;
    numOfMv NUMBER;
    numOfdimMV NUMBER;
    tempMVnum INTEGER;
    highLvlCount INTEGER;
    highLvlNum INTEGER;
    tableCount INTEGER;
  	tempName varchar2(30);
  	bufString varchar2(32767);
  	indexString varchar2(32767);
  	parentString varchar2(300);
  	childString varchar2(300);
  	mvString varchar2(32767);
		mvnum INTEGER;
		num_rows NUMBER;
		num_bytes NUMBER;
		partgid NUMBER;
		partnum NUMBER;
		tempnum NUMBER;
		tpnum NUMBER;
		val NUMBER;
		tkval NUMBER;
		times NUMBER;
		ntimes NUMBER;
		loopval NUMBER;
		tempval NUMBER;
		tpval NUMBER;
		query_length NUMBER;
		whereFlag BOOLEAN;
		dupmvFlag BOOLEAN;
		dupdimmvFlag BOOLEAN;
		others EXCEPTION;
--    partition BOOLEAN;
    poscount NUMBER;

		TYPE dimType IS RECORD
		(dimName varchar2(30),
		 hierCount  INTEGER);

    TYPE dimHiertype IS RECORD
	   (dimName   varchar2(30),
	   hierName   varchar2(30),
	   hierNum    INTEGER,
	   dimNum     INTEGER,
	   levelCount INTEGER
       );

 		TYPE dimhierList_t is TABLE of dimHiertype;



--		TYPE levelName_t IS TABLE OF VARCHAR2(30)
--	   INDEX BY BINARY_INTEGER;

--  	TYPE levelPos_t IS TABLE OF VARCHAR2(30)
--	   INDEX BY BINARY_INTEGER;

 		TYPE hierLeveltype is RECORD
	   (dimName varchar2(30),
	    hierName varchar2(30),
	    levelName varchar2(30),
	    levelPos  varchar2(30),
	    levelNum INTEGER
	   );

 		TYPE hierLevelList_t is TABLE of hierLeveltype;



    TYPE mv_dimHier IS RECORD
	   (mvNum    INTEGER,
	    dimName  varchar2(30),
	    hierName varchar2(30),
	    lvlCount  INTEGER,
	    highestLvl BOOLEAN
	   );


		TYPE mvtablename IS RECORD
		(ownerName varchar2(30),
		 tabName varchar2(30)
		);

		TYPE parnumtable is RECORD
		(gidNum    NUMBER,
		ownerName  varchar2(30)
		);


		TYPE parnumtable_t is TABLE of parnumtable;

		TYPE mvtable_t is TABLE of mvtablename;


    TYPE mvtype_t IS TABLE of mv_dimHier;

   	TYPE dimList_t IS TABLE OF dimType;
    --    TYPE dimList IS TABLE OF ALL_OLAP_FACT_LEVEL_USES.DIMENSION_NAME%TYPE

    TYPE levList_t IS TABLE OF ALL_OLAP_DIM_LEVELS.LEVEL_NAME%TYPE;

    TYPE hierarchyList_t IS TABLE OF ALL_OLAP_DIM_HIERARCHIES.DIMENSION_NAME%TYPE;

    TYPE colLvlList_t IS TABLE OF ALL_OLAP_LEVEL_KEY_COLUMN_USES.COLUMN_NAME%TYPE;



		CURSOR curDimName IS SELECT DIMENSION_NAME
   	FROM ALL_OLAP_FACT_LEVEL_USES WHERE FACT_TABLE_NAME= eFactName AND
   	OWNER= eOwnerName AND CUBE_NAME= eCubeName;

		CURSOR curLevelName IS SELECT V.L
	   FROM ( SELECT CHILD_LEVEL_NAME L, POSITION P
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
	   UNION
	         SELECT PARENT_LEVEL_NAME L, POSITION+1
	   FROM ALL_OLAP_DIM_HIER_LEVEL_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName) V
	   ORDER BY V.P DESC;

		CURSOR curHierarchyName IS SELECT HIERARCHY_NAME
   		FROM ALL_OLAP_DIM_HIERARCHIES WHERE
				 OWNER= eOwnerName AND
				 DIMENSION_NAME= eDimName;

		CURSOR curColName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
	         AND DIMENSION_NAME= eDimName ;

  	CURSOR curTabName IS SELECT LEVEL_TABLE_NAME
	   FROM ALL_OLAP_DIM_LEVELS
	   WHERE OWNER=  eOwnerName AND LEVEL_NAME= eLevelName
           AND DIMENSION_NAME= eDimName;

		CURSOR curParentName IS SELECT TABLE_NAME || '.' || COLUMN_NAME, TABLE_NAME
	   FROM ALL_OLAP_JOIN_KEY_COLUMN_USES
	   WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
		 AND HIERARCHY_NAME= eHierName
		 AND CHILD_LEVEL_NAME= eLevelName
     AND POSITION = ePosition;


    CURSOR curLeavName IS SELECT distinct a.column_name
		FROM ALL_OLAP_KEY_COLUMN_USES a, ALL_OLAP_FOREIGN_KEYS b,
     ALL_OLAP_FACT_LEVEL_USES c
		where a.table_name = b.table_name
      and a.position = ePosition
		  and   a.key_name = b.foreign_key_name
		  and   b.table_name = c.fact_table_name
		  and   b.foreign_key_name = c.foreign_key_name
		  and   c.dimension_name = eDimName
		  and   c.level_name = eLevelName
		  and   c.dimension_owner = eOwnerName
		  and   c.fact_table_owner = eOwnerName
		  and   c.fact_table_name = eFactName;

		CURSOR curFactName IS SELECT distinct c.fact_table_name
		from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
		where a.owner = b.owner
		and a.cube_name = b.cube_name
		and b.owner = c.owner
		and b.cube_name = c.cube_name
		and b.measure_name = c.measure_name
		and a.owner = eOwnerName
		and a.cube_name = eCubeName;

		CURSOR curUnitName IS SELECT distinct c.column_name
		from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
		where a.owner = b.owner
		and a.cube_name = b.cube_name
		and b.owner = c.owner
		and b.cube_name = c.cube_name
		and b.measure_name = c.measure_name
		and a.owner = eOwnerName
		and a.cube_name = eCubeName;

		CURSOR checkName IS SELECT QUERY from ALL_MVIEWS
		where owner= eOwnerName
		and QUERY_LEN= query_length;


		CURSOR curLevDimName IS SELECT LEVEL_NAME
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

	  CURSOR curColDimName IS SELECT COLUMN_NAME, POSITION
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;

		dimHierList dimhierList_t:= dimhierList_t();
		hierLevelList hierLevelList_t:= hierLevelList_t();
		dimList dimList_t:= dimList_t();
		levList levList_t:= levList_t();
 		hierarchyList hierarchyList_t:= hierarchyList_t();
 		colLvlList colLvlList_t:= colLvlList_t();
 		partList dimhierList_t:= dimhierList_t();
 		mvtableList mvtable_t:= mvtable_t();
		parnum parnumtable_t := parnumtable_t();


   	mvList	  	mvtype_t:= mvtype_t();
   	mvListinc		mvtype_t:= mvtype_t();
   	mvListdec		mvtype_t:= mvtype_t();
    mvdimList   mvtype_t:= mvtype_t();


		BEGIN
	--	CREATE TABLE SYSTEM.MVIEW$_ADV_INDEX (RUNID# NUMBER NOT NULL,
	--  																			RANK# NUMBER NOT NULL,
	--  																			SUMMARY_OWNER VARCHAR2(32),
	--  																			INDEX_TEXT LONG);
    numOfMV:= 1;
    numOfdimMV:= 1;
   -- eOutPath:= '/users/oracle';
    eOwnerName:= UPPER(exOwnerName);
    eCubeName:= UPPER(exCubeName);
 cwm2_olap_metadata_refresh.mr_refresh;
--    IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
-- 			filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
--    END IF;
    IF NOT curFactName%ISOPEN THEN
			OPEN curFactName;
			LOOP
				FETCH curFactName INTO eFactName;
				EXIT WHEN curFactName%NOTFOUND;
			END LOOP;
			CLOSE CurFactName;
    END IF;
    IF eFactName IS NULL THEN
      RAISE UTL_FILE.INTERNAL_ERROR;
    END IF;
    IF NOT curFactName%ISOPEN THEN
			OPEN curFactName;
			LOOP
				FETCH curFactName INTO eFactName;
				EXIT WHEN curFactName%NOTFOUND;
			END LOOP;
			CLOSE CurFactName;
    END IF;

    IF NOT curDimName%ISOPEN THEN
			OPEN curDimName;
			i:= 0;
			LOOP
				FETCH curDimName INTO tempName;
				EXIT WHEN curDimName%NOTFOUND;
				dimList.EXTEND;

				i:= i + 1;
					dimList(i).dimName:= tempName;
--					dimList(i):= i;
			END LOOP;
			CLOSE CurDimName;
    END IF;
    dimCount:= dimList.Count;
    j:= 0;
 		FOR i in 1..dimList.Count LOOP
			eDimName:= dimList(i).dimName;
      k:= 0;
			IF NOT curHierarchyName%ISOPEN THEN
   			OPEN curHierarchyName;
			END IF;
			LOOP
				FETCH curHierarchyName into tempName;
				EXIT WHEN curHierarchyName%NOTFOUND;
   			j:= j + 1;
   			k:= k + 1;
   			dimHierList.EXTEND;
      	dimHierList(j).dimName := eDimName;
				dimHierList(j).dimNum := i;
   			dimHierList(j).hierName:= tempName;
   			dimHierList(j).hierNum:= k;
			END LOOP;
			CLOSE curHierarchyName;
			IF k = 0 THEN
				j:= j + 1;
   			k:= k + 1;
   			dimHierList.EXTEND;
      	dimHierList(j).dimName := eDimName;
				dimHierList(j).dimNum := i;
   			dimHierList(j).hierName:= NULL;
   			dimHierList(j).hierNum:= k;
   		END IF;
			dimList(i).hierCount:= k;
		END LOOP;
    j:= 0;
		FOR i in 1..dimHierList.Count LOOP
   		eDimName:= dimHierList(i).dimName;
   		eHierName:= dimHierList(i).hierName;
   		k:= 0;
 			IF NOT curLevelName%ISOPEN THEN
    		OPEN curLevelName;
 			END IF;
 			LOOP
	   	 	FETCH curLevelName into tempName;
	   	 	EXIT WHEN curLevelName%NOTFOUND;
	      k:= k + 1;
	      j:= j + 1;
	      hierLevelList.EXTEND;
				hierLevelList(j).dimName:= eDimName;
				hierLevelList(j).hierName:= eHierName;
	      hierLevelList(j).levelName:= tempName;
	      hierLevelList(j).levelNum:= k;
	    --  hierLevelList(j).levelPos.EXTEND;
	    --  heirLevelList(j).levelName(k):= tempName;
 			END LOOP;
 			CLOSE curLevelName;
 			IF k= 0 THEN
 				IF NOT curLevDimName%ISOPEN THEN
    		OPEN curLevDimName;
 				END IF;
 				LOOP
	   	 		FETCH curLevDimName into tempName;
	   	 		EXIT WHEN curLevDimName%NOTFOUND;
	      	k:= k + 1;
	      	j:= j + 1;
	      	hierLevelList.EXTEND;
					hierLevelList(j).dimName:= eDimName;
					hierLevelList(j).hierName:= NULL;
	     	 	hierLevelList(j).levelName:= tempName;
	      	hierLevelList(j).levelNum:= k;
 				END LOOP;
 					CLOSE curLevDimName;
	    END IF;
    	dimHierList(i).levelCount:= k;

	 	END LOOP;

	    numOfMV:= 1;
	    FOR i in 1..dimList.Count LOOP
	  		numOfMV:= numOfMV * dimList(i).hierCount;
	    END LOOP;

			m:= 0;
			n:= 0;
			flag:= 1;
	    FOR i in 1..dimList.Count LOOP
	   		tempMVnum:= numOfMV / dimList(i).hierCount;
	   		eDimName:= dimList(i).dimName;
	   		n:= 0;
	   		IF flag= 1 THEN
	   			FOR j in 1..dimList(i).hierCount LOOP
	 					FOR l in 1..dimHierList.Count LOOP
				 				IF dimHierList(l).dimName= eDimName AND
				 					dimHierList(l).hierNum= j THEN
				 					FOR k in 1..tempMVnum LOOP
				 						eHierName:= dimHierList(l).hierName;
				 						m:= m + 1;
				 						n:= n + 1;
				 						flag:= 0;
				 						mvList.EXTEND;
				 						mvList(m).mvNum:= n;
				 						mvList(m).dimName:= eDimName;
				 						mvList(m).hierName:= eHierName;
				 					END LOOP;
		 						END IF;
		 					END LOOP;
		   			END LOOP;
				ELSE
						FOR k in 1..tempMVnum LOOP
							FOR j in 1..dimList(i).hierCount LOOP
			 				FOR l in 1..dimHierList.Count LOOP
		 						IF dimHierList(l).dimName= eDimName AND
		 							dimHierList(l).hierNum= j THEN
		 							eHierName:= dimHierList(l).hierName;
		 							m:= m + 1;
		 							n:= n + 1;
		 							flag:= 1;
		 							mvList.EXTEND;
		 							mvList(m).mvNum:= n;
		 							mvList(m).dimName:= eDimName;
		 							mvList(m).hierName:= eHierName;
									END IF;
								END LOOP;
							END LOOP;
						END LOOP;
	   		END IF;
	   	END LOOP;
	 		FOR i in 1..mvList.Count LOOP
	     		eDimName:= mvList(i).dimName;
	     		eHierName:= mvList(i).hierName;
	     		FOR j in 1..dimHierList.Count LOOP
	     			IF eDimName = dimHierList(j).dimName THEN
	     				IF (eHierName = dimHierList(j).hierName OR
	     					   dimHierList(j).hierName IS NULL ) THEN
	     					mvList(i).lvlCount:= dimHierList(j).levelCount;
	     					mvList(i).highestLvl:= FALSE;
	     				END IF;
	     			END IF;
	     		END LOOP;
	    END LOOP;
			FOR i in 1..numOfMV LOOP
	   		highLvlCount:= 0;
	   		highLvlNum:= 1;
	   		FOR j in 1..mvList.Count LOOP
	   			IF mvList(j).mvNum = i AND
	   				mvList(j).lvlCount >= highLvlCount THEN
	   				highLvlCount:= mvList(j).lvlCount;
	   				highLvlNum:= j;
	   			END IF;
	   		END LOOP;
	   		mvList(highLvlNum).highestLvl:= TRUE;
	  	END LOOP;

			 m:= 0;
		--	 	highLvlCount:= 0;
		   FOR i in 1..numOfMV LOOP
			   	j:= 1;
			   	FOR j in 1..mvList.Count LOOP
			   		IF mvList(j).mvNum = i AND
			   			mvList(j).highestLvl= TRUE THEN
			   				highLvlCount:= mvList(j).LvlCount;
			   	--		EXIT LOOP;
			   		END IF;
			   	END LOOP;
	--		  END LOOP;
			--  	WHILE j <= mvList.Count AND
	    --			mvList(j).mvNum= i
	    --			LOOP
	    --				highLvlCount:= mvList(j).LvlCount;
	    --				j:= j+ 1;
	  --  			EXIT WHEN mvList(j).highestLvl = TRUE;
	    --		END LOOP;
		--	 		FOR j in 1..mvList.Count LOOP
		--	 			IF mvList(j).mvNum= i AND
		--	 				mvList(j).highestLvl= TRUE THEN
		--	 				highLvlNum:= mvList(j).lvlCount;
		--	 				EXIT;
		--	 			END IF;
		--	 		END LOOP;
			 		FOR k in REVERSE 0..highLvlCount LOOP
						FOR j in 1..mvList.Count LOOP
							IF mvList(j).mvNum = i AND
								mvList(j).lvlCount = k THEN
								m:= m + 1;
								mvListdec.EXTEND;
								mvListdec(m).mvNum:= i;
								mvListdec(m).dimName:= mvList(j).dimName;
								mvListdec(m).hierName:= mvList(j).hierName;
								mvListdec(m).lvlCount:= mvList(j).lvlCount;
								mvListdec(m).highestLvl:= mvList(j).highestLvl;
							END IF;
						END LOOP;
			 		END LOOP;
				END LOOP;


     	FOR i in 1..mvListdec.Count LOOP
        mvListdec(i).highestLvl:= FALSE;
      END LOOP;

      FOR i in 1..numOfMV LOOP
	   		highLvlCount:= 0;
	   		highLvlNum:= 1;
	   		FOR j in 1..mvListdec.Count LOOP
	   			IF mvListdec(j).mvNum = i THEN
	   				IF highLvlCount = 0 THEN
	   					highLvlCount:= mvListdec(j).lvlCount;
	   				END IF;
	   				IF mvListdec(j).lvlCount <= highLvlCount THEN
	   					highLvlCount:= mvListdec(j).lvlCount;
	   					highLvlNum:= j;
	   				END IF;
	   			END IF;
	   		END LOOP;
	   		mvListdec(highLvlNum).highestLvl:= TRUE;
	  	END LOOP;
		m:= 0;
    FOR i in 1..numOfMV LOOP
    	j:= 1;
  		FOR j in 1..mvList.Count LOOP
	   		IF mvList(j).mvNum = i AND
	   			mvList(j).highestLvl= TRUE THEN
	   				highLvlCount:= mvList(j).LvlCount;
	   	--		EXIT LOOP;
	   		END IF;
		  END LOOP;
    --		WHILE j <= mvList.Count AND
    --			mvList(j).mvNum= i AND
    --			mvList(j).highestLvl= TRUE
    --		LOOP
    --				highLvlCount:= mvList(j).LvlCount;
    --				j:= j+ 1;
    --		END LOOP;
    -- 		FOR j in 1..mvList.Count LOOP
    -- 			IF mvList(j).mvNum= i AND
    -- 				mvList(j).highestLvl= TRUE THEN
    -- 				highLvlNum:= mvList(j).lvlCount;
    -- 				EXIT;
    -- 			END IF;
    -- 		END LOOP;
     		FOR k in 0..highLvlCount LOOP
     			FOR j in 1..mvList.Count LOOP
     				IF mvList(j).mvNum = i AND
     					mvList(j).lvlCount = k THEN
     					m:= m + 1;
     					mvListinc.EXTEND;
     					mvListinc(m).mvNum:= i;
     					mvListinc(m).dimName:= mvList(j).dimName;
     					mvListinc(m).hierName:= mvList(j).hierName;
     					mvListinc(m).lvlCount:= mvList(j).lvlCount;
     					mvListinc(m).highestLvl:= mvList(j).highestLvl;
     				END IF;
     			END LOOP;
     		END LOOP;
    END LOOP;
  --  INSERT INTO SYSTEM.MVIEW$_ADV_LOG(RUNID#,STATUS) VALUES
 	--  														(runid,1);
  	FOR i in 1..numofMV LOOP
		  --UTL_PUT(filehandle,buffer)
		  eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
--		  UTL_FILE.PUT_LINE(filehandle,'create materialized view');
--		  UTL_FILE.PUT(filehandle,eMVName);
--		  UTL_FILE.NEW_LINE(filehandle,1);
--		  UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
	--	  UTL_FILE.NEW_LINE(filehandle,1);
	--	  UTL_FILE.PUT_LINE(filehandle,'parallel');

			dupmvFlag:= FALSE;
  --    partition:= TRUE;
			IF partitioning = TRUE THEN
				partgid:= 0;
				partnum:= 0;
				o:= 0;
				indexString:= '';
				indexString:= indexString || 'partition by range (gid) (';
--				UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
				FOR j in 1..mvListdec.Count LOOP
		  		IF mvListdec(j).mvNum= i THEN
	    			edimName:= mvListdec(j).dimName;
	      		ehierName:= mvListdec(j).hierName;
	      		FOR l in 1..dimHierList.Count LOOP
	      			IF eDimName = dimHierList(l).dimName AND
		     				eHierName = dimHierList(l).hierName THEN
		     				o:= o + 1;
		     				partList.EXTEND;
	      				partList(o).dimName:= dimHierList(l).dimName;
	      				partList(o).hierName:= dimHierList(l).hierName;
								partList(o).dimNum:= dimHierList(l).dimNum;
	   						partList(o).hierNum:= dimHierList(l).hierNum;
	   						partList(o).levelCount:= dimHierList(l).levelCount;
	      			ELSIF eDimName = dimHierList(l).dimName AND
		     				    dimHierList(l).hierName IS NULL THEN
	      				o:= o + 1;
		     				partList.EXTEND;
	      				partList(o).dimName:= dimHierList(l).dimName;
	      				partList(o).hierName:= dimHierList(l).hierName;
								partList(o).dimNum:= dimHierList(l).dimNum;
	   						partList(o).hierNum:= dimHierList(l).hierNum;
	   						partList(o).levelCount:= dimHierList(l).levelCount;

	      			END IF;
	      		END LOOP;
	      	END IF;
				END LOOP;
				times:= 1;
				FOR k in 1..partList.Count LOOP
		     	times:= times * partList(k).levelCount;
				END LOOP;

				FOR k in 1..times LOOP
					parnum.EXTEND;
					parnum(k).gidNum:= 0;
				END LOOP;

				val:= 0;
				ntimes:= 1;
				loopval:= 1;
				tkval:= 0;
				tpnum:= 0;
				FOR k in 1..partList.Count LOOP
					c:= 0;
					tempMVNum:= times/ partList(k).levelCount;
					IF k= 1 THEN
						FOR z in 1..tempMVNum LOOP
							FOR m in 1..partList(k).levelCount LOOP
								c:= c + 1;
								parnum(c).gidNum:= val;
								IF z <> tempMVNum THEN
									IF m= partList(k).levelCount THEN
										val:= 0;
									ELSE
										IF val= 0 THEN
											val:= 1;
										ELSE
                      val:= (val * 2) + 1;
									--		tkval:= m - 1;
									--		tpnum:= tkval * 2;
									--		val:= val + tpnum;
										END IF;
									END IF;
								ELSE
									IF m = partList(k).levelCount THEN
										tpnum:= val + 1;
										val:= tpnum * 2;
										tkval:= val;
									ELSE
										IF val= 0 THEN
											val:= 1;
										ELSE
								--			tkval:= m - 1;
								--			tpnum:= tkval * 2;
								--			val:= val + tpnum;
                      val:= (val * 2) + 1;
											tkval:= val;
										END IF;
									END IF;
								END IF;
							END LOOP;
						END LOOP;
						loopval:= loopval * partList(k).levelCount;
					ELSE
						ntimes:= 1;
						ntimes:= tempMVNum / loopval;
						val:= 0;
--						tkval:= 0;
						FOR i in 1..ntimes LOOP
							FOR j in 1..partList(k).levelCount LOOP
							  tempval:= loopval / partList(1).levelCount;
								FOR v in 1..tempval LOOP
									FOR h in 1..partList(1).levelCount LOOP
										c:= c + 1;
									 	parnum(c).gidNum:= parnum(c).gidNum + val;
									END LOOP;
								END LOOP;
								IF j = partList(k).levelCount THEN
									IF i= ntimes THEN
										tkval:= tpnum * 4;
										val:= 0;
									ELSE
										tpnum:= tkval;
										val:= 0;
									END IF;
								ELSE
									IF val= 0 THEN
										tpnum:= tkval;
										val:= val + tpnum;
									ELSE
										tpnum:= tpnum * 2;
										val:= val + tpnum;
									END IF;
								END IF;
							END LOOP;
						END LOOP;
						loopval:= loopval * partList(k).levelCount;
					END IF;
				END LOOP;

				FOR k in 1..times LOOP
					IF k > 1 THEN
						IF k < 800 THEN
							indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';

						END IF;
--						UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
--						UTL_FILE.NEW_LINE(filehandle,1);
					END IF;
				END LOOP;
 	      indexString:= indexString || ' partition values less than(MAXVALUE))';
-- 	      UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
	      partList.DELETE;
	      parnum.DELETE;
      END IF;
--      UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
 --     IF mvspcName IS NOT NULL THEN
--        UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
--        UTL_FILE.NEW_LINE(filehandle,1);
 --     END IF;
--      UTL_FILE.PUT_LINE(filehandle,'build immediate');
--      UTL_FILE.PUT_LINE(filehandle,'using no index');
--      UTL_FILE.PUT_LINE(filehandle,'refresh force');
--		  UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
--		  UTL_FILE.PUT_LINE(filehandle,'AS');
--		  UTL_FILE.PUT_LINE(filehandle,'SELECT');
		 	bufString:= bufString || 'SELECT ';

--      UTL_FILE.PUT(filehandle,'GROUPING_ID(');
      bufString:= bufString || 'GROUPING_ID(';
	  	FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
    			edimName:= mvListinc(j).dimName;
      		ehierName:= mvListinc(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eDimName AND
	     				hierLevelList(l).hierName= eHierName THEN
			     		eLevelName:= hierLevelList(l).levelName;
			  			IF NOT curColName%ISOPEN THEN
			  				OPEN curColName;
			  			END IF;
			  			LOOP
			  				FETCH curColName into eLevelColName,ePosition;
			  				EXIT WHEN curColName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curColName;
			  			IF NOT curTabName%ISOPEN THEN
			  				OPEN curTabName;
			  			END IF;
			  			LOOP
			  				FETCH curTabName into eTabName;
			  				EXIT WHEN curTabName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curTabName;
	--		  			UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
			          --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName;

			  			bufString:= bufString  
                                                            || dbms_assert.enquote_name(eTabName) 
                                                            || '.' 
                                                            || dbms_assert.enquote_name(eLevelColName);

			  			IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
--			  				UTL_FILE.PUT_LINE(filehandle,',');
								bufString:= bufString || ', ';
			  			END IF;

			  		ELSIF hierLevelList(l).dimName = eDimName AND
		  				hierLevelList(l).hierName IS NULL THEN
		  				eLevelName:= hierLevelList(l).levelName;
		  				IF NOT curColDimName%ISOPEN THEN
		  					OPEN curColDimName;
		  				END IF;
		  				LOOP
		  					FETCH curColDimName into eLevelColName,ePosition ;
		  					EXIT WHEN curColDimName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curColDimName;
		  				IF NOT curTabName%ISOPEN THEN
		  					OPEN curTabName;
		  				END IF;
		  				LOOP
		  					FETCH curTabName into eTabName;
		  					EXIT WHEN curTabName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curTabName;
--	 	  			 	UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
		  	          --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName;

		  				bufString:= bufString 
                                                            || dbms_assert.enquote_name(eTabName) 
                                                            || '.' 
                                                            || dbms_assert.enquote_name(eLevelColName);

			  			IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
--			  				UTL_FILE.PUT_LINE(filehandle,',');
								bufString:= bufString || ', ';
			  			END IF;
  					END IF;
      		END LOOP;
      		IF mvListinc(j).highestLvl = FALSE THEN
--      			UTL_FILE.PUT_LINE(filehandle,',');
      			bufString:= bufString || ', ';
      		END IF;
	  		END IF;
	  	END LOOP;
--      UTL_FILE.PUT_LINE(filehandle,') gid,');
		  bufString:= bufString || ') gid, ';
		  IF NOT curUnitName%ISOPEN THEN
			OPEN curUnitName;
			LOOP
				FETCH curUnitName INTO eUnitName;
				EXIT WHEN curUnitName%NOTFOUND;
--				UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
--				UTL_FILE.NEW_LINE(filehandle,1);
	 --			UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
	 --			UTL_FILE.NEW_LINE(filehandle,1);
		  --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';

				bufString:= bufString 
                                            || 'SUM(' 
                                            || dbms_assert.enquote_name(eFactName) 
                                            || '.' 
                                            || dbms_assert.enquote_name(eUnitName)
                                            || ') ';

	          --Bug 8915150 bufString:= bufString || 'SUM_OF_' || eUnitName || ', ';

			        bufString:= bufString 
                                            || 'SUM_OF_' 
                                            || dbms_assert.enquote_name(eUnitName)
                                            || ', ';

	--			bufString:= bufString || 'COUNT(' || eFactName || '.' || eUnitName || ') ';
	--			bufString:= bufString || 'COUNT_OF_' || eUnitName || ', ';
			END LOOP;
			CLOSE CurUnitName;
    	END IF;
--			UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');
			bufString:= bufString || 'COUNT(*) COUNT_OF_STAR, ';

			FOR j in 1..mvListdec.Count LOOP
  			IF mvListdec(j).mvNum= i THEN
      		edimName:= mvListdec(j).dimName;
        	ehierName:= mvListdec(j).hierName;
	     	  FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= eDimName AND
		     			hierLevelList(l).hierName= ehierName THEN
		     			eLevelName:= hierLevelList(l).levelName;
			  			IF NOT curColName%ISOPEN THEN
			  				OPEN curColName;
			  			END IF;
			  			LOOP
			  				FETCH curColName into eLevelColName,ePosition ;
			  				EXIT WHEN curColName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curColName;
			  			IF NOT curTabName%ISOPEN THEN
			  				OPEN curTabName;
			  			END IF;
			  			LOOP
			  				FETCH curTabName into eTabName;
			  				EXIT WHEN curTabName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curTabName;
--			  			UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
			  --	  	UTL_FILE.NEW_LINE(filehandle,1);

			          --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName || '_' || l;

			  		        bufString:= bufString 
                                                            || dbms_assert.enquote_name(eTabName)
                                                            || '.'
                                                            || dbms_assert.enquote_name(eLevelColName) 
                                                            || ' ' 
                                                            || dbms_assert.enquote_name(eLevelColName) 
                                                            || '_' 
                                                            || l;

              IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
 --			  				UTL_FILE.PUT_LINE(filehandle,',');
								bufString:= bufString || ', ';
			  			END IF;
			  		ELSIF hierLevelList(l).dimName = eDimName AND
			  				hierLevelList(l).hierName IS NULL THEN
			  				eLevelName:= hierLevelList(l).levelName;
			  				IF NOT curColDimName%ISOPEN THEN
			  					OPEN curColDimName;
			  				END IF;
			  				LOOP
			  					FETCH curColDimName into eLevelColName,ePosition ;
			  					EXIT WHEN curColDimName%NOTFOUND;
			  				END LOOP;
			  				CLOSE curColDimName;
			  				IF NOT curTabName%ISOPEN THEN
			  					OPEN curTabName;
			  				END IF;
			  				LOOP
			  					FETCH curTabName into eTabName;
			  					EXIT WHEN curTabName%NOTFOUND;
			  				END LOOP;
			  				CLOSE curTabName;
	 		--  				UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
			  	          --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName || '_' || l ;

			  				bufString:= bufString 
                                                                    || dbms_assert.enquote_name(eTabName) 
                                                                    || '.' 
                                                                    || dbms_assert.enquote_name(eLevelColName) 
                                                                    || ' ' 
                                                                    || dbms_assert.enquote_name(eLevelColName) 
                                                                    || '_' 
                                                                    || l ;

                IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
			--  				  UTL_FILE.PUT_LINE(filehandle,',');
								  bufString:= bufString || ', ';
			  			  END IF;
		       	END IF;
	     	  END LOOP;
          IF mvListdec(j).highestLvl = FALSE THEN
      --			UTL_FILE.PUT_LINE(filehandle,',');
      			bufString:= bufString || ', ';
      		END IF;
  			END IF;
  		END LOOP;
     -- UTL_FILE.NEW_LINE(filehandle,1);
   	--	UTL_FILE.PUT_LINE(filehandle,'FROM');
  		o:= 0;
  		bufString:= bufString || ' FROM ';
  		FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
	      	FOR l in 1..hierLevelList.Count LOOP
	      		IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName AND
	      			mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
		  					eLevelName:=  hierLevelList(l).levelName;
			  				IF NOT curColName%ISOPEN THEN
			  					OPEN curColName;
			  				END IF;
				  			LOOP
				  				FETCH curColName into eLevelColName,ePosition;
				  				EXIT WHEN curColName%NOTFOUND;
				  			END LOOP;
				  			CLOSE curColName;
				  			IF NOT curTabName%ISOPEN THEN
				  				OPEN curTabName;
				  			END IF;
				  			LOOP
				  				FETCH curTabName into eTabName;
				  				EXIT WHEN curTabName%NOTFOUND;
				  			END LOOP;
				  			CLOSE curTabName;
				  			o:= o + 1;
				  			mvtableList.EXTEND;
				  			mvtableList(o).tabName:= eTabName;
				  			mvtableList(o).ownerName:= eOwnerName;
		--		  			UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
		--		  			UTL_FILE.PUT_LINE(filehandle,' ');
		--		  			bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
							ELSIF hierLevelList(l).dimName = eDimName AND
		  					hierLevelList(l).hierName IS NULL THEN
		  					eLevelName:= hierLevelList(l).levelName;
		  					IF NOT curColDimName%ISOPEN THEN
		  						OPEN curColDimName;
		  					END IF;
		  					LOOP
		  						FETCH curColDimName into eLevelColName,ePosition ;
		  						EXIT WHEN curColDimName%NOTFOUND;
		  					END LOOP;
		  					CLOSE curColDimName;
		  					IF NOT curTabName%ISOPEN THEN
		  						OPEN curTabName;
		  					END IF;
		  					LOOP
		  						FETCH curTabName into eTabName;
		  						EXIT WHEN curTabName%NOTFOUND;
		  					END LOOP;
		  					CLOSE curTabName;
		  					o:= o + 1;
				  			mvtableList.EXTEND;
				  			mvtableList(o).tabName:= eTabName;
				  			mvtableList(o).ownerName:= eOwnerName;
			  		END IF;
	       	END LOOP;
		   	END IF;
  		END LOOP;
  		FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
		  		FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  			--	mvnum:= mvnum + 1;
		  				IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
		  				LOOP
		  					FETCH curColName into eLevelColName,ePosition ;
		  					EXIT WHEN curColName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curColName;
		 	 				IF NOT curTabName%ISOPEN THEN
		  					OPEN curTabName;
		  				END IF;
		  				LOOP
		  					FETCH curTabName into eTabName;
		  					EXIT WHEN curTabName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curTabName;
	  					parentString:= eTabName || '.' || eLevelColName;
	  					childString:= parentString;
	  					IF NOT curParentName%ISOPEN THEN
	  						OPEN curParentName;
	  					END IF;
	  					LOOP
	  						FETCH curParentName into eparLevelName,etableName;
	  					EXIT WHEN curParentName%FOUND;
	  					END LOOP;
	  					CLOSE curParentName;
	  					childString:= eparLevelName;
	  					IF childString <> parentString THEN
	  						tableCount:= mvtableList.Count;
	  						z:= 1;
	  						whereFlag:= FALSE;
	  						WHILE z <= tableCount LOOP
	  							IF mvtableList(z).tabName = eTabName THEN
	  								whereFlag:= TRUE;
	  							END IF;
	  							EXIT WHEN mvtableList(z).tabName= eTabName;
	  							z:= z + 1;
	  						END LOOP;
	  						IF whereFlag= FALSE THEN
	  							o:= o +1;
	  							mvtableList.EXTEND;
				  				mvtableList(o).tabName:= eTabName;
				  				mvtableList(o).ownerName:= eOwnerName;
				  			END IF;
		  				--		 bufString:= bufString || ' AND ';
	  					--	UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
	  					--	bufString:= bufString || '(' || childString || '=' || parentString || ') ';
	  					END IF;
            ELSIF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= NULL THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  			--	mvnum:= mvnum + 1;
		  				IF NOT curColDimName%ISOPEN THEN
		  					OPEN curColDimName;
		  				END IF;
		  				LOOP
		  					FETCH curColDimName into eLevelColName,ePosition ;
		  					EXIT WHEN curColDimName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curColDimName;
		 	 				IF NOT curTabName%ISOPEN THEN
		  					OPEN curTabName;
		  				END IF;
		  				LOOP
		  					FETCH curTabName into eTabName;
		  					EXIT WHEN curTabName%NOTFOUND;
		  				END LOOP;
		  				CLOSE curTabName;
	  					parentString:= eTabName || '.' || eLevelColName;
	  					childString:= parentString;
	  					IF NOT curParentName%ISOPEN THEN
	  						OPEN curParentName;
	  					END IF;
	  					LOOP
	  						FETCH curParentName into eparLevelName,etableName;
	  					EXIT WHEN curParentName%FOUND;
	  					END LOOP;
	  					CLOSE curParentName;
	  					childString:= eparLevelName;
	  					IF childString <> parentString THEN
	  						tableCount:= mvtableList.Count;
	  						z:= 1;
	  						whereFlag:= FALSE;
	  						WHILE z <= tableCount LOOP
	  							IF mvtableList(z).tabName = eTabName THEN
	  								whereFlag:= TRUE;
	  							END IF;
	  							EXIT WHEN mvtableList(z).tabName= eTabName;
	  							z:= z + 1;
	  						END LOOP;
	  						IF whereFlag= FALSE THEN
	  							o:= o +1;
	  							mvtableList.EXTEND;
				  				mvtableList(o).tabName:= eTabName;
				  				mvtableList(o).ownerName:= eOwnerName;
				  			END IF;
		  				--		 bufString:= bufString || ' AND ';
	  					--	UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
	  					--	bufString:= bufString || '(' || childString || '=' || parentString || ') ';
              END IF;
						END IF;
		  		END LOOP;
	  		END IF;
			END LOOP;
			FOR o in 1..mvtableList.Count LOOP
 	  --Bug 8915150 bufString:= bufString || mvtableList(o).ownerName || '.' || mvtableList(o).tabName || ' '
 	  --Bug 8915150						|| mvtableList(o).tabName || ', ';

 		  	bufString:= bufString 
                                    || dbms_assert.enquote_name(mvtableList(o).ownerName) 
                                    || '.' 
                                    || dbms_assert.enquote_name(mvtableList(o).tabName)
                                    || ' '
 		  		    || dbms_assert.enquote_name(mvtableList(o).tabName) 
                                    || ', ';
-- 		  	UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
 --		  																				mvtableList(o).tabName);
 --				UTL_FILE.PUT_LINE(filehandle,' ');
			END LOOP;
			mvtableList.DELETE;
--			UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
--		  UTL_FILE.PUT_LINE(filehandle,' ');
--			UTL_FILE.PUT_LINE(filehandle,'WHERE');
			whereFlag:= FALSE;
	  --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';

			bufString:= bufString 
                                    || dbms_assert.enquote_name(eOwnerName)
                                    || '.'
                                    || dbms_assert.enquote_name(eFactName)
                                    || ' '
                                    || dbms_assert.enquote_name(eFactName) 
                                    || ' ';

			bufString:= bufString || ' ' || 'WHERE ';
			mvnum:= 0;

      FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
		  		FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName AND
	      			mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  				mvnum:= mvnum + 1;
		  				IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
              poscount:= 0;
              LOOP
		  					FETCH curColName into eLevelColName,ePosition;
                EXIT WHEN curColName%NOTFOUND;
                poscount:= poscount + 1;
              END LOOP;
              CLOSE curColName;
              IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
		  				LOOP
		  					FETCH curColName into eLevelColName,ePosition;
                EXIT WHEN curColName%NOTFOUND;
	              IF NOT curTabName%ISOPEN THEN
		  					  OPEN curTabName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curTabName into eTabName;
		  					  EXIT WHEN curTabName%NOTFOUND;
		  				  END LOOP;
		  				  CLOSE curTabName;
                IF NOT curLeavName%ISOPEN THEN
		  					  OPEN curLeavName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curLeavName into eLeavColName ;
		  					  EXIT WHEN curLeavName%FOUND;
		  				  END LOOP;
		  				  CLOSE curLeavName;
--                UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
  --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || ' = ' || eFactName || '.' || eLeavColName || ') ';

                bufString:= bufString
                            || '('
                            || dbms_assert.enquote_name(eTabName)
                            || '.'
                            || dbms_assert.enquote_name(eLevelColName) 
                            || ' = '
                            || dbms_assert.enquote_name(eFactName)
                            || '.' 
                            || dbms_assert.enquote_name(eLeavColName) 
                            || ') ';

                IF ePosition < poscount THEN
--                  UTL_FILE.PUT_LINE(filehandle,' AND');
                  bufString:= bufString || ' AND ';
                END IF;
              END LOOP;
              CLOSE curColName;
						ELSIF hierLevelList(l).dimName = eDimName AND
	  					hierLevelList(l).hierName IS NULL THEN
	  					eLevelName:= hierLevelList(l).levelName;
              mvnum:= mvnum + 1;
	  					IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
              poscount:= 0;
              LOOP
		  					FETCH curColDimName into eLevelColName,ePosition;
                EXIT WHEN curColDimName%NOTFOUND;
                poscount:= poscount + 1;
              END LOOP;
              CLOSE curColDimName;
              IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
	  					LOOP
	  						FETCH curColDimName into eLevelColName,ePosition ;
	  						EXIT WHEN curColDimName%NOTFOUND;
                IF NOT curTabName%ISOPEN THEN
	  						  OPEN curTabName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curTabName into eTabName;
	  						  EXIT WHEN curTabName%FOUND;
	  					  END LOOP;
	  					  CLOSE curTabName;
							  IF NOT curLeavName%ISOPEN THEN
		  					  OPEN curLeavName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curLeavName into eLeavColName ;
		  					  EXIT WHEN curLeavName%FOUND;
		  				  END LOOP;
		  				  CLOSE curLeavName;
           --     UTL_FILE.PUT_LINE(filehandle,' AND');
      --          UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
  --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || ' = ' || eFactName || '.' || eLeavColName || ') ';

                bufString:= bufString 
                            || '(' 
                            || dbms_assert.enquote_name(eTabName) 
                            || '.' 
                            || dbms_assert.enquote_name(eLevelColName) 
                            || ' = ' 
                            || dbms_assert.enquote_name(eFactName)
                            || '.' 
                            || dbms_assert.enquote_name(eLeavColName)
                            || ') ';

                IF ePosition < poscount THEN
       --           UTL_FILE.PUT_LINE(filehandle,' AND');
                  bufString:= bufString || ' AND ';
                END IF;
	  					END LOOP;
	  					CLOSE curColDimName;
		  			END IF;
		  		END LOOP;
		  		IF mvnum < dimList.Count THEN
		  --			UTL_FILE.PUT_LINE(filehandle,' AND');
						bufString:= bufString || ' AND ';
	    		END IF;
	  		END IF;
			END LOOP;
			FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
	  			edimName:= mvListinc(j).dimName;
	    		ehierName:= mvListinc(j).hierName;
		  		FOR l in 1..hierLevelList.Count LOOP
		  			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
		  				eLevelName:=  hierLevelList(l).levelName;
		  			--	mvnum:= mvnum + 1;
		  				IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				END IF;
		  				LOOP
		  					FETCH curColName into eLevelColName,ePosition ;
		  					EXIT WHEN curColName%NOTFOUND;
	              IF NOT curTabName%ISOPEN THEN
		  					  OPEN curTabName;
		  				  END IF;
		  				  LOOP
		  					  FETCH curTabName into eTabName;
		  					  EXIT WHEN curTabName%NOTFOUND;
                END LOOP;
	              CLOSE curTabName;
	  					  parentString:= eTabName || '.' || eLevelColName;
	  					  childString:= parentString;
	  					  IF NOT curParentName%ISOPEN THEN
	  						  OPEN curParentName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curParentName into eparLevelName,etableName ;
	  					    EXIT WHEN curParentName%FOUND;
	  					  END LOOP;
	  					  CLOSE curParentName;
	  					  childString:= eparLevelName;
	  					  IF childString <> parentString THEN
		  					  bufString:= bufString || ' AND ';
		  	--			  UTL_FILE.PUT_LINE(filehandle,' AND');
	  		--				  UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
	  			            --Bug 8915150 bufString:= bufString || '(' || childString || ' = ' || parentString || ') ';

	  					          bufString:= bufString 
                                                                      || '('
                                                                      || dbms_assert.enquote_name(childString)
                                                                      || ' = ' 
                                                                      || dbms_assert.enquote_name(parentString) 
                                                                      || ') ';
	  						 
	  					  END IF;
		  				END LOOP;
		  				CLOSE curColName;
						END IF;
		  		END LOOP;
	  		END IF;
			END LOOP;

--	  	UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');
	  	bufString:= bufString || 'GROUP BY ';
	  	FOR j in 1..mvListinc.Count LOOP
        IF mvListinc(j).mvNum= i THEN
        	mvnum:= 0;
  				edimName:= mvListinc(j).dimName;
    			ehierName:= mvListinc(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
      			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
		  				 eLevelName:=  hierLevelList(l).levelName;
		  				 mvnum:= mvnum + 1;
               IF NOT curColName%ISOPEN THEN
	  						OPEN curColName;
               END IF;
               poscount:= 0;
               LOOP
		  					FETCH curColName into eLevelColName,ePosition;
                EXIT WHEN curColName%NOTFOUND;
                poscount:= poscount + 1;
               END LOOP;
               CLOSE curColName;
               IF NOT curColName%ISOPEN THEN
		  					OPEN curColName;
		  				 END IF;
	  					 LOOP
	  						FETCH curColName INTO eLevelColName,ePosition ;
	  						EXIT WHEN curColName%NOTFOUND;
                IF NOT curTabName%ISOPEN THEN
	  						  OPEN curTabName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curTabName INTO eTabName;
	  						  EXIT WHEN curTabName%NOTFOUND;
	  					  END LOOP;
                CLOSE curTabName;
               
                IF ePosition < poscount AND ePosition = 1 THEN
                	bufString:= bufString || '(';
       --           UTL_FILE.PUT(filehandle,'(');
                ELSIF ePosition <= poscount AND ePosition <> 1 THEN
                	bufString:= bufString || ', ';
       --           UTL_FILE.PUT(filehandle,', ');
                END IF;
	  	 --				  UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
	  	 		          --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ;

	  	 					bufString:= bufString 
                                                                    || dbms_assert.enquote_name(eTabName) 
                                                                    || '.' 
                                                                    || dbms_assert.enquote_name(eLevelColName) ;

	  					END LOOP;
	  					CLOSE curColName;
              IF ePosition <> 1 THEN
              	bufString:= bufString || ') ';
        --        UTL_FILE.PUT(filehandle,')');
              END IF;
              IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
	  	 --					  UTL_FILE.PUT(filehandle,', ROLLUP( ');
	  						  bufString:= bufString || ', ROLLUP( ';
              ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
	  	 --					  UTL_FILE.PUT_LINE(filehandle,', ');
	  						  bufString:= bufString || ', ';
              ELSIF mvListinc(j).lvlCount= mvnum THEN
	  		--				  UTL_FILE.PUT(filehandle,') ');
	  						  bufString:= bufString || ') ';
              END IF;
	  				ELSIF hierLevelList(l).dimName = eDimName AND
	  					hierLevelList(l).hierName IS NULL THEN
	  					eLevelName:= hierLevelList(l).levelName;
	  					mvnum:= mvnum + 1;
              IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
              poscount:= 0;
              LOOP
		  					FETCH curColDimName into eLevelColName,ePosition;
                EXIT WHEN curColDimName%NOTFOUND;
                poscount:= poscount + 1;
              END LOOP;
              CLOSE curColDimName;
              IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
	  					LOOP
	  						FETCH curColDimName into eLevelColName,ePosition ;
	  						EXIT WHEN curColDimName%NOTFOUND;
	  					  IF NOT curTabName%ISOPEN THEN
	  						  OPEN curTabName;
	  					  END IF;
	  					  LOOP
	  						  FETCH curTabName into eTabName;
	  						  EXIT WHEN curTabName%FOUND;
	  					  END LOOP;
	  					  CLOSE curTabName;
	  			     --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ;

	  					   bufString:= bufString 
                                                               || dbms_assert.enquote_name(eTabName) 
                                                               || '.' 
                                                               || dbms_assert.enquote_name(eLevelColName) ;

           --     IF ePosition < poscount AND ePosition <> 1 THEN
           --     	bufString:= bufString || '(';
          --        UTL_FILE.PUT(filehandle,'(');
          --      ELSIF ePosition <= poscount THEN
          --      	bufString:= bufString || ', ';
           --       UTL_FILE.PUT(filehandle,',');
           --     END IF;
	  			 --		  UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
	  					 
              END LOOP;
              CLOSE curColDimName;
              IF ePosition <> 1 THEN
             --   UTL_FILE.PUT(filehandle,' )');
               	bufString:= bufString || ')';
              END IF;
	  				END IF;
      		END LOOP;
    			IF mvListinc(j).highestLvl = FALSE THEN
     	--			UTL_FILE.PUT(filehandle,', ');
     				bufString:= bufString || ', ';
  		--		ELSE
  			--		UTL_FILE.PUT(filehandle,';');
  			--		bufString:= bufString || ';';
  				END IF;
        END IF;
	  	END LOOP;
	  	eTempMV:= 'aaa';
	  	num_bytes:= 0;
	  	query_length:= LENGTH(bufString);
	  	IF NOT checkName%ISOPEN THEN
			  OPEN checkName;
			END IF;
			LOOP
				FETCH checkName into mvString;
				IF bufString = mvString THEN
					EXIT ;
				ELSE
				 	DBMS_SUMMARY.estimate_mview_size(eTempMV,bufString,num_rows,num_bytes);
					INSERT INTO SYSTEM.MVIEW$_ADV_OUTPUT(RUNID#,OUTPUT_TYPE,RANK#,SUMMARY_OWNER,
	  									QUERY_TEXT,STORAGE_IN_BYTES,BENEFIT_TO_COST_RATIO) VALUES
	  														(runid,0,i,eOwnerName,bufString,num_bytes,0);

	  			INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
                               (runId,i,eOwnerName,indexString);
          dupmvFlag:= TRUE;

      		indexString:= '';
				END IF;
				EXIT WHEN checkName%NOTFOUND;
			END LOOP;
			CLOSE checkName;

--      UTL_FILE.NEW_LINE(filehandle,2);
--	  	UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
--                        eOwnerName);
--      UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
--      UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
--	  	UTL_FILE.NEW_LINE(filehandle,1);
-- 	    UTL_FILE.NEW_LINE(filehandle,2);
--	  	UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);

	 		eTempMV:= '';
	 		bufString:= '';
--	  	UTL_FILE.NEW_LINE(filehandle,1);
			mvnum:= 0;
      tpnum:= 0;
	  	FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
    			edimName:= mvListinc(j).dimName;
      		ehierName:= mvListinc(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eDimName AND
	     				hierLevelList(l).hierName= eHierName THEN
			     		eLevelName:= hierLevelList(l).levelName;
			  			IF NOT curColName%ISOPEN THEN
			  				OPEN curColName;
			  			END IF;
			  			LOOP
			  				FETCH curColName into eLevelColName,ePosition ;
			  				EXIT WHEN curColName%NOTFOUND;
			  			END LOOP;
			  			CLOSE curColName;
			  			IF NOT curTabName%ISOPEN THEN
			  				OPEN curTabName;
			  			END IF;
			  			LOOP
			  				FETCH curTabName into eTabName;
			  				EXIT WHEN curTabName%NOTFOUND;
			  			END LOOP;
 			  			CLOSE curTabName;
              tpnum:= j * l;
              IF hierLevelList(l).levelNum <> 1 THEN
		 --	 				  UTL_FILE.NEW_LINE(filehandle,2);
		 --	  			  UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
	   --           UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);

  --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;

                bufString:= bufString 
                            || 'BI_' 
                            || SUBSTR(dbms_assert.enquote_name(exCubeName),0,5) 
                            || SUBSTR(dbms_assert.enquote_name(eLevelColName),0,5) 
                            || SUBSTR(dbms_assert.enquote_name(eLevelColName),7,5) 
                            || SUBSTR(dbms_assert.enquote_name(eLevelColName),-3,5) 
                            || '_' 
                            || tpnum;

                eLevelColName:= eLevelColName || '_' || l;
	  --		  			  UTL_FILE.NEW_LINE(filehandle,1);
		--	  			  UTL_FILE.PUT_LINE(filehandle,'LOCAL');
		--	  			  UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
    --            IF indspcName IS NOT NULL THEN
    --              UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
    --              UTL_FILE.NEW_LINE(filehandle,1);
    --            END IF;
		--	  			  UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
		--	  			  UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
		--	  			  UTL_FILE.NEW_LINE(filehandle,2);
								INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  											SUMMARY_OWNER) VALUES
	  										(runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
              END IF;
			  			mvnum:= mvnum + 1;
		--	  			bufString:= bufString || 'BMPIDX_' || eLevelColName;
			--  			bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
			--  			bufString:= bufString || 'PARALLEL NOLOGGING;   ';
			--				IF dupmvFlag= TRUE THEN
			--					INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  	--										SUMMARY_OWNER) VALUES
	  	--									(runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
	  		--			END IF;
							bufString:= '';
						ELSIF hierLevelList(l).dimName = eDimName AND
	  					hierLevelList(l).hierName IS NULL THEN
	  					eLevelName:= hierLevelList(l).levelName;
	  					mvnum:= mvnum + 1;
	  					IF NOT curColDimName%ISOPEN THEN
	  						OPEN curColDimName;
	  					END IF;
	  					LOOP
	  						FETCH curColDimName into eLevelColName,ePosition ;
	  						EXIT WHEN curColDimName%NOTFOUND;
	  					END LOOP;
	  					CLOSE curColDimName;
	  					IF NOT curTabName%ISOPEN THEN
	  						OPEN curTabName;
	  					END IF;
	  					LOOP
	  						FETCH curTabName into eTabName;
	  						EXIT WHEN curTabName%NOTFOUND;
	  					END LOOP;
	  					CLOSE curTabName;
			--				bufString:= bufString || 'BMPIDX_' || eLevelColName;
              IF hierLevelList(l).levelNum <> 1 THEN
				 --			  UTL_FILE.NEW_LINE(filehandle,2);
  --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;

                bufString:= bufString 
                            || 'BI_' 
                            || SUBSTR(dbms_assert.enquote_name(exCubeName),0,5) 
                            || SUBSTR(dbms_assert.enquote_name(eLevelColName),0,5)
                            || SUBSTR(dbms_assert.enquote_name(eLevelColName),7,5) 
                            || SUBSTR(dbms_assert.enquote_name(eLevelColName),-3,5) 
                            || '_' 
                            || tpnum;

                eLevelColName:= eLevelColName || '_' || l;
			   --			  UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
			   --			  UTL_FILE.NEW_LINE(filehandle,1);
			   --			  UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			   --			  UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
         --       IF indspcName IS NOT NULL THEN
         --         UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
         --         UTL_FILE.NEW_LINE(filehandle,1);
         --       END IF;
			   --			  UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
			   --			  UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			   --			  UTL_FILE.NEW_LINE(filehandle,2);
			   						INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  											SUMMARY_OWNER) VALUES
	  										(runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
			   
              END IF;
			--  			bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
			--  			bufString:= bufString || 'PARALLEL NOLOGGING;   ';
			--				IF dupmvFlag= TRUE THEN

  		--				  INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  	--										SUMMARY_OWNER) VALUES
	  	--									(runid,i + 100,mvnum,0,bufString,eLevelColName,eOwnerName);
			 --					INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  	 -- 										SUMMARY_OWNER) VALUES
	  	 --									(runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
	  	--				END IF;
							bufString:= '';
	  				END IF;
      		END LOOP;
	  		END IF;
	  	END LOOP;

	 -- 	INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
	 -- 									INDEX_TEXT) VALUES
	  --										(runid,i,eOwnerName,bufString);

	  	o:= 0;
			FOR j in 1..mvListinc.Count LOOP
	  		IF mvListinc(j).mvNum= i THEN
    			edimName:= mvListinc(j).dimName;
      		ehierName:= mvListinc(j).hierName;
      		FOR l in 1..dimHierList.Count LOOP
      			IF eDimName = dimHierList(l).dimName AND
	     				eHierName = dimHierList(l).hierName THEN
	     				o:= o + 1;
	     				partList.EXTEND;
      				partList(o).dimName:= dimHierList(l).dimName;
      				partList(o).hierName:= dimHierList(l).hierName;
							partList(o).dimNum:= dimHierList(l).dimNum;
   						partList(o).hierNum:= dimHierList(l).hierNum;
   						partList(o).levelCount:= dimHierList(l).levelCount;
      			ELSIF eDimName = dimHierList(l).dimName AND
	     				    dimHierList(l).hierName IS NULL THEN
      				o:= o + 1;
	     				partList.EXTEND;
      				partList(o).dimName:= dimHierList(l).dimName;
      				partList(o).hierName:= dimHierList(l).hierName;
							partList(o).dimNum:= dimHierList(l).dimNum;
   						partList(o).hierNum:= dimHierList(l).hierNum;
   						partList(o).levelCount:= dimHierList(l).levelCount;

      			END IF;
      		END LOOP;
      	END IF;
			END LOOP;
			times:= 0;
			FOR k in 1..partList.Count LOOP
	     	times:= times + partList(k).levelCount;
			END LOOP;

			FOR j in 1..partList.Count LOOP
	  		edimName:= partList(j).dimName;
      	ehierName:= partList(j).hierName;
	      m:= 0;

        tpnum:= j * i;
     -- 	UTL_FILE.NEW_LINE(filehandle,2);
		 --		UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
		 --		UTL_FILE.NEW_LINE(filehandle,1);
		 --		UTL_FILE.PUT_LINE(filehandle,'  ( ');
		 --	  UTL_FILE.PUT_LINE(filehandle,'     bin_to_num ');
		 --	  UTL_FILE.PUT_LINE(filehandle,'     ( ');
				FOR l in 1..hierLevelList.Count LOOP
  				IF hierLevelList(l).dimName= eDimName AND
     				hierLevelList(l).hierName= eHierName THEN
		     		eLevelName:= hierLevelList(l).levelName;
		  			IF NOT curColName%ISOPEN THEN
		  				OPEN curColName;
		  			END IF;
		  			LOOP
		  				FETCH curColName into eLevelColName,ePosition ;
		  				EXIT WHEN curColName%NOTFOUND;
		  			END LOOP;
		  			CLOSE curColName;
		  			IF NOT curTabName%ISOPEN THEN
		  				OPEN curTabName;
		  			END IF;
		  			LOOP
		  				FETCH curTabName into eTabName;
		  				EXIT WHEN curTabName%NOTFOUND;
		  			END LOOP;
		  			CLOSE curTabName;
		  			times:= times - 1;
					  m:= m + 1;
			--			UTL_FILE.PUTF(filehandle,'       sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
			--		  IF m < partList(j).levelCount THEN

      --			  UTL_FILE.PUT(filehandle,',');
			--		  END IF;
		  --			UTL_FILE.PUTF(filehandle,'     /* grouping(%s) */ ', eLevelColName);
			--			UTL_FILE.NEW_LINE(filehandle,1);
          END IF;
        END LOOP;
			--  UTL_FILE.PUT_LINE(filehandle,'     ) ');
			--	UTL_FILE.PUT_LINE(filehandle,'  ) ');
  		--	UTL_FILE.PUT_LINE(filehandle,'LOCAL');
  		--	UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
      --  IF indspcName IS NOT NULL THEN
      --    UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
      --    UTL_FILE.NEW_LINE(filehandle,1);
      --  END IF;
  		--	UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
  		--	UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
  		--	UTL_FILE.NEW_LINE(filehandle,2);
			END LOOP;
			partList.DELETE;
--      UTL_FILE.NEW_LINE(filehandle,2);
--      UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
--                        eOwnerName);
--      UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
--      UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'' , granularity=>''GLOBAL'') ;');
--      UTL_FILE.NEW_LINE(filehandle,1);

	  END LOOP;


--	  commit;
--	  UTL_FILE.FCLOSE(filehandle);
--	  EXCEPTION
--	 		WHEN others THEN
-- 			UTL_FILE.PUT('others');

--END ;

	  commit;
	  EXCEPTION
 --     WHEN UTL_FILE.INVALID_PATH THEN
 --       UTL_FILE.FCLOSE(filehandle);
 --       DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
 --     WHEN UTL_FILE.INVALID_OPERATION THEN
 --       UTL_FILE.FCLOSE(filehandle);
 --       DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
 --     WHEN UTL_FILE.INVALID_FILEHANDLE THEN
 --       UTL_FILE.FCLOSE(filehandle);
 --       DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
 --     WHEN UTL_FILE.WRITE_ERROR THEN
 --       UTL_FILE.FCLOSE(filehandle);
 --       DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
 --     WHEN UTL_FILE.INTERNAL_ERROR THEN
 --       UTL_FILE.FCLOSE(filehandle);
 --       DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
	 		WHEN others THEN
  --      UTL_FILE.FCLOSE(filehandle);
	 			RAISE;
	  END ;

END;
/
SHOW ERRORS;

GRANT EXECUTE ON OLAPSYS.OlapFactView TO CWM_USER;
CREATE OR REPLACE PUBLIC SYNONYM OlapFactView FOR OLAPSYS.OlapFactView;