USP_DATAFORMTEMPLATE_EDIT_TRANSACTIONSELLPROPERTY_2
The save procedure used by the edit dataform template "Revenue Transaction Sell Property 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 |
| @BROKERFEE | money | IN | Fees |
| @POSTSTATUSCODE | tinyint | IN | GL post status |
| @POSTDATE | datetime | IN | GL post date |
| @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_TRANSACTIONSELLPROPERTY_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SALEDATE datetime,
@SALEAMOUNT money,
@BROKERFEE money,
@POSTSTATUSCODE tinyint,
@POSTDATE datetime,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @REVENUEPAYMETHODID uniqueidentifier;
declare @PAYMENTMETHODCODE tinyint;
declare @UPDATEDISTRIBUTIONS bit;
declare @DATE datetime
set @DATE = getdate()
begin try
-- 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;
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
set @POSTSTATUSCODE = 2; -- Do not post
set @POSTDATE = null;
end;
if @POSTSTATUSCODE <> 2 and (select top 1 DONOTPOST from dbo.REVENUE where ID = @ID) = 1
begin
raiserror('BBERR_TRANSACTIONDONOTPOSTSALEPOST', 13, 1);
return 1;
end
declare
@ORGANIZATIONSALEAMOUNT money,
@ORGANIZATIONBROKERFEE money,
@BASESALEAMOUNT money,
@BASEBROKERFEE money,
@BASECURRENCYID uniqueidentifier,
@ORGANIZATIONEXCHANGERATEID uniqueidentifier,
@TRANSACTIONCURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier;
declare @OLDSPOTRATEID uniqueidentifier;
select
@TRANSACTIONCURRENCYID = PROPERTYDETAIL.TRANSACTIONCURRENCYID,
@BASECURRENCYID = PROPERTYDETAIL.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.[REVENUE]
inner join dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].REVENUEID = REVENUE.ID
inner join dbo.[PROPERTYDETAIL] on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID
left join dbo.CURRENCYEXCHANGERATE on PROPERTYDETAIL.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where
[REVENUE].[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,
@DATE,
@DATE
);
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 @BROKERFEE, @SALEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEBROKERFEE output, @ORGANIZATIONCURRENCYID, @ORGANIZATIONBROKERFEE output, @ORGANIZATIONEXCHANGERATEID, 0;
select top 1
@REVENUEPAYMETHODID = ID,
@PAYMENTMETHODCODE=PAYMENTMETHODCODE
from REVENUEPAYMENTMETHOD where REVENUEID = @ID;
if exists (select top 1 ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMETHODID and SALEPOSTSTATUSCODE = 0)
raiserror('This information has been posted and cannot be edited.', 13, 1);
-- check to see if sale amounts, broker fees, post date, post status, or exchange rates have changed
if exists (
select 1
from dbo.PROPERTYDETAIL
where PROPERTYDETAIL.ID = @REVENUEPAYMETHODID
and (
PROPERTYDETAIL.TRANSACTIONSALEAMOUNT <> @SALEAMOUNT
or PROPERTYDETAIL.TRANSACTIONBROKERFEE <> @BROKERFEE
or @POSTSTATUSCODE <> PROPERTYDETAIL.SALEPOSTSTATUSCODE
or @POSTDATE <> PROPERTYDETAIL.SALEPOSTDATE
or PROPERTYDETAIL.SALEDATE is null
or PROPERTYDETAIL.SALEAMOUNT <> @BASESALEAMOUNT
or PROPERTYDETAIL.BROKERFEE <> @BASEBROKERFEE
or PROPERTYDETAIL.ORGANIZATIONSALEAMOUNT <> @ORGANIZATIONSALEAMOUNT
or PROPERTYDETAIL.ORGANIZATIONBROKERFEE <> @ORGANIZATIONBROKERFEE
or PROPERTYDETAIL.BASEEXCHANGERATEID <> @BASEEXCHANGERATEID
or PROPERTYDETAIL.ORGANIZATIONEXCHANGERATEID <> @ORGANIZATIONEXCHANGERATEID
)
)
begin
set @UPDATEDISTRIBUTIONS = 1;
end
update dbo.[PROPERTYDETAIL] set
[SALEDATE] = @SALEDATE,
[SALEAMOUNT] = @BASESALEAMOUNT,
[BROKERFEE] = @BASEBROKERFEE,
[SALEPOSTSTATUSCODE] = @POSTSTATUSCODE,
[SALEPOSTDATE] = @POSTDATE,
[ADDEDBYID] = @CHANGEAGENTID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATEADDED] = @DATE,
[DATECHANGED] = @DATE,
ORGANIZATIONSALEAMOUNT = @ORGANIZATIONSALEAMOUNT,
ORGANIZATIONBROKERFEE = @ORGANIZATIONBROKERFEE,
TRANSACTIONSALEAMOUNT = @SALEAMOUNT,
TRANSACTIONBROKERFEE = @BROKERFEE,
BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
where
[PROPERTYDETAIL].[ID] = @REVENUEPAYMETHODID;
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.PROPERTYDETAILGLDISTRIBUTION where PROPERTYDETAILID = @REVENUEPAYMETHODID and OUTDATED = 0;
-- Add new property detail GL distributions
if @POSTSTATUSCODE <> 2
exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @DATE;
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
if @OLDSPOTRATEID is not null and not exists (select 1 from dbo.REVENUE R where R.ID = @ID and R.BASEEXCHANGERATEID = @OLDSPOTRATEID)
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;