| GLQ_VW | GLQ_REPRT | Created Field Name | Description | Query Usage Example | Type | Aggregate? | Code |
|---|
USE UM_ACCT_SUBCAT or UM_ACCT_SUBCAT_D field on UM_GLQ_ACCTA table
| USE UM_ACCT_SUBCAT or UM_ACCT_SUBCAT_D field on UM_GLQ_ACCTA table | SUBCATEGORY → USE UM_GLQ_ACCTA table
| Detail for ACCOUNT grouping not supported by UM_ACCT_SUBCAT - Account Subcategory | UMS_DTS_ADDITIONAL_SUBCATEGORY | Character, 100 | No |
/*+SUBCATEGORY*/
CASE WHEN ( CASE WHEN B.ACCOUNT = '51100' THEN 'UMS'
WHEN B.ACCOUNT = '54800' THEN 'UMS'
WHEN B.ACCOUNT = '70000' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61400' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61401' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61404' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'C1' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E2' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E4' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C3' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C6' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62504' AND B.CLASS_FLD = 'C1' THEN 'Other Vendors'
WHEN B.ACCOUNT = '67004' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E3' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' AND B.CLASS_FLD = 'C1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' AND B.CLASS_FLD = 'TV' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60103' THEN 'UMS'
WHEN B.ACCOUNT = '60302' THEN 'UMS'
WHEN B.ACCOUNT = '60109' AND B.CLASS_FLD = 'E1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C2' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C4' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C5' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C7' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C8' THEN 'AssetWorks'
WHEN B.ACCOUNT = '64750' AND B.CLASS_FLD = 'E1' THEN 'AssetWorks'
WHEN B.ACCOUNT < '50000' THEN 'Revenue' WHEN B.ACCOUNT >= '80000' THEN 'Revenue' ELSE ' ' END ) = 'AssetWorks'
AND B.ACCOUNT = '64750'
AND B.CLASS_FLD = 'E1' THEN 'Maintenance & Licensing'
WHEN ( CASE WHEN B.ACCOUNT = '51100' THEN 'UMS' WHEN B.ACCOUNT = '54800' THEN 'UMS'
WHEN B.ACCOUNT = '70000' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61400' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61401' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61404' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'C1' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E2' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E4' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C3' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C6' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62504' AND B.CLASS_FLD = 'C1' THEN 'Other Vendors'
WHEN B.ACCOUNT = '67004' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E3' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' AND B.CLASS_FLD = 'C1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' AND B.CLASS_FLD = 'TV' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60103' THEN 'UMS'
WHEN B.ACCOUNT = '60302' THEN 'UMS'
WHEN B.ACCOUNT = '60109' AND B.CLASS_FLD = 'E1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C2' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C4' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C5' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C7' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C8' THEN 'AssetWorks'
WHEN B.ACCOUNT = '64750' AND B.CLASS_FLD = 'E1' THEN 'AssetWorks'
WHEN B.ACCOUNT < '50000' THEN 'Revenue' WHEN B.ACCOUNT >= '80000' THEN 'Revenue' ELSE ' ' END ) = 'AssetWorks'
AND B.ACCOUNT = '62516'
AND B.CLASS_FLD = 'C4' THEN 'Maintenance & Licensing'
WHEN ( CASE WHEN B.ACCOUNT = '51100' THEN 'UMS'
WHEN B.ACCOUNT = '54800' THEN 'UMS'
WHEN B.ACCOUNT = '70000' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61400' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61401' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '61404' AND B.CLASS_FLD = 'E1' THEN 'UMS'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'C1' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E2' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E4' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C3' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C6' THEN 'Other Vendors'
WHEN B.ACCOUNT = '62504' AND B.CLASS_FLD = 'C1' THEN 'Other Vendors'
WHEN B.ACCOUNT = '67004' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60002' AND B.CLASS_FLD = 'E3' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' AND B.CLASS_FLD = 'C1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' AND B.CLASS_FLD = 'TV' THEN 'AssetWorks'
WHEN B.ACCOUNT = '60100' THEN 'Other Vendors'
WHEN B.ACCOUNT = '60103' THEN 'UMS'
WHEN B.ACCOUNT = '60302' THEN 'UMS'
WHEN B.ACCOUNT = '60109' AND B.CLASS_FLD = 'E1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C1' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C2' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C4' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C5' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C7' THEN 'AssetWorks'
WHEN B.ACCOUNT = '62516' AND B.CLASS_FLD = 'C8' THEN 'AssetWorks'
WHEN B.ACCOUNT = '64750' AND B.CLASS_FLD = 'E1' THEN 'AssetWorks'
WHEN B.ACCOUNT < '50000' THEN 'Revenue'
WHEN B.ACCOUNT >= '80000' THEN 'Revenue' ELSE ' ' END ) = 'AssetWorks'
THEN 'Other Asset Works' ELSE ' ' END
|
| USE UM_ACCT_GRP or UM_ACCT_GRP_D field on UM_GLQ_ACCTA table | USE UM_ACCT_GRP or UM_ACCT_GRP_D field on UM_GLQ_ACCTA table | ACCT_GROUP (REV_EXP sometimes) → USE UM_GLQ_ACCTA table | Categorize account type for Expense or Revenue? |
 DM-128
