Appendix: Mass Change

This appendix contains an overview and discusses how to:

Note. Mass Change is a deprecated product. Support will be maintained for this product, but no new development will be produced for Mass Change. If you used Mass Change in previous PeopleSoft releases, it is strongly recommended that you use Application Engine instead.

See Also

Application Engine Overview

Click to jump to parent topicUnderstanding Mass Change

When end users manipulate the data in a PeopleSoft application, they are essentially executing SQL statements. PeopleSoft provides many of the statements necessary for updating that data, but you might occasionally need to create more.

Mass Change is a SQL generator you can use to develop and perform custom applications. Using Mass Change, a developer can set up a series of INSERT, UPDATE, or DELETE SQL statements that the end user can execute to perform business functions.

The overall structure of Mass Change is similar to that of PeopleSoft Query, except that Query retrieves data from the database, while Mass Change actually updates the database.

Mass Change is also similar to Application Engine, as far as its end results—updating the database. However, unlike Application Engine, Mass Change generates SQL for you. Also, Mass Change definitions contain no processing logic.

You can use Mass Change to:

Click to jump to parent topicDefining Types

Mass Change types determine the basic structure of the SQL statements that a Mass Change definition will generate. They define how many SQL statements will be generated, which records will be operated on, how they will be operated on, and the order in which the operations will take place.

Mass Change types and templates both require a PeopleSoft owner. Assigning an owner designates the PeopleSoft system from which the Mass Change type originated. The list of owners to choose from is pre-defined by PeopleSoft.

You define Mass Change types using pages in the Mass Change Type Component.

Note. To create a new Mass Change type, you must create one from scratch. Because there is no File, Save As menu option, you cannot clone an existing type.

