This discussion is archived
14 Replies Latest reply: Nov 5, 2012 2:20 AM by Saro RSS

General Index Error for every Load

Saro Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points