Generating Report Data

This chapter provides an overview of report data generation and discusses how to:

• Define report data

• Validate report data

• Using report data

Understanding Report Data Generation

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.

The Data Reporting Process

Using the report data generation process, you can easily read absence results tables and use this data to populate your own reports.

The report data generation process enables you to create reports that contain:

• Retroactivity.

• Segmentation, retro-segmentation, and segmentation cancelled by retroactivity.

• Reverse calculations.

• Absence Management 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, 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 HCM security.

• Appropriate absence 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:

1. Assign output and input tables for the report on the Define Report Data page.

2. Define the rules used to create the output table on the Define Report Data page.

3. Map fields and create filters used to generate the output table on the Report Data Process Details page.

Selecting Absence 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 absence results and populate the output tables:

1. Select calendar groups.

2. Select eligible payees using payee lists, group builds, security, and payee filter. If a payee filter was created, it is applied here.

3. Join the payee process status table (GP_PYE_PRC_STAT) and the payee segment status table (GP_PYE_SEG_STAT).

4. Select the original segments. If a process filter was created it is applied here.

5. 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.

6. 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.

7. Update the output table with data from the additional input tables.

Overview of Generating Report Data 1

Overview of Generating Report Data 2

Filters

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. absence 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.

Report Data and Segmentation

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 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 Absence Management creates only one segment for the current period.

See Also

Defining Retroactive Processing

Output Table Definitions

Output tables must be defined to store your selected results. These tables must include the following key structure:

User-Defined Parameter Definitions

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:

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");
&amp_reportBatch.AddParamChar("REPORT", "STATE", "OR");

Batch Processing Setup

The batch process provides a public section called "EXTRACT" that can be called by an application engine process that prepares the absence 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;

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.

Real-Time Processing Setup

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 */

Building a Transaction That Uses Report Data

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.

Common Elements Used in This Chapter

Defining Report Data

This section discusses how to:

• Select output tables and primary input tables.

• Map fields.

• Create input filters.

Pages Used to Define Report Data

Selecting Output Tables and Primary Input Tables

Access the Define Report Data page (Setup HRMS, Product Related, Global Payroll & Absence Mgmt, Reports, Define Report Data, Define Report Data).

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

Mapping Fields

Access the Report Data Process Details page (click the Details link on the Define Report Data 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.

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 Map Element Tab

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

Field Map Element Options Tab

Creating Input Filters

Access the Report Data Process Details page.

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.

Validating Report Data

This section discusses how to validate report data.

Report Validation

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 Used to Validate Report Data

Entering Report Validation Parameters

Access the Validate Report Data page (Setup HRMS, Product Related, Global Payroll & Absence Mgmt, Reports, Validate Report Data, Validate Report Data).

Run Mode	Select one of three options: Batch (Application Engine) Real Time (PeopleCode) Script (Data Mover) 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: Batch (Application Engine): The system launches an Application Engine that runs the process in batch mode via process scheduler. Real Time (PeopleCode): The system launches a PeopleCode function that runs the process in real-time. Script (Data Mover): The system launches a Data Mover process that runs the process in batch mode via process scheduler.

Structuring Parameters

Below is a list of predefined function and parameter values.

Using Report Data

Absence Management delivers two reports that use the Report Data feature to extract results data:

• Absence Results Register

  See Reporting Payroll Data.

• Generic Reports

  See Running Generic Reports.