Skip to Content
avatar image
Former Member

Transaction Notification for blocking Purchase Order to add or update

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Nov 02, 2017 at 07:08 AM
    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
    Add comment
    10|10000 characters needed 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.