USP_DATAFORMTEMPLATE_EDITLOAD_REVENUESPLITDETAILSADJUST_4

The load procedure used by the edit dataform template "Revenue Split Details Adjust Form 4"

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
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjustment post date
@ADJUSTMENTDATE	datetime	INOUT	Adjustment date
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment details
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Adjustment status code
@CONSTITUENTTYPE	tinyint	INOUT	Constituent type
@DECLINESGIFTAID	bit	INOUT	Constituent declines Gift Aid for this application
@ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@GIFTAIDCOMMITTED	bit	INOUT	Gift Aid committed
@ISGIFTAIDSPONSORSHIP	bit	INOUT	Gift Aid sponsorship
@ALLOWGLDISTRIBUTIONS	bit	INOUT
@DEPOSITID	uniqueidentifier	INOUT
@POSTDATE	date	INOUT
@REQUIREDEPOSIT	bit	INOUT

Definition

Copy



          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REVENUESPLITDETAILSADJUST_4
          (
            @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,
            @ADJUSTMENTPOSTDATE datetime = null output,
            @ADJUSTMENTDATE datetime = null output,
            @ADJUSTMENTREASON nvarchar(300) = null output,
            @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
            @CONSTITUENTTYPE tinyint = null output,
            @DECLINESGIFTAID bit = null output,
            @ADJUSTMENTREASONCODEID uniqueidentifier = null output,
            @GIFTAIDCOMMITTED bit = null output,
            @ISGIFTAIDSPONSORSHIP bit = null output,
            @ALLOWGLDISTRIBUTIONS bit = null output,
            @DEPOSITID uniqueidentifier = null output,
            @POSTDATE date = null output,
            @REQUIREDEPOSIT bit = null output
          )
          as
            set nocount on

            declare @REVENUEID uniqueidentifier
            select
              @DATALOADED = 1,
              @TSLONG = REVENUESPLIT.TSLONG,
              @APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE,
              @APPLICATION = REVENUESPLIT_EXT.APPLICATION,
              @CONSTITUENTID = REVENUE.CONSTITUENTID,
              @DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID,
              @CATEGORYCODEID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID,
              @OTHERTYPECODEID = REVENUESPLITOTHER.OTHERTYPECODEID,
              @REVENUEID = REVENUESPLIT.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,
              @DEPOSITID = BADP.DEPOSITID,
              @POSTDATE = REVENUE.POSTDATE
            from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
            inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.REVENUECATEGORY on REVENUESPLIT.ID = REVENUECATEGORY.ID
            left join dbo.REVENUESPLITOTHER on REVENUESPLIT.ID = REVENUESPLITOTHER.ID
            left join dbo.REVENUEOPPORTUNITY on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
            left join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
            left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
            left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on REVENUE.ID = BADP.ID
            where
              REVENUESPLIT.ID = @ID
              and REVENUESPLIT.TYPECODE <> 1
              and REVENUESPLIT.DELETEDON is null
              and REVENUE.DELETEDON is null

            select
              @APPEALID = REVENUE_EXT.APPEALID
            from dbo.REVENUE_EXT
            where REVENUE_EXT.ID = @REVENUEID

            select
              @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
              @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
              @ADJUSTMENTPOSTSTATUSCODE = 0

            if not @DEPOSITID is null
              select @ADJUSTMENTPOSTDATE = POSTDATE 
              from dbo.BANKACCOUNTDEPOSITPAYMENT 
              inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTDEPOSIT.ID
              inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION.ID
              where BANKACCOUNTDEPOSITPAYMENT.ID = @REVENUEID;

            select top 1
              @ADJUSTMENTDATE = ADJUSTMENT.DATE,
              @ADJUSTMENTPOSTDATE = ADJUSTMENT.POSTDATE,
              @ADJUSTMENTREASON = ADJUSTMENT.REASON,
              @ADJUSTMENTPOSTSTATUSCODE = 1,
              @ADJUSTMENTREASONCODEID = ADJUSTMENT.REASONCODEID
            from dbo.ADJUSTMENT
            where ADJUSTMENT.REVENUEID = @REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1

            -- Check GL business rule for this account system and set to 'Do not post' if needed.

            declare @PDACCOUNTSYSTEMID uniqueidentifier;
            select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID,
            @REQUIREDEPOSIT = PDACCOUNTSYSTEM.REQUIREDPOSIT
            from dbo.PDACCOUNTSYSTEMFORREVENUE
            inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            where PDACCOUNTSYSTEMFORREVENUE.ID = @REVENUEID;
            set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
            if @ALLOWGLDISTRIBUTIONS = 0
              set @ADJUSTMENTPOSTDATE = null;