Oracle EBS Material Status FAQ

花蜻宽

Slideshare:Oracle EBS MaterialStatus FAQ(Pan Tian)

　　


　　Oracle EBS MATERIAL  STATUS  FAQ
　　

　　


Author:                         PTIAN (tianpan@gmail.com)
Creation Date:            16/04/2012
Last Updated:            
Version:                         1.0
Status:                           



　　Contents

　　
Material Status FAQ    1What’s onhand status    1How to activate onhand level material status control    1What’s the relationship between Onhand Status and Sub/Loc/Lot Status Level    2Which level status(Org/Sub/Loc/Lot Level) will be picked as default onhand status(In case doesn’t existing onhand)    2Which function is used to get default status id(Code Level)    3How To modify onhand material status    4How To Inquire On Onhand Material Status Updates And History    5Onhand Status for Intransit Org-Transfer    6Copy/Retain/Default Lot Status for Org-Transfer (Between Non-Onhand status tracking org)    7What’s value of Status_ID in MOQD If Org is Non-Onhand status tracking    7Which status_id is used to stamp into MTL_TRANSACTION_LOT_NUMBERS    7For serial controlled item, What’s value of onhand status_id    7In Material Status Form, The specific transaction type is not in the Allowed/Disallowed txn type list, How can I make my transaction type into Allowed/Disallowed txn type list?    8Cumulative Allowed/Disallowed Txn type    8What’s the ‘Item Status‘    9Material Status Useful SQL Queries    11Material Status Validation Sample Query for Lot/Sub LOV    12


　　Material Status FAQ
　　What’s onhand status
　　In R12 a new feature, called onhand status. It will take a location status to be material status. Onceyou 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.
　　The benefit of this is that if a portion of a pallet is crushed you can separate the pallet into twoparts and assign the bad crushed part a status of 'throw away' while assigning the good portion an acceptable status.
　　The onhand status only persists as long as you have inventory onhand. Once your inventory goes to 0for a certain lot or location the status no longer exists.
　　An example of onhand in the material workbench showing status


　　 How to activate onhand level material status control
　　Activate On-hand Level Material Status Tracking for one organization, Run concurrent request ‘ActivateOnhand Level Material Status Tracking’




　　NOTE: Once an organization is activated as onhand status tracked itcannot be reverted back. If you have accidentally Run Activate On-Hand Material Status, Please refer to Note1198443.1, The following Enhancement Request was logged suggesting that users should be allowed to deactivate the status:  Bug8626159 - 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 beused to default onhand status.
　　Once you enabled your organization as onhand status tracking then you should need to look for materialstatus 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 existingonhand)
　　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 defaultonhand 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 levelstatus as default onhand status.
　　There is priority order for onhand status picking, and like following…
　　Item Status/Lot status > Locator Status > Sub status > Org status


　　For lot controlled item, it can only set ‘Default Lot Status’, can NOT set ‘Default Material Status’.For plain item, it can only set ‘Default Material Status’, can NOT set ‘Default Lot Status’. So for a item, it’s impossible that item has item level status as well as has lot level status.
　　Which function is used to get default status id(Code Level)
　　Function: INV_MATERIAL_STATUS_GRP.get_default_status
　　This function returns the default material status for an onhand record in the table MOQD


　　Pseudo-Code:

• 
  Org is onhand tracking
  
  
  • 
    Check whether there is alreadyexisting a onhand record, If existing a onhand record, then return status_id of MOQD as default status id.
    
  • 
    If no onhand existing, Then getdefault status id from ‘Lot>Locator>Sub>Org’ level default status
    
  
  

　　How To modify onhand material status
　　Once clicking on the lower level onhand record then going to Tools > Status Update


　　How To Inquire On Onhand Material Status Updates And History

• 
  For Lot controlled item, we cancheck onhand status update history via
  

　　Lot Number Form > View Genealogy > Material Transaction Tab > Grade/Status

• 
  Material Status History Report
  

• 
  SQL query to check status history
  

