Wednesday Jul 25, 2012

Application Module Extension in OAF

Like the Controller extension, AM extension is also not supported by Oracle. However for some business needs we have to extend it sometimes.

In this exercise we have taken our “Data Entry OAF page” can be found @ https://blogs.oracle.com/prajkumar/entry/insert_data_oaf_page to extend application module i.e. InsertAM

This AM can be found under below BC4J package prajkumar.oracle.apps.fnd.insertdemo.server.InsertAM

 

Why we are extending AM:-

This InsertAM contains an apply method which subsequently commits the transaction.

public void apply() 

 getTransaction().commit();
}

Our business need is to capture user name and user id at runtime and insert it into a custom audit table for audit purpose

 

Here is Audit Table script --

CREATE TABLE xx_audit 
(   -- -------------------
    -- Data Columns
    -- -------------------- 
    user_id                  VARCHAR(50), 
    user_name            VARCHAR(50), 
    -- -------------------
    -- Who Columns
    -- -------------------
    last_update_date   DATE        NOT NULL,
    last_updated_by    NUMBER  NOT NULL,
    creation_date         DATE        NOT NULL,
    created_by              NUMBER  NOT NULL,
    last_update_login   NUMBER
);

 

Steps to Extend Application Module (AM)

1. Create a New Application Module (AM)

Right Click on InsertDemo > New > ADF Business Components > Application Module

Package -- prajkumar.oracle.apps.fnd.insertdemo.server

Name -- ExtendedAM

Extends -- prajkumar.oracle.apps.fnd.insertdemo.server.InsertAM

 

 

 

 

 

 

Write following code in ExtendedAMImpl.java

import oracle.apps.fnd.framework.OAException;
import java.sql.PreparedStatement;
import java.sql.Connection;

public class ExtendedAMImpl extends InsertAMImpl

 ....
 
 public void apply() 
 { 
  java.sql.Date d = getOADBTransaction().getCurrentDBDate().dateValue(); 
  try  
  {  
   Connection conn = getOADBTransaction().getJdbcConnection();
   String Query = "insert into xx_audit values(:1,:2,:3,:4,:5,:6,:7)";
   PreparedStatement stmt = conn.prepareStatement(Query);
   
   stmt.setInt(1, getOADBTransaction().getUserId());
   stmt.setString(2, getOADBTransaction().getUserName());
   stmt.setDate(3, d);
   stmt.setInt(4, getOADBTransaction().getUserId());
   stmt.setDate(5, d);
   stmt.setInt(6, getOADBTransaction().getUserId());
   stmt.setInt(7, getOADBTransaction().getUserId());
   stmt.execute();
  }
  
  catch(Exception exception)
  {
   throw new OAException("Error in Staffing Query"+exception, OAException.ERROR);  
  }  
 super.apply(); 
 } 
}

 

2. Perform AM Substitution

Double Click on InsertDemo.jpx

Business Components > Substitutions

 

 

 

3. After substitution it will modify *.jpx

In our case it will modify InsertDemo.jpx at project location

i.e. -- D:\xxxx\jdevhome\jdev\myclasses

 

4. Migrate/ Import the modified jpx

Open Command Prompt and go to following location of your project D:\xxxx\jdevbin\oaext\bin

 

Use this Import Command to import jpx

 

 

 

 

 

5. Congratulation you have successfully finished. Run Your InsertPG page and Test Your Work

 

 

 

 

 

 

 

 

 

How to deploy JAR file into R12 server in OA

Steps to add JAR file into R12 server in OA --

 

1. Deploy your JAR file at some custom location in R12 server

i.e. –

/u01/app/apnac03r12/XX_TEST/

 

 

2. Open “orion-application.xml” file present at path –

$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/

 

3. Edit orion-application.xml” file to add JAR file path

i.e. –

<library path="/u01/app/apnac03r12/XX_TEST/jxl.jar" />

 

 

4. Bounce Apache Server 

Tuesday Jul 24, 2012

Import Data from *.xlsx file to DB Table through OAF page

1. Create a New Workspace and Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarImportxlsDemo

 

Automatically a new OA Project will also be created

 

Project Name -- ImportxlsDemo

Default Package -- prajkumar.oracle.apps.fnd.importxlsdemo

 

2. Add following JAR files to Apache Library

1. poi-ooxml-3.7.jar

http://grepcode.com/snapshot/repo1.maven.org/maven2/org.apache.poi/poi-ooxml/3.7

 

2. ooxml-schemas-1.1.jar

http://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas/1.1

 

3. stax-api-1.0.1.jar

http://www.jarfinder.com/index.php/jars/versionInfo/69009

 

4. log4j-1.2.16.jar

http://mvnrepository.com/artifact/log4j/log4j/1.2.16

 

