Skip to Content
2

Base price list not found

Dec 27, 2016 at 08:55 AM

705

avatar image
Former Member

Hello Experts!

I get an error when using DTW for updating the prices of my items in SAP. Please see screenshot below for your reference. Thanks!

sap1.jpg (123.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Jitin Chawla
Dec 27, 2016 at 09:06 AM
0

Hi,

Can you check if "LineNum"column in template is entered or not ?

Regards,

Jitin

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Miss Jitin,

I'm an currently using SAP templates. Please see attached photo.

sap2.jpg (58.9 kB)
0
Rüdiger Frank Dec 27, 2016 at 04:12 PM
0

Dear Jitin,

you must enter "2" in coloumn linenum.

Best regards

Rüdiger

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 28, 2016 at 01:30 AM
0

Hi All,

I checked the existing price lists in my database. The base price exists. I initially uploaded prices to the base price list before uploading them to VISMIN price list.

I also checked on SAP to confirm if the prices were successfully stored after uploading to BASE PRICE.

Thank you!


sap3.jpg (53.2 kB)
sap4.jpg (98.9 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Sep 01, 2017 at 01:58 PM
0
ALTER PROCEDURE UF_DTW_CreatePriceListTemplate 
	@pPriceListToLoad	AS Integer			= 0
	,@pEmptyPriceNeed	AS Integer			= 0      -- 0 not listed, 1 listed
	,@pFromItemCode		AS Nvarchar(50)		= NULL
	,@pTillItemCode		AS Nvarchar(50)		= NULL
	,@pFromPrice		AS Decimal(19,6)	= NULL
	,@pTillPrice		AS Decimal(19,6)	= NULL
	,@pCurrency			AS Nvarchar(3)		= NULL
	,@pFirmName			AS Nvarchar(100)    = NULL
AS
BEGIN
	SET NOCOUNT ON;


	IF(OBJECT_ID('UF_DTW_ITM1_LineNum') Is Not Null)
	BEGIN
		DROP Table #UF_DTW_ITM1_LineNum
	END


	IF(OBJECT_ID('UF_DTW_ITM1_Loader') Is Not Null)
	BEGIN
		DROP Table #UF_DTW_ITM1_Loader
	END


	IF(OBJECT_ID('UF_DTW_ITM1_Loader') Is Not Null)
	BEGIN
		DROP Table #UF_DTW_OITM_Loader
	END


	CREATE TABLE #UF_DTW_ITM1_LineNum
	(
		LineNum	   Integer 	IDENTITY(0,1) PRIMARY KEY
		,PriceList Integer  NOT NULL
	)


	INSERT INTO	#UF_DTW_ITM1_LineNum
	SELECT		T0.ListNum	AS PriceList
	FROM		OPLN		AS T0
	ORDER BY	T0.ListNum


	CREATE TABLE #UF_DTW_ITM1_Loader
	(
		ItemCode    Nvarchar(50)
		,LineNum	Integer 	
		,PriceList  Integer
		,Price		Decimal(19,6)
		,Currency	Nvarchar(3)
	)


	INSERT		INTO #UF_DTW_ITM1_Loader
	SELECT		T0.ItemCode		AS ItemCode
				,T1.LineNum		AS LineNum
				,T0.PriceList   AS PriceList
				,T0.Price		AS Price
				,T0.Currency	AS Currency
	FROM		ITM1 AS T0
	INNER JOIN	#UF_DTW_ITM1_LineNum AS T1 ON T1.PriceList = T0.PriceList
	INNER JOIN  OITM AS T2 ON T2.ItemCode = T0.ItemCode
	LEFT JOIN	OMRC AS T3 ON T3.FirmCode = T2.FirmCode		 
	WHERE		( @pPriceListToLoad = 0    OR	@pPriceListToLoad=T0.PriceList ) 
	AND			( ISNULL(@pFromItemCode,N'') = N''	OR  ISNULL(@pFromItemCode,0) <= ISNULL(T0.ItemCode,0)  )
	AND			( ISNULL(@pTillItemCode,N'') = N''	OR  ISNULL(@pTillItemCode,0) >= ISNULL(T0.ItemCode,0) )
	AND			( ISNULL(T0.Price,0) > 0 OR (ISNULL(T0.Price,0) = 0 AND @pEmptyPriceNeed = 1 ))
	AND			( ISNULL(@pFromPrice,0) = 0			OR	ISNULL(T0.Price,0) = 0			OR ISNULL(@pFromPrice,0)    <  ISNULL(T0.Price,0))
	AND			( ISNULL(@pTillPrice,0) = 0			OR  ISNULL(T0.Price,0) = 0			OR ISNULL(@pTillPrice,0)    >= ISNULL(T0.Price,0))
	AND			( ISNULL(@pCurrency,N'') = N''		OR  ISNULL(T0.Currency,N'') = N''	OR ISNULL(@pCurrency,N'')   =  ISNULL(T0.Currency,N''))
	AND			( ISNULL(@pFirmName,N'') = N''		OR  ISNULL(T3.FirmName,N'')	= N''	OR ISNULL(@pFirmName,N'')   =  ISNULL(T3.FirmName,N''))
	
	ORDER BY	T0.ItemCode,T1.LineNum 


	CREATE TABLE #UF_DTW_OITM_Loader
	(
		ItemCode    Nvarchar(50)
	)


	INSERT		INTO #UF_DTW_OITM_Loader
	SELECT		T0.ItemCode		AS ItemCode
	FROM		#UF_DTW_ITM1_Loader AS T0	
	GROUP BY	T0.ItemCode


	-- Loader head CSV file:
	-- ItemCode  (1st row)
	-- ItemCode  (2nd row)
	-- Loader row 3,4...
	SELECT		T0.ItemCode AS ItemCode
	FROM		#UF_DTW_OITM_Loader	AS T0	
	ORDER BY	T0.ItemCode


	-- Loader rows CSV file: 
	-- ParentKey, LineNum, PriceList, Price, Currency (1st row)
	-- ItemCode, LineNum, PriceList, Price, Currency (2nd row)
	SELECT		T0.ItemCode				AS ItemCode
				,T0.LineNum				AS LineNum
				,T0.PriceList			AS PriceList
				,T0.Price				AS Price
				,T0.Currency			AS Currency
	FROM		#UF_DTW_ITM1_Loader	AS T0	
	ORDER BY	T0.ItemCode,T0.LineNum
END
GO
Share
10 |10000 characters needed characters left characters exceeded
Mike Taylor Jun 06 at 12:18 AM
0

Hello,

I had the exact same error message. I was using a HANA version and saw the forum messages to contact SAP. This was not necessary.

I ran the SELECT * FROM OPLN to find the price list numbers (keep in mind that there could be gaps in the price list numbers).

Then I copied the OPLN table into Excel. Then I left only the "Price List No." column. Then I used the following:

OITM:

RecordKey ItemCode
RecordKey ItemCode
ART0104   ART0104

ITM1:

RecordKey LineNum PriceList Price Currency
RecordKey LineNum PriceList Price Currency
ART0104   0       1         100   $
ART0104   1       2         200   $
ART0104   2       3         300   $
ART0104   3       4         400   $
ART0104   4       5         500   $
ART0104   5       7         700   $
ART0104   6       8         800   $
ART0104   7       9         900   $
ART0104   8       10        1000  $

You can see here that there are gaps in the PriceList column (missing 6 for example).

So then all you need to do is match the price list from the OPLN with the same LineNum and it worked for me.

ITM1 (Specific):

RecordKey LineNum PriceList Price Currency
RecordKey LineNum PriceList Price Currency
ART0104   3       4         400   $
ART0104   8       10        1000  $

So in the case above I'm just updating price list 4 and 10. But I kept the same LineNum. I don't know why this is the case specifically but it worked fine I tested it.

I also used the RecordKey as the ItemCode which makes things a bit easier.

But this definitely was a bit frustrating compared to the SQL version.

Mike

PS, I have a YouTube channel about SAP Business One: http://youtube.battleshipcobra.com/

Share
10 |10000 characters needed characters left characters exceeded