Creating Custom Oracle Workflow via Oracle Approvals Management

Oracle Approvals Management (formerly known as AME product) has been released years ago. AME provides advanced control over the approval processes. Main advantages is, you can create custom types. These are the definitions such as rules, attributes, conditions, approval groups, action types.

Featured action types are paralel or serial voting. You can use approval types like consensus (all approvers have to approve to end approval process) , first responder wins (first approver finishes all process). And you can define these approvers dynamically, It supports:

  • HR Organization Hierarchy
  • Roles
  • Users
  • Most important one: your custom approval processes with your custom rules

For further  information about AME you can check these document : Metalink Note 336901.1 and Note 282529.1

For an example, we created a simple custom Workflow and PL/SQL package for AME rules and the notification process. Custom workflow contains two processes: a main and a subprocess;



In main Workflow process we call AME process with only a return type  approved or rejected.  we are focussing only AME sub-process in this practice . Now check two notifcations and what function look like:




These three functions use following package:

CREATE OR REPLACE package apps.xx_ame_approval_pkg is 
   procedure getAmeTransactionDetail (x_transaction_type in out varchar2 , x_application_id out number );
   procedure getNextApprover(itemtype        in varchar2,
                               itemkey         in varchar2,
                               actid           in number,
                               funcmode        in varchar2,
                               resultout       out NOCOPY varchar2) ;
   procedure updateAmeWithResponse(itemtype        in varchar2,
                            itemkey         in varchar2,
                            actid           in number,
                            funcmode        in varchar2,
                            resultout       out nocopy varchar2) ;


