Dependent LOV in WEB ADI (When LOV is dependent on only one field)

Assume we have two fields one Resource Name and another Destination Organization. Now we are going to create LOV on Destination Organization which is dependent on Resource Name.

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 HolxDestOrgSQL extends BneBaseSQL {
    public HolxDestOrgSQL(BneWebAppsContext paramBneWebAppsContext,
                                    String paramString) throws SQLException,
                                                               BneException {
        Connection connection = paramBneWebAppsContext.getJDBCConnection();
        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("SELECT ORGANIZATION_CODE, ORGANIZATION_NAME FROM (SELECT DISTINCT OOD.ORGANIZATION_CODE, OOD.ORGANIZATION_NAME FROM CSP_RS_SUBINVENTORIES_V CRS, ORG_ORGANIZATION_DEFINITIONS OOD WHERE OOD.ORGANIZATION_ID = CRS.ORGANIZATION_ID AND CRS.CONDITION_TYPE_MEANING = 'Usable' AND SYSDATE BETWEEN EFFECTIVE_DATE_START AND NVL(CRS.EFFECTIVE_DATE_END,SYSDATE) AND UPPER(CRS.RESOURCE_NAME) = UPPER(NVL(:1,CRS.RESOURCE_NAME))) WHERE 1=1  ");
       
        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.HolxDestOrgSQL;

public class HolxDestOrgValidator extends BneUploadValidator {

    public String[] getDomainParameters() {
        return new String[] { "P_RESOURCE_NAME" };
    }

    public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
                                        Hashtable paramHashtable,
                                        BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException {

        HolxDestOrgSQL HolxDestOrgSQL = null;
        BneResultSet bneResultSet = null;
        BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

        if (paramBneCompositeSQLCriteria != null) {
            bneSQLStatement1 =
                    paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
        }

        String str1 = (String)paramHashtable.get("P_RESOURCE_NAME");

        if (str1 == null) {
            throw new BneMissingParameterException("Error while fetching Source Name in HolxDestOrgValidator");
        }
        try {
            HolxDestOrgSQL =
                    new HolxDestOrgSQL(paramBneWebAppsContext,
                                                 bneSQLStatement1.getStatement());
            BneSQLStatement bneSQLStatement2 =
                new BneSQLStatement(HolxDestOrgSQL.getQuery(),
                                    new Object[] { str1 });

            bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
            bneResultSet =
                    HolxDestOrgSQL.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.HolxDestOrgValidator;

public class HolxDestOrgComponent extends BneAbstractListOfValues {

    private HolxDestOrgValidator 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 HolxDestOrgValidator();
            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("ORGANIZATION_CODE"))
                    bneSimpleSQLCriteria =
                            new BneSimpleSQLCriteria(0, "ORGANIZATION_CODE", 0,
                                                     9, FILTERVALUE, 2);
                else
                    bneSimpleSQLCriteria =
                            new BneSimpleSQLCriteria(0, "ORGANIZATION_NAME",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 HolxDestOrgComponent: Can't get Destination Origination Code."),
                                      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 "DesOrg";
    }

    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_ORGANIZATION',
P_JAVA_CLASS           => 'holx.oracle.apps.icx.HolxDestOrgComponent',
P_WINDOW_CAPTION       => 'Destination Organization',
P_WINDOW_WIDTH         => 500,
P_WINDOW_HEIGHT        => 500,
P_TABLE_BLOCK_SIZE     => 50,
P_TABLE_COLUMNS        => 'ORGANIZATION_CODE,ORGANIZATION_NAME', 
P_TABLE_SELECT_COLUMNS => 'P_DESTINATION_ORGANIZATION',
P_TABLE_COLUMN_ALIAS   => 'P_DESTINATION_ORGANIZATION,ORGANIZATION_CODE',
P_TABLE_HEADERS        => 'Organization Code, Organization Name',
P_TABLE_SORT_ORDER     => 'Yes, Yes',
P_USER_ID              => 161909); 
commit;
end;
/