State of Connecticut

1. State of Connecticut Project Costing and Customer Contracts

2. Table of Contents • 1. PC/CA Overview Page 3 • 2. Creating a Project Page 6 • 3. Integration Page 22 • 4. Commitment Control Page 35 • 5. Custom Project Upload Page 39 • 6. eProcurement Page 43 • 7. Purchasing Page 47 • 8. Accounts Payable Page 51 • 9. Asset Management Page 54 • General Ledger Page 55 • HRMS Page 59 • Allocations Page 62 • Customer Contracts Page 68 • Billing Page 80 • Accounts Receivable Page 86 • Additional Information Page 90

3. Project Costing - PC • In Project Costing, a project is an identifier for cost collection and for monitoring the progress and status for a product or service. • Detail Projects include Activities. • Activities are specific tasks associated to a Project where transactional data gets recorded and monitored (i.e. contractual expenses, incidental costs). • Summary Projects do not include Activities and are used for grouping Detail Projects to identify project relationships for reporting and tracking purposes. • Template Projects, which are created during an agency implementation, are reusable to populate new Detail Projects with preset standard information such as Project Type, Project Status and Activities. Templates reduce redundant keyboard entry for common project attributes.

4. Customer Contracts - CA • Customer Contracts represent an agreement between a state agency and the customer that they are billing, which could be another State agency, a Federal agency, or a third party. • Customer Contracts come in two classifications: Standard and Government. The classification type determines the degree of functionality available. The main difference between a Government and a Standard classification is the Government classification allows for the entry of awarded and funded amounts, and Standard does not.

5. Security • Based on permission lists attached to roles and the requirements of agencies, Core-CT created multiple roles to access specific pages and functionality. • For a list of PC and CA roles and access, see the Role Handbook for Financials: http://www.core-ct.state.ct.us/ > Agency Security Liaison> Role Handbook for Financials • Core-CT utilizes Integration Broker - Application Messaging. CT_WEB_SERVICES, a custom Permission List, was created to include delivered and custom Service Operations entered on the Web Services page. This Permission List is necessary to allow transactions created by a user to get messaged to other applications (i.e. FN to HR).

6. Project Costing: Creating a Project • With Project Costing, Core-CT allows users to set up new Project IDs. These are available upon creation, unlike other new Chartfield values which must be submitted centrally for entry. • Core-CT has users create new Project IDs from templates to eliminate the entry of redundant data and for ease of use. • Many of the project pages are optional. • To Add/Find an Existing Project navigate to: Project Costing> Project Definitions> General Information

7. Create Detail Project

8. Create Detail Project • Agencies select predefined Project Templates to create Detail Projects. • The Start Date is used to trigger multiple effective dates within the new project.

9. General Information Page • The Description, Integration, Project Type, and Project Status fields default from the Project Template. • The Description field default will need to be overwritten with the title for the Project ID. • The Start Date defaults from the New Start Date field from the Create Project From Template page. • The End Date is calculated based on the time-span on the template.

10. General Information Page • Edits to the page are made by user to data defaulted from template.

11. Project Activities • This Activity defaulted from the template. Additional Activities may be added to the Project. • The Start Date defaulted from New Start Date field from the Create Project From Template page. The End Date defaults from the calculated time span on the template.

12. Project Costing Definition • Page access given for Project Utilities and Enforce Team functionality.

13. Customization: Enforce Team Functionality • If Enforce Team is set to Do Not Enforce – Team Members associated to the project are for reporting purposes only. • If Enforce Team is set to Project Team Only – Only the Team Members associated to the project will be allowed to enter a combo code to this project on their timesheet. • Effective dates are very important in Enforce Team functionality. For specific information see the Team Member Functionality job aid: http://www.core-ct.state.ct.us/ Financials User> Project Costing> Project Costing Job Aids> Team Member Functionality • Combo codes are customized to allow Chartfield 1 to be an Activity for Project Costing using agencies, as the Activity Chartfield is missing from the combo build process. • Application Messaging was modified to allow for the Enforce Team Effective Date and Team Scheduling dates to be captured in Human Resources.

14. Project Team - Team Detail • Enter Email and select Email Notify for Status Change to have employee receive notification.

15. Project Team • Core-CT does not recommend attaching Employees to a Template. The ability to delete team members on the detail project has been removed so that prior period payroll adjustments are not compromised. Team members can be inactivated through the use of the End Date. • Once a detail project is created from a Template an employee can be added by entering their Employee ID. • Email notification for Status Changes (requires set up in the Project Type Status Path configuration), Email ID, and Email Notify for Status Change checked. • A Project Role is required. If Project Manager is selected as the role, that employee will display on the Manager page.

