Only Four Left…

From time to time, a post appears in the OTN E-Business Suite Forums of the following form:

We are planning our upgrade to R12, and need to know how many new tables/forms/views/etc there are in the new version. Where are the documents that list these changes?

In these cases, forum posters are usually directed (quite appropriately) to review the upgrade manuals and Release Content documents for R12, which probably contain pieces of the answer to the question. To the best of my knowledge, however, there is no comprehensive, monolithic list of schema changes that occur in the course of an R12 upgrade. One significant reason is the resource burden involved in maintaining a such a list. The upgrade process has so many start and end points. Are you upgrading from 11.0.x? 11.5.8? 11.5.10? Which family packs and CUs are applied? Are you upgrading to to 12.0.6? 12.1.1? 12.1.2? Keeping track of all of those permutations, while perhaps possible, would accomplish little beyond crushing the spirit of a small army of interns.

This is not the question we're looking for; you can go about your business...

An even bigger reason to not be concerned about the number of schema changes in the R12 upgrade: it doesn't seem to be particularly useful information. I can't think of any "effort estimate" metric that would benefit from know knowing the number of new tables, forms, etc. that have been changed, added, or removed. Even knowing that specific tables and forms have disappeared or changed isn't very helpful. After all, you're going to be running thorough tests of any custom reports and forms, anyway, as well as testing to see what common user activities are impacted by the upgrade. Right? (Please say, "right!") We found out pretty quickly, for example, that there were some tables used by our custom reports that had been emptied; the data had been moved to another schema, and our custom reports returned no rows. Good times, and a simple "does this table still exist in R12?" test would not have caught that new feature.

After such a long setup, you must know where this is going. During a long ago (2007-2008) R12 upgrade project, I actually ran some queries comparing the contents of a few key tables (ALL_OBJECTS, FND_APPLICATION, etc) before and after the upgrade. I'll present the results of those queries below. Perhaps someone can find some use for them that I was unable to divine.

Data of dubious utility

Anyone who's read more than two posts here knows that I disclaim like there's no tomorrow. This post is no different:

• The data presented is for an 11.5.10.2 system upgraded to 12.0.3. Your mileage will vary, because no one's upgrading to 12.0.3 these days. At least, I hope not.
• This data is based on notes and SQLPLUS spool files that are over 2 years old by now, which is not exactly "fresh." Since I no longer have the source data, I can't even do a useful sanity check to be sure I extracted it all correctly. Where possible (i.e. salvageable from my logs), I have included the text of my queries, primarily so readers can understand how I generated the data. There is no doubt substantial room for improvement. Many of the queries are slapdash jobs, since I was just doing informal research. Nonetheless, I believe in showing my work, even if it's sometimes embarrassing.
• If you read Oracle documentation that contradicts anything that you see here, you should definitely trust those docs. Furthermore, if your own investigations contradict what you see here, you should trust your own work.

Categories

After all of that, if you're still on board, follow the links below to jump to the relevant sections. If you get any interesting nuggets out of these lists, then I'm glad I could help. Otherwise, you could just summarize the answer to the "how many changes" question as, "Whoa, lots."

Applications
Concurrent Programs
Forms
Profile Options
Database Objects

Applications

The following tables summarize the number of new and removed applications, as well as an attempt at listing the applications that are marked obsolete, but not actually removed. The major assumption is that the application_id does not change between versions, though I didn't do much to confirm that assumption. The absence of a "removed" table suggests that I didn't find any deleted application_ids, but I don't rule out the possibility that I was just lazy.

New applications

SQL>select application_short_name
 2       , application_name
 3    from r12_application_tl r12 join r12_application using (application_id)
 4   where application_id not in
 5                        (
 6                         select application_id
 7                           from r11_application r11
 8                        )
 9   order by 1
10  /

Renamed applications

SQL>select application_short_name
 2       , r12.application_name new_name
 3       , r11.application_name old_name
 4    from r12_application_tl r12 join r12_application using (application_id)
 5         join r11_application_tl r11 using (application_id)
 6   where not regexp_like(r12.application_name,'obsolete','i')
 7     and r11.application_name <> r12.application_name
 8  /

Obsolete applications

SQL>select application_short_name
 2       , application_name
 3    from r12_application_tl join r12_application using (application_id)
 4   where regexp_like(application_name,'obsolete','i')
 5   order by 1
 6  /

Concurrent Programs

In addition to new concurrent programs introduced in R12, a handful were removed, renamed, or marked obsolete. I also generated a longer list that broke down the counts by application, and another that listed each changed concurrent program individually. In the interest of saving space (and formatting pain), I've just offered the summary data here, followed by the query.

