APPS.INV_OPM_ITEM_MIGRATION SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 93

       SELECT *
         FROM gr_item_general
        WHERE item_code = p_item_code;

Line: 100

           SELECT name_description
             FROM gr_multilingual_name_tl
            WHERE language = userenv('LANG') and
                  label_code = '11007' and
                  item_code = p_item_code;

Line: 107

       SELECT profile_option_value
         FROM fnd_profile_options a, fnd_profile_option_values b
        WHERE b.level_id = 10001 and
              a.profile_option_id = b.profile_option_id and
              a.profile_option_name = 'FM_YIELD_TYPE';

Line: 116

           SELECT std_um
             FROM sy_uoms_typ
            WHERE um_type = v_um_type;

Line: 170

      x_reg_item_rec.DELETE_MARK        := 0;

Line: 173

      x_reg_item_rec.LAST_UPDATE_DATE   := l_reg_rec.last_update_date;

Line: 174

      x_reg_item_rec.LAST_UPDATED_BY    := l_reg_rec.last_updated_by;

Line: 175

      x_reg_item_rec.LAST_UPDATE_LOGIN  := l_reg_rec.last_update_login;

Line: 269

  SELECT *
  FROM ic_item_mst_tl
  WHERE item_id = p_item_id;

Line: 303

			SELECT * INTO l_opm_item
			FROM ic_item_mst_b
			WHERE item_id = p_item_id;

Line: 332

		SELECT inventory_item_id
		INTO l_inventory_item_id
		FROM mtl_system_items_b
		WHERE
			segment1 = l_opm_item.item_no and
			ROWNUM = 1;

Line: 353

			SELECT mtl_system_items_s.nextval
			INTO l_inventory_item_id
			FROM dual
			WHERE rownum = 1;

Line: 361

	IF (l_opm_item.delete_mark = 1) THEN
		l_enabled_flag :=  'N';

Line: 370

		SELECT  cost_of_sales_account, sales_account,
			expense_account, encumbrance_account,
			process_enabled_flag, organization_code
		INTO    l_cost_of_sales_account, l_sales_account,
			l_expense_account, l_encumbrance_account,
			l_process_enabled_flag, l_organization_code
		FROM    mtl_parameters
		WHERE   organization_id = p_organization_id
		AND     rownum = 1;

Line: 403

		SELECT uom_code,unit_of_measure
		INTO l_prim_uom_code, l_prim_unit_of_meassure
		FROM sy_uoms_mst
		WHERE um_code = l_opm_item.item_um;

Line: 411

			SELECT uom_code,unit_of_measure
			INTO l_sec_uom_code, l_sec_unit_of_meassure
			FROM sy_uoms_mst
			WHERE um_code = l_opm_item.item_um2;

Line: 479

			SELECT
				auto_lot_alpha_prefix, start_auto_lot_number,
				child_lot_prefix, child_lot_starting_number
			INTO
				g_auto_lot_alpha_prefix, g_start_auto_lot_number,
				g_child_lot_prefix, g_child_lot_starting_number
			FROM gmi_migration_parameters
			WHERE rownum = 1;

Line: 517

			SELECT status_id
			INTO l_status_id
			FROM ic_lots_sts
			WHERE
				lot_status = l_opm_item.lot_status and
				status_id is not NULL;

Line: 528

			SELECT ic_matr_days, ic_hold_days
			INTO l_maturity_days, l_hold_days
			FROM ic_item_cpg
			WHERE
				item_id = l_opm_item.item_id;

Line: 692

		l_item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG := 'Y';

Line: 805

	l_item_rec.LAST_UPDATE_DATE := SYSDATE;

Line: 806

	l_item_rec.LAST_UPDATED_BY := l_opm_item.last_updated_by;

Line: 807

	l_item_rec.LAST_UPDATE_LOGIN := NULL;

Line: 812

	-- Call the API to create/ update item item
	IF (l_action = 'I') THEN

		l_event := 'ORG_ASSIGN';

Line: 817

			l_event := 'INSERT';