5. poi-ooxml-schemas-3.7.jar

http://mavenhub.com/mvn/central/org.apache.poi/poi-ooxml-schemas/3.7

 

6. poi-3.7-20101029.jar

http://mvnrepository.com/artifact/org.apache.poi/poi/3.7-beta1

 

7. xmlbeans-2.4.0.jar

http://www.jarfinder.com/index.php/jars/versionInfo/14387

 

8. dom4j-1.6.1.jar

http://grepcode.com/snapshot/repo1.maven.org/maven2/dom4j/dom4j/1.6.1

 

 

Steps to add JAR files in Local Machine

Right Click on ImportxlsDemo > Project Properties > Libraries > Add jar/Directory and browse to directory where all JAR files have been downloaded and select the JAR files

 

 

 

 

Click here to know Steps to Add JAR file into R12 server in OA

 

3. Create a New Application Module (AM)

Right Click on ImportxlsDemo > New > ADF Business Components > Application Module

Name -- ImportxlsAM

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.server

Check Application Module Class: ImportxlsAMImpl Generate JavaFile(s)

 

4. Create Test Table in which we will insert data from *.xlsx file

CREATE TABLE xx_import_excel_data_demo
(    -- --------------------
     -- Data Columns
     -- --------------------
     column1                VARCHAR2(100),
     column2                VARCHAR2(100),
     column3                VARCHAR2(100),
     column4                VARCHAR2(100),
     column5                VARCHAR2(100),
     -- --------------------
     -- Who Columns
     -- --------------------
     last_update_date   DATE         NOT NULL,
     last_updated_by    NUMBER   NOT NULL,
     creation_date         DATE         NOT NULL,
     created_by             NUMBER    NOT NULL,
     last_update_login  NUMBER
);

 

5. Create a New Entity Object (EO)

Right click on ImportxlsDemo > New > ADF Business Components > Entity Object

Name – ImportxlsEO

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.schema.server

Database Objects -- XX_IMPORT_EXCEL_DATA_DEMO

 

Note – By default ROWID will be the primary key if we will not make any column to be primary key

 

Check the Accessors, Create Method, Validation Method and Remove Method

 

6. Create a New View Object (VO)

Right click on ImportxlsDemo > New > ADF Business Components > View Object

Name -- ImportxlsVO

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.server

 

In Step2 in Entity Page select ImportxlsEO and shuttle it to selected list

In Step3 in Attributes Window select all columns and shuttle them to selected list

 

In Java page

Select Generate Java File for View Object Class: ImportxlsVOImpl -> Generate Java File -> Bind Variable Accessors

Select Generate Java File for View Row Class: ImportxlsVORowImpl -> Generate Java File -> Accessors

 

7. Add Your View Object to Root UI Application Module

Right click on ImportxlsAM > Edit ImportxlsAM > Data Model >

Select ImportxlsVO and shuttle to Data Model list

 

8. Create a New Page

Right click on ImportxlsDemo > New > Web Tier > OA Components > Page

Name -- ImportxlsPG

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.webui

 

9. Select the ImportxlsPG and go to the strcuture pane where a default region has been created

 

10. Select region1 and set the following properties:

 

Attribute

Property

ID

PageLayoutRN

AM Definition

prajkumar.oracle.apps.fnd.importxlsdemo.server.ImportxlsAM

Window Title

Import Data From Excel(*.xlsx) through OAF Page Demo Window

Title

Import Data From Excel(*.xlsx) through OAF Page Demo

 

11. Create messageComponentLayout Region Under Page Layout Region

Right click PageLayoutRN > New > Region

 

Attribute

Property

ID

MainRN

Item Style

messageComponentLayout

 

12. Create a New Item messageFileUpload Bean under MainRN

Right click on MainRN > New > messageFileUpload

Set Following Properties for New Item --

 

Attribute

Property

ID

MessageFileUpload

Item Style

messageFileUpload

 

13. Create a New Item Submit Button Bean under MainRN

Right click on MainRN > New > messageLayout

Set Following Properties for messageLayout --

 

Attribute

Property

ID

ButtonLayout

 

Right Click on ButtonLayout > New > Item

 

Attribute

Property

ID

Go

Item Style

submitButton

Attribute Set

/oracle/apps/fnd/attributesets/Buttons/Go

 

14. Create Controller for page ImportxlsPG

Right Click on PageLayoutRN > Set New Controller

Package Name: prajkumar.oracle.apps.fnd.importxlsdemo.webui

Class Name: ImportxlsCO

 

