This discussion is archived
9 Replies Latest reply: Jan 29, 2013 11:56 AM by user8480398 RSS

How to get the current On-Hand Quantity of an item?

Joe DeLeo Newbie
Currently Being Moderated
Hi,

I want to get the on-hand quantity of items from backend,

When i use the table mtl_onhand_quantities :
SELECT msi.segment1, msi.organization_id, SUM (moq.transaction_quantity)
FROM mtl_system_items_b msi, mtl_onhand_quantities moq
WHERE moq.organization_id(+) = msi.organization_id
AND moq.inventory_item_id(+) = msi.inventory_item_id
and moq.ORGANIZATION_ID = 1006
and msi.SEGMENT1 = '14003-0063'
GROUP BY msi.segment1, msi.organization_id

I get a quantity that is different than the one i get from front end. (On-Hand Quantity Screen)

The mtl_onhand_quantities shows quantities when received only , but it doesn't count the transactions done on the item.

How can i get the correct current on hand quantity of an item ?

Thanks,
Joe
  • 1. Re: How to get the current On-Hand Quantity of an item?
    AutoInstall Explorer
    Currently Being Moderated
    The SQL should work fine. Differences may be due to the query didn't group by subinventory and locator.

    Also, please could you elaborate more on "it doesn't count the transactions done on the item." Thanks!
  • 2. Re: How to get the current On-Hand Quantity of an item?
    Joe DeLeo Newbie
    Currently Being Moderated
    Hi Auto Install,

    I'm pretty sure the query is correct.

    What i mean by "it doesn't count the transactions done on the item" is that if there are transactions like :
    Miscellaneous Issue, Which issues out items out of the inventory , so the item's on hand quantity should be decreased..
    While it is not in mtl_onhand_quantities ...

    Let me know if you need further clarification.

    Regards,
    Joe
  • 3. Re: How to get the current On-Hand Quantity of an item?
    HrishikeshJ Journeyer
    Currently Being Moderated
    Hi,

    Please use sum(primary_quantity), as the transactions might have been performed in different transaction UOMs.

    e.g. you may perform two receipt transactions: 1 for 2 Ea and second for 2 Dozens.
    In this case sum(transaction_quantity) will give the value as 4.
    (Assuming primary uom is Ea, sum(primary_quantity) will give the expected value of 26.

    Thanks,
    Hrishi
  • 4. Re: How to get the current On-Hand Quantity of an item?
    Joe DeLeo Newbie
    Currently Being Moderated
    I think you guys in this way are getting only the on hand quantity that we inserted when we first created the item...

    I think we should also calculate whatever transactions happened on that item ..
    i found this little program, please let me know if you think it's the correct way :

    FUNCTION CF_LAST_BALFormula RETURN NUMBER IS
    v_onhand_qty NUMBER;
    v_last_qty NUMBER;
    v_target_qty NUMBER;

    BEGIN

    SELECT SUM(transaction_quantity)
    INTO v_onhand_qty
    FROM mtl_onhand_quantities_detail
    WHERE inventory_item_id = :inventory_item_id
    AND organization_id = :organization_id
    AND subinventory_code = :Subinventory;

    SELECT NVL(SUM(Transaction_quantity),0)
    INTO v_last_qty
    FROM mtl_material_transactions
    WHERE inventory_item_id = :inventory_item_id
    AND organization_id = :organization_id
    AND subinventory_code = :Subinventory
    AND trunc(creation_date)>= :p_last_date;

    v_target_qty:=(v_onhand_qty)-(v_last_qty);

    RETURN( v_target_qty);

    EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
    END;
  • 5. Re: How to get the current On-Hand Quantity of an item?
    HrishikeshJ Journeyer
    Currently Being Moderated
    That is not a correct way.

    if there is any difference between the sum(primary_Quantity) from MMT table and sum(primary_quantity) from MOQD table, it is data corruption and please log a SR to get it corrected.

    If you are still facing problem, then please provide output of following query and also tell us what is the onhand quantity seen form front end.

    select primary_quantity, transaction_quantity, transaction_uom, organization_id, subinventory_code, locator_id
    from mtl_onhand_quantities_detail
    where organization_id = &org_id
    and inventory_item_id = &item_id

    Also tell me the profile value for 'TP:INV Transaction processing mode'

    Thanks,
    Hrishikesh
  • 6. Re: How to get the current On-Hand Quantity of an item?
    Joe DeLeo Newbie
    Currently Being Moderated
    Dear HrishikeshJ,

    Thanks for your reply,

    I ran your query for an item , and the total onhand quantity was 47 (see query result below)
    While the item onhand quantity from front end was 62 (see screenshot on following link: http://i48.tinypic.com/ogvznk.jpg )

    PRIMARY_TRANSACTION_QUANTITY     TRANSACTION_QUANTITY     TRANSACTION_UOM_CODE     ORGANIZATION_ID     SUBINVENTORY_CODE     LOCATOR_ID

    15                                        15                              EA                    1006               Store     
    32                                        32                              EA                    1006               Store     

    The value of the profile option TP:INV Transaction processing mode is On-line processing.

    Kind Regards,
  • 7. Re: How to get the current On-Hand Quantity of an item?
    HrishikeshJ Journeyer
    Currently Being Moderated
    Thats a bit strange.

    Please provide output of
    select primary_quantity, transaction_quantity, transaction_uom, organization_id, subinventory_code, locator_id
    from mtl_material_transactions_temp
    where organization_id = &org_id
    and inventory_item_id = &item_id
    and transaction_status <> 2

    select primary_quantity, transaction_quantity, transaction_uom, organization_id, subinventory_code, locator_id
    from mtl_material_transactions
    where organization_id = &org_id
    and inventory_item_id = &item_id

    Thanks,
    Hrishi
  • 8. Re: How to get the current On-Hand Quantity of an item?
    981337 Newbie
    Currently Being Moderated
    Hi,

    Please check the below query

    SELECT SUM(primary_transaction_quantity),organization_id
    FROM mtl_onhand_quantities_detail
    WHERE inventory_item_id = 'Item ID' -- Item Id of item '14003-0063'
    GROUP BY organization_id;

    Thanks!!
  • 9. Re: How to get the current On-Hand Quantity of an item?
    user8480398 Newbie
    Currently Being Moderated
    Hi Friendz,
    What about the quantities available with MTL_MATERIAL_TRANSACTIONS_TEMP? this table holds the move order transaction quantities. I think you Should take consideration of the quantities available in this table as well. PLease let me know if iam wrong..


    Abs

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points