In the previous article linked here, we saw how we can parse the Self Service HRMS XML data in HR_API_TRANSACTIONS's CLOB column. That approach will work fine in most of the cases. I am saying in "most" of the cases because it will not work perfectly fine in all the cases. This is due to the inherent limitation of parsing large XML Clob object size in SQL layer. Before we get into the resolution to that limitation, let us first see the exact nature of this limitation. This limitation is about the size of the XML column. Due to the design methodology implemented by Self Service HRMS product development team, if you upload document against performance objectives in Self Service HRMS, then even the uploaded binary document will get encoded within the XML data itself in HR_API_TRANSACTIONS. Logically this is fine because in Self Service HR, when the entered data goes for approval, and if the data gets rejected, then the entire contents within the HR_API_TRANSACTIONS gets rejected. If the document was uploaded against such SSHR transaction, then the attached document along with the underlying transaction data goes for approval[via XML column] and either gets applied to base tables[if approved] or gets deleted [if rejected].
Unfortunately lot of customers do not like this design feature from SSHR. For example, almost every customer faces an issue in Appraisal Management which is when they capture an appraisal which is a very lengthy process and it sometime takes the end users hours to enter their Appraisal details and if this goes for Appraisal Approval process via AME, if Manager rejects the Appraisal then the whole appraisal data gets deleted from the system. Ideally if the Approver rejects the data, then initiating user should be able to make further modifications to their appraisal. Given that SSHR product maps the "appraisal rejection" to "dropping the contents within XML", then entire Appraisal data entered by the user is lost during Appraisal rejection [Note Issue fixed in R12.1.1 or R12.2. For now you can use Return for Correction mode to avoid losing data]. Now coming back to attachments, one can argue that if Appraisal's XML data gets rejected, it seems logical that even the Attached documents against that transaction should get rejected. Therefore to keep their design consistent the SSHR team designed for the attachments to be encoded and captured within the XML itself. I do not think such design is ideal because there is already an attachment framework in Oracle that should be leveraged out of the box, and provision should be made to remove the attachments when the transaction referenced by primary key get deleted.
Anyway, given that attachments get loaded within the XML, we can hit 64K size limitation for XML parsing within SQL Layer. Its only after you have upgraded to Database 11g, you will be able to parse XML data containing attachments in SQL Layer. Therefore the only solution left with you to parse the XML that contains large attachments is to write a piece of code in Java to parse such XML. In this article you will see a sample code from a live project that I did in past, for parsing the XML that contains encoded binary objects making the XML size greater than 64K.
If you wish to use this code, simply copy paste and amend as needed. The key method in this code is processClobData() that takes TransactionId as parameter, and executes query on View Object named xxHRApiClobVO.
The image below shows an example of large document uploaded from the Performance Management screen against the objective.
It will show an error that you will receive if you parse such XML via SQL Layer, as per the SQL linked here
The next image shows how the attached document appears inside the XML of HR_API_TRANSACTIONS.TRANSACTION_DOCUMENT
The View Object that retrieves the XML data to be parsed
For parsing such XML, this sample code contains the following
1. View Object xxHRApiClobVO
2. Controller class xxHRApiTrxCO which calls method processClobData in AM xxHrApiTrxAM
3. Implementation of xxHrApiTrxAMImpl which performs the actual parsing
+++++++BEGIN CONTROLLER CLASS+++++++++++++++
package xxft.oracle.apps.per.wpm.transaction.webui;
import java.io.Serializable;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.per.wpm.objectives.webui.ObjectivesPageCO;
public class xxHRApiTrxCO extends ObjectivesPageCO {
public void processRequest(OAPageContext pageContext, OAWebBean webBean, String pScoreCardId) {
writeLog(pageContext,"before ObjectivesPageCO super call");
// super.processRequest(pageContext, webBean);
writeLog(pageContext,"After ObjectivesPageCO super call");
OAApplicationModule roam =
(OAApplicationModule)pageContext.getRootApplicationModule();
OAApplicationModule trxAM =
(OAApplicationModule)roam.findApplicationModule("xxHrApiTrxAM");
if (trxAM == null) {
trxAM =
(OAApplicationModule)roam.createApplicationModule("xxHrApiTrxAM",
"xxft.oracle.apps.per.wpm.transaction.server.xxHrApiTrxAM");
}
Serializable params[] = { pScoreCardId };
trxAM.invokeMethod("processClobData", params);
String s3 = getTransactionId(pageContext);
}
/**
*
* Method to set the requisition Number in the transaction.
*/
public void writeLog(OAPageContext pageContext,String messageText)
{
if(pageContext.isLoggingEnabled(OAWebBeanConstants.STATEMENT))
{
System.out.println(this+" : "+messageText);
pageContext.writeDiagnostics(this,"xxftMgrReqInputPageCO : "+messageText,1);
}
}
}
+++++++END CONTROLLER CLASS CODE++++++++++++++
+++++++BEGIN APPLICATION MODULE CODE++++++++++++++
package xxft.oracle.apps.per.wpm.transaction.server;
import java.io.Reader;
import java.sql.SQLException;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.jbo.JboSerializationException;
import oracle.jbo.common.JboXMLUtil;
import oracle.jbo.domain.ClobDomain;
import oracle.jbo.server.DBTransaction;
import oracle.jdbc.OracleCallableStatement;
import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
// ---------------------------------------------------------------------
// --- File generated by Oracle ADF Business Components Design Time.
// --- Custom code may be added to this class.
// --- Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class xxHrApiTrxAMImpl extends OAApplicationModuleImpl {
/**This is the default constructor (do not remove)
*/
public xxHrApiTrxAMImpl() {
}
/**Sample main for debugging Business Components code using the tester.
*/
public static void main(String[] args) { /* package name */
/* Configuration Name */launchTester("xxft.oracle.apps.per.wpm.transaction.server",
"xxHrApiTrxAMLocal");
}
public void captureDetailTransaction(String p_scorecard_id,
String p_weightingpercent,
String p_groupcode,
String p_objectiveid,
int p_rec_counter) {
DBTransaction dbtransaction = getDBTransaction();
String sExecString =
"begin xxft_hr_trx_clob_from_java.capture_details(:1,:2,:3,:4,:5); end; ";
OracleCallableStatement oraclecallablestatement =
(OracleCallableStatement)dbtransaction.createCallableStatement(sExecString,
1);
try {
oraclecallablestatement.setString(1, p_scorecard_id);
oraclecallablestatement.setString(2, p_weightingpercent);
oraclecallablestatement.setString(3, p_groupcode);
oraclecallablestatement.setString(4, p_objectiveid);
oraclecallablestatement.setInt(5, p_rec_counter);
oraclecallablestatement.execute();
oraclecallablestatement.close();
System.out.println("Pushed data xxWeightingPercent=>" +
p_weightingpercent + " xxGroupCode=>" +
p_groupcode + " xxObjectiveId=>" +
p_objectiveid + " xxScorecardId=>" +
p_scorecard_id);
} catch (SQLException sqlexception) {
throw OAException.wrapperException(sqlexception);
} catch (Exception exception) {
throw OAException.wrapperException(exception);
}
}
/**Container's getter for xxHRApiClobVO
*/
public xxHRApiClobVOImpl getxxHRApiClobVO() {
return (xxHRApiClobVOImpl)findViewObject("xxHRApiClobVO");
}
XMLDocument processByteStream(Reader reader) {
try {
DOMParser domparser = JboXMLUtil.createDOMParser(false);
domparser.parse(reader);
return domparser.getDocument();
} catch (Exception exception) {
throw new JboSerializationException(false, -1, "NoCache",
exception);
}
}
public XMLDocument convertClobToXMLDocument(ClobDomain clobdomain) {
return processByteStream(clobdomain.getCharacterStream());
}
public void processClobData(String xxTrId) {
int detCounter = 0;
xxHRApiClobVOImpl oav = getxxHRApiClobVO();
oav.setWhereClause(null);
oav.setWhereClauseParams(null);
oav.setWhereClause("transaction_ref_id = :1 and transaction_ref_table = :2");
oav.setWhereClauseParam(0, xxTrId);
oav.setWhereClauseParam(1, "PER_PERSONAL_SCORECARDS");
oav.executeQuery();
oav.first();
xxHRApiClobVORowImpl oarow = (xxHRApiClobVORowImpl)oav.getCurrentRow();
if (oarow != null) {
//System.out.println("Get the Trx Table =>" + oarow.getTransactionRefTable());
ClobDomain clob = oarow.getTransactionDocument();
if (clob == null) {
return;
}
XMLDocument xd = convertClobToXMLDocument(clob);
NodeList l_objective_rows =
xd.getElementsByTagName("ObjectiveEORow");
for (int j = 0; j <= l_objective_rows.getLength() - 1; j++) {
boolean processThisObjective = false;
Element objrow = (Element)l_objective_rows.item(j);
String psValue = objrow.getAttribute("PS");
System.out.println("PS Value =>" + psValue);
if (psValue != null && !(psValue.equals("")) &&
psValue.equals("3")) {
processThisObjective = true;
}
// System.out.println("psValue=>" + psValue);
NodeList childnodes = objrow.getChildNodes();
String xxScorecardId = "";
String xxWeightingPercent = "";
String xxGroupCode = "";
String xxObjectiveId = "";
String xxCurrentNodeName = "";
for (int k = 0; k <= childnodes.getLength() - 1; k++) {
xxCurrentNodeName = childnodes.item(k).getNodeName();
//System.out.println(childnodes.item(k).getNodeName() +"=>" +childnodes.item(k).getFirstChild().getNodeValue());
if ((childnodes.item(k).getNodeName().equals("WeightingPercent")) &&
(childnodes.item(k).getFirstChild().getNodeValue() !=
null) &&
!(childnodes.item(k).getFirstChild().getNodeValue().equals(""))) {
processThisObjective = true;
}
// childnodes.item(k).getNodeName();
if (xxCurrentNodeName.equals("ScorecardId"))
xxScorecardId =
childnodes.item(k).getFirstChild().getNodeValue();
if (xxCurrentNodeName.equals("WeightingPercent"))
xxWeightingPercent =
childnodes.item(k).getFirstChild().getNodeValue();
if (xxCurrentNodeName.equals("GroupCode"))
xxGroupCode =
childnodes.item(k).getFirstChild().getNodeValue();
if (xxCurrentNodeName.equals("ObjectiveId"))
xxObjectiveId =
childnodes.item(k).getFirstChild().getNodeValue();
}
if (processThisObjective) {
detCounter++;
captureDetailTransaction(xxTrId, xxWeightingPercent,
xxGroupCode, xxObjectiveId,
detCounter);
}
}
}
}
}
+++++++END APPLICATION MODULE CODE++++++++++++++
Apps2Fusion Articles 
Comments
RSS feed for comments to this post