Defining Data Retrieval Elements

This chapter provides an overview of data retrieval elements, and discusses how to:

Work with system elements.
Work with arrays.
Work with writable arrays.
Define brackets.
Define rate codes.

Understanding Data Retrieval Elements

In Absence Management, you use data retrieval elements to retrieve data from the system.

There are four data retrieval elements included in the system:

System elements
Arrays
Brackets
Rate codes

PeopleSoft defines and delivers system elements; you name and define the basic parameters of other elements, such as arrays and brackets.

Background Processing

During processing, the system truncates data that is retrieved by system elements and arrays when the data exceeds these lengths:

Character fields: 30

Numeric fields: 12.6

See Also

Defining Element Names

Working with System Elements

This section provides overviews of system elements and background processing of system elements, and discusses how to:

Name and define system elements.
View system element details.
View system element comments.

Understanding System Elements

System elements are delivered and maintained by PeopleSoft and usually contain payee-related data for use in calculations. Although you cannot add system elements, you can rename them, if necessary, and define various parameters that control their use.

There are two types of system elements:

Database system elements, which come from a predefined list of database records and fields.
System-computed elements, which come from internally stored data.

System-computed elements are resolved at different times, for example, at the beginning of calculations, daily, or when a rate code element is encountered, depending on the element's purpose.

Note. To store the value of a database system element for reporting purposes, include the element in your process.

Understanding Background Processing of System Elements

All system elements, whether they are database or system-computed elements, have several important considerations. A system element that is frequency controlled is annualized by the Frequency field and then deannualized, based on the calendar frequency. Thus, any absence element that uses the Frequency field should be defined as having a frequency equal to the use calendar period frequency. This approach ensures correct results, avoiding additional annualization or deannualization.

Database System Elements

For database elements from effective-dated records, the row selected is based on context. Typically the value is as of the segment end date. If referenced during resolution of a sliced element, the value is as of the slice end date. When retrieving values from job records, for example, the system matches on employee ID, employee record, and the segment and slice end date.

System element definitions for the country being processed and those used by all countries are loaded at the beginning of an absence run. System Element Names (PINS) are resolved when used in the absence process.

Note. Only database system elements that are used and have been defined as Store = Yes are stored in the result tables.

PeopleSoft Enterprise Human Resources Database System Element Records

The following table lists the database tables that populate database system elements and describes the records and rows that are used in the Payee Data Manager program:

Table Name	Description
PERSON	Not effective-dated. Join based on employee ID.
PERS_DATA_EFFDT (personal data effective date)	Max Effdt (maximum effective-dated) row is less than or equal to the segment end date. If referenced in a slice, looks at Max Effdt rows that are less than or equal to the slice end date.
JOB	The Max Effdt row is less than or equal to the segment end date. If referenced in a slice, looks at Max Effdt rows that are less than or equal to the slice end date.
JOB_DATES	Effective-dated. Same as JOB.
PER_ORG_ASGN (person organization assignment)	Not effective-dated.
PER_ORG_INST (person organization instance)	Not effective-dated.
CONTRACT_DATA	Not effective-dated. There are no employee ID or employee record number fields on record. Join based on CONTRACT_NUM (contract number) from the selected job record.
WKF_CNT_TYPE (workforce contract type)	The Max Effdt row is less than or equal to the segment end date where the contract number on this row matches the contract number on job. You can use CONTRACT_NUM from JOB or from CONTRACT_DATA, because the system synchronizes them. If the contract number is referenced in a slice, use the slice end date.
COMPENSATION	Includes all comp_effseq rows for each rate code (comp_ratecd) where Effdt and Effseq (effective sequence) match Effdt and Effseq from the Job table.

Note. These Human Resources tables are discussed in detail in the Human Resources documentation.

See Increasing the Workforce.

System-Computed Elements

System-computed elements appear on the output results table only if they are used, provided that the appropriate output options on the Source And Use page are selected.

During background processing, all database system elements are retrieved and stored in arrays, whereas system-computed element values are retrieved and set by the appropriate processing module. For example, when you process absences, the system populates only those system elements that are specific to absences.

With system-element processing:

The system populates the first active segment, based on all the active segments that are created for a specific process stat record.
The system also populates the last active segment, based on all the active segments that are created for a specific process stat record.

Pages Used to Modify and View System Elements

Page Name	Object Name	Navigation	Usage
System Element Name	GP_PIN	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, System Elements, System Element Name	Name the element and define its basic parameters.
Source And Use	GP_SYSTEM_PIN	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, System Elements, Source and Use	Displays whether a system element is a system-computed element or a database system element. For a database system element, it also displays the record and field that populates it and indicates whether the field is setID-controlled.
System Elements by Source	GP_SYSTEM_PIN_INQ	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, View System Elements by Source, System Elements by Source	View system elements by source.
Comments	GP_PIN_SEC	Click the Comments link on the System Element Name page.	View detailed comments about system-computed system elements that Absence Management delivers.

Naming System Elements

Access the System Element Name page.

Note. You name every element and define its basic parameters on an element name page with the object name of GP_PIN. The page title and general appearance of this page change based on the type of Absence Management element that you name and define. All of the fields on this page are documented in another chapter in this PeopleBook.

For each system-computed system element that is delivered by PeopleSoft, you can view detailed comments including a general description, when the system element is available, and where the element is used by clicking the Comments link. This is useful information when you want to learn more about how the system uses a specific system element.

See Also

Defining Element Names

Viewing System Element Details

Access the Source And Use page.

Record

Displays the record that the system element populates.

Field Name

Displays the field from which the system element obtains its information.

SetID Controlled

Selected if the field is controlled by setID. Elements that are controlled by setID have field name and prompt view values.

Use As Chart Field

This check box is not applicable to Absence Management.

Prompt View

Elements that are controlled by setID have a prompt view specified. If a setID-controlled system element is used as a supporting element override for positive input, it also requires a prompt view.

SetID Element

Displays the setID field name for the system element.

See Also

Business Units, Tablesets and SetIDs

Viewing System Element Comments

Access the Comments page.

For each system-computed system element that is delivered by the PeopleSoft system, you can view detailed comments including a general description, when the system element is available, and where it is used by clicking the Comments link on the System Element Name page. This is useful information when you want to learn about what a specific system element does.

Defining Array Elements

This section provides an overview of arrays and background processing of arrays, and discusses how to:

Name an array.
Select and define information about the SQL statement.
Define fields retrieved by an array.
Define formula processing for an array.

Understanding Array Elements

Arrays enable you to retrieve data from external sources such as Human Resources tables—data that you need to use, evaluate, or process in your absence rules.

To build an array, you assemble a Structured Query Language (SQL) statement out of FROM, SELECT, and WHERE clauses using the Array Definition component.

To define the FROM clause, you identify the table containing the required data in the Record (Table) Name field on the Field Map and Keys page.
To build the SELECT clause, you identify the table columns (fields) containing the data in the Fields Retrieved group box on the Field Map and Keys page.
To build the WHERE clause, you define the array keys and the conditions under which rows of data are to be pulled from the database table based on the values of the array keys.

To do this, you use the fields in the Retrieval Criteria group box on the Field Map and Keys page.
After defining the SQL statement, you must map the database column values in the array to Absence Management variables.

These variables hold the column values and make them available for use in Absence Management rules.

To do this, use the Variable Element Name field on the Field Map and Keys page.

Note. After you construct your SQL statement, you can also define any formulas; you need to evaluate the data retrieved by the array.

Do this on the Array Processing Formulas page.

Note. Arrays are not effective-dated. Any changes are likely to affect retroactive processing.

Understanding Background Processing of Arrays

Arrays can be used to access data in database tables or views that are not delivered by system elements. They do not resolve to a value but instead invoke processing.

You can create either payee-based arrays or non-payee-based arrays on the Field Map and Keys page. Payee-based arrays are resolved as of the slice or segment that is currently being resolved.

Arrays must be resolved for every segment and slice. If any element set by an array is used by another element that's being sliced, the array itself should be added to the event list to ensure that the array and all return column elements are also sliced. You must enter the array into the event list.

Payee-Based Array Processing

The system performs these steps when processing payee-based arrays:

Arrays call the database the first time that they are encountered in a calendar run.

All of the rows of data that fit the WHERE criteria (based on keys entered) are pulled into memory. The cursors are sorted by employee ID in ascending order, by employee record number in ascending order, and by effective date in descending order.
For each payee, a cursor is set to access the appropriate rows of data (the Payee and Effective Date fields based on field use on the Field Map and Keys page).

Payee-based arrays are aligned per absence based on the period end date. If segmentation occurs and the array is on the event list or is being used in an absence element that is being sliced or segmented, the array must be aligned by slice or segment end date.
The array process formulas are applied against the data that is stored in memory (for the payee cursor set above), based on the process code.
The database field is resolved to the last row of data against which the process formulas were applied.

Whenever the array is accessed, steps 2 through 4 are performed again. Step 1 is performed only if the payee has changed or if a new slice or segment is being resolved.

Non-Payee-Based Array Processing

When processing non-payee-based arrays, the system performs the following steps:

Arrays call the database the first time they are encountered in a calendar run.

All rows of data that fit the WHERE criteria (based on keys entered) are pulled into memory, so most effective-dated logic should be within the process formula logic.
If the table is effective-dated, the lookup formula references the system element that refers to the correct date (segment, slice, period).

If segmentation occurs and the array is on the event list or is being used in an absence element that’s being sliced or segmented, the array must be aligned by slice or segment end date. Arrays can return multiple rows from the database. The process formulas are applied against the rows.
The array process formula is run.
The database field is resolved to the last row of data against which the process formula was applied.

Whenever the array is accessed, steps 2 through 4 are performed again. Step 1 is performed only if the Reload for Each Resolution or Reload for Each Segment value is selected on the Field Map and Keys page.

Pages Used to Define Array Elements

Page Name	Object Name	Navigation	Usage
Array Name	GP_PIN	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Arrays, Array Name	Name the element and define its basic parameters.
Field Map and Keys	GP_ARRAY_KEYS	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Arrays, Field Map and Keys	Select the database table for the FROM clause of the SQL statement and define the values for the WHERE clause of the SQL statement the system uses when retrieving data for an array. Define the fields within an array. This provides the data for the SELECT clause of the SQL statement and determines the elements to resolve from this array call. You also use this page to define the sort order for retrieving rows from a database.
Processing Formulas	GP_ARRAY_PROCESS	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Arrays, Processing Formulas	Define all required formula processing.

Naming an Array Element

Access the Array Name page.

Because arrays are temporary tables that store results only during processing, storing the results after processing isn’t necessary. The system clears the Store and Store if Zero check boxes and makes them unavailable for entry.

In addition, arrays are not effective-dated, so this page has no definition as of date. To change an array definition, create a new array and new effective-dated elements that reference it. If a database table or view is renamed, create a new array.

Note. You name every element and define its basic parameters on an element name page with the object name of GP_PIN. The page title and general appearance of this page change based on the type of Absence Management element you are naming and defining. All of the fields on this page are documented in another chapter in this PeopleBook.

See Also

Defining Element Names

Arrays Used in Background Processing (Technical)

Selecting and Defining Information for the SQL Statement

Access the Field Map and Keys page.

Record (Table) Name

Select the table for the SQL statement to use from the list of PeopleSoft defined tables. In the FROM clause of the SQL statement, the system appends the prefix PS_ to the selected table name.

You can also select views. The prompt list displays all SQL tables and views in the database.

Loading Option

Select a value to control how often the array data are refreshed from the database.

Values are:

Employee-based look-up: Select to create a payee-based array.

Data is retrieved once for each payee. When that person is processed, the array storage becomes available for the next payee.

Load once (small table):Select to create a nonpayee-based array. Data is retrieved only once the very first time the array is referenced within the process. Compared to the two “Reload” alternatives, this option can significantly improve performance because the process only accesses the database once to load data. As indicated, this should be used only with smaller tables. The buffer for all arrays with this option combined can only hold 5000 rows. If the data in the table is changed by the process itself (and it is desirable to reflect these updates in the process, Load once is not a good option.

Reload for each resolution: Select to create a nonpayee-based array.

Data is retrieved from the database every time the array is resolved.

Reload for each segment: Select to create a nonpayee-based array.

Data is retrieved from the database once per segment being processed, regardless of the number of times the array may be resolved within each segment.

Note. The loading option that you select controls the key types that you can enter in the Key Type field.

Log statement at run time

Select this check box to view the text of the SQL statement dynamically generated by the array module during background processing. You can direct the output display into a file by selecting the Redirect Output option in PeopleTools Configuration Manager.

View Resulting Query

Click to view the SQL statement in real time. The system displays what SQL is to be created to pull data into the array, and lists how many rows are in the table defined in the Record (Table) Name field.

See Enterprise PeopleTools PeopleBook: Data Management.

Retrieval Criteria

Key Type	Select a key type. If the Loading Option is Employee-based look-up, the key types are Effdt (effective date), EmplRcd(employee record number), EmplID (employee ID), and Other. Select Otherif you want to use any other database field in the table that you are querying as a key. If the Loading Option is Load once (small table), Reload for each resolution, or Reload for each segment, all key fields are defined as Other. When specifying keys for a user-defined array, you generally define at least one key, but the system enables you to save this page without specifying keys. (In this case, the system loads the entire table.) Warning! Changing the array keys clears the data in the Keys and Retrieved fields and the Processing Formulas pages.
Field	Enter the exact name of the record (table) column as defined in the database. This is the first half of the equation in the WHERE clause. For example, suppose that you have the equation WHERE EmplID equals System Element Payee ID1, EmplID is what you enter underField. The field name must be the exact name of the column in the table in the database—not the field label or description
Operator	Indicates the user in the WHERE clause. You can enter an operator only if the key type is Other. Valid operators are: <, <=, <>, =, >=, >, and N.A.
Entry Type	Select Bracket, Formula, System Element, or Variable. You can enter an entry type only if the key type is Other.
Element Name	Select the element for the second half of the WHERE clause. For example, in the equation WHERE EmplID equals System Element Payee ID1, Payee ID1 is the element name. You can enter an element name only if the key type is Other.

Fields Retrieved

Specify the columns to be retrieved from selected rows in the Array - Fields Retrieved group box (this is the SELECT part of the SQL statement). Also specify the variable elements to populate with the selected columns’ values. The system orders the Keyed by Employee arrays by employee ID (and employee record number and effective date, if specified), with employee ID and employee record number in ascending order and effective date in descending order.

Field Use	Select from these options: Monetary: When monetary conversion is required. Pointer: To specify that a column contains a PIN. Other: (default) For all other cases.
Field	Select a field from the list of columns in the database table. The system displays all columns in the database table that can be used in the SELECT AND/OR ORDER BY clause.
Variable Element Name	Define the host variable to populate with the value of the selected field. The Variable Element Name field is not required. If this field is blank, only the database field name column value is used in the ORDER BY clause. Note. You cannot use the same variable name for two or more different field names.
Currency Field	If the field being retrieved from the database stores a monetary amount, enter a currency field name, indicating the column name of the field in the database that stores the corresponding currency code. If the currency code doesn’t equal the processing currency, the system converts the monetary amount in the database to the processing currency. This field is available for entry only if you select Monetary in Field Use.
Order By	Define the sort order—ascending, descending, or none—for retrieving rows from the database. This is crucial because, depending on the defined lookup formula, you may want only the first row retrieved or use special formula logic dependent on the order. Note. The same field can be assigned to more than one Absence Management variable, but it cannot be ordered in more than one way (for example, both ascending and descending).

Using System Elements as Key Values

Depending on your loading option, you can use EmplID, EmplRcd#, and Effdt as key fields.

If you select EmplID or EmplRcd as array keys, the system hides the Operator and Element Name fields and assumes an operator of equal to (=) current EmplID and EmplRcd#. For Effdt, the system assumes an operator of less than or equal to (<=) the date specified in the Compare Effdt (effective date) against field.

For example, if you select Employee-based look-up and enter a key type of EmplID (using field name EmplID), and a key type of EmplRcd# (using field name, EmplRcd#), the system builds a SQL statement that retrieves data for the current payee only.

Using Other as a Key Type in Payee-Based and Non-Payee-Based Arrays

If you are not using one or more of the three provided array key elements—or you want entries beyond those—specify a key field name, an operator, an entry type, and the correct element name.

Select the correct operator for the key field (record column) in the WHERE clause.
Select an entry type.
Enter an element name corresponding to the entry type—this must be a defined element of the type that you selected.

This is the data that the system uses to build the WHERE clause of the SQL statement that it needs for retrieving data for the array. You can enter multiple key fields for your definition.

Note. If you enter multiple rows on the page, the multiple conditions are processed as AND conditions.

Example: User-Defined Key Structure

Say that you’re searching the database for a row of data with a matching department ID.

You enter Other DEPTID = System Element DEPTID. The system looks for a row in the Department table (DEPT_TBL) with a DEPTID (department ID) that equals the value in the system element DEPTID for the payee currently being processed when it encounters this array.

Defining Formula Processing for an Array Element

Access the Processing Formulas page.

Processing Option

Select one of these values to determine when and how the system applies a formula. Values are:

By Formula, Apply all Rows: The system selects all the rows required for the array, applies the first formula to all rows, applies the second formula to all rows, and continues for all formulas.

By Row, Apply all Formulas: The system selects a row of data from the database and applies each formula on this page to that row. It then selects the next row and applies each formula to that row, continuing for all rows.

Look-up: The system selects a row of data from the database, applies each formula on this page to that row, selects the next row, and applies each formula to that row. The first formula that resolves to a value of 1 stops the loop. So if you are searching data for a particular value, the system stops looking when it finds that value.

Note. With regards to arrays with look-up processing, if you select a value of Look-up, but do not specify a formula value in the Formula Name field, the system uses the first row of data returned by the array.

Error Formula

Select an Error Formula Name for the system to use during error processing, if no rows are found.

Formula Name

Select the formula that the system applies to each row of data to resolve the array.

If you selected Look-up as the array processing option, select a Boolean formula here.

Note. Instead of using a formula, consider defining the array so that the desired row of data is always ordered first. Also, if the array will never return more than one row of data, and this row will always contain the desired data, no Formula Name field value is necessary with the processing option of Look-up.

Note. The system automatically assigns a sequence number to each formula. The sequence is unchangeable. If you make a mistake, delete all the items back to the mistake and add the formulas again in the correct order.

See Also

Defining Formula Elements

Defining Writable Array Elements

This section provides overviews of writable arrays, storage considerations for writable arrays, and background processing of writable arrays, and discusses how to:

Name a writable array.
Define writable array records and fields.

Understanding Writable Array Elements

Writable arrays are elements that you can use to populate user-defined result tables. You use PeopleSoft Application Designer to create the result table, then you use the writable array pages that are described in this section to define the element that populates the table during background processing. Writable arrays are in many ways the opposite of standard arrays. Instead of writing values from a row in a table to user-defined variables, a writable array writes the values of user-defined elements into a row in a table.

Writable arrays make it easy for you to design reports that exactly meet your needs—each row in your table can produce a corresponding line of information in your report. You can also combine multiple tables to create a report, for example, use one table for a report header, one for the body of the report, and another for the footer.

Finally, writable arrays can also conserve valuable storage space by consolidating many vertical rows of information into a single horizontal row.

To define a writable array:

Use Application Designer to create the table that the writable array element populates.

The first seven fields in the table must be the same as the keys in the basic result tables. You can also use some of the optional result table keys, such as INSTANCE or SLICE_BGN_DT, and include keys that are not used in the basic result tables, such as DEPTID.

The six fields are:
- EMPLID
- CAL_RUN_ID
- EMPL_RCD
- GP_PAYGROUP
- CAL_ID
- ORIG_CAL_RUN_ID
- RSLT_SEG_NUM
On the Writable Array Name page, define the naming information for the writable array.
On the Definition and Fields page, identify the table that the writable array is to populate, and map the elements that are to populate the table to the corresponding fields (columns) within the table.

Note. When you create a result table using a writable array, the table must include the basic segment status keys shared by all the standard result tables. You can include additional keys as well.

Understanding Storage Considerations for Writable Array Elements

While custom result tables can conserve storage space, they can also increase the need for space if they duplicate data from the standard result tables. Consider using writable arrays to consolidate or temporarily store the following types of results:

Miscellaneous personal data required for audit purposes.

You might want to store these values in a single row instead of in the standard result tables and keep the records as long as you need them for auditing purposes.
Period summary data.

If you need to summarize data by period on year-to-date reports, you can save summary data for each period for as far back as needed.
Temporary data used in reports.

For reporting purposes, you may want to duplicate data found in the standard result tables but present it in a different form. You can then delete the data once you complete the reporting period.

Because you may want to delete temporary data at different times, we leave the management of the data in your result tables up to you.

Understanding Background Processing of Writable Array Elements

Typically, each call to a writable array element creates one row of data. To produce multiple rows of output, you can use loops within a subprocess section and arrays to call the writable array multiple times. Using pointers and variables, you can populate the same field in a writable array with values from different elements, as long as you do not map character and numeric elements to the same field.

Pages Used to Define Writable Array Elements

Page Name	Object Name	Navigation	Usage
Writable Array Name	GP_PIN	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Writable Arrays, Writable Array Name	Name the element and define its basic parameters.
Definition and Fields	GP_WRITABLE_ARRAY	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Writable Arrays, Definition and Fields	Identifies the table into which the writable array process inserts data. Also identifies the element that populates each column (field name) in the table.

Naming a Writable Array Element

Access the Writable Array Name page.

When a writable array resolves successfully, the element itself is set to one. Most likely, you will not want to store this value, so the Store and Store if Zero options on the Element Name page are not selected, by default. You can select them if you choose.

In addition, writable arrays are not effective-dated, so there is no definition as of date on the Writable Array Name page. If you need to change the element’s definition, create a new writable array instead, and then create new effective-dated elements that reference the new writable array. If you rename a database table or view, create a new writable array.

Note. You name every element and define its basic parameters on an element name page with the object name of GP_PIN. The page title and general appearance of this page change based on the type of Absence Management element that you are naming and defining. All of the fields on this page are documented in another chapter in this PeopleBook.

See Also

Defining Element Names

Defining Writable Array Records and Fields

Access the Definition and Fields page.

Record (Table) Name

Enter the name of the table that the writable array is to populate.

The system appends the prefix PS_ to the table name and uses the new name in the INSERT clause of a SQL statement.

Insert Rows Immediately

Select if you are defining a writable array to generate data for immediate use by a standard array in the same process. This ensures that the data is present in the database when the standard array element is processed.

If this check box is not selected, the system does not immediately write data to the database table that the array is populating. It inserts the data at a later time using a bulk insert process.

Field Name

Enter the name of the writable array field that you want to populate. The list of available options includes only those fields that are included in the table that you selected in the Record (Table) Name field.

The order in which you add field name is not important.

Entry Type

Select the type of element that will populate the field.

Element Name

Select the element whose value the system uses to populate the writable array key field that you are defining.

Note. During processing, the system populates the array with the current value of the selected element. (Linking an element to a writable array field does not, in itself, cause the element to resolve.)

Defining Bracket Elements

This section provides overviews of brackets, interpolation methods, and background processing of brackets, and discusses how to:

Define the lookup rules for a bracket.
Identify the search keys and return columns.
Enter lookup values.

See Also

Defining Element Names

Understanding Bracket Elements

Brackets enable you to create simple lookup tables. Based on a table that you define in a bracket, a bracket is used to look up data and assign certain values based on the lookup data. Brackets are also called bands and lookup tables.

Brackets can use one or more lookup values (search columns). Using a lookup value, bracket processing returns one or more columns that are available for use in other elements such as formulas.

Before defining a bracket, create any elements that you need to use in the bracket definition. For example, before building a bracket that lets you look up, for example, additional absence entitlements based on years of service, create the duration element that returns the years of service.

To define a bracket:

Define the naming information for a bracket on the Bracket Name page.
Define the lookup rules for the bracket on the Lookup Rules page.
Select the search key values and the return column values that you’ll use in the lookup table on the Search Keys/Return Columns page.
Enter the lookup values on the Brackets - Data page.

Understanding Interpolation Methods

When a search key doesn't exactly match the lookup values defined for the bracket (on the Bracket Data page), the interpolation method defines how the system calculates the values that the bracket returns.

You can select one of the following three interpolation methods on the Lookup Rules page:

Linear:

Uses a mathematical formula to create a prorated value based on the next-higher and next-lower keys (works only with brackets that have one or two numeric keys and with numeric result columns). With the linear interpolation method, you can use a rounding rule to specify the type of rounding for the returned value.
Use Nearest:

Uses the value from the row that has the nearest key (works only with brackets that have numeric or date keys).
Use Next Higher:

Uses the value from the row that has the next-higher key (works with any bracket).
Use Next Lower:

Uses the value from the row that has the next-lower key (works with any bracket).

Use Lowest and Highest Option

For the linear interpolation method, the system looks for the next-lower and next-higher rows to calculate the return value. When there isn’t a lower or higher row, you can use the Use Lowest/Highest Option check box on the Lookup Rules page to indicate whether the system should take the lowest or highest matching option.

Understanding Background Processing of Bracket Elements

In bracket processing, the system looks up a row based on the key values and returns a bracket value.

Brackets always resolve to the value of the first column returned. They are resolved as they’re encountered in the process. If an element in the return column of a bracket is encountered during processing, it doesn’t automatically invoke resolution of the bracket, because we don’t know whether the element is associated with this bracket.

Brackets and all return columns are populated as of applicable slice and segment end dates that are used when encountered during processing, with these criteria:

If a bracket is used by another element that’s being sliced, the bracket and all return column elements are also sliced.
If the bracket is encountered on the process list, it is resolved for the segment that’s currently being processed.
If a bracket return column needs to be re-resolved during segmentation, the bracket should be used in the calculation or the bracket should be on the event list.

How Brackets Use Interpolation Methods

Three factors need to be considered:

Interpolation Method (corresponding Use Lowest/Highest Option check box if Use Next Higher, Use Next Lower, or Linear is selected).
Outside Table Limits (only if there’s no match on the first key).
Error Processing Options (when there’s no match using appropriate interpolation methods and selections).

Two options are available. If Generate Error is selected, the system does not resolve the bracket (or any of the return columns) and puts the calculation in error. If Continue Process is selected, the system resolves the bracket and all the other return columns to be either 0 or blank (depending on the field format).

This table clarifies how background processing treats each interpolation method:

Interpolation Method	Batch Process
Use Nearest	If mismatch key is non-numeric, go to Error Processing Options. If mismatch key is numeric determine whether next-lower and next-higher values exist: If only next-lower value exists, use that row. If only next-higher value exists, use that row. If both next-lower and next-higher values exist, determine which value is nearer and return that row (if exactly halfway between, return the higher row).
Use Next Lower	Determine whether next-lower row exists: If next-lower row exists, use that row. If next-lower row doesn’t exist, look at the Use Lowest/Highest Option check box (if lower value doesn’t exist). If Use Lowest/Highest Option check box is selected, use the lowest row where keys match. If Use Lowest/Highest Option check box is not selected, go to Error Processing Options.
Use Next Higher	Determine whether next-higher row exists: If next-higher row exists, use that row. If next-higher row doesn’t exist, look at the Use Lowest/Highest Option check box (if higher value doesn’t exist). If Use Lowest/Highest Option check box is selected, use the lowest row where keys match. If Use Lowest/Highest Option check box is not selected, go to Error Processing Options.
Linear	If mismatch key is non-numeric, go to Error Processing Options. If mismatch key is numeric determine whether next-lower and next-higher values exist: If only next-lower value or next-higher row exists, look at Use Lowest/Highest Option check box. If Use Lowest/Highest Option check box is selected, use that row (no linear interpolation required). If Use Lowest/Highest Option check box is not selected, go to Error Processing Options. If both next-lower and next-higher values exist: In relation to the keys: Determine the difference between the next-lower and next-higher rows (Key Difference 1). Determine the difference between your value and the next-lower value (Key Difference 2). Determine ratio by dividing Key Difference 2 by Key Difference 1 (Key Ratio). For each return column: Determine the difference between the values for the next-lower and next-higher rows (Return Column Difference 1). Apply Key Ratio to the Return Column Difference (Return Column Difference 2). Add Return Column Difference 2 to the value from the next-lower row. This is the value that should be returned for the return column.

Additional Notes About Background Processing for Bracket Elements

Search keys are considered in the order in which they are entered on the bracket definition. Values are in ascending order (based on key order).

For all interpolation methods, if all keys match, use that row. For all interpolation methods, try to match on keys sequentially (first key, second key, and so on).

What to Do When Keys Are Mismatched

If there’s a mismatch on the first key:

Determine whether the mismatch is specified in Outside of the Table Limits.

If the first key value is under the key 1 value defined on the first row:
- If Use First Row if Under is selected, use the first row.
- If Use First Row if Under isn’t selected, go to Error Processing Options.
If the first key value is over the key 1 value defined on the first row:
- If Use Last Row if Over is selected, use the last row.
- If Use Last Row if Over isn’t selected, go to Error Processing Options.
If the mismatch isn’t specified in Outside of the Table Limits and no interpolation method is selected, go to Error Processing Options.
If an interpolation method is selected and the field format of the first key is non-numeric, go to Error Processing Options.
If an interpolation method is selected and the field format is numeric, go to the appropriate logic, based on the interpolation method process. See details of each interpolation method in the preceding table.
If there’s a mismatch on a subsequent key and no interpolation method is selected, go to Error Processing Options.
If there’s a mismatch on a subsequent key and an interpolation method is selected, consider only the rows where all keys have been matched.

Go to the appropriate logic based on the interpolation method process. Refer to the details of each interpolation method in the preceding table.

Pages Used to Define Bracket Elements

Page Name	Object Name	Navigation	Usage
Bracket Name	GP_PIN	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Brackets, Bracket Name	Name the element and define its basic parameters.
Lookup Rules	GP_BRACKET1	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Brackets, Lookup Rules	Define the lookup rules for a bracket.
Search Keys/Return Columns	GP_BRACKET2	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Brackets, Search Keys/Return Columns	Identify the search keys and the return columns for the bracket.
Brackets - Data	GP_BRACKET3	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Brackets, Data	Enter lookup values. The search key values and the return column values that you selected in the Brackets - Search Keys/ Return Columns page appear here.

Naming Bracket Elements

Access the Bracket Name page.

Note. You name every element and define its basic parameters on an element name page with the object name of GP_PIN. The page title and general appearance of this page change based on the type of Absence Management element that you are naming and defining. All of the fields on this page are documented in another chapter in this PeopleBook.

See Also

Defining Element Names

Defining Lookup Rules for a Bracket Element

Access the Lookup Rules page.

Error Processing Options

Select from the following values:

Generate Error: Stops processing and generates an error.

Continue Process: Continues processing without returning any value.

Outside Table Limits

Defines what happens if the key values are over or under those that are defined in the table:

Use First Limit if Under: Select if key values are under those that are defined in the table.

Use Last Limit if Over: Select if key values are over those that are defined in the table.

Interpolation Method

Select an interpolation method that defines the values to return if the system doesn’t find an exact match; for example, if the key values are between two rows on the table. All interpolation methods use only one search key, the first mismatched one. Select from the following values:

Linear: Uses a mathematical formula to create a prorated value based on the next-higher and next-lower keys (works only with brackets that have one or two numeric keys and with numeric result columns).

Use Nearest: Uses the value from the row that has the nearest key (works only with brackets that have numeric or date keys).

Use Next Higher: Uses the value from the row that has the next-higher key (works with any bracket).

Use Next Lower: Uses the value from the row that has the next-lower key (works with any bracket).

See Understanding Interpolation Methods.

Use Lowest/Highest Option

When using linear interpolation, the system matches on all search columns that have already been matched. Then it looks for the next-lower and next-higher rows where the search columns match. When there isn’t a lower or higher row where search columns match, you can use the Use Lowest/Highest Option check box to indicate whether the system should take the lowest or highest matching option.

See Understanding Interpolation Methods.

Rounding Rule Element

With linear interpolation, you can use a rounding rule to specify the type of rounding for the returned value. The system applies this rounding rule to all return columns whose field format is numeric (decimal or monetary), because linear interpolation takes the ratio of the next-lower return column value and the next-higher value and returns the prorated value based on the ratio that is above the next-lower value. The result might be an excess of decimal places.

See Understanding Interpolation Methods.

Identifying Bracket Search Keys and Return Columns

Access the Search Keys/Return Columns page.

Search Columns

In the Search Columns group box, select the keys that the system uses to search the bracket data. For each key, select the Search Key Type (element type) that you’re entering. Then select the corresponding Element Name. You can enter up to five search keys.

Return Columns

In the Return Columns group box, select the columns that tell the system where to store the values returned by the lookup. For each column, select the Return Entry Type(element type) that you’re entering. Then select the corresponding Element Name. The bracket itself is the first returned column. You can enter up to eight return columns.

Entering Bracket Lookup Values

Access the Brackets - Data page.

Bracket Search Keys and Return Column Values - Search Keys Tab

Enter the values of the search keys.

Bracket Search Keys and Return Column Values - Return Columns Tab

Enter the values to be returned for each search key value, based on your entries on the Search Keys/Return Columns page. You can enter multiple return values.

Defining Rate Code Elements

This section provides overviews of rate code mapping, the use of rate codes and background processing of rate codes, and discusses how to:

Name rate code elements.
Create a rate code element.

Understanding Rate Code Mapping

You can use rate code elements to retrieve multiple components of pay that have been defined in Human Resources—including base pay and nonbase-pay components.

To have the system retrieve the rate codes set up in Human Resources, matching rate code elements must be defined in Absence Management and mapped to the corresponding Human Resources rate codes. A Human Resources rate code can be associated with only one Absence Management rate code element.

Rate code elements can be defined and mapped to Human Resources rate codes in two ways:

If you install Absence Management after creating rate codes in Human Resources, use the Rate Codes - Definition page to create rate code elements and map them to the corresponding rate codes in Human Resources.

Mapping an element to a Human Resources rate code makes the element take on the values of the Human Resources rate code.

If Absence Management is installed when you create rate codes in Human Resources, matching rate code elements are created automatically in Absence Management.

You can display the rate code elements via the Rate Codes - Definition page in Absence Management. You can also use the page to map the element to a different rate code.

This table lists the values that appear on the Rate Codes - Definition page:

Field	Value
Name	Same as Human Resources Rate Code Name
Element Type	Rate Code (RC)
Description	HR Rate Code description
Field Format	Decimal
Use Defn As Of	Calendar Period End Date
Always Recalculate	Off (No)
Owner	Customer Maintained
Class	Not Classified
Used By	All Countries
Country	Not applicable since All Countries
Industry/Region	Blank
Category	Blank
Override Levels	All options set to Off (No)
Store	On (Yes)
Store if Zero	On (Yes)
Customer Fields	Blank
Comments	Blank

Understanding the Use of Rate Code Elements

Rate code elements return the values of rate codes defined in Human Resources. To use a rate code to calculate a payee’s absence information, use the rate code in the absence element definition—either directly, as part of the absence calculation rule, or within a formula or other element used by the absence calculation rule.

Understanding Background Processing of Rate Code Elements

When the system encounters a rate code element during the payroll process, it calls the Rate Code PIN Resolution program, which first determines whether the element, effective on the calendar period end date, represents a base pay rate code or a nonbase-pay rate code. It then follows the hierarchy for base pay or nonbase-pay rate code elements, as appropriate, to resolve the rate code.

Criteria for Selecting the Job Row

Absence Management uses the RATE AS OF DATE system element to determine to which maximum effective-dated job row it refers for the rate code.

If RATE AS OF DATE is unpopulated, the system uses the end date of the current slice or segment to select the job row.
If RATE AS OF DATE is greater than the calendar period end date, the system uses the calendar period end date to select the job row.
If RATE AS OF DATE is before the first effective date on job, the rate code resolves to 0 and the payment is put in error.
In all other cases, RATE AS OF DATE is used.

The FTE factor that applies to some rate codes is retrieved from the maximum effective-dated job row that is less than or equal to the RATE AS OF DATE or slice end date where the payment keys match.

Currency Conversion

When the currency code for a flat amount, hourly, or hourly plus flat amount rate code, as defined in Human Resources, doesn’t match the processing currency, Absence Management performs its standard currency conversion during processing. That is, it uses the payee’s effective-dated exchange rate type to perform the conversion.

Note. Currency conversion is not required on percent or point rate codes, because returned values are nonmonetary.

Frequency Conversion

Absence Management also performs frequency conversion on any flat amount or hourly plus flat amount rate code, where the corresponding frequency code in Human Resources doesn’t match the calendar period frequency. The system annualizes the rate code using the corresponding frequency factor from Human Resources. It then deannualizes for the calendar period frequency (using the applicable frequency factor). Define all earnings elements that use rate codes as Use Calendar Period Frequency.

Note. Frequency conversion is not required on percent or point rate codes, because returned values are nonmonetary.

Resolving Multiple Instances of the Same Rate Code Element

If the PS_COMPENSATION record contains multiple instances for the same rate code (base pay or nonbase-pay), the system evaluates each instance separately, sums the instances, and returns one value to the rate code element. Absence Management references two system elements, RATE CODE GROUP and FTE INDICATOR, and applies the following rules:

If the Rate Code Group differs between the instances, the system element RATE CODE GROUP is resolved according to the last instance and an error message is generated.
If the FTE indicator differs between instances, the system resolves the system element named FTE INDICATOR according to the last instance and issues a warning message. The payee is not put into an error status. ( Absence Management uses the FTE INDICATOR only for rate codes types of flat amount and hourly plus flat amount.)

For example, if a flat amount rate code has one instance in which FTE applies, Absence Management uses the FTE_COMPRATE for this instance. If a second instance indicates that FTE doesn’t apply, the system uses the COMPRATE field value for the second instance and sums the two instances.

Hierarchy for Resolving Base Pay Rate Code Elements

When the system encounters a rate code element that's mapped to a Human Resources base pay rate code, it finds the appropriate rows on the PS_COMPENSATION record, where the element matches the rate code. It then identifies the value to be returned, based on the rate code type, as shown in this table:

Rate Code Type	Fields Evaluated on PS_COMPENSATION	Value Returned for Rate Code
Percent	COMP_PCT	Percent
Points	COMP_RATE_POINTS	Points
Flat Amount	COMPRATE and FTE_INDICATOR	If FTE_INDICATOR = Yes, return COMPRATE * FTE factor (stored on JOB) If FTE_INDICATOR = No, return COMPRATE
Hourly	COMPRATE	Hourly rate

Hierarchy for Resolving NonBase-Pay Rate Code Elements

When the system encounters a rate code element that's mapped to a Human Resources nonbase-pay rate code, it derives the rate from the following hierarchy, stopping when it finds the rate:

Compensation table (PS_COMPENSATION).
Job Code table (PS_JOBCD_COMP_RATE).

The system looks for the row where the SetID and Job code fields match the SETID_JOBCODE and JOBCODE system elements.
HR Comp Rate table (PS_COMP_RATECD_TBL).

The system identifies the value to be returned, based on the rate code type and FTE_INDICATOR, as shown in this table:

Rate Code Type	Fields Evaluated on PS_COMPENSATION	Value Returned for Rate Code
Percent	COMP_PCT	Percent
Points	COMP_RATE_POINTS	Points
Flat Amount, or Hourly + Flat Amount	COMP_RATE FTE_INDICATOR	If FTE_INDICATOR = Yes, return COMPRATE * FTE factor (stored on JOB) If FTE_INDICATOR = No, return COMPRATE
Hourly	COMPRATE	Hourly rate

Common Elements Used in This Section

Multiple components of pay	This functionality enables your organization to compensate a payee at more than one rate of pay, such as regular pay and merit pay. Components can represent a flat amount, hourly rate, hourly rate plus flat amount, percentage of the worker’s compensation package, or salary points.
Base pay components	Components that contribute to a payee’s base pay are called base pay components. All base pay components are stored in the PS_COMPENSATION record in Human Resources.
Non-base-pay components	Components that do not contribute to base pay are called nonbase-pay components and may or may not be stored in the PS_COMPENSATION record. When you run the absence process, the system follows a hierarchy to determine the applicable rate.

Pages Used to Create Rate Code Elements

Page Name	Object Name	Navigation	Usage
Rate Code Name	GP_PIN	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Rate Codes, Rate Code Name	Name the element and define its basic parameters.
Definition	GP_RATE_CODE	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Elements, Supporting Elements, Rate Codes, Definition	Create a rate code element and map it to a rate code defined in Human Resources. You can also use the page to view rate code elements and change the mapping.

Naming Rate Code Elements

Access the Rate Code Name page.

Note. You name every element and define its basic parameters on an element name page with the object name of GP_PIN. The page title and general appearance of this page change based on the type of Absence Management element that you are naming and defining. All of the fields on this page are documented in another chapter in this PeopleBook.

See Also

Defining Element Names

Creating a Rate Code Element

Access the Rate Code - Definition page.

Compensation Rate Code

Enter the Human Resources rate code to which you want to map this element. When you select the rate code, the lower half of the page displays information that is defined for the rate code in Human Resources. You cannot change this information in Absence Management.

Frequency Conversion

This check box is selected by default to indicate that the system is to perform frequency conversion (annualization and deannualization) on the value returned by the rate code.

Clear this check box if you want the system to return the value from the rate code definition without performing frequency conversion.

Generate Warning

This check box applies only to rate codes that represent a base pay component. It is selected automatically to indicate that the system generates a warning message during background processing if it does not find the rate code on the payee’s compensation record.

Clear the check box if you do not want the system to generate a warning message in these situations.

Record	Displays the record that the system element populates.
Field Name	Displays the field from which the system element obtains its information.
SetID Controlled	Selected if the field is controlled by setID. Elements that are controlled by setID have field name and prompt view values.
Use As Chart Field	This check box is not applicable to Absence Management.
Prompt View	Elements that are controlled by setID have a prompt view specified. If a setID-controlled system element is used as a supporting element override for positive input, it also requires a prompt view.
SetID Element	Displays the setID field name for the system element.

Record (Table) Name	Select the table for the SQL statement to use from the list of PeopleSoft defined tables. In the FROM clause of the SQL statement, the system appends the prefix PS_ to the selected table name. You can also select views. The prompt list displays all SQL tables and views in the database.
Loading Option	Select a value to control how often the array data are refreshed from the database. Values are: Employee-based look-up: Select to create a payee-based array. Data is retrieved once for each payee. When that person is processed, the array storage becomes available for the next payee. Load once (small table):Select to create a nonpayee-based array. Data is retrieved only once the very first time the array is referenced within the process. Compared to the two “Reload” alternatives, this option can significantly improve performance because the process only accesses the database once to load data. As indicated, this should be used only with smaller tables. The buffer for all arrays with this option combined can only hold 5000 rows. If the data in the table is changed by the process itself (and it is desirable to reflect these updates in the process, Load once is not a good option. Reload for each resolution: Select to create a nonpayee-based array. Data is retrieved from the database every time the array is resolved. Reload for each segment: Select to create a nonpayee-based array. Data is retrieved from the database once per segment being processed, regardless of the number of times the array may be resolved within each segment. Note. The loading option that you select controls the key types that you can enter in the Key Type field.
Log statement at run time	Select this check box to view the text of the SQL statement dynamically generated by the array module during background processing. You can direct the output display into a file by selecting the Redirect Output option in PeopleTools Configuration Manager.
View Resulting Query	Click to view the SQL statement in real time. The system displays what SQL is to be created to pull data into the array, and lists how many rows are in the table defined in the Record (Table) Name field.

Record (Table) Name	Enter the name of the table that the writable array is to populate. The system appends the prefix PS_ to the table name and uses the new name in the INSERT clause of a SQL statement.
Insert Rows Immediately	Select if you are defining a writable array to generate data for immediate use by a standard array in the same process. This ensures that the data is present in the database when the standard array element is processed. If this check box is not selected, the system does not immediately write data to the database table that the array is populating. It inserts the data at a later time using a bulk insert process.
Field Name	Enter the name of the writable array field that you want to populate. The list of available options includes only those fields that are included in the table that you selected in the Record (Table) Name field. The order in which you add field name is not important.
Entry Type	Select the type of element that will populate the field.
Element Name	Select the element whose value the system uses to populate the writable array key field that you are defining. Note. During processing, the system populates the array with the current value of the selected element. (Linking an element to a writable array field does not, in itself, cause the element to resolve.)

Error Processing Options	Select from the following values: Generate Error: Stops processing and generates an error. Continue Process: Continues processing without returning any value.
Outside Table Limits	Defines what happens if the key values are over or under those that are defined in the table: Use First Limit if Under: Select if key values are under those that are defined in the table. Use Last Limit if Over: Select if key values are over those that are defined in the table.
Interpolation Method	Select an interpolation method that defines the values to return if the system doesn’t find an exact match; for example, if the key values are between two rows on the table. All interpolation methods use only one search key, the first mismatched one. Select from the following values: Linear: Uses a mathematical formula to create a prorated value based on the next-higher and next-lower keys (works only with brackets that have one or two numeric keys and with numeric result columns). Use Nearest: Uses the value from the row that has the nearest key (works only with brackets that have numeric or date keys). Use Next Higher: Uses the value from the row that has the next-higher key (works with any bracket). Use Next Lower: Uses the value from the row that has the next-lower key (works with any bracket). See Understanding Interpolation Methods.
Use Lowest/Highest Option	When using linear interpolation, the system matches on all search columns that have already been matched. Then it looks for the next-lower and next-higher rows where the search columns match. When there isn’t a lower or higher row where search columns match, you can use the Use Lowest/Highest Option check box to indicate whether the system should take the lowest or highest matching option. See Understanding Interpolation Methods.
Rounding Rule Element	With linear interpolation, you can use a rounding rule to specify the type of rounding for the returned value. The system applies this rounding rule to all return columns whose field format is numeric (decimal or monetary), because linear interpolation takes the ratio of the next-lower return column value and the next-higher value and returns the prorated value based on the ratio that is above the next-lower value. The result might be an excess of decimal places. See Understanding Interpolation Methods.