[Home] [Help]
74: gross_weight_uom_code,
75: NVL(gross_weight, 0),
76: content_volume_uom_code,
77: NVL(content_volume, 0)
78: FROM wms_license_plate_numbers
79: WHERE license_plate_number LIKE (p_lpn)
80: ORDER BY license_plate_number;
81:
82: END GET_LPN_LOV;
105: gross_weight_uom_code,
106: NVL(gross_weight, 0),
107: content_volume_uom_code,
108: NVL(content_volume, 0)
109: FROM wms_license_plate_numbers
110: WHERE license_plate_number LIKE (p_lpn)
111: and organization_id LIKE (p_orgid)
112: ORDER BY license_plate_number;
113:
139: wlpn.gross_weight_uom_code,
140: NVL(wlpn.gross_weight, 0),
141: wlpn.content_volume_uom_code,
142: NVL(wlpn.content_volume, 0)
143: FROM wms_license_plate_numbers wlpn,
144: mtl_material_transactions_temp mmtt
145: WHERE wlpn.license_plate_number LIKE (p_lpn) and
146: mmtt.organization_id = p_org_id and
147: mmtt.cartonization_id = wlpn.lpn_id and
176: ' wlpn.content_volume_uom_code, ' ||
177: ' NVL(wlpn.content_volume, 0), ' ||
178: ' milk.concatenated_segments, ' ||
179: ' wlpn.lpn_context ' ||
180: 'FROM wms_license_plate_numbers wlpn, ' ||
181: ' mtl_item_locations_kfv milk, ' ||
182: ' wms_lpn_contents wlc ' ||
183: 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
184: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
229: ' INV_PROJECT.GET_PROJECT_NUMBER, ' ||
230: ' INV_PROJECT.GET_TASK_ID, ' ||
231: ' INV_PROJECT.GET_TASK_NUMBER, ' ||
232: ' wlpn.lpn_context ' ||
233: 'FROM wms_license_plate_numbers wlpn, ' ||
234: ' mtl_item_locations milk, ' ||
235: ' wms_lpn_contents wlc ' ||
236: 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
237: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
280: wlpn.content_volume_uom_code,
281: NVL(wlpn.content_volume, 0),
282: milk.concatenated_segments,
283: wlpn.lpn_context
284: FROM wms_license_plate_numbers wlpn,
285: mtl_item_locations_kfv milk,
286: wms_lpn_contents wlc
287: WHERE wlpn.organization_id = To_number(p_organization_id)
288: AND wlpn.organization_id = milk.organization_id (+)
325: wlpn.content_volume_uom_code,
326: NVL(wlpn.content_volume, 0),
327: milk.concatenated_segments,
328: wlpn.lpn_context
329: FROM wms_license_plate_numbers wlpn,
330: mtl_item_locations_kfv milk,
331: wms_lpn_contents wlc
332: WHERE wlpn.organization_id = milk.organization_id (+)
333: AND wlpn.locator_id = milk.inventory_location_id(+)
428: wlpn.content_volume_uom_code,
429: NVL(wlpn.content_volume, 0),
430: milk.concatenated_segments,
431: wlpn.lpn_context
432: FROM wms_license_plate_numbers wlpn,
433: mtl_item_locations_kfv milk,
434: wms_lpn_contents wlc
435: WHERE wlpn.organization_id = milk.organization_id (+)
436: AND wlpn.locator_id = milk.inventory_location_id(+)
492: wlpn.content_volume_uom_code,
493: NVL(wlpn.content_volume, 0),
494: milk.concatenated_segments,
495: wlpn.lpn_context
496: FROM wms_license_plate_numbers wlpn,
497: mtl_item_locations_kfv milk,
498: wms_lpn_contents wlc
499: WHERE wlpn.organization_id = milk.organization_id (+)
500: AND wlpn.locator_id = milk.inventory_location_id(+)
553: wlpn.content_volume_uom_code,
554: NVL(wlpn.content_volume, 0),
555: milk.concatenated_segments,
556: wlpn.lpn_context
557: FROM wms_license_plate_numbers wlpn,
558: mtl_item_locations_kfv milk,
559: wms_lpn_contents wlc
560: WHERE wlpn.organization_id = milk.organization_id (+)
561: AND wlpn.locator_id = milk.inventory_location_id(+)
719: , wlpn.content_volume_uom_code
720: , NVL(wlpn.content_volume, 0)
721: , milk.concatenated_segments
722: , wlpn.lpn_context
723: FROM wms_license_plate_numbers wlpn
724: , mtl_item_locations_kfv milk
725: WHERE wlpn.organization_id = milk.organization_id (+)
726: AND wlpn.locator_id = milk.inventory_location_id (+)
727: AND wlpn.outermost_lpn_id = wlpn.lpn_id
769: ' wlpn.content_volume_uom_code, ' ||
770: ' NVL(wlpn.content_volume, 0), ' ||
771: ' milk.concatenated_segments, ' ||
772: ' wlpn.lpn_context ' ||
773: 'FROM wms_license_plate_numbers wlpn, ' ||
774: ' mtl_item_locations_kfv milk, ' ||
775: ' wms_lpn_contents wlc, ' ||
776: ' mtl_serial_numbers msn, ' ||
777: ' mtl_serial_numbers_temp msnt ' ||
827: wlpn.content_volume_uom_code,
828: NVL(wlpn.content_volume, 0),
829: milk.concatenated_segments,
830: wlpn.lpn_context
831: FROM wms_license_plate_numbers wlpn,
832: mtl_item_locations_kfv milk,
833: wms_lpn_contents wlc,
834: mtl_serial_numbers msn,
835: mtl_serial_numbers_temp msnt
864: wlpn.content_volume_uom_code,
865: NVL(wlpn.content_volume, 0),
866: milk.concatenated_segments,
867: wlpn.lpn_context
868: FROM wms_license_plate_numbers wlpn,
869: mtl_item_locations_kfv milk,
870: wms_lpn_contents wlc,
871: mtl_serial_numbers msn,
872: mtl_serial_numbers_temp msnt,
930: wlpn.content_volume_uom_code,
931: NVL(wlpn.content_volume, 0),
932: milk.concatenated_segments,
933: wlpn.lpn_context
934: FROM wms_license_plate_numbers wlpn,
935: mtl_item_locations_kfv milk,
936: wms_lpn_contents wlc,
937: mtl_serial_numbers msn,
938: mtl_serial_numbers_temp msnt
964: wlpn.content_volume_uom_code,
965: NVL(wlpn.content_volume, 0),
966: milk.concatenated_segments,
967: wlpn.lpn_context
968: FROM wms_license_plate_numbers wlpn,
969: mtl_item_locations_kfv milk,
970: wms_lpn_contents wlc,
971: mtl_serial_numbers msn,
972: mtl_serial_numbers_temp msnt,
1027: wlpn.content_volume_uom_code,
1028: NVL(wlpn.content_volume, 0),
1029: milk.concatenated_segments,
1030: wlpn.lpn_context
1031: FROM wms_license_plate_numbers wlpn,
1032: mtl_item_locations_kfv milk,
1033: wms_lpn_contents wlc,
1034: mtl_serial_numbers msn,
1035: mtl_serial_numbers_temp msnt
1062: wlpn.content_volume_uom_code,
1063: NVL(wlpn.content_volume, 0),
1064: milk.concatenated_segments,
1065: wlpn.lpn_context
1066: FROM wms_license_plate_numbers wlpn,
1067: mtl_item_locations_kfv milk,
1068: wms_lpn_contents wlc,
1069: mtl_serial_numbers msn,
1070: mtl_serial_numbers_temp msnt,
1126: NVL(content_volume, 0),
1127: lpn_context -- Added for resolution of Bug# 4349304, The LPN Context is required by the LOVs called
1128: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1129: --organization, whether the LPN is "Issued out of Stores".
1130: FROM wms_license_plate_numbers
1131: WHERE organization_id = p_organization_id
1132: AND subinventory_code = p_subinventory_code
1133: AND lpn_context not in ( 4,6) --Bug#4267956.Added 6
1134: AND Nvl(locator_id, -99999) = Nvl(p_locator_id, -99999)
1162: NVL(wlpn.content_volume, 0),
1163: wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
1164: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1165: --organization, whether the LPN is "Issued out of Stores".
1166: FROM wms_license_plate_numbers wlpn,
1167: mtl_physical_inventory_tags mpit
1168: WHERE wlpn.organization_id = p_organization_id
1169: AND wlpn.subinventory_code = p_subinventory_code
1170: -- Bug# 1609449
1232: NVL(content_volume, 0)
1233: lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
1234: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1235: --organization, whether the LPN is "Issued out of Stores".
1236: FROM wms_license_plate_numbers
1237: WHERE organization_id = p_organization_id
1238: AND subinventory_code = p_subinventory_code
1239: AND lpn_context not in ( 4,6) --Bug#4267956.Added 6
1240: AND Nvl(locator_id, -99999) = Nvl(p_locator_id, -99999)
1269: NVL(wlpn.content_volume, 0),
1270: wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
1271: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
1272: --organization, whether the LPN is "Issued out of Stores".
1273: FROM wms_license_plate_numbers wlpn,
1274: mtl_physical_inventory_tags mpit
1275: WHERE wlpn.organization_id = p_organization_id
1276: AND wlpn.subinventory_code = p_subinventory_code
1277: -- Bug# 1609449
1345: gross_weight_uom_code,
1346: NVL(gross_weight, 0),
1347: content_volume_uom_code,
1348: NVL(content_volume, 0)
1349: FROM wms_license_plate_numbers wlpn
1350: WHERE wlpn.organization_id = p_org_id
1351: AND wlpn.lpn_id = p_orig_lpn_id
1352:
1353: UNION
1366: wlpn.gross_weight_uom_code,
1367: NVL(wlpn.gross_weight, 0),
1368: wlpn.content_volume_uom_code,
1369: NVL(wlpn.content_volume, 0)
1370: FROM wms_license_plate_numbers wlpn
1371: --,mtl_item_locations mil
1372: WHERE wlpn.organization_id = p_org_id
1373: AND wlpn.license_plate_number LIKE (p_lpn)
1374: AND (wlpn.lpn_context = 5
1467: NVL(wlpn.gross_weight, 0),
1468: wlpn.content_volume_uom_code,
1469: NVL(wlpn.content_volume, 0),
1470: wlpn.lpn_context --Added for bug#4202068.
1471: FROM wms_license_plate_numbers wlpn
1472: WHERE wlpn.organization_id = p_org_id
1473: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1474: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1475: NVL(wlpn.parent_lpn_id, 0))
1493: NVL(wlpn.gross_weight, 0),
1494: wlpn.content_volume_uom_code,
1495: NVL(wlpn.content_volume, 0),
1496: wlpn.lpn_context --Added for bug#4202068.
1497: FROM wms_license_plate_numbers wlpn
1498: WHERE wlpn.organization_id = p_org_id
1499: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1500: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1501: NVL(wlpn.parent_lpn_id, 0))
1538: NVL(wlpn.gross_weight, 0),
1539: wlpn.content_volume_uom_code,
1540: NVL(wlpn.content_volume, 0),
1541: wlpn.lpn_context --Added for bug#4202068.
1542: FROM wms_license_plate_numbers wlpn
1543: WHERE wlpn.organization_id = p_org_id
1544: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1545: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1546: NVL(wlpn.parent_lpn_id, 0))
1608: NVL(wlpn.gross_weight, 0),
1609: wlpn.content_volume_uom_code,
1610: NVL(wlpn.content_volume, 0),
1611: wlpn.lpn_context --Added for bug#4202068.
1612: FROM wms_license_plate_numbers wlpn
1613: WHERE wlpn.organization_id = p_org_id
1614: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1615: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1616: NVL(wlpn.parent_lpn_id, 0))
1634: NVL(wlpn.gross_weight, 0),
1635: content_volume_uom_code,
1636: NVL(wlpn.content_volume, 0),
1637: wlpn.lpn_context --Added for bug#4202068.
1638: FROM wms_license_plate_numbers wlpn,
1639: mtl_item_locations mil
1640: WHERE wlpn.organization_id = p_org_id
1641: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1642: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1699: NVL(wlpn.gross_weight, 0),
1700: content_volume_uom_code,
1701: NVL(wlpn.content_volume, 0),
1702: wlpn.lpn_context --Added for bug#4202068.
1703: FROM wms_license_plate_numbers wlpn,
1704: mtl_item_locations mil
1705: WHERE wlpn.organization_id = p_org_id
1706: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1707: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1801: NVL(wlpn.gross_weight, 0),
1802: wlpn.content_volume_uom_code,
1803: NVL(wlpn.content_volume, 0),
1804: wlpn.lpn_context
1805: FROM wms_license_plate_numbers wlpn
1806: WHERE wlpn.organization_id = p_org_id
1807: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1808: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
1809: AND wlpn.license_plate_number LIKE (p_lpn || '%')
1824: NVL(wlpn.gross_weight, 0),
1825: wlpn.content_volume_uom_code,
1826: NVL(wlpn.content_volume, 0),
1827: wlpn.lpn_context
1828: FROM wms_license_plate_numbers wlpn
1829: WHERE wlpn.organization_id = p_org_id
1830: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1831: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
1832: AND wlpn.license_plate_number LIKE (p_lpn || '%')
1857: NVL(wlpn.gross_weight, 0),
1858: wlpn.content_volume_uom_code,
1859: NVL(wlpn.content_volume, 0) ,
1860: wlpn.lpn_context
1861: FROM wms_license_plate_numbers wlpn
1862: WHERE wlpn.organization_id = p_org_id
1863: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1864: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
1865: AND wlpn.license_plate_number LIKE (p_lpn || '%')
1913: NVL(wlpn.gross_weight, 0),
1914: wlpn.content_volume_uom_code,
1915: NVL(wlpn.content_volume, 0),
1916: wlpn.lpn_context --Added for bug#4202068.
1917: FROM wms_license_plate_numbers wlpn
1918: WHERE wlpn.organization_id = p_org_id
1919: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1920: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1921: NVL(wlpn.parent_lpn_id, 0))
1939: NVL(wlpn.gross_weight, 0),
1940: wlpn.content_volume_uom_code,
1941: NVL(wlpn.content_volume, 0),
1942: wlpn.lpn_context --Added for bug#4202068.
1943: FROM wms_license_plate_numbers wlpn
1944: WHERE wlpn.organization_id = p_org_id
1945: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1946: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
1947: NVL(wlpn.parent_lpn_id, 0))
1992: NVL(wlpn.gross_weight, 0),
1993: wlpn.content_volume_uom_code,
1994: NVL(wlpn.content_volume, 0),
1995: wlpn.lpn_context --Added for bug#4202068.
1996: FROM wms_license_plate_numbers wlpn
1997: WHERE wlpn.organization_id = p_org_id
1998: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
1999: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2000: NVL(wlpn.parent_lpn_id, 0))
2067: NVL(wlpn.gross_weight, 0),
2068: wlpn.content_volume_uom_code,
2069: NVL(wlpn.content_volume, 0),
2070: wlpn.lpn_context --Added for bug#4202068.
2071: FROM wms_license_plate_numbers wlpn
2072: WHERE wlpn.organization_id = p_org_id
2073: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2074: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2075: NVL(wlpn.parent_lpn_id, 0))
2093: NVL(wlpn.gross_weight, 0),
2094: wlpn.content_volume_uom_code,
2095: NVL(wlpn.content_volume, 0),
2096: wlpn.lpn_context --Added for bug#4202068.
2097: FROM wms_license_plate_numbers wlpn,
2098: mtl_item_locations mil
2099: WHERE wlpn.organization_id = p_org_id
2100: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2101: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2150: NVL(wlpn.gross_weight, 0),
2151: wlpn.content_volume_uom_code,
2152: NVL(wlpn.content_volume, 0),
2153: wlpn.lpn_context --Added for bug#4202068.
2154: FROM wms_license_plate_numbers wlpn,
2155: mtl_item_locations mil
2156: WHERE wlpn.organization_id = p_org_id
2157: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2158: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id),
2338: NVL(gross_weight, 0),
2339: content_volume_uom_code,
2340: NVL(content_volume, 0),
2341: wlpn.lpn_context --Added for bug#4202068.
2342: FROM wms_license_plate_numbers wlpn
2343: WHERE
2344: wlpn.organization_id = p_org_id
2345: AND (wlpn.lpn_context = p_context
2346: OR (p_context = 0
2377: gross_weight_uom_code,
2378: NVL(gross_weight, 0),
2379: content_volume_uom_code,
2380: NVL(content_volume, 0)
2381: FROM wms_license_plate_numbers wlpn
2382: WHERE wlpn.organization_id = p_org_id
2383: AND NVL(subinventory_code, '@') = NVL(p_sub, NVL(subinventory_code, '@'))
2384: AND NVL(locator_id, '0') = NVL(TO_NUMBER(p_loc_id), NVL(locator_id, '0'))
2385: AND NOT lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2395: IS
2396: l_temp_num NUMBER;
2397: CURSOR child_lpn_cursor IS
2398: SELECT lpn_id
2399: FROM wms_license_plate_numbers
2400: WHERE parent_lpn_id = p_lpn_id;
2401:
2402: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2403: BEGIN
2429: IF (p_dynamic_entry_flag = 1) THEN -- Dynamic entries are allowed
2430: -- Select all LPN's which exist in the given org, sub, loc
2431: SELECT COUNT(*)
2432: INTO l_count
2433: FROM wms_license_plate_numbers
2434: WHERE organization_id = p_organization_id
2435: AND subinventory_code = p_subinventory_code
2436: AND Nvl(locator_id, -99999) = Nvl(p_locator_id, -99999)
2437: AND license_plate_number = p_lpn;
2447: ELSE -- Dynamic entries are not allowed
2448: -- Select only LPN's that exist in table MTL_PHYSICAL_INVENTORY_TAGS
2449: SELECT COUNT(*)
2450: INTO l_count
2451: FROM wms_license_plate_numbers wlpn,
2452: mtl_physical_inventory_tags mpit
2453: WHERE wlpn.organization_id = p_organization_id
2454: AND wlpn.subinventory_code = p_subinventory_code
2455: AND Nvl(wlpn.locator_id, -99999) = Nvl(p_locator_id, -99999)
2487: IF (p_unscheduled_entry = 1) THEN -- Unscheduled entries are allowed
2488: -- Select all LPN's which exist in the given org, sub, loc
2489: SELECT COUNT(*)
2490: INTO l_count
2491: FROM wms_license_plate_numbers
2492: WHERE organization_id = p_organization_id
2493: AND subinventory_code = p_subinventory_code
2494: AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
2495: AND license_plate_number = p_lpn;
2505: ELSE -- Unscheduled entries are not allowed
2506: -- Select only LPN's that exist in table MTL_CYCLE_COUNT_ENTRIES
2507: SELECT COUNT(*)
2508: INTO l_count
2509: FROM wms_license_plate_numbers wlpn,
2510: mtl_cycle_count_entries mcce
2511: WHERE wlpn.organization_id = p_organization_id
2512: AND wlpn.subinventory_code = p_subinventory_code
2513: AND NVL(wlpn.locator_id, -99999) = NVL(p_locator_id, -99999)
2593: NVL(gross_weight, 0),
2594: content_volume_uom_code,
2595: NVL(content_volume, 0)
2596: INTO l_lpn_record
2597: FROM wms_license_plate_numbers
2598: WHERE license_plate_number = p_lpn
2599: AND organization_id = p_organization_id
2600: ORDER BY license_plate_number;
2601:
2665: NVL(a.gross_weight, 0),
2666: a.content_volume_uom_code,
2667: NVL(a.content_volume, 0),
2668: nvl(rec_count.lpn_content_count, 0)
2669: FROM wms_license_plate_numbers a,
2670: mtl_txn_request_lines b,
2671: (SELECT count(*) lpn_content_count,grouped_contents.lpn_id
2672: FROM (SELECT mtrl.lpn_id lpn_id, -- Need extra grouping to group
2673: mtrl.inventory_item_id item_id,
2674: mtrl.revision revision
2675: --BUG 3358288: Use MOL to calculate the count instead of
2676: --using WLC because there may be items there does not
2677: --require inspection
2678: FROM wms_license_plate_numbers wlpn, mtl_txn_request_lines mtrl
2679: WHERE wlpn.license_plate_number LIKE (p_lpn)
2680: AND mtrl.lpn_id = wlpn.lpn_id
2681: AND mtrl.inspection_status = 1
2682: AND mtrl.wms_process_flag = 1
2727: OPEN x_lpn_lov FOR
2728: select lpnc.parent_lpn_id lpn_id,
2729: lpn.license_plate_number lpn,
2730: sum(lpnc.quantity) quantity
2731: from wms_lpn_contents lpnc, wms_license_plate_numbers lpn
2732: where lpn.organization_id = p_organization_id
2733: and lpnc.inventory_item_id = p_inv_item_id
2734: and lpnc.parent_lpn_id = lpn.lpn_id
2735: and nvl(lpn.SUBINVENTORY_CODE,'@@@') = nvl(p_subinventory_code,'@@@')
2794: lpn.source_header_id,
2795: rsh.shipment_num,
2796: count_row.n,
2797: rsh.shipment_header_id
2798: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2799: (SELECT COUNT(*) n
2800: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2801: --WHERE lpn.license_plate_number LIKE (p_lpn)--Bug 3090000
2802: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2796: count_row.n,
2797: rsh.shipment_header_id
2798: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2799: (SELECT COUNT(*) n
2800: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2801: --WHERE lpn.license_plate_number LIKE (p_lpn)--Bug 3090000
2802: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2803: AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2804: AND (lpn.source_header_id = rsh.shipment_header_id
2809: AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2810: AND (lpn.source_header_id = rsh.shipment_header_id
2811: OR lpn.source_name = rsh.shipment_num)
2812: -- Nested LPN changes , For I Patchset donot show nested LPNs
2813: AND lpn.lpn_id NOT IN (SELECT parent_lpn_id FROM wms_license_plate_numbers WHERE parent_lpn_id = lpn.lpn_id )
2814: AND lpn.parent_lpn_id IS NULL;
2815: ELSIF p_mode = 'E' THEN
2816:
2817: -- As Part of per bug 3435093 if shipment_header_id is not null
2839: wlpn1.source_header_id,
2840: rsh.shipment_num,
2841: 1,--This is a dummy value. Actually cound will be calculated in validate_from_lpn
2842: rsh.shipment_header_id
2843: FROM wms_license_plate_numbers wlpn1, rcv_shipment_headers rsh
2844: WHERE rsh.shipment_header_id = p_shipment_header_id
2845: AND ((wlpn1.lpn_context = 6 AND wlpn1.organization_id = rsh.organization_id) OR
2846: (wlpn1.lpn_context = 7 AND wlpn1.organization_id = rsh.ship_to_org_id))
2847: AND wlpn1.source_name = rsh.shipment_num
2846: (wlpn1.lpn_context = 7 AND wlpn1.organization_id = rsh.ship_to_org_id))
2847: AND wlpn1.source_name = rsh.shipment_num
2848: AND wlpn1.license_plate_number LIKE (p_lpn)
2849: and exists (SELECT wlpn2.lpn_id
2850: FROM wms_license_plate_numbers wlpn2
2851: START WITH wlpn2.lpn_id = wlpn1.lpn_id
2852: CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
2853: INTERSECT
2854: SELECT asn_lpn_id
2884: lpn.source_header_id,
2885: rsh.shipment_num,
2886: count_row.n,
2887: rsh.shipment_header_id
2888: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2889: (SELECT COUNT(*) n
2890: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2891: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2892: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2886: count_row.n,
2887: rsh.shipment_header_id
2888: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2889: (SELECT COUNT(*) n
2890: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2891: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2892: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2893: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2894: AND (lpn.source_header_id = rsh.shipment_header_id
2931: lpn.source_header_id,
2932: rsh.shipment_num,
2933: count_row.n,
2934: rsh.shipment_header_id
2935: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2936: ( SELECT COUNT(*) n
2937: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2938: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2939: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2933: count_row.n,
2934: rsh.shipment_header_id
2935: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2936: ( SELECT COUNT(*) n
2937: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2938: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2939: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2940: AND rsh.shipment_header_id = p_shipment_header_id
2941: AND (lpn.source_header_id = rsh.shipment_header_id
2970: lpn.source_header_id,
2971: rsh.shipment_num,
2972: count_row.n,
2973: rsh.shipment_header_id
2974: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2975: ( SELECT COUNT(*) n
2976: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2977: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2978: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2972: count_row.n,
2973: rsh.shipment_header_id
2974: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
2975: ( SELECT COUNT(*) n
2976: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
2977: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
2978: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2979: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2980: AND (lpn.source_header_id = rsh.shipment_header_id
3011: lpn.source_header_id,
3012: rsh.shipment_num,
3013: count_row.n,
3014: rsh.shipment_header_id
3015: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3016: ( SELECT COUNT(*) n
3017: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3018: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3019: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3013: count_row.n,
3014: rsh.shipment_header_id
3015: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3016: ( SELECT COUNT(*) n
3017: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3018: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3019: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3020: AND rsh.shipment_header_id = p_shipment_header_id
3021: AND (lpn.source_header_id = rsh.shipment_header_id
3051: lpn.source_header_id,
3052: rsh.shipment_num,
3053: count_row.n,
3054: rsh.shipment_header_id
3055: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3056: ( SELECT COUNT(*) n
3057: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3058: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3059: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3053: count_row.n,
3054: rsh.shipment_header_id
3055: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh,
3056: ( SELECT COUNT(*) n
3057: FROM wms_license_plate_numbers lpn, rcv_shipment_headers rsh
3058: --WHERE lpn.license_plate_number LIKE (p_lpn) --Bug 3090000
3059: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3060: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3061: AND (lpn.source_header_id = rsh.shipment_header_id
3113: INV_PROJECT.GET_PROJECT_ID,
3114: INV_PROJECT.GET_PROJECT_NUMBER,
3115: INV_PROJECT.GET_TASK_ID,
3116: INV_PROJECT.GET_TASK_NUMBER
3117: FROM wms_license_plate_numbers wlpn,
3118: wms_lpn_contents wlc,
3119: mtl_item_locations mil
3120: WHERE (mil.inventory_location_id = wlpn.locator_id
3121: AND wlpn.locator_id IS NOT NULL)
3169: INV_PROJECT.GET_PROJECT_ID,
3170: INV_PROJECT.GET_PROJECT_NUMBER,
3171: INV_PROJECT.GET_TASK_ID,
3172: INV_PROJECT.GET_TASK_NUMBER
3173: FROM wms_license_plate_numbers wlpn,
3174: mtl_item_locations mil
3175: WHERE mil.inventory_location_id(+) = wlpn.locator_id --OUTER JOIN is added for bug 3876495
3176: AND wlpn.license_plate_number LIKE (p_lpn)
3177: AND wlpn.organization_id = p_organization_id
3208: wlpn.gross_weight_uom_code,
3209: NVL(wlpn.gross_weight, 0),
3210: wlpn.content_volume_uom_code,
3211: NVL(wlpn.content_volume, 0)
3212: FROM wms_license_plate_numbers wlpn
3213: WHERE wlpn.license_plate_number LIKE (p_lpn)
3214: AND wlpn.organization_id = p_org_id
3215: and wlpn.lpn_context = 3
3216: AND exists (
3236: wlpn.gross_weight_uom_code,
3237: NVL(wlpn.gross_weight, 0),
3238: wlpn.content_volume_uom_code,
3239: NVL(wlpn.content_volume, 0)
3240: FROM wms_license_plate_numbers wlpn
3241: WHERE wlpn.license_plate_number LIKE (p_lpn)
3242: AND wlpn.organization_id = p_org_id
3243: and exists
3244: ( select inventory_location_id
3291: wlpn.gross_weight_uom_code,
3292: NVL(wlpn.gross_weight, 0),
3293: wlpn.content_volume_uom_code,
3294: NVL(wlpn.content_volume, 0)
3295: FROM wms_license_plate_numbers wlpn
3296: WHERE wlpn.license_plate_number LIKE (p_lpn)
3297: AND wlpn.organization_id = p_org_id
3298: and ( lpn_context = 5 or lpn_id = p_from_lpn_id )
3299: ORDER BY 1;
3313: gross_weight_uom_code,
3314: NVL(gross_weight, 0),
3315: content_volume_uom_code,
3316: NVL(content_volume, 0)
3317: FROM wms_license_plate_numbers wlpn
3318: WHERE license_plate_number LIKE (p_lpn)
3319: AND organization_id = p_org_id
3320: AND lpn_context = 3
3321: and exists (
3341: gross_weight_uom_code,
3342: NVL(gross_weight, 0),
3343: content_volume_uom_code,
3344: NVL(content_volume, 0)
3345: FROM wms_license_plate_numbers wlpn
3346: WHERE license_plate_number LIKE (p_lpn)
3347: and organization_id = p_org_id
3348: AND lpn_context = 1
3349: and exists
3396: gross_weight_uom_code,
3397: NVL(gross_weight, 0),
3398: content_volume_uom_code,
3399: NVL(content_volume, 0)
3400: FROM wms_license_plate_numbers wlpn
3401: WHERE license_plate_number LIKE (p_lpn)
3402: and organization_id = p_org_id
3403: and (lpn_context = 5 or lpn_id = p_from_lpn_id )
3404: ORDER BY 1;
3449: NVL(content_volume, 0),
3450: lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3451: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3452: --organization, whether the LPN is "Issued out of Stores".
3453: FROM wms_license_plate_numbers
3454: WHERE organization_id = p_organization_id
3455: AND (subinventory_code = p_subinventory_code OR
3456: l_container_discrepancy_option = 1)
3457: AND (NVL(locator_id, -99999) = NVL(p_locator_id, -99999) OR
3487: NVL(wlpn.content_volume, 0),
3488: wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3489: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3490: --organization, whether the LPN is "Issued out of Stores".
3491: FROM wms_license_plate_numbers wlpn,
3492: mtl_cycle_count_entries mcce
3493: WHERE wlpn.organization_id = p_organization_id
3494: AND (wlpn.subinventory_code = p_subinventory_code OR
3495: l_container_discrepancy_option = 1)
3563: NVL(content_volume, 0),
3564: lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3565: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3566: --organization, whether the LPN is "Issued out of Stores".
3567: FROM wms_license_plate_numbers
3568: WHERE organization_id = p_organization_id
3569: AND (subinventory_code = p_subinventory_code OR
3570: l_container_discrepancy_option = 1)
3571: AND (NVL(locator_id, -99999) = NVL(p_locator_id, -99999) OR
3601: NVL(wlpn.content_volume, 0),
3602: wlpn.lpn_context -- Added for resolution of Bug# 4349304. The LPN Context is required by the LOVs called
3603: -- by the Cycle Count and Physical Count pages to validate whether the LPN belongs to same
3604: --organization, whether the LPN is "Issued out of Stores".
3605: FROM wms_license_plate_numbers wlpn,
3606: mtl_cycle_count_entries mcce
3607: WHERE wlpn.organization_id = p_organization_id
3608: AND (wlpn.subinventory_code = p_subinventory_code OR
3609: l_container_discrepancy_option = 1)
3654: wlpn.locator_id,
3655: -- PJM-WMS Integration
3656: INV_PROJECT.GET_LOCSEGS(mil.inventory_location_id,p_org_id)
3657: FROM mtl_item_locations mil,-- -PJM-WMS Integration
3658: wms_license_plate_numbers wlpn
3659: WHERE mil.inventory_location_id = wlpn.locator_id
3660: AND mil.organization_id = wlpn.organization_id
3661: AND mil.segment19 is null
3662: -- bug 2267845 fix. checking this conditon
3706: wlpn.gross_weight_uom_code,
3707: NVL(wlpn.gross_weight, 0),
3708: wlpn.content_volume_uom_code,
3709: NVL(wlpn.content_volume, 0)*/
3710: FROM wms_license_plate_numbers wlpn,
3711: mtl_system_items_kfv msik
3712: WHERE wlpn.organization_id = p_org_id
3713: AND wlpn.inventory_item_id IS NOT NULL
3714: AND msik.inventory_item_id = wlpn.inventory_item_id
3804: x_newLPN,
3805: l_flag1,
3806: l_locator_id,
3807: x_context
3808: FROM wms_license_plate_numbers
3809: WHERE license_plate_number = p_lpn;
3810:
3811: EXCEPTION
3812: WHEN no_data_found THEN
3832: select 1,
3833: milk.concatenated_segments
3834: INTO l_flag2,
3835: x_concat_segments
3836: FROM wms_license_plate_numbers w,
3837: mtl_item_locations_kfv milk
3838: WHERE w.license_plate_number = p_lpn
3839: AND w.locator_id = milk.inventory_location_id
3840: AND w.organization_id = milk.organization_id;
3947: x_project_id,
3948: x_project_number,
3949: x_task_id,
3950: x_task_number
3951: FROM wms_license_plate_numbers
3952: WHERE license_plate_number = p_lpn;
3953:
3954: EXCEPTION
3955: WHEN no_data_found THEN
3995: gross_weight_uom_code,
3996: NVL(gross_weight, 0),
3997: content_volume_uom_code,
3998: NVL(content_volume, 0)
3999: FROM wms_license_plate_numbers
4000: WHERE license_plate_number LIKE (p_lpn)
4001: AND organization_id = p_organization_id
4002: AND lpn_context = NVL(TO_NUMBER(p_context), lpn_context);
4003:
4027: wlpn.gross_weight_uom_code,
4028: NVL(wlpn.gross_weight, 0),
4029: wlpn.content_volume_uom_code,
4030: NVL(wlpn.content_volume, 0)
4031: FROM wms_license_plate_numbers wlpn
4032: WHERE wlpn.license_plate_number LIKE (p_lpn)
4033: AND wlpn.organization_id = p_org_id
4034: AND WMS_RETURN_SV.GET_LPN_MARKED_STATUS(wlpn.lpn_id, wlpn.organization_id)='FULL'
4035: UNION ALL
4047: wlpn.gross_weight_uom_code,
4048: NVL(wlpn.gross_weight, 0),
4049: wlpn.content_volume_uom_code,
4050: NVL(wlpn.content_volume, 0)
4051: FROM wms_license_plate_numbers wlpn
4052: WHERE wlpn.license_plate_number LIKE (p_lpn)
4053: AND wlpn.organization_id = p_org_id
4054: AND WMS_RETURN_SV.GET_LPN_MARKED_STATUS(wlpn.lpn_id, wlpn.organization_id)='PARTIAL'
4055: ORDER BY 1;
4099: , count_row.n
4100: FROM wsh_delivery_details_ob_grp_v wdd
4101: , wsh_delivery_assignments_v wda
4102: , wsh_delivery_details_ob_grp_v wdd1
4103: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4104: FROM wsh_delivery_details_ob_grp_v wdd
4105: , wms_license_plate_numbers wlpn
4106: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4107: FROM wsh_delivery_assignments_v wda
4101: , wsh_delivery_assignments_v wda
4102: , wsh_delivery_details_ob_grp_v wdd1
4103: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4104: FROM wsh_delivery_details_ob_grp_v wdd
4105: , wms_license_plate_numbers wlpn
4106: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4107: FROM wsh_delivery_assignments_v wda
4108: , wsh_delivery_details_ob_grp_v wdd
4109: WHERE wda.delivery_detail_id in (select delivery_detail_id
4128: , count_row.n
4129: FROM wsh_delivery_details_ob_grp_v wdd
4130: , wsh_delivery_assignments_v wda
4131: , wsh_delivery_details_ob_grp_v wdd1
4132: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4133: FROM wsh_delivery_details_ob_grp_v wdd
4134: , wms_license_plate_numbers wlpn
4135: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4136: FROM wsh_delivery_assignments_v wda
4130: , wsh_delivery_assignments_v wda
4131: , wsh_delivery_details_ob_grp_v wdd1
4132: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4133: FROM wsh_delivery_details_ob_grp_v wdd
4134: , wms_license_plate_numbers wlpn
4135: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4136: FROM wsh_delivery_assignments_v wda
4137: , wsh_delivery_details_ob_grp_v wdd
4138: WHERE wda.delivery_detail_id in (select delivery_detail_id
4155: OPEN x_lpn_lov FOR
4156: SELECT distinct wln.license_plate_number
4157: , wln.lpn_id
4158: , 1
4159: FROM wms_license_plate_numbers wln,
4160: wsh_delivery_details_ob_grp_v wdd
4161: WHERE wln.lpn_context= 6
4162: AND wln.lpn_id = wdd.lpn_id
4163: AND wln.license_plate_number LIKE (p_lpn)
4175: , count_row.n
4176: FROM wsh_delivery_details_ob_grp_v wdd
4177: , wsh_delivery_assignments_v wda
4178: , wsh_delivery_details_ob_grp_v wdd1
4179: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4180: FROM wsh_delivery_details_ob_grp_v wdd
4181: , wms_license_plate_numbers wlpn
4182: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4183: FROM wsh_delivery_assignments_v wda
4177: , wsh_delivery_assignments_v wda
4178: , wsh_delivery_details_ob_grp_v wdd1
4179: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4180: FROM wsh_delivery_details_ob_grp_v wdd
4181: , wms_license_plate_numbers wlpn
4182: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4183: FROM wsh_delivery_assignments_v wda
4184: , wsh_delivery_details_ob_grp_v wdd
4185: WHERE wda.delivery_detail_id in (select delivery_detail_id
4210: , count_row.n
4211: FROM wsh_delivery_details_ob_grp_v wdd
4212: , wsh_delivery_assignments_v wda
4213: , wsh_delivery_details_ob_grp_v wdd1
4214: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4215: FROM wsh_delivery_details_ob_grp_v wdd
4216: , wms_license_plate_numbers wlpn
4217: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4218: FROM wsh_delivery_assignments_v wda
4212: , wsh_delivery_assignments_v wda
4213: , wsh_delivery_details_ob_grp_v wdd1
4214: , wms_license_plate_numbers wlpn, (SELECT count(*) n
4215: FROM wsh_delivery_details_ob_grp_v wdd
4216: , wms_license_plate_numbers wlpn
4217: WHERE wdd.lpn_id in (SELECT wdd.lpn_id
4218: FROM wsh_delivery_assignments_v wda
4219: , wsh_delivery_details_ob_grp_v wdd
4220: WHERE wda.delivery_detail_id in (select delivery_detail_id
4269: NVL(gross_weight, 0),
4270: content_volume_uom_code,
4271: NVL(content_volume, 0),
4272: lpn_context --Added for Bug#6504032
4273: FROM wms_license_plate_numbers wlpn
4274: WHERE wlpn.organization_id = p_org_id
4275: AND wlpn.license_plate_number LIKE (p_lpn)
4276: AND wlpn.lpn_context IN (1, 2, 3 , 5, 11); --Inventory, pregenerated, picked contexts /*Resides in WIP(2) added for bug#3953941*/
4277: -- Added 3 to pick LPNS in status 'Resides n Receiving' Bug 5501058
4297: outer.gross_weight_uom_code,
4298: NVL(outer.gross_weight, 0),
4299: outer.content_volume_uom_code,
4300: NVL(outer.content_volume, 0)
4301: from wms_license_plate_numbers outer, wms_license_plate_numbers inner,
4302: mtl_item_locations_kfv milk
4303: where inner.outermost_lpn_id <> inner.lpn_id
4304: AND inner.outermost_lpn_id = outer.lpn_id
4305: AND outer.locator_id = milk.inventory_location_id(+)
4363: gross_weight_uom_code,
4364: NVL(gross_weight, 0),
4365: content_volume_uom_code,
4366: NVL(content_volume, 0)
4367: FROM wms_license_plate_numbers wlpn
4368: WHERE wlpn.organization_id = p_org_id
4369: AND wlpn.license_plate_number LIKE (p_lpn)
4370: AND wlpn.subinventory_code = nvl(p_subinventory,wlpn.subinventory_Code)
4371: AND wlpn.locator_id = decode(p_locator,0,wlpn.locator_id,p_locator)
4399: NVL(wlpn.gross_weight, 0),
4400: wlpn.content_volume_uom_code,
4401: NVL(wlpn.content_volume, 0),
4402: wdd.delivery_detail_id
4403: FROM wms_license_plate_numbers wlpn,
4404: wsh_delivery_details wdd
4405: WHERE wlpn.organization_id = p_organization_id
4406: AND wlpn.lpn_context = 11
4407: AND wlpn.license_plate_number LIKE (p_lpn_id)
4448: wlpn.gross_weight_uom_code,
4449: NVL(wlpn.gross_weight, 0),
4450: wlpn.content_volume_uom_code,
4451: NVL(wlpn.content_volume, 0)
4452: FROM wms_license_plate_numbers wlpn
4453: WHERE wlpn.organization_id = p_organization_id
4454: AND wlpn.lpn_id <> p_lpn_id
4455: AND (wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
4456: OR (wlpn.lpn_context = p_lpn_context
4457: AND ( (NOT EXISTS (SELECT 'LPN_HAS_MATERIAL'
4458: FROM wms_lpn_contents
4459: WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
4460: FROM
4461: wms_license_plate_numbers wlpn1
4462: START WITH
4463: wlpn1.lpn_id =
4464: wlpn.outermost_lpn_id
4465: CONNECT BY PRIOR
4474: AND wdt.task_type = 2
4475: AND wdt.status = 4
4476: AND wdt.person_id = p_employee_id
4477: AND mmtt.lpn_id IN (SELECT lpn_id
4478: FROM wms_license_plate_numbers
4479: START WITH lpn_id = wlpn.outermost_lpn_id
4480: CONNECT BY PRIOR lpn_id = parent_lpn_id
4481: )
4482: )
4541: wlpn.content_volume_uom_code,
4542: NVL (wlpn.content_volume, 0),
4543: milk.concatenated_segments,
4544: wlpn.lpn_context
4545: FROM wms_license_plate_numbers wlpn,
4546: mtl_item_locations_kfv milk,
4547: wms_putaway_group_tasks_gtmp wpgt
4548: WHERE wlpn.organization_id = TO_NUMBER (p_organization_id)
4549: AND wlpn.organization_id = milk.organization_id(+)