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
maddene
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?

Thanks!!
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
maddene
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
maddene
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.

PROCEDURE BIO_OM_INBOUND_UNRESERVE
   (
   pc_po_req     IN        NUMBER,
   x_ret_sta_o   OUT	   VARCHAR2   
   ) 
   IS
   
  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;
  
  begin
  select oeh.org_id
        ,oeh.header_id
  into   vSoOrgId
        ,vHeaderId
  from   apps.oe_order_headers_all oeh
  where  oeh.order_number = pc_po_req;
 
  select fr.responsibility_id
        ,fr.application_id
  into   vRespId
        ,vRespAppId
  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;
 
/**** THIS USER SHALL HAVE TO CHANGE< TEMP FOR THE MOMENT ******/ 
  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);

  
  oe_debug_pub.initialize;
  oe_debug_pub.setdebuglevel(5);
  oe_msg_pub.initialize;

  dbms_output.put_line ('Retrieving Order Details ');

  oe_order_pub.get_order (1
                         ,FND_API.G_FALSE
                         ,FND_API.G_FALSE
                         ,vRetSta
                         ,vMsgCnt
                         ,vMsgDat
                         ,vHeaderId
                         ,FND_API.G_MISS_CHAR
                         ,x_header_rec
                         ,x_header_val_rec
                         ,x_Header_Adj_tbl
                         ,x_Header_Adj_val_tbl
                         ,x_Header_price_Att_tbl
                         ,x_Header_Adj_Att_tbl
                         ,x_Header_Adj_Assoc_tbl
                         ,x_Header_Scredit_tbl
                         ,x_Header_Scredit_val_tbl
                         ,x_line_tbl
                         ,x_line_val_tbl
                         ,x_Line_Adj_tbl
                         ,x_Line_Adj_val_tbl
                         ,x_Line_price_Att_tbl
                         ,x_Line_Adj_Att_tbl
                         ,x_Line_Adj_Assoc_tbl
                         ,x_Line_Scredit_tbl
                         ,x_Line_Scredit_val_tbl
                         ,x_Lot_Serial_tbl
                         ,x_Lot_Serial_val_tbl);

  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
  loop
    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,
                               Fnd_Api.G_TRUE,
                               Fnd_Api.G_FALSE,
                               Fnd_Api.G_FALSE,
                               vRetSta,
                               vMsgCnt,
                               vMsgDat,
                               --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;

  commit;
exception
  when others then
    raise_application_error (-20001,sqlerrm);
end;  

[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
maddene
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.

GMI_TRANS_ENGINE_PUB.Delete_Pending_Transaction(
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: Sat Dec 03 01:26:53 CST 2016

Total time taken to generate the page: 0.27626 seconds