cancel
Showing results for 
Search instead for 
Did you mean: 

Need help in writing SP notification Query

Former Member
0 Kudos

Hi,

My purpose of writing a query is to restrict a user to add journal entry in case account code lies in Expense Drawer and Cost Center Code is not in user define range.

Please help me on this:-

IF (@object_type = '30' AND (@transaction_type = 'A' Or @transaction_type = 'U'))

BEGIN

Declare @AccountCodeMaster as Nvarchar(20)

Declare @AccountonDocument as Nvarchar(20)

Declare @ProfitCentreMaster as Nvarchar(20)

Declare @ProfitCentreOnDocument as Nvarchar(20)

Set @AccountCodeMaster = (select AcctCode from oact where GroupMask = 5)

Set @AccountonDocument = (Select Account from jdt1 where TransId = @list_of_cols_val_tab_del)

Set @ProfitCentreMaster = (select PrcCode from ocr1 where PrcCode >= 91100001 and PrcCode <= 91921001)

Set @ProfitCentreOnDocument = (select ProfitCode from jdt1 where TransId = @list_of_cols_val_tab_del)

if (@AccountonDocument) in (@AccountCodeMaster) and (@ProfitCentreOnDocument) not in (@ProfitCentreMaster)

Begin

set @error =1

set @error_message = 'Profit Center is wrong,Please select the right profit Center else Consult to Finance Department'

End

END

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Welcome you post on the forum.

You may try:

IF (@object_type = '30' AND @transaction_type in ('A', 'U')

BEGIN

If Exists (Select T0.Account from jdt1 T0 inner join oact T1 ON T1.AcctCode=T0.Account AND T1.GroupMask = 5

where T0.TransId = @list_of_cols_val_tab_del AND T0.ProfitCode NOT IN (select PrcCode from ocr1 where PrcCode >= 91100001 and PrcCode <= 91921001))

Begin

set @error =1

set @error_message = 'Profit Center is wrong,Please select the right profit Center else Consult to Finance Department'

End

END

Thanks,

Gordon

Former Member
0 Kudos

Thanks a lot, it really works.

Answers (0)