Created by Corina C Larsen, last modified by Miki Yanagi on May 12, 2022
This is the highly recommended and frequently used table for Finance reporting. This is summary data table and the data is aggregated by FISCAL_YEAR, ACCOUNTING_PERIOD, BUSINESS_UNIT, DEPTID, PROJECT_ID, PROGRAM_CODE, FUND_CODE, OPERATING_UNIT, ACCOUNT, DESCR, CLASS_FLD, STATISTIC_CODE, TREE_NODE_NUM, and TREE_NODE.
It is scheduled to run at 2am and it generally finishes by 3:30 am depends on the activities.
Note: If you are having difficulty seeing the entire table below, or the entirety of an expanded text box, collapse the side page tree and/or decrease your view by selecting Ctrl -
| Key | Record.Fieldname | Format | XLAT | Heading Text | Source Table(s) | Notes |
|---|---|---|---|---|---|---|
| → | ACCOUNT - Account | Char10 | Account | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| → | ACCOUNTING_PERIOD - Accounting Period | Num3.0 | Period | LEDGER, LEDGER_BUDG, LEDGER_KK, CAL_DETP_TBL | ||
| AMT1 - Amount 1 | SNm25.3 | Amount 1 | NOT IN USE. Defaulted to 0 | |||
| AMT2 - Amount 2 | SNm25.3 | Amount 2 | NOT IN USE. Defaulted to 0 | |||
| AMT3 - Amount 3 | SNm25.3 | Amount 3 | NOT IN USE. Defaulted to 0 | |||
| AMT4 - Amount 4 | SNm25.3 | Amount 4 | NOT IN USE. Defaulted to 0 | |||
| → | BUSINESS_UNIT - Business Unit | Char5 | Unit | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| → | CLASS_FLD - Class Field | Char5 | Class | LEDGER, LEDGER_BUDG, LEDGER_KK | Please see the definition. | |
| → | DEPTID - Department | Char10 | Dept | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| DESCR - Description | Char30 | Descr | GL_ACCOUNT_TBL | |||
| → | FISCAL_YEAR - Fiscal Year | Num4.0 | Year | LEDGER, LEDGER_BUDG, LEDGER_KK, CAL_DETP_TBL | ||
| → | FUND_CODE - Fund Code | Char5 | Fund | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| → | OPERATING_UNIT - Operating Unit | Char8 | Oper Unit | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| → | PROGRAM_CODE - Program Code | Char5 | Program | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| → | PROJECT_ID - Project | Char15 | Project | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| PROJECT_STATUS - Project Status | Char1 | N | Status | PROJECT_STATUS | ||
| ROOT_NODE_NUM - Root Node Number | Num10.0 | Root Node | PSTREELEAF | |||
| → | STATISTICS_CODE - Statistics Code | Char3 | Stat | LEDGER, LEDGER_BUDG, LEDGER_KK | ||
| TREE_NODE - Tree Node | Char20 | Node | (TREE_NAME = 'ACCT_GL_INQ') | |||
| UM_ACCT_BAL_CLS - Account Balance Sheet Class | Char20 | Bal Class | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | Not used much. SQL-1 | ||
| UM_ACCT_BAL_CLS_D - Account Balance Sheet Class | Char60 | Bal Class | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | Not used much. SQL-1 | ||
| UM_ACCT_CASH_FLW - Account Cash Flow Class | Char20 | Cash Flow | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_ACCT_CASH_FLW_D - Account Cash Flow Class | Char60 | Cash Flow | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_ACCT_DESCR - Account Description | Char30 | Acct Desc | GL_ACCOUNT_TBL | |||
| UM_ACCT_DESCRSHRT - Account Short Description | Char10 | Acct Shrt Desc | GL_ACCOUNT_TBL | |||
| UM_ACCT_FBAL_CLS - Account Fund Balance Class | Char20 | Fund Bal | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | NOT IN USE. SQL-1 | ||
| UM_ACCT_FBAL_CLS_D - Account Fund Balance Class | Char60 | Fund Bal | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | NOT IN USE. SQL-1 | ||
| UM_ACCT_REV2 - 2-Digit Account Revenue Code | Char20 | 2-Digit Rev | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | NOT IN USE. SQL-1 | ||
| UM_ACCT_REV2_D - 2-Digit Account Revenue Code | Char60 | 2-Digit Rev | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | NOT IN USE. SQL-1 | ||
| UM_ACCT_REV4 - 4-Digit Account Revenue Code | Char20 | 4-Digit Rev | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | NOT IN USE. SQL-1 | ||
| UM_ACCT_REV4_D - 4-Digit Account Revenue Code | Char60 | 4-Digit Rev | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | NOT IN USE. SQL-1 | ||
| UM_ACCT_STATUS - Account Status | Char1 | Acct St | GL_ACCOUNT_TBL | |||
| UM_ACCT_YREND - Account Year End Schedule | Char20 | Acct YrEnd | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_ACCT_YREND_D - Account Year End Schedule | Char60 | Acct YrEnd | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_ACTUALS_CURR - Current Month | SNm25.2 | Current Month | SUM(POSTED_TOTAL_AMT) from LEDGER table | |||
| UM_ACTUALS_YTD - Actuals Year to Date | SNm25.2 | Year to Date | ||||
| UM_ACTUALS_YTD_LY - Actuals YTD Last Year | SNm25.2 | Actuals YTD LY | ||||
| UM_ACTUALS_YTD_PY - Actuals YTD Previous Year | SNm25.2 | Actuals YTD PY | ||||
| UM_ACTUAL_ACTIVITY - Actuals Activity | Char1 | Actuals Activit | ||||
| UM_BASE_BUDGET - Base Budget | SNm25.2 | Base Budget | SUM(POSTED_TOTAL_AMT) from LEDGER_BUDG table when LEDGER= 'BASEBUD' adding UM_BASE_BUDGET from previous period | |||
| UM_BBUDG_ACTIVITY - Base Budget Current Activity | Char1 | Current Activit | LEDGER_BUDG | |||
| UM_BU_DESCR - Business Unit | Char30 | Bus Unit | BUS_UNIT_TBL_FS | populated only for accounting periods 1-12. | ||
| UM_BU_DESCRSHORT - Business Unit | Char10 | Bus Unit | BUS_UNIT_TBL_FS | |||
| UM_CBUDG_ACTIVITY - Current Budget Activity | Char1 | Current Activit | LEDGER_BUDG | |||
| UM_CLASS_D - Class Desc | Char30 | Class Desc | UM_OBJ_XLAT | This is not a description of CLASS_FLD. | ||
| UM_CURR_BUDGET - Current Budget | SNm25.2 | Current Budget | SUM(POSTED_TOTAL_AMT) from LEDGER_BUDG table when LEDGER= 'BUDGET' adding UM_CURR_BUDGET from previous period | The amounts outside of account 40000-89999 may not match with GL Inquiry. | ||
| UM_CURR_BUDGET_LY - Current Budget Last Year | SNm25.2 | CurrBud LY | LEDGER_BUDG | |||
| UM_CURR_BUDGET_PY - Current Budget Previous Yr | SNm25.2 | CurrBud PY | LEDGER_BUDG | |||
| UM_DEPT_CR_1 - Campus Responsibility Code 1 | Char20 | CR_1 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 3 for UMS_DEPT_ORG tree node of the deptid (Tree: UMS_DEPT_ORG) | SQL-2 | ||
| UM_DEPT_CR_1_D - Campus Responsibility Code 1 | Char30 | CR_1 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 3 for UMS_DEPT_ORG tree node of the deptid (Tree: UMS_DEPT_ORG) | SQL-2 | ||
| UM_DEPT_CR_2 - Campus Responsibility Code 2 | Char20 | CR_2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 4 for UMS_DEPT_ORG tree node of the deptid | SQL-2 | ||
| UM_DEPT_CR_2_D - Campus Responsibility Code 2 | Char30 | CR_2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 4 for UMS_DEPT_ORG tree node of the deptid | SQL-2 | ||
| UM_DEPT_CR_3 - Campus Responsibility Code 3 | Char20 | CR_3 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 5 for UMS_DEPT_ORG tree node of the deptid | SQL-2 | ||
| UM_DEPT_CR_3_D - Campus Responsibility Code 3 | Char30 | CR_3 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 5 for UMS_DEPT_ORG tree node of the deptid | SQL-2 | ||
| UM_DEPT_CR_4 - Campus Responsibility Code 4 | Char20 | CR_4 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 6 for UMS_DEPT_ORG tree node of the deptid | SQL-2 | ||
| UM_DEPT_CR_4_D - Campus Responsibility Code 4 | Char30 | CR_4 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL The field comes from level 6 for UMS_DEPT_ORG tree node of the deptid | SQL-2 | ||
| UM_DEPT_DESCR - Department Description | Char30 | Dept Desc | DEPT_TBL | populated only for accounting periods 1-12. | ||
| UM_DEPT_DESCR_SHRT - Department Short Description | Char10 | Dept Short Desc | DEPT_TBL | |||
| UM_DEPT_EXP2 - 2-Digit Department Expense Cd | Char20 | Exp 2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL When tree_name = 'UMS_DEPT_F' and tree_level_num ='2', then tree_node | SQL-2 | ||
| UM_DEPT_EXP2_D - 2-Digit Department Expense Cd | Char30 | Exp 2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL When tree_name = 'UMS_DEPT_F' and tree_level_num ='2', then descr | SQL-2 | ||
| UM_DEPT_EXP4 - 4-Digit Department Expense Cd | Char20 | Exp 4 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL When tree_name = 'UMS_DEPT_F' and tree_level_num ='3', then tree_node | SQL-2 | ||
| UM_DEPT_EXP4_D - 4-Digit Department Expense Cd | Char30 | Exp 4 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL When tree_name = 'UMS_DEPT_F' and tree_level_num ='3', then descr | SQL-2 | ||
| UM_DEPT_HR - Human resources department | Char20 | HR Dept | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_DEPT_HR_D - HR deptartment description | Char60 | HR dept descr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_DEPT_MGR - Department Manager | Char30 | Dept Mgr | DEPT_TBL | |||
| UM_DEPT_STATUS - Department Status | Char1 | Dept St | DEPT_TBL | |||
| UM_DEPT_YREND - Department Year End | Char20 | Dept Yr End | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_DEPT_YREND_D - Department YrEnd | Char60 | Dept YrEnd | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_ENCUM_ACTIVITY - Encumbrance Current Activity | Char1 | Current Activit | LEDGER_KK | |||
| UM_FUND_DESCR - Fund Description | Char30 | Fund Descr | FUND_TBL | populated only for accounting periods 1-12. | ||
| UM_FUND_DESCRSHRT - Fund Short Description | Char10 | Fund Shrt Desc | FUND_TBL | |||
| UM_FUND_STATUS - Fund Status | Char1 | Fund Status | FUND_TBL | |||
| UM_FUTURE_BUDGET - Future Budget | SNm25.2 | Future Budget | LEDGER_BUDG | |||
| UM_FUTURE_BUDGET_1 - Future Budget Scenerio 1 | SNm25.2 | Future Budget 1 | LEDGER_BUDG | |||
| UM_FUTURE_BUDGET_2 - Future Budget Scenerio 2 | SNm25.2 | Future Budget 2 | LEDGER_BUDG | |||
| UM_OPUN_DESCR - Operating Unit Description | Char30 | Oper Unit Desc | OPER_UNIT_TBL | populated only for accounting periods 1-12. | ||
| UM_OPUN_DESCRSHRT - Operating Unit Short Descr | Char10 | OpUn Descrshrt | OPER_UNIT_TBL | |||
| UM_OPUN_STATUS - Operating Unit Status | Char1 | Oper Unit St | OPER_UNIT_TBL | |||
| UM_PRE_ENCUMBRANCE - Pre-Encumbrance | SNm25.2 | Pre-Encumbrance | SUM(POSTED_TOTAL_AMT) from LEDGER_KK table adding UM_PRE_ENCUMBRANCE from previous period | |||
| UM_PROG_CAT1 - Program Category 1 | Char20 | Prog Cat 1 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_CAT1_D - Program Category 1 | Char30 | Prog Cat 1 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_CAT2 - Program Category 2 | Char20 | Prog Cat 2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_CAT2_D - Program Category 2 | Char30 | Prog Cat 2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_CAT3 - Program Category 3 | Char20 | Prog Cat 3 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_CAT3_D - Program Category 3 | Char30 | Prog Cat 3 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_CAT4 - Program Category 4 | Char20 | Prog Cat 4 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_CAT4_D - Program Category 4 | Char30 | Prog Cat 4 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_DESCR - Program Description | Char30 | Prog Descr | PROGRAM_TBL | |||
| UM_PROG_DESCR_SHRT - Program Short Description | Char30 | Prog Shrt Descr | PROGRAM_TBL | |||
| UM_PROG_ENDOW_DIST - Program Endow Dist | Char20 | Prog Endow Dist | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROG_EXPENSE - Program Expenditure Category | Char20 | Prog Exp Cat | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROG_EXPENSE_D - Program Expenditure Category | Char60 | Prog Exp Cat | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROG_MGR_NAME - Program Manager Name | Char30 | Prog Mgr | PROGRAM_TBL | |||
| UM_PROG_RESTR_1 - Program Restriction 1 | Char20 | Prog Restr 1 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_RESTR_1_D - Program Restriction 1 | Char30 | Prog Restr 1 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_RESTR_2 - Program Restriction 2 | Char20 | Prog Restr 2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_RESTR_2_D - Program Restriction 2 | Char30 | Prog Restr 2 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_RESTR_3 - Program Restriction 3 | Char20 | Prog Restr 3 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_RESTR_3_D - Program Restriction 3 | Char30 | Prog Restr 3 | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROGRAM) | SQL-2 | ||
| UM_PROG_STATUS - Program Status | Char1 | Prog St | PROGRAM_TBL | |||
| UM_PROG_TI_INC_DST - Program TI INC Dist | Char20 | Proj TI INC Dst | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROG_YREND - Program YrEnd | Char20 | Prog YrEnd | CF_ATTRIB_TBL, CF_ATTRIB_VALUE when field name = 'PROGRAM_CODE' and CF_attribute = 'YR_END_SCHEDULE' then CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROG_YREND_D - Program YrEnd | Char30 | Prog YrEnd | CF_ATTRIB_TBL, CF_ATTRIB_VALUE when field name = 'PROGRAM_CODE' and CF_attribute = 'YR_END_SCHEDULE' then DESCR60 | SQL-1 | ||
| UM_PROJECT_TO_DT - Project to Date | SNm25.2 | Project to Date | LEDGER | |||
| UM_PROJ_BILL_CD - Project Billing Code | Char20 | Proj Bill Cd | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_BILL_CD_D - Project Billing Code | Char60 | Proj Bill Cd | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_BLD_LIFE - Project BLD Life | Char20 | Project BLD Lif | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_CAP_TYPE - Project Capital Type | Char20 | Proj Cap Type | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_CAP_TYPE_D - Project Capital Type | Char30 | Proj Cap Type | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_CATEGORY - Project Category | Char20 | Proj Category | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_CATEGORY_D - Project Category | Char30 | Proj Category | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_CFDA_N - Project CFDA Number | Char20 | Proj CFDA Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_CFDA_N_D - Project CFDA Number | Char60 | Proj CFDA Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_CLOSNCOMBO - Project Closing Combination | Char20 | Proj ClsCombo | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_CLSCOMBO_D - Project Closing Combination | Char30 | Proj ClsCombo D | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_DEPR_ST - Project Depreciation Status | Char20 | Proj Depr St | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_DEPR_ST_D - Project Depreciation Status | Char60 | Proj Depr St | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_DESCR - Project Description | Char30 | Proj Descr | PROJECT | populated only for accounting periods 1-12. | ||
| UM_PROJ_EFF_STATUS - Project Effective Status | Char1 | Proj Eff St | PROJECT | |||
| UM_PROJ_END_DT - Project End Date | Date | Proj End Dt | UM_PROJECT_STAT | |||
| UM_PROJ_EXPENSE - Project Expenditure Category | Char20 | Proj Exp Cat | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_EXPENSE_D - Project Expenditure Category | Char60 | Proj Exp Cat | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_FACIL_ID - Project Facilty Number | Char20 | Proj Facil Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_FACIL_ID_D - Project Facility Number | Char60 | Proj Facil Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_FED_AGCY - Project Federal Agency | Char20 | Proj Fed Agncy | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_FED_AGCY_D - Project Federal Agency | Char60 | Proj Fed Agncy | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_FIN_CONT - Project Fin Contact | Char20 | Project Fin Con | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_FIN_CONT_D - Project Fin Contact | Char60 | Project Fin Con | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_GC_RPT - Project GC Report | Char20 | Project GC Repo | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_GC_RPT_D - Project GC Report | Char60 | Project GC Rpt | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_GC_TYPE - Project Grant/Contract Type | Char20 | Proj GC Type | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_GC_TYPE_D - Project Grant/ContractType | Char60 | Proj GC Type | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_GRANT_N - Project Grant Number | Char20 | Proj Grant Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_GRANT_N_D - Project Grant Number | Char60 | Proj Grant Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_GROUP - Project Group | Char20 | Proj Group | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_GROUP_D - Project Group | Char30 | Proj Group | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_IMPVT_LIFE - Project IMPVT Life | Char20 | Proj IMPVT Life | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_IN_SRVC - Project In-Service Date | Date | Proj In-Srvc Dt | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_LOC_EXPIRE - Project Loc Expire | Char20 | Project Loc Exp | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_LOC_NUM - Project Letter of Credit Nbr | Char20 | Proj LOC Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_LOC_NUM_D - Project Letter of Credit Nbr | Char60 | Proj LOC Nbr | CF_ATTRIB_TBL, CF_ATTRIB_VALUE | SQL-1 | ||
| UM_PROJ_LOC_SRCE - Project Letter of Cred Source | Char20 | Proj LOC Source | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_LOC_SRCE_D - Project Letter of Cred Source | Char30 | Proj LOC Source | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_MAJSRC - Project Major Funding Source | Char20 | Proj Maj Source | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_MAJSRC_D - Project Major Funding Source | Char30 | Proj Maj Source | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_MGR_EFFDT - Project Manager Effdt | Date | Proj Mgr Eff | PROJECT_MGR | |||
| UM_PROJ_MGR_ID - Project Manager Id | Char11 | Proj Mgr Id | PROJECT_MGR | |||
| UM_PROJ_MGR_NAME - Project Manager | Char30 | Proj Mgr | PERSONAL_DATA | |||
| UM_PROJ_MINSRC - Project Minor Funding Source | Char20 | Proj Min Source | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_MINSRC_D - Project Minor Funding Source | Char30 | Proj Min Source | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_START_DT - Project Start Date | Date | Proj Start dt | UM_PROJECT_STAT | |||
| UM_PROJ_STAT_EFFDT - Project status effdt | Date | Proj Status Eff | PROJECT_STATUS | |||
| UM_PROJ_SUBGROUP - Project Subgroup | Char20 | Proj Subgroup | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_PROJ_SUBGROUP_D - Project Subgroup | Char30 | Proj Subgroup | PSTREELEAF, PSTREENODE, TREE_NODE_TBL (Tree: UMS_PROJECT) | SQL-2 | ||
| UM_TOT_ENCUMBRANCE - Total Encumbrance | SNm25.2 | Encumbrance | SUM(POSTED_TOTAL_AMT) from LEDGER_KK table when SUM(POSTED_TOTAL_AMT) <> 0 adding UM_TOT_ENCUMBRANCE from previous period |
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.
The SQR code is located in S:\FIN\SQR\Prod\umglrex.sqr.
The below are a part of the SQR code.
SQL-1 :Chartfield attribute Expand source
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 IN ('DEPTID','PROJECT_ID','PROGRAM_CODE','ACCOUNT')
AND RG.CF_ATTRIBUTE IN ('YR_END_SCHEDULE','ASSET_NUMBER','BILLING_CODES','CFDA_NUMBER','FEDERAL_AGENCY'
,'GRANT_NUMBER','GC_TYPE','BAL_SHEET_CLASS','CASH_FLOW_CLASS','FUND_BAL_CLASS'
,'REVENUE_CAT','REVENUE_CODE','FACILITY_ID','DEPR_STATUS','FEDERAL_LOC_NO'
,'IN_SERVICE','EXPENSE_CODE','PROG_YREND','PROG_EXPENSE'
,'ENDOW_DIST','TI_INCOME_DIST','BLD_LIFE','FIN_CONTACT','GC_REPORT','IMPVT_LIFE','LOC_EXPIRE'
,'CLOSINGCOMBO','HR_DEPT')
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
SQL-2 :Tree Attribute Expand source
select rp.tree_name,
rq.tree_node,
rq.tree_level_num,
rr.descr,
rp.range_from,
rp.range_to
from sysadm.pstreeleaf rp,
sysadm.pstreenode rq,
sysadm.ps_tree_node_tbl rr
where rp.tree_name in
('UMS_DEPT_ORG', 'UMS_DEPT_F', 'UMS_PROJECT', 'UMS_PROGRAM')
and (rq.tree_level_num >= 2 and rq.tree_level_num <= 9)
and rp.setid = 'UMSYS'
and rp.effdt = (select max(aef.effdt)
from sysadm.pstreeleaf aef
where aef.setid = rp.setid
and aef.setcntrlvalue = rp.setcntrlvalue
and aef.tree_name = rp.tree_name
and aef.effdt <= sysdate)
and rq.tree_name = rp.tree_name
and rq.setid = rp.setid
and rq.setcntrlvalue = rp.setcntrlvalue
and rp.tree_node_num between rq.tree_node_num and rq.tree_node_num_end
and rq.effdt = rp.effdt
and rr.setid = rq.setid
and rr.tree_node = rq.tree_node
and rr.effdt = (select max(rref.effdt)
from sysadm.ps_tree_node_tbl rref
where rref.setid = rr.setid
and rref.tree_node = rr.tree_node
and rref.effdt < sysdate)
and rr.eff_status = 'A'
