[Home] [Help]
383: msi.weight_uom_code,
384: msi.volume_uom_code,
385: msi.unit_weight,
386: msi.unit_volume
387: FROM wsh_delivery_details wdd,
388: mtl_system_items msi
389: WHERE wdd.delivery_detail_id = v_delivery_detail_id
390: AND msi.inventory_item_id = wdd.inventory_item_id
391: AND msi.organization_id = wdd.organization_id;
407: --lpn conv
408: wdd.container_flag,
409: NVL(wdd.wv_frozen_flag,'Y'),
410: nvl(line_direction,'O') line_direction -- LPN CONV. rv
411: from wsh_delivery_details wdd
412: where wdd.delivery_detail_id = v_delivery_detail_id;
413:
414: l_debug_on BOOLEAN;
415: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DETAIL_WEIGHT_VOLUME';
610: --}
611: END IF;
612: -- K LPN CONV. rv
613: --
614: UPDATE WSH_DELIVERY_DETAILS
615: SET net_weight = x_net_weight,
616: volume = x_volume,
617: gross_weight = x_net_weight,
618: weight_uom_code = nvl(weight_uom_code,l_item_wt_code),
655: END IF;
656: -- end of bug 3711017
657:
658: -- OTM R12 : packing ECO
659: -- after update wsh_delivery_details sql
660: -- if any weight/vol changed, mark the global variable
661: IF (G_DELIVERY_TMS_IMPACT = 'N' AND
662: (NVL(l_net_weight, -1) <> NVL(x_net_weight, -1) OR
663: NVL(l_volume, -1) <> NVL(x_volume, -1) OR
1347: l_frozen_flag,
1348: -- MDC
1349: l_wda_type,
1350: l_line_direction
1351: FROM wsh_delivery_details wdd,
1352: wsh_delivery_assignments wda
1353: WHERE wdd.delivery_detail_id = p_entity_id
1354: AND wdd.delivery_detail_id = wda.delivery_detail_id
1355: AND NVL(wda.type, 'S') in ('S', 'C')
1649: wdd.line_direction,
1650: -- MDC
1651: NVL(wda.type, 'S')
1652: FROM wsh_delivery_assignments wda,
1653: wsh_delivery_details wdd
1654: WHERE wdd.delivery_detail_id = p_delivery_detail_id
1655: AND NVL(wda.type, 'S') in ('S', 'C')
1656: AND wdd.delivery_detail_id = wda.delivery_detail_id;
1657:
1673: gross_weight,
1674: net_weight,
1675: volume,
1676: filled_volume
1677: FROM wsh_delivery_details
1678: WHERE delivery_detail_id = c_container_id;
1679:
1680: CURSOR C5(c_delivery_id IN NUMBER) IS
1681: SELECT gross_weight,
1724: l_item_id NUMBER;
1725:
1726: cursor c_get_item_id (v_delivery_detail_id number) IS
1727: SELECT inventory_item_id
1728: FROM wsh_delivery_details
1729: WHERE delivery_detail_id = v_delivery_detail_id;
1730:
1731: e_abort exception;
1732:
2466: INTO l_org_gross_wt,
2467: l_org_net_wt,
2468: l_org_vol,
2469: l_frozen_flag
2470: FROM wsh_delivery_details
2471: WHERE delivery_detail_id = p_delivery_detail_id
2472: AND container_flag = 'N';
2473: EXCEPTION
2474: WHEN NO_DATA_FOUND THEN
2610: filled_volume,
2611: container_flag,
2612: nvl(line_direction, 'O') line_direction, -- LPN CONV. rv
2613: organization_id -- LPN CONV. rv
2614: FROM wsh_delivery_details
2615: WHERE delivery_detail_id = p_container_instance_id;
2616:
2617: l_net_weight NUMBER := 0;
2618: l_gross_weight NUMBER := 0;
2734: --}
2735: END IF;
2736: -- K LPN CONV. rv
2737:
2738: UPDATE WSH_DELIVERY_DETAILS
2739: SET net_weight = p_net_weight,
2740: gross_weight = p_gross_weight,
2741: volume = p_volume,
2742: filled_volume= p_filled_volume,
2801:
2802: END IF;
2803:
2804: -- OTM R12 : packing ECO
2805: -- after update wsh_delivery_details sql
2806: -- if any weight/vol changed, mark the global variable
2807: IF (G_DELIVERY_TMS_IMPACT = 'N' AND
2808: (NVL(l_net_weight, -1) <> NVL(p_net_weight, -1) OR
2809: NVL(l_gross_weight, -1) <> NVL(p_gross_weight, -1) OR
3010: nvl(wdd.received_quantity, nvl(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))),
3011: wdd.inventory_item_id
3012: ) qty
3013: FROM mtl_system_items msi,
3014: wsh_delivery_details wdd
3015: WHERE msi.inventory_item_id (+) = wdd.inventory_item_id
3016: AND msi.organization_id (+) = wdd.organization_id
3017: AND wdd.delivery_detail_id = p_deliveryDetailId;
3018: --
3291: WSH_DEBUG_SV.logmsg(l_module_name,'Update W/V for DD '||childInfo_rec.delivery_detail_id);
3292: END IF;
3293: -- LPN CONV. rv
3294: -- no call reqd. because this update is for non-container lines
3295: UPDATE wsh_delivery_details
3296: SET net_weight = l_line_grossWeight_orig,
3297: gross_weight = l_line_grossWeight_orig,
3298: volume = l_line_volume_orig,
3299: unit_weight = NVL(unit_weight,childInfo_rec.unit_weight),
3329: raise mark_reprice_error;
3330: END IF;
3331:
3332: -- OTM R12 : packing ECO
3333: -- after update wsh_delivery_details sql
3334: -- if any weight/vol changed, mark the global variable
3335:
3336: -- G_DELIVERY_TMS_IMPACT is set to Y for the first one
3337: -- meeting the condition in immediateChildren_rec cursor,
3727: l_org_vol,
3728: l_org_fill_pc,
3729: l_organization_id,
3730: l_frozen_flag
3731: FROM wsh_delivery_details
3732: WHERE delivery_detail_id = p_container_instance_id
3733: AND container_flag IN ('Y', 'C');
3734: EXCEPTION
3735: WHEN NO_DATA_FOUND THEN
3851: -- for entities whose W/V is manually entered
3852: -- OTM R12 : packing ECO
3853: -- This procedure is modified to keep track of delivery
3854: -- detail wt/vol updates. Called first by WSHDDGPB
3855: -- (WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action),
3856: -- so the logic of checking G_RESET_WV is here.
3857: -- Parameters :
3858: -- IN:
3859: -- p_detail_rows IN wsh_util_core.id_tab_type REQUIRED
3874: x_return_status OUT NOCOPY VARCHAR2) IS
3875:
3876: CURSOR c_container_flag(x_id NUMBER) IS
3877: SELECT container_flag
3878: FROM wsh_delivery_details
3879: WHERE delivery_detail_id = x_id;
3880:
3881: -- OTM R12 : packing ECO
3882: CURSOR c_get_delivery_info(p_detail_id IN NUMBER) IS
3902: l_gross_weight NUMBER;
3903: l_volume NUMBER;
3904: l_fill_pc NUMBER;
3905: l_num_error NUMBER := 0;
3906: l_flag WSH_DELIVERY_DETAILS.CONTAINER_FLAG%TYPE;
3907:
3908: others EXCEPTION;
3909:
3910: l_debug_on BOOLEAN;
4111: nvl(unit_weight,0),
4112: weight_uom_code, volume_uom_code, organization_id,
4113: maximum_load_weight, maximum_volume, fill_percent, container_flag, container_name,
4114: nvl(line_direction, 'O') line_direction, organization_id
4115: FROM wsh_delivery_details
4116: WHERE delivery_detail_id = v_cont_instance_id;
4117:
4118: CURSOR Get_Cont_Contents (v_cont_instance_id NUMBER, v_org_id NUMBER) IS
4119: SELECT wda.delivery_detail_id,container_flag,
4120: inventory_item_id,
4121: NVL(received_quantity, -- J-IB-NPARIKH
4122: nvl(shipped_quantity, NVL(picked_quantity, requested_quantity))) pack_qty
4123: FROM wsh_delivery_assignments_v wda,
4124: wsh_delivery_details wdd
4125: WHERE wda.parent_delivery_detail_id = v_cont_instance_id AND
4126: wdd.delivery_detail_id = wda.delivery_detail_id;
4127:
4128:
4509: END IF;
4510: --}
4511: END IF;
4512: -- K LPN CONV. rv
4513: UPDATE WSH_DELIVERY_DETAILS
4514: SET fill_percent = x_fill_percent
4515: WHERE delivery_detail_id = p_container_instance_id;
4516:
4517: IF SQL%NOTFOUND THEN
4587: AND organization_id = v_org_id;
4588:
4589: CURSOR Get_Cont_Info (v_cont_inst_id NUMBER) IS
4590: SELECT inventory_item_id, weight_uom_code, organization_id
4591: FROM wsh_delivery_details
4592: WHERE delivery_detail_id = v_cont_inst_id AND
4593: container_flag = 'Y';
4594:
4595: l_cont_item_id NUMBER;
4740:
4741: /* wms - change : to check for LPN grouping, added inventory_item_id */
4742: CURSOR Get_Cont_Info IS
4743: SELECT fill_percent, minimum_fill_percent, inventory_item_id, organization_id
4744: FROM WSH_DELIVERY_DETAILS
4745: WHERE delivery_detail_id = p_container_instance_id AND
4746: container_flag = 'Y';
4747:
4748: l_fill_pc NUMBER;
4950: wdd.volume vol,
4951: wdd.inventory_item_id,
4952: wdd.weight_uom_code wt_uom,
4953: wdd.volume_uom_code vol_uom
4954: FROM wsh_delivery_details wdd,
4955: wsh_delivery_assignments_v wda
4956: WHERE wdd.delivery_detail_id = wda.delivery_detail_id AND
4957: wda.delivery_id = p_delivery_id AND
4958: wda.delivery_id IS NOT NULL AND
4967: dd.inventory_item_id inventory_item_id,
4968: dd.weight_uom_code wt_uom,
4969: dd.volume_uom_code vol_uom
4970: FROM wsh_delivery_assignments_v da,
4971: wsh_delivery_details dd
4972: WHERE da.delivery_id = p_delivery_id AND
4973: da.delivery_id IS NOT NULL AND
4974: da.delivery_detail_id = dd.delivery_detail_id AND
4975: dd.container_flag = 'Y' AND
5010: dd.volume vol,
5011: dd.inventory_item_id inventory_item_id,
5012: dd.weight_uom_code wt_uom,
5013: dd.volume_uom_code vol_uom
5014: from wsh_delivery_details dd, wsh_delivery_assignments da
5015: where dd.delivery_detail_id = da.delivery_detail_id
5016: and dd.container_flag = 'C'
5017: and NVL(da.type, 'S') = 'S'
5018: and da.delivery_id = p_delivery_id
5030: dd.inventory_item_id inventory_item_id,
5031: dd.weight_uom_code wt_uom,
5032: dd.volume_uom_code vol_uom
5033: FROM wsh_delivery_assignments da,
5034: wsh_delivery_details dd
5035: WHERE da.parent_delivery_detail_id = p_consol_lpn AND
5036: da.delivery_detail_id = dd.delivery_detail_id AND
5037: da.type = 'C';
5038:
6441: wdd.container_flag,
6442: nvl(wdd.line_direction, 'O') line_direction, -- LPN CONV. rv
6443: wdd.organization_id organization_id -- LPN CONV. rv
6444: FROM wsh_delivery_assignments_v wda,
6445: wsh_delivery_details wdd
6446: WHERE wda.delivery_id = p_delivery_id
6447: AND wda.parent_delivery_detail_id IS NULL
6448: AND wda.delivery_detail_id = wdd.delivery_detail_id
6449: AND nvl(wdd.gross_weight,0) > 0
6462: wdd.container_flag,
6463: nvl(wdd.line_direction, 'O') line_direction, -- LPN CONV. rv
6464: wdd.organization_id organization_id -- LPN CONV. rv
6465: FROM wsh_delivery_assignments_v wda,
6466: wsh_delivery_details wdd
6467: WHERE wda.parent_delivery_detail_id IS NOT NULL
6468: AND wda.parent_delivery_detail_id = p_container_id
6469: AND wda.delivery_detail_id = wdd.delivery_detail_id
6470: AND nvl(wdd.gross_weight,0) > 0
6893: WSH_DEBUG_SV.logmsg(l_module_name,'Updating the Weights of Delivery Details');
6894: END IF;
6895: --
6896: FORALL i IN 1..l_dd_upd_tbl.COUNT
6897: UPDATE wsh_delivery_details
6898: SET gross_weight = l_dd_upd_gross_wt_tbl(i),
6899: net_weight = l_dd_upd_net_wt_tbl(i),
6900: wv_frozen_flag = 'Y',
6901: last_update_date = SYSDATE,