LOV in OAF

Notice the way I have added where clause in the voimpl, since there is bug in OAF with bind variables being used in VO query.

Code in Controller
******************************************************
    public void processRequest(OAPageContext pageContext, OAWebBean webBean) {
        super.processRequest(pageContext, webBean);

        OAApplicationModule am =
            (OAApplicationModule)pageContext.getApplicationModule(webBean);

        String orgId = null;
        System.out.println("initializeLOVVO****" + pageContext.getOrgId());
        if (pageContext.getOrgId() != 0) {
            System.out.println("initializeLOVVO****" + pageContext.getOrgId());
            orgId = pageContext.getOrgId() + "";
        }

        System.out.println(" processFormRequest> SearchEFT Starts *****");
        voidEFTLovVOImpl vo =
            (voidEFTLovVOImpl)am.findViewObject("voidEFTLovVO1");
        Serializable[] parameters = { orgId };
        am.invokeMethod("initializeLOVVO", parameters);
        System.out.println("initializeLOVVO****" + vo);
    }


******************************************************
Call InitQuery in AM

//init orgcontext in VoidLOVVO

 public void initializeLOVVO(String orgId)
 {
 voidEFTLovVOImpl lovvo = getvoidEFTLovVO1();
 lovvo.initQuery(orgId);
 }

*************************************************
add init query in vimpl

 public void initQuery(String orgIdS)
 {

 OAApplicationModuleImpl localOAApplicationModuleImpl = (OAApplicationModuleImpl)getApplicationModule();
 OADBTransactionImpl localOADBTransactionImpl = (OADBTransactionImpl)localOAApplicationModuleImpl.getOADBTransaction();
 System.out.println("voidEFTLovVOImpl> initQuery called"+localOADBTransactionImpl.getOrgId()+"");
 System.out.println("voidEFTLovVOImpl> initQuery called");      

     if ((localOADBTransactionImpl.getOrgId()+"" != null) &&
         (!("".equals(localOADBTransactionImpl.getOrgId()+""))))
     {
       System.out.println("voidEFTLovVOImpl initQuery STARTED");
       // Do the following conversion for type consistency.
       Number orgId = null;
   
       try
       {
         orgId = new Number(localOADBTransactionImpl.getOrgId()+"");
         System.out.println("voidEFTLovVOImpl IN try");
       }
       catch(Exception e)
       {
         System.out.println("voidEFTLovVOImpl IN CATCH"+e);
       }

       setWhereClause(null);
       setWhereClauseParams(null);
       setWhereClause("ORG_ID ='"+orgId+"'");
       executeQuery();
 }

 }

Dependent LOV in WEBADI using Java validator

1. Create a webadi for invoice/supplier load

2. Interface Fields
    a. vendor name ()
    b. vendor site code ()

3. based on the value selected in vendor name, vendor site code field would be populated.

4. create a lov on vendor name using below code:

--vendor name
BEGIN
  BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV
(
  P_APPLICATION_ID => 200
, P_INTERFACE_CODE => 'XXX_AP_XINTG_INTF1'
, P_INTERFACE_COL_NAME => 'P_VENDOR_NAME'
, P_ID_COL => 'VENDOR_NAME'
, P_MEAN_COL => 'VENDOR_NAME'
, P_DESC_COL => 'VENDOR_NAME'
, P_TABLE => 'AP_SUPPLIERS'
, P_ADDL_W_C => NULL
, P_WINDOW_CAPTION => 'AP Suppliers'
, P_WINDOW_WIDTH => 400
, P_WINDOW_HEIGHT => 500
, P_TABLE_BLOCK_SIZE => 10
, P_TABLE_SORT_ORDER => 'VENDOR_NAME'
, P_USER_ID => -1
, P_POPLIST_FLAG => 'N'
, P_TABLE_COLUMNS => 'VENDOR_NAME,VENDOR_NAME'
  );
END;


Now create three java class files as given below:

1. abhiSupplierSiteNameSQL
//this contains the sql which would be executed with parameter paramString as vendor name

package abhi.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;
import oracle.apps.bne.utilities.sql.*;


public class abhiSupplierSiteNameSQL extends BneBaseSQL
{
    public abhiSupplierSiteNameSQL(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);  //parameter passed is query criteria
        }
        setQuery(connection, stringBuffer.toString());
    }
}



2. abhiSupplierSiteNameValidator
//this executes the sql with query where clause

package abhi.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 abhi.oracle.apps.lovtest.sql.abhiSupplierSiteNameSQL;

