DBA Data[Home] [Help]

APPS.INV_UI_ITEM_LOVS dependencies on MTL_SYSTEM_ITEMS

Line 34: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

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),'

Line 81: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl

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),'

Line 136: -- Changed mtl_system_items_kfv to mtl_system_items_vl

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.

Line 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;

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 ||

Line 143: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' ||

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

Line 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 */

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 ||

Line 258: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* 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 ||
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

Line 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 */

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 */

Line 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

Line 279: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */

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

Line 360: FROM mtl_system_items_vl /* Bug 5581528 */

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:

Line 411: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */

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

Line 472: FROM mtl_system_items_vl msik /* Bug 5581528 */

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

Line 476: FROM mtl_system_items_b msib

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
477: WHERE msib.inventory_item_id=msik.inventory_item_id
478: AND msib.organization_id = p_to_organization_id
479: AND msib.mtl_transactions_enabled_flag = 'Y'
480: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)

Line 484: FROM mtl_system_items_b msib1

480: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
481: AND (NVL(msik.serial_number_control_code,1) IN (2,5) OR nvl(msib.serial_number_control_code,1) IN (1,6))
482: AND (NVL(msik.revision_qty_control_code,1)=2 OR NVL(msib.revision_qty_control_code,1)=1)))
483: OR (p_transaction_action_id<>3 AND EXISTS (SELECT 1
484: FROM mtl_system_items_b msib1
485: WHERE msib1.inventory_item_id=msik.inventory_item_id
486: AND msib1.organization_id=p_to_organization_id
487: AND msib1.mtl_transactions_enabled_flag='Y')))
488: AND msik.concatenated_segments LIKE (p_concatenated_segments)

Line 535: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */

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

Line 539: FROM mtl_system_items_b msib

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
540: WHERE msib.inventory_item_id=msik.inventory_item_id
541: AND msib.organization_id = p_to_organization_id
542: AND msib.mtl_transactions_enabled_flag ='Y'
543: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)

Line 547: FROM mtl_system_items_b msib1

543: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
544: AND (NVL(msik.serial_number_control_code,1) IN (2,5) OR nvl(msib.serial_number_control_code,1) IN (1,6))
545: AND (NVL(msik.revision_qty_control_code,1)=2 OR NVL(msib.revision_qty_control_code,1)=1)))
546: OR ((p_transaction_action_id <>3 AND EXISTS(SELECT 1
547: FROM mtl_system_items_b msib1
548: WHERE msib1.inventory_item_id=msik.inventory_item_id
549: AND msib1.organization_id=p_to_organization_id
550: AND msib1.mtl_transactions_enabled_flag='Y'))))
551: AND msik.inventory_item_id = mcr.inventory_item_id

Line 770: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */

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

Line 831: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 899: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */

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

Line 960: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 1028: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */

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

Line 1087: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 1151: FROM mtl_system_items_vl msik /* Bug 5581528 */

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:

Line 1203: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 1260: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */

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

Line 1314: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 1381: FROM mtl_system_items_vl msik /* Bug 5581528 */

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

Line 1410: FROM mtl_system_items_vl msik /* Bug 5581528 */

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

Line 1438: FROM mtl_system_items_vl msik /* Bug 5581528 */

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

Line 1461: FROM mtl_system_items_vl msik /* Bug 5581528 */

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;

Line 1577: FROM mtl_system_items_vl msik /* Bug 5581528 */

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

Line 1640: FROM mtl_system_items_vl msik /* Bug 5581528 */

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

Line 1701: FROM mtl_system_items_vl msik /* Bug 5581528 */

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:

Line 1760: FROM mtl_system_items_vl msik /* Bug 5581528 */

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;

Line 1875: l_sql_stmt1 := l_sql_stmt1 || ' FROM mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd'

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:

Line 1880: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'

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'

Line 1963: FROM mtl_system_items_vl msik, wsh_delivery_details dd, wsh_delivery_assignments da, wsh_new_deliveries nd /* Bug 5581528 */

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

