Rem    NAME 
Rem      ckupgtab.sql
Rem    DESCRIPTION
Rem      Creates Tables used for Specific PreUpgrades
Rem    RETURNS
Rem 	 None
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    CREATED
Rem	  12/04/1999

-- -- ---- -- ---- -- ---- -- --
--   Application Systems
-- -- ---- -- ---- -- ---- -- --
PROMPT
PROMPT * Creating Table UPG_APP for Application Systems
PROMPT

CREATE TABLE UPG_APP (id)
AS ( SELECT CI_APPID
     FROM CK_MIGRATION_OBJECTS
     WHERE CI_STAGE = 'APPSEL' )
/

-- -- ---- -- ---- -- ---- -- --
--   Modules
-- -- ---- -- ---- -- ---- -- --
-- 913673 - Add MOD_UPG To track Module Upgrade *** Not used for data migration ****

PROMPT
PROMPT * Creating Table UPG_MOD for Modules
PROMPT

CREATE TABLE UPG_MOD (id number)
/

-- -- ---- -- ---- -- ---- -- --
--    Module Units
-- -- ---- -- ---- -- ---- -- --
PROMPT
PROMPT * Creating Table UPG_CI_MUN for Module Units
PROMPT

CREATE TABLE UPG_CI_MUN ( 
  IRID                      NUMBER        , 
  IVID                      NUMBER        , 
  APPLICATION_SYSTEM_OWNED_BY NUMBER      ,
  PAC_REF                   NUMBER        , 
  PARENT_IVID               NUMBER        , 
  TYPES                     NUMBER(38), 
  ELEMENT_TYPE_NAME         VARCHAR2(10)  DEFAULT 'MUN', 
  DATE_CREATED              DATE          DEFAULT SYSDATE, 
  CREATED_BY                VARCHAR2(30)  DEFAULT 'USER', 
  DATE_CHANGED              DATE, 
  CHANGED_BY                VARCHAR2(30), 
  NOTM                      NUMBER(38)    DEFAULT 0, 
  GENERAL_MODULE_REF        NUMBER(38), 
  MODULE_COMPONENT_REF      NUMBER(38), 
  NAME                      VARCHAR2(30), 
  SHORT_DESCRIPTION         VARCHAR2(60), 
  IMPLEMENTATION_ITEM       VARCHAR2(30), 
  LANGUAGE_REF              NUMBER(38), 
  MODULE_LEVEL_FLAG         VARCHAR2(1), 
  DATASOURCE_TYPE           VARCHAR2(16), 
  DATATARGET_TYPE           VARCHAR2(16), 
  INSERT_FLAG               VARCHAR2(1), 
  DELETE_FLAG               VARCHAR2(1), 
  UPDATE_FLAG               VARCHAR2(1), 
  SELECT_FLAG               VARCHAR2(1), 
  AUTOQUERY_FLAG            VARCHAR2(1), 
  DISPLAY_TITLE             VARCHAR2(70), 
  LAYOUT_STYLE              VARCHAR2(20), 
  ROWS_DISPLAYED            NUMBER(10), 
  REQ_OVERFLOW              VARCHAR2(30), 
  REQ_PAGE_WIDTH            NUMBER(6), 
  REQ_PAGE_HEIGHT           NUMBER(6), 
  REQ_VIEW_WIDTH            NUMBER(6), 
  REQ_VIEW_HEIGHT           NUMBER(6), 
  MODULE_UNIT_SUBTYPE       VARCHAR2(3), 
  HELP_CONTEXT_ID_PREFIX    NUMBER(3), 
  LIST_OF_VALUES_SUBTYPE    VARCHAR2(3), 
  MODULE_COMPONENT_SUBTYPE  VARCHAR2(3), 
  RUNTIME_PATHNAME          VARCHAR2(240), 
  GENERATED_FLAG            VARCHAR2(1), 
  HELP_CONTEXT_ID_SUFFIX    NUMBER(5), 
  USRX0                     VARCHAR2(240), 
  USRX1                     VARCHAR2(240), 
  USRX2                     VARCHAR2(240), 
  USRX3                     VARCHAR2(240), 
  USRX4                     VARCHAR2(240), 
  USRX5                     VARCHAR2(240), 
  USRX6                     VARCHAR2(240), 
  USRX7                     VARCHAR2(240), 
  USRX8                     VARCHAR2(240), 
  USRX9                     VARCHAR2(240), 
  USRX10                    VARCHAR2(240), 
  USRX11                    VARCHAR2(240), 
  USRX12                    VARCHAR2(240), 
  USRX13                    VARCHAR2(240), 
  USRX14                    VARCHAR2(240), 
  USRX15                    VARCHAR2(240), 
  USRX16                    VARCHAR2(240), 
  USRX17                    VARCHAR2(240), 
  USRX18                    VARCHAR2(240), 
  USRX19                    VARCHAR2(240) )
/


-- -- ---- -- ---- -- ---- -- --
--   List of Values Inclusions
-- -- ---- -- ---- -- ---- -- --

PROMPT
PROMPT * Creating Table UPG_CI_LVI for  List of Values Inclusions
PROMPT

CREATE TABLE UPG_LVI ( 
  IRID                NUMBER        , 
  IVID                NUMBER        , 
  APPLICATION_SYSTEM_OWNED_BY NUMBER      ,
  PAC_REF             NUMBER        , 
  PARENT_IVID         NUMBER        , 
  TYPES               NUMBER(38), 
  ELEMENT_TYPE_NAME   VARCHAR2(10)  DEFAULT 'LVI', 
  DATE_CREATED        DATE          DEFAULT SYSDATE, 
  CREATED_BY          VARCHAR2(30)  DEFAULT 'USER', 
  DATE_CHANGED        DATE, 
  CHANGED_BY          VARCHAR2(30), 
  NOTM                NUMBER(38)    DEFAULT 0, 
  GENERAL_MODULE_REF  NUMBER(38), 
  LIST_OF_VALUES_REF  NUMBER(38), 
  USAGE_SEQUENCE      NUMBER(6), 
  ITEM_REF            NUMBER(38), 
  RETURN_LIST         VARCHAR2(2000), 
  LONG_LIST_FLAG      VARCHAR2(1), 
  USED_FOR            VARCHAR2(8), 
  RESTRICTION_CLAUSE  VARCHAR2(10), 
  REQ_VIEW_POSX       NUMBER(6), 
  REQ_VIEW_POSY       NUMBER(6) ) 
/

-- -- ---- -- ---- -- ---- -- --
-- Module Detail Table Usages
-- -- ---- -- ---- -- ---- -- --

PROMPT
PROMPT * Creating Table UPG_CI_MDTU for Module Detail Table Usages
PROMPT

CREATE TABLE UPG_CI_MDTU AS
         (SELECT * 
          FROM   CI_MODULE_DETAIL_TABLE_USAGES@r212
  	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP))
/


-- -- ---- -- ---- -- ---- -- --
--    Data Bound Items
-- -- ---- -- ---- -- ---- -- --

PROMPT
PROMPT * Creating Table UPG_CI_ITEMS for All Items
PROMPT

