-- ---------------------------------
-- Do not edit this file manually --
-- ---------------------------------



-- Create Database Lock Table
CREATE TABLE DATABASECHANGELOGLOCK (ID int NOT NULL, LOCKED bit NOT NULL, LOCKGRANTED datetime2(3), LOCKEDBY nvarchar(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
GO

-- Initialize Database Lock Table
DELETE FROM DATABASECHANGELOGLOCK
GO

INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0)
GO

-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'buildpublite-jdk17-klkkm-whjbn (10.100.70.151)', LOCKGRANTED = GETDATE() WHERE ID = 1 AND LOCKED = 0
GO

-- Create Database Change Log Table
CREATE TABLE DATABASECHANGELOG (ID nvarchar(255) NOT NULL, AUTHOR nvarchar(255) NOT NULL, FILENAME nvarchar(255) NOT NULL, DATEEXECUTED datetime2(3) NOT NULL, ORDEREXECUTED int NOT NULL, EXECTYPE nvarchar(10) NOT NULL, MD5SUM nvarchar(35), DESCRIPTION nvarchar(255), COMMENTS nvarchar(255), TAG nvarchar(255), LIQUIBASE nvarchar(20), CONTEXTS nvarchar(255), LABELS nvarchar(255), DEPLOYMENT_ID nvarchar(10))
GO

-- Changeset nschema/mysql.93.changeLog.xml::InitialMysql93::shravan
INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('InitialMysql93', 'shravan', 'nschema/mysql.93.changeLog.xml', GETDATE(), 1, '8:f6f92addf0ab4bf6f65458d1abcee14f', 'sql', '', 'MARK_RAN', '93', NULL, '4.8.0', '0912889689')
GO

-- Changeset nschema/mssql.93.changeLog.xml::InitialMssql93::shravan
CREATE PROCEDURE p_remove_sess_user
AS
  DELETE FROM e_signature_context
  WHERE spid = @@SPID;
GO

create procedure p_set_sess_user
    @userid             nvarchar(85),
    @update_meaning     nvarchar(500),
    @updater_legal_name nvarchar(250)
AS
  EXEC p_remove_sess_user;
  INSERT INTO e_signature_context VALUES (@@SPID, @userid, @update_meaning, @updater_legal_name);
GO

create procedure p_disable_all_triggers
AS
  declare @string     varchar(8000)
  declare @table_name nvarchar(500)
  declare cur cursor
  for select
        name as tbname
      from sysobjects
      where id in (select
                     parent_obj
                   from sysobjects
                   where xtype = 'TR')
  open cur
  fetch next from cur
  into @table_name
  while @@fetch_status = 0
  begin
    set @string = 'ALTER TABLE ' + @table_name + ' DISABLE TRIGGER ALL'
    exec (@string)
    Fetch next from cur
    into @table_name
  end
  close cur
  deallocate cur
GO

create procedure p_enable_all_triggers
AS
  declare @string     varchar(8000)
  declare @table_name nvarchar(500)
  declare cur cursor
  for select
        name as tbname
      from sysobjects
      where id in (select
                     parent_obj
                   from sysobjects
                   where xtype = 'TR')
  open cur
  fetch next from cur
  into @table_name
  while @@fetch_status = 0
  begin
    set @string = 'ALTER TABLE ' + @table_name + ' ENABLE TRIGGER ALL'
    exec (@string)
    Fetch next from cur
    into @table_name
  end
  close cur
  deallocate cur
GO

create table ekpversion (
  dblevel          int          not null,
  splevel          int          not null,
  migrationlevel   varchar(512) null,
  activation_entry varchar(512) null
)
GO

insert into ekpversion (dblevel, splevel, migrationlevel, activation_entry) values
(93, 0, 'BASE;60_1;60_2;60_3;60_4;61_1;61_3;61_4;62_1;62_3;63_1;63_3;63_4;63_7;70_1;70_2;70_3;70_4;71_1;71_3;71_4;72_1;80_2;82_1;83_190_1;90_2;90_4;90_5;90_6;91_1;91_2;91_3;91_4;91_5;91_6;91_7;91_8;91_9;91_10;91_11;91_12;91_13;91_14;92_1;92_3;92_4;92_5;93_1;93_2;', '')
GO

create table widget_page_template (
  id                   nvarchar(15)  not null,
  template_name        nvarchar(200) not null,
  column_type          nvarchar(50)  null,
  total_rows           int           not null default 1,
  creator_user_id      nvarchar(85)  not null,
  created_date         datetime      not null,
  last_updated_user_id nvarchar(85)  null,
  last_updated         datetime      null,
  constraint PK_PAGE_TEMPLATE primary key (id)
)
GO

create unique index UK_WIDGET_PAGE_TEMPLATE_1 on widget_page_template (template_name)
GO

create table template_widget_layout (
  id           nvarchar(15)  not null,
  column_index int           not null,
  widget_id    nvarchar(200) not null,
  widget_index int           not null,
  template_id  nvarchar(15)  not null
)
GO

create table template_widget_layout_row (
  id          nvarchar(15) not null,
  template_id nvarchar(15) not null,
  row_index   int          not null,
  column_type nvarchar(50) not null,
  constraint PK_TEMPLATE_WIDGET_ROW primary key (id),
  constraint FK_WIDGET_TEMPLATE_ID foreign key (template_id) references widget_page_template (id)
)
GO

create table template_widget_layout_column (
  id           nvarchar(15) not null,
  row_id       nvarchar(15) not null,
  column_index int          not null,
  constraint PK_TEMPLATE_WIDGET_COL primary key (id),
  constraint FK_TEMPLATE_ROW_ID foreign key (row_id) references template_widget_layout_row (id)
)
GO

create table template_widget (
  id           nvarchar(15)  not null,
  column_id    nvarchar(15)  not null,
  widget_id    nvarchar(200) not null,
  widget_index int           not null,
  constraint PK_TEMPLATE_WIDGET primary key (id),
  constraint FK_TEMPLATE_WIDGET_COL foreign key (column_id) references template_widget_layout_column (id)
)
GO

INSERT INTO widget_page_template (id, template_name, total_rows, creator_user_id, created_date, last_updated_user_id, last_updated)
  VALUES ('DEFAULTTEMPLATE', 'Default', 1, 'ndadmin', CURRENT_TIMESTAMP, null, null)
GO

INSERT INTO template_widget_layout_row (id, template_id, row_index, column_type)
  VALUES ('DEFAULTWIDGETR1', 'DEFAULTTEMPLATE', 0, 'TWO_COLUMN_66_33')
GO

INSERT INTO template_widget_layout_column (id, row_id, column_index)
  VALUES ('DEFAULTWIDGETC1', 'DEFAULTWIDGETR1', 0)
GO

INSERT INTO template_widget_layout_column (id, row_id, column_index)
  VALUES ('DEFAULTWIDGETC2', 'DEFAULTWIDGETR1', 1)
GO

INSERT INTO template_widget (id, column_id, widget_id, widget_index)
  VALUES ('DEFAULTWIDGET11', 'DEFAULTWIDGETC1', 'RECENTACTIVITY', 0)
GO

INSERT INTO template_widget (id, column_id, widget_id, widget_index)
  VALUES ('DEFAULTWIDGET21', 'DEFAULTWIDGETC2', 'NEWSARTICLE', 0)
GO

INSERT INTO template_widget (id, column_id, widget_id, widget_index)
  VALUES ('DEFAULTWIDGET22', 'DEFAULTWIDGETC2', 'ENROLLMENTSLIST', 1)
GO

INSERT INTO template_widget (id, column_id, widget_id, widget_index)
  VALUES ('DEFAULTWIDGET23', 'DEFAULTWIDGETC2', 'PERSONALCALENDAR', 2)
GO

INSERT INTO template_widget (id, column_id, widget_id, widget_index)
  VALUES ('DEFAULTWIDGET24', 'DEFAULTWIDGETC2', 'NEWSPOLL', 3)
GO

INSERT INTO template_widget (id, column_id, widget_id, widget_index)
  VALUES ('DEFAULTWIDGET25', 'DEFAULTWIDGETC2', 'CATALOGSEARCH', 4)
GO

create table code_role (
  roleid            nvarchar(85)       not null,
  description       nvarchar(85)       not null,
  homepage_template nvarchar(15)       null,
  title_id_format   smallint default 0 not null,
  constraint PK_CODE_ROLE primary key (roleid),
  constraint FK_CODE_ROLE_HP_TMPL foreign key (homepage_template) references widget_page_template (id)
)
GO

insert into code_role (roleid, description) values ('G', 'label.code_role.guest')
GO

insert into code_role (roleid, description) values ('S', 'label.code_role.learner')
GO

insert into code_role (roleid, description) values ('I', 'label.code_role.instructor')
GO

insert into code_role (roleid, description) values ('D', 'label.code_role.da')
GO

insert into code_role (roleid, description) values ('M', 'label.code_role.manager')
GO

insert into code_role (roleid, description) values ('B', 'label.code_role.business.admin')
GO

insert into code_role (roleid, description) values ('A', 'label.code_role.system.admin')
GO

create table code_orgstructure (
  orgid                    nvarchar(15)                   not null,
  parentid                 nvarchar(15)                   null,
  orgcode                  nvarchar(85)                   not null,
  description              nvarchar(85)                   not null,
  managername_enabled      char(1)                        null,
  manageremail_enabled     char(1)                        null,
  costcenter_enabled       char(1)                        null,
  locationcode_enabled     char(1)                        null,
  profiling_enabled        char(1) default 'N'            null,
  accountcustomer_enabled  char(1) default 'N'            not null,
  allow_user_join          char(1) default 'Y'            not null,
  ext_desc                 nvarchar(1000) default ' '     not null,
  paymentplanid            nvarchar(15)                   null,
  admin_userid             nvarchar(85)                   null,
  welcome_email_profile_id nvarchar(15)                   null,
  creator_uid              nvarchar(85)                   null,
  datecreated              datetime                       null,
  default_approver_id      nvarchar(85)                   null,
  join_req_email_id        nvarchar(85)                   null,
  join_req_denied_email_id nvarchar(85)                   null,
  leave_req_email_id       nvarchar(85)                   null,
  leave_req_ok_email_id    nvarchar(85)                   null,
  leave_req_denied_id      nvarchar(85)                   null,
  manager_userid           nvarchar(85)                   null,
  report_dist1_userid      nvarchar(85)                   null,
  report_dist2_userid      nvarchar(85)                   null,
  report_dist3_userid      nvarchar(85)                   null,
  token_account_id         nvarchar(85)                   null,
  org_feedback_email       nvarchar(85)                   null,
  ew_id                    nvarchar(15)                   null,
  limit_det_user_rev       char(1) default 'R'            not null,
  limit_reviewer_option    char(1) default 'A'            null,
  limit_da_option          char(1) default 'A'            null,
  domain_id                nvarchar(15) default 'DEFAULT' not null,
  new_password_profile_id  nvarchar(15)                   null,
  background_picture       nvarchar(255)                  null,
  imprint                  ntext                          null,
  logoff_url               nvarchar(200)                  null,
  constraint PK_CODE_ORGSTRUCTURE primary key (orgid)
)
GO

create index NUK_ORG_NAME on code_orgstructure (ext_desc)
GO

create index nuk_orgstructure on code_orgstructure (parentid)
GO

create table screenLayout (
  ui                int                             not null,
  shortname         nvarchar(50)                    not null,
  directory         varchar(100)                    not null,
  description       nvarchar(255)                   not null,
  iconset           int                             not null,
  language          int                             not null,
  layout            int                             not null,
  topMenu           int                             not null,
  topContent        int                             not null,
  topHeight         nvarchar(5)                     not null,
  topStylesheet     nvarchar(20)                    not null,
  leftMenu          int                             not null,
  leftContent       int                             not null,
  leftWidth         nvarchar(5)                     not null,
  leftStylesheet    nvarchar(20)                    not null,
  mainMenu          int                             not null,
  mainContent       int                             not null,
  mainStylesheet    nvarchar(20)                    not null,
  bottomMenu        int                             not null,
  bottomContent     int                             not null,
  bottomHeight      nvarchar(5)                     not null,
  bottomStylesheet  nvarchar(20)                    not null,
  topTX             nvarchar(30)                    null,
  leftTX            nvarchar(30)                    null,
  bottomTX          nvarchar(30)                    null,
  homeTX            nvarchar(30)                    null,
  homepage_template nvarchar(30)                    null,
  showteachbutton   char(1)                         null,
  right_to_left     char(1) default 'N'             not null,
  usetab            char(1) default 'N'             not null,
  dynamicMenu       char(1) default 'N'             not null,
  displaySearchBox  char(1) default 'N'             not null,
  fixed_width       int default 0                   not null,
  frameless         char(1) default 'N'             not null,
  mouseovermenu     char(1) default 'Y'             not null,
  constraint PK_SCREENLAYOUT primary key (ui)
)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless)
  values (0, 'NTS-83', 'NTS-83', 'NTS-83-Skin', 0, 0, 4,
          100, 0, '0', 'top.css',
          100, 0, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          null, null, null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'Y', 0, 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless, mouseovermenu)
  values
  (15, 'NTS-83-RTL', 'NTS-83-RTL', 'NTS-83-Skin', 0, 0, 4, 100, 0, '0', 'top.css', 100, 0, '0', 'left.css',
   100, 0, 'main.css', 100, 0, '0', 'bottom.css', null, null, null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE',
   'Y', 'Y', 'Y', 'N', 'Y', 0, 'Y', 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width)
  values (1, 'NetD Left Menu', 'NetD-Left-Menu', 'Left Menu Skin', 0, 0, 4,
          101, 100, '144', 'top.css',
          101, 7, '200', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          'careertop', 'careerleft', null, 'tabnewsandassignments', null, 'Y', 'N', 'Y', 'N', 'Y', 0)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width)
  values (2, 'NetD Customized Menu', 'NetD-Cust-Menu', 'Skin with Customized Menu', 0, 0, 4,
          101, 100, '144', 'top.css',
          101, 7, '200', 'accordion.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          'careertop', 'configuredsidemenus', null, 'tabnewsandassignments', null, 'Y', 'N', 'Y', 'N', 'N', 0)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width)
  values (3, 'EKP Monsters', 'EKP_Monsters', 'EKP Monsters', 0, 0, 4,
          101, 100, '140', 'top.css',
          101, 7, '200', 'left.css',
          100, 0, 'main.css',
          100, 0, '50', 'bottom.css', 'texttop', 'careerleft', null, 'tabhometx', null, 'Y', 'N', 'Y', 'N', 'N', 0)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, showteachbutton,
                          right_to_left, usetab, dynamicMenu, fixed_width)
  values (4, 'NetD Tabs', 'NetD-Tabs', 'NetD Default Tabs', 0, 0, 5,
          101, 100, '114', 'top.css',
          101, 7, '177', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css', 'TEXTTOP', 'CAREERLEFT', null, 'TABHOMETX', 'Y', 'N', 'Y', 'N', 0)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, showteachbutton,
                          right_to_left, usetab, dynamicMenu, fixed_width)
  values (5, 'NetD Sky', 'NetD-Sky', 'NetD Sky', 0, 0, 4,
          101, 100, '187', 'top.css',
          101, 7, '44', 'left.css',
          100, 0, 'main.css',
          100, 0, '50', 'bottom.css', 'HORIZONTALTOP', 'VERTICALLEFT', null, 'MYHOMEDEFAULT', 'Y', 'N', 'N', 'N', 0)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width)
  values (6, 'EKP70-LeftMenu', 'EKP70-LeftMenu', 'EKP 7.0 Left Menu Skin', 0, 0, 4,
          101, 100, '139', 'top.css',
          101, 7, '200', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          'careertop', 'careerleft', '', 'tabnewsandassignments', '', 'Y', 'N', 'Y', 'N', 'Y', 0)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width)
  values (7, 'EKP70-TopNav', 'EKP70-TopNav', 'EKP 7.0 Top Navigation Skin', 0, 0, 4,
          101, 100, '145', 'top.css',
          100, 7, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '50', 'bottom.css',
          'careertop', '(none)', '', 'widgetcontainerpage', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'N', 0)
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless)
  values (8, 'EKP71-Frameless', 'EKP71-Frameless', 'EKP7.1 Frameless Skin', 0, 0, 4,
          101, 100, '145', 'top.css',
          100, 7, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '50', 'bottom.css',
          'careertop', '(none)', null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'Y', 0, 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless)
  values (9, 'EKP72-TopNav', 'EKP72-TopNav', 'EKP72-TopNav', 0, 0, 4,
          100, 0, '0', 'top.css',
          100, 0, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          null, null, null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'Y', 0, 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless)
  values (10, 'EKP73', 'EKP73', 'EKP73 Top Navigation Skin', 0, 0, 4,
          100, 0, '0', 'top.css',
          100, 0, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          null, null, null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'Y', 0, 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless)
  values (11, 'NTS-80', 'NTS-80', 'NTS-80', 0, 0, 4,
          100, 0, '0', 'top.css',
          100, 0, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          null, null, null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'Y', 0, 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless)
  values (12, 'NTS-81-Tab', 'NTS-81-Tab', 'NTS-81-Tab', 0, 0, 4,
          100, 0, '0', 'top.css',
          100, 0, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          null, null, null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'Y', 0, 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width, frameless)
  values (13, 'NTS-81-DropDown', 'NTS-81-DropDown', 'NTS-81-DropDown', 0, 0, 4,
          100, 0, '0', 'top.css',
          100, 0, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '0', 'bottom.css',
          null, null, null, 'WIDGETCONTAINERPAGE', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'Y', 0, 'Y')
GO

insert into screenLayout (ui, shortname, directory, description, iconset, language, layout, topMenu, topContent,
                          topHeight, topStylesheet, leftMenu, leftContent, leftWidth, leftStylesheet, mainMenu, mainContent, mainStylesheet,
                          bottomMenu, bottomContent, bottomHeight, bottomStylesheet, topTX, leftTX, bottomTX, homeTX, homepage_template,
                          showteachbutton, right_to_left, usetab, dynamicMenu, displaySearchBox, fixed_width)
  values (14, 'NetD Top Menu', 'NetD-Top-Menu', 'Top Menu Skin', 0, 0, 4,
          101, 100, '145', 'top.css',
          100, 7, '0', 'left.css',
          100, 0, 'main.css',
          100, 0, '50', 'bottom.css',
          'careertop', '(none)', null, 'widgetcontainerpage', 'DEFAULTTEMPLATE', 'Y', 'N', 'Y', 'N', 'N', 0)
GO

create table configDefaults (
  parmid             smallint     not null,
  defaultCatalogView int          not null,
  defaultLanguage    smallint     not null,
  defaultURL         int          not null,
  defaultStyle       nvarchar(15) not null,
  defaultUI          int          not null,
  eLearningURL       varchar(100) null,
  libraryURL         varchar(100) null,
  siteid             nvarchar(3)  not null,
  feedbackMail       varchar(100) null,
  logoffURL          varchar(200) null,
  vcserverURL        varchar(100) null,
  graphic1URL        varchar(100) null,
  graphic2URL        varchar(100) null,
  systemuser         nvarchar(85) null,
  systemapprover     nvarchar(85) null,
  vcLauncher         varchar(100) null,
  optionalLink1Label varchar(100) null,
  optionalLink1Url   varchar(100) null,
  optionalLink2Label varchar(100) null,
  optionalLink2Url   varchar(100) null,
  defaultUserRole    nvarchar(85) null,
  system_opml_user   nvarchar(85) null,
  ew_id              nvarchar(15) null,
  constraint PK_CONFIGDEFAULTS primary key (parmid),
  CONSTRAINT FK_CONFIGDEFAULTS_DEFAULTUI FOREIGN KEY (defaultUI) REFERENCES screenLayout (ui),
  CONSTRAINT FK_CONFIGDEFAULTS_DEFAULTROLE FOREIGN KEY (defaultUserRole) references code_role (roleid)
)
GO

insert into configDefaults (parmid, defaultCatalogView, defaultLanguage, defaultURL, defaultStyle, defaultUI, eLearningURL, libraryURL,
                            siteid, feedbackMail, logoffURL, vcserverURL, graphic1URL, graphic2URL, systemuser, systemapprover, vcLauncher)
  values (1, 254, 1, 0, 'default', 0, null, null, 'EKP', 'ndadmin@netdimensions.com', '', null, null, null, 'ndadmin', 'ndadmin', null)
GO

create table logical_domain (
  domain_id                    nvarchar(15)        not null,
  domain_name                  nvarchar(85)        not null,
  site_id                      nvarchar(3)         not null,
  comments                     nvarchar(255)       null,
  default_catalog_view         int                 not null,
  default_language             int                 not null,
  default_initial_page         int                 not null,
  default_style                nvarchar(15)        not null,
  default_ui                   int                 not null,
  default_user_role            nvarchar(85)        null,
  default_user_profile         nvarchar(85)        null,
  system_userid                nvarchar(85)        null,
  system_approverid            nvarchar(85)        null,
  feedback_mail                nvarchar(100)       null,
  logoff_url                   nvarchar(200)       null,
  system_opml_userid           nvarchar(85)        null,
  vc_server_url                nvarchar(100)       null,
  vc_launcher                  nvarchar(100)       null,
  ew_id                        nvarchar(15)        null,
  access_code                  nvarchar(85)        null,
  option1                      nvarchar(100)       null,
  option2                      nvarchar(100)       null,
  option3                      nvarchar(100)       null,
  option4                      nvarchar(100)       null,
  creator_uid                  nvarchar(85)        null,
  datecreated                  datetime            null,
  last_updated                 datetime            null,
  last_updater_uid             nvarchar(85)        null,
  disable_userrec_partitioning char(1) default 'N' not null,
  disable_content_partitioning char(1) default 'N' not null,
  constraint PK_LDOMAIN primary key (domain_id),
  CONSTRAINT FK_LDOMAIN_DEFAULTUI FOREIGN KEY (default_ui) REFERENCES screenLayout (ui),
  CONSTRAINT FK_LDOMAIN_DEFAULTROLE FOREIGN KEY (default_user_role) references code_role (roleid)
)
GO

INSERT INTO logical_domain (domain_id, domain_name, site_id, comments, feedback_mail,
                            default_catalog_view, default_language, default_initial_page, default_style, default_ui,
                            system_userid, system_approverid, system_opml_userid, vc_server_url, vc_launcher,
                            ew_id, datecreated, creator_uid)
  SELECT
    'DEFAULT',
    'Global Default',
    siteid,
    'The default logical domain.',
    feedbackMail,
    defaultCatalogView,
    defaultLanguage,
    defaultURL,
    defaultStyle,
    defaultUI,
    systemuser,
    systemapprover,
    system_opml_user,
    vcserverURL,
    vcLauncher,
    ew_id,
    getdate(),
    'ndadmin'
  FROM configDefaults
GO

create table logical_domain_criteria (
  record_key       nvarchar(15) not null,
  domain_id        nvarchar(15) not null,
  targetType       nvarchar(10) not null,
  targetValue      nvarchar(85) not null,
  exclusion_flag   char(1)      null,
  last_updated     datetime     null,
  last_updater_uid nvarchar(85) null,
  constraint PK_LDOMAIN_PK primary key (record_key),
  constraint FK_DOMAINID foreign key (domain_id) references logical_domain (domain_id)
)
GO

create index NUK_LDOMAIN_DOMAINID on logical_domain_criteria (domain_id)
GO

create table users (
  userid                         nvarchar(85)                    not null,
  password                       nvarchar(255)                   not null,
  currentstatus                  smallint                        not null,
  currentPID                     NVARCHAR(85)                    NOT NULL,
  familyname                     nvarchar(85)                    not null,
  givenname                      nvarchar(85)                    not null,
  othername                      nvarchar(85)                    null,
  personaltitle                  nvarchar(4)                     null,
  email                          nvarchar(85)                    null,
  dappraiser                     NVARCHAR(85)                    NULL,
  level1id                       nvarchar(15)                    not null,
  level2id                       nvarchar(15)                    not null,
  level3id                       nvarchar(15)                    not null,
  level4id                       nvarchar(15)                    null,
  level5id                       nvarchar(15)                    null,
  employeeno                     nvarchar(85)                    null,
  costcenter                     nvarchar(45)                    null,
  joindate                       datetime                        null,
  userattr1                      nvarchar(1000)                  null,
  userattr2                      nvarchar(1000)                  null,
  userattr3                      nvarchar(1000)                  null,
  userattr4                      nvarchar(1000)                  null,
  userrole                       nvarchar(85)                    not null,
  datecreated                    datetime                        not null,
  jobtitle                       nvarchar(85)                    null,
  externalAuthentication         nvarchar(1)                     not null,
  autoDelete                     nvarchar(1)                     not null,
  deleteDate                     datetime                        null,
  UserOption1                    varchar(100)                    null,
  UserOption2                    varchar(100)                    null,
  UserOption3                    varchar(100)                    null,
  profile                        nvarchar(1) default 'N'         null,
  managername                    nvarchar(85)                    null,
  manageremail                   nvarchar(85)                    null,
  departmentname                 nvarchar(85)                    null,
  locationcode                   nvarchar(85)                    null,
  dateofbirth                    datetime                        null,
  gender                         char(1)                         null,
  hrmanagername                  nvarchar(85)                    null,
  hrmanageremail                 nvarchar(85)                    null,
  expirationdate                 datetime                        null,
  nationality                    nvarchar(85)                    null,
  dietary_constraints            nvarchar(85)                    null,
  line_of_business               char(1)                         null,
  skills_assessment_enabled      char(1) default 'N'             not null,
  userattr5                      nvarchar(1000)                  null,
  userattr6                      nvarchar(1000)                  null,
  userattr7                      nvarchar(1000)                  null,
  userattr8                      nvarchar(1000)                  null,
  leaf_orgid                     nvarchar(15)                    null,
  creditbalance                  float default 0                 not null,
  hr_tag                         char(1)                         null,
  departmentid                   nvarchar(85)                    null,
  middlename                     nvarchar(85)                    null,
  costcentername                 nvarchar(85)                    null,
  creator_uid                    nvarchar(85)                    null,
  last_updated                   datetime                        null,
  last_updater_uid               nvarchar(85)                    null,
  offline_player_eval_days       INT                             NULL,
  offline_player_eval_start_date DATETIME                        NULL,
  skype_name                     NVARCHAR(85) default ''         NOT NULL,
  token_account_id               nvarchar(85)                    null,
  date_closed                    datetime                        null,
  domain_id                      nvarchar(15) default 'DEFAULT'  not null,
  constraint PK_USERS primary key (userid),
  constraint fk_users_userrole foreign key (userrole) references code_role (roleid),
  constraint ak_currentpid_users UNIQUE (currentPID),
  constraint FK_COLUMN_REF_CODE_ORGSTRUCT foreign key (leaf_orgid) references code_orgstructure (orgid),
  CONSTRAINT FK_USERS_DOMAIN_ID FOREIGN KEY (domain_id) references logical_domain (domain_id)
)
GO

create table user_additional_roles (
  userid nvarchar(85) not null,
  roleid nvarchar(85) not null,
  constraint PK_USER_ADD_ROLES primary key (userid, roleid),
  constraint FK_USER_ADD_ROLES_USERID foreign key (userid) references users (userid),
  constraint FK_USER_ADD_ROLES_ROLEID foreign key (roleid) references code_role (roleid)
)
GO

create index NUK_USER_ORGID on users (leaf_orgid)
GO

create index NUK_USERS_DA on users (dappraiser)
GO

create index NUK_USERS_L123 on users (level1id, level2id, level3id)
GO

create index NUK_USERS_L3 on users (level3id)
GO

