rem ---------------------------------------------------------------------------+
rem Dependency reporting views
rem ---------------------------------------------------------------------------+

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_supplr_pacs
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_supplr_pacs
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from sdd_dependencies     d
     , sdd_dependency_types t
     , sdd_object_versions  o
  where d.supplier_ivid         = o.ivid
  and   d.dependency_type_irid  = t.irid
  and   t.type                 != 'Provides'
  and   d.status               != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_client_pacs
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_client_pacs
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_object_versions  o
  where d.client_ivid = o.ivid
  and   d.status      != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_supplr_sacs
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_supplr_sacs
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_object_versions  o
       , sdd_dependency_types t
  where d.supplier_parent_ivid is not null
  and   d.supplier_parent_ivid = o.ivid
  and   d.dependency_type_irid = t.irid
  and   t.type                != 'Provides'
  and   d.status              != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_client_sacs
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_client_sacs
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from  sdd_dependencies d
      , sdd_object_versions  o
  where ( d.client_parent_ivid is not null
         and d.client_parent_ivid = o.ivid)
  and d.status != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_pac_nonprincipal_providers
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_pac_nonprincipal_providers
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_dependency_types t
       , sdd_object_versions  o
  where d.supplier_ivid        = o.ivid
  and   d.dependency_type_irid = t.irid
  and   t.type                 = 'Provides'
  and   (t.subtype is null
         or t.subtype         != 'Principal')
  and   d.status              != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_sac_nonprincipal_providers
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_sac_nonprincipal_providers
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_dependency_types t
       , sdd_object_versions  o
  where (d.supplier_parent_ivid is not null
         and d.supplier_parent_ivid = o.ivid)
  and   d.dependency_type_irid = t.irid
  and   t.type                = 'Provides'
  and   (t.subtype is null
         or t.subtype         != 'Principal')
  and   d.status              != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_all_nonprincipal_providers
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_all_nonprincipal_providers
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_dependency_types t
  where d.dependency_type_irid = t.irid
  and   t.type                = 'Provides'
  and   (t.subtype is null
         or t.subtype         != 'Principal')
  and   d.status              != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_PAC_PRINCIPAL_PROVIDERS
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_pac_principal_providers
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_dependency_types t
       , sdd_object_versions  o
  where d.supplier_ivid        = o.ivid
  and   d.dependency_type_irid = t.irid
  and   t.type                 = 'Provides'
  and   t.subtype              = 'Principal'
  and   d.status              != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_SAC_PRINCIPAL_PROVIDERS
rem NOTE: This view only fuctions correctly without a work area context set.
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_sac_principal_providers
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_dependency_types t
       , sdd_object_versions  o
  where (d.supplier_parent_ivid is not null
         and d.supplier_parent_ivid = o.ivid)
  and   d.dependency_type_irid = t.irid
  and   t.type                = 'Provides'
  and   t.subtype             = 'Principal'
  and   d.status              != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_ALL_PRINCIPAL_PROVIDERS
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_all_principal_providers
  as
  select
  d.irid
  , d.ivid
  , d.dependency_type_irid
  , d.supplier_irid
  , d.supplier_ivid
  , d.supplier_type_id
  , d.client_irid
  , d.client_ivid
  , d.client_type_id
  , d.status
  , d.number_of_times_modified
  , d.date_created
  , d.created_by
  , d.date_changed
  , d.changed_by
  , d.remark
  , d.copy_on_version
  , d.copy_on_copy
  , d.supplier_parent_ivid
  , d.client_parent_ivid
  , d.purge_candidate
  from   sdd_dependencies     d
       , sdd_dependency_types t
  where d.dependency_type_irid = t.irid
  and   t.type                = 'Provides'
  and   t.subtype             = 'Principal'
  and   d.status              != 'E'
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_indirect_supplr_pacs1
rem indirect supplier of a principal name
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_indirect_supplr_pacs1
  as
  select   nameDpnd.irid            dependency_irid
         , nameDpnd.client_type_id  client_type_id
         , nameDpnd.client_irid     client_irid
         , nameDpnd.client_ivid     client_ivid
         , pacDpnd.supplier_type_id supplier_type_id
         , pacDpnd.supplier_irid    supplier_irid
         , pacDpnd.supplier_ivid    supplier_ivid
         , name.irid                name_irid
         , name.ivid                name_ivid
         , name.domain_irid         name_domain_irid
         , name.type_id             name_type_id
  from     sdd_dependencies         nameDpnd
         , sdd_dependencies         pacDpnd
         , sdd_dependency_types     t
         , sdd_object_versions      o
         , sdd_global_names         name
  where pacDpnd.supplier_ivid        = o.ivid
  and   pacDpnd.dependency_type_irid = t.irid
  and   t.type                       = 'Provides'
  and   t.subtype                    = 'Principal'
  and   pacDpnd.status              != 'E'
  and   nameDpnd.supplier_ivid       = pacDpnd.client_ivid
  and   nameDpnd.status             != 'E'
  and   nameDpnd.supplier_irid       = name.irid
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_indirect_supplr_pacs2
rem an indirect supplier is the supplier of a non-principal name
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_indirect_supplr_pacs2
  as
  select   nameDpnd.irid            dependency_irid
         , nameDpnd.client_type_id  client_type_id
         , nameDpnd.client_irid     client_irid
         , nameDpnd.client_ivid     client_ivid
         , pacDpnd.supplier_type_id supplier_type_id
         , pacDpnd.supplier_irid    supplier_irid
         , pacDpnd.supplier_ivid    supplier_ivid
         , name.irid                name_irid
         , name.ivid                name_ivid
         , name.domain_irid         name_domain_irid
         , name.type_id             name_type_id
  from     sdd_dependencies         nameDpnd
         , sdd_global_names         name
         , sdd_dependencies         pacDpnd
         , sdd_dependency_types     dt
         , sdd_object_versions      o
  where    pacDpnd.supplier_ivid        = o.ivid
  and      pacDpnd.dependency_type_irid = dt.irid
  and      dt.type                      = 'Provides'
  and     (dt.subtype is null
           or dt.subtype         != 'Principal')
  and     pacDpnd.status         != 'E'
  and     nameDpnd.supplier_ivid  = pacDpnd.client_ivid
  and     nameDpnd.status            != 'E'
  and     nameDpnd.supplier_irid  = name.irid
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_indirect_supplr_sacs1
rem indirect supplier, accessed through a principal name
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_indirect_supplr_sacs1
  as
  select   nameDpnd.irid                dependency_irid
         , nameDpnd.client_type_id      client_type_id
         , nameDpnd.client_irid         client_irid
         , nameDpnd.client_ivid         client_ivid
         , sacDpnd.supplier_type_id     supplier_type_id
         , sacDpnd.supplier_irid        supplier_irid
         , sacDpnd.supplier_ivid        supplier_ivid
         , sacDpnd.supplier_parent_ivid supplier_parent_ivid
         , name.irid                    name_irid
         , name.ivid                    name_ivid
         , name.domain_irid             name_domain_irid
         , name.type_id                 name_type_id
  from     sdd_dependencies             nameDpnd
         , sdd_global_names             name
         , sdd_dependencies             sacDpnd
         , sdd_dependency_types         dt
         , sdd_object_versions          o
  where (sacDpnd.supplier_parent_ivid is not null
         and sacDpnd.supplier_parent_ivid = o.ivid)
  and   sacDpnd.dependency_type_irid = dt.irid
  and   dt.type                = 'Provides'
  and   dt.subtype             = 'Principal'
  and   sacDpnd.status        != 'E'
  and   nameDpnd.status        != 'E'
  and   nameDpnd.supplier_ivid  = sacDpnd.client_ivid
  and   nameDpnd.supplier_irid  = name.irid
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_indirect_supplr_sacs2
rem indirect supplier, accessed through non principal name
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_indirect_supplr_sacs2
  as
  select   nameDpnd.irid                dependency_irid
         , nameDpnd.client_type_id      client_type_id
         , nameDpnd.client_irid         client_irid
         , nameDpnd.client_ivid         client_ivid
         , sacDpnd.supplier_type_id     supplier_type_id
         , sacDpnd.supplier_irid        supplier_irid
         , sacDpnd.supplier_ivid        supplier_ivid
         , sacDpnd.supplier_parent_ivid supplier_parent_ivid
         , name.irid                    name_irid
         , name.ivid                    name_ivid
         , name.domain_irid             name_domain_irid
         , name.type_id                 name_type_id
  from     sdd_dependencies             nameDpnd
         , sdd_global_names               name
         , sdd_dependencies               sacDpnd
         , sdd_dependency_types           dt
         , sdd_object_versions            o
  where (sacDpnd.supplier_parent_ivid is not null
          and sacDpnd.supplier_parent_ivid = o.ivid)
  and    sacDpnd.dependency_type_irid = dt.irid
  and    dt.type              = 'Provides'
  and    (dt.subtype is null
          or dt.subtype      != 'Principal')
  and    sacDpnd.status      != 'E'
  and    nameDpnd.status      != 'E'
  and    nameDpnd.supplier_ivid  = sacDpnd.client_ivid
  and    nameDpnd.supplier_irid  = name.irid
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_indirect_client_pacs
rem indirect clients are *only* those clients that use the principal name of the
rem supplier. The principal name is the name which has a Provides/Principal
rem dependency to an object.
rem So, thisview equates to finding clients of principal names.
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_indirect_client_pacs
  as
  select    pacDpnd.irid                 dependency_irid
          , pacDpnd.client_type_id       client_type_id
          , pacDpnd.client_irid          client_irid
          , pacDpnd.client_ivid          client_ivid
          , pacDpnd.client_parent_ivid   client_parent_ivid
          , nameDpnd.supplier_type_id    supplier_type_id
          , nameDpnd.supplier_irid       supplier_irid
          , nameDpnd.supplier_ivid       supplier_ivid
          , nameDpnd.client_irid         name_irid
          , nameDpnd.client_ivid         name_ivid
  from      sdd_dependency_types         type
          , sdd_dependencies             pacDpnd
          , sdd_dependencies             nameDpnd
          , sdd_object_versions          o
  where     pacDpnd.status  != 'E'
  and       nameDpnd.status != 'E'
  and       nameDpnd.dependency_type_irid = type.irid
  and       type.type             = 'Provides'
  and       type.subtype          = 'Principal'
  and       pacDpnd.supplier_ivid = nameDpnd.client_ivid
  and       pacDpnd.client_ivid = o.ivid
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_indirect_client_sacs
rem ---------------------------------------------------------------------------+
rem
rem bug fix 1265399 - view incorrect, joined supplier_ivid to client_irid.
create or replace force view sdd_dpnd_indirect_client_sacs
  as
  select   sacDpnd.irid                 dependency_irid
         , sacDpnd.client_type_id       client_type_id
         , sacDpnd.client_irid          client_irid
         , sacDpnd.client_ivid          client_ivid
         , sacDpnd.client_parent_ivid   client_parent_ivid
         , nameDpnd.supplier_type_id    supplier_type_id
         , nameDpnd.supplier_irid       supplier_irid
         , nameDpnd.supplier_ivid       supplier_ivid
         , nameDpnd.client_irid         name_irid
         , nameDpnd.client_ivid         name_ivid
 from      sdd_dependencies             nameDpnd
         , sdd_dependencies             sacDpnd
         , sdd_dependency_types         type
         , sdd_object_versions          o
 where     sacDpnd.status  != 'E'
  and      nameDpnd.status != 'E'
  and      nameDpnd.dependency_type_irid = type.irid
  and      type.type             = 'Provides'
  and      type.subtype          = 'Principal'
  and      sacDpnd.supplier_ivid = nameDpnd.client_ivid
  and      sacDpnd.client_parent_ivid is not null
  and      sacDpnd.client_parent_ivid = o.ivid
/

rem ---------------------------------------------------------------------------+
prompt View: sdd_dpnd_names_without_prvdrs
rem ---------------------------------------------------------------------------+
rem
create or replace force view sdd_dpnd_names_without_prvdrs
as
  select
  d1.irid
  , d1.ivid
  , d1.dependency_type_irid
  , d1.supplier_irid
  , d1.supplier_ivid
  , d1.supplier_type_id
  , d1.client_irid
  , d1.client_ivid
  , d1.client_type_id
  , d1.status
  , d1.number_of_times_modified
  , d1.date_created
  , d1.created_by
  , d1.date_changed
  , d1.changed_by
  , d1.remark
  , d1.copy_on_version
  , d1.copy_on_copy
  , d1.supplier_parent_ivid
  , d1.client_parent_ivid
  , d1.purge_candidate
  , name.domain_irid         name_domain_irid
  , name.type_id             name_type_id
  from sdd_global_names name,
       sdd_dependencies d1
  where d1.supplier_irid = name.irid
  and   d1.status       != 'E'
  and not exists
     (select null
      from sdd_dependencies d2
      where d2.client_irid = name.irid)
/




