Create the Data Interface Manually

If you have followed the steps from the Create the ERP Connector Database and Create the Data Interface Automatically, and you succesfuly created the data interface tables, you don't need to follow the steps from this section. Use them only for your reference.

In case the database to be used for the ERP Connector has different name than Ec_Vault, the data interface tables will not be created automatically upon first connection to the database from the ERP Connector. Instead, run the below script on the database to create the tables manually before you connect to the database from the ERP Connector configuration window.

5KB

EcCreateDataInterface.sql

Open

Data Interface creation SQL script for download

/****** Be.Smart ERP Connector - Data Interface Create ******/

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

/****** Object:  Table [dbo].[EcENTITY] ******/

CREATE TABLE [dbo].[EcENTITY](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[bulk_id] [int] NOT NULL,
	[ent_class_id] [nvarchar](10) NOT NULL,
	[guid] [nvarchar](36) NULL,
	[name] [nvarchar](100) NOT NULL,
	[master_id] [bigint] NULL,
	[iter_id] [bigint] NULL,
	[revision] [nvarchar](12) NULL,
	[version] [int] NULL,
	[modified] [datetime] NULL,
	[category] [nvarchar](60) NULL,
	[lfcycst_id] [int] NULL,
	[lfcycst_name] [nvarchar](60) NULL,
	[base_qty] [float] NULL,
	[base_uom] [nvarchar](10) NULL,
	[requested_by] [nvarchar](50) NULL,
	[comm_dir] [int] NOT NULL,
	[comm_status] [smallint] NOT NULL,
	[thumbnail] [varbinary](max) NULL,
	[created] [datetime] NOT NULL,
	[processed1] [datetime] NULL,
	[processed2] [datetime] NULL,
 CONSTRAINT [PK_EcENTITY_Id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[EcENTITY] ADD  DEFAULT ('') FOR [base_uom]

ALTER TABLE [dbo].[EcENTITY] ADD  DEFAULT ((-1)) FOR [comm_status]

ALTER TABLE [dbo].[EcENTITY] ADD  DEFAULT (getdate()) FOR [created]



/****** Object:  Table [dbo].[EcASSOC] ******/

CREATE TABLE [dbo].[EcASSOC](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[assoc_type] [smallint] NOT NULL,
	[ent_id_parent] [bigint] NOT NULL,
	[ent_id_child] [bigint] NOT NULL,
	[qty_occ] [int] NULL,
	[qty_total] [float] NULL,
	[position] [nvarchar](6) NULL,
	[row_number] [nvarchar](6) NULL,
	[driven_by_cad] [bit] NULL,
 CONSTRAINT [PK_EcASSOC_Id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[EcASSOC] ADD  DEFAULT ((0)) FOR [driven_by_cad]

ALTER TABLE [dbo].[EcASSOC]  WITH CHECK ADD  CONSTRAINT [FK_AssocCldToEntity] FOREIGN KEY([ent_id_child])
REFERENCES [dbo].[EcENTITY] ([id])

ALTER TABLE [dbo].[EcASSOC] CHECK CONSTRAINT [FK_AssocCldToEntity]

ALTER TABLE [dbo].[EcASSOC]  WITH CHECK ADD  CONSTRAINT [FK_AssocParToEntity] FOREIGN KEY([ent_id_parent])
REFERENCES [dbo].[EcENTITY] ([id])

ALTER TABLE [dbo].[EcASSOC] CHECK CONSTRAINT [FK_AssocParToEntity]



/****** Object:  Table [dbo].[EcLINK]    Script Date: 17.2.2017 ******/

CREATE TABLE [dbo].[EcLINK](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[ent_id] [bigint] NOT NULL,
	[link_type] [smallint] NOT NULL,
	[vault] [nvarchar](20) NULL,
	[object_type] [nvarchar](20) NULL,
	[object_name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_EcLINK_Id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[EcLINK]  WITH CHECK ADD  CONSTRAINT [FK_LinkToEntity] FOREIGN KEY([ent_id])
REFERENCES [dbo].[EcENTITY] ([id])

ALTER TABLE [dbo].[EcLINK] CHECK CONSTRAINT [FK_LinkToEntity]


/****** Object:  Table [dbo].[EcLOG] ******/

CREATE TABLE [dbo].[EcLOG](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[ent_id] [bigint] NOT NULL,
	[logged] [datetime] NOT NULL,
	[level] [smallint] NOT NULL,
	[message] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_EcLOG_Id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ALTER TABLE [dbo].[EcLOG] ADD  DEFAULT ((-1)) FOR [ent_id]

ALTER TABLE [dbo].[EcLOG] ADD  DEFAULT (getdate()) FOR [logged]


/****** Object:  StoredProcedure [dbo].[EcWriteEntity] ******/

IF OBJECT_ID(N'[dbo].[EcWriteEntity]', 'P') IS NULL
EXEC('CREATE PROCEDURE[dbo].[EcWriteEntity]
@EntName NVARCHAR(100),
@BulkID INT = -1 OUT,
@EntClassID NVARCHAR(10) = ''ITEM''

AS
BEGIN
	-- check mandatory fields:
	IF @EntClassID IS NULL

    BEGIN
        RAISERROR(''@EntClassID parameter is NULL'', 16, 1);
        RETURN -1;
	END

    IF @EntName IS NULL
    BEGIN

        RAISERROR(''@EntName parameter is NULL'', 16, 1);
        RETURN -1;
	END

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements:

    SET NOCOUNT ON;

	DECLARE @InsertedID BIGINT;
	DECLARE @result TABLE(bid int, rid bigint);

        IF @BulkID IS NULL OR @BulkID< 0

        BEGIN
            INSERT INTO EcENTITY
		(ent_class_id, name, comm_dir, bulk_id)

        OUTPUT INSERTED.bulk_id, INSERTED.id INTO @result
        VALUES
		( @EntClassID, @EntName, 1, ISNULL((SELECT MAX(bulk_id) FROM EcENTITY), 0) + 1);
        END
        ELSE

    BEGIN
        INSERT INTO EcENTITY
        (ent_class_id, name, comm_dir, bulk_id)

        OUTPUT INSERTED.bulk_id, INSERTED.ID INTO @result
        VALUES
		( @EntClassID, @EntName, 1, @BulkID);
        END

        SET @BulkID = (SELECT TOP 1 bid FROM @result);
	SET @InsertedID = (SELECT TOP 1 rid FROM @result);
	RETURN @InsertedID;
END'
)