USP_SALESORDER_ADDREVENUE

CREATE procedure dbo.USP_SALESORDER_ADDREVENUE
(
    @SALESORDERID uniqueidentifier,
    @REVENUEID uniqueidentifier,
    @TRANSACTIONDATE datetime,
    @CONSTITUENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime,
    @ISCOMPLETEDORDEREDITWITHNOADJUSTMENT bit = 0,
    @COMPLETEDORDERADJUSTMENTID uniqueidentifier = null
)
as
begin
    declare @AMOUNT money

    select 
        @AMOUNT = isnull(sum(SALESORDERITEM.TOTAL),0)
    from
        dbo.SALESORDERITEM with (nolock)
    where
        SALESORDERITEM.SALESORDERID = @SALESORDERID and
        SALESORDERITEM.TYPECODE not in (5,12,13) --exclude discounts, sponsorships, and membership promotions


    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @ALLOWGLDISTRIBUTIONS bit;
    declare @CURRENCYSETID uniqueidentifier;

    /** Bug Fix 177959 - Don't hardcode the PDACCOUNTSYSTEMID instead pick the default one **/
    select
        @PDACCOUNTSYSTEMID = ID,
        @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS,
        @CURRENCYSETID = CURRENCYSETID
    from 
        dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();

    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    select 
        @TRANSACTIONCURRENCYID = BASECURRENCYID
    from 
        dbo.CURRENCYSET
    where 
        ID= @CURRENCYSETID

    if @TRANSACTIONCURRENCYID is null
        set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

    declare @POSTDATE date = null;
    declare @POSTSTATUSCODE tinyint = 3;  -- Do not post


    if @ALLOWGLDISTRIBUTIONS = 1 begin
        set @POSTDATE = @TRANSACTIONDATE;
        set @POSTSTATUSCODE = 1;  -- Not posted

    end

    if @COMPLETEDORDERADJUSTMENTID is null and @ISCOMPLETEDORDEREDITWITHNOADJUSTMENT = 0 begin
        insert into dbo.FINANCIALTRANSACTION
        (
            ID,
            CONSTITUENTID,
            DATE,
            POSTDATE,
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGAMOUNT,
            TYPECODE,
            POSTSTATUSCODE,
            TRANSACTIONCURRENCYID,
            PDACCOUNTSYSTEMID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @REVENUEID,
            @CONSTITUENTID,
            @TRANSACTIONDATE,
            @POSTDATE,
            @AMOUNT,
            @AMOUNT,
            @AMOUNT,
            5, --Order

            @POSTSTATUSCODE,
            @TRANSACTIONCURRENCYID,
            @PDACCOUNTSYSTEMID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

        declare @REFERENCE nvarchar(255);
        select @REFERENCE = 'Order-' + convert(nvarchar(50),(select SALESORDER.SEQUENCEID from dbo.SALESORDER with (nolock) where SALESORDER.ID = @SALESORDERID));

        insert into dbo.REVENUE_EXT
        (
            ID,
            REFERENCE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (    
            @REVENUEID,
            @REFERENCE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )

        --Add origination source

        exec dbo.USP_REVENUE_ADDORIGIN @REVENUEID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

        update dbo.SALESORDER with (rowlock)
        set REVENUEID = @REVENUEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @SALESORDERID;

        --add payment method details

        insert into dbo.REVENUEPAYMENTMETHOD 
        (
            ID,
            REVENUEID, 
            PAYMENTMETHODCODE, 
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        values 
        (
            newid(),
            @REVENUEID,
            9, -- none

            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );
    end;

    --create the payment application(s)

    declare @APPLICATIONS table
    (
        ID uniqueidentifier,
        AMOUNT money,
        APPLICATIONCODE tinyint,
        TYPECODE tinyint,
        PROGRAMID uniqueidentifier,
        EVENTID uniqueidentifier,
        FEEID uniqueidentifier,
        TAXID uniqueidentifier,
        SALESORDERITEMID uniqueidentifier,
        RESOURCEID uniqueidentifier,
        VOLUNTEERTYPEID uniqueidentifier,
        EVENTLOCATIONID uniqueidentifier,
        MERCHANDISEPRODUCTINSTANCEID uniqueidentifier
    )

    insert into @APPLICATIONS
    select 
        newid(),
        AMOUNT, 
        APPLICATIONCODE, 
        TYPECODE, 
        PROGRAMID, 
        EVENTID, 
        FEEID, 
        TAXID, 
        SALESORDERITEMID, 
        RESOURCEID, 
        VOLUNTEERTYPEID, 
        EVENTLOCATIONID, 
        MERCHANDISEPRODUCTINSTANCEID
    from 
        dbo.UFN_SALESORDER_GETAPPLICATIONSFORPAYMENT(@SALESORDERID, @AMOUNT)
    where 
        AMOUNT > 0 or 
        (AMOUNT = 0 and TYPECODE = 2 and -- Membership

        (
            select 
                sum(ADDON.TOTAL) 
            from 
                dbo.SALESORDERITEM ADDON
            inner join 
                dbo.SALESORDERITEMMEMBERSHIPADDON on ADDON.ID = SALESORDERITEMMEMBERSHIPADDON.ID
            where 
                SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMID) > 0
        );

    --Add Splits

    insert into dbo.FINANCIALTRANSACTIONLINEITEM
    (
        ID,
        FINANCIALTRANSACTIONID,
        TRANSACTIONAMOUNT,
        BASEAMOUNT,
        ORGAMOUNT,
        POSTDATE,
        POSTSTATUSCODE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED,
        FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    )
    select
        temp.ID,
        @REVENUEID,
        temp.AMOUNT,
        temp.AMOUNT,
        temp.AMOUNT,
        @POSTDATE,
        @POSTSTATUSCODE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        @COMPLETEDORDERADJUSTMENTID
    from 
        @APPLICATIONS temp
    where 
        temp.APPLICATIONCODE not in (1,5); -- Membership and Event Registration


    insert into dbo.REVENUESPLIT_EXT
    (
        ID,
        APPLICATIONCODE,
        DESIGNATIONID,
        TYPECODE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        temp.ID,
        temp.APPLICATIONCODE,
        SOID.DESIGNATIONID,
        temp.TYPECODE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from 
        @APPLICATIONS temp
    left join 
        dbo.SALESORDERITEMDONATION as SOID on temp.SALESORDERITEMID = SOID.ID
    where 
        temp.APPLICATIONCODE not in (1,5); -- Membership and Event Registration


    --add revenuesplits to item donation records

    update dbo.SALESORDERITEMDONATION
    set REVENUESPLITID = APPS.ID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from @APPLICATIONS APPS
    where APPS.SALESORDERITEMID = SALESORDERITEMDONATION.ID

    --insert recognitions if appropriate

    --only need to do this in a giving situation, is it necessary for tickets only?

    if @CONSTITUENTID is not null
        exec dbo.USP_REVENUE_CREATERECOGNITIONS @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, null

    declare @APPLICATIONID uniqueidentifier
    declare @MEMBERSHIPS xml
    declare @APPLICATIONAMOUNT money
    declare @APPLICATIONCODE tinyint
    declare @SALESORDERITEMID uniqueidentifier
    declare @MEMBERSHIPTRANSACTIONID uniqueidentifier

    --add membership splits

    if exists
    (
        select 1
        from @APPLICATIONS
        where APPLICATIONCODE = 5
    )
    begin

        declare memberships_cursor cursor LOCAL FAST_FORWARD for
        select 
            SALESORDERITEMMEMBERSHIP.MEMBERSHIPID as APPLICATIONID,
            dbo.UFN_SALESORDER_GETMEMBERSHIPITEMS_TOXML(SALESORDERITEMMEMBERSHIP.ID),
            APPS.AMOUNT,
            APPS.APPLICATIONCODE,
            SALESORDERITEMMEMBERSHIP.ID as ID
        from 
            @APPLICATIONS APPS
        inner join 
            dbo.SALESORDERITEMMEMBERSHIP on APPS.SALESORDERITEMID = SALESORDERITEMMEMBERSHIP.ID
        where 
            APPS.APPLICATIONCODE = 5

        OPEN memberships_cursor
        fetch next from memberships_cursor into
        @APPLICATIONID, @MEMBERSHIPS, @APPLICATIONAMOUNT, @APPLICATIONCODE, @SALESORDERITEMID

        while @@FETCH_STATUS = 0
        begin
            exec dbo.USP_MEMBERSHIP_ADDFROMSALES @REVENUEID, @CONSTITUENTID, @APPLICATIONID output,    @APPLICATIONAMOUNT,    @TRANSACTIONDATE,
                @MEMBERSHIPS, @CURRENTDATE,    @CHANGEAGENTID,    @MEMBERSHIPTRANSACTIONID output

            update dbo.SALESORDERITEMMEMBERSHIP
            set 
                SALESORDERITEMMEMBERSHIP.MEMBERSHIPID = @APPLICATIONID,
                MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where SALESORDERITEMMEMBERSHIP.ID = @SALESORDERITEMID

            update dbo.SALESORDERITEMMEMBERSHIPADDON
            set
                MEMBERSHIPID = @APPLICATIONID,
                MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID,
                MEMBERSHIPADDONID = MEMBERSHIPADDON.ID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from 
                dbo.SALESORDERITEMMEMBERSHIPADDON
            inner join 
                dbo.MEMBERSHIPADDON    on MEMBERSHIPADDON.MEMBERSHIPID = @APPLICATIONID and
                    SALESORDERITEMMEMBERSHIPADDON.ADDONID = MEMBERSHIPADDON.ADDONID and
                    MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID
            where 
                SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @SALESORDERITEMID

            insert into dbo.REVENUESPLITORDER
            (
                ID,
                MEMBERSHIPADDONID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                T.addons.value('(SPLITID)[1]','uniqueidentifier'),
                SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPADDONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from @MEMBERSHIPS.nodes('/MEMBERSHIPFIELDS/ITEM/ADDONS/ITEM') T(addons)
            inner join dbo.SALESORDERITEMMEMBERSHIPADDON
                on SALESORDERITEMMEMBERSHIPADDON.ID = T.addons.value('(SALESORDERITEMID)[1]','uniqueidentifier')

            set @MEMBERSHIPTRANSACTIONID = null

            fetch next from memberships_cursor into
                @APPLICATIONID, @MEMBERSHIPS, @APPLICATIONAMOUNT, @APPLICATIONCODE, @SALESORDERITEMID
        end

        close memberships_cursor
        deallocate memberships_cursor

        insert into dbo.REVENUESPLITORDER
        (
            ID, 
            MEMBERSHIPLEVELID,
            DESIGNATIONID,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        select 
            MEMBERSHIPTRANSACTION.REVENUESPLITID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            null as DESIGNATIONID,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from 
            dbo.MEMBERSHIPTRANSACTION
        inner join 
            dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
        where 
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID

        --Add the associated donation (for contributed memberships) to the revenuesplitorder table

        union all
        select
            FINANCIALTRANSACTIONLINEITEM.ID,
            null as MEMBERSHIPLEVELID,
            REVENUESPLIT_EXT.DESIGNATIONID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from 
            dbo.MEMBERSHIPTRANSACTION
        inner join 
            dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
        inner join 
            dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where 
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
            and REVENUESPLIT_EXT.TYPECODE = 0 -- Gift

            and REVENUESPLIT_EXT.APPLICATIONCODE = 0 -- Donation

    end

    --add event registration splits

    if exists
    (
        select 
            1
        from 
            @APPLICATIONS
        where 
            APPLICATIONCODE = 1
    )
    begin
        declare eventregistrations_cursor cursor LOCAL FAST_FORWARD for
        select 
            SALESORDERITEMEVENTREGISTRATION.REGISTRANTID as APPLICATIONID,
            APPS.AMOUNT,
            APPS.APPLICATIONCODE,
            SALESORDERITEMEVENTREGISTRATION.ID as ID
        from @APPLICATIONS APPS
        inner join 
            dbo.SALESORDERITEMEVENTREGISTRATION on APPS.SALESORDERITEMID = SALESORDERITEMEVENTREGISTRATION.ID
        where 
            APPS.APPLICATIONCODE = 1;

        open eventregistrations_cursor
        fetch next from eventregistrations_cursor into
            @APPLICATIONID, @APPLICATIONAMOUNT, @APPLICATIONCODE, @SALESORDERITEMID

        while @@FETCH_STATUS = 0
        begin
            exec dbo.USP_EVENT_ADDPAYMENT @REVENUEID, @APPLICATIONID, @APPLICATIONAMOUNT, @CURRENTDATE,    @CHANGEAGENTID

            fetch next from eventregistrations_cursor into
                @APPLICATIONID, @APPLICATIONAMOUNT, @APPLICATIONCODE, @SALESORDERITEMID
        end

        close eventregistrations_cursor
        deallocate eventregistrations_cursor

        insert into dbo.REVENUESPLITORDER
        (
            ID, 
            EVENTID, 
            DESIGNATIONID,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        )
        select 
            EVENTREGISTRANTPAYMENT.PAYMENTID,
            case 
                when REVENUESPLIT_EXT.DESIGNATIONID is not null then null
                else REGISTRANT.EVENTID
            end,
            REVENUESPLIT_EXT.DESIGNATIONID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from dbo.EVENTREGISTRANTPAYMENT
        inner join 
            dbo.REGISTRANT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
        inner join 
            dbo.FINANCIALTRANSACTIONLINEITEM on EVENTREGISTRANTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join 
            dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where 
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID;
    end

    insert into dbo.REVENUESPLITORDER
    (
        ID, 
        PROGRAMID, 
        EVENTID, 
        FEEID, 
        TAXID, 
        RESOURCEID,
        VOLUNTEERTYPEID, 
        EVENTLOCATIONID, 
        MERCHANDISEPRODUCTINSTANCEID, 
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
    )
    select 
        temp.ID,
        temp.PROGRAMID,
        temp.EVENTID,
        temp.FEEID,
        temp.TAXID,
        temp.RESOURCEID,
        temp.VOLUNTEERTYPEID,
        temp.EVENTLOCATIONID,
        temp.MERCHANDISEPRODUCTINSTANCEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from 
        @APPLICATIONS temp
    where 
        temp.TYPECODE in (5,6,7, 10, 11, 14, 16);

    --add benefits

    exec dbo.USP_SALESORDER_ADDBENEFITS @SALESORDERID, @REVENUEID, @CHANGEAGENTID, @CURRENTDATE

    --add receipt amounts

    exec dbo.USP_SALESORDER_ADDRECEIPTAMOUNTS @SALESORDERID, @REVENUEID, @CHANGEAGENTID, @CURRENTDATE

    exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
        @ID = @REVENUEID,
        @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
        @CHANGEDATE = @CURRENTDATE,
        @CHANGEAGENTID = @CHANGEAGENTID

    if @ALLOWGLDISTRIBUTIONS = 1 begin
        --gl distributions

        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION    @REVENUEID,    @CHANGEAGENTID, @CURRENTDATE
        exec dbo.USP_SAVE_BENEFITGLDISTRIBUTION_SALESORDER @REVENUEID,    @CHANGEAGENTID, @CURRENTDATE
    end
end