-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
--	Name 		: ckpopstr.sql
--	Description	: This script populates the jr_id_maps database table
--			  with GUID values for all rows in the sdd_structure_elements table
--
--	Date		: 30th November 1998
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Fixed US Bug 910075 Populate OWNERSHIP_FLAG WHERE APPXXX is for a SHARE
-- Fixed US Bug 935019 NEED TO SUPPORT MIGRATION OF PARENT APPLICATION SYSTEMS
-- Fixed US Bug 934995 NEED TO TIE IN SYSTEM LANGUAGES  WITH SYSTEM FOLDER  
-- Fixed US Bug 871929 Need to identify baseline contents of system folder 

DECLARE

BEGIN

  update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKPOPSTR';

  dbms_output.enable(1000000);


-- **************************************************************************
--
--				SDD STRUCTURE ELEMENTS
--
-- **************************************************************************

  dbms_output.put_line('* Inserting sdd_structure_elements For All STRUCTURE_ELEMENTS');

-- SDD_STRUCTURE_ELEMENTS
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type             
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id                          
                         , type_id 
                         , part_of_id
 	                   , use_of_id                  
                         , pop_tab )    
      	          SELECT   0
                         , str_id
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , str_type_of   
                         , NULL
                         , DECODE(str_elem_owned_by, el_elem_owned_by,'Y','N') 
                         , str_elem_owned_by
                         , NULL
                         , str_part_of
                         , str_use_of
                         , 'SDD_STRUCTURE_ELEMENTS'
	            FROM   sdd_structure_elements@r212,
				 sdd_elements@r212 
                  WHERE   str_type_of not in ('PRU','DEU','MODMOD','MODTAB','MODCOL')
			and     el_id = str_use_of
			and     str_elem_owned_by in (SELECT ID FROM UPG_APP)
			and     str_use_of in ( select old_id from jr_id_maps );

    -- BASE RELATION LOCATION (BRL)
	INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type             
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id                          
                         , type_id 
                         , part_of_id
 	                   , use_of_id                  
                         , pop_tab )    
      	          SELECT   0
                         , str_id
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'BRL'   
                         , NULL
                         , NULL
                         , str_elem_owned_by
                         , NULL
                         , str_part_of
                         , str_use_of
                         , 'SDD_STRUCTURE_ELEMENTS'
	            FROM   sdd_structure_elements@r212			
                WHERE   str_type_of = 'BRL'							
			    and     str_elem_owned_by in (SELECT ID FROM UPG_APP)
				and     str_use_of in ( select old_id from jr_id_maps );
				

-- Create folder members for application hierarchy
INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type             
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id                          
                         , type_id 
                         , part_of_id
 	                     , use_of_id                  
                         , pop_tab )    
      	          SELECT   0
                         , 0
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'APPXXX'   
                         , NULL
                         , 'Y' 
                         , el_within_id
                         , NULL
                         , el_within_id
                         , el_id
                         , 'SDD_STRUCTURE_ELEMENTS'
	            FROM   sdd_elements@r212 
                  WHERE   el_type_of = 'APP'
    			and el_within_id is not null
			and el_id in (SELECT ID FROM UPG_APP);


-- UPG_CI_PRU  
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type             
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id                          
                         , type_id 
                         , part_of_id
 	                   , use_of_id                  
                         , pop_tab )    
      	          SELECT   0
                         , id
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'PRU'  
                         , NULL
                         , NULL
                         , APPLICATION_SYSTEM_OWNED_BY
                         , NULL
                         , CIELEMENT_REFERENCE
                         , PREFERENCE_SET_REFERENCE
                         , 'SDD_STRUCTURE_ELEMENTS'
	            FROM   UPG_CI_PRU;

-- UPG_CI_MODMOD
-- B1275547 EXTERNAL REFS REPORTS MISSING NAMED PREFERENCE SET USAGES FOR CAMBRIDGE UNI DATA
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type             
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id                          
                         , type_id 
                         , part_of_id
 	                   , use_of_id                  
                         , pop_tab )    
      	          SELECT   0
                         , id
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'MODMOD'  
                         , NULL
                         , NULL
                         , APPLICATION_SYSTEM_OWNED_BY
                         , NULL
                         , PARENT_MODULE_REFERENCE
                         , CHILD_MODULE_REFERENCE
                         , 'SDD_STRUCTURE_ELEMENTS'
	            FROM   UPG_CI_MODMOD;

-- UPG_CI_DEU
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type             
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id                          
                         , type_id 
                         , part_of_id
 	                   , use_of_id                  
                         , pop_tab )    
      	          SELECT   0
                         , id
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'DEU'  
                         , NULL
                         , NULL
                         , APPLICATION_SYSTEM_OWNED_BY
                         , NULL
                         , DIAGRAM_REFERENCE
                         , CIELEMENT_REFERENCE
                         , 'SDD_STRUCTURE_ELEMENTS'
	            FROM   UPG_CI_DEU;
 
    -- Some Elements Reference STR_ID, in these cases we will need to remap STR_ID to a GUID
    -- They are as follows: 
    -- ELEMENT.COLUMN                                      REFERENCES ELEMENT.COLUMN
    -- --------------------------------------------------  -----------------------------------
    -- FUNCTION_ATTRIBUTE_USAGE.FUNENT_REFERENCE           FUNCTION_ENTITY_USAGE.ID
    -- COLUMN.RELATION_SELECTION_REFERENCE                 RELATION_SELECTION.ID
    -- BASE_RELATION_LOCATION.RELATION_SELECTION_REFERENCE RELATION_SELECTION.ID
    -- PASSED_VALUE.MODULE_NETWORK_REFERENCE               MODULE_NETWORK.ID
    -- DIAGRAM_ELEMENT_USAGE.CIELEMENT_REFERENCE           SDD_STRUCTURE_ELEMENTS.STR_ID
    -- PREFERENCE_SET_USAGE.CIELEMENT_REFERENCE            SDD_STRUCTURE_ELEMENTS.STR_ID
    -- USER_PREFERENCE.CIELEMENT_REFERENCE                 SDD_STRUCTURE_ELEMENTS.STR_ID
    -- MAPPING_ELEMENT.SOURCE_CIELEMENT_REFERENCE          SDD_STRUCTURE_ELEMENTS.STR_ID
    -- MAPPING_ELEMENT.STARGET_CIELEMENT_REFERENCE         SDD_STRUCTURE_ELEMENTS.STR_ID

    update ck_migration_objects set ci_created = 'Y' where ci_stage = 'CKPOPSTR';

    COMMIT;


EXCEPTION 
  when others then 
     dbms_output.put_line (' ** Exception Raised ** ');
     raise;
END;
/


