DML Operations using Query based View Object

Recently there is a request in Oracle Forums/Spaces on how to save data into db tables using View Object(which is not based on Entity Object(EO)).

We already know that only EO based VOs can handle DML operations. If the query is complex, it may not be feasible to create EOs(and associations if any) and then base your VO on the EOs. In this case, it's better to create VO directly based on your complex query and then implement the DML operations by calling PL/SQL APIs from the VO. This way you can even do DML operations on more than one table. For this to achive Oracle has already provided a class oracle.apps.fnd.framework.server.OAPlsqlViewObjectImpl. By default, when we create any view object, it always extends oracle.apps.fnd.framework.server.OAViewObjectImpl which doesn't support any DML operations. In our case we need to create our View Object by extending OAPlsqlViewObjectImpl

Note: The class oracle.apps.fnd.framework.server.OAPlsqlViewObjectImpl has been deprecated. So implement this method only if you don't have alternative.

Below are the steps to achieve the desired result

1. Create VO(MyVO) based on query as usual and generate the MyVOImpl and MyVORowImpl.
2. Now go to MyVOImpl.java and change the extends to OAPlsqlViewObjectImpl. So your VOImpl should look like below

public class MyVOVOImpl extends OAPlsqlViewObjectImpl

3. Now go to MyVORowImpl.java and change the extends to OAPlsqlViewRowImpl

public class MyVORowImpl extends OAPlsqlViewRowImpl

4. MyVORowImpl has three methods to carry DML operations insertRow, updateRow, and deleteRow(inherited from OAPlsqlViewRowImpl).
5. Override insertRow(byte)/insertRow and call respective PL/SQL procedure to insert row into the table. As this is RowImpl class you will get all values using their getter methods. Check javadoc for information about these two methods.
6. Override updateRow(byte)/updateRow and call respective PL/SQL procedure to update row in table. As this is RowImpl class you will get all values using their getter methods.Check javadoc for information about these two methods.
7. Override deleteRow(byte)/deleteRow and call respective PL/SQL procedure to delete row in table. As this is RowImpl class you will get all values using their getter methods. Check javadoc for information about these two methods.

Please let me know if I miss any points. I am happy to add here if it's relevant.

How to run PTO Carryover process for one employee

In Oracle HRMS, if we run the PTO Carryover process, it runs for all employees. But as a developer you might need to run carryover for one employee or set of employees for testing purposes. Below are the steps to follow to achieve the same. This method involves changing Oracle supplied package so please make sure to revert back your changes once your testing is complete.

• Open the package PAY_US_PTO_CO_PKG and go to the function PTO_CARRY_OVER_FOR_PLAN.
• Go to the cursor CSR_GET_ASSIGNMENT and add a condition to filter based on assignment id as asg.assignment_id = <assignment_id> or asg.assignment_id IN (<assignment_id1>, <asignment_id2>, etc)
• Optional: Turn on Logging by inserting/updating a row into PAY_ACTION_PARAMETERS table with LOGGING parameter with a value G. It means that, when you run the PTO Carryover process, the log messages in the package are displayed as Concurrent program log messages which can be viewed by clicking on View Log button after the Carryover process is completed. You can even add more log messages in the package but make sure you remove them once your testing is complete.
• Run PTO Carryover process as normal but now it runs only for one employee. Check the log messages if you enable logging
• Don't forget to remove your changes once the testing is done.

FYI: Oracle has already published a Note#261617.1 on how to run carryover for one employee. In this note, they haven't mentioned about the package which needs to be changed but about a file where the package resides. So we need to open that file and get the package name and then follow the other instructions as mentioned in the note.

Please let me know if I miss any points. I am happy to add here if it's relevant.

Launching Web Page From Oracle APPS Forms

We already know that OAF page can be launched from Oracle Applications PUI. We can also launch a web page or custom built html from Oracle Applications PUI. Below steps explains you on how to achieve the same.

Note: In 11i, we used to achieve this functionality by creating a FND function of type SSWA plsql function that opens a new window (Kiosk Mode) or SSWA plsql function. But from R12, these types should not be used. So we have to follow below procedure to launch a web page or custom html from Oracle Applications  PUI.

1) Create an .htm file calling the external link, for instance "xxpage.htm". 

<html>
<head>
<meta http-equiv="Refresh" content="0; URL=http://google.com">
</head>
</html>

or a custom html content

<html>
<body> 
<b>Our custom page</b>
</body>
</html>

2) Put this file under $OA_HTML directory. Check the permissions of the file

3) Go to System Administrator responsibility
4) Define a new fnd function as follows
- Enter function name and user function name 
- Function Type : 'JSP Interoperable with OA'
- HTML Call : xxpage.htm  

