FIN - Data Validation - Accounts Receivable
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:
All legacy non-Student Customers, AR balances, and Sponsor related balances that are open in the legacy accounts receivable system as of the conversion date will be extracted from the Legacy system and converted into PS Financials Accounts Receivable as open AR items.
Customer account validation will also take place during this cycle to ensure customers associated with open items have been translated properly into the PS system (ie. Address, short name, etc.).
*It will be imperative for colleges to bill (fully process) for specific Go Live term(s) Sponsor related open receivables in the Legacy system prior to the colleges' Go Live date in order to extract all the Sponsor related open receivables for conversion into PeopleSoft. Any unbilled AR that does not have accounting distribution in the Legacy system at the time of extraction will be captured on an error log.
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.
Questions regarding AR conversion data validation activities can be directed to Sabit Gottipati. General questions or concerns about the project data validation process can be addressed to the following individuals:
This conversion cycle involves verification of AR customer data, open item receivable balances, and item distribution. This an opportunity to identify issues and/or concerns so that they may be corrected prior to future cycles where more granular data may be analyzed.
The following are requirements and the general process for extracting data from Legacy:
- Extract customer account detail for invoices with outstanding charges.
- Include invoiced and non-invoiced open receivables that have accounting distribution only. Transactions that do not have an associated account structure would not be included in the GL.
- In the legacy AR system, there are transactions for non-student customers, students, and sponsors.
- AR customers are identified as follows:
- Students - Transactions with a CUST-TYP of “S”
- Non- students Transactions with a CUST-TYP of “O”
- Sponsors Transactions with a CUST-TYP of “o” and contain a student id with a suffix of “S” in the CUST-REF field.
- Non-student customer and sponsor open balance transactions will be written to Finance Accounts Receivable. Therefore, transactions will be written to the Pending Item and Pending DST (Distribution) files.
- Colleges should review balances in the Legacy system to ensure the data is accurate and should be converted to the AR sub-ledger in PeopleSoft. Files of the extracted data have also been provided on Canvas.
- Colleges should have readily available, completed reconciliations for all balances that will be converted by account, to include a report from legacy distinguishing which open receivables are non-student, and/or sponsor balances.
- Validation of balances for each Sponsor and non-student customers will need to occur within PS, ensuring that the open item extract detail matches what is shown within PeopleSoft.
- AR customers are identified as follows:
The following output files will be generated:
AR_CUSTOMERS This file has customer data (name, address, contact info, etc.)
QF1712S Pending Item File Write a single record to the Pending Item file for each invoice with open charges.
QF1713S Pending DST File - Write a record to the Pending DST file for invoiced transactions where the AMT-CHRG is greater than zero and the AMT-PYMT is equal to zero.
QF1715S AR Detail File This file will be used for research and cross walking. It contains a record for each AR transaction that will be processed.
Qf1714S Groupline File Write a record to the Groupline file for invoiced sponsor transactions that occur during go-live term (these transactions are merged within the files listed above - there is no separate file).
How values are extracted from Legacy:
An extract was created within Legacy to retrieve CUST-ACTV-D records with open charges. The extract filters on DEBT-TYP(OPTIONAL), CUST-TYP NOT equal to “S” and AMT-CHRG not less than zero (unless it’s a credit). These filters exclude transactions for students which will be converted via the Student Finance conversion.
Notes for Future Reference:
- All open sponsor and other non-student related charges prior to go-live will convert to the Finance module. Billing must be run with distribution prior to the conversion date in order for the transactions to convert to Finance.
- Payments made against converted sponsor balance transactions after go-live, will also be made in the Finance Accounts Receivables module until the balance is paid.
- For the go-live term, new contracts for sponsor transactions will be processed by PeopleSoft Student Financials (on and after the go-live date).
- New production begins with processing new transactions with accounting dates after the accounting cutover date.
It is possible that issues may arise in the conversion process that we may not be able to correct before college or agency representatives are asked to validate. While the issues should be minimal if at all, these issues generally involve the difference between the timing of when data is extracted out of legacy and when the last update to crosswalks have been made in PeopleSoft, for example.
In cycle 2, most of the differences related to individual customer balances are due to cross-walking the Legacy Customer ID to the correct PeopleSoft IDs and/or determining if new child records for shared customers should be created as an update to the parent record..
In cycle 3, we continue to review customer balances and customer accounts for accuracy. In addition, a major focus for this cycle is to also review transactions from a cross-pillar perspective (AR/SF). Where possible, the goal was to ensure reliability of data and to reduce duplication, if any, that could occur. Careful attention to the type of data within the Legacy extract files for AR is needed to ensure appropriate data is not duplicated in both SF and Finance in PS.
Types of issues found in cycle 3 pertaining to customers - to keep in mind for cycle 4:
1. Fields that have a % sign in the address fields need to be cleaned up within legacy with actual addresses or kept blank, perhaps. Think about how the address fields should be populated in PS (regardless of whether they are being mailed, emailed, or not). Consistency is key.
2. Are some of these customers emailed and do not need addresses? If so, please state which customers will be emailed only and ensure that the customer email addresses are included.
3. Customers that do not have an address within the system like NSF students for example, need an address if the intention is to bill those students within the finance module. If the students will be billed in SF, then those transactions should not come into the finance module.
In cycle 4, continue to review customer balances to include the chartstrings and customer accounts for accuracy. Ensure the local billing and AR business processes are compatible with how PS converts/processes data. It might also be a good idea to start thinking about when and how new transactions will be recorded before or during the time period that Legacy is taken down and Post Go-Live (after conversion weekend).
*Take a look at your AR_Invoice_Detail report from Legacy. This data was converted into PS. Are the transactions within the extract file from Legacy Sponsor and non-student related transactions only? If not, we need to determine how transactions that are not Sponsor or non-student related will be converted else where (SF).
Data Validation SMEs should review their converted records very closely, making sure that customer information (address, bill to information, etc.) and open receivables are reflected accurately within PS. This means that the converted open item receivables data (non-student and Sponsor) in PS should match the open AR balances reflected in Legacy at minimum, by invoice and by customer.
Customers are globally set up (shared WACTC). At minimum, the college responsibility entails making sure that the customer information (name, address, contact information, open balances, etc.) is accurate.
AR Open Items
An important part of the validation process is to reconcile converted PS balances to the ending AR open item Legacy System balances.
Extract Quality Assurance/Recon The QA and reconciliation is a College Data Validation SMEs responsibility. Quality checks and reconciliation between the two systems should be performed to ensure completeness, accuracy, and validity of the invoice conversion records. The following reconciliation checks can be a programmatic edit that is part of the extract process. However, it must also be done manually by our institution’s accounting staff to ensure quality.
- The sum of the extracted open balance records for an Invoice ID should equal the legacy system’s total Invoice open balance. (Pending Item 1 tab in PS)
- The sum of all extracted open amounts for a Customer ID should equal the Legacy system’s open customer balance.
- The sum of all extracted open amounts for all customers should equal the total open AR Balance.
- The sum of all extracted open amounts should reconcile to the legacy GL AR balance.
- It is recommended that you run a BM1710 AR Aging Report in Legacy for the same time frame as the data snapshot for validation.
If the information above presents a potential challenge that needs to be overcome for the final conversion run of AR open items, please be sure to make that concern known. Conversion planning discussions will be held during and after the Data Validation Workshops.
This section outlines the tasks necessary to perform data validation of the Customer Conversion and AR Open Item Conversion data provided by the College Data Validation SMEs. The goal for AR conversion is to convert and load AR open item information in PeopleSoft. This section specifies the key information from Legacy that will be translated into PeopleSoft. Please also be sure to read through the information within this section prior to starting your data validation work.
For each open item a customer has in a business unit, the interface program creates a pending item with an assigned business unit, customer ID, and item ID (invoice). When the Receivables Update Job processes these pending items, it determines if it can use the customer ID that is established in the TableSet for this business unit. It then creates the customer balance and history information for the customer in the business unit.
Navigation Path: Main Menu -> Accounts Receivable -> Customer Accounts -> Customer Information -> Account Overview
Listed above is the menu path you will use to get to the Customer Accounts Overview search page within Receivables so that you can validate the customer profile information, activity, and balance per invoice to assist in your data validation work. Start by clicking on “Main Menu” in the upper-left portion of the PeopleSoft landing page and drill into the menus using the above breadcrumbs; these will take you to the Customer Account Overview search page. It will be useful to have your list of customers with open balances available as you navigate this page.
As shown below, the Account Overview is displayed by Customer. The Balance tab shows summarized transactions.
Within this page is a hyperlink for Balance, that allows the user to “drill down” to review detailed transactions that make up the summarized balances.
The Profile tab shows Customer Information for billing purposes. You will want to verify that the customer information for your customers within the system are accurate.
To see the child accounts for addresses, navigate here: Customers > Customer Information > General Information > SetID = WACTC
Be sure to click on View All to see all of the records.
As you all know, customers and suppliers in ctcLink are global. We have a real challenge maintaining clean customer and supplier tables. Of course, our biggest challenge is during the conversion cycle when hundreds of customers and suppliers are being added.
In an effort to keep the global lists clean, we will not create duplicate customers or suppliers. If you believe truly you need a separate customer for the same company or agency, please check with Teri Sexton (email@example.com).
We are also asking you to clean up the Legacy customer lists that are added during each cycle. Please do not use abbreviations like “NLNT North C013” as users will be unable to figure out who it is (if it were added).
For cleaner, more consistent customers, see chart below:
Navigation Path: Main Menu -> Accounts Receivable -> Receivables Update -> Posting Results-Updated Items-> All Items w/Detail
Listed above is the menu path you will use to get to the All Items /w Detail search page and then to specific AR records to perform your data validation work. Start by navigating to the “Main Menu” and drill into the menus using the above breadcrumbs. Once there, select the Group Unit from the drop down menu, then select search.
This Group Control page shows that a group of items (Group ID 1) have been loaded into PS for WA140.
The focus for this page is to:
- Validate the totals
- Ensure the Control Total and Count matches the Entered Total and Count
- Verify that there are no differences between the Entered Total/Count and the Posted Total/Count
- If there are differences, notate any irreconcilable differences in OTM (explained later)
Next, select the pending item 1 tab. On this tab, you will see the detail for transactional amounts for all of the open invoice records within the converted group from legacy into PS.
*There are 228 open invoices within this group, however, several lines of accounting for most records (see 4.1.3). Review transactions to ensure the same amts. for each line exist in PS as they do in Legacy.
Pending Item 2 Tab - This is an informational tab only and is not needed for validation.
Pending Item 3 Tab - This is an informational tab only and is not needed for validation.
Navigation Path: Main Menu -> Reporting Tools -> Query -> Query Viewer
Next, enter QFS_AR_ITEM_DIST_CTC_CONV into the Search box as shown below and select “Search.” When the query appears under “Query Name” (just below), Select “Excel” to run the query.
When you select “Excel,” a SetID box will appear. Please choose your Business Unit from the drop down selection, then choose “View Results.”
When you choose “View Results” a dialog box will appear. “Open” the excel file for review accordingly.
Filter out the AR Control account 101010 to review transaction details from Legacy only
Review the excel spreadsheets (extracts) and/or data within Legacy to the data output viewed in PS. You are reviewing the data to determine:
- Did the Customer Data convert as expected?
- Did the data upload as expected in terms of values (detail/summarized transactional balance(s)?
- Is the accounting distribution (chartstring) correct?
*There are files on Canvas that may also prove useful as you complete your validation (see screenshot below as an example of the types of file you would expect to find on your Deployment Group's canvas page).
Data from an AR_Invoice_Detail
Data as converted into PeopleSoft
The word balance is highlighted in blue, which means it is a hyperlink that you can drill into. If you click on balance it will take you to another page.
Each invoice is highlighted in blue, which means it is a hyperlink that you can drill into. If you click on the first invoice (00000001770 - $579.20) it will take you to another page, where you can see the detail for that particular invoice. Notice the 4 lines of detail. 3 lines, 1,3, & 4 total the invoice amount of $579.20. If you drill into each of the 3 invoices, a total of eighteen lines will display (which matches the detail of the AR_Invoice_Detail).
*Notate any discrepancies found, if any in the Validation Results Template discussed below.
AMT-CHRG doesn't equal INVOICE-TOTAL
||This one is no longer
used. We always calculate the invoice total based on the detail lines so
this message is obsolete and will not be displayed on an error log report.
||Account Structure has
not been assigned to this item
||This transaction will
not be included in the extract because there is no associated account
||CHRG-ID has not been
assigned to this item
||This transaction will
not be included in the extract because there is no assigned CHRG-ID. Without
a CHRG-ID, the account structure cannot be retrieved.
||This transaction will
not be included in the extract because the invoice total is equal to zero.
||SOBJ and SRC-REV
||Accounts needs to be
||GL associated with charge does not equal 13xx
||Needs Legacy AR
account for proper crosswalk to PS AR account
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
Also, upon completion of data validation, please ensure feedback has been logged in OTM.
- Pillar: FIN
- Assigned To: Sabit Gottipati