Extending CO to execute custom SQL queries for validation purpose

It is not recommended to use JDBC Connections from within Controllers.
However, sometimes from within our extended controllers, we have a need to fire some SQL Statements.
You have various options to achieve this, and these are discussed with code samples in this article below.


Option 1
Create a new view object and attach that view object to the Application Module of that page.
Then call the SQL by executing query on "custom view object" from "extended application module"
Cons for Option 1
In this case you will have to extend the Application Module, so as to attach it the Custom View Object.
If the Application Module is Root AM, then such AM may not be extensible.

Option 2
Get the database connection within controller and call SQL Statements or PL/SQL APIs by doing one of the two below
OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)oapagecontext.getRootApplicationModule().getOADBTransaction();
CallableStatement callablestatement = oadbtransactionimpl.createCallableStatement("begin :1 := xx_get_Fnd_user_desc(:2); end; ", 1);
OR
OADBTransaction oadbtransaction = (OADBTransaction)oapagecontext.getApplicationModule(oawebbean).getTransaction();
java.sql.Connection connection = oadbtransaction.getJdbcConnection();
Cons for Option 2
Getting JDBC connection inside the controller is not recommended.


Option 3 - [ Recommened ]
Create the view object on the fly from SQL Statement. This view object will be automatically attached to the Application Module of the page.
In this article, we will see how you can do this.

Lets say our requirement is that, in the extended controller, for a given FND_USER.USER_NAME - we need to find FND_USER.DESCRIPTION



Overview Our steps will be [For Option 3]
1. Extend the controller
2. In the extended controller import these two classes as shown below
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.jbo.ViewObject;
3. Build the SQL Statement that you wish to execute in a String variable.
IMPORTANT- Ensure that you use Bind Variables as shown in this example below
4. Execute the Query on the View Object
5. Get the desired results from the rows of this view object


package oracle.apps.fnd.framework.toolbox.tutorial.webui;

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
//-----------------------------------------------------
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.jbo.ViewObject;

/**
* Controller for oracle.apps.fnd.framework.toolbox.tutorial.webui.HelloWorldPG
* page.
*/
public class HelloWorldMainCO extends OAControllerImpl
{
// Required for Applications source control
public static final String RCS_ID="$Header: HelloWorldMainCO.java 115.6 2004/01/19 10:14:57 atgopxxOnTheFlyVOQuery noship $";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "oracle.apps.fnd.framework.toolbox.tutorial.webui");