create index nuk_users_datecreated on users (datecreated)
GO

IF EXISTS(SELECT
            TABLE_NAME
          FROM INFORMATION_SCHEMA.VIEWS
          WHERE TABLE_NAME = 'valid_users_vw')
  DROP VIEW valid_users_vw
GO

CREATE VIEW valid_users_vw AS
  SELECT
    *
  FROM users
  WHERE currentstatus NOT IN (3, 5, 6)
GO

create table associated_orgs (
  associationid      nvarchar(85) not null,
  userid             nvarchar(85) not null,
  orgid              nvarchar(15) not null,
  association_status int          not null,
  constraint PK_associated_orgs primary key (associationid),
  constraint FK_ASSOCIATED_ORGS_F1 foreign key (userid) references users (userid),
  constraint FK_ASSOCIATED_ORGS_F2 foreign key (orgid) references code_orgstructure (orgid)
)
GO

create table code_userstatus (
  userstatus  smallint     not null,
  description nvarchar(85) not null,
  constraint PK_CODE_USERSTATUS primary key (userstatus)
)
GO

create table code_studentstatus (
  studentstatus smallint     not null,
  description   nvarchar(50) not null,
  constraint PK_CODE_STUDENTSTATUS primary key (studentstatus)
)
GO

create table report_wizard_outer_join (
  report_id      nvarchar(15)  not null,
  optional_table nvarchar(255) not null,
  constraint PK_RW_OUTER_JOIN primary key (report_id, optional_table))
GO

create table layoutMenuItems (
  ui                int           not null,
  listorder         int           not null,
  urlType           varchar(1)    not null,
  url               nvarchar(255) not null,
  labelResourceKey  nvarchar(255) not null,
  altTagResourceKey nvarchar(255) null,
  constraint PK_LAYOUTMENU primary key (ui, listorder)
)
GO

create table rssFeedmap (
  learningid nvarchar(85) not null,
  listorder  int          not null,
  rssFeedId  nvarchar(100),
  constraint PK_RSSFEEDMAP primary key (learningid, listorder)
)
GO

create table paymentplan (
  paymentplanid            nvarchar(15)        not null,
  title                    nvarchar(85)        not null,
  prepaidexpirationdate    datetime            null,
  prepaid                  char(1)             not null,
  applytoallcourses        char(1)             not null,
  onlinepaymentonly        char(1)             not null,
  allowpaybycreditcard     char(1) default 'N' not null,
  allowpaybycompanyaccount char(1) default 'N' not null,
  creditcard_ref_mand      char(1) default 'N' not null,
  companyaccount_ref_mand  char(1) default 'N' not null,
  datediscount             float               not null,
  discountexpirationdate   datetime            null,
  threshholddiscount       float               not null,
  enrollthreshhold         int                 not null,
  creditbalance            float default 0     not null,
  constraint PK_PAYMENTPLAN primary key (paymentplanid)
)
GO

create table appraisal (
  userid            nvarchar(85) not null,
  effYear           char(4)      not null,
  dateEntered       datetime     not null,
  lastUpdated       datetime     null,
  reviewer          nvarchar(85) null,
  appStatus         int          not null,
  otherAchievements ntext        null,
  complianceAtt     ntext        null,
  rating            int          not null,
  userAccepted      char(1)      not null,
  mgrAccepted       char(1)      not null,
  dialogue          char(1)      not null,
  constraint PK_APPRAISAL primary key (userid, effYear)
)
GO

create table appraisalObjectives (
  userid            nvarchar(85) not null,
  effYear           char(4)      not null,
  objectType        char(1)      not null,
  lineNumber        int          not null,
  area              ntext        null,
  response          ntext        null,
  appraisal_comment ntext        null,
  dueBy             datetime     null,
  met               char(1)      not null,
  constraint PK_APPRAISAL_OBJ primary key (userid, effYear, objectType, lineNumber)
)
GO

create table ContentItem (
  contentID                  nvarchar(15)  not null,
  itemID                     nvarchar(15)  not null,
  launchData                 ntext         null,
  masteryScore               ntext         null,
  maxTimeAllowed             nvarchar(13)  null,
  parameters                 ntext         null,
  timeLimitAction            char(1)       null,
  requires_cost_center_email char(1)       null,
  completionThreshold        nvarchar(20)  null,
  update_meaning             nvarchar(8)   null,
  last_updated               datetime      null,
  updater_legal_name         nvarchar(250) null,
  update_meaning_text        ntext         null,
  last_updater_uid           nvarchar(85)  null,
  constraint PK_CONTENTITEM primary key (itemID)
)
GO

create table AICC_Core_Vendor (
  courseid            nvarchar(85)  not null,
  core_vendor         ntext         null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_AICC_CORE_VENDOR primary key (courseid)
)
GO

create table AICC_au (
  courseid            nvarchar(85)  not null,
  systemid            nvarchar(40)  null,
  itemtype            nvarchar(40)  null,
  commandline         varchar(255)  null,
  filename            varchar(255)  null,
  maxscore            float         null,
  masteryscore        int           null,
  maxtimeallowed      nvarchar(8)   null,
  timelimitaction     nvarchar(20)  null,
  systemvendor        varchar(255)  null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_AICC_AU primary key (courseid)
)
GO

create table AICC_comments (
  session_id nvarchar(15) not null,
  comments   ntext        null,
  constraint PK_AICC_COMMENTS primary key (session_id)
)
GO

create table AICC_core (
  session_id            nvarchar(15)              not null,
  userid                nvarchar(85)              not null,
  student_id            nvarchar(85)              not null,
  courseid              nvarchar(85)              null,
  lesson_status         nvarchar(3)               null,
  lesson_location       nvarchar(255)             null,
  score1                nvarchar(20)              null,
  time1                 nvarchar(14)              null,
  lesson_mode           nvarchar(40)              null,
  core_lesson           ntext                     null,
  totalnAttempts        int                       null,
  totalSeconds          int                       null,
  lastAttemptDate       datetime                  null,
  highestscore          nvarchar(20) default 'NA' null,
  final_status          nvarchar(1)               null,
  finish_date           datetime                  null,
  entry                 char(1) default 'a'       not null,
  itemID                nvarchar(15)              null,
  totalTime             nvarchar(14)              null,
  totalTimeAtLastLaunch nvarchar(14)              null,
  testInstanceID        nvarchar(15)              null,
  license_time_seconds  int default '0'           null,
  license_trip_date     datetime                  null,
  progressMeasure       nvarchar(20)              null,
  scoreScaled           nvarchar(20)              null,
  completionStatus      char(1) default 'u'       not null,
  successStatus         char(1) default 'u'       not null,
  skipped               char(1) default 'N'       not null,
  currentClusterAttempt char(1) default 'N'       not null,
  constraint PK_AICC_CORE primary key (session_id),
  constraint FK_COLUMN_REF_USER foreign key (userid) references users (userid),
  constraint fk_aicc_core_item_id foreign key (itemID) references ContentItem (itemID)
)
GO

create index NUK_AICC_CORE_UID on AICC_core (userid)
GO

create index NUK_AICC_CORE_STD_ID on AICC_core (student_id)
GO

create table AICC_core_detail (
  session_id  nvarchar(15) not null,
  launch_date datetime     not null,
  constraint PK_AICC_CORE_DETAIL primary key (session_id, launch_date)
)
GO

