[Home] [Help]
69: msnt.attribute15, -- End of Bug 3628620
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
109: msnt.attribute15, -- End of Bug 3628620
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
149: msn.attribute15, -- End of Bug 3628620
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
189: msn.attribute15, -- End of Bug 3628620
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
450: WHERE transaction_temp_id IN
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
483: WHERE transaction_temp_id IN
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
870: CURSOR get_stops(p_batch_id NUMBER) is
871: SELECT DISTINCT wts.stop_id
872: FROM wsh_trip_stops wts,
873: wsh_trip_stops wts2,
874: wsh_delivery_legs wdl,
875: wsh_delivery_legs wdl2,
876: wsh_delivery_assignments_v wda,
877: wsh_delivery_assignments_v wda2,
878: wsh_delivery_details wdd,
871: SELECT DISTINCT wts.stop_id
872: FROM wsh_trip_stops wts,
873: wsh_trip_stops wts2,
874: wsh_delivery_legs wdl,
875: wsh_delivery_legs wdl2,
876: wsh_delivery_assignments_v wda,
877: wsh_delivery_assignments_v wda2,
878: wsh_delivery_details wdd,
879: wsh_delivery_details wdd2
1148: -- Lookup stop for this delivery
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
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
1187: AND wdl.delivery_id = wnd.delivery_id
1192:
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
1210: NULL,2,3);
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
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
1242: AND wts.stop_id = wdl.pick_up_stop_id
2738: dd.ship_to_site_use_id ship_to_site_use_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,
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
4044: CURSOR l_get_picking_ln_id_csr (p_batch_id IN NUMBER) IS
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
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
4549: where st.batch_id= p_batch_id and
4550: st.stop_id = lg.pick_up_stop_id and
4551: nvl(da.delivery_id,0)= lg.delivery_id)
4552: and wnd.delivery_id(+) = da.delivery_id
4877: and nvl(inv_interfaced_flag , 'N') <> 'X'
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
5031: CURSOR c_failed_stops (p_batch_id NUMBER) IS
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
5451: MINUS
5452: SELECT wdd.delivery_detail_id
5453: from wsh_delivery_details wdd,
5454: wsh_delivery_assignments_v wda,
5455: wsh_delivery_legs wdl,
5456: wsh_trip_stops wts
5457: where wts.batch_id = p_batch_id
5458: and wdl.pick_up_stop_id = wts.stop_id
5459: and wda.delivery_id = wdl.delivery_id
5473: MINUS
5474: select wdd.delivery_detail_id
5475: from wsh_delivery_details wdd,
5476: wsh_delivery_assignments_v wda,
5477: wsh_delivery_legs wdl,
5478: wsh_trip_stops wts
5479: where wts.batch_id = p_batch_id
5480: and wdl.pick_up_stop_id = wts.stop_id
5481: and wda.delivery_id = wdl.delivery_id
5493: MINUS
5494: SELECT wdd.delivery_detail_id
5495: from wsh_delivery_details wdd,
5496: wsh_delivery_assignments_v wda,
5497: wsh_delivery_legs wdl,
5498: wsh_trip_stops wts
5499: where wts.batch_id = p_batch_id
5500: and wdl.pick_up_stop_id = wts.stop_id
5501: and wda.delivery_id = wdl.delivery_id
5967: CURSOR c_get_stop_lines (p_stop_id NUMBER) IS
5968: SELECT wdd.source_line_id
5969: FROM wsh_delivery_details wdd,
5970: wsh_trip_stops wts,
5971: wsh_delivery_legs wdl,
5972: wsh_delivery_assignments_v wda
5973: WHERE wts.stop_id = p_stop_id
5974: AND wdl.pick_up_stop_id = wts.stop_id
5975: AND wdl.delivery_id = wda.delivery_id
6247: and released_status <> l_released_status
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
6331: set oe_interfaced_flag = l_oe_interfaced_flag
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
6366: and released_status <> l_released_status
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
6425: and released_status <> l_released_status
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
6802: CURSOR lock_delivery_line(p_batch_id NUMBER,c_source_header_id NUMBER,c_source_line_id NUMBER) IS
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
6831: AND wdd.released_status <> 'D'
6832: AND Wdd.delivery_detail_id in (
6833: SELECT wda.delivery_detail_id
6834: FROM wsh_trip_stops wts,
6835: wsh_delivery_legs wdl,
6836: wsh_delivery_assignments_v wda
6837: where wts.batch_id = c_batch_id
6838: AND wts.stop_id = wdl.pick_up_stop_id
6839: AND wdl.delivery_id = wda.delivery_id)
6849: AND wdd.released_status <> 'D'
6850: AND Wdd.delivery_detail_id in (
6851: SELECT wda.delivery_detail_id
6852: FROM wsh_trip_stops wts,
6853: wsh_delivery_legs wdl,
6854: wsh_delivery_assignments_v wda
6855: where wts.batch_id = c_batch_id
6856: AND wts.stop_id = wdl.pick_up_stop_id
6857: AND wdl.delivery_id = wda.delivery_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
6885: and wda.delivery_id is not null
6964: from wsh_delivery_details
6965: where source_line_id in (select source_line_id
6966: from wsh_delivery_details wdd,
6967: wsh_delivery_assignments_v wda,
6968: wsh_delivery_legs wdl,
6969: wsh_trip_stops wts
6970: where wts.batch_id = cp_batch_id
6971: and wdl.pick_up_stop_id = wts.stop_id
6972: and wda.delivery_id = wdl.delivery_id
6977: and (delivery_detail_id not in
6978: (select wdd.delivery_detail_id
6979: from wsh_delivery_details wdd,
6980: wsh_delivery_assignments_v wda,
6981: wsh_delivery_legs wdl,
6982: wsh_trip_stops wts
6983: where wts.batch_id = cp_batch_id
6984: and wdl.pick_up_stop_id = wts.stop_id
6985: and wda.delivery_id = wdl.delivery_id
6999:
7000: CURSOR check_bulk_csr (cp_batch_id NUMBER) IS
7001: select 'X'
7002: from wsh_trip_stops wts,
7003: wsh_delivery_legs wdl,
7004: wsh_delivery_assignments wda ,
7005: wsh_delivery_details wdd
7006: where wts.batch_id = cp_batch_id
7007: and wdl.pick_up_stop_id = wts.stop_id
7017: and wdd2.released_status <> 'D'
7018: and wdd2.delivery_detail_id NOT IN
7019: (select wda3.delivery_detail_id
7020: from wsh_delivery_assignments wda3 ,
7021: wsh_delivery_legs wdl3,
7022: wsh_trip_stops wts3,
7023: wsh_delivery_details wdd3
7024: where wts3.batch_id = cp_batch_id
7025: and wdl3.pick_up_stop_id = wts3.stop_id
7054: sum( nvl(dd.shipped_quantity,0) ) shipping_quantity,
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
7120: sum( nvl(dd.shipped_quantity, 0 )) total_shipped_quantity ,
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
7594: INTO l_tot_dd_req_qty,
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
8379: set inv_interfaced_flag = 'P'
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
8779: /* H integration: added 'WSH' to validate for inventory interface wrudge */
8780: CURSOR c_lines_not_interfaced(p_batch_id NUMBER) IS
8781: SELECT wdd.delivery_detail_id
8782: FROM wsh_trip_stops wts,
8783: wsh_delivery_legs wdl,
8784: wsh_delivery_assignments_v wda,
8785: wsh_delivery_details wdd
8786: WHERE wdd.inv_interfaced_flag IN ('N', 'P')
8787: AND wts.batch_id = p_batch_id
8847: FUNCTION OM_INTERFACED ( p_batch_id in number ) RETURN BOOLEAN is
8848: CURSOR c_lines_not_interfaced(p_batch_id NUMBER) IS
8849: SELECT wdd.delivery_detail_id
8850: FROM wsh_trip_stops wts,
8851: wsh_delivery_legs wdl,
8852: wsh_delivery_assignments_v wda,
8853: wsh_delivery_details wdd
8854: WHERE wdd.oe_interfaced_flag <> 'Y'
8855: AND wts.batch_id = p_batch_id