5) Search for the FND menu where you want to add access to our custom html. Add the newly created function to the menu with a proper name(displays in the navigator).

6) Clear cache and launch the page from Navigator. It's inevitable to clear the cache or else it raises a security exception upon launching the page.

Please let me know if I miss any points. I am happy to add here if it's relevant.

Assigning Enter Key to a button on OAF Page

In lot of web pages if we press enter, then it submits the page. Similarly we can also submit the page on pressing Enter key in our Custom OAF pages. If there are more than one button then we can assign Enter key to one of the button. This way the user can submit the page by just pressing on Enter key. Below code gives you an idea on how to assign enter key to a button(page can contain any number of submit buttons). It's obvious that we can only assign Enter key to one button in a page. So when the user presses enter key, the page gets submitted and execute the respective code.

Note: When we implement Search bean in OAF, by default Enter key is assigned to Go button in the search bean. So when the user clicks on enter search gets executed automatically.

import java.util.Hashtable;
import oracle.apps.fnd.framework.webui.OABoundValueEnterOnKeyPress;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
    OAPageLayoutBean page = pageContext.getPageLayoutBean();

    Hashtable applyParams = new Hashtable(); 
    /*There is a button in the page with id 'Apply' and
    we are assigning Enter key to this Apply button.*/
    applyParams.put ("Apply", "VAL1");
    page.setAttributeValue(
          OAWebBeanConstants.ON_KEY_PRESS_ATTR,
          new OABoundValueEnterOnKeyPress(pageContext,
                "DefaultFormName", // enclosing form name
                applyParams, // request parameters
                false, // client unvalidated
                false)); // server unvalidated
}

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

    /*When the user clicks on Enter key then the page 
    consider it like Apply button is pressed.*/
    if (pageContext.getParameter("Apply") != null)
    {
       --Do your work here 
    
    }
}

Please let me know if I miss any points. I am happy to add here if it's relevant.

Setting Oracle APPS(E-Biz) context in Stored Procedure(PL/SQL)

What is e-biz context?

While viewing or updating any data in Oracle APPS we need to login. But sometimes developers view/update data from stored procedures (PL/SQL). So in order to get the accurate results, it’s always better to mimic the db that we are accessing from a legitimate FND user with a proper responsibility. So to make sure the db is mimicked, we need to set the e-biz context (Setting FND User and Responsibility) before accessing any data.

Why do we need to set the e-biz context or what are the uses of setting e-biz context?

Most of the time while calling Oracle supplied APIs or custom APIs or even accessing some views, we might need to set the e-biz context. Below are the some of the reasons why we need to set the context.

• If the calling API is inserting, updating or deleting any table data, then the WHO columns will be populated properly. 
• Some of the results of the APIs are depending upon the security profile attached to the responsibility. For example in Oracle HRMS, based in the security profile attached to the responsibility, the data displayed will differ. So setting the context will ensue that we get correct data in return. 
• Selecting data from the secured views like PER_PEOPLE_V and PER_ASSIGNMENTS_V doesn't yield any results if the e-biz context is not set.

What are the mandatory things we need to set as part of e-biz context?

While accessing Oracle APPS, we need to login using FND User and then we need to select a responsibility before accessing any functionality. So just to replicate the same we need to set below values before accessing any data or calling any API.

• FND User
• FND Responsibility

How to set the e-biz context from Stored Procedure(PL/SQL)?

Oracle has already provided below API to set the e-biz context in your stored procedure.

FND_GLOBAL.APPS_INITIALIZE(USER_ID IN NUMBER,

                            RESP_ID IN NUMBER,

                            RESP_APPL_ID IN NUMBER,

                            SECURITY_GROUP_ID IN NUMBER DEFAULT 0,

                            SERVER_ID IN NUMBER DEFAULT -1)

How do I know what is my USER_ID and RESP_ID?

You can find USER_ID from FND_USER table if you know your FND user name using below SQL.

SELECT USER_ID FROM FND_USER WHERE USER_NAME = '<Your FND User Name>'

And you can find RESP_ID and RESP_APPL_ID from FND_RESPONSIBILITY table if you know your responsibility Key or name using below SQLs.

SELECT RESPONSIBILITY_ID, APPLICATION_ID FROM FND_RESPONSIBILITY WHERE RESPONSIBILITY_KEY = '<Responsibility Key>'

SELECT RESPONSIBILITY_ID, APPLICATION_ID FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_NAME = '<Responsibility Name>'

Finally how to set the e-biz context after getting all required values?

FND_GLOBAL.APPS_INITIALIZE is used for initializing the Oracle Applications context before calling any public  API's or custom build APIs in Oracle E-business suite.

