74 lines
2.5 KiB
Transact-SQL
74 lines
2.5 KiB
Transact-SQL
USE DD_ECM
|
|
|
|
IF (SELECT NAME FROM [TBMT_FUNCTIONS] WHERE NAME = 'ADDRESS') IS NULL
|
|
BEGIN
|
|
INSERT INTO TBMT_FUNCTIONS (NAME, DESCRIPTION, [PARAMETERS], ACTIVE, ADDED_WHO)
|
|
VALUES ('ADDRESS', 'Finds an Address by Winline Account Number', 'Name,Street,Zip,City', 1, 'UPDATE-006')
|
|
END
|
|
|
|
|
|
CREATE TABLE [dbo].[TBMT_TEMPLATE_ITEMS_FUNCTIONS](
|
|
[GUID] [int] IDENTITY(1,1) NOT NULL,
|
|
[TEMPLATE_ITEM_ID] [int] NOT NULL,
|
|
[FUNCTION_ID] [int] NOT NULL,
|
|
[FUNCTION_PARAMETERS] [nvarchar](max) NULL,
|
|
[SEQUENCE] [int] NULL,
|
|
[ADDED_WHEN] [datetime] NULL,
|
|
[ADDED_WHO] [nvarchar](50) NULL,
|
|
[CHANGED_WHEN] [datetime] NULL,
|
|
[CHANGED_WHO] [nvarchar](50) NULL
|
|
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[TBMT_TEMPLATE_ITEMS_FUNCTIONS] ADD CONSTRAINT [DF_TBMT_TEMPLATE_ITEMS_FUNCTIONS_ADDED_WHEN] DEFAULT (getdate()) FOR [ADDED_WHEN]
|
|
GO
|
|
ALTER TABLE [dbo].[TBMT_TEMPLATE_ITEMS_FUNCTIONS] ADD CONSTRAINT [DF_TBMT_TEMPLATE_ITEMS_FUNCTIONS_SEQUENCE] DEFAULT (0) FOR [SEQUENCE]
|
|
GO
|
|
|
|
IF (SELECT COUNT(*) FROM TBMT_TEMPLATE_ITEMS_FUNCTIONS) = 0
|
|
BEGIN
|
|
INSERT INTO TBMT_TEMPLATE_ITEMS_FUNCTIONS (TEMPLATE_ITEM_ID, FUNCTION_ID, FUNCTION_PARAMETERS, ADDED_WHO)
|
|
SELECT GUID AS TEMPLATE_ITEM_ID, FUNCTION_ID, FUNCTION_PARAMETERS, 'UPDATE-006' AS ADDED_WHO
|
|
FROM TBMT_TEMPLATE_ITEMS
|
|
WHERE FUNCTION_ID IS NOT NULL
|
|
END
|
|
GO
|
|
|
|
ALTER TABLE TBMT_TEMPLATE_ITEMS DROP COLUMN FUNCTION_ID
|
|
ALTER TABLE TBMT_TEMPLATE_ITEMS DROP COLUMN FUNCTION_PARAMETERS
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[VWMT_ITEMS]
|
|
AS
|
|
SELECT DISTINCT
|
|
[TBITEMS].[GUID] AS ITEM_ID
|
|
,[TBTEMPLATES].[NAME] [TEMPLATE_NAME]
|
|
,[TBTABLES].[NAME] AS [TABLE_NAME]
|
|
,[TBITEMS].[NAME] AS [ITEM_NAME]
|
|
,[TBTYPES].[NAME] AS [DATA_TYPE]
|
|
,[TBTABLES].[IS_HEAD] AS [IS_HEAD]
|
|
,[TBITEMS].[IS_READ_ONLY]
|
|
,[TBITEMS].[IS_VISIBLE]
|
|
,[TBITEMS].[IS_REQUIRED]
|
|
,[TBITEMS].[IS_VIRTUAL]
|
|
,[TBITEMS].[ORDER_KEY]
|
|
,[TBITEMS].[PREFER_EXTERNAL]
|
|
FROM
|
|
[dbo].[TBMT_TEMPLATE_ITEMS] [TBITEMS]
|
|
INNER JOIN [dbo].[TBMT_TABLES] [TBTABLES] ON [TBITEMS].[TABLE_ID] = [TBTABLES].[GUID]
|
|
INNER JOIN [dbo].[TBMT_TYPES] [TBTYPES] ON [TBITEMS].[TYPE_ID] = [TBTYPES].[GUID]
|
|
INNER JOIN [dbo].[TBMT_TEMPLATES] [TBTEMPLATES] ON [TBTABLES].[TEMPLATE_ID] = [TBTEMPLATES].[GUID]
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[VWMT_FUNCTIONS]
|
|
AS
|
|
SELECT
|
|
TBITEMS.TEMPLATE_ITEM_ID AS ITEM_ID,
|
|
TBFUNCTIONS.NAME FUNCTION_NAME,
|
|
TBITEMS.FUNCTION_ID,
|
|
TBITEMS.FUNCTION_PARAMETERS,
|
|
TBITEMS.SEQUENCE
|
|
FROM TBMT_TEMPLATE_ITEMS_FUNCTIONS TBITEMS
|
|
LEFT OUTER JOIN [dbo].[TBMT_FUNCTIONS] AS [TBFUNCTIONS] ON [TBITEMS].[FUNCTION_ID] = [TBFUNCTIONS].[GUID]
|
|
ORDER BY ITEM_ID, SEQUENCE
|
|
GO |