-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
--	Name 		: ckpopele.sql
--	Description	: This script populates the jr_id_maps database table
--			  with GUID values for all rows in the sdd_elements table
--
--	Date		: 30th November 1998
--      Bugs Fixed:
--      903163 FUNCTION GET_IRID GIVES ORA-01422 EXACT FETCH RETURNS MORE ROW THAN REQUESTED
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

DECLARE

BEGIN

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

  dbms_output.enable(1000000);

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

  dbms_output.put_line('* Inserting sdd_elements');

  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id  
                         , part_of_id                     
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.el_id
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , a.el_type_of
                         , a.el_occur_type                   
                         , NULL
                         , NULL
                         , a.el_elem_owned_by
                         , NULL
                         , nvl(a.el_within_id,a.el_elem_owned_by)
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   sdd_elements@r212   a
                   WHERE   (a.el_type_of not in ('APP', 'MCO','MTI','ITE','IGR','UPR','ACI','LAN','DIA')
                      OR  (a.el_type_of = 'ACI' and a.el_occur_type != 'NAI' ))
			   AND   a.el_elem_owned_by in (SELECT ID FROM UPG_APP);

-- APPLICATION SYSTEMS
 INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      --	used for storing property 'STATUS'
                         , owning_app              
                         , owning_app_id           
                         , type_id  
                         , part_of_id                     
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.el_id
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , a.el_type_of
                         , a.el_occur_type                   
                         , substrb(a.el_status, 1, 1)
                         , a.el_user_owned_by
                         , a.el_elem_owned_by
                         , NULL
                         , nvl(a.el_within_id,a.el_elem_owned_by)
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   sdd_elements@r212   a
                   WHERE   a.el_type_of = 'APP'
 		       AND     a.el_id in (SELECT ID FROM UPG_APP);


  -- UPG_CI_MUN
  -- Fixed Bugs : 905653
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id 
                         , part_of_id                      
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.IRID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'MUN'
                         , a.MODULE_UNIT_SUBTYPE  
                         , NULL
                         , NULL
                         , decode ( a. MODULE_UNIT_SUBTYPE , 'RLV', a.application_system_owned_by,
                                                             'RMC', a.application_system_owned_by,
                                                             'SMC', a.general_module_ref,
                                                             'SLV', a.general_module_ref)
                         , NULL
                         , a.general_module_ref
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   upg_ci_mun   a;

  -- UPG_LVI
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id  
                         , part_of_id                     
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.IRID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'LVI'
                         , NULL  
                         , NULL
                         , NULL
                         , a.application_system_owned_by 
                         , NULL
                         , a.general_module_ref   
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   upg_lvi   a;

  -- UPG_CI_MDTU   
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id  
                         , part_of_id
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.ID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'MTI'
                         , NULL 
                         , NULL
                         , NULL
                         , a.application_system_owned_by 
                         , NULL
                         , a.MODULE_COMPONENT_REFERENCE  
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   UPG_CI_MDTU   a;

  -- UPG_CI_ITEMS  
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id          
                         , part_of_id             
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.ID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'ITE'
                         , a.ITEM_TYPE
                         , NULL
                         , NULL
                         , a.application_system_owned_by 
                         , NULL
                         , decode ( a.ITEM_FOR , 'SQS', a.SQL_QUERY_SET_REFERENCE,
                                                 'MCO', a.MODULE_COMPONENT_REFERENCE)
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   UPG_CI_ITEMS   a;
  
  -- UPG_CI_IGR
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id       
                         , part_of_id                             
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.ID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'IGR'
                         , NULL
                         , NULL
                         , NULL
                         , a.application_system_owned_by 
                         , NULL
                         , nvl(a.ITEM_GROUP_REFERENCE, a.MODULE_COMPONENT_REFERENCE)
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   UPG_CI_IGR   a;

 -- UPG_CI_NAI
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id       
                         , part_of_id                             
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.ID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'ACI'
                         , 'NAI'
                         , NULL
                         , NULL
                         , a.application_system_owned_by 
                         , NULL
                         , nvl(a.MCN_REFERENCE, a.WINDOW_REFERENCE)
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   UPG_CI_NAI   a;

 
 -- UPG_CI_UPR
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id       
                         , part_of_id                             
                         , use_of_id               
                         , pop_tab )     
                  SELECT   0
                         , a.ID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'UPR'
                         , NULL
                         , NULL
                         , NULL
                         , a.application_system_owned_by 
                         , NULL
                         , a.CIELEMENT_REFERENCE
                         , NULL
                         , 'SDD_ELEMENTS'
                    FROM   UPG_CI_UPR  a;

  -- UPG_CI_LAN
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id       
                         , part_of_id                             
                         , use_of_id               
                         , pop_tab
				 , is_sys_lan
				 , lan_name )     
                  SELECT   0
                         , lan.ID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'LAN'
                         , NULL
                         , NULL
                         , NULL
                         , lan.application_system_owned_by 
                         , NULL
                         , NULL
                         , NULL
                         , 'SDD_ELEMENTS'
				 , 'N'
				 , lan.NAME
                    FROM  UPG_CI_LAN lan;

  -- UPG_CI_DIA
  INSERT INTO jr_id_maps ( trans_ref               
                         , old_id                 
                         , irid                  
                         , ivid                   
                         , element_type 
                         , occur_type                   
                         , is_a_pac_element      
                         , owning_app              
                         , owning_app_id           
                         , type_id       
                         , part_of_id                             
                         , use_of_id               
                         , pop_tab
				 , is_sys_lan
				 , lan_name )     
                  SELECT   0
                         , dia.ID
                         , jr_util.get_new_irid
                         , jr_system_util.get_new_ivid
                         , 'DIA'
                         , NULL
                         , NULL
                         , NULL
                         , dia.application_system_owned_by 
                         , NULL
                         , NULL
                         , NULL
                         , 'SDD_ELEMENTS'
			 , 'N'
			 , NULL
                    FROM  UPG_CI_DIA dia;

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

    COMMIT;

EXCEPTION 
   when others then
   dbms_output.put_line (' ** Exception Raised ** ');
   dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));
   raise;
END;
/


