Cascading Dependencies with $FLEX$

In this practice, you will create a descriptive flexfield that uses cascading dependencies to validate the values entered. The flexfield will track the manufacturer, model, and color of a vehicle. The possible model values will be restricted depending on the manufacturer value, and the possible color values will be restricted depending on the model value.
To set up the cascading dependencies, you will create table-validated value sets using the following tables:

CREATE TABLE APPS.CAR_MAKERS
(
MANUFACTURER_ID NUMBER,
MANUFACTURER_NAME VARCHAR2(10),
MANUFACTURER_DESCRIPTION VARCHAR2(10)
)

CREATE TABLE APPS.CAR_MODELS
(
MODEL_ID NUMBER,
MODEL_NAME VARCHAR2(10),
MODEL_DESCRIPTION VARCHAR2(10),
MANUFACTURER_ID NUMBER
)

CREATE TABLE APPS.CAR_COLORS
(
COLOR_ID NUMBER,
COLOR_NAME VARCHAR2(10) ,
COLOR_DESCRIPTION VARCHAR2(10) ,
MODEL_ID NUMBER ,
MANUFACTURER_ID NUMBER
)


SET DEFINE OFF;
Insert into CAR_MAKERS
   (MANUFACTURER_ID, MANUFACTURER_NAME, MANUFACTURER_DESCRIPTION)
 Values
   (1, 'Ford', 'Ford');
Insert into CAR_MAKERS
   (MANUFACTURER_ID, MANUFACTURER_NAME, MANUFACTURER_DESCRIPTION)
 Values
   (2, 'Nissan', 'Nissan');
Insert into CAR_MAKERS
   (MANUFACTURER_ID, MANUFACTURER_NAME, MANUFACTURER_DESCRIPTION)
 Values
   (3, 'Chevrolet', 'Chevrolet');
Insert into CAR_MAKERS
   (MANUFACTURER_ID, MANUFACTURER_NAME, MANUFACTURER_DESCRIPTION)
 Values
   (4, 'Chrysler', 'Chrysler');
COMMIT;

SET DEFINE OFF;
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (11, 'Mustang', 'Mustang', 1);
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (12, 'Windstart', 'Windstart', 1);
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (13, '240SX', '240SX', 2);
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (14, 'Altima', 'Altima', 2);
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (15, 'Corvette', 'Corvette', 3);
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (16, 'Camaro', 'Camaro', 3);
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (17, 'Sebring', 'Sebring', 4);
Insert into CAR_MODELS
   (MODEL_ID, MODEL_NAME, MODEL_DESCRIPTION, MANUFACTURER_ID)
 Values
   (18, 'Concorde', 'Concorde', 4);
COMMIT;

SET DEFINE OFF;
Insert into CAR_COLORS
   (COLOR_ID, COLOR_NAME, COLOR_DESCRIPTION, MODEL_ID, MANUFACTURER_ID)
 Values
   (101, 'Red', 'Red', 11, 1);
Insert into CAR_COLORS
   (COLOR_ID, COLOR_NAME, COLOR_DESCRIPTION, MODEL_ID, MANUFACTURER_ID)
 Values
   (102, 'Black', 'Black', 11, 1);
Insert into CAR_COLORS
   (COLOR_ID, COLOR_NAME, COLOR_DESCRIPTION, MODEL_ID, MANUFACTURER_ID)
 Values
   (107, 'Champagne', 'Champagne', 13, 2);
Insert into CAR_COLORS
   (COLOR_ID, COLOR_NAME, COLOR_DESCRIPTION, MODEL_ID, MANUFACTURER_ID)
 Values
   (115, 'Yellow', 'Yellow', 15, 3);
Insert into CAR_COLORS
   (COLOR_ID, COLOR_NAME, COLOR_DESCRIPTION, MODEL_ID, MANUFACTURER_ID)
 Values
   (119, 'Maroon', 'Maroon', 17, 4);
COMMIT;


Define your Value Sets
Responsibility: System Administrator
1- (N) Application > Validation > Set. Enter the information for the first value set in the following fields:
• Value Set Name: INAM_MFG
• Description: INAM Car Manufacturers Value Set
• Security Type: Non-Hierarchical
• Format Type: Char
• Maximum Size: 10
• Validation Type: Table
Click Edit Information to navigate to the Validation Table Information window.  Enter CAR_MAKERS as the table name.
In the Table Columns region, enter the following information for the Value column:
                  Name                                                          Type                       Size
Value         MANUFACTURER_NAME                      VarChar2                  10
Meaning     MANUFACTURER_DESCRIPTION        VarChar2                  30
ID                   MANUFACTURER_ID                         Number                      2

Save your work.
2- Enter the information for the second value set in the following fields:
• Value Set Name: INAM_MODEL
• Description: INAM Car Models Value Set
• Security Type: Non-Hierarchical Security
• Format Type: Char
• Maximum Size: 10
• Validation Type: Table
Click Edit Information to navigate to the Validation Table Information window.
Enter CAR_MODELS as the table name. In the Table Columns region, enter the following information for the Value column:
                                 Name                                  Type              Size
Value                        MODEL_NAME                 VarChar2     10
Meaning                    MODEL_DESCRIPTION   VarChar2     30
ID                             MODEL_ID                         Number         3
In the Where/Order By field, enter
WHERE MANUFACTURER_ID = :$FLEX$.INAM_MFG

Save your work.
3- Enter the information for the third value set in the following fields:
• Value Set Name: INAM_COLOR
• Description: INAM Car Colors Value Set
• Security Type: Non-Hierarchical Security
• Format Type: Char
• Maximum Size: 10
• Validation Type: Table
Click Edit Information to navigate to the Validation Table Information window. Enter CAR_COLORS as the table name. In the Table Columns region, enter the following information for the Value column:
                                 Name                                     Type                  Size
Value                        COLOR_NAME                    VarChar2           10
Meaning                    COLOR_DESCRIPTION      VarChar2           30
ID                             COLOR_ID                            Number               4
In the Where/Order By field, enter:
WHERE MODEL_ID = :$FLEX$.INAM_MODEL
ORDER BY COLOR_NAME

Save your work.
Define your Structure
1- (N) Application > Flexfield > Descriptive > Segments. Query your descriptive flexfield (eg; Journal Categories)  in the Descriptive Flexfield Segments window. Clear the Freeze Flexfield Definition check box.
Select the Global Data Elements context and click (B) Segments to navigate to the Segments Summary
window. Delete the segments that you defined previously. Save your work.
2- Enter the following information for the segments (pic below) :
Note: Save after each segment.

Save your work.
Navigate back to the Descriptive Flexfield Segments window.
Select the Freeze Flexfield Definition check box.
Click (B) Compile to compile the flexfield definition. Click (B) OK to each note about submitting your request to generate the flexfield view.
Test your Descriptive Flexfield
After defining your value sets and segments, navigate to your descriptive flexfield using the General Ledger Super User responsibility and test the results of your work. When you are finished, exit without saving.