
rem ----------------------------------------------------------------------------------+
prompt Table : i$sdd_rules
rem
rem Definitions of rules used to define the contents of a workarea
rem
rem Note, rules are not versionable
rem CW 16-Oct-98, fixed bug 747510, increased rule_sql column length from 3000 to 4000
rem
rem Changed dual to sys.dual to fix bug 1311500
rem ----------------------------------------------------------------------------------+ 
create table i$sdd_rules
( irid         number                         not null
, name         varchar2(30)                   not null
, user_defined varchar2(1)   default 'N'      not null
, exclude_rule varchar2(1)   default 'N'      not null
, notm         number        default 0        not null
, date_created date          default sysdate  not null
, created_by   varchar2(30)  default user     not null
, date_changed date
, changed_by   varchar2(30)
, rule_sql     varchar2(4000)
)
/

-- pk_sdd_rule
alter table i$sdd_rules add constraint pk_sdd_rule
primary key (irid)
/

-- uk_sdd_rule_name
alter table i$sdd_rules add constraint uk_sdd_rule_name
unique (name)
/

-- check_exclude_rule
alter table i$sdd_rules add constraint check_exclude_rule
check (exclude_rule in ('Y', 'N'))
/

-- check_user_defined
alter table i$sdd_rules add constraint check_user_defined
check (user_defined in ('Y', 'N'))
/

create or replace trigger sdd_rule_audit
  before update on i$sdd_rules
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

rem ----------------------------------------------------------------------------------+
PROMPT Creating Table 'i$sdd_rule_params'
rem
rem Definitions of parameters to rules
rem ----------------------------------------------------------------------------------+
create table i$sdd_rule_params
( rule_irid     number                            not null
, param_seq     number                            not null
, name          varchar2(30)                      not null
, nls_name      varchar2(60)                      not null
, type          varchar2(30)  default 'varchar2'  not null
, notm          number        default 0           not null
, date_created  date          default sysdate     not null
, created_by    varchar2(30)  default user        not null
, date_changed  date
, changed_by    varchar2(30)
)
/

-- pk_sdd_rulprm
alter table i$sdd_rule_params add constraint pk_sdd_rulprm
primary key (rule_irid, param_seq)
/

-- uk_sdd_rulprm_name
alter table i$sdd_rule_params add constraint uk_sdd_rulprm_name
unique (rule_irid, name)
/

-- fk_rulprm_to_rule
alter table i$sdd_rule_params add constraint fk_rulprm_to_rule 
foreign key (rule_irid) references i$sdd_rules on delete cascade
/

-- Note, not creating an index on fk_rulprm_to_rule (rule_irid) as this is a leading
-- column of the pk and uk

create or replace trigger sdd_rulprm_audit
  before update on i$sdd_rule_params
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

rem ----------------------------------------------------------------------------------+
prompt Populating Rules and Rule Parameters...
rem
rem CW 23-Sep-1999 Fix bug 1000526 maintain guid values for seed data (use core4011 values)
rem ----------------------------------------------------------------------------------+

prompt Creating Rule 'INCLUDE_FOLDER'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values (415838727997767736302153743193360441,'INCLUDE_FOLDER','N','N','');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values (415838727997767736302153743193360441,1,'CONTAINER_ELEMENT','Folder path','OBJECT_NAME');

prompt Creating Rule 'INCLUDE_OBJECT'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values (415838727997772458668636612838574137,'INCLUDE_OBJECT','N','N','');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values (415838727997772458668636612838574137,1,'ELEMENT','Object path','OBJECT_NAME');

prompt Creating Rule 'FOLDER_LATEST_CONTENTS'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values (424397028112712537220130327160703033,'FOLDER_LATEST_CONTENTS','N','N','');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values (424397028112712537220130327160703033,1,'CONTAINER_ELEMENT','Folder path','OBJECT_NAME');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values (424397028112712537220130327160703033,2,'BRANCH','Branch label','BRANCH_NAME');

prompt Creating Rule 'FOLDER_LATEST_BEFORE'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values (424397028112717259586613196805916729,'FOLDER_LATEST_BEFORE','N','N','');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values (424397028112717259586613196805916729,1,'CONTAINER_ELEMENT','Folder path','OBJECT_NAME');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values (424397028112717259586613196805916729,2,'BEFORE_DATE','Before date','DATE');

prompt Creating Rule 'FOLDER_LATEST_BETWEEN'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(424397028112721981953096066451130425,'FOLDER_LATEST_BETWEEN','N','N','');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(424397028112721981953096066451130425,1,'CONTAINER_ELEMENT','Folder path','OBJECT_NAME');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(424397028112721981953096066451130425,2,'START_DATE','Start Date','DATE');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(424397028112721981953096066451130425,3,'END_DATE','End Date','DATE');


