Tag Archives: Consistency

Oracle EBS – Consistency and Error Handling between OA Framework (OAF) and Oracle Database

Error handling is one of most important titles for consistent business logic in enterprise software development lifecycle. Also consistent and synchronous information transfer among architectural tiers such as Database and Web UI is key value for perfect functionality. In this case, we will practice small part of this consistency lifecycle and error handling between Oracle Application Framework (OAF) and Oracle E-Business Suite Database.

In this example , we are only focussing data consistency so we will check only the connection and binding layer between DB and Web UI in application module implementation class . Scenerio is creating Purchase Order Requisition by submiting requisition import concurrent using PO Requisition Interface’s from a custom OAF page .

We will use custom PL/SQL package which is containing different procedures associated with PO Requisition Import .This DB package includes some functionalities such as ; validating data with custom business rules , importing data to po_requisitions_interface_all table , submitting concurrent and error handling in every stage of process. So, In any stage of code , if there is an validational-programatical error, process breaks , inserts errored/unvalidated data with a description into custom error table then commits transaction in diffrent db session ( via PRAGMA AUTONOMOUS_TRANSACTION in insert_err_table procedure ) . Important thing is there must be no rollback in any stage of our xxxl_customapp_process_pkg package. there must be a commit at the end of insert_interface_table and submit_program procedures  (commit after interface insert is the key, you can not submit concurrent without that , of course if there is no handled error)

We are not focussing details of PO Requisition Import process in this time . So let’s check our “only” custom PL/SQL Package Spec :

CREATE OR REPLACE PACKAGE APPS.XXXL_CUSTOMAPP_PROCESS_PKG is

   
    procedure insert_interface_table(  p_batch_id in number ,
                                       p_po_header_segment1 in varchar2  ,
                                       p_po_line_location_id in number ,
                                       p_inventory_item_id in number ,
                                       p_price in number ,
                                       p_masraf_merkezi varchar2,
                                       p_from_organization_id in number ,
                                       p_vendor_id in number,
                                       p_ilave_km in number,
                                       p_process_type in varchar2,
                                       p_po_line_id in number ,
                                       p_sefer_no in varchar2 ,
                                       x_return_status out varchar2
                                    ) ;
    procedure insert_req_dist_interface (p_batch_id in number , --re_interface icin
                                         p_dist_sequence_id in number , --req_interface e baglamak icin
                                         p_inventory_item_id in number ,
                                         p_org_id in number ,
                                         p_process_type in varchar2 ,
                                         p_sefer_no in varchar2 ,
                                         p_quantity in number,
                                         x_is_inserted out boolean,
                                         x_return_status out varchar2
                                        );
    procedure submit_program(p_batch_id in number ,
                             p_sefer_no in varchar2 ,
                             x_return_status out varchar2
                            ) ;
    procedure insert_err_table  (batch_id in number,
                                 p_interface_table_name in varchar2,
                                 p_error_message_custom in varchar2 ) ;

   
end ;
/

And this is our application module implementation class ;

package xxxl.oracle.apps.xxxl.customapp.server;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import com.sun.java.util.collections.HashMap; 
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;  

public class XXXLcustomappAMImpl extends OAApplicationModuleImpl { 
    public XXXLcustomappAMImpl() {
    } 
    public static void main(String[] args) {
        launchTester("xxxl.oracle.apps.xxxl.customapp.server", /* package name */
      "XXXLcustomappAMLocal" /* Configuration Name */);
    }     
    
 //   public static boolean is_errored ;
 //   public static int batch_id ;
    
