University of Minnesota
Controller's Office
http://controller.umn.edu/ or http://www.finsys.umn.edu/
612-624-1617 or controller@umn.edu

Looking for legacy system data?

The easiest way to find data from the legacy system is to contact the University Financial Helpline and request assistance.

Information may reside in your department's local files, in central files, in the imaging system, in EDMS (another legacy imaging system), in the data warehouse (DWFS database), or elsewhere at the University. "Account" balances, open purchase orders or contracts, and other relevant activity was converted to the current financial system, EFS, when it was implemented in July of 2008.

CUFS

CUFS (the College and University Financial System) was the University of Minnesota's general ledger and financial system from November 1, 1991 through June 30, 2008. CUFS is no longer available as of June 30, 2008.

Data Warehouse

The Data Warehouse DWFS database of legacy CUFS financial data in the data warehouse will continue to be available to departments and colleges.

Financial FormsNirvana (FFN)

Financial FormsNirvana was a form preparation, editing, routing, and approval engine. FFN was designed and built by the University of Minnesota. At the time it went live in the mid 1990's, it was a fairly unique and cutting edge application. At the time it was decommissioned over a dozen years later, it had become a bit antiquated and no longer followed recommended risk mitigation/controls as related to the logic allowing the transaction preparer to select the approver. FFN is no longer available as of June 30, 2008.

Legacy UM Reports that displayed CUFS data

Effective July 1, 2014, legacy financial reports from UM Reports will be decommissioned. These reports provided the ability for departments to retrieve financial information from the University’s retired financial system, CUFS. CUFS was retired in June of 2008, with the implementation of PeopleSoft Financials. Six years into PeopleSoft Financials’ implementation, usage of these reports is very low and the costs associated with maintaining the reports is high. Decommissioning these reports is one of the final steps being taken to retire CUFS.

While the legacy financial reports are being decommissioned, the underlying CUFS data will remain available in the data warehouse. Four of the reports marked for retirement have notably higher usage than the others. They are:

  • Account Summary for Non-Sponsored Funds
  • Account Detail for Non-Sponsored Funds
  • Account Transactions for Non-Sponsored Funds
  • Department Account Summary for Non-Sponsored Funds

Similar to the decommissioning of "Financial Reports on the Web" in April of 2012, we will continue to provide people with a way to obtain the data contained on these top four legacy reports. This Excel spreadsheet describes the reports, their purpose, and how one might go about re-creating the top four legacy UM Reports that pulled CUFS data.

Account Summary for Non-Sponsored Funds
Account Detail for Non-Sponsored Funds
Account Transactions for Non-Sponsored Funds

The following SQL replaces "Account Summary for Non-Sponsored Funds", "Account Detail for Non-Sponsored Funds", & "Account Transactions for Non-Sponsored Funds":

SELECT a.FUND,
a.AREA,
a.ORG,
a.SUBORG,
a.FUNC,
CONCAT(a.TRANS_CD, a.TRANS_NO) AS "TRANSACTION",
a.date_of_record trans_date,
a.VENDOR_NAME,
CASE
WHEN a.acct_type in ('31')
THEN a.REV
WHEN a.acct_type in ('51')
THEN a.REV
WHEN a.acct_type in ('41')
THEN a.OBJ
ELSE a.obj
END AS "OBJ/REV",
CASE
WHEN a.acct_type in ('31')
THEN a.SUBREV
WHEN a.acct_type in ('51')
THEN '00'
WHEN a.acct_type in ('41')
THEN '00'
ELSE a.subobj
END AS "SUBOBJ/SUBREV",
CASE
WHEN a.acct_type in ('31')
THEN CONCAT(a.REV, a.SUBREV)
WHEN a.acct_type in ('51')
THEN a.REV||'00'
WHEN a.acct_type in ('41')
THEN a.OBJ||'00'
ELSE concat(a.obj, a.subobj)
END AS "OBJ(SUB)/REV(SUB)",
trim(a.LINE_DESC) line_desc,
CASE
WHEN a.ACCT_TYPE in ('31')
THEN a.ACCT_TYPE||' - Revenues'
WHEN a.ACCT_TYPE in ('21')
THEN a.ACCT_TYPE||' - Encumbrances'
WHEN a.ACCT_TYPE in ('22')
THEN a.ACCT_TYPE||' - Expenses'
WHEN a.ACCT_TYPE in ('51')
THEN a.ACCT_TYPE||' - Budgeted Revenues'
WHEN a.ACCT_TYPE in ('41')
THEN a.ACCT_TYPE||' - Budgeted Expenses'
END AS "ACCT_TYPE",
a.dollar_amt,
a.ACCTG_PD,
a.FISC_YR
FROM DWFS_TRAN_HIST a
WHERE a.AREA = '636'
AND a.ORG = '1205'
AND a.FISC_YR between '2008' and '2008'
AND a.ACCT_TYPE IN ('31','21','22','41','51')
union all
select
fund,
area,
org,
' ',
func,
' ',
null,
' ',
'N/A',
'N/A',
'N/A',
'Prior Year Carry Forward',
'Carryforward',
prior_yr_carryfwd,
' ',
fisc_yr
from dwfs_coa_org_hist
where area = '636'
and org = '1205'
and fisc_yr = '2008'
order by 13,11,9

