/***************************************************/
/*** STANDARD MODULE SYBASE DATABASE UPDATE FILE ***/
/***************************************************/

/*************************************************************/
/* Create a backup of the database first in the standard location */
/*************************************************************/
IF EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_BackupDatabase') THEN

  CALL "DBA".SM_BackupDatabase();

ELSE
  BACKUP DATABASE
    DIRECTORY 'C:\\StdMod_Backup';
END IF;


/********************************************************************/
/* 13/2/2004 - Add new fields to SM_Object_Class, and triggers to maintain them */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'Key_Field') THEN

  ALTER TABLE "DBA".SM_Object_Class
  ADD Key_Field VARCHAR(50);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'ListScript') THEN

  ALTER TABLE "DBA".SM_Object_Class
  ADD ListScript GEN_HUGE_DESCRIPTION;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'DateTime_Created') THEN

  ALTER TABLE "DBA".SM_Object_Class
  ADD DateTime_Created Entity_DateTime_Def_Current    not null;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'DateTime_Modified') THEN

  ALTER TABLE "DBA".SM_Object_Class
  ADD DateTime_Modified Entity_DateTime_Def_Current    not null;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'User_Created') THEN

  ALTER TABLE "DBA".SM_Object_Class
  ADD User_Created VARCHAR(50);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'User_Modified') THEN

  ALTER TABLE "DBA".SM_Object_Class
  ADD User_Modified VARCHAR(50);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systrigger JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND trigger_name = 'bi_SM_Object_Class') THEN

CREATE TRIGGER bi_SM_Object_Class BEFORE INSERT ON SM_Object_Class
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systrigger JOIN systable
  WHERE table_name = 'SM_Object_Class'
  AND trigger_name = 'bu_SM_Object_Class') THEN

CREATE TRIGGER bu_SM_Object_Class BEFORE UPDATE ON SM_Object_Class
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;


/********************************************************************/
/* 13/2/2004 - Add Items_Exist field to SM_Authorisation_Config, and triggers to */
/*   SM_Authorisation_Item to maintain it                           */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Authorisation_Config'
  AND column_name = 'Items_Exist') THEN

  --NOTE: NOT NULL constraint must be omitted as there may be records in the table
  ALTER TABLE "DBA".SM_Authorisation_Config
  ADD Items_Exist BOOLEAN DEFAULT 'N';

  --Initialise flag for all items already in table
  UPDATE SM_Authorisation_Config AC
  SET Items_Exist = 'Y'
  WHERE EXISTS(SELECT 1 FROM SM_Authorisation_Item AI WHERE AI.Authorisation_Config_No = AC.Authorisation_Config_No);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systrigger JOIN systable
  WHERE table_name = 'SM_Authorisation_Item'
  AND trigger_name = 'ai_SM_Authorisation_Item') THEN

CREATE TRIGGER ai_SM_Authorisation_Item AFTER INSERT ON SM_Authorisation_Item
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  UPDATE SM_Authorisation_Config
  SET Items_Exist = 'Y'
  WHERE Authorisation_Config_No = new_Row.Authorisation_Config_No;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systrigger JOIN systable
  WHERE table_name = 'SM_Authorisation_Item'
  AND trigger_name = 'ad_SM_Authorisation_Item') THEN

CREATE TRIGGER ad_SM_Authorisation_Item AFTER DELETE ON SM_Authorisation_Item
REFERENCING OLD AS old_Row
FOR EACH ROW
BEGIN
  IF NOT EXISTS(
    SELECT 1 FROM SM_Authorisation_Item
    WHERE Authorisation_Config_No = old_Row.Authorisation_Config_No) THEN

    UPDATE SM_Authorisation_Config
    SET Items_Exist = 'N'
    WHERE Authorisation_Config_No = old_Row.Authorisation_Config_No;
    
  END IF;
END;

END IF;

/**********************************************************/
/* 09/03/2004 - Add Customer_Ref field to Site Table      */
/**********************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_SITE'
  AND column_name = 'Customer_Ref') THEN

  ALTER TABLE SM_Site ADD Customer_Ref VarChar(20);

END IF;

/*==============================================================*/
/* View: Default_View_1042 Include IO Devices as these can      */
/* include card reader functionality                            */
/*==============================================================*/
if exists(select 1 from sys.systable where table_name='Default_View_1042' and table_type='VIEW') then
   drop view Default_View_1042
end if;

create view Default_View_1042 as
select H.Header_Name as "Card Reader",S.Site_Code,S.Description as "Site Description",H.Config_Header_No from
    SM_Enterprise_Config_Header as H left outer join SM_Site as S on H.Site_No = S.Site_No where
    H.Object_Class = 2015 OR H.Object_Class = 2017;


/*************************************************/
/* 05/04/2004 - Add Replication_Site_No field to table SM_Incident_Log */
/*************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Incident_Log'
  AND column_name = 'Replication_Site_No') THEN

  ALTER TABLE "DBA".SM_Incident_Log ADD Replication_Site_No Rep_Site_No;

END IF;


/********************************************************************/
/* 15/04/2004 - Update SM_UC_AssignConnectionUserCode procedure to create */
/*   SM_User_Connection table explicitly with "DBA" as owner        */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_UC_AssignConnectionUserCode'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER PROCEDURE "DBA".SM_UC_AssignConnectionUserCode(IN @UserID Operator_Code, IN @ProcessID INTEGER)
BEGIN
  DECLARE @ConnID UNSIGNED BIGINT;

  /* Get the connection ID of this connection */
  SET @ConnID = (SELECT connection_property('Number'));

  /* Create table if doesn't exist */
  IF NOT EXISTS(
    SELECT 1 FROM systable
    WHERE table_name = 'SM_User_Connection') THEN

    CREATE TABLE "DBA".SM_User_Connection(
      Connection_ID  UNSIGNED BIGINT,
      User_ID Operator_Code,
      Process_ID INTEGER,
      Connection_Time DATETIME DEFAULT CURRENT TIMESTAMP,
      PRIMARY KEY (Connection_ID)
    );

  END IF;

  /* Insert / update the record */
  IF NOT EXISTS(
    SELECT 1 FROM "DBA".SM_User_Connection
    WHERE Connection_ID = @ConnID) THEN

    INSERT "DBA".SM_User_Connection
    (Connection_ID, User_ID, Process_ID, Connection_Time)
    VALUES(@ConnID, @UserID, @ProcessID, GetDate());

  ELSE

    UPDATE "DBA".SM_User_Connection
    SET User_ID = @UserID, Process_ID = @ProcessID
    WHERE Connection_ID = @ConnID;

  END IF;
END;

--Add version control record
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('SM_UC_AssignConnectionUserCode', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;


/*****************************************************************************/
/* 11/05/2004 - Add Header_Name1 and Header_Name2 fields to table            */
/* SM_Enterprise_Config_Header. These fields are computed fields which       */
/* contain the LEFT and RIGHT pieces of the Header_Name field delimited by a */
/* colon (':') character. Their main use is in report filters, allowing the  */
/* Header_Name field pieces to be selected in a filter. They are also used   */
/* in Sybase reports, which would often be setup with such a filter.         */
/*****************************************************************************/
--Field Header_Name1.
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Enterprise_Config_Header'
  AND column_name = 'Header_Name1') THEN

  ALTER TABLE "DBA".SM_Enterprise_Config_Header
    add Header_Name1 varchar(128)                  
    compute (
      if charindex(':', SM_Enterprise_Config_Header.Header_Name) > 0 then
       left(SM_Enterprise_Config_Header.Header_Name, charindex(':', SM_Enterprise_Config_Header.Header_Name) - 1) 
      else
        SM_Enterprise_Config_Header.Header_Name
      endif
    );

END IF;

--Field Header_Name2.
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Enterprise_Config_Header'
  AND column_name = 'Header_Name2') THEN

  ALTER TABLE "DBA".SM_Enterprise_Config_Header
    add Header_Name2 varchar(128)                  
    compute (
      right(SM_Enterprise_Config_Header.Header_Name, length(SM_Enterprise_Config_Header.Header_Name) - charindex(':',SM_Enterprise_Config_Header.Header_Name))
    );

END IF;


/*********************************************************/
/* 21/05/2004 - Added Attachment_FileName to Email Queue */
/*********************************************************/
IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Email_Queue'
  AND column_name = 'Attachment_FileName') THEN

ALTER TABLE "DBA".SM_Email_Queue ADD Attachment_FileName VARCHAR(200);

END IF;

/*********************************************************/
/* 31/05/2004 - Add New Report Filter Fields             */
/* NOTE Fields must be specified as allowing NULL to be  */
/*  added to a non-empty table                           */
/*********************************************************/
IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_RPT_FILTER'
  AND column_name = 'Is_Visible') THEN

  ALTER TABLE "DBA".SM_RPT_FILTER ADD Is_Visible Boolean NULL Default 'Y';
  ALTER TABLE "DBA".SM_RPT_FILTER ADD Is_Locked Boolean NULL Default 'N';

END IF;


/*********************************************************************/
/* 24/08/2004 - Add new table SM_Site_Workstation. This table is a detail */
/*  table of SM_Site and contains details of individual computers    */
/*  used as workstations at the site                                 */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_Site_Workstation'
  AND table_type = 'BASE') THEN

CREATE TABLE "DBA".SM_Site_Workstation 
(
    Site_No              Entity_No                      not null,
    Site_Workstation_No  Identity_No                    not null,
    Name                 Entity_Code                    not null,
    Description          GEN_LARGE_DESCRIPTION,
    Windows_Computer_Name_1 varchar(100),
    Windows_Computer_Name_2 varchar(100),
    IP_Address_1         varchar(15),
    IP_Address_2         varchar(15),
    Available            BOOLEAN                        not null default 'Y',
    DateTime_Created     Entity_DateTime_Def_Current    not null,
    DateTime_Modified    Entity_DateTime_Def_Current    not null,
    User_Created         varchar(50),
    User_Modified        varchar(50),
    primary key (Site_No, Site_Workstation_No),
    unique (Site_No, Name),
    unique (Site_Workstation_No)
);

END IF;

--Create foreign key on the new table
IF NOT EXISTS(
  SELECT 1 from sys.sysforeignkey
  WHERE role='FK_SM_SITE_WORK_REF_SM_SITE') THEN

alter table "DBA".SM_Site_Workstation
  add foreign key FK_SM_SITE_WORK_REF_SM_SITE (Site_No)
    references "DBA".SM_Site (Site_No)
    on update restrict
    on delete cascade;

END IF;

--Create triggers on the new table
IF NOT EXISTS(
  SELECT 1 FROM sys.systrigger
  WHERE trigger_name = 'TR_I_SM_Site_Workstation') THEN

CREATE TRIGGER TR_I_SM_Site_Workstation BEFORE INSERT ON "DBA".SM_Site_Workstation
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sys.systrigger
  WHERE trigger_name = 'TR_U_SM_Site_Workstation') THEN

CREATE TRIGGER TR_U_SM_Site_Workstation BEFORE UPDATE ON "DBA".SM_Site_Workstation
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;


/*********************************************************************/
/* 25/08/2004 - Add Site Workstation reference to SM_Enterprise_Config_Header */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Enterprise_Config_Header'
  AND column_name = 'Site_Workstation_No') THEN

ALTER TABLE "DBA".SM_Enterprise_Config_Header
ADD Site_Workstation_No Entity_No;

END IF;

--Create foreign key reference
IF NOT EXISTS(
  SELECT 1 from sys.sysforeignkey
  WHERE role='FK_SM_ENTER_REF_SM_SITE_WKS') THEN

alter table "DBA".SM_Enterprise_Config_Header
   add foreign key FK_SM_ENTER_REF_SM_SITE_WKS (Site_Workstation_No)
      references "DBA".SM_Site_Workstation (Site_Workstation_No)
      on update restrict
      on delete restrict;

END IF;


/*********************************************************************/
/* 31/08/2004 - Add new stored procedures to implement Enterprise Configuration */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgCreateHeaderRec') THEN

CREATE PROCEDURE SM_EntCfgCreateHeaderRec(IN @HeaderName VARCHAR(128), IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No,
  IN @ObjectClass INT, IN @LocalSiteNo Entity_No, OUT @ConfigHeaderNo Large_Entity_No)
BEGIN
  --NOTE: @HeaderName and @LocalSiteNo MUST be specified
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;
  IF @LocalSiteNo IS NULL THEN
    RAISERROR 30000 'Local Site No must be specified';
    RETURN;
  END IF;

  INSERT "DBA".SM_Enterprise_Config_Header
  (Header_Name, Object_Class, Created_At, Site_No, Site_Workstation_No)
  VALUES(@HeaderName, @ObjectClass, @LocalSiteNo, @SiteNo, @SiteWorkstationNo);

  SET @ConfigHeaderNo = @@identity;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgCreateItemRec') THEN

CREATE PROCEDURE SM_EntCfgCreateItemRec(IN @ConfigHeaderNo Large_Entity_No, IN @ItemName VARCHAR(128),
  IN @ItemValue VARCHAR(256), OUT @ConfigItemNo Large_Entity_No)
BEGIN
  --NOTE: @ConfigHeaderNo and @ItemName MUST be specified
  IF @ConfigHeaderNo IS NULL THEN
    RAISERROR 30000 'Config Header No must be specified';
    RETURN;
  END IF;
  IF (@ItemName IS NULL) OR (@ItemName = '') THEN
    RAISERROR 30000 'Item Name must be specified';
    RETURN;
  END IF;

  INSERT "DBA".SM_Enterprise_Config_Item
  (Config_Header_No, Item_Name, Item_Value)
  VALUES(@ConfigHeaderNo, @ItemName, @ItemValue);

  SET @ConfigItemNo = @@identity;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgGetHeaderNo') THEN

CREATE FUNCTION SM_EntCfgGetHeaderNo(IN @HeaderName VARCHAR(128), IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No)
RETURNS Large_Entity_No
BEGIN
  /**********************************************************************************************/
  /* Return Config_Header_No column value for the specified site and workstation (if defined).  */
  /*                                                                                            */
  /* If no item exists for the specified site and workstation but an item exists for the site   */
  /*  only (i.e. workstation is NULL), that value is returned.                                  */
  /*                                                                                            */
  /* If no item exists for the specified site or workstation but a header exists where both are */
  /*  NULL, then that value is returned.                                                        */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RETURN NULL;
  END IF;

  SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
  FROM "DBA".SM_Enterprise_Config_Header
  WHERE (Header_Name = @HeaderName)
  AND (((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No = ISNULL(@SiteWorkstationNo, -1)))
    OR ((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No IS NULL))
    OR ((Site_No IS NULL) AND (Site_Workstation_No IS NULL)))
  ORDER BY Site_No DESC, Site_Workstation_No DESC;

  RETURN @CfgHeaderNo;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgGetItemNo') THEN

CREATE FUNCTION SM_EntCfgGetItemNo(IN @ConfigHeaderNo Large_Entity_No, IN @ItemName VARCHAR(128))
RETURNS Large_Entity_No
BEGIN
  /**********************************************************************************************/
  /* Return Config_Item_No column value for the specified Config Header No and Item Name. If the*/
  /*  specified Item does not exist, NULL is returned.                                          */
  /**********************************************************************************************/
  DECLARE @CfgItemNo Large_Entity_No;

  --NOTE: @ConfigHeaderNo and @ItemName MUST be specified
  IF @ConfigHeaderNo IS NULL THEN
    RETURN NULL;
  END IF;
  IF (@ItemName IS NULL) OR (@ItemName = '') THEN
    RETURN NULL;
  END IF;

  SELECT Config_Item_No INTO @CfgItemNo
  FROM "DBA".SM_Enterprise_Config_Item
  WHERE (Config_Header_No = @ConfigHeaderNo)
  AND (Item_Name = @ItemName);

  RETURN @CfgItemNo;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgGetItemValue') THEN

CREATE FUNCTION SM_EntCfgGetItemValue(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128),
  IN @ItemName VARCHAR(128))
RETURNS VARCHAR(128)
BEGIN
  /**********************************************************************************************/
  /* Return Item_Value column value for the specified Header and Item Name. If the specified    */
  /*  Item does not exist, NULL is returned.                                                    */
  /*                                                                                            */
  /* If no Header exists for the specified site and workstation but a Header exists for the site*/
  /*  only (i.e. workstation is NULL), the value for that Header is returned.                   */
  /*                                                                                            */
  /* If no Header exists for the specified site or workstation but a Header exists where both   */
  /*  are NULL, then the value for that Header is returned.                                     */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;
  DECLARE @ItemVal VARCHAR(128);

  --@HeaderName and @ItemName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RETURN NULL;
  END IF;
  IF @ItemName IS NULL THEN
    RETURN NULL;
  END IF;

  --Get the header no - defined site / workstation takes precedence over NULL values
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo);

  SELECT Item_Value INTO @ItemVal
  FROM "DBA".SM_Enterprise_Config_Item
  WHERE (Config_Header_No = ISNULL(@CfgHeaderNo, -1))
  AND (Item_Name = @ItemName);

  RETURN @ItemVal;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgDeleteItemGroup') THEN

