Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Inventory shortage qua...
Microsoft Dynamics GP (Archived)

Inventory shortage quantity query

(0) ShareShare
ReportReport
Posted on by Laura Bowie 214

Hi

I hope someone can help

I have a client using assembly entry that is heavily using BOM and want to automate the process of changing the status of the assembly to released by running a macro.

This seems quite straightforward however we need to pre-empt the pop-up that appears saying "a quantity shortage exists for one or more component. do you want to override the quantities?"

To get round this we believe we can enter vba to check the sql on the macro so that it will check the relevant table and if the quantity is less than zero run one macro that includes the pop-up and clicking yes or if it is greater than zero then run a different macro that doesn't have the pop-up.

My question is this does anyone know which field on which table it is referencing to make that pop up window come up?

*This post is locked for comments

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Inventory shortage quantity query

    Have you considered the allocated quantity ? Apparently, that was not deducted from your on-hand quantity. You should calculate the available quantity by deducting the allocated from the on-hand quantity.

  • Laura Bowie Profile Picture
    Laura Bowie 214 on at
    RE: Inventory shortage quantity query

    Thanks guys I have looked at the logic identified but when we run through the scripts to check then go into the assembly entry screen to test it I am still getting the quantity shortage pop up which means something is not quite right

    select

    *

      from

    IV00102 iv

    inner join BM00111 bm

    on

    iv.ITEMNMBR = bm.ITEMNMBR

    where

    iv.QTYONHND > 0 and bm.Design_Qty > 0 and iv.LOCNCODE = 'main' and CMPTITNM <> 'labour'

    I have tried the above and get a list of items but when trying to assemble it is still falling short on one of the sub components

    any further suggestions?

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Inventory shortage quantity query

    Adding up to what's mentioned above, I would personally advice to consider the IV10200 for calculating the actual on-hand quantity (quantity received-quantity sold), and then deducting the allocated quantity as retrieved from the quantity master table (IV00102). In some cases, IV00102 requires gets corrupted and requires running the inventory reconciliation in order for quantities to be recalculated according to the transaction tables.


    Here is a script that reconciles IV00102 (Item Quantity Master) and IV10200 (Purchase Receipt Table)

    Tables Included:

·IV00102 | Item Quantity Master

·IV10200 | Purchase Receipt Layers

 

SELECT  TRX_BALANCE.ITEMNMBR AS ItemNumber ,
        TRXLOCTN AS Location ,
        BALANCE AS TRX_BALNACE ,
        QTYONHND AS Master_Balnace ,
        ATYALLOC AS Master_AllocatedQuantity ,
        AvailableQuantity ,
        BALANCE - QTYONHND AS Variance
FROM    ( SELECT    ITEMNMBR ,
                    TRXLOCTN ,
                    SUM(QTYRECVD) - SUM(QTYSOLD) AS BALANCE
          FROM      dbo.IV10200
          GROUP BY  ITEMNMBR ,
                    TRXLOCTN
        ) AS TRX_BALANCE
        LEFT OUTER JOIN ( SELECT    ITEMNMBR ,
                                    LOCNCODE ,
                                    QTYONHND ,
                                    ATYALLOC ,
                                    QTYONHND - ATYALLOC AS AvailableQuantity
                          FROM      dbo.IV00102
                          WHERE     RCRDTYPE = 2
                        ) AS Master_Balance ON 
        TRX_BALANCE.ITEMNMBR = Master_Balance.ITEMNMBR
AND TRX_BALANCE.TRXLOCTN = Master_Balance.LOCNCODE

    Further details can be found on IV Reconciling Quantity on Hand

    Your feedback is highly appreciated,

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: Inventory shortage quantity query

    Hi,

    I believe it's checking the qty available in the IV00102 for the specified warehouse against the Design Quantity in the BM00111. Of course it would be the design quantity multiplied by however many you wanted to assemble. One other thing that I think matters is the Component Status. The component itself is either Active, Pending or Obsolete.

    Kind regards,

    Leslie

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard >

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans