This table contains account attributes which are based on chartfield attribute tables (CF_ATTRIB_TBL & CF_ATTRIB_VALUE). It can be used with the UM_GLQ_REPRTING table to improve performance.
Accounting period is not evaluated on this table and only fiscal year is used.
Not all of accounts are on this table. For example, the 9xxxx series of accounts are statistic accounts that contain data used by our allocation processes or the number of shares held in one of our investment pools; thus, we do not attach the attributes to this account series.
| Key | Record.Fieldname | Format | XLAT | Heading Text | Source table(s) |
|---|---|---|---|---|---|
| → | ACCOUNT - Account | Char10 | Account | ||
| DESCR - Description | Char30 | Descr | GL_ACCOUNT_TBL | ||
| DESCRSHORT - Short Description | Char10 | Short Desc | |||
| EFF_STATUS - Status as of Effective Date | Char1 | N | Status | ||
| → | FISCAL_YEAR - Fiscal Year | Num4.0 | Year | ||
| UM_ACCT_CASH_FLW - Account Cash Flow Class | Char20 | Cash Flow | |||
| UM_ACCT_CASH_FLW_D - Account Cash Flow Class | Char60 | Cash Flow | |||
| UM_ACCT_CAT - Account Category | Char20 | Acct Cat | |||
| UM_ACCT_CAT_D - Account Category Descr | Char60 | Acct Cat D | |||
| UM_ACCT_DTL - Account Detail | Char20 | Acct Dtl | |||
| UM_ACCT_DTL_D - Account Detail Descr | Char60 | Acct Dtl D | |||
| UM_ACCT_GRP - Account Group | Char20 | Acct Grp | |||
| UM_ACCT_GRP_D - Account Group Descr | Char60 | Acct Grp D | |||
| UM_ACCT_GRP_SHRT - Account Group Short | Char20 | Acct Grp Shrt | |||
| UM_ACCT_GRP_SHRT_D - Account Group Short Descr | Char60 | Acct Grp Shrt D | |||
| UM_ACCT_SUBCAT - Account Subcategory | Char20 | Acct Subcat | |||
| UM_ACCT_SUBCAT_D - Account Subcategory Descr | Char60 | Acct Subcat D | |||
| UM_ACCT_SUM - Account Summary | Char20 | Acct Sum | |||
| UM_ACCT_SUM_D - Account Summary Descr | Char60 | Acct Sum D | |||
| UM_ACCT_SUM_SHRT - Account Summary Short | Char20 | Acct Sum Shrt | |||
| UM_ACCT_SUM_SHRT_D - Account Summary Short Descr | Char60 | Acct Sum Shrt D | |||
| UM_ACCT_TYPE - Account Type | Char20 | Acct Typ | |||
| UM_ACCT_TYPE_D - Account Type Descr | Char60 | Acct Typ D | |||
| UM_RPT_CAT - Report Category | Char20 | Rpt Cat | |||
| UM_RPT_CAT_D - Report Category Descr | Char60 | Rpt Cat D | |||
| UM_RPT_GRP - Report Group | Char20 | Rpt Grp | |||
| UM_RPT_GRP_D - Report Group Descr | Char60 | Rpt Grp D | |||
| UM_RPT_TYPE - Report Type | Char20 | Rpt Typ | |||
| UM_RPT_TYPE_D - Report Type Description | Char60 | Rpt Typ D |
The SQR code is located in S:\FIN\SQR\Prod\umglacta.sqr.
The code below is a part of SQR which pulls account attributes.
SELECT DISTINCT
rg.fieldname,
rg.chartfield_value,
rg.CF_ATTRIBUTE,
rg.CF_ATTRIB_VALUE,
rh.descr60
FROM
SYSADM.PS_CF_ATTRIB_TBL rg,
SYSADM.PS_CF_ATTRIB_VALUE rh
WHERE
rg.fieldname = 'ACCOUNT'
AND RG.CF_ATTRIBUTE IN ('ACCT_CATEG', 'ACCT_CATEG_DESCR', 'ACCT_DETAIL', 'ACCT_DETAIL_DESCR',
'ACCT_GROUP', 'ACCT_GROUP_DESCR', 'ACCT_GRP_SHORT', 'ACCT_GRP_SHORT_DESCR', 'ACCT_SUBCAT',
'ACCT_SUBCAT_DESCR', 'ACCT_SUM', 'ACCT_SUM_DESCR', 'ACCT_SUM_SHORT', 'ACCT_SUM_SHORT_DESCR',
'ACCT_TYPE', 'ACCT_TYPE_DESCR', 'RPT_CATEG', 'RPT_CATEG_DESCR', 'RPT_GROUP', 'RPT_GROUP_DESCR',
'RPT_TYPE', 'RPT_TYPE_DESCR', 'CASH_FLOW_CLASS', 'CASH_FLOW_CLASS_DESCR')
AND rg.SETID = 'UMSYS'
AND rg.EFFDT = (SELECT MAX(BEF.EFFDT)
FROM sysadm.PS_CF_ATTRIB_TBL BEF
WHERE rg.SETID = BEF.SETID
AND rg.CHARTFIELD_VALUE = BEF.CHARTFIELD_VALUE
AND rg.FIELDNAME = BEF.FIELDNAME
AND rg.CF_ATTRIBUTE = BEF.CF_ATTRIBUTE
AND BEF.EFFDT <= sysdate)
AND rh.SETID = rg.SETID
AND rh.FIELDNAME = rg.FIELDNAME
AND rh.CF_ATTRIBUTE = rg.CF_ATTRIBUTE
AND rh.CF_ATTRIB_VALUE = rg.CF_ATTRIB_VALUE
Data Dictionary Links
Clicking the linked (blue) fields in any table will take you to the Data Dictionary and provide you with more information about that field as well as field values if they are available.