USP_SAVE_BENEFITGLDISTRIBUTION_ADJUSTMENT

CREATE procedure dbo.USP_SAVE_BENEFITGLDISTRIBUTION_ADJUSTMENT
(
    @ID uniqueidentifier,
    @GLDISTRIBUTION xml,
    @ADJUSTMENTDATE datetime,
    @ADJUSTMENTPOSTDATE datetime,
    @ADJUSTMENTREASONCODEID uniqueidentifier,
    @ADJUSTMENTREASON nvarchar(300),
    @BENEFITTYPECODE tinyint,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin try
    set nocount on;

    declare @ADJUSTMENTID uniqueidentifier;
    declare @ADJUST bit = 0;

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

    declare @CHANGEDATE datetime;
    set @CHANGEDATE = getdate();

    declare @CUSTOMIZED integer = 0;
    exec  @CUSTOMIZED = dbo.USP_SAVE_BENEFITGLDISTRIBUTION_ADJUSTMENT_CUSTOMIZE @ID, @GLDISTRIBUTION, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASONCODEID, @ADJUSTMENTREASON, @BENEFITTYPECODE, @CHANGEAGENTID

    if @CUSTOMIZED = 0
    begin
        declare @DISTRIBUTIONS table
        (
            ID uniqueidentifier,
            LINEITEMID uniqueidentifier,
            GLACCOUNTID uniqueidentifier,
            REFERENCE nvarchar(255),
            NEWJEID uniqueidentifier --if there is a new adjustment, there will be new journal entries. this is the relationship

        )

        insert into @DISTRIBUTIONS
        select
            DISTRIBUTIONS.ID,
            JE.FINANCIALTRANSACTIONLINEITEMID,
            GLACCOUNT.ID,
            DISTRIBUTIONS.REFERENCE,
            newid()
        from
            dbo.UFN_FINANCIALTRANSACTIONLINEITEM_GETJOURNALENTRIES_FROMITEMLISTXML(@GLDISTRIBUTION) DISTRIBUTIONS
            inner join dbo.JOURNALENTRY JE on JE.ID = DISTRIBUTIONS.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI ON LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
            left join dbo.GLACCOUNT on GLACCOUNT.ACCOUNTNUMBER = DISTRIBUTIONS.ACCOUNT and GLACCOUNT.PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID

        if exists
        (select
            1
        from
            @DISTRIBUTIONS D
            inner join dbo.JOURNALENTRY JE on JE.ID = D.ID
        where
            JE.COMMENT <> D.REFERENCE
            or JE.GLACCOUNTID <> D.GLACCOUNTID)
        begin
            set @ADJUST = 1;
        end

        /* If there was a change to GL related data log an adjustment for each revenue in the transaction */
        if @ADJUST = 1
        begin
            if @ADJUSTMENTREASONCODEID is null
                raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)

            set @ADJUSTMENTID = null;
            exec dbo.USP_SAVE_BENEFITADJUSTMENT_2 @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, 
                @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, default, null, @BENEFITTYPECODE;

            --update journal entries where the line item didn't change

            update JE set
                GLACCOUNTID = DISTRIBUTIONS.GLACCOUNTID,
                COMMENT = DISTRIBUTIONS.REFERENCE,
                POSTDATE = @ADJUSTMENTPOSTDATE,
                DATECHANGED = @CHANGEDATE,
                CHANGEDBYID = @CHANGEAGENTID
            from
                @DISTRIBUTIONS DISTRIBUTIONS
                inner join dbo.JOURNALENTRY JE on JE.ID = DISTRIBUTIONS.ID
            where
                JE.FINANCIALTRANSACTIONLINEITEMID = DISTRIBUTIONS.LINEITEMID

            insert into dbo.JOURNALENTRY
                (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, CLASSCODE, JOURNALCODEID,
                SEQUENCE, TRANSACTIONAMOUNT, PERCENTAGE, SUMMARYID, BATCHID, COMMENT, 
                POSTDATE, FINANCIALBATCHID, GLACCOUNTID, SUBLEDGERTYPECODE, BASEAMOUNT, ORGAMOUNT,
                TRANSACTIONCURRENCYID, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                DISTRIBUTIONS.NEWJEID, DISTRIBUTIONS.LINEITEMID, JE.TRANSACTIONTYPECODE, JE.CLASSCODE, JE.JOURNALCODEID,
                JE.SEQUENCE, JE.TRANSACTIONAMOUNT, JE.PERCENTAGE, JE.SUMMARYID, JE.BATCHID, DISTRIBUTIONS.REFERENCE,
                @ADJUSTMENTPOSTDATE, JE.FINANCIALBATCHID, DISTRIBUTIONS.GLACCOUNTID, JE.SUBLEDGERTYPECODE, JE.BASEAMOUNT, JE.ORGAMOUNT,
                JE.TRANSACTIONCURRENCYID, JE.TYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from
                @DISTRIBUTIONS DISTRIBUTIONS
                inner join dbo.JOURNALENTRY JE on JE.ID = DISTRIBUTIONS.ID
            where
                JE.FINANCIALTRANSACTIONLINEITEMID <> DISTRIBUTIONS.LINEITEMID

            insert into dbo.JOURNALENTRY_EXT
                (ID, PROJECT, JOURNAL, TABLENAMECODE, BATCHID, REVERSEDGLTRANSACTIONID, REVERSEDATE, DISTRIBUTIONTABLEID,
                REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, OUTDATED, WRITEOFFID, PROPERTYDETAILID, STOCKSALEID,
                PAYMENTMETHODCODE, LOGICALREVENUEID, GIFTINKINDSALEID, CREDITITEMID, DISCOUNTCREDITITEMID,
                REVENUESPLITGIFTFEEID, REVENUESPLITGIFTAIDID, ACCOUNT, PRECALCPOSTSTATUSCODE, REVENUEPURCHASEID,
                PLANNEDGIFTPAYOUTID, PRECALCORGANIZATIONEXCHANGERATEID, PRECALCBASEEXCHANGERATEID, FULLYPAIDSTATUSCODE,
                BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select
                DISTRIBUTIONS.NEWJEID, JEX.PROJECT, JEX.JOURNAL, JEX.TABLENAMECODE, null, JEX.REVERSEDGLTRANSACTIONID, null, JEX.DISTRIBUTIONTABLEID,
                JEX.REVENUEID, JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID, 0, JEX.WRITEOFFID, JEX.PROPERTYDETAILID, JEX.STOCKSALEID,
                JEX.PAYMENTMETHODCODE, JEX.LOGICALREVENUEID, JEX.GIFTINKINDSALEID, JEX.CREDITITEMID, JEX.DISCOUNTCREDITITEMID,
                JEX.REVENUESPLITGIFTFEEID, JEX.REVENUESPLITGIFTAIDID, JEX.ACCOUNT, JEX.PRECALCPOSTSTATUSCODE, JEX.REVENUEPURCHASEID,
                JEX.PLANNEDGIFTPAYOUTID, JEX.PRECALCORGANIZATIONEXCHANGERATEID, JEX.PRECALCBASEEXCHANGERATEID, JEX.FULLYPAIDSTATUSCODE,
                JEX.BENEFITTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
            from
                @DISTRIBUTIONS DISTRIBUTIONS
                inner join dbo.JOURNALENTRY JE on JE.ID = DISTRIBUTIONS.ID
                inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
            where
                JE.FINANCIALTRANSACTIONLINEITEMID <> DISTRIBUTIONS.LINEITEMID
        end
    end
end try

begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0