Skip to Content
0

Transaction Notification for blocking Purchase Order to add or update

Nov 01, 2017 at 07:29 PM

40

avatar image
Former Member

Hello,

I want to block a Purchase Order to be added if the details of GST like GST Regn No of the vendor is not entered in the BP Master Data. I have created a TN for the same-

-----------------------------

if @object_type = '22' and @transaction_type in (N'A')

begin

IF EXISTS (SELECT Distinct T0.[DocEntry] FROM OPOR T0 INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD1 T2 ON T1.[CARDCODE] = T2.[CARDCODE]

WHERE T0.DOCENTRY = @list_of_cols_val_tab_del AND T2.[GSTRegnNo] IS NULL OR T2.[GSTRegnNo]= '')

begin

set @error = 101

set @error_message = 'PLEASE UPDATE GST REG NO IN VENDOR MASTER DATA'

end

End

---------------------------------

This is working Ok but the problem is there are many vendors who does not comes under GST criteria and their GST Regn No can not be entered. So I have made an UDF in the BP Master data named Liable for Tax with valid values as 'Yes' and 'No'. But I dont know how to make queries which fulfills both the criteria.

In short if the value in the UDF is Yes then PO should not be added before the GST Regn No is filled. And if the value in UDF is No then PO should be added without adding any GST Regn NO.

Can anyone please help me on the above?

Thanks

A Kurwe

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
avatar image
Former Member Nov 02, 2017 at 07:08 AM
0
Try this

if @object_type = '22' and @transaction_type in (N'A')
begin
IF EXISTS (SELECT Distinct T0.[DocEntry] 
FROM OPOR T0 
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] 
INNER JOIN CRD1 T2 ON T1.[CARDCODE] = T2.[CARDCODE] 
WHERE T0.DOCENTRY = @list_of_cols_val_tab_del AND T2.[GSTRegnNo] IS NULL OR T2.[GSTRegnNo]= '' And T1.[Your UDF Name] = 'Yes') 
begin 
set @error = 101 
set @error_message = 'PLEASE UPDATE GST REG NO IN VENDOR MASTER DATA' 
end 
End
Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Ashish,

Thanks for your help. I made a slight change in the query provided by you and now its working fine. Thanks again for your help.

0