Line: 836

		INV_ITEM_PVT.Update_Item(
			p_item_rec => l_item_rec,
			P_Item_Category_Struct_Id => NULL,
			P_Inv_Install => INV_Item_Util.Appl_Install().INV,
			P_Master_Org_Id => p_master_org_id,
			P_Category_Set_Id => NULL,
			P_Item_Category_Id => NULL,
			P_Mode => 'UPDATE',
			P_Updateble_Item => NULL,
			P_Cost_Txn => NULL,
			P_Item_Cost_Details => NULL,
			P_Inv_Item_status_old => l_item_rec.INVENTORY_ITEM_STATUS_CODE,
			P_Default_Move_Order_Sub_Inv => '!',
			P_Default_Receiving_Sub_Inv => '!',
			P_Default_Shipping_Sub_Inv => '!');

Line: 859

		-- Update the item description in the TL tables.
		FOR d in c_ic_item_mst_tl LOOP
			UPDATE mtl_system_items_tl
			SET 	description         = d.item_desc1,
				long_description    = nvl(long_description, d.item_desc2),
				source_lang         = d.source_lang,
				last_update_date    = d.last_update_date,
				last_updated_by     = d.last_updated_by
			WHERE
				organization_id = p_organization_id AND
				inventory_item_id = l_item_rec.INVENTORY_ITEM_ID AND
				language = d.language;

Line: 874

		UPDATE ic_item_mst_b_mig
		SET
			inventory_item_id = x_inventory_item_id,
                        migrated_ind = 1,
			last_update_date = sysdate,
			last_updated_by = 0
		WHERE
			item_id = p_item_id AND
			organization_id = p_organization_id;

Line: 885

			INSERT INTO ic_item_mst_b_mig(
				item_id,
				organization_id,
				inventory_item_id,
				migrated_ind,
				creation_date,
				created_by,
				last_update_date,
				last_updated_by,
				last_update_login
			)values(
				p_item_id,
				p_organization_id,
				x_inventory_item_id,
				1,
				sysdate,
				0,
				sysdate,
				0,
				NULL
			);

Line: 1018

SELECT attribute_name FROM mtl_item_attributes
WHERE
	control_level = 1 AND
	attribute_name IN ( 'MTL_SYSTEM_ITEMS.TRACKING_QUANTITY_IND',
		'MTL_SYSTEM_ITEMS.ONT_PRICING_QTY_SOURCE',
		'MTL_SYSTEM_ITEMS.SECONDARY_DEFAULT_IND',
		'MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE',
		'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_HIGH',
		'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_LOW',
		'MTL_SYSTEM_ITEMS.ITEM_TYPE',
		'MTL_SYSTEM_ITEMS.AUTO_LOT_ALPHA_PREFIX',
		'MTL_SYSTEM_ITEMS.ENG_ITEM_FLAG',
		'MTL_SYSTEM_ITEMS.ITEM_TYPE',
		'MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE',
		'MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE',
		'MTL_SYSTEM_ITEMS.LOT_STATUS_ENABLED',
		'MTL_SYSTEM_ITEMS.START_AUTO_LOT_NUMBER') AND
	EXISTS (
		SELECT 1
		FROM mtl_parameters mo, mtl_parameters co
		WHERE
    			mo.organization_id = co.master_organization_id AND
    			Decode(mo.process_orgn_code, NULL, 'N', 'Y') <> Decode(co.process_orgn_code, NULL, 'N', 'Y'));

Line: 1087

SELECT col.descriptive_flex_context_code,
	col.application_column_name,
	col.end_user_column_name
FROM fnd_descr_flex_column_usages col,
	fnd_descr_flex_contexts cont
