On Hand Quantity in R12 TABLE Detail

Find On Hand Quantity in R12

R12 Tables To Find the On hand Quantity

on hand Quantity --> quantity from mtl_onhand_quantities_details
Allocated Quantity --> mtl_material_transactions_temp
Reserved Quantity --> mtl_reservations

Hope this Information will help you to develop simple SQL Scripts ...

How to Open Multiple Oracle Instance in Windows

How to Open Multiple Oracle Instance in Windows :

Go to IE in desktop

Right Click and select properties.

In Target add space –nomerge after iexplore.exe"

Like below and it works.

"C:\Program Files (x86)\Internet Explorer\iexplore.exe" -nomerge

OPM Process Execution Tables R12

OPM PROCESS EXECUTION TABLES R12

• GME_BATCH_HEADER
• GME_BATCH_HEADER_MIG
• GME_BATCH_HISTORY
• GME_BATCH_MAPPING_MIG
• GME_BATCH_SALES_ORDERS
• GME_BATCH_STEPS
• GME_BATCH_STEPS_MIG
• GME_BATCH_STEP_ACTIVITIES
• GME_BATCH_STEP_ACTIV_MIG
• GME_BATCH_STEP_CHARGES
• GME_BATCH_STEP_CHARGES_MIG
• GME_BATCH_STEP_DEPENDENCIES
• GME_BATCH_STEP_DEP_MIG
• GME_BATCH_STEP_ITEMS
• GME_BATCH_STEP_ITEMS_MIG
• GME_BATCH_STEP_RESOURCES
• GME_BATCH_STEP_RESOURCES_MIG
• GME_BATCH_STEP_RSRC_SUMMARY
• GME_BATCH_STEP_TRANSFERS
• GME_BATCH_STEP_TRANSFERS_MIG
• GME_BATCH_TXNS_MIG
• GME_ERES_GTMP
• GME_EXCEPTIONS_GTMP
• GME_GANTT_DOCUMENT_FILTER
• GME_INVENTORY_TXNS_GTMP
• GME_LAB_BATCH_LOTS
• GME_MATERIAL_DETAILS
• GME_MATERIAL_DETAILS_MIG
• GME_MATERIAL_DISPENSING_GTMP
• GME_MIGRATION_CONTROL
• GME_PARAMETERS
• GME_PENDING_PRODUCT_LOTS
• GME_PROCESS_PARAMETERS
• GME_PROCESS_PARAMETERS_MIG
• GME_RESOURCE_TXNS
• GME_RESOURCE_TXNS_GTMP
• GME_RESOURCE_TXNS_MIG
• GME_RESOURCE_TXNS_SUMMARY
• GME_SCALE_DETAIL
• GME_TEMP_EXCEPTIONS
• GME_TEXT_HEADER
• GME_TEXT__TL
• GME_TRANSACTION_PAIRS
• GME_UNALLOCATED_ITEMS_GTMP

OPM Inventory Tables R12

OPM INVENTORY TABLE R12

• BISOPM_ONHANDSALE_SUM
• BIS_OPM_PROD_SUM
• GMI_AUTO_ALLOCATION_BATCH
• GMI_CATEGORY_SETS
• GMI_CLOS_WAREHOUSES
• GMI_DISCRETE_TRANSFERS
• GMI_DISCRETE_TRANSFER_LINES
• GMI_DISCRETE_TRANSFER_LOTS
• GMI_ITEMS_XML_INTERFACE
• GMI_ITEM_CATEGORIES
• GMI_ITEM_CONV_AUDIT
• GMI_ITEM_CONV_AUDIT_DETAILS
• GMI_ITEM_ORGANIZATIONS
• GMI_LOTS_CONV_XML_INTERFACE
• GMI_LOTS_XML_INTERFACE
• GMI_LOT_TRACE
• GMI_MASSTRANS_CTL
• GMI_MIGRATION_PARAMETERS
• GMI_OBSOLETE_ITEM_COLUMNS
• GMI_QUANTITY_XML_INTERFACE
• GMI_SUBLOT_GENERATE
• GMI_TRAN_TMP
• IC_ADJS_JNL
• IC_ALLC_CLS
• IC_ALOT_PRM
• IC_CLDR_DTL
• IC_CLDR_HDR
• IC_CLDR_HDR_B
• IC_CLDR_HDR_TL
• IC_COMD_CDS
• IC_COST_CLS
• IC_CRUL_CLS
• IC_CTMS_CLS
• IC_CYCL_ADT
• IC_CYCL_DTL
• IC_CYCL_ERR
• IC_CYCL_HDR
• IC_CYCL_MSC
• IC_DECM_MST
• IC_FRGT_CLS
• IC_GLED_CLS
• IC_INVN_CLS
• IC_INVN_TYP
• IC_ITEM_CDT
• IC_ITEM_CNV
• IC_ITEM_CPG
• IC_ITEM_HIERARCHY
• IC_ITEM_MST
• IC_ITEM_MST_B
• IC_ITEM_MST_B_MIG
• IC_ITEM_MST_TL
• IC_ITEM_WHS
• IC_JRNL_MST
• IC_LOCT_INV
• IC_LOCT_MST
• IC_LOTS_CPG
• IC_LOTS_MST
• IC_LOTS_MST_MIG
• IC_LOTS_STS
• IC_PERD_BAL
• IC_PHYS_CNT
• IC_PHYS_ERR
• IC_PKGS_MST
• IC_PLNT_INV
• IC_PRCE_CLS
• IC_PRCH_CLS
• IC_PURG_PRM
• IC_RANK_MST
• IC_SALE_CLS
• IC_SHIP_CLS
• IC_STAT_RPT
• IC_STOR_CLS
• IC_SUMM_INV
• IC_TAXN_ASC
• IC_TAXN_CLS
• IC_TEXT_HDR
• IC_TEXT_TBL_TL
• IC_TRAN_ARC
• IC_TRAN_CMP
• IC_TRAN_PND
• IC_TXN_REQUEST_HEADERS
• IC_TXN_REQUEST_LINES
• IC_WHSE_INV
• IC_WHSE_MST
• IC_WHSE_REL
• IC_WHSE_STS
• IC_XFER_MST
• IN_ADDR_MST
• IN_CTRY_MST
• IN_ITEM_MST
• IN_REGN_MST
• IN_STAT_CTL
• IN_STAT_DTL
• IN_STAT_HDR
• IN_STAT_MSG
• IN_TEXT_HDR
• IN_TEXT_TBL_TL
• IN_TOFD_MST
• SY_WF_ITEM_ROLES

Work Flow Mailer Send Mail from the Cloned Instance

Do you find that ,

YOur Work Flow mailer is trigerring Mail often when you clone your Instance and you are unable to test the New stuff using work Flow Mailer


Here is the solution for the Work flow mailer to stop sending older mails in the Test Instance

UPDATE WF_NOTIFICATIONS

SET STATUS = 'CLOSED'

where status ='OPEN';

UPDATE WF_NOTIFICATIONS

SET MAIL_STATUS = 'SENT'

where status ='OPEN';

commit; 

Simple , but very Effective steps to Contrl your work flow mailer , sending old mails


Check  this Steps too:

Applicatioin Manager -- > Application Dashboard ->


Application System-- > Dev--> service Components --> Component Details

Set override Address : Dev: Work Flow Notification Mailer

Enter the Over Ride Address : *******. Mail .Com


Update the scripts:

update fnd_svc_comp_param_vals fscpv
set fscpv.PARAMETER_VALUE = '<override email address>'
where fscpv.parameter_id in (select fscpt.parameter_id
from fnd_svc_comp_params_tl fscpt
where fscpt.display_name = 'Test Address');

If this is Entered he/She will receive all the notification mails which has beed triggred in the DEv or UAT

Step 1Login to "Workflow Administrator Web Applications"


Step 2
Ensure that Notification Mailer is running, and then click on icon as below


Step 3
Click on "View Details"




Step 4.
Click on "Set Override Address"




Step 5.
Finally you can change the email address here. Please read the instructions in red carefully.

Unable to View the Assigned Responsibility

Unable to View the Assigned responsibility Even after the End date is removed from the USER


Run the Concurrent Program

Synchronize WF LOCAL tables

Workflow Directory Services User/Role

If still you find this issue is not resolved , then pls go head and Run the Script in the Putty

exec wf_maintenance.ValidateUserRoles(p_BatchSize => 1000, p_check_dangling => FALSE, p_check_missing_ura => TRUE, p_UpdateWho => FALSE);

Go to respon Funtional Administrator > Home > Core Services > Caching Framework > Global Configuaration > Clear All Cache

This should work

Disable diagnostics/examine/personalization Password

How to disable prompting the password for diagnostics/examine/personalization for forms ?

