In this article we will look into deprecation of DATA_CACHE column from 21A and the impact it’s going to create for the clients/users to modify existing reports using this field.
If you haven’t heard of this DATA_CACHE column, this field is used to read the XML data from the approval transaction related tables. DATA_CACHE column is present in HRX_TXN_DATA table which is child to the HRC_TXN_HEADER where the new replacement XML_DATA_CACHE resides.
Oracle has made this decision to take advantage of the performance gains by using the new column in BIP notifications and customer reports. Oracle urges customers using this DATA_CACHE to replace their existing reports with the replaced field (though not directly). DATA_CACHE was of CLOB type and the new field XML_DATA_CACHE field is of XML Type and that could be the reason to get better performance.
Oracle Document – Fusion HCM BIP Notifications: How to Identify BIP Reports Accessing DATA_CACHE Column and Replace References with XMLType (Doc ID 2692905.1)
First we need to find out all custom reports which use the DATA_CACHE column. As you all know we do not have access to the backend tables which stores the Data Model SQL’s. So Oracle has created a Diagnostic Report by name “BIP DataModel with DataCache References” (DATA_MODEL_DATA_CACHE_REF_CHECK) which lists all custom reports which reference this column.
The next part would be to replace the references of DATA_CACHE with the new reference for XML_DATA_CACHE. However as this field is an XML Type field we cannot reference it directly and we will have to use per_bipntf_utility.extractXMLSequenceForEO function by passing the transaction id.
We cannot view the XML data in the field XML_DATA_CACHE if we do a select * from HRC_TXN_HEADER table as XML Type fields won’t be visible directly. Even Oracle Documentation isn’t there on how to view this data. One of our Telegram Group Member and blog follower by name Vivek Omar raised an SR to get the details on how to view data in the XML field and here is what he got..
select xml_data_cache from hrc_txn_header e where e.transaction_id =300000330910999
The output doesn’t show anything
Now, let’s use the function getclobval() for this column. Make sure you use alias for the table and column else this query will not work.
Select e.xml_data_cache.getClobVal() as XML from hrc_txn_header e where e.transaction_id =30000033091999
Now, we get the output in XML format.
This query produces the output in XML format. Now we need to check the XML output and see what node is required on the output report and write parsing query accordingly.
Here are some samples from Vivek Omar which can be used as reference. Oracle has also provided 45 sample queries using DATA_CACHE and the new XML_DATA_CACHE field on at Fusion HCM BIP Notifications: How to Identify BIP Reports Accessing DATA_CACHE Column and Replace References with XMLType (Doc ID 2692905.1)
Sample from Oracle Document on the above Doc ID:
|Old Parsing in SQL Query||New Parsing in SQL Query|
Business_unit_prior VARCHAR2 (100) PATH ‘COMPAREDATA/CompareVO/BaseWorkerWorkTermsVORow[@operationType =”compare” or @operationType =”default”]//Compare//Label[text()=”Department”]/../OldValue’)
and hth.transaction_id = xt.transactionid
|regexp_replace(substr(xmltype.createxml(txndata.DATA_CACHE).extract(‘.//Compare//AttributeName[text()=”AssignmentStatusTypeId”]/../OldValue’).getStringVal(),instr(xmltype.createxml(txndata.DATA_CACHE).extract(‘.//Compare//AttributeName[text()=”AssignmentStatusTypeId”]/../OldValue’).getStringVal(),'<‘,1) +10,instr(xmltype.createxml(txndata.DATA_CACHE).extract(‘.//Compare//AttributeName[text()=”AssignmentStatusTypeId”]/../OldValue’).getStringVal(),'</’)-11),'<|>|OldValue|</|/>|NewValue’,”)||Regexp_replace(per_bipntf_utility.extractValueByXPath(Hth.transaction_id,'(//Compare//Label[text()=”AssignmentStatusTypeId”]/../OldValue[text()])’), ‘<|>|OldValue|</|/>|NewValue’, ”)|
You can apply the above parsing techniques and modify the existing report to make it work post 21A as well without any impact to the output of the report.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM