FIN - Data Validation - General Ledger

Purpose: This document is to provide colleges instructions on how to perform the validation of data in the PeopleSoft environment.

Colleges are being asked to review data that was converted from the legacy environment to PeopleSoft.  The goal of data validation is to ensure legacy data was accurately converted into PeopleSoft.  College Subject Matter Experts (SMEs) are included in data validation as a learning experience in navigating the PeopleSoft application, selecting records to review, and reporting issues discovered.

College executives are asked to support the time and effort expended by college SMEs necessary to complete the following:

  • Develop a familiarity with navigating the relevant PeopleSoft menus and pages.
  • Review selected records, comparing legacy data to the records as converted into PeopleSoft.
  • Report issues discovered after careful review of converted data.
  • Monitor resolution of reported issues for review in the next conversion testing round.
  • Develop an approach for self-managing the data validation and issue reporting process in subsequent rounds of data conversion testing.

It is expected that College Data Validation SMEs will have already read the Data Validation Overview guides before beginning their validation activities:

Overview of Data Validation for General Ledger
General Ledger Conversion Summary

Conversion starts with taking a copy of legacy data as of a specified date, and saving that copy into a separate database that can still be accessed via a special conversion login account.  This copy is referred to as the legacy snapshot.  For a list of conversion snapshot dates for each cycle, please refer to the Data Validation Overview - Getting Started guide on this topic.  General Ledger Data is not converted until Cycle #2.

There are a series of programs that read the legacy snapshot data and create a set of 'Extract' files.  The data in those files are loaded into PeopleSoft staging tables using conversion programs that read a set of cross-walk tables to convert the legacy distribution values to PeopleSoft Chartfield values.  The converted data is then loaded into ctcLink, and a process is run to create journals representing opening balances and monthly activity.  The journals are posted to the General Ledger, where validation occurs.

General Ledger validation will cover opening balances for the current fiscal year for all Assets, Liabilities, and Fund Balances. It will also include monthly balances for all Accounts as of the date of the legacy snapshot.

Conversion Validation Due Date

The Validation Results and Feedback are due NO LATER THAN on the last day of the Data Validation Period. It is strong recommended that issues are reported EARLY in the validation period and are not held until the end of the period. Validation Results and Feedback are for reporting closure of the college's validation activities for the conversion cycle. To view a list of the validation periods please refer to the timeline topic in the Data Validation Overview - Getting Started Guide.

Getting Help

Questions regarding these data validation activities can be directed to Andy Palm or Christyanna Dawson.

Contact Name
Contact Role
Contact Email
Andy Palm
GL/AM Functional Analyst
apalm@sbctc.edu
Christyanna Dawson
Finance Functional Team Lead
cdawson@sbctc.edu 
Conversion Detail Information - General Ledger

The GL conversion converts detailed General Ledger transaction data for the current fiscal year to up the conversion snapshot date, current fiscal year being based on when the conversion occurs.  Using a October 31, 2019 go-live date as an example, the fiscal year targeted for that go-live would be 2019-2020 (FY2020).  Given that target fiscal year, the conversion would bring over detail data from July 1, 2019 all the way through to the conversion date in October.  

For each conversion cycle, the snapshot date will change.  Please refer to the timeline topic in the Data Validation Overview - Getting Started Guide for specific data snapshot dates.

If the snapshot date is BEFORE July 1 of the current calendar year, then all the GL transaction data from July 1 of the prior year up to the snapshot date is included in the conversion for GL.  

If the snapshot date is AFTER July 1 of the current calendar year, then all the GL transaction data from July 1 of the current calendar year up to the snapshot date is included in the conversion for GL.  

Conversion Background

These General Ledger balances are converted into PeopleSoft through a four-step process:

  1. Legacy GL Extract  The GL Extract Program gets data out of FMS and formats it
  2. PeopleSoft GL Upload  GL Upload Program takes formatted extract data and stages it
  3. PeopleSoft Journal Generation  Staged GL data is picked up by Journal Generation, producing new Journals in PeopleSoft
  4. PeopleSoft Journal Processing  New Journals are posted, and balance are reflected on General Ledger reports in PeopleSoft

