ADF Model: Different Date Effective Delete Modes and behavior with Examples

After learning about different Date-Effective update modes available in ADF 11g and their behavior, now let us move to date-effective delete modes. Understanding and using different DE delete modes also little tricky. Please go through below explanation of different DE delete modes along with examples.

All the date-effective delete modes are defined as constants in oracle.job.Row class as any kind of effective dated operation is performed on a row itself. Before going into details, let us take an example to explain the concepts. For example, we'll take the same example of 'Job' date-effective object and explain different date-effective delete modes available. Sample application having the required code to perform the DE delete operations can be downloaded from here.

The following DE delete modes available in Jdeveloper 11g:
1.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.
For example, if the Job with JobId 100000020529001 has the following date-effective rows,

And, if the current row is [100000020529001,01-Jan-2011,31-Dec-2012] (or if the current effective date falls in the range of 01-Jan-1900 and 31-Dec-2012) and if we want to delete the next date-effective row i.e., [100000020529001,01-Jan-2013,31-Dec-4712], we'll use EFFDT_DELETE_NEXT_CHANGE_MODE.

After deleting the row in EFFDT_DELETE_NEXT_CHANGE_MODE, the resultant rows will be:

2. EFFDT_DELETE_THIS_CHANGE_MODE: When an effective dated row is deleted in "delete this change" mode, the current row is removed.
For example, let us continue with the same above 3 rows and if we want to delete the Job Row [100000020529001,01-Jan-1950,31-Dec-2010], we need to make this row as current row (i.e., the effective date will be between 1-Jan-1950 and 31-Dec-2010) and delete the row in EFFDT_DELETE_THIS_CHANGE_MODE.

 After deleting the current row in EFFDT_DELETE_THIS_CHANGE_MODE, the resultant rows will be:


3.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.
For example, let us assume that we have the following 4 date effective rows for the job with JobId 100000020529001

And, if we want to end date the 2nd row i.e., [100000020529001,01-Jan-1950,31-Dec-1974] at 01-Jan-1960 and want to delete all the future rows, we'll pass the effective date 01-Jan-1960 and use the mode EFFDT_DELETE_MODE.

After deleting the rows in EFFDT_DELETE_MODE mode, the resultant rows will be:

4.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.
For example, let us assume that we have the following 4 date effective rows for the job with JobId 100000020529001

And, if we want to delete all future date-effective rows starting from 01-Jan-1975, then, we need to make the row [100000020529001,01-Jan-1950,31-Dec-1974] as current row (or make effective date between 01-Jan-1950 and 31-Dec-1974) and delete the rows in EFFDT_DELETE_FUTURE_CHANGE_MODE. This will delete all remaining future rows and end date the current row i.e., [100000020529001,01-Jan-1950,31-Dec-1974] till EOT(31-12-4712).

 The resultant rows after deleting the rows in EFFDT_DELETE_FUTURE_CHANGE_MODE will be:


5.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.
For example, let us assume that we have the following 4 date effective rows for the job with JobId 100000020529001

And, if we want to delete all date-effective records of this job, we need to use EFFDT_DELETE_ZAP_MODE. Effective Date won't make any difference in this mode as it'll delete all existing date-effective records.

After deleting the rows in Zap mode,  all the date-effective rows got deleted:


Here is the method that I wrote to accomplish all of these DE delete operations with different DE delete modes (download the sample application for to find the method references used in the below code).

  public void deleteJobRow(Long jobId, Date effectiveDate) {
      if (effectiveDate == null) {
          throw new JboException("Effective Date cannot be null");
      }
      if (effectiveDate.compareTo(DateUtils.convertTosqlDate(MIN_START_DATE)) <=
          0 ||
          effectiveDate.compareTo(DateUtils.convertTosqlDate(MAX_END_DATE)) >=
          0) {
          throw new JboException("EffectiveDate cannot be before SOT and cannot be later than EOT");
      }
      //getting the Job row effective as of the given effectiveDate
      Row jobRow = getRowAsOfDate(jobId, effectiveDate);
      if (jobRow != null) {
          try {
              //Set the required date-effective delete mode. Here I'm setting it to EFFDT_DELETE_THIS_CHANGE_MODE
              jobRow.setEffectiveDateMode(Row.EFFDT_DELETE_THIS_CHANGE_MODE );
              jobRow.remove();
          } catch (Exception e) {
              e.printStackTrace();
          }
          this.getDBTransaction().commit();
      }
  }

