USP_DATAFORMTEMPLATE_EDITLOAD_REVENUESPLITDETAILS_2

The load procedure used by the edit dataform template "Revenue Split Details Edit Form 2"

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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@APPLICATIONCODE	tinyint	INOUT	Application code
@APPLICATION	nvarchar(18)	INOUT	Application
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent
@APPEALID	uniqueidentifier	INOUT	Appeal
@DESIGNATIONID	uniqueidentifier	INOUT	Designation
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@OTHERTYPECODEID	uniqueidentifier	INOUT	Other type
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity
@HASASSOCIATEDPLANNEDGIFT	bit	INOUT	Has associated planned gift
@CONSTITUENTTYPE	tinyint	INOUT	Constituent type
@DECLINESGIFTAID	bit	INOUT	Constituent declines Gift Aid for this application
@GIFTAIDCOMMITTED	bit	INOUT	Gift Aid committed
@ISGIFTAIDSPONSORSHIP	bit	INOUT	Gift Aid sponsorship

Definition

Copy



          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REVENUESPLITDETAILS_2
          (
            @ID uniqueidentifier,
            @DATALOADED bit = 0 output,
            @TSLONG bigint = 0 output,
            @APPLICATIONCODE tinyint = null output,
            @APPLICATION nvarchar(18) = null output,
            @CONSTITUENTID uniqueidentifier = null output,
            @APPEALID uniqueidentifier = null output,
            @DESIGNATIONID uniqueidentifier = null output,
            @CATEGORYCODEID uniqueidentifier = null output,
            @OTHERTYPECODEID uniqueidentifier = null output,
            @OPPORTUNITYID uniqueidentifier = null output,
            @HASASSOCIATEDPLANNEDGIFT bit = null output,
            @CONSTITUENTTYPE tinyint = null output,
            @DECLINESGIFTAID bit = null output,
            @GIFTAIDCOMMITTED bit = null output,
            @ISGIFTAIDSPONSORSHIP bit = null output
          )
          as
            set nocount on

            declare @REVENUEID uniqueidentifier;
            select
              @DATALOADED = 1,
              @TSLONG = FINANCIALTRANSACTIONLINEITEM.TSLONG,
              @APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE,
              @APPLICATION = REVENUESPLIT_EXT.APPLICATION,
              @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
              @DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID,
              @CATEGORYCODEID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID,
              @OTHERTYPECODEID = REVENUESPLITOTHER.OTHERTYPECODEID,
              @REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
              @OPPORTUNITYID = REVENUEOPPORTUNITY.OPPORTUNITYID,
              @CONSTITUENTTYPE = case
                when CONSTITUENT.ISORGANIZATION = 0 and CONSTITUENT.ISGROUP = 0 then 0
                when CONSTITUENT.ISORGANIZATION = 1 then 1
                when CONSTITUENT.ISGROUP = 1 and GROUPDATA.GROUPTYPECODE = 0 then 2
                else 3
              end,
              @DECLINESGIFTAID = REVENUESPLITGIFTAID.DECLINESGIFTAID,
              @GIFTAIDCOMMITTED = case when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '' then 1 else 0 end,
              @ISGIFTAIDSPONSORSHIP = REVENUESPLITGIFTAID.ISSPONSORSHIP
            from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            left join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.REVENUECATEGORY on FINANCIALTRANSACTIONLINEITEM.ID = REVENUECATEGORY.ID
            left join dbo.REVENUESPLITOTHER on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITOTHER.ID
            left join dbo.REVENUEOPPORTUNITY on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEOPPORTUNITY.ID
            left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
            left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
            where
              FINANCIALTRANSACTIONLINEITEM.ID = @ID
                            and FINANCIALTRANSACTION.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

            select
              @APPEALID = REVENUE_EXT.APPEALID
            from dbo.FINANCIALTRANSACTION
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            where FINANCIALTRANSACTION.ID = @REVENUEID
                            and FINANCIALTRANSACTION.DELETEDON is null

            if exists (select 1 from dbo.PLANNEDGIFTREVENUESPLIT where REVENUESPLITID = @ID) or
               exists (select 1 from dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS as PLANNEDGIFTREVENUE 
                      inner join dbo.INSTALLMENTSPLITPAYMENT on PLANNEDGIFTREVENUE.REVENUEID = INSTALLMENTSPLITPAYMENT.PLEDGEID
                  where INSTALLMENTSPLITPAYMENT.PAYMENTID = @ID)
            begin
              set @HASASSOCIATEDPLANNEDGIFT = 1
            end
            else
              set @HASASSOCIATEDPLANNEDGIFT = 0