Revision History Revision Date Description Approved Date July 27 to August 3 Distribution of ToC to Advisory and Core Team for review and revision as per Deliverable Acceptance Process July 31 Walk through draft with City PM Aug 3 Feedback from and walkthrough with PMT August 7 to 14 Project Costing Confirmation of Current State Report v SEA 2.0 distributed to Core Team and FinMAP Advisory Group for Support Deliverable Review Aug 14 Reminder to provide edits and comments and edits provided at Core Team meeting Aug 15 Status update to FinMAP Advisory Group Aug 15 -27 Comments and to Project Costing Confirmation of Current State Report by Core Team were received from CBO, DoIT, HSD, FAS, DPD, SCL, SDoT and SUMMIT by August 17. Follow up emails were sent to DPR and SPU (comments received from SPU Aug 27) Aug 27 to Sept 4 Project Costing Confirmation of Current State Report SEA v 3.0 distributed for Final sign-off by FinMAP Advisory Group Sept 5-12, 2012 Comments and to Project costing Confirmation of Current State Report by FinMAP Advisory Group were received from CBO, DoIT, HSD, DPD, SCL, SUMMIT, SPU and DPR Sept 11, 2012 Project Costing Confirmation of Current State Report v SEA 3.0 distributed to City PM for review Sept 13 – 20, 2012 Project Costing Confirmation of Current State Report v SEA 4.0 distributed to FinMAP Advisory Group for final review and sign-off Executive Summary The world we live in has changed dramatically from the time when the original designs for financial information systems were created. Related business processes appropriate for those systems built in the 1970’s and 1980’s have also changed. Where previously mainframe systems were state of the art
The Activity ChartField is the basic building block for this custom functionality. This field drives the entire customization. To use the Activity ChartField correctly within PeopleSoft PC functionality requires significant restructuring with a high level of complexity. The current structure limits the system’s functionality with regard to Activity Billing preventing the implementation of the Grants and Contracts modules. See the Recommendations in the APR Report under 4.1.2 PC ChartFields and 4.3 Project Cost Customizations MOD #34. The logic of the PeopleSoft Financials system in its entirety is driven by the ChartFields, SETIDs and Control tables. The implications are: The core configuration for SETIDs in SUMMIT needs to be expanded to five positions from three positions. After version PeopleSoft v7.5, a five position SETID was required due to the processing and data issues identified in previous versions. The SETID is the major key for the PeopleSoft system and required in most of the tables as well as processes. This conversion process would impact the majority of the tables in the financial system (from five to six thousand tables). ChartFields have not been used consistently with the same definitions as discussed in the various APR reports. The delivered ChartFields within an application have not been used consistently across the modules as identified the APR and Tucker reports. Control tables have not been configured for the City-wide requirements. For example, Locations is a control table that needs to contain the values for the entire City and all the business units/departments that use this table as part of their functionality and process requirements. Changes to minor modifications identified in the previous reports would require significant analysis to identify downstream process impacts. Any modification of ChartField configurations in the current SUMMIT system will also create a cascading effect on the current customizations from a processing perspective. A number of impacts are identified below: The Time Entry system uses the Activity ChartField instead of Project ChartField to report time. Changing the logic to project would have an impact to the Labor Distribution process from HRIS and the SUMMIT PC customizations contained on the five custom pages. These five pages drive the current interfaces to the Billing application and Revenue Recognition process. It should be noted that some departments, such as HSD, use the Project, Activity, and Org ChartFields as designed; however, they are not in the majority of users. Changing from over 200 Project Trees to one Project Tree with branches would cause the rewriting of the Project- based reports. This impacts the analysis, development and testing of reports and online reporting.
Department Attendees 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Total Department of Information Technology (DoIT) Patti DeFazio 1 1 Jan Tonning 1 1 Mitz Barber 1 1 1 1 1 1 1 1 1 1 1 1 12 Brandon Ha 1 1 2 Fati Le 1 1 1 1 4 Alice Lawson 1 1 1 1 1 1 6 Ming Leung 1 1 1 1 1 1 1 7 Corrie Martin 0 Truong Tran 1 1 1 3 Amy Widman 1 1 1 3 Department of Parks and Recreation (DPR) Gerald Asp 1 1 2 Marc Balatico 1 1 1 1 1 1 1 7 Dede Cabacungan 1 1 1 3 Nina Chan 1 1 Ayele Dagne 1 1 1 3 Shauna Fitzner 1 1 Joan Hirata 1 1 Doug Patton 1 1 2 Teresa Siegel 1 1 Roche 1 1 Whang 1 1 Department of Planning & Development (DPD) Sheila Chavez 1 1 1 1 1 1 1 7 Denise Campbell 1 1 2
DoIT Systems Name of System Type of System Type of Software or Program Interfaces and Integration Points with Summit Facility Center Database Work order & inventory7 system; tracks inventory purchases and uses; maintain inventory records used by Communication Technologies Group Tririga now purchased by IBM This System and its applications are used to track costs, revenue & activities. Together with Time Timekeeper, Pinnacle and Facility Center, these systems integrate to DoIT ABD where Activity Billing is based on. Also called the TMS Billing system which interfaces with SUMMIT via I03. Uploads data and download costs. Also used to create Journal Entries for Inventory to the General Ledger. TIMEKEEPER Part of the TMS billing system that tracks labor hours by project. Labor Costs are calculated at billing rate. In-house web application that reads current work order numbers from facilities center and allows techs to enter billable time to the work order Together with Facility Center, Pinnacle and Conference Bridge these systems integrates to DoIT ABD where Activity Billing is based on. Also called the TMS Billing system which interfaces with SUMMIT via I03 upload. CONFERENCE BRIDGE Track International conference calling in house application Together with Facility Center, Pinnacle and Conference Bridge these systems integrates to DoIT ABD where Activity Billing is based on. Also called the TMS Billing system which Interfaces with SUMMIT via I03 upload. PINNACLE Keeps track of recurring charges of Windstream Together with Time Keeper, Pinnacle and Conference Bridge these systems integrates to DoIT ABD where Activity Billing is based on. Also
VIMS Tracking the insurance status of taxicab operating within the City Payment information to SUMMIT via STORM (to be confirmed) WORKS City’s credit card and Epayable system (Bank of America) Payment information to SUMMIT (direct interface) Parks Page 1 Application Name Description Frequency Batch or Real Time Auto or Manual Tables Used Language Misc. Info Summit Interface Expenditure and Revenue Budget Download budget data from SUMMIT Budget Module and upload to GL Annually Batch Manual PS_JRNL_LN_BUDG PeopleTools Queries and Excel ODBC Capital Improvement Project System Download data from Rainier database to SQL Server and then forward to CIP.mdb, CIPEXPREV.mdb and CIPSUM.mdb Month-End Real Time Auto PS_PROJ_RESOURCE SQL Server MS Access 52 Crystal reports are generated by using this system data. It also provides data information to feed the CIP Monitor System. It provides detailed financial information to CIP managers, Sr. financial analysts and unit supervisors for their decision making regarding budget/expenditure/revenue projections and the potential financial status of the Department. ODBC
Parks Expenditure and Revenue System Download data from Rainier database to APPO.mdb, EXPREV.mdb, EXPSUM.mdb, REVENUE.mdb and SSRS.mdb Month-End Real Time Auto PS_PROJ_RESOURCE, PS_JRNL_LN_BUDG, PS_JRNL_LN, PS_LEDGER, PS_PO_LINE_ACCTG, PS_PAYMENT_TBL, PS_PYMNT_VCHR_XREF, PS_PYMNT_XREF_VW, PS_VCHR_ACCTG_LINE and PS_ZPO_ENC_SUMMARY SQL Server and MS Access 99 Crystal reports are generated by using this system data. It provides detailed financial information to directors, project managers, executive assistants, financial analysts and unit supervisors for their decision making regarding budget/expenditure/revenue projections and the potential financial status of the Department. ODBC Parks - Page 2 Application Name Description Frequency Batch or Real Time Auto or Manual Tables Used Language Misc. Info Summit Interface Expenditures and Revenues Views Download actual transactions (PC2005) from Rainier database to Parks SQL Server Real Time Manual/DTS PS_PROJ_RESOURCE T-SQL in SQL Server The directors, managers, Strategic Advisors, Sr. Financial Analysts and Unit Supervisors use the Expenditure and Revenue to create the Pivot tables for their management reports ODBC Budget Download budget transactions (GLBud2005) from Rainier database to Parks SQL Server Month-End Real Time Manual/DTS PS_JRNL_LN_BUDG T-SQL in SQL Server The directors, managers, Strategic Advisors, Sr. Financial Analysts and Unit Supervisors use the Budget views to create the ODBC
Pivot tables for their management reports Encumbrance Download encumbrance transactions (PCCom2005) from Rainier database to Parks SQL Server Month-End Real Time Manual/DTS PS_PROJ_RESOURCE T-SQL in SQL Server The directors, managers, Strategic Advisors, Sr. Financial Analysts and Unit Supervisors use the Encumbrance views to create the Pivot tables for their management reports ODBC Parks – Page 3 Application Name Description Frequency Batch or Real Time Auto or Manual Tables Used Language Misc. Info Summit Interface Personnel views Download personnel transactions (Personnel2005) from Rainier database to Parks SQL Server Month-End Real Time 8 PS_XDS_LBR_DTL T-SQL in SQL Server The directors, managers, Strategic Advisors, Sr. Financial Analysts and Unit Supervisors use the Personnel ODBC
views to create the Pivot tables for their management reports Report Trees Five reports trees and PeopleTools queries being created and Nvision is using to download the report tree information for organization rollup. Month-End Real Time Manual PSTREELEAF, PSTREENODE, TREE_NODE_TBL and DEPARTMENT_TBL PeopleTools Queries For all the repot rollup Nvision Proparks Reimbursement Journal Upload Occasionally Online Manual Excel Coding Correction Journal Upload Very Often Online Manual Excel Payroll Adjustment Journal Upload Bi-weekly Online Manual Data is extracted from MS Access database – Page Four Application Name Description Frequency or Real Time Auto or Manual Tables Used Language Misc. Info Summit Interface Telephone Billing Journal Upload Monthly Batch Manual Excel Utility Billing Journal Upload Three Times A Month Batch Manual Excel Data is extracted from MS Access database (SCLSPU2012.mdb).
Activity Transaction Report Download All expenditure data for the specified time period is downloaded from SUMMIT to support Finance analysis Finance Reporting Yes Excess Cost Download Data download from SUMMIT to support the Excess Cost Report. Fin/Acct Reporting Yes Excess Cost Download (LTD-CIP) Data download from SUMMIT to support the Excess Cost (LTD-CIP) Report. Finance Reporting Yes Excess Cost Download (LTD - O&M) Data download from SUMMIT to support the Excess Cost (LTD-O&M) Report. Finance Reporting Yes Financial Data Download Finance data is downloaded from SUMMIT for SDOT Finance after month-end close. Finance Reporting Yes Financial Data Download (Street Maintenance) Finance data (SM-tailored) is downloaded from SUMMIT for Street Maintenance as needed. SM Reporting Yes General Ledger Download years of GL data is downloaded from SUMMIT to support Finance analysis Finance Reporting Yes Resp Division Download Data download from SUMMIT to support the Responsible Division Report. all SDoT Reporting Yes Resp Division Report Real-time inquiry to support 4 detail on the Responsible Division Report. all SDoT Reporting Yes Unbilled Balance Download Data download from Summit to support the Unbilled Balance Report. Acct Reporting Yes SDOT Interfaces – Page 3 Type of Interface* Interfaces Description Business Supports Function Data Download Data View Prep for FTP Upload Prep for SUMMIT Upload
Interfaces Description Business Group Supports Function Data Download Data View Prep for FTP Upload Prep for SUMMIT Upload Utility Cut Restoration & Paving Billing Billing data to create invoices for Utility Cut Restoration work is uploaded to SUMMIT. Invoice details are retrieved from SUMMIT after invoices are created. A/R Invoicing Yes Yes Utility Cut Restoration & Paving Journal Upload Journal file to post allocation file for utility cut services is prepared and sent via FTP to SUMMIT. A/R Billing Yes Finance Ad-hoc Reports Various ad-hoc data requests are satisfied by pulling data from SUMMIT (STHELEN) into Access or Excel Finance Reporting Yes SDOT Uploads – Page 1 Type of Upload Upload Description I11 Journal I27 Billing Activity Create Activity Status Key Assign Vouchers