Skip to Main Content

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!

FDMEE Jython Error

DakkuNov 3 2015 — edited Nov 4 2015

I need some assistance. I am new to Jython. We have a script we wrote where if certain accounts match then use the ICP partner else put everything to ICP None. This script is failing in Jython and I have gone through it numerous times. Here are the errors I am getting

#Purpose: To tag all intercompany accounts with corresponding ICP partners

str1 = ""

StrMain = ""

strDelim = "\t"

def PAR_ICP(strField, strRecord):

str1 = strRecord.split(strDelim)[1] #split take charater in 0 1 2 3 order

StrMain = str1[0:8]

if strMain in ("11100001","11100003","11100002", "11100006","11100007","11100010","11100011","11100012","11100014","11350001","11350002","15510001","15520004","25710001","25710002","25720001","25720002","25720003","25720004","25720005","30100003","31000001","31000002","31000008","31000022","70200003","70200007","70200008"):

  return strField

else:

  return "[ICP None]"

2015-11-03 12:24:35,289 INFO  [AIF]: FDMEE Process Start, Process ID: 376

2015-11-03 12:24:35,289 INFO  [AIF]: FDMEE Logging Level: 5

2015-11-03 12:24:35,289 INFO  [AIF]: FDMEE Log File: \\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST\outbox\logs\TEST_376.log

2015-11-03 12:24:35,290 INFO  [AIF]: User:admin

2015-11-03 12:24:35,290 INFO  [AIF]: Location:CORP_DOM_ACT (Partitionkey:32)

2015-11-03 12:24:35,290 INFO  [AIF]: Period Name:SEP2015 (Period Key:9/30/15 12:00 AM)

2015-11-03 12:24:35,290 INFO  [AIF]: Category Name:ACT (Category key:4)

2015-11-03 12:24:35,291 INFO  [AIF]: Rule Name:test (Rule ID:3)

2015-11-03 12:24:36,868 INFO  [AIF]: FDM Version: 11.1.2.4.000

2015-11-03 12:24:36,868 INFO  [AIF]: Jython Version: 2.5.1 (Release_2_5_1:6813, Sep 26 2009, 13:47:54)

[Oracle JRockit(R) (Oracle Corporation)]

2015-11-03 12:24:36,868 INFO  [AIF]: Java Platform: java1.6.0_37

2015-11-03 12:24:36,868 INFO  [AIF]: Log File Encoding: UTF-8

2015-11-03 12:24:38,832 INFO  [AIF]: -------START IMPORT STEP-------

2015-11-03 12:24:38,918 DEBUG [AIF]: CommData.preImportData - START

2015-11-03 12:24:38,925 DEBUG [AIF]: CommData.getRuleInfo - START

2015-11-03 12:24:38,926 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 = 376

       

2015-11-03 12:24:38,932 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 = 20

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

      AND adim.BALANCE_COLUMN_NAME IS NOT NULL

      AND dim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

     

      ORDER BY adim.BALANCE_COLUMN_NAME

     

2015-11-03 12:24:38,934 DEBUG [AIF]: {'APPLICATION_ID': 20L, 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'PLAN_TYPE': None, 'RULE_NAME': u'test', 'ACTUAL_FLAG': None, 'IS_INCREMENTAL_LOAD': False, 'EPM_ORACLE_INSTANCE': u'D:\\Oracle\\Middleware\\user_projects\\epmsystem1', 'CATKEY': 4L, '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'N', 'LEDGER_GROUP': None, 'TARGET_APPLICATION_NAME': u'TEST', 'RECALCULATE_FLAG': u'Y', 'SOURCE_SYSTEM_ID': 7L, 'TEMP_DATA_TABLE_NAME': 'TDATASEG_T', 'KK_FLAG': u'N', 'IMPGROUPKEY': u'CORP_DOM_ACT', 'AMOUNT_TYPE': u'MONETARY', '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'], 'FCI_FLAG': 'N', 'IMPSOURCECOAID': 0L, 'TDATAMAPTYPE': 'ERP', 'LAST_UPDATED_BY': u'admin', 'DIMNAME_MAP': {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': 165L}, 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': 162L}, 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': 159L}, 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': 164L}, 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': 166L}, 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': 161L}, 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': 163L}}, 'TARGET_APPLICATION_TYPE': u'HFM', 'PARTITIONKEY': 32L, 'PARTVALGROUP': u'[NONE]', 'LEDGER_GROUP_CODE': u'NONE', 'INCLUDE_ZERO_BALANCE_FLAG': None, 'EXECUTION_MODE': u'REPLACE', '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': 3L, '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://orcl-hfm-dev01.mtvn.ad.viacom.com:6550/aif', 'DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'PARTTARGETAPPLICATIONID': 20L, 'IMPTARGETSOURCESYSTEMID': 0L}

2015-11-03 12:24:38,934 DEBUG [AIF]: CommData.getRuleInfo - END

2015-11-03 12:24:38,938 DEBUG [AIF]: CommData.insertFilePeriods - START