Write Following Code in ImportxlsCO in processFormRequest

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import java.io.Serializable;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.cabo.ui.data.DataObject;
import oracle.jbo.domain.BlobDomain;

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processFormRequest(pageContext, webBean);

 OAApplicationModule am = pageContext.getApplicationModule(webBean);

 if (pageContext.getParameter("Go") != null)
 {
  DataObject fileUploadData =

(DataObject)pageContext.getNamedDataObject("MessageFileUpload"); 

  String fileName;

  try
  {
   fileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
  }
  catch(NullPointerException ex)
  {
   throw new OAException("Please Select a File to Upload", OAException.ERROR);
  }

  BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);

  try
  {
   OAApplicationModule oaapplicationmodule = pageContext.getRootApplicationModule();
   Serializable aserializable2[] = {uploadedByteStream};
   Class aclass2[] = {BlobDomain.class };
   oaapplicationmodule.invokeMethod("ReadExcel", aserializable2,aclass2);
  }
  catch (Exception ex)
  {
   throw new OAException(ex.toString(), OAException.ERROR);
  }
 }
}

 

 

Write Following Code in ImportxlsAMImpl.java

import oracle.jbo.domain.BlobDomain;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import oracle.apps.fnd.framework.server.OAViewObjectImpl;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public void createRecord(String[] excel_data)

 ImportxlsVOImpl vo = (ImportxlsVOImpl)getImportxlsVO1();
 vo.InsertRecord(excel_data);
 getTransaction().commit();
}
   
public void ReadExcel(BlobDomain fileData) throws IOException
{
 try
 {
  InputStream in = fileData.getBinaryStream();
  XSSFWorkbook workbook = new XSSFWorkbook(in);
  
  XSSFSheet sheet = workbook.getSheetAt(0);
       
  int rowsCount = sheet.getLastRowNum();
  for (int i = 0; i <= rowsCount; i++)
  {
   int j = 0;
   int colCounts;
   Row row = sheet.getRow(i);
   try
   {
    colCounts = row.getLastCellNum();
   }
   catch (NullPointerException e)
   {
    colCounts=1;
   }
   String[] excel_data = new String[colCounts+1];
   
   for (int k = 0; k < colCounts; k++)
   {
    j=j+1;
    try
    {
     Cell cell = row.getCell(k);

     switch (cell.getCellType())
     {

      case Cell.CELL_TYPE_STRING:
      excel_data[j] = cell.getRichStringCellValue().getString();
      break;

      case Cell.CELL_TYPE_NUMERIC:
      if (DateUtil.isCellDateFormatted(cell))
      {
       DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
       excel_data[j] =df.format(cell.getDateCellValue());
      }
      else
      {
       int resultVar; 
       resultVar = (int)cell.getNumericCellValue();
       excel_data[j] = Integer.toString(resultVar);
      }
      break;

      case Cell.CELL_TYPE_BOOLEAN:
      excel_data[j] = Boolean.toString(cell.getBooleanCellValue());
      break;

      case Cell.CELL_TYPE_FORMULA:
      excel_data[j] = (String)cell.getCellFormula();
      break;
      
      default:
      excel_data[j] = "";
     }
    }
    catch (NullPointerException e)
    {
     excel_data[j] = "";
    }
   }

   createRecord(excel_data);
  }
 }
 catch (IOException e)
 {
  e.printStackTrace();
 }
}

 

 

Write Following Code in ImportxlsVOImpl.java

import oracle.apps.fnd.framework.server.OAViewObjectImpl;
import oracle.jbo.Row;

public void InsertRecord(String[] excel_data)
{
 try
 {
  executeQuery();   
  Row row = createRow();
  
  for (int i=1; i < excel_data.length; i++)
  {
   row.setAttribute("Column" +i ,excel_data[i]);
   insertRow(row);
  }
 }
 catch(Exception e)
 {
  System.out.println(e.getMessage());
 }
}

 

15. Congratulation you have successfully finished. Run Your page and Test Your Work

 

Consider Excel PRAJ_TEST.xlsx with following data --

 

Lets Try to import this data into DB Table --

 

 

 

 

Sunday Jul 15, 2012

Oracle iRecruitment vacancy Segregation Full Solution

This document describes the detailed steps for implementing Vacancy segregation in Oracle iRecruitment. The solution is configurable and can be very easily rolled out to new business groups as required.

This Solution allows attaching each external Oracle iRecruitment URL with one or more business groups. By doing so, the URL will secure the vacancies against one or more Business Groups. Therefore this design implements segregation of Oracle iRecruitment vacancies as per a set of Business Groups.

 

 

This image shows iRecruitment Visitors homepage before implementing the solution.

 

Note - After implementing the solution list of locations available for the search becomes restricted along with the restricted list of vacancies available for search.

 

Installation Steps

1. Create a custom copy of IrcVisitor.jsp and Compile

