PeopleSoft Enterprise Global Payroll for the United Kingdom 9.1 PeopleBook

Extracting P11D Information

This chapter provides overviews of P11D data extraction, P11D extraction setup, and modifying the P11D data extract tool, and discusses how to:

Set up P11D extraction.
Run the extract processes.
Load P11D extracts into third-party P11D solutions.

Understanding P11D Data Extraction

The P11D is a statutory report, created annually, to report employee expenses and benefits to HM Revenue and Customs (HMRC) for employees whose earnings exceed the limit set by the government. Employers are also required to provide a Statement of Benefits to their employees covered by the legislation. The information required for the P11D is diverse and includes benefits such as employee loans, company cars, and medical insurance. Typically this information is stored in different systems, including HR, payroll, and financial systems.

This diagram shows how the Global Payroll for the U.K. provides a P11D data extract tool that enables you to extract the P11D information that is stored in the standard PeopleSoft Enterprise HRMS tables and creates extract files in a CSV format that can be processed by a third-party P11D solution:

Overview of the P11D Production Process

The P11D data extract tool retrieves data from Global Payroll for the U.K. and PeopleSoft Enterprise Human Resources:

Employee details

Employee information such as name, employee ID, and employee record number are retrieved from Human Resources tables.
Company car data

This is extracted from the company car tables in Human Resources Manage Company Cars business process.
Loan data

Employee loans are administered and processed in payroll. The P11D data extract takes the loans information from the loans tables in Global Payroll for the U.K.
Employer information

Employer information is extracted from the pay entity tables.

Important! PeopleSoft Enterprise HRMS does not store all the information required to produce a P11D. Therefore, you cannot generate a complete P11D report using the P11D data extract tool as delivered. However, you can modify the delivered solution to include additional information in the extract files. This is discussed in more detail later in this chapter.

See Modifying the P11D Data Extract Tool.

This flowchart shows the steps in the P11D data extract process from calculating and finalizing payroll to loading the extract files into third-party P11D solutions:

Flowchart of the P11D Data Extract process

Before you can run the P11D processes, the payroll for the entire tax year must be calculated and finalized. You cannot run the P11D process if payees are open in any calendars. Therefore, you must plan carefully when you run the process between payroll runs.

The first step in the P11D data extract process is to run the P11D Global Payroll process. This process:

Is run in the same way as payroll.

Use the normal Calculate Absence and Payroll run control page to initiate P11D processing. However, the process requires a specific calendar and processing structure. This is explained in the setup section.

See Understanding P11D Extraction Setup.
Retrieves employee, company car, and loan data using arrays.

See Delivered Supporting Elements.
Populates a set of P11D results tables.

The process populates the result tables GPGB_P11DRSLT_L, GPGB_P11DRSLT_M, and GPGB_P11DRSLT_S. These generic result tables hold data from different source tables. The process assigns an extract type to each row of data that specifies the type of information. The Extract types page identifies the extract types that are delivered.

See Delivered Supporting Elements.
Retrieves data for all employees who have company car or loans data for the tax year.

Note. If some of your employees do not required P11Ds because their salaries are below the statutory minimum, then you can identify these employees using your P11D solution. This is done after the extraction is completed and the data has been loaded into the third-party P11D solution.

Use the message log to view the results of the process. You can also use the normal payroll results pages to view a list of employees who were processed.

When you have successfully run the payroll process, run the Extract P11D Data Application Engine process (GPGB_P11D). This process:

Extracts P11D information from various PeopleSoft tables.

The process extracts data from the P11D result tables, previously populated by the P11D global payroll process, and other HRMS tables.
Generates a series of CSV files using the extracted data.

The resulting CSV files are stored in the report repository.

The Extract P11D Data process uses the extract definition, which you define for your P11D solution, to determine the data extracted and the format of the CSV file.

Set up extract definitions using the Define P11D Solution GBR (GPGB_P11D_SOLUTION) component.

See Understanding P11D Extraction Setup.

This diagram summarizes the two processes that comprise the P11D data extract tool:

Summary of P11D Data Extract Processes

Note. The diagram shows an example of the extract files only. The CSV files produced differ according to the third-party P11D solution you are using.

Global Payroll for the U.K. delivers an extract definition that works with the KPMG P11D Solutions 2004 application, but this is provided as an example only. You can create extract definitions and modify the delivered elements for your own P11D solution.

See Also

Delivered Supporting Elements

Loading P11D Data Extracts into Third-Party P11D Solutions

Understanding P11D Extraction Setup

