Download this page export

Go to GitHub

Flexfield

The aim of this plugin is to provide dynamic item types which will be loaded at plugin runtime. For people who worked with Oracle eBusinessSuite I developed a similar idea which could fit perfectly with their own EBS flexfields.

Requisites to use:

·         An existing data base table with N flexfield fields of varchar2(4000) to fill

·         Optional: flexfield metadata table (FLEXFIELD_DEF, see example below)

·         This plugin on some page

Case of use

Some applications leave administrators the capability of extending its application by adding some fields to existing tables. In that case, administrator can expand application functionality by storing some data to application without the need of technical assistance.

Imagine you have your business table of products

PRODUCTS

You can define N flexfield items which application administrator will define:

PRODUCTS:

            FLEXFIELD_001 (varchar2(4000))

            FLEXFIELD_002 (varchar2(4000))

            ...

            FLEXFIELD_NNN (varchar2(4000))

Then you need a parametrization table for flexfield metadata, for instance:

FLEXFIELD_DEF:

Then you define a page which uses this plugin by querying previous table. SQL of flexfield plugin:

SELECT FLEX_NAME

                FLEX_DESCR,

                FLEX_TYPE,

                FLEX_SOURCE,

                ‘N’ FLEX_NULLABLE,

                          30 FLEX_SIZE

                null FLEX_MAX_SIZE,

                null FLEX_HEIGHT,

                ‘’ FLEX_HTML_ATTRIBUTES

                ‘’ LOV_SQL

FROM FLEXFIELD_DEF

WHERE FLEX_NAME = ‘MyFlex01’ and ACTIVE = ‘S’

Some tricks:

·         You can dynamically display item label by caching its value via ITEM. For instance, define item plugin label as &P1_ITEM. And inform item value at page value, by reading FLEXFIELD_DEF table.

·         Like previous sample, you can dynamically define its column name for a report using previous item &P1_ITEM.

·         You can lookup display value for report listing using FLEXFIELD.lookup_display_value(table,column,lov_return_value)

·         If you keep a naming convention for application items like <PREFIX>_TABLE_<FLEX_NUMBER> you can call procedure:  FLEXFIELD.intialize_application_items which will set each application time with its value

Available item types:

·         STRING: Textfield

·         TEXTAREA: TextArea

·         LOV: Select list (via APEX Lov)

o   Static

o   Dynamic

·         LOVSQL: LOV based on user SQL

o   LOVSQL column must be informed with inner sql

Select

       FLEX_NAME,        --(varchar2) Flexfield name

       FLEX_DESC,        --(varchar2) Flexfield description -> Shown at help

       FLEX_TYPE,        --(varchar2) STRING, LOV, LOVSQL, TEXTAREA -> Type of flexfield

       FLEX_LOV,         --(varchar2) Name of APEX LOV for LOV type flexfields

       FLEX_NULLABLE,    --(char(1))  S, N -> Specifies if allows null values

       FLEX_SIZE,        --(Integer)  Size of the HTML element

       FLEX_MAX_LENGTH,  --(Integer)  Max length of its content (only for STRING and TEXTAREA types)

       FLEX_HEIGHT,      --(Integer)  Height of HTML element (only for TEXTAREA)

       FLEX_HTML_ATTR,   --(varchar2) Additional HTML element attributes

       FLEX_LOV_SQL      --(varchar2) In case TYPE= LOVSQL this column specifies LOV SQL returning pairs display value and return value

from FLEXFIELD_DEF

where flexfield_name = 'MyField01'