Sales Order Header Close API

------------Spec
PROCEDURE xxxxxx_order_hdr_close_prg (errbuf out varchar2,retcode out varchar2,p_ord_num number,p_whse_code varchar2);
---------------------------------------
------------Body

PROCEDURE xxxxxx_order_hdr_close_prg (errbuf out varchar2,retcode out varchar2,p_ord_num number,p_whse_code varchar2) is

--Declare

 l_result  VARCHAR2(60);
 l_file_val  VARCHAR2(60);
 l_result_out varchar2(30);
 l_return_status  varchar2(30);
 err_msg VARCHAR2(240);
 ln_cnt NUMBER;


Cursor lc_status_check is

Select count(ool.line_id) cur_cnt,ooh.header_id
From   oe_order_headers_All ooh,oe_order_lines_all ool--,oe_transaction_types_v ott
Where  1=1
And    ooh.flow_status_code in ('BOOKED','ENTERED')
And    ooh.header_id = ool.header_id
Group  By ooh.header_id;

Cursor C1 is
 
SELECT l.line_id
  FROM oe_order_lines_all l, wf_items
 WHERE  (l.line_id) = to_number(item_key)
   AND item_type = 'OEOL'
   AND l.open_flag = 'N'
   AND end_date IS NULL
   AND l.flow_status_code = 'CANCELLED';

  ln_user_id number;
  ln_resp_id number;
  ln_resp_app_id number;

Begin

ln_user_id := APPS.FND_PROFILE.VALUE('USER_ID');
      IF ln_user_id IS NULL THEN
         ln_user_id := -1;
      END IF;
        ln_resp_id  := APPS.FND_PROFILE.VALUE('RESP_ID');
      ln_resp_app_id := APPS.FND_PROFILE.VALUE('RESP_APPL_ID');
 Begin
       FND_GLOBAL.apps_initialize(ln_user_id,ln_resp_id,ln_resp_app_id);    
     end;

For j in C1 Loop
Exit When C1%notfound;
oe_debug_pub.debug_on;
 oe_debug_pub.initialize;
 l_file_val    := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
 oe_Debug_pub.setdebuglevel(5);
  fnd_file.put_line(fnd_file.output,'log file name is: '||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
  fnd_file.put_line(fnd_file.output,'Line id is -'||j.line_id);
  OE_Standard_WF.OEOL_SELECTOR
           (   p_itemtype => 'OEOL'
              ,p_itemkey  => to_char(j.line_id)
                ,p_actid    => 12345
              ,p_funcmode => 'SET_CTX'
              ,p_result   => l_result
           );
   fnd_file.put_line(fnd_file.output,'Line id is -'||j.line_id);      
  fnd_file.put_line(fnd_file.output,' Return Status is for Line: '||l_result);

  wf_engine.handleError('OEOL', to_char(j.line_id), 'CLOSE_LINE','RETRY','COMPLETE');

End Loop;
Commit;

For i in  lc_status_check loop
Exit When lc_status_check%notfound;

ln_cnt:= 0;

 BEGIN

 Select Count(line_id) into ln_cnt
 From   oe_order_lines_all
 Where header_id =  i.header_id
 And   flow_status_code in ('CLOSED','CANCELLED');
 Exception
 WHEN OTHERS THEN
 ln_cnt := 0;
 END;

 If i.cur_cnt = ln_cnt Then

  oe_debug_pub.debug_on;
 oe_debug_pub.initialize;
 l_file_val    := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
 oe_Debug_pub.setdebuglevel(5);
  fnd_file.put_line(fnd_file.log,'log file name is: '||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);

  OE_Standard_WF.OEOH_SELECTOR
           (p_itemtype => 'OEOH'
           ,p_itemkey => to_char(i.header_id)
           ,p_actid => 12345
           ,p_funcmode => 'SET_CTX'
           ,p_result => l_result
           );
        fnd_file.put_line(fnd_file.log,' Return Status is for Header: '||l_result);

  wf_engine.handleError('OEOH', to_char(i.header_id), 'CLOSE_HEADER','RETRY','COMPLETE');
 
  fnd_file.put_line(fnd_file.log,'Order Header Id :  '|| i.header_id);
 
END IF;
END LOOP;
EXCEPTION
 WHEN OTHERS THEN
         err_msg := 'Error :'||SQLERRM;
         fnd_file.put_line(fnd_file.log,'Error: Pls ROLLBACK as following error ocurred:- '||SQLERRM);
         OE_DEBUG_PUB.ADD(err_msg);
END xxxxxx_order_hdr_close_prg;