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?