OAF: Calling AM method from CO in Oracle Application Framework

In this post I just want to put some sample code to show how to call the Application Module (AM) methods from the Controller (CO). The reason for this post with the basics is because, I still see in the OTN forum that AM methods are not called in the recommended approach.

In this post I have given example for methods with parameters, without parameters, couple of different types of parameters and methods with return types.

Sample 1:- AM method without parameter and return type.

AM Method:

 public void methodWithoutParams()  
 {  
   System.out.println("Dummy Method");  
 }  

CO Code:

 import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;  

 OAApplicationModuleImpl appModule = (OAApplicationModuleImpl)pageContext.getApplicationModule(webBean);  
 appModule.invokeMethod("methodWithoutParams");  

Sample 2 :- AM method with parameters and return type.

AM Method:

 public String methodWithParams(String[] strArray,String strVal)  
 {  
      System.out.println("strVal: " + strVal);  
      for(int i = 0; i < strArray.length; i ++)       
      {  
           System.out.println("strArray["+i+"]: " + strArray[i]);  
      }  
      return "Success";  
 }  

CO Code:

 import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;  
 import java.io.Serializable;  

 OAApplicationModuleImpl appModule = (OAApplicationModuleImpl)pageContext.getApplicationModule(webBean);  
 String[] strArray = {"1","2"};  
 String strVal   = "AJ";  
 Serializable[] params = {strArray, strVal};  
 Class[] paramTypes  = {String[].class, String.class};  
 String returnVal = (String)appModule.invokeMethod("methodWithParams", params, paramTypes);  
 System.out.println("returnVal : "+ returnVal);  

If we need to deal with non-Serializable parameters, refer : http://www.adftraining.com/blog/how-to-call-am-methods-from-controller-without-using-invokemethod

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: Some useful UNIX tips when working on Oracle Apps / Oracle eBusiness

In this post, I just want to put some useful UNIX commands when working on Oracle Apps/ eBusiness. This will be updated frequently with more commands.If you are reading this, you can contribute more commands in the comments and I can reference in this blog (obviously your name against it :)), which can help someone else as well.

#1. How to run .env file.
ENV files needs to be run to set the environment files for the session. I have seen some people try to run the file with 'sh' command and complains that the environment variables are not set. To run the env file, you just type (dot)(space)(filename).

 $ . <file_name>.env  

Related link: What are source environment file settings in Oracle Applications R12

#2. How to search for a file name.
You can search using the find command. 
Just type find(space)(starting directory)(space)-name(space)(file_name)

 find /home -name <file_name>  

More help: In Unix, what is the find command, and how do I use it to search through directories for files?

#3. How to find the version of a file.
You can use any of the below commands to get the header info of a file.

 $ adident Header <file_name>  

 $ strings -a <file_name> | grep Header  

Related links : 
Check Versions of Oracle Applications Components
Oracle Technical : How to find Files Versions and Locations

Feel free to point out if anything is missing/wrong in this blog.

PL/SQL: Sample code for using PRAGMA AUTONOMOUS_TRANSACTION

In this post I just want to post a sample code (which I use) to write debug in PL/SQL using PRAGMA AUTONOMOUS_TRANSACTION.

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction. Read more

 --create table  
 create table xxaj_debug (id number, msg varchar2(4000), date_stamp date);  
 --create sequence  
 create sequence xxaj_seq start with 1 increment by 1;  
 --create procedure  
 create or replace procedure xxaj_proc ( msg varchar2)  
 is  
      PRAGMA AUTONOMOUS_TRANSACTION;  
 begin  
      insert into xxaj_debug values (xxaj_seq.nextval, msg, sysdate);  
      commit;  
 end;  

Feel free to point out if anything is missing/wrong in this blog.

Oracle Database Developer Choice Awards: the votin...

Steven Feuerstein on Oracle PL/SQL: Oracle Database Developer Choice Awards: the votin...: The Oracle Database Developer Choice Awards celebrate and recognize technical expertise and contributions in the Oracle Database commun...

Click here to vote : The Oracle Database Developer Choice Awards

