[Home] [Help]
1: PACKAGE BODY WMS_LPN_LOVS AS
2: /* $Header: WMSLPNLB.pls 120.7.12010000.2 2008/08/05 22:56:22 ammathew ship $ */
3:
4: -- Name: GET_LPN_LOV
5: --
25: l_msg := msg;
26:
27: inv_mobile_helper_functions.tracelog
28: (p_err_msg => l_msg,
29: p_module => 'WMS_LPN_LOVS',
30: p_level => 4);
31:
32: --dbms_output.put_line(l_msg);
33: END;
238: ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ';
239: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
240: BEGIN
241: IF (l_debug = 1) THEN
242: inv_log_util.trace('In Get_WHERE_PJM_LPN_LOV', 'WMS_LPN_LOVs',1);
243: END IF;
244: l_sql_stmt := l_sql_stmt ||
245: 'AND wlpn.license_plate_number LIKE :p_lpn ' ||
246: p_where_clause;
1474: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1475: NVL(wlpn.parent_lpn_id, 0))
1476: AND wlpn.license_plate_number LIKE (p_lpn)
1477: /* Bug 3980914 -For LPN's with context 5, the following condition is not required
1478: AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
1479: AND (wlpn.lpn_context = 5)
1480: UNION ALL
1481: SELECT wlpn.license_plate_number,
1482: wlpn.lpn_id,
1499: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1500: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1501: NVL(wlpn.parent_lpn_id, 0))
1502: AND wlpn.license_plate_number LIKE (p_lpn)
1503: AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y'
1504: AND wlpn.lpn_context = 1
1505: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1506: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1507: AND ( ( p_mtrl_sts_check = 'Y' -- Bug 3980914
1544: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1545: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1546: NVL(wlpn.parent_lpn_id, 0))
1547: AND wlpn.license_plate_number LIKE (p_lpn)
1548: AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y')
1549: AND wlpn.lpn_context = 1
1550: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1551: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1552: AND ( ( p_mtrl_sts_check = 'Y' -- Bug 3980914
1615: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1616: NVL(wlpn.parent_lpn_id, 0))
1617: AND wlpn.license_plate_number LIKE (p_lpn)
1618: /* Bug 3980914 - For LPN's with context 5, no check for subinventory required.
1619: AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
1620: AND (wlpn.lpn_context = 5)
1621: UNION ALL
1622: SELECT wlpn.license_plate_number,
1623: wlpn.lpn_id,
1641: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1642: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1643: NVL(wlpn.parent_lpn_id, 0))
1644: AND wlpn.license_plate_number LIKE (p_lpn)
1645: AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y')
1646: AND wlpn.lpn_context = p_context
1647: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1648: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1649: AND mil.inventory_location_id = wlpn.locator_id
1706: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1707: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1708: NVL(wlpn.parent_lpn_id, 0))
1709: AND wlpn.license_plate_number LIKE (p_lpn)
1710: AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y')
1711: AND wlpn.lpn_context = p_context
1712: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
1713: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1714: AND mil.inventory_location_id = wlpn.locator_id
1920: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1921: NVL(wlpn.parent_lpn_id, 0))
1922: AND wlpn.license_plate_number LIKE (p_lpn)
1923: /* Bug 3980914 -For LPN's with context 5, the following condition is not required
1924: AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
1925: AND (wlpn.lpn_context = 5)
1926: UNION ALL
1927: SELECT wlpn.license_plate_number,
1928: wlpn.lpn_id,
1945: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1946: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1947: NVL(wlpn.parent_lpn_id, 0))
1948: AND wlpn.license_plate_number LIKE (p_lpn)
1949: AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
1950: AND wlpn.lpn_context = 1
1951: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
1952: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
1953: AND (( p_mtrl_sts_check = 'Y' --Bug 3980914
1998: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1999: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2000: NVL(wlpn.parent_lpn_id, 0))
2001: AND wlpn.license_plate_number LIKE (p_lpn)
2002: AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2003: AND wlpn.lpn_context = 1
2004: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
2005: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2006: AND ( ( p_mtrl_sts_check = 'Y' --Bug 3980914
2074: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2075: NVL(wlpn.parent_lpn_id, 0))
2076: AND wlpn.license_plate_number LIKE (p_lpn)
2077: /* Bug 3980914 -For LPN's with context 5, the following condition is not required
2078: AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id ) = 'Y' */
2079: AND (wlpn.lpn_context = 5)
2080: UNION ALL
2081: SELECT wlpn.license_plate_number,
2082: wlpn.lpn_id,
2100: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2101: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2102: NVL(wlpn.parent_lpn_id, 0))
2103: AND wlpn.license_plate_number LIKE (p_lpn)
2104: AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2105: AND wlpn.lpn_context = p_context
2106: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
2107: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2108: AND mil.inventory_location_id = wlpn.locator_id
2157: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2158: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2159: NVL(wlpn.parent_lpn_id, 0))
2160: AND wlpn.license_plate_number LIKE (p_lpn)
2161: AND (wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_org_id) ='Y')
2162: AND wlpn.lpn_context = p_context
2163: AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
2164: AND NVL(wlpn.locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(wlpn.locator_id, '0'))
2165: AND mil.inventory_location_id = wlpn.locator_id
4404: wsh_delivery_details wdd
4405: WHERE wlpn.organization_id = p_organization_id
4406: AND wlpn.lpn_context = 11
4407: AND wlpn.license_plate_number LIKE (p_lpn_id)
4408: AND wms_lpn_lovs.SUB_LPN_CONTROLLED(wlpn.subinventory_code, p_organization_id) ='Y'
4409: AND inv_material_status_grp.is_status_applicable (
4410: 'TRUE', NULL, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT, NULL,
4411: NULL, p_organization_id, NULL, wlpn.subinventory_code,
4412: wlpn.locator_id, NULL, NULL, 'Z') = 'Y'
4554: AND wlpn.license_plate_number LIKE p_lpn_name
4555: ORDER BY wlpn.license_plate_number;
4556: END get_from_gtmp_lov;
4557:
4558: END WMS_LPN_LOVS;