Troubleshooting

This chapter covers the following topics:

Overview

For a list of data collection errors, organized by error message, please review My Support Note 1227255.1 -- "Troubleshooting Errors with ATP/Planning Data Collections".

First Steps

You may encounter a failure for many reasons:

The first step for troubleshooting is to review the following:

Basic Performance Tuning for MSC Partitions

Diagnosing Data Collection Problems

To check on specific requests that are running and show the SQL that is running on the RDBMS, see My Oracle Support Note #186472.1 -- "Diagnostic Scripts: 11i - Hanging SQL - Find the Statement Causing Process to Hang" . Note:

To see details on all the requests in a set, see My Oracle Support Note #280295.1 -- "REQUESTS.sql Script for Parent/Child Request IDs and Trace File IDs". Enter the request id for the request that launched the set and it will provide details of all the requests. The default request for the set is 'Planning Data Collection (Request Set Planning Data Collection)'.

To set up traces for requests if you need to get details on a performance issue, see My Oracle Support Note #245974.1-- "FAQ - How to Use Debug Tools and Scripts for the VCP (aka APS) and EBS Applications", steps. #7 and #11. #11 is very useful for setting up a trace for Refresh Collection Snapshots on the EBS source when it is launched by data collections from the VCP destination. You can download guided navigation for both sections to better understand the steps involved.

Refresh Collection Snapshots or Planning Data Pull Errors

  1. For errors in Refresh Collection Snapshots or Planning Data Pull, check that all setup requests have completed successfully.

  2. Review Appendix C.3: Requests and check the Request Collection Snapshots log file to confirm that the setup requests were actually launched and that no errors occurred. There have been situations where the errors are not reported back to the main request.

  3. Check the profile MSC: Source Setup Required.

    • If it is set to 'Yes', then the setup requests did not complete successfully when Refresh Snapshots was launched.

    • If it is set to 'No', then change to 'Yes' and run Refresh Collection Snapshots as a standalone request one more time. If there were problems setting up the objects the first time, then this will likely resolve the issue.

ODS Load Errors

  1. If a timeout error is present in the ODS Loader worker log file, then run the request Planning Data Collection -- Purge Staging Tables with Validation parameter set to 'No'.

  2. Run Gather Schema Statistics on MSC Schema. In a distributed installation, run on the VCP Destination.

  3. Launch Standard Data Collections with increased timeout of 600 or higher. You may need as high as 1400 for the first run with lots of new data.

  4. Set the Planning Data Pull parameter Analyze Staging Tables to 'Yes' to help with new data loads into empty tables.

  5. After the successful completion, run Gather Schema Statistics on the MSC schema one more time.

Refresh Snapshot Process Doesn't Start Because of Concurrent Manager Unavailability

When launching Collections, the Planning Data Pull fails with the following error: "Refresh Snapshot process could not get started after waiting for X minutes because of Concurrent Manager Unavailability". This can happen in a centralized instance when dblink information has been populated in msc_apps_instances inadvertently. When dblink information is populated, the Planning Data Pull tries to launch the refresh collection snapshot using the dblink information available, but it fails to find a connected instance, and finally times out.

To resolve, run the following sql:

SELECT instance_id,instance_code, 
nvl(a2m_dblink,'Null'),nvl(m2a_dblink,'Null') 
FROM msc_apps_instances ;

If the above sql returns a value for a2mdbling and m2adblink other than the value 'Null', then the setup is wrong, and you need to do the following:

  1. Delete the records from the table MRP_AP_APPS_INSTANCES_ALL as follows:

    DELETE
FROM mrp_ap_apps_instances_all
WHERE instance_code = '&instance_code';
Commit;

  2. Go into the Advanced Supply Chain Planning Administrator responsibility.

  3. Navigate to Admin > Instances.

  4. Remove the dblink information. This will change the record in msc_apps_instances, mrp_ap_apps_instances_all and mrp_ap_apps_instance.

  5. Confirm whether the dblink information has correctly populated as Null in the msc_apps_instances using the following sql:

    SELECT instance_id,instance_code,
nvl (a2m_dblink,'Null'),nvl (m2a_dblink,'Null')
FROM msc_apps_instances;

For more information about dblinks, see Creating Links.

Data Collection Fails with Several Messages

If the Planning Data Pull concurrent program fails, review the logs produced by the planning data pull process. If the error message is among the list below, follow the correct action.

On the destination instance, using SQL*Plus, make sure that column ST_STATUS within MSC_APPS_INSTANCES equals zero for the instance being collected. At the destination instance, issue the following from within SQL*Plus:

select instance_code, st_status
from msc_apps_instances;

INS     ST_STATUS
-----   ----------------
crm                    0
vis                    3

MSC_APPS_INSTANCES contains the ST_STATUS column. This column is used by the collection process. The value of ST_STATUS is used to determine the stage of collection for the desired instance. The possible values are:

(0) Staging tables are empty for the particular instance. No instance data exists. This is the normal state of the instance after a successful data collection. This column needs to be 0 for a data collection to execute for the instance.

(1) Planning Data is being pulled from the source snapshots (complete refresh or net change) by the Planning Data Pull process. If set to 1, the following error will be in the Planning Data Pull Log file, 'Another Data Pull process is running'.

(2) Data has already been pulled from the source snapshots and is ready to be collected by the Planning ODS load process. If set to 2, the following error will be in the Planning Data Pull Log file, 'Staging tables' data is not collected'. Please submit the request Planning ODS Load.

