In this article, we will look into how to query the tables to get the HCM Extract Errors.
When we run the extract, sometimes we get errors and the flow will run to “Completed with Errors”. We have to navigate to Payroll > View Flows section and click on the flow and extract name and then the failed records to view the error messages.
It would be easy to find out the errors if we have the option to query the tables to get this information. We can avoid all the above manual steps and directly input the flow instance name to get the extract errors.
SQL Query to get the Extract Errors:
Query to find the Error count related to Extract:
select pprv.FLOW_NAME
,pprv.FLOW_INSTANCE_ID
,pprv.flow_instance_name
,pprv.LEGISLATIVE_DATA_GROUP_NAME
,pprv.TASK_STATUS_MEANING
,pprv.TASK_SUB_STATUS_MEANING
,pprv.submitted_by
,to_char(pprv.SUBMISSION_DATE,'MM/DD/YYYY') SUBMISSION_DATE
,pprv.RECORD PROCESSED_RECORDS
,pprv.STAT_ERROR_TOTAL ERROR_RECORDS
,pprv.PAYROLL_ACTION_ID
,pprv.MESSAGES TOTAL_ERROR_MSGS
from PAY_PROC_RESULTS_VL pprv
where pprv.flow_instance_name = <Flow Instance Name>
--and pprv.FLOW_NAME = <Extract Name>
Query to find the Error Messages related to Extract:
select distinct ERR_MSG.MESSAGE_TEXT
,ERR_MSG.TOTAL TOTAL_ERR_REC_WITH_ERR_MSG
from PAY_MESSAGES_VL ERR_MSG
WHERE ERR_MSG.FLOW_INSTANCE_ID = <Flow Instance ID from above query>We have to input the flow instance name to the first query and get the flow instance id and pass it to the second query. Actually, we can join the PAY_MESSAGES_VL table to the first query itself, however as it contains a lot of data related to extract, payroll processes, etc, we will land up in performance issues and hence two parts of running the query.
Output of the query for the above flow instance:
