APPS.INV_LOT_SERIAL_UPGRADE SQL Statements

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

Line: 28

		SELECT organization_id, inventory_item_id, lot_number
		FROM mtl_lot_numbers
		WHERE organization_id = nvl(p_org_id, organization_id)
		AND lot_attribute_category IS NULL
		ORDER BY organization_id, inventory_item_id;

Line: 40

	l_update_count NUMBER := 0;

Line: 101

				-- Step 3. Update lot with the default attribute
				UPDATE_LOT_SERIAL_ATTR(
					x_return_status     => l_return_status
				,	x_msg_count         => l_msg_count
				,  x_msg_data          => l_msg_data
				,  x_update_count      => l_update_count
				,  p_lot_serial_option => OPTION_LOT
				,  p_organization_id   => v_lot.organization_id
				,  p_inventory_item_id => v_lot.inventory_item_id
				,  p_lot_serial_number => v_lot.lot_number
				,	p_attribute_category  => l_context_code
				,  p_attributes        => l_attribute_default);

Line: 113

				--trace('Updated lot wtih attributes, status, count '
				--	|| l_return_status || ',' || l_update_count);

Line: 122

   					trace('Successfully updated lot:'||v_lot.lot_number
				     || ',org:'||v_lot.organization_id||',item:'||v_lot.inventory_item_id);

Line: 126

				END IF; -- Step 3. update lot

Line: 152

		SELECT current_organization_id, inventory_item_id, serial_number
		FROM mtl_serial_numbers
		WHERE current_organization_id = nvl(p_org_id, current_organization_id)
		AND serial_attribute_category IS NULL
		AND current_status in (3,4,5)
		ORDER BY current_organization_id, inventory_item_id;

Line: 165

	l_update_count NUMBER := 0;

Line: 225

				-- Step 3. Update serial with the default attribute
				UPDATE_LOT_SERIAL_ATTR(
					x_return_status     => l_return_status
				,	x_msg_count         => l_msg_count
				,  x_msg_data          => l_msg_data
				,  x_update_count      => l_update_count
				,  p_lot_serial_option => OPTION_SERIAL
				,  p_organization_id   => v_serial.current_organization_id
				,  p_inventory_item_id => v_serial.inventory_item_id
				,  p_lot_serial_number => v_serial.serial_number
				,	p_attribute_category  => l_context_code
				,  p_attributes        => l_attribute_default);

Line: 237

				--trace('Updated serial wtih attributes, status, count '
				--	|| l_return_status || ',' || l_update_count);

Line: 247

   					trace('Successfully updated serial:'||v_serial.serial_number
					  || ',org:'||v_serial.current_organization_id ||',item:'||v_serial.inventory_item_id);

Line: 251

				END IF; -- Step 3. update serial

Line: 272

/* Update lot/serial number with the given attribute record
   Input Parameter:
     p_lot_serial_option: specify update lot or serial
       possible value: OPTION_LOT(1), OPTION_SERIAL(2)*/
PROCEDURE UPDATE_LOT_SERIAL_ATTR(
	x_return_status     OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,	x_msg_count         OUT NOCOPY /* file.sql.39 change */ NUMBER
,  x_msg_data          OUT NOCOPY /* file.sql.39 change */ VARCHAR2
,  x_update_count      OUT NOCOPY /* file.sql.39 change */ NUMBER
,  p_lot_serial_option IN  NUMBER
,  p_organization_id   IN  NUMBER
,  p_inventory_item_id IN  NUMBER
,  p_lot_serial_number IN  VARCHAR2
,	p_attribute_category IN VARCHAR2
,  p_attributes        IN  inv_lot_sel_attr.lot_sel_attributes_tbl_type
)IS

	l_update_cur INTEGER;

Line: 290

	l_update_stmt  VARCHAR2(1000);

Line: 292

	l_rowupdated NUMBER;

Line: 295

	--trace('In UPDATE_LOT_SERIAL, lot/ser option, org, item, lot/ser, p_attr_count ');

Line: 302

	-- Construct the update statement
	l_update_stmt := 'UPDATE ';

Line: 305

		l_update_stmt := l_update_stmt || LOT_TABLE_NAME ||
		                 ' SET lot_attribute_category = :attr_category ';

Line: 308

		l_update_stmt := l_update_stmt || SERIAL_TABLE_NAME ||
		                 ' SET serial_attribute_category = :attr_category ';

Line: 313

		l_update_stmt := l_update_stmt || ' , ' || p_attributes(i).column_name || '=';

Line: 317

				l_update_stmt := l_update_stmt || 'null';

Line: 319

				l_update_stmt := l_update_stmt || p_attributes(i).column_value;

Line: 322

			l_update_stmt := l_update_stmt || '''' || p_attributes(i).column_value|| '''';

Line: 324

			l_update_stmt := l_update_stmt || p_attributes(i).column_value;

Line: 329

		l_update_stmt := l_update_stmt ||
			' WHERE organization_id=:org_id AND inventory_item_id=:item_id AND lot_number=:lot_serial';

Line: 332

		l_update_stmt := l_update_stmt ||
			' WHERE current_organization_id=:org_id AND inventory_item_id=:item_id AND serial_number=:lot_serial';

Line: 337

	l_update_cur := DBMS_SQL.OPEN_CURSOR;

Line: 339

	DBMS_SQL.PARSE(l_update_cur, l_update_stmt, DBMS_SQL.v7);

Line: 341

	DBMS_SQL.BIND_VARIABLE(l_update_cur, ':attr_category', p_attribute_category);

Line: 342

	DBMS_SQL.BIND_VARIABLE(l_update_cur, ':org_id', p_organization_id);

Line: 343

	DBMS_SQL.BIND_VARIABLE(l_update_cur, ':item_id', p_inventory_item_id);

Line: 344

	DBMS_SQL.BIND_VARIABLE(l_update_cur, ':lot_serial', p_lot_serial_number);

Line: 346

	l_rowupdated := DBMS_SQL.EXECUTE(l_update_cur);

Line: 348

	IF l_rowupdated >= 1 THEN
		x_update_count := l_rowupdated;

Line: 352

   		trace(' No rows updated , error in update lot/serial ' ||p_lot_serial_number
			|| ',org:'||p_organization_id	||',item:'||p_inventory_item_id);

Line: 359

	DBMS_SQL.CLOSE_CURSOR(l_update_cur);

Line: 363

		DBMS_SQL.CLOSE_CURSOR(l_update_cur);

Line: 365

   		trace('Error in update lot/serial '|| p_lot_serial_number
		 || ',org:'||p_organization_id	||',item:'||p_inventory_item_id);

Line: 370

END UPDATE_LOT_SERIAL_ATTR;