This section discusses:

Setting up extract types and extract definitions.
Setting up the P11D processing structure.
Delivered supporting elements.
Viewing delivered elements.

Setting Up Extract Types and Extraction Definitions

There are two setup components for the Extract P11D Data process:

Define P11D Extract Types GBR component (GPGB_P11D_E_TYP).

The extract type is a key field for the P11D result tables (GPGB_P11RSLT_L, GPGB_P11RSLT_M, and GPGB_P11RSLT_S). These tables store data retrieved from more than one source table. The extract type identifies the type of information stored in a row of data. For example, the GPGB_P11DRSLT_L table stores results relating to both Employee Details and Company Car allocations and the extract type is used to distinguish between these two sets of data. The extract type enables the Extract P11D Data process to select the relevant rows from the result tables. Do not change the delivered extract types unless you need to modify the global payroll process to retrieve additional data.

See Modifying the P11D Data Extract Tool.
Define P11D Solution GBR component (GPGB_P11D_SOLUTION).

The P11D Solution page is where you set up the extract definition for your P11D solution. The extract definition defines:
- The tables from which data is extracted.
- The extract type, if applicable.
  
  If data is extracted from the P11D results table, then an extract type is required to define the rows extracted.
- How to map the extracted data into the CSV file.
  
  Each third-party P11D solution has specific format requirements for loading data. Use the P11D Solution page to define the name of the CSV files and the format of data in the CSV files. Refer to the documentation for your third-party P11D solution for details of the formatting requirements.

See Also

Defining P11D Mapping

Setting Up the P11D Processing Structure

Before you run the P11D global payroll process, set up a processing structure to process results for the tax year for your pay groups. Here are the elements required and a description of the delivered elements:

Period for the tax year.

Global Payroll for the U.K. delivers a set of periods that correspond to tax years:
- GBRTAX0304 (U.K. tax year April 6, 2003 to April 5, 2004).
- GBRTAX0405 (U.K. tax year April 6, 2004 to April 5, 2005).
- GBRTAX0506 (U.K. tax year April 6, 2005 to April 5, 2006).
- GBRTAX0607 (U.K. tax year April 6, 2006 to April 5, 2007).
- GBRTAX0708 (U.K. tax year April 6, 2007 to April 5, 2008).
- GBRTAX0809 (U.K. tax year April 6, 2008 to April 5, 2009).
- GBRTAX0910 (U.K. tax year April 6, 2009 to April 5, 2010).
These periods are provided as sample data only. You can use the delivered periods or create your own. However, each period must be defined for the whole tax year.
A P11D Run Type

A P11D run type is delivered specifically for P11D data extract processing that is linked to the GBR PR P11D process list.
Calendars

Define a P11D calendar for your pay groups that is linked to a tax year period and the P11D run type.
Calendar groups

Note. The calendar group for P11D processing is defined when the Off Cycle check box is deselected. The P11D process is not an off-cycle process.
Sections

A P11D section is delivered for each type of extract:
- P11D SE EE DETS extracts employee details.
- P11D SE LOANS extracts loan information.
- P11D SE LOAN BAL extracts loan balance.
- P11D SE CAR ALLOC extracts company car data.
Process List

The delivered process list GBR PR P11D includes all the P11D sections listed above.

PeopleSoft delivers elements for P11D processing as sample data. You can use the delivered structure, or create your own.

See Also

Defining Processing Elements

Delivered Supporting Elements

This table lists the arrays that PeopleSoft delivers to retrieve information for the P11D:

Array	Description
GBR AR EMPLOYEE	Retrieves employee information from the table PERSONAL_DT_FST
GBR AR NAME	Retrieves employee first and last name from the view NAMES_VW.
GBR AR PAY ENTITY	Retrieves employer name and tax reference from the pay entity table GPGB_PYENT
P11D AR CAR ALLOC	Retrieves car allocations from the company car table CAR_ALLOC
P11D AR CAR DATA	Retrieves car data from the table CAR_DATA
P11D AR LOAN BAL	Retrieves loan balance from the writable array GPGB_LOAN_WA, which stores loan processing results.
P11D AR LOAN HD A	Retrieves loan data from the table GPGB_EE_LOAN_HD
P11D AR LOAN HD B	Retrieves loan data from the table GPGB_EE_LOAN_HD
P11D AR LOAN IN	Retrieves loan interest amount from table GPGB_EE_LOAN_IN.
P11D AR CAR PAY	Retrieves information about car payments.
P11D AR UNAV	Retrieves car information related to the periods when a car is not available.
P11D AR LOAN DT	Retrieves employee loan details.

