Skip to Content

Stored Procedure Required for to Block Duplicate vendor creation based on the GST No & PAN Matched

Dear All

Stored Procedure Required for to Block Duplicate vendor creation based on the

T1.[GSTRegnNo] and T2.[TaxId0] matches ,

Kindly help to over come the issuse

Thanks for advance Help

SELECT T0.[CardCode], T1.[GSTRegnNo], T2.[TaxId0]
FROM OCRD T0 INNER JOIN
CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode]

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Jul 23, 2020 at 05:31 AM

    BEGIN
    declare @GstNo as varchar (100)
    declare @CardCode as varchar (100)
    declare @PanNo as varchar (100)


    IF (@object_type = '2') and and @transaction_type in ('A')
    BEGIN
    SELECT @GstNo = T1.[GSTRegnNo], @CardCode = T0.[CardCode], @PanNo=T2.[TaxId0] from OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode] where T0.[CardCode] = @list_of_cols_Val_tab_del

    BEGIN
    if (@GstNo is not null)
    BEGIN
    IF 1!= (select count T0.[CardCode] from OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode]

    where (@GstNo = T1.[GSTRegnNo])and ( @CardCode = T0.[CardCode]) and (@PanNo=T2.[TaxId0]))


    BEGIN
    if (@PanNo is not null)

    BEGIN

    IF 1!= (select count T0.[CardCode] from OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode]

    where (@GstNo = T1.[GSTRegnNo])and ( @CardCode = T0.[CardCode]) and (@PanNo=T2.[TaxId0]))

    BEGIN

    select @error = 10
    select @error_message = 'BP Exist kindly check in bp Master ..'
    End
    End
    End
    End
    End

    The above SP is Not working , any suggestions please

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 03, 2020 at 02:57 PM

    Dear Ramudu,

    Please check this SP:

    IF (@object_type = '2') and @transaction_type in ('A')
    BEGIN
    Declare @GstNo as varchar (100)
    Declare @PanNo as varchar (100)
    
    SET @GstNo = (select T1.[GSTRegnNo] from OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode] where T0.[CardCode] = @list_of_cols_Val_tab_del)
    SET @PanNo = (Select T2.[TaxId0] from OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode] where T0.[CardCode] = @list_of_cols_Val_tab_del)
    
    IF EXISTS (
    	SELECT T0.[CardCode] from OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode] 	where (@GstNo = T1.[GSTRegnNo])
    	UNION ALL 
    	SELECT T0.[CardCode] from OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN CRD7 T2 ON T0.[CardCode] = T2.[CardCode] 	where (@PanNo=T2.[TaxId0])
    	)
     
    	BEGIN
    	set @error =-100
    	set @error_message = 'Duplicate vendor creation based on the GST No & PAN Matched'
    	END	
    END
    
    
    Thank you,
    Aziz
    
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.