USP_DATAFORMTEMPLATE_VIEW_AUCTIONPACKAGESUMMARY

The load procedure used by the view dataform template "Auction Package Summary View"

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.
@TOTALITEMS	int	INOUT	Total items
@TOTALVALUE	money	INOUT	Total value
@DESCRIPTION	nvarchar(255)	INOUT	Description
@CATEGORYNAME	nvarchar(100)	INOUT	Category
@SUBCATEGORYNAME	nvarchar(100)	INOUT	Subcategory
@MINIMUMBID	money	INOUT	Minimum bid
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@AUCTIONNAME	nvarchar(100)	INOUT	Auction
@SITES	nvarchar(500)	INOUT	Site
@PURCHASER	nvarchar(154)	INOUT	Purchaser
@TRANSACTIONDATE	date	INOUT	Transaction date
@PURCHASEAMOUNT	money	INOUT	Purchase price
@TAXAMOUNT	money	INOUT	Receipt amount
@ISRESERVED	bit	INOUT	ISRESERVED
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@PURCHASETRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Purchase transaction currency

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_AUCTIONPACKAGESUMMARY
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @TOTALITEMS int = null output,
                    @TOTALVALUE money = null output,
                    @DESCRIPTION nvarchar(255) = null output,
                    @CATEGORYNAME nvarchar(100) = null output,
                    @SUBCATEGORYNAME nvarchar(100) = null output,
                    @MINIMUMBID money = null output,
                    @EXPIRATIONDATE datetime = null output,
                    @AUCTIONNAME nvarchar(100) = null output,
                    @SITES nvarchar(500) = null output,
                    @PURCHASER nvarchar(154) = null output,
                    @TRANSACTIONDATE date = null output,
                    @PURCHASEAMOUNT money = null output,
                    @TAXAMOUNT money = null output,
                    @ISRESERVED bit = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @PURCHASETRANSACTIONCURRENCYID uniqueidentifier = null output
                )
                as
                    set nocount on;

                    -- be sure to set this, in case the select returns no rows

                    set @DATALOADED = 0;

                    select
                        @TOTALITEMS = count(AUCTIONITEM.ID),
                        @TOTALVALUE = sum(AUCTIONITEM.TRANSACTIONVALUE)
                    from dbo.AUCTIONITEM
                    where PACKAGEID = @ID

                    select 
                        @DATALOADED = 1,
                            @DESCRIPTION = AUCTIONITEM.DESCRIPTION,
                        @CATEGORYNAME = [CATEGORY].NAME,
                        @SUBCATEGORYNAME = [SUBCATEGORY].NAME,
                        @MINIMUMBID = AUCTIONITEM.TRANSACTIONMINIMUMBID,
                        @EXPIRATIONDATE = AUCTIONITEM.EXPIRATIONDATE,
                        @AUCTIONNAME = EVENT.NAME,
                        @SITES = dbo.UFN_AUCTIONPACKAGE_GETSITELIST(AUCTIONITEM.ID),
                                    @PURCHASER = case 
                             when AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null then dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID)
                             when AUCTIONITEMRESERVATION.PURCHASERID is not null then dbo.UFN_CONSTITUENT_BUILDNAME(AUCTIONITEMRESERVATION.PURCHASERID)
                         end,
                        @PURCHASEAMOUNT = case 
                            when AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null then [PURCHASEPRICES].TRANSACTIONPURCHASEPRICE
                            when AUCTIONITEMRESERVATION.PURCHASERID is not null then AUCTIONITEMRESERVATION.PURCHASEAMOUNT
                         end,
                        @TRANSACTIONDATE = case 
                                                when AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null then REVENUE.DATE
                                              end,
                        @TAXAMOUNT = case
                                              when (AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null) and ([PURCHASEPRICES].TRANSACTIONPURCHASEPRICE - [ITEMVALUES].TRANSACTIONVALUE > 0) then [PURCHASEPRICES].TRANSACTIONPURCHASEPRICE - [ITEMVALUES].TRANSACTIONVALUE
                                              when (AUCTIONITEMRESERVATION.ID is not null) and (AUCTIONITEMRESERVATION.PURCHASEAMOUNT - [ITEMVALUES].TRANSACTIONVALUE > 0) then AUCTIONITEMRESERVATION.PURCHASEAMOUNT - [ITEMVALUES].TRANSACTIONVALUE 
                                          else 0
                                        end,
                        @ISRESERVED = case
                                        when AUCTIONITEMRESERVATION.ID is null then
                                            0
                                        else
                                            1
                                      end,
                        @BASECURRENCYID = AUCTIONITEM.BASECURRENCYID,
                        @TRANSACTIONCURRENCYID = AUCTIONITEM.TRANSACTIONCURRENCYID,
                        @PURCHASETRANSACTIONCURRENCYID = coalesce(REVENUE.TRANSACTIONCURRENCYID,AUCTIONITEM.TRANSACTIONCURRENCYID)
                    from 
                        dbo.AUCTIONITEM
                        left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
                        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 on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = REVENUE.ID
                        left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEM.ID = AUCTIONITEMRESERVATION.AUCTIONITEMID
                        outer apply dbo.UFN_AUCTIONITEM_GETVALUES_INCURRENCY(AUCTIONITEM.ID) [ITEMVALUES]
                        outer apply dbo.UFN_AUCTIONITEM_GETPURCHASEPRICES_INCURRENCY(AUCTIONITEM.ID) [PURCHASEPRICES]
                    where AUCTIONITEM.ID = @ID

                    return 0;