Tag Archives: Oracle E-Business Suite

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 !

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 ;

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) ; 
                    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!