[Home] [Help]
1: PACKAGE BODY AD_ZD_PREP as
2: /* $Header: ADZDPRPB.pls 120.46.12020000.12 2013/05/20 02:28:33 sstomar ship $ */
3:
4: c_package constant varchar2(80) := 'ad.plsql.ad_zd_prep.';
5: g_apps_ne_schema constant varchar2(30) :='APPS_NE';
1: PACKAGE BODY AD_ZD_PREP as
2: /* $Header: ADZDPRPB.pls 120.46.12020000.12 2013/05/20 02:28:33 sstomar ship $ */
3:
4: c_package constant varchar2(80) := 'ad.plsql.ad_zd_prep.';
5: g_apps_ne_schema constant varchar2(30) :='APPS_NE';
6: g_xla_schema constant varchar2(10) :='XLA';
7:
8: type col_owners is table of number index by varchar2(150);
488: begin
489:
490: for queue in c_queues loop
491: load(x_phase => ad_zd_parallel_exec.c_phase_drop_unused_object,
492: x_sql => 'begin ad_zd_prep.drop_queues('
493: || '''' || queue.owner || ''', '''
494: || queue.name || '''); end; ');
495: end loop;
496: end DROP_TEMP_QUEUES;
879: for t in all_types loop
880: if( is_type_evolved(t.referenced_owner, t.referenced_name) <> 'Y') then
881: -- normal UDTs
882: load(x_phase => ad_zd_parallel_exec.c_phase_copy_type,
883: x_sql => 'begin ad_zd_prep.copy_type('''
884: || t.referenced_owner || ''', '''
885: || t.referenced_name || ''','''
886: || g_apps_ne_schema || '''); end;');
887:
893:
894: -- FIX_TYPE will check dependency, if exist, will try to fix before
895: -- dropping a type.
896: load(x_phase => ad_zd_parallel_exec.c_phase_fix_type,
897: x_sql => 'begin ad_zd_prep.fix_type('''
898: || t.referenced_owner || ''', '''
899: || t.referenced_name || '''); end; ');
900:
901: end if;
986:
987: exec('GRANT EXECUTE ON "'||g_apps_ne_schema||'"."' || l_type_name || '" TO PUBLIC ', l_module, true);
988:
989: load(x_phase=> ad_zd_parallel_exec.c_phase_fix_type,
990: x_sql => 'begin ad_zd_prep.fix_type('
991: || '''' || l_owner || ''', '''
992: || l_type_name || '''); end; ');
993:
994: end loop;
1236: if(l_count > 0 ) then
1237: -- Note: Evolved types should be created in a proper order and dependent TYPEs should be VALID.
1238: -- otherwise ALTER TYPE command will fail. So let it be run by a single worker.
1239: load(x_phase => ad_zd_parallel_exec.c_phase_copy_evolved_type,
1240: x_sql => 'begin ad_zd_prep.copy_evolved_types; end; ');
1241:
1242: end if;
1243: log(l_module, 'PROCEDURE', 'end');
1244: end COPY_EVOLVED_TYPES_WRAPPER;
1320: -- sys.dbms_objects_utils.update_types('APPS', APPS_NE', NULL, TRUE) , instead of
1321: -- calling for specific TYPE
1322: --
1323: if(l_flag) then
1324: l_sql := 'begin ad_zd_prep.fix_types(' ||
1325: 'x_source_schema =>''' || g_udt_obj_list(idx).owner || ''', ' ||
1326: 'x_target_schema => '''|| g_apps_ne_schema || ''') ; end;' ;
1327:
1328: load(x_phase => ad_zd_parallel_exec.c_phase_fix_column,
1370: --
1371: -- Merging with COPY_TYPE phase as both are independent.
1372: while (c_queues%found) loop
1373: load(x_phase => ad_zd_parallel_exec.c_phase_copy_type,
1374: x_sql => 'begin ad_zd_prep.stop_queue(''' || l_queue ||'''); end;');
1375:
1376: fetch c_queues INTO l_queue, l_queue_type;
1377: end loop;
1378: -- close cursor
1382:
1383: -- This phase should be executed after DROP_OBJECT, so if still there is any UDT reference
1384: -- , can be fixed.
1385: load(x_phase => ad_zd_parallel_exec.c_phase_recreate_aq_object,
1386: x_sql => 'begin ad_zd_prep.recreate_aq_object(''' || x_owner ||''', ''' || x_table ||'''); end;');
1387:
1388: log(l_module, 'PROCEDURE', 'end');
1389: end FIX_QUEUE;
1390:
1620: begin
1621: log(l_module, 'PROCEDURE', 'begin');
1622: for rec in c_public_synonyms loop
1623: load(x_phase=> ad_zd_parallel_exec.c_phase_fix_public_synonym,
1624: x_sql => 'begin ad_zd_prep.fix_public_synonym(''' || rec.synonym_name || ''', ''' ||
1625: rec.table_owner || ''',''' || rec.table_name ||''','''|| rec.db_link || '''); end;');
1626:
1627: end loop;
1628: log(l_module, 'PROCEDURE', 'end');
1783: begin
1784: log(l_module, 'PROCEDURE', 'begin');
1785: for rec in ctxpkg loop
1786: load(x_phase => ad_zd_parallel_exec.c_phase_drop_object,
1787: x_sql => 'begin ad_zd_prep.drop_ctxsys_pkg(''' || rec.name || '''); end;');
1788:
1789: end loop;
1790:
1791: -- drop problematic synonyms
1790:
1791: -- drop problematic synonyms
1792: for rec in c_synonyms loop
1793: load(x_phase => ad_zd_parallel_exec.c_phase_drop_object,
1794: x_sql => 'begin ad_zd_prep.drop_ctxsys_synonym(''' || rec.synonym_name || '''); end;');
1795: end loop;
1796: log(l_module, 'PROCEDURE', 'end');
1797: end FIX_CTXSYS;
1798:
1932:
1933: -- This is fine to run XML schema mover for all users as it will pick-up those
1934: -- users which are registered in fnd_oracle_userid (Only cost here is performance)
1935: -- TODO: Wait for DB fix 16286729, otherwise locking issue might occur.
1936: ad_zd_prep.move_xml_schemas;
1937:
1938: -- CASE#3: MV is dependent on function, throw error as this can be fixed automatically.
1939: when (l_invalidated_object.d_type = 42 and
1940: l_invalidated_object.p_type = 8) then
1961: -- Fix public synonyms.
1962: -- NOTE: Public synonyms will NOT have any record in sys.dependencies$ table
1963: for l_rec in c_public_synonyms loop
1964: log(l_module, 'STATEMENT', 'Fix PUBLIC synonym: ' || l_rec.synonym_name);
1965: ad_zd_prep.fix_public_synonym(l_rec.synonym_name, l_rec.table_owner, l_rec.table_name, l_rec.db_link);
1966: end loop;
1967:
1968: -- If still have noneditioned objects dependent on editioned objects?
1969: for l_invalidated_object in c_invalidated_objects(x_user) loop
2118: log(l_module, 'PROCEDURE', 'begin');
2119:
2120: for rec in c_users loop
2121: load(x_phase => ad_zd_parallel_exec.c_phase_enable_editioning,
2122: x_sql => 'begin ad_zd_prep.enable_user_4edition('''||rec.username||'''); end;');
2123:
2124: end loop;
2125: log(l_module, 'PROCEDURE', 'end');
2126: end ENABLE_EDITIONS;
2137:
2138: begin
2139:
2140: log(c_module, 'PROCEDURE', 'begin' );
2141: dbms_application_info.set_module('AD_ZD_PREP', 'DDL GENERATION');
2142:
2143: log(c_module, 'STATEMENT', 'Cleaning up AD_ZD_DDL_HANDLER table' );
2144:
2145: -- **** Clean ONLY DB PREP data, as SEED-UPGRADE data
2204: 'E-Business Suite Database Preparation for Editions: '
2205: || sqlcode || ' ' || substr(sqlerrm, 1, 64));
2206: end do_prep;
2207:
2208: end AD_ZD_PREP;