To disable prompting the apps password for diagnostics and examine/personalizations at form level



Set the value as Yes to the profile option
 "Utilities: Diagnostics" at any level


Utilities: Diagnostics = YES


Debug Profile Value , to Debg the Concurrent Program OM and Inventory

Hi Maria,


The Pick Selection Triggered and i have attached the Log files for your reference ,

Debug Profile values has been set as follows

OM: Debug Level - set to 5
INV: Debug Level - set to 10
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement

Set it to User Level , So that It will not Impact system performance

Unable to Run "Indented Bill of Materials"

Unable to Run "Indented Bill of Materials"

All ingredients should have a value for contribute_yield_ind of either 'Y' or 'N'. This indicator is only relevant to ingredients - it is not even visible on screen for products or by products. However, it appears that if the value is NULL for a product, the report has a problem. all products have a value of 'Y'. This will have no effect on anything else in the system because this column is not relevant to products. We can amend the update to make sure that we do not change any ingredients as follows, but there should not be any ingredients with a null value for this column, and if there is we need to fix that also.

update GMD.fm_matl_dtl
set contribute_yield_ind = 'Y'
where contribute_yield_ind is NULL
and line_type <> -1;
　
The above Query to be updated in production to make the Report run


Note: This will have no effect on anything else in the system because this column is not relevant to products

Un Costed Sales Order Line in R12

UNCosted Lines in Sales Order 　

　

 The number of processed rows in MTL_MATERIAL_TRANSACTIONS:
select count (*)
from MTL_MATERIAL_TRANSACTIONS
where COSTED_FLAG like NULL;
To resubmit the erred records in the MTL_MATERIAL_TRANSACTIONS table, it
must be done via a SQL statement by updating the costed_flag = ‘N’ and the
transaction_group_id = NULL.
Update MTL_MATERIAL_TRANSACTIONS
set COSTED_FLAG = ‘N’,
set TRANSACTION_GROUP_ID = NULL
where COSTED_FLAG = ‘E’ or COSTED_FLAG = ‘N’;



You can also Cross check data with this

We need a test instance to test this script

　

　1. Please run the following SQL to identify the transactions in error.

select * from mtl_material_transactions where costed_flag IN ('E','N');

2. In case the isssue is in the interface table, please run:

select
count(process_flag) total,
process_flag pflag,
substr(error_code,1,40) Code,
substr(error_explanation,1,65) Explan
from
mtl_transactions_interface
group by
process_flag, error_code, error_explanation;

3. Also check the temp table with the sql

select
count(process_flag),
process_flag,
substr(error_code,1,40) Code,
substr(error_explanation,1,65) Explan
from
mtl_material_transactions_temp
group by
process_flag, error_code, error_explanation;

Global Forecast Present in Collections Workbench But Not In Planner's Workbench [ID 1502156.1]

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

R12.1.1 CU3 - PRODUCTION - Unconstrained

Customer is generating global forecasts from Demantra and feeding these into ASCP at the Zone consumption level. Some items are not getting planned.
The items (both good and bad example) belong to a category set called AG_PKG_ILA and a sourcing rule is defined at the category level. This sourcing rule is added to the assignment set 'Nafta Global Assignment'.
Forecast is showing up for item 10473225 but not for 10305225.
During the OWC, we ran

select * from msc_demands
where plan_ID = 2 and origination_type = 29 and organization_id = -1 and inventory_item_id = 1291;

Plan Options were set to Overwrite None. Changed it to 'All' but still did not see the forecast for item10305225.
Customer then ran the plan with 'Demand Scheduled Items and Sales Orders' but still no Forecast for item 10305225.
(Customer even tried running the plan without the assignment set)

Item 10473225 is working correctly. Its inventory_item_id is 214633. It has a working global forecast for which planned orders were generated.
It belongs to category MTO, category_set_id = 1001, category_id = 117244

Item 10305225 is not working correctly. Its inventory_item_id is 1291. We see the forecast quantities under using_requirement_quantity (the global fc quantities) but no planned orders are generated.
It belongs to category MTS, category_set_id = 1001, category_id = 117245
For this plan run we have plan_id =3

When the customer changes the category AG_PKG_ILA on the non working item 10305225 to 'ILA_IL1_CUST' at the Item Master level, then recollects and replans, then the item is planned correctly.
For this plan we have plan_id = 2

This is happening in both PROD and TEST. The customer started using Demantra forecasts last week and though that all items were performing as expected. Several items, including 10305225 are not and this was only discovered last week. The customer is currently manually calculating forecasts for these items and manually creating planned orders in Production.

So the issue appears to be caused by a category. However, why do we get three planned orders for inventory_item_id 1291 in plan_id 3 (see msc_supplies.xls) while there are many more Forecasts that are not being planned?
Please investigate the following transaction_id in msc_supplies.dat for plan_id 3. Why are there only three planned orders generated while there are 83 forecasts for this item which we expect to see in the Planner's workbench.


  40267480
  42610172
  44465006

Cause

Global forecast is not getting distributed for the items where only BOD is defined.
It is getting distributed or distribution rules is being snapshoted if there is an instance level sourcing rule present for that category.

Research in bug 14563239

Solution

A BOD can only specify sources to an org. In the BOD, you must select the receiving org and then in the lower window, the user selects the sources for the receiving org. Therefore, a BOD cannot be applied to an item, it can only be applied to item-org.
So a BOD is not used by Global Forecasting and users should use sourcing rule instead of BOD for defining distribution rules for global forecast.

SQL on the Pegging Information

Case #1

Pegging in the plan is not correct and need SQL output to see results for one item in the plan.
This is standard SQL used by DEV to help understand the pegging in the plan output

Then we show item SQL to check for Order Modifiers which could be effecting how the item is pegging

Case #2

We are reviewing the Plan output and see that planned orders are appearing early in the plan.
When we review the pegging, we see that some orders have pegging lines that are more than 30 days into the future and we want to be able to get SQL output that will help us identify these orders throughout the plan and investigate/determine the possible cause(s)?

Fix

The following SQL scripts can be used to check item pegging, item order modifiers and check for for early orders.

Pegging SQL #1
Variation to check for a specific item
-- This is usually caused by Order Modifiers on the item - see Item SQL to follow up
-- requires you to provide the plan_id, inventory_item_id, organization_id, sr_instance_id

select /*+ ORDERED */
    s.inventory_item_id, s.organization_id
    s.new_schedule_date hls_sup_dt, /* hls final schedule date*/
    p.supply_date mbp_sup_dt, /* mbp supply  date during the pegging */
    p.supply_quantity peg_sup_qty,
    p.demand_date peg_dmd_dt, /* mbp demand date during the pegging */
    p.demand_quantity peg_dmd_qty,
    p.allocated_quantity peg_acl_qty,
    s.unbucketed_demand_date sup_unb_dbd_dt, /*demand date for push down */
    d.using_assembly_demand_date dmd_dmd_dt, /* hls final schedule date for the demand */
    nvl(d.demand_id,p.demand_id) demand_id,
    d.origination_type orig_ty,
    NVL(s.order_type, p.supply_type) ord_ty,
    d.disposition_id dmd_disp_id, /*disposition_id is the assembly level supply's transaction_Id for dependent demands */
    NVL(s.transaction_id, p.transaction_id) trans_id,
    p.pegging_id  peg_id, /* pegging_id is  in sequence, the earlier peg gets lower value */
    s.sco_supply_date sco_sup_dt,/* sco supply date for the planned orders or non-firm existing orders */
    s.unbucketed_start_date unbkt_st_dt
from
msc_full_pegging p
, msc_supplies s
, msc_demands d
where
 p.plan_id= &plan_id
and p.plan_id = s.plan_id (+)
and s.transaction_id (+)= p.transaction_id
and d.demand_id (+)= p.demand_id
and d.plan_id (+) = p.plan_id
and p.inventory_item_id= &item_id  -- use this if you want to check a particular item/org/instance combination
and p.organization_id= &org_id   -- use this if you want to check a particular item/org/instance combination
and p.sr_instance_id = &inst_id   -- use this if you want to check a particular item/org/instance combination
order by p.pegging_id;

Item SQL
Item check for order modifiers
-- use this to check item setups that could explain why orders are seen "early"
-- if they have modifiers, then we may be required to get the quantity or fixed days supply which makes order look too early or too large

