[Home] [Help]
334: msi.weight_uom_code weight_uom,
335: msi.internal_volume * wpc.quantity max_volume,
336: msi.volume_uom_code volume_uom,
337: msi.minimum_fill_percent min_fill_percent
338: from wsh_packed_containers wpc,
339: mtl_system_items msi
340: where wpc.delivery_id = x_delivery_id
341: and msi.inventory_item_id = wpc.container_inventory_item_id
342: and msi.organization_id = wpc.organization_id
383:
384: -- Weight
385: w_count NUMBER := 0;
386: w_net_excess NUMBER := 0;
387: w_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
388: w_seq_excess NUMBER := 0;
389:
390: -- Volume
391: v_count NUMBER := 0;
389:
390: -- Volume
391: v_count NUMBER := 0;
392: v_net_excess NUMBER := 0;
393: v_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
394: v_seq_excess NUMBER := 0;
395:
396: -- Load (based on load relationships)
397: l_count NUMBER := 0;
396: -- Load (based on load relationships)
397: l_count NUMBER := 0;
398: l_sum_excess NUMBER := 0;
399: l_avg_excess NUMBER := 0;
400: l_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
401: l_seq_excess NUMBER := 0;
402:
403: -- Underpacked (based on minimum fill percentage and its basis)
404: fill_basis_flag VARCHAR2(1);
402:
403: -- Underpacked (based on minimum fill percentage and its basis)
404: fill_basis_flag VARCHAR2(1);
405: u_count NUMBER := 0;
406: u_sequence WSH_PACKED_CONTAINERS.sequence_number%TYPE;
407: u_seq_fill NUMBER := 0;
408: u_least_packed NUMBER := 1;
409: u_factor NUMBER := 0; -- temporary result holder
410:
712: CURSOR container_volume(x_del_id NUMBER, x_o_id NUMBER, x_seq_num NUMBER) IS
713: SELECT pc.container_inventory_item_id inventory_item_id,
714: NVL(msi.unit_volume, 0) * pc.quantity volume,
715: msi.volume_uom_code uom_code
716: FROM wsh_packed_containers pc,
717: mtl_system_items msi
718: WHERE pc.delivery_id = x_del_id
719: AND pc.container_inventory_item_id = msi.inventory_item_id
720: AND msi.organization_id = x_o_id
867: CURSOR cont_fill(x_del_id NUMBER, x_o_id NUMBER,
868: x_container_item_id NUMBER, x_seq_num NUMBER) IS
869: SELECT SUM(pc.quantity / cl.max_load_quantity) fill
870: FROM wsh_container_load cl,
871: wsh_packed_containers pc
872: WHERE pc.delivery_id = x_del_id
873: AND NVL(pc.parent_sequence_number, -1) = NVL(x_seq_num, -1)
874: AND cl.load_item_id = pc.container_inventory_item_id
875: AND cl.container_item_id = x_container_item_id
1028: CURSOR get_container_id(
1029: P_container_id NUMBER)
1030: IS
1031: SELECT container_id
1032: FROM wsh_packed_containers
1033: START WITH container_id = P_container_id
1034: CONNECT BY PRIOR container_id = parent_container_id;
1035:
1036: L_pack_flag NUMBER;
1086: SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
1087: P_to_uom,
1088: NVL(msi.unit_weight, 0)) * pc.quantity,
1089: 0) cont_tare_weight
1090: FROM wsh_packed_containers pc,
1091: mtl_system_items msi
1092: WHERE msi.inventory_item_id = pc.container_inventory_item_id
1093: AND container_id = P_container_id
1094: AND msi.organization_id = p_org_id;
1096: CURSOR get_container_id(
1097: P_container_id NUMBER)
1098: IS
1099: SELECT container_id
1100: FROM wsh_packed_containers pc
1101: START WITH container_id = P_container_id
1102: CONNECT BY PRIOR container_id = parent_container_id;
1103:
1104: L_tmp_container_id NUMBER;
1134: SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
1135: P_to_uom,
1136: NVL(msi.unit_weight, 0)) * pc.quantity,
1137: 0) cont_tare_weight
1138: FROM wsh_packed_containers pc,
1139: mtl_system_items msi
1140: WHERE msi.inventory_item_id = pc.container_inventory_item_id
1141: AND container_id = P_container_id
1142: AND msi.organization_id = P_org_id;
1162: SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
1163: P_to_uom,
1164: NVL(msi.unit_weight, 0)) * pc.quantity,
1165: 0) cont_tare_weight
1166: FROM wsh_packed_containers pc,
1167: mtl_system_items msi
1168: WHERE msi.inventory_item_id = pc.container_inventory_item_id
1169: AND container_id = P_container_id
1170: AND msi.organization_id = P_org_id;
1172: CURSOR containers_lookup(
1173: p_del_id IN NUMBER)
1174: IS
1175: SELECT container_id
1176: FROM wsh_packed_containers pc
1177: WHERE pc.delivery_id = p_del_id;
1178:
1179: L_tmp_container_id NUMBER;
1180: L_tare_weight NUMBER := 0;
1217: msi.maximum_load_weight max_wt,
1218: msi.weight_uom_code msi_wt_uom,
1219: msi.volume_uom_code msi_v_uom,
1220: pc.fill_percent fill
1221: FROM wsh_packed_containers pc,
1222: mtl_system_items msi
1223: WHERE pc.delivery_id = p_del_id
1224: AND msi.inventory_item_id = pc.container_inventory_item_id
1225: AND msi.organization_id = p_org_id;
1250:
1251: l_gross_wt := l_net_wt + l_tare_wt;
1252:
1253: IF (l_gross_wt >= 0 AND l_net_wt >= 0 AND l_tare_wt >= 0) THEN
1254: UPDATE wsh_packed_containers
1255: SET gross_weight = l_gross_wt,
1256: net_weight = l_net_wt
1257: WHERE container_id = container.id
1258: AND delivery_id = x_del_id;
1268: x_pack_mode,
1269: l_volume);
1270:
1271: IF (l_volume >= 0) THEN
1272: UPDATE wsh_packed_containers
1273: SET volume = l_volume
1274: WHERE container_id = container.id
1275: AND delivery_id = x_del_id;
1276: END IF;
1317: l_fill_percent := Round(l_fill_percent * 100, 2);
1318: END IF;
1319:
1320: IF (l_fill_percent >= 0) THEN
1321: UPDATE wsh_packed_containers
1322: SET fill_percent = l_fill_percent
1323: WHERE container_id = container.id
1324: AND delivery_id = x_del_id;
1325: END IF;