Thursday, July 17, 2014

Workflow stuck in Ship Confirm, Trip Stop fails OM interface

During my carrier I often run across an annoying scenario where the entire order line was shipped but Trip Stop failed interfacing to OM and workflow would not progress past Ship Confirm.
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;

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. 

No comments:

Post a Comment