Department Account Summary for Non-Sponsored Funds

The following SQL replaces "Department Account Summary for Non-Sponsored Funds":

select
area,
org,
fund,
beg_bal,
bud_rev,
act_rev,
bud_ti,
act_ti,
bud_exp,
act_exp,
bud_to,
act_to,
beg_bal + act_rev + act_ti - act_exp - act_to cash_bal,
encumb,
beg_bal + act_rev + act_ti - act_exp - act_to - encumb unob_bal
from(
select
area,
org,
fund,
sum(b_bal) beg_bal,
sum(bud_rev) bud_rev,
sum(act_rev) act_rev,
sum(bud_ti) bud_ti,
sum(act_ti) act_ti,
sum(bud_exp) bud_exp,
sum(act_exp) act_exp,
sum(bud_to) bud_to,
sum(act_to) act_to,
sum(encumb) encumb
from(
select
area,
org,
fund,
prior_yr_carryfwd b_bal,
0 bud_rev,
0 act_rev,
0 bud_ti,
0 act_ti,
0 bud_exp,
0 act_exp,
0 bud_to,
0 act_to,
0 encumb
from dwfs_coa_org_hist
where area = '317'
and fisc_yr = '2008'
union all
select
a.area,
a.org,
a.fund,
0,
case when b.objrev_cat <> 'NT'
then a.curr_bud_amt
else 0 end,
case when b.objrev_cat <> 'NT'
then a.tot_rev
else 0 end,
case when b.objrev_cat = 'NT'
then a.curr_bud_amt
else 0 end,
case when b.objrev_cat = 'NT'
then a.tot_rev
else 0 end,
0,
0,
0,
0,
0
from dwfs_sum_rev_hist a,
dwfs_coa_objrev b
where a.area = '317'
and a.rev = b.objrev
and a.fisc_yr = '2008'
and a.acctg_pd = '15'
union all
select
a.area,
a.org,
a.fund,
0,
0,
0,
0,
0,
case when b.objrev_cat <> 'NT'
then a.curr_bud_amt
else 0 end,
case when b.objrev_cat <> 'NT'
then a.tot_exp
else 0 end,
case when b.objrev_cat = 'NT'
then a.curr_bud_amt
else 0 end,
case when b.objrev_cat = 'NT'
then a.tot_exp
else 0 end,
a.tot_pre_encumb + a.tot_encumb
from dwfs_sum_obj_hist a,
dwfs_coa_objrev b
where a.area = '317'
and a.obj = b.objrev
and a.fisc_yr = '2008'
and a.acctg_pd = '15')
group by
area,
org,
fund)
order by
fund,
area,
org

Financial.reports.umn.edu

One of the final pieces of the legacy financial system to be decommissioned is the old financial reporting web site http://financial.reports.umn.edu. This site allowed departments to run reports on legacy CUFS financial data. Many of these reports have not been run in years, while others are still run on occasion (primarily for troubleshooting issues related to activity that took place in FY2008 or earlier). The site will no longer be available as of April 30, 2012.

The Controller’s Office and OIT have put together information on how to get at the data in a manner similar to the most used reports or queries on the financial.reports.umn.edu site. For the few reports/queries still in use to trouble-shoot or view legacy financial transactions, alternate methods to get at the data are available below.

View an Excel spreadsheet that describes the reports that were available on this site, their purpose, and how one might go about re-creating the top ten *most used report results using the data warehouse (DWFS). (*Most used since July 2008.)

How to re-create the top ten queries and reports (click the item to see details)

CUFS DTEX or POTX for Specific Document

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS DTEX or POTX for a specific document. Substitute the type of CUFS transaction and transaction number where there is sample data provided below.

/** DTEX **/

