[Home] [Help]
33: --To check if it is wms org
34: /*Passing p_organization_id in below call as null as passing p_org_id was checking whether organization is wms
35: enabled or not and because of the updation logic was not working properly for non wms enabled organizations.
36: Here we need to check whether wms is installed or not.Bug # 6936019 */
37: --l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => p_org_id);
38:
39: l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => NULL);
40:
41: IF l_return_status <> fnd_api.g_ret_sts_success THEN
35: enabled or not and because of the updation logic was not working properly for non wms enabled organizations.
36: Here we need to check whether wms is installed or not.Bug # 6936019 */
37: --l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => p_org_id);
38:
39: l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => NULL);
40:
41: IF l_return_status <> fnd_api.g_ret_sts_success THEN
42: IF (l_debug = 1) THEN
43: DEBUG('Check if WMS installed');
39: l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => NULL);
40:
41: IF l_return_status <> fnd_api.g_ret_sts_success THEN
42: IF (l_debug = 1) THEN
43: DEBUG('Check if WMS installed');
44: END IF;
45: --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46:
47: END IF;
136: , p_item_id IN NUMBER
137: , p_sub IN VARCHAR2
138: , p_restrict_subinventories_code IN NUMBER
139: , p_transaction_type_id IN NUMBER
140: , p_wms_installed IN VARCHAR2
141: , p_location_id IN NUMBER --RCVLOCATORSSUPPORT
142: , p_lpn_context IN NUMBER
143: , p_putaway_code IN NUMBER
144: ) IS --RCVLOCATORSSUPPORT
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
480: , p_restrict_locators_code IN NUMBER
481: , p_inventory_item_id IN NUMBER
482: , p_concatenated_segments IN VARCHAR2
483: , p_transaction_type_id IN NUMBER
484: , p_wms_installed IN VARCHAR2
485: ) IS
486: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
487: BEGIN
488: IF (l_debug = 1) THEN
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:
546: , p_restrict_locators_code IN NUMBER
547: , p_inventory_item_id IN NUMBER
548: , p_concatenated_segments IN VARCHAR2
549: , p_transaction_type_id IN NUMBER
550: , p_wms_installed IN VARCHAR2
551: ) IS
552: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
553: BEGIN
554: IF (l_debug = 1) THEN
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:
919: FROM mtl_secondary_inventories
920: WHERE organization_id = p_org_id
921: AND secondary_inventory_name = p_sub_code;
922:
923: l_wms_org := wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_organization_id => p_org_id);
924:
925: IF l_return_status = 'S' THEN
926: IF l_wms_org THEN
927: l_loc_type := 3;
1034: , p_restrict_locators_code IN NUMBER
1035: , p_inventory_item_id IN NUMBER
1036: , p_concatenated_segments IN VARCHAR2
1037: , p_transaction_type_id IN NUMBER
1038: , p_wms_installed IN VARCHAR2
1039: ) IS
1040: l_org NUMBER;
1041: l_restrict_locators_code NUMBER;
1042: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1053: l_org := p_organization_id;
1054: l_restrict_locators_code := p_restrict_locators_code;
1055: END IF;
1056:
1057: get_loc_lov(x_locators, l_org, p_subinventory_code, l_restrict_locators_code, p_inventory_item_id, p_concatenated_segments, p_transaction_type_id, p_wms_installed);
1058: END;
1059:
1060: -- Name: GET_MO_FROMLOC_LOV
1061: --
1232: , p_restrict_subinventories_code IN NUMBER
1233: , p_secondary_inventory_name IN VARCHAR2
1234: , p_transaction_action_id IN NUMBER
1235: , p_transaction_type_id IN NUMBER
1236: , p_wms_installed IN VARCHAR2
1237: ) IS
1238: l_expense_to_asset VARCHAR2(1);
1239: l_debug NUMBER;
1240: BEGIN
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;
1388: , p_transaction_action_id IN NUMBER
1389: , p_to_organization_id IN NUMBER
1390: , p_serial_number_control_code IN NUMBER
1391: , p_transaction_type_id IN NUMBER
1392: , p_wms_installed IN VARCHAR2
1393: ) IS
1394: l_expense_to_asset VARCHAR2(1);
1395: l_inventory_asset_flag VARCHAR2(1);
1396: l_org NUMBER;
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;
1605: x_zones OUT NOCOPY t_genref
1606: , p_organization_id IN NUMBER
1607: , p_subinventory_code IN VARCHAR2
1608: , p_txn_type_id IN NUMBER
1609: , p_wms_installed IN VARCHAR2
1610: ) IS
1611: l_debug NUMBER;
1612: BEGIN
1613: l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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:
2567: , p_owning_org_id IN NUMBER
2568: , p_owning_tp_type IN NUMBER
2569: , p_to_organization_id IN NUMBER
2570: , p_transaction_type_id IN NUMBER
2571: , p_wms_installed IN VARCHAR2
2572: ) IS
2573: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2574: BEGIN
2575: OPEN x_lpns FOR
2594: AND a.parent_lpn_id IS NULL
2595: AND vaildate_lpn_status(a.outermost_lpn_id
2596: ,a.organization_id
2597: ,p_to_organization_id
2598: ,p_wms_installed
2599: ,p_transaction_type_id) = 'Y'
2600: AND (p_owning_org_id IS NULL
2601: OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
2602: ,wms_license_plate_numbers wlpn
2752: , p_from_sub_asset_inventory IN VARCHAR2
2753: , p_transaction_action_id IN NUMBER
2754: , p_to_organization_id IN NUMBER
2755: , p_transaction_type_id IN NUMBER
2756: , p_wms_installed IN VARCHAR2
2757: , p_secondary_inventory_name IN VARCHAR2
2758: ) IS
2759: l_org NUMBER;
2760: l_lpn_rsvd NUMBER;
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;
2821: AND lpn_controlled_flag = 1;
2822: END IF;
2823: END get_valid_lpn_tosubs;
2824:
2825: FUNCTION vaildate_to_lpn_sub(p_lpn_id IN NUMBER, p_to_subinventory IN VARCHAR2, p_orgid IN NUMBER, p_from_sub_asset_inventory IN VARCHAR2, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
2826: RETURN VARCHAR2 IS
2827: l_item_id NUMBER;
2828: l_restrict_subinventories_code NUMBER;
2829: l_inventory_asset_flag VARCHAR2(1);
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
2959:
2960: RETURN x_return;
2961: END vaildate_to_lpn_sub;
2962:
2963: FUNCTION vaildate_lpn_toloc(p_lpn_id IN NUMBER, p_to_subinventory IN VARCHAR2, p_orgid IN NUMBER, p_locator_id IN NUMBER, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
2964: RETURN VARCHAR2 IS
2965: l_item_id NUMBER;
2966: l_restrict_locators_code NUMBER;
2967: l_count NUMBER;
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
3026:
3027: RETURN x_return;
3028: END vaildate_lpn_toloc;
3029:
3030: PROCEDURE get_lpnloc_lov(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_lpn_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2) IS
3031: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3032: BEGIN
3033: OPEN x_locators FOR
3034: SELECT inventory_location_id
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
3106: x_return := 'Y';
3107: RETURN x_return;
3108: END validate_sub_loc_status;
3109:
3110: FUNCTION vaildate_lpn_status(p_lpn_id IN NUMBER, p_orgid IN NUMBER, p_to_org_id IN NUMBER, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
3111: RETURN VARCHAR2 IS
3112: x_return VARCHAR2(1);
3113:
3114: TYPE l_rec IS RECORD(
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';
3578: x_zones OUT NOCOPY t_genref
3579: , p_organization_id IN NUMBER
3580: , p_subinventory_code IN VARCHAR2
3581: , p_txn_type_id IN NUMBER
3582: , p_wms_installed IN VARCHAR2
3583: ) IS
3584: l_debug NUMBER;
3585: BEGIN
3586: l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
3630: , p_restrict_locators_code IN NUMBER
3631: , p_inventory_item_id IN NUMBER
3632: , p_concatenated_segments IN VARCHAR2
3633: , p_transaction_type_id IN NUMBER
3634: , p_wms_installed IN VARCHAR2
3635: , p_project_id IN NUMBER
3636: , p_task_id IN NUMBER
3637: ) IS
3638: x_return_status VARCHAR2(100);
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,
3688: NULL,
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,
3715: NULL,
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,
3746: NULL,
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,
3774: NULL,
3793: , p_restrict_locators_code IN NUMBER
3794: , p_inventory_item_id IN NUMBER
3795: , p_concatenated_segments IN VARCHAR2
3796: , p_transaction_type_id IN NUMBER
3797: , p_wms_installed IN VARCHAR2
3798: , p_project_id IN NUMBER
3799: , p_task_id IN NUMBER
3800: , p_alias IN VARCHAR2
3801: ) IS
3819: , p_restrict_locators_code => p_restrict_locators_code
3820: , p_inventory_item_id => p_inventory_item_id
3821: , p_concatenated_segments => p_concatenated_segments
3822: , p_transaction_type_id => p_transaction_type_id
3823: , p_wms_installed => p_wms_installed
3824: , p_project_id => p_project_id
3825: , p_task_id => p_task_id
3826: );
3827: RETURN;
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,
3872: NULL,
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,
3900: NULL,
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,
3932: NULL,
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,
3961: NULL,
3980: , p_restrict_locators_code IN NUMBER
3981: , p_inventory_item_id IN NUMBER
3982: , p_concatenated_segments IN VARCHAR2
3983: , p_transaction_type_id IN NUMBER
3984: , p_wms_installed IN VARCHAR2
3985: , p_project_id IN NUMBER
3986: , p_task_id IN NUMBER
3987: , p_alias IN VARCHAR2
3988: ) IS
4001: , p_restrict_locators_code => p_restrict_locators_code
4002: , p_inventory_item_id => p_inventory_item_id
4003: , p_concatenated_segments => p_concatenated_segments
4004: , p_transaction_type_id => p_transaction_type_id
4005: , p_wms_installed => p_wms_installed
4006: , p_project_id => p_project_id
4007: , p_task_id => p_task_id
4008: );
4009: END IF;
4027: , p_restrict_locators_code => l_restrict_locators_code
4028: , p_inventory_item_id => p_inventory_item_id
4029: , p_concatenated_segments => p_concatenated_segments
4030: , p_transaction_type_id => p_transaction_type_id
4031: , p_wms_installed => p_wms_installed
4032: , p_project_id => p_project_id
4033: , p_task_id => p_task_id
4034: , p_alias => p_alias
4035: );
4045: , p_restrict_locators_code IN NUMBER
4046: , p_inventory_item_id IN NUMBER
4047: , p_concatenated_segments IN VARCHAR2
4048: , p_transaction_type_id IN NUMBER
4049: , p_wms_installed IN VARCHAR2
4050: , p_project_id IN NUMBER
4051: , p_task_id IN NUMBER
4052: ) IS
4053: l_org NUMBER;
4074: , p_restrict_locators_code => l_restrict_locators_code
4075: , p_inventory_item_id => p_inventory_item_id
4076: , p_concatenated_segments => p_concatenated_segments
4077: , p_transaction_type_id => p_transaction_type_id
4078: , p_wms_installed => p_wms_installed
4079: , p_project_id => p_project_id
4080: , p_task_id => p_task_id
4081: );
4082: END get_valid_prj_to_locs;
4089: , p_lpn_id IN NUMBER
4090: , p_subinventory_code IN VARCHAR2
4091: , p_concatenated_segments IN VARCHAR2
4092: , p_transaction_type_id IN NUMBER
4093: , p_wms_installed IN VARCHAR2
4094: , p_project_id IN NUMBER
4095: , p_task_id IN NUMBER
4096: ) IS
4097: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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
4116: , p_lpn_id IN NUMBER
4117: , p_subinventory_code IN VARCHAR2
4118: , p_concatenated_segments IN VARCHAR2
4119: , p_transaction_type_id IN NUMBER
4120: , p_wms_installed IN VARCHAR2
4121: , p_project_id IN NUMBER
4122: , p_task_id IN NUMBER
4123: , p_alias IN VARCHAR2
4124: ) IS
4132: , p_lpn_id => p_lpn_id
4133: , p_subinventory_code => p_subinventory_code
4134: , p_concatenated_segments => p_concatenated_segments
4135: , p_transaction_type_id => p_transaction_type_id
4136: , p_wms_installed => p_wms_installed
4137: , p_project_id => p_project_id
4138: , p_task_id => p_task_id
4139: );
4140: RETURN;
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
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:
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,
4666: p_serial_status_enabled => NULL,
4700: , p_restrict_locators_code IN NUMBER
4701: , p_inventory_item_id IN NUMBER
4702: , p_concatenated_segments IN VARCHAR2
4703: , p_transaction_type_id IN NUMBER
4704: , p_wms_installed IN VARCHAR2
4705: , p_project_id IN NUMBER
4706: , p_task_id IN NUMBER
4707: , p_alias IN VARCHAR2
4708: ) IS
4720: , p_restrict_locators_code => p_restrict_locators_code
4721: , p_inventory_item_id => p_inventory_item_id
4722: , p_concatenated_segments => p_concatenated_segments
4723: , p_transaction_type_id => p_transaction_type_id
4724: , p_wms_installed => p_wms_installed
4725: , p_project_id => p_project_id
4726: , p_task_id => p_task_id
4727: );
4728: RETURN;
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;
4848: , p_restrict_locators_code IN NUMBER
4849: , p_inventory_item_id IN NUMBER
4850: , p_concatenated_segments IN VARCHAR2
4851: , p_transaction_type_id IN NUMBER
4852: , p_wms_installed IN VARCHAR2
4853: , p_project_id IN NUMBER
4854: , p_task_id IN NUMBER) IS
4855:
4856: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
4980: , p_restrict_locators_code IN NUMBER
4981: , p_inventory_item_id IN NUMBER
4982: , p_concatenated_segments IN VARCHAR2
4983: , p_transaction_type_id IN NUMBER
4984: , p_wms_installed IN VARCHAR2
4985: , p_project_id IN NUMBER
4986: , p_task_id IN NUMBER) IS
4987:
4988: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
5123: , p_restrict_locators_code IN NUMBER
5124: , p_inventory_item_id IN NUMBER
5125: , p_concatenated_segments IN VARCHAR2
5126: , p_transaction_type_id IN NUMBER
5127: , p_wms_installed IN VARCHAR2
5128: , p_project_id IN NUMBER
5129: , p_task_id IN NUMBER) IS
5130:
5131: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
5273: , p_restrict_locators_code IN NUMBER
5274: , p_inventory_item_id IN NUMBER
5275: , p_concatenated_segments IN VARCHAR2
5276: , p_transaction_type_id IN NUMBER
5277: , p_wms_installed IN VARCHAR2
5278: , p_project_id IN NUMBER
5279: , p_task_id IN NUMBER) IS
5280:
5281: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
5438: p_restrict_locators_code => p_restrict_locators_code,
5439: p_inventory_item_id => p_inventory_item_id,
5440: p_concatenated_segments => p_locator,
5441: p_transaction_type_id => p_transaction_type_id,
5442: p_wms_installed => 'Y',
5443: p_project_id => l_project_id,
5444: p_task_id => l_task_id);
5445:
5446: LOOP