In this step, you will create a custom copy of IrcVisitor.jsp. You will have to repeat this step for each unique segregation URL that you wish to create in Oracle iRecruitment

cd $OA_HTML

cp IrcVisitor.jsp XXUSIrcVisitor.jsp

 

2. Edit the Custom Copy of IrcVisitor.jsp

cd $OA_HTML

vi XXUSIrcVisitor.jsp

 

Search for text

String responsibilitykey = "IRC_EXT_CANDIDATE";

And replace with above sentence by

String responsibilitykey = "XX_US_IRC_EXT_CANDIDATE";

 

Save the file XXUSIrcVisitor.jsp

 

Before to replace the responsibilitykey --

 

After replacing the responsibilitykey --

 

3. Compile the JSP file and bounce oacore

cd $FND_TOP/patch/115/bin/

perl ojspCompile.pl --compile -s 'XXUSIrcVisitor.jsp' --flush

cd $ADMIN_SCRIPTS_HOME

adoacorectl.sh stop

adoacorectl.sh start

 

4. Create Custom Responsibility

Create Custom Responsibility “XX US iRecruitment External Candidate Resp” as given below screen shot --

 

 

The features of this responsibility are similar to Standard IRC_EXT_CANDIDATE.

 

Note – Include all Function Exclusions at the time of responsibility creation and do not forget to set “Available From” option to “Oracle Self Service Web Applications”

 

5. Copy the profile options from IRC_EXT_CANDIDATE to XX_US_IRC_EXT_CANDIDATE

Use script Copy_Profile_Values.sql to copy the profile values

After running the script, able to see the profile option values assigned to the new responsibility, as shown in below –

 

 

 

 

Note – Profile Option values will be get copied across from the Standard Responsibility to the Custom Responsibility. However, the Script ensures that the profile option “IRC: Registration Responsibility” points to new custom responsibility

 

6. Create a Descriptive Flexfield for Mapping the Business Groups

In this step, create a context sensitive Flexfield against the Lookup entry screen. This will ensure that business groups can be picked from LOV when defining the lookup codes. So have to create Flexfield context for each segregated URL.

For Example – from setup perspective, will create lookup type = “US_VACANCY_BG_LIST” and each lookup code will be mapped to a business group via descriptive Flexfield.

The context sensitive Flexfield will ensure that this DFF will appear only when lookup codes against the lookup type = “US_VACANCY_BG_LIST”

 

Download US_VACANCY_BG_LIST.ldt file and following FNDLOAD command

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct US_VACANCY_BG_LIST.ldt

After running this FNDLOAD check following setups have been configured –

 

Go to Application Developer > Flexfield > Descriptive > Register

 

 

Query using following criteria

Application -- Application Object Library

Name -- FND_COMMON_LOOKUPS

 

 

Click on “Reference Fields”

 

 

Go to Application Developer > Flexfield > Descriptive > Segments

 

 

Query using following criteria –

Application – Application Object Library

Title – Common Lookups

 

 

 

Select US_VACANCY_BG_LIST > Segments

 

 

7. Assign business groups to lookup type for security

In this step, create lookup type names US_VACANCY_BG_LIST. This lookup type has lookup codes. Each lookup code entry will represent a business group that is available to the Custom Oracle iRecruitment Responsibility/ URL. This lookup type is attached to a custom Oracle iRecruitment responsibility via a custom profile option

Note – The name of the lookup type will match the DFF context value for Common Lookups descriptive Flexfield. The number of lookup types created will be same as the number of custom Oracle iRecruitment URL’s that you wish to create for segregating the vacancies. Typically if a URL restricts the vacancies listed by one business group, then for such cases only one single lookup will be created with the lookup type.

 

Create Custom Lookup –

Navigate to Responsibility Application Developer > Application > Lookups > Common

 

 

 

 

 

 

 

Save Everything.

 

8. Create a custom profile option

This profile option will map the lookup type to a responsibility. Effectively a set of business groups will be mapped to a responsibility. The custom iRecruitment responsibility will be mapped to a URL. Therefore this profile will dictate the list of business groups which have theirs vacancies exposed to a custom Oracle iRecruitment URL.

 

Download US_VAC_INCLUDED_ORG_LOOKUP_TYPE_PROFILE.ldt file and following FNDLOAD command

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct US_INCLUDED_ORG_LOOKUP_TYPE_PRF.ldt

 

After running the above script, the profile option and its value will be created as shown below

 

Navigate to responsibility Application Developer > Profile

Query Profile Name as “XX_US_VAC_INCLUDED_ORG_LOOKUP_TYPE”

 

 

Navigate to responsibility System Administrator > Profile > System

Query with following Criteria –

Responsibility -- XX US iRecruitment External Candidate Resp

