Oracle Transactional Business Intelligence

Products Banner

BI Publisher Data Model completes with errors

Received Response
142
Views
3
Comments

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

Answers

  • Dhanuka is the error you see on view data or parse sql or in report, as using same sql works for me though there is no data due to filters.

  • Hi Syed,

    As I mentioned above, the errors are occurred when I am trying view data in the data model. In other words when I am trying to run the report. It happens when following part of the query tries to read the data_cache column;

                 TABLE
                    (XMLSEQUENCE
                        (XMLTYPE ('<root>' || td.data_cache || '</root>').EXTRACT
                            ('/root//EO[@Name=&quot;oracle.apps.hcm.goals.core.publicModel.entity.GoalEO"]'
                            )
                        )
                    ) itemrow


    Regards,
    Dhanuka

  • Hi,
     
    I raised a SR regarding this issue and they have given me a solution. Here it is;
     
    --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    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 fusion.hrc_txn_header a
                     WHERE 1 = 1
                       AND a.module_group = 'HcmGoalTxn'
                       AND a.module_identifier = 'PERFORMANCE_GOALS'
                  GROUP BY a.object_id) th,
                 fusion.hrc_txn_data td,
                 TABLE
                    (XMLSEQUENCE
                        (XMLTYPE (td.data_cache).EXTRACT
                            ('//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
      
    --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     
    If you compare the both queries very carefully you could see the diference.
     
    --old query
                 TABLE
                    (XMLSEQUENCE
                        (XMLTYPE ('<root>' || td.data_cache || '</root>').EXTRACT
                            ('/root//EO[@Name=&quot;oracle.apps.hcm.goals.core.publicModel.entity.GoalEO"]'
                            )
                        )
                    ) itemrow
     
    --new query
                 TABLE
                    (XMLSEQUENCE
                        (XMLTYPE (td.data_cache).EXTRACT
                            ('//EO[@Name=&quot;oracle.apps.hcm.goals.core.publicModel.entity.GoalEO"]'
                            )
                        )
                    ) itemrow
     
    According to their analysis the issue was caused due to the following reason.
     
    1) When XML prolog (<?xml version="1.0" encoding="UTF-8"?>) tag is present and if we surround it with another tag like root in this case, the XMLType fails
    2) Removing the root tag from the query would resolve the issue as the XML prolog (<?xml version="1.0" encoding="UTF-8"?>) becomes the first tag.
     
     
    Regards,
    Dhanuka