This work is done in two parts: one part extracts beginning balances and a second part extracts the transactional data that happened throughout the year.  Together, these two parts produce the detailed transactional data that will be converted into PeopleSoft.  It’s also important to note that no historical GL data is being converted.  

Common Conversion Issues

While the conversion approach for the data elements identified above, were followed, it’s possible that some provided data was not directly converted for the purpose of this conversion effort.  Additionally, it’s possible that some information will appear differently than the way it was provided.  Here is a list of these kinds of items to keep in mind while you’re doing your data validation:

  • In many cases, our Legacy accounting values are being changed into different PeopleSoft equivalents. For example, our 4-digit alpha-numeric Organization Index values are being translated into 5-character alphanumeric Departments in PeopleSoft, or at colleges that utilize the Program Chartfield, the 5-character Department + the 5-character Program. You can find more information on the different PeopleSoft Chartfields and from where their values are derived, in the “GL Extract Overview” document on DG4 Finance Conversion Information on Canvas or DG5 Finance Conversion Information on Canvas.
  • There are several instances where our Legacy accounting values are put through a crosswalk to get to the correct PeopleSoft equivalent value.  Values that you see in Legacy reports, in the Legacy GL Extract File, and in PeopleSoft Journals may not be directly comparable without factoring in these crosswalks.
  • In instances where the Org Index value in the extract was not found in the crosswalk, the department was defaulted to CNV19.
  • You will only see one Operating Unit value in the extract files for your college.  Remember that this value is also run through a crosswalk (the ORG_INDX to OU/Dept/Program crosswalk), and the Operating Unit value that actually appears in PeopleSoft will be based on that crosswalk, not on what’s seen in the extract files.  
  • Converted General Ledger balances will appear on Commitment Control reports or inquiry screens. Budget checking was set to Track with budget, which means Commitment Control budget entries were made related to the conversion journals with zero dollar amount means the operational budget lines were created from converted General Journal lines with zero dollar budget amount. This will be the case at go-live.
  • Any transaction line requiring a project was converted with a Project ID beginning with 0000003xxx (six zeros, then 3), and an Activity ID that begins with ACTx.
Minimum Validation Requirements

One cornerstone of a successful implementation is a valid conversion of legacy data.  For the General Ledger conversion, this means validating:

  1. the proper data was extracted from the legacy system
  2. the data was cross-walked to the proper accounting distribution in PeopleSoft
  3. the PeopleSoft journals match the extract cross-walked distributions
Conversion Validation Steps - General Ledger

This section outlines several comparisons you can make to help validate your converted General Ledger balances.  The tasks below are listed out in a progression that looks at conversion data going from Legacy into PeopleSoft.   This is how the conversion process itself flows, and due to the consolidation of values that happens during your college’s Legacy-to-PeopleSoft cross-walk effort, you will need to perform the validation tasks in the order they are shown below.

General Ledger Validation

Colleges are being asked to review data that was converted from the legacy environment to PeopleSoft.  The goal of data validation is to ensure legacy data was accurately converted into PeopleSoft.  College Subject Matter Experts (SMEs) are included in data validation as a learning experience in navigating the PeopleSoft application, selecting records to review, and reporting issues discovered.

College executives are asked to support the time and effort expended by college SMEs necessary to complete the following:

  • Develop a familiarity with navigating the relevant PeopleSoft menus and pages.
  • Review selected records, comparing legacy data to the records as converted into PeopleSoft.
  • Report issues discovered after careful review of converted data.
  • Monitor resolution of reported issues for review in the next conversion testing round.
  • Develop an approach for self-managing the data validation and issue reporting process in subsequent rounds of data conversion testing.
Compare Legacy Reports to the Legacy GL Extract File Data

As the first step in the conversion process is to produce a GL Extract File from Legacy, your first point of validation is to compare your Legacy reports to what appears in that Extract File.  This validation ensures that the correct transactions were selected out of the Legacy system to be translated into PeopleSoft.  Please keep in mind that the contents of the Extract File is not a 1:1 copy of Legacy, some value transformations are done during that extract process.  Please review GL Extract Overview file on Canvas for more information on where the values appearing in the Extract File originate from in Legacy and any transformation that may be occurring.

Compare the Legacy GL Extract File Data to the Conversion Journals in PeopleSoft

Once you understand where the transactions appearing in the GL Extract File came from, you can take the next step and compare that data to the actual Conversion Journals that were created in PeopleSoft.  This validation will ensure that the correct cross-walk effort is being done as the extracted data is loaded into PeopleSoft.  

To help you in this effort we have provided an Excel workbook that contains the legacy to ctcLink crosswalk that you can use to look up the ctcLink values.  You can retrieve it from the link on DG4 Finance Conversion Information on Canvas or DG5 Finance Conversion Information on Canvas.

Viewing Conversion Journals In PeopleSoft

Navigation Path: Navigator  General Ledger  Journals  Journal Entry  Create/Update Journal Entries

Follow the Navigation Path to view the journals created from your extracted data.  

  1. Click on the Find an Existing Value tab to search for the journals.
  2. Click the Clear button to clear the defaulted values on the Search page
  3. Enter your Business Unit: WAnnn (where 'nnn' is the college code, e.g., WA140)
  4. Enter CNV in the Journal ID begins with box.  All the conversion journals will begin with CNV.
  5. Click Search
Create/Update Journal Entries Image

You conversion journals will be displayed below your search criteria, and you can click on any of them to open the journal.  

Click on the Journal Date column heading to sort the journals by date. The Beginning Balance journals will have a journal date of 07/01 of the current fiscal year. The monthly journals are dated the last day of each month.

The Beginning Balance journal has been modified to be an Adjusting Journal in Period 998 to distinguish them from the monthly journals. You can see the journal type and period on the journal header page.

Search Results Image

Click on any field in a row to open that journal.  You will see the journal header page with some tabs across the top.

Tabs Image

Click on the Lines tab to view the journal lines.  Use the scroll at the bottom to see more fields.

Lines Tab Image

You can also use the query CTC_CNV_JNL_VALIDATION to view the lines or export them to Excel.

The query has prompts for your Business Unit, Journal ID and Fund.  The Journal ID and Fund prompts accept the use of wildcards (the % sign) to view more than on value in a query.

Overview of Supplemental Material

In addition to the instructions above, several other documents and workbooks have been provided on DG4 Finance Conversion Information on Canvas or DG5 Finance Conversion Information on Canvas that include information or data that may help you in your validation efforts.

  • Legacy Extract Data Files Excel files with the raw Legacy data before being converted to ctcLink Chartfields. Depending on the number of transactions, each college has one or more files with all the conversion data. Each month, and the beginning balances, will be a separate sheet in the workbook(s).
  • GL Extract Overview  This Word document provides a high level explanation of the GL conversion process as well as an explanation of how Legacy GL data is captured for conversion into PeopleSoft (how the Legacy GL Extract Data file is populated).
  • Crosswalk Tables  Link to the page containing Excel workbook crosswalks the Account and Class Chartfields, and an explanation of the Fund and Appropriation Index values in ctcLink.  Department and Program values come from the homework provided by each college.  Refer to that homework for the conversion values.
Submitting Data Validation Results

College data validation SMEs will communicate any conversion issues they encounter during their data validation via the Oracle Test Manager (OTM) application. The ctcLink Testing Team will provide OTM orientation materials before the data validation period for any data validation SMEs who are not familiar with the tool.  Please refer to the Data Validation Overview - Reporting Issues guide for more information on using OTM.

When logging a new issue in OTM related to the data being validated through this guide, remember to use the following values for these specific required fields on your issue entry:

  • Pillar: FIN
  • Assigned To: Andy Palm

0 Comments

Add your comment

E-Mail me when someone replies to this comment