cancel
Showing results for 
Search instead for 
Did you mean: 

Outgoing Payment Transaction notification...

former_member196081
Active Contributor
0 Kudos
236

Hi all,

I designed the following Transaction notification for outgoing payment.It is working well If doctype is 'C' or 'S'. I want to do this for Doctype 'A'. But not successful....

Waiting for your replies..


IF @transaction_type = 'A' AND @object_type = '46' 
BEGIN
--If exists (SELECT T0.docentry  FROM OVPM T0 where t0.docentry = @list_of_cols_val_tab_del and T0.cashsum>=20000000)
--	begin
--		SET @error = 10
--		SET @error_message = N'You cannot Pay Cash More than Rs. 2000/-'
--	end
--
If not exists (SELECT T0.docentry  FROM OVPM T0  where t0.docentry = @list_of_cols_val_tab_del and 
(((t0.cardcode like '%%VDLI%%' or t0.cardcode like '%%CDLI%%' ) and t0.Series = '61' and (t0.doctype = 'S' or t0.doctype = 'C'   ))
or ((t0.cardcode like '%%VAHD%%' or t0.cardcode like '%%CAHD%%' ) and t0.Series = '64' and (t0.doctype = 'S' or t0.doctype = 'C'  ))
or ((t0.cardcode like '%%VMUM%%' or t0.cardcode like '%%CMUM%%' ) and t0.Series = '63' and (t0.doctype = 'S' or t0.doctype = 'C'  )) 
or ((t0.cardcode like '%%VLDH%%' or t0.cardcode like '%%CLDH%%' ) and t0.Series = '66' and (t0.doctype = 'S' or t0.doctype = 'C' ))
or ((t0.cardcode like '%%VKOL%%' or t0.cardcode like '%%CKOL%%' ) and t0.Series = '62' and (t0.doctype = 'S' or t0.doctype = 'C' ))
))
	begin
		SET @error = 11
		SET @error_message = N'BP Code,Location ,Series ,Tax code combination does not match'
	end


END
-----------------------------------------------------------------

Regards

Deepak tyagi

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi

Try This


IF @transaction_type = 'A' AND @object_type = '46' 
BEGIN
--If exists (SELECT T0.docentry  FROM OVPM T0 where t0.docentry = @list_of_cols_val_tab_del and T0.cashsum>=20000000)
--	begin
--		SET @error = 10
--		SET @error_message = N'You cannot Pay Cash More than Rs. 2000/-'
--	end
--
If not exists (SELECT T0.docentry  FROM OVPM T0  where t0.docentry = @list_of_cols_val_tab_del and 
(((t0.cardcode like '%%VDLI%%' or t0.cardcode like '%%CDLI%%' ) and t0.Series = '61' and (t0.doctype = 'S' or t0.doctype = 'C'   or t0.doctype = 'A'  ))
or ((t0.cardcode like '%%VAHD%%' or t0.cardcode like '%%CAHD%%' ) and t0.Series = '64' and (t0.doctype = 'S' or t0.doctype = 'C' or t0.doctype = 'A'   ))
or ((t0.cardcode like '%%VMUM%%' or t0.cardcode like '%%CMUM%%' ) and t0.Series = '63' and (t0.doctype = 'S' or t0.doctype = 'C' or t0.doctype = 'A'   )) 
or ((t0.cardcode like '%%VLDH%%' or t0.cardcode like '%%CLDH%%' ) and t0.Series = '66' and (t0.doctype = 'S' or t0.doctype = 'C' or t0.doctype = 'A'  ))
or ((t0.cardcode like '%%VKOL%%' or t0.cardcode like '%%CKOL%%' ) and t0.Series = '62' and (t0.doctype = 'S' or t0.doctype = 'C' or t0.doctype = 'A'  ))
))
	begin
		SET @error = 11
		SET @error_message = N'BP Code,Location ,Series ,Tax code combination does not match'
	end
 
 
END

Thanks

Kevin

former_member196081
Active Contributor
0 Kudos

Hi kevin,

Thanks for quick Reply...

and sorry for not explain the question properly..

Actully when I choose the doctype = 'A'.

then only following condition should be match..



