How to write sql query in ODI

There are two possible ways to use sql query as a source.
1)Using knowledge module as SQL AS SOURCE
2)Using the property as OBI_SOURCE_SQL_OVERRIDE in the knowledge module IKM BIAPPS Oracle Control Append(this KM will be available in Biaaps11.1.1.8.1,etc...).

                                      Steps to Implement this scenario using BIApps Knowledge Module
                                  --------------------------------------------------------------------------
Step 1:Create temp interface
Step2:Create a temp table with the required columns in the interface like below.

Step3:Click on flow tab-->select knowledge module as IKM BIAPPS Oracle Control Append or IKM BIAPPS Oracle Incremental Update.
Step4:Select the property as OBI_SOURCE_SQL_OVERRIDE in the knowledge module IKM BIAPPS Oracle Control Append or IKM BIAPPS Oracle Incremental Update and then write the query like below.

 Note:The number of ports in the temp table and the number of columns in the select list should be match.
Note: No need to drag any source table.Here sql query as source.
Step5:Click on save
Step6:Create a new interface and drag the temp interface in the workspace of new interface.
Step7:Drag the target datastore from the models.Click yes to auto mapping.

Step8:Click on source table then enable the property as use temporary interface as Derived table(Sub-select).

Step9:Click on flow tab then select knowledge module as IKM BIAPPS Oracle Control Append or IKM BIAPPS Oracle Incremental Update.
Step10:Click on save.
Step11:Execute the interface.

ODI-1228:Task IS_INCREMENTAL(Variable) fails on the target connection (BIAPPS 11.1.1.8.1)

Cause:Step name of IS_INCREMENTAL in package is IS_INCREMENTAL.Step name of IS_INCREMENTAL in package should be with the name Refresh IS_INCREMENTAL.

Solution:Step name of IS_INCREMENTAL in package changed from IS_INCREMENTAL to Refresh IS_INCREMENTAL.

ORA-01850: hour must be between 0 and 23 in ODI

Cause:In Date column Hour part (value) is getting 240000 from source table.

Solution:Hour value need to be corrected in Source table.To resolve this issue,we have corrected the hour value from 24 to 235959 in source table.

How to use select statements to columns of target table in ODI (BIAPPS 11.1.1.8.1)

We can use select statements to columns of target tables.Below screen shot showing how to use select statements for columns of target tables.

But that select statement should not return more than one row.This select statement should be in braces.

Changed on Date logic in ODI(Biapps 11.1.1.8.1)

Changed on Date logic in ODI (Biapps11.1.1.8.1) if source system is JDE
----------------------------------------------------------------------
RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED',
JDE_DATE_CONV(TO_INTEGER(SQ_F4140.PJUPMJ),
TO_INTEGER(SQ_F4140.PJTDAY)),
SQ_F4140.CDC$_SRC_LAST_UPDATE_DATE)

The above function will work based on the IS_SDS_DEPLOYED variable.
If IS_SDS_DEPLOYED=N then JDE_DATE_CONV(TO_INTEGER(SQ_F4140.PJUPMJ),TO_INTEGER(SQ_F4140.PJTDAY))--JDE_DATE_CONV function will convert the JDE formate to the reguler formate like MM/DD/YYYY
ELSE
F4140.CDC$_SRC_LAST_UPDATE_DATE--(Golden gate date)

Implementation OF JDE_DATE_CONV FUNCTION
---------------------------------------------------------

CASE
WHEN $(DATE_PART)=0 OR $(DATE_PART) IS NULL THEN NULL
ELSE
DATEADD(day,($(DATE_PART)-(1000*(ROUND(($(DATE_PART)/1000),0)))),DATEADD(year, ROUND(($(DATE_PART)/1000),0), '1899-12-31 00:00:00'))
END

ORA-02287 :Sequence number not allowed here in ODI

Cause:Update option is enabled or checked for row_wid  in interface.Due to this row_wid also be including in the update statement.

Solution: Update option  need to be unchecked.So that row_wid wil be excluded in the update statement.