Oracle Web ADI Dependent LOV Process Steps

STEP1: Package Creation

CREATE OR REPLACE PACKAGE XXAA_TEST_WEBADI_LOV AUTHID CURRENT_USER

IS

/* ******************************************************************************

      NAME:     XXAA_TEST_WEBADI_LOV

      PURPOSE:

      REVISIONS:

      Ver        Date        Author            Description

      ---------  ----------  ---------------   ------------------------------------

      1.0                    Raju Chinthapatla Oracle Web ADI Dependent LOV Creation Steps             

***************************************************************************** */

procedure XXAA_LOV_DATA(p_supplier_name varchar2, p_supplier_site varchar2);

END;

/

CREATE OR REPLACE PACKAGE BODY XXAA_TEST_WEBADI_LOV

IS

  l_org_id number :=apps. FND_PROFILE.VALUE('ORG_ID');

  l_user_id number := apps.fnd_profile.value('USER_ID');

  lv_supplier_id number;

  lv_supplier_number varchar2(1000);

Procedure XXAA_LOV_DATA(p_supplier_name varchar2,p_supplier_site varchar2)

as

begin

  apps.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,' Testing LOV:');

  lv_supplier_id := 0;

IF P_SUPPLIER_NAME IS NULL

THEN

  apps.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,' Testing LOV P_Supplier_name:');

ELSE

    BEGIN

         SELECT VENDOR_ID,

                SEGMENT1

           INTO lv_supplier_id,

                lv_supplier_number

           FROM APPS.AP_SUPPLIERS

          WHERE vendor_name = P_SUPPLIER_NAME

            AND enabled_flag = 'Y';

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

        apps.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,' Testing LOV P_Supplier_name1:' ||SQLERRM);

        WHEN OTHERS THEN

        apps.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,' Testing LOV P_Supplier_name2:' ||SQLERRM);

    END;

END IF;

exception

when no_data_found

then

  apps.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,' Exception: NO_DATA_FOUND:');

when others

then

  apps.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG,' Exception: OTHERS:');

END ; -- End of XXAA_LOV_DATA

END XXAA_TEST_WEBADI_LOV;

/

STEP2: TABLE LOV Creation for the field “Supplier”

BEGIN

BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV

(P_APPLICATION_ID       => 50202,

 P_INTERFACE_CODE       => ' GENERAL_5_INTF',-->Created Interface Code -->BNE_INTERFACE_COLS_B.INTERFACE_CODE  

 P_INTERFACE_COL_NAME   => 'P_SUPPLIER_NAME', -->Interface Column Name (BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME)

 P_ID_COL               => 'SEGMENT1', -->Column Name from the table "AP_SUPPLIERS"

 P_MEAN_COL             => 'VENDOR_NAME',           

 P_DESC_COL             => NULL,

 P_TABLE                => 'AP_SUPPLIERS',

 P_ADDL_W_C             => null,

 P_WINDOW_CAPTION       => 'Suppliers', -->Window name of the Suppliers LOV @Template

 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;

 /

Create a folder structure in $JAVA_TOP/xxaa/oracle/apps/bne/lov/webui (/devapp/oracle/EBSDEV/fs1/EBSapps/comn/java/classes/xxaa/oracle/apps/bne/lov/webui)

Upload and Compile below JAVA files @  $JAVA_TOP/xxaa/oracle/apps/bne/lov/webui/

STEP3: JAVA Class File Creation

i.                 SQL Class Creation – XXAASiteNameSQL.java

package XXAA.oracle.apps.bne.lov.webui;

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 XXAASiteNameSQL extends BneBaseSQL

{

    public XXAASiteNameSQL(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);

        }

        setQuery(connection, stringBuffer.toString());

    }

}

Save the file with XXAASiteNameSQL.java

ii.  Validator Class Creation – XXAASiteNameValidator.java

package xxaa.oracle.apps.bne.lov.webui;

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 xxaa.oracle.apps.bne.lov.webui.XXAASiteNameSQL;

public class XXAASiteNameValidator extends BneUploadValidator {

   

    public String[] getDomainParameters()

    {

        return new String[] { "P_SUPPLIER_NAME" };

    }

    public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext, Hashtable paramHashtable,

                                        BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws BneException

    {

        XXAASiteNameSQL XXAASiteNameSQL = null;

        BneResultSet bneResultSet = null;

        BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

        if (paramBneCompositeSQLCriteria != null)

         {

            bneSQLStatement1 = paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);

        }

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

        if (str1 == null)

         {

            throw new BneMissingParameterException("Supplier Field Error");

        }

        try

         {

            XXAASiteNameSQL = new XXAASiteNameSQL(paramBneWebAppsContext,bneSQLStatement1.getStatement());

            BneSQLStatement bneSQLStatement2 = new BneSQLStatement(XXAASiteNameSQL.getQuery(), new Object[] { str1 });

            bneSQLStatement2.append("", bneSQLStatement1.getBindValues());

            bneResultSet = XXAASiteNameSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());

        }

         catch (Exception exception)

         {

            throw new BneFatalException(exception.toString());

        }

        return bneResultSet;

    }

}

Save the file with XXAASiteNameValidator.java

iii.            Component Class Creation – XXAASiteNameComponent.java

package xxaa.oracle.apps.bne.lov.webui;

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 xxaa.oracle.apps.bne.lov.webui.XXAASiteNameValidator;

public class XXAASiteNameComponent extends BneAbstractListOfValues

{

    private XXAASiteNameValidator 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 XXAASiteNameValidator();

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

    }

}

Save the file with XXAASiteNameComponent.java

STEP4: JAVA_LOV Creation

BEGIN

BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV

(P_APPLICATION_ID      => 50202,                -->Custom Application ID

P_INTERFACE_CODE       => 'GENERAL_5_INTF', -->BNE_INTERFACE_COLS_B.INTERFACE_CODE

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

P_JAVA_CLASS           => xxaa.oracle.apps.bne.lov.webui.XXAASiteNameComponent',

                            -->from $JAVA_TOP/custom path of the Component class file,

P_WINDOW_CAPTION       => 'Supplier Sites',     -->Window name for the suppliers Site

P_WINDOW_WIDTH         => 500,

P_WINDOW_HEIGHT        => 500,

P_TABLE_BLOCK_SIZE     => 50,

P_TABLE_COLUMNS        => 'VENDOR_SITE_CODE,ADDRESS', 

                            -->These 2 fields will displays @ pop-up window of the Supplier sites

P_TABLE_SELECT_COLUMNS => 'P_SUPPLIER_SITE',           

P_TABLE_COLUMN_ALIAS   => 'P_SUPPLIER_SITE,ADDRESS',

P_TABLE_HEADERS        => 'Vendor Site Code, Address',

P_TABLE_SORT_ORDER     => 'yes, yes',

P_USER_ID              => -1); 

COMMIT;

END;

/

STEP5: Jar file Creation and Server bounce

è   Take the backup of customall.jar file

è   Create JAR file by running the adcgnjar @ $JAVA_TOP

Ex:- […. Classes ] $ adcgnjar   (It will prompt for user/pwd: provide the APPS user name pwd to generate the “customall.jar”)

è   Restart the OA Core services

è   After creating the customall.jar fie download to local system and verify our three .class files are added or not.(Three .class files should be add into this customall.jar file)