USP_DATAFORMTEMPLATE_ADD_STOCKSALEBYTRANSACTION
The save procedure used by the add dataform template "Stock Sale By Transaction Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @REVENUEID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @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_ADD_STOCKSALEBYTRANSACTION
(
@ID uniqueidentifier = null output,
@REVENUEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@SALEDATE datetime,
@SALEAMOUNT money,
@FEE money = 0,
@NUMBEROFUNITS decimal(20,3),
@LOWPRICE decimal(19,4) = 0,
@MEDIANPRICE decimal(19,4) = 0,
@HIGHPRICE decimal(19,4) = 0,
@SALEPOSTDATE datetime = null,
@SALEPOSTSTATUSCODE tinyint = 1,
@BASEEXCHANGERATEID uniqueidentifier = null,
@EXCHANGERATE decimal(20,8) = null,
@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.FINANCIALTRANSACTION
where ID = @REVENUEID;
declare @ALLOWGLDISTRIBUTIONS bit;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
if @ALLOWGLDISTRIBUTIONS = 0
begin
set @SALEPOSTSTATUSCODE = 2 -- Do not post
set @SALEPOSTDATE = null
end
-- ****
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTID @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @ID is null
set @ID = newid();
-- The ID for STOCKDETAIL is the same as REVENUEPAYMENTMETHOD's ID
declare @STOCKDETAILID uniqueidentifier;
select top 1
@STOCKDETAILID = ID
from dbo.REVENUEPAYMENTMETHOD
where REVENUEID = @REVENUEID;
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
--JamesWill WI128968 2011-02-08 Don't allow a sale before the revenue date
if @SALEDATE < (select DATE from dbo.REVENUE where ID = @REVENUEID)
begin
raiserror('BBERR_SALEDATEBEFOREREVENUEDATE', 13, 1);
return 1;
end
--JamesWill WI128968 2011-02-08 Don't allow a sale to post before the revenue date
if @SALEPOSTDATE < (select DATE from dbo.REVENUE where ID = @REVENUEID)
begin
raiserror('BBERR_SALEPOSTDATEBEFOREREVENUEDATE', 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.REVENUE where ID = @REVENUEID) = 1
begin
raiserror('BBERR_TRANSACTIONDONOTPOSTSALEPOST', 13, 1);
return 1;
end
begin try
declare
@ORGANIZATIONSALEAMOUNT money,
@ORGANIZATIONFEE money,
@ORGANIZATIONLOWPRICE decimal(19,4),
@ORGANIZATIONMEDIANPRICE decimal(19,4),
@ORGANIZATIONHIGHPRICE decimal(19,4),
@BASEAMOUNT money,
@BASEFEE money,
@BASELOWPRICE decimal(19,4),
@BASEMEDIANPRICE decimal(19,4),
@BASEHIGHPRICE decimal(19,4),
@BASECURRENCYID uniqueidentifier,
@ORGANIZATIONEXCHANGERATEID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier;
select
@TRANSACTIONCURRENCYID = STOCKDETAIL.TRANSACTIONCURRENCYID,
@BASECURRENCYID = STOCKDETAIL.BASECURRENCYID
from
dbo.REVENUEPAYMENTMETHOD
inner join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
where
REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID;
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, @BASEAMOUNT 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;
if @BASEAMOUNT < 0
raiserror('CK_STOCKSALE_SALEAMOUNTPOSITIVE', 13, 1);
insert into dbo.FINANCIALTRANSACTION (
ID
,PARENTID
,DATE
,POSTSTATUSCODE
,POSTDATE
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,TRANSACTIONCURRENCYID
,BASEEXCHANGERATEID
,ORGEXCHANGERATEID
,PDACCOUNTSYSTEMID
,TYPECODE
,DESCRIPTION
,DELETEDON
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values (
@ID
,@REVENUEID
,@SALEDATE
,case isnull(@SALEPOSTSTATUSCODE, 1) when 0 then 2 when 1 then 1 when 2 then 3 end
,@SALEPOSTDATE
,@SALEAMOUNT
,@BASEAMOUNT
,@ORGANIZATIONSALEAMOUNT
,@TRANSACTIONCURRENCYID
,@BASEEXCHANGERATEID
,@ORGANIZATIONEXCHANGERATEID
,@PDACCOUNTSYSTEMID
,21
,''
,null
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
insert into dbo.STOCKSALE_EXT (
ID
,STOCKDETAILID
,SALEDATE
,NUMBEROFUNITS
,TRANSACTIONFEE
,ORGANIZATIONFEE
,FEE
,TRANSACTIONLOWPRICE
,TRANSACTIONMEDIANPRICE
,TRANSACTIONHIGHPRICE
,ORGANIZATIONLOWPRICE
,ORGANIZATIONMEDIANPRICE
,ORGANIZATIONHIGHPRICE
,LOWPRICE
,MEDIANPRICE
,HIGHPRICE
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
values (
@ID
,@STOCKDETAILID
,@SALEDATE
,@NUMBEROFUNITS
,@FEE
,@ORGANIZATIONFEE
,@BASEFEE
,@LOWPRICE
,@MEDIANPRICE
,@HIGHPRICE
,@ORGANIZATIONLOWPRICE
,@ORGANIZATIONMEDIANPRICE
,@ORGANIZATIONHIGHPRICE
,@BASELOWPRICE
,@BASEMEDIANPRICE
,@BASEHIGHPRICE
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
)
-- Add new stock detail GL distributions
if @SALEPOSTSTATUSCODE <> 2
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, @ID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;