WIXLIB

TXDOT PROJECT COSTING –PEOPLESOFT PROJECT SETUPSFinancial Management Division06/07/201806/07/2018

Table of contents1Flow Charts3-42Key Terms5-63Project Setups – General Information7-134Project Setups – Funds Distribution14-175Queries & Reports18-246Additional Activities & Error Messages25-277Contact Emails and Training Modules2806/07/20182

Partial Multi System Flow ChartSite ProjectCostingFAFOSFPAA and MPA(s)ProcessedFHWAFMISAutomatic Interface BetweenSystems/ModulesManual Load Between gPeopleSoftAccountsReceivableDCIS – Design Construction Information SystemFAFOS – Federal Aid Funding Obligation SystemFHWA FMIS – Fiscal Management Information SystemFAID – Finance Accounting Inactive Projects DatabaseFPAA – Federal Project Authorization and AgreementEstablishes initial obligation amounts forphase(s) of workMPA – Modified Project AgreementSubmitted to increase or decrease the federalobligation due to actual low bid, cost overruns orcost underruns, and final closeout06/07/20183

Project Set Up Overview06/07/20184

Key TermsKey TermDefinitionProject CostingBusiness Unit (PCBU)A business unit in Project Costing is defined as an operationalsubset of the organization. Ex: 60172, 60173, etc.CSJControl Section JobProject AKA “ProjectID”Project is the core project costing Chartfield that capturesinformation for project accounting. DD CSJ in 60172,60173, &60176. Ex: 02160101031(Source) ActivityProject component used to capture and collect costs for a specificphase of workDistributional ActivityDetermines how expenditures are funded for a given transaction.Ex: Federal, State, Local, or/and BondSource Analysis TypeAssigned to individual transactions to identify where thetransaction is coming from. For example: Transactions with an ACTare coming from the accounts payable module, transactions withGLE are coming from General Ledger, etc.Distributional AnalysisTypeDistinguishes the funding as federal, state, local, or bond. Ex: FDF,FDS, FDL, or FDB06/07/20185

Key TermsKey TermDefinitionFunds DistributionFunds Distribution is the process of distributing funding byapplying Funds Distribution rules to incoming transactions andassigning costs accordingly.Fund Source AKA“Resource Category”It enables you to know how the costs are being funded, whetherfederal, state, bond, or local. Ex: 0006, M001*The “Fund” field used to complete chartfields is a different field,but is often confused in conversation.Demo ID AKA“ResourceSubcategory”The Demo ID is a subcategory of a funding source that isclassified as earmarked. Ex: TX123Threshold Amount AKA“Obligation Amount”Funding limit dollar amount for a specific line or sequence offundingAFAAdvanced Funding Agreement06/07/20186

Business Unit OverviewProject setups in these business units are handled by the FIN Project Ledgers team06/07/20187

How Projects are Set-Up in PeopleSoft Project Costing The majority of projects are extracted from DCIS– State Letting Projects, Non-Letting Projects, and Local Letting Projects Usually setup in two phases– PE phase CSJ to PeopleSoft should only be requested when it’s ready for design work– Once a project ID is set up it cannot be reused Push to PeopleSoft is indicated by a date on the PE DCIS (P11) screen– Construction and CE phase Push to PeopleSoft indicated by an actual let date on the Project Info DCISScreen(P01) State Letting projects, set up process begins after monthly Letting– Projects are made active in PeopleSoft after Award by CommissionDCIS funding information directly impacts how projects are set up in PS and howexpenditures are distributed*Missing/incorrect DCIS information may delay project set up*06/07/20188

DCIS Inbound Interface Initiated by Letting Management Uses information from P01, P02, P2C, P2D, P03, P04, P05, P11 Three day/two night processThe Inbound interface from DCIS:Creates and updates Projects and ActivitiesCreates and updates Project AttributesCreates and updates Fund Distribution RulesCreates and updates Customer Contracts06/07/20189

DCIS P11 Screen – PE Phase PE push to PeopleSoft is initiated by the Letting Management coordinator assignedto that district Letting coordinator enters the date into the “PE Date” field and the interfaceprocess will begin that night06/07/201810

DCIS P01 Screen06/07/201811

Project Attributes Page DCIS is the source for allprojects in PCBU 60176– Project ID In PS, the project IDconsists of thedistrict/divisionfollowed by the CSJ(Ex: 02160101031)– P01 SCREEN: ProjectClass, AuthorityDescription, HighwayNumber, County, TxDOTproject number– P02 SCREEN: Low-BidAmount, Subreceipt/Vendor field– P03 SCREEN:Urban/Rural Code– P05: Primary CSJ06/07/201812

Project Attributes Page Some fields arederived from fields inDCIS and thenprogramming withinthe interface is usedto populate thefields in PeopleSoft.– Site ManagerContract No.– Primary CSJ– CE Percentage– ProrateFlag(Derived fromP05)06/07/201813

DCIS Interface- Funds Distribution “Waterfall” based onthe P02 and P2Cscreens Funding from P02screen will be used onactivities in thefollowing order:1) CNST2) CNSTF3) CE P02 screen MUSThave an open line offunding PSFT does nottrack Cat or Workprogram06/07/201814

Funds Distribution Contains funding rules& controls how projectspay out “Waterfall” funding isset up in FundsDistribution –throughthe use of Sequences Expenditures : Oncesequence 1 is fullyexhausted theremaining flows throughto sequence 2. Oncesequence 2 is fullyexhausted all remainingexpenses will flowthrough to catchsequence 3.Catch sequence indicated bytwelve 9’s06/07/201815

