[Home] [Help]
388: msi.weight_uom_code,
389: msi.volume_uom_code,
390: msi.unit_weight,
391: msi.unit_volume
392: FROM wsh_delivery_details wdd,
393: mtl_system_items msi
394: WHERE wdd.delivery_detail_id = v_delivery_detail_id
395: AND msi.inventory_item_id = wdd.inventory_item_id
396: AND msi.organization_id = wdd.organization_id;
412: --lpn conv
413: wdd.container_flag,
414: NVL(wdd.wv_frozen_flag,'Y'),
415: nvl(line_direction,'O') line_direction -- LPN CONV. rv
416: from wsh_delivery_details wdd
417: where wdd.delivery_detail_id = v_delivery_detail_id;
418:
419: l_debug_on BOOLEAN;
420: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DETAIL_WEIGHT_VOLUME';
615: --}
616: END IF;
617: -- K LPN CONV. rv
618: --
619: UPDATE WSH_DELIVERY_DETAILS
620: SET net_weight = x_net_weight,
621: volume = x_volume,
622: gross_weight = x_net_weight,
623: weight_uom_code = nvl(weight_uom_code,l_item_wt_code),
660: END IF;
661: -- end of bug 3711017
662:
663: -- OTM R12 : packing ECO
664: -- after update wsh_delivery_details sql
665: -- if any weight/vol changed, mark the global variable
666: IF (G_DELIVERY_TMS_IMPACT = 'N' AND
667: (NVL(l_net_weight, -1) <> NVL(x_net_weight, -1) OR
668: NVL(l_volume, -1) <> NVL(x_volume, -1) OR
1360: l_frozen_flag,
1361: -- MDC
1362: l_wda_type,
1363: l_line_direction
1364: FROM wsh_delivery_details wdd,
1365: wsh_delivery_assignments wda
1366: WHERE wdd.delivery_detail_id = p_entity_id
1367: AND wdd.delivery_detail_id = wda.delivery_detail_id
1368: AND NVL(wda.type, 'S') in ('S', 'C')
1662: wdd.line_direction,
1663: -- MDC
1664: NVL(wda.type, 'S')
1665: FROM wsh_delivery_assignments wda,
1666: wsh_delivery_details wdd
1667: WHERE wdd.delivery_detail_id = p_delivery_detail_id
1668: AND NVL(wda.type, 'S') in ('S', 'C')
1669: AND wdd.delivery_detail_id = wda.delivery_detail_id;
1670:
1686: gross_weight,
1687: net_weight,
1688: volume,
1689: filled_volume
1690: FROM wsh_delivery_details
1691: WHERE delivery_detail_id = c_container_id;
1692:
1693: CURSOR C5(c_delivery_id IN NUMBER) IS
1694: SELECT gross_weight,
1737: l_item_id NUMBER;
1738:
1739: cursor c_get_item_id (v_delivery_detail_id number) IS
1740: SELECT inventory_item_id
1741: FROM wsh_delivery_details
1742: WHERE delivery_detail_id = v_delivery_detail_id;
1743:
1744: e_abort exception;
1745:
2479: INTO l_org_gross_wt,
2480: l_org_net_wt,
2481: l_org_vol,
2482: l_frozen_flag
2483: FROM wsh_delivery_details
2484: WHERE delivery_detail_id = p_delivery_detail_id
2485: AND container_flag = 'N';
2486: EXCEPTION
2487: WHEN NO_DATA_FOUND THEN
2623: filled_volume,
2624: container_flag,
2625: nvl(line_direction, 'O') line_direction, -- LPN CONV. rv
2626: organization_id -- LPN CONV. rv
2627: FROM wsh_delivery_details
2628: WHERE delivery_detail_id = p_container_instance_id;
2629:
2630: l_net_weight NUMBER := 0;
2631: l_gross_weight NUMBER := 0;
2747: --}
2748: END IF;
2749: -- K LPN CONV. rv
2750:
2751: UPDATE WSH_DELIVERY_DETAILS
2752: SET net_weight = p_net_weight,
2753: gross_weight = p_gross_weight,
2754: volume = p_volume,
2755: filled_volume= p_filled_volume,
2814:
2815: END IF;
2816:
2817: -- OTM R12 : packing ECO
2818: -- after update wsh_delivery_details sql
2819: -- if any weight/vol changed, mark the global variable
2820: IF (G_DELIVERY_TMS_IMPACT = 'N' AND
2821: (NVL(l_net_weight, -1) <> NVL(p_net_weight, -1) OR
2822: NVL(l_gross_weight, -1) <> NVL(p_gross_weight, -1) OR
3023: nvl(wdd.received_quantity, nvl(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))),
3024: wdd.inventory_item_id
3025: ) qty
3026: FROM mtl_system_items msi,
3027: wsh_delivery_details wdd
3028: WHERE msi.inventory_item_id (+) = wdd.inventory_item_id
3029: AND msi.organization_id (+) = wdd.organization_id
3030: AND wdd.delivery_detail_id = p_deliveryDetailId;
3031: --
3304: WSH_DEBUG_SV.logmsg(l_module_name,'Update W/V for DD '||childInfo_rec.delivery_detail_id);
3305: END IF;
3306: -- LPN CONV. rv
3307: -- no call reqd. because this update is for non-container lines
3308: UPDATE wsh_delivery_details
3309: SET net_weight = l_line_grossWeight_orig,
3310: gross_weight = l_line_grossWeight_orig,
3311: volume = l_line_volume_orig,
3312: unit_weight = NVL(unit_weight,childInfo_rec.unit_weight),
3342: raise mark_reprice_error;
3343: END IF;
3344:
3345: -- OTM R12 : packing ECO
3346: -- after update wsh_delivery_details sql
3347: -- if any weight/vol changed, mark the global variable
3348:
3349: -- G_DELIVERY_TMS_IMPACT is set to Y for the first one
3350: -- meeting the condition in immediateChildren_rec cursor,
3740: l_org_vol,
3741: l_org_fill_pc,
3742: l_organization_id,
3743: l_frozen_flag
3744: FROM wsh_delivery_details
3745: WHERE delivery_detail_id = p_container_instance_id
3746: AND container_flag IN ('Y', 'C');
3747: EXCEPTION
3748: WHEN NO_DATA_FOUND THEN
3864: -- for entities whose W/V is manually entered
3865: -- OTM R12 : packing ECO
3866: -- This procedure is modified to keep track of delivery
3867: -- detail wt/vol updates. Called first by WSHDDGPB
3868: -- (WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action),
3869: -- so the logic of checking G_RESET_WV is here.
3870: -- Parameters :
3871: -- IN:
3872: -- p_detail_rows IN wsh_util_core.id_tab_type REQUIRED
3887: x_return_status OUT NOCOPY VARCHAR2) IS
3888:
3889: CURSOR c_container_flag(x_id NUMBER) IS
3890: SELECT container_flag
3891: FROM wsh_delivery_details
3892: WHERE delivery_detail_id = x_id;
3893:
3894: -- OTM R12 : packing ECO
3895: CURSOR c_get_delivery_info(p_detail_id IN NUMBER) IS
3915: l_gross_weight NUMBER;
3916: l_volume NUMBER;
3917: l_fill_pc NUMBER;
3918: l_num_error NUMBER := 0;
3919: l_flag WSH_DELIVERY_DETAILS.CONTAINER_FLAG%TYPE;
3920:
3921: others EXCEPTION;
3922:
3923: l_debug_on BOOLEAN;
4124: nvl(unit_weight,0),
4125: weight_uom_code, volume_uom_code, organization_id,
4126: maximum_load_weight, maximum_volume, fill_percent, container_flag, container_name,
4127: nvl(line_direction, 'O') line_direction, organization_id
4128: FROM wsh_delivery_details
4129: WHERE delivery_detail_id = v_cont_instance_id;
4130:
4131: CURSOR Get_Cont_Contents (v_cont_instance_id NUMBER, v_org_id NUMBER) IS
4132: SELECT wda.delivery_detail_id,container_flag,
4133: inventory_item_id,
4134: NVL(received_quantity, -- J-IB-NPARIKH
4135: nvl(shipped_quantity, NVL(picked_quantity, requested_quantity))) pack_qty
4136: FROM wsh_delivery_assignments_v wda,
4137: wsh_delivery_details wdd
4138: WHERE wda.parent_delivery_detail_id = v_cont_instance_id AND
4139: wdd.delivery_detail_id = wda.delivery_detail_id;
4140:
4141:
4522: END IF;
4523: --}
4524: END IF;
4525: -- K LPN CONV. rv
4526: UPDATE WSH_DELIVERY_DETAILS
4527: SET fill_percent = x_fill_percent
4528: WHERE delivery_detail_id = p_container_instance_id;
4529:
4530: IF SQL%NOTFOUND THEN
4600: AND organization_id = v_org_id;
4601:
4602: CURSOR Get_Cont_Info (v_cont_inst_id NUMBER) IS
4603: SELECT inventory_item_id, weight_uom_code, organization_id
4604: FROM wsh_delivery_details
4605: WHERE delivery_detail_id = v_cont_inst_id AND
4606: container_flag = 'Y';
4607:
4608: l_cont_item_id NUMBER;
4753:
4754: /* wms - change : to check for LPN grouping, added inventory_item_id */
4755: CURSOR Get_Cont_Info IS
4756: SELECT fill_percent, minimum_fill_percent, inventory_item_id, organization_id
4757: FROM WSH_DELIVERY_DETAILS
4758: WHERE delivery_detail_id = p_container_instance_id AND
4759: container_flag = 'Y';
4760:
4761: l_fill_pc NUMBER;
4963: wdd.volume vol,
4964: wdd.inventory_item_id,
4965: wdd.weight_uom_code wt_uom,
4966: wdd.volume_uom_code vol_uom
4967: FROM wsh_delivery_details wdd,
4968: wsh_delivery_assignments_v wda
4969: WHERE wdd.delivery_detail_id = wda.delivery_detail_id AND
4970: wda.delivery_id = p_delivery_id AND
4971: wda.delivery_id IS NOT NULL AND
4980: dd.inventory_item_id inventory_item_id,
4981: dd.weight_uom_code wt_uom,
4982: dd.volume_uom_code vol_uom
4983: FROM wsh_delivery_assignments_v da,
4984: wsh_delivery_details dd
4985: WHERE da.delivery_id = p_delivery_id AND
4986: da.delivery_id IS NOT NULL AND
4987: da.delivery_detail_id = dd.delivery_detail_id AND
4988: dd.container_flag = 'Y' AND
5023: dd.volume vol,
5024: dd.inventory_item_id inventory_item_id,
5025: dd.weight_uom_code wt_uom,
5026: dd.volume_uom_code vol_uom
5027: from wsh_delivery_details dd, wsh_delivery_assignments da
5028: where dd.delivery_detail_id = da.delivery_detail_id
5029: and dd.container_flag = 'C'
5030: and NVL(da.type, 'S') = 'S'
5031: and da.delivery_id = p_delivery_id
5043: dd.inventory_item_id inventory_item_id,
5044: dd.weight_uom_code wt_uom,
5045: dd.volume_uom_code vol_uom
5046: FROM wsh_delivery_assignments da,
5047: wsh_delivery_details dd
5048: WHERE da.parent_delivery_detail_id = p_consol_lpn AND
5049: da.delivery_detail_id = dd.delivery_detail_id AND
5050: da.type = 'C';
5051:
6473: wdd.container_flag,
6474: nvl(wdd.line_direction, 'O') line_direction, -- LPN CONV. rv
6475: wdd.organization_id organization_id -- LPN CONV. rv
6476: FROM wsh_delivery_assignments_v wda,
6477: wsh_delivery_details wdd
6478: WHERE wda.delivery_id = p_delivery_id
6479: AND wda.parent_delivery_detail_id IS NULL
6480: AND wda.delivery_detail_id = wdd.delivery_detail_id
6481: AND nvl(wdd.gross_weight,0) > 0
6494: wdd.container_flag,
6495: nvl(wdd.line_direction, 'O') line_direction, -- LPN CONV. rv
6496: wdd.organization_id organization_id -- LPN CONV. rv
6497: FROM wsh_delivery_assignments_v wda,
6498: wsh_delivery_details wdd
6499: WHERE wda.parent_delivery_detail_id IS NOT NULL
6500: AND wda.parent_delivery_detail_id = p_container_id
6501: AND wda.delivery_detail_id = wdd.delivery_detail_id
6502: AND nvl(wdd.gross_weight,0) > 0
6925: WSH_DEBUG_SV.logmsg(l_module_name,'Updating the Weights of Delivery Details');
6926: END IF;
6927: --
6928: FORALL i IN 1..l_dd_upd_tbl.COUNT
6929: UPDATE wsh_delivery_details
6930: SET gross_weight = l_dd_upd_gross_wt_tbl(i),
6931: net_weight = l_dd_upd_net_wt_tbl(i),
6932: wv_frozen_flag = 'Y',
6933: last_update_date = SYSDATE,