create table AICC_des (
  id                  nvarchar(15)   not null,
  courseid            nvarchar(85)   not null,
  systemid            nvarchar(40)   null,
  developerid         nvarchar(40)   null,
  title               nvarchar(1000) null,
  description         nvarchar(2000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_AICC_DES primary key (id)
)
GO

create index NUK_AICC_DES_1 on AICC_des (courseid)
GO

create table AICC_interactions (
  id               nvarchar(15)   not null,
  session_id       nvarchar(15)   not null,
  l_date           nvarchar(12)   null,
  l_time           nvarchar(12)   null,
  interaction_id   nvarchar(255)  not null,
  objective_id     nvarchar(255)  null,
  type_interaction nvarchar(15)   null,
  correct_response nvarchar(1000) null,
  response_value   nvarchar(510)  null,
  student_response nvarchar(510)  null,
  result           nvarchar(100)  null,
  weighting        nvarchar(100)  null,
  latency          nvarchar(100)  null,
  constraint PK_AICC_INTERACTIONS primary key (id)
)
GO

create index NUK_AICCINT on AICC_interactions (session_id, interaction_id)
GO

create table AICC_launch_options (
  courseid             nvarchar(85)  not null,
  comments             nvarchar(1)   null,
  evaluation           nvarchar(1)   null,
  objective_status     nvarchar(1)   null,
  student_data         nvarchar(1)   null,
  student_data_full    nvarchar(1)   null,
  student_demographics nvarchar(1)   null,
  student_preferences  nvarchar(1)   null,
  update_meaning       nvarchar(8)   null,
  last_updated         datetime      null,
  updater_legal_name   nvarchar(250) null,
  update_meaning_text  ntext         null,
  last_updater_uid     nvarchar(85)  null,
  constraint PK_AICC_LAUNCH_OPTIONS primary key (courseid)
)
GO

create table AICC_objectives_status (
  session_id nvarchar(15)  not null,
  j_id       nvarchar(255) not null,
  j_status   nvarchar(13)  null,
  j_score    nvarchar(20)  null,
  constraint PK_AICC_objectives_status primary key (session_id, j_id)
)
GO

create table AICC_student_data (
  id             nvarchar(15) not null,
  session_id     nvarchar(15) not null,
  attempt_number int          null,
  tries          int          null,
  status         nvarchar(15) null,
  score          nvarchar(20) null,
  time           nvarchar(12) null,
  constraint PK_AICC_STUDENT_DATA primary key (id)
)
GO

create index NUK_AICC_STUDENT on AICC_student_data (session_id)
GO

create table AICC_student_preferences (
  session_id    nvarchar(15)  not null,
  audio         int           null,
  language      nvarchar(255) null,
  lesson_type   nvarchar(10)  null,
  speed         int           null,
  text          int           null,
  text_color    nvarchar(30)  null,
  text_location nvarchar(20)  null,
  text_size     nvarchar(20)  null,
  video         nvarchar(15)  null,
  constraint PK_AICC_STUDENT_PREFERENCES primary key (session_id)
)
GO

create table AICC_student_preferences_1 (
  session_id  nvarchar(15) not null,
  extension   int          not null,
  window_desc nvarchar(20) null,
  constraint PK_AICC_STUDENT_PREFERENCES_1 primary key (session_id, extension)
)
GO

create table ContentObject (
  contentID           nvarchar(15)                      not null,
  href                ntext                             not null,
  installDate         datetime                          null,
  ownerID             nvarchar(85)                      null,
  packageID           nvarchar(85)                      null,
  testID              nvarchar(85)                      null,
  type                char(1)                           not null,
  ims_type            nvarchar(85) default 'webcontent' not null,
  update_meaning      nvarchar(8)                       null,
  last_updated        datetime                          null,
  updater_legal_name  nvarchar(250)                     null,
  update_meaning_text ntext                             null,
  last_updater_uid    nvarchar(85)                      null,
  constraint PK_CONTENTOBJECT primary key (contentID)
)
GO

create table Sequencing (
  sequencingID               nvarchar(15)                    not null,
  choice                     char(1) default 'Y'             not null,
  flow                       char(1) default 'N'             not null,
  forwardOnly                char(1) default 'N'             not null,
  choiceExit                 char(1) default 'Y'             not null,
  maxAttempts                int default 0                   not null,
  maxTimeAllowed             nvarchar(30),
  preventChoice              char(1) default 'N'             not null,
  constrainChoice            char(1) default 'N'             not null,
  rollupObjectiveSatisfied   char(1) default 'Y'             not null,
  rollupProgressCompletion   char(1) default 'Y'             not null,
  objectiveMeasureWeight     nvarchar(20),
  tracked                    char(1) default 'Y'             not null,
  completionSetByContent     char(1) default 'N'             not null,
  objectiveSetByContent      char(1) default 'N'             not null,
  rollupSatisfied            char(1) default 'A'             not null,
  rollupNotSatisfied         char(1) default 'A'             not null,
  rollupCompleted            char(1) default 'A'             not null,
  rollupIncomplete           char(1) default 'A'             not null,
  rollupMeasureSatisfied     char(1) default 'Y'             not null,
  randomizeTiming            char(1) default 'N'             not null,
  randomizeChildren          char(1) default 'N'             not null,
  useCurrentAttemptObjective char(1) default 'Y'             not null,
  useCurrentAttemptProgress  char(1) default 'Y'             not null,
  update_meaning             nvarchar(8)                     null,
  last_updated               datetime                        null,
  updater_legal_name         nvarchar(250)                   null,
  update_meaning_text        ntext                           null,
  last_updater_uid           nvarchar(85)                    null,
  constraint PK_SEQUENCING primary key (sequencingID),
  constraint CKC_SEQUENCING_C1 check (choice in ('Y', 'N')),
  constraint CKC_SEQUENCING_C2 check (flow in ('Y', 'N')),
  constraint CKC_SEQUENCING_C3 check (forwardOnly in ('Y', 'N')),
  constraint CKC_SEQUENCING_C4 check (choiceExit in ('Y', 'N')),
  constraint CKC_SEQUENCING_C5 check (maxAttempts >= 0),
  constraint CKC_SEQUENCING_C6 check (preventChoice in ('Y', 'N')),
  constraint CKC_SEQUENCING_C7 check (constrainChoice in ('Y', 'N'))
)
GO

create table SequenceRule (
  sequencingID        nvarchar(15)                   not null,
  ruleIndex           int                            not null,
  type                char(1)                        not null,
  combination         char(1) default 'A'            not null,
  action              char(1) default '-'            not null,
  update_meaning      nvarchar(8)                    null,
  last_updated        datetime                       null,
  updater_legal_name  nvarchar(250)                  null,
  update_meaning_text ntext                          null,
  last_updater_uid    nvarchar(85)                   null,
  constraint PK_SEQUENCERULE primary key (sequencingID, ruleIndex),
  constraint FK_SEQUENCERULE_F1 foreign key (sequencingID)
  references Sequencing (sequencingID),
  constraint CKC_SEQUENCERULE_C1 check (type in ('1', '2', 'X')),
  constraint CKC_SEQUENCERULE_C2 check (combination in ('A', 'O'))
)
GO

create table SeqRuleCondition (
  sequencingID        nvarchar(15)                   not null,
  ruleIndex           int                            not null,
  conditionIndex      int                            not null,
  ruleCondition       char(1)                        not null,
  referencedObjective nvarchar(2000),
  measureThreshold    nvarchar(20),
  operator            char(1) default '-'            not null,
  update_meaning      nvarchar(8)                    null,
  last_updated        datetime                       null,
  updater_legal_name  nvarchar(250)                  null,
  update_meaning_text ntext                          null,
  last_updater_uid    nvarchar(85)                   null,
  constraint PK_SEQRULECONDITION primary key (sequencingID, ruleIndex, conditionIndex),
  constraint FK_SEQRULECONDITION_F1 foreign key (sequencingID)
  references Sequencing (sequencingID),
  constraint CKC_SEQRULECONDITION_C1 check (operator in ('-', 'N'))
)
GO

create table SequenceObjective (
  sequencingID         nvarchar(15)                     not null,
  objectiveIndex       int                              not null,
  objectiveID          nvarchar(2000),
  satisfiedByMeasure   char(1) default 'N'              not null,
  minNormalizedMeasure nvarchar(20),
  isPrimary            char(1) default 'N'              not null,
  update_meaning       nvarchar(8)                      null,
  last_updated         datetime                         null,
  updater_legal_name   nvarchar(250)                    null,
  update_meaning_text  ntext                            null,
  last_updater_uid     nvarchar(85)                     null,
  constraint PK_SEQUENCEOBJECTIVE primary key (sequencingID, objectiveIndex),
  constraint FK_SEQUENCEOBJECTIVE_F1 foreign key (sequencingID)
  references Sequencing (sequencingID),
  constraint CKC_SEQUENCEOBJECTIVE_C1 check (satisfiedByMeasure in ('Y', 'N')),
  constraint CKC_SEQUENCEOBJECTIVE_C2 check (objectiveID is not null or isPrimary = 'Y'),
  constraint CKC_SEQUENCEOBJECTIVE_C3 check (isPrimary in ('Y', 'N'))
)
GO

create table SeqObjectiveMap (
  sequencingID           nvarchar(15)                   not null,
  objectiveIndex         int                            not null,
  mapIndex               int                            not null,
  targetObjectiveID      nvarchar(2000)                 not null,
  readSatisfiedStatus    char(1) default 'Y'            not null,
  readNormalizedMeasure  char(1) default 'Y'            not null,
  writeSatisfiedStatus   char(1) default 'N'            not null,
  writeNormalizedMeasure char(1) default 'N'            not null,
  update_meaning         nvarchar(8)                    null,
  last_updated           datetime                       null,
  updater_legal_name     nvarchar(250)                  null,
  update_meaning_text    ntext                          null,
  last_updater_uid       nvarchar(85)                   null,
  constraint PK_SEQOBJECTIVEMAP primary key (sequencingID, objectiveIndex, mapIndex),
  constraint FK_SEQOBJECTIVEMAP_F1 foreign key (sequencingID)
  references Sequencing (sequencingID),
  constraint CKC_SEQOBJECTIVEMAP_C1 check (readSatisfiedStatus in ('Y', 'N')),
  constraint CKC_SEQOBJECTIVEMAP_C2 check (readNormalizedMeasure in ('Y', 'N')),
  constraint CKC_SEQOBJECTIVEMAP_C3 check (writeSatisfiedStatus in ('Y', 'N')),
  constraint CKC_SEQOBJECTIVEMAP_C4 check (writeNormalizedMeasure in ('Y', 'N'))
)
GO

create table RollupRule (
  sequencingID        nvarchar(15)                   not null,
  ruleIndex           int                            not null,
  childActivitySet    char(1) default 'A'            not null,
  minimumCount        int default 0                  not null,
  minimumPercent      nvarchar(20),
  action              char(1)                        not null,
  conditionCombi      char(1) default 'O'            not null,
  update_meaning      nvarchar(8)                    null,
  last_updated        datetime                       null,
  updater_legal_name  nvarchar(250)                  null,
  update_meaning_text ntext                          null,
  last_updater_uid    nvarchar(85)                   null,
  constraint PK_ROLLUPRULE primary key (sequencingID, ruleIndex),
  constraint FK_ROLLUPRULE_F1 foreign key (sequencingID)
  references Sequencing (sequencingID),
  constraint CKC_ROLLUPRULE_C1 check (childActivitySet in ('A', 'O', 'N', 'C', 'P')),
  constraint CKC_ROLLUPRULE_C2 check (action in ('S', 'N', 'C', 'I')),
  constraint CKC_ROLLUPRULE_C3 check (conditionCombi in ('A', 'O'))
)
GO

create table RollupCondition (
  sequencingID        nvarchar(15)                   not null,
  ruleIndex           int                            not null,
  conditionIndex      int                            not null,
  rollupCondition     char(1)                        not null,
  operator            char(1) default '-'            not null,
  update_meaning      nvarchar(8)                    null,
  last_updated        datetime                       null,
  updater_legal_name  nvarchar(250)                  null,
  update_meaning_text ntext                          null,
  last_updater_uid    nvarchar(85)                   null,
  constraint PK_ROLLUPCONDITION primary key (sequencingID, ruleIndex, conditionIndex),
  constraint FK_ROLLUPCONDITION_F1 foreign key (sequencingID)
  references Sequencing (sequencingID),
  constraint CKC_ROLLUPCONDITION_C1 check (operator in ('-', 'N'))
)
GO

create table course_template (
  id          nvarchar(15) not null,
  name        nvarchar(85) not null,
  dir         nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_SCORM_TEMP_ID primary key (id)
)
GO

create table ContentOrganization (
  installDate              datetime                        not null,
  organizationID           nvarchar(15)                    not null,
  ownerID                  nvarchar(85)                    null,
  packageID                nvarchar(85)                    null,
  title                    ntext                           not null,
  description              ntext                           not null,
  duration                 varchar(100) default ''         null,
  course_id                nvarchar(85)                    null,
  course_template_id       nvarchar(15)                    null,
  is_master                char(1)                         null,
  course_creator           nvarchar(100)                   null,
  sequencingID             nvarchar(15)                    null,
  objectivesGlobalToSystem char(1) default 'Y'             not null,
  update_meaning           nvarchar(8)                     null,
  last_updated             datetime                        null,
  updater_legal_name       nvarchar(250)                   null,
  update_meaning_text      ntext                           null,
  last_updater_uid         nvarchar(85)                    null,
  constraint PK_CONTENTORGANIZATION primary key (organizationID),
  constraint FK_ID_REF_COURSE_TEMP foreign key (course_template_id) references course_template (id)
)
GO

create index NUK_CO_LOID on ContentOrganization (course_id)
GO

create table ContentOrganizationKeyword (
  organizationId nvarchar(15) not null,
  keywordIndex   int          not null,
  keywordText    ntext        null,
  constraint PK_CONTENTORGANIZATIONKEYWORD primary key (organizationId, keywordIndex)
)
GO

create table ContentOrganizationNode (
  isVisible           nvarchar(1)    NOT null,
  itemID              nvarchar(15)   null,
  nodeID              nvarchar(2000) not null,
  nodeIndex           int            not null,
  organizationID      nvarchar(15)   not null,
  parentID            nvarchar(85)   null,
  prerequisites       ntext          null,
  title               ntext          not null,
  hideNavUi           nvarchar(10)   null,
  sequencingID        nvarchar(15)   null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_CONTENTORGANIZATIONNODE primary key (organizationID, nodeID)
)
GO

create table ContentOrganizationObjective (
  organizationID      nvarchar(15)  not null,
  objectiveIndex      int           not null,
  description         ntext         not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_CONTENTORGANIZATIONOBJECTIV primary key (organizationID, objectiveIndex)
)
GO

create table ContentPackage (
  filename            ntext         null,
  installDate         datetime      not null,
  ownerID             nvarchar(85)  null,
  packageID           nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_CONTENTPACKAGE primary key (packageID)
)
GO

create table TranscriptNode (
  contentSessionID nvarchar(15) not null,
  nodeID           varchar(255) not null,
  transcriptID     nvarchar(15) not null,
  constraint PK_TRANSCRIPTNODE primary key (transcriptID, nodeID)
)
GO

create index nuk_transcriptnode on TranscriptNode (contentSessionID)
GO

create table ClusterNode (
  transcriptID   nvarchar(15)          not null,
  nodeID         varchar(255)          not null,
  totalnAttempts int default 0         not null,
  constraint PK_CLUSTERNODE primary key (transcriptID, nodeID)
)
GO

create table accessAuditTrail (
  dateentered datetime      not null,
  userid      nvarchar(85)  not null,
  problem     int           not null,
  sourceHost  nvarchar(85)  null,
  sourceAddr  nvarchar(85)  null,
  comments    nvarchar(255) null
)
GO

create index nuk_accessAuditTrail on accessAuditTrail (dateentered)
GO

create table accounts (
  transactionid    nvarchar(15)                  not null,
  userid           nvarchar(85)                  not null,
  entrydate        datetime                      not null,
  courseid         nvarchar(85)                  not null,
  transcriptid     nvarchar(15)                  not null,
  currency         nvarchar(15)                  not null,
  debit            numeric(15, 2)                not null,
  credit           numeric(15, 2)                not null,
  description      nvarchar(1000)                not null,
  creator          nvarchar(85) default 'System' not null,
  payment_id       int                           null,
  addchargeamount  numeric(15, 2) default 0      not null,
  priceinfo        nvarchar(300)                 null,
  charge_type      char(1) default 'C'           not null,
  last_updater_uid nvarchar(85)                  null,
  last_updated     datetime                      null,
  constraint PK_ACCOUNTS primary key (transactionid)
)
GO

create index NUK_ACCT_TRAN on accounts (transcriptid)
GO

create index NUK_ACCT_USER on accounts (userid)
GO

create index NUK_ACCT_ENTRYDATE on accounts (entrydate)
GO

create table approval_queue (
  transcriptid        nvarchar(15)   not null,
  request_date        datetime       not null,
  user_remarks        nvarchar(4000) null,
  policy_id           nvarchar(15)   not null,
  learner_orgid       nvarchar(15)   not null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_APPROVAL_QUEUE primary key (transcriptid)
)
GO

create table approval_queue_step (
  approval_step_id        nvarchar(15)  not null,
  policy_id               nvarchar(15)  not null,
  policy_step_id          nvarchar(15)  not null,
  sequence_no             int           not null,
  step_status             char(1)       not null,
  transcriptid            nvarchar(15)  not null,
  approver_id             nvarchar(85)  null,
  external_approver_email nvarchar(85)  null,
  approver_remarks        nvarchar(255) null,
  approval_date           datetime      null,
  remind_date             datetime      null,
  expire_date             datetime      null,
  remind_sent_date        datetime      null,
  expire_sent_date        datetime      null,
  charged_costcenter      nvarchar(45)  null,
  charged_email           nvarchar(85)  null,
  update_meaning          nvarchar(8)   null,
  last_updated            datetime      null,
  updater_legal_name      nvarchar(250) null,
  update_meaning_text     ntext         null,
  last_updater_uid        nvarchar(85)  null,
  constraint PK_APPROVAL_QUEUE_STEP primary key (approval_step_id)
)
GO

create index idx_approver_id on approval_queue_step (approver_id)
GO

create index idx_transcriptid on approval_queue_step (transcriptid)
GO

create table article (
  artid                   nvarchar(15)                    not null,
  articleStatus           nvarchar(1)                     not null,
  articleDisplay          nvarchar(1)                     not null,
  newscatid               int                             not null,
  fromuserid              nvarchar(85)                    not null,
  dateentered             datetime                        not null,
  expiredate              datetime                        not null,
  title                   nvarchar(255)                   not null,
  teaser                  nvarchar(255)                   null,
  picture                 varchar(255)                    null,
  downloadURL             varchar(255)                    null,
  text1                   ntext                           null,
  displayOrder            int                             not null,
  testid                  nvarchar(85)                    null,
  autoDelete              nvarchar(1)                     null,
  deleteDate              datetime                        null,
  showInCompCenter        char(1)                         null,
  replaceNewLinesWithHTML char(1)                         null,
  last_updated            datetime                        null,
  last_updater_uid        nvarchar(85)                    null,
  poll                    char(1) default 'N'             not null,
  mekp_target             char(1)                         null,
  to_desc_org             char(1) default 'Y'             not null,
  constraint PK_ARTICLE primary key (artid)
)
GO

create table audience (
  id                  nvarchar(15)   not null,
  learningid          nvarchar(85)   not null,
  description         nvarchar(1200) null,
  listorder           int            not null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_AUDIENCE primary key (id)
)
GO

create index NUK_AUDIENCE on audience (learningid, listorder)
GO

create table auditlogins (
  auditkey       int           not null,
  userid         nvarchar(85)  not null,
  logintime      datetime      not null,
  ipaddress      nvarchar(255) not null,
  nsecondsactive int           not null,
  constraint PK_AUDITLOGINS primary key (auditkey)
)
GO

create table auditobjects (
  auditkey   int            not null,
  userid     nvarchar(85)   null,
  actiontime datetime       not null,
  action     nvarchar(1)    not null,
  objecttype int            not null,
  objectid   nvarchar(85)   null,
  msg        nvarchar(4000) null,
  ipaddress  varchar(255)   null,
  constraint PK_AUDITOBJECTS primary key (auditkey)
)
GO

create table autoenroll (
  targetType          nvarchar(15)  not null,
  targetValue         nvarchar(85)  not null,
  learningid          nvarchar(85)  not null,
  learningType        nvarchar(1)   not null,
  eventid             nvarchar(15)  not null,
  startdate           datetime      null,
  enddate             datetime      null,
  durationenabled     char(1)       not null,
  durationamount      int           null,
  durationunit        smallint      null,
  exclusion_flag      char(1)       null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_AUTOENROLL primary key (learningid, eventid, targetType, targetValue, learningType)
)
GO

create index nuk_autoenroll on autoenroll (learningid, eventid)
GO

create table autonewsfilter (
  artid       nvarchar(15) not null,
  filtertype  nvarchar(1)  not null,
  filtervalue nvarchar(85) not null,
  startdate   datetime     null,
  enddate     datetime     null,
  constraint PK_AUTONEWSFILTER primary key (artid, filtertype, filtervalue)
)
GO

create index filtertype on autonewsfilter (filtertype, filtervalue)
GO

create table batchreport (
  userid     nvarchar(85)  not null,
  reportname nvarchar(255) not null,
  pathname   varchar(255)  not null,
  status     nvarchar(1)   not null,
  createdate datetime      not null
)
GO

create table bulletin (
  bltid       nvarchar(15)  not null,
  fromuserid  nvarchar(85)  not null,
  dateentered datetime      not null,
  expiredate  datetime      not null,
  subject     nvarchar(85)  not null,
  text1       nvarchar(255) not null,
  constraint PK_BULLETIN primary key (bltid)
)
GO

create table certcourses (
  certid     numeric(8)   not null,
  learningid nvarchar(85) not null,
  constraint PK_CERTCOURSES primary key (certid, learningid)
)
GO

create index nuk_certid on certcourses (certid)
GO

create table code_certtype (
  certtype    int          not null,
  description nvarchar(85) not null,
  constraint pk_certtype_key primary key (certtype)
)
GO

insert into code_certtype (certtype, description) values (0, 'label.code_certtype.ic')
GO

insert into code_certtype (certtype, description) values (1, 'label.code_certtype.ec')
GO

insert into code_certtype (certtype, description) values (2, 'label.code_certtype.ud')
GO

insert into code_certtype (certtype, description) values (3, 'label.code_certtype.cc')
GO

insert into code_certtype (certtype, description) values (4, 'label.code_certtype.pm')
GO

create table code_certpool (
  poolid      int           not null,
  description nvarchar(100) not null,
  constraint pk_cert_pool_key primary key (poolid)
)
GO

insert into code_certpool (poolid, description) values (0, 'label.code_certpool.general')
GO

create table certifications (
  certid                       numeric(8)                         not null,
  title                        nvarchar(1000)                     not null,
  certification_code           nvarchar(85)                       not null,
  description                  nvarchar(1000)                     not null,
  testid                       nvarchar(85)                       null,
  dateentered                  datetime                           not null,
  certstatus                   smallint                           not null,
  daysvalid                    int                                not null,
  testtype                     nvarchar(1)                        not null,
  issuedby                     nvarchar(50)                       not null,
  sendreminderdays             int                                not null,
  award_email_profile_id       nvarchar(15)                       null,
  poolid                       int default 0,
  certtype                     int default 0,
  allowUserPrint               CHAR(1) DEFAULT 'N'                NOT NULL,
  templateid                   NVARCHAR(15)                       NULL,
  cert_expire_email_profile_id nvarchar(15) default '*D_CRT_REM*' NOT null,
  sessionid                    nvarchar(15)                       null,
  learningid                   nvarchar(85)                       null,
  creator_uid                  nvarchar(85)                       null,
  datecreated                  datetime                           null,
  last_updated                 datetime                           null,
  last_updater_uid             nvarchar(85)                       null,
  expiregraceperiod            char(1) default 'N'                not null,
  fixed_expire_date            datetime                           null,
  award_email_attach_cert      varchar(20)                        null,
  expiration_type              char(1) default 'N'                not null,
  renewable_each_year          char(1) default 'Y'                not null,
  updater_legal_name           nvarchar(250)                      NULL,
  update_meaning_text          ntext                              NULL,
  constraint PK_CERTIFICATIONS primary key (certid),
  constraint CKC_CERTIFICATIONS_C1 check (expiregraceperiod in ('N', 'M')),
  constraint fk_certifications_pool_id foreign key (poolid) references code_certpool (poolid),
  constraint fk_certtype foreign key (certtype) references code_certtype (certtype),
  constraint uk_certification_code unique (certification_code)
)
GO

create table certprograms (
  certpgmid    numeric(8)    not null,
  description  nvarchar(255) not null,
  printed_name nvarchar(85)  null,
  constraint PK_CERTPROGRAMS primary key (certpgmid)
)
GO

insert into certprograms values (0, 'label.certprograms.description', 'label.certprograms.printed_name')
GO

create table certprograms_rule (
  rule_id      nvarchar(15) not null,
  certpgmid    int          not null,
  num_required int          not null,
  constraint PK_CERTPROGRAMS_RULE primary key (rule_id)
)
GO

create table certprograms_rule_detail (
  rule_id          nvarchar(15) not null,
  certid_certpgmid int          not null,
  type             char(1)      not null,
  constraint PK_CERTPROGRAMS_RULE_DETAIL primary key (rule_id, certid_certpgmid, type)
)
GO

create table certusers (
  userid         nvarchar(85)        not null,
  certid         numeric(8)          not null,
  dateachieved   datetime            not null,
  comments       nvarchar(255)       null,
  awardedby      nvarchar(85)        null,
  expiredate     datetime            null,
  nreminders     int                 not null,
  status         char(1) default 'P' not null,
  testinstanceid nvarchar(15)        null,
  learningid     nvarchar(85)        null,
  awardid        nvarchar(85)        not null,
  last_updated            datetime                        null,
  last_updater_uid        nvarchar(85)                    null,
  updater_legal_name      nvarchar(250)                   null,
  update_meaning_text     ntext                           null,
  constraint PK_CERTUSERS primary key (awardid)
)
GO

create index NUK_CERTUSER on certusers (userid, certid)
GO

create table chatrooms (
  roomid      nvarchar(50)  not null,
  category    nvarchar(50)  not null,
  createdby   nvarchar(85)  not null,
  datecreated datetime      not null,
  description nvarchar(255) not null,
  constraint PK_CHATROOMS primary key (roomid)
)
GO

create table code_category (
  categoryid  int          not null,
  description nvarchar(50) not null,
  constraint PK_CODE_CATEGORY primary key (categoryid)
)
GO

insert into code_category values (0, 'Unassigned')
GO

create table code_currency (
  currency    smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_CURRENCY primary key (currency)
)
GO

create table code_displayableJobCode (
  userAttr1 nvarchar(15) null
)
GO

create table code_eventstatus (
  eventstatus smallint     not null,
  description nvarchar(30) not null,
  constraint PK_CODE_EVENTSTATUS primary key (eventstatus)
)
GO

insert into code_eventstatus values (0, 'desc.Active')
GO

insert into code_eventstatus values (1, 'label.Pending')
GO

insert into code_eventstatus values (2, 'label.Completed')
GO

insert into code_eventstatus values (3, 'label.Closed')
GO

insert into code_eventstatus values (4, 'label.Cancelled')
GO

insert into code_eventstatus values (5, 'label.Invitation_Only')
GO

create table code_forumBoard (
  boardid     int                     not null,
  description nvarchar(85)            not null,
  isPublic    nvarchar(1) default 'Y' not null,
  constraint PK_CODE_FORUMBOARD primary key (boardid)
)
GO

insert into code_forumBoard values (0, 'label.forum.initial_forum_name', 'Y')
GO

create table code_initialURL (
  pageid      int          not null,
  description nvarchar(85) not null,
  pageURL     varchar(255) null,
  constraint PK_CODE_INITIALURL primary key (pageid)
)
GO

insert into code_initialURL values (0, 'Home', '/servlet/ekp?TX=MYHOME')
GO

insert into code_initialURL values (2, 'Learn', '/servlet/ekp?TX=TOCRECORDS')
GO

insert into code_initialURL values (3, 'Current Modules', '/servlet/ekp?TX=LISTSESSIONS')
GO

insert into code_initialURL values (10, 'Review/Approval', '/servlet/ekp?TX=TOCMGR')
GO

insert into code_initialURL values (11, 'Manage', '/servlet/ekp?TX=BSTOC')
GO

create table code_itemLanguage (
  ilangid     smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_ITEMLANGUAGE primary key (ilangid)
)
GO

insert into code_itemLanguage values (0, 'English')
GO

insert into code_itemLanguage values (2, 'Chinese')
GO

insert into code_itemLanguage values (4, 'French')
GO

insert into code_itemLanguage values (5, 'Dutch')
GO

insert into code_itemLanguage values (6, 'German')
GO

insert into code_itemLanguage values (8, 'Japanese')
GO

insert into code_itemLanguage values (10, 'Korean')
GO

insert into code_itemLanguage values (12, 'Spanish')
GO

create table code_language (
  langid          smallint     not null,
  description     nvarchar(50) not null,
  default_locale  nvarchar(6)  not null,
  default_charset nvarchar(20) null,
  constraint PK_CODE_LANGUAGE primary key (langid),
  constraint CKC_CODE_LANGUAGE_C1 check (langid > 99)
)
GO

create table code_launchInterface (
  interfaceid smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_LAUNCHINTERFACE primary key (interfaceid)
)
GO

insert into code_launchInterface values (0, 'Generic HTML')
GO

insert into code_launchInterface values (1, 'AICC Data Exchange')
GO

insert into code_launchInterface values (2, 'NETg Cookie Tracking')
GO

create table code_layouttype (
  layouttype  smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_LAYOUTTYPE primary key (layouttype)
)
GO

insert into code_layouttype values (1, 'Sequential Questions')
GO

insert into code_layouttype values (3, 'Multiple per Page')
GO

create table code_learningType (
  learningType nvarchar(1)  not null,
  description  nvarchar(50) not null,
  constraint PK_CODE_LEARNINGTYPE primary key (learningType)
)
GO

insert into code_learningType (description, learningType) values ('ONLINE COURSE', 'O')
GO

insert into code_learningType (description, learningType) values ('CLASSROOM', 'R')
GO

insert into code_learningType (description, learningType) values ('VIRTUAL CLASSROOM', 'V')
GO

insert into code_learningType (description, learningType) values ('EXTERNAL CLASS', 'E')
GO

insert into code_learningType (description, learningType) values ('LEARNING PROGRAM', 'C')
GO

insert into code_learningType (description, learningType) values ('BOOK', 'B')
GO

insert into code_learningType (description, learningType) values ('CD', 'D')
GO

insert into code_learningType (description, learningType) values ('VIDEO', 'I')
GO

insert into code_learningType (description, learningType) values ('JUST IN TIME LEARNING', 'Q')
GO

insert into code_learningType (description, learningType) values ('AUDIO CASSETTE', 'A')
GO

insert into code_learningType (description, learningType) values ('VIRTUAL CLASSROOM ARCHIVED', 'U')
GO

insert into code_learningType (description, learningType) values ('WORKSHOP', 'W')
GO

insert into code_learningType (description, learningType) values ('EXTERNAL CERTIFICATION', 'X')
GO

insert into code_learningType (description, learningType) values ('EXAM', 'M')
GO

create table code_media (
  mediaid     smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_MEDIA primary key (mediaid)
)
GO

insert into code_media values (0, 'Unassigned')
GO

insert into code_media values (1, 'WEB-based')
GO

insert into code_media values (2, 'Classroom')
GO

insert into code_media values (3, 'Seminar')
GO

insert into code_media values (4, 'CBT Download')
GO

insert into code_media values (5, 'Correspondence')
GO

insert into code_media values (6, 'Video Tape')
GO

insert into code_media values (7, 'Audio Tape')
GO

insert into code_media values (8, 'Video Conference')
GO

insert into code_media values (9, 'Textbook')
GO

insert into code_media values (10, 'White Paper')
GO

create table code_newscategory (
  newscatid   int          not null,
  description nvarchar(50) not null,
  constraint PK_CODE_NEWSCATEGORY primary key (newscatid)
)
GO

insert into code_newscategory values (1, 'Company Events')
GO

create table code_perfGrade (
  perfGradeid int          not null,
  description nvarchar(50) not null,
  constraint PK_CODE_PERFGRADE primary key (perfGradeid)
)
GO

insert into code_perfGrade values (1, 'perfgrade.excellent')
GO

insert into code_perfGrade values (2, 'perfgrade.very_good')
GO

insert into code_perfGrade values (3, 'perfgrade.good')
GO

insert into code_perfGrade values (4, 'perfgrade.average')
GO

insert into code_perfGrade values (5, 'perfgrade.poor')
GO

create table code_pool (
  poolid          int                      not null,
  description     nvarchar(150)            not null,
  parentpoolid    int default -1           not null,
  defaultPrefix   nvarchar(15) default 'Q' not null,
  usageLimit      int default 999999       not null,
  weighting       float default 1          not null,
  difficulty      float default 1          not null,
  selectfrequency float default 0          not null,
  menuitem        char(1)                  not null default 'N',
  constraint PK_CODE_POOL_CODE_POO primary key (poolid),
  constraint AK_CODE_POOL_U1 unique (parentpoolid, description)
)
GO

insert into code_pool (poolid, description) values (0, 'Default Question Pool')
GO

create table code_questiontype (
  questiontype smallint     not null,
  description  nvarchar(50) not null,
  constraint PK_CODE_QUESTIONTYPE primary key (questiontype)
)
GO

insert into code_questiontype values (1, 'Single Choice')
GO

insert into code_questiontype values (2, 'True/False')
GO

insert into code_questiontype values (3, 'Matching')
GO

insert into code_questiontype values (4, 'Essay')
GO

insert into code_questiontype values (5, 'Fill-In-The-Blank')
GO

insert into code_questiontype values (6, 'Rating')
GO

insert into code_questiontype values (7, 'Multiple Choice')
GO

insert into code_questiontype values (8, 'Triple Rating')
GO

insert into code_questiontype values (10, 'Drag And Drop')
GO

insert into code_questiontype values (11, 'Hotspot')
GO

create table code_regcontrol (
  regid       smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_REGCONTROL primary key (regid)
)
GO

insert into code_regcontrol values (0, 'No Pre-Approval Needed')
GO

insert into code_regcontrol values (1, 'Needs Manager Approval')
GO

insert into code_regcontrol values (2, 'Needs Instructor Approval')
GO

create table code_subject (
  subjectid   int          not null,
  description nvarchar(50) not null,
  constraint PK_CODE_SUBJECT primary key (subjectid)
)
GO

insert into code_subject values (0, 'Unassigned')
GO

create table code_testpool (
  tpoolid      int                 not null,
  description  nvarchar(150)       not null,
  parentpoolid int default -1      not null,
  menuitem     char(1) default 'N' not null,
  constraint PK_CODE_TESTPOOL primary key (tpoolid)
)
GO

insert into code_testpool (tpoolid, description) values (0, 'Default Test Pool')
GO

create table code_teststatus (
  statuscode  smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_TESTSTATUS primary key (statuscode)
)
GO

insert into code_teststatus values (0, 'Not Available')
GO

insert into code_teststatus values (1, 'In progress')
GO

insert into code_teststatus values (2, 'Completed')
GO

insert into code_teststatus values (3, 'Graded')
GO

insert into code_teststatus values (4, 'Aborted')
GO

create table code_teststyles (
  stylesheet  nvarchar(15) not null,
  description nvarchar(50) not null,
  constraint PK_CODE_TESTSTYLES primary key (stylesheet)
)
GO

insert into code_teststyles values ('tests', 'Test Default')
GO

insert into code_teststyles values ('evaluation', 'Evaluation')
GO

create table code_testtype (
  testtype    smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_TESTTYPE primary key (testtype)
)
GO

insert into code_testtype values (1, 'Fixed Questions')
GO

insert into code_testtype values (2, 'Random Question Pool')
GO

create table code_testuse (
  tuseid      smallint     not null,
  description nvarchar(50) not null,
  constraint PK_CODE_TESTUSE primary key (tuseid)
)
GO

insert into code_testuse values (0, 'Course Test')
GO

insert into code_testuse values (1, 'Survey')
GO

insert into code_testuse values (2, 'Skills Test')
GO

insert into code_testuse values (3, 'Course Evaluation')
GO

create table code_userattr1 (
  userattr1   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR1 primary key (userattr1)
)
GO

insert into code_userattr1 values ('0', 'Unassigned')
GO

create table code_userattr2 (
  userattr2   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR2 primary key (userattr2)
)
GO

insert into code_userattr2 values ('0', 'Unassigned')
GO

create table code_userattr3 (
  userattr3   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR3 primary key (userattr3)
)
GO

insert into code_userattr3 values ('0', 'Unassigned')
GO

create table code_userattr4 (
  userattr4   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR4 primary key (userattr4)
)
GO

insert into code_userattr4 values ('0', 'Unassigned')
GO

create table code_userattr5 (
  userattr5   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR5 primary key (userattr5)
)
GO

insert into code_userattr5 (userattr5, description) values ('0', 'Unassigned')
GO

create table code_userattr6 (
  userattr6   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR6 primary key (userattr6)
)
GO

insert into code_userattr6 (userattr6, description) values ('0', 'Unassigned')
GO

create table code_userattr7 (
  userattr7   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR7 primary key (userattr7)
)
GO

insert into code_userattr7 (userattr7, description) values ('0', 'Unassigned')
GO

create table code_userattr8 (
  userattr8   nvarchar(85) not null,
  description nvarchar(85) null,
  constraint PK_CODE_USERATTR8 primary key (userattr8)
)
GO

insert into code_userattr8 (userattr8, description) values ('0', 'Unassigned')
GO

create table code_vendorname (
  vendorname  varchar(100)  not null,
  description nvarchar(255) not null,
  constraint pk_code_vendor_name primary key (vendorname)
)
GO

insert into code_vendorname (vendorname, description) values ('Unknown', 'Unknown')
GO

create table column_available (
  tableid     int          not null,
  column_name nvarchar(30) not null,
  column_type nvarchar(20) not null,
  ref_tableid int          not null,
  ref_column  nvarchar(30) not null,
  constraint PK_COLUMN_AVAILABLE primary key (tableid, column_name)
)
GO

create table competency (
  skillid        nvarchar(15)        not null,
  courseid       nvarchar(85)        not null,
  minimumscore   float               not null,
  mandatory_flag char(1) default 'Y' not null,
  constraint PK_COMPETENCY primary key (skillid, courseid)
)
GO

create table contactList (
  learningid          nvarchar(85)  not null,
  event_session       nvarchar(15)  not null,
  userid              nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_CONTACTLIST primary key (learningid, event_session, userid)
)
GO

create index NUK_CONTACTLIST_UID on contactList (userid)
GO

create table cost_category (
  cost_category_id nvarchar(15) not null,
  description      nvarchar(50) not null,
  constraint PK_COST_CATEGORY primary key (cost_category_id)
)
GO

create table course (
  id                          nvarchar(15)              not null,
  courseid                    nvarchar(85)              not null,
  vendorid                    nvarchar(15)              not null,
  usageTotal                  int                       not null,
  launchpage                  ntext                     null,
  launchInterface             smallint                  not null,
  launchParms                 varchar(255)              null,
  useDefaultViewer            nvarchar(1)               not null,
  viewerpage                  varchar(100)              null,
  reserved1                   nvarchar(15)              not null,
  reserved2                   nvarchar(1)               not null,
  credits                     float                     not null,
  nAttachments                smallint                  not null,
  nTests                      smallint                  not null,
  cversion                    datetime                  not null,
  allowUserStatusChg          nvarchar(1)               not null,
  prompt_for_withdraw_reason  char(1) default 'N'       not null,
  allowRefAccess              nvarchar(1)               not null,
  instantAccess               nvarchar(1)               not null,
  duration                    nvarchar(100)             null,
  contentServer               nvarchar(20)              null,
  bookmark                    char(1)                   null,
  navigationTreeVisible       char(1)                   null,
  organizationID              nvarchar(15)              null,
  previousNextButtonsVisible  char(1)                   null,
  itemID                      nvarchar(15)              null,
  certid                      numeric(8, 0)             null,
  accumulateTime              char(1)                   null,
  skillsoft_course_type       int                       null,
  show_toolbars               char(1) default 'N'       not null,
  show_on_other_resources_tab CHAR(1) DEFAULT 'N'       NOT NULL,
  mekp_download               char(1) default 'N'       not null,
  update_meaning              nvarchar(8)               null,
  last_updated                datetime                  null,
  updater_legal_name          nvarchar(250)             null,
  update_meaning_text         ntext                     null,
  last_updater_uid            nvarchar(85)              null,
  launched_win_width          int                       null,
  launched_win_height         int                       null,
  constraint PK_COURSE primary key (id),
  attempts_allowed            int                       null,
  grading_method              char(1)                   null,
  completion_method           char(1)                   null,
  ieCompatibilityMode         char(1)                   null,
  CONSTRAINT fk_course_organization_id FOREIGN KEY (organizationID)
  REFERENCES ContentOrganization (organizationID),
  constraint UK_COURSE unique (courseid)
)
GO

create table courseAccessLevel (
  learningid          nvarchar(85)                          not null,
  sessionid           nvarchar(85) default '*ALL_SESSIONS*' not null,
  levelid             nvarchar(15)                          not null,
  update_meaning      nvarchar(8)                           null,
  last_updated        datetime                              null,
  updater_legal_name  nvarchar(250)                         null,
  update_meaning_text ntext                                 null,
  last_updater_uid    nvarchar(85)                          null,
  constraint PK_COURSEACCESSLVL1 primary key (learningid, sessionid, levelid)
)
GO

create table courseAccessUser (
  learningid          nvarchar(85)  not null,
  userid              nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_COURSEACCESSUSER primary key (learningid, userid)
)
GO

CREATE TABLE ekpfiles_revision (
  id              NVARCHAR(85)  NOT NULL,
  path            NVARCHAR(255) NOT NULL,
  revision_number INT           NOT NULL,
  file_name       NVARCHAR(255) NOT NULL,
  created_by      NVARCHAR(85)  NOT NULL,
  created_date    DATETIME      NOT NULL,
  revision_path   NVARCHAR(255) NOT NULL,
  CONSTRAINT PK_EKPFILES_REVISION PRIMARY KEY (id)
)
GO

create table courseDownloads (
  downloadid           numeric(8)                      not null,
  courseid             nvarchar(85)                    not null,
  fileURL              varchar(255)                    not null,
  URLType              smallint                        not null,
  description          nvarchar(255)                   null,
  prePostType          nvarchar(10) default 'standard' not null,
  ordering             int default '0'                 not null,
  update_meaning       nvarchar(8)                     null,
  last_updated         datetime                        null,
  updater_legal_name   nvarchar(250)                   null,
  update_meaning_text  ntext                           null,
  last_updater_uid     nvarchar(85)                    null,
  ekpfiles_revision_id nvarchar(85)                    null,
  constraint PK_COURSEDOWNLOADS primary key (downloadid),
  constraint FK_COURSED_EKPFILES_REV foreign key (ekpfiles_revision_id) references ekpfiles_revision (id)
)
GO

create index NUK_DOWN_COURSE on courseDownloads (courseid)
GO

create index coursedownloadNUK on courseDownloads (courseid, URLType, prePostType, ordering)
GO

create table course_session_cost (
  eventid             nvarchar(15)  not null,
  session_cost_id     nvarchar(15)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_COURSE_SESSION_COST primary key (eventid, session_cost_id)
)
GO

create table coursetest (
  courseid            nvarchar(85)        not null,
  testid              nvarchar(85)        not null,
  exam_type           char(1) default 'N' not null,
  update_meaning      nvarchar(8)         null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  constraint PK_COURSETEST primary key (courseid, testid)
)
GO

create table cschedule (
  eventid               nvarchar(15)            not null,
  courseid              nvarchar(85)            not null,
  eventstatus           smallint                not null,
  availability          nvarchar(1)             not null,
  startdate             datetime                null,
  enddate               datetime                null,
  timezone              varchar(50)             null,
  vcchannel             nvarchar(85)            null,
  contactinfo           nvarchar(255)           null,
  comments              nvarchar(255)           null,
  seat_limit_rule       varchar(1)              null,
  seatlimit             int                     not null,
  min_size              int default 0           not null,
  facilityid            nvarchar(15)            null,
  location              nvarchar(255)           null,
  enrollDeadline        datetime                null,
  institution           nvarchar(50)            null,
  approverid1           nvarchar(85)            null,
  regcontrol1           smallint                not null,
  approverid2           nvarchar(85)            null,
  regcontrol2           smallint                not null,
  currency              nvarchar(15)            not null,
  billed                char(1)                 null,
  ntraininghours        float default 0         not null,
  usage_expiration_date datetime                null,
  n_allowed_usage_days  int                     null,
  enrollBeginDate       datetime                null,
  withdrawn_nday        int default 0,
  completionDateLimit   datetime                null,
  scheduleComments      nvarchar(255)           null,
  useCustomText         nvarchar(1) default 'N' not null,
  geographic_region     nvarchar(255)           NULL,
  hw_duedate            datetime                NULL,
  use_nday              int                     NULL,
  cpd_credits           float default 0         not null,
  complete_by_date      datetime                null,
  complete_in_days      int                     null,
  enforce_deadline      nvarchar(1)             null,
  sequence              INT                     NOT NULL,
  description           NTEXT                   NULL,
  session_title         nvarchar(255)           null,
  session_code          nvarchar(85)            not null,
  ew_id                 nvarchar(15)            null,
  is_wf_assign_disabled char(1)                 null,
  tutor_select_type     char(1)                 null,
  update_meaning        nvarchar(8)             null,
  last_updated          datetime                null,
  updater_legal_name    nvarchar(250)           null,
  update_meaning_text   ntext                   null,
  last_updater_uid      nvarchar(85)            null,
  attendance_sheet_url  nvarchar(255)           null,
  create_date         datetime                  null,
  constraint PK_CSCHEDULE_CSCHEDUL primary key (eventid),
  constraint CKC_CSCHEDULE_C1 check (session_code <> ''),
  constraint CKC_CSCHEDULE_C2 unique (courseid, session_code)
)
GO

create unique index pk_csched_course on cschedule (courseid, eventid)
GO

create index NUK_CSCHEDULE_1 on cschedule (startdate)
GO

create table module_session_schedule (
  eventid             nvarchar(15)        not null,
  sessionid           nvarchar(15)        not null,
  startdate           datetime            null,
  enddate             datetime            null,
  utc_start_date      datetime            null,
  utc_end_date        datetime            null,
  facilityid          nvarchar(15)        null,
  location            nvarchar(255)       null,
  ignore_conflict     char(1) default 'N' not null,
  room_name           nvarchar(255)       null,
  update_meaning      nvarchar(8)         null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  constraint PK_MODULE_SESSION_SCHEDULE primary key (eventid),
  constraint FK_MODULE_SESSION_SCHEDULE foreign key (sessionid) references cschedule (eventid)
)
GO

create table currcourses (
  currid              nvarchar(85)    not null,
  sessionid           nvarchar(15)    not null,
  courseOrder         tinyint         not null,
  courseid            nvarchar(85)    not null,
  eventid             nvarchar(15)    not null,
  required            char(1)         not null,
  enrollbydefault     char(1)         not null,
  update_meaning      nvarchar(8)     null,
  last_updated        datetime        null,
  updater_legal_name  nvarchar(250)   null,
  update_meaning_text ntext           null,
  last_updater_uid    nvarchar(85)    null,
  modulecredits       float default 0 not null,
  constraint PK_CURRCOURSES primary key (currid, sessionid, courseid, eventid)
)
GO

create table curriculum (
  currid                     nvarchar(85)              not null,
  certid                     numeric(8)                null,
  certtestid                 nvarchar(85)              null,
  allowSelfEnrollment        nvarchar(1)               not null,
  allowUserStatusChg         nvarchar(1)               not null,
  prompt_for_withdraw_reason char(1) default 'N'       not null,
  approverid1                nvarchar(85)              null,
  regcontrol1                smallint                  not null,
  approverid2                nvarchar(85)              null,
  regcontrol2                smallint                  not null,
  allowreferenceaccess       nvarchar(1)               not null,
  update_meaning             nvarchar(8)               null,
  last_updated               datetime                  null,
  updater_legal_name         nvarchar(250)             null,
  update_meaning_text        ntext                     null,
  last_updater_uid           nvarchar(85)              null,
  constraint PK_CURRICULUM primary key (currid)
)
GO

create table dataloaderfiles (
  fileid        nvarchar(20)  not null,
  filename      nvarchar(255) not null,
  description   nvarchar(500) not null,
  dateloaded    datetime      not null,
  userid        nvarchar(85)  null,
  logname       nvarchar(255) null,
  exportcsvname nvarchar(500) null,
  status        smallint      not null,
  type          char(1)       not null,
  constraint PK_DATALOADERFILES primary key (fileid)
)
GO

create index INDEX_DATELOADED on dataloaderfiles (dateloaded)
GO

create table ekpfiles (
  repositoryid   nvarchar(15)                   not null,
  domain_id      nvarchar(15) default 'DEFAULT' not null,
  ownerid        nvarchar(85)                   not null,
  file_name      nvarchar(255)                  not null,
  path           varchar(255)                   null,
  parentfolderid nvarchar(15)                   null,
  description    nvarchar(255)                  null,
  file_type      char(1)                        not null,
  metadataid     nvarchar(15)                   null,
  dateloaded     datetime                       null,
  heritage       char(1)                        not null,
  constraint PK_EKPFILES primary key (repositoryid)
)
GO

create index owners_ekpfiles on ekpfiles (ownerid)
GO

create index nuk_ft_owner_ekpfiles on ekpfiles (file_type, ownerid)
GO

create index nuk_ft_parent_ekpfiles on ekpfiles (file_type, parentfolderid)
GO

create index nuk_parent_ekpfiles on ekpfiles (parentfolderid)
GO

create table email_profile (
  email_profile_id       nvarchar(15)                   not null,
  profile_name           nvarchar(100)                  not null,
  send_student           char(1)                        not null,
  send_manager           char(1)                        not null,
  send_instructor        char(1)                        not null,
  send_dept_approver     char(1)                        not null,
  send_system_approver   char(1)                        not null,
  send_system_admin      char(1)                        not null,
  send_specific_user     char(1)                        not null,
  specific_user_id       nvarchar(85)                   null,
  send_external_email    char(1)                        not null,
  send_reviewer          char(1) default 'O'            not null,
  send_verifier          char(1) default 'O'            not null,
  send_org_approver      char(1) default 'O'            not null,
  external_email         nvarchar(85)                   null,
  predefined_message_key nvarchar(50)                   null,
  send_hrmanager         char(1) default 'O'            not null,
  sender_id              nvarchar(85)                   null,
  creator_uid            nvarchar(85)                   null,
  datecreated            datetime                       null,
  last_updated           datetime                       null,
  last_updater_uid       nvarchar(85)                   null,
  send_reference         char(1) default 'N'            not null,
  send_maps              char(1) default 'N'            not null,
  domain_id              nvarchar(15) default 'DEFAULT' not null,
  constraint PK_EMAIL_PROFILE primary key (email_profile_id),
  constraint CKC_SEND_STUDENT_EMAIL_PR check (send_student in ('T', 'C', 'O')),
  constraint CKC_SEND_MANAGER_EMAIL_PR check (send_manager in ('T', 'C', 'O')),
  constraint CKC_SEND_INSTRUCTOR_EMAIL_PR check (send_instructor in ('T', 'C', 'O')),
  constraint CKC_SEND_DEPT_APPROVE_EMAIL_PR check (send_dept_approver in ('T', 'C', 'O')),
  constraint CKC_SEND_SYSTEM_APPRO_EMAIL_PR check (send_system_approver in ('T', 'C', 'O')),
  constraint CKC_SEND_SYSTEM_ADMIN_EMAIL_PR check (send_system_admin in ('T', 'C', 'O')),
  constraint CKC_SEND_SPECIFIC_USE_EMAIL_PR check (send_specific_user in ('T', 'C', 'O')),
  constraint CKC_SEND_EXTERNAL_EMA_EMAIL_PR check (send_external_email in ('T', 'C', 'O'))
)
GO

create unique index unique_profile_name on email_profile (profile_name)
GO

create table email_profile_attachment (
  email_profile_id nvarchar(15) not null,
  repositoryid     nvarchar(15) not null,
  constraint PK_EMAIL_PROFILE_ATTACHMENT primary key (email_profile_id, repositoryid)
)
GO

create table email_profile_detail (
  email_profile_id nvarchar(15)  not null,
  language_id      int           not null,
  subject          nvarchar(255) not null,
  body             ntext         not null,
  constraint PK_EMAIL_PROFILE_DETAIL primary key (email_profile_id, language_id)
)
GO

create table enrollment_policy (
  policy_id                      nvarchar(15)                   not null,
  policy_name                    nvarchar(50)                   not null,
  remarks                        nvarchar(255)                  null,
  is_active                      char(1)                        not null,
  withdraw_email_profile_id      nvarchar(15)                   null,
  approved_email_profile_id      nvarchar(15)                   null,
  send_group_enroll              char(1)                        not null,
  send_auto_enroll               char(1)                        not null,
  send_self_enroll               char(1)                        not null,
  reject_email_profile_id        nvarchar(15)                   null,
  enroll_email_profile_id        nvarchar(15)                   null,
  allow_approval_withdrawal      char(1) default 'Y'            not null,
  prompt_for_withdraw_reason     char(1) default 'N'            not null,
  consider_pay_plan              char(1) default 'N'            not null,
  all_parallel_steps_must_approv char(1) default 'N'            not null,
  policyDetails                  ntext                          NOT NULL,
  waitedListed_email_profile_id  nvarchar(15)                   null,
  enroll_pend_email_profile_id   nvarchar(15)                   null,
  session_trans_email_profile_id nvarchar(15)                   null,
  creator_uid                    nvarchar(85)                   null,
  datecreated                    datetime                       null,
  last_updated                   datetime                       null,
  last_updater_uid               nvarchar(85)                   null,
  updater_legal_name             nvarchar(250)                  null,
  update_meaning_text            ntext                          null,
  update_meaning                 nvarchar(8)                    null,
  domain_id                      nvarchar(15) default 'DEFAULT' not null,
  constraint PK_ENROLLMENT_POLICY primary key (policy_id)
)
GO

create unique index name_unique on enrollment_policy (policy_name)
GO

create table enrollment_policy_step (
  step_id             nvarchar(15)        not null,
  policy_id           nvarchar(15)        not null,
  sequence_no         int                 not null,
  step_name           nvarchar(50)        not null,
  remarks             nvarchar(255)       null,
  remind_day          int                 null,
  remind_email_id     nvarchar(15)        null,
  expire_day          int                 not null,
  expire_action       char(1)             not null,
  email_profile_id    nvarchar(15)        not null,
  expire_email_id     nvarchar(15)        null,
  update_meaning      nvarchar(8)         null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  manual              char(1)             not null
    constraint CKC_MANUAL_ENROLLME check (manual in ('Y', 'N')),
  approverid1         nvarchar(85)        null,
  regcontrol1         int                 null,
  parallel_step       char(1)             null,
  include_costcenter  char(1) default 'N' not null,
  include_email       char(1) default 'N' not null,
  notify_email_id     nvarchar(15)        null,
  constraint PK_ENROLLMENT_POLICY_STEP primary key (step_id)
)
GO

create unique index unique_seq_no_pol_id on enrollment_policy_step (sequence_no, policy_id)
GO

create table exam_comment (
  instanceid          nvarchar(15)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  ctype               char(1)       not null
    constraint CTYPE_EXAM_COMMENT check (ctype in ('A', 'B', 'R')),
  userid              nvarchar(85)  not null,
  dateprepared        datetime      not null,
  preparedby          nvarchar(85)  not null,
  commenttext         ntext         null,
  constraint PK_EXAMCOMMENT primary key (instanceid, ctype)
)
GO

create table evaluation (
  courseid            nvarchar(85)  null,
  eventid             nvarchar(15)  null,
  userid              nvarchar(85)  not null,
  testid              nvarchar(85)  not null,
  instanceid          nvarchar(15)  not null,
  datecreated         datetime      not null,
  evalstatus          smallint      not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_EVALUATION primary key (instanceid)
)
GO

create index nuk_eval_course on evaluation (courseid)
GO

create index nuk_eval_event on evaluation (eventid)
GO

create table eventMessage (
  eventType      nvarchar(15) not null,
  eventQualifier nvarchar(15) not null,
  eventAction    nvarchar(15) not null,
  subject        nvarchar(50) not null,
  contentid      numeric(16)  not null,
  constraint PK_EVENTMESSAGE primary key (eventType, eventQualifier, eventAction)
)
GO

create table extern_mail (
  expire_key     nvarchar(10)                  not null,
  sender_id      nvarchar(85)                  not null,
  receiver_email varchar(100)                  not null,
  url            varchar(255)                  not null,
  create_date    datetime default getdate()    not null,
  day_expire     int default 0                 not null,
  status         nvarchar(10) default 'active' not null,
  constraint PK_EXTERN_MAIL primary key (expire_key)
)
GO

create table external_training (
  recordid            nvarchar(15)             not null,
  userid              nvarchar(85)             not null,
  title               nvarchar(255)            not null,
  startdate           datetime                 null,
  enddate             datetime                 null,
  grade               nvarchar(30)             null,
  score               nvarchar(30)             null,
  comments            nvarchar(1024)           null,
  training_hours      float default 0          not null,
  course_type         nvarchar(100)            null,
  subject             nvarchar(100)            null,
  duration            float default 0          not null,
  duration_unit       nvarchar(50)             null,
  language            int default 0            not null,
  cost                numeric(15, 2) default 0 not null,
  currency            nvarchar(15)             null,
  vendor_info1        nvarchar(255)            null,
  vendor_info2        nvarchar(255)            null,
  other_info          nvarchar(255)            null,
  venue               nvarchar(512)            null,
  studentstatus       int default 0            not null,
  update_meaning      nvarchar(8)              null,
  last_updated        datetime                 null,
  updater_legal_name  nvarchar(250)            null,
  update_meaning_text ntext                    null,
  last_updater_uid    nvarchar(85)             null,
  display_filename    nvarchar(85)             null,
  path                varchar(255)             null,
  upload_date         datetime                 null,
  uploaded_by         nvarchar(85)             null,
  constraint PK_EXTERNAL_TRAINING primary key (recordid)
)
GO

create index NUK_EX_USERS on external_training (userid)
GO

create table facilities (
  facilityid             nvarchar(15)        not null,
  location               nvarchar(255)       not null,
  contactinfo            nvarchar(255)       null,
  directions             nvarchar(1000)      null,
  availstatus            nvarchar(1)         not null,
  training_center_id     nvarchar(15)        null,
  min_n_seats            int default 0       not null,
  max_n_seats            int default 0       not null,
  room_is_expandable     char(1) default 'N' not null,
  has_builtin_display_pc char(1) default 'N' not null,
  n_vga_projector        int default 0       not null,
  n_overhead_projector   int default 0       not null,
  n_student_pcs          int default 0       not null,
  n_whiteboards          int default 0       not null,
  facility_code          nvarchar(85)        not null,
  facility_type          char(1) default 'U',
  map_url                nvarchar(255)       null,
  creator_uid            nvarchar(85)        null,
  datecreated            datetime            null,
  last_updated           datetime            null,
  last_updater_uid       nvarchar(85)        null,
  constraint PK_FACILITIES primary key (facilityid)
)
GO

create index NUK_TRAINING_CENTER on facilities (training_center_id)
GO

insert into facilities (facilityid, location, contactinfo, directions, availstatus, facility_code, facility_type)
  values ('_none', 'None Assigned', null, null, 'A', '_none', 'S')
GO

insert into facilities (facilityid, location, contactinfo, directions, availstatus, facility_code, facility_type)
  values ('_TBD', 'To Be Determined', null, null, 'A', '_TBD', 'S')
GO

insert into facilities (facilityid, location, contactinfo, directions, availstatus, facility_code, facility_type)
  values ('_LOC', 'Use venue entered below', null, null, 'A', '_LOC', 'S')
GO

insert into facilities (facilityid, location, contactinfo, directions, availstatus, facility_code, facility_type)
  VALUES ('_VAR', 'Various', null, null, 'A', '_VAR', 'S')
GO

create table fileCategory (
  category nvarchar(50) not null,
  constraint PK_FILECATEGORY primary key (category)
)
GO

insert into fileCategory values ('Audio')
GO

insert into fileCategory values ('Documents')
GO

insert into fileCategory values ('General')
GO

insert into fileCategory values ('Images')
GO

insert into fileCategory values ('Powerpoint Presentations')
GO

insert into fileCategory values ('Video')
GO

create table fileUpload (
  itemid       numeric(8)    not null,
  userid       nvarchar(85)  not null,
  dateentered  datetime      not null,
  filename     nvarchar(255) not null,
  filestatus   nvarchar(1)   not null,
  description  nvarchar(255) not null,
  filelength   int           null,
  category     nvarchar(40)  null,
  originalname nvarchar(85)  null,
  mimetype     nvarchar(64)  null,
  constraint PK_FILEUPLOAD primary key (itemid)
)
GO

create index NUK_FILE_USER on fileUpload (userid)
GO

create table forum (
  msgid         numeric(16)         not null,
  boardid       int                 not null,
  forumid       numeric(16)         not null,
  topicid       numeric(16)         not null,
  nexttopic     numeric(16)         not null,
  prevtopic     numeric(16)         not null,
  replyto       numeric(16)         not null,
  nmembers      int                 not null,
  nhits         int                 not null,
  fromdate      datetime            not null,
  fromuserid    nvarchar(85)        not null,
  topic         nvarchar(50)        not null,
  contentid     numeric(16)         not null,
  publicAccess  nvarchar(1)         null,
  curricAccess  nvarchar(1)         null,
  displayStatus char(1) DEFAULT 'Y' NOT NULL,
  last_updated  datetime            null,
  moderator     nvarchar(85)        null,
  constraint PK_FORUM primary key (msgid, boardid, forumid, topicid),
  constraint AK_PK_FORUM_FORUM unique (msgid)
)
GO

create index NUK_BFT on forum (boardid, forumid, topicid)
GO

create table forumaccess (
  forumid numeric(16)  not null,
  userid  nvarchar(85) not null
)
GO

create table learningObject (
  learningid              nvarchar(85)                    not null,
  learningType            nvarchar(1)                     null,
  mediaid                 smallint                        not null,
  subject                 int                             not null,
  language                smallint                        not null,
  title                   nvarchar(255)                   not null,
  description1            ntext                           null,
  hyperlink               nvarchar(500)                   null,
  assignedChatRoom        nvarchar(30)                    null,
  assignedForum           NUMERIC(16)                     null,
  editcontrol             smallint                        not null,
  showInCatalog           nvarchar(1)                     not null,
  allowPeerReview         nvarchar(1)                     not null,
  datecreated             datetime                        not null,
  autoDelete              nvarchar(1)                     null,
  deleteDate              datetime                        null,
  picture                 varchar(100)                    null,
  evaluation              nvarchar(85)                    null,
  evalRequired            nvarchar(1)                     null,
  duration                nvarchar(100)                   null,
  policyid                nvarchar(15)                    null,
  org_policy_override     char(1) default 'N'             not null,
  prereqcontrol           int                             null,
  testRequired            char(1)                         null,
  startAvailabilityDate   datetime                        null,
  endAvailabilityDate     datetime                        null,
  vendorname              varchar(100)                    null,
  allowReopen             char(1) default 'Y',
  testEnablesCompletion   char(1)                         null,
  enforcePrePostTestOrder char(1)                         null,
  completionTimeLimit     int DEFAULT 0,
  allowReEnroll           CHAR(1) DEFAULT 'Y'             NOT NULL,
  historyExpireWindow     int DEFAULT 0                   NOT NULL,
  display_course_interest char(1)                         null,
  wikiURL                 varchar(255)                    null,
  supervisorAssessment    nvarchar(85)                    null,
  allowDappraiserAssess   char(1) default 'N'             not null,
  sticky                  char(1)                         null,
  creator_uid             nvarchar(85)                    null,
  last_updated            datetime                        null,
  last_updater_uid        nvarchar(85)                    null,
  mekp_target             char(1)                         null,
  update_meaning          nvarchar(8)                     null,
  wiki_key                nvarchar(255)                   null,
  wiki_display            char(1) default 'P'             not null,
  archived                char(1)                         null,
  updater_legal_name      nvarchar(250)                   null,
  update_meaning_text     ntext                           null,
  auto_archive_date       datetime                        null,
  show_expiration_date    datetime                        null,
  show_begin_date         datetime                        null,
  subject_category_id     nvarchar(15) default '0'        not null,
  require_esignature      char(1) default 'Y'             not null,
  support_contact         nvarchar(85)                    null,
  domain_id               nvarchar(15) default 'DEFAULT'  not null,
  allow_auto_recommend 	  char(1)      default 'N' not null,
  constraint PK_LEARNINGOBJ primary key (learningid),
  constraint fk_learning_object_language foreign key (language)
  references code_itemLanguage (ilangid),
  constraint fk_learning_object_vendor_name foreign key (vendorname)
  references code_vendorname (vendorname)
)
GO

create index NUK_LO_LTYPE on learningObject (learningType)
GO

create table sessiondetail (
  currid                   NVARCHAR(85)        not null,
  sessionid                NVARCHAR(15)        not null,
  approverid1              NVARCHAR(85)        null,
  regcontrol1              smallint            not null,
  approverid2              NVARCHAR(85)        null,
  regcontrol2              smallint            not null,
  contactinfo              nvarchar(255)       null,
  orders                   int                 not null,
  sessionstatus            int                 null,
  currency                 nvarchar(15)        not null,
  billed                   char(1)             null,
  startdate                datetime            null,
  completionDateLimit      datetime            null,
  requiredelectivehours    float               not null,
  enddate                  datetime            null,
  enforce_mod_order        char(1) default 'N' not null,
  complete_by_date         datetime            null,
  complete_in_days         int                 null,
  enforce_deadline         nvarchar(1)         null,
  availability             char(1)             null,
  enroll_limit             int default 0       not null,
  enforce_mod_pay          char(1) default 'N' not null,
  description              NTEXT               NULL,
  session_title            NVARCHAR(85)        null,
  use_program_session_cost char(1) default 'Y' not null,
  ew_id                    nvarchar(15)        null,
  is_wf_assign_disabled    char(1)             null,
  tutor_select_type        char(1)             null,
  update_meaning           nvarchar(8)         null,
  last_updated             datetime            null,
  updater_legal_name       nvarchar(250)       null,
  update_meaning_text      ntext               null,
  last_updater_uid         nvarchar(85)        null,
  timezone                 varchar(50)         null,
  earnedcredits            float default 0     not null,
  use_defined_credits      char(1) default 'N' not null,
  revised_date             datetime            null,
  create_date         	   datetime            null,
  constraint PK_SESSIONDETAIL primary key (currid, sessionid)
)
GO

create index NUK_SESSIONDETAIL_1 on sessiondetail (startdate)
GO

create table homework_file (
  currid              nvarchar(85)  null,
  sessionid           nvarchar(15)  null,
  eventid             nvarchar(15)  null,
  path                varchar(255)  not null,
  ownerid             nvarchar(85)  not null,
  createdate          datetime      not null,
  file_type           nvarchar(20)  not null,
  to_userid           nvarchar(85)  null,
  description         nvarchar(85)  null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_HOMEWORK_FILE primary key (ownerid, createdate),
  constraint AK_PATH_HOMEWORK unique (path),
  constraint FK_HOMEWORK_FILE_REF_CSC foreign key (eventid) references cschedule (eventid),
  constraint FK_HOMEWORK_FILE_REF_SES foreign key (currid, sessionid) references sessiondetail (currid, sessionid),
  constraint FK_HOMEWORK_FILE_REF_LO foreign key (currid) references learningObject (learningid),
  constraint FK_HOMEWORK_FILE_REF_USER_1 foreign key (ownerid) references users (userid),
  constraint FK_HOMEWORK_FILE_REF_USER_2 foreign key (to_userid) references users (userid)
)
GO

create table iComment (
  transcriptid        nvarchar(15)  not null,
  cType               nvarchar(1)   not null,
  userid              nvarchar(85)  not null,
  dateprepared        datetime      not null,
  preparedby          nvarchar(85)  not null,
  commenttext         ntext         null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_ICOMMENT primary key (transcriptid, cType),
  constraint CKC_CTYPE_ICOMMENT check (cType in ('A', 'B', 'R'))
)
GO

create index NUK_ICOMMENT_UID on iComment (userid)
GO

create view general_comment as
  select
    transcriptid,
    cType,
    userid,
    dateprepared,
    preparedby,
    commenttext
  from iComment
  where cType = 'A'
GO

create view recommendation as
  select
    transcriptid,
    cType,
    userid,
    dateprepared,
    preparedby,
    commenttext
  from iComment
  where cType = 'R'
GO

create table knowledgecenteroptions (
  id                       nvarchar(15)         not null,
  learningid               nvarchar(85)         not null,
  picture_url              varchar(255)         null,
  course_summary_area      char(1)              not null,
  notepad_area             char(1)              not null,
  evaluation_area          char(1)              not null,
  test_area                char(1)              not null,
  certification_area       char(1)              not null,
  peer_comments_area       char(1)              not null,
  news_area                char(1)              not null,
  status_change_area       char(1)              not null,
  instructor_list_area     char(1)              not null,
  forum_area               char(1)              not null,
  chatroom_area            char(1)              not null,
  contact_list_area        char(1)              not null,
  course_launch            char(1)              not null,
  reference_area           char(1)              not null,
  instructor_share_area    char(1)              not null,
  picture_area             char(1)              not null,
  module_name_launches_kc  char(1)              not null,
  instructor_about_me_area char(1)              not null,
  homework_file_share_area char(1)              not null,
  homework_drop_area       char(1)              not null,
  homework_marked_area     char(1)              not null,
  kc_layout                char(1)              not null,
  picture_location         char(1)              null,
  transcript_area          char(1)              not null,
  show_module_attributes   char(1) default 'Y'  not null,
  exam_wf_area             char(1) default 'N'  not null,
  extended_quick_eval_area varchar(100)         null,
  use_kc                   char(1) default 'Y'  not null,
  update_meaning           nvarchar(8)          null,
  last_updated             datetime             null,
  updater_legal_name       nvarchar(250)        null,
  update_meaning_text      ntext                null,
  last_updater_uid         nvarchar(85)         null,
  constraint PK_KNOWLEDGECENTEROPTIONS primary key (id),
  constraint UK_KNOWLEDGECENTEROPTIONS unique (learningid),
  constraint FK_knowledgecenteroptions_LO foreign key (learningid) references learningObject (learningid)
)
GO

create table learningObjectKeyword (
  learningId   nvarchar(85) not null,
  keywordIndex int          not null,
  keywordText  ntext        null,
  constraint PK_LEARNINGOBJECTKEYWORD primary key (learningId, keywordIndex)
)
GO

create table lo_static (
  learningid   nvarchar(85) not null,
  sessionid    nvarchar(15) not null,
  createdate   datetime     not null,
  n_enrollment int          not null,
  n_withdrawn  int          not null,
  n_finish     int          not null,
  n_kcopen     int          not null,
  pct_activity float        not null,
  constraint PK_LO_STATIC primary key (learningid, sessionid, createdate)
)
GO

create table locosts (
  id                  nvarchar(15)        not null,
  learningid          nvarchar(85)        not null,
  cost                numeric(15, 2)      not null,
  costtype            smallint            not null,
  currency            nvarchar(15)        not null,
  priceinfo           nvarchar(300)       null,
  sessionid           nvarchar(15)        null,
  payment_method      char(1) default 'C' not null,
  update_meaning      nvarchar(8)         null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  charged_costcenter  nvarchar(45)        null,
  constraint PK_LOCOSTS primary key (id)
)
GO

create unique index LOCOST_UN on locosts (learningid, sessionid, payment_method)
GO

create table lp_session_cost (
  session_cost_id     nvarchar(15)  not null,
  sessionid           nvarchar(15)  not null,
  currid              nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_LP_SESSION_COST primary key (session_cost_id, sessionid, currid)
)
GO

create table masterquestion (
  id                  nvarchar(85)  not null,
  testid              nvarchar(85)  not null,
  sectionnum          smallint      not null,
  questionnum         smallint      not null,
  pagenum             smallint      not null,
  questionid          nvarchar(85)  not null,
  qtype               smallint      not null,
  weight              float         not null,
  minuspoints         float         not null,
  questionpoolid      int default 0 not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_MASTERQUESTION PRIMARY KEY (id),
  constraint UNQ_MASTERQUESTION UNIQUE (testid, sectionnum, questionnum)
)
GO

create index NUK_MASTERQUESTION_1 on masterquestion (questionid)
GO

create table mastersection (
  id                     nvarchar(15)            not null,
  testid                 nvarchar(85)            not null,
  sectionnum             smallint                not null,
  nquestions             int                     not null,
  timelimit              int                     not null,
  layouttype             smallint                not null,
  testtype               smallint                not null,
  reqpoints              float                   null,
  maxpoints              float                   null,
  title                  nvarchar(255)           not null,
  description            nvarchar(255)           null,
  sectiontype            int                     not null,
  markType               nvarchar(1) default 'M' not null,
  randFixed              char(1) default 'N'     not null,
  weighting_option       char(1) default 'Q'     not null,
  update_meaning         nvarchar(8)             null,
  last_updated           datetime                null,
  updater_legal_name     nvarchar(250)           null,
  update_meaning_text    ntext                   null,
  last_updater_uid       nvarchar(85)            null,
  maxtimeperquestion     int default 0           not null,
  maxtimeperquestiontype int default -1          not null,
  constraint PK_MASTERSECTION primary key (id),
  constraint UK_MASTERSECTION unique (testid, sectionnum)
)
GO

create table mastertest (
  testid                         nvarchar(85)                not null,
  owner                          nvarchar(85)                not null,
  editcontrol                    smallint                    not null,
  autoscore                      nvarchar(1)                 not null,
  takelimit                      int                         not null,
  reviewable                     nvarchar(1)                 not null,
  allowhints                     nvarchar(1)                 not null,
  dateentered                    datetime                    not null,
  reqpoints                      float                       null,
  maxpoints                      float                       null,
  title                          nvarchar(255)               not null,
  description                    nvarchar(255)               null,
  stylesheet                     nvarchar(30)                not null,
  testuse                        int                         not null,
  longDescription                ntext                       null,
  maxTimeAllowed                 decimal(20)                 null,
  validFrom                      datetime                    null,
  validTo                        datetime                    null,
  allowPrint                     char(1) default 'N'         not null,
  allowResume                    char(1) default 'N'         not null,
  minimum_retake_interval_amount int default '0'             not null,
  minimum_retake_interval_unit   smallint default '5'        not null,
  allowscoredisplay              char(1) default 'Y'         not null,
  allowNavigation                char(1)                     null,
  allowIdDisplay                 char(1)                     null,
  password                       nvarchar(20)                null,
  disallowAbandon                CHAR(1)                     NULL,
  workflowStatus                 char(1)                     null,
  isPracticeTest                 char(1)                     null,
  allowImmediateQFeedback        char(1)                     null,
  parenttestid                   nvarchar(85)                null,
  resitDifference                float default 0,
  resit                          char(1) default 'N'         not null,
  resitParentTestId              nvarchar(85)                null,
  mentor_password                nvarchar(20)                null,
  is_template                    char(1)                     null,
  reviewQuestion                 char(14) default 'NOREVIEW' not null,
  markType                       nvarchar(1) default 'M'     not null,
  certid                         numeric(8)                  null,
  showmark                       char(1) default 'Y'         not null,
  reviewtype                     char(1) default 'A'         not null,
  maxtimeperquestion             int default 0               not null,
  pool_summary_lvl               INT default 0               not null,
  fail_email_id                  nvarchar(15)                null,
  retake_notif_email_id          nvarchar(15)                null,
  creator_uid                    nvarchar(85)                null,
  last_updated                   datetime                    null,
  last_updater_uid               nvarchar(85)                null,
  updater_legal_name             nvarchar(250)               null,
  update_meaning_text            ntext                       null,
  update_meaning                 nvarchar(8)                 null,
  constraint PK_MASTERTEST primary key (testid),
  constraint FK_MASTERTEST_CERTID foreign key (certid) references certifications (certid)
)
GO

create table messages (
  msgid           integer             not null,
  userid          nvarchar(85)        not null,
  fromdate        datetime            not null,
  fromuserid      nvarchar(85)        not null,
  readindicator   nvarchar(1)         not null,
  confirmationind nvarchar(1)         not null,
  subject         nvarchar(255)       not null,
  contentid       numeric(16)         not null,
  recipienttype   char(1) default 'T' not null,
  constraint PK_MESSAGES primary key (msgid)
)
GO

create index NUK_MSG_USER on messages (userid)
GO

create table msgContent (
  contentid   numeric(16)  not null,
  link        int          not null,
  refcount    int          not null,
  dateentered datetime     not null,
  userid      nvarchar(85) not null,
  attachments nvarchar(1)  not null,
  text1       ntext        null,
  constraint PK_MSGCONTENT primary key (contentid)
)
GO

create table msgContent_ekpfiles (
  contentid    numeric(16)  not null,
  repositoryid nvarchar(15) not null,
  constraint PK_MSGCONTENT_EKPFILES primary key (contentid, repositoryid)
)
GO

create table notepad (
  userid     nvarchar(85) not null,
  learningid nvarchar(85) not null,
  notetext   ntext        null,
  constraint PK_NOTEPAD primary key (userid, learningid)
)
GO

create table objectives_topics (
  learningid          nvarchar(85)   not null,
  entrynum            tinyint        not null,
  description         nvarchar(2000) not null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_OBJECTIVES_TOPICS primary key (learningid, entrynum)
)
GO

create table otherObjects (
  learningid          nvarchar(85)  not null,
  learningType        nvarchar(1)   not null,
  speaker_author      nvarchar(50)  null,
  publisher_institute nvarchar(50)  null,
  location            nvarchar(255) null,
  referenceno         nvarchar(40)  null,
  enrolldeadline      datetime      null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_OTHEROBJECTS primary key (learningid)
)
GO

create index NUK_OTHER_LTYPE on otherObjects (learningType)
GO

create table owners (
  learningid          nvarchar(85)  not null,
  userid              nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_OWNERS primary key (learningid, userid)
)
GO

create table peerComments (
  commentid    int          not null,
  learningid   nvarchar(85) not null,
  learningType nvarchar(1)  null,
  userid       nvarchar(85) not null,
  datecreated  datetime     not null,
  rating       smallint     not null,
  description1 ntext        null,
  nUseful      int          not null,
  nFeedback    int          not null,
  constraint PK_PEERCOMMENTS primary key (commentid)
)
GO

create index NUK_PEER_LID on peerComments (learningid)
GO

create table permissions_operator (
  objectid       nvarchar(85) not null,
  objecttype     nvarchar(5)  not null,
  permissioncode int          not null,
  operator       nvarchar(3)  not null,
  constraint PK_PERMISSIONS_OPERATOR primary key (objectid, objecttype, permissioncode)
)
GO

create table permissions (
  objectid       nvarchar(85) not null,
  objecttype     nvarchar(5)  not null,
  identityid     nvarchar(85) not null,
  permissioncode int          not null,
  identitytype   nvarchar(45) not null,
  constraint PK_PERMISSION primary key (objectid, objecttype, identityid, permissioncode, identitytype),
  constraint PERMISSIONS_PERMISSIONS_O_FK1 foreign key (objectid, objecttype, permissioncode) references permissions_operator (objectid, objecttype, permissioncode)
)
GO

create index idx_ekpfilepermissionsusers on permissions (objectid, identityid, objecttype)
GO

create index idx_ekpfilepermissionsusers_us on permissions (identityid)
GO

create index nuk_objtype_perm_permissions on permissions (objecttype, permissioncode)
GO

create index idx_ekpfilepermissionsobject on permissions (objectid, objecttype)
GO

insert into permissions_operator values ('*DEFAULT*', 'EP', 2, 'OR')
GO

insert into permissions_operator values ('*ROOT*', 'CT', 2, 'OR')
GO

insert into permissions_operator values ('0', 'CT', 2, 'OR')
GO

insert into permissions_operator values ('*DEFAULT*', 'TC', 1, 'OR')
GO

insert into permissions values ('*DEFAULT*', 'EP', 'A', 2, 'R')
GO

insert into permissions values ('*ROOT*', 'CT', '*ROOT*', 2, 'O')
GO

insert into permissions values ('0', 'CT', '*ROOT*', 2, 'O')
GO

insert into permissions values ('*DEFAULT*', 'TC', '*ROOT*', 1, 'O')
GO

INSERT INTO permissions_operator VALUES ('0', 'CSB', 1, 'OR')
GO

INSERT INTO permissions_operator VALUES ('0', 'CSB', 2, 'OR')
GO

INSERT INTO permissions VALUES ('0', 'CSB', '*ROOT*', 1, 'O')
GO

INSERT INTO permissions VALUES ('0', 'CSB', '*ROOT*', 2, 'O')
GO

INSERT INTO permissions_operator VALUES ('0', 'SCAT', 1, 'OR')
GO

INSERT INTO permissions_operator VALUES ('0', 'SCAT', 2, 'OR')
GO

INSERT INTO permissions VALUES ('0', 'SCAT', '*ROOT*', 1, 'O')
GO

INSERT INTO permissions VALUES ('0', 'SCAT', '*ROOT*', 2, 'O')
GO

create table preferences (
  userid     NVARCHAR(85) not null,
  preference NVARCHAR(85) not null,
  constraint PK_PREFERENCES primary key (preference, userid)
)
GO

create table prereq (
  learningid          nvarchar(85)  not null,
  prereqid            nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_PREREQ primary key (learningid, prereqid),
  constraint fk_prereq_learningid foreign key (learningid)
  references learningObject (learningid),
  constraint fk_prereq_prereqid foreign key (prereqid) references learningObject (learningid)
)
GO

create table qattributes (
  questionid          nvarchar(85)   not null,
  imageobject         varchar(255)   null,
  audioobject         varchar(255)   null,
  videoobject         varchar(255)   null,
  otherobject         nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QATTRIBUTES primary key (questionid)
)
GO

create table question (
  questionid          nvarchar(85)         not null,
  qtype               smallint             not null,
  difficulty          float                not null,
  owner               nvarchar(85)         not null,
  editcontrol         smallint             not null,
  hint                ntext                null,
  prenote             ntext                null,
  postnote            ntext                null,
  qtext               ntext                null,
  plain_text          ntext                null,
  searchText          nvarchar(1000)       null,
  updater_legal_name  nvarchar(250)        null,
  update_meaning_text ntext                null,
  category            smallint             null,
  allowPartialCredit  char(1)              null,
  weighting           float default 1      not null,
  referenceinfo       nvarchar(255)        null,
  qstatus             int default 0        not null,
  qcomments           nvarchar(512)        null,
  selectfrequency     float default 0      not null,
  usageTotal          int default 0        not null,
  usageLimit          int default 99999    not null,
  expiry_date         datetime             null,
  version             int default 1        not null,
  archivereferenceid  nvarchar(85)         null,
  archivecreatedate   datetime             null,
  writer              nvarchar(85)         null,
  writer_comments     nvarchar(255)        null,
  reviewer            nvarchar(85)         null,
  reviewer_comments   nvarchar(255)        null,
  approver            nvarchar(85)         null,
  approver_comments   nvarchar(255)        null,
  rejectedby          nvarchar(85)         null,
  reject_comments     nvarchar(255)        null,
  edittime            nvarchar(85)         null,
  reviewtime          nvarchar(85)         null,
  approvetime         nvarchar(85)         null,
  rejecttime          nvarchar(85)         null,
  is_ext_flagged      CHAR(1) DEFAULT 'N'  NOT NULL,
  creator_uid         nvarchar(85)         null,
  datecreated         datetime             null,
  last_updated        datetime             null,
  last_updater_uid    nvarchar(85)         null,
  update_meaning      nvarchar(8)          null,
  constraint PK_QUESTION primary key (questionid)
)
GO

create table question_stats (
  questionid   nvarchar(85) not null,
  totalwrong   int          not null,
  totalcorrect int          not null,
  constraint PK_QUESTION_STATS primary key (questionid)
)
GO

create table question_type1 (
  questionid          nvarchar(85)        not null,
  qattributes         smallint            not null,
  nchoices            smallint            not null,
  answer              smallint            null,
  qtext1              nvarchar(255)       null,
  qtext2              ntext               null,
  hint                nvarchar(1000)      null,
  choice1             nvarchar(1000)      null,
  choice2             nvarchar(1000)      null,
  choice3             nvarchar(1000)      null,
  choice4             nvarchar(1000)      null,
  choice5             nvarchar(1000)      null,
  choice6             nvarchar(1000)      null,
  choice7             nvarchar(1000)      null,
  choice8             nvarchar(1000)      null,
  choice9             nvarchar(1000)      null,
  choice10            nvarchar(1000)      null,
  prenote             nvarchar(1000)      null,
  postnote            nvarchar(1000)      null,
  fixedOrder          CHAR(1) DEFAULT 'Y' NOT NULL,
  update_meaning      nvarchar(8)         null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  constraint PK_QUESTION_TYPE1 primary key (questionid)
)
GO

create table question_type2 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       not null,
  answer              smallint       null,
  qtext1              nvarchar(255)  null,
  qtext2              ntext          null,
  hint                nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE2 primary key (questionid)
)
GO

