Stored procedure help

[Former Member]

I am trying to check if the item is discontinued and in stock. If the item is discontinued and not in stock then it should block the user from entering a sales order, but the SP only checks fro the discontinued item. Can some one tell me what is wrong with this query?

If (@Object_Type='17') and (@Transaction_Type='A'or @Transaction_Type='U')

Begin

If exists (Select T1.LineNum from RDR1 T1 INNER JOIN ORDR T0 ON

T0.DocENtry = T1.DocEntry inner join OITM t2 on t1.itemcode=t2.itemcode

inner join OitW T3 on T2.itemcode=t3.itemcode where T1.linestatus='o'

and t2.U_discontinuedDate is not null and T3.OnHand =0 and T0.DocEntry= @list_of_cols_val_tab_del)

Begin

set @Error='910'

Set @error_message='Item on this order has been discontinued '

End

End

Thank you

Chintan

[Former Member]

try this code

If (@Object_Type='17') and (@Transaction_Type='A'or @Transaction_Type='U')

Begin

If exists (Select T1.LineNum from RDR1 T1 INNER JOIN ORDR T0 ON

T0.DocENtry = T1.DocEntry inner join OITM t2 on t1.itemcode=t2.itemcode

inner join OitW T3 on T2.itemcode=t3.itemcode where T1.linestatus='o'

and t2.U_discontinuedDate is not null

and (T3.OnHand+T3.OnOrder-T3.IsCommited) <= 0

and T1.WhsCode = T3.WhsCode

and T0.DocEntry= @list_of_cols_val_tab_del)

Begin

set @Error='910'

Set @error_message='Item on this order has been discontinued '

End

End

Krishnan

[Former Member]

Thanks krishnan, the Query works fine. Is there a way to make it a pop-up and give a user choice to proceed instead of completely blocking it ?

Thank you

Chintan

[Former Member]

I don't think you can do this from the Stored Procedure. You will need to develop an SDK Add-On for this.

Krishnan