0

How to retrieve the Reserved quantity of a Sales Line?

Let me describe the two tables I think I need to work with. I say "think" because I'm new to NAV and Jet Reports.

In Sales Line, we have (among others) Document No_, No_, and Quantity. There is one record per Sales Line.

In Reservation Entry, we have (among others) Entry No_, Item No_, Quantity, Source Type, and Source Subtype. There are two records per reservation that are "linked" by Entry No_.

The fact that there are two entries per reservation is where I am getting hung up.

I came up with the beginnings of a Stored Procedure and it looks like this:

 

-- First, define some variables

DECLARE @EntryNo int
DECLARE @SourceId VARCHAR(MAX) = 'SO10517'
DECLARE @ItemNo VARCHAR(MAX) = '11501'

-- Find the @EntryNo

SELECT @EntryNo = [Entry No_]
FROM [Company$Reservation Entry]
WHERE [Source ID] = @SourceId
AND [Item No_] = @ItemNo

--This will result in one record (or none). This isn't enough information because we don't know what kind of reservation is defined in the companion record.

--PRINT @EntryNo

--Now find the companion record

SELECT [Entry No_]
, [Positive], [Item No_], [Location Code]
, [Quantity], [Description], [Creation Date]
, [Source Type], [Source Subtype]
FROM [Company$Reservation Entry]
WHERE [Entry No_] = @EntryNo
AND [Item No_] = @ItemNo
AND [Source Type] <> 37 --this is the record we found above so we don't need it again
GO

--This will give us one or more records. It could be a reservation to a PO or to inventory or an assembly order, etc.

With that out of the way, let me restate my need. For every Sales Line that is returned, I want to return Quantity from the companion record of the record pair found in Reservation Entry. I specifically want to 

 

Any help would be very much appreciated!

3 comments

Please sign in to leave a comment.