prompt Creating Rule 'LATEST'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(415838727997777181035119482483787833,'LATEST','N','N'
,'select va.irid ov_irid, va.to_ivid ov_ivid
from I$SDD_VERSION_ASSOCIATIONS va
    ,I$SDD_BRANCHES br
    ,I$SDD_OBJECT_VERSIONS ov
where va.branch_id = br.branch_id
and   br.name = :branch
and   va.edge_kind = ''T''
and   ov.ivid=va.to_ivid
and   ov.wastebasket=''N''')
/

insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997777181035119482483787833,1,'BRANCH','Branch label','BRANCH_NAME');

prompt Creating Rule 'LATEST_BEFORE'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(415838727997781903401602352129001529,'LATEST_BEFORE','N','N'
,'select va1.irid ov_irid, va1.to_ivid ov_ivid
    from sdd_version_associations va1
        ,I$SDD_BRANCHES br
   where va1.branch_id = br.branch_id
     and br.name = :branch
     and va1.edge_kind not in (''T'',''M'') 
     and va1.edge_sequence = (select max(va2.edge_sequence)
                                from SDD_VERSION_ASSOCIATIONS va2
                               where va2.irid = va1.irid
                                 and va2.branch_id = va1.branch_id
                                 and NVL(va2.date_changed,va2.date_created) < to_date(:before_date,''DD-MON-YYYY HH24:MI:SS''))')
/

insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997781903401602352129001529,1,'BRANCH','Branch label','BRANCH_NAME');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997781903401602352129001529,2,'BEFORE_DATE','Before date','DATE');

prompt Creating Rule 'LATEST_BETWEEN'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(415838727997786625768085221774215225,'LATEST_BETWEEN','N','N'
,'select va1.irid ov_irid, va1.to_ivid ov_ivid
    from sdd_version_associations va1
        ,I$SDD_BRANCHES br
   where va1.branch_id = br.branch_id
     and br.name = :branch
     and va1.edge_kind not in (''T'',''M'') 
     and va1.edge_sequence = (select max(va2.edge_sequence)
                                from SDD_VERSION_ASSOCIATIONS va2
                               where va2.irid = va1.irid
                                 and va2.branch_id = va1.branch_id
                                 and NVL(va2.date_changed,va2.date_created) < to_date(:end_date,''DD-MON-YYYY HH24:MI:SS'')
                                 and NVL(va2.date_changed,va2.date_created) > to_date(:start_date,''DD-MON-YYYY HH24:MI:SS''))')
;

insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997786625768085221774215225,1,'BRANCH','Branch label','BRANCH_NAME');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997786625768085221774215225,2,'START_DATE','Start Date','DATE');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997786625768085221774215225,3,'END_DATE','End Date','DATE');

prompt Creating Rule 'LATEST_CONFIG_CONTENTS'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(415838727997791348134568091419428921,'LATEST_CONFIG_CONTENTS','N','N'
,'select va.irid ov_irid, va.to_ivid ov_ivid
from sdd_version_associations va
    ,I$sdd_configuration_members cm
    ,I$sdd_branches br
where va.branch_id = br.branch_id
and   br.name = :branch
and   va.edge_kind = ''T''
and   va.irid = cm.object_irid
and   cm.config_ivid in (select jr_name.get_IVID(:configuration) from sys.dual)')
/

insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997791348134568091419428921,1,'CONFIGURATION','Configuration','OBJECT_NAME');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997791348134568091419428921,2,'BRANCH','Branch label','BRANCH_NAME');

prompt Creating Rule 'LATEST_DIAGRAM_CONTENTS'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(415838727997796070501050961064642617,'LATEST_DIAGRAM_CONTENTS','N','N'
,'select va.irid ov_irid, va.to_ivid ov_ivid
  from SDD_VERSION_ASSOCIATIONS va
      ,I$SDD_DEU deu
      ,I$SDD_BRANCHES br
 where deu.parent_ivid in (select jr_name.get_IVID(:diagram) from sys.dual)
   and va.irid = deu.cielement_ref
   and va.edge_kind = (''T'')
   and va.branch_id = br.branch_id
   and br.name=:branch')
/

insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997796070501050961064642617,1,'DIAGRAM','Diagram path','OBJECT_NAME');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997796070501050961064642617,2,'BRANCH','Branch label','BRANCH_NAME');

prompt Creating Rule 'EXCLUDE_OBJECT'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(415838727997800792867533830709856313,'EXCLUDE_OBJECT','N','Y','');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997800792867533830709856313,1,'ELEMENT','Object path','OBJECT_NAME');

prompt Creating Rule 'EXCLUDE_FOLDER'
insert into i$sdd_rules (irid,name,user_defined,exclude_rule,rule_sql) values(415838727997805515234016700355070009,'EXCLUDE_FOLDER','N','Y','');
insert into i$sdd_rule_params (rule_irid,param_seq,name,nls_name,type) values(415838727997805515234016700355070009,1,'CONTAINER_ELEMENT','Folder path','OBJECT_NAME');

