Forum Stats

  • 3,827,873 Users
  • 2,260,836 Discussions
  • 7,897,401 Comments

Discussions

SDE_ORA_EmployeeExpenseFact SQL error - Report_line_id

687539
687539 Member Posts: 23
Hi,

We are on Oracle EBS Version 11.5.10.2 Family pack/Patch set level 11i.FIN_PF.F/11i.OIE.I.
OBI Apps - 7.9.6

When executing Employee Expense Subject area 'SDE_ORA_EmployeeExpenseFact' fails, due to SQL Error.
As per OBI Apps config document, Employee Expense is supported on 11.5.10.2 (Family Pack M) and Family pack/Patch set level 11i.FIN_PF.F/11i.OIE.I.
AP_EXPENSE_REPORT_LINES_ALL table in this instance does not have Report_line_id.
According to documentation, ORacle R12 AP_EXPENSE_REPORT_LINES_ALL table has Report_Line_Id and not 11.5.10.
Is this a bug in OBI Apps 7.9.6?

Thanks,
Kai

Answers

  • 66787
    66787 Member Posts: 484
    What analytic apps are you using HR or Finance?
  • 687539
    687539 Member Posts: 23
    Finance - Procurement and Spend, to be specific
  • 66787
    66787 Member Posts: 484
    edited Jul 29, 2009 4:07PM
    I have recently implemented 7.9.6 with R12 Fin, scm, p&S with no issues.
    Also fin on R11.5.10.2 no issues.

    So can you paste the relevant section of the log from the session logs of informatica for this task?
    You mentioned the SQL from the task is not finding the column in the EBS side? correct?

    Can you double check that you used correct container for ETL and used 4 as datasource number?

    Edited by: shyamvaran on Jul 29, 2009 1:07 PM
  • 66787
    66787 Member Posts: 484
    Is it possible for you to select SCM too and run a test ETL, I recall there are some dependencies, as you may have noticed that some of the apps have changed like OM is part of SCM.
    Though I fail to understand why your SQL is looking for an absent column, if you can paste your session log I can compare it with my log (for this SDE that ran successfully)
  • 687539
    687539 Member Posts: 23
    Thanks Shyam,

    Appreciate your help.

    1. Yes, SCM execution is completed before running the Procurement and Spend Execution. Didn't know of any dependency, just picked Suppy Chain.
    2. Data Source has been set to 4 for Global parameter and for Source System Parameters as well.
    3. Using SDE_ORA11510_Adaptor folder in informatica. SDE_ORA_EmployeeExpenceFact mapping and SDE_ORA_EmployeeExpenseFact_FP uses mplt_BC_ORA_EmployeeExpenseFact and I looked at the source table definition in Informatica for AP_EXPENSE_REPORT_LINES_ALL and that contains REPORT_LINE_ID, but from metalink ETR it looks like that line was added in R12. Can you please let me know if in your 11.5.10.2 instance, this column exists. I am wondering if we are missing a patch..

    Here is the error from Informatica session log for SDE_ORA_EmployeeExpenseFact.

    Severity Timestamp Node Thread Message Code Message
    ERROR 7/29/2009 10:15:22 AM node01_sc-cognos-d3 READER_1_1_1
    RR_4035 SQL Error [
    ORA-00904: "EXP_LINE"."REPORT_LINE_ID": invalid identifier

    Database driver error...

    Function Name : Execute
    Oracle Fatal Error
    Database driver error...
    Function Name : Execute
    SQL Stmt : SELECT
    EXP_HDR.EMPLOYEE_ID AS EMPLOYEE_ID,
    EXP_HDR.REPORT_HEADER_ID AS REPORT_HEADER_ID,
    ...
    ...
    EXP_LINE.REPORT_LINE_ID AS REPORT_LINE_ID,
    EXP_LINE.START_EXPENSE_DATE AS START_EXPENSE_DATE,
    EXP_LINE.LAST_UPDATE_DATE AS AUX1_CHANGED_ON_DT,
    INV.GL_DATE AS GL_DATE,
    INV.INVOICE_AMOUNT AS INVOICE_AMOUNT,
    NVL(INV.INVOICE_DATE, EXP_HDR.WEEK_END_DATE) AS INVOICE_DATE,
    NVL(INV.INVOICE_NUM,EXP_HDR.INVOICE_NUM) AS INVOICE_NUM_INV,
    FND.EMPLOYEE_ID AS ENTERED_BY,
    NOTE.ENTERED_DATE AS ENTERED_DATE,
    PARAM.PARAMETER_ID AS PARAMETER_ID,
    EXP_HDR.FLEX_CONCATENATED,
    EXP_HDR.SET_OF_BOOKS_ID,
    '0' AS X_CUSTOM
    FROM
    AP_EXPENSE_REPORT_HEADERS_ALL EXP_HDR,
    AP_EXPENSE_REPORT_LINES_ALL EXP_LINE,
    AP_NOTES NOTE,
    FND_USER FND,
    AP_EXPENSE_REPORT_PARAMS_ALL PARAM,
    AP_INVOICES_ALL INV
    WHERE
    (EXP_HDR.LAST_UPDATE_DATE > TO_DATE('12/29/2008 00:00:00','MM/DD/YYYY HH24:MI:SS')
    OR EXP_LINE.LAST_UPDATE_DATE > TO_DATE ('12/29/2008 00:00:00','MM/DD/YYYY HH24:MI:SS'))
    AND EXP_HDR.REPORT_HEADER_ID (+)= EXP_LINE.REPORT_HEADER_ID
    AND INV.INVOICE_ID (+) = EXP_HDR.VOUCHNO
    AND NOTE.SOURCE_OBJECT_ID (+) = EXP_HDR.REPORT_HEADER_ID
    AND EXP_HDR.SOURCE NOT IN ('NonValidatedWebExpense','Both Pay')
    AND Nvl(EXP_HDR.EXPENSE_STATUS_CODE, 'X') NOT IN ('SAVED', 'CANCELLED', 'WITHDRAWN', 'INPROGRESS')
    AND PARAM.PARAMETER_ID (+) = EXP_LINE.WEB_PARAMETER_ID
    AND NVL(FND.USER_ID, 0) = NVL(NOTE.ENTERED_BY, 0)
    AND Nvl(NOTE.NOTE_ID,0) = (SELECT Nvl(MAX(A.NOTE_ID),0) FROM AP_NOTES A WHERE A.SOURCE_OBJECT_ID = EXP_HDR.REPORT_HEADER_ID AND A.SOURCE_OBJECT_CODE = 'OIE_EXPENSE_REPORT' AND A.NOTES_DETAIL like 'Approver Action: Approve%' )
    Oracle Fatal Error].
  • 709086
    709086 Member Posts: 6
    edited Aug 11, 2009 4:47PM
    Hi

    Did you figure what is wrong ? i'm facing the same issue

    thx

    Edited by: user11270300 on 11-Aug-2009 1:47 PM
  • 687539
    687539 Member Posts: 23
    Hi,

    I filed an SR with ORacle Support and they said that the expected OIE patch level is J, though the documentation says OIE.I is supported. From OIE.J, this new column is added and Oracle R12 has this column as well.

    As per Oracle Support, the suggested solution is to upgrade to OIE.J or R12. :) Or suggested workaround is to customize the mapping in informatica and use "NULL AS REPORT_LINE_ID".

    Upgrade is not an option right away for us. So, I customized the mapping and changed the task in DAC to use the custom mapping and it worked fine.

    Hope it helps.

    Thanks,
    Kai
This discussion has been closed.