USP_DATAFORMTEMPLATE_PRELOAD_GIFTINKINDSALEBYTRANSACTION

The load procedure used by the edit dataform template "Gift-in-Kind Sale By Transaction Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@ITEMNAME	nvarchar(100)	INOUT	Item name
@UNITSREMAINING	int	INOUT	Units remaining
@ORIGINALFAIRMARKETVALUE	decimal(19, 4)	INOUT	Original fair market price
@SALEDATE	datetime	INOUT	Date of sale
@SALEPOSTDATE	datetime	INOUT	GL post date
@SALEPOSTSTATUSCODE	tinyint	INOUT	GL post status
@TRANSACTIONDONOTPOST	bit	INOUT	Transaction marked do not post
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT	Transaction currency description
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Base currency ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@REVENUEREFERENCE	nvarchar(255)	INOUT	Reference
@ALLOWGLDISTRIBUTIONS	bit	INOUT	Allow GL distributions
@HADSPOTRATE	bit	INOUT	Had spot rate
@RATECHANGED	bit	INOUT	Rate changed

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_PRELOAD_GIFTINKINDSALEBYTRANSACTION
                (
                    @REVENUEID uniqueidentifier,
                    @ITEMNAME nvarchar(100) = null output,
                    @UNITSREMAINING integer = null output,
                    @ORIGINALFAIRMARKETVALUE decimal(19,4) = null output,
                    @SALEDATE datetime = null output,
                    @SALEPOSTDATE datetime = null output,
                    @SALEPOSTSTATUSCODE tinyint = null output,
                    @TRANSACTIONDONOTPOST bit = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
                    @BASEEXCHANGERATEID uniqueidentifier = null output,
                    @EXCHANGERATE decimal(20,8) = null output,
                    @REVENUEREFERENCE nvarchar(255) = null output,
                    @ALLOWGLDISTRIBUTIONS bit = null output,
                    @HADSPOTRATE bit = null output,
                    @RATECHANGED bit = null output
                )
                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.PDACCOUNTSYSTEMFORREVENUE
                    where ID = @REVENUEID;

                    set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

                    select
                        @ITEMNAME = GIFTINKINDPAYMENTMETHODDETAIL.ITEMNAME,
                        @UNITSREMAINING = GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS - coalesce((select sum(NUMBEROFUNITS) from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDPAYMENTMETHODDETAIL.ID), 0),
                        @ORIGINALFAIRMARKETVALUE = GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONFAIRMARKETVALUE,
                        @SALEPOSTDATE = convert(date, getdate()),
                        @SALEDATE = convert(date, getdate()),
                        @TRANSACTIONCURRENCYID = GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONCURRENCYID,
                        @BASECURRENCYID = GIFTINKINDPAYMENTMETHODDETAIL.BASECURRENCYID,
                        @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONCURRENCYID),
                        @REVENUEREFERENCE = REVENUEREFERENCE.REFERENCE
                    from
                        dbo.REVENUEPAYMENTMETHOD
                        inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
                        inner join dbo.REVENUEREFERENCE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUEREFERENCE.ID
                    where
                        REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID;

                    set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@SALEDATE,1,null);

                    set @EXCHANGERATE =
                        case
                            when @BASEEXCHANGERATEID is not null
                                then (select CURRENCYEXCHANGERATE.RATE from dbo.CURRENCYEXCHANGERATE where CURRENCYEXCHANGERATE.ID = @BASEEXCHANGERATEID)
                            when @TRANSACTIONCURRENCYID = @BASECURRENCYID
                                then 1
                            else 0
                        end;

                    select
                        @TRANSACTIONDONOTPOST = REVENUE.DONOTPOST,
                        -- If the transaction is set to do not post, default the gift-in-kind sale to do not post
                        @SALEPOSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 else 1 end
                    from
                        dbo.REVENUE
                    where
                        REVENUE.ID = @REVENUEID;