V_INSTALLED_CODETABLECATALOG

Fields

Field	Field Type	Null
ID	uniqueidentifier
DBTABLENAME	nvarchar(100)
CODETABLENAME	nvarchar(100)
CODETABLESPECXML	xml
ISBUILTIN	bit
CATEGORYID	uniqueidentifier
ADDEDBYID	uniqueidentifier
CHANGEDBYID	uniqueidentifier
DATEADDED	datetime
DATECHANGED	datetime
TS	timestamp
TSLONG	bigint	yes
ISSECURABLE	bit
SITEFILTERINGENABLED	bit
SPECUINAME	nvarchar(100)
UINAME	nvarchar(100)

Definition

Copy


create view dbo.V_INSTALLED_CODETABLECATALOG as 
with xmlnamespaces ('bb_appfx_commontypes' as common)
select
    ID,
    DBTABLENAME,
    CODETABLENAME,
    CODETABLESPECXML,
    ISBUILTIN,
    CATEGORYID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED,
    TS,
    TSLONG,
    ISSECURABLE,
    SITEFILTERINGENABLED,
    SPECUINAME,
    UINAME
from 
    dbo.CODETABLECATALOG 
where 
    CODETABLESPECXML.exist('(/*/common:InstalledProductList)[1]/common:InstalledProduct/@ID') = 0
    or
    case
        when CODETABLESPECXML.value('(/*/common:InstalledProductList)[1]/@EvaluationMethod', 'varchar(3)') = N'All' then
            case
                when not exists
                (
                    select null
                    from 
                        CODETABLESPECXML.nodes('(/*/common:InstalledProductList)[1]/common:InstalledProduct') T(c)  
                    left join 
                        dbo.INSTALLEDPRODUCTLIST on T.c.value('@ID', 'uniqueidentifier') = INSTALLEDPRODUCTLIST.ID  
                        and  
                        (  
                            len(INSTALLEDPRODUCTLIST.EXPIREDATE) = 0  
                            or  
                            getdate() <= convert(datetime, substring(INSTALLEDPRODUCTLIST.EXPIREDATE,1,4) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,5,2) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,7,2) + 'T00:00:00')  
                        )  
                    where  
                        INSTALLEDPRODUCTLIST.ID is null  
                ) then 1
                else 0
            end
        else
            case
                when exists 
                (
                    select null
                    from
                        CODETABLESPECXML.nodes('(/*/common:InstalledProductList)[1]/common:InstalledProduct') T(c)  
                    left join 
                        dbo.INSTALLEDPRODUCTLIST on T.c.value('@ID', 'uniqueidentifier') = INSTALLEDPRODUCTLIST.ID  
                    where  
                    (  
                        len(INSTALLEDPRODUCTLIST.EXPIREDATE) = 0  
                        or  
                        getdate() <= convert(datetime, substring(INSTALLEDPRODUCTLIST.EXPIREDATE,1,4) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,5,2) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,7,2) + 'T00:00:00')  
                    )  
                    and  
                    INSTALLEDPRODUCTLIST.ID is not null
                ) then 1
                else 0
            end
    end = 1