Dependent LOV in WEB ADI (When LOV is dependent on two fields)

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;
/