Making attachments as mandatory in OAF through CO Extension

The requirement is to stop the user from clicking next button in the TRAIN page if the attachment is not attached. The challenge i faced is attachment will not be committed to database till user click save button at the last step.

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

public void processFormRequest(OAPageCntext oapagecontext, OAWebBean oawebbean)
    {
      OAApplicationModule am = oapagecontext.getApplicationModule(oawebbean);
        super.processFormRequest(oapagecontext, oawebbean);
       
            if ("goto".equals(oapagecontext.getParameter(EVENT_PARAM)) || "NavBar".equals(oapagecontext.getParameter(SOURCE_PARAM))) //NavBar is used to get the even t from Train bar.
            {
            oapagecontext.writeDiagnostics(this," first IF condition passed " ,OAFwkConstants.STATEMENT) ;
            if(am != null)
            {   
                OAMessageAttachmentLinkBean localOAMessageAttachmentLinkBean = (OAMessageAttachmentLinkBean)oawebbean.findChildRecursive("Attachments");
               
                if(localOAMessageAttachmentLinkBean != null)
                {
                    String str = (String)localOAMessageAttachmentLinkBean.getAttributeValue(oapagecontext.getRenderingContext(), TEXT_ATTR);
                    oapagecontext.writeDiagnostics(this,"Printing str " +str ,OAFwkConstants.STATEMENT) ;
                    if ("None ".equals(str))
                    {
                            throw new OAException("Please Attach Required Supporting Documents To Process The Invoice.",OAException.ERROR);
                    }
                }
            }
            }

How to get value from Message Styled Text in OAF page

There are 4 ways to get the value of MessageStyled text from OAF page in CO.

First Option
=========
Get the handler of the VO which is used by that field.

import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.OARow;

OAViewObject vo = (OAViewObject)am.findViewObject("PosViewHeadersVO");
if(vo != null)
        {
            try
            {
                vo.reset();
                vo.next();
                OARow row = (OARow)vo.getCurrentRow();
                confirm_flag_vo = (String)row.getAttribute("Confirm");
            }
            catch(Exception e)
            {
                oapagecontext.writeDiagnostics(this,"Exception occurred " + e.toString() ,OAFwkConstants.STATEMENT);
            }
        }

Second Option
==============

Get the handler of the bean and use that handler to get the value.

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

OAMessageStyledTextBean oaconfirmflag = (OAMessageStyledTextBean)oawebbean.findChildRecursive("PosConfirm");
String confirm_flag_bean = null;
confirm_flag_bean = (String)oaconfirmflag.getText(oapagecontext);

Third Option
===========

Get the handler of the item using getParameter() method.

String confirm_flag_getparam = null;
confirm_flag_getparam = (String)oapagecontext.getParameter("PosConfirm");

Fourth Option
==========
Write a SQL query in your CO and parse the SQL statement. I have used this method in my previous post.

API to add the responsibility to the user

bEGIN

fnd_user_pkg.addresp(username => 'VINOTH'

,resp_app => 'SYSADMIN'

,resp_key => 'SYSTEM_ADMINISTRATOR'

,security_group => 'STANDARD'

,description => 'Auto Assignment'

,start_date => SYSDATE - 1

,end_date => SYSDATE + 1000);

COMMIT;

END;

OM to AR and SC to AR Linking queries

OM to AR Table linkage
==================
SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    RCTLA.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR (OOHA.ORDER_NUMBER)
AND    order_number =  :p_order_number;

SC to AR
=======
SELECT * FROM APPS.RA_INTERFACE_LINES_ALL WHERE BATCH_SOURCE_NAME = 'OKS_CONTRACTS'
AND INTERFACE_LINE_ATTRIBUTE1 = :p_contract_number;

SC Billing details
==============
SELECT hdr.contract_number "Contract"
, hdr.contract_number_modifier "Modifier"
, hdr.id
, TO_CHAR( cont.creation_date, 'DD-MON-YYYY HH24:MI') "Creation Date"
, bill_action
, cont.btn_id "Billing Transaction ID"
, amount
, TO_CHAR( DATE_BILLED_FROM, 'DD-MON-YYYY' ) "Date Billed From"
, TO_CHAR( DATE_BILLED_TO, 'DD-MON-YYYY' ) "Date Billed To"
, obt.trx_date
, obt.trx_amount
, obt.trx_number
, obt.trx_amount
FROM apps.oks_bill_cont_lines cont
, apps.okc_k_lines_b line
, apps.okc_k_headers_b hdr
, apps.oks_bill_transactions obt
, APPS.oks_bill_txn_lines obtl
WHERE hdr.id = line.dnz_chr_id
AND cont.cle_id = line.id
AND HDR.CONTRACT_number =:p_contract_number
AND CONT.BTN_ID = OBT.ID (+)
AND OBTL.BTN_ID (+) = CONT.BTN_ID
ORDER BY CONT.CREATION_DATE;

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 :)

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

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.