Profile -- XX US Vacancy Included Organizations LKP Type

 

 

 

Note – Assign value US_VACANCY_BG_LIST to profile Option “XX US Vacancy Included Organizations LKP Type” at responsibility level, as shown in above picture. If FNDLOAD was run, then manual step is not required.

 

9. Assign Custom responsibility to US_GUEST_USER

Navigate to System Administrator > Security > User > Define

Assign responsibility “XX US iRecruitment External Candidate Resp” to GUEST_USER user as shown below

 

 

10. Create grant for Custom Responsibility

Navigate to Functional Administrator Responsibility > Home

Click Create Grant

 

 

In the Name field, type in a unique name “XX US iRecruitment External Candidate Resp”

In the Security Context Region give following Values –

Grantee Type – Specific User

Grantee – GUEST

Responsibility -- XX US iRecruitment External Candidate Resp

 

 

Click Next Button

In the Set region query up ‘IRC External Site Visitor Permission Set’ and Select Next

 

 

 

On the review page verify that data is correct and then click Finish button

The grant should be created

 

11. Create PL/ SQL packages for security

Run the SQL Scripts by connecting to APPS schema

XX_SECURE_VACANCY_PKG.pks

XX_SECURE_VACANCY_PKG.pkb

 

12. Register the row level security

Run the SQL scripts by connecting to APPs schema

XX_SECURE_VACANCY.sql

 

13. Run your Custom JSP page and test your work

 

 

Note - After implementing the solution list of locations available for the search becomes restricted along with the restricted list of vacancies available for search.

 

 

Personalization and Extensions for OAF Pages Developer Guide

Here I will be sharing all Oracle Applications Framework Personalization and Extensions related articles

Item Type

Author

1

2

3

4

5

6

7

Enable Personalization Link for OAF Pages

OA Framework Personalization

Deploy JAR File into R12 in OA Server  

Controller Extension in OAF

Application Module Extension in OAF

EO based VO Extension in OAF

Entity Object Extension in OAF

Puneet Rajkumar

Puneet Rajkumar

Puneet Rajkumar

Puneet Rajkumar

Puneet Rajkumar

Puneet Rajkumar

Puneet Rajkumar

Controller Extension in OAF

Oracle does not recommend that customers extend controller objects associated with regions or webbeans in shipped E-Business Suite product pages.

Controller class (oracle.apps.fnd.framework.webui.OAControllerImpl) methods should effectively be considered private, since their implementation is subject to change. Controller extensions are therefore not considered to be durable between upgrades.

If it is absolutely essential to handle custom form submit events on a shipped product page, processFormRequest() is the only method that should be overriden in a controller class, although the risks outlined above still apply.

 

Let us try to Extend Controller in OAF Page –

Create one search page as explained in below link –

https://blogs.oracle.com/prajkumar/entry/create_oaf_search_page

In this exercise I am going to extend CO of SearchPG. First lets create CO for SearchPG.

Right Click PageLayoutRN under SearchPG page > Set New Controller 

Package Name -- prajkumar.oracle.apps.fnd.searchdemo.webui

Class Name --  SearchCO

Now we will extend this newly created CO under this exercise. 

The purpose of this exercise is to modify the VO query of results table. I have changed the Column1 and Column2 fields Property Selective Search Criteria as False.

Now when we click on Go button all the records are displaying in the results table and our OBJECTIVE is to bind the VO query of results table in such a way that in result Column1 value val5 and Column2 value val6 should not come as result on click Go button

 

 

 

Now for knowing which controller to extend we click on "About This Page" Link and select Expand All. Here we can see the Name of the controller that we need to extend

 

 

 

1. Create a New Workspace and Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarCOExtensionDemo

Automatically a new OA Project will also be created

Project Name -- COExtensionDemo

Default Package -- prajkumar.oracle.apps.fnd.coextensiondemo

 

2. Create a New Java Class

Right Click on COExtensionDemo > New > General > Java Class

Name -- ExtendedCO

Package -- prajkumar.oracle.apps.fnd.coextensiondemo.server

Extends -- prajkumar.oracle.apps.fnd.searchdemo.webui.SearchCO

 

 

Note -- Give the Name of your Extended Class give its package path and in the extends property select base class

 

 

3. Write below logic in ExtendedCO Java Class

package prajkumar.oracle.apps.fnd.coextensiondemo.webui;
     
import prajkumar.oracle.apps.fnd.searchdemo.webui.SearchCO;

import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.beans.layout.OAQueryBean;
import prajkumar.oracle.apps.fnd.searchdemo.server.SearchVOImpl;
 
public class XXItemSearchCO extends ItemSearchCO
{
 public XXItemSearchCO()
 {
 } 
        
