Hi everyone, I had a need to reproduce a sum field from Navision in SQL so I thought others might be able to use our example if needed. Works great for our intended purpose. By the way, we are on Nav 2.60f and SQL2000. This stored procedure simulates the “Bin Location Contents”.“Qty. On Hand (Base)” field by rolling up the appropriate entries from the Warehouse Ledger Entries table. The stored procedure returns a curson with the bin location fields requested and a QtyOnHandBase field/column with the warehouse quantity we need. We’ll, of course, be able to use this via VB/ADO, web, etc… Bill SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘NAV_spGetWhseQtyOnHand’ AND type = ‘P’) DROP PROCEDURE NAV_spGetWhseQtyOnHand GO CREATE PROCEDURE NAV_spGetWhseQtyOnHand @sItemNo varchar(20), @sVariant varchar(10) = ‘’, @sLocation varchar(10) = ‘’ AS – ************************************************************************************** – Procedure Name: NAV_spGetWhseQtyOnHand – Author: Bill Reese – Creation Date: 12/15/2002 – Description: Return cursor with quantity on hand from Navision – Bin Location Contents table. Reproduces – Bin Location Contents.Qyt. On Hand (Base) sum field – – Input Params: @sItemNo - Navision Item No. – @sVariant - Navision Variant Code (NAGGDN) – @sLocation - Navision Location Code (WHSE-XXX) – – Output Params: None – – Return Values: Cursor with/without rows depending on – input param. – – Modifications: – 12/15/2002 WBR - Initial procedure – – ************************************************************************************** – Navision SUM Field BinLoc.Qty. On Hand (Base) reproduced – Sum(“Warehouse Ledger Entry”.“Qty. Handled” – WHERE (Location Code=FIELD(Location Code), – Zone Code=FIELD(Zone Code),Bin=FIELD(Bin), – Container=FIELD(Container),Item No.=FIELD(Item No.), – Variant Code=FIELD(Variant Code),UOM=FIELD(UOM))) SELECT BinLoc.[Location Code] as Location, BinLoc.[Item No.] As ItemNo, BinLoc.[Variant Code] As Variant, UOM, BinLoc.[Zone Code] As Zone, Bin, BinLoc.[Qty. Per Unit of Measure] As QtyPerUOM, ISNULL((SELECT SUM(WhseLedg.[Qty. Handled]) FROM [My FIN$Warehouse Ledger Entry] As WhseLedg WHERE (WhseLedg.[Location Code] = BinLoc.[Location Code]) AND (WhseLedg.[Zone Code] = BinLoc.[Zone Code]) AND (WhseLedg.Bin = BinLoc.Bin) AND (WhseLedg.Container = BinLoc.Container) AND (WhseLedg.[Item No.] = BinLoc.[Item No.]) AND (WhseLedg.[Variant Code] = BinLoc.[Variant Code]) AND (WhseLedg.UOM = BinLoc.UOM)),0.00) As QtyOnHandBase FROM [My FIN$Bin Location Contents] AS BinLoc WHERE (BinLoc.[Item No.] LIKE @sItemNo + ‘%’) AND (BinLoc.[Variant Code] LIKE @sVariant + ‘%’) AND (BinLoc.[Location Code] LIKE @sLocation + ‘%’) ORDER BY BinLoc.[Item No.], BinLoc.[Variant Code], BinLoc.UOM GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO