How to Retrieve HCM Data Loader Error Details Using SQL?

Goal

How to get the error details or error records while automating the HCM Data Loader.

Fusion HCM Data Loader Limitations

The parent job completed as Normal (Successful), when any child jobs or any records error out during the Import and Load Process.

//
All the log file contains the error count only, but the job completed as Normal (Successful), there is no option to get the error record details while automating the HDL process.
//

Solution

For the HDL process limitations, we developed a custom report to display the error details. Which can be invoked using by soap webservice and its very helpful for data (integration) team or 3rd party system.

PHYSICAL LINES ERROR (GROUP G_PHY)

SELECT DATA_SET.UCM_CONTENT_ID,

       DATA_SET_BO.CREATED_BY,

       TO_CHAR(PHYSICAL_LINES.CREATION_DATE, 'MM/DD/YYYY') CREATION_DATE,

       MESSAGE_TYPE,

       MSG_TEXT,

       TEXT

  FROM HRC_DL_MESSAGE_LINES MESSAGE_LINES 

  JOIN HRC_DL_PHYSICAL_LINES PHYSICAL_LINES ON PHYSICAL_LINES.PHYSICAL_LINE_ID = MESSAGE_LINES.MESSAGE_SOURCE_LINE_ID 

  JOIN HRC_DL_FILE_ROWS FILE_ROWS ON FILE_ROWS.ROW_ID = PHYSICAL_LINES.ROW_ID 

  JOIN HRC_DL_FILE_LINES FILE_LINES ON FILE_LINES.LINE_ID = FILE_ROWS.LINE_ID 

  JOIN HRC_DL_DATA_SET_BUS_OBJS DATA_SET_BO ON DATA_SET_BO.DATA_SET_BUS_OBJ_ID = MESSAGE_LINES.DATA_SET_BUS_OBJ_ID 

  JOIN HRC_DL_DATA_SETS DATA_SET ON DATA_SET.DATA_SET_ID = DATA_SET_BO.DATA_SET_ID 

 WHERE 1=1

   AND DATA_SET.UCM_CONTENT_ID = NVL(:P_UCM_CONTENT_ID, DATA_SET.UCM_CONTENT_ID)

   AND TRUNC(PHYSICAL_LINES.CREATION_DATE) =  NVL (TO_DATE (TO_CHAR (:P_RUN_DATE, 'MM-DD-YYYY'), 'MM-DD-YYYY'), TRUNC(PHYSICAL_LINES.CREATION_DATE))

 ORDER BY 1

LOGICAL LINES ERROR (GROUP G_LOG)

SELECT DATA_SET.UCM_CONTENT_ID,

       DATA_SET_BO.CREATED_BY,

       TO_CHAR(LOGICAL_LINES.CREATION_DATE, 'MM/DD/YYYY') CREATION_DATE,

       MESSAGE_TYPE,

       MSG_TEXT,

       TEXT 

  FROM HRC_DL_MESSAGE_LINES MESSAGE_LINES 

  JOIN HRC_DL_LOGICAL_LINES LOGICAL_LINES ON LOGICAL_LINES.LOGICAL_LINE_ID = MESSAGE_LINES.MESSAGE_SOURCE_LINE_ID 

  JOIN HRC_DL_PHYSICAL_LINES PHYSICAL_LINES ON PHYSICAL_LINES.LOGICAL_LINE_ID = LOGICAL_LINES.LOGICAL_LINE_ID 

  JOIN HRC_DL_FILE_ROWS FILE_ROWS ON FILE_ROWS.ROW_ID = PHYSICAL_LINES.ROW_ID 

  JOIN HRC_DL_FILE_LINES FILE_LINES ON FILE_LINES.LINE_ID = FILE_ROWS.LINE_ID 

  JOIN HRC_DL_DATA_SET_BUS_OBJS DATA_SET_BO ON DATA_SET_BO.DATA_SET_BUS_OBJ_ID = MESSAGE_LINES.DATA_SET_BUS_OBJ_ID 

  JOIN HRC_DL_DATA_SETS DATA_SET ON DATA_SET.DATA_SET_ID = DATA_SET_BO.DATA_SET_ID 

 WHERE 1=1

   AND DATA_SET.UCM_CONTENT_ID = NVL(:P_UCM_CONTENT_ID, DATA_SET.UCM_CONTENT_ID)

   AND TRUNC(LOGICAL_LINES.CREATION_DATE) =  NVL (TO_DATE (TO_CHAR (:P_RUN_DATE, 'MM-DD-YYYY'), 'MM-DD-YYYY'), TRUNC(LOGICAL_LINES.CREATION_DATE))

 ORDER BY 1

PARAMETER (GROUP G_PARAM)

SELECT TO_CHAR(:P_RUN_DATE, 'MM/DD/YYYY') RUN_DATE,

       NVL(:P_UCM_CONTENT_ID, 'ALL') UCM_CONTENT_ID

  FROM DUAL

Input Parameters

1. UCM Content ID (Optional)
2. HDL Run Date (Optional)

Build a custom report using the queries mentioned above.