 public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) 
 { 
  super.processFormRequest(pageContext, webBean); 
  OAApplicationModule am = pageContext.getApplicationModule(webBean); 
  OAQueryBean queryBean = (OAQueryBean)webBean.findChildRecursive("QueryRN"); 
     
  //Capturing Go Button ID 
  String go = queryBean.getGoButtonName(); 
            
  //If its Not NULL which mean user has pressed "Go" Button 
  if(pageContext.getParameter(go)!=null) 
  { 
   // Setting whereClause at Runtime to restrict the query  
   SearchVOImpl vo = (SearchVOImpl)am.findViewObject("SearchVO1"); 
   vo.setWhereClause(null); 
   vo.setWhereClause("Column1 <>:1 AND Column2 <>:2");
   vo.setWhereClauseParam(0,"val5");
   vo.setWhereClauseParam(1,"val6");
  }
 }
}

 

4. Attach new controller to SearchPG through personalization

Click on Personalize Page link on top right hand side of your page

 

 

Note -- If you are not able to see this link then go through below link –

https://blogs.oracle.com/prajkumar/entry/how_to_enable_personalization_link

 

Click on Complete View -> Expand All -> Click on personalize icon next to Page Layout

 

 

Now at site level give the path of extended controller as we are extending the controller at SITE LEVEL

prajkumar.oracle.apps.fnd.coextensiondemo.webui.ExtendedCO

 

 

Click Apply -> Return to Application

 

5. Congratulation you have successfully finished. Run Your SearchPG page and Test Your Work

 

 

Click Go

Note – Record with Column1 value val5 and Column2 value val6 is not coming in result

 

 

How to Enable Personalization link for OAF Pages

Set the values of following profiles to enable Personalization Page link in OAF Pages

 

Profile Name

Value

FND: Personalization Region Link Enabled

Yes

Personalize Self-Service Defn

Yes

Disable Self-Service Personal

No

 

After Setting the Values of above profiles

 

Saturday Jul 14, 2012

Real Time Oracle Consulting Solutions

 In this area you will find some of the Real-Time Oracle Consulting Solutions.

Item Type

        Implementation

Author

1

2

iRecruitment Vacancy Segregation Solution          Full Solution

Oracle Application in DMZ                                     Full Solution

Puneet Rajkumar

Puneet Rajkumar

Oracle iRecruitment Vacancy Segregation Solution

Business Needs

In large organizations that have implemented Oracle iRecruitment, usually there is a business need to have multiple external facing Oracle iRecruitment URL’s. Each such URL might correspond to one or more business groups

 

For example, the standard URL http://nacdellxx.com:XXXX/xx/IrcVisitor.jsp can display all the vacancies across all the business groups in Organization.

 

However business might need URLs similar to below –

http://nacdellxx.com:XXXX/xx/India.jsp

http://nacdellxx.com:XXXX/xx/USA.jsp

 

Business has a need to create multiple facing URL’s in Oracle iRecruitment 

 

Business Challenges

Business want to secure the vacancies against one or more Business Groups, and thus displaying vacancies only for specific set of departments/ locations

 

Business Solution

·         A custom copy of IrcVisitor.jsp will be created

·         The customized copy of IrcVisitor.jsp will reference a custom Oracle iRecruitment responsibility

·         A lookup type will be defined for each custom Oracle iRecruitment responsibility. Each lookup code entry within lookup type will map to a business group

·         The lookup type definition will be attached to custom Oracle iRecruitment responsibility via a profile option. Effectively this allows a set of business groups to be associated with a specific custom iRecruitment responsibility

 

                        Figure: Oracle iRecruitment Vacancy Segregation Solution Blue Print

 

Solution Features

·         This solution allows attaching each external Oracle iRecruitment URL with one or more business groups. By doing so, the URL will secure the vacancies as per a set of Business Groups

·         The solution is configurable and can be very easily rolled out to new business groups as required by simple mapping

 

Benefits

·         By this Solution Oracle iRecruitment URL will secure the vacancies against one or more business groups, and thus displaying vacancies only for specific set of departments/ locations

 

To get Full Solution Implementation steps Click Here

Friday Jul 13, 2012

Remove Special Characters from String

To Remove Special Characters Use following Replace Functions

REGEXP_REPLACE(<Your_String>,'[^[:alnum:]'' '']', NULL)

 

Example --

