Inventory Posting List Default Posting Dates Range

[kedalenechong]

Hi All
Is it possible to change Default Posting Dates Range in Inventory Posting List from Item Master Data?

We are able to define Posting Dates Range in Inventory Reports->Inventory Posting List to save most recent Dates Range in Selection Criteria.

Kedalene Chong

[jitin_chawla · Accepted solution]

Hi,

You cannot have a different default date in the Inventory Posting Date from the Item Master Data. You have to manually enter the required dates.

You can check the KBA  3372510 regarding the same.

Kr,

Jitin

[LoHa]

Hi,

because of the problems in the past, I made this query.
Perhabs it fits to your needs

/*Ersetzt die Bestandsbuchungsliste*/
/*Datum von/bis ist optional*/
/*Lager ist optional*/
/*Author LoH*/
/*Update: 20240111*/

IF OBJECT_ID ('tempdb..#MoveData','U') IS NOT NULL
DROP TABLE dbo.#MoveData

IF OBJECT_ID ('tempdb..#SumData','U') IS NOT NULL
DROP TABLE dbo.#SumData

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.T_RefDate */ '[%1]'

/**SELECT FROM [OITM] T2 **/
DECLARE @ItemCode AS NVARCHAR(max)
/* WHERE */
SET @ItemCode = /* T2.ItemCode */ '[%2]'

/**SELECT FROM [OIVL] T3 **/
DECLARE @WhsCode AS NVARCHAR(max)
/* WHERE */
SET @WhsCode = /* T3.LocCode */ '[%3]'

--/*DEBUG ONLY*/
--DECLARE @DocDateFrom AS Date
--DECLARE @DocDateTo AS Date
--DECLARE @ItemCode AS NVARCHAR(max)
--DECLARE @WhsCode AS NVARCHAR(max)
--SET @DocDateFrom = '20220330'
--SET @DocDateTo = '20240116'
--SET @ItemCode ='BZ1024'
--SET @WhsCode = ''

/*Für den Where-Filter unten wird ein Datum benötigt*/
IF @DocDateTo = ''
	BEGIN
		SELECT @DocDateTo = '20991231'
	END

/*Hole alle Bewegungsdaten für den Artikel*/
SELECT
	ROW_NUMBER() OVER (PARTITION BY '' ORDER BY [OIVL].[Itemcode],[OIVL].[DocDate]) AS [LineNum]
	,[OIVL].[ItemCode]
	,CASE
	WHEN [OIVL].[TransType] = 15		THEN 'Lieferung'
	WHEN [OIVL].[TransType] = 16		THEN 'Retoure VK' 
	WHEN [OIVL].[TransType] = 13		THEN 'Ausgangsrechnung' 
	WHEN [OIVL].[TransType] = 14		THEN 'Ausgangsgutschrift'
	WHEN [OIVL].[TransType] = 132		THEN 'Ausgangskorrekturrechnung'
	WHEN [OIVL].[TransType] = 20		THEN 'Wareneingang EK'
	WHEN [OIVL].[TransType] = 21		THEN 'Retoure VK'
	WHEN [OIVL].[TransType] = 18		THEN 'Eingangsrechnung' 
	WHEN [OIVL].[TransType] = 19		THEN 'Eingangsgutschrift'
	WHEN [OIVL].[TransType] = -2		THEN 'Eröffnungsbilanz'
	WHEN [OIVL].[TransType] = 58		THEN 'Lagerjournal'
	WHEN [OIVL].[TransType] = 59		THEN 'Man. Wareneingang' 
	WHEN [OIVL].[TransType] = 60		THEN 'Man. Warenausgang'
	WHEN [OIVL].[TransType] = 67		THEN 'Bestandsumlagerung' 
	WHEN [OIVL].[TransType] = 68		THEN 'Produktionsanweisungen' 
	WHEN [OIVL].[TransType] = -1		THEN 'Alle Transaktionen'
	WHEN [OIVL].[TransType] = 162		THEN 'Bestandsneubewertung' 
	WHEN [OIVL].[TransType] = 69		THEN 'Wareneinstandspreise'
	WHEN [OIVL].[TransType] = 310000001	THEN 'Anfangsbestand'
	WHEN [OIVL].[TransType] = 10000071	THEN 'Bestandsbuchung' 
	ELSE CAST([OIVL].TransType AS nvarchar(max)) + ' ist unbekannt' 
	END AS [DocType]
	,[OIVL].[DocDate]
	,[OIVL].[TransType]
	,[OIVL].[BASE_REF] AS DocEntry
	,[OIVL].[DocLineNum]
	,[OIVL].[LocCode] AS WhsCode
	,[OIVL].[InQty]
	,[OIVL].[OutQty] 

