| Oracle® Retail Merchandising System Custom Flex Attribute Solution Implementation Guide Release 15.0 E65438-01 |
|
 Previous |
 Next |
| Oracle® Retail Merchandising System Custom Flex Attribute Solution Implementation Guide Release 15.0 E65438-01 |
|
Previous |
Next |
The CFAS framework is driven by information stored in the following CFAS-specific database objects. This appendix provides information on the CFAS-specific database objects. It also includes sample data along with each table definition to help you better understand the table definitions.
It includes the following:
These database tables contain information on the entities that are enabled for CFAS. It include the following tables:
This table holds the business object entities that have been extended in the base RMS code. It provides the mapping between extendable business objects (base RMS tables) and custom extension tables. By default, this table contains data that is populated by a seed data script run during the base RMS installation for each entity pre-enabled for customization. You can still populate this table if you want to customize entities not included during the installation. However, changes to the base code using the table are not supported by Oracle.
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| EXT_ENTITY_ID | NUMBER(10) | Y | Y | This column holds a generated ID that distinguishes the model extension point. | Create a sequence to generate. |
| BASE_RMS_TABLE | VARCHAR2(30) | N | Y | This column holds the base RMS table (for example, ordhead, tsfhead, and so on) which the extension refers. | Unique key to ensure no duplicates. |
| CUSTOM_EXT_TABLE | VARCHAR2(30) | N | Y | This column holds the name of custom extension table related to this model extension. The custom extension table must be created when CFAS is installed for an entity.
It is recommended (but not required) that the creator of the metadata append '_CFA_EXT' to the BASE_RMS_TABLE name. This is a recommendation only because some BASE_RMS_TABLE names, when appended with the recommended suffix, may be too long. In those cases, it is recommended that some abbreviation be created for the BASE_RMS_TABLE, and the '_CFA_EXT' suffix is still used. |
Unique key to ensure no duplicates. |
| ACTIVE_IND | VARCHAR2(1) | N | Y | Indicates if the entity is activated and ready for viewing. | Only set via batch.
Check constraint (Y,N). |
| BASE_IND | VARCHAR2(1) | N | Y | This indicates that the entity is pre-enabled for customization and supporting code in the base UI are already setup as part of base install. | Check constraint (Y, N). |
| VALIDATION_FUNCT | VARCHAR2(61) | N | N | This column holds the name (package and function) of code that should be called to validate this entity. | – |
The following table illustrates the CFAS_EXT_ENTITY table with sample data.
| EXT_ENTITY_ID | BASE_RMS_TABLE | CUSTOM_EXT_TABLE | VALIDATION_FUNCT |
|---|---|---|---|
| 3000000001 | ORDHEAD | ORDHEAD_CFA_EXT | – |
| 3000000002 | ITEM_SUPP_COUNTRY | ITEM_SUP_CTRY _CFA_EXT | – |
| 3000000003 | STORE | STORE_CFA_EXT | – |
| 3000000004 | SUPS | SUPS_CFA_EXT | – |
This table holds the key information for the extended base RMS tables. This information can be derived from system tables, but is instead stored in an application specific table for performance and clarity. This information is used to ensure that the generic CFAS persistence code can map correctly to the entity specific code and table. Data in this table will also be used to determine the number of key fields displayed in the CFAS user interface. By default, this table contains data that is populated by a seed data script run during the base RMS installation for each entity pre-enabled for customization. The values will be automatically populated when using the CFAS Extended Entity Maintenance screen.
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| BASE_RMS_TABLE | VARCHAR2(30) | Y | Y | This column holds the base RMS table (for example, ordhead, tsfhead, etc) which the extension refers. | FK to CFA_EXT_ENTITY. BASE_RMS_TABLE. |
| KEY_COL | VARCHAR2(30) | Y | Y | This column holds the name of the primary key column on the extended BASE_RMS_TABLE. | – |
| KEY_NUMBER | NUMBER | N | Y | This column holds the sequence of the key column. For tables that have a single key, this value will always be 1 and this table will only have one record for the BASE_RMS TABLE. For tables that have composite primary keys, this column will distinguish which element of the PK is represented and this table will have as many records as there are elements in the composite key. | – |
| DATA_TYPE | VARCHAR2(10) | N | Y | This column holds the data type of the primary key element. | – |
| DESCRIPTION_CODE | VARCHAR2(61) | N | N | This column contains the package.function name used by the CFAS UI to get the key value's description | – |
The following table illustrates the CFA_EXT_ENTITY_KEY table with sample data.
| BASE_RMS_TABLE | KEY_COL | KEY_NUMBER | DATA_TYPE | DESCRIPTION_CODE |
|---|---|---|---|---|
| ORDHEAD | ORDER_NO | 1 | NUMBER | |
| ITEM_SUPP_COUNTRY | ITEM | 1 | NUMBER | CUSTOM_SQL.GET_ITEM_DESC |
| ITEM_SUPP_COUNTRY | SUPPLIER | 2 | NUMBER | CUSTOM_SQL.GET_SUP_DESC |
| ITEM_SUPP_COUNTRY | ORIGIN_COUNTRY_ID | 3 | VARCHAR | CUSTOM_SQL.GET_CTRY_DESC |
| STORE | STORE | 1 | NUMBER | CUSTOM_SQL.GET_STORE_DESC |
| SUPS | SUPPLIER | 1 | NUMBER | CUSTOM_SQL.GET_SUP_DESC |
This table holds the description that should be used as the label for the key fields in the CFAS user interface header. Records must exist in this table to ensure that fields in the user interface are always labeled. By default, this table contains data that is populated by a seed data script run during the base RMS installation for each entity pre-enabled for customization (for all RMS supported languages).
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| BASE_RMS_TABLE | VARCHAR2(30) | Y | Y | This column holds the base RMS table (for example, ordhead, tsfhead, etc) which the extension refers. | FK to CFA_EXT_ENTITY_KEYS. BASE_RMS_TABLE. |
| KEY_COL | VARCHAR2(30) | Y | Y | This column holds the name of the primary key column on the extended BASE_RMS_TABLE. | CFA_EXT_ENTITY_KEYS. KEY_COL. |
| LANG | NUMBER(6) | Y | Y | This column holds the language that the KEY_DESC is in. It will be used to ensure the appropriate language is shown to any end users accessing the CFAS user interface. | FK to LANG.LANG.
UK on BASE_RMS_TABLE, KEY_COL, LANG. |
| DEFAULT_LANG_IND | VARCHAR2(1) | N | Y | This column indicates that the current records description should be considered the default and displayed in the user interface, if a translation for the end user's language does not exist. This column should be 'Y' for only one language for the BASE_RMS_TABLE and KEY_COL combination. | Check constraint (Y, N). |
| LABEL | VARCHAR2(255) | N | Y | This value will be displayed on the header of the CFAS user interface to label the value passed from the calling form to the CFAS user interface as the KEY_COL. | – |
The following table illustrates the CFA_EXT_ENTITY_KEY_LABELS table with sample data.
| BASE_RMS_TABLE | KEY_COL | LANG | DEFAULT_LANG_IND | KEY_DESC |
|---|---|---|---|---|
| ORDHEAD | ORDER_NO | 1 (English) | Y | Order |
| ORDHEAD | ORDER_NO | 4 (Spanish) | N | Orden de Compra |
| ORDHEAD | ORDER_NO | 12 (Brazilian Portuguese) | N | Ordem de Compra |
| ITEM_SUPP_COUNTRY | ITEM | 1 (English) | Y | Item |
| ITEM_SUPP_COUNTRY | ITEM | 4 (Spanish) | N | Item (in Spanish) |
| ITEM_SUPP_COUNTRY | SUPPLIER | 1 (English) | Y | Supplier |
| ITEM_SUPP_COUNTRY | SUPPLIER | 4 (Spanish | N | Supplier (in Spanish) |
| ITEM_SUPP_COUNTRY | ORIGIN_COUNTRY_ID | 1 (English) | Y | Origin Country ID |
| ITEM_SUPP_COUNTRY | ORIGIN_COUNTRY_ID | 4 (Spanish) | N | Origin Country ID (in Spanish) |
The custom metadata database tables contain all the information required to display and capture actual data on the extended entities. They include:
This table holds the metadata that defines attribute group sets for all entities. Each extended entity can have at most 10 group sets which are accessible from the Options menu of the relevant user interface. Attribute group sets represent a higher grouping of attributes that are functionally or feature-wise (in case of add-on packs) related.
Access to the group set may be controlled via rules. These rules are executed via function called when the option menu is activated. Only a function can be defined per group but the function can have any rule. See examples setting up rules (TBD).
A validation function can be defined to validate combinations of attributes within the group set spanning across multiple attribute groups.
| Column | Datatype | Primary Key? | Required? | Description | Notes |
|---|---|---|---|---|---|
| GROUP_SET_ID | NUMBER(10) | Y | Y | This column holds id of the set where the group belongs to. | System Generated. |
| EXT_ENTITY_ID | NUMBER(10) | N | Y | This column holds the ID of the entity where the set belongs to. | FK to CFA_EXT_ENTITY.EXT_ENTITY_ID. |
| GROUP_SET_VIEW_NAME | VARCHAR2(30) | N | Y | This column holds the name of the database view that will be generated to make access to user entered data easier. | – |
| DISPLAY_SEQ | NUMBER | N | Y | This column holds the order the attribute group set displayed in on the CFAS user interface when multiple group sets exist for the entity. | Check Constraint (1-10).
Unique across an entity. |
| QUALIFIER_FUNC | VARCHAR2(61) | N | N | This column holds the name (package and function) of code that should be called to check if required information is supplied from the base user interface to access the attributes within the group set (determines if the group set is enabled on the user interface). The inputs and outputs of this function code are tightly controlled. | – |
| DEFAULT_FUNC | VARCHAR2(61) | N | N | This column holds the name (package and function) of code that should be called on startup of the CFAS user interface to pre-populate attribute fields with default values (can be in any group within the set). The inputs and outputs of this attribute group set level default code are tightly controlled. | – |
| VALIDATION_FUNC | VARCHAR2(61) | N | N | This column holds the name (package and function) of code that should be called to validate this attribute group set. The inputs and outputs of this attribute group set level validation code are tightly controlled. | – |
| STAGING_TABLE_NAME | VARCHAR2(30) | N | N | This column holds the name of the staging area where data from an external source can be stored and exported to the CFA extension table linked to this group set. | – |
| ACTIVE_IND | VARCHAR2(1) | N | Y | This column indicates whether the group set is visible in the base user interface menu. Used for simulation purposes. | Check Constraint (Y, N). |
| BASE_IND | VARCHAR2(1) | N | Y | This column indicates if the attribute group set is defined by Oracle. Oracle defined group sets cannot be further customized. | Check Constraint (Y, N). |
The following table illustrates the CFA_ATTRIB _GROUP_SET table with sample data.
|
Note: Grouping the attribute groups into attribute group sets that are logical requires local domain knowledge. The examples below are meant for illustration purposes only. |
| GROUP_SET_ID | EXT_ENTITY_ID | DISPLAY_SEQ | ACTIVE_IND | GROUP_SET_VIEW_NAME | QUALIFIER_FUNC | DEFAULT_FUNC | VALIDATION_FUNC |
|---|---|---|---|---|---|---|---|
| 1000000001 | 3000000001 | 1 | Y | V_CFA_PO_CUSTOM | – |
– |
|
| 1000000002 | 3000000001 | 2 | Y | V_CFA_PO_TELCO | TELCO_SQL.CHK_TELCO_ACCESS | – |
– |
| 1000000003 | 3000000002 | 1 | Y | V_CFA_ISC_TELCO | TELCO_SQL.CHK_TELCO_ACCESS | – |
– |
This table holds the language specific descriptions that will be used to depict each specific group set in the attribute group set section of the CFAS UI. At least one record must exist on this table for each attribute group set.
| Column | Datatype | Primary Key? | Required? | Description | Notes |
|---|---|---|---|---|---|
| GROUP_SET_ID | NUMBER(10) | Y | Y | This column holds a generated ID that distinguishes the custom attribute group set. | FK to CFA_ATTRIB _GROUP_SET. GROUP_SET_ID. |
| LANG | NUMBER(6) | Y | Y | This column holds the language the of the user interface description. | FK to LANG.LANG. |
| DEFAULT_LANG_IND | VARCHAR2(1) | N | Y | This column indicates that the current records description should be considered the default and displayed in the user interface, if a translation for the end user's language does not exist. This column should be 'Y' for only one language for the GROUP_ID. | Check constraint (Y, N). |
| LABEL | VARCHAR2(255) | N | Y | This column holds the text that will be used to identify the attribute group on the CFAS UI. | – |
The following table illustrates the CFA_ATTRIB_GROUP_LABELS table with sample data.
|
Note: All text in the table below are for illustration purposes only. |
| GROUP_SET_ID | LANG | DEFAULT_LANG_IND | LABEL |
|---|---|---|---|
| 1000000001 | 1 | Y | Purchase Order Custom Attributes |
| 1000000002 | 1 | Y | Purchase Order Telecom Attributes |
| 1000000003 | 1 | Y | Item Supplier Country Telecom Attributes |
| 1000000001 | 4 | Y | Purchase Order Custom Attributes (In Spanish) |
| 1000000002 | 4 | Y | Purchase Order Telecom Attributes (In Spanish) |
| 1000000003 | 4 | Y | Item Supplier Country Telecom Attributes (In Spanish) |
This table holds the metadata that defines attributes groups for group sets for all entities.
Attribute groups define the grouping of attributes in the user interface widget. Each attribute group is limited to 22 possible attributes. 10 of these attributes can be character data, 10 attributes can be numeric data and two attributes can be date data. If you need to add 12 attributes to an entity, and each attribute is a number, you will need to split the attributes into two attribute groups.
A validation function can be defined to validate combinations of attributes within the group.
| Column | Datatype | Primary Key? | Required? | Description | Notes |
|---|---|---|---|---|---|
| GROUP_ID | NUMBER(10) | Y | Y | This column holds a generated ID that distinguishes the custom attribute group. | Create a sequence to generate these numbers. |
| GROUP_SET_ID | NUMBER(10) | N | Y | This column holds id of the set where the group belongs to. | FK to CFA_EXT_ENTITY_GROUP_SET. GROUP_SET_ID. |
| GROUP_VIEW_NAME | VARCHAR2(30) | N | Y | This column holds the name of the database view that will be generated to make access to user entered data easier. | – |
| DISPLAY_SEQ | NUMBER | N | Y | This column holds the order the attribute groups will be displayed in on the CFAS UI when multiple groups exist for a single attribute group set. | |
| VALIDATION_FUNC | VARCHAR2(61) | N | N | This column holds the name (package and function) of code that should be called to validate this attribute group. The inputs and outputs of this attribute group level validation code are tightly controlled. | – |
| ACTIVE_IND | VARCHAR2(1) | N | Y | This column indicates whether the group is visible in the CFAS user interface when accessed from the base user interface. Used for simulation purposes. | Check Constraint (Y, N). |
| BASE_IND | VARCHAR(1) | N | Y | This column indicates if the attribute group is defined by Oracle. Oracle defined groups cannot be further customized. | Check Constraint (Y, N). |
The following table illustrates the CFA_ATTRIB _GROUP table with sample data.
|
Note: Grouping the attributes into attribute groups that are logical requires local domain knowledge. The division of these attributes into attribute groups in the table below is for illustration purposes only; it does not reflect the local domain knowledge that will be required to create logical and usable CFAS metadata. |
| GROUP_ID | GROUP_SET_ID | DISPLAY_SEQ | ACTIVE_IND | GROUP_VIEW_NAME | VALIDATION_FUNC |
|---|---|---|---|---|---|
| 9000000001 | 1000000001 | 1 | Y | V_CFA_PO_IMPORT | – |
| 9000000002 | 1000000001 | 2 | Y | V_CFA_PO_SHIPPING | – |
| 9000000004 | 1000000001 | 3 | N | V_CFA_PO_BILLING | – |
| 9000000006 | 1000000002 | 1 | Y | V_CFA_PO_TELCO_ATTRIB | – |
| 9000000007 | 1000000002 | 2 | Y | V_CFA_PO_TELCO_CONTACT | – |
| 9000000008 | 1000000003 | 1 | Y | V_CFA_ISC_ITEM_RESTRICT | – |
| 9000000009 | 1000000003 | 2 | Y | V_CFA_ISC_INV | – |
This table holds the language specific descriptions that will be used to show each specific group in the attribute groups section of the CFAS user interface. At least one record must exist on this table for each attribute group.
| Column | Datatype | Primary Key? | Required? | Description | Notes |
|---|---|---|---|---|---|
| GROUP_ID | NUMBER(10) | Y | Y | This column holds a generated ID that distinguishes the custom attribute group. | FK to CFA_ATTRIB _GROUP. GROUP_ID. |
| LANG | NUMBER(6) | Y | Y | This column holds the language the of the UI description. | FK to LANG.LANG |
| DEFAULT_LANG_IND | VARCHAR2(1) | N | Y | This column indicates that the current records description should be considered the default and displayed in the user interface, if a translation for the end user's language does not exist. This column should be 'Y' for only one language for the GROUP_ID. | Check constraint (Y, N). |
| LABEL | VARCHAR2(255) | N | Y | This column holds the text that will be used to identify the attribute group on the CFAS user interface. | – |
The following table illustrates the CFA_ATTRIB_GROUP_LABELS table with sample data.
|
Note: All text in the table below are for illustration purposes only. |
| GROUP_ID | LANG | DEFAULT_LANG_IND | LABEL |
|---|---|---|---|
| 9000000001 | 1 | Y | Purchase Order Import Attributes |
| 9000000002 | 1 | Y | Purchase Order Shipping Attributes |
| 9000000004 | 1 | Y | Purchase Order Billing Attributes |
| 9000000006 | 1 | Y | Purchase Order Telecom Attributes |
| 9000000007 | 1 | Y | Purchase Order Telecom Contact Information |
| 9000000008 | 1 | Y | Item Supplier Country Restrictions |
| 9000000009 | 1 | Y | Item Supplier Country Telecom Attributes |
| 9000000001 | 4 | N | PO Import Attributes (In Spanish) |
| 9000000002 | 4 | N | PO Shipping Attributes (In Spanish) |
| 9000000004 | 4 | N | PO Billing Attributes (In Spanish) |
| 9000000006 | 4 | N | PO Telecom Attributes (In Spanish) |
| 9000000007 | 4 | N | PO Telecom Contact Information (In Spanish) |
| 9000000008 | 4 | N | Item Restrictions (In Spanish) |
| 9000000009 | 4 | N | Item Telecom Attributes (In Spanish) |
This table holds the metadata that defines custom attributes for all entities. The information stored in this table tells how each attribute is stored, basic data restrictions, and how the attribute is displayed in the CFAS user interface.
There are check constraints/unique constraints to ensure that each group contains no more than the prescribed 22 attributes, and that no more than 10 of these attributes are numbers, no more than 10 attributes are chars, and no more than 2 attributes are dates.
| Column | Datatype | Primary Key? | Required? | Description | Notes |
|---|---|---|---|---|---|
| ATTRIB_ID | NUMBER (10) |
Y | Y | This column holds the generated ID of the attribute. | Create a sequence to generate these numbers. |
| GROUP_ID | NUMBER (10) |
N | Y | This column holds the ID of the group this attribute belongs to. | FK to CFA_ATTRIB_GROUP. |
| VIEW_COL_NAME | VARCHAR2(30) | N | Y | This column holds the description of the attribute. This description is limited to 30 characters so it can be referenced in the easy access views. This text string should not contain spaces or special characters. | Character string should be in all caps. Unique across the entity. |
| STORAGE_COL_NAME | VARCHAR2(11) | N | Y | This column holds the text string that corresponds to the storage column on the entity specific CFAS Storage/Extension table. | Check constraint - value must be in the list:
VARCHAR2_1, VARCHAR2_2, VARCHAR2_3, VARCHAR2_4, VARCHAR2_5, VARCHAR2_6, VARCHAR2_7, VARCHAR2_8, VARCHAR2_9, VARCHAR2_10, NUMBER_11, NUMBER_12, NUMBER_13, NUMBER_14, NUMBER_15, NUMBER_16, NUMBER_17, NUMBER_18, NUMBER_19, NUMBER_20, DATE_21, DATE_22 |
| DISPLAY_SEQ | NUMBER(2) | N | Y | This column determines the order in which the attribute will be displayed on the CFAS user interface. | Check Constraint - number must be between 1 and 10 if data type is VARCHAR2, 11 and 20 if data type is NUMBER or 21 and 22 if data type is DATE. |
| DATA_TYPE | VARCHAR2(8) | N | Y | This column holds the data type of the attribute. Valid values are limited to VARCHAR2, NUMBER and DATE. | Check Constraint - 'VARCHAR2', 'NUMBER', 'DATE'. |
| UI_WIDGET | VARCHAR2(2) | N | Y | This column holds the user interface item type that should be displayed on the Custom Data Entry user interface. Valid values are TI (text item), RG (record group), LI (list item), CB (check box) and DT (date).
Note that if the check box item type is used, the checked value will always be 'Y' and the unchecked value will always be 'N'. Default value is 'N' (unchecked) if the attribute is required. |
Check Constraint - valid values are TI, RG, LI, CB, DT. |
| REC_GROUP_ID | NUMBER (10) |
N | N | This column holds the ID of the record group associated with the attribute. Record groups are only needed when the attribute uses the record group ('RG') UI item type. Record groups are defined on the CFA_REC_GROUP table. CFA record groups must be defined before an attribute using the record group is defined. | FK to CFA_REC_GROUP. REC_GROUP_ID. |
| CODE_TYPE | VARCHAR2(4) | N | N | This column holds the specific custom code type associated with the attribute. Code types are only needed when the attribute uses the list item ('LI') UI item type. | FK to CFA_CODE_HEAD. CODE_TYPE. |
| ENABLE_IND | VARCHAR2(1) | N | Y | This column determines if the field is disabled (display only) or editable. The value of the attribute should be sourced from CFA_ATTRIB_GROUP_SET.DEFAULT_FUNC if the attribute is disabled. | Check Constraint - valid values are 'Y', 'N'. |
| VALUE_REQ | VARCHAR2(1) | N | Y | This column determines if the field is required to be not null when a record is created in the custom extension storage table. | Check Constraint - valid values are 'Y', 'N'. |
| MAXIMUM_LENGTH | NUMBER(20) | N | N | This column determines the maximum valid length the user can enter for field on the UI. A value is recommended for Char and Number attributes, and not valid for date attributes. | – |
| LOWEST_ALLOWED_VALUE | NUMBER (20, 4) |
N | N | This value determines if the lowest numeric value that can be entered in the field. This value is optional and should only be populated when the data type of the attribute is NUMBER or DATE. | – |
| HIGHEST_ALLOWED_VALUE | NUMBER (20, 4) |
N | N | This value determines if the highest numeric value that can be entered in the field. This value is optional and should only be populated when the data type of the attribute is NUMBER or DATE. | – |
| VALIDATION_FUNC | VARCHAR2(20) | N | N | This column holds the name (package and function) of code that should be called to validate this attribute. | For an example of the type of validation code that could be written for field level validation, see CFAS User Interface Validation Routines. |
| ACTIVE_IND | VARCHAR2(1) | N | Y | This column indicates whether the attribute is displayed in the CFAS user interface when accessed from the base user interface. Used for simulation purposes. | Check Constraint - Y,N. |
| BASE_IND | VARCHAR2(1) | Y | This column indicates if the attribute is Oracle defined. Oracle defined attributes can not be further customized. | Check Constraint - Y,N. | |
| EDITOR_REQ | VARCHAR2(1) | Y | This column indicates whether the editor is required for the attribute. | Check Constraint - Y,N. | |
| Unique constraint on group_id and storage_col_name (to ensure metadata is not defined that attempts to map two attributes to the same slot on the entity extension table) | |||||
The following table illustrates the CFA_ATTRIB table with sample data.
|
Note: From the examples above, Group id 90000000001 extends ordhead for additional PO import attributes. Groups 9000000002 extends store for additional PO shipping attributes.If more extensive validation is needed for any single attribute, the name of a custom specific PL/SQL function can be specified in VALIDATION_FUNC column on this table. Any validations specified at the attribute level will fire as item level validation (in Oracle forms terms, in when validate item triggers). |
| ATTRIB_ID | GROUP_ID | DISPLAY_SEQ | ACTIVE_IND | VIEW_COL_NAME | STORAGE_COL_NAME | DATA_TYPE | UI_WIDGET | ENABLE_IND | VALUE_REQ | MAXIMUM_LENGTH | LOWEST_ALLOWED_VALUE | HIGHEST_ALLOWED_VALUE | REC_GROUP_ID | CODE_TYPE | VALIDATION_FUNC |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 9876001 | 9000000001 | 1 | Y | ENTRY_METHOD | VARCHAR2_1 | VARCHAR2 | LI | Y | N | 4 | – |
– |
– |
ENTR | – |
| 9876002 | 9000000001 | 2 | Y | PORT | NUMBER_11 | NUMBER | TI | Y | N | 10 | – |
– |
– |
– |
– |
| 9876003 | 9000000002 | 1 | Y | SHIP_TYPE | VARCHAR2_1 | VARCHAR2 | LI | Y | N | 4 | – |
– |
– |
– |
– |
| 9876004 | 9000000003 | 2 | Y | SHIP_CAP | NUMBER_11 | NUMBER | TI | Y | N | 10 | 0 | 9999 | – |
– |
– |
| 9876005 | 9000000004 | 3 | Y | BILL_TYPE_CODE | NUMBER_12 | NUMBER | RG | Y | N | 10 | – |
– |
77777 | – |
– |
| 9876006 | 9000000004 | 4 | Y | BILL_DATE | DATE | DATE | DT | N | N | – |
– |
– |
– |
– |
– |
If additional validation is required to ensure that two attributes in the same group are somehow compatible, the inter-attribute group validation code can be listed in CFA_ATTRIB_GROUP.VALIDATION_FUNC.
If additional validation is required to ensure that groups of attributes are somehow compatible, the validation code can be listed in CFA_ATTRIB_GROUP_SET.VALIDATION_FUNC. This type of validation could be used in cases where attributes are required in either one group or another (for example, the partner is either a federal taxpayer or a local taxpayer).
This table holds descriptions that will be used to label the attribute on the CFAS user interface.
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| ATTRIB_ID | NUMBER(10) | Y | Y | This column holds the attribute id of the attribute being described. | FK to CFA_ATTRIB.ATTRIB_ID. |
| LANG | NUMBER(6) | Y | Y | This column holds the language the description is in. | FK to LANG.LANG. |
| DEFAULT_LANG_IND | VARCHAR2(1) | N | Y | This column indicates that the current records description should be considered the default and displayed in the user interface, if a translation for the end user's language does not exist. This column should be 'Y' for only one language for the GROUP_ID. | Check constraint (Y, N). |
| LABEL | VARCHAR2(255) | N | Y | This column holds the text that will be used to label the attribute on the CFA UI. | – |
This table contains the source query for record groups used by LOVs in the CFAS UI.
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| REC_GROUP_ID | NUMBER (10) |
Y | Y | This column holds generated ID of the Custom record group. | Create a sequence to generate these IDS. |
| REC_GROUP_NAME | VARCHAR2(30) | N | Y | This column holds the name of the record group. | – |
| QUERY | VARCHAR2(2000) | N | Y | This column holds the query that will be built or executed when the record group is associated with an attribute and displayed on the CFAS UI.
The query must adhere to several rules: It must be a complete, valid query that is capable of executing in a SQL*Plus session. It must follow the translation standards. It can have no more than two columns in the SELECT portion of its statement, one ID column and one description column. Bind variables to limit the query can be used. The variable names should be the same as that with the CFA_ATTRIB.VIEW_COL_NAME or CFA_EXT_ENTITY_KEY.KEY_COL Ensure that any custom record groups have appropriate, functioning queries. |
– |
| BASE_IND | VARCHAR(1) | N | Y | This indicates that the record group is pre-enabled for customization and supporting code in the base UI are already setup as part of base install. | Check constraint (Y, N). |
| QUERY_TYPE | VARCHAR(2) | N | Y | This indicates the query type (simple or complex) set up for the record group. | – |
| TABLE_NAME | VARCHAR2(30) | N | N | This indicates the relevant table name used to build the simple query for the record group. | – |
| COLUMN_1 | VARCHAR2(30) | N | N | This indicates the column name of the first column to be used in building the simple query for the record group. | – |
| COLUMN_2 | VARCHAR2(30) | N | N | This indicates the column name of the second column to be used in building the simple query for the record group. | – |
| WHERE_COL_1 | VARCHAR2(30) | N | N | This indicates the column name of the first column used in the WHERE clause of the simple query for the record group. | – |
| WHERE_OPERATOR_1 | VARCHAR2(6) | N | N | This indicates the operator for the first column used in the WHERE clause of the simple query for the record group. | – |
| WHERE_COND_1 | VARCHAR2(120) | N | N | This indicates the condition set for the first column used in the WHERE clause of the simple query for the record group. | – |
| WHERE_COL_2 | VARCHAR2(30) | N | N | This indicates the column name of the second column used in the WHERE clause of the simple query for the record group. | – |
| WHERE_OPERATOR_2 | VARCHAR2(6) | N | N | This indicates the operator for the second column used in the WHERE clause of the simple query for the record group. | – |
| WHERE_COND_2 | VARCHAR2(120) | N | N | This indicates the condition set for the second column used in the WHERE clause of the simple query for the record group. | – |
| COL_1_DATA_TYPE | VARCHAR2(106) | N | N | This indicates the data type of the first column. | – |
| COL_1_DATA_LENGTH | NUMBER(10) | N | N | This indicates the length of data set up for the first column. | – |
The following table illustrates the CFA_REC_GROUP table with sample data.
| REC_GROUP_ID | REC_GROUP_NAME | QUERY | BASE_IND | QUERY_TYPE | TABLE_NAME | COLUMN_1 | COLUMN_2 | WHERE_COL_1 | WHERE_OPERATOR_1 | WHERE_COND_1 | WHERE_COL_2 | WHERE_OPERATOR_2 | WHERE_COND_2 | COL_1_DATA_TYPE | COL_1_DATA_LENGTH |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 77777 | CITYDESC | select city_desc,
city from city where get_primary_lang = get_user_lang and country_id = USA; |
N | SIMPLE | city | city_desc | city | get_primary_lang | = |
get_user_lang | country_id | = |
USA | - | - |
This table holds the column labels that should be displayed to end users when a specific record group list of values is invoked from the CFAS user interface.
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| REC_GROUP_ID | NUMBER(10) | Y | Y | This column holds generated ID of the custom record group. | FK to CFA_REC_GROUP.REC_GROUP_ID. |
| LANG | NUMBER(6) | Y | Y | This column holds the language the of the user interface description. | FK to LANG.LANG. |
| DEFAULT_LANG_IND | VARCHAR2(1) | N | Y | This column indicates that the current records description should be considered the default and displayed in the user interface, if a translation for the end user's language does not exist. This column should be 'Y' for only one language for the REC_GROUP_ID. | Check constraint (Y, N). |
| LOV_TITLE | VARCHAR2(255) | N | Y | This column holds the language specific value for the LOV title. This value will be displayed in the LOV header when the record group is invoked from the CFAS user interface. | – |
| LOV_COL1_HEADER | VARCHAR2(255) | N | Y | This column holds the language specific value for the first LOV column header. This value will be displayed in the LOV header when the record group is invoked from the CFAS user interface. | – |
| LOV_COL2_HEADER | VARCHAR2(255) | N | Y | This column holds the language specific value for the second LOV column header. This value will be displayed in the LOV header when the record group is invoked from the CFAS user interface. | – |
The following table illustrates the L10N_REC_GROUP_DESCS table with sample data.
| L10N_REC_GROUP_ID | LANG | DEFAULT_LANG_IND | LOV_TITLE | LOV_COL1_HEADER | LOV_COL2_HEADER |
|---|---|---|---|---|---|
| 77777 | 1 | Y | List of Cities | City Description | City |
| 77777 | 4 | N | List of Cities (In Spanish) | City Description (In Spanish) | City (In Spanish) |
This table holds the code types that will be used as the basis for defining list items in the CFAS UI.
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| CODE_TYPE | VARCHAR2(4) | Y | Y | This column holds the distinct code type. This code type can be related to a list item on the CFAS user interface. | – |
| CODE_TYPE_DESC | VARCHAR2(120) | N | Y | This column holds a description of the code type. The code type description is never displayed to end users, so no translation is necessary. This data exists only to make it easier for you to ensure that you have selected the correct CODE_TYPE when creating the metadata for a list item CFAS attribute. | – |
This table holds the code/descriptions within a code type that will displayed as individual choices within the list items on the CFA UI.
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| CODE_TYPE | VARCHAR2(4) | Y | Y | This column holds the distinct code type. This code type can be related to a list item on the CFAS user interface. | FK to CFA_CODE_HEAD.
CODE_TYPE. |
| CODE | VARCHAR2(6) | Y | Y | This column holds a code within the code type. This code will be an individual item within the list on the CFAS user interface widget. | – |
| SEQ_NO | NUMBER(4) | N | Y | This column determines the order the code values will be displayed within the list. | Unique within a code_type and lang. |
| LANG | NUMBER(6) | Y | Y | This column defines the language of the code description. | FK to LANG.LANG. |
| DEFAULT_LANG_IND | VARCHAR2(1) | N | Y | This column indicates that the current records description should be considered the default and displayed in the user interface, if a translation for the end user's language does not exist. This column should be 'Y' for only one language for the CODE_TYPE/ CODE combination. | Check constraint (Y, N). |
| CODE_DESC | VARCHAR2(40) | N | Y | This column holds the text value that will be displayed within the list to the end user. | – |
The following table illustrates the CFA_CODE_DETAIL_DESCS table with sample data.
| CODE_TYPE | CODE | SEQ_NO | LANG | DEFAULT_LANG_IND | CODE_DESC |
|---|---|---|---|---|---|
| BILT | ABC | 1 | 1 | Y | ABC Description |
| BILT | BCD | 2 | 1 | Y | BCD Description |
| BILT | CDE | 3 | 1 | Y | CDE Description |
| BILT | DEF | 4 | 1 | Y | DEF Description |
| BILT | ABC | 1 | 4 | N | ABC Description (In Spanish) |
| BILT | BCD | 2 | 4 | N | BCD Description (In Spanish) |
| BILT | CDE | 3 | 4 | N | CDE Description (In Spanish) |
| BILT | DEF | 4 | 4 | N | DEF Description (In Spanish) |
Each time a business entity is extended for customization, an appropriate entity specific CFAS storage table must be created. Creating this table is part of the installation process. This table will be part of the base RMS data model for those entities that are pre-enabled. Each time you extend a business entity (other than the pre-enabled ones) for customization, you must create a relevant entity specific CFAS storage table.
The general structure of this table is as follows:
Primary Key of BASE_RMS_TABLE
Group_id
10 columns that can hold attributes of the VARCHAR2 data type, named VARCHAR2_1, VARCHAR2_2, VARCHAR2_3, through to VARCHAR2_10. These columns must each be VARCHAR2(250).
10 columns that can hold attributes of the NUMBER data type, named NUMBER_11, NUMBER_12, NUMBER_13, through to NUMBER_20.
Two columns that can hold attributes of type DATE, named DATE_21 and DATE_22.
These column names do not have business meaning to the end user, but are generic enough to store data for most business requirements. Note that most custom specific code will not be written directly against these entity-specific extension tables, but instead around access views. The access views join to the metadata definition tables to provide a structure that has far more business meaning.
As an example, if the goal is to add extensions to purchase orders, there should be a record on CFA_EXT_ENTITY.
| EXT_ENTITY_ID | BASE_RMS_TABLE | CUSTOM_EXT_TABLE |
|---|---|---|
| 3000000001 | ORDHEAD | ORDHEAD_CFA_EXT |
The structure of ORDHEAD_CFA_EXT should then be:
| Column | Datatype | Primary Key? | Required? | Description | Keys/Notes |
|---|---|---|---|---|---|
| ORDER_NO | NUMBER(8) | Y | Y | This column holds the PO this extended data is associated with. | FK to ordhead.order_no. |
| GROUP_NO | NUMBER(10) | Y | Y | This column holds the attribute group id that this extended data is associated with. The logical business meaning of the VARCHAR_ , NUMBER_ and DATE_ columns on this table are determined by the metadata defined for this attribute. | FK to CFA_ATTRIB_GROUP.GROUP_NO. |
| VARCHAR2_1 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_1 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_2 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_2 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_3 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_3 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_4 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_4 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_5 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_5 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_6 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_6 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_7 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_7 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_8 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_8 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_9 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_9 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| VARCHAR2_10 | VARCHAR2(250) | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references VARCHAR2_10 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_11 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_1 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_12 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_2 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_13 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_3 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_14 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_4 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_15 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_5 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_16 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_6 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_17 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_7 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_18 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_8 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_19 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_9 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| NUMBER_20 | NUMBER | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references NUMBER_10 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| DATE_21 | DATE | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references DATE_1 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
| DATE_22 | DATE | N | N | This column holds data related to the attribute defined on the CFA_ATTRIB table that references DATE_2 as it's CFA_ATTRIB.STORAGE_COL_NAME. | – |
If more than 10 varchar2 attributes, 10 number attributes, or two date attributes are required, you must define additional attribute groups.
Attribute groups also determine how the information is grouped on the CFAS user interface. When designing the CFAS metadata, it is also important to consider the advantages of grouping attributes into multiple functionally related groups from an entity or group set combination.
The CFAS framework uses the following supporting custom objects that are generated based on the definitions in the Extension Installation and CFAS Metadata tables. When you modify the metadata, the access views and staging tables will need to be generated again:
These optional objects are business representation of the data that is to be stored in the generic CFAS extension tables.
The main purpose of CFAS access views is to simplify the data access to the metadata driven extension tables. It is recommended that custom code or external systems query the CFAS data from these views rather than directly accessing the CFAS extension tables (depending on performance requirements).
The view names are stored at two levels, at the group set level and at the group level. At the group set level, the names are stored in CFA_ATTRIB_GROUP_SET.GROUP_SET_VIEW_NAME and at the group level in CFA_ATTRIB_GROUP.GROUP_VIEW_NAME. Both these views will have column names defined in CFA_ATTRIB.VIEW_COL_NAME.
From the metadata definition examples earlier, the following tables illustrate the view and column name storage in the relevant tables.
CFA_ATTRIB_GROUP_SET
| GROUP_SET_ID | EXT_ENTITY_ID | DISPLAY_ORDER | GROUP_SET_VIEW_NAME |
|---|---|---|---|
| 1000000001 | 3000000001 (ORDHEAD) | 1 | V_CFA_PO_CUSTOM |
| 1000000003 | 3000000002 (ITEM_SUPP_COUNTRY) | 1 | V_CFA_ISC_TELCO |
CFA_ATTRIB_GROUP
| GROUP_ID | GROUP_SET_ID | GROUP_VIEW_NAME |
|---|---|---|
| 9000000001 | 1000000001 | V_CFA_PO_IMPORT |
| 9000000002 | 1000000001 | V_CFA_PO_SHIPPING |
| 9000000004 | 1000000001 | V_CFA_PO_BILLING |
| 9000000008 | 1000000003 | V_CFA_ISC_ITEM_RESTRICT |
| 9000000009 | 1000000003 | V_CFA_ISC_INV |
CFA_ATTRIB
The following table displays the subset of columns used in the view definition.
| ATTRIB_ID | GROUP_ID | ACTIVE_IND | VIEW_COL_NAME | STORAGE_COL_NAME | DATA_TYPE |
|---|---|---|---|---|---|
| 9876001 | 9000000001 | Y | ENTRY_METHOD | VARCHAR2_1 | VARCHAR2 |
| 9876002 | 9000000001 | Y | PORT | NUMBER_11 | NUMBER |
| 9876003 | 9000000002 | Y | SHIP_TYPE | VARCHAR2_1 | VARCHAR2 |
| 9876004 | 9000000003 | Y | SHIP_CAP | NUMBER_11 | NUMBER |
| 9876005 | 9000000004 | Y | BILL_CODE | NUMBER_12 | NUMBER |
| 9876006 | 9000000004 | Y | BILL_DATE | DATE | DATE |
The structure of the sample view at the group view set will be:
V_CFA_PO_CUSTOM
(
ENTRY_METHOD VARCHAR2(250),
PORT NUMBER,
SHIP_TYPE VARCHRA2(250),
SHIP_CAP NUMBER,
BILL_CODE NUMBER
BILL_DATE DATE
);
At the group level there will be several views, such as:
V_CFA_PO_IMPORT
(
ENTRY_METHOD VARCHAR2(250),
PORT NUMBER
);
V_CFA_PO_SHIPPING
(
SHIP_TYPE VARCHRA2(250),
SHIP_CAP NUMBER
);
V_CFA_PO_BILLING
(
BILL_CODE NUMBER
BILL_DATE DATE
);
Both levels are optional and you can determine the suitable view level based on your business needs.
When group sets, groups, and attributes are defined for CFAS, the information is stored as metadata on a series of base RMS tables. When the database objects are created based on the metadata set up, they are created as generic tables, with column names like NUMBER_1 and VARCHAR2_4. Since this makes querying data for the attributes very difficult, views are created when the activation scripts are run at the group set and group level.
The attribute group set view will contain all the groups and attributes in that group set. The view at the group level will only contain the attributes for that particular group. Depending on the way you have organized the attributes or the particular needs of the query, one or the other may be used. For example, if an attribute group (A) is created with 4 attributes for Order, the view will look similar to the following:
Order number
<Attribute A1>
<Attribute A2>
<Attribute A3>
<Attribute A4>
At the group set level, if there were two attribute groups in the set with group A being one and group B (with 5 attributes) the other, it will look similar to the following:
Order number
<Attribute A1>
<Attribute A2>
<Attribute A3>
<Attribute A4>
<Attribute B1>
<Attribute B2>
<Attribute B3>
<Attribute B4>
<Attribute B5>
|
Note: The group set and group numbers are not part of the views. |
The CFAS staging table is used for importing data from external source to the extension tables (reverse functionality of the CFAS access views).
The name of the staging table is not stored in the CFAS installation table, but derived from value stored in the CFA_EXT_ENTITY.CUSTOM_EXT_TABLE by prefixing with STG_. A single staging table is created per extension entity.
| EXT_ENTITY_ID | BASE_RMS_TABLE | CUSTOM_EXT_TABLE | Derived CFAS Staging Table Name |
|---|---|---|---|
| 3000000001 | ORDHEAD | ORDHEAD_CFA_EXT | STG_ORDHEAD_CFA_EXT |
| 3000000002 | ITEM_SUPP_COUNTRY | ITEM_SUP_CTRY _CFA_EXT | STG_ ITEM_SUP_CTRY _CFA_EXT |
| 3000000003 | STORE | STORE_CFA_EXT | STG_ STORE_CFA_EXT |
| 3000000004 | SUPS | SUPS_CFA_EXT | STG_ SUPS_CFA_EXT |
The columns of the staging table will be all the values in the CFA_ATTRIB.VIEW_COL_NAME for the extended entity.
Staging tables are created for the attribute group set level when the activation scripts are run. The layout of the staging tables are the same as the layout of the view. Staging tables allow data from external sources to update the flex attributes. You can use the CFAS Load scripts to take the data into these staging tables and load them into the CFAS tables.
The data load scripts include only basic validation (for example, data type) for the data to be uploaded. Errors with the data (for example, invalid store number) will not be caught until the users open the relevant form containing the attributes. Ensure that you set up the relevant validation before loading the data from the staging table.
