USP_SAVE_GIFTFEEADJUSTMENT_2

CREATE procedure dbo.USP_SAVE_GIFTFEEADJUSTMENT_2
(
    @REVENUEID uniqueidentifier = null,
    @ADJUSTMENTID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null,
    @DATE datetime = null,
    @POSTDATE datetime = null,
    @ADJUSTMENTREASON nvarchar(300) = null,
    @ISNEWREVENUE bit = 0,
    @ADJUSTMENTREASONCODEID uniqueidentifier = null,
    @POSTSTATUSCODE tinyint = 1,
    @UPDATEGIFTFEEOPTION tinyint = null,
    @TEMP_REVENUESPLITGIFTFEE_XML xml = null
)
with execute as owner
as
/*call this procedure before making changes to the revenue tables.*/

set nocount on;

declare @REVENUEPOSTDATE datetime;
declare @CONSTITUENTID uniqueidentifier;

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

if @CHANGEDATE is null
    set @CHANGEDATE = getdate();

--JamesWill WI197067 2012-03-08 Ensure that the date used does not have a timestamp

if not @DATE is null
    set @DATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATE);

if @UPDATEGIFTFEEOPTION is null
    set @UPDATEGIFTFEEOPTION = 1

declare @FTMPOSTSTATUSCODE tinyint;
set @FTMPOSTSTATUSCODE = case @POSTSTATUSCODE when 2 then 3 when 0 then 2 else 1 end;

-- Check GL business rule for this account system and set to 'Do not post' if needed.

declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE where ID = @REVENUEID;
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
  set @POSTSTATUSCODE = 2  -- Do not post

  set @POSTDATE = null
end

--Is the revenue posted?

if (    select count(REVENUE.ID) from dbo.REVENUE
        inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
        where REVENUE.ID = @REVENUEID) = 0
    raiserror('You cannot adjust an unposted gift fee', 13, 1)

--kwb Create FINANCIALTRANSACTIONLINEITEMADJUSTMENT records for original write-offs that will be adjusted

exec dbo.USP_CREATEINITIALADJUSTMENTHISTORY @REVENUEID, @CHANGEAGENTID

select @CONSTITUENTID = CONSTITUENTID from dbo.FINANCIALTRANSACTION where ID = @REVENUEID

--Update unposted adjustment if existing

select @ADJUSTMENTID = ID
from dbo.GIFTFEEADJUSTMENT
where GIFTFEEADJUSTMENT.REVENUEID = @REVENUEID and POSTSTATUSCODE <> 0;

if @ADJUSTMENTID is null
    set @ADJUSTMENTID = newid();

if @ADJUSTMENTID is not null and exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT where ID = @ADJUSTMENTID)
    update dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT set
        ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID
        ,REASON = @ADJUSTMENTREASON
        ,CONSTITUENTID = @CONSTITUENTID
        ,DATE = @DATE
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CHANGEDATE
    where ID = @ADJUSTMENTID;
