Analytics Software

Sandra,

I dont see that table in that mapping. For more info look at Lineage doc, link you may find in http://www.cool-bi.com/Ref/Ref.php

SELECT DISTINCT $$Hint1
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
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('$$INITIAL_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)

Edited by: Srini VEERAVALLI on Jun 4, 2013 7:53 AM

Thanks Srini for your reply.
You don't see the table in that mapping, hm...
Ok, I will refer to the link you gave me, If I compare the query you gave and query from the workflow task, the last 2 field in my workflow task doesn't exists in your query and those are : AEHEADER.EVENT_TYPE_CODE,
NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

Thanks again Srini for your help.

Regards,

Sandra

Given query is from Mapping.

If helps mark :)

Dear Srini,

I open the link and download Excel Oracle Business Intelligence Applications ETL Data Lineage Guide Release 7.9.6.4, and then open the sheet SQL Overrides.
For SQL_Override_ID : SDE_ORAR1211_Adaptor.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full, the SQL Override is like this :

SELECT DISTINCT $$Hint1 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('$$INITIAL_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)

You can see the last two fields in select clause, that's gonna be my problem. So what I am trying to do is remove these 2 fields.
But it didn't solve, but return another error : SQL Error [ORA-01007: variable not in select list]
So I am guess I just have to stay with the SQL from SQ Mapping, make sure it same with Source Qualifier in the mapping.
But If I did that, this Workflow Full Version will be the same just like the original. Actually I don't understand the reason for this Full Version. Can you give me some highlight?
Thanks

Regards,

Sandra

Can you double check the workflow name and folder, I didnt get the given code mapping/workflow from both folder SDE_ORAR1211_Adaptor and SDE_ORAR1213_Adaptor
can you send me the session log to my email?

Dear Srini,

I have changed the query in workflow task, and now it runs successfull, but I don't have any idea if the query is just like it supposed to be :D
I send you an email, please check. Thank you.

Regards,

Sandra