cancel
Showing results for 
Search instead for 
Did you mean: 

Sp for avoid the negative inventory for Sale/Transfer/produce transcation

Former Member
0 Kudos

Team

I have one warehouse used only for store the FG stock.

I want to black the usage of Negative inventory usage in this warehouse for Sale / Transfer / Production or for all transaction..

I request you to provide the suitable store procedure

Can we change in the following procedure

IF @object_type= '67' AND @transaction_type in ('A')

BEGIN

IF EXISTS (SELECT T0.DocEntry FROM [dbo].[WTR1] T0, [dbo].[OITW] T1, dbo.[OWTR] T2, dbo.[OWHS] T3 WHERE T3.WhsCode = T2.U_RecWshe and

T1.ItemCode = T0.ItemCode and T2.Filler = 'Y' and T1.OnHand <0 and T0.WhsCode = 'X' and T2.DocEntry = T0.DocEntry  AND  T2.DOCENTRY= @list_of_cols_val_tab_del)

BEGIN

SELECT @Error = 1, @error_message = 'Insufficient Stock'

END

END

Thanks

Anantha Desai

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Anantha,

From the error message, you have quite a few lines in the SP already. Can you located a line with ObjectType = '15' before you add the new code?

Thanks,

Gordon

Former Member
0 Kudos

Thanks for feedback, I want this for delivery, can you change this

Former Member
0 Kudos

Hi Anantha ,

Use this

IF @object_type= '15' AND @transaction_type in ('A')

BEGIN

IF EXISTS (select * from DLN1 r inner join oitw w on w.ItemCode=r.ItemCode and w.WhsCode='YOUR WAREHOUSE CODE'

where w.OnHand <r.Quantity     AND r.DOCENTRY= @list_of_cols_val_tab_del)

BEGIN

SELECT @Error = 1, @error_message = 'Insufficient Stock'

END

END

Former Member
0 Kudos

Thanks, I will provide the feedback after testing.

Former Member
0 Kudos

I am facing following error

"

Msg 102, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 82
Incorrect syntax near 'Insufficient'.
Msg 4145, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 117
An expression of non-boolean type specified in a context where a condition is expected, near 'with'.
Msg 4145, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 194
An expression of non-boolean type specified in a context where a condition is expected, near 'with'.
Msg 105, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 230
Unclosed quotation mark after the character string '

"

Former Member
0 Kudos

Hi Anantha,

Can you post the screenshot of  your SQL page

Former Member
0 Kudos

Hi Anantha,

Kindly use this SP to block Inventory transfer and in query give your warehouse code

IF @object_type= '67' AND @transaction_type in ('A')

BEGIN

IF EXISTS (select * from wtr1 r inner join oitw w on w.ItemCode=r.ItemCode and w.WhsCode='YOUR WAREHOUSE CODE'

where w.OnHand <r.Quantity     AND r.DOCENTRY= @list_of_cols_val_tab_del)

BEGIN

SELECT @Error = 1, @error_message = 'Insufficient Stock'

END

END