CREATE TABLE UPG_CI_ITEMS AS  
SELECT sdd_el.el_elem_owned_by  application_system_owned_by 
      ,sdd_el.el_user_changed_by changed_by 
      ,sdd_el.el_sd_dp_max checkout_version 
      ,sdd_el.el_2nd_within_id column_reference 
      ,sdd_el.el_flag5 context_flag 
      ,sdd_el.el_flag9 context_option_query_flag 
      ,sdd_el.el_user_created_by created_by 
      ,sdd_el.el_char1 datatype 
      ,sdd_el.el_date_changed date_changed 
      ,sdd_el.el_date_created date_created 
      ,sdd_el.el_def_val default_value 
      ,sdd_el.el_flag6 display_flag 
      ,sdd_el.el_format display_format 
      ,sdd_el.el_max_len2 display_height 
      ,sdd_el.el_format2 display_type 
      ,sdd_el.el_max_len display_width 
      ,sdd_el.el_3rd_within_id domain_reference 
      ,sdd_el.el_type_of element_type_name 
      ,sdd_el.el_col_occ field_name 
      ,sdd_el.el_size help_context_id_suffix 
      ,sdd_el.el_as_constraints highlighting 
      ,sdd_el.el_def_scr_help hint_text 
      ,sdd_el.el_id id 
      ,sdd_el.el_env_name implementation_item 
      ,sdd_el.el_flag2 insert_flag 
      ,sdd_el.el_srce_type item_for 
      ,sdd_el.el_5th_within_id item_group_reference 
      ,sdd_el.el_occur_type item_type 
      ,sdd_el.el_act_code justification 
      ,sdd_el.el_lv_ind create_lookup_item_flag 
      ,sdd_el.el_within_id module_component_reference 
      ,sdd_el.el_name name 
      ,sdd_el.el_flag4 nullify_flag 
      ,sdd_el.el_notm number_of_times_modified 
      ,sdd_el.el_seq2 order_sequence 
      ,sdd_el.el_def_scr_prompt prompt 
      ,sdd_el.el_6th_within_id detail_table_usage_reference 
      ,sdd_el.el_col_sign range_search_flag 
      ,sdd_el.el_comment remark 
      ,sdd_el.el_init_val reset_group 
      ,sdd_el.el_switches reset_level 
      ,sdd_el.el_flag1 select_flag 
      ,sdd_el.el_col_stor_format show_meaning 
      ,sdd_el.el_flag9 soft_lov 
      ,sdd_el.el_flag7 sorting_order 
      ,sdd_el.el_within_id sql_query_set_reference 
      ,sdd_el.el_syssort display_in_lov_flag 
      ,sdd_el.el_flag8 truncate_flag 
      ,sdd_el.el_ty types 
      ,sdd_el.el_classn unbound_type 
      ,sdd_el.el_flag3 update_flag 
      ,sdd_el.el_seq usage_sequence 
	,sdd_el.el_usrx0 USER_DEFINED_PROPERTY_0 
      ,sdd_el.el_usrx1 USER_DEFINED_PROPERTY_1
	,sdd_el.el_usrx2 USER_DEFINED_PROPERTY_2
	,sdd_el.el_usrx3 USER_DEFINED_PROPERTY_3
	,sdd_el.el_usrx4 USER_DEFINED_PROPERTY_4
	,sdd_el.el_usrx5 USER_DEFINED_PROPERTY_5
	,sdd_el.el_usrx6 USER_DEFINED_PROPERTY_6
	,sdd_el.el_usrx7 USER_DEFINED_PROPERTY_7
	,sdd_el.el_usrx8 USER_DEFINED_PROPERTY_8
	,sdd_el.el_usrx9 USER_DEFINED_PROPERTY_9
	,sdd_el.el_usrx10	USER_DEFINED_PROPERTY_10
	,sdd_el.el_usrx11 USER_DEFINED_PROPERTY_11
	,sdd_el.el_usrx12 USER_DEFINED_PROPERTY_12
	,sdd_el.el_usrx13 USER_DEFINED_PROPERTY_13
	,sdd_el.el_usrx14 USER_DEFINED_PROPERTY_14
	,sdd_el.el_usrx15 USER_DEFINED_PROPERTY_15
	,sdd_el.el_usrx16 USER_DEFINED_PROPERTY_16
	,sdd_el.el_usrx17 USER_DEFINED_PROPERTY_17
	,sdd_el.el_usrx18 USER_DEFINED_PROPERTY_18
	,sdd_el.el_usrx19 USER_DEFINED_PROPERTY_19
FROM   sdd_elements@r212 sdd_el 
WHERE  sdd_el.el_type_of = 'ITE'
AND    sdd_el.el_elem_owned_by in (SELECT ID FROM UPG_APP)
/

-- -- ---- -- ---- -- ---- -- --
--    Item Groups
-- -- ---- -- ---- -- ---- -- --

PROMPT
PROMPT * Creating Table UPG_CI_IGR for Item Groups
PROMPT

CREATE TABLE UPG_CI_IGR AS
         (SELECT * 
          FROM   CI_ITEM_GROUPS@r212
	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP))
/


-- -- ---- -- ---- -- ---- -- --
--    Multi-Line Text
-- -- ---- -- ---- -- ---- -- --
-- 885687  Specific Upgrade Process will require the RAU to Create/populate a UPG_RM_TEXT_LINES table
-- 951768  ORA-01403:NO DATA FOUND WHEN INSERTING INTO RM_TEXT_LINES
-- 1316504 MULTILINE TEXT PROPERTIES - SUMMARY, OBJECTIVES FOR APPLICATION SYS NOT MIGRATED