select substr(ITEM_NAME,1,30) Item_name
,PLAN_ID                    
,ORGANIZATION_ID            
,INVENTORY_ITEM_ID          
,SR_INSTANCE_ID             
,SR_INVENTORY_ITEM_ID       
,LOTS_EXPIRATION            
,LOT_CONTROL_CODE           
,FIXED_DAYS_SUPPLY          
,FIXED_ORDER_QUANTITY       
,FIXED_LOT_MULTIPLIER       
,MINIMUM_ORDER_QUANTITY     
,MAXIMUM_ORDER_QUANTITY     
,ROUNDING_CONTROL_TYPE      
,PLANNING_TIME_FENCE_DAYS   
,PLANNING_TIME_FENCE_DATE   
,FIXED_LEAD_TIME            
,VARIABLE_LEAD_TIME         
,PREPROCESSING_LEAD_TIME    
,POSTPROCESSING_LEAD_TIME   
,FULL_LEAD_TIME             
,CUMULATIVE_TOTAL_LEAD_TIME 
,CUM_MANUFACTURING_LEAD_TIME
from msc_system_items
where PLAN_ID  = &plan_id
and ORGANIZATION_ID = &org_id
and INVENTORY_ITEM_ID = &item_id                  
and SR_INSTANCE_ID = &inst_id;

 In an Unconstrained plan or Constrained EDD plan, most often, we see the cause is due to Order Modifiers being used for the item, so after identifying the items via the Pegging SQL, use the Item SQL to check the item setups.
In Constrained ECC and also in Constrained EDD - the plan results could be influenced by Resource or Supplier Constraints that cause the plan to move orders to a time in the plan where we have ability to produce or procure based on these constraints.
There have also been bug fixes released for these types of issues.
- In 11.5.10 and 12.0.6 in the latest ASCP Engine/UI patches, we find most all these issues related to bug fixes are resolved.
- In 12.1.3, if the customer is on earlier cumulative patches, then there might be some bug fixes that have not been performed that could cause this issue... At the time of this writing, VCP 12.1.3.8 is the latest Cumulative patch available (Ref Note 746824.1) and we have seen most of these fixes ported and included in the code.

Pegging SQL #2
Check for all planned orders that appear earlier than demand date by 30 days or more
-- This is usually caused by Order Modifiers on the item - see Item SQL to follow up
-- this uses threshold of 30 days early - which can be modified
-- requires you to provide the plan_id

select /*+ ORDERED */
    s.inventory_item_id, s.organization_id
    s.new_schedule_date hls_sup_dt, /* hls final schedule date*/
    p.supply_date mbp_sup_dt, /* mbp supply  date during the pegging */
    p.supply_quantity peg_sup_qty,
    p.demand_date peg_dmd_dt, /* mbp demand date during the pegging */
    p.demand_quantity peg_dmd_qty,
    p.allocated_quantity peg_acl_qty,
    s.unbucketed_demand_date sup_unb_dbd_dt, /*demand date for push down */
    d.using_assembly_demand_date dmd_dmd_dt, /* hls final schedule date for the demand */
    nvl(d.demand_id,p.demand_id) demand_id,
    d.origination_type orig_ty,
    NVL(s.order_type, p.supply_type) ord_ty,
    d.disposition_id dmd_disp_id, /*disposition_id is the assembly level supply's transaction_Id for dependent demands */
    NVL(s.transaction_id, p.transaction_id) trans_id,
    p.pegging_id  peg_id, /* pegging_id is  in sequence, the earlier peg gets lower value */
    s.sco_supply_date sco_sup_dt,/* sco supply date for the planned orders or non-firm existing orders */
    s.unbucketed_start_date unbkt_st_dt
from
msc_full_pegging p
, msc_supplies s
, msc_demands d
where
 p.plan_id= &plan_id
and p.plan_id = s.plan_id (+)
and s.transaction_id (+)= p.transaction_id
and d.demand_id (+)= p.demand_id
and d.plan_id (+) = p.plan_id
-- and d.origination_type = 29 using this line will restrict the query to pick only forecast demands to peg
and NVL(s.order_type, p.supply_type) = 5 -- using this line will restrict the pegging to only planned orders
and trunc(d.using_assembly_demand_date) - trunc(p.supply_date) >30  -- use this as threshold for how early orders are before the demand
order by p.pegging_id;

ASCP Pegging Information Logic

The Logic behind the storage of data in the Pegging tables

SCENARIO - 1
==============

Consider we have a Finished Good Item A with the following BOM [Bill Of Material]
A -> Finished Good Item
B -> Subassembly Item
C -> Purchased Good.

Consider we have a Forecast for the Item A, for quantity 100.

So after running the Plan the pegging would like below in the UI [User Interface / Planners Work Bench]...

Forecast Demand for A
    - Planned Order for A
        - Planned Order demand for B
            - Planned Order for B
                - Planned Order demand for C
                    - Planned Order for C


How is this seen in the Database Tables.
------------------------------------------------------------

The Demands are stored in the table MSC_DEMANDS
Say it looks something like this >>



The ORIGINATION_TYPE is actually a number. represented here as a meaning for better understanding.

The Supplies are stored in the table MSC_SUPPLIES
Say it looks something like this >>



How the above is hooked up in the MSC_FULL_PEGGING
Say it looks something like this >>



So as we see there would be three rows showing which demand is pegged to which supply. The Supply here is the TRANSACTION_ID.
The below is what you are seeing. >>

Forecast Demand for A
    - Planned Order for A

            - Planned Order demand for B
                    - Planned Order for B

                            - Planned Order demand for C
                                    - Planned Order for C

The relation between the Planned Order Demand of B with the forecast demand of A is stored in the PREV_PEGGING_ID .
The END_PEGGING_ID is the top demand in the whole chain.

SCENARIO - 2
=============

If we consider the quantities and we have 2 Forecasts in our Scenarios then how would the pegging work.
Consider we have a Finished Good Item A with the following BOM [Bill Of Material]
A -> Finished Good Item
B -> Subassembly Item

Consider we have a Forecast F1 for Item A for quantity 10
Consider we have another Forecast F2 for Item A for quantity 15

So after running the Plan the pegging would like below in the UI  [User Interface / Planners Workbench]

Forecast Demand F1 for A for quantity 10
            - Planned Order for A for quantity 25 Pegged quantity 10
                    - Planned Order demand for B for quantity 25 Pegged Quantity 10
                            - Planned Order for B for quantity 25 Pegged Quantity 10

Forecast Demand F2 for A for quantity 15
            - Planned Order for A for quantity 25 Pegged quantity 15
                    - Planned Order demand for B for quantity 25 Pegged Quantity 15
                            - Planned Order for B for quantity 25 Pegged Quantity 15

How is this seen in the Database Tables.
------------------------------------------------------------

The Demands are stored in the table MSC_DEMANDS
Say it looks something like this >>



The ORIGINATION_TYPE is a actually a number represented here as a meaning for better understanding.


The Supplies are stored in the table MSC_SUPPLIES
Say it looks something like this >>



How the above is hooked up in the MSC_FULL_PEGGING
Say it looks something like this >>



The relation between the Planned Order Demand of B with the forecast demand of A is stored in the PREV_PEGGING_ID .
The END_PEGGING_ID is the top demand in the whole chain.


SCENARIO - 3
=============

If we consider the quantities and we have 2 Forecasts in our Scenarios then how would the pegging work.
Consider we have a Finished Good Item A with the following BOM [Bill Of Material]
A -> Finished Good Item
B -> Subassembly Item

Consider we have a Forecast F1 for Item A for quantity 10.
Consider we have another Forecast F2 for Item A for quantity 15.

Also we have a On hand for Component B with quantity 5.

So after running the Plan the pegging would like below in the UI [User Interface / Planners Workbench]

Forecast Demand F1 for A for quantity 10
                    - Planned Order for A for quantity 25 Pegged quantity 10
                            - Planned Order demand for B for quantity 25 Pegged Quantity 10
                                    - Onhand for B for quantity 5 Pegged Quantity 5
                                    - Planned Order for B for quantity 20 Pegged Quantity 5

Forecast Demand F2 for A for quantity 15
                    - Planned Order for A for quantity 25 Pegged quantity 15
                            - Planned Order demand for B for quantity 25 Pegged Quantity 15
                                    - Planned Order for B for quantity 20 Pegged Quantity 15

How is this seen in the Database Tables.
------------------------------------------------------------

The Demands are stored in the table MSC_DEMANDS
Say it looks something like this >>



The ORIGINATION_TYPE is a actually a number. represented here as a meaning for better understanding.


The Supplies are stored in the table MSC_SUPPLIES
Say it looks something like this >>



How the above is hooked up in the MSC_FULL_PEGGING
Say it looks something like this >>




The relation between the Planned Order Demand of B with the forecast demand of A is stored in the PREV_PEGGING_ID .
The END_PEGGING_ID is the top demand in the whole chain.

ATP Process and Drill Down ATP

How Can I Drill Into ATP Availability Results to Understand ATP Scheduling Date?

Key Points:

