14 Replies Latest reply: Nov 5, 2012 4:20 AM by Saro RSS

    General Index Error for every Load

    Saro
      Hi friends,

      I recently had two successful loads on HRMS and FINANCE modules through DAC.

      While loading i came through the regular failed task load that is
      SDE_ORA_PositionDimension
      TASK_GROUP_Extract_EmployeeDimension
      SDE_ORA_PersistedStage_WorkforceEvent_Assignment
      If i see the reason behind those errors means, it is like "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found".

      Eventhough from the error the reason is with creating index as it is identifying duplicate records for those columns.

      For time being i cleared those issues by changing the values of the indexed column @ the back end of the table.

      But for permanent solution i tried to search in Support
      CREATE UNIQUE INDEX W_POSITION_DS_U1 ON W_POSITION_DS Fails with ORA-01452 Duplicate Keys Found [ID >1270038.1]
      For this they asked to follow below steps like

      Modify the sql query for the mapplet, sessions like

      Modify the Source Qualifier SQL Query to switch one of the Effective Date comparisons from '<=' to '<':
      The SQL Query modification is required in the following:
      
      1.  Mapping: SDE_ORA_PositionDimension
      ---------Mapplet: mplt_BC_ORA_PositionDimension
      ---------Source Qualifier: Sq_BC_PositionDimension
      
      2.  Workflow:  SDE_ORA_PositionDimension
      ---------Session:  SDE_ORA_PositionDimension
      
      3.  Workflow:  SDE_ORA_PositionDimension_Full
      --------Session:  SDE_ORA_PositionDimension_Full
      But my problem is when i looked @ the source query of the qualifier mplt_BC_ORA_PositionDimension i can see the condition
      AND (ASGN.EFFECTIVE_START_DATE < PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL)
      What i meant is they asked to change the ASGN.EFFECTIVE_START_DATE from '<=' to '<', since my query condition already has < symbol instead of <= in my source qualifier which they asked to change.

      But in the 2nd and 3rd step i couldnt find where to change the condition in the session as adviced in support.

      Kindly point me to the right direction to change the query condition in the session level.

      Thanks

      Regards,
      Saro
        • 1. Re: General Index Error for every Load
          Srini VEERAVALLI
          This is know issue, since you are using bi apps 7963 they might have corrected and the doc id might be old version.

          I would suggest to check the duplicate records in the table,get the row_id and check in source using this mapplet query.
          then decide what need to put in the condition/query.
          • 2. Re: General Index Error for every Load
            Saro
            Hi Svee,

            Thanks for your reply.

            But i couldnt find any new document id related with that issue in OBIA 7.9.6.3 in metalink.

            As in the old metalink id they said like in per_all_poeple_f there will be a record with effective_Start_Date is greater than effective_end_Date. But when i tried to query my source per_all_poeple_F i couldnt find such records like effective_start_Date is greater than effective_end_Date.

            But i can find the records with effective_start_Date is greater than or equal to effective_end_Date. I got 31 records for it.
            to check the duplicate records in the table,get the row_id and check in source using this mapplet query.
            Im not sure about the table in source with a duplicate record in it. Since this is my mapplet query for the corresponding failed task
            SELECT /*+ USE_HASH(PER PT ASGN ASGNT JOB ORG) */ 
               ASGN.PERSON_ID AS EMP_ID, 
               ASGN.SUPERVISOR_ID AS SUPERVISOR_ID, 
               JOB.NAME AS POSITION_NAME, 
               ORG.NAME  AS DIVN_NAME, 
               PER.LAST_NAME AS EMP_LAST_NAME, 
               PER.FIRST_NAME AS EMP_FST_NAME, 
               (SELECT FU.USER_NAME FROM FND_USER FU WHERE  
               FU.EMPLOYEE_ID = ASGN.PERSON_ID   AND 
               (FU.END_DATE >= SYSDATE  OR FU.END_DATE IS  NULL) 
               AND FU.START_DATE = (SELECT MAX(FU1.START_DATE) FROM FND_USER FU1 WHERE FU1.EMPLOYEE_ID = FU.EMPLOYEE_ID) 
               AND (FU.END_DATE =  (SELECT MAX(FU2.END_DATE) FROM FND_USER FU2 WHERE FU2.EMPLOYEE_ID = FU.EMPLOYEE_ID) OR FU.END_DATE IS NULL)    AND ROWNUM=1 
               ) AS  EMP_LOGIN,
               ASGN.CREATED_BY AS CREATED_BY_ID, 
               ASGN.LAST_UPDATED_BY AS CHANGED_BY_ID, 
               ASGN.CREATION_DATE AS CREATED_ON_DT, 
               ASGN.LAST_UPDATE_DATE AS CHANGED_ON_DT, 
               PER.LAST_UPDATE_DATE AS AUX1_CHANGED_ON_DT, 
               JOB.LAST_UPDATE_DATE AS AUX2_CHANGED_ON_DT, 
               ORG.LAST_UPDATE_DATE AS AUX3_CHANGED_ON_DT, 
               ASGN.EFFECTIVE_START_DATE AS SRC_EFF_FROM_DT, 
               ASGN.EFFECTIVE_END_DATE AS SRC_EFF_TO_DT, 
               '0' AS X_CUSTOM 
             FROM 
              PER_ALL_PEOPLE_F PER, 
               PER_PERSON_TYPES  PT, 
               PER_ALL_ASSIGNMENTS_F ASGN, 
              PER_ASSIGNMENT_STATUS_TYPES ASGNT, 
               PER_JOBS_TL JOB, 
               HR_ALL_ORGANIZATION_UNITS_TL ORG 
               WHERE 
               (ASGN.CREATION_DATE >= TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:mi:ss') OR 
                ASGN.LAST_UPDATE_DATE >= TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:mi:ss') OR 
               PER.LAST_UPDATE_DATE >= TO_DATE('$$LAST_EXTRACT_DATE','MM/DD/YYYY HH24:mi:ss') )AND 
              PER.PERSON_TYPE_ID = PT.PERSON_TYPE_ID AND 
               PER.PERSON_ID=ASGN.PERSON_ID AND     ASGN.ASSIGNMENT_STATUS_TYPE_ID=ASGNT.ASSIGNMENT_STATUS_TYPE_ID AND 
               PER.EFFECTIVE_START_DATE < ASGN.EFFECTIVE_START_DATE AND
              (ASGN.EFFECTIVE_START_DATE < PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL) AND   
               ASGN.PRIMARY_FLAG='Y' AND 
               ASGN.JOB_ID=JOB.JOB_ID (+) AND 
               JOB.LANGUAGE (+) = '$$DFLT_LANG'  AND 
               ASGN.ORGANIZATION_ID=ORG.ORGANIZATION_ID  AND 
               ORG.LANGUAGE='$$DFLT_LANG' AND 
               ASGNT.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK') AND 
               PT.SYSTEM_PERSON_TYPE IN 
                  ('EMP', 'CWK', 'OTHER', 'EMP_APL', 'EX_EMP', 'EX_CWK', 'EX_EMP_APL', 'RETIREE', 'PRTN') AND 
               ASGN.ASSIGNMENT_TYPE IN ('C','E')
            But when i tried to execute the above query in source, it is not returning any rows.

            Pretty bit confusion in finding it.

            Regards,
            Saro
            • 3. Re: General Index Error for every Load
              Srini VEERAVALLI
              You have to look at per_all_assignments. You have to go wit more simplified query, get the duplicate Ida and pass that in where clause and investigate for each at once. It'll take some time. Also there might be few unwanted or junk data in the source system.

              The below query was one of my old project code for same issue..do changes as per your case
              "SELECT COUNT(1), ASGN.PERSON_ID
              FROM
              PER_ALL_PEOPLE_F PER,
              PER_PERSON_TYPES PT,
              PER_ALL_ASSIGNMENTS_F ASGN,
              PER_ASSIGNMENT_STATUS_TYPES ASGNT,
              PER_JOBS_TL JOB,
              HR_ALL_ORGANIZATION_UNITS_TL ORG
              WHERE
              PER.PERSON_TYPE_ID = PT.PERSON_TYPE_ID AND
              PER.PERSON_ID=ASGN.PERSON_ID AND
              ASGN.ASSIGNMENT_STATUS_TYPE_ID=ASGNT.ASSIGNMENT_STATUS_TYPE_ID AND
              PER.EFFECTIVE_START_DATE <= ASGN.EFFECTIVE_START_DATE AND
              (ASGN.EFFECTIVE_START_DATE <= PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL) AND
              ASGN.PRIMARY_FLAG='Y' AND
              ASGN.JOB_ID=JOB.JOB_ID ( + ) AND
              JOB.LANGUAGE ( + ) = 'US' AND
              ASGN.ORGANIZATION_ID=ORG.ORGANIZATION_ID AND
              ORG.LANGUAGE='US' AND
              ASGNT.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK') AND
              PT.SYSTEM_PERSON_TYPE IN
              ('EMP', 'CWK', 'OTHER', 'EMP_APL', 'EX_EMP', 'EX_CWK', 'EX_EMP_APL', 'RETIREE', 'PRTN') AND
              ASGN.ASSIGNMENT_TYPE IN ('C','E')
              GROUP BY ASGN.PERSON_ID HAVING COUNT(1)>1"

              Edited by: veeravalli on Nov 3, 2012 10:41 PM
              • 4. Re: General Index Error for every Load
                Saro
                Dear Svee,

                Thanks for your helpful query to track the issue. I tried to check with the same that u have given from my source query like below
                 SELECT COUNT(1), ASGN.PERSON_ID
                FROM
                PER_ALL_PEOPLE_F PER,
                PER_PERSON_TYPES PT,
                PER_ALL_ASSIGNMENTS_F ASGN,
                PER_ASSIGNMENT_STATUS_TYPES ASGNT,
                PER_JOBS_TL JOB,
                HR_ALL_ORGANIZATION_UNITS_TL ORG
                WHERE
                PER.PERSON_TYPE_ID = PT.PERSON_TYPE_ID AND
                PER.PERSON_ID=ASGN.PERSON_ID AND
                ASGN.ASSIGNMENT_STATUS_TYPE_ID=ASGNT.ASSIGNMENT_STATUS_TYPE_ID AND
                PER.EFFECTIVE_START_DATE <= ASGN.EFFECTIVE_START_DATE AND ASGN.EFFECTIVE_END_DATE > SYSDATE AND --PER.EFFECTIVE_END_DATE > SYSDATE AND
                (ASGN.EFFECTIVE_START_DATE <= PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL) AND
                ASGN.PRIMARY_FLAG='Y' AND
                ASGN.JOB_ID=JOB.JOB_ID ( + ) AND
                JOB.LANGUAGE ( + ) = 'US' AND
                ASGN.ORGANIZATION_ID=ORG.ORGANIZATION_ID AND
                ORG.LANGUAGE='US' AND
                ASGNT.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK') AND
                PT.SYSTEM_PERSON_TYPE IN
                ('EMP', 'CWK', 'OTHER', 'EMP_APL', 'EX_EMP', 'EX_CWK', 'EX_EMP_APL', 'RETIREE', 'PRTN') AND
                ASGN.ASSIGNMENT_TYPE IN ('C','E')
                GROUP BY ASGN.PERSON_ID 
                HAVING COUNT(1)>1
                I used an extra condition in where clause like "ASGN.EFFECTIVE_END_DATE > SYSDATE" once after added this condition and executed the query i found count of 2 records for the person_id '6272', so after that i added one more condition in it like "PER.EFFECTIVE_END_DATE > SYSDATE" like below
                 SELECT COUNT(1), ASGN.PERSON_ID
                FROM
                PER_ALL_PEOPLE_F PER,
                PER_PERSON_TYPES PT,
                PER_ALL_ASSIGNMENTS_F ASGN,
                PER_ASSIGNMENT_STATUS_TYPES ASGNT,
                PER_JOBS_TL JOB,
                HR_ALL_ORGANIZATION_UNITS_TL ORG
                WHERE
                PER.PERSON_TYPE_ID = PT.PERSON_TYPE_ID AND
                PER.PERSON_ID=ASGN.PERSON_ID AND
                ASGN.ASSIGNMENT_STATUS_TYPE_ID=ASGNT.ASSIGNMENT_STATUS_TYPE_ID AND
                PER.EFFECTIVE_START_DATE <= ASGN.EFFECTIVE_START_DATE AND ASGN.EFFECTIVE_END_DATE > SYSDATE AND PER.EFFECTIVE_END_DATE > SYSDATE AND
                (ASGN.EFFECTIVE_START_DATE <= PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL) AND
                ASGN.PRIMARY_FLAG='Y' AND
                ASGN.JOB_ID=JOB.JOB_ID ( + ) AND
                JOB.LANGUAGE ( + ) = 'US' AND
                ASGN.ORGANIZATION_ID=ORG.ORGANIZATION_ID AND
                ORG.LANGUAGE='US' AND
                ASGNT.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK') AND
                PT.SYSTEM_PERSON_TYPE IN
                ('EMP', 'CWK', 'OTHER', 'EMP_APL', 'EX_EMP', 'EX_CWK', 'EX_EMP_APL', 'RETIREE', 'PRTN') AND
                ASGN.ASSIGNMENT_TYPE IN ('C','E')
                GROUP BY ASGN.PERSON_ID 
                HAVING COUNT(1)>1
                Now the above query dint return any record with duplicated records, so i added those two extra condition to my source qualifier like
                ASGN.EFFECTIVE_END_DATE > SYSDATE AND 
                PER.EFFECTIVE_END_DATE > SYSDATE
                and i saved it in the workflow designer and after that if i restarted the EP in DAC means, then too SDE_ORA_Dimension is failing again in DAC.

                Other than in designer, whether i need to change anywhere else.

                Thanks

                Regards,
                Saro
                • 5. Re: General Index Error for every Load
                  Srini VEERAVALLI
                  Since you changed the SDE mapping you suppose to change both once and full load workflows and also make sure they are refreshed with mapping and valid.

                  Other than this its very hard to tell why it is failing.

                  Q?: why you are using sysdate in your query? You might have to check with ebs guys.
                  • 6. Re: General Index Error for every Load
                    Saro
                    Hi Svee,
                    to change both once and full load workflows and also make sure they are refreshed with mapping and valid.
                    I couldnt get you from the above, whether u meant to validate the workflows related to the failed task in the workflow manager.
                    why you are using sysdate in your query?
                    effective_end_date>sysdate

                    Since after adding the above condition restricting the effective_end_date according to sysdate, i can get a single value for the person_id '6272' who initially had two entry's for him in the base table(per_all_people_f).This is the restriction will apply normally as discussed with ebs guys in the hrms table.

                    What do you say svee.

                    Regards,
                    Saro
                    • 7. Re: General Index Error for every Load
                      Srini VEERAVALLI
                      For Sysdate thats fine since you checked with ebs guys.

                      Regarding task failure can you share dac status description or informatica log?

                      Since you changed the query in the SDE mapping you suppose to make sure the changes are affected in both incr and full workflows.
                      For FULL(workflow ending with) might have session level sq query override etc.
                      • 8. Re: General Index Error for every Load
                        Saro
                        Hi svee,

                        This is my DAC status description message for the failed task SDE_PositionDimension
                        2012-11-04 15:57:39.186 CREATE INDEX:W_POSITION_DS:W_POSITION_DS_U1 has started.
                        
                        
                        
                        ANOMALY INFO::: Error while executing : CREATE INDEX:W_POSITION_DS:W_POSITION_DS_U1
                        MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX 
                             W_POSITION_DS_U1 
                        ON 
                             W_POSITION_DS
                        (
                             INTEGRATION_ID Asc
                             ,DATASOURCE_NUM_ID Asc
                             ,SRC_EFF_FROM_DT Asc
                        ) 
                        NOLOGGING TABLESPACE OBIEE_INDEX
                         with error DataWarehouse:CREATE UNIQUE INDEX 
                             W_POSITION_DS_U1 
                        ON 
                             W_POSITION_DS
                        (
                             INTEGRATION_ID Asc
                             ,DATASOURCE_NUM_ID Asc
                             ,SRC_EFF_FROM_DT Asc
                        ) 
                        NOLOGGING TABLESPACE OBIEE_INDEX
                        ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
                        
                        EXCEPTION CLASS::: java.lang.Exception
                        
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:450)
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:307)
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:214)
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:586)
                        com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
                        java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                        java.util.concurrent.FutureTask.run(FutureTask.java:138)
                        java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
                        java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                        java.util.concurrent.FutureTask.run(FutureTask.java:138)
                        java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
                        java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
                        java.lang.Thread.run(Thread.java:619)
                        
                        ::: CAUSE :::
                        MESSAGE:::Error while execution : CREATE UNIQUE INDEX 
                             W_POSITION_DS_U1 
                        ON 
                             W_POSITION_DS
                        (
                             INTEGRATION_ID Asc
                             ,DATASOURCE_NUM_ID Asc
                             ,SRC_EFF_FROM_DT Asc
                        ) 
                        NOLOGGING TABLESPACE OBIEE_INDEX
                         with error DataWarehouse:CREATE UNIQUE INDEX 
                             W_POSITION_DS_U1 
                        ON 
                             W_POSITION_DS
                        (
                             INTEGRATION_ID Asc
                             ,DATASOURCE_NUM_ID Asc
                             ,SRC_EFF_FROM_DT Asc
                        ) 
                        NOLOGGING TABLESPACE OBIEE_INDEX
                        ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
                        
                        EXCEPTION CLASS::: java.lang.Exception
                        
                        com.siebel.analytics.etl.etltask.SQLTask.doExecute(SQLTask.java:129)
                        com.siebel.analytics.etl.etltask.CreateIndexTask.doExecute(CreateIndexTask.java:90)
                        com.siebel.analytics.etl.etltask.CreateIndexTaskWrapper.doExecute(CreateIndexTaskWrapper.java:46)
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:411)
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:307)
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:214)
                        com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:586)
                        com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
                        java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                        java.util.concurrent.FutureTask.run(FutureTask.java:138)
                        java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
                        java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
                        java.util.concurrent.FutureTask.run(FutureTask.java:138)
                        java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
                        java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
                        java.lang.Thread.run(Thread.java:619)
                        
                        ::: CAUSE :::
                        MESSAGE:::DataWarehouse:CREATE UNIQUE INDEX 
                             W_POSITION_DS_U1 
                        ON 
                             W_POSITION_DS
                        (
                             INTEGRATION_ID Asc
                             ,DATASOURCE_NUM_ID Asc
                             ,SRC_EFF_FROM_DT Asc
                        ) 
                        NOLOGGING TABLESPACE OBIEE_INDEX
                        ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
                        
                        EXCEPTION CLASS::: com.siebel.etl.database.IllegalSQLQueryException
                        
                        com.siebel.etl.database.DBUtils.executeUpdate(DBUtils.java:288)
                        com.siebel.etl.database.WeakDBUtils.executeUpdate(WeakDBUtils.java:357)
                        com.siebel.analytics.etl.etltask.SQLTask.doExecute(SQLTask.java:117)
                        com.siebel.analytics.etl.etltask.CreateIndexTask.doExecute(CreateIndexTask.java:90)
                        com.siebel.a
                        This is my informatica sesslog for that task
                        DIRECTOR> VAR_27028 Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].
                        DIRECTOR> VAR_27028 Use override value [ORA_R1213] for session parameter:[$DBConnection_OLTP].
                        DIRECTOR> VAR_27028 Use override value [ORA_R1213.DATAWAREHOUSE.SDE_ORAR1213_Adaptor.SDE_ORA_PositionDimension_Full.log] for session parameter:[$PMSessionLogFile].
                        DIRECTOR> VAR_27028 Use override value [1000] for mapping parameter:[$$DATASOURCE_NUM_ID].
                        DIRECTOR> VAR_27028 Use override value [US] for mapping parameter:[mplt_BC_ORA_PositionDimension.$$DFLT_LANG].
                        DIRECTOR> VAR_27028 Use override value [09/05/1995] for mapping parameter:[mplt_BC_ORA_PositionDimension.$$INITIAL_EXTRACT_DATE].
                        DIRECTOR> VAR_27028 Use override value [] for mapping parameter:[mplt_BC_ORA_PositionDimension.$$LAST_EXTRACT_DATE].
                        DIRECTOR> VAR_27028 Use override value [DEFAULT] for mapping parameter:[$$TENANT_ID].
                        DIRECTOR> TM_6014 Initializing session [SDE_ORA_PositionDimension_Full] at [Fri Nov 02 17:48:51 2012].
                        DIRECTOR> TM_6683 Repository Name: [Oracle_BI_DW_Base]
                        DIRECTOR> TM_6684 Server Name: [Oracle_BI_DW_Base_Integration_Services]
                        DIRECTOR> TM_6686 Folder: [SDE_ORAR1213_Adaptor]
                        DIRECTOR> TM_6685 Workflow: [SDE_ORA_PositionDimension_Full] Run Instance Name: [] Run Id: [1548]
                        DIRECTOR> TM_6101 Mapping name: SDE_ORA_PositionDimension [version CheckedOut].
                        DIRECTOR> TM_6963 Pre 85 Timestamp Compatibility is Enabled
                        DIRECTOR> TM_6964 Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
                        DIRECTOR> TM_6827 [C:\Informatica\9.1.0\server\infa_shared\Storage] will be used as storage directory for session [SDE_ORA_PositionDimension_Full].
                        DIRECTOR> CMN_1802 Session recovery cache initialization is complete.
                        DIRECTOR> TM_6708 Using configuration property [DisableDB2BulkMode,Yes]
                        DIRECTOR> TM_6708 Using configuration property [overrideMpltVarWithMapVar,Yes]
                        DIRECTOR> TM_6708 Using configuration property [SiebelUnicodeDB ,apps@test03 biapps@obidb1]
                        
                        DIRECTOR> TM_6703 Session [SDE_ORA_PositionDimension_Full] is run by 64-bit Integration Service  [node01_biserver], version [9.1.0 HotFix2], build [0903].
                        MANAGER> PETL_24058 Running Partition Group [1].
                        MANAGER> PETL_24000 Parallel Pipeline Engine initializing.
                        MANAGER> PETL_24001 Parallel Pipeline Engine running.
                        MANAGER> PETL_24003 Initializing session run.
                        MAPPING> CMN_1569 Server Mode: [UNICODE]
                        MAPPING> CMN_1570 Server Code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
                        MAPPING> TM_6151 The session sort order is [Binary].
                        MAPPING> TM_6185 Warning. Code page validation is disabled in this session.
                        MAPPING> TM_6156 Using low precision processing.
                        MAPPING> TM_6180 Deadlock retry logic will not be implemented.
                        MAPPING> TM_6187 Session target-based commit interval is [10000].
                        MAPPING> TM_6307 DTM error log disabled. 
                        MAPPING> TE_7022 TShmWriter: Initialized
                        MAPPING> DBG_21075 Connecting to database [OBIDB1], user [BIAPPS]
                        MAPPING> CMN_1716 Lookup [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS] uses database connection [Relational:DataWarehouse] in code page [MS Windows Latin 1 (ANSI), superset of Latin1]
                        MAPPING> TM_6007 DTM initialized successfully for session [SDE_ORA_PositionDimension_Full]
                        DIRECTOR> PETL_24033 All DTM Connection Info: [<NONE>].
                        MANAGER> PETL_24004 Starting pre-session tasks. : (Fri Nov 02 17:48:54 2012)
                        MANAGER> PETL_24027 Pre-session task completed successfully. : (Fri Nov 02 17:48:54 2012)
                        DIRECTOR> PETL_24006 Starting data movement.
                        MAPPING> TM_6660 Total Buffer Pool size is 32000000 bytes and Block size is 128000 bytes.
                        LKPDP_1> DBG_21097 Lookup Transformation [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS]: Default sql to create lookup cache: SELECT DATASOURCE_NUM_ID,INTEGRATION_ID FROM W_POSITION_DS ORDER BY DATASOURCE_NUM_ID,INTEGRATION_ID
                        
                        LKPDP_1> TE_7212 Increasing [Index Cache] size for transformation [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS] from [1048576] to [1050000].
                        READER_1_1_1> DBG_21438 Reader: Source is [test03], user [apps]
                        READER_1_1_1> BLKR_16051 Source database connection [ORA_R1213] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
                        READER_1_1_1> BLKR_16003 Initialization completed successfully.
                        WRITER_1_*_1> WRT_8146 Writer: Target is database [OBIDB1], user [BIAPPS], bulk mode [ON]
                        WRITER_1_*_1> WRT_8106 Warning! Bulk Mode session - recovery is not guaranteed.
                        WRITER_1_*_1> WRT_8221 Target database connection [DataWarehouse] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
                        WRITER_1_*_1> WRT_8124 Target Table W_POSITION_DS :SQL INSERT statement:
                        INSERT INTO W_POSITION_DS(EMP_ID,PAR_INTEGRATION_ID,POSITION_NAME,DIVN_NAME,EMP_LAST_NAME,EMP_FST_NAME,EMP_LOGIN,OWNER_ALLOC,TYPE_FLG,POSTN_TYPE_CD,BU_ID,CREATED_BY_ID,CHANGED_BY_ID,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,SRC_EFF_FROM_DT,SRC_EFF_TO_DT,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,TENANT_ID,X_CUSTOM)  VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
                        WRITER_1_*_1> WRT_8270 Target connection group #1 consists of target(s) [W_POSITION_DS]
                        WRITER_1_*_1> WRT_8003 Writer initialization complete.
                        READER_1_1_1> BLKR_16007 Reader run started.
                        READER_1_1_1> RR_4029 SQ Instance [mplt_BC_ORA_PositionDimension.Sq_BC_PositionDimension] User specified SQL Query [SELECT /*+ USE_HASH(PER PT ASGN ASGNT JOB ORG) */ 
                           ASGN.PERSON_ID AS EMP_ID, 
                           ASGN.SUPERVISOR_ID AS SUPERVISOR_ID, 
                           JOB.NAME AS POSITION_NAME, 
                           ORG.NAME  AS DIVN_NAME, 
                           PER.LAST_NAME AS EMP_LAST_NAME, 
                           PER.FIRST_NAME AS EMP_FST_NAME, 
                           (SELECT FU.USER_NAME FROM FND_USER FU WHERE  
                           FU.EMPLOYEE_ID = ASGN.PERSON_ID   AND 
                           (FU.END_DATE >= SYSDATE  OR FU.END_DATE IS  NULL) 
                           AND FU.START_DATE = (SELECT MAX(FU1.START_DATE) FROM FND_USER FU1 WHERE FU1.EMPLOYEE_ID = FU.EMPLOYEE_ID) 
                           AND (FU.END_DATE =  (SELECT MAX(FU2.END_DATE) FROM FND_USER FU2 WHERE FU2.EMPLOYEE_ID = FU.EMPLOYEE_ID) OR FU.END_DATE IS NULL)    AND ROWNUM=1 
                           ) AS  EMP_LOGIN,
                           ASGN.CREATED_BY AS CREATED_BY_ID, 
                           ASGN.LAST_UPDATED_BY AS CHANGED_BY_ID, 
                           ASGN.CREATION_DATE AS CREATED_ON_DT, 
                           ASGN.LAST_UPDATE_DATE AS CHANGED_ON_DT, 
                           PER.LAST_UPDATE_DATE AS AUX1_CHANGED_ON_DT, 
                           JOB.LAST_UPDATE_DATE AS AUX2_CHANGED_ON_DT, 
                           ORG.LAST_UPDATE_DATE AS AUX3_CHANGED_ON_DT, 
                           ASGN.EFFECTIVE_START_DATE AS SRC_EFF_FROM_DT, 
                           ASGN.EFFECTIVE_END_DATE AS SRC_EFF_TO_DT, 
                           '0' AS X_CUSTOM 
                         FROM 
                          PER_ALL_PEOPLE_F PER, 
                           PER_PERSON_TYPES  PT, 
                           PER_ALL_ASSIGNMENTS_F ASGN, 
                          PER_ASSIGNMENT_STATUS_TYPES ASGNT, 
                           PER_JOBS_TL JOB, 
                           HR_ALL_ORGANIZATION_UNITS_TL ORG 
                           WHERE 
                          PER.PERSON_TYPE_ID = PT.PERSON_TYPE_ID AND 
                           PER.PERSON_ID=ASGN.PERSON_ID AND 
                           ASGN.ASSIGNMENT_STATUS_TYPE_ID=ASGNT.ASSIGNMENT_STATUS_TYPE_ID AND 
                           PER.EFFECTIVE_START_DATE <= ASGN.EFFECTIVE_START_DATE AND
                          (ASGN.EFFECTIVE_START_DATE < PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL) AND   
                           ASGN.PRIMARY_FLAG='Y' AND 
                           ASGN.JOB_ID=JOB.JOB_ID (+) AND 
                           JOB.LANGUAGE (+) = 'US'  AND 
                           ASGN.ORGANIZATION_ID=ORG.ORGANIZATION_ID  AND 
                           ORG.LANGUAGE='US' AND 
                           ASGNT.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK')  AND 
                           PT.SYSTEM_PERSON_TYPE IN 
                              ('EMP', 'CWK', 'OTHER', 'EMP_APL', 'EX_EMP', 'EX_CWK', 'EX_EMP_APL', 'RETIREE', 'PRTN') AND 
                           ASGN.ASSIGNMENT_TYPE IN ('C','E')]
                        READER_1_1_1> RR_4049 SQL Query issued to database : (Fri Nov 02 17:48:55 2012)
                        WRITER_1_*_1> WRT_8005 Writer run started.
                        WRITER_1_*_1> WRT_8158 
                        
                        *****START LOAD SESSION*****
                        
                        Load Start Time: Fri Nov 02 17:48:54 2012
                        
                        Target tables:
                        
                             W_POSITION_DS
                        
                        
                        READER_1_1_1> RR_4050 First row returned from database to reader : (Fri Nov 02 17:48:56 2012)
                        LKPDP_1> TM_6660 Total Buffer Pool size is 609824 bytes and Block size is 65536 bytes.
                        LKPDP_1:READER_1_1> DBG_21438 Reader: Source is [OBIDB1], user [BIAPPS]
                        LKPDP_1:READER_1_1> BLKR_16051 Source database connection [DataWarehouse] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
                        LKPDP_1:READER_1_1> BLKR_16003 Initialization completed successfully.
                        LKPDP_1:READER_1_1> BLKR_16007 Reader run started.
                        LKPDP_1:READER_1_1> RR_4049 SQL Query issued to database : (Fri Nov 02 17:48:56 2012)
                        LKPDP_1:READER_1_1> BLKR_16008 Reader run completed.
                        LKPDP_1:TRANSF_1_1> DBG_21682 Lookup table row count : 0
                        LKPDP_1:TRANSF_1_1> DBG_21297 Lookup cache row count : 0
                        
                        LKPDP_1:TRANSF_1_1> DBG_21294 Lookup cache creation completed : (Fri Nov 02 17:48:56 2012)
                        LKPDP_1:TRANSF_1_1> CMN_1671 Created new cache files PMLKUP18413_262150_0_1548W64.[dat/idx] in directory C:\Informatica\9.1.0\server\infa_shared\Cache for Lookup [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS].
                        LKPDP_1:TRANSF_1_1> DBG_21641 MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS: Index cache size = [1050000], Data cache size = [2097152]
                        LKPDP_1:TRANSF_1_1> DBG_21216 Finished transformations for Source Qualifier [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS{{DSQ}}]. Total errors [0] 
                        TRANSF_1_1_1> CMN_1079 WARNING: Lookup table contains no data.
                        TRANSF_1_1_1> DBG_21524 Transform : Lkp_W_POSITION_DS
                        
                        TRANSF_1_1_1> DBG_21313 Lookup table : W_POSITION_DS
                        
                        TRANSF_1_1_1> DBG_21562 WARNING : Output rows from Lkp_W_POSITION_DS will be the default port value
                        
                        WRITER_1_*_1> WRT_8167 Start loading table [W_POSITION_DS] at: Fri Nov 02 17:48:56 2012
                        WRITER_1_*_1> WRT_8161 
                        TARGET BASED COMMIT POINT  Fri Nov 02 17:49:01 2012
                        ===================================================
                        
                        WRT_8036 Target: W_POSITION_DS (Instance Name: [W_POSITION_DS])
                        WRT_8038 Inserted rows - Requested: 10048      Applied: 10048      Rejected: 0          Affected: 10048     
                        
                        READER_1_1_1> BLKR_16019 Read [11375] rows, read [0] error rows for source table [PER_ALL_PEOPLE_F] instance name [mplt_BC_ORA_PositionDimension.PER_ALL_PEOPLE_F]
                        READER_1_1_1> BLKR_16008 Reader run completed.
                        TRANSF_1_1_1> DBG_21216 Finished transformations for Source Qualifier [mplt_BC_ORA_PositionDimension.Sq_BC_PositionDimension]. Total errors [0] 
                        WRITER_1_*_1> WRT_8168 End loading table [W_POSITION_DS] at: Fri Nov 02 17:49:01 2012
                        WRITER_1_*_1> WRT_8141 
                        Commit on end-of-data  Fri Nov 02 17:49:01 2012
                        ===================================================
                        
                        WRT_8036 Target: W_POSITION_DS (Instance Name: [W_POSITION_DS])
                        WRT_8038 Inserted rows - Requested: 11375      Applied: 11375      Rejected: 0          Affected: 11375     
                        
                        WRITER_1_*_1> WRT_8035 Load complete time: Fri Nov 02 17:49:01 2012
                        
                        LOAD SUMMARY
                        ============
                        
                        WRT_8036 Target: W_POSITION_DS (Instance Name: [W_POSITION_DS])
                        WRT_8038 Inserted rows - Requested: 11375      Applied: 11375      Rejected: 0          Affected: 11375     
                        
                        
                        WRITER_1_*_1> WRT_8043 *****END LOAD SESSION*****
                        WRITER_1_*_1> WRT_8006 Writer run completed.
                        MANAGER> PETL_24031 
                        ***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****
                        Thread [READER_1_1_1] created for [the read stage] of partition point [mplt_BC_ORA_PositionDimension.Sq_BC_PositionDimension] has completed.
                             Total Run Time = [6.921875] secs
                             Total Idle Time = [2.234375] secs
                             Busy Percentage = [67.720090]
                        Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [mplt_BC_ORA_PositionDimension.Sq_BC_PositionDimension] has completed.
                             Total Run Time = [6.125000] secs
                             Total Idle Time = [1.328125] secs
                             Busy Percentage = [78.316327]
                             Transformation-specific statistics for this thread were not accurate enough to report.
                        Thread [WRITER_1_*_1] created for [the write stage] of partition point [W_POSITION_DS] has completed.
                             Total Run Time = [5.843750] secs
                             Total Idle Time = [2.937500] secs
                             Busy Percentage = [49.732620]
                        
                        MAPPING> CMN_1793 The index cache size that would hold [0] rows in the lookup table for [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS], in memory, is [10000] bytes
                        MAPPING> CMN_1792 The data cache size that would hold [0] rows in the lookup table for [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS], in memory, is [16384] bytes
                        MANAGER> PETL_24005 Starting post-session tasks. : (Fri Nov 02 17:49:04 2012)
                        MANAGER> PETL_24029 Post-session task completed successfully. : (Fri Nov 02 17:49:04 2012)
                        MAPPING> TE_7216 Deleting cache files [PMLKUP18413_262150_0_1548W64] for transformation [MPLT_POSITION_INS_REJ.Lkp_W_POSITION_DS].
                        MAPPING> TM_6018 The session completed with [0] row transformation errors. 
                        MANAGER> PETL_24002 Parallel Pipeline Engine finished.
                        DIRECTOR> PETL_24012 Session run completed successfully.
                        DIRECTOR> TM_6022 
                        
                        SESSION LOAD SUMMARY
                        ================================================
                        
                        DIRECTOR> TM_6252 Source Load Summary.
                        DIRECTOR> CMN_1740 Table: [Sq_BC_PositionDimension] (Instance Name: [mplt_BC_ORA_PositionDimension.Sq_BC_PositionDimension])
                              Output Rows [11375], Affected Rows [11375], Applied Rows [11375], Rejected Rows [0]
                        DIRECTOR> TM_6253 Target Load Summary.
                        DIRECTOR> CMN_1740 Table: [W_POSITION_DS] (Instance Name: [W_POSITION_DS])
                              Output Rows [11375], Affected Rows [11375], Applied Rows [11375], Rejected Rows [0]
                        DIRECTOR> TM_6023 
                        ===================================================
                        
                        DIRECTOR> TM_6020 Session [SDE_ORA_PositionDimension_Full] completed at [Fri Nov 02 17:49:05 2012].
                        Regards,
                        Saro
                        • 9. Re: General Index Error for every Load
                          Srini VEERAVALLI
                          Not sure how you are running it and there might be old data in the table.
                          Make sure you run task in full mode, you can go for that by setting null for refresh dates for both source and target tables. If you are getting same error then get the duplicate records and start investigation... Might be sysdate in not permanent fix ;)
                          • 10. Re: General Index Error for every Load
                            Saro
                            Hi Svee,

                            From the informatica session log it seems that i couldn't see the sysdate condition that i added newly in the source qualifier query in my previous post, may be that might be the issue i guess. It seems that the query change that i applied is not taken by the informatica.

                            Although i saved it in the informatica designer for the particular mapping soon after adding the sysdate condition in where clause. Whether is my guess is correct.

                            This below is the query found in the log file without my sysdate condition which is not applied in the log file.
                            SELECT /*+ USE_HASH(PER PT ASGN ASGNT JOB ORG) */ 
                               ASGN.PERSON_ID AS EMP_ID, 
                               ASGN.SUPERVISOR_ID AS SUPERVISOR_ID, 
                               JOB.NAME AS POSITION_NAME, 
                               ORG.NAME  AS DIVN_NAME, 
                               PER.LAST_NAME AS EMP_LAST_NAME, 
                               PER.FIRST_NAME AS EMP_FST_NAME, 
                               (SELECT FU.USER_NAME FROM FND_USER FU WHERE  
                               FU.EMPLOYEE_ID = ASGN.PERSON_ID   AND 
                               (FU.END_DATE >= SYSDATE  OR FU.END_DATE IS  NULL) 
                               AND FU.START_DATE = (SELECT MAX(FU1.START_DATE) FROM FND_USER FU1 WHERE FU1.EMPLOYEE_ID = FU.EMPLOYEE_ID) 
                               AND (FU.END_DATE =  (SELECT MAX(FU2.END_DATE) FROM FND_USER FU2 WHERE FU2.EMPLOYEE_ID = FU.EMPLOYEE_ID) OR FU.END_DATE IS NULL)    AND ROWNUM=1 
                               ) AS  EMP_LOGIN,
                               ASGN.CREATED_BY AS CREATED_BY_ID, 
                               ASGN.LAST_UPDATED_BY AS CHANGED_BY_ID, 
                               ASGN.CREATION_DATE AS CREATED_ON_DT, 
                               ASGN.LAST_UPDATE_DATE AS CHANGED_ON_DT, 
                               PER.LAST_UPDATE_DATE AS AUX1_CHANGED_ON_DT, 
                               JOB.LAST_UPDATE_DATE AS AUX2_CHANGED_ON_DT, 
                               ORG.LAST_UPDATE_DATE AS AUX3_CHANGED_ON_DT, 
                               ASGN.EFFECTIVE_START_DATE AS SRC_EFF_FROM_DT, 
                               ASGN.EFFECTIVE_END_DATE AS SRC_EFF_TO_DT, 
                               '0' AS X_CUSTOM 
                             FROM 
                              PER_ALL_PEOPLE_F PER, 
                               PER_PERSON_TYPES  PT, 
                               PER_ALL_ASSIGNMENTS_F ASGN, 
                              PER_ASSIGNMENT_STATUS_TYPES ASGNT, 
                               PER_JOBS_TL JOB, 
                               HR_ALL_ORGANIZATION_UNITS_TL ORG 
                               WHERE 
                              PER.PERSON_TYPE_ID = PT.PERSON_TYPE_ID AND 
                               PER.PERSON_ID=ASGN.PERSON_ID AND 
                               ASGN.ASSIGNMENT_STATUS_TYPE_ID=ASGNT.ASSIGNMENT_STATUS_TYPE_ID AND 
                               PER.EFFECTIVE_START_DATE <= ASGN.EFFECTIVE_START_DATE AND
                              (ASGN.EFFECTIVE_START_DATE < PER.EFFECTIVE_END_DATE OR PER.EFFECTIVE_END_DATE IS NULL) AND   
                               ASGN.PRIMARY_FLAG='Y' AND 
                               ASGN.JOB_ID=JOB.JOB_ID (+) AND 
                               JOB.LANGUAGE (+) = 'US'  AND 
                               ASGN.ORGANIZATION_ID=ORG.ORGANIZATION_ID  AND 
                               ORG.LANGUAGE='US' AND 
                               ASGNT.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN','ACTIVE_CWK')  AND 
                               PT.SYSTEM_PERSON_TYPE IN 
                                  ('EMP', 'CWK', 'OTHER', 'EMP_APL', 'EX_EMP', 'EX_CWK', 'EX_EMP_APL', 'RETIREE', 'PRTN') AND 
                               ASGN.ASSIGNMENT_TYPE IN ('C','E')]
                            Also i couldnt get the below
                            you can go for that by setting null for refresh dates for both source and target tables
                            where i need to set the above condition. Can you be more elaborate where i need to do the change, as a last chance let me try this svee like you said. or else i need to delete the duplicate records in the source i guess
                            ;)

                            Thanks

                            REgards,
                            Saro
                            • 11. Re: General Index Error for every Load
                              Srini VEERAVALLI
                              To set refreshdate as NULL got to DAC->Settings->Physical connections->Select source lower pane select the table and set refresh date as null, and do the same for target tables.
                              Once you run incr load you can see these tables are running as FULL(load) mode.

                              Since workflow have custom sq sql override your changes are not affected.thats the reason I was asking you to check that.

                              Looks like your issue is fixed just run incr (as said above) or full load
                              • 12. Re: General Index Error for every Load
                                Ahsan Shah
                                First of all, dont use SYSDATE...as the timestamp you run the load is not the right effective date as stamped in EBS.

                                Second, why arent you checking the reason for this: "2 records for the person_id '6272"

                                Check the following in EBS:

                                1. Do you have 2 IDs for the same person in the same POSITION?
                                2. Do you have 2 overlapping timeframes for aposition.

                                Rather than changing the logic, try to find the root cause. Its possible this employeeID is improperly defined in EBS (2 positions overlapping or IDs assigned incorrectly).

                                If helpful, pls mark
                                • 13. Re: General Index Error for every Load
                                  Saro
                                  Hi Svee,

                                  I have also tried to set the refresh dates for both the source and target tables involved in that task to null. After that i restarted my EP but then too that task is failing.

                                  Not sure what the problem is. For time being, i dont know what to do so i tried to change the duplicated records in the target table and started the load in which that failed issue has passed now.

                                  But i couldnt get you from this
                                  Once you run incr load you can see these tables are running as FULL(load) mode.
                                  Im not sure what is the difference between the "Full Load" and "Incremental Load"

                                  Thanks

                                  Regards,
                                  Saro
                                  • 14. Re: General Index Error for every Load
                                    Saro
                                    Hi Shah,

                                    Thanks for your reply too.

                                    I couldnt get with the root cause of the issue still. As checked with ebs guys normally with apply sysdate restrictions to effective date it seems.

                                    Hence for the time being, i wipped that task and proceeded the task.

                                    Regards,
                                    Saro