Oracle HRMS uses security views to display the data based on the responsibility the user is accessing. If you are using same secured views in your pl/sql, then while calling those APIs it gives wrong values as the e-biz context is not set.

Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function. We are passing the mandatory parameters. You can pass other parameters depending on your requirement.

FND_GLOBAL.APPS_INITIALIZE(USER_ID=> <Your User ID>,

                                              RESP_ID  => <Your Responsibility ID>,

                                              RESP_APPL_ID => <Your Responsibility Application ID>);

Then call your your respective API or get the results from secured views. 

Please let me know if I miss any points. I am happy to add here if it's relevant.

Creating LOV in Oracle WebADI Spreadsheet

It's very helpful for users if we provide LOVs for some of the obvious columns in our custom Oracle WebADI Spreadsheets. In this topic, I am trying to explain on how to create LOV in Oracle WebADI spreadsheets.

My attempt to create LOVs from Oracle Self-Service pages(available in 12.1.3) was futile so I had to create LOV using API. Below API creates an LOV for Person ID field and it displays Employee Number and Name in the LOV to choose from and are return back to the spreadsheet as well.

BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV
  (P_APPLICATION_ID                 => <Application ID>,
   P_INTERFACE_CODE               => <Interface Code>,
   P_INTERFACE_COL_NAME    => 'P_PERSON_ID',
  --Interface Column Name from where the user double clicks to open the LOV and P_ID_COL is also passed to this spreadsheet column.
  P_ID_COL                                    => 'PERSON_ID',
  P_MEAN_COL                            => 'EMPLOYEE_NUMBER',
  P_DESC_COL                             => 'FULL_NAME',
  P_TABLE                                     => 'PER_PEOPLE_X',
  P_ADDL_W_C                            => NULL,
  P_WINDOW_CAPTION            => 'Select an employee', --LOV window caption
  P_WINDOW_WIDTH                => 600, --Window Width. Optional.
  P_WINDOW_HEIGHT               => 500, --Window Height. Optional
  P_TABLE_BLOCK_SIZE           => 10, --Number of records to be displayed in the LOV
  P_TABLE_SORT_ORDER         => 'ascending', --Order to be displayed in LOV
  P_USER_ID                                => <FND User ID>,
  --FND User ID of the person who is creating this LOV.
  P_TABLE_COLUMNS              => 'EMPLOYEE_NUMBER,FULL_NAME',
  --LOV fields to be displayed. Note person ID will not be displayed.
  P_TABLE_SELECT_COLUMNS => 'P_PERSON_ID', --EMPLOYEE_NUMBER(First value in P_TABLE_COLUMNS) is passed to this spreadsheet column and is displayed. PERSON_ID is passed to this same field as hidden value and is passed to the API when upload happens. If you want FULL_NAME etc also passed to the spereadsheet then mention that spreadsheet column(s) separated by commas 
  P_TABLE_COLUMN_ALIAS      => 'P_PERSON_ID',
  P_TABLE_HEADERS                   => 'Employee Number,Full Name',
  P_POPLIST_FLAG                       => 'N')

P_INTERFACE_COL_NAME	Interface Column Name where the LOV should appear. User double clicks this cell to open LOV.
P_TABLE	Table/View where the data comes to LOV. If you are planning to use SQL you can use but there is a limit of number of characters. So better create a view and use it if it's the case.
P_ID_COL	ID Column of the table.
P_MEAN_COL	Meaning Column of the table.
P_DESC_COL	Description Column of the table. If you want other fields to be displayed in the LOV add here by separating them with commas.
P_ADDL_W_C	Additional WHERE clause if any.
P_TABLE_COLUMNS	LOV fields to be displayed. Make sure before you mention the columns here, these should be included in any of P_ID_COL, P_MEAN_COL or P_DESC_COL.
P_TABLE_SELECT_COLUMNS	Mention spreadsheet column name(s). After selecting a value from LOV, the values mentioned in the parameter P_TABLE_COLUMNS will be passed to these columns. If it has only one column mentioned then the first value from P_TABLE_COLUMNS will be stored in the column.
P_TABLE_COLUMN_ALIAS	Has to explore this however setting it same as P_TABLE_SELECT_COLUMNS is working for me.
P_TABLE_HEADERS	Column Header name in LOV. Make sure to follow the same sequence as P_TABLE_COLUMNS. If you don't pass any value to this field then it uses the column names mentioned in P_TABLE_COLUMNS.
P_POPLIST_FLAG	N -> For LOV , Y -> For PopList

The LOV shows like below

Employee Number LOV

Please let me know if I miss any points. I am happy to add here if it's relevant.

Regards,
Peddi.