9 Replies Latest reply: Oct 2, 2013 7:01 AM by 1007772 RSS

    DAC: failed task during ETL for financial apps

    1007772

      I am trying  my first ETL on OBIA 7.9.6.4

      i'm using  Oracle EBS 12.1.1 as source system.

       

       

       

      the ETL completes 314 tasks successfully ,but it fails the task named:

      "SDE_ORA_GL_AR_REV_LinkageInformation_Extract"

       

      DAC Error log:

       

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

      STD OUTPUT

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

       

       

      Informatica(r) PMCMD, version [9.1.0 HotFix2], build [357.0903], Windows 32-bit

      Copyright (c) Informatica Corporation 1994 - 2011

      All Rights Reserved.

       

       

      Invoked at Wed Sep 18 09:46:41 2013

       

       

      Connected to Integration Service: [infor_int].

      Folder: [SDE_ORAR1211_Adaptor]

      Workflow: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

      Instance: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

      Mapping: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract]

      Session log file: [C:\Informatica\server\infa_shared\SessLogs\.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full.ORA_R1211.log]

      Source success rows: [0]

      Source failed rows: [0]

      Target success rows: [0]

      Target failed rows: [0]

      Number of transformation errors: [0]

      First error code [4035]

      First error message: [RR_4035 SQL Error [

      ORA-00904: "XLA_EVENTS"."UPG_BATCH_ID": invalid identifier

       

       

      Database driver error...

      Function Name : Execute

      SQL Stmt : SELECT DISTINCT

       

       

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

       

       

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

       

       

      AELINE.ACCOUNTING_CLASS_CODE,

       

       

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

       

       

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

       

       

      AELINE.AE_HEADER_ID AE_HEADER_ID,

       

       

      AELINE.AE_LINE_NUM AE_LINE_NUM,

       

       

      T.LEDGER_ID LEDGER_ID,

       

       

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

       

       

          JBATCH.NAME BATCH_NAME,

       

       

         JHEADER.NAME HEADER_NAME,

       

       

            PER.END_DATE,

       

       

      AELINE.CODE_COMBINATI]

      Task run status: [Failed]

      Integration Service: [infor_int]

      Integration Service Process: [infor_int]

      Integration Service Grid: [infor_int]

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

      Node Name(s) [node01_AMAZON-9C628AAE]

      Preparation fragment

       

       

      Partition: [Partition #1]

      Transformation instance: [SQ_XLA_AE_LINES]

      Transformation: [SQ_XLA_AE_LINES]

      Applied rows: [0]

      Affected rows: [0]

      Rejected rows: [0]

      Throughput(Rows/Sec): [0]

      Throughput(Bytes/Sec): [0]

      Last error code [16004], message [ERROR: Prepare failed. : [

      ORA-00904: "XLA_EVENTS"."UPG_BATCH_ID": invalid identifier

       

       

      Database driver error...

      Function Name : Execute

      SQL Stmt : SELECT DISTINCT

       

       

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

       

       

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

       

       

      AELINE.ACCOUNTING_CLASS_CODE,

       

       

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

       

       

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

       

       

      AELINE.AE_HEADER_ID AE_HEADER_ID,

       

       

      AELINE.AE_LINE_NUM AE_LINE_NUM,

       

       

      T.LEDGER_ID LEDGER_ID,

       

       

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

       

       

          JBATCH.NAME BATCH_NAME,

       

       

         JHEADER.NAME HEADER_NAME,

       

       

            PER.END_DATE,

       

       

      AELINE.CODE_CO]

      Start time: [Wed Sep 18 09:46:13 2013]

      End time: [Wed Sep 18 09:46:13 2013]

       

       

      Partition: [Partition #1]

      Transformation instance: [W_GL_LINKAGE_INFORMATION_GS]

      Transformation: [W_GL_LINKAGE_INFORMATION_GS]

      Applied rows: [0]

      Affected rows: [0]

      Rejected rows: [0]

      Throughput(Rows/Sec): [0]

      Throughput(Bytes/Sec): [0]

      Last error code [0], message [No errors encountered.]

      Start time: [Wed Sep 18 09:46:14 2013]

      End time: [Wed Sep 18 09:46:14 2013]

       

      Disconnecting from Integration Service

       

      Completed at Wed Sep 18 09:46:41 2013

       

       

       

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

      Informatica session logs:

      DIRECTOR> VAR_27028 Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].

      DIRECTOR> VAR_27028 Use override value [ORA_R1211] for session parameter:[$DBConnection_OLTP].

      DIRECTOR> VAR_27028 Use override value [.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full.ORA_R1211.log] for session parameter:[$PMSessionLogFile].

      DIRECTOR> VAR_27028 Use override value [26] for mapping parameter:[$$DATASOURCE_NUM_ID].

      DIRECTOR> VAR_27028 Use override value ['N'] for mapping parameter:[$$FILTER_BY_LEDGER_ID].

      DIRECTOR> VAR_27028 Use override value ['N'] for mapping parameter:[$$FILTER_BY_LEDGER_TYPE].

      DIRECTOR> VAR_27028 Use override value [] for mapping parameter:[$$Hint1].

      DIRECTOR> VAR_27028 Use override value [01/01/1970] for mapping parameter:[$$INITIAL_EXTRACT_DATE].

      DIRECTOR> VAR_27028 Use override value [01/01/1990] for mapping parameter:[$$LAST_EXTRACT_DATE].

      DIRECTOR> VAR_27028 Use override value [1] for mapping parameter:[$$LEDGER_ID_LIST].

      DIRECTOR> VAR_27028 Use override value ['NONE'] for mapping parameter:[$$LEDGER_TYPE_LIST].

      DIRECTOR> TM_6014 Initializing session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] at [Wed Sep 18 09:46:13 2013].

      DIRECTOR> TM_6683 Repository Name: [infor_rep]

      DIRECTOR> TM_6684 Server Name: [infor_int]

      DIRECTOR> TM_6686 Folder: [SDE_ORAR1211_Adaptor]

      DIRECTOR> TM_6685 Workflow: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] Run Instance Name: [] Run Id: [2130]

      DIRECTOR> TM_6101 Mapping name: SDE_ORA_GL_AR_REV_LinkageInformation_Extract [version 1].

      DIRECTOR> TM_6963 Pre 85 Timestamp Compatibility is Enabled

      DIRECTOR> TM_6964 Date format for the Session is [MM/DD/YYYY HH24:MI:SS]

      DIRECTOR> TM_6827 [C:\Informatica\server\infa_shared\Storage] will be used as storage directory for session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full].

      DIRECTOR> CMN_1805 Recovery cache will be deleted when running in normal mode.

      DIRECTOR> CMN_1802 Session recovery cache initialization is complete.

      DIRECTOR> TM_6708 Using configuration property [DisableDB2BulkMode ,Yes]

      DIRECTOR> TM_6708 Using configuration property [OraDateToTimestamp ,Yes]

      DIRECTOR> TM_6708 Using configuration property [overrideMpltVarWithMapVar,Yes]

      DIRECTOR> TM_6708 Using configuration property [SiebelUnicodeDB,[APPS]@[ 54.225.65.108:1521:VIS] [DWH_REP2]@[AMAZON-9C628AAE:1521:obiaDW1]]

       

       

      DIRECTOR> TM_6703 Session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] is run by 32-bit Integration Service  [node01_AMAZON-9C628AAE], version [9.1.0 HotFix2], build [0903].

      MANAGER> PETL_24058 Running Partition Group [1].

      MANAGER> PETL_24000 Parallel Pipeline Engine initializing.

      MANAGER> PETL_24001 Parallel Pipeline Engine running.

      MANAGER> PETL_24003 Initializing session run.

      MAPPING> CMN_1569 Server Mode: [ASCII]

      MAPPING> CMN_1570 Server Code page: [MS Windows Latin 1 (ANSI), superset of Latin1]

      MAPPING> TM_6151 The session sort order is [Binary].

      MAPPING> TM_6156 Using low precision processing.

      MAPPING> TM_6180 Deadlock retry logic will not be implemented.

      MAPPING> TM_6187 Session target-based commit interval is [10000].

      MAPPING> TM_6307 DTM error log disabled.

      MAPPING> TE_7022 TShmWriter: Initialized

      MAPPING> TE_7004 Transformation Parse Warning [IIF(EVENT_TYPE_CODE='RECP_REVERSE',

       

       

      IIF(UPG_BATCH_ID>0,

       

       

      SOURCE_TABLE || '~' || DISTRIBUTION_ID,

       

       

      SOURCE_TABLE || '~RECEIPTREVERSE~' || DISTRIBUTION_ID),

       

       

      SOURCE_TABLE || '~' || DISTRIBUTION_ID)

       

       

      ]; transformation continues...

      MAPPING> TE_7004 Transformation Parse Warning [<<PM Parse Warning>> [||]: operand converted to a string

      ... IIF(EVENT_TYPE_CODE='RECP_REVERSE',

       

       

      IIF(UPG_BATCH_ID>0,

       

       

      SOURCE_TABLE || '~' || >>>>DISTRIBUTION_ID<<<<,

       

       

      SOURCE_TABLE || '~RECEIPTREVERSE~' || DISTRIBUTION_ID),

       

       

      SOURCE_TABLE || '~' || DISTRIBUTION_ID)

       

       

      <<PM Parse Warning>> [||]: operand converted to a string

      ... IIF(EVENT_TYPE_CODE='RECP_REVERSE',

       

       

      IIF(UPG_BATCH_ID>0,

       

       

      SOURCE_TABLE || '~' || DISTRIBUTION_ID,

       

       

      SOURCE_TABLE || '~RECEIPTREVERSE~' || >>>>DISTRIBUTION_ID<<<<),

       

       

      SOURCE_TABLE || '~' || DISTRIBUTION_ID)

       

       

      <<PM Parse Warning>> [||]: operand converted to a string

      ... IIF(EVENT_TYPE_CODE='RECP_REVERSE',

       

       

      IIF(UPG_BATCH_ID>0,

       

       

      SOURCE_TABLE || '~' || DISTRIBUTION_ID,

       

       

      SOURCE_TABLE || '~RECEIPTREVERSE~' || DISTRIBUTION_ID),

       

       

      SOURCE_TABLE || '~' || >>>>DISTRIBUTION_ID<<<<)

       

       

      ]; transformation continues...

      MAPPING> TE_7004 Transformation Parse Warning [JE_HEADER_ID || '~' || JE_LINE_NUM]; transformation continues...

      MAPPING> TE_7004 Transformation Parse Warning [<<PM Parse Warning>> [||]: operand converted to a string

      ... >>>>JE_HEADER_ID<<<< || '~' || JE_LINE_NUM<<PM Parse Warning>> [JE_LINE_NUM]: operand converted to a string

      ... JE_HEADER_ID || '~' || >>>>JE_LINE_NUM<<<<]; transformation continues...

      MAPPING> TE_7004 Transformation Parse Warning [AE_HEADER_ID || '~' || AE_LINE_NUM]; transformation continues...

      MAPPING> TE_7004 Transformation Parse Warning [<<PM Parse Warning>> [||]: operand converted to a string

      ... >>>>AE_HEADER_ID<<<< || '~' || AE_LINE_NUM<<PM Parse Warning>> [AE_LINE_NUM]: operand converted to a string

      ... AE_HEADER_ID || '~' || >>>>AE_LINE_NUM<<<<]; transformation continues...

      MAPPING> TM_6007 DTM initialized successfully for session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

      DIRECTOR> PETL_24033 All DTM Connection Info: [<NONE>].

      MANAGER> PETL_24004 Starting pre-session tasks. : (Wed Sep 18 09:46:13 2013)

      MANAGER> PETL_24027 Pre-session task completed successfully. : (Wed Sep 18 09:46:13 2013)

      DIRECTOR> PETL_24006 Starting data movement.

      MAPPING> TM_6660 Total Buffer Pool size is 12582912 bytes and Block size is 128000 bytes.

      READER_1_1_1> DBG_21438 Reader: Source is [54.225.65.108:1521/VIS], user [APPS]

      READER_1_1_1> BLKR_16003 Initialization completed successfully.

      WRITER_1_*_1> WRT_8146 Writer: Target is database [AMAZON-9C628AAE:1521/obiaDW1], user [DWH_REP2], bulk mode [ON]

      WRITER_1_*_1> WRT_8106 Warning! Bulk Mode session - recovery is not guaranteed.

      WRITER_1_*_1> WRT_8124 Target Table W_GL_LINKAGE_INFORMATION_GS :SQL INSERT statement:

      INSERT INTO W_GL_LINKAGE_INFORMATION_GS(SOURCE_DISTRIBUTION_ID,JOURNAL_LINE_INTEGRATION_ID,LEDGER_ID,LEDGER_TYPE,DISTRIBUTION_SOURCE,JE_BATCH_NAME,JE_HEADER_NAME,JE_LINE_NUM,POSTED_ON_DT,GL_ACCOUNT_ID,SLA_TRX_INTEGRATION_ID,DATASOURCE_NUM_ID)  VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

      WRITER_1_*_1> WRT_8270 Target connection group #1 consists of target(s) [W_GL_LINKAGE_INFORMATION_GS]

      WRITER_1_*_1> WRT_8003 Writer initialization complete.

      READER_1_1_1> BLKR_16007 Reader run started.

      READER_1_1_1> RR_4029 SQ Instance [SQ_XLA_AE_LINES] User specified SQL Query [SELECT DISTINCT

       

       

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

       

       

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

       

       

      AELINE.ACCOUNTING_CLASS_CODE,

       

       

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

       

       

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

       

       

      AELINE.AE_HEADER_ID AE_HEADER_ID,

       

       

      AELINE.AE_LINE_NUM AE_LINE_NUM,

       

       

      T.LEDGER_ID LEDGER_ID,

       

       

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

       

       

          JBATCH.NAME BATCH_NAME,

       

       

         JHEADER.NAME HEADER_NAME,

       

       

            PER.END_DATE,

       

       

      AELINE.CODE_COMBINATION_ID,

       

       

      AEHEADER.EVENT_TYPE_CODE,

       

       

      NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

       

       

      FROM XLA_DISTRIBUTION_LINKS DLINK

       

       

         , GL_IMPORT_REFERENCES        GLIMPREF

       

       

         , XLA_AE_LINES                              AELINE

       

       

         , GL_JE_HEADERS                         JHEADER

       

       

         , GL_JE_BATCHES                         JBATCH

       

       

         , GL_LEDGERS                                 T

       

       

         , GL_PERIODS   PER

       

       

      WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

       

       

               (  'AR_DISTRIBUTIONS_ALL'

       

       

                , 'RA_CUST_TRX_LINE_GL_DIST_ALL')

       

       

      AND DLINK.APPLICATION_ID = 222

       

       

      AND AELINE.APPLICATION_ID = 222

       

       

      AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

       

       

      AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

       

       

      AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

       

       

      AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

       

       

      AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

       

       

      AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

       

       

      AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

       

       

      AND JHEADER.STATUS                         = 'P'

       

       

      AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

       

       

      AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

       

       

      AND JHEADER.CREATION_DATE >=

       

       

                TO_DATE('01/01/1970 00:00:00'

       

       

                      , 'MM/DD/YYYY HH24:MI:SS' )

       

       

      AND DECODE('N', 'Y', T.LEDGER_ID, 1) IN (1)

       

       

      AND DECODE('N', 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ('NONE')]

      READER_1_1_1> RR_4049 SQL Query issued to database : (Wed Sep 18 09:46:13 2013)

      WRITER_1_*_1> WRT_8005 Writer run started.

      WRITER_1_*_1> WRT_8158

       

       

      *****START LOAD SESSION*****

       

       

      Load Start Time: Wed Sep 18 09:46:13 2013

       

       

      Target tables:

       

       

           W_GL_LINKAGE_INFORMATION_GS

       

       

       

       

      READER_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 18 09:46:13 2013]

      READER_1_1_1> RR_4035 SQL Error [

      ORA-00904: "XLA_EVENTS"."UPG_BATCH_ID": invalid identifier

       

       

      Database driver error...

      Function Name : Execute

      SQL Stmt : SELECT DISTINCT

       

       

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

       

       

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

       

       

      AELINE.ACCOUNTING_CLASS_CODE,

       

       

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

       

       

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

       

       

      AELINE.AE_HEADER_ID AE_HEADER_ID,

       

       

      AELINE.AE_LINE_NUM AE_LINE_NUM,

       

       

      T.LEDGER_ID LEDGER_ID,

       

       

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

       

       

          JBATCH.NAME BATCH_NAME,

       

       

         JHEADER.NAME HEADER_NAME,

       

       

            PER.END_DATE,

       

       

      AELINE.CODE_COMBINATION_ID,

       

       

      AEHEADER.EVENT_TYPE_CODE,

       

       

      NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

       

       

      FROM XLA_DISTRIBUTION_LINKS DLINK

       

       

         , GL_IMPORT_REFERENCES        GLIMPREF

       

       

         , XLA_AE_LINES                              AELINE

       

       

         , GL_JE_HEADERS                         JHEADER

       

       

         , GL_JE_BATCHES                         JBATCH

       

       

         , GL_LEDGERS                                 T

       

       

         , GL_PERIODS   PER

       

       

      WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

       

       

               (  'AR_DISTRIBUTIONS_ALL'

       

       

                , 'RA_CUST_TRX_LINE_GL_DIST_ALL')

       

       

      AND DLINK.APPLICATION_ID = 222

       

       

      AND AELINE.APPLICATION_ID = 222

       

       

      AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

       

       

      AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

       

       

      AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

       

       

      AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

       

       

      AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

       

       

      AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

       

       

      AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

       

       

      AND JHEADER.STATUS                         = 'P'

       

       

      AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

       

       

      AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

       

       

      AND JHEADER.CREATION_DATE >=

       

       

                TO_DATE('01/01/1970 00:00:00'

       

       

                      , 'MM/DD/YYYY HH24:MI:SS' )

       

       

      AND DECODE('N', 'Y', T.LEDGER_ID, 1) IN (1)

       

       

      AND DECODE('N', 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ('NONE')

      Oracle Fatal Error

      Database driver error...

      Function Name : Execute

      SQL Stmt : SELECT DISTINCT

       

       

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

       

       

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

       

       

      AELINE.ACCOUNTING_CLASS_CODE,

       

       

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

       

       

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

       

       

      AELINE.AE_HEADER_ID AE_HEADER_ID,

       

       

      AELINE.AE_LINE_NUM AE_LINE_NUM,

       

       

      T.LEDGER_ID LEDGER_ID,

       

       

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

       

       

          JBATCH.NAME BATCH_NAME,

       

       

         JHEADER.NAME HEADER_NAME,

       

       

            PER.END_DATE,

       

       

      AELINE.CODE_COMBINATION_ID,

       

       

      AEHEADER.EVENT_TYPE_CODE,

       

       

      NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

       

       

      FROM XLA_DISTRIBUTION_LINKS DLINK

       

       

         , GL_IMPORT_REFERENCES        GLIMPREF

       

       

         , XLA_AE_LINES                              AELINE

       

       

         , GL_JE_HEADERS                         JHEADER

       

       

         , GL_JE_BATCHES                         JBATCH

       

       

         , GL_LEDGERS                                 T

       

       

         , GL_PERIODS   PER

       

       

      WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

       

       

               (  'AR_DISTRIBUTIONS_ALL'

       

       

                , 'RA_CUST_TRX_LINE_GL_DIST_ALL')

       

       

      AND DLINK.APPLICATION_ID = 222

       

       

      AND AELINE.APPLICATION_ID = 222

       

       

      AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

       

       

      AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

       

       

      AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

       

       

      AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

       

       

      AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

       

       

      AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

       

       

      AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

       

       

      AND JHEADER.STATUS                         = 'P'

       

       

      AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

       

       

      AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

       

       

      AND JHEADER.CREATION_DATE >=

       

       

                TO_DATE('01/01/1970 00:00:00'

       

       

                      , 'MM/DD/YYYY HH24:MI:SS' )

       

       

      AND DECODE('N', 'Y', T.LEDGER_ID, 1) IN (1)

       

       

      AND DECODE('N', 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ('NONE')

      Oracle Fatal Error].

      READER_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 18 09:46:13 2013]

      READER_1_1_1> BLKR_16004 ERROR: Prepare failed.

      WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.

      WRITER_1_*_1> WRT_8325 Final rollback executed for the target [W_GL_LINKAGE_INFORMATION_GS] at end of load

      WRITER_1_*_1> WRT_8035 Load complete time: Wed Sep 18 09:46:13 2013

       

       

      LOAD SUMMARY

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

       

       

      WRT_8036 Target: W_GL_LINKAGE_INFORMATION_GS (Instance Name: [W_GL_LINKAGE_INFORMATION_GS])

      WRT_8044 No data loaded for this target

       

       

       

       

       

       

      WRITER_1_*_1> WRT_8043 *****END LOAD SESSION*****

      MANAGER> PETL_24031

      ***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****

      Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_XLA_AE_LINES] has completed. The total run time was insufficient for any meaningful statistics.

      Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_XLA_AE_LINES] has completed. The total run time was insufficient for any meaningful statistics.

      Thread [WRITER_1_*_1] created for [the write stage] of partition point [W_GL_LINKAGE_INFORMATION_GS] has completed. The total run time was insufficient for any meaningful statistics.

       

       

      MANAGER> PETL_24005 Starting post-session tasks. : (Wed Sep 18 09:46:14 2013)

      MANAGER> PETL_24029 Post-session task completed successfully. : (Wed Sep 18 09:46:14 2013)

      MAPPING> TM_6018 The session completed with [0] row transformation errors.

      MANAGER> PETL_24002 Parallel Pipeline Engine finished.

      DIRECTOR> PETL_24013 Session run completed with failure.

      DIRECTOR> TM_6022

       

       

      SESSION LOAD SUMMARY

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

       

       

      DIRECTOR> TM_6252 Source Load Summary.

      DIRECTOR> CMN_1740 Table: [SQ_XLA_AE_LINES] (Instance Name: [SQ_XLA_AE_LINES])

        Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]

      DIRECTOR> TM_6253 Target Load Summary.

      DIRECTOR> CMN_1740 Table: [W_GL_LINKAGE_INFORMATION_GS] (Instance Name: [W_GL_LINKAGE_INFORMATION_GS])

        Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]

      DIRECTOR> TM_6023

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

       

       

      DIRECTOR> TM_6020 Session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] completed at [Wed Sep 18 09:46:14 2013].

       

       

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

      *I did some queries in my source database (Vision) , table "XLA_EVENTS" exists , column "UPG_BATCH_ID" also exists

       

      *I added "XLA_EVENTS" to the FROM clause and ran it in SQL Developer


      *in the SELECT clause ,i see a column named "AEHEADER.EVENT_TYPE_CODE"

      but there is no table named "AEHEADER" in the FROM clause

      so i added it manually , it's probably refers to "XLA_AE_HEADERS"

       

      Final query looks like this:

      SELECT DISTINCT

      DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

      DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

      AELINE.ACCOUNTING_CLASS_CODE,

      GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

      GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

      AELINE.AE_HEADER_ID AE_HEADER_ID,

      AELINE.AE_LINE_NUM AE_LINE_NUM,

      T.LEDGER_ID LEDGER_ID,

      T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

          JBATCH.NAME BATCH_NAME,

         JHEADER.NAME HEADER_NAME,

            PER.END_DATE,

      AELINE.CODE_COMBINATION_ID,

      AEHEADER.EVENT_TYPE_CODE,

      NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

      FROM XLA_DISTRIBUTION_LINKS DLINK

         , GL_IMPORT_REFERENCES        GLIMPREF

         , XLA_AE_LINES                              AELINE

         , GL_JE_HEADERS                         JHEADER

         , GL_JE_BATCHES                         JBATCH

         , GL_LEDGERS                                 T

         , GL_PERIODS   PER

         , XLA_AE_HEADERS AEHEADER

         , XLA_EVENTS

      WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

               (  'AR_DISTRIBUTIONS_ALL'

                , 'RA_CUST_TRX_LINE_GL_DIST_ALL')

      AND DLINK.APPLICATION_ID = 222

      AND AELINE.APPLICATION_ID = 222

      AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

      AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

      AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

      AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

      AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

      AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

      AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

      AND JHEADER.STATUS                         = 'P'

      AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

      AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

      AND JHEADER.CREATION_DATE >=

                TO_DATE('01/01/1970 00:00:00'

                      , 'MM/DD/YYYY HH24:MI:SS' )

      AND DECODE('N', 'Y', T.LEDGER_ID, 1) IN (1)

      AND DECODE('N', 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ('NONE')

       

       

      *when i run that query,it takes a lot of time executing without returning any results (last time it took 4 hours before i cancel it)

       

       

      my questions are:
      -what's wrong with that query?

       

      -how can i change the query in the workflow?

       

      could anyone please help?

        • 1. Re: DAC: failed task during ETL for financial apps
          1007772

          can anybody help with this?
          at least : i need to know how to edit the sql query of the informatica workflow

           

          thanks,

          • 2. Re: DAC: failed task during ETL for financial apps
            SunilSharma

            Folder: [SDE_ORAR1211_Adaptor]

            Workflow: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

            Instance: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

            Mapping: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract]

             

            thats the folder,workflow and session and mapping info. Go to the workflow monitor and open the corresponding folder,workflow and session and look for source table SQ_XLA_AE_LINES from here you can edit the sql query or else login to mapping desinger and open corresponding mapping and src qualifier to edit the query.

            • 3. Re: DAC: failed task during ETL for financial apps
              1007772

              thank you very much
              i found SQ_XLA_AE_LINES and checked its SQL query,it's a very healthy query


              SELECT  DISTINCT

              DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

              DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

                    AELINE.ACCOUNTING_CLASS_CODE,

              GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

              GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

              AELINE.AE_HEADER_ID AE_HEADER_ID,

              AELINE.AE_LINE_NUM AE_LINE_NUM,

              T.LEDGER_ID LEDGER_ID,

              T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

                  JBATCH.NAME BATCH_NAME,

                 JHEADER.NAME HEADER_NAME,

                    PER.END_DATE,

              AELINE.CODE_COMBINATION_ID,

              AEHEADER.EVENT_TYPE_CODE,

              NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

              FROM XLA_DISTRIBUTION_LINKS DLINK

                 , GL_IMPORT_REFERENCES        GLIMPREF

                 , XLA_AE_LINES                              AELINE

                 , XLA_AE_HEADERS AEHEADER

                 , GL_JE_HEADERS                         JHEADER

                 , GL_JE_BATCHES                         JBATCH

                 , GL_LEDGERS                                 T

                 , GL_PERIODS   PER

                 , XLA_EVENTS

              WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

                       (  'AR_DISTRIBUTIONS_ALL'

                        , 'RA_CUST_TRX_LINE_GL_DIST_ALL')

              AND DLINK.APPLICATION_ID = 222

              AND AELINE.APPLICATION_ID = 222

              AND AEHEADER.APPLICATION_ID = 222

              AND XLA_EVENTS.APPLICATION_ID=222

              AND AEHEADER.AE_HEADER_ID = AELINE.AE_HEADER_ID

              AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

              AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

              AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

              AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

              AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

              AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

              AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

              AND JHEADER.STATUS                         = 'P'

              AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

              AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

              AND AEHEADER.EVENT_ID=XLA_EVENTS.EVENT_ID

              AND JHEADER.LAST_UPDATE_DATE >=

                        TO_DATE('$$LAST_EXTRACT_DATE'

                              , 'MM/DD/YYYY HH24:MI:SS' )

              AND DECODE($$FILTER_BY_LEDGER_ID, 'Y', T.LEDGER_ID, 1) IN ($$LEDGER_ID_LIST)

              AND DECODE($$FILTER_BY_LEDGER_TYPE, 'Y', T.LEDGER_CATEGORY_CODE, 'NONE') IN ($$LEDGER_TYPE_LIST)

               


              i compared this query with the query that appears in the Error messages,they are different (the Error message is stated in the first post)
              the query that appears in the Error messages misses a couple of lines,specifically in the "FROM" clause and the "WHERE" clause

              what might cause that issue?

              • 4. Re: DAC: failed task during ETL for financial apps
                1007772


                when i changed the Query in the SQ_XLA_AE_LINES ,i restarted the ETL to complete the failed tasks ,but it run the same old Query that i deleted,and so gives the same old error !

                 

                do i need to re-build the ETL and start it from the beginning?

                • 5. Re: DAC: failed task during ETL for financial apps
                  SunilSharma

                  yes you need to rebuild the EP in DAC whenever you make any changes. So that it can fetch the latest info

                  • 6. Re: DAC: failed task during ETL for financial apps
                    1007772

                    thanks,
                    when i re-built the EP,it say "ETL didn't change"

                     

                    i'm sure i edited the mapping in Designer,it's validated & saved & checked in into repository
                    did i miss something?

                    • 7. Re: DAC: failed task during ETL for financial apps
                      1007772

                      can anyone help please?

                       

                      my aim is to edit the sql script that causes the task to fail

                       

                      what i did exactly is:
                      -opened informatica Designer

                      -logged in to my informatica repository service

                      -opened folder SDE_ORAR1211_Adaptor

                      -opened SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full

                      -used "mapping designer" to edit the SQL query

                      -saved the mapping

                      -validated the mapping & then "checked in" changes

                      -re-selected the "subjects areas" in DAC ,and then "Built" the ETL

                      -started the ETL

                       

                      after all of that,it still runs the "old SQL script",so it gives me the same old error !
                      did i miss something?

                      • 8. Re: DAC: failed task during ETL for financial apps
                        SunilSharma

                        check if the session is re-usable or non-reusable. If its re-usable then you might need to edit the sql query in task window

                        • 9. Re: DAC: failed task during ETL for financial apps
                          1007772

                          thanks a lot
                          i opened the task using Informatica workflow manager,and edited the session SQL query there,it worked perfectly
                          best regards,