------------------------------------------------------------------------------
--- Copyright (c) 1998-2005 Luna Imaging, Inc.  All Rights Reserved.
---
--- This software is confidential and proprietary information of
--- Luna Imaging, Inc.  ("Confidential Information").  You shall not
--- disclose such Confidential Information and shall use it only in
--- accordance with the terms of the license agreement you entered into
--- with Luna Imaging, Inc.
---
--- The software may not be copied, reproduced, translated or reduced to
--- any electronic medium or machine-readable form without
--- the prior written consent of Luna Imaging.
---
--- You are not allowed to distribute the binary and source code
--- (if released) to third parties, without the prior written consent from
--- Luna Imaging.
---
--- You are not allowed to reverse engineer, disassemble or decompile
--- code, or make any modifications of the binary or source code, remove
--- or alter any trademark, logo, copyright or other proprietary notices,
--- legends, symbols, or labels in the Software.
---
--- You are not allowed to sub-license the Software or any derivative
--- work based on or derived from the Software.
---
--- LUNA IMAGING MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY
--- OF THE SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
--- TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
--- PURPOSE, OR NON-INFRINGEMENT, LUNA IMAGING SHALL NOT BE LIABLE FOR ANY
--- DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
--- DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
------------------------------------------------------------------------------


------------------------------------------------------------------------------
-- Reports
--
-- It is important that Create Procedure and Create View privileges are
-- assigned before executing this script or the procedure.
------------------------------------------------------------------------------


set serveroutput on size 1000000;


------------------------------------------------
-- View RPT_DATA_SOURCE
------------------------------------------------

CREATE OR REPLACE VIEW RPT_DATA_SOURCE
AS
SELECT
  ISCOLLECTIONENTITYMAP.UniqueCollectionID,
  (SELECT MAX(IRCOLLECTIONCONFIGURATIONINFO.CollectionName)
   FROM
     IRCOLLECTIONCONFIGURATIONINFO
   WHERE
     IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
         = ISCOLLECTIONENTITYMAP.UniquecollectionID
     AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
  ) AS CollectionName,
  DTVALUETOENTITYMAP.EntityID, 
  ISENTITYTYPES.EntityTypeDisplayName,
  DTVALUETOENTITYMAP.EntityTypeID, 
  ISENTITYTYPES.EntityTypeDisplayOrder,
  DTVALUETOENTITYMAP.RelEntityTypeID, 
  DTVALUETOENTITYMAP.Grouping, 
  DTVALUES.FieldID,
  IRFIELDS.FieldName,
  IRFIELDS.DisplayName AS FieldDisplayName,
  IRFIELDS.DisplayOrder AS FieldDisplayOrder,
  IRFIELDS.DisplayedInData,
  IRFIELDS.FieldType,
  DTVALUES.ValueText,
  DTVALUES.ValueNumber,              
  DTVALUETOENTITYMAP.Preferred AS ValuePreferred,
  DTVALUETOENTITYMAP.DisplayOrder AS ValueDisplayOrder,
  DTVALUETOENTITYMAP.ParentEntityTypeID, 
  DTVALUETOENTITYMAP.ParentEntityID,
  DTVALUETOENTITYMAP.RelEntityID,
  ISENTITYTYPES.IsPreferredEntity
FROM
  IRFIELDS, DTVALUES, DTVALUETOENTITYMAP, ISCOLLECTIONENTITYMAP, ISENTITYTYPES
WHERE
  IRFIELDS.FieldID = DTVALUES.FieldID
  AND ISENTITYTYPES.EntityTypeID = DTVALUETOENTITYMAP.EntityTypeID
  AND DTVALUES.ValueID = DTVALUETOENTITYMAP.ValueID
  AND DTVALUES.Enabled = 1
  AND DTVALUETOENTITYMAP.EntityTypeID = ISCOLLECTIONENTITYMAP.EntityTypeID
  AND DTVALUETOENTITYMAP.EntityID = ISCOLLECTIONENTITYMAP.EntityID
;



/*
 * Example:

   SELECT * FROM RPT_DATA_SOURCE
   WHERE
     EntityID = 1
     AND EntityTypeID = 1
   ORDER BY
     UniqueCollectionID,
     EntityID,
     EntityTypeDisplayOrder,
     RelEntityTypeID,
     Grouping,
     FieldDisplayOrder,
     FieldID,
     ValueDisplayOrder
*/





