SCT Banner ODS OPERATIONAL DATA STORE Presented by Penney Doughtie revised 1/31/2013 - PowerPoint PPT Presentation

sct banner ods operational data store presented by penney doughtie revised 1 31 2013 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SCT Banner ODS OPERATIONAL DATA STORE Presented by Penney Doughtie revised 1/31/2013 PowerPoint Presentation
Download Presentation
SCT Banner ODS OPERATIONAL DATA STORE Presented by Penney Doughtie revised 1/31/2013

play fullscreen
1 / 42
SCT Banner ODS OPERATIONAL DATA STORE Presented by Penney Doughtie revised 1/31/2013
346 Views
Download Presentation
pakuna
Download Presentation

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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