USP_DATAFORMTEMPLATE_ADJUST_GIFTINKINDSALE
The save procedure used by the edit dataform template "Gift-in-Kind Sale By Transaction Edit 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 | date | IN | Date of sale |
| @SALEAMOUNT | money | IN | Sale amount |
| @NUMBEROFUNITS | int | IN | Units sold |
| @ADJUSTMENTPOSTDATE | datetime | IN | Adjustment post date |
| @ADJUSTMENTDATE | datetime | IN | Adjustment date |
| @ADJUSTMENTREASON | nvarchar(100) | IN | Adjustment details |
| @ADJUSTMENTREASONCODEID | uniqueidentifier | IN | Adjustment reason |
| @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_ADJUST_GIFTINKINDSALE
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@SALEDATE date,
@SALEAMOUNT money,
@NUMBEROFUNITS integer,
@ADJUSTMENTPOSTDATE datetime,
@ADJUSTMENTDATE datetime,
@ADJUSTMENTREASON nvarchar(100),
@ADJUSTMENTREASONCODEID uniqueidentifier,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on
begin try
declare
@ORGANIZATIONSALEAMOUNT money,
@BASESALEAMOUNT money,
@BASEFEE money,
@BASECURRENCYID uniqueidentifier,
@ORGANIZATIONEXCHANGERATEID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@CURRENTDATE datetime,
@OLDSPOTRATEID uniqueidentifier,
@ADJUSTMENTPOSTSTATUSCODE tinyint;
set @CURRENTDATE = getdate()
select top 1 @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE
from dbo.GIFTINKINDSALEADJUSTMENT
where GIFTINKINDSALEID = @ID and POSTSTATUSCODE <> 0
order by DATEADDED desc;
if @ADJUSTMENTPOSTSTATUSCODE is null
set @ADJUSTMENTPOSTSTATUSCODE = 1
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();
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
declare
@ORIGINALSALEAMOUNT money,
@ORIGINALBASESALEAMOUNT money,
@ORIGINALORGANIZATIONSALEAMOUNT money,
@ORIGINALTRANSACTIONCURRENCYID uniqueidentifier,
@ORIGINALBASECURRENCYID uniqueidentifier,
@ORIGINALBASEEXCHANGERATEID uniqueidentifier,
@ORIGINALORGANIZATIONEXCHANGERATEID uniqueidentifier,
@ORIGINALNUMBEROFUNITS int,
@SALEPOSTSTATUSCODE tinyint,
@GIFTINKINDPAYMENTMETHODDETAILID uniqueidentifier,
@REVENUEID uniqueidentifier;
select
@ORIGINALSALEAMOUNT = GIFTINKINDSALE.TRANSACTIONSALEAMOUNT,
@ORIGINALBASESALEAMOUNT = GIFTINKINDSALE.SALEAMOUNT,
@ORIGINALORGANIZATIONSALEAMOUNT = GIFTINKINDSALE.ORGANIZATIONSALEAMOUNT,
@ORIGINALTRANSACTIONCURRENCYID = GIFTINKINDSALE.TRANSACTIONCURRENCYID,
@ORIGINALBASECURRENCYID = GIFTINKINDSALE.BASECURRENCYID,
@ORIGINALBASEEXCHANGERATEID = GIFTINKINDSALE.BASEEXCHANGERATEID,
@ORIGINALORGANIZATIONEXCHANGERATEID = GIFTINKINDSALE.ORGANIZATIONEXCHANGERATEID,
@SALEPOSTSTATUSCODE = GIFTINKINDSALE.SALEPOSTSTATUSCODE,
@GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID,
@ORIGINALNUMBEROFUNITS = GIFTINKINDSALE.NUMBEROFUNITS,
@REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
from dbo.GIFTINKINDSALE
inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
where GIFTINKINDSALE.ID = @ID
declare @NEEDSADJUSTMENT bit
set @NEEDSADJUSTMENT = 0
if
coalesce(@ORIGINALSALEAMOUNT, 0) <> coalesce(@SALEAMOUNT, 0) or
coalesce(@ORIGINALBASESALEAMOUNT, 0) <> coalesce(@BASESALEAMOUNT, 0) or
coalesce(@ORIGINALORGANIZATIONSALEAMOUNT, 0) <> coalesce(@ORGANIZATIONSALEAMOUNT, 0) or
coalesce(@ORIGINALNUMBEROFUNITS, 0) <> coalesce(@NUMBEROFUNITS, 0) or
@ORIGINALTRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID or
@ORIGINALBASECURRENCYID <> @BASECURRENCYID or
@ORIGINALBASEEXCHANGERATEID <> @BASEEXCHANGERATEID or
(@ORIGINALBASEEXCHANGERATEID is not null and @BASEEXCHANGERATEID is null) or
(@ORIGINALBASEEXCHANGERATEID is null and @BASEEXCHANGERATEID is not null) or
@ORIGINALORGANIZATIONEXCHANGERATEID <> @ORGANIZATIONEXCHANGERATEID or
(@ORIGINALORGANIZATIONEXCHANGERATEID is not null and @ORGANIZATIONEXCHANGERATEID is null) or
(@ORIGINALORGANIZATIONEXCHANGERATEID is null and @ORGANIZATIONEXCHANGERATEID is not null)
begin
set @NEEDSADJUSTMENT = 1
end
-- Verify the transaction has already been posted
if @SALEPOSTSTATUSCODE <> 0 -- Posted
begin
raiserror('GIFTINKINDSALEMUSTBEPOSTED', 13, 1)
return 1
end
-- Already adjusted
if @NEEDSADJUSTMENT = 0
if exists ( select 1 from dbo.GIFTINKINDSALEADJUSTMENT
where GIFTINKINDSALEID = @ID and POSTSTATUSCODE = 1)
set @NEEDSADJUSTMENT = 1
-- Make sure the adjustment date fields are set if it will be adjusted
if @NEEDSADJUSTMENT = 1 and @ADJUSTMENTPOSTDATE is null and @ADJUSTMENTPOSTSTATUSCODE <> 2
begin
raiserror('ADJUSTMENTPOSTDATEREQUIRED', 13, 1)
return 1
end
if @NEEDSADJUSTMENT = 1 and @ADJUSTMENTDATE is null
begin
raiserror('ADJUSTMENTDATEREQUIRED', 13, 1)
return 1
end
if @NEEDSADJUSTMENT = 1 and @ADJUSTMENTREASONCODEID is null
begin
raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)
return 1
end
declare @ADJUSTMENTID uniqueidentifier
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTID @CHANGEAGENTID output
if @NEEDSADJUSTMENT = 1
begin
exec dbo.USP_SAVE_GIFTINKINDSALEADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE
end
if @NUMBEROFUNITS <= 0
raiserror ('CK_GIFTINKINDSALE_NUMBEROFUNITSPOSITIVE', 16, 1);
if @NUMBEROFUNITS > dbo.UFN_GIFTINKINDPAYMENTMETHODDETAIL_REMAININGUNITS(@GIFTINKINDPAYMENTMETHODDETAILID,@ID)
raiserror ('CK_GIFTINKINDSALE_NUMBEROFUNITSVALID2', 16, 1);
if @BASESALEAMOUNT < 0
raiserror ('CK_GIFTINKINDSALE_SALEAMOUNTPOSITIVE', 16, 1);
update FINANCIALTRANSACTION set
DATE = @SALEDATE
,TRANSACTIONAMOUNT = @SALEAMOUNT
,BASEAMOUNT = @BASESALEAMOUNT
,ORGAMOUNT = @ORGANIZATIONSALEAMOUNT
,TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
,ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
,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 = isnull(@SALEAMOUNT, 0)
,BASEAMOUNT = isnull(@BASESALEAMOUNT, 0)
,ORGAMOUNT = isnull(@ORGANIZATIONSALEAMOUNT, 0)
,DATECHANGED = @CURRENTDATE
,CHANGEDBYID = @CHANGEAGENTID
where FINANCIALTRANSACTIONID = @ID and POSTSTATUSCODE != 2 and TYPECODE != 1;
if @NEEDSADJUSTMENT = 1
begin
--Clear GL
--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
exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, @ID
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
if @ADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_GIFTINKINDSALE_SAVEHISTORY @ID, @CHANGEAGENTID, null, @ADJUSTMENTID
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