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

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

PROCEDURE CreateDimTab(exOwnerName IN varchar2,exDimName IN varchar2,eOutFile IN varchar2,
												eOutPath IN varchar2, mvspcName IN varchar2 default null,indspcName IN varchar2 default null) IS

-- This it the Procedure to create a MV script for a given Dimension. This procedure writes to a
-- file.
 filehandle UTL_FILE.FILE_TYPE;

    eDimName varchar2(30);
    eOwnerName varchar2(30);
		eMVName  varchar2(30);
--		eeOutFile varchar2(30);
--		eeOutPath varchar2(30);
    eeDimName varchar2(30);
    eeLevelName varchar2(30);
    eHierName varchar2(30);
    eLevelName varchar2(30);
    eLevelColName varchar2(30);
    eLeavColName varchar2(30);
    eFactName varchar2(30);
    eUnitName varchar2(30);
    eAttrName varchar2(30);
    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;
    counter INTEGER;
    ePosition NUMBER;
    poscount NUMBER;
    createMV BOOLEAN;
		others EXCEPTION;

		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 tabColtable is RECORD
		(gidNum    NUMBER,
		 colName  varchar2(60)
		);

		TYPE tabColtable_t is TABLE of tabColtable;

		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 curDispName IS SELECT DISPLAY_NAME
   	FROM ALL_OLAP_DIMENSIONS WHERE DIMENSION_NAME = eDimName AND
   	OWNER= eOwnerName;

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

		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 COLUMN_NAME
	   FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName;


	  CURSOR curAttrName IS SELECT COLUMN_NAME, ATTRIBUTE_NAME, LEVEL_NAME
	   FROM ALL_OLAP_DIM_LEVEL_ATTR_MAPS
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName
     	   AND LEVEL_NAME= eeLevelName;


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

    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();
		tabCol tabColtable_t := tabColtable_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);
    cwm2_olap_metadata_refresh.mr_refresh;
    numOfMV:= 1;
    numOfdimMV:= 1;
    i:= 0;
  --  eeOutPath:= '/users/mrangwal';
   -- eOutFile:= 'tete';
    eDimName:= UPPER(exDimName);
    eOwnerName:= UPPER(exOwnerName);
    IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
 			filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
    END IF;
  --  tempName:= '';
    IF NOT curDispName%ISOPEN THEN
   			OPEN curDispName;
    END IF;
      LOOP
	    FETCH curDispName into tempName;
				EXIT WHEN curDispName%NOTFOUND;
      END LOOP;
      CLOSE curDispName;

    IF tempName IS NULL THEN
        RAISE UTL_FILE.INTERNAL_ERROR;
    END IF;
		dimList.EXTEND;
		i:= i + 1;
		dimList(i).dimName:= eDimName;
