Defining Report Data

Page Name

Definition Name

Usage

Define Report Data Page

GP_RPT_TBL

Assign the output table, writable array, primary input table type, and segment options.

Report Data Process Details Page

GP_RPT_DTL_SEC

Map fields and create filters used to generate the output table.

Use the Define Report Data page (GP_RPT_TBL) to assign the output table, writable array, primary input table type, and segment options.

Navigation

Set Up HCM > Product Related > Global Payroll & Absence Mgmt > Reports > Define Report Data > Define Report Data

Image: Define Report Data page

This example illustrates the fields and controls on the Define Report Data page.

Define Report Data page

Field or Control

Definition
Report Data

Displays the identification code for the report data setup.
Generate Log

Select to generate log details when you run the report.
Segment Writable Array

Enter a writable array segment. The corresponding writable array table can be used throughout the report data generation process in two ways. First, any field on this writable array table can be included in the field map. Thus it is used to populate fields in the output tables. Second, any field on this writable array table can be used as a filter for the process (using the parameter "SEGFILTER" on the Validate Report Data page). For example, for a writable array that contains the Company and Establishment fields, a process can be run for a single company or establishment.
Row Level Security

Select to enable row level security for the report. When row level security is enabled, the Report Data process selects payee records from EMPL_SRCH_GBL where the Row Security class equals the one defined for the user.
Run Time Security Override

Select to allow the Row Level Security value to be overridden on the run control page of the report.

Process Details

Field or Control

Definition
Sequence

Enter the sequence number. The same input and output tables may be used repeatedly.

Warning! The report data generation process uses this sequence number when generating output tables. The output table must have a SEQ_NUM field to hold the sequence number value.
Output Table

Enter the record name of the output table.

Primary Input Table

Select the primary table to be read for data selection. The Primary Input Table and the Output Table have a one to one relationship; for each row selected in the Primary Input Table there will be one row selected in the Output Table. The options available are:

  • Absence Daily Data (GP_RSLT_ABS).

  • Accumulators (GP_RSLT_ACUM).

  • Earnings and Deductions (GP_RSLT_ERN_DED).

  • Internal Worktable (GP_PYE_PRC_STAT joined with GP_PYE_SEG_STAT and a segment writable array).

  • Positive Inputs (GP_RSLT_PI_DATA).

  • Supporting Elements (GP_RSLT_PIN).

  • Writable Array Table (any writable array table).
Writable Array Table

When the Primary Input Table is Writable Array, enter the table name here. This writable array is not the same as the value in the Segment Writable Array field, which applies to the entire process.
Segment Option

Select which data will be selected if the process encounters retroactive information. Options are:

  • Original: Select only original segment information (no retroactive information).

  • Original and Retro: Select information coming from recalculated segments and prior segments in addition to the original segments.

  • Last Version/Revision: Select only the segments with the latest version and revision numbers.

  • User Defined: Select this option to manually choose the segment. Selecting this field displays the Last, Retro (Prior), Retro (Recalculated), and Original Calc fields.

Use the Report Data Process Details page (GP_RPT_DTL_SEC) to map fields and create filters used to generate the output table.

Navigation

Click the Details link on the Define Report Data page.

Image: Report Data Process Details page (1 of 2)

This example illustrates the fields and controls on the Report Data Process Details page (1 of 2).

Report Data Process Details page (1 of 2)

Image: Report Data Process Details page (2 of 2)

This example illustrates the fields and controls on the Report Data Process Details page (2 of 2).

Report Data Process Details page (2 of 2)

Additional Input Tables

Additional input tables can be used in addition to the Primary Input Table. To use additional tables in the process, a linking relationship must be defined on this page in order to retrieve the data. The relationship can be defined with either the primary input table or the output table.

Field or Control

Definition
Input Table

Enter an input table. Once a table is defined as an additional input, any of its fields can be used to populate a field in the output table.
Join with

Select one of the following options:

  • Output Table: This creates a one to one relationship. The additional input table will be read after the output table has been populated from the primary input table. Then the selected fields from the additional input table will be used in an update statement on the output table.

  • Primary Input: This creates a many to many relationship. The additional input table will be read during the selection of the primary input table. A join is made between these tables and thus the selected fields from the additional input table will be used in an insert statement on the output table.

    Note: Only one additional input table can be joined with the primary input table. This additional input table is called the link table and it can be filtered with an input filter.