• Drill to the ATP Details Screen and check Pegging Details - take a screen shot
• You must drill to the ATP Supply/Demand to find out what ATP considered for supply and demand in scheduling the order
• MRP: Calculate Supply/Demand = Yes is required for ATP to return these details.
• Review the Example below which shows navigation and details to access this view.
• ATP Supply/Demand will only show supply and demand that was considered to schedule the order. Future supply and demand past the Available Date returned in the Availability window is NOT shown in the results.. 
• For ATP Based On Collected Data
• Results seen in this screen can be verified in the Collections Workbench (View Collected Data)
• Only order lines with Scheduled Ship Date will appear in this screen.
• For ATP/CTP Based on Planning Data
• Results seen in this screen can be verified in the ASCP Planner Workbench (for your ATP Plan)
• Please note that ATP will honor Reschedule and Cancel messages in the plan output. To avoid these messages, you will have to firm the supply in source application (PO or WIP)
• Check the ATP Rule being used for the item and understand the Infinite Supply Time Fence setting
• If you use a Lead Time setting like Total Lead Time,. then review the item setups
• ATP Supply / Demand does NOT show the current sales order line in the results because we are checking availability of this line.
• Customers will sometimes use entered sales order line to check availability of an item -This is wrong - use ATP Inquiry to check availability which includes all supplies and demands for a given item.

Next Steps:

• Review the example below and other parts of this note for a helpful guide to investigating and understanding the results
• Several other Questions in this note will explain behaviors under certain conditions
• If you are having trouble understanding you results after reviewing this information and require assistance from support, then we need to see all the details of the ATP results
• Screen shot of the Availability results
• Screen shot of the pegging results
• /File /Export of the ATP Supply /Demand to verify date for all lines and columns
• /File /Export of the Workbench information (Collections Workbench OR Planner Workbench)
• Screen shot of the ATP Rule used for this item
• Provide Item Lead Time setups if these influence the ATP results.
• ATO Items setups can be printed  for review using SQL in BomExplode.zip

Example -Why can't I ship my sales order today when I have enough on-hand?

The first screen shot show the ATP results and the following will show more detailed steps to review the results.

Here we make the point  - do NOT use the Inventory Supply/Demand screen to try and understand the ATP results.
ATP Results come from the MSC tables, and not the regular Transaction tables of Inventory, OM, PO, WIP, etc which are used for the Inventory Supply/Demand screen
Data Collections is the process that updates the data in the MSC tables.
ATP processes for new Sales orders or canceled Sales Orders will also update the MSC tables to keep ATP results in synch when new demand is placed via a sales order.

The ATP Details screen will display pegging information. To allow access to rt-click functionality in the pegging window and see further information required to investigate the issue, you must set profile MRP: Calculate Supply Demand = Yes
-- Note that this can have performance implications, but when trying to debug ATP results, this is required. Set at the User Level is usually the best solution.

IF using 'ATP Based On Collected Data', then the results are coming from data that is in the Collections Workbench (View Collected Data menu option)
AND IF using 'ATP/CTP Based on Planning Output', then the results of the ASCP Plan are where the ATP results are coming from to populate the ATP Details and produce the Supply/Demand picture being presented in ATP. Note that these workbenches can show data that is not being used for ATP.

The Supply/Demand here will show how ATP arrived at the results in the Availability Window and which demands are taking the supply that could be used today.
Since ATP is First-Come/First Served, it will not be able to schedule the order for today, unless steps are taken.
See the next question  - for a discussion of the possibilities.
Also since the Supply/Demand window is small and displays only 5 lines, there will be times when many lines exist and using /File /Export to generate a TSV file which can be viewed in MS Excel is the easiest method to view the results in total - see How can I view ATP Supply/Demand results easily? if you are not familiar with this process.

How Can We Avoid The Error: Scheduling Failed. Cannot Meet Request Date or Latest Acceptable Date: Material Available Date (DD-MON-YY)

You can get this error when attempting to schedule a Sales Order Line,
OR in the Availability Window for ATP in the sales order line, you will see the "Cannot meet request date or Latest Acceptable date"

Latest Acceptable Date is populated via Defaulting Rules and can be setup at:
A> Order Header Level (Latest Schedule Limit) - which will be passed to the order line as Latest Acceptable Date
OR
B> Order line level (Latest Acceptable Date)

You can view Latest Acceptable Date in the Shipping Tab by using /Folder/Show Field to reveal the column
If you have setup at the header level, then in the Other Tab, you can show field Latest Schedule Limit

Typically, this is a date that is far into the future to prevent the scheduling faillure....

This DOES NOT MEAN that you will get today's date on a sales order, but it does mean that you will get a schedule date in the future, usually the infinite supply time fence date which is defined in the ATP rule.
You will likely still see an "error" different than before, which is "Unable to meet the request quantity"

Below is an example of Defaulting rule setup in our Internal Instance and how it effects the LAD in the sales order line.

Why is Discrete Job Or PO Supply Available Today, But Not Available for ATP On the Next Day?

In ATP Based on Collected Data, it is necessary to create supplies to satisfy Sales Order demand that is scheduled at the Infinite Supply Time Fence date BEFORE the next day.
Otherwise, when you move forward in time to the next day, this demand is now INSIDE the Infinite Time Fence and can consume any expected supplies.
Note 883278.1 has been created to provide a detailed scenario and explanation of how ATP works for this scenario.

Can I build and ship an ATO Today? ATP Returns results of today + 1 day for item I can build in 1 hour and ship today.

ATP is a DAY level scheduling engine, so this is not possible under the current design.

Typical ATO for this scenario.

ATO which has components that are stocked on hand.
ATO has routing that has short work time (e.g. After Routing lead time calculation and Rollup - Leadtime shows ZERO Fixed LT and 0.125 Variable LT)
When ATO is configured for build of 1 item, and job is created, the job completes in just over 1 hour.

ATP Schedule Date results are the same if using ATP Based on Planning Output or ATP Based on Collected Data.

Example 1: Order QTY = 1 - ATP detects Variable LT and since it schedules by the DAY, it must consider this as using a full day, so the results show that Order will be satisfied on SYSDATE + 1

Example 2: Order QTY = 50 - ATP calculates the Variable LT accurately, by "hours", but there is partial day used at end of this calcuation, so this partial day at end of calculation is rounded to a full day.
Order will be satisfied on SYSDATE + 10 days based on leadtime, resource availability, and MFG calendar working days.

An Enhancment 7711440 has been entered to allow ATP to consider this and be able to return a schedule date of today for these type of short build ATO models.

How can I ship it today, when I cannot schedule the order?

There are two options: ATP Override and Unscheduling Other Orders make inventory avaiable for this order.
ATP Override
Continuing with the example used above, we will show how to use ATP Override, which is the fastest way to get the order shipped.
Set the profile OM: Authorize ATP Override at the Site or User Level to allow the functionality



In the Shipping tab, the user can use / Folder / Show Field (or rt-click mouse as shown below) and reveal the ATP Override field




Then the field will be available in the Shipping Tab of the order line.
And any schedule date may be entered by the user.

Unschedule Other Orders
Order lines in the future, which are taking up supply, can be Unscheduled, using /Tools/Scheduling - then choose Unschedule
Then the order line for today can be scheduled. Then the unscheduled lines can be scheduled again.

Why does ATP Based on Planning Data does not see PO Supplies in the ATP Plan and returns Infinite Supply Quantity today?

We are using ATP based on Planning Data
We have Buy Item with leadtimes setup to 0 for all pre-processing, processing and post-processing leadtime
There is not any on hand quantity for this item.
PO supplies are due today and also in the next fiew days in the ATP Plan results.
Since we source from many different suppliers, we do not have sourcing rules setup for the item.
Check ATP and ATP Components are setup to Material Only
We have ATP rule setup for 180 days user defined.

ATP returns result that item is available in Infinite Supply quantity of 10 billion today and ATP Pegging does not see the PO supplies we have in the ASCP Plan.

Why does ATP not see these PO supplies?

ANSWER:
When you use planning data and have the setups defined above, ATP will try to plan for the item, and when you have no leadtimes, then ATP determines that the item is available today - immediately and therefore it does not need to check for any supplies that may be coming in for the item.

You need to setup a reasonable leadtime for the item to reflect reality and then it will search the supply chain for any expected supplies.

If you do NOT have Sourcing defined, then ATP will not find a supplier, so it will return the Infinite Supply quantity of 10 billion.
If you have Sourcing defined, then ATP will return the Supplier from the Sourcing rule in the ATP Pegging results.

Both examples below would be considered valid results - Key setup needed is leadtime to get correct results.

Example #1 - Customer on base release code of 11.5.10 (Rollup #1 - from 2005) and see today returned, and no reference to PO's expected:


Example #2 - Internal test with Sourcing which shows you can procure from supplier today and ship tomorrow - in this case, there was not PO supply due on request date
Note this test was conducted against the latest ATP code from Aug-2009 Rollup #26.

