A sampling of R12 AOL and database object changes

Note: This post should be considered obsolete. The Applications Technology Group at Oracle has released the EBS Data Model Comparison Report, which should help you to identify data model changes between older versions of Oracle Apps and Release 12.1.3.

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. :)

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  /
APPLICATION_SHORT_NAME APPLICATION_NAME
CDR Oracle Clinical Data Repository
CLA APAC Consulting Localizations
CLE EMEA Consulting Localizations
CLJ Japan Consulting Localizations
CLL LAD Consulting Localizations
CSN Call Center
DEM01 Team 01 Order Entry Demo
DUMMY_GMO Obsolete Process Operations
GMO Manufacturing Execution System for Process Manufacturing
IBW Oracle Web Analytics
IPM Oracle Imaging Process Management
IRC iRecruitment
ITA Information Technology Audit
JMF Supply Chain Localizations
OKC_REP_TXT_INDEX_OPTIMIZE Optimize Contracts Repository Text index
OKC_REP_TXT_INDEX_SYNC Build/syncronize Contracts Repository Text index
OUC University Curriculum
PSR Public Sector Receivables
RRC Retail Core
RRS Site Management
TEST test

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  /
APPLICATION_SHORT_NAME NEW_NAME OLD_NAME
ICX Oracle iProcurement Self-Service Web Applications
FEM Enterprise Performance Foundation Strategic Enterprise
Management
IBY Payments iPayment
IGS Student System Student Systems
ASP Oracle Sales for Handhelds Field Sales/Palm Devices
CSE Asset Tracking Enterprise Install Base
XNB Oracle Telecommunications Billing Integrator eBusiness
Billing
GCS Financial Consolidation Hub Global Consolidation System
FPA Project Portfolio Analysis Portfolio Analyzer
HCP Healthcare Intelligence Healthcare Portal
PFT Oracle Profitability Manager Performance Analyzer

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  /
APPLICATION_SHORT_NAME APPLICATION_NAME
ABM Activity Based Management (Obsolete)
AHM Hosting Manager(Obsolete)
AMF Fulfillment Services (Obsolete)
BIC Customer Intelligence (obsolete)
CSS Support (obsolete)
CUE Billing Connect (obsolete)
CUN Network Logistics - NATS (obsolete)
DUMMY_GMO Obsolete Process Operations
EAA SEM Exchange (obsolete)
FPT Banking Center (obsolete)
IBA iMarketing (Obsolete)
IMT iMeeting (obsolete)
IPD Product Development (obsolete)
ME Controlled Availability Product(Obsolete)
OKB Contracts for Subscriptions (Obsolete)
OKO Contracts for Sales (Obsolete)
OKP Contracts for Procurement (Obsolete)
OKR Contracts for Rights (Obsolete)
OZP Trade Planning (Obsolete)
OZS iClaims (Obsolete)
RCM Regulatory Capital Manager (obsolete)
RHX Advanced Planning Foundation(obsolete)
RLA Release Management Integration Kit (Obsolete)
VEH Automotive Integration Kit (Obsolete)
XNC Sales for Communications (Obsolete)
XNI Install Base Intelligence (Obsolete)
XNM Marketing for Communications (Obsolete)
XNS Service for Communications (obsolete)

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.

