-- Package : jrmquery
-- A collection of cursor definitions for queries on the
-- repository meta model tables

prompt Package Header: jrmquery

create or replace package jrmquery is

  --------------------------------------------------------------------------
  -- Cursor : element_type_cur
  --
  -- MR 25-Feb-00, Query element type details by id.
  --------------------------------------------------------------------------
  cursor element_type_cur (type_id number) is    
    select 
	    product                                 product,
	    name                                    name,
	    short_name                              short_name,
	    plural_name                             plural_name,
	    icon                                    icon,
		abstract                                abstract
  from  rm_element_types 
  where id = jrmquery.element_type_cur.type_id
  order by product, name;


  --------------------------------------------------------------------------
  -- Cursor : associations_from_cur
  --
  -- MR 23-Feb-00, Query associations details that our element has FKs to 
  --------------------------------------------------------------------------
  cursor associations_from_cur (type_id number) is    
    select 
        this_link.is_owning_association         is_owning_association,
		this_elem.id                            element_id,
		this_link.name                          assoc_name,
		that_elem.id                            other_element_id,
		that_link.name                          other_assoc_name,
        this_link.link_type                     link_type,
        this_link.arc_no                        arc_no,
        this_link.aggregation                   aggregation,
        this_link.min_cardinality               min_cardinality,
        this_link.max_cardinality               max_cardinality,
        this_link.transferable                  transferable,
        this_link.navigable                     navigable,
        this_link.sequence_in_type              sequence_in_type,
        this_link.sequence_in_link              sequence_in_link
 from  rm_element_types this_elem,
       rm_element_types that_elem, 
	   rm_properties this_prop, 
	   rm_link_properties this_link, 
	   rm_link_properties  that_link, 
	   rm_property_maps map 
  where this_prop.id       = map.property 
  and  map.context         = associations_from_cur.type_id 
  and this_link.id         <> that_link.id 
  and this_prop.id         = this_link.id 
  and that_link.defined_against = that_elem.id 
  and this_link.defined_against = this_elem.id 
  and this_link.link_type = that_link.link_type 
  and this_link.sequence_in_link = that_link.sequence_in_link 
  and this_link.is_stored = 'Y'
  order by this_link.link_type, that_elem.name, this_link.name;

  --------------------------------------------------------------------------
  -- Cursor : associations_to_cur
  --
  -- MR 23-Feb-00, Query associations details that have FKs to our element 
  --------------------------------------------------------------------------
  cursor associations_to_cur (type_id number) is    
    select 
		this_link.is_owning_association         is_owning_association,
		this_elem.id                            element_id,
		this_link.name                          assoc_name, 
		that_link.name                          other_assoc_name, 
		that_link.defined_against               other_element_id,
		this_link.link_type                     link_type, 
		this_link.arc_no                        arc_no, 
		this_link.aggregation                   aggregation, 
		this_link.min_cardinality               min_cardinality, 
		this_link.max_cardinality               max_cardinality, 
		this_link.transferable                  transferable, 
		this_link.navigable                     navigable, 
        this_link.sequence_in_type              sequence_in_type,
        this_link.sequence_in_link              sequence_in_link 
    from     rm_element_types    this_elem, 
             rm_properties       this_prop, 
	         rm_properties       that_prop, 
	         rm_link_properties  this_link, 
	         rm_link_properties  that_link, 
	         rm_property_maps    map 
    where    this_prop.id              = map.property 
    and      map.context               = this_elem.id 
    and      this_prop.id              <> that_prop.id 
    and      this_prop.id              = this_link.id 
    and      that_link.defined_against in ( select id from rm_Element_Types                                           
										   start with id = associations_to_cur.type_id 
										   connect by id = prior supertypes) 
    and      this_link.defined_against  = this_elem.id 
    and      that_prop.id               = that_link.id 
    and      this_link.link_type        = that_link.link_type 
    and      this_link.is_stored        = 'Y'
    and      this_link.sequence_in_link = that_link.sequence_in_link 
    order by this_link.link_type, 
             this_link.sequence_in_type, 
	  	     this_elem.name, 
		     this_prop.name;


end jrmquery;
/