USP_DATAFORMTEMPLATE_VIEW_GIFTINKINDPAYMENTMETHODDETAILS

The load procedure used by the view dataform template "Gift-in-Kind Details 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	int	INOUT	Units sold
@UNITSREMAINING	int	INOUT	Units remaining
@TOTALSALEAMOUNT	money	INOUT	Total sale amount
@TOTALGAINLOSS	money	INOUT	Total gain/loss
@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)
@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_GIFTINKINDPAYMENTMETHODDETAILS
                (
                    @ID uniqueidentifier,
                    @UNITSSOLD int = null output,
                    @UNITSREMAINING int = null output,
                    @TOTALSALEAMOUNT money = null output,
                    @TOTALGAINLOSS money = null output,
                    @DATALOADED bit = 0 output,
                    @BASETOTALSALEAMOUNT money = null output,
                    @BASETOTALGAINLOSS 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(GIFTINKINDSALE.NUMBEROFUNITS), 0),
                        @UNITSREMAINING = coalesce(GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS, 0) - coalesce(sum(GIFTINKINDSALE.NUMBEROFUNITS), 0),
                        @TOTALSALEAMOUNT = coalesce(sum(GIFTINKINDSALE.TRANSACTIONSALEAMOUNT), 0),
                        @TOTALGAINLOSS = coalesce(sum(GIFTINKINDSALE.TRANSACTIONSALEAMOUNT), 0) - (GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONFAIRMARKETVALUE * coalesce(sum(GIFTINKINDSALE.NUMBEROFUNITS), 0)),
                        @BASETOTALSALEAMOUNT = coalesce(sum(GIFTINKINDSALE.SALEAMOUNT), 0),
                        @BASETOTALGAINLOSS = coalesce(sum(GIFTINKINDSALE.SALEAMOUNT), 0) - (GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE * coalesce(sum(GIFTINKINDSALE.NUMBEROFUNITS), 0)),
                        @DATALOADED = 1
                    from
                        dbo.GIFTINKINDPAYMENTMETHODDETAIL
                        left join dbo.GIFTINKINDSALE on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
                    where
                        GIFTINKINDPAYMENTMETHODDETAIL.ID = @ID
                    group by
                        GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS,
                        GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE,
                        GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONFAIRMARKETVALUE;

                    if @DATALOADED = 1
                    begin
                        select
                            @TRANSACTIONCURRENCYID = GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONCURRENCYID,
                            @BASECURRENCYID = GIFTINKINDPAYMENTMETHODDETAIL.BASECURRENCYID
                        from
                            dbo.GIFTINKINDPAYMENTMETHODDETAIL
                        where
                            GIFTINKINDPAYMENTMETHODDETAIL.ID = @ID;

                        set @BASEAMOUNTSAREVALID =
                            case 
                                when (@TRANSACTIONCURRENCYID = @BASECURRENCYID)
                                    then 1
                                when exists 
                                (
                                    select
                                        1
                                    from 
                                        dbo.GIFTINKINDSALE
                                    where 
                                        GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @ID
                                        and GIFTINKINDSALE.BASEEXCHANGERATEID is null
                                )
                                    then 0
                                else 1
                            end;

                    end

                return 0;