CREATE PROCEDURE SM_EntCfgDeleteItemGroup(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128))
BEGIN
  /**********************************************************************************************/
  /* Delete the Config Header record corresponding to the specified @HeaderName.                */
  /*                                                                                            */
  /* If no item exists for the specified site and workstation but an item exists for the site   */
  /*  only (i.e. workstation is NULL), that record is deleted.                                  */
  /*                                                                                            */
  /* If no item exists for the specified site or workstation but a header exists where both are */
  /*  NULL, then that record is deleted.                                                        */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName must be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;

  --Get the header no
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo);

  --Delete it (don't care if exists or not as the query will still work)
  DELETE "DBA".SM_Enterprise_Config_Header
  WHERE Config_Header_No = @CfgHeaderNo;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgHeaderNames') THEN

CREATE PROCEDURE "DBA".SM_EntCfgHeaderNames(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No)
BEGIN
  /**********************************************************************************************/
  /* Return the header names for all Config Header records matching the specified site and      */
  /*  workstation.                                                                              */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  IF (@SiteNo IS NOT NULL) AND (@SiteWorkstationNo IS NOT NULL) THEN
    SELECT Header_Name
    FROM "DBA".SM_Enterprise_Config_Header
    WHERE (Site_No = @SiteNo) AND (Site_Workstation_No = @SiteWorkstationNo);
  ELSEIF (@SiteNo IS NOT NULL) AND (@SiteWorkstationNo IS NULL) THEN
    SELECT Header_Name
    FROM "DBA".SM_Enterprise_Config_Header
    WHERE (Site_No = @SiteNo) AND (Site_Workstation_No IS NULL);
  ELSE  --(@SiteNo AND @SiteWorkstationNo are null)
    SELECT Header_Name
    FROM "DBA".SM_Enterprise_Config_Header
    WHERE (Site_No IS NULL) AND (Site_Workstation_No IS NULL);
  END IF;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgItemValues') THEN

CREATE PROCEDURE SM_EntCfgItemValues(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128))
BEGIN
  /**********************************************************************************************/
  /* Return Item_Name and Item_Value columns for the specified Header Name.                     */
  /*                                                                                            */
  /* If no Header exists for the specified site and workstation but a Header exists for the site*/
  /*  only (i.e. workstation is NULL), values for that Header are returned.                     */
  /*                                                                                            */
  /* If no Header exists for the specified site or workstation but a Header exists where both   */
  /*  are NULL, then values for that Header are returned.                                       */
  /*                                                                                            */
  /* If the specified Header Name does not exist, an empty data set is returned.                */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName must be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;

  --Get the header no
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo);

  --Return the results
  SELECT Item_Name, Item_Value
  FROM "DBA".SM_Enterprise_Config_Item
  WHERE Config_Header_No = ISNULL(@CfgHeaderNo, -1)
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_EntCfgSetItemValue') THEN

CREATE PROCEDURE SM_EntCfgSetItemValue(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128),
  IN @ItemName VARCHAR(128), IN @ItemValue VARCHAR(256), IN @ObjectClass INT, IN @LocalSiteNo Entity_No)
BEGIN
  /**********************************************************************************************/
  /* Set Item_Value column value for the specified Header and Item Name. If the specified       */
  /*  Item does not exist, it is created.                                                       */
  /**********************************************************************************************/
  DECLARE @HeaderNo Large_Entity_No;
  DECLARE @ItemNo Large_Entity_No;

  --@HeaderName and @ItemName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;
  IF @ItemName IS NULL THEN
    RAISERROR 30000 'Item Name must be specified';
    RETURN;
  END IF;

  SET @HeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo);
  IF @HeaderNo IS NULL THEN
    --Local Site No required to create config header
    IF @LocalSiteNo IS NULL THEN
      RAISERROR 30000 'Local Site No must be specified';
      RETURN;
    END IF;

    CALL "DBA".SM_EntCfgCreateHeaderRec(@HeaderName, @SiteNo, @SiteWorkstationNo, @ObjectClass, @LocalSiteNo, @HeaderNo);
    CALL "DBA".SM_EntCfgCreateItemRec(@HeaderNo, @ItemName, @ItemValue, @ItemNo);
  ELSE
    SET @ItemNo = "DBA".SM_EntCfgGetItemNo(@HeaderNo, @ItemName);
    IF @ItemNo IS NULL THEN
      CALL "DBA".SM_EntCfgCreateItemRec(@HeaderNo, @ItemName, @ItemValue, @ItemNo);
    ELSE
      UPDATE "DBA".SM_Enterprise_Config_Item
      SET Item_Value = @ItemValue
      WHERE Config_Header_No = @HeaderNo
      AND Config_Item_No = @ItemNo
    END IF;
  END IF;
END;

END IF;


/*********************************************************************/
/* 31/08/2004 - Create Unique constraint on SM_Enterprise_Config_Header, */
/*  Header_Name / Site_No / Site_Workstation_No fields               */
/* NOTE: This MAY not be able to be added if existing data violates it */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysindex
  WHERE index_name = 'IDX_Header_Name') THEN

CREATE UNIQUE INDEX IDX_Header_Name ON "DBA".SM_Enterprise_Config_Header
(Header_Name ASC, Site_No ASC, Site_Workstation_No ASC);

END IF;


/*********************************************************************/
/* 18/10/2004 - Fix bug in Enterprise Config procs preventing an     */
/*  explicit header from being created for the Site and Workstation  */
/*  when these are specified in a call to SM_EntCfgSetItemValue      */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgGetHeaderNo'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER FUNCTION "DBA".SM_EntCfgGetHeaderNo(IN @HeaderName VARCHAR(128), IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No,
  IN @RequireExactMatch Boolean)
RETURNS Large_Entity_No
BEGIN
  /**********************************************************************************************/
  /* Return Config_Header_No column value for the specified site and workstation (if defined).  */
  /*                                                                                            */
  /* If no item exists for the specified site and workstation but an item exists for the site   */
  /*  only (i.e. workstation is NULL), that value is returned.                                  */
  /*                                                                                            */
  /* If no item exists for the specified site or workstation but a header exists where both are */
  /*  NULL, then that value is returned.                                                        */
  /*                                                                                            */
  /* If @RequireExactMatch = 'Y', the header returned will be the one that matches the @SiteNo  */
  /*  and @SiteWorkstationNo supplied. If no header exists with the supplied values, NULL will  */
  /*  be returned.                                                                              */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RETURN NULL;
  END IF;

  IF @RequireExactMatch = 'N' THEN
    SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
    FROM "DBA".SM_Enterprise_Config_Header
    WHERE (Header_Name = @HeaderName)
    AND (((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No = ISNULL(@SiteWorkstationNo, -1)))
      OR ((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No IS NULL))
      OR ((Site_No IS NULL) AND (Site_Workstation_No IS NULL)))
    ORDER BY Site_No DESC, Site_Workstation_No DESC;
  ELSE
    IF @SiteNo IS NULL THEN
      SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
      FROM "DBA".SM_Enterprise_Config_Header
      WHERE (Header_Name = @HeaderName)
      AND (Site_No IS NULL);
    ELSEIF @SiteWorkstationNo IS NULL THEN
      SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
      FROM "DBA".SM_Enterprise_Config_Header
      WHERE (Header_Name = @HeaderName)
      AND (Site_No = @SiteNo)
      AND (Site_Workstation_No IS NULL);
    ELSE
      SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
      FROM "DBA".SM_Enterprise_Config_Header
      WHERE (Header_Name = @HeaderName)
      AND (Site_No = @SiteNo)
      AND (Site_Workstation_No = @SiteWorkstationNo);
    END IF;
  END IF;

  RETURN @CfgHeaderNo;
END;

--Add version control record
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('SM_EntCfgGetHeaderNo', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgSetItemValue'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER PROCEDURE "DBA".SM_EntCfgSetItemValue(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128),
  IN @ItemName VARCHAR(128), IN @ItemValue VARCHAR(256), IN @ObjectClass INT, IN @LocalSiteNo Entity_No)
BEGIN
  /**********************************************************************************************/
  /* Set Item_Value column value for the specified Header and Item Name. If the specified       */
  /*  Item does not exist, it is created.                                                       */
  /**********************************************************************************************/
  DECLARE @HeaderNo Large_Entity_No;
  DECLARE @ItemNo Large_Entity_No;

  --@HeaderName and @ItemName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;
  IF @ItemName IS NULL THEN
    RAISERROR 30000 'Item Name must be specified';
    RETURN;
  END IF;

  --NOTE: A header must specifically exist for the specified Site and/or Workstation
  SET @HeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo, 'Y');
  IF @HeaderNo IS NULL THEN
    --Local Site No required to create config header
    IF @LocalSiteNo IS NULL THEN
      RAISERROR 30000 'Local Site No must be specified';
      RETURN;
    END IF;

    CALL "DBA".SM_EntCfgCreateHeaderRec(@HeaderName, @SiteNo, @SiteWorkstationNo, @ObjectClass, @LocalSiteNo, @HeaderNo);
    CALL "DBA".SM_EntCfgCreateItemRec(@HeaderNo, @ItemName, @ItemValue, @ItemNo);
  ELSE
    SET @ItemNo = "DBA".SM_EntCfgGetItemNo(@HeaderNo, @ItemName);
    IF @ItemNo IS NULL THEN
      CALL "DBA".SM_EntCfgCreateItemRec(@HeaderNo, @ItemName, @ItemValue, @ItemNo);
    ELSE
      UPDATE "DBA".SM_Enterprise_Config_Item
      SET Item_Value = @ItemValue
      WHERE Config_Header_No = @HeaderNo
      AND Config_Item_No = @ItemNo
    END IF;
  END IF;
END;

--Add version control
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('SM_EntCfgSetItemValue', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgDeleteItemGroup'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER PROCEDURE "DBA".SM_EntCfgDeleteItemGroup(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128))
BEGIN
  /**********************************************************************************************/
  /* Delete the Config Header record corresponding to the specified @HeaderName.                */
  /*                                                                                            */
  /* If no item exists for the specified site and workstation but an item exists for the site   */
  /*  only (i.e. workstation is NULL), that record is deleted.                                  */
  /*                                                                                            */
  /* If no item exists for the specified site or workstation but a header exists where both are */
  /*  NULL, then that record is deleted.                                                        */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName must be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;

  --Get the header no
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo, 'N');

  --Delete it (don't care if exists or not as the query will still work)
  DELETE "DBA".SM_Enterprise_Config_Header
  WHERE Config_Header_No = @CfgHeaderNo;
END;

--Add version control
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('SM_EntCfgDeleteItemGroup', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgGetItemValue'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER FUNCTION "DBA".SM_EntCfgGetItemValue(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128),
  IN @ItemName VARCHAR(128))
RETURNS VARCHAR(128)
BEGIN
  /**********************************************************************************************/
  /* Return Item_Value column value for the specified Header and Item Name. If the specified    */
  /*  Item does not exist, NULL is returned.                                                    */
  /*                                                                                            */
  /* If no Header exists for the specified site and workstation but a Header exists for the site*/
  /*  only (i.e. workstation is NULL), the value for that Header is returned.                   */
  /*                                                                                            */
  /* If no Header exists for the specified site or workstation but a Header exists where both   */
  /*  are NULL, then the value for that Header is returned.                                     */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;
  DECLARE @ItemVal VARCHAR(128);

  --@HeaderName and @ItemName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RETURN NULL;
  END IF;
  IF @ItemName IS NULL THEN
    RETURN NULL;
  END IF;

  --Get the header no - defined site / workstation takes precedence over NULL values
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo, 'N');

  SELECT Item_Value INTO @ItemVal
  FROM "DBA".SM_Enterprise_Config_Item
  WHERE (Config_Header_No = ISNULL(@CfgHeaderNo, -1))
  AND (Item_Name = @ItemName);

  RETURN @ItemVal;
END;

--Add version control
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('SM_EntCfgGetItemValue', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgItemValues'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER PROCEDURE "DBA".SM_EntCfgItemValues(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128))
BEGIN
  /**********************************************************************************************/
  /* Return Item_Name and Item_Value columns for the specified Header Name.                     */
  /*                                                                                            */
  /* If no Header exists for the specified site and workstation but a Header exists for the site*/
  /*  only (i.e. workstation is NULL), values for that Header are returned.                     */
  /*                                                                                            */
  /* If no Header exists for the specified site or workstation but a Header exists where both   */
  /*  are NULL, then values for that Header are returned.                                       */
  /*                                                                                            */
  /* If the specified Header Name does not exist, an empty data set is returned.                */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName must be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;

  --Get the header no
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @SiteNo, @SiteWorkstationNo, 'N');

  --Return the results
  SELECT Item_Name, Item_Value
  FROM "DBA".SM_Enterprise_Config_Item
  WHERE Config_Header_No = ISNULL(@CfgHeaderNo, -1)
END;

