USP_DATAFORMTEMPLATE_EDIT_GIFTINKINDSALE
The save procedure used by the edit dataform template "Gift-in-Kind Sale 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. |
| @SALEDATE | datetime | IN | Date of sale |
| @SALEAMOUNT | money | IN | Sale amount |
| @NUMBEROFUNITS | int | IN | Units sold |
| @SALEPOSTDATE | datetime | IN | GL post date |
| @SALEPOSTSTATUSCODE | tinyint | IN | GL 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_GIFTINKINDSALE
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@SALEDATE datetime,
@SALEAMOUNT money,
@NUMBEROFUNITS integer,
@SALEPOSTDATE datetime,
@SALEPOSTSTATUSCODE tinyint,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on
-- 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;
declare @ALLOWGLDISTRIBUTIONS bit;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
if @ALLOWGLDISTRIBUTIONS = 0
begin
set @SALEPOSTSTATUSCODE = 2 -- Do not post
set @SALEPOSTDATE = null
end
-- ****
declare @CURRENTSALEPOSTSTATUSCODE tinyint
select
@CURRENTSALEPOSTSTATUSCODE = SALEPOSTSTATUSCODE
from dbo.GIFTINKINDSALE where ID = @ID
-- Verify the transaction hasn't already been posted
if @CURRENTSALEPOSTSTATUSCODE = 0 -- Posted
begin
raiserror('GIFTINKINDSALEMUSTNOTBEPOSTED', 13, 1)
return 1
end
-- Raise error if the transaction is do not post but the gift-in-kind sale isn't do not post
if @SALEPOSTSTATUSCODE <> 2 and
( select top 1 DONOTPOST from dbo.GIFTINKINDSALE
inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
where GIFTINKINDSALE.ID = @ID) = 1
begin
raiserror('BBERR_TRANSACTIONDONOTPOSTSALEPOST', 13, 1)
return 1
end
--Don't allow a sale before the revenue date.
if @SALEDATE <
( select DATE from dbo.GIFTINKINDSALE
inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
where GIFTINKINDSALE.ID = @ID)
begin
raiserror('BBERR_SALEDATEBEFOREREVENUEDATE', 13, 1);
return 1;
end
--Don't allow a sale to post before the revenue date.
if @SALEPOSTDATE <
( select DATE from dbo.GIFTINKINDSALE
inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
where GIFTINKINDSALE.ID = @ID)
begin
raiserror('BBERR_SALEPOSTDATEBEFOREREVENUEDATE', 13, 1);
return 1;
end
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTID @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @UPDATEDISTRIBUTIONS bit = 0
begin try
declare
@ORGANIZATIONSALEAMOUNT money,
@BASESALEAMOUNT money,
@BASECURRENCYID uniqueidentifier,
@ORGANIZATIONEXCHANGERATEID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier;
declare @OLDSPOTRATEID uniqueidentifier;
select
@TRANSACTIONCURRENCYID = GIFTINKINDSALE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = GIFTINKINDSALE.BASECURRENCYID,
@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
from
dbo.GIFTINKINDSALE
left join dbo.CURRENCYEXCHANGERATE on GIFTINKINDSALE.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where
GIFTINKINDSALE.ID = @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,
@SALEDATE,
2,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @SALEAMOUNT, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASESALEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONSALEAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1; --Get an organization currency and exchange rate on the first call
-- check to see if sale amount, post date, or post status has changed
if exists (
select 1
from dbo.GIFTINKINDSALE
where GIFTINKINDSALE.ID = @ID
and (TRANSACTIONSALEAMOUNT <> @SALEAMOUNT
or @SALEPOSTSTATUSCODE <> SALEPOSTSTATUSCODE
or @NUMBEROFUNITS <> NUMBEROFUNITS
or @SALEPOSTDATE <> SALEPOSTDATE or SALEDATE is null
or SALEAMOUNT <> @BASESALEAMOUNT
or ORGANIZATIONSALEAMOUNT <> @ORGANIZATIONSALEAMOUNT
or TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID
or BASECURRENCYID <> @BASECURRENCYID
or BASEEXCHANGERATEID <> @BASEEXCHANGERATEID
or (BASEEXCHANGERATEID is null and @BASEEXCHANGERATEID is not null)
or (BASEEXCHANGERATEID is not null and @BASEEXCHANGERATEID is null)
or ORGANIZATIONEXCHANGERATEID <> @ORGANIZATIONEXCHANGERATEID
or (ORGANIZATIONEXCHANGERATEID is null and @ORGANIZATIONEXCHANGERATEID is not null)
or (ORGANIZATIONEXCHANGERATEID is not null and @ORGANIZATIONEXCHANGERATEID is null)
)
)
set @UPDATEDISTRIBUTIONS = 1;
update dbo.FINANCIALTRANSACTION set
DATE = isnull(@SALEDATE,FINANCIALTRANSACTION.DATE),
BASEAMOUNT = @BASESALEAMOUNT,
TRANSACTIONAMOUNT = @SALEAMOUNT,
ORGAMOUNT = @ORGANIZATIONSALEAMOUNT,
POSTDATE = @SALEPOSTDATE,
POSTSTATUSCODE = case @SALEPOSTSTATUSCODE when 2 then 3 else 1 end,
TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @ID;
update dbo.GIFTINKINDSALE_EXT set
SALEDATE = @SALEDATE,
NUMBEROFUNITS = @NUMBEROFUNITS,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where ID = @ID
update dbo.FINANCIALTRANSACTIONLINEITEM set
TRANSACTIONAMOUNT = @SALEAMOUNT,
ORGAMOUNT = @ORGANIZATIONSALEAMOUNT,
BASEAMOUNT = @BASESALEAMOUNT,
POSTDATE = @SALEPOSTDATE,
POSTSTATUSCODE = case @SALEPOSTSTATUSCODE when 2 then 3 else 1 end,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
FINANCIALTRANSACTIONID = @ID;
if @UPDATEDISTRIBUTIONS = 1
begin
-- Cache CONTEXT INFO
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.GIFTINKINDSALEGLDISTRIBUTION where GIFTINKINDSALEID = @ID and OUTDATED = 0;
-- Add new gift-in-kind detail GL distributions
if @SALEPOSTSTATUSCODE <> 2
begin
declare @REVENUEID uniqueidentifier
select @REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
from dbo.REVENUEPAYMENTMETHOD
inner join dbo.GIFTINKINDSALE on REVENUEPAYMENTMETHOD.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
where GIFTINKINDSALE.ID = @ID
exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, @ID;
end
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
if @OLDSPOTRATEID is not null
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