--	  dimList(i):= i;
    dimCount:= dimList.Count;
    j:= 0;
 		FOR i in 1..dimList.Count LOOP
			eeDimName:= 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;
        RAISE UTL_FILE.INVALID_OPERATION;
   		END IF;
			dimList(i).hierCount:= k;
		END LOOP;
    j:= 0;
		FOR i in 1..dimHierList.Count LOOP
   		eeDimName:= 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:= eHierName;
	     	 	hierLevelList(j).levelName:= tempName;
	      	hierLevelList(j).levelNum:= k;
 				END LOOP;
 				CLOSE curLevDimName;
	    END IF;
    	dimHierList(i).levelCount:= k;

		END LOOP;

		numOfdimMV:= 0;
	  FOR i in 1..dimList.Count LOOP
	  	numOfdimMV:= numOfdimMV + dimList(i).hierCount;
	  END LOOP;

		FOR l in 1..dimHierList.Count LOOP
			mvdimList.EXTEND;
			mvdimList(l).mvNum:= l;
			mvdimList(l).dimName:= dimHierList(l).dimName;
			mvdimList(l).hierName:= dimHierList(l).hierName;
			mvdimList(l).lvlCount:= dimHierList(l).levelCount;
			mvdimList(l).highestLvl:= FALSE;
		END LOOP;

  	FOR i in 1..numofdimMV LOOP
  			eMVName:= '';
        createMV:= TRUE;
  			eMVName:= SUBSTR(eDimName,0,5) || '_' || SUBSTR(mvdimList(i).hierName,0,5);
  			eMVName:= eMVName || '_OLAP';
		  --UTL_PUT(filehandle,buffer);
		  UTL_FILE.PUT(filehandle,'create materialized view  ');
		  UTL_FILE.PUT_LINE(filehandle,eMVName);
      UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
      o:= 0;
	  	FOR j in 1..mvdimList.Count LOOP
  			IF mvdimList(j).mvNum= i THEN
  				eedimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(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;
    				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
							 --			tkval:= m - 1;
							 --			tpnum:= tkval * 2;
							 --			val:= val + tpnum;
                      val:= (val * 2) + 1;
									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;
				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))');
      IF mvspcName IS NOT NULL THEN
        UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
        UTL_FILE.NEW_LINE(filehandle,1);
      END IF;
	--	  UTL_FILE.PUT(filehandle,'TABLESPACE');
	--	  UTL_FILE.PUT_LINE(filehandle,eOwnerName);
		  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');
		  UTL_FILE.PUT_LINE(filehandle,'COUNT(*)     COUNT_STAR,');
      UTL_FILE.PUT(filehandle,'GROUPING_ID( ');


	    bufString:= bufString || 'SELECT ';
	    bufString:= bufString || 'COUNT(*) COUNT_STAR, ';
	    bufString:= bufString || 'GROUPING_ID( ';
   		dupdimmvFlag:= FALSE;
	  	FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
    			eeDimName:= mvdimList(j).dimName;
      		ehierName:= mvdimList(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eeDimName 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 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;

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

			          --Bug 8915090 bufString:= bufString || eLevelColName;

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

			  			IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
			  				bufString:= bufString || ', ';
			  				UTL_FILE.PUT_LINE(filehandle,',');
			  			END IF;
            ELSIF hierLevelList(l).dimName= eeDimName AND
	     				hierLevelList(l).hierName= NULL THEN
			     		eLevelName:= hierLevelList(l).levelName;
              createMV:= FALSE;
			  			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 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;

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

			          --Bug 8915090 bufString:= bufString || eLevelColName;

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

			  			IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
			  				bufString:= bufString || ', ';
			  				UTL_FILE.PUT_LINE(filehandle,',');
			  			END IF;
	  				END IF;
      		END LOOP;
	  		END IF;
	  	END LOOP;
      UTL_FILE.PUT_LINE(filehandle,') gid,');
      bufString:= bufString || ') gid, ';
      FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
    			eeDimName:= mvdimList(j).dimName;
      		ehierName:= mvdimList(j).hierName;
          FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eeDimName AND
	     				hierLevelList(l).hierName= eHierName THEN
			     		eeLevelName:= hierLevelList(l).levelName;
			     	  --	eLevelName:= hierLevelList(l).levelName;
	  			    IF NOT curAttrName%ISOPEN THEN
	  				    OPEN curAttrName;
	  			    END IF;
	  			    LOOP
	  				    FETCH curAttrName into eLevelColName,eAttrName,eLevelName ;
	  				    IF NOT curAttrTabName%ISOPEN THEN
	  					    OPEN curAttrTabName;
	  				    END IF;
	  				    LOOP
	  					    FETCH curAttrTabName into eTabName;
	  					    EXIT WHEN curAttrTabName%FOUND;
	  				    END LOOP;
	  				    CLOSE curAttrTabName;
                EXIT WHEN curAttrName%NOTFOUND;
	  		      --Bug 8915090 bufString:= bufString || 'max(' || eTabName || '.' || eLevelColName || ')';

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

	  		      --Bug 8915090 bufString:= bufString || ' ' || eLevelColName || '_AR' || ',';

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

	  		      --		UTL_FILE.PUT_LINE(filehandle,') gid');
	  				    UTL_FILE.PUTF(filehandle,'max(%s.%s) %s%s_AR', eTabName,eLevelColName,eLevelColName,UPPER(SUBSTR(eAttrName,0,5)));
	  				    UTL_FILE.PUT_LINE(filehandle,',');
	  			    END LOOP;
	  			    CLOSE curAttrName;
            END IF;
          END LOOP;
	  		END IF;
	  	END LOOP;
    	FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(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', eTabName,eLevelColName,eLevelColName);
				  --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;

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

	--		  		        bufString:= bufString || eLevelColName || ' ' || eLevelColName;
			  			IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
			  				bufString:= bufString || ', ';
			  				UTL_FILE.PUT_LINE(filehandle,', ');
			  			ELSE
			  				UTL_FILE.PUT_LINE(filehandle,'');
			  			END IF;
            ELSIF hierLevelList(l).dimName= eDimName AND
	     				hierLevelList(l).hierName= 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', eTabName,eLevelColName,eLevelColName);
				  --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;

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

	--		  			bufString:= bufString || eLevelColName || ' ' || eLevelColName;
			  			IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
			  				bufString:= bufString || ', ';
			  				UTL_FILE.PUT_LINE(filehandle,', ');
			  			ELSE
			  				UTL_FILE.PUT_LINE(filehandle,'');
			  			END IF;
	  				END IF;
	    		END LOOP;
	  		END IF;
  		END LOOP;
	    bufString:= bufString || ' FROM ';
	  	UTL_FILE.PUT_LINE(filehandle,'FROM ');
			o:= 0;
  		FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(j).hierName;
	      	FOR l in 1..hierLevelList.Count LOOP
	      		IF hierLevelList(l).dimName= eedimName AND
	      			hierLevelList(l).hierName= ehierName AND
	      			mvdimList(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;
	--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
	--			  			UTL_FILE.PUT_LINE(filehandle,' ');
	--			  			bufString:= bufString || eTabName || ' ';
				  			o:= o + 1;
  		  				mvtableList.EXTEND;
			  				mvtableList(o).tabName:= eTabName;
			  				mvtableList(o).ownerName:= eOwnerName;
			  				tableCount:= mvtableList.Count;
	--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
	--			  			UTL_FILE.PUT_LINE(filehandle,' ');
	--			  			bufString:= bufString || eTabName || ' ';
            ELSIF hierLevelList(l).dimName= eedimName AND
	      			hierLevelList(l).hierName= NULL AND
	      			mvdimList(j).lvlCount= hierLevelList(l).levelNum 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,', eTabName);
	--			  			UTL_FILE.PUT_LINE(filehandle,' ');
	--			  			bufString:= bufString || eTabName || ' ';
				  			o:= o + 1;
  		  				mvtableList.EXTEND;
			  				mvtableList(o).tabName:= eTabName;
			  				mvtableList(o).ownerName:= eOwnerName;
			  				tableCount:= mvtableList.Count;
	--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
	--			  			UTL_FILE.PUT_LINE(filehandle,' ');
	--			  			bufString:= bufString || eTabName || ' ';
			  		END IF;
	       	END LOOP;
		   	END IF;
  		END LOOP;
  		FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(j).hierName;
--		     	mvdimList(j).lvlCount= hierLevelList(l).levelNum THEN
--  					eLevelName:=  hierLevelList(l).levelName;
            FOR l in 1..hierLevelList.Count LOOP
	  				  IF hierLevelList(l).dimName= eeDimName AND
	     				  hierLevelList(l).hierName= eHierName THEN
			     		  eeLevelName:= hierLevelList(l).levelName;
					      IF NOT curAttrName%ISOPEN THEN
  					      OPEN curAttrName;
  				      END IF;
	  			      LOOP
  					      FETCH curAttrName into eLevelColName,eAttrName,eLevelName ;
  					      IF curAttrName%FOUND THEN
  						      IF NOT curAttrTabName%ISOPEN THEN
  							    OPEN curAttrTabName;
  						      END IF;
  						      LOOP
  							      FETCH curAttrTabName into eTabName;
  							      EXIT WHEN curAttrTabName%FOUND;
  						      END LOOP;
  						      CLOSE curAttrTabName;
  						      whereFlag:= FALSE;
  						      z:= 1;
  						      tableCount:= mvtableList.Count;
							      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;
						      END IF;
                  EXIT WHEN curAttrName%NOTFOUND;
                END LOOP;
  				      CLOSE curAttrName;
              END IF;
            END LOOP;
  			END IF;
	  	END LOOP;

  		FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(j).hierName;
	      	FOR l in 1..hierLevelList.Count LOOP
	      		IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
--		      			mvdimList(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;
			  				parentString:= eTabName || '.' || eLevelColName;
							childString:= parentString;
							IF NOT curParentName%ISOPEN THEN
								OPEN curParentName;
							END IF;
							LOOP
								FETCH curParentName into eparLevelName,etableName;
								EXIT WHEN curParentName%NOTFOUND;
							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;
--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
--			  			UTL_FILE.PUT_LINE(filehandle,' ');
--			  			bufString:= bufString || eTabName || ' ';
							END IF;
		        ELSIF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= NULL THEN
--		      			mvdimList(j).lvlCount= hierLevelList(l).levelNum 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;
			  				parentString:= eTabName || '.' || eLevelColName;
							childString:= parentString;
							IF NOT curParentName%ISOPEN THEN
								OPEN curParentName;
							END IF;
							LOOP
								FETCH curParentName into eparLevelName,etableName;
								EXIT WHEN curParentName%NOTFOUND;
							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;
--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
--			  			UTL_FILE.PUT_LINE(filehandle,' ');
--			  			bufString:= bufString || eTabName || ' ';
							END IF;

  					END IF;
	       	END LOOP;
		   	END IF;
  		END LOOP;
			FOR o in 1..mvtableList.Count LOOP
	  --Bug 8915090 bufString:= bufString || mvtableList(o).ownerName || '.' || mvtableList(o).tabName || ' '
	  --Bug 8915090			        	|| 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);

	  		IF o < mvtableList.Count THEN
		  		bufString:= bufString	|| ', ';
	  			UTL_FILE.PUT(filehandle,',');
	  		END IF;
	  		UTL_FILE.PUT_LINE(filehandle,' ');
			END LOOP;
			mvtableList.DELETE;

--	  	bufString:= bufString || ' ' || 'WHERE ';
--			mvnum:= 0;
			whereFlag:= FALSE;
			FOR j in 1..mvdimList.Count LOOP
  			IF mvdimList(j).mvNum= i THEN
  				eeDimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(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
	  						IF whereFlag = FALSE THEN
	  						 	bufString:= bufString || ' ' || 'WHERE ';
	  						 	UTL_FILE.PUT_LINE(filehandle,' WHERE');
	  						 	whereFlag:= TRUE;
	  						ELSE
	  							bufString:= bufString || ' ' || ' AND ';
	  							UTL_FILE.PUT_LINE(filehandle,'  AND');
	  						END IF;
	  						UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
	  			          --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';

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

            ELSIF hierLevelList(l).dimName= edimName AND
      				hierLevelList(l).hierName= ehierName 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
	  						IF whereFlag = FALSE THEN
	  						 	bufString:= bufString || ' ' || 'WHERE ';
	  						 	UTL_FILE.PUT_LINE(filehandle,' WHERE');
	  						 	whereFlag:= TRUE;
	  						ELSE
	  							bufString:= bufString || ' ' || ' AND ';
	  							UTL_FILE.PUT_LINE(filehandle,'  AND');
	  						END IF;
	  						UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
	  				  --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';

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

	    UTL_FILE.PUT_LINE(filehandle,'GROUP BY ');
	  	bufString:= bufString || 'GROUP BY ';
	  	FOR j in 1..mvdimList.Count LOOP
        IF mvdimList(j).mvNum= i THEN
        	mvnum:= 0;
  				eeDimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(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 8915090 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';

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

	--  					bufString:= bufString || eLevelColName || ' ';
              END LOOP;
	  					CLOSE curColName;
              IF ePosition <> 1 THEN
                UTL_FILE.PUT(filehandle,')');
              END IF;
	  					IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
	  						UTL_FILE.PUT(filehandle,', ROLLUP( ');
	  						bufString:= bufString || ', ROLLUP( ';
	  					ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
	  						UTL_FILE.PUT(filehandle,', ');
	  						bufString:= bufString || ', ';
	  					ELSIF mvdimList(j).lvlCount= mvnum THEN
	  						UTL_FILE.PUT_LINE(filehandle,') ');
	  						bufString:= bufString || ') ';
	  					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;
              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 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 8915090 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';

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

	--  					bufString:= bufString || eLevelColName || ' ';
              END LOOP;
	  					CLOSE curColDimName;
       --       IF ePosition <> 1 THEN
       --         UTL_FILE.PUT(filehandle,')');
       --       END IF;
	  	 --				IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
	  		--				UTL_FILE.PUT(filehandle,', ROLLUP( ');
	  		--				bufString:= bufString || ', ROLLUP( ';
	  		--			ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
	  		--				UTL_FILE.PUT(filehandle,', ');
	  		--				bufString:= bufString || ', ';
	  		--			ELSIF mvdimList(j).lvlCount= mvnum THEN
	  		--				UTL_FILE.PUT_LINE(filehandle,') ');
	  		--				bufString:= bufString || ') ';
	  		--			END IF;
	  				END IF;
      		END LOOP;
        END IF;
	  	END LOOP;
      UTL_FILE.PUT(filehandle,';');

	  	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+ 100,eOwnerName,bufString,23333,0);
  --				INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
  --                           (runId,i+100,eOwnerName,indexString);
          dupdimmvFlag:= TRUE;

      		indexString:= '';
				END IF;
				EXIT WHEN checkName%NOTFOUND;
			END LOOP;
			CLOSE checkName;
	--  	DBMS_SUMMARY.estimate_summary_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+ 100,eOwnerName,bufString,23333,0);
	--  	INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
  --                             (runId,i+100,eOwnerName,indexString);
	 		eTempMV:= '';
			bufString:= '';

	  	UTL_FILE.NEW_LINE(filehandle,2);
	  --	UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
	 	--	bufString:= bufString || 'ANALYZE TABLE  COMPUTE STATISTICS; ';
	  --	UTL_FILE.NEW_LINE(filehandle,1);
      IF createMV = TRUE THEN
	  	  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,method_opt=>''for all columns size skewonly'') ;');
	 	--	bufString:= bufString || 'ANALYZE TABLE  COMPUTE STATISTICS; ';
	  --	UTL_FILE.NEW_LINE(filehandle,1);

    --  UTL_FILE.NEW_LINE(filehandle,2);
	  	  UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
        UTL_FILE.NEW_LINE(filehandle,2);
      END IF;
	  	FOR j in 1..mvdimList.Count LOOP
        IF mvdimList(j).mvNum= i THEN
        	mvnum:= 0;
  				eeDimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(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;
               counter:= j * i * mvnum;
	  					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;
              IF hierLevelList(l).levelNum <> 1 THEN
                IF CreateMV = TRUE THEN
	  			 		    UTL_FILE.NEW_LINE(filehandle,2);
	  					    UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
	                UTL_FILE.NEW_LINE(filehandle,1);
                  IF indspcName IS NOT NULL THEN
                    UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
                    UTL_FILE.NEW_LINE(filehandle,1);
                  END IF;
			--  			  bufString:= bufString || 'INDEX MV_';
			--  			  bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
			  	--		  UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			  UTL_FILE.PUT_LINE(filehandle,'ONLINE');
                  UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
			  			    UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
                  UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  			    UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  			    UTL_FILE.NEW_LINE(filehandle,2);
                END IF;
              END IF;
  --Bug 8915090 bufString:= bufString || 'INDEX_MV_' || eLevelColName;

                bufString:= bufString 
                            || 'INDEX_MV_' 
                            || 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 dupdimmvFlag= 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);
	  	--					END IF;
	  					bufString:= '';

            ELSIF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= NULL THEN
		  				 eLevelName:=  hierLevelList(l).levelName;
		  				 mvnum:= mvnum + 1;
               counter:= j * i * mvnum;
	  					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;
              IF hierLevelList(l).levelNum <> 1 THEN
                IF CreateMV = TRUE THEN
	  			 		    UTL_FILE.NEW_LINE(filehandle,2);
	  					    UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
	                UTL_FILE.NEW_LINE(filehandle,1);
                  IF indspcName IS NOT NULL THEN
                    UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
                    UTL_FILE.NEW_LINE(filehandle,1);
                  END IF;
			--  			  bufString:= bufString || 'INDEX MV_';
			--  			  bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
			  	--		  UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			  UTL_FILE.PUT_LINE(filehandle,'ONLINE');
                  UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
			  			    UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
                  UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  			    UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  			    UTL_FILE.NEW_LINE(filehandle,2);
                END IF;
              END IF;
  --Bug 8915090 bufString:= bufString || 'INDEX_MV_' || eLevelColName;

                bufString:= bufString  
                            || 'INDEX_MV_' 
                            || 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 dupdimmvFlag= 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);
	  	--					END IF;
	  					bufString:= '';
	  				END IF;
	  			END LOOP;
      	END IF;
	  	END LOOP;
      FOR j in 1..mvdimList.Count LOOP
        IF mvdimList(j).mvNum= i THEN
          IF CreateMV = TRUE THEN
            UTL_FILE.NEW_LINE(filehandle,2);
            UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
       	    UTL_FILE.NEW_LINE(filehandle,1);
            IF indspcName IS NOT NULL THEN
              UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
              UTL_FILE.NEW_LINE(filehandle,1);
            END IF;
			-- bufString:= bufString || 'INDEX MV_';
      -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
			-- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			-- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
            UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
            UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
            UTL_FILE.PUT_LINE(filehandle,'LOCAL');
            UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
            UTL_FILE.NEW_LINE(filehandle,2);
          END IF;
        END IF;
      END LOOP;
    m:= 0;
    FOR j in 1..mvdimList.Count LOOP
  		IF mvdimList(j).mvNum= i THEN
  			eeDimName:= mvdimList(j).dimName;
    		ehierName:= mvdimList(j).hierName;
    		FOR l in 1..hierLevelList.Count LOOP
  				IF hierLevelList(l).dimName= eeDimName 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);
						m:= m + 1;
						tabcol.EXTEND;
					--	tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;

						tabcol(m).colName:= eLevelColName;
          ELSIF hierLevelList(l).dimName= eeDimName AND
     				hierLevelList(l).hierName= 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);
						m:= m + 1;
						tabcol.EXTEND;
					--	tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;

						tabcol(m).colName:= eLevelColName;
          END IF;
    		END LOOP;
  		END IF;
    END LOOP;
    UTL_FILE.NEW_LINE(filehandle,2);
    IF CreateMV = TRUE THEN
      UTL_FILE.NEW_LINE(filehandle,2);
	    UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
		  UTL_FILE.NEW_LINE(filehandle,1);
    --  bufString:= bufString || 'INDEX MV_';
    --  bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
		  UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');

