rem ------------------------------------------
prompt Creating Association Type views
rem ------------------------------------------


rem ------------------------------------------
prompt jr_association_types
rem ------------------------------------------
rem
create or replace force view jr_association_types as
select
       et.id                                                id,
       et.types                                             types,
       et.short_name                                        short_name,
       et.name                                              long_name,
       etx.nls_name                                         nls_name,
       etx.nls_plural_name                                  nls_plural_name,
       et.name                                              name,
       et.plural_name                                       plural_name,
       rtrim(et.product)||'_'||rtrim(upper(et.plural_name)) view_name,
       rtrim(et.product)||'O'||rtrim(upper(et.name))        package_name,
       vsl.vsl_use_3d                                       short_name_3d,
       etpo.id                                              part_of_reference,
       vsl.vsl_part_of                                      part_of_short_name,
       etpo.name                                            part_of_name,
       nvl(vsl.vsl_part_degree,'M')                         part_of_degree,
       etuo.id                                              use_of_reference,
       vsl.vsl_use_of                                       use_of_short_name,
       etuo.name                                            use_of_name,
       nvl(vsl.vsl_use_degree,'M')                          use_of_degree,
       vsl.vsl_part_type                                    type_of_short_name,
       etto.name                                            type_of_name,
       nvl(vsl.vsl_part_type_degree,'M')                    type_of_degree,
       vsl.vsl_for_matrix_diagrammer                        is_for_matrix,
       substr(et.user_extension,1,1)                        is_user_type,
       substr(et.user_extension,2,1)                        is_extended,
       substr(et.user_extension,3,1)                        is_published
  from rm_element_types etpo,
       rm_element_types etuo,
       rm_element_types etto,
       rm_element_type_extensions etx,
       rm_element_types et,
       sdw_valid_structure_elements vsl
 where
       et.short_name = vsl.vsl_type
       and etx.for_type = et.id
       and etpo.short_name = vsl.vsl_part_of
       and etuo.short_name = vsl.vsl_use_of
       and etto.short_name(+) = vsl.vsl_part_type
       and nvl(et.abstract,'N') = 'N'
       and et.product = 'SDD'
       and et.short_name in ('CFGMBR','WASPEC')
union
select *
from ciue_association_types
/
