JDev11g new feature: Effective Dated Entity Objects

Many data models have the concept of effective dates where each row includes effective_from and effective_to dates. For example an employee's wage may very over time, so we store multiple records each with an effective date range with no overlaps in time (unless we want to pay our employees double of course). Within the database we could store this in a separate table to the employees table, tracking the employees changing wages to a specific time period:

CREATE TABLE emp_wages
(wage_id NUMBER(10) PRIMARY KEY
,emp_id NUMBER(10) CONSTRAINT emp_wages_fk REFERENCES employees(emp_id)
,eff_from DATE NOT NULL
,eff_to DATE
,wage NUMBER(10));

INSERT INTO emp_wages
VALUES (1, 1000, '19/OCT/2005','01/MAR/2007', 50000);

INSERT INTO emp_wages
VALUES (2, 1000, '01/MAR/2007','01/JUN/2007', 60000);

INSERT INTO emp_wages
VALUES (3, 1000, '01/JUN/2007', NULL, 70000);

A potential query based on this data is what was the employee's wages 1st April 2007 with the desired result being record 2.

With JDeveloper 10g a potential solution to this using ADF Business Components was to deliver a default EO/VO based on emp_wages, and within the VO add a bind variable and modify the query such that the data could be queried at a certain date.

JDeveloper 11g introduces the concept of Effective Dated Entity Objects which effectively (no pun intended) does this for you based on property settings. You can find documentation on this within the JDev 11g Fusion Guide under section 4.2.8 and section 5.4.

To turn this facility on do the following in your EO:

• In your EO identify your eff_from attribute. In the Edit Attribute dialog select the Effective Date checkbox followed by the Start radio button.

• Ditto for the eff_to attribute, except select the End radio button.

• With the EO document window open, select the General tab, then within the Property Inspector under the Type category, and change the Effective Date Type property = EffectiveDated.

• Return to the EO document window, under the Attribute tab you'll see a new transient attribute column SysEffectiveDate.

You next need to turn the facility on for the specific VO you want to support this functionality:

• Open the VO document window.

• With the General tab selected, in the Property Inspector set the Effective Dated field = True.

• You'll note within the VO document window Attribute tab, the EO attribute transient attribute SysEffectiveDate.

On running the Business Component Browser and opening the specific VO you'll be prompted to enter a date for SysEffectiveDate. On entering a valid date (eg. 2007-04-01).....


....the records returned in the result set are filtered to this specific date:


If you don't enter a date for the bind variable, the current date is taken as default.

Returning back to the EO Effective Date Type property, you will have noted in the Edit Property dialog you had the option of selecting either EffectiveDated or Dated. The difference in functionality is best explained by the queries ADF BC undertakes on your behalf:

EffectiveDated query:

SELECT emp_wages.wage_id
,emp_wages.emp_id
,emp_wages.eff_from
,emp_wages.eff_to
,emp_wages.wage
FROM emp_wages emp_wages
WHERE (:Bind_SysEffectiveDate
BETWEEN emp_wages.eff_from
AND emp_wages.eff_to)

Dated query:

SELECT emp_wages.wage_id
,emp_wages.emp_id
,emp_wages.eff_from
,emp_wages.eff_to
,emp_wages.wage
FROM emp_wages emp_wages
WHERE (:Bind_SysEffectiveDate
BETWEEN emp_wages.eff_from
AND COALESCE(emp_wages.eff_to, TO_DATE('12/31/4712', 'MM/DD/RRRR')))