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 */
