USP_DATAFORMTEMPLATE_EDIT_AUCTIONITEM_2
The save procedure used by the edit dataform template "Auction Item Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @NAME | nvarchar(100) | IN | Name |
| @DESIGNATIONID | uniqueidentifier | IN | Designation |
| @EVENTAUCTIONID | uniqueidentifier | IN | Auction |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @AUCTIONITEMCATEGORYID | uniqueidentifier | IN | Category |
| @AUCTIONITEMSUBCATEGORYID | uniqueidentifier | IN | Subcategory |
| @DONORID | uniqueidentifier | IN | Donor |
| @DONATIONDATE | date | IN | Donation date |
| @EXPIRATIONDATE | date | IN | Expiration date |
| @VALUE | money | IN | Value |
| @MINIMUMBID | money | IN | Minimum bid |
| @REVENUEAUCTIONDONATIONID | uniqueidentifier | IN | Revenue ID |
| @ISANONYMOUS | bit | IN | Donation is anonymous |
| @ISPOSTED | bit | IN | Is posted |
| @POSTDATE | datetime | IN | Post date |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_AUCTIONITEM_2 (
@ID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@NAME nvarchar(100),
@DESIGNATIONID uniqueidentifier,
@EVENTAUCTIONID uniqueidentifier,
@DESCRIPTION nvarchar(255),
@AUCTIONITEMCATEGORYID uniqueidentifier,
@AUCTIONITEMSUBCATEGORYID uniqueidentifier,
@DONORID uniqueidentifier,
@DONATIONDATE date,
@EXPIRATIONDATE date,
@VALUE money,
@MINIMUMBID money,
@REVENUEAUCTIONDONATIONID uniqueidentifier,
@ISANONYMOUS bit,
@ISPOSTED bit,
@POSTDATE datetime,
@POSTSTATUSCODE tinyint,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8)
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
if @ISANONYMOUS is null
set @ISANONYMOUS = 0;
declare @CLEARGLDISTRIBUTION bit = 0;
declare @DONOTPOST bit;
set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;
declare @ORGANIZATIONVALUE money;
declare @ORGANIZATIONMINIMUMBID money;
declare @BASEVALUE money;
declare @BASEMINIMUMBID money;
declare @BASECURRENCYID uniqueidentifier;
declare @ORIGINTOORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @PREVIOUSDATE datetime;
declare @OLDSPOTRATEID uniqueidentifier;
begin try
if @ISPOSTED = 1
raiserror('This form should not be used to edit posted auction items.', 13, 1);
-- Check GL business rule for this account system and set to 'Do not post' if needed.
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID=PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE
inner join dbo.AUCTIONITEM on AUCTIONITEM.REVENUEAUCTIONDONATIONID = PDACCOUNTSYSTEMFORREVENUE.ID
where AUCTIONITEM.ID = @ID
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
set @POSTSTATUSCODE = 2 -- Do not post
set @DONOTPOST = 1
set @POSTDATE = null
end
if @EXPIRATIONDATE < @DONATIONDATE
raiserror('The expiration date must be on or after the donation date.', 13, 1);
declare @PREVIOUSEVENTAUCTIONID uniqueidentifier;
select @PREVIOUSEVENTAUCTIONID = EVENTAUCTIONID from dbo.AUCTIONITEM where ID = @ID
if (((@PREVIOUSEVENTAUCTIONID is null) and (@EVENTAUCTIONID is not null)) or ((@PREVIOUSEVENTAUCTIONID is not null) and (@EVENTAUCTIONID is not null) and (@PREVIOUSEVENTAUCTIONID <> @EVENTAUCTIONID))) and exists(select 1 from dbo.EVENT where EVENT.ID = @EVENTAUCTIONID and EVENT.ISACTIVE = 0)
raiserror('BBERR_EVENTAUCTIONISINACTIVE',13,1);
--Do not allow written off auction items to be added to auctions
if @PREVIOUSEVENTAUCTIONID is null and @EVENTAUCTIONID is not null and exists(select 1 from dbo.FINANCIALTRANSACTION where FINANCIALTRANSACTION.PARENTID = @REVENUEAUCTIONDONATIONID and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTION.TYPECODE = 20)
raiserror('BBERR_AUCTIONITEM_WRITTENOFF',13,1);
declare @PREVIOUSDONORID uniqueidentifier;
declare @WASANONYMOUS bit;
select
@PREVIOUSDONORID = REVENUE.CONSTITUENTID,
@WASANONYMOUS = REVENUE.GIVENANONYMOUSLY,
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = REVENUE.BASECURRENCYID,
@PREVIOUSDATE = REVENUE.DATE,
@OLDSPOTRATEID =
case
when CURRENCYEXCHANGERATE.TYPECODE = 2
and not (@BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID or (@BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001' and @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE))
then CURRENCYEXCHANGERATE.ID
else
null
end,
@ORIGINTOORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID
from
dbo.REVENUE
left join dbo.CURRENCYEXCHANGERATE on REVENUE.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where
REVENUE.ID = @REVENUEAUCTIONDONATIONID;
declare @EVENTAUCTIONBASECURRENCYID uniqueidentifier;
if @EVENTAUCTIONID is not null
begin
select
@EVENTAUCTIONBASECURRENCYID = EVENT.BASECURRENCYID
from dbo.EVENT
where EVENT.ID = @EVENTAUCTIONID
if (@EVENTAUCTIONBASECURRENCYID <> @TRANSACTIONCURRENCYID)
raiserror('BBERR_AUCTIONITEM_EVENT_BASECURRENCY', 13, 1);
end
--If the record uses a new spot rate, create it and set the rate ID.
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,
@DONATIONDATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
declare @LOOKUPORGANZIATIONEXCHANGERATE bit = 0;
if @ORIGINTOORGANIZATIONEXCHANGERATEID is null
begin
set @LOOKUPORGANZIATIONEXCHANGERATE = 1;
end
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @VALUE, @DONATIONDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEVALUE output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONVALUE output, @ORIGINTOORGANIZATIONEXCHANGERATEID output, @LOOKUPORGANZIATIONEXCHANGERATE;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @MINIMUMBID, @DONATIONDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEMINIMUMBID output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONMINIMUMBID output, @ORIGINTOORGANIZATIONEXCHANGERATEID output, @LOOKUPORGANZIATIONEXCHANGERATE;
-- check to see if amount, postdate, or post status has changed
if @CLEARGLDISTRIBUTION = 0
if (
select count(REVENUE.ID)
from dbo.REVENUE
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
where REVENUE.ID = @REVENUEAUCTIONDONATIONID
and REVENUE.TRANSACTIONAMOUNT = @VALUE
and REVENUE.AMOUNT = @BASEVALUE
and REVENUE.ORGANIZATIONAMOUNT = @ORGANIZATIONVALUE
and ((REVENUE.BASEEXCHANGERATEID is null and @BASEEXCHANGERATEID is null) or (REVENUE.BASEEXCHANGERATEID = @BASEEXCHANGERATEID))
and ((REVENUE.ORGANIZATIONEXCHANGERATEID is null and @ORIGINTOORGANIZATIONEXCHANGERATEID is null) or (REVENUE.ORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID))
and REVENUE.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
and REVENUE.BASECURRENCYID = @BASECURRENCYID
and REVENUE.POSTDATE = @POSTDATE
and ((@POSTSTATUSCODE = 2 and REVENUE.DONOTPOST = 1) or (@POSTSTATUSCODE = 1 and REVENUE.DONOTPOST = 0))
and REVENUESPLIT.DESIGNATIONID = @DESIGNATIONID
) = 0
begin
set @CLEARGLDISTRIBUTION = 1;
end
-- make sure we aren't trying to modify revenue in a locked/closed deposit/bank
exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @REVENUEAUCTIONDONATIONID
update dbo.REVENUE set
REVENUE.CONSTITUENTID = @DONORID,
REVENUE.DATE = @DONATIONDATE,
REVENUE.POSTDATE = @POSTDATE,
REVENUE.DONOTPOST = @DONOTPOST,
REVENUE.AMOUNT = @BASEVALUE,
REVENUE.RECEIPTAMOUNT = @VALUE,
REVENUE.GIVENANONYMOUSLY = @ISANONYMOUS,
REVENUE.CHANGEDBYID = @CHANGEAGENTID,
REVENUE.DATECHANGED = @CURRENTDATE,
REVENUE.TRANSACTIONAMOUNT = @VALUE,
REVENUE.ORGANIZATIONAMOUNT = @ORGANIZATIONVALUE,
REVENUE.ORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID,
REVENUE.BASEEXCHANGERATEID = @BASEEXCHANGERATEID
where REVENUE.ID = @REVENUEAUCTIONDONATIONID
update dbo.REVENUESPLIT set
REVENUESPLIT.DESIGNATIONID = @DESIGNATIONID,
REVENUESPLIT.AMOUNT = @BASEVALUE,
REVENUESPLIT.CHANGEDBYID = @CHANGEAGENTID,
REVENUESPLIT.DATECHANGED = @CURRENTDATE,
REVENUESPLIT.TRANSACTIONAMOUNT = @VALUE,
REVENUESPLIT.BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
REVENUESPLIT.ORGANIZATIONAMOUNT = @ORGANIZATIONVALUE,
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID
where REVENUESPLIT.REVENUEID = @REVENUEAUCTIONDONATIONID
update dbo.REVENUEPAYMENTMETHOD set
REVENUEPAYMENTMETHOD.AMOUNT = @BASEVALUE,
REVENUEPAYMENTMETHOD.CHANGEDBYID = @CHANGEAGENTID,
REVENUEPAYMENTMETHOD.DATECHANGED = @CURRENTDATE
where REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEAUCTIONDONATIONID
update dbo.AUCTIONITEM set
AUCTIONITEM.NAME = @NAME,
AUCTIONITEM.AUCTIONITEMCATEGORYID = @AUCTIONITEMCATEGORYID,
AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = @AUCTIONITEMSUBCATEGORYID,
AUCTIONITEM.DESCRIPTION = @DESCRIPTION,
AUCTIONITEM.VALUE = @BASEVALUE,
AUCTIONITEM.MINIMUMBID = @BASEMINIMUMBID,
AUCTIONITEM.EXPIRATIONDATE = @EXPIRATIONDATE,
AUCTIONITEM.EVENTAUCTIONID = @EVENTAUCTIONID,
AUCTIONITEM.CHANGEDBYID = @CHANGEAGENTID,
AUCTIONITEM.DATECHANGED = @CURRENTDATE,
AUCTIONITEM.TRANSACTIONVALUE = @VALUE,
AUCTIONITEM.ORGANIZATIONVALUE = @ORGANIZATIONVALUE,
AUCTIONITEM.TRANSACTIONMINIMUMBID = @MINIMUMBID,
AUCTIONITEM.ORGANIZATIONMINIMUMBID = @ORGANIZATIONMINIMUMBID,
AUCTIONITEM.BASECURRENCYID = @BASECURRENCYID,
AUCTIONITEM.BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
AUCTIONITEM.ORIGINTOORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID,
AUCTIONITEM.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
where ID = @ID
if @CLEARGLDISTRIBUTION = 1
begin
-- Clear GL
delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @REVENUEAUCTIONDONATIONID and OUTDATED = 0;
-- Add new GL distributions
if @POSTSTATUSCODE <> 2
begin
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEAUCTIONDONATIONID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_AUCTIONITEM_ADJUSTPURCHASEGLFORDONATION @ID, 0, @CHANGEAGENTID, @CURRENTDATE;
end
end
if @PREVIOUSDONORID <> @DONORID or @ISANONYMOUS <> @WASANONYMOUS
begin
-- Remove previous recognition credits
delete from dbo.REVENUERECOGNITION where REVENUESPLITID in (select ID from dbo.REVENUESPLIT where REVENUEID = @REVENUEAUCTIONDONATIONID)
-- Create the new default recognition credits
insert into dbo.REVENUERECOGNITION
(
REVENUESPLITID,
CONSTITUENTID,
AMOUNT,
EFFECTIVEDATE,
REVENUERECOGNITIONTYPECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID
)
select
REVENUESPLIT.ID,
RECOGNITIONS.CONSTITUENTID,
RECOGNITIONS.AMOUNT,
REVENUE.DATE,
RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@BASECURRENCYID,
case
when @BASECURRENCYID = @ORGANIZATIONCURRENCYID then
RECOGNITIONS.AMOUNT
else
dbo.UFN_CURRENCY_CONVERT(RECOGNITIONS.AMOUNT, @ORIGINTOORGANIZATIONEXCHANGERATEID)
end,
@ORIGINTOORGANIZATIONEXCHANGERATEID
from dbo.REVENUE
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(@ISANONYMOUS, @DONORID, REVENUESPLIT.AMOUNT, REVENUE.DATE, null) as RECOGNITIONS
where
REVENUE.ID = @REVENUEAUCTIONDONATIONID
end
if @OLDSPOTRATEID is not null and not exists (select 1 from dbo.REVENUE where ID = @REVENUEAUCTIONDONATIONID and BASEEXCHANGERATEID = @OLDSPOTRATEID)
begin
exec dbo.USP_CURRENCYEXCHANGERATE_DELETEBYID_WITHCHANGEAGENTID @OLDSPOTRATEID, @CHANGEAGENTID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;