create table appraisal_s_career_interval (
  id            nvarchar(15)   not null,
  career_dev_id nvarchar(15)   not null,
  interval_key  nvarchar(1000) not null,
  display_order int            not null,
  constraint PK_APPRAISAL_S_CAREER_INT primary key (id),
  constraint FK_APPRAISAL_S_CAREER_INT_F1 foreign key (career_dev_id) references appraisal_s_career_dev (section_id)
)
GO

create table appraisal_s_sign_off (
  section_id                   nvarchar(15)        not null,
  display_appraisee_comments   char(1) default 'Y' not null,
  display_appraiser_comments   char(1) default 'Y' not null,
  display_appraisee_acceptance char(1) default 'N' not null,
  display_appraiser_acceptance char(1) default 'N' not null,
  display_compliance_attitude  char(1) default 'N' not null,
  display_career_dev_dialogue  char(1) default 'N' not null,
  display_right_to_reply       char(1) default 'N' not null,
  constraint PK_APPRAISAL_S_SIGN_OFF primary key (section_id),
  constraint FK_APPRAISAL_S_SIGN_OFF_F1 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table user_appraisal (
  id                    nvarchar(15)        not null,
  appraisal_template_id nvarchar(15)        not null,
  appraisee_id          nvarchar(85)        not null,
  appraiser_id          nvarchar(85)        not null,
  created_user_id       nvarchar(85)        not null,
  created_date          datetime            not null,
  last_modified_user_id nvarchar(85)        not null,
  last_modified_date    datetime            not null,
  status_id             nvarchar(15)        null,
  status                char(1) default '1' not null,
  completed_date        datetime            null,
  super_appraiser_id    nvarchar(85)        null,
  constraint PK_USER_APPRAISAL primary key (id),
  constraint FK_USER_APPRAISAL_1 foreign key (appraisal_template_id) references appraisal_template (id),
  constraint FK_USER_APPRAISAL_2 foreign key (appraisee_id) references users (userid),
  constraint FK_USER_APPRAISAL_3 foreign key (status_id) references appraisal_status (id),
  constraint FK_USER_APPRAISAL_4 foreign key (super_appraiser_id) references users (userid),
  constraint FK_USER_APPRAISAL_5 foreign key (appraiser_id) references users (userid)
)
GO

create index NUK_USER_APPRAISAL_X1 on user_appraisal (created_date)
GO

create table appraisal_u_free_text (
  id           nvarchar(15) not null,
  appraisal_id nvarchar(15) not null,
  section_id   nvarchar(15) not null,
  entry_text   ntext        null,
  constraint PK_APPRAISAL_U_FREE_TEXT primary key (id),
  constraint FK_APPRAISAL_U_FREE_TEXT_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_FREE_TEXT_2 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table appraisal_u_past_objective (
  id               nvarchar(15)        not null,
  appraisal_id     nvarchar(15)        not null,
  section_id       nvarchar(15)        not null,
  display_order    int                 not null,
  entry_order      int default 0       not null,
  objective_desc   ntext               null,
  target_date      datetime            null,
  measurement_desc ntext               null,
  achievement_desc ntext               null,
  appraisee_rating nvarchar(15)        null,
  appraiser_rating nvarchar(15)        null,
  agreed_rating    nvarchar(15)        null,
  rating_na        char(1) default 'N' not null,
  carry_over       char(1) default 'N' not null,
  fixed            char(1) default 'N' not null,
  constraint PK_APPRAISAL_U_PAST_OBJ primary key (id),
  constraint FK_APPRAISAL_U_PAST_OBJ_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_PAST_OBJ_2 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_U_PAST_OBJ_3 foreign key (appraisee_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_PAST_OBJ_4 foreign key (appraiser_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_PAST_OBJ_5 foreign key (agreed_rating) references appraisal_rating (id)
)
GO

create table appraisal_u_competency (
  id                   nvarchar(15)        not null,
  appraisal_id         nvarchar(15)        not null,
  section_id           nvarchar(15)        not null,
  competency_id        nvarchar(15)        not null,
  appraisee_rating     nvarchar(15)        null,
  appraiser_rating     nvarchar(15)        null,
  agreed_rating        nvarchar(15)        null,
  appraisee_comp_level nvarchar(15)        null,
  appraiser_comp_level nvarchar(15)        null,
  agreed_comp_level    nvarchar(15)        null,
  rating_na            char(1) default 'N' not null,
  carry_over           char(1) default 'N' not null,
  appraisee_comments   ntext               null,
  appraiser_comments   ntext               null,
  constraint PK_APPRAISAL_U_COMP primary key (id),
  constraint FK_APPRAISAL_U_COMP_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_COMP_2 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_U_COMP_3 foreign key (competency_id) references individual_competency (id),
  constraint FK_APPRAISAL_U_COMP_4 foreign key (appraisee_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_COMP_5 foreign key (appraiser_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_COMP_6 foreign key (agreed_rating) references appraisal_rating (id)
)
GO

create table appraisal_u_single_choice (
  id               nvarchar(15),
  appraisal_id     nvarchar(15) not null,
  section_id       nvarchar(15) not null,
  appraisee_choice nvarchar(15) null,
  appraiser_choice nvarchar(15) null,
  agreed_choice    nvarchar(15) null,
  constraint PK_APPRAISAL_U_SC primary key (id),
  constraint FK_APPRAISAL_U_SC_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_SC_2 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_U_SC_3 foreign key (appraisee_choice) references appraisal_s_sc_option (id),
  constraint FK_APPRAISAL_U_SC_4 foreign key (appraiser_choice) references appraisal_s_sc_option (id),
  constraint FK_APPRAISAL_U_SC_5 foreign key (agreed_choice) references appraisal_s_sc_option (id)
)
GO

create table appraisal_u_future_objective (
  id                        nvarchar(15),
  appraisal_id              nvarchar(15)  not null,
  section_id                nvarchar(15)  not null,
  objective_desc            ntext         null,
  how_desc                  ntext         null,
  details_desc              ntext         null,
  target_date               datetime      null,
  display_order             int default 0 not null,
  entry_order               int default 0 not null,
  past_achievement_entry_id nvarchar(15)  null,
  constraint PK_APPRAISAL_U_FUTURE_OBJ primary key (id),
  constraint FK_APPRAISAL_U_FUTURE_OBJ_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_FUTURE_OBJ_2 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_U_FUTURE_OBJ_3 foreign key (past_achievement_entry_id) references appraisal_u_past_objective (id)
)
GO

create table appraisal_u_career_dev (
  id             nvarchar(15) not null,
  appraisal_id   nvarchar(15) not null,
  section_id     nvarchar(15) not null,
  interval_id    nvarchar(15) not null,
  display_order  int          not null,
  objective_desc ntext        null,
  how_desc       ntext        null,
  details_desc   ntext        null,
  measure_desc   ntext        null,
  target_date    datetime     null,
  constraint PK_APPRAISAL_U_CAREER_DEV primary key (id),
  constraint FK_APPRAISAL_U_CAREER_DEV_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_CAREER_DEV_2 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_U_CAREER_DEV_3 foreign key (interval_id) references appraisal_s_career_interval (id)
)
GO

create table appraisal_u_prob_sign_off (
  id                         nvarchar(15)        not null,
  appraisal_id               nvarchar(15)        not null,
  section_id                 nvarchar(15)        not null,
  outcome                    char(1) default 'P' not null,
  probation_extension        int                 null,
  probation_extension_reason ntext               null,
  constraint PK_APPRAISAL_U_PROB_SIGN_OFF primary key (id),
  constraint FK_APPRAISAL_U_PROB_SIGN_OFF_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_PROB_SIGN_OFF_2 foreign key (section_id) references appraisal_template_section (id),
  constraint CKC_APPRAISAL_U_PROB_SO_1 check (outcome in ('P', 'F', 'E'))
)
GO

create table appraisal_u_sign_off (
  id                   nvarchar(15)        not null,
  appraisal_id         nvarchar(15)        not null,
  section_id           nvarchar(15)        not null,
  appraisee_comments   ntext               null,
  appraiser_comments   ntext               null,
  compliance_attitude  ntext               null,
  appraisee_acceptance char(1) default 'N' not null,
  appraiser_acceptance char(1) default 'N' not null,
  career_dev_dialogue  char(1) default 'N' not null,
  appraisee_reply      ntext               null,
  overall_rating       float               null,
  average_goal_rating	float				null,
  average_objective_rating  float				null,
  average_competency_rating float				null,
  average_dimensions_rating float				null,
  CONSTRAINT PK_APPRAISAL_U_SIGN_OFF primary key (id),
  constraint FK_APPRAISAL_U_SIGN_OFF_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_SIGN_OFF_2 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table custom_course_policy (
  learning_id         nvarchar(85)  not null,
  object_type         nvarchar(5)   not null,
  object_id           nvarchar(85)  not null,
  policy_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_CUSTOM_COURSE_POLICY primary key (learning_id, object_type, object_id, policy_id)
)
GO

create table withdraw_ded_opt_pay_items (
  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_WDRAW_DED_OPT_PAY_ITM primary key (policy_id, day_before_start)
)
GO

create table withdraw_reason (
  id                  nvarchar(15)   not null,
  user_id             nvarchar(85)   not null,
  transcript_id       nvarchar(15)   not null,
  created_date        datetime       not null,
  reason              nvarchar(1000) 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_WITHDRAWREASON primary key (id),
  constraint WITHDRAW_REASON_TRANSCRIP_FK1 foreign key (transcript_id) references transcript (transcriptid),
  constraint WITHDRAW_REASON_USERS_FK1 foreign key (user_id) references users (userid)
)
GO

create table course_stats (
  course_id   nvarchar(85)   not null,
  usage_total int default 0  not null,
  constraint PK_COURSE_STATS primary key (course_id)
)
GO

create table appraisal_section_statuses (
  section_id nvarchar(15) not null,
  status_id  nvarchar(15) not null,
  constraint PK_APP_SECT_STATUSES primary key (section_id, status_id),
  constraint FK_APP_SEC_STAT_1 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APP_SEC_STAT_2 foreign key (status_id) references appraisal_status (id)
)
GO

create table user_appraisal_change_log (
  id           nvarchar(15)   not null,
  user_id      nvarchar(85)   not null,
  role_id      char(1)        not null,
  appraisal_id nvarchar(15)   not null,
  create_date  datetime       not null,
  from_status  nvarchar(15)   null,
  to_status    nvarchar(15)   not null,
  reason       nvarchar(1000) null,
  signed       char(1)        not null,
  constraint pk_user_appraisal_change_log primary key (id),
  constraint fk_user_appraisal_change_log_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint fk_user_appraisal_change_log_2 foreign key (user_id) references users (userid)
)
GO

create table appraisal_s_dimension (
  section_id                 nvarchar(15)        not null,
  display_appraisee_rating   char(1) default 'N' not null,
  display_appraiser_rating   char(1) default 'N' not null,
  display_agreed_rating      char(1) default 'N' not null,
  rating_scale_id            nvarchar(15)        null,
  restrict_rating_visibility char(1) default 'N' not null,
  display_appraisee_comments char(1) default 'N' not null,
  display_appraiser_comments char(1) default 'N' not null,
  average_rating             char(1) default 'N' not null,
  average_rating_label_key   nvarchar(255)       null,
  editable_for               char(1) default 'A' not null,
  constraint PK_APPRAISAL_S_DIMENSION primary key (section_id),
  constraint FK_APPRAISAL_S_DIMENSION_1 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_S_DIMENSION_2 foreign key (rating_scale_id) references appraisal_rating_scale (id)
)
GO

create table appraisal_dimension (
  id         nvarchar(15)   not null,
  section_id nvarchar(15)   not null,
  label_key  nvarchar(1000) not null,
  constraint PK_APPRAISAL_DIMENSION primary key (id),
  constraint FK_APPRAISAL_DIMENSION foreign key (section_id) references appraisal_s_dimension (section_id)
)
GO

create table appraisal_u_dimension (
  id                 nvarchar(15)        not null,
  appraisal_id       nvarchar(15)        not null,
  dimension_id       nvarchar(15)        not null,
  appraisee_rating   nvarchar(15)        null,
  appraiser_rating   nvarchar(15)        null,
  agreed_rating      nvarchar(15)        null,
  not_applicable     char(1) default 'N' not null,
  appraisee_comments ntext               null,
  appraiser_comments ntext               null,
  constraint PK_APPRAISAL_U_DIMENSION primary key (id),
  constraint FK_APPRAISAL_U_DIMENSION_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_DIMENSION_2 foreign key (dimension_id) references appraisal_dimension (id),
  constraint FK_APPRAISAL_U_DIMENSION_3 foreign key (appraisee_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_DIMENSION_4 foreign key (appraiser_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_DIMENSION_5 foreign key (agreed_rating) references appraisal_rating (id)
)
GO

create table vc_adobe_connect_trng (
  eventid            nvarchar(15) NOT NULL,
  ac_sco_id          nvarchar(85) NOT NULL,
  ac_title           nvarchar(85) NOT NULL,
  ac_start_date      datetime     NULL,
  ac_end_date        datetime     NULL,
  ac_folder_sco_id   nvarchar(85) NOT NULL,
  ac_template_sco_id nvarchar(85) NOT NULL,
  is_synched         char(1)      NOT NULL,
  date_created       datetime     NOT NULL,
  date_modified      datetime     NOT NULL,
  url_path           nvarchar(85) NULL,
  constraint PK_VC_AC primary key (eventid),
  constraint FK_VC_AC_EVENTID foreign key (eventid) references cschedule (eventid)
)
GO

create table content_security (
  rnd_key nvarchar(512) NOT NULL
)
GO

create table vc_horizon_wimba_acct (
  id             nvarchar(15) NOT NULL,
  display_name   nvarchar(85) NOT NULL,
  proxy_type     nvarchar(85) NOT NULL,
  http_proxy_add nvarchar(85) NOT NULL,
  server_url     nvarchar(85) NOT NULL,
  account        nvarchar(85) NOT NULL,
  secret         nvarchar(85) NOT NULL,
  login          nvarchar(85) NOT NULL,
  password       nvarchar(85) NOT NULL,
  constraint PK_WIMBA_ID primary key (id)
)
GO

create table vc_ac_acct (
  acid                nvarchar(15) NOT NULL,
  display_name        nvarchar(85) NOT NULL,
  account_url         nvarchar(85) NOT NULL,
  username            nvarchar(85) NOT NULL,
  main_password       nvarchar(85) NOT NULL,
  use_main_pwd        char(1)      NOT NULL,
  is_auto_rec_enabled char(1)      NOT NULL,
  date_created        datetime     NOT NULL,
  date_modified       datetime     NOT NULL,
  constraint PK_AC_ACCT primary key (acid)
)
GO

create table vc_ac_vc_config (
  course_id     nvarchar(85) NOT NULL,
  acid          nvarchar(15) NOT NULL,
  password      nvarchar(85) NOT NULL,
  date_created  datetime     NOT NULL,
  date_modified datetime     NOT NULL,
  constraint PK_AC_VC_CONFIG primary key (course_id),
  constraint FK_AC_VC_CONFIG_COURSEID foreign key (course_id) references learningObject (learningid),
  constraint FK_AC_VC_CONFIG_ACID foreign key (acid) references vc_ac_acct (acid)
)
GO

create table vc_ac_avc_config (
  course_id     nvarchar(85) NOT NULL,
  acid          nvarchar(15) NOT NULL,
  password      nvarchar(85) NOT NULL,
  launch_url    nvarchar(85) NOT NULL,
  date_created  datetime     NOT NULL,
  date_modified datetime     NOT NULL,
  constraint PK_AC_AVC_CONFIG primary key (course_id),
  constraint FK_AC_AVC_CONFIG_COURSEID foreign key (course_id) references learningObject (learningid)
)
GO

create table holiday_calendar (
  calendar_id nvarchar(15) NOT NULL,
  name        nvarchar(85) NOT NULL,
  constraint PK_HOL_CAL_ID primary key (calendar_id)
)
GO

create table holiday_date (
  holiday_date datetime     NOT NULL,
  calendar_id  nvarchar(15) NOT NULL,
  description  ntext        NULL,
  constraint PK_HOL_DATE primary key (holiday_date, calendar_id),
  constraint FK_HOL_DATE_CAL_ID foreign key (calendar_id) references holiday_calendar (calendar_id)
)
GO

create table training_ctr_holiday (
  training_center_id nvarchar(15) NOT NULL,
  calendar_id        nvarchar(15) NOT NULL,
  constraint PK_TRN_CTR_HOL primary key (training_center_id, calendar_id),
  constraint FK_TRN_CTR_HOL_TID foreign key (training_center_id) references code_training_center (training_center_id),
  constraint FK_TRN_CTR_HOL_CID foreign key (calendar_id) references holiday_calendar (calendar_id)
)
GO

create table mekp_license_configuration (
  record_key             char(1)       not null,
  feature_level          nvarchar(20)  not null,
  disable_no_sync_days   nvarchar(20)  null,
  n_users_allowed        nvarchar(20)  null,
  relative_ndays_expire  nvarchar(20)  null,
  absolute_expiration    datetime      null,
  creation_date          datetime      not null,
  last_updated           datetime      not null,
  record_checksum        nvarchar(100) null,
  local_serial_number    nvarchar(100) null,
  delimited_license_Opts nvarchar(100) null,
  constraint PK_MEKP_LICENSE primary key (record_key)
)
GO

create table delegation (
  record_key      nvarchar(15) not null,
  userid          nvarchar(85) not null,
  delegate_userid nvarchar(85) not null,
  startdate       datetime     not null,
  enddate         datetime     not null,
  constraint PK_DELEGATE_KEY primary key (record_key)
)
GO

create index NUK_DELEGATE_USER on delegation (userid)
GO

create index NUK_DELEGATE_USER2 on delegation (delegate_userid)
GO

create table sync_id_mapping (
  mapping_code nvarchar(3)  not null,
  ekp_id       nvarchar(85) not null,
  mekp_id      nvarchar(85) not null,
  constraint PK_SYNC_ID_MAPPING primary key (mapping_code, ekp_id),
  constraint UK_SYNC_ID_MAPPING unique (mapping_code, mekp_id)
)
GO

create index NUK_AUDITOBJECTS_TIME on auditobjects (actiontime)
GO

create index NUK_AUDITOBJECTS_OBJTYPE on auditobjects (objecttype)
GO

create table jobprofile_autoenroll (
  targetType          nvarchar(15)  not null,
  targetValue         nvarchar(85)  not null,
  jobprofileid        nvarchar(15)  not null,
  startdate           datetime      null,
  enddate             datetime      null,
  durationenabled     char(1)       not null,
  durationamount      int           null,
  durationunit        int           null,
  exclusion_flag      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,
  constraint PK_JOB_AUTOENROLL primary key (jobprofileid, targetType, targetValue)
)
GO

create table email_event_tracking (
  transcriptid     nvarchar(15) not null,
  email_event_type nvarchar(15) not null,
  nmsgs_sent       int          not null,
  last_updated     datetime     null,
  constraint PK_EMAIL_TRACKING primary key (transcriptid, email_event_type)
)
GO

create table appraisal_s_rating_summary (
  section_id   nvarchar(15) not null,
  summary_type nvarchar(30) not null,
  apply_weight char(1)      not null default 'N',
  constraint PK_APPRAISAL_S_RS primary key (section_id),
  constraint FK_APPRAISAL_S_RS_F1 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table appraisal_s_rs_weight (
  id         nvarchar(15)  not null,
  section_id nvarchar(15)  not null,
  page_id    nvarchar(15)  not null,
  weight     numeric(5, 2) not null,
  constraint PK_APPRAISAL_S_RS_WEIGHT primary key (id),
  constraint FK_APPRAISAL_S_RS_WEIGHT_F1 foreign key (section_id) references appraisal_s_rating_summary (section_id)
)
GO

create table appraisal_s_training_rec (
  section_id           nvarchar(15) not null,
  show_summarized_view char(1)      not null default 'N',
  constraint PK_APPRAISAL_S_TRAINING_REC primary key (section_id),
  constraint FK_APPRAISAL_S_TRAINING_REC_F1 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table tms_skill_dictionary (
  id              nvarchar(15)  not null, -- EKP000000000
  dictionary_name nvarchar(255) not null, -- "SFIA"
  level_group_id  nvarchar(15)  not null,
  imported_on     datetime      not null,
  imported_by     nvarchar(85)  not null, -- "ndadmin"
  constraint PK_TMS_SKILL_DICTIONARY primary key (id),
  constraint FK_TMS_SKILL_DICT_LVL_GP foreign key (level_group_id) references tms_responsibility_level_group (id)
)
GO

create table tms_skill_category (
  id            nvarchar(15)  not null, -- EKP000000002
  label         nvarchar(255) not null, -- "Information Strategy"
  parent_id     nvarchar(15)  null, -- EKP000000001 which could be "Strategy & Architecture"
  dictionary_id nvarchar(15)  not null, -- EKP000000000 which could be "SFIA"
  constraint PK_TMS_SKILL_CATEGORY primary key (id),
  constraint FK_TMS_SKILL_CATEGORY_1 foreign key (dictionary_id) references tms_skill_dictionary (id)
)
GO

create table tms_skill (
  id          nvarchar(15)   not null, -- EKP000000003
  category_id nvarchar(15)   not null, -- EKP000000002 which could be "Information Strategy"
  label       nvarchar(255)  not null, -- "Corporate governance of IT"
  code        nvarchar(15)   not null, -- "GOVN"
  desc_text   nvarchar(2000) null, -- "The planning and implementation of initiatives and procedures .... "
  constraint PK_TMS_SKILL primary key (id),
  constraint FK_TMS_SKILL_1 foreign key (category_id) references tms_skill_category (id)
)
GO

create table tms_skill_level (
  skill_id nvarchar(15)   not null, -- EKP000000003 which could be "Corporate governance of IT"
  level_id nvarchar(15)   not null, -- EKP000000004 which could be "Level 1"
  label    nvarchar(2000) not null, -- "The overall management of the control and exploitation of all ..."
  constraint PK_TMS_SKILL_LEVEL primary key (skill_id, level_id),
  constraint FK_TMS_SKILL_LEVEL_1 foreign key (skill_id) references tms_skill (id),
  constraint FK_TMS_SKILL_LEVEL_2 foreign key (level_id) references tms_responsibility_level (id)
)
GO

create table job_profile_competency (
  job_profile_id             nvarchar(15)        not null,
  competency_id              nvarchar(15)        not null,
  competency_order           int default 0       not null,
  mandatory                  char(1) default 'Y' not null,
  competency_target_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_job_profile_competency
  primary key (job_profile_id, competency_id),
  constraint fk_job_profile_competency_job_profile_id
  foreign key (job_profile_id) references job_profile (id),
  constraint fk_job_profile_competency_competency_id
  foreign key (competency_id) references individual_competency (id),
  constraint fk_jp_target_level_id
  foreign key (competency_target_level_id) references competency_level (id)
)
GO

create table competency_assessment_template (
  id                            nvarchar(85)  not null,
  title                         nvarchar(255) not null,
  desc_text                     ntext         null,
  instr_text                    ntext         null,
  valid_from                    datetime      null,
  valid_to                      datetime      null,
  start_msg_id                  nvarchar(15)  null,
  reminder_msg_id               nvarchar(15)  null,
  reminder_frequency            int           null,
  completion_msg_id             nvarchar(15)  null,
  overdue_msg_id                nvarchar(15)  null,
  sign_off_msg_id               nvarchar(15)  null,
  allow_comp_selection          char(1)       null,
  use_job_profile               char(1)       null,
  use_competency                char(1)       null,
  display_req_level             char(1)       null,
  finished_msg_id               nvarchar(15)  null,
  use_comp_specified            char(1)       null,
  display_s_reviewer_name       char(1)       null,
  display_s_reviewer_comment    char(1)       null,
  display_s_reviewer_rating     char(1)       null,
  allow_s_reviewer_done_partial char(1)       null,
  reviewee_select_s_reviewer    char(1)       null,
  accepted_msg_id               nvarchar(15)  null,
  allow_rating_control          char(1)       null,
  select_reviewers_msg_id       nvarchar(15)  null,
  approve_reviewers_msg_id      nvarchar(15)  null,
  display_reviewee_level        char(1)       null,
  constraint PK_COMP_ASSESS primary key (id)
)
GO

create table comp_assess_participant (
  template_id      nvarchar(85) not null,
  participant_type char(1)      not null,
  due_date         datetime     null,
  days_from_assign int          null,
  weight_ratio     float        not null,
  deadline_type    char(1)      not null,
  number_of        int          not null,
  constraint PK_COMP_ASSESS_REVIEWER primary key (template_id, participant_type),
  constraint FK_COMP_ASSESS_REVIEWER_1 foreign key (template_id) references competency_assessment_template (id)
)
GO

create table competency_assessment (
  id                            nvarchar(85)  not null,
  ref_template                  nvarchar(85)  null,
  reviewee_id                   nvarchar(85)  not null,
  status                        char(2)       not null,
  title                         nvarchar(255) not null,
  desc_text                     ntext         null,
  instr_text                    ntext         null,
  start_msg_id                  nvarchar(15)  null,
  reminder_msg_id               nvarchar(15)  null,
  reminder_frequency            int           null,
  completion_msg_id             nvarchar(15)  null,
  overdue_msg_id                nvarchar(15)  null,
  sign_off_msg_id               nvarchar(15)  null,
  date_created                  datetime      not null,
  last_updated                  datetime      null,
  display_req_level             char(1)       null,
  finished_msg_id               nvarchar(15)  null,
  display_s_reviewer_name       char(1)       null,
  display_s_reviewer_comment    char(1)       null,
  display_s_reviewer_rating     char(1)       null,
  allow_s_reviewer_done_partial char(1)       null,
  accepted_msg_id               nvarchar(15)  null,
  allow_rating_control          char(1)       null,
  select_reviewers_msg_id       nvarchar(15)  null,
  approve_reviewers_msg_id      nvarchar(15)  null,
  display_reviewee_level        char(1)       null,
  constraint PK_COMPETENCY_ASSESSMENT primary key (id)
)
GO

create table comp_assess_user_participant (
  id                        nvarchar(85) not null,
  comp_assess_id            nvarchar(85) not null,
  user_id                   nvarchar(85) null,
  participant_type          char(1)      not null,
  status                    char(2)      not null,
  due_date                  datetime     not null,
  weight_ratio              float        not null,
  overall_comments          ntext        null,
  overall_comments_modified char(1)      not null default 'N',
  constraint PK_COMP_ASSESS_USER_PTCP primary key (id),
  constraint FK_COMP_ASSESS foreign key (comp_assess_id) references competency_assessment (id)
)
GO

create table session_attribute (
  learningid           nvarchar(85)   not null,
  sessionid            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_SESSION_ATTR primary key (learningid, sessionid, attribute_id),
  CONSTRAINT FK_SESSION_ATTR_ATTR_ID FOREIGN KEY (attribute_id) REFERENCES dynamic_attribute (attribute_id),
  CONSTRAINT FK_SESSION_ATTR_LEARNINGID FOREIGN KEY (learningid) REFERENCES learningObject (learningid)
)
GO

create table comp_assess_participant_eval (
  id                   nvarchar(85) not null,
  participant_id       nvarchar(85) not null,
  job_profile_id       nvarchar(15) null,
  competency_id        nvarchar(15) not null,
  proficiency_level_id nvarchar(15) null,
  evidence             ntext        null,
  evidence_modified    char(1)      not null default 'N',
  CONSTRAINT PK_PARTICIPANT_EVAL PRIMARY KEY (id),
  CONSTRAINT FK_PARTICIPANT_EVAL_PART_ID FOREIGN KEY (participant_id) REFERENCES comp_assess_user_participant (id)
)
GO

create table comp_assess_reminder_tracking (
  comp_assess_id nvarchar(85) not null,
  user_id        nvarchar(85) not null,
  reminder_type  char(1)      not null,
  nmsgs_sent     int          not null,
  last_updated   datetime     null,
  CONSTRAINT PK_COMP_ASSESS_RMD_TRACKING PRIMARY KEY (comp_assess_id, user_id, reminder_type)
)
GO

create table comp_assess_user_training_plan (
  assessment_id nvarchar(85) not null,
  competency_id nvarchar(15) not null,
  learning_id   nvarchar(85) not null,
  doby          datetime     null,
  priority      nvarchar(1)  not null,
  auto          nvarchar(1)  not null,
  CONSTRAINT PK_USER_DEV_PLAN PRIMARY KEY (assessment_id, competency_id, learning_id),
  CONSTRAINT FK_PARTICIPANT_DEV_PLAN FOREIGN KEY (assessment_id) REFERENCES competency_assessment (id)
)
GO

create table shared_files (
  id           nvarchar(85)  not null,
  filename     nvarchar(85)  not null,
  display_name nvarchar(85)  null,
  path         nvarchar(255) null,
  description  nvarchar(255) null,
  owner_id     nvarchar(85)  not null,
  uploaded_by  nvarchar(85)  null,
  upload_date  datetime      null,
  CONSTRAINT PK_SHARED_FILES PRIMARY KEY (id),
  CONSTRAINT FK_OWNER_ID FOREIGN KEY (owner_id) REFERENCES users (userid)
)
GO

create table comp_assess_template_comp (
  template_id   nvarchar(85) not null,
  competency_id nvarchar(15) not null,
  CONSTRAINT PK_COMP_ASSESS_TEMPLATE_COMP PRIMARY KEY (template_id, competency_id),
  CONSTRAINT FK_COMP_ASSESS_TEMPLATE FOREIGN KEY (template_id) REFERENCES competency_assessment_template (id)
)
GO

create table goal_program (
  id          nvarchar(85)  not null,
  name        nvarchar(255) not null,
  description ntext         not null,
  start_date  datetime      not null,
  end_date    datetime      not null,
  CONSTRAINT PK_GOAL_PROGRAM PRIMARY KEY (id),
  CONSTRAINT UK_GOAL_PROGRAM UNIQUE (name)
)
GO

create table goal_category (
  id         nvarchar(85)  not null,
  name       nvarchar(255) not null,
  program_id nvarchar(85)  not null,
  CONSTRAINT PK_GOAL_CATEGORY PRIMARY KEY (id),
  CONSTRAINT UK_GOAL_CATEGORY UNIQUE (program_id, name),
  CONSTRAINT FK_GOAL_CATEGORY_ID FOREIGN KEY (program_id) REFERENCES goal_program (id)
)
GO

create table goal (
  id                     nvarchar(85)  not null,
  title                  nvarchar(255) not null,
  description            ntext         null,
  is_available_appraisal char(1)       not null,
  owner_id               nvarchar(85)  null,
  CONSTRAINT PK_GOAL PRIMARY KEY (id),
  CONSTRAINT FK_GOAL_ASSIGN_TO FOREIGN KEY (owner_id) REFERENCES users (userid)
)
GO

create table goal_organizational (
  id                   nvarchar(85)        not null,
  parent_id            nvarchar(85)        null,
  program_id           nvarchar(85)        not null,
  category_id          nvarchar(85)        null,
  auto_update_tracking char(1) default 'N' not null,
  show_all_tracking    char(1) default 'N' not null,
  CONSTRAINT PK_GOAL_ORG PRIMARY KEY (id),
  CONSTRAINT FK_GOAL_ORG_GOAL FOREIGN KEY (id) REFERENCES goal (id),
  CONSTRAINT FK_GOAL_ORG_PROG_ID FOREIGN KEY (program_id) REFERENCES goal_program (id),
  CONSTRAINT FK_GOAL_ORG_CAT_ID FOREIGN KEY (category_id) REFERENCES goal_category (id),
  CONSTRAINT FK_GOAL_ORG_PARENT_ID FOREIGN KEY (parent_id) REFERENCES goal_organizational (id)
)
GO

create table goal_personal (
  id                 nvarchar(85) not null,
  start_date         datetime     not null,
  target_date        datetime     not null,
  weight             int          not null,
  measurement        ntext        null,
  org_goal_id        nvarchar(85) null,
  created_by         nvarchar(85) not null,
  created_date       datetime     not null,
  last_modified_by   nvarchar(85) not null,
  last_modified_date datetime     not null,
  date_closed        datetime     null,
  CONSTRAINT PK_GOAL_PERSON PRIMARY KEY (id),
  CONSTRAINT FK_GOAL_PERSON_GOAL FOREIGN KEY (id) REFERENCES goal (id),
  CONSTRAINT FK_GOAL_PERSON_ORG_GOAL_ID FOREIGN KEY (org_goal_id) REFERENCES goal_organizational (id)
)
GO

create table goal_tracking (
  id                   nvarchar(85) not null,
  goal_id              nvarchar(85) not null,
  event_type           char(1)      not null,
  result_to_date       ntext        null,
  result_achieved_date datetime     not null,
  progress             int          null,
  status               char(1)      not null,
  created_by           nvarchar(85) not null,
  date_created         datetime     not null,
  CONSTRAINT PK_GOAL_TRACKING PRIMARY KEY (id),
  CONSTRAINT FK_GOAL_TRACKING_GOAL FOREIGN KEY (goal_id) REFERENCES goal (id)
)
GO

CREATE INDEX NUK_GOAL_TRACKING_GOAL ON goal_tracking (goal_id)
GO

create table exam_workflow (
  ew_id               nvarchar(15)  not null,
  display_name        nvarchar(85)  null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_exam_workflow primary key (ew_id)
)
GO

create table exam_workflow_step (
  ews_id              nvarchar(15)    not null,
  ew_id               nvarchar(15)    not null,
  testid              nvarchar(85)    not null,
  type                char(1)         null,
  description         nvarchar(255)   null,
  target_assessor     char(1)         null,
  email_profile_id    nvarchar(15)    null,
  no_days             int             null,
  is_after            char(1)         null,
  is_at_course_start  char(1)         null,
  frequency           char(1)         null,
  reminder_send_limit int default 0   not null,
  step_order          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_exam_workflow_step primary key (ews_id),
  constraint FK_exam_workflow_step01 foreign key (ew_id) references exam_workflow (ew_id),
  constraint FK_exam_workflow_step02 foreign key (testid) references mastertest (testid)
)
GO

create table appraisal_s_goal_result (
  section_id                 nvarchar(15)        not null,
  rating_scale_id            nvarchar(15)        null,
  selection_method           char(1)             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_appraisee_comments char(1) default 'N' not null,
  display_appraiser_comments char(1) default 'N' not null,
  average_rating             char(1) default 'N' not null,
  average_rating_label_key   nvarchar(255)       null,
  editable_for               char(1) default '1' not null,
  constraint PK_APPRAISAL_S_GOAL_RESULT primary key (section_id),
  constraint FK_APPRAISAL_S_GOAL_RESULT_F1 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_S_GOAL_RESULT foreign key (rating_scale_id) references appraisal_rating_scale (id)
)
GO

create table appraisal_u_goal_result (
  id                 nvarchar(15) not null,
  appraisal_id       nvarchar(15) not null,
  section_id         nvarchar(15) not null,
  goal_id            nvarchar(85) not null,
  appraisee_comments ntext        null,
  appraiser_comments ntext        null,
  appraisee_rating   nvarchar(15) null,
  appraiser_rating   nvarchar(15) null,
  agreed_rating      nvarchar(15) null,
  constraint PK_APPRAISAL_U_GOAL_RESULT primary key (id),
  constraint FK_APPRAISAL_U_GOAL_RESULT_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_GOAL_RESULT_2 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_U_GOAL_RESULT_3 foreign key (goal_id) references goal (id),
  constraint FK_APPRAISAL_U_GOAL_RESULT_4 foreign key (appraisee_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_GOAL_RESULT_5 foreign key (appraiser_rating) references appraisal_rating (id),
  constraint FK_APPRAISAL_U_GOAL_RESULT_6 foreign key (agreed_rating) references appraisal_rating (id)
)
GO

create table appraisal_s_goal_plan (
  section_id   nvarchar(15)        not null,
  editable_for char(1) default '1' not null,
  constraint PK_APPRAISAL_S_GOAL_PLAN primary key (section_id),
  constraint FK_APPRAISAL_S_GOAL_PLAN_F1 foreign key (section_id) references appraisal_template_section (id)
)
GO

create table appraisal_u_goal_plan (
  id           nvarchar(15) not null,
  appraisal_id nvarchar(15) not null,
  section_id   nvarchar(15) not null,
  goal_id      nvarchar(85) not null,
  constraint PK_APPRAISAL_U_GOAL_PLAN primary key (id),
  constraint FK_APPRAISAL_U_GOAL_PLAN_1 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_GOAL_PLAN_2 foreign key (section_id) references appraisal_template_section (id),
  constraint FK_APPRAISAL_U_GOAL_PLAN_3 foreign key (goal_id) references goal (id)
)
GO

create table code_subject_category (
  subject_category_id nvarchar(15) not null,
  description         nvarchar(85) not null,
  constraint PK_CODE_SUBJECT_CAT primary key (subject_category_id)
)
GO

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

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

CREATE VIEW goal_status_prg_vw AS
  SELECT
    got.id,
    got.goal_id,
    got.event_type,
    got.result_to_date,
    got.result_achieved_date,
    got.progress,
    got.status,
    got.created_by
  FROM goal_tracking got, goal gop
  WHERE got.goal_id = gop.id
        AND got.id = (SELECT
                        TOP 1 got2.id
                      FROM goal_tracking got2
                      WHERE got2.goal_id = got.goal_id
                      ORDER BY got2.result_achieved_date desc, got2.date_created desc, got2.id desc)
GO

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

CREATE VIEW goal_vw AS
  SELECT
    g.id,
    'O'  as goal_type,
    g.title,
    g.description,
    g.is_available_appraisal,
    g.owner_id,
    gorg.parent_id,
    gorg.program_id,
    gorg.category_id,
    NULL as start_date,
    NULL as target_date,
    NULL as weight,
    NULL as measurement,
    NULL as org_goal_id,
    NULL as org_goal_title,
    NULL as created_by,
    gt.status,
    gt.progress,
    gt.result_achieved_date,
    gorg.auto_update_tracking,
    gt.result_to_date
  FROM goal g
    INNER JOIN goal_organizational gorg
      ON g.id = gorg.id
    LEFT JOIN goal_status_prg_vw gt
      ON gt.goal_id = gorg.id
  UNION ALL
  SELECT
    g.id,
    'P'               as goal_type,
    g.title,
    g.description,
    g.is_available_appraisal,
    g.owner_id,
    NULL              as parent_id,
    gorg2.program_id  as program_id,
    gorg2.category_id as category_id,
    gp.start_date,
    gp.target_date,
    gp.weight,
    gp.measurement,
    gp.org_goal_id,
    gorg.title        as org_goal_title,
    gp.created_by,
    gt.status,
    gt.progress,
    gt.result_achieved_date,
    NULL              as auto_update_tracking,
    gt.result_to_date
  FROM goal g
    INNER JOIN goal_personal gp
      ON g.id = gp.id
    LEFT JOIN goal gorg
      ON gp.org_goal_id = gorg.id
    LEFT JOIN goal_organizational gorg2
      ON gorg2.id = gorg.id
    LEFT JOIN goal_status_prg_vw gt
      ON gt.goal_id = gp.id
GO

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

CREATE VIEW user_job_profile_competency_vw AS
  SELECT
    ujp.user_id,
    jp.id                              AS job_profile_id,
    jp.name                            AS job_profile_name,
    c.name,
    rl.level_order,
    jpcrl.level_order                  AS target_level_order,
    uc.assessment_mode,
    uc.assigned_by,
    uc.assigned_comment,
    jpc.competency_id,
    uc.competency_level_id,
    uc.date_assigned,
    rl.level_order - jpcrl.level_order AS gap,
    CASE
    WHEN rl.level_order IS NULL THEN 'N'
    WHEN jpcrl.level_order > rl.level_order THEN 'N'
    ELSE 'Y'
    END                                AS met,
    rl.code                            AS level_code,
    jpcrl.code                         AS target_level_code,
    jpc.competency_order,
    c.acquire_method,
    c.requires_all,
    c.n_completions_required,
    c.reference_code,
    c.description
  FROM user_job_profile ujp
    LEFT OUTER JOIN job_profile_competency jpc
      ON ujp.job_profile_id = jpc.job_profile_id
    LEFT OUTER JOIN user_competency uc
      ON jpc.competency_id = uc.competency_id AND ujp.user_id = uc.user_id
    LEFT OUTER JOIN competency_level cl
      ON cl.id = uc.competency_level_id
    LEFT OUTER JOIN tms_responsibility_level rl
      ON cl.res_id = rl.id
    LEFT OUTER JOIN competency_level jpcl
      ON jpcl.id = jpc.competency_target_level_id
    LEFT OUTER JOIN tms_responsibility_level jpcrl
      ON jpcrl.id = jpcl.res_id
    ,
    individual_competency c, job_profile jp
  WHERE jp.id = ujp.job_profile_id
        AND jpc.job_profile_id = ujp.job_profile_id
        AND jpc.competency_id = c.id
        AND c.id = jpc.competency_id
GO

create table wiki_interaction_log (
  id               nvarchar(85)  not null,
  service_call     nvarchar(255) not null,
  call_param       ntext         not null,
  token            nvarchar(255) not null,
  status           char(1)       null,
  error_msg        ntext         null,
  initiator_userid nvarchar(85)  not null,
  create_date      datetime      not null,
  CONSTRAINT PK_WIKI_USER_MNGT_LOG PRIMARY KEY (id)
)
GO

create table transcript_sync_history (
  id            nvarchar(85) not null,
  transcript_id nvarchar(15) not null,
  sync_date     datetime     not null,
  sync_type     nvarchar(50) not null,
  sync_method   nvarchar(50) not null,
  response_code nvarchar(4)  not null,
  sync_user_id  nvarchar(85) null,
  constraint PK_TRANSCRIPT_HISTORY primary key (id),
  constraint FK_HISTORY_TRANSCRIPT_ID foreign key (transcript_id) references transcript (transcriptid)
)
GO

create table personal_event (
  id        nvarchar(85) not null,
  userid    nvarchar(85) not null,
  title     nvarchar(85) not null,
  date_from datetime     not null,
  date_to   datetime     not null,
  all_day   char(1)      not null,
  constraint PK_PERSONAL_EVENT primary key (id),
  constraint FK_USER_ID foreign key (userid) references users (userid)
)
GO

CREATE TABLE user_comp_msg_tracking (
  user_id       nvarchar(85) not null,
  competency_id nvarchar(15) not null,
  learning_id   nvarchar(85) null,
  msg_type      char(1)      not null,
  msg_sent      datetime     not null
)
GO

create index nuk_user_comp_msg_tracking on user_comp_msg_tracking (user_id, competency_id, msg_type, learning_id)
GO

CREATE TABLE withdraw_ded_fixed_amount (
  learningid          nvarchar(85)  not null,
  day_before_start    int           not null,
  fixed_deduction     float         not null,
  last_updated        datetime      null,
  update_meaning      nvarchar(8)   null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  CONSTRAINT PK_WITHDRAW_DED_FIXED_AMOUNT primary key (learningid, day_before_start)
)
GO

create table session_tutor_competency (
  learningid                 nvarchar(85) not null,
  eventid                    nvarchar(15) not null,
  competency_target_level_id nvarchar(15) not null,
  constraint PK_SESSION_TUTOR_COMPETENCY primary key (learningid, eventid, competency_target_level_id),
  constraint FK_SESSION_TUTOR_COMPETENCY_1 foreign key (competency_target_level_id) references competency_level (id)
)
GO

create table external_link (
  linkid  nvarchar(85)  not null,
  linkurl nvarchar(255) not null,
  CONSTRAINT PK_EXTERNAL_LINK primary key (linkid),
  CONSTRAINT UK_EXTERNAL_LINK unique (linkurl)
)
GO

create table job_profile_comp_stats (
  job_profile_stat_id nvarchar(85) not null,
  leaf_org_id         nvarchar(85) null,
  orgid_level_1       nvarchar(85) null,
  orgid_level_2       nvarchar(85) null,
  orgid_level_3       nvarchar(85) null,
  orgid_level_4       nvarchar(85) null,
  orgid_level_5       nvarchar(85) null,
  user_attr_1         nvarchar(85) null,
  user_attr_2         nvarchar(85) null,
  user_attr_3         nvarchar(85) null,
  user_attr_4         nvarchar(85) null,
  user_attr_5         nvarchar(85) null,
  user_attr_6         nvarchar(85) null,
  user_attr_7         nvarchar(85) null,
  user_attr_8         nvarchar(85) null,
  week_number         int          not null,
  snapshot_year       int          not null,
  job_profile_id      nvarchar(15) null,
  CONSTRAINT PK_JOB_PROFILE_COMP_STATS primary key (job_profile_stat_id)
)
GO

create table job_profile_comp_stats_dtl (
  job_profile_stat_id nvarchar(85)        not null,
  learning_id         nvarchar(85)        not null,
  user_id             nvarchar(85)        not null,
  finished            char(1) default 'N' null,
  constraint PK_JOB_PROFILE_COMP_STATS_DTL primary key (job_profile_stat_id, learning_id, user_id),
  CONSTRAINT FK_JOB_PROFILE_COMP_STATS_DTL foreign key (job_profile_stat_id)
  references job_profile_comp_stats (job_profile_stat_id)
)
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    ui,
    'SKIN',
    1,
    'OR'
  FROM screenLayout
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    ui,
    'SKIN',
    2,
    'OR'
  FROM screenLayout
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    ui,
    'SKIN',
    '*ROOT*',
    1,
    'O'
  FROM screenLayout
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    ui,
    'SKIN',
    '*ROOT*',
    2,
    'O'
  FROM screenLayout
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    extension_no,
    'UAE',
    1,
    'OR'
  FROM user_extension_label
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    extension_no,
    'UAE',
    2,
    'OR'
  FROM user_extension_label
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    extension_no,
    'UAE',
    '*ROOT*',
    1,
    'O'
  FROM user_extension_label
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    extension_no,
    'UAE',
    '*ROOT*',
    2,
    'O'
  FROM user_extension_label
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    roleid,
    'RO',
    1,
    'OR'
  FROM code_role
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    roleid,
    'RO',
    2,
    'OR'
  FROM code_role
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    roleid,
    'RO',
    '*ROOT*',
    1,
    'O'
  FROM code_role
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    roleid,
    'RO',
    '*ROOT*',
    2,
    'O'
  FROM code_role
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    attribute_id,
    'DA',
    1,
    'OR'
  FROM dynamic_attribute
  WHERE category = 'LO' or category = 'SE'
GO

INSERT INTO permissions_operator
(objectid, objecttype, permissioncode, operator)
  SELECT
    attribute_id,
    'DA',
    2,
    'OR'
  FROM dynamic_attribute
  WHERE category = 'LO' or category = 'SE'
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    attribute_id,
    'DA',
    '*ROOT*',
    1,
    'O'
  FROM dynamic_attribute
  WHERE category = 'LO' or category = 'SE'
GO

INSERT INTO permissions
(objectid, objecttype, identityid, permissioncode, identitytype)
  SELECT
    attribute_id,
    'DA',
    '*ROOT*',
    2,
    'O'
  FROM dynamic_attribute
  WHERE category = 'LO' or category = 'SE'
GO

create table task_signoff_config (
  learning_id          nvarchar(85)         not null,
  signoff_type         char(1)              not null,
  last_updated         datetime             null,
  updater_legal_name   nvarchar(250)        null,
  update_meaning_text  ntext                null,
  last_updater_uid     nvarchar(85)         null,
  check_pre_requisites char(1) default 'Y'  not null,
  CONSTRAINT PK_TASK_SIGNOFF_OPT primary key (learning_id),
  CONSTRAINT FK_TASK_SIGNOFF_LID foreign key (learning_id) references learningObject (learningid)
)
GO

create table task_signoff_users (
  transcript_id       nvarchar(15)  not null,
  signoff_user_id     nvarchar(85)  null,
  signoff_date        datetime      null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  CONSTRAINT PK_TASK_SIGNOFF_USER primary key (transcript_id),
  CONSTRAINT FK_TASK_SIGNOFF_USER_TID foreign key (transcript_id) references transcript (transcriptid)
)
GO

create table appraisal_u_succession_plan (
  id               nvarchar(15) not null,
  appraisal_id     nvarchar(15) not null,
  section_id       nvarchar(15) not null,
  question_id      nvarchar(15) not null,
  appraiser_choice nvarchar(15) null,
  constraint PK_APPRAISAL_U_SP primary key (id),
  constraint FK_APPRAISAL_U_SP_F2 foreign key (appraisal_id) references user_appraisal (id),
  constraint FK_APPRAISAL_U_SP_F3 foreign key (section_id) references appraisal_s_succession_plan (section_id),
  constraint FK_APPRAISAL_U_SP_F4 foreign key (question_id) references appraisal_s_sp_question (question_id),
  constraint FK_APPRAISAL_U_SP_F5 foreign key (appraiser_choice) references appraisal_s_sc_option (id)
)
GO

create table user_profile_education (
  educationId nvarchar(85)        not null,
  userid      nvarchar(85)        not null,
  startDate   datetime            not null,
  endDate     datetime            null,
  present     char(1) default 'N' not null,
  institution nvarchar(250)       not null,
  degree      nvarchar(250)       not null,
  program     nvarchar(250)       not null,
  location    nvarchar(250)       null,
  CONSTRAINT PK_USER_PROFILE_EDUCATION primary key (educationId),
  CONSTRAINT FK_USER_PROFILE_EDU_USERS foreign key (userid) references users (userid)
)
GO

create table user_profile_work_history (
  work_history_id nvarchar(85)  not null,
  user_id         nvarchar(85)  not null,
  company_name    nvarchar(250) not null,
  job_title       nvarchar(250) not null,
  work_type       char(1)       not null,
  location        nvarchar(250) not null,
  start_date      datetime      not null,
  end_date        datetime      null,
  achievements    ntext         null,
  awards          ntext         null,
  CONSTRAINT PK_WORK_HIST_ID primary key (work_history_id),
  CONSTRAINT FK_WORK_HIST_UID foreign key (user_id) references users (userid)
)
GO

CREATE TABLE access_token (
  id         NVARCHAR(85)  NOT NULL,
  secret     NVARCHAR(85)  NOT NULL,
  user_id    NVARCHAR(85)  NOT NULL,
  client_url NVARCHAR(255) NOT NULL,
  created    DATETIME      NOT NULL,
  revoked    DATETIME      NULL,
  CONSTRAINT pk_access_token PRIMARY KEY (id),
  CONSTRAINT fk_access_token_user_id FOREIGN KEY (user_id) references users (userid)
)
GO

CREATE INDEX nuk_access_token ON access_token (revoked, user_id, client_url)
GO

CREATE TABLE user_profile_lang_skills (
  language_skills_id nvarchar(85)  not null,
  user_id            nvarchar(85)  not null,
  language           nvarchar(250) not null,
  display_order      int           not null,
  read_level         char(1)       null,
  write_level        char(1)       null,
  speak_level        char(1)       null,
  CONSTRAINT PK_LANGUAGE_SKILLS_ID primary key (language_skills_id),
  CONSTRAINT FK_LANGUAGE_SKILLS_UID foreign key (user_id) references users (userid)
)
GO

CREATE TABLE task_reminder_log (
  transcript_id nvarchar(15) not null,
  approver_id   nvarchar(85) not null,
  sent_date     datetime     not null,
  CONSTRAINT FK_TASK_REMINDER_LOG_TID foreign key (transcript_id) references transcript (transcriptid)
    ON DELETE CASCADE,
  CONSTRAINT FK_TASK_REMINDER_LOG_UID foreign key (approver_id) references users (userid)
    ON DELETE CASCADE
)
GO

insert into code_itemLanguage (ilangid, description) values (88, 'Multiple Languages')
GO

create table rule_set (
  rule_id          nvarchar(15)  not null,
  rule_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,
  constraint PK_RULE_SET primary key (rule_id),
  constraint AK_RULE_SET_NAME_ unique (rule_name)
)
GO

create table rule_set_membership (
  rule_id nvarchar(15) not null,
  userid  nvarchar(85) not null,
  constraint PK_RULE_SET_MEMBERSHIP primary key (rule_id, userid),
  constraint FK_RULE_SET_MEMBER_ID foreign key (rule_id) references rule_set (rule_id)
)
GO

create table rule_set_membership_rule (
  rule_id       nvarchar(15) not null,
  identity_id   nvarchar(85) not null,
  identity_type nvarchar(45) not null,
  constraint PK_RULE_SET_MEMBERSHIP_RULE primary key (rule_id, identity_id, identity_type),
  constraint FK_RULE_SET_MEMBER_RULE_ID foreign key (rule_id) references rule_set (rule_id)
)
GO

create table terms_of_use (
  id           NVARCHAR(85)  NOT NULL,
  created_date DATETIME      NOT NULL,
  title        NVARCHAR(255) NOT NULL,
  accept_label NTEXT         NOT NULL,
  text         NTEXT         NOT NULL,
  publish      CHAR(1)       NOT NULL,
  constraint PK_TERMS_OF_USE primary key (id)
)
GO

create table user_terms_of_use (
  user_id       NVARCHAR(85) NOT NULL,
  accepted_date DATETIME     NOT NULL,
  terms_id      NVARCHAR(85) NOT NULL,
  constraint PK_USER_TERMS_OF_USE primary key (user_id, terms_id),
  constraint FK_TERMS_OF_USE_TERMS_ID foreign key (terms_id) references terms_of_use (id),
  constraint FK_TERMS_OF_USE_USER_ID foreign key (user_id) references users (userid)
)
GO

create table netassessor_launcher (
  learningid          nvarchar(85)  not null,
  url                 nvarchar(85)  null,
  parameters          nvarchar(255) null,
  preshared_key       nvarchar(85)  null,
  language            nvarchar(85)  null,
  timezone            nvarchar(85)  null,
  last_updated        datetime      null,
  updater_legal_name  nvarchar(250) null,
  update_meaning_text ntext         null,
  last_updater_uid    nvarchar(85)  null,
  constraint PK_NETASSESSOR_LAUNCHER primary key (learningid)
)
GO

create table AICC_Core_Trans_Attempts (
  transcript_id nvarchar(15) not null,
  session_id    nvarchar(15) not null,
  constraint pk_aicc_attempt primary key (transcript_id, session_id)
)
GO

create table AICC_Core_Trans_Node_Attempts (
  session_id     nvarchar(15) not null,
  sco_session_id nvarchar(15) not null,
  node_id        varchar(255) not null,
  constraint pk_aicc_tnode_attempt primary key (session_id, sco_session_id)
)
GO

create table user_group_mem_otherdetail (
  group_id       nvarchar(15) not null,
  employeeno     nvarchar(85) null,
  jobtitle       nvarchar(85) null,
  departmentname nvarchar(85) null,
  departmentid   nvarchar(85) null,
  locationcode   nvarchar(85) null,
  costcenter     nvarchar(85) null,
  costcentername nvarchar(85) null,
  languagepref   int          null,
  joindatestart  datetime     null,
  joindateend    datetime     null,
  dappraiser     NVARCHAR(85) null,
  hrmanagername  nvarchar(85) null,
  managername    nvarchar(85) null,
  constraint PK_USER_GROUP_MEM_O_RULE primary key (group_id),
  constraint FK_USER_GROUP_MEM_O_RULE foreign key (group_id) references user_group (group_id)
)
GO

create table user_group_mem_jobprofile_rule (
  group_id      nvarchar(15) not null,
  jobprofile_id nvarchar(15) not null,
  constraint PK_USER_GROUP_MEM_J_RULE primary key (group_id, jobprofile_id),
  constraint FK_GROUPID_RULE foreign key (group_id) references user_group (group_id),
  constraint FK_JOBPROFILE_RULE foreign key (jobprofile_id) references job_profile (id)
)
GO

create table user_group_mem_competency_rule (
  group_id                 nvarchar(15) not null,
  competency_id            nvarchar(15) not null,
  from_competency_level_id nvarchar(15) not null,
  to_competency_level_id   nvarchar(15) not null,
  constraint PK_USER_GROUP_MEM_C_RULE primary key (group_id, competency_id),
  constraint FK_GROUPID_C_RULE foreign key (group_id) references user_group (group_id),
  constraint FK_COMPETENCY_RULE foreign key (competency_id) references individual_competency (id),
  constraint FK_F_COMPETENCY_LEVEL_RULE foreign key (from_competency_level_id) references competency_level (id),
  constraint FK_T_COMPETENCY_LEVEL_RULE foreign key (to_competency_level_id) references competency_level (id)
)
GO

create table user_group_mem_userattrex_rule (
  group_id        nvarchar(15) not null,
  extension_no    int          not null,
  extension_value nvarchar(85) not null,
  constraint PK_USER_GROUP_MEM_U_RULE primary key (group_id, extension_no),
  constraint FK_GROUPID_U_RULE foreign key (group_id) references user_group (group_id),
  constraint FK_USER_EXTENSION_LABEL_RULE foreign key (extension_no) references user_extension_label (extension_no)
)
GO

create table logical_domain_config (
  domain_id    nvarchar(15)  not null,
  config_key   nvarchar(255) not null,
  config_value ntext         null,
  constraint PK_LOGICAL_DOMAIN_CONFIG primary key (domain_id, config_key),
  constraint FK_LOGICAL_DOMAIN_CONFIG foreign key (domain_id) references logical_domain (domain_id)
)
GO

create table autoenroll_trace (
  id         nvarchar(85) not null,
  userid     nvarchar(85) not null,
  learningid nvarchar(85) null,
  sessionid  nvarchar(85) null,
  event_date datetime     not null,
  message    ntext        not null,
  constraint PK_AUTOENROLL_TRACE primary key (id),
  constraint FK_AUTOENROLL_USER_ID foreign key (userid) references users (userid)
)
GO

create table learning_recommendation (
  learningid      nvarchar(85) not null,
  next_order      int          not null,
  next_learningid nvarchar(85) not null,
  is_auto_generated char(1) default 'N' not null,
  constraint PK_RECOMMENDED primary key (learningid, next_order)
)
GO

CREATE TABLE e_signature_context (
  spid                int           not null,
  userid              nvarchar(85)  not null,
  update_meaning_text nvarchar(500) not null,
  updater_legal_name  nvarchar(250) not null,
  constraint PK_E_SIGN_CONTEXT primary key (spid)
)
GO

create index NUK_TESTSTART on testinstance (teststart)
GO

create index NUK_TI_QUESTIONID on testquestion (questionid)
GO

CREATE TABLE vc_gtt_application (
  app_id       nvarchar(85)   not null,
  display_name nvarchar(1000) not null,
  api_key      nvarchar(255)  not null,
  constraint PK_VC_GTT_APPLICATION primary key (app_id)
)
GO

CREATE TABLE vc_gtt_organizer (
  user_id       nvarchar(85)  not null,
  gtt_app_id    nvarchar(85)  not null,
  account_key   nvarchar(255) not null,
  organizer_key nvarchar(255) not null,
  access_token  nvarchar(255) not null,
  constraint PK_VC_GTT_ORGANIZER primary key (user_id),
  constraint FK_GTT_ORGANIZER_APPLICATION foreign key (gtt_app_id) references vc_gtt_application (app_id)
)
GO

create index NUK_GTT_ORGANIZER on vc_gtt_organizer (organizer_key)
GO

CREATE TABLE vc_launcher_gtt (
  course_id           nvarchar(85)  not null,
  gtt_app_id          nvarchar(85)  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_VC_L_GTT primary key (course_id),
  constraint FK_VC_L_GTT_LEARNING_OBJECT foreign key (course_id) references learningObject (learningid),
  constraint FK_VC_L_GTT_APPLICATION foreign key (gtt_app_id) references vc_gtt_application (app_id)
)
GO

CREATE TABLE vc_launcher_gtt_training (
  eventid                  nvarchar(15)        not null,
  user_id                  nvarchar(85)        not null,
  course_id                nvarchar(85)        not null,
  training_key             nvarchar(85)        null,
  is_updated               char(1)             null,
  is_organizer_updated     char(1) default 'N' not null,
  is_training_history_sync char(1) default 'N' not null,
  last_sync_date           datetime            null,
  overall_status           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_VC_LAUNCHER_GTT_TRAINING primary key (eventid),
  constraint FK_GTT_TRAINING_LEARNING_OBJ foreign key (course_id) references learningObject (learningid),
  constraint FK_GTT_TRAINING_CSCHEDULE foreign key (eventid) references cschedule (eventid)
)
GO

CREATE TABLE transcript_gtt (
  transcript_id  nvarchar(15)   not null,
  join_url       nvarchar(1024) not null,
  registrant_key nvarchar(255)  null,
  constraint PK_TRANSCRIPT_GTT primary key (transcript_id),
  constraint FK_TRANSCRIPT_GTT_TRANSCRIPT foreign key (transcript_id) references transcript (transcriptid)
)
GO

CREATE TABLE additional_attempts (
  id            nvarchar(15)   not null,
  transcript_id nvarchar(15)   not null,
  num_attempts  int            not null default 0,
  grantor_id    nvarchar(85)   not null,
  reason        nvarchar(1000) not null,
  grant_date    datetime       not null,
  constraint PK_ADDITIONAL_ATTEMPTS primary key (id),
  constraint TRANSCRIPT_FK foreign key (transcript_id) references transcript (transcriptid)
    on delete cascade
)
GO

CREATE TABLE autoassign_proficiency_range (
  assign_object_id         NVARCHAR(15) NOT NULL,
  learning_id              NVARCHAR(85) NOT NULL,
  competency_id            NVARCHAR(15) NOT NULL,
  from_competency_level_id NVARCHAR(15) NOT NULL,
  to_competency_level_id   NVARCHAR(15) NOT NULL,
  autoassign_source        CHAR(1)      NOT NULL,
  CONSTRAINT PK_AUTOASSIGN_PROF_RANGE PRIMARY KEY (assign_object_id, learning_id, competency_id, autoassign_source),
  CONSTRAINT FK_AUTOASSIGN_COMPETENCY FOREIGN KEY (competency_id) REFERENCES individual_competency (id)
    ON DELETE CASCADE,
  CONSTRAINT FK_AUTOASSIGN_F_COMP_LEVEL FOREIGN KEY (from_competency_level_id) REFERENCES competency_level (id),
  CONSTRAINT FK_AUTOASSIGN_T_COMP_LEVEL FOREIGN KEY (to_competency_level_id) REFERENCES competency_level (id)
)
GO

CREATE TABLE autoassign_user_attr_ex (
  assign_object_id  NVARCHAR(15) NOT NULL,
  learning_id       NVARCHAR(85) NOT NULL,
  extension_no      INT          NOT NULL,
  extension_value   NVARCHAR(85) NOT NULL,
  autoassign_source CHAR(1)      NOT NULL,
  CONSTRAINT PK_AUTOASSIGN_USER_ATTR_EX PRIMARY KEY (assign_object_id, learning_id, extension_no, autoassign_source),
  CONSTRAINT FK_AUTOASSIGN_USER_EXTENSION FOREIGN KEY (extension_no) REFERENCES user_extension_label (extension_no)
)
GO

CREATE TABLE autoassign_competency_operator (
  assign_object_id  NVARCHAR(15)              NOT NULL,
  learning_id       NVARCHAR(85)              NOT NULL,
  operator          NVARCHAR(3) DEFAULT 'AND' NOT NULL,
  autoassign_source CHAR(1)                   NOT NULL,
  CONSTRAINT PK_AUTOASSIGN_COMP_OP PRIMARY KEY (assign_object_id, learning_id, autoassign_source)
)
GO

CREATE TABLE course_competency_exp (
  course_id     NVARCHAR(85) NOT NULL,
  competency_id NVARCHAR(15) NOT NULL,
  user_id       NVARCHAR(85) NOT NULL,
  expiry_date   DATETIME     NOT NULL,
  CONSTRAINT PK_COURSE_COMPETENCY_EXP primary key (course_id, competency_id, user_id),
  CONSTRAINT FK_COMP_EXP_LO FOREIGN KEY (course_id) REFERENCES learningObject (learningid),
  CONSTRAINT FK_COMP_EXP_COMP FOREIGN KEY (competency_id) REFERENCES individual_competency (id),
  CONSTRAINT FK_COMP_EXP_USER FOREIGN KEY (user_id) REFERENCES users (userid)
)
GO

CREATE TABLE vc_launcher_gtt_organizer (
  user_id             nvarchar(85)  NOT NULL,
  eventid             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_VC_LAUNCHER_GTT_ORGANIZER PRIMARY KEY (user_id, eventid),
  CONSTRAINT FK_GTT_ORGANIZER_USERID FOREIGN KEY (user_id) REFERENCES users (userid),
  CONSTRAINT FK_GTT_ORGANIZER_CSCHEDULE FOREIGN KEY (eventid) REFERENCES cschedule (eventid)
)
GO

CREATE TABLE analytics_profile (
  id          	  NVARCHAR(85)        NOT NULL,
  org_code    	  NVARCHAR(85)        NOT NULL,
  profile_name    NVARCHAR(85)        NULL,
  description 	  NVARCHAR(255)       NULL,
  is_enable   	  CHAR(1) DEFAULT 'Y' NOT NULL,
  sso_param   	  NVARCHAR(1000)      NULL,
  prop_1      	  NVARCHAR(1000)      NULL,
  prop_2      	  NVARCHAR(1000)      NULL,
  prop_3      	  NVARCHAR(1000)      NULL,
  CONSTRAINT PK_ANALYTICS_PROFILE PRIMARY KEY (id)
)
GO

CREATE TABLE analytics_role (
  id          	  NVARCHAR(85)   NOT NULL,
  profile_id      NVARCHAR(85)   NOT NULL,
  role_name   	  NVARCHAR(85)   NOT NULL,
  description 	  NVARCHAR(255)  NULL,
  prop_1      	  NVARCHAR(1000) NULL,
  prop_2      	  NVARCHAR(1000) NULL,
  prop_3      	  NVARCHAR(1000) NULL,
  CONSTRAINT PK_ANALYTICS_ROLE PRIMARY KEY (id)
)
GO

CREATE TABLE analytics_profile_map (
  id             NVARCHAR(85)        NOT NULL,
  profile_id         NVARCHAR(85)        NULL,
  nts_org_id     NVARCHAR(15)        NOT NULL,
  inherit_parent CHAR(1) DEFAULT 'N' NOT NULL,
  prop_1         NVARCHAR(1000)      NULL,
  prop_2         NVARCHAR(1000)      NULL,
  prop_3         NVARCHAR(1000)      NULL,
  CONSTRAINT PK_ANALYTICS_PROFILE_M PRIMARY KEY (id)
)
GO

CREATE TABLE analytics_role_map (
  id          NVARCHAR(85)   NOT NULL,
  org_map_id  NVARCHAR(85)   NOT NULL,
  role_id     NVARCHAR(85)   NULL,
  nts_role_id NVARCHAR(85)   NOT NULL,
  prop_1      NVARCHAR(1000) NULL,
  prop_2      NVARCHAR(1000) NULL,
  prop_3      NVARCHAR(1000) NULL,
  CONSTRAINT PK_ANALYTICS_ROLE_M PRIMARY KEY (id)
)
GO

CREATE TABLE analytics_user (
  id          NVARCHAR(85)   NOT NULL,
  nts_user_id NVARCHAR(85)   NOT NULL,
  org_id      NVARCHAR(85)   NOT NULL,
  role_id     NVARCHAR(85)   NULL,
  sso_param   NVARCHAR(1000) NULL,
  prop_1      NVARCHAR(1000) NULL,
  prop_2      NVARCHAR(1000) NULL,
  prop_3      NVARCHAR(1000) NULL,
  CONSTRAINT PK_ANALYTICS_USER PRIMARY KEY (id)
)
GO

create table ekp_audit_version (
  dblevel int not null
)
GO

INSERT INTO permissions_operator VALUES ('*TMS*', 'PLS', 1, 'OR')
GO

INSERT INTO permissions_operator VALUES ('*TMS*', 'PLS', 2, 'OR')
GO

INSERT INTO permissions VALUES ('*TMS*', 'PLS', '*ROOT*', 1, 'O')
GO

INSERT INTO permissions VALUES ('*TMS*', 'PLS', '*ROOT*', 2, 'O')
GO

CREATE VIEW user_appraisal_vw AS
	select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.currentPID as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
		   sda.currentPID as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
		   ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
		   aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
		   aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
		   atp.start_date as template_start_date, atp.end_date as template_end_date
	from user_appraisal ua
	inner join appraisal_template atp on atp.id = ua.appraisal_template_id
	left join valid_users_vw da on da.userid = ua.appraiser_id
	left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
	inner join appraisal_status ast on ast.id = ua.status_id
	left join appraisal_status_category ac on ac.id = ast.category_id
	left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

CREATE VIEW user_appraisal_competency_vw AS
  SELECT u.appraisal_id, u.competency_id, s.title_key, s.section_code, s.description_key, appraisee_r.rating as appraisee_rating,
		 appraiser_r.rating as appraiser_rating, agreed_r.rating as agreed_rating, u.rating_na,
		 u.carry_over, u.appraisee_comments, u.appraiser_comments,
		 appraisee_rl.level_order as appraisee_comp_level, appraiser_rl.level_order as appraiser_comp_level,
		 agreed_rl.level_order as agreed_comp_level
  FROM appraisal_template_section s
  INNER JOIN appraisal_u_competency u ON u.section_id = s.id
  LEFT JOIN appraisal_rating appraisee_r ON appraisee_r.id = u.appraisee_rating
  LEFT JOIN appraisal_rating appraiser_r ON appraiser_r.id = u.appraiser_rating
  LEFT JOIN appraisal_rating agreed_r ON agreed_r.id = u.agreed_rating
  LEFT JOIN competency_level appraisee_l ON appraisee_l.id = u.appraisee_comp_level
  LEFT JOIN tms_responsibility_level appraisee_rl ON appraisee_rl.id = appraisee_l.res_id
  LEFT JOIN competency_level appraiser_l ON appraiser_l.id = u.appraiser_comp_level
  LEFT JOIN tms_responsibility_level appraiser_rl ON appraiser_rl.id = appraiser_l.res_id
  LEFT JOIN competency_level agreed_l ON agreed_l.id = u.agreed_comp_level
  LEFT JOIN tms_responsibility_level agreed_rl ON agreed_rl.id = agreed_l.res_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('InitialMssql93', 'shravan', 'nschema/mssql.93.changeLog.xml', GETDATE(), 2, '8:500d9750ff50601c2143a12582567105', 'sql', '', 'EXECUTED', '93', NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/oracle.93.changeLog.xml::InitialOracle93::shravan
INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('InitialOracle93', 'shravan', 'nschema/oracle.93.changeLog.xml', GETDATE(), 3, '8:d2d44e2b80bd8f6ec01b748f04f4885e', 'sql', '', 'MARK_RAN', '93', NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/derby.93.changeLog.xml::InitialDerby93::shravan
INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('InitialDerby93', 'shravan', 'nschema/derby.93.changeLog.xml', GETDATE(), 4, '8:8e24bbeb01f41fd860c1f59e84448049', 'sql', '', 'MARK_RAN', '93', NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::23Oct2013-1::shravan
UPDATE ekpversion SET dblevel = 100, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('23Oct2013-1', 'shravan', 'nschema/100.changeLog.xml', GETDATE(), 5, '8:4fcdef3998603abcc5c59a7cbfe445a6', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::4nov2013-gl::garbo.lim
ALTER TABLE courseDownloads ALTER COLUMN fileURL nvarchar(1000)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('4nov2013-gl', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 6, '8:cd0ae873ff1bf927a77eb346b9d7b736', 'modifyDataType columnName=fileURL, tableName=courseDownloads', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::8nov-rc-1::ryan.chan
ALTER TABLE mastertest ADD allowDownload char(1) CONSTRAINT DF_mastertest_allowDownload DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8nov-rc-1', 'ryan.chan', 'nschema/100.changeLog.xml', GETDATE(), 7, '8:73eee3617f4c172b2bdddb19f5ee2b57', 'addColumn tableName=mastertest', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::8nov-rc-2::ryan.chan
ALTER TABLE goal_personal ADD send_reminder_to_owner char(1) CONSTRAINT DF_goal_personal_send_reminder_to_owner DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8nov-rc-2', 'ryan.chan', 'nschema/100.changeLog.xml', GETDATE(), 8, '8:9ee5b55f576988a451dceee3412dc176', 'addColumn tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::8nov-rc-3::ryan.chan
ALTER TABLE goal_personal ADD send_reminder_to_da char(1) CONSTRAINT DF_goal_personal_send_reminder_to_da DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8nov-rc-3', 'ryan.chan', 'nschema/100.changeLog.xml', GETDATE(), 9, '8:53f03ca75d903c21cea2d3338be98f5e', 'addColumn tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::8nov-rc-4::ryan.chan
ALTER TABLE goal_personal ADD frequency_for_owner char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8nov-rc-4', 'ryan.chan', 'nschema/100.changeLog.xml', GETDATE(), 10, '8:a3540ad7ea2a799108ed3130f2a53d4c', 'addColumn tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::8nov-rc-5::ryan.chan
ALTER TABLE goal_personal ADD nunit_for_owner int
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8nov-rc-5', 'ryan.chan', 'nschema/100.changeLog.xml', GETDATE(), 11, '8:0d68f84eaec82d67ceac72af1e65c3e7', 'addColumn tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::8nov-rc-6::ryan.chan
ALTER TABLE goal_personal ADD frequency_for_da char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8nov-rc-6', 'ryan.chan', 'nschema/100.changeLog.xml', GETDATE(), 12, '8:b2faf83b650fbca573a274f28505ed72', 'addColumn tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::8nov-rc-7::ryan.chan
ALTER TABLE goal_personal ADD nunit_for_da int
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8nov-rc-7', 'ryan.chan', 'nschema/100.changeLog.xml', GETDATE(), 13, '8:8208f215f18d2cc4a658e061e8a6b293', 'addColumn tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::15nov-rc-1::ryan.chen
CREATE TABLE goal_reminder_tracking (goal_id nvarchar(85) NOT NULL, reminder_type char(1) NOT NULL, last_updated datetime)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('15nov-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 14, '8:640772a1f9a7150dba5d9bc49cee47fe', 'createTable tableName=goal_reminder_tracking', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::19nov-rc-1::ryan.chen
ALTER TABLE goal_reminder_tracking ADD CONSTRAINT PK_GOAL_REMINDER_TRACKING PRIMARY KEY (goal_id, reminder_type)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('19nov-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 15, '8:0694e8bd574054813aacc91a5ff19cf9', 'addPrimaryKey constraintName=PK_GOAL_REMINDER_TRACKING, tableName=goal_reminder_tracking', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20nov-rc-1::ryan.chen
ALTER TABLE goal_reminder_tracking ADD CONSTRAINT FK_GOAL_REMINDER_TRACKING FOREIGN KEY (goal_id) REFERENCES goal (id) ON DELETE CASCADE
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20nov-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 16, '8:bc2525bf6a0d8329cb447e5219a574d6', 'addForeignKeyConstraint baseTableName=goal_reminder_tracking, constraintName=FK_GOAL_REMINDER_TRACKING, referencedTableName=goal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::21nov-gl-1::garbo.lim
CREATE TABLE course_comp_exp_temp (course_id nvarchar(85) NOT NULL, competency_id nvarchar(15) NOT NULL, user_id nvarchar(85) NOT NULL, expiry_date datetime NOT NULL, transcript_id nvarchar(15) NOT NULL)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('21nov-gl-1', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 17, '8:c800ad30129e5be5cdd78f90160403d5', 'createTable tableName=course_comp_exp_temp', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::21nov-gl-2::garbo.lim
INSERT INTO course_comp_exp_temp (course_id, competency_id, user_id, expiry_date, transcript_id)
				SELECT 	c.course_id,
				       	c.competency_id,
				    	c.user_id,
				    	c.expiry_date,
				    	t.transcriptid
				FROM course_competency_exp c, transcript t
  				WHERE c.course_id = t.learningid AND c.user_id = t.userid AND t.studentstatus IN (4, 13, 14, 22)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('21nov-gl-2', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 18, '8:25986b8cf0855ca5fabdc136f9f2edd6', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::21nov-gl-3::garbo.lim
truncate table course_competency_exp
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('21nov-gl-3', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 19, '8:7a5d42b86fb8617b759e8e5222241244', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::21nov-gl-4::garbo.lim
ALTER TABLE course_competency_exp ADD transcript_id nvarchar(15) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('21nov-gl-4', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 20, '8:38e99244972635b72091d4b160c0d493', 'addColumn tableName=course_competency_exp', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::21nov-gl-5::garbo.lim
ALTER TABLE course_competency_exp DROP CONSTRAINT FK_COMP_EXP_LO
GO

ALTER TABLE course_competency_exp DROP CONSTRAINT PK_COURSE_COMPETENCY_EXP
GO

ALTER TABLE course_competency_exp ADD CONSTRAINT PK_COURSE_COMPETENCY_EXP PRIMARY KEY (transcript_id, competency_id, user_id)
GO

ALTER TABLE course_competency_exp ADD CONSTRAINT FK_COMP_EXP_LO FOREIGN KEY (course_id) REFERENCES learningObject (learningid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('21nov-gl-5', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 21, '8:f03acbf1444d1d67a8e256e4944cd70a', 'dropForeignKeyConstraint baseTableName=course_competency_exp, constraintName=FK_COMP_EXP_LO; dropPrimaryKey constraintName=PK_COURSE_COMPETENCY_EXP, tableName=course_competency_exp; addPrimaryKey constraintName=PK_COURSE_COMPETENCY_EXP, tableName=...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20131126-rl-1::robert.lowe
CREATE TABLE oauth_temporary_credentials (id nvarchar(85) NOT NULL, secret nvarchar(85) NOT NULL, callback nvarchar(255) NOT NULL, created datetime NOT NULL)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20131126-rl-1', 'robert.lowe', 'nschema/100.changeLog.xml', GETDATE(), 22, '8:b126c78931357ddb84983a823c52fa86', 'createTable tableName=oauth_temporary_credentials', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20131201-gl-1::garbo.lim
ALTER TABLE course_competency_exp DROP CONSTRAINT FK_COMP_EXP_LO
GO

ALTER TABLE course_competency_exp DROP CONSTRAINT FK_COMP_EXP_COMP
GO

ALTER TABLE course_competency_exp DROP CONSTRAINT FK_COMP_EXP_USER
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20131201-gl-1', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 23, '8:972a357c177367636c736233de5f4983', 'dropForeignKeyConstraint baseTableName=course_competency_exp, constraintName=FK_COMP_EXP_LO; dropForeignKeyConstraint baseTableName=course_competency_exp, constraintName=FK_COMP_EXP_COMP; dropForeignKeyConstraint baseTableName=course_competency_ex...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20131216-rl-1::robert.lowe
ALTER TABLE oauth_temporary_credentials ADD CONSTRAINT PK_OAUTH_TEMPORARY_CREDENTIALS PRIMARY KEY (id)
GO

CREATE TABLE oauth_verifier (verifier nvarchar(85) NOT NULL, temporary_credentials_id nvarchar(85) NOT NULL, user_id nvarchar(85) NOT NULL, created datetime NOT NULL)
GO

ALTER TABLE oauth_verifier ADD CONSTRAINT PK_OAUTH_VERIFIER PRIMARY KEY (verifier)
GO

ALTER TABLE oauth_verifier ADD CONSTRAINT FK_OAUTH_VERIFIER_TEMP_TOKEN FOREIGN KEY (temporary_credentials_id) REFERENCES oauth_temporary_credentials (id)
GO

ALTER TABLE oauth_verifier ADD CONSTRAINT FK_OAUTH_VERIFIER_USER FOREIGN KEY (user_id) REFERENCES users (userid)
GO

CREATE TABLE oauth_token_credentials (id nvarchar(85) NOT NULL, secret nvarchar(85) NOT NULL, verifier nvarchar(85) NOT NULL, created datetime NOT NULL, revoked datetime)
GO

ALTER TABLE oauth_token_credentials ADD CONSTRAINT PK_OAUTH_TOKEN_CREDENTIALS PRIMARY KEY (id)
GO

ALTER TABLE oauth_token_credentials ADD CONSTRAINT FK_OAUTH_TOKEN_VERIFIER FOREIGN KEY (verifier) REFERENCES oauth_verifier (verifier)
GO

DROP TABLE access_token
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20131216-rl-1', 'robert.lowe', 'nschema/100.changeLog.xml', GETDATE(), 24, '8:fc88987c0bafd6c2575466697856db3a', 'addPrimaryKey constraintName=PK_OAUTH_TEMPORARY_CREDENTIALS, tableName=oauth_temporary_credentials; createTable tableName=oauth_verifier; addPrimaryKey constraintName=PK_OAUTH_VERIFIER, tableName=oauth_verifier; addForeignKeyConstraint baseTableNa...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20131218-kc-1::kennex.chan
CREATE TABLE goal_development (id nvarchar(85) NOT NULL, start_date datetime NOT NULL, target_date datetime NOT NULL, weight int NOT NULL, measurement nvarchar (max), created_by nvarchar(85) NOT NULL, created_date datetime NOT NULL, last_modified_by nvarchar(85) NOT NULL, last_modified_date datetime NOT NULL, date_closed datetime, send_reminder_to_owner char(1) CONSTRAINT DF_goal_development_send_reminder_to_owner DEFAULT 'N' NOT NULL, send_reminder_to_da char(1) CONSTRAINT DF_goal_development_send_reminder_to_da DEFAULT 'N' NOT NULL, frequency_for_owner char(1), nunit_for_owner int, frequency_for_da char(1), nunit_for_da int)
GO

ALTER TABLE goal_development ADD CONSTRAINT PK_GOAL_DEV PRIMARY KEY (id)
GO

ALTER TABLE goal_development ADD CONSTRAINT FK_GOAL_DEV FOREIGN KEY (id) REFERENCES goal (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20131218-kc-1', 'kennex.chan', 'nschema/100.changeLog.xml', GETDATE(), 25, '8:d0b7d3b3d5df1933ef9576f0f5b329b1', 'createTable tableName=goal_development; addPrimaryKey constraintName=PK_GOAL_DEV, tableName=goal_development; addForeignKeyConstraint baseTableName=goal_development, constraintName=FK_GOAL_DEV, referencedTableName=goal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20131227-mc-1::mike.chung
exec sp_rename 'screenLayout.dynamicMenu', 'menu_position'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20131227-mc-1', 'mike.chung', 'nschema/100.changeLog.xml', GETDATE(), 26, '8:1db4f84b61edb503a913eb8b2a7aa6dc', 'renameColumn newColumnName=menu_position, oldColumnName=dynamicMenu, tableName=screenLayout', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20131230-mc-1::mike.chung
CREATE TABLE template_widget_html_config (widget_id nvarchar(15) NOT NULL, title nvarchar(255), is_show_title char(1) CONSTRAINT DF_template_widget_html_config_is_show_title DEFAULT 'Y' NOT NULL, content nvarchar (max))
GO

ALTER TABLE template_widget_html_config ADD CONSTRAINT PK_WIDGET_HTML_CONFIG PRIMARY KEY (widget_id)
GO

ALTER TABLE template_widget_html_config ADD CONSTRAINT FK_WIDGET_HTML_CONFIG FOREIGN KEY (widget_id) REFERENCES template_widget (id)
GO

INSERT INTO template_widget_html_config (widget_id, title, is_show_title, content)
                SELECT w.id, t.config_value, 'Y', c.config_value
                FROM template_widget w
                INNER JOIN logical_domain_config t ON w.widget_id = 'HTML' AND t.config_key = 'home.htmlWidgetTitle'
                INNER JOIN logical_domain_config c ON c.config_key = 'home.htmlWidgetContent'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20131230-mc-1', 'mike.chung', 'nschema/100.changeLog.xml', GETDATE(), 27, '8:185a1266a6ae715cb89dd6c3c923fcfd', 'createTable tableName=template_widget_html_config; addPrimaryKey constraintName=PK_WIDGET_HTML_CONFIG, tableName=template_widget_html_config; addForeignKeyConstraint baseTableName=template_widget_html_config, constraintName=FK_WIDGET_HTML_CONFIG, ...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140102-mc-1::mike.chung
ALTER TABLE template_widget_html_config DROP CONSTRAINT FK_WIDGET_HTML_CONFIG
GO

ALTER TABLE template_widget_html_config ADD CONSTRAINT FK_WIDGET_HTML_CONFIG FOREIGN KEY (widget_id) REFERENCES template_widget (id) ON DELETE CASCADE
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140102-mc-1', 'mike.chung', 'nschema/100.changeLog.xml', GETDATE(), 28, '8:ad24a152f4e25989e24eda50a71590b8', 'dropForeignKeyConstraint baseTableName=template_widget_html_config, constraintName=FK_WIDGET_HTML_CONFIG; addForeignKeyConstraint baseTableName=template_widget_html_config, constraintName=FK_WIDGET_HTML_CONFIG, referencedTableName=template_widget', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140106-sg-1::shravan.gurrala
ALTER TABLE courseDownloads ALTER COLUMN fileURL nvarchar(1000) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140106-sg-1', 'shravan.gurrala', 'nschema/100.changeLog.xml', GETDATE(), 29, '8:d8c6483d728d17ff8aa4e1012f438c1a', 'addNotNullConstraint columnName=fileURL, tableName=courseDownloads', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140106-js-1::janice.sung
ALTER TABLE goal_development ADD learningid nvarchar(85)
GO

ALTER TABLE goal_development ADD CONSTRAINT FK_GOAL_DEVELOP_LEARNINGID FOREIGN KEY (learningid) REFERENCES learningObject (learningid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140106-js-1', 'janice.sung', 'nschema/100.changeLog.xml', GETDATE(), 30, '8:445fbfdd4f5bcfb17cecc2cd9ded19ac', 'addColumn tableName=goal_development; addForeignKeyConstraint baseTableName=goal_development, constraintName=FK_GOAL_DEVELOP_LEARNINGID, referencedTableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140108-rc-1::ryan.chen
ALTER TABLE olsa_account ADD sync_catalog_type char(1) CONSTRAINT DF_olsa_account_sync_catalog_type DEFAULT 'T' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140108-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 31, '8:f009422799aa317a2bf22bb8bbb2bb8c', 'addColumn tableName=olsa_account', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140108-rc-2::ryan.chen
ALTER TABLE olsa_account ADD use_exist_assignment char(1) CONSTRAINT DF_olsa_account_use_exist_assignment DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140108-rc-2', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 32, '8:b0d0d847787b990821ba8ed03a3106ae', 'addColumn tableName=olsa_account', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140108-jw-1::james.wong
ALTER TABLE appraisal_s_achievement_rec ADD display_na char(1) CONSTRAINT DF_appraisal_s_achievement_rec_display_na DEFAULT 'Y' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140108-jw-1', 'james.wong', 'nschema/100.changeLog.xml', GETDATE(), 33, '8:da5d81f6f158c73a43ad8db2c58ac2b1', 'addColumn tableName=appraisal_s_achievement_rec', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140108-jw-2::james.wong
ALTER TABLE appraisal_s_dimension ADD display_na char(1) CONSTRAINT DF_appraisal_s_dimension_display_na DEFAULT 'Y' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140108-jw-2', 'james.wong', 'nschema/100.changeLog.xml', GETDATE(), 34, '8:6b9455ab42d94ec23b959981dd53d86a', 'addColumn tableName=appraisal_s_dimension', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140114-kc-1::kennex.chan
ALTER TABLE appraisal_status_category ALTER COLUMN code nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140114-kc-1', 'kennex.chan', 'nschema/100.changeLog.xml', GETDATE(), 35, '8:5181738799328b1c0ca188761b95cea5', 'modifyDataType columnName=code, tableName=appraisal_status_category', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140114-kc-2::kennex.chan
ALTER TABLE appraisal_status ALTER COLUMN code nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140114-kc-2', 'kennex.chan', 'nschema/100.changeLog.xml', GETDATE(), 36, '8:5686deedb1b5709181e66242d1684262', 'modifyDataType columnName=code, tableName=appraisal_status', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140114-mc-1::mike.chung
insert into permissions_operator (objectid, objecttype, permissioncode, operator) select token_account_id, 'OTA', 1, 'OR' from token_account where account_type='O'
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) select token_account_id, 'OTA', 2, 'OR' from token_account where account_type='O'
GO

insert into permissions (objectid, objecttype, identityid, permissioncode, identitytype) select token_account_id, 'OTA', '*ROOT*', 1, 'O' from token_account where account_type='O'
GO

insert into permissions (objectid, objecttype, identityid, permissioncode, identitytype) select token_account_id, 'OTA', '*ROOT*', 2, 'O' from token_account where account_type='O'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140114-mc-1', 'mike.chung', 'nschema/100.changeLog.xml', GETDATE(), 37, '8:2aff74da5ccb0b15b545fcbbb2b7dffb', 'sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140116-gl-1::garbo.lim
ALTER TABLE code_orgstructure ADD limit_instructor_option char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140116-gl-1', 'garbo.lim', 'nschema/100.changeLog.xml', GETDATE(), 38, '8:02621cb9cb6fa0dfda37878afd4d2f94', 'addColumn tableName=code_orgstructure', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140116-jw-3::james.wong
update appraisal_s_achievement_rec 
			 set display_na = 'N'
			 where display_appraisee_rating = 'N' and display_appraiser_rating = 'N' and display_agreed_rating = 'N'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140116-jw-3', 'james.wong', 'nschema/100.changeLog.xml', GETDATE(), 39, '8:09105c052304c1da532054e3296da4f3', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140117-rc-1::ryan.chen
ALTER TABLE schedule_task ADD notification_email_id nvarchar(15)
GO

ALTER TABLE schedule_task ADD CONSTRAINT FK_SCHEDULE_TASK_EMAIL_ID FOREIGN KEY (notification_email_id) REFERENCES email_profile (email_profile_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140117-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 40, '8:a34712899e15dd43321bab377d767a2a', 'addColumn tableName=schedule_task; addForeignKeyConstraint baseTableName=schedule_task, constraintName=FK_SCHEDULE_TASK_EMAIL_ID, referencedTableName=email_profile', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140118-rr-1::ray.ruff
ALTER TABLE userprefs ADD welcome_email_senddate datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140118-rr-1', 'ray.ruff', 'nschema/100.changeLog.xml', GETDATE(), 41, '8:ff8fb0c38ee328f6e7a470ff9ec1197f', 'addColumn tableName=userprefs', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140118-rr-2::ray.ruff
ALTER TABLE code_orgstructure ADD ext_approval_email_id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140118-rr-2', 'ray.ruff', 'nschema/100.changeLog.xml', GETDATE(), 42, '8:94e07826e901c31718f68eb25a5a503e', 'addColumn tableName=code_orgstructure', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140120-rc-1::ryan.chen
ALTER TABLE schedule_task DROP CONSTRAINT FK_SCHEDULE_TASK_EMAIL_ID
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE schedule_task DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'schedule_task') AND [c].[name] = N'notification_email_id'
EXEC sp_executesql @sql
GO

ALTER TABLE schedule_task DROP COLUMN notification_email_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140120-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 43, '8:bf83c75a2f67bca4be2da079336f9862', 'dropForeignKeyConstraint baseTableName=schedule_task, constraintName=FK_SCHEDULE_TASK_EMAIL_ID; dropColumn columnName=notification_email_id, tableName=schedule_task', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140120-rc-2::ryan.chen
ALTER TABLE schedule_report ADD notification_email_id nvarchar(15)
GO

ALTER TABLE schedule_report ADD CONSTRAINT FK_SCHEDULE_TASK_EMAIL_ID FOREIGN KEY (notification_email_id) REFERENCES email_profile (email_profile_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140120-rc-2', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 44, '8:c32f8814a25b8bca53894a8944db751f', 'addColumn tableName=schedule_report; addForeignKeyConstraint baseTableName=schedule_report, constraintName=FK_SCHEDULE_TASK_EMAIL_ID, referencedTableName=email_profile', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140120-ns-1::nicky.siu
CREATE TABLE template_widget_goals_config (widget_id nvarchar(15) NOT NULL, number_of_goals int, target_period int, target_period_unit char(1), display_option char(1) NOT NULL)
GO

ALTER TABLE template_widget_goals_config ADD CONSTRAINT PK_WIDGET_GOALS_CONFIG PRIMARY KEY (widget_id)
GO

ALTER TABLE template_widget_goals_config ADD CONSTRAINT FK_WIDGET_GOALS_CONFIG FOREIGN KEY (widget_id) REFERENCES template_widget (id) ON DELETE CASCADE
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140120-ns-1', 'nicky.siu', 'nschema/100.changeLog.xml', GETDATE(), 45, '8:3cc836bebfe71c30fa9e2fec820939f0', 'createTable tableName=template_widget_goals_config; addPrimaryKey constraintName=PK_WIDGET_GOALS_CONFIG, tableName=template_widget_goals_config; addForeignKeyConstraint baseTableName=template_widget_goals_config, constraintName=FK_WIDGET_GOALS_CON...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140122-rc-1::ryan.chen
ALTER TABLE schedule_report DROP CONSTRAINT FK_SCHEDULE_TASK_EMAIL_ID
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140122-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 46, '8:faf30f81353b853647084c1e2dd9febe', 'dropForeignKeyConstraint baseTableName=schedule_report, constraintName=FK_SCHEDULE_TASK_EMAIL_ID', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140123-rr-1::ray.ruff
CREATE TABLE org_email_association (keyid nvarchar(15) NOT NULL, orgid nvarchar(15) NOT NULL, email_association nvarchar(85) NOT NULL)
GO

ALTER TABLE org_email_association ADD CONSTRAINT PK_ORG_EMAIL_ASSOC PRIMARY KEY (keyid)
GO

ALTER TABLE org_email_association ADD CONSTRAINT FK_ORG_EMAIL_ORGID FOREIGN KEY (orgid) REFERENCES code_orgstructure (orgid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140123-rr-1', 'ray.ruff', 'nschema/100.changeLog.xml', GETDATE(), 47, '8:b2d88856e8ef9189d8758aab10c0ac34', 'createTable tableName=org_email_association; addPrimaryKey constraintName=PK_ORG_EMAIL_ASSOC, tableName=org_email_association; addForeignKeyConstraint baseTableName=org_email_association, constraintName=FK_ORG_EMAIL_ORGID, referencedTableName=code...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140124-rc-1::ryan.chen
ALTER TABLE schedule_report ADD CONSTRAINT FK_SCHEDULE_TASK_EMAIL_ID FOREIGN KEY (notification_email_id) REFERENCES email_profile (email_profile_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140124-rc-1', 'ryan.chen', 'nschema/100.changeLog.xml', GETDATE(), 48, '8:be2b392798f59dfc9983b1327b731ddd', 'addForeignKeyConstraint baseTableName=schedule_report, constraintName=FK_SCHEDULE_TASK_EMAIL_ID, referencedTableName=email_profile', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140124-rr-1::ray.ruff
ALTER TABLE mastertest ADD disable_right_click char(1)
GO

ALTER TABLE mastertest ADD start_based_time_limit char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140124-rr-1', 'ray.ruff', 'nschema/100.changeLog.xml', GETDATE(), 49, '8:89d1088a09532b5578163eafaaf1aecb', 'addColumn tableName=mastertest', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140204-kc-1::kennex.chan
ALTER TABLE appraisal_status_category ALTER COLUMN code nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140204-kc-1', 'kennex.chan', 'nschema/100.changeLog.xml', GETDATE(), 50, '8:5181738799328b1c0ca188761b95cea5', 'modifyDataType columnName=code, tableName=appraisal_status_category', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140204-kc-2::kennex.chan
ALTER TABLE appraisal_status ALTER COLUMN code nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140204-kc-2', 'kennex.chan', 'nschema/100.changeLog.xml', GETDATE(), 51, '8:5686deedb1b5709181e66242d1684262', 'modifyDataType columnName=code, tableName=appraisal_status', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140204-kc-3::kennex.chan
ALTER TABLE appraisal_status ALTER COLUMN label_key nvarchar(100)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140204-kc-3', 'kennex.chan', 'nschema/100.changeLog.xml', GETDATE(), 52, '8:2276806af264cf314943be144d7f2856', 'modifyDataType columnName=label_key, tableName=appraisal_status', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/100.changeLog.xml::20140204-kc-4::kennex.chan
ALTER TABLE appraisal_status_category ALTER COLUMN label_key nvarchar(100)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140204-kc-4', 'kennex.chan', 'nschema/100.changeLog.xml', GETDATE(), 53, '8:215668805d7beccf550e0f967212906c', 'modifyDataType columnName=label_key, tableName=appraisal_status_category', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::27Jan2014-sg-1::shravan
UPDATE ekpversion SET dblevel = 101, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('27Jan2014-sg-1', 'shravan', 'nschema/101.changeLog.xml', GETDATE(), 54, '8:b00547a379e1e608b6948be38776ad09', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140204-kh-1::kelvin.hung
ALTER TABLE appraisal_template ADD super_appraiser_option char(1) CONSTRAINT DF_appraisal_template_super_appraiser_option DEFAULT 'D' NOT NULL
GO

ALTER TABLE appraisal_template ADD specified_super_appraiser_id nvarchar(85)
GO

ALTER TABLE appraisal_template ADD CONSTRAINT FK_SUPER_APPRAISER_USER_ID FOREIGN KEY (specified_super_appraiser_id) REFERENCES users (userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140204-kh-1', 'kelvin.hung', 'nschema/101.changeLog.xml', GETDATE(), 55, '8:f1d9a307f4f2b2d10bfd0dc1c1f75ab4', 'addColumn tableName=appraisal_template; addForeignKeyConstraint baseTableName=appraisal_template, constraintName=FK_SUPER_APPRAISER_USER_ID, referencedTableName=users', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140207-gl-1::garbo.lim
DROP TABLE course_comp_exp_temp
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140207-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 56, '8:6b04e22cc555cec809e74b27868307a4', 'dropTable tableName=course_comp_exp_temp', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140211-sn-1::sam.ng
update code_initialURL set description = 'label.firstscreen.current_modules' where pageid = 0
GO

update code_initialURL set description = 'label.firstscreen.home_page' where pageid = 2
GO

update code_initialURL set description = 'label.firstscreen.learn_menu' where pageid = 3
GO

update code_initialURL set description = 'label.firstscreen.manage_menu' where pageid = 10
GO

update code_initialURL set description = 'label.firstscreen.enrollment_approval' where pageid = 11
GO

insert into code_initialURL (pageid, description, pageURL) values (12, 'label.firstscreen.learning_path', '/servlet/ekp?TX=LEARNINGPATH')
GO

insert into code_initialURL (pageid, description, pageURL) values (13, 'label.firstscreen.catalog_browser', '/servlet/ekp?TX=STRUCTUREDCATALOG')
GO

insert into code_initialURL (pageid, description, pageURL) values (14, 'label.firstscreen.catalog_search', '/servlet/ekp?TX=FRAMELESSCATALOGSEARCH')
GO

insert into code_initialURL (pageid, description, pageURL) values (15, 'label.firstscreen.records_transcript', '/servlet/ekp?TX=TABLISTTRAN')
GO

insert into code_initialURL (pageid, description, pageURL) values (16, 'label.firstscreen.career_center_summary', '/servlet/ekp?TX=COMPCENTERINTRO')
GO

insert into code_initialURL (pageid, description, pageURL) values (17, 'label.firstscreen.dashboard', '/servlet/ekp?TX=DASHBOARD')
GO

insert into code_initialURL (pageid, description, pageURL) values (18, 'label.firstscreen.review', '/servlet/ekp?TX=REVIEW')
GO

insert into code_initialURL (pageid, description, pageURL) values (19, 'label.firstscreen.admin_access', '/servlet/ekp?TX=TEACHREVIEW&MODE=ADMIN')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140211-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 57, '8:17175379bdba95b15a33b2a8bd69c258', 'sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140211-rc-1::ryan.chen
ALTER TABLE code_orgstructure ADD esig_legalname_format nvarchar(85) CONSTRAINT DF_code_orgstructure_esig_legalname_format DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_course_csv_loader char(1) CONSTRAINT DF_code_orgstructure_esig_course_csv_loader DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_program_csv_loader char(1) CONSTRAINT DF_code_orgstructure_esig_program_csv_loader DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_course_withdraw char(1) CONSTRAINT DF_code_orgstructure_esig_course_withdraw DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_course_modify char(1) CONSTRAINT DF_code_orgstructure_esig_course_modify DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_course_launch char(1) CONSTRAINT DF_code_orgstructure_esig_course_launch DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_course_import char(1) CONSTRAINT DF_code_orgstructure_esig_course_import DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_course_finish char(1) CONSTRAINT DF_code_orgstructure_esig_course_finish DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_exam_launch char(1) CONSTRAINT DF_code_orgstructure_esig_exam_launch DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_question_modify char(1) CONSTRAINT DF_code_orgstructure_esig_question_modify DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_question_import char(1) CONSTRAINT DF_code_orgstructure_esig_question_import DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_grading_test_answer char(1) CONSTRAINT DF_code_orgstructure_esig_grading_test_answer DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_reviewer_modify_trans char(1) CONSTRAINT DF_code_orgstructure_esig_reviewer_modify_trans DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_editor_modify_trans char(1) CONSTRAINT DF_code_orgstructure_esig_editor_modify_trans DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_modify_attendance char(1) CONSTRAINT DF_code_orgstructure_esig_modify_attendance DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_wizard_modify_trans char(1) CONSTRAINT DF_code_orgstructure_esig_wizard_modify_trans DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_modify_ext_training char(1) CONSTRAINT DF_code_orgstructure_esig_modify_ext_training DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_modify_cert char(1) CONSTRAINT DF_code_orgstructure_esig_modify_cert DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_award_revert_cert char(1) CONSTRAINT DF_code_orgstructure_esig_award_revert_cert DEFAULT 'I' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140211-rc-1', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 58, '8:6afd6af6ef912b7a622e82ab43d227db', 'addColumn tableName=code_orgstructure', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140211-ns-1::nicky.siu
ALTER TABLE goal ADD is_template char(1) CONSTRAINT DF_goal_is_template DEFAULT 'N' NOT NULL
GO

ALTER TABLE goal ADD goal_template_name char(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140211-ns-1', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 59, '8:b6d2e07ea1d716163020a79938f4cb1c', 'addColumn tableName=goal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140211-ns-2::nicky.siu
DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[' + @DynamicSchemaName + N'].[goal_vw]'))
    BEGIN
DECLARE @sql AS NVARCHAR(MAX) set @sql = N'CREATE VIEW [' + @DynamicSchemaName + N'].[goal_vw] AS SELECT ''This is a code stub which will be replaced by an Alter Statement'' as [code_stub]';
EXEC sp_executesql @sql;
END
GO

ALTER VIEW goal_vw AS SELECT
				    g.id,
				    'O'  as goal_type,
				    g.title,
				    g.description,
				    g.is_available_appraisal,
				    g.owner_id,
				    gorg.parent_id,
				    gorg.program_id,
				    gorg.category_id,
				    NULL as start_date,
				    NULL as target_date,
				    NULL as weight,
				    NULL as measurement,
				    NULL as org_goal_id,
				    NULL as org_goal_title,
				    NULL as created_by,
				    gt.status,
				    gt.progress,
				    gt.result_achieved_date,
				    gorg.auto_update_tracking,
				    gt.result_to_date
				  FROM goal g
				    INNER JOIN goal_organizational gorg
				      ON g.id = gorg.id
				    LEFT JOIN goal_status_prg_vw gt
				      ON gt.goal_id = gorg.id
				  UNION ALL
				  SELECT
				    g.id,
				    'P'               as goal_type,
				    g.title,
				    g.description,
				    g.is_available_appraisal,
				    g.owner_id,
				    NULL              as parent_id,
				    gorg2.program_id  as program_id,
				    gorg2.category_id as category_id,
				    gp.start_date,
				    gp.target_date,
				    gp.weight,
				    gp.measurement,
				    gp.org_goal_id,
				    gorg.title        as org_goal_title,
				    gp.created_by,
				    gt.status,
				    gt.progress,
				    gt.result_achieved_date,
				    NULL              as auto_update_tracking,
				    gt.result_to_date
				  FROM goal g
				    INNER JOIN goal_personal gp
				      ON g.is_template = 'N' and g.id = gp.id
				    LEFT JOIN goal gorg
				      ON gp.org_goal_id = gorg.id
				    LEFT JOIN goal_organizational gorg2
				      ON gorg2.id = gorg.id
				    LEFT JOIN goal_status_prg_vw gt
				      ON gt.goal_id = gp.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140211-ns-2', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 60, '8:1c78b62222414976852897a7241955b3', 'createView viewName=goal_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140214-mc-1::mike.chung
ALTER TABLE schedule_report ADD attachment_file_name nvarchar(255)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140214-mc-1', 'mike.chung', 'nschema/101.changeLog.xml', GETDATE(), 61, '8:ca1832622c3723e4cb4deabdd643312f', 'addColumn tableName=schedule_report', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140214-dc-1::derek.chan
ALTER TABLE learningObject ADD allowSupervisorAssess char(1) CONSTRAINT DF_learningObject_allowSupervisorAssess DEFAULT 'N' NOT NULL
GO

ALTER TABLE transcript ADD allowSupervisorAssess char(1) CONSTRAINT DF_transcript_allowSupervisorAssess DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140214-dc-1', 'derek.chan', 'nschema/101.changeLog.xml', GETDATE(), 62, '8:63bcca3d1723343e45adf1103aa0f09e', 'addColumn tableName=learningObject; addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140217-sn-2::sam.ng
update code_initialURL set description = 'label.firstscreen.home_page' where pageid = 0
GO

update code_initialURL set description = 'label.firstscreen.learn_menu' where pageid = 2
GO

update code_initialURL set description = 'label.firstscreen.current_modules' where pageid = 3
GO

update code_initialURL set description = 'label.firstscreen.enrollment_approval' where pageid = 10
GO

update code_initialURL set description = 'label.firstscreen.manage_menu' where pageid = 11
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140217-sn-2', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 63, '8:738ef6845c910b440296472bb9effc3d', 'sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140218-ns-1::nicky.siu
ALTER TABLE goal_personal ADD date_of_assignment char(1) CONSTRAINT DF_goal_personal_date_of_assignment DEFAULT 'N' NOT NULL
GO

ALTER TABLE goal_personal ADD target_date_period int
GO

ALTER TABLE goal_personal ADD target_date_period_unit char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140218-ns-1', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 64, '8:5e2639b57576930d8845976378b1351d', 'addColumn tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140218-ns-2::nicky.siu
ALTER TABLE goal_development ADD date_of_assignment char(1) CONSTRAINT DF_goal_development_date_of_assignment DEFAULT 'N' NOT NULL
GO

ALTER TABLE goal_development ADD target_date_period int
GO

ALTER TABLE goal_development ADD target_date_period_unit char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140218-ns-2', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 65, '8:c6cb55ba02a6240b6ea2ebda5e78aee0', 'addColumn tableName=goal_development', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140218-ns-3::nicky.siu
ALTER TABLE goal_personal ALTER COLUMN weight int NULL
GO

ALTER TABLE goal_personal ALTER COLUMN start_date datetime NULL
GO

ALTER TABLE goal_personal ALTER COLUMN target_date datetime NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140218-ns-3', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 66, '8:86d5cb3dc1585330b0c6a530a94e0342', 'dropNotNullConstraint columnName=weight, tableName=goal_personal; dropNotNullConstraint columnName=start_date, tableName=goal_personal; dropNotNullConstraint columnName=target_date, tableName=goal_personal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140218-ns-4::nicky.siu
ALTER TABLE goal_development ALTER COLUMN weight int NULL
GO

ALTER TABLE goal_development ALTER COLUMN start_date datetime NULL
GO

ALTER TABLE goal_development ALTER COLUMN target_date datetime NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140218-ns-4', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 67, '8:41f55cf46d565d6546cf548f975b6a70', 'dropNotNullConstraint columnName=weight, tableName=goal_development; dropNotNullConstraint columnName=start_date, tableName=goal_development; dropNotNullConstraint columnName=target_date, tableName=goal_development', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140218-ns-5::nicky.siu
ALTER TABLE goal ALTER COLUMN title nvarchar(255) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140218-ns-5', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 68, '8:8eee942a2ba0f03a4436794b14e7ff34', 'dropNotNullConstraint columnName=title, tableName=goal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140221-ns-1::nicky.siu
ALTER TABLE goal ALTER COLUMN goal_template_name nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140221-ns-1', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 69, '8:b1ab29daa4598487008a3ca0613984a7', 'modifyDataType columnName=goal_template_name, tableName=goal', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140224-sn-1::sam.ng
ALTER TABLE course ADD effective_revision int CONSTRAINT DF_course_effective_revision DEFAULT 0 NOT NULL
GO

ALTER TABLE course_revision ADD date_effective datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140224-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 70, '8:2b25ef9f8b29965291a54a321ba58ded', 'addColumn tableName=course; addColumn tableName=course_revision', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140314-js-1::janice.sung
ALTER TABLE course ADD approved_revision int
GO

ALTER TABLE course_revision ADD date_approved datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140314-js-1', 'janice.sung', 'nschema/101.changeLog.xml', GETDATE(), 71, '8:6ee2747e50a8ce3430ad9e0ca51a6f03', 'addColumn tableName=course; addColumn tableName=course_revision', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140402-gl-1::garbo.lim
CREATE TABLE users_meta (user_meta_id 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), personaltitle nvarchar(4), email nvarchar(85), employeeno nvarchar(85), externalAuthentication char(1) NOT NULL, autoDelete char(1), deleteDate datetime, dateofbirth datetime, gender char(1), expirationdate datetime, nationality nvarchar(85), dietary_constraints nvarchar(85), middlename nvarchar(85), skype_name nvarchar(85), date_closed datetime, languagepref smallint NOT NULL, useExternalMail char(1) NOT NULL, email_forward_type char(1) CONSTRAINT DF_users_meta_email_forward_type DEFAULT 'N' NOT NULL, forward_to_email nvarchar(85), b_companyname nvarchar(50), b_address1 nvarchar(150), b_address2 nvarchar(150), b_city nvarchar(50), b_province_state nvarchar(50), b_country nvarchar(3), b_phone nvarchar(85), b_mobile nvarchar(15), b_postalcode nvarchar(10), fax nvarchar(15), reloc_willingness char(1), reloc_desired_location nvarchar(250), reloc_financial_assist char(1), reloc_reason nvarchar (max), resume nvarchar(100), resume_byte_size int, resume_uploader_uid nvarchar(85), resume_upload_date datetime, lastlogon datetime, lastpwchange datetime, nlogons int NOT NULL, nattempts int NOT NULL, elapsedloginseconds int NOT NULL, nlogouts int NOT NULL, first_login datetime, firstlogonfailure datetime, lastlogonfailure datetime, nlogonfailure int, requirepwchange char(1) CONSTRAINT DF_users_meta_requirepwchange DEFAULT 'N' NOT NULL, lastactivedate datetime, mekp_last_sync datetime, mekp_activation datetime, terms_of_use_accept_date datetime)
GO

ALTER TABLE users_meta ADD CONSTRAINT pk_users_meta PRIMARY KEY (user_meta_id)
GO

ALTER TABLE users_meta ADD CONSTRAINT uk_currentpid_users_meta UNIQUE (currentPID)
GO

INSERT INTO users_meta (user_meta_id, password, currentstatus, currentPID, familyname,
            								givenname, othername, personaltitle, email, employeeno,
            								externalAuthentication, autoDelete, deleteDate, dateofbirth,
            								gender, expirationdate, nationality, dietary_constraints,
            								middlename, skype_name, date_closed, languagepref,
            								useExternalMail, email_forward_type, forward_to_email,
            								b_companyname, b_address1, b_address2, b_city, b_province_state,
            								b_country, b_phone, b_mobile, b_postalcode, fax,
            								reloc_willingness, reloc_desired_location, reloc_financial_assist,
            								reloc_reason, resume, resume_byte_size, resume_uploader_uid,
            								resume_upload_date, lastlogon, lastpwchange, nlogons, nattempts,
            								elapsedloginseconds, nlogouts, first_login, firstlogonfailure,
            								lastlogonfailure, nlogonfailure, requirepwchange, lastactivedate,
            								mekp_last_sync, mekp_activation, terms_of_use_accept_date)
SELECT 	u.userid, u.password, u.currentstatus, u.currentPID, u.familyname,
          				u.givenname, u.othername, u.personaltitle, u.email, u.employeeno,
          				u.externalAuthentication, u.autoDelete, u.deleteDate, u.dateofbirth,
          				u.gender, u.expirationdate, u.nationality, u.dietary_constraints,
          				u.middlename, u.skype_name, u.date_closed, coalesce(up.languagepref, 1),
          				coalesce(up.useExternalMail, 'N'), coalesce(up.email_forward_type, 'N'), up.forward_to_email,
          				ui.b_companyname, ui.b_address1, ui.b_address2, ui.b_city, ui.b_province_state,
          				ui.b_country, ui.b_phone, ui.b_mobile, ui.b_postalcode, ui.fax,
          				ui.reloc_willingness, ui.reloc_desired_location, ui.reloc_financial_assist,
          				ui.reloc_reason, ui.resume, ui.resume_byte_size, ui.resume_uploader_uid,
          				ui.resume_upload_date, us.lastlogon, us.lastpwchange, coalesce(us.nlogons, 0), coalesce(us.nattempts,0),
          				coalesce(us.elapsedloginseconds,0), coalesce(us.nlogouts,0), us.first_login, us.firstlogonfailure,
          				us.lastlogonfailure, us.nlogonfailure, coalesce(us.requirepwchange, 'N'), us.lastactivedate,
          				us.mekp_last_sync, us.mekp_activation, us.terms_of_use_accept_date
				FROM users u left join userprefs up on u.userid = up.userid
				left join userinfo ui on u.userid = ui.userid
				left join userstats us on u.userid = us.userid
GO

ALTER TABLE users ADD user_meta_id nvarchar(85)
GO

ALTER TABLE users ADD assignment_ref_code nvarchar(255)
GO

CREATE NONCLUSTERED INDEX nuk_users_meta ON users(user_meta_id, userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140402-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 72, '8:03a8246a2c184d566140eb99d72c0192', 'createTable tableName=users_meta; addPrimaryKey constraintName=pk_users_meta, tableName=users_meta; addUniqueConstraint constraintName=uk_currentpid_users_meta, tableName=users_meta; sql; addColumn tableName=users; createIndex indexName=nuk_users_...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140402-gl-2::garbo.lim
update users set user_meta_id = userid
GO

DROP INDEX nuk_users_meta ON users
GO

ALTER TABLE users ALTER COLUMN user_meta_id nvarchar(85) NOT NULL
GO

CREATE NONCLUSTERED INDEX nuk_users_meta ON users(user_meta_id, userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140402-gl-2', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 73, '8:1473f0fa920411fa4b8677e38b11eb12', 'sql; dropIndex indexName=nuk_users_meta, tableName=users; addNotNullConstraint columnName=user_meta_id, tableName=users; createIndex indexName=nuk_users_meta, tableName=users', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140417-mc-1::mike.chung
IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_registration'
            )
            DROP TRIGGER i_registration
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'u_registration'
            )
            DROP TRIGGER u_registration
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'd_registration'
            )
            DROP TRIGGER d_registration
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140417-mc-1', 'mike.chung', 'nschema/101.changeLog.xml', GETDATE(), 74, '8:b245227da0e4e0dc775c044d5fd84dff', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140403-kc-1::kennex.chan
ALTER TABLE registration DROP CONSTRAINT UK_REGISTRATION
GO

ALTER TABLE registration ADD user_meta_id nvarchar(85)
GO

update registration set user_meta_id = userid
GO

ALTER TABLE registration ALTER COLUMN user_meta_id nvarchar(85) NOT NULL
GO

ALTER TABLE registration ADD CONSTRAINT UK_REGISTRATION UNIQUE (eventid, user_meta_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140403-kc-1', 'kennex.chan', 'nschema/101.changeLog.xml', GETDATE(), 75, '8:09ea578da5ed64bc9249af83175345bc', 'dropUniqueConstraint constraintName=UK_REGISTRATION, tableName=registration; addColumn tableName=registration; sql; addNotNullConstraint columnName=user_meta_id, tableName=registration; addUniqueConstraint constraintName=UK_REGISTRATION, tableName...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140403-gl-1::garbo.lim
DECLARE @default sysname
			SELECT @default = object_name(default_object_id) FROM sys.columns WHERE object_id=object_id('users') AND name='skype_name'
			EXEC ('ALTER TABLE [users] DROP CONSTRAINT ' + @default)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140403-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 76, '8:fe86ce7410b03aabbe472d8d0f57b489', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140403-gl-2::garbo.lim
ALTER TABLE users ALTER COLUMN skype_name nvarchar(85) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140403-gl-2', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 77, '8:b496d529cd0c1bf12338ea284310e090', 'dropNotNullConstraint columnName=skype_name, tableName=users', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140507-dc-1::derek.chan
IF EXISTS (
            SELECT * 
			FROM sys.indexes 
			WHERE name='NUK_EMAIL' AND object_id = OBJECT_ID('users')
            )
            DROP INDEX NUK_EMAIL on users
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140507-dc-1', 'derek.chan', 'nschema/101.changeLog.xml', GETDATE(), 78, '8:c7671b9eb56130fc3ee63dab0be83e2f', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140403-gl-3::garbo.lim
ALTER TABLE users ADD CONSTRAINT fk_users_users_meta FOREIGN KEY (user_meta_id) REFERENCES users_meta (user_meta_id)
GO

ALTER TABLE users DROP CONSTRAINT ak_currentpid_users
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'password'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN password
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'currentstatus'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN currentstatus
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'currentPID'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN currentPID
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'familyname'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN familyname
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'givenname'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN givenname
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'othername'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN othername
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'personaltitle'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN personaltitle
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'email'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN email
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'employeeno'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN employeeno
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'externalAuthentication'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN externalAuthentication
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'autoDelete'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN autoDelete
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'deleteDate'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN deleteDate
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'dateofbirth'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN dateofbirth
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'gender'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN gender
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'expirationdate'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN expirationdate
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'nationality'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN nationality
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'dietary_constraints'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN dietary_constraints
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'middlename'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN middlename
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'skype_name'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN skype_name
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users') AND [c].[name] = N'date_closed'
EXEC sp_executesql @sql
GO

ALTER TABLE users DROP COLUMN date_closed
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140403-gl-3', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 79, '8:cad4bb8d121cb5cba43ea6ebea2cb867', 'addForeignKeyConstraint baseTableName=users, constraintName=fk_users_users_meta, referencedTableName=users_meta; dropUniqueConstraint constraintName=ak_currentpid_users, tableName=users; dropColumn columnName=password, tableName=users; dropColumn ...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140403-gl-4::garbo.lim
exec sp_rename 'users', 'users_assignment'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140403-gl-4', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 80, '8:0f2b3a779775290dcfbdfd67d12faef4', 'renameTable newTableName=users_assignment, oldTableName=users', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140403-gl-6::garbo.lim
CREATE VIEW users AS select ua.*, um.password, um.currentstatus, um.currentPID, um.familyname,
           		   um.givenname, um.othername, um.personaltitle, um.email, um.employeeno,
           		   um.externalAuthentication, um.autoDelete, um.deleteDate, um.dateofbirth,
           		   um.gender, um.expirationdate, um.nationality, um.dietary_constraints,
           		   um.middlename, um.skype_name, um.date_closed
			from users_assignment ua, users_meta um
			where ua.user_meta_id = um.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140403-gl-6', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 81, '8:af4e25db979fe0b5cae8706d0c8cb301', 'createView viewName=users', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140403-gl-8::garbo.lim
DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[' + @DynamicSchemaName + N'].[valid_users_vw]'))
    BEGIN
DECLARE @sql AS NVARCHAR(MAX) set @sql = N'CREATE VIEW [' + @DynamicSchemaName + N'].[valid_users_vw] AS SELECT ''This is a code stub which will be replaced by an Alter Statement'' as [code_stub]';
EXEC sp_executesql @sql;
END
GO

ALTER VIEW valid_users_vw AS select *
		  	from users
		  	where currentstatus NOT IN (3, 5, 6)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140403-gl-8', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 82, '8:57a0cf83b79fa10660bf9fff1538358b', 'createView viewName=valid_users_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140404-gl-2::garbo.lim
DROP VIEW user_appraisal_vw
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140404-gl-2', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 83, '8:89d3deeab5faa7e9945ac2a74845138d', 'dropView viewName=user_appraisal_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140404-gl-3::garbo.lim
CREATE VIEW user_appraisal_vw AS select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.currentPID as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
				   sda.currentPID as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
				   ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
				   aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
				   aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
				   atp.start_date as template_start_date, atp.end_date as template_end_date
			from user_appraisal ua
			inner join appraisal_template atp on atp.id = ua.appraisal_template_id
			left join valid_users_vw da on da.userid = ua.appraiser_id
			left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
			inner join appraisal_status ast on ast.id = ua.status_id
			left join appraisal_status_category ac on ac.id = ast.category_id
			left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140404-gl-3', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 84, '8:8d16f590a69794e6d7eeba5642b8204f', 'createView viewName=user_appraisal_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140407-sn-1::sam.ng
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_companyname'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_companyname
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_address1'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_address1
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_address2'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_address2
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_city'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_city
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_province_state'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_province_state
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_country'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_country
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_phone'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_phone
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_mobile'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_mobile
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'b_postalcode'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN b_postalcode
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo') AND [c].[name] = N'fax'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo DROP COLUMN fax
GO

exec sp_rename 'userinfo', 'userinfo_assignment'
GO

CREATE VIEW userinfo AS select userinfo_assignment.userid, userinfo_assignment.picture,
	               users_meta.b_companyname, users_meta.b_address1, users_meta.b_address2,users_meta.b_city,users_meta.b_province_state,
	               users_meta.b_country,users_meta.b_phone,users_meta.b_mobile,users_meta.b_postalcode,users_meta.fax, 
	               userinfo_assignment.e_country, userinfo_assignment.employeestatus, userinfo_assignment.employeeid,
	               userinfo_assignment.personalid, userinfo_assignment.taxid, userinfo_assignment.returndate, 
	               userinfo_assignment.regular_temp, userinfo_assignment.full_part_time,
	               userinfo_assignment.contractor, userinfo_assignment.language, userinfo_assignment.business_unit_code, 
	               userinfo_assignment.business_unit_desc, userinfo_assignment.joblocation, userinfo_assignment.taxlocation, 
	               userinfo_assignment.reloc_willingness, userinfo_assignment.reloc_desired_location, userinfo_assignment.reloc_financial_assist,
	               userinfo_assignment.reloc_reason, userinfo_assignment.resume, userinfo_assignment.resume_byte_size,
	               userinfo_assignment.resume_uploader_uid, userinfo_assignment.resume_upload_date
	        from userinfo_assignment
	        JOIN users_assignment on userinfo_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140407-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 85, '8:a74a0d0227e95d86a7cb46294ba534a8', 'dropColumn columnName=b_companyname, tableName=userinfo; dropColumn columnName=b_address1, tableName=userinfo; dropColumn columnName=b_address2, tableName=userinfo; dropColumn columnName=b_city, tableName=userinfo; dropColumn columnName=b_province...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140408-gl-1::garbo.lim
update users_assignment set assignment_ref_code = (select currentPID from users_meta where users_meta.user_meta_id = users_assignment.user_meta_id)
GO

ALTER TABLE users_assignment ALTER COLUMN assignment_ref_code nvarchar(255) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140408-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 86, '8:ea908dacabd1f6bac9163515962cfe56', 'sql; addNotNullConstraint columnName=assignment_ref_code, tableName=users_assignment', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140409-gl-1::garbo.lim
ALTER TABLE users_assignment ADD UNIQUE (assignment_ref_code)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140409-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 87, '8:8d5176a70b36e403d4d76b47bdac128d', 'addUniqueConstraint tableName=users_assignment', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140408-sn-1::sam.ng
DECLARE @default sysname
			SELECT @default = object_name(default_object_id) FROM sys.columns WHERE object_id=object_id('userstats') AND name='requirepwchange'
			EXEC ('ALTER TABLE [userstats] DROP CONSTRAINT ' + @default)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140408-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 88, '8:01991592486f89c68a18c563e74534de', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140408-sn-2::sam.ng
ALTER TABLE userstats ALTER COLUMN nlogons int NULL
GO

ALTER TABLE userstats ALTER COLUMN nattempts int NULL
GO

ALTER TABLE userstats ALTER COLUMN elapsedloginseconds int NULL
GO

ALTER TABLE userstats ALTER COLUMN nlogouts int NULL
GO

ALTER TABLE userstats ALTER COLUMN requirepwchange char(1) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140408-sn-2', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 89, '8:61fb2355d9b2488a553b64fe0c2eac9a', 'dropNotNullConstraint columnName=nlogons, tableName=userstats; dropNotNullConstraint columnName=nattempts, tableName=userstats; dropNotNullConstraint columnName=elapsedloginseconds, tableName=userstats; dropNotNullConstraint columnName=nlogouts, t...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140408-sn-3::sam.ng
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'lastlogon'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN lastlogon
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'lastpwchange'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN lastpwchange
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'nlogons'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN nlogons
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'nattempts'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN nattempts
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'elapsedloginseconds'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN elapsedloginseconds
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'nlogouts'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN nlogouts
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'first_login'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN first_login
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'firstlogonfailure'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN firstlogonfailure
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'lastlogonfailure'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN lastlogonfailure
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'nlogonfailure'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN nlogonfailure
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'requirepwchange'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN requirepwchange
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'lastactivedate'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN lastactivedate
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'mekp_activation'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN mekp_activation
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'mekp_last_sync'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN mekp_last_sync
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userstats DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userstats') AND [c].[name] = N'terms_of_use_accept_date'
EXEC sp_executesql @sql
GO

ALTER TABLE userstats DROP COLUMN terms_of_use_accept_date
GO

exec sp_rename 'userstats', 'userstats_assignment'
GO

CREATE VIEW userstats AS select userstats_assignment.userid, 
	               users_meta.lastlogon,
	               users_meta.lastpwchange, 
	               users_meta.nlogons,
	               users_meta.nattempts,
	               userstats_assignment.nmsgspending,
	               users_meta.elapsedloginseconds,
	               users_meta.nlogouts,
	               userstats_assignment.lastAutoEnrollUpdate,
	               users_meta.first_login,
	               users_meta.firstlogonfailure, 
	               users_meta.lastlogonfailure, 
	               users_meta.nlogonfailure, 
	               userstats_assignment.last_catalog_id,
	               users_meta.requirepwchange, 
	               users_meta.lastactivedate, 
	               users_meta.mekp_activation, 
	               users_meta.mekp_last_sync, 
	               userstats_assignment.last_job_auto_assign_update,
	               userstats_assignment.last_crs_offline_auto_enroll, 
	               userstats_assignment.last_job_offline_auto_assign, 
	               users_meta.terms_of_use_accept_date
	        from userstats_assignment
	        JOIN users_assignment on userstats_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140408-sn-3', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 90, '8:48a860fd53099e03582049fca51d4c90', 'dropColumn columnName=lastlogon, tableName=userstats; dropColumn columnName=lastpwchange, tableName=userstats; dropColumn columnName=nlogons, tableName=userstats; dropColumn columnName=nattempts, tableName=userstats; dropColumn columnName=elapsedl...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140409-sn-1::sam.ng
DECLARE @default sysname
			SELECT @default = object_name(default_object_id) FROM sys.columns WHERE object_id=object_id('userprefs') AND name='email_forward_type'
			EXEC ('ALTER TABLE [userprefs] DROP CONSTRAINT ' + @default)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140409-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 91, '8:c7ba084061cd672981925119fdb76f83', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140409-sn-2::sam.ng
ALTER TABLE userprefs ALTER COLUMN languagepref smallint NULL
GO

ALTER TABLE userprefs ALTER COLUMN useExternalMail char(1) NULL
GO

ALTER TABLE userprefs ALTER COLUMN email_forward_type char(1) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140409-sn-2', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 92, '8:12787a5b39b1e663133418fb5df11587', 'dropNotNullConstraint columnName=languagepref, tableName=userprefs; dropNotNullConstraint columnName=useExternalMail, tableName=userprefs; dropNotNullConstraint columnName=email_forward_type, tableName=userprefs', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140409-sn-3::sam.ng
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userprefs DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userprefs') AND [c].[name] = N'languagepref'
EXEC sp_executesql @sql
GO

ALTER TABLE userprefs DROP COLUMN languagepref
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userprefs DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userprefs') AND [c].[name] = N'useExternalMail'
EXEC sp_executesql @sql
GO

ALTER TABLE userprefs DROP COLUMN useExternalMail
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userprefs DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userprefs') AND [c].[name] = N'email_forward_type'
EXEC sp_executesql @sql
GO

ALTER TABLE userprefs DROP COLUMN email_forward_type
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userprefs DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userprefs') AND [c].[name] = N'forward_to_email'
EXEC sp_executesql @sql
GO

ALTER TABLE userprefs DROP COLUMN forward_to_email
GO

exec sp_rename 'userprefs', 'userprefs_assignment'
GO

CREATE VIEW userprefs AS select userprefs_assignment.userid, 
	               userprefs_assignment.contentServer,
	               userprefs_assignment.ui, 
	               users_meta.languagepref,
	               userprefs_assignment.autoMailList,
	               userprefs_assignment.autoRegMsg,
	               users_meta.useExternalMail,
	               userprefs_assignment.initialURL,
	               userprefs_assignment.resultsPerPage,
	               userprefs_assignment.catalogid,
	               userprefs_assignment.userPref1, 
	               userprefs_assignment.userPref2, 
	               userprefs_assignment.time_zone, 
	               users_meta.email_forward_type,
	               users_meta.forward_to_email, 
	               userprefs_assignment.welcome_email_senddate
	        from userprefs_assignment
	        JOIN users_assignment on userprefs_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140409-sn-3', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 93, '8:6f956fd9f0bb02fade0506acaae7aedc', 'dropColumn columnName=languagepref, tableName=userprefs; dropColumn columnName=useExternalMail, tableName=userprefs; dropColumn columnName=email_forward_type, tableName=userprefs; dropColumn columnName=forward_to_email, tableName=userprefs; rename...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140409-sn-4::sam.ng
ALTER TABLE user_profile_education DROP CONSTRAINT FK_USER_PROFILE_EDU_USERS
GO

ALTER TABLE user_profile_work_history DROP CONSTRAINT FK_WORK_HIST_UID
GO

ALTER TABLE user_profile_lang_skills DROP CONSTRAINT FK_LANGUAGE_SKILLS_UID
GO

exec sp_rename 'user_profile_education.userid', 'user_meta_id'
GO

exec sp_rename 'user_profile_work_history.user_id', 'user_meta_id'
GO

exec sp_rename 'user_profile_lang_skills.user_id', 'user_meta_id'
GO

ALTER TABLE user_profile_education ADD CONSTRAINT FK_USER_PROFILE_EDU_USERS FOREIGN KEY (user_meta_id) REFERENCES users_meta (user_meta_id)
GO

ALTER TABLE user_profile_work_history ADD CONSTRAINT FK_WORK_HIST_UID FOREIGN KEY (user_meta_id) REFERENCES users_meta (user_meta_id)
GO

ALTER TABLE user_profile_lang_skills ADD CONSTRAINT FK_LANGUAGE_SKILLS_UID FOREIGN KEY (user_meta_id) REFERENCES users_meta (user_meta_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140409-sn-4', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 94, '8:96bde76be999167d02f936e1cbe1c5b5', 'dropForeignKeyConstraint baseTableName=user_profile_education, constraintName=FK_USER_PROFILE_EDU_USERS; dropForeignKeyConstraint baseTableName=user_profile_work_history, constraintName=FK_WORK_HIST_UID; dropForeignKeyConstraint baseTableName=user...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140409-sn-5::sam.ng
DROP VIEW userinfo
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'reloc_willingness'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN reloc_willingness
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'reloc_desired_location'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN reloc_desired_location
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'reloc_financial_assist'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN reloc_financial_assist
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'reloc_reason'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN reloc_reason
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'resume'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN resume
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'resume_byte_size'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN resume_byte_size
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'resume_uploader_uid'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN resume_uploader_uid
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userinfo_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userinfo_assignment') AND [c].[name] = N'resume_upload_date'
EXEC sp_executesql @sql
GO

ALTER TABLE userinfo_assignment DROP COLUMN resume_upload_date
GO

CREATE VIEW userinfo AS select userinfo_assignment.userid, userinfo_assignment.picture,
	               users_meta.b_companyname, users_meta.b_address1, users_meta.b_address2,users_meta.b_city,users_meta.b_province_state,
	               users_meta.b_country,users_meta.b_phone,users_meta.b_mobile,users_meta.b_postalcode,users_meta.fax, 
	               userinfo_assignment.e_country, userinfo_assignment.employeestatus, userinfo_assignment.employeeid,
	               userinfo_assignment.personalid, userinfo_assignment.taxid, userinfo_assignment.returndate, 
	               userinfo_assignment.regular_temp, userinfo_assignment.full_part_time,
	               userinfo_assignment.contractor, userinfo_assignment.language, userinfo_assignment.business_unit_code, 
	               userinfo_assignment.business_unit_desc, userinfo_assignment.joblocation, userinfo_assignment.taxlocation, 
	               users_meta.reloc_willingness, users_meta.reloc_desired_location, users_meta.reloc_financial_assist,
	               users_meta.reloc_reason, users_meta.resume, users_meta.resume_byte_size,
	               users_meta.resume_uploader_uid, users_meta.resume_upload_date
	        from userinfo_assignment
	        JOIN users_assignment on userinfo_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140409-sn-5', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 95, '8:87a6a7f6b61e1a7d0d1270e686704841', 'dropView viewName=userinfo; dropColumn columnName=reloc_willingness, tableName=userinfo_assignment; dropColumn columnName=reloc_desired_location, tableName=userinfo_assignment; dropColumn columnName=reloc_financial_assist, tableName=userinfo_assig...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140411-kc-1::kennex.chan
DROP INDEX nuk_reg_user ON registration
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE registration DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'registration') AND [c].[name] = N'userid'
EXEC sp_executesql @sql
GO

ALTER TABLE registration DROP COLUMN userid
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140411-kc-1', 'kennex.chan', 'nschema/101.changeLog.xml', GETDATE(), 96, '8:0570d440ff553bf446a721e31dcd80b9', 'dropIndex indexName=nuk_reg_user, tableName=registration; dropColumn columnName=userid, tableName=registration', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140416-rl-1::robert.lowe
ALTER TABLE oauth_verifier DROP CONSTRAINT FK_OAUTH_VERIFIER_USER
GO

ALTER TABLE oauth_verifier ADD CONSTRAINT FK_OAUTH_VERIFIER_USER FOREIGN KEY (user_id) REFERENCES users_meta (user_meta_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140416-rl-1', 'robert.lowe', 'nschema/101.changeLog.xml', GETDATE(), 97, '8:39e3eb32c7031626be16f5a0795c76d1', 'dropForeignKeyConstraint baseTableName=oauth_verifier, constraintName=FK_OAUTH_VERIFIER_USER; addForeignKeyConstraint baseTableName=oauth_verifier, constraintName=FK_OAUTH_VERIFIER_USER, referencedTableName=users_meta', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140423-gl-1::garbo.lim
ALTER TABLE users_assignment ADD dappraiserid nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140423-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 98, '8:d3bb1b9505cdc71ccf04d95679b10f5d', 'addColumn tableName=users_assignment', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140423-gl-3::garbo.lim
update users_assignment set dappraiserid = (select u.userid from users_assignment u, users_meta um where um.user_meta_id = u.user_meta_id and um.currentPID = users_assignment.dappraiser)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140423-gl-3', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 99, '8:2f086138638cefcef6c500678ff2469b', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140423-gl-4::garbo.lim
CREATE NONCLUSTERED INDEX nuk_users_daid ON users_assignment(dappraiserid)
GO

DROP VIEW user_appraisal_vw
GO

DROP VIEW valid_users_vw
GO

DROP VIEW users
GO

CREATE VIEW users AS select ua.userid, ua.dappraiser, ua.level1id, ua.level2id, ua.level3id, ua.level4id, ua.level5id,
    		       ua.costcenter, ua.joindate, ua.userattr1, ua.userattr2, ua.userattr3, ua.userattr4, ua.userrole,
    		       ua.datecreated, ua.jobtitle, ua.UserOption1, ua.UserOption2, ua.UserOption3, ua.profile, ua.managername,
    		       ua.manageremail, ua.departmentname, ua.locationcode, ua.hrmanagername, ua.hrmanageremail,
    		       ua.line_of_business, ua.skills_assessment_enabled, ua.userattr5, ua.userattr6, ua.userattr7,
    		       ua.userattr8, ua.leaf_orgid, ua.creditbalance, ua.hr_tag, ua.departmentid, ua.costcentername,
    		       ua.creator_uid, ua.last_updated, ua.last_updater_uid, ua.offline_player_eval_days, ua.offline_player_eval_start_date,
    		       ua.token_account_id, ua.domain_id, ua.user_meta_id, ua.assignment_ref_code, ua.dappraiserid,
    		       um.password, um.currentstatus, um.currentPID, um.familyname,
           		   um.givenname, um.othername, um.personaltitle, um.email, um.employeeno,
           		   um.externalAuthentication, um.autoDelete, um.deleteDate, um.dateofbirth,
           		   um.gender, um.expirationdate, um.nationality, um.dietary_constraints,
           		   um.middlename, um.skype_name, um.date_closed
			from users_assignment ua, users_meta um
			where ua.user_meta_id = um.user_meta_id
GO

CREATE VIEW valid_users_vw AS select *
		  	from users
		  	where currentstatus NOT IN (3, 5, 6)
GO

CREATE VIEW user_appraisal_vw AS select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.userid as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
				   sda.userid as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
				   ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
				   aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
				   aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
				   atp.start_date as template_start_date, atp.end_date as template_end_date
			from user_appraisal ua
			inner join appraisal_template atp on atp.id = ua.appraisal_template_id
			left join valid_users_vw da on da.userid = ua.appraiser_id
			left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
			inner join appraisal_status ast on ast.id = ua.status_id
			left join appraisal_status_category ac on ac.id = ast.category_id
			left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140423-gl-4', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 100, '8:4f9666900b5b8d781d0b4feb8704e7ca', 'createIndex indexName=nuk_users_daid, tableName=users_assignment; dropView viewName=user_appraisal_vw; dropView viewName=valid_users_vw; dropView viewName=users; createView viewName=users; createView viewName=valid_users_vw; createView viewName=us...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140423-jw-1::james.wong
CREATE TABLE org_attribute (attribute_id nvarchar(15) NOT NULL, orgid nvarchar(15) NOT NULL, attribute_value_data nvarchar(2000), last_updater_uid nvarchar(85), last_updated datetime)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140423-jw-1', 'james.wong', 'nschema/101.changeLog.xml', GETDATE(), 101, '8:917f8d82774d05f10df9310fa637426b', 'createTable tableName=org_attribute', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140428-rc-4::ryan.chen
DROP VIEW user_appraisal_vw
GO

CREATE VIEW user_appraisal_vw AS select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.currentPID as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
				   sda.currentPID as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
				   ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
				   aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
				   aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
				   atp.start_date as template_start_date, atp.end_date as template_end_date
			from user_appraisal ua
			inner join appraisal_template atp on atp.id = ua.appraisal_template_id
			left join valid_users_vw da on da.userid = ua.appraiser_id
			left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
			inner join appraisal_status ast on ast.id = ua.status_id
			left join appraisal_status_category ac on ac.id = ast.category_id
			left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140428-rc-4', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 102, '8:b9b4d1b8bae4ff66a6bc0e6671a8090c', 'dropView viewName=user_appraisal_vw; createView viewName=user_appraisal_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140502-sn-1::sam.ng
ALTER TABLE tms_defaults ADD CONSTRAINT pk_tms_defaults PRIMARY KEY (default_level_group)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140502-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 103, '8:5e4189cf642dfef6c1606f7770cf1054', 'addPrimaryKey constraintName=pk_tms_defaults, tableName=tms_defaults', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140502-sn-2::sam.ng
ALTER TABLE ekpversion ADD CONSTRAINT pk_ekpversion PRIMARY KEY (dblevel)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140502-sn-2', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 104, '8:7113fd91b80d852066f32981fbeff1bf', 'addPrimaryKey constraintName=pk_ekpversion, tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140502-sn-3::sam.ng
ALTER TABLE ekp_audit_version ADD CONSTRAINT pk_ekp_audit_version PRIMARY KEY (dblevel)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140502-sn-3', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 105, '8:c6805337e4a090852cac27d63aa049c5', 'addPrimaryKey constraintName=pk_ekp_audit_version, tableName=ekp_audit_version', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140502-sn-4::sam.ng
DROP TABLE userGroup
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140502-sn-4', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 106, '8:9c9edbef969f0979a81a24e25838e537', 'dropTable tableName=userGroup', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140502-sn-5::sam.ng
DROP TABLE userGroupMember
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140502-sn-5', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 107, '8:1ad31c720f5cea4b4a5d38d3796f3442', 'dropTable tableName=userGroupMember', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140502-jw-1::james.wong
ALTER TABLE org_attribute ADD CONSTRAINT PK_ORG_ATTRIBUTE PRIMARY KEY (attribute_id, orgid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140502-jw-1', 'james.wong', 'nschema/101.changeLog.xml', GETDATE(), 108, '8:cb3dc5fe379ea91f3d659754ea1d02b3', 'addPrimaryKey constraintName=PK_ORG_ATTRIBUTE, tableName=org_attribute', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140507-dc-2::derek.chan
CREATE NONCLUSTERED INDEX nuk_email ON users_meta(email)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140507-dc-2', 'derek.chan', 'nschema/101.changeLog.xml', GETDATE(), 109, '8:bba49c70f693c136a4ec22556647487b', 'createIndex indexName=nuk_email, tableName=users_meta', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140512-ns-1::nicky.siu
DROP VIEW user_appraisal_vw
GO

DROP VIEW valid_users_vw
GO

DROP VIEW users
GO

DROP VIEW userinfo
GO

DROP VIEW userprefs
GO

DROP VIEW userstats
GO

ALTER TABLE users_meta ADD userattr1 nvarchar(1000)
GO

ALTER TABLE users_meta ADD userattr2 nvarchar(1000)
GO

ALTER TABLE users_meta ADD userattr3 nvarchar(1000)
GO

ALTER TABLE users_meta ADD userattr4 nvarchar(1000)
GO

ALTER TABLE users_meta ADD userattr5 nvarchar(1000)
GO

ALTER TABLE users_meta ADD userattr6 nvarchar(1000)
GO

ALTER TABLE users_meta ADD userattr7 nvarchar(1000)
GO

ALTER TABLE users_meta ADD userattr8 nvarchar(1000)
GO

UPDATE users_meta set userattr1 = (select MAX(ua.userattr1) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id),
			userattr2 = (select MAX(ua.userattr2) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id),
			userattr3 = (select MAX(ua.userattr3) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id),
			userattr4 = (select MAX(ua.userattr4) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id),
			userattr5 = (select MAX(ua.userattr5) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id),
			userattr6 = (select MAX(ua.userattr6) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id),
			userattr7 = (select MAX(ua.userattr7) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id),
			userattr8 = (select MAX(ua.userattr8) from users_assignment ua where ua.user_meta_id = users_meta.user_meta_id)
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr1'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr1
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr2'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr2
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr3'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr3
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr4'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr4
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr5'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr5
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr6'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr6
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr7'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr7
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE users_assignment DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'users_assignment') AND [c].[name] = N'userattr8'
EXEC sp_executesql @sql
GO

ALTER TABLE users_assignment DROP COLUMN userattr8
GO

CREATE VIEW users AS select ua.userid, ua.dappraiser, ua.level1id, ua.level2id, ua.level3id, ua.level4id, ua.level5id,
    		       ua.costcenter, ua.joindate, um.userattr1, um.userattr2, um.userattr3, um.userattr4, ua.userrole,
    		       ua.datecreated, ua.jobtitle, ua.UserOption1, ua.UserOption2, ua.UserOption3, ua.profile, ua.managername,
    		       ua.manageremail, ua.departmentname, ua.locationcode, ua.hrmanagername, ua.hrmanageremail,
    		       ua.line_of_business, ua.skills_assessment_enabled, um.userattr5, um.userattr6, um.userattr7,
    		       um.userattr8, ua.leaf_orgid, ua.creditbalance, ua.hr_tag, ua.departmentid, ua.costcentername,
    		       ua.creator_uid, ua.last_updated, ua.last_updater_uid, ua.offline_player_eval_days, ua.offline_player_eval_start_date,
    		       ua.token_account_id, ua.domain_id, ua.user_meta_id, ua.assignment_ref_code, ua.dappraiserid,
    		       um.password, um.currentstatus, um.currentPID, um.familyname,
           		   um.givenname, um.othername, um.personaltitle, um.email, um.employeeno,
           		   um.externalAuthentication, um.autoDelete, um.deleteDate, um.dateofbirth,
           		   um.gender, um.expirationdate, um.nationality, um.dietary_constraints,
           		   um.middlename, um.skype_name, um.date_closed
			from users_assignment ua, users_meta um
			where ua.user_meta_id = um.user_meta_id
GO

CREATE VIEW valid_users_vw AS select *
		  	from users
		  	where currentstatus NOT IN (3, 5, 6)
GO

CREATE VIEW user_appraisal_vw AS select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.userid as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
				   sda.userid as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
				   ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
				   aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
				   aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
				   atp.start_date as template_start_date, atp.end_date as template_end_date
			from user_appraisal ua
			inner join appraisal_template atp on atp.id = ua.appraisal_template_id
			left join valid_users_vw da on da.userid = ua.appraiser_id
			left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
			inner join appraisal_status ast on ast.id = ua.status_id
			left join appraisal_status_category ac on ac.id = ast.category_id
			left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

CREATE VIEW userinfo AS select userinfo_assignment.userid, userinfo_assignment.picture,
	               users_meta.b_companyname, users_meta.b_address1, users_meta.b_address2,users_meta.b_city,users_meta.b_province_state,
	               users_meta.b_country,users_meta.b_phone,users_meta.b_mobile,users_meta.b_postalcode,users_meta.fax, 
	               userinfo_assignment.e_country, userinfo_assignment.employeestatus, userinfo_assignment.employeeid,
	               userinfo_assignment.personalid, userinfo_assignment.taxid, userinfo_assignment.returndate, 
	               userinfo_assignment.regular_temp, userinfo_assignment.full_part_time,
	               userinfo_assignment.contractor, userinfo_assignment.language, userinfo_assignment.business_unit_code, 
	               userinfo_assignment.business_unit_desc, userinfo_assignment.joblocation, userinfo_assignment.taxlocation, 
	               users_meta.reloc_willingness, users_meta.reloc_desired_location, users_meta.reloc_financial_assist,
	               users_meta.reloc_reason, users_meta.resume, users_meta.resume_byte_size,
	               users_meta.resume_uploader_uid, users_meta.resume_upload_date
	        from userinfo_assignment
	        JOIN users_assignment on userinfo_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

CREATE VIEW userprefs AS select userprefs_assignment.userid, 
	               userprefs_assignment.contentServer,
	               userprefs_assignment.ui, 
	               users_meta.languagepref,
	               userprefs_assignment.autoMailList,
	               userprefs_assignment.autoRegMsg,
	               users_meta.useExternalMail,
	               userprefs_assignment.initialURL,
	               userprefs_assignment.resultsPerPage,
	               userprefs_assignment.catalogid,
	               userprefs_assignment.userPref1, 
	               userprefs_assignment.userPref2, 
	               userprefs_assignment.time_zone, 
	               users_meta.email_forward_type,
	               users_meta.forward_to_email, 
	               userprefs_assignment.welcome_email_senddate
	        from userprefs_assignment
	        JOIN users_assignment on userprefs_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

CREATE VIEW userstats AS select userstats_assignment.userid, 
	               users_meta.lastlogon,
	               users_meta.lastpwchange, 
	               users_meta.nlogons,
	               users_meta.nattempts,
	               userstats_assignment.nmsgspending,
	               users_meta.elapsedloginseconds,
	               users_meta.nlogouts,
	               userstats_assignment.lastAutoEnrollUpdate,
	               users_meta.first_login,
	               users_meta.firstlogonfailure, 
	               users_meta.lastlogonfailure, 
	               users_meta.nlogonfailure, 
	               userstats_assignment.last_catalog_id,
	               users_meta.requirepwchange, 
	               users_meta.lastactivedate, 
	               users_meta.mekp_activation, 
	               users_meta.mekp_last_sync,
	               userstats_assignment.last_job_auto_assign_update,
	               userstats_assignment.last_crs_offline_auto_enroll, 
	               userstats_assignment.last_job_offline_auto_assign, 
	               users_meta.terms_of_use_accept_date
	        from userstats_assignment
	        JOIN users_assignment on userstats_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140512-ns-1', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 110, '8:eab8da8772cc0661bcd0a6959af6b7ed', 'dropView viewName=user_appraisal_vw; dropView viewName=valid_users_vw; dropView viewName=users; dropView viewName=userinfo; dropView viewName=userprefs; dropView viewName=userstats; addColumn tableName=users_meta; addColumn tableName=users_meta; a...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140512-sm-1::simon.mok
CREATE TABLE user_extension_meta (user_meta_id nvarchar(85) NOT NULL, extension_no smallint NOT NULL, extension_value_data nvarchar(85))
GO

ALTER TABLE user_extension_meta ADD CONSTRAINT pk_user_extension_meta PRIMARY KEY (user_meta_id, extension_no)
GO

INSERT INTO user_extension_meta(user_meta_id, extension_no, extension_value_data)
				SELECT b.user_meta_id, extension_no, max(extension_value_data)
					FROM user_extension a
					JOIN users_assignment b
					ON a.userid = b.userid
					GROUP BY b.user_meta_id, extension_no
GO

DROP TABLE user_extension
GO

CREATE VIEW user_extension AS SELECT userid, extension_no, extension_value_data
			FROM user_extension_meta
			JOIN users_assignment
			ON user_extension_meta.user_meta_id = users_assignment.user_meta_id
GO

ALTER TABLE user_extension_meta ADD CONSTRAINT FK_user_extension_meta FOREIGN KEY (user_meta_id) REFERENCES users_meta (user_meta_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140512-sm-1', 'simon.mok', 'nschema/101.changeLog.xml', GETDATE(), 111, '8:9345feaf118809aab02dfbe2da525349', 'createTable tableName=user_extension_meta; addPrimaryKey constraintName=pk_user_extension_meta, tableName=user_extension_meta; sql; dropTable tableName=user_extension; createView viewName=user_extension; addForeignKeyConstraint baseTableName=user_...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140512-sg-01::shravan.gurrala
IF NOT EXISTS
            (  SELECT [name]
            FROM sys.tables
            WHERE [name] = 'stats_overall'
            )
            CREATE TABLE [stats_overall] ([snapshotid] NVARCHAR(85) NOT NULL, [reference_code] NVARCHAR(255), [title] NVARCHAR(255), [nts_release] NVARCHAR(50) NOT NULL, [db_performance_index] FLOAT NOT NULL, [max_connections] INT NOT NULL, [max_concurrent_logins] INT NOT NULL, [total_logins] INT NOT NULL, [total_users] INT NOT NULL, [n_system_errors] INT NOT NULL, [elapsed_time_sec] INT NOT NULL, [jvm_version] NVARCHAR(85), [max_config_memory_mb] INT NOT NULL, [current_memory_alloc_mb] INT NOT NULL, [total_free_memory_mb] INT NOT NULL, [creator_pid] NVARCHAR(85) NOT NULL, [datecreated] datetime NOT NULL CONSTRAINT [PK_STATS_OVERALL] PRIMARY KEY ([snapshotid]))
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140512-sg-01', 'shravan.gurrala', 'nschema/101.changeLog.xml', GETDATE(), 112, '8:0e3c5d42e1941b3bfb3ae96e4ca646df', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140512-sg-02::shravan.gurrala
IF NOT EXISTS
            (  SELECT [name]
            FROM sys.tables
            WHERE [name] = 'stats_tx'
            )
            CREATE TABLE [stats_tx] ([snapshotid] NVARCHAR(85) NOT NULL, [tx] NVARCHAR(85) NOT NULL, [n_invocations] INT NOT NULL, [invocations_percent] FLOAT NOT NULL, [total_exec_time_sec] FLOAT NOT NULL, [avg_exec_time_sec] FLOAT NOT NULL, [percent_3] FLOAT NOT NULL, [percent_3_to_6] FLOAT NOT NULL, [percent_6_to_10] FLOAT NOT NULL, [percent_10] FLOAT NOT NULL, [max_exec_sec] FLOAT NOT NULL CONSTRAINT [PK_STATS_TX] PRIMARY KEY ([snapshotid], [tx]))
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140512-sg-02', 'shravan.gurrala', 'nschema/101.changeLog.xml', GETDATE(), 113, '8:40bd4aa8b40f4777bc93ebf1229793ac', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140513-jw-1::james.wong
ALTER TABLE currcourses ADD weighting float(53) CONSTRAINT DF_currcourses_weighting DEFAULT 0 NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140513-jw-1', 'james.wong', 'nschema/101.changeLog.xml', GETDATE(), 114, '8:d3e40ed0c0bdd5168184fcde9eb3cb5e', 'addColumn tableName=currcourses', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140513-jw-2::james.wong
ALTER TABLE sessiondetail ADD passing_score float(53) CONSTRAINT DF_sessiondetail_passing_score DEFAULT 0 NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140513-jw-2', 'james.wong', 'nschema/101.changeLog.xml', GETDATE(), 115, '8:07b8db0214dc38cd00887aad3c5e6b42', 'addColumn tableName=sessiondetail', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-rc-01::ryan.chen
ALTER TABLE batchreport ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-rc-01', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 116, '8:a820443437e8e788705bb7a6a4e4ae81', 'addColumn tableName=batchreport', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-rc-03::ryan.chen
DECLARE @counter nvarchar(85) 
   				SET @counter = '0' 
   				UPDATE batchreport 
   				SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-rc-03', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 117, '8:0adf238e8e124753148de7361b837844', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-rc-06::ryan.chen
ALTER TABLE batchreport ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE batchreport ADD CONSTRAINT pk_batchreport PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-rc-06', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 118, '8:5845144b4ffad54f19d4b34cd2badc6c', 'addNotNullConstraint columnName=id, tableName=batchreport; addPrimaryKey constraintName=pk_batchreport, tableName=batchreport', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140514-dr-1::dreidel.regala
ALTER TABLE testinstance ADD next_attempt_date datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140514-dr-1', 'dreidel.regala', 'nschema/101.changeLog.xml', GETDATE(), 119, '8:9d81722d4587a716189afc7548656f83', 'addColumn tableName=testinstance', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-mc-1::mike.chung
ALTER TABLE task_reminder_log ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-mc-1', 'mike.chung', 'nschema/101.changeLog.xml', GETDATE(), 120, '8:9b0d644cb9a797c9edba2fe72fd213c3', 'addColumn tableName=task_reminder_log', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-mc-2::mike.chung
DECLARE @counter nvarchar(85)
            SET @counter = '0'
            UPDATE task_reminder_log
            SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-mc-2', 'mike.chung', 'nschema/101.changeLog.xml', GETDATE(), 121, '8:1751b7478ff2a9cd6d3492072d624350', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-mc-6::mike.chung
ALTER TABLE task_reminder_log ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE task_reminder_log ADD CONSTRAINT pk_task_reminder_log PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-mc-6', 'mike.chung', 'nschema/101.changeLog.xml', GETDATE(), 122, '8:704119d43625cdcf9057ef91e70e2096', 'addNotNullConstraint columnName=id, tableName=task_reminder_log; addPrimaryKey constraintName=pk_task_reminder_log, tableName=task_reminder_log', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-ns-1::nicky.siu
ALTER TABLE content_security ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-ns-1', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 123, '8:3c2219b1f03e311e8e95ecfcee5284a0', 'addColumn tableName=content_security', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-ns-3::nicky.siu
DECLARE @counter nvarchar(85)
			SET @counter = '0'
			UPDATE content_security 
			SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-ns-3', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 124, '8:acfe6c8f71a3e1d1cac7a181110d0b67', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-ns-6::nicky.siu
ALTER TABLE content_security ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE content_security ADD CONSTRAINT pk_content_security PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-ns-6', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 125, '8:341b1a3c85b0ddc8f06c722f624d60a7', 'addNotNullConstraint columnName=id, tableName=content_security; addPrimaryKey constraintName=pk_content_security, tableName=content_security', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-gl-1::garbo.lim
ALTER TABLE accessAuditTrail ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 126, '8:c6189b7dcaaa6a5c65cbc65222f55b7c', 'addColumn tableName=accessAuditTrail', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-gl-2::garbo.lim
DECLARE @counter nvarchar(85)
			SET @counter = '0'
			UPDATE accessAuditTrail
			SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-gl-2', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 127, '8:e2534fb0b0cce6a4b9df956222f1cda5', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-gl-6::garbo.lim
ALTER TABLE accessAuditTrail ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE accessAuditTrail ADD CONSTRAINT pk_accessAuditTrail PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-gl-6', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 128, '8:662addca9005b887180350312bd1cf0d', 'addNotNullConstraint columnName=id, tableName=accessAuditTrail; addPrimaryKey constraintName=pk_accessAuditTrail, tableName=accessAuditTrail', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140515-dc-1::derek.chan
DROP TABLE forumaccess
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140515-dc-1', 'derek.chan', 'nschema/101.changeLog.xml', GETDATE(), 129, '8:c6ec99f076e1738be303d6a477ce4a44', 'dropTable tableName=forumaccess', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140516-ns-1::nicky.siu
ALTER TABLE user_comp_msg_tracking ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140516-ns-1', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 130, '8:c1e831f7c50f3b5979f3520ad6013659', 'addColumn tableName=user_comp_msg_tracking', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140516-ns-3::nicky.siu
DECLARE @counter nvarchar(85)
			SET @counter = '0'
			UPDATE user_comp_msg_tracking 
			SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140516-ns-3', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 131, '8:fceb3359a832b90489cba83d2c36cdd7', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140516-ns-6::nicky.siu
ALTER TABLE user_comp_msg_tracking ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE user_comp_msg_tracking ADD CONSTRAINT pk_user_comp_msg_tracking PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140516-ns-6', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 132, '8:c912d8049a9e1ffe1965535bced189ef', 'addNotNullConstraint columnName=id, tableName=user_comp_msg_tracking; addPrimaryKey constraintName=pk_user_comp_msg_tracking, tableName=user_comp_msg_tracking', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140516-rc-1::ryan.chen
ALTER TABLE code_displayableJobCode ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140516-rc-1', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 133, '8:da7747d713b47981388c41137bdbf5ff', 'addColumn tableName=code_displayableJobCode', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140516-rc-3::ryan.chen
DECLARE @counter nvarchar(85) 
   				SET @counter = '0' 
   				UPDATE code_displayableJobCode 
   				SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140516-rc-3', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 134, '8:2b689a411460de6dd81e2ad33c6876d2', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140516-rc-6::ryan.chen
ALTER TABLE code_displayableJobCode ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE code_displayableJobCode ADD CONSTRAINT pk_displayable_jobcode PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140516-rc-6', 'ryan.chen', 'nschema/101.changeLog.xml', GETDATE(), 135, '8:eec3c6c390e60b3471c5c3fe0e6bc89c', 'addNotNullConstraint columnName=id, tableName=code_displayableJobCode; addPrimaryKey constraintName=pk_displayable_jobcode, tableName=code_displayableJobCode', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140516-gl-1::garbo.lim
update users_assignment set assignment_ref_code = assignment_ref_code + '-1'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140516-gl-1', 'garbo.lim', 'nschema/101.changeLog.xml', GETDATE(), 136, '8:3b862052d445e19e71cdce8993ea50f6', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140519-js-1::janice.sung
ALTER TABLE users_meta ADD other_info nvarchar (max)
GO

UPDATE users_meta SET other_info = (SELECT userbio.other_info FROM 
   			 	users_assignment ua JOIN userbio ON userbio.userid = ua.userid 
   			 	WHERE ua.user_meta_id = users_meta.user_meta_id)
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE userbio DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'userbio') AND [c].[name] = N'other_info'
EXEC sp_executesql @sql
GO

ALTER TABLE userbio DROP COLUMN other_info
GO

exec sp_rename 'userbio', 'userbio_assignment'
GO

CREATE VIEW userbio AS SELECT userbio_assignment.userid, userbio_assignment.viewingConstraint, 
	               userbio_assignment.bio, userbio_assignment.bio_work_experience, userbio_assignment.bio_edu_background, 
	               userbio_assignment.bio_interests, userbio_assignment.public_activity, users_meta.other_info
	        FROM users_assignment
	        JOIN userbio_assignment ON userbio_assignment.userid = users_assignment.userid
	        JOIN users_meta ON users_assignment.user_meta_id = users_meta.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140519-js-1', 'janice.sung', 'nschema/101.changeLog.xml', GETDATE(), 137, '8:42adb83743a5e65952687ef8f716d810', 'addColumn tableName=users_meta; sql; dropColumn columnName=other_info, tableName=userbio; renameTable newTableName=userbio_assignment, oldTableName=userbio; createView viewName=userbio', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140523-mm-1::marco.mate
ALTER TABLE transcript ADD weightedscore char(20)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140523-mm-1', 'marco.mate', 'nschema/101.changeLog.xml', GETDATE(), 138, '8:328e6b12f93386ab21e59af14d7f3721', 'addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140523-mm-2::marco.mate
DROP VIEW cschedule_vw
GO

DROP VIEW valid_transcript_vw
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE transcript DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'transcript') AND [c].[name] = N'weightedscore'
EXEC sp_executesql @sql
GO

ALTER TABLE transcript DROP COLUMN weightedscore
GO

ALTER TABLE transcript ADD weightedscore float(53)
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 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140523-mm-2', 'marco.mate', 'nschema/101.changeLog.xml', GETDATE(), 139, '8:93dae0482fc973b520a23b6020e3bb86', 'dropView viewName=cschedule_vw; dropView viewName=valid_transcript_vw; dropColumn columnName=weightedscore, tableName=transcript; addColumn tableName=transcript; createView viewName=cschedule_vw; createView viewName=valid_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140523-mm-3::marco.mate
ALTER TABLE transcript ADD weighting float(53)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140523-mm-3', 'marco.mate', 'nschema/101.changeLog.xml', GETDATE(), 140, '8:fbfde72239e98e556b10647538b66efc', 'addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140527-mm-4::marco.mate
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,
		    weighting,
		    weightedscore
		  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,
		    b.weighting,
		    b.weightedscore
		  FROM transcript a, transcript b
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140527-mm-4', 'marco.mate', 'nschema/101.changeLog.xml', GETDATE(), 141, '8:35e6723bba63619b60607a2a122d51a2', 'dropView viewName=valid_transcript_vw; createView viewName=valid_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140604-sn-1::sam.ng
CREATE TABLE autoassign_org_attr (assign_object_id nvarchar(15) NOT NULL, learning_id nvarchar(85) NOT NULL, attribute_id nvarchar(15) NOT NULL, attribute_value_data nvarchar(2000) NOT NULL, autoassign_source char(1) NOT NULL)
GO

ALTER TABLE autoassign_org_attr ADD CONSTRAINT PK_AUTOASSIGN_ORG_ATTR PRIMARY KEY (assign_object_id, learning_id, attribute_id, autoassign_source)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140604-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 142, '8:c3ec937cf497f43127b6bd4f5d34dfdc', 'createTable tableName=autoassign_org_attr; addPrimaryKey constraintName=PK_AUTOASSIGN_ORG_ATTR, tableName=autoassign_org_attr', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140604-ns-1::nicky.siu
DROP VIEW user_appraisal_vw
GO

DROP VIEW valid_users_vw
GO

DROP VIEW users
GO

ALTER TABLE users_assignment ADD assignment_end_date datetime
GO

CREATE VIEW users AS select ua.userid, ua.dappraiser, ua.level1id, ua.level2id, ua.level3id, ua.level4id, ua.level5id,
    		       ua.costcenter, ua.joindate, um.userattr1, um.userattr2, um.userattr3, um.userattr4, ua.userrole,
    		       ua.datecreated, ua.jobtitle, ua.UserOption1, ua.UserOption2, ua.UserOption3, ua.profile, ua.managername,
    		       ua.manageremail, ua.departmentname, ua.locationcode, ua.hrmanagername, ua.hrmanageremail,
    		       ua.line_of_business, ua.skills_assessment_enabled, um.userattr5, um.userattr6, um.userattr7,
    		       um.userattr8, ua.leaf_orgid, ua.creditbalance, ua.hr_tag, ua.departmentid, ua.costcentername,
    		       ua.creator_uid, ua.last_updated, ua.last_updater_uid, ua.offline_player_eval_days, ua.offline_player_eval_start_date,
    		       ua.token_account_id, ua.domain_id, ua.user_meta_id, ua.assignment_ref_code, ua.dappraiserid,
    		       um.password, um.currentstatus, um.currentPID, um.familyname,
           		   um.givenname, um.othername, um.personaltitle, um.email, um.employeeno,
           		   um.externalAuthentication, um.autoDelete, um.deleteDate, um.dateofbirth,
           		   um.gender, um.expirationdate, um.nationality, um.dietary_constraints,
           		   um.middlename, um.skype_name, um.date_closed, ua.assignment_end_date
			from users_assignment ua, users_meta um
			where ua.user_meta_id = um.user_meta_id
GO

CREATE VIEW valid_users_vw AS select *
		  	from users
		  	where currentstatus NOT IN (3, 5, 6)
GO

CREATE VIEW user_appraisal_vw AS select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.userid as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
				   sda.userid as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
				   ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
				   aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
				   aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
				   atp.start_date as template_start_date, atp.end_date as template_end_date
			from user_appraisal ua
			inner join appraisal_template atp on atp.id = ua.appraisal_template_id
			left join valid_users_vw da on da.userid = ua.appraiser_id
			left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
			inner join appraisal_status ast on ast.id = ua.status_id
			left join appraisal_status_category ac on ac.id = ast.category_id
			left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140604-ns-1', 'nicky.siu', 'nschema/101.changeLog.xml', GETDATE(), 143, '8:2776cccad471a84107a6b7d025f29b42', 'dropView viewName=user_appraisal_vw; dropView viewName=valid_users_vw; dropView viewName=users; addColumn tableName=users_assignment; createView viewName=users; createView viewName=valid_users_vw; createView viewName=user_appraisal_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140605-kh-1::kelvin.hung
ALTER TABLE permissions ADD identityvalue nvarchar(2000)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140605-kh-1', 'kelvin.hung', 'nschema/101.changeLog.xml', GETDATE(), 144, '8:e83452d967114aafd4479bf493ad6e1b', 'addColumn tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140605-jw-1::joe.wong
ALTER TABLE users_meta ALTER COLUMN useExternalMail char(1) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140605-jw-1', 'joe.wong', 'nschema/101.changeLog.xml', GETDATE(), 145, '8:3b4c83520560adfe1d2fd962b82e721b', 'dropNotNullConstraint columnName=useExternalMail, tableName=users_meta', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140609-sn-1::sam.ng
ALTER TABLE autoassign_org_attr DROP CONSTRAINT PK_AUTOASSIGN_ORG_ATTR
GO

ALTER TABLE autoassign_org_attr ADD CONSTRAINT PK_AUTOASSIGN_ORG_ATTR PRIMARY KEY (autoassign_source, attribute_id, assign_object_id, learning_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140609-sn-1', 'sam.ng', 'nschema/101.changeLog.xml', GETDATE(), 146, '8:e9800ea192816182e6677c39ba2d334a', 'dropPrimaryKey constraintName=PK_AUTOASSIGN_ORG_ATTR, tableName=autoassign_org_attr; addPrimaryKey constraintName=PK_AUTOASSIGN_ORG_ATTR, tableName=autoassign_org_attr', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140611-kh-1::kelvin.hung
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE permissions DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'permissions') AND [c].[name] = N'identityvalue'
EXEC sp_executesql @sql
GO

ALTER TABLE permissions DROP COLUMN identityvalue
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140611-kh-1', 'kelvin.hung', 'nschema/101.changeLog.xml', GETDATE(), 147, '8:07424ac6ffa3858303b1c06b92408e69', 'dropColumn columnName=identityvalue, tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140611-kh-2::kelvin.hung
DROP INDEX idx_ekpfilepermissionsusers ON permissions
GO

DROP INDEX idx_ekpfilepermissionsusers_us ON permissions
GO

DROP INDEX nuk_objtype_perm_permissions ON permissions
GO

DROP INDEX idx_ekpfilepermissionsobject ON permissions
GO

ALTER TABLE permissions DROP CONSTRAINT PK_PERMISSION
GO

ALTER TABLE permissions ALTER COLUMN identityid nvarchar(85) NULL
GO

ALTER TABLE permissions ALTER COLUMN identityid nvarchar(200)
GO

ALTER TABLE permissions ALTER COLUMN identityid nvarchar(200) NOT NULL
GO

ALTER TABLE permissions ADD CONSTRAINT PK_PERMISSION PRIMARY KEY (objectid, objecttype, identityid, permissioncode, identitytype)
GO

CREATE NONCLUSTERED INDEX idx_ekpfilepermissionsusers ON permissions(objectid, identityid, objecttype)
GO

CREATE NONCLUSTERED INDEX idx_ekpfilepermissionsusers_us ON permissions(identityid)
GO

CREATE NONCLUSTERED INDEX nuk_objtype_perm_permissions ON permissions(objecttype, permissioncode)
GO

CREATE NONCLUSTERED INDEX idx_ekpfilepermissionsobject ON permissions(objectid, objecttype)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140611-kh-2', 'kelvin.hung', 'nschema/101.changeLog.xml', GETDATE(), 148, '8:92e82a8aaec0ad3ba1d049c456f400a4', 'dropIndex indexName=idx_ekpfilepermissionsusers, tableName=permissions; dropIndex indexName=idx_ekpfilepermissionsusers_us, tableName=permissions; dropIndex indexName=nuk_objtype_perm_permissions, tableName=permissions; dropIndex indexName=idx_ekp...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140611-kh-4::kelvin.hung
DROP TABLE autoassign_org_attr
GO

CREATE TABLE autoassign_org_attr (assign_object_id nvarchar(200) NOT NULL, learning_id nvarchar(85) NOT NULL, attribute_id nvarchar(15) NOT NULL, attribute_value_data nvarchar(2000) NOT NULL, autoassign_source char(1) NOT NULL)
GO

ALTER TABLE autoassign_org_attr ADD CONSTRAINT PK_AUTOASSIGN_ORG_ATTR PRIMARY KEY (autoassign_source, attribute_id, assign_object_id, learning_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140611-kh-4', 'kelvin.hung', 'nschema/101.changeLog.xml', GETDATE(), 149, '8:ab0db4d84d61fba3d9d230aff0032a22', 'dropTable tableName=autoassign_org_attr; createTable tableName=autoassign_org_attr; addPrimaryKey constraintName=PK_AUTOASSIGN_ORG_ATTR, tableName=autoassign_org_attr', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140611-jw-1::joe.wong
ALTER TABLE user_terms_of_use DROP CONSTRAINT FK_TERMS_OF_USE_USER_ID
GO

ALTER TABLE user_terms_of_use DROP CONSTRAINT PK_USER_TERMS_OF_USE
GO

exec sp_rename 'user_terms_of_use.user_id', 'user_meta_id'
GO

ALTER TABLE user_terms_of_use ADD CONSTRAINT PK_USER_TERMS_OF_USE PRIMARY KEY (user_meta_id, terms_id)
GO

ALTER TABLE user_terms_of_use ADD CONSTRAINT FK_TERMS_OF_USE_USER_ID FOREIGN KEY (user_meta_id) REFERENCES users_meta (user_meta_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140611-jw-1', 'joe.wong', 'nschema/101.changeLog.xml', GETDATE(), 150, '8:2109067d56129aac87f2878c25993918', 'dropForeignKeyConstraint baseTableName=user_terms_of_use, constraintName=FK_TERMS_OF_USE_USER_ID; dropPrimaryKey constraintName=PK_USER_TERMS_OF_USE, tableName=user_terms_of_use; renameColumn newColumnName=user_meta_id, oldColumnName=user_id, tabl...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140612-mc-1::mike.chung
ALTER TABLE schedule_report ALTER COLUMN parameters nvarchar (max)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140612-mc-1', 'mike.chung', 'nschema/101.changeLog.xml', GETDATE(), 151, '8:b79ee7c63fe625c61057045b7a18fce4', 'varcharToText columnName=parameters, tableName=schedule_report', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140613-rr-1::ray.ruff
CREATE TABLE language_custom_string (locale_file_id nvarchar(85) NOT NULL, string_id nvarchar(85) NOT NULL, string_value nvarchar(1000) NOT NULL)
GO

ALTER TABLE language_custom_string ADD CONSTRAINT PK_LANG_CUST_STR PRIMARY KEY (locale_file_id, string_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140613-rr-1', 'ray.ruff', 'nschema/101.changeLog.xml', GETDATE(), 152, '8:91b67c42e1ce788e19e01dc7a67c5baa', 'createTable tableName=language_custom_string; addPrimaryKey constraintName=PK_LANG_CUST_STR, tableName=language_custom_string', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140614-rr-2::ray.ruff
ALTER TABLE language_custom_string ADD last_updater_uid nvarchar(85)
GO

ALTER TABLE language_custom_string ADD last_updated datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140614-rr-2', 'ray.ruff', 'nschema/101.changeLog.xml', GETDATE(), 153, '8:ff2edf375e4647b851b2d84b733cc7db', 'addColumn tableName=language_custom_string', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/101.changeLog.xml::20140624-mc-1::mike.chung
INSERT INTO permissions_operator (objectid, objecttype, permissioncode, operator) VALUES ('R131', 'RT', 1, 'OR')
GO

INSERT INTO permissions (objectid, objecttype, permissioncode, identityid, identitytype) VALUES ('R131', 'RT', 1, '*ROOT*', 'O')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140624-mc-1', 'mike.chung', 'nschema/101.changeLog.xml', GETDATE(), 154, '8:6b4c5dfff27cb46665e9799772ec9ce4', 'insert tableName=permissions_operator; insert tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::16Jun2014-sg-1::shravan
UPDATE ekpversion SET dblevel = 102, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('16Jun2014-sg-1', 'shravan', 'nschema/102.changeLog.xml', GETDATE(), 155, '8:3de4c0489ecfc4f09620f8d1f54501e2', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140710-gl-1::garbo.lim
update code_orgstructure 
		     set esig_course_csv_loader = (select case when esig_course_csv_loader = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		   	 , esig_program_csv_loader = (select case when esig_program_csv_loader = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_course_withdraw = (select case when esig_course_withdraw = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_course_modify = (select case when esig_course_modify = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_course_launch = (select case when esig_course_launch = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_course_import = (select case when esig_course_import = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_course_finish = (select case when esig_course_finish = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_exam_launch = (select case when esig_exam_launch = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_question_modify = (select case when esig_question_modify = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_question_import = (select case when esig_question_import = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_grading_test_answer = (select case when esig_grading_test_answer = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_reviewer_modify_trans = (select case when esig_reviewer_modify_trans = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_editor_modify_trans = (select case when esig_editor_modify_trans = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_modify_attendance = (select case when esig_modify_attendance = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_wizard_modify_trans = (select case when esig_wizard_modify_trans = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_modify_ext_training = (select case when esig_modify_ext_training = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_modify_cert = (select case when esig_modify_cert = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
		     , esig_award_revert_cert = (select case when esig_award_revert_cert = 'Y' then 'A' else 'O' end 
		     							   from (select * from code_orgstructure where orgid = '*ROOT*') o)
			 where orgid <> '*ROOT*'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140710-gl-1', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 156, '8:879c4685a78a422aa08c733001bf7220', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140715-js-1::janice.sung
CREATE TABLE checklist_item (itemid nvarchar(15) NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15) NOT NULL, description nvarchar(255) NOT NULL, deadline datetime, creator_id nvarchar(85), completion_status char(1) CONSTRAINT DF_checklist_item_completion_status DEFAULT 'I' NOT NULL, date_completed datetime, last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE checklist_item ADD CONSTRAINT PK_CHECKLIST_ITEM PRIMARY KEY (itemid)
GO

ALTER TABLE checklist_item ADD CONSTRAINT FK_CHECKLIST_ITEM_LEARNINGID FOREIGN KEY (learningid) REFERENCES learningObject (learningid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140715-js-1', 'janice.sung', 'nschema/102.changeLog.xml', GETDATE(), 157, '8:75328bfca06b6bebc105c45a60f22c55', 'createTable tableName=checklist_item; addPrimaryKey constraintName=PK_CHECKLIST_ITEM, tableName=checklist_item; addForeignKeyConstraint baseTableName=checklist_item, constraintName=FK_CHECKLIST_ITEM_LEARNINGID, referencedTableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140715-sn-1::sam.ng
ALTER TABLE personal_event ADD is_private char(1) CONSTRAINT DF_personal_event_is_private DEFAULT 'N' NOT NULL
GO

ALTER TABLE personal_event ADD description nvarchar (max)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140715-sn-1', 'sam.ng', 'nschema/102.changeLog.xml', GETDATE(), 158, '8:653954190c19116f68a0991d7d9d4ef1', 'addColumn tableName=personal_event', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140715-sn-2::sam.ng
ALTER TABLE reminders ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140715-sn-2', 'sam.ng', 'nschema/102.changeLog.xml', GETDATE(), 159, '8:6788d870deb0f62e4e86f494cc6fb38f', 'addColumn tableName=reminders', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140715-sn-4::sam.ng
DECLARE @counter nvarchar(85)
			SET @counter = '0'
			UPDATE reminders 
			SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO personal_event 
			SELECT id, userid,
				   case when LEN(notetext) <= 16 then notetext else substring(notetext, 1, 16) + '...' end title,
				   postingdate, postingdate, 'Y', 'Y', notetext 
			FROM reminders
GO

DROP TABLE reminders
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140715-sn-4', 'sam.ng', 'nschema/102.changeLog.xml', GETDATE(), 160, '8:d5eae0a38eaa346859e6d5a3fde56e2c', 'sql; sql; dropTable tableName=reminders', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140716-sn-1::sam.ng
UPDATE roleSecurity SET securitylevel = 
					(select case when r.securitylevel = 1 then 2 else 1 end from 
						(select * from roleSecurity where feature = 82) r where r.userrole = roleSecurity.userrole) 
			WHERE feature = 19 and securitylevel = 1
GO

DELETE FROM roleSecurity WHERE feature = 82
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140716-sn-1', 'sam.ng', 'nschema/102.changeLog.xml', GETDATE(), 161, '8:3ce6b361fd055812d235941560784d69', 'sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140729-gl-1::garbo.lim
CREATE TABLE equivalency_rules (id nvarchar(85) NOT NULL, substitute_id nvarchar(85) NOT NULL, target_id nvarchar(85) NOT NULL, create_date datetime NOT NULL, created_by nvarchar(85) NOT NULL)
GO

ALTER TABLE equivalency_rules ADD CONSTRAINT PK_EQUIVALENCY_RULES PRIMARY KEY (id)
GO

ALTER TABLE equivalency_rules ADD CONSTRAINT FK_EQ_RULES_SUBSTITUTE_LID FOREIGN KEY (substitute_id) REFERENCES learningObject (learningid)
GO

ALTER TABLE equivalency_rules ADD CONSTRAINT FK_EQ_RULES_TARGET_LID FOREIGN KEY (target_id) REFERENCES learningObject (learningid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140729-gl-1', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 162, '8:ccfff5aa2dc166efb2b6a4018afe400a', 'createTable tableName=equivalency_rules; addPrimaryKey constraintName=PK_EQUIVALENCY_RULES, tableName=equivalency_rules; addForeignKeyConstraint baseTableName=equivalency_rules, constraintName=FK_EQ_RULES_SUBSTITUTE_LID, referencedTableName=learni...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140729-gl-2::garbo.lim
CREATE NONCLUSTERED INDEX nuk_eq_rules_sub ON equivalency_rules(substitute_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140729-gl-2', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 163, '8:c1c52e7457e9e30ee567edede34de508', 'createIndex indexName=nuk_eq_rules_sub, tableName=equivalency_rules', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140729-js-2::janice.sung
UPDATE logical_domain_config SET config_value = 'SAMEORIGIN' 
			WHERE config_key = 'security.IFrameProtection' AND CAST(config_value AS NVARCHAR(4)) = 'DENY'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140729-js-2', 'janice.sung', 'nschema/102.changeLog.xml', GETDATE(), 164, '8:807577673f1b8be115efbccd5f7d9ec0', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140804-dc-1::derek.chan
insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('1','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('1','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('2','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('2','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('3','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('3','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('4','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('4','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('5','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('5','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('6','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('6','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('7','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('7','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('10','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('10','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('11','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('11','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('12','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('12','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('13','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('13','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('14','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('14','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('15','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('15','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('16','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('16','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('17','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('17','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('18','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('18','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('19','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('19','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('20','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('20','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('21','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('21','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('22','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('22','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('23','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('23','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('24','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('24','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('25','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('25','TSS',2,'*ROOT*','O')
GO

insert into permissions_operator (objectid, objecttype, permissioncode, operator) values ('26','TSS',2,'OR')
GO

insert into permissions (objectid, objecttype, permissioncode, identityid, identitytype) values ('26','TSS',2,'*ROOT*','O')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140804-dc-1', 'derek.chan', 'nschema/102.changeLog.xml', GETDATE(), 165, '8:63e5866d927032b248f811e631baa72c', 'sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140805-sm-1::simon.mok
ALTER TABLE permissions ADD operator nvarchar(3)
GO

UPDATE permissions
			SET operator = (SELECT permissions_operator.operator FROM permissions_operator WHERE
				permissions.objecttype = permissions_operator.objecttype
				AND permissions.permissioncode = permissions_operator.permissioncode
				AND permissions.objectid = permissions_operator.objectid)
GO

ALTER TABLE permissions ALTER COLUMN operator nvarchar(3) NOT NULL
GO

ALTER TABLE permissions DROP CONSTRAINT PERMISSIONS_PERMISSIONS_O_FK1
GO

DROP TABLE permissions_operator
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140805-sm-1', 'simon.mok', 'nschema/102.changeLog.xml', GETDATE(), 166, '8:b52b88b99e56f69d3d3c6b9f1bebe19c', 'addColumn tableName=permissions; sql; addNotNullConstraint columnName=operator, tableName=permissions; dropForeignKeyConstraint baseTableName=permissions, constraintName=PERMISSIONS_PERMISSIONS_O_FK1; dropTable tableName=permissions_operator', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140806-kh-1::kelvin.hung
DROP TABLE checklist_item
GO

CREATE TABLE checklist_item (itemid nvarchar(85) NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15) NOT NULL, important char(1) NOT NULL, description nvarchar(255) NOT NULL, deadline datetime, creator_id nvarchar(85), completion_status char(1) CONSTRAINT DF_checklist_item_completion_status DEFAULT 'I' NOT NULL, date_completed datetime, date_created datetime NOT NULL, last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE checklist_item ADD CONSTRAINT PK_CHECKLIST_ITEM PRIMARY KEY (itemid)
GO

ALTER TABLE checklist_item ADD CONSTRAINT FK_CHECKLIST_ITEM_LEARNINGID FOREIGN KEY (learningid) REFERENCES learningObject (learningid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140806-kh-1', 'kelvin.hung', 'nschema/102.changeLog.xml', GETDATE(), 167, '8:ecfbcb495bfc40bda9ad6cc29bf8bc4b', 'dropTable tableName=checklist_item; createTable tableName=checklist_item; addPrimaryKey constraintName=PK_CHECKLIST_ITEM, tableName=checklist_item; addForeignKeyConstraint baseTableName=checklist_item, constraintName=FK_CHECKLIST_ITEM_LEARNINGID, ...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140806-jw-1::joe.wong
exec sp_rename 'certifications.daysvalid', 'valid_period'
GO

exec sp_rename 'certifications.renewable_each_year', 'renewable'
GO

ALTER TABLE certifications ADD valid_period_unit char(1) CONSTRAINT DF_certifications_valid_period_unit DEFAULT 'D' NOT NULL
GO

ALTER TABLE certifications ADD renewable_period int CONSTRAINT DF_certifications_renewable_period DEFAULT 0 NOT NULL
GO

ALTER TABLE certifications ADD renewable_period_unit char(1) CONSTRAINT DF_certifications_renewable_period_unit DEFAULT 'D' NOT NULL
GO

ALTER TABLE certifications ADD CONSTRAINT DF_certifications_valid_period DEFAULT 0 FOR valid_period
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140806-jw-1', 'joe.wong', 'nschema/102.changeLog.xml', GETDATE(), 168, '8:8a8fa6237d36fa4582f507d7880cb782', 'renameColumn newColumnName=valid_period, oldColumnName=daysvalid, tableName=certifications; renameColumn newColumnName=renewable, oldColumnName=renewable_each_year, tableName=certifications; addColumn tableName=certifications; addDefaultValue colu...', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140807-gl-1::garbo.lim
INSERT INTO permissions (objectid, objecttype, permissioncode, identityid, identitytype, operator) VALUES ('R132', 'RT', 1, '*ROOT*', 'O', 'OR')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140807-gl-1', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 169, '8:4485da3ec35a53319cbe724b0f1a8a65', 'insert tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140808-sm-1::simon.mok
ALTER TABLE permissions ADD count int
GO

UPDATE permissions
				SET count = (SELECT count(DISTINCT identitytype)
					FROM (SELECT * FROM permissions) x
					WHERE permissions.objecttype = x.objecttype
						AND permissions.permissioncode = x.permissioncode
						AND permissions.objectid = x.objectid
						AND identitytype not in ('OX', 'U'))
GO

CREATE NONCLUSTERED INDEX idx_permissions_count ON permissions(objecttype, identitytype, identityid, objectid, permissioncode, count)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140808-sm-1', 'simon.mok', 'nschema/102.changeLog.xml', GETDATE(), 170, '8:6a7dfaf0c5c4ffef30dabf43da4e755a', 'addColumn tableName=permissions; sql; createIndex indexName=idx_permissions_count, tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140812-js-1::janice.sung
ALTER TABLE checklist_item ADD owner_id nvarchar(85)
GO

ALTER TABLE checklist_item ADD assigned_by nvarchar(85)
GO

ALTER TABLE checklist_item ADD reminder_days int
GO

ALTER TABLE checklist_item ADD reminder_sent char(1) CONSTRAINT DF_checklist_item_reminder_sent DEFAULT 'N' NOT NULL
GO

UPDATE checklist_item SET owner_id = creator_id, assigned_by = creator_id
GO

ALTER TABLE checklist_item ALTER COLUMN owner_id nvarchar(85) NOT NULL
GO

ALTER TABLE checklist_item ALTER COLUMN assigned_by nvarchar(85) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140812-js-1', 'janice.sung', 'nschema/102.changeLog.xml', GETDATE(), 171, '8:4d11efb0e84931db7e4c42d49e920715', 'addColumn tableName=checklist_item; sql; addNotNullConstraint columnName=owner_id, tableName=checklist_item; addNotNullConstraint columnName=assigned_by, tableName=checklist_item', '', 'EXECUTED', NULL, NULL, '4.8.0', '6820121693')
GO

-- Changeset nschema/102.changeLog.xml::20140813-ns-1::nicky.siu
