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="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
