| Chapter 25 - FRS Data Warehouse |
| February 2008 |
The data warehouse containing the raw information from the mainframe
systems. It is an Oracle database server designed to distribute campus central
administrative data to individual college and departmental users. It allows
the user to download data to the users PC. The D.W. has been implemented using
a relational database. The data from the mainframe is replicated periodically
into D.W. tables which provide a convenient and easy mechanism for adhoc
queries using a variety of desktop tools. The current campus favorite is
Microsoft Access.
Tables and views starting with APOLLO.WFRS contain FRS financial information.
| cur_mo tables contain current information |
(DOS names start with FG) |
| prv_mo tables contain data as of the last month-end |
(DOS names start with FH) |
| his_fy tables contain data from FY91 to the present |
(DOS names start with FY) |
|
Contact your LAN Manager for the log in procedures. Consult with the network
manager for your department prior to logging on to D.W.. A D.W. application
for FRS must be completed prior to access being granted to the user.
D.W. Administrator:
Josh Clark, Information Systems, 6th Floor-North, University Services Center, 491-7169
FRS Administrator:
John Hunter, Business & Financial Services, 555 S Howes St, 3rd Floor, 491-1956
|
LOG ON TO D.W.: (MICROSOFT ACCESS)
START, PROGRAMS, MICROSOFT ACCESS
When viewing a table, or running a query, you will be prompted to input your
user login and password. |
LOG OUT OF D.W.: (MICROSOFT ACCESS)
Close Microsoft Access by clicking FILE, EXIT.
When viewing a table, or running a query, you will be prompted to input your
user login and password. |
MAILING LIST:
To Subscribe to the D.W. mailing list, to receive training information,
new tables or changes to table, etc.
Click on the Link below:
Subscribe to one of CSU's non-class-related Mailing Lists
- select DELHPI-L from the pull-down list of mailing lists
- enter your email address
- enter your name
- click [Submit Query]
|
LINKING TABLES:
Once in access and you have a database open, click:
Click on the Link below:
FILE
GET EXTERNAL DATA
LINK TABLES
|
In the "files of type" area towards the bottom left it will say
"Microsoft Access". Click in that box and select ODBC Databases.
A "select data source" popup box appears. Select "delphi (not
sharable).dsn" and click OK.
You'll be prompted to login to the D.W.. Input your userid and password;
then click OK.
A "link tables" popup box appears. Highlight the table you want
to link, and click OK. note: frs tables are under apollo.f**** OR apollo.wfrs.****
TABLES AVAILABLE:
| DOS | Windows |
| Current Files |
| These tables are refreshed daily (M-F night) |
Apollo.wfrs_ |
| Chart of Accounts |
FGCHRT |
cur_mo_coa_01 |
| Cost Share Accts |
FGCSSP |
cur_mo_sl_attr_cs_01 |
| Encumbrances |
FGENCM |
cur_mo_encumb_01 |
| GL Balances |
FGGLAC |
cur_mo_gl_bal_01 |
| GL Attributes |
FGGLDM |
cur_mo_gl_attr_01 |
| GL C&G Attributes |
FGGLSP |
cur_mo_gl_attr_sp_01 |
| Principal Investigators |
FGPISP |
cur_mo_sl_attr_pi_01 |
| SL Balances |
FGSLAC |
cur_mo_sl_bal_01 |
| SL Attributes |
FGSLDM |
cur_mo_sl_attr_01 |
| SL C&G Attributes |
FGSLSP |
cur_mo_sl_attr_sp_01 |
| Curr Yr Transactions |
FGTRAN |
currfy_trans_00 |
| FRS Buildings |
|
bldg_number_desc_00 |
| FRS Departments |
|
whrs_dept_table_00 |
| FRS Subcodes |
FRSBCD |
subcodes_00 |
|
| Prior Month Files |
| These tables are frozen
after the end of each monthly closing. |
| Chart of Accounts |
FHCHRT |
prv_mo_coa_01 |
| Cost Share Accts |
FHCSSP |
prv_mo_sl_attr_cs_01 |
| Encumbrances |
FHENCM |
prv_mo_encumb_01 |
| GL Balances |
FHGLAC |
prv_mo_gl_bal_01 |
| GL Attributes |
FHGLDM |
prv_mo_gl_attr_01 |
| GL C&G Attributes |
FHGLSP |
prv_mo_gl_attr_sp_01 |
| Principal Investigators |
FHPISP |
prv_mo_sl_attr_pi_01 |
| SL Balances |
FHSLAC |
prv_mo_sl_bal_01 |
| SL Attributes |
FHSLDM |
prv_mo_sl_attr_01 |
| SL C&G Attributes |
FHSLSP |
prv_mo_sl_attr_sp_01 |
|
| Prior Year Files |
| These tables currently
contain data from FY91 to the present. |
| These tables are frozen
after the end of each yearend closing. |
| Chart of Accounts |
FYCHRT |
his_fy_coa_01 |
| Cost Share Accts |
FYCSSP |
his_fy_sl_attr_cs_01 |
| Encumbrances |
FYENCM |
his_fy_encumb_01 |
| GL Balances |
FYGLAC |
his_fy_gl_bal_01 |
| GL Attributes |
FYGLDM |
his_fy_gl_attr_01 |
| GL C&G Attributes |
FYGLSP |
his_fy_gl_attr_sp_01 |
| Principal Investigators |
FYPISP |
his_fy_sl_attr_pi_01 |
| SL Balances |
FYSLAC |
his_fy_sl_bal_01 |
| SL Attributes |
FYSLDM |
his_fy_sl_attr_01 |
| SL C&G Attributes |
FYSLSP |
his_fy_sl_attr_sp_01 |
| Curr Yr Transactions |
FYTRAN |
prevfy_trans_00 |
|
UPDATES
FRS data is updated on the D.W. daily, Monday thru Friday night.
When viewing a table, or running a query, you will be prompted to input your
user login and password. |
GL (General Ledger) vs SL (Sub-ledger)
FRS data is updated on the D.W. daily, Monday thru Friday night.
When viewing a table, or running a query, you will be prompted to input your
user login and password.
General Ledger accounts begin with zero and maintain the assets, liabilities
and fund balance of an account. Sub-ledger accounts (those beginning with
1 thru 9) maintain the revenue and expenses for the fiscal year of an account.
As a rule, users would be interested in the general ledger for 21, 22, 26,
62, 64 and 99 fund accounts - those accounts where there is a carryover of
funds from the previous year. General Ledger accounts for 13 and 53 funds
are not of interest for the "general" campus user.
|
| COA_01 |
Chart of Accounts
Tables |
|
| Element |
FG/FS |
Length |
| Account Number |
002/002 |
A6 |
| Responsible Person |
008/008 |
A20 |
| Delete Flag |
026/026 |
A1 |
| Freeze Flag |
028/028 |
A1 |
| Department |
144/052 |
A4 |
| College |
142/050 |
A2 |
| Fund |
148/066 |
A2 |
|
36 |
|
| SL_ATTR_CS_01 |
Cost Share Account
Attributes |
| subledger only |
|
| Element |
|
Length |
| Fiscal Year |
FS002 |
A2 |
| Account Number |
FS008 |
A6 |
| Cost Share Letter |
NA |
A1 |
| Cost Share Account A-E |
FS891-895 |
A6 |
| Extract Date (8,23) |
date |
D8 (MM/DD/YY) |
| Division |
FS048 |
A2 |
| College |
FS050 |
A2 |
| Department (4) |
FS052 |
A4 |
| Vice President (1) |
FS046 |
A1 |
| Fund Group |
FS066 |
A2 |
| Purpose |
FS082 |
A2 |
| Security |
FS506 |
A6 |
|
47 |
|
| ENCUMB_01 |
Encumbrance Tables |
| subledger only |
|
| Element |
|
Length |
| Fiscal Year |
FS002 |
A2 |
| Account Number |
FS008 |
A6 |
| Subcode |
FS914 |
A4 |
| Document Reference |
FO024 |
A7 |
| Description |
FO036 |
A20 |
| Entry/Transaction Code |
FO060 |
A3 |
| Original Dollars (16,31) |
FO038 |
$16 (12.2-) |
| Current Dollars (16,31) |
FO040 |
$16 (12.2-) |
| Liquidating/Paid Amount |
FO042 |
$16 (12.2-) |
| Original Date (8,23) |
FO048/50/46 |
D8 (MM/DD/YY) |
| Last Update (8,23) |
FO056/58/54 |
D8 (MM/DD/YY) |
| Drop Flag |
FO026 |
A1 |
| System Drop Flag |
FO028 |
A1 |
| Extract Date (8,23) |
date |
D8 (MM/DD/YY) |
| Division |
FS048 |
A2 |
| College |
FS050 |
A2 |
| Department |
FS052 |
A4 |
| Vice President |
FS046 |
A2 |
| Fund Group |
FS066 |
A2 |
| Purpose |
FS082 |
A2 |
| Security |
FS506 |
A6 |
|
136 |
| GL_BAL_01 |
GL Account Balance Tables |
| SL_BAL_01 |
SL Account Balance Tables |
|
| Element |
FG/FS |
Length |
| Fiscal Year |
002/002 |
A2 |
| Account Number |
008/008 |
A6 |
| Subcode |
914/914 |
A4 |
| Acct Control Description |
506/940 |
A20 |
| FY Beginning Balance |
942/NA |
$16 (12.2-) |
| Original Budget |
NA/944 |
$16 (12.2-) |
| Revised Budget |
NA/948 |
$16 (12.2-) |
| Current Month Activity |
944/954 |
$16 (12.2-) |
| Year-to-Date Balance |
946/956 |
$16 (12.2-) |
| Encumbrances |
NA/960 |
$16 (12.2-) |
| Budget Balance Available |
NA/962 |
$16 (12.2-) |
| Project Fiscal Yr to Date |
NA/520 |
$16 (12.2-) |
| Project/Inception to date |
NA/958 |
$16 (12.2-) |
| Prior CM Activity |
948/964 |
$16 (12.2-) |
| Prior YTD Balance |
950/966 |
$16 (12.2-) |
| Extract Date (8,23) |
date |
D8 (MM/DD/YY) |
| Division |
140/048 |
A2 |
| College |
142/050 |
A2 |
| Department |
144/052 |
A4 |
| Vice President |
138/046 |
A2 |
| Fund Group |
148/066 |
A2 |
| Purpose |
156/082 |
A2 |
| Security |
500/506 |
A6 |
|
140/220 |
|
| Notes: |
|
| The history balance tables will contain
data from FY91 to the present. Enter the fiscal year desired
in the FISCAL YEAR field. (07 should be entered for FY 2007) |
|
| GL_ATTR_01 |
GL Attribute/Demographic
Tables |
| SL_ATTR_01 |
SL Attribute/Demographic
Tables |
|
| Element |
FG/FS |
Length |
| Fiscal Year |
002/002 |
A2 |
| Account Number |
008/008 |
A6 |
| Delete Flag |
026/026 |
A1 |
| Freeze Flag |
028/028 |
A1 |
| ABR Flag |
NA/036 |
A1 |
| Map Code |
NA/039 |
A5 |
| Short Title |
040/040 |
A20 |
| Responsible Person |
042/042 |
A20 |
| Address |
044/060 |
A20 |
| Long Description 1/2 |
501/510 |
A40 |
| Long Description 2/2 |
502/511 |
A40 |
| Audit |
895/775 |
A1 |
| COFRS Fund |
552/160 |
A3 |
| Controlled Funds |
709/709 |
A1 |
| Nacubo Expense 1-10 |
730/730 |
A10 |
| Nacubo Revenue 1-5 |
743/743 |
A5 |
| State Fund Group |
844/883 |
A2 |
| State Report Code |
897/774 |
A3 |
| Year End |
046/058 |
A1 |
| Zwriter Dept |
841/860 |
A14 |
| Student Housing |
NA/859 |
A5 |
| 1984A Fin St Cd |
834/824 |
A2 |
| Aux Enterprise |
832/879 |
A1 |
| Aux FBM070 |
831/880 |
A1 |
| Aux Fin Stmt Cd |
833/861 |
A2 |
| ADP |
NA/888 |
A2 |
| Budget Special Reporting |
NA/878 |
A1 |
| CCI |
NA/850 |
A1 |
| College Attribute 1 |
NA/873 |
A1 |
| Continuing Ed |
NA/862 |
A1 |
| Exp Stat Master Project |
NA/877 |
A4 |
| IDC Proposal |
NA/707 |
A1 |
| Instructional Services |
702/702 |
A1 |
| Physical Plant |
835/881 |
A2 |
| Rev Fd Purpose |
836/871 |
A3 |
| Payroll Rollforward |
NA/867 |
A1 |
| Extract Date (8,23) |
date |
D8 (MM/DD/YY) |
| Division |
140/048 |
A2 |
| College |
142/050 |
A2 |
| Department (4) |
144/052 |
A4 |
| Vice President (1) |
138/046 |
A1 |
| Fund Group |
148/066 |
A2 |
| Purpose |
156/082 |
A2 |
| Security |
500/506 |
A6 |
| Subdept |
146/054 |
A2 |
|
231/254 |
|
| GL_ATTR_SP_01 |
Contract & Grant
Attribute/Demographic Tables |
| SL_ATTR_SP_01 |
| Element |
FG/FS |
Length |
| Fiscal Year |
002/002 |
A2 |
| Account Number |
008/008 |
A6 |
| Month Project Ends |
NA/505 |
A2 |
| Project Type |
701/701 |
A1 |
| Federal Flow Through |
708/708 |
A2 |
| Federal Domestic Asst |
860/772 |
A6 |
| Final Billing (14,2) |
NA/773 |
$14 (10.2-) |
| Auto Billing Type |
NA/780 |
A2 |
| Payment Method |
899/785 |
A2 |
| Billing Frequency |
NA/804 |
A3 |
| Working Capital |
862/805 |
A1 |
| IDC Limit (14,2) |
NA/806 |
$14 (10.2-) |
| Contract Rate (6,1) |
NA/807 |
$ 6 ( .4-) |
| Billing Rate (6,1) |
NA/808 |
$6 ( .4-) |
| IDC Adjustment |
NA/809 |
A1 |
| Indirect Cost Method |
NA/810 |
A2 |
| Provisional Amt (14,2) |
NA/811 |
$14 (10.2-) |
| Cash/Federal Flag |
812/812 |
A1 |
| Research Type |
870/815 |
A1 |
| NSF Major Category |
871/816 |
A2 |
| Sponsor Document # |
NA/820 |
A30 |
| WRC-Combined |
847/821 |
A1 |
| WRC-Individual |
846/822 |
A1 |
| Master Contract Number |
861/823 |
A6 |
| C/G Fund Source |
866/826 |
A2 |
| PI Department A (4) |
NA/833 |
A4 |
| PI SSN A |
NA/834 |
A9 |
| PI Name A (16) |
NA/835 |
A16 |
| Sponsor Source |
867/836 |
A6 |
| Budget Close Date (8,23) |
876/838 |
D8 (MM/DD/YY) |
| Budget Open Date (8,23) |
877/839 |
D8 (MM/DD/YY) |
| End Date (8,23) |
438/840 |
D8 (MM/DD/YY) |
| Start Date (8,23) |
436/841 |
D8 (MM/DD/YY) |
| Root Number |
882/849 |
A8 |
| Account Sponsor |
896/886 |
A5 |
| Budget Special Reporting |
NA/878 |
A1 |
| Campus Indicator |
NA/889 |
A1 |
| Cost Share Flag |
NA/890 |
A1 |
| Cost Share Acct A |
893/895 |
A6 |
| Sponsor |
894/896 |
A6 |
| FBM090 Report Indicator |
NA/898 |
A1 |
| Extract Date (8,23) |
date |
D8 (MM/DD/YY) |
| Division |
140/048 |
A2 |
| College |
142/050 |
A2 |
| Department (4) |
144/052 |
A4 |
| Vice President (1) |
138/046 |
A1 |
| Fund Group |
148/066 |
A2 |
| Purpose |
156/082 |
A2 |
| Security |
500/506 |
A6 |
|
124/251 |
|
| SL_ATTR_PI_01 |
Principal Investigators
Attributes |
| subledger only |
|
| Element |
|
Length |
| Fiscal Year |
FS002 |
A2 |
| Account Number |
FS008 |
A6 |
| PI Letter |
NA |
A1 |
| PI Name A-E |
FS835,832,829,719,716 |
A20 |
| PI SSN A-E |
FS834,831,828,718,715 |
A9 |
| PI Dept A-E |
FS833,830,827,717,714 |
A4 |
| Extract Date (8,23) |
date |
D8 (MM/DD/YY) |
| Division |
FS048 |
A2 |
| College |
FS050 |
A2 |
| Department (4) |
FS052 |
A4 |
| Vice President (1) |
FS046 |
A1 |
| Fund Group |
FS066 |
A2 |
| Purpose |
FS082 |
A2 |
| Security |
FS506 |
A6 |
|
74 |
|
| CURRFY_TRANS_00 |
Transaction Tables |
| PREVFY_TRANS_00 |
|
| Element |
|
Length |
| Fiscal Year |
FT020 |
A2 |
| Audit Account |
FT701 |
A6 |
| Audit Subcode |
FT702 |
A4 |
| Processed Month |
FT710 |
A2 |
| Entry Code |
FT030 |
A3 |
| Document Reference |
FT048 |
A7 |
| Description |
FT054 |
A20 |
| Amount (16,31) |
FT605 |
$16 (12.2-) |
| Document Date |
FT050 |
A4 (MMDD) |
| Batch Reference |
FT703 |
A6 |
| Batch Date |
FT704 |
D8 (MM/DD/YY) |
| Operator ID |
FT708 |
A4 |
| Reference # 2 |
FT060 |
A7 |
| Identification (Ref # 3) |
FT067 |
A11 |
| Debit/Credit Indicator |
FT058 |
A1 |
| CPU Date |
FT718 |
D8 (MM/DD/YY) |
| Debit Account |
FT042 |
A6 |
| Debit Subcode |
FT046 |
A4 |
| Credit Account |
FT713 |
A6 |
| Credit Subcode |
FT716 |
A4 |
| Extract Date (8,23) |
date |
D8 (MM/DD/YY) |
| Division |
FG140/FS048 |
A2 |
| College |
FG142/FS050 |
A2 |
| Department |
FG144/FS052 |
A4 |
| Vice President |
FG138/FS046 |
A2 |
| Fund Group |
FG148/FS066 |
A2 |
| Purpose |
FG156/FS082 |
A2 |
| Security |
FG500/FS506 |
A6 |
| Bank Number |
FT739 |
A2 |
| User Area |
FT741 |
A15 |
| Additional Description |
FT743 |
A44 |
|
218 |
| Notes: |
|
| Each transaction has a transaction or entry
code. |
|
| 02* |
Budget Entries |
05* |
Encumbrances |
| 03* |
Cash Receipts |
06* |
Journal Entries |
| 04* |
Cash Disbursements/Payments |
09* |
Balance Forwards |
|
|
| In many instances, 02*-Budget and 05*-Encumbrance
entries should be excluded from your query. |
|
| SUBCODES_00 |
Subcode Tables |
| Ledger (G or S) |
NA |
A1 |
| Subcode |
FG914/FS914 |
A4 |
| Title |
FG506/FS940 |
A20 |
|
25 |
| Continue |
