APPS.EAM_ASSET_ROUTES_PUB SQL Statements

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

Line: 70

			SELECT count(*) INTO l_count_rec
			FROM mtl_system_items MSI , mtl_serial_numbers MSN
			WHERE MSN.serial_number = p_asset_number
			AND MSN.gen_object_id = p_maintenance_object_id
			AND MSN.inventory_item_id = MSI.inventory_item_id
			AND MSI.inventory_item_id = p_asset_group_id
			AND MSI.organization_id = p_creation_organization_id;

Line: 82

			SELECT count(*) INTO l_count_rec
			FROM mtl_system_items
			WHERE inventory_item_id = p_asset_group_id
			AND organization_id = p_creation_organization_id;

Line: 125

	SELECT
	  msn.gen_object_id
	FROM
	  mtl_serial_numbers msn,
	  mtl_system_items_b msi
	WHERE
	  msn.inventory_item_id =  p_inventory_item_id  and
	  msn.serial_number= p_serial_number  and
	  msn.current_organization_id = p_organization_id  and
	  msi.inventory_item_id = msn.inventory_item_id  and
	  msi.eam_item_type = 1 and
	  msi.organization_id = msn.current_organization_id and
	  msn.current_status=3;

Line: 180

  select network_asset_flag into l_asset_route from mtl_serial_numbers where gen_object_id = p_object_id;

Line: 259

        SELECT COUNT(*) INTO l_count
	FROM MTL_EAM_NETWORK_ASSETS
	WHERE       network_item_id= p_network_ITEM_id
		AND network_serial_number= p_network_serial_number
		AND inventory_item_id = p_inventory_item_id
		AND serial_number= p_serial_number
		AND organization_id = p_organization_id
		AND decode(p_create_flag, 0, NETWORK_ASSOCIATION_ID,1) =
                    decode(p_create_flag, 0, p_network_association_id,1);

Line: 285

PROCEDURE insert_asset_routes
(
        p_api_version       		IN	NUMBER			,
  	p_init_msg_list			IN	VARCHAR2:= FND_API.G_FALSE	,
	p_commit	    		IN  	VARCHAR2:= FND_API.G_FALSE	,
	p_validation_level		IN  	NUMBER  := FND_API.G_VALID_LEVEL_FULL,
	x_return_status			OUT NOCOPY VARCHAR2	 ,
	x_msg_count			OUT NOCOPY NUMBER	 ,
	x_msg_data	    		OUT NOCOPY VARCHAR2  ,

	P_ORGANIZATION_ID               IN	NUMBER		,
	P_START_DATE_ACTIVE             IN	DATE	default null,
	P_END_DATE_ACTIVE               IN	DATE	default null,
	P_ATTRIBUTE_CATEGORY            IN	VARCHAR2	default null,
	P_ATTRIBUTE1	            	IN      VARCHAR2	default null,
	P_ATTRIBUTE2	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE3	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE4	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE5	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE6	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE7	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE8	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE9	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE10	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE11	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE12	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE13	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE14	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE15	            	IN	VARCHAR2	default null,
	P_NETWORK_ITEM_ID               IN	NUMBER		,
	P_NETWORK_SERIAL_NUMBER         IN	VARCHAR2		,
	P_INVENTORY_ITEM_ID             IN	NUMBER		,
	P_SERIAL_NUMBER	            	IN	VARCHAR2		,
	P_NETWORK_OBJECT_TYPE           IN	NUMBER	default null	,
	P_NETWORK_OBJECT_ID             IN	NUMBER	default null	,
	P_MAINTENANCE_OBJECT_TYPE       IN	NUMBER	default null	,
	P_MAINTENANCE_OBJECT_ID         IN	NUMBER	default null	,
	P_NETWORK_ASSET_NUMBER         	IN	VARCHAR2	default null	,
	P_ASSET_NUMBER         		IN	VARCHAR2	default null
)
IS
	l_api_name			CONSTANT VARCHAR2(30)	:= 'APIname';

Line: 353

    SAVEPOINT	insert_asset_routes;

Line: 421

			select instance_id into l_network_object_id
			from csi_item_instances
			where instance_number = p_network_asset_number;

Line: 435

			select instance_id into l_network_object_id
			from csi_item_instances
			where serial_number = l_network_serial_number
			and inventory_item_id = l_network_item_id;

Line: 448

			select cii.serial_number, cii.inventory_item_id
				into l_network_serial_number, l_network_item_id
			from csi_item_instances cii
			where cii.instance_id = l_network_object_id;

Line: 462

		select count(*) into l_count
		from csi_item_instances cii
		where cii.instance_id = l_network_object_id
		and cii.serial_number = l_network_serial_number
		and cii.inventory_item_id = l_network_item_id
		and nvl(cii.network_asset_flag, 'N') = 'Y';

Line: 518

			select instance_id into l_maintenance_object_id
			from csi_item_instances
			where instance_number = p_asset_number;

Line: 532

			select instance_id into l_maintenance_object_id
			from csi_item_instances
			where serial_number = l_serial_number
			and inventory_item_id = l_asset_group_id;

Line: 546

			select serial_number, inventory_item_id into
				l_serial_number, l_asset_group_id
			from csi_item_instances
			where instance_id = l_maintenance_object_id;

Line: 561

		select count(*) into l_count
		from csi_item_instances cii
		where cii.instance_id = l_maintenance_object_id
		and cii.serial_number = l_serial_number
		and cii.inventory_item_id = l_asset_group_id
		and nvl(cii.network_asset_flag, 'N') = 'N';

Line: 581

		select count(*) into l_count
		from mtl_eam_network_assets mena
		where mena.network_object_id = l_network_object_id
		and mena.network_object_type = l_network_object_type
		and mena.maintenance_object_id = l_maintenance_object_id
		and mena.maintenance_object_type = l_maintenance_object_type;

Line: 599

        select MTL_EAM_NETWORK_ASSETS_S.NEXTVAL into l_network_id from dual;

Line: 602

	INSERT INTO MTL_EAM_NETWORK_ASSETS (
			NETWORK_ASSOCIATION_ID	,
			ORGANIZATION_ID	,
			NETWORK_OBJECT_TYPE	,
			NETWORK_OBJECT_ID	,
			MAINTENANCE_OBJECT_TYPE	,
			MAINTENANCE_OBJECT_ID 	,
			NETWORK_ITEM_ID	,
			NETWORK_SERIAL_NUMBER	,
			INVENTORY_ITEM_ID	,
			SERIAL_NUMBER	,
			START_DATE_ACTIVE	,
			END_DATE_ACTIVE	,
			ATTRIBUTE_CATEGORY	,
			ATTRIBUTE1	,
			ATTRIBUTE2	,
			ATTRIBUTE3	,
			ATTRIBUTE4	,
			ATTRIBUTE5	,
			ATTRIBUTE6	,
			ATTRIBUTE7	,
			ATTRIBUTE8	,
			ATTRIBUTE9	,
			ATTRIBUTE10	,
			ATTRIBUTE11	,
			ATTRIBUTE12	,
			ATTRIBUTE13	,
			ATTRIBUTE14	,
			ATTRIBUTE15	,
			CREATED_BY           	,
			CREATION_DATE       	,
			LAST_UPDATE_LOGIN  	,
			LAST_UPDATE_DATE  	,
			LAST_UPDATED_BY
		      )VALUES
		      (
			l_network_id	,
			/*P_ORGANIZATION_ID	,*/
			l_org_id,
			l_NETWORK_OBJECT_TYPE	,
			l_NETWORK_OBJECT_ID	,
			/*P_MAINTENANCE_OBJECT_TYPE	,
			P_MAINTENANCE_OBJECT_ID 	,*/
			l_maintenance_object_type,
			l_maintenance_object_id ,
			l_NETWORK_ITEM_ID	,
			l_NETWORK_SERIAL_NUMBER	,
			/*P_INVENTORY_ITEM_ID	,
			P_SERIAL_NUMBER	,*/
			l_asset_group_id,
			l_asset_number	,
			P_START_DATE_ACTIVE	,
			P_END_DATE_ACTIVE	,
			P_ATTRIBUTE_CATEGORY	,
			P_ATTRIBUTE1	,
			P_ATTRIBUTE2	,
			P_ATTRIBUTE3	,
			P_ATTRIBUTE4	,
			P_ATTRIBUTE5	,
			P_ATTRIBUTE6	,
			P_ATTRIBUTE7	,
			P_ATTRIBUTE8	,
			P_ATTRIBUTE9	,
			P_ATTRIBUTE10	,
			P_ATTRIBUTE11	,
			P_ATTRIBUTE12	,
			P_ATTRIBUTE13	,
			P_ATTRIBUTE14	,
			P_ATTRIBUTE15	,
			fnd_global.user_id	,
			sysdate	,
			fnd_global.login_id	,
			sysdate    	,
			fnd_global.user_id
		      );

Line: 692

		ROLLBACK TO insert_asset_routes;

Line: 699

		ROLLBACK TO insert_asset_routes;

Line: 706

		ROLLBACK TO insert_asset_routes;

Line: 720

END insert_asset_routes;

Line: 723

