[Home] [Help]
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
79: and dg.delivery_id = dl.delivery_id
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
119: and dg.delivery_id = dl.delivery_id
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
159: and dg.delivery_id = dl.delivery_id
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
199: and dg.delivery_id = dl.delivery_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
460: and dg.delivery_id = dl.delivery_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
493: and dg.delivery_id = dl.delivery_id
743: l_stops_in_batch NUMBER;
744: --
745: CURSOR c_get_num_stops(p_batch_id NUMBER) IS
746: SELECT count(*)
747: FROM wsh_trip_stops
748: WHERE batch_id = p_batch_id;
749: --
750: BEGIN
751: --
868:
869:
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,
869:
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,
924: -- if there are some stops removed from previous batch then add them to
925: -- the current batch also add these stops to the x_stop_tab( first add
926: -- them to l_stop_tab)
927: --
928: UPDATE wsh_trip_stops
929: SET batch_id = p_batch_id
930: WHERE stop_id = x_split_stops(x);
931:
932: IF l_debug_on THEN
966: END IF ; --}
967: END LOOP; --}
968:
969: IF l_found THEN --{
970: UPDATE wsh_trip_stops
971: SET batch_id = NULL
972: WHERE stop_id = l_stop_tab(x)
973: AND batch_id = p_batch_id;
974:
1125:
1126: -- bug 2657859 frontport bug 2630535: avoid deadlocks
1127: CURSOR lock_row ( p_stop_id in NUMBER, p_flag in VARCHAR2 ) IS
1128: SELECT stop_id
1129: FROM wsh_trip_stops
1130: WHERE stop_id = p_stop_id
1131: AND pending_interface_flag = p_flag
1132: FOR UPDATE NOWAIT;
1133:
1132: FOR UPDATE NOWAIT;
1133:
1134: CURSOR lock_batch ( p_batch_id in NUMBER, p_flag in VARCHAR2 ) IS
1135: SELECT stop_id
1136: FROM wsh_trip_stops
1137: WHERE batch_id = p_batch_id
1138: AND pending_interface_flag = p_flag
1139: FOR UPDATE NOWAIT;
1140:
1147:
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
1159: -- bug 3642085
1160: -- Find closed stops that have pick up deliveries with lines to interface
1161: CURSOR c_stop_to_interface(p_trip_stop_id NUMBER) IS
1162: SELECT wts.stop_id
1163: FROM wsh_trip_stops wts
1164: WHERE wts.stop_id = p_trip_stop_id
1165: AND wts.pending_interface_flag = 'Y'
1166: AND nvl(wts.SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M');
1167:
1167:
1168: -- Find closed stops that have pick up deliveries with lines to interface
1169: CURSOR c_all_elig_stops_to_interface IS
1170: SELECT wts.stop_id
1171: FROM wsh_trip_stops wts
1172: WHERE wts.pending_interface_flag = 'Y'
1173: AND nvl(wts.SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M') -- J Inbound Logistics jckwok
1174: ORDER BY DECODE(wts.lock_stop_id, wts.stop_id, 1, NULL, 2, 3);
1175: -- bug 3642085
1177: -- Find closed stops that have pick up deliveries with lines to interface
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
1191:
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
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
1220: st.stop_location_id = dl.initial_pickup_location_id AND
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
1243: AND wts.stop_location_id = wnd.initial_pickup_location_id;
1510: END IF;
1511: END IF;
1512: -- Workflow Changes ==/
1513:
1514: -- Get the new batch_id for the stops (wsh_trip_stops.batch_id)
1515: IF l_stop_per_batch_counter = 1 THEN
1516: l_dsno_stop_tab.DELETE;
1517: l_stop_tab.DELETE;
1518: OPEN c_get_batch;
1563: END IF;
1564:
1565:
1566: -- mark stop as being processed; refresh its LAST_UPDATED info.
1567: UPDATE wsh_trip_stops
1568: SET pending_interface_flag = 'P',
1569: request_id = fnd_global.conc_request_id,
1570: last_updated_by = fnd_global.user_id,
1571: last_update_date = sysdate,
1868: END IF;
1869:
1870: -- Filter out the failed stops from the batch.
1871: FORALL x IN l_err_stops.FIRST..l_err_stops.LAST
1872: UPDATE wsh_trip_stops
1873: SET pending_interface_flag = 'Y',
1874: batch_id = NULL
1875: WHERE stop_id = l_err_stops(x);
1876:
1963: -- of the ITS.
1964:
1965: IF l_found THEN
1966: l_action := 'updating';
1967: UPDATE wsh_trip_stops
1968: SET pending_interface_flag = l_new_flag,
1969: last_updated_by = fnd_global.user_id,
1970: last_update_date = sysdate
1971: WHERE batch_id = l_stop_batch_id;
2150: WSH_DEBUG_SV.log(l_module_name,
2151: 'Automatic clean up');
2152: END IF;
2153:
2154: UPDATE wsh_trip_stops wts
2155: SET wts.pending_interface_flag = 'Y',
2156: wts.last_updated_by = fnd_global.user_id,
2157: wts.last_update_date = sysdate
2158: WHERE wts.pending_interface_flag = 'P'
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,
2748: wsh_delivery_assignments_v da1
4012: l_count NUMBER := 0;
4013:
4014: CURSOR lock_row ( p_batch_id in NUMBER ) IS
4015: SELECT stop_id
4016: FROM wsh_trip_stops
4017: WHERE batch_id = p_batch_id
4018: FOR UPDATE NOWAIT;
4019: Recinfo lock_row%ROWTYPE;
4020:
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
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
4054: AND st.batch_id = p_batch_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
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
4887: st.batch_id = p_batch_id AND
5019: l_dummy NUMBER;
5020:
5021: CURSOR c_lock_batch (p_batch_id NUMBER) IS
5022: SELECT batch_id
5023: FROM wsh_trip_stops
5024: WHERE batch_id = p_batch_id
5025: FOR UPDATE NOWAIT;
5026:
5027:
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
5041: AND dg.delivery_id = dl.delivery_id
5110: CLOSE c_lock_batch;
5111:
5112: l_start := l_err_stops.FIRST;
5113: FORALL i IN l_start..l_count
5114: UPDATE wsh_trip_stops
5115: SET batch_id = NULL,
5116: pending_interface_flag = 'Y'
5117: WHERE stop_id = l_err_stops(i);
5118:
5223: l_num_errors NUMBER := 0;
5224:
5225: CURSOR lock_row ( p_batch_id in NUMBER ) IS
5226: SELECT stop_id
5227: FROM wsh_trip_stops
5228: WHERE batch_id = p_batch_id
5229: FOR UPDATE NOWAIT;
5230: Recinfo lock_row%ROWTYPE;
5231:
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
5460: and wdd.delivery_detail_id = wda.delivery_detail_id
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
5482: and wdd.delivery_detail_id = wda.delivery_detail_id
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
5502: and wdd.delivery_detail_id = wda.delivery_detail_id
5959: l_header_id NUMBER;
5960:
5961: CURSOR c_get_batch_stops (p_batch_id NUMBER) IS
5962: SELECT stop_id
5963: FROM wsh_trip_stops
5964: WHERE batch_id = p_batch_id
5965: ORDER BY stop_id ;
5966:
5967: CURSOR c_get_stop_lines (p_stop_id NUMBER) IS
5966:
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
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
6257: st.stop_id = dg.pick_up_stop_id AND
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
6341: da.delivery_id IS NOT NULL AND
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
6376: st.stop_id = dg.pick_up_stop_id AND
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
6435: st.stop_id = dg.pick_up_stop_id AND
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
6812: dg.delivery_id = dl.delivery_id AND
6830: AND wdd.source_line_set_id = c_source_line_set_id
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
6848: AND wdd.source_line_id = c_source_line_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
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
6886: and wnd.delivery_id = wdl.delivery_id
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
6973: and wdd.delivery_detail_id = wda.delivery_detail_id
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
6986: and wdd.delivery_detail_id = wda.delivery_detail_id
6998: */
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
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
7026: and wda3.delivery_id = wdl3.delivery_id
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
7064: st.stop_location_id = dl.initial_pickup_location_id AND
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
7130: st.stop_location_id = dl.initial_pickup_location_id AND
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
7604: AND dg.delivery_id = dl.delivery_id
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
8389: st.batch_id = p_batch_id AND
8778: -- inv_interfaced_flag = 'X' means no need to get inv interfaced
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')
8846:
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'