State Agencies Bulletin No. 760

Subject
Renaming PS Queries in Preparation for the Payroll System Upgrade to PeopleSoft Version 8.9
Date Issued
November 15, 2007

Purpose

To notify agencies of the process for renaming queries designed in the current version of PS Query so the queries will be compatible with and run effectively in the upgraded version of PS Query

Affected Employees

All PS Query users and staff who request reports from PS Query

Effective Date(s)

Immediately

Background

OSC will be upgrading from PeopleSoft version 8.3 to 8.9 in 2008. Query users are asked to review current queries and rename any query that will be needed after the upgrade implementation. There will be “migration dates” when queries will be copied from the 8.3 version to a development version of 8.9. Queries that have been renamed before the final migration date will be available in version 8.9. Queries that have not been renamed will not be available after the upgrade is implemented.

Due to the large number of saved queries and resource limitations, OSC may not be able to test and modify all of the migrated queries. Users are asked to prioritize their Private queries that are renamed and need to have available after the upgrade. Details of the prioritization plan follow.

In addition, users are asked to rename any Public query that needs to be migrated from version 8.3 to 8.9.

OSC’s goal is to test all Public queries and all critical Private queries. However, the number of renamed Public queries will determine how many of the queries actually can be tested.

OSC will provide agencies with a list of the Public queries that have been renamed after the initial migration. Users may be asked to prioritize these queries to indicate which queries are most critical. Public queries designated as highest priority will be tested first. There will be a limit of 25% of the renamed Public queries that can be assigned the highest priority. OSC’s goal is to test all renamed Public queries, but initial testing will focus on the highest priority queries.

For the 2003 Query upgrade, approximately 25% of saved queries were renamed and migrated. Query users determined that the remaining 75% of saved queries had served their purpose and were no longer needed.

Upgrade Naming Convention

PS Query users are asked to identify those queries that are to be brought forward to version 8.9 by renaming the existing query, giving it a name that meets the criterion described below. Queries that are not renamed will not be brought forward from the version 8.3 database.

The naming convention for PS Query modules to be brought forward to version 8.9 will be comprised of a prefix, the existing descriptive name and a suffix (for Private queries only), as described as follows:

  • Prefix - For agency users, all queries to be brought forward will have a prefix of Q89_. Please include the underscore character before the body of the query name.
  • Existing Query Name - The existing PS Query name should remain as-is, whenever possible. There is a 30 character limit on query names. Some query names may have to be modified to include the prefix and suffix. If the query name begins with UG, replace “UG” with “Q89”.
  • Suffix – The suffix will be added to Private queries only. It will represent the priority assigned by the person designating the Private query to be brought forward. The suffix will be one of those described below:
    • _P1: Indicates the query is deemed Critical. The query is required in order to accurately produce or verify [i.e. audit] significant portions of every payroll. Private queries with this designation must be available at the version 8.9 Go Live date. No more than 25% of each user’s renamed Private queries can be designated as Critical.
    • _P2: Indicates the query is of Medium priority. The query is important for use in post-payroll activities or used on a less frequent, periodic basis [e.g. monthly or quarterly]. These queries will be migrated and available on the Go Live date but may not have been tested. If necessary, these queries will be tested after the testing of the Critical queries has been completed.
    • _P3: Indicates that the Private query is of relatively Low priority. The query is submitted on a somewhat regular basis, but less frequently than the higher priority ones and/or has less critical use. Upgrading these queries will be responsibility of the individual query user.

For example, a critical every-pay-cycle Private query currently named YTD_OVT_BY_POSN would be renamed to be Q89_YTD_OVT_BY_POSN_P1.

Public Queries
  • OSC will rename all of the “Locked” generic queries. Agency staff must not rename any of these queries.
  • Any PS Query user may designate Public queries to be brought forward to version 8.9, by adding the Q89_ prefix as described above.
  • Public queries that are renamed in this manner must remain Public. The person renaming the Public query should not rename it to be Private.
  • Public queries that are designated for upgrade do not need to have a priority suffix assigned. All Public queries that are brought forward will be available by the version 8.9 Go Live date.