Update Sequence

You must enter a sequence number when joining an input table with an output table. This sequence number determines the order of the different accesses to the additional input tables that are triggered by some update statements. There is one update statement per additional input table. For example, the field UNION_CD is populated with data from JOBCODE_TBL based on the SETID and JOBCODE defined in the output table. Then the field DISABILITY_INS is populated with data from UNION_TBL based on the UNION_CD. In this case the JOBCODE_TBL must have an Update Sequence value of 1 and the UNION_TBL must have an Update Sequencevalue of 2.
Join Condition Defined

The system uses this field to indicate that a join condition is defined between the additional input table and the primary input table or the additional input table and the output table, depending on the value in the Join with field. This field is read-only.
Generate Field Map

Use this push button to generate a field mapping based on the output table definition, the primary input table, the additional input tables, the writable array segment and the worktable. Once the field mapping has been generated, the user can change it using the Field Map group box.

Field Map

Once you have clicked the Generate Field Map button, the system populates this group box. You may then alter these fields.

Field Map Input Record and Field Tab

Field or Control

Definition
Output Field

Displays the field name of the output table. The fields of the output table are not required. You cannot select the fields that define the mandatory key structure.
Input Type

The following options are available:

  • Primary Input: A field from the primary input table populates the output field.

  • Internal Worktable: A field from the worktable populates the output field. Any field from GP_PYE_PRC_STAT and GP_PYE_SEG_STAT can be selected.

  • Additional Input: A field from a specific record populates the output field.

  • Element: An element populates the output field. The as of date can be specified in the As of Field field (field present in the output table).

  • Writable Array: A field from a writable array populates the output field. The as of date is the segment retro period.

  • Period Accum: A balance accumulator element populates the output field. The balance period is defined under Period Accum.

  • Writable Array Segment: The field is populated by the corresponding field present in the segment writable array, if one is defined.
Input Record

Enter a record name if Input Type is Additional Input or Writable Array. For other input types, the records' technical names appear.
Input Field

Displays the field name of the primary input, additional input, the worktable, the writable array segment, or a writable array.
Reverse Sign

Indicates if the field must take the opposite sign (+/-) when the result is attached to a prior amount. Usually, this field is selected for all the fields that can be summed. This option should not be selected for fields that do not contain values, such as PIN_NUM or INSTANCE.

Field Map Element Tab

The following fields are required if the Input Type is Element.

Field or Control

Definition
Entry Type

Select from: System Element, Variable, Formula, Bracket, Earning, Deduction, Auto-Assigned, Accumulator, Count, and Duration.

Element

This field is required if the Input Type is Element. Be sure to use the correct Country field.

Field Map Element Options Tab

Field or Control

Definition
Element Source

Required if the Input Type is Element or Writable Array. Determines how the element is read, in the case of retroactivity.

  • Original: Read in the original segment.

  • Recalculated: Read in the recalculated segment.
As of Field

Required if the Input Type is Element and the Element Source is Original. The field name is used for the As of Date to find the element. This field is populated by the output table.
Period Accumulator Type

Required if the Input Type is Period Accumulator. Values are: MTD Amount, MTD Unit, PTD Amount, PTD Unit, QTD Amount, QTD Unit, YTD Amount, and YTD Unit.

Access the Report Data Process Details page.

Field or Control

Definition
SQL

Write a where clause to execute during the selection of the primary input table. Aliases are available. For example:

  • To filter only positive amounts: "and PRI.CALC_RSLT_VAL>0".

  • To filter for one country: "and WRK.COUNTRY='USA'".

  • To filter on a field stored in an additional input table (if joined with the primary input table): "and LNK.PIN_TYPE='ER'".
Aliases available for Filter

This legend shows that the aliases that can be used in the SQL field. Use the button to refresh the legend. Aliases are as follows:

  • WRK: Represents the worktable.

  • PRI: Represents the primary input table.

  • LNK: Represents the record used as the additional input table that is joined with primary input table.

Note: You can use specific words in your SQL where clause. For example: "and WRK.PAY_ENTITY in (%PARM.PRMARG.1)". In this statement, the report data process will replace %PARM.PRMARG.1 with the appropriate value.