BI Publisher Data Model completes with errors

Summary

BI Publisher Data Model completes with errors

Content

Hi Everyone,

I'm trying to create a BI Publisher report using HRC_TXN_HEADER and HRC_TXN_DATA tables. Here I am trying to extract pending performance goal details from a XML code which is in data_cache column of HRC_TXN_DATA table.

I am using below code;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
WITH pending_goals AS
     (SELECT EXTRACTVALUE (VALUE (itemrow),
                           '*/GoalEORow/PersonId/DATA'
                          ) person_id,
             EXTRACTVALUE (VALUE (itemrow),
                           '*/GoalEORow/AssignmentId/DATA'
                          ) assignment_id,
             th.object_id goal_plan_assignment_id,
             th.transaction_id transaction_id,
             EXTRACTVALUE (VALUE (itemrow),
                           '*/GoalEORow/GoalId/DATA') goal_id,