------------------------------------------------
-- View RPT_TEMPLATE
------------------------------------------------

CREATE OR REPLACE VIEW RPT_TEMPLATE
AS
SELECT
  SLSTANDARDSLOOKUP.StandardID,
  SLSTANDARDSLOOKUP.StandardName,
  SLSTANDARDSLOOKUP.Version,
  SLSTANDARDSLOOKUP.Version_Info,
  SLSTANDARDSLOOKUP.StandardDescriptionURL,
  SLSTANDARDSLOOKUP.IsPCTemplate,
  SLSTANDARDSLOOKUP.ISPublishedTemplate,
  ISENTITYTYPES.EntityTypeDisplayName,
  ISENTITYTYPES.EntityTypeDisplayOrder,
  IRFIELDS.FieldID,
  IRFIELDS.DisplayName AS FieldDisplayName,
  IRFIELDS.FieldName,
  IRFIELDS.FieldType,
  IRTYPES.TypeName AS FieldTypeName,
  IRFIELDS.DisplayOrder AS FieldDisplayOrder
FROM
  SLSTANDARDSLOOKUP,
  ISTEMPLATEENTITYTYPEMAP,
  ISENTITYTYPES,
  ISENTITYFIELDS,
  IRFIELDS,
  IRTYPES
WHERE
  SLSTANDARDSLOOKUP.ISTemplate = 1
  AND SLSTANDARDSLOOKUP.StandardID = ISTEMPLATEENTITYTYPEMAP.TemplateID
  AND ISTEMPLATEENTITYTYPEMAP.EntityTypeID = ISENTITYTYPES.EntityTypeID
  AND ISENTITYTYPES.EntityTypeID = ISENTITYFIELDS.EntityTypeID
  AND ISENTITYFIELDS.FieldID = IRFIELDS.FieldID
  AND IRFIELDS.FieldType = IRTYPES.TypeID
;



/*
 * Example:

   SELECT
     StandardName,
     Version,
     Version_Info,
     StandardDescriptionURL,
     IsPCTemplate,
     ISPublishedTemplate,
     EntityTypeDisplayName,
     FieldDisplayName,
     FieldName,
     FieldTypeName
   FROM RPT_TEMPLATE
   ORDER BY
     StandardID,
     EntityTypeDisplayOrder,
     FieldDisplayOrder,
     FieldID
*/





------------------------------------------------
-- View RPT_DATA_THUMBNAIL_LABELS
------------------------------------------------

