"Compile error: User-defined type not defined - Dim oParser As New SAXXMLReader60" in Oracle Web ADI

Issue:

Getting the below error while downloading the Excel in Web ADI.

System Configuration:

Windows 8.1 or higher and  MS Excel 2010 or higher.

Root Cause:

This error occurs due to excel reference "Microsoft XML, v6.0" is not enabled. With the combination of Windows 8.1 or higher and MS Excel 2010 or higher, this reference doesn't enable automatically. So through the program, we need to enable it.

Solution:           

1. Click on below link and download the file "WebADI-Addins-MSXML6.zip" and extract it in your local system at the path like (eg.<C:\Users\<User Name>\AppData\Roaming\Microsoft\AddIns>).

    https://drive.google.com/open?id=19xMwOEVc1rIh1nU5lsN7d1UBg_X7G6lP

2. Open the MS Excel and navigate to "File > Options > Add-ins"
3. At the bottom under Manage, select Excel Add-ins and click the Go.
4. Click on the Browse.. button and find the location where "WebADI-Addins-MSXML6.xla" was saved and select it.
5. Select the checkbox to enable the Add-in and click on Ok.
6. Close Excel and test the issue.

Contract Query: Drill down From Contract to AR Invoice

SELECT okh.contract_number,
   okh.start_date contract_start_date,
   okh.end_date contract_end_date,
   oksl.line_number Sub_line_number,
   okbc.bill_action,
   okbt.trx_number invoice_number,
   okbs.date_billed_from,
   okbs.date_billed_to,
  (SELECT a.segment1
   FROM mtl_system_items_b a,
     csi_item_instances b,
okc_k_items c
   WHERE a.inventory_item_id = b.inventory_item_id
     and c.dnz_chr_id = okh.id
     and c.jtot_object1_code = 'OKX_CUSTPROD'
     and b.instance_id = c.object1_id1
     and a.organization_id = b.inv_master_organization_id
     and c.cle_id = oksl.id) product_item,
  (SELECT b.serial_number
   FROM mtl_system_items_b a,
     csi_item_instances b,
okc_k_items c
   WHERE a.inventory_item_id = b.inventory_item_id
     and c.dnz_chr_id = okh.id
     and c.jtot_object1_code = 'OKX_CUSTPROD'
     and b.instance_id = c.object1_id1
     and a.organization_id = b.inv_master_organization_id
     and c.cle_id = oksl.id) serial_number
FROM okc_k_headers_all_b okh,
  okc_k_lines_b okl,
  okc_k_lines_b oksl,
  oks_bill_sub_lines okbs,
  oks_bill_txn_lines okbt,
  oks_bill_cont_lines okbc
WHERE okh.id = okl.chr_id
  AND okl.id = oksl.cle_id
  AND oksl.id = okbs.cle_id
  AND okbs.bcl_id = okbt.bcl_id
  AND okbt.bcl_id = okbc.id
--AND okbt.trx_number = '91107083'
  AND okh.contract_number = 'SMFR00069'
ORDER BY okbt.trx_number;

Script to update Contract Header, Contract Lines and Cascading Attributes

DECLARE
  l_api_version        CONSTANT NUMBER := 1.0;
  l_init_msg_list       CONSTANT    VARCHAR2(1) := 'T'; 
  l_return_status      VARCHAR2(1);
  l_msg_count          NUMBER;
  l_msg_data           VARCHAR2(2000);
  l_chrv_tbl_in        okc_contract_pub.chrv_tbl_type;
  l_chrv_tbl_out       okc_contract_pub.chrv_tbl_type;
  l_clev_tbl_in        okc_contract_pub.clev_tbl_type;
  l_clev_tbl_out       okc_contract_pub.clev_tbl_type;
  l_cle_tbl_in         okc_contract_pub.clev_tbl_type;
  l_cle_tbl_out        okc_contract_pub.clev_tbl_type;
  i                    NUMBER  := 1;
  
  l_count number :=0;
  l_id okc_k_headers_all_b.id%TYPE;
  l_end_date okc_k_headers_all_b.end_date%TYPE;
  
  CURSOR header_cur IS
  SELECT id,
    contract_number,
    start_date,
    end_date
  FROM okc_k_headers_all_b
  WHERE contract_number = 'CJ-10123';
 -- where authoring_org_id = <ORG_ID>;
  
  CURSOR line_cur (l_header_id NUMBER)IS
  SELECT id,
    line_number
  FROM okc_k_lines_b
  WHERE chr_id = l_header_id;
  
  CURSOR subline_cur (l_header_id NUMBER)IS
  SELECT id
  FROM okc_k_lines_b
  WHERE dnz_chr_id = l_header_id;
  
