Create Order Hold in EBS

OE_HOLDS_PUB.Apply_Holds

image 15 Create Order Hold in EBS 1

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
image 12 Create Order Hold in EBS 2
Sales order with hold applied

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%'

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Cloud Technologies Blog
Logo
Enable registration in settings - general
Compare items
  • Total (0)
Compare
0
Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Shopping cart