PROMPT Template script for cleaning up corrupt data at seq level 1

DECLARE
   bug_no varchar2(8) := 'CKTEMP01';
BEGIN

  update ck_migration_objects set ci_created = 'N' where ci_stage = 'CKPREMIG' and ci_name = bug_no;

-- BUG 1348991 - THE ANALYZER DELETES ROWS CREATED BY UPGRADE DESIGNER 1.3.2 -> DESIGNER 6.0  
-- Running the analyzer deletes rows that were created by the upgrade from 
-- Designer 1.3.2 to Designer 6.0.

-- This is caused by a denormalised reference property to the owning application 
-- system that was added in Designer 2.0 (APPLICATION_SYSTEM_OWNED_BY).

-- This new column APPLICATION_SYSTEM_OWNED_BY added to the CI_... views is 
-- mandatory in the Meta-Model but has not been filled in for the following 
-- objects created by the upgrade process:

--   - CONSTRAINT_USAGE
--   - ITEM
--   - ITEM_GROUP
--   - MODULE_COMPONENT
--   - MODULE_COMPONENT_INCLUSION
--   - MODULE_DETAIL_TABLE_USAGE
--   - WINDOW

-- The following updates will set the mandatory denormalized reference property
-- for the above element types and prevent effected rows from being removed by the
-- referential integrity enforcement process during migration.
  
-- CONSTRAINT_USAGE:
update ci_constraint_usages@r212 cnu
set cnu.application_system_owned_by = 
       ( select mti.application_system_owned_by
         from ci_module_detail_table_usages@r212 mti
         where cnu.table_usage_reference = mti.id
       )
where cnu.application_system_owned_by is null;

-- ITEM:
update ci_items@r212 it
set it.application_system_owned_by = 
         ( select mco.application_system_owned_by
             from ci_module_components@r212 mco
            where it.module_component_reference = mco.id
         )
where it.application_system_owned_by is null;

-- ITEM_GROUP:
update ci_item_groups@r212 ig
set ig.application_system_owned_by = 
         ( select mco.application_system_owned_by
             from ci_module_components@r212 mco
            where ig.module_component_reference = mco.id
         )
where ig.application_system_owned_by is null;
  
-- MODULE_COMPONENT:
update ci_module_components@r212 mco
set mco.application_system_owned_by = 
          ( select m.application_system_owned_by
              from ci_modules@r212 m
             where mco.GENERAL_MODULE_REFERENCE = m.id
          )
where mco.application_system_owned_by is null;
  
-- MODULE_COMPONENT_INCLUSION:
update ci_module_component_inclusions@r212 mco
set mco.application_system_owned_by = 
          ( select m.application_system_owned_by
              from ci_modules@r212 m
             where mco.GENERAL_MODULE_REFERENCE = m.id
          )
where mco.application_system_owned_by is null;
   
-- MODULE_DETAIL_TABLE_USAGE:
update ci_MODULE_DETAIL_TABLE_USAGES@r212 mti
set mti.application_system_owned_by = 
          ( select mco.application_system_owned_by
              from ci_module_components@r212 mco
             where mti.MODULE_COMPONENT_REFERENCE = mco.id
          )
where mti.application_system_owned_by is null;
  
-- WINDOW
update ci_windows@r212 win
set win.application_system_owned_by = 
          ( select m.application_system_owned_by
              from ci_modules@r212 m
             where win.GENERAL_MODULE_REFERENCE = m.id
          )
where win.application_system_owned_by is null;
  
-- MODULES not converted by 6.0 upgrade script need to be converted before
--         the rest of upgrade runs.  Otherwise the views CI_GENERAL_MODULES
--         doesn't see the un upgrade module.
-- Bug 1481765: REPOSITORY MIGRATION 6.0 TO 6I: 
--              ORA-20291 ORA-6512 ORA-4088 RME-2124 CKMIGDAT 
--
-- Copied code from upgrade script primarily to set the el_occur type correctly
-- but probably need to correct all other column values too as these modules will
-- have missed the entire 1.3.2 to 6.0 upgrade process

UPDATE sdd_elements@r212 mod1
SET    el_classn = decode(el_occur_type,'MENU','MENU','')
       ,el_degree = decode(el_comp_ind,'Y','COMPLETE',
                                   'N','NOT STARTED','NOT STARTED')
       ,el_condition = el_char1 -- return_type
       ,el_flag5 = 'N'          -- prevent_generation_flag
       ,el_occur_type = 'GEM'
       ,el_status = nvl(el_status,'N') -- candidate_flag
       ,el_comp_ind = ''
       ,el_5th_within_id = ''
       ,el_char1 = ''
       ,el_flag8 = ''
       ,el_call_meth = ''
       ,el_flag1 = ''
       ,el_flag3 = ''
       ,el_switches = ''
WHERE  mod1.el_type_of = 'MOD'
AND    mod1.el_occur_type NOT IN ('PLM', 'GEM')
AND    mod1.el_elem_owned_by  IN ( select ci_appid 
                                   from   ck_migration_objects 
                                   where  ci_stage = 'APPSEL' ) ;


UPDATE  sdd_elements@r212 mod1
SET     el_within_id = 
(       SELECT el_id
        FROM   sdd_elements@r212 lan
        WHERE  lan.el_name = decode( mod1.el_occur_type
        ,      'CHART',  'Developer/2000 Graphics'
        ,      'MENU',   'Developer/2000 Forms'
        ,      'REPORT', 'Developer/2000 Reports'
        ,      'SCREEN', 'Developer/2000 Forms'
        ,      mod1.el_occur_type
        )
        AND    lan.el_type_of = 'LAN'
        AND    lan.el_elem_owned_by = mod1.el_elem_owned_by
)
WHERE   mod1.el_within_id is null
AND     mod1.el_type_of = 'MOD'
AND     mod1.el_elem_owned_by  IN ( select ci_appid 
                                    from   ck_migration_objects 
                                    where  ci_stage = 'APPSEL' )
AND     exists 
(       SELECT null
        FROM   sdd_elements@r212 lan
        WHERE  lan.el_name = decode( mod1.el_occur_type
        ,      'CHART',  'Developer/2000 Graphics'
        ,      'MENU',   'Developer/2000 Forms'
        ,      'REPORT', 'Developer/2000 Reports'
        ,      'SCREEN', 'Developer/2000 Forms'
        ,      mod1.el_occur_type
        )
        AND    lan.el_type_of = 'LAN'
        AND    lan.el_elem_owned_by = mod1.el_elem_owned_by
)
AND     mod1.el_occur_type != 'PLM';  -- Exclude PL/SQL modules as these have no language ref

-- Bug 1905906 
-- ORA-01401: INSERTED VALUE TOO LARGE .../RME-02124: FAILED TO EXECUTE SQL STAT ...
-- The analyzer is not detecting ATTRIBUTE_VALUES where the mandatory reference to
-- the owning COLUMN, DOMAIN, MODULE_ARGUMENT, UNBOUND_ITEM or ATTRIBUTE is missing

delete from sdd_elements@r212 atv
where  atv.el_type_of = 'ATV'
and    atv.el_within_id is null
and    atv.el_elem_owned_by in    ( select ci_appid 
                                    from   ck_migration_objects 
                                    where  ci_stage = 'APPSEL' );

-- PLACE YOUR (additional) SQL CODE TO CLEAN UP CORRUPT DATA HERE

  update ck_migration_objects set ci_created = 'Y' where ci_stage = 'CKPREMIG' and ci_name = bug_no;
   
  commit;
  
EXCEPTION 
  when others then
     dbms_output.put_line (' ** Exception Raised ** ');
     dbms_output.put_line ('...ERROR: '||substrb(sqlerrm, 1, 200));
     raise;
	 
end;
/
