USP_MEMBERSHIPDUESBATCH_VALIDATE_PAYPLEDGEDMEMBERSHIP

CREATE procedure dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_PAYPLEDGEDMEMBERSHIP
(
    @MEMBERSHIPTRANSACTIONTYPECODE tinyint,
    @TRANSACTIONDATE datetime,
    @EXPIRATIONDATE datetime,
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @MEMBERSHIPLEVELID uniqueidentifier,
    @MEMBERSHIPLEVELTERMID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier,
    @REVENUETYPECODE tinyint,
    @EXISTINGMEMBERSHIPID uniqueidentifier,
    @MEMBERSHIPPROGRAMADDON xml,
    @EXISTINGMEMBERS xml,
    @MEMBERSHIPCARDS xml,
    @EXISTINGCHILDREN xml,            -- not currently supported

  @NUMBEROFCHILDREN smallint    -- Temporary workaround for children not being implemented in 2012 Q1

)
as begin
    declare @CANBEPAIDINFULL bit = 0;
    declare @CURRENTSTATUS tinyint = 0;
    declare @JOINDATE datetime;
    declare @ORIGINALPROGRAMID uniqueidentifier;
    declare @ORIGINALLEVELID uniqueidentifier;
    declare @ORIGINALTERMID uniqueidentifier;
    declare @ORIGINALEXPIRATIONDATE datetime;
    declare @EXISTINGMEMBERSHIPTRANSACTIONID uniqueidentifier;

    --Get the pledge or recurring gift that this payment is being applied to

    select top 1 
        @EXISTINGMEMBERSHIPTRANSACTIONID = FINANCIALTRANSACTION.ID,
        @CURRENTSTATUS = STATUSCODE
    from dbo.MEMBERSHIPTRANSACTION
    inner join dbo.MEMBERSHIP 
        on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM 
        on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
    left join dbo.REVENUESPLIT_EXT
        on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    left join dbo.FINANCIALTRANSACTION 
        on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    left join dbo.REVENUE_EXT
        on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    where
        MEMBERSHIPTRANSACTION.MEMBERSHIPID = @EXISTINGMEMBERSHIPID and
        (
            STATUSCODE = 2 or
            (
                FINANCIALTRANSACTION.TYPECODE in (2,15)
                and FINANCIALTRANSACTION.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                and ((FINANCIALTRANSACTION.TYPECODE = 15 and MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= coalesce(MEMBERSHIP.LASTRENEWEDON, MEMBERSHIP.JOINDATE)) or (FINANCIALTRANSACTION.TYPECODE = 2 and MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= MEMBERSHIP.JOINDATE))
            )
        )
    order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE desc

    if @EXISTINGMEMBERSHIPTRANSACTIONID is null and @CURRENTSTATUS <> 2
        raiserror('BBERR_NOCOMMITMENTFORPAY', 13, 1)

    select 
        @ORIGINALPROGRAMID = MEMBERSHIPPROGRAMID,
        @ORIGINALLEVELID = MEMBERSHIPLEVELID,
        @ORIGINALTERMID = MEMBERSHIPLEVELTERMID,
        @ORIGINALEXPIRATIONDATE = EXPIRATIONDATE,
        @JOINDATE = JOINDATE
    from dbo.MEMBERSHIP 
    where ID = @EXISTINGMEMBERSHIPID;

    if @MEMBERSHIPPROGRAMID <> @ORIGINALPROGRAMID
        raiserror('BBERR_INVALIDPROGRAMFORPAY', 13, 1);

    if @MEMBERSHIPLEVELID <> @ORIGINALLEVELID
        raiserror('BBERR_INVALIDLEVELFORPAY', 13, 1);

    if @MEMBERSHIPLEVELTERMID <> @ORIGINALTERMID
        raiserror('BBERR_INVALIDTERMFORPAY', 13, 1);

    if dbo.UFN_DATE_GETEARLIESTTIME(@EXPIRATIONDATE) <> dbo.UFN_DATE_GETEARLIESTTIME(@ORIGINALEXPIRATIONDATE)
        raiserror('BBERR_INVALIDEXPIRATIONDATEFORPAY', 13, 1);

    if exists (
            select * 
            from dbo.MEMBERSHIPTRANSACTION 
            where MEMBERSHIPID = @EXISTINGMEMBERSHIPID and 
                TRANSACTIONDATE > @TRANSACTIONDATE
    ) and @CURRENTSTATUS <> 2
        raiserror('BBERR_DATEEARLIERTHANLASTMEMBERSHIPTRANSACTION', 13,1);

    if @EXPIRATIONDATE < @JOINDATE
        raiserror('BBERR_EXPIRATIONDATEB4JOINDATE', 13, 1);

    select 
        @CANBEPAIDINFULL = MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM
    from
        dbo.MEMBERSHIPPROGRAM
        inner join dbo.MEMBERSHIPLEVEL 
            on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
    where
        MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID
        and MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID

    if @CANBEPAIDINFULL = 0 and @REVENUETYPECODE = 0
        raiserror('BBERR_PROGRAMCANNOTBEPAIDINFULL', 13, 1);

    if exists (
            select 
                T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') 
            from @EXISTINGMEMBERS.nodes('/EXISTINGMEMBERS/ITEM') T(c)
        )
        raiserror('BBERR_NOADDITIONALMEMBERSWHENPAYING', 13, 1);

    if exists (
            select 
                T.c.value('ADDONID [1]', 'uniqueidentifier')            
            from @MEMBERSHIPPROGRAMADDON.nodes('/MEMBERSHIPPROGRAMADDON/ITEM') T(c)
            where T.c.value('APPLY [1]', 'bit') = 1
                and T.c.value('NUMBEROFADDONS [1]', 'int') > 0
        )
        raiserror('BBERR_NOADDONSWHENPAYING', 13, 1);

    if exists (
            select 
                T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
            from @EXISTINGCHILDREN.nodes('/EXISTINGCHILDREN/ITEM') T(c)
        )
        raiserror('BBERR_NOCHILDRENWHENPAYING', 13, 1);

  if @NUMBEROFCHILDREN <> (select NUMBEROFCHILDREN from dbo.MEMBERSHIP where ID = @EXISTINGMEMBERSHIPID)
        raiserror('BBERR_NOCHILDRENWHENPAYING', 13, 1);

    if exists (
        select 
            T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
        from @MEMBERSHIPCARDS.nodes('/MEMBERSHIPCARDS/ITEM') T(c)
        )
        raiserror('BBERR_NOCARDSWHENPAYING', 13, 1);

end