12 Replies Latest reply on Nov 4, 2016 7:00 AM by Caniut Alex

    FDMEE to HFM (Updating TDATASEG_T)

    Caniut Alex

      Hi Experts,

       

      We have been running into an issue in FDMEE 11.1.2.4 where we are pulling data from EBS R12 using UDA . We have setup a data load rule for this location .

       

      Now when I execute the data load rule ,on the FDMEE log its says Processing Mappings for 'Account ' with the update sql and hangs on from there . Below is the process log of it and please help us to get with this.

       

       

      2016-10-04 16:54:23,340 INFO  [AIF]: Location     : Actual_UDA_2 (Partitionkey:25)

      2016-10-04 16:54:23,341 INFO  [AIF]: Period Name  : March-2016 (Period Key:31/03/16 12:00 AM)

      2016-10-04 16:54:23,342 INFO  [AIF]: Category Name: Actual (Category key:1)

      2016-10-04 16:54:23,342 INFO  [AIF]: Rule Name    : WGL_HYP_ACTUAL_UDA_2 (Rule ID:43)

      2016-10-04 16:54:33,363 INFO  [AIF]: FDM Version: 11.1.2.4.200

      2016-10-04 16:54:33,369 INFO  [AIF]: Log File Encoding: UTF-8

      2016-10-04 16:54:36,253 INFO  [AIF]: -------START IMPORT STEP-------

      2016-10-04 16:54:36,364 DEBUG [AIF]: CommData.preImportData - START

      2016-10-04 16:54:36,375 DEBUG [AIF]: CommData.getRuleInfo - START

      2016-10-04 16:54:36,427 DEBUG [AIF]:

            SELECT adim.BALANCE_COLUMN_NAME DIMNAME, adim.DIMENSION_ID, dim.TARGET_DIMENSION_CLASS_NAME, tiie.IMPCONCATCHAR

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

      FROM TPOVPARTITION tpp

      INNER JOIN AIF_TARGET_APPL_DIMENSIONS adim

      ON adim.APPLICATION_ID = 6

            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 = 25

      AND adim.BALANCE_COLUMN_NAME IS NOT NULL

      AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

            ORDER BY adim.BALANCE_COLUMN_NAME

       

      2016-10-04 16:54:36,449 DEBUG [AIF]: {'APPLICATION_ID': 6L, 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'PLAN_TYPE': None, 'RULE_NAME': u'WGL_HYP_ACTUAL_UDA_2', 'ACTUAL_FLAG': None, 'IS_INCREMENTAL_LOAD': False, 'EPM_ORACLE_INSTANCE': u'D:\\Oracle\\Middleware\\user_projects\\epmsystem1', 'CATKEY': 1L, 'BAL_SEG_VALUE_OPTION_CODE': None, 'INCLUDE_ADJ_PERIODS_FLAG': u'N', 'PERIOD_MAPPING_TYPE': u'EXPLICIT', 'SOURCE_SYSTEM_TYPE': u'UDA_ORCL', 'CHECK_FLAG': u'N', 'LEDGER_GROUP': None, 'TARGET_APPLICATION_NAME': u'WP0002', 'RECALCULATE_FLAG': u'Y', 'SOURCE_SYSTEM_ID': 7L, '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, 'SOURCE_APPLICATION_ID': None, 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'ICP', u'UD1', u'UD2', u'UD3', u'UD4', u'UD5', u'UD6', u'UD7', u'UD8'], 'FCI_FLAG': 'N', 'IMPSOURCECOAID': 0L, 'TDATAMAPTYPE': 'ERP', 'LAST_UPDATED_BY': u'L085637', 'DIMNAME_MAP': {u'UD6': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD6', 'DIMENSION_ID': 98L}, u'UD3': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD3', 'DIMENSION_ID': 95L}, u'UD8': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD8', 'DIMENSION_ID': 100L}, u'ICP': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Icp', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'ICP', 'DIMENSION_ID': 92L}, u'ENTITY': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Entity', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'ENTITY', 'DIMENSION_ID': 89L}, u'UD7': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD7', 'DIMENSION_ID': 99L}, u'UD2': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD2', 'DIMENSION_ID': 94L}, u'UD5': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD5', 'DIMENSION_ID': 97L}, u'UD4': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD4', 'DIMENSION_ID': 96L}, u'ACCOUNT': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Account', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'ACCOUNT', 'DIMENSION_ID': 91L}, u'UD1': {'IMPCONCATCHAR': None, 'TARGET_DIMENSION_CLASS_NAME': u'Generic', 'COA_SEGMENT_NAME1': None, 'DIMNAME': u'UD1', 'DIMENSION_ID': 93L}}, 'TARGET_APPLICATION_TYPE': u'HFM', 'PARTITIONKEY': 25L, 'PARTVALGROUP': u'[NONE]', 'LEDGER_GROUP_CODE': u'NONE', 'INCLUDE_ZERO_BALANCE_FLAG': u'N', 'EXECUTION_MODE': None, 'PLAN_NUMBER': 0L, 'MULTI_PERIOD_FILE_FLAG': None, 'PS_LEDGER': None, 'BALANCE_SELECTION': None, 'IMPGROUPFILETYPE': u'ODI', 'BALANCE_AMOUNT_IS': u'PERIODIC', 'RULE_ID': 43L, '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://AU2004SDE566.esdevau.wbcdevau.westpac.com.au:6550/aif', 'DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'PARTTARGETAPPLICATIONID': 6L, 'IMPTARGETSOURCESYSTEMID': 0L}

      2016-10-04 16:54:36,449 DEBUG [AIF]: CommData.getRuleInfo - END

      2016-10-04 16:54:36,488 DEBUG [AIF]: CommData.insertPeriods - START

      2016-10-04 16:54:36,499 DEBUG [AIF]: CommData.getLedgerListAndMap - START

      2016-10-04 16:54:36,500 DEBUG [AIF]: CommData.getLedgerSQL - START

      2016-10-04 16:54:36,501 DEBUG [AIF]: CommData.getLedgerSQL - END

      2016-10-04 16:54:36,501 DEBUG [AIF]:

                SELECT COALESCE(br.SOURCE_LEDGER_ID,0) SOURCE_LEDGER_ID, NULL SOURCE_LEDGER_NAME, NULL SOURCE_COA_ID

      ,br.CALENDAR_ID, NULL SETID, NULL PERIOD_TYPE, NULL LEDGER_TABLE_NAME

      FROM AIF_BALANCE_RULES br

      WHERE br.RULE_ID = 43

       

      2016-10-04 16:54:36,504 DEBUG [AIF]: CommData.getLedgerListAndMap - END

      2016-10-04 16:54:36,508 DEBUG [AIF]: doAppPeriodMappingsExist - WP0002: N

      2016-10-04 16:54:36,510 DEBUG [AIF]: Period mapping section: ERPI/EXPLICIT/BUDGET/APFY

      2016-10-04 16:54:36,514 DEBUG [AIF]:

              INSERT INTO AIF_PROCESS_PERIODS (

      PROCESS_ID

      ,PERIODKEY

      ,PERIOD_ID

      ,ADJUSTMENT_PERIOD_FLAG

      ,GL_PERIOD_YEAR

      ,GL_PERIOD_NUM

      ,GL_PERIOD_NAME

      ,GL_PERIOD_CODE

      ,GL_EFFECTIVE_PERIOD_NUM

      ,YEARTARGET

      ,PERIODTARGET

      ,IMP_ENTITY_TYPE

      ,IMP_ENTITY_ID

      ,IMP_ENTITY_NAME

      ,TRANS_ENTITY_TYPE

      ,TRANS_ENTITY_ID

      ,TRANS_ENTITY_NAME

      ,PRIOR_PERIOD_FLAG

      ,SOURCE_LEDGER_ID

      )

                      SELECT DISTINCT brl.LOADID PROCESS_ID

      ,pp.PERIODKEY PERIODKEY

      ,prd.PERIOD_ID

      ,COALESCE(prd.ADJUSTMENT_PERIOD_FLAG, 'N') ADJUSTMENT_PERIOD_FLAG

      ,COALESCE(prd.YEAR, ppsrc.GL_PERIOD_YEAR,0) GL_PERIOD_YEAR

      ,COALESCE(prd.PERIOD_NUM, ppsrc.GL_PERIOD_NUM,0) GL_PERIOD_NUM

      ,COALESCE(prd.PERIOD_NAME, ppsrc.GL_PERIOD_NAME,'0') GL_PERIOD_NAME

      ,COALESCE(prd.PERIOD_CODE, CAST(COALESCE(prd.PERIOD_NUM, ppsrc.GL_PERIOD_NUM,0) AS VARCHAR(38)),'0') GL_PERIOD_CODE

      ,(COALESCE(prd.YEAR, ppsrc.GL_PERIOD_YEAR,0) * 10000 + COALESCE(prd.PERIOD_NUM, ppsrc.GL_PERIOD_NUM,0)) GL_EFFECTIVE_PERIOD_NUM

      ,pp.YEARTARGET YEARTARGET

      ,pp.PERIODTARGET PERIODTARGET

      ,'PROCESS_BAL_IMP' IMP_ENTITY_TYPE

      ,(COALESCE(prd.YEAR, ppsrc.GL_PERIOD_YEAR,0) * 10000 + COALESCE(prd.PERIOD_NUM, ppsrc.GL_PERIOD_NUM,0)) IMP_ENTITY_ID

      ,COALESCE(prd.PERIOD_NAME, ppsrc.GL_PERIOD_NAME,'0')||' ('||pp.PERIODDESC||')' IMP_ENTITY_NAME

      ,'PROCESS_BAL_TRANS' TRANS_ENTITY_TYPE

      ,(COALESCE(prd.YEAR, ppsrc.GL_PERIOD_YEAR,0) * 10000 + COALESCE(prd.PERIOD_NUM, ppsrc.GL_PERIOD_NUM,0)) TRANS_ENTITY_ID

      ,pp.PERIODDESC TRANS_ENTITY_NAME

      ,'N' PRIOR_PERIOD_FLAG

      ,0 SOURCE_LEDGER_ID

      FROM (

      AIF_BAL_RULE_LOADS brl

      INNER JOIN TPOVCATEGORY pc

      ON pc.CATKEY = brl.CATKEY

       

                        INNER JOIN TPOVPERIOD_FLAT_V pp

      ON pp.PERIODFREQ = pc.CATFREQ

      AND pp.PERIODKEY >= brl.START_PERIODKEY

      AND pp.PERIODKEY <= brl.END_PERIODKEY

                      )

      INNER JOIN TPOVPERIODSOURCE ppsrc

      ON ppsrc.PERIODKEY = pp.PERIODKEY

      AND ppsrc.MAPPING_TYPE = 'EXPLICIT'

      AND ppsrc.SOURCE_SYSTEM_ID = 7

      AND ppsrc.CALENDAR_ID IN ('A')

      LEFT OUTER JOIN AIF_GL_PERIODS_STG prd

      ON prd.PERIOD_ID = ppsrc.PERIOD_ID

      AND prd.SOURCE_SYSTEM_ID = ppsrc.SOURCE_SYSTEM_ID

      AND prd.CALENDAR_ID = ppsrc.CALENDAR_ID

       

                        AND prd.ADJUSTMENT_PERIOD_FLAG = 'N'

      WHERE brl.LOADID = 786

      ORDER BY pp.PERIODKEY

      ,GL_EFFECTIVE_PERIOD_NUM

       

      2016-10-04 16:54:36,515 DEBUG [AIF]: periodSQL - periodParams: ['N', 'PROCESS_BAL_IMP', 'PROCESS_BAL_TRANS', 0L, u'EXPLICIT', 7L, u'A', 'N', 786]

      2016-10-04 16:54:36,543 DEBUG [AIF]: insertRowCount: 1

      2016-10-04 16:54:36,545 DEBUG [AIF]: CommData.insertPeriods - END

      2016-10-04 16:54:36,550 DEBUG [AIF]: CommData.getPovList - START

      2016-10-04 16:54:36,550 DEBUG [AIF]:

              SELECT DISTINCT brl.PARTITIONKEY, part.PARTNAME, brl.CATKEY, cat.CATNAME, pprd.PERIODKEY

      ,COALESCE(pp.PERIODDESC, TO_CHAR(pprd.PERIODKEY,'YYYY-MM-DD HH24:MI:SS')) PERIODDESC

      ,brl.RULE_ID, br.RULE_NAME, CASE WHEN (tlp.INTLOCKSTATE = 60) THEN 'Y' ELSE 'N' END LOCK_FLAG

      FROM AIF_BAL_RULE_LOADS brl

      INNER JOIN AIF_BALANCE_RULES br

      ON br.RULE_ID = brl.RULE_ID

      INNER JOIN TPOVPARTITION part

      ON part.PARTITIONKEY = brl.PARTITIONKEY

      INNER JOIN TPOVCATEGORY cat

      ON cat.CATKEY = brl.CATKEY

      INNER JOIN AIF_PROCESS_PERIODS pprd

      ON pprd.PROCESS_ID = brl.LOADID

              LEFT OUTER JOIN TPOVPERIOD pp

      ON pp.PERIODKEY = pprd.PERIODKEY

      LEFT OUTER JOIN TLOGPROCESS tlp

      ON tlp.PARTITIONKEY = brl.PARTITIONKEY AND tlp.CATKEY = brl.CATKEY AND tlp.PERIODKEY = pprd.PERIODKEY AND tlp.RULE_ID = brl.RULE_ID

      WHERE brl.LOADID = 786

      ORDER BY brl.PARTITIONKEY, brl.CATKEY, pprd.PERIODKEY, brl.RULE_ID

       

      2016-10-04 16:54:36,568 DEBUG [AIF]: CommData.getPovList - END

      2016-10-04 16:54:36,570 DEBUG [AIF]: CommData.insertImportProcessDetails - START

      2016-10-04 16:54:36,571 DEBUG [AIF]:

                INSERT INTO AIF_PROCESS_DETAILS (

      PROCESS_ID, ENTITY_TYPE, ENTITY_ID, ENTITY_NAME, ENTITY_NAME_ORDER, TARGET_TABLE_NAME, EXECUTION_START_TIME

      ,EXECUTION_END_TIME, RECORDS_PROCESSED, STATUS, LAST_UPDATED_BY, LAST_UPDATE_DATE

      )

      SELECT PROCESS_ID, ENTITY_TYPE, ENTITY_ID, ENTITY_NAME, ENTITY_NAME_ORDER, 'TDATASEG' TARGET_TABLE_NAME, CURRENT_TIMESTAMP EXECUTION_START_TIME

      ,NULL EXECUTION_END_TIME, 0 RECORDS_PROCESSED, 'PENDING' STATUS, 'L085637' LAST_UPDATED_BY, CURRENT_TIMESTAMP LAST_UPDATE_DATE

      FROM (

      SELECT DISTINCT PROCESS_ID, IMP_ENTITY_TYPE ENTITY_TYPE, IMP_ENTITY_ID ENTITY_ID, IMP_ENTITY_NAME ENTITY_NAME

      ,(COALESCE(SOURCE_LEDGER_ID,0) * 100000000 + GL_EFFECTIVE_PERIOD_NUM) ENTITY_NAME_ORDER

      FROM AIF_PROCESS_PERIODS

      WHERE PROCESS_ID = 786

      ) q

      ORDER BY ENTITY_NAME_ORDER

       

      2016-10-04 16:54:36,581 DEBUG [AIF]: CommData.insertImportProcessDetails - END

      2016-10-04 16:54:36,589 DEBUG [AIF]: Comm.doScriptInit - START

      2016-10-04 16:54:36,797 DEBUG [AIF]: fdmContext: {BATCHSCRIPTDIR=D:\Oracle\Middleware\user_projects\epmsystem1\FinancialDataQuality, INBOXDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002\inbox, LOCNAME=Actual_UDA_2, SOURCENAME=EBS_R12, APPID=6, SOURCEID=7, APPROOTDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002, IMPORTFORMAT=ACTUAL_UDA, SCRIPTSDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002\data\scripts, EPMORACLEHOME=D:\Oracle\Middleware\EPMSystem11R1, TARGETAPPTYPE=HFM, RULEID=43, CATNAME=Actual, EPMORACLEINSTANCEHOME=D:\Oracle\Middleware\user_projects\epmsystem1, LOADID=786, PERIODNAME=March-2016, IMPORTMODE=null, SOURCETYPE=UDA_ORCL, PERIODKEY=2016-03-31, EXPORTFLAG=N, TARGETAPPDB=null, TARGETAPPNAME=WP0002, LOCKEY=25, RULENAME=WGL_HYP_ACTUAL_UDA_2, OUTBOXDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002\outbox, MULTIPERIODLOAD=N, EXPORTMODE=null, CATKEY=1, USERNAME=L085637, FILEDIR=null, IMPORTFLAG=Y, USERLOCALE=null}

      2016-10-04 16:54:36,821 DEBUG [AIF]: Added jar to Classpath: D:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\lib\epm-aif-odi-manual.jar

      2016-10-04 16:54:36,864 DEBUG [AIF]: The executeEventScript is set to: YES

      2016-10-04 16:54:36,865 DEBUG [AIF]: The AppRootFolder is set to: \\AU2004SDE566\FDMEEDev\Apps\WP0002

      2016-10-04 16:54:36,865 DEBUG [AIF]: The JavaHome is set to: %EPM_ORACLE_HOME%/../jdk160_35

      2016-10-04 16:54:36,865 DEBUG [AIF]: The OleDatabaseProvider is set to: ORAOLEDB.ORACLE

      2016-10-04 16:54:36,865 DEBUG [AIF]: Comm.doScriptInit - END

      2016-10-04 16:54:36,866 DEBUG [AIF]: Comm.executeScript - START

      2016-10-04 16:54:36,892 INFO  [AIF]: Executing the following script: \\AU2004SDE566\FDMEEDev\Apps\WP0002/data/scripts/event/BefImport.py

      2016-10-04 16:54:42,429 DEBUG [AIF]: Successfully collected stats on: TDATAMAP_T

      2016-10-04 16:54:42,652 DEBUG [AIF]: Successfully collected stats on: TDATASEG_T

      2016-10-04 16:54:42,658 DEBUG [AIF]: Comm.executeVBScript - START

      2016-10-04 16:54:42,693 DEBUG [AIF]: The WindowsTempFolder is set to: c:\windows\temp

      2016-10-04 16:54:42,694 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002\data\scripts\event\BefImport.vbs

      2016-10-04 16:54:42,695 DEBUG [AIF]: Comm.executeVBScript - END

      2016-10-04 16:54:42,695 DEBUG [AIF]: Comm.executeScript - END

      2016-10-04 16:54:42,695 DEBUG [AIF]: CommData.preImportData - END

      2016-10-04 16:54:48,734 DEBUG [AIF]:

      ERP GL Balances Print Variables

      ===============================

      p_sql_db_type: ORACLE

      p_adp_project_code: AIF_ORACLE

      p_adp_scenario_name: ACTUAL_UDA

      p_prd_entity_name: MAR-16 (March-2016)

      p_prd_periodkey: 2016-03-31 00:00:00

       

      2016-10-04 16:59:38,248 DEBUG [AIF]: Comm.updateProcessDetail - START

      2016-10-04 16:59:38,253 DEBUG [AIF]:

          UPDATE AIF_PROCESS_DETAILS

      SET STATUS = 'SUCCESS'

      ,RECORDS_PROCESSED = CASE

      WHEN RECORDS_PROCESSED IS NULL THEN 0

      ELSE RECORDS_PROCESSED

      END + 0

      ,EXECUTION_END_TIME = CURRENT_TIMESTAMP

      ,LAST_UPDATED_BY = CASE

            WHEN ('L085637' IS NULL) THEN LAST_UPDATED_BY

      ELSE 'L085637'

      END

      ,LAST_UPDATE_DATE = CURRENT_TIMESTAMP

      WHERE PROCESS_ID = 786

      AND ENTITY_TYPE = 'PROCESS_BAL_IMP'

      AND ENTITY_NAME = 'MAR-16 (March-2016)'

       

      2016-10-04 16:59:38,261 DEBUG [AIF]: Comm.updateProcessDetail - END

      2016-10-04 16:59:38,445 DEBUG [AIF]: CommData.postImportData - START

      2016-10-04 16:59:38,455 DEBUG [AIF]: CommData.getRuleInfo - START

      2016-10-04 16:59:38,455 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 = 'FILE' THEN 'N'

      WHEN ss.SOURCE_SYSTEM_TYPE = 'EPM' 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 WHEN 'PLAN6' THEN 6 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 ss.SOURCE_SYSTEM_TYPE = 'EPM' THEN 'NONE'

      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, imp.IMPSOURCELEDGERID

      ,imp.IMPGROUPFILETYPE, imp.IMPTARGETSOURCESYSTEMID, imp.IMPSOURCECOAID, part.PARTTARGETAPPLICATIONID

      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 = 786

       

      2016-10-04 16:59:38,498 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 DISTINCT 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 = 6

            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 = 25

      AND adim.BALANCE_COLUMN_NAME IS NOT NULL

      AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

       

            ORDER BY adim.BALANCE_COLUMN_NAME

       

      2016-10-04 16:59:38,533 DEBUG [AIF]: {'APPLICATION_ID': 6L, 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'PLAN_TYPE': None, 'RULE_NAME': u'WGL_HYP_ACTUAL_UDA_2', 'ACTUAL_FLAG': None, 'IS_INCREMENTAL_LOAD': False, 'EPM_ORACLE_INSTANCE': u'D:\\Oracle\\Middleware\\user_projects\\epmsystem1', 'CATKEY': 1L, 'BAL_SEG_VALUE_OPTION_CODE': None, 'INCLUDE_ADJ_PERIODS_FLAG': u'N', 'PERIOD_MAPPING_TYPE': u'EXPLICIT', 'SOURCE_SYSTEM_TYPE': u'UDA_ORCL', 'CHECK_FLAG': u'N', 'LEDGER_GROUP': None, 'TARGET_APPLICATION_NAME': u'WP0002', 'RECALCULATE_FLAG': u'Y', 'SOURCE_SYSTEM_ID': 7L, '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, 'SOURCE_APPLICATION_ID': None, 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'ICP', u'UD1', u'UD2', u'UD3', u'UD4', u'UD5', u'UD6', u'UD7', u'UD8'], 'FCI_FLAG': 'N', 'IMPSOURCECOAID': 0L, 'TDATAMAPTYPE': 'ERP', 'LAST_UPDATED_BY': u'L085637', 'DIMNAME_MAP': {u'UD6': {'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'UD6', 'DIMENSION_ID': 98L}, u'UD3': {'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'UD3', 'DIMENSION_ID': 95L}, u'UD8': {'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'UD8', 'DIMENSION_ID': 100L}, 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': 92L}, 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': 89L}, u'UD7': {'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'UD7', 'DIMENSION_ID': 99L}, 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': 94L}, u'UD5': {'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'UD5', 'DIMENSION_ID': 97L}, u'UD4': {'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'UD4', 'DIMENSION_ID': 96L}, 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': 91L}, 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': 93L}}, 'TARGET_APPLICATION_TYPE': u'HFM', 'PARTITIONKEY': 25L, 'PARTVALGROUP': u'[NONE]', 'LEDGER_GROUP_CODE': u'NONE', 'INCLUDE_ZERO_BALANCE_FLAG': u'N', 'EXECUTION_MODE': None, 'PLAN_NUMBER': 0L, 'MULTI_PERIOD_FILE_FLAG': None, 'PS_LEDGER': None, 'BALANCE_SELECTION': None, 'IMPGROUPFILETYPE': u'ODI', 'BALANCE_AMOUNT_IS': u'PERIODIC', 'RULE_ID': 43L, '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://AU2004SDE566.esdevau.wbcdevau.westpac.com.au:6550/aif', 'DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'PARTTARGETAPPLICATIONID': 6L, 'IMPTARGETSOURCESYSTEMID': 0L}

      2016-10-04 16:59:38,535 DEBUG [AIF]: CommData.getRuleInfo - END

      2016-10-04 16:59:38,545 DEBUG [AIF]: CommData.updateTDATASEG_T - START

      2016-10-04 16:59:38,547 DEBUG [AIF]: CommData.getLedgerListAndMap - START

      2016-10-04 16:59:38,549 DEBUG [AIF]: CommData.getLedgerSQL - START

      2016-10-04 16:59:38,549 DEBUG [AIF]: CommData.getLedgerSQL - END

      2016-10-04 16:59:38,550 DEBUG [AIF]:

                SELECT COALESCE(br.SOURCE_LEDGER_ID,0) SOURCE_LEDGER_ID

      ,NULL SOURCE_LEDGER_NAME

      ,NULL SOURCE_COA_ID

      ,br.CALENDAR_ID

      ,NULL SETID

      ,NULL PERIOD_TYPE

      ,NULL LEDGER_TABLE_NAME

      FROM AIF_BALANCE_RULES br

      WHERE br.RULE_ID = 43

       

      2016-10-04 16:59:38,552 DEBUG [AIF]: CommData.getLedgerListAndMap - END

      2016-10-04 16:59:38,557 DEBUG [AIF]: CommData.getPovList - START

      2016-10-04 16:59:38,561 DEBUG [AIF]: doAppPeriodMappingsExist - WP0002: N

      2016-10-04 16:59:38,561 DEBUG [AIF]:

              SELECT DISTINCT brl.PARTITIONKEY, part.PARTNAME, brl.CATKEY, cat.CATNAME, pprd.PERIODKEY

      ,COALESCE(pp.PERIODDESC, TO_CHAR(pprd.PERIODKEY,'YYYY-MM-DD HH24:MI:SS')) PERIODDESC

      ,brl.RULE_ID, br.RULE_NAME, CASE WHEN (tlp.INTLOCKSTATE = 60) THEN 'Y' ELSE 'N' END LOCK_FLAG

      FROM AIF_BAL_RULE_LOADS brl

      INNER JOIN AIF_BALANCE_RULES br

      ON br.RULE_ID = brl.RULE_ID

      INNER JOIN TPOVPARTITION part

      ON part.PARTITIONKEY = brl.PARTITIONKEY

      INNER JOIN TPOVCATEGORY cat

      ON cat.CATKEY = brl.CATKEY

      INNER JOIN AIF_PROCESS_PERIODS pprd

      ON pprd.PROCESS_ID = brl.LOADID

       

                LEFT OUTER JOIN TPOVPERIOD pp

      ON pp.PERIODKEY = pprd.PERIODKEY

      LEFT OUTER JOIN TLOGPROCESS tlp

      ON tlp.PARTITIONKEY = brl.PARTITIONKEY

      AND tlp.CATKEY = brl.CATKEY

      AND tlp.PERIODKEY = pprd.PERIODKEY

      AND tlp.RULE_ID = brl.RULE_ID

      WHERE brl.LOADID = 786

      ORDER BY brl.PARTITIONKEY, brl.CATKEY, pprd.PERIODKEY, brl.RULE_ID

       

      2016-10-04 16:59:38,577 DEBUG [AIF]: CommData.getPovList - END

      2016-10-04 16:59:38,580 INFO  [AIF]:

      Import Data from Source for Period 'March-2016'

      2016-10-04 16:59:38,588 DEBUG [AIF]: CommData.updateWorkflow - START

      2016-10-04 16:59:38,588 DEBUG [AIF]:

          SELECT tlp.PROCESSSTATUS

      ,tlps.PROCESSSTATUSDESC

      ,CASE WHEN (tlp.INTLOCKSTATE = 60) THEN 'Y' ELSE 'N' END LOCK_FLAG

      FROM TLOGPROCESS tlp

      ,TLOGPROCESSSTATES tlps

      WHERE tlp.PARTITIONKEY = 25

      AND tlp.CATKEY = 1

      AND tlp.PERIODKEY = '2016-03-31'

      AND tlp.RULE_ID = 43

      AND tlps.PROCESSSTATUSKEY = tlp.PROCESSSTATUS

       

      2016-10-04 16:59:38,593 DEBUG [AIF]:

              UPDATE TLOGPROCESS

      SET PROCESSENDTIME = CURRENT_TIMESTAMP

              ,PROCESSSTATUS = 0

      ,PROCESSIMP = 0

      ,PROCESSVAL = 0

      ,PROCESSEXP = 0

      ,PROCESSENTLOAD = 0

      ,PROCESSENTVAL = 0

      ,BLNWCDIRTY = 0

      ,BLNLOGICDIRTY = 0

      ,BLNVALDIRTY = 0

      ,PROCESSIMPNOTE = NULL

      ,PROCESSVALNOTE = NULL

      ,PROCESSEXPNOTE = NULL

      ,PROCESSENTLOADNOTE = NULL

      ,PROCESSENTVALNOTE = NULL

      WHERE PARTITIONKEY = 25 AND CATKEY = 1 AND PERIODKEY = '2016-03-31' AND RULE_ID = 43

           

      2016-10-04 16:59:38,604 DEBUG [AIF]:

                INSERT INTO TLOGPROCESS (

      PROCESSENDTIME

       

              ,PROCESSSTATUS

      ,PROCESSIMP

      ,PROCESSVAL

      ,PROCESSEXP

      ,PROCESSENTLOAD

      ,PROCESSENTVAL

      ,BLNWCDIRTY

      ,BLNLOGICDIRTY

      ,BLNVALDIRTY

      ,PROCESSIMPNOTE

      ,PROCESSVALNOTE

      ,PROCESSEXPNOTE

      ,PROCESSENTLOADNOTE

      ,PROCESSENTVALNOTE

      ,PARTITIONKEY

      ,CATKEY

      ,PERIODKEY

      ,RULE_ID

      ) VALUES (

      CURRENT_TIMESTAMP

       

              ,0

      ,0

      ,0

      ,0

      ,0

      ,0

      ,0

      ,0

      ,0

      ,NULL

      ,NULL

      ,NULL

      ,NULL

      ,NULL

      ,25

      ,1

      ,'2016-03-31'

      ,43

      )

       

      2016-10-04 16:59:38,626 DEBUG [AIF]: CommData.updateWorkflow - END

      2016-10-04 16:59:38,627 DEBUG [AIF]:

                  SELECT adim.BALANCE_COLUMN_NAME DIMNAME

      ,dim.TARGET_DIMENSION_CLASS_NAME

      FROM TPOVPARTITION tpp

      INNER JOIN AIF_TARGET_APPL_DIMENSIONS adim

      ON adim.APPLICATION_ID = tpp.PARTTARGETAPPLICATIONID

                    AND adim.BALANCE_COLUMN_NAME IS NOT NULL

       

                  INNER JOIN AIF_DIMENSIONS dim

      ON dim.DIMENSION_ID = adim.DIMENSION_ID

      WHERE tpp.PARTITIONKEY = 25

      AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

      ORDER BY adim.BALANCE_COLUMN_NAME

       

      2016-10-04 16:59:38,639 DEBUG [AIF]:

                  UPDATE TDATASEG_T

       

                  SET AMOUNTX = AMOUNT

       

                    ,ACCOUNTX = NULL

      ,ENTITYX = NULL

      ,ICPX = NULL

      ,UD1X = NULL

      ,UD2X = NULL

      ,UD3X = NULL

      ,UD4X = NULL

      ,UD5X = NULL

      ,UD6X = NULL

      ,UD7X = NULL

      ,UD8X = NULL

      WHERE TDATASEG_T.LOADID = 786

                  AND TDATASEG_T.PARTITIONKEY = 25

      AND TDATASEG_T.CATKEY = 1

      AND TDATASEG_T.PERIODKEY = '2016-03-31'

       

      2016-10-04 17:00:03,579 INFO  [AIF]:       Generic Data Rows Imported from Source: 1487839

      2016-10-04 17:00:03,581 DEBUG [AIF]:

                    INSERT INTO AIF_APPL_LOAD_AUDIT (

      LOADID

      ,TARGET_APPLICATION_TYPE

      ,TARGET_APPLICATION_NAME

      ,PLAN_TYPE

      ,SOURCE_LEDGER_ID

      ,EPM_YEAR

      ,EPM_PERIOD

      ,SNAPSHOT_FLAG

      ,PARTITIONKEY

      ,CATKEY

      ,RULE_ID

      ,PERIODKEY

      ,EXPORT_TO_TARGET_FLAG

      )

      SELECT DISTINCT PROCESS_ID LOADID

      ,'HFM' TARGET_APPLICATION_TYPE

      ,'WP0002' TARGET_APPLICATION_NAME

      ,NULL PLAN_TYPE

      ,0 SOURCE_LEDGER_ID

      ,YEARTARGET EPM_YEAR

      ,PERIODTARGET EPM_PERIOD

      ,'Y' SNAPSHOT_FLAG

      ,25 PARTITIONKEY

      ,1 CATKEY

      ,43 RULE_ID

      ,PERIODKEY

      ,'N' EXPORT_TO_TARGET_FLAG

      FROM AIF_PROCESS_PERIODS

      WHERE PROCESS_ID = 786

      AND PERIODKEY = '2016-03-31'

       

      2016-10-04 17:00:03,792 INFO  [AIF]: Total Data Rows Imported from Source: 1487839

      2016-10-04 17:00:03,793 DEBUG [AIF]: CommData.updateTDATASEG_T - END

      2016-10-04 17:00:03,804 DEBUG [AIF]: Comm.doScriptInit - START

      2016-10-04 17:00:04,023 DEBUG [AIF]: fdmContext: {BATCHSCRIPTDIR=D:\Oracle\Middleware\user_projects\epmsystem1\FinancialDataQuality, INBOXDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002\inbox, LOCNAME=Actual_UDA_2, SOURCENAME=EBS_R12, APPID=6, SOURCEID=7, APPROOTDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002, IMPORTFORMAT=ACTUAL_UDA, SCRIPTSDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002\data\scripts, EPMORACLEHOME=D:\Oracle\Middleware\EPMSystem11R1, TARGETAPPTYPE=HFM, RULEID=43, CATNAME=Actual, EPMORACLEINSTANCEHOME=D:\Oracle\Middleware\user_projects\epmsystem1, LOADID=786, PERIODNAME=March-2016, IMPORTMODE=null, SOURCETYPE=UDA_ORCL, PERIODKEY=2016-03-31, EXPORTFLAG=N, TARGETAPPDB=null, TARGETAPPNAME=WP0002, LOCKEY=25, RULENAME=WGL_HYP_ACTUAL_UDA_2, OUTBOXDIR=\\AU2004SDE566\FDMEEDev\Apps\WP0002\outbox, MULTIPERIODLOAD=N, EXPORTMODE=null, CATKEY=1, USERNAME=L085637, FILEDIR=null, IMPORTFLAG=Y, USERLOCALE=null}

      2016-10-04 17:00:04,023 DEBUG [AIF]: The EpmOracleHome is set to: D:\Oracle\Middleware\EPMSystem11R1

      2016-10-04 17:00:04,024 DEBUG [AIF]: The EpmOracleInstance is set to: D:\Oracle\Middleware\user_projects\epmsystem1

      2016-10-04 17:00:04,024 DEBUG [AIF]: The JavaHome is set to: %EPM_ORACLE_HOME%/../jdk160_35

      2016-10-04 17:00:04,024 DEBUG [AIF]: The executeEventScript is set to: YES

      2016-10-04 17:00:04,025 DEBUG [AIF]: The OleDatabaseProvider is set to: ORAOLEDB.ORACLE

      2016-10-04 17:00:04,025 DEBUG [AIF]: The AppRootFolder is set to: \\AU2004SDE566\FDMEEDev\Apps\WP0002

      2016-10-04 17:00:04,025 DEBUG [AIF]: Comm.doScriptInit - END

      2016-10-04 17:00:04,025 DEBUG [AIF]: Comm.executeScript - START

      2016-10-04 17:00:04,027 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002/data/scripts/event/AftImport.py

      2016-10-04 17:00:04,034 DEBUG [AIF]: Comm.executeVBScript - START

      2016-10-04 17:00:04,034 DEBUG [AIF]: The WindowsTempFolder is set to: c:\windows\temp

      2016-10-04 17:00:04,035 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002\data\scripts\event\AftImport.vbs

      2016-10-04 17:00:04,036 DEBUG [AIF]: Comm.executeVBScript - END

      2016-10-04 17:00:04,036 DEBUG [AIF]: Comm.executeScript - END

      2016-10-04 17:00:04,036 DEBUG [AIF]: CommData.postImportData - END

      2016-10-04 17:00:04,368 DEBUG [AIF]: LogicGroup.createLogicAccounts - START

      2016-10-04 17:00:04,368 DEBUG [AIF]: Comm.executeScript - START

      2016-10-04 17:00:04,370 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002/data/scripts/event/BefProcLogicGrp.py

      2016-10-04 17:00:04,370 DEBUG [AIF]: Comm.executeVBScript - START

      2016-10-04 17:00:04,372 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002\data\scripts\event\BefProcLogicGrp.vbs

      2016-10-04 17:00:04,372 DEBUG [AIF]: Comm.executeVBScript - END

      2016-10-04 17:00:04,372 DEBUG [AIF]: Comm.executeScript - END

      2016-10-04 17:00:04,375 DEBUG [AIF]: CommLogicGroup.getRuleInfo - START

      2016-10-04 17:00:04,376 DEBUG [AIF]:

              select part.PARTLOGICGROUP, lgrp.CALCGROUPTYPE

      from AIF_PROCESSES prs, TPOVPARTITION part,TBHVLOGICGROUP lgrp

      where prs.PROCESS_ID=786 and prs.PARTITIONKEY=part.PARTITIONKEY

      and part.PARTLOGICGROUP=lgrp.CALCGROUPKEY

           

      2016-10-04 17:00:04,384 DEBUG [AIF]: CommLogicGroup.getRuleInfo - END

      2016-10-04 17:00:04,385 DEBUG [AIF]: Comm.executeScript - START

      2016-10-04 17:00:04,386 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002/data/scripts/event/AftProcLogicGrp.py

      2016-10-04 17:00:04,386 DEBUG [AIF]: Comm.executeVBScript - START

      2016-10-04 17:00:04,391 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002\data\scripts\event\AftProcLogicGrp.vbs

      2016-10-04 17:00:04,391 DEBUG [AIF]: Comm.executeVBScript - END

      2016-10-04 17:00:04,391 DEBUG [AIF]: Comm.executeScript - END

      2016-10-04 17:00:04,391 DEBUG [AIF]: LogicGroup.createLogicAccounts - END

      2016-10-04 17:00:04,430 DEBUG [AIF]: CommData.mapData - START

      2016-10-04 17:00:04,433 DEBUG [AIF]: CommData.insertTransProcessDetails - START

      2016-10-04 17:00:04,433 DEBUG [AIF]:

                INSERT INTO AIF_PROCESS_DETAILS (

      PROCESS_ID, ENTITY_TYPE, ENTITY_ID, ENTITY_NAME, ENTITY_NAME_ORDER, TARGET_TABLE_NAME, EXECUTION_START_TIME

      ,EXECUTION_END_TIME, RECORDS_PROCESSED, STATUS, LAST_UPDATED_BY, LAST_UPDATE_DATE

      )

      SELECT PROCESS_ID, ENTITY_TYPE, ENTITY_ID, ENTITY_NAME, ENTITY_NAME_ORDER, 'TDATASEG' TARGET_TABLE_NAME, CURRENT_TIMESTAMP EXECUTION_START_TIME

      ,NULL EXECUTION_END_TIME, 0 RECORDS_PROCESSED, 'PENDING' STATUS, 'L085637' LAST_UPDATED_BY, CURRENT_TIMESTAMP LAST_UPDATE_DATE

      FROM (

      SELECT PROCESS_ID, TRANS_ENTITY_TYPE ENTITY_TYPE, MIN(TRANS_ENTITY_ID) ENTITY_ID, TRANS_ENTITY_NAME ENTITY_NAME

      ,MIN(COALESCE(SOURCE_LEDGER_ID,0) * 100000000 + GL_EFFECTIVE_PERIOD_NUM) ENTITY_NAME_ORDER

      FROM AIF_PROCESS_PERIODS

      WHERE PROCESS_ID = 786

      AND PRIOR_PERIOD_FLAG = 'N'

      GROUP BY PROCESS_ID, TRANS_ENTITY_TYPE, TRANS_ENTITY_NAME

      ) q

      ORDER BY ENTITY_NAME_ORDER

       

      2016-10-04 17:00:04,442 DEBUG [AIF]: CommData.insertTransProcessDetails - END

      2016-10-04 17:00:04,447 DEBUG [AIF]:

              DELETE FROM TDATAMAP_T

      WHERE LOADID < 786

      AND EXISTS ( SELECT 1 FROM AIF_PROCESSES p WHERE p.RULE_ID = 43 AND p.PROCESS_ID = TDATAMAP_T.LOADID )

       

      2016-10-04 17:00:04,469 DEBUG [AIF]:

              DELETE FROM TDATASEG_T

      WHERE LOADID < 786

      AND EXISTS ( SELECT 1 FROM AIF_PROCESSES p WHERE p.RULE_ID = 43 AND p.PROCESS_ID = TDATASEG_T.LOADID )

       

      2016-10-04 17:00:04,515 DEBUG [AIF]:

              DELETE FROM AIF_PROCESS_PERIODS

      WHERE PROCESS_ID < 786

      AND EXISTS ( SELECT 1 FROM AIF_PROCESSES p WHERE p.RULE_ID = 43 AND p.PROCESS_ID = AIF_PROCESS_PERIODS.PROCESS_ID )

       

      2016-10-04 17:00:04,524 DEBUG [AIF]: CommMap.loadTDATAMAP_T - START

      2016-10-04 17:00:04,526 DEBUG [AIF]: CommData.getMapPartitionKeyandName - START

      2016-10-04 17:00:04,527 DEBUG [AIF]:

              SELECT COALESCE(part_parent.PARTITIONKEY, part.PARTITIONKEY) PARTITIONKEY, COALESCE(part_parent.PARTNAME, part.PARTNAME) PARTNAME

      FROM TPOVPARTITION part

      LEFT OUTER JOIN TPOVPARTITION part_parent

      ON part_parent.PARTITIONKEY = part.PARTPARENTKEY

      WHERE part.PARTITIONKEY = 25

       

      2016-10-04 17:00:04,530 DEBUG [AIF]: CommData.getMapPartitionKeyandName - END

      2016-10-04 17:00:04,530 DEBUG [AIF]:

              INSERT INTO TDATAMAP_T (

      LOADID, DATAKEY, PARTITIONKEY, DIMNAME, SRCKEY, SRCDESC, TARGKEY

      ,WHERECLAUSETYPE, WHERECLAUSEVALUE, CHANGESIGN, SEQUENCE, VBSCRIPT, TDATAMAPTYPE, SYSTEM_GENERATED_FLAG, RULE_ID

      )

      SELECT 786, DATAKEY, 25 PARTITIONKEY, DIMNAME, SRCKEY, SRCDESC, CASE WHEN TARGKEY = '<BLANK>' THEN ' ' ELSE TARGKEY END

      ,WHERECLAUSETYPE, WHERECLAUSEVALUE, CHANGESIGN, SEQUENCE, VBSCRIPT, TDATAMAPTYPE, SYSTEM_GENERATED_FLAG, RULE_ID

      FROM TDATAMAP tdm

      WHERE PARTITIONKEY = 25

      AND ( RULE_ID IS NULL OR RULE_ID = 43 )

      AND ( TDATAMAPTYPE = 'ERP'

      OR

      ( TDATAMAPTYPE = 'MULTIDIM'

      AND EXISTS ( SELECT 1

      FROM TDATAMAP multidim

      WHERE multidim.PARTITIONKEY = tdm.PARTITIONKEY

      AND multidim.TDATAMAPTYPE = 'ERP'

      AND multidim.DATAKEY = tdm.TARGKEY )

      )

      )

       

      2016-10-04 17:00:27,190 DEBUG [AIF]: Number of Rows inserted into TDATAMAP_T: 613673

      2016-10-04 17:00:27,297 DEBUG [AIF]: CommMap.updateTDATASEG_T_TDATASEGW - START

      2016-10-04 17:00:27,297 DEBUG [AIF]: Comm.executeScript - START

      2016-10-04 17:00:27,302 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002/data/scripts/event/BefProcMap.py

      2016-10-04 17:00:27,303 DEBUG [AIF]: Comm.executeVBScript - START

      2016-10-04 17:00:27,303 DEBUG [AIF]: The following script does not exist: \\AU2004SDE566\FDMEEDev\Apps\WP0002\data\scripts\event\BefProcMap.vbs

      2016-10-04 17:00:27,304 DEBUG [AIF]: Comm.executeVBScript - END

      2016-10-04 17:00:27,304 DEBUG [AIF]: Comm.executeScript - END

      2016-10-04 17:00:27,305 DEBUG [AIF]:

            SELECT DIMNAME, SRCKEY, TARGKEY, WHERECLAUSETYPE, WHERECLAUSEVALUE, CHANGESIGN, SEQUENCE, DATAKEY, MAPPING_TYPE

      ,CASE WHEN (RULE_ID IS NOT NULL) THEN 'Y' ELSE 'N' END IS_RULE_MAP

      FROM (

      SELECT DISTINCT tdm.DIMNAME, tdm.RULE_ID, NULL SRCKEY, NULL TARGKEY, tdm.WHERECLAUSETYPE, tdm.WHERECLAUSEVALUE, NULL CHANGESIGN

      ,1 SEQUENCE, COALESCE(tdm.SYSTEM_GENERATED_FLAG,'N') SYSTEM_GENERATED_FLAG, NULL DATAKEY

      ,CASE WHEN tdm.WHERECLAUSETYPE IS NULL THEN 1 ELSE 3 END MAPPING_TYPE, COALESCE(atad.CALC_SEQ,9999999999) CALC_SEQ

      FROM TDATAMAP_T tdm

      INNER JOIN AIF_TARGET_APPL_DIMENSIONS atad ON atad.BALANCE_COLUMN_NAME = tdm.DIMNAME

      WHERE tdm.LOADID = 786

      AND atad.APPLICATION_ID = 6

      AND tdm.PARTITIONKEY = 25

      AND tdm.TDATAMAPTYPE = 'ERP'

      AND (tdm.RULE_ID IS NULL OR tdm.RULE_ID = 43)

      AND tdm.WHERECLAUSETYPE IS NULL

      UNION ALL

      SELECT tdm.DIMNAME, tdm.RULE_ID, tdm.SRCKEY, tdm.TARGKEY, tdm.WHERECLAUSETYPE, tdm.WHERECLAUSEVALUE, tdm.CHANGESIGN

      ,CASE tpp.PARTSEQMAP

      WHEN 0 THEN CASE WHEN (tdm.WHERECLAUSETYPE = 'BETWEEN') THEN 2

      WHEN (tdm.WHERECLAUSETYPE = 'IN') THEN 3

      WHEN (tdm.WHERECLAUSETYPE = 'MULTIDIM') THEN 4

      WHEN (tdm.WHERECLAUSETYPE = 'LIKE') THEN 5

      ELSE 0 END   

                       ELSE tdm.SEQUENCE

      END SEQUENCE

      ,COALESCE(tdm.SYSTEM_GENERATED_FLAG,'N') SYSTEM_GENERATED_FLAG, tdm.DATAKEY

      ,CASE WHEN tdm.WHERECLAUSETYPE IS NULL THEN 1 ELSE 3 END MAPPING_TYPE, COALESCE(atad.CALC_SEQ,9999999999) CALC_SEQ

      FROM TDATAMAP_T tdm

      INNER JOIN TPOVPARTITION tpp ON tpp.PARTITIONKEY = tdm.PARTITIONKEY

      INNER JOIN AIF_TARGET_APPL_DIMENSIONS atad ON atad.BALANCE_COLUMN_NAME = tdm.DIMNAME

      WHERE tdm.LOADID = 786

      AND atad.APPLICATION_ID = 6

      AND tdm.PARTITIONKEY = 25

      AND tdm.TDATAMAPTYPE = 'ERP'

      AND (tdm.RULE_ID IS NULL OR tdm.RULE_ID = 43)

      AND tdm.WHERECLAUSETYPE IN ('BETWEEN','IN','MULTIDIM','LIKE')

      ) q

      ORDER BY CALC_SEQ, DIMNAME, SEQUENCE, RULE_ID, SYSTEM_GENERATED_FLAG, SRCKEY

       

      2016-10-04 17:00:30,492 INFO  [AIF]:

      Map Data for Period 'March-2016'

      2016-10-04 17:00:30,494 DEBUG [AIF]: Comm.updateProcessDetail - START

      2016-10-04 17:00:30,496 DEBUG [AIF]:

          UPDATE AIF_PROCESS_DETAILS

      SET STATUS = 'RUNNING'

      ,RECORDS_PROCESSED = CASE WHEN RECORDS_PROCESSED IS NULL THEN 0 ELSE RECORDS_PROCESSED END + NULL

      ,EXECUTION_END_TIME = CURRENT_TIMESTAMP

      ,LAST_UPDATED_BY = CASE WHEN ('L085637' IS NULL) THEN LAST_UPDATED_BY ELSE 'L085637' END

      ,LAST_UPDATE_DATE = CURRENT_TIMESTAMP

      WHERE PROCESS_ID = 786 AND ENTITY_TYPE = 'PROCESS_BAL_TRANS' AND ENTITY_NAME = 'March-2016'

       

      2016-10-04 17:00:30,505 DEBUG [AIF]: Comm.updateProcessDetail - END

      2016-10-04 17:00:30,508 DEBUG [AIF]:

            SELECT COUNT(*) ROW_COUNT

      FROM TDATASEG_T

      WHERE LOADID = 786

            AND (PARTITIONKEY = 25 AND CATKEY = 1 AND PERIODKEY = '2016-03-31' AND RULE_ID = 43 AND VALID_FLAG = 'Y')

       

      2016-10-04 17:00:33,012 DEBUG [AIF]:

                    DELETE FROM TDATASEG_T

      WHERE LOADID = 786

      AND PARTITIONKEY = 25

      AND CATKEY = 1

      AND PERIODKEY = '2016-03-31'

      AND AMOUNT = 0

       

      2016-10-04 17:00:41,205 INFO  [AIF]:

      Processing Mappings for Column 'ACCOUNT'

      2016-10-04 17:00:41,206 DEBUG [AIF]:

                    UPDATE TDATASEG_T

      SET (ACCOUNTX ,ACCOUNTR, AMOUNTX, CHANGESIGN, VALID_FLAG) = (

      SELECT tdm.TARGKEY

      ,tdm.DATAKEY

      ,AMOUNTX * CASE tdm.CHANGESIGN WHEN 1 THEN -1 ELSE 1 END

      ,CASE tdm.CHANGESIGN

      WHEN 1 THEN CASE TDATASEG_T.CHANGESIGN

      WHEN 1 THEN 0

      WHEN 0 THEN 1

      ELSE TDATASEG_T.CHANGESIGN

      END

      ELSE TDATASEG_T.CHANGESIGN

      END

      ,CASE tdm.TARGKEY WHEN 'IGNORE' THEN 'I' ELSE TDATASEG_T.VALID_FLAG END

                                 FROM TDATAMAP_T tdm

      WHERE tdm.LOADID = TDATASEG_T.LOADID

      AND tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY

      AND tdm.DIMNAME = 'ACCOUNT'

      AND tdm.WHERECLAUSETYPE IS NULL

      AND tdm.SRCKEY = TDATASEG_T.ACCOUNT

      AND tdm.RULE_ID IS NULL

      AND tdm.TDATAMAPTYPE <> 'MULTIDIM'

                    )

      ,ACCOUNTF = 1

      WHERE LOADID = 786

      AND PARTITIONKEY = 25

      AND CATKEY = 1

      AND (ACCOUNTX IS NULL OR ACCOUNTX = '')

      AND EXISTS ( SELECT 1

                                 FROM TDATAMAP_T tdm

      WHERE tdm.LOADID = TDATASEG_T.LOADID

      AND tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY

      AND tdm.DIMNAME = 'ACCOUNT'

      AND tdm.WHERECLAUSETYPE IS NULL

      AND tdm.SRCKEY = TDATASEG_T.ACCOUNT

      AND tdm.RULE_ID IS NULL

      AND tdm.TDATAMAPTYPE <> 'MULTIDIM' )

      AND PERIODKEY = '2016-03-31'

       

      ***Moderator action (Timo): removed the call out to a specific user. User please don't call specific users as long as they don't have participated in the thread as this is rude to other users.***

      Thanks & Regards,

      Caniut