Base price list not found

[former_member236650]

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!

[former_member759609]

Hola, Algo tarde mi respuesta , pero dejo esto aquí por si alguien lo necesita.

Para actualizar los precios de los artículos , debes utilizar la siguiente estructura en tu template

Para los Encabezados en la OITM usa la siguiente estructura

Para el detalle en ITM1 , usa la siguiente Estructura , (del Template Original , solo es necesario agregar la columna BasePriceList) , esta columna falta en tu template y eso provoca tu error

🙂

[BattleshipCobra]

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/

[former_member372973]

Thanks for the help mike!

[Former Member]

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

[klein_thomas]

Looks very sophisticated - yet: what does it do?

Does it make relevant / permanent modifications to my system if I exceute it?
Does it modify / release restrictions that DTW depends upon when importing?

Undocumented code is of little use, unfortunately.

[former_member236650]

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!

[rdiger_frank]

Dear Jitin,

you must enter "2" in coloumn linenum.

Best regards

Rüdiger

[klein_thomas]

Is there a sound explanation behind this hint?

I could guess the "lineNum" should be one less than "PriceList", however this is just guessing.
As ERP is the foundation of our business, I would greatly appreciate a credible source of information, at best from SAP themselves.

Documentation of DTW and underlying procedures is incredibly poor when you dive into details 😕

[jitin_chawla]

Hi,

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

Regards,

Jitin

[former_member236650]

Hi Miss Jitin,

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