OAF: OAMessageChoice / poplist / dropdown not refreshing the data

Sometimes when we use the MessageChoice /poplist / dropdown in our page and we want to see different data everytime the page loads. The example screnarios are 

• Data has to be different based on the user logged in.
• Data has to be different based on the responsibility used to access the page. 
• Dropdown has to list the newly added data from the previous session or by a different user just before this page is accessed.

The poplist data object that OA Framework creates the first time, is cached in the JVM and reused until you explicitly disable caching. The caching is enabled by default. So, if you are looking for a fix for data not refreshing in your dropdown/poplist, you need to disable the poplist caching explicitly using the below API in processRequest() method of your controller.

poplistBean.setPickListCacheEnabled(false);

Example usage :

 import oracle.apps.fnd.framework.webui.beans.message.OAMessageChoiceBean;  

 OAMessageChoiceBean poplistBean = (OAMessageChoiceBean)webBean.findChildRecursive("<MessageChoice_Field_Name>");  
 poplistBean.setPickListCacheEnabled(false);  

From the Dev Guide :

You should explicitly disable poplist caching in the following cases:

  1. When you use the view object row getter method to encrypt the value for a poplist.

The poplist data object that OA Framework creates the first time is cached in the JVM and reused until you explicitly disable caching. When you encrypt the value of a poplist in the view object row getter method, the encrypt method uses the current Oracle E-Business Suite user session ID as a key to encrypt the poplist value. When you decrypt the values in their controller to further process the poplist, the decrypt method will also use the current Oracle E-Business Suite user session ID as the key to decrypt the value.

Since the poplist data object is cached at a JVM level, the data object will always contain the cached value with the encryption done with the first Oracle E-Business Suite user session ID and will reuse that value to render the poplist. Since the decrypt method also uses the current Oracle E-Business Suite user session ID as the key and the encrypted value that is returned is a cached value, the decrypt method will fail and return a null value. To avoid this scenario, you should always disable poplist caching when you encrypt the poplist values in their view object row getters.

2. When the poplist data (the query string or WHERE clause parameters of the poplist view object) keeps changing with user or thread-specific context.

Only data that can be shared across user threads within the JVM should be stored in the cache. Otherwise, the cache size increases redundantly, thereby increasing memory consumption.

Feel free to point out if anything is missing/wrong in this blog.

OAF: How to display Help link in custom OAF Page

Recently I saw a question in OTN Forum asking how to add the help link in custom OAF Page. Since I couldn't find much available for this, I am adding a post for achieving that.

Highlevel steps:

1. Create the .htm file (Need to have a specific structure as per the sample given below)

2. Upload the file using the Help Upload functionality.

3. Creating the target using Help Builder (I think this is optional if you are creating one help page alone)

4. Specify the Help Target for the PageLayoutRN of the Page.

Thanks to Chetan Gowda

1. Create the .htm file.

Create a .htm file with the structure similar to the sample one below:

Sample structure is given below:

 <HTML LANG="en-US" DIR="LTR">  
      <HEAD>   
           <TITLE>AJ Test Page </TITLE>   
           <LINK REL="stylesheet" HREF="../fnd/iHelp.css">  
      </HEAD>  
      <A NAME="FND_AJTEST_HLP_TestPG"></A>   
      <CENTER><H2><B>AJ Test Heading</B></H2></CENTER>   
      <H3><B>AJ Test Content</B></H3>   
      <BODY>  
           This is the help page for the AJ Test PG.  
      </BODY>  
 </HTML>  

Note:- <A NAME="FND_AJTEST_HLP_TestPG"></A>. This is used to specify the target. 

2. Upload the file using the Help Upload functionality.

Navigate to System Administration --> Help Administration --> Help Upload.

Specify the parameters as below :

     Action                        : Single File Upload

     Desktop File              : <Choose the .htm file created before> 

     Application                : FND  (Specify the application for which the file is getting created)

     Customization Level : 101 (anything above 100)

3. Creating the target using Help Builder.

