USP_DATAFORMTEMPLATE_VIEW_SALESDEPOSITSUMMARYREPORT

The load procedure used by the view dataform template "Sales Deposit Summary Report View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CASHDEPOSITID	uniqueidentifier	INOUT	Cash Deposit ID
@CHECKDEPOSITID	uniqueidentifier	INOUT	Check Deposit ID
@CASHDEPOSITCOLLECTION	nvarchar(max)	INOUT	All Cash Deposits
@CHECKDEPOSITCOLLECTION	nvarchar(max)	INOUT	All Cash Deposits

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESDEPOSITSUMMARYREPORT
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CASHDEPOSITID uniqueidentifier = null output,
                    @CHECKDEPOSITID uniqueidentifier = null output,
                    @CASHDEPOSITCOLLECTION nvarchar(max) = null output,
                    @CHECKDEPOSITCOLLECTION nvarchar(max) = null output
                )
                as
                    set nocount on;

                    declare @TABLENAME nvarchar(255);
                    declare @SQL nvarchar(4000);

                    select @TABLENAME = [BUSINESSPROCESSOUTPUT].[TABLENAME]
                    from dbo.[BUSINESSPROCESSSTATUS]
                    inner join dbo.[BUSINESSPROCESSOUTPUT]
                        on [BUSINESSPROCESSSTATUS].[ID] = [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID]
                    where [BUSINESSPROCESSSTATUS].[ID] = @ID

                    set @DATALOADED = 1

                    set @SQL = 'select top 1 @DEPOSITID = [DEPOSITID]
                                from dbo.' + @TABLENAME + '
                                where 
                                    [PAYMENTMETHODCODE] = @PAYMENTMETHODCODE and
                                    [DEPOSITID] is not null'

                    exec sp_executesql @SQL, N'@DEPOSITID uniqueidentifier out, @PAYMENTMETHODCODE tinyint', @PAYMENTMETHODCODE = 0, @DEPOSITID = @CASHDEPOSITID output;
                    exec sp_executesql @SQL, N'@DEPOSITID uniqueidentifier out, @PAYMENTMETHODCODE tinyint', @PAYMENTMETHODCODE = 1, @DEPOSITID = @CHECKDEPOSITID output;

                    set @SQL = 'set @DEPOSITCOLLECTION = '''';'

                    exec sp_executesql @SQL, N'@DEPOSITCOLLECTION nvarchar(max) out, @PAYMENTMETHODCODE tinyint', @PAYMENTMETHODCODE = 0, @DEPOSITCOLLECTION = @CASHDEPOSITCOLLECTION output;
                    exec sp_executesql @SQL, N'@DEPOSITCOLLECTION nvarchar(max) out, @PAYMENTMETHODCODE tinyint', @PAYMENTMETHODCODE = 1, @DEPOSITCOLLECTION = @CHECKDEPOSITCOLLECTION output;

                    if not exists (
                        select [ID]
                        from dbo.[BANKACCOUNTDEPOSIT]
                        where [ID] = @CASHDEPOSITID
                    )
                    begin
                        set @CASHDEPOSITID = null;
                        set @CASHDEPOSITCOLLECTION = null;
                    end

                    if not exists (
                        select [ID]
                        from dbo.[BANKACCOUNTDEPOSIT]
                        where [ID] = @CHECKDEPOSITID
                    )
                    begin
                        set @CHECKDEPOSITID = null;
                        set @CHECKDEPOSITCOLLECTION = null;
                    end


                return 0;