1 Reply Latest reply on Dec 15, 2014 2:39 PM by TomBlakeley

    FDMEE Connection

    2744508

      I am trying to integrate metadata and data between Oracle EBS and EPM system using FDMEE. The source system connection shows successful, but when I open logs there is an error as follows.

       

      2014-12-14 13:37:42,224 INFO  [AIF]: FDMEE Process Start, Process ID: 48

      2014-12-14 13:37:42,224 INFO  [AIF]: FDMEE Logging Level: 5

      2014-12-14 13:37:42,225 INFO  [AIF]: FDMEE Log File: \\do-hyp01\myFDMEE\outbox\logs\TRA_48.log

      2014-12-14 13:37:42,226 INFO  [AIF]: User:admin

      2014-12-14 13:37:42,228 INFO  [AIF]: Location:NA (Partitionkey:null)

      2014-12-14 13:37:42,229 INFO  [AIF]: Period Name:NA (Period Key:null)

      2014-12-14 13:37:42,230 INFO  [AIF]: Category Name:NA (Category key:null)

      2014-12-14 13:37:42,232 INFO  [AIF]: Rule Name:NA (Rule ID:null)

      2014-12-14 13:37:45,924 INFO  [AIF]: Jython Version: 2.5.1 (Release_2_5_1:6813, Sep 26 2009, 13:47:54)

      [Oracle JRockit(R) (Oracle Corporation)]

      2014-12-14 13:37:45,926 INFO  [AIF]: Java Platform: java1.6.0_37

      2014-12-14 13:38:09,036 INFO  [AIF]: LKM EBS Extract Summary Nodes - Load Summary Dim Members Stage - START

      2014-12-14 13:38:09,057 DEBUG [AIF]:

              SELECT DISTINCT S.VALUE_SET_ID

              ,VALIDATION_TYPE

              ,VALUE_COLUMN_NAME

              ,MEANING_COLUMN_NAME

              ,APPLICATION_TABLE_NAME

              ,ADDITIONAL_WHERE_CLAUSE

              ,COMPILED_ATTRIBUTE_COLUMN_NAME

              ,ID_COLUMN_NAME

              ,max(INTERCOMPANY_TYPE_FLAG) INTERCOMPANY_TYPE_FLAG

              FROM AIF_COA_SEGMENTS S

              ,AIF_EBS_COA_SEGMENTS ES

              WHERE s.source_system_id = 7

              AND s.coa_line_id  = es.coa_line_id

              AND es.VALIDATION_TYPE in ('I','F')

              GROUP BY s.value_set_id

              ,VALIDATION_TYPE

              ,VALUE_COLUMN_NAME

              ,MEANING_COLUMN_NAME

              ,APPLICATION_TABLE_NAME

              ,ADDITIONAL_WHERE_CLAUSE

              ,COMPILED_ATTRIBUTE_COLUMN_NAME

              ,ID_COLUMN_NAME

             

      2014-12-14 13:38:09,062 DEBUG [AIF]:

              DELETE FROM AIF_DIM_MEMBERS_STG

              WHERE SUMMARY_FLAG = \?

              AND VALUE_SET_ID = \?

              AND SOURCE_SYSTEM_ID = \?

              AND EXISTS (

                SELECT 1

                FROM AIF_COA_SEGMENTS

                ,AIF_EBS_COA_SEGMENTS

                WHERE AIF_COA_SEGMENTS.source_system_id = AIF_DIM_MEMBERS_STG.source_system_id

                AND AIF_COA_SEGMENTS.value_set_id = AIF_DIM_MEMBERS_STG.value_set_id

                AND AIF_EBS_COA_SEGMENTS.coa_line_id = AIF_COA_SEGMENTS.coa_line_id

                AND AIF_EBS_COA_SEGMENTS.validation_type IN ('I','F')

              )

             

      2014-12-14 13:38:09,062 DEBUG [AIF]:

              DELETE FROM AIF_DIM_MEMBERS_STG_T

              WHERE LOADID = \?

              AND SOURCE_SYSTEM_ID = \?

              AND VALUE_SET_ID = \?

             

      2014-12-14 13:38:09,062 DEBUG [AIF]:

              INSERT INTO AIF_DIM_MEMBERS_STG (

                SOURCE_SYSTEM_ID

                ,VALUE_SET_ID

                ,VALUE_ID

                ,VALUE_NAME

                ,VALUE_DESCRIPTION

                ,SUMMARY_FLAG

                ,NODE_FLAG

                ,COMPILED_VALUE_ATTRIBUTES

                ,STAT_ACCOUNT_FLAG

                ,UNIT_OF_MEASURE

                ,LANGUAGE

                ,LAST_UPDATED_BY

                ,LAST_UPDATE_DATE

                ) VALUES (\?,\?,\?,\?,\?,'N','N',null,'N',null,\?,\?,current_timestamp)

               

      2014-12-14 13:38:09,086 DEBUG [AIF]:

                SELECT NAME FLEX_VALUE

                  ,CASE

                    WHEN SET_OF_BOOKS_ID IS NULL THEN 1

                    ELSE SET_OF_BOOKS_ID

                  END FLEX_VALUE_ID

                  ,CASE

                    WHEN DESCRIPTION IS NULL THEN NAME

                    ELSE DESCRIPTION

                  END FLEX_VALUE_MEANING

                ,'N' SUMMARY_FLAG

                FROM GL_SETS_OF_BOOKS

      2014-12-14 13:38:09,099 DEBUG [AIF]: 

              INSERT INTO AIF_DIM_MEMBERS_STG (

                SOURCE_SYSTEM_ID

                ,VALUE_SET_ID

                ,VALUE_ID

                ,VALUE_NAME

                ,VALUE_DESCRIPTION

                ,SUMMARY_FLAG

                ,NODE_FLAG

                ,STAT_ACCOUNT_FLAG

                ,ACCOUNT_TYPE

                ,BALANCE_FORWARD_FLAG

                ,COMPILED_VALUE_ATTRIBUTES

                ,UNIT_OF_MEASURE

                ,LANGUAGE

                ,LAST_UPDATED_BY

                ,LAST_UPDATE_DATE

              )

              SELECT SOURCE_SYSTEM_ID

              ,VALUE_SET_ID

              ,VALUE_ID

              ,VALUE_NAME

              ,VALUE_DESCRIPTION

              ,SUMMARY_FLAG

              ,NODE_FLAG

              ,STAT_ACCOUNT_FLAG

              ,ACCOUNT_TYPE

              ,BALANCE_FORWARD_FLAG

              ,COMPILED_VALUE_ATTRIBUTES

              ,UNIT_OF_MEASURE

              ,LANGUAGE

              ,LAST_UPDATED_BY

              ,current_timestamp

              FROM AIF_DIM_MEMBERS_STG_T

              WHERE source_system_id = \?

              AND value_set_id = \?

             

      2014-12-14 13:38:09,120 FATAL [AIF]: Error in LKM EBS Extract Summary Nodes - Load Summary Dim Members Stage - Independent Value Set - Source System Id: 7 - Value Set Id: 1015547

      Traceback (most recent call last):

        File "<string>", line 404, in loadSummaryDimMembersStage

      SQLIntegrityConstraintViolationException: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (FDMEE.AIF_DIM_MEMBERS_STG_U1) violated

       

       

      2014-12-14 13:38:09,186 INFO  [AIF]: LKM EBS Extract Summary Nodes - Load Summary Dim Members Stage - END

      2014-12-14 13:38:11,516 INFO  [AIF]: FDMEE Process End, Process ID: 48

       

       

      Can anyone help me with this?

        • 1. Re: FDMEE Connection
          TomBlakeley

          Do you have access to the SQL database?

           

          You should go look at that table, and figure out what the constraint is:

          FDMEE.AIF_DIM_MEMBERS_STG_U1

           

          After you figure that out, you could look to see what source records might be violating it.

           

          If this is a development environment, then you could truncate the base table in the FDMEE schema and try rerunning the load - you could have bad records that never go cleared out.