How to check Interface Trip Stop problems

SOURCE

Most of the Interface Trip Stop problems will require a datafix from Development. The following are some of the scenario that can appear for Oracle Order Management Interface issues. The scenario also includes the query which needs to be run where the output will need to be included in the datafix bug.

1) Order line is closed but the delivery detail is not yet OM interfaced:

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id, wdd.released_status,wdd.creation_date
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.released_status <> 'D'
and wdd.oe_interfaced_flag in ('N','P')
and wdd.source_line_id = ol.line_id
and trunc(wdd.creation_date) > sysdate -365
and ol.open_flag = 'N'
and ol.cancelled_flag = 'N'
and not exists ( select 'x'
from wsh_delivery_details wdd1
where wdd1.source_code = 'OE'
and wdd1.source_line_id = wdd.source_line_id
and wdd1.oe_interfaced_flag = 'Y')
order by 1,2,3;

2) Order line is shipped and one of the WDDs is OM interfaced but other WDDs are not OM interfaced.

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,wdd.released_status,wdd.creation_date
from wsh_delivery_details wdd
where wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'N'
and wdd.released_status <> 'D'
and trunc(wdd.creation_date) > sysdate - 365
and exists ( select 'x'
from wsh_delivery_details wdd1
where wdd1.source_code = 'OE'
and wdd.source_line_id = wdd1.source_line_id
and wdd1.oe_interfaced_flag = 'Y')
order by 1,2,3;

Comments: Please provide the raw trace file and debug log file of Interface Trip Stop process (with debug settings as per Metalink Note 290432.1) for a similar order(with same order type, item where user ships partial quantity). These outputs are needed to verify if there are any intermediate commits either in the custom code or supported package extensions/hooks.
Apply split_line_trigger.sql to stop future occurences of this issue. Log a bug once a process(like ITS) errors out because of this trigger.

3) Model components are interfaced to OM but the Model line is failing OM interface.

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and trunc(wdd.creation_date) > sysdate-365
and wdd.source_line_id = wdd.top_model_line_id
and wdd.oe_interfaced_flag in ('N','P')
and wdd.source_line_id = ol.line_id
and ol.open_flag = 'N'
and not exists (select 'x'
from wsh_delivery_details wdd1
where wdd.top_model_line_id = wdd1.top_model_line_id
and wdd1.source_code = 'OE'
and wdd1.released_status <> 'D'
and wdd1.oe_interfaced_flag = 'Y'
and wdd1.released_status = 'C'
and wdd1.source_line_id <> wdd.top_model_line_id)
order by 1;

4) Trip Stop/delivery do not show up in ITS LOV but some of the details are not OM/INV interfaced.

select distinct wdd.source_header_number, wts.stop_id, wts.trip_id
from wsh_delivery_details wdd,wsh_delivery_assignments wda,wsh_new_deliveries wnd, wsh_delivery_legs wdl,wsh_trip_stops wts
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and (wdd.oe_interfaced_flag in ('N','P') or wdd.inv_interfaced_flag in ('N','P'))
and wdd.delivery_detail_id = wda.delivery_detail_id
and trunc(wdd.creation_date) > sysdate -365
and wda.delivery_id = wnd.delivery_id
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and nvl(wts.pending_interface_flag,'X') <> 'Y'
order by 1;

5) Inventory Interface/Interface Trip stop log file shows 'Requisition Line not found' message and the details are not INV interfaced.

select wdd.source_header_number,wdd.source_header_id,wdd.source_line_id,wdd.delivery_detail_id
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.source_line_id = ol.line_id
and trunc(wdd.creation_date) > sysdate -365
and wdd.released_status <> 'D'
and nvl(wdd.inv_interfaced_flag,'N') in ('N','P')
and wdd.source_line_id = ol.line_id
and ol.source_document_type_id = 10
and not exists ( select 'x'
from po_requisition_lines_all pl,
po_req_distributions_all pd
where pl.requisition_line_id = ol.source_document_line_id
and pl.requisition_header_id = ol.source_document_id
and pl.requisition_line_id = pd.requisition_line_id)
order by 1,3,4;

6) ITS completes with 'Batch is not yet fully interfaced to OM'.
You need to use one of the following queries depending on the symptoms.

i) Use this query if the shipped quantity on details is 0/null and the released status is 'Shipped'.
select distinct wdd.source_header_number, wdd.delivery_detail_id,wdd.creation_date
from wsh_delivery_details wdd
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and nvl(wdd.shipped_quantity,0) = 0
and wdd.oe_interfaced_flag = 'N'
and trunc(wdd.creation_date) > sysdate -365
order by 1,2;

Comments: Apply trigger ship_qty_zero_trig.sql to stop future occurences of this issue. Log a bug once a process(like 'Ship Confirm'/'Close Stop') errors out because of this trigger.

ii) Otherwise, use this one
select wdd.source_header_number,wdd.source_line_id,ol.shipping_quantity,ol.flow_status_code, wdd.delivery_detail_id,wdd.creation_date
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and wdd.source_line_id = ol.line_id
and wdd.oe_interfaced_flag = 'P'
and trunc(wdd.creation_date) > sysdate -365
order by 1,2,5;

7) Interface trip Stops program errors out with "for this transaction row the serial records are missing".
select wdd.source_header_number , wdd.delivery_detail_id, wdd.shipped_quantity,
wdd.transaction_temp_id, msi.serial_number_control_code
from wsh_delivery_details wdd, mtl_system_items msi
where wdd.released_status = 'C'
and wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.inv_interfaced_flag in ('N','P')
and wdd.inventory_item_id = msi.inventory_item_id
and wdd.organization_id = msi.organization_id
and wdd.transaction_temp_id is null
and exists (
select 'x'
from wsh_serial_numbers wsn where wsn.delivery_detail_id = wdd.delivery_detail_id );