[Home] [Help]
70: dd.inventory_item_id -- 3704188
71: from wsh_delivery_details dd,
72: wsh_delivery_assignments_v da,
73: wsh_delivery_legs dg,
74: wsh_new_deliveries dl,
75: wsh_trip_stops st,
76: mtl_serial_numbers_temp msnt
77: where st.batch_id = p_batch_id
78: and st.stop_location_id = dl.initial_pickup_location_id
110: dd.inventory_item_id -- 3704188
111: from wsh_delivery_details dd,
112: wsh_delivery_assignments_v da,
113: wsh_delivery_legs dg,
114: wsh_new_deliveries dl,
115: wsh_trip_stops st,
116: mtl_serial_numbers_temp msnt
117: where st.batch_id = p_batch_id
118: and st.stop_location_id = dl.initial_pickup_location_id
150: dd.inventory_item_id -- 3704188
151: from wsh_delivery_details dd,
152: wsh_delivery_assignments_v da,
153: wsh_delivery_legs dg,
154: wsh_new_deliveries dl,
155: wsh_trip_stops st,
156: mtl_serial_numbers msn
157: where st.batch_id = p_batch_id
158: and st.stop_location_id = dl.initial_pickup_location_id
190: dd.inventory_item_id -- 3704188
191: from wsh_delivery_details dd,
192: wsh_delivery_assignments_v da,
193: wsh_delivery_legs dg,
194: wsh_new_deliveries dl,
195: wsh_trip_stops st,
196: mtl_serial_numbers msn
197: where st.batch_id = p_batch_id
198: and st.stop_location_id = dl.initial_pickup_location_id
451: ( SELECT DISTINCT dd.transaction_temp_id
452: from wsh_delivery_details dd,
453: wsh_delivery_assignments da,
454: wsh_delivery_legs dg,
455: wsh_new_deliveries dl,
456: wsh_trip_stops st,
457: mtl_serial_numbers_temp msnt
458: where st.batch_id = p_batch_id
459: and st.stop_location_id = dl.initial_pickup_location_id
484: ( SELECT DISTINCT dd.transaction_temp_id
485: from wsh_delivery_details dd,
486: wsh_delivery_assignments da,
487: wsh_delivery_legs dg,
488: wsh_new_deliveries dl,
489: wsh_trip_stops st,
490: mtl_serial_numbers_temp msnt
491: where st.batch_id = p_batch_id
492: and st.stop_location_id = dl.initial_pickup_location_id
1149: CURSOR c_delivery_stop(p_delivery_id NUMBER) IS
1150: SELECT wts.stop_id
1151: FROM wsh_trip_stops wts,
1152: wsh_delivery_legs wdl,
1153: wsh_new_deliveries wnd
1154: WHERE wnd.delivery_id = p_delivery_id
1155: AND wdl.delivery_id = wnd.delivery_id
1156: AND wts.stop_id = wdl.pick_up_stop_id
1157: AND wts.stop_location_id = wnd.initial_pickup_location_id;
1178: -- for a given organization
1179: CURSOR c_stops_org(p_organization_id NUMBER) IS
1180: SELECT DISTINCT wts.stop_id
1181: FROM wsh_trip_stops wts,
1182: wsh_new_deliveries wnd,
1183: wsh_delivery_legs wdl
1184: WHERE wts.pending_interface_flag = 'Y'
1185: AND wdl.pick_up_stop_id = wts.stop_id
1186: AND wnd.initial_pickup_location_id = wts.stop_location_id
1193: CURSOR c_batch_stop(p_batch_id NUMBER, p_trip_type VARCHAR2)IS
1194: SELECT wts.stop_id
1195: FROM wsh_trip_stops wts,
1196: wsh_delivery_legs wdl,
1197: wsh_new_deliveries wnd,
1198: wsh_picking_batches wpb
1199: WHERE p_batch_id IS NOT NULL
1200: AND wnd.batch_id = p_batch_id
1201: AND wdl.delivery_id = wnd.delivery_id
1211:
1212: CURSOR pickup_oke_headers (p_stop_id in number) IS
1213: SELECT 1
1214: FROM wsh_delivery_legs dg,
1215: wsh_new_deliveries dl,
1216: wsh_trip_stops st
1217: WHERE st.stop_id = dg.pick_up_stop_id AND
1218: nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') AND -- J Inbound Logistics jckwok
1219: st.stop_id = p_stop_id AND
1233: wnd.organization_id,
1234: wnd.initial_pickup_location_id,
1235: wnd.delivery_scpod_wf_process,
1236: wnd.del_wf_interface_attr
1237: FROM wsh_new_deliveries wnd,
1238: wsh_delivery_legs wdl,
1239: wsh_trip_stops wts
1240: WHERE wnd.delivery_id = wdl.delivery_id
1241: AND wdl.pick_up_stop_id = p_stop_id
2739: --
2740: FROM wsh_delivery_details dd,
2741: wsh_delivery_assignments_v da,
2742: wsh_delivery_legs dg,
2743: wsh_new_deliveries dl,
2744: wsh_trip_stops st,
2745: wsh_trips tr,
2746: wsh_carriers wcv,
2747: wsh_delivery_details dd1,
2792:
2793: -- FP bug 4166635: cursor to look up delivery's freight carrier
2794: CURSOR c_freight_code_del(p_delivery_id number) is
2795: SELECT wc.freight_code
2796: FROM wsh_new_deliveries wnd,
2797: wsh_carriers wc
2798: WHERE wnd.delivery_id = p_delivery_id AND
2799: wc.carrier_id = wnd.carrier_id;
2800:
4023: -- bug 5736840
4024:
4025: CURSOR get_details (p_batch_id IN NUMBER) IS
4026: SELECT da.delivery_detail_id
4027: FROM wsh_delivery_assignments da , wsh_delivery_legs dg, wsh_new_deliveries dl, wsh_trip_stops st
4028: where dl.delivery_id = da.delivery_id
4029: AND da.delivery_id IS NOT NULL
4030: AND st.stop_id = dg.pick_up_stop_id
4031: AND st.batch_id = p_batch_id
4045: SELECT mti.picking_line_id
4046: FROM mtl_transactions_interface mti,
4047: wsh_delivery_assignments_v da ,
4048: wsh_delivery_legs dg,
4049: wsh_new_deliveries dl,
4050: wsh_trip_stops st
4051: WHERE mti.picking_line_id = da.delivery_detail_id
4052: AND dl.delivery_id = da.delivery_id
4053: AND st.stop_id = dg.pick_up_stop_id
4435: cursor assigned_line_total is
4436: SELECT count(*) total
4437: from wsh_delivery_details dd,
4438: wsh_delivery_assignments_v da,
4439: wsh_new_deliveries ds
4440: where dd.delivery_detail_id = da.delivery_detail_id
4441: and da.delivery_id = ds.delivery_id
4442: and ds.status_code NOT IN ('CL','IT','CO', 'SR', 'SC') /* Closed, In Transit, Confirmed */
4443: and da.delivery_id <> p_delivery_id
4540: cursor other_batch_delivery_details is
4541: select sum(nvl(wdd.picked_quantity, wdd.requested_quantity))
4542: from wsh_delivery_details wdd,
4543: wsh_delivery_assignments_v da,
4544: wsh_new_deliveries wnd
4545: where wdd.source_line_id=p_source_line_id and
4546: wdd.source_code = 'OE' and
4547: wdd.delivery_detail_id=da.delivery_detail_id and
4548: not exists (select 1 from wsh_delivery_legs lg, wsh_trip_stops st
4878: and dd.delivery_Detail_id in (
4879: SELECT da.delivery_detail_id
4880: FROM wsh_delivery_assignments_v da ,
4881: wsh_delivery_legs dg,
4882: wsh_new_deliveries dl,
4883: wsh_trip_stops st
4884: where dl.delivery_id = da.delivery_id AND
4885: da.delivery_id IS NOT NULL AND
4886: st.stop_id = dg.pick_up_stop_id AND
5032: SELECT DISTINCT stop_id
5033: FROM wsh_delivery_Details dd,
5034: wsh_delivery_assignments_v da ,
5035: wsh_delivery_legs dg,
5036: wsh_new_deliveries dl,
5037: wsh_trip_stops st
5038: WHERE st.stop_id = dg.pick_up_stop_id
5039: AND st.batch_id = p_batch_id
5040: AND st.stop_location_id = dl.initial_pickup_location_id
6248: and dd.delivery_detail_id in (
6249: SELECT /*+ no_unnest */ da.delivery_detail_id
6250: FROM wsh_delivery_assignments_v da ,
6251: wsh_delivery_legs dg,
6252: wsh_new_deliveries dl,
6253: wsh_trip_stops st
6254: where da.delivery_detail_id = dd.delivery_detail_id AND
6255: dl.delivery_id = da.delivery_id AND
6256: da.delivery_id IS NOT NULL AND
6332: where delivery_detail_id in (
6333: SELECT da.delivery_detail_id
6334: FROM wsh_delivery_assignments_v da ,
6335: wsh_delivery_legs dg,
6336: wsh_new_deliveries dl,
6337: wsh_trip_stops st,
6338: oe_order_lines_all ol
6339: where da.delivery_detail_id = dd.delivery_detail_id AND
6340: dl.delivery_id = da.delivery_id AND
6367: and dd.delivery_detail_id in (
6368: SELECT /*+ no_unnest */ da.delivery_detail_id
6369: FROM wsh_delivery_assignments_v da ,
6370: wsh_delivery_legs dg,
6371: wsh_new_deliveries dl,
6372: wsh_trip_stops st
6373: where da.delivery_detail_id = dd.delivery_detail_id AND
6374: dl.delivery_id = da.delivery_id AND
6375: da.delivery_id IS NOT NULL AND
6426: and dd.delivery_detail_id in (
6427: SELECT /*+ no_unnest */ da.delivery_detail_id
6428: FROM wsh_delivery_assignments_v da ,
6429: wsh_delivery_legs dg,
6430: wsh_new_deliveries dl,
6431: wsh_trip_stops st
6432: where da.delivery_detail_id = dd.delivery_detail_id AND
6433: dl.delivery_id = da.delivery_id AND
6434: da.delivery_id IS NOT NULL AND
6803: SELECT dd.source_line_id
6804: FROM wsh_delivery_Details dd,
6805: wsh_delivery_assignments_v da ,
6806: wsh_delivery_legs dg,
6807: wsh_new_deliveries dl,
6808: wsh_trip_stops st
6809: WHERE st.stop_id = dg.pick_up_stop_id AND
6810: st.batch_id = p_batch_id AND
6811: st.stop_location_id = dl.initial_pickup_location_id AND
6876: and wdd.source_line_set_id = c_source_line_set_id
6877: and not exists (
6878: select 'x'
6879: from wsh_delivery_assignments_v wda,
6880: wsh_new_deliveries wnd,
6881: wsh_delivery_legs wdl,
6882: wsh_trip_stops wts
6883: where wdd.delivery_detail_id = wda.delivery_detail_id
6884: and wda.delivery_id = wnd.delivery_id
6892: c_source_header_id NUMBER) IS
6893: select 'x'
6894: from wsh_delivery_details wdd,
6895: wsh_delivery_assignments_v wda,
6896: wsh_new_deliveries wnd
6897: where wdd.source_line_id = c_source_line_id
6898: and wdd.source_code = 'OE'
6899: and wdd.source_header_id = c_source_header_id
6900: and wdd.delivery_detail_id = wda.delivery_detail_id
7055: sum( nvl(dd.shipped_quantity2, 0 )) shipping_quantity2
7056: FROM wsh_delivery_Details dd,
7057: wsh_delivery_assignments_v da ,
7058: wsh_delivery_legs dg,
7059: wsh_new_deliveries dl,
7060: wsh_trip_stops st,
7061: oe_order_lines_all ol
7062: WHERE st.stop_id = dg.pick_up_stop_id AND
7063: st.batch_id = cp_batch_id AND
7121: sum( nvl(dd.shipped_quantity2, 0 )) total_shipped_quantity2
7122: FROM wsh_delivery_Details dd,
7123: wsh_delivery_assignments_v da ,
7124: wsh_delivery_legs dg,
7125: wsh_new_deliveries dl,
7126: wsh_trip_stops st,
7127: oe_order_lines_all ol
7128: WHERE st.stop_id = dg.pick_up_stop_id AND
7129: st.batch_id = cp_batch_id AND
7595: l_tot_dd_shp_qty
7596: FROM wsh_delivery_Details dd,
7597: wsh_delivery_assignments_v da ,
7598: wsh_delivery_legs dg,
7599: wsh_new_deliveries dl,
7600: wsh_trip_stops st
7601: WHERE st.stop_id = dg.pick_up_stop_id
7602: AND st.batch_id = P_batch_id
7603: AND st.stop_location_id = dl.initial_pickup_location_id
8380: where delivery_detail_id in (
8381: SELECT da.delivery_detail_id
8382: FROM wsh_delivery_assignments_v da ,
8383: wsh_delivery_legs dg,
8384: wsh_new_deliveries dl,
8385: wsh_trip_stops st
8386: where dl.delivery_id = da.delivery_id AND
8387: da.delivery_id IS NOT NULL AND
8388: st.stop_id = dg.pick_up_stop_id AND