[Home] [Help]
88: l_entity_table(1).p_ship_to_location_id,
89: l_entity_table(1).p_intmed_location_id,
90: l_entity_table(1).p_planned_flag,
91: l_entity_table(1).p_status_code
92: FROM wsh_new_deliveries_ob_grp_v wnd
93: WHERE wnd.delivery_id = p_delivery_id;
94:
95: --for validation, 'UPDATE' is the action code
96: l_entity_table(1).p_action_code := wsh_fte_comp_constraint_grp.g_action_update;
167: OPEN x_deliveryLOV for
168: SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
169: wnd.waybill,
170: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
171: FROM wsh_new_deliveries_ob_grp_v wnd, wsh_delivery_assignments_v wda,wsh_delivery_details_ob_grp_v wdd
172: WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
173: AND wda.delivery_id = wnd.delivery_id
174: and ( wdd.released_status = 'Y' or
175: ( wdd.released_status = 'X' and
194: OPEN x_deliveryLOV for
195: SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
196: wnd.waybill,
197: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
198: FROM wsh_new_deliveries_ob_grp_v wnd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
199: WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
200: AND wda.delivery_id = wnd.delivery_id
201: and wdd.organization_id = p_organization_id
202: and wnd.name like (p_delivery_name);
209: * nesting with a join clause.
210: select wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
211: wnd.waybill,
212: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
213: from wsh_new_deliveries_ob_grp_v wnd
214: where wnd.delivery_id IN -- bug 2326192
215: ( select wda.delivery_id
216: from wsh_delivery_assignments_v wda
217: where parent_delivery_detail_id =
227: -- Replaced nested sub query by a join condition
228: SELECT wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
229: wnd.waybill,
230: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
231: FROM wsh_new_deliveries wnd
232: WHERE wnd.delivery_id IN
233: ( SELECT wda.delivery_id
234: FROM wsh_delivery_details wdd,
235: wsh_delivery_assignments wda
251: OPEN x_deliveryLineLOV for
252: SELECT wdd.delivery_detail_id
253: FROM wsh_delivery_details_ob_grp_v wdd,
254: wsh_delivery_assignments_v wda,
255: wsh_new_deliveries_ob_grp_v wnd
256: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
257: AND wda.delivery_id = wnd.delivery_id
258: AND wnd.delivery_id = p_delivery_id
259: AND wdd.inventory_item_id = nvl(p_inventory_item_id, wdd.inventory_item_id)
311: open x_delivery_info for
312: SELECT wnd.name, wnd.delivery_id, nvl(wnd.gross_weight, 0), wnd.weight_uom_code,
313: wnd.waybill,' ',
314: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
315: FROM wsh_new_deliveries_ob_grp_v wnd
316: WHERE wnd.delivery_id = p_delivery_id;
317: END GET_DELIVERY_INFO;
318:
319:
346: REQUESTED_QUANTITY_UOM2,
347: REQUESTED_QUANTITY2,
348: PICKED_QUANTITY2,
349: SHIPPED_QUANTITY2
350: FROM wsh_new_deliveries_ob_grp_v del, wsh_delivery_details_ob_grp_v dd,
351: wsh_delivery_assignments_v da, mtl_system_items_vl msiv
352: WHERE da.delivery_id = del.delivery_id
353: AND da.delivery_detail_id = dd.delivery_detail_id
354: AND ( dd.inventory_item_id = p_inventory_item_id or p_inventory_item_id = -1 )
370: --3348813
371: --Adding picked_quantity as part of the return cursor.
372: dd.picked_quantity,
373: dd.shipped_quantity
374: FROM wsh_new_deliveries_ob_grp_v del,
375: wsh_delivery_details_ob_grp_v dd,
376: wsh_delivery_assignments_v da,
377: mtl_system_items_vl msiv
378: WHERE da.delivery_id = del.delivery_id
400: l_item_name VARCHAR2(40);
401: all_items VARCHAR2(20000) := NULL;
402: cursor item_name is
403: select msik.concatenated_segments
404: from wsh_new_deliveries_ob_grp_v del,
405: wsh_delivery_details_ob_grp_v dd,
406: wsh_delivery_assignments_v da,
407: mtl_system_items_kfv msik
408: where da.delivery_id = del.delivery_id
559: from wsh_trips_ob_grp_v trip,
560: wsh_trip_stops_ob_grp_v pickup_stop,
561: wsh_trip_stops_ob_grp_v dropoff_stop,
562: wsh_delivery_legs_ob_grp_v wdl,
563: wsh_new_deliveries_ob_grp_v wnd
564: where wdl.delivery_id = wnd.delivery_id(+)
565: and wdl.delivery_id = p_delivery_id
566: and pickup_stop.stop_id = wdl.pick_up_stop_id
567: and dropoff_stop.stop_id = wdl.drop_off_stop_id
2111: SELECT count(*)
2112: INTO unshipped_count
2113: FROM wsh_delivery_details_ob_grp_v wdd,
2114: wsh_delivery_assignments_v wda,
2115: wsh_new_deliveries_ob_grp_v wnd
2116: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
2117: AND wda.delivery_id = wnd.delivery_id
2118: AND wnd.delivery_id = p_delivery_id
2119: AND wdd.ship_set_id = l_ship_set_id
2176: CURSOR delivery_details IS
2177: select wdd.delivery_detail_id
2178: from wsh_delivery_details_ob_grp_v wdd,
2179: wsh_delivery_assignments_v wda,
2180: wsh_new_deliveries_ob_grp_v wnd
2181: where wdd.delivery_detail_id = wda.delivery_detail_id
2182: AND wda.delivery_id = wnd.delivery_id
2183: AND wnd.delivery_id = p_delivery_id
2184: AND wdd.shipped_quantity is null
2266: -- Locked the record first, so that others will not able to ship the same delivery
2267: BEGIN
2268: select organization_id
2269: into l_organization_id
2270: from wsh_new_deliveries_ob_grp_v
2271: where delivery_id = p_delivery_id
2272: for update NOWAIT;
2273: EXCEPTION WHEN others THEN
2274: x_return_Status := 'N';
2984: SELECT wt.ship_method_code,
2985: wt.trip_id
2986: INTO x_trip_shipmethod_code,
2987: p_trip_id
2988: FROM wsh_new_deliveries del,
2989: wsh_delivery_legs dlg,
2990: wsh_trip_stops st,
2991: wsh_trips wt
2992: WHERE del.delivery_id = dlg.delivery_id