3 Replies Latest reply: Dec 12, 2011 4:31 PM by 785129 RSS

    How I can set ORG_ID context (for Oracle Apps Views) in ODI designer tool?

      Hi All,

      I want to get data from apps views (like OE_ORDER_HEADER_V) into our data warehouse detination table using ‘Oracle Data Integrator’ tool.
      The issue is that when I reverse Oracle Table or Synonym directly, everything works fine i.e. after 'Interface' execution, I can see how many rows imported into destination table from this source Oracle table/synonym.
      But when I use Apps view as a source then 'Interface' executes fine with ‘no error’ but NO rows imports to the destination table.

      I believe, the only difference is that I am not able to set Org_Id before executing ODI interface so it is not picking the data.

      Please let me know the steps/instructions to set org_id context on apps view then reverse in ODI Designer tool.

      Please note that I can set org_id using SQLPlus successfully as given below (and see data using the same Apps view). What to do in ODI designer tool to achieve similar data:

      fnd_global.apps_initialize(0, 21623, 660);

      -- connect using apps/apps at Vision SQLPlus
      -- User_Id -> 0 (<-- SYSADMIN )
      -- Responsibility_Id-> 21623 (<-- Order Management Super User )
      -- Application_id -> 660 (<-- ONT )

      -- select count(*) from oe_order_headers_v where rownum < = 100

      I would appreciate your quick help in this.
      Thanks in advance.