USP_SALESORDER_ADDPAYMENTREVENUE

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

    declare @SALESMETHODTYPECODE tinyint;
    select 
        @SALESMETHODTYPECODE = SALESMETHODTYPECODE
    from 
        dbo.SALESORDER with (nolock)
    where 
        ID = @SALESORDERID;

    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    declare @ALLOWGLDISTRIBUTIONS bit;
    declare @HASSPONSORSHIPS bit = 0;
    declare @CURRENCYISO nvarchar(3);

  if exists(select 'x' from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 12) 
  begin
    set @HASSPONSORSHIPS = 1;

    select top 1
        @PDACCOUNTSYSTEMID = SALESORDERITEMSPONSORSHIP.PDACCOUNTSYSTEMID,
        @ALLOWGLDISTRIBUTIONS = PDACCOUNTSYSTEM.ALLOWGLDISTRIBUTIONS,
        @CURRENCYISO = CURRENCYISO
      from dbo.SALESORDERITEMSPONSORSHIP
      inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMSPONSORSHIP.ID
    inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = SALESORDERITEMSPONSORSHIP.PDACCOUNTSYSTEMID
      where SALESORDERITEM.SALESORDERID = @SALESORDERID and SALESORDERITEM.TOTAL > 0;
   end

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

    declare @PAYMENTAMOUNT money;

    if @SALESMETHODTYPECODE = 3 begin
        select @PAYMENTAMOUNT = AMOUNT from dbo.[SALESORDERPAYMENT] where [PAYMENTID] = @ORDERPAYMENTID;
        exec dbo.USP_SALESORDER_CREATETOPDOWNDISTRIBUTIONS @REVENUEID, @SALESORDERID, @PDACCOUNTSYSTEMID, @ALLOWGLDISTRIBUTIONS, @CURRENTDATE, @CHANGEAGENTID, @ORDERPAYMENTID, @PAYMENTAMOUNT
    end
    else begin
        if @ORDERPAYMENTID is not null begin
            select @PAYMENTAMOUNT = AMOUNT from dbo.[SALESORDERPAYMENT] where [PAYMENTID] = @ORDERPAYMENTID;

            --insert the payment applications

            --This will copy the applications from the order revenue and appropriately split a partial payment

            exec dbo.USP_ORDER_COPYSPLITS @REVENUEID, @ORDERPAYMENTID, @CHANGEAGENTID, @CURRENTDATE, @PAYMENTAMOUNT, 10 

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

            if @ALLOWGLDISTRIBUTIONS = 1
                --gl distributions                    

                exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION    @ORDERPAYMENTID, @CHANGEAGENTID, @CURRENTDATE
        end
        else begin
            --create splits for sales order payments

            declare @PAYMENTID uniqueidentifier;
            declare @DEPOSITPOSTDATE date;
            declare @DEPOSITID uniqueidentifier;

            declare PAYMENTS_CURSOR cursor LOCAL FAST_FORWARD for
            select 
                PAYMENTID, 
                AMOUNT
            from 
                dbo.SALESORDERPAYMENT with (nolock)
            where 
                SALESORDERID = @SALESORDERID;

            open PAYMENTS_CURSOR
            fetch next from PAYMENTS_CURSOR into @PAYMENTID, @PAYMENTAMOUNT

            while @@FETCH_STATUS = 0
            begin
                --insert the payment applications

                --This will copy the applications from the order revenue and appropriately split a partial payment

                exec dbo.USP_ORDER_COPYSPLITS @REVENUEID, @PAYMENTID, @CHANGEAGENTID, @CURRENTDATE, @PAYMENTAMOUNT, 10 

                --------------------------------------------

                -- add sponsorship applications

                if @HASSPONSORSHIPS = 1 begin
                    declare @CONSTITUENTID uniqueidentifier
                    declare @TRANSACTIONDATE datetime
                    declare @TRANSACTIONCURRENCYID uniqueidentifier;
                    declare @BASECURRENCYID uniqueidentifier;

                    select @CONSTITUENTID = CONSTITUENTID,
                            @TRANSACTIONDATE = dbo.UFN_DATE_GETEARLIESTTIME(TRANSACTIONDATE)
                    from dbo.SALESORDER with (nolock)
                    where ID = @SALESORDERID;

                    /*
                        AS: WI 224281 - Pick the base currency of the account system's currency set as the
                        transaction currency. This change will only impact Sponsorship transactions.
                        LH: WI 326139  - Changing how base currency and transaction currency is determined.
                        base currency will be determined by the account system's base currency and the 
                        transaction currency will be determined by the currency on the merchant's account.
                        If the currencyISO on the incoming transaction is null, use the existing code from
                        WI 224281
                    */
                    if @CURRENCYISO is not null
                    begin
                        set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETCURRENCYFROMISO(@CURRENCYISO);
                        --

                        select @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
                        from dbo.PDACCOUNTSYSTEM
                        inner join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
                        where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
                    end
                    else
                    begin
                        select @TRANSACTIONCURRENCYID = CURRENCYSET.BASECURRENCYID,
                        @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
                        from dbo.PDACCOUNTSYSTEM
                        inner join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
                        where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
                    end

                    update dbo.REVENUE_EXT set
                        RECEIPTAMOUNT = @PAYMENTAMOUNT,
                        --WI#274833  Get proper receipt type for (3=recurring gift payment)

                        RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,3),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @PAYMENTID;

                    declare @TOTAL money, @ORGANIZATIONAPPLIEDAMOUNT money,
                    @DESIGNATIONID uniqueidentifier,@EXCHANGERATEID uniqueidentifier

                    declare @BASEEXCHANGERATEID uniqueidentifier,@BASEAMOUNT money
                    declare @ORGANIZATIONCURRENCYID uniqueidentifier
                    set  @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                    -- WI 257256  We need the proper org exchange rate and org amount on the revenued record.

                    set @ORGANIZATIONAPPLIEDAMOUNT = @PAYMENTAMOUNT
                    set @BASEAMOUNT =  @PAYMENTAMOUNT

                    set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @BASECURRENCYID, @CURRENTDATE,1,null)

                    if @BASEEXCHANGERATEID is not null
                        set @BASEAMOUNT = dbo.UFN_CURRENCY_CONVERT(@PAYMENTAMOUNT, @BASEEXCHANGERATEID)

                    if @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                    begin
                        set @EXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENTDATE,null,null)

                        if @EXCHANGERATEID is not null
                            set @ORGANIZATIONAPPLIEDAMOUNT = dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@BASEAMOUNT,@EXCHANGERATEID,@PAYMENTAMOUNT)
                    end
                    else
                        set @ORGANIZATIONAPPLIEDAMOUNT = @BASEAMOUNT

                    update dbo.FINANCIALTRANSACTION set
                        TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                        ORGEXCHANGERATEID = @EXCHANGERATEID,
                        ORGAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE,
                        BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                        BASEAMOUNT = @BASEAMOUNT
                    where ID = @PAYMENTID;

                    declare SPONSORSHIPSPLITS cursor local fast_forward for 
                    select
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                        SALESORDERITEM.TOTAL,
                        SALESORDERITEMSPONSORSHIP.ID
                    from 
                        dbo.SALESORDERITEMSPONSORSHIP
                    inner join 
                        dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMSPONSORSHIP.ID
                    inner join 
                        dbo.SPONSORSHIP on SPONSORSHIP.ID = SALESORDERITEMSPONSORSHIP.SPONSORSHIPID
                    inner join 
                        dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = SPONSORSHIP.REVENUESPLITID
                    where 
                        SALESORDERITEM.SALESORDERID = @SALESORDERID
                        and SALESORDERITEM.TOTAL > 0;

                    declare @APPLICATIONID uniqueidentifier
                    declare @APPLICATIONAMOUNT money
                    declare @SALESORDERITEMID uniqueidentifier

                    OPEN SPONSORSHIPSPLITS
                    fetch next from SPONSORSHIPSPLITS into @APPLICATIONID, @APPLICATIONAMOUNT, @SALESORDERITEMID

                    while @@FETCH_STATUS = 0
                    begin
                        exec dbo.USP_RECURRINGGIFT_ADDPAYMENT @PAYMENTID, @APPLICATIONID, @APPLICATIONAMOUNT, @CONSTITUENTID, @TRANSACTIONDATE,    @CURRENTDATE, @CHANGEAGENTID
                        fetch next from SPONSORSHIPSPLITS into @APPLICATIONID, @APPLICATIONAMOUNT, @SALESORDERITEMID
                    end

                    close SPONSORSHIPSPLITS
                    deallocate SPONSORSHIPSPLITS

                    --insert statements for additional donation on sponsorship

                    declare SPONSORSHIPADDITIONALSPLITS cursor local fast_forward for 
                    select
                        TOTAL,
                        DESIGNATIONID
                    from 
                        dbo.SALESORDERITEM
                    inner join 
                        dbo.SALESORDERITEMDONATION on SALESORDERITEM.ID = SALESORDERITEMDONATION.ID
                    where 
                        SALESORDERITEM.CATEGORYNAME = 'Sponsorship' and SALESORDERITEM.TYPECODE = 2 -- Donation

                        and SALESORDERITEM.SALESORDERID = @SALESORDERID;

                    OPEN SPONSORSHIPADDITIONALSPLITS
                    fetch next from SPONSORSHIPADDITIONALSPLITS into @TOTAL, @DESIGNATIONID

                    while @@FETCH_STATUS = 0
                    begin
                        declare @LINEITEMID uniqueidentifier = newid();
                        insert into dbo.FINANCIALTRANSACTIONLINEITEM 
                        (
                            ID,
                            FINANCIALTRANSACTIONID,
                            TYPECODE,
                            BASEAMOUNT,
                            TRANSACTIONAMOUNT,
                            ORGAMOUNT,
                            POSTDATE,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        ) 
                        values 
                        (
                            @LINEITEMID,
                            @PAYMENTID,
                            0,
                            dbo.UFN_CURRENCY_CONVERT(@TOTAL, @BASEEXCHANGERATEID),
                            @TOTAL,
                            dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(@TOTAL, @EXCHANGERATEID, @TOTAL),
                            isnull(@DEPOSITPOSTDATE, @TRANSACTIONDATE),
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );

                        insert into dbo.REVENUESPLIT_EXT 
                        (
                            ID,
                            APPLICATIONCODE,
                            DESIGNATIONID,
                            TYPECODE,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        values 
                        (
                            @LINEITEMID,
                            0,
                            @DESIGNATIONID,
                            17,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );

                        -- create recognitions

                        declare @REVENUEGIVENANONYMOUSLY bit;
                        select @REVENUEGIVENANONYMOUSLY = R.GIVENANONYMOUSLY
                        from dbo.REVENUE_EXT R
                        where R.ID = @PAYMENTID;

                        insert into dbo.REVENUERECOGNITION
                        (
                            REVENUESPLITID, 
                            CONSTITUENTID, 
                            AMOUNT,
                            EFFECTIVEDATE,
                            REVENUERECOGNITIONTYPECODEID,
                            ORGANIZATIONAMOUNT,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID
                        )
                        select
                            @LINEITEMID,
                            RECOGNITIONS.CONSTITUENTID,
                            RECOGNITIONS.AMOUNT,
                            @TRANSACTIONDATE,
                            RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
                            dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(RECOGNITIONS.AMOUNT, @EXCHANGERATEID, RECOGNITIONS.AMOUNT),
                            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @BASECURRENCYID
                        from 
                            dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(@REVENUEGIVENANONYMOUSLY, @CONSTITUENTID, @TOTAL, @TRANSACTIONDATE, null) as RECOGNITIONS;

                        fetch next from SPONSORSHIPADDITIONALSPLITS into @TOTAL, @DESIGNATIONID
                    end

                    close SPONSORSHIPADDITIONALSPLITS
                    deallocate SPONSORSHIPADDITIONALSPLITS
                end
                --------------------------------------------


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

                if @ALLOWGLDISTRIBUTIONS = 1
                begin
                    --gl distributions                    

                    exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION    @PAYMENTID,    @CHANGEAGENTID, @CURRENTDATE

                    if @DEPOSITID is not null
                        exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @PAYMENTID, @DEPOSITID, @CHANGEAGENTID, @CURRENTDATE;
                end

                fetch next from PAYMENTS_CURSOR into @PAYMENTID, @PAYMENTAMOUNT
            end

            close PAYMENTS_CURSOR
            deallocate PAYMENTS_CURSOR
        end
    end

    return 0;
end