Thursday, February 16, 2017

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) 


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect