How to write stored procedure in SAP B1 2007A ?

[Former Member]

Hi,

Customer is using 2007A, they want system prompting out message if the delivery quantities is more than SO quantities. We believe this can be done using standard stored procedure in SAP Business One. May I know how and whether this will void SAP support?

Regards

Thomas

[former_member186095 · Accepted solution]

Hi,

you just need to open stored procedured transaction notification in the stored procedure folder in the programmability folder of your live database. It is in the SQL server databases folder.

This sample SQL script is similar what you need :

IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'15')

begin

if exists (SELECT T0.BaseEntry, SUM(T0.Quantity) FROM [dbo].[DLN1] T0 INNER

JOIN [dbo].[RDR1] T1 ON T1.DOCENTRY =

T0.BASEENTRY

WHERE T0.BaseType = 17 AND T0.ItemCode = T1.ItemCode AND T0.BaseLine = T1.LineNum

and T0.DOCENTRY = @list_of_cols_val_tab_del

GROUP BY T0.BaseEntry

HAVING (SUM(T0.Quantity) > SUM(T1.Quantity)) or sum(t0.quantity) > sum(t0.BaseOpnQty))

begin

select @Error = 10, @error_message = 'Delviery quantity is over SO quantity'

end

end

Anyway, search the forum first are good solution too. Here is the link:

try to browse it if the case you submit have had solution

Rgds

[Former Member]

Check this link for explanation of SP_Tranaction Notification Procedure [https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/e991e2b9-0901-0010-0395-ef5268b00aaf]

[Former Member]

If they use SP_TransactionNotifiction, that would be fine. Beside that, they have to use SDK to program it using DI API or UI API.

Thanks,

Gordon