BEGIN  
FOR header_rec IN header_cur LOOP
l_chrv_tbl_in(1).ID := header_rec.id;
l_chrv_tbl_in(1).start_date := header_rec.start_date;
l_chrv_tbl_in(1).end_date    := LAST_DAY(header_rec.start_date);

 okc_contract_pub.update_contract_header 
              (
                p_api_version       => l_api_version,
                p_init_msg_list     => FND_API.G_TRUE,
                x_return_status     => l_return_status,
                x_msg_count         => l_msg_count,
                x_msg_data          => l_msg_data,
                p_restricted_update => 'N',
                p_chrv_tbl          => l_chrv_tbl_in,
                x_chrv_tbl          => l_chrv_tbl_out
               );
  IF (l_return_status <>  'S') THEN
     dbms_output.put_line ('Error while updating the Contract Header for Contract '||header_rec.contract_number);
     dbms_output.put_line ('Error: '||l_msg_data);
  ELSE
    --init_contract_line (l_clev_tbl_in);
  --init_contract_oks_line (l_klnv_tbl_in);
    FOR line_rec IN line_cur(header_rec.id) LOOP
      l_clev_tbl_in(i).id                := line_rec.id;
      l_clev_tbl_in(i).start_date        := header_rec.start_date;
      l_clev_tbl_in(i).end_date            := LAST_DAY(header_rec.start_date);
      i := i+1;
    END LOOP;
    
    IF (i > 1) THEN
      okc_contract_pub.update_contract_line 
            (p_api_version          => l_api_version,
             p_init_msg_list        => l_init_msg_list,
             x_return_status        => l_return_status,
             x_msg_count            => l_msg_count,
             x_msg_data             => l_msg_data,
             p_restricted_update    => 'F',
             p_clev_tbl             => l_clev_tbl_in,
             x_clev_tbl             => l_clev_tbl_out);
      IF l_return_status <> 'S' THEN
         dbms_output.put_line ('Error while updating the Contract Line for Contract '||header_rec.contract_number);
         dbms_output.put_line ('Error: '||l_msg_data);
      ELSE
         FOR line_rec IN line_cur(header_rec.id) LOOP
           oks_coverages_pvt.update_coverage_effectivity(
                  p_api_version     => l_api_version,
                  p_init_msg_list   => l_init_msg_list,
                  x_return_status   => l_return_status,
                  x_msg_count        => l_msg_count,
                  x_msg_data        => l_msg_data,
                  p_service_Line_Id => line_rec.id,
                  p_New_Start_Date  => header_rec.start_date,
                  p_New_End_Date    => LAST_DAY(header_rec.start_date));
                  
          IF l_return_status <> 'S' THEN
            dbms_output.put_line ('Error while updating the Contract Coverage for Contract '||header_rec.contract_number);
            dbms_output.put_line ('Error: '||l_msg_data);
          ELSE
            oks_pm_programs_pvt.adjust_pm_program_schedule
                 (p_api_version          => l_api_version,
                  p_init_msg_list        => l_init_msg_list,
                  p_contract_line_id     => line_rec.id,
                  p_new_start_date       => header_rec.start_date,
                  p_new_end_date         => LAST_DAY(header_rec.start_date),
                  x_return_status        => l_return_status,
                  x_msg_count            => l_msg_count,
                  x_msg_data             => l_msg_data
                  );
            IF l_return_status <> 'S' THEN
              dbms_output.put_line ('Error while updating the PM Schedule for Contract '||header_rec.contract_number);
              dbms_output.put_line ('Error: '||l_msg_data);
            ELSE
              i :=1;
              --init_contract_line (l_cle_tbl_in);
            --init_contract_oks_line (l_kln_tbl_in);
              FOR subline_rec IN subline_cur(header_rec.id) LOOP
                l_cle_tbl_in(i).id                    := subline_rec.id;
                l_cle_tbl_in(i).start_date            := header_rec.start_date;
                l_cle_tbl_in(i).end_date                := LAST_DAY(header_rec.start_date);
              --l_cle_tbl_in(i).object_version_number := l_object_version_number; 
                i := i+1;
              END LOOP;
                okc_contract_pub.update_contract_line 
                      (p_api_version          => l_api_version,
                       p_init_msg_list        => l_init_msg_list,
                       x_return_status        => l_return_status,
                       x_msg_count            => l_msg_count,
                       x_msg_data             => l_msg_data,
                       p_restricted_update    => 'F',
                       p_clev_tbl             => l_cle_tbl_in,
                       x_clev_tbl             => l_cle_tbl_out);
              IF l_return_status <> 'S' THEN
                dbms_output.put_line ('Error while updating the Sub Line for Contract '||header_rec.contract_number);
                dbms_output.put_line ('Error: '||l_msg_data);
              ELSE
                dbms_output.put_line ('Sript Updated the END Date successfully for Contract '||header_rec.contract_number);
               COMMIT;
              END IF;
            END IF;
          END IF;
         END LOOP;
      END IF;
    END IF;
  END IF;
END LOOP;
END;
/

Script to send emails

This summary is not available. Please click here to view the post.

Steps to get all the parameter of a Business Event

1. Create a temp table MY_LOG_TABLE using below script, this will store parameters information.

2. Compile the below package TEST_BUSINESSEVENT.

3. Create a subscription of that business event using function TEST_BUSINESSEVENT.UPDATE_ASGN.

4. Perform the steps to fire the business event.

5. Check the table MY_LOG_TABLE for the parameters detail.

Table Script: 

create table my_log_table (PARAMETERS VARCHAR2(100));

Package:

create or replace package TEST_BUSINESSEVENT
AS
procedure log_me (t varchar2);
function update_asgn (p_subscription_guid in raw,
                      p_event in out WF_EVENT_T) return varchar2;
END;
/
create or replace package body TEST_BUSINESSEVENT
AS
procedure log_me (t varchar2) IS pragma autonomous_transaction;
begin
  insert into my_log_table values(t);
  commit;

end;

function update_asgn (p_subscription_guid in raw,
                      p_event in out WF_EVENT_T) return varchar2 is
                     
    l_wf_parameter_list_t wf_parameter_list_t;
    i number := 1;
    c number;
    l_key varchar2(30);
    l_val varchar2(2000);
begin
  l_wf_parameter_list_t := p_event.getParameterList();
  c := l_wf_parameter_list_t.count();
 
  log_me('event = ' || p_event.getEventName());
  log_me('count : ' || c);
 
  while (i<=c)
  loop
   
    l_key := l_wf_parameter_list_t(i).getName();
    l_val := l_wf_parameter_list_t(i).getValue();
   
    log_me(l_key || '=' || l_val);
   
    i := i + 1;
  end loop;
 
  return 'SUCCESS';
  exception
    when others then
      log_me('errm' || sqlerrm);
end;
end;

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

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