create table question_type3 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       not null,
  nchoices            smallint       not null,
  qtext1              nvarchar(255)  null,
  qtext2              ntext          null,
  hint                nvarchar(1000) null,
  choice1             nvarchar(1000) null,
  choice2             nvarchar(1000) null,
  choice3             nvarchar(1000) null,
  choice4             nvarchar(1000) null,
  choice5             nvarchar(1000) null,
  choice6             nvarchar(1000) null,
  choice7             nvarchar(1000) null,
  choice8             nvarchar(1000) null,
  choice9             nvarchar(1000) null,
  choice10            nvarchar(1000) null,
  match1              nvarchar(1000) null,
  match2              nvarchar(1000) null,
  match3              nvarchar(1000) null,
  match4              nvarchar(1000) null,
  match5              nvarchar(1000) null,
  match6              nvarchar(1000) null,
  match7              nvarchar(1000) null,
  match8              nvarchar(1000) null,
  match9              nvarchar(1000) null,
  match10             nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE3 primary key (questionid)
)
GO

create table question_type4 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       not null,
  qtext1              nvarchar(255)  null,
  qtext2              ntext          null,
  hint                nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE4 primary key (questionid)
)
GO

create table question_type5 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       not null,
  qtext1              nvarchar(255)  null,
  qtext2              ntext          null,
  answer              nvarchar(1000) null,
  hint                nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE5 primary key (questionid)
)
GO

