• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:March 5, 2021
  • Reading time:5 mins read
What is the alternative for DATA_CACHE which is being deprecated from 21A?
What is the alternative for DATA_CACHE which is being deprecated from 21A?
- What is the alternative for DATA_CACHE which is being deprecated from 21A?
Total Visits: 8

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.

image 37 - What is the alternative for DATA_CACHE which is being deprecated from 21A?

Sample Output:

image 38 1024x418 - What is the alternative for DATA_CACHE which is being deprecated from 21A?

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

image 39 - What is the alternative for DATA_CACHE which is being deprecated from 21A?

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.

image 40 - What is the alternative for DATA_CACHE which is being deprecated from 21A?

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:

image 41 - What is the alternative for DATA_CACHE which is being deprecated from 21A?
Old Parsing in SQL QueryNew Parsing in SQL Query
XMLTABLE(‘/TRANSACTION’
                      PASSING xmltype(txndata.data_cache)
                     COLUMNS
                      Business_unit_prior     VARCHAR2 (100)  PATH ‘COMPAREDATA/CompareVO/BaseWorkerWorkTermsVORow[@operationType =”compare” or @operationType =”default”]//Compare//Label[text()=”Department”]/../OldValue’)
PASSING per_bipntf_utility.extractXMLType(txndata.transaction_id,’TRANSACTION’)
and hth.transaction_id = xt.transactionid
TABLE(xmlsequence(extract(xmltype(‘<root>’||htd.data_cache||'</root>’), ‘//TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow’)))TABLE(per_bipntf_utility.extractXMLSequenceForEO(TO_CHAR(HTH.TRANSACTION_ID),’PeriodOfServiceEO’))
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()])[1]’), ‘<|>|OldValue|</|/>|NewValue’, ”)
extractvalue(xmltype(‘<root>’||htd.data_cache||'</root>’),’root/TRANSACTION/TransCtx/CmpTxnNewSalaryValuesMap/NewAdjustmentAmount’)extractvalue(xml_data_cache,’/TRANSACTION/TransCtx/CmpTxnNewSalaryValuesMap/NewAdjustmentPercentage’)

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.