Understanding Effective Dated Entities Behavior - Creation

JDev Version 11.1.2.

Effective dated entities are used to implement the functionality required to view data related to a specific point in time. Within Effective dated entities, there are two variations: 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:

In addition, you need to add 4 extra attributes to allow the framework to implement the effective dated functionality. Those attributes are:

• StartDate attribute: An attribute with Date data type, which StartDate property selected.
• EndDate attribute: An attribute with Date data type, which EndDate property selected.
• Sequence attribute: An attribute with Sequence property selected. I have used String data type for this attribute.
• SequenceFlag attribute: An attribute with Sequence Flag property selected. I have used String data type for this attribute.

The Sequence and SequenceFlag attribute are required for Effective Dated entities only if you want to enable multiple changes in a single day. Otherwise, only StartDate and EndDate attributes are required.

When you create an effective dated entity object, JDeveloper creates a transient attribute called SysEffectiveDate to store the effective date for the row. This attribute is used to find/match the records to update, as the following type of where clause is appended to the query at runtime:

:SysEffectiveDateBindVar BETWEEN MultiChangesSingleDayEO.START_DATE AND MultiChangesSingleDayEO.END_DATE

At runtime, you can pass the value for the SysEffectiveDate attribute by setting the AM's property EFF_DT_PROPERTY_STR, as shown below:

            ApplicationModuleImpl rootAM = this.getRootApplicationModule();
            rootAM.setProperty(EFF_DT_PROPERTY_STR, effectiveDate);

At runtime, the bind value for the query is obtained from a property of the root application module.
The default value of effective date is current date. So if EFF_DT_PROPERTY_STR property of AM is not set, current date is used as effective date. The Effective Dated VOs are created using Effective Dated EOs.

I am using the following App for this blog:

To test all the scenarios, I have created the followings Effected Dated VOs:

MultiChangesSingleDayVO: To capture multiple changes in a single day.
SingleChangeSingleDayVO: To allow only single change in a day.
DatedVO: Its is a Dated VO, used in the blog 'Difference Between Dated and Effective Dated Entity Types'.


MultiChangesSingleDayVO and DatedVO are based on the following table:

CREATE TABLE MULTI_CHANGES_SINGLE_DAY_TABLE (
  id            NUMBER(4,0)  NOT NULL,
  text          VARCHAR2(10) NOT NULL,
  start_date    DATE         NOT NULL,
  end_date      DATE         NULL,
  "SEQUENCE"      VARCHAR2(10) NOT NULL,
  sequence_flag VARCHAR2(1)  NOT NULL
)
/

ALTER TABLE MULTI_CHANGES_SINGLE_DAY_TABLE
  ADD CONSTRAINT MCSDT_PK1 PRIMARY KEY (
    id,
    start_date,
    "SEQUENCE",
    sequence_flag
  )
/

SingleChangeSingleDayVO is based on the following table:

CREATE TABLE SINGLE_CHANGE_SINGLE_DAY_TABLE (
  id         NUMBER(4,0)  NOT NULL,
  text       VARCHAR2(10) NOT NULL,
  start_date DATE         NOT NULL,
  end_date   DATE         NOT NULL
)
  PCTUSED    0
/

ALTER TABLE SINGLE_CHANGE_SINGLE_DAY_TABLE
  ADD CONSTRAINT SCSDT_PK1 PRIMARY KEY (
    id,
    start_date,
    end_date
  )
/

I have created a composite Primary key for both the tables because without it, Effective Dates VOs throw TooManyObjects exceptions for row split operations. It's not documented so posted this question on the following forum post to confirm:

https://forums.oracle.com/forums/thread.jspa?threadID=2280616&tstart=0

Effective Dated Entities Creation Behavior

When we create a new row for an Effective Dated Entity, the framework automatically takes care for the default values of the four Effective Dated attributes: StartDate, EndDate, Sequence & SequenceFlag. Sequence & SequenceFlag attributes are used to track multiple changes in a single day. Sequence shows the update count, whereas SequenceFlag is set to Y for the recently updated record.
I think these two attribute should not be modified manually. However, for StartDate, and EndDate we can provide values while creation.
For example, see the following figure:

Here we have only specified the values for 3 attributes, ID, Text, & transient attribute SysEffectiveDate. On commit, other attributes are automatically populated, as shown below:

Similarly, you can provide values for StartDate and EndDate as well, as shown below:

On commit, remaining attributes are automatically populated, as shown below: