Create the Data Interface Manually
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.
/****** 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'
)
Last updated
Was this helpful?