Effective Dated Entity Objects - with Modes and Example

Effective dated tables are used to provide a view into the data set pertaining to a specific point in time. Effective dated tables are widely used in application.

There are two variations in effective dated entity, One that allows multiple changes in a single day, and the other one which does not.  To make an entity object effective dated, you need to mark the entity object as Effective Dated, as shown below:

to do this we need two Date attributes (Start Date Attribute and  End Date Attribute) , to do multiple changes in a day then one number attribute (Effective Date Sequence) and one string attribute (Effective Date Sequence Flag).

• For Start Date Attribute, select the attribute that corresponds to the start date.
• For End Date Attribute, select the attribute that corresponds to the end date.
• For Effective Date Sequence, select the attribute that stores the sequence of changes.
• For Effective Date Sequence Flag, select the attribute that stores a flag indicating the most recent change in the sequence.

Create a simple Model project based on the fallowing data base table .

            CREATE TABLE EMP_DATE_EFF
            (
              EMP_ID NUMBER NOT NULL
            , EMP_NAME VARCHAR2(30) NOT NULL
            , START_DATE DATE NOT NULL
            , END_DATE DATE NOT NULL
            , CREATED_BY VARCHAR2(20) NOT NULL
            , MODIFIED_BY VARCHAR2(20) NOT NULL
            , CREATED_DATE DATE NOT NULL
            , MODIFIED_DATE DATE NOT NULL
            , EFF_DATE_SEQ NUMBER NOT NULL
            , EFF_DATE_FLAG VARCHAR2(20)
            , CONSTRAINT EMP_DATE_EFF_PK PRIMARY KEY
              (
                EMP_ID
              , START_DATE
              , END_DATE
              , EFF_DATE_SEQ
              )
              ENABLE
            );

To make an entity object as date effective. Open the EO -> Click on General Tab -> Open property inspector -> Click on Type Tab -> Right click on " Effective Date Type" -> Select "EffectiveDated".

 Than select the appropriate attributes from drop down.


Created a simple application module method to change the data in this table.

In this method i am taking Mode as a parameter, Based on the param i will change the mode or the date effective data.

EX:

        EFFDT_NONE_MODE = -1;
        EFFDT_EXPERT_MODE = 0;
        EFFDT_UPDATE_CORRECTION = 1;
        EFFDT_UPDATE_MODE = 2;
        EFFDT_UPDATE_OVERRIDE_MODE = 3;
        EFFDT_UPDATE_CHANGE_INSERT_MODE = 4;
        EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE = 5;
        EFFDT_DELETE_MODE = 6;
        EFFDT_DELETE_THIS_CHANGE_MODE = 7;
        EFFDT_DELETE_NEXT_CHANGE_MODE = 8;
        EFFDT_DELETE_FUTURE_CHANGE_MODE = 9;
        EFFDT_DELETE_ZAP_MODE = 10;

EFFDT_DELETE_FUTURE_CHANGE_MODE
    When an effective dated row is deleted in "delete future change" mode, the end date of the row is set to the end of time and all the future rows for the same key values are deleted.

EFFDT_DELETE_MODE
    When an effective dated row is deleted in "delete" mode, the end date of the row is set to the row's effective date and all the future rows for the same key values are deleted.

EFFDT_DELETE_NEXT_CHANGE_MODE
    When an effective dated row is deleted in "delete next change" mode, the end date of the row is set to the end date of adjoining row and the adjoining row is deleted.

EFFDT_DELETE_THIS_CHANGE_MODE
    When an effective dated row is deleted in "delete this change" mode, the current row is removed.

EFFDT_DELETE_ZAP_MODE
    When an effective dated row is deleted in "zap" mode, all the effective dated rows with the same key values are deleted.

EFFDT_EXPERT_MODE
    If the row is in a expert mode, the runtime will not carry out any effective date specific logic for row modifications.

EFFDT_NONE_MODE
    Default state of the effective date mode on the row.

EFFDT_UPDATE_CHANGE_INSERT_MODE
    When an effective dated row is updated in "change insert" mode, the modified row is end dated on the effective date and a new row is inserted that fits between the effective date and the start date of the next row in the effective date time line.

EFFDT_UPDATE_CORRECTION
    When an effective dated row is updated in "correction" mode, the effective start date and effective end date is left unchanged.

EFFDT_UPDATE_MODE
    When an effective dated row is updated in "update" mode, the modified row is end dated on the effective date and a new row is created with the changed values.

EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE
    Updating in "new earliest change" mode is supported only in Multiple Changes Per Day.

EFFDT_UPDATE_OVERRIDE_MODE
    When an effective dated row is updated in "override" mode, the modified row is end dated on the effective date and the start date of the next row in the effective date time line is set to effective date + 1 day.

Case -1

i update the employee data in EFFDT_UPDATE_MODE (mode -2), by changing the employee name to "Krishna". Then it created the second row and update the Name.

Case -2

i update the employee data in EFFDT_UPDATE_CORRECTION (mode -1), by changing the employee name to "Sundar Krishna" and date as "2013-09-09". Then it modified(Corrected) the existing record.

Sample Code.

Note:

Because the date-effective view object must be based on an date-effective entity object, setting a view object's Effective Dated property to True without an underlying date-effective entity object, will result in a runtime exception.

End of your application if you turn off your "date effective" in entity object then, then complete effective dated features are ignored.

For effective date, It first obtained from the driving row(current row). If it is not available, then it is obtained from the property EFF_DT_PROPERTY_STR of the root application module. If you do not set EFF_DT_PROPERTY_STR for the application module, the current date is used in the query filter