------------------------------------------------------------------------------
--- 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
--
------------------------------------------------------------------------------



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

CREATE 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
GO



/*
 * 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 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
GO



/*
 * 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 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(CASE WHEN DTVALUES.ValueText IS NULL THEN
                CONVERT(varchar(10), DTVALUES.ValueNumber)
              ELSE DTVALUES.ValueText END)
   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(CASE WHEN DTVALUES.ValueText IS NULL THEN
                CONVERT(varchar(10), DTVALUES.ValueNumber)
              ELSE DTVALUES.ValueText END)
   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(CASE WHEN DTVALUES.ValueText IS NULL THEN
                CONVERT(varchar(10), DTVALUES.ValueNumber)
              ELSE DTVALUES.ValueText END)
   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(CASE WHEN DTVALUES.ValueText IS NULL THEN
                CONVERT(varchar(10), DTVALUES.ValueNumber)
              ELSE DTVALUES.ValueText END)
   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)
GO



/*
 * Example:

   SELECT *
   FROM RPT_DATA_THUMBNAIL_LABELS
   ORDER BY
     UniquecollectionID,
     ObjectID
*/






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

CREATE PROCEDURE GENERATE_REPORT_VIEWS
AS
BEGIN
/*
  Create the views necessary for reports.

  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 UID values with only one Master row in
  -- IRCOLLECTIONCONFIGURATIONINFO
  DECLARE cFields CURSOR FOR
    SELECT DISTINCT irc1.UniqueCollectionID, 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
  FOR READ ONLY


  DECLARE @viewEntityTmpName varchar(100)
  DECLARE @viewEntityTmpSQL  varchar(8000)
  DECLARE @viewEntityName    varchar(100)
  DECLARE @viewEntitySQL     varchar(6000)
  DECLARE @uid               int
  DECLARE @prevuid           int
  DECLARE @collectionName    varchar(255)
  DECLARE @fieldid           int
  DECLARE @fieldname         varchar(100)
  DECLARE @fielddisplayname  varchar(100)
  DECLARE @fieldtype         int
  DECLARE @fielddisplayorder int

  SET @prevuid = -777

  -- Loop over uniqueCollectionID values
  OPEN cFields
  FETCH NEXT FROM cFields INTO @uid, @collectionName,
        @fieldid, @fieldname, @fielddisplayname, @fieldtype, @fielddisplayorder
  WHILE @@FETCH_STATUS = 0
  BEGIN

    IF @uid != @prevuid
    BEGIN
      IF @prevuid != -777
      BEGIN
        -- Now create the views

        SET @viewEntityTmpSQL = @viewEntityTmpSQL
          + ' FROM'
          + ' RPT_DATA_SOURCE'
          + ' WHERE'
          + ' UniqueCollectionID = ' + CONVERT(varchar(10), @prevuid)

        SET @viewEntitySQL = @viewEntitySQL
          + ' FROM'
          + ' ' + @viewEntityTmpName
          + ' GROUP BY'
          + ' UniqueCollectionID,'
          + ' CollectionName,'
          + ' EntityTypeID,'
          + ' EntityTypeDisplayName,'
          + ' IsPreferredEntity,'
          + ' EntityID,'
          + ' Grouping'

        EXEC(@viewEntityTmpSQL)
        EXEC(@viewEntitySQL)
      END

      SET @prevuid = @uid

      -- Generate the first view
      SET @viewEntityTmpName =
        'RPT_DATA_SOURCE_TMP_UID_' + CONVERT(varchar(10), @uid)

      IF OBJECT_ID(@viewEntityTmpName) IS NOT NULL
        EXEC('DROP VIEW ' + @viewEntityTmpName)

      SET @viewEntityTmpSQL =
        'CREATE VIEW ' + @viewEntityTmpName
        + ' AS'
        + ' SELECT'
        + ' UniqueCollectionID,'
        + ' CollectionName,'
        + ' EntityTypeID,'
        + ' EntityTypeDisplayName,'
        + ' IsPreferredEntity,'
        + ' EntityID,'
        + ' RelEntityTypeID,'
        + ' Grouping,'
        + ' Fieldid'


      -- Generate the second view
      SET @collectionName = REPLACE(@collectionName, ' ', '_')
      SET @collectionName = REPLACE(@collectionName, '-', '_')
      SET @collectionName = REPLACE(@collectionName, '.', '_')
      SET @collectionName = REPLACE(@collectionName, ':', '_')
      SET @collectionName = REPLACE(@collectionName, '&', '_')
      SET @collectionName = REPLACE(@collectionName, '~', '_')

      SET @viewEntityName =
        'RPT_DATA_' + CONVERT(varchar(10), @uid) + '_' + @collectionName

      IF OBJECT_ID(@viewEntityName) IS NOT NULL
        EXEC('DROP VIEW ' + @viewEntityName)

      SET @viewEntitySQL =
        'CREATE VIEW ' + @viewEntityName
        + ' AS'
        + ' SELECT'
        + ' UniqueCollectionID,'
        + ' CollectionName,'
        + ' EntityTypeID,'
        + ' EntityTypeDisplayName,'
        + ' IsPreferredEntity,'
        + ' EntityID,'
        + ' Grouping'
    END


    SET @viewEntityTmpSQL = @viewEntityTmpSQL
      + ', (CASE WHEN FieldID='
              + CONVERT(varchar(10), @fieldid) + ' THEN'

    IF @fieldtype = 1
      SET @viewEntityTmpSQL = @viewEntityTmpSQL
        + ' valuetext'
    ELSE
      SET @viewEntityTmpSQL = @viewEntityTmpSQL
        + ' convert(varchar(10), valuenumber)'

    SET @viewEntityTmpSQL = @viewEntityTmpSQL
      + ' ELSE NULL END) AS ['
      + @fieldname + CONVERT(varchar(10), @fieldid) + ']'


    SET @viewEntitySQL = @viewEntitySQL
      + ', MAX([' + @fieldname + CONVERT(varchar(10), @fieldid) + '])'
      + ' AS [' + @fielddisplayname + ']'


    FETCH NEXT FROM cFields INTO @uid, @collectionName,
      @fieldid, @fieldname, @fielddisplayname, @fieldtype, @fielddisplayorder
  END  -- cFields
  CLOSE cFields
  DEALLOCATE cFields

  IF @prevuid != -777
  BEGIN
    -- Now create the views

    SET @viewEntityTmpSQL = @viewEntityTmpSQL
      + ' FROM'
      + ' RPT_DATA_SOURCE'
      + ' WHERE'
      + ' UniqueCollectionID = ' + CONVERT(varchar(10), @prevuid)

    SET @viewEntitySQL = @viewEntitySQL
      + ' FROM'
      + ' ' + @viewEntityTmpName
      + ' GROUP BY'
      + ' UniqueCollectionID,'
      + ' CollectionName,'
      + ' EntityTypeID,'
      + ' EntityTypeDisplayName,'
      + ' IsPreferredEntity,'
      + ' EntityID,'
      + ' Grouping'

    EXEC(@viewEntityTmpSQL)
    EXEC(@viewEntitySQL)
  END

END
GO