create table question_type6 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       null,
  qtext1              nvarchar(255)  null,
  qtext2              ntext          null,
  spread              smallint       null,
  lowlabel            nvarchar(85)   null,
  highlabel           nvarchar(85)   null,
  hint                nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE6 primary key (questionid)
)
GO

create table question_type7 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       not null,
  nchoices            smallint       not null,
  answer              int            null,
  fixedOrder          nvarchar(1)    null,
  singleChoice        nvarchar(1)    null,
  qtext1              ntext          null,
  hint                nvarchar(1000) null,
  choice1             nvarchar(1000) null,
  choice2             nvarchar(1000) null,
  choice3             nvarchar(1000) null,
  choice4             nvarchar(1000) null,
  choice5             nvarchar(1000) null,
  choice6             nvarchar(1000) null,
  choice7             nvarchar(1000) null,
  choice8             nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE7 primary key (questionid)
)
GO

create table question_type8 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       not null,
  qtext               ntext          null,
  columnheading1      nvarchar(1000) null,
  columnheading2      nvarchar(1000) null,
  columnheading3      nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  hint                nvarchar(1000) null,
  spread              smallint       not null,
  lowlabel            nvarchar(20)   null,
  highlabel           nvarchar(20)   null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE8 primary key (questionid)
)
GO

create table question_type8_item (
  questionid          nvarchar(85)   not null,
  qindex              smallint       not null,
  qtext               nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE8_ITEM primary key (questionid, qindex)
)
GO

create table registeredevents (
  eventid             nvarchar(15)  not null,
  event_type          char(1)       not null,
  email_message_id    nvarchar(15)  not null,
  learningid          nvarchar(85)  not null,
  parm1_ndays         int           null,
  parm2_freq          int           null,
  parm3_interval      int           null,
  parm4_nmsgs_limit   int           null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_REGISTEREDEVENTS primary key (eventid, event_type, email_message_id, learningid)
)
GO

create table registration (
  id                  nvarchar(15)  not null,
  eventid             nvarchar(15)  not null,
  userid              nvarchar(85)  not null,
  transcriptid        nvarchar(15)  not null,
  studentstatus       int           not null,
  datecreated         datetime      not null,
  currTranscriptid    nvarchar(15)  null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_REGISTRATION primary key (id),
  constraint UK_REGISTRATION unique (eventid, userid)
)
GO

create index nuk_reg_tran on registration (transcriptid)
GO

create index nuk_reg_user on registration (userid, eventid)
GO

create table reminders (
  userid      nvarchar(85)  not null,
  postingdate datetime      not null,
  notetext    nvarchar(255) null,
  constraint PK_REMINDERS primary key (userid, postingdate)
)
GO

create index pdate on reminders (postingdate)
GO

create table report_column (
  tableid        int          not null,
  table_desc     nvarchar(50) not null,
  column_name    nvarchar(30) not null,
  column_desc    nvarchar(30) not null,
  access_control varchar(255) null,
  constraint PK_REPORT_COLUMN primary key (tableid, column_name)
)
GO

create table report_security (
  sqlid      nvarchar(15) not null,
  permission ntext        not null,
  constraint PK_REPORT_SECURITY primary key (sqlid)
)
GO

create table report_sql (
  sqlid       nvarchar(15) not null,
  tableid     int          not null,
  column_name nvarchar(30) not null,
  position    int          not null,
  criteria    varchar(255) not null,
  sort_by     nvarchar(10) not null,
  group_by    smallint     not null,
  algorithm   varchar(255) not null,
  constraint PK_REPORT_SQL primary key (sqlid, tableid, column_name)
)
GO

create table reports (
  rpid         nvarchar(8)  not null,
  sqlid        nvarchar(15) not null,
  owner        nvarchar(85) not null,
  title        nvarchar(85) not null,
  style        int          not null,
  date_created datetime     not null,
  html         ntext        null,
  constraint PK_REPORTS primary key (sqlid)
)
GO

create table roleSecurity (
  userrole      nvarchar(85) not null,
  feature       int          not null,
  securitylevel smallint     not null,
  constraint PK_ROLESECURITY primary key (userrole, feature)
)
GO

create table sequenceKeys (
  nodeid         int         not null,
  keyid          int         not null,
  sequenceNumber numeric(16) null,
  constraint PK_SEQUENCEKEYS primary key (keyid, nodeid)
)
GO

create table session_cost (
  session_cost_id     nvarchar(15)  not null,
  extra_info          nvarchar(50)  null,
  amount              float         not null,
  exchange_rate       float         not null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  cost_category_id    nvarchar(15)  not null,
  currency            nvarchar(15)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_SESSION_COST primary key (session_cost_id)
)
GO

create table skills (
  skillid     nvarchar(15) not null,
  dateentered datetime     not null,
  skstatus    smallint     not null,
  category    nvarchar(50) not null,
  description nvarchar(50) not null,
  testid      nvarchar(85) not null,
  constraint PK_SKILLS primary key (skillid)
)
GO

create table supervises (
  userid     nvarchar(85)             not null,
  id         nvarchar(15)             not null,
  group_type char(1) default 'O'      not null,
  constraint PK_SUPERVISES primary key (userid, id, group_type)
)
GO

create table survey (
  userid      nvarchar(85) not null,
  testid      nvarchar(85) not null,
  instanceid  nvarchar(15) not null,
  datecreated datetime     not null,
  teststatus  smallint     not null,
  constraint PK_SURVEY primary key (instanceid)
)
GO

create index nuk_survey_test on survey (testid)
GO

create table table_available (
  tableid        int          not null,
  table_name     nvarchar(30) not null,
  pri_key        nvarchar(30) not null,
  access_allowed smallint     not null,
  constraint PK_TABLE_AVAILABLE primary key (tableid)
)
GO

create table testinstance (
  instanceid          nvarchar(15)          not null,
  testuse             smallint              not null,
  eventid             nvarchar(15)          null,
  transcriptid        nvarchar(15)          null,
  userid              nvarchar(85)          not null,
  testid              nvarchar(85)          not null,
  teststatus          smallint              not null,
  teststart           datetime              not null,
  testfinish          datetime              not null,
  score               float                 not null,
  weightedscore       float                 not null,
  nquestions          int                   not null,
  ncorrect            int                   not null,
  reviewable          nvarchar(1)           not null,
  sessionID           nvarchar(15)          null,
  questionnum         smallint              not null,
  sectionnum          smallint              not null,
  preview             char(1) default 'N'   not null,
  passFailFlag        CHAR(1)               NULL,
  maxscore            float default 0.0     NOT NULL,
  mekp_sync           datetime              null,
  is_pre_enrollment   char(1)               null,
  update_meaning      nvarchar(8)           null,
  last_updated        datetime              null,
  updater_legal_name  nvarchar(250)         null,
  update_meaning_text ntext                 null,
  last_updater_uid    nvarchar(85)          null,
  is_active           char(1) default ('Y') not null,
  constraint PK_TESTINSTANCE primary key (instanceid),
  constraint fk_testinstance_session_id foreign key (sessionID) references AICC_core (session_id)
)
GO

create index nuk_testi_tran on testinstance (transcriptid)
GO

create table testquestion (
  instanceid          nvarchar(15)        not null,
  sectionnum          smallint            not null,
  questionnum         smallint            not null,
  questionid          nvarchar(85)        not null,
  qtype               smallint            not null,
  guess               int                 null,
  answer              int                 null,
  points              float               not null,
  review              char(1) default 'N' not null,
  version             int default 1       not null,
  comments            nvarchar(1000)      null,
  choice_order        nvarchar(10)        null,
  update_meaning      nvarchar(8)         null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  elapsedTime         decimal(20)         null,
  weight              float default 0     not null,
  constraint PK_TESTQUESTION primary key (instanceid, sectionnum, questionnum),
  constraint FK_testquestion_questionid foreign key (questionid) references question (questionid)
)
GO

create table testquestion3 (
  instanceid          nvarchar(15)  not null,
  sectionnum          smallint      not null,
  questionnum         smallint      not null,
  questionid          nvarchar(85)  not null,
  gs1                 smallint      null,
  gs2                 smallint      null,
  gs3                 smallint      null,
  gs4                 smallint      null,
  gs5                 smallint      null,
  gs6                 smallint      null,
  gs7                 smallint      null,
  gs8                 smallint      null,
  gs9                 smallint      null,
  gs10                smallint      null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TESTQUESTION3 primary key (instanceid, sectionnum, questionnum)
)
GO

create table testquestion4 (
  instanceid          nvarchar(15)  not null,
  sectionnum          smallint      not null,
  questionnum         smallint      not null,
  questionid          nvarchar(85)  not null,
  text1               ntext         null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TESTQUESTION4 primary key (instanceid, sectionnum, questionnum)
)
GO

create table testquestion5 (
  instanceid          nvarchar(15)   not null,
  sectionnum          smallint       not null,
  questionnum         smallint       not null,
  questionid          nvarchar(85)   not null,
  fib                 nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_TESTQUESTION5 primary key (instanceid, sectionnum, questionnum)
)
GO

create table testquestion6 (
  instanceid          nvarchar(15)  not null,
  sectionnum          smallint      not null,
  questionnum         smallint      not null,
  questionid          nvarchar(85)  not null,
  rating              smallint      not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TESTQUESTION6 primary key (instanceid, sectionnum, questionnum)
)
GO

create table testquestion8 (
  instanceid          nvarchar(15)  not null,
  sectionnum          smallint      not null,
  questionnum         smallint      not null,
  itemindex           smallint      not null,
  questionid          nvarchar(85)  not null,
  rating1             smallint      null,
  rating2             smallint      null,
  rating3             smallint      null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TESTQUESTION8 primary key (instanceid, sectionnum, questionnum, itemindex)
)
GO

create table testsection (
  instanceid          nvarchar(15)  not null,
  sectionnum          smallint      not null,
  teststart           datetime      not null,
  testfinish          datetime      not null,
  score               float         not null,
  weightedscore       float         not null,
  nquestions          int           not null,
  maxpoints           float         not null,
  ncorrect            int           not null,
  elapsedTime         decimal(20)   null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TESTSECTION primary key (instanceid, sectionnum)
)
GO

create table trainingPlan (
  userid       nvarchar(85)  not null,
  dateprepared datetime      not null,
  preparedby   nvarchar(85)  not null,
  learningid   nvarchar(85)  not null,
  plancomment  nvarchar(255) null,
  doby         datetime      null,
  statusInd    nvarchar(1)   not null,
  priority     nvarchar(1)   not null,
  constraint PK_TRAININGPLAN primary key (userid, learningid)
)
GO

create table transcript (
  transcriptid             nvarchar(15)          not null,
  userid                   nvarchar(85)          not null,
  learningid               nvarchar(85)          not null,
  currid                   nvarchar(85)          null,
  learningType             nvarchar(1)           not null,
  eventid                  nvarchar(15)          not null,
  startdate                datetime              not null,
  finishdate               datetime              null,
  finalgrade               float                 null,
  studentstatus            int                   not null,
  progress                 nvarchar(10)          null,
  assessDate               datetime              null,
  attendance               nvarchar(1)           null,
  perfGradeid              smallint              null,
  sessionid                nvarchar(15)          null,
  creditTranscriptLink     nvarchar(15)          null,
  curriculumTranscriptLink nvarchar(15)          null,
  assessmentscore          nvarchar(20)          null,
  requestSource            int                   null,
  contentSessionID         nvarchar(15)          null,
  certSessionID            nvarchar(15)          null,
  enrollmentDate           datetime              null,
  remindercount            int                   null,
  costcenter               nvarchar(45)          null,
  confirmation_status      int                   null,
  reopen                   char(1)               null,
  isOpen                   char(1) default 'N',
  pre_assess_score         float                 null,
  post_assess_score        float                 null,
  usage_expiration_date    datetime              null,
  quick_eval               int                   null,
  notifyemail              char(85)              null,
  completion_deadline      datetime              null,
  deadline_expired         nvarchar(1)           null,
  revision_number          int                   null,
  ntraininghours           float default 0       not null,
  allowDappraiserAssess    char(1) default 'N'   not null,
  offline_key              NVARCHAR(32)          NULL,
  mekp_sync                datetime              null,
  send_notif_ew            char(1) default 'Y'   not null,
  update_meaning           nvarchar(8)           null,
  last_updated             datetime              null,
  updater_legal_name       nvarchar(250)         null,
  update_meaning_text      ntext                 null,
  last_updater_uid         nvarchar(85)          null,
  creator_uid              nvarchar(85)          null,
  datecreated              datetime              null,
  curr_total_time          nvarchar(25)          null,
  attempts_allowed         int                   null,
  grading_method           char(1)               null,
  attempts_count           int                   null,
  completion_method        char(1)               null,
  final_score              nvarchar(20)          null,
  constraint PK_TRANSCRIPT primary key (transcriptid),
  constraint fk_transcript_userid foreign key (userid)
  references users (userid),
  constraint fk_transcript_learningid foreign key (learningid)
  references learningObject (learningid),
  constraint fk_transcript_contentSessionID foreign key (contentSessionID)
  references AICC_core (session_id)
)
GO

create index NUK_TRAN_LID on transcript (learningid)
GO

create index NUK_TRAN_SID on transcript (contentSessionID)
GO

create index NUK_TRAN_TRANLINK on transcript (curriculumTranscriptLink)
GO

create index nuk_tran_event on transcript (eventid)
GO

create index nuk_tran_user on transcript (userid)
GO

create index NUK_TRAN_ENROLLMENTDATE on transcript (enrollmentDate)
GO

create index NUK_TRAN_CREDIT_LINK on transcript (creditTranscriptLink)
GO

IF EXISTS(SELECT
            TABLE_NAME
          FROM INFORMATION_SCHEMA.VIEWS
          WHERE TABLE_NAME = 'valid_transcript_vw')
  DROP VIEW valid_transcript_vw
GO

CREATE VIEW valid_transcript_vw AS
  SELECT
    transcriptid,
    userid,
    learningid,
    currid,
    learningType,
    eventid,
    startdate,
    finishdate,
    finalgrade,
    studentstatus,
    progress,
    assessDate,
    attendance,
    perfGradeid,
    sessionid,
    creditTranscriptLink,
    curriculumTranscriptLink,
    assessmentscore,
    requestSource,
    contentSessionID,
    certSessionID,
    enrollmentDate,
    remindercount,
    costcenter,
    confirmation_status,
    reopen,
    isOpen,
    pre_assess_score,
    post_assess_score,
    usage_expiration_date,
    quick_eval,
    notifyemail,
    completion_deadline,
    deadline_expired,
    revision_number,
    ntraininghours,
    allowDappraiserAssess,
    offline_key,
    mekp_sync,
    send_notif_ew,
    update_meaning,
    last_updated,
    updater_legal_name,
    update_meaning_text,
    last_updater_uid,
    creator_uid,
    datecreated,
    curr_total_time,
    attempts_allowed,
    grading_method,
    attempts_count,
    completion_method,
    final_score
  FROM transcript
  WHERE studentstatus NOT IN (8, 9, 10)
  UNION ALL
  SELECT
    a.transcriptid,
    a.userid,
    a.learningid,
    a.currid,
    a.learningType,
    a.eventid,
    b.startdate,
    b.finishdate,
    b.finalgrade,
    b.studentstatus,
    b.progress,
    b.assessDate,
    b.attendance,
    b.perfGradeid,
    a.sessionid,
    NULL,
    a.curriculumTranscriptLink,
    b.assessmentscore,
    a.requestSource,
    b.contentSessionID,
    b.certSessionID,
    a.enrollmentDate,
    b.remindercount,
    b.costcenter,
    b.confirmation_status,
    b.reopen,
    a.isOpen,
    b.pre_assess_score,
    b.post_assess_score,
    b.usage_expiration_date,
    b.quick_eval,
    b.notifyemail,
    b.completion_deadline,
    b.deadline_expired,
    b.revision_number,
    b.ntraininghours,
    b.allowDappraiserAssess,
    b.offline_key,
    b.mekp_sync,
    b.send_notif_ew,
    b.update_meaning,
    b.last_updated,
    b.updater_legal_name,
    a.update_meaning_text,
    a.last_updater_uid,
    a.creator_uid,
    a.datecreated,
    b.curr_total_time,
    b.attempts_allowed,
    b.grading_method,
    b.attempts_count,
    b.completion_method,
    b.final_score
  FROM transcript a, transcript b
  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