PROMPT
PROMPT * Creating Table UPG_RM_TEXT_LINES for Mult-Line-Text
PROMPT

CREATE TABLE UPG_RM_TEXT_LINES AS
         (SELECT TXT_REF
		    ,TXT_SEQ
		    ,TXT_TYPE
                ,TXT_TEXT
                ,TXT_NOTM			 
          FROM  RM_TEXT_LINES@r212 
          WHERE TXT_TYPE IN ( select TEXT_TYPE 
 				      from RM_TEXT_TYPES@r212 )
	    AND TXT_REF IN ( select EL_ID
				   		 from SDD_ELEMENTS@r212 EL, UPG_APP UAPP
	  				     where EL.EL_ELEM_OWNED_BY = UAPP.ID 
                         union all
				   		 select STR_ID
	         			 from SDD_STRUCTURE_ELEMENTS@r212, UPG_APP UAPP
  				   		 where STR_ELEM_OWNED_BY = UAPP.ID
				   		 union all
				   		 select al.AL_ID
	                     from ck_application_logic@r212 al, sdd_elements@r212 el, upg_app uapp
					     where al.al_repos_element = el.el_id
                         and el.el_elem_owned_by = uapp.id	                          
						 union all
						 select id from upg_app
				 ))
/


-- -- ---- -- ---- -- ---- -- --
--    Navigation Action Items
-- -- ---- -- ---- -- ---- -- --
-- 882490 Specific Upgrade Process will require the RAU to Create/populate a UPG_CI_NAI table

PROMPT
PROMPT * Creating Table UPG_CI_NAI for Navigation Action Items
PROMPT

CREATE TABLE UPG_CI_NAI AS
         (SELECT * 
          FROM   CI_NAVIGATION_ACTION_ITEMS@r212
	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP))
/


-- -- ---- -- ---- -- ---- -- --
--    Languages
-- -- ---- -- ---- -- ---- -- --
--
-- B871929 Need to identify baseline contents of system folder   

PROMPT
PROMPT * Creating Table UPG_CI_LAN for Languages
PROMPT

CREATE TABLE UPG_CI_LAN AS
          (SELECT APPLICATION_SYSTEM_OWNED_BY,
			ID,
 			CHANGED_BY,
 		      COMMAND_LINE,
			CREATED_BY,
		      DATE_CHANGED,
                  DATE_CREATED,
		      DESCRIPTION,
                  ELEMENT_TYPE_NAME,
                  GENERATABLE_FLAG,
                  INTERNAL_NAME,
                  LANGUAGE_TYPE, 
                  DECODE(INTERNAL_NAME, 'Forms','Oracle Forms', 'Web','Web PL/SQL', 'Reports', 'Oracle Reports',
                         'D2KLIB','Oracle Common Library',  NAME ) NAME, 
                  RUNTIME_DIRECTORY,
                  SOURCE_DIRECTORY,
                  NUMBER_OF_TIMES_MODIFIED,
 			USER_DEFINED_PROPERTY_0,        
 			USER_DEFINED_PROPERTY_1,        
			USER_DEFINED_PROPERTY_2,        
 			USER_DEFINED_PROPERTY_3,       
 			USER_DEFINED_PROPERTY_4,        
 			USER_DEFINED_PROPERTY_5,        
 			USER_DEFINED_PROPERTY_6,        
 			USER_DEFINED_PROPERTY_7,        
 			USER_DEFINED_PROPERTY_8,        
 			USER_DEFINED_PROPERTY_9,        
 			USER_DEFINED_PROPERTY_10,               
 			USER_DEFINED_PROPERTY_11,               
 			USER_DEFINED_PROPERTY_12,               
 			USER_DEFINED_PROPERTY_13,               
 			USER_DEFINED_PROPERTY_14,               
 			USER_DEFINED_PROPERTY_15,               
 			USER_DEFINED_PROPERTY_16,               
			USER_DEFINED_PROPERTY_17,               
 			USER_DEFINED_PROPERTY_18,               
 			USER_DEFINED_PROPERTY_19 
          FROM   CI_LANGUAGES@r212
	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP))
