USP_DATAFORMTEMPLATE_VIEW_STOCKSALETOTALS

The load procedure used by the view dataform template "Stock Sale Totals View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@UNITSSOLD	decimal(20, 3)	INOUT	Units sold
@UNITSREMAINING	decimal(20, 3)	INOUT	Units remaining
@TOTALSALEAMOUNT	money	INOUT	Total sale amount
@TOTALGAINLOSS	money	INOUT	Total gain/loss
@TOTALPROCEEDS	money	INOUT	Total proceeds
@TOTALFEES	money	INOUT	Total fees
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@BASETOTALSALEAMOUNT	money	INOUT	Total sale amount (base)
@BASETOTALGAINLOSS	money	INOUT	Total gain/loss (base)
@BASETOTALPROCEEDS	money	INOUT	Total proceeds (base)
@BASETOTALFEES	money	INOUT	Total fees (base)
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@BASEAMOUNTSAREVALID	bit	INOUT	Base amounts are valid

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STOCKSALETOTALS
                (
                    @ID uniqueidentifier,
                    @UNITSSOLD decimal(20,3) = null output,
                    @UNITSREMAINING decimal(20,3) = null output,
                    @TOTALSALEAMOUNT money = null output,
                    @TOTALGAINLOSS money = null output,
                    @TOTALPROCEEDS money = null output,
                    @TOTALFEES money = null output,
                    @DATALOADED bit = 0 output,
                    @BASETOTALSALEAMOUNT money = null output,
                    @BASETOTALGAINLOSS money = null output,
                    @BASETOTALPROCEEDS money = null output,
                    @BASETOTALFEES money = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @BASEAMOUNTSAREVALID bit = null output
                )
                as
                    set nocount on

                    set @TRANSACTIONCURRENCYID = null;
                    set @BASECURRENCYID = null;
                    set @BASEAMOUNTSAREVALID = 0;

                    select
                        @UNITSSOLD = coalesce(sum(STOCKSALE.NUMBEROFUNITS), 0),
                        @UNITSREMAINING = coalesce(STOCKDETAIL.NUMBEROFUNITS, 0) - coalesce(sum(STOCKSALE.NUMBEROFUNITS), 0),
                        @TOTALSALEAMOUNT = coalesce(sum(STOCKSALE.TRANSACTIONSALEAMOUNT), 0),
                        @TOTALGAINLOSS = coalesce(sum(STOCKSALE.TRANSACTIONSALEAMOUNT), 0) - ((coalesce(REVENUEPAYMENTMETHOD.AMOUNT,0)/coalesce(STOCKDETAIL.NUMBEROFUNITS, 1)) * coalesce(sum(STOCKSALE.NUMBEROFUNITS), 0)),
                        @TOTALPROCEEDS = coalesce(sum(STOCKSALE.TRANSACTIONSALEAMOUNT), 0) - coalesce(sum(STOCKSALE.TRANSACTIONFEE), 0),
                        @TOTALFEES = coalesce(sum(STOCKSALE.TRANSACTIONFEE), 0),
                        @BASETOTALSALEAMOUNT = coalesce(sum(STOCKSALE.SALEAMOUNT), 0),
                        @BASETOTALGAINLOSS = coalesce(sum(STOCKSALE.SALEAMOUNT), 0) - (STOCKDETAIL.MEDIANPRICE * coalesce(sum(STOCKSALE.NUMBEROFUNITS), 0)),
                        @BASETOTALPROCEEDS = coalesce(sum(STOCKSALE.SALEAMOUNT), 0) - coalesce(sum(STOCKSALE.FEE), 0),
                        @BASETOTALFEES = coalesce(sum(STOCKSALE.FEE), 0),
                        @DATALOADED = 1
                    from
                        dbo.STOCKDETAIL
                        left join dbo.STOCKSALE on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
                        inner join dbo.REVENUEPAYMENTMETHOD on STOCKDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                    where
                        REVENUEPAYMENTMETHOD.REVENUEID = @ID
                    group by
                        STOCKDETAIL.NUMBEROFUNITS,
                        STOCKDETAIL.MEDIANPRICE,
                        STOCKDETAIL.TRANSACTIONMEDIANPRICE,
                        REVENUEPAYMENTMETHOD.AMOUNT;

                    if @DATALOADED = 1
                    begin
                        select
                            @TRANSACTIONCURRENCYID = STOCKDETAIL.TRANSACTIONCURRENCYID,
                            @BASECURRENCYID = STOCKDETAIL.BASECURRENCYID
                        from
                            dbo.STOCKDETAIL
                            inner join dbo.REVENUEPAYMENTMETHOD on STOCKDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                        where
                            REVENUEPAYMENTMETHOD.REVENUEID = @ID;

                        set @BASEAMOUNTSAREVALID =
                            case 
                                when (@TRANSACTIONCURRENCYID = @BASECURRENCYID)
                                    then 1
                                when exists 
                                (
                                    select
                                        1
                                    from 
                                        dbo.STOCKSALE
                                        inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                                    where 
                                        REVENUEPAYMENTMETHOD.REVENUEID = @ID
                                        and STOCKSALE.BASEEXCHANGERATEID is null
                                )
                                    then 0
                                else 1
                            end;

                    end

                return 0;