Transaction Notification Samples
An example of SP that must be filled or input by user before adding a document :
USe [DBName]
Go
/****** Object: StoredProcedure [dbo].[SBO_SP_TransactionNotification] Script Date: 20120721 Kennedy******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[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
declare @dno nvarchar (200)
select @error = 0
select @error_message = N’Ok’
——————————————————————————————————————————–
/* Start */
if @transaction_type in (‘A’, ‘U’)
and @object_type in (’23’,’17’,’15’,’16’,’203′,’13’,’14’)
begin
if exists(
–Sales Quotation
select DocEntry
from OQUT with (nolock)
where @object_type = ’23’
and @list_of_key_cols_tab_del = N’DocEntry’
and DocEntry = @list_of_cols_val_tab_del
and isnull(U_SO_TRANS_TYPE,”) = ”
union all
–SO
select DocEntry
from ORDR with (nolock)
where @object_type = ’17’
and @list_of_key_cols_tab_del = N’DocEntry’
and DocEntry = @list_of_cols_val_tab_del
and isnull(U_SO_TRANS_TYPE,”) = ”
union all
–DO
select DocEntry
from ODLN with (nolock)
where @object_type = ’15’
and @list_of_key_cols_tab_del = N’DocEntry’
and DocEntry = @list_of_cols_val_tab_del
and isnull(U_SO_TRANS_TYPE,”) = ”
union all
–Return
select DocEntry
from ORDN with (nolock)
where @object_type = ’16’
and @list_of_key_cols_tab_del = N’DocEntry’
and DocEntry = @list_of_cols_val_tab_del
and isnull(U_SO_TRANS_TYPE,”) = ”
union all
–A/R DP
select DocEntry
from ODPI with (nolock)
where @object_type = ‘203’
and @list_of_key_cols_tab_del = N’DocEntry’
and DocEntry = @list_of_cols_val_tab_del
and isnull(U_SO_TRANS_TYPE,”) = ”
union all
–A/R Invoice
select DocEntry
from OINV with (nolock)
where @object_type = ’13’
and @list_of_key_cols_tab_del = N’DocEntry’
and DocEntry = @list_of_cols_val_tab_del
and isnull(U_SO_TRANS_TYPE,”) = ”
union all
–A/R Credit Memo
select DocEntry
from ORIN with (nolock)
where @object_type = ’14’
and @list_of_key_cols_tab_del = N’DocEntry’
and DocEntry = @list_of_cols_val_tab_del
and isnull(U_SO_TRANS_TYPE,”) = ”
)
begin
set @error = -1
set @error_message = ‘SO Transaction Type must be filled’
end
end
/* End */
Hi Kennedy,
Can you give me an example in which situation we have to use this SP. So that I can make use of it..
If you required to make a field or UDF to be mandatory for the End user you can try this
Rgds
Kennedy
Hi Kennedy,
I have two question:
1) Is it possible to use SP just for warning and no blocking action?
2) How can mandatory the value of a field to fill just based on a dynamic list of data?
Would you please help me?
thank you
Hi
This SP is useful
Thanks
Hi expert,
i need help for this Transaction notification, all i want is the button add in a/p invoice if the user click this button, the docstatus on my RFP ad-on change to Close,
IF @object_type =’13’AND @transaction_type IN(‘A’)
BEGIN
DECLARE @State varchar(32),@DocNum varchar (32)
SELECT @State =DocStatus ,@DocNum =U_APP_DocNum FROM [OPCH]
IF @State =’1′
UPDATE [@APP_ORFP] SET U_APP_DocStatus =’2′ WHERE U_APP_DocNum =@DocNum
END
thankx
Hello,
What condition should I need to use to trigger my script only on normal document adding or updating but not on adding cancellation document?
Help is much appreciated.