This discussion is archived
11 Replies Latest reply: Jan 9, 2013 6:40 AM by 983813 RSS

SDE_ORA_CodeDimension_Flex_JobFamily

660060 Newbie
Currently Being Moderated
Hi,

Production DB's ebs is 10.2.0.3.0
obiee datawarehouse DB is 11.1.0.7.0
DAC 7.9.6
Informatica 8.6
We are using EBS R12.

We have a failed TASK on SDE_ORA_CodeDimension_Flex_Job Family. DAC is not generating correct sql statement, Key word "Where" is missing. Any ideas ..?

DAC Parameter file looks like this :

[SDE_ORAR12_Adaptor.SDE_ORA_CodeDimension_Flex_JobFamily]
$$ANALYSIS_END=01/01/2011 12:59:00
$$ANALYSIS_END_WID=20110101
$$ANALYSIS_START=12/31/1979 01:00:00
$$ANALYSIS_START_WID=19791231
$$COST_TIME_GRAIN=QUARTER
$$CURRENT_DATE=07/14/2009
$$CURRENT_DATE_IN_SQL_FORMAT=TO_DATE('2009-07-14', 'YYYY-MM-DD')
$$CURRENT_DATE_WID=20090714
$$DATASOURCE_NUM_ID=9
$$DEFAULT_LOC_RATE_TYPE=Corporate
$$DFLT_LANG=US
$$ETL_PROC_WID=20793361
$$FILTER_BY_LEDGER_ID='Y'
$$FILTER_BY_LEDGER_TYPE='Y'
$$FLEX_VALUE_SET_JOB_FAMILY= UNION SELECT LOOKUP_CODE , '1008262', 'JOB_FAMILY' FROM FND_LOOKUP_VALUES lookup_type = 'ITC_HR_JOB_GROUP' AND enabled_flag = 'Y'
$$GBL_CALENDAR_ID=ITC_GLB_CAL~1
$$GBL_DATASOURCE_NUM_ID=9
$$GLOBAL1_CURR_CODE=USD
$$GLOBAL1_RATE_TYPE=Corporate
$$GLOBAL2_CURR_CODE=ARS
$$GLOBAL2_RATE_TYPE=Corporate
$$GLOBAL3_CURR_CODE=USD
$$GLOBAL3_RATE_TYPE=Corporate
$$HI_DATE=TO_DATE('3714-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
$$HI_DT=01/01/3714 12:00:00
$$HR_ABSNC_EXTRACT_DATE=TO_DATE('1980-01-01 08:19:00', 'YYYY-MM-DD HH24:MI:SS')
$$HR_WRKFC_ADJ_SERVICE_DATE='N'
$$HR_WRKFC_EXTRACT_DATE=01/01/1970
$$HR_WRKFC_SNAPSHOT_DT=TO_DATE('2004-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
$$HR_WRKFC_SNAPSHOT_TO_WID=20090714
$$Hint_Tera_Post_Cast=
$$Hint_Tera_Pre_Cast=
$$INITIAL_EXTRACT_DATE=01/01/2000
$$INVPROD_CAT_SET_ID=27
$$INV_PROD_CAT_SET_ID10=
$$INV_PROD_CAT_SET_ID1=1100000001
$$INV_PROD_CAT_SET_ID2=
$$INV_PROD_CAT_SET_ID3=
$$INV_PROD_CAT_SET_ID4=
$$INV_PROD_CAT_SET_ID5=
$$INV_PROD_CAT_SET_ID6=
$$INV_PROD_CAT_SET_ID7=
$$INV_PROD_CAT_SET_ID8=
$$INV_PROD_CAT_SET_ID9=
$$LANGUAGE_CODE=E
$$LAST_EXTRACT_DATE=
$$LAST_EXTRACT_DATE_IN_SQL_FORMAT=
$$LAST_EXTRACT_DATE_WID=
$$LAST_TARGET_EXTRACT_DATE_IN_SQL_FORMAT=
$$LEDGER_ID_LIST=3,1,6,68,28
$$LEDGER_TYPE_LIST='PRIMARY'
$$LOAD_DT=TO_DATE('2009-07-14 12:57:04', 'YYYY-MM-DD HH24:MI:SS')
$$LOW_DATE=TO_DATE('1899-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
$$LOW_DT=01/01/1899 00:00:00
$$MASTER_CODE_NOT_FOUND=
$$ORA_HI_DATE=TO_DATE('4712-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
$$PROD_CAT_SET_ID10=
$$PROD_CAT_SET_ID1=1100000002
$$PROD_CAT_SET_ID2=
$$PROD_CAT_SET_ID3=
$$PROD_CAT_SET_ID4=
$$PROD_CAT_SET_ID5=
$$PROD_CAT_SET_ID6=
$$PROD_CAT_SET_ID7=
$$PROD_CAT_SET_ID8=
$$PROD_CAT_SET_ID9=
$$PROD_CAT_SET_ID=2
$$SOURCE_CODE_NOT_SUPPLIED=
$$TENANT_ID=DEFAULT
$$WH_DATASOURCE_NUM_ID=999
$DBConnection_OLAP=DataWarehouse
$DBConnection_OLTP=ORA_R12
$PMSessionLogFile=ORA_R12.DATAWAREHOUSE.SDE_ORAR12_Adaptor.SDE_ORA_CodeDimension_Flex_JobFamily.log
  • 1. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    66787 Journeyer
    Currently Being Moderated
    you have posted paramter file, go one level up and from sessions logs provide the session log, look for any ORA_ error and paste the SQL that is erroring out

    C:\Informatica\PowerCenter8.6.0\server\infa_shared\SessLogs sample log folder
  • 2. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    660060 Newbie
    Currently Being Moderated
    Session log :
    =========

    DIRECTOR> VAR_27028 Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].
    DIRECTOR> VAR_27028 Use override value [ORA_R12] for session parameter:[$DBConnection_OLTP].
    DIRECTOR> VAR_27028 Use override value [ORA_R12.DATAWAREHOUSE.SDE_ORAR12_Adaptor.SDE_ORA_CodeDimension_Flex_JobFamily.log] for session parameter:[$PMSessionLogFile].
    DIRECTOR> VAR_27028 Use override value [9] for mapping parameter:[$$DATASOURCE_NUM_ID].
    DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[MPLT_SA_ORA_CODES.$$DATASOURCE_NUM_ID].
    DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[$$FLEX_VALUE_SET_CODE].
    DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[$$FLEX_VALUE_SET_JOB_CODE].
    DIRECTOR> VAR_27028 Use override value [UNION   SELECT LOOKUP_CODE , '1008262', 'JOB_FAMILY' FROM FND_LOOKUP_VALUES    lookup_type = 'ITC_HR_JOB_GROUP' AND enabled_flag = 'Y'] for mapping parameter:[$$FLEX_VALUE_SET_JOB_FAMILY].
    DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[$$FLEX_VALUE_SET_JOB_FUNCTION].
    DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[$$FLEX_VALUE_SET_JOB_LEVEL].
    DIRECTOR> VAR_27028 Use override value [E] for mapping parameter:[$$LANGUAGE_CODE].
    DIRECTOR> VAR_27027 Use default value [DEFAULT] for mapping parameter:[MPLT_SA_ORA_CODES.$$TENANT_ID].
    DIRECTOR> TM_6014 Initializing session [SDE_ORA_CodeDimension_Flex_JobFamily] at [Tue Jul 14 13:05:44 2009].
    DIRECTOR> TM_6683 Repository Name: [itcrepository]
    DIRECTOR> TM_6684 Server Name: [Integrations_Service]
    DIRECTOR> TM_6686 Folder: [SDE_ORAR12_Adaptor]
    DIRECTOR> TM_6685 Workflow: [SDE_ORA_CodeDimension_Flex_JobFamily] Run Instance Name: [] Run Id: [13450]
    DIRECTOR> TM_6101 Mapping name: SDE_ORA_CodeDimension_Flex_KFF_DFF_Load [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_6703 Session [SDE_ORA_CodeDimension_Flex_JobFamily] is run by 32-bit Integration Service [node01_itc-365-biapp01], version [8.6.0 HotFix4], build [1017].
    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_6307 DTM error log disabled.
    MAPPING> TE_7022 TShmWriter: Initialized
    MAPPING> DBG_21075 Connecting to database [itcpbiee], user [pbiwh]
    MAPPING> TM_6007 DTM initialized successfully for session [SDE_ORA_CodeDimension_Flex_JobFamily]
    DIRECTOR> PETL_24033 All DTM Connection Info: [<NONE>].
    MANAGER> PETL_24004 Starting pre-session tasks. : (Tue Jul 14 13:05:44 2009)
    MANAGER> PETL_24027 Pre-session task completed successfully. : (Tue Jul 14 13:05:44 2009)
    DIRECTOR> PETL_24006 Starting data movement.
    MAPPING> TM_6660 Total Buffer Pool size is 32000000 bytes and Block size is 1280000 bytes.
    READER_1_1_1> DBG_21438 Reader: Source is [PRODISET], user [apps]
    READER_1_1_1> BLKR_16003 Initialization completed successfully.
    WRITER_1_*_1> WRT_8366 Bulk mode is not applicable to delete, update, or data-driven sessions, default to normal mode.
    WRITER_1_*_1> WRT_8148 Writer: Target is database [itcpbiee], user [pbiwh], bulk mode [N/A]
    WRITER_1_*_1> WRT_8397 Appending output data to file [C:\Informatica\PowerCenter8.6.0\server\infa_shared\BadFiles\w_code_d1.bad] (initial size [1486]).
    WRITER_1_*_1> WRT_8124 Target Table W_CODE_D :SQL INSERT statement:
    INSERT INTO W_CODE_D(DATASOURCE_NUM_ID,SOURCE_CODE,SOURCE_CODE_1,SOURCE_CODE_2,SOURCE_CODE_3,SOURCE_NAME_1,SOURCE_NAME_2,CATEGORY,LANGUAGE_CODE,MASTER_DATASOURCE_NUM_ID,MASTER_CODE,MASTER_VALUE,W_INSERT_DT,W_UPDATE_DT,TENANT_ID) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    WRITER_1_*_1> WRT_8124 Target Table W_CODE_D :SQL UPDATE statement:
    UPDATE W_CODE_D SET SOURCE_CODE_1 = ?, SOURCE_CODE_2 = ?, SOURCE_CODE_3 = ?, SOURCE_NAME_1 = ?, SOURCE_NAME_2 = ?, MASTER_DATASOURCE_NUM_ID = ?, MASTER_CODE = ?, MASTER_VALUE = ?, W_INSERT_DT = ?, W_UPDATE_DT = ?, TENANT_ID = ? WHERE DATASOURCE_NUM_ID = ? AND SOURCE_CODE = ? AND CATEGORY = ? AND LANGUAGE_CODE = ?
    WRITER_1_*_1> WRT_8124 Target Table W_CODE_D :SQL DELETE statement:
    DELETE FROM W_CODE_D WHERE DATASOURCE_NUM_ID = ? AND SOURCE_CODE = ? AND CATEGORY = ? AND LANGUAGE_CODE = ?
    WRITER_1_*_1> WRT_8270 Target connection group #1 consists of target(s) [W_CODE_D]
    WRITER_1_*_1> WRT_8003 Writer initialization complete.
    READER_1_1_1> BLKR_16007 Reader run started.
    WRITER_1_*_1> WRT_8005 Writer run started.
    WRITER_1_*_1> WRT_8158

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

    Load Start Time: Tue Jul 14 13:05:44 2009

    Target tables:

    W_CODE_D


    READER_1_1_1> RR_4029 SQ Instance [SQ_FND_LOOKUP_VALUES] User specified SQL Query [SELECT 'CODE', 'DESCRIPTION', 'CODE_ID', 'CATEGORY' FROM DUAL UNION   SELECT LOOKUP_CODE , '1008262', 'JOB_FAMILY' FROM FND_LOOKUP_VALUES    lookup_type = 'ITC_HR_JOB_GROUP' AND enabled_flag = 'Y']
    READER_1_1_1> RR_4049 SQL Query issued to database : (Tue Jul 14 13:05:44 2009)
    READER_1_1_1> CMN_1761 Timestamp Event: [Tue Jul 14 13:05:44 2009]
    READER_1_1_1> RR_4035 SQL Error [
    ORA-00933: SQL command not properly ended

    Database driver error...
    Function Name : Execute
    SQL Stmt : SELECT 'CODE', 'DESCRIPTION', 'CODE_ID', 'CATEGORY' FROM DUAL UNION SELECT LOOKUP_CODE , '1008262', 'JOB_FAMILY' FROM FND_LOOKUP_VALUES lookup_type = 'ITC_HR_JOB_GROUP' AND enabled_flag = 'Y'
    Oracle Fatal Error
    Database driver error...
    Function Name : Execute
    SQL Stmt : SELECT 'CODE', 'DESCRIPTION', 'CODE_ID', 'CATEGORY' FROM DUAL UNION SELECT LOOKUP_CODE , '1008262', 'JOB_FAMILY' FROM FND_LOOKUP_VALUES lookup_type = 'ITC_HR_JOB_GROUP' AND enabled_flag = 'Y'
    Oracle Fatal Error].
    READER_1_1_1> CMN_1761 Timestamp Event: [Tue Jul 14 13:05:44 2009]
    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_CODE_D] at end of load
    WRITER_1_*_1> WRT_8035 Load complete time: Tue Jul 14 13:05:44 2009

    LOAD SUMMARY
    ============

    WRT_8036 Target: W_CODE_D (Instance Name: [W_CODE_D])
    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_FND_LOOKUP_VALUES] 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_FND_LOOKUP_VALUES] 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_CODE_D] has completed. The total run time was insufficient for any meaningful statistics.

    MANAGER> PETL_24005 Starting post-session tasks. : (Tue Jul 14 13:05:44 2009)
    MANAGER> PETL_24029 Post-session task completed successfully. : (Tue Jul 14 13:05:44 2009)
    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_FND_LOOKUP_VALUES] (Instance Name: [SQ_FND_LOOKUP_VALUES])
         Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
    DIRECTOR> TM_6253 Target Load Summary.
    DIRECTOR> CMN_1740 Table: [W_CODE_D] (Instance Name: [W_CODE_D])
         Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
    DIRECTOR> TM_6023
    ===================================================

    DIRECTOR> TM_6020 Session [SDE_ORA_CodeDimension_Flex_JobFamily] completed at [Tue Jul 14 13:05:45 2009].
  • 3. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    66787 Journeyer
    Currently Being Moderated
    Try running this standalone on the DW to see if you get same Oracle error

    SQL Stmt : SELECT 'CODE', 'DESCRIPTION', 'CODE_ID', 'CATEGORY' FROM DUAL UNION SELECT LOOKUP_CODE , '1008262', 'JOB_FAMILY' FROM FND_LOOKUP_VALUES lookup_type = 'ITC_HR_JOB_GROUP' AND enabled_flag = 'Y';
  • 4. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    660060 Newbie
    Currently Being Moderated
    Yes, because word "where" is missing in the condition in the select statement. How it is constructing a select statement without the key word "where"

    rm
  • 5. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    66787 Journeyer
    Currently Being Moderated
    ok can you tell what analytic apps are you using is it HR?

    It could be a bug, but you can open informatica designer and look at this task under Ora R12
    SDE_ORA_CodeDimension_Flex_JobFamily
  • 6. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    660060 Newbie
    Currently Being Moderated
    this is a release 12 EBS with HR
  • 7. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    66787 Journeyer
    Currently Being Moderated
    ubfortunately I have not run HR in 796 for R12....
    let's see someone who has done it comments here

    however general the Task generates the SQL unless there is overwrite SQL that has syntax error, trying looking in informatica
  • 8. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    641632 Newbie
    Currently Being Moderated
    You find this usually if you have not provided a value to the DAC Parameter, which is being used in the SQL Qualifier.
  • 9. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    695404 Newbie
    Currently Being Moderated
    BTW, where did you find this file?

    SDE_ORAR12_Adaptor.SDE_ORA_CodeDimension_Flex_JobFamily

    Under Informatica dirs or somewhere else?

    Thanks,
  • 10. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    66787 Journeyer
    Currently Being Moderated
    look here
    C:\Informatica\PowerCenter8.6.0\server\infa_shared\SessLogs

    substitute your Informatica home...on the server
  • 11. Re: SDE_ORA_CodeDimension_Flex_JobFamily
    983813 Newbie
    Currently Being Moderated
    Hi, did you solve this? I have a similar issue

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points