USP_DATAFORMTEMPLATE_EDITLOAD_AUCTIONDONATIONADJUST

The load procedure used by the edit dataform template "Posted Auction Donation Data Form"

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.
@AMOUNT	money	INOUT	Value
@DATE	datetime	INOUT	Date
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@DESIGNATIONID	uniqueidentifier	INOUT	Designation
@ADJUSTMENTDATE	datetime	INOUT	Adjusted date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjusted post date
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Post status code
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment description
@ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@HADSPOTRATE	bit	INOUT	Had spot rate
@RATECHANGED	bit	INOUT	Rate changed
@BASEDECIMALDIGITS	bit	INOUT	Decimal digits
@BASEROUNDINGTYPECODE	tinyint	INOUT	Rounding type
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(100)	INOUT	Transaction currency description
@USERGRANTEDSPOTRATE	bit	INOUT	User granted spot rate
@ALLOWGLDISTRIBUTIONS	bit	INOUT

Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_AUCTIONDONATIONADJUST(
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @TSLONG bigint = 0 output,
                @AMOUNT money = null output,
                @DATE datetime = null output,
                @EXPIRATIONDATE datetime = null output,
                @DESIGNATIONID uniqueidentifier = null output,
                @ADJUSTMENTDATE datetime = null output,
                @ADJUSTMENTPOSTDATE datetime = null output,
                @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
                @ADJUSTMENTREASON nvarchar(300) = null output,
                @ADJUSTMENTREASONCODEID uniqueidentifier = null output,

                @BASECURRENCYID uniqueidentifier = null output,
                @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                @BASEEXCHANGERATEID uniqueidentifier = null output,
                @EXCHANGERATE decimal(20,8) = null output,
                @HADSPOTRATE bit = null output,
                @RATECHANGED bit = null output,
                @BASEDECIMALDIGITS bit = null output,
                @BASEROUNDINGTYPECODE tinyint = null output,
                @TRANSACTIONCURRENCYDESCRIPTION nvarchar(100) = null output,
                @USERGRANTEDSPOTRATE bit = null output,
                @ALLOWGLDISTRIBUTIONS bit = null output
            )
            as

            set nocount on;

            -- be sure to set these, in case the select returns no rows
            set @DATALOADED = 0;
            set @TSLONG = 0;

            --replace with commented code for PBI 102747
            set @USERGRANTEDSPOTRATE = 1;
            /*set @USERGRANTEDSPOTRATE = case 
                when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                    dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '911f104d-ba5f-4469-b0ae-184c879aea99') = 1 
                        then 1
                        else 0
                end;*/

            set @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
            set @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
            set @ADJUSTMENTPOSTSTATUSCODE = 0;


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


            select
                @DATALOADED = 1,
                @TSLONG = FINANCIALTRANSACTION.TSLONG,
                @AMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                @DATE = FINANCIALTRANSACTION.DATE,
                @EXPIRATIONDATE = AUCTIONITEM.EXPIRATIONDATE,
                @DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID,

                @BASECURRENCYID = V.BASECURRENCYID,
                @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE,
                @HADSPOTRATE = case when CURRENCYEXCHANGERATE.TYPECODE = 2 then 1 else 0 end,
                @RATECHANGED = 0,
                @BASEDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                @BASEROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
                @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(FINANCIALTRANSACTION.TRANSACTIONCURRENCYID)
            from
                dbo.FINANCIALTRANSACTION
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.AUCTIONITEM on AUCTIONITEM.REVENUEAUCTIONDONATIONID = FINANCIALTRANSACTION.ID
                inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
                left join dbo.CURRENCY on CURRENCY.ID = V.BASECURRENCYID
                left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
            where
                FINANCIALTRANSACTION.ID = @ID;


            -- Check GL business rule for this account system and set to 'Do not post' if needed.
            declare @PDACCOUNTSYSTEMID uniqueidentifier;
            select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE where ID = @ID;
            set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
            if @ALLOWGLDISTRIBUTIONS = 0
                set @ADJUSTMENTPOSTDATE = null;

            return 0;