This table lists the writable arrays that are delivered to populate the P11D results tables:

Writable Array	Description
P11D WR RESULT L	Populates the results table GPGB_P11DRSLT_L
P11D WR RESULT M	Populates the results table GPGB_P11DRSLT_M
P11D WR RESULT S	Populates the results table GPGB_P11DRSLT_S

Viewing Delivered Elements

The PeopleSoft system delivers a query that you can run to view the names of all delivered elements designed for the United Kingdom. Instructions for running the query are provided in the PeopleSoft Enterprise Global Payroll 9.1 PeopleBook.

Modifying the P11D Data Extract Tool

The P11D Data Extract tool is designed to be adapted to different third-party P11D solutions, and to enable organizations to modify the data extracted.

Each third-party P11D solution is different and therefore the steps for adapting the P11D data extract tool vary for each solution. Here are some guidelines describing the key tasks:

Review the documentation for your third-party P11D solution to determine data requirements and the file format required for importing data.
Map your PeopleSoft data to the third-party data structure.

Find out if the data items are in the same format in both systems or require manipulation. You need to determine which PeopleSoft tables store the data required by your third-party P11D solution.
Determine if any of the delivered sections can be reused.

For example the section P11D SE EE DETS may already select all the data elements you need relating to employee details.
If an existing section is close to your requirements, copy the section and supporting elements then modify the new section, rather than modifying the delivered section.
Create any new sections and supporting elements, if required.

Review the design of the delivered sections and the supporting elements. Use these as a basis for creating new sections and supporting elements.
Create a new process list and include the sections previously identified or created.
Determine whether you can use one of the existing result tables.

Review the definition of tables, GPGB_P11DRSLT_L, GPGB_P11DRSLT_M, and GPGB_P11DRSLT_S. Use the smallest table with enough fields to match your data requirements. For example, if the smallest table, GPGB_P11DRSLT_S, has enough fields then use that rather than GPGB_P11DRSLT_L or GPGB_P11DRSLT_M, which have more fields. If your data does not easily map to one of the existing result tables you may need to create a new table and a writable array to populate it.
Decide if new views are required.

For example, you need views if the data for an item in the third-party P11D solution exists across various tables in HRMS, or if only a subset of data is required based on some criteria.
Use the Define P11D Solution page to specify the mappings.
If complex data manipulation is required, then you may need to create some PeopleCode by adding methods to the Application Class GPGB_P11D:Extract.

During the development process, you can make use of a debug field. PeopleCode in the application engine sets the value of a debug field GPGB_P11D_AET.P11D_DEBUG. The field is set to "N" when delivered. If you modify the PeopleCode to set the debug field to "Y" then the log file generated by the Extract P11D Data process includes additional information which may help when developing modifications.

Typically the data required by your third-party P11D solution comes from a number of different sources. The data that resides in HRMS can be extracted using the P11D data extract tool. The data from other systems can be loaded into your third-party P11D solution directly. Alternatively you could make use of the various PeopleSoft integration tools to load the data into PeopleSoft Enterprise HRMS first and then channel it through the P11D data extract tool.

Setting Up P11D Extraction

This section discusses how to:

Set up extract types.
Define P11D mapping.
View mapping.

Pages Used to Set Up P11D Extraction

Page Name	Definition Name	Navigation	Usage
Define P11D Extract Types GBR	GPGB_P11D_E_TYP	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Framework, Processing, Define P11D Extract Types GBR, Define P11D Extract Types GBR	Define extract types.
P11D Solution	GPGB_P11D_SOLUTION	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Framework, Processing, Define P11D Solution GBR, P11D Solution	Define the extract definition for your third-party P11D solution.
View Mapping	GPGB_P11D_SOL_VIEW	Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Framework, Processing, Define P11D Solution GBR, View Mapping	View the mapping defined on the P11D Solution page.

Setting Up Extract Types

Access the Define P11D Extract Types GBR page (Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Framework, Processing, Define P11D Extract Types GBR, Define P11D Extract Types GBR).

Extract Type

Enter the extract type.

Defining P11D Mapping

Access the P11D Solution page (Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Framework, Processing, Define P11D Solution GBR, P11D Solution).

Solution ID

Enter the identifier for your P11D solutions software.

Extract ID

Enter a number for the extract.

File Name

Enter the name of the CSV file that is created by the Application Engine process.

Warning! The file name must not include spaces. If your third-party solution requires a file name that includes spaces, rename the file after running the Extract P11D Data process.