2015-11-03 12:24:38,941 DEBUG [AIF]: doAppPeriodMappingsExist - TEST: Y

2015-11-03 12:24:38,941 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 = 'TEST'

          INNER JOIN AIF_BAL_RULE_LOAD_PARAMS brp

            ON brp.loadid = brl.loadid

            AND convert(varchar,brp.parameter_value,111) = pp.periodkey

          WHERE brl.LOADID = 376   

            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 = 'TEST'

            AND pp.PERIODKEY >= brl.START_PERIODKEY

            AND pp.PERIODKEY <= brl.END_PERIODKEY        

          WHERE brl.LOADID = 376   

            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 = 'TEST'

            AND pp.PERIODKEY >= brl.START_PERIODKEY

            AND pp.PERIODKEY <= brl.END_PERIODKEY

          WHERE brl.LOADID = 376             

            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-11-03 12:24:38,943 INFO  [AIF]: File Name: HFM_Actual_201512_Version 5.TXT

2015-11-03 12:24:38,943 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 (376,'2015-09-30','N',0,'0' ,'0',0,1,'2015','SEP', 'PROCESS_BAL_IMP_FILE','HFM_Actual_201512_Version 5.TXT','PROCESS_BAL_TRANS','SEP2015','N' )

       

2015-11-03 12:24:38,945 DEBUG [AIF]: CommData.insertFilePeriods - END

2015-11-03 12:24:38,948 DEBUG [AIF]: CommData.getPovList - START

2015-11-03 12:24:38,948 DEBUG [AIF]:

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

        ,COALESCE(pp.PERIODDESC, CONVERT(VARCHAR,pprd.PERIODKEY,120)) 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 = 'TEST'

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

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

     

2015-11-03 12:24:38,949 DEBUG [AIF]: CommData.getPovList - END

2015-11-03 12:24:38,950 DEBUG [AIF]: CommData.insertImportProcessDetails - START

2015-11-03 12:24:38,951 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

          ,'admin' 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 = 376

          ) q

          ORDER BY ENTITY_NAME_ORDER

         

2015-11-03 12:24:38,954 DEBUG [AIF]: CommData.insertImportProcessDetails - END

2015-11-03 12:24:38,960 DEBUG [AIF]: Comm.doScriptInit - START

2015-11-03 12:24:39,166 DEBUG [AIF]: fdmContext: {BATCHSCRIPTDIR=D:\Oracle\Middleware\user_projects\epmsystem1\FinancialDataQuality, INBOXDIR=\\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST\inbox, LOCNAME=CORP_DOM_ACT, SOURCENAME=File System, APPID=20, SOURCEID=7, APPROOTDIR=\\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST, IMPORTFORMAT=CORP_DOM_ACT, SCRIPTSDIR=\\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST\data\scripts, EPMORACLEHOME=D:\Oracle\Middleware\EPMSystem11R1, TARGETAPPTYPE=HFM, RULEID=3, CATNAME=ACT, EPMORACLEINSTANCEHOME=D:\Oracle\Middleware\user_projects\epmsystem1, LOADID=376, PERIODNAME=SEP2015, IMPORTMODE=REPLACE, SOURCETYPE=FILE, PERIODKEY=2015-09-30, EXPORTFLAG=N, TARGETAPPDB=null, TARGETAPPNAME=TEST, FILENAME=HFM_Actual_201512_Version 5.TXT, LOCKEY=32, RULENAME=test, OUTBOXDIR=\\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST\outbox, MULTIPERIODLOAD=N, EXPORTMODE=null, CATKEY=4, USERNAME=admin, FILEDIR=, IMPORTFLAG=Y, USERLOCALE=en}

2015-11-03 12:24:39,166 DEBUG [AIF]: The EpmOracleHome is set to: D:\Oracle\Middleware\EPMSystem11R1

2015-11-03 12:24:39,166 DEBUG [AIF]: The EpmOracleInstance is set to: D:\Oracle\Middleware\user_projects\epmsystem1

2015-11-03 12:24:39,167 DEBUG [AIF]: The JavaHome is set to: %EPM_ORACLE_HOME%/../jdk160_35

2015-11-03 12:24:39,167 DEBUG [AIF]: The executeEventScript is set to: YES

2015-11-03 12:24:39,167 DEBUG [AIF]: The OleDatabaseProvider is set to: SQLOLEDB

2015-11-03 12:24:39,167 DEBUG [AIF]: The AppRootFolder is set to: \\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST

2015-11-03 12:24:39,167 DEBUG [AIF]: Comm.doScriptInit - END

2015-11-03 12:24:39,167 DEBUG [AIF]: Comm.executeScript - START

2015-11-03 12:24:39,168 DEBUG [AIF]: The following script does not exist: \\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST/data/scripts/event/BefImport.py

2015-11-03 12:24:39,171 DEBUG [AIF]: Comm.executeVBScript - START

2015-11-03 12:24:39,171 DEBUG [AIF]: The WindowsTempFolder is set to: c:\windows\temp