To define a Mass Change Type:

  1. Open or add a type.

    To open an existing type, select Mass Change, Use, Mass Change Type you’ll be prompted for a Mass Change Type ID. Enter letters or numbers in the search field. Click Search to get a list of possible values.

    The Description page appears.

    You use the Description page in the Mass Change Type Component to enter details about how the type is to be used and who “owns” it.

  2. Select a PeopleSoft Owner, deselect Public Use, if desired, and enter a Description.

    The PeopleSoft Owner identifies the PeopleSoft system from which the type originates. Each type must have an owner. When the Public Use checkbox is selected, the Mass Change type will be available to other users. Deselecting Public Use prevents other users from being able to access this type. This option is selected by default.

  3. Select the Records and Join Fields page.

    This page is where you lay the foundation for the SQL statements that Mass Change generates.

  4. Enter the appropriate number in the Execution Seq field and define the SQL statement.

    The Execution Seq field indicates the order in which the statements will be executed. When you add a SQL Statement, you enter the Execution Seq number manually. The number cannot exist already.

    You can insert statements within an existing sequence by renumbering the subsequent steps, starting with the last step. For example, if the type consists of 5 statements and you want to add a new one between statements 3 and 4, you give statement 5 an Execution Seq value of 6, give statement 4 an Execution Seq value of 5, and create the new statement with an Execution Seq of 4.

    If you select the Free Form SQL option, you bypass Mass Change’s automated SQL generation process and will only be able to enter SQL into the last page in this Component. All other fields will be disabled.

    If you select Used for File Download/Upload, the system will automatically generate a SQL SELECT statement based on the structure of the table you are uploading from or downloading to. This is the only time the system will generate a SQL Select statement on its own initiative.

  5. Choose a Record and select the SQL Action you want to perform against it. Assign it a Sequence number.

    The second set of scroll arrows on the page controls which records each SQL statement will operate on, and in what manner. Each record is operated on in order, as indicated by the Sequence number. You manipulate record order the in same way as statement order (Execution Seq).

    For each Record you add to an SQL Statement, you select a SQL Action to be performed. The SQL Action you choose tells Mass Change what kind of SQL statement to generate and the Record values specify which records and fields to use in the statement.

    The following table explains the available SQL Actions:

    SQL Action

    Definition

    Use

    Delete

    Removes specified rows from the record.

    Eliminates data.

    Insert

    Inserts rows of data into the record. Data is supplied by one or more Select-type SQL Actions.

    If no Select action is added, adds rows with null values and any specified field default values.

    Adds data.

    Select Distinct

    Returns one row for each unique row retrieved from the record.

    Adds data without adding duplicate rows.

    Select Sum

    Returns one row; each field contains the field value total from all rows retrieved from the record.

    Adds one row of data reflecting the total of all field values from the specified rows.

    Select

    When used with Insert, returns specified rows from the record.

    Adds data.

    Update

    Updates specified rows in the record.

    Changes data.

  6. Add more Records, as desired.

    For each additional record, click the add row button. Then, repeat step 4.

  7. Add more SQL Statements, as desired.

    For each additional SQL Statement, click the add row button. Then, repeat steps 2 through 4.

  8. Save your work.

    Click Save to save this Mass Change type to the database.

  9. Select the Join Fields for each SQL Statement.

    Once you’ve selected the records and actions for a Mass Change type and saved your work, you can define the Join Fields that Mass Change will use to build a SQL SELECT clauses for the INSERT statement(s). You’ll be prompted with a list of all fields common to the records for which you’ve chosen a Select, Select Distinct, or Select Sum SQL action.

    Only two join fields appear on the page at one time. However, you can add as many additional rows as there are common fields. Use the scroll arrows to view all your join fields.

  10. Navigate to the Fields and Where page.

    This page is where you enter field defaults for the records you are updating or into which you are inserting information. These are usually system fields that are key to your system processing, but of which the end user is unaware.

  11. For each Insert or Update action row, define any system fields to be set.

    Enter a Field Name for which you want to set a default value, select the Field Action that will be performed on it, and a Value appropriate for the Field Action. To add more fields, click the add new row button.

    Field Action specifies how the field value will be updated. The Value field is used in a number of ways, depending on your Field Action selection. The following table explains Field Action options and usage:

    Field Action

    Definition

    Use

    Append

    Adds the text specified in Value to the existing string.

    Adds text to an existing text string. CHAR type only.

    Count

    Count.

    CNT (Business_Unit)

    Don’t put quotation marks around values. Mass Change will do this for you on character fields.

    Field

    Sets the field value equal to that of the field specified in Value.

    Copies the field value of one field into another.

    Max

    Sets the field value equal to the highest value found between the current value and the value of the field specified in Value.

    Finds the highest value between two fields.

    Sum Field

    Sets the field value to equal the current value plus the value of the field specified in Value.

    Adds two field values together.

    Value

    Sets the field value = Value.

    Hard-codes a field value.

  12. For each Insert, Update, or Delete action row, specify the Additional Where Clause, if any.

    You can use the Fields and Where page to append an Additional Where Clause to each SQL statement. The WHERE clause you specify will be appended to the SQL generated for the record experiencing an update, delete, or insert.

    You can insert substitution parameters into the additional WHERE clause. These parameters are identified by two dollar signs ($$) before and after them. For example:

    AND COST_BAL_VW.MIN_TRANS_DT <= MC_DEFN_AM_TRANS_DT AND COST_BAL_VW.PROCESS_INSTANCE = $$PI$$

    The system will supply the bind value when the SQL statement is executed. Valid parameters are:

  13. Save your work.

Free Form SQL Page

The last page in the Mass Change Type Component—Free Form SQL—enables you to enter customized SQL statements for a Mass Change type.

The only field on this page is Free Form SQL Statement. If this field is enabled—meaning you’ve selected Freeform SQL on the Records and Join Fields page, you can enter the SQL statement(s) you want the Mass Change type to use.

Note. It is not recommended to use the freeform option, as it can greatly complicate maintenance. You should be able to generate just about any SQL statement you need using the standard Mass Change pages.