public class abhiSupplierSiteNameValidator extends BneUploadValidator {

    public String[] getDomainParameters() {
        return new String[] { "P_VENDOR_NAME" }; //query criteria
    }

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

        abhiSupplierSiteNameSQL abhiSupplierSiteNameSQL = null;
        BneResultSet bneResultSet = null;
        BneSQLStatement bneSQLStatement1 = new BneSQLStatement();

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

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

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


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

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


        return bneResultSet;
    }

}


3. abhiSupplierSiteNameComponent
//file class file, which builds the LOV

package abhi.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 abhi.oracle.apps.lovtest.validator.abhiSupplierSiteNameValidator;
import oracle.cabo.servlet.Page;
import oracle.cabo.servlet.event.PageEvent;
import oracle.cabo.ui.data.DictionaryData;


public class abhiSupplierSiteNameComponent extends BneAbstractListOfValues {

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


6. Once above class files are compiled in Jdev or server, place the class files in
$JAVA_TOP/abhi/oracle/apps/lovtest/<respective dir>

7. Execute the below code

--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 =>
'XXX_AP_XINTG_INTF1', --BNE_INTERFACE_COLS_B.INTERFACE_CODE
  P_INTERFACE_COL_NAME =>
'P_VENDOR_SITE_CODE',  --BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME
  P_JAVA_CLASS => 'abhi.oracle.apps.lovtest.component.icarSupplierSiteNameComponent',
  P_WINDOW_CAPTION => 'Supplier Sites', P_WINDOW_WIDTH => 500, P_WINDOW_HEIGHT => 500, P_TABLE_BLOCK_SIZE => 50,
  P_TABLE_COLUMNS => 'VENDOR_SITE_CODE',
  P_TABLE_SELECT_COLUMNS => 'P_VENDOR_SITE_CODE',
  P_TABLE_COLUMN_ALIAS => 'P_VENDOR_SITE_CODE',
  P_TABLE_HEADERS => 'Vendor Site Code',
  P_TABLE_SORT_ORDER => 'yes', P_USER_ID => -1
  );
  COMMIT;
END;


8. Bounce the apache

Host Concurrent Program check if file exists

Steps:

• shell script is generally saved with ".sh". But here should be "prog" extension.
• save the shell script with extension "prog". 
• move this file to server.
• place our script file in bin folder of any product top. 
• create a soft link for this file by using ln command.
• For example ln $FND_TOP/bin/fndcpesr <our script name without extension>
  

 

Create a .prog file in $XX_CUSTOM_TOP

echo "Start"
P_SOURCE_DIR=$5
P_FILENAME=$6
echo "File Path-->>" $P_SOURCE_DIR
echo "File Name-->>" $P_FILENAME
if [ ! -f $P_SOURCE_DIR/$P_FILENAME ]
then
    echo $P_SOURCE_DIR/$P_FILENAME " does not exist"
    echo "**************************************************" #>> $REPFILE
    echo "*$P_SOURCE_DIR/$P_FILENAME " does not exist" *" #>> $REPFILE
    echo "**************************************************" # >> $REPFILE
    PROBLEMS=1
    exit 1
else
    echo $P_SOURCE_DIR/$P_FILENAME " exist"
    exit 0
fi


Run below commands
------------------------

cd $XX_CUSTOM_TOP/bin
chmod 755 XX_FILE_CHECK.prog
$ dos2unix XX_FILE_CHECK.prog
$ ln -s $FND_TOP/bin/fndcpesr XX_FILE_CHECK

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

Create executable

Create Program.

How to Register a Host Concurrent Program in Applications

touch /u01/1153/visionappl/fnd/11.5.0/bin/TEST.txt

2. Register this as a concurrent executable in Application Object Library called TEST of type HOST.

3. Register this as a concurrent program in Application Object Library called TEST of type HOST.

4. Add this request to the System Administrators request group.

5. In the $FND_TOP/bin create the softlink from TEST.prog to fndcpesr:

        $ ln -s $FND_TOP/bin/fndcpesr  $FND_TOP/bin/TEST

6. IN $FND_TOP/bin type:
      TEST TEST.prog 
   The result will be a creation of a file called TEST.txt.

7. Delete the file TEST.txt: rm TEST.txt

8. Bounce the concurrent managers.

9. Test in Applications, by running TEST as a user with  the 'System Administrators' responsibility.

Java Data Structure Examples

http://www.tutorialspoint.com/java/java_data_structures.htm

The data structures provided by the Java utility package are very powerful and perform a wide range of functions. These data structures consist of the following interface and classes:

