This chapter provides an overview of report data generation and discusses how to:
Define report data.
Validate report data.
Using report data.
This section discusses:
The data reporting process.
Filters.
Report data and segmentation.
Output table definitions.
User-defined parameter definition.
Batch processing setup.
Real time processing setup.
Building a transaction that uses report data.
Using the report data generation process, you can easily read payroll results tables and use this data to populate your own reports.
The report data generation process enables you to create reports that contain:
Corrective and forwarding retroactivity or a mixture of the two.
Segmentation, retro-segmentation, and segmentation cancelled by retroactivity.
Reverse calculations.
Global Payroll status, indicators, and other technical fields.
Pay group, pay entity, and payment key changes resulting from corrective retroactivity.
Calculation results stored in earnings, deductions, balance accumulators, accumulators stored with each calculation, absence daily data, processed positive input, generated positive input, supporting elements stored with each calculation, and specific result tables (writable arrays).
Multiple selections by process run.
Data based on a calendar group or a period of time.
Results from finalized and un-finalized calendars.
Payees selected from any table at the EMPLID level, adhering to HRMS security.
Appropriate payroll segments.
Selected elements.
Any and all slices.
Steps for Creating Reports Using the Report Data Generation process
To create reports using the report data generation process:
Assign output and input tables for the report on the Define Report Data page.
Define the rules used to create the output table on the Define Report Data page.
Map fields and create filters used to generate the output table on the Report Data Process Details page.
Selecting Payroll Results and Populating Output Tables
After you set up the input and output table details using the Define Report Data page, and map fields and define filters on the Report Data Process Details page, the system completes the following steps to select the payroll results and populate the output tables:
Select calendar groups.
Select eligible payees using payee lists, group builds, security, and payee filter. If a payee filter was created, it is applied here.
Join the payee process status table (GP_PYE_PRC_STAT) and the payee segment status table (GP_PYE_SEG_STAT).
Select the original segments. If a process filter was created it is applied here.
Insert the original, recalc, prior, and last version/revision segments into the worktable (GP_RPTW_TMP). The worktable now contains rows by payee for selected segments, recalculations, prior segments, and last version/revision segments. If a segment filter was created it is applied here.
Join the worktable, the primary input table, and the link table (the additional input table joined with the primary input table) according to the data on the Report Data Process Details page. The results are posted to the output table. Only the results derived from the worktable or the primary input table are populated. If an input filter was created it is applied here.
Update the output table with data from the additional input tables.
Overview of Generating Report Data 1
Overview of Generating Report Data 2
Four levels of filters exist: payee, process, segment, and input.
Payee Filter
This SQL parameter is used to filter the population. It is applied when the selection is based on a specific record or employment table.
Process Filter
This SQL parameter is used to filter the entire process in the original segments only. It can be based on any field in the payee process status or payee segment status tables. For example, “and COUNTRY = 'USA'” selects only U.S. payroll results.
Segment Filter
This field parameter is used to create a filter for every step of the process in every segment. It can be based on any field in the payee process status, the payee segment status, or the segment writable array table (if defined in the setup). For example, COMPANY 'GBI' makes a selection on the company GBI if COMPANY is stored in a segment writable array.
Input Filter
This SQL filter is defined in the setup for each step used to filter the primary input table, the additional input table joined with the primary input table (the link table), or the worktable. Input filters can be used as a filter or as an additional join condition. For example, “and PRI.CALC_RSLT_VAL>0” selects only the positive results.
When retroactivity is encountered, the method used to store report data depends on the retroactive method encountered. When the forwarding method is used, deltas are forwarded to the first slice in the first segment that matches the pay group, run types, and payment keys. In corrective mode, the deltas are not forwarded.
The Report Data feature manages four kinds of segments:
Original segments
Original segments correspond to all segments for version 1 revision 1 (V1R1) for a period. In this kind of segment, there is also a virtual segment. The virtual segment is created when there is corrective retroactivity but the pay group, run type, or payment key of the current period does not match the recalculated period.
Recalculated segments
Recalculated segments correspond to all segments other than V1R1 that were created during a calendar group run.
Prior segments
Prior segments correspond to the previous version/revision of all recalculated or reversal segments.
Last version/revision
Here is how the system interprets the following situations:
If a payee has only one calendar and only one segment, then the payee has one original segment.
If a payee has one calendar and two segments, then the payee has two original segments.
If a payee has two calendars and one segment, then the payee has two original segments.
If a payee has one calendar and one segment for the current period, and retro (forwarding or corrective) back to the twelve previous periods (with the same pay group and payment keys), then the payee has one original segment.
If a payee has one calendar and one segment for the current period, and forwarding retro back to the twelve previous periods (with the same pay group and two different payment keys), then the payee has two original segments — one active, one inactive.
If a payee has one calendar and one segment for the current period, and a corrective retro back to the twelve previous periods (with the same pay group and two different payment keys), then the payee has two original segments even if Global Payroll creates only one segment for the current period.
See Also
Defining Retroactive Processing
Output tables must be defined to store your selected results. These tables must include the following key structure:
Field Name |
Type |
Length |
Key |
Required |
PROCESS_INSTANCE |
Num |
10 |
KA |
N |
EMPLID |
Char |
11 |
KA |
Y |
CAL_RUN_ID |
Char |
18 |
KA |
Y |
EMPL_RCD |
Num |
3 |
KA |
N |
GP_PAYGROUP |
Char |
10 |
KA |
Y |
CAL_ID |
Char |
18 |
KA |
Y |
ORIG_CAL_RUN_ID |
Char |
18 |
KA |
Y |
RSLT_SEG_NUM |
Num |
4 |
KA |
Y |
GP_RPT_KEY |
Char |
22 |
KA |
Y |
SEQ_NUM |
Num |
3 |
KA |
Y |
The user-defined parameters are defined when setting up the input filter on the Report Data Process Details page. They must follow this syntax:
%PARM.<function>.<parameter>
Consider the following example using the filter And PRI.STATE in (%PARM.REPORT.STATE) and these parameters:
REPORT |
STATE |
CA |
REPORT |
STATE |
OR |
In this example, the generated SQL statement would include: And PRI.STATE IN ('CA','OR'). Below is an example of how you would use these user-defined parameters in the program:
&reportBatch.AddParamChar("REPORT", "STATE", "CA"); &_reportBatch.AddParamChar("REPORT", "STATE", "OR");
The batch process provides a public section called “EXTRACT” that can be called by an application engine process that prepares the payroll results for reporting. You must prepare the list of parameters. Then the EXTRACT section generates the SQL requests based on the parameters and executes the requests.
A section called “MAIN” is provided for testing purposes. The MAIN section is used by the delivered run control Validate Report Data. The parameters are managed by the delivered application classes via an object &reportBatch defined at the component level:
import GP_RPT_DATA:ReportDataClasses:*; Component ReportDataAE &reportBatch;
Section |
Step |
Main |
|
EXTRACT (public) |
|
EXECUTE |
|
The application engine is based on the state record GP_RPT_AET and includes the following dedicated temporary tables: GP_RPTC_TMP, GP_RPTO_TMP, GP_RPTE_TMP, GP_RPTS_TMP, and GP_RPTW_TMP.
Below is an example of the batch processing setup:
import GP_RPT_DATA:ReportDataClasses:*; Component ReportDataAE &reportBatch; &reportBatch.AddParamChar("EXTPRC", "COUNTRY", "USA"); &reportBatch.AddParamChar("EXTPRC", "NAME", "GP_REPORT"); &reportBatch.AddParamChar("EXTPRC", "TYPE", "P"); &reportBatch.AddParamChar("SELPYE", "SECUR", "Y"); &reportBatch.AddParamChar("SELCAL", "CALGRPID", GP_REGISTER_AET.CAL_RUN_ID); &reportBatch.AddParamChar("SELCAL", "FROMDT", GP_REGISTER_AET.FROM_DT); &reportBatch.AddParamChar("SELCAL", "TODT", GP_REGISTER_AET.TO_DT);
The call section name is EXTRACT, and the program ID is GP_EXT_AE.
Below is an example of real-time processing setup:
import GP_RPT_DATA:ReportDataClasses:*; Component ReportDataReal &report; Local number &instance; &report = create ReportDataReal(); &report.AddParamChar("EXTPRC", "COUNTRY", "USA"); &report.AddParamChar("EXTPRC", "NAME", "GP_REPORT"); &report.AddParamChar("EXTPRC", "TYPE", "P"); &report.AddParamChar("SELPYE", "SECUR", "Y"); &report.AddParamChar("SELCAL", "CALGRPID", GP_REGISTER_AET.CAL_RUN_ID); &report.AddParamDate("SELCAL", "FROMDT", GP_REGISTER_AET.FROM_DT); &report.AddParamDate("SELCAL", "TODT", DERIVED_GP.TO_DT); &instance = &report.GetInstance(); &report.ExecuteNow(); /* selection from the output tables (using &instance) can be done here in order to display results */
When building a transaction that uses report data, you should follow these guidelines:
Design one or more temporary tables for transaction purposes using the same key structure as the batch process. The tables are keyed by process instance.
The user transaction should contain inquiry parameters such as EMPLID or CAL_RUN_ID.
Create a push button or similar feature to launch a PeopleCode program to access the report data. It should run as follows:
The class ReportDataReal is initiated.
The PeopleCode transforms the inquiry parameters into report data parameters using the method .AppParamChar/Date/Num().
The method .GetInstance() is triggered. This method generates a process instance number using a table designed for report data.
The method .ExecuteNow() is triggered.
The PeopleCode can then load data from the temporary tables into the page buffers (using, for example, the process instance number returned by method .GetInstance()).
The content of the temporary tables must then be deleted using the process instance number.
If two users run the same transaction simultaneously, the system generates two different process instance numbers.
Output Table |
Name of the table to be populated. |
Primary Input Table |
The functional name of a Global Payroll result table. For example, GP_RSLT_ERN_DED would be referred to as the Result Table for Earnings and Deductions. |
Original Segment |
The segment represented by version 1 and revision 1. |
Recalculated Segment |
The segment that is not version 1 and revision 1. |
Prior Segment |
The segment that the Global Payroll core application used to calculate deltas. |
This section discusses how to:
Select output tables and primary input tables.
Map fields.
Create input filters.
Page Name |
Object Name |
Navigation |
Usage |
GP_RPT_TBL |
Setup HRMS, Product Related, Global Payroll & Absence Mgmt, Reports, Define Report Data, Define Report Data |
Assign the output table, writable array, primary input table type, and segment options. |
|
GP_RPT_DTL_SEC |
Click the Details link on the Define Report Data page. |
Map fields and create filters used to generate the output table. |
Access the Define Report Data page.
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 operator. |
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
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:
|
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:
|
Access the Report Data Process Details page.
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.
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:
|
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 selected the Generate Field Map button, the system populates this group box. You may then alter these fields.
Field Map Input Record and Field Tab
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:
|
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.
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
Element Source |
Required if the Input Type is Element or Writable Array. Determines how the element is read, in the case of retroactivity.
|
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.
SQL |
Write a where clause to execute during the selection of the primary input table. Aliases are available. For example:
|
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:
|
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.
This section discusses how to validate report data.
The Validate Report Data page enables you to test a defined report with specific parameters so that you can validate the results. With this page you can:
Run the report data generation process in batch mode.
Run the report data generation process in real time.
Generate SQL requests.
View generated report data.
Page Name |
Object Name |
Navigation |
Usage |
GP_RC_RPT |
Setup HRMS, Product Related, Global Payroll & Absence Mgmt, Reports, Validate Report Data, Validate Report Data |
Enter report data parameters. Run the report data generation process in batch mode or real time. Generate SQL requests. |
Access the Validate Report Data page.
Run Mode |
Select one of three options:
The Run Mode determines how the SQL statements are applied. |
Script Location |
Enter the location of the script. Data Mover can execute the script only if you selected Script (Data Mover) in Run Mode. Otherwise the generated scripts can be used to debug the SQL statements. |
Function and Parameter |
Enter the functions and parameter that the system will use to select data for the report. For example, by entering SELCAL/CALGRPID you can then select which calendar group ID data to use in the report. Defined values are listed in the expandable Functions and Parameters group box. Note. You can also create user-defined functions and parameters to use in the input filter. |
Type |
Indicate the field type of the Parameter. Select either Char, Date, Numeric, or SQL. |
Character Value |
Enter the value of the Function or Parameter. |
Create Scripts |
Click to create a .dms containing the SQL statements and to display them on the page. The script includes a list of parameters used to generate the statements. If a parameter is not used in the process (because of a syntax error or a missing definition) the words “Not used” appear in the list. |
Validate Report Data |
Select this option to validate the report data. The process used depends on your Run Mode selection:
|
Below is a list of predefined function and parameter values.
Function |
Parameter |
Parameter Value |
Comment |
EXTPRC |
COUNTRY |
Country Code |
This parameter is required. |
EXTPRC |
NAME |
Report Data |
This parameter is required |
EXTPRC |
TYPE |
P or A or B |
Select one of the following:
|
SELPYE |
SECUR |
Y/N |
Use this parameter to apply security to payee selection. |
SELPYE |
RECORD |
Record Name |
Payee selection is based on this record. It must include EMPLID and EMPL_RCD. |
SELPYE |
SQL |
SQL Statement |
When the parameter value field is not long enough, the user can repeat the same parameter. The system will concatenate the different values. |
SELPYE |
GRPBUILD |
Group Build Code |
|
SELPYE |
GRPVER |
Group Build Version |
|
SELPYE |
GRPLST |
Group List Name |
|
SELPYE |
STREAM |
Stream Number |
Use this parameter to tell the system whether it must use EMPL_FROM/TO. |
SELPYE |
EMPLID |
Employee ID |
The same parameter can be repeated. |
SELPYE |
EMPL_RCD |
Employee Record Number |
|
SELCAL |
CALGRPID |
Calendar Group ID |
|
SELCAL |
DATE |
1, 2, 3, or 4 |
Select one of the following:
If this parameter is not defined, the system uses the period end date. |
SELCAL |
FROMDT |
From Date |
|
SELCAL |
TODT |
To Date |
|
SELCAL |
CYCLE |
1, 2, or 3 |
Select one of the following:
|
SELSEG |
SQL |
This is the process filter. |
|
SELSEG |
BALANCES |
1 or 2 |
Select one of the following:
|
SELSEG |
REPLACE |
Y/N |
Use this parameter to determine whether replacements are considered original. |
SEGFILTER |
Any field name on the worktable and the segment writable array (if a segment writable array is defined) |
Any Value |
This is the segment filter. The process adds WRK.fieldname to the report. |
Global Payroll delivers two reports that use the Report Data feature to extract results data:
Payroll Results Register
Generic Reports