cancel
Showing results for 
Search instead for 
Did you mean: 

Transaction Notification for blocking Purchase Order to add or update

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member434614
Discoverer
0 Kudos
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
Former Member
0 Kudos

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.

Answers (0)