USP_DATAFORMTEMPLATE_EDITLOAD_TRANSACTIONSELLPROPERTYADJUST

The load procedure used by the edit dataform template "Revenue Transaction Posted Sold 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
@ADJUSTMENTDATE	datetime	INOUT	Adjusted Date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjusted Post Date
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Post Status Code
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment reason
@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.

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_TRANSACTIONSELLPROPERTYADJUST (
                    @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
                )
                as                
                set nocount on;

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

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

                -- if adjustment already exists for this sold property detail

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

                select top 1
                    @DATALOADED = 1,
                    @SALEDATE = PROPERTYDETAIL.[SALEDATE],
                    @SALEAMOUNT = PROPERTYDETAIL.[SALEAMOUNT],
                    @BROKERFEE = PROPERTYDETAIL.[BROKERFEE],
                    @POSTSTATUSCODE = PROPERTYDETAIL.[SALEPOSTSTATUSCODE],
                    @POSTDATE = PROPERTYDETAIL.[SALEPOSTDATE],
                    @ALREADYSOLD = case when [REVENUEPAYMENTMETHOD].PAYMENTMETHODCODE = 5 and PROPERTYDETAIL.ISSOLD = 1 then 1 else 0 end,
                    @TSLONG = PROPERTYDETAIL.[TSLONG]
                from
                    dbo.[REVENUE] 
                inner join dbo.[REVENUEPAYMENTMETHOD] on [REVENUEPAYMENTMETHOD].REVENUEID = REVENUE.ID
                inner join dbo.[PROPERTYDETAIL] on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID                    
                where
                    [REVENUE].[ID] = @ID and [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 5 --Property


                select 
                    @UNSOLDAMOUNT = sum(REVENUE.AMOUNT)
                from
                    dbo.[REVENUE]
                where
                    [REVENUE].[ID] = @ID;

                return 0;