--Bug 8915090 bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;

              bufString:= bufString 
                          || 'CREATE BITMAP INDEX_MV_' 
                          || dbms_assert.enquote_name(eMVName) ;

--Bug 8915090 bufString:= bufString || 'PREL_FI ON ' || eMVName;

              bufString:= bufString 
                          || 'PREL_FI ON ' 
                          || dbms_assert.enquote_name(eMVName);

      bufString:= bufString || '( (CASE SYS_OP_PARGID(GID';
      FOR n in 1..times LOOP
        UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
--Bug 8915090 bufString:= bufString || tabCol(n).colName;

              bufString:= bufString 
                          || dbms_assert.enquote_name(tabCol(n).colName);

      END LOOP;
      UTL_FILE.PUT_LINE(filehandle,')');
 --     bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
			  --  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
			  --  			bufString:= bufString || 'PARALLEL NOLOGGING;   ';

   --  	bufString:= bufString || 'INDEX_MV_' || eLevelColName;
 		  m:= 0;
 		  FOR k in REVERSE 1..times LOOP
        IF k = times THEN
				    bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
			 		  bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
					  UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
					  UTL_FILE.NEW_LINE(filehandle,1);
            UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
            FOR n in 1..times LOOP
            UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
--Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;

              bufString:= bufString 
                          || ',' 
                          || dbms_assert.enquote_name(tabCol(n).colName);

            END LOOP;
            UTL_FILE.PUT_LINE(filehandle,'))');
            bufString:= bufString || '))';
			  ELSIF k = 1 THEN
				  bufString:= bufString || ' ELSE ';
          bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
          UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR(SYS_OP_PAR(%s, GID', m);
				--	UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
				--	UTL_FILE.NEW_LINE(filehandle,1);
          FOR n in 1..times LOOP
            UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
--Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;

              bufString:= bufString 
                          || ',' 
                          || dbms_assert.enquote_name(tabCol(n).colName);

          END LOOP;
          UTL_FILE.PUT_LINE(filehandle,')) END) )');
          bufString:= bufString || ')) END) )';
--					UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			UTL_FILE.PUT_LINE(filehandle,'ONLINE');
          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,'PCTFREE 0');
			  	UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
          UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  	UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  	UTL_FILE.NEW_LINE(filehandle,2);
			  ELSE
				  bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
      --    bufString:= bufString || 'TO_CHAR(SYS_OP_PAR(GID)';
          bufString:= bufString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID) ';
			  	UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
					UTL_FILE.NEW_LINE(filehandle,1);
