Forum Stats

  • 3,814,696 Users
  • 2,258,899 Discussions
  • 7,892,808 Comments

Discussions

Error in Comm Map Data

User_DBZQ4
User_DBZQ4 Member Posts: 6 Green Ribbon
edited Feb 5, 2020 5:27AM in Financial Data Management

Hi,

When attempting to import csv file, business rule execution failed and I found that "Transform and stage balane data to export for period yyyymm" didn't work at all as below.

FDMEE ERROR.png

And log :

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

2020-02-03 05:08:01,417 INFO  [AIF]: FDMEE Process: 2923, Log Level: 4, Log File: /u03/inbox/outbox/logs/PBCS4PPS_2923.log

2020-02-03 05:08:01,417 INFO  [AIF]: Location     : PBCS4PPS (Partitionkey:7)

2020-02-03 05:08:01,417 INFO  [AIF]: Period Name  : 201911 (Period Key:11/30/19 12:00 AM)

2020-02-03 05:08:01,417 INFO  [AIF]: Category Name: Actual (Category key:2)

2020-02-03 05:08:01,417 INFO  [AIF]: Rule Name    : PL (Rule ID:9)

2020-02-03 05:08:02,998 INFO  [AIF]: Log File Encoding: Shift_JIS

2020-02-03 05:08:04,067 INFO  [AIF]: -------START IMPORT STEP-------

2020-02-03 05:08:04,316 INFO  [AIF]: File Name: PL201911.csv

2020-02-03 05:08:04,502 INFO  [AIF]: EPMFDM-140274:Message - LOADID:PARTKEY:CATKEY:RULEID:CURRENCYKEY:IMPGROUPFILETYPE::2923;7:2:9:Local::DELIMITED

2020-02-03 05:08:04,508 INFO  [AIF]: EPMFDM-140274:Message - File CharEncoding as per settings: Shift_JIS

2020-02-03 05:08:04,508 INFO  [AIF]: EPMFDM-140274:Message - ImportTextData - Start

2020-02-03 05:08:04,552 INFO  [AIF]: EPMFDM-140274:Message - Initialize error maps

2020-02-03 05:08:04,552 INFO  [AIF]: EPMFDM-140274:Message - File Name PL201911.csv

periodKey2019-11-30

2020-02-03 05:08:04,725 INFO  [AIF]: EPMFDM-140274:Message -  PROCESS ID: 2923

PARTITIONKEY: 7

IMPORT GROUP: PL(Actual)

FILE TYPE: DELIMITED

DELIMITER: ,

SOURCE FILE: PL201911.csv

PROCESSING CODES:

Rows Loaded: 2240
Rows Rejected: 93

2020-02-03 05:08:04,963 INFO  [AIF]: EPMFDM-140274:Message - ARCHIVE MODE: Move
2020-02-03 05:08:04,963 INFO  [AIF]: EPMFDM-140274:Message - Start archiving file:
2020-02-03 05:08:04,964 INFO  [AIF]: EPMFDM-140274:Message - Archive file name: 292320191130.csv
2020-02-03 05:08:04,976 INFO  [AIF]: EPMFDM-140274:Message - Deleting the source file: PL201911.csv
2020-02-03 05:08:04,977 INFO  [AIF]: EPMFDM-140274:Message - Source File deleted: PL201911.csv
2020-02-03 05:08:04,994 INFO  [AIF]: EPMFDM-140274:Message - ImportTextData - End
2020-02-03 05:08:04,994 INFO  [AIF]: EPMFDM-140274:Message - Total time taken for the import in ms = 486
2020-02-03 05:08:05,063 INFO  [AIF]:
Import Data from Source for Period '201911'
2020-02-03 05:08:05,121 INFO  [AIF]: Executing delete statement:
2020-02-03 05:08:05,122 INFO  [AIF]: Successfully executed delete statement
2020-02-03 05:08:05,248 FATAL [AIF]: Error in COMM Map Data
2020-02-03 05:08:05,254 INFO  [AIF]: FDMEE Process End, Process ID: 2923

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

According to this log, this failure seemed to result from Error in COMM Map Data, but I don't know what it is and how to solve this problem.

Could you tell me them?

User_DBZQ4

