Unit Price Validation
Can you please let me know what is wrong with below query?
This validation is not working on AR Invoice + Payment Transaction
|IF (@object_type = N'13' AND @transaction_type in (N'A', N'U') )|
IF EXISTS(select a.DocEntry from INV1 a --inner join ITM1 b on a.ItemCode = b.ItemCode
where a.PriceBefDi >= (SELECT distinct q.[Price] FROM OITM p INNER JOIN ITM1 q ON p.[ItemCode] = q.[ItemCode] WHERE q.[PriceList] = '2' and q.ItemCode = a.ItemCode )
and a.PriceBefDi <= (SELECT distinct q.[Price] FROM OITM p INNER JOIN ITM1 q ON p.[ItemCode] = q.[ItemCode] WHERE q.[PriceList] = '1' and q.ItemCode = a.ItemCode)
and a.DocEntry = @list_of_cols_val_tab_del)
Set @error =1
set @error_message = 'Please check the price! Item is going either under cost or over cost....'
PriceList 1 - WholeSale
PriceList 2 - Retail
If user put less than the wholesale price of that item, it should prompt him to do so.