--					UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
--			  	UTL_FILE.NEW_LINE(filehandle,1);
--          UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
	--				UTL_FILE.NEW_LINE(filehandle,1);
			--		UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
          UTL_FILE.PUTF(filehandle,'THEN TO_CHAR(SYS_OP_PAR(%s, GID', m);
          FOR n in 1..times LOOP
            UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
--Bug 8915090 bufString:= bufString || ',' || tabCol(n).colName;

              bufString:= bufString 
                          || ',' 
                          || dbms_assert.enquote_name(tabCol(n).colName);

          END LOOP;
          UTL_FILE.PUT_LINE(filehandle,'))');
          bufString:= bufString || '))';
			  END IF;
			  m:= m + 1;
		  END LOOP;
    END IF;
		bufString:= ' ';
    IF CreateMV= TRUE THEN
		  UTL_FILE.NEW_LINE(filehandle,2);
	    UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
      UTL_FILE.NEW_LINE(filehandle,1);

			--  			bufString:= bufString || 'INDEX MV_';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
		  UTL_FILE.PUT_LINE(filehandle,'( (CASE GID');


--Bug 8915090 bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;

              bufString:= bufString 
                          || 'CREATE BITMAP INDEX_MV_' 
                          || dbms_assert.enquote_name(eMVName) ;

--Bug 8915090 bufString:= bufString || 'ET_FI ON ' || eMVName;

              bufString:= bufString 
                          || 'ET_FI ON ' 
                          || dbms_assert.enquote_name(eMVName);

      bufString:= bufString || '( (CASE GID';

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

 --  	bufString:= bufString || 'INDEX_MV_' || eLevelColName;
 		  m:= 1;
 		  FOR k in REVERSE 1..times LOOP
			  IF k = 1 THEN
				  bufString:= bufString || ' ELSE ';
		  --Bug 8915090 bufString:= bufString || 'TO_CHAR(' || tabCol(m).colName || ') END) )';

			  	bufString:= bufString  
                                            || 'TO_CHAR(' 
                                            || dbms_assert.enquote_name(tabCol(m).colName) 
                                            || ') END) )';
			  	UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
			  	UTL_FILE.NEW_LINE(filehandle,1);
					UTL_FILE.PUT_LINE(filehandle,')');
			--		UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			UTL_FILE.PUT_LINE(filehandle,'ONLINE');

          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,'PCTFREE 0');
			  	UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
          UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  	UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  	UTL_FILE.NEW_LINE(filehandle,2);
        ELSE
				  bufString:= bufString || ' WHEN(' || parnum(k).gidNum || ')';
		  --Bug 8915090 bufString:= bufString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';

			  	bufString:= bufString 
                                            || 'THEN TO_CHAR(' 
                                            || dbms_assert.enquote_name(tabCol(m).colName) 
                                            || ') ';
			  	UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
					UTL_FILE.NEW_LINE(filehandle,1);
					UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
			  	UTL_FILE.NEW_LINE(filehandle,1);
			  END IF;
			  m:= m + 1;
		  END LOOP;
    END IF;
    bufString:= ' ';

    partList.DELETE;
    parnum.DELETE;
    tabCol.DELETE;
    UTL_FILE.NEW_LINE(filehandle,2);
    IF CreateMV = TRUE THEN
      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'') ;');
      UTL_FILE.NEW_LINE(filehandle,1);
    END IF;
 END LOOP;


	 -- 	INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
	 -- 									INDEX_TEXT) VALUES
	 -- 										(runid,i+ 100,eOwnerName,bufString);
	  UTL_FILE.FCLOSE(filehandle);
	  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('MV not created as only one level in Dimension ');
      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 CreateDimTab(runId IN NUMBER, exOwnerName IN varchar2,exDimName IN varchar2) IS

-- This it the Procedure to create a MV script for a given Dimension. This procedure writes to a
-- table for OEM to pick up.
-- filehandle UTL_FILE.FILE_TYPE;

    eDimName varchar2(30);
    eOwnerName varchar2(30);
		eMVName  varchar2(30);
--		eeOutFile varchar2(30);
--		eeOutPath varchar2(30);
    eeDimName varchar2(30);
    eeLevelName varchar2(30);
    eAttrName varchar2(30);
    eHierName varchar2(30);
    eLevelName varchar2(30);
    eLevelColName varchar2(30);
    eLeavColName varchar2(30);
    eFactName varchar2(30);
    eUnitName varchar2(30);
    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;
    counter INTEGER;
    ePosition NUMBER;
    poscount NUMBER;
    tpcount NUMBER;
    rankNum NUMBER;
    createMV BOOLEAN;
		others EXCEPTION;

		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 tabColtable is RECORD
		(gidNum    NUMBER,
		 colName  varchar2(60)
		);

		TYPE tabColtable_t is TABLE of tabColtable;

		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 curDispName IS SELECT DISPLAY_NAME
   	FROM ALL_OLAP_DIMENSIONS WHERE DIMENSION_NAME = eDimName AND
   	OWNER= eOwnerName;

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

		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 curAttrName IS SELECT COLUMN_NAME,ATTRIBUTE_NAME, LEVEL_NAME
	   FROM ALL_OLAP_DIM_LEVEL_ATTR_MAPS
	   WHERE OWNER=  eOwnerName AND DIMENSION_NAME= eDimName
     AND LEVEL_NAME= eeLevelName;


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

    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();
		tabCol tabColtable_t := tabColtable_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);
    cwm2_olap_metadata_refresh.mr_refresh;
    numOfMV:= 1;
    numOfdimMV:= 1;
    i:= 0;
  --  eeOutPath:= '/users/mrangwal';
   -- eOutFile:= 'tete';
    eDimName:= UPPER(exDimName);
    eOwnerName:= UPPER(exOwnerName);
--    IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
-- 			filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
--    END IF;
  --  tempName:= '';
    IF NOT curDispName%ISOPEN THEN
   			OPEN curDispName;
    END IF;
      LOOP
	    FETCH curDispName into tempName;
				EXIT WHEN curDispName%NOTFOUND;
      END LOOP;
      CLOSE curDispName;

    IF tempName IS NULL THEN
        RAISE UTL_FILE.INTERNAL_ERROR;
    END IF;
		dimList.EXTEND;
		i:= i + 1;
		dimList(i).dimName:= eDimName;
