[Home] [Help]
188: 2,--not be reservable
189: reservable_type))
190: AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
191: AND msub.secondary_inventory_name LIKE (p_sub)
192: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
193: UNION ALL
194: SELECT msub.secondary_inventory_name
195: , NVL(msub.locator_type, 1)
196: , msub.description
250: FROM mtl_item_sub_inventories mis
251: WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
252: AND mis.inventory_item_id = p_item_id
253: AND mis.secondary_inventory = msub.secondary_inventory_name)
254: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
255: UNION ALL
256: SELECT msub.secondary_inventory_name
257: , NVL(msub.locator_type, 1)
258: , msub.description
316: msub.reservable_type)
317: ),
318: msub.reservable_type
319: )
320: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
321: ORDER BY UPPER(msub.secondary_inventory_name);
322: ELSE
323: -- It is a restricted item,
324: OPEN x_sub FOR
362: FROM mtl_item_sub_inventories mis
363: WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
364: AND mis.inventory_item_id = p_item_id
365: AND mis.secondary_inventory = msub.secondary_inventory_name)
366: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msub.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
367: ORDER BY UPPER(msub.secondary_inventory_name);
368: END IF;
369: ELSIF p_location_id IS NOT NULL THEN
370: -- For Putaway, p_location_id will always be null, so no change
501: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
502: AND b.subinventory_code = p_subinventory_code
503: AND a.inventory_location_id = b.secondary_locator
504: AND a.concatenated_segments LIKE (p_concatenated_segments)
505: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
506: ORDER BY a.concatenated_segments;
507: ELSE --Locators not restricted
508: OPEN x_locators FOR
509: SELECT inventory_location_id
514: WHERE organization_id = p_organization_id
515: AND subinventory_code = p_subinventory_code
516: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
517: AND concatenated_segments LIKE (p_concatenated_segments)
518: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
519: ORDER BY concatenated_segments;
520: END IF;
521: END get_loc_lov;
522:
570: AND a.project_id IS NULL
571: AND a.task_id IS NULL
572: AND a.concatenated_segments LIKE (p_concatenated_segments)
573: AND NVL(a.physical_location_id, a.inventory_location_id) = a.inventory_location_id
574: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
575: ORDER BY 2;
576: ELSE --Locators not restricted
577: OPEN x_locators FOR
578: SELECT inventory_location_id
585: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
586: AND concatenated_segments LIKE (p_concatenated_segments)
587: AND project_id IS NULL
588: AND task_id IS NULL
589: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
590: ORDER BY 2;
591: END IF;
592: END get_loc_lov_pjm;
593:
1259: AND b.inventory_item_id = p_inventory_item_id
1260: AND a.secondary_inventory_name = b.secondary_inventory
1261: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1262: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1263: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1264: ELSE
1265: OPEN x_zones FOR
1266: SELECT secondary_inventory_name
1267: , NVL(locator_type, 1)
1272: FROM mtl_secondary_inventories
1273: WHERE organization_id = p_organization_id
1274: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1275: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1276: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1277: END IF;
1278: ELSE
1279: IF p_restrict_subinventories_code = 1 THEN
1280: OPEN x_zones FOR
1290: AND a.secondary_inventory_name = b.secondary_inventory
1291: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1292: AND b.inventory_item_id = p_inventory_item_id
1293: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1294: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1295: ELSE
1296: OPEN x_zones FOR
1297: SELECT secondary_inventory_name
1298: , NVL(locator_type, 1)
1303: FROM mtl_secondary_inventories
1304: WHERE organization_id = p_organization_id
1305: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1306: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1307: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1308: END IF;
1309: END IF;
1310: ELSE
1311: IF (p_transaction_action_id <> 2
1325: AND a.secondary_inventory_name = b.secondary_inventory
1326: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1327: AND b.inventory_item_id = p_inventory_item_id
1328: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1329: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1330: ELSE
1331: OPEN x_zones FOR
1332: SELECT secondary_inventory_name
1333: , NVL(locator_type, 1)
1338: FROM mtl_secondary_inventories
1339: WHERE organization_id = p_organization_id
1340: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1341: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1342: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1343: END IF;
1344: ELSE
1345: IF p_restrict_subinventories_code = 1 THEN
1346: OPEN x_zones FOR
1356: AND a.secondary_inventory_name = b.secondary_inventory
1357: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1358: AND b.inventory_item_id = p_inventory_item_id
1359: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1360: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1361: ELSE
1362: OPEN x_zones FOR
1363: SELECT secondary_inventory_name
1364: , NVL(locator_type, 1)
1369: FROM mtl_secondary_inventories
1370: WHERE organization_id = p_organization_id
1371: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1372: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1373: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1374: END IF;
1375: END IF;
1376: END IF;
1377: END get_from_subs;
1441: AND a.secondary_inventory_name = b.secondary_inventory
1442: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1443: AND b.inventory_item_id = p_inventory_item_id
1444: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1445: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1446: ELSE
1447: OPEN x_to_sub FOR
1448: SELECT secondary_inventory_name
1449: , NVL(locator_type, 1)
1454: FROM mtl_secondary_inventories
1455: WHERE organization_id = l_org
1456: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1457: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1458: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1459: END IF;
1460: ELSE
1461: IF l_restrict_subinventories_code = 1 THEN
1462: IF l_inventory_asset_flag = 'Y' THEN
1475: AND b.inventory_item_id = p_inventory_item_id
1476: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1477: AND a.secondary_inventory_name = b.secondary_inventory
1478: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1479: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1480: ELSE
1481: OPEN x_to_sub FOR
1482: SELECT a.secondary_inventory_name
1483: , NVL(a.locator_type, 1)
1492: AND b.inventory_item_id = p_inventory_item_id
1493: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1494: AND a.secondary_inventory_name = b.secondary_inventory
1495: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1496: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1497: END IF;
1498: ELSE
1499: OPEN x_to_sub FOR
1500: SELECT a.secondary_inventory_name
1509: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1510: AND a.secondary_inventory_name = b.secondary_inventory
1511: AND b.inventory_item_id = p_inventory_item_id
1512: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1513: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, a.secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1514: END IF;
1515: ELSE
1516: IF l_inventory_asset_flag = 'Y' THEN
1517: IF l_from_sub_asset_inventory = 1 THEN
1526: WHERE organization_id = l_org
1527: --and asset_inventory = 1
1528: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1529: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1530: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1531: ELSE
1532: OPEN x_to_sub FOR
1533: SELECT secondary_inventory_name
1534: , NVL(locator_type, 1)
1540: WHERE organization_id = l_org
1541: AND asset_inventory = 2
1542: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1543: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1544: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1545: END IF;
1546: ELSE
1547: OPEN x_to_sub FOR
1548: SELECT secondary_inventory_name
1554: FROM mtl_secondary_inventories
1555: WHERE organization_id = l_org
1556: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1557: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1558: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_to_organization_id, p_inventory_item_id, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y';
1559: END IF;
1560: END IF;
1561: END IF;
1562: END get_to_sub;
1623: FROM mtl_secondary_inventories
1624: WHERE organization_id = NVL(p_organization_id, organization_id)
1625: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1626: AND secondary_inventory_name LIKE (p_subinventory_code)
1627: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
1628: ORDER BY secondary_inventory_name;
1629: ELSE -- Organization_id is not null
1630: OPEN x_zones FOR
1631: SELECT secondary_inventory_name
1637: FROM mtl_secondary_inventories
1638: WHERE organization_id = p_organization_id
1639: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1640: AND secondary_inventory_name LIKE (p_subinventory_code)
1641: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
1642: ORDER BY secondary_inventory_name;
1643: END IF;
1644: END get_valid_subinvs;
1645:
2068: AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2069: AND msub.secondary_inventory_name LIKE (p_subinventory_code)
2070: -- Bug# 2770853
2071: -- Check for material status at the subinventory level
2072: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2073: NULL,
2074: 4,
2075: NULL,
2076: NULL,
2099: AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2100: AND msub.secondary_inventory_name LIKE (p_subinventory_code)
2101: -- Bug# 2770853
2102: -- Check for material status at the subinventory level
2103: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2104: NULL,
2105: 4,
2106: NULL,
2107: NULL,
2211: AND mcce.entry_status_code IN (1,3)
2212: AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2213: -- Bug# 2770853
2214: -- Check for material status at the locator level
2215: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2216: NULL,
2217: 4,
2218: NULL,
2219: NULL,
2244: AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2245: AND project_id = p_project_id
2246: AND task_id = p_task_id
2247: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2248: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2249: NULL,
2250: 4,
2251: NULL,
2252: NULL,
2271: AND subinventory_code = p_subinventory_code
2272: AND concatenated_segments LIKE (p_concatenated_segments )--inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2273: AND project_id = p_project_id
2274: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2275: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2276: NULL,
2277: 4,
2278: NULL,
2279: NULL,
2298: WHERE organization_id = p_organization_id
2299: AND subinventory_code = p_subinventory_code
2300: AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2301: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2302: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2303: NULL,
2304: 4,
2305: NULL,
2306: NULL,
2399: AND mcce.entry_status_code IN (1,3)
2400: AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2401: -- Bug# 2770853
2402: -- Check for material status at the locator level
2403: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2404: NULL,
2405: 4,
2406: NULL,
2407: NULL,
2432: AND alias = p_alias
2433: AND project_id = p_project_id
2434: AND task_id = p_task_id
2435: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2436: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2437: NULL,
2438: 4,
2439: NULL,
2440: NULL,
2460: AND alias = p_alias
2461: --inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2462: AND project_id = p_project_id
2463: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2464: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2465: NULL,
2466: 4,
2467: NULL,
2468: NULL,
2488: AND subinventory_code = p_subinventory_code
2489: AND alias = p_alias
2490: -- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2491: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2492: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2493: NULL,
2494: 4,
2495: NULL,
2496: NULL,
2860: FETCH l_item_cursor INTO l_item_id;
2861: EXIT WHEN l_item_cursor%NOTFOUND;
2862: l_lpn_content := 1;
2863:
2864: IF inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_orgid, l_item_id, p_to_subinventory, NULL, NULL, NULL, 'Z') = 'Y' THEN
2865: SELECT restrict_subinventories_code
2866: , inventory_asset_flag
2867: INTO l_restrict_subinventories_code
2868: , l_inventory_asset_flag
2977: WHERE outermost_lpn_id = p_lpn_id)
2978: AND inventory_item_id IS NOT NULL;
2979: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2980: BEGIN
2981: IF inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_orgid, l_item_id, p_to_subinventory, p_locator_id, NULL, NULL, 'L') = 'Y' THEN
2982: OPEN l_item_cursor;
2983: l_lpn_content := 0;
2984:
2985: LOOP
3083: RETURN x_return;
3084: END IF;
3085:
3086: IF p_txn_type_id IN (500, 502) THEN
3087: pack_sub_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'Z');
3088: pack_loc_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'L');
3089:
3090: IF pack_sub_check = 'N'
3091: OR pack_loc_check = 'N' THEN
3084: END IF;
3085:
3086: IF p_txn_type_id IN (500, 502) THEN
3087: pack_sub_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'Z');
3088: pack_loc_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'L');
3089:
3090: IF pack_sub_check = 'N'
3091: OR pack_loc_check = 'N' THEN
3092: x_return := 'N';
3092: x_return := 'N';
3093: RETURN x_return;
3094: END IF;
3095: ELSIF p_txn_type_id IN (501, 502) THEN
3096: unpack_sub_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'Z');
3097: unpack_loc_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'L');
3098:
3099: IF unpack_sub_check = 'N'
3100: OR unpack_loc_check = 'N' THEN
3093: RETURN x_return;
3094: END IF;
3095: ELSIF p_txn_type_id IN (501, 502) THEN
3096: unpack_sub_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'Z');
3097: unpack_loc_check := inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_org_id, NULL, p_sub, p_loc_id, NULL, NULL, 'L');
3098:
3099: IF unpack_sub_check = 'N'
3100: OR unpack_loc_check = 'N' THEN
3101: x_return := 'N';
3153: LOOP
3154: FETCH l_cursor INTO l_record;
3155: EXIT WHEN l_cursor%NOTFOUND;
3156:
3157: IF inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_orgid, l_record.inventory_item_id, l_record.subinventory_code, l_record.locator_id, l_record.lot_number, l_record.serial_number, 'A') =
3158: 'Y' THEN
3159: x_return := 'Y';
3160: ELSE
3161: x_return := 'N';
3206: , msi.enable_locator_alias
3207: FROM mtl_secondary_inventories msi, MTL_ONHAND_QUANTITIES_DETAIL moq
3208: WHERE msi.secondary_inventory_name = moq.subinventory_code
3209: AND msi.organization_id = moq.organization_id
3210: AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, moq.subinventory_code, NULL, NULL, NULL, 'Z') = 'Y'
3211: AND moq.containerized_flag = 2
3212: AND moq.subinventory_code LIKE (p_subinventory_code)
3213: AND (moq.revision = p_revision
3214: OR (moq.revision IS NULL
3258: AND mil.organization_id = p_organization_id
3259: -- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
3260: AND mil.project_id IS NULL
3261: AND mil.task_id IS NULL
3262: AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, moq.locator_id, NULL, NULL, 'L') = 'Y'
3263: AND moq.containerized_flag = 2
3264: AND (moq.revision = p_revision
3265: OR (moq.revision IS NULL
3266: AND p_revision IS NULL
3307: AND mil.organization_id = p_organization_id
3308: -- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
3309: AND mil.project_id IS NULL
3310: AND mil.task_id IS NULL
3311: AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, moq.locator_id, NULL, NULL, 'L') = 'Y'
3312: AND moq.containerized_flag = 2
3313: AND (moq.revision = p_revision
3314: OR (moq.revision IS NULL
3315: AND p_revision IS NULL
3597: WHERE organization_id = NVL(p_organization_id, organization_id)
3598: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3599: AND secondary_inventory_name LIKE (p_subinventory_code)
3600: AND lpn_controlled_flag = 1
3601: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
3602: ORDER BY secondary_inventory_name;
3603: ELSE -- Organization_id is not null
3604: OPEN x_zones FOR
3605: SELECT secondary_inventory_name
3612: WHERE organization_id = p_organization_id
3613: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3614: AND secondary_inventory_name LIKE (p_subinventory_code)
3615: AND lpn_controlled_flag = 1
3616: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, secondary_inventory_name, NULL, NULL, NULL, 'Z') = 'Y'
3617: ORDER BY secondary_inventory_name;
3618: END IF;
3619:
3620: END get_valid_lpn_controlled_subs;
3679: AND b.subinventory_code = p_Subinventory_Code
3680: AND a.inventory_location_id = b.secondary_locator
3681: AND a.concatenated_segments LIKE (p_concatenated_segments)
3682: /* BUG#2810405: To show only common locators in the LOV */
3683: AND inv_material_status_grp.is_status_applicable
3684: ( p_wms_installed,
3685: NULL,
3686: p_transaction_type_id,
3687: NULL,
3706: AND subinventory_code = p_Subinventory_Code
3707: AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3708: AND concatenated_segments LIKE (p_concatenated_segments )
3709: /* BUG#2810405: To show only common locators in the LOV */
3710: AND inv_material_status_grp.is_status_applicable
3711: ( p_wms_installed,
3712: NULL,
3713: p_transaction_type_id,
3714: NULL,
3737: AND a.inventory_location_id = b.secondary_locator
3738: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
3739: AND a.concatenated_segments like (p_concatenated_segments )
3740: /* BUG#2810405: To show only common locators in the LOV */
3741: AND inv_material_status_grp.is_status_applicable
3742: ( p_wms_installed,
3743: NULL,
3744: p_transaction_type_id,
3745: NULL,
3765: AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3766: AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
3767: AND concatenated_segments LIKE (p_concatenated_segments )
3768: /* BUG#2810405: To show only common locators in the LOV */
3769: AND inv_material_status_grp.is_status_applicable
3770: ( p_wms_installed,
3771: NULL,
3772: p_transaction_type_id,
3773: NULL,
3863: AND a.inventory_location_id = b.secondary_locator
3864: -- AND a.concatenated_segments LIKE (p_concatenated_segments)
3865: AND a.alias = p_alias
3866: /* BUG#2810405: To show only common locators in the LOV */
3867: AND inv_material_status_grp.is_status_applicable
3868: ( p_wms_installed,
3869: NULL,
3870: p_transaction_type_id,
3871: NULL,
3891: AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
3892: -- AND concatenated_segments LIKE (p_concatenated_segments )
3893: AND alias = p_alias
3894: /* BUG#2810405: To show only common locators in the LOV */
3895: AND inv_material_status_grp.is_status_applicable
3896: ( p_wms_installed,
3897: NULL,
3898: p_transaction_type_id,
3899: NULL,
3923: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
3924: -- AND a.concatenated_segments like (p_concatenated_segments )
3925: AND a.alias = p_alias
3926: /* BUG#2810405: To show only common locators in the LOV */
3927: AND inv_material_status_grp.is_status_applicable
3928: ( p_wms_installed,
3929: NULL,
3930: p_transaction_type_id,
3931: NULL,
3952: AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
3953: -- AND concatenated_segments LIKE (p_concatenated_segments )
3954: AND alias = p_alias
3955: /* BUG#2810405: To show only common locators in the LOV */
3956: AND inv_material_status_grp.is_status_applicable
3957: ( p_wms_installed,
3958: NULL,
3959: p_transaction_type_id,
3960: NULL,
4467: FETCH l_item_txn_cursor INTO l_item_id, l_transaction_type;
4468: EXIT WHEN l_item_txn_cursor%NOTFOUND;
4469: -- Check if the sub's material status is valid
4470: -- for the current move order line's transaction type
4471: x_return := inv_material_status_grp.is_status_applicable(p_wms_installed => 'TRUE', p_trx_status_enabled => NULL, p_trx_type_id => l_transaction_type
4472: , p_lot_status_enabled => NULL, p_serial_status_enabled => NULL, p_organization_id => p_organization_id
4473: , p_inventory_item_id => l_item_id, p_sub_code => p_subinventory_code, p_locator_id => NULL, p_lot_number => NULL
4474: , p_serial_number => NULL, p_object_type => 'Z');
4475:
4657: EXIT WHEN l_item_txn_cursor%NOTFOUND;
4658:
4659: -- Check if the loc's material status is valid
4660: -- for the current move order line's transaction type
4661: x_return := inv_material_status_grp.is_status_applicable
4662: (p_wms_installed => 'TRUE',
4663: p_trx_status_enabled => NULL,
4664: p_trx_type_id => l_transaction_type,
4665: p_lot_status_enabled => NULL,
4750: AND b.subinventory_code = p_subinventory_code
4751: AND a.inventory_location_id = b.secondary_locator
4752: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
4753: AND a.alias = p_alias
4754: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4755: ORDER BY 2;
4756: ELSE --Locators not restricted
4757: OPEN x_locators FOR
4758: SELECT inventory_location_id
4764: AND subinventory_code = p_subinventory_code
4765: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4766: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4767: AND alias = p_alias
4768: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4769: ORDER BY 2;
4770: END IF;
4771: ELSE /*PJM org*/
4772: IF p_project_id IS NULL THEN
4783: AND b.subinventory_code = p_subinventory_code
4784: AND a.inventory_location_id = b.secondary_locator
4785: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
4786: AND a.alias = p_alias
4787: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4788: ORDER BY 2;
4789: ELSE --Locators not restricted
4790: OPEN x_locators FOR
4791: SELECT inventory_location_id
4797: AND subinventory_code = p_subinventory_code
4798: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4799: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4800: AND alias = p_alias
4801: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4802: ORDER BY 2;
4803: END IF;
4804: ELSE /*PJM org:Project id not null */
4805: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
4817: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4818: AND a.alias = p_alias
4819: AND a.project_id = p_project_id
4820: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
4821: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4822: ORDER BY 2;
4823: ELSE --Locators not restricted
4824: OPEN x_locators FOR
4825: SELECT inventory_location_id
4833: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4834: AND alias = p_alias
4835: AND project_id = p_project_id
4836: AND NVL(task_id, -1) = NVL(p_task_id, -1)
4837: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4838: ORDER BY 2;
4839: END IF;
4840: END IF;
4841: END IF;
4880: AND b.subinventory_code = p_subinventory_code
4881: AND a.inventory_location_id = b.secondary_locator
4882: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
4883: AND a.concatenated_segments LIKE (p_concatenated_segments)
4884: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4885: ORDER BY 2;
4886: ELSE --Locators not restricted
4887: OPEN x_locators FOR
4888: SELECT inventory_location_id
4894: AND subinventory_code = p_subinventory_code
4895: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4896: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4897: AND concatenated_segments LIKE (p_concatenated_segments)
4898: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4899: ORDER BY 2;
4900: END IF;
4901: ELSE /*PJM org*/
4902: IF p_project_id IS NULL THEN
4913: AND b.subinventory_code = p_subinventory_code
4914: AND a.inventory_location_id = b.secondary_locator
4915: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
4916: AND a.concatenated_segments LIKE (p_concatenated_segments)
4917: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4918: ORDER BY 2;
4919: ELSE --Locators not restricted
4920: OPEN x_locators FOR
4921: SELECT inventory_location_id
4927: AND subinventory_code = p_subinventory_code
4928: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4929: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4930: AND concatenated_segments LIKE (p_concatenated_segments)
4931: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4932: ORDER BY 2;
4933: END IF;
4934: ELSE /*PJM org:Project id not null */
4935: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
4947: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4948: AND a.concatenated_segments LIKE (p_concatenated_segments)
4949: AND a.project_id = p_project_id
4950: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
4951: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
4952: ORDER BY 2;
4953: ELSE --Locators not restricted
4954: OPEN x_locators FOR
4955: SELECT inventory_location_id
4963: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
4964: AND concatenated_segments LIKE (p_concatenated_segments)
4965: AND project_id = p_project_id
4966: AND NVL(task_id, -1) = NVL(p_task_id, -1)
4967: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
4968: ORDER BY 2;
4969: END IF;
4970: END IF;
4971: END IF;
5012: AND b.subinventory_code = p_subinventory_code
5013: AND a.inventory_location_id = b.secondary_locator
5014: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5015: AND a.concatenated_segments LIKE (p_concatenated_segments)
5016: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5017: ORDER BY 2;
5018: ELSE --Locators not restricted
5019: OPEN x_locators FOR
5020: SELECT inventory_location_id
5026: AND subinventory_code = p_subinventory_code
5027: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5028: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5029: AND concatenated_segments LIKE (p_concatenated_segments)
5030: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5031: ORDER BY 2;
5032: END IF;
5033: ELSE /*PJM org*/
5034: IF p_project_id IS NULL THEN
5046: AND a.inventory_location_id = b.secondary_locator
5047: -- AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5048: AND a.project_id is null
5049: AND a.concatenated_segments LIKE (p_concatenated_segments)
5050: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5051: ORDER BY 2;
5052: ELSE --Locators not restricted
5053: OPEN x_locators FOR
5054: SELECT inventory_location_id
5061: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5062: -- AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5063: AND project_id is null
5064: AND concatenated_segments LIKE (p_concatenated_segments)
5065: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5066: ORDER BY 2;
5067: END IF;
5068: ELSE /*PJM org:Project id not null */
5069: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5081: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5082: AND a.concatenated_segments LIKE (p_concatenated_segments)
5083: AND a.project_id = p_project_id
5084: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5085: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5086: ORDER BY 2;
5087: ELSE --Locators not restricted
5088: OPEN x_locators FOR
5089: SELECT inventory_location_id
5097: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5098: AND concatenated_segments LIKE (p_concatenated_segments)
5099: AND project_id = p_project_id
5100: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5101: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5102: ORDER BY 2;
5103: END IF;
5104: END IF;
5105: END IF;
5155: AND b.subinventory_code = p_subinventory_code
5156: AND a.inventory_location_id = b.secondary_locator
5157: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5158: AND a.concatenated_segments LIKE (p_concatenated_segments)
5159: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5160: ORDER BY 2;
5161: ELSE --Locators not restricted
5162: OPEN x_locators FOR
5163: SELECT inventory_location_id
5170: AND subinventory_code = p_subinventory_code
5171: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5172: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5173: AND concatenated_segments LIKE (p_concatenated_segments)
5174: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5175: ORDER BY 2;
5176: END IF;
5177: ELSE /*PJM org*/
5178: IF p_project_id IS NULL THEN
5191: AND a.inventory_location_id = b.secondary_locator
5192: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5193: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5194: AND a.concatenated_segments LIKE (p_concatenated_segments)
5195: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5196: ORDER BY 2;
5197: ELSE --Locators not restricted
5198: OPEN x_locators FOR
5199: SELECT inventory_location_id
5207: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5208: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5209: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5210: AND concatenated_segments LIKE (p_concatenated_segments)
5211: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5212: ORDER BY 2;
5213: END IF;
5214: ELSE /*PJM org:Project id not null */
5215: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5228: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5229: AND a.concatenated_segments LIKE (p_concatenated_segments)
5230: AND a.project_id = p_project_id
5231: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5232: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5233: ORDER BY 2;
5234: ELSE --Locators not restricted
5235: OPEN x_locators FOR
5236: SELECT inventory_location_id
5245: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5246: AND concatenated_segments LIKE (p_concatenated_segments)
5247: AND project_id = p_project_id
5248: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5249: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code, inventory_location_id, NULL, NULL, 'L') = 'Y'
5250: ORDER BY 2;
5251: END IF;
5252: END IF;
5253: END IF;
5305: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5306: /*AND b.subinventory_code = p_subinventory_code*/ /*Removed Subinventory restriction, displayes all locs in the org*/
5307: AND a.inventory_location_id = b.secondary_locator
5308: AND a.concatenated_segments LIKE (p_concatenated_segments)
5309: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5310: ORDER BY 2;
5311: ELSE --Locators not restricted
5312: OPEN x_locators FOR
5313: SELECT inventory_location_id
5319: WHERE organization_id = p_organization_id
5320: /*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5321: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5322: AND concatenated_segments LIKE (p_concatenated_segments)
5323: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
5324: ORDER BY 2;
5325: END IF;
5326: ELSE /*PJM org*/
5327: IF p_project_id IS NULL THEN
5339: /*AND b.subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5340: AND a.inventory_location_id = b.secondary_locator
5341: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5342: AND a.concatenated_segments LIKE (p_concatenated_segments)
5343: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5344: ORDER BY 2;
5345: ELSE --Locators not restricted
5346: OPEN x_locators FOR
5347: SELECT inventory_location_id
5354: /*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5355: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5356: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5357: AND concatenated_segments LIKE (p_concatenated_segments)
5358: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
5359: ORDER BY 2;
5360: END IF;
5361: ELSE /*PJM org:Project id not null */
5362: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5374: AND a.inventory_location_id = b.secondary_locator
5375: AND a.concatenated_segments LIKE (p_concatenated_segments)
5376: AND a.project_id = p_project_id
5377: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5378: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, a.inventory_location_id, NULL, NULL, 'L') = 'Y'
5379: ORDER BY 2;
5380: ELSE --Locators not restricted
5381: OPEN x_locators FOR
5382: SELECT inventory_location_id
5390: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5391: AND concatenated_segments LIKE (p_concatenated_segments)
5392: AND project_id = p_project_id
5393: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5394: AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, inventory_location_id, NULL, NULL, 'L') = 'Y'
5395: ORDER BY 2;
5396: END IF;
5397: END IF;
5398: END IF;