CREATE TABLE dbo.[UniqueIdStateRepositoryXref] (
	[UniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[StateRepositoryUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_UniqueId_StateRepositoryUniqueId] DEFAULT (''),
	[Id] [int] NOT NULL ,
	[Comment] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_UniqueId_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_UniqueId_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_UniqueId] PRIMARY KEY  CLUSTERED 
	(
		[UniqueId],
		[StateRepositoryUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO




CREATE TABLE dbo.[PipeProfileCustomPropertyValue] (
	[PipeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PipeProfileCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeProfileCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeProfileCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_PipeProfileCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[PipeProfileUniqueId],
		[PipeProfileCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[PipeProfileCustomProperty] (
	[PipeProfileCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PipeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeProfileCustomProperty_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeProfileCustomProperty_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_PipeProfileCustomProperty] PRIMARY KEY  CLUSTERED 
	(
		[PipeProfileCustomPropertyId],
		[PipeProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[PipeProfile] (
	[PipeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorProfileUniqueIdProducer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorProfileUniqueIdConsumer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeProfile_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeProfile_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_PipeProfile] PRIMARY KEY  CLUSTERED 
	(
		[PipeProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[PipeItemCustomStatusFlag] (
	[BitPosition] [smallint] NOT NULL ,
	[FlagDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeStatusFlag_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeStatusFlag_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_PipeStatusFlag] PRIMARY KEY  CLUSTERED 
	(
		[BitPosition]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_PipeStatusFlag] CHECK ([BitPosition] >= 1 and [BitPosition] <= 32)
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[PipeItemCustomPropertyValue] (
	[PipeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PipeItemCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeItemCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeItemCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_PipeItemCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[PipeItemUniqueId],
		[PipeItemCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[PipeItemCustomProperty] (
	[PipeItemCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PipeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeItemCustomProperty_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_PipeItemCustomProperty_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_PipeItemCustomProperty] PRIMARY KEY  CLUSTERED 
	(
		[PipeItemCustomPropertyId],
		[PipeItemUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[PipeItem] (
	[PipeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PipeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeItemUniqueIdProducer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeItemUniqueIdConsumer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorItemUniqueIdProducer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorItemUniqueIdConsumer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RequestedStateStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_PipeItem_RequestedStateStatus] DEFAULT (''),
	[StateStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_PipeItem_StateStatus] DEFAULT (''),
	[CustomStatusFlags] [int] NOT NULL CONSTRAINT [DF_PipeItem_CustomStatusFlags] DEFAULT (0),
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Pipe_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pipe_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Pipe] PRIMARY KEY  CLUSTERED 
	(
		[PipeItemUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeTypeSupportedConnectorType] (
	[NodeTypeSupportedConnectorTypeId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsSupported] [bit] NOT NULL ,
	[IsActive] [bit] NOT NULL CONSTRAINT [DF_NodeTypeSupportedConnectorType_IsActive] DEFAULT (0),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeSupportedConnectorType_CreateDate1] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeSupportedConnectorType_UpdateDate1] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeTypeSupportedConnectorType] PRIMARY KEY  CLUSTERED 
	(
		[NodeTypeSupportedConnectorTypeId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeTypeCustomPropertyValue] (
	[NodeTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeTypeCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeTypeCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[NodeTypeUniqueId],
		[NodeTypeCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeTypeCustomProperty] (
	[NodeTypeCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeCustomProperty_CreateDate_1] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeCustomProperty_UpdateDate_1] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeTypeCustomProperty_1] PRIMARY KEY  CLUSTERED 
	(
		[NodeTypeCustomPropertyId],
		[NodeTypeUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeType] (
	[NodeTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UniqueNodeTypeName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsVirtual] [bit] NOT NULL ,
	[NumInstanceMin] [int] NOT NULL CONSTRAINT [DF__NodeType__NumIns__61A66D40] DEFAULT (0),
	[NumInstanceMax] [int] NOT NULL CONSTRAINT [DF__NodeType__NumIns__629A9179] DEFAULT (0),
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeType_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeType_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeType] PRIMARY KEY  CLUSTERED 
	(
		[NodeTypeUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeProfileSupportedConnectorProfile] (
	[NodeProfileSupportedConnectorProfileId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsSupported] [bit] NOT NULL ,
	[IsActive] [bit] NOT NULL CONSTRAINT [DF_NodeProfileSupportedConnectorProfile_IsActive] DEFAULT (0),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfileSupportedConnectorProfile_CreateDate1] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfileSupportedConnectorProfile_UpdateDate1] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeProfileSupportedConnectorProfile] PRIMARY KEY  CLUSTERED 
	(
		[NodeProfileSupportedConnectorProfileId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeProfileCustomPropertyValue] (
	[NodeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeProfileCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfileCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfileCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeProfileCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[NodeProfileUniqueId],
		[NodeProfileCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeProfileCustomProperty] (
	[NodeProfileCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfileCustomProperty_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfileCustomProperty_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeProfileCustomProperty] PRIMARY KEY  CLUSTERED 
	(
		[NodeProfileCustomPropertyId],
		[NodeProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeProfileCompatibleNodeProfile] (
	[NodeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompatibleNodeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsCompatible] [bit] NOT NULL ,
	[IsActive] [bit] NOT NULL CONSTRAINT [DF_NodeProfileSupportedNodeProfile_IsActive] DEFAULT (0),
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeSupportedNodeType_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeSupportedNodeType_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeTypeSupportedNodeType] PRIMARY KEY  CLUSTERED 
	(
		[NodeProfileUniqueId],
		[CompatibleNodeProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeProfile] (
	[NodeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NumInstanceMin] [int] NOT NULL CONSTRAINT [DF__NodeProfi__NumIn__72D0F942] DEFAULT (0),
	[NumInstanceMax] [int] NOT NULL CONSTRAINT [DF__NodeProfi__NumIn__73C51D7B] DEFAULT (0),
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfile_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeProfile_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeProfile] PRIMARY KEY  CLUSTERED 
	(
		[NodeProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeItemCustomStatusFlag] (
	[BitPosition] [smallint] NOT NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeStatusFlag_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeStatusFlag_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeStatusFlag] PRIMARY KEY  CLUSTERED 
	(
		[BitPosition]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_NodeStatusFlag] CHECK ([BitPosition] >= 1 and [BitPosition] <= 32)
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeItemCustomPropertyValue] (
	[NodeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeItemCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[NodeItemUniqueId],
		[NodeItemCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeItemCustomProperty] (
	[NodeItemCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeCustomProperty_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_NodeTypeCustomProperty_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_NodeTypeCustomProperty] PRIMARY KEY  CLUSTERED 
	(
		[NodeItemCustomPropertyId],
		[NodeItemUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[NodeItem] (
	[NodeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NumInstanceMin] [int] NOT NULL CONSTRAINT [DF__NodeItem__NumIns__0AA882D3] DEFAULT (0),
	[NumInstanceMax] [int] NOT NULL CONSTRAINT [DF__NodeItem__NumIns__0B9CA70C] DEFAULT (0),
	[RequestedStateStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_NodeItem_RequestedStateStatus] DEFAULT (''),
	[StateStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_NodeItem_StateStatus] DEFAULT (''),
	[CustomStatusFlags] [int] NOT NULL CONSTRAINT [DF_NodeItem_CustomStatusFlags] DEFAULT (0),
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Node_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Node_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Node] PRIMARY KEY  CLUSTERED 
	(
		[NodeItemUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[DataType] (
	[DataTypeId] [tinyint] NOT NULL ,
	[DataTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsDateTime] [bit] NOT NULL ,
	[IsNumeric] [bit] NOT NULL ,
	[IsInteger] [bit] NOT NULL ,
	[IsFloatingPoint] [bit] NOT NULL ,
	[NeedsQuotes] [bit] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL ,
	[UpdateDate] [datetime] NOT NULL ,
	CONSTRAINT [PK_DataType] PRIMARY KEY  CLUSTERED 
	(
		[DataTypeId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorTypeCustomPropertyValue] (
	[ConnectorTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorTypeCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorTypeCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorTypeCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorTypeCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorTypeUniqueId],
		[ConnectorTypeCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorTypeCustomProperty] (
	[ConnectorTypeCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorTypeCustomProperty_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorTypeCustomProperty_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorTypeCustomProperty] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorTypeCustomPropertyId],
		[ConnectorTypeUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorType] (
	[ConnectorTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UniqueConnectorTypeName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsProducer] [bit] NOT NULL ,
	[IsPipeInitiator] [bit] NOT NULL ,
	[IsVirtual] [bit] NOT NULL CONSTRAINT [DF_ConnectorType_IsVirtual] DEFAULT (0),
	[IsSynchronous] [bit] NOT NULL CONSTRAINT [DF_ConnectorType_IsSynchronous] DEFAULT (1),
	[Protocol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ConnectorType_Protocol] DEFAULT (''),
	[NumInstanceMin] [int] NOT NULL CONSTRAINT [DF__Connector__NumIn__2744C181] DEFAULT (0),
	[NumInstanceMax] [int] NOT NULL CONSTRAINT [DF__Connector__NumIn__2838E5BA] DEFAULT (0),
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorType_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorType_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorType] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorTypeUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorProfileCustomPropertyValue] (
	[ConnectorProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorProfileCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorProfileCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorProfileCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorProfileCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorProfileUniqueId],
		[ConnectorProfileCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorProfileCustomProperty] (
	[ConnectorProfileCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorProfileCustomProperty_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorProfileCustomProperty_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorProfileCustomProperty] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorProfileCustomPropertyId],
		[ConnectorProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorProfileCompatibleConnectorProfile] (
	[ConnectorProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompatibleConnectorProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NOT NULL CONSTRAINT [DF_ConnectorProfileCompatibleConnectorProfile_IsActive] DEFAULT (0),
	[IsCompatible] [bit] NOT NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorTypeCompatibleConnectorType_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorTypeCompatibleConnectorType_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorTypeCompatibleConnectorType] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorProfileUniqueId],
		[CompatibleConnectorProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorProfile] (
	[ConnectorProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorTypeUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NumInstanceMin] [int] NOT NULL CONSTRAINT [DF__Connector__NumIn__386F4D83] DEFAULT (0),
	[NumInstanceMax] [int] NOT NULL CONSTRAINT [DF__Connector__NumIn__396371BC] DEFAULT (0),
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorProfile_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorProfile_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorProfile] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorProfileUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorItemCustomStatusFlag] (
	[BitPosition] [smallint] NOT NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorItemCustomStatusFlag_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorItemCustomStatusFlag_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorItemCustomStatusFlag] PRIMARY KEY  CLUSTERED 
	(
		[BitPosition]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorItemCustomPropertyValue] (
	[ConnectorItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorItemCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CustomPropertyValue] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorItemCustomPropertyValue_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorItemCustomPropertyValue_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorItemCustomPropertyValue] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorItemUniqueId],
		[ConnectorItemCustomPropertyId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorItemCustomProperty] (
	[ConnectorItemCustomPropertyId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[KeyText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataTypeId] [tinyint] NOT NULL ,
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorItemCustomProperty_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_ConnectorItemCustomProperty_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorItemCustomProperty] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorItemCustomPropertyId],
		[ConnectorItemUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO


CREATE TABLE dbo.[ConnectorItem] (
	[ConnectorItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NodeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PipeItemUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ConnectorProfileUniqueId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LongName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[NumInstanceMin] [int] NOT NULL CONSTRAINT [DF__Connector__NumIn__513AFB4D] DEFAULT (0),
	[NumInstanceMax] [int] NOT NULL CONSTRAINT [DF__Connector__NumIn__522F1F86] DEFAULT (0),
	[RequestedStateStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ConnectorItem_RequestedStateStatus] DEFAULT (''),
	[StateStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ConnectorItem_StateStatus] DEFAULT (''),
	[CustomStatusFlags] [int] NOT NULL CONSTRAINT [DF_ConnectorItem_StatusFlags] DEFAULT (0),
	[IsActive] [bit] NULL ,
	[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Connector_CreateDate] DEFAULT (getdate()),
	[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Connector_UpdateDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ConnectorItem] PRIMARY KEY  CLUSTERED 
	(
		[ConnectorItemUniqueId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
--GO

----------------- SPs -----------------------
ALTER PROCEDURE dbo.GetTablesForStateRepository (
	@staterepositoryuniqueid		varchar (50)
)
AS
BEGIN

	DECLARE	@ConnectorItemUniqueIds TABLE (ConnectorItemUniqueId varchar (50))
	DECLARE	@ConnectorProfileUniqueIds TABLE (ConnectorProfileUniqueId varchar (50))
	DECLARE	@ConnectorTypeUniqueIds TABLE (ConnectorTypeUniqueId varchar (50))
	DECLARE	@ConnectorTypeCustomPropertyUniqueIds TABLE (ConnectorTypeCustomPropertyUniqueId varchar (50))
	DECLARE	@NodeItemUniqueIds TABLE (NodeItemUniqueId varchar (50))
	DECLARE	@NodeProfileUniqueIds TABLE (NodeProfileUniqueId varchar (50))
	DECLARE	@NodeTypeUniqueIds TABLE (NodeTypeUniqueId varchar (50))
	DECLARE	@PipeItemUniqueIds TABLE (PipeItemUniqueId varchar (50))
	DECLARE	@PipeProfileUniqueIds TABLE (PipeProfileUniqueId varchar (50))

	
		
	INSERT @NodeItemUniqueIds
	SELECT DISTINCT ni.NodeItemUniqueId
	FROM NodeItem ni
	JOIN UniqueIdStateRepositoryXref uisrx
	ON
		(uisrx.UniqueId = ni.NodeItemUniqueId)
	WHERE
		(ni.IsActive IS NULL OR ni.IsActive = 1)
		AND (uisrx.IsActive IS NULL OR uisrx.IsActive = 1)
		AND (uisrx.StateRepositoryUniqueId = @staterepositoryuniqueid OR 'x' + uisrx.StateRepositoryUniqueId + 'x' = 'xx')
		
		
	INSERT @NodeProfileUniqueIds
	SELECT DISTINCT np.NodeProfileUniqueId
	FROM NodeProfile np
	JOIN NodeItem ni
	ON
		(np.NodeProfileUniqueId = ni.NodeProfileUniqueId)
	JOIN @NodeItemUniqueIds niui
	ON
		(niui.NodeItemUniqueId = ni.NodeItemUniqueId)
	WHERE
		(np.IsActive IS NULL OR np.IsActive = 1)

	INSERT @NodeTypeUniqueIds
	SELECT DISTINCT nt.NodeTypeUniqueId
	FROM NodeType nt
	JOIN NodeProfile np
	ON
		(np.NodeTypeUniqueId = nt.NodeTypeUniqueId)
	JOIN @NodeProfileUniqueIds npui
	ON
		(npui.NodeProfileUniqueId = np.NodeProfileUniqueId)
	WHERE
		(nt.IsActive IS NULL OR nt.IsActive = 1)

	INSERT @PipeItemUniqueIds
	SELECT DISTINCT pi.PipeItemUniqueId
	FROM PipeItem pi
	JOIN @NodeItemUniqueIds niui
	ON
		(niui.NodeItemUniqueId = pi.NodeItemUniqueIdProducer
		OR niui.NodeItemUniqueId = pi.NodeItemUniqueIdConsumer)
	WHERE
		(pi.IsActive IS NULL OR pi.IsActive = 1)
		
	INSERT @PipeProfileUniqueIds
	SELECT DISTINCT pp.PipeProfileUniqueId
	FROM PipeProfile pp
	JOIN PipeItem pi
	ON
		(pp.PipeProfileUniqueId = pi.PipeProfileUniqueId)
	JOIN @PipeItemUniqueIds piui
	ON
		(piui.PipeItemUniqueId = pi.PipeItemUniqueId)
	WHERE
		(pp.IsActive IS NULL OR pp.IsActive = 1)


	INSERT @ConnectorItemUniqueIds
	SELECT DISTINCT ci.ConnectorItemUniqueId
	FROM ConnectorItem ci
	JOIN @PipeItemUniqueIds piui
	ON
		(piui.PipeItemUniqueId = ci.PipeItemUniqueId)
	WHERE
		(ci.IsActive IS NULL OR ci.IsActive = 1)
		
	INSERT @ConnectorProfileUniqueIds
	SELECT DISTINCT cp.ConnectorProfileUniqueId
	FROM ConnectorProfile cp
	JOIN ConnectorItem ci
	ON
		(cp.ConnectorProfileUniqueId = ci.ConnectorProfileUniqueId)
	JOIN @ConnectorItemUniqueIds ciui
	ON
		(ciui.ConnectorItemUniqueId = ci.ConnectorItemUniqueId)
	WHERE
		(cp.IsActive IS NULL OR cp.IsActive = 1)

	INSERT @ConnectorTypeUniqueIds
	SELECT DISTINCT ct.ConnectorTypeUniqueId
	FROM ConnectorType ct
	JOIN ConnectorProfile cp
	ON
		(cp.ConnectorTypeUniqueId = ct.ConnectorTypeUniqueId)
	JOIN @ConnectorProfileUniqueIds cpui
	ON
		(cpui.ConnectorProfileUniqueId = cp.ConnectorProfileUniqueId)
	WHERE
		(ct.IsActive IS NULL OR ct.IsActive = 1)
		
		
	-- return order is important; make sure sync with code
	
	-- return ConnectorItem
	SELECT
		a.ConnectorItemUniqueId, a.NodeItemUniqueId, a.PipeItemUniqueId, a.ConnectorProfileUniqueId,
		a.ShortName, a.LongName, a.NumInstanceMin, a.NumInstanceMax, a.RequestedStateStatus, a.StateStatus,
		a.CustomStatusFlags, a.CreateDate, a.UpdateDate
	FROM ConnectorItem a
	JOIN @ConnectorItemUniqueIds b
	ON a.ConnectorItemUniqueId = b.ConnectorItemUniqueId

	-- return ConnectorItemCustomProperty
	SELECT a.ConnectorItemCustomPropertyId, a.ConnectorItemUniqueId, a.KeyText, a.DataTypeId,
		a.CreateDate, a.UpdateDate
	FROM ConnectorItemCustomProperty a
	JOIN @ConnectorItemUniqueIds b
	ON a.ConnectorItemUniqueId = b.ConnectorItemUniqueId

	-- return ConnectorItemCustomPropertyValue
	SELECT a.ConnectorItemUniqueId, a.ConnectorItemCustomPropertyId, a.CustomPropertyValue,
		a.CreateDate, a.UpdateDate
	FROM ConnectorItemCustomPropertyValue a
	JOIN @ConnectorItemUniqueIds b
	ON a.ConnectorItemUniqueId = b.ConnectorItemUniqueId

	-- return ConnectorItemCustomStatusFlag
	SELECT BitPosition, Name, CreateDate, UpdateDate
	FROM ConnectorItemCustomStatusFlag
	WHERE IsActive IS NULL or IsActive = 1

		
	-- return ConnectorProfile
	SELECT a.ConnectorProfileUniqueId, a.ConnectorTypeUniqueId, a.ShortName, a.LongName,
		a.NumInstanceMin, a.NumInstanceMax, a.CreateDate, a.UpdateDate
	FROM ConnectorProfile a
	JOIN @ConnectorProfileUniqueIds b
	ON a.ConnectorProfileUniqueId = b.ConnectorProfileUniqueId

	-- return ConnectorProfileCompatibleConnectorProfile
	SELECT a.ConnectorProfileUniqueId, a.CompatibleConnectorProfileUniqueId, a.IsActive,
		a.IsCompatible, a.CreateDate, a.UpdateDate
	FROM ConnectorProfileCompatibleConnectorProfile a
	JOIN @ConnectorProfileUniqueIds b
	ON a.ConnectorProfileUniqueId = b.ConnectorProfileUniqueId

	-- return ConnectorProfileCustomProperty
	SELECT a.ConnectorProfileCustomPropertyId, a.ConnectorProfileUniqueId, a.KeyText,
		a.DataTypeId, a.CreateDate, a.UpdateDate
	FROM ConnectorProfileCustomProperty a
	JOIN @ConnectorProfileUniqueIds b
	ON a.ConnectorProfileUniqueId = b.ConnectorProfileUniqueId

	-- return ConnectorProfileCustomPropertyValue
	SELECT a.ConnectorProfileUniqueId, a.ConnectorProfileCustomPropertyId, a.CustomPropertyValue,
		a.CreateDate, a.UpdateDate
	FROM ConnectorProfileCustomPropertyValue a
	JOIN @ConnectorProfileUniqueIds b
	ON a.ConnectorProfileUniqueId = b.ConnectorProfileUniqueId

	-- return ConnectorType
	SELECT a.ConnectorTypeUniqueId, a.UniqueConnectorTypeName, a.ShortName, a.LongName,
		a.IsProducer, a.IsPipeInitiator, a.IsVirtual, a.IsSynchronous, a.Protocol, a.NumInstanceMin,
		a.NumInstanceMax, a.CreateDate, a.UpdateDate
	FROM ConnectorType a
	JOIN @ConnectorTypeUniqueIds b
	ON a.ConnectorTypeUniqueId = b.ConnectorTypeUniqueId

	-- return ConnectorTypeCustomProperty
	SELECT a.ConnectorTypeCustomPropertyId, a.ConnectorTypeUniqueId, a.KeyText, a.DataTypeId,
		a.CreateDate, a.UpdateDate
	FROM ConnectorTypeCustomProperty a
	JOIN @ConnectorTypeUniqueIds b
	ON a.ConnectorTypeUniqueId = b.ConnectorTypeUniqueId

	-- return ConnectorTypeCustomPropertyValue
	SELECT a.ConnectorTypeUniqueId, a.ConnectorTypeCustomPropertyId, a.CustomPropertyValue,
		a.CreateDate, a.UpdateDate
	FROM ConnectorTypeCustomPropertyValue a
	JOIN @ConnectorTypeUniqueIds b
	ON a.ConnectorTypeUniqueId = b.ConnectorTypeUniqueId
	
	-- return DataType
	SELECT DataTypeId, DataTypeName, IsDateTime, IsNumeric, IsInteger,
		IsFloatingPoint, NeedsQuotes, CreateDate, UpdateDate
	FROM DataType
	WHERE IsActive IS NULL or IsActive = 1

	
	-- return NodeItem
	SELECT a.NodeItemUniqueId, a.NodeProfileUniqueId, a.ShortName, a.LongName, a.NumInstanceMin,
		a.NumInstanceMax, a.RequestedStateStatus, a.StateStatus, a.CustomStatusFlags, a.CreateDate, a.UpdateDate
	FROM NodeItem a
	JOIN @NodeItemUniqueIds b
	ON a.NodeItemUniqueId = b.NodeItemUniqueId

	-- return NodeItemCustomProperty
	SELECT a.NodeItemCustomPropertyId, a.NodeItemUniqueId, a.KeyText, a.DataTypeId, a.CreateDate, a.UpdateDate
	FROM NodeItemCustomProperty a
	JOIN @NodeItemUniqueIds b
	ON a.NodeItemUniqueId = b.NodeItemUniqueId

	-- return NodeItemCustomPropertyValue
	SELECT a.NodeItemUniqueId, a.NodeItemCustomPropertyId, a.CustomPropertyValue, a.CreateDate, a.UpdateDate
	FROM NodeItemCustomPropertyValue a
	JOIN @NodeItemUniqueIds b
	ON a.NodeItemUniqueId = b.NodeItemUniqueId

	-- return NodeItemCustomStatusFlag
	SELECT BitPosition, Name, CreateDate, UpdateDate
	FROM NodeItemCustomStatusFlag
	WHERE IsActive IS NULL or IsActive = 1

	
	-- return NodeProfile
	SELECT a.NodeProfileUniqueId, a.NodeTypeUniqueId, a.ShortName, a.LongName, a.NumInstanceMin,
		a.NumInstanceMax, a.CreateDate, a.UpdateDate
	FROM NodeProfile a
	JOIN @NodeProfileUniqueIds b
	ON a.NodeProfileUniqueId = b.NodeProfileUniqueId

	-- return NodeProfileCompatibleNodeProfile
	SELECT a.NodeProfileUniqueId, a.CompatibleNodeProfileUniqueId, a.IsCompatible, a.IsActive,
		a.CreateDate, a.UpdateDate
	FROM NodeProfileCompatibleNodeProfile a
	JOIN @NodeProfileUniqueIds b
	ON a.NodeProfileUniqueId = b.NodeProfileUniqueId

	-- return NodeProfileCustomProperty
	SELECT a.NodeProfileCustomPropertyId, a.NodeProfileUniqueId, a.KeyText, a.DataTypeId,
		a.CreateDate, a.UpdateDate
	FROM NodeProfileCustomProperty a
	JOIN @NodeProfileUniqueIds b
	ON a.NodeProfileUniqueId = b.NodeProfileUniqueId

	-- return NodeProfileCustomPropertyValue
	SELECT a.NodeProfileUniqueId, a.NodeProfileCustomPropertyId, a.CustomPropertyValue,
		a.CreateDate, a.UpdateDate
	FROM NodeProfileCustomPropertyValue a
	JOIN @NodeProfileUniqueIds b
	ON a.NodeProfileUniqueId = b.NodeProfileUniqueId

	-- return NodeProfileSupportedConnectorProfile
	SELECT a.NodeProfileSupportedConnectorProfileId, a.NodeProfileUniqueId,
		a.ConnectorProfileUniqueId, a.IsSupported, a.IsActive, a.CreateDate, a.UpdateDate
	FROM NodeProfileSupportedConnectorProfile a
	JOIN @NodeProfileUniqueIds b
	ON a.NodeProfileUniqueId = b.NodeProfileUniqueId
	
	-- return NodeType
	SELECT a.NodeTypeUniqueId, a.UniqueNodeTypeName, a.ShortName, a.LongName, a.IsVirtual,
		a.NumInstanceMin, a.NumInstanceMax, a.CreateDate, a.UpdateDate
	FROM NodeType a
	JOIN @NodeTypeUniqueIds b
	ON a.NodeTypeUniqueId = b.NodeTypeUniqueId

	-- return NodeTypeCustomProperty
	SELECT a.NodeTypeCustomPropertyId, a.NodeTypeUniqueId, a.KeyText, a.DataTypeId, a.CreateDate, a.UpdateDate
	FROM NodeTypeCustomProperty a
	JOIN @NodeTypeUniqueIds b
	ON a.NodeTypeUniqueId = b.NodeTypeUniqueId

	-- return NodeTypeCustomPropertyValue
	SELECT a.NodeTypeUniqueId, a.NodeTypeCustomPropertyId, a.CustomPropertyValue, a.CreateDate, a.UpdateDate
	FROM NodeTypeCustomPropertyValue a
	JOIN @NodeTypeUniqueIds b
	ON a.NodeTypeUniqueId = b.NodeTypeUniqueId
	
	-- return NodeTypeSupportedConnectorType
	SELECT a.NodeTypeSupportedConnectorTypeId, a.NodeTypeUniqueId, a.ConnectorTypeUniqueId,
		a.IsSupported, a.IsActive, a.CreateDate, a.UpdateDate
	FROM NodeTypeSupportedConnectorType a
	JOIN @NodeTypeUniqueIds b
	ON a.NodeTypeUniqueId = b.NodeTypeUniqueId
	
	-- return PipeItem
	SELECT a.PipeItemUniqueId, a.PipeProfileUniqueId, a.ShortName, a.LongName,
		a.NodeItemUniqueIdProducer, a.NodeItemUniqueIdConsumer,
		a.ConnectorItemUniqueIdProducer, a.ConnectorItemUniqueIdConsumer, a.RequestedStateStatus,
		a.StateStatus, a.CustomStatusFlags, a.CreateDate, a.UpdateDate
	FROM PipeItem a
	JOIN @PipeItemUniqueIds b
	ON a.PipeItemUniqueId = b.PipeItemUniqueId

	-- return PipeItemCustomProperty
	SELECT a.PipeItemCustomPropertyId, a.PipeItemUniqueId, a.KeyText, a.DataTypeId, a.CreateDate, a.UpdateDate
	FROM PipeItemCustomProperty a
	JOIN @PipeItemUniqueIds b
	ON a.PipeItemUniqueId = b.PipeItemUniqueId

	-- return PipeItemCustomPropertyValue
	SELECT a.PipeItemUniqueId, a.PipeItemCustomPropertyId, a.CustomPropertyValue, a.CreateDate, a.UpdateDate
	FROM PipeItemCustomPropertyValue a
	JOIN @PipeItemUniqueIds b
	ON a.PipeItemUniqueId = b.PipeItemUniqueId

	-- return PipeItemCustomStatusFlag
	SELECT BitPosition, FlagDescription, CreateDate, UpdateDate
	FROM PipeItemCustomStatusFlag
	WHERE IsActive IS NULL or IsActive = 1

	
	-- return PipeProfile
	SELECT a.PipeProfileUniqueId, a.ShortName, a.LongName, a.ConnectorProfileUniqueIdProducer,
		a.ConnectorProfileUniqueIdConsumer, a.CreateDate, a.UpdateDate
	FROM PipeProfile a
	JOIN @PipeProfileUniqueIds b
	ON a.PipeProfileUniqueId = b.PipeProfileUniqueId

	-- return PipeProfileCustomProperty
	SELECT a.PipeProfileCustomPropertyId, a.PipeProfileUniqueId, a.KeyText, a.DataTypeId,
		a.CreateDate, a.UpdateDate
	FROM PipeProfileCustomProperty a
	JOIN @PipeProfileUniqueIds b
	ON a.PipeProfileUniqueId = b.PipeProfileUniqueId

	-- return PipeProfileCustomPropertyValue
	SELECT a.PipeProfileUniqueId, a.PipeProfileCustomPropertyId, a.CustomPropertyValue,
		a.CreateDate, a.UpdateDate
	FROM PipeProfileCustomPropertyValue a
	JOIN @PipeProfileUniqueIds b
	ON a.PipeProfileUniqueId = b.PipeProfileUniqueId

	-- return UniqueIdStateRepositoryXref
	SELECT UniqueId, StateRepositoryUniqueId, Id, Comment, CreateDate, UpdateDate
	FROM UniqueIdStateRepositoryXref
	WHERE IsActive IS NULL or IsActive = 1


END


ALTER PROCEDURE dbo.ReplicateNode (
	@originaluniqueid		varchar (50),
	@newuniqueid		varchar (50) = NULL
)
AS
BEGIN

	IF @newuniqueid IS NULL
	BEGIN
		SELECT @newuniqueid = CONVERT (varchar (50), NEWID ())		
	END

	-- insert UniqueIdStateRepositoryXref
	INSERT INTO UniqueIdStateRepositoryXref
	(
		UniqueId,
		StateRepositoryUniqueId,
		Id,
		Comment,
		IsActive
	)
	SELECT
		@newuniqueid,
		StateRepositoryUniqueId,
		Id,
		Comment,
		0	-- IsActive
	FROM UniqueIdStateRepositoryXref
	WHERE UniqueId = @originaluniqueid

	-- insert NodeItem
	INSERT INTO NodeItem
	(
		NodeItemUniqueId,
		NodeProfileUniqueId,
		ShortName,
		LongName,
		NumInstanceMin,
		NumInstanceMax,
		RequestedStateStatus,
		StateStatus,
		CustomStatusFlags,
		IsActive
	)
	SELECT
		@newuniqueid, 
		NodeProfileUniqueId, 
		ShortName, 
		LongName, 
		NumInstanceMin,
		NumInstanceMax, 
		RequestedStateStatus, 
		StateStatus, 
		CustomStatusFlags, 
		IsActive
	FROM NodeItem
	WHERE NodeItemUniqueId = @originaluniqueid

	-- insert NodeItemCustomProperty
	INSERT INTO NodeItemCustomProperty
	(
		NodeItemCustomPropertyId,
		NodeItemUniqueId,
		KeyText,
		DataTypeId,
		IsActive
	)
	SELECT
		NodeItemCustomPropertyId,
		@newuniqueid,
		KeyText,
		DataTypeId,
		IsActive
	FROM NodeItemCustomProperty
	WHERE NodeItemUniqueId = @originaluniqueid

	-- insert NodeItemCustomPropertyValue
	INSERT INTO NodeItemCustomPropertyValue
	(
		NodeItemUniqueId,
		NodeItemCustomPropertyId,
		CustomPropertyValue,
		IsActive
	)
	SELECT
		@newuniqueid,
		NodeItemCustomPropertyId,
		CustomPropertyValue,
		IsActive
	FROM NodeItemCustomPropertyValue
	WHERE NodeItemUniqueId = @originaluniqueid


END


ALTER PROCEDURE dbo.ReplicatePipe (
	@pipeoriginaluniqueid		varchar (50),
	@pipenewuniqueid		varchar (50) = NULL,
	@producernodenewuniqueid		varchar (50) = NULL,
	@producerconnectornewuniqueid		varchar (50) = NULL,
	@consumernodenewuniqueid		varchar (50) = NULL,
	@consumerconnectornewuniqueid		varchar (50) = NULL
)
AS
BEGIN

	DECLARE @producerconnectororiginaluniqueid varchar (50)
	DECLARE @consumerconnectororiginaluniqueid varchar (50)
	
	IF @pipenewuniqueid IS NULL
	BEGIN
		SELECT @pipenewuniqueid = CONVERT (varchar (50), NEWID ())		
	END

	IF @producernodenewuniqueid IS NULL
	BEGIN
		SELECT @producernodenewuniqueid = CONVERT (varchar (50), NEWID ())		
	END

	IF @consumernodenewuniqueid IS NULL
	BEGIN
		SELECT @consumernodenewuniqueid = CONVERT (varchar (50), NEWID ())		
	END

	IF @producerconnectornewuniqueid IS NULL
	BEGIN
		SELECT @producerconnectornewuniqueid = CONVERT (varchar (50), NEWID ())		
	END

	IF @consumerconnectornewuniqueid IS NULL
	BEGIN
		SELECT @consumerconnectornewuniqueid = CONVERT (varchar (50), NEWID ())		
	END
	
	
	-- find producer connector
	SELECT @producerconnectororiginaluniqueid = ConnectorItemUniqueId
	FROM ConnectorItem ci
	JOIN ConnectorProfile cp
	ON
		cp.ConnectorProfileUniqueId = ci.ConnectorProfileUniqueId
	JOIN ConnectorType ct
	ON
		ct.ConnectorTypeUniqueId = cp.ConnectorTypeUniqueId
	WHERE
		ci.PipeItemUniqueId = @pipeoriginaluniqueid
		AND ct.IsProducer = 1
		AND (ct.IsActive IS NULL OR ct.IsActive = 1)
		
	-- find consumer connector
	SELECT @consumerconnectororiginaluniqueid = ConnectorItemUniqueId
	FROM ConnectorItem ci
	JOIN ConnectorProfile cp
	ON
		cp.ConnectorProfileUniqueId = ci.ConnectorProfileUniqueId
	JOIN ConnectorType ct
	ON
		ct.ConnectorTypeUniqueId = cp.ConnectorTypeUniqueId
	WHERE
		ci.PipeItemUniqueId = @pipeoriginaluniqueid
		AND ct.IsProducer = 0
		AND (ct.IsActive IS NULL OR ct.IsActive = 1)
	


	-- insert PipeItem
	INSERT INTO PipeItem
	(
		PipeItemUniqueId,
		PipeProfileUniqueId,
		ShortName,
		LongName,
		NodeItemUniqueIdProducer,
		NodeItemUniqueIdConsumer,
		ConnectorItemUniqueIdProducer,
		ConnectorItemUniqueIdConsumer,
		RequestedStateStatus,
		StateStatus,
		CustomStatusFlags,
		IsActive
	)
	SELECT
		@pipenewuniqueid,
		PipeProfileUniqueId,
		ShortName,
		LongName,
		@producernodenewuniqueid,
		@consumernodenewuniqueid,
		@producerconnectornewuniqueid,
		@consumerconnectornewuniqueid,
		'',
		'',
		CustomStatusFlags,
		IsActive	
	FROM PipeItem
	WHERE PipeItemUniqueId = @pipeoriginaluniqueid	

	-- insert PipeItemCustomProperty
	INSERT INTO PipeItemCustomProperty
	(
		PipeItemCustomPropertyId,
		PipeItemUniqueId,
		KeyText,
		DataTypeId,
		IsActive
	)
	SELECT
		PipeItemCustomPropertyId,
		@pipenewuniqueid,
		KeyText,
		DataTypeId,
		IsActive	
	FROM PipeItemCustomProperty
	WHERE PipeItemUniqueId = @pipeoriginaluniqueid	

	-- insert PipeItemCustomPropertyValue
	INSERT INTO PipeItemCustomPropertyValue
	(
		PipeItemUniqueId,
		PipeItemCustomPropertyId,
		CustomPropertyValue,
		IsActive
	)
	SELECT
		@pipenewuniqueid,
		PipeItemCustomPropertyId,
		CustomPropertyValue,
		IsActive	
	FROM PipeItemCustomPropertyValue
	WHERE PipeItemUniqueId = @pipeoriginaluniqueid	

	
	-- insert ConnectorItem
	INSERT INTO ConnectorItem
	(
		ConnectorItemUniqueId,
		NodeItemUniqueId,
		PipeItemUniqueId,
		ConnectorProfileUniqueId,
		ShortName,
		LongName,
		NumInstanceMin,
		NumInstanceMax,
		RequestedStateStatus,
		StateStatus,
		CustomStatusFlags,
		IsActive
	)
	SELECT
		@producerconnectornewuniqueid,
		@producernodenewuniqueid,
		@pipenewuniqueid,
		ConnectorProfileUniqueId,
		ShortName,
		LongName,
		NumInstanceMin,
		NumInstanceMax,
		'',
		'',
		CustomStatusFlags,
		IsActive
	FROM ConnectorItem
	WHERE ConnectorItemUniqueId = @producerconnectororiginaluniqueid


	INSERT INTO ConnectorItem
	(
		ConnectorItemUniqueId,
		NodeItemUniqueId,
		PipeItemUniqueId,
		ConnectorProfileUniqueId,
		ShortName,
		LongName,
		NumInstanceMin,
		NumInstanceMax,
		RequestedStateStatus,
		StateStatus,
		CustomStatusFlags,
		IsActive
	)
	SELECT
		@consumerconnectornewuniqueid,
		@consumernodenewuniqueid,
		@pipenewuniqueid,
		ConnectorProfileUniqueId,
		ShortName,
		LongName,
		NumInstanceMin,
		NumInstanceMax,
		'',
		'',
		CustomStatusFlags,
		IsActive
	FROM ConnectorItem
	WHERE ConnectorItemUniqueId = @consumerconnectororiginaluniqueid	
	

	-- insert ConnectorItemCustomProperty
	INSERT INTO ConnectorItemCustomProperty
	(
		ConnectorItemCustomPropertyId,
		ConnectorItemUniqueId,
		KeyText,
		DataTypeId,
		IsActive
	)
	SELECT
		ConnectorItemCustomPropertyId,
		@producerconnectornewuniqueid,
		KeyText,
		DataTypeId,
		IsActive	
	FROM ConnectorItemCustomProperty
	WHERE ConnectorItemUniqueId = @producerconnectororiginaluniqueid	

	INSERT INTO ConnectorItemCustomProperty
	(
		ConnectorItemCustomPropertyId,
		ConnectorItemUniqueId,
		KeyText,
		DataTypeId,
		IsActive
	)
	SELECT
		ConnectorItemCustomPropertyId,
		@consumerconnectornewuniqueid,
		KeyText,
		DataTypeId,
		IsActive	
	FROM ConnectorItemCustomProperty
	WHERE ConnectorItemUniqueId = @consumerconnectororiginaluniqueid	

	-- insert ConnectorItemCustomPropertyValue
	INSERT INTO ConnectorItemCustomPropertyValue
	(
		ConnectorItemUniqueId,
		ConnectorItemCustomPropertyId,
		CustomPropertyValue,
		IsActive
	)
	SELECT
		@producerconnectornewuniqueid,
		ConnectorItemCustomPropertyId,
		CustomPropertyValue,
		IsActive	
	FROM ConnectorItemCustomPropertyValue
	WHERE ConnectorItemUniqueId = @producerconnectororiginaluniqueid	

	INSERT INTO ConnectorItemCustomPropertyValue
	(
		ConnectorItemUniqueId,
		ConnectorItemCustomPropertyId,
		CustomPropertyValue,
		IsActive
	)
	SELECT
		@consumerconnectornewuniqueid,
		ConnectorItemCustomPropertyId,
		CustomPropertyValue,
		IsActive	
	FROM ConnectorItemCustomPropertyValue
	WHERE ConnectorItemUniqueId = @consumerconnectororiginaluniqueid	

END