USP_DATAFORMTEMPLATE_EDITLOAD_TRANSACTIONSELLPROPERTY

The load 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 used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@UNSOLDAMOUNT	money	INOUT	Unsold amount
@SALEDATE	datetime	INOUT	Date of sale
@SALEAMOUNT	money	INOUT	Sale amount
@BROKERFEE	money	INOUT	Fees
@POSTSTATUSCODE	tinyint	INOUT	GL post status
@POSTDATE	datetime	INOUT	GL post date
@ALREADYSOLD	bit	INOUT	Already sold
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@PAYMENTISDONOTPOST	bit	INOUT	Payment is do not post
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@HADSPOTRATE	bit	INOUT	Had spot rate
@RATECHANGED	bit	INOUT	Rate changed
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT	Transaction currency description
@ALLOWGLDISTRIBUTIONS	bit	INOUT
@USERGRANTEDSPOTRATE	bit	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_TRANSACTIONSELLPROPERTY (
          @ID uniqueidentifier,
          @DATALOADED bit = 0 output,
          @UNSOLDAMOUNT money = null output,
          @SALEDATE datetime = null output,
          @SALEAMOUNT money = null output,
          @BROKERFEE money = null output,
          @POSTSTATUSCODE tinyint = null output,
          @POSTDATE datetime = null output,
          @ALREADYSOLD bit = null output,
          @TSLONG bigint = 0 output,
          @PAYMENTISDONOTPOST bit = null output,
          @BASECURRENCYID uniqueidentifier = null output,
          @TRANSACTIONCURRENCYID uniqueidentifier = null output,
          @BASEEXCHANGERATEID uniqueidentifier = null output,
          @EXCHANGERATE decimal(20,8) = null output,
          @HADSPOTRATE bit = null output,
          @RATECHANGED bit = null output,
          @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
          @ALLOWGLDISTRIBUTIONS bit = null output,
          @USERGRANTEDSPOTRATE bit = null output
                )
                as
                set nocount on;

                set @DATALOADED = 0;
                set @TSLONG = 0;

                select top 1
                    @DATALOADED = 1,
                    @SALEDATE = PROPERTYDETAIL.[SALEDATE],
                    @SALEAMOUNT = PROPERTYDETAIL.[TRANSACTIONSALEAMOUNT],
                    @BROKERFEE = PROPERTYDETAIL.[TRANSACTIONBROKERFEE],
                    @POSTSTATUSCODE = 
                        case 
                            when REVENUE.DONOTPOST = 1
                                then 2
                            else
                                case PROPERTYDETAIL.[ISSOLD] when 1 then PROPERTYDETAIL.[SALEPOSTSTATUSCODE] else case when PROPERTYDETAIL.[SALEPOSTDATE] is null then 1 else  
                                PROPERTYDETAIL.[SALEPOSTSTATUSCODE] end end
                        end,
                    @POSTDATE = PROPERTYDETAIL.[SALEPOSTDATE],
                    @ALREADYSOLD = case when [REVENUEPAYMENTMETHOD].PAYMENTMETHODCODE = 5 and PROPERTYDETAIL.ISSOLD = 1 then 1 else 0 end,
                    @TSLONG = PROPERTYDETAIL.[TSLONG],
                    @UNSOLDAMOUNT = REVENUE.TRANSACTIONAMOUNT,
                    @BASECURRENCYID = PROPERTYDETAIL.BASECURRENCYID,
                    @TRANSACTIONCURRENCYID = PROPERTYDETAIL.TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID = PROPERTYDETAIL.BASEEXCHANGERATEID,
                    @EXCHANGERATE = 
                        case
                            when PROPERTYDETAIL.BASEEXCHANGERATEID is not null
                                then CURRENCYEXCHANGERATE.RATE
                            when PROPERTYDETAIL.TRANSACTIONCURRENCYID = PROPERTYDETAIL.BASECURRENCYID
                                then 1
                            else 0
                        end,
                    @HADSPOTRATE =  
                        case
                            when CURRENCYEXCHANGERATE.TYPECODE = 2
                                then 1
                            else 
                                0
                        end,
                    @RATECHANGED = 0,
                    @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(PROPERTYDETAIL.TRANSACTIONCURRENCYID)
                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 and [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 5 --Property



                -- 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;
                set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

                --replace with commented code for PBI 102747

                set @USERGRANTEDSPOTRATE = 1;
                /*set @USERGRANTEDSPOTRATE = case 
                    when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or 
                        dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '911f104d-ba5f-4469-b0ae-184c879aea99') = 1 
                            then 1
                            else 0
                    end;*/

                return 0;