SCT Banner ODS OPERATIONAL DATA STORE Presented by Penney Doughtie revised 1/31/2013

1. SCT Banner ODSOPERATIONAL DATA STOREPresented byPenney Doughtierevised 1/31/2013

2. What is ODS? OPERATIONAL DATA STORAGE • SCT Banner Product for Reporting • Retrieves production data • Inquiry only, no update

3. 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.

4. How Do I Get the Software I Need? • Create an Online Request ticket at http://www.ecu.edu/cs-itcs/ithelpdesk/ • Request PC installation of ODBC drivers for “REPT” • This is no charge to the department

5. 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

6. 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

7. 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

8. ODS Quick Queries located on the Systems Coordination website (www.ecu.edu/syscoord) • Banner Vendor History Search • Banner Journal Voucher Search • Banner Operating Balances (with Pool Account Subtotals) • Banner Revenue/Expense Transactions • Banner Encumbrance Summary • Payroll History • Banner Cash Balances by Orgn (for Foundations and Special Funds) • Banner Account Description

9. 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

10. Click “Enable Content”, beside Security Warning

11. Enter the Banner Vendor Number and Fiscal YearTab to cell A9, then click REFRESH

12. Service name will default to REPT, enter PirateID in the “User Name” field , enter REPT password. Click OK.

13. Query Results

14. Go to the Systems Coordination website (www.ecu.edu/syscoord) • Scroll down to the Quick Query List • Click “Banner Journal Voucher Search” • Click “Enable Content”, beside Security Warning

15. Click “Enable Content”, beside Security Warning

16. Enter the Journal Voucher Number, Tab to cell A7, click REFRESH

17. Service name will default to REPT, enter PirateID in the “User Name” field, enter REPT password. Click OK.

18. Query Results

19. Go to the Systems Coordination website (www.ecu.edu/syscoord) • Scroll down to the Quick Query List • Click “Banner Operating Ledger Balances” (with Pool Account Subtotals) • Click “Enable Content”, beside Security Warning

20. Click “Enable Content”, beside Security Warning

21. Enter Fund, Orgn, Program, Fiscal Year and Calendar Month, Tab to cell A9,click REFRESH

22. Service name will default to REPT, enter PirateID in the “User Name” field, enter REPT password. Click OK.

23. Query Results

24. Go to the Systems Coordination website (www.ecu.edu/syscoord) • Scroll down to the Quick Query List • Click “Banner Revenue/Expense Transactions” • Click “Enable Content”, beside Security Warning

25. Click “Enable Content”, beside Security Warning

26. Enter Fund, Orgn, Program, Beg Transaction Date, End Transaction Date, Fiscal Year, Tab to cell A9,click REFRESH

27. Service name will default to REPT, enter in the “User Name” field, enter REPT password. Click OK.

28. Query Results

29. 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.

30. 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

31. Click “Enable Content”, beside Security Warning

32. Enter Fund, Orgn, Tab to cell A9, click DATA

33. Service name will default to REPT, enter in the “User Name” field, enter REPT password. Click OK.

34. Query Results

35. 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 OneStopSelect “PORT”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.

36. 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 !

37. 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.

38. 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.

39. 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 doughtiep@ecu.edu or 328-2706.

40. Helpful Rule Codes(also called Transaction Type)

41. BANNER OPERATING BUDGET ACCOUNTS Also known as Budget Pool Accounts 72000 Supplies Budget 72350 Library Books Budget 72400 Equipment Budget 73000 Contractual Services Budget 73200 Travel Budget 73400 Current Services Budget 73600 Fixed Charges Budget 73800 Undistributed Budget 73950 Grants and Aides 74060 Education Awards 75000 Utilities Budget BANNER SALARY BUDGET ACCOUNTS 60000 Miscellaneous Salary Budget 60100 EPA Non Teaching Salary Budget 60150 EPA Faculty Salary Budget 60200 SPA Staff Salary Budget 60250 SPA LEO Salary Budget 61200 Benefits Budget

42. Websites for additionalBanner Information • www.ecu.edu/syscoord/ • www.ecu.edu/sctbanner/index_news.cfm