web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Item shows zero on-han...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Item shows zero on-hand but in crystal reports it shows Negative

(1) ShareShare
ReportReport
Posted on by Syrus 4,156

Hello GP Gurus,

A very strange issue with Inventory on-hand. item show ZERO on-hand but when I run a crystal report, it shows a Negative on-hand. I have looked all over the Inventory but there is nothing, though there has lot adjustments made between multiple warehouse locations. Also, When I run HITB smart list , it shows negative on-hand in History column.

I have reconciliation on the item but no change and nothing was picked up by this action.

Any help will be appreciated very highly. screen shot of smart Screenshot-2022_2D00_04_2D00_14-101253.pnglist is attached

Categories:
Microsoft Dynamics GP
I have the same question (0)
  • Suggested answer
    agaber Profile Picture
    agaber Microsoft Employee on at

    Hello Cesar-

    Thank you for posting your question on the Forums.  It looks like this is not a canned Smartlist and/or report?  Different or custom reports can pull data from different SQL tables, so it would seem that is likely why you are seeing discrepancies.  If you run the canned(*) Inventory Item Quantities Smartlist, does that show the correct on-hand quantity?  

    Have a great day!

    Adam G.

    Microsoft Dynamics GP

  • Syrus Profile Picture
    Syrus 4,156 on at

    Hi Adam,

    Thank you very much for looking into it. yes, it is run from canned(*) smart list and that is what it shows in Crystal report even though inventory item shows ZERO on hand.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,061 Moderator on at

    Syed,

    Have you tried to run an Item reconcile on this particular item ? The smatlist you're showing on screen seem to imply that it makes use of some HITB data to compare vs. the Inventory tables.. that's not a standard report as far as I know out of the GP default lists.

    GP often has discrepancies in the "on hand" quantities and it's easy to fix by running the reconcile. If you have a large inventory, don't run it on all item at once, but take chunks of it, as it can be pretty lengthy. It can be run against a single item too, but no window in GP should be open which call inventory items..

    PSTL does offer an optimized reconcile for inventory, if you have that module installed in GP.

  • Syrus Profile Picture
    Syrus 4,156 on at

    Hi Beat,

    Thanks for your reply. Yes, I have run the reconciliation many times and was expecting that it will fix the issue., but nothing changed.  We actually have a Crystal report of inventory On-hand and it shows this item as Negative on-hand while I have looked all over the tables , it is not making any sense and nor it is giving any clue. In GP it shows ZERO on hand if we run any inquiry under Inventory module.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,061 Moderator on at

    Syed,

    Without having access to the source queries for this smartlist and the Crystal report, it's hard to tell where the mis-calculation happens.

    the QTY OnHand is usually a calculated field already in GP when you look at the Item Inquiry Qty..

    If you can post somewhat the underlying query for each report, it might help shed some light on what could cause the differences.

  • Syrus Profile Picture
    Syrus 4,156 on at

    Hi Beat,

    So Smartlist pulls the data from a view ( I believe it is canned View) called Object:  View [dbo].[View_Check_HITB_vs_OnHand] and below is the script used 

    select
    A.ITEMNMBR as Item_Number,
    A.LOCNCODE as Site,
    I.ITEMDESC as Item_Desc,
    I.ITMCLSCD as Item_Class,
    MAX(I.CURRCOST) as CurrCost,
    MAX(I.STNDCOST) as StdCost,
    sum(TRXQTYINBASE) as HITB_Qty,
    --SUM(VARIANCEQTY) as VARIANCEQTY,
    --MAX(UNITCOST) as UNITCOSTMAX,
    --MIN(UNITCOST) as UNITCOSTMIN,
    MAX(IV2.QTYONHND) as Qty_On_Hand,
    MAX(IV2.QtyONHND) - SUM(TRXQTYINBASE) as 'OnHand-HITB',
    (MAX(IV2.QtyONHND) - SUM(TRXQTYINBASE))*MAX(I.CURRCOST) as '$'
    from SEE30303 A
    LEFT JOIN
    IV00101 I ON A.ITEMNMBR = I.ITEMNMBR
    LEFT JOIN
    IV00102 IV2 on A.ITEMNMBR = IV2.ITEMNMBR AND A.LOCNCODE= IV2.LOCNCODE
    where IV2.RCRDTYPE = '2'
    group by a.itemnmbr, a.locncode, i.itemdesc, i.ITMCLSCD

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

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 676

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 633 Super User 2026 Season 1

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 624 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans