USP_DATAFORMTEMPLATE_EDIT_STOCKSALE_2
The save procedure used by the edit dataform template "Stock Sale 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 | datetime | 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 |
| @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_STOCKSALE_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@SALEDATE datetime,
@SALEAMOUNT money,
@FEE money,
@NUMBEROFUNITS decimal(20,3),
@LOWPRICE decimal(19,4),
@MEDIANPRICE decimal(19,4),
@HIGHPRICE decimal(19,4),
@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.STOCKSALE where ID = @ID
-- Verify the transaction hasn't already been posted
if @CURRENTSALEPOSTSTATUSCODE = 0 -- Posted
begin
raiserror('STOCKSALEMUSTNOTBEPOSTED', 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
-- Raise error if the transaction is do not post but the stock sale isn't do not post
if @SALEPOSTSTATUSCODE <> 2 and
( select top 1 DONOTPOST from dbo.STOCKSALE
inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
where STOCKSALE.ID = @ID) = 1
begin
raiserror('BBERR_TRANSACTIONDONOTPOSTSALEPOST', 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,
@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 @OLDSPOTRATEID uniqueidentifier;
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;
-- check to see if sale amount, broker fee, post date, or post status has changed
if exists (
select 1
from dbo.STOCKSALE
where STOCKSALE.ID = @ID
and (TRANSACTIONSALEAMOUNT <> @SALEAMOUNT
or TRANSACTIONFEE <> @FEE
or @SALEPOSTSTATUSCODE <> SALEPOSTSTATUSCODE
or @SALEPOSTDATE <> SALEPOSTDATE or SALEDATE is null
or SALEAMOUNT <> @BASESALEAMOUNT
or FEE <> @BASEFEE
or ORGANIZATIONSALEAMOUNT <> @ORGANIZATIONSALEAMOUNT
or ORGANIZATIONFEE <> @ORGANIZATIONFEE
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)
or NUMBEROFUNITS != @NUMBEROFUNITS
)
)
set @UPDATEDISTRIBUTIONS = 1;
update dbo.FINANCIALTRANSACTION set
DATE = @SALEDATE,
BASEAMOUNT = @BASESALEAMOUNT,
TRANSACTIONAMOUNT = @SALEAMOUNT,
ORGAMOUNT = @ORGANIZATIONSALEAMOUNT,
POSTDATE = @SALEPOSTDATE,
POSTSTATUSCODE = case @SALEPOSTSTATUSCODE
when 0
then 2
when 1
then 1
when 2
then 3
end,
TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @ID;
update dbo.STOCKSALE_EXT set
SALEDATE = @SALEDATE,
FEE = @BASEFEE,
NUMBEROFUNITS = @NUMBEROFUNITS,
LOWPRICE = @BASELOWPRICE,
MEDIANPRICE = @BASEMEDIANPRICE,
HIGHPRICE = @BASEHIGHPRICE,
ORGANIZATIONFEE = @ORGANIZATIONFEE,
ORGANIZATIONLOWPRICE = @ORGANIZATIONLOWPRICE,
ORGANIZATIONMEDIANPRICE = @ORGANIZATIONMEDIANPRICE,
ORGANIZATIONHIGHPRICE = @ORGANIZATIONHIGHPRICE,
TRANSACTIONFEE = @FEE,
TRANSACTIONLOWPRICE = @LOWPRICE,
TRANSACTIONMEDIANPRICE = @MEDIANPRICE,
TRANSACTIONHIGHPRICE = @HIGHPRICE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where ID = @ID
update dbo.FINANCIALTRANSACTIONLINEITEM set
TRANSACTIONAMOUNT = @SALEAMOUNT,
ORGAMOUNT = @ORGANIZATIONSALEAMOUNT,
BASEAMOUNT = @BASESALEAMOUNT,
POSTDATE = @SALEPOSTDATE,
POSTSTATUSCODE = case @SALEPOSTSTATUSCODE
when 0
then 2
when 1
then 1
when 2
then 3
end,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
FINANCIALTRANSACTIONID = @ID;
if @UPDATEDISTRIBUTIONS = 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;
-- Add new stock detail GL distributions
if @SALEPOSTSTATUSCODE <> 2
begin
declare @REVENUEID uniqueidentifier
select @REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
from dbo.REVENUEPAYMENTMETHOD
inner join dbo.STOCKSALE on REVENUEPAYMENTMETHOD.ID = STOCKSALE.STOCKDETAILID
where STOCKSALE.ID = @ID
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @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