JDev Version: 11.1.2.
In this blog, I have documented the results of the tests I did for Effective Dated Entities update behavior. For the setup, and project details, please refer to the earlier blog: 'Understanding Effective Dated Entities Behavior - Creation'.
The following modes in the oracle.jbo.Row interface are available to update Effective Dated rows:
I am using the following method to update the rows of MultiChangesSingleDayVO:
public void updateMultiChangesSingleDayEffDatedVORow( String text, Date startDate, Date endDate, Date effectiveDate, int mode){
if (effectiveDate != null) {
ApplicationModuleImpl rootAM = this.getRootApplicationModule();
rootAM.setProperty(EFF_DT_PROPERTY_STR, effectiveDate);
}
ViewObjectImpl vo = this.getMultiChangesSingeDayVO1();
Row row = null;
row = (vo.findByKey(new Key(new Object[]{ 1, null, null, "Y"}), 1))[0];
if(mode==1)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_MODE );
else if(mode==2)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CHANGE_INSERT_MODE );
else if(mode==3)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CORRECTION );
else if(mode==4)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE );
else if(mode==5){
row.setEffectiveDateMode(Row.EFFDT_UPDATE_OVERRIDE_MODE );
}
else if(mode==6)
row.setEffectiveDateMode(Row.EFFDT_NONE_MODE );
row.setAttribute("Text", text);
if(startDate!=null)
row.setAttribute("StartDate", startDate);
if(endDate!=null)
row.setAttribute("EndDate", endDate);
this.getDBTransaction().commit();
}
As you can see, I am using the following mode value to identify which mode to use:
1 = Row.EFFDT_UPDATE_MODE
2 = Row.EFFDT_UPDATE_CHANGE_INSERT_MODE
3 = Row.EFFDT_UPDATE_CORRECTION
4 = Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE
5 = Row.EFFDT_UPDATE_OVERRIDE_MODE
6 = Row.EFFDT_NONE_MODE
Testing Effective Dated Table Update:
Documenting some of results I got when I tested the Effective Dated entities update using all the above flags:
Started experimenting by updates on single row. Here is the data used for all the below cases:
Test Cases on the above data:
1.
Parameter Passed:
text = MODE_1
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode =1
Other parameters are null. These parameters are passed as shown below:
After giving the above specified input to updateMultiChangesSingleDayEffDatedVORow method, got the follout output and table contents:
Output: Success
Table Content After Method Call:
We can see that when EFFDT_UPDATE_MODE mode is used, the single row is split into two rows, where the initial row is end dated on the effective date passed as parameter.
Recalling the definition of Row.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. This is exactly what has happened.
In addition, in the JDev log, I saw that the following query is fired for the findByKey operation:
SELECT MultiChangesSingleDayEO.ID, MultiChangesSingleDayEO.TEXT, MultiChangesSingleDayEO.START_DATE, MultiChangesSingleDayEO.END_DATE, MultiChangesSingleDayEO.SEQUENCE, MultiChangesSingleDayEO.SEQUENCE_FLAG
FROM SCOTT.MULTI_CHANGES_SINGLE_DAY_TABLE MultiChangesSingleDayEO
WHERE (MultiChangesSingleDayEO.ID = :fbkKy__0 AND MultiChangesSingleDayEO.SEQUENCE_FLAG = :fbkKy__3)
AND (:SysEffectiveDateBindVar BETWEEN MultiChangesSingleDayEO.START_DATE AND MultiChangesSingleDayEO.END_DATE) AND (MultiChangesSingleDayEO.SEQUENCE_FLAG = 'Y')
2.
Parameter Passed:
text = MODE_2
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 2
Output:
(oracle.jbo.JboException) JBO-27132: Current operation cannot be performed in UPDATE_CHANGE_INSERT mode because no future rows exist. key=oracle.jbo.Key[1 2011-09-11 1 Y ]
Recalling the definition of 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.
As there is no next row, this operation returns an error. Table contents are unchanged because of this.
3.
Parameter Passed:
text =MODE_3
effectiveDate =2011-09-11(yyyy-mm-dd)
mode =3
Output: Success
Table Content After Method Call:
Recalling the definition of Row.EFFDT_UPDATE_CORRECTION: When an effective dated row is updated in "correction" mode, the effective start date and effective end date is left unchanged.
So, none of the effective dated attibutes are touched in this mode.
4.
Parameter Passed:
text = MODE_4
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 4
Output:
(oracle.jbo.JboException) JBO-27133: Current operation cannot be performed in UPDATE_CHANGE_INSERT mode because no previous rows exist. key=oracle.jbo.Key[1 2011-09-11 1 Y ]
Recalling the definition of Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE: Updating in "new earliest change" mode is supported only in Multiple Changes Per Day.
This definition is somewhat not stating what it does, but it actually captures the scenarios somewhat opposite of Row.EFFDT_UPDATE_CHANGE_INSERT_MODE.
5.
Parameter Passed:
text = MODE_5
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 5
Output:
(oracle.jbo.JboException) JBO-27130: Current mode UPDATE_OVERRIDE is not supported in Multiple Changes Per Day.
Recalling the definition of Row.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.
Discussed later in the blog.
6.
Parameter Passed:
text = MODE_6
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 6
Output: Success
Table Content After Method Call:
Recalling the definition of Row.EFFDT_NONE_MODE: Default state of the effective date mode on the row.
Just the Text attribute passed as parameter is updated.
So out of 6 test cases, 3 failed. Let's move to testing the Effective Dated feature with multiple records.
Now consider the following records used for all the below cases:
Table Content:
These records are created by first inserting a record using effective date 01-JAN-2001, and then breaking it using Row.EFFDT_UPDATE_MODE using 2 separate effective dates: 01-JAN-2005, and 01-JAN-2008. So this covers our updates for multiple dates scenario as well.
Now we will try to modify the second record for all the different flags:
Test Cases:
1.
Parameter Passed:
text = MODE_1
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 1
Output:
(oracle.jbo.JboException) JBO-27127: The effective date operation for Entity MultiChangesSingleDayEO will introduce gaps or overlaps. key=oracle.jbo.Key[1 2005-01-01 1 Y ]
Recalling the definition of Row.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.
As this mode inserts a new row with changed values, I guess this mode only works with last (corner) row.
Basically, it can't insert a row in between when already a future row exists.
2.
Parameter Passed:
text = MODE_2
effectivedate = 2005-01-01(yyyy-mm-dd)
mode = 2
Output: Success
Table Content After Method Call:
Recalling the definition of 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.
Perfect.
3.
Parameter Passed:
text = MODE_3
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 3
Output: Success
Table Content After Method Call:
Recalling the definition of Row.EFFDT_UPDATE_CORRECTION: When an effective dated row is updated in "correction" mode, the effective start date and effective end date is left unchanged.
So none of the effective dated attibutes are touched in this mode.
4.
Parameter Passed:
text = MODE_4
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 4
Output: Success
Table Content After Method Call:
Recalling the definition of Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE: Updating in "new earliest change" mode is supported only in Multiple Changes Per Day.
This definition is somewhat not stating what it does, but it actually captures the scenarios somewhat opposite of Row.EFFDT_UPDATE_CHANGE_INSERT_MODE.
5.
Parameter Passed:
text = MODE_5
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 5
Output:
(oracle.jbo.JboException) JBO-27130: Current mode UPDATE_OVERRIDE is not supported in Multiple Changes Per Day.
Recalling the definition of Row.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.
Discussed later in the blog.
6.
Parameter Passed:
text =MODE_6
effectiveDate =2005-01-01(yyyy-mm-dd)
mode = 6
Output: Success
Table Content After Method Call:
Recalling the definition of Row.EFFDT_NONE_MODE: Default state of the effective date mode on the row.
Just the Text attribute passed as parameter is updated.
Apart from Row.EFFDT_UPDATE_OVERRIDE_MODE, all other modes are clear till here. For Row.EFFDT_UPDATE_OVERRIDE_MODE mode, posted this on the forum post:
https://forums.oracle.com/forums/thread.jspa?threadID=2281030&tstart=0
And John Stegemen pointed out that it works only for those entities where multiple changes on a single day is not allowed.
Lets try this on the entity (SingleChangeSingleDayVO) not configured for multiple changes in a single day. Using the following method to update SingleChangeSingleDayVO row:
public void updateSingleChangeSingleDayEffDatedVORow( String text, Date startDate, Date effectiveDate, int mode){
if (effectiveDate != null) {
ApplicationModuleImpl rootAM = this.getRootApplicationModule();
rootAM.setProperty(EFF_DT_PROPERTY_STR, effectiveDate);
}
ViewObjectImpl vo = this.getSingleChangeSingleDayVO1();
Row row = null;
row = (vo.findByKey(new Key(new Object[]{ 1, startDate, null }), 1))[0];
if(mode==1)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_MODE );
else if(mode==2)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CHANGE_INSERT_MODE );
else if(mode==3)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CORRECTION );
else if(mode==4)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE );
else if(mode==5){
row.setEffectiveDateMode(Row.EFFDT_UPDATE_OVERRIDE_MODE );
}
else if(mode==6)
row.setEffectiveDateMode(Row.EFFDT_NONE_MODE );
row.setAttribute("Text", text);
this.getDBTransaction().commit();
}
Initial Table Content:
Test Cases:
1.
Parameter Passed:
text = MODE_5
effectiveDate = 2011-01-01(yyyy-mm-dd)
startDate = 2011-01-01(yyyy-mm-dd)
mode =5
Output:
(oracle.jbo.JboException) JBO-27128: The effective date update operation for Entity SingleChangeSingleDayEO failed because an existing row has the effective start date that matches the effective date. key=oracle.jbo.Key[1 2011-01-01 4712-12-31 ]
So, it fails again when StartDate is equal to EffectiveDate. This same test case works with Row.EFFDT_UPDATE_MODE so dont care if it fails with Row.EFFDT_UPDATE_OVERRIDE_MODE.
Now, lets try with effective date later than start date.
2.
Parameter Passed:
text = MODE_5
effectiveDate = 2012-01-01(yyyy-mm-dd)
startDate = 2011-01-01(yyyy-mm-dd)
mode = 5
Output: Success
Table Content After Method Call:
So, MODE_5 works only for entities not configured for multiple changes in a single day.
In this blog, I have documented the results of the tests I did for Effective Dated Entities update behavior. For the setup, and project details, please refer to the earlier blog: 'Understanding Effective Dated Entities Behavior - Creation'.
The following modes in the oracle.jbo.Row interface are available to update Effective Dated rows:
- Row.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.
- 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.
- Row.EFFDT_UPDATE_CORRECTION: When an effective dated row is updated in "correction" mode, the effective start date and effective end date is left unchanged.
- Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE: Updating in "new earliest change" mode is supported only in Multiple Changes Per Day.
- Row.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.
- Row.EFFDT_NONE_MODE: Default state of the effective date mode on the row.
I am using the following method to update the rows of MultiChangesSingleDayVO:
public void updateMultiChangesSingleDayEffDatedVORow( String text, Date startDate, Date endDate, Date effectiveDate, int mode){
if (effectiveDate != null) {
ApplicationModuleImpl rootAM = this.getRootApplicationModule();
rootAM.setProperty(EFF_DT_PROPERTY_STR, effectiveDate);
}
ViewObjectImpl vo = this.getMultiChangesSingeDayVO1();
Row row = null;
row = (vo.findByKey(new Key(new Object[]{ 1, null, null, "Y"}), 1))[0];
if(mode==1)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_MODE );
else if(mode==2)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CHANGE_INSERT_MODE );
else if(mode==3)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CORRECTION );
else if(mode==4)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE );
else if(mode==5){
row.setEffectiveDateMode(Row.EFFDT_UPDATE_OVERRIDE_MODE );
}
else if(mode==6)
row.setEffectiveDateMode(Row.EFFDT_NONE_MODE );
row.setAttribute("Text", text);
if(startDate!=null)
row.setAttribute("StartDate", startDate);
if(endDate!=null)
row.setAttribute("EndDate", endDate);
this.getDBTransaction().commit();
}
As you can see, I am using the following mode value to identify which mode to use:
1 = Row.EFFDT_UPDATE_MODE
2 = Row.EFFDT_UPDATE_CHANGE_INSERT_MODE
3 = Row.EFFDT_UPDATE_CORRECTION
4 = Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE
5 = Row.EFFDT_UPDATE_OVERRIDE_MODE
6 = Row.EFFDT_NONE_MODE
Testing Effective Dated Table Update:
Documenting some of results I got when I tested the Effective Dated entities update using all the above flags:
Started experimenting by updates on single row. Here is the data used for all the below cases:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | First | 11-SEP-11 | 31-DEC-12 | 1 | Y |
Test Cases on the above data:
1.
Parameter Passed:
text = MODE_1
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode =1
Other parameters are null. These parameters are passed as shown below:
After giving the above specified input to updateMultiChangesSingleDayEffDatedVORow method, got the follout output and table contents:
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | MODE_1 | 11-SEP-11 | 31-DEC-12 | 2 | Y |
| 1 | First | 11-SEP-11 | 11-SEP-11 | 1 | N |
We can see that when EFFDT_UPDATE_MODE mode is used, the single row is split into two rows, where the initial row is end dated on the effective date passed as parameter.
Recalling the definition of Row.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. This is exactly what has happened.
In addition, in the JDev log, I saw that the following query is fired for the findByKey operation:
SELECT MultiChangesSingleDayEO.ID, MultiChangesSingleDayEO.TEXT, MultiChangesSingleDayEO.START_DATE, MultiChangesSingleDayEO.END_DATE, MultiChangesSingleDayEO.SEQUENCE, MultiChangesSingleDayEO.SEQUENCE_FLAG
FROM SCOTT.MULTI_CHANGES_SINGLE_DAY_TABLE MultiChangesSingleDayEO
WHERE (MultiChangesSingleDayEO.ID = :fbkKy__0 AND MultiChangesSingleDayEO.SEQUENCE_FLAG = :fbkKy__3)
AND (:SysEffectiveDateBindVar BETWEEN MultiChangesSingleDayEO.START_DATE AND MultiChangesSingleDayEO.END_DATE) AND (MultiChangesSingleDayEO.SEQUENCE_FLAG = 'Y')
2.
Parameter Passed:
text = MODE_2
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 2
Output:
(oracle.jbo.JboException) JBO-27132: Current operation cannot be performed in UPDATE_CHANGE_INSERT mode because no future rows exist. key=oracle.jbo.Key[1 2011-09-11 1 Y ]
Recalling the definition of 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.
As there is no next row, this operation returns an error. Table contents are unchanged because of this.
3.
Parameter Passed:
text =MODE_3
effectiveDate =2011-09-11(yyyy-mm-dd)
mode =3
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | MODE_3 | 11-SEP-11 | 31-DEC-12 | 1 | Y |
Recalling the definition of Row.EFFDT_UPDATE_CORRECTION: When an effective dated row is updated in "correction" mode, the effective start date and effective end date is left unchanged.
So, none of the effective dated attibutes are touched in this mode.
4.
Parameter Passed:
text = MODE_4
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 4
Output:
(oracle.jbo.JboException) JBO-27133: Current operation cannot be performed in UPDATE_CHANGE_INSERT mode because no previous rows exist. key=oracle.jbo.Key[1 2011-09-11 1 Y ]
Recalling the definition of Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE: Updating in "new earliest change" mode is supported only in Multiple Changes Per Day.
This definition is somewhat not stating what it does, but it actually captures the scenarios somewhat opposite of Row.EFFDT_UPDATE_CHANGE_INSERT_MODE.
5.
Parameter Passed:
text = MODE_5
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 5
Output:
(oracle.jbo.JboException) JBO-27130: Current mode UPDATE_OVERRIDE is not supported in Multiple Changes Per Day.
Recalling the definition of Row.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.
Discussed later in the blog.
6.
Parameter Passed:
text = MODE_6
effectiveDate = 2011-09-11(yyyy-mm-dd)
mode = 6
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | MODE_6 | 11-SEP-11 | 31-DEC-12 | 1 | Y |
Recalling the definition of Row.EFFDT_NONE_MODE: Default state of the effective date mode on the row.
Just the Text attribute passed as parameter is updated.
So out of 6 test cases, 3 failed. Let's move to testing the Effective Dated feature with multiple records.
Now consider the following records used for all the below cases:
Table Content:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | First | 01-JAN-01 | 31-DEC-04 | 1 | Y |
| 1 | Second | 01-JAN-05 | 31-DEC-07 | 1 | Y |
| 1 | Third | 01-JAN-08 | 31-DEC-12 | 1 | Y |
These records are created by first inserting a record using effective date 01-JAN-2001, and then breaking it using Row.EFFDT_UPDATE_MODE using 2 separate effective dates: 01-JAN-2005, and 01-JAN-2008. So this covers our updates for multiple dates scenario as well.
Now we will try to modify the second record for all the different flags:
Test Cases:
1.
Parameter Passed:
text = MODE_1
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 1
Output:
(oracle.jbo.JboException) JBO-27127: The effective date operation for Entity MultiChangesSingleDayEO will introduce gaps or overlaps. key=oracle.jbo.Key[1 2005-01-01 1 Y ]
Recalling the definition of Row.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.
As this mode inserts a new row with changed values, I guess this mode only works with last (corner) row.
Basically, it can't insert a row in between when already a future row exists.
2.
Parameter Passed:
text = MODE_2
effectivedate = 2005-01-01(yyyy-mm-dd)
mode = 2
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | First | 01-JAN-01 | 31-DEC-04 | 1 | Y |
| 1 | Second | 01-JAN-05 | 01-JAN-05 | 1 | N |
| 1 | Third | 01-JAN-08 | 31-DEC-12 | 1 | Y |
| 1 | MODE_2 | 01-JAN-05 | 31-DEC-07 | 2 | Y |
Recalling the definition of 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.
Perfect.
3.
Parameter Passed:
text = MODE_3
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 3
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | First | 01-JAN-01 | 31-DEC-04 | 1 | Y |
| 1 | MODE_3 | 01-JAN-05 | 31-DEC-07 | 1 | Y |
| 1 | Third | 01-JAN-08 | 31-DEC-12 | 1 | Y |
Recalling the definition of Row.EFFDT_UPDATE_CORRECTION: When an effective dated row is updated in "correction" mode, the effective start date and effective end date is left unchanged.
So none of the effective dated attibutes are touched in this mode.
4.
Parameter Passed:
text = MODE_4
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 4
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | First | 01-JAN-01 | 31-DEC-04 | 1 | Y |
| 1 | second | 01-JAN-05 | 31-DEC-07 | 2 | Y |
| 1 | Third | 01-JAN-08 | 31-DEC-12 | 1 | Y |
| 1 | MODE_4 | 01-JAN-05 | 01-JAN-05 | 1 | N |
Recalling the definition of Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE: Updating in "new earliest change" mode is supported only in Multiple Changes Per Day.
This definition is somewhat not stating what it does, but it actually captures the scenarios somewhat opposite of Row.EFFDT_UPDATE_CHANGE_INSERT_MODE.
5.
Parameter Passed:
text = MODE_5
effectiveDate = 2005-01-01(yyyy-mm-dd)
mode = 5
Output:
(oracle.jbo.JboException) JBO-27130: Current mode UPDATE_OVERRIDE is not supported in Multiple Changes Per Day.
Recalling the definition of Row.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.
Discussed later in the blog.
6.
Parameter Passed:
text =MODE_6
effectiveDate =2005-01-01(yyyy-mm-dd)
mode = 6
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE | SEQUENCE | SEQUENCE_FLAG |
|---|---|---|---|---|---|
| 1 | First | 01-JAN-01 | 31-DEC-04 | 1 | Y |
| 1 | MODE_6 | 01-JAN-05 | 31-DEC-07 | 1 | Y |
| 1 | Third | 01-JAN-08 | 31-DEC-12 | 1 | Y |
Recalling the definition of Row.EFFDT_NONE_MODE: Default state of the effective date mode on the row.
Just the Text attribute passed as parameter is updated.
Apart from Row.EFFDT_UPDATE_OVERRIDE_MODE, all other modes are clear till here. For Row.EFFDT_UPDATE_OVERRIDE_MODE mode, posted this on the forum post:
https://forums.oracle.com/forums/thread.jspa?threadID=2281030&tstart=0
And John Stegemen pointed out that it works only for those entities where multiple changes on a single day is not allowed.
Lets try this on the entity (SingleChangeSingleDayVO) not configured for multiple changes in a single day. Using the following method to update SingleChangeSingleDayVO row:
public void updateSingleChangeSingleDayEffDatedVORow( String text, Date startDate, Date effectiveDate, int mode){
if (effectiveDate != null) {
ApplicationModuleImpl rootAM = this.getRootApplicationModule();
rootAM.setProperty(EFF_DT_PROPERTY_STR, effectiveDate);
}
ViewObjectImpl vo = this.getSingleChangeSingleDayVO1();
Row row = null;
row = (vo.findByKey(new Key(new Object[]{ 1, startDate, null }), 1))[0];
if(mode==1)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_MODE );
else if(mode==2)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CHANGE_INSERT_MODE );
else if(mode==3)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_CORRECTION );
else if(mode==4)
row.setEffectiveDateMode(Row.EFFDT_UPDATE_NEW_EARLIEST_CHANGE_MODE );
else if(mode==5){
row.setEffectiveDateMode(Row.EFFDT_UPDATE_OVERRIDE_MODE );
}
else if(mode==6)
row.setEffectiveDateMode(Row.EFFDT_NONE_MODE );
row.setAttribute("Text", text);
this.getDBTransaction().commit();
}
Initial Table Content:
| ID | TEXT | START_DATE | END_DATE |
|---|---|---|---|
| 1 | First | 01-JAN-11 | 31-DEC-12 |
Test Cases:
1.
Parameter Passed:
text = MODE_5
effectiveDate = 2011-01-01(yyyy-mm-dd)
startDate = 2011-01-01(yyyy-mm-dd)
mode =5
Output:
(oracle.jbo.JboException) JBO-27128: The effective date update operation for Entity SingleChangeSingleDayEO failed because an existing row has the effective start date that matches the effective date. key=oracle.jbo.Key[1 2011-01-01 4712-12-31 ]
So, it fails again when StartDate is equal to EffectiveDate. This same test case works with Row.EFFDT_UPDATE_MODE so dont care if it fails with Row.EFFDT_UPDATE_OVERRIDE_MODE.
Now, lets try with effective date later than start date.
2.
Parameter Passed:
text = MODE_5
effectiveDate = 2012-01-01(yyyy-mm-dd)
startDate = 2011-01-01(yyyy-mm-dd)
mode = 5
Output: Success
Table Content After Method Call:
| ID | TEXT | START_DATE | END_DATE |
|---|---|---|---|
| 1 | MODE_5 | 01-JAN-12 | 31-DEC-12 |
| 1 | First | 01-JAN-11 | 31-DEC-11 |
So, MODE_5 works only for entities not configured for multiple changes in a single day.
No comments:
Post a Comment