SDE_ORA_EmployeeExpenseFact SQL error - Report_line_id

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].

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