--Add version control
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('SM_EntCfgItemValues', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;


/****************************************************************************************/
/* 25/10/2004 - Addition of five generic customer reference fields to the Site table.   */
/* These field may be used for Customer Reference data values which are Site specific.  */
/****************************************************************************************/
IF NOT EXISTS(
  select column_name from systable
    join syscolumn on systable.table_id = syscolumn.table_id
   where table_name  = 'SM_Site'
     and column_name = 'Customer_Ref1') then

  -- Generic customer site reference fields.
  alter table "DBA".SM_Site add Customer_Ref1 varchar(20);
  alter table "DBA".SM_Site add Customer_Ref2 varchar(20);
  alter table "DBA".SM_Site add Customer_Ref3 varchar(20);
  alter table "DBA".SM_Site add Customer_Ref4 varchar(20);
  alter table "DBA".SM_Site add Customer_Ref5 varchar(20);

END IF;


/****************************************************************************************/
/* 27/10/2004 - Add index to SM_Security_Object to speed up access                      */
/****************************************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysindex
  WHERE index_name = 'IDX_SecurityAccess') THEN

CREATE INDEX IDX_SecurityAccess
ON "DBA".SM_SECURITY_OBJECT (SECURITY_CLASS_CODE ASC, PARENT_OBJECT_NO ASC, SECURITY_OBJECT_REF ASC, PRODUCT_ID ASC);

END IF;


/****************************************************************************************/
/* 29/10/2004 - Add indexes to SM_Authorisation_Item to speed up access                 */
/****************************************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysindex
  WHERE index_name = 'IDX_AuthingKey') THEN

CREATE INDEX IDX_AuthingKey ON "DBA".SM_Authorisation_Item (Authorising_Class_Key ASC);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysindex
  WHERE index_name = 'IDX_AuthedKey') THEN

CREATE INDEX IDX_AuthedKey ON "DBA".SM_Authorisation_Item (Authorised_Class_Key ASC);

END IF;


/**********************************************************************/
/* 23/11/2004 - Fix bug in Enterprise Config procs preventing items   */
/*  saved against a header with a NULL site / workstation no from     */
/*  being retrieved when a header with a specified site / workstation */
/*  exists                                                            */
/**********************************************************************/
IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgGetHeaderNo'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 2) THEN

ALTER FUNCTION "DBA".SM_EntCfgGetHeaderNo(IN @HeaderName VARCHAR(128), IN @ItemName VARCHAR(128), IN @SiteNo Entity_No,
  IN @SiteWorkstationNo Entity_No, IN @RequireExactMatch Boolean)
RETURNS Large_Entity_No
BEGIN
  /**********************************************************************************************/
  /* Return Config_Header_No column value for the specified site and workstation (if defined).  */
  /*                                                                                            */
  /* If no item exists for the specified site and workstation but an item exists for the site   */
  /*  only (i.e. workstation is NULL), that value is returned.                                  */
  /*                                                                                            */
  /* If no item exists for the specified site or workstation but a header exists where both are */
  /*  NULL, then that value is returned.                                                        */
  /*                                                                                            */
  /* If @RequireExactMatch = 'Y', the header returned will be the one that matches the @SiteNo  */
  /*  and @SiteWorkstationNo supplied. If no header exists with the supplied values, NULL will  */
  /*  be returned.                                                                              */
  /*                                                                                            */
  /* If @ItemName is non-NULL, then only headers that include the item in their detail records  */
  /*  will be considered. Otherwise, all headers that match the other criteria will be used.    */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RETURN NULL;
  END IF;

  IF @RequireExactMatch = 'N' THEN
    IF @ItemName IS NOT NULL THEN  --get only headers that also define the item
      SELECT TOP 1 H.Config_Header_No INTO @CfgHeaderNo
      FROM "DBA".SM_Enterprise_Config_Header H
      INNER JOIN "DBA".SM_Enterprise_Config_Item I ON (I.Config_Header_No = H.Config_Header_No)
      WHERE (Header_Name = @HeaderName)
      AND (Item_Name = @ItemName)
      AND (((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No = ISNULL(@SiteWorkstationNo, -1)))
        OR ((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No IS NULL))
        OR ((Site_No IS NULL) AND (Site_Workstation_No IS NULL)))
      ORDER BY Site_No DESC, Site_Workstation_No DESC;
    ELSE  --don't take the item into account
      SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
      FROM "DBA".SM_Enterprise_Config_Header
      WHERE (Header_Name = @HeaderName)
      AND (((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No = ISNULL(@SiteWorkstationNo, -1)))
        OR ((Site_No = ISNULL(@SiteNo, -1)) AND (Site_Workstation_No IS NULL))
        OR ((Site_No IS NULL) AND (Site_Workstation_No IS NULL)))
      ORDER BY Site_No DESC, Site_Workstation_No DESC;
    END IF;
  ELSE
    IF @ItemName IS NOT NULL THEN  --get only headers that also define the item
      IF @SiteNo IS NULL THEN
        SELECT TOP 1 H.Config_Header_No INTO @CfgHeaderNo
        FROM "DBA".SM_Enterprise_Config_Header H
        INNER JOIN "DBA".SM_Enterprise_Config_Item I ON (I.Config_Header_No = H.Config_Header_No)
        WHERE (Header_Name = @HeaderName)
        AND (Item_Name = @ItemName)
        AND (Site_No IS NULL);
      ELSEIF @SiteWorkstationNo IS NULL THEN
        SELECT TOP 1 H.Config_Header_No INTO @CfgHeaderNo
        FROM "DBA".SM_Enterprise_Config_Header H
        INNER JOIN "DBA".SM_Enterprise_Config_Item I ON (I.Config_Header_No = H.Config_Header_No)
        WHERE (Header_Name = @HeaderName)
        AND (Item_Name = @ItemName)
        AND (Site_No = @SiteNo)
        AND (Site_Workstation_No IS NULL);
      ELSE
        SELECT TOP 1 H.Config_Header_No INTO @CfgHeaderNo
        FROM "DBA".SM_Enterprise_Config_Header H
        INNER JOIN "DBA".SM_Enterprise_Config_Item I ON (I.Config_Header_No = H.Config_Header_No)
        WHERE (Header_Name = @HeaderName)
        AND (Item_Name = @ItemName)
        AND (Site_No = @SiteNo)
        AND (Site_Workstation_No = @SiteWorkstationNo);
      END IF;
    ELSE  --don't take the item into account
      IF @SiteNo IS NULL THEN
        SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
        FROM "DBA".SM_Enterprise_Config_Header
        WHERE (Header_Name = @HeaderName)
        AND (Site_No IS NULL);
      ELSEIF @SiteWorkstationNo IS NULL THEN
        SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
        FROM "DBA".SM_Enterprise_Config_Header
        WHERE (Header_Name = @HeaderName)
        AND (Site_No = @SiteNo)
        AND (Site_Workstation_No IS NULL);
      ELSE
        SELECT TOP 1 Config_Header_No INTO @CfgHeaderNo
        FROM "DBA".SM_Enterprise_Config_Header
        WHERE (Header_Name = @HeaderName)
        AND (Site_No = @SiteNo)
        AND (Site_Workstation_No = @SiteWorkstationNo);
      END IF;
    END IF;
  END IF;

  RETURN @CfgHeaderNo;
END;

--Update version control
UPDATE "DBA".SM_Version_Control
SET Version_No = 2
WHERE Entity_Name = 'SM_EntCfgGetHeaderNo'
AND Database_ID = (SELECT db_property('GlobalDbID'));

END IF;

IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgSetItemValue'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 2) THEN

ALTER PROCEDURE "DBA".SM_EntCfgSetItemValue(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128),
  IN @ItemName VARCHAR(128), IN @ItemValue VARCHAR(256), IN @ObjectClass INT, IN @LocalSiteNo Entity_No)
BEGIN
  /**********************************************************************************************/
  /* Set Item_Value column value for the specified Header and Item Name. If the specified       */
  /*  Item does not exist, it is created.                                                       */
  /**********************************************************************************************/
  DECLARE @HeaderNo Large_Entity_No;
  DECLARE @ItemNo Large_Entity_No;

  --@HeaderName and @ItemName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;
  IF @ItemName IS NULL THEN
    RAISERROR 30000 'Item Name must be specified';
    RETURN;
  END IF;

  --NOTE: A header must specifically exist for the specified Site and/or Workstation and does not need to include the item
  SET @HeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, NULL, @SiteNo, @SiteWorkstationNo, 'Y');
  IF @HeaderNo IS NULL THEN
    --Local Site No required to create config header
    IF @LocalSiteNo IS NULL THEN
      RAISERROR 30000 'Local Site No must be specified';
      RETURN;
    END IF;

    CALL "DBA".SM_EntCfgCreateHeaderRec(@HeaderName, @SiteNo, @SiteWorkstationNo, @ObjectClass, @LocalSiteNo, @HeaderNo);
    CALL "DBA".SM_EntCfgCreateItemRec(@HeaderNo, @ItemName, @ItemValue, @ItemNo);
  ELSE
    SET @ItemNo = "DBA".SM_EntCfgGetItemNo(@HeaderNo, @ItemName);
    IF @ItemNo IS NULL THEN
      CALL "DBA".SM_EntCfgCreateItemRec(@HeaderNo, @ItemName, @ItemValue, @ItemNo);
    ELSE
      UPDATE "DBA".SM_Enterprise_Config_Item
      SET Item_Value = @ItemValue
      WHERE Config_Header_No = @HeaderNo
      AND Config_Item_No = @ItemNo
    END IF;
  END IF;
END;

--Update version control
UPDATE "DBA".SM_Version_Control
SET Version_No = 2
WHERE Entity_Name = 'SM_EntCfgSetItemValue'
AND Database_ID = (SELECT db_property('GlobalDbID'));

END IF;

IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgDeleteItemGroup'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 2) THEN

ALTER PROCEDURE "DBA".SM_EntCfgDeleteItemGroup(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128))
BEGIN
  /**********************************************************************************************/
  /* Delete the Config Header record corresponding to the specified @HeaderName.                */
  /*                                                                                            */
  /* If no item exists for the specified site and workstation but an item exists for the site   */
  /*  only (i.e. workstation is NULL), that record is deleted.                                  */
  /*                                                                                            */
  /* If no item exists for the specified site or workstation but a header exists where both are */
  /*  NULL, then that record is deleted.                                                        */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName must be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;

  --Get the header no
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, NULL, @SiteNo, @SiteWorkstationNo, 'N');

  --Delete it (don't care if exists or not as the query will still work)
  DELETE "DBA".SM_Enterprise_Config_Header
  WHERE Config_Header_No = @CfgHeaderNo;
END;

--Update version control
UPDATE "DBA".SM_Version_Control
SET Version_No = 2
WHERE Entity_Name = 'SM_EntCfgDeleteItemGroup'
AND Database_ID = (SELECT db_property('GlobalDbID'));

END IF;

IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgGetItemValue'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 2) THEN

ALTER FUNCTION "DBA".SM_EntCfgGetItemValue(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128),
  IN @ItemName VARCHAR(128))
RETURNS VARCHAR(128)
BEGIN
  /**********************************************************************************************/
  /* Return Item_Value column value for the specified Header and Item Name. If the specified    */
  /*  Item does not exist, NULL is returned.                                                    */
  /*                                                                                            */
  /* If no Header exists for the specified site and workstation but a Header exists for the site*/
  /*  only (i.e. workstation is NULL), the value for that Header is returned.                   */
  /*                                                                                            */
  /* If no Header exists for the specified site or workstation but a Header exists where both   */
  /*  are NULL, then the value for that Header is returned.                                     */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;
  DECLARE @ItemVal VARCHAR(128);

  --@HeaderName and @ItemName MUST be specified as a minimum
  IF @HeaderName IS NULL THEN
    RETURN NULL;
  END IF;
  IF @ItemName IS NULL THEN
    RETURN NULL;
  END IF;

  --Get the header no - defined site / workstation takes precedence over NULL values. Header must include the item
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, @ItemName, @SiteNo, @SiteWorkstationNo, 'N');

  SELECT Item_Value INTO @ItemVal
  FROM "DBA".SM_Enterprise_Config_Item
  WHERE (Config_Header_No = ISNULL(@CfgHeaderNo, -1))
  AND (Item_Name = @ItemName);

  RETURN @ItemVal;
END;

--Update version control
UPDATE "DBA".SM_Version_Control
SET Version_No = 2
WHERE Entity_Name = 'SM_EntCfgGetItemValue'
AND Database_ID = (SELECT db_property('GlobalDbID'));

END IF;

IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_EntCfgItemValues'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 2) THEN

ALTER PROCEDURE "DBA".SM_EntCfgItemValues(IN @SiteNo Entity_No, IN @SiteWorkstationNo Entity_No, IN @HeaderName VARCHAR(128))
BEGIN
  /**********************************************************************************************/
  /* Return Item_Name and Item_Value columns for the specified Header Name.                     */
  /*                                                                                            */
  /* If no Header exists for the specified site and workstation but a Header exists for the site*/
  /*  only (i.e. workstation is NULL), values for that Header are returned.                     */
  /*                                                                                            */
  /* If no Header exists for the specified site or workstation but a Header exists where both   */
  /*  are NULL, then values for that Header are returned.                                       */
  /*                                                                                            */
  /* If the specified Header Name does not exist, an empty data set is returned.                */
  /*                                                                                            */
  /* NOTE: NULL can be supplied for one or both of @SiteNo and @SiteWorkstationNo. If @SiteNo   */
  /*  is NULL, any value supplied for @SiteWorkstationNo is ignored.                            */
  /**********************************************************************************************/
  DECLARE @CfgHeaderNo Large_Entity_No;

  --@HeaderName must be specified as a minimum
  IF @HeaderName IS NULL THEN
    RAISERROR 30000 'Header Name must be specified';
    RETURN;
  END IF;

  --Get the header no
  SET @CfgHeaderNo = "DBA".SM_EntCfgGetHeaderNo(@HeaderName, NULL, @SiteNo, @SiteWorkstationNo, 'N');

  --Return the results
  SELECT Item_Name, Item_Value
  FROM "DBA".SM_Enterprise_Config_Item
  WHERE Config_Header_No = ISNULL(@CfgHeaderNo, -1)
END;

--Update version control
UPDATE "DBA".SM_Version_Control
SET Version_No = 2
WHERE Entity_Name = 'SM_EntCfgItemValues'
AND Database_ID = (SELECT db_property('GlobalDbID'));

END IF;


/*********************************************************************/
/* 13/12/2004 - Changes to SM_Action_Trigger table.                  */
/*  1. Remove Action_No from the Primary Key                         */
/*  2. Remove Site_No from the Primary Key and change to allow NULLs */
/*  3. Add Action_Trigger_No as an Identity field and make it the    */
/*     Primary Key                                                   */
/*  4. Add Site_Workstation_No field with foreign key                */
/*  5. Remove Use_Schedule, Input_Event_ID and Input_Event_State fields */
/*  6. Change the delete action on the SM_Site foreign key to CASCADE*/
/*  7. Add a unique index on combination of Action_No, Site_No       */
/*     and Site_Workstation_No                                       */
/*  NOTE: To add a column with a non-NULL constraint the table must  */
/*   be empty. As this table is not currently used by any application*/
/*   a TRUNCATE TABLE is issued to make sure it is empty             */
/*********************************************************************/
IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Action_Trigger'
  AND column_name = 'Action_Trigger_No') THEN

TRUNCATE TABLE "DBA".SM_Action_Trigger;  --Remove any records that may be present (shouldn't be any)

ALTER TABLE "DBA".SM_Action_Trigger
DELETE PRIMARY KEY;

IF EXISTS(
  SELECT 1
  FROM sys.sysforeignkey
  WHERE role='FK_SM_ACTIO_TRIG_REF_SM_SITE') THEN

ALTER TABLE "DBA".SM_Action_Trigger
DELETE FOREIGN KEY FK_SM_ACTIO_TRIG_REF_SM_SITE;  --Note F/K must be deleted before column can be changed

END IF;

ALTER TABLE "DBA".SM_Action_Trigger
MODIFY Site_No Entity_No NULL;  --Change to Entity_No (may be Site_No in some locations)

ALTER TABLE "DBA".SM_Action_Trigger
MODIFY Site_No NULL;  --Allow NULL values - must be done in a separate step to modifying the column domain

ALTER TABLE "DBA".SM_Action_Trigger
ADD Action_Trigger_No Identity_No NOT NULL PRIMARY KEY;

ALTER TABLE "DBA".SM_Action_Trigger
ADD Site_Workstation_No Entity_No NULL;

ALTER TABLE "DBA".SM_Action_Trigger
   add foreign key FK_SM_ACT_TRIG_REF_SM_SITEWK (Site_Workstation_No)
      references "DBA".SM_Site_Workstation (Site_Workstation_No)
      on update restrict
      on delete restrict;

ALTER TABLE "DBA".SM_Action_Trigger
DROP Input_Event_ID;

ALTER TABLE "DBA".SM_Action_Trigger
DROP Input_Event_State;

ALTER TABLE "DBA".SM_Action_Trigger
DROP Use_Schedule;

ALTER TABLE "DBA".SM_Action_Trigger
   add foreign key FK_SM_ACTIO_TRIG_REF_SM_SITE (Site_No)
      references "DBA".SM_Site (Site_No)
      on update cascade
      on delete cascade;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysindex
  WHERE index_name = 'IDX_ActionTrigSite') THEN

CREATE UNIQUE INDEX IDX_ActionTrigSite
ON "DBA".SM_Action_Trigger (Action_No ASC, Site_No ASC, Site_Workstation_No ASC);

END IF;


/*********************************************************************/
/* 13/12/2004 - Changes to table SM_Action_Schedules.                */
/*  1. Add Site_Workstation_No field with foreign key                */
/*  2. Remove non-NULL constraint from Site_No field                 */
/*  3. Change the delete action on the SM_Site foreign key to CASCADE*/
/*********************************************************************/
IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Action_Schedules'
  AND column_name = 'Site_Workstation_No') THEN

ALTER TABLE "DBA".SM_Action_Schedules
ADD Site_Workstation_No Entity_No NULL;

ALTER TABLE "DBA".SM_Action_Schedules
   add constraint FK_SM_ACT_SCHED_REF_SM_SITEWK foreign key (Site_Workstation_No)
      references "DBA".SM_Site_Workstation (Site_Workstation_No)
      on update restrict
      on delete restrict;

IF EXISTS(
  SELECT 1
  FROM sys.sysforeignkey
  WHERE role='FK_SM_ACTIO_SCHED_REF_SM_SITE') THEN

ALTER TABLE "DBA".SM_Action_Schedules
DELETE FOREIGN KEY FK_SM_ACTIO_SCHED_REF_SM_SITE;  --Note F/K must be deleted before column can be changed

END IF;

ALTER TABLE "DBA".SM_Action_Schedules
MODIFY Site_No Entity_No NULL;  --Change to Entity_No (may be Site_No in some locations)

ALTER TABLE "DBA".SM_Action_Schedules
MODIFY Site_No NULL;  --Allow NULL values - must be done in a separate step to modifying the column domain

ALTER TABLE "DBA".SM_Action_Schedules
   add foreign key FK_SM_ACTIO_SCHED_REF_SM_SITE (Site_No)
      references "DBA".SM_Site (Site_No)
      on update cascade
      on delete cascade;

END IF;


/*********************************************************************/
/* 13/12/2004 - Add index on the Action_No, Site_No and Site_Workstation_No */
/*  fields of SM_Action_Schedules                                    */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysindex
  WHERE index_name = 'IDX_ActionSite') THEN

CREATE INDEX IDX_ActionSite 
ON "DBA".SM_Action_Schedules (Action_No ASC, Site_No ASC, Site_Workstation_No ASC);

END IF;


/*********************************************************************/
/* 20/12/2004 - Add Last_Refresh column to SM_User_Connection        */
/* Drop Process_ID from SM_User_Connection                           */
/*********************************************************************/
IF EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_User_Connection')
AND NOT EXISTS(
  SELECT 1 FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_User_Connection'
  AND column_name = 'Last_Refresh') THEN