How are the results calculated for the On-Hand and Reservable in Availability Window?

ALL subinventories are used to display the On Hand Qty seen in the Availability window. OM calls an Inventory private API to display this information. This is the design so that the user has complete picture of all available inventories and may make decisions based on this knowledge.

The Qty Reservable is also called by OM using a standard Inventory private API for Hard Reserved quantities.
In this example, the quantities were reserved using / Tools / Scheduling / Reserve

This means that both these fields are displaying data that may not be consistent with ATP data in the MSC tables, The example below shows how the data in the Availability Window could be different from data in the ATP Supply/Demand window.

ATP Supply/Demand window, it will show the line(s) with Order Type = On Hand quantity based on the following:
1. ATP based on Collected Data - ATP-able subinventories as defined in Inventory / Setup / Organizations / Subinventories
2. ATP/CTP based on Planning Data - All inventories included in the ASCP Plan Options / Organizations Tab - Subinventories button. This means that if there is a Nettable subinventory that is NOT ATP-able, any supply from that subinventory that is included in the plan could will as available in the ATP Supply/Demand window results unless you explicitly remove that sub inventory from the plan options of your ATP plan.

Closed, Canceled, or Wrong Quantity Sales orders showing up in the ATP Supply/Demand and keeping me from scheduling orders. How can I fix this?

This issue has been reported in several ways and is always difficult to duplicate:

1. The order line has been deleted. This can be prevented in Order Management by using setups to not allow order lines to be deleted. Companies that are not using ATP may find that they want to be able to simple delete lines. But any ATP user should not allow this to occur and should instead use Cancel functionality for any sales order line that is no longer required. 
2. The Order line in ATP Supply/Demand results has the wrong quantity. This is reported when user changes line quantity on a scheduled line and ATP is not notified. To avoid this issue, users should use / Tools / Scheduling / Unschedule BEFORE changing any line quantities. Then Schedule the order line again after changing the quantity.
   As an example, with auto-scheduling turned on, order is entered for 100 and ATP Supply/Demand shows that 40 is available now. User changes quantity from 100 to 40 and enters new line for remaining quantity - 60.
3. The Order Line has been canceled, but still shows in the ATP Supply/Demand Results.
   The difficulty usually encountered with this issue, is that customers do not know how to duplicate the issue.
   For example, in internal testing, the issue was not duplicated when the following steps were performed.
   Item has 200 on hand and created 3 sales orders for an item totaling 190.
   Then canceled one sales order for 150 each,
   Then created a new sales order for 100 each and ATP was fine... it had been updated correctly for the canceled order and I was able to schedule the new order. This is the way it is supposed to work. When a Sales order is canceled, then ATP is notified so that the ATP results for the next order are correctly calculated.
4. The Order Line has been Shipped and Closed, but still shows in the ATP Supply/Demand Results. When the order is shipped, then ATP is NOT notified of this change automatically. ALSO, the On Hand quantity is not updated until data collections is run, so the ATP results will still be correct.
   Running Data Collections with Net Change or Complete will update the data for both sales orders and on hand quantities and update the data.
   Problems reported are that it appears that the on hand IS updated, but the Closed Sales order remains in the system. 
5. New issue discovered where a few Closed Sales Order lines are seen in the output for ATP and in the Collections Workbench. This issue happened shortly after upgrade to 12.1 from 11.5.10 for a limited number of sales orders. Discovery and resolution are discussed in Note 1338781.1

Solution:

1. Best Solution:
   For Distributed installations where APS Suite is on a separate server: Run Data Collections from Advanced Supply Chain Planner with a Targeted Refresh and Sales Orders set to explicitly to Yes (this will default to No for all collections types for performance reasons) - Set parameter Purge Previously Collected Data = No so that Collection Method parameter gives option for Targeted Refresh
   OR
   IF not using ASCP and a Centralized installation where APS suite is not deployed on a separate server, THEN run ATP Data Collections with Sales order parameter set to Complete Refresh.
   Note: This step will mean that ATP will not be available during part of the Data Collections processing cycle
   
2. For Customers on EBS 12.1.3 - See Upgrade/Install ALERT for EBS 12.1.3 For Customers Using ATP Data Collections and ATP Based On Collected Data Note 1511936.1
   
3. IMPORTANT: Is the Database version been upgraded to 10g?
   IF Yes, THEN it is very important that the latest Data Collections Rollup patch be applied!!
   We have observed that 10g treats data collections objects like Snapshots much differently than 9i RDBMS and this has forced code changes.
   Minimum rollup patch level for Collections is Rollup #18. Check Note 252108.1 - and see section Collections Cumulative Patch List - and see the SQL in the note to determine which is the latest patch applied. We have fixed many issues for Sales Orders and other data by having the customer apply the latest Data Collection rollup patch.
   
   
4. Possible Solution: As above, run Data Collections, but use Net Change Refresh for the parameters instead of Complete Refresh.
   Note: ATP will still be available when you use this option, so it us usually the preferred options for running data collections.
   This solution has been known not to work, even though it should work... and if this occurs, then a Complete Refresh as described in #1 above is the only solution:
   Reasons why a Net Change of Data Collections may not work:
   1. The profile MSC: Sales Order Offset Days is not set properly. This profile is used in Net Change Collections UNLESS you are on 11.5.10 and have applied the last Collections Rollup patch from Section IV of Note 223026.1 - List of High Priority Patches for the APS Suite.
      To setup the profile properly, it should be setup in a value that exceeds the earliest Schedule Ship Date for an unshipped Sales Order that exists on the system.
      Example: If today is 01-Jun-2007 and the order that was shipped had a Scheduled Ship Date of 01-Mar-2007 and the profile was not set to greater than 92, this closed order would be missed during Net Change Collections and not update correctly.
   2. The Data Collections code level is old and changes updates to the code are not on the system. For instance, if Note 278055.1 has not been followed after the installation of 11.5.10.2 during an install or upgrade and the latest Rollup patches for Data Collections is not applied OR IF customer has been live for a long time without problems and code level is old. In Data Collections 11.5.10.2 base release, code level of key file $MSC_TOP/sql/MSCVIEWS.sql is 115.228.11510.13 and the latest released version as of 01-Jun-2007 is 115.228.11510.62 - almost 60 revisions higher. Applying the latest Collections Rollup patch from Note 223026.1 is imperative when code levels are older.
   3. The profile MRP: Consume MPS = Yes and the Planning Manager is not running. NAV: Material Planner / Setup / Planning Manager to see if the planning manager is running normally by checking the messages in the form. If the interval = 00:00:30 , the default setting, then message should be posted in the form with today's date and time stamp of every 1 - 3 minutes under normal operations.
   4. The Interface Trip Stop process errored and did not complete the transaction processing, so that updating of some tables was not completed and demand or supply still exists in related tables. This is a data corruption issue that would require a data fix. Only possible interim is to attempt Complete Refresh of Data Collections and then proceed to work with the Inventory support team to get a data fix, possible patch for the failure of the program.
   5. OM code is older and did not properly call the MRP API to relieve the sales order demand on the planning side of the application. $ONT_TOP/patch/115/sql/OEXUSCHB.pls is one of the key files used in OM to call ATP and Planning routines. In base release of 11.5.10.2, the version is 115.153.11510.13 and OM regularly releases Cumulative (rollup) patches for their code. As of Dec-2010 the latest version is 115.153.11510.63 released in the Dec 2010 cumulative patch 10273104

For ATP/CTP Based On Planning Output, How can I get Date for Buy Items in the same way I get Dates for Make Items?

This scenario is for the following conditions:

• Profile INV: Capable to Promise = ATP/CTP Based on Planning Output
• All items using ATP Rule with User-Defined Time Fence Days > than lead times to Make or Buy the items

Key Setup Points:

1. ATP rule is setup Infinite Supply using User Defined Time Fence of X days where X is greater than leadtime of the purchased item.
2. Check ATP set to Material Only and ATP Components must be set to Material Onlyin the Order Management tab of the Organization Items form.
   1. Setting ATP Components - Material Only is not an intuitive setup for Buy items since they generally do not have a BOM. This is the setup that tells ATP code to search the supply chain for further availability for purchased items.
   2. In this case, it will look at the supplier availability.
   3. Note: Item should NOT have a BOM defined.
3. Sourcing Rule for this item in the Assignment Set assigned to the ASCP Plan checked for ATP, and also in profile MSC: ATP Assignment Set
4. Item exists in the ASCP ATP Plan results
5. If ASL and Supply Capacity is setup for an item, then it will be considered during ATP calculation.

Details of this Example:

