USP_DATAFORMTEMPLATE_ADJUST_STOCKSALE_3
The save procedure used by the edit dataform template "Stock Sale By Transaction Edit Form 2".
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 |
| @FEE | money | IN | Fees |
| @NUMBEROFUNITS | decimal(20, 3) | IN | Units sold |
| @LOWPRICE | decimal(19, 4) | IN | Low |
| @MEDIANPRICE | decimal(19, 4) | IN | Median |
| @HIGHPRICE | decimal(19, 4) | IN | High |
| @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_STOCKSALE_3
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@SALEDATE date,
@SALEAMOUNT money,
@FEE money,
@NUMBEROFUNITS decimal(20,3),
@LOWPRICE decimal(19,4),
@MEDIANPRICE decimal(19,4),
@HIGHPRICE decimal(19,4),
@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,
@ORGANIZATIONFEE money,
@ORGANIZATIONLOWPRICE decimal(19,4),
@ORGANIZATIONMEDIANPRICE decimal(19,4),
@ORGANIZATIONHIGHPRICE decimal(19,4),
@BASESALEAMOUNT money,
@BASEFEE money,
@BASELOWPRICE decimal(19,4),
@BASEMEDIANPRICE decimal(19,4),
@BASEHIGHPRICE decimal(19,4),
@BASECURRENCYID uniqueidentifier,
@ORGANIZATIONEXCHANGERATEID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier;
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @OLDSPOTRATEID uniqueidentifier;
declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
select top 1 @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE
from dbo.STOCKSALEADJUSTMENT
where STOCKSALEID = @ID and POSTSTATUSCODE <> 0
order by DATEADDED desc;
if @ADJUSTMENTPOSTSTATUSCODE is null
set @ADJUSTMENTPOSTSTATUSCODE = 1
select
@TRANSACTIONCURRENCYID = STOCKSALE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = STOCKSALE.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.STOCKSALE
left join dbo.CURRENCYEXCHANGERATE on STOCKSALE.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where
STOCKSALE.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
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @FEE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEFEE output, @ORGANIZATIONCURRENCYID, @ORGANIZATIONFEE output, @ORGANIZATIONEXCHANGERATEID, 0;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @LOWPRICE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASELOWPRICE output, @ORGANIZATIONCURRENCYID, @ORGANIZATIONLOWPRICE output, @ORGANIZATIONEXCHANGERATEID, 0;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @MEDIANPRICE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEMEDIANPRICE output, @ORGANIZATIONCURRENCYID, @ORGANIZATIONMEDIANPRICE output, @ORGANIZATIONEXCHANGERATEID, 0;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @HIGHPRICE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEHIGHPRICE output, @ORGANIZATIONCURRENCYID, @ORGANIZATIONHIGHPRICE output, @ORGANIZATIONEXCHANGERATEID, 0;
declare
@ORIGINALSALEAMOUNT money,
@ORIGINALFEE money,
@ORIGINALLOWPRICE decimal(19,4),
@ORIGINALMEDIANPRICE decimal(19,4),
@ORIGINALHIGHPRICE decimal(19,4);
declare
@ORIGINALBASESALEAMOUNT money,
@ORIGINALBASEFEE money,
@ORIGINALBASELOWPRICE decimal(19,4),
@ORIGINALBASEMEDIANPRICE decimal(19,4),
@ORIGINALBASEHIGHPRICE decimal(19,4);
declare
@ORIGINALORGANIZATIONSALEAMOUNT money,
@ORIGINALORGANIZATIONFEE money,
@ORIGINALORGANIZATIONLOWPRICE decimal(19,4),
@ORIGINALORGANIZATIONMEDIANPRICE decimal(19,4),
@ORIGINALORGANIZATIONHIGHPRICE decimal(19,4);
declare
@ORIGINALTRANSACTIONCURRENCYID uniqueidentifier,
@ORIGINALBASECURRENCYID uniqueidentifier,
@ORIGINALBASEEXCHANGERATEID uniqueidentifier,
@ORIGINALORGANIZATIONEXCHANGERATEID uniqueidentifier,
@ORIGINALNUMBEROFUNITS int;
declare
@SALEPOSTSTATUSCODE tinyint,
@STOCKDETAILID uniqueidentifier,
@REVENUEID uniqueidentifier
select
@ORIGINALSALEAMOUNT = STOCKSALE.TRANSACTIONSALEAMOUNT,
@ORIGINALFEE = STOCKSALE.TRANSACTIONFEE,
@ORIGINALLOWPRICE = STOCKSALE.TRANSACTIONLOWPRICE,
@ORIGINALMEDIANPRICE = STOCKSALE.TRANSACTIONMEDIANPRICE,
@ORIGINALHIGHPRICE = STOCKSALE.TRANSACTIONHIGHPRICE,
@ORIGINALBASESALEAMOUNT = STOCKSALE.SALEAMOUNT,
@ORIGINALBASEFEE = STOCKSALE.FEE,
@ORIGINALBASELOWPRICE = STOCKSALE.LOWPRICE,
@ORIGINALBASEMEDIANPRICE = STOCKSALE.MEDIANPRICE,
@ORIGINALBASEHIGHPRICE = STOCKSALE.HIGHPRICE,
@ORIGINALORGANIZATIONSALEAMOUNT = STOCKSALE.ORGANIZATIONSALEAMOUNT,
@ORIGINALORGANIZATIONFEE = STOCKSALE.ORGANIZATIONFEE,
@ORIGINALORGANIZATIONLOWPRICE = STOCKSALE.ORGANIZATIONLOWPRICE,
@ORIGINALORGANIZATIONMEDIANPRICE = STOCKSALE.ORGANIZATIONMEDIANPRICE,
@ORIGINALORGANIZATIONHIGHPRICE = STOCKSALE.ORGANIZATIONHIGHPRICE,
@ORIGINALTRANSACTIONCURRENCYID = STOCKSALE.TRANSACTIONCURRENCYID,
@ORIGINALBASECURRENCYID = STOCKSALE.BASECURRENCYID,
@ORIGINALBASEEXCHANGERATEID = STOCKSALE.BASEEXCHANGERATEID,
@ORIGINALORGANIZATIONEXCHANGERATEID = STOCKSALE.ORGANIZATIONEXCHANGERATEID,
@SALEPOSTSTATUSCODE = STOCKSALE.SALEPOSTSTATUSCODE,
@STOCKDETAILID = STOCKSALE.STOCKDETAILID,
@ORIGINALNUMBEROFUNITS = STOCKSALE.NUMBEROFUNITS,
@REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
from dbo.STOCKSALE
inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
where STOCKSALE.ID = @ID
declare @NEEDSADJUSTMENT bit
set @NEEDSADJUSTMENT = 0
if
coalesce(@ORIGINALSALEAMOUNT, 0) <> coalesce(@SALEAMOUNT, 0) or
coalesce(@ORIGINALFEE, 0) <> coalesce(@FEE, 0) or
coalesce(@ORIGINALBASESALEAMOUNT, 0) <> coalesce(@BASESALEAMOUNT, 0) or
coalesce(@ORIGINALBASEFEE, 0) <> coalesce(@BASEFEE, 0) or
coalesce(@ORIGINALORGANIZATIONSALEAMOUNT, 0) <> coalesce(@ORGANIZATIONSALEAMOUNT, 0) or
coalesce(@ORIGINALORGANIZATIONFEE, 0) <> coalesce(@ORGANIZATIONFEE, 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('STOCKSALEMUSTBEPOSTED', 13, 1)
return 1
end
-- Already adjusted
if @NEEDSADJUSTMENT = 0
if exists ( select 1 from dbo.STOCKSALEADJUSTMENT
where STOCKSALEID = @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
if @LOWPRICE < 0
begin
raiserror('BBERR_LOWPRICEPERSHARENEGATIVE', 13, 1)
return 1
end
if @MEDIANPRICE < 0
begin
raiserror('BBERR_MEDIANPRICEPERSHARENEGATIVE', 13, 1)
return 1
end
if @HIGHPRICE < 0
begin
raiserror('BBERR_HIGHPRICEPERSHARENEGATIVE', 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_STOCKSALEADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE
end
if @BASESALEAMOUNT < 0
raiserror('CK_STOCKSALE_SALEAMOUNTPOSITIVE', 13, 1);
update dbo.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.STOCKSALE_EXT set
SALEDATE = @SALEDATE
,NUMBEROFUNITS = @NUMBEROFUNITS
,TRANSACTIONFEE = @FEE
,FEE = @BASEFEE
,ORGANIZATIONFEE = @ORGANIZATIONFEE
,TRANSACTIONLOWPRICE = @LOWPRICE
,TRANSACTIONMEDIANPRICE = @MEDIANPRICE
,TRANSACTIONHIGHPRICE = @HIGHPRICE
,LOWPRICE = @BASELOWPRICE
,MEDIANPRICE = @BASEMEDIANPRICE
,HIGHPRICE = @BASEHIGHPRICE
,ORGANIZATIONLOWPRICE = @ORGANIZATIONLOWPRICE
,ORGANIZATIONMEDIANPRICE = @ORGANIZATIONMEDIANPRICE
,ORGANIZATIONHIGHPRICE = @ORGANIZATIONHIGHPRICE
,DATECHANGED = @CURRENTDATE
,CHANGEDBYID = @CHANGEAGENTID
where ID = @ID;
update dbo.FINANCIALTRANSACTIONLINEITEM set
TRANSACTIONAMOUNT = isnull(@SALEAMOUNT, 0) + isnull(@FEE, 0)
,BASEAMOUNT = isnull(@BASESALEAMOUNT, 0) + isnull(@BASEFEE, 0)
,ORGAMOUNT = isnull(@ORGANIZATIONSALEAMOUNT, 0) + isnull(@ORGANIZATIONFEE, 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.STOCKSALEGLDISTRIBUTION where STOCKSALEID = @ID and OUTDATED = 0
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @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_STOCKSALE_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