Click to jump to parent topicGenerating SQL

In the sections to come, we explain how to make Mass Change generate SQL statements based on the information you entered in the type, template, and definition pages. However, in order to know what information to provide, you should understand a bit about how Mass Change uses that information.

As we explained earlier, the SQL Action values in the Records and Join Fields page tell Mass Change what kind of SQL statement to generate; the Record values specify which records and fields will be used in the statement.

Insert and Select SQL actions work together. If a SQL Statement contains an Insert action, Mass Change creates an INSERT statement and uses all Select-action records for creating the associated SELECT clause. It doesn’t matter where in the sequence the Selects and Inserts occur. If more than one Insert action exists in a SQL Statement row, additional INSERT statements will be generated, all using the same SELECT clause. In short, Mass Change can create multiple SQL statements using the information from one SQL Statement row.

The name SQL Statement on the Records and Join Fields page is a little misleading because Mass Change can actually generate a number of statements from one SQL Statement row, depending on your Record and SQL Action selections.

Typically, we recommend limiting your page selections to create just one SQL statement per SQL Statement row. For example, put an Update-action record in one SQL Statement row, put a Delete-action record in another, and so on. However, in cases where Inserts share a common SELECT clause, it makes sense to put them all in one SQL Statement so you don’t have to set up the Select records over and over. If you have multiple Inserts that each require a different SELECT clause, then you must put each Insert in its own SQL Statement row.

Note. SQL Statement rows that contain Select actions with no Inserts will not generate any SQL. Select actions must be associated with at least one Insert action.

Mass Change always includes the tables MC_DEFN and MC_DEFN_owner in the FROM portion of SELECT clauses. The fields in these tables are used to control execution and to add null values for time, date, and datetime fields, if necessary. Therefore, if you specify an Insert-action record without specifying at least one Select-action record, Mass Change will still generate a SQL statement. However, it will result in added rows that are empty except for any system field defaults or MC_DEFN fields that match up.

In addition, Mass Change always ends each SELECT clause with the following WHERE clause:

WHERE MC_DEFN.MC_DEFN_ID = '<definition_ID>' AND MC_DEFN.MC_DEFN_ID = MC_DEFN_<owner>.MC_DEFN_ID

If any other WHERE conditions are supplied—from join fields, criteria fields, or an additional Where clause—Mass Change appends them to this clause.

Click to jump to parent topicDefining Templates

Mass change templates take the SQL definition one step further. Templates enable you to control which fields will be available for the user to specify when defining a Mass Change definition, and whether those fields will be used as selection criteria or defaults. Criteria fields are used in the WHERE clause for the statement. Default fields are used in SELECT clauses in INSERT statements and in SET clauses in UPDATE statements.

You define Mass Change templates using the Mass Change Template Component.

Note. To create a new Mass Change template, you must create one from scratch. Because there is no File, Save As menu option, you cannot clone an existing template.

To define a Mass Change template:

  1. Open or add a template.

    To open an existing template, select Mass Change, Use, Mass Change Template you’ll be prompted for a Mass Change Template ID. Enter one and click OK. The Description page appears.

    The Description page in the Mass Change Template Component is where you assign a Mass Change type and an owner to the template.

  2. Select a Mass Change Type ID, and a PS Owner. Enter a Description.

    The Mass Change Type ID specifies the type on which the template will be based. This sets up the default record and field selections in the next page. The PS Owner field identifies the PeopleSoft system from which the template originates. Each template must have an owner.

    The Description should explain how and why you would use a particular template.

  3. Navigate to the Criteria and Fields page.

    You use the Criteria and Fields page to specify which fields will be used as selection criteria, and which will be used as defaults.

  4. Enter your Criteria Fields and Default Fields information.

    Criteria Fields are those fields that the end user will use to retrieve rows from the Select-, Update-, and Delete-action records identified in the associated Mass Change type. In other words, these are the fields to be used in the WHERE clause of the generated SQL statement.

    Default Fields are those to which an end user can assign a default value.

    Use the scroll arrows to view each SQL Statement. For each statement, select the Record and the Field Name for the criteria and default fields for which the end user will enter values.

    The Mass Change type associated with a template limits which Record and Field Name can be selected for each SQL Statement. Prompting on Record brings up a list of valid records for each statement. When a Record is selected, prompting on Field Name shows the valid fields. For each field selected, enter a descriptive Field Label or use the default.

    The Field Label text will appear as a display-only label above the corresponding field entry box in the Mass Change Definition pages—to guide the end-user.

    To add another field, click the add new row button.

  5. Click the Save button to save your work.

  6. Grant yourself access to the new template.

    Before you can use the template to build a definition, you must update your Mass Change Operator Security profile to include access to the template.

