
rem ---------------------------------------------------------------------------+
PROMPT View : sdd_object_versions
rem ---------------------------------------------------------------------------+
rem Changed dual to sys.dual to fix bug 1311500
rem
create or replace force view sdd_object_versions
( irid
, ivid
, name
, table_irid
, logical_type_id
, notm
, obj_notm
, obj_notm_when_analyzed
, state
, wastebasket
, lock_flag
, date_created
, created_by
, date_changed
, changed_by
, vlabel
, branch_id
, sequence_in_branch
, irid_guid
, comments
)
as select 
  o.irid
, o.ivid
, o.name
, o.table_irid
, o.logical_type_id
, o.notm
, o.obj_notm
, o.obj_notm_when_analyzed
, o.state
, o.wastebasket
, o.lock_flag
, o.date_created
, o.created_by
, o.date_changed
, o.changed_by
, o.vlabel
, o.branch_id
, o.sequence_in_branch
, o.irid_guid
, o.comments
from  i$sdd_object_versions o
/

rem ---------------------------------------------------------------------------+
PROMPT View : sdd_configurations
rem ---------------------------------------------------------------------------+
create or replace force view sdd_configurations
   (
    irid
   ,ivid
   ,name
   ,notm
   ,date_created
   ,created_by
   ,date_changed
   ,changed_by
   ,config_spec_irid
   ,kind
   ,tracking
   ,description
   )
as select 
    c.irid
   ,c.ivid
   ,c.name
   ,c.notm
   ,c.date_created
   ,c.created_by
   ,c.date_changed
   ,c.changed_by
   ,c.config_spec_irid
   ,c.kind
   ,c.tracking
   ,c.description
from  i$sdd_configurations      c
     ,i$sdd_object_versions     o
where c.ivid        = o.ivid
and   o.wastebasket = jr_context.wastebasket
and exists (select null
              from sys.dual
             where jr_acc_rights.can_sel(c.ivid,'Y') = 'Y')
/

create or replace trigger sdd_configurations_i
 instead of insert on sdd_configurations
 for each row
 declare
  cfg_irid number;
  cfg_ivid number;
 begin
  jr_configuration_def.create_configuration(:new.name
                                           ,cfg_irid
                                           ,cfg_ivid
                                           ,:new.description);
 end sdd_configurations_i;
/

create or replace trigger sdd_configurations_u
 instead of update on sdd_configurations
 for each row
 declare
 begin
  if :new.name!=:old.name then
   jr_configuration_def.rename_configuration(:old.irid
                                            ,:old.ivid
                                            ,:new.name);
  end if;
  if :new.description!=:old.description then
   jr_configuration_def.change_configuration_desc(:old.irid
                                                 ,:old.ivid
                                                 ,:new.description);
  end if;
 end sdd_configurations_u;
/

create or replace trigger sdd_configurations_d
 instead of delete on sdd_configurations
 for each row
 declare
 begin
  jr_configuration_def.remove_configuration(:old.irid,:old.ivid);
 end sdd_configurations_d;
/


rem ---------------------------------------------------------------------------+
PROMPT View : sdd_configuration_members
rem ---------------------------------------------------------------------------+
create or replace force view sdd_configuration_members
   (
    config_irid
   ,config_ivid
   ,object_irid
   ,object_ivid
   ,notm
   ,date_created
   ,created_by
   ,date_changed
   ,changed_by
   ,kind
   )
as select 
    cm.config_irid
   ,cm.config_ivid
   ,cm.object_irid
   ,cm.object_ivid
   ,cm.notm
   ,cm.date_created
   ,cm.created_by
   ,cm.date_changed
   ,cm.changed_by
   ,cm.kind
from  i$sdd_configuration_members cm
    , i$sdd_object_versions ov
where ov.ivid = cm.config_ivid
and   ov.wastebasket = jr_context.wastebasket
and  exists (select null
               from sys.dual
              where  jr_acc_rights.can_sel(cm.config_ivid)='Y')
/

rem ---------------------------------------------------------------------------+
PROMPT View : sdd_branches
rem ---------------------------------------------------------------------------+
create or replace force view sdd_branches
  ( branch_id
  , name
  , notm
  , date_created
  , created_by
  , date_changed
  , changed_by
  , notes
  )
as select
    branch_id
  , name
  , notm
  , date_created
  , created_by
  , date_changed
  , changed_by
  , notes
