UFN_POSTTOGLPROCESS_DETAILWITHGLACCOUNTID

CREATE function dbo.UFN_POSTTOGLPROCESS_DETAILWITHGLACCOUNTID
(@InRecords UDT_GENERICIDANDBIT readonly, @ID uniqueidentifier)
returns @WORKTABLE table
    (
    GLTRANSACTIONID uniqueidentifier,
    ACCOUNTSTRING  nvarchar(100),
    POSTDATE datetime,    
    TRANTYPE nvarchar(1),
    DEBITCREDIT nvarchar(1),
    AMOUNT money,    
    JOURNAL nvarchar(255),
    REFERENCE nvarchar(255),                
    BATCH uniqueidentifier,
    GLACCOUNTID uniqueidentifier,
    BASECURRENCYID uniqueidentifier,
    ORGANIZATIONAMOUNT money,
    NUMBEROFRECORD smallint,
    ROWNUMBER smallint,
    PARENTID uniqueidentifier,
    TABLENAMECODE smallint
    )    
  with execute as caller
    as
    begin

    declare @BASECURRENCYID uniqueidentifier;
    declare @REVALUATIONGAINLOSSPOSTMETHOD tinyint;
    declare @REVALUATIONGAINLOSSREFERENCE nvarchar(255);
    declare @QUERYVIEWID uniqueidentifier;
    declare @ISPEACHTREE bit = 0;

    declare @Pegadog table (GLTRANSACTIONID uniqueidentifier primary key, IsReversal bit, IsUsed bit, LeftoverID uniqueidentifier);

    declare @TTranCount table (TRANIDENTIFIERID uniqueidentifier primary key, Count int);
    declare @Leftover table (TRANIDENTIFIERID uniqueidentifier, TABLENAMECODE int,  Count int);


    declare @TABLENAMETABLE table (TABLECODE int);

    insert into @TABLENAMETABLE (TABLECODE)
    select distinct tablenamecode            
        from JOURNALENTRY_EXT a    
            join @InRecords b on a.ID = b.ID

    insert into @Pegadog (GLTRANSACTIONID, IsReversal, IsUsed, LeftoverID)
    select distinct ID, Flag, 0, '99999999-9999-9999-9999-999999999999' from @InRecords


    select 
        @BASECURRENCYID = coalesce(CURRENCYSET.BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()),
        @REVALUATIONGAINLOSSPOSTMETHOD = POSTTOGLPROCESSDETAIL.REVALUATIONGAINLOSSPOSTMETHODCODE, 
        @REVALUATIONGAINLOSSREFERENCE = isnull(nullif(POSTTOGLPROCESSDETAIL.REVALUATIONGAINLOSSREFERENCE,''),'Summarized Revaluation Gain/Loss'),
        @QUERYVIEWID = BUSINESSPROCESSVIEW.QUERYVIEWCATALOGID
    from 
        dbo.POSTTOGLPROCESSDETAIL 
        left join dbo.POSTTOGLPROCESS on POSTTOGLPROCESSDETAIL.ID = POSTTOGLPROCESS.ID
        left join dbo.BUSINESSPROCESSVIEW on BUSINESSPROCESSVIEW.ID = POSTTOGLPROCESS.BUSINESSPROCESSVIEWID
        left join dbo.PDACCOUNTSYSTEM ON POSTTOGLPROCESS.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
        left join dbo.CURRENCYSET ON PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    where
        POSTTOGLPROCESSDETAIL.ID = @ID;

     if @QUERYVIEWID = 'ADD6036D-9B56-48CF-BDD2-1F48F74EDD0A' 
        set @ISPEACHTREE = 1

    -- Revenue

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 1)    
        begin
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 1
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,isnull(f.POSTDATE, L.POSTDATE)
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 1
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on e.ID = c.REVENUEID
                left outer join dbo.FINANCIALTRANSACTION f on f.ID = e.DEPOSITID
            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Deposit Corrections

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 3)    
        begin    
            insert into @TTranCount(TRANIDENTIFIERID, Count)
                select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
                from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 3
                Join @InRecords P on P.ID = J.ID
                where L.FINANCIALTRANSACTIONID is not null
                group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
                select 
                    a.GLTRANSACTIONID
                    ,GL.ACCOUNTNUMBER
                    , b.POSTDATE
                    ,'R'
                    ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                    ,F.BASEAMOUNT
                    ,c.JOURNAL
                    ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                    ,c.BATCHID
                    ,TC.COUNT
                    ,b.GLACCOUNTID
                    ,@BASECURRENCYID
                    ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                    ,ROW_NUMBER() OVER(partition by F.ID ORDER by F.ID)
                    ,L.FINANCIALTRANSACTIONID
                    ,c.TABLENAMECODE                    
                from  @Pegadog as a 
                    inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                    inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 3
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                    inner join dbo.FINANCIALTRANSACTION F on L.FINANCIALTRANSACTIONID = F.ID
                    inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                    inner join @TTranCount TC on F.ID = TC.TRANIDENTIFIERID
                where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
                order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Credit

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 6)
        begin
            insert into @TTranCount(TRANIDENTIFIERID, Count)
                select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)

                from dbo.FINANCIALTRANSACTIONLINEITEM L 
                    join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                    join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 6
                    Join @InRecords P on P.ID = J.ID
                where L.FINANCIALTRANSACTIONID is not null
                group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
                select a.GLTRANSACTIONID,b.ACCOUNT, b.POSTDATE,'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                b.AMOUNT,
                JOURNAL,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.REFERENCE, 255),BATCHID,TC.COUNT,b.GLACCOUNTID,@BASECURRENCYID,b.ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by c.REVENUEID ORDER by c.REVENUEID)
                ,c.REVENUEID
                ,6
                from  @Pegadog as a 
                inner join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.CREDITGLDISTRIBUTION   as c on c.GLTRANSACTIONID = b.ID 
                inner join @TTranCount TC on c.REVENUEID = TC.TRANIDENTIFIERID
                where b.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
                order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end


    -- Property

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 10)
        begin
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID 
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null and x.TABLENAMECODE = 10
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        
            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0 and c.TABLENAMECODE = 10
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Stock

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 11)
        begin
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID 
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null and x.TABLENAMECODE = 11
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        
            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0 and c.TABLENAMECODE = 11
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Gift-in-Kind 

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 13)
        begin
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID 
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null and x.TABLENAMECODE = 13
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        
            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0 and c.TABLENAMECODE = 13
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end


    -- GiftAid 

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 7)
        begin
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID 
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null and x.TABLENAMECODE = 7
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        
            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0 and c.TABLENAMECODE = 7
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- GiftFee   

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 8)
        begin
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID 
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null and x.TABLENAMECODE = 8
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        
            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0 and c.TABLENAMECODE = 8
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end



    -- benefit

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 5)
        begin    
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 5
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER 
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 5
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID

            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Auction

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 2)
        begin    
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 2
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 2
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        

            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Bank Adjustments

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 4)
        begin    
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 4
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 4
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        

            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Writeoff

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 12)
        begin    
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 12
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER 
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 12
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        

            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end


    -- Payout

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 9)
        begin    
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 9
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 9
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        

            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end

    -- Unreal

    if exists(select 1 from @TABLENAMETABLE where TABLECODE = 14)
        begin    
            insert into @TTranCount(TRANIDENTIFIERID, Count)
            select     L.FINANCIALTRANSACTIONID, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID and X.TABLENAMECODE = 14
                Join @InRecords P on P.ID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID having COUNT(J.ID) > 1

            insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,
                                    BATCH,NUMBEROFRECORD,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                                    ,PARENTID,TABLENAMECODE)
            select 
                a.GLTRANSACTIONID
                ,GL.ACCOUNTNUMBER
                ,L.POSTDATE
                ,'R'
                ,case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end
                ,b.BASEAMOUNT
                ,c.JOURNAL
                ,left(case when a.ISREVERSAL = 0 then '' else 'Reversal - ' end + b.COMMENT, 255)
                ,c.BATCHID
                ,TC.COUNT
                ,b.GLACCOUNTID
                ,@BASECURRENCYID
                ,b.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,ROW_NUMBER() OVER(partition by L.FINANCIALTRANSACTIONID ORDER by L.FINANCIALTRANSACTIONID)
                ,L.FINANCIALTRANSACTIONID
                ,c.TABLENAMECODE                
            from  @Pegadog as a 
                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID and c.TABLENAMECODE = 14
                inner join dbo.FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                inner join @TTranCount TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID        

            where L.POSTSTATUSCODE = 1 and  a.ISREVERSAL = 0
            order by TC.TRANIDENTIFIERID

            update @Pegadog set ISUSED = 1
                from @Pegadog tv1 inner join
                @WORKTABLE V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID

            delete  from @TTranCount
        end





    if exists(select 1 from @Pegadog where IsUsed = 0)
        begin

        insert into @Leftover(TRANIDENTIFIERID, TABLENAMECODE, Count)
            select     L.FINANCIALTRANSACTIONID,X.TABLENAMECODE, COUNT(J.ID)
            from dbo.FINANCIALTRANSACTIONLINEITEM L 
                join dbo.JOURNALENTRY J on J.FINANCIALTRANSACTIONLINEITEMID = L.ID
                join dbo.JOURNALENTRY_EXT X on J.ID = X.ID 
                Join @Pegadog P on P.GLTRANSACTIONID = J.ID
            where L.FINANCIALTRANSACTIONID is not null
            group by L.FINANCIALTRANSACTIONID, X.TABLENAMECODE  having COUNT(J.ID) > 1


        If @ISPEACHTREE = 0
            begin

            insert into @WORKTABLE 
            (
                GLTRANSACTIONID, 
                ACCOUNTSTRING, 
                POSTDATE, 
                TRANTYPE, 
                DEBITCREDIT, 
                AMOUNT,
                JOURNAL, 
                REFERENCE, 
                BATCH, 
                NUMBEROFRECORD, 
                GLACCOUNTID, 
                BASECURRENCYID,
                ORGANIZATIONAMOUNT,
                ROWNUMBER,
                PARENTID,
                TABLENAMECODE
            )
                select 
                    a.GLTRANSACTIONID, 
                    GL.ACCOUNTNUMBER,
                    COALESCE(ADJUSTMENT.POSTDATE,UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE,L.POSTDATE),
                    'R', 
                    case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, 
                    b.BASEAMOUNT,
                    c.JOURNAL,
                    left(case when a.ISREVERSAL = 0 then b.COMMENT else 'Reversal' + ' - ' + b.COMMENT end, 255), 
                    c.BATCHID,
                    TC.COUNT, 
                    b.GLACCOUNTID,
                    @BASECURRENCYID,
                    b.ORGAMOUNT as ORGANIZATIONAMOUNT,
                    ROW_NUMBER() OVER(partition by a.LeftoverID ORDER by a.LeftoverID)
                    ,L.FINANCIALTRANSACTIONID
                    ,c.TABLENAMECODE                
                from 
                    @Pegadog as a                 
            inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
            inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID 
            inner join FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
            inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
            inner join @Leftover TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID and c.TABLENAMECODE = TC.TABLENAMECODE
            left join dbo.ADJUSTMENT on L.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on L.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1        

                where 
                    a.ISUSED = 0
            end 
        else
            begin

                    insert into @WORKTABLE 
                    (
                        GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,NUMBEROFRECORD,GLACCOUNTID, BASECURRENCYID,ORGANIZATIONAMOUNT,ROWNUMBER
                        ,PARENTID,TABLENAMECODE
                    )
                        select 
                            a.GLTRANSACTIONID, 
                            GL.ACCOUNTNUMBER,
                            COALESCE(ADJUSTMENT.POSTDATE,UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE,L.POSTDATE),
                            'R', 
                            case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,                         
                            b.BASEAMOUNT,
                            c.JOURNAL,
                            left(case when a.ISREVERSAL = 0 then b.COMMENT else 'Reversal' + ' - ' + b.COMMENT end, 255),
                            c.BATCHID,
                            TC.COUNT, 
                            b.GLACCOUNTID,
                            @BASECURRENCYID,
                            b.ORGAMOUNT as ORGANIZATIONAMOUNT,
                            ROW_NUMBER() OVER(partition by a.LeftoverID ORDER by a.LeftoverID)
                            ,L.FINANCIALTRANSACTIONID
                            ,c.TABLENAMECODE                        
                        from 
                            @Pegadog as a 
                                inner join dbo.JOURNALENTRY as b on a.GLTRANSACTIONID = b.ID 
                                inner join dbo.JOURNALENTRY_EXT as c on c.ID = b.ID 
                                inner join FINANCIALTRANSACTIONLINEITEM L on b.FINANCIALTRANSACTIONLINEITEMID = L.ID
                                inner join dbo.GLACCOUNT GL on GL.ID = b.GLACCOUNTID
                                inner join @Leftover TC on L.FINANCIALTRANSACTIONID = TC.TRANIDENTIFIERID and c.TABLENAMECODE = TC.TABLENAMECODE                                
                                left join dbo.ADJUSTMENT on L.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
                                left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on L.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1                
                            where 
                                a.ISUSED = 0

            end
    end
return
end