1. Purchased item has an ATP Rule with User Defined Time Fence = 120 days
2. Item DG-ATP-Buy has only 4 days processing leadtime. In general, Total Leadtime must be less than 120 days (Pre-processing + Processing + Post-processing Leadtime)

Item ATP SetupSee that Check ATP and ATP Components are both set to Material Only in the Organization Items screen

Note: This setup may result in error:

APP-INV-05421 - ATP Components must be set to "No" when Pick Components is set to "No", Assemble To Order = "No", and WIP Supply Type is not Phantom"

This error will only occur when INV: Capable to Promise  = ATP Based on Collected Data,
Check your profile, as this setup is valid only for ATP/CTP based on Planning Data

ATP Inquiry Details - when the setups are complete and the item exists in the ATP plan



ATP Results when the setups are complete and the item exists in the ATP plan


Example of the data returned

IF Item has setup with only Check ATP – Material Only (ATP Components –NULL)
OR
Item does NOT exist in the ATP plan (and therefore ATP calculation reverts to ATP Based On Collected Data)
OR
ATP based on Collected Data

Note: The Infinite Time Fence date is always calculated using Org Mfg calendars since this is an Item-org setting.
SO IF you have an ASL or other leadtime value that needs to be considered for ATP date, then using ATP Components = Material Only is required.

ATP Results show the infinite supply time fence from the ATP Rule - in this example - 120 days (uses Org Mfg calendar)



Example of results that are considered normal for a Finished Good item where Check ATP and ATP Components are setup for Material Only
These are the results that customer wants to see for the Buy type items as well as the Make items and why this question was answered.
In this example, the ATP Date of 15-APR-2008 is based on Leadtime to Make 150 of this item when Variable Leadtime is conputed along with Manufacturing Leadtime and Lot Size in the Organization Item setups


ATP Results for Transfer From Sourcing Showing the Full Supply Chain

Note 1512629.1 GOP - How To Setup and See Full Pegging And Understand Dates for ATP When Using Transfering From Another Org

For ATP/CTP based on Planning Output - How are Dates Influenced by Lead Times, ATP Rules and Planning Time Fence?

We have published new note (29-NOV-2012) which helps explain how ATP calculates dates.
Why is ATP Pegging Showing Infinite Supply Date Before The Ship Date in ATP Pegging Details and How Does Planning Time Fence Influence ATP Dates? (Doc ID 1508335.1)

How Can I Setup Calendars To Ship to Customers Only On Certain Days Of The Week?

How Can I Setup Carrier Transit Calendar and Customer Receiving Calendar?

Update 09-Dec-2009 -Basic Transit Times Setup from Warehouse to Customer - Note 262124.1 has been revised and enhanced.

Update 07-Sep-2009 - Internal Sales Orders - we have enhanced and updated Note 251449.1 which deals with Internal Sales Orders and setup and processing of Internal Requistions, Internal Sales Orders and ATP. This note should be reviewed for any functionality relating to Internal Sales Orders.
It will also point back to these documents in cases where Sales Order will involve the complexity of using specific Calendar for different parts of the ATP process. Key Setups for Internal Sales orders and ATP are convered in this note.

Updated 15-Dec-2008 - New sections (10 and 11) added to this answer and the PDF file to show setups for Carrier Transit Calendar and Customer Receiving Calendar.
Also shows the difference between Order Date Type Schedule and Arrival (the type of Request Date used when scheduling)

The Users Guide discusses this and the application allows for setup of a calendar with assignment type of Shipping/Customer, which seems logical, but is not the correct setup for this requirement. Enhancement 5169488 has been logged to enable this functionality, but there are methods currently available in the application to setup this requirement.

Key Concepts:

1. Available in 11.5.10 and above
2. Shipping Calendars are for Shipping FROM, not Shipping to [Customers], so the setup is based on the Organization where the order will be Shipping FROM.
3. It is possible to setup for different shipping days in a single organization using different carriers, ship methods and calendars as noted below in Summary of Setup Steps - #3
4. This functionality is available for ATP using EITHER
   INV: Capable to Promise = ATP Based on Collected Data OR ATP/CTP Based on planning data

Click here to download the PDF File for Detailed Screen Shots - 436771.1_Shipping_to_Specific_Customers_on_Specific_Days.pdf

Summary of the Setup Steps from in the PDF file
Check Note 417249.1 - Shipping Calendar Is Being Ignored At Ship Confirm - Make sure that you are not exposed to this 11.5.10 bug for Shipping

1. Profile settings at the Site Level:
   a. MSC: Use Shipping/Receiving Calendars. = Yes
   b. INV: Capable to Promise = ATP Based on Collected Data ORATP/CTP Based on planning data.
   c. Note: If using Distributed/Decentralized installation where Transactional Instance (ERP Source) and Planning Instance (APS Destination) are separate databases, then you need to set the profiles on BOTH instances.
2. Setup the Calendar for required days
   Example: Tuesday and Friday as active days for this example.
   Setup different calendars to model the different requirements for shipping.
3. Setup of Freight Carriers and Ship Methods and assign to organizations
   Example: Yellow Freight used in this example
   Note: If you have same carrier shipping to different customers on different days, then you need to setup different carrier codes:
   Examples:
   Yellow Freight-Mon-Wed (ships from org on Mon and Wed)
   Yellow Freight-Tue-Thu
   Yellow Freight-Wed-Fri
4. Create the Calendar Assignment for the Organization / Shipping / Carrier
5. Assign Ship Method to Customer
6. Setup Regions
7. Assign Transit Times to Region
   a. This can also be setup for specific Customer instead of a Region
8. Run Data Collections and ASCP ATP Plan.
9. Enter Sales orders and observe results
10. Using Carrier Transit Calendar / Customer Receiving Calendar
11. Order Date Type - Arrival
    

Where can I get more information about the error messages that are displayed in the ATP Availability window?

The Users Guide - Oracle Global Order Promising Implementation and User’s Guide - Release 11i  - Part No. B10512-03 - June 2004
has a Chapter 8 - Troubleshooting- which addresses many of the ATP errors including

• Try ATP again later.
• Summary Concurrent program is running. Try ATP later
• Invalid ATP rule or No ATP rule defined
• Unable to meet the request quantity
• Unable to find a calendar date
• No APS instance defined. Set APS instance and run collections
• Wrong Check ATP setup for product family item and member item
• One of the group elements or mandatory components failed
• INV: Capable to Promise must be same at source and destination
• ATP processing error
• Cannot meet request date or latest acceptable date
• ATP not applicable
• No Sources
• This item is not collected. Please run Data Collection
• No Assignment Set
• Plan not found. ATP is calculated using collected data

Additionally more troubleshooting tips for common problems and some SQL queries to investigate certain errors is included in this Chapter of the Users Guide
To get the latest version of the users guide, see Note 118086.1 APS Documentation for 11.5 and 12.0

Why do we get the message 'Try ATP again later'?

This is standard requirement when running Targeted or Complete Refresh of Data Collections.
During time when the sales order and customer tables are being refresh during the ODS load, then we cannot provide ATP results.
The only way to avoid this entirely is to run Data Collections with Net Change Refresh.

Customers Running ATP Data Collections from Order Management Application:
--------------------------------------------------------------------------------------------

1. We shutdown ATP for the entire Data Collections process when parameter Sales Orders - Complete Refresh
2. When running with parameters
   Collection Type - Complete Refresh
   Sales Orders - Net Change ,
   Then we have limited downtime during the ODS Load phase when we are doing TRANFORM KEYS* processing
3. When running both parameters with Net Change, then no ATP downtime would be experienced.

Customers Running ASCP Data Collections:
---------------------------------------------------

1. Net Change Collections - ATP is never down during Net Change data collections
2. Complete shutdown of ATPwhen:
   1. Running Targeted Refresh with Sales Order Parameter = Yes
   2. Running Complete Refresh Data Collections with Sales Order Parameter Set = Yes and using ODS ATP (ATP Based on Collected Data)
   3. Running Continous Collections where threshold is reached for Sales Orders which triggers the Targeted refresh of sales orders and using ATP Based On Collected Data
      
      Note: Running Complete Refresh of Data Collections with default parameters will have Sales Order Parameter = No. this means that the Sales Orders are collected in Net Change mode
      This is by design to limit downtime and for performance reasons.
      Unless you experience problems with sales order information in ASCP or ATP, then we do not recommend running a complete refresh (or Targeted Refresh) of sales orders.
3. To minimize downtime we have code designed so that during ODS Load - TRANSFORM KEYS* process, we will shutdown ATP IF:
   1. Complete Refresh with default parameters (which means sales orders is No)
   2. Complete Refresh setting with Sales Orders Parameter = Yes AND when using PDS ATP (INV: Capable To Promise = ATP/CTP based on Planning output)
