USP_POSTTOGLPROCESS_SUMMARIZE

            CREATE proc dbo.USP_POSTTOGLPROCESS_SUMMARIZE
            (@ID uniqueidentifier,
            @TEMPTABLE nvarchar(255)            
            )

            as
                set nocount on


                declare @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,
                PROJECT nvarchar(100)
                )            
                declare @REVENUEPOSTMETHOD tinyint    
                declare @REVENUEREFERENCE nvarchar(255)
                declare @CASHPOSTMETHOD tinyint    
                declare @CASHREFERENCE nvarchar(255)
                declare @RECEIVABLEPOSTMETHOD tinyint    
                declare @RECEIVABLEREFERENCE nvarchar(255)                
                declare @SUMMARIZEDATE tinyint    
                declare @SUMMARIZEDATESQL nvarchar(20)
                declare @DEPOSITSUMMARIZED nvarchar(20) = 'Summarized Deposit'
                declare @BENEFITSUMMARIZED nvarchar(20) = 'Summarized Benefits'                

                select 
                @REVENUEPOSTMETHOD=REVENUEPOSTMETHODCODE, @REVENUEREFERENCE=isnull(nullif(REVENUEREFERENCE,''),'Summarized Revenue'), 
                @CASHPOSTMETHOD=CASHPOSTMETHODCODE, @CASHREFERENCE=isnull(nullif(CASHREFERENCE,''),'Summarized Cash'), 
                @RECEIVABLEPOSTMETHOD=ARPOSTMETHODCODE, @RECEIVABLEREFERENCE=isnull(nullif(ARREFERENCE,''),'Summarized Receivables'),                
                @SUMMARIZEDATE=SUMMARIZECODE from POSTTOGLPROCESSDETAIL where id = @ID

                if @REVENUEPOSTMETHOD = 0 and @CASHPOSTMETHOD = 0 and @RECEIVABLEPOSTMETHOD = 0 
                    set @SUMMARIZEDATE = 0


    if @SUMMARIZEDATE = 0  -- by Post Date

        begin    
            if @REVENUEPOSTMETHOD = 0     -- Detail

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select a.GLTRANSACTIONID,b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, b.postdate)),'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on e.ID = c.REVENUEID
                left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0 and d.TRANSACTIONTYPECODE = 1 and ((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
                or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
                --or (d.REVENUETRANSACTIONTYPECODE  = 4 and d.APPLICATIONCODE = 0)

                or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
                    union all                
                select a.GLTRANSACTIONID,b.ACCOUNT,isnull(adj.postdate, b.postdate),'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BENEFITADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0             
            else        
                if @REVENUEPOSTMETHOD = 1  -- Summary 

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))),
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,@REVENUEREFERENCE,null,''
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on e.ID = c.REVENUEID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID or f.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as g on g.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as h on h.ID = g.BANKACCOUNTTRANSACTIONID
                    where a.ISREVERSAL = 0 and b.TRANSACTIONTYPECODE = 1 and (((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
                    or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
                    --or (d.REVENUETRANSACTIONTYPECODE  = 4 and d.APPLICATIONCODE = 0)

                    or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
                    or h.ID is not null or k.ID is not null)
                    group by b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))),b.TRANSACTIONTYPECODE,JOURNAL        
                        union all                    
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    isnull(adj.postdate,  b.postdate),
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,@BENEFITSUMMARIZED,null,''
                    from  #Pegadog as a 
                    join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BENEFITADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    where a.ISREVERSAL = 0 
                    group by b.ACCOUNT,isnull(adj.postdate,b.postdate),b.TRANSACTIONTYPECODE,JOURNAL                    

                else  -- Summary by application type

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))),
                    'R',case d.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,
                    @REVENUEREFERENCE + case when d.APPLICATION is null then '' else ' - ' + d.APPLICATION end
                    ,null,''
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on e.ID = c.REVENUEID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID or f.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as g on g.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as h on h.ID = g.BANKACCOUNTTRANSACTIONID
                    where a.ISREVERSAL = 0 and b.TRANSACTIONTYPECODE = 1 and (((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
                    or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
                    --or (d.REVENUETRANSACTIONTYPECODE  = 4 and d.APPLICATIONCODE = 0)

                    or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
                    or h.ID is not null or k.ID is not null)
                    group by b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))),d.TRANSACTIONTYPECODE,d.APPLICATION,JOURNAL
                        union all
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    isnull(adj.postdate, b.postdate),
                    'R',case d.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,
                    @BENEFITSUMMARIZED 
                    ,null,''
                    from  #Pegadog as a 
                    join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BENEFITADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    where a.ISREVERSAL = 0 
                    group by b.ACCOUNT,isnull(adj.postdate,  b.postdate),d.TRANSACTIONTYPECODE,JOURNAL



            update #Pegadog set ISUSED = 1 where GLTRANSACTIONID in ((select a.GLTRANSACTIONID from  #Pegadog as a 
            left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
            left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
            left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as g on g.GLTRANSACTIONID = b.ID and @REVENUEPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTTRANSACTION as h on h.ID = g.BANKACCOUNTTRANSACTIONID and @REVENUEPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID and @REVENUEPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID and @REVENUEPOSTMETHOD != 0
            where a.ISREVERSAL = 0 and b.TRANSACTIONTYPECODE = 1 and (((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
            or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
            or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
            or h.ID is not null or k.ID is not null))    
                union all
            (select a.GLTRANSACTIONID from  #Pegadog as a 
            inner join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = a.GLTRANSACTIONID
            where a.ISREVERSAL = 0 ))


            if @CASHPOSTMETHOD = 0     -- Detail            

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select a.GLTRANSACTIONID,b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, b.postdate)),'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on e.ID = c.REVENUEID
                left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
                or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0))

            else
                if @CASHPOSTMETHOD = 1 -- Summary                 

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate,b.postdate))),    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,@CASHREFERENCE,null,''                    
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on e.ID = c.REVENUEID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID or f.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as g on g.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as h on h.ID = g.BANKACCOUNTTRANSACTIONID
                    where a.ISREVERSAL = 0 and (((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
                    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                    or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0))
                    or ((h.ID is not null or k.ID is not null) and b.TRANSACTIONTYPECODE = 0))
                    group by b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate,b.postdate))),b.TRANSACTIONTYPECODE,JOURNAL
                else  -- Summary by deposit

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))),    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,
                    case when e.DEPOSITID IS null then @CASHREFERENCE else @DEPOSITSUMMARIZED + ' - ' + dbo.UFN_BANKACCOUNT_GETDEPOSITNAME(e.DEPOSITID)end  ,
                    null,''                    
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on c.REVENUEID = e.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID or f.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as g on g.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as h on h.ID = g.BANKACCOUNTTRANSACTIONID
                    where a.ISREVERSAL = 0 and (((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
                    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                    or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0))
                    or ((h.ID is not null or k.ID is not null) and b.TRANSACTIONTYPECODE = 0))
                    group by b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))),b.TRANSACTIONTYPECODE,JOURNAL,e.DEPOSITID

            update #Pegadog set ISUSED = 1 where GLTRANSACTIONID in (select a.GLTRANSACTIONID from  #Pegadog as a 
            left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
            left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
            left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as g on g.GLTRANSACTIONID = b.ID and @CASHPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTTRANSACTION as h on h.ID = g.BANKACCOUNTTRANSACTIONID and @CASHPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID and @CASHPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID and @CASHPOSTMETHOD != 0
            where a.ISREVERSAL = 0 and (((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
            or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
            or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0)))
            or ((h.ID is not null or k.ID is not null) and b.TRANSACTIONTYPECODE = 0))

            if @RECEIVABLEPOSTMETHOD = 0  -- Detail    

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select a.GLTRANSACTIONID,b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, b.postdate)),'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on c.REVENUEID = e.ID
                left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 0 )
                --or (d.REVENUETRANSACTIONTYPECODE = 4 and d.APPLICATIONCODE = 0  and d.TRANSACTIONTYPECODE = 0 )

                or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5) and d.TRANSACTIONTYPECODE = 0)
                or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and d.TRANSACTIONTYPECODE = 1)
                or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205))
            else        -- Summary

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                isnull(adj.postdate, isnull(f.postdate, b.postdate)),
                'R',case d.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                sum(b.AMOUNT),JOURNAL,@RECEIVABLEREFERENCE,null,''                
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on c.REVENUEID = e.ID
                left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 0 )
                --or (d.REVENUETRANSACTIONTYPECODE = 4 and d.APPLICATIONCODE = 0  and d.TRANSACTIONTYPECODE = 0 )

                or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5) and d.TRANSACTIONTYPECODE = 0)
                or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and d.TRANSACTIONTYPECODE = 1)
                or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205))
                group by b.ACCOUNT,isnull(adj.postdate, isnull(f.postdate, b.postdate)),d.TRANSACTIONTYPECODE,JOURNAL

            update #Pegadog set ISUSED = 1 where GLTRANSACTIONID in (select a.GLTRANSACTIONID from  #Pegadog as a 
            join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
            join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
            join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 0 )
            --or (d.REVENUETRANSACTIONTYPECODE = 4 and d.APPLICATIONCODE = 0  and d.TRANSACTIONTYPECODE = 0 )

            or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5) and d.TRANSACTIONTYPECODE = 0)
            or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and d.TRANSACTIONTYPECODE = 1)
            or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205)))
        end

    else  -- by Fiscal Period

        begin    
            if @REVENUEPOSTMETHOD = 0      -- Detail    

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select a.GLTRANSACTIONID,b.ACCOUNT,
                isnull(adj.postdate, isnull(f.postdate, b.postdate)),
                'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on c.REVENUEID = e.ID
                left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0 and d.TRANSACTIONTYPECODE = 1 and ((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
                or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
                --or (d.REVENUETRANSACTIONTYPECODE  = 4 and d.APPLICATIONCODE = 0)

                or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
                    union all                
                select a.GLTRANSACTIONID,b.ACCOUNT,isnull(adj.postdate, b.postdate),'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BENEFITADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0                 

            else        
                if @REVENUEPOSTMETHOD = 1  -- Summary

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    e.enddate as POSTDATE,
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,
                    @REVENUEREFERENCE
                    ,null,''
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as f on c.REVENUEID = f.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION g on g.ID = f.DEPOSITID or g.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as h on h.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as i on i.ID = h.BANKACCOUNTTRANSACTIONID
                    join dbo.GLFISCALPERIOD as e on isnull(adj.postdate, isnull(g.postdate, isnull(i.postdate, b.postdate))) between e.STARTDATE and e.ENDDATE
                    where a.ISREVERSAL = 0 and b.TRANSACTIONTYPECODE = 1 and (((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
                    or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
                    --or (d.REVENUETRANSACTIONTYPECODE  = 4 and d.APPLICATIONCODE = 0)

                    or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
                    or i.ID is not null or k.ID is not null)
                    group by b.ACCOUNT,e.enddate,b.TRANSACTIONTYPECODE,JOURNAL
                        union all                    
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    e.enddate as POSTDATE,
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,@BENEFITSUMMARIZED,null,''
                    from  #Pegadog as a 
                    join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BENEFITADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    join dbo.GLFISCALPERIOD as e on isnull(adj.postdate, b.postdate) between e.STARTDATE and e.ENDDATE
                    where a.ISREVERSAL = 0 
                    group by b.ACCOUNT,e.enddate,b.TRANSACTIONTYPECODE,JOURNAL                    
                else  -- Summary by application type

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    e.enddate as POSTDATE,
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,
                    @REVENUEREFERENCE + case when d.APPLICATION is null then '' else ' - ' + d.APPLICATION end
                    ,null,''
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as f on c.REVENUEID = f.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION g on g.ID = f.DEPOSITID or g.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as h on h.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as i on i.ID = h.BANKACCOUNTTRANSACTIONID
                    join dbo.GLFISCALPERIOD as e on isnull(adj.postdate, isnull(g.postdate, isnull(i.postdate, b.postdate))) between e.STARTDATE and e.ENDDATE
                    where a.ISREVERSAL = 0 and b.TRANSACTIONTYPECODE = 1 and (((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
                    or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
                    --or (d.REVENUETRANSACTIONTYPECODE  = 4 and d.APPLICATIONCODE = 0)

                    or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
                    or i.ID is not null or k.ID is not null)
                    group by b.ACCOUNT,e.enddate,b.TRANSACTIONTYPECODE,d.APPLICATION,JOURNAL
                        union all                    
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    e.enddate as POSTDATE,
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,@BENEFITSUMMARIZED 
                    ,null,''
                    from  #Pegadog as a 
                    join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BENEFITADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    join dbo.GLFISCALPERIOD as e on isnull(adj.postdate, b.postdate) between e.STARTDATE and e.ENDDATE
                    where a.ISREVERSAL = 0 
                    group by b.ACCOUNT,e.enddate,b.TRANSACTIONTYPECODE,JOURNAL    

            update #Pegadog set ISUSED = 1 where GLTRANSACTIONID in ((select a.GLTRANSACTIONID from  #Pegadog as a 
            left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
            left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
            left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as h on h.GLTRANSACTIONID = b.ID and @REVENUEPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTTRANSACTION as i on i.ID = h.BANKACCOUNTTRANSACTIONID and @REVENUEPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID and @REVENUEPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID and @REVENUEPOSTMETHOD != 0
            where a.ISREVERSAL = 0 and b.TRANSACTIONTYPECODE = 1 and (((d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
            or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
            --or (d.REVENUETRANSACTIONTYPECODE  = 4 and d.APPLICATIONCODE = 0)

            or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201)))
            or i.ID is not null or k.ID is not null)
                union all
            (select a.GLTRANSACTIONID from  #Pegadog as a 
            inner join dbo.BENEFITGLDISTRIBUTION as c on c.GLTRANSACTIONID = a.GLTRANSACTIONID 
            where a.ISREVERSAL = 0 ))            

            if @CASHPOSTMETHOD = 0      -- Detail                

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select a.GLTRANSACTIONID,b.ACCOUNT,
                isnull(adj.postdate, isnull(f.postdate, b.postdate)),
                'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on c.REVENUEID = e.ID
                left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
                or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0))
            else
                if @CASHPOSTMETHOD = 1     -- Summary            

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    e.enddate as POSTDATE,
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,@CASHREFERENCE,null,''
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as f on c.REVENUEID = f.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION g on g.ID = f.DEPOSITID or g.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as h on h.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as i on i.ID = h.BANKACCOUNTTRANSACTIONID
                    join dbo.GLFISCALPERIOD as e on isnull(adj.postdate, isnull(g.postdate, isnull(i.postdate, b.postdate))) between e.STARTDATE and e.ENDDATE
                    where a.ISREVERSAL = 0 and (((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
                    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                    or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0))
                    or ((i.ID is not null or k.ID is not null) and b.TRANSACTIONTYPECODE = 0))
                    group by b.ACCOUNT,e.enddate,b.TRANSACTIONTYPECODE,JOURNAL
                else   -- Summary by deposit

                    insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                    select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                    e.enddate as POSTDATE,
                    'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                    sum(b.AMOUNT),JOURNAL,
                    case when f.DEPOSITID IS null then @CASHREFERENCE else @DEPOSITSUMMARIZED + ' - ' + dbo.UFN_BANKACCOUNT_GETDEPOSITNAME(f.DEPOSITID)end  ,
                    null,''
                    from  #Pegadog as a 
                    left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                    left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as f on c.REVENUEID = f.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID
                    left outer join dbo.BANKACCOUNTTRANSACTION g on g.ID = f.DEPOSITID or g.ID = k.DEPOSITID
                    left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                    left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as h on h.GLTRANSACTIONID = b.ID
                    left outer join dbo.BANKACCOUNTTRANSACTION as i on i.ID = h.BANKACCOUNTTRANSACTIONID
                    join dbo.GLFISCALPERIOD as e on isnull(adj.postdate, isnull(g.postdate, isnull(i.postdate, b.postdate))) between e.STARTDATE and e.ENDDATE
                    where a.ISREVERSAL = 0 and (((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
                    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                    or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0))
                    or ((i.ID is not null or k.ID is not null) and b.TRANSACTIONTYPECODE = 0))
                    group by b.ACCOUNT,e.enddate,b.TRANSACTIONTYPECODE,JOURNAL,f.DEPOSITID

            update #Pegadog set ISUSED = 1 where GLTRANSACTIONID in (select a.GLTRANSACTIONID from  #Pegadog as a 
            left outer join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
            left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
            left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION as h on h.GLTRANSACTIONID = b.ID and @CASHPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTTRANSACTION as i on i.ID = h.BANKACCOUNTTRANSACTIONID and @CASHPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION as j on j.GLTRANSACTIONID = b.ID and @CASHPOSTMETHOD != 0
            left outer join dbo.BANKACCOUNTDEPOSITCORRECTION as k on k.ID = j.BANKACCOUNTDEPOSITCORRECTIONID and @CASHPOSTMETHOD != 0
            where a.ISREVERSAL = 0 and (((d.REVENUETRANSACTIONTYPECODE  = 0 and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11) and d.TRANSACTIONTYPECODE = 0)
            or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
            or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 and d.TRANSACTIONTYPECODE = 0)))
            or ((i.ID is not null or k.ID is not null) and b.TRANSACTIONTYPECODE = 0))

            if @RECEIVABLEPOSTMETHOD = 0   -- Detail        

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select a.GLTRANSACTIONID,b.ACCOUNT,
                isnull(adj.postdate, isnull(f.postdate, b.postdate)),
                'R',case d.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,b.PROJECT
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on c.REVENUEID = e.ID
                left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 0 )
                --or (d.REVENUETRANSACTIONTYPECODE = 4 and d.APPLICATIONCODE = 0  and d.TRANSACTIONTYPECODE = 0 )

                or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5) and d.TRANSACTIONTYPECODE = 0)
                or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and d.TRANSACTIONTYPECODE = 1)
                or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205))
            else   -- Summary

                insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
                select '99999999-9999-9999-9999-999999999999', b.ACCOUNT,
                e.enddate as POSTDATE,
                'R',case d.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
                sum(b.AMOUNT),JOURNAL,@RECEIVABLEREFERENCE,null,''
                from  #Pegadog as a 
                join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
                join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
                join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
                left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as f on c.REVENUEID = f.ID
                left outer join dbo.BANKACCOUNTTRANSACTION g on g.ID = f.DEPOSITID
                left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
                join dbo.GLFISCALPERIOD as e on isnull(adj.postdate, isnull(g.postdate, b.postdate)) between e.STARTDATE and e.ENDDATE
                where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 0 )
                --or (d.REVENUETRANSACTIONTYPECODE = 4 and d.APPLICATIONCODE = 0  and d.TRANSACTIONTYPECODE = 0 )

                or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5) and d.TRANSACTIONTYPECODE = 0)
                or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and d.TRANSACTIONTYPECODE = 1)
                or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205))
                group by b.ACCOUNT,e.enddate,d.TRANSACTIONTYPECODE,d.APPLICATIONCODE,JOURNAL            

            update #Pegadog set ISUSED = 1 where GLTRANSACTIONID in (select a.GLTRANSACTIONID from  #Pegadog as a 
            join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
            join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID 
            join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            where a.ISREVERSAL = 0 and ((d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 0 )
            --or (d.REVENUETRANSACTIONTYPECODE = 4 and d.APPLICATIONCODE = 0  and d.TRANSACTIONTYPECODE = 0 )

            or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5) and d.TRANSACTIONTYPECODE = 0)
            or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and d.TRANSACTIONTYPECODE = 1)
            or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and d.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205)))
        end

        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT)
        select a.GLTRANSACTIONID,b.ACCOUNT,
        CASE WHEN (@SUMMARIZEDATE != 0) THEN i.enddate ELSE isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))) END,
        'R',case b.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,b.AMOUNT,JOURNAL,
        case when a.ISREVERSAL = 0 then b.REFERENCE else 'Reversal' + ' - ' +  b.REFERENCE end, 
        BATCHID,b.PROJECT
        from  #Pegadog  as a join dbo.GLTRANSACTION as b on a.GLTRANSACTIONID = b.ID 
        left outer join dbo.REVENUEGLDISTRIBUTION as c on c.GLTRANSACTIONID = b.ID
        left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on c.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID                
        left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on c.REVENUEID = e.ID
        left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
        left outer join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION g on g.GLTRANSACTIONID = b.ID
        left outer join dbo.BANKACCOUNTTRANSACTION h on h.ID = g.BANKACCOUNTTRANSACTIONID
        left outer join dbo.ADJUSTMENT adj on adj.REVENUEID = c.REVENUEID and adj.POSTSTATUSCODE = 1
        left outer join dbo.GLFISCALPERIOD as i on (@SUMMARIZEDATE != 0) and (isnull(adj.postdate, isnull(f.postdate, isnull(h.postdate, b.postdate))) between i.STARTDATE and i.ENDDATE)
        where ISUSED = 0



                    select    GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,''--PROJECT

                        from 
                        @WORKTABLE order by ACCOUNTSTRING