IF EXISTS(SELECT
            TABLE_NAME
          FROM INFORMATION_SCHEMA.VIEWS
          WHERE TABLE_NAME = 'cschedule_vw')
  DROP VIEW cschedule_vw
GO

CREATE VIEW cschedule_vw AS
  SELECT
    cschedule.*,
    (select
    count(*)
     from transcript
     where transcript.eventid = cschedule.eventid and studentstatus in (2, 3)) nregistered
  FROM cschedule
GO

create table tutor (
  id                  nvarchar(15)  not null,
  learningid          nvarchar(85)  not null,
  eventid             nvarchar(15)  null,
  sessionid           nvarchar(15)  null,
  userid              nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TUTOR primary key (id)
)
GO

create index UK_TUTOR on tutor (learningid, eventid, sessionid, userid)
GO

create index NUK_TUTOR_USERS on tutor (userid)
GO

CREATE VIEW tutor_vw AS
  SELECT
    DISTINCT userid,
    learningid,
    eventid
  FROM tutor
GO

create table userGroup (
  groupid   int           not null,
  name      nvarchar(255) null,
  owner     nvarchar(85)  not null,
  grouptype char(1)       not null
)
GO

create table userGroupMember (
  userid  nvarchar(85) not null,
  groupid int          not null
)
GO

create index NUK_USERGROUPMEM_GID on userGroupMember (groupid)
GO

create index NUK_USERGROUPMEM_UID on userGroupMember (userid)
GO

create table user_custom_attr (
  userid nvarchar(85) not null,
  attr01 nvarchar(85) null,
  attr02 nvarchar(85) null,
  attr03 nvarchar(85) null,
  attr04 nvarchar(85) null,
  attr05 nvarchar(85) null,
  attr06 nvarchar(85) null,
  attr07 nvarchar(85) null,
  attr08 nvarchar(85) null,
  attr09 nvarchar(85) null,
  attr10 nvarchar(85) null,
  attr11 nvarchar(85) null,
  attr12 nvarchar(85) null,
  constraint PK_USER_CUSTOM_ATTR primary key (userid)
)
GO

create table user_orgs (
  userid         nvarchar(85)        not null,
  orgsequence    int                 not null,
  orgid          nvarchar(15)        not null,
  membershiptype char(1)             not null,
  domaintype     char(1) default 'P' not null,
  constraint PK_USER_ORGS primary key (userid, orgsequence, orgid, membershiptype, domaintype),
  constraint fk_user_orgs_org_id foreign key (orgid) references code_orgstructure (orgid)
)
GO

create index nuk_userorgs on user_orgs (orgid)
GO

create index NUK_USER_ORGS_UID on user_orgs (userid)
GO

create table userbio (
  userid              nvarchar(85) not null,
  viewingConstraint   nvarchar(1)  not null,
  bio                 ntext        null,
  bio_work_experience ntext        null,
  bio_edu_background  ntext        null,
  bio_interests       ntext        null,
  public_activity     CHAR(1)      NOT NULL,
  other_info          ntext        null,
  constraint PK_USERBIO primary key (userid)
)
GO

create table userinfo (
  userid                 nvarchar(85)        not null,
  picture                varchar(100)        null,
  b_companyname          nvarchar(50)        null,
  b_address1             nvarchar(150)       null,
  b_address2             nvarchar(150)       null,
  b_city                 nvarchar(50)        null,
  b_province_state       nvarchar(50)        null,
  b_country              nvarchar(3)         null,
  b_phone                nvarchar(85)        null,
  b_mobile               nvarchar(15)        null,
  b_postalcode           nvarchar(10)        null,
  fax                    varchar(15)         null,
  e_country              nvarchar(3)         null,
  employeestatus         char(1)             null,
  employeeid             nvarchar(85)        null,
  personalid             nvarchar(85)        null,
  taxid                  nvarchar(85)        null,
  returndate             datetime            null,
  regular_temp           char(1) default 'R' not null,
  full_part_time         char(1) default 'F' not null,
  contractor             nvarchar(20)        null,
  language               nvarchar(3)         null,
  business_unit_code     nvarchar(85)        null,
  business_unit_desc     nvarchar(85)        null,
  joblocation            nvarchar(85)        null,
  taxlocation            nvarchar(85)        null,
  reloc_willingness      char(1)             null,
  reloc_desired_location nvarchar(250)       null,
  reloc_financial_assist char(1)             null,
  reloc_reason           ntext               null,
  resume                 varchar(100)        null,
  resume_byte_size       int                 null,
  resume_uploader_uid    nvarchar(85)        null,
  resume_upload_date     datetime            null,
  constraint PK_USERINFO primary key (userid)
)
GO

create table userprefs (
  userid             nvarchar(85)               not null,
  contentServer      nvarchar(20)               not null,
  ui                 int                        not null,
  languagepref       tinyint                    not null,
  autoMailList       nvarchar(1)                not null,
  autoRegMsg         nvarchar(1)                not null,
  useExternalMail    nvarchar(1)                not null,
  initialURL         int                        null,
  resultsPerPage     int default 25             not null,
  catalogid          int                        not null,
  userPref1          nvarchar(1)                not null,
  userPref2          nvarchar(1),
  time_zone          nvarchar(85)               null,
  email_forward_type char(1) default 'N'        not null,
  forward_to_email   nvarchar(85)               null,
  constraint PK_USERPREFS primary key (userid),
  CONSTRAINT FK_USERPREFS_UI FOREIGN KEY (ui) REFERENCES screenLayout (ui)
)
GO

create table userstats (
  userid                       nvarchar(85)                      not null,
  lastlogon                    datetime                          null,
  lastpwchange                 datetime                          null,
  nlogons                      int                               not null,
  nattempts                    int                               not null,
  nmsgspending                 int                               not null,
  elapsedloginseconds          int                               not null,
  nlogouts                     int                               not null,
  lastAutoEnrollUpdate         datetime                          null,
  first_login                  datetime                          null,
  firstlogonfailure            datetime                          null,
  lastlogonfailure             datetime                          null,
  nlogonfailure                int                               null,
  last_catalog_id              nvarchar(15)                      null,
  requirepwchange              char(1) default 'N'               not null,
  lastactivedate               datetime                          null,
  mekp_activation              datetime                          null,
  mekp_last_sync               datetime                          null,
  last_job_auto_assign_update  datetime                          null,
  last_crs_offline_auto_enroll datetime                          null,
  last_job_offline_auto_assign datetime                          null,
  terms_of_use_accept_date     datetime                          null,
  constraint PK_USERSTATS primary key (userid)
)
GO

create table withdraw_deduction (
  policy_id           nvarchar(15)  not null,
  day_before_start    int           not null,
  deduction           float         not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_WITHDRAW_DEDUCTION primary key (policy_id, day_before_start)
)
GO

create table withdrawnStudents (
  id                      nvarchar(15)    not null,
  learningid              nvarchar(85)    not null,
  eventid                 nvarchar(15)    not null,
  userid                  nvarchar(85)    not null,
  transcriptid            nvarchar(15)    not null,
  datecreated             datetime        not null,
  substitute_transcriptid nvarchar(85)    null,
  update_meaning          nvarchar(8)     null,
  last_updated            datetime        null,
  updater_legal_name      nvarchar(250)   null,
  update_meaning_text     ntext           null,
  last_updater_uid        nvarchar(85)    null,
  studentstatus           int default 5   not null,
  constraint PK_WITHDRAWNSTUDENTS primary key (id)
)
GO

create index nuk_withdrawn_students on withdrawnStudents (userid, transcriptid)
GO

create index nuk_withdrawn on withdrawnStudents (learningid, eventid)
GO

create table report_wizard (
  report_id        nvarchar(15)                    not null,
  name             nvarchar(255)                   null,
  owner            nvarchar(85)                    null,
  report_type      nvarchar(50)                    not null,
  distinct_row     CHAR(1) DEFAULT 'N'             not null,
  separate_org     CHAR(1) DEFAULT 'Y'             not null,
  convert_timezone CHAR(1) DEFAULT 'N'             not null,
  report_format    nvarchar(10) DEFAULT 'HTML'     not null,
  csv_encoding     nvarchar(25) DEFAULT 'UTF-8'    not null,
  description      nvarchar(500)                   null,
  join_sql         ntext                           null,
  custom_filter    ntext                           null,
  creator_uid      nvarchar(85)                    null,
  datecreated      datetime                        null,
  last_updated     datetime                        null,
  last_updater_uid nvarchar(85)                    null,
  csv_format       char(1) default 'D'             not null,
  constraint PK_REPORT_WIZARD primary key (report_id)
)
GO

create table report_wizard_column (
  report_id         nvarchar(15)      not null,
  report_column_key nvarchar(50)      not null,
  ordering          tinyint default 0 not null,
  sorting           int               null,
  constraint PK_REPORT_WIZARD_COLUMN primary key (report_id, report_column_key),
  constraint FK_COLUMN_REF_REPORT_WIZARD foreign key (report_id) references report_wizard (report_id)
)
GO

create table report_wizard_aggregate (
  report_id         nvarchar(15)        not null,
  report_column_key nvarchar(50)        not null,
  is_avg            char(1)             not null,
  is_sum            char(1)             not null,
  is_max            char(1)             not null,
  is_min            char(1)             not null,
  is_count          char(1)             not null,
  is_groupby        char(1) default 'N' not null,
  is_distinct       char(1) default 'N' not null,
  constraint PK_REPORT_WIZARD_AGG primary key (report_id, report_column_key),
  constraint FK_COLUMN_REF_REPORT_AGGREGATE foreign key (report_id, report_column_key)
  references report_wizard_column (report_id, report_column_key)
)
GO

create table report_wizard_criteria (
  report_id         nvarchar(15) not null,
  report_column_key nvarchar(50) not null,
  operator1         nvarchar(15) not null,
  operator2         nvarchar(15) null,
  report_value      nvarchar(85) not null,
  constraint PK_REPORT_WIZARD_CRITERIA primary key (report_id, report_column_key, report_value),
  constraint FK_CRITERIA_REF_REPORT_WIZARD foreign key (report_id) references report_wizard (report_id)
)
GO

CREATE TABLE dynamic_attribute (
  attribute_id  nvarchar(15)                   NOT NULL,
  code          nvarchar(85)                   NULL,
  display_areas nvarchar(15)                   NULL,
  label_key     nvarchar(85)                   NULL,
  type          char(1) default 'F'            NOT NULL,
  sum_total     char(1) default 'N'            NOT NULL,
  attr_order    int                            NOT NULL,
  is_mandatory  char(1) default 'N'            NOT NULL,
  category      char(2)                        NOT NULL,
  domain_id     nvarchar(15) default 'DEFAULT' not null,
  CONSTRAINT PK_DYNAMIC_ATTR PRIMARY KEY (attribute_id),
  CONSTRAINT ak_dynamic_attribute_u1 UNIQUE (code, category)
)
GO

CREATE TABLE dynamic_attribute_dropdown_val (
  attribute_key        nvarchar(85) NOT NULL,
  attribute_id         nvarchar(15) NOT NULL,
  attribute_value_data nvarchar(85) NULL,
  label_key            nvarchar(85) NULL,
  CONSTRAINT PK_DYNAMIC_ATTR_DROPDOWN_VAL PRIMARY KEY (attribute_key, attribute_id),
  CONSTRAINT FK_DYNAMIC_ATTR_DD_ATTR_ID FOREIGN KEY (attribute_id) REFERENCES dynamic_attribute (attribute_id)
)
GO

CREATE INDEX NUK_CODE_DY_ATTR_X1 on dynamic_attribute_dropdown_val (attribute_id)
GO

create table lo_attribute (
  learningid           nvarchar(85)   not null,
  attribute_id         nvarchar(15)   not null,
  attribute_value_data nvarchar(2000) null,
  update_meaning       nvarchar(8)    null,
  last_updated         datetime       null,
  updater_legal_name   nvarchar(250)  null,
  update_meaning_text  ntext          null,
  last_updater_uid     nvarchar(85)   null,
  constraint PK_LO_ATTRIBUTE primary key (learningid, attribute_id),
  CONSTRAINT FK_LO_ATTRIBUTE_ATTR_ID FOREIGN KEY (attribute_id) REFERENCES dynamic_attribute (attribute_id)
)
GO

create table transcript_attribute (
  transcriptid         nvarchar(85)   not null,
  attribute_id         nvarchar(15)   not null,
  attribute_value_data nvarchar(2000) null,
  update_meaning       nvarchar(8)    null,
  last_updated         datetime       null,
  updater_legal_name   nvarchar(250)  null,
  update_meaning_text  ntext          null,
  last_updater_uid     nvarchar(85)   null,
  constraint PK_TRANSCRIPT_ATTR primary key (transcriptid, attribute_id),
  CONSTRAINT FK_TRANS_ATTR_ATTR_ID FOREIGN KEY (attribute_id) REFERENCES dynamic_attribute (attribute_id)
)
GO

create table course_template_frame (
  id               nvarchar(15) not null,
  frame_id         integer      not null,
  frame_type       nvarchar(15) null,
  styleSheet       nvarchar(55) null,
  velocityTemplate nvarchar(55) null,
  frame_length     integer      not null,
  is_pixel         char(1)      null,
  constraint PK_SCORM_FRAME_NAME primary key (id, frame_id),
  constraint FK_NAME_REF_COURSE_TEMP foreign key (id) references course_template (id)
)
GO

create table code_contentServer (
  cs_key         varchar(15)                not null,
  cs_name        nvarchar(60)               not null,
  cs_description nvarchar(85)               not null,
  cs_hostname    nvarchar(85)               not null,
  cs_scheme      varchar(10) default 'http' not null,
  constraint pk_code_contentServer primary key (cs_key)
)
GO

insert into code_contentServer (cs_key, cs_name, cs_description, cs_hostname, cs_scheme)
  values ('*ANY*', 'No content server', 'No content server specified', '*ANY*', 'http')
GO

create table contentserver_ipcheck (
  cs_key      varchar(15) not null,
  sequence_no int         not null,
  from_ip     varchar(20) not null,
  to_ip       varchar(20) not null,
  constraint pk_contentserver_ipcheck primary key (cs_key, sequence_no),
  constraint FK_CODE_CONTENTSERVER foreign key (cs_key) references code_contentServer (cs_key)
)
GO

create table code_compcategory (
  categoryid           varchar(15)   not null,
  parentid             varchar(15)   not null,
  description          nvarchar(85)  not null,
  extended_description nvarchar(500) not null,
  constraint pk_compcat_key primary key (categoryid),
  constraint fk_comp_category_parent foreign key (parentid)
  references code_compcategory (categoryid)
)
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('*ROOT*', '*ROOT*', 'All', 'All')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('*UNASSIGNED*', '*ROOT*', '(Unassigned)', '(Unassigned)')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP1', '*ROOT*', 'Management', 'Management')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP2', 'EKP1', 'Teamwork', 'Management/Teamwork')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP3', 'EKP1', 'Project Planning', 'Management/Project Planning')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP4', 'EKP1', 'Leadership', 'Management/Leadership')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP5', '*ROOT*', 'Technical', 'Technical')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP6', 'EKP5', 'Programming', 'Technical/Programming')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP7', 'EKP5', 'Databases', 'Technical/Databases')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP8', 'EKP5', 'Networks', 'Technical/Networks')
GO

insert into code_compcategory (categoryid, parentid, description, extended_description) values ('EKP9', 'EKP5', 'Administration', 'Technical/Administration')
GO

create table code_jobprofile_pool (
  poolid      int          not null,
  description nvarchar(85) not null,
  constraint pk_jp_pool_key primary key (poolid)
)
GO

insert into code_jobprofile_pool (poolid, description) values (0, 'General')
GO

create table job_profile (
  id                       nvarchar(15)              not null,
  name                     nvarchar(85)              not null,
  poolid                   int                       not null,
  description              ntext                     not null,
  is_active                char(1)                   not null,
  reference_code           nvarchar(85)              not null,
  categoryid               varchar(15)               not null,
  allows_manual_assignment char(1) default 'Y'       not null,
  allows_auto_assignment   char(1) default 'N'       not null,
  allows_self_assignment   char(1) default 'N'       not null,
  requires_all             char(1)                   not null,
  n_competencies_required  int                       not null,
  creator_uid              nvarchar(85)              null,
  datecreated              datetime                  null,
  last_updated             datetime                  null,
  last_updater_uid         nvarchar(85)              null,
  profile_type             varchar(30) default 'JOB' not null,
  updater_legal_name       nvarchar(250)             null,
  update_meaning_text      ntext                     null,
  constraint pk_job_profile primary key (id),
  constraint ak_job_profile_ref_code unique (reference_code),
  constraint fk_job_profile_category foreign key (categoryid) references code_compcategory (categoryid),
  constraint fk_jobprofile_pool_id foreign key (poolid) references code_jobprofile_pool (poolid)
)
GO

create table competency_model (
  model_id    int          not null,
  description nvarchar(85) not null,
  parentid    int          null,
  rootid      int          not null,
  ownerid     nvarchar(85) null,
  constraint pk_comp_model_key primary key (model_id),
  constraint fk_competency_model_key1 foreign key (parentid) references competency_model (model_id)
)
GO

create index competency_model_index1 on competency_model (parentid)
GO

insert into competency_model (model_id, description, rootid, ownerid) values (0, 'General', 0, 'ndadmin')
GO

create table competency_group (
  id             nvarchar(15)   not null,
  name           nvarchar(85)   not null,
  model_id       int            not null,
  description    nvarchar(2000) not null,
  is_active      char(1)        not null,
  reference_code nvarchar(85)   not null,
  categoryid     varchar(15)    not null,
  certid         numeric(8, 0)  null,
  constraint FK_COMP_GRP_CERTIFICATION foreign key (certid) references certifications (certid),
  constraint pk_competency_group primary key (id),
  constraint ak_comp_group_ref_code unique (reference_code),
  constraint fk_comp_group_category foreign key (categoryid) references code_compcategory (categoryid),
  constraint fk_compgrp_model foreign key (model_id) references competency_model (model_id)
)
GO

create table tms_responsibility_level_group (
  id       nvarchar(15)         not null,
  title    nvarchar(255)        null,
  internal char(1) default 'N'  not null,
  constraint PK_TMS_RESP_LEVEL_GROUP primary key (id)
)
GO

insert into tms_responsibility_level_group (id, title, internal) values ('*TMS*', 'Default Level Group', 'Y')
GO

create table tms_defaults (
  default_level_group nvarchar(15) not null,
  constraint FK_DEFAULT_LEVEL_GROUP foreign key (default_level_group) references tms_responsibility_level_group (id)
)
GO

insert into tms_defaults (default_level_group) values ('*TMS*')
GO

create table individual_competency (
  id                        nvarchar(15)                 not null,
  name                      nvarchar(85)                 not null,
  model_id                  int                          not null,
  description               ntext                        not null,
  is_active                 char(1)                      not null,
  reference_code            nvarchar(85)                 not null,
  allows_manual_award       char(1)                      not null,
  acquire_method            char(1)                      not null,
  requires_all              char(1)                      not null,
  allows_self_award         char(1)                      not null,
  categoryid                varchar(15)                  not null,
  creator_uid               nvarchar(85)                 null,
  datecreated               datetime                     null,
  last_updated              datetime                     null,
  last_updater_uid          nvarchar(85)                 null,
  n_completions_required    int default 0                not null,
  task_type                 varchar(30) default 'LEARN'  not null,
  source_name               nvarchar(255)                null,
  level_group_id            nvarchar(15) default '*TMS*' not null,
  min_wait_period           int default 0                not null,
  min_wait_unit             char(1) default 'D'          not null,
  auto_revoke               char(1) default 'N'          not null,
  auto_revoke_period        int default 0                not null,
  auto_revoke_unit          char(1) default 'D'          not null,
  auto_revoke_remind_period int                          NULL,
  auto_revoke_remind_unit   char(1) default 'D'          not null,
  send_revoked_email        char(1) default 'N'          not null,
  updater_legal_name        nvarchar(250)                null,
  update_meaning_text       ntext                        null,
  adhoc_exp_period          int default 0                not null,
  adhoc_exp_unit            char(1) default 'D'          not null,
  renewal_period            int default 0                not null,
  renewal_period_unit       char(1) default 'D'          not null,
  expiration_logic          char(1) default 'L'          not null,
  constraint pk_individual_competency primary key (id),
  constraint ak_comp_ref_code unique (reference_code),
  constraint fk_comp_category foreign key (categoryid) references code_compcategory (categoryid),
  constraint fk_comp_model foreign key (model_id) references competency_model (model_id),
  constraint fk_comp_model_lvl_gp foreign key (level_group_id) references tms_responsibility_level_group (id)
)
GO

create table competency_group_competency (
  competency_group_id nvarchar(15) not null,
  competency_id       nvarchar(15) not null,
  constraint pk_competency_group_competency primary key (competency_group_id, competency_id),
  constraint fk_competency_group foreign key (competency_group_id) references competency_group (id),
  constraint fk_competency foreign key (competency_id) references individual_competency (id)
)
GO

create table user_job_profile (
  user_id             nvarchar(85)        not null,
  job_profile_id      nvarchar(15)        not null,
  date_assigned       datetime            null,
  assigned_by         nvarchar(85)        null,
  assign_type         char(1)             null,
  status_flag         char(1) default 'N' not null,
  status_date         datetime            null,
  status_percent      float default 0     not null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  constraint pk_user_job_profile primary key (user_id, job_profile_id),
  constraint fk_user_job_profile_user_id foreign key (user_id)
  references users (userid),
  constraint fk_user_job_profile_job_profile_id foreign key (job_profile_id)
  references job_profile (id)
)
GO

create table tms_responsibility_level (
  id                  nvarchar(15)  not null, -- EKP000000004
  level_order         int           not null, -- e.g. 1, 2, 3, 4, 5, 6, 7
  code                nvarchar(255) not null, -- most likely just 1,2,3 etc but could also be "Bad", "Good", "Excellent"
  title               nvarchar(255) null, -- e.g. "follow", "assist", "apply", "enable", "ensure, advise", etc; nullable because we are not sure if importing libraries must have this value
  desc_text           ntext         null,
  level_group_id      nvarchar(15)  not null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TMS_RESP_LEVEL primary key (id),
  constraint FK_TMS_RESP_LEVEL_GROUP foreign key (level_group_id) references tms_responsibility_level_group (id),
  constraint UK_RESP_LEVEL_GROUP_ORDER unique (level_group_id, level_order)
)
GO

create table competency_level (
  id                  nvarchar(15)  not null, -- EKP000000004
  res_id              nvarchar(15)  not null, -- tms_responsibility_level id
  competency_id       nvarchar(15)  not null, -- individual_competency id
  desc_text           ntext         null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_COMPETENCY_LEVEL primary key (id),
  constraint FK_COMPETENCY_LEVEL_1 foreign key (res_id) references tms_responsibility_level (id),
  constraint FK_COMPETENCY_LEVEL_2 foreign key (competency_id) references individual_competency (id),
  constraint UK_COMP_RESP_LEVEL unique (competency_id, res_id)
)
GO

create table user_competency (
  user_id             nvarchar(85)   not null,
  competency_id       nvarchar(15)   not null,
  date_assigned       datetime       not null,
  assigned_by         nvarchar(85)   null,
  assessment_mode     char(1)        not null,
  assigned_comment    nvarchar(1000) null,
  competency_level_id nvarchar(15)   null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint pk_user_competency primary key (user_id, competency_id),
  constraint fk_user_comp_user_id foreign key (user_id)
  references users (userid),
  constraint fk_user_comp_comp_id foreign key (competency_id)
  references individual_competency (id),
  constraint fk_user_comp_level_id foreign key (competency_level_id)
  references competency_level (id),
  constraint fk_user_comp_assigned_by foreign key (assigned_by)
  references users (userid)
)
GO

create table course_competency (
  course_id                  nvarchar(85)        not null,
  competency_id              nvarchar(15)        not null,
  module_order               int default 1       not null,
  competency_target_level_id nvarchar(15)        null,
  mandatory                  char(1) default 'N' not null,
  course_revision            char(1) default 'A' not null,
  valid_for_period           int                 null,
  valid_for_unit             char(1) default 'D' not null,
  last_updated               datetime            null,
  updater_legal_name         nvarchar(250)       null,
  update_meaning_text        ntext               null,
  last_updater_uid           nvarchar(85)        null,
  constraint pk_course_competency primary key (course_id, competency_id),
  constraint fk_lo_comp_course_id foreign key (course_id) references learningObject (learningid),
  constraint fk_course_comp_comp_id foreign key (competency_id) references individual_competency (id),
  constraint fk_course_comp_target_level_id foreign key (competency_target_level_id) references competency_level (id)
)
GO

create table certification_competency (
  certification_id numeric(8)   not null,
  competency_id    nvarchar(15) not null,
  constraint pk_certification_competency
  primary key (certification_id, competency_id),
  constraint fk_cert_comp_cert_id foreign key (certification_id)
  references certifications (certid),
  constraint fk_cert_comp_comp_id foreign key (competency_id)
  references individual_competency (id)
)
GO

create table question_type9 (
  questionid          nvarchar(85)   not null,
  qattributes         smallint       not null,
  qtext2              ntext          null,
  hint                nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE9 primary key (questionid)
)
GO

create table testquestion9 (
  instanceid          nvarchar(15)  not null,
  sectionnum          smallint      not null,
  questionnum         smallint      not null,
  questionid          nvarchar(85)  not null,
  rid                 nvarchar(20)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_TESTQUESTION9 primary key (instanceid, sectionnum, questionnum)
)
GO

CREATE TABLE code_geographic_regions (
  id        	nvarchar(15) primary key  NOT NULL,
  code 			nvarchar(255)             NOT NULL,
  display_name 	nvarchar(255)             NULL
)
GO

CREATE TABLE code_audience (
  id          nvarchar(15) primary key  NOT NULL,
  description nvarchar(255)             NOT NULL
)
GO

create table netg_proxied_authentication (
  courseid            nvarchar(85)  not null,
  publickey           nvarchar(32)  not null,
  privatekey          nvarchar(32)  not null,
  destination         nvarchar(10)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_NETG_PROXIED_AUTH primary key (courseid)
)
GO

create table get_abstract_launcher (
  courseid            nvarchar(85)  not null,
  uvalue              nvarchar(20)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_GET_ABSTRACT_LAUNCH primary key (courseid)
)
GO

create table schedule_report (
  schedule_report_id nvarchar(15)        not null,
  reportid           nvarchar(15)        not null,
  attach             char(1) default 'N' not null,
  parameters         nvarchar(3000),
  constraint PK_SCHEDULE_REPORT primary key (schedule_report_id)
)
GO

create table schedule_task (
  schedule_task_id nvarchar(15) not null,
  task_id          nvarchar(15) not null,
  userid           nvarchar(85) not null,
  name             nvarchar(100),
  status           char(1),
  daily            char(1),
  weekly           int,
  monthly          smallint,
  run_at           datetime     not null,
  start_date       datetime     null,
  end_date         datetime     null,
  timezone         nvarchar(50) null,
  constraint PK_SCHEDULE_TASK primary key (schedule_task_id),
  constraint FK_SCHEDULE_USER foreign key (userid) references users (userid)
)
GO

create table schedule_report_delivery (
  schedule_report_id nvarchar(15) not null,
  userid             nvarchar(85) not null,
  constraint PK_SCHEDULE__DELIVERY primary key (schedule_report_id, userid)
)
GO

create table mastertest_print (
  testid              nvarchar(85)  not null,
  printTitle          nvarchar(255) null,
  print_abcd          char(1)       null,
  printAnswersDate    datetime      null,
  printExamDate       datetime      null,
  coverSheetId        nvarchar(15)  null,
  printAnswersDateEnd datetime      null,
  printExamDateEnd    datetime      null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_MASTERTEST_PRINT primary key (testid)
)
GO

CREATE TABLE olsa_account (
  id                INT                  NOT NULL,
  name              NVARCHAR(127)        NOT NULL,
  endpoint          NVARCHAR(127)        NOT NULL,
  customerid        NVARCHAR(127)        NOT NULL,
  sharedsecret      NVARCHAR(127)        NOT NULL,
  laststatus        INT                  NOT NULL,
  show_in_catalog   CHAR(1)              NOT NULL,
  include_in_search CHAR(1) default 'N'  NOT NULL,
  CONSTRAINT pk_olsa_account PRIMARY KEY (id),
  CONSTRAINT ak_olsa_account_name UNIQUE (name)
)
GO

CREATE TABLE olsa_account_course (
  courseid     NVARCHAR(85) NOT NULL,
  dateimported DATETIME     NOT NULL,
  accountid    INT          NOT NULL,
  CONSTRAINT pk_olsa_account_course PRIMARY KEY (courseid, accountid),
  CONSTRAINT fk_olsa_course_accountid FOREIGN KEY (accountid) REFERENCES olsa_account (id)
)
GO

create table creditbalancehistory (
  auditid        nvarchar(15) not null,
  recipientid    nvarchar(85) not null,
  recipienttype  char(1)      not null,
  dategranted    datetime     not null,
  amount         float        not null,
  grantor_userid nvarchar(85) not null,
  CONSTRAINT pk_creditaudit primary key (auditid)
)
GO

create table user_extension (
  userid               nvarchar(85) not null,
  extension_no         int          not null,
  extension_value_data nvarchar(85) null,
  constraint PK_USER_EXT primary key (userid, extension_no)
)
GO

create table code_user_extension (
  extension_key        nvarchar(15) not null,
  extension_no         int          not null,
  code nvarchar(85) null,
  display_name nvarchar(85) null,
  constraint PK_CODE_USER_EXT primary key (extension_key, extension_no),
  constraint UK_CODE_USER_EXT_KEY unique (extension_key)
)
GO

create table user_extension_label (
  extension_no int                            not null,
  label        nvarchar(85)                   null,
  domain_id    nvarchar(15) default 'DEFAULT' not null,
  constraint PK_USER_EXT_LABEL primary key (extension_no)
)
GO

create table ip_filter_table (
  testid        nvarchar(85) not null,
  qindex        smallint     not null,
  ipfilterstart nvarchar(15) null,
  ipfilterend   nvarchar(15) null,
  constraint PK_IP_FILTER_TABLE primary key (testid, qindex)
)
GO

create table ip_filter_table_default (
  qindex        smallint     not null,
  ipfilterstart nvarchar(15) null,
  ipfilterend   nvarchar(15) null,
  constraint PK_IP_FILTER_TABLE_DEFAULT primary key (qindex)
)
GO

create table randsection (
  testid              nvarchar(85)  not null,
  sectionnum          smallint      not null,
  ordernum            smallint      not null,
  poolid              int           null,
  nquest              int           null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_RANDSECTION primary key (testid, sectionnum, ordernum),
  constraint FK_RANDSECTION_POOLID foreign key (poolid) references code_pool (poolid)
)
GO

create table question_pools (
  questionid nvarchar(85) not null,
  sequence   int          not null,
  poolid     int          not null,
  constraint PK_QPOOLS primary key (questionid, sequence),
  constraint FK_QPOOLS foreign key (poolid) references code_pool (poolid)
)
GO

create table test_pools (
  testid   nvarchar(85) not null,
  sequence int          not null,
  poolid   int          not null,
  constraint PK_TPOOLS primary key (testid, sequence),
  constraint FK_TPOOLS foreign key (poolid) references code_testpool (tpoolid)
)
GO

create table cert_requirements (
  certid         numeric(8)                      not null,
  learningid     nvarchar(85)                    not null,
  since_n_days   int                             not null,
  use_fixed_date char(1) default 'N'             not null,
  since_date     datetime                        null,
  constraint PK_CERTREQ primary key (certid, learningid)
)
GO

create table prev_passwords (
  passwordid nvarchar(15) not null,
  userid     nvarchar(85) not null,
  password   nvarchar(50) not null,
  timestamp  datetime     not null,
  constraint PK_PREV_PWDS primary key (passwordid),
  constraint FK_PREV_PWDS_USERID foreign key (userid) references users (userid)
)
GO

create index NUK_PREV_PASSWORDS_USERID on prev_passwords (userid)
GO

create table cert_renewal (
  certid                numeric(8)    not null,
  renewal_type          nvarchar(85)  null,
  renewal_regulation    nvarchar(85)  null,
  prior_grace_n_days    int           not null,
  post_grace_n_days     int           not null,
  course_n_months_valid int           not null,
  last_updated          datetime      NULL,
  updater_legal_name    nvarchar(250) NULL,
  update_meaning_text   ntext         NULL,
  last_updater_uid      nvarchar(85)  NULL,
  constraint PK_CERTRENEW primary key (certid)
)
GO

create table globalenglish_launcher (
  courseid            nvarchar(85)  not null,
  keycode             nvarchar(85)  not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_GLOBALENGLISH_LAUNCHER primary key (courseid)
)
GO

create table coupon (
  couponid     nvarchar(85) not null,
  sessionid    nvarchar(85) not null,
  learningid   nvarchar(85) not null,
  learningtype nvarchar(1)  not null,
  issuerid     nvarchar(85) not null,
  issuedate    datetime     null,
  expirydate   datetime     null,
  validity     nvarchar(15) not null,
  resellerid   nvarchar(85) null,
  holderid     nvarchar(85) null,
  transcriptid nvarchar(15) null,
  constraint PK_COUPON primary key (couponid)
)
GO

create table code_exp_category (
  categoryid  nvarchar(15)   not null,
  parentid    nvarchar(15)   null,
  description nvarchar(85)   not null,
  node_type   char(1)        not null,
  ext_desc    nvarchar(1000) null,
  constraint PK_CODE_EXP_CATEGORY primary key (categoryid)
)
GO

create table user_exp_profile (
  userid            nvarchar(85)   not null,
  categoryid        nvarchar(15)   not null,
  nyears_experience int            not null,
  levelid           nvarchar(15)   not null,
  comments          nvarchar(1000) null,
  constraint PK_USER_EXP_PROFILE primary key (userid, categoryid),
  constraint FK_USER_EXP_PROFILE foreign key (categoryid) references code_exp_category (categoryid)
)
GO

create table code_level (
  levelid     nvarchar(15) not null,
  description nvarchar(50) not null,
  constraint PK_CODE_LEVEL primary key (levelid)
)
GO

insert into code_level values ('0', 'N/A')
GO

insert into code_level values ('1', 'Beginner')
GO

insert into code_level values ('2', 'Intermediate')
GO

insert into code_level values ('3', 'Advanced')
GO

create table extended_enrollment_details (
  transcript_id  nvarchar(15) not null,
  branch_number  nvarchar(50) null,
  branch_phone   nvarchar(50) null,
  manager_email  nvarchar(50) null,
  is_over_21     char(1)      null,
  gender         char(1)      null,
  cell_phone     nvarchar(50) null,
  city           nvarchar(50) null,
  state          nvarchar(50) null,
  need_flight    char(1)      null,
  need_hotel     char(1)      null,
  need_car       char(1)      null,
  arrival_date   datetime     null,
  comments       ntext        null,
  charge_to_user char(1)      null,
  preferred_date datetime     null,
  preferred_loc  nvarchar(85) null,
  constraint PK_EXT_ENROLL_DETAIL primary key (transcript_id)
)
GO

create table enrollment_policy_ext_details (
  policy_id              nvarchar(15)        not null,
  include_branch_number  char(1)             null,
  include_branch_phone   char(1)             null,
  include_manager_email  char(1)             null,
  include_is_over_21     char(1)             null,
  include_gender         char(1)             null,
  include_cell_phone     char(1)             null,
  include_city           char(1)             null,
  include_state          char(1)             null,
  include_need_flight    char(1)             null,
  include_need_hotel     char(1)             null,
  include_need_car       char(1)             null,
  include_arrival_date   char(1)             null,
  include_comments       char(1)             null,
  include_charge_to_user char(1)             null,
  include_preferred_date char(1)             null,
  include_preferred_loc  char(1)             null,
  include_test           char(1)             null,

  mand_branch_number     char(1) default 'N' not null,
  mand_branch_phone      char(1) default 'N' not null,
  mand_manager_email     char(1) default 'N' not null,
  mand_gender            char(1) default 'N' not null,
  mand_cell_phone        char(1) default 'N' not null,
  mand_city              char(1) default 'N' not null,
  mand_state             char(1) default 'N' not null,
  mand_arrival_date      char(1) default 'N' not null,
  mand_comments          char(1) default 'N' not null,
  mand_preferred_date    char(1) default 'N' not null,
  mand_preferred_loc     char(1) default 'N' not null,
  value_testid           nvarchar(85)        null,

  constraint PK_ENROLL_POLICY_EXT_DETAIL primary key (policy_id)
)
GO

create table code_training_center (
  training_center_id   nvarchar(15)   not null,
  training_center_name nvarchar(255)  not null,
  description          nvarchar(255)  null,
  location             nvarchar(255)  null,
  contactinfo          nvarchar(255)  null,
  directions           nvarchar(1000) null,
  map_url              nvarchar(255)  null,
  constraint PK_CODE_TRAINING primary key (training_center_id)
)
GO

insert into code_training_center (training_center_id, training_center_name) values ('*DEFAULT*', 'Default')
GO

create table code_training_resource (
  training_center_id     nvarchar(15)                   not null,
  resource_id            nvarchar(15)                   not null,
  resource_name          nvarchar(85)                   not null,
  resource_type          char(1) default 'U'            not null,
  resource_serial_number nvarchar(85)                   null,
  qty                    int default 0                  not null,
  description            nvarchar(1000)                 not null,
  constraint PK_CODE_TRAINING_RESOURCE primary key (resource_id)
)
GO

create index NUK_TRAINING_RESOURCE on code_training_resource (training_center_id)
GO

create table training_resource_reservation (
  reservation_id nvarchar(15)               not null,
  resource_id    nvarchar(15)               not null,
  entry_type     char(1) default 'R'        not null,
  userid         nvarchar(85)               not null,
  startdate      datetime                   null,
  finishdate     datetime                   null,
  qty            int                        not null,
  eventid        nvarchar(15)               null,
  sessionid      nvarchar(15)               null,
  comments       nvarchar(1000)             null,
  constraint PK_TRAINING_RESOURCE_RSV primary key (reservation_id)
)
GO

create table instructor_qualification (
  learningid nvarchar(85) not null,
  userid     nvarchar(85) not null,
  constraint PK_INSTR_QUAL primary key (learningid, userid)
)
GO

create index NUK_INSTR_QUAL on instructor_qualification (userid)
GO

create table course_interest (
  learningid    nvarchar(85)  not null,
  userid        nvarchar(85)  not null,
  date1         datetime      null,
  date2         datetime      null,
  date3         datetime      null,
  comments      nvarchar(255) null,
  interest_date datetime      null,
  constraint PK_COURSE_INTEREST primary key (learningid, userid)
)
GO

create table safari_launcher (
  courseid            nvarchar(85)  not null,
  safari_host_url     nvarchar(85)  null,
  ui_code             nvarchar(85)  null,
  target_page         nvarchar(85)  null,
  target_page_param   varchar(255)  null,
  secret_code         nvarchar(85)  null,
  dummy_email_domain  nvarchar(85)  null,
  reset_flag          nvarchar(1)   null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_SAFARI_LAUNCHER primary key (courseid)
)
GO

CREATE TABLE vc_launcher_generic (
  course_id           nvarchar(85)  NOT NULL,
  launch_url          ntext         NULL,
  launch_program      nvarchar(255) NULL,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  last_updater_uid    nvarchar(85)  null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  CONSTRAINT PK_LAUNCHER_GENERIC PRIMARY key (course_id),
  CONSTRAINT FK_LAUNCHER_GENERIC_COURSEID FOREIGN KEY (course_id) REFERENCES learningObject (learningid)
)
GO

CREATE TABLE vc_webex_account (
  webex_id        nvarchar(85)        NOT NULL,
  site_id         nvarchar(85)        NOT NULL,
  partner_id      nvarchar(85)        NOT NULL,
  site_name       nvarchar(255)       NOT NULL,
  context         nvarchar(255)       NULL,
  display_name    nvarchar(1000)      NULL,
  active_account  char(1) DEFAULT 'Y' NOT NULL,
  webex_admin_id  nvarchar(85)        NULL,
  webex_admin_pwd nvarchar(255)       NULL,
  CONSTRAINT PK_WEBEX_ACCOUNT PRIMARY key (webex_id)
)
GO

CREATE TABLE vc_launcher_webex (
  course_id           nvarchar(85)  NOT NULL,
  webex_id            nvarchar(85)  NULL,
  meeting_key         nvarchar(255) NULL,
  webex_host_id       nvarchar(85)  NULL,
  webex_host_pwd      nvarchar(255) NULL,
  webex_attr1         nvarchar(85)  NULL,
  webex_attr2         nvarchar(255) NULL,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  last_updater_uid    nvarchar(85)  null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  CONSTRAINT PK_LAUNCHER_WEBEX PRIMARY KEY (course_id),
  CONSTRAINT FK_LAUNCHER_WEBEX_COURSEID FOREIGN KEY (course_id) REFERENCES learningObject (learningid),
  CONSTRAINT FK_VC_WEBEX_ACCOUNT_WEBEXID FOREIGN KEY (webex_id) REFERENCES vc_webex_account (webex_id)
)
GO

CREATE TABLE vc_launcher_session (
  event_id            nvarchar(15)  not null,
  course_id           nvarchar(85)  not null,
  session_key         nvarchar(255) null,
  instructor          nvarchar(255) null,
  last_update         datetime      null,
  overall_status      int           null,
  mastery_score       nvarchar(20)  null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  last_updater_uid    nvarchar(85)  null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  CONSTRAINT PK_LAUNCHER_SESSION PRIMARY KEY (event_id),
  CONSTRAINT FK_LAUNCHER_SESSION_EVENTID FOREIGN KEY (event_id) REFERENCES cschedule (eventid),
  CONSTRAINT FK_LAUNCHER_SESSION_COURSEID FOREIGN KEY (course_id) REFERENCES learningObject (learningid)
)
GO

CREATE TABLE vc_user_attribute (
  userid      NVARCHAR(85)  NOT NULL,
  session_key nvarchar(255) NOT null,
  register_id NVARCHAR(85)  NULL,
  attr1       nvarchar(85)  null,
  attr2       nvarchar(255) null,
  CONSTRAINT PK_VC_USER_ATTRIBUTE PRIMARY KEY (userid, session_key),
  CONSTRAINT FK_VC_USER_ATTR_USERID FOREIGN KEY (userid) REFERENCES users (userid)
)
GO

create table content_org_node_objective (
  organization_id     nvarchar(15)   not null,
  node_id             nvarchar(2000) not null,
  ordinal             int            not null,
  objective_id        nvarchar(2000) not null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint pk_content_org_node_objective primary key (organization_id, node_id, ordinal),
  constraint fk_content_org_node_objective foreign key (organization_id, node_id)
  references ContentOrganizationNode (organizationID, nodeID)
)
GO

create table code_catalog_structure (
  catalogid            nvarchar(15)        not null,
  parentid             nvarchar(15)        null,
  title                nvarchar(255)       not null,
  comments             nvarchar(1000)      null,
  responsible_party    nvarchar(255)       null,
  ext_desc             nvarchar(1000)      null,
  inherit_parent_perms char(1)             null,
  display_format       nvarchar(20)        null,
  picture              nvarchar(255)       null,
  creation_uid         nvarchar(85)        null,
  creation_date        datetime            null,
  is_public            char(1) default 'N' not null,
  allow_download       char(1) default 'N' not null,
  reference_code       nvarchar(30)        null,
  constraint PK_CODE_CATALOG_ID primary key (catalogid)
)
GO

insert into code_catalog_structure (catalogid, parentid, title, comments, ext_desc, display_format)
  values ('*ROOT*', '*ROOT*', 'Top', 'The base for all catalogs', 'Top', 'F3')
GO

insert into code_catalog_structure (catalogid, parentid, title, comments, ext_desc)
  values ('0', '*ROOT*', 'Default Public Catalog', 'The default catalog', 'Default Public Catalog')
GO

create table catalog_map (
  catalogid  nvarchar(15)              not null,
  objecttype nvarchar(15) default 'LO' not null,
  objectid   nvarchar(85)              not null,
  constraint PK_CATALOG_MAP primary key (catalogid, objecttype, objectid),
  constraint FK_CATALOG_MAP foreign key (catalogid) references code_catalog_structure (catalogid)
)
GO

create index NUK_CATALOG_OID on catalog_map (objectid)
GO

create table usercatalogs_map (
  userid    nvarchar(85) not null,
  catalogid nvarchar(15) not null,
  constraint PK_USERCATALOG_MAP primary key (userid, catalogid),
  constraint FK_USERCATALOG_MAP foreign key (catalogid) references code_catalog_structure (catalogid)
)
GO

CREATE TABLE olsa_account_catalog (
  accountid INT          NOT NULL,
  catalogid NVARCHAR(15) NOT NULL,
  CONSTRAINT pk_olsa_account_catalog PRIMARY KEY (accountid, catalogid),
  CONSTRAINT fk_catalog_olsa_accountid FOREIGN KEY (accountid) REFERENCES olsa_account (id),
  CONSTRAINT fk_olsa_account_catalogid FOREIGN KEY (catalogid) REFERENCES code_catalog_structure (catalogid)
)
GO

CREATE TABLE dashboard_tabs (
  tab_id INT           NOT NULL,
  userid nvarchar(85)  NOT NULL,
  title  nvarchar(255) NOT NULL,
  type   char(1)       NOT NULL,
  constraint PK_DASHBOARD_TABS primary key (tab_id),
  constraint FK_DASHBOARD_TABS foreign key (userid) references users (userid)
)
GO

CREATE TABLE dashboard_tab_chart (
  indicator_instance_id nvarchar(85) NOT NULL,
  tab_id                INT          NOT NULL,
  position              INT          NOT NULL,
  chart_type            INT          NOT NULL,
  constraint PK_DASHBOARD_TAB_CHART primary key (indicator_instance_id),
  constraint FK_DASHBOARD_TABS_CHART foreign key (tab_id) references dashboard_tabs (tab_id),
  constraint ak_tab_id_pos_dashboard unique (tab_id, position)
)
GO

CREATE TABLE indicator_instance_parameters (
  indicator_param_id    INT          NOT NULL,
  indicator_instance_id nvarchar(85) NOT NULL,
  parameter_type        nvarchar(85) NOT NULL,
  parameter             nvarchar(255),
  constraint PK_INDICATOR_INSTANCE_PARAMS primary key (indicator_param_id),
  constraint FK_INDICATOR_INSTANCE_ID foreign key (indicator_instance_id)
  references dashboard_tab_chart (indicator_instance_id)
)
GO

create table compliance (
  complianceid      nvarchar(15)   not null,
  title             nvarchar(85)   not null,
  reference         nvarchar(85)   null,
  issuing_authority nvarchar(85)   null,
  description       nvarchar(1000) null,
  for_info_contact  nvarchar(85)   null,
  current_status    nvarchar(15)   null,
  compliance_type   nvarchar(85)   null,
  creation_date     datetime       null,
  creation_uid      nvarchar(85)   null,
  constraint PK_COMPLIANCE primary key (complianceid)
)
GO

create table code_compliance_catalog (
  comp_catalogid nvarchar(15)   not null,
  title          nvarchar(85)   not null,
  description    nvarchar(1000) null,
  constraint PK_COMPLIANCE_CAT primary key (comp_catalogid)
)
GO

create table compliance_catalog_content (
  comp_catalogid nvarchar(15) not null,
  complianceid   nvarchar(85) not null,
  constraint PK_COMPLIANCE_CAT_CONTENT primary key (comp_catalogid)
)
GO

create table compliance_map (
  complianceid nvarchar(15)              not null,
  objecttype   nvarchar(15) default 'LO' not null,
  objectid     nvarchar(85)              not null,
  constraint PK_COMPLIANCE_MAP primary key (complianceid, objecttype, objectid),
  constraint FK_COMPLIANCE_MAP foreign key (complianceid) references compliance (complianceid)
)
GO

create index NUK_COMPLIANCE__MAP_LO on compliance_map (objectid, objecttype)
GO

CREATE TABLE course_revision (
  course_id            NVARCHAR(85)        NOT NULL,
  revision_number      INT                 NOT NULL,
  date_revised         DATETIME            NOT NULL,
  launch_interface     SMALLINT            NOT NULL,
  organization_id      NVARCHAR(15)        NULL,
  item_id              NVARCHAR(15)        NULL,
  encoding             NVARCHAR(25)        NULL,
  revision_comment     NTEXT               NOT NULL,
  allow_download       CHAR(1)             NOT NULL,
  publish_for          CHAR(1)             NULL,
  update_meaning       nvarchar(8)         null,
  last_updated         datetime            null,
  last_updater_uid     nvarchar(85)        null,
  updater_legal_name   nvarchar(250)       null,
  update_meaning_text  ntext               null,
  available            char(1) default 'Y' not null,
  course_raw_byte_size int                 null,
  CONSTRAINT pk_course_revision PRIMARY KEY (course_id, revision_number),
  CONSTRAINT fk_course_revision_org_id FOREIGN KEY (organization_id) REFERENCES ContentOrganization (organizationID),
  CONSTRAINT fk_course_revision_item_id FOREIGN KEY (item_id) REFERENCES ContentItem (itemID)
)
GO

CREATE TABLE ex_q_approval (
  id                  NVARCHAR(15)  NOT NULL,
  question_id         NVARCHAR(85)  NOT NULL,
  user_id             NVARCHAR(85)  NOT NULL,
  app_comment         NVARCHAR(500) NOT NULL,
  comment_date        DATETIME      NOT NULL,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  last_updater_uid    nvarchar(85)  null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  constraint PK_EX_Q_APPROVAL PRIMARY KEY (id),
  CONSTRAINT FK_EX_Q_APPROVAL_1 FOREIGN KEY (question_id) REFERENCES question (questionid),
  CONSTRAINT FK_EX_Q_APPROVAL_2 FOREIGN KEY (user_id) REFERENCES users (userid)
)
GO

CREATE table my_report (
  report_id     nvarchar(15) not null,
  std_report_id nvarchar(15),
  wiz_report_id nvarchar(15),
  report_type   char(1)      not null,
  owner         nvarchar(85) not null,
  category      nvarchar(50),
  constraint PK_MY_REPORT_ID primary key (report_id),
  constraint FK_MY_REPORT_WIZ_ID foreign key (wiz_report_id)
  references report_wizard (report_id),
  constraint CKC_MY_REPORT_ID check (
    (report_type = 'S' and std_report_id is not null and wiz_report_id is null) or
    (report_type = 'W' and std_report_id is null and wiz_report_id is not null)),
  constraint FK_MY_REPORT_OWNER foreign key (owner) references users (userid)
)
GO

create table job_profile_group (
  id             nvarchar(15)   not null,
  name           nvarchar(85)   not null,
  poolid         int            not null,
  description    nvarchar(2000) null,
  is_active      char(1)        not null,
  reference_code nvarchar(85)   not null,
  categoryid     varchar(15)    not null,
  certid         numeric(8)     null,
  constraint pk_job_profile_grp primary key (id),
  constraint ak_job_profile_grp_ref_code unique (reference_code),
  constraint fk_job_profile_grp_pool foreign key (poolid) references code_jobprofile_pool (poolid),
  constraint fk_certid foreign key (certid) references certifications (certid)
)
GO

create table job_profile_group_map (
  job_profile_group_id nvarchar(15) not null,
  job_profile_id       nvarchar(15) not null,
  constraint pk_job_profile_group_map primary key (job_profile_group_id, job_profile_id),
  constraint fk_job_profile_group_id foreign key (job_profile_group_id) references job_profile_group (id),
  constraint fk_job_profile_id foreign key (job_profile_id) references job_profile (id)
)
GO

create table payment (
  payment_id          int                      not null,
  create_date         datetime                 not null,
  pay_user_id         nvarchar(85)             not null,
  ccy_code            char(3)                  not null,
  total_amount        numeric(15, 2)           not null,
  tax                 numeric(15, 2) default 0 not null,
  status              char(1) default 'U'      not null,
  settled_date        datetime,
  order_ref           nvarchar(50)             not null,
  pay_ref             nvarchar(50)             not null,
  order_fulfilled     char(1) default '-'      not null,
  refunded_date       datetime,
  additional_charge   nvarchar(85),
  is_reviewed         char(1) default 'N'      not null,
  reviewed_by         nvarchar(85),
  reviewed_on         datetime,
  user_ref            nvarchar(85),
  pay_type            char(3) default 'CC'     not null,
  sales_order_no      nvarchar(85)             null,
  update_meaning      nvarchar(8)              null,
  last_updated        datetime                 null,
  updater_legal_name  nvarchar(250)            null,
  update_meaning_text ntext                    null,
  last_updater_uid    nvarchar(85)             null,
  constraint PK_PAYMENT primary key (payment_id),
  constraint CKC_PAYMENT_C1 check (status in ('U', 'Y', 'N', 'X', 'R')),
  constraint CKC_PAYMENT_C2 check (total_amount > 0),
  constraint CKC_PAYMENT_C3 check (settled_date is null or (settled_date is not null and status <> 'U' and settled_date >= create_date)),
  constraint CKC_PAYMENT_C4 check (total_amount >= tax),
  constraint CKC_PAYMENT_C5 check (order_fulfilled in ('-', 'Y', 'N')),
  constraint CKC_PAYMENT_C6 check (order_fulfilled = '-' or status in ('Y', 'R')),
  constraint CKC_PAYMENT_C7 check (refunded_date is null or (refunded_date is not null and status = 'R' and refunded_date >= settled_date)),
  constraint CKC_PAYMENT_C8 check (tax >= 0),
  constraint CKC_PAYMENT_C9 check (is_reviewed in ('Y', 'N')),
  constraint FK_PAYMENT_F1 foreign key (pay_user_id) references users (userid)
)
GO

create index NUK_PAYMENT_X1 on payment (order_ref)
GO

create index NUK_PAYMENT_X2 on payment (create_date)
GO

create index NUK_PAYMENT_X3 on payment (pay_ref)
GO