/**
* Layout and page setup logic for region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);

//First get the Application Module
OAApplicationModule oam = pageContext.getApplicationModule(webBean);

//Lets say I need to get the description of FND_USER Names GUEST
String sWhereClauseValue = "GUEST" ;

//Build the select statement for this on the fly view object
String xxOnTheFlyVOQuery = "select description xxdesc from fnd_user WHERE user_"+
"_name = :1 ";

//First see if this VO is already attached to view object
ViewObject xxOnTheFlyViewObject = oam.findViewObject("xxFNDUserDescVO");
if(xxOnTheFlyViewObject == null)
xxOnTheFlyViewObject = oam.createViewObjectFromQueryStmt("xxFNDUserDescVO", xxOnTheFlyVOQuery);
//By now we are sure that the view object exists
xxOnTheFlyViewObject.setWhereClauseParams(null);
//Set the where clause
xxOnTheFlyViewObject.setWhereClauseParam(0, sWhereClauseValue);
xxOnTheFlyViewObject.executeQuery();
oracle.jbo.Row row = xxOnTheFlyViewObject.first();
//get the value of description column from View Object record returned
if(row != null)
{
String mSupHierarchyUsed = row.getAttribute(0).toString();
System.out.println("Result from Dynamic VO is =>" + mSupHierarchyUsed );
}
//Remove the view object, as this is no longer required
xxOnTheFlyViewObject.remove();
}

/**
* Procedure to handle form submissions for form elements in
* region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);

if (pageContext.getParameter("Go") != null)
{
// NEVER hard-code a message like this in your application. This is just
// shown for the sake of simplicity in this first lesson. In the next
// lessons you'll learn how to define translateable messages.
String userContent = pageContext.getParameter("HelloName");
String message = "Hello, " + userContent + "!";
throw new OAException(message, OAException.INFORMATION);

}
}
}

Example for Option 2:
package oracle.apps.pos.isp.server;

import java.sql.*;
import java.util.Vector;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.functionSecurity.FunctionSecurity;
import oracle.jbo.server.DBTransaction;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OraclePreparedStatement;

public class PosServerUtil
{

public static final String RCS_ID = "$Header: PosServerUtil.java 115.45.11510.1 2004/09/28 09:35:34 amkalia ship $";
public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header: PosServerUtil.java 115.45.11510.1 2004/09/28 09:35:34 amkalia ship $", "oracle.apps.pos.isp.server");
public static final String WHERE_CLAUSE_BEG_DELIMITER = "###";
public static final String WHERE_CLAUSE_END_DELIMITER = "$$$";
public static String ifContractEnabled = null;
public static String ifEresEnabled = null;
public static final String securePOString = "SELECT COUNT(*) FROM po_headers_all WHERE po_header_id = :1";

public static Object[] getParamArray(Vector vector)
{
int i = vector.size();
Object aobj[] = new Object[i];
for(int j = 0; j < s2 = "Y"> 0)
{
CallableStatement callablestatement = null;
ResultSet resultset = null;
StringBuffer stringbuffer = new StringBuffer("SELECT COUNT(*) FROM po_headers_all WHERE po_header_id = :1");
try
{
Vector vector = new Vector();
vector.addElement(s);
String s3 = "VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID";
String s4 = getSecureWhereClause(vector, oadbtransaction, s3);
if(s4 != null && s4.length() > 0)
{
stringbuffer.append(" AND ").append(s4);
}
int i = -1;
callablestatement = oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
for(int j = 0; j < resultset =" callablestatement.executeQuery();" i =" resultset.getInt(1);" i ="="" s2 = "N" s2 = "Y" s2 = "Y" s3 =" new" s4 =" new" s5 = "VENDOR_ID, VENDOR_SITE_ID" s6 = "I"> 0 && s1 != null && s1.length() > 0)
{
if("ASN".equals(s1))
{
s6 = "S";
s3 = "SHIPMENT_NUM";
s4 = "POS_VIEW_ASN";
} else
if("INVOICE".equals(s1))
{
s3 = "INVOICE_ID";
s4 = "AP_INVOICES_ALL";
} else
if("PAYMENT".equals(s1))
{
s3 = "CHECK_ID";
s4 = "AP_CHECKS_ALL";
} else
if("RFQ".equals(s1))
{
s3 = "PO_HEADER_ID";
s4 = "POS_PO_RFQ_HEADERS_V";
s5 = "VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID";
} else
if("RECEIPT".equals(s1))
{
s3 = "SHIPMENT_HEADER_ID";
s4 = "POS_RECEIPT_HEADERS_V";
s5 = "VENDOR_ID";
}
CallableStatement callablestatement = null;
ResultSet resultset = null;
try
{
StringBuffer stringbuffer = new StringBuffer("SELECT COUNT(*) FROM ");
stringbuffer.append(s4).append(" WHERE ").append(s3);
stringbuffer.append(" = :1");
Vector vector = new Vector();
vector.addElement(s);
String s7 = getSecureWhereClause(vector, oadbtransaction, s5);
if(s7 != null && s7.length() > 0)
{
stringbuffer.append(" AND ").append(s7);
}
int i = -1;
callablestatement = oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
for(int j = 0; j < j ="="" resultset =" callablestatement.executeQuery();" i =" resultset.getInt(1);" i ="="" s2 = "N" s2 = "Y" functionsecurity =" oadbtransaction.getFunctionSecurity();" function =" functionsecurity.getFunction(" function1 =" functionsecurity.getFunction(" function2 =" functionsecurity.getFunction(" function3 =" functionsecurity.getFunction(" function4 =" functionsecurity.getFunction(" function5 =" functionsecurity.getFunction(" integer =" new" integer ="="" null =" null" s1 = "" s3 = "" i =" oadbtransaction.getUserId();" callablestatement =" null;" callablestatement1 =" null;" resultset =" null;" resultset1 =" null;" s4 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" responsibility_id =" ?" attribute_application_id =" 177" s5 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" web_user_id =" ?" e_code =" ?" attribute_application_id =" 177" responsibility_id =" ?" attribute_application_id =" 177" attribute_code =" ?" number_value =" -9999" callablestatement =" oadbtransaction.createCallableStatement(s4," resultset =" callablestatement.executeQuery();" s6 = "" s2 =" resultset.getString(" s6 = "VENDOR_ID" s6 = "VENDOR_CONTACT_ID" s6 = "VENDOR_SITE_ID" s7 = "" s8 = "" callablestatement1 =" oadbtransaction.createCallableStatement(s5," resultset1 =" callablestatement1.executeQuery();" flag =" true;" j =" 0;" vector1 =" new" s9 =" resultset1.getString(" flag =" false;">= 254)
{
break;
}
}
if(j < k =" 0;" s7 ="="" s7 =" s7" s7 =" s7" s3 =" s3" s3 =" s3" s3 =" s3" s3 =" s3" s10 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" web_user_id =" :" attribute_code =" :" attribute_application_id =" 177" s3 =" s3" s3 =" s3" s3 ="="" null =" null" s4 =" null;" s4 =" getDocumentSecurityClause(oadbtransaction," s5 =" null;" s5 =" getOperatingUnitSecureClause(oadbtransaction," s5 =" getInventoryOrgSecureClause(oadbtransaction," s4 ="="" s4 =" s5;" s4 =" s4" obj =" null;" obj1 =" null;" s2 =" null;" s3 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; " callablestatement =" null;" j =" oadbtransaction.getOrgId();" k =" oadbtransaction.getEmployeeId();" s4 =" Integer.toString(k);" callablestatement =" oadbtransaction.createCallableStatement(s3," s =" callablestatement.getString(6);" s1 =" callablestatement.getString(7);" s2 =" callablestatement.getString(8);" s4 =" null;" s5 = "a#$$#1" s7 =" getDocumentSecurityClause(oadbtransaction," k =" oadbtransaction.getEmployeeId();" s8 =" Integer.toString(k);" stringbuffer =" new" l =" 0;" i1 =" s7.indexOf(s5,"> 0; i1 = s7.indexOf(s5, l))
{
stringbuffer.append(s7.substring(l, i1));
stringbuffer.append(":" + Integer.toString(i++));
vector.addElement(s8);
l = i1 + s5.length();
}

stringbuffer.append(s7.substring(l));
s4 = stringbuffer.toString();
}
}
if(s2 != null && !"".equals(s2))
{
String s6 = null;
if("OU".equals(s2))
{
s6 = getOperatingUnitSecureClause(oadbtransaction, s3);
} else
if("INV".equals(s2))
{
s6 = getInventoryOrgSecureClause(oadbtransaction, s3);
}
if(s6 != null && !"".equals(s6))
{
if(s4 == null)
{
s4 = s6;
} else
{
s4 = s4 + " and " + s6;
}
}
}
return s4;
}

public static String getDocumentSecurityClause(OADBTransaction oadbtransaction, int i, int j)
{
Object obj = null;
Object obj1 = null;
String s2 = null;
if(j > 0)
{
String s3 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; ";
CallableStatement callablestatement = null;
try
{
int k = oadbtransaction.getEmployeeId();
String s4 = Integer.toString(k);
callablestatement = oadbtransaction.createCallableStatement(s3, 1);
callablestatement.setString(1, "QRSLT");
callablestatement.setString(2, "AGENT_ID");
callablestatement.setString(3, s4);
callablestatement.setInt(4, i);
callablestatement.setInt(5, j);
callablestatement.registerOutParameter(6, 12);
callablestatement.registerOutParameter(7, 12);
callablestatement.registerOutParameter(8, 12);
callablestatement.execute();
String s = callablestatement.getString(6);
String s1 = callablestatement.getString(7);
if(!"U".equals(s) && !"E".equals(s))
{
s2 = callablestatement.getString(8);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return s2;
}

public static String getDocumentSecurityClause(OADBTransaction oadbtransaction, int i, int j, String s)
{
Object obj = null;
Object obj1 = null;
String s3 = null;
if(j > 0)
{
String s4 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; ";
CallableStatement callablestatement = null;
try
{
callablestatement = oadbtransaction.createCallableStatement(s4, 1);
callablestatement.setString(1, "QRSLT");
callablestatement.setString(2, "AGENT_ID");
callablestatement.setString(3, s);
callablestatement.setInt(4, i);
callablestatement.setInt(5, j);
callablestatement.registerOutParameter(6, 12);
callablestatement.registerOutParameter(7, 12);
callablestatement.registerOutParameter(8, 12);
callablestatement.execute();
String s1 = callablestatement.getString(6);
String s2 = callablestatement.getString(7);
if(!"U".equals(s1) && !"E".equals(s1))
{
s3 = callablestatement.getString(8);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return s3;
}

public static String getOperatingUnitSecureClause(OADBTransaction oadbtransaction, String s)
{
String s1 = null;
String s2 = "select count(*) from (select organization_id from per_organization_list where se" +
"curity_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))"
;
int i = 0;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
if(i > 0)
{
s1 = s + " in (select organization_id from per_organization_list " + "where security_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))";
}
return s1;
}

public static String getInventoryOrgSecureClause(OADBTransaction oadbtransaction, String s)
{
String s1 = null;
String s2 = "select count(*) from (select organization_id from per_organization_list where se" +
"curity_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))"
;
int i = 0;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception _ex) { }
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
if(i > 0)
{
s1 = s + " in (select organization_id from " + "org_organization_definitions where operating_unit in (select organization_id fro" +
"m "
+ "per_organization_list where security_profile_id = " + "fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')))";
}
return s1;
}

public static String getSupplierView(OADBTransaction oadbtransaction)
{
String s = "N";
try
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
if(functionsecurity.testFunction(function) functionsecurity.testFunction(function1) functionsecurity.testFunction(function2) functionsecurity.testFunction(function3))
{
s = "Y";
}
}
catch(Exception _ex) { }
return s;
}

public static String getBuyerView(String s, OADBTransaction oadbtransaction)
{
String s1 = s;
if("Y".equals(s1))
{
try
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_INTERNAL_HOME");
if(functionsecurity.testFunction(function))
{
s1 = "Y";
} else
{
s1 = "N";
}
}
catch(Exception _ex) { }
} else
{
s1 = "N";
}
return s1;
}

public static String getSupplierSecureClause(Vector vector, OADBTransaction oadbtransaction, String s)
throws SQLException
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function4 = functionsecurity.getFunction("POS_INTERNAL_HOME");
oracle.apps.fnd.functionSecurity.Function function5 = functionsecurity.getFunction("POS_VIEW_ORDER");
Integer integer = new Integer(oadbtransaction.getResponsibilityId());
if(integer == null oadbtransaction.getResponsibilityId() == 0 !functionsecurity.testFunction(function) && !functionsecurity.testFunction(function1) && !functionsecurity.testFunction(function2) && !functionsecurity.testFunction(function3) && !functionsecurity.testFunction(function4) && !functionsecurity.testFunction(function5))
{
return "null = null";
}
String s1 = "";
String s3 = "";
int i = oadbtransaction.getUserId();
CallableStatement callablestatement = null;
CallableStatement callablestatement1 = null;
ResultSet resultset = null;
ResultSet resultset1 = null;
String s4 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" +
"HERE arsa.responsibility_id = ? AND arsa.attribute_application_id = 177"
;
String s5 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" +
"value FROM ak_web_user_sec_attr_values WHERE web_user_id = ? AND attribut" +
"e_code = ? AND attribute_application_id = 177 UNION SELECT nvl(to_char(numbe" +
"r_value), nvl(varchar2_value,to_char(date_value))) sec_value FROM AK_RESP_SECURI" +
"TY_ATTR_VALUES WHERE responsibility_id = ? AND attribute_application_id = 177 A" +
"ND attribute_code = ? AND number_value = -9999 ORDER BY sec_value "
;
try
{
callablestatement = oadbtransaction.createCallableStatement(s4, 1);
callablestatement.setInt(1, oadbtransaction.getResponsibilityId());
for(resultset = callablestatement.executeQuery(); resultset.next();)
{
String s6 = "";
String s2 = resultset.getString("attribute_code");
if(s2 != null)
{
if(s2.equals("ICX_SUPPLIER_ORG_ID") && s.indexOf("SUPPLIER_ID") != -1)
{
s6 = "SUPPLIER_ID";
} else
if(s2.equals("ICX_SUPPLIER_CONTACT_ID") && s.indexOf("VENDOR_CONTACT_ID") != -1)
{
s6 = "VENDOR_CONTACT_ID";
} else
if(s2.equals("ICX_SUPPLIER_SITE_ID") && s.indexOf("SUPPLIER_SITE_ID") != -1)
{
s6 = "SUPPLIER_SITE_ID";
}
if(s6 != "")
{
String s7 = "";
String s8 = "";
callablestatement1 = oadbtransaction.createCallableStatement(s5, 1);
callablestatement1.setInt(1, i);
callablestatement1.setString(2, s2);
callablestatement1.setInt(3, oadbtransaction.getResponsibilityId());
callablestatement1.setString(4, s2);
resultset1 = callablestatement1.executeQuery();
boolean flag = true;
int j = 0;
Vector vector1 = new Vector();
while(resultset1.next())
{
String s9 = resultset1.getString("sec_value");
if(s9 != null && s9 != "")
{
flag = false;
if(!s9.equals("-9999"))
{
vector1.addElement(s9);
}
j++;
}
if(j >= 254)
{
break;
}
}
if(j < k =" 0;" s7 ="="" s7 =" s7" s7 =" s7" s3 =" s3" s3 =" s3" s3 =" s3" s3 =" s3" s10 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" web_user_id =" :" attribute_code =" :" attribute_application_id =" 177" s3 =" s3" s3 =" s3" s3 ="="" null =" null" i =" 0;"> 0 && !s.equals("null"))
{
int j = Integer.parseInt(s);
String s2 = "select org_id from po_headers_all where po_header_id = :1";
if(s1 != null && s1.length() > 0 && !s1.equals("null"))
{
j = Integer.parseInt(s1);
s2 = "select org_id from po_releases_all where po_release_id = :1";
}
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
callablestatement.setInt(1, j);
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return i;
}

private static String isNumber(String s, String s1)
{
if(s1.equals("NUMBER"))
{
return s;
} else
{
return "'" + s + "'";
}
}

public static String wrapValue(String s)
{
return "###" + s + "$$$";
}

public static final String replaceNull(Object obj)
{
if(obj == null obj.equals("null"))
{
return "";
} else
{
return obj.toString();
}
}

public static final int replaceInteger(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0;
} else
{
return Integer.parseInt(obj.toString());
}
}

public static final float replaceFloat(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0.0F;
} else
{
Float float1 = new Float(obj.toString());
return float1.floatValue();
}
}

public static final double replaceDouble(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0.0D;
} else
{
Double double1 = new Double(obj.toString());
return double1.doubleValue();
}
}

public static String ifContractsInstalled(OADBTransaction oadbtransaction)
throws SQLException
{
StringBuffer stringbuffer = new StringBuffer(" BEGIN");
stringbuffer.append(" :1 := PO_CONTERMS_UTL_GRP.is_contracts_enabled();");
stringbuffer.append(" END;");
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
oraclecallablestatement.registerOutParameter(1, 12, 0, 1);
oraclecallablestatement.executeQuery();
ifContractEnabled = oraclecallablestatement.getString(1);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return ifContractEnabled;
}

public static String ifEresInstalled(OADBTransaction oadbtransaction)
throws SQLException
{
String s = "{call PO_ERECORDS_PVT.ERECORDS_ENABLED(:1) }";
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(s, 1);
oraclecallablestatement.registerOutParameter(1, 12, 0, 1);
oraclecallablestatement.executeQuery();
ifEresEnabled = oraclecallablestatement.getString(1);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return ifEresEnabled;
}

public static String ifXdoInstalled(String s, OADBTransaction oadbtransaction)
throws SQLException
{
String s1 = null;
StringBuffer stringbuffer = new StringBuffer(" BEGIN ");
stringbuffer.append(" fnd_client_info.set_org_context(:1) ; ");
stringbuffer.append(" :2 := PO_COMMUNICATION_PVT.po_communication_profile();");
stringbuffer.append(" END;");
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
oraclecallablestatement.registerOutParameter(2, 12, 0, 1);
oraclecallablestatement.setString(1, s);
oraclecallablestatement.executeQuery();
s1 = oraclecallablestatement.getString(2);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return s1;
}

public static String[] checkNegotiations(OADBTransaction oadbtransaction, String s)
throws SQLException
{
String as[] = new String[2];
String s1 = "declare x boolean; BEGIN x := FND_INSTALLATION.GET_APP_INFO ( :1, :" +
"2, :3, :4 ); END; "
;
String s2 = "BEGIN pon_auction_interface_pkg.Get_PO_Negotiation_Link ( :1, :2, " +
" :3, :4, :5, :6, :7 ); END; "
;
String s3 = null;
String s4 = null;
String s5 = "";
try
{
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s1, 1);
callablestatement.setString(1, "PON");
callablestatement.registerOutParameter(2, 12);
callablestatement.registerOutParameter(3, 12);
callablestatement.registerOutParameter(4, 12);
callablestatement.execute();
s5 = callablestatement.getString(2);
callablestatement.close();
}
catch(Exception exception)
{
exception.printStackTrace();
}
CallableStatement callablestatement1 = null;
if(s5.equals("I"))
{
try
{
callablestatement1 = oadbtransaction.createCallableStatement(s2, 1);
callablestatement1.setInt(1, Integer.parseInt(s));
callablestatement1.registerOutParameter(2, 4);
callablestatement1.registerOutParameter(3, 12);
callablestatement1.registerOutParameter(4, 12);
callablestatement1.registerOutParameter(5, 4);
callablestatement1.registerOutParameter(6, 12);
callablestatement1.registerOutParameter(7, 12);
callablestatement1.execute();
String s6 = String.valueOf(callablestatement1.getInt(2));
s4 = callablestatement1.getString(3);
s3 = callablestatement1.getString(4);
callablestatement1.getInt(5);
callablestatement1.getString(6);
callablestatement1.getString(7);
s3 = s3 + "&AuctionId=" + "{!!" + oadbtransaction.encrypt(s6) + "&from=ISP";
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
finally
{
try
{
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
}
as[0] = s3;
as[1] = s4;
return as;
}

public static String checkShipments(OADBTransaction oadbtransaction, String s, String s1)
throws SQLException
{
String s2 = "";
if(s1 != null && s1.length() > 0)
{
String s3 = "Begin select count(distinct ship_to_location_id) into :1 from po_line_locations_" +
"all where po_release_id= :2 ; end;"
;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s3, 1);
try
{
callablestatement.registerOutParameter(1, 4);
callablestatement.setInt(2, Integer.parseInt(s1));
callablestatement.execute();
s2 = String.valueOf(callablestatement.getInt(1));
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
finally
{
try
{
callablestatement.close();
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
} else
if(s != null)
{
String s4 = "Begin select count(distinct ship_to_location_id) into :1 from po_line_locations_" +
"all where po_header_id= :2 ; end;"
;
CallableStatement callablestatement1 = oadbtransaction.createCallableStatement(s4, 1);
try
{
callablestatement1.registerOutParameter(1, 4);
callablestatement1.setInt(2, Integer.parseInt(s));
callablestatement1.execute();
s2 = String.valueOf(callablestatement1.getInt(1));
}
catch(Exception exception4)
{
exception4.printStackTrace();
}
finally
{
try
{
callablestatement1.close();
}
catch(Exception exception5)
{
exception5.printStackTrace();
}
}
}
return s2;
}

public static String getGASecureWhereClause(Vector vector, OADBTransaction oadbtransaction)
throws SQLException
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function4 = functionsecurity.getFunction("POS_INTERNAL_HOME");
Integer integer = new Integer(oadbtransaction.getResponsibilityId());
if(integer == null oadbtransaction.getResponsibilityId() == 0 !functionsecurity.testFunction(function) && !functionsecurity.testFunction(function1) && !functionsecurity.testFunction(function2) && !functionsecurity.testFunction(function3) && !functionsecurity.testFunction(function4))
{
return "null = null";
}
String s = "";
String s2 = "";
int i = oadbtransaction.getUserId();
CallableStatement callablestatement = null;
CallableStatement callablestatement1 = null;
ResultSet resultset = null;
ResultSet resultset1 = null;
String s3 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" +
"HERE arsa.responsibility_id = ? AND arsa.attribute_application_id = 177"
;
String s4 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" +
"value FROM ak_web_user_sec_attr_values WHERE web_user_id = ? AND attribut" +
"e_code = ? AND attribute_application_id = 177 UNION SELECT nvl(to_char(numbe" +
"r_value), nvl(varchar2_value,to_char(date_value))) sec_value FROM AK_RESP_SECU" +
"RITY_ATTR_VALUES WHERE responsibility_id = ? AND attribute_application_id =" +
" 177 AND attribute_code = ? AND number_value = -9999 ORDER BY sec_value "
;
try
{
callablestatement = oadbtransaction.createCallableStatement(s3, 1);
callablestatement.setInt(1, oadbtransaction.getResponsibilityId());
for(resultset = callablestatement.executeQuery(); resultset.next();)
{
String s5 = "";
String s1 = resultset.getString("attribute_code");
if(s1 != null)
{
if(s1.equals("ICX_SUPPLIER_ORG_ID"))
{
s5 = "VENDOR_ID";
} else
if(s1.equals("ICX_SUPPLIER_CONTACT_ID"))
{
s5 = "VENDOR_CONTACT_ID";
} else
if(s1.equals("ICX_SUPPLIER_SITE_ID"))
{
s5 = "VENDOR_SITE_ID";
}
if(s5 != "")
{
String s6 = "";
String s7 = "";
Vector vector1 = new Vector();
callablestatement1 = oadbtransaction.createCallableStatement(s4, 1);
callablestatement1.setInt(1, i);
callablestatement1.setString(2, s1);
callablestatement1.setInt(3, oadbtransaction.getResponsibilityId());
callablestatement1.setString(4, s1);
resultset1 = callablestatement1.executeQuery();
boolean flag = true;
int j = 0;
Vector vector2 = new Vector();
while(resultset1.next())
{
String s8 = resultset1.getString("sec_value");
if(s8 != null && s8 != "")
{
flag = false;
if(!s8.equals("-9999"))
{
vector2.addElement(s8);
}
j++;
}
if(j >= 254)
{
break;
}
}
if(j < 254)
{
for(int k = 0; k < vector2.size(); k++)
{
if(s5.equals("VENDOR_SITE_ID"))
{
vector1.addElement(vector2.elementAt(k));
}
vector.addElement(vector2.elementAt(k));
if(s6 == "")
{
s6 = s6 + ":" + vector.size();
} else
{
s6 = s6 + ", :" + vector.size();
}
}

if(flag)
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " = null";
}
if(s6 != "" && !s5.equals("VENDOR_SITE_ID"))
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " in (" + s6 + ")";
} else
if(s6 != "" && s5.equals("VENDOR_SITE_ID"))
{
if(s2 != "")
{
s2 = s2 + " AND ";
}
s2 = s2 + " ((VENDOR_SITE_ID in (" + s6 + ")" + " AND NVL(global_agreement_flag, 'N') = 'N')";
s6 = "";
if(!vector1.isEmpty())
{
for(int l = 0; l < vector1.size(); l++)
{
vector.addElement(vector1.elementAt(l));
if(s6 == "")
{
s6 = ":" + vector.size();
} else
{
s6 = s6 + ", :" + vector.size();
}
}

s2 = s2 + " OR (PO_RELEASE_ID is null AND PO_HEADER_ID in ( select PGA.po_header_id from " +
"PO_GA_ORG_ASSIGNMENTS PGA, PO_HEADERS_ALL POH where PGA.po_header_id = POH.po_h" +
"eader_id and PGA.enabled_flag = 'Y' and POH.global_agreement_flag = 'Y' an" +
"d PGA.vendor_site_id in ("
+ s6 + "))))";
}
}
} else
{
vector.addElement(Integer.toString(i));
vector.addElement(s1);
String s9 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" +
"M ak_web_user_sec_attr_values WHERE web_user_id = :"
+ (vector.size() - 1) + " AND attribute_code = :" + vector.size() + " AND attribute_application_id = 177 ";
if(s5.equals("VENDOR_SITE_ID"))
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + " ((VENDOR_SITE_ID in (" + s9 + ")" + " AND NVL(global_agreement_flag, 'N') = 'N')" + " OR (PO_RELEASE_ID is null AND PO_HEADER_ID in (" + " select PGA.po_header_id" + " from PO_GA_ORG_ASSIGNMENTS PGA, PO_HEADERS_ALL POH" + " where PGA.po_header_id = POH.po_header_id" + " and PGA.enabled_flag = 'Y'" + " and POH.global_agreement_flag = 'Y'" + " and PGA.vendor_site_id in (" + s9 + "))))";
} else
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " in (" + s9 + ")";
}
}
try
{
if(resultset1 != null)
{
resultset1.close();
}
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
}
}

}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset1 != null)
{
resultset1.close();
}
if(resultset != null)
{
resultset.close();
}
if(callablestatement != null)
{
callablestatement.close();
}
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
}
if(s2 == null s2.trim().length() == 0)
{
return " null = null ";
} else
{
return s2;
}
}

public PosServerUtil()
{
}

}