FIN - Data Validation - Asset Management

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 Asset Management
Asset Management Conversion Summary

During the Business Process Fit Gap sessions, attendees were provided an Asset Conversion Worksheet template.  You were asked to provide a complete list of your assets for converting into ctcLink. Note: For DG4 Asset Management Data will not be converted until Cycle #3.

The worksheets were transformed into ExceltoCI workbooks.  The assets were then loaded into ctcLink and processes run to calculate current year depreciation and create the requisite accounting entries.  There are separate accounting entries for the creation of the asset and recording the monthly depreciation amounts.

After go-live, asset additions and depreciation would flow to the General Ledger via Journal Generator, but for conversion the General Ledger already reflects the addition and accumulated depreciation.  These journals will not be created for conversion, so the validation will use online pages and queries on the DIST_LN (AM accounting line) table to compare with the sample data provided.

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 strongly 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 about the conversion or the conversion validation effort can be directed to:

Contact Name
Contact Role
Contact Email
Andy Palm GL/AM Functional Analyst apalm@sbctc.edu
Christyanna Dawson
Finance Functional Team Lead
cdawson@sbctc.edu
Accessing the Legacy Conversion Data Snapshot

Data for asset conversion comes solely from the workbooks you provided.  Those workbooks are posted on DG4 Finance Conversion Information on Canvas or DG5 Finance Conversion Information on Canvas site.

Conversion Detail Information
Conversion Background

The data for the Asset Conversion comes from the BPFG Homework assignments.  Colleges were asked for a complete asset listing, as of the last update to your fixed asset accounts in FMS.   Compare the ctcLink asset data to your legacy data.  Also compare the ctcLink asset totals to the converted GL balances.

Common Conversion Issues

Several issues were noted during prior conversions that need to be considered as you do your validations.  The issues, and any remediation, is noted below.

  • Asset cost did not meet minimum capitalization thresholds.  Depending on the Asset Class, whether the asset represented a Small & Attractive asset or not, the threshold used may have been adjusted to allow the asset to be loaded.  Otherwise, assets that did not meet the thresholds did not get loaded.
  • Small & Attractive assets do not create any accounting or depreciation entries.  These assets can still be viewed from the Basic Add pages.
  • Accumulated depreciation data exceeds the Asset cost on the supplied spreadsheet, resulting in a negative net book value (NBV).  No adjustment was made for these assets, and when the depreciation calculation was processed, ctcLink created negative deprecation amounts to bring the net book value back up to $0 at the end of the asset’s useful life.  (If the asset did not fall into the next group.)
  • Assets were listed with In Service dates that indicated that the asset should have been fully depreciated, but the asset still showed a positive net book value.  If, for example, the asset Class (as defined by OFM) should be depreciated for seven years, but the In Service date was 10 years ago, ctcLink will not generate any further depreciation for that asset.  These assets will never depreciate down to a $0 NBV.
Minimum Validation Requirements

Follow the steps below to ensure the cost and accumulated depreciation agree to the provided data and verify the future depreciation calculations are correct.

Conversion Validation Steps
Verify all assets were loaded

Navigate to: Navigator > Reporting Tools > Query > Query Viewer

Run the query CTC_CNV_ASSET_VALIDATION and download it to Excel to slice-and-dice the asset data as needed.  The query will prompt you for your Business Unit.

Query Viewer Image

Make sure all the assets listed on your spreadsheet are listed.  Capital and Small and Attractive assets will be listed.  If an asset is not listed, check your spreadsheet for total cost  the asset may have not met the required threshold.

Verify Asset Cost, Accumulated Depreciation, and Future Depreciation

Navigate to: Navigator > Reporting Tools > Query > Query Viewer

Run the query QFS_AM_CNV_DEPR_VALIDATION to Excel and validate the Current Period (month), YTD, and total Accumulated Depreciation amounts.  This query also calculates the net book value of each asset as of June 30 of the fiscal year being converted.

Supply your Business Unit and click View Results.  Only capital assets will be listed, as Small and Attractive assets do not depreciate.

Query Viewer Image

