Dependent LOV(List of Values) in Oracle Web ADI with steps and source code

Hello,

Achieving Dependent LOV in Oracle Web ADI is quite tedious task. But don't worry, I am sharing the steps to create one.

You must know basics of Oracle Web-ADI before trying below steps.

Dependent LOV in Oracle Web ADI is created by writing 3 Java Classes as,
1)SQL Class
2)Validator Class
3)Component Class
The Java Source Code of these classes is present below,


Scenario-
Consider we have two columns in Web-ADI,
1)Supplier
2)Supplier Site

We want to show only Supplier Sites of selected Supplier, i.e., we want to filter Supplier Sites as per the Supplier selection.

We will create Supplier as static LOV.We can also create Supplier as static pop-list.

The steps are as below,
(1)Create Web-ADI containing Supplier and Supplier Site as plain enterable columns, i.e., on which LOV is not set yet.

(2)Create Static LOV (Table LOV) for Supplier using following API,
begin
BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV
 (P_APPLICATION_ID => 200,
 P_INTERFACE_CODE => 'GENERAL_1_INTF', --BNE_INTERFACE_COLS_B.INTERFACE_CODE
 P_INTERFACE_COL_NAME => 'P_SUP', --BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME
 P_ID_COL => 'SEGMENT1',
 P_MEAN_COL => 'VENDOR_NAME',
 P_DESC_COL =>NULL,
 P_TABLE => 'AP_SUPPLIERS',
 P_ADDL_W_C => null,
 P_WINDOW_CAPTION => 'Suppliers',
 P_WINDOW_WIDTH => 400,
 P_WINDOW_HEIGHT => 300,
 P_TABLE_BLOCK_SIZE => 10,
 P_TABLE_SORT_ORDER => 'ascending',
 P_USER_ID => -1
 ,P_TABLE_COLUMNS =>NULL,
 P_TABLE_SELECT_COLUMNS =>NULL,
 P_TABLE_COLUMN_ALIAS   =>NULL,
 P_TABLE_HEADERS        =>NULL,
 P_POPLIST_FLAG         =>'N'
 );
 commit;
 end;

(3)Copy below 3 java files to JAVA_TOP and compile in given order only

a)SagarSupplierSiteNameSQL.java-below is source code


package sagar.oracle.apps.lovtest.sql;

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 SagarSupplierSiteNameSQL extends BneBaseSQL {
    public SagarSupplierSiteNameSQL(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);
        }

        //stringBuffer.append(" ORDER BY VENDOR_SITE_CODE, ADDRESS ");

        setQuery(connection, stringBuffer.toString());
    }
}

b)SagarSupplierSiteNameValidator.java-below is source code


package sagar.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 sagar.oracle.apps.lovtest.sql.SagarSupplierSiteNameSQL;

public class SagarSupplierSiteNameValidator extends BneUploadValidator {

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

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

        SagarSupplierSiteNameSQL sagarSupplierSiteNameSQL = null;
        BneResultSet bneResultSet = null;
        BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

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

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

        if (str1 == null) {
            throw new BneMissingParameterException("Supplier Field Error");
        }


        try {
            sagarSupplierSiteNameSQL = 
                    new SagarSupplierSiteNameSQL(paramBneWebAppsContext, 
                                                 bneSQLStatement1.getStatement());
            BneSQLStatement bneSQLStatement2 = 
                new BneSQLStatement(sagarSupplierSiteNameSQL.getQuery(), 
                                    new Object[] { str1 });

            bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
            bneResultSet = 
                    sagarSupplierSiteNameSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
        } catch (Exception exception) {
            throw new BneFatalException(exception.toString());
        }


        return bneResultSet;
    }


}

(c)SagarSupplierSiteNameComponent.java-below is source code


package sagar.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 sagar.oracle.apps.lovtest.validator.SagarSupplierSiteNameValidator;

import oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;

public class SagarSupplierSiteNameComponent extends BneAbstractListOfValues {

    private SagarSupplierSiteNameValidator 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 SagarSupplierSiteNameValidator();
            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";
    }
}

(4)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       => 'GENERAL_1_INTF', --BNE_INTERFACE_COLS_B.INTERFACE_CODE

P_INTERFACE_COL_NAME   => 'P_SUP_SITE', --BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME                             

P_JAVA_CLASS           => 'sagar.oracle.apps.lovtest.component.SagarSupplierSiteNameComponent',

P_WINDOW_CAPTION       => 'Supplier Sites',

P_WINDOW_WIDTH         => 500, 

P_WINDOW_HEIGHT        => 500, 

P_TABLE_BLOCK_SIZE     => 50, 

P_TABLE_COLUMNS        => 'VENDOR_SITE_CODE,ADDRESS',  

P_TABLE_SELECT_COLUMNS => 'P_SUP_SITE',

P_TABLE_COLUMN_ALIAS   => 'P_SUP_SITE,ADDRESS',

P_TABLE_HEADERS        => 'Vendor Site Code, Address',

P_TABLE_SORT_ORDER     => 'yes, yes',

P_USER_ID              => -1);  

commit;

end;

(5)Bounce the server and test whether Dependent LOV works?