Home » Applications » Oracle Fusion Apps & E-Business Suite » Process Order API - Unreserving a Sales Order in Oracle
Process Order API - Unreserving a Sales Order in Oracle [message #228372] Mon, 02 April 2007 14:50 Go to next message
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

Hi there,

I was taking a look at the 11.5.10 API and Open Interface Manual for Order Management.

I did not see anything for the Process Order API that would allow me to unreserve the Sales Order

Any suggestions or am I missing the section?

Re: Process Order API - Unreserving a Sales Order in Oracle [message #228384 is a reply to message #228372] Mon, 02 April 2007 16:00 Go to previous messageGo to next message
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

It looks like you can use Process_Order

schedule_action_code can be used on the order line record giving it the value unreserve.

will post a more detailed solution when done
Re: Process Order API - Unreserving a Sales Order in Oracle [message #228923 is a reply to message #228372] Wed, 04 April 2007 09:28 Go to previous messageGo to next message
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

I have put together the following code to UNRESERVE the Sales Order Lines. However I am getting a return status of E whenever I run the procedure. I would appreciate anyone to take a look over and give their opinions.

   pc_po_req     IN        NUMBER,
   x_ret_sta_o   OUT	   VARCHAR2   
  vSoOrgId      NUMBER;
  vRespId       NUMBER;
  vRespAppId    NUMBER;
  vUserId       NUMBER;
  vHeaderId     NUMBER;
  vLineId       NUMBER;
  vRetSta       VARCHAR2(2000);
  vMsgCnt       NUMBER;
  vMsgDat       VARCHAR2(2000);
  vItemNumber   VARCHAR2(240);
  x_return_status           VARCHAR2(1);
  x_msg_count               NUMBER;
  x_msg_data                VARCHAR2(4000);
  x_header_rec              OE_ORDER_PUB.Header_Rec_Type;        
  x_header_val_rec          OE_ORDER_PUB.Header_Val_Rec_Type;  
  x_Header_Adj_tbl          OE_ORDER_PUB.Header_Adj_Tbl_Type;
  x_Header_Adj_val_tbl      OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
  x_Header_price_Att_tbl    OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
  x_Header_Adj_Att_tbl      OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
  x_Header_Adj_Assoc_tbl    OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
  x_Header_Scredit_tbl      OE_ORDER_PUB.Header_Scredit_Tbl_Type;
  x_Header_Scredit_val_tbl  OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
  x_line_tbl                OE_ORDER_PUB.Line_Tbl_Type;
  x_line_val_tbl            OE_ORDER_PUB.Line_Val_Tbl_Type;
  x_Line_Adj_tbl            OE_ORDER_PUB.Line_Adj_Tbl_Type;
  x_Line_Adj_val_tbl        OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
  x_Line_price_Att_tbl      OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
  x_Line_Adj_Att_tbl        OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
  x_Line_Adj_Assoc_tbl      OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
  x_Line_Scredit_tbl        OE_ORDER_PUB.Line_Scredit_Tbl_Type;
  x_Line_Scredit_val_tbl    OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
  x_Lot_Serial_tbl          OE_ORDER_PUB.Lot_Serial_Tbl_Type;
  x_Lot_Serial_val_tbl      OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
  x_action_request_tbl      OE_ORDER_PUB.Request_Tbl_Type;
  select oeh.org_id
  into   vSoOrgId
  from   apps.oe_order_headers_all oeh
  where  oeh.order_number = pc_po_req;
  select fr.responsibility_id
  into   vRespId
  from   apps.fnd_responsibility_vl fr
        ,apps.hr_all_organization_units hou_ou
        ,apps.hr_organization_information hoi
  where  hoi.org_information_context = 'Accounting Information'
  and    hoi.org_information3 = hou_ou.organization_id
  and    hou_ou.organization_id = vSoOrgId
  --and    substr(hou_ou.name,1,instr(hou_ou.name,'_')-1)||' OM Super User' = fr.responsibility_name
  and  	 fr.responsibility_name like '%BVI%%TRANSFER%'
  and    rownum = 1;
  select user_id
  into   vUserId
  from   apps.fnd_user fu
  where  user_name = 'MADDENE_TEMP';

  dbms_output.put_line ('Initializing');
  fnd_global.apps_initialize (vUserId, vRespId, vRespAppId);


  dbms_output.put_line ('Retrieving Order Details ');

  oe_order_pub.get_order (1

  dbms_output.put_line ('Number of Lines on Order : '||to_char(x_line_tbl.count));
  dbms_output.put_line ('Updating Scheduling Details ');
 for i in 1..x_line_tbl.count
    select msi.segment1
    into   vItemNumber
    from   apps.mtl_system_items msi
    where  msi.inventory_item_id = x_line_tbl(i).inventory_item_id
    and    msi.organization_id = x_line_tbl(i).ship_from_org_id;

    dbms_output.put_line ('Part Number: '||vItemNumber);
    x_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
--    x_line_tbl(i).schedule_status_code := null;

	--x_line_tbl(i).operation := OE_GLOBALS.G_RESCHEDULE_LINE;
    x_line_tbl(i).schedule_action_code := 'UNRESERVE';
	--x_line_tbl(i).reserved_quantity := null;
    x_line_tbl(i).request_date := sysdate;
--    x_line_tbl(i).schedule_ship_date := null;
--    x_line_tbl(i).schedule_arrival_date := null;

-- Set the Action Request Values
  x_action_request_tbl(i).request_type := OE_GLOBALS.G_SCHEDULE_LINE;
  x_action_request_tbl(i).entity_code := OE_GLOBALS.G_ENTITY_LINE;
  x_action_request_tbl(i).entity_id := x_line_tbl(i).line_id;
  end loop;
  dbms_output.put_line ('Calling Oe_Order_Pub.Process_Order ');

  Oe_Order_Pub.Process_Order  (1,
                               --IN PARAMETERS
                               p_line_tbl   => x_line_tbl,
                               --OUT PARAMETERS
                               x_header_rec             => x_header_rec,
                               x_header_val_rec         => x_header_val_rec,
                               x_Header_Adj_tbl         => x_Header_Adj_tbl,
                               x_Header_Adj_val_tbl     => x_Header_Adj_val_tbl,
                               x_Header_price_Att_tbl   => x_Header_price_Att_tbl,
                               x_Header_Adj_Att_tbl     => x_Header_Adj_Att_tbl,
                               x_Header_Adj_Assoc_tbl   => x_Header_Adj_Assoc_tbl,
                               x_Header_Scredit_tbl     => x_Header_Scredit_tbl,
                               x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl,
                               x_line_tbl               => x_line_tbl,
                               x_line_val_tbl           => x_line_val_tbl,
                               x_Line_Adj_tbl           => x_Line_Adj_tbl,
                               x_Line_Adj_val_tbl       => x_Line_Adj_val_tbl,
                               x_Line_price_Att_tbl     => x_Line_price_Att_tbl,
                               x_Line_Adj_Att_tbl       => x_Line_Adj_Att_tbl,
                               x_Line_Adj_Assoc_tbl     => x_Line_Adj_Assoc_tbl,
                               x_Line_Scredit_tbl       => x_Line_Scredit_tbl,
                               x_Line_Scredit_val_tbl   => x_Line_Scredit_val_tbl,
                               x_Lot_Serial_tbl         => x_Lot_Serial_tbl,
                               x_Lot_Serial_val_tbl     => x_Lot_Serial_val_tbl,
                               x_action_request_tbl     => x_action_request_tbl);
  dbms_output.put_line ('Return Status: '||vRetSta);
  dbms_output.put_line ('Return Count: '||vMsgCnt);
  dbms_output.put_line ('Return Data: '||vMsgDat);
  --x_ret_sta_o := vRetSta;

  when others then
    raise_application_error (-20001,sqlerrm);

[Updated on: Wed, 04 April 2007 09:29]

Report message to a moderator

Re: Process Order API - Unreserving a Sales Order in Oracle [message #229532 is a reply to message #228923] Mon, 09 April 2007 01:45 Go to previous message
Messages: 18
Registered: March 2007
Location: Ireland and Boston
Junior Member

Firstly, sorry for the load of crap I put in the previous post. I didn't mean for all that to go in and apparently one can only edit posts before a reply is made to the original post.

Eventually I got this to work. Basically in OPM it works different to unreserve an order than in Oracle Discrete Manufacturing.

In OPM the reservation is created against an existing Batch or a Firm Planned Order. This manifests as a line in the Pending Transaction Table (IC_TRAN_PND) which contains a LINE_ID field which links it to the Sales Order Line_Id.

The Procedure Below can be used to UnReserve quantity on the Sales Order Line.

Hope this helps somebody else.

p_api_version => G_API_VERSION_NUMBER,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_tran_rec => p_tran_rec,
x_tran_row => x_tran_row,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data

[Updated on: Mon, 09 April 2007 01:48]

Report message to a moderator

Previous Topic: Finally Close a PO Requisition - API or Open Interface
Next Topic: After calling an Activity in Workflow proceeding is Deferred
Goto Forum:

Current Time: Tue Oct 25 11:18:37 CDT 2016

Total time taken to generate the page: 0.11617 seconds