USP_DATAFORMTEMPLATE_EDITLOAD_AUCTIONITEMADJUST

The load procedure used by the edit dataform template "Auction Item Adjustment 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.
@NAME	nvarchar(100)	INOUT	Name
@DESIGNATIONID	uniqueidentifier	INOUT	Designation
@EVENTAUCTIONID	uniqueidentifier	INOUT	Auction
@DESCRIPTION	nvarchar(255)	INOUT	Description
@AUCTIONITEMCATEGORYID	uniqueidentifier	INOUT	Category
@AUCTIONITEMSUBCATEGORYID	uniqueidentifier	INOUT	Subcategory
@DONORID	uniqueidentifier	INOUT	Donor
@DONATIONDATE	date	INOUT	Donation date
@EXPIRATIONDATE	date	INOUT	Expiration date
@VALUE	money	INOUT	Value
@MINIMUMBID	money	INOUT	Minimum bid
@REVENUEAUCTIONDONATIONID	uniqueidentifier	INOUT	Revenue ID
@ISANONYMOUS	bit	INOUT	Donation is anonymous
@ISPOSTED	bit	INOUT	Is posted
@ADJUSTMENTDATE	datetime	INOUT	Adjusted date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjusted post date
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment description
@ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Post status code
@POSTDATE	datetime	INOUT	Post date
@POSTSTATUSCODE	tinyint	INOUT	Post status
@PACKAGEID	uniqueidentifier	INOUT	Package
@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_AUCTIONITEMADJUST(
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @NAME nvarchar(100) = null output,
                        @DESIGNATIONID uniqueidentifier = null output,
                        @EVENTAUCTIONID uniqueidentifier = null output,
                        @DESCRIPTION nvarchar(255) = null output,
                        @AUCTIONITEMCATEGORYID uniqueidentifier = null output,
                        @AUCTIONITEMSUBCATEGORYID uniqueidentifier = null output,
                        @DONORID uniqueidentifier = null output,
                        @DONATIONDATE date = null output,
                        @EXPIRATIONDATE date = null output,
                        @VALUE money = null output,
                        @MINIMUMBID money = null output,
                        @REVENUEAUCTIONDONATIONID uniqueidentifier = null output,
                        @ISANONYMOUS bit = null output,
                        @ISPOSTED bit = null output,
                        @ADJUSTMENTDATE datetime = null output,
                        @ADJUSTMENTPOSTDATE datetime = null output,
                        @ADJUSTMENTREASON nvarchar(300) = null output,
                        @ADJUSTMENTREASONCODEID uniqueidentifier = null output,
                        @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
                        @POSTDATE datetime = null output,
                        @POSTSTATUSCODE tinyint = null output,
                        @PACKAGEID 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;

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

                        select
                            @DATALOADED = 1,
                            @TSLONG = AUCTIONITEM.TSLONG,
                            @NAME = AUCTIONITEM.NAME,
                            @AUCTIONITEMCATEGORYID = AUCTIONITEM.AUCTIONITEMCATEGORYID,
                            @AUCTIONITEMSUBCATEGORYID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID,
                            @DESCRIPTION = AUCTIONITEM.DESCRIPTION,
                            @VALUE = AUCTIONITEM.TRANSACTIONVALUE,
                            @MINIMUMBID = AUCTIONITEM.TRANSACTIONMINIMUMBID,
                            @DONATIONDATE = REVENUE.DATE,
                            @EXPIRATIONDATE = AUCTIONITEM.EXPIRATIONDATE,
                            @DONORID = REVENUE.CONSTITUENTID,
                            @EVENTAUCTIONID = AUCTIONITEM.EVENTAUCTIONID,
                            @DESIGNATIONID = REVENUESPLIT.DESIGNATIONID,
                            @REVENUEAUCTIONDONATIONID = AUCTIONITEM.REVENUEAUCTIONDONATIONID,
                            @ISANONYMOUS = REVENUE.GIVENANONYMOUSLY,
                            @ISPOSTED = case when REVENUEPOSTED.ID is null then 0 else 1 end,
                            @POSTDATE = REVENUE.POSTDATE,
                            @POSTSTATUSCODE = 0,
                            @PACKAGEID = AUCTIONITEM.PACKAGEID,
                            @BASECURRENCYID = AUCTIONITEM.BASECURRENCYID,
                            @TRANSACTIONCURRENCYID = AUCTIONITEM.TRANSACTIONCURRENCYID,
                            @BASEEXCHANGERATEID = AUCTIONITEM.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(AUCTIONITEM.TRANSACTIONCURRENCYID)
                        from dbo.AUCTIONITEM
                        inner join dbo.REVENUE on REVENUE.ID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
                        inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                        left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                        left join dbo.CURRENCY on CURRENCY.ID = AUCTIONITEM.BASECURRENCYID
                        left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = AUCTIONITEM.BASEEXCHANGERATEID
                        where AUCTIONITEM.ID = @ID;

                        select  @ADJUSTMENTDATE = getdate(),
                                @ADJUSTMENTPOSTDATE = getdate(),
                                @ADJUSTMENTPOSTSTATUSCODE = 0

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

                        -- 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 = @REVENUEAUCTIONDONATIONID

                        set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
                        if @ALLOWGLDISTRIBUTIONS = 0
                          set @ADJUSTMENTPOSTDATE = null;

                        return 0;