Fusion Blog

EBS Blog


Contact Us

Functional Documents
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active

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 trxAM = 
        if (trxAM == null) {
            trxAM = 
        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)


          System.out.println(this+" : "+messageText);
          pageContext.writeDiagnostics(this,"xxftMgrReqInputPageCO : "+messageText,1);

+++++++END CONTROLLER CLASS 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", 


    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 = 
        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);
            System.out.println("Pushed data xxWeightingPercent=>" + 
                               p_weightingpercent + " xxGroupCode=>" + 
                               p_groupcode + " xxObjectiveId=>" + 
                               p_objectiveid + " xxScorecardId=>" + 


        } 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);
            return domparser.getDocument();
        } catch (Exception exception) {
            throw new JboSerializationException(false, -1, "NoCache", 


    public XMLDocument convertClobToXMLDocument(ClobDomain clobdomain) {
        return processByteStream(clobdomain.getCharacterStream());


    public void processClobData(String xxTrId) {
        int detCounter = 0;
        xxHRApiClobVOImpl oav = getxxHRApiClobVO();
        oav.setWhereClause("transaction_ref_id = :1 and transaction_ref_table = :2");
        oav.setWhereClauseParam(0, xxTrId);
        oav.setWhereClauseParam(1, "PER_PERSONAL_SCORECARDS");
        xxHRApiClobVORowImpl oarow = (xxHRApiClobVORowImpl)oav.getCurrentRow();
        if (oarow != null) {
            //System.out.println("Get the Trx Table =>" + oarow.getTransactionRefTable());
            ClobDomain clob = oarow.getTransactionDocument();
            if (clob == null) {


            XMLDocument xd = convertClobToXMLDocument(clob);
            NodeList l_objective_rows = 


            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 = 


                    if (xxCurrentNodeName.equals("WeightingPercent"))
                        xxWeightingPercent = 
                    if (xxCurrentNodeName.equals("GroupCode"))
                        xxGroupCode = 
                    if (xxCurrentNodeName.equals("ObjectiveId"))
                        xxObjectiveId = 


                if (processThisObjective) {
                    captureDetailTransaction(xxTrId, xxWeightingPercent, 
                                             xxGroupCode, xxObjectiveId, 







+++++++END APPLICATION MODULE CODE++++++++++++++

Anil Passi


0 #1 donaldjeo 2010-03-08 06:47
Thanks for sharing nice information with us. i like your post and all you share with us is uptodate and quite informative, i would like to bookmark the page so i can come here again to read you, as you have done a wonderful job. I like your work and would love to send this page to my friends so they can read more about mcse with my friend. i like to see more post form you on the same way as you have done in past and love to visit you again with hosting reseller. keep working like this.... Have a wonderful time.
0 #2 lace wigs 2011-09-06 12:01
lace wigs part the hair in any direction or wear it in a high ponytail withokut the worry of exposed tracks.

Add comment

Security code

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Jun 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
   1  2  3  4  5  6
  7  8  910111213

Enquire For Training

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner