Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Exporting Budget data to General Ledger

Priyanka MuttannavarOct 6 2017 — edited Mar 13 2018

Hi,

we are able to export the data from Data Management to Excel sheet but we are getting error in ERP side that

revalidating Budget data errored. Error message: There exist multiple ledgers associated with different Chart Of Accounts and Calendar. All ledgers in a single run should be associated with the same Chart Of Accounts and Calendar

we have asked ERP team but they were using only one ledger for Budgeting purpose then why we are getting this error :

2017-10-06 06:02:58,148 INFO [AIF]: FDMEE Process: 641, Log Level: 5

2017-10-06 06:02:58,149 INFO [AIF]: Location : Export_PBCS to GL (Partitionkey:23)

2017-10-06 06:02:58,149 INFO [AIF]: Period Name : September 2017 (Period Key:9/30/17 12:00 AM)

2017-10-06 06:02:58,149 INFO [AIF]: Category Name: Budget (Category key:3)

2017-10-06 06:02:58,149 INFO [AIF]: Rule Name : Export_PBCS to GL (Rule ID:31)

2017-10-06 06:03:09,177 INFO [AIF]: FDM Version: 11.1.2.4.220

2017-10-06 06:03:09,178 INFO [AIF]: Log File Encoding: UTF-8

2017-10-06 06:03:10,442 INFO [AIF]: -------START IMPORT STEP-------

2017-10-06 06:03:10,579 DEBUG [AIF]: CommData.preImportData - START

2017-10-06 06:03:10,579 DEBUG [AIF]: CommData.getRuleInfo - START

2017-10-06 06:03:10,624 DEBUG [AIF]:

SELECT adim.BALANCE_COLUMN_NAME DIMNAME, adim.DIMENSION_ID, adim.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 = 20

LEFT OUTER JOIN TBHVIMPITEMERPI tiie

ON tiie.IMPGROUPKEY = tpp.PARTIMPGROUP AND tiie.IMPFLDFIELDNAME = adim.BALANCE_COLUMN_NAME AND tiie.IMPMAPTYPE = 'ERP'

WHERE tpp.PARTITIONKEY = 23

AND adim.BALANCE_COLUMN_NAME IS NOT NULL

AND adim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

ORDER BY adim.BALANCE_COLUMN_NAME