ALTER TABLE "DBA".SM_User_Connection
ADD Last_Refresh DATETIME DEFAULT CURRENT TIMESTAMP;

END IF;

IF EXISTS(
  SELECT 1 FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_User_Connection'
  AND column_name = 'Process_ID') THEN

ALTER TABLE "DBA".SM_User_Connection
DROP Process_ID;

END IF;

IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_UC_AssignConnectionUserCode'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 2) THEN

ALTER PROCEDURE "DBA".SM_UC_AssignConnectionUserCode(IN @UserID Operator_Code, IN @ProcessID INTEGER)
BEGIN
  DECLARE @ConnID UNSIGNED BIGINT;

  /* Get the connection ID of this connection */
  SET @ConnID = (SELECT connection_property('Number'));

  /* Create table if doesn't exist */
  IF NOT EXISTS(
    SELECT 1 FROM systable
    WHERE table_name = 'SM_User_Connection') THEN

    CREATE TABLE "DBA".SM_User_Connection(
      Connection_ID  UNSIGNED BIGINT,
      User_ID Operator_Code,
      Connection_Time DATETIME DEFAULT CURRENT TIMESTAMP,
      Last_Refresh DATETIME DEFAULT CURRENT TIMESTAMP,
      PRIMARY KEY (Connection_ID)
    );

  END IF;

  /* Insert / update the record */
  IF NOT EXISTS(
    SELECT 1 FROM "DBA".SM_User_Connection
    WHERE Connection_ID = @ConnID) THEN

    INSERT "DBA".SM_User_Connection
    (Connection_ID, User_ID, Connection_Time, Last_Refresh)
    VALUES(@ConnID, @UserID, GetDate(), GetDate());

  ELSE

    UPDATE "DBA".SM_User_Connection
    SET User_ID = @UserID, Last_Refresh = GetDate()
    WHERE Connection_ID = @ConnID;

  END IF;
END;

--Update version control
UPDATE "DBA".SM_Version_Control
SET Version_No = 2
WHERE Entity_Name = 'SM_UC_AssignConnectionUserCode'
AND Database_ID = (SELECT db_property('GlobalDBId'));

END IF;


/*********************************************************************/
/* 20/12/2004 - Add new procedure SM_UC_ValidateUserConnections      */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_UC_ValidateUserConnections') THEN

CREATE PROCEDURE "DBA".SM_UC_ValidateUserConnections()
BEGIN
  DECLARE connid INTEGER;
  DECLARE LOCAL TEMPORARY TABLE t_conn_info(
    Number INTEGER NULL,
    Name VARCHAR(255) NULL,
    Userid VARCHAR(255) NULL)
    ON COMMIT PRESERVE ROWS;

  SET connid = next_connection(connid, NULL);

  lbl: LOOP
    IF connid IS NULL THEN
      LEAVE lbl
    END IF;

    INSERT INTO t_conn_info VALUES(
      connid,
      connection_property('Name', connid),
      connection_property('Userid', connid));
    SET connid = next_connection(connid, NULL)
  END LOOP lbl;

  --Delete all records that have not refreshed within the last 60 minutes or no longer exist as connections
  DELETE "DBA".SM_User_Connection
  WHERE (DATEDIFF(minute, Last_Refresh, GETDATE()) > 60)
  OR (Connection_ID NOT IN
    (SELECT Number FROM t_conn_info));
END;

END IF;

/****************************************************************************************/
/* 24/03/2005 - Addition fields to support version 2 list grids                         */
/****************************************************************************************/
IF NOT EXISTS(
  select column_name from systable
    join syscolumn on systable.table_id = syscolumn.table_id
   where table_name  = 'SM_List_Form_View'
     and column_name = 'V2_Allow_Grouping') then

  alter table "DBA".SM_List_Form_View add V2_Allow_Grouping  Boolean NULL DEFAULT 'Y';
  alter table "DBA".SM_List_Form_View add V2_Allow_Filtering Boolean NULL DEFAULT 'Y';
  alter table "DBA".SM_List_Form_View add V2_Allow_Moving    Boolean NULL DEFAULT 'Y';
  alter table "DBA".SM_List_Form_View add V2_Allow_Sorting   Boolean NULL DEFAULT 'Y';
  alter table "DBA".SM_List_Form_View add V2_Show_Grid       integer default 0;
  alter table "DBA".SM_List_Form_View add V2_Font_Size       char(1) default 'D';

END IF;

/****************************************************************************************/
/* 18/04/2005 - Addition fields to support version 2 list grids                         */
/****************************************************************************************/
IF NOT EXISTS(
  select column_name from systable
    join syscolumn on systable.table_id = syscolumn.table_id
   where table_name  = 'SM_List_Form_View'
     and column_name = 'V2_Enable_Chart') then

  alter table "DBA".SM_List_Form_View add V2_Enable_Chart    Boolean NULL Default 'N';
  alter table "DBA".SM_List_Form_View add V2_XAxis_FieldName GEN_SHORT_DESCRIPTION NULL;
  alter table "DBA".SM_List_Form_View add V2_YAxis_FieldName GEN_SHORT_DESCRIPTION NULL;
  alter table "DBA".SM_List_Form_View add V2_Label_FieldName GEN_SHORT_DESCRIPTION NULL;
  alter table "DBA".SM_List_Form_View add V2_Enable_3D       Boolean NULL Default 'N';
  alter table "DBA".SM_List_Form_View add V2_Chart_Type      integer default 0;

END IF;