In the above method, we're actually getting the Job row effective as of the passed effective date(this will make that effective-dated row as current row) and then performing date-effective delete operations basing on that row.

Instructions to run the sample application:
1. Create the required tables for illustrating date-effective operations executing the sql script downloading from here.

2. Unzip the sample application and, run the DemoAM. Input JobId, effective date and click 'Execute'.

3. Change the date-effective delete mode in method 'deleteJobRow' each time you want to change the delete mode(right now it's hard coded to EFFDT_DELETE_THIS_CHANGE_MODE), rebuild and continue. You can find the screen shots above how to provide input in AM Tester.

4. Query the DB to find the resultant rows after the delete operation.

SELECT Job_ID,
  TO_CHAR(effective_start_date,'dd-Mon-yyyy') AS ESD,
  TO_CHAR(effective_end_date,'dd-Mon-yyyy')   AS EED,
  JOB_NAME,
  MIN_SAL,
  MAX_SAL,
  STATUS,
  JOB_LEVEL,
  MED_CHECK_REQ AS MED_CHECK
FROM de_jobs
WHERE job_id=100000020529001
ORDER BY effective_start_date;

Enjoy!!!

ADF Model: Different Date Effective Update Modes and behavior with Examples

When it comes to date-effectivity, the tricky part is date-effective updates to the rows. It's not as simple as updating non date-effective rows. You need to know the basics of date-effectivity like what is an effective date and how it impacts search, create and update operations on date-effective records. Here, we're going to discuss different date-effective update modes available in ADF 11g and their behavior.

All the date-effective update modes are defined as constants in oracle.job.Row class as any kind of effective dated operation is performed on a row itself. Before going into details, let us take an example to explain the concepts. For example, we'll take the same example of 'Job' date-effective object and explain different date-effective update modes available. Sample application having the required code to perform the DE update operations can be downloaded from here.

The following DE update modes available in Jdeveloper 11g:

1. 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.

For example,  if the Job with JobId 100000020529001 has the following effective-dated row

From the above data, you can see that the details of the job 'Apps Engineer' from 01-Jan-1990 to 31-Dec-4712. And, for example if we want to increase the salary ranges of this job to MinSal=25000 and MaxSal=35000 from 01-Jan-2011 onwards. To achieve this, we need to end date the existing row at 31-Dec-2010 and create a new record for the job from 01-Jan-2011 to 31-12-4712. For such cases, we use this EFFDT_UPDATE_CHANGE_INSERT_MODE.

After updating the above job record with effective date 01-Jan-2011, the resultant rows will be as below:

Again, if we want to update the MedicalCheckRequired attribute to 'Y' effective 01-Jan-1950 (till 31-Dec-2010), we need to update again with effective date 01-Jan-1950.

 After updating, the resultant rows will be:






2. EFFDT_UPDATE_CORRECTION: When an effective dated row is updated in "correction" mode, the effective start date and effective end date is left unchanged. We generally use this correction mode to correct the existing data of a single date-effective record (Correction mode won't create a new row, it'll just correct/modify the existing date-effective record data).

For example, let us take the same above 3 date-effective job records and if I want to correct the JobLevel of the Job record [100000020529001,01-Jan-1900,31-Dec-1949] to 1 from existing value 2, we need to correct the record in EFFDT_UPDATE_CORRECTION mode. For correction, we can use any date between EffectiveStartDate and EffectiveEndDate as the effective date.

 After correction, the result will be as follows:






3. 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.

For example, let us continue with the same above 3 rows and I want to update the JobName to 'Apps Specialist' from 'Apps Engineer' effective 01-Jan-2015, we can use EFFDT_UPDATE_MODE.

After updating in UPDATE mode, the resultant rows will be:

Here, one thing that we need to keep is that EFFDT_UPDATE_MODE works only for the last date-effective row. The reason is that this mode will always create the modified new record till EOT(31-12-4712) starting from the given effective date. i.e., if we try to update the row [100000020529001,01-Jan-1900,31-Dec-1949] with effective date 01-Jan-1925 in UPDATE_MODE mode, it'll try to create the new record [100000020529001,01-Jan-1920,31-Dec-4712] which will result in overlap with the existing record [100000020529001,01-Jan-1950,31-Dec-2010] and throws exception saying 'the date effective operation will result in gaps or overlaps'.

4. 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.

For example, let us continue with the above 4 date-effective rows, and if we want to move the EffectiveStartDate of the Job row [100000020529001,01-Jan-2015,31-Dec-4712] to 01-Jan-2013. i.e., we want to make the 'Apps Specialist' job title applicable from 01-Jan-2013 onwards instead of 01-Jan-2015. And, you can also set/change the other attributes for the resultant updated row [100000020529001,01-Jan-2013,31-Dec-4712] (for e.g., I'm setting the JobLevel from 2 to 3).

After updating the above rows with effective date 01-Jan-2013 in EFFDT_UPDATE_OVERRIDE_MODE will result in the following rows:

From the above table, you can observe that EFFDT_UPDATE_OVERRIDE_MODE didn't create any new rows. It just moved the existing ESD to the passed effective date.

5. EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE: Updating in "new earliest change" mode is supported only in Multiple Changes Per Day (MCPD). MCPD entities are the entities that support multiple date-effective updates on a single day. We're not covering this mode in this post as it would be out of scope of this post.

Here is the method that I wrote to accomplish all of these DE update operations with different DE update modes (download the sample application for to find the method references used in the below code).

    public void updateJobRow(Long jobId, Date effectiveDate, String jobName,
                             String status, Long minSal, Long maxSal,
                             String medCheckReq, Integer jobLevel) {
        if (effectiveDate == null) {
            throw new JboException("Effective Date cannot be null");
        }
        if (effectiveDate.compareTo(DateUtils.convertTosqlDate(MIN_START_DATE)) <=
            0 ||
            effectiveDate.compareTo(DateUtils.convertTosqlDate(MAX_END_DATE)) >=
            0) {
            throw new JboException("UpdateDate cannot be before SOT and cannot be later than EOT");
        }
        //getting the Job row effective as of the given effectiveDate
        Row jobRow = getRowAsOfDate(jobId, effectiveDate);
        if (jobRow != null) {
            try {
                //Set the required date-effective mode. Here I'm setting it to EFFDT_UPDATE_CHANGE_INSERT_MODE
                jobRow.setEffectiveDateMode(Row.EFFDT_UPDATE_CHANGE_INSERT_MODE);
                //Setting the passed attributes to the updated row
                if (jobName != null && !"".equals(jobName))
                    jobRow.setAttribute("JobName", jobName);
                if (status != null && !"".equals(status))
                    jobRow.setAttribute("Status", status);
                if (minSal != null && !"".equals(minSal))
                    jobRow.setAttribute("MinSal", minSal);
                if (maxSal != null && !"".equals(maxSal))
                    jobRow.setAttribute("MaxSal", maxSal);
                if (jobLevel != null && !"".equals(jobLevel))
                    jobRow.setAttribute("JobLevel", jobLevel);
                if (medCheckReq != null && !"".equals(medCheckReq))
                    jobRow.setAttribute("MedCheckReq", medCheckReq);
            } catch (Exception e) {
                e.printStackTrace();
            }
            this.getDBTransaction().commit();
        }
    }

In the above method, we're actually getting the Job row effective as of the passed effective date(this will make that effective-dated row as current row) and then performing date-effective update operations basing on that row.

Instructions to run the sample application:
1. Create the required tables for illustrating date-effective operations executing the sql script downloading from here.
2. Unzip the sample application and, run the DemoAM. Input JobId, effective date and other attributes you want to update and click 'Execute'.
3. Change the date-effective update mode in method 'updateJobRow' each time you want to change the update mode(right now it's hard coded to EFFDT_UPDATE_CHANGE_INSERT_MODE), rebuild and continue. You can find the screen shots above how to provide input in AM Tester.
4. Query the DB to find the updated rows.

SELECT Job_ID,
  TO_CHAR(effective_start_date,'dd-Mon-yyyy') AS ESD,
  TO_CHAR(effective_end_date,'dd-Mon-yyyy')   AS EED,
  JOB_NAME,
  MIN_SAL,
  MAX_SAL,
  STATUS,
  JOB_LEVEL,
  MED_CHECK_REQ AS MED_CHECK
FROM de_jobs
WHERE job_id=100000020529001
ORDER BY effective_start_date;

Enjoy!!!

ADF UI - Implementing Date Effective Search with Example

After learning how to create date-effective objects (i.e., Creating Date-Effective EO, Creating Date-Effective Associations and VOs), now we'll see how to implement date-effective search.

Sample Use Case:
Please go through my previous post to see the sample example use cases of performing date-effective operations on 'Job' object. So, here the requirement is to search for a job effective as of the given date. Sample application illustrating this example can be downloaded from here. Before running the example, you need to create the required tables in DB. The sql script for these table can be downloaded from here.

Implementation Steps:
1. Create the date-effective EO (JobEO) and date-effective VO (JobVO). Marking the JobVO will create a new transient attribute called SysEffectiveDate in the VO attributes.

2. Create a view criteria 'JobSearch' and add the required attributes as query criteria items on which you want to perform the search. As a best practice, use bind variables for all the view criteria items(attributes).

3. In addition to those attributes, add SysEffectiveDate in the query attributes and bind it to the bind variable SysEffectiveDateBindVar of type Date(if this bind variable is not already exist, create a new bind variable with same name and associate it to SysEffectiveDate). Here, the bind variable name that binds SysEffectiveDate should be SysEffectiveDateBindVar as this is the bind variable name generated at runtime by ADF for SysEffectiveDate. Otherwise, it'll throw run time exception.

4. Now, you're done with model part of defining view criteria with SysEffectiveDate. Now, implement search and search results in a jsff with this view criteria.

5. To test the functionality, create multiple job records with different date-effective updates and try to search for a required job records specifying the Effective Date in the search criteria. You'll get the job records which match the query criteria as of the given effective date. Here are the sample screen shots.

6. If the effective date (SysEffectiveDate) is not provided, it'll return the rows effective as of today (i.e.,the system date on which search is made). Screen shot below.

Enjoy!!!

ADF Model: Creating Date Effective Association and Date Effective VO

To learn the basics of date-effectivity in ADF, please go through my post Learning basics of Date Effectivity in ADF. To learn how to create date-effective EO, please go through my post Creating Date Effective EO.

Creating Date Effective Association:
To have the basic idea of association between EOs, please go through my post ADF Model: Creating Entity Association. But, by default the association created is not date-effective. But, if you're creating association between two EOs in which at least on of them is effective-dated, then you should mark the association as 'Effective Dated'. To make the association date-effective,

1. Open Association -> Goto 'Relationship' tab -> Behavior -> Check 'Effective Dated Association' checkbox.

Marking the association 'Effective Dated Association' will take effective date into consideration while searching, inserting and updating the records.

Creating Date Effective VO:
Creating date effective VO is same as creating normal VO. In addition, we need to

1. Mark the VO as date-effective by setting EffectiveDated='true' for the VO. You can find this property in 'General' tab property inspector.

Specifying the above property for the VO will generate a new transient attribute called SysEffectiveDate in the VO.

2.Optional: Change the data type of SysEffectiveDate attribute to 'java.sql.Date' from 'oracle.jbo.domain.Number'. We'll often find it easier with native Java sql data types instead of using Oracle's jbo datatypes. It is recommended to use java native sql type(java.sql.Date) for all date type attributes in the EO.

Marking the VO effective dated, will support date-effective updates for a single record.

Learning basics of Date Effectivity in ADF

This post explains the basics of date-effectivity which include mainly the basic date-effective operations like Date 'Effective Search', 'Date Effective Create', 'Date Effective Update' and 'Date Effective Delete'. But, it'll be easier to explain the concepts with examples.

So, here for example, I'm taking the simple example of HR admin, who can search/create/update/correct jobs. Let us assume, each job has a name, code and other attributes like job level, if the job requires medical checkup required, minimum salary, maximum salary, etc. What makes the job is date effective is that the attributes of job may change over the time, but still we need to be able to search the jobs based on the attributes that were applicable previously or that will be applicable in future.

For instance, let us take a job with Name 'Java Associate' which was created on 01-01-2000. When the job was created, the minimum salary for the job (minSal) was 10000 and maximum salary (maxSal) was 15000. But, on 01-01-2005, the job was revised and the management decided to move their technology to ADF and changed the job name to 'ADF Associate'.On 01-01-2010, the same job was revised and salary ranges were updated to minSal 25000 and maxSal 40000. Again, the management decided to change all Associate jobs to Developer Jobs from 01-01-2012 onwards (i.e., the job name will be changed from 'ADF Associate' to 'ADF Developer') etc.,

Here, the requirement is that if the user knows the name of the job applicable in 2000, he should be able to search the job as of that date. Similarly, if the user wants to search as of the current date or future dates, he should be able to search as well. That means we shouldn't simply update the existing values of the attributes as it will update the existing values and the previous values would be lost! We need to keep different versions of the attributes for the same job. So, how will it be possible as to have the multiple records with same JobId? Here comes EffectiveStartDate and EffectiveEndDate. Actually, we need to have a composite key based on JobId, EffectiveStartDate and EffectiveEndDate(i.e., the combination of these 3 attributes should be unique).

So, the data in the above requirement can be shown in table as follows. Changed attributes for each date-effective update are highlighted in yellow.

If you see the above table, you'll notice that for any change in the Job attribute, we have new row with the corresponding EffectiveStartDate and EffectiveEndDate and there are no gaps in between. i.e., for any given date, only one record is applicable among multiple date-effective rows of a single Job record.

Now we'll see what date effectivity means in ADF for different operations on the records.

Date Effective Search:

For example, if we want to find the jobs whose name starts with 'ADF%' as of date 01-01-2006. Or, we want to find out the jobs which will be applicable after 01-01-2012. Or, we want to find the minSal and maxSal for a given job as of 01-01-2007. Here, the results would change changing the Effective Dates as each job would have different values for the same attribute for different effective dates. Date-effective search will make all these possible. Date-effective search is explained with an example here.

Date Effective Create:

For example, we want to create a job that will be applicable in future say from 01-01-2013. Or, I want to create record which should be applicable from the past say 12-12-1900. Or, I want to create a new job which should be effective from today. Date-effective create makes all of these possible.

Date Effective Update:

For example, we want to insert a new date-effective record for the job to represent a change in one of the attributes of the job. Say, I want to update the maxSal of the job with jobId 1001 to 20000 from 01-01-2008. This will insert a new record for the same job with EffectiveStartDate 01-01-2008 with maxSal 20000. Different date-effective update modes and the corresponding behavior has been explained here.

Date Effective Correct:

For example, I want to correct or change one or more of the attributes of one of the date-effective rows of a single job record. Date Effective Correct operation just corrects the existing data. It won't insert any new date-effective rows. You can find the example of DE correction mode here.

Date Effective Delete:

For example, we want to delete a single date-effective record out of multiple date-effective records of a job. For instance, we want to delete the job record [1001, 01-01-2005, 31-12-2009]. If the record is non-date effective, then deleting the row means deleting the entire record as there won't be multiple rows. But, in case of date-effective objects, if you want to delete entire record, you need to delete all date-effective rows of that record if any.

Now, you understand the basics of date-effectivity in ADF. If you need any clarification, please leave a comment.

ADF Model: Creating Date Effective EO

Date-effectivity is an excellent feature available in Jdeveloper 11g. To learn the basics of date-effectivity in ADF, please go through my post Learning basic of Date Effectivity in ADF. With Jdev 11g, we can create date-effective objects and do date-effective operations using simple API calls. Before going into all those details, the first requirement would be creating Date-Effective EO.

The first requirement to create a DE-EO based on a table, the table should have two date columns to represent Effective Start Date(ESD) and Effecitve End Date(EED) of the record and these columns should be marked as primary keys along with id column. In other words, we need to define a composite key based on the id column and ESD and EED columns.

Creating Date Effective EO is same as creating normal EO. In addition, we need to do the following steps to make it date-effective.
1. Mark the EO as Date Effective by specifying the attribute Effective Date Type = 'EffectiveDated'. You can find this property in 'General' tab property inspector.

This will generate a new transient attributte called 'SysEffectiveDate' in the EO. Please find the screenshot below:

2. We need to specify which columns represent effective-date columns by checking the Check 'Effective Date' check box and selecting 'Start' and 'End' radio buttons for the effective-date columns which represent Effective Start Date and Effective End Date. Find the screen shots below:

3. Optional: Change the data type of SysEffectiveDate attribute to 'java.sql.Date' from 'oracle.jbo.domain.Number'. We'll often find it easier with native Java sql data types instead of using Oracle's jbo datatypes. It is recommended to use java native sql type(java.sql.Date) for all date type attributes in the EO.

That's it. Now, your EO is date-effective and supports date-effective operations on it's rows.