USP_DATAFORMTEMPLATE_VIEW_AUCTIONITEMSDETAIL_2
The load procedure used by the view dataform template "Auction Items Detail View Form 2"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(38) | 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 |
| @VALUECURRENCYID | uniqueidentifier | INOUT | Value currency |
| @MINIMUMBIDCURRENCYID | uniqueidentifier | INOUT | Minimum bid currency |
| @PURCHASECURRENCYID | uniqueidentifier | INOUT | Purchase transaction currency |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_AUCTIONITEMSDETAIL_2
(
@ID nvarchar(38),
@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,
@VALUECURRENCYID uniqueidentifier = null output,
@MINIMUMBIDCURRENCYID uniqueidentifier = null output,
@PURCHASECURRENCYID uniqueidentifier = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @AUCTIONITEMID uniqueidentifier = cast(right(@ID, 36) as uniqueidentifier);
declare @DISPLAYCURRENCYTYPECODE tinyint = cast(left(@ID, 1) as tinyint);
select
@DATALOADED = 1,
@DESCRIPTION = AUCTIONITEM.DESCRIPTION,
@CATEGORY = [CATEGORY].NAME,
@SUBCATEGORY = [SUBCATEGORY].NAME,
@VALUE = case
when AUCTIONITEM.TYPECODE = 0 then
case when @DISPLAYCURRENCYTYPECODE = 0 then AUCTIONITEM.TRANSACTIONVALUE
when @DISPLAYCURRENCYTYPECODE = 2 then AUCTIONITEM.ORGANIZATIONVALUE
else AUCTIONITEM.VALUE
end
else
coalesce((
select
case when @DISPLAYCURRENCYTYPECODE = 0 then sum([ITEM].TRANSACTIONVALUE)
when @DISPLAYCURRENCYTYPECODE = 2 then sum([ITEM].ORGANIZATIONVALUE)
else sum([ITEM].VALUE)
end
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 = case when @DISPLAYCURRENCYTYPECODE = 0 then AUCTIONITEM.TRANSACTIONMINIMUMBID
when @DISPLAYCURRENCYTYPECODE = 2 then AUCTIONITEM.ORGANIZATIONMINIMUMBID
else AUCTIONITEM.MINIMUMBID
end,
@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, --TODO
@TAXAMOUNT = case
when (AUCTIONITEM.TYPECODE = 0) and (AUCTIONITEMRESERVATION.ID is not null) and (AUCTIONITEMRESERVATION.PURCHASEAMOUNT - AUCTIONITEM.TRANSACTIONVALUE > 0) then AUCTIONITEMRESERVATION.PURCHASEAMOUNT - AUCTIONITEM.TRANSACTIONVALUE
when (AUCTIONITEM.TYPECODE = 1) and (AUCTIONITEMRESERVATION.ID is not null) and (AUCTIONITEMRESERVATION.PURCHASEAMOUNT - [ITEMVALUES].TRANSACTIONVALUE > 0) then AUCTIONITEMRESERVATION.PURCHASEAMOUNT - [ITEMVALUES].TRANSACTIONVALUE
else 0
end, --TODO
@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,
@VALUECURRENCYID =
case
when AUCTIONITEM.TYPECODE = 0 then
case when @DISPLAYCURRENCYTYPECODE = 0 then AUCTIONITEM.TRANSACTIONCURRENCYID
when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
else AUCTIONITEM.BASECURRENCYID
end
else
coalesce((
select top 1
case when @DISPLAYCURRENCYTYPECODE = 0 then [ITEM].TRANSACTIONCURRENCYID
when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
else [ITEM].BASECURRENCYID
end
from dbo.AUCTIONITEM [ITEM]
where [ITEM].PACKAGEID = AUCTIONITEM.ID
), case when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() else EVENT.BASECURRENCYID end)
end,
@MINIMUMBIDCURRENCYID =
case
when AUCTIONITEM.TYPECODE = 0 then
case when @DISPLAYCURRENCYTYPECODE = 0 then AUCTIONITEM.TRANSACTIONCURRENCYID
when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
else AUCTIONITEM.BASECURRENCYID
end
else
coalesce((
select top 1
case when @DISPLAYCURRENCYTYPECODE = 0 then [ITEM].TRANSACTIONCURRENCYID
when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
else [ITEM].BASECURRENCYID
end
from dbo.AUCTIONITEM [ITEM]
where [ITEM].PACKAGEID = AUCTIONITEM.ID
), case when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() else EVENT.BASECURRENCYID end)
end,
@PURCHASECURRENCYID =
case
when @DISPLAYCURRENCYTYPECODE = 0 then AUCTIONITEM.TRANSACTIONCURRENCYID
when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
else AUCTIONITEM.BASECURRENCYID
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
outer apply dbo.UFN_AUCTIONITEM_GETVALUES_INCURRENCY(AUCTIONITEM.ID) [ITEMVALUES]
where
AUCTIONITEM.ID = @AUCTIONITEMID;
return 0;