Transcript Banner ODS Training Manual

SCT Banner ODS
OPERATIONAL DATA
STORE
Presented by
Penney Doughtie
revised 1/29/2016
1
What is ODS?
OPERATIONAL DATA STORAGE



SCT Banner Product for Reporting
Retrieves production data
Inquiry only, no update
2
How Do I Get Security Access?
•
•
•
•
Security access to Banner Finance will
provide access to ODS.
User Name = PirateID
Password = is a random number issued by
ITCS.
An automated email will be sent to each
user when Banner Finance security access
is granted and the temporary password is
included.
3
How Do I Get the Software I Need?
•
•
•
Create an Online Request ticket at
https://ithelp.ecu.edu/
Request PC installation of ODBC drivers for
“REPT”
This is no charge to the department
4
What data is available?
Transaction Detail History
• General ledger balance data
• Operating ledger balance data
• Encumbrance Summary
• Finance data starting 07/01/1995
thru last night
•Payroll History
•
5
ODS Finance Data








Banner Finance Data
July 1, 2005 to present
Database = REPT
Owner= ECU
Table = FS_Transactions_with Check
FRS Finance Data
July 1, 1995 to January 31, 2006
Database = Rept
Owner = ECUHIST
Table = FRS_Transactions
6
ODS Human Resources Data









FRS HR Data
July 1, 1995 to January 31, 2006
Database = Rept
Owner = ECUHIST
Table = FRS_Payroll_Trans
******Missing payroll data from February 1, 2006 to June 30, 2007 can be requested by
contacting Systems Coordination at 328-2706. This data timeframe is located on SBPD
instance in ECUHISTORY.PY_HIST_BASE by payroll check date and SSN, this is not
included in ODS security.
Banner HR Data
July 1, 2007 to present
Database = Rept
Owner = ECU
Table = PY_PAYROLL_HISTORY
Use transaction date
7
ODS Quick Queries located on the Systems Coordination
website (www.ecu.edu/syscoord)








Banner Vendor History Search
Document Search
Operating Ledger Balances (with Pool Account Subtotals)
Transactions
Banner Encumbrance Summary
Payroll History
Banner Cash Balances by Orgn (for Foundations and Special Funds)
Banner Account Description
8
Go to the Systems Coordination
website (www.ecu.edu/syscoord)



Scroll down to the Quick Query List
Click “Banner Vendor History Search”
Click “Enable Content”, beside Security
Warning
9
Click “Enable Content”, beside Security Warning
10
Enter the Banner Vendor Number and Fiscal Year
Tab to cell A9, then click REFRESH
11
Service name will default to REPT, enter PirateID
in the “User Name” field , enter REPT password.
Click OK.
12
Query Results
13
Go to the Systems Coordination
website (www.ecu.edu/syscoord)



Scroll down to the Quick Query List
Click “Document Search”
Click “Enable Content”
14
Click “Enable Content”, beside Security Warning
15
Enter the Document Number,
Tab to cell A7, click REFRESH
16
Service name will default to REPT, enter PirateID in the
“User Name” field, enter REPT password. Click OK.
17
Query Results
18
Go to the Systems Coordination
website (www.ecu.edu/syscoord)



Scroll down to the Quick Query List
Click “Operating Ledger Balances”
(with Pool Account Subtotals)
Click “Enable Content”, beside Security
Warning
19
Click “Enable Content”, beside Security Warning
20
Enter Fund, Orgn, Program, Fiscal Year and
Calendar Month, Tab to cell A9,
click REFRESH
21
Service name will default to REPT, enter PirateID in the
“User Name” field, enter REPT password. Click OK.
22
Query Results
23
Go to the Systems Coordination
website (www.ecu.edu/syscoord)








Scroll down to the Quick Query List
Click “Transactions”
Click “Enable Content
Enter Fiscal Year
Enter Fund or % for all Funds
Enter Organization or % for all Funds
Enter Effect Cash (Y, B, E, or %)
 Y = Revenue & Expense Transactions
 B = Budget Transactions
 E = Encumbrance Transactions
 % = All Transactions
