[Home] [Help]
125: --Bug 8467875
126: from wsh_delivery_details dd,
127: wsh_delivery_assignments_v da,
128: wsh_delivery_legs dg,
129: wsh_new_deliveries dl,
130: wsh_trip_stops st,
131: mtl_serial_numbers_temp msnt
132: where st.batch_id = p_batch_id
133: and st.stop_location_id = dl.initial_pickup_location_id
220: --Bug 8467875
221: from wsh_delivery_details dd,
222: wsh_delivery_assignments_v da,
223: wsh_delivery_legs dg,
224: wsh_new_deliveries dl,
225: wsh_trip_stops st,
226: mtl_serial_numbers_temp msnt
227: where st.batch_id = p_batch_id
228: and st.stop_location_id = dl.initial_pickup_location_id
315: --Bug 8467875
316: from wsh_delivery_details dd,
317: wsh_delivery_assignments_v da,
318: wsh_delivery_legs dg,
319: wsh_new_deliveries dl,
320: wsh_trip_stops st,
321: mtl_serial_numbers msn
322: where st.batch_id = p_batch_id
323: and st.stop_location_id = dl.initial_pickup_location_id
410: --Bug 8467875
411: from wsh_delivery_details dd,
412: wsh_delivery_assignments_v da,
413: wsh_delivery_legs dg,
414: wsh_new_deliveries dl,
415: wsh_trip_stops st,
416: mtl_serial_numbers msn
417: where st.batch_id = p_batch_id
418: and st.stop_location_id = dl.initial_pickup_location_id
894: ( SELECT DISTINCT dd.transaction_temp_id
895: from wsh_delivery_details dd,
896: wsh_delivery_assignments da,
897: wsh_delivery_legs dg,
898: wsh_new_deliveries dl,
899: wsh_trip_stops st,
900: mtl_serial_numbers_temp msnt
901: where st.batch_id = p_batch_id
902: and st.stop_location_id = dl.initial_pickup_location_id
927: ( SELECT DISTINCT dd.transaction_temp_id
928: from wsh_delivery_details dd,
929: wsh_delivery_assignments da,
930: wsh_delivery_legs dg,
931: wsh_new_deliveries dl,
932: wsh_trip_stops st,
933: mtl_serial_numbers_temp msnt
934: where st.batch_id = p_batch_id
935: and st.stop_location_id = dl.initial_pickup_location_id
1592: CURSOR c_delivery_stop(p_delivery_id NUMBER) IS
1593: SELECT wts.stop_id
1594: FROM wsh_trip_stops wts,
1595: wsh_delivery_legs wdl,
1596: wsh_new_deliveries wnd
1597: WHERE wnd.delivery_id = p_delivery_id
1598: AND wdl.delivery_id = wnd.delivery_id
1599: AND wts.stop_id = wdl.pick_up_stop_id
1600: AND wts.stop_location_id = wnd.initial_pickup_location_id;
1621: -- for a given organization
1622: CURSOR c_stops_org(p_organization_id NUMBER) IS
1623: SELECT DISTINCT wts.stop_id
1624: FROM wsh_trip_stops wts,
1625: wsh_new_deliveries wnd,
1626: wsh_delivery_legs wdl
1627: WHERE wts.pending_interface_flag = 'Y'
1628: AND wdl.pick_up_stop_id = wts.stop_id
1629: AND wnd.initial_pickup_location_id = wts.stop_location_id
1636: CURSOR c_batch_stop(p_batch_id NUMBER, p_trip_type VARCHAR2)IS
1637: SELECT wts.stop_id
1638: FROM wsh_trip_stops wts,
1639: wsh_delivery_legs wdl,
1640: wsh_new_deliveries wnd,
1641: wsh_picking_batches wpb
1642: WHERE p_batch_id IS NOT NULL
1643: AND wnd.batch_id = p_batch_id
1644: AND wdl.delivery_id = wnd.delivery_id
1654:
1655: CURSOR pickup_oke_headers (p_stop_id in number) IS
1656: SELECT 1
1657: FROM wsh_delivery_legs dg,
1658: wsh_new_deliveries dl,
1659: wsh_trip_stops st
1660: WHERE st.stop_id = dg.pick_up_stop_id AND
1661: nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') AND -- J Inbound Logistics jckwok
1662: st.stop_id = p_stop_id AND
1676: wnd.organization_id,
1677: wnd.initial_pickup_location_id,
1678: wnd.delivery_scpod_wf_process,
1679: wnd.del_wf_interface_attr
1680: FROM wsh_new_deliveries wnd,
1681: wsh_delivery_legs wdl,
1682: wsh_trip_stops wts
1683: WHERE wnd.delivery_id = wdl.delivery_id
1684: AND wdl.pick_up_stop_id = p_stop_id
3348: --
3349: FROM wsh_delivery_details dd,
3350: wsh_delivery_assignments_v da,
3351: wsh_delivery_legs dg,
3352: wsh_new_deliveries dl,
3353: wsh_trip_stops st,
3354: wsh_trips tr,
3355: wsh_carriers wcv,
3356: wsh_delivery_details dd1,
3402:
3403: -- FP bug 4166635: cursor to look up delivery's freight carrier
3404: CURSOR c_freight_code_del(p_delivery_id number) is
3405: SELECT wc.freight_code
3406: FROM wsh_new_deliveries wnd,
3407: wsh_carriers wc
3408: WHERE wnd.delivery_id = p_delivery_id AND
3409: wc.carrier_id = wnd.carrier_id;
3410:
4935: -- bug 5736840
4936:
4937: CURSOR get_details (p_batch_id IN NUMBER) IS
4938: SELECT da.delivery_detail_id
4939: FROM wsh_delivery_assignments da , wsh_delivery_legs dg, wsh_new_deliveries dl, wsh_trip_stops st
4940: where dl.delivery_id = da.delivery_id
4941: AND da.delivery_id IS NOT NULL
4942: AND st.stop_id = dg.pick_up_stop_id
4943: AND st.batch_id = p_batch_id
4957: SELECT mti.picking_line_id
4958: FROM mtl_transactions_interface mti,
4959: wsh_delivery_assignments_v da ,
4960: wsh_delivery_legs dg,
4961: wsh_new_deliveries dl,
4962: wsh_delivery_details wdd,
4963: wsh_trip_stops st
4964: WHERE mti.picking_line_id = da.delivery_detail_id
4965: AND wdd.delivery_detail_id = da.delivery_detail_id
4975: CURSOR c_get_rtv_deliveries (p_batch_id IN NUMBER) IS
4976: SELECT DISTINCT wnd.delivery_id
4977: from wsh_delivery_details wdd,
4978: wsh_delivery_assignments_v wda,
4979: wsh_new_deliveries wnd,
4980: wsh_delivery_legs wdl,
4981: wsh_trip_stops wts
4982: where wdd.source_code = 'RTV'
4983: and nvl(wdd.inv_interfaced_flag, 'N') = 'N'
5420: cursor assigned_line_total is
5421: SELECT count(*) total
5422: from wsh_delivery_details dd,
5423: wsh_delivery_assignments_v da,
5424: wsh_new_deliveries ds
5425: where dd.delivery_detail_id = da.delivery_detail_id
5426: and da.delivery_id = ds.delivery_id
5427: and ds.status_code NOT IN ('CL','IT','CO', 'SR', 'SC') /* Closed, In Transit, Confirmed */
5428: and da.delivery_id <> p_delivery_id
5527: -- muom
5528: select sum(decode(p_fulfill_base,'S',nvl(wdd.picked_quantity2, wdd.requested_quantity2),nvl(wdd.picked_quantity, wdd.requested_quantity)))
5529: from wsh_delivery_details wdd,
5530: wsh_delivery_assignments_v da,
5531: wsh_new_deliveries wnd
5532: where wdd.source_line_id=p_source_line_id and
5533: wdd.source_code = 'OE' and
5534: wdd.delivery_detail_id=da.delivery_detail_id and
5535: not exists (select 1 from wsh_delivery_legs lg, wsh_trip_stops st
5812: AND wdd.source_line_set_id = c_source_line_set_id
5813: AND NOT EXISTS
5814: ( SELECT 'x'
5815: FROM wsh_delivery_assignments_v wda,
5816: wsh_new_deliveries wnd ,
5817: wsh_delivery_legs wdl ,
5818: wsh_trip_stops wts
5819: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
5820: AND wda.delivery_id = wnd.delivery_id
5827: CURSOR c_picked_dd(c_source_line_id NUMBER, c_source_header_id NUMBER) IS
5828: SELECT 'x'
5829: FROM wsh_delivery_details wdd ,
5830: wsh_delivery_assignments_v wda,
5831: wsh_new_deliveries wnd
5832: WHERE wdd.source_line_id = c_source_line_id
5833: AND wdd.source_code = 'OE'
5834: AND wdd.source_header_id = c_source_header_id
5835: AND wdd.delivery_detail_id = wda.delivery_detail_id
5906: l_client_id -- LSP PROJECT
5907: FROM wsh_delivery_Details dd ,
5908: wsh_delivery_assignments_v da ,
5909: wsh_delivery_legs dg ,
5910: wsh_new_deliveries dl ,
5911: wsh_trip_stops st
5912: WHERE st.stop_id = dg.pick_up_stop_id
5913: AND st.batch_id = P_batch_id
5914: AND st.stop_location_id = dl.initial_pickup_location_id
6402: and dd.delivery_Detail_id in (
6403: SELECT da.delivery_detail_id
6404: FROM wsh_delivery_assignments_v da ,
6405: wsh_delivery_legs dg,
6406: wsh_new_deliveries dl,
6407: wsh_trip_stops st
6408: where dl.delivery_id = da.delivery_id AND
6409: da.delivery_id IS NOT NULL AND
6410: st.stop_id = dg.pick_up_stop_id AND
6557: SELECT DISTINCT stop_id
6558: FROM wsh_delivery_Details dd,
6559: wsh_delivery_assignments_v da ,
6560: wsh_delivery_legs dg,
6561: wsh_new_deliveries dl,
6562: wsh_trip_stops st
6563: WHERE st.stop_id = dg.pick_up_stop_id
6564: AND st.batch_id = p_batch_id
6565: AND st.stop_location_id = dl.initial_pickup_location_id
7781: and dd.delivery_detail_id in (
7782: SELECT /*+ no_unnest */ da.delivery_detail_id
7783: FROM wsh_delivery_assignments_v da ,
7784: wsh_delivery_legs dg,
7785: wsh_new_deliveries dl,
7786: wsh_trip_stops st
7787: where da.delivery_detail_id = dd.delivery_detail_id AND
7788: dl.delivery_id = da.delivery_id AND
7789: da.delivery_id IS NOT NULL AND
7867: where delivery_detail_id in (
7868: SELECT da.delivery_detail_id
7869: FROM wsh_delivery_assignments_v da ,
7870: wsh_delivery_legs dg,
7871: wsh_new_deliveries dl,
7872: wsh_trip_stops st,
7873: oe_order_lines_all ol
7874: where da.delivery_detail_id = dd.delivery_detail_id AND
7875: dl.delivery_id = da.delivery_id AND
7907: and dd.delivery_detail_id in (
7908: SELECT /*+ no_unnest */ da.delivery_detail_id
7909: FROM wsh_delivery_assignments_v da ,
7910: wsh_delivery_legs dg,
7911: wsh_new_deliveries dl,
7912: wsh_trip_stops st
7913: where da.delivery_detail_id = dd.delivery_detail_id AND
7914: dl.delivery_id = da.delivery_id AND
7915: da.delivery_id IS NOT NULL AND
7970: and dd.delivery_detail_id in (
7971: SELECT /*+ no_unnest */ da.delivery_detail_id
7972: FROM wsh_delivery_assignments_v da ,
7973: wsh_delivery_legs dg,
7974: wsh_new_deliveries dl,
7975: wsh_trip_stops st
7976: where da.delivery_detail_id = dd.delivery_detail_id AND
7977: dl.delivery_id = da.delivery_id AND
7978: da.delivery_id IS NOT NULL AND
8348: SELECT dd.source_line_id
8349: FROM wsh_delivery_Details dd,
8350: wsh_delivery_assignments_v da ,
8351: wsh_delivery_legs dg,
8352: wsh_new_deliveries dl,
8353: wsh_trip_stops st
8354: WHERE st.stop_id = dg.pick_up_stop_id AND
8355: st.batch_id = p_batch_id AND
8356: st.stop_location_id = dl.initial_pickup_location_id AND
8427: and wdd.source_line_set_id = c_source_line_set_id
8428: and not exists (
8429: select 'x'
8430: from wsh_delivery_assignments_v wda,
8431: wsh_new_deliveries wnd,
8432: wsh_delivery_legs wdl,
8433: wsh_trip_stops wts
8434: where wdd.delivery_detail_id = wda.delivery_detail_id
8435: and wda.delivery_id = wnd.delivery_id
8443: c_source_header_id NUMBER) IS
8444: select 'x'
8445: from wsh_delivery_details wdd,
8446: wsh_delivery_assignments_v wda,
8447: wsh_new_deliveries wnd
8448: where wdd.source_line_id = c_source_line_id
8449: and wdd.source_code = 'OE'
8450: and wdd.source_header_id = c_source_header_id
8451: and wdd.delivery_detail_id = wda.delivery_detail_id
8616: item_id => dd.inventory_item_id)))) shipped_quantity
8617: FROM wsh_delivery_Details dd,
8618: wsh_delivery_assignments_v da ,
8619: wsh_delivery_legs dg,
8620: wsh_new_deliveries dl,
8621: wsh_trip_stops st,
8622: oe_order_lines_all ol
8623: WHERE st.stop_id = dg.pick_up_stop_id AND
8624: st.batch_id = cp_batch_id AND
8697: item_id => dd.inventory_item_id)))) total_om_shipped_quantity
8698: FROM wsh_delivery_Details dd,
8699: wsh_delivery_assignments_v da ,
8700: wsh_delivery_legs dg,
8701: wsh_new_deliveries dl,
8702: wsh_trip_stops st,
8703: oe_order_lines_all ol
8704: WHERE st.stop_id = dg.pick_up_stop_id AND
8705: st.batch_id = cp_batch_id AND
9763: where delivery_detail_id in (
9764: SELECT da.delivery_detail_id
9765: FROM wsh_delivery_assignments_v da ,
9766: wsh_delivery_legs dg,
9767: wsh_new_deliveries dl,
9768: wsh_trip_stops st
9769: where dl.delivery_id = da.delivery_id AND
9770: da.delivery_id IS NOT NULL AND
9771: st.stop_id = dg.pick_up_stop_id AND
10924: SELECT initial_pickup_date
10925: INTO l_pick_up_date
10926: FROM wsh_delivery_details wdd ,
10927: wsh_delivery_assignments wda,
10928: wsh_new_deliveries wnd
10929: WHERE wdd.source_line_id = line_rec.source_line_id
10930: AND wda.delivery_detail_id = wdd.delivery_detail_id
10931: AND wnd.delivery_id = wda.delivery_id
10932: AND ROWNUM =1;