CREATE OR REPLACE package body apps.xx_ame_approval_pkg is 

     procedure getAmeTransactionDetail (x_transaction_type in out varchar2 , x_application_id out number )
     cursor c1 is 
            select fnd_application_id as application_id, 
                   transaction_type_id as transaction_type
              from ame_transaction_types_v
             where transaction_type_id = x_transaction_type;
            for crec in c1 loop
                x_application_id := crec.application_id ; 
            end loop ; 
     end ; 
     procedure getNextApprover(  itemtype        in varchar2,
                                 itemkey         in varchar2,
                                 actid           in number,
                                 funcmode        in varchar2,
                                 resultout       out NOCOPY varchar2) IS

      E_FAILURE                   EXCEPTION;
      l_transaction_id            number;
      l_next_approver             ame_util.approverRecord2;
      l_next_approvers            ame_util.approversTable2;
      l_next_approvers_count      number;
      l_approver_index            number;
      l_is_approval_complete      VARCHAR2(1);
      l_transaction_type          VARCHAR2(200);
      l_application_id            number;
      l_role_users  WF_DIRECTORY.UserTable;
      l_role_name                            VARCHAR2(320) ;
      l_role_display_name                    VARCHAR2(360)  ;

      l_all_approvers            ame_util.approversTable;

    cursor c1(p_user_name varchar2) is
           select papf.full_name from  fnd_user fu ,
                          per_all_people_f papf
                   where  fu.employee_id = papf.person_id
                     and  fu.user_name = p_user_name
                     and sysdate between papf.EFFECTIVE_START_DATE and nvl(papf.EFFECTIVE_end_DATE,sysdate+1)
                     and sysdate between fu.start_date and nvl(fu.end_date,sysdate+1) ;


    if (funcmode = 'RUN') THEN

      -- l_transaction_id :=  TO_NUMBER(itemkey);
       l_transaction_id:= wf_engine.getItemAttrNumber( itemtype =>  itemtype,
                                                         itemkey  => itemkey,
                                                         aname    => 'AME_TRANSACTION_ID' );

       l_transaction_type :=  wf_engine.getItemAttrText( itemtype =>  itemtype,
                                                         itemkey  => itemkey,
                                                         aname    => 'AME_TRANSACTION_TYPE' );

       getAmeTransactionDetail  ( l_transaction_type,l_application_id );

                                flagApproversAsNotifiedIn => ame_util.booleanTrue,
                                approvalProcessCompleteYNOut => l_is_approval_complete,

      l_next_approvers_count:=l_next_approvers.count ;

      if (l_is_approval_complete = ame_util.booleanTrue) then

  --  Incase of consensus voting method, next approver count might be zero but there will be pending approvers
      elsif (l_next_approvers.Count = 0) then

                                    approvalProcessCompleteYNOut => l_is_approval_complete,
                                    approversOut =>l_next_approvers);
      end if;

      l_next_approvers_count := l_next_approvers.Count;

      if (l_next_approvers_count = 0)  then
      end if;

      if (l_next_approvers_count > 0)  then
      end if;

      if (l_next_approvers_count = 1)  then

          wf_engine.SetItemAttrText( itemtype   => itemType,
                                  itemkey    => itemkey,
                                  aname      => 'APPROVER_USER_NAME' ,
                                  avalue     =>;

           wf_engine.SetItemAttrText( itemtype   => itemType,
                                  itemkey    => itemkey,
                                  aname      => 'APPROVER_DISPLAY_NAME' ,
                                  avalue     => l_next_approver.display_name);

           /*role name is user name here */
           for crec in c1( loop

               l_role_display_name:=crec.full_name ;

           end loop  ;
                        --  l_role_name:=


      end if;

      l_approver_index := l_next_approvers.first();

      while ( l_approver_index is not null ) loop

          l_role_users(l_approver_index):= l_next_approvers(l_approver_index).name ;

          l_approver_index :=;

      end loop;

	 wf_directory.CreateAdHocRole2( role_name => l_role_name
								  ,role_display_name => l_role_display_name
								  ,language => null
								  ,territory => null
								  ,role_description => 'AME ROLE DESC'
								  ,notification_preference => null
								  ,role_users => l_role_users
								  ,email_address => null
								  ,fax => null
								  ,status => 'ACTIVE'
								  ,expiration_date => null
								  ,parent_orig_system => null
								  ,parent_orig_system_id => null
								  ,owner_tag => null

      wf_engine.setitemattrtext(itemtype => itemtype,
                                itemkey => itemkey,
                                aname => 'RECIPIENT_ROLE',
                                avalue => l_role_name

	end if; -- run

      when others then
        -- The line below records this function call in the error
        -- system in the case of an exception.
    end getNextApprover;

    procedure updateAmeWithResponse(itemtype        in varchar2,
                                    itemkey         in varchar2,
                                    actid           in number,
                                    funcmode        in varchar2,
                                    resultout       out nocopy varchar2) is
      e_failure                   exception;
      l_transaction_id            number;
      l_nid                       number;
      l_gid                       number;
      l_approver_name             varchar2(240);
      l_result                    varchar2(100);
      l_ame_status                varchar2(20);
      l_original_approver_name         varchar2(240);
      l_forwardeein  ame_util.approverrecord2;
      l_transaction_type varchar2 (200 byte);
      l_application_id   number ;

     l_transaction_type :=  wf_engine.getItemAttrText( itemtype =>  itemtype,
													   itemkey  => itemkey,
													   aname    => 'AME_TRANSACTION_TYPE');
     getAmeTransactionDetail (l_transaction_type,
     l_transaction_id:= wf_engine.getItemAttrNumber( itemtype =>  itemtype,
                                                     itemkey  => itemkey,
                                                     aname    => 'AME_TRANSACTION_ID');
     if (funcmode = 'RUN') then

                      -- l_transaction_id :=  itemkey;
                       l_gid := wf_engine.context_nid;

                       select responder,notification_id
                              into  l_approver_name,l_nid
                         from wf_notifications
                        ---where group_id=l_gid
                        where group_id=l_gid
                          and status = 'CLOSED';

                       l_result := wf_notification.getattrtext(l_nid, 'RESULT');

                       if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval
                         l_ame_status := ame_util.approvedstatus;
                       elsif (l_result = 'REJECTED') then
                         l_ame_status := ame_util.rejectstatus;
                       else -- reject for lack of information, conservative approach
                         l_ame_status := ame_util.rejectstatus;
                       end if;
                       --set approver as approved or rejected based on approver response
                       ame_api2.updateapprovalstatus2(  applicationidin=>l_application_id,
                                                        approvalstatusin => l_ame_status,
                                                        approvernamein => l_approver_name);

     elsif  ( funcmode = 'TIMEOUT' ) then
           l_gid := wf_engine.context_nid;

           select responder,notification_id
             into l_approver_name,l_nid
             from wf_notifications
            where group_id=l_gid;
            --and status = 'CLOSED';

           l_result := wf_notification.getattrtext(l_nid, 'RESULT');

           if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval
              l_ame_status := ame_util.approvedstatus;
           elsif (l_result = 'REJECTED') then
             l_ame_status := ame_util.rejectstatus;
           else -- reject for lack of information, conservative approach
             l_ame_status := ame_util.rejectstatus;
           end if;
           --set approver as approved or rejected based on approver response
           ame_api2.updateapprovalstatus2(  applicationidin=>l_application_id,
                                            approvalstatusin => l_ame_status,
                                            approvernamein => l_approver_name);     
     elsif  ( funcmode = 'TRANSFER' ) then

            --l_transaction_id :=  itemkey;
            l_original_approver_name:= wf_engine.context_original_recipient;

              ame_api2.updateapprovalstatus2(applicationidin=>l_application_id ,
                        approvalstatusin => 'FORWARD',
                        approvernamein => l_original_approver_name,
          forwardeein => l_forwardeein );

     end if; -- run

     resultout:= wf_engine.eng_completed || ':' || l_result;

      when others then
    end updateAmeWithResponse;    


What we had done in package above is simple:

in getNextApprover : we used AME engine to find existing situation of our notification and approvers. Then we created a role for the current approver then set it as notification performer.

in updateAmeWithResponse : we updated current notification status using AME engine(ame_api2 package) and control if it is in timeout period or not , also if it is final approver of AME approver group.

Now we begin to start this WF process from this following package below for Orders which have holds (startApprovalWF procedure) .  We can log this process (insertLog procedure) . We set document type attribute to set a custom OAF page for notification content. We also have custom business rules like timeout for notification process just in case our approver doesn’t give a response to it . (getTimeoutDay function). In this case AME transaction type has the same name as workflow item type:

CREATE OR REPLACE package xx_ame_start_appr_pkg is

       procedure startApprovalWF(errbuff out varchar2,
                                 retcode out number,
                                 p_order_number in number,
                                 p_hold_type in varchar2 ,
                                 p_comment in varchar2
                                ) ;
       function getTimeoutDay (p_header_id in number) return number ;
       procedure insertLog (itemtype IN VARCHAR2,
                            itemkey IN VARCHAR2,
                            p_log in varchar2
                            ) ;
end ;


CREATE OR REPLACE package apps.xx_ame_start_appr_pkg is 

	/*This procedure starts our Approval WF via setting AME transaction types and
	required information (this can be also called as Concurrent Program)*/
	procedure startApprovalWF(errbuff out varchar2,
							  retcode out number,
							  p_order_number in number,  
							  p_hold_type in varchar2, 
							  p_comment in varchar2
							 ) is
	 cursor c1 is
		 select oh.header_id ,
		   from oe_order_holds_all ha ,
				oe_order_headers_all oh
		 where ha.header_id = oh.header_id 
		   and oh.ORDER_NUMBER= p_order_number
		   and ha.released_flag = 'N';

	 itemkey varchar2(240);
	 itemtype varchar2(8);
	 created_by_user_name varchar2(250);
	 l_seq number ;
	 is_entered boolean := false ;


		 select xxxl.xxxl_wf_item_key.nextval into l_seq from dual ;
		 itemkey := l_seq||'-'||p_hold_type;
		 itemtype :='YOUR CUSTOM WF NAME'; 
		 for crec in c1 loop

			 insertLog (itemtype ,
						itemkey ,
						'startApprovalWF procedure has been called');

			 wf_engine.createprocess (itemtype, 
			 itemkey ,

			 select user_name into created_by_user_name
		   	   from fnd_user
			  where user_id = fnd_profile.value('USER_ID');

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'CREATED_BY_USER_NAME',
			 avalue=>created_by_user_name) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'HOLD_TYPE',
			 avalue=>p_hold_type) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 avalue=>'YOUR CUSTOM WF NAME') ;

			 wf_engine.setItemAttrNumber(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'AME_TRANSACTION_ID',
			 avalue=>crec.header_id) ;

			 wf_engine.setItemAttrNumber(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'ORDER_HEADER_ID',
			 ) ;

			 wf_engine.setItemAttrNumber(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'ORDER_NUMBER',
			 ) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'START_COMMENT',
			 avalue=>p_comment) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'NUMBER_OF_WAIT_DAYS',
			 avalue=>getTimeoutDay(crec.header_id )) ;

			 wf_engine.setitemattrtext (itemtype,  

			 wf_engine.startprocess (itemtype, 

			 is_entered := true ;

		 end loop;

		 if (not is_entered) then

			 insertLog (itemtype ,
			 itemkey ,
			 'startApprovalWF no record has been found' );
			 fnd_file.put_line (fnd_file.LOG,'startApprovalWF no record has been found');
			 retcode := 2; --error

		 end if ;

	end ;
	/*This function defines our custom rules for timeout function in our AME WF*/
	function getTimeoutDay (p_header_id in number) return number is
    l_control number;


            select 1 into l_control from oe_transaction_types_tl ott,
                                         oe_order_headers_all ooh
                                   where language='US'
                                     and like 'Your Custom Transaction Type'
                                     and ooh.header_id=p_header_id
                                     and ott.transaction_type_id= ooh.order_type_id;

            /*Your Custom Transaction Type Order if they wouldn't order until next day at 5:00pm approval process cancel*/
            return  round( ((24-to_char(sysdate,'HH24') ))+ 17 )*60;  --dakika
          --round( ((24-to_char(sysdate,'HH24') )/24)+ 17/24 , 2)  ;

        exception when others then
			/*If it is not approved in 5 days it will be cancelled */
            return 5*24*60  ;  --dakika

        end ;

    end ;
	procedure insertLog (itemtype IN VARCHAR2,
                         itemkey IN VARCHAR2,
                         p_log in varchar2
                        ) is

    l_rec xxxl.xxxl_wf_log%rowtype ;

       l_rec.item_type:=itemtype  ;
       l_rec.item_key:=itemkey ;
       l_rec.xlog:=p_log  ;
       l_rec.object_name:='xx_ame_start_appr_pkg' ;
       l_rec.trnx_Date :=sysdate ;
       insert into xxxl.xxxl_wf_log VALUES l_rec ;

    end ; 


I hope this will help to increase the demand for more Oracle products.




Oracle Inventory Reservation Transfer between Different Sources

Creating reservation for inventory items is one of most common actions regarding business requirements in Oracle E-Business Suite platform. In this example, we transfer existing reservation quantity between different transaction source types/organizations/uom’s in R12 version of EBS.

For each three item-organization-transaction source type , there should be only one unique row in INV.MTL_RESERVATIONS table. So following loop is for a better code review and look. (using cursor depends on you).  We use standart Oracle Inventory API’s and table/record types in this procedure. Here is the PL/SQL script;

procedure transfer_reservation  (  p_rsv_rec in  inv_reservation_global.mtl_reservation_rec_type,
								   x_msg_count out nocopy number,
								   x_msg_data  out nocopy varchar2,
								   x_return_status out varchar2,
								   x_reservation_id out number ) is

x_serial_number   inv_reservation_global.serial_number_tbl_type;
v_rsv_from_rec    inv_reservation_global.mtl_reservation_rec_type ;
v_rsv_to_rec      inv_reservation_global.mtl_reservation_rec_type ;
ln_total_res_qty number:=0 ;

cursor c_res(p_inv_item_id number , p_organization_id number , p_uom varchar2)  is
		select	re.reservation_id,
		        /*you can use a your conversion functions if your source 
				and destination for different UOM's*/
				xxln_inv_pkg.UOM_CONVERSION(p_inventory_item_id =>p_inv_item_id,
					  			       pcur_qty =>re.reservation_quantity,
								       pcur_uom =>re.reservation_uom_code,
								       pnew_uom =>p_uom) as reservation_quantity,
		   from mtl_reservations re
		  where organization_id = p_organization_id
			and inventory_item_id = p_inv_item_id
			and demand_source_type_id = (select transaction_source_type_id
										   from mtl_txn_source_types														
									      where transaction_source_type_name = 'SOME-TRANSACTION-SOURCE')
	   order by re.creation_date;

		v_rsv_to_rec.demand_source_type_id  := p_rsv_rec.demand_source_type_id; 
		v_rsv_to_rec.demand_source_name     := to_char(sysdate);

		for crec in c_res(p_rsv_rec.inventory_item_id,p_rsv_rec.organization_id, p_rsv_rec.reservation_uom_code)  loop

			v_rsv_from_rec.reservation_id  := crec.reservation_id;
			/*Here I use each remaining quantity from source until it equals to demand amount.
			 After process , we could have still remaining amount in our quota*/
			if( p_rsv_rec.reservation_quantity-ln_total_res_qty<crec.reservation_quantity ) then

				v_rsv_to_rec.reservation_quantity :=p_rsv_rec.reservation_quantity-ln_total_res_qty;
				v_rsv_to_rec.reservation_uom_code :=p_rsv_rec.reservation_uom_code;
				ln_total_res_qty:=ln_total_res_qty+v_rsv_to_rec.reservation_quantity ;


				v_rsv_to_rec.reservation_quantity := crec.reservation_quantity;
				v_rsv_to_rec.reservation_uom_code := p_rsv_rec.reservation_uom_code;

			end if;

			inv_reservation_pub.transfer_reservation(p_api_version_number        => 1.0,
													 p_init_msg_lst              => FND_API.G_TRUE,
													 x_return_status             => x_return_status,
													 x_msg_count                 => x_msg_count,
													 x_msg_data                  => x_msg_data,
												   --p_is_transfer_supply       IN  VARCHAR2 DEFAULT fnd_api.g_true
													 p_original_rsv_rec          =>  v_rsv_from_rec,
													 p_to_rsv_rec                =>  v_rsv_to_rec ,
													 p_original_serial_number    =>  x_serial_number ,
													 p_to_serial_number          =>  x_serial_number ,
												   --p_validation_flag          IN  VARCHAR2 DEFAULT fnd_api.g_true
		   										   --p_over_reservation_flag    IN  NUMBER DEFAULT 0
													 x_to_reservation_id         =>x_reservation_id);

			if (x_return_status<>'S') then

				return  ;

			end if ;

		end loop ;

end ;

That’s it !

Data Migration via Advanced PL/SQL (Bulk Processing)

Data Migration/Manuplation operations are common in projects, in this case we will use “Bulk Processing” for fastest UPDATE operations in Oracle Databases. We have two staging tables; xxln.xxln_bom_operational_routings xbor and xxln.xxln_bom_operation_sequences . We are moving old sequence values on our production tables in E-Business Suite BOM (Bill of materials) schema .

This PL/SQL code shows how it works ;


cursor c1 is 
      select /*+ FIRST_ROWS
             bos.rowid as main_comp_rowid, 
            from xxln.xxln_bom_operational_routings xbor, 
                 xxln.xxln_bom_operation_sequences xbos ,   
                 mtl_system_items_b msib, 
                 bom_operational_routings bor,
                 bom_operation_sequences bos ,
                 mtl_parameters mp  
            where xbor.assembly_item_code = msib.segment1
              and xbor.organization_code = mp.organization_code
              and bor.organization_id=mp.organization_id
              and msib.organization_id = mp.organization_id
              and bor.assembly_item_id = msib.inventory_item_id
              and bor.organization_id = mp.organization_id
              and bos.routing_sequence_id = bor.routing_sequence_id
              and xbos.routing_sequence_id= xbor.routing_Sequence_id
              and nvl(bor.alternate_routing_designator ,'-99') = nvl(xbor.alternate_routing_designator,'-99')
              and nvl(bos.disable_date,sysdate-1000) = nvl(xbos.disable_date,sysdate-1000)  
              and bos.operation_seq_num = xbos.operation_seq_num ;              
type crec_type is table of c1%rowtype index by binary_integer; 
crec crec_type ; 


        open c1 ; 
        fetch c1 bulk collect into crec limit 2000000 ; 
            forall i in crec.first..crec.last 
                     update bom_operation_sequences set 
                            attribute14 = crec(i).routing_sequence_id,
                            attribute15 = crec(i).operation_sequence_id,
                            where rowid =crec(i).main_comp_rowid ; 
        exit when c1%notfound ; 
        end loop ; 
end ;

We use main tables rowid on UPDATE operation , FIRST_ROWS hint and combine it fetching data with BULK COLLECT INTO and FORALL LOOP for best result. You can also divide the query by adding a condition and execute it in different times , this helps cleaning rollback segments (caused by commit) if you are working on VLDB ‘s .


Mongo DB DaaS – Connecting AWS EC2 via SSH Tunnel Port Forwarding

In this case, we will connect Mongo DB on AWS EC2 cloud platform via SSH tunnel features. I assume that you got Public AWS DNS and finished authentication procedures (adding key pairs/passwords). We login AWS with Putty – SSH session . After that we change putty configurations in connection>ssh>tunnels tab ;


In this case , Mongo DB is running on 27000 port on AWS . And we are forwarding this port to local .


We can use Robomongo for testing connection to Mongo DB cloud ;



Now we can query our collections ,


Simple! , we finished connecting Mongo DB DaaS on AWS EC2 via SSH Tunnel Port Forwarding process. That’s it!


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 :


    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 ;


import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;  

public class XXXLcustomappAMImpl extends OAApplicationModuleImpl { 
    public XXXLcustomappAMImpl() {
    public static void main(String[] args) {
        launchTester("", /* 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) ; 
                    String return_status = cs.getString(1);
                    if (!return_status.equals("S")) {
                        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) ; 
                String return_status_submit = cs_submit.getString(1);
                System.out.println("return_status_submit :"+return_status_submit);
                if ( return_status_submit.equals("E") )
                } else retarr.put("xx_is_errored","N");
        return retarr  ; 
    public void initErrorTable(String p_batch_id ) {
        XXXLcustomappImportErrorsVOImpl vo = getXXXLcustomappImportErrorsVO1() ;

    /**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!

Oracle E-Business Suite – Bill of Materials ATO Configurations Migration via SQL Loader

In this case,  we are going to create scripts for migrating data from 11i Oracle EBS to version R12.  This process is including generating “meaningful” data with SQL , creation of custom staging table of bom.bom_ato_configurations , a bulk insert via SQL Loader using single control(.ctl) file and finally runing it in Linux. Let’s start ;

First of all we are creating custom staging table and indexes for ATO Configurations in R12 on custom schema  ;

  config_item_id number, 
  organization_id number, 
  base_model_id number, 
  component_item_id number,  
  component_code varchar2(240 BYTE), 
  creation_date date,  
  component_quantity number, 
  last_referenced_date date, 
  cfm_routing_flag number, 
  organization_code varchar2(3 byte),
  config_item_code varchar2(150 byte),
  base_model_code varchar2(150 byte),
  component_item_code varchar2 (150 byte),
  component_code_name varchar2(2000 byte)
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT

            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT


Then we generate our more “meaningful” hierarchical material – item data associated with components via following SQL script;

select b.config_item_id, 
       msib.segment1 as config_item_code,
       msib2.segment1 as base_model_code,
       msib3.segment1 as component_item_code,
             x3.segment1 ,'--') as component_code_name 
  from bom.bom_ato_configurations b , 
       mtl_system_items_b msib ,
       mtl_system_items_b msib2, 
       mtl_system_items_b msib3,
       mtl_parameters mp ,
       mtl_system_items_b x1,
       mtl_system_items_b x2,
       mtl_system_items_b x3
 where b.organization_id = mp.organization_id 
   and mp.organization_id = msib.organization_id 
   and b.config_item_id = msib.inventory_item_id
   and mp.organization_id = msib2.organization_id 
   and b.base_model_id = msib2.inventory_item_id
   and mp.organization_id = msib3.organization_id 
   and b.component_item_id = msib3.inventory_item_id 
   and x1.inventory_item_id(+) = rtrim(ltrim( substr(b.component_code, 0,
                                    decode(instr (b.component_code,'-',1,1), 0,
                                       length(b.component_code),instr (b.component_code,'-',1,1))),'-') ,'-' )  
   and x1.organization_id(+)  = b.organization_id 
   and x2.inventory_item_id(+) =  rtrim(ltrim( substr(b.component_code,
                                    decode( instr(b.component_code,'-',1,1),0,1000, instr(b.component_code,'-',1,1)) ,
                                    decode(instr (b.component_code,'-',1,2),0,length(b.component_code)+1,
                                     instr(b.component_code,'-',1,2)) - instr (b.component_code,'-',1,1)),'-'))
   and x2.organization_id(+)   = b.organization_id 
   and x3.inventory_item_id(+) =  rtrim(ltrim( substr(b.component_code,
                                    decode(instr (b.component_code,'-',1,2),0,1000,instr (b.component_code,'-',1,2)),
                                    length(b.component_code)+1- instr (b.component_code,'-',1,2)),'-'))
   and x3.organization_id(+)  = b.organization_id

Now we need to export this data to SQL Loader control file , i use TOAD’s sqlloader extension in this case , it can directly write output results to single .ctl file . But you can also manually create your own control file by refencing a exported .csv sheet.


I use EBS Vision Database so item and component names don’t look like a material! but your in production DB should not look like this . This script also creates .bad and .dsc file in the same directory that you can check if there is any error . SQL Loader control file is that one  ;

Fields terminated by ";" Optionally enclosed by '|'
4524;207;143;1492;|143-1490-1492|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM55243|;|CN97444-OC42556-CM55243|
4524;207;143;251;|143-299-251|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM08830|;|CN97444-OC29315-CM08830|
4524;207;143;197;|143-313-197|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM94043|;|CN97444-OC55449-CM94043|
4524;207;143;185;|143-297-185|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM41684|;|CN97444-OC68020-CM41684|
4524;207;143;217;|143-347-217|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM56560|;|CN97444-OC55437-CM56560|
4524;207;143;257;|143-347-257|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM49954|;|CN97444-OC55437-CM49954|
4524;207;143;219;|143-347-219|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM59311|;|CN97444-OC55437-CM59311|
4524;207;143;215;|143-347-215|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CM54321|;|CN97444-OC55437-CM54321|
4524;207;143;1490;|143-1490|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC42556|;|CN97444-OC42556|
4524;207;143;299;|143-299|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC29315|;|CN97444-OC29315|
4524;207;143;313;|143-313|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC55449|;|CN97444-OC55449|
4524;207;143;297;|143-297|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC68020|;|CN97444-OC68020|
4524;207;143;347;|143-347|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|OC55437|;|CN97444-OC55437|
4524;207;143;143;|143|;|03/29/2001 10:49:56|;1;|03/29/2001 10:49:56|;NULL;|M1|;|CN97444*4523|;|CN97444|;|CN97444|;|CN97444|
221170;209;220028;220035;|220028-220035|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT001-Computer-Controlled|;|HFAT001|;|HFOC006|;|HFAT001-HFOC006|
221170;209;220028;220061;|220028-220035-220061|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT001-Computer-Controlled|;|HFAT001|;|HF55004|;|HFAT001-HFOC006-HF55004|
221170;209;220028;220028;|220028|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT001-Computer-Controlled|;|HFAT001|;|HFAT001|;|HFAT001|
221168;209;220080;220030;|220080-220030|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HFOC001|;|HFAT002-HFOC001|
221168;209;220080;220037;|220080-220030-220037|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HF50001|;|HFAT002-HFOC001-HF50001|
221168;209;220080;220075;|220080-220075|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HFOC008|;|HFAT002-HFOC008|
221168;209;220080;220077;|220080-220075-220077|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HF57001|;|HFAT002-HFOC008-HF57001|
221168;209;220080;220080;|220080|;|10/23/2009 13:22:02|;1;|10/23/2009 13:22:02|;NULL;|M2|;|HFAT002-375HP|;|HFAT002|;|HFAT002|;|HFAT002|
10541;1884;7033;7113;|7033-7047-7113|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WD50152|;|WDAT001-WDOC104-WD50152|
10541;1884;7033;7107;|7033-7045-7107|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WD50100|;|WDAT001-WDOC102-WD50100|
10541;1884;7033;7099;|7033-7041-7099|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WD50007|;|WDAT001-WDOC100-WD50007|
10541;1884;7033;7047;|7033-7047|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDOC104|;|WDAT001-WDOC104|
10541;1884;7033;7045;|7033-7045|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDOC102|;|WDAT001-WDOC102|
10541;1884;7033;7041;|7033-7041|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDOC100|;|WDAT001-WDOC100|
10541;1884;7033;10539;|7033-7039|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT004*10538|;|WDAT001-WDAT004|
10541;1884;7033;10537;|7033-7037|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT003*10536|;|WDAT001-WDAT003|
10541;1884;7033;10535;|7033-7035|;|07/31/2003 12:46:34|;2;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT002*10534|;|WDAT001-WDAT002|
10541;1884;7033;7033;|7033|;|07/31/2003 12:46:34|;1;|07/31/2003 12:46:34|;NULL;|W1|;|WDAT001*10540|;|WDAT001|;|WDAT001|;|WDAT001|
10539;1884;7039;7397;|7039-7071-7397|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD51301|;|WDAT004-WDOC114-WD51301|
10539;1884;7039;7389;|7039-7069-7389|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD51205|;|WDAT004-WDOC115-WD51205|
10539;1884;7039;7377;|7039-7067-7377|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD51111|;|WDAT004-WDOC113-WD51111|
10539;1884;7039;7327;|7039-7063-7327|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50910|;|WDAT004-WDOC111-WD50910|
10539;1884;7039;7303;|7039-7061-7303|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50810|;|WDAT004-WDOC116-WD50810|
10539;1884;7039;7237;|7039-7057-7237|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50610|;|WDAT004-WDOC109-WD50610|
10539;1884;7039;7189;|7039-7053-7189|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50410|;|WDAT004-WDOC107-WD50410|
10539;1884;7039;7129;|7039-7049-7129|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50207|;|WDAT004-WDOC105-WD50207|
10539;1884;7039;7109;|7039-7047-7109|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50150|;|WDAT004-WDOC104-WD50150|
10539;1884;7039;7107;|7039-7045-7107|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50100|;|WDAT004-WDOC102-WD50100|
10539;1884;7039;7101;|7039-7041-7101|;|07/31/2003 12:46:33|;1;|07/31/2003 12:46:33|;NULL;|W1|;|WDAT004*10538|;|WDAT004|;|WD50008|;|WDAT004-WDOC100-WD50008|

Now , bom_ato_configurations.ctl file generated from 11i enviroment is ready to migrate with R12 . After deploying our .ctl file to database server , we use this shell script on Redhat Linux to run sqlloader , you should change the directories on this script with your deployment directory :

sqlldr   apps/$AppsPWD control=./files/ctl/bom_ato_configurations.ctl

Finally , we finished Bill of Materials ATO Configurations Migration via SQL Loader process . We have a “meaningful” ATO Configuration data in our custom XXTH.XXTH_BOM_ATO_CONFIGURATIONS staging  table that we can use in Reports and API’s in R12 . Enjoy!

End to End Virtualization E-Business Suite 12.2.3 on a Windows host


What’s Our Expectations In This Process?

We will create a guest Oracle Linux virtual machine on Oracle VM VirtualBox with a Single Node Vision VM Template Appliance. And we will configure it as EBS rapid-express installation standarts for version 12.2. In this process we will also exercise basic Unix Shell Scripting skills on Oracle Linux 6 for less system administrator more “developer” “mates”. After Installation/configuration process will have a 12.2.3 version R12 EBS enviroment that helps us to examine differences between older versions of EBS on particular areas such as http/servlet services on application tier (Weblogic Server) and file system structure.Before we start this virtualization process, we need some requirements ;

  • Virtual Disk Space : 500 GB
  • Actual Disk Space Used on File System : 256 GB
  • RAM : 6 GB
  • A post modern CPU
  • An Oracle E-Delivery account
  • Totally around 18 hours (Depends on your network connection) to Finish End to End process including downloading and post-configuration.
  • Cups of coffees!

First of all, we are starting this flow by loging in the side and selecting Oracle VM Templates as product pack and x86 64bit as Platform. Then we download releated files as follows ;


  • V43683-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64
  • V43684-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64
  • V43685-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64
  • V43686-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64
  • V43687-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64
  • V43688-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64
  • V43689-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64
  • V43690-01  Oracle EBS 12.2.3 Single Node Vision Install X86 (64 bit) Linux x86-64

We need to download 16 zip files from E-delivery. Sum of these files cost 53 GB . Also it is important to verify files after download, i do this with Digest (SHA-1) for each file using DownloadManager (Mozilla). We can find SHA list on edelivery download page.


After finishing download we need to unzip all files then we need assemble all .ova files in a single .ova file . Oracle gives us a assemble script on this document 1620448.1 . But i wrote my own .bat file on windows its simple ..

ECHO “Starting Assemble…”
Copy /B Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.00+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.01+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.02+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.03+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.04+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.05+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.06+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.07+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.08+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.09+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.10+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.11+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.12+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.13+Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova.14 Oracle-E-Business-Suite-12.2.3-VISION-INSTALL.ova
ECHO “Assemble finished”

Then we need to import single .ova file to Oracle VM Virtual Box from file>import appliance menu;


Then we need to change network adapter to Host-Only Adapter from VM Manager, if you are using public networks like British Library, it is possible to have new problems with types like Bridged Adapter and that would costs your hours 🙂


After ending the VM appliance, we boot it. In first boot VM comes with pre-configured linux version , so we don’t need to do any changes for starting SSH protocols or any necessary services for OS. We need to enter new passwords for root in boot screen, applmgr, oracle users .After that patch 18545803 is necassary for last version of appliance scripts, this will update .sh files which is using in installment process . then when patching is finished , we run /u01/install/scripts/ to configure host and domain names (default values are , ebs as host , as domain , i gave for that process. ) . Then we run /u01/install/scripts/ to force force configuring next boot.Finally we boot the machine with reboot command. after reboot we need to check network configurations as follows  ;


After finishing network configurations we run vision configuration scripts , these are asking you db host-domain names and SID while processing .  ;

  • /u01/install/VISION/scripts/ script with root user , this script refines db config files for autoconfig then automatically starts db tier services.
  • /u01/install/VISION/scripts/ script with root user , this script refine application config files for autoconfig then automatically starts all application tier services . Before running this script we should check that database services is up(TNS listener).

Then we need to run autoconfig for all db and application tier . before running autoconfig we must set enviroments (.env)  . We should check doc 387859.1 from , this document includes detailed information about autoconfig process. I run both autoconfig process with oracle user. And don’t logout! After finishing both db and application autoconfig we need to check enviroment variables as oracle user ;


Now we have an EBS platform with these default passwords ;

  • weblogic/welcome1
  • root-oracle-applmgr / <user defined passwords>
  • sysadmin/sysadmin

Then we must change ~/.bashrc for both oracle and applmgr users .This initializes enviroment variables in every login for each session ;

  • For oracle user /home/oracle/.bashrc
  • For applmgr user /home/applmgr/.bashrc

After adding last two line both must look like that ;


Also we need to append our <host> .<domain> ( with to c:windowssystem32driversetchosts file to connect our VM guest machine from Windows .Now Check ! EBS 12.2.3 Platform and Weblogic Console from these URLs 😉 ;

  • EBS :    <host> .<domain>:8000/OA_HTML/AppsLogin
  • Weblogic Console :    <host> .<domain>:7001/console/




Now we finished configuration , there are some key administration scripts that we need to check while shutting down the VM. these are under $ADMIN_SCRIPTS_HOME directory , and we must always run some of these scripts before shutdown ;


  • $ADMIN_SCRIPTS_HOME/ (this stops all application tier services)
  • $ADMIN_SCRIPTS_HOME/ (this starts all application tier services )

Before shutting down VM we need to shutdown all db and application services as follows ;




On every boot of VM , db and application services are being started so we don’t need to start these services manually.We completed End to End Virtualization E-Business Suite 12.2.3 process .

Enjoy R12 – 12.2.3 now your VM is ready for development!!!

Refereced Oracle Documents ;

  • Doc ID 1620448.1  : General installation process for Virtual Box
  • Doc ID 387859.1    : document that includes Autoconfig details
  • Doc ID e22950-17  : Installation Guide: Using Rapid Install

What’s that holy WebLogic “thing” in Oracle E-Business Suite 12.2 ?

First version of Oracle E-Business Suite 12.2 was released about one year ago at September 2013. As a result of Oracle Sales Managers hard work, it’s planing to start implementations of this new version to clients/partners probably will be at the end of this year. In Release 12.2, Web and Forms services are provided by Oracle Application Server and Oracle Fusion Middleware. They are no longer servers in the sense of being a single process, as was the case in previous releases. There is no other spesific changes on client or database tiers.

We can figure this changes and current EBS 12.2 Architecture as follows ;


Association between Client and Application Tiers are different then other R12 versions. It seems Forms and Web Services are integrated with Fusion Middleware services  ;


When we also want to check the “new” Application Tier ,we are still seeing that there are two ORACLE_HOMEs like other older R12 versions. Oracle documents are writing that enabling this two ORACLE_HOMEs are about advantages of lasted Oracle Technologies.


In EBS 12.2 Web Service components of Oracle Application Server processes requests are mainly includes two components. These are  ;

  • Web Listener : Our old friend Oracle HTTP Listener Apache
  • Java Servlet Engine : Our new crush(!) Oracle Weblogic Server .

Web listener(Apache) basicly gives information by accepting HTTP requests from client browsers and gives to integrated Weblogic Service-WLS for security and context . And also Apache still can supply HTML content for basic requests, it only routes to Servlet if requested content is advanced level. If HTTP request(url) is including technologies like ADF and OAF or hybrid of ADF/OAF, Weblogic takes responsibilty by obtaining data from database and making the meal with the context from metadata dictionary (constructs HTML page), then sends back HTML page to our precious end user browsers with the help of Apache(Listener). This figure shows how it works ;


What kind of holy product is this Weblogic Server and what is its real occupation in these old sustainable/traditional EBS Architecture?

Yes we can answer this question in an easy simple way; Weblogic services are replaced with OC4J (Oracle Container for Java) as new Servlet Engine. All HTML-Based Applications and Oracle Application Framework (OAF) is still using Java servlets provided by Apache Jserv module and BC4J patterns (Business Components for Java) but Oracle Weblogic provides connection to database and application-specific functionality such as flexfields via WLS service. Weblogic is integrated with this tier planning to create hybrid applications using OAF-ADF and optimizing clustring performances . I think possible further requirements in the future for EBS to Fusion evolution is also key reason for Oracle’s decision on this change.

In 12.2 , OA Framework Architecture includes Weblogic services such as WLS,UIX,Java Controller, Metadata UI. And it looks like this ;


When we compare 12.2 with other R12 versions we can’t not see any changes for Forms Services . There is no change in Form Listener Servlet .They are still connecting database with Oracle Net networking infrastructure.But! Weblogic manages this processes ;


 Summary  ;

A server is an instance of WebLogic Server that runs in its own Java Virtual Machine (JVM) and has its own configuration. And there five WebLogic instances these are ;

  • Admin(Server)
  • oacore (also cluster)
  • oafm (also cluster)
  • forms (also cluster)
  • formsc4ws  (also cluster)


Referenced Oracle Documents about Weblogic thing in Oracle E-Business Suite:

  •  Part No. E12841-04
  •  Part No. E22949-09