WHERE
	col.application_id = 551 and
	col.descriptive_flexfield_name = 'ITEM_FLEX' and
	col.enabled_flag = 'Y' and
	col.application_id = cont.application_id and
	col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
	col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
	cont.enabled_flag = 'Y' and
	col.application_column_name in (
		SELECT col2.application_column_name
		FROM fnd_descr_flex_column_usages col2,
			fnd_descr_flex_contexts cont2
		WHERE
			col2.application_id = 401 and
			col2.descriptive_flexfield_name = 'MTL_SYSTEM_ITEMS' and
			col2.enabled_flag = 'Y' and
			col.application_id = cont2.application_id and
			col.descriptive_flexfield_name = cont2.descriptive_flexfield_name and
			col.descriptive_flex_context_code = cont2.descriptive_flex_context_code and
			cont2.enabled_flag = 'Y' );

Line: 1115

SELECT col.descriptive_flex_context_code,
	col.application_column_name,
	col.end_user_column_name
FROM fnd_descr_flex_column_usages col,
	fnd_descr_flex_contexts cont
WHERE
	col.application_id = 551 and
	col.descriptive_flexfield_name = 'ITEM_FLEX' and
	col.enabled_flag = 'Y' and
	col.application_id = cont.application_id and
	col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
	col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
	cont.enabled_flag = 'Y';

Line: 1135

		SELECT cont.descriptive_flex_context_code
		INTO l_opm_context
		FROM fnd_descr_flex_contexts cont
		WHERE cont.application_id = 551 and
			cont.descriptive_flexfield_name = 'ITEM_FLEX' and
			cont.enabled_flag = 'Y' and
			cont.global_flag = 'N' and
			rownum = 1;

Line: 1149

		SELECT cont.descriptive_flex_context_code
		INTO l_odm_context
		FROM fnd_descr_flex_contexts cont
		WHERE cont.application_id = 401 and
			cont.descriptive_flexfield_name = 'MTL_SYSTEM_ITEMS' and
			cont.enabled_flag = 'Y' and
			cont.global_flag = 'N' and
			rownum = 1;

Line: 1350

		-- Select and lock the row to avoid errors associated with running this routine in
		-- parallel from routines using AD parrallel update logic.
		SELECT inventory_item_id, migrated_ind
		INTO g_inventory_item_id, l_migrated_ind
		FROM ic_item_mst_b_mig
		WHERE
			item_id = g_item_id AND
			organization_id = g_organization_id
		FOR UPDATE;

Line: 1368

				INSERT INTO ic_item_mst_b_mig(
					item_id,
					organization_id,
					inventory_item_id,
					migrated_ind,
					creation_date,
					created_by,
					last_update_date,
					last_updated_by,
					last_update_login
				)values(
					g_item_id,
					g_organization_id,
					NULL,
					0,
					sysdate,
					0,
					sysdate,
					0,
					NULL
				);

Line: 1395

			SELECT 1
			INTO dv
			FROM ic_item_mst_b_mig
			WHERE
				item_id = g_item_id AND
				organization_id = g_organization_id
			FOR UPDATE;

Line: 1410

		SELECT master_organization_id
		INTO l_master_organization_id
		FROM mtl_parameters
		WHERE
			organization_id = p_organization_id;

Line: 1416

		-- Select and lock the row to avoid errors associated with running this routine in
		-- parallel from routines using AD parrallel update logic.
		SELECT i.migrated_ind
		INTO l_migrated_ind_m
		FROM ic_item_mst_b_mig i
		WHERE
			i.organization_id = l_master_organization_id and
			i.item_id = p_item_id
		FOR UPDATE;

Line: 1430

					INSERT INTO ic_item_mst_b_mig(
						item_id,
						organization_id,
						inventory_item_id,
						migrated_ind,
						creation_date,
						created_by,
						last_update_date,
						last_updated_by,
						last_update_login
					)values(
						p_item_id,
						l_master_organization_id,
						NULL,
						0,
						sysdate,
						0,
						sysdate,
						0,
						NULL
					);

Line: 1457

				SELECT 1
				INTO dv
				FROM ic_item_mst_b_mig
				WHERE
					item_id = p_item_id AND
					organization_id = l_master_organization_id
				FOR UPDATE;

Line: 1643

SELECT m.organization_id, m.inventory_item_id, i.alt_itema,
    i.alt_itemb, i.match_type, i.upc_code, i.qcitem_id,
    i.qchold_res_code
FROM ic_item_mst_b i, ic_item_mst_b_mig m
WHERE
    i.item_id = m.item_id AND
    m.migrated_ind = 1;

Line: 1714

		SELECT flexfield_column_name, migrated_ind
		INTO l_flexfield_column_name, l_migrated_ind
		FROM gmi_obsolete_item_columns
		WHERE
			obsolete_column_name = p_obsolete_column_name AND
			migrated_ind = 1;

Line: 1746

		SELECT 1
		INTO l_count
		FROM fnd_tables t, fnd_columns c
		WHERE
			t.application_id = 401 AND
			t.table_name = 'MTL_SYSTEM_ITEMS_B' AND
			t.application_id = c.application_id AND
			t.table_id = c.table_id AND
			c.flexfield_application_id = 401 AND
			c.flexfield_name = 'MTL_SYSTEM_ITEMS' AND
			c.flexfield_usage_code = 'D' AND
			c.column_name = p_flexfield_column_name;

Line: 1780

		SELECT end_user_column_name
		INTO l_end_user_column_name
		FROM fnd_descr_flex_column_usages col2,
			fnd_descr_flex_contexts cont2
		WHERE
			col2.application_id IN (401, 551) and
			col2.descriptive_flexfield_name in ('MTL_SYSTEM_ITEMS', 'ITEM_FLEX') AND
			col2.enabled_flag = 'Y' and
			col2.application_id = cont2.application_id and
			col2.descriptive_flexfield_name = cont2.descriptive_flexfield_name and
			col2.descriptive_flex_context_code = cont2.descriptive_flex_context_code and
			cont2.enabled_flag = 'Y' AND
			col2.application_column_name = p_flexfield_column_name;

Line: 1814

			-- Update flexfield definition
			fnd_flex_dsc_api.set_session_mode ('customer_data');

Line: 1844

		SELECT DECODE (p_obsolete_column_name,
			'ALT_ITEMA', i.ALT_ITEMA,
			'ALT_ITEMB', i.ALT_ITEMB,
			'MATCH_TYPE', i.MATCH_TYPE,
			'UPC_CODE', i.UPC_CODE,
			'QCITEM_ID', i.QCITEM_ID,
			'QCHOLD_RES_CODE', i.QCHOLD_RES_CODE)
		INTO l_obsolete_column_value
		FROM dual
		WHERE rownum = 1;

