[Home] [Help]
128: p_org_id => l_org_id);
129:
130: SELECT nvl(msi.comms_nl_trackable_flag, 'N')
131: INTO l_ib_trackable_flag
132: FROM mtl_system_items msi
133: WHERE msi.inventory_item_id = l_inventory_item_id
134: AND msi.organization_id = l_organization_id;
135:
136: IF l_ib_trackable_flag = 'Y' THEN
333: p_org_id => l_line_tbl(l_ind).org_id);
334:
335: SELECT nvl(msi.comms_nl_trackable_flag,'N')
336: INTO l_ib_trackable_flag
337: FROM mtl_system_items msi
338: WHERE msi.inventory_item_id = l_line_tbl(l_ind).inventory_item_id
339: AND msi.organization_id = l_organization_id;
340:
341: /* if trackable populate it for the final out table */
630: x_item_attrib_rec.bom_item_type,
631: x_item_attrib_rec.pick_components_flag,
632: x_item_attrib_rec.model_item_id,
633: x_item_attrib_rec.primary_uom_code
634: FROM mtl_system_items
635: WHERE inventory_item_id = p_inventory_item_id
636: AND organization_id = p_organization_id;
637:
638: -- get org attributes aswell
635: WHERE inventory_item_id = p_inventory_item_id
636: AND organization_id = p_organization_id;
637:
638: -- get org attributes aswell
639: -- Ib_item_instance_class column may or may not exists in mtl_system_items_b.
640: -- Hence used a Dynamic sal based on the existance of this column in ALL_TAB_COLUMNS.
641:
642: -- For Bug 3431768
643: l_return := FND_INSTALLATION.get_app_info('INV',l_status,l_industry,l_oracle_schema);
653: /* Modified for bug 15896185 - Online patching compatible fix
654: select 'X'
655: into l_exists
656: from all_tab_columns
657: where table_name = 'MTL_SYSTEM_ITEMS_B'
658: and column_name = 'IB_ITEM_INSTANCE_CLASS'
659: and OWNER = l_oracle_schema
660: and rownum < 2;
661: Modified for bug 15896185 - Online patching compatible fix*/
662: select 'X'
663: into l_exists
664: from user_synonyms syn,
665: all_tab_columns col
666: where syn.synonym_name = 'MTL_SYSTEM_ITEMS_B'
667: and col.column_name = 'IB_ITEM_INSTANCE_CLASS'
668: and col.OWNER = l_oracle_schema
669: AND col.owner = syn.table_owner
670: AND col.table_name = syn.table_name
673: Exception
674: when no_data_found then
675: l_exists := 'N';
676: End;
677: l_sql_stmt := 'select ib_item_instance_class from MTL_SYSTEM_ITEMS_B '||
678: 'where inventory_item_id = :item_id '||
679: 'and organization_id = :vld_org_id';
680: --
681: IF l_exists = 'X' THEN
696: /* Modified for bug 15896185 - Online patching compatible fix
697: select 'X'
698: into l_exists
699: from all_tab_columns
700: where table_name = 'MTL_SYSTEM_ITEMS_B'
701: and column_name = 'CONFIG_MODEL_TYPE'
702: and OWNER = l_oracle_schema
703: and rownum < 2;
704: Modified for bug 15896185 - Online patching compatible fix*/
705: select 'X'
706: into l_exists
707: from user_synonyms syn,
708: all_tab_columns col
709: where syn.synonym_name = 'MTL_SYSTEM_ITEMS_B'
710: and col.column_name = 'CONFIG_MODEL_TYPE'
711: and col.OWNER = l_oracle_schema
712: AND col.owner = syn.table_owner
713: AND col.table_name = syn.table_name
716: Exception
717: when no_data_found then
718: l_exists := 'N';
719: End;
720: l_sql_stmt := 'select config_model_type from MTL_SYSTEM_ITEMS_B '||
721: 'where inventory_item_id = :item_id '||
722: 'and organization_id = :vld_org_id';
723: --
724: IF l_exists = 'X' THEN
5148: msi.primary_uom_code primary_uom,
5149: abs(mmt.primary_quantity) primary_quantity,
5150: mmt.transaction_type_id transaction_type_id,
5151: mmt.transaction_action_id transaction_action_id
5152: FROM mtl_system_items msi,
5153: mtl_unit_transactions mut,
5154: mtl_material_transactions mmt
5155: WHERE mmt.transaction_id = p_txn_id
5156: AND mmt.transaction_id = mut.transaction_id(+)
5174: msi.primary_uom_code primary_uom,
5175: abs(mtln.primary_quantity) primary_quantity,
5176: mmt.transaction_type_id transaction_type_id,
5177: mmt.transaction_action_id transaction_action_id
5178: FROM mtl_system_items msi,
5179: mtl_unit_transactions mut,
5180: mtl_transaction_lot_numbers mtln,
5181: mtl_material_transactions mmt
5182: WHERE mmt.transaction_id = p_txn_id