Wednesday, October 30, 2013

OAF Controller Extension

How to extend a standard controller CO?

I have a standard page in HRMS module and  have a business requirement to have additional check(Validations) to be performed before i click Transfer button. Transfer button Will have a standard controller.

How to find the controller Name?
Click About this page hyperlink displayed at bottom of the page where your controller has to be extended.

Note: FND: Diagnostics (FND_DIAGNOSTICS) profile option to Yes to render the "About this page" link at the bottom of each OA Framework-based page.

1)Unbook the Business components hierarchy
2)It will display all the BC4J components of the OA page.
3)Find the controller at $JAVA_TOP/oracle/apps/per/wpm/objectives/webui
4)Download the controllerCO.class file from "oracle/apps/per/wpm/objectives/webui" to your local system. Your controller will be applicable based on the application top.
5)Decompile the class file to get java file using CAVAJ utility.
6)Open the Original Java file and Start modify the processFormRequest( ) Method. I have highlighted the changes done to achieve the business requirement.

 package xx.oracle.apps.per.wpm.objectives.webui;

import com.sun.java.util.collections.HashMap;
import java.io.Serializable;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.form.OASubmitButtonBean;
import oracle.apps.per.selfservice.common.SSHRParams;
import oracle.jbo.domain.Number;
import oracle.apps.per.wpm.objectives.webui.*;
import oracle.apps.fnd.framework.OAFwkConstants;
import oracle.apps.fnd.framework.server.OADBTransaction; //to establish DB connection
import java.sql.CallableStatement; //to execute a package.procedure
import java.sql.Types;//used for type conversions and VARCHAR2



// Referenced classes of package oracle.apps.per.wpm.objectives.webui:
//            ObjectivesPageCO

public class XXSetObjectivesPageCO extends SetObjectivesPageCO
{

    public static final String RCS_ID = "$Header:" +
" ship $"
;
    public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header:" +
" ship $"
, "oracle.apps.per.wpm.objectives.webui");

    public XXSetObjectivesPageCO()
    {

           //Constructor;
    }


    public void processRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
    {
   
      super.processRequest(oapagecontext, oawebbean);
    //I need standard processRequest method to be executed first.
    }

   
    public void processFormRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
    {
      String p_return_msg  = null;
      if(oapagecontext.getParameter("MgrTransfer") != null) //This is the Transfer Button ID name
       {
        OADBTransaction tx = oapagecontext.getRootApplicationModule().getOADBTransaction();
    
                  String sql="BEGIN " +
                 "XXValidateProc("+
                                   "p_param_1=> :1,"+    
                                   "p_return_msg => :2);"+
                                   "       END;";           
          CallableStatement cStmt = (CallableStatement)tx.createCallableStatement(sql, 1);
          if(cStmt!=null) 
               {
                         try { 
                       
                         cStmt.setString(1,oapagecontext.getDecryptedParameter("
p_param_1"));      
                         cStmt.registerOutParameter(2,Types.VARCHAR);                            
                         cStmt.execute();                                                           
                         p_return_msg = cStmt.getString(2);                                                  
                         cStmt.close();                                                             
                         }
                        catch(Exception e)
                        {
                          throw new OAException("SBM: Error Calling XXValidateProc" + e.toString(),OAException.ERROR);
                        }
                                         
                }
        if(!p_return_msg.equalsIgnoreCase("No Errors")){
                  throw new OAException(p_return_msg,OAException.ERROR);                                           
        }
        else
        {
                         super.processFormRequest(oapagecontext, oawebbean);
        }       
       
     
    }
    else

   { //In case of any exception in the custom procedure, I want the standard controller method's to be called.
     super.processFormRequest(oapagecontext, oawebbean);
   }
     
    }
    }
   
Migration of OA Controller Extension

1)Create a folder under $JAVA_TOP xx/oracle/apps/per/wpm/objectives/webui;
2)Move the  XXSetObjectivesPageCO.java to the above location.
3)Compile the Java using javac XXSetObjectivesPageCO.java
4)Once compilation is done successfully.
5) Goto the page where the Controller is extended. Click Personalize Page Option.

Note: Personalize Self-service Defn (FND_CUSTOM_OA_DEFINTION) profile option should be enabled.


 Go to The regions where controller is got extended. Inherit the Controller location as

xx/oracle/apps/per/wpm/objectives/webui/XXSetObjectivesPageCO

Controller Extension is Done :)

Tuesday, March 5, 2013

SQL Loader Control File

