I am a Fresher in writing queries.
I wrote following a query to block the A/P invoice, if price added in A/P invoice and the Price in Price list is different.
and this invoice should not be able to add by users other than UserSign = 5,6.
IF (@transaction_type='A' OR @transaction_type = 'U') AND @OBJECT_TYPE='18'
BEGIN
If EXISTS(SELECT T1.ItemCode,
T1.Price AS Inv_Price,
T2.U_ListPrice AS Listed_Price
FROM OPCH AS T0
INNER JOIN
PCH1 AS T1 ON
T0.DocEntry = T1.DocEntry
LEFT OUTER JOIN
dbo.[@PRICELISTS] AS T2 ON
T0.CardCode = T2.U_BPCode AND
T1.ItemCode = T2.U_ItemNo
WHERE
(T0.UserSign <> 6) AND
(T0.UserSign <> 5) AND
(T1.DocEntry = @list_of_cols_val_tab_del) AND
(T1.Price <> T2.U_ListPrice)
(T2.U_ListCurrency = T0.DocCur)
BEGIN
SET @error = 123 --1234567991234567918
SET @error_message = 'Deviation in price'
END
After executing, still everyone can add the A/P invoice with price difference.
Please look at the above code and help me to find out my fault to make it correct.
Is there any need to add some condition in where clause?
Regards,
Hitul Varia
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.