select *
from dwfs_tran_dtext
where trans_cd = 'PV'
and trans_no = '881F6228523'
union all
select *
from dwfs_tran_dtext_hist
where trans_cd = 'PV'
and trans_no = '881F6228523'
order by 3;

 

/** POTX **/

select *
from dwfs_tran_potx
where trans_cd = 'PO'
and trans_no = 'B0082952504'
order by 3,4

Transaction/Account Inquiry (TRNI)

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Transaction/Account Inquiry (similar to what appeared on the TRNI table in CUFS) for a specific document. Substitute the type of CUFS transaction type and transaction number where there is sample data provided below.

select *
from dwfs_tran_hist
where trans_cd = 'PV'
and trans_no = '881F6228523'

Sponsored Accounts Receivable and Aging by Area Org

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Sposnored Accounts Receivable and Aging by Area Org. Substitute the CUFS Area and Org where there is sample data provided below. Open this Excel spreadsheet and review the sample information provided on the tab "Spons AR and Aging by Area Org" for information on how to treat/view the data.

SELECT a.MAIN_FUND as "Fund" , a.MAIN_AREA as "Area" , a.MAIN_ORG as "Org" , a.AREA_CLS_NAME30 as "Area Class" ,
a.PROJECT_NAME as "Project Name" , a.GRANTOR_NAME as "Grantor" , a.PRIN_INV1_NAME as "PI Name" ,
a.INVOICE_DATE as "Invoice Date" , a.INVOICE_NBR as "Invoice Number" , a.INVOICE_AMT as "Total Invoiced" ,
a.PAYMENT_AMT as "Total Collected" , a.AMOUNT_DUE as "Current" , a.AMOUNT_AGING_CAT_2 as "31-60" ,
a.AMOUNT_AGING_CAT_3 as "61-90" , a.AMOUNT_AGING_CAT_4 as "91-120" , a.AMOUNT_AGING_CAT_5 as "Over 120"
FROM DWFS_GRANT_ACCT_REC a
WHERE a.MAIN_AREA = '268'
AND a.MAIN_ORG = '9006'
ORDER BY a.INVOICE_DATE

Automated Disbursement (Check) Detail

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Automated Disbursement (Check) Detail for a specific CUFS check. Substitute the CUFS check number where there is sample data provided below.

select a.*
, nvl(b.dtext_line,'No Check Message') ckmsg
from(
select a.trans_cd
, a.trans_no
, a.vendor_cd
, a.vendor_name
, a.ref_trans_id pv_numb
, b.date_of_record
, b.acceptance_date
, b.fisc_yr
, b.acctg_pd
, nvl(b.doc_desc,' ') doc_desc
, b.fund
, b.area
, b.org
, nvl(b.suborg, ' ') sorg
, nvl(b.obj, ' ') object
, nvl(b.subobj, ' ') sub_object
, nvl(b.rev,' ') revenue
, nvl(b.subrev,' ')sub_revenue
, nvl(b.bs_acct,' ') bacc
, nvl(b.ref_trans_id,' ') ref_trans_id
, nvl(b.line_desc,' ') line_desc
, b.dollar_amt
from(
select distinct trans_cd
, trans_no
, ref_trans_id
, vendor_cd
, vendor_name
from dwfs_tran_hist
where trans_cd = 'AD'
and trans_no = '00007628779') a
, dwfs_tran_hist b
where substr(a.ref_trans_id,1,2) = b.trans_cd
and substr(a.ref_trans_id,3,11) = b.trans_no
and (acct_type in ('22','01','31','02')
and bs_acct <> '2000'
or bs_acct is null)) a
left outer join dwfs_tran_dtext_hist b
on substr(a.pv_numb,1,2) = b.trans_cd
and substr(a.pv_numb,3,11) = b.trans_no
and b.dtext_line_no = '001'
and b.dtext_line like 'CKMSG:'
union
select trans_cd
, trans_no
, vendor_cd
, vendor_name
, ref_trans_id
, date_of_record
, acceptance_date
, fisc_yr
, acctg_pd
, nvl(doc_desc,' ')
, fund
, area
, org
, nvl(suborg,' ')
, obj
, subobj
, nvl(rev,' ')
, nvl(subrev,' ')
, nvl(bs_acct,' ')
, nvl(ref_trans_id,' ')
, nvl(line_desc,' ')
, dollar_amt
, 'Discount'
from dwfs_tran_hist
where trans_cd = 'AD'
and trans_no = '00007628779'
and acct_type in ('01','22')
and (bs_acct <> '1000'
or bs_acct is null)
order by 5,10,7;