/

-- -- ---- -- ---- -- ---- -- --
--    Application logic
-- -- ---- -- ---- -- ---- -- --
-- 885683 Specific Upgrade Process will require the RAU to Create/populate a UPG_APPLICATION_LOGIC table

PROMPT
PROMPT * Creating Table UPG_APPLICATION_LOGIC for Application Logic
PROMPT

CREATE TABLE UPG_APPLICATION_LOGIC AS
         (SELECT * 
          FROM   CK_APPLICATION_LOGIC@r212
	    WHERE AL_REPOS_ELEMENT IN ( select EL_ID
						  from SDD_ELEMENTS@r212, UPG_APP UAPP
						  where EL_ELEM_OWNED_BY = UAPP.ID
						 ))
/

-- -- ---- -- ---- -- ---- -- --
--    Preference Set Usages
-- -- ---- -- ---- -- ---- -- --
-- 885690 Specific Upgrade Process will require the RAU to Create/populate a UPG_CI_PRU table

PROMPT
PROMPT * Creating Table UPG_CI_PRU for Preference Set Usages
PROMPT

CREATE TABLE UPG_CI_PRU AS
         (SELECT * 
          FROM   ci_preference_set_usages@r212
	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP))
/


-- -- ---- -- ---- -- ---- -- --
--   User Preferences
-- -- ---- -- ---- -- ---- -- --
-- 885691 Specific Upgrade Process will require the RAU to Create/populate a UPG_CI_UPR table


PROMPT
PROMPT * Creating Table UPG_CI_UPR for Preference Sets
PROMPT

CREATE TABLE UPG_CI_UPR AS
         (SELECT * 
          FROM   ci_user_preferences@r212
	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP))
/

-- -- ---- -- ---- -- ---- -- --
--   Module Networks
-- -- ---- -- ---- -- ---- -- --
--    Specific Upgrade Process will require the RAU to Create/populate a UPG_CI_MODMOD table


PROMPT
PROMPT * Creating Table UPG_CI_MODMOD for Module Networks
PROMPT

CREATE TABLE UPG_CI_MODMOD AS
         (SELECT * 
          FROM   ci_module_networks@r212
	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP))
/

-- -- ---- -- ---- -- ---- -- --
--   Diagrams
-- -- ---- -- ---- -- ---- -- --
--    Specific Upgrade Process will require the RAU to Create/populate a UPG_CI_DIA table


PROMPT
PROMPT * Creating Table UPG_CI_DIA for Diagrams
PROMPT

CREATE TABLE UPG_CI_DIA AS
         (SELECT * 
          FROM   ci_diagrams@r212
	    WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP)
	    AND DIAGRAM_TYPE != 'ATD')
/

-- -- ---- -- ---- -- ---- -- --
--   Diagram Element Usages
-- -- ---- -- ---- -- ---- -- --
--    Specific Upgrade Process will require the RAU to Create/populate a UPG_CI_DEU table


PROMPT
PROMPT * Creating Table UPG_CI_DEU for Diagram Element Usages
PROMPT

CREATE TABLE UPG_CI_DEU AS
         (SELECT * 
          FROM   ci_diagram_element_usages@r212
	  WHERE APPLICATION_SYSTEM_OWNED_BY IN (SELECT ID FROM UPG_APP)
	  AND diagram_reference in (SELECT ID FROM UPG_CI_DIA))
/

-- -- ---- -- ---- -- ---- -- --
--   Global Names
-- -- ---- -- ---- -- ---- -- --
--    Specific Upgrade Process will require the RAU to Create/populate a UPG_GLOBAL_NAMES table


PROMPT
PROMPT * Creating Table UPG_ for Global Names
PROMPT

CREATE TABLE UPG_GLOBAL_NAMES AS
         (SELECT * 
          FROM   SDD_GLOBAL_NAMES
          WHERE 1 = 2)
/


-- -- ---- -- ---- -- ---- -- --
--   Dependencies
-- -- ---- -- ---- -- ---- -- --
--    Specific Upgrade Process will require the RAU to Create/populate a UPG_DEPENDENCIES table