• Enumeration
• BitSet
• Vector
• Stack
• Dictionary
• Hashtable
• Properties

Using Lookups in OAF

Fetching values from Lookup


//returns lookup description
  public String getLookupDescription(String paramString1, String paramString2)
  {
    HashMap localHashMap = getLookupData(paramString1, paramString2);
    if (null != localHashMap) {
      return (String)localHashMap.get("DESCRIPTION");
    }
    return "";
  }

//returns lookup meaning
 public String getLookupMeaning(String paramString1, String paramString2)
 {
   HashMap localHashMap = getLookupData(paramString1, paramString2);
   if (null != localHashMap) {
     return (String)localHashMap.get("MEANING");
   }
   return "";
 }


//returns HashMap for meaning and Description

   public HashMap getLookupData(String paramString1, String paramString2)
   {
     if ((null == paramString1) || (null == paramString2)) {
       return null;
     }
     ViewObject localViewObject1 = findViewObject("LookupDataVO");
     if (null != localViewObject1) {
       localViewObject1.remove();
     }
     OADBTransactionImpl localOADBTransactionImpl = (OADBTransactionImpl)getOADBTransaction();
     String str1 = localOADBTransactionImpl.getCurrentLanguage();

     String str2 = "select meaning, description from fnd_lookup_values where lookup_type = :1 and lookup_code = :2 and language = :3 ";

   ViewObject localViewObject2 = createViewObjectFromQueryStmt("LookupDataVO", str2);
   localViewObject2.setWhereClauseParam(0, paramString1);
   localViewObject2.setWhereClauseParam(1, paramString2);
   localViewObject2.setWhereClauseParam(2, str1);

   Row localRow = localViewObject2.first();
   HashMap localHashMap = new HashMap();

   if (null != localRow)
   {
     localHashMap.put("MEANING", (String)localRow.getAttribute("MEANING"));
     localHashMap.put("DESCRIPTION", (String)localRow.getAttribute("DESCRIPTION"));
   }
   else
   {
     localHashMap.put("MEANING", "");
     localHashMap.put("DESCRIPTION", "");
   }
   localViewObject2.remove();
   return localHashMap;
 }

wf_engine.completeactivity

CREATE PROCEDURE xx_continue_activity (
   errbuf            IN OUT NOCOPY   VARCHAR2,
   errcode           IN OUT NOCOPY   INTEGER,
   p_itemtype        IN              VARCHAR2,
   p_activity_name   IN              VARCHAR2
)
AS
   v_errorname      VARCHAR2 (30);
   v_errormsg       VARCHAR2 (2000);
   v_errorstack     VARCHAR2 (32000);
   invalid_action   EXCEPTION;
   PRAGMA EXCEPTION_INIT (invalid_action, -20002);

   CURSOR c1
   IS
      SELECT item_key
        FROM wf_item_activity_statuses
       WHERE item_type = p_itemtype
         AND activity_status = 'NOTIFIED'
         AND process_activity IN (
                SELECT MAX (instance_id)
                  FROM wf_process_activities
                 WHERE activity_item_type = p_itemtype
                   AND activity_name = p_activity_name);
--AND item_key = 1228692;
BEGIN
   FOR c1_rec IN c1
   LOOP
      BEGIN
         fnd_file.put_line (fnd_file.output,
                            'EXECUTING FOR ITEM-KEY: ' || c1_rec.item_key
                           );
         wf_engine.completeactivity (itemtype      => p_itemtype,
                                     itemkey       => c1_rec.item_key,
                                     activity      => p_activity_name,
                                     --'xx_INSUFF_RESPON_BLOCK',
                                     RESULT        => wf_engine.eng_null
                                    );
         COMMIT;
      EXCEPTION
         WHEN invalid_action
         THEN
            wf_core.get_error (v_errorname, v_errormsg, v_errorstack);
            fnd_file.put_line (fnd_file.LOG, 'ITEM-KEY: ' || c1_rec.item_key);
            fnd_file.put_line (fnd_file.LOG, 'ERROR NAME: ' || v_errorname);
            fnd_file.put_line (fnd_file.LOG, 'ERROR MESSAGE: ' || v_errormsg);
            fnd_file.put_line (fnd_file.LOG, 'ERROR STACK: ' || v_errorstack);
      END;
   END LOOP;
END xx_continue_activity;

Apache Bounce

Use below commands 

2) cd $ADMIN_SCRIPTS_HOME
3) adapcctl.sh stop
4) adoacorectl.sh stop
5) adapcctl.sh start
6) adoacorectl.sh start