Previous  Next          Contents  Index  Navigation  Glossary  Library

Descriptive Flexfield View Example

Here is an example view and report created for the Oracle Assets Asset Category Descriptive Flexfield, which uses the table FA_ADDITIONS. The columns shown in bold print are columns that particularly pertain to the flexfield itself. You should note the differences between the boldfaced columns in the underlying table and those in its view. The descriptive flexfield columns in this table include the ATTRIBUTEn columns and the CONTEXT column (structure column).

Original Underlying Descriptive Flexfield Table 

SQL> describe FA_ADDITIONS
Name                             Null?    Type
------------------------------- -------- ----
ASSET_ID                         NOT NULL NUMBER(15)
ASSET_NUMBER                     NOT NULL VARCHAR2(15)
ASSET_KEY_CCID                            NUMBER(15)
CURRENT_UNITS                    NOT NULL NUMBER(4)
ASSET_TYPE                       NOT NULL VARCHAR2(11)
TAG_NUMBER                                VARCHAR2(15)
DESCRIPTION                      NOT NULL VARCHAR2(80)
ASSET_CATEGORY_ID                NOT NULL NUMBER(15)
PARENT_ASSET_ID                           NUMBER(15)
MANUFACTURER_NAME                         VARCHAR2(30)
SERIAL_NUMBER                             VARCHAR2(35)
MODEL_NUMBER                              VARCHAR2(40)
PROPERTY_TYPE_CODE                        VARCHAR2(10)
PROPERTY_1245_1250_CODE                   VARCHAR2(4)
IN_USE_FLAG                      NOT NULL VARCHAR2(3)
OWNED_LEASED                     NOT NULL VARCHAR2(6)
NEW_USED                         NOT NULL VARCHAR2(4)
UNIT_ADJUSTMENT_FLAG             NOT NULL VARCHAR2(3)
ADD_COST_JE_FLAG                 NOT NULL VARCHAR2(3)
ATTRIBUTE1                                VARCHAR2(150)
ATTRIBUTE2                                VARCHAR2(150)
  . . .                                     . . .
ATTRIBUTE29                               VARCHAR2(150)
ATTRIBUTE30                               VARCHAR2(150)
ATTRIBUTE_CATEGORY_CODE          NOT NULL VARCHAR2(210)
CONTEXT                                   VARCHAR2(210)
LEASE_ID                                  NUMBER(15)
LAST_UPDATE_DATE                 NOT NULL DATE
LAST_UPDATED_BY                  NOT NULL NUMBER(15)
CREATED_BY                                NUMBER(15)
CREATION_DATE                             DATE
LAST_UPDATE_LOGIN                         NUMBER(15)

This descriptive flexfield has three context-sensitive structures: VEHICLE.OWNSTD, VEHICLE.HEAVY, and BUILDING.OFFICE. The BUILDING.OFFICE structure has two segments, square footage and insurer. The VEHICLE.OWNSTD structure has five segments, as shown. The VEHICLE.HEAVY structure has five segments as well, square footage cargo, number of axles, transmission type, insurance company, and insurance policy number. The two VEHICLE structures share the same segment name for the insurance company segment.

The resulting view contains a total of eleven segment columns, rather than twelve, for the three structures. The column CONTEXT_VALUE in the view corresponds to the column CONTEXT in the table (the context field prompt defined in the Descriptive Flexfield Segments window is "Context Value"). See: Descriptive Flexfield Segments.

Descriptive Flexfield View

SQL> describe FA_ADDITIONS_DFV
Name                             Null?    Type
------------------------------- -------- ----
ROW_ID                                    ROWID
CONTEXT_VALUE                             VARCHAR2(210)
SQUARE_FOOTAGE                            NUMBER
INSURER                                   VARCHAR2(150)
LICENSE_NUMBER                            VARCHAR2(150)
INSURANCE_COMPANY                         VARCHAR2(150)
INSURANCE_POLICY_NUMBER                   VARCHAR2(150)
SQ_FOOTAGE_CARGO                          NUMBER
NUMBER_OF_AXLES                           NUMBER
TRANSMISSION_TYPE                         VARCHAR2(150)
LICENSE_RENEWAL_DATE                      DATE
POLICY_RENEWAL_DATE                       DATE
POLICY_NUMBER                             VARCHAR2(150)
CONCATENATED_SEGMENTS                     VARCHAR2(1116)

Example of Reporting from a Descriptive Flexfield View

Here is a simple example of selecting some data from the table and its corresponding view.

SQL> select ADD.ASSET_NUMBER ASSET, ADD.DESCRIPTION, 
            CONTEXT_VALUE, CONCATENATED_SEGMENTS
     from FA_ADDITIONS ADD, FA_ADDITIONS_DFV 
     where ADD.rowid = ROW_ID;

Note that in this simple report, the structure name (BUILDING.OFFICE, VEHICLE.HEAVY, and VEHICLE.OWNSTD) appears in two columns: CONTEXT_VALUE (the structure column) and in the CONCATENATED_SEGMENTS column as the first "segment" value (the context value appears first because there are no enabled global segments). Some context values do not have any enabled segments, so the CONCATENATED_SEGMENTS column is empty for those assets. Some assets, such as asset number 363, while they belong to structures with enabled segments, do not have values filled in for the descriptive flexfield. For those assets, the CONCATENATED_ SEGMENTS column contains the structure name and several periods (segment separators) that designate empty segment values.

See Also

Overview of Reporting on Flexfields Data

Overview of Flexfield Views

Key Flexfield Concatenated Segment View

Key Flexfield Structure View

Descriptive Flexfield View

Creating a Flexfield View

Segment Naming Conventions

Using Flexfield Views to Write a Report

         Previous  Next          Contents  Index  Navigation  Glossary  Library