Assume we have three fields first Resource Name, second Destination
Organization and third Destination Sub-inventory. Now we are going to create LOV on Destination Sub-inventory which is dependent on Resource Name and Destination Organization.
For a dependent LOV we need to create 3 java files.
1. SQL
2. Validator
3. Component
1. SQL:
package holx.oracle.apps.icx;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.utilities.sql.BneBaseSQL;
public class HolxDestSubSQL extends BneBaseSQL {
public HolxDestSubSQL(BneWebAppsContext paramBneWebAppsContext, String s1, String s2,
String paramString) throws SQLException,
BneException {
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT SECONDARY_INVENTORY_NAME, DESCRIPTION FROM MTL_SUBINVENTORIES_VAL_V WHERE SECONDARY_INVENTORY_NAME IN (SELECT DISTINCT SUBINVENTORY_CODE from CSP_RS_SUBINVENTORIES_V WHERE CONDITION_TYPE_MEANING = 'Usable' AND SYSDATE BETWEEN EFFECTIVE_DATE_START AND NVL(EFFECTIVE_DATE_END,SYSDATE) AND UPPER(RESOURCE_NAME) = UPPER(:1) AND UPPER(ORGANIZATION_CODE) = UPPER(:2)) ");
if ((paramString != null) && (!paramString.trim().equals(""))) {
stringBuffer.append(" AND " + paramString);
}
setQuery(connection, stringBuffer.toString());
}
}
2. Validator:
package holx.oracle.apps.icx;
import java.util.Hashtable;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.exception.BneFatalException;
import oracle.apps.bne.exception.BneMissingParameterException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.integrator.validators.BneUploadValidator;
import oracle.apps.bne.utilities.sql.BneCompositeSQLCriteria;
import oracle.apps.bne.utilities.sql.BneResultSet;
import oracle.apps.bne.utilities.sql.BneSQLStatement;
import holx.oracle.apps.icx.HolxDestSubSQL;
public class HolxDestSubValidator extends BneUploadValidator {
public String[] getDomainParameters() {
return new String[] {"P_RESOURCE_NAME", "P_DESTINATION_ORGANIZATION"};
}
public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
HolxDestSubSQL HolxDestSubSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();
if (paramBneCompositeSQLCriteria != null)
bneSQLStatement1 = paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
try
{
String s1 = (String)paramHashtable.get("P_RESOURCE_NAME");
String s2 = (String)paramHashtable.get("P_DESTINATION_ORGANIZATION");
if(s1 == null || "".equals(s1) || s2 == null || "".equals(s2)) {
throw new BneMissingParameterException("Error while fetching Source Name in HolxDestSubValidator");
}
HolxDestSubSQL holxdestsubsql =
new HolxDestSubSQL(paramBneWebAppsContext, s1,s2,
bneSQLStatement1.getStatement());
BneSQLStatement bneSQLStatement2 =
new BneSQLStatement(holxdestsubsql.getQuery(),
new Object[] { s1,s2 });
bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet =
holxdestsubsql.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
} catch (Exception exception) {
throw new BneFatalException(exception.toString());
}
return bneResultSet;
}
}
3. Component:
package holx.oracle.apps.icx;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.Vector;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.exception.BneMissingParameterException;
import oracle.apps.bne.exception.BneParameterException;
import oracle.apps.bne.exception.BneSQLException;
import oracle.apps.bne.framework.BneBajaContext;
import oracle.apps.bne.framework.BneBajaPage;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.integrator.component.BneAbstractListOfValues;
import oracle.apps.bne.parameter.BneParameter;
import oracle.apps.bne.repository.BneResourceString;
import oracle.apps.bne.utilities.BneUIXUtils;
import oracle.apps.bne.utilities.sql.BneCompositeSQLCriteria;
import oracle.apps.bne.utilities.sql.BneResultSet;
import oracle.apps.bne.utilities.sql.BneSimpleSQLCriteria;
import oracle.apps.bne.webui.control.BneLOVControlBean;
import oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;
import holx.oracle.apps.icx.HolxDestSubValidator;
public class HolxDestSubComponent extends BneAbstractListOfValues {
private HolxDestSubValidator VALIDATOR = null;
private String[] VALIDATOR_PARAMS = null;
private String FILTERFIELD = null;
private String FILTERVALUE = null;
public String getLOVProcessorType() {
return "TABLE";
}
public void init(BneBajaContext paramBneBajaContext, Page paramPage,
PageEvent paramPageEvent) {
if (VALIDATOR == null) {
VALIDATOR = new HolxDestSubValidator();
VALIDATOR_PARAMS = VALIDATOR.getDomainParameters();
}
}
public BneBajaPage handleListOfValues(BneBajaContext paramBneBajaContext,
Page paramPage,
PageEvent paramPageEvent,
BneLOVControlBean paramBneLOVControlBean) throws BneException {
BneWebAppsContext bneWebAppsContext =
paramBneBajaContext.getBneWebAppsContext();
BneCompositeSQLCriteria bneCompositeSQLCriteria = null;
Hashtable hashtable = new Hashtable();
handlePageParameters(paramPageEvent);
for (int i = 0; i < VALIDATOR_PARAMS.length; i++) {
String str2 =
getParameterValue(bneWebAppsContext, VALIDATOR_PARAMS[i]);
if (str2 == null)
continue;
hashtable.put(VALIDATOR_PARAMS[i], str2);
}
if ((FILTERVALUE != null) && (!FILTERVALUE.trim().equals(""))) {
bneCompositeSQLCriteria = new BneCompositeSQLCriteria();
if (FILTERFIELD != null && !FILTERFIELD.equals("")) {
BneSimpleSQLCriteria bneSimpleSQLCriteria;
if (FILTERFIELD.equals("SECONDARY_INVENTORY_NAME"))
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "SECONDARY_INVENTORY_NAME", 0,
9, FILTERVALUE, 2);
else
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "DESCRIPTION",0,
9, FILTERVALUE, 2);
bneSimpleSQLCriteria.setSearchsCaseInsensitivity(true);
bneCompositeSQLCriteria.addCriteria(bneSimpleSQLCriteria);
}
}
setTableFilter(true);
setTableData(getTableData(bneWebAppsContext, paramBneLOVControlBean,
hashtable, bneCompositeSQLCriteria));
return null;
}
public void getListOfValueParameters() throws BneParameterException {
for (int i = 0; i < VALIDATOR_PARAMS.length; i++) {
String str1 = VALIDATOR_PARAMS[i];
String str2 ="Test" + str1 + ".";
addComponentParameter(new BneParameter(str1, "", str2));
}
}
private void handlePageParameters(PageEvent paramPageEvent) throws BneException {
FILTERFIELD =
BneUIXUtils.getPageEventParameter(paramPageEvent, "listOfValues:bne:filterField");
FILTERVALUE =
BneUIXUtils.getPageEventParameter(paramPageEvent, "listOfValues:bne:filterValue");
}
public DictionaryData[] getTableData(BneWebAppsContext paramBneWebAppsContext,
BneLOVControlBean paramBneLOVControlBean,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
DictionaryData dictionaryData = null;
Vector vector = new Vector();
BneResultSet bneResultSet = null;
ResultSetMetaData resultSetMetaData = null;
try {
String str = null;
bneResultSet =
VALIDATOR.getDomainValues(paramBneWebAppsContext, paramHashtable,
paramBneCompositeSQLCriteria);
if (bneResultSet != null) {
resultSetMetaData = bneResultSet.getResultSet().getMetaData();
while (bneResultSet.next()) {
dictionaryData = new DictionaryData();
for (int i = 1; i <= resultSetMetaData.getColumnCount();
i++) {
str = bneResultSet.getString(i);
if (str == null) {
dictionaryData.put(resultSetMetaData.getColumnName(i),
"");
} else {
dictionaryData.put(resultSetMetaData.getColumnName(i),
str);
}
}
vector.addElement(dictionaryData);
}
}
} catch (SQLException sqlException) {
throw new BneSQLException(BneResourceString.getMlsString(-1L, -1L,
"Error in HolxDestSubComponent: Can't get Source Sub-inventory."),
sqlException);
} catch (BneMissingParameterException bneMissingParameterException) {
paramBneLOVControlBean.addError(bneMissingParameterException.getMessage());
}
DictionaryData[] arrayOfDictionaryData =
new DictionaryData[vector.size()];
for (int i = 0; i < vector.size(); i++) {
arrayOfDictionaryData[i] = ((DictionaryData)vector.elementAt(i));
}
return arrayOfDictionaryData;
}
public String getComponentName() {
return "DestSub";
}
public String getComponentVersion() {
return "R12";
}
}
Keep these three files on server and run the below script.
begin
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV
(P_APPLICATION_ID => 20003,
P_INTERFACE_CODE => 'HOLX_PART_XINTG_INTF1',
P_INTERFACE_COL_NAME => 'P_DESTINATION_SUBINVENTORY',
P_JAVA_CLASS => 'holx.oracle.apps.icx.HolxDestSubComponent',
P_WINDOW_CAPTION => 'Destination Subinventory',
P_WINDOW_WIDTH => 500,
P_WINDOW_HEIGHT => 500,
P_TABLE_BLOCK_SIZE => 50,
P_TABLE_COLUMNS => 'SECONDARY_INVENTORY_NAME,DESCRIPTION',
P_TABLE_SELECT_COLUMNS => 'P_DESTINATION_SUBINVENTORY',
P_TABLE_COLUMN_ALIAS => 'P_DESTINATION_SUBINVENTORY,SECONDARY_INVENTORY_NAME',
P_TABLE_HEADERS => 'Sub Inventory,Description',
P_TABLE_SORT_ORDER => 'Yes,Yes',
P_USER_ID => 161909);
commit;
end;
/
