[Home] [Help]
8: G_CALLED_FROM_INBOUND BOOLEAN := FALSE;
9:
10: TYPE empty_container_info IS RECORD (
11: container_index NUMBER,
12: container_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
13: mast_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
14: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
15: group_id NUMBER,
16: numerator NUMBER,
9:
10: TYPE empty_container_info IS RECORD (
11: container_index NUMBER,
12: container_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
13: mast_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
14: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
15: group_id NUMBER,
16: numerator NUMBER,
17: denominator NUMBER,
10: TYPE empty_container_info IS RECORD (
11: container_index NUMBER,
12: container_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
13: mast_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
14: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
15: group_id NUMBER,
16: numerator NUMBER,
17: denominator NUMBER,
18: empty NUMBER);
22: g_num_cont_index NUMBER := 0;
23:
24: TYPE assign_detail_info IS RECORD (
25: container_index NUMBER,
26: gross_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
27: net_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
28: volume WSH_DELIVERY_DETAILS.volume%TYPE,
29: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
30: delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
23:
24: TYPE assign_detail_info IS RECORD (
25: container_index NUMBER,
26: gross_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
27: net_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
28: volume WSH_DELIVERY_DETAILS.volume%TYPE,
29: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
30: delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
31: -- J: W/V Changes
24: TYPE assign_detail_info IS RECORD (
25: container_index NUMBER,
26: gross_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
27: net_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
28: volume WSH_DELIVERY_DETAILS.volume%TYPE,
29: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
30: delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
31: -- J: W/V Changes
32: inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
26: gross_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
27: net_weight WSH_DELIVERY_DETAILS.net_weight%TYPE,
28: volume WSH_DELIVERY_DETAILS.volume%TYPE,
29: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
30: delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
31: -- J: W/V Changes
32: inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
33: weight_uom WSH_DELIVERY_DETAILS.weight_uom_code%TYPE,
34: volume_uom WSH_DELIVERY_DETAILS.volume_uom_code%TYPE,
28: volume WSH_DELIVERY_DETAILS.volume%TYPE,
29: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
30: delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
31: -- J: W/V Changes
32: inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
33: weight_uom WSH_DELIVERY_DETAILS.weight_uom_code%TYPE,
34: volume_uom WSH_DELIVERY_DETAILS.volume_uom_code%TYPE,
35: -- K LPN CONV
36: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE);
29: delivery_id wsh_delivery_assignments_v.delivery_id%TYPE,
30: delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
31: -- J: W/V Changes
32: inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
33: weight_uom WSH_DELIVERY_DETAILS.weight_uom_code%TYPE,
34: volume_uom WSH_DELIVERY_DETAILS.volume_uom_code%TYPE,
35: -- K LPN CONV
36: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE);
37: TYPE assign_detail_info_tab IS TABLE OF assign_detail_info INDEX BY BINARY_INTEGER;
30: delivery_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
31: -- J: W/V Changes
32: inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
33: weight_uom WSH_DELIVERY_DETAILS.weight_uom_code%TYPE,
34: volume_uom WSH_DELIVERY_DETAILS.volume_uom_code%TYPE,
35: -- K LPN CONV
36: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE);
37: TYPE assign_detail_info_tab IS TABLE OF assign_detail_info INDEX BY BINARY_INTEGER;
38: g_assign_detail_tab assign_detail_info_tab;
32: inventory_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
33: weight_uom WSH_DELIVERY_DETAILS.weight_uom_code%TYPE,
34: volume_uom WSH_DELIVERY_DETAILS.volume_uom_code%TYPE,
35: -- K LPN CONV
36: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE);
37: TYPE assign_detail_info_tab IS TABLE OF assign_detail_info INDEX BY BINARY_INTEGER;
38: g_assign_detail_tab assign_detail_info_tab;
39: g_assign_detail_index NUMBER := 0;
40:
39: g_assign_detail_index NUMBER := 0;
40:
41: -- added number_of_containers to the record structure
42: TYPE new_container_info IS RECORD (
43: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
44: container_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
45: mast_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
46: cont_instance_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
47: number_of_containers NUMBER
40:
41: -- added number_of_containers to the record structure
42: TYPE new_container_info IS RECORD (
43: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
44: container_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
45: mast_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
46: cont_instance_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
47: number_of_containers NUMBER
48: );
41: -- added number_of_containers to the record structure
42: TYPE new_container_info IS RECORD (
43: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
44: container_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
45: mast_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
46: cont_instance_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
47: number_of_containers NUMBER
48: );
49: TYPE new_container_info_tab IS TABLE OF new_container_info INDEX BY BINARY_INTEGER;
42: TYPE new_container_info IS RECORD (
43: organization_id WSH_DELIVERY_DETAILS.organization_id%TYPE,
44: container_item_id WSH_DELIVERY_DETAILS.inventory_item_id%TYPE,
45: mast_cont_item_id WSH_DELIVERY_DETAILS.master_container_item_id%TYPE,
46: cont_instance_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
47: number_of_containers NUMBER
48: );
49: TYPE new_container_info_tab IS TABLE OF new_container_info INDEX BY BINARY_INTEGER;
50: g_new_container_tab new_container_info_tab;
52: -- another temp table to store id
53: -- Store container instance id after Bulk Insert
54: TYPE new_contid_rec IS RECORD (
55: match_id NUMBER,
56: cont_instance_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE,
57: -- J: W/V Changes
58: unit_weight NUMBER,
59: unit_volume NUMBER,
60: weight_uom VARCHAR2(3),
723: -- AND inventory_item_status_code='Active';
724:
725: CURSOR Get_Cont_Name (v_cont_inst_id NUMBER) IS
726: SELECT container_name
727: FROM WSH_DELIVERY_DETAILS
728: WHERE delivery_detail_id = v_cont_inst_id
729: AND container_flag = 'Y';
730:
731: --added cursors
729: AND container_flag = 'Y';
730:
731: --added cursors
732: CURSOR Get_Wdd_Nextval IS
733: SELECT wsh_delivery_details_s.nextval
734: FROM sys.dual;
735:
736: CURSOR Get_Wda_Nextval IS
737: SELECT wsh_delivery_assignments_s.nextval
749: l_primary_uom VARCHAR2(3);
750: -- J: W/V Changes
751:
752: l_container_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
753: l_del_assg_rec WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
754:
755: l_row_id VARCHAR2(30);
756: l_del_assg_id NUMBER;
757:
829: AND wlh.SOURCE_TYPE_ID = 1;
830:
831: CURSOR c_get_rowid (v_delivery_detail number) IS
832: SELECT rowid , container_name
833: from wsh_delivery_details
834: WHERE delivery_detail_id = v_delivery_detail;
835:
836:
837:
1103: -- within the Loop
1104:
1105: IF l_debug_on THEN
1106: WSH_DEBUG_SV.logmsg(l_module_name, 'CREATING DELIVERY DETAIL'||p_num_of_containers );
1107: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
1108: END IF;
1109: --
1110:
1111:
1108: END IF;
1109: --
1110:
1111:
1112: WSH_DELIVERY_DETAILS_PKG.create_delivery_details_bulk
1113: ( p_delivery_details_info => l_container_rec,
1114: p_num_of_rec => p_num_of_containers,
1115: p_container_info_rec => l_container_info_rec,
1116: x_return_status => x_return_status,
1159: l_del_assg_rec.parent_delivery_detail_id := null;
1160:
1161: --
1162: IF l_debug_on THEN
1163: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIV_ASSIGNMENT_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
1164: --WSH_DEBUG_SV.logmsg(l_module_name,'NUMBER OF CONTS'||p_num_of_containers);
1165: WSH_DEBUG_SV.logmsg(l_module_name,'NUMBER OF RECORDS IN WDD '||l_cont_dummy_tab.count);
1166: END IF;
1167: --
1165: WSH_DEBUG_SV.logmsg(l_module_name,'NUMBER OF RECORDS IN WDD '||l_cont_dummy_tab.count);
1166: END IF;
1167: --
1168:
1169: WSH_DELIVERY_DETAILS_PKG.create_deliv_assignment_bulk
1170: ( p_delivery_assignments_info => l_del_assg_rec,
1171: p_num_of_rec => p_num_of_containers,
1172: p_dd_id_tab => l_cont_dummy_tab,
1173: x_da_id_tab => l_assignment_dummy_tab,
1367: AND rownum < 2);
1368:
1369: CURSOR Check_Container (v_detail_id NUMBER) IS
1370: SELECT container_flag
1371: FROM WSH_DELIVERY_DETAILS
1372: WHERE delivery_detail_id = v_detail_id;
1373:
1374:
1375:
1460:
1461: --
1462: IF l_debug_on THEN
1463: WSH_DEBUG_SV.log(l_module_name,'Delivery Status',l_del_sts);
1464: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1465: END IF;
1466: --
1467: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details (
1468: p_rowid => null,
1463: WSH_DEBUG_SV.log(l_module_name,'Delivery Status',l_del_sts);
1464: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1465: END IF;
1466: --
1467: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details (
1468: p_rowid => null,
1469: p_delivery_detail_id => p_container_id,
1470: x_return_status => x_return_status);
1471:
1640: AND rownum < 2;
1641:
1642: CURSOR Get_Cont_Item IS
1643: SELECT inventory_item_id, master_serial_number
1644: FROM WSH_DELIVERY_DETAILS
1645: WHERE delivery_detail_id = p_container_instance_id
1646: AND container_flag = 'Y';
1647:
1648: CURSOR Get_Det_Cont_Item (v_detail_id NUMBER) IS
1647:
1648: CURSOR Get_Det_Cont_Item (v_detail_id NUMBER) IS
1649: SELECT nvl(detail_container_item_id, master_container_item_id),
1650: source_line_id, source_header_id,source_code
1651: FROM WSH_DELIVERY_DETAILS
1652: WHERE delivery_detail_id = v_detail_id
1653: AND container_flag = 'N';
1654:
1655: l_del_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
1651: FROM WSH_DELIVERY_DETAILS
1652: WHERE delivery_detail_id = v_detail_id
1653: AND container_flag = 'N';
1654:
1655: l_del_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
1656:
1657: l_group_id_tab WSH_UTIL_CORE.id_tab_type;
1658: -- l_temp_detail_tab WSH_UTIL_CORE.id_tab_type;
1659:
2011: -- create assignment with l_del_detail_id and p_container_instance_id;
2012:
2013: --
2014: IF l_debug_on THEN
2015: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
2016: END IF;
2017: --
2018: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_To_Cont (
2019: l_del_detail_id,
2014: IF l_debug_on THEN
2015: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
2016: END IF;
2017: --
2018: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_To_Cont (
2019: l_del_detail_id,
2020: p_container_instance_id,
2021: x_return_status);
2022:
2083:
2084:
2085: END IF;
2086:
2087: UPDATE WSH_DELIVERY_DETAILS
2088: SET master_serial_number = l_master_serial_number
2089: WHERE delivery_detail_id = l_del_detail_id;
2090:
2091: IF SQL%ROWCOUNT > 1 OR SQL%NOTFOUND THEN
2188: cnt NUMBER;
2189:
2190: CURSOR Get_Min_Fill IS
2191: SELECT minimum_fill_percent
2192: FROM WSH_DELIVERY_DETAILS
2193: WHERE delivery_detail_id = p_container_instance_id
2194: AND container_flag = 'Y';
2195:
2196: /* wms -change : Added inentory_item_id to later check (ignore) fill pc when LPN */
2195:
2196: /* wms -change : Added inentory_item_id to later check (ignore) fill pc when LPN */
2197: CURSOR Is_Container (v_detail_id NUMBER) IS
2198: SELECT container_flag, serial_number, master_serial_number, inventory_item_id
2199: FROM WSH_DELIVERY_DETAILS
2200: WHERE delivery_detail_id = v_detail_id;
2201:
2202: CURSOR Get_Cont (v_detail_id NUMBER) IS
2203: SELECT wda.parent_delivery_detail_id container_instance_id, wda.delivery_id,
2203: SELECT wda.parent_delivery_detail_id container_instance_id, wda.delivery_id,
2204: wddp.organization_id, nvl(wddp.line_direction,'O'), -- K LPN CONV. rv
2205: wddp.container_flag -- K: MDC
2206: FROM wsh_delivery_assignments wda,
2207: wsh_delivery_details wddp -- K LPN CONV. rv
2208: WHERE wda.delivery_detail_id = v_detail_id
2209: AND wda.parent_delivery_detail_id = wddp.delivery_detail_id(+)
2210: AND NVL(wda.type, 'S') in ('S', 'C');
2211:
2249: l_sync_tmp_rec wsh_glbl_var_strct_grp.sync_tmp_rec_type;
2250:
2251: cursor l_get_cnt_org_csr (p_cnt_inst_id IN NUMBER) is
2252: select organization_id
2253: from wsh_delivery_details
2254: where delivery_detail_id = p_cnt_inst_id;
2255:
2256: -- K LPN CONV. rv
2257:
2399:
2400: END IF;
2401: --
2402: IF l_debug_on THEN
2403: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2404: END IF;
2405: --
2406: WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details (
2407: l_del_detail_rec,
2402: IF l_debug_on THEN
2403: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2404: END IF;
2405: --
2406: WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details (
2407: l_del_detail_rec,
2408: p_del_unassign,
2409: p_cont_unassign,
2410: x_return_status,
2557: --}
2558: END IF;
2559: -- K LPN CONV. rv
2560:
2561: UPDATE WSH_DELIVERY_DETAILS
2562: SET customer_id = NULL,
2563: ship_to_location_id = NULL,
2564: intmed_ship_to_location_id = NULL,
2565: fob_code = NULL,
2645: WSH_DEBUG_SV.log(l_module_name,'l_cont_flag',l_cont_flag);
2646: WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',l_del_detail_rec(i));
2647: END IF;
2648: --
2649: UPDATE wsh_delivery_details --bug 5165197
2650: SET master_serial_number = NULL
2651: WHERE delivery_detail_id = l_del_detail_rec(i);
2652: --
2653: ELSIF l_cont_flag = 'Y' THEN --}{
2794: --}
2795: END IF;
2796: -- K LPN CONV. rv
2797:
2798: UPDATE WSH_DELIVERY_DETAILS
2799: SET customer_id = NULL,
2800: ship_to_location_id = NULL,
2801: intmed_ship_to_location_id = NULL,
2802: fob_code = NULL,
2895: --}
2896: END IF;
2897: -- K LPN CONV. rv
2898:
2899: UPDATE WSH_DELIVERY_DETAILS
2900: SET master_serial_number = serial_number
2901: WHERE delivery_detail_id = l_del_detail_rec(i);
2902:
2903: IF SQL%ROWCOUNT > 1 OR SQL%NOTFOUND THEN
3055: x_return_status => l_return_status);
3056:
3057: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3058:
3059: UPDATE wsh_delivery_details
3060: SET deliver_to_location_id = l_deliver_to_location_id
3061: WHERE delivery_detail_id = l_cont_tab(i);
3062: END IF;
3063:
3194: -- remove this cursor for Bug
3195:
3196: CURSOR Get_Cont_Org (v_cont_instance_id NUMBER) IS
3197: SELECT organization_id
3198: FROM WSH_DELIVERY_DETAILS
3199: WHERE delivery_detail_id = v_cont_instance_id
3200: AND container_flag = 'Y';
3201:
3202: CURSOR Get_Del_Org (v_del_id NUMBER) IS
3203: SELECT organization_id, freight_terms_code
3204: FROM WSH_NEW_DELIVERIES
3205: WHERE delivery_id = v_del_id;
3206:
3207: l_del_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3208:
3209: l_cont_org NUMBER;
3210: l_del_org NUMBER;
3211:
3486: END IF;
3487: --
3488: --
3489: IF l_debug_on THEN
3490: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
3491: END IF;
3492: --
3493: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_to_Delivery(
3494: P_DETAIL_ID =>p_container_instance_id ,
3489: IF l_debug_on THEN
3490: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
3491: END IF;
3492: --
3493: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_to_Delivery(
3494: P_DETAIL_ID =>p_container_instance_id ,
3495: P_DELIVERY_ID => p_delivery_id,
3496: X_RETURN_STATUS =>x_return_status,
3497: x_dlvy_has_lines => x_dlvy_has_lines,
3646: -- remove this cursor
3647:
3648: CURSOR Get_Cont_Org (v_cont_instance_id NUMBER) IS
3649: SELECT organization_id
3650: FROM WSH_DELIVERY_DETAILS
3651: WHERE delivery_detail_id = v_cont_instance_id
3652: AND container_flag in ('Y', 'C');
3653:
3654: l_det_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3650: FROM WSH_DELIVERY_DETAILS
3651: WHERE delivery_detail_id = v_cont_instance_id
3652: AND container_flag in ('Y', 'C');
3653:
3654: l_det_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3655: l_par_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3656:
3657: l_det_cont_org NUMBER;
3658: l_par_cont_org NUMBER;
3651: WHERE delivery_detail_id = v_cont_instance_id
3652: AND container_flag in ('Y', 'C');
3653:
3654: l_det_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3655: l_par_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3656:
3657: l_det_cont_org NUMBER;
3658: l_par_cont_org NUMBER;
3659:
4023: --dbms_output.put_line('creating assignment of cont ' || p_det_cont_inst_id || ' to container ' || p_par_cont_inst_id);
4024: -- just create assignment of child container to parent.
4025: --
4026: IF l_debug_on THEN
4027: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4028: END IF;
4029: --
4030: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4031: p_det_cont_inst_id,
4026: IF l_debug_on THEN
4027: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4028: END IF;
4029: --
4030: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4031: p_det_cont_inst_id,
4032: p_par_cont_inst_id,
4033: x_return_status);
4034:
4088:
4089: -- just create assignment of child container to parent.
4090: --
4091: IF l_debug_on THEN
4092: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4093: END IF;
4094: --
4095: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4096: p_det_cont_inst_id,
4091: IF l_debug_on THEN
4092: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4093: END IF;
4094: --
4095: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4096: p_det_cont_inst_id,
4097: p_par_cont_inst_id,
4098: x_return_status);
4099:
4177:
4178: -- just create assignment of child container to parent.
4179: --
4180: IF l_debug_on THEN
4181: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4182: END IF;
4183: --
4184: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4185: p_det_cont_inst_id,
4180: IF l_debug_on THEN
4181: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4182: END IF;
4183: --
4184: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4185: p_det_cont_inst_id,
4186: p_par_cont_inst_id,
4187: x_return_status);
4188:
4307:
4308: -- create assignment between child and parent container
4309: --
4310: IF l_debug_on THEN
4311: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4312: END IF;
4313: --
4314: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4315: p_det_cont_inst_id,
4310: IF l_debug_on THEN
4311: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4312: END IF;
4313: --
4314: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4315: p_det_cont_inst_id,
4316: p_par_cont_inst_id,
4317: x_return_status);
4318:
4373: x_return_status => l_return_status);
4374:
4375: IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4376:
4377: UPDATE wsh_delivery_details
4378: SET deliver_to_location_id = l_deliver_to_location_id
4379: WHERE delivery_detail_id = p_par_cont_inst_id;
4380:
4381: END IF;
5094:
5095: --dbms_output.put_line('Splitting dd '||p_line_cont_rec.delivery_detail_id||' with qty '||l_tmp_split_qty);
5096: --
5097: IF l_debug_on THEN
5098: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
5099: WSH_DEBUG_SV.logmsg(l_module_name,'SPLIT QTY IS'||l_tmp_split_qty);
5100: END IF;
5101: --
5102:
5104: -- empty container logic, need to test and modify this
5105: -- the value for p_num_of_split is passed as 1
5106: -- so that split occurs once
5107:
5108: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details_bulk (
5109: p_from_detail_id => p_line_cont_rec.delivery_detail_id,
5110: p_req_quantity => l_tmp_split_qty,
5111: p_req_quantity2 => l_tmp_split_qty2,
5112: p_manual_split => 'C',
5122: --dbms_output.put_line('STATUS after SPLITDD in empty'||l_return_status||l_dd_id_tab.count);
5123:
5124: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5125: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5126: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '|| l_return_status);
5127: x_return_status := C_ERROR_STATUS;
5128: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '||x_return_status);
5129: FND_MESSAGE.SET_NAME('WSH','WSH_DET_SPLIT_ERROR');
5130: FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_line_cont_rec.delivery_detail_id);
5124: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5125: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5126: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '|| l_return_status);
5127: x_return_status := C_ERROR_STATUS;
5128: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '||x_return_status);
5129: FND_MESSAGE.SET_NAME('WSH','WSH_DET_SPLIT_ERROR');
5130: FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_line_cont_rec.delivery_detail_id);
5131: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
5132: --
5343: -- End BugFix#3475352
5344:
5345: --dbms_output.put_line('Splitting dd '||p_line_cont_rec.delivery_detail_id||' with qty '||l_split_qty);
5346: IF l_debug_on THEN
5347: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
5348: WSH_DEBUG_SV.logmsg(l_module_name,'Split Qty is'||l_split_qty);
5349: WSH_DEBUG_SV.logmsg(l_module_name,'Number of split is'||l_num_of_split);
5350: END IF;
5351:
5353: -- Call BULK Split API
5354:
5355: --dbms_output.put_line('======BEFORE SPLIT'||l_dd_id_tab.count||'<'||l_num_of_split);
5356:
5357: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details_bulk (
5358: p_from_detail_id => p_line_cont_rec.delivery_detail_id,
5359: p_req_quantity => l_split_qty,
5360: p_req_quantity2 => l_split_qty2,
5361: p_manual_split => 'C',
5370: END IF;
5371:
5372: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5373: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5374: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '|| l_return_status);
5375: x_return_status := C_ERROR_STATUS;
5376: FND_MESSAGE.SET_NAME('WSH','WSH_DET_SPLIT_ERROR');
5377: FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_line_cont_rec.delivery_detail_id);
5378: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
5593: wdd.source_code,
5594: wda.parent_delivery_detail_id parent_delivery_detail_id,
5595: wda.delivery_id delivery_id, -- added delivery id
5596: msi.indivisible_flag indivisible_flag
5597: FROM WSH_DELIVERY_DETAILS wdd,
5598: wsh_delivery_assignments_v wda,
5599: MTL_SYSTEM_ITEMS msi,
5600: WSH_TMP wt
5601: WHERE wdd.delivery_detail_id = wt.id
5648: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DD_FOR_PACK';
5649: --
5650:
5651: -- HW OPMCONV - New variable to hold item info
5652: l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
5653: -- HW OPM BUG# 3011758
5654: -- HW OPMCONV - Removed opm variables
5655:
5656: -- HW OPMCONV - Renamed variable from lot_indivisible to l_lot_divisible_flag
5779: -- HW OPMCONV - 1)Change the call from OPM API to get item info
5780: -- to a new WSH API
5781: -- - 2) Remove checking for process
5782:
5783: WSH_DELIVERY_DETAILS_INV.Get_item_information
5784: (
5785: p_organization_id => rec.organization_id
5786: , p_inventory_item_id => rec.inventory_item_id
5787: , x_mtl_system_items_rec => l_item_info
6373: NVL(line_direction,'O') line_direction, -- J-IB-NPARIKH
6374: shipping_control, -- J-IB-NPARIKH
6375: NVL(ignore_for_planning,'N') ignore_for_planning,
6376: client_id -- LSP PROJECT
6377: FROM wsh_delivery_details
6378: WHERE delivery_detail_id = v_delivery_detail_id;
6379:
6380: -- OTM R12 : assign delivery detail
6381: CURSOR c_get_tare_weight(v_detail_id NUMBER) IS
6379:
6380: -- OTM R12 : assign delivery detail
6381: CURSOR c_get_tare_weight(v_detail_id NUMBER) IS
6382: SELECT nvl(unit_weight, 0)
6383: FROM wsh_delivery_details
6384: WHERE delivery_detail_id = v_detail_id;
6385:
6386: CURSOR c_get_plan_and_tms_flag(v_delivery_id NUMBER) IS
6387: SELECT nvl(ignore_for_planning, 'N'),
6520: l_gc3_is_installed VARCHAR2(1);
6521: l_is_delivery_empty VARCHAR2(1);
6522: l_index NUMBER;
6523: l_index_update NUMBER;
6524: l_tare_weight WSH_DELIVERY_DETAILS.UNIT_WEIGHT%TYPE;
6525: l_ignore_for_planning_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
6526: l_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
6527: l_delivery_was_empty_tab BOOLEAN_TAB_TYPE;
6528: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
6554: l_container_id NUMBER;
6555:
6556: CURSOR c_get_deliver_to_location_id(v_delivery_detail_id number) IS
6557: SELECT deliver_to_location_id
6558: FROM wsh_delivery_details
6559: WHERE delivery_detail_id = v_delivery_detail_id;
6560:
6561: --
6562: l_debug_on BOOLEAN;
7283: l_mdc_id_tab(l_mdc_index_j) := l_ddlist(l_mdc_index_i);
7284: l_mdc_index_i := l_ddlist.next(l_mdc_index_i);
7285:
7286: END LOOP;
7287: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
7288: p_detail_id_tab => l_mdc_id_tab,
7289: x_return_status => x_return_status);
7290:
7291: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7462: IF l_debug_on THEN
7463: WSH_DEBUG_SV.log(l_module_name,'l_deliver_to_location_id1 ', l_deliver_to_location_id1);
7464: END IF;
7465:
7466: UPDATE WSH_DELIVERY_DETAILS
7467: SET customer_id = l_tab_cust_id(cnt),
7468: ship_to_location_id = l_tab_ship_location_id(cnt),
7469: intmed_ship_to_location_id = l_tab_intmed_location_id(cnt),
7470: fob_code = l_tab_fob_code(cnt),
7723: l_mdc_index_i := l_contlist1.next(l_mdc_index_i);
7724:
7725: END LOOP;
7726:
7727: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
7728: p_detail_id_tab => l_mdc_id_tab,
7729: x_return_status => x_return_status);
7730:
7731: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7771: -- 75.1 Bulk Update Weight/Volume on packed delivery details
7772: -- added the WHO columns in the UPDATE
7773: -- in WSHWVUTB.pls , update_container_wt there are no WHO columns
7774: FORALL cnt IN 1..g_assign_detail_tab.COUNT
7775: UPDATE WSH_DELIVERY_DETAILS
7776: SET NET_WEIGHT = l_NetWtlist(cnt),
7777: GROSS_WEIGHT = l_GrossWtlist(cnt),
7778: VOLUME = l_Vollist(cnt),
7779: last_update_date = SYSDATE,
8004:
8005: CURSOR Get_Delivery_Lines (v_delivery_id NUMBER) IS
8006: SELECT wda.delivery_detail_id
8007: FROM wsh_delivery_assignments_v wda,
8008: WSH_DELIVERY_DETAILS wdd
8009: WHERE wda.delivery_id = v_delivery_id
8010: AND wda.delivery_id IS NOT NULL
8011: AND wda.parent_delivery_detail_id IS NULL
8012: AND wda.delivery_detail_id = wdd.delivery_detail_id
8400: CURSOR Get_Cont_Info (v_cont_instance_id NUMBER) IS
8401: SELECT inventory_item_id, organization_id, gross_weight, net_weight,
8402: volume, weight_uom_code, volume_uom_code, fill_percent,
8403: minimum_fill_percent, maximum_load_weight, maximum_volume
8404: FROM wsh_delivery_details
8405: WHERE delivery_detail_id = v_cont_instance_id
8406: AND container_flag = 'Y';
8407:
8408: CURSOR Get_Detail_Info (v_del_detail_id NUMBER) IS
8410: requested_quantity_uom, net_weight, volume,
8411: weight_uom_code, volume_uom_code, organization_id,
8412: nvl(detail_container_item_id, master_container_item_id) cont_item_id,
8413: nvl(shipped_quantity2, NVL(picked_quantity2, requested_quantity2)) pack_qty2
8414: FROM wsh_delivery_details
8415: WHERE delivery_detail_id = v_del_detail_id
8416: AND container_flag = 'N';
8417:
8418: TYPE Cont_Rec IS RECORD (container_instance_id NUMBER, pack_status VARCHAR2(30));
8529:
8530: -- HW OPMCONV - Renamed variable to l_lot_divisible_flag
8531: l_lot_divisible_flag VARCHAR2(1);
8532: -- HW OPMCONV - New variable to hold item info
8533: l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
8534:
8535: -- LPN CONV. rv
8536: l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
8537: l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
8841: -- HW OPMCONV - 1) Removed branching code
8842: -- - 2) Removed call to OPM API to get item info
8843: -- - 3) New WSH API call to get item info
8844:
8845: WSH_DELIVERY_DETAILS_INV.Get_item_information
8846: (
8847: p_organization_id =>l_det_org_id
8848: , p_inventory_item_id => l_det_inv_item_id
8849: , x_mtl_system_items_rec => l_item_info
9161: WSH_DEBUG_SV.log(l_module_name,'l_split_qty',l_split_qty);
9162: WSH_DEBUG_SV.log(l_module_name,'l_split_qty2',l_split_qty2);
9163: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty2',l_det_pack_qty2);
9164: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty',l_det_pack_qty);
9165: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9166: END IF;
9167: --
9168:
9169: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9165: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9166: END IF;
9167: --
9168:
9169: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9170: p_from_detail_id => l_det_tab(j).delivery_detail_id,
9171: p_req_quantity => l_split_qty,
9172: p_req_quantity2 => l_split_qty2,
9173: x_new_detail_id => l_split_det_id,
9332: -- HW OPMCONV - 1)Change the call from OPM API to get item info
9333: -- to a new WSH API
9334: -- - 2) Remove checking for process
9335:
9336: WSH_DELIVERY_DETAILS_INV.Get_item_information
9337: (
9338: p_organization_id => l_det_org_id
9339: , p_inventory_item_id => l_det_inv_item_id
9340: , x_mtl_system_items_rec => l_item_info
9480: WSH_DEBUG_SV.log(l_module_name,'l_split_qty',l_split_qty);
9481: WSH_DEBUG_SV.log(l_module_name,'l_split_qty2',l_split_qty2);
9482: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty2',l_det_pack_qty2);
9483: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty',l_det_pack_qty);
9484: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9485: END IF;
9486:
9487: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9488: p_from_detail_id => l_det_tab(j).delivery_detail_id,
9483: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty',l_det_pack_qty);
9484: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9485: END IF;
9486:
9487: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9488: p_from_detail_id => l_det_tab(j).delivery_detail_id,
9489: p_req_quantity => l_split_qty,
9490: p_req_quantity2 => l_split_qty2,
9491: x_new_detail_id => l_split_det_id,
10148: x_return_status OUT NOCOPY VARCHAR2) IS
10149:
10150: CURSOR Get_Ship_Qty (v_det_id NUMBER) IS
10151: SELECT nvl(shipped_quantity,-99)
10152: FROM WSH_DELIVERY_DETAILS
10153: WHERE delivery_detail_id = v_det_id
10154: AND nvl(line_direction,'O') IN ('O','IO')
10155: AND container_flag = 'N';
10156:
10253:
10254: l_shp_qty := l_shp_qty - nvl(p_split_qty,0) + l_db_split_shp_qty;
10255: --dbms_output.put_line('new l_shp_qty = ' || l_shp_qty);
10256:
10257: UPDATE WSH_DELIVERY_DETAILS
10258: SET shipped_quantity = l_shp_qty
10259: WHERE delivery_detail_id = p_delivery_detail_id;
10260:
10261: IF SQL%NOTFOUND THEN
10270: --
10271: return;
10272: END IF;
10273:
10274: UPDATE WSH_DELIVERY_DETAILS
10275: SET shipped_quantity = p_split_qty
10276: WHERE delivery_detail_id = p_split_detail_id;
10277:
10278: IF SQL%NOTFOUND THEN
10563: wdd.organization_id organization_id,
10564: wdd.delivery_detail_id,
10565: wda.parent_delivery_detail_id parent_delivery_detail_id,
10566: nvl(wdd.detail_container_item_id,wdd.master_container_item_id) detail_container_item_id
10567: FROM wsh_delivery_details wdd,
10568: wsh_delivery_assignments_v wda
10569: WHERE wdd.delivery_detail_id = c_dd_id
10570: AND wdd.delivery_detail_id = wda.delivery_detail_id
10571: AND source_code = 'WSH';
11197: CURSOR c_get_detail(p_del_det_id NUMBER) IS
11198: SELECT nvl(ignore_for_planning, 'N') ignore_for_planning,
11199: organization_id, -- K LPN CON. rv
11200: nvl(line_direction,'O') line_direction -- K LPN CONV. rv
11201: FROM wsh_delivery_details
11202: WHERE delivery_detail_id=p_del_det_id;
11203:
11204: -- K LPN CONV. rv
11205: l_wms_org VARCHAR2(10) := 'N';
11531: END IF;
11532: --}
11533: END IF;
11534: -- K LPN CONV. rv
11535: UPDATE wsh_delivery_details
11536: SET ignore_for_planning=cur.ignore_for_planning
11537: WHERE delivery_detail_id=l_cont_instance_id;
11538:
11539: IF SQL%NOTFOUND THEN
11666: vendor_id, -- J-IB-NPARIKH
11667: party_id, -- J-IB-NPARIKH
11668: nvl(ignore_for_planning, 'N') ignore_for_planning, --J TP Release
11669: client_id -- LSP PROJECT : ClientID needs to be updated on LPN
11670: FROM WSH_DELIVERY_DETAILS
11671: WHERE delivery_detail_id = v_detail_id;
11672:
11673: CURSOR Get_Del_Attr (v_del_id NUMBER) IS
11674: SELECT organization_id, customer_id, ultimate_dropoff_location_id,
11902: IF NVL(l_deliver_to_location_id,-99) <> l_deliver_to_loc_id then
11903: l_deliver_to_loc_id:=NULL;
11904: END IF;
11905:
11906: UPDATE WSH_DELIVERY_DETAILS SET
11907: customer_id = l_cust_id,
11908: ship_to_location_id = l_ship_to_loc_id,
11909: intmed_ship_to_location_id = l_intmed_loc_id,
11910: fob_code = l_fob_code,
11993: SELECT customer_id, intmed_ship_to_location_id,
11994: fob_code, freight_terms_code, ship_method_code,
11995: mode_of_transport, service_level, carrier_id,
11996: deliver_to_location_id
11997: FROM WSH_DELIVERY_DETAILS
11998: WHERE delivery_detail_id = v_detail_id;
11999:
12000: l_cont_name VARCHAR2(30);
12001:
12153: CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
12154:
12155: CURSOR Get_Cont_Flag (v_detail_id NUMBER) IS
12156: SELECT container_flag
12157: FROM WSH_DELIVERY_DETAILS
12158: WHERE delivery_detail_id = v_detail_id;
12159:
12160: l_master_cont_inst_id NUMBER;
12161: l_master_cont_name VARCHAR2(30);
12326: SELECT count(*)
12327: FROM wsh_delivery_assignments_v
12328: WHERE delivery_detail_id IN
12329: (SELECT delivery_detail_id
12330: FROM WSH_DELIVERY_DETAILS
12331: WHERE container_flag = 'Y')
12332: START WITH delivery_detail_id = v_cont_id
12333: CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
12334:
12535: select wnd.delivery_id,
12536: wnd.name,
12537: wnd.status_code,
12538: wda.delivery_detail_id
12539: from wsh_delivery_details wdd,
12540: wsh_delivery_assignments_v wda,
12541: wsh_new_deliveries wnd
12542: where wdd.container_name = p_lpn_name
12543: and wdd.container_flag = 'Y'
12661: END IF;
12662:
12663: IF(l_dup_del_id is not null AND nvl(l_dup_del_status,'OP') = 'CL') THEN
12664: --{
12665: update wsh_delivery_details
12666: set container_name = SUBSTRB(p_lpn_name || '.' ||l_dup_del_name,1,50)
12667: where delivery_detail_id = l_dup_cnt_id;
12668: --}
12669: ELSIF(l_dup_cnt_id IS NOT NULL) THEN
12722: --
12723: --
12724: -- Update Container record with delivery grouping attributes
12725: --
12726: UPDATE WSH_DELIVERY_DETAILS
12727: SET lpn_id = p_lpn_id,
12728: shipped_quantity = DECODE(p_transactionType,'ASN',1,shipped_quantity),
12729: received_quantity = DECODE(p_transactionType,'RECEIPT',1,received_quantity),
12730: released_status = DECODE(p_transactionType,'ASN','C','RECEIPT','L','C'),
12750: WHERE rowid = l_rowid;
12751: --
12752: -- l_container_instance_id is the delivery_detail_id, use for DBI call
12753: -- DBI Project
12754: -- Update of wsh_delivery_details where requested_quantity/released_status
12755: -- are changed, call DBI API after the update.
12756: -- This API will also check for DBI Installed or not
12757: IF l_debug_on THEN
12758: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_container_instance_id);
13031: --
13032: --
13033: -- Delete all containers within the delivery
13034: --
13035: DELETE WSH_DELIVERY_DETAILS
13036: WHERE DELIVERY_DETAIL_ID IN (
13037: SELECT wdd.delivery_detail_id
13038: FROM wsh_delivery_details wdd,
13039: wsh_delivery_assignments_v wda
13034: --
13035: DELETE WSH_DELIVERY_DETAILS
13036: WHERE DELIVERY_DETAIL_ID IN (
13037: SELECT wdd.delivery_detail_id
13038: FROM wsh_delivery_details wdd,
13039: wsh_delivery_assignments_v wda
13040: WHERE wda.delivery_id = p_delivery_id
13041: AND wda.delivery_detail_id = wdd.delivery_detail_id
13042: AND NVL(wdd.container_flag,'N') = 'Y'
13141: x_return_status OUT NOCOPY VARCHAR2) IS
13142:
13143: CURSOR Get_Exist_Cont(v_cont_name VARCHAR2) IS
13144: SELECT NVL(MAX(1),0) FROM DUAL
13145: WHERE EXISTS ( SELECT 1 FROM WSH_DELIVERY_DETAILS
13146: WHERE container_name = v_cont_name
13147: AND container_flag = 'Y');
13148:
13149: l_cont_name VARCHAR2(30);
13297: PRAGMA EXCEPTION_INIT(child_cont_locked, -00054);
13298:
13299: CURSOR lock_containers(v_container_id NUMBER) IS
13300: SELECT delivery_detail_id
13301: FROM wsh_delivery_details
13302: --WHERE container_flag = 'Y'
13303: WHERE delivery_detail_id IN
13304: (SELECT delivery_detail_id
13305: FROM wsh_delivery_assignments
13334: LOOP --{
13335: FETCH lock_containers INTO l_container_id;
13336: EXIT WHEN lock_containers%NOTFOUND;
13337:
13338: UPDATE wsh_delivery_details
13339: SET locator_id = p_locator_id,
13340: subinventory = p_subinventory
13341: WHERE delivery_detail_id = l_container_id;
13342:
13397: x_return_status OUT NOCOPY VARCHAR2) IS
13398:
13399: cursor c_get_container_info(p_delivery_detail_id in number) is
13400: select d.container_flag, a.delivery_id, a.parent_delivery_id, d.inventory_item_id, d.organization_id
13401: from wsh_delivery_details d, wsh_delivery_assignments a
13402: where d.delivery_detail_id = p_delivery_detail_id
13403: and d.container_flag in ('Y', 'C')
13404: and a.type in ('C', 'S')
13405: and a.delivery_detail_id = d.delivery_detail_id
13457:
13458: l_assign_flag VARCHAR2(1);
13459: l_del_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
13460: l_parent_del_info_rec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
13461: l_del_asg_info_rec WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_TYPE;
13462: l_child_details_tab wsh_util_core.id_tab_type;
13463: l_dummy_asg_tab wsh_util_core.id_tab_type;
13464: l_wv_delivery_tab wsh_util_core.id_tab_type;
13465: l_child_delivery_id NUMBER;
13485: l_return_status VARCHAR2(30);
13486:
13487: CURSOR c_get_filled_vol(p_container_id NUMBER) IS
13488: SELECT filled_volume
13489: FROM WSH_DELIVERY_DETAILS
13490: WHERE delivery_detail_id = p_container_id;
13491:
13492: l_filled_volume NUMBER;
13493:
13778:
13779: -- Lock the delivery details and assignments.
13780:
13781: BEGIN
13782: wsh_delivery_details_pkg.lock_detail_no_compare(p_delivery_detail_id => p_parent_container_id);
13783: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_parent_container_id);
13784: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_child_container_id);
13785: EXCEPTION
13786: WHEN OTHERS THEN
13779: -- Lock the delivery details and assignments.
13780:
13781: BEGIN
13782: wsh_delivery_details_pkg.lock_detail_no_compare(p_delivery_detail_id => p_parent_container_id);
13783: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_parent_container_id);
13784: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_child_container_id);
13785: EXCEPTION
13786: WHEN OTHERS THEN
13787: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13780:
13781: BEGIN
13782: wsh_delivery_details_pkg.lock_detail_no_compare(p_delivery_detail_id => p_parent_container_id);
13783: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_parent_container_id);
13784: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_child_container_id);
13785: EXCEPTION
13786: WHEN OTHERS THEN
13787: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13788: FND_MESSAGE.SET_NAME('WSH','WSH_NO_LOCK');
13798: --
13799: END;
13800:
13801:
13802: update wsh_delivery_details
13803: set container_flag = 'C',
13804: ship_to_location_id = l_parent_del_info_rec.ultimate_dropoff_location_id,
13805: intmed_ship_to_location_id = l_parent_del_info_rec.intmed_ship_to_location_id,
13806: customer_id = l_parent_del_info_rec.customer_id,
13867: -- lock the children.
13868:
13869: BEGIN
13870: FOR i in 1..l_child_details_tab.count LOOP
13871: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => l_child_details_tab(i));
13872: END LOOP;
13873:
13874: EXCEPTION
13875: WHEN OTHERS THEN
13919: l_del_asg_info_rec.parent_delivery_id := l_new_parent_delivery_id;
13920: l_del_asg_info_rec.type := 'C';
13921:
13922: IF l_debug_on THEN
13923: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk',WSH_DEBUG_SV.C_PROC_LEVEL);
13924: END IF;
13925: WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk(
13926: p_delivery_assignments_info => l_del_asg_info_rec,
13927: p_num_of_rec => l_child_details_tab.count,
13921:
13922: IF l_debug_on THEN
13923: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk',WSH_DEBUG_SV.C_PROC_LEVEL);
13924: END IF;
13925: WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk(
13926: p_delivery_assignments_info => l_del_asg_info_rec,
13927: p_num_of_rec => l_child_details_tab.count,
13928: p_dd_id_tab => l_child_details_tab,
13929: x_da_id_tab => l_dummy_asg_tab,
14074: p_calc_del_wv => 'Y',
14075: x_return_status => l_return_status,
14076: p_suppress_errors => 'N');
14077:
14078: UPDATE wsh_delivery_details
14079: SET filled_volume = l_filled_volume
14080: WHERE delivery_detail_id = p_parent_container_id;
14081:
14082: IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
14250: wdd1.volume_uom_code,
14251: wdd1.inventory_item_id,
14252: wdd2.weight_uom_code,
14253: wdd2.volume_uom_code
14254: from wsh_delivery_details wdd1,
14255: wsh_delivery_details wdd2,
14256: wsh_delivery_assignments wda,
14257: wsh_new_deliveries wnd
14258: where wdd1.delivery_detail_id = p_dd_id
14251: wdd1.inventory_item_id,
14252: wdd2.weight_uom_code,
14253: wdd2.volume_uom_code
14254: from wsh_delivery_details wdd1,
14255: wsh_delivery_details wdd2,
14256: wsh_delivery_assignments wda,
14257: wsh_new_deliveries wnd
14258: where wdd1.delivery_detail_id = p_dd_id
14259: and wda.delivery_detail_id = wdd1.delivery_detail_id
14353: -- Lock the delivery assignments.
14354:
14355: BEGIN
14356:
14357: WSH_DELIVERY_DETAILS_PKG.lock_wda_no_compare(p_delivery_detail_id => p_delivery_details_tab(i));
14358:
14359: EXCEPTION
14360: WHEN OTHERS THEN
14361: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
14540: x_return_status OUT NOCOPY VARCHAR2) IS
14541:
14542: CURSOR get_deliver_to_location_id (p_cont_id NUMBER) IS
14543: SELECT wdd.deliver_to_location_id
14544: FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
14545: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
14546: AND wda.parent_delivery_detail_id = p_cont_id;
14547:
14548: cnt NUMBER := 0;