Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / Item shows zero on-han...
Microsoft Dynamics GP forum
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:
Other
  • Syrus Profile Picture
    Syrus 4,156 on at
    RE: Item shows zero on-hand but in crystal reports it shows Negative

    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

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Moderator on at
    RE: Item shows zero on-hand but in crystal reports it shows Negative

    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
    RE: Item shows zero on-hand but in crystal reports it shows Negative

    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.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Moderator on at
    RE: Item shows zero on-hand but in crystal reports it shows Negative

    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
    RE: Item shows zero on-hand but in crystal reports it shows Negative

    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
    agaber Profile Picture
    agaber on at
    RE: Item shows zero on-hand but in crystal reports it shows Negative

    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

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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,645 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans