Defining 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:

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.
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.
Select the Records and Join Fields page.
This page is where you lay the foundation for the SQL statements that Mass Change generates.
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.

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.

Add more Records, as desired.
For each additional record, click the add row button. Then, repeat step 4.
Add more SQL Statements, as desired.
For each additional SQL Statement, click the add row button. Then, repeat steps 2 through 4.
Save your work.
Click Save to save this Mass Change type to the database.
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.
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.

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.

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:
- $$ARCHIVE_DT$$. Archive date.
- $$ARCHIVE_ID$$. Archive ID.
- $$OPRID$$. User ID.
- $$PI$$. Process Instance.
- $$RC$$. Return Code.
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.