USP_DATAFORMTEMPLATE_PRELOAD_STOCKSALEBYTRANSACTION

The load procedure used by the edit dataform template "Stock 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.
@ISSUER	nvarchar(100)	INOUT	Issuer
@UNITSREMAINING	decimal(20, 3)	INOUT	Units remaining
@ORIGINALMEDIANPRICE	decimal(19, 4)	INOUT	Original median 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
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT	Transaction currency description
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@ALLOWGLDISTRIBUTIONS	bit	INOUT	Allow GL distributions
@PAYMENTAMOUNT	money	INOUT
@ORIGINALUNITS	decimal(20, 3)	INOUT

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_PRELOAD_STOCKSALEBYTRANSACTION
                    (
                        @REVENUEID uniqueidentifier,
                        @ISSUER nvarchar(100) = null output,
                        @UNITSREMAINING decimal(20,3) = null output,
                        @ORIGINALMEDIANPRICE 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,
                        @ALLOWGLDISTRIBUTIONS bit = null output,
                        @PAYMENTAMOUNT money = null output,
                        @ORIGINALUNITS decimal(20,3) = 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
                            @ISSUER = STOCKDETAIL.ISSUER,
                            @UNITSREMAINING = STOCKDETAIL.NUMBEROFUNITS - coalesce((select sum(NUMBEROFUNITS) from dbo.STOCKSALE where STOCKDETAILID = STOCKDETAIL.ID), 0),
                            @ORIGINALMEDIANPRICE = STOCKDETAIL.TRANSACTIONMEDIANPRICE,
                            @SALEPOSTDATE = convert(date, getdate()),
                            @SALEDATE = convert(date, getdate()),
                            @TRANSACTIONCURRENCYID = STOCKDETAIL.TRANSACTIONCURRENCYID,
                            @BASECURRENCYID = STOCKDETAIL.BASECURRENCYID,
                            @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(STOCKDETAIL.TRANSACTIONCURRENCYID),
                            @PAYMENTAMOUNT = REVENUEPAYMENTMETHOD.AMOUNT,
                            @ORIGINALUNITS = STOCKDETAIL.NUMBEROFUNITS
                        from
                            dbo.REVENUEPAYMENTMETHOD
                            inner join dbo.STOCKDETAIL on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.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 stock sale to do not post

                            @SALEPOSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 else 1 end
                        from
                            dbo.REVENUE
                        where
                            REVENUE.ID = @REVENUEID;