Navigate to System Administration --> Help Administration --> Help Builder.  

     Create a new Node by clicking on the New Node icon.

  Prompt: AJ Test
  Data: @fnd_ajtest_hlp_testpg
  Node Application : FND

     Click on apply.

     Change the Type to 'Document' and click on 'Apply'. You can now click on the 'View' button to view the page.

4. Specify the Help Target for the PageLayoutRN of the Page.

Specify the Help Target Appl ShortName and the Help Target for the pageLayoutRN. Help Target Appl ShortName should be give as the application short name and the Help Target should be the one specified (FND_AJTEST_HLP_TestPG) in the anchor tag in the .htm file uploaded.

Now run the page(I was using 12.2.4 Jdev against 12.1.3 environment, so the screen shots looks bit different from the above images):

Click on the help link.

Have a look at the section "Oracle E-Business Siute Help" in the "System Administrator's Guide":
http://docs.oracle.com/cd/E18727_01/doc.121/e12893.pdf

Also refer the section "Help Global Button" in the Developer Guide:
Oracle Application Framework Developer's Guide Release 12.1.3 (Doc ID 1107973.1)


Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: How to delete a DFF Context

You cannot delete a DFF Context from the front-end. You can only disable it.
Assume you have created one by mistake and you want to delete that.How to do that ?

• The segments added to the context can be deleted from the front-end. Delete those first.
• You can use fnd_descr_flex_contexts_pkg.delete_row to delete the context.

 procedure DELETE_ROW (  
  X_APPLICATION_ID in NUMBER,  
  X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,  
  X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2  
 );  

Ex:-

 begin  
  fnd_descr_flex_contexts_pkg.delete_row(0,'FND_COMMON_LOOKUPS','TEST');  
 end;  

You need to call commit after the above call.

Also make sure that you UnFreeze and Freeze the DFF definition and compile the DFF.

Script to check from backend whether it is deleted:

 select * from FND_DESCR_FLEX_COLUMN_USAGES where application_id = 0 and descriptive_flex_context_code = 'TEST';  
 select * from FND_DESCR_FLEX_COL_USAGE_TL  where application_id = 0 and descriptive_flex_context_code = 'TEST';  
 select * from FND_DESCR_FLEX_CONTEXTS      where application_id = 0 and descriptive_flex_context_code = 'TEST';  
 select * from FND_DESCR_FLEX_CONTEXTS_TL   where application_id = 0 and descriptive_flex_context_code = 'TEST';  

Related Links : Delete a Descriptive Flexfield Context Value

Feel free to point out if anything is missing/wrong in this blog.

Oracle Apps: How to set the required fields to Yellow color in Professional User Interface (Forms screens)

Recently I saw a thread in OTN which talks about the profile option FND: Indicator Colors which is used to set the coloring in the Forms screens. So thought of adding that to my post so that it will be useful for someone.

OTN Thread: Unable to see required/mandatory parameters in a Concurrent request

Reference: 
Oracle E-Business Suite Accessibility Features
Profile Options in Oracle Application Object Library

FND: Indicator Colors

The default for this profile option is null, which means "Yes." When this profile option is set to Yes:

• Required fields are displayed in yellow.
• Queryable fields are displayed in a different color while in enter-query mode.
• Fields that cannot be entered (read-only) are rendered in dark gray.

Users can see and update this profile option.

Level	Visible	Allow Update
Site	No	No
Application	No	No
Responsibility	No	No
User	Yes	Yes

The internal name for this profile option is FND_INDICATOR_COLORS.

When the profile option is set to 'No'.

When the profile is set to null or 'Yes'

This profile value also reflects on the Concurrent Request screens as shown in the above images.

Feel free to point out if anything is missing/wrong in this blog.

OAF: How to add logging / debug messages in Oracle Application Framework

In this post I will explain how to write log / debug statements in Controller (CO), Application Module Implementation class(AM), ViewObject and ViewObject Row Implementation class (VO) and Entity Object Implementation class (EO) in OA Framework.

OA Framework has a method writeDiagnostics() which can be used from anywhere in the above mentioned classes. This methods are published by oracle.apps.fnd.framework.webui.OAPageContext and oracle.apps.fnd.framework.server.OADBTransaction (for example, if you want to make logging calls in a UI controller, use OAPageContext. If you want to make logging calls from an application module, use OADBTransaction).  Below give are the example to show how to call these methods in different places.

