Use oe_order_close_util
Metalink 133837.1
The header level workflow process 'Close-Order' has a wait function, which causes the header to wait until all the lines on the order
are closed before progressing the header to a closed status. This wait function comes seeded with a constant value which sets it to
defer until the last day of the month.
The following example from the omse11i.sql output shows the workflow status:
PROCESS_NAME ACTIVITY_NAME RESULT ACT_STATUS BEGIN_DATE
Close - Order Wait Null Deferred 31-JAN 00:00:00
This wait value comes seeded with the Application.
You can view the workflow process 'Close-Order' as follows:
In Workflow Builder, open the workflow definition for OM Order Header.
Under Processes, open up Close - Order.
Here you will notice the Wait function just after the Start function.
Right click on Wait and select Properties.
Under the Node Attributes tab you will see the following:
Wait Mode is a constant set to 'Day Of Month',
Day of Month is a constant set to 'Last'
These values can be changed to cause the workflow to defer for a set number of days, or to progress immediately. However it is
recommended that changes to workflows be done by someone proficient in Oracle Workflow. NOTE: These changes should not be
made to seeded workflow processes. It is advisable that a new workflow process be created and the necessary change made.
However if the seeded workflow or existing flow is modified, the change will not affect existing orders. Only newly created orders
will adopt the change.
Declare
ReplyDelete--
l_file_val VARCHAR2(300);
l_result VARCHAR2(30);
--
CURSOR header_cur
IS
SELECT h.header_id
FROM wf_item_activity_statuses st
, oe_order_headers_all h
, wf_process_activities wpa
WHERE wpa.instance_id = st.process_activity
AND st.item_type = 'OEOH'
AND wpa.activity_name = 'CLOSE_WAIT_FOR_L'
AND wpa.process_name = 'CLOSE_HEADER_PROCESS'
AND st.activity_status = 'NOTIFIED'
AND st.item_key = to_char(h.header_id)
AND h.header_id = 815719
AND h.open_flag = 'Y'
AND h.flow_status_code = 'BOOKED'
AND NOT EXISTS(SELECT 1
FROM oe_order_lines_all oel
WHERE oel.header_id = h.header_id
AND oel.open_flag = 'Y');
--
Begin
--
oe_debug_pub.setdebuglevel(5);
oe_debug_pub.debug_on;
oe_debug_pub.initialize;
l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
--
dbms_output.put_line('Debug File : '||l_file_val);
--
FOR header_rec IN header_cur LOOP
dbms_output.put_line('Closing Header:'||header_rec.header_id);
oe_debug_pub.add('Closing Header:'||header_rec.header_id);
--
OE_Standard_WF.OEOH_SELECTOR
(p_itemtype => 'OEOH'
,p_itemkey => to_char(header_rec.header_id)
,p_actid => 12345
,p_funcmode => 'SET_CTX'
,p_result => l_result
);
--
wf_engine.handleError('OEOH',header_rec.header_id, 'CLOSE_HEADER_PROCESS:CLOSE_HEADER', 'RETRY',NULL);
--
END LOOP;
--
COMMIT;
--
EXCEPTION
--
WHEN OTHERS THEN
oe_debug_pub.add(' Error is : '||sqlerrm);
dbms_output.put_line('Error :'||sqlerrm);
--
END;
/