You are currently viewing How to customize seeded payroll extracts to add additional fields?
How to customize seeded payroll extracts to add additional fields?

In this article, we will try to analyze how to add additional fields to the seeded payroll extracts.

Usually, the seeded payroll extracts cannot be modified as most of them are built based on the procedure based attributes and it is not possible to add other data groups and link them to the existing ones. And for the ones where it is possible to clone the extract, I have seen cases where the database items and fast formulas did not work.

When one of the customer had a tough ask on adding location to the payroll register report, we did a lot of brain storming on this and came up with a solution where it worked.

Instead of concentrating on modifying the seeded extract, we moved the focus to the global reports data model which the BI Report uses and came up with a way to add an additional data set which will pull the person number, assignment number and the location and then modify the template to pull this additional field by matching the person number from the extract output.

Let’s take the example of the Payroll Register Report and try to add a location field to it as it is not one of the seeded fields on the report. for which the underlying extract name is “Global Payroll Register“.

We need to copy the seeded global reports data model to the custom folder and add additional data set to pull the location and then group it with the main data set.

SQL Query for the additional dataset

select distinct paaf.person_id, papf.person_number, paaf.assignment_number, hla.location_name from
HR_LOCATIONS_ALL HLA, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F PAPF
where
1=1
--AND PAAF.PRIMARY_FLAG = 'Y'
and paaf.assignment_type = 'E'
and papf.person_id = paaf.person_id
and TRUNC(SYSDATE) between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) between PAAF.EFFECTIVE_START_DATE and PAAF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) between HLA.effective_start_date and HLA.effective_end_date
AND PAAF.EFFECTIVE_LATEST_CHANGE='Y'
AND HLA.LOCATION_ID = PAAF.LOCATION_ID
image 12 1024x561 - How to customize seeded payroll extracts to add additional fields?

We need to copy the seeded payroll register report to custom folder and map this custom data model to it.

Now, we need to add a new delivery option to the seeded payroll register report, so that the custom report generates the xml with the additional data set that we added.

image 13 1024x348 - How to customize seeded payroll extracts to add additional fields?

Once added, we need to validate the extract and run it to get the sample xml file. Once we get the xml file, we need to import the xml to the seeded template and modify the template to add the location field.

In the template, we need to first set the variable for PERSON_ID

<?xdoxslt:set_variable($_XDOCTX, 'person', ./PERSON_ID)?>

Next add a column and modify the attribute to below

<?/DATA_DS/G_1/G_3[PERSON_ID = xdoxslt:get_variable($_XDOCTX, 'person')]/LOCATION_NAME?>

Once these changes are done, we can upload the template to the report in custom folder and rerun the payroll register report. Now the payroll register report will generate the output with the location on that file as per the updated template.