SQL Loader is a Oracle provided utility to load the data or records from any external systems.
It can load .csv(Comma seperated values), Pipe delimited values.

In Oracle apps, there's a executable type called "SQL* Loader"
This kind of concurrent programs search for control file in $APPL_TOP/1.0.0/bin

Sample control file

OPTIONS (ERRORS=30000)
OPTIONS(SKIP=1)
   LOAD DATA
   INFILE '%1' --to get the file name as parameter from the concurrent program
   APPEND -- it can also be INSERT to insert into fresh tables
   INTO TABLE APPS.SERIAL_TABLE
   FIELDS TERMINATED BY ','  --Pipe delimited value (, or |)
   OPTIONALLY ENCLOSED BY '"'
   TRAILING NULLCOLS
  (
        SERIAL_NUMBER,
        MAX_COUNT,
        EXPIRY_DATE         "TO_DATE(TRIM(:EXPIRY_DATE),'MM/DD/YYYY')",
        TEMP_NAME         "REPLACE(:TEMPLATE_NAME,'|',',')",
        TEM_ID,
        EXT_DATE FILLER , -- FILLER option can be used above SQL loader Version 8i.
        --EXT_DATE          "TO_DATE(TRIM(:EXTRACT_DATE), 'MM/DD/YYYY HH24:MI')",
        --EXT_DATE         "TO_DATE(TRIM(:EXTRACT_DATE), 'MM/DD/YYYY HH:MI:SS PM')",
        BATCH_ID "trim(trailing ' ' from LTRIM(RTRIM(:BATCH_ID)))",
        STATUS CONSTANT 'NEW',
        CREATION_DATE SYSDATE,
        LAST_UPDATE_DATE SYSDATE,
        CREATED_BY     "-1",
        LAST_UPDATED_BY "-1"
  )


To Run the Control file
sqlldr apps/apps_pwd control=control_file_name.ctl

Wednesday, January 30, 2013

Phases of a Concurrent Program

When we run concurrent programs in Oracle applications. It follows the below phases to complete a program
1) Pending
2)Running
3)Completed
4)InActive

There are more status for each phases. Find it below

Phase Status Description
PENDING Normal Request is waiting for the next available manager

Standby Program to run request is incompatible with other program(s) currently running

Scheduled Request is scheduled to start at a future time or date.

Waiting A child request is waiting for its Parent request to mark it ready to run. For example, a request in a request set that runs sequentially must wait for a prior request to complete
RUNNING Normal Request is running normally
Paused Parent request pauses for all its child requests to finish running. For example, a request set pauses for all requests in the set to complete.

Resuming All requests submitted by the same parent request have completed running. The Parent request resumes running.

Terminating Request is terminated by choosing the Cancel Request button in Requests window
COMPLETED Normal Request completed successfully.

Error Request failed to complete successfully

Warning Request completed with warnings. For example, a request is generated successfully but fails to print

Cancelled Pending or Inactive request is cancelled by choosing the Cancel Request button in the Requests window.

Terminated Request is terminated by choosing the Cancel Request button in the Requests window.
Inactive Disabled Program to run request is not enabled. Contact your system administrator.

On Hold Pending request is placed on hold by choosing the Hold Request button in the Requests window.

No Manager No manager is defined to run the request. Check with your system administrator. A status of No Manager is also given when all managers are locked by run-alone requests.

Tuesday, January 29, 2013

Query to find Request group name from Concurrent Program Name

Here's the query to find the request group name by passing concurrent program name as parameter

SELECT
    FRG.REQUEST_GROUP_NAME,
    FRG.DESCRIPTION,
    CP.CONCURRENT_PROGRAM_ID,
    CP.CONCURRENT_PROGRAM_NAME,
    CPT.USER_CONCURRENT_PROGRAM_NAME,
    FRV.RESPONSIBILITY_NAME
FROM
    FND_REQUEST_GROUPS FRG,
    FND_REQUEST_GROUP_UNITS FRGU,
    FND_CONCURRENT_PROGRAMS CP,
    FND_CONCURRENT_PROGRAMS_TL CPT,
    FND_RESPONSIBILITY_VL FRV
WHERE
    FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
    AND FRGU.REQUEST_UNIT_ID = CP.CONCURRENT_PROGRAM_ID
    AND CP.CONCURRENT_PROGRAM_ID = CPT.CONCURRENT_PROGRAM_ID
    AND FRV.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID(+)
AND CPT.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX Conc Pgm Name';