2. Interface Fields
a. vendor name ()
b. vendor site code ()
3. based on the value selected in vendor name, vendor site code field would be populated.
4. create a lov on vendor name using below code:
--vendor name
BEGIN
BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV
(
P_APPLICATION_ID => 200
, P_INTERFACE_CODE => 'XXX_AP_XINTG_INTF1'
, P_INTERFACE_COL_NAME => 'P_VENDOR_NAME'
, P_ID_COL => 'VENDOR_NAME'
, P_MEAN_COL => 'VENDOR_NAME'
, P_DESC_COL => 'VENDOR_NAME'
, P_TABLE => 'AP_SUPPLIERS'
, P_ADDL_W_C => NULL
, P_WINDOW_CAPTION => 'AP Suppliers'
, P_WINDOW_WIDTH => 400
, P_WINDOW_HEIGHT => 500
, P_TABLE_BLOCK_SIZE => 10
, P_TABLE_SORT_ORDER => 'VENDOR_NAME'
, P_USER_ID => -1
, P_POPLIST_FLAG => 'N'
, P_TABLE_COLUMNS => 'VENDOR_NAME,VENDOR_NAME'
);
END;
Now create three java class files as given below:
1. abhiSupplierSiteNameSQL
//this contains the sql which would be executed with parameter paramString as vendor name
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;
import oracle.apps.bne.utilities.sql.*;
public class abhiSupplierSiteNameSQL extends BneBaseSQL
{
public abhiSupplierSiteNameSQL(BneWebAppsContext paramBneWebAppsContext,
String paramString) throws SQLException,
BneException {
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT ss.vendor_site_code, ss.address_line1 || ',' || ss.city || ',' || ss.state address FROM ap_suppliers s,ap_supplier_sites_all ss WHERE ss.vendor_id = s.vendor_id AND s.vendor_name = :1");
if ((paramString != null) && (!paramString.trim().equals(""))) {
stringBuffer.append("AND " + paramString); //parameter passed is query criteria
}
setQuery(connection, stringBuffer.toString());
}
}
2. abhiSupplierSiteNameValidator
//this executes the sql with query where clause
package abhi.oracle.apps.lovtest.validator;
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 abhi.oracle.apps.lovtest.sql.abhiSupplierSiteNameSQL;
public class abhiSupplierSiteNameValidator extends BneUploadValidator {
public String[] getDomainParameters() {
return new String[] { "P_VENDOR_NAME" }; //query criteria
}
public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable,
BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {
abhiSupplierSiteNameSQL abhiSupplierSiteNameSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();
if (paramBneCompositeSQLCriteria != null) {
bneSQLStatement1 =
paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
}
String str1 = (String)paramHashtable.get("P_VENDOR_NAME");
if (str1 == null) {
throw new BneMissingParameterException("Supplier Field Error");
}
try {
abhiSupplierSiteNameSQL =
new abhiSupplierSiteNameSQL(paramBneWebAppsContext,
bneSQLStatement1.getStatement());
BneSQLStatement bneSQLStatement2 =
new BneSQLStatement(abhiSupplierSiteNameSQL.getQuery(),
new Object[] { str1 });
bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet =
abhiSupplierSiteNameSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
} catch (Exception exception) {
throw new BneFatalException(exception.toString());
}
return bneResultSet;
}
}
3. abhiSupplierSiteNameComponent
//file class file, which builds the LOV
package abhi.oracle.apps.lovtest.component;
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 abhi.oracle.apps.lovtest.validator.abhiSupplierSiteNameValidator;
import oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;
public class abhiSupplierSiteNameComponent extends BneAbstractListOfValues {
private abhiSupplierSiteNameValidator 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 abhiSupplierSiteNameValidator();
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("VENDOR_SITE_CODE"))
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "VENDOR_SITE_CODE", 0,
9, FILTERVALUE, 2);
else
bneSimpleSQLCriteria =
new BneSimpleSQLCriteria(0, "ADDRESS_LINE1 || ',' || CITY || ',' || STATE",
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 =
"Oracle Applications Sup Sup Site Test." + str1 + " field.";
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,
"Cannot get Supplier Site Name information"),
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 "SupplierSiteName";
}
public String getComponentVersion() {
return "R12";
}
}
6. Once above class files are compiled in Jdev or server, place the class files in
$JAVA_TOP/abhi/oracle/apps/lovtest/<respective dir>
7. Execute the below code
--Create Dynamic LOV (JAVA LOV) for Supplier Site using following API,
BEGIN
BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV
(
P_APPLICATION_ID => 200,
P_INTERFACE_CODE =>
'XXX_AP_XINTG_INTF1', --BNE_INTERFACE_COLS_B.INTERFACE_CODE
P_INTERFACE_COL_NAME =>
'P_VENDOR_SITE_CODE', --BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME
P_JAVA_CLASS => 'abhi.oracle.apps.lovtest.component.icarSupplierSiteNameComponent',
P_WINDOW_CAPTION => 'Supplier Sites', P_WINDOW_WIDTH => 500, P_WINDOW_HEIGHT => 500, P_TABLE_BLOCK_SIZE => 50,
P_TABLE_COLUMNS => 'VENDOR_SITE_CODE',
P_TABLE_SELECT_COLUMNS => 'P_VENDOR_SITE_CODE',
P_TABLE_COLUMN_ALIAS => 'P_VENDOR_SITE_CODE',
P_TABLE_HEADERS => 'Vendor Site Code',
P_TABLE_SORT_ORDER => 'yes', P_USER_ID => -1
);
COMMIT;
END;
8. Bounce the apache