CREATE OR REPLACE VIEW RPT_DATA_THUMBNAIL_LABELS
AS
SELECT
  (SELECT MAX(DTVALUETOOBJECT.UniquecollectionID)
   FROM
     DTVALUETOOBJECT
   WHERE
     DTVALUETOOBJECT.ObjectID = IROBJECTIMAGEMAP.ObjectID
     AND DTVALUETOOBJECT.Preferred = 1
  ) AS UniquecollectionID,
  (SELECT MAX(IRCOLLECTIONCONFIGURATIONINFO.CollectionName)
   FROM
     DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
   WHERE
     DTVALUETOOBJECT.ObjectID = IROBJECTIMAGEMAP.ObjectID
     AND DTVALUETOOBJECT.Preferred = 1
     AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
         = DTVALUETOOBJECT.UniquecollectionID
     AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
  ) AS CollectionName,
  IROBJECTIMAGEMAP.ObjectID,
  IROBJECTIMAGEMAP.ImageID,
  (SELECT MAX(DTVALUES.ValueText || TO_CHAR(DTVALUES.ValueNumber))
   FROM DTVALUES, DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
   WHERE
     IROBJECTIMAGEMAP.ObjectID = DTVALUETOOBJECT.ObjectID
     AND DTVALUETOOBJECT.Preferred = 1
     AND DTVALUES.ValueID = DTVALUETOOBJECT.ValueID
     AND DTVALUES.Enabled = 1
     AND DTVALUES.FieldID
       = CAST(IRCOLLECTIONCONFIGURATIONINFO.ThumbnailDataField1 AS INTEGER)
     AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
       = DTVALUETOOBJECT.UniquecollectionID
     AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
  ) AS ThumbnailLabel1,
  (SELECT MAX(DTVALUES.ValueText || TO_CHAR(DTVALUES.ValueNumber))
   FROM DTVALUES, DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
   WHERE
     IROBJECTIMAGEMAP.ObjectID = DTVALUETOOBJECT.ObjectID
     AND DTVALUETOOBJECT.Preferred = 1
     AND DTVALUES.ValueID = DTVALUETOOBJECT.ValueID
     AND DTVALUES.Enabled = 1
     AND DTVALUES.FieldID
       = CAST(IRCOLLECTIONCONFIGURATIONINFO.ThumbnailDataField2 AS INTEGER)
     AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
       = DTVALUETOOBJECT.UniquecollectionID
     AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
  ) AS ThumbnailLabel2,
  (SELECT MAX(DTVALUES.ValueText || TO_CHAR(DTVALUES.ValueNumber))
   FROM DTVALUES, DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
   WHERE
     IROBJECTIMAGEMAP.ObjectID = DTVALUETOOBJECT.ObjectID
     AND DTVALUETOOBJECT.Preferred = 1
     AND DTVALUES.ValueID = DTVALUETOOBJECT.ValueID
     AND DTVALUES.Enabled = 1
     AND DTVALUES.FieldID
       = CAST(IRCOLLECTIONCONFIGURATIONINFO.ThumbnailDataField3 AS INTEGER)
     AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
       = DTVALUETOOBJECT.UniquecollectionID
     AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
  ) AS ThumbnailLabel3,
  (SELECT MAX(DTVALUES.ValueText || TO_CHAR(DTVALUES.ValueNumber))
   FROM DTVALUES, DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
   WHERE
     IROBJECTIMAGEMAP.ObjectID = DTVALUETOOBJECT.ObjectID
     AND DTVALUETOOBJECT.Preferred = 1
     AND DTVALUES.ValueID = DTVALUETOOBJECT.ValueID
     AND DTVALUES.Enabled = 1
     AND DTVALUES.FieldID
       = CAST(IRCOLLECTIONCONFIGURATIONINFO.ThumbnailDataField4 AS INTEGER)
     AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
       = DTVALUETOOBJECT.UniquecollectionID
     AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
  ) AS ThumbnailLabel4,
  (SELECT MAX(IRSPS.URL || '/' || IRLPS.LPS || '/' || IRIMAGEFILES.Filename)
   FROM IRIMAGEFILES, IRSPS, IRLPS
   WHERE
     IRIMAGEFILES.ImageID = IROBJECTIMAGEMAP.ImageID
     AND IRIMAGEFILES.LPSid = IRLPS.LPSid
     AND IRIMAGEFILES.Format = IRSPS.Format
     AND IRIMAGEFILES.MediaType = IRSPS.MediaType
     AND (IRSPS.CollectionId = 0 OR IRSPS.CollectionId IN
       (SELECT IRCOLLECTIONCONFIGURATIONINFO.CollectionID
        FROM DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
        WHERE
          DTVALUETOOBJECT.ObjectID = IROBJECTIMAGEMAP.ObjectID
          AND DTVALUETOOBJECT.Preferred = 1
          AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
              = DTVALUETOOBJECT.UniquecollectionID
          AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
       ))
     AND IRSPS.ResolutionSize = 0
     AND IRIMAGEFILES.ResolutionSize = 0
  ) AS MediaURL0,
  (SELECT MAX(IRSPS.URL || '/' || IRLPS.LPS || '/' || IRIMAGEFILES.Filename)
   FROM IRIMAGEFILES, IRSPS, IRLPS
   WHERE
     IRIMAGEFILES.ImageID = IROBJECTIMAGEMAP.ImageID
     AND IRIMAGEFILES.LPSid = IRLPS.LPSid
     AND IRIMAGEFILES.Format = IRSPS.Format
     AND IRIMAGEFILES.MediaType = IRSPS.MediaType
     AND (IRSPS.CollectionId = 0 OR IRSPS.CollectionId IN
       (SELECT IRCOLLECTIONCONFIGURATIONINFO.CollectionID
        FROM DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
        WHERE
          DTVALUETOOBJECT.ObjectID = IROBJECTIMAGEMAP.ObjectID
          AND DTVALUETOOBJECT.Preferred = 1
          AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
              = DTVALUETOOBJECT.UniquecollectionID
          AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
       ))
     AND IRSPS.ResolutionSize = 1
     AND IRIMAGEFILES.ResolutionSize = 1
  ) AS MediaURL1,
  (SELECT MAX(IRSPS.URL || '/' || IRLPS.LPS || '/' || IRIMAGEFILES.Filename)
   FROM IRIMAGEFILES, IRSPS, IRLPS
   WHERE
     IRIMAGEFILES.ImageID = IROBJECTIMAGEMAP.ImageID
     AND IRIMAGEFILES.LPSid = IRLPS.LPSid
     AND IRIMAGEFILES.Format = IRSPS.Format
     AND IRIMAGEFILES.MediaType = IRSPS.MediaType
     AND (IRSPS.CollectionId = 0 OR IRSPS.CollectionId IN
       (SELECT IRCOLLECTIONCONFIGURATIONINFO.CollectionID
        FROM DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
        WHERE
          DTVALUETOOBJECT.ObjectID = IROBJECTIMAGEMAP.ObjectID
          AND DTVALUETOOBJECT.Preferred = 1
          AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
              = DTVALUETOOBJECT.UniquecollectionID
          AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
       ))
     AND IRSPS.ResolutionSize = 2
     AND IRIMAGEFILES.ResolutionSize = 2
  ) AS MediaURL2,
  (SELECT MAX(IRSPS.URL || '/' || IRLPS.LPS || '/' || IRIMAGEFILES.Filename)
   FROM IRIMAGEFILES, IRSPS, IRLPS
   WHERE
     IRIMAGEFILES.ImageID = IROBJECTIMAGEMAP.ImageID
     AND IRIMAGEFILES.LPSid = IRLPS.LPSid
     AND IRIMAGEFILES.Format = IRSPS.Format
     AND IRIMAGEFILES.MediaType = IRSPS.MediaType
     AND (IRSPS.CollectionId = 0 OR IRSPS.CollectionId IN
       (SELECT IRCOLLECTIONCONFIGURATIONINFO.CollectionID
        FROM DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
        WHERE
          DTVALUETOOBJECT.ObjectID = IROBJECTIMAGEMAP.ObjectID
          AND DTVALUETOOBJECT.Preferred = 1
          AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
              = DTVALUETOOBJECT.UniquecollectionID
          AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
       ))
     AND IRSPS.ResolutionSize = 3
     AND IRIMAGEFILES.ResolutionSize = 3
  ) AS MediaURL3,
  (SELECT MAX(IRSPS.URL || '/' || IRLPS.LPS || '/' || IRIMAGEFILES.Filename)
   FROM IRIMAGEFILES, IRSPS, IRLPS
   WHERE
     IRIMAGEFILES.ImageID = IROBJECTIMAGEMAP.ImageID
     AND IRIMAGEFILES.LPSid = IRLPS.LPSid
     AND IRIMAGEFILES.Format = IRSPS.Format
     AND IRIMAGEFILES.MediaType = IRSPS.MediaType
     AND (IRSPS.CollectionId = 0 OR IRSPS.CollectionId IN
       (SELECT IRCOLLECTIONCONFIGURATIONINFO.CollectionID
        FROM DTVALUETOOBJECT, IRCOLLECTIONCONFIGURATIONINFO
        WHERE
          DTVALUETOOBJECT.ObjectID = IROBJECTIMAGEMAP.ObjectID
          AND DTVALUETOOBJECT.Preferred = 1
          AND IRCOLLECTIONCONFIGURATIONINFO.UniquecollectionID
              = DTVALUETOOBJECT.UniquecollectionID
          AND IRCOLLECTIONCONFIGURATIONINFO.VCID = 'NA'
       ))
     AND IRSPS.ResolutionSize = 4
     AND IRIMAGEFILES.ResolutionSize = 4
  ) AS MediaURL4
