Hello Experts!!!
I am trying to load journal file via FDMEE. the data is successfully imported and Validated. But i am not able to export it to HFM. While exporting following appears resulting in export failure:-
2015-10-12 12:48:03,318 INFO [AIF]: FDMEE Process Start, Process ID: 358
2015-10-12 12:48:03,318 INFO [AIF]: FDMEE Logging Level: 5
2015-10-12 12:48:03,318 INFO [AIF]: FDMEE Log File: D:\FDMEE\outbox\logs\APP2_358.log
2015-10-12 12:48:03,319 INFO [AIF]: User:admin
2015-10-12 12:48:03,319 INFO [AIF]: Location:APP2jr_loc (Partitionkey:27)
2015-10-12 12:48:03,319 INFO [AIF]: Period Name:JUN (Period Key:6/30/03 12:00 AM)
2015-10-12 12:48:03,319 INFO [AIF]: Category Name:Actual (Category key:1)
2015-10-12 12:48:03,320 INFO [AIF]: Rule Name:APP2jr_dlr (Rule ID:107)
2015-10-12 12:48:06,939 INFO [AIF]: FDM Version: 11.1.2.4.000
2015-10-12 12:48:06,940 INFO [AIF]: Jython Version: 2.5.1 (Release_2_5_1:6813, Sep 26 2009, 13:47:54)
[Oracle JRockit(R) (Oracle Corporation)]
2015-10-12 12:48:06,940 INFO [AIF]: Java Platform: java1.6.0_37
2015-10-12 12:48:06,940 INFO [AIF]: Log File Encoding: UTF-8
2015-10-12 12:48:10,246 INFO [AIF]: -------START IMPORT STEP-------
2015-10-12 12:48:10,309 INFO [AIF]: -------END IMPORT STEP-------
2015-10-12 12:48:10,379 INFO [AIF]: -------START EXPORT STEP-------
2015-10-12 12:48:10,409 DEBUG [AIF]: CommData.exportData - START
2015-10-12 12:48:10,419 DEBUG [AIF]: Comm.doScriptInit - START
2015-10-12 12:48:10,611 DEBUG [AIF]: fdmContext: {BATCHSCRIPTDIR=D:\Oracle\middleware\user_projects\epmsystem1\FinancialDataQuality, INBOXDIR=D:\FDMEE\inbox, LOCNAME=APP2jr_loc, SOURCENAME=File, APPID=7, SOURCEID=1, APPROOTDIR=D:\FDMEE, IMPORTFORMAT=APP2jr_imf, SCRIPTSDIR=D:\FDMEE\data\scripts, EPMORACLEHOME=D:\Oracle\middleware\EPMSystem11R1, TARGETAPPTYPE=HFM, RULEID=107, CATNAME=Actual, EPMORACLEINSTANCEHOME=D:\Oracle\middleware\user_projects\epmsystem1, LOADID=358, PERIODNAME=JUN, IMPORTMODE=null, SOURCETYPE=FILE, PERIODKEY=2003-06-30, EXPORTFLAG=Y, TARGETAPPDB=null, TARGETAPPNAME=APP2, FILENAME=null, LOCKEY=27, RULENAME=APP2jr_dlr, OUTBOXDIR=D:\FDMEE\outbox, MULTIPERIODLOAD=N, EXPORTMODE=Replace, CATKEY=1, USERNAME=admin, FILEDIR=acle\middleware\user_projects\domains\EPMSystem\FDMEE, IMPORTFLAG=N, USERLOCALE=null}
2015-10-12 12:48:10,611 DEBUG [AIF]: The EpmOracleHome is set to: D:\Oracle\middleware\EPMSystem11R1
2015-10-12 12:48:10,611 DEBUG [AIF]: The EpmOracleInstance is set to: D:\Oracle\middleware\user_projects\epmsystem1
2015-10-12 12:48:10,611 DEBUG [AIF]: The JavaHome is set to: %EPM_ORACLE_HOME%/../jdk160_35
2015-10-12 12:48:10,611 DEBUG [AIF]: The executeEventScript is set to: YES
2015-10-12 12:48:10,612 DEBUG [AIF]: The OleDatabaseProvider is set to: ORAOLEDB.ORACLE
2015-10-12 12:48:10,612 DEBUG [AIF]: The AppRootFolder is set to: D:\FDMEE
2015-10-12 12:48:10,612 DEBUG [AIF]: Comm.doScriptInit - END
2015-10-12 12:48:10,612 DEBUG [AIF]: Comm.executeScript - START
2015-10-12 12:48:10,612 DEBUG [AIF]: The following script does not exist: D:\FDMEE/data/scripts/event/BefExportToDat.py
2015-10-12 12:48:10,617 DEBUG [AIF]: Comm.executeVBScript - START
2015-10-12 12:48:10,617 DEBUG [AIF]: The WindowsTempFolder is set to: c:\windows\temp
2015-10-12 12:48:10,617 DEBUG [AIF]: The following script does not exist: D:\FDMEE\data\scripts\event\BefExportToDat.vbs
2015-10-12 12:48:10,617 DEBUG [AIF]: Comm.executeVBScript - END
2015-10-12 12:48:10,617 DEBUG [AIF]: Comm.executeScript - END
2015-10-12 12:48:10,626 DEBUG [AIF]: CommData.getRuleInfo - START
2015-10-12 12:48:10,629 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 = 358
2015-10-12 12:48:10,635 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 = 7
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 = 27
AND adim.BALANCE_COLUMN_NAME IS NOT NULL
AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'
ORDER BY adim.BALANCE_COLUMN_NAME
2015-10-12 12:48:10,639 DEBUG [AIF]: {'APPLICATION_ID': 7L, 'IMPORT_FROM_SOURCE_FLAG': u'N', 'PLAN_TYPE': None, 'RULE_NAME': u'APP2jr_dlr', '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'DEFAULT', 'SOURCE_SYSTEM_TYPE': u'FILE', 'CHECK_FLAG': u'Y', 'LEDGER_GROUP': None, 'TARGET_APPLICATION_NAME': u'APP2', 'RECALCULATE_FLAG': u'N', 'SOURCE_SYSTEM_ID': 1L, 'TEMP_DATA_TABLE_NAME': 'TDATASEG_T', 'KK_FLAG': u'N', 'IMPGROUPKEY': u'APP2jr_imf', 'AMOUNT_TYPE': u'MONETARY', 'DATA_TABLE_NAME': 'TDATASEG', 'EXPORT_TO_TARGET_FLAG': u'Y', 'JOURNAL_FLAG': None, 'SOURCE_APPLICATION_ID': None, 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'ICP', u'UD1', u'UD2'], 'FCI_FLAG': 'N', 'IMPSOURCECOAID': 0L, 'TDATAMAPTYPE': 'ERP', 'LAST_UPDATED_BY': u'admin', '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': 75L}, 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': 72L}, 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': 74L}, 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': 70L}, 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': 73L}}, 'TARGET_APPLICATION_TYPE': u'HFM', 'PARTITIONKEY': 27L, 'PARTVALGROUP': u'[NONE]', 'LEDGER_GROUP_CODE': u'NONE', 'INCLUDE_ZERO_BALANCE_FLAG': None, 'EXECUTION_MODE': None, 'PLAN_NUMBER': 0L, 'MULTI_PERIOD_FILE_FLAG': u'N', 'PS_LEDGER': None, 'BALANCE_SELECTION': u'FUNCTIONAL', 'IMPGROUPFILETYPE': u'DELIMITED', 'BALANCE_AMOUNT_IS': u'PERIODIC', 'RULE_ID': 107L, 'BALANCE_AMOUNT_BS': u'YTD', 'CURRENCY_CODE': None, 'SOURCE_ADAPTER_FLAG': u'N', 'BALANCE_METHOD_CODE': None, 'SIGNAGE_METHOD': u'ABSOLUTE', 'WEB_SERVICE_URL': u'http://CSVHYP003:6550/aif', 'DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'PARTTARGETAPPLICATIONID': 7L, 'IMPTARGETSOURCESYSTEMID': 0L}
2015-10-12 12:48:10,639 DEBUG [AIF]: CommData.getRuleInfo - END
2015-10-12 12:48:10,645 DEBUG [AIF]: CommData.insertFilePeriods - START
2015-10-12 12:48:10,648 DEBUG [AIF]: doAppPeriodMappingsExist - APP2: Y
2015-10-12 12:48:10,649 DEBUG [AIF]:
select PERIODKEY,PERIODDESC,YEARTARGET,PERIODTARGET,FILE_NAME_STATIC,FILE_NAME_SUFFIX_TYPE,FILE_NAME_DATE_FORMAT,GL_EFFECTIVE_PERIOD_NUM
from (
SELECT pp.PERIODKEY
,pp.PERIODDESC
,pp.YEARTARGET YEARTARGET
,pp.PERIODTARGET PERIODTARGET
,brl.FILE_NAME_STATIC
,brl.FILE_NAME_SUFFIX_TYPE
,brl.FILE_NAME_DATE_FORMAT
,coalesce(brp.parameter_id,0) GL_EFFECTIVE_PERIOD_NUM
FROM AIF_BALANCE_RULES br, AIF_BAL_RULE_LOADS brl
INNER JOIN TPOVCATEGORY pc
ON pc.CATKEY = brl.CATKEY
INNER JOIN TPOVPERIODADAPTOR_FLAT_V pp
ON pp.PERIODFREQ = pc.CATFREQ
AND pp.INTSYSTEMKEY = 'APP2'
INNER JOIN AIF_BAL_RULE_LOAD_PARAMS brp
ON brp.loadid = brl.loadid
AND to_date(brp.parameter_value,'yyyy/MM/dd') = pp.periodkey
WHERE brl.LOADID = 358
AND br.rule_id = brl.rule_id
AND br.MULTI_PERIOD_FILE_FLAG in ('T','H')
AND brp.parameter_name like 'AMOUNT%'
UNION
SELECT pp.PERIODKEY
,pp.PERIODDESC
,pp.YEARTARGET YEARTARGET
,pp.PERIODTARGET PERIODTARGET
,brl.FILE_NAME_STATIC
,brl.FILE_NAME_SUFFIX_TYPE
,brl.FILE_NAME_DATE_FORMAT
,0 GL_EFFECTIVE_PERIOD_NUM
FROM AIF_BALANCE_RULES br, AIF_BAL_RULE_LOADS brl
INNER JOIN TPOVCATEGORY pc
ON pc.CATKEY = brl.CATKEY
INNER JOIN TPOVPERIODADAPTOR_FLAT_V pp
ON pp.PERIODFREQ = pc.CATFREQ
AND pp.INTSYSTEMKEY = 'APP2'
AND pp.PERIODKEY >= brl.START_PERIODKEY
AND pp.PERIODKEY <= brl.END_PERIODKEY
WHERE brl.LOADID = 358
AND br.rule_id = brl.rule_id
AND br.MULTI_PERIOD_FILE_FLAG in( 'Y', 'X','E')
) qrslt
UNION
SELECT pp.PERIODKEY
,pp.PERIODDESC
,pp.YEARTARGET YEARTARGET
,pp.PERIODTARGET PERIODTARGET
,brl.FILE_NAME_STATIC
,brl.FILE_NAME_SUFFIX_TYPE
,brl.FILE_NAME_DATE_FORMAT
, 0 GL_EFFECTIVE_PERIOD_NUM
FROM AIF_BALANCE_RULES br, AIF_BAL_RULE_LOADS brl
INNER JOIN TPOVCATEGORY pc
ON pc.CATKEY = brl.CATKEY
INNER JOIN TPOVPERIODADAPTOR_FLAT_V pp
ON pp.PERIODFREQ = pc.CATFREQ
AND pp.INTSYSTEMKEY = 'APP2'
AND pp.PERIODKEY >= brl.START_PERIODKEY
AND pp.PERIODKEY <= brl.END_PERIODKEY
WHERE brl.LOADID = 358
AND (br.MULTI_PERIOD_FILE_FLAG is null or br.MULTI_PERIOD_FILE_FLAG = 'N' )
AND br.rule_id = brl.rule_id
ORDER BY PERIODKEY
2015-10-12 12:48:10,650 INFO [AIF]: File Name: dummy.txt
2015-10-12 12:48:10,650 DEBUG [AIF]:
INSERT INTO AIF_PROCESS_PERIODS (
PROCESS_ID
,PERIODKEY
,ADJUSTMENT_PERIOD_FLAG
,GL_PERIOD_YEAR
,GL_PERIOD_CODE
,GL_PERIOD_NAME
,GL_PERIOD_NUM
,GL_EFFECTIVE_PERIOD_NUM
,YEARTARGET
,PERIODTARGET
,IMP_ENTITY_TYPE
,IMP_ENTITY_NAME
,TRANS_ENTITY_TYPE
,TRANS_ENTITY_NAME
,PRIOR_PERIOD_FLAG
) VALUES (358,'2003-06-30','N',0,'0' ,'0',0,1,'2003','JUN', 'PROCESS_BAL_IMP_FILE','dummy.txt','PROCESS_BAL_TRANS','JUN','N' )
2015-10-12 12:48:10,653 DEBUG [AIF]: CommData.insertFilePeriods - END
2015-10-12 12:48:10,658 DEBUG [AIF]: CommData.moveData - START
2015-10-12 12:48:10,662 DEBUG [AIF]: CommData.getPovList - START
2015-10-12 12:48:10,663 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 TPOVPERIODADAPTOR pp
ON pp.PERIODKEY = pprd.PERIODKEY
AND pp.INTSYSTEMKEY = 'APP2'
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 = 358
ORDER BY brl.PARTITIONKEY, brl.CATKEY, pprd.PERIODKEY, brl.RULE_ID
2015-10-12 12:48:10,664 DEBUG [AIF]: CommData.getPovList - END
2015-10-12 12:48:10,665 INFO [AIF]:
Move Data for Period 'JUN'
2015-10-12 12:48:10,666 DEBUG [AIF]:
UPDATE TDATASEG
SET LOADID = 358
WHERE PARTITIONKEY = 27
AND CATKEY = 1
AND RULE_ID = 107
AND LOADID < 358
AND PERIODKEY = '2003-06-30'
2015-10-12 12:48:10,680 DEBUG [AIF]: Number of Rows updated on TDATASEG: 4
2015-10-12 12:48:10,680 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, SEGMENT_FILTER_FLAG, PARTITIONKEY, CATKEY, RULE_ID, PERIODKEY, EXPORT_TO_TARGET_FLAG
)
SELECT DISTINCT
358, TARGET_APPLICATION_TYPE, TARGET_APPLICATION_NAME, PLAN_TYPE, SOURCE_LEDGER_ID, EPM_YEAR, EPM_PERIOD,
SNAPSHOT_FLAG, SEGMENT_FILTER_FLAG, PARTITIONKEY, CATKEY, RULE_ID, PERIODKEY, 'Y'
FROM AIF_APPL_LOAD_AUDIT
WHERE PARTITIONKEY = 27 AND CATKEY = 1 AND RULE_ID = 107 AND LOADID < 358
AND PERIODKEY = '2003-06-30'
2015-10-12 12:48:10,681 DEBUG [AIF]: Number of Rows inserted into AIF_APPL_LOAD_AUDIT: 1
2015-10-12 12:48:10,682 DEBUG [AIF]: CommData.moveData - END
2015-10-12 12:48:10,692 DEBUG [AIF]: CommData.updateWorkflow - START
2015-10-12 12:48:10,692 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 = 27
AND tlp.CATKEY = 1
AND tlp.PERIODKEY = '2003-06-30'
AND tlp.RULE_ID = 107
AND tlps.PROCESSSTATUSKEY = tlp.PROCESSSTATUS
2015-10-12 12:48:10,693 DEBUG [AIF]:
UPDATE TLOGPROCESS
SET PROCESSENDTIME = CURRENT_TIMESTAMP
,PROCESSSTATUS = 20
,PROCESSEXP = 0
,PROCESSENTLOAD = 0
,PROCESSENTVAL = 0
,PROCESSEXPNOTE = NULL
,PROCESSENTLOADNOTE = NULL
,PROCESSENTVALNOTE = NULL
WHERE PARTITIONKEY = 27 AND CATKEY = 1 AND PERIODKEY = '2003-06-30' AND RULE_ID = 107
2015-10-12 12:48:10,695 DEBUG [AIF]: CommData.updateWorkflow - END
2015-10-12 12:48:10,698 DEBUG [AIF]: CommData.purgeInvalidRecordsTDATASEG - START
2015-10-12 12:48:10,700 DEBUG [AIF]:
DELETE FROM TDATASEG
WHERE LOADID = 358
AND (
PARTITIONKEY = 27
AND CATKEY = 1
AND PERIODKEY = '2003-06-30'
AND RULE_ID = 107
AND VALID_FLAG = 'N'
)
2015-10-12 12:48:10,700 DEBUG [AIF]: Number of Rows deleted from TDATASEG: 0
2015-10-12 12:48:10,700 DEBUG [AIF]: CommData.purgeInvalidRecordsTDATASEG - END
2015-10-12 12:48:10,701 DEBUG [AIF]: CommData.updateAppLoadAudit - START
2015-10-12 12:48:10,701 DEBUG [AIF]:
UPDATE AIF_APPL_LOAD_AUDIT
SET EXPORT_TO_TARGET_FLAG = 'Y'
WHERE LOADID = 358
AND PARTITIONKEY = 27
AND CATKEY = 1
AND PERIODKEY= '2003-06-30'
AND RULE_ID = 107
2015-10-12 12:48:10,704 DEBUG [AIF]: Number of Rows updated on AIF_APPL_LOAD_AUDIT: 1
2015-10-12 12:48:10,704 DEBUG [AIF]: CommData.updateAppLoadAudit - END
2015-10-12 12:48:10,705 DEBUG [AIF]: CommData.updateWorkflow - START
2015-10-12 12:48:10,705 DEBUG [AIF]:
UPDATE TLOGPROCESS
SET PROCESSENDTIME = CURRENT_TIMESTAMP
,PROCESSSTATUS = 21
,PROCESSEXP = 1
,PROCESSEXPNOTE = 'AIF_WF_EXP_EXPORT_OK_TXT'
WHERE PARTITIONKEY = 27 AND CATKEY = 1 AND PERIODKEY = '2003-06-30' AND RULE_ID = 107
2015-10-12 12:48:10,708 DEBUG [AIF]: CommData.updateWorkflow - END
2015-10-12 12:48:10,710 DEBUG [AIF]: Comm.executeJythonScript - START
2015-10-12 12:48:10,711 INFO [AIF]: Executing the following script: D:\Oracle\middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_EXPORT.py
2015-10-12 12:48:10,770 DEBUG [AIF]: ************************************************************************************
2015-10-12 12:48:10,770 DEBUG [AIF]: * HFM_EXPORT.py Started for LoadID: 358
2015-10-12 12:48:10,770 DEBUG [AIF]: ************************************************************************************
2015-10-12 12:48:10,796 DEBUG [AIF]: User Locale is not set. Defaulting to: en_US
2015-10-12 12:48:11,004 INFO [AIF]: Cluster name: CSVHYP003
2015-10-12 12:48:11,006 DEBUG [AIF]: Global User ID:
2015-10-12 12:48:17,819 DEBUG [AIF]: Session ID = 3ade7fe5-c0c2-486e-9d06-e02dea91d3ed
2015-10-12 12:48:17,822 DEBUG [AIF]: Connected to: APP2
2015-10-12 12:48:17,827 DEBUG [AIF]: ***Export data as data and journals as journals***
2015-10-12 12:48:17,911 DEBUG [AIF]: HFM Export Complete.
2015-10-12 12:48:17,911 DEBUG [AIF]: ************************************************************************************
2015-10-12 12:48:17,911 DEBUG [AIF]: Comm.executeJythonScript - END
2015-10-12 12:48:17,911 DEBUG [AIF]: Comm.executeScript - START
2015-10-12 12:48:17,912 DEBUG [AIF]: The following script does not exist: D:\FDMEE/data/scripts/event/AftExportToDat.py
2015-10-12 12:48:17,912 DEBUG [AIF]: Comm.executeVBScript - START
2015-10-12 12:48:17,913 DEBUG [AIF]: The following script does not exist: D:\FDMEE\data\scripts\event\AftExportToDat.vbs
2015-10-12 12:48:17,913 DEBUG [AIF]: Comm.executeVBScript - END
2015-10-12 12:48:17,913 DEBUG [AIF]: Comm.executeScript - END
2015-10-12 12:48:17,913 DEBUG [AIF]: CommData.exportData - END
2015-10-12 12:48:18,053 DEBUG [AIF]: HfmData.loadData - START
2015-10-12 12:48:18,053 DEBUG [AIF]: Comm.executeScript - START
2015-10-12 12:48:18,054 DEBUG [AIF]: The following script does not exist: D:\FDMEE/data/scripts/event/BefLoad.py
2015-10-12 12:48:18,054 DEBUG [AIF]: Comm.executeVBScript - START
2015-10-12 12:48:18,055 DEBUG [AIF]: The following script does not exist: D:\FDMEE\data\scripts\event\BefLoad.vbs
2015-10-12 12:48:18,055 DEBUG [AIF]: Comm.executeVBScript - END
2015-10-12 12:48:18,055 DEBUG [AIF]: Comm.executeScript - END
2015-10-12 12:48:18,055 DEBUG [AIF]: CommData.getRuleInfo - START
2015-10-12 12:48:18,055 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 = 358
2015-10-12 12:48:18,058 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 = 7
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 = 27
AND adim.BALANCE_COLUMN_NAME IS NOT NULL
AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'
ORDER BY adim.BALANCE_COLUMN_NAME
2015-10-12 12:48:18,063 DEBUG [AIF]: {'APPLICATION_ID': 7L, 'IMPORT_FROM_SOURCE_FLAG': u'N', 'PLAN_TYPE': None, 'RULE_NAME': u'APP2jr_dlr', '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'DEFAULT', 'SOURCE_SYSTEM_TYPE': u'FILE', 'CHECK_FLAG': u'Y', 'LEDGER_GROUP': None, 'TARGET_APPLICATION_NAME': u'APP2', 'RECALCULATE_FLAG': u'N', 'SOURCE_SYSTEM_ID': 1L, 'TEMP_DATA_TABLE_NAME': 'TDATASEG_T', 'KK_FLAG': u'N', 'IMPGROUPKEY': u'APP2jr_imf', 'AMOUNT_TYPE': u'MONETARY', 'DATA_TABLE_NAME': 'TDATASEG', 'EXPORT_TO_TARGET_FLAG': u'Y', 'JOURNAL_FLAG': None, 'SOURCE_APPLICATION_ID': None, 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'ICP', u'UD1', u'UD2'], 'FCI_FLAG': 'N', 'IMPSOURCECOAID': 0L, 'TDATAMAPTYPE': 'ERP', 'LAST_UPDATED_BY': u'admin', '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': 75L}, 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': 72L}, 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': 74L}, 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': 70L}, 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': 73L}}, 'TARGET_APPLICATION_TYPE': u'HFM', 'PARTITIONKEY': 27L, 'PARTVALGROUP': u'[NONE]', 'LEDGER_GROUP_CODE': u'NONE', 'INCLUDE_ZERO_BALANCE_FLAG': None, 'EXECUTION_MODE': None, 'PLAN_NUMBER': 0L, 'MULTI_PERIOD_FILE_FLAG': u'N', 'PS_LEDGER': None, 'BALANCE_SELECTION': u'FUNCTIONAL', 'IMPGROUPFILETYPE': u'DELIMITED', 'BALANCE_AMOUNT_IS': u'PERIODIC', 'RULE_ID': 107L, 'BALANCE_AMOUNT_BS': u'YTD', 'CURRENCY_CODE': None, 'SOURCE_ADAPTER_FLAG': u'N', 'BALANCE_METHOD_CODE': None, 'SIGNAGE_METHOD': u'ABSOLUTE', 'WEB_SERVICE_URL': u'http://CSVHYP003:6550/aif', 'DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'PARTTARGETAPPLICATIONID': 7L, 'IMPTARGETSOURCESYSTEMID': 0L}
2015-10-12 12:48:18,064 DEBUG [AIF]: CommData.getRuleInfo - END
2015-10-12 12:48:18,064 DEBUG [AIF]: CommData.getPovList - START
2015-10-12 12:48:18,064 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 TPOVPERIODADAPTOR pp
ON pp.PERIODKEY = pprd.PERIODKEY
AND pp.INTSYSTEMKEY = 'APP2'
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 = 358
ORDER BY brl.PARTITIONKEY, brl.CATKEY, pprd.PERIODKEY, brl.RULE_ID
2015-10-12 12:48:18,066 DEBUG [AIF]: CommData.getPovList - END
2015-10-12 12:48:18,066 DEBUG [AIF]: CommData.updateWorkflow - START
2015-10-12 12:48:18,066 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 = 27
AND tlp.CATKEY = 1
AND tlp.PERIODKEY = '2003-06-30'
AND tlp.RULE_ID = 107
AND tlps.PROCESSSTATUSKEY = tlp.PROCESSSTATUS
2015-10-12 12:48:18,067 DEBUG [AIF]:
UPDATE TLOGPROCESS
SET PROCESSENDTIME = CURRENT_TIMESTAMP
,PROCESSSTATUS = 30
,PROCESSENTLOAD = 0
,PROCESSENTVAL = 0
,PROCESSENTLOADNOTE = NULL
,PROCESSENTVALNOTE = NULL
WHERE PARTITIONKEY = 27 AND CATKEY = 1 AND PERIODKEY = '2003-06-30' AND RULE_ID = 107
2015-10-12 12:48:18,071 DEBUG [AIF]: CommData.updateWorkflow - END
2015-10-12 12:48:18,071 DEBUG [AIF]: Comm.executeJythonScript - START
2015-10-12 12:48:18,072 INFO [AIF]: Executing the following script: D:\Oracle\middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py
2015-10-12 12:48:18,186 INFO [AIF]: ************************************************************************************
2015-10-12 12:48:18,186 INFO [AIF]: * HFM_LOAD.py Started for LoadID: 358
2015-10-12 12:48:18,186 INFO [AIF]: ************************************************************************************
2015-10-12 12:48:18,186 DEBUG [AIF]: User Locale is not set. Defaulting to: en_US
2015-10-12 12:48:18,359 INFO [AIF]: Cluster name: CSVHYP003
2015-10-12 12:48:18,361 DEBUG [AIF]: Global User ID:
2015-10-12 12:48:18,467 DEBUG [AIF]: Session ID = 9a4b3e91-6ea2-40a2-8550-1fdc5d7eff01
2015-10-12 12:48:18,470 INFO [AIF]: Connected to: APP2
2015-10-12 12:48:18,598 ERROR [AIF]: The script has failed to execute:
2015-10-12 12:48:18,600 FATAL [AIF]: Error in Comm.executeJythonScript
Traceback (most recent call last):
File "<string>", line 564, in executeJythonScript
File "D:\Oracle\middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 1036, in <module>
blnLoadStatus = exportJournalFile(BigDecimal(str(fdmContext["LOCKEY"])),hfmAdapter,fdmAPI,fdmContext,strJournalFile,TargetOptions.get(20),TargetOptions.get(23),TargetOptions.get(21))
File "D:\Oracle\middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 651, in exportJournalFile
rsItem = fdmAPI.executeQuery(strTB, None)
SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis
2015-10-12 12:48:18,601 FATAL [AIF]: Error in HfmData.loadData
Traceback (most recent call last):
File "<string>", line 42, in loadData
File "<string>", line 587, in executeVBScript
File "<string>", line 564, in executeJythonScript
File "D:\Oracle\middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 1036, in <module>
blnLoadStatus = exportJournalFile(BigDecimal(str(fdmContext["LOCKEY"])),hfmAdapter,fdmAPI,fdmContext,strJournalFile,TargetOptions.get(20),TargetOptions.get(23),TargetOptions.get(21))
File "D:\Oracle\middleware\EPMSystem11R1/products/FinancialDataQuality/bin/HFM_LOAD.py", line 651, in exportJournalFile
rsItem = fdmAPI.executeQuery(strTB, None)
SQLSyntaxErrorException: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis
2015-10-12 12:48:18,601 DEBUG [AIF]: CommData.updateWorkflow - START
2015-10-12 12:48:18,601 DEBUG [AIF]:
UPDATE TLOGPROCESS
SET PROCESSENDTIME = CURRENT_TIMESTAMP
,PROCESSSTATUS = 32
,PROCESSENTLOAD = 0
,PROCESSENTLOADNOTE = 'AIF_WF_LD_LOAD_ERR'
WHERE PARTITIONKEY = 27 AND CATKEY = 1 AND PERIODKEY = '2003-06-30' AND RULE_ID = 107
2015-10-12 12:48:18,604 DEBUG [AIF]: CommData.updateWorkflow - END
2015-10-12 12:48:18,604 DEBUG [AIF]: Comm.executeScript - START
2015-10-12 12:48:18,604 DEBUG [AIF]: The following script does not exist: D:\FDMEE/data/scripts/event/AftLoad.py
2015-10-12 12:48:18,604 DEBUG [AIF]: Comm.executeVBScript - START
2015-10-12 12:48:18,605 DEBUG [AIF]: The following script does not exist: D:\FDMEE\data\scripts\event\AftLoad.vbs
2015-10-12 12:48:18,605 DEBUG [AIF]: Comm.executeVBScript - END
2015-10-12 12:48:18,605 DEBUG [AIF]: Comm.executeScript - END
2015-10-12 12:48:18,605 DEBUG [AIF]:
SELECT COALESCE(usr.PROFILE_OPTION_VALUE, app.PROFILE_OPTION_VALUE, site.PROFILE_OPTION_VALUE) PROFILE_OPTION_VALUE
FROM AIF_PROFILE_OPTIONS po
LEFT OUTER JOIN AIF_PROFILE_OPTION_VALUES site
ON site.PROFILE_OPTION_NAME = po.PROFILE_OPTION_NAME
AND site.LEVEL_ID = 1000
AND site.LEVEL_VALUE = 0
AND site.LEVEL_ID <= po.MAX_LEVEL_ID
LEFT OUTER JOIN AIF_PROFILE_OPTION_VALUES app
ON app.PROFILE_OPTION_NAME = po.PROFILE_OPTION_NAME
AND app.LEVEL_ID = 1005
AND app.LEVEL_VALUE = 7
AND app.LEVEL_ID <= po.MAX_LEVEL_ID
LEFT OUTER JOIN AIF_PROFILE_OPTION_VALUES usr
ON usr.PROFILE_OPTION_NAME = po.PROFILE_OPTION_NAME
AND usr.LEVEL_ID = 1010
AND usr.LEVEL_VALUE = NULL
AND usr.LEVEL_ID <= po.MAX_LEVEL_ID
WHERE po.PROFILE_OPTION_NAME = 'PROFILE_APP_ROOT_DIRECTORY'
2015-10-12 12:48:18,606 DEBUG [AIF]: HFM logFile : D:\FDMEE/outbox/logs/APP2_358_HFM.log
2015-10-12 12:48:18,606 DEBUG [AIF]: HFM Log File not found: D:\FDMEE/outbox/logs/APP2_358_HFM.log
2015-10-12 12:48:18,696 DEBUG [AIF]: Comm.finalizeProcess - START
2015-10-12 12:48:18,697 DEBUG [AIF]: CommData.updateRuleStatus - START
2015-10-12 12:48:18,697 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 = 358
AND pd.STATUS IN ('FAILED','WARNING')
)
WHEN 0 THEN 'SUCCESS'
ELSE (
SELECT MIN(pd.STATUS)
FROM AIF_PROCESS_DETAILS pd
WHERE pd.PROCESS_ID = 358
AND pd.STATUS IN ('FAILED','WARNING')
)
END
ELSE 'FAILED'
END
WHERE RULE_ID = 107
2015-10-12 12:48:18,700 DEBUG [AIF]: CommData.updateRuleStatus - END
2015-10-12 12:48:18,700 FATAL [AIF]: Error in HFM Load Data
2015-10-12 12:48:18,701 DEBUG [AIF]: Comm.updateProcess - START
2015-10-12 12:48:18,705 DEBUG [AIF]: Comm.updateProcess - END
2015-10-12 12:48:18,707 DEBUG [AIF]: The fdmAPI connection has been closed.
2015-10-12 12:48:18,709 INFO [AIF]: FDMEE Process End, Process ID: 358
Please suggest how to overcome this error. Also pls let me know if i am missing any step/Set up in FDMEE.
Thanks in advence.