--	  dimList(i):= i;
    dimCount:= dimList.Count;
    j:= 0;
 		FOR i in 1..dimList.Count LOOP
			eeDimName:= 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:= '';
   			dimHierList(j).hierNum:= k;
   		END IF;
			dimList(i).hierCount:= k;
		END LOOP;
    j:= 0;
		FOR i in 1..dimHierList.Count LOOP
   		eeDimName:= 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:= eHierName;
	     	 	hierLevelList(j).levelName:= tempName;
	      	hierLevelList(j).levelNum:= k;
 				END LOOP;
 				CLOSE curLevDimName;
	    END IF;
    	dimHierList(i).levelCount:= k;

		END LOOP;

		numOfdimMV:= 0;
	  FOR i in 1..dimList.Count LOOP
	  	numOfdimMV:= numOfdimMV + dimList(i).hierCount;
	  END LOOP;

		FOR l in 1..dimHierList.Count LOOP
			mvdimList.EXTEND;
			mvdimList(l).mvNum:= l;
			mvdimList(l).dimName:= dimHierList(l).dimName;
			mvdimList(l).hierName:= dimHierList(l).hierName;
			mvdimList(l).lvlCount:= dimHierList(l).levelCount;
			mvdimList(l).highestLvl:= FALSE;
		END LOOP;
		SELECT nvl(max(rank#), 100) INTO rankNum FROM SYSTEM.MVIEW$_ADV_OUTPUT
     		WHERE RUNID# = runid;
  	FOR i in 1..numofdimMV LOOP
        createMV:= TRUE;
  			eMVName:= '';
  			eMVName:= SUBSTR(eDimName,0,5) || '_' || SUBSTR(mvdimList(i).hierName,0,5);
  			eMVName:= eMVName || '_OLAP';
		  --UTL_PUT(filehandle,buffer);
	--	  UTL_FILE.PUT(filehandle,'create materialized view  ');
	--	  UTL_FILE.PUT_LINE(filehandle,eMVName);
  --    UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
	    indexString:= '';
  	  indexString:= indexString || 'partition by range (gid) (';
      o:= 0;
	  	FOR j in 1..mvdimList.Count LOOP
  			IF mvdimList(j).mvNum= i THEN
  				eedimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(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;
    				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
							 --			tkval:= m - 1;
							 --			tpnum:= tkval * 2;
							 --			val:= val + tpnum;
                      val:= (val * 2) + 1;
									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;
				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))');
--      IF mvspcName IS NOT NULL THEN
--        UTL_FILE.PUTF(filehandle,'TABLESPACE %s', mvspcName);
--        UTL_FILE.NEW_LINE(filehandle,1);
--      END IF;
	--	  UTL_FILE.PUT(filehandle,'TABLESPACE');
	--	  UTL_FILE.PUT_LINE(filehandle,eOwnerName);
	--	  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');
	--	  UTL_FILE.PUT_LINE(filehandle,'COUNT(*)     COUNT_STAR,');
  --    UTL_FILE.PUT(filehandle,'GROUPING_ID( ');


	    bufString:= bufString || 'SELECT ';
	    bufString:= bufString || 'COUNT(*) COUNT_STAR, ';
	    bufString:= bufString || 'GROUPING_ID( ';
   		dupdimmvFlag:= FALSE;
	  	FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
    			eeDimName:= mvdimList(j).dimName;
      		ehierName:= mvdimList(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eeDimName 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 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;

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

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

            ELSIF hierLevelList(l).dimName = eDimName AND
		  				hierLevelList(l).hierName IS NULL THEN
		  				eLevelName:= hierLevelList(l).levelName;
              createMV:= FALSE;
              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 8915090 bufString:= bufString || eTabName || '.' || eLevelColName;

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

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


	  				END IF;
      		END LOOP;
	  		END IF;
	  	END LOOP;
  --    UTL_FILE.PUT_LINE(filehandle,') gid,');
      bufString:= bufString || ') gid, ';
      FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
    			eeDimName:= mvdimList(j).dimName;
      		ehierName:= mvdimList(j).hierName;
          FOR l in 1..hierLevelList.Count LOOP
	  				IF hierLevelList(l).dimName= eeDimName AND
	     				hierLevelList(l).hierName= eHierName THEN
			     		eeLevelName:= hierLevelList(l).levelName;
			     	  --	eLevelName:= hierLevelList(l).levelName;
	  			    IF NOT curAttrName%ISOPEN THEN
	  				    OPEN curAttrName;
	  			    END IF;
	  			    LOOP
	  				    FETCH curAttrName into eLevelColName,eAttrName,eLevelName ;
	  				    IF NOT curAttrTabName%ISOPEN THEN
	  					    OPEN curAttrTabName;
	  				    END IF;
	  				    LOOP
	  					    FETCH curAttrTabName into eTabName;
	  					    EXIT WHEN curAttrTabName%FOUND;
	  				    END LOOP;
	  				    CLOSE curAttrTabName;
                EXIT WHEN curAttrName%NOTFOUND;
	  		      --Bug 8915090 bufString:= bufString || 'max(' || eTabName || '.' || eLevelColName || ')';

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

	  		      --Bug 8915090 bufString:= bufString || ' ' || eLevelColName || UPPER(SUBSTR(eAttrName,0,5)) || '_AR' || ',';
	  				    
                                            bufString:= bufString 
                                                        || ' ' 
                                                        || dbms_assert.enquote_name(eLevelColName) 
                                                        || UPPER(SUBSTR(dbms_assert.enquote_name(eAttrName),0,5)) 
                                                        || '_AR' 
                                                        || ',';

	  		      --		UTL_FILE.PUT_LINE(filehandle,') gid');
	  				 --   UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
	  				 --   UTL_FILE.PUT_LINE(filehandle,',');
	  			    END LOOP;
	  			    CLOSE curAttrName;
            ELSIF hierLevelList(l).dimName = eDimName AND
		  				hierLevelList(l).hierName IS NULL THEN
		  				eLevelName:= hierLevelList(l).levelName;
              IF NOT curAttrName%ISOPEN THEN
	  				    OPEN curAttrName;
	  			    END IF;
	  			    LOOP
	  				    FETCH curAttrName into eLevelColName,eAttrName, eLevelName ;
	  				    IF NOT curAttrTabName%ISOPEN THEN
	  					    OPEN curAttrTabName;
	  				    END IF;
	  				    LOOP
	  					    FETCH curAttrTabName into eTabName;
	  					    EXIT WHEN curAttrTabName%FOUND;
	  				    END LOOP;
	  				    CLOSE curAttrTabName;
                EXIT WHEN curAttrName%NOTFOUND;
	  		      --Bug 8915090 bufString:= bufString || 'max(' || eTabName || '.' || eLevelColName || ')';

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

	  		      --Bug 8915090 bufString:= bufString || ' ' || eLevelColName || '_AR' || ',';

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

	  		      --		UTL_FILE.PUT_LINE(filehandle,') gid');
	  				 --   UTL_FILE.PUTF(filehandle,'max(%s.%s) %s_AR', eTabName,eLevelColName,eLevelColName);
	  				 --   UTL_FILE.PUT_LINE(filehandle,',');
	  			    END LOOP;
	  			    CLOSE curAttrName;
            END IF;
          END LOOP;
	  		END IF;
	  	END LOOP;
    	FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(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', eTabName,eLevelColName,eLevelColName);
				          --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
							bufString:= bufString 
                                                                    || dbms_assert.enquote_name(eTabName) 
                                                                    || '.' 
                                                                    || dbms_assert.enquote_name(eLevelColName) 
                                                                    || ' ' 
                                                                    || dbms_assert.enquote_name(eLevelColName);
	--		  			bufString:= bufString || eLevelColName || ' ' || eLevelColName;
			  			IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
			  				bufString:= bufString || ', ';
			--  				UTL_FILE.PUT_LINE(filehandle,', ');
			 -- 			ELSE
			 -- 				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', eTabName,eLevelColName,eLevelColName);
					  --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
							bufString:= bufString 
                                                                    || dbms_assert.enquote_name(eTabName) 
                                                                    || '.' 
                                                                    || dbms_assert.enquote_name(eLevelColName) 
                                                                    || ' ' 
                                                                    || dbms_assert.enquote_name(eLevelColName);

	--		  			bufString:= bufString || eLevelColName || ' ' || eLevelColName;
			  			IF hierLevelList(l).levelNum < mvdimList(j).lvlCount THEN
			  				bufString:= bufString || ', ';
			--  				UTL_FILE.PUT_LINE(filehandle,', ');
			 -- 			ELSE
			 -- 				UTL_FILE.PUT_LINE(filehandle,'');
        --        bufString:= bufString || ' ';
			  			END IF;
	  				END IF;
	    		END LOOP;
	  		END IF;
  		END LOOP;
	    bufString:= bufString || ' FROM ';
	--  	UTL_FILE.PUT_LINE(filehandle,'FROM ');
			o:= 0;
  		FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(j).hierName;
	      	FOR l in 1..hierLevelList.Count LOOP
	      		IF hierLevelList(l).dimName= eedimName AND
	      			hierLevelList(l).hierName= ehierName AND
	      			mvdimList(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;
	--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
	--			  			UTL_FILE.PUT_LINE(filehandle,' ');
	--			  			bufString:= bufString || eTabName || ' ';
				  			o:= o + 1;
  		  				mvtableList.EXTEND;
			  				mvtableList(o).tabName:= eTabName;
			  				mvtableList(o).ownerName:= eOwnerName;
			  				tableCount:= mvtableList.Count;
	--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
	--			  			UTL_FILE.PUT_LINE(filehandle,' ');
	--			  			bufString:= bufString || 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;

	--			  	UTL_FILE.PUTF(filehandle,'%s,', eTabName);
	--			  	UTL_FILE.PUT_LINE(filehandle,' ');
	--			  	bufString:= bufString || eTabName || ' ';
              o:= o + 1;
              mvtableList.EXTEND;
              mvtableList(o).tabName:= eTabName;
              mvtableList(o).ownerName:= eOwnerName;
              tableCount:= mvtableList.Count;
	--			  	UTL_FILE.PUTF(filehandle,'%s,', eTabName);
	--			  	UTL_FILE.PUT_LINE(filehandle,' ');
	--			  	bufString:= bufString || eTabName || ' ';

			  		END IF;
	       	END LOOP;
		   	END IF;
  		END LOOP;
  		FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(j).hierName;
--		     	mvdimList(j).lvlCount= hierLevelList(l).levelNum THEN
--  					eLevelName:=  hierLevelList(l).levelName;
            FOR l in 1..hierLevelList.Count LOOP
	  				  IF hierLevelList(l).dimName= eeDimName AND
	     				  hierLevelList(l).hierName= eHierName THEN
			     		  eeLevelName:= hierLevelList(l).levelName;
					      IF NOT curAttrName%ISOPEN THEN
  					      OPEN curAttrName;
  				      END IF;
	  			      LOOP
  					      FETCH curAttrName into eLevelColName,eAttrName, eLevelName ;
  					      IF curAttrName%FOUND THEN
  						      IF NOT curAttrTabName%ISOPEN THEN
  							    OPEN curAttrTabName;
  						      END IF;
  						      LOOP
  							      FETCH curAttrTabName into eTabName;
  							      EXIT WHEN curAttrTabName%FOUND;
  						      END LOOP;
  						      CLOSE curAttrTabName;
  						      whereFlag:= FALSE;
  						      z:= 1;
  						      tableCount:= mvtableList.Count;
							      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;
						      END IF;
                  EXIT WHEN curAttrName%NOTFOUND;
                END LOOP;
  				      CLOSE curAttrName;
              END IF;
            END LOOP;
  			END IF;
	  	END LOOP;
  		FOR j in 1..mvdimList.Count LOOP
	  		IF mvdimList(j).mvNum= i THEN
	  			eeDimName:= mvdimList(j).dimName;
	    		ehierName:= mvdimList(j).hierName;
	      	FOR l in 1..hierLevelList.Count LOOP
	      		IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
--		      			mvdimList(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;
              parentString:= eTabName || '.' || eLevelColName;
							childString:= parentString;
							IF NOT curParentName%ISOPEN THEN
								OPEN curParentName;
							END IF;
							LOOP
								FETCH curParentName into eparLevelName,etableName;
								EXIT WHEN curParentName%NOTFOUND;
							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;
--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
--			  			UTL_FILE.PUT_LINE(filehandle,' ');
--			  			bufString:= bufString || eTabName || ' ';
							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;
              parentString:= eTabName || '.' || eLevelColName;
							childString:= parentString;
							IF NOT curParentName%ISOPEN THEN
								OPEN curParentName;
							END IF;
							LOOP
								FETCH curParentName into eparLevelName,etableName;
								EXIT WHEN curParentName%NOTFOUND;
							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;
--			  			UTL_FILE.PUTF(filehandle,'%s,', eTabName);
--			  			UTL_FILE.PUT_LINE(filehandle,' ');
--			  			bufString:= bufString || eTabName || ' ';
							END IF;
  					END IF;
	       	END LOOP;
		   	END IF;
  		END LOOP;
			FOR o in 1..mvtableList.Count LOOP
	  --Bug 8915090 bufString:= bufString || mvtableList(o).ownerName || '.' || mvtableList(o).tabName || ' '
	  --Bug 8915090	                        		|| mvtableList(o).tabName || ' ';

	  		bufString:= bufString 
                                    || dbms_assert.enquote_name(mvtableList(o).ownerName, FALSE) 
                                    || '.' 
                                    || 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);

	  		IF o < mvtableList.Count THEN
		  		bufString:= bufString	|| ', ';
--	  			UTL_FILE.PUT(filehandle,',');
	  		END IF;
	--  		UTL_FILE.PUT_LINE(filehandle,' ');
          bufString:= bufString	|| ' ';
			END LOOP;
			mvtableList.DELETE;

--	  	bufString:= bufString || ' ' || 'WHERE ';
--			mvnum:= 0;
			whereFlag:= FALSE;
			FOR j in 1..mvdimList.Count LOOP
  			IF mvdimList(j).mvNum= i THEN
  				eeDimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(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%NOTFOUND;
	  					END LOOP;
	  					CLOSE curParentName;
	  					childString:= eparLevelName;
	  					IF childString <> parentString THEN
	  						IF whereFlag = FALSE THEN
	  						 	bufString:= bufString || ' ' || 'WHERE ';
	  					--	 	UTL_FILE.PUT_LINE(filehandle,' WHERE');
	  						 	whereFlag:= TRUE;
	  						ELSE
	  							bufString:= bufString || ' ' || ' AND ';
	  				--			UTL_FILE.PUT_LINE(filehandle,'  AND');
	  						END IF;
	  				--		UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
	  				  --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';

	  						bufString:= bufString 
                                                                    || '(' 
                                                                    || dbms_assert.enquote_name(childString) 
                                                                    || '=' 
                                                                    || dbms_assert.enquote_name(parentString) 
                                                                    || ') ';

	  					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;
	  					parentString:= eTabName || '.' || eLevelColName;
	  					childString:= parentString;
	  					IF NOT curParentName%ISOPEN THEN
	  						OPEN curParentName;
	  					END IF;
	  					LOOP
	  						FETCH curParentName into eparLevelName,etableName ;
	  					EXIT WHEN curParentName%NOTFOUND;
	  					END LOOP;
	  					CLOSE curParentName;
	  					childString:= eparLevelName;
	  					IF childString <> parentString THEN
	  						IF whereFlag = FALSE THEN
	  						 	bufString:= bufString || ' ' || 'WHERE ';
	  					--	 	UTL_FILE.PUT_LINE(filehandle,' WHERE');
	  						 	whereFlag:= TRUE;
	  						ELSE
	  							bufString:= bufString || ' ' || ' AND ';
	  				--			UTL_FILE.PUT_LINE(filehandle,'  AND');
	  						END IF;
	  				--		UTL_FILE.PUTF(filehandle,'(%s = %s)', childString,parentString);
	  				  --Bug 8915090 bufString:= bufString || '(' || childString || '=' || parentString || ') ';

	  						bufString:= bufString 
                                                                    || '(' 
                                                                    || dbms_assert.enquote_name(childString) 
                                                                    || '=' 
                                                                    || dbms_assert.enquote_name(parentString) 
                                                                    || ') ';

	  					END IF;
	  				END IF;
	  			END LOOP;
    		END IF;
  		END LOOP;

--	    UTL_FILE.PUT_LINE(filehandle,'GROUP BY ');
	  	bufString:= bufString || 'GROUP BY ';
	  	FOR j in 1..mvdimList.Count LOOP
        IF mvdimList(j).mvNum= i THEN
        	mvnum:= 0;
  				eeDimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(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,'(');
	                bufString:= bufString || '(';
                ELSIF ePosition <= poscount AND ePosition <> 1 THEN
            --      UTL_FILE.PUT(filehandle,', ');
                  bufString:= bufString || ', ';
                END IF;
	  			 --		  UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
				            --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ;

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

	--  					bufString:= bufString || eLevelColName || ' ';
              END LOOP;
	  					CLOSE curColName;
              IF ePosition <> 1 THEN
                bufString:= bufString || ')';
        --        UTL_FILE.PUT(filehandle,')');
              END IF;
	  					IF mvdimList(j).lvlCount > mvnum AND mvnum = 1 THEN
	  		--				UTL_FILE.PUT(filehandle,', ROLLUP( ');
	  						bufString:= bufString || ', ROLLUP( ';
	  					ELSIF mvdimList(j).lvlCount > mvnum AND mvnum > 1 THEN
	  			--			UTL_FILE.PUT(filehandle,', ');
	  						bufString:= bufString || ', ';
	  					ELSIF mvdimList(j).lvlCount= mvnum 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;
              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 ePosition < poscount AND ePosition = 1 THEN
              --  UTL_FILE.PUT(filehandle,'(');
	           --     bufString:= bufString || '(';
             --   ELSIF ePosition <= poscount AND ePosition <> 1 THEN
            --      UTL_FILE.PUT(filehandle,', ');
             --     bufString:= bufString || ', ';
             --   END IF;
	  			 --		  UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
					    --Bug 8915090 bufString:= bufString || eTabName || '.' || eLevelColName ;

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

	--  					bufString:= bufString || eLevelColName || ' ';
              END LOOP;
	  					CLOSE curColDimName;
	  				END IF;
      		END LOOP;
        END IF;
	  	END LOOP;
	--  	rankNum:= 0;
  --    UTL_FILE.PUT(filehandle,';');
  --			SELECT nvl(max(rank#), 100) INTO rankNum FROM SYSTEM.MVIEW$_ADV_OUTPUT
  --   		WHERE RUNID# = runid;
      rankNum:= rankNum + 1;
	  	query_length:= LENGTH(bufString);
	--  	IF NOT checkName%ISOPEN THEN
	--		  OPEN checkName;
	--		END IF;
  --		LOOP
	--			FETCH checkName into mvString;
	--			IF bufString = mvString THEN
	--				EXIT ;
	--			ELSE

          IF createMV = TRUE THEN
		    	  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,1,rankNum,eOwnerName,bufString,num_bytes,0);
  				  INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
                             (runId,rankNum,eOwnerName,indexString);
          END IF;
          dupdimmvFlag:= TRUE;

      		indexString:= '';
	--			END IF;
	--			EXIT WHEN checkName%NOTFOUND;
	--		END LOOP;
	--		CLOSE checkName;
	--  	DBMS_SUMMARY.estimate_summary_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+ 100,eOwnerName,bufString,23333,0);
	--  	INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
  --                             (runId,i+100,eOwnerName,indexString);
	 		eTempMV:= '';
			bufString:= '';

	 -- 	UTL_FILE.NEW_LINE(filehandle,2);
	  --	UTL_FILE.PUTF(filehandle,'ANALYZE TABLE %s COMPUTE STATISTICS; ',eMVName);
	 	--	bufString:= bufString || 'ANALYZE TABLE  COMPUTE STATISTICS; ';
	  --	UTL_FILE.NEW_LINE(filehandle,1);
	 -- 	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,method_opt=>''for all columns size skewonly'') ;');
	 	--	bufString:= bufString || 'ANALYZE TABLE  COMPUTE STATISTICS; ';
	  --	UTL_FILE.NEW_LINE(filehandle,1);

    --  UTL_FILE.NEW_LINE(filehandle,2);
	 -- 	UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
   --   UTL_FILE.NEW_LINE(filehandle,2);


  --    SELECT nvl(max(rank#), 100) INTO rankNum FROM SYSTEM.MVIEW$_ADV_INDEX
  --    WHERE RUNID# = runid;

  --    rankNum:= rankNum + 1;
     	mvnum:= 0;
	  	FOR j in 1..mvdimList.Count LOOP
        IF mvdimList(j).mvNum= i THEN
  				eeDimName:= mvdimList(j).dimName;
    			ehierName:= mvdimList(j).hierName;
      		FOR l in 1..hierLevelList.Count LOOP
      			IF hierLevelList(l).dimName= edimName AND
	      			hierLevelList(l).hierName= ehierName THEN
		  				 eLevelName:=  hierLevelList(l).levelName;
               counter:= j * i * mvnum;
	  					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;
              IF hierLevelList(l).levelNum <> 1 THEN
	  			-- 		  UTL_FILE.NEW_LINE(filehandle,2);
	  			--		  UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
	        --      UTL_FILE.NEW_LINE(filehandle,1);
          --      IF indspcName IS NOT NULL THEN
          --        UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
          --        UTL_FILE.NEW_LINE(filehandle,1);
          --      END IF;
			--  			bufString:= bufString || 'INDEX MV_';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
			  	--		UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			UTL_FILE.PUT_LINE(filehandle,'ONLINE');
          --      UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
			  	--		  UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
          --      UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  	--		  UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  	--		  UTL_FILE.NEW_LINE(filehandle,2);

--Bug 8915090 bufString:= bufString || 'MV_' || eLevelColName || SUBSTR(mvdimList(j).hierName,0,2) || 'BI_' || counter;

              bufString:= bufString 
                          || 'MV_' 
                          || dbms_assert.enquote_name(eLevelColName) 
                          || SUBSTR(dbms_assert.enquote_name(mvdimList(j).hierName),0,2) 
                          || 'BI_' 
                          || counter;

                IF createMV = TRUE THEN
                  mvnum:= mvnum + 1;
 								  INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
	  											SUMMARY_OWNER) VALUES
	  										(runid,rankNum,mvnum,0,bufString,eLevelColName,eOwnerName);
                END IF;
	  						tpcount:= i;
              END IF;
          ELSIF hierLevelList(l).dimName = eDimName AND
		  				  hierLevelList(l).hierName IS NULL THEN
		  				  eLevelName:= hierLevelList(l).levelName;
                counter:= j * i * mvnum;
	  					  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;
                IF hierLevelList(l).levelNum <> 1 THEN
	  			-- 		  UTL_FILE.NEW_LINE(filehandle,2);
	  			--		  UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s%s_BI%s ON %s(%s)',eLevelColName,SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName,eLevelColName);
	        --      UTL_FILE.NEW_LINE(filehandle,1);
          --      IF indspcName IS NOT NULL THEN
          --        UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
          --        UTL_FILE.NEW_LINE(filehandle,1);
          --      END IF;
			--  			bufString:= bufString || 'INDEX MV_';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
			  	--		UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			UTL_FILE.PUT_LINE(filehandle,'ONLINE');
          --      UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
			  	--		  UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
          --      UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			  	--		  UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			  	--		  UTL_FILE.NEW_LINE(filehandle,2);

                  IF createMV = TRUE THEN

	                  mvnum:= mvnum + 1;
--Bug 8915090 bufString:= bufString || 'MV_' || eLevelColName || SUBSTR(mvdimList(j).hierName,0,2) || 'BI_' || counter;

              bufString:= bufString 
                          || 'MV_' 
                          || dbms_assert.enquote_name(eLevelColName) 
                          || SUBSTR(dbms_assert.enquote_name(mvdimList(j).hierName),0,2) 
                          || 'BI_' 
                          || counter;

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

              	END IF;

	        --      UTL_FILE.NEW_LINE(filehandle,1);
			--  			bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
			--  			bufString:= bufString || 'PARALLEL NOLOGGING;   ';
			--					IF dupdimmvFlag= 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);
	  	--					END IF;

	  				END IF;
            		bufString:= '';
	  			END LOOP;
      	END IF;
	  	END LOOP;

      FOR j in 1..mvdimList.Count LOOP
        IF mvdimList(j).mvNum= i THEN
--          UTL_FILE.NEW_LINE(filehandle,2);
--          UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_GID_%s_BI_%s ON %s(gid)',SUBSTR(mvdimList(j).hierName,0,2),counter,eMVName);
--       	  UTL_FILE.NEW_LINE(filehandle,1);
--Bug 8915090 bufString:= bufString || 'MV_GID_' || SUBSTR(mvdimList(j).hierName,0,2) || '_BI_' || counter;

              bufString:= bufString 
                          || 'MV_GID_' 
                          || SUBSTR(dbms_assert.enquote_name(mvdimList(j).hierName),0,2) 
                          || '_BI_' 
                          || counter;

--          IF indspcName IS NOT NULL THEN
--            UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
--            UTL_FILE.NEW_LINE(filehandle,1);
--          END IF;
			-- bufString:= bufString || 'INDEX MV_';
      -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
			-- UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			-- UTL_FILE.PUT_LINE(filehandle,'ONLINE');
  --        UTL_FILE.PUT_LINE(filehandle,'PCTFREE 0');
  --        UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
  --        UTL_FILE.PUT_LINE(filehandle,'LOCAL');
  --        UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
  --        UTL_FILE.NEW_LINE(filehandle,2);


          IF createMV = TRUE THEN
              mvnum:= mvnum + 1;
              INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
              SUMMARY_OWNER) VALUES
              (runid, rankNum,mvnum,0,bufString,'gid',eOwnerName);
          END IF;
          bufString:= '';
        END IF;
      END LOOP;
    m:= 0;
    FOR j in 1..mvdimList.Count LOOP
  		IF mvdimList(j).mvNum= i THEN
  			eeDimName:= mvdimList(j).dimName;
    		ehierName:= mvdimList(j).hierName;
    		FOR l in 1..hierLevelList.Count LOOP
  				IF hierLevelList(l).dimName= eeDimName 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);
						m:= m + 1;
						tabcol.EXTEND;
					--	tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;

						tabcol(m).colName:= eLevelColName;
          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);
						m:= m + 1;
						tabcol.EXTEND;
					--	tabcol(m).colName:= eTabName || '.' || eLevelColName || ' ' || eLevelColName;

						tabcol(m).colName:= eLevelColName;
          END IF;
    		END LOOP;
  		END IF;
    END LOOP;

 --UTL_FILE.NEW_LINE(filehandle,2);
	 -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_PREL_FI ON %s',eMVName,eMVName);
	--	UTL_FILE.NEW_LINE(filehandle,1);
  --  bufString:= bufString || 'INDEX MV_';
  --  bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
	--	UTL_FILE.PUT(filehandle,'( (CASE SYS_OP_PARGID(GID');

--Bug 8915090 bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;

              bufString:= bufString 
                          || 'CREATE BITMAP INDEX_MV_' 
                          || dbms_assert.enquote_name(eMVName) ;

 --   bufString:= bufString || 'PREL_FI ON ' || eMVName;
    indexString:= indexString || '( (CASE SYS_OP_PARGID(GID';
    FOR n in 1..times LOOP
    --  UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
      indexString:= indexString || ', ' || tabCol(n).colName;
    END LOOP;
 --   UTL_FILE.PUT_LINE(filehandle,')');
 --   bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
			--  			bufString:= bufString || 'PARALLEL NOLOGGING;   ';

 --  	bufString:= bufString || 'INDEX_MV_' || eLevelColName;
 		m:= 0;
 		FOR k in REVERSE 1..times LOOP
			IF k = times THEN
				  indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
			 		indexString:= indexString || 'THEN TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
			--		UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
			--		UTL_FILE.NEW_LINE(filehandle,1);
      --    UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
          FOR n in 1..times LOOP
      --      UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
            indexString:= indexString || ', ' || tabCol(n).colName;
          END LOOP;
       --   UTL_FILE.PUT_LINE(filehandle,'))');
          indexString:= indexString || '))';
			ELSIF k = 1 THEN
				  indexString:= indexString || ' ELSE ';
			--  		indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(GID)';
            indexString:= indexString || 'TO_CHAR(SYS_OP_PAR(' || m || ', GID)';
				--	UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
				--	UTL_FILE.NEW_LINE(filehandle,1);
        --  UTL_FILE.PUT(filehandle,'(ELSE TO_CHAR(CASE SYS_OP_PAR(GID');
          FOR n in 1..times LOOP
         --   UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
            indexString:= indexString || ',' || tabCol(n).colName;
          END LOOP;
       --   UTL_FILE.PUT_LINE(filehandle,'))' END);
          indexString:= indexString || ')) END))';
--					UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			UTL_FILE.PUT_LINE(filehandle,'ONLINE');
      --    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,'PCTFREE 0');
			--  	UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
      --    UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			--  	UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			--  	UTL_FILE.NEW_LINE(filehandle,2);
			ELSE
				  indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
			--  	indexString:= indexString || 'THEN TO_CHAR(' || tabCol(m).colName || ') ';
          indexString:= indexString || 'ELSE TO_CHAR(SYS_OP_PAR(' || m || ', GID)';

			  --	UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
				--	UTL_FILE.NEW_LINE(filehandle,1);