Project Summary - Expenses

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Project Summary - Expenses. Substitute the CUFS Area and Org codes where there is sample data provided below. Open this Excel spreadsheet and review the sample information provided on the tab "Project Summary - Expenses" for information on how to treat/view the data.

SELECT a.dollar_amt AS "Trans Amt",
CONCAT(a.trans_cd, a.trans_no) AS "Trans",
a.fisc_yr,
a.acctg_pd,
a.obj,
a.subobj,
a.fund, a.area, a.org,
a.line_desc,
a.vendor_name,
a.date_of_record AS "Date",
a.acct_type
FROM dwfs_tran_hist a
WHERE a.area = '187' AND a.org = '6600'
AND (a.acct_type = '22' OR a.acct_type = '21' OR a.acct_type = '41')
AND a.acctg_pd < '14' AND a.fisc_yr < '2009'
AND a.obj < '8501'

Payment Voucher (PV) Payment Status

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Payment Voucher Payment Status for a specific document. Substitute the type of CUFS PV transaction number where there is sample data provided below.

select distinct a.*
, b.trans_cd ck_cd
, b.trans_no ck_no
from (
select a.trans_cd
, a.trans_no
, a.vendor_cd
, a.vendor_name
, a.line_no
, a.fund
, a.area
, a.org
, a.suborg
, a.rev
, a.subrev
, a.obj
, a.subobj
, a.bs_acct
, a.doc_desc
, a.line_desc
, a.dollar_amt
, b.acceptance_date voucher_date
, sum(b.dollar_amt) voucher_amt
from (
select trans_cd
, trans_no
, vendor_cd
, vendor_name
, line_no
, fund
, area
, org
, nvl(suborg,' ') suborg
, nvl(rev,' ') rev
, nvl(subrev,' ') subrev
, nvl(obj,' ') obj
, nvl(subobj,' ') subobj
, nvl(bs_acct,' ') bs_acct
, nvl(doc_desc,' ') doc_desc
, nvl(line_desc,' ') line_desc
, sum(dollar_amt) dollar_amt
from dwfs_tran_hist
where trans_cd = 'PV'
and trans_no = '881F6228523'
and acct_type in ('02', '01', '22', '31')
and (bs_acct <> '2000'
or bs_acct is null)
group by trans_cd
, trans_no
, vendor_cd
, vendor_name
, line_no
, fund
, area
, org
, nvl(suborg,' ')
, nvl(rev,' ')
, nvl(subrev,' ')
, nvl(obj,' ')
, nvl(subobj,' ')
, nvl(bs_acct,' ')
, nvl(doc_desc,' ')
, nvl(line_desc,' ')) a
, dwfs_tran_hist b
where a.trans_cd = b.trans_cd
and a.trans_no = b.trans_no
and b.acct_type in ('02', '01', '22', '31')
and (b.bs_acct <> '2000'
or b.bs_acct is null)
group by a.trans_cd
, a.trans_no
, a.vendor_cd
, a.vendor_name
, a.line_no
, a.fund
, a.area
, a.org
, a.suborg
, a.rev
, a.subrev
, a.obj
, a.subobj
, a.bs_acct
, a.doc_desc
, a.line_desc
, a.dollar_amt
, b.acceptance_date) a
left outer join dwfs_tran_hist b
on a.trans_cd||a.trans_no = b.ref_trans_id
and b.trans_cd in ('AD', 'MW', 'DD', 'TC')
and b.bs_acct in ('2000','2006')

Balance Sheet Detail

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Balance Sheet Detail. Substitute the desired values where there is sample data provided below. Open this Excel spreadsheet and review the sample information provided on the tab "Balance Sheet Detail" for information on how to treat/view the data.

1) SQL for LTD thru specified FY and ACCT PD

SELECT a.FUND, a.ACCT_TYPE, a.REPORT_CAT, a.BS_ACCT, a.ACCTG_PD, a.FISC_YR, a.TOT_BAL
FROM DWFS_SUM_BALSHT_HIST a
WHERE a.FUND = '1717'
AND a.BS_ACCT = '1127'
AND a.ACCT_TYPE = '01'
AND a.FISC_YR = '2008'
AND a.ACCTG_PD = '11'

NOTE: can add additional criteria in where clause for a.REPORT_CAT as AND a.REPORT_CAT = 'XXXX'

2) SQL for Trans Detail for FY and ACCT PD