16. Manager • The Team Member with the Project Manager checked displays on the Manager page. • There can be only one Project Manager on a project at any given time. Based on the data below a new Manager can be assigned as of 07/01/2099.

17. Location • Location page is optional. Location Codes are configured as required by agencies. Users can enter or select a Location Code and upon Save, the Address information will default. Location is for reporting purposes only.

18. User Fields • The User Fields page is optional. User Fields are free form fields. Each agency can come up with their own mapping for what each field represents. User Fields are for reporting purposes only.

19. Rates • In Core-CT, a Project ID gets attached to a Customer Contract along with a Rate Set or Rate Plan. The Rates page is a view of that association. In this example, the project has not been attached to a Customer Contract.

20. Rates • An example of the Rates page where a Project has an associated Customer Contract.

21. Asset Integration Rules • Asset Integration Rules are required if an Agency is integrating the costs of a Project to an Asset. This is used for infrastructure-type assets, e.g., a Project is the construction of a new building that when completed will need to be captured in the Asset Management module.

22. Integration • The following slide shows Project Costing (PC) and Customer Contracts (CA) integration with other Core-CT modules. • Since PC and CA are integrated with other modules, errors in coding or amounts need to be corrected in the source modules (where possible) to correct the data for consistency/reconciliation throughout Core-CT. • The PROJ_RESOURCE table pulls in approximately 150 fields of detail from other modules. The transaction specifies which fields get populated.

23. Blue: Analysis Type Green: Batch process Purple: Application messages

24. Integration Broker Application Messaging • Integration Broker is a middleware technology that performs asynchronous and synchronous messaging among internal systems. These processes include defining services and service operations, messages, queues, nodes, routings, and transformations. • Services are used to logically group a set of service operations. • Service operations contain the processing logic for an integration and determine how the integration is to be processed (synchronously or asynchronously). • Messages provide the description of the data being sent, including fields and field lengths. Messages need to be set up exactly the same in HR as they are in FN.

25. Integration Broker Application Messaging • Queues are used to queue service operations for processing. • Node definitions define the locations to or from which messages can be routed. • Routings specify the direction of the integration (inbound or outbound). • Core-CT uses both single sync and full sync functionality. For instance, PROJECT_SYNC messages Project Record data to HR upon create/save of a project by the user. PROJECT_FULLSYNC is run centrally and truncates the HR PROJECT record and messages the entire FN PROJECT record data. • Some security set up is required to allow users to Message data.

26. Application Messages used by Core-CT Project Costing

27. Includes Version and Message Type The expanded record on the message displays the fields set up to sync over to HR. The checks represent the fields that will be sent. Message Setup for PROJECT_SYNC

28. Service Operation Setup for PROJECT_SYNC • General tab includes: Operation Type, Version, Status and Queue Name • Routings tab includes: Sender Node, Receiver Node, Direction and Status

29. This queue holds all of the Project related applicationmessages. Queue Setup for PROJECTS_SETUP

30. Commonly Used Analysis Types • Analysis Types identify different types of transactions, such as budget amounts, actual costs, and billed costs. • Navigation: Set Up Financials/Supply Chain> Product Related> Project Costing> Transaction Options> Analysis Types

31. Analysis Groups • Analysis Groups are used to group analysis types and define the relationships among them in order to analyze project costs. For example, they can be used to calculate the total budgeted cost of a project in relation to the total actual costs. • Analysis Groups are key configuration items used for reporting and batch processing. • It is important to understand the sign (+ or -) of the amounts that are populated on PROJ_RESOURCE to know and understand how to set up multipliers on an Analysis Group. • Navigation: Set Up Financials/Supply Chain> Product Related> Project Costing>Transaction Options> Analysis Groups

32. Analysis Groups • This example demonstrates how the Analysis Group CTENC (Encumbrance) is determined. • The Analysis Type COM (PO Encumbrance) minus the Analysis Type CRV (Commitment Reversal or Voucher payment) minus the Analysis Type CCA (Commitment Adjustments or PO Closed balance of Commitment) equals the Encumbrance (Analysis Group CTENC). • A PO is created for $100; a voucher is created from the PO Receipt for $75; later the PO is closed. COM= $100, ACT= $75, CRV= $75, CCA= $25. Based on the CTENC Analysis Group Multipliers the encumbrance balance is:minus $25 (CCA) plus $100 (COM) minus $75 (CRV) = $0*** Note the Analysis Group Options: Perform Multiplication

33. Using Analysis Groups in Reporting • The Core-CT created report Project Data by Analysis Group allows the user to report on up to six analysis groups, any chartfield, and different date ranges for projects. • Navigation: Project Costing> Core-CT Reports> Project Data by Analysis Group

