Skip to Content
avatar image
Former Member

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

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...?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 11, 2014 at 12:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 11, 2014 at 08:13 PM

    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

    Add comment
    10|10000 characters needed characters exceeded