APP-FND-01630: Cannot open file $APPLTMP/OFAAA5uaODo.t for reading

Recently MSCPLD (ASCP Loader) failed with these errors:

Advanced Supply Chain Planning: Version : 11.5.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

MSCPLD module: Loader Worker With Direct Load Option
+---------------------------------------------------------------------------+

Current system time is 06-APR-2009 20:34:10

+---------------------------------------------------------------------------+


===================================================================
Debug Mode : Disabled
Output to Terminal : No
Argument Method : Database Fetch
Trace Mode : Disabled

===================================================================
Argument 1 (CTRL_FILE) = $APPLCSF/out/data01/mbpoutput/MSLD_RESERVATIONS.ctl
Argument 2 (DATA_FILE) = $APPLCSF/out/data01/mbpoutput/MSLD_RESERVATIONS.dat
Argument 3 (DISCARD_FILE) = $APPLCSF/out/data01/mbpoutput/MSLD_RESERVATIONS.dis
Argument 4 (BAD_FILE) = $APPLCSF/out/data01/mbpoutput/MSLD_RESERVATIONS.bad
Argument 5 (LOG_FILE) =
Argument 6 (NUM_OF_ERRORS) = 0

===================================================================
Creating dummy log file ...
Parent Program Name: MSCMON
This IS part of a Plan run.
usdsop: exec failed during spawnsqlldr: No such file or directory
Program exited with status 1
APP-FND-01630: Cannot open file $APPLTMP/OFAAA5uaODo.t for reading

Cause: USDINS encountered an error when attempting to open file $APPLTMP/OFAAA5uaODo.t for reading.

Action: Verify that the filename is correct and that the environment variables controlling that filename are correct.

Action: If the file is opened in read mode, check that the file exists. Check that you have privileges to read the file in the file directory. Contact your system administrator to obtain read privileges.

Action: If the file is opened in write or append mode, check that you have privileges to create and write files in the file directory. Contact your system administrator to obtain create and write privileges.

We checked permissions, utl_file_dir, profile option utl_file_log and various other settings. Everything was configured correctly.

Then we checked FND_EXECUTABLES for what is being called by MSCPLD

SQL> utables
Name Null? Type
----------------------------------------- -------- ----------------------------
APPLICATION_ID NOT NULL NUMBER(15)
EXECUTABLE_ID NOT NULL NUMBER(15)
EXECUTABLE_NAME NOT NULL VARCHAR2(30)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN NOT NULL NUMBER(15)
EXECUTION_METHOD_CODE NOT NULL VARCHAR2(1)
EXECUTION_FILE_NAME VARCHAR2(61)
SUBROUTINE_NAME VARCHAR2(30)
ICON_NAME VARCHAR2(30)
EXECUTION_FILE_PATH VARCHAR2(510)

SQL> select EXECUTABLE_NAME,EXECUTION_FILE_NAME
2 from fnd_executables
3 where EXECUTABLE_NAME='MSCPLD';

EXECUTABLE_NAME
------------------------------
EXECUTION_FILE_NAME
-------------------------------------------------------------
MSCPLD
MSCPLD.sh

When we checked the contents of MSCPLD.sh, it was having these lines:

#!/bin/ksh
#
# $Header: MSCPLD.sh 115.0 2004/02/26 21:05:53 shhan noship $
#
# Shell script to support direct path option in
# sql*loader this sets the oracle_home to server oracle home
#
# make sure following environment variables are setup correct
# and point to the server oracle home
# ORACLE_HOME
# LD_LIBRARY_PATH
# PATH

# please uncomment after setting

export ORACLE_HOME=/prod11i/oracle/10.2.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

$MSC_TOP/bin/MSCSLD $*
~

The ORACLE_HOME was pointing to Production path of 10g DB home.

After we corrected this to /dev11i/oracle/10.2.0, it worked fine.