/**
  @file
  @brief migration script to move to v7.11 of Data Controller

  OPTIONAL CHANGE - upload additional validation rules (HARDREGEX, SOFTREGEX)
  into the RULE_TYPE dropdown (data only)

**/

%let dclib=YOURDCLIB;

libname &dclib "/YOUR/DATACONTROLLER/LIBRARY/PATH";


/* add new validation rules */
proc sql noprint;
select max(selectbox_rk) into: maxrk
  from &dclib..mpe_selectbox;
insert into &dclib..mpe_selectbox set
    selectbox_rk=&maxrk+1
    ,ver_from_dttm=0
    ,select_lib="&dclib"
    ,select_ds="MPE_VALIDATIONS"
    ,base_column="RULE_TYPE"
    ,selectbox_value='HARDREGEX'
    ,selectbox_order=13
    ,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &dclib..mpe_selectbox set
    selectbox_rk=&maxrk+2
    ,ver_from_dttm=0
    ,select_lib="&dclib"
    ,select_ds="MPE_VALIDATIONS"
    ,base_column="RULE_TYPE"
    ,selectbox_value='SOFTREGEX'
    ,selectbox_order=14
    ,ver_to_dttm='31DEC5999:23:59:59'dt;
quit;


/*
  Update the MPE_SECURITY LIBREF validation from HARDSELECT on MPE_TABLES
  (EDITABLE libraries only) to the SOFTSELECT_HOOK, which lists ALL libraries
  (services/validations/libraries_all)
*/
proc sql noprint;
/* close out the current record */
update &dclib..mpe_validations set
    tx_to=%sysfunc(datetime())
  where base_lib="&dclib"
    and base_ds="MPE_SECURITY"
    and base_col="LIBREF"
    and rule_type='HARDSELECT'
    and tx_to='31DEC5999:23:59:59'dt;
/* insert the new version */
insert into &dclib..mpe_validations set
    tx_from=%sysfunc(datetime())
    ,base_lib="&dclib"
    ,base_ds="MPE_SECURITY"
    ,base_col="LIBREF"
    ,rule_type='SOFTSELECT_HOOK'
    ,rule_value="services/validations/libraries_all"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
quit;
