Unit Price Validation

[Former Member]

Hi Experts,

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') )

    BEGIN

    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)

    BEGIN

    Set @error =1

    set @error_message = 'Please check the price! Item is going either under cost or over cost....'

    END

    END

PriceList 1 - WholeSale

PriceList 2 - Retail

If user put less than the wholesale price of that item, it should prompt him to do so.

Thanks

[Former Member]

Hi,

Try:

IF (@object_type = N'13' AND @transaction_type in (N'A', N'U') )

BEGIN

    IF EXISTS(select a.DocEntry from INV1 a

where (a.PriceBefDi >= (SELECT q.[Price] FROM ITM1 q WHERE q.[PriceList] = 2 and q.ItemCode = a.ItemCode )

    OR a.PriceBefDi <= (SELECT s.[Price] FROM ITM1 s WHERE s.[PriceList] = 1 and s.ItemCode = a.ItemCode))

    and a.DocEntry = @list_of_cols_val_tab_del)

    Begin

    Set @error =1

    set @error_message = 'Please check the price! Item is going either under cost or over cost....'

    End

END

Thanks,

Gordon

[Former Member]

Hi Team Tech,

Check below SP Transaction Notification.

IF @transaction_type IN ('A','U') AND @object_type = '13'and @error = 0

BEGIN

IF EXISTS (SELECT T0.DocEntry FROM OINV T0

INNER JOIN INV1 T1 on T1.DocEntry=T0.DocEntry

inner join OCRD on OCRD.CardCode=T0.CardCode

inner join ITM1 on ITM1.ItemCode=T1.ItemCode and ITM1.PriceList=OCRD.ListNum

inner join oitm on oitm.ItemCode=ITM1.ItemCode

WHERE OITM.InvntItem='Y' and  ITM1.Price !=T1.PriceBefDi

AND T0.DOCENTRY = @list_of_cols_val_tab_del)

BEGIN

SELECT @Error = 1, @error_message = 'You are not authorized to change unit Price'

END

END

Hope this helps

--

--

Regards::::

Atul Chakraborty