INTO [dbo]..[#MoveData]
FROM 
	[OIVL]

WHERE 
	([OIVL].DocDate <= @DocDateTo OR @DocDateTo = '')
	AND 
	([OIVL].ItemCode = @ItemCode /*OR @ItemCode = ''*/)
	AND 
	(OIVL.LocCode = @WhsCode OR @WhsCode = '')
	AND
	([OIVL].InQty > 0 OR [OIVL].OutQty > 0)

/*Wenn das Startdatum nicht leer ist muss eine Summe berechet werden damit die Endsumme passt*/
IF @DocDateFrom <> ''
	BEGIN
		SELECT
		DISTINCT
		 0 AS [LineNum] 
		,[MD].[ItemCode]
		,'Summe Datum kleiner ->' AS [DocType]
		,@DocDateFrom AS [DocDate]
		,0 AS [TransType]
		,'' AS [DocEntry]
		,0 AS [DocLineNum]
		,'' AS [WhsCode]
		,ISNULL(SUM([MD].[InQty]),0) AS [InQty]
		,ISNULL(SUM([MD].[OutQty]),0) AS [OutQty]
		,ISNULL(SUM(ISNULL([MD].[InQty],0) - ISNULL([MD].[OutQty],0)),0) AS [SumTotal]
		INTO [dbo]..[#SumData]
		FROM
			[dbo]..[#MoveData] MD
		WHERE
			[MD].[DocDate] < @DocDateFrom
		GROUP BY 
			[MD].[ItemCode]
	END

/*Wenn ein Startdatum gesetzt ist, hole die Summe als Anzeige und dann den Rest der Daten*/
IF @DocDateFrom <> ''

	BEGIN
		/*Summe*/
		SELECT 
			*
		FROM 
			[dbo]..[#SumData] SD
	
	UNION ALL
	
	SELECT 
		/*Restdaten*/
		[MoDa].*
		,SUM([MoDa].[InQty] - [MoDa].[OutQty]) OVER (ORDER BY [MoDa].[LineNum])  + [SD].[SumTotal] /*Die Summe wird hinzugerechnet damit es passt*/
	FROM 
		[dbo]..[#MoveData] [MoDa]
		INNER JOIN [dbo]..[#SumData] [SD] ON [SD].[ItemCode] = [MoDa].[ItemCode]
	WHERE 
		[MoDa].[DocDate] >= @DocDateFrom 
		AND 
		[MoDa].[DocDate] <= @DocDateTo
	ORDER BY 
		[LineNum] ASC
	END

ELSE
/*Ansonsten nimm alle Daten*/
	BEGIN
		SELECT 
			*
			,SUM([MoDa].[InQty] - [MoDa].[OutQty]) OVER (ORDER BY [MoDa].[LineNum]) AS [SumTotal]
		FROM 
			[dbo]..[#MoveData] [MoDa]
		ORDER BY 
			[MoDa].[LineNum] ASC
	END

regards Lothar

[kedalenechong]

Thanks for sharing your hardwork! This is good for tracking Quantity Balances!