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!!!