Source Record

Select the record from which data is extracted.

Extract Type

Select the extract type. This field is only available if the source record is GPGB_P11DRSLT_L, GPGB_P11DRSLT_M, or GPGB_P11DRSLT_S. It defines the information that is extracted from these generic results tables (the extract type is a key field for these tables).

Mapping

This group box defines how the information in the source record maps to the output file.

Sequence	Enter a sequence number that defines the ordering of the columns in the csv file.
Include in output file	Select this check box if the data is written directly to the output file. Deselect the check box if a value is selected but it is not included in the output file. The value can then be used by another mapping, which usually calls an App Class Method to manipulate the data in some way. For an example of how this works review the delivered KPMG solution, extract 11 (PAYE Reference Numbers). The mapping with sequence 10 selects the field GPGB_ER_TAXNUM and the mapping with sequence 11 selects the field GPGB_ER_PAYE_REF. Both of these are not included in the output file, but are used by the method defined on mapping 12 to create the tax office reference.
Exclude row if no value	Select this check box to prevent the whole row of data being written to the CSV file if the value of this field is null.
Column Heading	Enter the name of the column heading in the CSV file. The column heading is determined by the requirements of your third-party P11D solution.
Field Name	Select the field in the source record that stores the value you want to write to the CSV file. If you complete this field, you can't complete the Constant field or the App Class Method field.
Constant	Enter the value written to the CSV file in the column specified by Column Heading. If you complete this field, you can't complete the Field Name field or the App Class Method field.
App Class Method (application class method)	Enter the name of the application class method. Use this field if you want to call a PeopleCode program to return the value that is written to the CSV file. The method you enter here must be defined in the application class GPGB_P11D: Extract. This application class is in the application package GPGB_P11D. If you complete this field, you can't complete the Field Name field or the Constant field. Note. The system does not validate the name of the method you enter in this field. You must ensure that the method is entered correctly. Otherwise, the application engine process returns the status "No Success" if it is unable to find the method specified.

Viewing Mapping

Access the View Mapping page (Set Up HRMS, Product Related, Global Payroll & Absence Mgmt, Framework, Processing, Define P11D Solution GBR, P11D Solution, View Mapping tab).

This page summarizes the mapping that you defined on the P11D Solution page. The left hand column shows the heading that appears in the CSV file created. The right hand column shows where the data comes from (a record and field name, a constant, or an App Class Method).

Running the P11D Extract Processes

This section discusses how to run the P11D extract process.

Pages Used to Run P11D Extract

Page Name	Definition Name	Navigation	Usage
Calculate Absence and Payroll	GP_RUNCTL	Global Payroll & Absence Mgmt, Absence and Payroll Processing, Calculate Absence and Payroll, Calculate Absence and Payroll	Run the P11D global payroll process to populate the P11D results tables. Remember that you must run this process when the payroll for the tax year is finalized and the payees are not linked to any open calendars. Note. The P11D global payroll process must be finalized before normal payroll processing can resume. However, you should check that the Extract P11D Data process runs correctly before finalizing the payroll process. See Understanding P11D Data Extraction.
Extract P11D Data GBR	GPGB_P11D_RC	Global Payroll & Absence Mgmt, Year-End Processing, Extract P11D Data GBR, Extract P11D Data GBR	Run the Extract P11D Data process to create the CSV files.

Page Name

Definition Name

Navigation

Usage

Calculate Absence and Payroll

GP_RUNCTL

Global Payroll & Absence Mgmt, Absence and Payroll Processing, Calculate Absence and Payroll, Calculate Absence and Payroll

Run the P11D global payroll process to populate the P11D results tables. Remember that you must run this process when the payroll for the tax year is finalized and the payees are not linked to any open calendars.

Note. The P11D global payroll process must be finalized before normal payroll processing can resume. However, you should check that the Extract P11D Data process runs correctly before finalizing the payroll process.

See Understanding P11D Data Extraction.

Extract P11D Data GBR

GPGB_P11D_RC

Global Payroll & Absence Mgmt, Year-End Processing, Extract P11D Data GBR, Extract P11D Data GBR

Run the Extract P11D Data process to create the CSV files.

Running the P11D Extract Process

Access the Extract P11D Data GBR page (Global Payroll & Absence Mgmt, Year-End Processing, Extract P11D Data GBR, Extract P11D Data GBR).

Tax Year (End)

Enter the tax year for which to run the P11D data extract process. This is the year the tax years ends.

P11D Solution ID

