Let's consider that we have a project on which all the target tables have the following two technical fields:

• BUSINESS_DATE: contains the business reference date
• LOAD_DATE: contains the date when the record was inserted in the database

We want to create a specific IKM for this project which checks if any of these two fields are explicitly mapped and, if not, assigns the default values as follows:

• BUSINESS_DATE <- TO_DATE('#GLOBAL.REF_DATE', 'YYYY-MM-DD')
• LOAD_DATE <- SYSDATE

Creating a custom IKM 

As recommended I start by creating a new IKM having as base the existing IKM Insert Oracle knowledge module. We do this by creating a new integration IKM and specifying IKM Insert Oracle km as being the Base KM for the newly created IKM:

Next, in the newly created KM, we need to edit the KM step where the INSERT SELECT statement is generated. This step is named "Insert new rows".

We will not change the Command Text section of this step. Instead, we change directly the AST used by ODI to generate the INSERT SELECT statement. To do this we edit the Groovy Task-local section and enter the following code:

The above code is checking to see if the BUSINESS_DATE and LOAD_DATE are already present in AST. This is done by looking to the list of columns of the INSERT object and to the list of expressions of the QUERY object. If any of these columns are mapped then they will be found. If they are not found we add them to AST as shown above.

The IKM can be downloaded here