2 Replies Latest reply on Oct 2, 2014 4:45 PM by Reach87

    FDMEE AIF_OPEN_INTERFACE - Regenerate ODI Scenario when migrating

    Reach87

      Hi Everyone -

       

      This isn't an outstanding issue, just wanted to share a lesson learned incase it can save anyone some time in the future.

       

      I recently migrated an FDMEE 11.1.2.3.510 application from a dev/test to production environments via LCM.  When validating the application in the production environment everything worked as expected with the exception of an FDM location which was importing data directly from the AIF_OPEN_INTERFACE table.  On import no data would be imported from the open interface table and the process detail log (pasted below) would show very little error information - really just the statement: FATAL [AIF]: Error in Launch GL Load Balances Data

       

      I compared the environments in detail to detect any variances, recycled the servers, etc. etc. - no luck.  Finally I found a line in the ODI log referring to an ODI Scenario that reminded me of something - when I originally developed the location in the dev/test environment there was a required step to generate an ODI scenario.  Specifically, there is a button labeled 'Regenerate ODI Scenario' on the import format screen.  When migrating between environments, at least initially, you must regenerate the ODI scenario in the new environment for the data import from AIF_OPEN_INTERFACE to work.  Very logical and self-explanatory, but something I felt could be easily overlooked when migrating.

       

      Level 5 process detail log and a few lines from the ODI log below to hopefully hit for google search results.

       

      Best,

      Dan H

       

      FDMEE Process Detail Log:

       

      2014-09-23 12:41:54,724 INFO  [AIF]: FDMEE Process Start, Process ID: 48

      2014-09-23 12:41:54,724 INFO  [AIF]: FDMEE Logging Level: 5

      2014-09-23 12:41:54,724 INFO  [AIF]: FDMEE Log File: \\DTC2-HYPFND01\fdmdata\FDMEE\outbox\logs\RCAPHFM_48.log

      2014-09-23 12:41:54,724 INFO  [AIF]: User:dheinitsh

      2014-09-23 12:41:54,724 INFO  [AIF]: Location:RCAP_MRI_SQL (Partitionkey:9)

      2014-09-23 12:41:54,724 INFO  [AIF]: Period Name:Dec - 2015 (Period Key:12/1/15 12:00 AM)

      2014-09-23 12:41:54,724 INFO  [AIF]: Category Name:Actual (Category key:1)

      2014-09-23 12:41:54,724 INFO  [AIF]: Rule Name:RCAP_MRI_Actual_SQL (Rule ID:8)

      2014-09-23 12:41:58,468 INFO  [AIF]: Jython Version: 2.5.1 (Release_2_5_1:6813, Sep 26 2009, 13:47:54)

      [Oracle JRockit(R) (Oracle Corporation)]

      2014-09-23 12:41:58,468 INFO  [AIF]: Java Platform: java1.6.0_37

      2014-09-23 12:41:58,468 INFO  [AIF]: Log File Encoding: UTF-8

      2014-09-23 12:42:03,990 INFO  [AIF]: -------START IMPORT STEP-------

      2014-09-23 12:42:04,318 DEBUG [AIF]: Comm.finalizeProcess - START

      2014-09-23 12:42:04,318 DEBUG [AIF]: CommData.updateRuleStatus - START

      2014-09-23 12:42:04,334 DEBUG [AIF]: CommData.getRuleInfo - START

      2014-09-23 12:42:04,334 DEBUG [AIF]:

              SELECT brl.RULE_ID

              ,br.RULE_NAME

              ,brl.PARTITIONKEY

              ,brl.CATKEY

              ,part.PARTVALGROUP

              ,br.SOURCE_SYSTEM_ID

              ,ss.SOURCE_SYSTEM_TYPE

              ,CASE

                WHEN ss.SOURCE_SYSTEM_TYPE LIKE 'EBS%' THEN 'N'

                WHEN ss.SOURCE_SYSTEM_TYPE LIKE 'PS%' THEN 'N'

                WHEN ss.SOURCE_SYSTEM_TYPE LIKE 'FUSION%' THEN 'N'

                WHEN ss.SOURCE_SYSTEM_TYPE LIKE 'FILE%' THEN 'N'

                ELSE 'Y'

              END SOURCE_ADAPTER_FLAG

              ,app.APPLICATION_ID

              ,app.TARGET_APPLICATION_NAME

              ,app.TARGET_APPLICATION_TYPE

              ,app.DATA_LOAD_METHOD

              ,brl.PLAN_TYPE

              ,CASE brl.PLAN_TYPE

                WHEN 'PLAN1' THEN 1

                WHEN 'PLAN2' THEN 2

                WHEN 'PLAN3' THEN 3

                WHEN 'PLAN4' THEN 4

                WHEN 'PLAN5' THEN 5

                ELSE 0

              END PLAN_NUMBER

              ,br.INCL_ZERO_BALANCE_FLAG

              ,br.PERIOD_MAPPING_TYPE

              ,br.INCLUDE_ADJ_PERIODS_FLAG

              ,br.BALANCE_TYPE ACTUAL_FLAG

              ,br.AMOUNT_TYPE

              ,br.BALANCE_SELECTION

              ,br.BALANCE_METHOD_CODE

              ,COALESCE(br.SIGNAGE_METHOD, 'ABSOLUTE') SIGNAGE_METHOD

              ,br.CURRENCY_CODE

              ,br.BAL_SEG_VALUE_OPTION_CODE

              ,brl.EXECUTION_MODE

              ,COALESCE(brl.IMPORT_FROM_SOURCE_FLAG, 'Y') IMPORT_FROM_SOURCE_FLAG

              ,COALESCE(brl.RECALCULATE_FLAG, 'N') RECALCULATE_FLAG

              ,COALESCE(brl.EXPORT_TO_TARGET_FLAG, 'N') EXPORT_TO_TARGET_FLAG

              ,COALESCE(brl.CHECK_FLAG, 'N') CHECK_FLAG

              ,CASE

                WHEN (br.LEDGER_GROUP_ID IS NOT NULL) THEN 'MULTI'

                WHEN (br.SOURCE_LEDGER_ID IS NOT NULL) THEN 'SINGLE'

                ELSE 'NONE'

              END LEDGER_GROUP_CODE

              ,COALESCE(br.BALANCE_AMOUNT_BS, 'YTD') BALANCE_AMOUNT_BS

              ,COALESCE(br.BALANCE_AMOUNT_IS, 'PERIODIC') BALANCE_AMOUNT_IS

              ,br.LEDGER_GROUP

              ,(SELECT brd.DETAIL_CODE

                FROM AIF_BAL_RULE_DETAILS brd

                WHERE brd.RULE_ID = br.RULE_ID

                AND brd.DETAIL_TYPE = 'LEDGER'       

              ) PS_LEDGER

              ,CASE lg.LEDGER_TEMPLATE

                WHEN 'COMMITMENT' THEN 'Y'

                ELSE 'N'

              END KK_FLAG

              ,p.LAST_UPDATED_BY

              ,p.AIF_WEB_SERVICE_URL WEB_SERVICE_URL

              ,p.EPM_ORACLE_INSTANCE

              ,brl.JOURNAL_FLAG

              ,br.MULTI_PERIOD_FILE_FLAG

              ,br.IMPGROUPKEY

              FROM AIF_PROCESSES p

              INNER JOIN AIF_BAL_RULE_LOADS brl

                ON brl.LOADID = p.PROCESS_ID

              INNER JOIN AIF_BALANCE_RULES br

                ON br.RULE_ID = brl.RULE_ID

              INNER JOIN AIF_SOURCE_SYSTEMS ss

                ON ss.SOURCE_SYSTEM_ID = br.SOURCE_SYSTEM_ID

              INNER JOIN AIF_TARGET_APPLICATIONS app

                ON app.APPLICATION_ID = brl.APPLICATION_ID

              INNER JOIN TPOVPARTITION part

                ON part.PARTITIONKEY = br.PARTITIONKEY

              INNER JOIN TBHVIMPGROUP imp

                ON imp.IMPGROUPKEY = part.PARTIMPGROUP

              LEFT OUTER JOIN AIF_COA_LEDGERS l

                ON l.SOURCE_SYSTEM_ID = p.SOURCE_SYSTEM_ID

                AND l.SOURCE_LEDGER_ID = COALESCE(br.SOURCE_LEDGER_ID,imp.IMPSOURCELEDGERID)

              LEFT OUTER JOIN AIF_PS_SET_CNTRL_REC_STG scr

                ON scr.SOURCE_SYSTEM_ID = l.SOURCE_SYSTEM_ID

                AND scr.SETCNTRLVALUE = l.SOURCE_LEDGER_NAME

                AND scr.RECNAME = 'LED_GRP_TBL'

              LEFT OUTER JOIN AIF_PS_LED_GRP_TBL_STG lg

                ON lg.SOURCE_SYSTEM_ID = scr.SOURCE_SYSTEM_ID

                AND lg.SETID = scr.SETID

                AND lg.LEDGER_GROUP = br.LEDGER_GROUP

              WHERE p.PROCESS_ID = 48

          

      2014-09-23 12:42:04,349 DEBUG [AIF]:

            SELECT adim.BALANCE_COLUMN_NAME DIMNAME

            ,adim.DIMENSION_ID

            ,dim.TARGET_DIMENSION_CLASS_NAME

            ,(SELECT COA_SEGMENT_NAME FROM AIF_COA_SEGMENTS cs WHERE cs.COA_LINE_ID = tiie.IMPSOURCECOALINEID1) COA_SEGMENT_NAME1

            ,(SELECT COA_SEGMENT_NAME FROM AIF_COA_SEGMENTS cs WHERE cs.COA_LINE_ID = tiie.IMPSOURCECOALINEID2) COA_SEGMENT_NAME2

            ,(SELECT COA_SEGMENT_NAME FROM AIF_COA_SEGMENTS cs WHERE cs.COA_LINE_ID = tiie.IMPSOURCECOALINEID3) COA_SEGMENT_NAME3

            ,(SELECT COA_SEGMENT_NAME FROM AIF_COA_SEGMENTS cs WHERE cs.COA_LINE_ID = tiie.IMPSOURCECOALINEID4) COA_SEGMENT_NAME4

            ,(SELECT COA_SEGMENT_NAME FROM AIF_COA_SEGMENTS cs WHERE cs.COA_LINE_ID = tiie.IMPSOURCECOALINEID5) COA_SEGMENT_NAME5

            ,(SELECT CASE mdd.ORPHAN_OPTION_CODE

                WHEN 'CHILD' THEN 'N'

                WHEN 'ROOT' THEN 'N'

                ELSE 'Y'

              END DIMENSION_FILTER_FLAG

              FROM AIF_MAP_DIM_DETAILS_V mdd

              ,AIF_MAPPING_RULES mr

              WHERE mr.PARTITIONKEY = tpp.PARTITIONKEY

              AND mdd.RULE_ID = mr.RULE_ID

              AND mdd.DIMENSION_ID = adim.DIMENSION_ID

            ) DIMENSION_FILTER_FLAG

            ,tiie.IMPCONCATCHAR

            FROM TPOVPARTITION tpp

            INNER JOIN AIF_TARGET_APPL_DIMENSIONS adim

              ON adim.APPLICATION_ID = 4

            INNER JOIN AIF_DIMENSIONS dim

              ON dim.DIMENSION_ID = adim.DIMENSION_ID

            LEFT OUTER JOIN TBHVIMPITEMERPI tiie

              ON tiie.IMPGROUPKEY = tpp.PARTIMPGROUP

              AND tiie.IMPFLDFIELDNAME = adim.BALANCE_COLUMN_NAME

              AND tiie.IMPMAPTYPE = 'ERP'

            WHERE tpp.PARTITIONKEY = 9

            AND adim.BALANCE_COLUMN_NAME IS NOT NULL

            AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

        

            ORDER BY adim.BALANCE_COLUMN_NAME

        

      2014-09-23 12:42:04,349 DEBUG [AIF]: {'APPLICATION_ID': 4L, 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'PLAN_TYPE': None, 'RULE_NAME': u'RCAP_MRI_Actual_SQL', 'ACTUAL_FLAG': None, 'IS_INCREMENTAL_LOAD': False, 'EPM_ORACLE_INSTANCE': u'C:\\Oracle\\Middleware\\user_projects\\epmsystem_FND', 'CATKEY': 1L, 'BAL_SEG_VALUE_OPTION_CODE': None, 'INCLUDE_ADJ_PERIODS_FLAG': u'N', 'PERIOD_MAPPING_TYPE': u'EXPLICIT', 'SOURCE_SYSTEM_TYPE': u'OTHERS', 'CHECK_FLAG': u'N', 'LEDGER_GROUP': None, 'TARGET_APPLICATION_NAME': u'RCAPHFM', 'RECALCULATE_FLAG': u'Y', 'SOURCE_SYSTEM_ID': 5L, 'TEMP_DATA_TABLE_NAME': 'TDATASEG_T', 'KK_FLAG': u'N', 'IMPGROUPKEY': None, 'AMOUNT_TYPE': None, 'DATA_TABLE_NAME': 'TDATASEG', 'EXPORT_TO_TARGET_FLAG': u'N', 'JOURNAL_FLAG': None, 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'ICP', u'UD1', u'UD2'], 'TDATAMAPTYPE': 'ERP', 'LAST_UPDATED_BY': u'dheinitsh', 'DIMNAME_MAP': {u'ICP': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'ICP', 'COA_SEGMENT_NAME5': None, 'COA_SEGMENT_NAME1': None, 'COA_SEGMENT_NAME2': None, 'COA_SEGMENT_NAME3': None, 'DIMENSION_FILTER_FLAG': None, 'COA_SEGMENT_NAME4': None, 'DIMNAME': u'ICP', 'DIMENSION_ID': 30L}, u'ENTITY': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Entity', 'COA_SEGMENT_NAME5': None, 'COA_SEGMENT_NAME1': None, 'COA_SEGMENT_NAME2': None, 'COA_SEGMENT_NAME3': None, 'DIMENSION_FILTER_FLAG': None, 'COA_SEGMENT_NAME4': None, 'DIMNAME': u'ENTITY', 'DIMENSION_ID': 34L}, u'UD2': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME5': None, 'COA_SEGMENT_NAME1': None, 'COA_SEGMENT_NAME2': None, 'COA_SEGMENT_NAME3': None, 'DIMENSION_FILTER_FLAG': None, 'COA_SEGMENT_NAME4': None, 'DIMNAME': u'UD2', 'DIMENSION_ID': 32L}, u'ACCOUNT': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Account', 'COA_SEGMENT_NAME5': None, 'COA_SEGMENT_NAME1': None, 'COA_SEGMENT_NAME2': None, 'COA_SEGMENT_NAME3': None, 'DIMENSION_FILTER_FLAG': None, 'COA_SEGMENT_NAME4': None, 'DIMNAME': u'ACCOUNT', 'DIMENSION_ID': 33L}, u'UD1': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME5': None, 'COA_SEGMENT_NAME1': None, 'COA_SEGMENT_NAME2': None, 'COA_SEGMENT_NAME3': None, 'DIMENSION_FILTER_FLAG': None, 'COA_SEGMENT_NAME4': None, 'DIMNAME': u'UD1', 'DIMENSION_ID': 31L}}, 'TARGET_APPLICATION_TYPE': u'HFM', 'PARTITIONKEY': 9L, 'PARTVALGROUP': u'RCAP_TB_Check', 'LEDGER_GROUP_CODE': u'NONE', 'INCLUDE_ZERO_BALANCE_FLAG': u'N', 'EXECUTION_MODE': u'SNAPSHOT', 'PLAN_NUMBER': 0L, 'MULTI_PERIOD_FILE_FLAG': None, 'PS_LEDGER': None, 'BALANCE_SELECTION': None, 'BALANCE_AMOUNT_IS': u'PERIODIC', 'RULE_ID': 8L, 'BALANCE_AMOUNT_BS': u'YTD', 'CURRENCY_CODE': None, 'SOURCE_ADAPTER_FLAG': u'Y', 'BALANCE_METHOD_CODE': None, 'SIGNAGE_METHOD': u'ABSOLUTE', 'WEB_SERVICE_URL': u'http://DTC2-HYPFND01.arlcap.net:6550/aif', 'DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI'}

      2014-09-23 12:42:04,349 DEBUG [AIF]: CommData.getRuleInfo - END

      2014-09-23 12:42:04,349 DEBUG [AIF]:

          UPDATE AIF_BALANCE_RULES

          SET STATUS = CASE 'FAILED'

            WHEN 'SUCCESS' THEN

              CASE (

                SELECT COUNT(*)

                FROM AIF_PROCESS_DETAILS pd

                WHERE pd.PROCESS_ID = 48

                AND pd.STATUS IN ('FAILED','WARNING')

              )

              WHEN 0 THEN 'SUCCESS'

              ELSE (

                SELECT MIN(pd.STATUS)

                FROM AIF_PROCESS_DETAILS pd

                WHERE pd.PROCESS_ID = 48

                AND pd.STATUS IN ('FAILED','WARNING')

              )

              END

            ELSE 'FAILED'

          END

          WHERE RULE_ID = 8

       

      2014-09-23 12:42:04,365 DEBUG [AIF]: CommData.updateRuleStatus - END

      2014-09-23 12:42:04,365 FATAL [AIF]: Error in Launch GL Load Balances Data

      2014-09-23 12:42:04,365 DEBUG [AIF]: Comm.updateProcess - START

      2014-09-23 12:42:04,380 DEBUG [AIF]: Comm.updateProcess - END

      2014-09-23 12:42:04,380 INFO  [AIF]: FDMEE Process End, Process ID: 48

       

      Lines of important from the ODI Log:

       

      ODI-1217: Session COMM_LOAD_BALANCES (29501) fails with return code 7000.

      ODI-1226: Step FAILED - Import Data fails after 1 attempt(s).

      ODI-1232: Procedure COMM Finalize Process execution fails.

      Caused By: org.apache.bsf.BSFException: exception from Jython: Traceback (most recent call last): File "<string>", line 13, in <module> File "<string>", line 162, in finalizeProcess RuntimeError:

      ODI-1226: Step Launch GL Load Balances Data fails after 1 attempt(s).

      ODI-1241: Oracle Data Integrator tool execution fails. Caused By: com.sunopsis.sql.SnpsMissingParametersException:

      ODI-30058: -SCEN_NAME parameter is mandatory.