PROMPT
PROMPT * Creating Table UPG_ for Dependencies
PROMPT

CREATE TABLE UPG_DEPENDENCIES AS
         (SELECT * 
          FROM   SDD_DEPENDENCIES
          WHERE 1 = 2)
/


-- -- ---- -- ---- -- ---- -- --
--   Extended Elements
-- -- ---- -- ---- -- ---- -- --
PROMPT
PROMPT * Creating Table UPG_ for Extended Elements
PROMPT

CREATE TABLE UPG_SDD_UEE AS
         (SELECT sdd_el.el_elem_owned_by APPLICATION_SYSTEM_OWNED_BY
		    ,sdd_el.el_id ID
 		    ,sdd_el.el_type_of ELEMENT_TYPE_NAME
		    ,sdd_el.el_date_created DATE_CREATED
 	          ,sdd_el.el_user_created_by created_by 
    		    ,sdd_el.el_date_changed DATE_CHANGED
		    ,sdd_el.el_user_changed_by CHANGED_BY 
                ,sdd_el.el_notm NOTM
		    ,sdd_el.el_name NAME
		    ,sdd_el.el_comment COMMENTS
	  	    ,sdd_el.el_usrx0 USRX0 
 		    ,sdd_el.el_usrx1 USRX1
		    ,sdd_el.el_usrx2 USRX2
		    ,sdd_el.el_usrx3 USRX3
		    ,sdd_el.el_usrx4 USRX4
	    	    ,sdd_el.el_usrx5 USRX5
	   	    ,sdd_el.el_usrx6 USRX6
                ,sdd_el.el_usrx7 USRX7
                ,sdd_el.el_usrx8 USRX8
                ,sdd_el.el_usrx9 USRX9
                ,sdd_el.el_usrx10 USRX10
		    ,sdd_el.el_usrx11 USRX11
		    ,sdd_el.el_usrx12 USRX12
	     	    ,sdd_el.el_usrx13 USRX13
		    ,sdd_el.el_usrx14 USRX14
	 	    ,sdd_el.el_usrx15 USRX15
	          ,sdd_el.el_usrx16 USRX16
		    ,sdd_el.el_usrx17 USRX17
	          ,sdd_el.el_usrx18 USRX18
		    ,sdd_el.el_usrx19 USRX19
          FROM   SDD_ELEMENTS@r212 sdd_el, CIUE_ELEMENT_TYPES CET
          WHERE  sdd_el.EL_TYPE_OF = CET.SHORT_NAME
          AND    CET.IS_USER_TYPE = 'Y'
	    AND    sdd_el.el_elem_owned_by in (SELECT ID FROM UPG_APP))
/

-- -- ---- -- ---- -- ---- -- --
--   Extended Structure Elements
-- -- ---- -- ---- -- ---- -- --
PROMPT
PROMPT * Creating Table UPG_ for Extended Structure Elements
PROMPT

