USP_DATAFORMTEMPLATE_VIEW_BATCHMEMBERSHIPDUESROWCONTEXT

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@PRIMARYMEMBERNAME	nvarchar(100)	INOUT
@PRIMARYMEMBERLOOKUPID	nvarchar(20)	INOUT
@PROMONAME	nvarchar(100)	INOUT
@PROMOTIONTYPECODE	tinyint	INOUT
@PROMODISCOUNTCALCULATIONTYPECODE	tinyint	INOUT
@PROMOAMOUNT	money	INOUT
@PROMOPERCENT	decimal(5, 2)	INOUT
@PROMOEXTENSIONCALCULATIONTYPECODE	tinyint	INOUT
@PROMOEXTENSIONVALUE	int	INOUT
@RECEIPTAMOUNT	money	INOUT
@TOTALAMOUNT	money	INOUT
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT
@ADDITIONALMEMBERS	xml	INOUT
@BENEFITS	xml	INOUT
@ADDONS	xml	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BATCHMEMBERSHIPDUESROWCONTEXT
(
    @ID uniqueidentifier
    , @DATALOADED bit = 0 output
    , @PRIMARYMEMBERNAME nvarchar(100) = null output
    , @PRIMARYMEMBERLOOKUPID nvarchar(20) = null output
    , @PROMONAME nvarchar(100) = null output
    , @PROMOTIONTYPECODE tinyint = null output
    , @PROMODISCOUNTCALCULATIONTYPECODE tinyint = null output
    , @PROMOAMOUNT money = null output
    , @PROMOPERCENT decimal(5,2) = null output
    , @PROMOEXTENSIONCALCULATIONTYPECODE tinyint = null output
    , @PROMOEXTENSIONVALUE integer = null output
    , @RECEIPTAMOUNT money = null output
    , @TOTALAMOUNT money = null output
    , @TRANSACTIONCURRENCYID uniqueidentifier = null output
    , @ADDITIONALMEMBERS xml = null output
    , @BENEFITS xml = null output
    , @ADDONS xml = null output
)
as
    set nocount on;

  --to fix the 'record specified does not exist' error when adding new rows through batch grid
    set @DATALOADED = 1;

    --None of this actually matters; data is sent over from the batch handler directly.
    select
        @PRIMARYMEMBERNAME =  C.NAME
        , @PRIMARYMEMBERLOOKUPID = C.LOOKUPID
        , @PROMONAME =
            case when BMD.PROMOTIONCODE is not null
                then 'Promo code'
                else MP.[NAME]
            end
        , @PROMOTIONTYPECODE = MP.PROMOTIONTYPECODE
        , @PROMODISCOUNTCALCULATIONTYPECODE = MP.DISCOUNTCALCULATIONTYPECODE
        , @PROMOAMOUNT = MP.[AMOUNT]
        , @PROMOPERCENT = MP.[PERCENT]
        , @PROMOEXTENSIONCALCULATIONTYPECODE = MP.EXTENSIONCALCULATIONTYPECODE
        , @PROMOEXTENSIONVALUE = MP.EXTENSIONVALUE
        , @RECEIPTAMOUNT = BMD.TAXDEDUCTIBLEAMOUNT
        , @TOTALAMOUNT = BMD.TOTALAMOUNT
        , @TRANSACTIONCURRENCYID = BMD.TRANSACTIONCURRENCYID
        , @ADDITIONALMEMBERS = dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERSFORCONTEXTVIEW_TOITEMLISTXML(@ID)
        , @BENEFITS = dbo.UFN_MEMBERSHIPDUESBATCH_GETALLBENEFITSFORCONTEXTVIEW_TOITEMLISTXML(@ID)
        , @ADDONS = dbo.UFN_MEMBERSHIPDUESBATCH_GETADDONSFORCONTEXTVIEW_TOITEMLISTXML(@ID)
        from dbo.BATCHMEMBERSHIPDUES BMD
            inner join dbo.CONSTITUENT C
                on C.ID = case
                    when BMD.DUESTYPECODE = 0 then BMD.BILLTOCONSTITUENTID -- Paying for membership
                    else BMD.MEMBERSHIPRECIPIENTID     -- Giving or comping membership
                end
            left join dbo.MEMBERSHIPPROMO MP
                on coalesce (BMD.APPLIEDDISCOUNTID, BMD.MEMBERSHIPPROMOID) = MP.ID
        where BMD.ID = @ID

    return 0;