Creating an XDS Policy with performance in mind
You are here
Ledger Financial Dimension Tags
Ledger Financial Dimension Tags
Submitted by Nathan Clouse on 09/29/15.
A list of all available and allowed financial dimension by name with a description and the financial dimension the values belong to regardless of if they are in use or not. So, this would be a list of all customers, vendors, projects, items, item groups and so on. If the value is not tied to a financial dimension, its DimensionName value will be null. Useful in other views related to the GL
CREATE VIEW [dbo].[vw_ledgerAllFinancialTags]
AS
SELECT t.*,
'DimensionName' = da.NAME
FROM (SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
'TableId' = 11751
FROM dimattributeassetgroup
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11752
FROM dimattributeassettable
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11753
FROM dimattributebankaccounttable
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11754
FROM dimattributecompanyinfo
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11755
FROM dimattributecustgroup
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11756
FROM dimattributecusttable
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11757
FROM dimattributehcmjob
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11758
FROM dimattributehcmposition
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11759
FROM dimattributehcmworker
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11760
FROM dimattributeinventitemgroup
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11761
FROM dimattributeinventtable
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11762
FROM dimattributemainaccount
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11763
FROM dimattributeombusinessunit
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11764
FROM dimattributeomcostcenter
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11765
FROM dimattributeomdepartment
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
103000
FROM dimattributeomretailchannel
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
11766
FROM dimattributeomvaluestream
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11767
FROM dimattributeprojgroup
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11768
FROM dimattributeprojinvoicetable
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11769
FROM dimattributeprojtable
UNION ALL
SELECT key_,
Cast(value AS VARCHAR(20)),
NAME,
'',
partition,
recid,
103004
FROM dimattributeretailchannel
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
103005
FROM dimattributeretailstore
UNION ALL
SELECT key_,
value,
NAME,
'',
partition,
recid,
103006
FROM dimattributeretailterminal
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11770
FROM dimattributesmmbusreltable
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11771
FROM dimattributesmmcampaigntable
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
103008
FROM dimattributetaxbranch
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11772
FROM dimattributetrvtraveltxt
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11773
FROM dimattributevendgroup
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11774
FROM dimattributevendtable
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11775
FROM dimattributewrkctrresourcegroup
UNION ALL
SELECT key_,
value,
NAME,
dataareaid,
partition,
recid,
11776
FROM dimattributewrkctrtable
UNION ALL
SELECT financialtagcategory,
value,
description,
'',
recid,
partition,
656
FROM dimensionfinancialtag) t
LEFT JOIN dimensionattribute da
ON da.partition = t.partition
AND da.backingentitytype = t.tableid
go