from i$sdd_branches
/

create or replace trigger sdd_branches_i
 instead of insert on sdd_branches
 for each row
 declare
  branch_id number;
 begin
  branch_id:=jr_version.mk_branch(:new.name,:new.notes);
 end sdd_branches_i;
/

create or replace trigger sdd_branches_u
 instead of update on sdd_branches
 for each row
 declare
 begin
  if :new.name!=:old.name then
   jr_version.change_branch_label(:old.branch_id
                                 ,:new.name);
  end if;
  if :new.notes!=:old.notes then
   jr_version.change_branch_label_notes(:old.branch_id
                                        ,:new.notes);
  end if;
 end sdd_branches_u;
/

create or replace trigger sdd_branches_d
 instead of delete on sdd_branches
 for each row
 declare
 begin
  jr_version.delete_branch(:old.branch_id);
 end sdd_branches_d;
/


rem ---------------------------------------------------------------------------+
PROMPT View : sdd_workareas
rem ---------------------------------------------------------------------------+
create or replace force view sdd_workareas
  ( irid
  , owner
  , name
  , kind
  , notm
  , date_created
  , created_by
  , notc
  , date_changed
  , changed_by
  , is_compiled
  , default_checkin_branch
  , description
  )
as select
    irid
  , owner
  , name
  , kind
  , notm
  , date_created
  , created_by
  , notc
  , date_changed
  , changed_by
  , is_compiled
  , default_checkin_branch
  , description
from i$sdd_workareas
where  exists (select null
               from sys.dual
              where jr_acc_rights.can_sel_wa(irid)='Y')

/

create or replace trigger sdd_workareas_i
 instead of insert on sdd_workareas
 for each row
 declare
  workarea_id number;
 begin
  workarea_id:=jr_workarea.create_workarea(:new.owner
                                          ,:new.name
                                          ,:new.description
                                          ,:new.kind);
 end sdd_workareas_i;
/

create or replace trigger sdd_workareas_u
 instead of update on sdd_workareas
 for each row
 declare
 begin
  if :new.name!=:old.name then
   jr_workarea.rename_workarea(:old.irid
                              ,:new.name);
  end if;
  if :new.description!=:old.description then
   jr_workarea.change_workarea_desc(:old.irid
                                   ,:new.description);
  end if;
  if :new.kind!=:old.kind then
   if :new.kind='private' then
    jr_workarea.set_workarea_private(:old.irid);
   elsif :new.kind='shared' then
    jr_workarea.set_workarea_shared(:old.irid);
   end if;
  end if;
  if :new.is_compiled!=:old.is_compiled then
   jr_workarea.set_compile_flag(:old.irid
                               ,:new.is_compiled);
  end if;
 end sdd_workareas_u;
/

create or replace trigger sdd_workareas_d
 instead of delete on sdd_workareas
 for each row
 declare
 begin
  jr_workarea.delete_workarea(:old.irid);
 end sdd_workareas_d;
/


rem ---------------------------------------------------------------------------+
PROMPT View : sdd_rules
rem ---------------------------------------------------------------------------+
create or replace force view sdd_rules
  ( irid
  , name
  , user_defined
  , exclude_rule
  , notm
  , date_created
  , created_by
  , date_changed
  , changed_by
  , rule_sql
  )
as select
    irid
  , name
  , user_defined
  , exclude_rule
  , notm
  , date_created
  , created_by
  , date_changed
  , changed_by
  , rule_sql
from i$sdd_rules
/

rem ---------------------------------------------------------------------------+
PROMPT View : sdd_workarea_spec_entries
rem ---------------------------------------------------------------------------+
create or replace force view sdd_workarea_spec_entries
  ( seq
  , workarea_irid
  , config_ivid
  , exclude
  , notm
  , date_created
  , created_by
  , date_changed
  , changed_by
  , rule_irid
  , predecessor
  )
as select
    seq
  , workarea_irid
  , config_ivid
  , exclude
  , notm
  , date_created
  , created_by
  , date_changed
  , changed_by
  , rule_irid
  , predecessor
from i$sdd_workarea_spec_entries
/

rem ---------------------------------------------------------------------------+
PROMPT View : sdd_version_associations
rem ---------------------------------------------------------------------------+
create or replace force view sdd_version_associations
(irid,
 from_ivid,
 to_ivid,
 edge_kind,
 date_created,
 created_by,
 date_changed,
 changed_by,
 notm,
 branch_id,
 edge_sequence,
 notes) as
