Skip to Content
0
Jul 29, 2016 at 02:57 AM

Query to extract list price - and special price in one list for a particular BP

594 Views

Hi,

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:

AG3100~HOOT BEANIE~15.900000~34.950000~0~9319057031006

Any help would be appreciated.

Thanks

Rick