APPS.WSH_CONTAINER_UTILITIES dependencies on WSH_DELIVERY_ASSIGNMENTS

Line 12: wsh_delivery_assignments_v table. This function can be used in

8: PARAMETERS : p_container_instance_id - instance id for the container
9: RETURNS : master container instance id
10: DESCRIPTION : This function derives the master container instance id
11: of the container by using a heirarchical SQL query on
12: wsh_delivery_assignments_v table. This function can be used in
13: SELECT statements that need to use the master container id.
14: ------------------------------------------------------------------------------
15: */
16:

Line 27: FROM wsh_delivery_assignments

23: l_mast_cont_id NUMBER;
24:
25: CURSOR Get_Master_Cont (v_cont_inst_id NUMBER) IS
26: SELECT delivery_detail_id
27: FROM wsh_delivery_assignments
28: WHERE parent_delivery_detail_id IS NULL
29: AND NVL(type, 'S') in ('S', 'C')
30: START WITH delivery_detail_id = v_cont_inst_id
31: AND NVL(type, 'S') in ('S', 'C')

Line 321: FROM wsh_delivery_assignments_v wda

317: NVL(wdd.picked_quantity, wdd.requested_quantity))) packed_qty
318: FROM wsh_delivery_details wdd
319: WHERE wdd.delivery_detail_id IN
320: (SELECT wda.delivery_detail_id
321: FROM wsh_delivery_assignments_v wda
322: WHERE wda.parent_delivery_detail_id IS NOT NULL
323: AND wda.parent_delivery_detail_id = v_container_instance_id)
324: AND wdd.container_flag ='N';
325:

Line 2231: FROM wsh_delivery_assignments

2227: x_return_status OUT NOCOPY VARCHAR2) IS
2228:
2229: CURSOR Get_Master_Cont (v_cont_inst_id NUMBER) IS
2230: SELECT delivery_detail_id
2231: FROM wsh_delivery_assignments
2232: WHERE parent_delivery_detail_id IS NULL
2233: AND NVL(type, 'S') in ('S', 'C')
2234: START WITH delivery_detail_id = v_cont_inst_id
2235: AND NVL(type, 'S') in ('S', 'C')

Line 2396: FROM wsh_delivery_assignments_v

2392: x_return_status OUT NOCOPY VARCHAR2) IS
2393:
2394: CURSOR Get_Child_Containers(v_cont_instance_id NUMBER) IS
2395: SELECT delivery_detail_id
2396: FROM wsh_delivery_assignments_v
2397: START WITH parent_delivery_detail_id = v_cont_instance_id
2398: CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
2399:
2400: CURSOR l_get_det_attribs_csr(p_detail_id IN NUMBER) IS

Line 2838: FROM wsh_delivery_assignments_v wda,

2834: x_return_status OUT NOCOPY VARCHAR2) IS
2835:
2836: CURSOR Fetch_Details (v_cont_instance_id NUMBER) IS
2837: SELECT wda.delivery_detail_id, wda.delivery_id
2838: FROM wsh_delivery_assignments_v wda,
2839: WSH_DELIVERY_DETAILS wdd
2840: WHERE wda.parent_delivery_detail_id = v_cont_instance_id
2841: AND wdd.delivery_detail_id = wda.delivery_detail_id
2842: AND wdd.container_flag = 'N'

Line 2941: -- since it causes 2 full table scan on wsh_delivery_assignments

2937:
2938:
2939: -- bug 4891897, sql 15036897
2940: -- removed the outer query (select 1 from dual where exists ())
2941: -- since it causes 2 full table scan on wsh_delivery_assignments
2942:
2943:
2944: CURSOR c_details_exist (v_delivery_detail_id NUMBER) IS
2945: SELECT 1

Line 2949: FROM wsh_delivery_assignments_v

2945: SELECT 1
2946: FROM wsh_delivery_details
2947: WHERE delivery_detail_id in (
2948: SELECT delivery_detail_id
2949: FROM wsh_delivery_assignments_v
2950: START WITH delivery_detail_id = v_delivery_detail_id
2951: CONNECT BY prior delivery_detail_id = parent_delivery_detail_id)
2952: AND container_flag = 'N';
2953: l_delivery_detail_id NUMBER;

Line 3158: FROM wsh_delivery_assignments_v wda, WSH_NEW_DELIVERIES wnd, WSH_DELIVERY_DETAILS wdd

3154: x_return_status OUT NOCOPY VARCHAR2) IS
3155:
3156: CURSOR Get_Delivery_Status (v_cont_instance_id NUMBER) IS
3157: SELECT wda.delivery_id, wnd.status_code
3158: FROM wsh_delivery_assignments_v wda, WSH_NEW_DELIVERIES wnd, WSH_DELIVERY_DETAILS wdd
3159: WHERE wda.delivery_detail_id = v_cont_instance_id
3160: AND wda.delivery_id = wnd.delivery_id
3161: AND wdd.delivery_detail_id = wda.delivery_detail_id
3162: AND wdd.container_flag in ('Y', 'C');