[Home] [Help]
242: 2,--not be reservable
243: reservable_type))
244: AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
245: AND msub.secondary_inventory_name LIKE (p_sub)
246: 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'
247: UNION ALL
248: SELECT msub.secondary_inventory_name
249: , NVL(msub.locator_type, 1)
250: , msub.description
304: FROM mtl_item_sub_inventories mis
305: WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
306: AND mis.inventory_item_id = p_item_id
307: AND mis.secondary_inventory = msub.secondary_inventory_name)
308: 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'
309: UNION ALL
310: SELECT msub.secondary_inventory_name
311: , NVL(msub.locator_type, 1)
312: , msub.description
370: msub.reservable_type)
371: ),
372: msub.reservable_type
373: )
374: 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'
375: ORDER BY UPPER(msub.secondary_inventory_name);
376: ELSE
377: -- It is a restricted item,
378: OPEN x_sub FOR
416: FROM mtl_item_sub_inventories mis
417: WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
418: AND mis.inventory_item_id = p_item_id
419: AND mis.secondary_inventory = msub.secondary_inventory_name)
420: 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'
421: ORDER BY UPPER(msub.secondary_inventory_name);
422: END IF;
423: ELSIF p_location_id IS NOT NULL THEN
424: -- For Putaway, p_location_id will always be null, so no change
511: 2,--not be reservable
512: reservable_type))
513: AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
514: AND msub.secondary_inventory_name LIKE (p_sub)
515: 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'
516: UNION ALL
517: SELECT msub.secondary_inventory_name
518: , NVL(msub.locator_type, 1)
519: , msub.description
573: FROM mtl_item_sub_inventories mis
574: WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
575: AND mis.inventory_item_id = p_item_id
576: AND mis.secondary_inventory = msub.secondary_inventory_name)
577: 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'
578: UNION ALL
579: SELECT msub.secondary_inventory_name
580: , NVL(msub.locator_type, 1)
581: , msub.description
639: msub.reservable_type)
640: ),
641: msub.reservable_type
642: )
643: 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'
644: ORDER BY UPPER(msub.secondary_inventory_name);
645: ELSE
646: -- It is a restricted item,
647: OPEN x_sub FOR
685: FROM mtl_item_sub_inventories mis
686: WHERE mis.organization_id = NVL(p_organization_id, mis.organization_id)
687: AND mis.inventory_item_id = p_item_id
688: AND mis.secondary_inventory = msub.secondary_inventory_name)
689: 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'
690: ORDER BY UPPER(msub.secondary_inventory_name);
691: END IF;
692: ELSIF p_location_id IS NOT NULL THEN
693: -- For Putaway, p_location_id will always be null, so no change
833: AND b.subinventory_code = p_subinventory_code
834: AND a.inventory_location_id = b.secondary_locator
835: and a.organization_id = b.organization_id --13472820
836: AND a.concatenated_segments LIKE (p_concatenated_segments)
837: 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'
838: ORDER BY a.concatenated_segments;
839: ELSE --Locators not restricted
840: OPEN x_locators FOR
841: SELECT inventory_location_id
846: WHERE organization_id = p_organization_id
847: AND subinventory_code = p_subinventory_code
848: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
849: AND concatenated_segments LIKE (p_concatenated_segments)
850: 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'
851: ORDER BY concatenated_segments;
852: END IF;
853: END get_loc_lov;
854:
903: AND a.project_id IS NULL
904: AND a.task_id IS NULL
905: AND a.concatenated_segments LIKE (p_concatenated_segments)
906: AND NVL(a.physical_location_id, a.inventory_location_id) = a.inventory_location_id
907: 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'
908: ORDER BY 2;
909: ELSE --Locators not restricted
910: OPEN x_locators FOR
911: SELECT inventory_location_id
918: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
919: AND concatenated_segments LIKE (p_concatenated_segments)
920: AND project_id IS NULL
921: AND task_id IS NULL
922: 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'
923: ORDER BY 2;
924: END IF;
925: END get_loc_lov_pjm;
926:
1634: AND b.inventory_item_id = p_inventory_item_id
1635: AND a.secondary_inventory_name = b.secondary_inventory
1636: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1637: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1638: 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';
1639: ELSE
1640: OPEN x_zones FOR
1641: SELECT secondary_inventory_name
1642: , NVL(locator_type, 1)
1647: FROM mtl_secondary_inventories
1648: WHERE organization_id = p_organization_id
1649: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1650: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1651: 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';
1652: END IF;
1653: ELSE
1654: IF p_restrict_subinventories_code = 1 THEN
1655: OPEN x_zones FOR
1665: AND a.secondary_inventory_name = b.secondary_inventory
1666: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1667: AND b.inventory_item_id = p_inventory_item_id
1668: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1669: 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';
1670: ELSE
1671: OPEN x_zones FOR
1672: SELECT secondary_inventory_name
1673: , NVL(locator_type, 1)
1678: FROM mtl_secondary_inventories
1679: WHERE organization_id = p_organization_id
1680: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1681: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1682: 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';
1683: END IF;
1684: END IF;
1685: ELSE
1686: IF (p_transaction_action_id <> 2
1700: AND a.secondary_inventory_name = b.secondary_inventory
1701: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1702: AND b.inventory_item_id = p_inventory_item_id
1703: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1704: 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';
1705: ELSE
1706: OPEN x_zones FOR
1707: SELECT secondary_inventory_name
1708: , NVL(locator_type, 1)
1713: FROM mtl_secondary_inventories
1714: WHERE organization_id = p_organization_id
1715: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1716: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1717: 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';
1718: END IF;
1719: ELSE
1720: IF p_restrict_subinventories_code = 1 THEN
1721: OPEN x_zones FOR
1731: AND a.secondary_inventory_name = b.secondary_inventory
1732: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1733: AND b.inventory_item_id = p_inventory_item_id
1734: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1735: 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';
1736: ELSE
1737: OPEN x_zones FOR
1738: SELECT secondary_inventory_name
1739: , NVL(locator_type, 1)
1744: FROM mtl_secondary_inventories
1745: WHERE organization_id = p_organization_id
1746: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1747: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1748: 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';
1749: END IF;
1750: END IF;
1751: END IF;
1752: END get_from_subs;
1816: AND a.secondary_inventory_name = b.secondary_inventory
1817: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1818: AND b.inventory_item_id = p_inventory_item_id
1819: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1820: 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';
1821: ELSE
1822: OPEN x_to_sub FOR
1823: SELECT secondary_inventory_name
1824: , NVL(locator_type, 1)
1829: FROM mtl_secondary_inventories
1830: WHERE organization_id = l_org
1831: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1832: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1833: 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';
1834: END IF;
1835: ELSE
1836: IF l_restrict_subinventories_code = 1 THEN
1837: IF l_inventory_asset_flag = 'Y' THEN
1850: AND b.inventory_item_id = p_inventory_item_id
1851: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1852: AND a.secondary_inventory_name = b.secondary_inventory
1853: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1854: 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';
1855: ELSE
1856: OPEN x_to_sub FOR
1857: SELECT a.secondary_inventory_name
1858: , NVL(a.locator_type, 1)
1867: AND b.inventory_item_id = p_inventory_item_id
1868: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1869: AND a.secondary_inventory_name = b.secondary_inventory
1870: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1871: 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';
1872: END IF;
1873: ELSE
1874: OPEN x_to_sub FOR
1875: SELECT a.secondary_inventory_name
1884: AND NVL(a.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1885: AND a.secondary_inventory_name = b.secondary_inventory
1886: AND b.inventory_item_id = p_inventory_item_id
1887: AND a.secondary_inventory_name LIKE (p_secondary_inventory_name)
1888: 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';
1889: END IF;
1890: ELSE
1891: IF l_inventory_asset_flag = 'Y' THEN
1892: IF l_from_sub_asset_inventory = 1 THEN
1901: WHERE organization_id = l_org
1902: --and asset_inventory = 1
1903: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1904: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1905: 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';
1906: ELSE
1907: OPEN x_to_sub FOR
1908: SELECT secondary_inventory_name
1909: , NVL(locator_type, 1)
1915: WHERE organization_id = l_org
1916: AND asset_inventory = 2
1917: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1918: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1919: 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';
1920: END IF;
1921: ELSE
1922: OPEN x_to_sub FOR
1923: SELECT secondary_inventory_name
1929: FROM mtl_secondary_inventories
1930: WHERE organization_id = l_org
1931: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
1932: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
1933: 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';
1934: END IF;
1935: END IF;
1936: END IF;
1937: END get_to_sub;
1998: FROM mtl_secondary_inventories
1999: WHERE organization_id = NVL(p_organization_id, organization_id)
2000: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2001: AND secondary_inventory_name LIKE (p_subinventory_code)
2002: 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'
2003: ORDER BY secondary_inventory_name;
2004: ELSE -- Organization_id is not null
2005: OPEN x_zones FOR
2006: SELECT secondary_inventory_name
2012: FROM mtl_secondary_inventories
2013: WHERE organization_id = p_organization_id
2014: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2015: AND secondary_inventory_name LIKE (p_subinventory_code)
2016: 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'
2017: ORDER BY secondary_inventory_name;
2018: END IF;
2019: END get_valid_subinvs;
2020:
2445: AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2446: AND msub.secondary_inventory_name LIKE (p_subinventory_code)
2447: -- Bug# 2770853
2448: -- Check for material status at the subinventory level
2449: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2450: NULL,
2451: 4,
2452: NULL,
2453: NULL,
2476: AND NVL(msub.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
2477: AND msub.secondary_inventory_name LIKE (p_subinventory_code)
2478: -- Bug# 2770853
2479: -- Check for material status at the subinventory level
2480: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2481: NULL,
2482: 4,
2483: NULL,
2484: NULL,
2588: AND mcce.entry_status_code IN (1,3)
2589: AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2590: -- Bug# 2770853
2591: -- Check for material status at the locator level
2592: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2593: NULL,
2594: 4,
2595: NULL,
2596: NULL,
2621: AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2622: AND project_id = p_project_id
2623: AND task_id = p_task_id
2624: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2625: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2626: NULL,
2627: 4,
2628: NULL,
2629: NULL,
2648: AND subinventory_code = p_subinventory_code
2649: AND concatenated_segments LIKE (p_concatenated_segments )--inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2650: AND project_id = p_project_id
2651: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2652: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2653: NULL,
2654: 4,
2655: NULL,
2656: NULL,
2675: WHERE organization_id = p_organization_id
2676: AND subinventory_code = p_subinventory_code
2677: AND concatenated_segments LIKE (p_concatenated_segments )-- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2678: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2679: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2680: NULL,
2681: 4,
2682: NULL,
2683: NULL,
2776: AND mcce.entry_status_code IN (1,3)
2777: AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2778: -- Bug# 2770853
2779: -- Check for material status at the locator level
2780: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2781: NULL,
2782: 4,
2783: NULL,
2784: NULL,
2809: AND alias = p_alias
2810: AND project_id = p_project_id
2811: AND task_id = p_task_id
2812: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2813: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2814: NULL,
2815: 4,
2816: NULL,
2817: NULL,
2837: AND alias = p_alias
2838: --inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2839: AND project_id = p_project_id
2840: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2841: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2842: NULL,
2843: 4,
2844: NULL,
2845: NULL,
2865: AND subinventory_code = p_subinventory_code
2866: AND alias = p_alias
2867: -- inv_project.get_locsegs(inventory_location_id,organization_id) LIKE (p_concatenated_segments || '%')
2868: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE) -- bug # 4866575
2869: AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
2870: NULL,
2871: 4,
2872: NULL,
2873: NULL,
3237: FETCH l_item_cursor INTO l_item_id;
3238: EXIT WHEN l_item_cursor%NOTFOUND;
3239: l_lpn_content := 1;
3240:
3241: 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
3242: SELECT restrict_subinventories_code
3243: , inventory_asset_flag
3244: INTO l_restrict_subinventories_code
3245: , l_inventory_asset_flag
3372: WHERE outermost_lpn_id = p_lpn_id)
3373: AND inventory_item_id IS NOT NULL;
3374: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3375: BEGIN
3376: 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
3377: OPEN l_item_cursor;
3378: l_lpn_content := 0;
3379:
3380: LOOP
3478: RETURN x_return;
3479: END IF;
3480:
3481: IF p_txn_type_id IN (500, 502) THEN
3482: 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');
3483: 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');
3484:
3485: IF pack_sub_check = 'N'
3486: OR pack_loc_check = 'N' THEN
3479: END IF;
3480:
3481: IF p_txn_type_id IN (500, 502) THEN
3482: 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');
3483: 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');
3484:
3485: IF pack_sub_check = 'N'
3486: OR pack_loc_check = 'N' THEN
3487: x_return := 'N';
3487: x_return := 'N';
3488: RETURN x_return;
3489: END IF;
3490: ELSIF p_txn_type_id IN (501, 502) THEN
3491: 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');
3492: 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');
3493:
3494: IF unpack_sub_check = 'N'
3495: OR unpack_loc_check = 'N' THEN
3488: RETURN x_return;
3489: END IF;
3490: ELSIF p_txn_type_id IN (501, 502) THEN
3491: 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');
3492: 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');
3493:
3494: IF unpack_sub_check = 'N'
3495: OR unpack_loc_check = 'N' THEN
3496: x_return := 'N';
3548: LOOP
3549: FETCH l_cursor INTO l_record;
3550: EXIT WHEN l_cursor%NOTFOUND;
3551: --Added p_lpn_id for bug 13415614
3552: IF inv_material_status_grp.is_status_applicable(p_wms_installed, NULL,
3553: p_transaction_type_id, NULL, NULL, p_orgid, l_record.inventory_item_id,
3554: l_record.subinventory_code, l_record.locator_id, l_record.lot_number, l_record.serial_number,
3555: 'A',p_lpn_id) = 'Y' THEN
3556: x_return := 'Y';
3603: , msi.enable_locator_alias
3604: FROM mtl_secondary_inventories msi, MTL_ONHAND_QUANTITIES_DETAIL moq
3605: WHERE msi.secondary_inventory_name = moq.subinventory_code
3606: AND msi.organization_id = moq.organization_id
3607: 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'
3608: AND moq.containerized_flag = 2
3609: AND moq.subinventory_code LIKE (p_subinventory_code)
3610: AND (moq.revision = p_revision
3611: OR (moq.revision IS NULL
3655: AND mil.organization_id = p_organization_id
3656: -- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
3657: AND mil.project_id IS NULL
3658: AND mil.task_id IS NULL
3659: 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'
3660: AND moq.containerized_flag = 2
3661: AND (moq.revision = p_revision
3662: OR (moq.revision IS NULL
3663: AND p_revision IS NULL
3706: AND mil.organization_id = p_organization_id
3707: -- Bug 2325664 AND mil.physical_location_id is null -- PJM-WMS Integration
3708: AND mil.project_id IS NULL
3709: AND mil.task_id IS NULL
3710: 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'
3711: AND moq.containerized_flag = 2
3712: AND (moq.revision = p_revision
3713: OR (moq.revision IS NULL
3714: AND p_revision IS NULL
4015: WHERE organization_id = NVL(p_organization_id, organization_id)
4016: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4017: AND secondary_inventory_name LIKE (p_subinventory_code)
4018: AND lpn_controlled_flag = 1
4019: 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'
4020: ORDER BY secondary_inventory_name;
4021: ELSE -- Organization_id is not null
4022: OPEN x_zones FOR
4023: SELECT secondary_inventory_name
4030: WHERE organization_id = p_organization_id
4031: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4032: AND secondary_inventory_name LIKE (p_subinventory_code)
4033: AND lpn_controlled_flag = 1
4034: 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'
4035: ORDER BY secondary_inventory_name;
4036: END IF;
4037:
4038:
4099: AND a.inventory_location_id = b.secondary_locator
4100: and a.organization_id = b.organization_id --13472820
4101: AND a.concatenated_segments LIKE (p_concatenated_segments)
4102: /* BUG#2810405: To show only common locators in the LOV */
4103: AND inv_material_status_grp.is_status_applicable
4104: ( p_wms_installed,
4105: NULL,
4106: p_transaction_type_id,
4107: NULL,
4126: AND subinventory_code = p_Subinventory_Code
4127: AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
4128: AND concatenated_segments LIKE (p_concatenated_segments )
4129: /* BUG#2810405: To show only common locators in the LOV */
4130: AND inv_material_status_grp.is_status_applicable
4131: ( p_wms_installed,
4132: NULL,
4133: p_transaction_type_id,
4134: NULL,
4158: and a.organization_id = b.organization_id --13472820
4159: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
4160: AND a.concatenated_segments like (p_concatenated_segments )
4161: /* BUG#2810405: To show only common locators in the LOV */
4162: AND inv_material_status_grp.is_status_applicable
4163: ( p_wms_installed,
4164: NULL,
4165: p_transaction_type_id,
4166: NULL,
4186: AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
4187: AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
4188: AND concatenated_segments LIKE (p_concatenated_segments )
4189: /* BUG#2810405: To show only common locators in the LOV */
4190: AND inv_material_status_grp.is_status_applicable
4191: ( p_wms_installed,
4192: NULL,
4193: p_transaction_type_id,
4194: NULL,
4285: and a.organization_id = b.organization_id --13472820
4286: -- AND a.concatenated_segments LIKE (p_concatenated_segments)
4287: AND a.alias = p_alias
4288: /* BUG#2810405: To show only common locators in the LOV */
4289: AND inv_material_status_grp.is_status_applicable
4290: ( p_wms_installed,
4291: NULL,
4292: p_transaction_type_id,
4293: NULL,
4313: AND nvl(disable_date, trunc(sysdate+1)) > trunc(sysdate)
4314: -- AND concatenated_segments LIKE (p_concatenated_segments )
4315: AND alias = p_alias
4316: /* BUG#2810405: To show only common locators in the LOV */
4317: AND inv_material_status_grp.is_status_applicable
4318: ( p_wms_installed,
4319: NULL,
4320: p_transaction_type_id,
4321: NULL,
4346: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
4347: -- AND a.concatenated_segments like (p_concatenated_segments )
4348: AND a.alias = p_alias
4349: /* BUG#2810405: To show only common locators in the LOV */
4350: AND inv_material_status_grp.is_status_applicable
4351: ( p_wms_installed,
4352: NULL,
4353: p_transaction_type_id,
4354: NULL,
4375: AND inventory_location_id=NVL(physical_location_id,inventory_location_id)
4376: -- AND concatenated_segments LIKE (p_concatenated_segments )
4377: AND alias = p_alias
4378: /* BUG#2810405: To show only common locators in the LOV */
4379: AND inv_material_status_grp.is_status_applicable
4380: ( p_wms_installed,
4381: NULL,
4382: p_transaction_type_id,
4383: NULL,
4922: FETCH l_item_txn_cursor INTO l_item_id, l_transaction_type;
4923: EXIT WHEN l_item_txn_cursor%NOTFOUND;
4924: -- Check if the sub's material status is valid
4925: -- for the current move order line's transaction type
4926: 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
4927: , p_lot_status_enabled => NULL, p_serial_status_enabled => NULL, p_organization_id => p_organization_id
4928: , p_inventory_item_id => l_item_id, p_sub_code => p_subinventory_code, p_locator_id => NULL, p_lot_number => NULL
4929: , p_serial_number => NULL, p_object_type => 'Z');
4930:
5114: EXIT WHEN l_item_txn_cursor%NOTFOUND;
5115:
5116: -- Check if the loc's material status is valid
5117: -- for the current move order line's transaction type
5118: x_return := inv_material_status_grp.is_status_applicable
5119: (p_wms_installed => 'TRUE',
5120: p_trx_status_enabled => NULL,
5121: p_trx_type_id => l_transaction_type,
5122: p_lot_status_enabled => NULL,
5208: AND a.inventory_location_id = b.secondary_locator
5209: and a.organization_id = b.organization_id --13472820
5210: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5211: AND a.alias = p_alias
5212: 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'
5213: ORDER BY 2;
5214: ELSE --Locators not restricted
5215: OPEN x_locators FOR
5216: SELECT inventory_location_id
5222: AND subinventory_code = p_subinventory_code
5223: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5224: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5225: AND alias = p_alias
5226: 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'
5227: ORDER BY 2;
5228: END IF;
5229: ELSE /*PJM org*/
5230: IF p_project_id IS NULL THEN
5242: AND a.inventory_location_id = b.secondary_locator
5243: and a.organization_id = b.organization_id --13472820
5244: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5245: AND a.alias = p_alias
5246: 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'
5247: ORDER BY 2;
5248: ELSE --Locators not restricted
5249: OPEN x_locators FOR
5250: SELECT inventory_location_id
5256: AND subinventory_code = p_subinventory_code
5257: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5258: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5259: AND alias = p_alias
5260: 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'
5261: ORDER BY 2;
5262: END IF;
5263: ELSE /*PJM org:Project id not null */
5264: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5277: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5278: AND a.alias = p_alias
5279: AND a.project_id = p_project_id
5280: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5281: 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'
5282: ORDER BY 2;
5283: ELSE --Locators not restricted
5284: OPEN x_locators FOR
5285: SELECT inventory_location_id
5293: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5294: AND alias = p_alias
5295: AND project_id = p_project_id
5296: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5297: 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'
5298: ORDER BY 2;
5299: END IF;
5300: END IF;
5301: END IF;
5341: AND a.inventory_location_id = b.secondary_locator
5342: and a.organization_id = b.organization_id --13472820
5343: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5344: AND a.concatenated_segments LIKE (p_concatenated_segments)
5345: 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'
5346: ORDER BY 2;
5347: ELSE --Locators not restricted
5348: OPEN x_locators FOR
5349: SELECT inventory_location_id
5355: AND subinventory_code = p_subinventory_code
5356: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5357: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5358: AND concatenated_segments LIKE (p_concatenated_segments)
5359: 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'
5360: ORDER BY 2;
5361: END IF;
5362: ELSE /*PJM org*/
5363: IF p_project_id IS NULL THEN
5375: AND a.inventory_location_id = b.secondary_locator
5376: and a.organization_id = b.organization_id --13472820
5377: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5378: AND a.concatenated_segments LIKE (p_concatenated_segments)
5379: 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'
5380: ORDER BY 2;
5381: ELSE --Locators not restricted
5382: OPEN x_locators FOR
5383: SELECT inventory_location_id
5389: AND subinventory_code = p_subinventory_code
5390: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5391: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5392: AND concatenated_segments LIKE (p_concatenated_segments)
5393: 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'
5394: ORDER BY 2;
5395: END IF;
5396: ELSE /*PJM org:Project id not null */
5397: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5410: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5411: AND a.concatenated_segments LIKE (p_concatenated_segments)
5412: AND a.project_id = p_project_id
5413: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5414: 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'
5415: ORDER BY 2;
5416: ELSE --Locators not restricted
5417: OPEN x_locators FOR
5418: SELECT inventory_location_id
5426: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5427: AND concatenated_segments LIKE (p_concatenated_segments)
5428: AND project_id = p_project_id
5429: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5430: 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'
5431: ORDER BY 2;
5432: END IF;
5433: END IF;
5434: END IF;
5476: AND a.inventory_location_id = b.secondary_locator
5477: and a.organization_id = b.organization_id --13472820
5478: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5479: AND a.concatenated_segments LIKE (p_concatenated_segments)
5480: 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'
5481: ORDER BY 2;
5482: ELSE --Locators not restricted
5483: OPEN x_locators FOR
5484: SELECT inventory_location_id
5490: AND subinventory_code = p_subinventory_code
5491: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5492: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5493: AND concatenated_segments LIKE (p_concatenated_segments)
5494: 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'
5495: ORDER BY 2;
5496: END IF;
5497: ELSE /*PJM org*/
5498: IF p_project_id IS NULL THEN
5511: and a.organization_id = b.organization_id --13472820
5512: -- AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5513: AND a.project_id is null
5514: AND a.concatenated_segments LIKE (p_concatenated_segments)
5515: 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'
5516: ORDER BY 2;
5517: ELSE --Locators not restricted
5518: OPEN x_locators FOR
5519: SELECT inventory_location_id
5526: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5527: -- AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5528: AND project_id is null
5529: AND concatenated_segments LIKE (p_concatenated_segments)
5530: 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'
5531: ORDER BY 2;
5532: END IF;
5533: ELSE /*PJM org:Project id not null */
5534: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5547: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5548: AND a.concatenated_segments LIKE (p_concatenated_segments)
5549: AND a.project_id = p_project_id
5550: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5551: 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'
5552: ORDER BY 2;
5553: ELSE --Locators not restricted
5554: OPEN x_locators FOR
5555: SELECT inventory_location_id
5563: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5564: AND concatenated_segments LIKE (p_concatenated_segments)
5565: AND project_id = p_project_id
5566: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5567: 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'
5568: ORDER BY 2;
5569: END IF;
5570: END IF;
5571: END IF;
5622: AND a.inventory_location_id = b.secondary_locator
5623: and a.organization_id = b.organization_id --13472820
5624: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5625: AND a.concatenated_segments LIKE (p_concatenated_segments)
5626: 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'
5627: ORDER BY 2;
5628: ELSE --Locators not restricted
5629: OPEN x_locators FOR
5630: SELECT inventory_location_id
5637: AND subinventory_code = p_subinventory_code
5638: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5639: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5640: AND concatenated_segments LIKE (p_concatenated_segments)
5641: 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'
5642: ORDER BY 2;
5643: END IF;
5644: ELSE /*PJM org*/
5645: IF p_project_id IS NULL THEN
5659: and a.organization_id = b.organization_id --13472820
5660: /*AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)*/
5661: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5662: AND a.concatenated_segments LIKE (p_concatenated_segments)
5663: 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'
5664: ORDER BY 2;
5665: ELSE --Locators not restricted
5666: OPEN x_locators FOR
5667: SELECT inventory_location_id
5675: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5676: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5677: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5678: AND concatenated_segments LIKE (p_concatenated_segments)
5679: 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'
5680: ORDER BY 2;
5681: END IF;
5682: ELSE /*PJM org:Project id not null */
5683: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5697: --AND inv_project.get_locsegs(a.inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5698: AND a.concatenated_segments LIKE (p_concatenated_segments)
5699: AND a.project_id = p_project_id
5700: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5701: 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'
5702: ORDER BY 2;
5703: ELSE --Locators not restricted
5704: OPEN x_locators FOR
5705: SELECT inventory_location_id
5714: --AND inv_project.get_locsegs(inventory_location_id, p_organization_id) LIKE (p_concatenated_segments)
5715: AND concatenated_segments LIKE (p_concatenated_segments)
5716: AND project_id = p_project_id
5717: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5718: 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'
5719: ORDER BY 2;
5720: END IF;
5721: END IF;
5722: END IF;
5775: /*AND b.subinventory_code = p_subinventory_code*/ /*Removed Subinventory restriction, displayes all locs in the org*/
5776: AND a.inventory_location_id = b.secondary_locator
5777: and a.organization_id = b.organization_id --13472820
5778: AND a.concatenated_segments LIKE (p_concatenated_segments)
5779: 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'
5780: ORDER BY 2;
5781: ELSE --Locators not restricted
5782: OPEN x_locators FOR
5783: SELECT inventory_location_id
5789: WHERE organization_id = p_organization_id
5790: /*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5791: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5792: AND concatenated_segments LIKE (p_concatenated_segments)
5793: 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'
5794: ORDER BY 2;
5795: END IF;
5796: ELSE /*PJM org*/
5797: IF p_project_id IS NULL THEN
5810: AND a.inventory_location_id = b.secondary_locator
5811: and a.organization_id = b.organization_id --13472820
5812: AND a.inventory_location_id=nvl(a.physical_location_id,a.inventory_location_id)
5813: AND a.concatenated_segments LIKE (p_concatenated_segments)
5814: 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'
5815: ORDER BY 2;
5816: ELSE --Locators not restricted
5817: OPEN x_locators FOR
5818: SELECT inventory_location_id
5825: /*AND subinventory_code = p_subinventory_code*//*Removed Subinventory restriction, displayes all locs in the org*/
5826: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5827: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
5828: AND concatenated_segments LIKE (p_concatenated_segments)
5829: 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'
5830: ORDER BY 2;
5831: END IF;
5832: ELSE /*PJM org:Project id not null */
5833: IF p_restrict_locators_code = 1 THEN --Locators restricted to predefined list
5846: and a.organization_id = b.organization_id --13472820
5847: AND a.concatenated_segments LIKE (p_concatenated_segments)
5848: AND a.project_id = p_project_id
5849: AND NVL(a.task_id, -1) = NVL(p_task_id, -1)
5850: 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'
5851: ORDER BY 2;
5852: ELSE --Locators not restricted
5853: OPEN x_locators FOR
5854: SELECT inventory_location_id
5862: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
5863: AND concatenated_segments LIKE (p_concatenated_segments)
5864: AND project_id = p_project_id
5865: AND NVL(task_id, -1) = NVL(p_task_id, -1)
5866: 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'
5867: ORDER BY 2;
5868: END IF;
5869: END IF;
5870: END IF;