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  |
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 #228923 is a reply to message #228372] |
Wed, 04 April 2007 09:28  |
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 |
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 |
|
|
|
Goto Forum:
Current Time: Fri Jul 05 18:27:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