--					UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
--			  	UTL_FILE.NEW_LINE(filehandle,1);
--          UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
	--				UTL_FILE.NEW_LINE(filehandle,1);
			--		UTL_FILE.PUT_LINE(filehandle,'THEN NULL');
      --    UTL_FILE.PUT(filehandle,'(THEN TO_CHAR(CASE SYS_OP_PAR(GID');
          FOR n in 1..times LOOP
      --      UTL_FILE.PUTF(filehandle,', %s',tabCol(n).colName);
            indexString:= indexString || ',' || tabCol(n).colName;
          END LOOP;
       --   UTL_FILE.PUT_LINE(filehandle,'))');
          indexString:= indexString || '))';
			END IF;
			m:= m + 1;
		END LOOP;

    eLevelColName:= '';
    IF createMV = TRUE THEN
      mvnum:= mvnum + 1;
      INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
        SUMMARY_OWNER,INDEX_CONTENT) VALUES
        (runid,rankNum,mvnum,1,bufString,eLevelColName,eOwnerName,indexString);
    END IF;
		bufString:= ' ';
    indexString:= ' ';
--		UTL_FILE.NEW_LINE(filehandle,2);
--	  UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX MV_%s_ET_FI ON %s',eMVName,eMVName);
--    UTL_FILE.NEW_LINE(filehandle,1);

			--  			bufString:= bufString || 'INDEX MV_';
			--  			bufString:= bufString || 'TABLESPACE TS_INDEX_01 ONLINE COMPUTE STATISTICS NOLOGGING ';