create table AICC_global_objectives (
  userid       nvarchar(85)  not null,
  objective_id nvarchar(255) not null,
  status       nvarchar(13)  null,
  score        nvarchar(20)  null,
  constraint PK_AICC_global_objectives primary key (userid, objective_id),
  constraint FK_AICC_GLOBAL_OBJECTIVES_F1 foreign key (userid)
  references users (userid)
)
GO

create table custom_extension (
  property_key   nvarchar(15)  not null,
  object_type    nvarchar(5)   not null,
  object_id      nvarchar(85)  not null,
  property_id    nvarchar(85)  not null,
  property_value nvarchar(512) null,
  constraint PK_CUSTOM_ENAHNCEMENT primary key (property_key)
)
GO

create index NUK_CUSTOM_OBJECT on custom_extension (object_type, object_id)
GO

create index NUK_CUSTOM_PROP on custom_extension (property_id)
GO

create table opt_pay_items (
  itemid      nvarchar(15)   not null,
  label       nvarchar(85)   not null,
  label_key   nvarchar(85)   null,
  description nvarchar(1000) null,
  constraint PK_opt_pay_items primary key (itemid)
)
GO

create table lo_opt_pay_items (
  sessionid           nvarchar(85)        not null,
  learningid          nvarchar(85)        not null,
  itemid              nvarchar(15)        not null,
  offering            int                 not null,
  offering_amount     numeric(15, 2)      not null,
  payment_method      char(1) default 'C' not null,
  update_meaning      nvarchar(8)         null,
  last_updated        datetime            null,
  updater_legal_name  nvarchar(250)       null,
  update_meaning_text ntext               null,
  last_updater_uid    nvarchar(85)        null,
  constraint PK_lo_opt_pay_items primary key (sessionid, learningid, itemid, payment_method),
  constraint FK_LO_OPT_PAY_ITEMS_F1 foreign key (itemid) references opt_pay_items (itemid)
)
GO

create table user_opt_pay_items (
  user_itemid         nvarchar(85)   not null,
  userid              nvarchar(85)   not null,
  transcriptid        nvarchar(15)   not null,
  itemid              nvarchar(15)   not null,
  description         nvarchar(255)  not null,
  quantity            int            not null,
  unit_cost           numeric(15, 2) not null,
  paid                char(1) default 'N',
  paymentid           int            null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_user_opt_pay_items primary key (user_itemid),
  constraint FK_USER_OPT_PAY_ITEMS_F1 foreign key (userid) references users (userid),
  constraint FK_USER_OPT_PAY_ITEMS_F2 foreign key (transcriptid) references transcript (transcriptid),
  constraint FK_USER_OPT_PAY_ITEMS_F3 foreign key (itemid) references opt_pay_items (itemid)
)
GO

create table question_type_drag_and_drop (
  questionid          nvarchar(85)   not null,
  qattributes         int            not null,
  qtext               ntext          null,
  hint                nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  num_draggable       int default 1  not null,
  background_url      nvarchar(255)  not null,
  background_width    int            not null,
  background_height   int            not null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE_DRAG_AND_DROP primary key (questionid),
  constraint CKC_QUESTION_TYPE_DND_C1 check (background_width > 0),
  constraint CKC_QUESTION_TYPE_DND_C2 check (background_height > 0),
  constraint CKC_QUESTION_TYPE_DND_C3 check (num_draggable >= 1)
)
GO

create table draggable (
  draggable_id        nvarchar(15)  not null,
  questionid          nvarchar(85)  not null,
  display_order       int default 0 not null,
  image_url           nvarchar(255) not null,
  image_width         int           not null,
  image_height        int           not null,
  hot_area_x          int           not null,
  hot_area_y          int           not null,
  hot_area_width      int           not null,
  hot_area_height     int           not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_DRAGGABLE primary key (draggable_id),
  constraint FK_DRAGGABLE_QUESTIONID foreign key (questionid) references question_type_drag_and_drop (questionid),
  constraint CKC_DRAGGABLE_C1 check (image_width > 0),
  constraint CKC_DRAGGABLE_C2 check (image_height > 0),
  constraint CKC_DRAGGABLE_C3 check (hot_area_x >= 0),
  constraint CKC_DRAGGABLE_C4 check (hot_area_y >= 0),
  constraint CKC_DRAGGABLE_C5 check (hot_area_width > 0),
  constraint CKC_DRAGGABLE_C6 check (hot_area_height > 0)
)
GO

create table answer_drag_and_drop (
  answer_id           nvarchar(15)  not null,
  test_instance_id    nvarchar(15)  not null,
  section_num         int           not null,
  question_num        int           not null,
  question_id         nvarchar(85)  not null,
  draggable_id        nvarchar(15)  not null,
  position_x          int           not null,
  position_y          int           not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_ANSWER_DRAG_AND_DROP primary key (answer_id),
  constraint FK_ANSWER_DRAG_AND_DROP_F1 foreign key (question_id) references question_type_drag_and_drop (questionid),
  constraint FK_ANSWER_DRAG_AND_DROP_F2 foreign key (draggable_id) references draggable (draggable_id),
  constraint FK_ANSWER_DRAG_AND_DROP_F3 foreign key (test_instance_id) references testinstance (instanceid),
  constraint CKC_ANSWER_DRAG_AND_DROP_C1 check (position_x >= 0),
  constraint CKC_ANSWER_DRAG_AND_DROP_C2 check (position_y >= 0)
)
GO

create table user_group (
  group_id              nvarchar(15)                   not null,
  group_name            nvarchar(255)                  not null,
  creator_uid           nvarchar(85)                   not null,
  created_date          datetime                       null,
  last_updater_uid      nvarchar(85)                   null,
  last_updated          datetime                       null,
  to_desc_org           char(1) default 'Y'            not null,
  isjoinallcompetencies char(1)                        null,
  domain_id             nvarchar(15) default 'DEFAULT' not null,
  constraint PK_USER_GROUP primary key (group_id),
  constraint AK_USER_GROUP_GROUP_NAME_ unique (group_name)
)
GO

create table user_group_membership (
  group_id nvarchar(15) not null,
  userid   nvarchar(85) not null,
  constraint PK_USER_GROUP_MEMBERSHIP primary key (group_id, userid),
  constraint FK_USR_GROUP_MEMBER_GRP_ID foreign key (group_id) references user_group (group_id)
)
GO

create table user_group_membership_rule (
  group_id      nvarchar(15) not null,
  identity_id   nvarchar(85) not null,
  identity_type nvarchar(45) not null,
  constraint PK_USER_GROUP_MEMBERSHIP_RULE primary key (group_id, identity_id, identity_type),
  constraint FK_USR_GRP_MEMBER_RULE_GRP_ID foreign key (group_id) references user_group (group_id)
)
GO

create table opml_task_log (
  record_id           nvarchar(15)  not null,
  task_type           nvarchar(200) not null,
  frequency           nvarchar(15)  NOT NULL,
  outline_type        nvarchar(500) NOT NULL,
  xmlUrl              nvarchar(500) NOT NULL,
  date_started        datetime      not null,
  date_finished       datetime      not null,
  final_status        varchar(30),
  n_records_processed int           not null,
  n_errors            int           not null,
  error_exceptions    ntext         null,
  warning_exceptions  ntext         null,
  email_notification  nvarchar(200) null,
  constraint PK_OPML_RECORD primary key (record_id)
)
GO

create index NUK_OPML_DATE on opml_task_log (date_started)
GO

create table attendance_audit (
  record_id    nvarchar(15) not null,
  eventid      nvarchar(15) not null,
  userid       nvarchar(85) not null,
  session_date datetime     not null,
  instructor   nvarchar(85) not null,
  audit_date   datetime     not null,
  constraint PK_ATTEND_AUDIT primary key (record_id)
)
GO

create index NUK_ATTEND_DATE on attendance_audit (eventid)
GO

create table question_type_hotspot (
  questionid          nvarchar(85)   not null,
  qattributes         int            not null,
  qtext               ntext          null,
  hint                nvarchar(1000) null,
  prenote             nvarchar(1000) null,
  postnote            nvarchar(1000) null,
  num_hotspot         int default 1  not null,
  background_url      nvarchar(255)  not null,
  background_width    int            not null,
  background_height   int            not null,
  update_meaning      nvarchar(8)    null,
  last_updated        datetime       null,
  updater_legal_name  nvarchar(250)  null,
  update_meaning_text ntext          null,
  last_updater_uid    nvarchar(85)   null,
  constraint PK_QUESTION_TYPE_HOTSPOT primary key (questionid),
  constraint CKC_QUESTION_TYPE_HS_C1 check (background_width > 0),
  constraint CKC_QUESTION_TYPE_HS_C2 check (background_height > 0),
  constraint CKC_QUESTION_TYPE_HS_C3 check (num_hotspot >= 1)
)
GO

create table hotspot (
  hotspot_id          nvarchar(15)  not null,
  questionid          nvarchar(85)  not null,
  hot_area_x          int           not null,
  hot_area_y          int           not null,
  hot_area_width      int           not null,
  hot_area_height     int           not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_HOTSPOT_ID primary key (hotspot_id),
  constraint FK_HOTSPOT_QUESTIONID foreign key (questionid) references question_type_hotspot (questionid),
  constraint CKC_HOTSPOT_C3 check (hot_area_x >= 0),
  constraint CKC_HOTSPOT_C4 check (hot_area_y >= 0),
  constraint CKC_HOTSPOT_C5 check (hot_area_width > 0),
  constraint CKC_HOTSPOT_C6 check (hot_area_height > 0)
)
GO

create table answer_hotspot (
  answer_id           nvarchar(15)  not null,
  test_instance_id    nvarchar(15)  not null,
  section_num         int           not null,
  question_num        int           not null,
  question_id         nvarchar(85)  not null,
  position_x          int           not null,
  position_y          int           not null,
  update_meaning      nvarchar(8)   null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_ANSWER_HOTSPOT primary key (answer_id),
  constraint FK_ANSWER_HOTSPOT_F1 foreign key (question_id) references question_type_hotspot (questionid),
  constraint FK_ANSWER_HOTSPOT_F2 foreign key (test_instance_id) references testinstance (instanceid),
  constraint CKC_ANSWER_HOTSPOT_C1 check (position_x >= 0),
  constraint CKC_ANSWER_HOTSPOT_C2 check (position_y >= 0)
)
GO

create table token_package (
  id                 nvarchar(15)   not null,
  package_name       nvarchar(200)  not null,
  label_key          nvarchar(100)  null,
  status             int            not null,
  cost_currency_code nvarchar(10)   not null,
  cost               numeric(15, 2) not null,
  token_value        numeric(15, 2) not null,
  created_date       datetime       not null,
  expiry_date        datetime       null,
  expires_in         int            null,
  expiry_period      nvarchar(15)   null,
  tax_rate           numeric(4, 2)  null,
  constraint PK_TOKEN_PACKAGE primary key (id),
  constraint CKC_TOKEN_PACKAGE_C1 check (token_value > 0),
  constraint CKC_TOKEN_PACKAGE_C2 check (expiry_date is null or (expiry_date > created_date))
)
GO

create table token_account (
  token_account_id nvarchar(15)  not null,
  account_name     nvarchar(200) null,
  account_type     char(1)       not null,
  constraint PK_TOKEN_ACCOUNT primary key (token_account_id)
)
GO

create index NUK_TOKEN_ACCOUNT_X1 on token_account (account_type)
GO

create table token (
  token_id         nvarchar(15)   not null,
  token_account_id nvarchar(15)   not null,
  balance          numeric(15, 2) not null,
  expiry_date      datetime       null,
  constraint PK_TOKEN primary key (token_id),
  constraint FK_TOKEN_F1 foreign key (token_account_id) references token_account (token_account_id),
  constraint CKC_TOKEN_C1 check (balance >= 0)
)
GO

create table token_transaction (
  token_transaction_id nvarchar(15)   not null,
  token_id             nvarchar(15)   not null,
  transaction_date     datetime       not null,
  credit               numeric(15, 2) not null,
  debit                numeric(15, 2) not null,
  source               char(1)        not null,
  user_id              nvarchar(85)   not null,
  payment_id           int default -1 not null,
  transcript_id        nvarchar(15)   null,
  tax_rate             numeric(4, 2)  null,
  tax_amount           numeric(15, 2) null,
  constraint PK_TOKEN_TRANSACTION primary key (token_transaction_id),
  constraint FK_TOKEN_TRANSACTION_F1 foreign key (token_id) references token (token_id),
  constraint CKC_TOKEN_TRANSACTION_C1 check (credit >= 0 AND debit >= 0)
)
GO

create table mekp_sync_properties (
  record_key                   nvarchar(85)        not null,
  master_sync_url              nvarchar(500)       null,
  master_sync_enabled          char(1)             not null,
  tracing_enabled              char(1)             not null,
  client_user_id_must_exist    char(1)             not null,
  client_user_email_must_match char(1)             not null,
  client_other_fields          nvarchar(500)       null,
  client_default_profile       nvarchar(85)        null,
  last_upload_sync             datetime            null,
  last_download_sync           datetime            null,
  master_encryption_key        char(85)            null,
  node_activation_key          varchar(255)        null,
  node_activation_key_index    int default 0       not null,
  node_activation_date         datetime            null,
  checksum                     nvarchar(50)        null,
  sync_freq_minutes            int default 0       not null,
  sync_password                nvarchar(50)        null,
  allow_global_transcript_sync char(1)             null,
  wallet_activation_credits    nvarchar(85)        null,
  user_activation_count        int default 0       not null,
  default_autosync_userid      nvarchar(85)        null,
  default_autosync_password    nvarchar(255)       null,
  enable_req_auth              char(1) default 'Y' not null,
  constraint PK_MEKP_PROPERTIES primary key (record_key)
)
GO

create table mekp_node_activation_list (
  node_activation_key varchar(255) not null,
  creator_userid      nvarchar(85) not null,
  mekp_version        nvarchar(85) not null,
  datecreated         datetime     not null,
  constraint PK_MEKP_ACTIVATION_LIST primary key (node_activation_key, creator_userid)
)
GO

create table appraisal_template (
  id                           nvarchar(15)                             not null,
  template_name                nvarchar(200)                            not null,
  created_date                 datetime                                 not null,
  created_user_id              nvarchar(85)                             not null,
  status                       char(1) default 'I'                      not null,
  display_past_achievements    char(1) default 'Y'                      not null,
  past_achievements_label      nvarchar(30) default 'Past Achievements' not null,
  display_future_planning      char(1) default 'Y'                      not null,
  future_planning_label        nvarchar(30) default 'Future Planning'   not null,
  allow_appraiser_change       char(1) default 'N'                      not null,
  allow_create_appraisal       char(1) default '3'                      not null,
  probationary_review          char(1) default 'N'                      not null,
  flow_model                   char(1) default 'B'                      not null,
  allow_super_appraiser_change char(1) default 'N'                      not null,
  start_date                   datetime                                 null,
  end_date                     datetime                                 null,
  appraisal_type               char(1)                                  not null,
  constraint PK_APPRAISAL_TEMPLATE primary key (id)
)
GO

create table appraisal_status_category (
  id        nvarchar(15)  not null,
  code      nvarchar(15)  not null,
  label_key nvarchar(100) null,
  constraint PK_APPRAISAL_STAT_CAT primary key (id)
)
GO

create table appraisal_status (
  id                   nvarchar(15)   not null,
  template_id          nvarchar(15)   not null,
  code                 nvarchar(15)   not null,
  label_key            nvarchar(100)  null,
  editable_by          char(1)        not null,
  visible_to           char(1)        not null,
  category_id          nvarchar(15)   null,
  email_id1            nvarchar(15)   null,
  email_id2            nvarchar(15)   null,
  req_sign             char(1)        not null,
  submit_txt_label_key nvarchar(2000) null,
  flow_order           int            not null,
  constraint PK_APPRAISAL_STATUS primary key (id),
  constraint FK_APPRAISAL_STATUS_1 foreign key (template_id) references appraisal_template (id),
  constraint FK_APPRAISAL_STATUS_2 foreign key (category_id) references appraisal_status_category (id),
  constraint FK_APPRAISAL_STATUS_3 foreign key (email_id1) references email_profile (email_profile_id),
  constraint FK_APPRAISAL_STATUS_4 foreign key (email_id2) references email_profile (email_profile_id)
)
GO

create table appraisal_rating_scale (
  id                            nvarchar(15)                 not null,
  template_id                   nvarchar(15)                 not null,
  scale_name                    nvarchar(200)                not null,
  display_format                nvarchar(30) default 'ICONS' not null,
  appraisee_complete_for_status nvarchar(15)                 null,
  appraiser_complete_for_status nvarchar(15)                 null,
  agreed_complete_for_status    nvarchar(15)                 null,
  constraint PK_APPRAISAL_RATING_SCALE primary key (id),
  constraint FK_APPRAISAL_RATING_SCALE_1 foreign key (template_id) references appraisal_template (id),
  constraint FK_APPRAISAL_RATING_SCALE_2 foreign key (appraisee_complete_for_status) references appraisal_status (id),
  constraint FK_APPRAISAL_RATING_SCALE_3 foreign key (appraiser_complete_for_status) references appraisal_status (id),
  constraint FK_APPRAISAL_RATING_SCALE_4 foreign key (agreed_complete_for_status) references appraisal_status (id)
)
GO

create table appraisal_rating (
  id              nvarchar(15)   not null,
  scale_id        nvarchar(15)   not null,
  rating          numeric(8, 2)  not null,
  description_key nvarchar(255)  not null,
  explanation_key nvarchar(2000) null,
  constraint PK_APPRAISAL_RATING primary key (id),
  constraint FK_APPRAISAL_RATING_1 foreign key (scale_id) references appraisal_rating_scale (id)
)
GO

create table appraisal_overall_rating (
  id              nvarchar(15)   not null,
  template_id     nvarchar(15)   not null,
  title_key       nvarchar(255)  not null,
  description_key nvarchar(2000) not null,
  from_value      numeric(4, 2)  not null,
  to_value        numeric(4, 2)  not null,
  constraint PK_APPRAISAL_OVERALL_RATING primary key (id),
  constraint FK_APPRAISAL_OVERALL_RATING_1 foreign key (template_id) references appraisal_template (id)
)
GO

create table appraisal_template_page (
  id            nvarchar(15)   not null,
  template_id   nvarchar(15)   not null,
  part          char(1)        not null,
  display_order int            not null,
  title_key     nvarchar(1000) not null,
  constraint PK_APPRAISAL_TEMPLATE_PAGE primary key (id),
  constraint FK_APPRAISAL_TEMPLATE_PAGE foreign key (template_id) references appraisal_template (id)
)
GO

create table appraisal_template_section (
  id                   nvarchar(15)   not null,
  section_code         nvarchar(15)   null,
  type                 char(1)        not null,
  page_id              nvarchar(15)   not null,
  display_order        int            not null,
  title_key            nvarchar(1000) null,
  description_key      nvarchar(2000) null,
  visible_for_statuses nvarchar(15)   null,
  constraint PK_APPRAISAL_TEMPLATE_SEC primary key (id),
  constraint FK_APPRAISAL_TEMPLATE_SEC_F1 foreign key (page_id) references appraisal_template_page (id)
)
GO

create table appraisal_s_free_text (
  section_id   nvarchar(15)        not null,
  input_type   char(1) default 'A' not null,
  copy_key     nvarchar(15)        null,
  allow_copy   char(1) default 'N' not null,
  editable_for char(1) default '1' not null,
  constraint PK_APPRAISAL_S_FREE_TEXT primary key (section_id),
  constraint FK_APPRAISAL_S_FREE_TEXT_F1 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table appraisal_s_achievement_rec (
  section_id                 nvarchar(15)        not null,
  rating_scale_id            nvarchar(15)        null,
  max_objectives             int                 not null,
  num_entries                int default 1       not null,
  display_target_date        char(1) default 'Y' not null,
  display_how                char(1) default 'Y' not null,
  display_details            char(1) default 'Y' not null,
  display_appraisee_rating   char(1) default 'Y' not null,
  display_appraiser_rating   char(1) default 'N' not null,
  display_agreed_rating      char(1) default 'N' not null,
  allow_copy                 char(1) default 'N' not null,
  auto_copy                  char(1) default 'N' not null,
  header_objective_key       nvarchar(255)       null,
  header_target_date_key     nvarchar(255)       null,
  header_how_key             nvarchar(255)       null,
  header_details_key         nvarchar(255)       null,
  header_allow_copy_key      nvarchar(255)       null,
  average_rating             char(1) default 'N' not null,
  restrict_rating_visibility char(1) default 'N' not null,
  average_rating_label_key   nvarchar(255)       null,
  copy_key                   nvarchar(15)        null,
  editable_for               char(1) default 'A' not null,
  constraint PK_APPRAISAL_S_ACHIEV_REC primary key (section_id),
  constraint FK_APPRAISAL_S_ACHIEV_REC_F1 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_S_ACHIEV_REC_F2 foreign key (rating_scale_id) references appraisal_rating_scale (id)
)
GO

create table appraisal_s_competency (
  section_id                    nvarchar(15)        not null,
  rating_scale_id               nvarchar(15)        null,
  display_appraisee_rating      char(1) default 'Y' not null,
  display_appraiser_rating      char(1) default 'N' not null,
  display_agreed_rating         char(1) default 'N' not null,
  allow_copy                    char(1) default 'N' not null,
  header_allow_copy_key         nvarchar(255)       null,
  restrict_rating_visibility    char(1) default 'N' not null,
  average_rating                char(1) default 'N',
  average_rating_label_key      nvarchar(255)       null,
  copy_key                      nvarchar(15)        null,
  editable_for                  char(1) default 'A' not null,
  show_jp_comp                  char(1) default 'Y' not null,
  show_appraisee_comp           char(1) default 'Y' not null,
  use_comp_rating               char(1) default 'N' not null,
  appraisee_complete_for_status nvarchar(15)        null,
  appraiser_complete_for_status nvarchar(15)        null,
  agreed_complete_for_status    nvarchar(15)        null,
  use_sel_comp                  char(1) default 'N' not null,
  include_comp_rating           char(1) default 'N' not null,
  display_required_level        char(1) default 'Y' not null,
  display_previous_level        char(1) default 'Y' not null,
  display_appraisee_comments    char(1) default 'Y' not null,
  display_appraiser_comments    char(1) default 'Y' not null,
  constraint PK_APPRAISAL_S_COMPETENCY primary key (section_id),
  constraint FK_APPRAISAL_S_COMPETENCY_F1 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_S_COMPETENCY_F2 foreign key (rating_scale_id) references appraisal_rating_scale (id),
  constraint FK_APPRAISAL_S_COMPETENCY_F3 foreign key (appraisee_complete_for_status) references appraisal_status (id),
  constraint FK_APPRAISAL_S_COMPETENCY_F4 foreign key (appraiser_complete_for_status) references appraisal_status (id),
  constraint FK_APPRAISAL_S_COMPETENCY_F5 foreign key (agreed_complete_for_status) references appraisal_status (id)
)
GO

create table appraisal_competency (
  id         nvarchar(15) not null,
  section_id nvarchar(15) not null,
  constraint PK_APPRAISAL_COMPETENCY primary key (id, section_id),
  constraint FK_APPRAISAL_COMPETENCY_1 foreign key (id) references individual_competency (id),
  constraint FK_APPRAISAL_COMPETENCY_2 foreign key (section_id) references appraisal_s_competency (section_id)
)
GO

create table appraisal_s_single_choice (
  section_id                   nvarchar(15)        not null,
  display_appraisee_rating     char(1) default 'Y' not null,
  display_appraiser_rating     char(1) default 'N' not null,
  display_agreed_rating        char(1) default 'N' not null,
  restrict_rating_visibility   char(1) default 'N' not null,
  display_in_summary           char(1) default '3' not null,
  editable_for                 char(1) default 'A' not null,
  appraisee_complete_status_id nvarchar(15)        null,
  appraiser_complete_status_id nvarchar(15)        null,
  agreed_complete_status_id    nvarchar(15)        null,
  constraint PK_APPRAISAL_S_SC primary key (section_id),
  constraint FK_APPRAISAL_S_SC_F1 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_S_SC_F2 foreign key (appraisee_complete_status_id) references appraisal_status (id),
  constraint FK_APPRAISAL_S_SC_F3 foreign key (appraiser_complete_status_id) references appraisal_status (id),
  constraint FK_APPRAISAL_S_SC_F4 foreign key (agreed_complete_status_id) references appraisal_status (id)
)
GO

create table appraisal_s_succession_plan (
  section_id                   nvarchar(15)        not null,
  editable_for                 char(1) default '6' not null,
  appraiser_complete_status_id nvarchar(15)        null,
  constraint PK_APPRAISAL_S_SP primary key (section_id),
  constraint FK_APPRAISAL_S_SP_F1 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_S_SP_F3 foreign key (appraiser_complete_status_id) references appraisal_status (id)
)
GO

create table appraisal_s_sp_question (
  question_id        nvarchar(15)        not null,
  succession_plan_id nvarchar(15)        not null,
  question_text      nvarchar(2000)      not null,
  display_order      int                 not null,
  question_type      char(1) default 'P' not null,
  constraint PK_APPRAISAL_S_SP_QUESTION primary key (question_id),
  constraint FK_APPRAISAL_S_SP_QUESTION_F2 foreign key (succession_plan_id) references appraisal_s_succession_plan (section_id)
)
GO

create table appraisal_s_sc_option (
  id               nvarchar(15)   not null,
  single_choice_id nvarchar(15)   null,
  display_order    int            not null,
  description_key  nvarchar(1000) not null,
  question_id      nvarchar(15)   null,
  score            float          null,
  constraint PK_APPRAISAL_S_SC_OPTION primary key (id),
  constraint FK_APPRAISAL_S_SC_OPTION_F1 foreign key (single_choice_id) references appraisal_s_single_choice (section_id),
  constraint FK_APPRAISAL_S_SC_OPTION_F5 foreign key (question_id) references appraisal_s_sp_question (question_id)
)
GO

create table appraisal_s_future_objective (
  section_id              nvarchar(15)        not null,
  display_how             char(1) default 'Y' not null,
  display_details         char(1) default 'Y' not null,
  display_target_date     char(1) default 'Y' not null,
  header_objective_key    nvarchar(255)       null,
  header_target_date_key  nvarchar(255)       null,
  header_how_key          nvarchar(255)       null,
  header_details_key      nvarchar(255)       null,
  min_objectives          int                 not null,
  max_objectives          int                 not null,
  num_entries             int default 1       not null,
  copy_key                nvarchar(15)        null,
  allow_copy_competencies char(1) default 'N' not null,
  external_content_url    nvarchar(255) default null,
  external_content_width  int default 0       not null,
  external_content_height int default 0       not null,
  editable_for            char(1) default 'A' not null,
  constraint PK_APPRAISAL_S_FUTURE_OBJ primary key (section_id),
  constraint FK_APPRAISAL_S_FUTURE_OBJ_F1 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table appraisal_s_career_dev (
  section_id             nvarchar(15)        not null,
  display_how            char(1) default 'Y' not null,
  display_details        char(1) default 'Y' not null,
  display_measure        char(1) default 'Y' not null,
  display_target_date    char(1) default 'Y' not null,
  header_objective_key   nvarchar(255)       null,
  header_target_date_key nvarchar(255)       null,
  header_how_key         nvarchar(255)       null,
  header_details_key     nvarchar(255)       null,
  header_measure_key     nvarchar(255)       null,
  editable_for           char(1) default 'A' not null,
  constraint PK_APPRAISAL_S_CAREER_DEV primary key (section_id),
  constraint FK_APPRAISAL_S_CAREER_DEV_F1 foreign key (section_id) references appraisal_template_section (id)
)
GO

