[Home] [Help]
7278: l_progress := '30';
7279:
7280: /* Bug 5073354 : Changed following query to access the base table wsh_delivery_details
7281: ** instead of using the view wsh_delivery_details_ob_grp_v and
7282: ** wsh_delivery_assignments instead of wsh_delivery_assignments_v. This
7283: ** is being done to overcome performance issues reported for 10G database.
7284: */
7285:
7286: -- verify the LPN belongs choosen internal order.
7286: -- verify the LPN belongs choosen internal order.
7287: BEGIN
7288: SELECT 'Y' INTO x_lpn_flag
7289: FROM wsh_delivery_details wdd,
7290: wsh_delivery_assignments wda,
7291: wsh_delivery_details wdd1
7292: WHERE wdd.lpn_id IN
7293: (SELECT lpn_id FROM wms_license_plate_numbers
7294: START WITH lpn_id = p_lpn_id
7311: END IF;
7312:
7313: /* Bug 5073354 : Changed following query to access the base table wsh_delivery_details
7314: ** instead of using the view wsh_delivery_details_ob_grp_v and
7315: ** wsh_delivery_assignments instead of wsh_delivery_assignments_v. This
7316: ** is being done to overcome performance issues reported for 10G database.
7317: */
7318:
7319: -- Verify this LPN does not belong any other internal requision
7326: CONNECT BY parent_lpn_id = PRIOR lpn_id) LOOP
7327: BEGIN
7328: SELECT 'N' INTO x_lpn_flag
7329: FROM wsh_delivery_details wdd,
7330: wsh_delivery_assignments wda,
7331: wsh_delivery_details wdd1
7332: WHERE wdd.lpn_id = l_lpn_rec.lpn_id
7333: AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
7334: AND wda.delivery_detail_id = wdd1.delivery_detail_id
7362: END IF;
7363:
7364: /* Bug 5073354 : Changed following query to access the base table wsh_delivery_details
7365: ** instead of using the view wsh_delivery_details_ob_grp_v and
7366: ** wsh_delivery_assignments instead of wsh_delivery_assignments_v. This
7367: ** is being done to overcome performance issues reported for 10G database.
7368: */
7369:
7370: -- Check are there any LPNs left for this order.
7370: -- Check are there any LPNs left for this order.
7371: BEGIN
7372: SELECT 1 INTO x_count_of_lpns
7373: FROM wsh_delivery_details wdd,
7374: wsh_delivery_assignments wda,
7375: wsh_delivery_details wdd1
7376: WHERE wdd.lpn_id NOT IN
7377: (SELECT lpn_id FROM wms_license_plate_numbers
7378: START WITH lpn_id = p_lpn_id