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.
The picture shows the 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 is as shown below
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++++++++++++++
Set as favorite
Bookmark
Email This
Hits: 2894
Comments
(2)
Subscribe to this comment's feed
...
written by donaldjeo , March 08, 2010
written by donaldjeo , March 08, 2010
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 l**e 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 l**e to visit you again with hosting reseller. keep working like this.... Have a wonderful time.
Votes: +1
report abuse
vote down
vote up
