Required SP_TN based on Item Creation date

[Former Member]

Hi,

I have small requirement. Based on item master creation date need to restrict some transaction through SP_TN.

Scenario:

1. Item Master Creation as "'01.09.2016 & Higher" Can't Allow to add Goods receipt/Goods issue/Inventory Posting.

2. If Item Master creation smaller Than "0109.2016" can be allow to add/adjust & amend.

Super user can be amend or adjust any criteria. This is my requirement.

Please help me to provide SP_TN.    

Thanks in advance.

[Former Member · Accepted solution]

Hi Mohamed,

Try below for Goods receipt.

IF @transaction_type IN (N'A', N'U')

   AND (@object_type = '59')

BEGIN

    IF   EXISTS(

           SELECT T0.DocEntry

           FROM   OIGN T0

                  INNER JOIN IGN1 T1

                       ON  T0.DocEntry = T1.DocEntry

                  INNER JOIN OITM T2

                       ON  T1.ItemCode = T2.ItemCode

           WHERE  T0.DocEntry = @list_of_cols_val_tab_del AND T2.CreateDate > '20160901'

       )

    BEGIN

        SELECT @Error = 13                                                   

        SELECT @error_message = 'Item created after 01st Sep'16'

    END

END

Thanks,

Harshal

[Former Member · Accepted solution]

Hi,

Thank You. But This SP Blocking all the user's. Only Super User Can Add & others can be restrict.

How to include condition for Super User Sign.

[narayanis]

Hi,

You can try this code snippet. I tried this for goods receipt for logged in user for preventing him to add as goods receipt transaction. For further restrictions you can make use of usersign to restrict user wise.

if @object_type = 59 and @transaction_type = 'A'

Declare @minline22 int

Declare @maxline22 int

Declare @CreateDt as VarChar(20)

begin

  set @minline22 = (select min(T0.linenum) from IGN1 T0 where T0.docentry=@list_of_cols_val_tab_del)

  set @maxline22 = (select max(T0.linenum) from IGN1 T0 where T0.docentry=@list_of_cols_val_tab_del)

  select @ItemName = itemcode from ign1 inner join oign on oign.docentry = ign1.docentry

  while @minline22 <= @maxline22

  Begin

  select @itmCd = T0.itemcode from IGN1 T0 where T0.docentry = @list_of_cols_val_tab_del and T0.linenum = @minline22

  select @CreateDt = convert(varchar(20),T0.CreateDate,102) from OITM T0 where T0.itemcode = @itmCd

  if @CreateDt = '2016.09.27'

  begin

  set @error = -50

  set @error_message = 'Item Was Created On' + convert(char,@CreateDt)

  end

  set @minline22 = @minline22+1

  End

End

Hope this will give you an idea.

Let me know.

Regards

Narayani