Select the third-party P11D solution you want to use for the extraction. This defines the information that is extracted from HRMS tables and the output created. The Solution IDs are defined on the P11D Solution page.

Generate Log File

Select this check box to generate a log file.

Reviewing Log File

Here is an example of a log file generated by the P11D Data Extract process:

Parameters P11D Solution: KPMG UK Tax Year End: 2005 Debug: N Extract ID: 10 Record: GPGB_PYENT Extract Type: n/a File Name: Employers.csv Rows Selected: 5 Extract ID: 11 Record: GPGB_PYENT Extract Type: n/a File Name: PAYE_References.csv Rows Selected: 5 Extract ID: 12 Record: GPGB_PYENT Extract Type: n/a File Name: PAYE_Allocations.csv Rows Selected: 5 Extract ID: 20 Record: GPGB_P11DRSLT_L Extract Type: EE DETS File Name: Employees.csv Rows Selected: 3 Extract ID: 30 Record: GPGB_CAR_VW Extract Type: n/a File Name: Car_Details.csv Rows Selected: 5 Extract ID: 31 Record: GPGB_CAR_EXT_VW Extract Type: n/a File Name: Car_Extras.csv Rows Selected: 2 Extract ID: 32 Record: GPGB_P11DRSLT_L Extract Type: CAR ALLOC File Name: Car_Allocations.csv Rows Selected: 2 Extract ID: 40 Record: GPGB_P11DRSLT_M Extract Type: LOANS File Name: Loans.csv Rows Selected: 2 Extract ID: 41 Record: GPGB_P11DRSLT_S Extract Type: LOAN BAL File Name: Loan_Balances.csv Rows Selected: 3 End of processing

Loading P11D Data Extracts into Third-Party P11D Solutions

Loading the extract files into your third-party P11D solution varies according to the requirements of the solution. Refer to the documentation for your third-party P11D solution.

Here are some general guidelines:

Check the file formats accepted by the third-party P11D solution and convert the CSV files if necessary.

The Global Payroll for the U.K. processes generate CSV files. Some applications, such as KPMG P11D Solutions 2004, accept spreadsheets.
Check the file naming conventions used by the third-party software and adjust filenames if necessary.

The filenames of CSV files created by the Extract P11D Data process do not include spaces. This is a restriction of Application Engine processes. If your third-party P11D solution uses a filenaming convention that includes spaces, rename the CSV files.
Check if data must be loaded in a particular order.
Identify employees who do not require a P11D.

The P11D data extract tool extracts data for all employees with benefits data (company cars or loans). If there are employees who are not eligible for a P11D, identify them using your third-party P11D solution.

Loading P11D Data Extracts into KPMG P11D Solutions 2004

To load the P11D data into KPMG's P11D Solutions 2004:

Convert the CSV files into Excel spreadsheets.

Open the CSV file from within Excel and save the file as an Excel spreadsheet. You can load all the CSV files into one workbook (xls) file or create a separate file for each CSV file. If you create one workbook (xls file), each extract file goes on a separate sheet with the appropriate name in the tab.
Edit the worksheet names.

When you save the CSV files as Excel spreadsheets, the worksheet name defaults to the CSV file name. Replace the underscores (_) with spaces. For example, when you import the file PAYE_References.csv, edit the worksheet name to PAYE References.
Load the Excel spreadsheets in the correct order.

KPMG P11D Solutions 2004 requires data loading in a certain order. The order is defined by the Extract ID field on the P11D Solutions page.
Identify employees who do not require a P11D.

Refer to the KPMG documentation for instructions about selecting employees who don't require a P11D.

Solution ID	Enter the identifier for your P11D solutions software.
Extract ID	Enter a number for the extract.
File Name	Enter the name of the CSV file that is created by the Application Engine process. Warning! The file name must not include spaces. If your third-party solution requires a file name that includes spaces, rename the file after running the Extract P11D Data process.
Source Record	Select the record from which data is extracted.
Extract Type	Select the extract type. This field is only available if the source record is GPGB_P11DRSLT_L, GPGB_P11DRSLT_M, or GPGB_P11DRSLT_S. It defines the information that is extracted from these generic results tables (the extract type is a key field for these tables).

Tax Year (End)	Enter the tax year for which to run the P11D data extract process. This is the year the tax years ends.
P11D Solution ID	Select the third-party P11D solution you want to use for the extraction. This defines the information that is extracted from HRMS tables and the output created. The Solution IDs are defined on the P11D Solution page.
Generate Log File	Select this check box to generate a log file.