[Home] [Help]
33: l_sql_stmt VARCHAR2(7500);
34: l_conc_seg varchar2(2000) := p_concatenated_segments;
35: l_append varchar2(2):='';
36: -- Bug# 6747729
37: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
38: l_sql_stmt1 VARCHAR2(7500)
39: := 'select concatenated_segments,'
40: || 'msik.inventory_item_id, msik.description,'
41: || 'Nvl(revision_qty_control_code,1),'
81: || 'NVL(DUAL_UOM_DEVIATION_HIGH,0),'
82: || 'NVL(DUAL_UOM_DEVIATION_LOW,0),'
83: || 'stock_enabled_flag';
84: -- Bug# 6747729
85: -- Added code to also fetch stock_enabled_flag from mtl_system_items_vl
86: l_sql_stmt_xref VARCHAR2(7500)
87: := 'select concatenated_segments,'
88: || 'msik.inventory_item_id, msik.description,'
89: || 'Nvl(revision_qty_control_code,1),'
140:
141: l_append:=wms_deploy.get_item_suffix_for_lov(p_concatenated_segments);
142:
143: -- Modified for Bug # 5472330
144: -- Changed mtl_system_items_kfv to mtl_system_items_vl
145:
146: 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;
147: -- 1 effectivity control implies that the item is NOT effectivity
148: -- controlled.
142:
143: -- Modified for Bug # 5472330
144: -- Changed mtl_system_items_kfv to mtl_system_items_vl
145:
146: 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;
147: -- 1 effectivity control implies that the item is NOT effectivity
148: -- controlled.
149:
150: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
147: -- 1 effectivity control implies that the item is NOT effectivity
148: -- controlled.
149:
150: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
151: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' ||
152: ' WHERE msik.organization_id = ' || p_organization_id ||
153: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
154: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type || val ||
155: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
273: l_where_clause := p_where_clause;
274: l_append:=wms_deploy.get_item_suffix_for_lov(p_concatenated_segments);
275:
276: IF (p_locator_id IS NOT NULL ) THEN
277: 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 */
278: || ''' AND moqd.locator_id = ' || p_locator_id ||'
279: 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;
280:
281: l_sql_stmt := l_sql_stmt||' union all '||l_sql_stmt1 || ' from mtl_system_items_kfv msik, mtl_onhand_quantities_detail moqd
277: 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 */
278: || ''' AND moqd.locator_id = ' || p_locator_id ||'
279: 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;
280:
281: l_sql_stmt := l_sql_stmt||' union all '||l_sql_stmt1 || ' from mtl_system_items_kfv msik, mtl_onhand_quantities_detail moqd
282: WHERE msik.organization_id = ' || p_organization_id || ' AND moqd.subinventory_code = ''' || p_subinventory_code || '''
283: AND moqd.locator_id in (select inventory_location_id from mtl_item_locations mil where mil.organization_id = ' || p_organization_id ||'
284: AND mil.physical_location_id = ' || p_locator_id || '
285: and mil.subinventory_code = ''' ||p_subinventory_code||''')
286:
287: 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;
288:
289: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
290: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
291: ' WHERE msik.organization_id = ' || p_organization_id ||
292: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
293: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type ||''''||
294: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
295: , ''%'' ), ' || g_gtin_code_length || ' , ' || '''00000000000000'''||')' ||
296: ' AND (mcr.organization_id = msik.organization_id OR mcr.org_independent_flag = ' || '''Y''' || ')' || l_where_clause;
297:
298: ELSE
299: 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 */
300: || ''' 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;
301:
302: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
303: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
299: 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 */
300: || ''' 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;
301:
302: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
303: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
304: ' WHERE msik.organization_id = ' || p_organization_id ||
305: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
306: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type ||''''||
307: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
307: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
308: , ''%'' ), ' || g_gtin_code_length || ' , ' || '''00000000000000'''||')' ||
309: ' AND (mcr.organization_id = msik.organization_id OR mcr.org_independent_flag = ' || '''Y''' || ')' || l_where_clause;
310: l_sql_stmt := l_sql_stmt || ' UNION ' || l_sql_stmt_xref ||
311: ' FROM mtl_system_items_vl msik, mtl_cross_references mcr ' || /* Bug 5581528 */
312: ' WHERE msik.organization_id = ' || p_organization_id ||
313: ' AND msik.inventory_item_id = mcr.inventory_item_id ' ||
314: ' AND mcr.cross_reference_type = ''' || g_gtin_cross_ref_type ||''''||
315: ' AND mcr.cross_reference like lpad(rtrim(:l_conc_seg
396: NVL(SECONDARY_DEFAULT_IND,''),
397: NVL(TRACKING_QUANTITY_IND,'P'),
398: NVL(DUAL_UOM_DEVIATION_HIGH,0),
399: NVL(DUAL_UOM_DEVIATION_LOW,0)
400: FROM mtl_system_items_vl /* Bug 5581528 */
401: WHERE organization_id = p_organization_id
402: AND concatenated_segments LIKE nvl(l_concatenated_segments,concatenated_segments)
403: AND inventory_item_id in (select inventory_item_id from mtl_txn_request_lines where header_id =to_number(p_header_id))
404:
448: NVL(SECONDARY_DEFAULT_IND,''),
449: NVL(TRACKING_QUANTITY_IND,'P'),
450: NVL(DUAL_UOM_DEVIATION_HIGH,0),
451: NVL(DUAL_UOM_DEVIATION_LOW,0)
452: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
453: WHERE msik.organization_id = p_organization_id
454: AND msik.inventory_item_id = mcr.inventory_item_id
455: AND mcr.cross_reference_type = g_gtin_cross_ref_type
456: AND mcr.cross_reference LIKE l_cross_ref
514: NVL(msik.SECONDARY_DEFAULT_IND,''),
515: NVL(msik.TRACKING_QUANTITY_IND,'P'),
516: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
517: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
518: FROM mtl_system_items_vl msik /* Bug 5581528 */
519: WHERE msik.organization_id = p_organization_id
520: AND msik.mtl_transactions_enabled_flag = 'Y'
521: AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
522: FROM mtl_system_items_b msib
518: FROM mtl_system_items_vl msik /* Bug 5581528 */
519: WHERE msik.organization_id = p_organization_id
520: AND msik.mtl_transactions_enabled_flag = 'Y'
521: AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
522: FROM mtl_system_items_b msib
523: WHERE msib.inventory_item_id=msik.inventory_item_id
524: AND msib.organization_id = p_to_organization_id
525: AND msib.mtl_transactions_enabled_flag = 'Y'
526: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
526: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
527: AND (NVL(msik.serial_number_control_code,1) IN (2,5) OR nvl(msib.serial_number_control_code,1) IN (1,6))
528: AND (NVL(msik.revision_qty_control_code,1)=2 OR NVL(msib.revision_qty_control_code,1)=1)))
529: OR (p_transaction_action_id<>3 AND EXISTS (SELECT 1
530: FROM mtl_system_items_b msib1
531: WHERE msib1.inventory_item_id=msik.inventory_item_id
532: AND msib1.organization_id=p_to_organization_id
533: AND msib1.mtl_transactions_enabled_flag='Y')))
534: AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
578: NVL(SECONDARY_DEFAULT_IND,''),
579: NVL(TRACKING_QUANTITY_IND,'P'),
580: NVL(DUAL_UOM_DEVIATION_HIGH,0),
581: NVL(DUAL_UOM_DEVIATION_LOW,0)
582: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
583: WHERE msik.organization_id = p_organization_id
584: AND msik.mtl_transactions_enabled_flag = 'Y'
585: AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
586: FROM mtl_system_items_b msib
582: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
583: WHERE msik.organization_id = p_organization_id
584: AND msik.mtl_transactions_enabled_flag = 'Y'
585: AND ((p_transaction_action_id=3 AND EXISTS (SELECT 1
586: FROM mtl_system_items_b msib
587: WHERE msib.inventory_item_id=msik.inventory_item_id
588: AND msib.organization_id = p_to_organization_id
589: AND msib.mtl_transactions_enabled_flag ='Y'
590: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
590: AND (NVL(msik.lot_control_code,1)=2 OR nvl(msib.lot_control_code,1)=1)
591: AND (NVL(msik.serial_number_control_code,1) IN (2,5) OR nvl(msib.serial_number_control_code,1) IN (1,6))
592: AND (NVL(msik.revision_qty_control_code,1)=2 OR NVL(msib.revision_qty_control_code,1)=1)))
593: OR ((p_transaction_action_id <>3 AND EXISTS(SELECT 1
594: FROM mtl_system_items_b msib1
595: WHERE msib1.inventory_item_id=msik.inventory_item_id
596: AND msib1.organization_id=p_to_organization_id
597: AND msib1.mtl_transactions_enabled_flag='Y'))))
598: AND msik.inventory_item_id = mcr.inventory_item_id
874: NVL(msik.SECONDARY_DEFAULT_IND,''),
875: NVL(msik.TRACKING_QUANTITY_IND,'P'),
876: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
877: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
878: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
879: WHERE msik.lot_split_enabled = 'Y'
880: AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
881: AND msik.organization_id = mln.organization_id
882: AND msik.inventory_item_id = mln.inventory_item_id
936: NVL(msik.SECONDARY_DEFAULT_IND,''),
937: NVL(msik.TRACKING_QUANTITY_IND,'P'),
938: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
939: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
940: FROM mtl_system_items_vl msik, /* Bug 5581528 */
941: mtl_lot_numbers mln,
942: mtl_cross_references mcr
943: WHERE msik.lot_split_enabled = 'Y'
944: AND msik.organization_id = mln.organization_id
1005: NVL(msik.SECONDARY_DEFAULT_IND,''),
1006: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1007: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1008: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1009: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
1010: WHERE msik.lot_merge_enabled = 'Y'
1011: AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
1012: AND msik.organization_id = mln.organization_id
1013: AND msik.inventory_item_id = mln.inventory_item_id
1067: NVL(msik.SECONDARY_DEFAULT_IND,''),
1068: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1069: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1070: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1071: FROM mtl_system_items_vl msik, /* Bug 5581528 */
1072: mtl_lot_numbers mln,
1073: mtl_cross_references mcr
1074: WHERE msik.lot_merge_enabled = 'Y'
1075: AND msik.organization_id = mln.organization_id
1136: NVL(msik.SECONDARY_DEFAULT_IND,''),
1137: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1138: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1139: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1140: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
1141: WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
1142: AND msik.organization_id = mln.organization_id
1143: AND msik.inventory_item_id = mln.inventory_item_id
1144: AND mln.lot_number = p_lot_number
1196: NVL(msik.SECONDARY_DEFAULT_IND,''),
1197: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1198: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1199: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1200: FROM mtl_system_items_vl msik, /* Bug 5581528 */
1201: mtl_lot_numbers mln,
1202: mtl_cross_references mcr
1203: WHERE msik.organization_id = mln.organization_id
1204: AND msik.inventory_item_id = mln.inventory_item_id
1261: NVL(msik.SECONDARY_DEFAULT_IND,''),
1262: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1263: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1264: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1265: FROM mtl_system_items_vl msik /* Bug 5581528 */
1266: WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
1267: AND msik.lot_control_code = 2
1268: AND msik.organization_id = p_organization_id
1269:
1314: NVL(msik.SECONDARY_DEFAULT_IND,''),
1315: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1316: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1317: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1318: FROM mtl_system_items_vl msik, /* Bug 5581528 */
1319: mtl_cross_references mcr
1320: WHERE msik.lot_control_code = 2
1321: AND msik.organization_id = p_organization_id
1322: AND msik.inventory_item_id = mcr.inventory_item_id
1372: NVL(msik.SECONDARY_DEFAULT_IND,''),
1373: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1374: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1375: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1376: FROM mtl_system_items_vl msik, mtl_lot_numbers mln /* Bug 5581528 */
1377: WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
1378: AND msik.organization_id = mln.organization_id
1379: AND msik.inventory_item_id = mln.inventory_item_id
1380: AND mln.lot_number = p_lot_number
1427: NVL(msik.SECONDARY_DEFAULT_IND,''),
1428: NVL(msik.TRACKING_QUANTITY_IND,'P'),
1429: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
1430: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
1431: FROM mtl_system_items_vl msik, /* Bug 5581528 */
1432: mtl_lot_numbers mln,
1433: mtl_cross_references mcr
1434: WHERE msik.organization_id = mln.organization_id
1435: AND msik.inventory_item_id = mln.inventory_item_id
1494: , x_shelf_life_days
1495: , x_allowed_units_lookup_code
1496: , x_lot_status_enabled
1497: , x_default_lot_status_id
1498: FROM mtl_system_items_vl msik /* Bug 5581528 */
1499: WHERE msik.lot_split_enabled = 'Y'
1500: AND msik.organization_id = p_organization_id
1501: AND msik.inventory_item_id = p_inventory_item_id;
1502: ELSE
1523: , x_shelf_life_days
1524: , x_allowed_units_lookup_code
1525: , x_lot_status_enabled -- nsinghi bug#5475282
1526: , x_default_lot_status_id -- nsinghi bug#5475282
1527: FROM mtl_system_items_vl msik /* Bug 5581528 */
1528: WHERE msik.lot_merge_enabled = 'Y'
1529: AND msik.organization_id = p_organization_id
1530: AND msik.inventory_item_id = p_inventory_item_id;
1531: ELSIF p_transaction_type_id = inv_globals.g_type_inv_lot_translate -- Lot Translate 84 Added bug4096035
1551: , x_shelf_life_days
1552: , x_allowed_units_lookup_code
1553: , x_lot_status_enabled
1554: , x_default_lot_status_id
1555: FROM mtl_system_items_vl msik /* Bug 5581528 */
1556: WHERE msik.lot_translate_enabled = 'Y'
1557: AND msik.organization_id = p_organization_id
1558: AND msik.inventory_item_id = p_inventory_item_id; /*Added bug4096035*/
1559: ELSE
1574: , x_primary_uom_code
1575: , x_shelf_life_code
1576: , x_shelf_life_days
1577: , x_allowed_units_lookup_code
1578: FROM mtl_system_items_vl msik /* Bug 5581528 */
1579: WHERE msik.organization_id = p_organization_id
1580: AND msik.inventory_item_id = p_inventory_item_id;
1581: END IF;
1582: END IF;
1690: , x_SECONDARY_DEFAULT_IND
1691: , x_TRACKING_QUANTITY_IND
1692: , x_DUAL_UOM_DEVIATION_HIGH
1693: , x_DUAL_UOM_DEVIATION_LOW
1694: FROM mtl_system_items_vl msik /* Bug 5581528 */
1695: WHERE msik.lot_split_enabled = 'Y'
1696: AND msik.organization_id = p_organization_id
1697: AND msik.inventory_item_id = p_inventory_item_id;
1698: ELSE
1753: , x_SECONDARY_DEFAULT_IND
1754: , x_TRACKING_QUANTITY_IND
1755: , x_DUAL_UOM_DEVIATION_HIGH
1756: , x_DUAL_UOM_DEVIATION_LOW
1757: FROM mtl_system_items_vl msik /* Bug 5581528 */
1758: WHERE msik.lot_merge_enabled = 'Y'
1759: AND msik.organization_id = p_organization_id
1760: AND msik.inventory_item_id = p_inventory_item_id;
1761: ELSIF p_transaction_type_id = inv_globals.g_type_inv_lot_translate -- Lot Translate 84 Added bug4096035
1814: , x_SECONDARY_DEFAULT_IND
1815: , x_TRACKING_QUANTITY_IND
1816: , x_DUAL_UOM_DEVIATION_HIGH
1817: , x_DUAL_UOM_DEVIATION_LOW
1818: FROM mtl_system_items_vl msik /* Bug 5581528 */
1819: WHERE msik.lot_translate_enabled = 'Y'
1820: AND msik.organization_id = p_organization_id
1821: AND msik.inventory_item_id = p_inventory_item_id; /*Added bug4096035*/
1822:
1873: , x_SECONDARY_DEFAULT_IND
1874: , x_TRACKING_QUANTITY_IND
1875: , x_DUAL_UOM_DEVIATION_HIGH
1876: , x_DUAL_UOM_DEVIATION_LOW
1877: FROM mtl_system_items_vl msik /* Bug 5581528 */
1878: WHERE msik.organization_id = p_organization_id
1879: AND msik.inventory_item_id = p_inventory_item_id;
1880: END IF;
1881: END IF;
1993: l_cross_ref := lpad(Rtrim(p_concatenated_segments, '%'), g_gtin_code_length, '00000000000000');
1994:
1995: l_append:=wms_deploy.get_item_suffix_for_lov(p_concatenated_segments);
1996:
1997: l_sql_stmt1 := l_sql_stmt1 || ' FROM mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd'
1998: || ' WHERE msik.concatenated_segments LIKE (''' || p_concatenated_segments ||l_append|| ''')'
1999: || ' AND msik.organization_id = ' || p_organization_id
2000: || ' AND (msik.lot_status_enabled = ''Y'' OR msik.serial_status_enabled = ''Y'')';
2001:
1998: || ' WHERE msik.concatenated_segments LIKE (''' || p_concatenated_segments ||l_append|| ''')'
1999: || ' AND msik.organization_id = ' || p_organization_id
2000: || ' AND (msik.lot_status_enabled = ''Y'' OR msik.serial_status_enabled = ''Y'')';
2001:
2002: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'
2003: || 'mtl_cross_references mcr, mtl_onhand_quantities_detail moqd'
2004: || ' WHERE msik.organization_id = ' || p_organization_id
2005: || ' AND (msik.lot_status_enabled = ''Y'' OR msik.serial_status_enabled = ''Y'' )'
2006: || ' AND msik.inventory_item_id = mcr.inventory_item_id'
2086: NVL(msik.SECONDARY_DEFAULT_IND,''),
2087: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2088: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2089: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2090: FROM mtl_system_items_vl msik, wsh_delivery_details dd, wsh_delivery_assignments da, wsh_new_deliveries nd /* Bug 5581528 */
2091: WHERE msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
2092: AND msik.organization_id = p_organization_id
2093: AND msik.inventory_item_id = dd.inventory_item_id
2094: AND nd.delivery_id = p_delivery_id
2144: NVL(msik.SECONDARY_DEFAULT_IND,''),
2145: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2146: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2147: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2148: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2149: wsh_delivery_details dd,
2150: wsh_delivery_assignments da,
2151: wsh_new_deliveries nd,
2152: mtl_cross_references mcr
2247: NVL(SECONDARY_DEFAULT_IND,''),
2248: NVL(TRACKING_QUANTITY_IND,'P'),
2249: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2250: NVL(DUAL_UOM_DEVIATION_LOW,0)
2251: --FROM mtl_system_items_vl /* Bug 5581528 */
2252: from mtl_system_items_vl msik /*bug7626228*/
2253: WHERE organization_id = p_organization_id
2254: AND concatenated_segments LIKE (p_concatenated_segments||l_append)
2255: /*AND INV_MATERIAL_STATUS_GRP.loc_valid_for_item(p_locator_id,
2248: NVL(TRACKING_QUANTITY_IND,'P'),
2249: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2250: NVL(DUAL_UOM_DEVIATION_LOW,0)
2251: --FROM mtl_system_items_vl /* Bug 5581528 */
2252: from mtl_system_items_vl msik /*bug7626228*/
2253: WHERE organization_id = p_organization_id
2254: AND concatenated_segments LIKE (p_concatenated_segments||l_append)
2255: /*AND INV_MATERIAL_STATUS_GRP.loc_valid_for_item(p_locator_id,
2256: p_organization_id,
2309: NVL(SECONDARY_DEFAULT_IND,''),
2310: NVL(TRACKING_QUANTITY_IND,'P'),
2311: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2312: NVL(DUAL_UOM_DEVIATION_LOW,0)
2313: FROM mtl_system_items_vl msik, mtl_cross_references mcr /* Bug 5581528 */
2314: WHERE msik.organization_id = p_organization_id
2315: AND msik.inventory_item_id = mcr.inventory_item_id
2316: AND mcr.cross_reference_type = g_gtin_cross_ref_type
2317: AND mcr.cross_reference LIKE l_cross_ref
2366: NVL(msik.SECONDARY_DEFAULT_IND,''),
2367: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2368: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2369: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2370: FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit /* Bug 5581528 */
2371: WHERE msik.organization_id = p_organization_id
2372: AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
2373: AND msik.inventory_item_id = mpit.inventory_item_id
2374: AND mpit.physical_inventory_id = p_physical_inventory_id
2438: NVL(msik.SECONDARY_DEFAULT_IND,''),
2439: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2440: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2441: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2442: FROM mtl_system_items_vl msik, mtl_physical_inventory_tags mpit, mtl_cross_references mcr /* Bug 5581528 */
2443: WHERE msik.organization_id = p_organization_id
2444: AND msik.inventory_item_id = mpit.inventory_item_id
2445: AND mpit.physical_inventory_id = p_physical_inventory_id
2446: AND mpit.organization_id = p_organization_id
2623: NVL(SECONDARY_DEFAULT_IND,''),
2624: NVL(TRACKING_QUANTITY_IND,'P'),
2625: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2626: NVL(DUAL_UOM_DEVIATION_LOW,0)
2627: FROM mtl_system_items_vl /* Bug 5581528 */
2628: WHERE organization_id = p_organization_id
2629: AND container_item_flag = 'Y'
2630: AND mtl_transactions_enabled_flag = 'Y'
2631: AND concatenated_segments LIKE (p_concatenated_segments||l_append)
2679: NVL(SECONDARY_DEFAULT_IND,''),
2680: NVL(TRACKING_QUANTITY_IND,'P'),
2681: NVL(DUAL_UOM_DEVIATION_HIGH,0),
2682: NVL(DUAL_UOM_DEVIATION_LOW,0)
2683: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2684: mtl_cross_references mcr
2685: WHERE msik.organization_id = p_organization_id
2686: AND msik.container_item_flag = 'Y'
2687: AND msik.mtl_transactions_enabled_flag = 'Y'
2795: NVL(msik.SECONDARY_DEFAULT_IND,''),
2796: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2797: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2798: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2799: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2800: mtl_cycle_count_items mcci
2801: WHERE msik.organization_id = p_organization_id
2802: AND msik.inventory_item_id = mcci.inventory_item_id
2803: AND mcci.cycle_count_header_id = p_cycle_count_header_id
2862: NVL(msik.SECONDARY_DEFAULT_IND,''),
2863: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2864: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2865: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2866: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2867: mtl_cycle_count_items mcci, mtl_cross_references mcr
2868: WHERE msik.organization_id = p_organization_id
2869: AND msik.inventory_item_id = mcci.inventory_item_id
2870: AND mcci.cycle_count_header_id = p_cycle_count_header_id
2925: NVL(msik.SECONDARY_DEFAULT_IND,''),
2926: NVL(msik.TRACKING_QUANTITY_IND,'P'),
2927: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
2928: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
2929: FROM mtl_system_items_vl msik, /* Bug 5581528 */
2930: mtl_cycle_count_entries mcce
2931: WHERE msik.organization_id = p_organization_id
2932: AND msik.concatenated_segments LIKE (p_concatenated_segments||l_append)
2933: AND msik.inventory_item_id = mcce.inventory_item_id
2998: NVL(msik.SECONDARY_DEFAULT_IND,''),
2999: NVL(msik.TRACKING_QUANTITY_IND,'P'),
3000: NVL(msik.DUAL_UOM_DEVIATION_HIGH,0),
3001: NVL(msik.DUAL_UOM_DEVIATION_LOW,0)
3002: FROM mtl_system_items_vl msik, /* Bug 5581528 */
3003: mtl_cycle_count_entries mcce, mtl_cross_references mcr
3004: WHERE msik.organization_id = p_organization_id
3005: AND msik.inventory_item_id = mcce.inventory_item_id
3006: AND mcce.cycle_count_header_id = p_cycle_count_header_id
3197: NVL(a.SECONDARY_DEFAULT_IND,''),
3198: NVL(a.TRACKING_QUANTITY_IND,'P'),
3199: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
3200: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
3201: FROM mtl_txn_request_lines b , mtl_system_items_vl a /* Bug 5581528 */ --BUG12820609 changed order of MTRL and MSI
3202: WHERE b.lpn_id = p_lpn_id
3203: AND b.organization_id = p_organization_id
3204: AND b.inspection_status is not null -- 8405606
3205: AND b.organization_id = a.organization_id
3256: NVL(a.SECONDARY_DEFAULT_IND,''),
3257: NVL(a.TRACKING_QUANTITY_IND,'P'),
3258: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
3259: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
3260: FROM mtl_txn_request_lines b, mtl_system_items_vl a, /* Bug 5581528 */
3261: mtl_cross_references mcr
3262: WHERE b.lpn_id = p_lpn_id
3263: AND b.organization_id = p_organization_id
3264: AND b.inspection_status is not null -- 8405606
3322: NVL(a.SECONDARY_DEFAULT_IND,''),
3323: NVL(a.TRACKING_QUANTITY_IND,'P'),
3324: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
3325: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
3326: FROM mtl_txn_request_lines b, mtl_system_items_vl a /* Bug 5581528 */
3327: WHERE b.lpn_id = p_lpn_id
3328: AND b.organization_id = p_organization_id
3329: AND b.inspection_status is not null -- 8405606
3330: AND b.organization_id = a.organization_id
3382: NVL(a.SECONDARY_DEFAULT_IND,''),
3383: NVL(a.TRACKING_QUANTITY_IND,'P'),
3384: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
3385: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
3386: FROM mtl_txn_request_lines b, mtl_system_items_vl a /* Bug 5581528 */
3387: WHERE b.lpn_id = p_lpn_id
3388: AND b.organization_id = p_organization_id
3389: AND b.inspection_status is not null -- 8405606
3390: AND b.organization_id = a.organization_id
3484: NVL(msi.SECONDARY_DEFAULT_IND,''),
3485: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3486: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3487: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3488: 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
3489: WHERE moq.organization_id = p_org_id
3490: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3491: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3492: AND moq.containerized_flag = 2
3541: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3542: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3543: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3544: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3545: mtl_system_items_vl msi, /* Bug 5581528 */
3546: mtl_cross_references mcr
3547: WHERE moq.organization_id = p_org_id
3548: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3549: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3602: NVL(msi.SECONDARY_DEFAULT_IND,''),
3603: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3604: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3605: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3606: FROM mtl_onhand_quantities_detail moq, mtl_system_items_vl msi /* Bug 5581528 */
3607: -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3608: WHERE moq.organization_id = p_org_id
3609: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3610: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3660: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3661: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3662: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3663: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
3664: mtl_system_items_vl msi, /* Bug 5581528 */
3665: mtl_cross_references mcr
3666: WHERE moq.organization_id = p_org_id
3667: AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
3668: AND NVL(moq.locator_id, -1) = NVL(p_locator_id, NVL(moq.locator_id, -1))
3730: NVL(msi.SECONDARY_DEFAULT_IND,''),
3731: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3732: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3733: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3734: FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */
3735: WHERE wlc.organization_id = p_org_id
3736: AND wlc.parent_lpn_id = TO_NUMBER(p_lpn_id)
3737: AND msi.inventory_item_id = wlc.inventory_item_id
3738: AND msi.organization_id = wlc.organization_id
3784: NVL(msi.SECONDARY_DEFAULT_IND,''),
3785: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3786: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3787: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3788: FROM mtl_system_items_vl msi, /* Bug 5581528 */
3789: wms_lpn_contents wlc,
3790: mtl_cross_references mcr
3791: WHERE wlc.organization_id = p_org_id
3792: AND wlc.parent_lpn_id = TO_NUMBER(p_lpn_id)
3855: NVL(msi.SECONDARY_DEFAULT_IND,''),
3856: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3857: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3858: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3859: FROM mtl_system_items_vl msi /* Bug 5581528 */
3860: WHERE msi.organization_id = p_org_id
3861: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3862: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
3863: AND msi.concatenated_segments LIKE (p_item||l_append)
3908: NVL(msi.SECONDARY_DEFAULT_IND,''),
3909: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3910: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3911: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3912: FROM mtl_system_items_vl msi, /* Bug 5581528 */
3913: mtl_cross_references mcr
3914: WHERE msi.organization_id = p_org_id
3915: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3916: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
3966: NVL(msi.SECONDARY_DEFAULT_IND,''),
3967: NVL(msi.TRACKING_QUANTITY_IND,'P'),
3968: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
3969: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
3970: 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
3971: WHERE msi.organization_id = p_org_id
3972: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
3973: AND msi.concatenated_segments LIKE (p_item||l_append)
3974: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
4025: NVL(msi.TRACKING_QUANTITY_IND,'P'),
4026: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
4027: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
4028: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
4029: mtl_system_items_vl msi, /* Bug 5581528 */
4030: mtl_cross_references mcr
4031: WHERE msi.organization_id = p_org_id
4032: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
4033: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
4089: NVL(SECONDARY_DEFAULT_IND,''),
4090: NVL(TRACKING_QUANTITY_IND,'P'),
4091: NVL(DUAL_UOM_DEVIATION_HIGH,0),
4092: NVL(DUAL_UOM_DEVIATION_LOW,0)
4093: FROM mtl_system_items_vl /* Bug 5581528 */
4094: WHERE organization_id = p_org_id
4095: AND NVL(container_item_flag, 'N') = NVL(p_container_item_flag, NVL(container_item_flag, 'N'))
4096: AND NVL(serial_number_control_code, 1) IN (1, 2, 5, 6)
4097: AND concatenated_segments LIKE (p_item||l_append)
4142: NVL(SECONDARY_DEFAULT_IND,''),
4143: NVL(TRACKING_QUANTITY_IND,'P'),
4144: NVL(DUAL_UOM_DEVIATION_HIGH,0),
4145: NVL(DUAL_UOM_DEVIATION_LOW,0)
4146: FROM mtl_system_items_vl msik, /* Bug 5581528 */
4147: mtl_cross_references mcr
4148: WHERE msik.organization_id = p_org_id
4149: AND NVL(msik.container_item_flag, 'N') = NVL(p_container_item_flag, NVL(msik.container_item_flag, 'N'))
4150: AND NVL(msik.serial_number_control_code, 1) IN (1, 2, 5, 6)
4278: NVL(a.SECONDARY_DEFAULT_IND,''),
4279: NVL(a.TRACKING_QUANTITY_IND,'P'),
4280: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4281: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4282: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4283: WHERE b.to_organization_id = p_organization_id
4284: AND b.po_header_id = p_source_id
4285: AND b.inspection_status_code = 'NOT INSPECTED'
4286: AND b.routing_id = 2 /* Inspection routing */
4344: NVL(a.SECONDARY_DEFAULT_IND,''),
4345: NVL(a.TRACKING_QUANTITY_IND,'P'),
4346: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4347: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4348: FROM mtl_system_items_vl a, /* Bug 5581528 */
4349: rcv_transactions_v b,
4350: mtl_cross_references mcr
4351: WHERE b.to_organization_id = p_organization_id
4352: AND b.po_header_id = p_source_id
4409: NVL(a.SECONDARY_DEFAULT_IND,''),
4410: NVL(a.TRACKING_QUANTITY_IND,'P'),
4411: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4412: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4413: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4414: WHERE b.to_organization_id = p_organization_id
4415: AND b.shipment_header_id = p_source_id
4416: AND b.receipt_source_code <> 'VENDOR'
4417: AND b.inspection_status_code = 'NOT INSPECTED'
4470: NVL(a.SECONDARY_DEFAULT_IND,''),
4471: NVL(a.TRACKING_QUANTITY_IND,'P'),
4472: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4473: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4474: FROM mtl_system_items_vl a, /* Bug 5581528 */
4475: rcv_transactions_v b,
4476: mtl_cross_references mcr
4477: WHERE b.to_organization_id = p_organization_id
4478: AND b.shipment_header_id = p_source_id
4537: NVL(a.SECONDARY_DEFAULT_IND,''),
4538: NVL(a.TRACKING_QUANTITY_IND,'P'),
4539: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4540: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4541: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4542: WHERE b.to_organization_id = p_organization_id
4543: AND b.oe_order_header_id = p_source_id
4544: AND b.receipt_source_code <> 'VENDOR'
4545: AND b.inspection_status_code = 'NOT INSPECTED'
4598: NVL(a.SECONDARY_DEFAULT_IND,''),
4599: NVL(a.TRACKING_QUANTITY_IND,'P'),
4600: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4601: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4602: FROM mtl_system_items_vl a, /* Bug 5581528 */
4603: rcv_transactions_v b,
4604: mtl_cross_references mcr
4605: WHERE b.to_organization_id = p_organization_id
4606: AND b.oe_order_header_id = p_source_id
4665: NVL(a.SECONDARY_DEFAULT_IND,''),
4666: NVL(a.TRACKING_QUANTITY_IND,'P'),
4667: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4668: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4669: FROM mtl_system_items_vl a, rcv_transactions_v b /* Bug 5581528 */
4670: WHERE b.to_organization_id = p_organization_id
4671: AND b.shipment_header_id = TO_CHAR(p_source_id)
4672: AND b.inspection_status_code = 'NOT INSPECTED'
4673: AND b.routing_id = 2 /* Inspection routing */
4731: NVL(a.SECONDARY_DEFAULT_IND,''),
4732: NVL(a.TRACKING_QUANTITY_IND,'P'),
4733: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4734: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4735: FROM mtl_system_items_vl a, /* Bug 5581528 */
4736: rcv_transactions_v b,
4737: mtl_cross_references mcr
4738: WHERE b.to_organization_id = p_organization_id
4739: AND b.shipment_header_id = TO_CHAR(p_source_id)
4798: NVL(a.SECONDARY_DEFAULT_IND,''),
4799: NVL(a.TRACKING_QUANTITY_IND,'P'),
4800: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4801: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4802: FROM mtl_system_items_vl a, /* Bug 5581528 */
4803: rcv_transactions_v b,
4804: (SELECT DISTINCT rsl.shipment_header_id
4805: FROM po_requisition_lines pol,
4806: rcv_shipment_lines rsl
4866: NVL(a.SECONDARY_DEFAULT_IND,''),
4867: NVL(a.TRACKING_QUANTITY_IND,'P'),
4868: NVL(a.DUAL_UOM_DEVIATION_HIGH,0),
4869: NVL(a.DUAL_UOM_DEVIATION_LOW,0)
4870: FROM mtl_system_items_vl a, /* Bug 5581528 */
4871: rcv_transactions_v b,
4872: mtl_cross_references mcr,
4873: (SELECT DISTINCT rsl.shipment_header_id
4874: FROM po_requisition_lines pol,
5037: NVL(msi.SECONDARY_DEFAULT_IND,''),
5038: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5039: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5040: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5041: 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
5042: WHERE moq.containerized_flag = 2
5043: AND moq.organization_id = p_org_id
5044: AND moq.inventory_item_id = msi.inventory_item_id
5045: AND msi.concatenated_segments LIKE (p_item||l_append)
5091: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5092: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5093: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5094: FROM mtl_onhand_quantities_detail moq, -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
5095: mtl_system_items_vl msi, /* Bug 5581528 */
5096: mtl_cross_references mcr
5097: WHERE moq.containerized_flag = 2
5098: AND moq.organization_id = p_org_id
5099: AND moq.inventory_item_id = msi.inventory_item_id
5153: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5154: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5155: -- bug 5172851, wms_lpn_contents_v is replaced with
5156: -- wms_lpn_contents for performance reason
5157: FROM mtl_system_items_vl msi, wms_lpn_contents wlc /* Bug 5581528 */
5158: WHERE msi.concatenated_segments LIKE (p_item||l_append)
5159: AND msi.inventory_item_id = wlc.inventory_item_id
5160: AND msi.organization_id = p_org_id
5161: AND wlc.parent_lpn_id = p_lpn_id
5206: NVL(msi.SECONDARY_DEFAULT_IND,''),
5207: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5208: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5209: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5210: FROM mtl_system_items_vl msi, /* Bug 5581528 */
5211: -- bug 5172851, wms_lpn_contents_v is replaced with
5212: -- wms_lpn_contents for performance reason
5213: wms_lpn_contents wlc,
5214: mtl_cross_references mcr
5294: NVL(SECONDARY_DEFAULT_IND,''),
5295: NVL(TRACKING_QUANTITY_IND,'P'),
5296: NVL(DUAL_UOM_DEVIATION_HIGH,0),
5297: NVL(DUAL_UOM_DEVIATION_LOW,0)
5298: FROM mtl_system_items_vl /* Bug 5581528 */
5299: WHERE organization_id = p_org_id
5300: AND concatenated_segments LIKE (p_item||l_append)
5301:
5302: --Changes for GTIN
5345: NVL(SECONDARY_DEFAULT_IND,''),
5346: NVL(TRACKING_QUANTITY_IND,'P'),
5347: NVL(DUAL_UOM_DEVIATION_HIGH,0),
5348: NVL(DUAL_UOM_DEVIATION_LOW,0)
5349: FROM mtl_system_items_vl msik, /* Bug 5581528 */
5350: mtl_cross_references mcr
5351: WHERE msik.organization_id = p_org_id
5352: AND msik.inventory_item_id = mcr.inventory_item_id
5353: AND mcr.cross_reference_type = g_gtin_cross_ref_type
5410: NVL(msi.SECONDARY_DEFAULT_IND,''),
5411: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5412: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5413: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5414: FROM mtl_serial_numbers msn, mtl_system_items_vl msi /* Bug 5581528 */
5415: WHERE msn.current_organization_id = p_org_id
5416: AND msn.serial_number = p_serial
5417: AND msn.inventory_item_id = msi.inventory_item_id
5418: AND msi.organization_id = msn.current_organization_id
5465: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5466: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5467: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5468: FROM mtl_serial_numbers msn,
5469: mtl_system_items_vl msi, /* Bug 5581528 */
5470: mtl_cross_references mcr
5471: WHERE msn.current_organization_id = p_org_id
5472: AND msn.serial_number = p_serial
5473: AND msn.inventory_item_id = msi.inventory_item_id
5535: NVL(msi.SECONDARY_DEFAULT_IND,''),
5536: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5537: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5538: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5539: FROM mtl_system_items_vl msi, wms_lpn_contents wlpnc /* Bug 5581528 */
5540: WHERE wlpnc.parent_lpn_id = p_lpn_id
5541: AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5542: AND wlpnc.organization_id = p_org_id
5543: AND msi.organization_id = wlpnc.organization_id
5587: NVL(msi.SECONDARY_DEFAULT_IND,''),
5588: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5589: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5590: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5591: FROM mtl_system_items_vl msi, mtl_serial_numbers msn /* Bug 5581528 */
5592: WHERE msn.lpn_id = p_lpn_id
5593: AND msn.last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5594: AND msn.current_organization_id = p_org_id
5595: AND msi.organization_id = msn.current_organization_id
5642: NVL(msi.SECONDARY_DEFAULT_IND,''),
5643: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5644: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5645: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5646: FROM mtl_system_items_vl msi, /* Bug 5581528 */
5647: wms_lpn_contents wlpnc,
5648: mtl_cross_references mcr
5649: WHERE wlpnc.parent_lpn_id = p_lpn_id
5650: AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5701: NVL(msi.SECONDARY_DEFAULT_IND,''),
5702: NVL(msi.TRACKING_QUANTITY_IND,'P'),
5703: NVL(msi.DUAL_UOM_DEVIATION_HIGH,0),
5704: NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
5705: FROM mtl_system_items_vl msi, /* Bug 5581528 */
5706: mtl_serial_numbers msn,
5707: mtl_cross_references mcr
5708: WHERE msn.lpn_id = p_lpn_id
5709: AND msn.last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
5745: OPEN x_vehicle FOR
5746: SELECT msi.concatenated_segments
5747: , msi.description
5748: , msi.inventory_item_id
5749: FROM mtl_system_items_vl msi /* Bug 5581528 */
5750: WHERE msi.organization_id = p_organization_id
5751: AND msi.concatenated_segments LIKE (p_concatenated_segments)
5752: AND msi.vehicle_item_flag = 'Y'
5753: ORDER BY UPPER(msi.concatenated_segments);
5891: ELSE
5892: BEGIN
5893: SELECT primary_uom_code
5894: INTO l_primary_uom_code
5895: FROM mtl_system_items
5896: WHERE organization_id = p_organization_id
5897: AND inventory_item_id = p_item_id;
5898: EXCEPTION
5899: WHEN OTHERS THEN
6027:
6028:
6029: IF P_LPN IS NULL THEN
6030:
6031: l_sql_stmt1 := l_sql_stmt1 || ' FROM mtl_system_items_vl msik, mtl_onhand_quantities_detail moqd'
6032: || ' WHERE msik.concatenated_segments LIKE (''' || p_concatenated_segments ||l_append|| ''')' -- Bug 9369327
6033:
6034: || ' AND msik.organization_id = ' || p_organization_id
6035: || ' AND (msik.serial_number_control_code in (1,6) OR msik.serial_status_enabled = ''Y'' )'
6036: || ' AND moqd.inventory_item_id = msik.inventory_item_id '
6037: || ' AND moqd.organization_id = msik.organization_id '
6038: || ' AND moqd.lpn_id is NULL ' ;
6039:
6040: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'
6041: || 'mtl_cross_references mcr, mtl_onhand_quantities_detail moqd'
6042: || ' WHERE msik.organization_id = ' || p_organization_id
6043: || ' AND (msik.serial_number_control_code in (1,6) OR msik.serial_status_enabled = ''Y'' )'
6044: || ' AND msik.inventory_item_id = mcr.inventory_item_id'
6064: l_sql_stmt_xref := l_sql_stmt_xref || ' AND moqd.locator_id = ' || p_locator_id;
6065: END IF;
6066: END IF;
6067: ELSE
6068: 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'
6069: || ' WHERE WLPN.LICENSE_PLATE_NUMBER = ''' || p_lpn || ''''
6070: || ' AND WLC.PARENT_LPN_ID = WLPN.LPN_ID '
6071: || ' AND MSIK.INVENTORY_ITEM_ID = WLC.INVENTORY_ITEM_ID '
6072: || ' AND msik.concatenated_segments LIKE (''' || p_concatenated_segments ||l_append|| ''')' -- Bug 9369327
6074: || ' AND (msik.serial_number_control_code in (1,6) OR msik.serial_status_enabled = ''Y'' )'
6075: || ' AND moqd.organization_id = msik.organization_id'
6076: || ' AND moqd.inventory_item_id = msik.inventory_item_id';
6077:
6078: l_sql_stmt_xref := l_sql_stmt_xref || ' FROM mtl_system_items_vl msik,'
6079: || 'mtl_cross_references mcr, mtl_onhand_quantities_detail moqd ,WMS_LICENSE_PLATE_NUMBERS WLPN ,WMS_LPN_CONTENTS WLC'
6080: || ' WHERE WLPN.LICENSE_PLATE_NUMBER = ''' || p_lpn || ''''
6081: || ' AND WLC.PARENT_LPN_ID = WLPN.LPN_ID '
6082: || ' AND MSIK.INVENTORY_ITEM_ID = WLC.INVENTORY_ITEM_ID '
6129: SELECT primary_uom_code,
6130: SECONDARY_UOM_CODE
6131: INTO l_primary_uom_code,
6132: l_sec_uom_code
6133: FROM mtl_system_items
6134: WHERE organization_id = p_organization_id
6135: AND inventory_item_id = p_item_id;
6136: EXCEPTION
6137: WHEN OTHERS THEN