SELECT REGEXP_REPLACE('##$$$123&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual;

 

OutPut –

123

 

OR

 

Use TRANSLATE function as given below

 

Example --

SELECT translate('##$$$1$$2#3&&!!__!', '[0-9]#$&&!_','[0-9]') FROM dual;

 

OR

 

SELECT translate('##$$$123&&!!__!', '0#$&&!_','0') FROM dual;

 

OutPut –

123 

Sunday Jul 08, 2012

Import Data from Excel sheet to DB Table through OAF page

1. Create a New Workspace and Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarImportxlsDemo

 

Automatically a new OA Project will also be created

 

Project Name -- ImportxlsDemo

Default Package -- prajkumar.oracle.apps.fnd.importxlsdemo

 

2. Add JAR file jxl-2.6.3.jar to Apache Library

Download jxl-2.6.3.jar from following link –

http://www.findjar.com/jar/net.sourceforge.jexcelapi/jars/jxl-2.6.jar.html

 

Steps to add jxl.jar file in Local Machine

Right Click on ImportxlsDemo > Project Properties > Libraries > Add jar/Directory and browse to directory where jxl-2.6.3.jar has been downloaded and select the JAR file

 

 

 

 

  Click here to know Steps to Add JAR file into R12 server in OA 

 

3. Create a New Application Module (AM)

Right Click on ImportxlsDemo > New > ADF Business Components > Application Module

Name -- ImportxlsAM

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.server

 

Check Application Module Class: ImportxlsAMImpl Generate JavaFile(s)

 

4. Create Test Table in which we will insert data from excel

CREATE TABLE xx_import_excel_data_demo
   -- --------------------
     -- Data Columns
     -- --------------------
     column1                 VARCHAR2(100),
     column2                 VARCHAR2(100),
     column3                 VARCHAR2(100),
     column4                 VARCHAR2(100),
     column5                 VARCHAR2(100),
     -- --------------------
     -- Who Columns
     -- --------------------
     last_update_date   DATE         NOT NULL,
     last_updated_by    NUMBER   NOT NULL,
     creation_date         DATE         NOT NULL,
     created_by             NUMBER    NOT NULL,
     last_update_login  NUMBER
);

 

5. Create a New Entity Object (EO)

Right click on ImportxlsDemo > New > ADF Business Components > Entity Object

Name – ImportxlsEO

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.schema.server

Database Objects -- XX_IMPORT_EXCEL_DATA_DEMO

 

Note – By default ROWID will be the primary key if we will not make any column to be primary key

Check the Accessors, Create Method, Validation Method and Remove Method

 

6. Create a New View Object (VO)

Right click on ImportxlsDemo > New > ADF Business Components > View Object

Name -- ImportxlsVO

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.server

 

In Step2 in Entity Page select ImportxlsEO and shuttle it to selected list

In Step3 in Attributes Window select all columns and shuttle them to selected list

 

In Java page Uncheck Generate Java file for View Object Class: ImportxlsVOImpl

Select Generate Java File for View Row Class: ImportxlsVORowImpl -> Generate Java File -> Accessors

 

7. Add Your View Object to Root UI Application Module

Right click on ImportxlsAM > Edit ImportxlsAM > Data Model >

Select ImportxlsVO and shuttle to Data Model list

 

8. Create a New Page

Right click on ImportxlsDemo > New > Web Tier > OA Components > Page

Name -- ImportxlsPG

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.webui

 

9. Select the ImportxlsPG and go to the strcuture pane where a default region has been created

 

10. Select region1 and set the following properties:

 

Attribute

Property

ID

PageLayoutRN

AM Definition

prajkumar.oracle.apps.fnd.importxlsdemo.server.ImportxlsAM

Window Title

Import Data From Excel through OAF Page Demo Window

Title

Import Data From Excel through OAF Page Demo

 

11. Create messageComponentLayout Region Under Page Layout Region

Right click PageLayoutRN > New > Region

 

Attribute

Property

ID

MainRN

Item Style

messageComponentLayout

 

12. Create a New Item messageFileUpload Bean under MainRN

Right click on MainRN > New > messageFileUpload

Set Following Properties for New Item --

 

Attribute

Property

ID

MessageFileUpload

Item Style

messageFileUpload

 

13. Create a New Item Submit Button Bean under MainRN

Right click on MainRN > New > messageLayout

Set Following Properties for messageLayout --

 

Attribute

Property

ID

ButtonLayout

 

Right Click on ButtonLayout > New > Item

 

Attribute

Property

ID

Go

Item Style

submitButton

Attribute Set

/oracle/apps/fnd/attributesets/Buttons/Go

 

14. Create Controller for page ImportxlsPG

Right Click on PageLayoutRN > Set New Controller

Package Name: prajkumar.oracle.apps.fnd.importxlsdemo.webui

Class Name: ImportxlsCO

 

