Selecting the Method to Number Accounting Lines

Within the Accounting Line Creation process, a substantial reduction in processing time can be achieved by using set-based processing to number the lines of the newly-created accounting entries. As delivered, the system uses row-by-row processing to create the sequential accounting line numbers. By performing additional set up steps you can switch from row-based processing to set-based processing to generate the line numbers.

Note: The set-based approach is optional. The delivered default option is the row-by-row approach.

Set-based processing of the line numbers can be accomplished by using the database platform's approach and syntax to generate sequential numbers. All PeopleSoft supported database platforms have an approach for generating sequence numbers within the database. The Cost Accounting Creation process can take advantage of this feature with some minor additional set up steps.

The architecture of the set-based sequence approach is similar across all platforms with just a few changes for platform specific syntax:

  • Create a temporary working table with an identity column or use a database specific rownumber function on the database platform, depending on the platform. The syntax is platform specific but the behavior is fairly common. The create table syntax is delivered as a special PeopleSoft Data Mover Script (DMS) for installation, however, some platforms will require further intervention by your database administrator.

  • During the Accounting Line Creation process, the SQL statement to insert the data to the special temporary work table has slightly different syntax on different database platforms but the behaviors are very common across platforms. This syntax is delivered as part of the process logic and requires no special user intervention.

Database Platform

Sequential Numbering Method

Microsoft SQL Server

These platforms use an IDENTITY type column. When a table is created with an identity column, the sequence numbers are generated automatically by the database behind the scenes.

Oracle

This platform uses a ROWNUM function to generate sequential numbers.

The set-based sequencing approach depends on a special installation process to set up the objects. The setup has the following steps on all platforms except Oracle:

  1. Find the DMS for your specific database platform. The scripts are located on the scripts folder: <PeopleTools home>\scripts.

    Database Platform

    PeopleSoft Data Mover Script

    Microsoft SQL Server

    cm_id_create_mss.dms

    DB2

    cm_id_create_db2unix.dms

    OS390

    cm_id_create_db2os390.dms

    Oracle

    (no DMS needed)

  2. Engage your Database Administrator and review the provided PeopleTools DMS. If your system uses customized ChartField column names, then the DMS must be modified manually. If the database is configured to use the unicode character set, then the DMS must be modified manually. Some platforms require tablespace names and other database administration parameters to be defined as part of the create table command.

  3. Run the provided PeopleTools DMS to recreate the sequence numbering work table (the CM_ACCTG_LN_TM2 record) with an IDENTITY type column.

  4. The Set based Sequencing check box on the Installation Options- Inventory page should now be available. Select the check box to use set-based sequencing.

On Oracle the installation process for set-based sequencing is just one step. Select the Set based Sequencing check box on the Installation Options- Inventory page. It is always available on an Oracle platform. The system uses a built-in database ROWNUM function and work table (the CM_ACCTG_LN_TMP record) as originally installed by PeopleTools.

At run time, the Cost Accounting Creation process checks for three specific issues before deciding to use set-based sequencing:

  • Verifies the DMS has been run (This is always set for ORACLE).

  • Verifies the Set based Sequencing check box has been selected on the Installation Options - Inventory page.

  • Verifies the table structure of the sequence numbering work table (PS_CM_ACCTG_LN_TMP in Oracle and PS_CM_ACCTG_LN_TM2 in the rest of the platforms) and its sub record have not changed since the time the Set based Sequencing check box was selected.

The message log for the Cost Accounting Creation process displays which type of sequencing is being used to create accounting lines (row-by-row or set-based). This can be used to confirm the type of sequencing selected.

Note: Using the set-based processing method to generate the sequential accounting line number is significantly faster than row-based processing. However, set-based processing can produce gaps in numbering. For example, within one accounting entry with four lines, the lines can be numbered 1, 5, 6, and 11, rather than 1, 2, 3, and 4.

Page Name

Definition Name

Usage

Inventory Page

INSTALLATION_INV

Select the Set based Sequencing check box to use set-based sequencing to number your accounting lines created by the Accounting Line Creation process.