4. Targeted Refresh- Yes for the following parameters:
   1. Customers/Supplier/Orgs
   2. Items
   3. Sourcing Rules
   4. Sales Orders
5. Continuous Collectionswhere threshold is reached for any of the following entities, which will trigger Targeted Refresh of those entities
   1. Customers/Supplier/Orgs
   2. Items
   3. Sourcing Rules
   4. Sales Orders

* TRANSFORM KEYS is the process that insures we maintain unique ID's for key entities when collecting from Multiple instances and is part of the base design of Data Collections and ASCP.
Notes:

1. We set the value of MSC_APPS_INSTANCES.SO_TBL_STATUS = 2 during this downtime and change it back to 1 when the processing is finished.
2. Ref Bug 2605884 and Bug 5727196 / 5883296
3. For 11.5.10, these enhancements to minimize downtime were introduced in Data Collections Rollup #18 -patch 5743081 and higher
4. For R12.0, this functionality was included starting in 12.0.3 and above
5. For R12.1.x, this functionality is forward ported via Bug 9385005
   For customers on VCP 12.1.3.3, 12.1.3.4 and 12.1.3.5, there is a one-off patch available - Patch 9385005 - Password from Support is required
   Fix is included in VCP 12.1.3.6 and above
   More information on these patches available in Note 746824.1
   ALSO SEE - Upgrade/Install ALERT for EBS 12.1.3 For Customers Using ATP Data Collections and ATP Based On Collected Data Note 1511936.1

How can I view ATP Supply/Demand results easily OR How to Use /File/Export in Oracle Applications?

This slide shows how to use / File / Export to create a TSV file of the screen output and then shows some setup to make viewing and using the file as a spreadsheet easy.
This is very effective, since once the spreadsheet is setup, you can also add the ATP Horizontal Plan to the same spreadsheet and then also add screen shots showing the ATP results and have a complete picture of the ATP issue in one file!!




IF the file automatically opens in IE, then you can manipulate there before saving.



If the TSV file opens in IE as a spreadsheet
Then you can save the File as an XLS file

