This chapter covers the following topics:
This section provides a detailed explanation on Oracle Global Order Promising:
Troubleshooting
Debugging
SQL Queries
The following are error messages Oracle Global Order Promising users may receive, and how to troubleshoot these messages.
Note: The lookup codes are passed from the Oracle Global Order Promising API to the calling module, which displays the error messages to the users.
Error Message | Code | Message Received When | To Resolve this Message |
---|---|---|---|
Try ATP again later. | 130 | The complete / target collection or sales order collection is running. | Wait until so_tbl_status in msc_apps_instances is set to Null or 0. |
Summary Concurrent program is running. Try ATP later. | 160 | The Load Summary Based on Collected Data concurrent program is running in complete refresh mode, and ATP is offline. | Wait until the Load Summary Based on Collected Data concurrent program completes successfully. |
Invalid ATP rule or No ATP rule defined. | 57 | A valid ATP rule is not assigned to the item or the organization. | Assign a valid ATP Rule to the item or the organization level. Run collections to collect the new item definition and organization setup. |
Unable to meet the request quantity. | 52 | The infinite time fence is not defined in the ATP Rule, and Oracle Global Order Promising cannot find the entire requested quantity within the Manufacturing calendar of the organization. | Define the infinite time fence in the ATP Rule or ensure that sufficient supply is available for the item within the calendar limits. |
Unable to find a calendar date. | 47 |
|
Do one of the following:
|
No APS instance defined. Set APS instance and run collections. | 140 | The instance for Oracle Global Order Promising is not defined in the Advanced Planning and Scheduling server. | Define the instance to collect the data to the APS server. For details, see: System Setup. |
Wrong Check ATP setup for product family item and member item. | 180 | Member items for a product family item have Check ATP set to Material, if the product family item has Check ATP set to Material. However, when the requested member item has Check ATP set to None, this error occurs. | Set the Check ATP attribute to Material for the member item that failed with that product family. |
One of the group elements or mandatory components failed. | 19 | Lines in a ship or arrival set receive this error when any line in the set has an error. For example, in a three line ship set where one line has the error Plan not Found, the other two lines get this error message. | Correct the error for the line item that has an error message other than this error message. Then the entire ship or arrival set will not fail. |
ATP has detected invalid objects. Contact your system admin. | 220 | Some ATP packages are invalid. | Contact your database administrator. This error typically relate to applying the correct patch. |
ATP processing error. | 23 | - | Contact your system administrator to investigate the problem. |
INV: Capable to Promise must be same at source and destination. | 230 | The profile INV: Capable to Promise is set differently at the source than at the destination. | Set the profile INV: Capable to Promise to the same value in both the source and destination instances. |
Cannot meet request date or latest acceptable date | 53 | Scheduling fails because the demand cannot be satisfied for the latest acceptable date, but the demand can be satisfied at a later date. | Excess supply is required for this item. |
ATP not applicable. | 61 | Item is not ATP capable. | No action is needed unless the item should be ATP-capable. |
No Sources. | 80 | Sourcing is not defined for that customer. Thus, Oracle Global Order Promising cannot select the shipping organization in the ATP Assignment Set. | Globally define all organization sourcing rules and assign them to the appropriate levels for that customer in the ATP Assignment Set. Note that Bills of Distribution and local sourcing rules are not applicable. |
This item is not collected. Please run Data Collection. | 85 | An ATP-capable able item was not collected. | Run collections to collect the item. |
No Assignment Set. | 90 | Both the MRP: ATP Assignment Set at source and destination are null. | Define at least one assignment set. You must do this when you want Oracle Global Order Promising to recommend a shipping warehouse. |
Error Message | Code | Message Received When | To Resolve this Message |
---|---|---|---|
Plan not found. | 120 | For Standard Items, this error is generated only when the plan is running or has errored out. For the Multi-Level, Multi-Org ATO Model:
|
For Standard Items, the plan that contains the item is running or has errored out. Try ATP after the plan run has completed successfully For the Multi-Level, Multi-Org ATO Model:
|
Plan not found. ATP is calculated using collected data. | 150 | If standard items are not found in any ATP-capable plan, use collected data to find availability. | No action is needed. If you expect the item should have a plan, make sure that the plan is ATP-capable and has been run successfully. |
Run ATP Post Plan Processing concurrent program. | 200 | The ATP Post Plan Processing concurrent program has not been run for the ATP-capable plan. | Run the ATP Post Plan Processing concurrent program. If you are not using demand priority based Allocated ATP or Summary Based ATP, make sure the related profile is set correctly. |
Summary Concurrent program is running. Try ATP later. | 160 | The ATP Post Plan Processing concurrent program is still running. | Wait until the ATP Post Plan Processing concurrent program has finished running. |
MRP: Default Assignment Set sourcing not in sync with ATP Plan. | 240 | Only applicable for multi-level, multi-org ATO models: the sourcing defined for the model in the assignment set assigned to the profile MRP: Default Assignment Set is not the same as the sourcing defined in the assignment set used in the ATP plan. | Only applicable for multi-level, multi-org ATO models: ensure that the ATO model has the same sourcing and is the same in the two assignment sets is used in profile and plan. |
Unable to find a calendar date. | 47 | One of the following conditions occurs:
|
Do one of the following:
|
No APS instance defined. Set APS instance and run collections. | 140 | The instance for Oracle Global Order Promising has not be is not defined in the Advanced Planning and Scheduling server. | Define the instance to collect the data to the APS server. For details, see: System Setup. |
Wrong Check ATP setup for product family item and member item. | 180 | Member items for a product family item have Check ATP set to Material, if the product family item has Check ATP set to Material. However, when the requested member item has Check ATP set to None, this error occurs. | Set the Check ATP attribute to Material for the member item that failed with that product family. |
One of the group elements or mandatory components failed. | 19 | Lines in a ship or arrival set receive this error when any line in the set has an error. For example, in a three line ship set where one line has the error Plan not Found, then the other two lines get this error message. | Correct the error for the line item that has an error message other than this error message. Then the entire ship or arrival set will not fail. |
ATP has detected invalid objects. Contact your system administrator. | 220 | Some ATP packages are invalid. | Contact your database administrator. This erroris typically related to applying the correct patch. |
ATP processing error. | 23 | One of several possible errors occurred. | Contact your system administrator to investigate the problem. |
INV: Capable to Promise must be same at source and destination. | 230 | The profile INV: Capable to Promise is set differently at the source than at the destination. | Set the profile INV: Capable to Promise to the same value in both the source and destination instances. |
Unable to meet the latest acceptable date. | 53 | The scheduling fails because the demand cannot be satisfied for the latest acceptable date, but the demand can be satisfied at a later date. | Excess supply is required for this item. |
ATP not applicable. | 61 | Item is not ATP-capable. | No action is needed unless the item should be ATP-capable. |
No Sources. | 80 | Sourcing is not defined for that customer. Thus, Oracle Global Order Promising cannot select the shipping organization in the ATP Assignment Set. | Globally, define all organization sourcing rules and assign them to the appropriate levels for that customer in the ATP Assignment Set. Note that Bills of Distribution and local sourcing rules are not applicable. |
This item is not collected. Please run Data Collection. | 85 | An ATP-capable item was not notcollected. | Run collections to collect the item. |
No Assignment Set. | 90 | Both the MRP: ATP Assignment Set at source and destination are null. | Define at least one assignment set. You must do this when you want Oracle Global Order Promising to recommend a shipping warehouse. |
Options selected have exclusive sources. | 350 | Two or more options needed to build a configuration can only be sourced from different organizations. | Review option-specific sourcing rules defined for your options to ensure that the configuration can be manufactured in at least one source. |
No valid source for this configuration. | 310 | There option-specific sourcing rules do not match the sourcing rules for the model. | Review the sources rules for the model and the option specific sourcing rules to ensure that at least one model sourcing rule can build this configuration. |
Failure during ATP Match process. | 320 | A failure occurred when attempting to match your order to existing configurations. | - |
Note: For both ATP Based on Collected Data and Planning Data tables, each customer has only one table to use.
Error | When Error Occurs | To Resolve this Message |
---|---|---|
Collection Process Fails. | The collections process fails due to invalid state of msc_atp_plan_sn snapshot. | Set the following event in the init.ora file to: EVENT = 32333 trace name context forever, level 8 |
ATP performance is slow. | - | Do the following:
|
Some past due sales orders, supplies, or demands are not included in the ATP. | - | For ATP based on collected data, check the ATP Rule assigned to the item:
For example, for a 5-day Past Due Demand, ATP only considers the past due demand that falsl under the last 5 working days before the sysdate. To consider all past due demand, these fields should be left blank. For ATP based on planned data, check the ATP Rule assigned to the item:
|
Some supplies and demands are missing. | When a sales order is not present in ATP supply and demand details, and the supply is not present in ATP supply and demand details. | Check if this supply and demand is visible in the Collections Workbench:
If supply or demand records are missing, run the SQL query (for Error 11: some supplies and demands are missing) to determine if collection was successful in bringing the supply and demand from the source to the destination. |
Subscript Beyond Count. | Internal error. | Contact your system administrator or contact Oracle Support to investigate the problem. |
Delivery Lead Time Not Calculated. | For Lead Time set between the customer location and the source organization's location:
For lead time set between customer ship-to site location, customer region, or customer zone:
|
For Lead Time set between the customer location and the source organization's location:
For lead time set between customer ship-to site location, customer region, or customer zone:
|
The ATP debug file contains process information that helps facilitate ATP debugging whenever logging a tar or bug. In a Standalone Planning Server Configuration, ATP produces one ATP debug file. In an Integrated Planning Server Configuration, ATP produces one ATP debug file at the source and one ATP debug file at the destination.
Set the profile MSC: ATP Debug Mode to Debug Only at the user level of the source instance.
Run the following query to find the location where the ATP debug file is created:
select ltrim(rtrim(value))
from
(select value from v$parameter2
where name='utl_file_dir'
order by rownum desc)
where rownum <2;
Ensure that the query returned no * values at the end of utl_file_dir value. Also, ensure that this directory is writable.
Run the ATP Inquiry for the item.
When the test case is complete, pick latest session file from the location determined in the first step.
Upload the debug:.
Complete Steps 1 to 4 on both the source and destination instance.
Set profile MSC: ATP Debug Mode back to None at the appropriate site level after generating the debug file. If the profile option is not set back to None, then the ATP performance degrades.
At the instance where the query is performed, set the profile MSC: ATP Debug Mode to Debug Only at the user level.
Run the following query to find the location where the ATP debug file is created:
select ltrim(rtrim(value))
from
(select value from v$parameter2
where name='utl_file_dir'
order by rownum desc)
where rownum <2;
Ensure that the query returned no * values at the end of utl_file_dir value. Also, ensure that this directory is writable.
Run the ATP Inquiry for the item.
Pick the latest session file from the location determined in the first step, when the test case is complete.
People commonly debug the following areas to ensure that the basic setup and ATP calculations are correct.
Oracle Global Order Promising Architecture
The source view, mrp_ap_apps_instances, must contain one row. Verify that the Enable ATP checkbox is enabled for a correct instance in the instance setup UI. The instance_id column must contain the instance id assigned to this source instance on the APS instance.
The destination table, msc_apps_instances, may contain as many rows as the collected source instances.
The complete / target collections must be completed at least once before using ATP.
For ATP based on collected data to work correctly, the collections process must be completed successfully. A value of zero or null in the st_status column of the msc_apps_instance table for the source instance indicates that collections for this source have been run successfully, and ATP can be performed against this instance. If this column contains any other value, then collections are running or collections have errored out.
For details about ATP based on collected data, see Data Collection.
For ATP based on planning data to work correctly, collections and the plan run must be completed successfully. Plan_completion_date and data_completion_date columns in msc_plans table for the plan are populated if the plan has completed successfully. If any of these columns contains a null value, then the plan has errored out. The plan must be relaunched and completed successfully before performing the ATP check against this plan.
Oracle Global Order Promising Database Links
Run the following query to verify the database link at the source:
Select substr(a2m_dblink, 1, 35), substr(m2a_dblink, 1, 35) from mrp_ap_apps_instances;
a2m_dblink must contain the database link that points from the source to the destination.
m2a_dblink must contain the database link that points from the destination to the source.
Run the following query to verify the database link at the destination:
Select substr(a2m_dblink, 1, 35), substr(m2a_dblink, 1, 35) from msc_apps_instances where instance_code = l_instance_code;
where l_instance_id = instance_id of the source instance;
a2m_dblink must contain the database link that points from the source to the destination.
m2a_dblink must contain the database link that points from the destination to the source.
Note: Your organization's database administrator should be the person designated to run these SQL Scripts.
A basic understanding of the architecture of Oracle Global Order Promising is helpful. The following notes may also be helpful:
Data Collection is responsible for gathering all the relevant data in the Source Instance from the INV, BOM, PO, OE, MRP, and WIP schemas, and duplicating the data in the MSC schema for ATP and Oracle Advanced Supply Chain Planning purposes.
Since you can collect data from more than one instance, MSC has its own item numbering system that is seen in MSC_SYSTEM_ITEMS. This means that each item is assigned a new INVENTORY_ITEM_ID in MSC_SYSTEM_ITEMS when it is collected. The Source Inventory Item ID is stored under SR_INVENTORY_ITEM_ID.
SR_% is used in many common column names to denote a value on the Source Instance.
Many tables contain the first column PLAN_ID.
The PLAN_ID "-1" is reserved for the Collected Data.
Positive PLAN_ID numbers are for the ASCP planning data generated when a plan is executed in Oracle Advanced Supply Chain Planning.
Some collected data is used regardless of ATP type being executed, and do not need a PLAN_ID. Sourcing rules is an example.
MSC_PLANS and MSC_PLAN_PARTITIONS contain the setup data for your plans. For example, Collections is PLAN_ID = -1 in MSC_PLANS.
If you collect data from more than one instance, then the SR_INSTANCE_ID column in the MSC_% tables becomes very important since the data from the two different instances can be exactly the same with the exception of this value.
The ORGANIZATION_ID does not change in the MSC tables. It is differentiated by the SR_INTANCE_ID when collecting from more than one instance.
The basic queries below retrieve item and plan information to start diagnosis of an item problem:
From the Source instance, the following gives you:
The organization_id for your organization_code
The inventory_item_id which is the sr_inventory_item_id is MSC_SYSTEM_ITEMS
Select
i.concatenated_segments,
i.inventory_item_id,
i.organization_id
from
mtl_system_items_kfv i,
mtl_parameters p
where
i.concatenated_segments like '&Item_name'
and i.organization_id = p.organization_id
and p.organization_code = '&Organization_code'
and i.organization_id = p.organization_id;
Use
&Organization_Code = Enter your Org Code when prompted
&Item_name = Enter your Item Name when prompted
The following query gives you:
The instance_id of your source instance
The instance_code of your source instance
Select
instance_id, instance_code
from
mrp_ap_apps_instances;
From the destination, the two following queries should produce results to allow for most of the various variables:
Select
substr(item_name,1.30) Item_name,
sr_inventory_item_id,
inventory_item_id,
organization_id
from
msc_system_items
where
sr_inventory_item_id = &Source_Inventory_item_id
and organization_id = &Organization_id
and sr_instance_id = &Instance_ID
and plan_id = -1;
USE
&Source_Inventory_item_id = from first query on the source
&Organization_id = organization_id from the first quest on the source
&Instance_ID = from the second query of on the source
These SQL queries address the ATP Based on Collected Data Errors and the Other Data Errors from the Troubleshooting section.
Two possible issues:
The calendar is not assigned to the organization.
Select
calendar_code,
calendar_exception_set_id
from
msc_trading_partners
where
sr_instance_id = &Instance_id
and sr_tp_id = &Organization_id
and partner_type = 3;
Use
&Instance_id = instance id of the source instance
&Organization_id = organization id of the item
If the above two columns are not populated, then a calendar has not been assigned to the organization. Assign a calendar to the organization and run Data Collections with a Complete Refresh.
The request date is outside the date range of the calendar assigned to the organization.
Select
Min(calendar_date),
Max(calendar_date)
from
msc_calendar_dates
where
sr_instance_id = &Instance_id
and calendar_code = '&Calendar_code'
and exception_set_id = &Calendar_exception_set_id;
Use
&Instance_id = instance id of the source instance
&Calendar_code = calendar code from query in section a
&Calendar_exception_set_id = calendar exception set id from query in section a
Select
nvl(msi.atp_rule_id, tp.default_atp_rule_id) atp_rule_id
from
msc_system_items msi,
msc_trading_partners tp
where
msi.sr_inventory_item_id = &Source_Inventory_item_id
and msi.sr_instance_id = &Instance_id
and msi.organization_id = &Organization_id
and plan_id = -1
and tp.sr_instance_id = msi.sr_instance_id
and tp.sr_tp_id = msi.organization_id
and tp.partner_type = 3;
Use
&Source_Inventory_item_id = source inventory item id
&Instance_id = instance id of the source instance
&Organization_id = organization id of the sourcing organization
Select
item_name,
atp_flag,
atp_components_flag
from
msc_system_items
where
sr_inventory_item_id = &Source_Inventory_item_id
and sr_instance_id = &Instance_id
and plan_id = -1;
Use
&Source_Inventory_item_id = source inventory item id
&Instance_id = instance id of the source instance
&Organization_id = organization id of the item
Note: If the above query does not return a row for the organization that you are expecting, then the item has not been collected. Check the item's setup on the source instance. Run Data Collections with a Complete Refresh or Target Refresh. Then check for the item again.
Execute the following query:
Select
distinct(plan_id) Plan_id
from
msc_atp_plan_sn;
Then:
Select
plan_id,
from
msc_atp_plan_sn
where
sr_inventory_item_id = &Source_Inventory_item_id
and sr_instance_id = &Instance_id
and organization-id = &Organization_id;
Use
&Source_Inventory_item_id = source inventory item id
&Instance_id = instance id of the source instance
&Organization_id = organization id of the item
Note: If the above query does not return any data, then:
The Plan has not been made ATP-able. Check that the Inventory ATP Flag is the plan options is checked.
The Plan has not run successfully. Then:
Select
plan_completion_date,
data_completion_date,
plan_id
from
msc_plans
where
compile_designator = '&Plan_name';
Use
&Plan_name.
The name of the plan.
If plan is run successfully, then above two date columns should be populated.
The Plan does not contain the item. Then:
Select
item_name
from
msc_system_items
where
plan_id = &Plan_id
and sr_inventory_item_id = &Source_Inventory_item_id
and sr_instance_id = &Instance_id
and organization-id = &Organization_id;
Use
&Plan_id = The Plan_id from above
&Source_Inventory_item_id = source inventory item id
&Instance_id = instance id of the source instance
&Organization_id = organization id of the item
Run the following queries on the source instance:
Select
a2m_dblink from mrp_ap_apps_instances;
Note: The above query should return the name of only one valid database link.
The following query provides some of the possible invalid objects in the database:
Select
owner,
object_name,
object_type,
status
from
all_objects
where
status = 'INVALID'
and object_name like 'MSC%'
and object_type != 'UNDEFINED';
Then:
Select
owner,
object_name,
object_type,
status
from
all_objects
where
status = 'INVALID'
and object_name like 'MRP%'
and object_type != 'UNDEFINED';
Note: If any objects are owned by APPS_MRC, ignore them. Recompile the rest of invalid objects returned, and report the errors for each object that cannot be recompiled.
Run the following queries on both source and destination:
Select
FND_PROFILE.value('INV_CTP') from dual;
Note: This profile must be the same in both instances and should only be set at the site level.
Select
rule_name,
past_due_demand_cutoff_fence,
past_due_supply_cutoff_fence,
include_sales_orders,
include_purchase_orders,
include_discrete_wip_receipts,
include_nonstd_wip_receipts,
include_discrete_mps,
include_rep_mps,
include_rep_wip_receipts
from
msc_atp_rules
where
rule_id = &ATP_Rule_id
and sr_instance_id = &Instance_id;
Use
&ATP_Rule_id = rule id determined using query in the section above
&Instance_id = instance id of the source instance
Note: For any supply and demand types that have values returned as 1, ATP only considers the past due demand and supply for that many days.
For example: for Past Due Demand Days of 5 days, ATP only considers past due demands that fall under the sysdate of 5 days. To consider all past due demands, these fields should be left blank.