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