(3) Data is being validated in the staging tables and then transformed from the staging tables and loaded to the Operational Data Store by the Planning ODS Load process. If set to 3, the following error will be in the Planning Data Pull Log file, 'Another Planning ODS Load process is running'.

(4) Data is being purged from the staging tables. If set to 4, the following error will be in the Planning Data Pull Log file, 'Staging tables' data is being purged'.

(5) There are processes running that will interfere with your request. Please investigate using the query:

select instance_code, st_status
from msc_apps_instances

If this has happened after an abnormal termination of the collection process, use the following SQL to reset and then rerun:

update msc_apps_instances set st_status=0 where instance_id=<>;
commit;

After all the collection tasks are successfully complete, the Planning Data Pull program updates ST_STATUS to 0. This indicates that the instance is ready for another collection. In the SQL example above, the crm ST_STATUS is set to 0 and is ready for another collection. The vis instance ST_STATUS is set to 3 and is currently executing the planning data pull. The crm instance is ready for collection. If the collection process is not running and the ST_STATUS column is > 0, reset the ST_STATUS columns, purge the staging tables and resubmit the collection.

Solution:

  1. Navigate to Advanced Supply Chain Planner > Other > Request > Submit A New Request (even if ASCP is not installed).

  2. Submit the concurrent request 'Purge Staging Tables' at the destination instance.

  3. Uncheck validation to ensure this process completes without a warning.

  4. Run the data collection.

    • Set Recalculate Sourcing History = No.

    • Set Recalculate NRA = No unless required.

Missing Data Collection Entities

Many times customers face issues where certain entities are not collected into the VCP destination instance. Refer to My Oracle Support Note 558477.1 -- Troubleshooting Missing Data Collection Entities for diagnostic information to track the various data collection entities from the EBS source tables to the VCP destination tables. This note contains scripts that provide information from the related EBS table, snapshot, synonym, planning view, staging table, and planning table. If data is not collected into the planning table, you can trace back through the output to see where the data went missing. For example, the problem could be that the data was in the snapshot, but not in the planning view. Investigation would then be needed to understand why the view did not pick up the data. Or maybe the issue is that the data from the EBS source table is not in the snapshot. In this case, investigation would be needed to understand why the Refresh Collection Snapshot program did not update the corresponding snapshot.

Tracing

Data collection errors should be level 4 traces with binds; perfomance issues should be level 8 traces with waits. For information about how to set up trace requests to get details on a performance issue, see My Oracle Support Note 245974.1 -- "FAQ -- How to Use Debug Tools and Scripts for the APS Suite".

Other Troubleshooting Resources

For additional assistance with troubleshooting, please see the following My Oracle Support notes:

Note Number Title
415135.1 How To Create APS Related Snapshots And mLogs
211121.1 How to Run MSRFWOR - Refresh Collections Snapshots Concurrent Request from the Application
550005.1 How to Improve the Performance of the Refresh Collection Snapshots When Running Very High Volumes
1400555.2 Information Center: Data Collections Value Chain Planning Advanced Supply Chain Planning

See also:

Data Field Notes

Calendar: Oracle recommends that you load calendars separate from all other entities and before all other entities. When you collect all other entities, set Calendar selection to No..

Currency: Currencies collected from Oracle Order Management and Oracle Purchasing are posted in functional currency even if their source is in transactional currency.

Currency Conversion Rates: Collect currency and currency conversion rates for a past, current and future period from the source in order to support displaying the cost in reporting currency. The default value is No. Currency conversion rates are collected only:

See profile options:

Discrete Jobs: A discrete job that is complete and not closed appears in the collected data with quantity zero. A discrete job that is complete and closed does not appear in the collected data. You can control this with profile option MSC: Collect Completed Jobs

Drop ship purchase orders: The collections process does not collect supplies against drop ship purchase orders because the planning engine does not plan drop ship sales orders.

End-Item Substitutes: You can see end-item substitutes in Collections Workbench as long as you have not defined a substitute set. If you have defined a substitute set, use Planner Workbench Items window to see the substitute set and end-item substitute.

Global Forecasts: You can review global forecast entries in Collections Workbench horizontal plan using rows Original and Cumulative Original.

Inactive forecasts: The collections process does not collect the demands of inactive forecasts if run in Full Refresh or Targeted Refresh collections mode

Intransit, Purchase Order, and Requisition: Oracle recommends that you always collect purchase order, requisition and intransit together. To do so, set Planning Data Pull parameter Purchase Orders/Purchase Requisitions to Yes.

Routings: The collections process does not collect routing operation resources with Schedule = No and does not display them in their routings. It also does not collect alternate resources of primary resources that are Schedule = No, even if the alternate resources themselves are Schedule = Yes.

Trading Partners: When loading trading partners, access file Parameters.txt and set Calendar_Overwrite_Flag to N.

Legacy Collections Data Notes

OWNING_PARTNER_SITE_CODE: Organization code

OWNING_TP_TYPE: Valid values are:

PLANNING_PARTNER_SITE_CODE: Organization code

PLANNING_TP_TYPE: Valid values are:

Resource Balance Flag: Indicates whether a resource is load balanced. Valid values are:

This flag is only for Oracle Process Manufacturing. Since you cannot use legacy collections with Oracle Process Manufacturing, always leave this field null.

Unit of Measure: Load all base unit of measure comversions without an item name This creates rows in MSC_UOM_CONVERSIONS with inventory_item_id = 0, for example:

For specific conversions across UOM Class or specific intra-class unit of measure conversions for some items, load them using the item name

Resetting Item Attribute Values

To reset item attribute values: