USP_DATAFORMTEMPLATE_VIEW_AUCTIONITEMSDETAIL

The load procedure used by the view dataform template "Auction Items Detail View 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.
@DESCRIPTION	nvarchar(255)	INOUT	Description
@CATEGORY	nvarchar(100)	INOUT	Category
@SUBCATEGORY	nvarchar(100)	INOUT	Subcategory
@VALUE	money	INOUT	Value
@AUCTION	nvarchar(100)	INOUT	Auction
@DONOR	nvarchar(154)	INOUT	Donor
@DONATIONDATE	datetime	INOUT	Donation date
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@MINIMUMBID	money	INOUT	Minimum bid
@DESIGNATION	nvarchar(512)	INOUT	Designation
@PURCHASER	nvarchar(154)	INOUT	Purchaser
@TRANSACTIONDATE	date	INOUT	Transaction date
@PURCHASEAMOUNT	money	INOUT	Purchase price
@TAXAMOUNT	money	INOUT	Receipt amount
@TYPECODE	tinyint	INOUT	Auction item type
@TOTALITEMS	int	INOUT	Total items
@ISRESERVED	bit	INOUT	ISRESERVED

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_AUCTIONITEMSDETAIL
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @DESCRIPTION nvarchar(255) = null output,
                    @CATEGORY nvarchar(100) = null output,
                    @SUBCATEGORY nvarchar(100) = null output,
                    @VALUE money = null output,
                    @AUCTION nvarchar(100) = null output,
                    @DONOR nvarchar(154) = null output,
                    @DONATIONDATE datetime = null output,
                    @EXPIRATIONDATE datetime = null output,
                    @MINIMUMBID money = null output,
                    @DESIGNATION nvarchar(512) = null output,
                    @PURCHASER nvarchar(154) = null output,
                    @TRANSACTIONDATE date = null output,
                    @PURCHASEAMOUNT money = null output,
                    @TAXAMOUNT money = null output,
                    @TYPECODE tinyint = null output,
                    @TOTALITEMS int = null output,
                    @ISRESERVED bit = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    select 
                        @DATALOADED = 1,
                        @DESCRIPTION = AUCTIONITEM.DESCRIPTION,
                        @CATEGORY = [CATEGORY].NAME,
                        @SUBCATEGORY = [SUBCATEGORY].NAME,
                        @VALUE = case
                                    when AUCTIONITEM.TYPECODE = 0 then 
                                        AUCTIONITEM.VALUE
                                    else 
                                        coalesce((select sum(VALUE) from dbo.AUCTIONITEM [ITEM] where [ITEM].PACKAGEID = AUCTIONITEM.ID),0)
                                    end,
                        @AUCTION = EVENT.NAME,
                        @DONOR = dbo.UFN_CONSTITUENT_BUILDNAME([DONATION].CONSTITUENTID),
                        @DONATIONDATE = [DONATION].DATE,
                        @EXPIRATIONDATE = AUCTIONITEM.EXPIRATIONDATE,
                        @MINIMUMBID = AUCTIONITEM.MINIMUMBID,
                        @DESIGNATION = DESIGNATION.NAME,
                        @PURCHASER = case 
                                         when AUCTIONITEMRESERVATION.PURCHASERID is not null then dbo.UFN_CONSTITUENT_BUILDNAME(AUCTIONITEMRESERVATION.PURCHASERID)
                                         when [RESERVEDPACKAGEITEM].PURCHASERID is not null then dbo.UFN_CONSTITUENT_BUILDNAME([RESERVEDPACKAGEITEM].PURCHASERID)
                                     end,
                        @PURCHASEAMOUNT = case 
                                               when AUCTIONITEMRESERVATION.PURCHASERID is not null then AUCTIONITEMRESERVATION.PURCHASEAMOUNT
                                               else null
                                        end,
                        @TAXAMOUNT = case
                              when (AUCTIONITEM.TYPECODE = 0) and (AUCTIONITEMRESERVATION.ID is not null) and (AUCTIONITEMRESERVATION.PURCHASEAMOUNT - AUCTIONITEM.VALUE > 0) then AUCTIONITEMRESERVATION.PURCHASEAMOUNT - AUCTIONITEM.VALUE 
                              when (AUCTIONITEM.TYPECODE = 1) and (AUCTIONITEMRESERVATION.ID is not null) and (AUCTIONITEMRESERVATION.PURCHASEAMOUNT - dbo.UFN_AUCTIONITEM_GETVALUE(AUCTIONITEM.ID) > 0) then AUCTIONITEMRESERVATION.PURCHASEAMOUNT - dbo.UFN_AUCTIONITEM_GETVALUE(AUCTIONITEM.ID) 
                              else 0
                            end,
                        @TYPECODE = AUCTIONITEM.TYPECODE,
                        @TOTALITEMS = case
                                        when AUCTIONITEM.TYPECODE = 0 then
                                            0
                                        else
                                            coalesce((select count(ID) from dbo.AUCTIONITEM [ITEM] where [ITEM].PACKAGEID = AUCTIONITEM.ID),0)
                                        end,
                        @ISRESERVED = case
                                        when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEM.PACKAGEID is null and AUCTIONITEMRESERVATION.ID is null then 0
                                        when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEM.PACKAGEID is not null and [RESERVEDPACKAGEITEM].ID is null then 0
                                        when AUCTIONITEM.TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is null then 0
                                        else 1
                                      end
                    from 
                        dbo.AUCTIONITEM
                        left join dbo.AUCTIONITEMCATEGORY [CATEGORY] on AUCTIONITEM.AUCTIONITEMCATEGORYID = CATEGORY.ID
                        left join dbo.AUCTIONITEMSUBCATEGORY [SUBCATEGORY] on AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = SUBCATEGORY.ID
                        left join dbo.EVENT on AUCTIONITEM.EVENTAUCTIONID = EVENT.ID
                        left join dbo.REVENUE [DONATION] on AUCTIONITEM.REVENUEAUCTIONDONATIONID = [DONATION].ID
                        left join dbo.REVENUESPLIT [DONATIONSPLIT] on AUCTIONITEM.REVENUEAUCTIONDONATIONID = DONATIONSPLIT.REVENUEID
                        left join dbo.DESIGNATION on DONATIONSPLIT.DESIGNATIONID = DESIGNATION.ID
                        left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEMRESERVATION.AUCTIONITEMID = AUCTIONITEM.ID
                        left join dbo.AUCTIONITEMRESERVATION [RESERVEDPACKAGEITEM] on [RESERVEDPACKAGEITEM].AUCTIONITEMID = AUCTIONITEM.PACKAGEID
                    where
                        AUCTIONITEM.ID = @ID

                    return 0;