IF the file is saved to your local disc before you open it, then you can find the file, and then use rt-click / Open With and choose Excel ( or choose program to associate with Excel or see below to setup the file types for TSV files

How Can I Setup /File/Export OR /File/Export Fails To Open as an XLS Type File?

Set up TSV file types to default to Excel. In Windows Explorer (NOT your Browser), use / Tools  / Folder Options as shown below to setup the default file type to recognise a RSV file as a Excel type document. When this is setup properly then the next time you use /File/Export it should open in the browser and look like a spreadsheet and you should be able to Save As a spreadsheet.

IF you are having problems getting /File/Export to work properly, check the following Notes:

Note.427555.1 - File > Export via IE- Browser Closes After Tool Bar Completion
Note.414616.1 - Forms Export Process Crashes Internet Explorer 7 and 6
Note 357667.1 - Export Of Records Errors And Displays No Result

By

Gautham Gangadharan

Reopen Inventory Accounting Period R12 /11i

Re open Inventory Accounting Period

Applicable to 11i and R12


If an Inventory Accounting Period has been closed prematurely by accident the following scripts can be used to re-open the accounting period if the corresponding GL period is open.  Re-Opening a closed period will allow transactions to be process for that period.
The re-opening of a closed period should not be used to back date transactions, the system allow back dated transactions but this may cause discrepancies between inventory and GL.


THE RE-OPENING OF A CLOSED INVENTORY PERIOD COULD POTENTIALLY CAUSE DATA CORRUPTION AND ANY DATA CORRUPTION CAUSED BY RE-OPENING A CLOSED INVENTORY PERIOD WILL BE THE RESPONSIBILITY OF THE CUSTOMER AND NO DATA FIX WILL BE PROVIDED FOR ANY DATA CORRUPTION THAT HAS BEEN CAUSED BY RE-OPENING A CLOSED PERIOD.
TEST THOROUGHLY ALL SCRIPTS ON A NON-PRODUCTION INSTANCE, FIRST BACKING UP ALL TABLE DATA PRIOR TO IMPLEMENTING IN PRODUCTION.

Here you Go :
***************

SELECT acct_period_id period, open_flag, period_name name, 
      period_start_date, schedule_close_date, period_close_date 
      FROM  org_acct_periods 
      WHERE organization_id = &org_id 
      order by 1,2;



UPDATE org_acct_periods
SET open_flag = 'Y',
period_close_date = NULL,
summarized_flag = 'N'
WHERE organization_id = &&org_id
AND acct_period_id >= &&acct_period_id;



DELETE mtl_period_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



DELETE mtl_period_cg_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



DELETE mtl_per_close_dtls
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



DELETE cst_period_close_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;



commit

OPM Formulae Query Report R12

 

Query to Find the Product and Incredient in the Fomulae ( OPM Formulator )

There is no Standard report in R12 , which can give you the information on Formulator for all the Products.

A simple query can be used to achieve the same ..

--------------------------------------------------------------------------------------------------------------------------

SELECT ffm.formula_no, ffm.formula_desc1 formula_name,

DECODE (fmd.line_type, 1, 'Product', 'Ingredient') TYPE,

msi.segment1 icode, ood.organization_code org, fmd.qty,

fmd.detail_uom,

DECODE (ffm.formula_status, 700, 'Active', 'New') status

FROM apps.fm_matl_dtl fmd,

apps.fm_form_mst ffm,

apps.mtl_system_items_b msi,

apps.org_organization_definitions ood

WHERE fmd.formula_id = ffm.formula_id

AND (fmd.inventory_item_id = msi.inventory_item_id(+)

AND fmd.organization_id = msi.organization_id(+))

AND fmd.organization_id = ood.organization_id

ORDER BY formula_no, DECODE (fmd.line_type, 1, 'Product', 'Ingredient') DESC;

---------------------------------------------------------------------------------------------------------------------------

Hope this Information is Usefull,

Onhand Level Material Status Control and Item Status

How to activate onhand level material status control

 Activate On-hand Level Material Status Tracking for one organization, Run concurrent request ‘Activate Onhand Level Material Status Tracking’


NOTE: Once an organization is activated as onhand status tracked it cannot be reverted back. If you have accidentally Run Activate On-Hand Material Status, Please refer to Note 1198443.1, The following Enhancement Request was logged suggesting that users should be allowed to deactivate the status: Bug 8626159 - ER to allow de-activate: Allow user to deactivate / de-activate 12.1 Feature: On-hand Status Tracking

What’s the relationship between Onhand Status and Sub/Loc/Lot Status Level

For onhand status tracked organization, status at subinventory, locator and lot level would ONLY be used to default onhand status.
Once you enabled your organization as onhand status tracking then you should need to look for material status at onhand level and not at the individual level. All the status validations will happen considering status at onhand level.

Which level status(Org/Sub/Loc/Lot Level) will be picked as default onhand status(In case doesn’t existing onhand)

The default status comes from the lowest available default at the organization, subinventory, locator, lot (as determined by a PL/SQL call to INV_MATERIAL_STATUS_GRP.get_default_status)
If Org is onhand tracking, and Org level default status is ‘Org_Status’,
Sub level status is ‘Sub_Status’,
Locator level status is ‘Locator_Status’,
Item level status is ‘Item_Status’,
Perform misc receipt for the item, you will see onhand status will pick item level status as default onhand status.
Similarly, for lot controlled item, onhand status will pick default lot status as onhand status.
If user does not set a default status at Item level or Lot level, onhand status will pick locator level status as default onhand status.

How To modify onhand material status

Navigate to Inventory --> On hand Quantity --->
Once clicking on the lower level onhand record then going to Tools > Status Update

How To Inquire On Onhand Material Status Updates And History

1.    For Lot controlled item, we can check onhand status update history via
Lot Number Form > View Genealogy > Material Transaction Tab > Grade/Status

Onhand Status for Intransit Org-Transfer

In intransit Org-Transfer, if onhand not exists in the destination, the Onhand status was not carried over as it does for direct org-transfer(For Direct org-transfer, Status_ID can be carried over from source org to receiving org). Currently the status of the onhand created was using defaulting logic.
Reference: NOTE: 1300203.1, Bug 11719987
For lot controlled item, onhand status is stamped on to lot transaction history. This can be used to default onhand status for intransit org transfer. 
(Version of QtyManager.java should be >= 120.26.12010000.25)
For non-lot controlled, there’s no efficient way getting the status once item completely issued out, hence it is also not in the scope of this project.

What’s the ‘Item Status‘

This item status is different with ‘Material Status’.
We use item statuses to provide default values for certain item attributes to control the functionality of an item. When you update the values for a status, all items to which it is assigned are also updated.
Attention: When your current organization is not the Item Master organization, the organization is temporarily changed to the Item Master organization until you exit this window. You can use the statuses created here in all defined organizations.
A status code controls certain item attributes designated as status attributes. The status attributes are:
BOM Allowed
Build in WIP
Customer Orders Enabled
Internal Orders Enabled
Invoice Enabled
Transactable
Purchasable
Stockable
Process Execution Enabled
Recipe Enabled
Associated with each status attribute is a Status Setting option. This option determines whether a status attribute value is set by the status code and is not updatable, defaulted and updatable, or not used when you define an item. You choose a Status Setting for a status attribute with the Item Attributes Controls window. You assign a status code to an item when you define the item.

Material Status Useful SQL Queries

Check whether the organization is onhand status tracking org
SELECT DEFAULT_STATUS_ID
FROM MTL_PARAMETERS WHERE ORGANIZATION_ID = 207;

Query Material Status Definition Header Part
SELECT B.*
FROM MTL_MATERIAL_STATUSES_B B,MTL_MATERIAL_STATUSES_TL T
WHERE B.STATUS_ID = T.STATUS_ID
AND T.STATUS_CODE = '&StatusCode';

For specific status, Check whether specific transaction type is allowed
SELECT IS_ALLOWED
FROM MTL_STATUS_TRANSACTION_CONTROL
WHERE STATUS_ID = &StatusID
AND TRANSACTION_TYPE_ID = &TxnTypeID;
Or use PL/SQL function to query IS_TRX_ALLOWED
SELECT INV_MATERIAL_STATUS_GRP.IS_TRX_ALLOWED_WRAP(&StatusID,&TxnTypeID)
FROM DUAL;

Use location information to check IS_STATUS_APPLICABLE
DECLARE
   result      VARCHAR2 (2);
   v_user_id   NUMBER := 1068;
   v_resp_id   NUMBER := 20634;
   v_appl_id   NUMBER := 401;
BEGIN
   FND_GLOBAL.apps_initialize (v_user_id, v_resp_id, v_appl_id);
   fnd_global.set_nls_context ('AMERICAN');

   SELECT   INV_MATERIAL_STATUS_GRP.IS_STATUS_APPLICABLE
('TRUE',1,18,'Y',NULL,207,1416050,'tp_sub',7919,'test01',NULL,'O')
               is_status_applicable
     INTO   result
     FROM   DUAL;

   DBMS_OUTPUT.put_line ('is_status_applicable=' || result);
END;
Material Status Validation Sample Query for Lot/Sub LOV
Lot Number LOV Sample
SELECT  MLN.LOT_NUMBER, MIN (MLN.EXPIRATION_DATE) EXPIRATION_DATE
       , PARENT_LOT_NUMBER, GRADE_CODE
FROM MTL_LOT_NUMBERS MLN
WHERE MLN.INVENTORY_ITEM_ID
= :MTL_LOT_REFERENCE.INVENTORY_ITEM_ID
AND MLN.ORGANIZATION_ID = :MTL_LOT_REFERENCE.ORG_ID
AND NVL (MLN.DISABLE_FLAG, '2') = '2'
AND INV_MATERIAL_STATUS_GRP.IS_STATUS_APPLICABLE (
            :PARAMETER.WMS_INSTALLED
          , :MTL_LOT_REFERENCE.TRX_STATUS_ENABLED
          , :MTL_LOT_REFERENCE.TRANSACTION_TYPE_ID
          , :MTL_LOT_REFERENCE.LOT_STATUS_ENABLED
          , NULL
          , :PARAMETER.ORG_ID
          , :MTL_LOT_REFERENCE.INVENTORY_ITEM_ID
          , NULL
          , NULL
          , MLN.LOT_NUMBER
          , NULL
          , 'O'
         ) = 'Y'
GROUP BY MLN.LOT_NUMBER, PARENT_LOT_NUMBER, GRADE_CODE
ORDER BY 2, MLN.LOT_NUMBER
Subinventory LOV Sample
SELECT SECONDARY_INVENTORY_NAME ,DESCRIPTION,
QUANTITY_TRACKED, ASSET_INVENTORY, LOCATOR_TYPE,
MATERIAL_ACCOUNT
FROM MTL_SUBINVENTORIES_VAL_V
WHERE ORGANIZATION_ID = :PARAMETER.ORG_ID
AND INV_MATERIAL_STATUS_GRP.IS_STATUS_APPLICABLE(
    :PARAMETER.WMS_INSTALLED
,    :MTL_TRX_HEADER.TRX_STATUS_ENABLED
,    DECODE(:MTL_TRX_HEADER.TRANSACTION_TYPE_ID,-99,
:MTL_TRX_LINE.TRANSACTION_TYPE_ID,:MTL_TRX_HEADER.TRANSACTION_TYPE_ID)
,    NULL
,    NULL
,    :PARAMETER.ORG_ID
,    :MTL_TRX_LINE.INVENTORY_ITEM_ID
,    SECONDARY_INVENTORY_NAME
,    NULL
,    NULL
,    NULL
,    NVL(:MTL_TRX_HEADER.MATERIAL_STATUS_OBJECT_TYPE,'Z') ) = 'Y'
ORDER BY SECONDARY_INVENTORY_NAME

Flex Fields Oracle

Overview of Flexfields:

A flexfield is a field made up of sub–fields, or segments. There are two types of flexfields: key flexfields and descriptive flexfields.

Key Flexfields:

A Key flexfield is a field made up of segments, where each segment has both a value and a meaning, which appears on your form as a normal text field with an appropriate prompt.

One example of a key flexfield is the Accounting Flexfield. This flexfield can always be customized to have as many segments as needed like Company, Department, Cost center, Account etc.

Descriptive flexfields:

Descriptive flexfields lets you add additional fields to the form in order to track additional information needed by the business that would not be captured by the standard form. Descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

A descriptive flexfield appears on a form as a single–character, unnamed field enclosed in Square Brackets [ ]. Each field or segment in a descriptive flexfield has a prompt and can have a set of valid values 

Descriptive Flexfield Concepts:

• Descriptive Flexfield Segments:
  

Descriptive flexfields have two different types of segments, global and context–sensitive.

A global segment is a segment that always appears in the descriptive flexfield pop–up window. 

A context–sensitive segment is a segment that may or may not appear depending upon what other information is present in your form.

A descriptive flexfield can get context information from either a field somewhere on the form, or from a special field (acontext field) inside the descriptive flexfield pop–up window. If the descriptive flexfield derives the context information from a form field (either displayed or hidden from users), that field is called a reference field for the descriptive flexfield.

• How and where are the segment information is stored:
  

A descriptive flexfield uses columns that are added on to a database table. A descriptive flexfield requires one column for each possible segment and one additional column in which to store structure information (that is, the context value). The descriptive flexfield columns are usually named ATTRIBUTEn where n is a number.

For example, if you have a descriptive flexfield on an Order Entry (Header) form, it populates the Structure column with the context field and the columns ATTRIBUTEn with the flexfield segments of the table OE_ORDER_HEADERS_ALL.

Example of enabling DFF:

Goal: Enable DFF in Sales Order form in Order Management

Navigation: Go to “Order management super User, Vision Operations” responsibility

Orders, Returns >> Sales Orders

Enable the DFF at right bottom screen.

To know the name of the DFF in a particular form do the following

Navigation: Help Menu >> Diagnostics >> Examine

Select Block Name as $DESCRIPTIVE_FLEXFIELD$

Field as ORDER.DF

Value will be populated and it will be the title of your DFF.

Title: Additional Header Information (Order Management) 

Order management in the brackets represent the Application in which this DFF is registered. 

To enable this 

Navigation: Go to Application Developer >> Flexfield >> Descriptive >> Segments 

Query the DFF 

Press F11 and enter

Title: Additional Header Information

Application: Order Management

Press CTL + F11 to query the record

Click on Segments

Enter the following information

Number: 5

Name: Erp Schools Demo

Window Prompt: Erp Schools Demo

Column: ATTRIBUTE11

Save the record (CTL + S)

Press OK

You can assign any value set if you want, its optional so I am just leaving it as blank

Now check the “Freeze Flexfield Definition” Checkbox

Press OK

Now Click Compile button

Press OK

You will see the confirmation as shown above.

Now to go to Order management super User, Vision Operations” and open the Sales order Form and click on DFF box.

You can hide the context field by un checking the Displayed Check Box in Context Field

To Make any changes you have to uncheck the “Freeze Flexfield Definition” Checkbox

Press OK

Now uncheck the Displayed Box in Context Field area as shown

Save your Record

Check the “Freeze Flexfield Definition” Checkbox

Press OK

Press Compile Button

Press OK

“Flexfield View Generator” concurrent program will be submitted. To see the status of the program go to View Menu on top >> Requests >> Find

Once you see the status as completed you can go to Order Management and check for your changes

Now you should be able to hide the Context.