PSFT Total Project Cost – Calculating Threshold AmountsFor all construction CSJs that are let through Site Manager, Finance has additivepercentages that are added onto the low bid to come up with a more accurate estimateof what the project will cost.Rates based on project class, derived from historical data Contingency (CONT%) – Estimated amount of changes to bid items or work that fallbelow the threshold for creating a change order Potential Change Order (PCO%) – Estimated amount of changes orders that will likelybe approved and applied to the CSJ Construction Engineering (ENG%) – Estimated costs for engineering and time chargedby TxDOT during the construction phase06/07/201816

Summary of Project Setups DCIS houses important information on numerous screens that is used tosetup projects in Project Costing– Project attributes come from the P01, P02, P04, & P05 screens– Project fund distribution rules come from the P02 & P2C screens It is crucial for the information in DCIS to be accurate in order for the projectto be setup correctly06/07/201817

Running Queries Main Menu Reporting Tools Query Query Viewer Must have at least PROJECT COSTING (PC) READ ONLY user rights– Call 512-266-6328 or submit a TxDOTNOW ticket if you need this access06/07/201818

Running Queries To search for a specific query, enter all or part of the query name and click Search– Click ‘HTML’ link to view in a web browser or ‘Excel’ to pull it up in a spreadsheet06/07/201819

Running Queries Multiple parameter combinations may be used to run theX PC EXP BY PROJECT DETAIL query. Business Unit and Project ID were used here. Click View Results06/07/201820

Query: X PC EXP BY PROJECT DETAILSequence adds up to 100% Rate % for eachsequence addsup to 100%Catch sequence for activity CNST Each activityhas a “catch”sequence Analysis TypesAnalysis Types To re-distributeanyexpenditures aPeopleSoftadjustment isneeded06/07/201821

Common Analysis TypesAnalysis TypeDescriptionDescription of UseFDFFederal DistributionFederal expenditures after funds distribution processingFDAAdvanced ConstructionAdvanced construction expenditures after funds distributionprocessing, expenditures with this analysis type cannot bebilled to the federal governmentFDSState DistributionState expenditures after funds distribution processingFDBBond DistributionBond expenditures after funds distribution processingFDLLocal DistributionLocal expenditures after funds distribution processingTDCTDC DistributionTransportation development credits used as the match andbilled to FHWA. This occurs after funds distribution processingSPOSponsor ReimbursementDistributionTo account for the non-cash match for the local participantsINKIn‐Kind DistributionIn kind contribution expenditures after funds distributionprocessingL- -Last Distribution/CatchSequenceWhen federal funds are involved, it allows borrowing withinthe same fund source and same federal project numberComplete list can be found under the PeopleSoft References link on the FIN TN page06/07/201822

Common Queries & ReportsQuery/Report NameDescriptionX PC EXP BY PROJECT DETAILFunding & LTD expenditures at the project ID,federal project, TxDOT project number orSiteManager level.X PC PRIMARY SECONDARY CSJProvides all projects ID within a SiteManagercontract number. Lists the CE% and pro-rateflagX PC AH DIST PROJ LTDLife to date expenditure details such asaccount, voucher number and supplier IDX PC FED PROJ RESOURCE DETAILPulls entire Project Resource table transactionsfor a project ID, federal project number, TxDOTproject number, or SiteManager Contract No.X PC RECV SUMM QUERYProvides receivables summary details for localentities5202 ReportGeneral project expenditure details based onvarious search criteria06/07/201823

Common Navigations Queries & Reports X PC EXP BY PROJECT DETAIL Instructions: Common navigations: Expenditure 5202 Report Instructions:PDF FilePDF FileMicrosoft WordDocument06/07/201824

Additional Activities PE-ADM– Used for TxDOT time charges while fundingagreements are created– 100% state funded– Currently only for off-system bridge projects CNSTF-AAH– Submit Abatement/Asbestos RemovalRequest Form (resource type 225) Request form is on the TNtoday FIN Pageunder FIN Applications tab Notifies Ledgers and Letting Management– Any work that is done before the FPAA isauthorized would not be eligible for federalreimbursement CE-C– Redesign of plans Consultant EngineeringCoordinated with PEPS– Can only be setup if the project has gonethrough letting– Will be funded the same as PE06/07/201825

Error MessagesCheck that the correct PCBU, Project ID and source activity are being usedContact a FIN Project Ledger’s employee for assistance-Justin Lawson-Sarah BohuslavFor PE activity, please contact your Project Coordinator in Letting Management06/07/2018

Error MessagesBudget related error messages “SRCACT No Budget Exists”– the combination of activity and source type is not valid in the system “ORG No Budget Exists”– the Budget Chartfield string that you are attempting to charge to is not yet set up in the system “APPROP No Budget Exists”– the Appropriation (Class) being used for charges does not exist or does not have enough budgetfor the payment "Chartfield combination CLASS FLD ACCOUNT CNTRT; Invalid Chartfield combination“– combination within your chartfield string is incorrectFIN Budget Analyst should be contacted to assist with error06/07/2018

Contact Emails & TrainingContact EmailsFor questions please contact the below mailboxes: Initial project setups - FIN PROJECT LEDGERS Local participation - FIN Local-Participation Federal inactive projects – FIN UrgentReviewOfFHWAInactiveProjects Project status changes – FIN PSFT Status ChangesTraining From the ELM homepage, look for this training:– DEV505 – PSFT Third Party Funding From this link: http://crossroads/org/erp/ERP KnowledgeCenter.html, look forthis training:– DEV334 – Maintain Projects– DEV343 – Managing Contracts06/07/201828

Questions06/07/201829

Push to PeopleSoft is indicated by a date on the PE DCIS (P11) screen - Construction and CE phase Push to PeopleSoft indicated by an actual let date on the Project Info DCIS Screen(P01) State Letting projects, set up process begins after monthly Letting - Projects are made active in PeopleSoft after Award by Commission