　　SELECT   MSIB.ORGANIZATION_ID, MP.ORGANIZATION_CODE,MSIB.SEGMENT1,MMSH.PRIMARY_ONHAND,ML.MEANINGUPDATE_METHOD,MTR.REASON_NAME,MMSH.*
　　FROM   MTL_MATERIAL_STATUS_HISTORY MMSH,MTL_SYSTEM_ITEMS_B MSIB,MFG_LOOKUPS ML,MTL_TRANSACTION_REASONSMTR,MTL_PARAMETERS MP
　　WHERE       MSIB.INVENTORY_ITEM_ID = MMSH.INVENTORY_ITEM_ID
　　AND MSIB.ORGANIZATION_ID = MMSH.ORGANIZATION_ID
　　AND MP.ORGANIZATION_ID = MMSH.ORGANIZATION_ID
　　AND ML.LOOKUP_CODE = MMSH.UPDATE_METHOD
　　AND ML.LOOKUP_TYPE = 'MTL_STATUS_UPDATE_METHOD'
　　AND MTR.REASON_ID(+) = MMSH.UPDATE_REASON_ID
　　AND MSIB.SEGMENT1 LIKE '&EnterYouritem%'
　　ORDER BY   MSIB.SEGMENT1;
　　Onhand Status for Intransit Org-Transfer
　　In intransit Org-Transfer, if onhand not exists in the destination, the Onhand status was not carriedover 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 onhandstatus for intransit org transfer.
　　(Version of QtyManager.java shouldbe >= 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.
　　Copy/Retain/Default Lot Status for Org-Transfer (Between Non-Onhand status tracking org)

Item is lot status enabled, Source org & Destination org are non-onhand enabled.	Allow Different material Status = Y	Allow Different material Status = N	Allow Different material Status = With Exception
Lot Does NOT Exist in Destination Org	Intransit/Direct: Default Status from the organization item setup. (See Bug 11688000)	Intransit: Default Status from the organization item    setup. Direct: Copy lot status from      source org to destination org.	Intransit/Direct: Default Status from the organization item setup.
Lot Exist In Destination org	Retain lot status in destination organization	Retain lot status in destination organization. (If lot status is different between source org and destination org, Then user can Not perform org transfer for the lot number,    Cannot select the lot while shipping)	Retain lot status in destination organization. (If lot status is different between source org and destination org, Then user can Not perform org transfer for the lot number, Cannot select the lot while shipping)

　　Status_ID handle code logic see method createLotNumber in LotTrxManager.java
　　

　　What’s value of Status_ID in MOQD If Org is Non-Onhand status tracking
　　Status_ID should be NULL(In some customer’s cases, I saw it is -1) in MOQD, if organization is non-onhandstatus tracking.
　　Which status_id is used to stamp into MTL_TRANSACTION_LOT_NUMBERS

• 
  If org is tracking status atonhand level, Whenever perform a transaction for a lot controlled item, we need to stamp status_id into the lot history table(MTL_TRANSACTION_LOT_NUMBERS), And this status_id in MTL_TRANSACTION_LOT_NUMBERS is populated with the value of status_id from MTL_ONHAND_QUANTITIES_DETAIL.
  
• 
  If org is not tracking statusat onhand level, we will stamp status_id from mtl_lot_numbers into MTLN
  

　　See bug: 10322629
　　For serial controlled item, What’s value of onhand status_id
　　In case organization is onhand status enabled, Other than plain item or lot controlled item, For serialcontrolled item,  status_id in MOQD always stamp NULL value, We take status_id in MSN  to be material status.
　　In Material Status Form, The specific transaction type is not in the Allowed/Disallowed txn type list, Howcan I make my transaction type into Allowed/Disallowed txn type list?
　　You need enable ‘Status Control’ for the transaction type in Transaction Types Form.


　　Cumulative Allowed/Disallowed Txn type
　　A example, Misc receipt item into sub:pt_subb01, locator:55.55.55.., lot number:S00567
　　Subinv Status: s_sub01, It disallow transaction type: Sales Order issue
　　Locator Status:s_loc01, It disallow transaction type: Transfer to Consigned
　　Lot Status: s_lot01, It disallow transaction type: Transfer to Regular
　　So for the item, it will disallow all above three transaction types.


　　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 functionalityof 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 temporarilychanged 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 attributesare:
　　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 statusattribute 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 whenyou 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
　　


　　转载请注明出处:http://blog.iyunv.com/pan_tian/article/details/7489165