[Home] [Help]
30: l_where_clause VARCHAR2(7500) := '';
31: l_sql_stmt VARCHAR2(7500);
32: l_conc_seg varchar2(2000) := p_concatenated_segments;
33: -- Bug# 6747729
34: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
35: l_sql_stmt1 VARCHAR2(7500)
36: := 'select concatenated_segments,'
37: || 'msik.inventory_item_id, msik.description,'
38: || 'Nvl(revision_qty_control_code,1),'
77: || 'NVL(DUAL_UOM_DEVIATION_HIGH,0),'
78: || 'NVL(DUAL_UOM_DEVIATION_LOW,0),'
79: || 'stock_enabled_flag';
80: -- Bug# 6747729
81: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
82: l_sql_stmt_xref VARCHAR2(7500)
83: := 'select concatenated_segments,'
84: || 'msik.inventory_item_id, msik.description,'
85: || 'Nvl(revision_qty_control_code,1),'
132: -- Bug 4997004 sql Id 14813260 End
133: BEGIN
134: l_where_clause := p_where_clause;
135: -- Modified for Bug # 5472330
136: -- Changed mtl_system_items_kfv to mtl_system_items_vl
137:
138: l_sql_stmt := l_sql_stmt1 || ' from mtl_system_items_vl msik WHERE organization_id = ' || p_organization_id || ' AND concatenated_segments like :l_conc_seg ' || l_where_clause;
139: -- 1 effectivity control implies that the item is NOT effectivity
140: -- controlled.
134: l_where_clause := p_where_clause;
135: -- Modified for Bug # 5472330
136: -- Changed mtl_system_items_kfv to mtl_system_items_vl
137:
138: l_sql_stmt := l_sql_stmt1 || ' from mtl_system_items_vl msik WHERE organization_id = ' || p_organization_id || ' AND concatenated_segments like :l_conc_seg ' || l_where_clause;
139: -- 1 effectivity control implies that the item is NOT effectivity
140: -- controlled.
141:
142: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
139: -- 1 effectivity control implies that the item is NOT effectivity
140: -- controlled.
141:
142: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
143: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' ||
144: ' WHERE msik.organization_id = ' || p_organization_id ||
145: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
146: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type || val ||
147: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
249:
250: BEGIN
251: l_where_clause := p_where_clause;
252: IF (p_locator_id IS NOT NULL ) THEN
253: l_sql_stmt := l_sql_stmt1 || ' from mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd WHERE msik.organization_id = ' || p_organization_id || ' AND moqd.subinventory_code = ''' || p_subinventory_code /* Bug 5581528 */
254: || ''' AND moqd.locator_id = ' || p_locator_id ||
255: ' AND msik.concatenated_segments like :l_conc_seg ' || ' AND msik.organization_id = moqd.organization_id AND msik.inventory_item_id = moqd.inventory_item_id ' || l_where_clause;
256:
257: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
254: || ''' AND moqd.locator_id = ' || p_locator_id ||
255: ' AND msik.concatenated_segments like :l_conc_seg ' || ' AND msik.organization_id = moqd.organization_id AND msik.inventory_item_id = moqd.inventory_item_id ' || l_where_clause;
256:
257: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
258: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
259: ' WHERE msik.organization_id = ' || p_organization_id ||
260: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
261: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type ||''''||
262: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
263: , ''%'' ), ' || g_gtin_code_length || ' , ' || '''00000000000000'''||')' ||
264: ' AND (mcr.organization_id = msik.organization_id OR mcr.org_independent_flag = ' || '''Y''' || ')' || l_where_clause;
265:
266: ELSE
267: l_sql_stmt := l_sql_stmt1 || ' from mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd WHERE msik.organization_id = ' || p_organization_id || ' AND moqd.subinventory_code = ''' || p_subinventory_code /* Bug 5581528 */
268: || ''' AND msik.concatenated_segments like :l_conc_seg ' || ' AND msik.organization_id = moqd.organization_id AND msik.inventory_item_id = moqd.inventory_item_id ' || l_where_clause;
269:
270: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
271: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
267: l_sql_stmt := l_sql_stmt1 || ' from mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd WHERE msik.organization_id = ' || p_organization_id || ' AND moqd.subinventory_code = ''' || p_subinventory_code /* Bug 5581528 */
268: || ''' AND msik.concatenated_segments like :l_conc_seg ' || ' AND msik.organization_id = moqd.organization_id AND msik.inventory_item_id = moqd.inventory_item_id ' || l_where_clause;
269:
270: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
271: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
272: ' WHERE msik.organization_id = ' || p_organization_id ||
273: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
274: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type ||''''||
275: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
275: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
276: , ''%'' ), ' || g_gtin_code_length || ' , ' || '''00000000000000'''||')' ||
277: ' AND (mcr.organization_id = msik.organization_id OR mcr.org_independent_flag = ' || '''Y''' || ')' || l_where_clause;
278: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
279: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
280: ' WHERE msik.organization_id = ' || p_organization_id ||
281: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
282: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type ||''''||
283: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
356: NVL(SECONDARY_DEFAULT_IND,''),
357: NVL(TRACKING_QUANTITY_IND,'P'),
358: NVL(DUAL_UOM_DEVIATION_HIGH,0),
359: NVL(DUAL_UOM_DEVIATION_LOW,0)
360: FROM mtl_system_items_vl /* Bug 5581528 */
361: WHERE organization_id = p_organization_id
362: AND concatenated_segments LIKE nvl(p_concatenated_segments,concatenated_segments)
363: AND inventory_item_id in (select inventory_item_id from mtl_txn_request_lines where header_id =to_number(p_header_id))
364:
407: NVL(SECONDARY_DEFAULT_IND,''),
408: NVL(TRACKING_QUANTITY_IND,'P'),
409: NVL(DUAL_UOM_DEVIATION_HIGH,0),
410: NVL(DUAL_UOM_DEVIATION_LOW,0)
411: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
412: WHERE msik.organization_id = p_organization_id
413: AND msik.inventory_item_id = mcr.inventory_item_id
414: AND mcr.cross_reference_type = g_gtin_cross_ref_type
415: AND mcr.cross_reference LIKE l_cross_ref
468: NVL(msik.SECONDARY_DEFAULT_IND,''),
469: NVL(msik.TRACKING_QUANTITY_IND,'P'),
470: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
471: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
472: FROM mtl_system_items_vl msik /* Bug 5581528 */
473: WHERE msik.organization_id = p_organization_id
474: AND msik.mtl_transactions_enabled_flag = 'Y'
475: AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
476: FROM mtl_system_items_b msib
531: NVL(SECONDARY_DEFAULT_IND,''),
532: NVL(TRACKING_QUANTITY_IND,'P'),
533: NVL(DUAL_UOM_DEVIATION_HIGH,0),
534: NVL(DUAL_UOM_DEVIATION_LOW,0)
535: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
536: WHERE msik.organization_id = p_organization_id
537: AND msik.mtl_transactions_enabled_flag = 'Y'
538: AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
539: FROM mtl_system_items_b msib
766: NVL(msik.SECONDARY_DEFAULT_IND,''),
767: NVL(msik.TRACKING_QUANTITY_IND,'P'),
768: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
769: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
770: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
771: WHERE msik.lot_split_enabled = 'Y'
772: AND msik.concatenated_segments LIKE (p_concatenated_segments)
773: AND msik.organization_id = mln.organization_id
774: AND msik.inventory_item_id = mln.inventory_item_id
827: NVL(msik.SECONDARY_DEFAULT_IND,''),
828: NVL(msik.TRACKING_QUANTITY_IND,'P'),
829: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
830: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
831: FROM mtl_system_items_vl msik, /* Bug 5581528 */
832: mtl_lot_numbers mln,
833: mtl_cross_references mcr
834: WHERE msik.lot_split_enabled = 'Y'
835: AND msik.organization_id = mln.organization_id
895: NVL(msik.SECONDARY_DEFAULT_IND,''),
896: NVL(msik.TRACKING_QUANTITY_IND,'P'),
897: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
898: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
899: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
900: WHERE msik.lot_merge_enabled = 'Y'
901: AND msik.concatenated_segments LIKE (p_concatenated_segments)
902: AND msik.organization_id = mln.organization_id
903: AND msik.inventory_item_id = mln.inventory_item_id
956: NVL(msik.SECONDARY_DEFAULT_IND,''),
957: NVL(msik.TRACKING_QUANTITY_IND,'P'),
958: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
959: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
960: FROM mtl_system_items_vl msik, /* Bug 5581528 */
961: mtl_lot_numbers mln,
962: mtl_cross_references mcr
963: WHERE msik.lot_merge_enabled = 'Y'
964: AND msik.organization_id = mln.organization_id
1024: NVL(msik.SECONDARY_DEFAULT_IND,''),
1025: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1026: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1027: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1028: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
1029: WHERE msik.concatenated_segments LIKE (p_concatenated_segments)
1030: AND msik.organization_id = mln.organization_id
1031: AND msik.inventory_item_id = mln.inventory_item_id
1032: AND mln.lot_number = p_lot_number
1083: NVL(msik.SECONDARY_DEFAULT_IND,''),
1084: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1085: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1086: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1087: FROM mtl_system_items_vl msik, /* Bug 5581528 */
1088: mtl_lot_numbers mln,
1089: mtl_cross_references mcr
1090: WHERE msik.organization_id = mln.organization_id
1091: AND msik.inventory_item_id = mln.inventory_item_id
1147: NVL(msik.SECONDARY_DEFAULT_IND,''),
1148: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1149: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1150: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1151: FROM mtl_system_items_vl msik /* Bug 5581528 */
1152: WHERE msik.concatenated_segments LIKE (p_concatenated_segments)
1153: AND msik.lot_control_code = 2
1154: AND msik.organization_id = p_organization_id
1155:
1199: NVL(msik.SECONDARY_DEFAULT_IND,''),
1200: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1201: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1202: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1203: FROM mtl_system_items_vl msik, /* Bug 5581528 */
1204: mtl_cross_references mcr
1205: WHERE msik.lot_control_code = 2
1206: AND msik.organization_id = p_organization_id
1207: AND msik.inventory_item_id = mcr.inventory_item_id
1256: NVL(msik.SECONDARY_DEFAULT_IND,''),
1257: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1258: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1259: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1260: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
1261: WHERE msik.concatenated_segments LIKE (p_concatenated_segments)
1262: AND msik.organization_id = mln.organization_id
1263: AND msik.inventory_item_id = mln.inventory_item_id
1264: AND mln.lot_number = p_lot_number
1310: NVL(msik.SECONDARY_DEFAULT_IND,''),
1311: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1312: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1313: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1314: FROM mtl_system_items_vl msik, /* Bug 5581528 */
1315: mtl_lot_numbers mln,
1316: mtl_cross_references mcr
1317: WHERE msik.organization_id = mln.organization_id
1318: AND msik.inventory_item_id = mln.inventory_item_id
1377: , x_shelf_life_days
1378: , x_allowed_units_lookup_code
1379: , x_lot_status_enabled
1380: , x_default_lot_status_id
1381: FROM mtl_system_items_vl msik /* Bug 5581528 */
1382: WHERE msik.lot_split_enabled = 'Y'
1383: AND msik.organization_id = p_organization_id
1384: AND msik.inventory_item_id = p_inventory_item_id;
1385: ELSE
1406: , x_shelf_life_days
1407: , x_allowed_units_lookup_code
1408: , x_lot_status_enabled -- nsinghi bug#5475282
1409: , x_default_lot_status_id -- nsinghi bug#5475282
1410: FROM mtl_system_items_vl msik /* Bug 5581528 */
1411: WHERE msik.lot_merge_enabled = 'Y'
1412: AND msik.organization_id = p_organization_id
1413: AND msik.inventory_item_id = p_inventory_item_id;
1414: ELSIF p_transaction_type_id = inv_globals.g_type_inv_lot_translate -- Lot Translate 84 Added bug4096035
1434: , x_shelf_life_days
1435: , x_allowed_units_lookup_code
1436: , x_lot_status_enabled
1437: , x_default_lot_status_id
1438: FROM mtl_system_items_vl msik /* Bug 5581528 */
1439: WHERE msik.lot_translate_enabled = 'Y'
1440: AND msik.organization_id = p_organization_id
1441: AND msik.inventory_item_id = p_inventory_item_id; /*Added bug4096035*/
1442: ELSE
1457: , x_primary_uom_code
1458: , x_shelf_life_code
1459: , x_shelf_life_days
1460: , x_allowed_units_lookup_code
1461: FROM mtl_system_items_vl msik /* Bug 5581528 */
1462: WHERE msik.organization_id = p_organization_id
1463: AND msik.inventory_item_id = p_inventory_item_id;
1464: END IF;
1465: END IF;
1573: , x_SECONDARY_DEFAULT_IND
1574: , x_TRACKING_QUANTITY_IND
1575: , x_DUAL_UOM_DEVIATION_HIGH
1576: , x_DUAL_UOM_DEVIATION_LOW
1577: FROM mtl_system_items_vl msik /* Bug 5581528 */
1578: WHERE msik.lot_split_enabled = 'Y'
1579: AND msik.organization_id = p_organization_id
1580: AND msik.inventory_item_id = p_inventory_item_id;
1581: ELSE
1636: , x_SECONDARY_DEFAULT_IND
1637: , x_TRACKING_QUANTITY_IND
1638: , x_DUAL_UOM_DEVIATION_HIGH
1639: , x_DUAL_UOM_DEVIATION_LOW
1640: FROM mtl_system_items_vl msik /* Bug 5581528 */
1641: WHERE msik.lot_merge_enabled = 'Y'
1642: AND msik.organization_id = p_organization_id
1643: AND msik.inventory_item_id = p_inventory_item_id;
1644: ELSIF p_transaction_type_id = inv_globals.g_type_inv_lot_translate -- Lot Translate 84 Added bug4096035
1697: , x_SECONDARY_DEFAULT_IND
1698: , x_TRACKING_QUANTITY_IND
1699: , x_DUAL_UOM_DEVIATION_HIGH
1700: , x_DUAL_UOM_DEVIATION_LOW
1701: FROM mtl_system_items_vl msik /* Bug 5581528 */
1702: WHERE msik.lot_translate_enabled = 'Y'
1703: AND msik.organization_id = p_organization_id
1704: AND msik.inventory_item_id = p_inventory_item_id; /*Added bug4096035*/
1705:
1756: , x_SECONDARY_DEFAULT_IND
1757: , x_TRACKING_QUANTITY_IND
1758: , x_DUAL_UOM_DEVIATION_HIGH
1759: , x_DUAL_UOM_DEVIATION_LOW
1760: FROM mtl_system_items_vl msik /* Bug 5581528 */
1761: WHERE msik.organization_id = p_organization_id
1762: AND msik.inventory_item_id = p_inventory_item_id;
1763: END IF;
1764: END IF;
1871: BEGIN
1872:
1873: l_cross_ref := lpad(Rtrim(p_concatenated_segments, '%'), g_gtin_code_length, '00000000000000');
1874:
1875: l_sql_stmt1 := l_sql_stmt1 || ' FROM mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd'
1876: || ' WHERE msik.concatenated_segments LIKE (''' || p_concatenated_segments || ''')'
1877: || ' AND msik.organization_id = ' || p_organization_id
1878: || ' AND (msik.lot_status_enabled = ''Y'' OR msik.serial_status_enabled = ''Y'')';
1879:
1876: || ' WHERE msik.concatenated_segments LIKE (''' || p_concatenated_segments || ''')'
1877: || ' AND msik.organization_id = ' || p_organization_id
1878: || ' AND (msik.lot_status_enabled = ''Y'' OR msik.serial_status_enabled = ''Y'')';
1879:
1880: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'
1881: || 'mtl_cross_references mcr, mtl_onhand_quantities_detail moqd'
1882: || ' WHERE msik.organization_id = ' || p_organization_id
1883: || ' AND (msik.lot_status_enabled = ''Y'' OR msik.serial_status_enabled = ''Y'' )'
1884: || ' AND msik.inventory_item_id = mcr.inventory_item_id'
1959: NVL(msik.SECONDARY_DEFAULT_IND,''),
1960: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1961: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1962: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1963: FROM mtl_system_items_vl msik, wsh_delivery_details dd, wsh_delivery_assignments da, wsh_new_deliveries nd /* Bug 5581528 */
1964: WHERE msik.concatenated_segments LIKE (p_concatenated_segments)
1965: AND msik.organization_id = p_organization_id
1966: AND msik.inventory_item_id = dd.inventory_item_id
1967: AND nd.delivery_id = p_delivery_id
2016: NVL(msik.SECONDARY_DEFAULT_IND,''),
2017: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2018: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2019: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2020: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2021: wsh_delivery_details dd,
2022: wsh_delivery_assignments da,
2023: wsh_new_deliveries nd,
2024: mtl_cross_references mcr
2114: NVL(SECONDARY_DEFAULT_IND,''),
2115: NVL(TRACKING_QUANTITY_IND,'P'),
2116: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2117: NVL(DUAL_UOM_DEVIATION_LOW,0)
2118: FROM mtl_system_items_vl /* Bug 5581528 */
2119: WHERE organization_id = p_organization_id
2120: AND concatenated_segments LIKE (p_concatenated_segments)
2121: AND INV_MATERIAL_STATUS_GRP.loc_valid_for_item(p_locator_id,
2122: p_organization_id,
2124: p_subinventory_code)='Y' --Bug# 2879164
2125: AND INV_MATERIAL_STATUS_GRP.sub_valid_for_item (p_organization_id,
2126: inventory_item_id,
2127: p_subinventory_code)='Y' -- Bug 5500255
2128: AND mtl_system_items_vl.stock_enabled_flag = 'Y' -- Added for Bug 6310345
2129:
2130: --Changes for GTIN
2131: UNION
2132:
2172: NVL(SECONDARY_DEFAULT_IND,''),
2173: NVL(TRACKING_QUANTITY_IND,'P'),
2174: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2175: NVL(DUAL_UOM_DEVIATION_LOW,0)
2176: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
2177: WHERE msik.organization_id = p_organization_id
2178: AND msik.inventory_item_id = mcr.inventory_item_id
2179: AND mcr.cross_reference_type = g_gtin_cross_ref_type
2180: AND mcr.cross_reference LIKE l_cross_ref
2228: NVL(msik.SECONDARY_DEFAULT_IND,''),
2229: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2230: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2231: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2232: FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit /* Bug 5581528 */
2233: WHERE msik.organization_id = p_organization_id
2234: AND msik.concatenated_segments LIKE (p_concatenated_segments)
2235: AND msik.inventory_item_id = mpit.inventory_item_id
2236: AND mpit.physical_inventory_id = p_physical_inventory_id
2297: NVL(msik.SECONDARY_DEFAULT_IND,''),
2298: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2299: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2300: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2301: FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit, mtl_cross_references mcr /* Bug 5581528 */
2302: WHERE msik.organization_id = p_organization_id
2303: AND msik.inventory_item_id = mpit.inventory_item_id
2304: AND mpit.physical_inventory_id = p_physical_inventory_id
2305: AND mpit.organization_id = p_organization_id
2479: NVL(SECONDARY_DEFAULT_IND,''),
2480: NVL(TRACKING_QUANTITY_IND,'P'),
2481: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2482: NVL(DUAL_UOM_DEVIATION_LOW,0)
2483: FROM mtl_system_items_vl /* Bug 5581528 */
2484: WHERE organization_id = p_organization_id
2485: AND container_item_flag = 'Y'
2486: AND mtl_transactions_enabled_flag = 'Y'
2487: AND concatenated_segments LIKE (p_concatenated_segments)
2534: NVL(SECONDARY_DEFAULT_IND,''),
2535: NVL(TRACKING_QUANTITY_IND,'P'),
2536: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2537: NVL(DUAL_UOM_DEVIATION_LOW,0)
2538: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2539: mtl_cross_references mcr
2540: WHERE msik.organization_id = p_organization_id
2541: AND msik.container_item_flag = 'Y'
2542: AND msik.mtl_transactions_enabled_flag = 'Y'
2645: NVL(msik.SECONDARY_DEFAULT_IND,''),
2646: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2647: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2648: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2649: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2650: mtl_cycle_count_items mcci
2651: WHERE msik.organization_id = p_organization_id
2652: AND msik.inventory_item_id = mcci.inventory_item_id
2653: AND mcci.cycle_count_header_id = p_cycle_count_header_id
2709: NVL(msik.SECONDARY_DEFAULT_IND,''),
2710: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2711: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2712: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2713: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2714: mtl_cycle_count_items mcci, mtl_cross_references mcr
2715: WHERE msik.organization_id = p_organization_id
2716: AND msik.inventory_item_id = mcci.inventory_item_id
2717: AND mcci.cycle_count_header_id = p_cycle_count_header_id
2771: NVL(msik.SECONDARY_DEFAULT_IND,''),
2772: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2773: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2774: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2775: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2776: mtl_cycle_count_entries mcce
2777: WHERE msik.organization_id = p_organization_id
2778: AND msik.concatenated_segments LIKE (p_concatenated_segments)
2779: AND msik.inventory_item_id = mcce.inventory_item_id
2843: NVL(msik.SECONDARY_DEFAULT_IND,''),
2844: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2845: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2846: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2847: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2848: mtl_cycle_count_entries mcce, mtl_cross_references mcr
2849: WHERE msik.organization_id = p_organization_id
2850: AND msik.inventory_item_id = mcce.inventory_item_id
2851: AND mcce.cycle_count_header_id = p_cycle_count_header_id
3037: NVL(a.SECONDARY_DEFAULT_IND,''),
3038: NVL(a.TRACKING_QUANTITY_IND,'P'),
3039: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
3040: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
3041: FROM mtl_system_items_vl a, mtl_txn_request_lines b /* Bug 5581528 */
3042: WHERE b.lpn_id = p_lpn_id
3043: AND b.organization_id = p_organization_id
3044: AND b.inspection_status = 1 /* Yet to be inspected */
3045: AND b.organization_id = a.organization_id
3094: NVL(a.SECONDARY_DEFAULT_IND,''),
3095: NVL(a.TRACKING_QUANTITY_IND,'P'),
3096: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
3097: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
3098: FROM mtl_system_items_vl a, /* Bug 5581528 */
3099: mtl_txn_request_lines b,
3100: mtl_cross_references mcr
3101: WHERE b.lpn_id = p_lpn_id
3102: AND b.organization_id = p_organization_id
3158: NVL(a.SECONDARY_DEFAULT_IND,''),
3159: NVL(a.TRACKING_QUANTITY_IND,'P'),
3160: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
3161: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
3162: FROM mtl_system_items_vl a, mtl_txn_request_lines b /* Bug 5581528 */
3163: WHERE b.lpn_id = p_lpn_id
3164: AND b.organization_id = p_organization_id
3165: AND b.inspection_status = 1 /* Yet to be inspected */
3166: AND b.organization_id = a.organization_id
3254: NVL(msi.SECONDARY_DEFAULT_IND,''),
3255: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3256: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3257: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3258: FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3259: WHERE moq.organization_id = p_org_id
3260: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3261: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3262: AND moq.containerized_flag = 2
3310: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3311: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3312: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3313: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3314: mtl_system_items_vl msi, /* Bug 5581528 */
3315: mtl_cross_references mcr
3316: WHERE moq.organization_id = p_org_id
3317: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3318: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3370: NVL(msi.SECONDARY_DEFAULT_IND,''),
3371: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3372: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3373: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3374: FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi /* Bug 5581528 */
3375: -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3376: WHERE moq.organization_id = p_org_id
3377: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3378: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3427: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3428: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3429: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3430: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3431: mtl_system_items_vl msi, /* Bug 5581528 */
3432: mtl_cross_references mcr
3433: WHERE moq.organization_id = p_org_id
3434: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3435: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3493: NVL(msi.SECONDARY_DEFAULT_IND,''),
3494: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3495: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3496: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3497: FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */
3498: WHERE wlc.organization_id = p_org_id
3499: AND wlc.parent_lpn_id = TO_NUMBER(p_lpn_id)
3500: AND msi.inventory_item_id = wlc.inventory_item_id
3501: AND msi.organization_id = wlc.organization_id
3546: NVL(msi.SECONDARY_DEFAULT_IND,''),
3547: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3548: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3549: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3550: FROM mtl_system_items_vl msi, /* Bug 5581528 */
3551: wms_lpn_contents wlc,
3552: mtl_cross_references mcr
3553: WHERE wlc.organization_id = p_org_id
3554: AND wlc.parent_lpn_id = TO_NUMBER(p_lpn_id)
3613: NVL(msi.SECONDARY_DEFAULT_IND,''),
3614: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3615: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3616: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3617: FROM mtl_system_items_vl msi /* Bug 5581528 */
3618: WHERE msi.organization_id = p_org_id
3619: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3620: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
3621: AND msi.concatenated_segments LIKE (p_item)
3665: NVL(msi.SECONDARY_DEFAULT_IND,''),
3666: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3667: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3668: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3669: FROM mtl_system_items_vl msi, /* Bug 5581528 */
3670: mtl_cross_references mcr
3671: WHERE msi.organization_id = p_org_id
3672: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3673: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
3722: NVL(msi.SECONDARY_DEFAULT_IND,''),
3723: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3724: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3725: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3726: FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3727: WHERE msi.organization_id = p_org_id
3728: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3729: AND msi.concatenated_segments LIKE (p_item)
3730: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
3780: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3781: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3782: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3783: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3784: mtl_system_items_vl msi, /* Bug 5581528 */
3785: mtl_cross_references mcr
3786: WHERE msi.organization_id = p_org_id
3787: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3788: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
3843: NVL(SECONDARY_DEFAULT_IND,''),
3844: NVL(TRACKING_QUANTITY_IND,'P'),
3845: NVL(DUAL_UOM_DEVIATION_HIGH,0),
3846: NVL(DUAL_UOM_DEVIATION_LOW,0)
3847: FROM mtl_system_items_vl /* Bug 5581528 */
3848: WHERE organization_id = p_org_id
3849: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
3850: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3851: AND concatenated_segments LIKE (p_item)
3895: NVL(SECONDARY_DEFAULT_IND,''),
3896: NVL(TRACKING_QUANTITY_IND,'P'),
3897: NVL(DUAL_UOM_DEVIATION_HIGH,0),
3898: NVL(DUAL_UOM_DEVIATION_LOW,0)
3899: FROM mtl_system_items_vl msik, /* Bug 5581528 */
3900: mtl_cross_references mcr
3901: WHERE msik.organization_id = p_org_id
3902: AND NVL(msik.container_item_flag, 'N') = NVL(p_container_item_flag, NVL(msik.container_item_flag, 'N'))
3903: AND NVL(msik.serial_number_control_code, 1) IN (1, 2, 5, 6)
4026: NVL(a.SECONDARY_DEFAULT_IND,''),
4027: NVL(a.TRACKING_QUANTITY_IND,'P'),
4028: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4029: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4030: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4031: WHERE b.to_organization_id = p_organization_id
4032: AND b.po_header_id = p_source_id
4033: AND b.inspection_status_code = 'NOT INSPECTED'
4034: AND b.routing_id = 2 /* Inspection routing */
4085: NVL(a.SECONDARY_DEFAULT_IND,''),
4086: NVL(a.TRACKING_QUANTITY_IND,'P'),
4087: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4088: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4089: FROM mtl_system_items_vl a, /* Bug 5581528 */
4090: rcv_transactions_v b,
4091: mtl_cross_references mcr
4092: WHERE b.to_organization_id = p_organization_id
4093: AND b.po_header_id = p_source_id
4149: NVL(a.SECONDARY_DEFAULT_IND,''),
4150: NVL(a.TRACKING_QUANTITY_IND,'P'),
4151: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4152: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4153: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4154: WHERE b.to_organization_id = p_organization_id
4155: AND b.shipment_header_id = p_source_id
4156: AND b.receipt_source_code <> 'VENDOR'
4157: AND b.inspection_status_code = 'NOT INSPECTED'
4209: NVL(a.SECONDARY_DEFAULT_IND,''),
4210: NVL(a.TRACKING_QUANTITY_IND,'P'),
4211: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4212: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4213: FROM mtl_system_items_vl a, /* Bug 5581528 */
4214: rcv_transactions_v b,
4215: mtl_cross_references mcr
4216: WHERE b.to_organization_id = p_organization_id
4217: AND b.shipment_header_id = p_source_id
4275: NVL(a.SECONDARY_DEFAULT_IND,''),
4276: NVL(a.TRACKING_QUANTITY_IND,'P'),
4277: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4278: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4279: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4280: WHERE b.to_organization_id = p_organization_id
4281: AND b.oe_order_header_id = p_source_id
4282: AND b.receipt_source_code <> 'VENDOR'
4283: AND b.inspection_status_code = 'NOT INSPECTED'
4335: NVL(a.SECONDARY_DEFAULT_IND,''),
4336: NVL(a.TRACKING_QUANTITY_IND,'P'),
4337: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4338: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4339: FROM mtl_system_items_vl a, /* Bug 5581528 */
4340: rcv_transactions_v b,
4341: mtl_cross_references mcr
4342: WHERE b.to_organization_id = p_organization_id
4343: AND b.oe_order_header_id = p_source_id
4401: NVL(a.SECONDARY_DEFAULT_IND,''),
4402: NVL(a.TRACKING_QUANTITY_IND,'P'),
4403: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4404: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4405: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4406: WHERE b.to_organization_id = p_organization_id
4407: AND b.shipment_header_id = TO_CHAR(p_source_id)
4408: AND b.inspection_status_code = 'NOT INSPECTED'
4409: AND b.routing_id = 2 /* Inspection routing */
4460: NVL(a.SECONDARY_DEFAULT_IND,''),
4461: NVL(a.TRACKING_QUANTITY_IND,'P'),
4462: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4463: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4464: FROM mtl_system_items_vl a, /* Bug 5581528 */
4465: rcv_transactions_v b,
4466: mtl_cross_references mcr
4467: WHERE b.to_organization_id = p_organization_id
4468: AND b.shipment_header_id = TO_CHAR(p_source_id)
4526: NVL(a.SECONDARY_DEFAULT_IND,''),
4527: NVL(a.TRACKING_QUANTITY_IND,'P'),
4528: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4529: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4530: FROM mtl_system_items_vl a, /* Bug 5581528 */
4531: rcv_transactions_v b,
4532: (SELECT DISTINCT rsl.shipment_header_id
4533: FROM po_requisition_lines pol,
4534: rcv_shipment_lines rsl
4593: NVL(a.SECONDARY_DEFAULT_IND,''),
4594: NVL(a.TRACKING_QUANTITY_IND,'P'),
4595: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4596: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4597: FROM mtl_system_items_vl a, /* Bug 5581528 */
4598: rcv_transactions_v b,
4599: mtl_cross_references mcr,
4600: (SELECT DISTINCT rsl.shipment_header_id
4601: FROM po_requisition_lines pol,
4759: NVL(msi.SECONDARY_DEFAULT_IND,''),
4760: NVL(msi.TRACKING_QUANTITY_IND,'P'),
4761: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
4762: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
4763: FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
4764: WHERE moq.containerized_flag = 2
4765: AND moq.organization_id = p_org_id
4766: AND moq.inventory_item_id = msi.inventory_item_id
4767: AND msi.concatenated_segments LIKE (p_item)
4812: NVL(msi.TRACKING_QUANTITY_IND,'P'),
4813: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
4814: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
4815: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
4816: mtl_system_items_vl msi, /* Bug 5581528 */
4817: mtl_cross_references mcr
4818: WHERE moq.containerized_flag = 2
4819: AND moq.organization_id = p_org_id
4820: AND moq.inventory_item_id = msi.inventory_item_id
4873: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
4874: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
4875: -- bug 5172851, wms_lpn_contents_v is replaced with
4876: -- wms_lpn_contents for performance reason
4877: FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */
4878: WHERE msi.concatenated_segments LIKE (p_item)
4879: AND msi.inventory_item_id = wlc.inventory_item_id
4880: AND msi.organization_id = p_org_id
4881: AND wlc.parent_lpn_id = p_lpn_id
4925: NVL(msi.SECONDARY_DEFAULT_IND,''),
4926: NVL(msi.TRACKING_QUANTITY_IND,'P'),
4927: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
4928: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
4929: FROM mtl_system_items_vl msi, /* Bug 5581528 */
4930: -- bug 5172851, wms_lpn_contents_v is replaced with
4931: -- wms_lpn_contents for performance reason
4932: wms_lpn_contents wlc,
4933: mtl_cross_references mcr
5008: NVL(SECONDARY_DEFAULT_IND,''),
5009: NVL(TRACKING_QUANTITY_IND,'P'),
5010: NVL(DUAL_UOM_DEVIATION_HIGH,0),
5011: NVL(DUAL_UOM_DEVIATION_LOW,0)
5012: FROM mtl_system_items_vl /* Bug 5581528 */
5013: WHERE organization_id = p_org_id
5014: AND concatenated_segments LIKE (p_item)
5015:
5016: --Changes for GTIN
5058: NVL(SECONDARY_DEFAULT_IND,''),
5059: NVL(TRACKING_QUANTITY_IND,'P'),
5060: NVL(DUAL_UOM_DEVIATION_HIGH,0),
5061: NVL(DUAL_UOM_DEVIATION_LOW,0)
5062: FROM mtl_system_items_vl msik, /* Bug 5581528 */
5063: mtl_cross_references mcr
5064: WHERE msik.organization_id = p_org_id
5065: AND msik.inventory_item_id = mcr.inventory_item_id
5066: AND mcr.cross_reference_type = g_gtin_cross_ref_type
5118: NVL(msi.SECONDARY_DEFAULT_IND,''),
5119: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5120: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5121: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5122: FROM mtl_serial_numbers msn, mtl_system_items_vl msi /* Bug 5581528 */
5123: WHERE msn.current_organization_id = p_org_id
5124: AND msn.serial_number = p_serial
5125: AND msn.inventory_item_id = msi.inventory_item_id
5126: AND msi.organization_id = msn.current_organization_id
5172: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5173: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5174: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5175: FROM mtl_serial_numbers msn,
5176: mtl_system_items_vl msi, /* Bug 5581528 */
5177: mtl_cross_references mcr
5178: WHERE msn.current_organization_id = p_org_id
5179: AND msn.serial_number = p_serial
5180: AND msn.inventory_item_id = msi.inventory_item_id
5237: NVL(msi.SECONDARY_DEFAULT_IND,''),
5238: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5239: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5240: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5241: FROM mtl_system_items_vl msi, wms_lpn_contents wlpnc /* Bug 5581528 */
5242: WHERE wlpnc.parent_lpn_id = p_lpn_id
5243: AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5244: AND wlpnc.organization_id = p_org_id
5245: AND msi.organization_id = wlpnc.organization_id
5288: NVL(msi.SECONDARY_DEFAULT_IND,''),
5289: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5290: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5291: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5292: FROM mtl_system_items_vl msi, mtl_serial_numbers msn /* Bug 5581528 */
5293: WHERE msn.lpn_id = p_lpn_id
5294: AND msn.last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5295: AND msn.current_organization_id = p_org_id
5296: AND msi.organization_id = msn.current_organization_id
5342: NVL(msi.SECONDARY_DEFAULT_IND,''),
5343: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5344: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5345: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5346: FROM mtl_system_items_vl msi, /* Bug 5581528 */
5347: wms_lpn_contents wlpnc,
5348: mtl_cross_references mcr
5349: WHERE wlpnc.parent_lpn_id = p_lpn_id
5350: AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5400: NVL(msi.SECONDARY_DEFAULT_IND,''),
5401: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5402: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5403: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5404: FROM mtl_system_items_vl msi, /* Bug 5581528 */
5405: mtl_serial_numbers msn,
5406: mtl_cross_references mcr
5407: WHERE msn.lpn_id = p_lpn_id
5408: AND msn.last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5444: OPEN x_vehicle FOR
5445: SELECT msi.concatenated_segments
5446: , msi.description
5447: , msi.inventory_item_id
5448: FROM mtl_system_items_vl msi /* Bug 5581528 */
5449: WHERE msi.organization_id = p_organization_id
5450: AND msi.concatenated_segments LIKE (p_concatenated_segments)
5451: AND msi.vehicle_item_flag = 'Y'
5452: ORDER BY UPPER(msi.concatenated_segments);
5693:
5694:
5695: IF P_LPN IS NULL THEN
5696:
5697: l_sql_stmt1 := l_sql_stmt1 || ' FROM mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd'
5698: || ' WHERE msik.concatenated_segments LIKE (''' || p_concatenated_segments || ''')'
5699:
5700: || ' AND msik.organization_id = ' || p_organization_id
5701: || ' AND (msik.serial_number_control_code in (1,6) OR msik.serial_status_enabled = ''Y'' )'
5702: || ' AND moqd.inventory_item_id = msik.inventory_item_id '
5703: || ' AND moqd.organization_id = msik.organization_id '
5704: || ' AND moqd.lpn_id is NULL ' ;
5705:
5706: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'
5707: || 'mtl_cross_references mcr, mtl_onhand_quantities_detail moqd'
5708: || ' WHERE msik.organization_id = ' || p_organization_id
5709: || ' AND (msik.serial_number_control_code in (1,6) OR msik.serial_status_enabled = ''Y'' )'
5710: || ' AND msik.inventory_item_id = mcr.inventory_item_id'
5730: l_sql_stmt_xref := l_sql_stmt_xref || ' AND moqd.locator_id = ' || p_locator_id;
5731: END IF;
5732: END IF;
5733: ELSE
5734: l_sql_stmt1 := l_sql_stmt1 || ' FROM mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd ,WMS_LICENSE_PLATE_NUMBERS WLPN ,WMS_LPN_CONTENTS WLC'
5735: || ' WHERE WLPN.LICENSE_PLATE_NUMBER = ''' || p_lpn || ''''
5736: || ' AND WLC.PARENT_LPN_ID = WLPN.LPN_ID '
5737: || ' AND MSIK.INVENTORY_ITEM_ID = WLC.INVENTORY_ITEM_ID '
5738: || ' AND msik.concatenated_segments LIKE (''' || p_concatenated_segments || ''')'
5740: || ' AND (msik.serial_number_control_code in (1,6) OR msik.serial_status_enabled = ''Y'' )'
5741: || ' AND moqd.organization_id = msik.organization_id'
5742: || ' AND moqd.inventory_item_id = msik.inventory_item_id';
5743:
5744: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'
5745: || 'mtl_cross_references mcr, mtl_onhand_quantities_detail moqd ,WMS_LICENSE_PLATE_NUMBERS WLPN ,WMS_LPN_CONTENTS WLC'
5746: || ' WHERE WLPN.LICENSE_PLATE_NUMBER = ''' || p_lpn || ''''
5747: || ' AND WLC.PARENT_LPN_ID = WLPN.LPN_ID '
5748: || ' AND MSIK.INVENTORY_ITEM_ID = WLC.INVENTORY_ITEM_ID '