USP_AUCTIONITEM_ADD
Add an auction item.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @NAME | nvarchar(100) | IN | |
| @AUCTIONITEMCATEGORYID | uniqueidentifier | IN | |
| @AUCTIONITEMSUBCATEGORYID | uniqueidentifier | IN | |
| @DESCRIPTION | nvarchar(255) | IN | |
| @VALUE | money | IN | |
| @MINIMUMBID | money | IN | |
| @DONATIONDATE | date | IN | |
| @EXPIRATIONDATE | date | IN | |
| @DONORID | uniqueidentifier | IN | |
| @EVENTAUCTIONID | uniqueidentifier | IN | |
| @PACKAGEID | uniqueidentifier | IN | |
| @TYPECODE | int | IN | |
| @COPIEDFROMID | uniqueidentifier | IN | |
| @DESIGNATIONID | uniqueidentifier | IN | |
| @ISANONYMOUS | bit | IN | |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN | |
| @BATCHNUMBER | nvarchar(100) | IN | |
| @POSTDATE | datetime | IN | |
| @POSTSTATUSCODE | tinyint | IN | |
| @BASECURRENCYID | uniqueidentifier | IN | |
| @TRANSACTIONCURRENCYID | uniqueidentifier | IN | |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | |
| @EXCHANGERATE | decimal(20, 8) | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_AUCTIONITEM_ADD
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100) = '',
@AUCTIONITEMCATEGORYID uniqueidentifier = null,
@AUCTIONITEMSUBCATEGORYID uniqueidentifier = null,
@DESCRIPTION nvarchar(255) = '',
@VALUE money = null,
@MINIMUMBID money = 0,
@DONATIONDATE date = null,
@EXPIRATIONDATE date = null,
@DONORID uniqueidentifier = null,
@EVENTAUCTIONID uniqueidentifier = null,
@PACKAGEID uniqueidentifier = null,
@TYPECODE integer = null,
@COPIEDFROMID uniqueidentifier = null,
@DESIGNATIONID uniqueidentifier = null,
@ISANONYMOUS bit = null,
@PDACCOUNTSYSTEMID uniqueidentifier = null,
@BATCHNUMBER nvarchar(100) = '',
@POSTDATE datetime = null,
@POSTSTATUSCODE tinyint = 1,
@BASECURRENCYID uniqueidentifier = null,
@TRANSACTIONCURRENCYID uniqueidentifier = null,
@BASEEXCHANGERATEID uniqueidentifier = null,
@EXCHANGERATE decimal(20,8) = null,
@CHANGEDATE datetime = null
)
as
set nocount on;
if @ID is null
set @ID = newid();
if @ISANONYMOUS is null
set @ISANONYMOUS = 0;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
declare @EVENTAUCTIONBASECURRENCYID uniqueidentifier;
if @EVENTAUCTIONID is not null
select
@EVENTAUCTIONBASECURRENCYID = EVENT.BASECURRENCYID
from
dbo.EVENT
where
EVENT.ID = @EVENTAUCTIONID;
declare @PACKAGETRANSACTIONCURRENCYID uniqueidentifier;
if @PACKAGEID is not null
select @PACKAGETRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
from dbo.AUCTIONITEM
where ID = @PACKAGEID;
if @PDACCOUNTSYSTEMID is null
set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';
begin try
-- Check GL business rule for this account system and set to 'Do not post' if needed.
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
set @POSTSTATUSCODE = 2; -- Do not post
set @POSTDATE = null;
end
--Set currency parameters for backwards compatibility
if @TRANSACTIONCURRENCYID is null
set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
--Multicurrency - Retrieve base currency from the account system's currency set.
declare @CURRENCYSETID uniqueidentifier;
select @CURRENCYSETID = CURRENCYSETID from dbo.PDACCOUNTSYSTEM where ID = @PDACCOUNTSYSTEMID;
if @BASECURRENCYID is null
begin
select
@BASECURRENCYID = BASECURRENCYID
from
dbo.CURRENCYSET
where
ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET());
end
-- Item checks
if @TYPECODE = 0
begin
-- Ensure that we can add an item with this transaction currency to the account system.
if not exists
(
select 1
from
dbo.CURRENCYSETTRANSACTIONCURRENCY
inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSETTRANSACTIONCURRENCY.CURRENCYSETID
where
PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
and CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID = @TRANSACTIONCURRENCYID
)
begin
raiserror('TRANSACTIONCURRENCYINVALIDFORACCOUNTSYSTEM',13,1);
end
if @PACKAGEID is not null
begin
if (@PACKAGETRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID)
raiserror('BBERR_AUCTIONITEM_INVALIDTRANSACTIONCURRENCY_FORPACKAGE', 13, 1);
declare @PACKAGEACCOUNTSYSTEMID uniqueidentifier;
select top 1
@PACKAGEACCOUNTSYSTEMID = PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID
from
dbo.AUCTIONITEM
inner join dbo.PDACCOUNTSYSTEMFORREVENUE on AUCTIONITEM.REVENUEAUCTIONDONATIONID = PDACCOUNTSYSTEMFORREVENUE.ID
where
AUCTIONITEM.PACKAGEID = @PACKAGEID;
if (@PACKAGEACCOUNTSYSTEMID is not null) and (@PDACCOUNTSYSTEMID <> @PACKAGEACCOUNTSYSTEMID)
raiserror('BBERR_AUCTIONITEM_INVALIDPDACCOUNTSYSTEM_FORPACKAGE', 13, 1);
end
if @EVENTAUCTIONID is not null and (@EVENTAUCTIONBASECURRENCYID <> @TRANSACTIONCURRENCYID)
raiserror('BBERR_AUCTIONITEM_EVENT_BASECURRENCY', 13, 1);
end
-- Package checks
if @TYPECODE = 1
begin
if @EVENTAUCTIONID is null
raiserror('BBERR_AUCTIONITEM_PACKAGE_NOEVENT', 13, 1);
if @EVENTAUCTIONID is not null and (@EVENTAUCTIONBASECURRENCYID <> @TRANSACTIONCURRENCYID)
raiserror('BBERR_AUCTIONITEM_PACKAGE_INVALIDTRANSACTIONCURRENCY_FOREVENT', 13, 1);
--Packages don't post so they don't NEED a base currency; but it helps with backwards compatibility to have one.
--So set the base to be transaction
set @BASECURRENCYID = @TRANSACTIONCURRENCYID;
end
if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
begin
set @BASEEXCHANGERATEID = newid();
insert into dbo.CURRENCYEXCHANGERATE
(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@EXCHANGERATE,
coalesce(@DONATIONDATE, @CHANGEDATE),
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
declare @BASEVALUE money;
declare @BASEMINIMUMBID money;
declare @ORGANIZATIONVALUE money;
declare @ORGANIZATIONMINIMUMBID money;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @ORIGINTOORGANIZATIONEXCHANGERATEID uniqueidentifier;
-- Get base and organization values for the auction item value
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @VALUE, @DONATIONDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEVALUE output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONVALUE output, @ORIGINTOORGANIZATIONEXCHANGERATEID output, 1;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @MINIMUMBID, @DONATIONDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEMINIMUMBID output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONMINIMUMBID output, @ORIGINTOORGANIZATIONEXCHANGERATEID output, 1;
declare @REVENUEID uniqueidentifier;
declare @FINANCIALTRANSACTIONLINEITEMID uniqueidentifier;
declare @SITEID uniqueidentifier;
declare @CAMPAIGNS xml;
if @EVENTAUCTIONID is not null and exists(select 1 from dbo.EVENT where EVENT.ID = @EVENTAUCTIONID and EVENT.ISACTIVE = 0)
raiserror('BBERR_EVENTAUCTIONISINACTIVE',13,1);
if @VALUE is not null and @VALUE < 0
raiserror('CK_JOURNALENTRY_VALIDAMOUNT',13,1);
if (@TYPECODE = 0)
begin
set @REVENUEID = newid();
set @FINANCIALTRANSACTIONLINEITEMID = newid();
select @SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(@DESIGNATIONID);
insert into dbo.FINANCIALTRANSACTION
(
ID,
CONSTITUENTID,
DATE,
POSTDATE,
POSTSTATUSCODE,
TYPECODE,
BASEAMOUNT,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGAMOUNT,
ORGEXCHANGERATEID,
PDACCOUNTSYSTEMID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@REVENUEID,
@DONORID,
@DONATIONDATE,
@POSTDATE,
case @POSTSTATUSCODE when 2 then 3 else 1 end,
7,
@BASEVALUE,
@VALUE,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
@ORGANIZATIONVALUE,
@ORIGINTOORGANIZATIONEXCHANGERATEID,
@PDACCOUNTSYSTEMID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
insert into dbo.REVENUE_EXT
(
ID,
BATCHNUMBER,
DONOTRECEIPT,
RECEIPTAMOUNT,
GIVENANONYMOUSLY,
NONPOSTABLEBASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@REVENUEID,
@BATCHNUMBER,
0,
@VALUE,
@ISANONYMOUS,
@BASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
insert into dbo.FINANCIALTRANSACTIONLINEITEM
(
ID,
FINANCIALTRANSACTIONID,
BASEAMOUNT,
ORGAMOUNT,
TRANSACTIONAMOUNT,
POSTDATE,
POSTSTATUSCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@FINANCIALTRANSACTIONLINEITEMID,
@REVENUEID,
@BASEVALUE,
@ORGANIZATIONVALUE,
@VALUE,
@POSTDATE,
case @POSTSTATUSCODE when 2 then 3 else 1 end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
insert into dbo.REVENUESPLIT_EXT
(
ID,
DESIGNATIONID,
TYPECODE,
APPLICATIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@FINANCIALTRANSACTIONLINEITEMID,
@DESIGNATIONID,
0,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
insert into dbo.REVENUEPAYMENTMETHOD
(
ID,
REVENUEID,
PAYMENTMETHODCODE,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
newid(),
@REVENUEID,
9,
@BASEVALUE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
insert into dbo.REVENUEAUCTIONDONATION
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@REVENUEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
if exists
(
select 1 from dbo.REVENUESPLIT_EXT
inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and DESIGNATION.ISACTIVE = 0
)
begin
raiserror('Revenue cannot be added to inactive designations.', 13, 1);
end
--Campaigns
select
@CAMPAIGNS = (select -- campaigns for designation
CAMPAIGNID,
CAMPAIGNSUBPRIORITYID
from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(@DESIGNATIONID, @DONATIONDATE)
for xml raw('ITEM'), type, elements, root('CAMPAIGNS'), BINARY BASE64);
if @DESIGNATIONID is not null and @CAMPAIGNS is not null
exec dbo.USP_REVENUESPLIT_GETCAMPAIGNS_ADDFROMXML @FINANCIALTRANSACTIONLINEITEMID, @CAMPAIGNS, @CHANGEAGENTID;
--Recognition credits
insert into dbo.REVENUERECOGNITION
(
REVENUESPLITID,
CONSTITUENTID,
AMOUNT,
EFFECTIVEDATE,
REVENUERECOGNITIONTYPECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID
)
select
FINANCIALTRANSACTIONLINEITEM.ID,
RECOGNITIONS.CONSTITUENTID,
RECOGNITIONS.AMOUNT,
@DONATIONDATE,
RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@BASECURRENCYID,
case
when @BASECURRENCYID = @ORGANIZATIONCURRENCYID then
RECOGNITIONS.AMOUNT
else
dbo.UFN_CURRENCY_CONVERT(RECOGNITIONS.AMOUNT, @ORIGINTOORGANIZATIONEXCHANGERATEID)
end,
@ORIGINTOORGANIZATIONEXCHANGERATEID
from
dbo.FINANCIALTRANSACTIONLINEITEM
cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(@ISANONYMOUS, @DONORID, FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT, @DONATIONDATE, null) as RECOGNITIONS
where
FINANCIALTRANSACTIONLINEITEM.ID = @FINANCIALTRANSACTIONLINEITEMID;
exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
@ID = @REVENUEID,
@PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
if @POSTSTATUSCODE <> 2
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CHANGEDATE;
end
insert into dbo.AUCTIONITEM
(
ID,
NAME,
AUCTIONITEMCATEGORYID,
AUCTIONITEMSUBCATEGORYID,
DESCRIPTION,
VALUE,
MINIMUMBID,
EXPIRATIONDATE,
REVENUEAUCTIONDONATIONID,
EVENTAUCTIONID,
PACKAGEID,
TYPECODE,
COPIEDFROMID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
TRANSACTIONVALUE,
ORGANIZATIONVALUE,
TRANSACTIONMINIMUMBID,
ORGANIZATIONMINIMUMBID,
BASECURRENCYID,
BASEEXCHANGERATEID,
BASETOORGANIZATIONEXCHANGERATEID,
ORIGINTOORGANIZATIONEXCHANGERATEID,
TRANSACTIONCURRENCYID
)
values
(
@ID,
@NAME,
@AUCTIONITEMCATEGORYID,
@AUCTIONITEMSUBCATEGORYID,
@DESCRIPTION,
case
when @TYPECODE = 0 then
@BASEVALUE
else
0
end,
@BASEMINIMUMBID,
@EXPIRATIONDATE,
@REVENUEID,
@EVENTAUCTIONID,
@PACKAGEID,
@TYPECODE,
@COPIEDFROMID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@VALUE,
case
when @TYPECODE = 0 then
@ORGANIZATIONVALUE
else
0
end,
@MINIMUMBID,
@ORGANIZATIONMINIMUMBID,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
null,
@ORIGINTOORGANIZATIONEXCHANGERATEID,
@TRANSACTIONCURRENCYID
);
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;