USP_PLEDGE_UPDATEINSTALLMENT2

CREATE procedure dbo.USP_PLEDGE_UPDATEINSTALLMENT2
(
    @PLEDGEID uniqueidentifier,
    @INSTALLMENTS xml,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime,
    @ADJPAYMENT_DATE datetime,
    @ADJPAYMENT_POSTDATE datetime,
    @ADJPAYMENT_REASONCODEID uniqueidentifier,
    @ADJPAYMENT_DETAILS nvarchar(255)
)
as 
begin
    set nocount on;

    declare @INSTALLMENTSPLITS xml;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CURRENTDATE is null  
        set @CURRENTDATE = GetDate();

    --We need to make sure all of the installments have an ID so the child objects will update properly                                         

    declare @TempTbl table (
       [ID] uniqueidentifier,
       [DATE] datetime,
       [AMOUNT] money,
       [BALANCE] money,
       [APPLIED] money,
       [SEQUENCE] int,
       [SPLITID] uniqueidentifier,
       [SPLITDESIGNATIONID] uniqueidentifier,
       [SPLITAMOUNT] money,
       [REVENUESPLITID] uniqueidentifier);

    --case when T2.split.value('(ID)[1]','nvarchar(50)') = '' then null else T2.split.value('(ID)[1]','nvarchar(50)') end AS 'SPLITID',

    insert into @TempTbl
    SELECT
        T1.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T1.c.value('(DATE)[1]','datetime') AS 'DATE',
        T1.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
        T1.c.value('(BALANCE)[1]','money') AS 'BALANCE',
        T1.c.value('(APPLIED)[1]','money') AS 'APPLIED',
        T1.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
        T2.split.value('(ID)[1]','nvarchar(50)') AS 'SPLITID',
        T2.split.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'SPLITDESIGNATIONID',
        T2.split.value('(AMOUNT)[1]','money') AS 'SPLITAMOUNT',
        T2.split.value('(REVENUESPLITID)[1]', 'uniqueidentifier') as 'REVENUESPLITID'
    from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T1(c)
    cross apply T1.c.nodes('./INSTALLMENTSPLITS/ITEM') as T2(split);

    declare @TempSeqTbl table (
       [ID] uniqueidentifier,
       [SEQUENCE] int);

    insert into @TempSeqTbl
    select newid() ID, MISSINGID.SEQUENCE
        from (select distinct SEQUENCE from @TempTbl where id is null) MISSINGID



    -- ** Work for PBI 40790 **

    declare @CONSTITUENTID uniqueidentifier
    declare @REVENUEID uniqueidentifier
    declare @DEPOSITID uniqueidentifier
    declare @APPLIEDAMOUNT money
    declare @DONOTPOST bit
    declare @REVENUEDATE datetime
    declare @UNAPPLIEDMATCHINGGIFTSPLITS xml
    declare @CREATEDSPLITS xml 
    declare @OVERPAYMENTAPPLICATIONTYPECODE tinyint 
    declare @APPLICATIONTYPE tinyint
    declare @AMOUNTPAID money 
    declare @ADJUSTMENTID uniqueidentifier
    declare @ADJ_POSTSTATUS tinyint
    declare @ADJUSTED bit

    -- Save the IDs for any payments that need to be updated. --

    declare @TempRevenue table (
      REVENUEID uniqueidentifier,
      RSPLITID uniqueidentifier,
      DEPOSITID uniqueidentifier,
      REVENUEAMOUNT money,
      REVENUEDATE datetime,
      POSTED bit,
      DONOTPOST bit,
      ADJUSTED bit,
      SEQUENCE int,
      OVERPAYMENTAPPLICATIONTYPECODE tinyint);

    insert into @TempRevenue 
    Select t3.REVENUEID, t3.ID, 
     (select DEPOSITID from BANKACCOUNTDEPOSITPAYMENT where ID = t3.REVENUEID),
      t3.AMOUNT, t4.DATE as REVENUEDATE, 
      dbo.UFN_REVENUE_ISPOSTED(t3.REVENUEID) as POSTED, t4.DONOTPOST, 
     (select Count(ID) from ADJUSTMENT s1 where s1.REVENUEID = t3.REVENUEID and POSTSTATUSCODE = 1 ) ADJUSTED,
      t0.SEQUENCE, t2.OVERPAYMENTAPPLICATIONTYPECODE
        from INSTALLMENT as t0
        inner join INSTALLMENTSPLIT as t1 on t0.ID = t1.INSTALLMENTID 
        inner join INSTALLMENTSPLITPAYMENT as t2 on t2.INSTALLMENTSPLITID = t1.ID 
        inner join REVENUESPLIT as t3 on t2.PAYMENTID = t3.ID 
        inner join REVENUE as t4 on t3.REVENUEID=t4.ID 
        left outer join @TempTbl as t5 on t1.ID = t5.SPLITID 
        where t0.REVENUEID = @PLEDGEID 
        and (t1.Amount <> IsNull(t5.SPLITAMOUNT,0) or t1.DESIGNATIONID <> IsNull(t5.SPLITDESIGNATIONID, '00000000-0000-0000-0000-000000000000'))

    -- Create adjustments for Posted Payments.

    declare POSTEDCURSOR cursor local fast_forward for
        Select distinct REVENUEID, ADJUSTED from @TempRevenue where POSTED = 1

    open POSTEDCURSOR;
    fetch next from POSTEDCURSOR into @REVENUEID, @ADJUSTED;
    while (@@FETCH_STATUS=0)
    begin
        set @ADJUSTMENTID = null
        set @ADJ_POSTSTATUS = 1 --< Revisit do we need to let User set this?


        -- Do not add a new adjustment if an unposted adjustment exists. ?

        IF @ADJUSTED = 0
            exec dbo.USP_SAVE_ADJUSTMENT  @REVENUEID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJPAYMENT_DATE, 
                    @ADJPAYMENT_POSTDATE, @ADJPAYMENT_DETAILS, default, @ADJPAYMENT_REASONCODEID,  @ADJ_POSTSTATUS;

        fetch next from POSTEDCURSOR into @REVENUEID, @ADJUSTED;
    end
    close POSTEDCURSOR;
    deallocate POSTEDCURSOR;

    -- Delete the Revenue Split records and distributions.

    Delete from REVENUESPLIT 
    where ID in (Select RSPLITID from @TempRevenue)

    -- Delete the Distributions for any Un-posted Payments.

    Delete from GLTRANSACTION 
    where POSTSTATUSCODE <> 1 and ID In
        (Select GLTRANSACTIONID from REVENUEGLDISTRIBUTION 
         where OUTDATED=0 and REVENUEID in (Select REVENUEID from @TempRevenue)) 

    Delete from REVENUEGLDISTRIBUTION 
    where OUTDATED=0 and REVENUEID in (Select REVENUEID from @TempRevenue) 
    -- ****


    -- Update installments 

    update a
    set ID = b.ID
    from @TempTbl a
    inner join @TempSeqTbl b
        on a.SEQUENCE = b.SEQUENCE;

    set @INSTALLMENTS = (select distinct
            ID, DATE, AMOUNT, BALANCE, APPLIED, SEQUENCE
        from @TempTbl
        for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64);

    set @INSTALLMENTSPLITS = (select distinct
            SPLITID ID, ID INSTALLMENTID, SPLITDESIGNATIONID DESIGNATIONID, SPLITAMOUNT AMOUNT, REVENUESPLITID
        from @TempTbl
        for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64);

    exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_UPDATEFROMXML @PLEDGEID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;

    exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_UPDATEFROMXML @PLEDGEID, @INSTALLMENTSPLITS, @CHANGEAGENTID, @CURRENTDATE;


    -- More Work for PBI 40790

    select @CONSTITUENTID=CONSTITUENTID from REVENUE where ID = @PLEDGEID 
    set @APPLICATIONTYPE = 2

    declare REVENUECURSOR cursor local fast_forward for
        Select REVENUEID, DEPOSITID, SUM(REVENUEAMOUNT), REVENUEDATE, DONOTPOST, OVERPAYMENTAPPLICATIONTYPECODE from @TempRevenue
        group by REVENUEID, DEPOSITID, REVENUEDATE, DONOTPOST, OVERPAYMENTAPPLICATIONTYPECODE

    open REVENUECURSOR;
    fetch next from REVENUECURSOR into @REVENUEID, @DEPOSITID, @APPLIEDAMOUNT, @REVENUEDATE, @DONOTPOST, @OVERPAYMENTAPPLICATIONTYPECODE;
    while (@@FETCH_STATUS=0)
    begin
        set @CREATEDSPLITS = null
        set @AMOUNTPAID = null

        -- Re-Add the Payment Split.

        exec dbo.USP_PLEDGE_ADDPAYMENT @REVENUEID, @PLEDGEID, @APPLIEDAMOUNT, @CONSTITUENTID, @REVENUEDATE, @UNAPPLIEDMATCHINGGIFTSPLITS, @APPLICATIONTYPE, @AMOUNTPAID output, @CURRENTDATE, @CHANGEAGENTID, @CREATEDSPLITS output, @OVERPAYMENTAPPLICATIONTYPECODE 

        if @DONOTPOST = 0
        begin
            -- Update the Distribution.

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

            -- Update the distribution with the deposit info.

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

        fetch next from REVENUECURSOR into @REVENUEID, @DEPOSITID, @APPLIEDAMOUNT, @REVENUEDATE, @DONOTPOST, @OVERPAYMENTAPPLICATIONTYPECODE;
    end
    close REVENUECURSOR;
    deallocate REVENUECURSOR;

end