有关SBO_SP_TransactionNotification介绍

SAP BUSINESS ONE provides the SBO_SP_TransactionNotification stored procedure mechanism for receiving notification of data-driven events.This stored procedure is fired whenever operations are performed on business objects like document or master records in SAP Business One.This applies not only to objects already exposed through DI API,but to any business object in the application-including User-Defined Objects(UDO).By adding your own Transact-SQL code ,you can pass parameters to your application and retrieve the corresponding objects from SAP Business ONE.

The SBO_SP_TransactionNotification stored procedure is created automatically when you create a new company in SAP Business One. It is missing from some of the SAP Business One demo databases (for example the US demo database), but you can easily add it manually using SQL Enterprise Manager or SQL Query Analyzer. When it is initially created, it looks like this:

CREATE proc SBO_SP_TransactionNotification

@object_type nvarchar(20),, -- SBO Object Type

@transaction_type nchar(1),

-- When a record was [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose

@num_of_cols_in_key int,

@list_of_key_cols_tab_del nvarchar(255),

@list_of_cols_val_tab_del nvarchar(255)

AS

begin

-- Return values

declare @error int -- Result (0 for no error)

declare @error_message nvarchar (200) -- Error string to be displayed

select @error = 0

select @error_message = N'Ok'

------------------------------------------------------------

-- ADD YOUR CODE HERE

-------------------------------------------------------------

-- Select the return values

select @error, @error_message

end

GO

There are five output parameters:

Parameter	Example	Description
@objecttype	2 (Business Partner)	Since version 2005 it returns a string representing the type of object – used to be a numerical value in previous versions. A full list of object types exposed through DI API can be found in the DI API documentation under the section Enumerations. The enumeration is called BoObjectTypes.
@transaction_type	U (update)	It may have values of: A (where a record was added) U (where a record was updated) D (where a record was deleted), C (where a document was canceled) L (where a document was closed)
@num_of_cols_in_key	1	Returns the number of columns or fields in the key to the record. A Business Partner, for example, has a key consisting of a single field (CardCode), so this variable would have a value of "1". A Special Prices object, however, has a key consisting of the CardCode and ItemCode, so for a Special Prices object this variable would have a value of "2".
@list_of_key_cols_tab_del	CardCode	It returns a tab-delimited list of column names (field names) that represent the object key. For a Business Partner, this would be "CardCode". For a Special Prices object, it would be "CardCode ItemCode".
@list_of_cols_val_tab_del	C40001	This returns a tab-delimited list of values required to retrieve the object from Business One. For example, a Business Partner record might have a value of "C40001". A Special Prices object would contain 2 fields separated by a tab character. For example: "V10005 ... A00001"

For Example :

以下是在项目时做的对生产收货的上些控制
ALTER proc SBO_SP_TransactionNotification

@object_type nvarchar(20), -- SBO Object Type
@transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)

AS

begin

-- Return values
declare @error int -- Result (0 for no error)
declare @error_message nvarchar (200) -- Error string to be displayed
select @error = 0
select @error_message = N'Ok'
--------------------------------------------------------------------------------------------------------------------------------

DECLARE @stop smallint --only use it to accord with sql-expression
--生产发货
IF @object_type = '60'
begin
declare @cnt int
declare @PlanQty decimal
declare @IgnQtySum decimal
declare @IgnQtyCur decimal
DECLARE @CN INT
declare @bas int
declare @min decimal
declare @compqty decimal
declare @quan decimal
declare @u_003 varchar(20)
declare @count int
declare @ite varchar(20)
--deal with the transaction based on transactiontype
IF @transaction_type IN('A','U')
declare cursend cursor for select t1.baseentry,sum(t1.quantity) from oign t0,ign1 t1 where t0.docentry=t1.docentry and t1.baseline is null and t0.docentry = @list_of_cols_val_tab_del group by baseentry
open cursend
fetch next from cursend into @bas,@quan
while @@fetch_status=0
begin

--生产订单对应的组件是否已经都发货（不包括反冲件）
SELECT @CN=count(*) FROM OWOR T0,WOR1 T1 WHERE T0.DOCENTRY=T1.DOCENTRY and t0.type in('P','R')AND T1.ISSUETYPE='M' AND T0.DOCNUM=@BAS
and t1.itemcode not in (select T1.ITEMCODE from oige t0,ige1 t1 where t0.docentry=t1.docentry and t1.baseentry=@bas )
select @IgnQtyCur=sum(quantity)from ign1 where docentry = @list_of_cols_val_tab_del group by baseentry
select @PlanQty = plannedqty from owor where docentry = @bas
select @IgnQtySum = sum(quantity) from ign1 where baseentry = @bas and baseline is null
select @compqty=ISNULL(sum(t1.quantity),0) from ign1 t1

where t1.baseentry=@bas and baseline is null

and docentry !=@list_of_cols_val_tab_del
select @min=ISNULL(min(t0.quantity/t1.baseqty),0)

from
(select t1.itemcode,SUM(t1.quantity) quantity
from oige t0,ige1 t1
where t0.docentry=t1.docentry
and t1.baseentry=@bas
GROUP BY T1.ITEMCODE,T1.BASEENTRY) t0,
(SELECT t1.itemcode,t1.baseqty,t0.plannedqty
FROM OWOR T0,WOR1 T1
WHERE T0.DOCENTRY=T1.DOCENTRY
AND T0.DOCNUM=@bas
--AND T1.ISSUETYPE='M'
) t1
where t0.itemcode=t1.itemcode


IF(@CN>0)
begin
SELECT @error = 1
select @error_message=N'请确认生产订单(' +convert(varchar,@bas) +')的所有物料是否已发货！！！'
SELECT @error,@error_message
close cursend
DEALLOCATE cursend
return
end

else if (@IgnQtySum>@PlanQty)
begin
SELECT @error = 1
SELECT @error_message = N'错误---超过订单计划数量收货(订单号=' +convert(varchar,@bas) + N' 计划数量=' + convert(varchar,@planqty) + N' 已执行数量=' + convert(varchar,@IgnQtySum - @IgnQtyCur ) + N')'
SELECT @error,@error_message
close cursend
DEALLOCATE cursend
return
end
else IF EXISTS(SELECT t1.itemcode,t1.baseqty,t0.plannedqty
FROM OWOR T0,WOR1 T1
WHERE T0.DOCENTRY=T1.DOCENTRY
AND T0.DOCNUM=@bas
AND T1.ISSUETYPE='M')
BEGIN
if(@minbegin
SELECT @error = 1
SELECT @error_message = N'错误---生产收货数量('+convert(varchar,@quan)+')'+'大于生产发货数量('+convert(varchar,@min)+')'+'已收货数量('+convert(varchar,@compqty)+')'
SELECT @error,@error_message
close cursend
DEALLOCATE cursend
return
end
END

fetch next from cursend into @bas,@quan
end
close cursend
DEALLOCATE cursend
end

ELSE IF @transaction_type = 'D' --where a record was deleted
begin
select @stop = 0
end
ELSE IF @transaction_type = 'C' --where a document was canceled
begin
select @stop = 0
end
ELSE IF @transaction_type = 'L' --where a document was closed
begin
select @stop = 0
end
end

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/493515/viewspace-910408/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/493515/viewspace-910408/