/* ================================================= */
/*                 Union Field check                 */
/* ================================================= */


SELECT extfield,
       fieldid
FROM   view_mapping
WHERE  schemaid IN (SELECT schemaid
                    FROM   arschema
                    WHERE  NAME = 'SMT:UnionSmartIT_TicketConsole')
       AND extfield != 'FORMNAME'
       AND extfield NOT IN (SELECT unionfieldname
                            FROM   shr_union_datasource_fields
                            WHERE  status = 0
                                   AND unionusedby = 'Smart IT Ticket Console')  



/* ================================================= */
/*      Selection Value Check (Enum Conversion)      */
/* ================================================= */

SELECT *
FROM   (SELECT unionfieldname,
               sourceform,
               sourcefieldid,
               t5.schemaid,
               value
        FROM   (SELECT t3.unionfieldname,
                       t3.sourceform,
                       t3.sourcefieldid,
                       t4.schemaid
                FROM   (SELECT t1.unionfieldname,
                               sourceform,
                               sourcefieldid
                        FROM   shr_union_datasource_fields t1
                               INNER JOIN shr_union_datasource_fieldmapp t2
                                       ON ( t1.unionusedby = t2.unionusedby
                                            AND
                               t1.unionfieldname = t2.unionfieldname )
                        WHERE  t1.status = 0
                               AND t2.status = 0
                               AND t1.unionusedby = 'Smart IT Ticket Console'
                               AND NOT ( t1.convertenumvals IS NULL )) AS t3
                       INNER JOIN arschema t4
                               ON t3.sourceform = t4.NAME
                                  AND t4.overlaygroup = '0') AS t5
               INNER JOIN field_enum_values AS t6
                       ON t5.schemaid = t6.schemaid
                          AND t5.sourcefieldid = CAST(t6.fieldid AS varchar)) AS t9
       LEFT JOIN (SELECT extfield,
                         value
                  FROM   (SELECT *
                          FROM   view_mapping
                          WHERE  schemaid IN (SELECT schemaid
                                              FROM   arschema
                                              WHERE  NAME =
                                             'SMT:UnionSmartIT_TicketConsole')
                                 AND extfield IN (SELECT unionfieldname
                                                  FROM
                                     shr_union_datasource_fields
                                                  WHERE
                                     status = 0
                                     AND unionusedby =
                                                 'Smart IT Ticket Console'
                                                         AND NOT (
                                     convertenumvals IS
                                     NULL ))) AS
                         t7
                         INNER JOIN field_enum_values AS t8
                                 ON t7.schemaid = t8.schemaid
                                    AND t7.fieldid = t8.fieldid) AS t10
              ON t9.unionfieldname = t10.extfield
                 AND t9.value = t10.value
WHERE  extfield IS NULL 



/* ================================================= */
/*          U__ views created for data views         */
/* ================================================= */

SELECT CONCAT('U__',SourceDBViewName) FROM SHR_Union_DataSource_Forms WHERE UnionUsedBy='Smart IT Ticket Console' AND Status=0;
SELECT CONCAT('U__',UsedByDBViewName) FROM SHR_Union_DataSource_UsedBy WHERE UnionUsedBy='Smart IT Ticket Console';