To view log mesages, select the Diagnostics global button from any page (this global button is configured as part of the standard global menu added to each page; the display of this menu item is controlled by the profile option FND: Diagnostics (FND_DIAGNOSTICS)) 

Diagnostics can be enabled at various levels:

• Show Log - directs you to the Oracle Applications Manager where you can view a "snapshot" of your Oracle E-Business Suite system.
• Show Log on Screen - allows you to specify a log level and display Java log messages for a particular HTTP Request-Response at the end of the current page. For additional information about this feature, refer to the section titled "Using Logging to Screen" in the Oracle Application Framework Developer's Guide chapter called How to Configure Logging of the Oracle E-Business Suite Supportability Guide, available on the Applications Release 12 Online Documentation CD.
• Set Trace Level - displays the Set Trace page where you can specify the level of information to collect in a database trace. 
• Show Pool Monitor - displays the Application Pool Monitor where you view different aspects of runtime and configuration information about the JVM.

Here we are discussing only about the option "Show Log on Screen".

The debug messages can be written in various level:

• Statement (1)
• Procedure(2)
• Event(3)
• Exception (4)
• Error (5)
• Unexpected(6)

Depends on which options you choose, the screen will display all the debug messges written on that level or above. I prefer to write the debug messages at the exception level, so if I select the option Exception(4) in the screen, i can see only my debug messages.(most of the standard logging statements are written at Statement(1) level.)

• From a Controller

 if(pageContext.isLoggingEnabled(OAFwkConstants.EXCEPTION))  
 {  
      pageContext.writeDiagnostics(this,"[TestCO]processRequest():- <Log Message Here!!!!!!>",OAFwkConstants.EXCEPTION);  
 }  

• From AM Impl class

 if(this.isLoggingEnabled(OAFwkConstants.EXCEPTION))   
 {  
      this.writeDiagnostics(this,"[TestAMImpl]testMethod():- <Log Message Here!!!!!!>",OAFwkConstants.EXCEPTION);  
 }  

• From VOImpl class

 if(this.isLoggingEnabled(OAFwkConstants.EXCEPTION))   
 {  
      this.writeDiagnostics(this,"[TestVOImpl]testMethod():- <Log Message Here!!!!!!>",OAFwkConstants.EXCEPTION);  
 }  

• From VORow Impl class

 import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;  

 OAApplicationModuleImpl appModule = (OAApplicationModuleImpl)this.getApplicationModule();  
 if(appModule.isLoggingEnabled(OAFwkConstants.EXCEPTION))   
 {  
      appModule.writeDiagnostics(this,"[TestVORowImpl]testMethod():- <Log Message Here!!!!!!>",OAFwkConstants.EXCEPTION);  
 }  

• From EOImpl class

 import oracle.apps.fnd.framework.server.OADBTransaction;  

 OADBTransaction transaction = this.getOADBTransaction();  
 if(transaction.isLoggingEnabled(OAFwkConstants.EXCEPTION))   
 {  
      transaction.writeDiagnostics(this,"[TestEOImpl]testMethod():- <Log Message Here!!!!!!>",OAFwkConstants.EXCEPTION);  
 }  

If you are running from Jdeveloper, you could just use the SOP statements.

 System.out.println("[TestCO]processRequest():- <Error Message Here!!!!!!>");  

This is a custom method which I normally use, which is helpful when you want to change the debug levels. This is used in CO, if you want you can write something similar in AM :)

 public void writeLog(OAPageContext pageContext, String message)   
 {  
      if(pageContext.isLoggingEnabled(OAFwkConstants.EXCEPTION))  
      {  
           pageContext.writeDiagnostics(this,message,OAFwkConstants.EXCEPTION);  
      }  
      System.out.println(message);  
 }  

Related Links: Logging in OAF Pages – A Technical Note!



Feel free to point out if anything is missing/wrong in this blog.

PL/SQL: Some helpful SQL queries -1