-
ABCDE
BI Tool
DM-129
-
AM061 - Revenue & Expense
BI Tool
| Character, 20 | No |
/*+REV_EXP 11-05-17*/
CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense'
ELSE 'Other' END
|
| UM_BASE_BUDGET_CAL (POSTED_TOTAL_AMT from source Table LEDGER_BUDG ) Accounts 40000-89999(inclusive) |
| BASE_BUDGET
| Total amount for base budget For Accounts 40000-89999 (inclusive) Revenue, Expense and Transfer(IN and OUT) Accounts |
DM-272
-
UO-Account # Query
Closed
DM-274
-
UO- Expense Summary
Closed
DM-129
-
AM061 - Revenue & Expense
BI Tool
| Signed Number, 11,2 | Yes |
/*+BASE_BUDGET 12-12-17*/
SUM((CASE WHEN ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) THEN A.UM_BASE_BUDGET ELSE 0 END))
Some reports don't restrict the range of account. (DM-129) -this can be set up to SUM in MaineStreet as well /*+BASE_BUDGET 11-03-17*/
SUM(A.UM_BASE_BUDGET) |
|
| BUDGET_BALANCE
- Tamara has processed for view
| Calculates total amount for budget balance |
DM-272
-
UO-Account # Query
Closed
DM-274
-
UO- Expense Summary
Closed
DM-129
-
AM061 - Revenue & Expense
BI Tool
| Signed Number, 11,2 | Yes |
/*+BUDGET_BALANCE 11-03-17*/
SUM(
((CASE WHEN ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) THEN A.UM_CURR_BUDGET ELSE 0 END)
-(CASE WHEN ( A.PROJECT_ID = ' ' AND ( A.ACCOUNT >= '30000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.PROJECT_ID <> ' ' AND ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' ) THEN A.UM_PROJECT_TO_DT ELSE 0 END)
-A.UM_TOT_ENCUMBRANCE))
|
|
| DEFICIT_BALANCE
- Tamara has processed for view
| Calculates deficits or balance |
DM-81
-
YE- Deficits - MEIF - Liz
Parked
| Signed Number, 10,2 | Yes |
/*+DEFICIT_BALANCE 10-03-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('3') then A.UM_PROJECT_TO_DT else 0 end)
+ sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_ACTUALS_YTD else 0 end)
+ sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_ACTUALS_YTD else 0 end)
DEFICIT_BALANCE returns different values than BUDGET_BALANCE |
|
| PROJECT_(BALANCE)_DEFICIT
- Tamara has processed for view
Tamara J Saarinen, Darla asked to add parenthesis to clarify the data
| Balance or deficits for project |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
DM-75
-
YE - Deficits - Cost Sharing - Liz
Parked
| Signed Number, 10,2 | Yes |
/*+PROJECT_BAL_DEFICIT 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_PROJECT_TO_DT else 0 end) +
sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_PROJECT_TO_DT else 0 end)
|
|
| REV_(OVER)_UNDER_BUDGET
- Tamara has processed for view
Tamara J Saarinen, Darla asked to add parenthesis to clarify the data
| Calculates revenue balance (Over or under budget) |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
DM-75
-
YE - Deficits - Cost Sharing - Liz
Parked
| Signed Number, 10,2 | Yes |
/*+REV_OVER_UNDER_BUDGET 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_PROJECT_TO_DT else 0 end) -
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_CURR_BUDGET else 0 end)
|
|
| EXP_OVER_(UNDER)_BUDGET
- Tamara has processed for view
Tamara J Saarinen, Darla asked to add parenthesis to clarify the data
| Calculates expense balance (Over or under budget) |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
DM-75
-
YE - Deficits - Cost Sharing - Liz
Parked
| Signed Number, 10,2 | Yes |
/*+EXP_OVER_UNDER_BUDGET 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_PROJECT_TO_DT else 0 end)
-sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_CURR_BUDGET else 0 end)
Tamara J Saarinen, I don't like to start with "-" (negative sign) on the formula, so I switched. |
|
| CURRENT_BUDGET - Tamara has processed for view
| Total amount for current budget |
DM-272
-
UO-Account # Query
Closed
DM-274
-
UO- Expense Summary
Closed
DM-129
-
AM061 - Revenue & Expense
BI Tool
| Signed Number, 11,2 | Yes |
/*+CURRENT_BUDGET 12-12-17*/
SUM((CASE WHEN ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) THEN A.UM_CURR_BUDGET ELSE 0 END))
Some reports don't restrict the range of account. (DM-129) /*+CURRENT_BUDGET 11-03-17*/
SUM(A.UM_CURR_BUDGET) |
|
| CURR_BUDGET - Tamara has processed for view
| Total amount for current budget with prompts for Fiscal Year and Accounting Period |
DM-273
-
UO- Budget Worksheet FY17
Closed
| Signed Number, 11, 2 | Yes |
/*+CURR_BUDGET 12-12-17*/
SUM(CASE WHEN A.FISCAL_YEAR = :1 AND A.ACCOUNTING_PERIOD = :2 THEN
(CASE WHEN ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) THEN A.UM_CURR_BUDGET ELSE 0 END) ELSE 0 END)
|
|
| NET_BUDGET
- Tamara has processed for view
| Total amount for net budget |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
| Signed Number, 10,2 | Yes |
/*+NET_BUDGET 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_CURR_BUDGET else 0 end) +
sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_CURR_BUDGET else 0 end)
|
|
| NET_TRANSFER
- Tamara has processed for view
| Total amount for net transfer |
DM-289
-
USM Advancement Report 6 Gift Income and Campus Programs Balances
Closed
| Signed Number, 11,2 | Yes |
/*+NET_TRANSFER 1-30-18*/
SUM(CASE WHEN A.ACCOUNT >= '70000' AND A.ACCOUNT <= '89999' THEN (CASE WHEN ( A.PROJECT_ID = ' ' AND ( A.ACCOUNT >= '30000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.PROJECT_ID <> ' ' AND ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' ) THEN A.UM_PROJECT_TO_DT ELSE 0 END) ELSE 0 END)
|
|
| ENCUMBRANCE - Tamara has processed for view
| Total amount for encumbrance |
DM-272
-
UO-Account # Query
Closed
DM-274
-
UO- Expense Summary
Closed
DM-129
-
AM061 - Revenue & Expense
BI Tool
| Signed Number, 11,2 | Yes |
/*+ENCUMBRANCE*/
SUM(A.UM_TOT_ENCUMBRANCE)
|
|
| ENCUMB_NET_EXPENSE
- Tamara has processed for view
| Total amount for encumbrance net expense |
DM-289
-
USM Advancement Report 6 Gift Income and Campus Programs Balances
Closed
| Signed Number, 11,2 | Yes |
/*+ENCUMB_NET_EXPENSE 1-30-18*/
SUM(CASE WHEN A.ACCOUNT >= '50000' AND A.ACCOUNT <= '69999' THEN (A.UM_TOT_ENCUMBRANCE) ELSE 0 END)
|
|
| PTD
- Tamara has processed for view
| PTD - Project To Date |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
| Signed Number, 10,2 | Yes |
/*+PTD_REV_BUDGET 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_CURR_BUDGET else 0 end)
|
|
| PTD_EXP_BUDGET PTD_EXPENSE_BUDGET
- Tamara has processed for view
| Total amount for expense budget PTD - Project To Date |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
| Signed Number, 10,2 | Yes |
/*+PTD_EXP_BUDGET 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_CURR_BUDGET else 0 end)
/*+PTD_EXPENSE_BUDGET 12-29-17*/
SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Expense' THEN A.UM_CURR_BUDGET ELSE 0 END)
|
|
| PROGRAM_CODE_GROUP
- Tamara has processed for view
| Categorize PROGRAM_CODE |
DM-93
-
MEIF-All Unused Funds BOY
Closed
| Character, 20 | No |
/*+GROUP*/
CASE
WHEN SUBSTR(A.PROGRAM_CODE, 2,4) = '9110' THEN '1 Adv Tech'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '1' THEN '1 Adv Tech'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '2' THEN '2 Aquaculture'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '3' THEN '3 BioTech'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '4' THEN '4 Comp'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '5' THEN '5 Environ'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '6' THEN '6 IT'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '7' THEN '7 Pre Man'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '8' THEN '8'
WHEN SUBSTR(A.PROGRAM_CODE, 4,1) = '9' THEN '9 Cross Sec'
ELSE '99' END
|
|
| REG_TEMP - Tamara has processed for view
| This derives Regular or Temporary Fringe Cost based on Account and Fund Code for budget costing estimation also used in HRPRD - UM_F_POS_BUD_VW Table |
DM-27
-
PM Budgets vs Annual Distributions
Closed
| Character, 10 | No |
/*+ REG_TEMP by ACCOUNT 11_30_17 */
CASE
WHEN A.ACCOUNT = '16001' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '16001' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '16004' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '16004' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '20155' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'TMP'
WHEN A.ACCOUNT = '20155' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'TMP'
WHEN A.ACCOUNT = '21600' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '21600' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50000' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50000' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50001' THEN 'TMP'
WHEN A.ACCOUNT = '50002' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50002' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50003' THEN 'TMP'
WHEN A.ACCOUNT = '50004' THEN 'TMP'
WHEN A.ACCOUNT = '50005' THEN 'TMP'
WHEN A.ACCOUNT = '50006' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50006' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50007' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50007' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50008' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50008' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50009' THEN 'TMP'
WHEN A.ACCOUNT = '50010' THEN 'TMP'
WHEN A.ACCOUNT = '50011' THEN 'TMP'
WHEN A.ACCOUNT = '50012' THEN 'TMP'
WHEN A.ACCOUNT = '50013' THEN 'TMP'
WHEN A.ACCOUNT = '50014' THEN 'TMP'
WHEN A.ACCOUNT = '50015' THEN 'TMP'
WHEN A.ACCOUNT = '50016' THEN 'TMP'
WHEN A.ACCOUNT = '50017' THEN 'TMP'
WHEN A.ACCOUNT = '50018' THEN 'TMP'
WHEN A.ACCOUNT = '50019' THEN 'TMP'
WHEN A.ACCOUNT = '50030' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50030' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50100' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50100' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50101' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50101' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50200' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50200' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50300' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '50300' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '50400' THEN 'TMP'
WHEN A.ACCOUNT = '50401' THEN 'TMP'
WHEN A.ACCOUNT = '50402' THEN 'TMP'
WHEN A.ACCOUNT = '50403' THEN 'TMP'
WHEN A.ACCOUNT = '50404' THEN 'TMP'
WHEN A.ACCOUNT = '51000' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '51000' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '51001' THEN 'TMP'
WHEN A.ACCOUNT = '51002' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '51002' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '51005' THEN 'TMP'
WHEN A.ACCOUNT = '51007' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '51007' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '51008' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '51008' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '51009' THEN 'TMP'
WHEN A.ACCOUNT = '51011' THEN 'TMP'
WHEN A.ACCOUNT = '51012' THEN 'TMP'
WHEN A.ACCOUNT = '51100' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '51100' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '51101' THEN 'TMP'
WHEN A.ACCOUNT = '51200' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '51200' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '51201' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '51201' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '51202' THEN 'TMP'
WHEN A.ACCOUNT = '51203' THEN 'TMP'
WHEN A.ACCOUNT = '52000' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '52000' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '52001' THEN 'TMP'
WHEN A.ACCOUNT = '52002' THEN 'TMP'
WHEN A.ACCOUNT = '52003' THEN 'TMP'
WHEN A.ACCOUNT = '52009' THEN 'TMP'
WHEN A.ACCOUNT = '52011' THEN 'TMP'
WHEN A.ACCOUNT = '52012' THEN 'TMP'
WHEN A.ACCOUNT = '52014' THEN 'TMP'
WHEN A.ACCOUNT = '52100' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '52100' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '52200' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '52200' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '52201' THEN 'TMP'
WHEN A.ACCOUNT = '52300' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '52300' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '52400' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '52400' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '52500' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '52500' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '53090' THEN 'TMP'
WHEN A.ACCOUNT = '53013' THEN 'TMP'
WHEN A.ACCOUNT = '53300' THEN 'TMP'
WHEN A.ACCOUNT = '53600' THEN 'TMP'
WHEN A.ACCOUNT = '53601' THEN 'TMP'
WHEN A.ACCOUNT = '53900' AND A.FUND_CODE IN('00','03','04','10','13','18','22') THEN 'REG'
WHEN A.ACCOUNT = '53900' AND A.FUND_CODE IN('20','23','24','28','89') THEN 'REG'
WHEN A.ACCOUNT = '53901' THEN 'TMP'
WHEN A.ACCOUNT = '53902' THEN 'TMP'
WHEN A.ACCOUNT = '53903' THEN 'REG'
WHEN A.ACCOUNT = '54100' THEN 'TMP'
WHEN A.ACCOUNT = '54200' THEN 'REG'
WHEN A.ACCOUNT = '54202' THEN 'TMP'
WHEN A.ACCOUNT = '54204' THEN 'TMP'
WHEN A.ACCOUNT = '54218' THEN 'TMP'
WHEN A.ACCOUNT = '54330' THEN 'REG'
END
|
|
| YTD_ACTUALS (ACTUALS_YTD)
- Tamara has processed for view
| Total amount for year-to-date actual |
DM-93
-
MEIF-All Unused Funds BOY
Closed
DM-272
-
UO-Account # Query
Closed
DM-274
-
UO- Expense Summary
Closed
DM-176
-
FM Acct Balances by Dept by FY by Month-Nancy
Closed
| Signed Number, 10, 2 | Yes |
/*+YTD_ACTUALS*/
SUM (CASE
WHEN (A.ACCOUNT between 10000 AND 29999) then A.UM_ACTUALS_YTD
WHEN A.ACCOUNT between 40000 AND 89999 then A.UM_ACTUALS_YTD
WHEN A.ACCOUNT between 30000 AND 39999 then
A.UM_PROJECT_TO_DT
ELSE 0 END)
Some reports don't restrict the range of account. (DM-176) /*+ACTUALS_YTD*/
SUM(A.UM_ACTUALS_YTD)
|
|
| CURR_MO_EXP MTD_EXPENSE
- Tamara has processed for view
| Total amount for expense for current month |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
| Signed Number, 10, 2 | Yes | This one is much simpler than the other one.
/*+CURR_MO_EXP 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_ACTUALS_CURR else 0 end)
This one has logic.
/*+MTD_EXPENSE 12-29-17 DM-223*/
SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Expense' THEN A.UM_ACTUALS_CURR ELSE 0 END)
Both return the same results. |
|
| YTD_EXPENSE
- Tamara has processed for view
| Total amount for year-to-date expense |
DM-81
-
YE- Deficits - MEIF - Liz
Parked
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
| Signed Number, 10, 2 | Yes | This one is much simpler than the other one.
/*+YTD_EXP 10-03-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_ACTUALS_YTD else 0 end)
This one has logic.
/*+YTD_EXPENSE 12-29-17 DM-223*/
SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Expense' THEN A.UM_ACTUALS_YTD ELSE 0 END)
Both return the same results. |
|
| YTD_REVENUE
- Tamara has processed for view
| Total amount for year-to-date revenue |
DM-81
-
YE- Deficits - MEIF - Liz
Parked
| Signed Number, 10, 2 | Yes |
/*+YTD_REVENUE 10-03-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_ACTUALS_YTD else 0 end)
|
|
| BOY_FUND_EQUITY
- Tamara has processed for view
| Total amount for Beginning of Year |
DM-81
-
YE- Deficits - MEIF - Liz
Parked
| Signed Number, 10, 2 | Yes |
/*+BOY_FUND_EQUITY 10-03-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('3') then A.UM_PROJECT_TO_DT else 0 end)
|
|
| PTD_REV_BUDGET PROJECT_REV_BUDGET
- Tamara has processed for view
| Project to Date Revenue Budget |
DM-81
-
YE- Deficits - MEIF - Liz
Parked
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
DM-221
-
FOIA Request - All Construction projects
Parked
| Signed Number, 10, 2 | Yes | This one is much simpler than the other one.
/*+PTD_REV_BUDGET*/
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_CURR_BUDGET else 0 end)
This one has logic.
/*+PTD_REV_BUDGET 12-29-17*/
SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' THEN A.UM_CURR_BUDGET ELSE 0 END)
Both DM-81 and DM-223 return the same results.
DM-221 has "-" in front of the SUM.
/*+PROJECT_REV_BUDGET 1-8-18*/
-SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' THEN A.UM_CURR_BUDGET ELSE 0 END)
|
|
| PTD_REVENUE Why does it have "-" (Negative sign) sometimes??
- Tamara has processed for view
| Project to Date Revenue |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
DM-221
-
FOIA Request - All Construction projects
Parked
| Signed Number, 10, 2 | Yes | This one is much simpler than the other one.
/*+PTD_REVENUE 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('4','8') then A.UM_PROJECT_TO_DT else 0 end)
This one has logic.
/*+PTD_REVENUE 12-29-17*/
SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
Both DM-141 and DM-223 return the same results. DM-221 has "-"(Negative sign) in front of the SUM.
/*+PTD_REVENUE 1-8-18*/
-SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
|
|
| PTD_EXPENSE
- Tamara has processed for view
| Project to Date Expense |
DM-141
-
YE- Deficits - Campus Projects
BI Tool
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
| Signed Number, 10, 2 | Yes | This one is much simpler than the other one.
/*+PTD_EXPENSE 10-19-17*/
sum (case when substr(A.ACCOUNT,1,1) in ('5','6','7') then A.UM_PROJECT_TO_DT else 0 end)
This one has logic.
/*+PTD_EXPENSE 12-29-17*/
SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Expense' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
Both return the same results. |
|
| CAL_BALANCE
- Tamara has processed for view
| Accout Asset Type Calculation |
DM-196
-
Scholarships Master Report - Account Balances
Closed
| Signed Number, 11, 2 | Yes | No ideas how this set up
/*+CAL_BALANCE 10-11-17*/
( ( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' AND A.ACCOUNT = '43695' THEN A.UM_CURR_BUDGET ELSE 0 END) )
+( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' AND (SUBSTR(A.ACCOUNT,1,3)) <> '436' THEN A.UM_CURR_BUDGET ELSE 0 END) )
+( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' AND (SUBSTR(A.ACCOUNT,1,3)) = '436' AND A.ACCOUNT <> '43695' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Net Assets' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT = '44199' THEN A.UM_PROJECT_TO_DT ELSE 0 END) ) )
+( SUM(CASE WHEN A.ACCOUNT = '55100' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
|
|
| ACTUAL_EXP_YTD
- Tamara has processed for view
|
|
DM-196
-
Scholarships Master Report - Account Balances
Closed
| Signed Number, 11, 2 | Yes | Account 55100 = Scholarships
/*+ACTUAL_EXP_YTD 10-11-17*/
SUM(CASE WHEN A.ACCOUNT = '55100' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
|
|
| CURRENT_BUDGET - Tamara has processed for view
| Calculates total amount for current budget Financial Aid data only??
|
DM-196
-
Scholarships Master Report - Account Balances
Closed
| Signed Number, 11, 2 | Yes |
/*+CURRENT_BUDGET 10-11-17*/
( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' AND A.ACCOUNT = '43695'
THEN A.UM_CURR_BUDGET ELSE 0 END) )
+( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' AND (SUBSTR(A.ACCOUNT,1,3)) <> '436'
THEN A.UM_CURR_BUDGET ELSE 0 END) )
+( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' AND (SUBSTR(A.ACCOUNT,1,3)) = '436'
AND A.ACCOUNT <> '43695' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Net Assets' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT = '44199' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
|
|
| CAMPUS - Tamara has processed for view
| Traslate UMS0X to short institution name |
DM-226
-
ALDW - Maintenance Expenditures
Closed
| Character, 5 | No |
/*+CAMPUS 12-29-17*/
(CASE WHEN A.BUSINESS_UNIT = 'UMS01' THEN 'UMA'
WHEN A.BUSINESS_UNIT = 'UMS02' THEN 'UMF'
WHEN A.BUSINESS_UNIT = 'UMS03' THEN 'UMFK'
WHEN A.BUSINESS_UNIT = 'UMS04' THEN 'UMM'
WHEN A.BUSINESS_UNIT = 'UMS05' THEN 'UM'
WHEN A.BUSINESS_UNIT = 'UMS06' THEN 'USM'
WHEN A.BUSINESS_UNIT = 'UMS07' THEN 'UMPI'
WHEN A.BUSINESS_UNIT = 'UMS08' THEN 'SWS'
ELSE 'Other' END)
|
|
| REVENUE_BALANCE
- Tamara has processed for view
| Balance for Revenue ( PTD Revenue - PTD Revenue Budget) ( PTD Revenue - Project Revenue Budget) |
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
DM-221
-
FOIA Request - All Construction projects
Parked
| Signed Number, 11, 2 | Yes |
/*+REVENUE_BALANCE 12-29-17*/
( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
-( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue' THEN A.UM_CURR_BUDGET ELSE 0 END) )
DM-221 has "-" in front of the SUM.
/*+REVENUE_BALANCE 1-8-18*/
( -SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue'
THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
-( -SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense' WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Revenue'
THEN A.UM_CURR_BUDGET ELSE 0 END) )
|
|
| EXPENSE_BALANCE
- Tamara has processed for view
|
|
DM-223
-
Projects - 2.5 million State Appropr - SUM
Client Wait
| Signed Number, 11, 2 | Yes |
/*+EXPENSE_BALANCE 12-29-17*/
( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Expense' THEN A.UM_CURR_BUDGET ELSE 0 END) )
-( SUM(CASE WHEN (CASE WHEN (SUBSTR(A.ACCOUNT,1,1)) = '1' THEN 'Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '2' THEN 'Liabilities'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '3' THEN 'Net Assets'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '4' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '8' THEN 'Revenue'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '5' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '6' THEN 'Expense'
WHEN (SUBSTR(A.ACCOUNT,1,1)) = '7' THEN 'Expense' ELSE 'Other' END) = 'Expense' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
|
|
| ENDOWMENT INCOME
- Tamara has processed for view
|
|
DM-286
-
Endowment & Interest Income by Month_PT.xls
Closed
| Signed Number, 11, 2 | Yes |
/*+ENDOWMENT INCOME 1-30-18*/
SUM(CASE WHEN A.ACCOUNT >= '44000' AND A.ACCOUNT <= '44099' THEN A.UM_ACTUALS_CURR ELSE 0 END)
|
|
| ENDOW_INC_DIST
- Tamara has processed for view
| Endowment income distribution |
DM-289
-
USM Advancement Report 6 Gift Income and Campus Programs Balances
Closed
DM-344
-
Gift Income Account BalancesM
Parked
| Signed Number, 11, 2 | Yes |
/*+ENDOW_INC_DIST 1-30-18*/
SUM(CASE WHEN A.ACCOUNT = '44099' THEN
(CASE WHEN ( A.PROJECT_ID = ' ' AND ( A.ACCOUNT >= '30000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.PROJECT_ID <> ' ' AND ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' ) THEN A.UM_PROJECT_TO_DT ELSE 0 END) ELSE 0 END)
|
|
| YTD ENDOWMENT INCOME
- Tamara has processed for view
|
|
DM-287
-
UMS06 Endowment Shares, Book Value, Market Value, and Spending Balance.xls
Closed
| Signed Number, 11, 2 | Yes |
/*+YTD ENDOWMENT INCOME 1-30-18*/
SUM(CASE WHEN A.ACCOUNT = '44099' AND A.STATISTICS_CODE = ' ' AND
( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes' WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51'
THEN 'Yes' ELSE 'No' END ) = 'No' THEN (CASE WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' ) THEN A.UM_ACTUALS_YTD
WHEN ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) THEN A.UM_ACTUALS_YTD WHEN A.ACCOUNT >= '30000' AND A.ACCOUNT <= '39999'
THEN A.UM_PROJECT_TO_DT ELSE 0 END) ELSE 0 END)
|
|
| ENDOW
- Tamara has processed for view
| ENDOWMENT FUND_CODE 11 = Unrestricted Quasi Endowment
FUND_CODE 21 = Restricted Quasi Endowment FUND_CODE 31 = Restricted Non-Expendable Endowment FUND_CODE 51 = Endowment Funds Held for Others
|
DM-286
-
Endowment & Interest Income by Month_PT.xls
Closed
| Character, 3 | No |
/*+ENDOW 1-30-18*/
CASE
WHEN A.FUND_CODE = '11' THEN 'Yes'
WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes'
WHEN A.FUND_CODE = '51' THEN 'Yes'
ELSE 'No' END
|
|
| INTEREST INCOME
- Tamara has processed for view
|
|
DM-286
-
Endowment & Interest Income by Month_PT.xls
Closed
| Signed Number, 11, 2 | Yes |
/*+INTEREST INCOME 1-30-18*/
SUM(CASE WHEN A.ACCOUNT >= '44100' AND A.ACCOUNT <= '44299' THEN A.UM_ACTUALS_CURR ELSE 0 END)
|
|
| YTD INTEREST INCOME (It looks like related to above, but I don't know why this one added fund code or other conditions)
- Tamara has processed for view
|
|
DM-287
-
UMS06 Endowment Shares, Book Value, Market Value, and Spending Balance.xls
Closed
| Signed Number, 11, 2 | Yes |
/*+YTD INTEREST INCOME 1-30-18*/
SUM(CASE WHEN A.ACCOUNT = '44199' AND A.STATISTICS_CODE = ' ' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51' THEN 'Yes' ELSE 'No' END ) = 'No' THEN (CASE WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' )
THEN A.UM_ACTUALS_YTD WHEN ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) THEN A.UM_ACTUALS_YTD WHEN A.ACCOUNT >= '30000' AND A.ACCOUNT <= '39999'
THEN A.UM_PROJECT_TO_DT ELSE 0 END) ELSE 0 END)
|
|
| MARKET VALUE
- Tamara has processed for view
| FUND_CODE 11 = Unrestricted Quasi Endowment
FUND_CODE 21 = Restricted Quasi Endowment FUND_CODE 31 = Restricted Non-Expendable Endowment FUND_CODE 51 = Endowment Funds Held for Others ACCOUNT 1260 = Invested in Endowment Pool ACCOUNT 1261 = Distributed Realized Gains ACCOUNT 1262 = Distributed Unrealized Gains |
DM-287
-
UMS06 Endowment Shares, Book Value, Market Value, and Spending Balance.xls
Closed
DM-336
-
YREND End Sh,BV MV, Net Assets
Closed
| Signed Number, 11, 2 | Yes |
/*+MARKET VALUE 1-30-18*/
( SUM(CASE WHEN A.ACCOUNT = '12160' AND A.STATISTICS_CODE = ' ' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51' THEN 'Yes' ELSE 'No' END ) = 'Yes' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT = '12161' AND A.STATISTICS_CODE = ' ' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51' THEN 'Yes' ELSE 'No' END ) = 'Yes' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT = '12162' AND A.STATISTICS_CODE = ' ' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51' THEN 'Yes' ELSE 'No' END ) = 'Yes' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
This query (DM-336) has a condition of FUND_CODE in list ('11','21','31','51') on the query criteria.
/*+MARKET VALUE 4-24-18*/
( SUM(CASE WHEN A.ACCOUNT = '12160' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT = '12161' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT = '12162' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
|
|
| UNREALIZED GAINS
- Tamara has processed for view
|
|
DM-287
-
UMS06 Endowment Shares, Book Value, Market Value, and Spending Balance.xls
Closed
DM-336
-
YREND End Sh,BV MV, Net Assets
Closed
| Signed Number, 11, 2 | Yes |
/*+UNREALIZED GAINS 1-30-18*/
SUM(CASE WHEN A.ACCOUNT = '12162' AND A.STATISTICS_CODE = ' ' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51' THEN 'Yes' ELSE 'No' END ) = 'Yes' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
This query (DM-336) has a condition of FUND_CODE in list ('11','21','31','51') on the query criteria.
/*+UNREALIZED_GAINS 4-24-18*/
SUM(CASE WHEN A.ACCOUNT = '12162' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
For Sep Inv (Separated Investment) report, the criteria is different according to Mary Allen:
/*+UNREALIZED_GAINS 4-24-18*/
SUM(CASE WHEN A.ACCOUNT = '12172' OR A.ACCOUNT = '12202' AND A.STATISTICS_CODE = ' ' THENA.UM_PROJECT_TO_DT ELSE 0 END)
|
|
| REALIZED GAINS
- Tamara has processed for view
|
|
DM-287
-
UMS06 Endowment Shares, Book Value, Market Value, and Spending Balance.xls
Closed
DM-336
-
YREND End Sh,BV MV, Net Assets
Closed
| Signed Number, 11, 2 | Yes |
/*+UNREALIZED GAINS 1-30-18*/
SUM(CASE WHEN A.ACCOUNT = '12162' AND A.STATISTICS_CODE = ' ' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51' THEN 'Yes' ELSE 'No' END ) = 'Yes' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
This query (DM-336) has a condition of FUND_CODE in list ('11','21','31','51') on the query criteria.
/*+REALIZED_GAINS 4-24-18*/
SUM(CASE WHEN A.ACCOUNT = '12161' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
For Sep Inv (Separated Investment) report, the criteria is different according to Mary Allen:
/*+REALIZED_GAINS 4-24-18*/
SUM(CASE WHEN A.ACCOUNT = '12171' OR A.ACCOUNT = '12201' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
|
|
| INITIAL INVESTMENT
- Tamara has processed for view
|
|
DM-287
-
UMS06 Endowment Shares, Book Value, Market Value, and Spending Balance.xls
Closed
DM-336
-
YREND End Sh,BV MV, Net Assets
Closed
| Signed Number, 11, 2 | Yes |
/*+INITIAL INVESTMENT BY FUND 1-30-18*/
SUM(CASE WHEN A.ACCOUNT = '12160' AND A.STATISTICS_CODE = ' ' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes'
WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes'
WHEN A.FUND_CODE = '51' THEN 'Yes'
ELSE 'No' END ) = 'Yes' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
This query (DM-336) has a condition of FUND_CODE in list ('11','21','31','51') on the query criteria.
/*+INITIAL INVESTMENT 12160 Account-Invested in Endowment Pool 4-24-18*/
SUM(CASE WHEN A.ACCOUNT = '12160' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
For Sep Inv (Separated Investment) report, the criteria is different according to Mary Allen:
/*+INITIAL INVESTMENT 12200 - Investments-Endowment Other, 11360 - CD-Endowment 4-24-18*/
SUM(CASE WHEN ( A.ACCOUNT = '12200' OR A.ACCOUNT ='11360' AND A.STATISTICS_CODE = ' ' ) THEN A.UM_PROJECT_TO_DT ELSE 0 END)
|
|
| SHARES
- Tamara has processed for view
|
|
DM-287
-
UMS06 Endowment Shares, Book Value, Market Value, and Spending Balance.xls
Closed
DM-336
-
YREND End Sh,BV MV, Net Assets
Closed
| Signed Number, 11, 2 | Yes |
/*+SHARES 1-30-18*/
SUM(CASE WHEN A.ACCOUNT = '12160' AND A.STATISTICS_CODE = 'EPS' AND ( CASE WHEN A.FUND_CODE = '11' THEN 'Yes' WHEN A.FUND_CODE = '21' THEN 'Yes'
WHEN A.FUND_CODE = '31' THEN 'Yes' WHEN A.FUND_CODE = '51' THEN 'Yes' ELSE 'No' END ) = 'Yes' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
This query (DM-336) has a condition of FUND_CODE in list ('11','21','31','51') on the query criteria.
/*+SHARES 4-24-18*/
SUM(CASE WHEN A.ACCOUNT = '12160' AND A.STATISTICS_CODE = 'EPS' THEN A.UM_PROJECT_TO_DT ELSE 0 END)
|
|
| TOTAL_LIABILITY
- Tamara has processed for view
|
|
DM-336
-
YREND End Sh,BV MV, Net Assets
Closed
DM-332
-
Endowment Shares, Book Value, and Market Value by Prog Rest 1D
Closed
| Signed Number, 11, 2 | Yes |
/*+TOTAL_LIABILITY 4-24-18*/
( SUM(CASE WHEN A.ACCOUNT = '24007' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT = '26000' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
On DM-332, the caluclation for liabilities total is different (sum of Liabilities_current and Liabilities_HFO, same as ACCOUNT between 20000 and 29999.)
/*+LIABILITIES_TOTAL 4-24-18*/
( SUM(CASE WHEN A.ACCOUNT >= '20000' AND A.ACCOUNT <= '25999' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
+( SUM(CASE WHEN A.ACCOUNT >= '26000' AND A.ACCOUNT <= '29999' AND A.STATISTICS_CODE = ' ' THEN A.UM_PROJECT_TO_DT ELSE 0 END) )
|
|
| SCHOLARSHIPS
- Tamara has processed for view
|
|
DM-289
-
USM Advancement Report 6 Gift Income and Campus Programs Balances
Closed
DM-344
-
Gift Income Account BalancesM
Parked
| Signed Number, 11, 2 | Yes |
/*+SCHOLARSHIPS 1-30-18*/
SUM(CASE WHEN (SUBSTR(A.ACCOUNT,1,2)) = '55' THEN
(CASE WHEN ( A.PROJECT_ID = ' ' AND ( A.ACCOUNT >= '30000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.PROJECT_ID <> ' ' AND ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' ) THEN A.UM_PROJECT_TO_DT ELSE 0 END) ELSE 0 END)
|
|
| GIFT_REVENUE
- Tamara has processed for view
|
|
DM-289
-
USM Advancement Report 6 Gift Income and Campus Programs Balances
Closed
DM-344
-
Gift Income Account BalancesM
Parked
| Signed Number, 11, 2 | Yes |
/*+GIFT_REVENUE 1-30-18*/
SUM(CASE WHEN A.ACCOUNT >= '43600' AND A.ACCOUNT <= '43699' THEN (CASE WHEN ( A.PROJECT_ID = ' ' AND ( A.ACCOUNT >= '30000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.PROJECT_ID <> ' ' AND ( A.ACCOUNT >= '40000' AND A.ACCOUNT <= '89999' ) ) THEN A.UM_PROJECT_TO_DT
WHEN ( A.ACCOUNT >= '10000' AND A.ACCOUNT <= '29999' ) THEN A.UM_PROJECT_TO_DT ELSE 0 END) ELSE 0 END)
|