OE_HOLDS_PUB.Apply_Holds
There are times when business wants to put a sales order on hold based on some specific conditions.
There are public apis available for applying hold on an order. you can execute the oe_holds_pub.Apply_Holds api for a sales order and it can be called from your calling programs like
- OAF page
- Oracle Alerts
- Form Personalization
- Oracle Workflow
- Custom trigger
- Custom Concurrent program
Sample Pl SQL Code for Creating Order Hold
This package can be called from the OM workflow
CREATE OR REPLACE PACKAGE IDC_Apply_hold_Pkg As
PROCEDURE IDC_apply_hold (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2);
End IDC_Apply_hold_Pkg;
/
CREATE OR REPLACE Package Body IDC_Apply_hold_Pkg As
PROCEDURE IDC_apply_hold (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
IS
l_order_tbl OE_HOLDS_PVT.order_tbl_type;
l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
l_return_status VARCHAR2(5);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
j number;
i number;
v_sec_qty number;
v_sum_price number;
v_line_id number;
v_unit_price number;
cursor c1 is select ola.ordered_quantity2, ola.attribute19, ola.unit_selling_price, ola.line_id
from oe_order_headers_all oha, oe_order_lines_all ola
where ola.header_id = oha.header_id and oha.header_id = ola.header_id
and oha.org_id = 996
and oha.header_id = itemkey;
begin
open c1;
LOOP
fetch c1 into v_sec_qty, v_sum_price,v_unit_price ,v_line_id ;
EXIT WHEN c1%NOTFOUND;
OE_MSG_PUB.initialize;
-- l_order_tbl(1).header_id :=itemkey;
FND_GLOBAL.apps_initialize(2330,52417,660);
mo_global.init('ONT');
mo_global.set_policy_context('S',996);
/* select ola.ordered_quantity2, ola.attribute19, ola.unit_selling_price, ola.line_id
into v_sec_qty,v_sum_price,v_unit_price ,v_line_id from oe_order_headers_all oha, oe_order_lines_all ola
where ola.header_id = oha.header_id and oha.header_id =ola.header_id
and oha.org_id = 996
and oha.header_id = itemkey;*/
if TRUNC(v_sec_qty) != v_sec_qty then
l_hold_source_rec.hold_id := 1083;
l_hold_source_rec.hold_entity_code := 'O';
l_hold_source_rec.hold_entity_id := itemkey;--1353187;
l_hold_source_rec.line_id :=v_line_id;-- 1899441;
l_hold_source_rec.hold_comment := 'IDC Full Case Required ';
oe_holds_pub.Apply_Holds
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_hold_source_rec => l_hold_source_rec
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
end if;
-- cond 2 item price hold
if nvl(v_sum_price,0) != v_unit_price then
l_hold_source_rec.hold_id := 1082;
l_hold_source_rec.hold_entity_code := 'O';
l_hold_source_rec.hold_entity_id := itemkey;--1353187;
l_hold_source_rec.line_id :=v_line_id;-- 1899441;
l_hold_source_rec.hold_comment := 'IDC Item Price Hold ';
oe_holds_pub.Apply_Holds
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_hold_source_rec => l_hold_source_rec
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
end if;
dbms_output.put_line('ret status' ||l_return_status);
FOR j in 1..OE_MSG_PUB.count_msg
LOOP
OE_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => i
);
dbms_output.put_line('Error: ' || j || ':' || l_msg_data);
END LOOP;
resultout := 'COMPLETE';
return;
end loop;
close c1;
end;
end;
-- select * from user_errors where lower(name) like 'idc%apply%hold%'
