USP_MAPPINGERROR_REVENUE_RECREATEDISTRIBUTION

CREATE procedure dbo.USP_MAPPINGERROR_REVENUE_RECREATEDISTRIBUTION
(
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @PROCESSID uniqueidentifier = null
) as 
begin

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

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

    declare @DEFAULTGLACCOUNTID uniqueidentifier;
    declare @DEPOSITID uniqueidentifier;
    declare @HASDEFAULT as bit = 0;
    declare @REVENUESPLITID uniqueidentifier;
    declare @ORIGINALID uniqueidentifier = @REVENUEID;

    select @REVENUEID = isnull(S.REVENUEID, @REVENUEID)
    from dbo.SALESORDER S where S.ID = @REVENUEID

    select @DEFAULTGLACCOUNTID = S.DEFAULTGLACCOUNTID
    from dbo.PDACCOUNTSYSTEM S
    inner join dbo.FINANCIALTRANSACTION on S.ID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
    where FINANCIALTRANSACTION.ID = @REVENUEID;

    select @DEPOSITID = DEPOSITID
    from dbo.BANKACCOUNTDEPOSITPAYMENT
    where ID = @REVENUEID;

    if @DEFAULTGLACCOUNTID is not null
    begin
        -- Use underlying tables from REVENUEGLDISTRIBUTION for performance reasons

        if exists(select 1 
                    from dbo.JOURNALENTRY 
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
                    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1
                    inner join dbo.GLTRANSACTION on JOURNALENTRY.ID = GLTRANSACTION.ID
                    where FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTION.ID = @REVENUEID and GLTRANSACTION.GLACCOUNTID = @DEFAULTGLACCOUNTID and GLTRANSACTION.POSTSTATUSCODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select 
                                                        JOURNALENTRY.ID as GLTRANSACTIONID
                                                        from dbo.JOURNALENTRY 
                                                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
                                                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
                                                        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1
                                                        where FINANCIALTRANSACTION.DELETEDON is null and JOURNALENTRY_EXT.OUTDATED = 0 and FINANCIALTRANSACTION.ID = @REVENUEID) and POSTSTATUSCODE >= 1;

            delete JOURNALENTRY from dbo.JOURNALENTRY 
            inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
            inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID 
            inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
            where FTLI.POSTSTATUSCODE != 2 and FTLI.TYPECODE != 1
            and JOURNALENTRY_EXT.DISTRIBUTIONTABLEID in (select 
                                                          JOURNALENTRY_EXT.GLTRANSACTIONID
                                                          from dbo.JOURNALENTRY 
                                                          inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
                                                          inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
                                                          inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1
                                                          where FINANCIALTRANSACTION.DELETEDON is null and JOURNALENTRY_EXT.OUTDATED = 0 and FINANCIALTRANSACTION.ID = @REVENUEID);            



            exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
            set @HASDEFAULT = 1;
        end

        if exists(select 1 
            from dbo.STOCKSALEGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1 
            from dbo.GIFTINKINDSALEGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.GIFTINKINDSALEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.GIFTINKINDSALEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1 
            from dbo.PROPERTYDETAILGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1 
            from dbo.BENEFITGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = D.REVENUEID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1 and FT.TYPECODE = 0)
        begin
            delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_PAYMENT_BENEFITGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1 
            from dbo.BENEFITGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = D.REVENUEID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1 and FT.TYPECODE = 5)
        begin
            delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_BENEFITGLDISTRIBUTION_SALESORDER @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1 
            from dbo.BENEFITGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = D.REVENUEID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1 and FT.TYPECODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select GLTRANSACTIONID from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1
            from dbo.GIFTFEEGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select D.GLTRANSACTIONID from dbo.GIFTFEEGLDISTRIBUTION D where D.REVENUEID = @REVENUEID and D.OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.GIFTFEEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_GIFTFEEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1
            from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select D.GLTRANSACTIONID from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION D where D.REVENUEID = @REVENUEID and D.OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_UNREALIZEDGAINLOSSGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1
            from dbo.WRITEOFFGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin

            -- Map by reason code

            if dbo.UFN_VALID_BASICGL_INSTALLED() = 1 
            begin
                -- Using a table to support redistributing across multiple write-offs, e.g. editing a pledge designation

                declare @WRITEOFFIDTABLE UDT_GENERICID;
                insert into @WRITEOFFIDTABLE
                select D.WRITEOFFID
                from dbo.WRITEOFFGLDISTRIBUTION D
                inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
                where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1
                group by WRITEOFFID
            end

            delete from dbo.GLTRANSACTION where ID in (select D.GLTRANSACTIONID from dbo.WRITEOFFGLDISTRIBUTION D where D.REVENUEID = @REVENUEID and D.OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.WRITEOFFGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            if dbo.UFN_VALID_BASICGL_INSTALLED() = 1 -- Map by reason code

                exec dbo.USP_SAVE_PLEDGEWRITEOFFGLDISTRIBUTION @REVENUEID, @WRITEOFFIDTABLE, @CHANGEAGENTID, @CURRENTDATE
            else
                exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1
            from dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            delete from dbo.GLTRANSACTION where ID in (select D.GLTRANSACTIONID from dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION D where D.REVENUEID = @REVENUEID and D.OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

            declare @PLANNEDGIFTPAYOUTID uniqueidentifier;
            select @PLANNEDGIFTPAYOUTID = ID from dbo.PLANNEDGIFTPAYOUT where REVENUEID = @REVENUEID;

            exec dbo.USP_SAVE_PLANNEDGIFTPAYOUTGLDISTRIBUTION @REVENUEID, @PLANNEDGIFTPAYOUTID, @CHANGEAGENTID, @CURRENTDATE;
        end

        if exists(select 1
            from dbo.GIFTAIDGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            declare revenueSplitCursor cursor local fast_forward for
            select R.ID from dbo.REVENUESPLIT R where R.REVENUEID = @REVENUEID;

            open revenueSplitCursor;
            fetch next from revenueSplitCursor into @REVENUESPLITID;
            while @@FETCH_STATUS = 0
            begin
                if exists(select 1
                    from dbo.GIFTAIDGLDISTRIBUTION D
                    inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
                    where D.REVENUESPLITGIFTAIDID = @REVENUESPLITID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
                begin
                    delete from dbo.GLTRANSACTION where ID in (select D.GLTRANSACTIONID from dbo.GIFTAIDGLDISTRIBUTION D where D.REVENUESPLITGIFTAIDID = @REVENUESPLITID and D.OUTDATED = 0) and POSTSTATUSCODE >= 1;
                    delete from dbo.GIFTAIDGLDISTRIBUTION where REVENUESPLITGIFTAIDID = @REVENUESPLITID and OUTDATED = 0;

                    exec dbo.USP_SAVE_GIFTAIDGLDISTRIBUTION @REVENUESPLITID, @CHANGEAGENTID, @CURRENTDATE;
                end
                fetch next from revenueSplitCursor into @REVENUESPLITID;
            end

            close revenueSplitCursor;
            deallocate revenueSplitCursor;
        end

        if exists(select 1
            from dbo.CREDITGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            declare creditCursor cursor local fast_forward for
            select C.ID from dbo.CREDIT C inner join dbo.SALESORDER S on S.ID = C.SALESORDERID where S.REVENUEID = @REVENUEID and C.TYPECODE = 1;

            open creditCursor;
            fetch next from creditCursor into @REVENUESPLITID;
            while @@FETCH_STATUS = 0
            begin
                if exists(select 1
                    from dbo.CREDITGLDISTRIBUTION D
                    inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
                    inner join dbo.CREDITITEM I on D.CREDITITEMID = I.ID
                    where I.CREDITID = @REVENUESPLITID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID)
                begin
                    delete from dbo.GLTRANSACTION where ID in (select D.GLTRANSACTIONID from dbo.CREDITGLDISTRIBUTION D inner join dbo.CREDITITEM I on D.CREDITITEMID = I.ID where I.CREDITID = @REVENUESPLITID and D.OUTDATED = 0) and POSTSTATUSCODE >= 1;
                    delete from dbo.CREDITGLDISTRIBUTION where CREDITITEMID in (select ID from dbo.CREDITITEM where CREDITID = @REVENUESPLITID) and OUTDATED = 0;

                    exec dbo.USP_SALESORDER_CREATEDISCOUNTGLDISTRIBUTION @REVENUESPLITID, @CHANGEAGENTID, @CURRENTDATE;
                end
                fetch next from creditCursor into @REVENUESPLITID;
            end

            close creditCursor;
            deallocate creditCursor;
        end

        if exists(select 1
            from dbo.AUCTIONPURCHASEGLDISTRIBUTION D
            inner join dbo.GLTRANSACTION T on D.GLTRANSACTIONID = T.ID
            where D.REVENUEID = @REVENUEID or D.REVENUEPURCHASEID = @REVENUEID and T.GLACCOUNTID = @DEFAULTGLACCOUNTID and T.POSTSTATUSCODE = 1)
        begin
            select TOP 1 @REVENUEID = D.REVENUEPURCHASEID from AUCTIONPURCHASEGLDISTRIBUTION D where D.REVENUEID = @REVENUEID or D.REVENUEPURCHASEID = @REVENUEID;

            delete from dbo.GLTRANSACTION where ID in (select D.GLTRANSACTIONID from dbo.AUCTIONPURCHASEGLDISTRIBUTION D where D.REVENUEPURCHASEID = @REVENUEID and D.OUTDATED = 0) and POSTSTATUSCODE >= 1;
            delete from dbo.AUCTIONPURCHASEGLDISTRIBUTION where REVENUEPURCHASEID = @REVENUEID and OUTDATED = 0;

            exec dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
            set @HASDEFAULT = 1;
        end

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

    if exists(
        select 1 
        from dbo.GLACCOUNTMAPPINGERROR 
        where 
            TRANSACTIONID = @ORIGINALID 
            and DELETED = 0
            and ERRORMESSAGE in (
                select E.ERRORMESSAGE 
                from dbo.GLACCOUNTMAPPINGERROR E 
                where 
                    E.TRANSACTIONID = @ORIGINALID 
                    and E.GLACCOUNTMAPPINGERRORUPDATEPROCESSID = @PROCESSID
            )
    )
        return 0
    else
        return 1
end