2017-10-06 06:03:10,626 DEBUG [AIF]: {'TARGET_SOURCE_SYSTEM_TYPE': u'FUSION_GL_CLOUD', 'EXPORT_TO_TARGET_FLAG': u'Y', 'PLAN_NUMBER': 0L, 'TARGET_APPLICATION_SUB_TYPE': u'FUSION', 'JOURNAL_FLAG': None, 'DIMNAME_MAP': {u'UD2': {'DIMNAME': u'UD2', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD1': {'DIMNAME': u'UD1', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD4': {'DIMNAME': u'UD4', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD3': {'DIMNAME': u'UD3', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD5': {'DIMNAME': u'UD5', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'ACCOUNT': {'DIMNAME': u'ACCOUNT', 'TARGET_DIMENSION_CLASS_NAME': u'Account'}, u'ENTITY': {'DIMNAME': u'ENTITY', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}}, 'CATKEY': 3L, 'SOURCE_TGT_APP_NAME': u'BVBudget', 'APPLICATION_ID': 20L, 'SOURCE_DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'UD1', u'UD2', u'UD3', u'UD4', u'UD5'], 'TARGET_APPLICATION_TYPE': u'ESSBASE', 'CALENDAR_ID': None, 'PARTITIONKEY': 23L, 'EXECUTION_MODE': u'REPLACE', 'LEDGER_GROUP_CODE': u'NONE', 'PS_LEDGER': None, 'PARTVALGROUP': u'[NONE]', 'WEB_SERVICE_URL': u'http://ap1pbcsr302111.audc1.oraclecloud.com:9000/aif', 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'IS_INCREMENTAL_LOAD': False, 'INCLUDE_ADJ_PERIODS_FLAG': u'N', 'SOURCE_APPLICATION_TYPE': u'HPL', 'FCI_FLAG': 'N', 'IMPSOURCECOAID': 0L, 'KK_FLAG': u'N', 'LAST_UPDATED_BY': u'raghunandan.rao@bharatividyapeeth.edu', 'SOURCE_SYSTEM_ID': 0L, 'CHECK_FLAG': u'Y', 'RULE_ID': 31L, 'PERIOD_MAPPING_TYPE': u'EXPLICIT', 'INCLUDE_ZERO_BALANCE_FLAG': u'N', 'SOURCE_APP_NAME': u'BVBudget', 'IMPGROUPKEY': u'Export_PBCS to GL', 'SOURCE_SYSTEM_TYPE': u'EPM', 'IMPGROUPFILETYPE': u'EPM_EPM', 'LEDGER_GROUP': None, 'SOURCE_ADAPTER_FLAG': u'N', 'RECALCULATE_FLAG': u'Y', 'DATA_LOAD_METHOD': u'FUSION_GL_CLOUD', 'SOURCE_APPLICATION_ID': u'4', 'PLAN_TYPE': None, 'IMPTARGETSOURCESYSTEMID': 11L, 'TARGET_APPLICATION_NAME': u'BHARATIVIDYAPEETH'}

2017-10-06 06:03:10,628 DEBUG [AIF]: {u'AccountingPeriod': {'BCN': None, 'DCN': u'Period'}, u'Ledger': {'BCN': u'ACCOUNT', 'DCN': u'Account'}, u'FUTURE2': {'BCN': u'UD5', 'DCN': u'Generic'}, u'FUTURE1': {'BCN': u'UD4', 'DCN': u'Generic'}, u'DEPARTMENT': {'BCN': u'UD2', 'DCN': u'Generic'}, u'COURSE': {'BCN': u'UD1', 'DCN': u'Generic'}, u'Scenario': {'BCN': u'ENTITY', 'DCN': u'Generic'}, u'ACCOUNT': {'BCN': u'UD3', 'DCN': u'Generic'}, u'Balance Amount': {'BCN': None, 'DCN': u'Generic'}, u'Amount Type': {'BCN': None, 'DCN': u'Generic'}, u'Currency': {'BCN': None, 'DCN': u'Currency'}, u'Currency Type': {'BCN': None, 'DCN': u'Generic'}}

2017-10-06 06:03:10,630 DEBUG [AIF]: {u'UD2': {'IMPFLDSOURCECOLNAME': u'Entity'}, u'UD1': {'IMPFLDSOURCECOLNAME': u'Version'}, u'UD4': {'IMPFLDSOURCECOLNAME': u'Version'}, u'UD3': {'IMPFLDSOURCECOLNAME': u'Account'}, u'UD5': {'IMPFLDSOURCECOLNAME': u'Version'}, u'ACCOUNT': {'IMPFLDSOURCECOLNAME': u'Version'}, u'ENTITY': {'IMPFLDSOURCECOLNAME': u'Version'}, u'AMOUNT': {'IMPFLDSOURCECOLNAME': u'Amount'}}

2017-10-06 06:03:10,630 DEBUG [AIF]: CommData.getRuleInfo - END

2017-10-06 06:03:10,630 DEBUG [AIF]: CommData.insertEpmPeriods - START

2017-10-06 06:03:10,631 DEBUG [AIF]: doAppPeriodMappingsExist - BHARATIVIDYAPEETH: Y

2017-10-06 06:03:10,632 DEBUG [AIF]: doAppPeriodMappingsExist - BVBudget: Y

2017-10-06 06:03:10,632 DEBUG [AIF]: Period mapping section: EXPLICIT

2017-10-06 06:03:10,637 DEBUG [AIF]:

INSERT INTO AIF_PROCESS_PERIODS (

PROCESS_ID, PERIODKEY, ADJUSTMENT_PERIOD_FLAG, GL_PERIOD_YEAR, GL_PERIOD_YEAR_CHAR, GL_PERIOD_NUM, GL_PERIOD_NAME, GL_PERIOD_CODE

,GL_EFFECTIVE_PERIOD_NUM, YEARTARGET, PERIODTARGET, IMP_ENTITY_TYPE, IMP_ENTITY_NAME, TRANS_ENTITY_TYPE, TRANS_ENTITY_NAME, PRIOR_PERIOD_FLAG

)

SELECT DISTINCT brl.LOADID PROCESS_ID

,tpp.PERIODKEY PERIODKEY

,'N' ADJUSTMENT_PERIOD_FLAG

,0 GL_PERIOD_YEAR

,spp.YEARTARGET GL_PERIOD_YEAR_CHAR

,0 GL_PERIOD_NUM

,spp.PERIODTARGET GL_PERIOD_NAME

,spp.PERIODKEY GL_PERIOD_CODE

,0 GL_EFFECTIVE_PERIOD_NUM

,tpp.YEARTARGET YEARTARGET

,tpp.PERIODTARGET PERIODTARGET

,'PROCESS_BAL_IMP_FILE' IMP_ENTITY_TYPE

,'BVBudget_641.dat' IMP_ENTITY_NAME

,'PROCESS_BAL_TRANS' TRANS_ENTITY_TYPE

,tpp.PERIODDESC TRANS_ENTITY_NAME

,'N' PRIOR_PERIOD_FLAG

FROM (

AIF_BAL_RULE_LOADS brl

INNER JOIN TPOVCATEGORY pc

ON pc.CATKEY = brl.CATKEY

INNER JOIN TPOVPERIODADAPTOR_FLAT_V tpp

ON tpp.PERIODFREQ = pc.CATFREQ

AND tpp.PERIODKEY >= brl.START_PERIODKEY

AND tpp.PERIODKEY <= brl.END_PERIODKEY

AND tpp.INTSYSTEMKEY = 'BHARATIVIDYAPEETH'

)

INNER JOIN TPOVPERIODSOURCE ppsrc

ON ppsrc.TARGET_PERIODKEY = tpp.PERIODKEY

AND ppsrc.SOURCE_SYSTEM_ID = 0

AND SOURCE_APPLICATION_NAME = 'BVBudget'

AND TARGET_APPLICATION_NAME = 'BHARATIVIDYAPEETH'

AND ppsrc.MAPPING_TYPE = 'EXPLICIT'

INNER JOIN TPOVPERIODADAPTOR_FLAT_V spp

ON spp.PERIODFREQ = pc.CATFREQ

AND spp.PERIODKEY = ppsrc.PERIODKEY

AND spp.INTSYSTEMKEY = 'BVBudget'

WHERE brl.LOADID = 641

ORDER BY tpp.PERIODKEY, spp.PERIODKEY

2017-10-06 06:03:10,638 DEBUG [AIF]: periodSQL - periodParams: [u'BVBudget_641.dat', u'BHARATIVIDYAPEETH', 0L, u'BVBudget', u'BHARATIVIDYAPEETH', u'BVBudget', 641]

2017-10-06 06:03:10,671 DEBUG [AIF]: insertRowCount: 2

2017-10-06 06:03:10,672 DEBUG [AIF]: CommData.insertEpmPeriods - END

2017-10-06 06:03:10,672 DEBUG [AIF]: CommData.updateBalRuleLoadParams - START

2017-10-06 06:03:10,678 DEBUG [AIF]: yearDimensionName=Years, yearFilter="FY17"

2017-10-06 06:03:10,678 DEBUG [AIF]: periodDimensionName=Period, periodFilter="Sep","Total_Year"

2017-10-06 06:03:10,684 DEBUG [AIF]: CommData.updateBalRuleLoadParams - END

2017-10-06 06:03:10,685 DEBUG [AIF]: CommData.getPovList - START

2017-10-06 06:03:10,688 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 = 'BHARATIVIDYAPEETH'

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

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

2017-10-06 06:03:10,694 DEBUG [AIF]: CommData.getPovList - END

2017-10-06 06:03:10,694 DEBUG [AIF]: Comm.doScriptInit - START

2017-10-06 06:03:10,760 DEBUG [AIF]: fdmContext: {PERIODKEY=2017-09-30, CATKEY=3, EXPORTFLAG=Y, SOURCEID=0, TARGETAPPNAME=BHARATIVIDYAPEETH, USERLOCALE=en, IMPORTFLAG=Y, SCRIPTSDIR=/u03/inbox/data/scripts, SOURCENAME=EPM, PERIODNAME=September 2017, BATCHSCRIPTDIR=/u02/user_projects/instance0/FinancialDataQuality, LOCNAME=Export_PBCS to GL, USERNAME=raghunandan.rao@bharatividyapeeth.edu, APPROOTDIR=/u03/inbox, EPMORACLEHOME=/u02/Oracle/Middleware/EPMSystem11R1, TARGETAPPTYPE=ESSBASE, OUTBOXDIR=/u03/inbox/outbox, TARGETAPPDB=db, FILEDIR=inbox, APPID=20, MULTIPERIODLOAD=N, LOADID=641, RULEID=31, IMPORTMODE=REPLACE, SOURCETYPE=EPM, CATNAME=Budget, RULENAME=Export_PBCS to GL, LOCKEY=23, EXPORTMODE=null, INBOXDIR=/u03/inbox/inbox, EPMORACLEINSTANCEHOME=/u02/user_projects/instance0, IMPORTFORMAT=Export_PBCS to GL}

2017-10-06 06:03:10,769 DEBUG [AIF]: Added jar to Classpath: /u02/Oracle/Middleware/EPMSystem11R1/products/FinancialDataQuality/lib/epm-aif-odi-manual.jar

2017-10-06 06:03:10,777 DEBUG [AIF]: The executeEventScript is set to: NO

2017-10-06 06:03:10,777 DEBUG [AIF]: The AppRootFolder is set to: /u03/inbox

2017-10-06 06:03:10,778 DEBUG [AIF]: The JavaHome is set to: %EPM_ORACLE_HOME%/../jdk160_35

2017-10-06 06:03:10,778 DEBUG [AIF]: The OleDatabaseProvider is set to: ORAOLEDB.ORACLE

2017-10-06 06:03:10,778 DEBUG [AIF]: Comm.doScriptInit - END

2017-10-06 06:03:10,778 DEBUG [AIF]: CommData.preImportData - END

2017-10-06 06:03:10,821 DEBUG [AIF]: CommData.extractDataFromSource - START

2017-10-06 06:03:10,821 DEBUG [AIF]: AIFUtil.callOdiServlet - START

2017-10-06 06:03:10,887 INFO [AIF]: EssbaseService.extractData - START

2017-10-06 06:03:10,919 DEBUG [AIF]: appId:20, appType:HPL, mcFlag:false, dataLoadMethod:CLASSIC_VIA_EPMI, mDataFlowNode:LOCAL, textDataLoad:N, isFccsLoad:false, isJournalLoad:false

2017-10-06 06:03:10,919 DEBUG [AIF]: LOAD_METHOD:ESSFILE, LOAD_TYPE:DATA, EXPORT_MODE:STORE_DATA, CREATE_DRILL_REGION:false, PURGE_DATA_FILE:false, EXPORT_FILE_DELIMITER:,, BATCH_SIZE:10000

2017-10-06 06:03:10,976 INFO [AIF]: cloudServiceType: Planning, Resolved user name for application access: epm_default_cloud_admin

2017-10-06 06:03:10,976 DEBUG [AIF]: We need to connect to Essbase server.

2017-10-06 06:03:11,579 DEBUG [AIF]: Extracting data from BVBudget:Budget...

2017-10-06 06:03:11,685 INFO [AIF]: Extract Script (Calc): SET DataExportOptions

{

DATAEXPORTCOLFORMAT ON;

DATAEXPORTDIMHEADER ON;

DATAEXPORTDRYRUN OFF;

DataExportRelationalFile OFF;

DataExportNonExistingBlocks OFF;

DataExportLevel ALL;

DATAEXPORTCOLHEADER "Period";

DATAEXPORTOVERWRITEFILE ON;

DataExportDynamicCalc OFF;

};

FIX ("50201001","21001","Sep","Total_Year","Budget","Working","FY17")

DATAEXPORT "File" "," "/u03/inbox/data/BVBudget_Budget_641.txt" "#";

ENDFIX

2017-10-06 06:03:15,086 INFO [AIF]: EssbaseService.extractData - END (true)

2017-10-06 06:03:15,107 DEBUG [AIF]: AIFUtil.callOdiServlet - END

2017-10-06 06:03:15,108 DEBUG [AIF]: CommData.extractDataFromSource - END

2017-10-06 06:03:15,132 DEBUG [AIF]: CommData.insertImportProcessDetails - START

2017-10-06 06:03:15,133 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, 'raghunandan.rao@bharatividyapeeth.edu' 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 = 641

) q

ORDER BY ENTITY_NAME_ORDER

2017-10-06 06:03:15,137 DEBUG [AIF]: CommData.insertImportProcessDetails - END

2017-10-06 06:03:15,159 DEBUG [AIF]: FileData.importData - START

2017-10-06 06:03:15,160 DEBUG [AIF]: Importing data for load id: 641

2017-10-06 06:03:15,206 DEBUG [AIF]: CommData.getRuleInfo - START

2017-10-06 06:03:15,210 DEBUG [AIF]:

SELECT adim.BALANCE_COLUMN_NAME DIMNAME, adim.DIMENSION_ID, adim.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 = 20

LEFT OUTER JOIN TBHVIMPITEMERPI tiie

ON tiie.IMPGROUPKEY = tpp.PARTIMPGROUP AND tiie.IMPFLDFIELDNAME = adim.BALANCE_COLUMN_NAME AND tiie.IMPMAPTYPE = 'ERP'

WHERE tpp.PARTITIONKEY = 23

AND adim.BALANCE_COLUMN_NAME IS NOT NULL

AND adim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

ORDER BY adim.BALANCE_COLUMN_NAME

2017-10-06 06:03:15,211 DEBUG [AIF]: {'TARGET_SOURCE_SYSTEM_TYPE': u'FUSION_GL_CLOUD', 'EXPORT_TO_TARGET_FLAG': u'Y', 'PLAN_NUMBER': 0L, 'TARGET_APPLICATION_SUB_TYPE': u'FUSION', 'JOURNAL_FLAG': None, 'DIMNAME_MAP': {u'UD2': {'DIMNAME': u'UD2', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD1': {'DIMNAME': u'UD1', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD4': {'DIMNAME': u'UD4', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD3': {'DIMNAME': u'UD3', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD5': {'DIMNAME': u'UD5', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'ACCOUNT': {'DIMNAME': u'ACCOUNT', 'TARGET_DIMENSION_CLASS_NAME': u'Account'}, u'ENTITY': {'DIMNAME': u'ENTITY', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}}, 'CATKEY': 3L, 'SOURCE_TGT_APP_NAME': u'BVBudget', 'APPLICATION_ID': 20L, 'SOURCE_DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'UD1', u'UD2', u'UD3', u'UD4', u'UD5'], 'TARGET_APPLICATION_TYPE': u'ESSBASE', 'CALENDAR_ID': None, 'PARTITIONKEY': 23L, 'EXECUTION_MODE': u'REPLACE', 'LEDGER_GROUP_CODE': u'NONE', 'PS_LEDGER': None, 'PARTVALGROUP': u'[NONE]', 'WEB_SERVICE_URL': u'http://ap1pbcsr302111.audc1.oraclecloud.com:9000/aif', 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'IS_INCREMENTAL_LOAD': False, 'INCLUDE_ADJ_PERIODS_FLAG': u'N', 'SOURCE_APPLICATION_TYPE': u'HPL', 'FCI_FLAG': 'N', 'IMPSOURCECOAID': 0L, 'KK_FLAG': u'N', 'LAST_UPDATED_BY': u'raghunandan.rao@bharatividyapeeth.edu', 'SOURCE_SYSTEM_ID': 0L, 'CHECK_FLAG': u'Y', 'RULE_ID': 31L, 'PERIOD_MAPPING_TYPE': u'EXPLICIT', 'INCLUDE_ZERO_BALANCE_FLAG': u'N', 'SOURCE_APP_NAME': u'BVBudget', 'IMPGROUPKEY': u'Export_PBCS to GL', 'SOURCE_SYSTEM_TYPE': u'EPM', 'IMPGROUPFILETYPE': u'EPM_EPM', 'LEDGER_GROUP': None, 'SOURCE_ADAPTER_FLAG': u'N', 'RECALCULATE_FLAG': u'Y', 'DATA_LOAD_METHOD': u'FUSION_GL_CLOUD', 'SOURCE_APPLICATION_ID': u'4', 'PLAN_TYPE': None, 'IMPTARGETSOURCESYSTEMID': 11L, 'TARGET_APPLICATION_NAME': u'BHARATIVIDYAPEETH'}

2017-10-06 06:03:15,213 DEBUG [AIF]: {u'AccountingPeriod': {'BCN': None, 'DCN': u'Period'}, u'Ledger': {'BCN': u'ACCOUNT', 'DCN': u'Account'}, u'FUTURE2': {'BCN': u'UD5', 'DCN': u'Generic'}, u'FUTURE1': {'BCN': u'UD4', 'DCN': u'Generic'}, u'DEPARTMENT': {'BCN': u'UD2', 'DCN': u'Generic'}, u'COURSE': {'BCN': u'UD1', 'DCN': u'Generic'}, u'Scenario': {'BCN': u'ENTITY', 'DCN': u'Generic'}, u'ACCOUNT': {'BCN': u'UD3', 'DCN': u'Generic'}, u'Balance Amount': {'BCN': None, 'DCN': u'Generic'}, u'Amount Type': {'BCN': None, 'DCN': u'Generic'}, u'Currency': {'BCN': None, 'DCN': u'Currency'}, u'Currency Type': {'BCN': None, 'DCN': u'Generic'}}

2017-10-06 06:03:15,214 DEBUG [AIF]: {u'UD2': {'IMPFLDSOURCECOLNAME': u'Entity'}, u'UD1': {'IMPFLDSOURCECOLNAME': u'Version'}, u'UD4': {'IMPFLDSOURCECOLNAME': u'Version'}, u'UD3': {'IMPFLDSOURCECOLNAME': u'Account'}, u'UD5': {'IMPFLDSOURCECOLNAME': u'Version'}, u'ACCOUNT': {'IMPFLDSOURCECOLNAME': u'Version'}, u'ENTITY': {'IMPFLDSOURCECOLNAME': u'Version'}, u'AMOUNT': {'IMPFLDSOURCECOLNAME': u'Amount'}}

2017-10-06 06:03:15,214 DEBUG [AIF]: CommData.getRuleInfo - END

2017-10-06 06:03:15,215 DEBUG [AIF]:

SELECT a.partitionkey, a.catkey, a.start_periodkey, b.perioddesc, coalesce(a.journal_flag,'N') JOURNAL_FLAG, c.last_updated_by,

coalesce(a.FILE_NAME_STATIC,'') FILE_NAME_STATIC, a.FILE_PATH, a.RULE_ID, a.application_id

FROM aif_bal_rule_loads a,

aif_balance_rules br,

tpovperiodadaptor b,

aif_processes c

WHERE a.loadid = 641

AND a.start_periodkey=b.periodkey

AND a.loadid =c.process_id

AND br.rule_id = a.rule_id

AND b.INTSYSTEMKEY = 'BHARATIVIDYAPEETH'

2017-10-06 06:03:15,217 DEBUG [AIF]: FileData.py: Importing Text data for load id: 641

2017-10-06 06:03:15,295 INFO [AIF]: EPMFDM-140274:Message - LOADID:PARTKEY:CATKEY:RULEID:CURRENCYKEY:FILEPATH:IMPGROUPFILETYPE::641;23:3:31:INR:/u03/inbox/data:EPM_EPM

2017-10-06 06:03:15,300 INFO [AIF]: EPMFDM-140274:Message - File CharEncoding as per settings: UTF-8

2017-10-06 06:03:15,300 INFO [AIF]: EPMFDM-140274:Message - ImportTextData - Start

2017-10-06 06:03:15,300 DEBUG [AIF]: EPMFDM-140274:Message - SELECT pp.PERIODKEY,

pp.PERIODTARGET,

pp.GL_PERIOD_NAME,

en.FILE_ENTITY_NAME,

en.ENTITY_NAME_ORDER,

br.MULTI_PERIOD_FILE_FLAG,

pp.TRANS_ENTITY_NAME,

pp.GL_PERIOD_YEAR_CHAR,

pp.GL_PERIOD_NUM ,

pp.YEARTARGET

FROM AIF_PROCESS_PERIODS pp,

AIF_BALANCE_RULES br,

AIF_PROCESSES p,

(SELECT DISTINCT entity_name FILE_ENTITY_NAME,

pd.process_id,

pd.entity_name_order

FROM aif_process_details pd

WHERE pd.entity_type = 'PROCESS_BAL_IMP_FILE'

AND pd.target_table_name = 'TDATASEG'

AND pd.status = 'PENDING'

) en

WHERE pp.PROCESS_ID = ?

AND p.process_id = pp.process_id

AND en.process_id = pp.process_id

AND pp.IMP_ENTITY_NAME = en.file_entity_name

AND br.rule_id = p.rule_id

AND en.entity_name_order = pp.GL_EFFECTIVE_PERIOD_NUM

ORDER BY en.entity_name_order

2017-10-06 06:03:15,383 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=AMOUNT:ImpFieldText=null:StartPos=1:SourceClName=Amount

2017-10-06 06:03:15,383 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD3:ImpFieldText=null:StartPos=1:SourceClName=Account

2017-10-06 06:03:15,383 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD2:ImpFieldText=null:StartPos=1:SourceClName=Entity

2017-10-06 06:03:15,383 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD1:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,383 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD5:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,383 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ACCOUNT:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,383 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ENTITY:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,384 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD4:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,384 INFO [AIF]: EPMFDM-140274:Message - Initialize error maps

2017-10-06 06:03:15,385 INFO [AIF]: EPMFDM-140274:Message - File Name BVBudget_641.dat

periodKey2017-09-30

2017-10-06 06:03:15,385 INFO [AIF]: EPMFDM-140274:Message - dataLoadEngine.getSourceSystemTypeEPM() HPL

2017-10-06 06:03:15,385 DEBUG [AIF]: BOM exists

2017-10-06 06:03:15,385 DEBUG [AIF]: After removing BOM ["Years","Scenario","Version","Entity","Account","Period"]

2017-10-06 06:03:15,385 DEBUG [AIF]: EPMFDM-140274:Message - HEADER: ["Years", "Scenario", "Version", "Entity", "Account", "Period"]

2017-10-06 06:03:15,390 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=CURKEY:ImpFieldText=null:StartPos=1:SourceClName=Currency

2017-10-06 06:03:15,418 DEBUG [AIF]: EPMFDM-140274:Message - Exception while applying CURKEY mapping on the row:["FY17","Budget","Working","21001","50201001",129042,1548500]

2017-10-06 06:03:15,478 INFO [AIF]: EPMFDM-140274:Message - PROCESS ID: 641

PARTITIONKEY: 23

IMPORT GROUP: Export_PBCS to GL

FILE TYPE: DELIMITED

DELIMITER: ,

SOURCE FILE: BVBudget_641.dat

PROCESSING CODES:

-----------------------------------------------------------------------------------

BLANK............. Line is blank or empty.

NN................ Non-Numeric, Amount field contains non numeric characters.

TC................ Type Conversion, Amount field could not be converted to a number.

ZP................ Zero Suppress, Amount field contains a 0 value and zero suppress is ON.

SKIP FIELD.............. SKIP field value was found

NULL ACCOUNT VALUE.............. Account Field is null

SKIP FROM SCRIPT.............. Skipped through Script

HEADER ROW.............. Header Row

ERROR_INVALID_PERIOD.............. Invalid period specified in the file.

Rows Loaded: 1

Rows Rejected: 0

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=AMOUNT:ImpFieldText=null:StartPos=1:SourceClName=Amount

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD3:ImpFieldText=null:StartPos=1:SourceClName=Account

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD2:ImpFieldText=null:StartPos=1:SourceClName=Entity

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD1:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD5:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ACCOUNT:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ENTITY:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,516 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD4:ImpFieldText=null:StartPos=1:SourceClName=Version

2017-10-06 06:03:15,517 INFO [AIF]: EPMFDM-140274:Message - Initialize error maps

2017-10-06 06:03:15,517 INFO [AIF]: EPMFDM-140274:Message - File Name BVBudget_641.dat

periodKey2017-09-30

2017-10-06 06:03:15,517 INFO [AIF]: EPMFDM-140274:Message - dataLoadEngine.getSourceSystemTypeEPM() HPL

2017-10-06 06:03:15,517 DEBUG [AIF]: BOM exists

2017-10-06 06:03:15,517 DEBUG [AIF]: After removing BOM ["Years","Scenario","Version","Entity","Account","Period"]

2017-10-06 06:03:15,517 DEBUG [AIF]: EPMFDM-140274:Message - HEADER: ["Years", "Scenario", "Version", "Entity", "Account", "Period"]

2017-10-06 06:03:15,520 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=CURKEY:ImpFieldText=null:StartPos=1:SourceClName=Currency

2017-10-06 06:03:15,545 DEBUG [AIF]: EPMFDM-140274:Message - Exception while applying CURKEY mapping on the row:["FY17","Budget","Working","21001","50201001",129042,1548500]

2017-10-06 06:03:15,547 INFO [AIF]: EPMFDM-140274:Message - PROCESS ID: 641

PARTITIONKEY: 23

IMPORT GROUP: Export_PBCS to GL

FILE TYPE: DELIMITED

DELIMITER: ,

SOURCE FILE: BVBudget_641.dat

PROCESSING CODES:

-----------------------------------------------------------------------------------

BLANK............. Line is blank or empty.

NN................ Non-Numeric, Amount field contains non numeric characters.

TC................ Type Conversion, Amount field could not be converted to a number.

ZP................ Zero Suppress, Amount field contains a 0 value and zero suppress is ON.

SKIP FIELD.............. SKIP field value was found

NULL ACCOUNT VALUE.............. Account Field is null

SKIP FROM SCRIPT.............. Skipped through Script

HEADER ROW.............. Header Row

ERROR_INVALID_PERIOD.............. Invalid period specified in the file.

Rows Loaded: 1

Rows Rejected: 0

2017-10-06 06:03:15,548 DEBUG [AIF]: EPMFDM-140274:Message - Script- archiveDataFile- Start

2017-10-06 06:03:15,549 INFO [AIF]: EPMFDM-140274:Message - ARCHIVE MODE: Copy

2017-10-06 06:03:15,549 INFO [AIF]: EPMFDM-140274:Message - Start archiving file:

2017-10-06 06:03:15,549 DEBUG [AIF]: EPMFDM-140274:Message - ArchiveId: 64120170930

2017-10-06 06:03:15,550 DEBUG [AIF]: EPMFDM-140274:Message - Archive- SourceFileName: BVBudget_641

Extension: dat

2017-10-06 06:03:15,550 INFO [AIF]: EPMFDM-140274:Message - Archive file name: 64120170930.dat

2017-10-06 06:03:15,552 INFO [AIF]: EPMFDM-140274:Message - Deleting the source file: BVBudget_641.dat

2017-10-06 06:03:15,552 INFO [AIF]: EPMFDM-140274:Message - File not deleted: /u03/inbox/data/BVBudget_641.dat

2017-10-06 06:03:15,555 DEBUG [AIF]: EPMFDM-140274:Message - ArchiveDocKey:BVBudget_641-raghunandan.rao@bharatividyapeeth.edu-20171006_060315-1

2017-10-06 06:03:15,562 DEBUG [AIF]: EPMFDM-140274:Message - Script- archiveDataFile- End

2017-10-06 06:03:15,562 INFO [AIF]: EPMFDM-140274:Message - ImportTextData - End

2017-10-06 06:03:15,563 INFO [AIF]: EPMFDM-140274:Message - Total time taken for the import in ms = 262

2017-10-06 06:03:15,565 DEBUG [AIF]: FileData.importData - END

2017-10-06 06:03:15,636 DEBUG [AIF]: CommData.postImportData - START

2017-10-06 06:03:15,636 DEBUG [AIF]: CommData.updateTDATASEG_T - START

2017-10-06 06:03:15,636 DEBUG [AIF]:

SELECT pp.PERIODKEY

FROM AIF_PROCESS_PERIODS pp

,( SELECT entity_name FILE_ENTITY_NAME, pd.process_id, pd.entity_name_order

FROM aif_process_details pd

WHERE pd.entity_type = 'PROCESS_BAL_IMP_FILE'

AND pd.target_table_name = 'TDATASEG'

AND pd.status = 'FAILED'

) en

WHERE pp.PROCESS_ID = 641

AND en.process_id = pp.process_id

AND pp.IMP_ENTITY_NAME = en.file_entity_name

order by en.entity_name_order

2017-10-06 06:03:15,639 INFO [AIF]:

Import Data from Source for Period 'September 2017'

2017-10-06 06:03:15,639 DEBUG [AIF]: CommData.updateWorkflow - START

2017-10-06 06:03:15,639 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 = 23 AND tlp.CATKEY = 3 AND tlp.PERIODKEY = '2017-09-30' AND tlp.RULE_ID = 31 AND tlps.PROCESSSTATUSKEY = tlp.PROCESSSTATUS

2017-10-06 06:03:15,645 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=23 AND CATKEY=3 AND PERIODKEY='2017-09-30' AND RULE_ID=31

2017-10-06 06:03:15,649 DEBUG [AIF]: CommData.updateWorkflow - END

2017-10-06 06:03:15,649 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 = 23

AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

ORDER BY adim.BALANCE_COLUMN_NAME

2017-10-06 06:03:15,656 DEBUG [AIF]:

UPDATE TDATASEG_T

SET AMOUNTX = AMOUNT, DATAX = NULL

,ACCOUNTX = NULL

,ENTITYX = NULL

,UD1X = NULL

,UD2X = NULL

,UD3X = NULL

,UD4X = NULL

,UD5X = NULL

WHERE TDATASEG_T.LOADID = 641 AND TDATASEG_T.PARTITIONKEY = 23 AND TDATASEG_T.CATKEY = 3 AND TDATASEG_T.PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,659 INFO [AIF]: Generic Data Rows Imported from Source: 2

2017-10-06 06:03:15,659 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, 'ESSBASE', 'BHARATIVIDYAPEETH', NULL, 0

,YEARTARGET, PERIODTARGET, 'Y', 23, 3, 31, PERIODKEY, 'N'

FROM AIF_PROCESS_PERIODS

WHERE PROCESS_ID = 641

AND PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,674 INFO [AIF]: Total Data Rows Imported from Source: 2

2017-10-06 06:03:15,674 DEBUG [AIF]: CommData.updateTDATASEG_T - END

2017-10-06 06:03:15,674 DEBUG [AIF]: CommData.postImportData - END

2017-10-06 06:03:15,699 DEBUG [AIF]: LogicGroup.createLogicAccounts - START

2017-10-06 06:03:15,699 DEBUG [AIF]: CommLogicGroup.getRuleInfo - START

2017-10-06 06:03:15,699 DEBUG [AIF]:

select part.PARTLOGICGROUP, lgrp.CALCGROUPTYPE

from AIF_PROCESSES prs, TPOVPARTITION part,TBHVLOGICGROUP lgrp

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

and part.PARTLOGICGROUP=lgrp.CALCGROUPKEY

2017-10-06 06:03:15,700 DEBUG [AIF]: CommLogicGroup.getRuleInfo - END

2017-10-06 06:03:15,701 DEBUG [AIF]: LogicGroup.createLogicAccounts - END

2017-10-06 06:03:15,725 DEBUG [AIF]: CommData.mapData - START

2017-10-06 06:03:15,725 DEBUG [AIF]: CommData.insertTransProcessDetails - START

2017-10-06 06:03:15,725 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, 'raghunandan.rao@bharatividyapeeth.edu' 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 = 641

AND PRIOR_PERIOD_FLAG = 'N'

GROUP BY PROCESS_ID, TRANS_ENTITY_TYPE, TRANS_ENTITY_NAME

) q

ORDER BY ENTITY_NAME_ORDER

2017-10-06 06:03:15,729 DEBUG [AIF]: CommData.insertTransProcessDetails - END

2017-10-06 06:03:15,729 DEBUG [AIF]:

DELETE FROM TDATAMAP_T

WHERE LOADID < 641

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

2017-10-06 06:03:15,734 DEBUG [AIF]:

DELETE FROM TDATASEG_T

WHERE LOADID < 641

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

2017-10-06 06:03:15,737 DEBUG [AIF]:

DELETE FROM AIF_PROCESS_PERIODS

WHERE PROCESS_ID < 641

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

2017-10-06 06:03:15,740 DEBUG [AIF]: CommMap.loadTDATAMAP_T - START

2017-10-06 06:03:15,740 DEBUG [AIF]: CommData.getMapPartitionKeyandName - START

2017-10-06 06:03:15,740 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 = 23

2017-10-06 06:03:15,741 DEBUG [AIF]: CommData.getMapPartitionKeyandName - END

2017-10-06 06:03:15,741 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 641, DATAKEY, 23 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

WHERE PARTITIONKEY = 23

AND ( RULE_ID IS NULL OR RULE_ID = 31 )

2017-10-06 06:03:15,760 DEBUG [AIF]: Number of Rows inserted into TDATAMAP_T: 7

2017-10-06 06:03:15,761 DEBUG [AIF]: CommMap.updateTDATASEG_T_TDATASEGW - START

2017-10-06 06:03:15,761 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 = 641

AND atad.APPLICATION_ID = 20

AND tdm.PARTITIONKEY = 23

AND tdm.TDATAMAPTYPE = 'ERP'

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

AND tdm.WHERECLAUSETYPE IS NULL

UNION ALL

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

,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 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 = 641

AND atad.APPLICATION_ID = 20

AND tdm.PARTITIONKEY = 23

AND tdm.TDATAMAPTYPE = 'ERP'

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

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

) q

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

2017-10-06 06:03:15,770 INFO [AIF]:

Map Data for Period 'September 2017'

2017-10-06 06:03:15,770 DEBUG [AIF]: Comm.updateProcessDetail - START

2017-10-06 06:03:15,770 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 ('raghunandan.rao@bharatividyapeeth.edu' IS NULL) THEN LAST_UPDATED_BY ELSE 'raghunandan.rao@bharatividyapeeth.edu' END

,LAST_UPDATE_DATE = CURRENT_TIMESTAMP

WHERE PROCESS_ID = 641 AND ENTITY_TYPE = 'PROCESS_BAL_TRANS' AND ENTITY_NAME = 'September 2017'

2017-10-06 06:03:15,772 DEBUG [AIF]: Comm.updateProcessDetail - END

2017-10-06 06:03:15,773 DEBUG [AIF]:

SELECT COUNT(*) ROW_COUNT

FROM TDATASEG_T

WHERE LOADID = 641

AND (PARTITIONKEY = 23 AND CATKEY = 3 AND PERIODKEY = '2017-09-30' AND RULE_ID = 31 AND VALID_FLAG = 'Y')

2017-10-06 06:03:15,775 INFO [AIF]:

Processing Mappings for Column 'ACCOUNT'

2017-10-06 06:03:15,776 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 = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM'

)

,ACCOUNTF = 1

WHERE LOADID = 641

AND PARTITIONKEY = 23

AND CATKEY = 3

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 = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM' )

AND PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,779 INFO [AIF]: Data Rows Updated by Rule Mapping EXPLICIT: 2

2017-10-06 06:03:15,779 INFO [AIF]:

Processing Mappings for Column 'ENTITY'

2017-10-06 06:03:15,781 DEBUG [AIF]:

UPDATE TDATASEG_T

SET (ENTITYX ,ENTITYR, 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 = 'ENTITY'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.ENTITY

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM'

)

,ENTITYF = 1

WHERE LOADID = 641

AND PARTITIONKEY = 23

AND CATKEY = 3

AND (ENTITYX IS NULL OR ENTITYX = '')

AND EXISTS ( SELECT 1

FROM TDATAMAP_T tdm

WHERE tdm.LOADID = TDATASEG_T.LOADID

AND tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY

AND tdm.DIMNAME = 'ENTITY'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.ENTITY

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM' )

AND PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,784 INFO [AIF]: Data Rows Updated by Rule Mapping EXPLICIT: 2

2017-10-06 06:03:15,784 INFO [AIF]:

Processing Mappings for Column 'UD1'

2017-10-06 06:03:15,786 DEBUG [AIF]:

UPDATE TDATASEG_T

SET (UD1X ,UD1R, 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 = 'UD1'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD1

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM'

)

,UD1F = 1

WHERE LOADID = 641

AND PARTITIONKEY = 23

AND CATKEY = 3

AND (UD1X IS NULL OR UD1X = '')

AND EXISTS ( SELECT 1

FROM TDATAMAP_T tdm

WHERE tdm.LOADID = TDATASEG_T.LOADID

AND tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY

AND tdm.DIMNAME = 'UD1'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD1

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM' )

AND PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,789 INFO [AIF]: Data Rows Updated by Rule Mapping EXPLICIT: 2

2017-10-06 06:03:15,789 INFO [AIF]:

Processing Mappings for Column 'UD2'

2017-10-06 06:03:15,790 DEBUG [AIF]:

UPDATE TDATASEG_T

SET (UD2X ,UD2R, 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 = 'UD2'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD2

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM'

)

,UD2F = 1

WHERE LOADID = 641

AND PARTITIONKEY = 23

AND CATKEY = 3

AND (UD2X IS NULL OR UD2X = '')

AND EXISTS ( SELECT 1

FROM TDATAMAP_T tdm

WHERE tdm.LOADID = TDATASEG_T.LOADID

AND tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY

AND tdm.DIMNAME = 'UD2'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD2

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM' )

AND PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,793 INFO [AIF]: Data Rows Updated by Rule Mapping EXPLICIT: 2

2017-10-06 06:03:15,794 INFO [AIF]:

Processing Mappings for Column 'UD3'

2017-10-06 06:03:15,795 DEBUG [AIF]:

UPDATE TDATASEG_T

SET (UD3X ,UD3R, 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 = 'UD3'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD3

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM'

)

,UD3F = 1

WHERE LOADID = 641

AND PARTITIONKEY = 23

AND CATKEY = 3

AND (UD3X IS NULL OR UD3X = '')

AND EXISTS ( SELECT 1

FROM TDATAMAP_T tdm

WHERE tdm.LOADID = TDATASEG_T.LOADID

AND tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY

AND tdm.DIMNAME = 'UD3'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD3

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM' )

AND PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,798 INFO [AIF]: Data Rows Updated by Rule Mapping EXPLICIT: 2

2017-10-06 06:03:15,798 INFO [AIF]:

Processing Mappings for Column 'UD4'

2017-10-06 06:03:15,799 DEBUG [AIF]:

UPDATE TDATASEG_T

SET (UD4X ,UD4R, 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 = 'UD4'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD4

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM'

)

,UD4F = 1

WHERE LOADID = 641

AND PARTITIONKEY = 23

AND CATKEY = 3

AND (UD4X IS NULL OR UD4X = '')

AND EXISTS ( SELECT 1

FROM TDATAMAP_T tdm

WHERE tdm.LOADID = TDATASEG_T.LOADID

AND tdm.PARTITIONKEY = TDATASEG_T.PARTITIONKEY

AND tdm.DIMNAME = 'UD4'

AND tdm.WHERECLAUSETYPE IS NULL

AND tdm.SRCKEY = TDATASEG_T.UD4

AND tdm.RULE_ID = TDATASEG_T.RULE_ID

AND tdm.TDATAMAPTYPE <> 'MULTIDIM' )

AND PERIODKEY = '2017-09-30'

2017-10-06 06:03:15,802 INFO [AIF]: Data Rows Updated by Rule Mapping EXPLICIT: 2

2017-10-06 06:03:15,803 INFO [AIF]:

Processing Mappings for Column 'UD5'

2017-10-06 06:03:15,804 DEBUG [AIF]:

UPDATE TDATASEG_T

SET (UD5X ,UD5R, 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

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 10 2018

Added on Oct 6 2017

#business-intelligence-applications, #performance-management-applications, #planning-and-budgeting

15 comments

1,320 views