Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(Back to H-IN Inventory Module SQL Posted to Wiki Listing - H-IN Inventory Module SQL Posted to Wiki   )

TYPE: Monthly Special

AUTHOR NAME: Neetu Dhami

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP01 PL08.  This SQL script is used to get the list of Item prices in each price list as separate column, also list out the stock and item group details. It can be modified as per client requirements.

Here is the screen shot of script results:

 
Copy and Paste this SQL:

--H-IN SAP B1 SQL Item Prices in Each Price List

 --DESCRIPTION:  SQL displays Item price in each price list as separate column with item group and stock details.

--AUTHOR(s): -Version 1 Neetu Dhami 24 May 2011

DECLARE @priceLists AS TABLE (
 RowId INT,
 ListNum INT,
 ListName NVARCHAR(MAX))
DECLARE @index AS INT, @count AS INT, @columns AS NVARCHAR(MAX) 

INSERT INTO @priceLists
 SELECT ROW_NUMBER() OVER (ORDER BY ListNum) AS [Row], ListNum, ListName FROM OPLN
SELECT @index = 1, @count = COUNT(1), @columns = '' FROM @priceLists
WHILE (@index <= @count)
BEGIN
    BEGIN
   SET @columns = @columns + ', (SELECT ISNULL(Price,0) FROM ITM1 T1 WHERE T1.ItemCode = T0.ItemCode AND T1.PriceList = ' + (SELECT CAST(ListNum AS NVARCHAR) FROM @priceLists WHERE RowId =    @index) + ') AS [' + (SELECT ListName FROM @priceLists WHERE RowId = @index) + ']'
            SET @index = @index + 1
    END
END
EXEC ('SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam as [Item Group], T0.OnHand,T0.OnOrder,T0.IsCommited ' + @columns + ' FROM OITM T0 INNER JOIN OITB T1 on T0.ItmsGrpCod=T1.ItmsGrpCod')

  • No labels