Application Composer-2- DM model behind custom application

Application Composer Series: Customization Data Storage

By Richard Bingham on May 22, 2014

Use-Case

This post offers insight into where the records created using custom objects built in Application Composer are stored in the database. For example if you created the Trouble Ticket (TroubleTicket_c) custom object then when your users submit the related creation page, the resulting new records are stored in the tables mentioned below. As such this may be useful information if you are designing custom reports with very specific requirements, or doing more invasive custom development work.

The information provided here is for information only, and should not be considered official documentation. It is intended for query-only use for on-premises development work and all inserting, updating or deleting records directly from any application tables is strictly prohibited and may render your system unsupportable. The information applies to Releases 7 and 8, and does not represent a public API and may change without notice. You can find the formal table definitions in Oracle Enterprise Repository.

The Object Model

All custom objects are registered in the central FND_OBJECTS table and it is for this reason that they cannot be duplicated or reused once registered. When querying this table you'll notice that DATABASE_OBJECT_NAME gives which product table the related records are stored in. Also the CREATED_BY and CREATION_DATE columns in this table can be useful when investigating customization sources.

The detailed definitions for fields, objects, and other Application Composer entries are stored in a combination of MDS and ADF internal structures and tables, and detail on this is outside the scope of this article.

Object Record Tables

Each product provides a [CODE]_REF_ENTITIES table where all its object records are stored, and most have a [CODE]_REF_ENTITIES_XM table for child object records. Note the tables are created only upon creation of the first new object.

CRM Common

• HZ_REF_ENTITIES - object records
• HZ_IMP_REF_ENTITIES - used for import

Customer Center

• ZCX_REF_ENTITIES - object records
• ZCX_REF_ENTITIES_XM - child object records

Sales

• MOO_IMP_REF_ENTITIES - used for import 
• MOO_REF_ENTITIES - object records
• MOO_REF_ENTITIES_XM - child object records

Marketing

• MKT_IMP_REF_ENTITIES - used for import
• MKT_REF_ENTITIES - object records
• MKT_REF_ENTITIES_XM - child object records

Territories and Quotas

• MOT_REF_ENTITIES - object records
• MOT_REF_ENTITIES_XM - child object records

Other similar tables you might notice are intended for alternative (e.g. flex fields) or potential future use, and the reason the Sales Catalog application is not listed here as that does not currently allow creation of custom objects.

Useful Columns

The tables mentioned above have a standardized structure, comprising of some general attribute columns then extensive sets for each supported data type. So if you create an object with three text fields and two number fields, the table will have data for three of the CHAR(1500) columns and two of the NUMBER columns. All these field-related columns are named in the same way, using the following format:

• EXTN_ATTRIBUTE_[DATATYPE][ID] - where [DATATYPE] is either NUMBER, TIMESTAMP, CHAR, or CLOB, and [ID] is a sequential numeric value.

Examples are EXTN_ATTRIBUTE_CHAR006 (regularly the first custom text field), EXTN_ATTRIBUTE_NUMBER021, EXTN_ATTRIBUTE_TIMESTAMP005, or  EXTN_ATTRIBUTE_CLOB008.

This can make it hard to determine which field on your custom object (e.g. description_c) corresponds to which column (e.g. EXTN_ATTRIBUTE_CHAR015) however the queried values returned should provide a clue, and you can consider looking at the response output (in SDO format) from calling the related custom object web servicesdiscussed here. Some other important columns that provide useful values for queries include: ID as the internal primary key of the table (e.g. 300000002763453), the RECORD_NAME as given in the create object page in Application Composer and ATTRIBUTE_CATEGORY as the object API name to which this record belongs (e.g. TERROBJ1_c below).