/* Formatted on 06/12/2010 13:20:04 (QP5 v5.114.809.3010) */
SELECT DISTINCT
p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
DECODE (v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'UnDef')
"CONTEXT",
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
and n.user_profile_option_name = 'ICX: Numeric characters'
--or n.user_profile_option_name like '%Password%Case%')
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
--AND v.profile_option_value LIKE '%PROD%'
AND n.LANGUAGE = 'US'
ORDER BY short_name, level_set
Monday, January 31, 2011
Email string validation code in PL/SQL
Form validations for PL/SQL
--***PL/SQL code for Email validation***---
DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
t_atpos NUMBER(2):= 1;
i NUMBER(2) := 1;
t_pointpos NUMBER(2):= 1;
mail_ch VARCHAR2(1);
result number; --:GR
BEGIN
t_totallen := LENGTH(:text_item_email);
t_counter := t_totallen;
i := 1;
t_valid := 1;
-------------------------------------------------------------------------------------
IF LENGTH(ltrim(rtrim(:text_item_email))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check special characters are present or not in the email ID
t_counter := t_totallen;
WHILE t_counter > 0
LOOP
mail_ch := substr(:text_item_email,i,1);
i := i+1;
t_counter := t_counter -1;
IF mail_ch IN (' ','!','#','$','%','^','&','*','(',')','-','','"',
'+','|','{','}','[',']',':','>','<','?','/','\','=') THEN t_valid := 0; EXIT; END IF; END LOOP; --------------------------------------------------------------------------------------- --This is to check more than one '@' character present or not t_atpos := instr(:text_item_email,'@',1,2) ; IF t_atpos > 1 then
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at minimum and at maximum only one '@' character present
t_atpos := instr(:text_item_email,'@',1) ;
IF t_atpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one '.' character present or not
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one character is present between @ and '.' :GR
t_atpos := instr(:text_item_email,'@',1) ;
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos-t_atpos<=1 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- --This is to check at least one character is present after '.' :GR t_pointpos := instr(:text_item_email,'.',1) ; IF t_totallen-t_pointpos<=0 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- END IF; if(t_valid=0) then message('Invalid Email'); result:=show_alert('alert_email'); raise form_trigger_failure; end if; END; ---***PL/SQL code for Name Validation***--- DECLARE t_valid NUMBER(1); t_totallen NUMBER(2); t_counter NUMBER(2):=0; i NUMBER(2) := 1; name_ch VARCHAR2(1); --name_ch_ascii NUMBER; result number; --:GR BEGIN t_totallen := LENGTH(:text_item_first_name); t_counter := t_totallen; i := 1; t_valid := 1; ------------------------------------------------------------------------------------- IF LENGTH(ltrim(rtrim(:text_item_first_name))) = 0 THEN t_valid := 0; ELSE --------------------------------------------------------------------------------------- --This is to check if each character in name lies in the valid ascii range t_counter := t_totallen; WHILE t_counter > 0
LOOP
name_ch := upper(substr(:text_item_first_name,i,1));
--name_ch_ascii := convert(name_ch,'US7ASCII');
i := i+1;
t_counter := t_counter -1;
--IF name_ch_ascii not between 65 and 90 THEN
IF name_ch not between 'A' and 'Z' THEN
t_valid := 0;
EXIT;
END IF;
END LOOP;
---------------------------------------------------------------------------------------
END IF;
if(t_valid=0) then
message('Invalid First Name');
result:=show_alert('alert_first_name');
raise form_trigger_failure;
end if;
END;
--***PL/SQL code for Email validation***---
DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
t_atpos NUMBER(2):= 1;
i NUMBER(2) := 1;
t_pointpos NUMBER(2):= 1;
mail_ch VARCHAR2(1);
result number; --:GR
BEGIN
t_totallen := LENGTH(:text_item_email);
t_counter := t_totallen;
i := 1;
t_valid := 1;
-------------------------------------------------------------------------------------
IF LENGTH(ltrim(rtrim(:text_item_email))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check special characters are present or not in the email ID
t_counter := t_totallen;
WHILE t_counter > 0
LOOP
mail_ch := substr(:text_item_email,i,1);
i := i+1;
t_counter := t_counter -1;
IF mail_ch IN (' ','!','#','$','%','^','&','*','(',')','-','','"',
'+','|','{','}','[',']',':','>','<','?','/','\','=') THEN t_valid := 0; EXIT; END IF; END LOOP; --------------------------------------------------------------------------------------- --This is to check more than one '@' character present or not t_atpos := instr(:text_item_email,'@',1,2) ; IF t_atpos > 1 then
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at minimum and at maximum only one '@' character present
t_atpos := instr(:text_item_email,'@',1) ;
IF t_atpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one '.' character present or not
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one character is present between @ and '.' :GR
t_atpos := instr(:text_item_email,'@',1) ;
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos-t_atpos<=1 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- --This is to check at least one character is present after '.' :GR t_pointpos := instr(:text_item_email,'.',1) ; IF t_totallen-t_pointpos<=0 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- END IF; if(t_valid=0) then message('Invalid Email'); result:=show_alert('alert_email'); raise form_trigger_failure; end if; END; ---***PL/SQL code for Name Validation***--- DECLARE t_valid NUMBER(1); t_totallen NUMBER(2); t_counter NUMBER(2):=0; i NUMBER(2) := 1; name_ch VARCHAR2(1); --name_ch_ascii NUMBER; result number; --:GR BEGIN t_totallen := LENGTH(:text_item_first_name); t_counter := t_totallen; i := 1; t_valid := 1; ------------------------------------------------------------------------------------- IF LENGTH(ltrim(rtrim(:text_item_first_name))) = 0 THEN t_valid := 0; ELSE --------------------------------------------------------------------------------------- --This is to check if each character in name lies in the valid ascii range t_counter := t_totallen; WHILE t_counter > 0
LOOP
name_ch := upper(substr(:text_item_first_name,i,1));
--name_ch_ascii := convert(name_ch,'US7ASCII');
i := i+1;
t_counter := t_counter -1;
--IF name_ch_ascii not between 65 and 90 THEN
IF name_ch not between 'A' and 'Z' THEN
t_valid := 0;
EXIT;
END IF;
END LOOP;
---------------------------------------------------------------------------------------
END IF;
if(t_valid=0) then
message('Invalid First Name');
result:=show_alert('alert_first_name');
raise form_trigger_failure;
end if;
END;
Property to set in JDeveloper to make the VO substitution work locally.
In order for JDeveloper to recognize the vo substitution you need to proform below setup in JDEV.
Navaigation: Select the project, right click and go to project setting
> Configurations > Development > Runner
Add the following at the end of the "Java Options" strings
-Djbo.project=<Project Name with out .jpr extension>
eg: -Djbo.project=iSupplierPortal
Navaigation: Select the project, right click and go to project setting
> Configurations > Development > Runner
Add the following at the end of the "Java Options" strings
-Djbo.project=<Project Name with out .jpr extension>
eg: -Djbo.project=iSupplierPortal
How to personalize the embedded notification content sent via e-mail?
--------------------------------------------------------------------------------------------------
SSHR application generated approval notification with notification containing all the information of the transaction.
How to personalize the embedded notification content sent via e-mail?
---------------------------------------------------------------------
Solution:
To implement the solution, please execute the following steps:
1 - Make profile Personalize Self-Service Defn is set to Yes at user level for the user who is going to perform personalization. This profile need not be set for all other users.
2 - Login to the application , click "workflow user web applications" responsibility.
3 - Start Worklist/Notifications function.
3 - Access the notification.
4 - Perform a SITE level personalization.
Now, in all future email notifications, the personalized content is sent to the approver via email.
---------------------------------------------------------------------------------------------------
SSHR application generated approval notification with notification containing all the information of the transaction.
How to personalize the embedded notification content sent via e-mail?
---------------------------------------------------------------------
Solution:
To implement the solution, please execute the following steps:
1 - Make profile Personalize Self-Service Defn is set to Yes at user level for the user who is going to perform personalization. This profile need not be set for all other users.
2 - Login to the application , click "workflow user web applications" responsibility.
3 - Start Worklist/Notifications function.
3 - Access the notification.
4 - Perform a SITE level personalization.
Now, in all future email notifications, the personalized content is sent to the approver via email.
---------------------------------------------------------------------------------------------------
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()
{
}
}
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()
{
}
}
OAF - Creating Runtime ViewObject
Code to create viewobject dynamically at runtime:
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
...
...
OAApplicationModule am = pageContext.getApplicationModule(webBean);
// Before you create the VO, check if any instances of the VO object exists or not.
ViewObject checkQVO = am.findViewObject("Your VO Name");
if (checkQVO != null) {
// if one exists then delete that view object.
checkQVO.remove();
}
// Now dynamically create the view object with your custom query using createViewObjectFromQueryStmt method.
checkQVO = am.createViewObjectFromQueryStmt("Your VO Name", "SELECT count(*) as rec_count FROM emp WHERE person"
+"_id = :1");
//NOTE: split the column name into two where need to break the line, otherwise it will give you runtime error. For instance if you change the where clause to be
"SELECT... WHERE person_id "
+= :1");. This will lead to runtime error.
checkQVO.setWhereClauseParams(null);
checkQVO.setWhereClauseParam(0, suppReqId); // pass the input value
checkQVO.executeQuery(); // execute the query
Row checkQRow = checkQVO.getRowAtRangeIndex(0);
if (checkQRow != null) {
int checkQCount = ((Number)checkQRow.getAttribute(0)).intValue();
if (checkQCount == 0) {
throw new OAException("< Type your error message here...>");
}
}
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAViewObject;
...
...
OAApplicationModule am = pageContext.getApplicationModule(webBean);
// Before you create the VO, check if any instances of the VO object exists or not.
ViewObject checkQVO = am.findViewObject("Your VO Name");
if (checkQVO != null) {
// if one exists then delete that view object.
checkQVO.remove();
}
// Now dynamically create the view object with your custom query using createViewObjectFromQueryStmt method.
checkQVO = am.createViewObjectFromQueryStmt("Your VO Name", "SELECT count(*) as rec_count FROM emp WHERE person"
+"_id = :1");
//NOTE: split the column name into two where need to break the line, otherwise it will give you runtime error. For instance if you change the where clause to be
"SELECT... WHERE person_id "
+= :1");. This will lead to runtime error.
checkQVO.setWhereClauseParams(null);
checkQVO.setWhereClauseParam(0, suppReqId); // pass the input value
checkQVO.executeQuery(); // execute the query
Row checkQRow = checkQVO.getRowAtRangeIndex(0);
if (checkQRow != null) {
int checkQCount = ((Number)checkQRow.getAttribute(0)).intValue();
if (checkQCount == 0) {
throw new OAException("< Type your error message here...>");
}
}
Oracle applications - Order to Cash cycle - Table Details
This is available in metalink and many other internet sites/blogs. I have reproduced these contests for my understanding and reference only.
Order Management Tables.
Entered:
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked:
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.
Payment Terms
ra_terms
AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details
Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits
Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text
Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all
Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all
Hold Relaese
oe_hold_releases_all Hold released Sales Order.
Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.
Cancel Orders
oe_order_lines_all Cancel Order Details.
Order Management Tables.
Entered:
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked:
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.
Payment Terms
ra_terms
AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details
Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits
Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text
Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all
Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all
Hold Relaese
oe_hold_releases_all Hold released Sales Order.
Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.
Cancel Orders
oe_order_lines_all Cancel Order Details.
Subscribe to:
Posts (Atom)