USE [master]
GO

CREATE DATABASE [Demo_Maximo]
GO

USE [Demo_Maximo]
GO

CREATE TABLE dbo.[Meters] (
	UNID UNIQUEIDENTIFIER NOT NULL,
	UNIT VARCHAR(50) NULL,
	Function_CD VARCHAR(200) NULL,
	Location_Name VARCHAR(200) NULL,
	TVA_ALIAS VARCHAR(50) NULL,
	Description VARCHAR(200) NULL,
	MeterSector VARCHAR(200) NULL,
	MSCO VARCHAR(200) NULL,
	StationLine VARCHAR(200) NULL,
	Asset_Desc VARCHAR(200) NULL,
	Serial_No VARCHAR(50) NULL,
	TVA_Model_No VARCHAR(50) NULL,
	ASSET_STATUS_CD VARCHAR(200) NULL,
	Company_Name VARCHAR(200) NULL
)
GO

USE [Demo_XDA]
GO

UPDATE ExternalDatabases
SET ConnectionString = 'Data Source=vmhostsql; Initial Catalog=Demo_Maximo; Integrated Security=SSPI'
WHERE Name = 'dbMaximo'
GO

INSERT INTO extDBTables (TableName,Query,ExtDBID)
VALUES ('Meters','
	(
        SELECT 
	    UNID AS "Maximo Asset Number (UID)",
	    UNIT_CD AS "Unit",
	    Function_CD AS "Function",
	    Location_Name AS "Location Name",
	    TVA_ALIAS AS "TVA Alias",
	    Description AS "Description",
	    MeterSector AS "Meter Sector",
	    MSCO AS "MSCO",
	    StationLine AS "Station/Line",
	    Asset_Desc AS "Asset Desc",
	    Serial_No AS "Serial No (Maximo)",
	    TVA_Model_No AS "TVA Model No",
	    ASSET_STATUS_CD AS "Status",
	    Company_Name AS "Manufacturer",
	    Company_Name AS "Supplier"
    FROM Meters
	WHERE "Maximo Asset Number (UID)" LIKE {key} OR {key} IS NULL
    )',(SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo'))
GO

INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Maximo Asset Number (UID)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),1,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Function','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Location Name','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Meter Sector','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','TVA Model No','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Unit','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Asset Desc','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Serial No (Maximo)','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Station/Line','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','MSCO','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Supplier','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Manufacturer','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','Status','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO
INSERT INTO AdditionalField (ParentTable,FieldName,Type,IsSecure,Searchable,ExternalDBTableID,IsKey,IsInfo) VALUES ('Meter','TVA Alias','string',0,0,(SELECT ID FROM extDBTables WHERE TableName = 'Meters' AND ExtDBID = (SELECT ID FROM ExternalDatabases WHERE Name = 'dbMaximo')),0,0)
GO

INSERT INTO ExternalOpenXDAField (ParentTable,FieldName,ExternalDBTableID) VALUES ('Meter','Description',(SELECT ID FROM extDBTables WHERE TableName = 'Meters'))
GO