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