(t1.acctcode like '%%ADLI%%' and t0.Series = '61' and  t0.doctype = 'A' and t1.LocCode = '1' )
or (t1.acctcode like '%%AAHD%%' and t0.Series = '64' and  t0.doctype = 'A' and t1.LocCode = '2' )
or ( t1.acctcode like '%%AMUM%%' and t0.Series = '63' and  t0.doctype = 'A' and t1.LocCode = '3' ) 
or ( t1.acctcode like '%%ALDH%%' and t0.Series = '66' and  t0.doctype = 'A' and t1.LocCode = '5')
or ( t1.acctcode like '%%AKOL%%' and t0.Series = '62' and  t0.doctype = 'A' and t1.LocCode = '4')

else the previous code should be run...

Thanks

Former Member
0 Kudos

Hi,

Try this...


IF @transaction_type = N'A' AND @object_type = N'46' 
BEGIN
SELECT T0.docentry FROM OVPM T0  
INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum and  
t0.docentry = @list_of_cols_val_tab_del and 
(t1.acctcode like '%%ADLI%%' and t0.Series = '61' and  t0.doctype = 'A' and t1.LocCode = '1' )
or (t1.acctcode like '%%AAHD%%' and t0.Series = '64' and  t0.doctype = 'A' and t1.LocCode = '2' )
or ( t1.acctcode like '%%AMUM%%' and t0.Series = '63' and  t0.doctype = 'A' and t1.LocCode = '3' ) 
or ( t1.acctcode like '%%ALDH%%' and t0.Series = '66' and  t0.doctype = 'A' and t1.LocCode = '5')
or ( t1.acctcode like '%%AKOL%%' and t0.Series = '62' and  t0.doctype = 'A' and t1.LocCode = '4')
)
	begin
		SET @error = 11
		SET @error_message = N'BP Code,Location ,Series ,Tax code combination does not match'
	end
 
 
END

Thanks

Kevin

former_member196081
Active Contributor
0 Kudos

Hi Kevin,

Actually this notification should be run for doctype 'A','S' ,'C'.

if I run your above notification then previous is giving wrong result..

I want to run all condition together....

Thanks

Deepak Tyagi

Former Member
0 Kudos

Hi


Declare @tmp  varchar(max)
IF @transaction_type = 'A' AND @object_type = '46' 
BEGIN

set @tmp= (SELECT T0.[DocType] FROM OVPM T0 where t0.docentry = @list_of_cols_val_tab_del )
 
                      if (@tmp in ('S','C'))
                                            Begin
If not exists (SELECT T0.docentry  FROM OVPM T0  where t0.docentry = @list_of_cols_val_tab_del and 
(((t0.cardcode like '%%VDLI%%' or t0.cardcode like '%%CDLI%%' ) and t0.Series = '61' and (t0.doctype = 'S' or t0.doctype = 'C'   ))
or ((t0.cardcode like '%%VAHD%%' or t0.cardcode like '%%CAHD%%' ) and t0.Series = '64' and (t0.doctype = 'S' or t0.doctype = 'C'  ))
or ((t0.cardcode like '%%VMUM%%' or t0.cardcode like '%%CMUM%%' ) and t0.Series = '63' and (t0.doctype = 'S' or t0.doctype = 'C'  )) 
or ((t0.cardcode like '%%VLDH%%' or t0.cardcode like '%%CLDH%%' ) and t0.Series = '66' and (t0.doctype = 'S' or t0.doctype = 'C' ))
or ((t0.cardcode like '%%VKOL%%' or t0.cardcode like '%%CKOL%%' ) and t0.Series = '62' and (t0.doctype = 'S' or t0.doctype = 'C' ))
))
	begin
		SET @error = 11
		SET @error_message = N'BP Code,Location ,Series ,Tax code combination does not match'
	end
                                            end
                      if(@tmp= 'A')
                                            Begin
 
If not exists  (SELECT T0.docentry FROM OVPM T0  
INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum and  
t0.docentry = @list_of_cols_val_tab_del and 
(t1.acctcode like '%%ADLI%%' and t0.Series = '61' and  t0.doctype = 'A' and t1.LocCode = '1' )
or (t1.acctcode like '%%AAHD%%' and t0.Series = '64' and  t0.doctype = 'A' and t1.LocCode = '2' )
or ( t1.acctcode like '%%AMUM%%' and t0.Series = '63' and  t0.doctype = 'A' and t1.LocCode = '3' ) 
or ( t1.acctcode like '%%ALDH%%' and t0.Series = '66' and  t0.doctype = 'A' and t1.LocCode = '5')
or ( t1.acctcode like '%%AKOL%%' and t0.Series = '62' and  t0.doctype = 'A' and t1.LocCode = '4')
)
	begin
		SET @error = 12
		SET @error_message = N'BP Code,Location ,Series ,Tax code combination does not match'
	end
                                            end
 


Hope we are near to resolved it

Thanks

Kevin

Edited by: Kevin Shah on May 10, 2011 12:59 PM

former_member196081
Active Contributor
0 Kudos

Hi Kevin,

if I choose doc type A. it is not stoping me if i am entering wrong data...

Sorry for late response.....

Actully I have done it but I want to do this through your way like variable declaration.....

so I am wating for your response...

Thanks

Deepak Tyagi

former_member206488
Active Contributor
0 Kudos

Hi Deepak,

Try this way:#

if @object_type='46' 
BEGIN
      IF @transaction_type='A' OR @transaction_type='U'
      BEGIN
            DECLARE @OPCHDocEntry int
            DECLARE @OPCHDocType nvarchar(1)
             SET @OPCHDocEntry = CAST(@list_of_cols_val_tab_del as int)
            SET @OPCHDocType = (SELECT DocType FROM OPCH WHERE DocEntry=@OPCHDocEntry)

        IF @OPCHDocType in ('S','C')
            BEGIN
                If not exists (SELECT T0.docentry  FROM OVPM T0  where t0.docentry = @list_of_cols_val_tab_del and 
						(((t0.cardcode like '%%VDLI%%' or t0.cardcode like '%%CDLI%%' ) and t0.Series = '61' and (t0.doctype = 'S' or t0.doctype = 'C'   ))
						or ((t0.cardcode like '%%VAHD%%' or t0.cardcode like '%%CAHD%%' ) and t0.Series = '64' and (t0.doctype = 'S' or t0.doctype = 'C'  ))
						or ((t0.cardcode like '%%VMUM%%' or t0.cardcode like '%%CMUM%%' ) and t0.Series = '63' and (t0.doctype = 'S' or t0.doctype = 'C'  )) 
						or ((t0.cardcode like '%%VLDH%%' or t0.cardcode like '%%CLDH%%' ) and t0.Series = '66' and (t0.doctype = 'S' or t0.doctype = 'C' ))
						or ((t0.cardcode like '%%VKOL%%' or t0.cardcode like '%%CKOL%%' ) and t0.Series = '62' and (t0.doctype = 'S' or t0.doctype = 'C' ))
						))
				begin
								SET @error = -1000
								SET @error_message = N'BP Code,Location ,Series ,Tax code combination does not match'
				end
            END
         IF @OPCHDocType ='A'
         BEGIN
           If not exists  (SELECT T0.docentry FROM OVPM T0 INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum and t0.docentry = @list_of_cols_val_tab_del and 
									(t1.acctcode like '%%ADLI%%' and t0.Series = '61' and  t0.doctype = 'A' and T1.LocCode = '1' )
									or (t1.acctcode like '%%AAHD%%' and t0.Series = '64' and  t0.doctype = 'A' and t1.LocCode = '2' )
									or ( t1.acctcode like '%%AMUM%%' and t0.Series = '63' and  t0.doctype = 'A' and t1.LocCode = '3' ) 
									or ( t1.acctcode like '%%ALDH%%' and t0.Series = '66' and  t0.doctype = 'A' and t1.LocCode = '5')
									or ( t1.acctcode like '%%AKOL%%' and t0.Series = '62' and  t0.doctype = 'A' and t1.LocCode = '4')
									)

            BEGIN
                        SET @error=-1001
                        SET @error_message = N'BP Code,Location ,Series ,Tax code combination does not match'
        END    
      END
END

END

Thanks,

Neetu

former_member196081
Active Contributor
0 Kudos

Hi Kevin,Neetu,

Thanks for your quick reply...

I did this through diffrent way...your solution are not working but i found a new way for Notifications

Regards

Deepak Tyagi