34. Project Costing Global Requirements for Integration All transactional data from feeder systems (Budgets, Requisitions, Purchase Orders, Vouchers, Journals, Timesheets and Payroll) must have the proper Project Costing chartfield coding for the data to flow into the Project Costing module. • PC Business Unit • Project ID • Activity ID

35. Commitment Control - KK • Project Costing has three budget structures: • PC1 – Project level budget • PC2 – Project, Fund, and Special Identification Code (SID) level budget • PC3 – Project, Activity, Fund, and SID level budget • Budget journals for Project Costing are posted using the KK_PC3 ledger group. Commitment Control creates entries for the Parent budgets, PC1 and PC2, from the PC3 entry. Each level can have its own Budget Control Option. • Users post their own agency level budgets. The Office of the State Comptroller (OSC) posts the legal level budgets. There are no system ties between the legal and agency level budgets. • The financial transaction will be passed to the Project Costing module with the Analysis Type of BD1 (Total Cost Budget). • To Add/Find an Existing Budget Journal navigate to: Commitment Control> Budget Journals> Enter Budget Journals

36. Budget Journal Header • Budget journals for Project Costing are posted using the KK_PC3 ledger group.

37. Budget Journal Lines **** Note: This budget journal has been coded to include PC Business Unit, Project ID and Activity; prerequisites for inclusion in the Project Costing Module.

38. Budget Journal Lines • Once the Project Budget is posted in Commitment Control, a nightly batch process will run the interface from Commitment Control to Project Costing PC_KK_TO_PC). This process will create the budget transactions (BD1) on the PROJ_RESOURCE table.

39. Custom Project Upload • The Custom Project Upload tool is an Excel template designed to add data that does not get posted to the General Ledger but is available in PROJ_RESOURCE for reporting (i.e., the analysis type EST - estimated budgets). • Core-CT created a custom analysis group of UPALL under each agency’s SETID. Only the analysis types within that group are allowed to be used with this tool. • Agency users load the Excel files to staging tables.

40. PC Upload ToolMain page

41. PC Upload ToolDetails • Chartfield validations are built into the load process so that erroneous data cannot get loaded • This data gets loaded to the INTFC_PROJ_RES staging table.

42. PC Upload Tool • Once the data is in the staging table; a 10am, 12pm, 2pm and overnight batch process will run the interface to load data to Project Costing (PC_INTFEDIT). This process will create the transactions on the PROJ_RESOURCE table.

43. eProcurement - Requisitions • Requisitions are used by most agencies when ordering commodities through the Catalog, or as an internal business practice for that agency. • The requisition, once budget checked, reduces the available budget in the form of a pre-encumbrance. • The financial transaction will be passed to the Project Costing module with the Analysis Type of REQ. • To Add a Requisition navigate to: eProcurement> Create Requisitions • To Find an existing requisition navigate to: eProcurement> Manage Requisitions

44. Requisition Line Details • All requisitions source to Purchase Orders. This screen shot displays the Manage Requisitions page after the requisition has sourced to a Purchase Order.

45. Requisition Schedule & Distribution **** Note: This requisition has been coded to include PC Business Unit, Project ID and Activity; prerequisites for inclusion in the Project Costing Module.

46. PC_PO_TO_PC • Once the Requisition is approved and budget checked valid, a nightly batch process will run the interface from Purchasing to Project Costing (PC_PO_TO_PC). This process will create the pre-encumbrance transaction (REQ) on the PROJ_RESOURCE table in the Project Costing module.

47. Purchasing - Purchase Orders • POs are created to procure items and services. • POs can be sourced from a requisition or created manually online. • The PO, once budget checked, reduces the available budget in the form of an encumbrance. • The financial transaction will be passed to the Project Costing module with the Analysis Type of COM - Commitment. • Reversals: When a purchase order is sourced from a requisition, the pre-encumbrance is reversed. The financial transaction will be passed to the Project Costing module with the Analysis Type of RRV – Requisition Reversal. • To Add/Find an Existing Purchase Order navigate to: Purchasing> Purchase Orders> Add/Update Pos

48. Purchase Order (PO) Header & Line

49. PO Distribution Line **** Note: This purchase order has been coded to include the PC Business Unit, Project ID and Activity; prerequisites for inclusion in the Project Costing Module.

50. PC_PO_TO_PC & PC_POADJUST • Once the PO is approved, budget checked valid, and dispatched, a nightly batch process will run the interface from Purchasing to Project Costing (PC_PO_TO_PC). This process will create the encumbrance transaction (COM) on the PROJ_RESOURCE table in the Project Costing module. • Subsequent to the PC_PO_TO_PC process, the PC_POADJUST process runs. This process will create the pre-encumbrance reversal transaction (RRV) on the PROJ_RESOURCE table.