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 View Link

A View Link is created between two VOs to link them via foreign key. Please follow the below steps (screen shots) to create a View Link.

1. Right click any package and select 'New View Link..'

2. Give package and name for the view link. Click 'Next'.

3. Select source and destination VOs and the select the attribute based on which you need to link the VOs. If there is already association defined between the base EOs of the selected VOs, you can select the source and destination associations and click on 'Add'. The source and destination attributes will be shown at the bottom. Now, click 'Next'.

4. Select the check boxes to generate accessors in each of the VOs. Click 'Next'.

5. Click 'Next' until the 'Summary' stop, check the summary and click 'Finish'.

ADF Model: Creating and Running Application Module (AM)

Creating application module is easy. Follow the below steps (screen shots) to create and run application module.

1. Right click any package and select 'New Application Module' -> 'Create Application Module' windown opens.

2. Give package, name for AM and click 'Next'.

  

3. In the 'Data Model' stop, shuffle the VOs you want to add to AM to right side so that they'll be added to the application module. Click 'Next'.

4. In 'Application Modules' stop, you can add other application modules to the AM. i.e., you can nest the application modules. Now, click 'Next'.

5. In 'Java' stop, you can select to generate the application module implementation class (AMImpl).

6. Click 'Next' to see the summary and 'Finish'.

7. Now, you can see the created AM under the specified package. To run the AM, right click the AM and click 'Run'/'Debug'.

8. Now, the application module runs and opens the window where you can see the VO data. You can add, modify, remove VO rows/data and commit.

ADF Model: Creating View Object (VO)

View Object (VO) is build on one or more entity objects. Please follow the below steps to create VO.


1. In Model project, right click on any package, select 'New View Object..'

2. Now, 'Create View Object' wizard appears -> In the 'Name' stop, Give Name for the VO, select/give correct package for the VO. The VO Name is generally follows the convention <objectname>VO. For e.g., EmpVO

3. Click 'Next' -> In the 'Entity Objects' section, select the EOs on which you want create the VO and shuffle to the right side.

4. Click 'Next' -> In the 'Attributes' section, select the EO attributes you want to include in the VO and shuffle them to the right side.

5. Click 'Next' -> In the 'Attribute Settings' section, you can give settings for each of the VO attributes.

6. Click 'Next' -> The 'Query' section displays the VO query along with the generated Where clause.

7. You can add 'Order By' clause to the query in 'Order By' filed. You can 'Edit..' and select the 'Order By' attribute'

8. Click 'Next' -> In the 'Bind Variables' section, you can add one or more bind variables. Bind variables are used to form parameterized queries.

9. Click 'Next' -> In 'Java' section, you can opt to select ViewObject class and VO row class etc.

10. Click 'Next' -> In the 'Application Module' section, you can check 'Application Module' check box and create application module to add this VO.

11. Click 'Next' to see the summary of the VO and then click 'Finish'.

12. Now, you'll find the newly created Association in model project under the specified package.

Creating DB Connection in Jdeveloper

The basic requirement in Jdeveloper is to connect to database and execute queries in Jdeveloper. We'll see how to do it in below steps.

1. Check if 'Database Navigator' palette is open in Jdeveloper. If now, cick on 'View' and select 'Database Navigator'.

2. In the 'Database Navigator', Right click 'IDE Connections' and select 'New Connection'.

3. In the 'Create Database Connection' window, give Name for the connection, select the type of DB you want to connect.

4. Give the connection details for the DB. You can give database connection URL by checking 'Enter Custom JDBC URL'. Test your connection and click 'OK'.

5. Now, you can see the newly created DB under 'IDE Connections'. Select it and Right Click and select 'Connect'.

6. It'll open SQL editor window where you can type and execute your queries (It is same as Oracle SQL Developer).

7. Alternatively, you can also connect to DB by clicking on the 'SQL' icon and selecting the appropriate DB connection.

ADF Model: Creating Entity Association

An Entity Association defines the relationship between two entities or EOs. Follow the below steps to create an entity association.

1. In Model project, right click on any package, select 'New Association'.

2. Now, 'Create Association' wizard appears -> In the 'Name' stop, Give Name for the Association, select/give package for the Association. The Association Name is generally follows the convention <SourceEO>To<DestinationEO>. E.g., DeptEOToEmpEO.

3. Click 'Next' -> In the 'Entity Objects' section, select the Source and Destination EOs and the attributes in Source and Destination EOs on which you want define the association.

4. Click 'Next' -> In the 'Assocation Properties' section, specify the properties if applies. For e.g., if the assocation is composite, check the box 'Composite Association'.

5. Click 'Next' -> In the 'Edit Assocation Query' section, verify if the where clause for the association generated is right. You can modify the query where clause if needed.

6. Click 'Next' -> 'Summary' displays the summary of the created association -> Click 'Finish'.

7. Now, you'll find the newly created Association in model project under the specified package.

8. Once, you create the association between EmpEO and DeptEO, Jdeveloper will add the association accessors in each of the EOs.

ADF Model: Creating Entity Object (EO)

ADF Entity Object (EO) is created based on database table. To create an entity object, follow the below steps:

1. Right click on your model project and select 'New'. Alternatively, you can select 'New Entity Object' right clicking the package name.

2. 'New Gallery' window opens -> Search for Entity Object -> Select 'Entity Object' and click 'OK'.

3. If the database connection is not yet defined, it'll open 'Initialize Business Components' wizard -> Click on '+' and it'll open 'Create Database Connection' window -> Give your database settings -> 'Test Connection' -> Click 'OK' when the connection is success.

4. It'll select the created DB to create all business components.

5. Now, 'Create Entity Object' wizard appears -> In the 'Name' stop, Give Name for the EO, select/give package for the EO. And, you can browse for existing schema objects by clicking on 'Browse..' and then search and select the DB table.

6. Click 'Next' -> The 'Attributes' section displays the all columns in the table selected and they'll become the EO attributes.

7. Click 'Next' -> In the 'Attribute Settings', you can give properties for your EO attributes. At least one column should be primary key (I selected EmpNo). Otherwise, it'll ask to have RowID as the Primary Key.

8. Click 'Next' -> The 'Java' section is for generating Java Impl files for EO. Click 'Next' -> You can optionally generate default View Object (VO) and Application Module (AM) for the EO.

9. Click 'Next' -> 'Summary' displays the summary of EO -> Click 'Finish'.

10. Now, you'll find the newly created EO in model project under the specified package.