Defining Array Elements
|
Page Name |
Definition Name |
Usage |
|---|---|---|
|
GP_PIN |
Name the element and define its basic parameters. |
|
|
GP_ARRAY_KEYS |
|
|
|
GP_ARRAY_PROCESS |
Define all required formula processing. |
Arrays enable you to retrieve data from external sources such as HR tables—data that you need to use, evaluate, or process in your payroll 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 Global Payroll variables.
These variables hold the column values and make them available for use in Global Payroll 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.
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 payment based on the pay period end date. If segmentation occurs and the array is on the event list or is being used in an earning, deduction, or absence element that's 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 earning, deduction, or 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.
Use the Array Name page (GP_PIN) to name the element and define its basic parameters.
Navigation:
This example illustrates the fields and controls on 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 deselects 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 Global Payroll element you are naming and defining. All of the fields on this page are documented in another topic in this product documentation.
Use the Field Map and Keys page (GP_ARRAY_KEYS) to do the following:
Select the database table for the FROM clause of the SQL statement.
Define the keys and retrieval criteria for the array and the WHERE clause of the SQL statement the system uses when retrieving data for the array.
Define the fields to be returned and the elements to be filled by the 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.
Navigation:
This example illustrates the fields and controls on the Field Map and Keys page.
Field or Control |
Description |
|---|---|
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 non payee-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 non payee-based array. Data is retrieved from the database every time the array is resolved. Reload for each segment: Select to create a non payee-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. |
See PeopleTools: Data Management product documentation.
Map Retrieved Fields to Variable Elements
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 or Control |
Description |
|---|---|
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 Global Payroll variable, but it cannot be ordered in more than one way (for example, both ascending and descending). |
Keys and Retrieval Criteria
Field or Control |
Description |
|---|---|
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 Other if 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. |
Element 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. |
Review Generated SQL Statement
Field or Control |
Description |
|---|---|
Log statement at run time |
Select this check box to view the text of the SQL statement dynamically generated by the array module during batch 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. |
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.
Use the Processing Formulas page (GP_ARRAY_PROCESS) to define all required formula processing.
Navigation:
This example illustrates the fields and controls on the Processing Formulas page.
Field or Control |
Description |
|---|---|
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.