In this post I am just putting some helpful SQL queries which will be useful at some point in your SQL / PLSQL Programming life. Most of these are taken from the OTN Forum answers or from my project experiences.

#1. How to convert Rows into Columns. 

Source Data

Expected Output

Using Pivot:

 with t as  
 (select 1 id,'MONDAY' day,'10:00' start_time,'12:00' end_time from dual  
 union  
 select 1,'TUESDAY','11:00','12:00' from dual  
 union  
 select 1,'WEDNESDAY','09:00','12:00' from dual  
 union  
 select 2,'MONDAY','08:00','10:00' from dual  
 union  
 select 3,'TUESDAY','06:00','09:00' from dual)  
 select * from t  
 pivot   
 (  
   max(start_time) as start_time, max(end_time) as end_time  
   for day in ('MONDAY' "Mon",'TUESDAY' "Tue",'WEDNESDAY' "Wed")  
 )  
 order by 1;  

#2. How to convert Columns into Rows. 
Source Data

Expected output

For versions prior to 11g. If you want null values also, then remove the where clause.

 with t as   
 (select '1' column1, '2' column2, null column3, '4' column4, null column5   
  from dual)  
 select decode(val,1,column1,2,column2,3,column3,4,column4,column5) display_val  
 from  t  
 cross join (select level val from dual connect by level <= 5)  
 where decode(val,1,column1,2,column2,3,column3,4,column4,column5) is not null;  

For 11g or above - using PIVOT/UNPIVOT. If you don't want the null values, remove 'include nulls'.

 with t as   
 (select '1' column1, '2' column2, null column3, '4' column4, null column5   
  from dual)  
 select val disp_val   
 from t   
 unpivot include nulls (val for col in(column1, column2, column3,column4,column5));   

Refer:
PIVOT and UNPIVOT Operators in Oracle Database 11g Release 
Pivot and Unpivot

#3. How to get the first 100 Numbers in a query.

 select rownum val  
 from dual  
 connect by rownum <= 100;  

#4. How to get all the dates between 2 dates.
I have used 01-Jan-2015 as the start date and 31-Jan-2015 as the end date.

 select (to_date('01-Jan-2015','DD-MON-YYYY') + level - 1) date_val  
 from dual  
 connect by level <= (to_date('31-Jan-2015','DD-MON-YYYY') - to_date('01-Jan-2015','DD-MON-YYYY') + 1);  

#5. How to remove duplicates from one column.
Source Data

Expected Output

Normally this is job of the client / front end tool. But some reason you want this output in the query itself, you can use the below query.

 with t as  
 (select 'ABC Company' company,'India' branch,'11111' contact from dual union all  
  select 'ABC Company','Australia','22222'  from dual union all  
  select 'ABC Company','America'  ,'33333'  from dual union all  
  select 'XYZ Company','Australia','000000' from dual union all  
  select 'XYZ Company','America'  ,'111111' from dual  
 )  
 select  (case when row_number() over (partition by company order by branch desc) = 1  
              then company  
         end) company  
        ,branch  
        ,contact  
 from t  

#6. How to get the cumulative total.
Source Data

Expected Output

The data has to be sorted with the ITEM_NO and then with the Date and the Cumulative sum for each Item. 

 with t as (  
  select 1 item_no,to_date('2015-10-30','YYYY-MM-DD') inv_date ,30  amt from dual union  
  select 2 item_no,to_date('2015-10-23','YYYY-MM-DD') inv_date ,100 amt from dual union  
  select 2 item_no,to_date('2014-10-23','YYYY-MM-DD') inv_date ,70  amt from dual union  
  select 1 item_no,to_date('2014-09-10','YYYY-MM-DD') inv_date ,50  amt from dual union  
  select 1 item_no,to_date('2013-08-17','YYYY-MM-DD') inv_date ,40  amt from dual  
 )  
 select item_no,to_char(inv_date, 'DD-Mon-YYYY') invoice_date,amt amount  
   ,sum(amt) over (partition by item_no order by item_no,inv_date) as cumulative_amount  
 from t  
 order by item_no, inv_date;  

Feel free to point out if anything is missing/wrong in this blog.