Skip to Content

Problem in SP to Auto release Child Production Order

Dear Experts,

We have created below SP to give Production Order Release rights of particular Document Series to particular users. We have also configured SP to Auto Release Child Work Order as soon as Main Father Production Order Status update to Release which is working find since last 3 months. But yesterday we got SQL Server Error to update Production Order Release Status with Error Message: 3621 and our channel partner told us that due to below SP Code having problem and cause of this we face this error.

Kindly look into the same and give your valuable input.

IF @transaction_type in('U') AND (@Object_type = N'202')

declare @satusn as varchar(10)

BEGIN

declare @satusn1 as varchar(10)

declare @dconumn1 as varchar(10)

set @satusn1 = ( select owor.Status from OWOR where owor.DocEntry = @list_of_cols_val_tab_del)

set @dconumn1 = ( select owor.DocNum from OWOR where owor.DocEntry = @list_of_cols_val_tab_del)

if @satusn = 'R'

begin

declare @tempudepartment nvarchar(50)

declare @tempitemcode nvarchar(50)

declare @tempDocEntry int

declare @u_mwo int =@dconumn1

DECLARE cur_UpdateDepartment CURSOR

STATIC FOR

select owor.DocEntry, owor.U_Dept, owor.ItemCode from OWOR inner join oitm on oitm.itemcode = owor.itemcode where u_mwo = @u_mwo

and owor.Status not in ('C')

and oitm.U_RWO not in ('electric','Design')

OPEN cur_UpdateDepartment

IF @@CURSOR_ROWS > 0

BEGIN

FETCH NEXT FROM cur_UpdateDepartment INTO @tempDocEntry,@tempudepartment,@tempitemcode

WHILE @@Fetch_status = 0

BEGIN

declare @dept nvarchar(50)=''

select @dept= U_RWO from OITM where ItemCode = @tempitemcode

update OWOR set U_Dept = @dept where DocEntry= @tempDocEntry

FETCH NEXT FROM cur_UpdateDepartment INTO @tempDocEntry,@tempudepartment,@tempitemcode

END

END

CLOSE cur_UpdateDepartment

DEALLOCATE cur_UpdateDepartment

end

end

Thanks & Warm Regards,

Vijay N. Chavda

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 16, 2016 at 07:48 PM

    Hi Vijay,

    if you haven't any syntax error in you SP, your SP will not run never!

    The variable @satusn never assigned

    Kind Regards

    Evangelos D. Plagianos

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 16, 2016 at 08:06 PM

    Hi Vijay,

    after a search over the net, i focused that the error may caused to the assignment of a variable.

    The only things that you may have are:

    1) Arithmetic overflow (when you assigning DocEntry to @tempDocEntry). But to be honest, in that case you will not receive 3621.

    2) It is possible the customer turned the UDF U_RWO to mandatory (not null) and you are receiving a null result from OITM. In that case you are trying to assign null to a not null field and the query crash!

    PS.

    Its not a good tactic to declare variables into a block of IF. Makes the debugging harder.


    Kind Regards,

    Evangelos D. Plagianos

    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.