FROM
  IROBJECTIMAGEMAP
WHERE
  IROBJECTIMAGEMAP.ObjectID IN (SELECT EntityID FROM ISCOLLECTIONENTITYMAP)
;



/*
 * Example:

   SELECT *
   FROM RPT_DATA_THUMBNAIL_LABELS
   ORDER BY
     UniquecollectionID,
     ObjectID
*/






------------------------------------------------
-- PROCEDURE GENERATE_REPORT_VIEWS
------------------------------------------------

CREATE OR REPLACE PROCEDURE GENERATE_REPORT_VIEWS
IS
/*
  Create the views necessary for reports.

  It is important that Create Procedure and Create View privileges are
  assigned before executing this script or the procedure.

  View RPT_DATA_SOURCE_TMP_UID_ is internal.
  View RPT_DATA_uid_ will be used for a report on all entity data.
*/


  -- List of fields in a collection
  -- Pick up Master UCID values with only one Master row in
  -- IRCOLLECTIONCONFIGURATIONINFO
  CURSOR cFields IS
    SELECT DISTINCT irc1.UniqueCollectionID AS ucid, irc1.CollectionName,
      eb.FieldID, eb.FieldName, eb.FieldDisplayName,
      eb.FieldType, eb.FieldDisplayOrder
    FROM IRCOLLECTIONCONFIGURATIONINFO irc1, RPT_DATA_SOURCE eb
    WHERE irc1.UniqueCollectionID = eb.UniqueCollectionID
      AND irc1.VCID = 'NA'
      AND irc1.CollectionName =
            (SELECT MAX(CollectionName) FROM IRCOLLECTIONCONFIGURATIONINFO irc2
             WHERE irc1.UniqueCollectionID = irc2.UniqueCollectionID
             AND irc2.VCID = 'NA')
    ORDER BY irc1.UniqueCollectionID, eb.FieldDisplayOrder;
  rFields cFields%ROWTYPE;

  viewEntityTmpName VARCHAR2(100);
  viewEntityTmpSQL  VARCHAR2(8000);
  viewEntityName    VARCHAR2(100);
  viewEntitySQL     VARCHAR2(6000);
  prevucid          NUMBER;
  vfieldname        VARCHAR2(100);
  vcollectionname   VARCHAR2(255);

  NONUID            NUMBER := -777;

