Bookmark and Share Subscribe You may face visibility problem if http://docs.google.com is blocked on your network

Monday, June 22, 2009

Datafix to Close Sales Order Header in Oracle

Many times Sales order line/s gets closed properly but the header remain in BOOKED status. In this case you need to go for data fix.
Proper way to do this is call appropriate function/procedure/API and close the orders
For this you need set environment variable by

begin
fnd_global.apps_initialize(:USER_ID, :RESP_ID, :RESP_APPL_ID);

end;
/


/*If Work Flow exist for Order header then use the script below. This will close the Order
After running this script run "Workflow Background Process" */

begin
wf_engine.completeactivity
(
'OEOH', --item_type
'9999999', --item_key = header_id .... pass the HEADER_ID of the order you want to close
'CLOSE_WAIT_FOR_L', -- apps.wf_process_activities.activity_name where instance_id= apps.wf_item_activity_statuses.process_activity
null
);
end;
/
Commit;

/*If Work Flow does NOT exist for Order then use the script below .
After running this script run "Workflow Background Process" */

declare
l_return_status VARCHAR2(240);
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_header_id NUMBER := 99999999; --pass the HEADER_ID of the order you want to close
begin
OE_ORDER_CLOSE_UTIL.Close_Order
( p_api_version_number => 1.0
, p_header_id => l_header_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
dbms_output.put_line('status = ' l_return_status);
end;
/
Commit;

/* After running this script run "Workflow Background Process" */
**********************************************************************
**********************************************************************
The other way is to write the script which does not call any API or function and closes the Sales Order directly. For which you can use the script below.

Ensure that all lines for order are closed before closing header:

Select HEADER_ID, OPEN_FLAG, FLOW_STATUS_CODE
from apps.oe_order_lines_all
where org_id=:org_id and header_id =:HEADER_ID; --pass the ORG_ID and HEADER_ID of the order you want to close

update apps.oe_order_headers_all
set open_flag = 'N' , FLOW_STATUS_CODE= 'CLOSED'
where org_id=:org_id and header_id =:HEADER_ID; --pass the ORG_ID and HEADER_ID of the order you want to close

Commit;

4 comments:

  1. we can not perform dml directly on base tables right?

    ReplyDelete
  2. We can perform DML on base tables if we have access. But ideally we shall not do that. In some exceptional cases we when we do not any other option we have to execute DML on base table.

    Thanks
    Devendra

    ReplyDelete
  3. Awesome. It works like a charm. Thanks.

    ReplyDelete
  4. Hi Devendra,

    Do you have a similar datafix script to close sales order lines?

    ReplyDelete

Please add your valuable feedback / comments

Declaimer:

This blog is purely personal and the thoughts expressed here represent only me. The purpose of this blog is to share information and knowledge about Oracle's product which I have come across with my exposure to the product, practice and observations. The blog has been created keeping only one intention of sharing knowledge and for learning purpose. The blog has been created solely as a educational, for storing portions of the vast Oracle knowledge world. Oracle EBS is an Oracle Corp. product and you should contact Oracle directly for any specific fact or issue.

*NOTE: Few articles on this blog are not completely prepared by me, content is edited and complied after referring various sites to make visitor's job easy