2015-11-03 12:24:39,171 DEBUG [AIF]: The following script does not exist: \\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST\data\scripts\event\BefImport.vbs

2015-11-03 12:24:39,171 DEBUG [AIF]: Comm.executeVBScript - END

2015-11-03 12:24:39,172 DEBUG [AIF]: Comm.executeScript - END

2015-11-03 12:24:39,172 DEBUG [AIF]: CommData.preImportData - END

2015-11-03 12:24:42,821 DEBUG [AIF]: FileData.importData - START

2015-11-03 12:24:42,822 DEBUG [AIF]: Importing data for load id: 376

2015-11-03 12:24:42,823 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,

                                  coalesce(br.MULTI_PERIOD_FILE_FLAG, 'N') MULTI_PERIOD_FILE_FLAG

                                FROM aif_bal_rule_loads a,

                                aif_balance_rules br,

                                  tpovperiod b,

                                  aif_processes c

                                WHERE a.loadid       = 376

                                AND a.start_periodkey=b.periodkey

                                AND a.loadid         =c.process_id

                                AND br.rule_id = a.rule_id

                               

2015-11-03 12:24:42,825 DEBUG [AIF]: FileData.py: Importing Text data for load id: 376

2015-11-03 12:24:42,841 INFO  [AIF]: EPMFDM-140274:Message - Log File Name :\\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST\outbox\logs\TEST_376.log

2015-11-03 12:24:42,841 DEBUG [AIF]: EPMFDM-140274:Message - SELECT COALESCE(p.partitionkey,0) PARTITIONKEY,

  COALESCE(brl.catkey,0) CATKEY,

  COALESCE(p.rule_id,0) RULE_ID,

  part.PARTCURRENCYKEY PARTCURRENCYKEY,

  dir.FILE_PATH,

  p.CLUSTER_NAME,

  p.APPLICATION_ID

FROM aif_processes p,

  aif_bal_rule_loads brl,

  tpovpartition part,

  (SELECT COALESCE(

    (SELECT PROFILE_OPTION_VALUE

      + '/'

    FROM AIF_PROFILE_OPTION_VALUES

    WHERE PROFILE_OPTION_NAME = 'PROFILE_APP_ROOT_DIRECTORY'

    AND LEVEL_ID              = 1005

    AND LEVEL_VALUE           = brl.APPLICATION_ID

    ) ,COALESCE(

    (SELECT PROFILE_OPTION_VALUE

      + '/'

    FROM AIF_PROFILE_OPTION_VALUES

    WHERE PROFILE_OPTION_NAME = 'PROFILE_APP_ROOT_DIRECTORY'

    AND LEVEL_ID              = 1000

    ) ,'' ) ,'' )

    + COALESCE(brl.FILE_PATH, '') FILE_PATH,

    brl.LOADID

  FROM AIF_BAL_RULE_LOADS brl

  ) dir

WHERE p.process_id    = ?

AND p.process_id      = dir.loadid

AND brl.loadid        = p.process_id

AND part.partitionkey = p.partitionkey

2015-11-03 12:24:42,842 INFO  [AIF]: EPMFDM-140274:Message - LOADID:PARTKEY:CATKEY:RULEID:CURRENCYKEY:FILEPATH::376;32:4:3:[NONE]:\\orcl-hfm-dev01\d$\Oracle\Middleware\user_projects\domains\EPMSystem\fdmee\TEST/

2015-11-03 12:24:42,847 INFO  [AIF]: EPMFDM-140274:Message - File CharEncoding as per settings: UTF-8

2015-11-03 12:24:42,848 INFO  [AIF]: EPMFDM-140274:Message - ImportTextData - Start

2015-11-03 12:24:42,848 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

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

AND ((br.MULTI_PERIOD_FILE_FLAG in ( 'Y','T','X','E','H')   )

                          OR

    (br.MULTI_PERIOD_FILE_FLAG is null OR br.MULTI_PERIOD_FILE_FLAG  = 'N')

Comments

DStrack

use the function apex_string.split to split clobs. (available since APEX 19.2)
select s.column_value from clob_tab, table(apex_string.split(clob_column,'class:abcd'||chr(10)||'}')) s;

Solomon Yakobson

Assuming you are on 12C or higher:

with sample as (
                select '{
adam smith
class:abcd
}
{
xxxyyyy
class:abcd
}
{
zzzz
class:abcd
}' data from dual
)
select  rownum,
        r
  from  sample,
        lateral(
                select  regexp_substr(data,'{[^}]*}',1,level) r
                  from  dual
                  connect by level <= regexp_count(data,'{')
               )
/

    ROWNUM R
---------- --------------------
         1 {
           adam smith
           class:abcd
           }

         2 {
           xxxyyyy
           class:abcd
           }

         3 {
           zzzz
           class:abcd
           }

SQL>

And if performance is a factor you can change REGEXP_SUBSTR with SUBSTR + INSTR and REGEXP_COUNT with LENGTH + REPLACE.
SY.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 2 2015
Added on Nov 3 2015
8 comments
2,773 views