USP_DATAFORMTEMPLATE_EDIT_AUCTIONDONATION_2
The save procedure used by the edit dataform template "Auction Donation Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @AMOUNT | money | IN | Value |
| @DATE | datetime | IN | Date |
| @EXPIRATIONDATE | datetime | IN | Expiration date |
| @DESIGNATIONID | uniqueidentifier | IN | Designation |
| @POSTDATE | datetime | IN | Post date |
| @POSTSTATUSCODE | tinyint | IN | Post status |
| @BASEEXCHANGERATEID | uniqueidentifier | IN | Exchange rate ID |
| @EXCHANGERATE | decimal(20, 8) | IN | Exchange rate |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_AUCTIONDONATION_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@AMOUNT money,
@DATE datetime,
@EXPIRATIONDATE datetime,
@DESIGNATIONID uniqueidentifier,
@POSTDATE datetime,
@POSTSTATUSCODE tinyint,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @DONOTPOST bit;
set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;
declare @ORGANIZATIONAMOUNT money;
declare @BASEAMOUNT money;
declare @BASECURRENCYID uniqueidentifier;
declare @ORIGINTOORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @PREVIOUSDATE datetime;
declare @OLDSPOTRATEID uniqueidentifier;
begin try
-- 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 = @ID;
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
set @POSTSTATUSCODE = 2 -- Do not post
set @DONOTPOST = 1
set @POSTDATE = null
end;
declare @SITEID uniqueidentifier;
select @SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(@DESIGNATIONID);
declare @EVENTAUCTIONID uniqueidentifier;
select @EVENTAUCTIONID = AUCTIONITEM.EVENTAUCTIONID from dbo.AUCTIONITEM where REVENUEAUCTIONDONATIONID = @ID;
if (@EVENTAUCTIONID is not null)
and (
(@SITEID is null and exists(select 1 from dbo.EVENTSITE where EVENTID = @EVENTAUCTIONID))
or
(@SITEID is not null and not exists(select 1 from dbo.EVENTSITE where EVENTID = @EVENTAUCTIONID and SITEID = @SITEID))
)
raiserror('BBERR_AUCTIONITEM_DESIGNATIONNOTVALID',13,1);
declare @ISPOSTED bit = 0;
declare @ORIGINALAMOUNT money = 0;
select
@ISPOSTED = case when FINANCIALTRANSACTION.POSTSTATUSCODE != 2 then 0 else 1 end,
@ORIGINALAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
@BASECURRENCYID = V.BASECURRENCYID,
@PREVIOUSDATE = FINANCIALTRANSACTION.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 = FINANCIALTRANSACTION.ORGEXCHANGERATEID
from
dbo.FINANCIALTRANSACTION
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
left join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where FINANCIALTRANSACTION.ID = @ID;
if @ISPOSTED = 1
raiserror('BBERR_AUCTIONDONATION_SHOULDBEADJUSTMENT', 13, 1);
if @EXPIRATIONDATE < @DATE
raiserror('BBERR_AUCTIONDONATION_DONATIONDATEMUSTNOTBEAFTEREXPIRAITONDATE', 13, 1);
declare @LOOKUPORGANZIATIONEXCHANGERATE bit = 0;
if @ORIGINTOORGANIZATIONEXCHANGERATEID is null
begin
set @LOOKUPORGANZIATIONEXCHANGERATE = 1;
end
declare @HASWRITEOFF bit = 0;
select
@HASWRITEOFF = case when count(*) > 0 then 1 else 0 end
from
dbo.FINANCIALTRANSACTION
inner join
dbo.WRITEOFF_EXT on FINANCIALTRANSACTION.ID = WRITEOFF_EXT.ID
where
FINANCIALTRANSACTION.TYPECODE = 20
and FINANCIALTRANSACTION.DELETEDON is null
and FINANCIALTRANSACTION.PARENTID = @ID
and isnull(FINANCIALTRANSACTION.POSTSTATUSCODE, 3) != 3;
if @HASWRITEOFF = 1 and @AMOUNT <> @ORIGINALAMOUNT
raiserror('BBERR_AUCTIONDONATION_CANNOTADJUSTWRITTENOFFAMOUNT', 13, 1);
-- make sure we aren't trying to modify revenue in a locked/closed deposit/bank
exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;
--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();
--Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future
/*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
begin
raiserror('User does not have the right to add a new spot rate.', 13, 1);
return 1;
end*/
insert into dbo.CURRENCYEXCHANGERATE
(
ID,
FROMCURRENCYID,
TOCURRENCYID,
RATE,
ASOFDATE,
TYPECODE,
SOURCECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
@BASECURRENCYID,
@EXCHANGERATE,
@DATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORIGINTOORGANIZATIONEXCHANGERATEID output, @LOOKUPORGANZIATIONEXCHANGERATE;
-- handle updating the data
update dbo.FINANCIALTRANSACTION set
BASEAMOUNT = @BASEAMOUNT,
DATE = @DATE,
POSTDATE = @POSTDATE,
POSTSTATUSCODE = case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 2 else case when @DONOTPOST = 1 then 3 else case @POSTSTATUSCODE when 0 then 2 else 1 end end end,
TRANSACTIONAMOUNT = @AMOUNT,
ORGAMOUNT = @ORGANIZATIONAMOUNT,
ORGEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
FINANCIALTRANSACTION.ID = @ID
and FINANCIALTRANSACTION.POSTSTATUSCODE <> 2;
update dbo.REVENUE_EXT set
RECEIPTAMOUNT = @AMOUNT,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from dbo.REVENUE_EXT
where
REVENUE_EXT.ID = @ID;
update dbo.FINANCIALTRANSACTIONLINEITEM set
POSTSTATUSCODE = case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 2 else case when @DONOTPOST = 1 then 3 else case @POSTSTATUSCODE when 0 then 2 else 1 end end end,
POSTDATE = @POSTDATE,
BASEAMOUNT = @BASEAMOUNT,
TRANSACTIONAMOUNT = @AMOUNT,
ORGAMOUNT = @ORGANIZATIONAMOUNT,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM REVERSE on FINANCIALTRANSACTIONLINEITEM.ID = REVERSE.REVERSEDLINEITEMID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
and REVERSE.ID is null;
update dbo.REVENUESPLIT_EXT set
DESIGNATIONID = @DESIGNATIONID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
dbo.REVENUESPLIT_EXT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID;
update dbo.REVENUESPLIT_EXT set
DESIGNATIONID = @DESIGNATIONID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from dbo.REVENUESPLIT_EXT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where
FINANCIALTRANSACTION.PARENTID = @ID
and FINANCIALTRANSACTION.TYPECODE = 20 --Write off
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;
update dbo.AUCTIONITEM set
VALUE = @BASEAMOUNT,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID,
TRANSACTIONVALUE = @AMOUNT,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
ORGANIZATIONVALUE = @ORGANIZATIONAMOUNT,
ORIGINTOORGANIZATIONEXCHANGERATEID = @ORIGINTOORGANIZATIONEXCHANGERATEID
where REVENUEAUCTIONDONATIONID = @ID;
update dbo.REVENUEPAYMENTMETHOD set
REVENUEPAYMENTMETHOD.AMOUNT = @BASEAMOUNT,
REVENUEPAYMENTMETHOD.CHANGEDBYID = @CHANGEAGENTID,
REVENUEPAYMENTMETHOD.DATECHANGED = @CURRENTDATE
where REVENUEPAYMENTMETHOD.REVENUEID = @ID
delete from dbo.REVENUEGLDISTRIBUTION
where REVENUEID = @ID
and OUTDATED = 0;
delete from dbo.WRITEOFFGLDISTRIBUTION
where REVENUEID = @ID
and OUTDATED = 0;
--Collect the IDs of revenue that will need new GL distributions
--as a result of editing this auction donation.
declare @AUCTIONPAYMENT table
(
ID uniqueidentifier
);
--Auction purchase for this item
insert into @AUCTIONPAYMENT (ID)
select top 1
AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID
from
dbo.AUCTIONITEM
inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
where
AUCTIONITEM.REVENUEAUCTIONDONATIONID = @ID
and AUCTIONITEM.PACKAGEID is null
and AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null;
--Purchases involving items that share a package with this auction donation
insert into @AUCTIONPAYMENT (ID)
select
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
from
dbo.AUCTIONITEM
inner join dbo.AUCTIONITEM as SAMEPACKAGEAUCTIONITEM on AUCTIONITEM.PACKAGEID = SAMEPACKAGEAUCTIONITEM.PACKAGEID
inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = SAMEPACKAGEAUCTIONITEM.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = AUCTIONITEMPURCHASE.PURCHASEID
where
AUCTIONITEM.REVENUEAUCTIONDONATIONID = @ID;
delete from dbo.AUCTIONPURCHASEGLDISTRIBUTION
where AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID in (select ID from @AUCTIONPAYMENT)
and AUCTIONPURCHASEGLDISTRIBUTION.OUTDATED = 0;
if @POSTSTATUSCODE <> 2
begin
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
end
--Need to update purchase GL because gains and losses may have changed
exec dbo.USP_AUCTIONITEM_ADJUSTPURCHASEGLFORDONATION @ID, 1, @CHANGEAGENTID, @CURRENTDATE;
if @OLDSPOTRATEID is not null and not exists (select 1 from dbo.REVENUE where ID = @ID 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;