Line: 1855

		UPDATE mtl_system_items_b
		SET
			ATTRIBUTE1 = DECODE (p_flexfield_column_name, 'ATTRIBUTE1', l_obsolete_column_value, ATTRIBUTE1),
			ATTRIBUTE2 = DECODE (p_flexfield_column_name, 'ATTRIBUTE2', l_obsolete_column_value, ATTRIBUTE2),
			ATTRIBUTE3 = DECODE (p_flexfield_column_name, 'ATTRIBUTE3', l_obsolete_column_value, ATTRIBUTE3),
			ATTRIBUTE4 = DECODE (p_flexfield_column_name, 'ATTRIBUTE4', l_obsolete_column_value, ATTRIBUTE4),
			ATTRIBUTE5 = DECODE (p_flexfield_column_name, 'ATTRIBUTE5', l_obsolete_column_value, ATTRIBUTE5),
			ATTRIBUTE6 = DECODE (p_flexfield_column_name, 'ATTRIBUTE6', l_obsolete_column_value, ATTRIBUTE6),
			ATTRIBUTE7 = DECODE (p_flexfield_column_name, 'ATTRIBUTE7', l_obsolete_column_value, ATTRIBUTE7),
			ATTRIBUTE8 = DECODE (p_flexfield_column_name, 'ATTRIBUTE8', l_obsolete_column_value, ATTRIBUTE8),
			ATTRIBUTE9 = DECODE (p_flexfield_column_name, 'ATTRIBUTE9', l_obsolete_column_value, ATTRIBUTE9),
			ATTRIBUTE10 = DECODE (p_flexfield_column_name, 'ATTRIBUTE10', l_obsolete_column_value, ATTRIBUTE10),
			ATTRIBUTE11 = DECODE (p_flexfield_column_name, 'ATTRIBUTE11', l_obsolete_column_value, ATTRIBUTE11),
			ATTRIBUTE12 = DECODE (p_flexfield_column_name, 'ATTRIBUTE12', l_obsolete_column_value, ATTRIBUTE12),
			ATTRIBUTE13 = DECODE (p_flexfield_column_name, 'ATTRIBUTE13', l_obsolete_column_value, ATTRIBUTE13),
			ATTRIBUTE14 = DECODE (p_flexfield_column_name, 'ATTRIBUTE14', l_obsolete_column_value, ATTRIBUTE14),
			ATTRIBUTE15 = DECODE (p_flexfield_column_name, 'ATTRIBUTE15', l_obsolete_column_value, ATTRIBUTE15),
			ATTRIBUTE16 = DECODE (p_flexfield_column_name, 'ATTRIBUTE16', l_obsolete_column_value, ATTRIBUTE16),
			ATTRIBUTE17 = DECODE (p_flexfield_column_name, 'ATTRIBUTE17', l_obsolete_column_value, ATTRIBUTE17),
			ATTRIBUTE18 = DECODE (p_flexfield_column_name, 'ATTRIBUTE18', l_obsolete_column_value, ATTRIBUTE18),
			ATTRIBUTE19 = DECODE (p_flexfield_column_name, 'ATTRIBUTE19', l_obsolete_column_value, ATTRIBUTE19),
			ATTRIBUTE20 = DECODE (p_flexfield_column_name, 'ATTRIBUTE20', l_obsolete_column_value, ATTRIBUTE20),
			ATTRIBUTE21 = DECODE (p_flexfield_column_name, 'ATTRIBUTE21', l_obsolete_column_value, ATTRIBUTE21),
			ATTRIBUTE22 = DECODE (p_flexfield_column_name, 'ATTRIBUTE22', l_obsolete_column_value, ATTRIBUTE22),
			ATTRIBUTE23 = DECODE (p_flexfield_column_name, 'ATTRIBUTE23', l_obsolete_column_value, ATTRIBUTE23),
			ATTRIBUTE24 = DECODE (p_flexfield_column_name, 'ATTRIBUTE24', l_obsolete_column_value, ATTRIBUTE24),
			ATTRIBUTE25 = DECODE (p_flexfield_column_name, 'ATTRIBUTE25', l_obsolete_column_value, ATTRIBUTE25),
			ATTRIBUTE26 = DECODE (p_flexfield_column_name, 'ATTRIBUTE26', l_obsolete_column_value, ATTRIBUTE26),
			ATTRIBUTE27 = DECODE (p_flexfield_column_name, 'ATTRIBUTE27', l_obsolete_column_value, ATTRIBUTE27),
			ATTRIBUTE28 = DECODE (p_flexfield_column_name, 'ATTRIBUTE28', l_obsolete_column_value, ATTRIBUTE28),
			ATTRIBUTE29 = DECODE (p_flexfield_column_name, 'ATTRIBUTE29', l_obsolete_column_value, ATTRIBUTE29),
			ATTRIBUTE30 = DECODE (p_flexfield_column_name, 'ATTRIBUTE30', l_obsolete_column_value, ATTRIBUTE30)
		WHERE
			organization_id = i.organization_id AND
			inventory_item_id = i.inventory_item_id;

Line: 1893

		-- Update the mig table.
		UPDATE gmi_obsolete_item_columns
		SET
			migrated_ind = 1,
			last_update_date = sysdate,
			last_updated_by = 0
		WHERE
			obsolete_column_name = p_obsolete_column_name;

Line: 1903

			INSERT INTO gmi_obsolete_item_columns(
				obsolete_column_name,
				flexfield_column_name,
				migrated_ind,
				creation_date,
				created_by,
				last_update_date,
				last_updated_by,
				last_update_login
			)values(
				p_obsolete_column_name,
				p_flexfield_column_name,
				1,
				sysdate,
				0,
				sysdate,
				0,
				NULL
			);