Click to jump to parent topicSelecting Prompt Tables

If you want the user to be able to prompt for criteria and default field values when creating a Mass Change definition, you must select a prompt table for each field using the Mass Change Prompt Records page.

To add a prompt record:

  1. Select Mass Change, Use, Mass Change Prompt Records, and follow the link to Add a New Value.

  2. Enter the Record (Table) Name and Field Name for which you want to set up a prompt table.

    The Prompt Records page appears.

  3. Select the Prompt Table.

    Enter the appropriate Prompt Table name, or select one from the drop-down list.

  4. Click the Save button to save your work.

Click to jump to parent topicConfiguring Date and Datetime Formatting

Each RDBMS handles date and datetime field data in different ways. You can instruct Mass Change to automatically format your date and datetime fields correctly using the Mass Change Datetime Parms page.

To specify the datetime formatting for an RDBMS:

  1. Select Use, Mass Change Datetime Parms.

    The Mass Change Datetime Parms dialog appears.

  2. Select a Database Platform and click OK.

    The Mass Change Datetime Parms page is displayed.

  3. Specify the appropriate prefixes and suffixes.

  4. Click the Save button to save your work.

Click to jump to parent topicBuilding Mass Change Definitions

Once you’ve properly set up the types, templates, prompt tables, and security, you can build a Mass Change definition. When you create a definition, all information will default from its Mass Change type and template, except for the criteria and default field values and users

You define a Mass Change definition by using the Mass Change Definition Component. We discuss the first page, Description, in the previous section. The remaining pages are where you identify criteria fields and default fields, and generate the actual SQL statements.

To add a new definition:

  1. Select Mass Change, Use, Mass Change Definition.

    The Mass Change Definition dialog displays:

    Here is where you identify the Mass Change Template on which the definition will be based.

  2. Select a Mass Change Template, specify an Archive ID and Archive Date, if desired, and enter a Description.

  3. In the Criteria and Defaults page, specify the Criteria and Defaults information.

    In the Criteria and Defaults page, you specify the values of the criteria and default fields defined by the template.

    The Criteria rows consist of a SQL operator (edit box on the left) and a value (on the right). When you specify an operator and a value, you are completing a WHERE clause condition for the field in question. You can only enter one SQL operator per Criteria Field. However, you can enter multiple values by adding rows.

    For each criteria field, select an operator and a value. For each default field, enter a value.

    The following table lists the valid operators, how they are used, and an example of each one as used on a set of Business Units that includes: CORP, FRNGN, NEWGN, SUBCO, and WORLD.

    Operator

    Meaning

    Example

    Result

    <

    Less than

    < NEWGN

    CORP, FRNGN

    < =

    Less than or equal to

    < = NEWGN

    CORP, FRNGN, NEWGN

    < >

    Not equal to

    < >NEWGN

    CORP, FRNGN, SUBCO, WORLD

    =

    Equal to

    = NEWGN

    NEWGN

    > =

    Greater than or equal to

    > = NEWGN

    NEWGN, SUBCO, WORLD

    >

    Greater than

    > NEWGN

    SUBCO, WORLD

    BTW

    Between value A and value B

    BTW CORP SUBCO

    CORP, FRNGN, NEWGN, SUBCO

    NBT

    Not between value A and value B

    NBT CORP SUBCO

    WORLD

    IN

    In a subset of

    IN CORP SUBCO

    CORP, SUBCO

    NIN

    Not in a subset of (complement)

    NIN CORP SUBCO

    FRNGN, NEWGN, WORLD

    LIK

    Like (used with a % wildcard)

    LIK NEW%

    NEWGN

    NLK

    Not like (used with a % wildcard)

    NLK %GN

    CORP, SUBCO, WORLD

    The Default Fields box displays fields that will be updated and allows you to enter a Value for each field. If you’ve created prompt tables for the fields on this page, you can select from a list of valid values for each field.

  4. In the Application Specific Fields page, enter any information required for the definitions for your particular application.

  5. Select the Generate SQL page.

    You use this page to create the SQL statement(s) based on the information you’ve contained in the type, template, and definition. Mass Change gives you the opportunity to check the SQL text generated by a Mass Change definition before actually executing it.

    You view the SQL statement(s) by clicking the Generate SQL button. The SQL text is created and displayed in the large, display-only, edit box.

    If you’re unhappy with the SQL, you can delete it, using the Clear SQL button, and rewrite the Mass Change definition, template, or type, as needed.

    Using the Count button in the lower left corner of the page displays the total number of rows affected by each statement.

    In this page, you can also opt to Execute SQL Upon Saving. If you select this checkbox, the SQL will be executed when you save the page if your Mass Change Operator Security profile authorizes you to execute definitions online. If you do not enable this option, you can save the Mass Change definition, then execute it in the background, using a run control.

  6. Review the statement(s).

    Check the statement text. Be sure any FROM or WHERE clauses reference the proper tables, fields, and values. As a further test, press Count. This displays the total rows affected by each statement.

    Are the totals what you expected? If everything checks out, continue to the next step. If not, redefine the definition, template, or type, as necessary.

  7. Select Execute SQL Upon Saving, if desired.

  8. Click the Save button to save the definition.

    If you selected Execute SQL Upon Saving, the definition begins executing.

Click to jump to parent topicCreating Groups

Quite often, you may need to execute a group of Mass Change definitions in series. Mass Change functionality makes it possible to define groups of definitions and execute them all using one run control ID.

You define groups using the Mass Change Group page.

Like types and templates, each group must have a single PS Owner. This designates from which PeopleSoft system the mass change group originates.

The Mass Change Definition field identities the definitions in the group. You can add as many as you like. Each one should be assigned an Execution Sequence number, which determines the execution order.

To create a group:

  1. Select Use, Mass Change Group, Add.

    The Mass Change Group page displays.

  2. Assign a PS Owner, and add Mass Change Definitions to the group.

    To add a definition, place your cursor in a definition field and click the add new row button.

  3. Assign Execution Sequence numbers to each definition.

    These numbers determine the order of execution.

  4. Click the Save button to save the group.

Click to jump to parent topicExecuting Mass Change Definitions

You can execute Mass Change definitions either online or in the background.

Click to jump to top of pageClick to jump to parent topicExecuting Online

To execute a Mass Change online, you must have permission granted in PeopleTools security (OK To Execute MC Online? selected).

To execute a Mass Change online:

  1. In the Mass Change Definition, Generate SQL page, select the Execute SQL Upon Saving checkbox.

  2. Click the Save button to save the page.

Click to jump to top of pageClick to jump to parent topicExecuting in the Background

To execute one or more Mass Change definition(s) in the background, you’ll first need to specify the definition or group you’d like to execute. You do this using the Run Mass Change page. When you run the definition or group, you’ll see the Process Scheduler Request dialog.