BEGIN
  prevucid := NONUID;

  -- Loop over uniqueCollectionID values
  OPEN cFields;
  LOOP
    FETCH cFields INTO rFields;
    EXIT WHEN cFields%NOTFOUND;

    IF rFields.ucid != prevucid THEN
      IF prevucid != NONUID THEN
        -- Now create the views

        viewEntityTmpSQL := viewEntityTmpSQL
          || ' FROM'
          || ' RPT_DATA_SOURCE'
          || ' WHERE'
          || ' UniqueCollectionID = ' || TO_CHAR(prevucid);

        viewEntitySQL := viewEntitySQL
          || ' FROM'
          || ' ' || viewEntityTmpName
          || ' GROUP BY'
          || ' UniqueCollectionID,'
          || ' CollectionName,'
          || ' EntityTypeID,'
          || ' EntityTypeDisplayName,'
          || ' IsPreferredEntity,'
          || ' EntityID,'
          || ' Grouping';

        BEGIN
          dbms_output.put_line(viewEntityTmpName
                               || ': ' || SUBSTR(viewEntityTmpSQL, 1, 200));
          dbms_output.put_line(viewEntityName
                               || ': ' || SUBSTR(viewEntitySQL, 1, 200));

          EXECUTE IMMEDIATE viewEntityTmpSQL;
          EXECUTE IMMEDIATE viewEntitySQL;
          NULL;
        EXCEPTION WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception in query execution:');
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
          NULL;
        END;
      END IF;

      prevucid := rFields.ucid;

      -- Generate the first view
      viewEntityTmpName :=
        'RPT_DATA_SOURCE_TMP_UID_' || TO_CHAR(rFields.ucid);

      BEGIN
        EXECUTE IMMEDIATE 'DROP VIEW ' || viewEntityTmpName;
        NULL;
      EXCEPTION WHEN OTHERS THEN
        NULL;
      END;

      viewEntityTmpSQL :=
        'CREATE VIEW ' || viewEntityTmpName
        || ' AS'
        || ' SELECT'
        || ' UniqueCollectionID,'
        || ' CollectionName,'
        || ' EntityTypeID,'
        || ' EntityTypeDisplayName,'
        || ' IsPreferredEntity,'
        || ' EntityID,'
        || ' RelEntityTypeID,'
        || ' Grouping,'
        || ' Fieldid';


      -- Generate the second view
      vcollectionname := SUBSTR(REPLACE(rFields.collectionName, ' ', '_'), 1, 16);
      vcollectionname := REPLACE(vcollectionname, '-', '_');
      vcollectionname := REPLACE(vcollectionname, '.', '_');
      vcollectionname := REPLACE(vcollectionname, ':', '_');
      vcollectionname := REPLACE(vcollectionname, '&', '_');
      vcollectionname := REPLACE(vcollectionname, '~', '_');

      viewEntityName :=
        'RPT_DATA_' || TO_CHAR(rFields.ucid) || '_' || vcollectionname;

      BEGIN
        EXECUTE IMMEDIATE 'DROP VIEW ' || viewEntityName;
        NULL;
      EXCEPTION WHEN OTHERS THEN
        NULL;
      END;

      viewEntitySQL :=
        'CREATE VIEW ' || viewEntityName
        || ' AS'
        || ' SELECT'
        || ' UniqueCollectionID,'
        || ' CollectionName,'
        || ' EntityTypeID,'
        || ' EntityTypeDisplayName,'
        || ' IsPreferredEntity,'
        || ' EntityID,'
        || ' Grouping';
    END IF;

    -- Make sure the column name has no spaces so the MAX function works below
    vfieldname := REPLACE(rFields.fieldname, ' ', '_');
    vfieldname := REPLACE(vfieldname, '-', '_');
    vfieldname := REPLACE(vfieldname, '.', '_');
    vfieldname := REPLACE(vfieldname, ':', '_');
    vfieldname := REPLACE(vfieldname, '&', '_');
    vfieldname := REPLACE(vfieldname, '~', '_');
    vfieldname := SUBSTR(UPPER(vfieldname), 1, 25);

    viewEntityTmpSQL := viewEntityTmpSQL
      || ', (CASE WHEN FieldID='
              || TO_CHAR(rFields.fieldid) || ' THEN';

    IF rFields.fieldtype = 1 THEN
      viewEntityTmpSQL := viewEntityTmpSQL
        || ' valuetext';
    ELSE
      viewEntityTmpSQL := viewEntityTmpSQL
        || ' TO_CHAR(valuenumber)';
    END IF;

    viewEntityTmpSQL := viewEntityTmpSQL
      || ' ELSE NULL END) AS "'
      || vfieldname || TO_CHAR(rFields.fieldid) || '"';


    viewEntitySQL := viewEntitySQL
      || ', MAX(' || vfieldname || TO_CHAR(rFields.fieldid) || ')'
      || ' AS "' || SUBSTR(rFields.fielddisplayname, 1, 30) || '"';


  END LOOP; -- cFields
  CLOSE cFields;

  IF prevucid != NONUID THEN
    -- Now create the views

    viewEntityTmpSQL := viewEntityTmpSQL
      || ' FROM'
      || ' RPT_DATA_SOURCE'
      || ' WHERE'
      || ' UniqueCollectionID = ' || TO_CHAR(prevucid);

    viewEntitySQL := viewEntitySQL
      || ' FROM'
      || ' ' || viewEntityTmpName
      || ' GROUP BY'
      || ' UniqueCollectionID,'
      || ' CollectionName,'
      || ' EntityTypeID,'
      || ' EntityTypeDisplayName,'
      || ' IsPreferredEntity,'
      || ' EntityID,'
      || ' Grouping';

    BEGIN
      dbms_output.put_line(viewEntityTmpName
                           || ': ' || SUBSTR(viewEntityTmpSQL, 1, 200));
      dbms_output.put_line(viewEntityName
                           || ': ' || SUBSTR(viewEntitySQL, 1, 200));

      EXECUTE IMMEDIATE viewEntityTmpSQL;
      EXECUTE IMMEDIATE viewEntitySQL;
      NULL;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Exception in query execution:');
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      NULL;
    END;
  END IF;

  COMMIT;

END;
/
SHOW ERRORS;


COMMIT;