SELECT a.dollar_amt AS "Trans Amt",
CONCAT(a.trans_cd, a.trans_no) AS "Trans",
a.report_cat,
a.fisc_yr,
a.acctg_pd,
a.bs_acct,
a.fund, a.area, a.org,
a.line_desc,
a.date_of_record AS "Date",
a.acct_type
FROM dwfs_tran_hist a
WHERE a.fund = '1717'
AND a.bs_acct = '1127'
AND a.acct_type = '01'
AND a.acctg_pd = '12' AND a.fisc_yr = '2008'

Flexible Period Expense Summary

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Flexible Period Expense Summary information. Substitute the desired values where there is sample data provided below. Open this Excel spreadsheet and review the sample information provided on the tab "Flexible Period Expense Summary" for information on how to treat/view the data (same SQL used as Project Summary - Expenses, just filtered differently in an Excel pivot table).

SELECT a.dollar_amt AS "Trans Amt",
CONCAT(a.trans_cd, a.trans_no) AS "Trans",
a.fisc_yr,
a.acctg_pd,
a.obj,
a.subobj,
a.fund, a.area, a.org,
a.line_desc,
a.vendor_name,
a.date_of_record AS "Date",
a.acct_type
FROM dwfs_tran_hist a
WHERE a.area = '187' AND a.org = '6600'
AND (a.acct_type = '22' OR a.acct_type = '21' OR a.acct_type = '41')
AND a.acctg_pd < '14' AND a.fisc_yr < '2009'
AND a.obj < '8501'

Sub Detail (sponsored)

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Sub Detail information. Substitute the desired values where there is sample data provided below. Open this Excel spreadsheet and review the sample information provided on the tab "Sub Detail" for information on how to treat/view the data.

SELECT a.FUND, a.AREA, a.ORG, a.SUBORG, a.FUNC, CONCAT(a.TRANS_CD,
a.TRANS_NO) AS "TRANSACTION", a.VENDOR_NAME,
CONCAT(a.OBJ, a.SUBOBJ) AS "OBJ(SUB)/REV(SUB)",
a.LINE_DESC, a.DOLLAR_AMT, a.ACCT_TYPE, a.ACCTG_PD, a.FISC_YR
FROM DWFS_TRAN_HIST a
WHERE a.AREA = '529'
AND a.ORG = '6231'
AND a.ACCT_TYPE = '22'
UNION ALL
SELECT a.FUND, a.AREA, a.ORG, a.SUBORG, a.FUNC, CONCAT(a.TRANS_CD,
a.TRANS_NO) AS "TRANSACTION", a.VENDOR_NAME,
CONCAT(a.REV, a.SUBREV) AS "OBJ(SUB)/REV(SUB)",
a.LINE_DESC, a.DOLLAR_AMT, a.ACCT_TYPE, a.ACCTG_PD, a.FISC_YR
FROM DWFS_TRAN_HIST a
WHERE a.AREA = '529'
AND a.ORG = '6231'
AND a.ACCT_TYPE = '31'

Transaction Detail by Area/Org/Fiscal Year

Use the SQL below in the data warehouse (DWFS) free form query tool to find CUFS Transaction Detail by Area/Org/Fiscal Year information. Substitute the desired values where there is sample data provided below. Open this Excel spreadsheet and review the sample information provided on the tab "Trans Detail by Area-Org-FY" for information on how to treat/view the data.

SELECT a.FUND, a.AREA, a.ORG, a.SUBORG, a.FUNC, CONCAT(a.TRANS_CD,
a.TRANS_NO) AS "TRANSACTION", a.VENDOR_NAME,
CONCAT(a.OBJ, a.SUBOBJ) AS "OBJ(SUB)/REV(SUB)",
a.LINE_DESC, a.DOLLAR_AMT, a.ACCT_TYPE, a.ACCTG_PD, a.FISC_YR
FROM DWFS_TRAN_HIST a
WHERE a.AREA = '529'
AND a.ORG = '6231'
AND a.FISC_YR = '2007'
AND a.ACCT_TYPE = '22'
UNION ALL
SELECT a.FUND, a.AREA, a.ORG, a.SUBORG, a.FUNC, CONCAT(a.TRANS_CD,
a.TRANS_NO) AS "TRANSACTION", a.VENDOR_NAME,
CONCAT(a.REV, a.SUBREV) AS "OBJ(SUB)/REV(SUB)",
a.LINE_DESC, a.DOLLAR_AMT, a.ACCT_TYPE, a.ACCTG_PD, a.FISC_YR
FROM DWFS_TRAN_HIST a
WHERE a.AREA = '529'
AND a.ORG = '6231'
AND a.FISC_YR = '2007'
AND a.ACCT_TYPE = '31'