MSRFWOR ORA-01403: no data found

Refresh Snapshot failed in Source instance with ORA-01403 No Data found error. Here's the request log:

+---------------------------------------------------------------------------+
Master Scheduling/MRP: Version : 11.5.0

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

MSRFWOR module: Refresh Snapshots
+---------------------------------------------------------------------------+

Current system time is 16-APR-2009 09:39:30

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

**Starts**16-APR-2009 09:39:30
**Ends**16-APR-2009 09:39:31
ORA-01403: no data found
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
The degree of parallelism for Refreshing snapshots is set to: 0
ORA-01403: no data found
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


+---------------------------------------------------------------------------+
Executing request completion options...


Output is not being printed because:
The print option has been disabled for this report.


Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 16-APR-2009 09:39:32

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

I checked Metalink Note 727547.1 which advises checking for a blank msc_inst_partitions on APS instance

SELECT * FROM msc_inst_partitions;

However partitions were present.

This note pointed to another note 353674.1 which asks to check for a blank mrp_ap_apps_instances on source. This table had a single row.

Here's the solution they provide:

1. Using Order Management Responsibility - Scheduling/ATP and Scheduling Setup/Instance - Change
the GMT Difference field to some other value - Save - Change the GMT Difference Field back to its
original value - Save.

This can also be accessed by Advanced Planning Administrator Responsibility - Admin/Instances and
follow the balance of step 1.

2. Verify there is a record in table mrp_ap_apps_instances (there should also be a corresponding record in msc_apps_instances):

select count(*)
from mrp_ap_apps_instances;

There should be at least one row here.

3. Rerun a complete refresh of data collections.

Out of the above:

2. Verify there is a record in table mrp_ap_apps_instances (there should also be a corresponding record in msc_apps_instances):

Sounded most promising. And when we checked on source and APS for instance_id:

On Source:

SQL> select instance_id from apps.mrp_ap_apps_instances;

INSTANCE_ID
-----------
1

On APS:

SQL> select instance_id from apps.msc_apps_instances
2 /

INSTANCE_ID
-----------
22

That's why it was giving ORA-1401 No Data found.

We first updated APS:

update apps.msc_apps_instances
set instance_id=1;

commit;

However we got error:

Invalid instance id (22), plan data pull errored.

So we updated the instance_id back to 22 in APS and also updated the source to 22:

APS:

update apps.msc_apps_instances
set instance_id=22;

Source:

update apps.mrp_ap_apps_instances
set instance_id=22;

Refresh snapshot completed normal on source.

However, the plan failed on memory based snapshot:

Memory based snapshot 64 bit sun failed
message:
calculate plan buckets 21
APP-FND-01564: ORACLE error 1405 in main

Cause: main failed due to Error while trying to retrieve text for error ORA-01405
.

The SQL statement being executed at the time of the error was: begin msc_snapshot_pk . calculate_plan_buckets ( :plan_id , :err_mesg: and was executed from the file /apps/11510/aps64_src/msc/115/src/mscnsp.pc.
APP-MRP-22075: An internal error has occurred (main, 1, , )
@ERRORTEXT Cause: The current routine encountered the specified@ERRORTEXT x Action: Contact your customer supportrepresentative.
Oracle Home Set to ORACLE_HOME=$MSO_TOP/bin/nls8174
return value 0
Oracle NLS33 Set to ORA_NLS33=$MSO_TOP/bin/nls8174/ocommon/nls/admin/data
return value 0

Still an open issue.