UFN_SALESORDER_TOTALS

CREATE function dbo.UFN_SALESORDER_TOTALS(@SALESORDERID uniqueidentifier)
returns table
as return (
    with SALESORDERITEM_CTE as (
        select
            ID,
            TYPECODE,
            PRICINGSTRUCTURECODE,
            TOTAL
        from
            dbo.SALESORDERITEM
        where
            SALESORDERID = @SALESORDERID
    ),
    PAYMENTS_CTE as (
        select
            isnull(sum(AMOUNTTENDERED), 0) as AMOUNTTENDERED,
            isnull(sum(AMOUNT), 0) as AMOUNTPAID,
            isnull(sum(CHANGEDUE), 0) as CHANGEDUE,
            count(*) as NUMBEROFPAYMENTS
        from dbo.SALESORDERPAYMENT
        where SALESORDERID = @SALESORDERID
    ),
    SECURITYDEPOSIPAYMENTS_CTE as (
        select isnull(sum(AMOUNT), 0) as SECURITYDEPOSITAMOUNTPAID
        from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
        where RESERVATIONID = @SALESORDERID
    ),
    TOTALS_CTE as (
        select
            isnull(sum(
                case SALESORDERITEM_CTE.TYPECODE
                    when 5 then  -- Discount

                        -SALESORDERITEM_CTE.TOTAL
                    else
                        SALESORDERITEM_CTE.TOTAL
                end

                - ITEMDISCOUNTTOTAL.AMOUNT - MEMBERSHIPPROMOANDAPPLIEDTICKETSTOTAL.APPLIEDAMOUNT - MEMBERSHIPPROMOANDAPPLIEDTICKETSTOTAL.PROMOAMOUNT
            ), 0) as TOTAL,
            isnull(sum(case when SALESORDERITEM_CTE.TYPECODE = 4 then SALESORDERITEM_CTE.TOTAL else 0 end), 0) as TAXES,
            isnull(sum(case when SALESORDERITEM_CTE.TYPECODE = 3 then SALESORDERITEM_CTE.TOTAL else 0 end), 0) as FEES,
            isnull(sum(case when SALESORDERITEM_CTE.TYPECODE = 5 then SALESORDERITEM_CTE.TOTAL else 0 end), 0) as ORDERDISCOUNTS,
            isnull(sum(ITEMDISCOUNTTOTAL.AMOUNT), 0) as ITEMDISCOUNTS,
            isnull(sum(MEMBERSHIPPROMOANDAPPLIEDTICKETSTOTAL.APPLIEDAMOUNT), 0) as APPLIEDTICKETS,
            isnull(sum(MEMBERSHIPPROMOANDAPPLIEDTICKETSTOTAL.PROMOAMOUNT), 0) as MEMBERSHIPPROMOTIONS,
            isnull(sum(case when SALESORDERITEM_CTE.PRICINGSTRUCTURECODE = 1 then SALESORDERITEM_CTE.TOTAL else 0 end), 0) as FLATRATEAPPLIEDAMOUNT
        from
            SALESORDERITEM_CTE
        outer apply (
            select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
            from dbo.SALESORDERITEMITEMDISCOUNT
            where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM_CTE.ID
        ) as ITEMDISCOUNTTOTAL
        outer apply (
            select
                isnull(sum(case when SALESORDERITEMMEMBERSHIPITEMPROMOTION.MEMBERSHIPPROMOID is null then SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT else 0 end), 0) as APPLIEDAMOUNT,
                isnull(sum(case when SALESORDERITEMMEMBERSHIPITEMPROMOTION.MEMBERSHIPPROMOID is not null then SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT else 0 end), 0) as PROMOAMOUNT
            from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
            where SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM_CTE.ID
        ) as MEMBERSHIPPROMOANDAPPLIEDTICKETSTOTAL
    ),
    SALESORDERINDIVIDUALTOTALS_CTE as (
        select
            (
                select AMOUNT
                from dbo.RESERVATIONRATESCALEPRICE
                where RESERVATIONRATESCALEID = @SALESORDERID and INUSE = 1
            ) as FLATRATEAMOUNT,
            isnull((
                select sum(CREDITPAYMENT.AMOUNT)
                from dbo.CREDITPAYMENT
                inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = CREDITPAYMENT.REVENUEID
                where SALESORDERPAYMENT.SALESORDERID = @SALESORDERID
            ), 0) as REFUNDS
    ),
    SALESORDERITEMTOTALS_CTE as (
        select
            (
                TOTAL
                +
                case
                    when FLATRATEAMOUNT is null then
                        0
                    else
                        case
                            when FLATRATEAMOUNT <> FLATRATEAPPLIEDAMOUNT then
                                (FLATRATEAMOUNT - FLATRATEAPPLIEDAMOUNT)
                            else
                                0
                        end
                end
                - TAXES
            ) as SUBTOTAL,
            TAXES,
            ORDERDISCOUNTS,
            ITEMDISCOUNTS,
            APPLIEDTICKETS,
            MEMBERSHIPPROMOTIONS,
            (ORDERDISCOUNTS + ITEMDISCOUNTS + APPLIEDTICKETS + MEMBERSHIPPROMOTIONS) as TOTALDISCOUNTS,
            REFUNDS,
            (
                TOTAL
                +
                case
                    when FLATRATEAMOUNT is null then
                        0
                    else
                        case
                            when FLATRATEAMOUNT <> FLATRATEAPPLIEDAMOUNT then
                                (FLATRATEAMOUNT - FLATRATEAPPLIEDAMOUNT)
                            else
                                0
                        end
                end
            ) as TOTAL
        from
            TOTALS_CTE
        outer apply
            SALESORDERINDIVIDUALTOTALS_CTE
    )
    select
        SALESORDERITEMTOTALS_CTE.SUBTOTAL,
        SALESORDERITEMTOTALS_CTE.TAXES,
        SALESORDERITEMTOTALS_CTE.ORDERDISCOUNTS,
        SALESORDERITEMTOTALS_CTE.ITEMDISCOUNTS,
        SALESORDERITEMTOTALS_CTE.APPLIEDTICKETS,
        SALESORDERITEMTOTALS_CTE.MEMBERSHIPPROMOTIONS,
        SALESORDERITEMTOTALS_CTE.TOTALDISCOUNTS,
        SALESORDERITEMTOTALS_CTE.REFUNDS,
        SALESORDERITEMTOTALS_CTE.TOTAL,
        PAYMENTS_CTE.AMOUNTTENDERED,
        case
            when SALESORDER.SALESMETHODTYPECODE = 3 then  -- Group Sales should never have change due

                cast(0 as money)
            when SALESORDER.STATUSCODE <> 1 then  -- Incomplete orders may have payments where the change due field needs to be updated

                PAYMENTS_CTE.AMOUNTTENDERED - SALESORDERITEMTOTALS_CTE.TOTAL
            else
                PAYMENTS_CTE.CHANGEDUE
        end as CHANGEDUE,
        PAYMENTS_CTE.AMOUNTPAID,
        PAYMENTS_CTE.NUMBEROFPAYMENTS,
        (
            SALESORDERITEMTOTALS_CTE.TOTAL
            -
                case
                    -- Depending on how things are added/removed to the card

                    -- the AMOUNTPAID can be off so it's best to use tendered until complete

                    when SALESORDER.STATUSCODE <> 1 then  -- Complete

                        PAYMENTS_CTE.AMOUNTTENDERED
                    else
                        PAYMENTS_CTE.AMOUNTPAID
                end
            +
                case
                    when SALESORDER.SALESMETHODTYPECODE = 3 then  -- Group Sales

                        SALESORDERITEMTOTALS_CTE.REFUNDS + OVERAGEKEPT.AMOUNT
                    else
                        0
                end
        ) as BALANCE,
        SECURITYDEPOSIPAYMENTS_CTE.SECURITYDEPOSITAMOUNTPAID,
        OVERAGEKEPT.AMOUNT as OVERAGEKEPT
    from
        dbo.SALESORDER
    outer apply
        SALESORDERITEMTOTALS_CTE
    outer apply
        PAYMENTS_CTE
    outer apply
        SECURITYDEPOSIPAYMENTS_CTE
    outer apply (
        select isnull(sum(LI.BASEAMOUNT), 0) as AMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join dbo.REVENUESPLIT_EXT as EXT on EXT.ID = LI.ID
        where
            LI.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
            and EXT.TYPECODE = 20  -- Overage

            and LI.DELETEDON is null
            and LI.TYPECODE = 0  -- Standard

    ) as OVERAGEKEPT
    where
        SALESORDER.ID = @SALESORDERID
)