USP_DATAFORMTEMPLATE_EDITLOAD_TRANSACTIONSELLPROPERTYADJUST_2

The load procedure used by the edit dataform template "Revenue Transaction Posted Sold Property Edit Form 2"

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
@ADJUSTMENTDATE	datetime	INOUT	Adjustment date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjustment post date
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Post status code
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment details
@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.
@ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@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

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_TRANSACTIONSELLPROPERTYADJUST_2
                (
                    @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,
                    @ADJUSTMENTDATE datetime = null output,
                    @ADJUSTMENTPOSTDATE datetime = null output,
                    @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
                    @ADJUSTMENTREASON nvarchar(300) = null output,
                    @TSLONG bigint = 0 output,
                    @ADJUSTMENTREASONCODEID uniqueidentifier = 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
                )
                as                
                set nocount on;

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

                select  @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                        @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                        @ADJUSTMENTPOSTSTATUSCODE = 0;

                -- if adjustment already exists for this sold property detail

                select top 1
                    @ADJUSTMENTDATE = DATE,
                    @ADJUSTMENTPOSTDATE = POSTDATE,
                    @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE,
                    @ADJUSTMENTREASON = REASON,
                    @ADJUSTMENTREASONCODEID = REASONCODEID
                from dbo.PROPERTYDETAILADJUSTMENT
                where PROPERTYDETAILID in (select REVENUEPAYMENTMETHOD.ID from dbo.REVENUEPAYMENTMETHOD where REVENUEPAYMENTMETHOD.REVENUEID = @ID) and POSTSTATUSCODE <> 0;

                select top 1
                    @DATALOADED = 1,
                    @SALEDATE = PROPERTYDETAIL.[SALEDATE],
                    @SALEAMOUNT = PROPERTYDETAIL.[TRANSACTIONSALEAMOUNT],
                    @BROKERFEE = PROPERTYDETAIL.[TRANSACTIONBROKERFEE],
                    @POSTSTATUSCODE = 
                        case 
                            when REVENUE.DONOTPOST = 1
                                then 2
                            else
                                PROPERTYDETAIL.[SALEPOSTSTATUSCODE]
                        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


                return 0;