Skip to Content
author's profile photo Former Member
Former Member

Required SP_TN based on Item Creation date

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 30, 2016 at 01:32 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 30, 2016 at 01:41 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.