    public HashMap processReqRel () throws SQLException{
        HashMap retarr = new HashMap(2) ;
        retarr.put("xx_is_errored","N")  ; 
        retarr.put("xx_batch_id","") ;
       
        XXXLcustomappResultsVOImpl vo = getXXXLcustomappResultsVO1() ; 
        
        String sefer_no = "";
        
        int rowcount = vo.getRowCount() ; 
        if (rowcount>0) {
            String batch_id = getOADBTransaction().getSequenceValue("PO_REQUISITIONS_INTERFACE_S")+"";
            retarr.put("xx_batch_id",batch_id) ;
            
            vo.setRangeSize(rowcount) ; 
            vo.setRangeStart(0) ; 
            for (int i=0 ; i<rowcount ;i++) {
                XXXLcustomappResultsVORowImpl eferow = (XXXLcustomappResultsVORowImpl)vo.getRowAtRangeIndex(i) ; 
                if (eferow.getSelectedFlag().equals("Y")  ) {
                   
                    sefer_no = eferow.getSeferNo() ;
                
                    String statement = "begin\n" + 
                    "    XXXL_customapp_PROCESS_PKG.insert_interface_table(p_batch_id=>"+batch_id+" ,\n" + 
                    "                                        p_po_header_segment1=>"+eferow.getBpoNo()+" ,  \n" + 
                    "                                        p_po_line_location_id=>"+eferow.getPoLineLocationId()+" ,  \n" + 
                    "                                        p_inventory_item_id=>"+eferow.getInventoryItemId()+" ,  \n" + 
                    "                                        p_price=>"+eferow.getPrice()+" ,  \n" + 
                    "                                        p_masraf_merkezi=>'"+eferow.getMasrafMerkezi()+"' ,  \n" + 
                    "                                        p_from_organization_id=>"+eferow.getFromOrgId()+" ,  \n" + 
                    "                                        p_vendor_id=>"+eferow.getVendorId()+" ,  \n" + 
                    "                                        p_ilave_km=>"+eferow.getIlaveKilometre()+" ,  \n" + 
                    "                                        p_process_type=>'"+eferow.getProcessType()+"' ,  \n" + 
                    "                                        p_po_line_id=>"+eferow.getPoLineId()+" ,  \n" + 
                    "                                        p_sefer_no=>'"+sefer_no+"' ,  \n" + 
                    "                                        x_return_status=>?) ; \n" + 
                    "end ; " ;
                    CallableStatement cs = getOADBTransaction().createCallableStatement(statement,0) ; 
                    cs.registerOutParameter(1,Types.VARCHAR);  
                    cs.execute();        
                    String return_status = cs.getString(1);
                    cs.close();
                    if (!return_status.equals("S")) {
                        retarr.put("xx_is_errored","Y");
                        return retarr; 
                    }
                }
            }
            
            if (retarr.get("xx_is_errored").equals("N")) {
                String statement_submit = "begin\n" + 
                "    XXXL_customapp_PROCESS_PKG.submit_program(p_batch_id=>"+batch_id+" ,\n" +
                "                                         p_sefer_no=>'"+sefer_no+"' ,  \n" + 
                "                                         x_return_status=>?) ; \n" + 
                "end ; " ;
                CallableStatement cs_submit = getOADBTransaction().createCallableStatement(statement_submit,0) ; 
                cs_submit.registerOutParameter(1,Types.VARCHAR);  
                cs_submit.execute();        
                String return_status_submit = cs_submit.getString(1);
                cs_submit.close();
                System.out.println("return_status_submit :"+return_status_submit);
                if ( return_status_submit.equals("E") )
                {
                    retarr.put("xx_is_errored","Y");
                } else retarr.put("xx_is_errored","N");
                
            } 
        }
        return retarr  ; 
    }
    public void initErrorTable(String p_batch_id ) {
        XXXLcustomappImportErrorsVOImpl vo = getXXXLcustomappImportErrorsVO1() ;
        vo.setWhereClauseParams(null);
        vo.setWhereClause(null);
        vo.setWhereClauseParam(0,p_batch_id);
        vo.executeQuery();
    }
    
      

    /**Container's getter for XXXLcustomappResultsVO1
     */
    public XXXLcustomappResultsVOImpl getXXXLcustomappResultsVO1() {
        return (XXXLcustomappResultsVOImpl)findViewObject("XXXLcustomappResultsVO1");
    } 
    /**Container's getter for XXXLcustomappImportErrorsVO1
     */
    public XXXLcustomappImportErrorsVOImpl getXXXLcustomappImportErrorsVO1() {
        return (XXXLcustomappImportErrorsVOImpl)findViewObject("XXXLcustomappImportErrorsVO1");
    } 
}

As you see , we call PL/SQL package from OA Framework using callable staments in this class and bind return values (batch_id and is_errored) to a Hashmap in processReqRel method to use in our page controller classes and page navigation. In this application module implemention java class, we see that submitting requisition import concurrent is depending on error result(Y/N). Type of method is Hashmap so we can use instance of this class/method in page controller as Hashmap. Furthermore , the most important thing is we didn’t use any OADBtransation commit code in application module implementation class to be consistent! . Also we can handle unvalidated data and error descriptions with XXXLcustomappImportErrorsVO view object which is binded to our custom error database table . We can use this VO in OAF page components (such as tables) then can initilaze it with batch id parameter (initErrorTable method) that allows us to show content of validation and error handling process to end users.

A small part of Oracle EBS Consistency and Error Handling checking is finished. Be consistent!

Please like & share: