[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'
175: OR (wdd.released_status='X' and wdd.source_code='RTV') --RTV Change 16197273
195: OPEN x_deliveryLOV for
196: SELECT distinct wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
197: wnd.waybill,
198: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
199: FROM wsh_new_deliveries_ob_grp_v wnd, wsh_delivery_assignments_v wda, wsh_delivery_details_ob_grp_v wdd
200: WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
201: AND wda.delivery_id = wnd.delivery_id
202: and wdd.organization_id = p_organization_id
203: and wnd.name like (p_delivery_name);
210: * nesting with a join clause.
211: select wnd.name, wnd.delivery_id, wnd.gross_weight, wnd.weight_uom_code,
212: wnd.waybill,
213: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
214: from wsh_new_deliveries_ob_grp_v wnd
215: where wnd.delivery_id IN -- bug 2326192
216: ( select wda.delivery_id
217: from wsh_delivery_assignments_v wda
218: where parent_delivery_detail_id =
252: OPEN x_deliveryLineLOV for
253: SELECT wdd.delivery_detail_id
254: FROM wsh_delivery_details_ob_grp_v wdd,
255: wsh_delivery_assignments_v wda,
256: wsh_new_deliveries_ob_grp_v wnd
257: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
258: AND wda.delivery_id = wnd.delivery_id
259: AND wnd.delivery_id = p_delivery_id
260: AND wdd.inventory_item_id = nvl(p_inventory_item_id, wdd.inventory_item_id)
312: open x_delivery_info for
313: SELECT wnd.name, wnd.delivery_id, nvl(wnd.gross_weight, 0), wnd.weight_uom_code,
314: wnd.waybill,' ',
315: GET_SHIPMETHOD_MEANING(wnd.ship_method_code)
316: FROM wsh_new_deliveries_ob_grp_v wnd
317: WHERE wnd.delivery_id = p_delivery_id;
318: END GET_DELIVERY_INFO;
319:
320:
347: REQUESTED_QUANTITY_UOM2,
348: REQUESTED_QUANTITY2,
349: PICKED_QUANTITY2,
350: SHIPPED_QUANTITY2
351: FROM wsh_new_deliveries_ob_grp_v del, wsh_delivery_details_ob_grp_v dd,
352: wsh_delivery_assignments_v da, mtl_system_items_vl msiv
353: WHERE da.delivery_id = del.delivery_id
354: AND da.delivery_detail_id = dd.delivery_detail_id
355: AND ( dd.inventory_item_id = p_inventory_item_id or p_inventory_item_id = -1 )
371: --3348813
372: --Adding picked_quantity as part of the return cursor.
373: dd.picked_quantity,
374: dd.shipped_quantity
375: FROM wsh_new_deliveries_ob_grp_v del,
376: wsh_delivery_details_ob_grp_v dd,
377: wsh_delivery_assignments_v da,
378: mtl_system_items_vl msiv
379: WHERE da.delivery_id = del.delivery_id
401: l_item_name VARCHAR2(40);
402: all_items VARCHAR2(20000) := NULL;
403: cursor item_name is
404: select msik.concatenated_segments
405: from wsh_new_deliveries_ob_grp_v del,
406: wsh_delivery_details_ob_grp_v dd,
407: wsh_delivery_assignments_v da,
408: mtl_system_items_kfv msik
409: where da.delivery_id = del.delivery_id
560: from wsh_trips_ob_grp_v trip,
561: wsh_trip_stops_ob_grp_v pickup_stop,
562: wsh_trip_stops_ob_grp_v dropoff_stop,
563: wsh_delivery_legs_ob_grp_v wdl,
564: wsh_new_deliveries_ob_grp_v wnd
565: where wdl.delivery_id = wnd.delivery_id(+)
566: and wdl.delivery_id = p_delivery_id
567: and pickup_stop.stop_id = wdl.pick_up_stop_id
568: and dropoff_stop.stop_id = wdl.drop_off_stop_id
2155: SELECT count(*)
2156: INTO unshipped_count
2157: FROM wsh_delivery_details_ob_grp_v wdd,
2158: wsh_delivery_assignments_v wda,
2159: wsh_new_deliveries_ob_grp_v wnd
2160: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
2161: AND wda.delivery_id = wnd.delivery_id
2162: AND wnd.delivery_id = p_delivery_id
2163: AND wdd.ship_set_id = l_ship_set_id
2220: CURSOR delivery_details IS
2221: select wdd.delivery_detail_id
2222: from wsh_delivery_details_ob_grp_v wdd,
2223: wsh_delivery_assignments_v wda,
2224: wsh_new_deliveries_ob_grp_v wnd
2225: where wdd.delivery_detail_id = wda.delivery_detail_id
2226: AND wda.delivery_id = wnd.delivery_id
2227: AND wnd.delivery_id = p_delivery_id
2228: AND wdd.shipped_quantity is null
2382: -- Locked the record first, so that others will not able to ship the same delivery
2383: BEGIN
2384: select organization_id
2385: into l_organization_id
2386: from wsh_new_deliveries_ob_grp_v
2387: where delivery_id = p_delivery_id
2388: for update NOWAIT;
2389: EXCEPTION WHEN others THEN
2390: x_return_Status := 'N';