--		UTL_FILE.PUT_LINE(filehandle,'( (CASE GID');


  --  bufString:= bufString || 'CREATE BITMAP INDEX_MV_' || eMVName ;
--Bug 8915090 bufString:= bufString || 'MV_' || eMVName || '_ET_FI';

              bufString:= bufString 
                          || 'MV_' 
                          || dbms_assert.enquote_name(eMVName) 
                          || '_ET_FI';

      indexString:= indexString || '( (CASE GID';

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

 --  	bufString:= bufString || 'INDEX_MV_' || eLevelColName;
   		m:= 1;
   		FOR k in REVERSE 1..times LOOP
  			IF k = 1 THEN
  				  indexString:= indexString || ' ELSE ';
  			  	indexString:= indexString || 'TO_CHAR(' || tabCol(m).colName || ') END) )';
			 -- 	UTL_FILE.PUTF(filehandle,'ELSE TO_CHAR( %s) END)', tabCol(m).colName);
			 -- 	UTL_FILE.NEW_LINE(filehandle,1);
			 --		UTL_FILE.PUT_LINE(filehandle,')');
			--		UTL_FILE.PUT_LINE(filehandle,'TABLESPACE TS_INDEX_01');
			--  			UTL_FILE.PUT_LINE(filehandle,'ONLINE');

      --    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,'PCTFREE 0');
			--  	UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
      --    UTL_FILE.PUT_LINE(filehandle,'LOCAL');
			--  	UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
			--  	UTL_FILE.NEW_LINE(filehandle,2);
  			ELSE
  				  indexString:= indexString || ' WHEN(' || parnum(k).gidNum || ')';
  			  	indexString:= indexString || ' THEN TO_CHAR(' || tabCol(m).colName || ') ';
			 -- 	UTL_FILE.PUTF(filehandle,'WHEN(%s)',parnum(k).gidNum);
			 --		UTL_FILE.NEW_LINE(filehandle,1);
			 --		UTL_FILE.PUTF(filehandle,'THEN TO_CHAR( %s)', tabCol(m).colName);
			 -- 	UTL_FILE.NEW_LINE(filehandle,1);
  			END IF;
  		m:= m + 1;
  		END LOOP;
      eLevelColName:= '';
      IF createMV = TRUE THEN

        mvnum:= mvnum + 1;
        INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
          SUMMARY_OWNER,INDEX_CONTENT) VALUES
          (runid,rankNum,mvnum,1,bufString,eLevelColName,eOwnerName,indexString);
      END IF;
		bufString:= ' ';
    indexString:= ' ';
    partList.DELETE;
    parnum.DELETE;
    tabCol.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'') ;');
 --   UTL_FILE.NEW_LINE(filehandle,1);

 END LOOP;
	 -- 	INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
	 -- 									INDEX_TEXT) VALUES
	 -- 										(runid,i+ 100,eOwnerName,bufString);
--	  UTL_FILE.FCLOSE(filehandle);
	  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.OlapDimView TO CWM_USER;
CREATE OR REPLACE PUBLIC SYNONYM OlapDimView FOR OLAPSYS.OlapDimView;