Answers

  • SH_INT
    SH_INT Member Posts: 3,192 Bronze Crown
    edited Feb 4, 2020 4:04AM

    Can you set the log level to 5 and re-run the process generating the mapping error as this will provide more detail in the log of the the statements being executed and help with debug

  • User_DBZQ4
    User_DBZQ4 Member Posts: 6 Green Ribbon
    edited Feb 4, 2020 4:14AM

    Thank you for your advising!  I'll set and re-try. ;-)

  • User_DBZQ4
    User_DBZQ4 Member Posts: 6 Green Ribbon
    edited Feb 4, 2020 4:29AM

    Thanks to your advice, I got to know more detail log and error occured in executing UPDATE sql.

    However, I still don't know how to solve this....

    2020-02-04 09:19:20,439 INFO  [AIF]: FDMEE Process: 2945, Log Level: 5, Log File: /u03/inbox/outbox/logs/PBCS4PPS_2945.log

    2020-02-04 09:19:20,439 INFO  [AIF]: Location     : PBCS4PPS (Partitionkey:7)

    2020-02-04 09:19:20,439 INFO  [AIF]: Period Name  : 201911 (Period Key:11/30/19 12:00 AM)

    2020-02-04 09:19:20,439 INFO  [AIF]: Category Name: Actual (Category key:2)

    2020-02-04 09:19:20,439 INFO  [AIF]: Rule Name    : PL (Rule ID:9)

    2020-02-04 09:19:22,185 DEBUG [AIF]: FDMEE Version: 11.1.2.4.000, p1_2001_002.drv

    2020-02-04 09:19:22,185 INFO  [AIF]: Log File Encoding: Shift_JIS

    2020-02-04 09:19:23,449 INFO  [AIF]: -------START IMPORT STEP-------

    2020-02-04 09:19:23,582 DEBUG [AIF]: FileData.extractDataFromSource - START

    2020-02-04 09:19:23,583 DEBUG [AIF]: CommData.getRuleInfo - START

    2020-02-04 09:19:23,585 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 = 11

          LEFT OUTER JOIN TBHVIMPITEMERPI tiie

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

          WHERE tpp.PARTITIONKEY = 7

          AND adim.BALANCE_COLUMN_NAME IS NOT NULL

          AND adim.TARGET_DIMENSION_CLASS_NAME <> 'ICPTRANS'

          AND (adim.VALID_FOR_PLAN1 = 1 OR adim.TARGET_DIMENSION_CLASS_NAME = 'LOOKUP')

          ORDER BY adim.BALANCE_COLUMN_NAME

         

    2020-02-04 09:19:23,590 DEBUG [AIF]: SELECT property_value FROM aif_target_appl_properties WHERE application_id = 11 and property_type = 'WORKFLOW_MODE'

    2020-02-04 09:19:23,591 DEBUG [AIF]: {'PLAN_NUMBER': 1L, 'EXPORT_TO_TARGET_FLAG': u'Y', 'SRC_TARGET_APP_NAME': 'NA', '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'Version'}, u'UD3': {'DIMNAME': u'UD3', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD6': {'DIMNAME': u'UD6', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD5': {'DIMNAME': u'UD5', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD8': {'DIMNAME': u'UD8', 'TARGET_DIMENSION_CLASS_NAME': u'Generic'}, u'UD10': {'DIMNAME': u'UD10', 'TARGET_DIMENSION_CLASS_NAME': u'LOOKUP'}, u'UD7': {'DIMNAME': u'UD7', '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'Entity'}}, 'CATKEY': 2L, 'TGT_APP_SUB_TYPE': None, 'TGT_APP_ID': 11L, 'DIMNAME_LIST': [u'ACCOUNT', u'ENTITY', u'UD1', u'UD10', u'UD2', u'UD3', u'UD5', u'UD6', u'UD7', u'UD8'], 'SRC_SOURCE_SYSTEM_ID': 1L, 'TGT_SOURCE_SYSTEM_TYPE': None, 'TGT_APP_TYPE': u'HPL', 'CALENDAR_ID': None, 'TGT_TARGET_APP_NAME': u'PBCS4PPS', 'PARTITIONKEY': 7L, 'EXECUTION_MODE': u'REPLACE', 'LEDGER_GROUP_CODE': u'NONE', 'PS_LEDGER': None, 'SRC_DATA_LOAD_METHOD': 'NA', 'PARTVALGROUP': u'[NONE]', 'WEB_SERVICE_URL': u'http://ap1pbcsr304487.audc1.oraclecloud.com:9000/aif', 'IMPORT_FROM_SOURCE_FLAG': u'Y', 'IS_INCREMENTAL_LOAD': False, 'INCLUDE_ADJ_PERIODS_FLAG': u'N', 'SRC_APP_NAME': 'NA', 'FCI_FLAG': 'N', 'KK_FLAG': u'N', 'LAST_UPDATED_BY': u'[email protected]', 'CHECK_FLAG': u'Y', 'RULE_ID': 9L, 'PERIOD_MAPPING_TYPE': u'DEFAULT', 'SRC_APP_ID': None, 'INCLUDE_ZERO_BALANCE_FLAG': None, 'IMPGROUPKEY': u'PL(Actual)', 'WORKFLOW_MODE': 'FULL', 'TGT_SOURCE_SYSTEM_ID': 0L, 'SRC_APP_TYPE': 'NA', 'TGT_APP_NAME': u'PBCS4PPS', 'IMPGROUPFILETYPE': u'DELIMITED', 'LEDGER_GROUP': None, 'TGT_COA_ID': 0L, 'SOURCE_ADAPTER_FLAG': u'N', 'RECALCULATE_FLAG': u'Y', 'TGT_DATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI', 'SRC_SOURCE_SYSTEM_TYPE': u'FILE', 'PLAN_TYPE': u'PLAN1', 'TGT_METADATA_LOAD_METHOD': u'CLASSIC_VIA_EPMI'}

    2020-02-04 09:19:23,593 DEBUG [AIF]: {u'Account': {'BCN': u'ACCOUNT', 'DCN': u'Account'}, u'Entity': {'BCN': u'ENTITY', 'DCN': u'Entity'}, u'Years': {'BCN': None, 'DCN': u'Year'}, u'Period': {'BCN': None, 'DCN': u'Period'}, u'Product': {'BCN': u'UD3', 'DCN': u'Generic'}, u'Salesmethod': {'BCN': u'UD5', 'DCN': u'Generic'}, u'Scenario': {'BCN': None, 'DCN': u'Scenario'}, u'Suppliers': {'BCN': u'UD6', 'DCN': u'Generic'}, u'Allocation': {'BCN': u'UD7', 'DCN': u'Generic'}, u'Customers': {'BCN': u'UD8', 'DCN': u'Generic'}, u'Version': {'BCN': u'UD1', 'DCN': u'Version'}, u'Currency': {'BCN': None, 'DCN': u'Currency'}, u'HSP_View': {'BCN': u'UD2', 'DCN': u'Generic'}}

    2020-02-04 09:19:23,595 DEBUG [AIF]: {u'UD2': {'IMPFLDSOURCECOLNAME': u'HSP_View'}, u'UD1': {'IMPFLDSOURCECOLNAME': u'Version'}, u'UD4': {'IMPFLDSOURCECOLNAME': u'\u53d6\u5f15\u5148'}, u'UD3': {'IMPFLDSOURCECOLNAME': u'\u88fd\u54c1'}, u'UD6': {'IMPFLDSOURCECOLNAME': u'\u4ed5\u5165\u5148'}, u'UD5': {'IMPFLDSOURCECOLNAME': u'\u8ca9\u58f2\u65b9\u6cd5'}, u'UD8': {'IMPFLDSOURCECOLNAME': u'\u53d6\u5f15\u5148'}, u'UD10': {'IMPFLDSOURCECOLNAME': u'CHECK'}, u'UD7': {'IMPFLDSOURCECOLNAME': u'\u914d\u8ce6'}, u'UD9': {'IMPFLDSOURCECOLNAME': u'Currency'}, u'ACCOUNT': {'IMPFLDSOURCECOLNAME': u'\u79d1\u76ee'}, u'ENTITY': {'IMPFLDSOURCECOLNAME': u'\u7d44\u7e54'}, u'AMOUNT': {'IMPFLDSOURCECOLNAME': u'\u91d1\u984d'}, u'UD12': {'IMPFLDSOURCECOLNAME': u'\u53d6\u5f15\u5148'}, u'DESC1': {'IMPFLDSOURCECOLNAME': u'\u4f1d\u7968\u6458\u8981'}}

    2020-02-04 09:19:23,595 DEBUG [AIF]: CommData.getRuleInfo - END

    2020-02-04 09:19:23,596 DEBUG [AIF]: FileData.extractDataFromSource - END

    2020-02-04 09:19:23,618 DEBUG [AIF]: FileData.preProcessDataFile - START

    2020-02-04 09:19:23,657 DEBUG [AIF]: FileData.preProcessDataFile - END

    2020-02-04 09:19:23,680 DEBUG [AIF]: CommData.preImportData - START

    2020-02-04 09:19:23,680 DEBUG [AIF]: CommData.insertFilePeriods - START

    2020-02-04 09:19:23,682 DEBUG [AIF]: doAppPeriodMappingsExist - PBCS4PPS: N

    2020-02-04 09:19:23,682 DEBUG [AIF]: SELECT 1 FROM AIF_BAL_RULE_LOAD_PARAMS WHERE LOADID = 2945 AND PARAMETER_TYPE = 'SOURCE_PERIODS'

    2020-02-04 09:19:23,683 DEBUG [AIF]: Period mapping section: SINGLE-COLUMN/DEFAULT

    2020-02-04 09:19:23,683 DEBUG [AIF]:

                SELECT pp.PERIODKEY, pp.PERIODDESC, pp.YEARTARGET, pp.PERIODTARGET, brl.FILE_NAME_STATIC, brl.FILE_NAME_SUFFIX_TYPE, brl.FILE_NAME_DATE_FORMAT

                      ,0 GL_EFFECTIVE_PERIOD_NUM, 0 GL_PERIOD_NUM, '0' GL_PERIOD_NAME, 0 GL_PERIOD_YEAR

                FROM AIF_BAL_RULE_LOADS brl

                INNER JOIN TPOVCATEGORY pc

                  ON pc.CATKEY = brl.CATKEY

            INNER JOIN TPOVPERIOD_FLAT_V pp

              ON pp.PERIODFREQ = pc.CATFREQ AND pp.PERIODKEY >= brl.START_PERIODKEY AND pp.PERIODKEY <= brl.END_PERIODKEY

                WHERE brl.LOADID = 2945

                ORDER BY PERIODKEY

               

    2020-02-04 09:19:23,684 INFO  [AIF]: File Name: PL201911.csv

    2020-02-04 09:19:23,685 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, GL_PERIOD_YEAR_CHAR)

            VALUES (2945,'2019-11-30','N',0,'0' ,'0',0,1,'FY19','Nov', 'PROCESS_BAL_IMP_FILE','PL201911.csv','PROCESS_BAL_TRANS','201911','N', 0)

           

    2020-02-04 09:19:23,687 DEBUG [AIF]: insertRowCount: 1

    2020-02-04 09:19:23,688 DEBUG [AIF]: CommData.insertFilePeriods - END

    2020-02-04 09:19:23,688 DEBUG [AIF]: CommData.getPovList - START

    2020-02-04 09:19:23,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 TPOVPERIOD pp

              ON pp.PERIODKEY = pprd.PERIODKEY

            LEFT OUTER JOIN TLOGPROCESS tlp

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

            WHERE brl.LOADID = 2945

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

           

    2020-02-04 09:19:23,689 DEBUG [AIF]: CommData.getPovList - END

    2020-02-04 09:19:23,690 DEBUG [AIF]: CommData.preImportData - END

    2020-02-04 09:19:23,712 DEBUG [AIF]: CommData.insertImportProcessDetails - START

    2020-02-04 09:19:23,713 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, '[email protected]' 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 = 2945

              ) q

              ORDER BY ENTITY_NAME_ORDER

             

    2020-02-04 09:19:23,730 DEBUG [AIF]: Comm.doScriptInit - START

    2020-02-04 09:19:23,786 DEBUG [AIF]: fdmContext: {PERIODKEY=2019-11-30, CATKEY=2, APPNAME=PBCS4PPS, EXPORTFLAG=Y, SOURCEID=1, TARGETAPPNAME=PBCS4PPS, USERLOCALE=en, IMPORTFLAG=Y, SOURCENAME=File, PERIODNAME=201911, LOCNAME=PBCS4PPS, [email protected], FILENAME=PL201911.csv, TARGETAPPTYPE=HPL, TARGETAPPDB=PLAN1, FILEDIR=inbox, APPID=11, MULTIPERIODLOAD=N, LOADID=2945, RULEID=9, IMPORTMODE=REPLACE, SOURCETYPE=FILE, CATNAME=Actual, RULENAME=PL, LOCKEY=7, EXPORTMODE=STORE_DATA, IMPORTFORMAT=PBCS4PPS}

    2020-02-04 09:19:23,788 DEBUG [AIF]: The executeEventScript is set to: NO

    2020-02-04 09:19:23,789 DEBUG [AIF]: The OleDatabaseProvider is set to: ORAOLEDB.ORACLE

    2020-02-04 09:19:23,789 DEBUG [AIF]: Comm.doScriptInit - END

    2020-02-04 09:19:23,789 DEBUG [AIF]: CommData.insertImportProcessDetails - END

    2020-02-04 09:19:23,812 DEBUG [AIF]: FileData.importData - START

    2020-02-04 09:19:23,813 DEBUG [AIF]: Importing data for load id: 2945

    2020-02-04 09:19:23,813 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,

                     tpovperiod b,

                     aif_processes c

                WHERE a.loadid       = 2945

                AND a.start_periodkey=b.periodkey

                AND a.loadid         =c.process_id

                AND br.rule_id = a.rule_id

               

    2020-02-04 09:19:23,815 DEBUG [AIF]: FileData.py: Importing Text data for load id: 2945

    2020-02-04 09:19:23,885 INFO  [AIF]: EPMFDM-140274:Message - LOADID:PARTKEY:CATKEY:RULEID:CURRENCYKEY:IMPGROUPFILETYPE::2945;7:2:9:Local::DELIMITED

    2020-02-04 09:19:23,890 INFO  [AIF]: EPMFDM-140274:Message - File CharEncoding as per settings: Shift_JIS

    2020-02-04 09:19:23,890 INFO  [AIF]: EPMFDM-140274:Message - ImportTextData - Start

    2020-02-04 09:19:23,891 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

    2020-02-04 09:19:23,936 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=AMOUNT:ImpFieldText=null:StartPos=90:SourceClName=金額:ImpFldTargetExp=null

    2020-02-04 09:19:23,936 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD12:ImpFieldText=null:StartPos=84:SourceClName=取引先:ImpFldTargetExp=null

    2020-02-04 09:19:23,936 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD4:ImpFieldText=null:StartPos=84:SourceClName=取引先:ImpFldTargetExp=null

    2020-02-04 09:19:23,936 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD9:ImpFieldText=Local:StartPos=1:SourceClName=Currency:ImpFldTargetExp=null

    2020-02-04 09:19:23,937 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=DESC1:ImpFieldText=null:StartPos=14:SourceClName=伝票摘要:ImpFldTargetExp=null

    2020-02-04 09:19:23,937 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ACCOUNT:ImpFieldText=null:StartPos=91:SourceClName=科目:ImpFldTargetExp=null

    2020-02-04 09:19:23,937 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=ENTITY:ImpFieldText=null:StartPos=83:SourceClName=組織:ImpFldTargetExp=null

    2020-02-04 09:19:23,937 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD1:ImpFieldText=BUVersion:StartPos=1:SourceClName=Version:ImpFldTargetExp=null

    2020-02-04 09:19:23,937 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD10:ImpFieldText=null:StartPos=1:SourceClName=CHECK:ImpFldTargetExp=null

    2020-02-04 09:19:23,937 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD2:ImpFieldText=BaseData:StartPos=1:SourceClName=HSP_View:ImpFldTargetExp=null

    2020-02-04 09:19:23,938 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD3:ImpFieldText=null:StartPos=85:SourceClName=製品:ImpFldTargetExp=null

    2020-02-04 09:19:23,938 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD5:ImpFieldText=null:StartPos=87:SourceClName=販売方法:ImpFldTargetExp=null

    2020-02-04 09:19:23,938 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD6:ImpFieldText=none:StartPos=1:SourceClName=仕入先:ImpFldTargetExp=null

    2020-02-04 09:19:23,938 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD7:ImpFieldText=Direct:StartPos=1:SourceClName=配賦:ImpFldTargetExp=null

    2020-02-04 09:19:23,938 DEBUG [AIF]: EPMFDM-140274:Message - ImpFieldName=UD8:ImpFieldText=null:StartPos=84:SourceClName=取引先:ImpFldTargetExp=null

    2020-02-04 09:19:23,939 INFO  [AIF]: EPMFDM-140274:Message - Initialize error maps

    2020-02-04 09:19:23,939 INFO  [AIF]: EPMFDM-140274:Message - File Name PL201911.csv

    periodKey2019-11-30

    2020-02-04 09:19:24,098 INFO  [AIF]: EPMFDM-140274:Message -  PROCESS ID: 2945

    PARTITIONKEY: 7

    IMPORT GROUP: PL(Actual)

    FILE TYPE: DELIMITED

    DELIMITER: ,

    SOURCE FILE: PL201911.csv

    PROCESSING CODES:

    (Snip)

    2020-02-04 09:19:24,337 DEBUG [AIF]: EPMFDM-140274:Message - Script- archiveDataFile- Start

    2020-02-04 09:19:24,338 INFO  [AIF]: EPMFDM-140274:Message - ARCHIVE MODE: Move

    2020-02-04 09:19:24,338 INFO  [AIF]: EPMFDM-140274:Message - Start archiving file:

    2020-02-04 09:19:24,338 DEBUG [AIF]: EPMFDM-140274:Message - ArchiveId: 294520191130

    2020-02-04 09:19:24,338 DEBUG [AIF]: EPMFDM-140274:Message - Archive- SourceFileName: PL201911

    Extension: csv

    2020-02-04 09:19:24,339 INFO  [AIF]: EPMFDM-140274:Message - Archive file name: 294520191130.csv

    2020-02-04 09:19:24,352 INFO  [AIF]: EPMFDM-140274:Message - Deleting the source file: PL201911.csv

    2020-02-04 09:19:24,367 INFO  [AIF]: EPMFDM-140274:Message - Source File deleted: PL201911.csv

    2020-02-04 09:19:24,370 DEBUG [AIF]: EPMFDM-140274:Message - ArchiveDocKey:[email protected]-20200204_091924-1

    2020-02-04 09:19:24,375 DEBUG [AIF]: EPMFDM-140274:Message - Script- archiveDataFile- End

    2020-02-04 09:19:24,382 INFO  [AIF]: EPMFDM-140274:Message - ImportTextData - End

    2020-02-04 09:19:24,382 INFO  [AIF]: EPMFDM-140274:Message - Total time taken for the import in ms = 492

    2020-02-04 09:19:24,388 DEBUG [AIF]: FileData.importData - END

    2020-02-04 09:19:24,454 DEBUG [AIF]: CommData.postImportData - START

    2020-02-04 09:19:24,455 DEBUG [AIF]: CommData.updateTDATASEG_T - START

    2020-02-04 09:19:24,455 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    = 2945

              AND en.process_id      = pp.process_id

              AND pp.IMP_ENTITY_NAME = en.file_entity_name

              order by en.entity_name_order

             

    2020-02-04 09:19:24,456 INFO  [AIF]:

    Import Data from Source for Period '201911'

    2020-02-04 09:19:24,456 DEBUG [AIF]: CommData.updateWorkflow - START

    2020-02-04 09:19:24,456 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 = 7 AND tlp.CATKEY = 2 AND tlp.PERIODKEY = '2019-11-30' AND tlp.RULE_ID = 9 AND tlps.PROCESSSTATUSKEY = tlp.PROCESSSTATUS

       

    2020-02-04 09:19:24,458 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=7 AND CATKEY=2 AND PERIODKEY='2019-11-30' AND RULE_ID=9

           

    2020-02-04 09:19:24,460 DEBUG [AIF]: CommData.updateWorkflow - END

    2020-02-04 09:19:24,461 DEBUG [AIF]: CommData.updateTDATASEG_T - END

    2020-02-04 09:19:24,461 DEBUG [AIF]: CommData.postImportData - END

    2020-02-04 09:19:24,482 DEBUG [AIF]: LogicGroup.createLogicAccounts - START

    2020-02-04 09:19:24,482 DEBUG [AIF]: CommLogicGroup.getRuleInfo - START

    2020-02-04 09:19:24,483 DEBUG [AIF]:

            select part.PARTLOGICGROUP, lgrp.CALCGROUPTYPE

            from AIF_PROCESSES prs, TPOVPARTITION part,TBHVLOGICGROUP lgrp

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

            and part.PARTLOGICGROUP=lgrp.CALCGROUPKEY 

           

    2020-02-04 09:19:24,484 DEBUG [AIF]: CommLogicGroup.getRuleInfo - END

    2020-02-04 09:19:24,484 DEBUG [AIF]: LogicGroup.createLogicAccounts - END

    2020-02-04 09:19:24,505 DEBUG [AIF]: CommData.mapData - START

    2020-02-04 09:19:24,505 DEBUG [AIF]: CommData.insertTransProcessDetails - START

    2020-02-04 09:19:24,506 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, '[email protected]' 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 = 2945

                AND PRIOR_PERIOD_FLAG = 'N'

                GROUP BY PROCESS_ID, TRANS_ENTITY_TYPE, TRANS_ENTITY_NAME

              ) q

              ORDER BY ENTITY_NAME_ORDER

             

    2020-02-04 09:19:24,508 DEBUG [AIF]: CommData.insertTransProcessDetails - END

    2020-02-04 09:19:24,508 DEBUG [AIF]:

            DELETE FROM TDATAMAP_T

            WHERE LOADID < 2945

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

           

    2020-02-04 09:19:24,511 DEBUG [AIF]:

              SELECT COUNT(*) FROM TDATASEG_T

              WHERE LOADID < 2945

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

             

    2020-02-04 09:19:24,513 DEBUG [AIF]: intermediateCommit: True, skipRegularSql: False

    2020-02-04 09:19:24,513 INFO  [AIF]: Executing delete statement:

    2020-02-04 09:19:24,513 DEBUG [AIF]:

            DELETE FROM TDATASEG_T

            WHERE LOADID < 2945

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

           

    2020-02-04 09:19:24,718 INFO  [AIF]: Successfully executed delete statement

    2020-02-04 09:19:24,719 DEBUG [AIF]:

            DELETE FROM AIF_PROCESS_PERIODS

            WHERE PROCESS_ID < 2945

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

           

    2020-02-04 09:19:24,721 DEBUG [AIF]: CommMap.loadTDATAMAP_T - START

    2020-02-04 09:19:24,721 DEBUG [AIF]: CommData.getMapPartitionKeyandName - START

    2020-02-04 09:19:24,721 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 = 7

           

    2020-02-04 09:19:24,722 DEBUG [AIF]: CommData.getMapPartitionKeyandName - END

    2020-02-04 09:19:24,723 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 2945, DATAKEY, 7 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 = 7

            AND ( RULE_ID IS NULL OR RULE_ID = 9 )

           

    2020-02-04 09:19:24,725 DEBUG [AIF]: Number of Rows inserted into TDATAMAP_T: 22

    2020-02-04 09:19:24,726 DEBUG [AIF]: CommMap.loadTDATAMAP_T - END

    2020-02-04 09:19:24,726 DEBUG [AIF]: CommMap.updateTDATASEG_T_TDATASEGW - START

    2020-02-04 09:19:24,726 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 = 2945

              AND atad.APPLICATION_ID = 11

              AND tdm.PARTITIONKEY = 7

              AND tdm.TDATAMAPTYPE = 'ERP'

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

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

              AND atad.APPLICATION_ID = 11

              AND tdm.PARTITIONKEY = 7

              AND tdm.TDATAMAPTYPE = 'ERP'

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

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

            ) q

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

           

    2020-02-04 09:19:24,732 DEBUG [AIF]: SELECT VBSCRIPT FROM TDATAMAP_T WHERE LOADID = 2945 AND PARTITIONKEY = 7 AND DATAKEY = 59

    2020-02-04 09:19:24,805 DEBUG [AIF]: Comm.finalizeProcess - START

    2020-02-04 09:19:24,806 DEBUG [AIF]: CommData.updateRuleStatus - START

    2020-02-04 09:19:24,806 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 = 2945 AND pd.STATUS IN ('FAILED','WARNING') )

              WHEN 0 THEN 'SUCCESS'

              ELSE ( SELECT MIN(pd.STATUS) FROM AIF_PROCESS_DETAILS pd WHERE pd.PROCESS_ID = 2945 AND pd.STATUS IN ('FAILED','WARNING') ) END

          ELSE 'FAILED' END

        WHERE RULE_ID = 9

       

    2020-02-04 09:19:24,809 DEBUG [AIF]: CommData.updateRuleStatus - END

    2020-02-04 09:19:24,809 FATAL [AIF]: Error in COMM Map Data

    2020-02-04 09:19:24,809 DEBUG [AIF]: Comm.updateProcess - START

    2020-02-04 09:19:24,811 DEBUG [AIF]: Comm.updateProcess - END

    2020-02-04 09:19:24,813 DEBUG [AIF]: The fdmAPI connection has been closed.

    2020-02-04 09:19:24,813 DEBUG [AIF]: Comm.finalizeProcess - END

    2020-02-04 09:19:24,813 INFO  [AIF]: FDMEE Process End, Process ID: 2945

  • SH_INT
    SH_INT Member Posts: 3,192 Bronze Crown
    edited Feb 4, 2020 6:17AM

    Check your mappings, do you have any mapping scripts? I'd check those first for syntax errors

    User_DBZQ4
  • User_DBZQ4
    User_DBZQ4 Member Posts: 6 Green Ribbon
    edited Feb 4, 2020 8:19PM

    You mean import format mapping? (Sorry for my poor understanding...) If so, PL(Actual) mapping :

    Account : Column 91

    Allocation : Column 1 (Direct)

    Amount : Column 90

    CHECK : Coulumn 1

    Customers : Column 84

    Discription 1 : Column 14

    Entity : Column 83

    HSP_View : Column 1 (BaseData)

    Product : Column 85

    Salesmethod : Column 87

    Suppliers : Column 1 (none)

    Version : Coulumn 1 (BUVersion).

  • SureshM-Oracle
    SureshM-Oracle Member Posts: 407 Employee
    edited Feb 5, 2020 4:19AM

    Please confirm if data has been imported successfully in data load workbench and it is failing only during the validation? If yes, did you see if you have any validation error?

    What's the encoding format of the file? You can check the encoding method in the notepad++ for the file you are importing here?

    What condition is being used in the business rules?

    You could try to reset the service and see if this helps.

  • User_DBZQ4
    User_DBZQ4 Member Posts: 6 Green Ribbon
    edited Feb 5, 2020 5:27AM

    Thanks for replying. ;-)

    >Please confirm if data has been imported successfully in data load workbench

    I found that data has been imported successfully in data load workbench. (Until you pointed out it, I didn't confirm data load workbench.... Thank you for advising!)

    >and it is failing only during the validation?

    Sorry, I don't know how to confirm it.... Is it possible to confirm from log from monitor process details?

    >What's the encoding format of the file?

    The csv file was encoded in Shift-JIS.

    >What condition is being used in the business rules?

    All business rules seem to be successfully completed as below (Sorry in Japanese).

    business rule condition.png

  • MichaelReed
    MichaelReed Member Posts: 38 Bronze Badge

    Was this resolved? We are having an issue where an import will fail 2 or 3 times then successfully import the next upload. We make no changes and all of a sudden it works. This is on the import step not validation, at least we are getting the grey fish on import.

  • User_DBZQ4
    User_DBZQ4 Member Posts: 6 Green Ribbon

    Yes, it was resolved.

    Do you use an epmautomate - command "runbusinessrule" perhaps, when importing a csv-file?

    If you want to execute not a business-rule but a ruleset (*), you have to use an epmautomate - command "runRuleSet" after getting latest version of EPM Automate.

    (*) ruleset : a series of business-rules

  • MichaelReed
    MichaelReed Member Posts: 38 Bronze Badge

    We do not. I will wait on our SR to be answered