When I run across the same issue a few days ago Oracle Support quickly identified the problem and ... recommended a patch that would take weeks to test and put into production.
To work around the issue I have prepared the below script. Use it for a single stuck OM line at a time and only if there are going to be no more partial shipments for it :
declare
cursor c is
select line_id from oe_order_lines_all where line_id in (
select distinct(source_line_id) from wsh_delivery_details where delivery_detail_id in (147432,147710)
and flow_status_code='AWAITING_SHIPPING'
);
begin
FOR r in c loop
-- progress the workflow
wf_engine.CompleteActivity('OEOL', to_char(r.line_id),'SHIP_LINE', 'SHIP_CONFIRM');
--mark shipment as itnerfaced
to OM so you can run Trip Stop for Inventory
update wsh_delivery_details set
oe_interfaced_flag='Y' where source_line_id=r.line_id;
-- update order line with
quantity shipped so that it can be invoiced,
-- leaving this out would
result in Invoice Interface not Eligible and no invoice
update oe_order_lines_all set
shipped_quantity=
(select sum(shipped_quantity) from wsh_delivery_details where source_line_id=r.line_id)
where line_id=r.line_id;
where line_id=r.line_id;
dbms_output.put_line(r.line_id||' processed');
end loop;
Exception
when others then null;
end;
This will push the OM line past Ship Confirm and create invoice in the end. To be safe you may consider adding a 'not exist' statement in the cursor for the wsh_delivery_details t
o eliminate order lines that have delivery details in status other than Shipped or Cancelled.
