[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),
696: -- AND inventory_item_status_code='Active';
697:
698: CURSOR Get_Cont_Name (v_cont_inst_id NUMBER) IS
699: SELECT container_name
700: FROM WSH_DELIVERY_DETAILS
701: WHERE delivery_detail_id = v_cont_inst_id
702: AND container_flag = 'Y';
703:
704: --added cursors
702: AND container_flag = 'Y';
703:
704: --added cursors
705: CURSOR Get_Wdd_Nextval IS
706: SELECT wsh_delivery_details_s.nextval
707: FROM sys.dual;
708:
709: CURSOR Get_Wda_Nextval IS
710: SELECT wsh_delivery_assignments_s.nextval
722: l_primary_uom VARCHAR2(3);
723: -- J: W/V Changes
724:
725: l_container_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
726: l_del_assg_rec WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
727:
728: l_row_id VARCHAR2(30);
729: l_del_assg_id NUMBER;
730:
802: AND wlh.SOURCE_TYPE_ID = 1;
803:
804: CURSOR c_get_rowid (v_delivery_detail number) IS
805: SELECT rowid , container_name
806: from wsh_delivery_details
807: WHERE delivery_detail_id = v_delivery_detail;
808:
809:
810:
1076: -- within the Loop
1077:
1078: IF l_debug_on THEN
1079: WSH_DEBUG_SV.logmsg(l_module_name, 'CREATING DELIVERY DETAIL'||p_num_of_containers );
1080: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
1081: END IF;
1082: --
1083:
1084:
1081: END IF;
1082: --
1083:
1084:
1085: WSH_DELIVERY_DETAILS_PKG.create_delivery_details_bulk
1086: ( p_delivery_details_info => l_container_rec,
1087: p_num_of_rec => p_num_of_containers,
1088: p_container_info_rec => l_container_info_rec,
1089: x_return_status => x_return_status,
1132: l_del_assg_rec.parent_delivery_detail_id := null;
1133:
1134: --
1135: IF l_debug_on THEN
1136: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIV_ASSIGNMENT_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
1137: --WSH_DEBUG_SV.logmsg(l_module_name,'NUMBER OF CONTS'||p_num_of_containers);
1138: WSH_DEBUG_SV.logmsg(l_module_name,'NUMBER OF RECORDS IN WDD '||l_cont_dummy_tab.count);
1139: END IF;
1140: --
1138: WSH_DEBUG_SV.logmsg(l_module_name,'NUMBER OF RECORDS IN WDD '||l_cont_dummy_tab.count);
1139: END IF;
1140: --
1141:
1142: WSH_DELIVERY_DETAILS_PKG.create_deliv_assignment_bulk
1143: ( p_delivery_assignments_info => l_del_assg_rec,
1144: p_num_of_rec => p_num_of_containers,
1145: p_dd_id_tab => l_cont_dummy_tab,
1146: x_da_id_tab => l_assignment_dummy_tab,
1340: AND rownum < 2);
1341:
1342: CURSOR Check_Container (v_detail_id NUMBER) IS
1343: SELECT container_flag
1344: FROM WSH_DELIVERY_DETAILS
1345: WHERE delivery_detail_id = v_detail_id;
1346:
1347:
1348:
1433:
1434: --
1435: IF l_debug_on THEN
1436: WSH_DEBUG_SV.log(l_module_name,'Delivery Status',l_del_sts);
1437: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1438: END IF;
1439: --
1440: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details (
1441: p_rowid => null,
1436: WSH_DEBUG_SV.log(l_module_name,'Delivery Status',l_del_sts);
1437: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1438: END IF;
1439: --
1440: WSH_DELIVERY_DETAILS_PKG.Delete_Delivery_Details (
1441: p_rowid => null,
1442: p_delivery_detail_id => p_container_id,
1443: x_return_status => x_return_status);
1444:
1613: AND rownum < 2;
1614:
1615: CURSOR Get_Cont_Item IS
1616: SELECT inventory_item_id, master_serial_number
1617: FROM WSH_DELIVERY_DETAILS
1618: WHERE delivery_detail_id = p_container_instance_id
1619: AND container_flag = 'Y';
1620:
1621: CURSOR Get_Det_Cont_Item (v_detail_id NUMBER) IS
1620:
1621: CURSOR Get_Det_Cont_Item (v_detail_id NUMBER) IS
1622: SELECT nvl(detail_container_item_id, master_container_item_id),
1623: source_line_id, source_header_id,source_code
1624: FROM WSH_DELIVERY_DETAILS
1625: WHERE delivery_detail_id = v_detail_id
1626: AND container_flag = 'N';
1627:
1628: l_del_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
1624: FROM WSH_DELIVERY_DETAILS
1625: WHERE delivery_detail_id = v_detail_id
1626: AND container_flag = 'N';
1627:
1628: l_del_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
1629:
1630: l_group_id_tab WSH_UTIL_CORE.id_tab_type;
1631: -- l_temp_detail_tab WSH_UTIL_CORE.id_tab_type;
1632:
1984: -- create assignment with l_del_detail_id and p_container_instance_id;
1985:
1986: --
1987: IF l_debug_on THEN
1988: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
1989: END IF;
1990: --
1991: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_To_Cont (
1992: l_del_detail_id,
1987: IF l_debug_on THEN
1988: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
1989: END IF;
1990: --
1991: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_To_Cont (
1992: l_del_detail_id,
1993: p_container_instance_id,
1994: x_return_status);
1995:
2056:
2057:
2058: END IF;
2059:
2060: UPDATE WSH_DELIVERY_DETAILS
2061: SET master_serial_number = l_master_serial_number
2062: WHERE delivery_detail_id = l_del_detail_id;
2063:
2064: IF SQL%ROWCOUNT > 1 OR SQL%NOTFOUND THEN
2161: cnt NUMBER;
2162:
2163: CURSOR Get_Min_Fill IS
2164: SELECT minimum_fill_percent
2165: FROM WSH_DELIVERY_DETAILS
2166: WHERE delivery_detail_id = p_container_instance_id
2167: AND container_flag = 'Y';
2168:
2169: /* wms -change : Added inentory_item_id to later check (ignore) fill pc when LPN */
2168:
2169: /* wms -change : Added inentory_item_id to later check (ignore) fill pc when LPN */
2170: CURSOR Is_Container (v_detail_id NUMBER) IS
2171: SELECT container_flag, serial_number, master_serial_number, inventory_item_id
2172: FROM WSH_DELIVERY_DETAILS
2173: WHERE delivery_detail_id = v_detail_id;
2174:
2175: CURSOR Get_Cont (v_detail_id NUMBER) IS
2176: SELECT wda.parent_delivery_detail_id container_instance_id, wda.delivery_id,
2176: SELECT wda.parent_delivery_detail_id container_instance_id, wda.delivery_id,
2177: wddp.organization_id, nvl(wddp.line_direction,'O'), -- K LPN CONV. rv
2178: wddp.container_flag -- K: MDC
2179: FROM wsh_delivery_assignments wda,
2180: wsh_delivery_details wddp -- K LPN CONV. rv
2181: WHERE wda.delivery_detail_id = v_detail_id
2182: AND wda.parent_delivery_detail_id = wddp.delivery_detail_id(+)
2183: AND NVL(wda.type, 'S') in ('S', 'C');
2184:
2222: l_sync_tmp_rec wsh_glbl_var_strct_grp.sync_tmp_rec_type;
2223:
2224: cursor l_get_cnt_org_csr (p_cnt_inst_id IN NUMBER) is
2225: select organization_id
2226: from wsh_delivery_details
2227: where delivery_detail_id = p_cnt_inst_id;
2228:
2229: -- K LPN CONV. rv
2230:
2369:
2370: END IF;
2371: --
2372: IF l_debug_on THEN
2373: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2374: END IF;
2375: --
2376: WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details (
2377: l_del_detail_rec,
2372: IF l_debug_on THEN
2373: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2374: END IF;
2375: --
2376: WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details (
2377: l_del_detail_rec,
2378: p_del_unassign,
2379: p_cont_unassign,
2380: x_return_status,
2527: --}
2528: END IF;
2529: -- K LPN CONV. rv
2530:
2531: UPDATE WSH_DELIVERY_DETAILS
2532: SET customer_id = NULL,
2533: ship_to_location_id = NULL,
2534: intmed_ship_to_location_id = NULL,
2535: fob_code = NULL,
2614: WSH_DEBUG_SV.log(l_module_name,'l_cont_flag',l_cont_flag);
2615: WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',l_del_detail_rec(i));
2616: END IF;
2617: --
2618: UPDATE wsh_delivery_details --bug 5165197
2619: SET master_serial_number = NULL
2620: WHERE delivery_detail_id = l_del_detail_rec(i);
2621: --
2622: ELSIF l_cont_flag = 'Y' THEN --}{
2761: --}
2762: END IF;
2763: -- K LPN CONV. rv
2764:
2765: UPDATE WSH_DELIVERY_DETAILS
2766: SET customer_id = NULL,
2767: ship_to_location_id = NULL,
2768: intmed_ship_to_location_id = NULL,
2769: fob_code = NULL,
2861: --}
2862: END IF;
2863: -- K LPN CONV. rv
2864:
2865: UPDATE WSH_DELIVERY_DETAILS
2866: SET master_serial_number = serial_number
2867: WHERE delivery_detail_id = l_del_detail_rec(i);
2868:
2869: IF SQL%ROWCOUNT > 1 OR SQL%NOTFOUND THEN
3140: -- remove this cursor for Bug
3141:
3142: CURSOR Get_Cont_Org (v_cont_instance_id NUMBER) IS
3143: SELECT organization_id
3144: FROM WSH_DELIVERY_DETAILS
3145: WHERE delivery_detail_id = v_cont_instance_id
3146: AND container_flag = 'Y';
3147:
3148: CURSOR Get_Del_Org (v_del_id NUMBER) IS
3149: SELECT organization_id, freight_terms_code
3150: FROM WSH_NEW_DELIVERIES
3151: WHERE delivery_id = v_del_id;
3152:
3153: l_del_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3154:
3155: l_cont_org NUMBER;
3156: l_del_org NUMBER;
3157:
3432: END IF;
3433: --
3434: --
3435: IF l_debug_on THEN
3436: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
3437: END IF;
3438: --
3439: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_to_Delivery(
3440: P_DETAIL_ID =>p_container_instance_id ,
3435: IF l_debug_on THEN
3436: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
3437: END IF;
3438: --
3439: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Detail_to_Delivery(
3440: P_DETAIL_ID =>p_container_instance_id ,
3441: P_DELIVERY_ID => p_delivery_id,
3442: X_RETURN_STATUS =>x_return_status,
3443: x_dlvy_has_lines => x_dlvy_has_lines,
3592: -- remove this cursor
3593:
3594: CURSOR Get_Cont_Org (v_cont_instance_id NUMBER) IS
3595: SELECT organization_id
3596: FROM WSH_DELIVERY_DETAILS
3597: WHERE delivery_detail_id = v_cont_instance_id
3598: AND container_flag in ('Y', 'C');
3599:
3600: l_det_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3596: FROM WSH_DELIVERY_DETAILS
3597: WHERE delivery_detail_id = v_cont_instance_id
3598: AND container_flag in ('Y', 'C');
3599:
3600: l_det_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3601: l_par_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3602:
3603: l_det_cont_org NUMBER;
3604: l_par_cont_org NUMBER;
3597: WHERE delivery_detail_id = v_cont_instance_id
3598: AND container_flag in ('Y', 'C');
3599:
3600: l_det_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3601: l_par_detail_id WSH_DELIVERY_DETAILS.delivery_detail_id%TYPE;
3602:
3603: l_det_cont_org NUMBER;
3604: l_par_cont_org NUMBER;
3605:
3965: --dbms_output.put_line('creating assignment of cont ' || p_det_cont_inst_id || ' to container ' || p_par_cont_inst_id);
3966: -- just create assignment of child container to parent.
3967: --
3968: IF l_debug_on THEN
3969: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
3970: END IF;
3971: --
3972: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
3973: p_det_cont_inst_id,
3968: IF l_debug_on THEN
3969: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
3970: END IF;
3971: --
3972: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
3973: p_det_cont_inst_id,
3974: p_par_cont_inst_id,
3975: x_return_status);
3976:
4030:
4031: -- just create assignment of child container to parent.
4032: --
4033: IF l_debug_on THEN
4034: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4035: END IF;
4036: --
4037: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4038: p_det_cont_inst_id,
4033: IF l_debug_on THEN
4034: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4035: END IF;
4036: --
4037: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4038: p_det_cont_inst_id,
4039: p_par_cont_inst_id,
4040: x_return_status);
4041:
4119:
4120: -- just create assignment of child container to parent.
4121: --
4122: IF l_debug_on THEN
4123: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4124: END IF;
4125: --
4126: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4127: p_det_cont_inst_id,
4122: IF l_debug_on THEN
4123: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4124: END IF;
4125: --
4126: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4127: p_det_cont_inst_id,
4128: p_par_cont_inst_id,
4129: x_return_status);
4130:
4249:
4250: -- create assignment between child and parent container
4251: --
4252: IF l_debug_on THEN
4253: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4254: END IF;
4255: --
4256: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4257: p_det_cont_inst_id,
4252: IF l_debug_on THEN
4253: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_CONT_TO_CONT',WSH_DEBUG_SV.C_PROC_LEVEL);
4254: END IF;
4255: --
4256: WSH_DELIVERY_DETAILS_ACTIONS.Assign_Cont_To_Cont (
4257: p_det_cont_inst_id,
4258: p_par_cont_inst_id,
4259: x_return_status);
4260:
5022:
5023: --dbms_output.put_line('Splitting dd '||p_line_cont_rec.delivery_detail_id||' with qty '||l_tmp_split_qty);
5024: --
5025: IF l_debug_on THEN
5026: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
5027: WSH_DEBUG_SV.logmsg(l_module_name,'SPLIT QTY IS'||l_tmp_split_qty);
5028: END IF;
5029: --
5030:
5032: -- empty container logic, need to test and modify this
5033: -- the value for p_num_of_split is passed as 1
5034: -- so that split occurs once
5035:
5036: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details_bulk (
5037: p_from_detail_id => p_line_cont_rec.delivery_detail_id,
5038: p_req_quantity => l_tmp_split_qty,
5039: p_req_quantity2 => l_tmp_split_qty2,
5040: p_manual_split => 'C',
5050: --dbms_output.put_line('STATUS after SPLITDD in empty'||l_return_status||l_dd_id_tab.count);
5051:
5052: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5053: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5054: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '|| l_return_status);
5055: x_return_status := C_ERROR_STATUS;
5056: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '||x_return_status);
5057: FND_MESSAGE.SET_NAME('WSH','WSH_DET_SPLIT_ERROR');
5058: FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_line_cont_rec.delivery_detail_id);
5052: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5053: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5054: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '|| l_return_status);
5055: x_return_status := C_ERROR_STATUS;
5056: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '||x_return_status);
5057: FND_MESSAGE.SET_NAME('WSH','WSH_DET_SPLIT_ERROR');
5058: FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_line_cont_rec.delivery_detail_id);
5059: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
5060: --
5271: -- End BugFix#3475352
5272:
5273: --dbms_output.put_line('Splitting dd '||p_line_cont_rec.delivery_detail_id||' with qty '||l_split_qty);
5274: IF l_debug_on THEN
5275: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
5276: WSH_DEBUG_SV.logmsg(l_module_name,'Split Qty is'||l_split_qty);
5277: WSH_DEBUG_SV.logmsg(l_module_name,'Number of split is'||l_num_of_split);
5278: END IF;
5279:
5281: -- Call BULK Split API
5282:
5283: --dbms_output.put_line('======BEFORE SPLIT'||l_dd_id_tab.count||'<'||l_num_of_split);
5284:
5285: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details_bulk (
5286: p_from_detail_id => p_line_cont_rec.delivery_detail_id,
5287: p_req_quantity => l_split_qty,
5288: p_req_quantity2 => l_split_qty2,
5289: p_manual_split => 'C',
5298: END IF;
5299:
5300: IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
5301: WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
5302: --dbms_output.put_line('WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details returned '|| l_return_status);
5303: x_return_status := C_ERROR_STATUS;
5304: FND_MESSAGE.SET_NAME('WSH','WSH_DET_SPLIT_ERROR');
5305: FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_line_cont_rec.delivery_detail_id);
5306: WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
5521: wdd.source_code,
5522: wda.parent_delivery_detail_id parent_delivery_detail_id,
5523: wda.delivery_id delivery_id, -- added delivery id
5524: msi.indivisible_flag indivisible_flag
5525: FROM WSH_DELIVERY_DETAILS wdd,
5526: wsh_delivery_assignments_v wda,
5527: MTL_SYSTEM_ITEMS msi,
5528: WSH_TMP wt
5529: WHERE wdd.delivery_detail_id = wt.id
5576: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_DD_FOR_PACK';
5577: --
5578:
5579: -- HW OPMCONV - New variable to hold item info
5580: l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
5581: -- HW OPM BUG# 3011758
5582: -- HW OPMCONV - Removed opm variables
5583:
5584: -- HW OPMCONV - Renamed variable from lot_indivisible to l_lot_divisible_flag
5707: -- HW OPMCONV - 1)Change the call from OPM API to get item info
5708: -- to a new WSH API
5709: -- - 2) Remove checking for process
5710:
5711: WSH_DELIVERY_DETAILS_INV.Get_item_information
5712: (
5713: p_organization_id => rec.organization_id
5714: , p_inventory_item_id => rec.inventory_item_id
5715: , x_mtl_system_items_rec => l_item_info
6300: deliver_to_location_id,
6301: NVL(line_direction,'O') line_direction, -- J-IB-NPARIKH
6302: shipping_control, -- J-IB-NPARIKH
6303: NVL(ignore_for_planning,'N') ignore_for_planning
6304: FROM wsh_delivery_details
6305: WHERE delivery_detail_id = v_delivery_detail_id;
6306:
6307: -- OTM R12 : assign delivery detail
6308: CURSOR c_get_tare_weight(v_detail_id NUMBER) IS
6306:
6307: -- OTM R12 : assign delivery detail
6308: CURSOR c_get_tare_weight(v_detail_id NUMBER) IS
6309: SELECT nvl(unit_weight, 0)
6310: FROM wsh_delivery_details
6311: WHERE delivery_detail_id = v_detail_id;
6312:
6313: CURSOR c_get_plan_and_tms_flag(v_delivery_id NUMBER) IS
6314: SELECT nvl(ignore_for_planning, 'N'),
6444: l_gc3_is_installed VARCHAR2(1);
6445: l_is_delivery_empty VARCHAR2(1);
6446: l_index NUMBER;
6447: l_index_update NUMBER;
6448: l_tare_weight WSH_DELIVERY_DETAILS.UNIT_WEIGHT%TYPE;
6449: l_ignore_for_planning_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
6450: l_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
6451: l_delivery_was_empty_tab BOOLEAN_TAB_TYPE;
6452: l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
7197: l_mdc_id_tab(l_mdc_index_j) := l_ddlist(l_mdc_index_i);
7198: l_mdc_index_i := l_ddlist.next(l_mdc_index_i);
7199:
7200: END LOOP;
7201: WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
7202: p_detail_id_tab => l_mdc_id_tab,
7203: x_return_status => x_return_status);
7204:
7205: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7334: END IF;
7335: -- K LPN CONV. rv
7336: -- update container with all the attributes
7337: FORALL cnt IN 1..g_assign_detail_tab.COUNT
7338: UPDATE WSH_DELIVERY_DETAILS
7339: SET customer_id = l_tab_cust_id(cnt),
7340: ship_to_location_id = l_tab_ship_location_id(cnt),
7341: intmed_ship_to_location_id = l_tab_intmed_location_id(cnt),
7342: fob_code = l_tab_fob_code(cnt),
7594: l_mdc_index_i := l_contlist1.next(l_mdc_index_i);
7595:
7596: END LOOP;
7597:
7598: WSH_DELIVERY_DETAILS_ACTIONS.Create_Consol_Record(
7599: p_detail_id_tab => l_mdc_id_tab,
7600: x_return_status => x_return_status);
7601:
7602: IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
7642: -- 75.1 Bulk Update Weight/Volume on packed delivery details
7643: -- added the WHO columns in the UPDATE
7644: -- in WSHWVUTB.pls , update_container_wt there are no WHO columns
7645: FORALL cnt IN 1..g_assign_detail_tab.COUNT
7646: UPDATE WSH_DELIVERY_DETAILS
7647: SET NET_WEIGHT = l_NetWtlist(cnt),
7648: GROSS_WEIGHT = l_GrossWtlist(cnt),
7649: VOLUME = l_Vollist(cnt),
7650: last_update_date = SYSDATE,
7875:
7876: CURSOR Get_Delivery_Lines (v_delivery_id NUMBER) IS
7877: SELECT wda.delivery_detail_id
7878: FROM wsh_delivery_assignments_v wda,
7879: WSH_DELIVERY_DETAILS wdd
7880: WHERE wda.delivery_id = v_delivery_id
7881: AND wda.delivery_id IS NOT NULL
7882: AND wda.parent_delivery_detail_id IS NULL
7883: AND wda.delivery_detail_id = wdd.delivery_detail_id
8271: CURSOR Get_Cont_Info (v_cont_instance_id NUMBER) IS
8272: SELECT inventory_item_id, organization_id, gross_weight, net_weight,
8273: volume, weight_uom_code, volume_uom_code, fill_percent,
8274: minimum_fill_percent, maximum_load_weight, maximum_volume
8275: FROM wsh_delivery_details
8276: WHERE delivery_detail_id = v_cont_instance_id
8277: AND container_flag = 'Y';
8278:
8279: CURSOR Get_Detail_Info (v_del_detail_id NUMBER) IS
8281: requested_quantity_uom, net_weight, volume,
8282: weight_uom_code, volume_uom_code, organization_id,
8283: nvl(detail_container_item_id, master_container_item_id) cont_item_id,
8284: nvl(shipped_quantity2, NVL(picked_quantity2, requested_quantity2)) pack_qty2
8285: FROM wsh_delivery_details
8286: WHERE delivery_detail_id = v_del_detail_id
8287: AND container_flag = 'N';
8288:
8289: TYPE Cont_Rec IS RECORD (container_instance_id NUMBER, pack_status VARCHAR2(30));
8400:
8401: -- HW OPMCONV - Renamed variable to l_lot_divisible_flag
8402: l_lot_divisible_flag VARCHAR2(1);
8403: -- HW OPMCONV - New variable to hold item info
8404: l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
8405:
8406: -- LPN CONV. rv
8407: l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
8408: l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
8712: -- HW OPMCONV - 1) Removed branching code
8713: -- - 2) Removed call to OPM API to get item info
8714: -- - 3) New WSH API call to get item info
8715:
8716: WSH_DELIVERY_DETAILS_INV.Get_item_information
8717: (
8718: p_organization_id =>l_det_org_id
8719: , p_inventory_item_id => l_det_inv_item_id
8720: , x_mtl_system_items_rec => l_item_info
9032: WSH_DEBUG_SV.log(l_module_name,'l_split_qty',l_split_qty);
9033: WSH_DEBUG_SV.log(l_module_name,'l_split_qty2',l_split_qty2);
9034: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty2',l_det_pack_qty2);
9035: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty',l_det_pack_qty);
9036: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9037: END IF;
9038: --
9039:
9040: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9036: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9037: END IF;
9038: --
9039:
9040: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9041: p_from_detail_id => l_det_tab(j).delivery_detail_id,
9042: p_req_quantity => l_split_qty,
9043: p_req_quantity2 => l_split_qty2,
9044: x_new_detail_id => l_split_det_id,
9203: -- HW OPMCONV - 1)Change the call from OPM API to get item info
9204: -- to a new WSH API
9205: -- - 2) Remove checking for process
9206:
9207: WSH_DELIVERY_DETAILS_INV.Get_item_information
9208: (
9209: p_organization_id => l_det_org_id
9210: , p_inventory_item_id => l_det_inv_item_id
9211: , x_mtl_system_items_rec => l_item_info
9351: WSH_DEBUG_SV.log(l_module_name,'l_split_qty',l_split_qty);
9352: WSH_DEBUG_SV.log(l_module_name,'l_split_qty2',l_split_qty2);
9353: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty2',l_det_pack_qty2);
9354: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty',l_det_pack_qty);
9355: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9356: END IF;
9357:
9358: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9359: p_from_detail_id => l_det_tab(j).delivery_detail_id,
9354: WSH_DEBUG_SV.log(l_module_name,'l_det_pack_qty',l_det_pack_qty);
9355: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
9356: END IF;
9357:
9358: WSH_DELIVERY_DETAILS_ACTIONS.Split_Delivery_Details (
9359: p_from_detail_id => l_det_tab(j).delivery_detail_id,
9360: p_req_quantity => l_split_qty,
9361: p_req_quantity2 => l_split_qty2,
9362: x_new_detail_id => l_split_det_id,
10019: x_return_status OUT NOCOPY VARCHAR2) IS
10020:
10021: CURSOR Get_Ship_Qty (v_det_id NUMBER) IS
10022: SELECT nvl(shipped_quantity,-99)
10023: FROM WSH_DELIVERY_DETAILS
10024: WHERE delivery_detail_id = v_det_id
10025: AND nvl(line_direction,'O') IN ('O','IO')
10026: AND container_flag = 'N';
10027:
10124:
10125: l_shp_qty := l_shp_qty - nvl(p_split_qty,0) + l_db_split_shp_qty;
10126: --dbms_output.put_line('new l_shp_qty = ' || l_shp_qty);
10127:
10128: UPDATE WSH_DELIVERY_DETAILS
10129: SET shipped_quantity = l_shp_qty
10130: WHERE delivery_detail_id = p_delivery_detail_id;
10131:
10132: IF SQL%NOTFOUND THEN
10141: --
10142: return;
10143: END IF;
10144:
10145: UPDATE WSH_DELIVERY_DETAILS
10146: SET shipped_quantity = p_split_qty
10147: WHERE delivery_detail_id = p_split_detail_id;
10148:
10149: IF SQL%NOTFOUND THEN
10434: wdd.organization_id organization_id,
10435: wdd.delivery_detail_id,
10436: wda.parent_delivery_detail_id parent_delivery_detail_id,
10437: nvl(wdd.detail_container_item_id,wdd.master_container_item_id) detail_container_item_id
10438: FROM wsh_delivery_details wdd,
10439: wsh_delivery_assignments_v wda
10440: WHERE wdd.delivery_detail_id = c_dd_id
10441: AND wdd.delivery_detail_id = wda.delivery_detail_id
10442: AND source_code = 'WSH';
11068: CURSOR c_get_detail(p_del_det_id NUMBER) IS
11069: SELECT nvl(ignore_for_planning, 'N') ignore_for_planning,
11070: organization_id, -- K LPN CON. rv
11071: nvl(line_direction,'O') line_direction -- K LPN CONV. rv
11072: FROM wsh_delivery_details
11073: WHERE delivery_detail_id=p_del_det_id;
11074:
11075: -- K LPN CONV. rv
11076: l_wms_org VARCHAR2(10) := 'N';
11402: END IF;
11403: --}
11404: END IF;
11405: -- K LPN CONV. rv
11406: UPDATE wsh_delivery_details
11407: SET ignore_for_planning=cur.ignore_for_planning
11408: WHERE delivery_detail_id=l_cont_instance_id;
11409:
11410: IF SQL%NOTFOUND THEN
11536: shipping_control, -- J-IB-NPARIKH
11537: vendor_id, -- J-IB-NPARIKH
11538: party_id, -- J-IB-NPARIKH
11539: nvl(ignore_for_planning, 'N') ignore_for_planning --J TP Release
11540: FROM WSH_DELIVERY_DETAILS
11541: WHERE delivery_detail_id = v_detail_id;
11542:
11543: CURSOR Get_Del_Attr (v_del_id NUMBER) IS
11544: SELECT organization_id, customer_id, ultimate_dropoff_location_id,
11753: --
11754: --}
11755: END IF;
11756: -- K LPN CONV. rv
11757: UPDATE WSH_DELIVERY_DETAILS SET
11758: customer_id = l_cust_id,
11759: ship_to_location_id = l_ship_to_loc_id,
11760: intmed_ship_to_location_id = l_intmed_loc_id,
11761: fob_code = l_fob_code,
11843: SELECT customer_id, intmed_ship_to_location_id,
11844: fob_code, freight_terms_code, ship_method_code,
11845: mode_of_transport, service_level, carrier_id,
11846: deliver_to_location_id
11847: FROM WSH_DELIVERY_DETAILS
11848: WHERE delivery_detail_id = v_detail_id;
11849:
11850: l_cont_name VARCHAR2(30);
11851:
12003: CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
12004:
12005: CURSOR Get_Cont_Flag (v_detail_id NUMBER) IS
12006: SELECT container_flag
12007: FROM WSH_DELIVERY_DETAILS
12008: WHERE delivery_detail_id = v_detail_id;
12009:
12010: l_master_cont_inst_id NUMBER;
12011: l_master_cont_name VARCHAR2(30);
12176: SELECT count(*)
12177: FROM wsh_delivery_assignments_v
12178: WHERE delivery_detail_id IN
12179: (SELECT delivery_detail_id
12180: FROM WSH_DELIVERY_DETAILS
12181: WHERE container_flag = 'Y')
12182: START WITH delivery_detail_id = v_cont_id
12183: CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
12184:
12385: select wnd.delivery_id,
12386: wnd.name,
12387: wnd.status_code,
12388: wda.delivery_detail_id
12389: from wsh_delivery_details wdd,
12390: wsh_delivery_assignments_v wda,
12391: wsh_new_deliveries wnd
12392: where wdd.container_name = p_lpn_name
12393: and wdd.container_flag = 'Y'
12511: END IF;
12512:
12513: IF(l_dup_del_id is not null AND nvl(l_dup_del_status,'OP') = 'CL') THEN
12514: --{
12515: update wsh_delivery_details
12516: set container_name = SUBSTRB(p_lpn_name || '.' ||l_dup_del_name,1,50)
12517: where delivery_detail_id = l_dup_cnt_id;
12518: --}
12519: ELSIF(l_dup_cnt_id IS NOT NULL) THEN
12572: --
12573: --
12574: -- Update Container record with delivery grouping attributes
12575: --
12576: UPDATE WSH_DELIVERY_DETAILS
12577: SET lpn_id = p_lpn_id,
12578: shipped_quantity = DECODE(p_transactionType,'ASN',1,shipped_quantity),
12579: received_quantity = DECODE(p_transactionType,'RECEIPT',1,received_quantity),
12580: released_status = DECODE(p_transactionType,'ASN','C','RECEIPT','L','C'),
12600: WHERE rowid = l_rowid;
12601: --
12602: -- l_container_instance_id is the delivery_detail_id, use for DBI call
12603: -- DBI Project
12604: -- Update of wsh_delivery_details where requested_quantity/released_status
12605: -- are changed, call DBI API after the update.
12606: -- This API will also check for DBI Installed or not
12607: IF l_debug_on THEN
12608: WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_container_instance_id);
12881: --
12882: --
12883: -- Delete all containers within the delivery
12884: --
12885: DELETE WSH_DELIVERY_DETAILS
12886: WHERE DELIVERY_DETAIL_ID IN (
12887: SELECT wdd.delivery_detail_id
12888: FROM wsh_delivery_details wdd,
12889: wsh_delivery_assignments_v wda
12884: --
12885: DELETE WSH_DELIVERY_DETAILS
12886: WHERE DELIVERY_DETAIL_ID IN (
12887: SELECT wdd.delivery_detail_id
12888: FROM wsh_delivery_details wdd,
12889: wsh_delivery_assignments_v wda
12890: WHERE wda.delivery_id = p_delivery_id
12891: AND wda.delivery_detail_id = wdd.delivery_detail_id
12892: AND NVL(wdd.container_flag,'N') = 'Y'
12991: x_return_status OUT NOCOPY VARCHAR2) IS
12992:
12993: CURSOR Get_Exist_Cont(v_cont_name VARCHAR2) IS
12994: SELECT NVL(MAX(1),0) FROM DUAL
12995: WHERE EXISTS ( SELECT 1 FROM WSH_DELIVERY_DETAILS
12996: WHERE container_name = v_cont_name
12997: AND container_flag = 'Y');
12998:
12999: l_cont_name VARCHAR2(30);
13147: PRAGMA EXCEPTION_INIT(child_cont_locked, -00054);
13148:
13149: CURSOR lock_containers(v_container_id NUMBER) IS
13150: SELECT delivery_detail_id
13151: FROM wsh_delivery_details
13152: --WHERE container_flag = 'Y'
13153: WHERE delivery_detail_id IN
13154: (SELECT delivery_detail_id
13155: FROM wsh_delivery_assignments
13184: LOOP --{
13185: FETCH lock_containers INTO l_container_id;
13186: EXIT WHEN lock_containers%NOTFOUND;
13187:
13188: UPDATE wsh_delivery_details
13189: SET locator_id = p_locator_id,
13190: subinventory = p_subinventory
13191: WHERE delivery_detail_id = l_container_id;
13192:
13247: x_return_status OUT NOCOPY VARCHAR2) IS
13248:
13249: cursor c_get_container_info(p_delivery_detail_id in number) is
13250: select d.container_flag, a.delivery_id, a.parent_delivery_id, d.inventory_item_id, d.organization_id
13251: from wsh_delivery_details d, wsh_delivery_assignments a
13252: where d.delivery_detail_id = p_delivery_detail_id
13253: and d.container_flag in ('Y', 'C')
13254: and a.type in ('C', 'S')
13255: and a.delivery_detail_id = d.delivery_detail_id
13307:
13308: l_assign_flag VARCHAR2(1);
13309: l_del_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
13310: l_parent_del_info_rec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
13311: l_del_asg_info_rec WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_TYPE;
13312: l_child_details_tab wsh_util_core.id_tab_type;
13313: l_dummy_asg_tab wsh_util_core.id_tab_type;
13314: l_wv_delivery_tab wsh_util_core.id_tab_type;
13315: l_child_delivery_id NUMBER;
13335: l_return_status VARCHAR2(30);
13336:
13337: CURSOR c_get_filled_vol(p_container_id NUMBER) IS
13338: SELECT filled_volume
13339: FROM WSH_DELIVERY_DETAILS
13340: WHERE delivery_detail_id = p_container_id;
13341:
13342: l_filled_volume NUMBER;
13343:
13628:
13629: -- Lock the delivery details and assignments.
13630:
13631: BEGIN
13632: wsh_delivery_details_pkg.lock_detail_no_compare(p_delivery_detail_id => p_parent_container_id);
13633: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_parent_container_id);
13634: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_child_container_id);
13635: EXCEPTION
13636: WHEN OTHERS THEN
13629: -- Lock the delivery details and assignments.
13630:
13631: BEGIN
13632: wsh_delivery_details_pkg.lock_detail_no_compare(p_delivery_detail_id => p_parent_container_id);
13633: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_parent_container_id);
13634: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_child_container_id);
13635: EXCEPTION
13636: WHEN OTHERS THEN
13637: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13630:
13631: BEGIN
13632: wsh_delivery_details_pkg.lock_detail_no_compare(p_delivery_detail_id => p_parent_container_id);
13633: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_parent_container_id);
13634: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => p_child_container_id);
13635: EXCEPTION
13636: WHEN OTHERS THEN
13637: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
13638: FND_MESSAGE.SET_NAME('WSH','WSH_NO_LOCK');
13648: --
13649: END;
13650:
13651:
13652: update wsh_delivery_details
13653: set container_flag = 'C',
13654: ship_to_location_id = l_parent_del_info_rec.ultimate_dropoff_location_id,
13655: intmed_ship_to_location_id = l_parent_del_info_rec.intmed_ship_to_location_id,
13656: customer_id = l_parent_del_info_rec.customer_id,
13717: -- lock the children.
13718:
13719: BEGIN
13720: FOR i in 1..l_child_details_tab.count LOOP
13721: wsh_delivery_details_pkg.lock_wda_no_compare(p_delivery_detail_id => l_child_details_tab(i));
13722: END LOOP;
13723:
13724: EXCEPTION
13725: WHEN OTHERS THEN
13769: l_del_asg_info_rec.parent_delivery_id := l_new_parent_delivery_id;
13770: l_del_asg_info_rec.type := 'C';
13771:
13772: IF l_debug_on THEN
13773: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk',WSH_DEBUG_SV.C_PROC_LEVEL);
13774: END IF;
13775: WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk(
13776: p_delivery_assignments_info => l_del_asg_info_rec,
13777: p_num_of_rec => l_child_details_tab.count,
13771:
13772: IF l_debug_on THEN
13773: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk',WSH_DEBUG_SV.C_PROC_LEVEL);
13774: END IF;
13775: WSH_Delivery_Details_PKG.Create_Deliv_Assignment_bulk(
13776: p_delivery_assignments_info => l_del_asg_info_rec,
13777: p_num_of_rec => l_child_details_tab.count,
13778: p_dd_id_tab => l_child_details_tab,
13779: x_da_id_tab => l_dummy_asg_tab,
13924: p_calc_del_wv => 'Y',
13925: x_return_status => l_return_status,
13926: p_suppress_errors => 'N');
13927:
13928: UPDATE wsh_delivery_details
13929: SET filled_volume = l_filled_volume
13930: WHERE delivery_detail_id = p_parent_container_id;
13931:
13932: IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
14100: wdd1.volume_uom_code,
14101: wdd1.inventory_item_id,
14102: wdd2.weight_uom_code,
14103: wdd2.volume_uom_code
14104: from wsh_delivery_details wdd1,
14105: wsh_delivery_details wdd2,
14106: wsh_delivery_assignments wda,
14107: wsh_new_deliveries wnd
14108: where wdd1.delivery_detail_id = p_dd_id
14101: wdd1.inventory_item_id,
14102: wdd2.weight_uom_code,
14103: wdd2.volume_uom_code
14104: from wsh_delivery_details wdd1,
14105: wsh_delivery_details wdd2,
14106: wsh_delivery_assignments wda,
14107: wsh_new_deliveries wnd
14108: where wdd1.delivery_detail_id = p_dd_id
14109: and wda.delivery_detail_id = wdd1.delivery_detail_id
14203: -- Lock the delivery assignments.
14204:
14205: BEGIN
14206:
14207: WSH_DELIVERY_DETAILS_PKG.lock_wda_no_compare(p_delivery_detail_id => p_delivery_details_tab(i));
14208:
14209: EXCEPTION
14210: WHEN OTHERS THEN
14211: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;