11 Replies Latest reply: Jan 9, 2013 8:40 AM by 983813 RSS

    SDE_ORA_CodeDimension_Flex_JobFamily

    660060
      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
          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
            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
              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
                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
                  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
                    this is a release 12 EBS with HR
                    • 7. Re: SDE_ORA_CodeDimension_Flex_JobFamily
                      66787
                      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
                        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
                          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
                            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
                              Hi, did you solve this? I have a similar issue