Below is Query to get Order line, Delivery Details , Delivery associated with a Trip , that is not yet interfaced.
Select L.Line_Id,l.Shipped_Quantity,l.flow_status_code,wnd.delivery_id
From Oe_Order_Lines_All L,
Wsh_Delivery_Details Wdd,
Wsh_New_Deliveries Wnd,
wsh_delivery_assignments wda,
Wsh_Delivery_Legs Wdl,
wsh_trip_stops wts
Where L.Line_Id = Wdd.Source_Line_Id
And L.Open_Flag = 'Y'
And Nvl(L.Shipped_Quantity,0) = 0
and nvl(l.fulfilled_quantity,0)= 0
And Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_Id
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'
And Wnd.Status_Code <> 'OP'
and wts.stop_id = &Enter_Trip_id
And Wdd.Source_Code='OE'
And Wdd.Released_Status = 'C'
And Nvl(Wdd.Oe_Interfaced_Flag,'N')='N'
And Nvl(Wdd.Inv_Interfaced_Flag,'N')='N'
Thanks for query , this again saved me of time
ReplyDeleteSR