I have a customer that I want to provide them with all of our products in a spreadsheet.
I would like to extract from SAP the full list of products along with two price columns, in stock column (y/n) and barcode column.
The price columns should have the list price (price list #1) and RRP price (price list #3) in it - however, if there is a special price for that particular customer (lets say C104511) - then it should display it within the first price column.
This should take into account the period discount as well.
I'm having trouble combining the two lists into one query.
At the moment this is the SQLCMD that I setup in a bat file to export out this file on a daily basis:
SQLCMD -S <servername> -d <databasename> -U sa -P <password> -Q
"set nocount on;SELECT T0.ItemCode,T0.FrgnName,(SELECT T3.Price FROM ITM1 T3 where T3.ItemCode = T0.Itemcode and T3.[PriceList] = '1'),T1.Price, CASE WHEN T2.OnHand - T2.IsCommited > 10 THEN '10' ELSE '0' END, T0.CodeBars
FROM OITM T0
inner join ITM1 T1 on T0.ItemCode = T1.ItemCode and T1.[PriceList] = '2'
inner join OITW T2 on T0.ItemCode = T2.ItemCode and T2.WhsCode = '01. Main'" -s "," -o "<serverlocation>\stock.csv" -h-1 -s"~" -W -w 999
This exports out in the format:
Any help would be appreciated.