CREATE TABLE UPG_SDD_UES AS
         (SELECT sdd_str.str_elem_owned_by APPLICATION_SYSTEM_OWNED_BY
		    ,sdd_str.str_id ID
 		    ,sdd_str.str_type_of ELEMENT_TYPE_NAME
		    ,sdd_str.str_date_created DATE_CREATED
 	          ,sdd_str.str_created_by created_by 
    		    ,sdd_str.str_date_changed DATE_CHANGED
		    ,sdd_str.str_changed_by CHANGED_BY 
                ,sdd_str.str_notm NOTM
		    ,sdd_str.str_part_of PART_OF_REF
		    ,sdd_str.str_use_of USE_OF_REF
		    ,sdd_str.str_comment COMMENTS
	  	    ,sdd_str.str_usrx0 USRX0 
 		    ,sdd_str.str_usrx1 USRX1
		    ,sdd_str.str_usrx2 USRX2
		    ,sdd_str.str_usrx3 USRX3
		    ,sdd_str.str_usrx4 USRX4
	    	    ,sdd_str.str_usrx5 USRX5
	   	    ,sdd_str.str_usrx6 USRX6
                ,sdd_str.str_usrx7 USRX7
                ,sdd_str.str_usrx8 USRX8
                ,sdd_str.str_usrx9 USRX9
                ,sdd_str.str_usrx10 USRX10
		    ,sdd_str.str_usrx11 USRX11
		    ,sdd_str.str_usrx12 USRX12
	     	    ,sdd_str.str_usrx13 USRX13
		    ,sdd_str.str_usrx14 USRX14
	 	    ,sdd_str.str_usrx15 USRX15
	          ,sdd_str.str_usrx16 USRX16
		    ,sdd_str.str_usrx17 USRX17
	          ,sdd_str.str_usrx18 USRX18
		    ,sdd_str.str_usrx19 USRX19
          FROM   SDD_STRUCTURE_ELEMENTS@r212 sdd_str, CIUE_ASSOCIATION_TYPES CAT
          WHERE  sdd_str.STR_TYPE_OF = CAT.SHORT_NAME
          AND    CAT.IS_USER_TYPE = 'Y'
          AND    sdd_str.STR_ELEM_OWNED_BY in (SELECT ID FROM UPG_APP)
          UNION
	    SELECT sdd_str.str_elem_owned_by APPLICATION_SYSTEM_OWNED_BY
		    ,sdd_str.str_id ID
 		    ,sdd_str.str_type_of ELEMENT_TYPE_NAME
		    ,sdd_str.str_date_created DATE_CREATED
 	          ,sdd_str.str_created_by created_by 
    		    ,sdd_str.str_date_changed DATE_CHANGED
		    ,sdd_str.str_changed_by CHANGED_BY 
                ,sdd_str.str_notm NOTM
		    ,sdd_str.str_part_of PART_OF_REF
		    ,sdd_str.str_use_of USE_OF_REF
		    ,sdd_str.str_comment COMMENTS
	  	    ,sdd_str.str_usrx0 USRX0 
 		    ,sdd_str.str_usrx1 USRX1
		    ,sdd_str.str_usrx2 USRX2
		    ,sdd_str.str_usrx3 USRX3
		    ,sdd_str.str_usrx4 USRX4
	    	    ,sdd_str.str_usrx5 USRX5
	   	    ,sdd_str.str_usrx6 USRX6
                ,sdd_str.str_usrx7 USRX7
                ,sdd_str.str_usrx8 USRX8
                ,sdd_str.str_usrx9 USRX9
                ,sdd_str.str_usrx10 USRX10
		    ,sdd_str.str_usrx11 USRX11
		    ,sdd_str.str_usrx12 USRX12
	     	    ,sdd_str.str_usrx13 USRX13
		    ,sdd_str.str_usrx14 USRX14
	 	    ,sdd_str.str_usrx15 USRX15
	          ,sdd_str.str_usrx16 USRX16
		    ,sdd_str.str_usrx17 USRX17
	          ,sdd_str.str_usrx18 USRX18
		    ,sdd_str.str_usrx19 USRX19
          FROM   SDD_STRUCTURE_ELEMENTS@r212 sdd_str, 
		     CIUE_ASSOCIATION_TYPES CAT,
                 CK_CIUE_UPGRADE_ACTIONS CIUA
          WHERE  sdd_str.STR_TYPE_OF = CIUA.OLD_SN
	    AND    CIUA.NEW_SN = CAT.SHORT_NAME
          AND    CAT.IS_USER_TYPE = 'Y'
	    AND    sdd_str.STR_ELEM_OWNED_BY in (SELECT ID FROM UPG_APP))
/


-- -- ---- -- ---- -- ---- -- --
--   Access Rights
-- -- ---- -- ---- -- ---- -- --
--    Specific Upgrade Process will require the RAU to Create/populate a UPG_SDW_ACCESS_RIGHTS table


REM 1300096 Removed USR_GRANTED_BY clause
PROMPT
PROMPT * Creating Table UPG_ for Access Rights
PROMPT

CREATE TABLE UPG_SDW_ACCESS_RIGHTS AS
         (SELECT *
          FROM  SDW_ACCESS_RIGHTS@R212
          WHERE AR_APPID_FOR IN ( SELECT ID FROM UPG_APP )
	      AND AR_USR_GRANTED_TO IN ( SELECT USERNAME FROM SDW_USERS ))
/