To execute a definition or group in the background:

  1. Select Process, Mass Change, Add.

    The Add -- Mass Change dialog appears.

  2. Enter a Run Control ID and click OK.

    The Process -- Mass Change page opens.

    The Mass Change Run Type options specify the kind of execution that will occur. In this section we discuss only Execute Single Mass Change and Execute Mass Change Group.

  3. Select either Execute Single Mass Change or Execute Mass Change Group.

  4. Select the desired definition or group from the Execution Parameters boxes.

  5. Click Run.

    The Process Scheduler Request dialog appears.

    In this dialog, you specify how, when, and where to execute the mass change.

  6. Enter the desired settings and click OK.

Click to jump to parent topicPerforming Mass Changes in PeopleSoft Asset Management

Because of the extreme power Mass Change has to change large amounts of data in the database, we strongly recommend that you do not use Mass Change to write directly to the database. Rather, we suggest you write to intermediary tables, so that you can review the changes and approve, delete, or correct them. Then use another SQR to load the data into the database.

In the Asset Management implementation of Mass Change, we write to three load tables, INTFC_FIN, INTFC_PHY_A, and INTFC_PHY_B. INTFC_FIN holds financial information; INTFC_PHY_A and _B hold non-financial information.

Our AM Utilities window contains pages for reviewing, editing, and approving data in the load tables. This same window also displays pages for running the AM Transaction Loader SQR, which loads the data in the load tables into the Asset Management database.

This setup affords the user some protection against making massive erroneous changes to the database. As an example, we’ll look at how to perform mass changes in PeopleSoft Asset Management.

Processing mass changes in PeopleSoft Asset Management consists of a definition phase and a processing phase. First, you define the selection criteria and changes to be made to the selected data, then you run the SQRs that carry out the changes you defined.

In general, you’ll complete the following steps.

To run Mass Change in Asset Management:

  1. Choose a mass change template and use it to create a mass change definition. Outline the criteria for selecting rows, and identify the columns and values to be changed.

  2. Run the mass change SQR to select, change, and transfer the data to the load tables.

  3. Preview the data for suitability (optional).

  4. Run the Transaction Loader SQR to load the data from the load tables into your PeopleSoft Asset Management tables.

Click to jump to parent topicDownloading and Uploading Data with Mass Change

Using Mass Change, you can download data from a single table to a sequential file. Conversely, you can upload that data back to the table from the sequential file. The Download/Upload procedure consists of two phases:

Preparing the File or Table Structure

Process

Description

Downloads

The purpose of this phase is to rewrite the MASSLAYO.SQC to reflect the table layout of the table you’re downloading. This rewrites part of the Mass Change SQR so that you can do the next step. Do this on a local version of the Mass Change SQCs, not your network copy. The MASSLAYO.SQC To Be Updated field is where you specify the actual copy of MASSLAYO.SQC to be rewritten. You may need to modify your SQR environment variables to ensure that you are executing the local copy of Mass Change.

Uploads

This step is similar to the first phase in downloading, explained in the previous section. The Mass Change SQR needs to rewrite itself, so that it can upload the data. Specify the file to be uploaded in the Download/Upload Data File field. Remember that this file contains a copy of the MASSLAYO.SQC, which was used to download the table as well as the actual table data.

Generating the File or Populating the Table

Process

Description

Downloads

After completing the first step, you’re ready to download the data to a sequential file. This file contains a copy of MASSLAYO.SQC that you generated in the previous step, as well as the actual table data. Specify the filename that will store this data in the Download/Upload Data File field. Keep track of this file, in case you need to upload the data later on.

Uploads

After completing the first step, you’re ready to upload the file data to the table it was downloaded from. Specify the file to be uploaded in the Download/Upload Data File field.

Note. Your SQR environment variables must be set to execute the local copy of Mass Change. A common mistake is to update a local copy of MASSLAYO.SQC in the file preparation step, but then use the network copy of MASSLAYO.SQC when executing Mass Change, due to improper setting of your SQR environment variables.