dbo.QER_FTLicenseOverview

1create function dbo.QER_FTLicenseOverview () RETURNS @ergFinal TABLE ( SortOrder Int ,ObjectClass VARCHAR(32) collate database_default ,Object VARCHAR(256
2) 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 where
4 ismaindatabase = 1 and ProductionLevel = @prodlevel) Begin IF exists (select top 1 1 from qbmmoduledef where ModuleName = 'TSB')  Begin Insert into @Erg
5 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 select
7 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 CountObjects
9 from (  select 1 as Sortorder, N'Managed Person' as LicenseType, * from (select count(*) as CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType
10 from ( select isnull(p.uid_personmasteridentity, p.uid_person) as uid_personmasteridentity, p.uid_person, v2.uid_unsaccount, pm.EmployeeType, pm.IdentityType
11 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 and
12 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_personmasteridentity
15, 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_person
16 = isnull(p.uid_personmasteridentity, p.uid_person) join unsaccount v2 on v2.UID_Person = p.UID_Person and p.IsInActive = 0 where isnull(pm.employeetype
17,'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 CountAccounts
19, UID_PersonMasterIdentity, EmployeeType, IdentityType from ( select isnull(p.uid_personmasteridentity, p.uid_person) as uid_personmasteridentity, p.uid_person
20, 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, EmployeeType
24, IdentityType from ( select isnull(p.uid_personmasteridentity, p.uid_person) as uid_personmasteridentity, p.uid_person, v2.uid_unsaccount, pm.EmployeeType
25, 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 LicenseType
28, * from (select count(*) as CountAccounts, UID_PersonMasterIdentity, EmployeeType, IdentityType from ( select isnull(p.uid_personmasteridentity, p.uid_person
29) as uid_personmasteridentity, p.uid_person, v2.uid_unsaccount, pm.EmployeeType, pm.IdentityType from person p join person pm on pm.uid_person = isnull
30(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 by
32 Sortorder, ObjectClass, Object Insert into @Erg select 9 +ROW_NUMBER() OVER(ORDER BY md.DisplayValue ASC) AS Sortorder, N'Addon License Modules' as ObjectClass
33, md.DisplayValue as Object, 1 as CountObjects from QBMModuleDef md where Left(md.UID_ModuleDef,3) in ('EBS', 'EPC', 'SAP', 'ACN', 'QAM') Insert into @Erg
34 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 CountObjects
36 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 IsInactive
40 = 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 Select
42 26 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'External Active Identities having user Account' as Object, count(*) as CountObjects from person
43 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_personmasteridentity
45,'') = '' 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_person
47 from unsaccount) Insert into @Erg Select 29 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Inactive Main Identities' as Object, count(*) as CountObjects
48 from person where isnull(uid_personmasteridentity,'') = '' and IsInActive = 1 Insert into @Erg Select 30 as Sortorder, N'IdentityObjectClass' as ObjectClass
49, N'Inactive Main Identities having user account' as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity,'') = '' and IsInActive
50 = 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 Sortorder
52, N'IdentityObjectClass' as ObjectClass, N'Active Sub Identities having user account' as Object, count(*) as CountObjects from person where isnull(uid_personmasteridentity
53,'') <> '' 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) Insert
57 into @Erg Select 35 as Sortorder, N'IdentityObjectClass' as ObjectClass, N'Active Sub Identities having Inactive Main Identity' as Object, count(*) as
58 CountObjects from person where isnull(uid_personmasteridentity,'') <> '' and IsInActive = 0 and UID_PersonMasterIdentity in (select uid_person from person
59 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() OVER
61(ORDER BY EmployeeType ASC) AS Sortorder, N'IdentityClassification' as ObjectClass, 'Identities EmployeeType '+ EmployeeType as Object, count(*) as CountObjects
62 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.EmployeeType
64 , ', ' , case p.IsInActive when 0 then 'active' else 'inactive' end, ' having user account' ) AS OBJECT from person p where uid_person in (select UID_Person
65 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.IsInActive
67 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 ObjectClass
68, 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 as
71 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_FCVGUIDToModuleOwner
75(UID_AOBEntitlement) = 'CCC' end Insert into @Erg select 106 as SortOrder , N'DBCount' as ObjectClass, N'Service Items' as Object, CountRows as CountObjects
76 from dialogtable where tablename = 'AccProduct' Insert into @Erg select 112 as SortOrder , N'ObjectCount' as ObjectClass, N'Hardware PC' as Object, Count
77(*) as CountObjects from Hardware where (ispc = 1 ) Insert into @Erg select 113 as SortOrder , N'ObjectCount' as ObjectClass, N'Hardware Server' Object
78, 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 ADSMachine
80 end Insert into @Erg select 115 as SortOrder , N'ObjectCount' as ObjectClass, N'Hardware VM Host' as Object, Count(*) as CountObjects from Hardware where