USP_DATAFORMTEMPLATE_EDIT_AUCTIONITEMADJUST_2
The save procedure used by the edit dataform template "Auction Item Adjustment 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 |
| @ADJUSTMENTDATE | datetime | IN | Adjusted date |
| @ADJUSTMENTPOSTDATE | datetime | IN | Adjusted post date |
| @ADJUSTMENTREASON | nvarchar(300) | IN | Adjustment description |
| @ADJUSTMENTREASONCODEID | uniqueidentifier | IN | Adjustment reason |
| @POSTDATE | datetime | IN | Post date |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @BASECURRENCYID | uniqueidentifier | IN | Base currency |
| @TRANSACTIONCURRENCYID | uniqueidentifier | IN | Transaction currency |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @HADSPOTRATE | bit | IN | Had spot rate |
| @RATECHANGED | bit | IN | Rate changed |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_AUCTIONITEMADJUST_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,
@ADJUSTMENTDATE datetime,
@ADJUSTMENTPOSTDATE datetime,
@ADJUSTMENTREASON nvarchar(300),
@ADJUSTMENTREASONCODEID uniqueidentifier,
@POSTDATE datetime,
@POSTSTATUSCODE tinyint,
@BASECURRENCYID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@HADSPOTRATE bit,
@RATECHANGED bit
)
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 @ADJUSTMENTID uniqueidentifier = null;
declare @ADJUST bit = 0;
declare @ADJUSTMENTPOSTSTATUSCODE tinyint = 1;
declare @CLEARGLDISTRIBUTION bit = 0;
declare @DONOTPOST bit;
-- 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
where ID = @REVENUEAUCTIONDONATIONID
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
set @ADJUSTMENTPOSTSTATUSCODE = 2 -- Do not post
set @POSTSTATUSCODE = 2 -- Do not post
set @POSTDATE = null
end;
set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;
declare @ORIGINTOORGANIZATIONEXCHANGERATEID uniqueidentifier;
begin try
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);
--Transaction currency cannot be changed, make sure it is the same as the revenue
select
@TRANSACTIONCURRENCYID = AUCTIONITEM.TRANSACTIONCURRENCYID,
@ORIGINTOORGANIZATIONEXCHANGERATEID = AUCTIONITEM.ORIGINTOORGANIZATIONEXCHANGERATEID
from
dbo.AUCTIONITEM
where
AUCTIONITEM.ID = @ID;
--Multicurrency - If the revenue previously used a spot rate, but
-- its rate has changed, store the old rate's ID, so we can remove it later.
declare @OLDSPOTRATE uniqueidentifier
if @HADSPOTRATE = 1 and @RATECHANGED = 1
begin
select
@OLDSPOTRATE = BASEEXCHANGERATEID
from dbo.AUCTIONITEM
where ID = @ID
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
--Multicurrency - Retrieve and calculate the necessary multicurrency values.
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @BASEVALUE money;
declare @BASEMINIMUMBID money;
declare @ORGANIZATIONVALUE money;
declare @ORGANIZATIONMINIMUMBID money;
declare @LOOKUPORGANZIATIONEXCHANGERATE bit = 0;
if @ORIGINTOORGANIZATIONEXCHANGERATEID is null
begin
set @LOOKUPORGANZIATIONEXCHANGERATE = 1;
end
exec dbo.USP_CURRENCY_GETCURRENCYVALUES
@VALUE,
@DONATIONDATE,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID output,
@BASEVALUE output,
@ORGANIZATIONCURRENCYID output,
@ORGANIZATIONVALUE output,
@ORIGINTOORGANIZATIONEXCHANGERATEID output,
@LOOKUPORGANZIATIONEXCHANGERATE;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES
@MINIMUMBID,
@DONATIONDATE,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID output,
@BASEMINIMUMBID output,
@ORGANIZATIONCURRENCYID output,
@ORGANIZATIONMINIMUMBID output,
@ORIGINTOORGANIZATIONEXCHANGERATEID output,
@LOOKUPORGANZIATIONEXCHANGERATE;
declare @PREVIOUSDONORID uniqueidentifier;
declare @WASANONYMOUS bit;
select @PREVIOUSDONORID = CONSTITUENTID, @WASANONYMOUS = GIVENANONYMOUSLY from dbo.REVENUE where REVENUE.ID = @REVENUEAUCTIONDONATIONID
declare @ORIGINALAMOUNT money = 0;
select
@ORIGINALAMOUNT = AMOUNT
from dbo.REVENUE
where REVENUE.ID = @REVENUEAUCTIONDONATIONID;
declare @HASWRITEOFF bit = 0;
select
@HASWRITEOFF = case when count(*) > 0 then 1 else 0 end
from DBO.REVENUE
inner join DBO.WRITEOFF on REVENUE.ID = WRITEOFF.REVENUEID
where REVENUE.ID = @REVENUEAUCTIONDONATIONID
if @HASWRITEOFF = 1 and @VALUE <> @ORIGINALAMOUNT
raiserror('BBERR_AUCTIONDONATION_CANNOTADJUSTWRITTENOFFAMOUNT', 13, 1);
if @EXPIRATIONDATE < @DONATIONDATE
raiserror('The expiration date must be on or after the donation date.', 13, 1);
-- check to see if amount, postdate, post status, or currency fields 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.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 REVENUESPLIT.DESIGNATIONID = @DESIGNATIONID
) = 0
begin
set @CLEARGLDISTRIBUTION = 1;
end
select @ADJUST = 1
from
dbo.REVENUE
inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
where
REVENUE.ID = @REVENUEAUCTIONDONATIONID
and
(
REVENUE.TRANSACTIONAMOUNT <> @VALUE
or
REVENUE.AMOUNT <> @BASEVALUE
or
REVENUE.DATE <> @DONATIONDATE
or
REVENUE.BASEEXCHANGERATEID <> @BASEEXCHANGERATEID
or
REVENUE.ORGANIZATIONEXCHANGERATEID <> @ORIGINTOORGANIZATIONEXCHANGERATEID
or
REVENUE.TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID
or
REVENUE.BASECURRENCYID <> @BASECURRENCYID
or
REVENUESPLIT.DESIGNATIONID <> @DESIGNATIONID
or
REVENUE.CONSTITUENTID <> @DONORID
);
if @ADJUST = 1
begin
if @ADJUSTMENTREASONCODEID is null
raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)
exec dbo.USP_SAVE_ADJUSTMENT @REVENUEAUCTIONDONATIONID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE;
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.TRANSACTIONAMOUNT = @VALUE,
REVENUE.RECEIPTAMOUNT = @VALUE,
REVENUE.GIVENANONYMOUSLY = @ISANONYMOUS,
REVENUE.BASECURRENCYID = @BASECURRENCYID,
REVENUE.ORGANIZATIONAMOUNT = @ORGANIZATIONVALUE,
REVENUE.BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
REVENUE.ORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID,
REVENUE.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
REVENUE.CHANGEDBYID = @CHANGEAGENTID,
REVENUE.DATECHANGED = @CURRENTDATE
where REVENUE.ID = @REVENUEAUCTIONDONATIONID
update dbo.REVENUESPLIT set
REVENUESPLIT.DESIGNATIONID = @DESIGNATIONID,
REVENUESPLIT.AMOUNT = @BASEVALUE,
REVENUESPLIT.TRANSACTIONAMOUNT = @VALUE,
REVENUESPLIT.BASECURRENCYID = @BASECURRENCYID,
REVENUESPLIT.ORGANIZATIONAMOUNT = @ORGANIZATIONVALUE,
REVENUESPLIT.BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID,
REVENUESPLIT.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
REVENUESPLIT.CHANGEDBYID = @CHANGEAGENTID,
REVENUESPLIT.DATECHANGED = @CURRENTDATE
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.TRANSACTIONVALUE = @VALUE,
AUCTIONITEM.VALUE = @BASEVALUE,
AUCTIONITEM.TRANSACTIONMINIMUMBID = @MINIMUMBID,
AUCTIONITEM.MINIMUMBID = @BASEMINIMUMBID,
AUCTIONITEM.EXPIRATIONDATE = @EXPIRATIONDATE,
AUCTIONITEM.EVENTAUCTIONID = @EVENTAUCTIONID,
AUCTIONITEM.BASECURRENCYID = @BASECURRENCYID,
AUCTIONITEM.ORGANIZATIONVALUE = @ORGANIZATIONVALUE,
AUCTIONITEM.ORGANIZATIONMINIMUMBID = @ORGANIZATIONMINIMUMBID,
AUCTIONITEM.BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
AUCTIONITEM.ORIGINTOORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID,
AUCTIONITEM.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
AUCTIONITEM.CHANGEDBYID = @CHANGEAGENTID,
AUCTIONITEM.DATECHANGED = @CURRENTDATE
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 @ADJUST = 1
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @REVENUEAUCTIONDONATIONID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTID
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
--Multicurrency - If we stored an old spot rate earlier, now is the time to
-- remove it.
if @OLDSPOTRATE is not null
begin
delete CURRENCYEXCHANGERATE
where ID=@OLDSPOTRATE
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;