Below is query to Get the Order lines where Order lines are Closed , Workflow is also closed , but ITS is not yet executed.
SELECT line_id, header_id,
ordered_quantity
FROM oe_order_lines_all l,
wf_items wi
WHERE l.open_flag = 'N'
AND Nvl(l.cancelled_flag,'N') = 'N'
AND l.booked_flag = 'Y'
AND l.shipping_interfaced_flag = 'Y'
AND Nvl(l.shipped_quantity,0) = 0
AND Nvl(l.fulfilled_quantity,0) = 0
AND l.fulfilled_flag = 'Y'
AND wi.item_type = 'OEOL'
AND wi.item_key = To_Char(l.line_id)
AND wi.end_date IS NOT NULL
AND EXISTS
(
SELECT 1
FROM wsh_delivery_details
WHERE source_code = 'OE'
AND released_status = 'C'
AND source_line_id = l.line_id
AND Nvl(oe_interfaced_flag,'N') = 'N'
AND Nvl(inv_interfaced_flag,'N') = 'N'
);
No comments:
Post a Comment