[Home] [Help]
68: msnt.attribute14,
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
108: msnt.attribute14,
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
148: msn.attribute14,
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
188: msn.attribute14,
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
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
880: WHERE wts.batch_id = p_batch_id
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
880: WHERE wts.batch_id = p_batch_id
881: AND wts.stop_id = wdl.pick_up_stop_id
2737: dd.ship_from_location_id ship_from_location_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,
2744: wsh_trip_stops st,
2745: wsh_trips tr,
2746: wsh_carriers wcv,
2747: wsh_delivery_details dd1,
2748: wsh_delivery_assignments_v da1
2749: WHERE st.stop_id = dg.pick_up_stop_id
2750: AND st.batch_id = p_batch_id
2751: AND st.stop_location_id = dl.initial_pickup_location_id
2752: AND dg.delivery_id = dl.delivery_id
4043: -- in the previous run
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
4184: -- The ones that got sent to MMT have been updated to 'Y' earlier by Update_Interfaced_Details.
4185: -- So , this statement will update the ones still in 'P' and are neither in MMT nor in MTI
4186:
4187: -- sql repository performance bug 4891985 (>1M sharable memory)
4188: -- changed wsh_delivery_assignments_v to wsh_delivery_assignments
4189:
4190: -- bug 5736840
4191: open get_details(p_batch_id);
4192: fetch get_details bulk collect into l_detail_ids_tbl;
4434: FUNCTION More_Shipment_Exist(p_delivery_id number, p_source_code varchar2, p_source_line_id number) RETURN BOOLEAN is
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 */
4449: l_assigned_total assigned_line_total%ROWTYPE;
4450: cursor unassigned_line_total is
4451: SELECT count(*) total
4452: from wsh_delivery_details dd,
4453: wsh_delivery_assignments_v da
4454: where dd.delivery_detail_id = da.delivery_detail_id
4455: and da.delivery_id is NULL
4456: and dd.source_line_id = p_source_line_id
4457: and dd.source_code = p_source_code
4539:
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
4876: and nvl(inv_interfaced_flag , 'N') <> c_inv_int_full
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
5030:
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
5450: AND wdd.source_code = 'OE' -- bug 3642085
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
5472: AND wdd.source_code = 'OE' -- bug 3642085
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
5492: AND wdd.source_code = 'OE' -- bug 3642085
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
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
5976: AND wdd.delivery_detail_id = wda.delivery_detail_id
6246: and source_code = l_source_code
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
6330: UPDATE wsh_delivery_details dd
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
6365: and source_code = l_source_code
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
6424: and source_code = l_source_code
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
6801: --Now OM locks oe_order_lines_all table for the associated lines
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
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)
6840: for update nowait;
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)
6858: for update nowait;
6875: and wdd.source_code = 'OE'
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
6891: CURSOR c_picked_dd(c_source_line_id NUMBER,
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
6952: -- anxsharm for Load Tender
6953: l_trip_id_tab wsh_util_core.id_tab_type;
6954:
6955: -- sql repository performance bug 4891985 (>1M sharable memory)
6956: -- 1) changed wsh_delivery_assignments_v to wsh_delivery_assignments
6957: -- 2) restructured the query
6958: -- 3) added the missing condition wdd2.released_status <> 'D' not to handled the cancelled delivery lines
6959:
6960: --HVOP heali
6963: select 'X'
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
6976: )
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
7053: ol.org_id org_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
7119: sum( dd.requested_quantity2 ) total_requested_quantity2,
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
7593: SUM( nvl(dd.shipped_quantity, 0 ))
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
8378: update wsh_delivery_details
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
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
8788: AND wts.stop_location_id = wdd.ship_from_location_id
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
8856: AND wts.stop_location_id = wdd.ship_from_location_id