PROG_COUNT PROG_STATUS
1587 New
31 Obsolete
87 Removed
294 Renamed
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  /
APPLICATION_NAME NEW_FORMS
Asia/Pacific Localizations 114
Subledger Accounting 17
Process Manufacturing Process Execution 7
Student System 7
General Ledger 7
Field Service 6
Inventory 6
Process Manufacturing Financials 5
Marketing 5
Process Manufacturing Product Development 5
Bills of Material 4
Depot Repair 4
Advanced Supply Chain Planning 4
Supply Chain Localizations 4
Financial Aid 3
Public Sector Budgeting 3
Public Sector Financials International 3
Receivables 3
Cash Management 2
Enterprise Asset Management 2
Process Manufacturing Regulatory Management 2
Treasury 2
Shipping Execution 2
Warehouse Management 2
Payables 2
E-Business Tax 2
Install Base 2
Federal Financials 2
Assets 2
Process Manufacturing Systems 2
Process Manufacturing Inventory 1
Property Manager 1
XML Gateway 1
Shop Floor Management 1
Application Object Library 1
Manufacturing Execution System for Process Manufacturing 1
Collections 1
Regional Localizations 1
Oracle Telecommunications Billing Integrator 1
Purchasing 1

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  /
APPLICATION_NAME DELETED_FORMS
Public Sector Budgeting 7
Project Contracts 4
Grants Accounting 3
Quality 3
Financial Aid 3
Common Modules-AK 1

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  /
AFFECTED_APPS NEW_OPTIONS
115 963

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  /
AFFECTED_APPS REMOVED_OPTIONS
32 260

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  /
APPLICATION_SHORT_NAME OBSOLETE_OPTION
AK AK: Loaders input directory - Obsolete
AK AK: Loaders output directory - Obsolete
AR HZ: Report Error on Obsolete Columns
CS Knowledge: OBSOLETED PROFILE - CS_KB_NEAR_REALTIME_INDEXIN
CSS OBSOLETE: Automated Escalation Task Owner Role
CSS OBSOLETE: Automated Escalation Task Assignee Role
CSS OBSOLETE: Automated Escalation Task Template Name
CSS OBSOLETE: Automated Escalation Notification Role
CSS OBSOLETE: Default Notification Condition
CSS OBSOLETE: Knowledge Base Status
CSS OBSOLETE: Knowledge Base Set Type
CSS OBSOLETE: Product Reporting Category Set
CSS OBSOLETE: Platform Category Set
GMA GML: (this profile can be deleted) **obsolete**
ICX Obsolete
IGS IGS: JTF Resource - Obsolete
IGS IGS: JTF Result - Obsolete
IGS IGS: Degree Audit Organization Types --Obsolete
IGS IGS: Degree Audit Program Major Definitions --Obsolete
IGS IGS: Degree Audit Student List --Obsolete
IGS IGS: Degree Audit Student Alternate ID Type --Obsolete
IGS IGS: Degree Audit Debug Option --Obsolete
IGS IGS: Degree Audit Major Unit Set Category --Obsolete
IGS IGS: Degree Audit Preferred Address Usage --Obsolete
IGS IGS: Degree Audit What If Unit Set Titles --Obsolete
IGS IGS: Degree Audit Track Unit Set Category --Obsolete
IGS IGS: Degree Audit Institution Alternate Identifier Types --Obsolete
IGS IGS: Degree Audit Minor Unit Set Category --Obsolete
IGS IGS: JTF Outcome - Obsolete
IGS IGS: AMS Category Type - Obsolete
IGS IGS: AMS Category Sub Type - Obsolete
IGS IGS: JTF Reason - Obsolete
JL Obsolete in R12 - JLBR Automatically Populate Payment Batch Name
SQLGL GL AHE: Saving Allowed (Obsolete)

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  /
OBJECT_TYPE NEW_OBJECTS
EVALUATION CONTEXT 2
INDEX 6901
INDEX PARTITION 497
INDEX SUBPARTITION 48
JAVA CLASS 28
LOB 271
LOB PARTITION 14
LOB SUBPARTITION 48
MATERIALIZED VIEW 104
PACKAGE 4687
PACKAGE BODY 4635
PROCEDURE 2
QUEUE 11
RULE 2
RULE SET 6
SEQUENCE 1393
SYNONYM 8801
TABLE 4562
TABLE PARTITION 1311
TABLE SUBPARTITION 48
TRIGGER 749
TYPE 402
TYPE BODY 76
VIEW 2903
XML SCHEMA 3
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  /
OBJECT_TYPE REMOVED_OBJECTS
INDEX 2577
INDEX PARTITION 48
JAVA CLASS 296
JAVA RESOURCE 39
LOB 119
MATERIALIZED VIEW 9
PACKAGE 2279
PACKAGE BODY 2237
PROCEDURE 1
QUEUE 6
SEQUENCE 282
SYNONYM 1326
TABLE 922
TABLE PARTITION 12
TRIGGER 322
TYPE 14
VIEW 2593

6 Comments

  1. Posted 25 March 2010 at 16:44 | Permalink

    Hi John,

    Excellent post - will be useful for linking to from those inevitable forum posts ;-)

    Regards,
    Gareth

  2. Posted 25 March 2010 at 17:15 | Permalink

    Thanks, Gareth. I was reminded/inspired to dust off the old log files after participating in this OTN thread. Hopefully the post will be useful enough that all of the table formatting will be worthwhile. ;-)

    Regards,

    John P.

  3. Posted 9 June 2010 at 21:38 | Permalink

    This post in simple word is Supurb. I never thought such information can extracted from the system.

    Cheers
    Sanjit

  4. Posted 9 June 2010 at 21:41 | Permalink

    Thanks, Sanjit. I hope you can get some use from the information. :-)

    Regards,

    John P.

  5. murali
    Posted 22 July 2010 at 9:07 | Permalink

    very nice post

  6. Posted 22 July 2010 at 21:07 | Permalink

    Thank you, Murali. I appreciate the visit.

    Regards,

    John P.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*