else if @ADJUSTMENTID is not null
    insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
        ID
        ,ADJUSTMENTREASONCODEID
        ,REASON
        ,CONSTITUENTID
        ,DATE
        ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values (@ADJUSTMENTID
        ,@ADJUSTMENTREASONCODEID
        ,@ADJUSTMENTREASON
        ,@CONSTITUENTID
        ,@DATE
        ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

update dbo.GIFTFEEADJUSTMENT
set DATE = @DATE, 
    POSTDATE = @POSTDATE,
    REASON = @ADJUSTMENTREASON,
    REASONCODEID = @ADJUSTMENTREASONCODEID,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CHANGEDATE,
    POSTSTATUSCODE = @POSTSTATUSCODE
where ID = @ADJUSTMENTID;

if @@ROWCOUNT = 0
begin
    declare @TRANSACTIONAMOUNT money;
    declare @AMOUNT money;
    declare @ORGANIZATIONAMOUNT money;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

    select
        @TRANSACTIONAMOUNT = sum(RSGF.TRANSACTIONAMOUNT),
        @AMOUNT = sum(RSGF.FEE),
        @ORGANIZATIONAMOUNT = sum(RSGF.ORGANIZATIONAMOUNT),
        @TRANSACTIONCURRENCYID = RSGF.TRANSACTIONCURRENCYID,
        @BASECURRENCYID = RSGF.BASECURRENCYID,
        @BASEEXCHANGERATEID = RSGF.BASEEXCHANGERATEID,
        @ORGANIZATIONEXCHANGERATEID = RSGF.ORGANIZATIONEXCHANGERATEID
    from dbo.REVENUESPLITGIFTFEE RSGF
    left join dbo.REVENUESPLIT RS on RS.ID = RSGF.ID
    where RS.REVENUEID = @REVENUEID
    group by RSGF.TRANSACTIONCURRENCYID, RSGF.BASECURRENCYID, RSGF.BASEEXCHANGERATEID, RSGF.ORGANIZATIONEXCHANGERATEID;

    --Log Adjustment if new

    insert into dbo.GIFTFEEADJUSTMENT(
        ID,
        REVENUEID,
        PREVIOUSAMOUNT,
        DATE,
        POSTDATE,
        POSTSTATUSCODE,
        REASON,
        REASONCODEID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED,
        BASECURRENCYID,
        TRANSACTIONCURRENCYID,
        TRANSACTIONPREVIOUSAMOUNT,
        BASEEXCHANGERATEID,
        ORGANIZATIONPREVIOUSAMOUNT,
        ORGANIZATIONEXCHANGERATEID
    )
    select
        @ADJUSTMENTID,
        @REVENUEID,
        coalesce(@AMOUNT, 0),
        @DATE,
        @POSTDATE,
        @POSTSTATUSCODE,
        @ADJUSTMENTREASON,
        @ADJUSTMENTREASONCODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CHANGEDATE,
        @CHANGEDATE,
        coalesce(@BASECURRENCYID, R.BASECURRENCYID),
        coalesce(@TRANSACTIONCURRENCYID, R.TRANSACTIONCURRENCYID),
        coalesce(@TRANSACTIONAMOUNT, 0),
        coalesce(@BASEEXCHANGERATEID, R.BASEEXCHANGERATEID),
        coalesce(@ORGANIZATIONAMOUNT, 0),
        coalesce(@ORGANIZATIONEXCHANGERATEID, R.ORGANIZATIONEXCHANGERATEID)
    from
        dbo.REVENUE R
    where
        R.ID = @REVENUEID;

    declare @LINEITEMS UDT_GENERICID

    if @UPDATEGIFTFEEOPTION = 0 and @TEMP_REVENUESPLITGIFTFEE_XML is not null
    begin

        declare @LINEITEMSFROMXML table
        (
            ID uniqueidentifier,
            FEE money,
            FTLIDELETED bit
        )

        insert into @LINEITEMSFROMXML (ID, FEE, FTLIDELETED)
        select 
            T.c.value('(ID)[1]','uniqueidentifier') as ID, 
            T.c.value('(FEE)[1]','money') as FEE,
            T.c.value('(FTLIDELETED)[1]','bit') as FTLIDELETED
        from 
            @TEMP_REVENUESPLITGIFTFEE_XML.nodes('REVENUESPLITGIFTFEES/ITEM') T(c)

        insert into @LINEITEMS (ID) 
        select FTLIGIFTFEE.ID
        from     
        dbo.FINANCIALTRANSACTION FT
           inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLIREVERSAL on FT.ID = FTLIREVERSAL.FINANCIALTRANSACTIONID 
          inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLIGIFTFEE on FTLIGIFTFEE.SOURCELINEITEMID = FTLIREVERSAL.ID
          inner join @LINEITEMSFROMXML LI on LI.ID = FTLIREVERSAL.REVERSEDLINEITEMID
        where
            FTLIREVERSAL.[TYPECODE] = 0 and 
            FT.ID = @REVENUEID and LI.FTLIDELETED = 1

    end

    if @UPDATEGIFTFEEOPTION <> 0
    begin
        insert into @LINEITEMS (ID)
        select distinct
            LI.ID
        from
            dbo.FINANCIALTRANSACTIONLINEITEM LI
        where
            LI.FINANCIALTRANSACTIONID = @REVENUEID 
            and LI.TYPECODE = 7 
            and LI.DELETEDON is null
    end

    --Log reversals in the GLTRANSACTION table

    if @POSTSTATUSCODE <> 2 
    begin
        exec dbo.USP_SAVE_REVERSAL_LINEITEM @LINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @FTMPOSTSTATUSCODE

        exec dbo.USP_SAVE_HISTORICAL_LINEITEM @LINEITEMS, @ADJUSTMENTID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE, @FTMPOSTSTATUSCODE

        update FTLI set
            SOURCELINEITEMID = isnull(PAYMENTLI.REVERSEDLINEITEMID, FTLI.SOURCELINEITEMID)
            ,DATECHANGED = @CHANGEDATE
            ,CHANGEDBYID = @CHANGEAGENTID
        from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
        inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on PAYMENTLI.ID = FTLI.SOURCELINEITEMID
        where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
            and FTLI.POSTSTATUSCODE != 2
            and FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
            and FTLI.DELETEDON is null;

    end
end
else /*make sure @ADJUSTMENTID gets set to the correct value*/
begin
    --declare @FTMPOSTSTATUSCODE tinyint;

    --set @FTMPOSTSTATUSCODE = case @POSTSTATUSCODE when 2 then 3 when 0 then 2 else 1 end;


    update dbo.FINANCIALTRANSACTIONLINEITEM set
        POSTDATE = @POSTDATE
        ,POSTSTATUSCODE = @FTMPOSTSTATUSCODE
        ,SOURCELINEITEMID = isnull((select REVERSEDLINEITEMID from dbo.FINANCIALTRANSACTIONLINEITEM where ID = FTLI.SOURCELINEITEMID), FTLI.SOURCELINEITEMID)
        ,DATECHANGED = @CHANGEDATE
        ,CHANGEDBYID = @CHANGEAGENTID
    from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
    where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
        and FTLI.POSTSTATUSCODE != 2
        and FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
        and FTLI.DELETEDON is null;

    -- Update the post date on the GL transaction records

    update dbo.JOURNALENTRY set
        JOURNALENTRY.POSTDATE = @POSTDATE
        ,JOURNALENTRY.DATECHANGED = @CHANGEDATE
        ,JOURNALENTRY.CHANGEDBYID = @CHANGEAGENTID
    from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
    inner join dbo.JOURNALENTRY as JE on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
    inner join dbo.JOURNALENTRY_EXT as JEE on JE.ID = JEE.ID
    where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
        and FTLI.POSTSTATUSCODE != 2
        and FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
        and FTLI.DELETEDON is null
        and (JEE.OUTDATED = 0 or JEE.TABLENAMECODE = 0);
end

-- create gift fees if USP_SAVE_HISTORICAL_LINEITEM does not handle creating it

if @POSTSTATUSCODE <> 2
begin
    if (
            not exists (
                select 1
                from dbo.FINANCIALTRANSACTIONLINEITEM LI
                where LI.FINANCIALTRANSACTIONID = @REVENUEID
                    and LI.TYPECODE = 7
                    and LI.DELETEDON is null
                    and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null
                )
            )
    begin
        insert FINANCIALTRANSACTIONLINEITEM (
            ID
            ,FINANCIALTRANSACTIONID
            ,TRANSACTIONAMOUNT
            ,VISIBLE
            ,DESCRIPTION
            ,SEQUENCE
            ,TYPECODE
            ,POSTDATE
            ,POSTSTATUSCODE
            ,BASEAMOUNT
            ,ORGAMOUNT
            ,SOURCELINEITEMID
            ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
            ,BATCHID
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
            )
        select newid()
            ,@REVENUEID
            ,RSGF.TRANSACTIONAMOUNT
            ,1
            ,'Gift Fee'
            ,row_number() over (
                order by FTLI.sequence
                )
            ,7
            ,@POSTDATE
            ,1
            ,RSGF.FEE
            ,RSGF.ORGANIZATIONAMOUNT
            ,RSGF.ID
            ,@ADJUSTMENTID
            ,null
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CHANGEDATE
            ,@CHANGEDATE
        from dbo.REVENUESPLITGIFTFEE RSGF
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on RSGF.ID = FTLI.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM as GIFTFEELI on GIFTFEELI.SOURCELINEITEMID =  RSGF.ID and GIFTFEELI.FINANCIALTRANSACTIONID = @REVENUEID
        where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
        and GIFTFEELI.DELETEDON is null
        and (GIFTFEELI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID != @ADJUSTMENTID or GIFTFEELI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null)
    end
end


declare @Error nvarchar(255) = ''
if @@ROWCOUNT > 0 and @POSTSTATUSCODE <> 2
    set @Error = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE)

if @Error <> '' 
    raiserror(@Error, 13, 1)