[Home] [Help]
40: --To check if it is wms org
41: /*Passing p_organization_id in below call as null as passing p_org_id was checking whether organization is wms
42: enabled or not and because of the updation logic was not working properly for non wms enabled organizations.
43: Here we need to check whether wms is installed or not.Bug # 6936019 */
44: --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);
45:
46: 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);
47:
48: IF l_return_status <> fnd_api.g_ret_sts_success THEN
42: enabled or not and because of the updation logic was not working properly for non wms enabled organizations.
43: Here we need to check whether wms is installed or not.Bug # 6936019 */
44: --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);
45:
46: 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);
47:
48: IF l_return_status <> fnd_api.g_ret_sts_success THEN
49: IF (l_debug = 1) THEN
50: DEBUG('Check if WMS installed');
46: 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);
47:
48: IF l_return_status <> fnd_api.g_ret_sts_success THEN
49: IF (l_debug = 1) THEN
50: DEBUG('Check if WMS installed');
51: END IF;
52: --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53: END IF;
54:
190: , p_item_id IN NUMBER
191: , p_sub IN VARCHAR2
192: , p_restrict_subinventories_code IN NUMBER
193: , p_transaction_type_id IN NUMBER
194: , p_wms_installed IN VARCHAR2
195: , p_location_id IN NUMBER --RCVLOCATORSSUPPORT
196: , p_lpn_context IN NUMBER
197: , p_putaway_code IN NUMBER
198: ) IS --RCVLOCATORSSUPPORT
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
451: , p_item_id IN NUMBER
452: , p_sub IN VARCHAR2
453: , p_restrict_subinventories_code IN NUMBER
454: , p_transaction_type_id IN NUMBER
455: , p_wms_installed IN VARCHAR2
456: , p_location_id IN NUMBER --RCVLOCATORSSUPPORT
457: , p_shipment_header_id IN NUMBER
458: , p_lpn_context IN NUMBER
459: , p_putaway_code IN NUMBER
473: debug('p_organization_id => ' || p_organization_id);
474: debug('p_item_id => ' || p_item_id);
475: debug('p_sub => ' || p_sub);
476: debug('p_transaction_type_id => ' || p_transaction_type_id);
477: debug('p_wms_installed => ' || p_wms_installed);
478: debug('p_putaway_code => ' || p_putaway_code);
479: debug('p_shipment_header_id => ' || p_shipment_header_id);
480: END IF;
481: IF p_lpn_context = 3 THEN
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
811: , p_restrict_locators_code IN NUMBER
812: , p_inventory_item_id IN NUMBER
813: , p_concatenated_segments IN VARCHAR2
814: , p_transaction_type_id IN NUMBER
815: , p_wms_installed IN VARCHAR2
816: ) IS
817: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
818: BEGIN
819: IF (l_debug = 1) THEN
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:
878: , p_restrict_locators_code IN NUMBER
879: , p_inventory_item_id IN NUMBER
880: , p_concatenated_segments IN VARCHAR2
881: , p_transaction_type_id IN NUMBER
882: , p_wms_installed IN VARCHAR2
883: ) IS
884: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
885: BEGIN
886: IF (l_debug = 1) THEN
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:
1256: FROM mtl_secondary_inventories
1257: WHERE organization_id = p_org_id
1258: AND secondary_inventory_name = p_sub_code;
1259:
1260: 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);
1261:
1262: IF l_return_status = 'S' THEN
1263: IF l_wms_org THEN
1264: l_loc_type := 3;
1371: , p_restrict_locators_code IN NUMBER
1372: , p_inventory_item_id IN NUMBER
1373: , p_concatenated_segments IN VARCHAR2
1374: , p_transaction_type_id IN NUMBER
1375: , p_wms_installed IN VARCHAR2
1376: ) IS
1377: l_org NUMBER;
1378: l_restrict_locators_code NUMBER;
1379: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1390: l_org := p_organization_id;
1391: l_restrict_locators_code := p_restrict_locators_code;
1392: END IF;
1393:
1394: 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);
1395: END;
1396:
1397: -- Name: GET_MO_FROMLOC_LOV
1398: --
1607: , p_restrict_subinventories_code IN NUMBER
1608: , p_secondary_inventory_name IN VARCHAR2
1609: , p_transaction_action_id IN NUMBER
1610: , p_transaction_type_id IN NUMBER
1611: , p_wms_installed IN VARCHAR2
1612: ) IS
1613: l_expense_to_asset VARCHAR2(1);
1614: l_debug NUMBER;
1615: BEGIN
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;
1763: , p_transaction_action_id IN NUMBER
1764: , p_to_organization_id IN NUMBER
1765: , p_serial_number_control_code IN NUMBER
1766: , p_transaction_type_id IN NUMBER
1767: , p_wms_installed IN VARCHAR2
1768: ) IS
1769: l_expense_to_asset VARCHAR2(1);
1770: l_inventory_asset_flag VARCHAR2(1);
1771: l_org NUMBER;
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;
1980: x_zones OUT NOCOPY t_genref
1981: , p_organization_id IN NUMBER
1982: , p_subinventory_code IN VARCHAR2
1983: , p_txn_type_id IN NUMBER
1984: , p_wms_installed IN VARCHAR2
1985: ) IS
1986: l_debug NUMBER;
1987: BEGIN
1988: l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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:
2944: , p_owning_org_id IN NUMBER
2945: , p_owning_tp_type IN NUMBER
2946: , p_to_organization_id IN NUMBER
2947: , p_transaction_type_id IN NUMBER
2948: , p_wms_installed IN VARCHAR2
2949: ) IS
2950: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2951: BEGIN
2952: OPEN x_lpns FOR
2971: AND a.parent_lpn_id IS NULL
2972: AND vaildate_lpn_status(a.outermost_lpn_id
2973: ,a.organization_id
2974: ,p_to_organization_id
2975: ,p_wms_installed
2976: ,p_transaction_type_id) = 'Y'
2977: AND (p_owning_org_id IS NULL
2978: OR EXISTS(SELECT 1 FROM mtl_onhand_quantities_detail moqd
2979: ,wms_license_plate_numbers wlpn
3129: , p_from_sub_asset_inventory IN VARCHAR2
3130: , p_transaction_action_id IN NUMBER
3131: , p_to_organization_id IN NUMBER
3132: , p_transaction_type_id IN NUMBER
3133: , p_wms_installed IN VARCHAR2
3134: , p_secondary_inventory_name IN VARCHAR2
3135: ) IS
3136: l_org NUMBER;
3137: l_lpn_rsvd NUMBER;
3178: , enable_locator_alias
3179: FROM mtl_secondary_inventories
3180: WHERE organization_id = l_org
3181: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3182: 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'
3183: AND secondary_inventory_name LIKE (p_secondary_inventory_name);
3184: ELSE
3185: OPEN x_to_sub FOR
3186: SELECT secondary_inventory_name
3191: , enable_locator_alias
3192: FROM mtl_secondary_inventories
3193: WHERE organization_id = l_org
3194: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
3195: 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'
3196: AND secondary_inventory_name LIKE (p_secondary_inventory_name)
3197: AND reservable_type = 1
3198: AND lpn_controlled_flag = 1;
3199: END IF;
3198: AND lpn_controlled_flag = 1;
3199: END IF;
3200: END get_valid_lpn_tosubs;
3201:
3202: 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)
3203: RETURN VARCHAR2 IS
3204: l_item_id NUMBER;
3205: l_restrict_subinventories_code NUMBER;
3206: l_inventory_asset_flag VARCHAR2(1);
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
3354:
3355: RETURN x_return;
3356: END vaildate_to_lpn_sub;
3357:
3358: 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)
3359: RETURN VARCHAR2 IS
3360: l_item_id NUMBER;
3361: l_restrict_locators_code NUMBER;
3362: l_count NUMBER;
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
3421:
3422: RETURN x_return;
3423: END vaildate_lpn_toloc;
3424:
3425: 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
3426: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3427: BEGIN
3428: OPEN x_locators FOR
3429: SELECT inventory_location_id
3433: FROM wms_item_locations_kfv
3434: WHERE organization_id = p_organization_id
3435: AND subinventory_code = p_subinventory_code
3436: AND concatenated_segments LIKE (p_concatenated_segments)
3437: 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';
3438: END get_lpnloc_lov;
3439:
3440: 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)
3441: RETURN VARCHAR2 IS
3501: x_return := 'Y';
3502: RETURN x_return;
3503: END validate_sub_loc_status;
3504:
3505: 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)
3506: RETURN VARCHAR2 IS
3507: x_return VARCHAR2(1);
3508:
3509: TYPE l_rec IS RECORD(
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';
3996: x_zones OUT NOCOPY t_genref
3997: , p_organization_id IN NUMBER
3998: , p_subinventory_code IN VARCHAR2
3999: , p_txn_type_id IN NUMBER
4000: , p_wms_installed IN VARCHAR2
4001: ) IS
4002: l_debug NUMBER;
4003: BEGIN
4004: l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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:
4049: , p_restrict_locators_code IN NUMBER
4050: , p_inventory_item_id IN NUMBER
4051: , p_concatenated_segments IN VARCHAR2
4052: , p_transaction_type_id IN NUMBER
4053: , p_wms_installed IN VARCHAR2
4054: , p_project_id IN NUMBER
4055: , p_task_id IN NUMBER
4056: ) IS
4057: x_return_status VARCHAR2(100);
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,
4108: NULL,
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,
4135: NULL,
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,
4167: NULL,
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,
4195: NULL,
4214: , p_restrict_locators_code IN NUMBER
4215: , p_inventory_item_id IN NUMBER
4216: , p_concatenated_segments IN VARCHAR2
4217: , p_transaction_type_id IN NUMBER
4218: , p_wms_installed IN VARCHAR2
4219: , p_project_id IN NUMBER
4220: , p_task_id IN NUMBER
4221: , p_alias IN VARCHAR2
4222: ) IS
4240: , p_restrict_locators_code => p_restrict_locators_code
4241: , p_inventory_item_id => p_inventory_item_id
4242: , p_concatenated_segments => p_concatenated_segments
4243: , p_transaction_type_id => p_transaction_type_id
4244: , p_wms_installed => p_wms_installed
4245: , p_project_id => p_project_id
4246: , p_task_id => p_task_id
4247: );
4248: RETURN;
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,
4294: NULL,
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,
4322: NULL,
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,
4355: NULL,
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,
4384: NULL,
4403: , p_restrict_locators_code IN NUMBER
4404: , p_inventory_item_id IN NUMBER
4405: , p_concatenated_segments IN VARCHAR2
4406: , p_transaction_type_id IN NUMBER
4407: , p_wms_installed IN VARCHAR2
4408: , p_project_id IN NUMBER
4409: , p_task_id IN NUMBER
4410: , p_alias IN VARCHAR2
4411: ) IS
4425: , p_restrict_locators_code => p_restrict_locators_code
4426: , p_inventory_item_id => p_inventory_item_id
4427: , p_concatenated_segments => p_concatenated_segments
4428: , p_transaction_type_id => p_transaction_type_id
4429: , p_wms_installed => p_wms_installed
4430: , p_project_id => p_project_id
4431: , p_task_id => p_task_id
4432: );
4433: RETURN; --Bug 8237335 Added return statement as for p_alias is null.
4453: , p_restrict_locators_code => l_restrict_locators_code
4454: , p_inventory_item_id => p_inventory_item_id
4455: , p_concatenated_segments => p_concatenated_segments
4456: , p_transaction_type_id => p_transaction_type_id
4457: , p_wms_installed => p_wms_installed
4458: , p_project_id => p_project_id
4459: , p_task_id => p_task_id
4460: , p_alias => p_alias
4461: );
4471: , p_restrict_locators_code IN NUMBER
4472: , p_inventory_item_id IN NUMBER
4473: , p_concatenated_segments IN VARCHAR2
4474: , p_transaction_type_id IN NUMBER
4475: , p_wms_installed IN VARCHAR2
4476: , p_project_id IN NUMBER
4477: , p_task_id IN NUMBER
4478: ) IS
4479: l_org NUMBER;
4488: debug('p_restrict_locators_code : '|| p_restrict_locators_code);
4489: debug('p_inventory_item_id : '|| p_inventory_item_id);
4490: debug('p_concatenated_segments : '|| p_concatenated_segments);
4491: debug('p_transaction_type_id :' || p_transaction_type_id);
4492: debug('p_wms_installed : '|| p_wms_installed);
4493: debug('p_project_id : '|| p_project_id);
4494: debug('p_task_id : '|| p_task_id);
4495:
4496: IF p_transaction_action_id IN (3, 21) THEN
4515: , p_restrict_locators_code => l_restrict_locators_code
4516: , p_inventory_item_id => p_inventory_item_id
4517: , p_concatenated_segments => p_concatenated_segments
4518: , p_transaction_type_id => p_transaction_type_id
4519: , p_wms_installed => p_wms_installed
4520: , p_project_id => p_project_id
4521: , p_task_id => p_task_id
4522: ); */
4523:
4529: , p_restrict_locators_code => l_restrict_locators_code
4530: , p_inventory_item_id => p_inventory_item_id
4531: , p_concatenated_segments => p_concatenated_segments
4532: , p_transaction_type_id => p_transaction_type_id
4533: , p_wms_installed => p_wms_installed
4534: , p_project_id => p_project_id
4535: , p_task_id => p_task_id
4536: );
4537: END get_valid_prj_to_locs;
4544: , p_lpn_id IN NUMBER
4545: , p_subinventory_code IN VARCHAR2
4546: , p_concatenated_segments IN VARCHAR2
4547: , p_transaction_type_id IN NUMBER
4548: , p_wms_installed IN VARCHAR2
4549: , p_project_id IN NUMBER
4550: , p_task_id IN NUMBER
4551: ) IS
4552: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4562: AND subinventory_code = p_subinventory_code
4563: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4564: AND concatenated_segments LIKE (p_concatenated_segments)
4565: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4566: 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';
4567: END get_prj_lpnloc_lov;
4568: PROCEDURE get_prj_lpnloc_lov(
4569: x_locators OUT NOCOPY t_genref
4570: , p_organization_id IN NUMBER
4571: , p_lpn_id IN NUMBER
4572: , p_subinventory_code IN VARCHAR2
4573: , p_concatenated_segments IN VARCHAR2
4574: , p_transaction_type_id IN NUMBER
4575: , p_wms_installed IN VARCHAR2
4576: , p_project_id IN NUMBER
4577: , p_task_id IN NUMBER
4578: , p_alias IN VARCHAR2
4579: ) IS
4587: , p_lpn_id => p_lpn_id
4588: , p_subinventory_code => p_subinventory_code
4589: , p_concatenated_segments => p_concatenated_segments
4590: , p_transaction_type_id => p_transaction_type_id
4591: , p_wms_installed => p_wms_installed
4592: , p_project_id => p_project_id
4593: , p_task_id => p_task_id
4594: );
4595: RETURN;
4605: AND subinventory_code = p_subinventory_code
4606: AND NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
4607: AND alias = p_alias
4608: AND inventory_location_id=nvl(physical_location_id,inventory_location_id)
4609: 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';
4610: END get_prj_lpnloc_lov;
4611:
4612:
4613: -- This procedure is used for user directed putaway
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:
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,
5123: p_serial_status_enabled => NULL,
5157: , p_restrict_locators_code IN NUMBER
5158: , p_inventory_item_id IN NUMBER
5159: , p_concatenated_segments IN VARCHAR2
5160: , p_transaction_type_id IN NUMBER
5161: , p_wms_installed IN VARCHAR2
5162: , p_project_id IN NUMBER
5163: , p_task_id IN NUMBER
5164: , p_alias IN VARCHAR2
5165: ) IS
5177: , p_restrict_locators_code => p_restrict_locators_code
5178: , p_inventory_item_id => p_inventory_item_id
5179: , p_concatenated_segments => p_concatenated_segments
5180: , p_transaction_type_id => p_transaction_type_id
5181: , p_wms_installed => p_wms_installed
5182: , p_project_id => p_project_id
5183: , p_task_id => p_task_id
5184: );
5185: RETURN;
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;
5308: , p_restrict_locators_code IN NUMBER
5309: , p_inventory_item_id IN NUMBER
5310: , p_concatenated_segments IN VARCHAR2
5311: , p_transaction_type_id IN NUMBER
5312: , p_wms_installed IN VARCHAR2
5313: , p_project_id IN NUMBER
5314: , p_task_id IN NUMBER) IS
5315:
5316: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
5443: , p_restrict_locators_code IN NUMBER
5444: , p_inventory_item_id IN NUMBER
5445: , p_concatenated_segments IN VARCHAR2
5446: , p_transaction_type_id IN NUMBER
5447: , p_wms_installed IN VARCHAR2
5448: , p_project_id IN NUMBER
5449: , p_task_id IN NUMBER) IS
5450:
5451: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
5589: , p_restrict_locators_code IN NUMBER
5590: , p_inventory_item_id IN NUMBER
5591: , p_concatenated_segments IN VARCHAR2
5592: , p_transaction_type_id IN NUMBER
5593: , p_wms_installed IN VARCHAR2
5594: , p_project_id IN NUMBER
5595: , p_task_id IN NUMBER) IS
5596:
5597: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
5742: , p_restrict_locators_code IN NUMBER
5743: , p_inventory_item_id IN NUMBER
5744: , p_concatenated_segments IN VARCHAR2
5745: , p_transaction_type_id IN NUMBER
5746: , p_wms_installed IN VARCHAR2
5747: , p_project_id IN NUMBER
5748: , p_task_id IN NUMBER) IS
5749:
5750: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
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;
5910: p_restrict_locators_code => p_restrict_locators_code,
5911: p_inventory_item_id => p_inventory_item_id,
5912: p_concatenated_segments => p_locator,
5913: p_transaction_type_id => p_transaction_type_id,
5914: p_wms_installed => 'Y',
5915: p_project_id => l_project_id,
5916: p_task_id => l_task_id);
5917:
5918: LOOP
6905: , p_restrict_locators_code IN NUMBER
6906: , p_inventory_item_id IN NUMBER
6907: , p_concatenated_segments IN VARCHAR2
6908: , p_transaction_type_id IN NUMBER
6909: , p_wms_installed IN VARCHAR2
6910: , p_project_id IN NUMBER
6911: , p_task_id IN NUMBER
6912: ) IS
6913: x_return_status VARCHAR2(100);
6924: debug('p_restrict_locators_code : ' || p_restrict_locators_code);
6925: debug('p_inventory_item_id : ' || p_inventory_item_id);
6926: debug('p_concatenated_segments : ' || p_concatenated_segments);
6927: debug('p_transaction_type_id : ' || p_transaction_type_id);
6928: debug('p_wms_installed : ' || p_wms_installed);
6929: debug('p_project_id : ' || p_project_id);
6930: debug('p_task_id : ' || p_task_id);
6931:
6932: BEGIN