USP_SALESORDER_ADDBENEFITS

CREATE procedure dbo.USP_SALESORDER_ADDBENEFITS
(
    @SALESORDERID uniqueidentifier,
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime
)
as begin
    set nocount on;

    declare @BENEFITS table (
        BENEFITID uniqueidentifier,
        DETAILS text,
        QUANTITY int,
        SEQUENCE int,
        UNITVALUE money,
        PERCENTAPPLICABLEAMOUNT money,
        VALUEPERCENT int,
        BASECURRENCYID uniqueidentifier,
        REVENUESPLITID uniqueidentifier
    )

    insert into @BENEFITS
    select 
        MEMBERSHIPLEVELBENEFIT.BENEFITID,
        MEMBERSHIPLEVELBENEFIT.DETAILS,
        SUM(MEMBERSHIPLEVELBENEFIT.QUANTITY) as QUANTITY,
        MEMBERSHIPLEVELBENEFIT.SEQUENCE,
        MEMBERSHIPLEVELBENEFIT.UNITVALUE, 
        SALESORDERITEM.TOTAL - isnull((
      select sum(AMOUNT) from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION P where P.SALESORDERITEMID = SALESORDERITEM.ID
      ), 0) as PERCENTAPPLICABLEAMOUNT,
        MEMBERSHIPLEVELBENEFIT.VALUEPERCENT,
        MEMBERSHIPLEVELBENEFIT.BASECURRENCYID as BASECURRENCYID,
        MEMBERSHIPTRANSACTION.REVENUESPLITID
    from dbo.SALESORDER
        inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        inner join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
        inner join dbo.MEMBERSHIPTRANSACTION on SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
        inner join dbo.MEMBERSHIPLEVEL ON SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
        inner join dbo.MEMBERSHIPLEVELBENEFIT on MEMBERSHIPLEVEL.ID = MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID
    where SALESORDER.REVENUEID = @REVENUEID
        and (MEMBERSHIPTRANSACTION.ACTIONCODE = 0 or (MEMBERSHIPTRANSACTION.ACTIONCODE <> 0 and MEMBERSHIPLEVELBENEFIT.FREQUENCYCODE = 0))
    group by
        MEMBERSHIPLEVELBENEFIT.BENEFITID, MEMBERSHIPLEVELBENEFIT.UNITVALUE, MEMBERSHIPLEVELBENEFIT.DETAILS, MEMBERSHIPLEVELBENEFIT.SEQUENCE,
        MEMBERSHIPLEVELBENEFIT.VALUEPERCENT, SALESORDERITEM.TOTAL, SALESORDERITEM.ID,
        MEMBERSHIPLEVELBENEFIT.BASECURRENCYID, MEMBERSHIPTRANSACTION.REVENUESPLITID

    union all
    select 
        BENEFITID,
        DETAILS,
        sum(QUANTITY) as QUANTITY,
        0 as SEQUENCE,
        VALUE,
        sum(PERCENTAPPLICABLEAMOUNT) as PERCENTAPPLICABLEAMOUNT,
        0 as VALUEPERCENT,
        BASECURRENCYID,
        REVENUESPLITID
    -- From: Getting registration benefits grouped by REGISTRANTREGISTRATION so we can properly total PERCENTAPPLICABLEAMOUNT

    -- If we were to sum REGISTRANTREGISTRATION.AMOUNT below, the total would be a multiple of the number of registrants in that registration + a multiple of the other registrations that share a benefit and split

    from (
        select
            REGISTRANTBENEFIT.BENEFITID,
            REGISTRANTBENEFIT.DETAILS,
            sum(REGISTRANTBENEFIT.QUANTITY) as QUANTITY,
            UNIT.VALUE,
            REGISTRANTREGISTRATION.AMOUNT as PERCENTAPPLICABLEAMOUNT,
            REGISTRANTBENEFIT.BASECURRENCYID as BASECURRENCYID,
            FINANCIALTRANSACTIONLINEITEM.ID as REVENUESPLITID
        from (
            select 
                SALESORDERITEMEVENTREGISTRATION.REGISTRANTID as REGISTRANTID, 
                dbo.UFN_EVENTREGISTRANT_GETBALANCE(SALESORDERITEMEVENTREGISTRATION.REGISTRANTID) as BALANCE
            from dbo.SALESORDERITEM with (nolock)
            inner join dbo.SALESORDERITEMEVENTREGISTRATION with (nolock) on
                SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
            where SALESORDERITEM.SALESORDERID = @SALESORDERID
        ) as [REGISTRATIONS]
        inner join (
            select
                ID,
                ID as JOINID,
                BENEFITSWAIVED
            from dbo.REGISTRANT with (nolock)

            union all
            select
                ID,
                GUESTOFREGISTRANTID as JOINID,
                BENEFITSWAIVED
            from
                dbo.REGISTRANT with (nolock)
        ) as REGISTRANTS on REGISTRANTS.JOINID = REGISTRATIONS.REGISTRANTID
        inner join dbo.REGISTRANTREGISTRATIONMAP with (nolock) on 
            REGISTRANTS.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
        inner join dbo.REGISTRANTREGISTRATION with (nolock) on 
            REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
        inner join dbo.EVENTPRICE with (nolock) on 
            REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
        inner join dbo.REGISTRANTBENEFIT with (nolock)
            on REGISTRANTS.ID = REGISTRANTBENEFIT.REGISTRANTID
        outer apply (
            select
                case
                    when REGISTRANTREGISTRATION.AMOUNT = 0 and EVENTPRICE.AMOUNT > 0  then 0
                    else REGISTRANTBENEFIT.UNITVALUE
                end VALUE
        ) UNIT
        inner join dbo.EVENTREGISTRANTPAYMENT with (nolock) on 
            [REGISTRATIONS].REGISTRANTID = EVENTREGISTRANTPAYMENT.REGISTRANTID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on
            FINANCIALTRANSACTIONLINEITEM.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
        where 
            REGISTRANTS.BENEFITSWAIVED = 0 and
            [REGISTRATIONS].BALANCE  = 0 and
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and 
            FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID is null -- Do not get the line item for the designation here.

        group by
            REGISTRANTBENEFIT.BENEFITID, 
            REGISTRANTBENEFIT.DETAILS, 
            REGISTRANTBENEFIT.BASECURRENCYID,
            FINANCIALTRANSACTIONLINEITEM.ID,
            REGISTRANTREGISTRATION.ID,
            REGISTRANTREGISTRATION.AMOUNT,
            UNIT.VALUE
    ) as [BENEFITDATA]
    group by
        BENEFITID, 
        DETAILS, 
        BASECURRENCYID,
        REVENUESPLITID,
        VALUE;

    if @@rowcount > 0 begin
        declare @UNITVALUESUMS table (
            ID uniqueidentifier,
            UNITSUM money
        )

        insert into @UNITVALUESUMS
        select REVENUESPLITID, sum(UNITVALUE) from @BENEFITS
        group by REVENUESPLITID

        update @BENEFITS
        set PERCENTAPPLICABLEAMOUNT -= (select UNITSUM from @UNITVALUESUMS where REVENUESPLITID = ID)
        where PERCENTAPPLICABLEAMOUNT > 0

        update @BENEFITS
        set PERCENTAPPLICABLEAMOUNT = 0
        where PERCENTAPPLICABLEAMOUNT < 0

        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        declare @ORIGINCODE tinyint;

        select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0)
        from dbo.MULTICURRENCYCONFIGURATION

        declare @REVENUETRANSACTIONCURRENCYID uniqueidentifier

        select
            @REVENUETRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID
        from
            dbo.FINANCIALTRANSACTION as FT
        inner join
            dbo.REVENUE_EXT as EXT on EXT.ID = FT.ID
        where
            FT.ID = @REVENUEID
            and FT.DELETEDON is null

        insert into dbo.REVENUEBENEFIT
        (
            REVENUEID,
            BENEFITID,
            QUANTITY,
            UNITVALUE,
            DETAILS,
            SEQUENCE,
            ADDEDBYID,
            CHANGEDBYID,
            SENDBENEFIT,
            PERCENTAPPLICABLEAMOUNT,
            VALUEPERCENT,
            TRANSACTIONTOTALVALUE,
            ORGANIZATIONTOTALVALUE,
            BASECURRENCYID,
            TRANSACTIONCURRENCYID,
            BASEEXCHANGERATEID,
            ORGANIZATIONEXCHANGERATEID,
            REVENUESPLITID
        )
        (
            select 
                @REVENUEID,
                BENEFITID,
                QUANTITY,
                UNITVALUE,
                DETAILS,
                SEQUENCE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                0, -- Send benefit: 0 to create gl distribution when committed.

                PERCENTAPPLICABLEAMOUNT,
                VALUEPERCENT,
                TRANSACTIONTOTALVALUE,
                case
                    when @ORIGINCODE = 0 then
                        case
                            when BASECURRENCYID = @ORGANIZATIONCURRENCYID
                                then TOTALVALUE
                            else dbo.UFN_CURRENCY_CONVERT(TOTALVALUE, ORGANIZATIONEXCHANGERATEID)
                        end
                    when @ORIGINCODE = 1 then
                        case                    
                            when @REVENUETRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID  
                                then CONVERSION.TRANSACTIONTOTALVALUE
                            else dbo.UFN_CURRENCY_CONVERT(CONVERSION.TRANSACTIONTOTALVALUE, ORGANIZATIONEXCHANGERATEID)
                        end
                end ORGANIZATIONTOTALVALUE,
                BASECURRENCYID,
                @REVENUETRANSACTIONCURRENCYID TRANSACTIONCURRENCYID,
                BASEEXCHANGERATEID,
                ORGANIZATIONEXCHANGERATEID,
                REVENUESPLITID
            from(
                select 
                    BENEFITID,
                    QUANTITY,
                    UNITVALUE,
                    cast(coalesce((QUANTITY * UNITVALUE),0) + coalesce((PERCENTAPPLICABLEAMOUNT * VALUEPERCENT * 0.01),0) as money) TOTALVALUE,
                    DETAILS,
                    SEQUENCE,
                    PERCENTAPPLICABLEAMOUNT,
                    VALUEPERCENT,
                    BASECURRENCYID,
                    REVENUESPLITID,
                    case
                        when BASECURRENCYID <> @REVENUETRANSACTIONCURRENCYID
                            then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(BASECURRENCYID,@REVENUETRANSACTIONCURRENCYID, @CURRENTDATE, 1, null)
                        else null
                    end BASEEXCHANGERATEID,
                    case
                        when @ORIGINCODE = 0 then
                            case
                                when BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                                    then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null)
                                else null
                            end
                        when @ORIGINCODE = 1 then
                            case                    
                                when @REVENUETRANSACTIONCURRENCYID <> @ORGANIZATIONCURRENCYID
                                    then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@REVENUETRANSACTIONCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null)
                                else null
                            end
                    end ORGANIZATIONEXCHANGERATEID
                from @BENEFITS
            ) BASE
            outer apply(
                select 
                    case
                        when BASECURRENCYID <> @REVENUETRANSACTIONCURRENCYID
                            then dbo.UFN_CURRENCY_CONVERT(TOTALVALUE, BASEEXCHANGERATEID)
                        else TOTALVALUE
                    end TRANSACTIONTOTALVALUE
            ) CONVERSION
        )
    end
end