v_DIM_DESIGNATION
The designation dimension contains information about designations
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| DESIGNATIONDIMID | int | BBDW.[DIM_DESIGNATION].[DESIGNATIONDIMID] | |
| DESIGNATIONSYSTEMID | uniqueidentifier | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONSYSTEMID] |
| DESIGNATIONLOOKUPID | nvarchar(512) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONLOOKUPID] |
| DESIGNATIONNAME | nvarchar(512) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONNAME] |
| DESIGNATIONPUBLICNAME | nvarchar(512) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONPUBLICNAME] |
| DESIGNATIONUSE | nvarchar(100) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONUSE] |
| DESIGNATIONISACTIVE | bit | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONISACTIVE] |
| DESIGNATIONREPORT1CODE | nvarchar(100) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONREPORT1CODE] |
| DESIGNATIONREPORT2CODE | nvarchar(100) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONREPORT2CODE] |
| DESIGNATIONSTARTDATE | datetime | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONSTARTDATE] |
| DESIGNATIONSTARTDATEDIMID | int | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONSTARTDATEDIMID] |
| DESIGNATIONENDDATE | datetime | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONENDDATE] |
| DESIGNATIONENDDATEDIMID | int | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONENDDATEDIMID] |
| GLACCOUNTNUMBER | nvarchar(512) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONGLACCOUNTNUMBER] |
| GLCODE | nvarchar(100) | yes | BBDW.[DIM_DESIGNATION].[GLCODE] |
| GLDEPARTMENT | nvarchar(100) | yes | BBDW.[DIM_DESIGNATION].[GLDEPARTMENT] |
| GLPROJECTCODE | nvarchar(100) | yes | BBDW.[DIM_DESIGNATION].[DESIGNATIONPROJECTCODE] |
| VSECATEGORY | nvarchar(70) | yes | BBDW.[DIM_DESIGNATION].[VSECATEGORY] |
| VSESUBCATEGORY | nvarchar(100) | yes | BBDW.[DIM_DESIGNATION].[VSESUBCATEGORY] |
| VSEPURPOSE | nvarchar(20) | yes | BBDW.[DIM_DESIGNATION].[VSEPURPOSE] |
| VSEUSAGE | nvarchar(20) | yes | BBDW.[DIM_DESIGNATION].[VSEUSAGE] |
| PURPOSELEVEL1DIMID | int | yes | BBDW.[DIM_DESIGNATION].[PURPOSELEVEL1DIMID] |
| PURPOSELEVEL1NAME | nvarchar(100) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELNAME] |
| PURPOSELEVEL1PUBLICNAME | nvarchar(512) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELPUBLICNAME] |
| PURPOSELEVEL1DESCRIPTION | nvarchar(255) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELDESCRIPTION] |
| PURPOSELEVEL2DIMID | int | yes | BBDW.[DIM_DESIGNATION].[PURPOSELEVEL2DIMID] |
| PURPOSELEVEL2NAME | nvarchar(100) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELNAME] |
| PURPOSELEVEL2PUBLICNAME | nvarchar(512) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELPUBLICNAME] |
| PURPOSELEVEL2DESCRIPTION | nvarchar(255) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELDESCRIPTION] |
| PURPOSELEVEL3DIMID | int | yes | BBDW.[DIM_DESIGNATION].[PURPOSELEVEL3DIMID] |
| PURPOSELEVEL3NAME | nvarchar(100) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELNAME] |
| PURPOSELEVEL3PUBLICNAME | nvarchar(512) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELPUBLICNAME] |
| PURPOSELEVEL3DESCRIPTION | nvarchar(255) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELDESCRIPTION] |
| PURPOSELEVEL4DIMID | int | yes | BBDW.[DIM_DESIGNATION].[PURPOSELEVEL4DIMID] |
| PURPOSELEVEL4NAME | nvarchar(100) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELNAME] |
| PURPOSELEVEL4PUBLICNAME | nvarchar(512) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELPUBLICNAME] |
| PURPOSELEVEL4DESCRIPTION | nvarchar(255) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELDESCRIPTION] |
| PURPOSELEVEL5DIMID | int | yes | BBDW.[DIM_DESIGNATION].[PURPOSELEVEL5DIMID] |
| PURPOSELEVEL5NAME | nvarchar(100) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELNAME] |
| PURPOSELEVEL5PUBLICNAME | nvarchar(512) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELPUBLICNAME] |
| PURPOSELEVEL5DESCRIPTION | nvarchar(255) | yes | BBDW.[DIM_PURPOSELEVEL].[PURPOSELEVELDESCRIPTION] |
| SITESYSTEMID | uniqueidentifier | yes | BBDW.[DIM_DESIGNATION].[SITESYSTEMID] |
| SITEDIMID | int | yes | BBDW.[DIM_DESIGNATION].[SITEDIMID] |
Definition
Copy
CREATE view [BBDW].[v_DIM_DESIGNATION] as
select
d.[DESIGNATIONDIMID],
d.[DESIGNATIONSYSTEMID],
d.[DESIGNATIONLOOKUPID],
d.[DESIGNATIONNAME],
d.[DESIGNATIONPUBLICNAME],
d.[DESIGNATIONUSE],
d.[DESIGNATIONISACTIVE],
d.[DESIGNATIONREPORT1CODE],
d.[DESIGNATIONREPORT2CODE],
d.[DESIGNATIONSTARTDATE],
d.[DESIGNATIONSTARTDATEDIMID],
d.[DESIGNATIONENDDATE],
d.[DESIGNATIONENDDATEDIMID],
d.[DESIGNATIONGLACCOUNTNUMBER] as [GLACCOUNTNUMBER],
d.[GLCODE],
d.[GLDEPARTMENT],
d.[DESIGNATIONPROJECTCODE] as [GLPROJECTCODE],
d.[VSECATEGORY],
d.[VSESUBCATEGORY],
d.[VSEPURPOSE],
d.[VSEUSAGE],
d.[PURPOSELEVEL1DIMID],
pl1.[PURPOSELEVELNAME] as [PURPOSELEVEL1NAME],
pl1.[PURPOSELEVELPUBLICNAME] as [PURPOSELEVEL1PUBLICNAME],
pl1.[PURPOSELEVELDESCRIPTION] as [PURPOSELEVEL1DESCRIPTION],
d.[PURPOSELEVEL2DIMID],
pl2.[PURPOSELEVELNAME] as [PURPOSELEVEL2NAME],
pl2.[PURPOSELEVELPUBLICNAME] as [PURPOSELEVEL2PUBLICNAME],
pl2.[PURPOSELEVELDESCRIPTION] as [PURPOSELEVEL2DESCRIPTION],
d.[PURPOSELEVEL3DIMID],
pl3.[PURPOSELEVELNAME] as [PURPOSELEVEL3NAME],
pl3.[PURPOSELEVELPUBLICNAME] as [PURPOSELEVEL3PUBLICNAME],
pl3.[PURPOSELEVELDESCRIPTION] as [PURPOSELEVEL3DESCRIPTION],
d.[PURPOSELEVEL4DIMID],
pl4.[PURPOSELEVELNAME] as [PURPOSELEVEL4NAME],
pl4.[PURPOSELEVELPUBLICNAME] as [PURPOSELEVEL4PUBLICNAME],
pl4.[PURPOSELEVELDESCRIPTION] as [PURPOSELEVEL4DESCRIPTION],
d.[PURPOSELEVEL5DIMID],
pl5.[PURPOSELEVELNAME] as [PURPOSELEVEL5NAME],
pl5.[PURPOSELEVELPUBLICNAME] as [PURPOSELEVEL5PUBLICNAME],
pl5.[PURPOSELEVELDESCRIPTION] as [PURPOSELEVEL5DESCRIPTION],
d.[SITESYSTEMID],
d.[SITEDIMID]
from
BBDW.[DIM_DESIGNATION] d
inner join BBDW.[DIM_PURPOSELEVEL] pl1
on d.PURPOSELEVEL1DIMID = pl1.PURPOSELEVELDIMID
inner join BBDW.[DIM_PURPOSELEVEL] as pl2
on d.PURPOSELEVEL2DIMID = pl2.PURPOSELEVELDIMID
inner join BBDW.[DIM_PURPOSELEVEL] as pl3
on d.PURPOSELEVEL3DIMID = pl3.PURPOSELEVELDIMID
inner join BBDW.[DIM_PURPOSELEVEL] as pl4
on d.PURPOSELEVEL4DIMID = pl4.PURPOSELEVELDIMID
inner join BBDW.[DIM_PURPOSELEVEL] as pl5
on d.PURPOSELEVEL5DIMID = pl5.PURPOSELEVELDIMID;