9 Replies Latest reply: May 29, 2013 2:09 PM by amrassel RSS

    Tables in Informatica Workflow not qualified

    amrassel
      Using EBS ver 12.1.3 as my source for the Financials subject area loads, the loads fail with table or view does not exist.
      In the PowerCenter Workflow Monitor log file, the schemas are not specified on the tables in the SQL statements being issued against the source.
      Did I miss updating a parameter? My datasource_num_id is set to 1000.
        • 1. Re: Tables in Informatica Workflow not qualified
          Veeresh Rayan
          Not clear on this statement of yours

          In the PowerCenter Workflow Monitor log file, the schemas are not specified on the tables in the SQL statements being issued against the source.

          Could please explain and also can you paste the session log for the task?..

          Regards,
          Veeresh Rayan
          • 2. Re: Tables in Informatica Workflow not qualified
            amrassel
            In the SQL being issued for the load, the table names are not qualified with the schema. For example, GL_LEDGER_CONFIG_DETAILS needs to be GL.GL_LEDGER_CONFIG_DETAILS If I take the following SQL, qualify the tables, and run it in PL/SQL Developer, it runs fine.

            Function Name : Execute
            SQL Stmt : SELECT
            LCD.OBJECT_ID LEGER_ID,
            LCD2.OBJECT_ID LEGAL_ENTITY_ID,
            LEB.FLEX_VALUE_SET_ID,
            LEB.FLEX_SEGMENT_VALUE
            FROM
            GL_LEDGER_CONFIG_DETAILS LCD,
            GL_LEDGER_CONFIG_DETAILS LCD2,
            GL_LEGAL_ENTITIES_BSVS LEB
            WHERE
            LCD.OBJECT_TYPE_CODE IN ('PRIMARY', 'SECONDARY') AND
            LCD.SETUP_STEP_CODE = 'NONE' AND
            LCD2.OBJECT_TYPE_CODE = 'LEGAL_ENTITY' AND
            LCD.CONFIGURATION_ID = LCD2.CONFIGURATION_ID AND
            LCD2.OBJECT_ID = LEB.LEGAL_ENTITY_ID


            From the log file:
            2013-05-02 10:19:55 : INFO : (2883 | READER_1_1_1) : (IS | biapps_is_qa) : oasqz1.i.midamerican.com : RR_4029 : SQ Instance [mplt_BC_ORA_InternalOrganizationDimension_BalanceSegmentValue_LegalEntity.SQ] User specified SQL Query [SELECT
            LCD.OBJECT_ID LEGER_ID,
            LCD2.OBJECT_ID LEGAL_ENTITY_ID,
            LEB.FLEX_VALUE_SET_ID,
            LEB.FLEX_SEGMENT_VALUE
            FROM
            GL_LEDGER_CONFIG_DETAILS LCD,
            GL_LEDGER_CONFIG_DETAILS LCD2,
            GL_LEGAL_ENTITIES_BSVS LEB
            WHERE
            LCD.OBJECT_TYPE_CODE IN ('PRIMARY', 'SECONDARY') AND
            LCD.SETUP_STEP_CODE = 'NONE' AND
            LCD2.OBJECT_TYPE_CODE = 'LEGAL_ENTITY' AND
            LCD.CONFIGURATION_ID = LCD2.CONFIGURATION_ID AND
            LCD2.OBJECT_ID = LEB.LEGAL_ENTITY_ID]
            2013-05-02 10:19:55 : INFO : (2883 | READER_1_1_1) : (IS | biapps_is_qa) : oasqz1.i.midamerican.com : RR_4049 : RR_4049 SQL Query issued to database : (Thu May 02 10:19:55 2013)
            2013-05-02 10:19:55 : ERROR : (2883 | READER_1_1_1) : (IS | biapps_is_qa) : oasqz1.i.midamerican.com : RR_4035 : SQL Error [
            ORA-00942: table or view does not exist

            Database driver error...
            • 3. Re: Tables in Informatica Workflow not qualified
              Lombo
              I´ve also had that problem... OBI doc says that the user registered in DAC should have at least read privilege... We could not make this work... Had to use the table owner in DAC so you dont need the qualification.
              Antonio
              • 4. Re: Tables in Informatica Workflow not qualified
                amrassel
                At this point, I'm thinking of requesting private synonyms be created for my APP user in the source database.
                • 5. Re: Tables in Informatica Workflow not qualified
                  Veeresh Rayan
                  You should have read priviledge for all the tables which APPS owns.You can not keep identifying one table each time and ask your DBA's to give u permission.

                  I would suggest you to use APPS schema and password instead of creating the synonyms or creating dummy user and granting privileges.

                  If it is not possible for your company to give you the password,then provide them the doc saying where and all they need to enter password(One in DAC and another in Informatica workflow manager).

                  Mark as correct or helpful if it helps,

                  Regards,
                  Veeresh Rayan
                  • 6. Re: Tables in Informatica Workflow not qualified
                    Ahsan Shah
                    Yes, you need to get the synonyms and read grants to all the ERP tables. As an alternative you can do an alter session prefix to add the schema prefix but its best to get the DBA To grant the user access to read those synonyms or get the APPS schema and password directly.
                    • 7. Re: Tables in Informatica Workflow not qualified
                      amrassel
                      I am not able to use the APPS user for the ETL process. So, we have created an application user in the source system and would like to create private synonms for each of the source tables.

                      In the OBIA Release Notes for 7.9.6.3 and 7.9.6.4, section 1.3.54 APPS User Providing Generic Access Violating SOX Compliance With Oracle EBS, there are steps to get a list of source tables for the ETL process so we can create the synonyms necessary. However, the tables in the output file do not have an owner listed with them.

                      Is it possible to modify the workaround steps in order to get the table owner in the output file instead of just the table names?

                      Has anyone else experienced this issue? Or is it standard to just use the APPS user?

                      Edited by: amrassel on May 29, 2013 11:37 AM
                      • 8. Re: Tables in Informatica Workflow not qualified
                        Voltaire
                        The best and easy way to handle this is create a new user like apps_read and use a logon trigger to set the current session to apps.

                        create or replace trigger after_logon_trg
                        after LOGON on SCHEMA
                        begin
                        dbms_application_info.set_module(USER, 'Initialized');
                        execute immediate ‘ALTER SESSION SET current_schema=apps’;
                        end;

                        Let me know if this worked.

                        Regards,
                        Jay
                        • 9. Re: Tables in Informatica Workflow not qualified
                          amrassel
                          Thank you! Worked perfectly!!