USP_REVENUEBATCH_APPLYBUSINESSUNITS

CREATE procedure dbo.USP_REVENUEBATCH_APPLYBUSINESSUNITS
(
    @REVENUEID uniqueidentifier,
    @APPLICATIONBUSINESSUNITS xml,
    @TYPECODE tinyint,
    @CHANGEAGENTID uniqueidentifier= null,
    @REVENUESPLITSPONSORSHIPID xml = null,
    @REVENUESTREAMS xml = null,
    @ADDITIONALAPPLICATIONSSTREAM xml = null

)
as
begin

    declare @APPLICATIONID uniqueidentifier;
    declare @BUSINESSUNITS xml;
    declare @REASON uniqueidentifier;
    declare @REVENUESPLITID uniqueidentifier;
    declare @OVERRIDEBUSINESSUNITS bit;
    declare @APPLIEDAMOUNT money;
    declare @BUSINESSUNITITEMAMOUNT money;
    declare @REVENUESPLITIDFETCH uniqueidentifier;

    if @CHANGEAGENTID is null
    begin
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
    end
   --WI 194341/208872 Need to be able to discriminate between more than one additional sponsorship donation.

     declare @REVENUESPLITSPONSORSHIPTABLE table
     (
          REVENUESPLITID uniqueidentifier,
          SPONSORSHIPID uniqueidentifier
     )  
    insert into @REVENUESPLITSPONSORSHIPTABLE(REVENUESPLITID,SPONSORSHIPID)
     select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') as REVENUESPLITID,
     T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier') as SPONSORSHIPID
     from @REVENUESPLITSPONSORSHIPID.nodes('REVENUESPLITSPONSORSHIPID/ITEM') T(c)  

    if @TYPECODE = 0
    begin
        declare @ADDITIONALAPPLICATIONDECLINESGIFTAID bit;
      declare @ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier;


        if @APPLICATIONBUSINESSUNITS is not null
        begin
            declare APPLICATIONS_PAYMENT cursor local fast_forward for 
            select 
                T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
                case when T.c.exist('./BUSINESSUNITS/ITEM') = 1 then cast(T.c.query('BUSINESSUNITS') as xml) else null end as BUSINESSUNITS,
                T.c.value('(REASON)[1]','uniqueidentifier') as REASON,
                T.c.value('(OVERRIDEBUSINESSUNITS)[1]','bit') as OVERRIDEBUSINESSUNITS,
                T.c.value('(ADDITIONALAPPLICATIONDECLINESGIFTAID)[1]','bit') as ADDITIONALAPPLICATIONDECLINESGIFTAID,
                T.c.value('(ADDITIONALAPPLICATIONSPONSORSHIPID)[1]','uniqueidentifier') as ADDITIONALAPPLICATIONSPONSORSHIPID,
                T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') as REVENUESPLITIDFETCH
            from @APPLICATIONBUSINESSUNITS.nodes('/APPLICATIONBUSINESSUNITS/ITEM') T(c)

            open APPLICATIONS_PAYMENT
            fetch next from APPLICATIONS_PAYMENT into @APPLICATIONID, @BUSINESSUNITS, @REASON, @OVERRIDEBUSINESSUNITS, @ADDITIONALAPPLICATIONDECLINESGIFTAID, @ADDITIONALAPPLICATIONSPONSORSHIPID, @REVENUESPLITIDFETCH

            while @@FETCH_STATUS = 0
            begin
                -- Validate only if we have values in the fields to validate

                if(@BUSINESSUNITS is not null and (@REVENUESTREAMS is not null or @ADDITIONALAPPLICATIONSSTREAM is not null))
                begin
                    set @BUSINESSUNITITEMAMOUNT = 0
                    set @APPLIEDAMOUNT = 0

                    select @BUSINESSUNITITEMAMOUNT = T.c.value('(AMOUNT)[1]','money') 
                    from @BUSINESSUNITS.nodes('/BUSINESSUNITS/ITEM') T(c);

                    -- Get applied amount from REVENUESTREAMS

                    if(@REVENUESTREAMS is not null)
                        select @APPLIEDAMOUNT = APPLIED 
                        from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS) 
                        where 
                            (REVENUESPLITID is not null and REVENUESPLITID = @REVENUESPLITIDFETCH) --In case of RUB, @APPLICATIONBUSINESSUNITS have REVENUESPLITID.

                            or
                            (APPLICATIONID is not null and APPLICATIONID = @APPLICATIONID) --In case of ERB, @APPLICATIONBUSINESSUNITS have APPLICATIONID and not REVENUESPLITID.


                    -- Try get applied amount from ADDITIONALAPPLICATIONSSTREAM, if not found in REVENUESTREAMS

                    if(@APPLIEDAMOUNT = 0 and @ADDITIONALAPPLICATIONSSTREAM is not null)
                        select @APPLIEDAMOUNT = APPLIED 
                        from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_FROMITEMLISTXML(@ADDITIONALAPPLICATIONSSTREAM) 
                        where ID = @APPLICATIONID

                    -- Check if BUSINESSUNITITEMAMOUNT is greater than APPLIEDAMOUNT    

                    if(@BUSINESSUNITITEMAMOUNT > @APPLIEDAMOUNT)
                    begin
                        raiserror('BBERR_APPLICATIONBUSINESSUNITS_INCORRECTAMOUNT', 13, 1);
                        return
                    end
                end

                    --For each revenue split in the application id apply the same business units

                    declare @REVENUESPLITS table(REVENUESPLIT uniqueidentifier);
                    declare @VALID bit=0;

                    if exists(select ID from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = @APPLICATIONID)
                    begin
                        --Payment to recurring gift

                        insert into @REVENUESPLITS
                        select
                            REVENUESPLIT.ID
                        from dbo.RECURRINGGIFTACTIVITY
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
                        where SOURCEREVENUEID= @APPLICATIONID 
                            and REVENUESPLIT.REVENUEID = @REVENUEID   

                        set @VALID = 1;
                    end

                    if exists(select ID from dbo.INSTALLMENTSPLITPAYMENT where PLEDGEID = @APPLICATIONID)
                    begin
                        --Payment to pledge

                        insert into @REVENUESPLITS
                        select
                            REVENUESPLIT.ID
                        from dbo.INSTALLMENTSPLITPAYMENT
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                        where PLEDGEID = @APPLICATIONID 
                            and REVENUESPLIT.REVENUEID = @REVENUEID
                            and 
                            (
                                (@REVENUESPLITIDFETCH is not null and @REVENUESPLITIDFETCH = REVENUESPLIT.ID) --In case of RUB, @APPLICATIONBUSINESSUNITS have REVENUESPLITID.

                                or 
                                (@REVENUESPLITIDFETCH is null) --In case of ERB, @APPLICATIONBUSINESSUNITS do not have REVENUESPLITID.

                            )

                        set @VALID = 1;
                    end

                    if @VALID=0
                    begin
                        --Donation

                        if exists(    
                            select REVENUESPLIT.ID 
                            from dbo.REVENUESPLIT
                                inner join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
                            where REVENUESPLIT.DESIGNATIONID = @APPLICATIONID 
                                and REVENUESPLIT.REVENUEID = @REVENUEID 
                                and REVENUESPLIT.APPLICATIONCODE = 0 
                                and REVENUESPLITGIFTAID.DECLINESGIFTAID = @ADDITIONALAPPLICATIONDECLINESGIFTAID
                        )
                        begin
                            insert into @REVENUESPLITS
                            select REVENUESPLIT.ID 
                            from dbo.REVENUESPLIT
                                inner join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
                            where REVENUESPLIT.DESIGNATIONID = @APPLICATIONID 
                                and REVENUESPLIT.REVENUEID = @REVENUEID 
                                and REVENUESPLIT.APPLICATIONCODE = 0 
                                and REVENUESPLITGIFTAID.DECLINESGIFTAID = @ADDITIONALAPPLICATIONDECLINESGIFTAID
                        end
                        else
                        begin
                            --Bug 246052 - AnkushGu - Handle the cases for transaction different from Donation

                            if exists(select 1 from dbo.REVENUESPLIT
                                    where REVENUESPLIT.REVENUEID = @REVENUEID
                                    and (REVENUESPLIT.APPLICATIONCODE = 0 or REVENUESPLIT.APPLICATIONCODE = 4 or REVENUESPLIT.APPLICATIONCODE = 7))
                            begin
                                --Bug 178636 - AdamBu - 2/24/12 - Join to the batch table to make sure we narrow the

                                --    splits down by designation.

                                insert into @REVENUESPLITS
                                select
                                    REVENUESPLIT.ID
                                from dbo.REVENUESPLIT 
                                    inner join dbo.BATCHREVENUEADDITIONALAPPLICATIONS on BATCHREVENUEADDITIONALAPPLICATIONS.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
                                    left join @REVENUESPLITSPONSORSHIPTABLE RST on RST.REVENUESPLITID = REVENUESPLIT.ID
                                where REVENUESPLIT.REVENUEID = @REVENUEID 
                                    and BATCHREVENUEADDITIONALAPPLICATIONS.ID = @APPLICATIONID
                                    and (REVENUESPLIT.APPLICATIONCODE = 0 or REVENUESPLIT.APPLICATIONCODE = 4 or REVENUESPLIT.APPLICATIONCODE = 7)
                                    and coalesce(RST.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(@ADDITIONALAPPLICATIONSPONSORSHIPID,'00000000-0000-0000-0000-000000000000');
                            end

                            if exists(select 1 from dbo.REVENUESPLIT
                                    inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.REVENUESPLITID = REVENUESPLIT.ID
                                    where REVENUESPLIT.REVENUEID = @REVENUEID
                                    and REVENUESPLIT.APPLICATIONCODE = 1)
                            begin
                                insert into @REVENUESPLITS
                                select
                                    REVENUESPLIT.ID
                                from dbo.REVENUESPLIT 
                                    inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATION.REVENUESPLITID = REVENUESPLIT.ID
                                where REVENUESPLIT.REVENUEID = @REVENUEID 
                                    and BATCHREVENUEAPPLICATION.REGISTRANTID = @APPLICATIONID
                                    and REVENUESPLIT.APPLICATIONCODE = 1
                            end
                        end
                    end

                    declare @RSID uniqueidentifier;
                    declare @OVERRIDE bit;

                    --Removing @OVERRIDE check. Due to this check business unit was updated only first time.

                    declare REVENUE_SPLITS cursor local fast_forward for
                    select distinct 
                        REVENUESPLIT,
                        REVENUESPLIT.OVERRIDEBUSINESSUNITS                        
                    from @REVENUESPLITS RS
                        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = RS.REVENUESPLIT
                    where  REVENUESPLIT.REVENUEID = @REVENUEID

                    open REVENUE_SPLITS
                    fetch next from REVENUE_SPLITS into @RSID, @OVERRIDE

                    while @@FETCH_STATUS = 0
                    begin
                            --Update BU if updated from UI.

                    if (@OVERRIDE = 1 or @OVERRIDEBUSINESSUNITS = 1)
                    begin
                            update dbo.REVENUESPLIT
                            set 
                                OVERRIDEBUSINESSUNITS=1, 
                                REVENUESPLITBUSINESSUNITOVERRIDECODEID=@REASON
                            where REVENUESPLIT.ID = @RSID

                            delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITID = @RSID

                            insert into dbo.REVENUESPLITBUSINESSUNIT(
                                ID,
                                REVENUESPLITID,
                                BUSINESSUNITCODEID,
                                AMOUNT,
                                ADDEDBYID,
                                CHANGEDBYID)
                            select
                                newid(),
                                @RSID,
                                T.c.value('(BUSINESSUNITCODEID)[1]','uniqueidentifier') as BUSINESSUNITCODEID,
                                case RS.OVERRIDEBUSINESSUNITS 
                                    when 0 
                                        then T.c.value('(AMOUNT)[1]','money') * (RS.AMOUNT/R.AMOUNT)
                                    else T.c.value('(AMOUNT)[1]','money') 
                                end,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID
                            from @BUSINESSUNITS.nodes('/BUSINESSUNITS/ITEM') T(c)
                                inner join dbo.REVENUESPLIT RS on RS.ID = @RSID
                                inner join dbo.REVENUE R on R.ID = RS.REVENUEID
              end
                        fetch next from REVENUE_SPLITS into @RSID,@OVERRIDE
                    end
                    close REVENUE_SPLITS
                    deallocate REVENUE_SPLITS

        --Bug 178636 - AdamBu - 2/24/12 - Clear the splits table before processing the next application

                delete @REVENUESPLITS

                fetch next from APPLICATIONS_PAYMENT into @APPLICATIONID, @BUSINESSUNITS, @REASON, @OVERRIDEBUSINESSUNITS, @ADDITIONALAPPLICATIONDECLINESGIFTAID, @ADDITIONALAPPLICATIONSPONSORSHIPID, @REVENUESPLITIDFETCH
            end
            close APPLICATIONS_PAYMENT
            deallocate APPLICATIONS_PAYMENT
        end
    end
    else
    begin
        -- Commitment splits

        if @APPLICATIONBUSINESSUNITS is not null
        begin
            declare APPLICATIONS_COMMITMENT cursor local fast_forward for 
            select 
                T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
                case when T.c.exist('./BUSINESSUNITS/ITEM') = 1 then cast(T.c.query('BUSINESSUNITS') as xml) else null end as BUSINESSUNITS,
                T.c.value('(REASON)[1]','uniqueidentifier') as REASON,
                T.c.value('(OVERRIDEBUSINESSUNITS)[1]','bit') as OVERRIDEBUSINESSUNITS
            from @APPLICATIONBUSINESSUNITS.nodes('/APPLICATIONBUSINESSUNITS/ITEM') T(c)

            open APPLICATIONS_COMMITMENT
            fetch next from APPLICATIONS_COMMITMENT into @APPLICATIONID, @BUSINESSUNITS, @REASON, @OVERRIDEBUSINESSUNITS

            while @@FETCH_STATUS = 0
            begin
                    select
                        @REVENUESPLITID = ID,
                        @OVERRIDE = OVERRIDEBUSINESSUNITS
                    from dbo.REVENUESPLIT 
                    where REVENUESPLIT.REVENUEID = @REVENUEID 
                        and REVENUESPLIT.DESIGNATIONID= @APPLICATIONID

            if (@OVERRIDE = 1 or @OVERRIDEBUSINESSUNITS = 1)
                  begin
                    --In case of ERB validate, @REVENUESPLITID remains null.

                  if @REVENUESPLITID is not null 
                  begin
                      update dbo.REVENUESPLIT
                      set 
                          OVERRIDEBUSINESSUNITS=@OVERRIDEBUSINESSUNITS, 
                          REVENUESPLITBUSINESSUNITOVERRIDECODEID=@REASON
                      where REVENUESPLIT.ID = @REVENUESPLITID

                      exec dbo.USP_REVENUESPLIT_GETBUSINESSSUNIT_UPDATEFROMXML @REVENUESPLITID, @BUSINESSUNITS, @CHANGEAGENTID;  
                    end
          end
                  fetch next from APPLICATIONS_COMMITMENT into @APPLICATIONID, @BUSINESSUNITS, @REASON, @OVERRIDEBUSINESSUNITS
            end
            close APPLICATIONS_COMMITMENT
            deallocate APPLICATIONS_COMMITMENT
        end
    end

    exec dbo.USP_REVENUESPLIT_APPLYBUSINESSUNITS @REVENUEID, @CHANGEAGENTID;
end