Migration Plan

Queries that have been renamed by January 18, 2008 will be migrated to version 8.9 in February 2008. OSC will then begin reviewing, testing and modifying (as necessary) queries based on their assigned priority.

A final migration will take place before the upgrade Go Live date to capture queries that were saved after the initial migration.

Since the final migration will be near the Go Live date, there will be a limited amount of time to review and test queries that are included in the final migration.

Users should immediately begin renaming queries that may be needed after the upgrade. This task must be completed by January 18, 2008 to assure that these queries are included in the initial migration.

PS Query Testing

The testing process will involve running the migrated queries in the current and upgraded versions. The results will be compared, differences and issues identified, and solutions developed.

The database changes between versions 8.3 and 8.9 are not as significant as those identified in the 2003 Query upgrade.

Agency Testing and Assistance for Agencies

Agency query users may be asked to assist, if available, with the query testing. A query testing guide will be prepared and provided to agencies. Agency staff can use this guide to test and upgrade their non-essential Private queries after the Go Live date. OSC will provide assistance as necessary.

Agency Actions

Payroll officers are requested to distribute this bulletin to all query users in their agency.

Query users should:

  • Review Private and Public queries created by each user and any Public query associated with their agency code that the user runs. (Most Public query names begin with an agency code.)
  • Refer to the final sections of this bulletin for:
    • strategies to determine if a query should be renamed
    • naming conventions
    • instructions on how to obtain a list of private queries and how to rename queries
  • Rename any query that is to be migrated to the upgraded version.
  • Save newly created queries according to the new naming conventions.

The target date for completion of this renaming phase is January 18, 2008. If additional Public queries are renamed after the initial deadline, these queries will be included in a future migration. The date will be determined and users will be notified.

Query names that do not conform to the new naming conventions will not be migrated and will not be available after the upgrade.

Strategies for Renaming Queries

The current PS Query version requires that all queries must be saved before they can be run. This requirement has resulted in a very large increase in the number of saved queries. Users can delete any Private queries that have served their purpose and are no longer needed.

Public Queries
  • Query users should rename any Public query that is needed to run after the upgrade. (The “locked” generic Public queries will be renamed by OSC.)
Comparable Public and Private Queries
  • If there are Public and Private queries that are essentially the same, agency users should rename only the Public query. If a commonly used Public query is renamed, tested and converted, it will not be necessary to rename the Private copies of that query.
  • After the upgrade is completed, agency users can open the Public query that has been tested and converted and save it as a Private version. This will reduce the number of queries that must be tested.
Private Queries
  • Agency users should also begin renaming their Private queries immediately with the following guidelines.

To limit the amount of time spent renaming Private queries, users should:

  1. Determine if there is a comparable Public version of their Private query. In such cases, rename only the Public query.
  2. Determine which of their Private queries will be needed after the upgrade. Delete Private queries that will not be needed.
  3. If the Private query will be needed, the user should review the following before renaming the query:
    1. If a comparable Public query has been renamed, there is no need to rename the Private copy.
    2. If a Public version exists but has not been renamed, the user should rename the Public version only.
    3. If there is no Public version, the user should rename the Private version according to the new naming convention. (Public and Private queries cannot have identical names).
  4. OSC will migrate renamed Private queries to the new version but only test the highest priority Private queries. It is the responsibility of agency staff to test the remaining Private queries after the upgrade.

Naming Convention Review

PS Query naming conventions:

  • must be all upper case alphanumeric characters;
  • must not contain any spaces;
  • must not contain any special characters (e.g. @, #, $, %, &, -, .) except for the underscore character;
  • must not contain any dots or periods;
  • are limited to 30 characters; and
  • must begin with Q89_ (remember to include the underscore) for the purpose of conversion and testing.

Private Query Lists and Renaming Instructions

A list of individual users’ Private queries and a step-by-step instruction guide on how to rename queries can be requested from the Query Help mailbox.

Questions

Questions regarding this bulletin may be directed to the Query Help mailbox.