Line 2020: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 2118: FROM mtl_system_items_vl /* Bug 5581528 */

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,

Line 2128: AND mtl_system_items_vl.stock_enabled_flag = 'Y' -- Added for Bug 6310345

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:

Line 2176: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */

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

Line 2232: FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit /* Bug 5581528 */

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

Line 2301: FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit, mtl_cross_references mcr /* Bug 5581528 */

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

Line 2483: FROM mtl_system_items_vl /* Bug 5581528 */

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)

Line 2538: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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'

Line 2649: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 2713: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 2775: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 2847: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 3041: FROM mtl_system_items_vl a, mtl_txn_request_lines b /* Bug 5581528 */

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

Line 3098: FROM mtl_system_items_vl a, /* Bug 5581528 */

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

Line 3162: FROM mtl_system_items_vl a, mtl_txn_request_lines b /* Bug 5581528 */

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

Line 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

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

Line 3314: mtl_system_items_vl msi, /* Bug 5581528 */

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))

Line 3374: FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi /* Bug 5581528 */

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))

Line 3431: mtl_system_items_vl msi, /* Bug 5581528 */

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))

Line 3497: FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */

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

Line 3550: FROM mtl_system_items_vl msi, /* Bug 5581528 */

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)

Line 3617: FROM mtl_system_items_vl msi /* Bug 5581528 */

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)

Line 3669: FROM mtl_system_items_vl msi, /* Bug 5581528 */

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'))

Line 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

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'))

Line 3784: mtl_system_items_vl msi, /* Bug 5581528 */

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'))

Line 3847: FROM mtl_system_items_vl /* Bug 5581528 */

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)

Line 3899: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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)

Line 4030: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */

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 */

Line 4089: FROM mtl_system_items_vl a, /* Bug 5581528 */

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

Line 4153: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */

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'

Line 4213: FROM mtl_system_items_vl a, /* Bug 5581528 */

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

Line 4279: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */

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'

Line 4339: FROM mtl_system_items_vl a, /* Bug 5581528 */

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

Line 4405: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */

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 */

Line 4464: FROM mtl_system_items_vl a, /* Bug 5581528 */

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)

Line 4530: FROM mtl_system_items_vl a, /* Bug 5581528 */

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

Line 4597: FROM mtl_system_items_vl a, /* Bug 5581528 */

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,

Line 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

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)

Line 4816: mtl_system_items_vl msi, /* Bug 5581528 */

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

Line 4877: FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */

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

Line 4929: FROM mtl_system_items_vl msi, /* Bug 5581528 */

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

Line 5012: FROM mtl_system_items_vl /* Bug 5581528 */

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

Line 5062: FROM mtl_system_items_vl msik, /* Bug 5581528 */

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

Line 5122: FROM mtl_serial_numbers msn, mtl_system_items_vl msi /* Bug 5581528 */

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

Line 5176: mtl_system_items_vl msi, /* Bug 5581528 */

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

Line 5241: FROM mtl_system_items_vl msi, wms_lpn_contents wlpnc /* Bug 5581528 */

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

Line 5292: FROM mtl_system_items_vl msi, mtl_serial_numbers msn /* Bug 5581528 */

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

Line 5346: FROM mtl_system_items_vl msi, /* Bug 5581528 */

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')

Line 5404: FROM mtl_system_items_vl msi, /* Bug 5581528 */

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')

Line 5448: FROM mtl_system_items_vl msi /* Bug 5581528 */

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);

Line 5566: FROM mtl_system_items

5562: ELSE
5563: BEGIN
5564: SELECT primary_uom_code
5565: INTO l_primary_uom_code
5566: FROM mtl_system_items
5567: WHERE organization_id = p_organization_id
5568: AND inventory_item_id = p_item_id;
5569: EXCEPTION
5570: WHEN OTHERS THEN

Line 5697: l_sql_stmt1 := l_sql_stmt1 || ' FROM mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd'

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'' )'

Line 5706: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'

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'

Line 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'

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 || ''')'

Line 5744: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'

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 '