SQL>select count(r12c.concurrent_program_id) prog_count
  2  	 , 'New' prog_status
  3   from r12_application_tl r12a join r12_concurrent_programs r12c
  4        using (application_id)
  5  where not exists
  6  	   (
  7  	    select application_id
  8  	         , concurrent_program_id
  9  	      from r11_concurrent_programs r11c
 10  	     where r11c.application_id = application_id
 11  	       and r12c.concurrent_program_id = r11c.concurrent_program_id
 12  	    )
 13  group by 'New'
 14  union all
 15  select  count(r11c.concurrent_program_id) prog_count
 16  	  , 'Removed' Status
 17    from r11_application_tl r11a join r11_concurrent_programs r11c
 18  	    using (application_id)
 19   where not exists
 20  	    (
 21  	     select application_id
 22  		  , concurrent_program_id
 23  	       from r12_concurrent_programs r12c
 24  	      where r12c.application_id = application_id
 25  		and r11c.concurrent_program_id = r12c.concurrent_program_id
 26  	    )
 27  group by 'Removed'
 28  union all
 29  select  count(r12c.user_concurrent_program_name) prog_count
 30  	  , 'Obsolete' prog_status
 31    from r12_application_tl r12a
 32  	    join
 33  	    r12_concurrent_programs_tl r12c
 34  	    using (application_id)
 35  	    join r11_concurrent_programs_tl r11c
 36  	    using (application_id, concurrent_program_id)
 37   where regexp_like(r12c.user_concurrent_program_name,'obsolete','i')
 38  	and r12c.user_concurrent_program_name <> r11c.user_concurrent_program_name
 39  group by 'Obsolete'
 40  union all
 41  select  count(r12c.user_concurrent_program_name) prog_count
 42  	  , 'Renamed' prog_status
 43    from r12_application_tl r12a
 44  	    join
 45  	    r12_concurrent_programs_tl r12c
 46  	    using (application_id)
 47  	    join r11_concurrent_programs_tl r11c
 48  	    using (application_id, concurrent_program_id)
 49   where not regexp_like(r12c.user_concurrent_program_name,'obsolete','i')
 50  	and r12c.user_concurrent_program_name <> r11c.user_concurrent_program_name
 51  group by 'Renamed'
 52  order by 2
 53  /

Forms

Here's a count by application of new and removed forms. We didn't have much in the way of customized forms, so I didn't drill down to form names, but the queries would be easy enough to modify if needed.

SQL>select r12a.application_name
  2       , count(r12f.user_form_name) new_forms
  3    from r12_application_tl r12a join r12_form_tl r12f
  4         using (application_id)
  5   where not exists
  6         (
  7          select application_id
  8               , form_id
  9            from r11_form_tl r11f
 10           where r11f.application_id = application_id
 11             and r12f.form_id = r11f.form_id
 12         )
 13  group by r12a.application_name
 14  order by 2 desc
 15  /

SQL>select r11a.application_name
  2       , count(r11f.user_form_name) deleted_forms
  3    from r11_application_tl r11a join r11_form_tl r11f
  4         using (application_id)
  5   where not exists
  6         (
  7          select application_id
  8               , form_id
  9            from r12_form_tl r12f
 10           where r12f.application_id = application_id
 11             and r11f.form_id = r12f.form_id
 12         )
 13  group by r11a.application_name
 14  order by 2 desc
 15  /

Profile Options

Summary data for new and removed profile options, with additional information here. Some profile options marked obsolete as well, but if there were any renamed, either I didn't run the query or the results weren't very interesting:

SQL>select count(distinct application_id) affected_apps
  2  	  , count(1) new_options
  3    from r12_profile_options r12
  4   where not exists
  5  	    (
  6  	     select 1
  7  	       from r11_profile_options r11
  8  	      where r12.application_id = r11.application_id
  9  		and r12.profile_option_id = r11.profile_option_id
 10  	    )
 11  /

SQL>select count(distinct application_id) affected_apps
  2  	  , count(1) removed_options
  3    from r11_profile_options r11
  4   where not exists
  5  	    (
  6  	     select 1
  7  	       from r12_profile_options r12
  8  	      where r12.application_id = r11.application_id
  9  		and r12.profile_option_id = r11.profile_option_id
 10  	    )
 11  /

SQL>select application_short_name
  2       , user_profile_option_name obsolete_option
  3    from r12_profile_options_tl
  4         join r12_profile_options
  5         using (profile_option_name)
  6         join r12_application
  7         using (application_id)
  8   where regexp_like(user_profile_option_name,'obsolete','i')
  9   order by 1
 10  /

Database Objects

How many new database objects? A ton. How many deleted database objects? A lot, but less than a ton. Breakdown by application would probably be instructive, since many of those tables and packages are likely in support of new applications.

SQL>select r12.object_type
  2  	  , count(object_name) new_objects
  3    from r12_objects r12
  4   where not exists
  5  	    (
  6  	     select 1
  7  	       from r11_objects r11
  8  	      where r12.owner = r11.owner
  9  		and r12.object_name = r11.object_name
 10  		and r12.object_type = r11.object_type
 11  	     )
 12  group by r12.object_type
 13  order by 1
 14  /

SQL>select r11.object_type
  2  	  , count(object_name) removed_objects
  3    from r11_objects r11
  4   where not exists
  5  	    (
  6  	     select 1
  7  	       from r12_objects r12
  8  	      where r12.owner = r11.owner
  9  		and r12.object_name = r11.object_name
 10  		and r12.object_type = r11.object_type
 11  	     )
 12  group by r11.object_type
 13  order by 1
 14  /