Write Following Code in ImportxlsCO in processFormRequest

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import java.io.Serializable;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.cabo.ui.data.DataObject;
import oracle.jbo.domain.BlobDomain;

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processFormRequest(pageContext, webBean);

 if (pageContext.getParameter("Go") != null)
 {
  DataObject fileUploadData = (DataObject)pageContext.getNamedDataObject("MessageFileUpload");
  String fileName = null;
             
  try
  {
   fileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
  }
  catch(NullPointerException ex)
  {
   throw new OAException("Please Select a File to Upload", OAException.ERROR);
  }

  BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);
  try
  {
   OAApplicationModule oaapplicationmodule = pageContext.getRootApplicationModule();
   Serializable aserializable2[] = {uploadedByteStream};
   Class aclass2[] = {BlobDomain.class };
   oaapplicationmodule.invokeMethod("ReadExcel", aserializable2,aclass2);
  }
  catch (Exception ex)
  {
   throw new OAException(ex.toString(), OAException.ERROR);
  }
 }
}

 

 

Write Following Code in ImportxlsAMImpl.java

import java.io.IOException;
import java.io.InputStream;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.jbo.Row;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.server.OAViewObjectImpl;
import oracle.jbo.domain.BlobDomain;

public void createRecord(String[] excel_data)

 OAViewObject vo = (OAViewObject)getImportxlsVO1();  
       
 if (!vo.isPreparedForExecution())  
 {
  vo.executeQuery();   
  }   
                
 Row row = vo.createRow();
 try
 {
  for (int i=0; i < excel_data.length; i++)
  {
   row.setAttribute("Column" +(i+1) ,excel_data[i]);
  }
 }
 catch(Exception e)
 {
  System.out.println(e.getMessage());
  }

 vo.insertRow(row);
 getTransaction().commit();

   

public void ReadExcel(BlobDomain fileData) throws IOException
{
 String[] excel_data  = new String[5];
 InputStream inputWorkbook = fileData.getInputStream();
 Workbook w;
       
 try
 {
  w = Workbook.getWorkbook(inputWorkbook);
                   
  // Get the first sheet
  Sheet sheet = w.getSheet(0);
                   
  for (int i = 0; i < sheet.getRows(); i++)
  {
   for (int j = 0; j < sheet.getColumns(); j++)
   {
    Cell cell = sheet.getCell(j, i);
    CellType type = cell.getType();
    if (cell.getType() == CellType.LABEL)
    {
     System.out.println("I got a label " + cell.getContents());
     excel_data[j] = cell.getContents();
    }

    else if (cell.getType() == CellType.NUMBER)
    {  
     System.out.println("I got a number " + cell.getContents());
     excel_data[j] = cell.getContents();
    }

    else
    {
     excel_data[j] = "";
    }
   

  }
   createRecord(excel_data);
  }
 }
           
 catch (BiffException e)
 {
  e.printStackTrace();
 }
}

 

15. Congratulation you have successfully finished. Run Your page and Test Your Work

 

Consider Excel PRAJ_TEST.xls with following data --

 

 

 

Lets Try to import this data into DB Table --

 

 

 

 

 

Thursday Jul 05, 2012

OAF Page to Upload Files into Server from local Machine

1. Create a New Workspace and Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarFileUploadDemo

 

Automatically a new OA Project will also be created

 

Project Name -- FileUploadDemo

Default Package -- prajkumar.oracle.apps.fnd.fileuploaddemo

 

2. Create a New Application Module (AM)

Right Click on FileUploadDemo > New > ADF Business Components > Application Module

Name -- FileUploadAM

Package -- prajkumar.oracle.apps.fnd.fileuploaddemo.server

Check Application Module Class: FileUploadAMImpl Generate JavaFile(s)

 

3. Create a New Page

Right click on FileUploadDemo > New > Web Tier > OA Components > Page

Name -- FileUploadPG

Package -- prajkumar.oracle.apps.fnd.fileuploaddemo.webui

 

4. Select the FileUploadPG and go to the strcuture pane where a default region has been created

 

5. Select region1 and set the following properties --

   

Attribute

Property

ID

PageLayoutRN

AM Definition

prajkumar.oracle.apps.fnd.fileuploaddemo.server.FileUploadAM

Window Title

Uploading File into Server from Local Machine Demo Window

Title

Uploading File into Server from Local Machine Demo

   

6. Create messageComponentLayout Region Under Page Layout Region

Right click PageLayoutRN > New > Region

 

Attribute

Property

ID

MainRN

Item Style

messageComponentLayout

 

7. Create a New Item messageFileUpload Bean under MainRN

Right click on MainRN > New > messageFileUpload

Set Following Properties for New Item --

 

Attribute

Property

ID

MessageFileUpload

Item Style

messageFileUpload

 

8. Create a New Item Submit Button Bean under MainRN

Right click on MainRN > New > messageLayout

Set Following Properties for messageLayout --

 

Attribute

Property

ID

ButtonLayout

 

Right Click on ButtonLayout > New > Item