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

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
      Shopping cart