Back to OIM Explorer

dbo.QER_FTLicenseOverview

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 13.455 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVGUIDToModuleOwner source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL566 lines
1CREATE FUNCTION dbo.QER_FTLicenseOverview(2) RETURNS @ergFinal TABLE(SortOrder Int,3ObjectClass VARCHAR(32) collate database_default,4Object VARCHAR(256) collate database_default,5CountObjects int6)7AS8BEGIN9  DECLARE @LiteLimit int = 3,10  @ProdLevel int = 211  DECLARE @erg TABLE(SortOrder Int,12  ObjectClass VARCHAR(256) collate database_default,13  Object VARCHAR(256) collate database_default,14  CountObjects int)15  IF EXISTS(16    SELECT TOP 1 117    FROM dialogdatabase18    WHERE19      ismaindatabase = 1 AND ProductionLevel = @prodlevel)20  BEGIN21    IF EXISTS(22      SELECT TOP 1 123      FROM qbmmoduledef24      WHERE25        ModuleName = 'TSB')26    BEGIN27      INSERT INTO @Erg28      SELECT29        Sortorder,30        ObjectClass,31        Object,32        sum(CountObjects) AS CountObjects33      FROM(34      SELECT35        1 AS Sortorder,36        N 'LicenseType' AS ObjectClass,37        N 'Managed Person' AS Object,38        0 AS CountObjects39      UNION40      SELECT41        2 AS Sortorder,42        N 'LicenseType' AS ObjectClass,43        N 'Managed Lite Person' AS Object,44        0 AS CountObjects45      UNION46      SELECT47        3 AS Sortorder,48        N 'LicenseType' AS ObjectClass,49        N 'Managed External Person' AS Object,50        0 AS CountObjects51      UNION52      SELECT53        4 AS Sortorder,54        N 'LicenseType' AS ObjectClass,55        N 'Managed Thing' AS Object,56        0 AS CountObjects57      UNION58      SELECT59        Sortorder,60        N 'LicenseType' AS ObjectClass,61        LicenseType AS Object,62        count(*) AS CountObjects63      FROM(64      SELECT65        1 AS Sortorder, N 'Managed Person' AS LicenseType, *66      FROM(67      SELECT68        count(*) AS CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType69      FROM(70      SELECT71        isnull(p.uid_personmasteridentity, p.uid_person) AS uid_personmasteridentity, p.uid_person, v2.uid_unsaccount,72        pm.EmployeeType, pm.IdentityType73      FROM person p74      JOIN person pm75        ON pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person)76      JOIN unsaccount v277        ON v2.UID_Person = p.UID_Person AND p.IsInActive = 078      WHERE79        isnull(pm.employeetype, 'None') IN('Employee', 'Apprentice', 'Contractor', 'Other', 'None') AND pm.IdentityType NOT IN('Sponsored',80      'Shared', 'Service', 'Machine')) v381      GROUP BY UID_PersonMasterIdentity, EmployeeType, IdentityType) v82      UNION all83      SELECT84        1 AS Sortorder, N 'Managed Person' AS LicenseType, *85      FROM(86      SELECT87        count(*) AS CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType88      FROM(89      SELECT90        isnull(p.uid_personmasteridentity, p.uid_person) AS uid_personmasteridentity, p.uid_person, v2.uid_unsaccount,91        pm.EmployeeType, pm.IdentityType92      FROM person p93      JOIN person pm94        ON pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person)95      JOIN unsaccount v296        ON v2.UID_Person = p.UID_Person AND p.IsInActive = 097      WHERE98        isnull(pm.employeetype, 'None') IN('manual') AND pm.IdentityType NOT IN('Sponsored', 'Shared', 'Service',99      'Machine')) v3100      GROUP BY UID_PersonMasterIdentity, EmployeeType, IdentityType) v101      WHERE102        CountAccounts > @LiteLimit103      UNION all104      SELECT105        2 AS Sortorder, N 'Managed Lite Person' AS LicenseType, *106      FROM(107      SELECT108        count(*) AS CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType109      FROM(110      SELECT111        isnull(p.uid_personmasteridentity, p.uid_person) AS uid_personmasteridentity, p.uid_person, v2.uid_unsaccount,112        pm.EmployeeType, pm.IdentityType113      FROM person p114      JOIN person pm115        ON pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person)116      JOIN unsaccount v2117        ON v2.UID_Person = p.UID_Person AND p.IsInActive = 0118      WHERE119        isnull(pm.employeetype, 'None') IN('manual') AND pm.IdentityType NOT IN('Sponsored', 'Shared', 'Service',120      'Machine')) v3121      GROUP BY UID_PersonMasterIdentity, EmployeeType, IdentityType) v122      WHERE123        CountAccounts < @LiteLimit + 1124      UNION all125      SELECT126        3 AS Sortorder, N 'Managed External Person' AS LicenseType, *127      FROM(128      SELECT129        count(*) AS CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType130      FROM(131      SELECT132        isnull(p.uid_personmasteridentity, p.uid_person) AS uid_personmasteridentity, p.uid_person, v2.uid_unsaccount,133        pm.EmployeeType, pm.IdentityType134      FROM person p135      JOIN person pm136        ON pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person)137      JOIN unsaccount v2138        ON v2.UID_Person = p.UID_Person AND p.IsInActive = 0139      WHERE140        pm.employeetype IN('Consultant', 'Partner', 'Customer') AND pm.IdentityType NOT IN('Sponsored',141      'Shared', 'Service', 'Machine')) v3142      GROUP BY UID_PersonMasterIdentity, EmployeeType, IdentityType) v143      UNION all144      SELECT145        4 AS Sortorder, N 'Managed Thing' AS LicenseType, *146      FROM(147      SELECT148        count(*) AS CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType149      FROM(150      SELECT151        isnull(p.uid_personmasteridentity, p.uid_person) AS uid_personmasteridentity, p.uid_person, v2.uid_unsaccount,152        pm.EmployeeType, pm.IdentityType153      FROM person p154      JOIN person pm155        ON pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person)156      JOIN unsaccount v2157        ON v2.UID_Person = p.UID_Person AND p.IsInActive = 0158      WHERE159        pm.IdentityType IN('Sponsored', 'Shared', 'Service', 'Machine')) v3160      GROUP BY UID_PersonMasterIdentity, EmployeeType, IdentityType) v) x161      GROUP BY Sortorder,162      LicenseType) v1163      GROUP BY Sortorder,164      ObjectClass,165      Object166      INSERT INTO @Erg167      SELECT 9 +ROW_NUMBER() OVER(168      ORDER BY md.DisplayValue ASC) AS Sortorder,169      N 'Addon License Modules' AS ObjectClass,170      md.DisplayValue AS Object,171      1 AS CountObjects172      FROM QBMModuleDef md173      WHERE174      LEFT(md.UID_ModuleDef,175      3) IN('EBS',176      'EPC',177      'SAP',178      'ACN',179      'QAM')180      INSERT INTO @Erg181      SELECT182        21 AS Sortorder,183        N 'IdentityObjectClass' AS ObjectClass,184        N 'Active Identities' AS Object,185        count(*) AS CountObjects186      FROM person187      WHERE188        IsInActive = 0189      INSERT INTO @Erg190      SELECT191        22 AS Sortorder,192        N 'IdentityObjectClass' AS ObjectClass,193        N 'Active Identities having user account' AS Object,194        count(*) AS CountObjects195      FROM person196      WHERE197        IsInActive = 0 AND uid_person IN(198      SELECT uid_person199      FROM unsaccount)200      INSERT INTO @Erg201      SELECT202        23 AS Sortorder,203        N 'IdentityObjectClass' AS ObjectClass,204        N 'Internal Active Identities' AS Object,205        count(*) AS CountObjects206      FROM person207      WHERE208        isnull(employeetype,209      'None') IN('Employee',210      'Apprentice',211      'Contractor',212      'Other',213      'None') AND IsInactive = 0214      INSERT INTO @Erg215      SELECT216        24 AS Sortorder,217        N 'IdentityObjectClass' AS ObjectClass,218        N 'Internal Active Identities having user Account' AS Object,219        count(*) AS CountObjects220      FROM person221      WHERE222        isnull(employeetype,223      'None') IN('Employee',224      'Apprentice',225      'Contractor',226      'Other',227      'None') AND IsInactive = 0 AND uid_person IN(228      SELECT uid_person229      FROM unsaccount)230      INSERT INTO @Erg231      SELECT232        25 AS Sortorder,233        N 'IdentityObjectClass' AS ObjectClass,234        N 'External Active Identities' AS Object,235        count(*) AS CountObjects236      FROM person237      WHERE238        employeetype IN('Consultant',239      'Partner',240      'Customer') AND IsInactive = 0241      INSERT INTO @Erg242      SELECT243        26 AS Sortorder,244        N 'IdentityObjectClass' AS ObjectClass,245        N 'External Active Identities having user Account' AS Object,246        count(*) AS CountObjects247      FROM person248      WHERE249        employeetype IN('Consultant',250      'Partner',251      'Customer') AND IsInactive = 0 AND uid_person IN(252      SELECT uid_person253      FROM unsaccount)254      INSERT INTO @Erg255      SELECT256        27 AS Sortorder,257        N 'IdentityObjectClass' AS ObjectClass,258        N 'Active Main Identities' AS Object,259        count(*) AS CountObjects260      FROM person261      WHERE262        isnull(uid_personmasteridentity,263      '') = '' AND IsInActive = 0264      INSERT INTO @Erg265      SELECT266        28 AS Sortorder,267        N 'IdentityObjectClass' AS ObjectClass,268        N 'Active Main Identities having user account' AS Object,269        count(*) AS CountObjects270      FROM person271      WHERE272        isnull(uid_personmasteridentity,273      '') = '' AND IsInActive = 0 AND uid_person IN(274      SELECT uid_person275      FROM unsaccount)276      INSERT INTO @Erg277      SELECT278        29 AS Sortorder,279        N 'IdentityObjectClass' AS ObjectClass,280        N 'Inactive Main Identities' AS Object,281        count(*) AS CountObjects282      FROM person283      WHERE284        isnull(uid_personmasteridentity,285      '') = '' AND IsInActive = 1286      INSERT INTO @Erg287      SELECT288        30 AS Sortorder,289        N 'IdentityObjectClass' AS ObjectClass,290        N 'Inactive Main Identities having user account' AS Object,291        count(*) AS CountObjects292      FROM person293      WHERE294        isnull(uid_personmasteridentity,295      '') = '' AND IsInActive = 1 AND uid_person IN(296      SELECT uid_person297      FROM unsaccount)298      INSERT INTO @Erg299      SELECT300        31 AS Sortorder,301        N 'IdentityObjectClass' AS ObjectClass,302        N 'Active Sub Identities' AS Object,303        count(*) AS CountObjects304      FROM person305      WHERE306        isnull(uid_personmasteridentity,307      '') <> '' AND IsInActive = 0308      INSERT INTO @Erg309      SELECT310        32 AS Sortorder,311        N 'IdentityObjectClass' AS ObjectClass,312        N 'Active Sub Identities having user account' AS Object,313        count(*) AS CountObjects314      FROM person315      WHERE316        isnull(uid_personmasteridentity,317      '') <> '' AND IsInActive = 0 AND uid_person IN(318      SELECT uid_person319      FROM unsaccount)320      INSERT INTO @Erg321      SELECT322        33 AS Sortorder,323        N 'IdentityObjectClass' AS ObjectClass,324        N 'Inactive Sub Identities' AS Object,325        count(*) AS CountObjects326      FROM person327      WHERE328        isnull(uid_personmasteridentity,329      '') <> '' AND IsInActive = 1330      INSERT INTO @Erg331      SELECT332        34 AS Sortorder,333        N 'IdentityObjectClass' AS ObjectClass,334        N 'Inactive Sub Identities having user account' AS Object,335        count(*) AS CountObjects336      FROM person337      WHERE338        isnull(uid_personmasteridentity,339      '') <> '' AND IsInActive = 1 AND uid_person IN(340      SELECT uid_person341      FROM unsaccount)342      INSERT INTO @Erg343      SELECT344        35 AS Sortorder,345        N 'IdentityObjectClass' AS ObjectClass,346        N 'Active Sub Identities having Inactive Main Identity' AS Object,347        count(*) AS CountObjects348      FROM person349      WHERE350        isnull(uid_personmasteridentity,351      '') <> '' AND IsInActive = 0 AND UID_PersonMasterIdentity IN(352      SELECT uid_person353      FROM person354      WHERE355        IsInActive=1)356      INSERT INTO @Erg357      SELECT 39+ROW_NUMBER() OVER(358      ORDER BY IdentityType ASC) AS Sortorder,359      N 'IdentityClassification' AS ObjectClass,360      'Identities IdentityType ' + Identitytype AS Object,361      count(*) AS CountObjects362      FROM person363      WHERE364        IsInActive = 0365      GROUP BY IdentityType366      INSERT INTO @Erg367      SELECT 59+ROW_NUMBER() OVER(368      ORDER BY EmployeeType ASC) AS Sortorder,369      N 'IdentityClassification' AS ObjectClass,370      'Identities EmployeeType ' + EmployeeType AS Object,371      count(*) AS CountObjects372      FROM person373      WHERE374        IsInActive = 0375      GROUP BY EmployeeType376      INSERT INTO @Erg377      SELECT378        convert(int,379        69 + row_number() OVER(380      ORDER BY x.object)) AS SortOrder,381      N 'IdentityClassification' AS Objectclass,382      x.OBJECT,383      count(*) AS CountObjects384      FROM(385      SELECT386        CONCAT('Identities ', p.IdentityType, ', ', p.EmployeeType, ', ', CASE p.IsInActive387        WHEN 0 THEN388        'active'389      ELSE 'inactive'390      END, ' having user account') AS OBJECT391      FROM person p392      WHERE393        uid_person IN(394      SELECT UID_Person395      FROM unsaccount)) AS x396      GROUP BY x.OBJECT397      INSERT INTO @Erg398      SELECT399        convert(int,400        79 + row_number() OVER(401      ORDER BY x.object)) AS SortOrder,402      N 'IdentityClassification' AS Objectclass,403      x.OBJECT,404      count(*) AS CountObjects405      FROM(406      SELECT407        CONCAT('Identities ', p.IdentityType, ', ', p.EmployeeType, ', ', CASE p.IsInActive408        WHEN 0 THEN409        'active'410      ELSE 'inactive'411      END) AS OBJECT412      FROM person p) AS x413      GROUP BY x.OBJECT414      INSERT INTO @Erg415      SELECT416        100 AS SortOrder,417        N 'DBCount' AS ObjectClass,418        N 'History Databases' AS Object,419        Count(*) AS CountObjects420      FROM DialogHistoryDB421      INSERT INTO @Erg422      SELECT423        101 AS SortOrder,424        N 'DBCount' AS ObjectClass,425        N 'Custom Target Systems' AS Object,426        Count(*) AS CountObjects427      FROM UNSRoot428      WHERE429        dbo.QBM_FCVGUIDToModuleOwner(UID_UNSRoot) = 'CCC'430      INSERT INTO @Erg431      SELECT432        102 AS SortOrder,433        N 'DBCount' AS ObjectClass,434        N 'Business Roles ' AS Object,435        Count(*) AS CountObjects436      FROM Basetree437      WHERE438        UID_OrgRoot LIKE '____[^V]%'439      INSERT INTO @Erg440      SELECT441        103 AS SortOrder,442        N 'DBCount' AS ObjectClass,443        N 'System Roles' AS Object,444        Count(*) AS CountObjects445      FROM ESet446      WHERE447        dbo.QBM_FCVGUIDToModuleOwner(UID_Eset) = 'CCC'448      IF EXISTS(449        SELECT TOP 1 1450        FROM QBMModuleDef451        WHERE452          ModuleName = 'AOB')453      BEGIN454        INSERT INTO @Erg455        SELECT456          104 AS SortOrder,457          N 'DBCount' AS ObjectClass,458          N 'Business Applications' AS Object,459          Count(*) AS CountObjects460        FROM AOBApplication461        WHERE462          dbo.QBM_FCVGUIDToModuleOwner(UID_AOBApplication) = 'CCC'463        INSERT INTO @Erg464        SELECT465          105 AS SortOrder,466          N 'DBCount' AS ObjectClass,467          N 'Business Application Entitlements' AS Object,468          Count(*) AS CountObjects469        FROM AOBEntitlement470        WHERE471          dbo.QBM_FCVGUIDToModuleOwner(UID_AOBEntitlement) = 'CCC'472      END473      INSERT INTO @Erg474      SELECT475        106 AS SortOrder,476        N 'DBCount' AS ObjectClass,477        N 'Service Items' AS Object,478        CountRows AS CountObjects479      FROM dialogtable480      WHERE481        tablename = 'AccProduct'482      INSERT INTO @Erg483      SELECT484        112 AS SortOrder,485        N 'ObjectCount' AS ObjectClass,486        N 'Hardware PC' AS Object,487        Count(*) AS CountObjects488      FROM Hardware489      WHERE490        (ispc = 1)491      INSERT INTO @Erg492      SELECT493        113 AS SortOrder,494        N 'ObjectCount' AS ObjectClass,495        N 'Hardware Server' Object,496        Count(*) AS CountObjects497      FROM Hardware498      WHERE499        (isserver = 1)500      IF EXISTS(501        SELECT TOP 1 1502        FROM QBMModuleDef503        WHERE504          ModuleName = 'ADS')505      BEGIN506        INSERT INTO @Erg507        SELECT508          114 AS SortOrder,509          N 'ObjectCount' AS Object,510          N 'Hardware having Active Directory account' AS Object,511          Count(*) AS CountObjects512        FROM ADSMachine513      END514      INSERT INTO @Erg515      SELECT516        115 AS SortOrder,517        N 'ObjectCount' AS ObjectClass,518        N 'Hardware VM Host' AS Object,519        Count(*) AS CountObjects520      FROM Hardware521      WHERE522        (ispc = 1 OR isServer = 1) AND IsVMHost = 1523      INSERT INTO @Erg524      SELECT525        116 AS SortOrder,526        N 'ObjectCount' AS ObjectClass,527        N 'Hardware VM Client' AS Object,528        Count(*) AS CountObjects529      FROM Hardware530      WHERE531        (ispc = 1 OR isServer = 1) AND IsVMClient = 1532      IF EXISTS(533        SELECT TOP 1 1534        FROM QBMModuleDef535        WHERE536          ModuleName = 'QAM')537      BEGIN538        INSERT INTO @Erg539        SELECT540          117 AS SortOrder,541          N 'ObjectCount' AS Object,542          N 'Data objects under Governance' AS Object,543          Count(*) AS CountObjects544        FROM QAMDuG545        WHERE546          dbo.QBM_FCVGUIDToModuleOwner(UID_QAMDuG) = 'CCC'547      END548    END549  END550  INSERT INTO @ergFinal(SortOrder,551  ObjectClass,552  Object,553  CountObjects)554  SELECT555    e.SortOrder,556    isnull(557    LEFT(e.ObjectClass, 32),558    ''),559    isnull(560    LEFT(e.Object, 256),561    ''),562    e.CountObjects563  FROM @erg e564  ORDER BY e.SortOrder565  RETURN566END
Open raw exported source
SQL ยท Raw87 lines
1create function dbo.QER_FTLicenseOverview () RETURNS @ergFinal TABLE ( SortOrder Int ,ObjectClass VARCHAR(32) collate database_default ,Object VARCHAR(2562) collate database_default ,CountObjects int ) AS BEGIN declare @LiteLimit int = 3, @ProdLevel int = 2 declare @erg TABLE ( SortOrder Int ,ObjectClass 3VARCHAR(256) collate database_default ,Object VARCHAR(256) collate database_default ,CountObjects int ) If exists (select top 1 1 from dialogdatabase where4 ismaindatabase = 1 and ProductionLevel = @prodlevel) Begin IF exists (select top 1 1 from qbmmoduledef where ModuleName = 'TSB')  Begin Insert into @Erg5 select Sortorder, ObjectClass, Object, sum(CountObjects) as CountObjects from ( select 1 as Sortorder, N'LicenseType' as ObjectClass, N'Managed Person'6 as Object, 0 as CountObjects union select 2 as Sortorder, N'LicenseType' as ObjectClass, N'Managed Lite Person' as Object, 0 as CountObjects union select7 3 as Sortorder, N'LicenseType' as ObjectClass, N'Managed External Person' as Object, 0 as CountObjects union select 4 as Sortorder, N'LicenseType' as 8ObjectClass, N'Managed Thing' as Object, 0 as CountObjects union  select Sortorder, N'LicenseType' as ObjectClass, LicenseType as Object, count(*) as CountObjects9 from (  select 1 as Sortorder, N'Managed Person' as LicenseType, * from (select count(*) as CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType10 from ( select isnull(p.uid_personmasteridentity, p.uid_person) as uid_personmasteridentity, p.uid_person, v2.uid_unsaccount, pm.EmployeeType, pm.IdentityType11 from person p join person pm on pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person) join unsaccount v2 on v2.UID_Person = p.UID_Person and12 p.IsInActive = 0 where isnull(pm.employeetype,'None') in ('Employee', 'Apprentice', 'Contractor', 'Other', 'None')  and pm.IdentityType not in ('Sponsored'13, 'Shared', 'Service', 'Machine') ) v3 group by UID_PersonMasterIdentity, EmployeeType, IdentityType) v  Union all  select 1 as Sortorder, N'Managed Person'14 as LicenseType, * from (select count(*) as CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType from ( select isnull(p.uid_personmasteridentity15, p.uid_person) as uid_personmasteridentity, p.uid_person, v2.uid_unsaccount, pm.EmployeeType, pm.IdentityType from person p join person pm on pm.uid_person16 = isnull(p.uid_personmasteridentity, p.uid_person) join unsaccount v2 on v2.UID_Person = p.UID_Person and p.IsInActive = 0 where isnull(pm.employeetype17,'None') in ('manual')  and pm.IdentityType not in ('Sponsored', 'Shared', 'Service', 'Machine') ) v3 group by UID_PersonMasterIdentity, EmployeeType, 18IdentityType) v where CountAccounts > @LiteLimit union all  select 2 as Sortorder, N'Managed Lite Person' as LicenseType, * from (select count(*) as CountAccounts19, UID_PersonMasterIdentity, EmployeeType, IdentityType from ( select isnull(p.uid_personmasteridentity, p.uid_person) as uid_personmasteridentity, p.uid_person20, v2.uid_unsaccount, pm.EmployeeType, pm.IdentityType from person p join person pm on pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person) 21join unsaccount v2 on v2.UID_Person = p.UID_Person and p.IsInActive = 0  where isnull(pm.employeetype,'None') in ('manual') and pm.IdentityType not in 22('Sponsored', 'Shared', 'Service', 'Machine') ) v3 group by UID_PersonMasterIdentity, EmployeeType, IdentityType) v where CountAccounts < @LiteLimit + 231 union all  select 3 as Sortorder, N'Managed External Person' as LicenseType, * from (select count(*) as CountAccounts, UID_PersonMasterIdentity, EmployeeType24, IdentityType from ( select isnull(p.uid_personmasteridentity, p.uid_person) as uid_personmasteridentity, p.uid_person, v2.uid_unsaccount, pm.EmployeeType25, pm.IdentityType from person p join person pm on pm.uid_person = isnull(p.uid_personmasteridentity, p.uid_person) join unsaccount v2 on v2.UID_Person 26= p.UID_Person and p.IsInActive = 0 where pm.employeetype in ('Consultant', 'Partner', 'Customer')  and pm.IdentityType not in ('Sponsored', 'Shared', 27'Service', 'Machine') ) v3 group by UID_PersonMasterIdentity, EmployeeType, IdentityType) v  union all  select 4 as Sortorder, N'Managed Thing' as LicenseType28, * from (select count(*) as CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType from ( select isnull(p.uid_personmasteridentity, p.uid_person29) as uid_personmasteridentity, p.uid_person, v2.uid_unsaccount, pm.EmployeeType, pm.IdentityType from person p join person pm on pm.uid_person = isnull30(p.uid_personmasteridentity, p.uid_person) join unsaccount v2 on v2.UID_Person = p.UID_Person and p.IsInActive = 0 where pm.IdentityType in ('Sponsored'31, 'Shared', 'Service', 'Machine') ) v3 group by UID_PersonMasterIdentity, EmployeeType, IdentityType) v ) x group by Sortorder,LicenseType ) v1 group by32 Sortorder, ObjectClass, Object Insert into @Erg select 9 +ROW_NUMBER() OVER(ORDER BY md.DisplayValue ASC) AS Sortorder, N'Addon License Modules' as ObjectClass33, md.DisplayValue as Object, 1 as CountObjects from QBMModuleDef md where Left(md.UID_ModuleDef,3) in ('EBS', 'EPC', 'SAP', 'ACN', 'QAM') Insert into @Erg34 Select 21 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Active Identities' as Object, count(*) as CountObjects from person where IsInActive =35 0 Insert into @Erg Select 22 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Active Identities having user account' as Object , count(*) as CountObjects36 from person where IsInActive = 0 and uid_person in (select uid_person from unsaccount) Insert into @Erg Select 23 as Sortorder, N'IdentityObjectClass'37 as ObjectClass, N'Internal Active Identities' as Object, count(*) as CountObjects from person where isnull(employeetype,'None') in ('Employee', 'Apprentice'38, 'Contractor', 'Other', 'None') and IsInactive = 0 Insert into @Erg Select 24 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Internal Active Identities having user Account'39 as Object, count(*) as CountObjects from person where isnull(employeetype,'None') in ('Employee', 'Apprentice', 'Contractor', 'Other', 'None') and IsInactive40 = 0 and uid_person in (select uid_person from unsaccount) Insert into @Erg Select 25 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'External Active Identities'41 as Object, count(*) as CountObjects from person where employeetype in ('Consultant', 'Partner', 'Customer') and IsInactive = 0 Insert into @Erg Select42 26 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'External Active Identities having user Account' as Object, count(*) as CountObjects from person43 where employeetype in ('Consultant', 'Partner', 'Customer') and IsInactive = 0 and uid_person in (select uid_person from unsaccount) Insert into @Erg 44Select 27 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Active Main Identities' as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity45,'') = '' and IsInActive = 0 Insert into @Erg Select 28 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Active Main Identities having user account'46 as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity,'') = '' and IsInActive = 0 and uid_person in (select uid_person47 from unsaccount) Insert into @Erg Select 29 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Inactive Main Identities' as Object, count(*) as CountObjects48 from person where isnull(uid_personmasteridentity,'') = '' and IsInActive = 1 Insert into @Erg Select 30 as Sortorder, N'IdentityObjectClass' as ObjectClass49, N'Inactive Main Identities having user account' as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity,'') = '' and IsInActive50 = 1 and uid_person in (select uid_person from unsaccount) Insert into @Erg Select 31 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Active Sub Identities'51 as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity,'') <> '' and IsInActive = 0 Insert into @Erg Select 32 as Sortorder52, N'IdentityObjectClass' as ObjectClass, N'Active Sub Identities having user account' as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity53,'') <> '' and IsInActive = 0 and uid_person in (select uid_person from unsaccount) Insert into @Erg Select 33 as Sortorder, N'IdentityObjectClass' as 54ObjectClass, N'Inactive Sub Identities' as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity,'') <> '' and IsInActive 55= 1 Insert into @Erg Select 34 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Inactive Sub Identities having user account' as Object, count(*) 56as CountObjects from person where isnull(uid_personmasteridentity,'') <> '' and IsInActive = 1 and uid_person in (select uid_person from unsaccount) Insert57 into @Erg Select 35 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Active Sub Identities having Inactive Main Identity' as Object, count(*) as58 CountObjects from person where isnull(uid_personmasteridentity,'') <> '' and IsInActive = 0 and UID_PersonMasterIdentity in (select uid_person from person59 where IsInActive=1) Insert into @Erg SELECT 39+ROW_NUMBER() OVER(ORDER BY IdentityType ASC) AS Sortorder, N'IdentityClassification' as ObjectClass, 'Identities IdentityType '60 + Identitytype as Object, count(*) as CountObjects from person where IsInActive = 0 group by IdentityType Insert into @Erg SELECT 59+ROW_NUMBER() OVER61(ORDER BY EmployeeType ASC) AS Sortorder, N'IdentityClassification' as ObjectClass, 'Identities EmployeeType '+ EmployeeType as Object, count(*) as CountObjects62 from person where IsInActive = 0 group by EmployeeType Insert into @Erg SELECT convert(int, 69 + row_number() OVER (ORDER BY x.object)) AS SortOrder ,63N'IdentityClassification' AS Objectclass , x.OBJECT ,count(*) AS CountObjects from ( select concat('Identities ' , p.IdentityType , ', ' , p.EmployeeType64 , ', ' , case p.IsInActive when 0 then 'active' else 'inactive' end, ' having user account' ) AS OBJECT from person p where uid_person in (select UID_Person65 from unsaccount) ) as x GROUP BY x.OBJECT Insert into @Erg SELECT convert(int, 79 + row_number() OVER (ORDER BY x.object)) AS SortOrder ,N'IdentityClassification'66 AS Objectclass , x.OBJECT ,count(*) AS CountObjects from ( select concat('Identities ' , p.IdentityType , ', ' , p.EmployeeType , ', ' , case p.IsInActive67 when 0 then 'active' else 'inactive' end ) AS OBJECT from person p ) as x GROUP BY x.OBJECT Insert into @Erg select 100 as SortOrder , N'DBCount' as ObjectClass68, N'History Databases' as Object, Count(*) as CountObjects from DialogHistoryDB Insert into @Erg select 101 as SortOrder , N'DBCount' as ObjectClass, N'Custom Target Systems'69 as Object, Count(*) as CountObjects from UNSRoot where dbo.QBM_FCVGUIDToModuleOwner(UID_UNSRoot) = 'CCC' Insert into @Erg select 102 as SortOrder , N'DBCount'70 as ObjectClass, N'Business Roles ' as Object, Count(*) as CountObjects from Basetree where UID_OrgRoot like '____[^V]%' Insert into @Erg select 103 as71 SortOrder , N'DBCount' as ObjectClass, N'System Roles' as Object, Count(*) as CountObjects from ESet where dbo.QBM_FCVGUIDToModuleOwner(UID_Eset) = 'CCC'72 If exists (select top 1 1 from QBMModuleDef where ModuleName = 'AOB') begin Insert into @Erg select 104 as SortOrder , N'DBCount' as ObjectClass, N'Business Applications'73 as Object, Count(*) as CountObjects from AOBApplication where dbo.QBM_FCVGUIDToModuleOwner(UID_AOBApplication) = 'CCC' Insert into @Erg select 105 as 74SortOrder , N'DBCount' as ObjectClass, N'Business Application Entitlements' as Object, Count(*) as CountObjects from AOBEntitlement where dbo.QBM_FCVGUIDToModuleOwner75(UID_AOBEntitlement) = 'CCC' end Insert into @Erg select 106 as SortOrder , N'DBCount' as ObjectClass, N'Service Items' as Object, CountRows as CountObjects76 from dialogtable where tablename = 'AccProduct' Insert into @Erg select 112 as SortOrder , N'ObjectCount' as ObjectClass, N'Hardware PC' as Object, Count77(*) as CountObjects from Hardware where (ispc = 1 ) Insert into @Erg select 113 as SortOrder , N'ObjectCount' as ObjectClass, N'Hardware Server' Object78, Count(*) as CountObjects from Hardware where (isserver = 1 ) If exists (select top 1 1 from QBMModuleDef where ModuleName = 'ADS') begin Insert into 79@Erg select 114 as SortOrder, N'ObjectCount' as Object, N'Hardware having Active Directory account' as Object, Count (*) as CountObjects from ADSMachine80 end Insert into @Erg select 115 as SortOrder , N'ObjectCount' as ObjectClass, N'Hardware VM Host' as Object, Count(*) as CountObjects from Hardware where81 (ispc = 1 or isServer = 1) and IsVMHost = 1 Insert into @Erg select 116 as SortOrder , N'ObjectCount' as ObjectClass, N'Hardware VM Client' as Object,82 Count(*) as CountObjects from Hardware where (ispc = 1 or isServer = 1) and IsVMClient = 1 If exists (select top 1 1 from QBMModuleDef where ModuleName83 = 'QAM') begin Insert into @Erg select 117 as SortOrder, N'ObjectCount' as Object, N'Data objects under Governance' as Object, Count (*) as CountObjects84 from QAMDuG where dbo.QBM_FCVGUIDToModuleOwner(UID_QAMDuG) = 'CCC' end end end insert into @ergFinal( SortOrder  , ObjectClass  , Object  ,CountObjects85 ) select e.SortOrder  , isnull(left(e.ObjectClass,32), '')  , isnull(left(e.Object, 256), '')  ,e.CountObjects  from @erg e order by e.SortOrder RETURN86 END 87