USP_DATAFORMTEMPLATE_VIEW_AUCTIONITEMPROFILE

The load procedure used by the view dataform template "Auction Item Summary 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.
@NAME	nvarchar(100)	INOUT	Name
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@PACKAGEID	uniqueidentifier	INOUT	Package ID
@PACKAGE	nvarchar(100)	INOUT	Package
@DESIGNATIONID	uniqueidentifier	INOUT	Designation ID
@DESIGNATION	nvarchar(100)	INOUT	Designation
@EVENTAUCTIONID	uniqueidentifier	INOUT	Auction ID
@EVENTAUCTION	nvarchar(100)	INOUT	Auction
@DESCRIPTION	nvarchar(255)	INOUT	Description
@AUCTIONITEMCATEGORYID	uniqueidentifier	INOUT	Category ID
@AUCTIONITEMCATEGORY	nvarchar(100)	INOUT	Category
@AUCTIONITEMSUBCATEGORYID	uniqueidentifier	INOUT	Subcategory ID
@AUCTIONITEMSUBCATEGORY	nvarchar(100)	INOUT	Subcategory
@COPIEDFROMID	uniqueidentifier	INOUT	Copied from ID
@COPIEDFROM	nvarchar(100)	INOUT	Copied from
@REVENUEAUCTIONDONATIONID	uniqueidentifier	INOUT	Revenue ID
@DONORID	uniqueidentifier	INOUT	Donor ID
@DONOR	nvarchar(100)	INOUT	Donor name
@DONATIONDATE	date	INOUT	Donation date
@EXPIRATIONDATE	date	INOUT	Expiration date
@VALUE	money	INOUT	Value
@MINIMUMBID	money	INOUT	Minimum bid
@PURCHASEID	uniqueidentifier	INOUT	Purchase ID
@PURCHASERID	uniqueidentifier	INOUT	Purchaser ID
@PURCHASER	nvarchar(100)	INOUT	Purchaser
@TRANSACTIONDATE	date	INOUT	Transaction date
@PURCHASEAMOUNT	money	INOUT	Purchase price
@TAXAMOUNT	money	INOUT	Receipt amount
@ISANONYMOUS	bit	INOUT	Donated anonymously
@ISRESERVED	bit	INOUT	ISRESERVED
@ISPURCHASED	bit	INOUT	ISPURCHASED
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@BASEVALUE	money	INOUT	Base value
@PURCHASEBASECURRENCYID	uniqueidentifier	INOUT	Purchase base currency ID
@PURCHASETRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Purchase transaction currency ID

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_AUCTIONITEMPROFILE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(100) = null output,
                    @LOOKUPID nvarchar(100) = null output,
                    @PACKAGEID uniqueidentifier = null output,
                    @PACKAGE nvarchar(100) = null output,
                    @DESIGNATIONID uniqueidentifier = null output,
                    @DESIGNATION nvarchar(100) = null output,
                    @EVENTAUCTIONID uniqueidentifier = null output,
                    @EVENTAUCTION nvarchar(100) = null output,
                    @DESCRIPTION nvarchar(255) = null output,
                    @AUCTIONITEMCATEGORYID uniqueidentifier = null output,
                    @AUCTIONITEMCATEGORY nvarchar(100) = null output,
                    @AUCTIONITEMSUBCATEGORYID uniqueidentifier = null output,
                    @AUCTIONITEMSUBCATEGORY nvarchar(100) = null output,
                    @COPIEDFROMID uniqueidentifier = null output,
                    @COPIEDFROM nvarchar(100) = null output,
                    @REVENUEAUCTIONDONATIONID uniqueidentifier = null output,
                    @DONORID uniqueidentifier = null output,
                    @DONOR nvarchar(100) = null output,
                    @DONATIONDATE date = null output,
                    @EXPIRATIONDATE date = null output,
                    @VALUE money = null output,
                    @MINIMUMBID money = null output,
                    @PURCHASEID uniqueidentifier = null output,
                    @PURCHASERID uniqueidentifier = null output,
                    @PURCHASER nvarchar(100) = null output,
                    @TRANSACTIONDATE date = null output,
                    @PURCHASEAMOUNT money = null output,
                    @TAXAMOUNT money = null output,
                    @ISANONYMOUS bit = null output,
                    @ISRESERVED bit = null output,
                    @ISPURCHASED bit = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @BASEVALUE money = null output,
                    @PURCHASEBASECURRENCYID uniqueidentifier = null output,
                    @PURCHASETRANSACTIONCURRENCYID uniqueidentifier = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    select @DATALOADED = 1,
                           @LOOKUPID = AUCTIONITEM.LOOKUPID,
                           @NAME = AUCTIONITEM.NAME,
                           @DESCRIPTION = AUCTIONITEM.DESCRIPTION,
                           @AUCTIONITEMCATEGORYID = AUCTIONITEM.AUCTIONITEMCATEGORYID,
                           @AUCTIONITEMCATEGORY = AUCTIONITEMCATEGORY.NAME,
                           @AUCTIONITEMSUBCATEGORYID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID,
                           @AUCTIONITEMSUBCATEGORY = AUCTIONITEMSUBCATEGORY.NAME,
                           @VALUE = AUCTIONITEM.TRANSACTIONVALUE,
                           @TAXAMOUNT = case
                                          when (AUCTIONITEMPURCHASE.PURCHASEID is not null) and (ITEMPURCHASEPRICES.TRANSACTIONPURCHASEPRICE - AUCTIONITEM.TRANSACTIONVALUE > 0) then ITEMPURCHASEPRICES.TRANSACTIONPURCHASEPRICE - AUCTIONITEM.TRANSACTIONVALUE
                                          when (RESERVATION.PURCHASERID is not null) and (RESERVATION.PURCHASEAMOUNT - AUCTIONITEM.TRANSACTIONVALUE > 0) then RESERVATION.PURCHASEAMOUNT - AUCTIONITEM.TRANSACTIONVALUE else 0
                                        end,
                           @MINIMUMBID = AUCTIONITEM.TRANSACTIONMINIMUMBID,
                           @DONATIONDATE = DONATION.DATE,
                           @EXPIRATIONDATE = AUCTIONITEM.EXPIRATIONDATE,
                           @REVENUEAUCTIONDONATIONID = AUCTIONITEM.REVENUEAUCTIONDONATIONID,
                           @DONORID = DONATION.CONSTITUENTID,
                           @DONOR = dbo.UFN_CONSTITUENT_BUILDNAME(DONATION.CONSTITUENTID),
                           @EVENTAUCTIONID = AUCTIONITEM.EVENTAUCTIONID,
                           @EVENTAUCTION = EVENT.NAME,
                           @PACKAGEID = AUCTIONITEM.PACKAGEID,
                           @PACKAGE = PACKAGE.NAME,
                           @COPIEDFROMID = AUCTIONITEM.COPIEDFROMID,
                           @COPIEDFROM = COPIEDFROM.NAME,
                           @DESIGNATIONID = REVENUESPLIT.DESIGNATIONID,
                           @DESIGNATION = DESIGNATION.NAME,
                           @PURCHASEID = AUCTIONITEMPURCHASE.PURCHASEID,
                           @PURCHASERID = case 
                                            when AUCTIONITEMPURCHASE.PURCHASEID is not null then PURCHASE.CONSTITUENTID
                                            when RESERVATION.PURCHASERID is not null then RESERVATION.PURCHASERID
                                            when [RESERVEDPACKAGEITEM].PURCHASERID is not null then [RESERVEDPACKAGEITEM].PURCHASERID
                                          end,
                           @PURCHASER = case 
                                          when AUCTIONITEMPURCHASE.PURCHASEID is not null then dbo.UFN_CONSTITUENT_BUILDNAME(PURCHASE.CONSTITUENTID)
                                          when RESERVATION.PURCHASERID is not null then dbo.UFN_CONSTITUENT_BUILDNAME(RESERVATION.PURCHASERID)
                                          when [RESERVEDPACKAGEITEM].PURCHASERID is not null then dbo.UFN_CONSTITUENT_BUILDNAME([RESERVEDPACKAGEITEM].PURCHASERID)
                                        end,
                           @PURCHASEAMOUNT = case 
                                               when AUCTIONITEMPURCHASE.PURCHASEID is not null then ITEMPURCHASEPRICES.TRANSACTIONPURCHASEPRICE
                                               else dbo.UFN_AUCTIONITEM_GETRESERVATIONAMOUNT(AUCTIONITEM.ID)
                                        end,
                           @TRANSACTIONDATE = case 
                                                when AUCTIONITEMPURCHASE.PURCHASEID is not null then PURCHASE.DATE
                                              end,
                           @ISANONYMOUS = DONATION.GIVENANONYMOUSLY,
                           @ISRESERVED = case
                                           when (RESERVATION.ID is not null) OR ([RESERVEDPACKAGEITEM].ID is not null) then 1
                                           else 0
                                         end,
                           @ISPURCHASED = case
                                            when AUCTIONITEMPURCHASE.PURCHASEID is not null then 1
                                            else 0
                                          end,
                           @BASECURRENCYID = DONATION.BASECURRENCYID,
                           @TRANSACTIONCURRENCYID = DONATION.TRANSACTIONCURRENCYID,
                           @BASEVALUE = AUCTIONITEM.VALUE,
                           @PURCHASEBASECURRENCYID = coalesce(PURCHASE.BASECURRENCYID,DONATION.BASECURRENCYID),
                           @PURCHASETRANSACTIONCURRENCYID = coalesce(PURCHASE.TRANSACTIONCURRENCYID,DONATION.TRANSACTIONCURRENCYID)
                    from dbo.AUCTIONITEM
                    left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                    left join dbo.AUCTIONITEMCATEGORY on AUCTIONITEMCATEGORY.ID = AUCTIONITEM.AUCTIONITEMCATEGORYID
                    left join dbo.AUCTIONITEMSUBCATEGORY on AUCTIONITEMSUBCATEGORY.ID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID
                    left join dbo.EVENT on EVENT.ID = AUCTIONITEM.EVENTAUCTIONID
                    left join dbo.AUCTIONITEM as PACKAGE on PACKAGE.ID = AUCTIONITEM.PACKAGEID
                    left join dbo.AUCTIONITEM as COPIEDFROM on COPIEDFROM.ID = AUCTIONITEM.COPIEDFROMID
                    inner join dbo.REVENUE as DONATION on DONATION.ID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = DONATION.ID
                    inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
                    left join dbo.REVENUESPLIT as PURCHASESPLIT on PURCHASESPLIT.ID = AUCTIONITEMPURCHASE.PURCHASEID
                    left join dbo.REVENUE as PURCHASE on PURCHASE.ID = PURCHASESPLIT.REVENUEID
                    left join dbo.AUCTIONITEMRESERVATION as RESERVATION on RESERVATION.AUCTIONITEMID = @ID
                    left join dbo.AUCTIONITEMRESERVATION [RESERVEDPACKAGEITEM] on [RESERVEDPACKAGEITEM].AUCTIONITEMID = AUCTIONITEM.PACKAGEID
                    outer apply dbo.UFN_AUCTIONITEM_GETVALUES_INCURRENCY(AUCTIONITEM.ID) [ITEMVALUES]
                    outer apply dbo.UFN_AUCTIONITEM_GETPURCHASEPRICES_INCURRENCY(AUCTIONITEM.ID) [ITEMPURCHASEPRICES]
                    where AUCTIONITEM.ID = @ID

                    return 0;