select
 v.irid,
 v.from_ivid,
 v.to_ivid,
 v.edge_kind,
 v.date_created,
 v.created_by,
 v.date_changed,
 v.changed_by,
 v.notm,
 v.branch_id,
 v.edge_sequence,
 v.notes
from i$sdd_version_associations v
    ,i$sdd_object_versions o1
    ,i$sdd_object_versions o2
where v.to_ivid = o1.ivid
  and v.from_ivid = o2.ivid
  and o1.wastebasket = 'N'
  and o2.wastebasket = 'N'
/
rem ---------------------------------------------------------------------------+
PROMPT View : sdd_wastebasket
rem ---------------------------------------------------------------------------+
create or replace force view sdd_wastebasket 
(irid, 
 ivid, 
 name, 
 table_irid, 
 logical_type_id, 
 notm,
 obj_notm,
 obj_notm_when_analyzed, 
 state, 
 lock_flag, 
 date_created, 
 created_by, 
 date_changed,
 changed_by,
 date_deleted, 
 deleted_by, 
 vlabel,
 branch_id,
 sequence_in_branch, 
 irid_guid, 
 comments) as 
select
    o.irid
   ,o.ivid
   ,o.name
   ,o.table_irid
   ,o.logical_type_id
   ,o.notm
   ,o.obj_notm
   ,o.obj_notm_when_analyzed
   ,o.state
   ,o.lock_flag
   ,o.date_created
   ,o.created_by
   ,o.date_changed
   ,o.changed_by
   ,o.date_changed
   ,o.changed_by
   ,o.vlabel
   ,o.branch_id
   ,o.sequence_in_branch
   ,o.irid_guid
   ,o.comments
from i$sdd_object_versions o
where o.wastebasket = 'Y'
  and (
       (
        jr_context.workarea is null
       )
      or exists
       (select null
        from I$SDD_WA_CONTEXT ctxt
        where ctxt.object_ivid=o.ivid
          and ctxt.workarea_irid=jr_context.workarea))           
/

rem ---------------------------------------------------------------------------+
PROMPT View : sdd_lost_and_found
rem Hard-coded type id 384781276385787681422765648005056569 is for CONFIGURATION
rem used rather than join to RM_ELEMENT_TYPES for permormance reasons
rem ---------------------------------------------------------------------------+
create or replace force view sdd_lost_and_found
   (
    irid
   ,ivid
   ,name
   ,table_irid
   ,logical_type_id
   ,notm
   ,obj_notm
   ,obj_notm_when_analyzed
   ,state
   ,wastebasket
   ,lock_flag
   ,date_created
   ,created_by
   ,date_changed
   ,changed_by
   ,vlabel
   ,branch_id
   ,sequence_in_branch
   ,irid_guid
   ,comments
   )
as 
select 
  o.irid
, o.ivid
, o.name
, o.table_irid
, o.logical_type_id
, o.notm
, o.obj_notm
, o.obj_notm_when_analyzed
, o.state
, o.wastebasket
, o.lock_flag
, o.date_created
, o.created_by
, o.date_changed
, o.changed_by
, o.vlabel
, o.branch_id
, o.sequence_in_branch
, o.irid_guid
, o.comments
from i$sdd_object_versions o
where o.wastebasket='N'  
  and o.logical_type_id!=384781276385787681422765648005056569
  and not exists (select null
                  from   i$sdd_folder_members fm
                        ,i$sdd_object_versions o2
                  where  fm.member_object = o.irid
                    and  o2.ivid=fm.parent_ivid
                    and  fm.ownership_flag='Y'
                    and  o2.wastebasket='N'
                 )           
minus 
select 
  o.irid
, o.ivid
, o.name
, o.table_irid
, o.logical_type_id
, o.notm
, o.obj_notm
, o.obj_notm_when_analyzed
, o.state
, o.wastebasket
, o.lock_flag
, o.date_created
, o.created_by
, o.date_changed
, o.changed_by
, o.vlabel
, o.branch_id
, o.sequence_in_branch
, o.irid_guid
, o.comments
from i$sdd_object_versions o
    ,I$sdd_folders fol
where fol.ivid=o.ivid
  and fol.root_flag='Y'
/