Enter Beginning Account Number and Ending Account Number
24
Click “Enable Content”, beside Security Warning
25
Enter Fund, Orgn, Program, Beg Transaction Date,
End Transaction Date, Fiscal Year, Tab to cell A9,
click REFRESH
26
Service name will default to REPT, enter in the
“User Name” field, enter REPT password. Click OK.
27
Query Results
28
Format the Data
Once the data is returned, it can be
reformatted. For example…in the Banner
Vendor History Search Quick Query




Click Review
Click Unprotect Worksheet
Click the drop down in the desired column heading
A pop up window will have options, like sorting or
selecting the elements of the FOAPAL.
29
Go to the Systems Coordination
website (www.ecu.edu/syscoord)



Scroll down to the Quick Query List
Click “Banner Encumbrance Summary”
Click “Enable Content”, beside Security
Warning
30
Click “Enable Content”, beside Security Warning
31
Enter Fund, Orgn, Tab to cell A9, click DATA
32
Service name will default to REPT, enter in the
“User Name” field, enter REPT password. Click OK.
33
Query Results
34
Closely review all open encumbrances to determine if a manual
liquidation is needed in PORT.
Contact Materials Management to request “manual liquidation” in PORT
and Banner Finance.
Log onto Pirate Port
Select “PORT – Purchasing Request”
Select desired Purchase Order under “Action Items”
Click “Available Action” (on the top right)
Select “Add a Comment”
Click “Go”
Type an explanation in the comment box including the reason why
manual liquidation is needed.
Click “Add Comment”
You may also call Materials Management at 328-6434 or Systems
Coordination at 328-2706.
35
Create a customized query










Open Microsoft Excel
Click “Data”
Click “Get External Data”
Click “From Other Sources”
Click “From Microsoft Query”
In the “Choose Data Source” window, scroll down and
select “REPT*”
Click “OK”
Type PirateID in the User Name, type REPT password.
Click OK
Congratulations – You are now in ODS !
36
Select the desired table






Click Option
Select “ECU” as the Owner for Banner Data
Select “ECUHIST” as the Owner for FRS Data
Click “OK”
Select the desired table (FS_TRANSACTION_WITH_CHECK)
The Query Wizard will then lead you through the
development of a query.
37
Helpful Tips

Use Effects Cash as a Filter
Y=Receipt and Expense transactions
B=Budget and Encumbrances transactions



Use Fiscal Year as a Filter
Use FOAP as a Filter
Account number greater than 49999, this
will exclude the Assets and Liabilities that
regular users don’t need.
38
To Request Security Access
The following website provides detailed
instructions on how to request Banner
Finance Security Access.
http://www.ecu.edu/cs-admin/financial_serv/Securityindex.cfm
If you have questions regarding this
security form, please contact Penney
Doughtie [email protected]
or 328-2706.
39
Helpful Rule Codes
(also called Transaction Type)
Rule Code
ADEI
BD01
B22
CORD
D44
E090
HGRB
HGRS
INEI
INNI
J51
J63
PORD
R30
SI1
SI3
SI5
XYZ
Description
Additional charges on Invoice
Original Budget
Temporary budget transfer
Purchase Order Change
Disbursement adjustment
Year End Encumbrance Accrual
HR Benefit Transactions
HR Salary Payroll Transactions
Invoice with encumbrance (purchase order)
Invoice without encumbrance (no purchase order)
Internal Sales (ECU departments billings within ECU
ID Charge / Journal Entry
Purchase Order
Cash Receipt
Medical Supplies Storeroom
Office Supplies Storeroom
Maintenance Supplies Storeroom
Miscellaneous Cash Receipt
40
Also known as Budget Pool Accounts
72000
72350
72400
73000
73200
73400
73600
73800
73950
74060
75000
Supplies Budget
Library Books Budget
Equipment Budget
Contractual Services Budget
Travel Budget
Current Services Budget
Fixed Charges Budget
Undistributed Budget
Grants and Aides
Education Awards
Utilities Budget
BANNER SALARY BUDGET ACCOUNTS
60000
Budget
60100
Budget
60150
60200
60250
Miscellaneous Salary
EPA Non Teaching Salary
EPA Faculty Salary Budget
SPA Staff Salary Budget
SPA LEO Salary Budget
41
Websites for additional
Banner Information

www.ecu.edu/syscoord/
 www.ecu.edu/sctbanner/index_news.cfm
42