9 Replies Latest reply: Jan 29, 2013 1:56 PM by user8480398 RSS

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

    Joe DeLeo
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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