cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure to block cost center or profit center to present in JE

Former Member
0 Kudos

Hi ...

I have a case where i need to block if either one of the cost center or the Profit Center is not present in the manual journal entry.

Below is my Stored Procedure which i have written....My Problem is that this block allows me to update only if both the Cost Center and the Profit Center is Present and does not allow if any one is present.

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

BEGIN

  if(Select Count(*) from OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId=T1.TransId

  INNER JOIN OACT T2 ON T2.AcctCode=T1.Account

  where  T2.ExportCode LIKE '1%' And (isnull(T1.ProfitCode,0)='0'  OR ISNULL(T1.OcrCode2,'')='')  and

   T0.TransId=@list_of_cols_val_tab_del)>0

  begin

  SET @error = 0201

     SET @error_message= 'R1'

end

end

What could be the problem...?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

Have you tried inner query to bring back anything?

Select Count(*) from OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId=T1.TransId

  INNER JOIN OACT T2 ON T2.AcctCode=T1.Account

  where  T2.ExportCode LIKE '1%' And (isnull(T1.ProfitCode,0)='0'  OR ISNULL(T1.OcrCode2,'')='')

Thanks,

Gordon

former_member205766
Active Contributor
0 Kudos

Hi Gopi

Try This

IF @transaction_type IN ('A','U') and @Object_type = 30

BEGIN

IF EXISTS (SELECT T0.TransId FROM dbo.JDT1 T0

WHERE ((T0.ProfitCode ='' OR T0.ProfitCode IS NULL) and (T0.[OcrCode2]='' OR T0.[OcrCode2] IS NULL) and (T0.[OcrCode3]='' OR T0.[OcrCode3] IS NULL) and

(T0.[OcrCode4]='' OR T0.[OcrCode4] IS NULL) and (T0.[OcrCode5]='' OR T0.[OcrCode5] IS NULL)) AND T0.TransId = @list_of_cols_val_tab_del)

Begin

SET @Error = 1111

SET @error_message = 'At least one cost center is mandatory!'

End

End

With Regards

Balaji Sampath