[Home] [Help]
123: msnt.time_since_visit,
124: msnt.serial_attribute_category
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
218: msnt.time_since_visit,
219: msnt.serial_attribute_category
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
313: msn.time_since_visit,
314: msn.serial_attribute_category
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
408: msn.time_since_visit,
409: msn.serial_attribute_category
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
892: DELETE mtl_serial_numbers_temp
893: WHERE transaction_temp_id IN
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
925: DELETE mtl_serial_numbers_temp
926: WHERE transaction_temp_id IN
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
1315: FROM wsh_trip_stops wts,
1316: wsh_trip_stops wts2,
1317: wsh_delivery_legs wdl,
1318: wsh_delivery_legs wdl2,
1319: wsh_delivery_assignments_v wda,
1320: wsh_delivery_assignments_v wda2,
1321: wsh_delivery_details wdd,
1322: wsh_delivery_details wdd2
1323: WHERE wts.batch_id = p_batch_id
1316: wsh_trip_stops wts2,
1317: wsh_delivery_legs wdl,
1318: wsh_delivery_legs wdl2,
1319: wsh_delivery_assignments_v wda,
1320: wsh_delivery_assignments_v wda2,
1321: wsh_delivery_details wdd,
1322: wsh_delivery_details wdd2
1323: WHERE wts.batch_id = p_batch_id
1324: AND wts.stop_id = wdl.pick_up_stop_id
3346: dd.ship_from_location_id ship_from_location_id,
3347: dd.ship_to_site_use_id ship_to_site_use_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,
3353: wsh_trip_stops st,
3354: wsh_trips tr,
3355: wsh_carriers wcv,
3356: wsh_delivery_details dd1,
3357: wsh_delivery_assignments_v da1
3358: WHERE st.stop_id = dg.pick_up_stop_id
3359: AND st.batch_id = p_batch_id
3360: AND st.stop_location_id = dl.initial_pickup_location_id
3361: AND dg.delivery_id = dl.delivery_id
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
4955: -- in the previous run
4956: CURSOR l_get_picking_ln_id_csr (p_batch_id IN NUMBER) IS
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
4974: --RTV changes
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'
5158: -- The ones that got sent to MMT have been updated to 'Y' earlier by Update_Interfaced_Details.
5159: -- So , this statement will update the ones still in 'P' and are neither in MMT nor in MTI
5160:
5161: -- sql repository performance bug 4891985 (>1M sharable memory)
5162: -- changed wsh_delivery_assignments_v to wsh_delivery_assignments
5163:
5164: -- bug 5736840
5165: open get_details(p_batch_id);
5166: fetch get_details bulk collect into l_detail_ids_tbl;
5419: FUNCTION More_Shipment_Exist(p_delivery_id number, p_source_code varchar2, p_source_line_id number) RETURN BOOLEAN is
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 */
5434: l_assigned_total assigned_line_total%ROWTYPE;
5435: cursor unassigned_line_total is
5436: SELECT count(*) total
5437: from wsh_delivery_details dd,
5438: wsh_delivery_assignments_v da
5439: where dd.delivery_detail_id = da.delivery_detail_id
5440: and da.delivery_id is NULL
5441: and dd.source_line_id = p_source_line_id
5442: and dd.source_code = p_source_code
5526: cursor other_batch_delivery_details is
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
5811: AND wdd.source_code = 'OE'
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
5826:
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
5904: l_tot_dd_req_qty2,
5905: l_tot_dd_shp_qty2,
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
6400: and nvl(inv_interfaced_flag , 'N') <> c_inv_int_full
6401: and nvl(inv_interfaced_flag , 'N') <> 'X'
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
6555:
6556: CURSOR c_failed_stops (p_batch_id NUMBER) IS
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
6975: AND wdd.source_code = 'OE' -- bug 3642085
6976: MINUS
6977: SELECT wdd.delivery_detail_id
6978: from wsh_delivery_details wdd,
6979: wsh_delivery_assignments_v wda,
6980: wsh_delivery_legs wdl,
6981: wsh_trip_stops wts
6982: where wts.batch_id = p_batch_id
6983: and wdl.pick_up_stop_id = wts.stop_id
6997: AND wdd.source_code = 'OE' -- bug 3642085
6998: MINUS
6999: select wdd.delivery_detail_id
7000: from wsh_delivery_details wdd,
7001: wsh_delivery_assignments_v wda,
7002: wsh_delivery_legs wdl,
7003: wsh_trip_stops wts
7004: where wts.batch_id = p_batch_id
7005: and wdl.pick_up_stop_id = wts.stop_id
7017: AND wdd.source_code = 'OE' -- bug 3642085
7018: MINUS
7019: SELECT wdd.delivery_detail_id
7020: from wsh_delivery_details wdd,
7021: wsh_delivery_assignments_v wda,
7022: wsh_delivery_legs wdl,
7023: wsh_trip_stops wts
7024: where wts.batch_id = p_batch_id
7025: and wdl.pick_up_stop_id = wts.stop_id
7497: SELECT wdd.source_line_id
7498: FROM wsh_delivery_details wdd,
7499: wsh_trip_stops wts,
7500: wsh_delivery_legs wdl,
7501: wsh_delivery_assignments_v wda
7502: WHERE wts.stop_id = p_stop_id
7503: AND wdl.pick_up_stop_id = wts.stop_id
7504: AND wdl.delivery_id = wda.delivery_id
7505: AND wdd.delivery_detail_id = wda.delivery_detail_id
7779: and source_code = l_source_code
7780: and released_status <> l_released_status
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
7865: request_id = fnd_global.conc_request_id,
7866: last_updated_by = fnd_global.user_id
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
7905: and source_code = l_source_code
7906: and released_status <> l_released_status
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
7968: and source_code = l_source_code
7969: and released_status <> l_released_status
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
8346: --Now OM locks oe_order_lines_all table for the associated lines
8347: CURSOR lock_delivery_line(p_batch_id NUMBER,c_source_header_id NUMBER,c_source_line_id NUMBER) IS
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
8377: AND EXISTS (
8378: SELECT 'X'
8379: FROM wsh_trip_stops wts,
8380: wsh_delivery_legs wdl,
8381: wsh_delivery_assignments_v wda
8382: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
8383: AND wts.batch_id = c_batch_id
8384: AND wts.stop_id = wdl.pick_up_stop_id
8385: AND wdl.delivery_id = wda.delivery_id
8398: AND EXISTS (
8399: SELECT 'X'
8400: FROM wsh_trip_stops wts,
8401: wsh_delivery_legs wdl,
8402: wsh_delivery_assignments_v wda
8403: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
8404: AND wts.batch_id = c_batch_id
8405: AND wts.stop_id = wdl.pick_up_stop_id
8406: AND wdl.delivery_id = wda.delivery_id
8426: and wdd.source_code = 'OE'
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
8442: CURSOR c_picked_dd(c_source_line_id NUMBER,
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
8505: -- anxsharm for Load Tender
8506: l_trip_id_tab wsh_util_core.id_tab_type;
8507:
8508: -- sql repository performance bug 4891985 (>1M sharable memory)
8509: -- 1) changed wsh_delivery_assignments_v to wsh_delivery_assignments
8510: -- 2) restructured the query
8511: -- 3) added the missing condition wdd2.released_status <> 'D' not to handled the cancelled delivery lines
8512:
8513: --HVOP heali
8516: select 'X'
8517: from wsh_delivery_details
8518: where source_line_id in (select source_line_id
8519: from wsh_delivery_details wdd,
8520: wsh_delivery_assignments_v wda,
8521: wsh_delivery_legs wdl,
8522: wsh_trip_stops wts
8523: where wts.batch_id = cp_batch_id
8524: and wdl.pick_up_stop_id = wts.stop_id
8529: )
8530: and (delivery_detail_id not in
8531: (select wdd.delivery_detail_id
8532: from wsh_delivery_details wdd,
8533: wsh_delivery_assignments_v wda,
8534: wsh_delivery_legs wdl,
8535: wsh_trip_stops wts
8536: where wts.batch_id = cp_batch_id
8537: and wdl.pick_up_stop_id = wts.stop_id
8553: CURSOR check_bulk_csr (cp_batch_id NUMBER) IS
8554: select 'X'
8555: from wsh_trip_stops wts,
8556: wsh_delivery_legs wdl,
8557: wsh_delivery_assignments wda ,
8558: wsh_delivery_details wdd
8559: where wts.batch_id = cp_batch_id
8560: and wdl.pick_up_stop_id = wts.stop_id
8561: and wda.delivery_id = wdl.delivery_id
8569: and wdd2.container_flag = 'N'
8570: and wdd2.released_status <> 'D'
8571: and wdd2.delivery_detail_id NOT IN
8572: (select wda3.delivery_detail_id
8573: from wsh_delivery_assignments wda3 ,
8574: wsh_delivery_legs wdl3,
8575: wsh_trip_stops wts3,
8576: wsh_delivery_details wdd3
8577: where wts3.batch_id = cp_batch_id
8614: to_uom => ol.order_quantity_uom,
8615: quantity => nvl(dd.shipped_quantity2,0),
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
8695: to_uom => ol.order_quantity_uom,
8696: quantity => nvl(dd.shipped_quantity2, 0 ),
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
9761: last_updated_by = fnd_global.user_id
9762:
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
10164: CURSOR c_lines_not_interfaced(p_batch_id NUMBER) IS
10165: SELECT wdd.delivery_detail_id
10166: FROM wsh_trip_stops wts,
10167: wsh_delivery_legs wdl,
10168: wsh_delivery_assignments_v wda,
10169: wsh_delivery_details wdd
10170: WHERE wdd.inv_interfaced_flag IN ('N', 'P')
10171: AND wts.batch_id = p_batch_id
10172: AND wts.stop_location_id = wdd.ship_from_location_id
10232: CURSOR c_lines_not_interfaced(p_batch_id NUMBER) IS
10233: SELECT wdd.delivery_detail_id
10234: FROM wsh_trip_stops wts,
10235: wsh_delivery_legs wdl,
10236: wsh_delivery_assignments_v wda,
10237: wsh_delivery_details wdd
10238: WHERE -- wdd.oe_interfaced_flag <> 'Y'
10239: wdd.oe_interfaced_flag NOT IN ( 'Y' ,'X') --Standalone WMS project changes
10240: AND wts.batch_id = p_batch_id
10624: AND ol.shipped_quantity IS NULL
10625: AND dd.source_line_id IN
10626: ( SELECT DISTINCT dd1.source_line_id
10627: FROM wsh_delivery_details dd1 ,
10628: wsh_delivery_assignments_v da
10629: WHERE da.delivery_id = c_delivery_id
10630: AND da.delivery_detail_id = dd1.delivery_detail_id
10631: AND NVL(dd1.oe_interfaced_flag,'X') = 'X'
10632: AND dd1.source_code = 'OE'
10923: --{
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