PROCEDURE update_asset_routes
(
        p_api_version       		IN	NUMBER			,
  	p_init_msg_list			IN	VARCHAR2:= FND_API.G_FALSE	,
	p_commit	    		IN  	VARCHAR2:= FND_API.G_FALSE	,
	p_validation_level		IN  	NUMBER  := FND_API.G_VALID_LEVEL_FULL,
	x_return_status			OUT NOCOPY VARCHAR2	 ,
	x_msg_count			OUT NOCOPY NUMBER	 ,
	x_msg_data	    		OUT NOCOPY VARCHAR2  ,

	P_ORGANIZATION_ID               IN	NUMBER		,
	P_START_DATE_ACTIVE             IN	DATE	default null,
	P_END_DATE_ACTIVE               IN	DATE	default null,
	P_ATTRIBUTE_CATEGORY            IN	VARCHAR2	default null,
	P_ATTRIBUTE1	            	IN      VARCHAR2	default null,
	P_ATTRIBUTE2	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE3	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE4	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE5	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE6	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE7	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE8	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE9	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE10	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE11	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE12	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE13	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE14	            	IN	VARCHAR2	default null,
	P_ATTRIBUTE15	            	IN	VARCHAR2	default null,
	P_NETWORK_ITEM_ID               IN	NUMBER		,
	P_NETWORK_SERIAL_NUMBER         IN	VARCHAR2		,
	P_INVENTORY_ITEM_ID             IN	NUMBER		,
	P_SERIAL_NUMBER	            	IN	VARCHAR2		,
	P_NETWORK_ASSOCIATION_ID        IN	NUMBER		,
	P_NETWORK_OBJECT_TYPE           IN	NUMBER	default null	,
	P_NETWORK_OBJECT_ID             IN	NUMBER	default null	,
	P_MAINTENANCE_OBJECT_TYPE       IN	NUMBER	default null	,
	P_MAINTENANCE_OBJECT_ID         IN	NUMBER	default null	,
	P_NETWORK_ASSET_NUMBER         	IN	VARCHAR2	default null	,
	P_ASSET_NUMBER         		IN	VARCHAR2	default null
)
IS
	l_api_name			CONSTANT VARCHAR2(30)	:= 'APIname';

Line: 791

    SAVEPOINT	update_asset_routes;

Line: 859

			select instance_id into l_network_object_id
			from csi_item_instances
			where instance_number = p_network_asset_number;

Line: 873

			select instance_id into l_network_object_id
			from csi_item_instances
			where serial_number = l_network_serial_number
			and inventory_item_id = l_network_item_id;

Line: 886

			select serial_number, inventory_item_id into
				l_network_serial_number, l_network_item_id
			from csi_item_instances
			where instance_id = l_network_object_id;

Line: 901

		select count(*) into l_count
		from csi_item_instances cii
		where cii.instance_id = l_network_object_id
		and cii.serial_number = l_network_serial_number
		and cii.inventory_item_id = l_network_item_id
		and cii.network_asset_flag = 'Y';

Line: 956

			select instance_id into l_maintenance_object_id
			from csi_item_instances
			where instance_number = p_asset_number;

Line: 970

			select instance_id into l_maintenance_object_id
			from csi_item_instances
			where serial_number = l_serial_number
			and inventory_item_id = l_asset_group_id;

Line: 984

			select serial_number, inventory_item_id into
				l_serial_number, l_asset_group_id
			from csi_item_instances
			where instance_id = l_maintenance_object_id;

Line: 999

		select count(*) into l_count
		from csi_item_instances cii
		where cii.instance_id = l_maintenance_object_id
		and cii.serial_number = l_serial_number
		and cii.inventory_item_id = l_asset_group_id
		and cii.network_asset_flag = 'N';

Line: 1019

		select count(*) into l_count
		from mtl_eam_network_assets mena
		where mena.network_object_id = l_network_object_id
		and mena.network_object_type = l_network_object_type
		and mena.maintenance_object_id = l_maintenance_object_id
		and mena.maintenance_object_type = l_maintenance_object_type
		and mena.network_association_id = p_network_association_id;

Line: 1039

        UPDATE MTL_EAM_NETWORK_ASSETS
        SET
		START_DATE_ACTIVE	=	P_START_DATE_ACTIVE	,
		END_DATE_ACTIVE		=	P_END_DATE_ACTIVE	,
		ATTRIBUTE_CATEGORY	=	P_ATTRIBUTE_CATEGORY	,
		ATTRIBUTE1		=	P_ATTRIBUTE1	,
		ATTRIBUTE2		=	P_ATTRIBUTE2	,
		ATTRIBUTE3		=	P_ATTRIBUTE3	,
		ATTRIBUTE4		=	P_ATTRIBUTE4	,
		ATTRIBUTE5		=	P_ATTRIBUTE5	,
		ATTRIBUTE6		=	P_ATTRIBUTE6	,
		ATTRIBUTE7		=	P_ATTRIBUTE7	,
		ATTRIBUTE8		=	P_ATTRIBUTE8	,
		ATTRIBUTE9		=	P_ATTRIBUTE9	,
		ATTRIBUTE10		=	P_ATTRIBUTE10	,
		ATTRIBUTE11		=	P_ATTRIBUTE11	,
		ATTRIBUTE12		=	P_ATTRIBUTE12	,
		ATTRIBUTE13		=	P_ATTRIBUTE13	,
		ATTRIBUTE14		=	P_ATTRIBUTE14	,
		ATTRIBUTE15		=	P_ATTRIBUTE15	,

		LAST_UPDATE_LOGIN	=	fnd_global.login_id	,
		LAST_UPDATE_DATE	=	sysdate	,
		LAST_UPDATED_BY		=	fnd_global.user_id

	WHERE NETWORK_ASSOCIATION_ID    = P_NETWORK_ASSOCIATION_ID;

Line: 1079

		ROLLBACK TO update_asset_routes;

Line: 1086

		ROLLBACK TO update_asset_routes;

Line: 1093

		ROLLBACK TO update_asset_routes;

Line: 1107

END update_asset_routes;