[Home] [Help]
7810: l_progress := '30';
7811:
7812: /* Bug 5073354 : Changed following query to access the base table wsh_delivery_details
7813: ** instead of using the view wsh_delivery_details_ob_grp_v and
7814: ** wsh_delivery_assignments instead of wsh_delivery_assignments_v. This
7815: ** is being done to overcome performance issues reported for 10G database.
7816: */
7817:
7818: -- verify the LPN belongs choosen internal order.
7818: -- verify the LPN belongs choosen internal order.
7819: BEGIN
7820: SELECT 'Y' INTO x_lpn_flag
7821: FROM wsh_delivery_details wdd,
7822: wsh_delivery_assignments wda,
7823: wsh_delivery_details wdd1
7824: WHERE wdd.lpn_id IN
7825: (SELECT lpn_id FROM wms_license_plate_numbers
7826: START WITH lpn_id = p_lpn_id
7843: END IF;
7844:
7845: /* Bug 5073354 : Changed following query to access the base table wsh_delivery_details
7846: ** instead of using the view wsh_delivery_details_ob_grp_v and
7847: ** wsh_delivery_assignments instead of wsh_delivery_assignments_v. This
7848: ** is being done to overcome performance issues reported for 10G database.
7849: */
7850:
7851: -- Verify this LPN does not belong any other internal requision
7858: CONNECT BY parent_lpn_id = PRIOR lpn_id) LOOP
7859: BEGIN
7860: SELECT 'N' INTO x_lpn_flag
7861: FROM wsh_delivery_details wdd,
7862: wsh_delivery_assignments wda,
7863: wsh_delivery_details wdd1
7864: WHERE wdd.lpn_id = l_lpn_rec.lpn_id
7865: AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
7866: AND wda.delivery_detail_id = wdd1.delivery_detail_id
7894: END IF;
7895:
7896: /* Bug 5073354 : Changed following query to access the base table wsh_delivery_details
7897: ** instead of using the view wsh_delivery_details_ob_grp_v and
7898: ** wsh_delivery_assignments instead of wsh_delivery_assignments_v. This
7899: ** is being done to overcome performance issues reported for 10G database.
7900: */
7901:
7902: -- Check are there any LPNs left for this order.
7902: -- Check are there any LPNs left for this order.
7903: BEGIN
7904: SELECT 1 INTO x_count_of_lpns
7905: FROM wsh_delivery_details wdd,
7906: wsh_delivery_assignments wda,
7907: wsh_delivery_details wdd1
7908: WHERE wdd.lpn_id NOT IN
7909: (SELECT lpn_id FROM wms_license_plate_numbers
7910: START WITH lpn_id = p_lpn_id