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,
             EXTRACTVALUE (VALUE (itemrow),
                           '*/GoalEORow/GoalName/DATA'
                          ) goal_name,
             DECODE
                (EXTRACTVALUE (VALUE (itemrow),
                               '*/GoalEORow/GoalVersionTypeCode/DATA'
                              ),
                 'PENDING_APPROVAL', 'Pending Approval',
                 'ORA_REQUIRES_APPROVAL', 'Requires Approval',
                 EXTRACTVALUE (VALUE (itemrow),
                               '*/GoalEORow/GoalVersionTypeCode/DATA'
                              )
                ) goal_status,
             td.last_update_date
        FROM (SELECT   a.object_id, MAX (a.transaction_id) transaction_id
                  FROM hrc_txn_header a
                 WHERE 1 = 1
                   AND a.module_group = 'HcmGoalTxn'
                   AND a.module_identifier = 'PERFORMANCE_GOALS'
              GROUP BY a.object_id) th,
             hrc_txn_data td,
             TABLE
                (XMLSEQUENCE
                    (XMLTYPE ('<root>' || td.data_cache || '</root>').EXTRACT
                        ('/root//EO[@Name=&quot;oracle.apps.hcm.goals.core.publicModel.entity.GoalEO"]'
                        )
                    )
                ) itemrow
       WHERE 1 = 1
         AND th.transaction_id = td.transaction_id
         AND td.status != 'APPROVED'
         AND td.state != 'COMPLETE')
SELECT pg.*
  FROM pending_goals pg
  
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I am getting below errors;

--++++++++++++++++++++++++++++++++++++++++++
oracle.xdo.servlet.data.DataException: 
oracle.xdo.servlet.data.DataException: 
oracle.xdo.servlet.data.DataException: 
oracle.xdo.XDOException: java.sql.SQLException: 
ORA-31011: XML parsing failed 
ORA-19213: error occurred in XML processing at lines 1 
LPX-00209: PI names starting with XML are reserved 
ORA-06512: at "SYS.XMLTYPE", line 272 
ORA-06512: at line 1 
--++++++++++++++++++++++++++++++++++++++++++

Any help/ pointer in this regard is highly appreciated.

Thanks,
Dhanuka

Version

20C