[Home] [Help]
1: PACKAGE BODY inv_ui_item_sub_loc_lovs AS
2: /* $Header: INVITPSB.pls 120.20 2008/05/08 10:54:44 aambulka ship $ */
3:
4: g_pkg_name CONSTANT VARCHAR2(30) := 'INV_UI_ITEM_SUB_LOC_LOVS';
5:
1: PACKAGE BODY inv_ui_item_sub_loc_lovs AS
2: /* $Header: INVITPSB.pls 120.20 2008/05/08 10:54:44 aambulka ship $ */
3:
4: g_pkg_name CONSTANT VARCHAR2(30) := 'INV_UI_ITEM_SUB_LOC_LOVS';
5:
6: PROCEDURE debug(p_msg VARCHAR2) IS
7:
8: BEGIN
1690: BEGIN
1691: l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1692: OPEN x_zones FOR
1693: SELECT secondary_inventory_name
1694: , inv_ui_item_sub_loc_lovs.check_loc_existence(p_organization_id, secondary_inventory_name)
1695: , msi.description
1696: , asset_inventory
1697: , mmsv.status_id
1698: , status_code
2801: , enable_locator_alias
2802: FROM mtl_secondary_inventories
2803: WHERE organization_id = l_org
2804: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2805: AND inv_ui_item_sub_loc_lovs.vaildate_to_lpn_sub(p_lpn_id, secondary_inventory_name, l_org, p_from_sub_asset_inventory, p_wms_installed, p_transaction_type_id) = 'Y'
2806: AND secondary_inventory_name LIKE (p_secondary_inventory_name);
2807: ELSE
2808: OPEN x_to_sub FOR
2809: SELECT secondary_inventory_name
2814: , enable_locator_alias
2815: FROM mtl_secondary_inventories
2816: WHERE organization_id = l_org
2817: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2818: AND inv_ui_item_sub_loc_lovs.vaildate_to_lpn_sub(p_lpn_id, secondary_inventory_name, l_org, p_from_sub_asset_inventory, p_wms_installed, p_transaction_type_id) = 'Y'
2819: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
2820: AND reservable_type = 1
2821: AND lpn_controlled_flag = 1;
2822: END IF;
3038: FROM wms_item_locations_kfv
3039: WHERE organization_id = p_organization_id
3040: AND subinventory_code = p_subinventory_code
3041: AND concatenated_segments LIKE (p_concatenated_segments)
3042: AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
3043: END get_lpnloc_lov;
3044:
3045: FUNCTION validate_sub_loc_status(p_lpn IN VARCHAR2, p_org_id IN NUMBER, p_sub IN VARCHAR2, p_loc_id IN NUMBER, p_not_lpn_id IN VARCHAR2 := NULL, p_parent_lpn_id IN VARCHAR2 := '0', p_txn_type_id IN NUMBER)
3046: RETURN VARCHAR2 IS
4107: AND subinventory_code = p_subinventory_code
4108: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4109: AND concatenated_segments LIKE (p_concatenated_segments)
4110: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4111: AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
4112: END get_prj_lpnloc_lov;
4113: PROCEDURE get_prj_lpnloc_lov(
4114: x_locators OUT NOCOPY t_genref
4115: , p_organization_id IN NUMBER
4150: AND subinventory_code = p_subinventory_code
4151: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4152: AND alias = p_alias
4153: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4154: AND inv_ui_item_sub_loc_lovs.vaildate_lpn_toloc(p_lpn_id, p_subinventory_code, p_organization_id, inventory_location_id, p_wms_installed, p_transaction_type_id) = 'Y';
4155: END get_prj_lpnloc_lov;
4156:
4157:
4158: -- This procedure is used for user directed putaway
4189: FROM mtl_secondary_inventories
4190: WHERE organization_id = p_organization_id
4191: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4192: AND secondary_inventory_name LIKE (p_subinventory_code)
4193: AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4194: secondary_inventory_name,
4195: p_lpn_id) = 'Y'
4196: UNION ALL
4197: SELECT msub.secondary_inventory_name
4242: WHERE organization_id = p_organization_id
4243: AND Nvl(subinventory_type,1) = 1
4244: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4245: AND secondary_inventory_name LIKE (p_subinventory_code)
4246: AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4247: secondary_inventory_name,
4248: p_lpn_id) = 'Y';
4249: ELSIF (p_rcv_sub_only = 4) THEN
4250: -- SO XDOCK
4264: AND lpn_controlled_flag = 1
4265: AND reservable_type = 1
4266: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4267: AND secondary_inventory_name LIKE (p_subinventory_code)
4268: AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4269: secondary_inventory_name,
4270: p_lpn_id) = 'Y';
4271: ELSIF (p_rcv_sub_only = 5) THEN
4272: -- Only include INV Subs that are non-reservable and non-LPN-Controlled
4285: AND lpn_controlled_flag = 2
4286: AND reservable_type = 2
4287: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4288: AND secondary_inventory_name LIKE (p_subinventory_code)
4289: AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4290: secondary_inventory_name,
4291: p_lpn_id) = 'Y';
4292:
4293: ELSIF (p_rcv_sub_only = 6) THEN
4307: AND lpn_controlled_flag = 1
4308: AND reservable_type = 1
4309: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4310: AND secondary_inventory_name LIKE (p_subinventory_code)
4311: AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4312: secondary_inventory_name,
4313: p_lpn_id) = 'Y'
4314: UNION ALL
4315: SELECT msub.secondary_inventory_name
4344: AND lpn_controlled_flag = 2
4345: AND reservable_type = 2
4346: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4347: AND secondary_inventory_name LIKE (p_subinventory_code)
4348: AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4349: secondary_inventory_name,
4350: p_lpn_id) = 'Y'
4351: UNION ALL
4352: SELECT msub.secondary_inventory_name
4379: FROM mtl_secondary_inventories
4380: WHERE organization_id = p_organization_id
4381: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4382: AND secondary_inventory_name LIKE (p_subinventory_code)
4383: AND inv_ui_item_sub_loc_lovs.validate_lpn_sub(p_organization_id,
4384: secondary_inventory_name,
4385: p_lpn_id) = 'Y'
4386: ORDER BY UPPER(secondary_inventory_name);
4387: END IF;
4391:
4392: -- This function will validate the LPN for item/sub restrictions
4393: -- and also for sub material status for each move order line transaction.
4394: -- This function should only be called from the procedure
4395: -- get_userput_subs in this package: INV_UI_ITEM_SUB_LOC_LOVS
4396: FUNCTION validate_lpn_sub(
4397: p_organization_id IN NUMBER
4398: , p_subinventory_code IN VARCHAR2
4399: , p_lpn_id IN NUMBER)
4530: AND subinventory_code = p_subinventory_code
4531: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4532: AND alias = p_alias
4533: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4534: AND inv_ui_item_sub_loc_lovs.validate_lpn_loc(p_organization_id,
4535: p_subinventory_code,
4536: inventory_location_id,
4537: p_lpn_id) = 'Y'
4538: ORDER BY 2;
4560: AND subinventory_code = p_subinventory_code
4561: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4562: AND concatenated_segments LIKE (p_concatenated_segments)
4563: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4564: AND inv_ui_item_sub_loc_lovs.validate_lpn_loc(p_organization_id,
4565: p_subinventory_code,
4566: inventory_location_id,
4567: p_lpn_id) = 'Y'
4568: ORDER BY 2;
4570:
4571: -- This function will validate the LPN for item/sub/loc restrictions
4572: -- and also for locator material status for each move order line transaction.
4573: -- This function should only be called from the procedure
4574: -- get_userput_locs in this package: INV_UI_ITEM_SUB_LOC_LOVS
4575: FUNCTION validate_lpn_loc(p_organization_id IN NUMBER ,
4576: p_subinventory_code IN VARCHAR2 ,
4577: p_locator_id IN NUMBER ,
4578: p_lpn_id IN NUMBER)
6198: , x_return_status OUT NOCOPY VARCHAR2 ) IS
6199: BEGIN
6200: x_loc_exists := 'Y';
6201: BEGIN
6202: DEBUG('INV_UI_ITEM_SUB_LOC_LOVS.GET_PICKLOAD_LOC_DETAILS');
6203: DEBUG('p_inventory_location_id -> '||p_inventory_location_id);
6204: DEBUG('p_organization_id -> '||p_organization_id);
6205: SELECT subinventory_code
6206: , CONCATENATED_SEGMENTS
6411: x_msg_data := SQLERRM;
6412: x_msg_count := 1;
6413: END get_value_from_alias;
6414:
6415: END inv_ui_item_sub_loc_lovs;