You can also review and validate the future depreciation by month and distribution for individual assets.  Go the Period Depreciation page to see the calculated monthly depreciation amounts.  You can view any year by selecting a different Year and clicking Get Period Depr.  You can get the useful life in years for an Asset Class from OFM.  ctcLink calculates depreciation monthly, so you’ll have to convert years to months to do your own calculations.

Navigate to: Navigator > Asset Management > Depreciation > Review Depreciation Info > Asset Depreciation

Supply your Business Unit and click search.  Only capital assets will be listed, as Small and Attractive assets do not depreciate.

Asset Depreciation Find Existing Value Image

Go the Period Depreciation page to see the calculated monthly depreciation amounts.  You can view any year by selecting a different Year and clicking Get Period Depr.  You can get the useful life in years for an Asset Class from OFM.

At the bottom of each page is a Next in List button.  You can click that button to walk through the details for each asset for your Business Unit.  If you get a message asking if you want to save your changes, just click No.  The next asset will load, and you can navigate to any of the three pages to validate the next asset.  Alternatively, click on Return to Search.  The original search results will still be displayed, and you can click on a different asset ID.

Validate the Accounting for the Asset

In ctcLink, the Account used comes from system configuration, the remaining Chartfields are copied from the distribution provided on the spreadsheets.

A list of applicable asset-related accounts is provided here for your reference.

Navigate to: Navigator > Asset Management > Accounting Entries > Review Financial Entries

Supply your Business Unit and click Search.  Select the first asset in the search results.  Like the Asset Depreciation pages, there is a Next in List button that will let you walk through the assets one at a time.  

Asset Accounting Entries Image

Review the Account for each line of each Trans Type.  ADD rows indicate the entries created by adding the asset and accumulated depreciation.  DPR rows indicate the monthly depreciation entries.

Pay particular attention to the accounting for assets acquired with Proprietary Funds, as opposed to Governmental Funds.  Proprietary Funds use separate Depreciation Expense accounts, as well as different accounting upon asset retirement.  

Scroll the page to the right to see the Amount field.  The Account field is fixed and will still display.

Book Accounting Entries Image

In the above example, row 1 shows the fixed asset account 1121070  Furnishings and Equipment is being debited for $15,223.59.  This amount is offset on row 3 by account 5040010  Furnishings and Equipment expense.  The system assumes the asset was purchased through AP/PO on an expense account, so this entry is to reclass the expense as a capital asset.  (Don’t worry.  We know these entries have already posted to the legacy GL, and we will not be posting any conversion accounting entries to the ctcLink GL.)

Row 2 is crediting 1121080  Allowance for Depr  Furn and Equip, and debiting 5070010  Depreciation Expense  Proprietary on row 4.  Rows 5-10 represent the monthly depreciation amounts.

Verifying Small and Attractive Assets

Because Small and Attractive (S&A) assets don’t create capital asset or depreciation entries, they cannot be validated using the steps above.  You can Search for an Asset (Navigator > Asset Management > Search for an Asset) and see if they’re listed, but to see the details, you need to go to the Basic Add pages.

Navigate to: Navigator > Asset Management > Asset Transactions > Owned Assets > Basic Add

Supply your Business Unit and an S&A asset ID from your spreadsheet and click Search.

Asset Basic IOnformation Image

Your S&A asset General Information page displays.  Here you can see the Description, the Asset Class, Acquisition Date, Tag Number and other details.

Go to the Asset Acquisition Detail page to see the Asset Cost.  On a special note  if you supplied Federal Asset Identification Numbers (FAINs) on your spreadsheet, the Description on this page is the only place you can see it, other than queries.

FAIN goes here Image
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 College 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 data being validated through this guide, remember to use the following values for these specific required fields in your issue entry under the project tied to this Deployment Group and Data Validation Cycle

  • Summary: (Provide a title for the issue discovered)
  • Assigned To: Andy Palm
  • Status: (Leave as NEW)
  • College or Location : (Specify the college or district)
  • Priority: (Leave blank - not used)

Also, upon completion of data validation, please ensure feedback has been logged in OTM.

0 Comments

Add your comment

E-Mail me when someone replies to this comment