/****************************************************************************************/
/* 22/04/2005 - Add SM_GetLocalSite and functions to support it                         */
/****************************************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_GetAppName') THEN

CREATE FUNCTION "DBA".SM_GetAppName()
RETURNS VARCHAR(100)
BEGIN
  DECLARE @ConnProp VARCHAR(300);
  DECLARE @St VARCHAR(300);
  DECLARE @KeyName VARCHAR(20);
  DECLARE @KeyValue VARCHAR(100);
  DECLARE @AppName VARCHAR(100);

  --Get the Host Name as Exe name without path info or .exe extension
  SET @KeyName = 'EXE';
  SET @ConnProp=connection_property('appinfo');
  IF CHARINDEX(@KeyName || '=', @ConnProp) > 0 THEN
    SET @St=RIGHT(@ConnProp, (LENGTH(@ConnProp) - CHARINDEX(@KeyName || '=', @ConnProp) + 1));
    SET @St=LEFT(@St, CHARINDEX(';', @St)-1);
    SET @KeyValue=RIGHT(@St, LENGTH(@St) - (CHARINDEX('=', @St) + 1) + 1);
  ELSE
    SET @KeyValue = NULL;
  END IF;
  SET @AppName = @KeyValue;
  IF CHARINDEX('\', @AppName) > 0 THEN
    SET @AppName = SUBSTR(@AppName, (LOCATE(@AppName, '\', -1) + 1));
  END IF;
  IF CHARINDEX('.EXE', @AppName) > 0 THEN
    SET @AppName = LEFT(@AppName, (CHARINDEX('.EXE', @AppName) - 1));
  END IF;

  RETURN @AppName;

END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_GetHostName') THEN

CREATE FUNCTION "DBA".SM_GetHostName()
RETURNS VARCHAR(100)
BEGIN
  DECLARE @ConnProp VARCHAR(300);
  DECLARE @St VARCHAR(300);
  DECLARE @KeyName VARCHAR(20);
  DECLARE @KeyValue VARCHAR(100);
  DECLARE @HostName VARCHAR(100);

  --Get the Host Name
  SET @KeyName = 'HOST';
  SET @ConnProp=connection_property('appinfo');
  IF CHARINDEX(@KeyName || '=', @ConnProp) > 0 THEN
    SET @St=RIGHT(@ConnProp, (LENGTH(@ConnProp) - CHARINDEX(@KeyName || '=', @ConnProp) + 1));
    SET @St=LEFT(@St, CHARINDEX(';', @St)-1);
    SET @KeyValue=RIGHT(@St, LENGTH(@St) - (CHARINDEX('=', @St) + 1) + 1);
  ELSE
    SET @KeyValue = NULL;
  END IF;
  SET @HostName = @KeyValue;

  RETURN @HostName;

END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_GetLocalSiteNo') THEN

CREATE FUNCTION "DBA".SM_GetLocalSiteNo()
RETURNS Entity_No
BEGIN
  DECLARE @SiteNo Entity_No;
  DECLARE @HostName VARCHAR(100);
  DECLARE @AppName VARCHAR(100);

  IF EXISTS(
    SELECT 1 FROM systable
    WHERE table_name = 'SM_Local_Site'
    AND table_type = 'BASE') THEN
    
    --Get the Host Name and App Name
    SELECT "DBA".SM_GetHostName(), "DBA".SM_GetAppName() INTO @HostName, @AppName;

    IF EXISTS(
      SELECT 1 FROM "DBA".SM_Local_Site
      WHERE Host_Name = @HostName
      AND App_Name = @AppName) THEN

      --Get the specific one
      SELECT TOP 1 Site_No INTO @SiteNo
      FROM "DBA".SM_Local_Site
      WHERE Host_Name = @HostName
      AND App_Name = @AppName;

    ELSE  --No specific entry, get the last one used

      SELECT TOP 1 Site_No INTO @SiteNo
      FROM "DBA".SM_Local_Site
      WHERE Host_Name IS NULL
      AND App_Name IS NULL;
      
    END IF;

  ELSE
    SET @SiteNo = NULL;  --can't determine
  END IF;

  RETURN @SiteNo;

END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_SetDefaultLocalSite') THEN

CREATE PROCEDURE "DBA".SM_SetDefaultLocalSite(@SiteNo Entity_No)
BEGIN
  --Create table if doesn't exist
  IF NOT EXISTS(
    SELECT 1 FROM systable
    WHERE table_name = 'SM_Local_Site'
    AND table_type = 'BASE') THEN
    
    CREATE TABLE "DBA".SM_Local_Site(
      Rec_ID     Identity_No NOT NULL,
      Host_Name  VARCHAR(80),
      App_Name   VARCHAR(80),
      Site_No    Entity_No NOT NULL,
      CONSTRAINT PK_SM_LOCAL_SITE PRIMARY KEY(Rec_ID)
    );

    CREATE UNIQUE INDEX IDX_HostApp ON "DBA".SM_Local_Site
    (Host_Name ASC, App_Name ASC);
    
  END IF;

  --Add/update record for NULL Host_Name and App_Name
  IF EXISTS(
    SELECT 1 FROM "DBA".SM_Local_Site
    WHERE Host_Name IS NULL
    AND App_Name IS NULL) THEN

    UPDATE "DBA".SM_Local_Site
    SET Site_No = @SiteNo
    WHERE Host_Name IS NULL
    AND App_Name IS NULL;

  ELSE
  
    INSERT "DBA".SM_Local_Site
    (Site_No)
    VALUES(@SiteNo);
    
  END IF;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_SetLocalSite') THEN

CREATE PROCEDURE "DBA".SM_SetLocalSite(@HostName VARCHAR(80), @AppName VARCHAR(80), @SiteNo Entity_No)
BEGIN
  --Create table if doesn't exist
  IF NOT EXISTS(
    SELECT 1 FROM systable
    WHERE table_name = 'SM_Local_Site'
    AND table_type = 'BASE') THEN
    
    CREATE TABLE "DBA".SM_Local_Site(
      Rec_ID     Identity_No NOT NULL,
      Host_Name  VARCHAR(80),
      App_Name   VARCHAR(80),
      Site_No    Entity_No NOT NULL,
      CONSTRAINT PK_SM_LOCAL_SITE PRIMARY KEY(Rec_ID)
    );

    CREATE UNIQUE INDEX IDX_HostApp ON "DBA".SM_Local_Site
    (Host_Name ASC, App_Name ASC);
    
  END IF;

  --Add/update record for specified Host_Name and App_Name, and NULL Host_Name and App_Name if doesn't already exist
  IF EXISTS(
    SELECT 1 FROM "DBA".SM_Local_Site
    WHERE Host_Name = @HostName
    AND App_Name = @AppName) THEN

    UPDATE "DBA".SM_Local_Site
    SET Site_No = @SiteNo
    WHERE Host_Name = @HostName
    AND App_Name = @AppName;

  ELSE
  
    INSERT "DBA".SM_Local_Site
    (Host_Name, App_Name, Site_No)
    VALUES(RTRIM(@HostName), RTRIM(@AppName), @SiteNo);
    
  END IF;

  IF NOT EXISTS(
    SELECT 1 FROM "DBA".SM_Local_Site
    WHERE Host_Name IS NULL
    AND App_Name IS NULL) THEN

    INSERT "DBA".SM_Local_Site
    (Site_No)
    VALUES(@SiteNo);
    
  END IF;
END;

END IF;


/*********************************************************************************/
/* 17/05/2005 - Add authorisation list procedures                                */
/*********************************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_GetAuthorisedItemsScript') THEN

CREATE FUNCTION "DBA".SM_GetAuthorisedItemsScript(
  IN @AuthorisingClassID Authorisation_Class_ID,
  IN @AuthorisedClassID Authorisation_Class_ID,
  IN @ProcessID Authorisation_Process,
  IN @AuthorisingKeyValue Authorisation_Class_Key,
  IN @AuthorisedKeyFieldName VARCHAR(128),
  IN @AuthorisedItemsListQuery VARCHAR(8000))
RETURNS VARCHAR(8000)
NOT DETERMINISTIC
BEGIN
  DECLARE @AuthCfgNo Entity_No;
  DECLARE @IsAuth BOOLEAN;
  DECLARE @ItemsReqd BOOLEAN;
  DECLARE @ItemsExist BOOLEAN;
  DECLARE @SQLStr VARCHAR(8000);

  SELECT Authorisation_Config_No, Is_Authorising_Relationship, Items_Must_Exist, Items_Exist
  INTO @AuthCfgNo, @IsAuth, @ItemsReqd, @ItemsExist
  FROM "DBA".SM_Authorisation_Config
  WHERE (Authorising_Class_ID = @AuthorisingClassID)
  AND (Authorised_Class_ID = @AuthorisedClassID)
  AND (Authorised_Process = @ProcessID)
  AND (Is_Enabled = 'Y');

  /* If Items Exist, must check further to see whether items actually exist for */
  /* the specific authorising key value */  
  IF (@ItemsExist = 'Y')
  AND NOT EXISTS(
    SELECT 1 FROM "DBA".SM_Authorisation_Item 
    WHERE Authorisation_Config_No = @AuthCfgNo
    AND Authorising_Class_Key = @AuthorisingKeyValue) THEN
    SET @ItemsExist = 'N';
  END IF;

  SET @SQLStr = @AuthorisedItemsListQuery;

  IF @AuthCfgNo IS NOT NULL THEN

    IF @ItemsExist = 'Y' THEN

      IF @IsAuth = 'Y' THEN
        IF CHARINDEX('WHERE', @AuthorisedItemsListQuery) > 0 THEN
          SET @SQLStr = @SQLStr || ' AND (' || @AuthorisedKeyFieldName || ' IN ';
        ELSE
          SET @SQLStr = @SQLStr || ' WHERE (' || @AuthorisedKeyFieldName || ' IN ';
        END IF;
      ELSE
        /* Return all records EXCEPT those specified in auth items table */
        IF CHARINDEX('WHERE', @AuthorisedItemsListQuery) > 0 THEN
          SET @SQLStr = @SQLStr || ' AND (' || @AuthorisedKeyFieldName || ' NOT IN ';
        ELSE
          SET @SQLStr = @SQLStr || ' WHERE (' || @AuthorisedKeyFieldName || ' NOT IN ';
        END IF;
      END IF;

      SET @SQLStr = @SQLStr ||
        '(SELECT Authorised_Class_Key FROM "DBA".SM_Authorisation_Item WHERE Authorisation_Config_No = ' ||
        RTRIM(CAST(@AuthCfgNo AS CHAR)) || ' AND Authorising_Class_Key = ''' || @AuthorisingKeyValue || '''))';

    ELSE

      IF (@IsAuth = 'Y') AND (@ItemsReqd = 'Y') THEN
        /* NOTHING is authorised - return NO records */
        /* Add WHERE / AND condition that always returns FALSE */
        IF CHARINDEX('WHERE', @AuthorisedItemsListQuery) > 0 THEN
          SET @SQLStr = @SQLStr || ' AND (0 = 1)';
        ELSE
          SET @SQLStr = @SQLStr || ' WHERE (0 = 1)';
        END IF;
      END IF;
      /* Otherwise return ALL records (everything implicitly authorised) */

    END IF;

  END IF;
  /* Otherwise return ALL records (everything implicitly authorised) */

  /* Return the query */
  RETURN @SQLStr;

END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_GetAuthorisingItemsScript') THEN

CREATE FUNCTION "DBA".SM_GetAuthorisingItemsScript(
  IN @AuthorisingClassID Authorisation_Class_ID,
  IN @AuthorisedClassID Authorisation_Class_ID,
  IN @ProcessID Authorisation_Process,
  IN @AuthorisedKeyValue Authorisation_Class_Key,
  IN @AuthorisingKeyFieldName VARCHAR(128),
  IN @AuthorisingItemsListQuery VARCHAR(8000))
RETURNS VARCHAR(8000)
NOT DETERMINISTIC
BEGIN
  DECLARE @AuthCfgNo Entity_No;
  DECLARE @IsAuth BOOLEAN;
  DECLARE @ItemsReqd BOOLEAN;
  DECLARE @ItemsExist BOOLEAN;
  DECLARE @SQLStr VARCHAR(8000);
  DECLARE @AuthorisingTableName VARCHAR(128);

  SELECT Authorisation_Config_No, Is_Authorising_Relationship, Items_Must_Exist, Items_Exist
  INTO @AuthCfgNo, @IsAuth, @ItemsReqd, @ItemsExist
  FROM "DBA".SM_Authorisation_Config
  WHERE (Authorising_Class_ID = @AuthorisingClassID)
  AND (Authorised_Class_ID = @AuthorisedClassID)
  AND (Authorised_Process = @ProcessID)
  AND (Is_Enabled = 'Y');

  SELECT Table_Name INTO @AuthorisingTableName
  FROM "DBA".SM_Object_Class
  WHERE Object_Class = @AuthorisingClassID;

  SET @SQLStr = @AuthorisingItemsListQuery;

  IF @AuthCfgNo IS NOT NULL THEN

    IF @ItemsExist = 'Y' THEN

      IF @IsAuth = 'Y' THEN

          IF CHARINDEX('WHERE', @AuthorisingItemsListQuery) > 0 THEN
            SET @SQLStr = @SQLStr || ' AND (' || @AuthorisingKeyFieldName || ' IN ';
          ELSE
            SET @SQLStr = @SQLStr || ' WHERE (' || @AuthorisingKeyFieldName || ' IN ';
          END IF;

      ELSE

          /* Return all records EXCEPT those specified in auth items table */
          IF CHARINDEX('WHERE', @AuthorisingItemsListQuery) > 0 THEN
            SET @SQLStr = @SQLStr || ' AND (' || @AuthorisingKeyFieldName || ' NOT IN ';
          ELSE
            SET @SQLStr = @SQLStr || ' WHERE (' || @AuthorisingKeyFieldName || ' NOT IN ';
          END IF;

      END IF;

      /* If Items are required (and Is_Auth.. = 'Y'), use an INNER join to get only the entities explicitly authorising */
      /* If Items are not required, use a LEFT OUTER join to get the entities explicitly authorising nothing as well */
      SET @SQLStr = @SQLStr || '(SELECT DISTINCT ' || @AuthorisingKeyFieldName || 
        ' FROM "DBA".' || @AuthorisingTableName || ' T ';
      IF (@IsAuth = 'Y') AND (@ItemsReqd = 'Y') THEN
        SET @SQLStr = @SQLStr || 'INNER JOIN "DBA".SM_Authorisation_Item AI ';
      ELSE
        SET @SQLStr = @SQLStr || 'LEFT OUTER JOIN "DBA".SM_Authorisation_Item AI ';
      END IF;
      SET @SQLStr = @SQLStr ||
        'ON ((AI.Authorising_Class_Key = RTRIM(CAST(T.' || @AuthorisingKeyFieldName || ' AS CHAR))) ' || 
          'AND (AI.Authorisation_Config_No = ' || RTRIM(CAST(@AuthCfgNo AS CHAR)) || ')) ' ||
        'WHERE (AI.Authorised_Class_Key = ''' || @AuthorisedKeyValue || ''') ' ||
        'OR (AI.Authorised_Class_Key IS NULL)))';

    ELSE

      IF (@IsAuth = 'Y') AND (@ItemsReqd = 'Y') THEN

        /* NOTHING is authorised - return NO records */
        /* Add WHERE / AND condition that always returns FALSE */
        IF CHARINDEX('WHERE', @AuthorisingItemsListQuery) > 0 THEN
          SET @SQLStr = @SQLStr || ' AND (0 = 1)';
        ELSE
          SET @SQLStr = @SQLStr || ' WHERE (0 = 1)';
        END IF;

      END IF;
      /* Otherwise return ALL records (everything implicitly authorised) */

    END IF;

  END IF;
  /* Otherwise return ALL records (everything implicitly authorised) */

  /* Return the query */
  RETURN @SQLStr;

END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_AuthLookupList') THEN

CREATE PROCEDURE "DBA".SM_AuthLookupList(IN @LookupClassID Authorisation_Class_ID, IN @ProcessID Authorisation_Process,
  IN @LookupListQuery GEN_HUGE_DESCRIPTION, IN @ResultTableName VARCHAR(128))
BEGIN
  /*******************************************************************************************/
  /* This procedure will generate a lookup list of @LookupClassID objects that can be used   */
  /*  for process @ProcessID, given the current values of other authorisation classes in     */
  /*  effect. The other classes and their values are passed in a temporary table named       */
  /*  #AuthedItems, which is assumed to exist even if no rows are in it. The schema of       */
  /*  #AuthedItems should include at least two fields, Authed_Class_ID and Authed_Class_Key. */
  /*                                                                                         */
  /* Due to limitations presently under Sybase, the entire list is not generated by this     */
  /*  procedure. Instead, the valid lookup class key values are inserted into the specified  */
  /*  @ResultTableName, which can then be used by the caller to filter an arbitrary script.  */
  /*******************************************************************************************/

  DECLARE @LookupKeyFieldName VARCHAR(128);
  DECLARE @LookupTableName VARCHAR(128);
  DECLARE @AuthedClassID Authorisation_Class_ID;
  DECLARE @AuthedClassKey Authorisation_Class_Key;
  DECLARE @SQLStr VARCHAR(8000);
  DECLARE Err_NotFound EXCEPTION FOR SQLSTATE VALUE '02000';
  DECLARE Auth_Item_Cursor NO SCROLL CURSOR FOR
    SELECT Authed_Class_ID, Authed_Class_Key
    FROM #AuthedItems
    WHERE Authed_Class_ID <> @LookupClassID
  FOR READ ONLY;

  --Get the key field and table names of the lookup class from SM_Object_Class
  SELECT TOP 1 Key_Field, Table_Name
  INTO @LookupKeyFieldName, @LookupTableName
  FROM "DBA".SM_Object_Class
  WHERE Object_Class = @LookupClassID;

  SET @SQLStr = 'SELECT ' || @LookupKeyFieldName || ' FROM ' || @LookupTableName;

  --Loop over all class / key pairs in #AuthedItems and add filter clauses to @SQLStr
  OPEN Auth_Item_Cursor;

  This_Loop: LOOP
    FETCH NEXT Auth_Item_Cursor INTO @AuthedClassID, @AuthedClassKey;
    IF SQLSTATE = Err_NotFound THEN
      LEAVE This_Loop;
    END IF;

    --Get all @LookupClassID values authorised by the @AuthedClassID value...
    SET @SQLStr = "DBA".SM_GetAuthorisedItemsScript (@AuthedClassID, @LookupClassID, @ProcessID, @AuthedClassKey, @LookupKeyFieldName, @SQLStr);

    --...@LookupClassID values must also authorise the @AuthedClassID value to be valid
    SET @SQLStr = "DBA".SM_GetAuthorisingItemsScript (@LookupClassID, @AuthedClassID, @ProcessID, @AuthedClassKey, @LookupKeyFieldName, @SQLStr);

  END LOOP This_Loop;
  CLOSE Auth_Item_Cursor;

  --Add the results of the SELECT to the temp table
  EXECUTE IMMEDIATE 'INSERT ' || @ResultTableName || '(' || @LookupKeyFieldName || ') ' || @SQLStr;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_ObjectClassForName') THEN

CREATE FUNCTION "DBA".SM_ObjectClassForName(IN @ObjectName VARCHAR(50))
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
  /*********************************************************************/
  /* This function attempts to locate a record in SM_Object_Class with */
  /*  Object_Name equal to @ObjectName, and returns the Object_Class   */
  /*  of that record.                                                  */
  /* If no record is found, or more than one record is found, the      */
  /*  function returns NULL.                                           */
  /*********************************************************************/
  DECLARE @RecCount INTEGER;
  DECLARE @ObjID INTEGER;

  SELECT COUNT(*) INTO @RecCount
  FROM "DBA".SM_Object_Class
  WHERE Object_Name = @ObjectName;

  IF @RecCount = 1 THEN  --return the ID - may be NULL if does not exist
    SELECT Object_Class INTO @ObjID
    FROM "DBA".SM_Object_Class
    WHERE Object_Name = @ObjectName;
  ELSE
    SET @ObjID = NULL;
  END IF;

  RETURN @ObjID;
END;

END IF;


/*********************************************************************************/
/* 19/05/2005 - Add new fields to SM_Object_Class                                */
/*********************************************************************************/
IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'Class_Type') THEN

ALTER TABLE "DBA".SM_Object_Class
ADD Class_Type INTEGER;

END IF;

IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'Class_Group') THEN

ALTER TABLE "DBA".SM_Object_Class
ADD Class_Group INTEGER;

END IF;

IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'Application_Class_Type') THEN

ALTER TABLE "DBA".SM_Object_Class
ADD Application_Class_Type INTEGER;

END IF;

IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'Key_Field_Is_String') THEN

--To add a NOT NULL field the table must be empty so it is added as null-permitting here
ALTER TABLE "DBA".SM_Object_Class
ADD Key_Field_Is_String BOOLEAN NULL DEFAULT 'N';

END IF;

IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'User_Key_Field') THEN

ALTER TABLE "DBA".SM_Object_Class
ADD User_Key_Field VARCHAR(50);

END IF;

IF NOT EXISTS(
  SELECT column_name FROM systable
  JOIN syscolumn ON systable.table_id = syscolumn.table_id
  WHERE table_name = 'SM_Object_Class'
  AND column_name = 'User_Key_Field_Is_String') THEN

--To add a NOT NULL field the table must be empty so it is added as null-permitting here
ALTER TABLE "DBA".SM_Object_Class
ADD User_Key_Field_Is_String BOOLEAN NULL;

END IF;


/*********************************************************************/
/* 18/07/2005 - Modify SM_GetAuthorisingItemsScript to handle        */
/*  Authorising Key Field Names passed in with an alias prefix       */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'SM_GetAuthorisingItemsScript'
  AND Database_ID = (SELECT db_property( 'GlobalDBId' ))) THEN

ALTER FUNCTION "DBA".SM_GetAuthorisingItemsScript(
  IN @AuthorisingClassID Authorisation_Class_ID,
  IN @AuthorisedClassID Authorisation_Class_ID,
  IN @ProcessID Authorisation_Process,
  IN @AuthorisedKeyValue Authorisation_Class_Key,
  IN @AuthorisingKeyFieldName VARCHAR(128),
  IN @AuthorisingItemsListQuery VARCHAR(8000))
RETURNS VARCHAR(8000)
NOT DETERMINISTIC
BEGIN
  DECLARE @AuthCfgNo Entity_No;
  DECLARE @IsAuth BOOLEAN;
  DECLARE @ItemsReqd BOOLEAN;
  DECLARE @ItemsExist BOOLEAN;
  DECLARE @SQLStr VARCHAR(8000);
  DECLARE @AuthorisingTableName VARCHAR(128);
  DECLARE @AuthKeyFieldNoAlias VARCHAR(128);

  SELECT Authorisation_Config_No, Is_Authorising_Relationship, Items_Must_Exist, Items_Exist
  INTO @AuthCfgNo, @IsAuth, @ItemsReqd, @ItemsExist
  FROM "DBA".SM_Authorisation_Config
  WHERE (Authorising_Class_ID = @AuthorisingClassID)
  AND (Authorised_Class_ID = @AuthorisedClassID)
  AND (Authorised_Process = @ProcessID)
  AND (Is_Enabled = 'Y');

  SELECT Table_Name INTO @AuthorisingTableName
  FROM "DBA".SM_Object_Class
  WHERE Object_Class = @AuthorisingClassID;

  SET @SQLStr = @AuthorisingItemsListQuery;

  IF @AuthCfgNo IS NOT NULL THEN

    IF @ItemsExist = 'Y' THEN

      IF @IsAuth = 'Y' THEN

          IF CHARINDEX('WHERE', @AuthorisingItemsListQuery) > 0 THEN
            SET @SQLStr = @SQLStr || ' AND (' || @AuthorisingKeyFieldName || ' IN ';
          ELSE
            SET @SQLStr = @SQLStr || ' WHERE (' || @AuthorisingKeyFieldName || ' IN ';
          END IF;

      ELSE

          /* Return all records EXCEPT those specified in auth items table */
          IF CHARINDEX('WHERE', @AuthorisingItemsListQuery) > 0 THEN
            SET @SQLStr = @SQLStr || ' AND (' || @AuthorisingKeyFieldName || ' NOT IN ';
          ELSE
            SET @SQLStr = @SQLStr || ' WHERE (' || @AuthorisingKeyFieldName || ' NOT IN ';
          END IF;

      END IF;

      /* Get the key field name without an alias */
      IF CHARINDEX('.', @AuthorisingKeyFieldName) > 0 THEN
        SET @AuthKeyFieldNoAlias = RIGHT(@AuthorisingKeyFieldName, LENGTH(@AuthorisingKeyFieldName) - CHARINDEX('.', @AuthorisingKeyFieldName));
      ELSE
        SET @AuthKeyFieldNoAlias = @AuthorisingKeyFieldName;
      END IF;

      /* If Items are required (and Is_Auth.. = 'Y'), use an INNER join to get only the entities explicitly authorising */
      /* If Items are not required, use a LEFT OUTER join to get the entities explicitly authorising nothing as well */
      SET @SQLStr = @SQLStr || '(SELECT DISTINCT ' || @AuthKeyFieldNoAlias || 
        ' FROM "DBA".' || @AuthorisingTableName || ' T ';
      IF (@IsAuth = 'Y') AND (@ItemsReqd = 'Y') THEN
        SET @SQLStr = @SQLStr || 'INNER JOIN "DBA".SM_Authorisation_Item AI ';
      ELSE
        SET @SQLStr = @SQLStr || 'LEFT OUTER JOIN "DBA".SM_Authorisation_Item AI ';
      END IF;
      SET @SQLStr = @SQLStr ||
        'ON ((AI.Authorising_Class_Key = RTRIM(CAST(T.' || @AuthKeyFieldNoAlias || ' AS CHAR))) ' || 
          'AND (AI.Authorisation_Config_No = ' || RTRIM(CAST(@AuthCfgNo AS CHAR)) || ')) ' ||
        'WHERE (AI.Authorised_Class_Key = ''' || @AuthorisedKeyValue || ''') ' ||
        'OR (AI.Authorised_Class_Key IS NULL)))';

    ELSE

      IF (@IsAuth = 'Y') AND (@ItemsReqd = 'Y') THEN

        /* NOTHING is authorised - return NO records */
        /* Add WHERE / AND condition that always returns FALSE */
        IF CHARINDEX('WHERE', @AuthorisingItemsListQuery) > 0 THEN
          SET @SQLStr = @SQLStr || ' AND (0 = 1)';
        ELSE
          SET @SQLStr = @SQLStr || ' WHERE (0 = 1)';
        END IF;

      END IF;
      /* Otherwise return ALL records (everything implicitly authorised) */

    END IF;

  END IF;
  /* Otherwise return ALL records (everything implicitly authorised) */

  /* Return the query */
  RETURN @SQLStr;

END;

--Add version control record
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('SM_GetAuthorisingItemsScript', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;


/*********************************************************************/
/* 05/08/2005 - Add Filter_DateTime_Type field to SM_Rpt_Filter      */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Rpt_Filter'
  AND column_name = 'Filter_DateTime_Type') THEN

ALTER TABLE "DBA".SM_Rpt_Filter
ADD Filter_DateTime_Type CHAR(1) NOT NULL DEFAULT 'D'
  CONSTRAINT CKC_FILTER_DATETIME_T_SM_RPT_F CHECK (Filter_DateTime_Type IN ('D','T'));

END IF;


/*********************************************************************/
/* 22/08/2005 - Fix bug in SM_Operator update trigger causing foreign */
/*  key violations when a user does an initial entry of operator details */
/*  in the security manager, including the password                  */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'au_SM_Operator'
  AND Database_ID = (SELECT db_property( 'GlobalDBId' ))) THEN

ALTER TRIGGER au_SM_OPERATOR AFTER UPDATE OF Operator_Code, Pass_Word ON "DBA".SM_Operator
referencing old as old_row new as new_Row
for each row
begin
  IF UPDATE(Operator_Code) THEN
    --Remove operator history for old code
    DELETE SM_Operator_History
    WHERE Operator_Code = old_row.Operator_Code;
  END IF;

  IF UPDATE(Pass_Word) THEN
    update SM_Operator set
      Password_Date = current timestamp where
      Operator_Code = new_Row.Operator_Code;

    --Save the old password into the history table
    --SKIP if the operator code has been changed as the old code no longer exists
    IF NOT UPDATE(Operator_Code) THEN
      insert into SM_OPERATOR_HISTORY(Operator_Code,Pass_word)
      values(old_row.Operator_Code,old_Row.Pass_Word)
    END IF;
  END IF;
end;

--Add version control rec
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('au_SM_Operator', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;


/*********************************************************************/
/* 19/09/2005 - Add Entity_Comment_Text domain if doesn't exist      */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysusertype
  WHERE type_name = 'Entity_Comment_Text') THEN

CREATE DOMAIN Entity_Comment_Text AS TEXT;

END IF;


/*********************************************************************/
/* 23/08/2005 - Add Grouping Code tables                             */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_Group_Definition'
  AND table_type = 'BASE') THEN

CREATE TABLE "DBA".SM_Group_Definition 
(
    Group_Definition_No  Identity_No                    not null,
    Data_Entity_Name     Entity_Name                    not null,
    Group_Code           Entity_Code                    not null,
    Name                 Entity_Name                    not null,
    Memo                 Entity_Comment_Text,
    Group_Code_Default_No Entity_No,
    Is_System_Defined    BOOLEAN                        not null default 'N',
    Expiry_Date          Entity_Date,
    Available            BOOLEAN                        not null,
    DateTime_Created     Entity_DateTime_Def_Current    not null,
    DateTime_Modified    Entity_DateTime_Def_Current    not null,
    User_Created         varchar(50),
    User_Modified        varchar(50),
    constraint PK_SM_GROUP_DEFINITION primary key  (Group_Definition_No),
    constraint AK_KEY_2_SM_GRPDEF unique (Data_Entity_Name, Group_Code)
);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_Group_Code_Definition'
  AND table_type = 'BASE') THEN

CREATE TABLE "DBA".SM_Group_Code_Definition 
(
    Group_Definition_No  Entity_No                      not null,
    Group_Code_Definition_No Identity_No                    not null,
    Value                Entity_Code                    not null,
    Description          GEN_SHORT_DESCRIPTION,
    Available            BOOLEAN                        not null,
    DateTime_Created     Entity_DateTime_Def_Current    not null,
    DateTime_Modified    Entity_DateTime_Def_Current    not null,
    User_Created         varchar(50),
    User_Modified        varchar(50),
    constraint PK_SM_GROUP_CODE_DEFINITION primary key  (Group_Definition_No, Group_Code_Definition_No),
    constraint AK_KEY_2_SM_GRPCODE unique (Group_Definition_No, Value),
    constraint AK_KEY_3_SM_GRPCODE unique (Group_Code_Definition_No)
);

END IF;


--Create foreign keys on the new tables
IF NOT EXISTS(
  SELECT 1 from sys.sysforeignkey
  WHERE role='FK_SM_GROUP_REFERENCE_SM_GROUP') THEN

alter table "DBA".SM_Group_Code_Definition
   add constraint FK_SM_GROUP_REFERENCE_SM_GROUP foreign key (Group_Definition_No)
      references "DBA".SM_Group_Definition (Group_Definition_No)
      on update restrict
      on delete cascade;

END IF;

IF NOT EXISTS(
  SELECT 1 from sys.sysforeignkey
  WHERE role='FK_SM_GRPDEF_REF_SM_GRPCODE') THEN

alter table "DBA".SM_Group_Definition
   add constraint FK_SM_GRPDEF_REF_SM_GRPCODE foreign key (Group_Code_Default_No)
      references "DBA".SM_Group_Code_Definition (Group_Code_Definition_No)
      on update restrict
      on delete set null;

END IF;

--Create triggers on the new tables
IF NOT EXISTS(
  SELECT 1 FROM sys.systrigger
  WHERE trigger_name = 'au_SM_Group_Definition') THEN

CREATE TRIGGER au_SM_Group_Definition AFTER UPDATE OF Group_Code_Default_No ON "DBA".SM_Group_Definition
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  --Group_Code_Default_No must belong to this Group
  --(Foreign key constraint will ensure that it exists at all)
  IF NOT EXISTS(
    SELECT 1 
    FROM "DBA".SM_Group_Code_Definition
    WHERE Group_Definition_No = new_Row.Group_Definition_No
    AND Group_Code_Definition_No = new_Row.Group_Code_Default_No) THEN
    
    RAISERROR 30000 'Selected Group Code Default does not belong to this Group Definition';
    
  END IF;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sys.systrigger
  WHERE trigger_name = 'bi_SM_Group_Definition') THEN

CREATE TRIGGER bi_SM_Group_Definition BEFORE INSERT ON "DBA".SM_Group_Definition
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sys.systrigger
  WHERE trigger_name = 'bu_SM_Group_Definition') THEN

CREATE TRIGGER bu_SM_Group_Definition BEFORE UPDATE ON "DBA".SM_Group_Definition
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sys.systrigger
  WHERE trigger_name = 'bi_SM_Group_Code_Definition') THEN

CREATE TRIGGER bi_SM_Group_Code_Definition BEFORE INSERT ON "DBA".SM_Group_Code_Definition
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sys.systrigger
  WHERE trigger_name = 'bu_SM_Group_Code_Definition') THEN

CREATE TRIGGER bu_SM_Group_Code_Definition BEFORE UPDATE ON "DBA".SM_Group_Code_Definition
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;


/*********************************************************************/
/* 23/08/2005 - Add Grouping Code fields to relevant tables          */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Actions'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_Actions
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Actions
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Actions
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Actions
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Actions
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Cost_Rule'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_Cost_Rule
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Cost_Rule
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Cost_Rule
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Cost_Rule
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Cost_Rule
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_IdCard_Header'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_IdCard_Header
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_IdCard_Header
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_IdCard_Header
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_IdCard_Header
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_IdCard_Header
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_List_Form_View'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_List_Form_View
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_List_Form_View
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_List_Form_View
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_List_Form_View
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_List_Form_View
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Operator'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_Operator
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Operator
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Operator
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Operator
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Operator
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Site'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_Site
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Site_Workstation'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_Site_Workstation
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site_Workstation
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site_Workstation
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site_Workstation
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Site_Workstation
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Unit_Of_Measure'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_Unit_Of_Measure
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Unit_Of_Measure
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Unit_Of_Measure
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Unit_Of_Measure
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Unit_Of_Measure
  ADD Group_G4 Entity_Code NULL;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Vehicle_Type'
  AND column_name = 'Group_G0') THEN

  ALTER TABLE "DBA".SM_Vehicle_Type
  ADD Group_G0 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Vehicle_Type
  ADD Group_G1 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Vehicle_Type
  ADD Group_G2 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Vehicle_Type
  ADD Group_G3 Entity_Code NULL;

  ALTER TABLE "DBA".SM_Vehicle_Type
  ADD Group_G4 Entity_Code NULL;

END IF;


/*********************************************************************/
/* 19/10/2005 - Add stored procedures to purge System and Audit Logs */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_PurgeAuditLog') THEN

CREATE PROCEDURE SM_PurgeAuditLog(IN @MaxRetentionDays INTEGER, IN @ReferenceDate DATE DEFAULT CURRENT DATE)
BEGIN
  /******************************************************************************************/
  /* Procedure SM_PurgeAuditLog                                                             */
  /*                                                                                        */
  /* This procedure will delete all records in SM_Audit_Log that are older than the number  */
  /* of days specified in the @MaxRetentionDays parameter. Data is deleted relative to the  */
  /* input date, @ReferenceDate, which defaults to the current system date. (The            */
  /* @ReferenceDate is not included in the @MaxRetentionDays.)                              */
  /*                                                                                        */
  /* WARNING!!! In a replicating environment the procedure and the resulting replication    */
  /* will take considerable time when many DELETEs have taken place. This is because a      */
  /* separate DELETE statement is written to the log for every record deleted. This time is */
  /* multiplied as the number of subscriptions increase.                                    */
  /******************************************************************************************/
  DECLARE @RowsDeleted INTEGER;

  --Retention days must be specified
  IF @MaxRetentionDays IS NULL THEN
    RAISERROR 30000 'You must supply a Retention Period.';
    RETURN;
  END IF;

  --Cannot specify max retention days less than 90
  IF @MaxRetentionDays < 90 THEN
    RAISERROR 30001 'Minimum of 90 retention days allowed.';
    RETURN;
  END IF;

  --Date must be valid
  IF @ReferenceDate IS NULL THEN
    RAISERROR 30002 'You must supply a Reference Date.';
    RETURN;
  END IF;

  --REMOVE THE RECORDS
  DELETE "DBA".SM_Audit_Log
  WHERE DATEDIFF(Day, Event_Date, @ReferenceDate) > @MaxRetentionDays;
  SET @RowsDeleted = @@rowcount;

  --Add an audit log entry for the current date to log the purge
  INSERT "DBA".SM_Audit_Log
  (Site_No, Event_Date, Event_Time, Event_Class, Originator, Operator_Code, Event_Desc, App_Name, Replication_Site_No)
  VALUES ("DBA".SM_GetLocalSiteNo(), CURRENT DATE, CURRENT TIME, 5/*Audit Event*/, 'SM_PurgeAuditLog', "DBA".SM_UC_GetConnectionAppUserID(), 
    'Audit Log entries older than ' || RTRIM(CONVERT(CHAR, DATEADD(Day, (-1 * @MaxRetentionDays), @ReferenceDate), 103)) || ' have been purged from the database. ' ||
      'Approximately ' || RTRIM(CAST(@RowsDeleted AS CHAR)) || ' row(s) were deleted.',
    'Database', "DBA".SM_GetLocalSiteNo());
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_PurgeSystemLog') THEN

CREATE PROCEDURE SM_PurgeSystemLog(IN @MaxRetentionDays INTEGER, IN @ReferenceDate DATE DEFAULT CURRENT DATE)
BEGIN
  /******************************************************************************************/
  /* Procedure SM_PurgeSystemLog                                                            */
  /*                                                                                        */
  /* This procedure will delete all records in SM_System_Log that are older than the number */
  /* of days specified in the @MaxRetentionDays parameter. Data is deleted relative to the  */
  /* input date, @ReferenceDate, which defaults to the current system date. (The            */
  /* @ReferenceDate is not included in the @MaxRetentionDays.)                              */
  /*                                                                                        */
  /* WARNING!!! In a replicating environment the procedure and the resulting replication    */
  /* will take considerable time when many DELETEs have taken place. This is because a      */
  /* separate DELETE statement is written to the log for every record deleted. This time is */
  /* multiplied as the number of subscriptions increase.                                    */
  /******************************************************************************************/
  DECLARE @RowsDeleted INTEGER;

  --Retention days must be specified
  IF @MaxRetentionDays IS NULL THEN
    RAISERROR 30000 'You must supply a Retention Period.';
    RETURN;
  END IF;

  --Cannot specify max retention days less than 90
  IF @MaxRetentionDays < 90 THEN
    RAISERROR 30001 'Minimum of 90 retention days allowed.';
    RETURN;
  END IF;

  --Date must be valid
  IF @ReferenceDate IS NULL THEN
    RAISERROR 30002 'You must supply a Reference Date.';
    RETURN;
  END IF;

  --REMOVE THE RECORDS
  DELETE "DBA".SM_System_Log
  WHERE DATEDIFF(Day, Event_Date, @ReferenceDate) > @MaxRetentionDays;
  SET @RowsDeleted = @@rowcount;

  --Add an audit log entry for the current date to log the purge
  INSERT "DBA".SM_Audit_Log
  (Site_No, Event_Date, Event_Time, Event_Class, Originator, Operator_Code, Event_Desc, App_Name, Replication_Site_No)
  VALUES ("DBA".SM_GetLocalSiteNo(), CURRENT DATE, CURRENT TIME, 5/*Audit Event*/, 'SM_PurgeSystemLog', "DBA".SM_UC_GetConnectionAppUserID(), 
    'System Log entries older than ' || RTRIM(CONVERT(CHAR, DATEADD(Day, (-1 * @MaxRetentionDays), @ReferenceDate), 103)) || ' have been purged from the database. ' ||
      'Approximately ' || RTRIM(CAST(@RowsDeleted AS CHAR)) || ' row(s) were deleted.',
    'Database', "DBA".SM_GetLocalSiteNo());
END;

END IF;


/********************************************************************/
/* 1/11/2005 - Add field Global_Database_ID to SM_Site table        */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Site'
  AND column_name = 'Global_Database_ID') THEN

ALTER TABLE "DBA".SM_Site
ADD Global_Database_ID UNSIGNED INTEGER;

END IF;


/********************************************************************/
/* 15/02/2006 - Drop redundant indexes                              */
/********************************************************************/
IF EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_Site'
  AND i.index_name = 'SI_Site_Machine') THEN

DROP INDEX "DBA".SM_Site.SI_Site_Machine;

END IF;

IF EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_Authorisation_Config'
  AND i.index_name = 'IDX_Auth_Config_Relationship') THEN

DROP INDEX "DBA".SM_Authorisation_Config.IDX_Auth_Config_Relationship;

END IF;


/********************************************************************/
/* 15/02/2006 - Add clustered indexes (when supported) on System and Audit Log tables */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_System_Log'
  AND i.index_name = 'IDX_Event_Date') THEN

CREATE CLUSTERED INDEX IDX_Event_Date ON "DBA".SM_System_Log (EVENT_DATE ASC);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_Audit_Log'
  AND i.index_name = 'IDX_Event_Date') THEN

CREATE CLUSTERED INDEX IDX_Event_Date ON "DBA".SM_Audit_Log (EVENT_DATE ASC);

END IF;


/********************************************************************/
/* 15/02/2006 - Add indexes to replication fields                   */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_Image_Store'
  AND i.index_name = 'IDX_Replication') THEN

CREATE INDEX IDX_Replication ON "DBA".SM_Image_Store (Replication_Site_No ASC);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_System_Log'
  AND i.index_name = 'IDX_Replication') THEN

CREATE INDEX IDX_Replication ON "DBA".SM_System_Log (Replication_Site_No ASC);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_Audit_Log'
  AND i.index_name = 'IDX_Replication') THEN

CREATE INDEX IDX_Replication ON "DBA".SM_Audit_Log (Replication_Site_No ASC);

END IF;


/*********************************************************************/
/* 24/02/2006 - Add delete and update triggers to relevant entities to */
/*  implement referential integrity with respect to authorisation    */
/* (DELETEs are Cascaded, UPDATEs to primary keys are Restricted.)   */
/*********************************************************************/
--SM_Operator
IF NOT EXISTS(
  SELECT 1 FROM systrigger
  WHERE trigger_name = 'ad_SM_Operator') THEN

CREATE TRIGGER ad_SM_Operator AFTER DELETE ON DBA.SM_OPERATOR
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  DELETE SM_Authorisation_Item
  WHERE ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorising_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_OPERATOR')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorising_Class_Key IN
      (SELECT RTRIM(CAST(OPERATOR_CODE AS CHAR)) FROM DELETED)))
  OR ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorised_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_OPERATOR')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorised_Class_Key IN
      (SELECT RTRIM(CAST(OPERATOR_CODE AS CHAR)) FROM DELETED)))
END;

END IF;

--Authorisation trigger becomes the new au_SM_Operator, prior au_SM_Operator becomes au_SM_Operator_2
IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'au_SM_Operator'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 2) THEN

ALTER TRIGGER au_SM_OPERATOR AFTER UPDATE OF OPERATOR_CODE ON DBA.SM_OPERATOR
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  IF EXISTS(
    SELECT 1 FROM SM_Authorisation_Item
    WHERE ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorising_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_OPERATOR')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorising_Class_Key IN
        (SELECT RTRIM(CAST(OPERATOR_CODE AS CHAR)) FROM DELETED)))
    OR ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorised_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_OPERATOR')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorised_Class_Key IN
        (SELECT RTRIM(CAST(OPERATOR_CODE AS CHAR)) FROM DELETED)))
    ) THEN
    RAISERROR 30000 'Cannot modify field OPERATOR_CODE when authorisations exist.';
  END IF;
END;

--Update version control rec
UPDATE "DBA".SM_Version_Control
SET Version_No = 2
WHERE Entity_Name = 'au_SM_Operator'
AND Database_ID = (SELECT db_property('GlobalDbID'));

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systrigger
  WHERE trigger_name = 'au_SM_Operator_2') THEN

CREATE TRIGGER au_SM_Operator_2 AFTER UPDATE OF Operator_Code, Pass_Word ORDER 2 ON "DBA".SM_Operator
referencing old as old_row new as new_Row
for each row
begin
  IF UPDATE(Operator_Code) THEN
    --Remove operator history for old code
    DELETE SM_Operator_History
    WHERE Operator_Code = old_row.Operator_Code;
  END IF;

  IF UPDATE(Pass_Word) THEN
    update SM_Operator set
      Password_Date = current timestamp where
      Operator_Code = new_Row.Operator_Code;

    --Save the old password into the history table
    --SKIP if the operator code has been changed as the old code no longer exists
    IF NOT UPDATE(Operator_Code) THEN
      insert into SM_OPERATOR_HISTORY(Operator_Code,Pass_word)
      values(old_row.Operator_Code,old_Row.Pass_Word)
    END IF;
  END IF;
end;

END IF;

--SM_Security_Object
IF NOT EXISTS(
  SELECT 1 FROM systrigger
  WHERE trigger_name = 'ad_SM_Security_Object') THEN

CREATE TRIGGER ad_SM_Security_Object AFTER DELETE ON DBA.SM_SECURITY_OBJECT
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  DELETE SM_Authorisation_Item
  WHERE ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorising_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_SECURITY_OBJECT')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorising_Class_Key IN
      (SELECT RTRIM(CAST(SECURITY_OBJECT_NO AS CHAR)) FROM DELETED)))
  OR ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorised_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_SECURITY_OBJECT')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorised_Class_Key IN
      (SELECT RTRIM(CAST(SECURITY_OBJECT_NO AS CHAR)) FROM DELETED)))
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systrigger
  WHERE trigger_name = 'au_SM_Security_Object') THEN

CREATE TRIGGER au_SM_Security_Object AFTER UPDATE OF SECURITY_OBJECT_NO ON DBA.SM_SECURITY_OBJECT
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  IF EXISTS(
    SELECT 1 FROM SM_Authorisation_Item
    WHERE ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorising_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_SECURITY_OBJECT')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorising_Class_Key IN
        (SELECT RTRIM(CAST(SECURITY_OBJECT_NO AS CHAR)) FROM DELETED)))
    OR ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorised_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_SECURITY_OBJECT')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorised_Class_Key IN
        (SELECT RTRIM(CAST(SECURITY_OBJECT_NO AS CHAR)) FROM DELETED)))
    ) THEN
    RAISERROR 30000 'Cannot modify field SECURITY_OBJECT_NO when authorisations exist.';
  END IF;
END;

END IF;

--SM_Site
--AFTER DELETE trigger must be redefined as the SM_Authorisation_Detail table is no longer used
IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'ad_SM_Site'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER TRIGGER ad_SM_Site AFTER DELETE ON DBA.SM_Site
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  DELETE SM_Authorisation_Item
  WHERE ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorising_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_Site')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorising_Class_Key IN
      (SELECT RTRIM(CAST(Site_No AS CHAR)) FROM DELETED)))
  OR ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorised_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_Site')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorised_Class_Key IN
      (SELECT RTRIM(CAST(Site_No AS CHAR)) FROM DELETED)))
END;

--Insert version control rec
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('ad_SM_Site', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systrigger
  WHERE trigger_name = 'au_SM_Site') THEN

CREATE TRIGGER au_SM_Site AFTER UPDATE OF Site_No ON DBA.SM_Site
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  IF EXISTS(
    SELECT 1 FROM SM_Authorisation_Item
    WHERE ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorising_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_Site')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorising_Class_Key IN
        (SELECT RTRIM(CAST(Site_No AS CHAR)) FROM DELETED)))
    OR ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorised_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_Site')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorised_Class_Key IN
        (SELECT RTRIM(CAST(Site_No AS CHAR)) FROM DELETED)))
    ) THEN
    RAISERROR 30000 'Cannot modify field Site_No when authorisations exist.';
  END IF;
END;

END IF;


/*********************************************************************/
/* 24/02/2006 - Add SM_PurgeInvalidAuthItems proc                    */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_PurgeInvalidAuthItems') THEN

CREATE PROCEDURE SM_PurgeInvalidAuthItems()
BEGIN
  DECLARE @ClassID INT;
  DECLARE @TableName VARCHAR(128);
  DECLARE @KeyFieldName VARCHAR(128);
  DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000';

  --NOTE: Cannot query when field name contains a ';', i.e. composite field, so skip these if present
  DECLARE Object_Cursor NO SCROLL CURSOR FOR
    SELECT Object_Class, Table_Name, Key_Field
    FROM SM_Object_Class
    WHERE (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
    AND (Class_Group = 2)	-- Object Group = ogTable
    AND ((Object_Class IN (SELECT Authorising_Class_ID FROM SM_Authorisation_Config))
      OR (Object_Class IN (SELECT Authorised_Class_ID FROM SM_Authorisation_Config)))
    AND (CHARINDEX(';', Key_Field) = 0);

  OPEN Object_Cursor;
  This_Loop:
  LOOP
    FETCH NEXT Object_Cursor INTO @ClassID, @TableName, @KeyFieldName;
    IF SQLSTATE = err_notfound THEN
      LEAVE This_Loop
    END IF;

    --Strip any alias present off the Key Field name
    IF CHARINDEX('.', @KeyFieldName) > 0 THEN
      SET @KeyFieldName = RIGHT(@KeyFieldName, LEN(@KeyFieldName) - CHARINDEX('.', @KeyFieldName));
    END IF;

    EXECUTE IMMEDIATE 'DELETE SM_Authorisation_Item
    WHERE ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorising_Class_ID = ' || @ClassID || '))
      AND (Authorising_Class_Key NOT IN
        (SELECT RTRIM(CAST(' || @KeyFieldName || ' AS CHAR)) FROM ' || @TableName || ')))
    OR ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorised_Class_ID = ' || @ClassID ||'))
      AND (Authorised_Class_Key NOT IN
        (SELECT RTRIM(CAST(' || @KeyFieldName || ' AS CHAR)) FROM ' || @TableName || ')))';

  END LOOP This_Loop;
  CLOSE Object_Cursor;
END;

END IF;


/********************************************************************/
/* 24/03/2006 - Add Site Workstation No column to SM_System_Log and SM_Audit_Log */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_System_Log'
  AND column_name = 'Site_Workstation_No') THEN

ALTER TABLE "DBA".SM_System_Log
ADD Site_Workstation_No Entity_No NULL;

ALTER TABLE "DBA".SM_System_Log
   add constraint FK_SM_SYSLOG_REF_SM_SITE_WKS foreign key (Site_Workstation_No)
      references "DBA".SM_Site_Workstation (Site_Workstation_No)
      on update restrict
      on delete restrict;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Audit_Log'
  AND column_name = 'Site_Workstation_No') THEN

ALTER TABLE "DBA".SM_Audit_Log
ADD Site_Workstation_No Entity_No NULL;

ALTER TABLE "DBA".SM_Audit_Log
   add constraint FK_SM_AUDLOG_REF_SM_SITE_WKS foreign key (Site_Workstation_No)
      references "DBA".SM_Site_Workstation (Site_Workstation_No)
      on update restrict
      on delete restrict;

END IF;


/********************************************************************/
/* 19/04/2006 - Add Last_Replication field to SM_Site                */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Site'
  AND column_name = 'Last_Replication') THEN

ALTER TABLE "DBA".SM_Site
ADD Last_Replication Entity_DateTime DEFAULT NULL;

END IF;


/********************************************************************/
/* 26/05/2006 - Add SM_IsReplicating function                       */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_IsReplicating') THEN

CREATE FUNCTION "DBA".SM_IsReplicating()
RETURNS Boolean
BEGIN
  IF EXISTS(SELECT 1 FROM syspublication) THEN
    RETURN 'Y';
  ELSE
    RETURN 'N';
  END IF;
END;

END IF;


/********************************************************************/
/* 3/11/2006 - Remove WasteMan 2G-specific commands from trigger on */
/*  SM_Action_Schedules                                             */
/********************************************************************/
IF EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'bu_SM_Action_Schedules'
  AND Database_ID = (SELECT db_property('GlobalDbID'))
  AND Version_No < 3) THEN

ALTER TRIGGER bu_SM_Action_Schedules BEFORE UPDATE ON "DBA".SM_Action_Schedules
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

--Update version control rec
UPDATE "DBA".SM_Version_Control
SET Version_No = 3
WHERE Entity_Name = 'bu_SM_Action_Schedules'
AND Database_ID = (SELECT db_property('GlobalDbID'));

END IF;


/********************************************************************/
/* 3/11/2006 - Add Replication_Site_Flag to SM_Enterprise_Config_Header */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Enterprise_Config_Header'
  AND column_name = 'Replication_Site_Flag') THEN

ALTER TABLE "DBA".SM_Enterprise_Config_Header
ADD Replication_Site_Flag Rep_Site_No NULL;

--Add index on field
CREATE INDEX IDX_Replication ON "DBA".SM_Enterprise_Config_Header
(Replication_Site_Flag ASC);

END IF;


/********************************************************************/
/* 14/11/2006 - Add View_Version field to SM_List_Form_View         */
/* NOTE: This field does not permit NULLs in the schema but is allowed */
/*  to be NULL here due to restrictions when adding fields to tables with data */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_List_Form_View'
  AND column_name = 'View_Version') THEN

ALTER TABLE "DBA".SM_List_Form_View
ADD VIEW_VERSION UNSIGNED INT NULL DEFAULT 0;

END IF;


/********************************************************************/
/* 16/11/2006 - Add start and end stored procedures for List View Test */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_ListViewTestStart') THEN

CREATE PROCEDURE "DBA".SM_ListViewTestStart(IN @ViewID CHAR(4))
BEGIN
  --NOTE 1: Use value of '1' as it will (99% of cases) not cause any errors when used as either
  -- a string, integer or float
  --NOTE 2: A maximum of 20 parameters is supported

  CREATE VARIABLE @Param1 VARCHAR(50);
  SET @Param1 = '1';
  CREATE VARIABLE @Param2 VARCHAR(50);
  SET @Param2 = '1';
  CREATE VARIABLE @Param3 VARCHAR(50);
  SET @Param3 = '1';
  CREATE VARIABLE @Param4 VARCHAR(50);
  SET @Param4 = '1';
  CREATE VARIABLE @Param5 VARCHAR(50);
  SET @Param5 = '1';
  CREATE VARIABLE @Param6 VARCHAR(50);
  SET @Param6 = '1';
  CREATE VARIABLE @Param7 VARCHAR(50);
  SET @Param7 = '1';
  CREATE VARIABLE @Param8 VARCHAR(50);
  SET @Param8 = '1';
  CREATE VARIABLE @Param9 VARCHAR(50);
  SET @Param9 = '1';
  CREATE VARIABLE @Param10 VARCHAR(50);
  SET @Param10 = '1';
  CREATE VARIABLE @Param11 VARCHAR(50);
  SET @Param11 = '1';
  CREATE VARIABLE @Param12 VARCHAR(50);
  SET @Param12 = '1';
  CREATE VARIABLE @Param13 VARCHAR(50);
  SET @Param13 = '1';
  CREATE VARIABLE @Param14 VARCHAR(50);
  SET @Param14 = '1';
  CREATE VARIABLE @Param15 VARCHAR(50);
  SET @Param15 = '1';
  CREATE VARIABLE @Param16 VARCHAR(50);
  SET @Param16 = '1';
  CREATE VARIABLE @Param17 VARCHAR(50);
  SET @Param17 = '1';
  CREATE VARIABLE @Param18 VARCHAR(50);
  SET @Param18 = '1';
  CREATE VARIABLE @Param19 VARCHAR(50);
  SET @Param19 = '1';
  CREATE VARIABLE @Param20 VARCHAR(50);
  SET @Param20 = '1';
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_ListViewTestEnd') THEN

CREATE PROCEDURE "DBA".SM_ListViewTestEnd(IN @ViewID CHAR(4))
BEGIN
  DROP VARIABLE @Param1;
  DROP VARIABLE @Param2;
  DROP VARIABLE @Param3;
  DROP VARIABLE @Param4;
  DROP VARIABLE @Param5;
  DROP VARIABLE @Param6;
  DROP VARIABLE @Param7;
  DROP VARIABLE @Param8;
  DROP VARIABLE @Param9;
  DROP VARIABLE @Param10;
  DROP VARIABLE @Param11;
  DROP VARIABLE @Param12;
  DROP VARIABLE @Param13;
  DROP VARIABLE @Param14;
  DROP VARIABLE @Param15;
  DROP VARIABLE @Param16;
  DROP VARIABLE @Param17;
  DROP VARIABLE @Param18;
  DROP VARIABLE @Param19;
  DROP VARIABLE @Param20;
END;

END IF;


/********************************************************************/
/* 19/12/2006 - Add Refresh_Login_Time column to SM_Operator        */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Operator'
  AND column_name = 'Refresh_Login_Time') THEN

ALTER TABLE "DBA".SM_Operator
ADD Refresh_Login_Time UNSIGNED SMALLINT NULL DEFAULT 0;

END IF;


/********************************************************************/
/* 23/01/2007 - Add new fields to SM_Print_Template_Item            */
/* NOTE: These are added as NULL-permitting to prevent DB errors    */
/*  when run on < 9.0.2 DB server version                           */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Print_Template_Item'
  AND column_name = 'Expandable') THEN

ALTER TABLE "DBA".SM_Print_Template_Item
ADD Expandable Boolean NULL DEFAULT 'N';

END IF;

IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Print_Template_Item'
  AND column_name = 'Max_Expand_Lines') THEN

ALTER TABLE "DBA".SM_Print_Template_Item
ADD Max_Expand_Lines SMALLINT NULL DEFAULT 0;

END IF;


/*********************************************************************/
/* 23/01/2007 - Update PrintService_TemplateDets to return new fields */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'PrintService_TemplateDets'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

ALTER PROCEDURE "DBA".PrintService_TemplateDets(IN @TemplateNo Entity_No)
RESULT(
  Template_Title VARCHAR(25),
  Template_Description Gen_Large_Description,
  Page_Width UNSIGNED INT,
  Page_Length UNSIGNED INT,
  Template_Section UNSIGNED INT,
  Visible Boolean,
  Height_In_Lines UNSIGNED INT,
  Template_Line_No Entity_No,
  Line_No UNSIGNED INT,
  Template_Item_No Entity_No,
  Width UNSIGNED INT,
  Column_No UNSIGNED INT,
  Print_Item_Type INTEGER,
  Print_Item_Value VARCHAR(80),
  Alignment UNSIGNED INT,
  Bold Boolean,
  Underline Boolean,
  Italic Boolean,
  Double_Width Boolean,
  Special_Format Boolean,
  Expandable Boolean,
  Max_Expand_Lines SMALLINT)
BEGIN
  SELECT t.Template_Title,
    t.Template_Description,
    t.Page_Width,
    t.Page_Length,
    s.Template_Section,
    s.Visible,
    s.Height_In_Lines,
    l.Template_Line_No,
    l.Line_No,
    i.Template_Item_No,
    i.Width,
    i.Column_No,
    i.Print_Item_Type,
    i.Print_Item_Value,
    i.Alignment,
    i.Bold,
    i.Underline,
    i.Italic,
    i.Double_Width,
    i.Special_Format,
    i.Expandable,
    i.Max_Expand_Lines
  FROM SM_Print_Template t
  LEFT OUTER JOIN SM_Print_Template_Section s
  LEFT OUTER JOIN SM_Print_Template_Line l
  LEFT OUTER JOIN SM_Print_Template_Item i
  WHERE t.Template_No = @TemplateNo
  ORDER BY s.Template_Section, l.Line_No, i.Column_No;
END;

--Add version control rec
INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
VALUES('PrintService_TemplateDets', (SELECT db_property( 'GlobalDBId' )), 1);

END IF;


/*********************************************************************/
/* 20/03/2007 - Add stored procedure to increment the EFTPOS reference no. */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysprocedure
  WHERE proc_name = 'SM_GetNextEftposRef') THEN

CREATE PROCEDURE "DBA".SM_GetNextEftposRef(IN @SiteNo Entity_No, IN @SiteCode Entity_Code,
  OUT @EftposRef VARCHAR(16), OUT @NextRefNo VARCHAR(8))
BEGIN ATOMIC
  DECLARE @localSiteNo Entity_No;
  DECLARE @refNo VARCHAR(8);

  IF (@SiteNo IS NULL) OR (ISNULL(@SiteCode, '') = '') THEN
    RAISERROR 50001 'Site Number and Code must be specified.';
    RETURN;
  END IF;

  SET @localSiteNo = SM_GetLocalSiteNo();

  --Get current ref no. or 1 if doesn't exist
  SET @refNo = ISNULL(SM_EntCfgGetItemValue(@SiteNo, NULL, 'SRV2023:Tran Ref', 'Next Tran Ref No'), 1);

  --Write @refNo + 1 back to enterprise config
  --SM_EntCfgSetItemValue proc will create the header and/or item if necessary
  IF ISNUMERIC(@refNo) = 1 THEN
    SET @refNo = TRUNCNUM(CAST(RTRIM(@refNo) AS FLOAT), 0);  --remove decimals if returned
    SET @NextRefNo = RTRIM(CAST((CAST(@refNo AS INT) + 1) AS CHAR));
  ELSE
    RAISERROR 50001 'Invalid value found for Next Tran Ref No: %1!', @refNo;
    RETURN;
  END IF;
  CALL SM_EntCfgSetItemValue(@SiteNo, NULL, 'SRV2023:Tran Ref', 'Next Tran Ref No', @NextRefNo, 2023, @localSiteNo);

  --Return the eftpos reference no.
  SET @EftposRef = LEFT(@SiteCode, 8) || REPLICATE('0', 8 - LENGTH(@refNo)) || @refNo;

END;

END IF;


/*********************************************************************/
/* 16/08/2007 - Add Grouping_Code domain if it doesn't exist         */
/* Entry is added to SM_Version_Control if domain is added. This     */
/*  is to indicate to application update scripts that the domain     */
/*  addition must be passed through when replication is enabled      */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM sysusertype
  WHERE type_name = 'Grouping_Code') THEN

CREATE DOMAIN Grouping_Code AS VARCHAR(100);

INSERT "DBA".SM_Version_Control
(Entity_Name, Entity_Comment, Version_No)
VALUES('Grouping_Code Add', 'Flags Grouping_Code domain added. Not required in a new database', 1);

END IF;


/*********************************************************************/
/* 16/08/2007 - Add new tables required for Vehicle Configurations   */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_Vehicle_Configuration') THEN

CREATE TABLE "DBA".SM_Vehicle_Configuration 
(
    Vehicle_Configuration_No Identity_No                    not null,
    Code                 Entity_Code                    not null,
    Name                 Entity_Name,
    Memo                 Entity_Comment,
    Gross_Max            Weight                         not null,
    DateTime_Created     Entity_DateTime_Def_Current    not null default current timestamp,
    DateTime_Modified    Entity_DateTime_Def_Current    not null default current timestamp,
    User_Created         varchar(50)                    not null,
    User_Modified        varchar(50)                    not null,
    Group_G0             Grouping_Code,
    Group_G1             Grouping_Code,
    Group_G2             Grouping_Code,
    Group_G3             Grouping_Code,
    Group_G4             Grouping_Code,
    constraint PK_SM_VEHICLE_CONFIGURATION primary key  (Vehicle_Configuration_No)
);

CREATE CLUSTERED INDEX IDX_Code
ON "DBA".SM_Vehicle_Configuration(Code ASC);

CREATE TRIGGER bi_SM_Vehicle_Configuration BEFORE INSERT ON "DBA".SM_Vehicle_Configuration
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

CREATE TRIGGER bu_SM_Vehicle_Configuration BEFORE UPDATE ON "DBA".SM_Vehicle_Configuration
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

CREATE TRIGGER bi_SM_Vehicle_Configuration_2 BEFORE INSERT ORDER 2 ON "DBA".SM_Vehicle_Configuration
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  --Permit duplicates entered via SQL Remote but bomb out if anyone else enters
  IF CURRENT REMOTE USER IS NULL
  AND EXISTS(SELECT 1 FROM SM_Vehicle_Configuration WHERE Code = new_Row.Code) THEN
    RAISERROR 30000 'Vehicle Configuration Code "' || new_Row.Code || '" already exists';
  END IF;
END;

CREATE TRIGGER bu_SM_Vehicle_Configuration_2 BEFORE UPDATE ORDER 2 ON "DBA".SM_Vehicle_Configuration
REFERENCING OLD AS old_Row NEW AS new_Row
FOR EACH ROW
BEGIN
  --Permit duplicates entered via SQL Remote but bomb out if anyone else enters
  IF UPDATE(Code) THEN
    IF CURRENT REMOTE USER IS NULL
    AND EXISTS(SELECT 1 FROM SM_Vehicle_Configuration WHERE Code = new_Row.Code) THEN
      RAISERROR 30000 'Vehicle Configuration Code "' || new_Row.Code || '" already exists';
    END IF;
  END IF;
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_Vehicle_Weigh_Configuration') THEN

CREATE TABLE "DBA".SM_Vehicle_Weigh_Configuration 
(
    Vehicle_Weigh_Configuration_No Identity_No                    not null,
    Vehicle_Configuration_No Entity_No                      not null,
    Description          GEN_SHORT_DESCRIPTION          not null,
    Weighbridge          varchar(256)                   not null,
    Weigh_Direction      char(1)                        not null default 'A'
       constraint CKC_WEIGH_DIRECTION_SM_VEHIC check (Weigh_Direction in ('A','I','O')),
    DateTime_Created     Entity_DateTime_Def_Current    not null default current timestamp,
    DateTime_Modified    Entity_DateTime_Def_Current    not null default current timestamp,
    User_Created         varchar(50)                    not null,
    User_Modified        varchar(50)                    not null,
    constraint PK_SM_VEHICLE_WEIGH_CONFIGURAT primary key  (Vehicle_Weigh_Configuration_No)
);

CREATE UNIQUE INDEX IDX_Direction ON "DBA".SM_Vehicle_Weigh_Configuration
(Vehicle_Configuration_No ASC, Weighbridge ASC, Weigh_Direction ASC);

ALTER TABLE "DBA".SM_Vehicle_Weigh_Configuration
   add constraint FK_VehicleWeighConfig_Ref_VehicleConfig foreign key (Vehicle_Configuration_No)
      references "DBA".SM_Vehicle_Configuration (Vehicle_Configuration_No)
      on update restrict
      on delete cascade;

CREATE TRIGGER bi_SM_Vehicle_Weigh_Configuration BEFORE INSERT ON "DBA".SM_Vehicle_Weigh_Configuration
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

CREATE TRIGGER bu_SM_Vehicle_Weigh_Configuration BEFORE UPDATE ON "DBA".SM_Vehicle_Weigh_Configuration
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;

IF NOT EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_Vehicle_Weighment_Configuration') THEN

CREATE TABLE "DBA".SM_Vehicle_Weighment_Configuration 
(
    Vehicle_Weighment_Configuration_No Identity_No                    not null,
    Vehicle_Weigh_Configuration_No Entity_No                      not null,
    Weighbridge_Platform varchar(256)                   not null,
    Sequence_Number      integer                        not null default 1,
    Gross_Min            Weight                         not null,
    Gross_Max            Weight                         not null,
    DateTime_Created     Entity_DateTime_Def_Current    not null default current timestamp,
    DateTime_Modified    Entity_DateTime_Def_Current    not null default current timestamp,
    User_Created         varchar(50)                    not null,
    User_Modified        varchar(50)                    not null,
    constraint PK_SM_VEHICLE_WEIGHMENT_CONFIG primary key  (Vehicle_Weighment_Configuration_No)
);

ALTER TABLE "DBA".SM_Vehicle_Weighment_Configuration
   add constraint FK_VehicleWeighmentConfig_Ref_VehicleWeighConfig foreign key (Vehicle_Weigh_Configuration_No)
      references "DBA".SM_Vehicle_Weigh_Configuration (Vehicle_Weigh_Configuration_No)
      on update restrict
      on delete cascade;

CREATE TRIGGER bi_SM_Vehicle_Weighment_Configuration BEFORE INSERT ON "DBA".SM_Vehicle_Weighment_Configuration
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

CREATE TRIGGER bu_SM_Vehicle_Weighment_Configuration BEFORE UPDATE ON "DBA".SM_Vehicle_Weighment_Configuration
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;


/********************************************************************/
/* 26/10/2007 - Add new field Replication_Cull_In_Progress to SM_Site */
/*  This field is not referenced by code and is required only for Sybase */
/*  replication processes                                           */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Site'
  AND column_name = 'Replication_Cull_In_Progress') THEN

ALTER TABLE "DBA".SM_Site
ADD Replication_Cull_In_Progress BOOLEAN NOT NULL DEFAULT 'N';

END IF;


/*********************************************************************/
/* 19/11/2007 - Add allowed value of I (for DateTime) to SM_Report_Filter.Filter_DateTime_Type */
/*********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM "DBA".SM_Version_Control
  WHERE Entity_Name = 'CKC_FILTER_DATETIME_T_SM_RPT_F'
  AND Database_ID = (SELECT db_property('GlobalDbID'))) THEN

IF EXISTS(
  SELECT 1
  FROM systable
  WHERE table_name = 'sysconstraint') THEN

  --Named constraints supported, update by constraint name if exists otherwise update without constraint name
  IF EXISTS(
    SELECT 1
    FROM sysconstraint
    WHERE constraint_name = 'CKC_FILTER_DATETIME_T_SM_RPT_F') THEN

    ALTER TABLE "DBA".SM_Rpt_Filter
    MODIFY Filter_DateTime_Type CONSTRAINT CKC_FILTER_DATETIME_T_SM_RPT_F CHECK (Filter_DateTime_Type IN ('D','T','I'));

    --Add version control rec (consolidated)
    INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
    VALUES('CKC_FILTER_DATETIME_T_SM_RPT_F', (SELECT db_property('GlobalDbID')), 1);

  ELSE

    ALTER TABLE "DBA".SM_Rpt_Filter
    MODIFY Filter_DateTime_Type CHECK (Filter_DateTime_Type in ('D','T','I'));

    --Add version control rec (consolidated)
    INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
    VALUES('CKC_FILTER_DATETIME_T_SM_RPT_F', (SELECT db_property('GlobalDbID')), 1);

  END IF;

ELSE  --Named constraints not supported, modify check constraint without name

  ALTER TABLE "DBA".SM_Rpt_Filter
  MODIFY Filter_DateTime_Type CHECK (Filter_DateTime_Type in ('D','T','I'));

  --Add version control rec (consolidated)
  INSERT "DBA".SM_Version_Control(Entity_Name, Database_ID, Version_No)
  VALUES('CKC_FILTER_DATETIME_T_SM_RPT_F', (SELECT db_property('GlobalDbID')), 1);

END IF;

END IF;


/***********************************************************************/
/* 20/11/2007 - Add SM_Site_Group table                                */
/***********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM systable
  WHERE table_name = 'SM_Site_Group') THEN

CREATE TABLE "DBA".SM_Site_Group 
(
    Site_Group_No        Identity_No                    not null,
    Name                 Entity_Name                    not null,
    Memo                 Entity_Comment,
    DateTime_Created     Entity_DateTime_Def_Current    not null default current timestamp,
    DateTime_Modified    Entity_DateTime_Def_Current    not null default current timestamp,
    User_Created         varchar(50),
    User_Modified        varchar(50),
    Group_G0             Grouping_Code,
    Group_G1             Grouping_Code,
    Group_G2             Grouping_Code,
    Group_G3             Grouping_Code,
    Group_G4             Grouping_Code,
    constraint PK_SM_SITE_GROUP primary key  (Site_Group_No),
    constraint AK_KEY_2_SM_SITE_GROUP unique (Name)
);

CREATE TRIGGER ad_SM_Site_Group AFTER DELETE ON "DBA".SM_Site_Group
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  DELETE SM_Authorisation_Item
  WHERE ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorising_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_Site_Group')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorising_Class_Key IN
      (SELECT RTRIM(CAST(Site_Group_No AS CHAR)) FROM DELETED)))
  OR ((Authorisation_Config_No IN
    (SELECT Authorisation_Config_No
     FROM SM_Authorisation_Config
     WHERE Authorised_Class_ID IN (
       SELECT Object_Class FROM SM_Object_Class
       WHERE (Table_Name = 'SM_Site_Group')
       AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
       AND (Class_Group = 2))))		-- Object Group = ogTable
    AND (Authorised_Class_Key IN
      (SELECT RTRIM(CAST(Site_Group_No AS CHAR)) FROM DELETED)))
END;

CREATE TRIGGER au_SM_Site_Group AFTER UPDATE OF Site_Group_No ON "DBA".SM_Site_Group
REFERENCING OLD AS DELETED
FOR EACH STATEMENT
BEGIN
  IF EXISTS(
    SELECT 1 FROM SM_Authorisation_Item
    WHERE ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorising_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_Site_Group')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorising_Class_Key IN
        (SELECT RTRIM(CAST(Site_Group_No AS CHAR)) FROM DELETED)))
    OR ((Authorisation_Config_No IN
      (SELECT Authorisation_Config_No
       FROM SM_Authorisation_Config
       WHERE Authorised_Class_ID IN (
         SELECT Object_Class FROM SM_Object_Class
         WHERE (Table_Name = 'SM_Site_Group')
         AND (Class_Type IN (3,4))	-- Object Class = ocStandardModuleEntity / ocApplicationEntity
         AND (Class_Group = 2))))		-- Object Group = ogTable
      AND (Authorised_Class_Key IN
        (SELECT RTRIM(CAST(Site_Group_No AS CHAR)) FROM DELETED)))
    ) THEN
    RAISERROR 30000 'Cannot modify field Site_Group_No when authorisations exist.';
  END IF;
END;

CREATE TRIGGER bi_SM_Site_Group BEFORE INSERT ON "DBA".SM_Site_Group
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.User_Created = (SELECT SM_UC_GetConnectionAppUserID());
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

CREATE TRIGGER bu_SM_Site_Group BEFORE UPDATE ON "DBA".SM_Site_Group
REFERENCING NEW AS new_Row
FOR EACH ROW
BEGIN
  SET new_Row.DateTime_Modified = GetDate();
  SET new_Row.User_Modified = (SELECT SM_UC_GetConnectionAppUserID());
END;

END IF;

--Add F/K to SM_Site
IF NOT EXISTS(
  SELECT 1 FROM systable t
  INNER JOIN syscolumn c ON (c.table_id = t.table_id)
  WHERE t.table_name = 'SM_Site'
  AND c.column_name = 'Site_Group_No') THEN

ALTER TABLE "DBA".SM_Site
ADD Site_Group_No Entity_No NULL;

ALTER TABLE "DBA".SM_Site
   add constraint FK_SM_SITE_REF_SM_SITE_GROUP foreign key (Site_Group_No)
      references "DBA".SM_Site_Group (Site_Group_No)
      on update restrict
      on delete restrict;

END IF;


/********************************************************************/
/* 18/01/2008 - Add Retry_Counter to email queue                    */
/********************************************************************/
IF NOT EXISTS(
  SELECT 1 FROM syscolumn JOIN systable
  WHERE table_name = 'SM_Email_Queue'
  AND column_name = 'Retry_Count') THEN

DELETE SM_Email_Queue;
ALTER TABLE SM_Email_Queue ADD Retry_Count int not null default 0;

END IF;


/********************************************************************/
/* 23/06/2008 - Change index on SM_Security_Object to UNIQUE to prevent */
/*  duplicate records being inserted                                */
/********************************************************************/
IF EXISTS(
  SELECT 1 FROM sysindex i
  INNER JOIN systable t on (t.table_id = i.table_id)
  WHERE t.table_name = 'SM_Security_Object'
  AND i.index_name = 'IDX_SecurityAccess'
  AND i."unique" = 'N') THEN

DROP INDEX "DBA".SM_SECURITY_OBJECT.IDX_SecurityAccess;

CREATE UNIQUE INDEX IDX_SecurityAccess ON "DBA".SM_SECURITY_OBJECT
(SECURITY_CLASS_CODE ASC, PARENT_OBJECT_NO ASC, SECURITY_OBJECT_REF ASC, PRODUCT_ID ASC, OBJECT_TITLE ASC);

END IF;



/* *** %MG - RETAIN THIS MESSAGE AT THE END OF FILE *** */
/* REMEMBER TO UPDATE "Default DB Version Nos.sql" IN SOURCE FOLDER "\Database\Sybase\Standard Modules\Default Data" */
/* WHEN A CHANGE HAS BEEN MADE TO SM_Version_Control  */