UFN_POSTTOGLPROCESS_SUMMARIZEWITHGLACCOUNTID

CREATE function dbo.UFN_POSTTOGLPROCESS_SUMMARIZEWITHGLACCOUNTID(@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,
    PROJECT nvarchar(100),
    GLACCOUNTID uniqueidentifier,
    BASECURRENCYID uniqueidentifier,
    ORGANIZATIONAMOUNT money,
    DUMMYCOL integer identity(1,1),
    primary key(GLTRANSACTIONID, DUMMYCOL)
)
with execute as caller
as
begin
    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'                
    declare @BASECURRENCYID uniqueidentifier;
    declare @REVALUATIONGAINLOSSPOSTMETHOD tinyint;
    declare @REVALUATIONGAINLOSSREFERENCE nvarchar(255);

    select 
        @REVENUEPOSTMETHOD = POSTTOGLPROCESSDETAIL.REVENUEPOSTMETHODCODE, 
        @REVENUEREFERENCE = isnull(nullif(POSTTOGLPROCESSDETAIL.REVENUEREFERENCE,''),'Summarized Revenue'), 
        @CASHPOSTMETHOD = POSTTOGLPROCESSDETAIL.CASHPOSTMETHODCODE, 
        @CASHREFERENCE = isnull(nullif(POSTTOGLPROCESSDETAIL.CASHREFERENCE,''),'Summarized Cash'), 
        @RECEIVABLEPOSTMETHOD = POSTTOGLPROCESSDETAIL.ARPOSTMETHODCODE, 
        @RECEIVABLEREFERENCE = isnull(nullif(POSTTOGLPROCESSDETAIL.ARREFERENCE,''),'Summarized Receivables'),                
        @SUMMARIZEDATE = POSTTOGLPROCESSDETAIL.SUMMARIZECODE,
        @BASECURRENCYID = coalesce(CURRENCYSET.BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()),
        @REVALUATIONGAINLOSSPOSTMETHOD = POSTTOGLPROCESSDETAIL.REVALUATIONGAINLOSSPOSTMETHODCODE, 
        @REVALUATIONGAINLOSSREFERENCE = isnull(nullif(POSTTOGLPROCESSDETAIL.REVALUATIONGAINLOSSREFERENCE,''),'Summarized Revaluation Gain/Loss')
    from 
        dbo.POSTTOGLPROCESSDETAIL 
        left join dbo.POSTTOGLPROCESS on POSTTOGLPROCESSDETAIL.ID = POSTTOGLPROCESS.ID
        left join dbo.PDACCOUNTSYSTEM ON POSTTOGLPROCESS.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
        left join dbo.CURRENCYSET ON PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    where 
        POSTTOGLPROCESSDETAIL.ID = @ID;

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

declare @PD2 table (GLTRANSACTIONID uniqueidentifier primary key, ACCOUNT nvarchar(100), ISREVERSAL bit, POSTDATE datetime, TRANSACTIONTYPECODE tinyint, 
    AMOUNT money, JOURNAL nvarchar(255), PROJECT nvarchar(100), GLACCOUNTID uniqueidentifier, ORGAMOUNT money, FINANCIALTRANSACTIONLINEITEMID uniqueidentifier, GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
    BATCHID uniqueidentifier, COMMENT nvarchar(255), FINANCIALTRANSACTIONID uniqueidentifier, TABLENAMECODE tinyint, ISUSED bit,
    CREDITPAYMENTID uniqueidentifier, CREDITITEMID uniqueidentifier)

insert into @PD2 (GLTRANSACTIONID, ACCOUNT, ISREVERSAL, POSTDATE, TRANSACTIONTYPECODE, AMOUNT, JOURNAL, PROJECT, GLACCOUNTID, ORGAMOUNT, 
    GLPAYMENTMETHODREVENUETYPEMAPPINGID, BATCHID, COMMENT, FINANCIALTRANSACTIONID, TABLENAMECODE, ISUSED, CREDITPAYMENTID, CREDITITEMID)
select InRecs.ID, GLACCOUNT.ACCOUNTNUMBER, InRecs.Flag, JOURNALENTRY.POSTDATE, JOURNALENTRY.TRANSACTIONTYPECODE, JOURNALENTRY.BASEAMOUNT, JOURNALENTRY_EXT.JOURNAL,
    JOURNALENTRY_EXT.PROJECT, JOURNALENTRY.GLACCOUNTID, JOURNALENTRY.ORGAMOUNT, JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID, JOURNALENTRY.BATCHID, JOURNALENTRY.COMMENT,
    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID, JOURNALENTRY_EXT.TABLENAMECODE, 0, JOURNALENTRY_EXT.CREDITPAYMENTID, JOURNALENTRY_EXT.CREDITITEMID
from @InRecords InRecs inner join dbo.JOURNALENTRY on InRecs.ID = JOURNALENTRY.ID
inner join dbo.JOURNALENTRY_EXT on InRecs.ID = JOURNALENTRY_EXT.ID
inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID 
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
option (recompile)

if @SUMMARIZEDATE = 0  -- by Post Date

begin
    if @REVENUEPOSTMETHOD = 0     -- Detail

        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT)
        select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, JE2.POSTDATE, PD.POSTDATE), 'R', 
            case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, PD.JOURNAL,
            left(PD.COMMENT,255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
        left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
        left join dbo.JOURNALENTRY JE2 on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = JE2.ID
        left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
        left join dbo.BENEFITADJUSTMENT ON PD.FINANCIALTRANSACTIONID = BENEFITADJUSTMENT.ID and BENEFITADJUSTMENT.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0
        and (
                (
                    PD.TRANSACTIONTYPECODE = 1
                    and PD.TABLENAMECODE = 1
                    and (
                        (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0) --Pledge transaction, gift application

                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
                    ) 
                )
                or PD.TABLENAMECODE = 5
            )

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, 'R', 
            case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, PD.JOURNAL,
            left(case PD.IsReversal when 0 then '' else 'Reversal - ' end  + PD.COMMENT,255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
        where PD.TABLENAMECODE = 6
        and (
                (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
            )
        option (recompile)
    else
    if @REVENUEPOSTMETHOD = 1  -- Summary 

    begin
        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, V1.POSTDATE, 'R', case when V1.TRANSACTIONTYPECODE = 1 THEN 'C' ELSE 'D' END, sum(V1.AMOUNT), V1.JOURNAL, @REVENUEREFERENCE, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE,PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TRANSACTIONTYPECODE = 1 
                and PD.TABLENAMECODE = 1
                and (
                    (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0) --Pledge transaction, gift application

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201) --Non-bookable pledge

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5)
                )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
            where PD.TABLENAMECODE = 6
                and (
                        (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                        or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                    )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 4
                and (
                    (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) 
                    or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0)
                )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 3
                and (
                        (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) 
                        or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0)
                )
        ) V1
        group by V1.ACCOUNT, V1.PostDate, V1.JOURNAL, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID
        option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT)
        select 
            '99999999-9999-9999-9999-999999999999', V1.Account,
            V1.Postdate, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT), V1.JOURNAL, @BENEFITSUMMARIZED, null, '', V1.GLACCOUNTID,@BASECURRENCYID,sum(V1.ORGAMOUNT)
        from (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD 
            inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on
                PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
            left outer join dbo.BENEFITADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
            where PD.IsReversal = 0
                and PD.TABLENAMECODE = 5
        ) V1
        group by V1.ACCOUNT ,V1.Postdate, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end
    else if @REVENUEPOSTMETHOD = 2  -- Summary by application type

        begin
        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, V1.PostDate, 'R', case when V1.TRANSACTIONTYPECODE = 1 then 'C' else 'D' end, sum(V1.AMOUNT), V1.JOURNAL, @REVENUEREFERENCE+' - '+V1.APPLICATION, null, '',V1.GLACCOUNTID, @BASECURRENCYID,sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATION, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD
            inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and PD.TRANSACTIONTYPECODE = 1 
                and (
                    (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0) --Pledge transaction, gift application

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201) --Non-bookable pledge

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5)
                )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, CREDIT.[TYPE] APPLICATION, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
            left join dbo.CREDITITEM_EXT EXT on EXT.ID = DISCOUNT.ID
            left join dbo.CREDIT on EXT.CREDITID = CREDIT.ID or REFUND.CREDITID = CREDIT.ID
            where PD.TABLENAMECODE = 6
                and (
                        (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                        or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                    )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTTRANSACTION.TRANSACTIONTYPE, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            left join dbo.JOURNALENTRY_EXT on PD.GLTRANSACTIONID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1    --Not sure about this

            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 4 
                and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0))

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPE, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0
                and PD.TABLENAMECODE = 3
                and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0))
        ) V1
        group by V1.ACCOUNT, V1.PostDate, V1.JOURNAL, V1.APPLICATION, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID
        option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.Account, V1.Postdate,'R',case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT),V1.JOURNAL,@BENEFITSUMMARIZED,null,'',V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD
            inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID 
            left outer join dbo.BENEFITADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
            where PD.IsReversal = 0
                and PD.TABLENAMECODE = 5
        ) V1
        group by V1.ACCOUNT ,V1.Postdate, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end
    else  -- Summary by deposit

    begin
        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, V1.POSTDATE, 'R', case when V1.TRANSACTIONTYPECODE = 1 THEN 'C' ELSE 'D' END, sum(V1.AMOUNT), V1.JOURNAL,
            case when (V1.DEPOSITID IS null or @REVENUEREFERENCE != 'Summarized Revenue') then @REVENUEREFERENCE else @DEPOSITSUMMARIZED + ' - ' + Cast(BAT.TRANSACTIONNUMBER as nvarchar(10)) + ' - ' + BA.ACCOUNTNAME end, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE,PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BANKACCOUNTTRANSACTION.ID [DEPOSITID]
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TRANSACTIONTYPECODE = 1 
                and PD.TABLENAMECODE = 1
                and (
                        (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0) --Pledge transaction, gift application

                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201) --Non-bookable pledge

                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5)
                    )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BADP.DEPOSITID
            from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
            left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on PD.FINANCIALTRANSACTIONID = BADP.ID
            where PD.TABLENAMECODE = 6
                and    (
                        (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                    or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
            )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BANKACCOUNTTRANSACTION.ID [DEPOSITID]
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and (
                    (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) 
                    or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0)
                )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BANKACCOUNTTRANSACTION.ID [DEPOSITID]
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 3
                and (
                        (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) 
                        or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0)
                    )
        ) V1
        left join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = V1.DEPOSITID
        left join dbo.BANKACCOUNT BA ON BAT.BANKACCOUNTID = BA.ID
        group by V1.DEPOSITID, V1.ACCOUNT, V1.PostDate, V1.JOURNAL, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
        option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,GLACCOUNTID,BASECURRENCYID,ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.Account,
            V1.Postdate, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT), V1.JOURNAL, @BENEFITSUMMARIZED, null, '', V1.GLACCOUNTID,@BASECURRENCYID,sum(V1.ORGAMOUNT)
        from  
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from
            @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID 
            left outer join dbo.BENEFITADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
            where PD.IsReversal = 0
            and PD.TABLENAMECODE = 5
        ) V1
        group by V1.ACCOUNT ,V1.Postdate, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end

    update @PD2 set ISUSED = 1
    from @PD2 tv1 inner join
    (
        select PD.GLTRANSACTIONID 
        from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 1
            and PD.TRANSACTIONTYPECODE = 1 
            and (
                (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0) --Pledge transaction, gift application

                or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201) --Non-bookable pledge

                or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5 and @REVENUEPOSTMETHOD != 0)
            )

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0))
            and @REVENUEPOSTMETHOD != 0

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        where PD.ISREVERSAL = 0  
            and PD.TABLENAMECODE = 3
            and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0))
            and @REVENUEPOSTMETHOD != 0

        union all
        select PD.GLTRANSACTIONID 
        from  @PD2 as PD 
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 5

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
        where PD.TABLENAMECODE = 6
            and (
                    (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                    or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                )
    ) V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID
    option (recompile)

    if @CASHPOSTMETHOD = 0     -- Detail

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(adj.POSTDATE, f.POSTDATE, PD.POSTDATE), 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from  @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
        left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on PD.FINANCIALTRANSACTIONID = e.ID
        left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
        left outer join dbo.ADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 1
            and (
                    (
                        d.REVENUETRANSACTIONTYPECODE = 0
                        and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,10,11,13,18,19,203)
                        and PD.TRANSACTIONTYPECODE = 0
                        and (
                            d.REVENUESPLITTYPECODE <> 19 /* Unearned revenue */ 
                            or d.TRANSACTIONTYPECODE = 0 /* Debit */
                        )
                    )
                    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                    or (
                        d.REVENUETRANSACTIONTYPECODE = 201 
                        and d.APPLICATIONCODE = 201 
                        and PD.TRANSACTIONTYPECODE = 0
                        and (d.REVENUESPLITTYPECODE <> 19 or d.TRANSACTIONTYPECODE = 0)
                    )
            )

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, PD.JOURNAL, left(PD.COMMENT,255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17,32) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16,33) and PD.TRANSACTIONTYPECODE = 0))

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, PD.JOURNAL, left(PD.COMMENT,255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT 
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 3
            and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0,2) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1,3) and PD.TRANSACTIONTYPECODE = 0))

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BAT.POSTDATE, PD.POSTDATE), 'R', 'C', PD.AMOUNT, PD.JOURNAL, left(PD.COMMENT,255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on REFUND.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
        left join dbo.BANKACCOUNTTRANSACTION BAT on BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID = BAT.ID
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 6
            and PD.TRANSACTIONTYPECODE = 1
        option (recompile)

    else if @CASHPOSTMETHOD = 1 -- Summary

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, V1.PostDate, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, 
        sum(V1.AMOUNT), V1.JOURNAL, @CASHREFERENCE, null, '',V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and (
                        (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11,13,18,19,203)
                            and PD.TRANSACTIONTYPECODE = 0
                        )
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 200 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 200)
                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and 
                            GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201 and 
                            PD.TRANSACTIONTYPECODE = 0
                        )
                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 10 
                            and PD.TRANSACTIONTYPECODE = 0
                            and (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 OR GLPAYMENTMETHODREVENUETYPEMAPPING.TRANSACTIONTYPECODE = 0)
                        )
                    )

            union 
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) , PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 0))

            union 
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 3
            and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 0))

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BAT.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on REFUND.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION as BAT on BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID = BAT.ID
            where PD.ISREVERSAL = 0
                and PD.TABLENAMECODE = 6
                and PD.TRANSACTIONTYPECODE = 1
        ) V1
        group by V1.ACCOUNT, V1.PostDate, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end
    else  -- Summary by deposit

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, V1.PostDate, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, 
        sum(V1.AMOUNT), V1.JOURNAL,
            case when (V1.DEPOSITID IS null or @CASHREFERENCE != 'Summarized Cash') then @CASHREFERENCE else @DEPOSITSUMMARIZED + ' - ' + Cast(BAT.TRANSACTIONNUMBER as nvarchar(10)) + ' - ' + BA.ACCOUNTNAME end, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE,PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTDEPOSITPAYMENT.DEPOSITID, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.id
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0  
                and PD.TABLENAMECODE = 1
                and (
                        (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,10,11,13,18,19,203) 
                            and PD.TRANSACTIONTYPECODE = 0
                            and (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 or GLPAYMENTMETHODREVENUETYPEMAPPING.TRANSACTIONTYPECODE = 0)
                        )
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 200 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 200)
                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201 
                            and PD.TRANSACTIONTYPECODE = 0
                            and (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 OR GLPAYMENTMETHODREVENUETYPEMAPPING.TRANSACTIONTYPECODE = 0)
                        )
                    )

            union 
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, null, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 4
                and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 0))

            union 
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTDEPOSITCORRECTION.DEPOSITID, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 3
                and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 0))

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BAT.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BAT.ID, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND ON PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on REFUND.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION as BAT ON BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID = BAT.ID
            where PD.ISREVERSAL = 0
                and PD.TABLENAMECODE = 6
                and PD.TRANSACTIONTYPECODE = 1
        ) V1
        left join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = V1.DEPOSITID
        left join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
        group by V1.ACCOUNT, V1.PostDate, V1.JOURNAL, V1.DEPOSITID, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
        option (recompile)
    end

    update @PD2 set ISUSED = 1
    from @PD2 tv1 inner join
    (
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.id
        where PD.ISREVERSAL = 0
        and PD.TABLENAMECODE = 1
        and (
                (
                    GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0
                    and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11,13,18,19,203)
                    and PD.TRANSACTIONTYPECODE = 0
                )
                or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 200 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 200)
                or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201 and PD.TRANSACTIONTYPECODE = 0)
                or (
                    GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0
                    and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 10
                    and (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 OR GLPAYMENTMETHODREVENUETYPEMAPPING.TRANSACTIONTYPECODE = 0)
                )
            )

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 0))

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 3
            and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 0))

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 6
            and PD.TRANSACTIONTYPECODE = 1
    ) V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID
    option (recompile)

    if @RECEIVABLEPOSTMETHOD = 0  -- Detail    

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(adj.POSTDATE, f.POSTDATE, PD.POSTDATE),'R',case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from  @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
        left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on PD.FINANCIALTRANSACTIONID = e.ID
        left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
        left outer join dbo.ADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 1
            and (
                    (d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 0) --Pledge transaction, gift application, debit mapping type

                    or (d.REVENUETRANSACTIONTYPECODE = 15 and d.APPLICATIONCODE in (0,5,18) and PD.TRANSACTIONTYPECODE = 0) --Membership installment plan transaction; gift, membership, add-on application; debit

                    or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5,1,16,18) and PD.TRANSACTIONTYPECODE = 0)
                    or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10,203) and PD.TRANSACTIONTYPECODE = 1 and d.REVENUESPLITTYPECODE <> 19) --AR Payments: Pledge, Grant Award, Order, Membership Installment Plan Credit Transactions | Not liability (split)

                    or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205)
                )

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD inner join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID and DISCOUNT.TYPECODE = 5
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 6
            and PD.TRANSACTIONTYPECODE = 0
        option (recompile)
    else -- Summary

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, BASECURRENCYID, GLACCOUNTID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, V1.postdate,    'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
        sum(V1.AMOUNT),V1.JOURNAL,@RECEIVABLEREFERENCE,null,'', @BASECURRENCYID, V1.GLACCOUNTID, sum(V1.ORGAMOUNT)
        from  
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID 
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and (
                        (--Pledge transaction, gift application, debit mapping type

                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0
                            and PD.TRANSACTIONTYPECODE = 0
                        )
                        or ( --Membership installment plan transaction; gift, membership, add-on application; debit

                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)
                            and PD.TRANSACTIONTYPECODE = 0
                        )
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5 and PD.TRANSACTIONTYPECODE = 0)
                        or ( --AR Payments: Pledge, Grant Award, Order, Membership Installment Plan Credit Transactions

                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (2,8,10,203) 
                            and PD.TRANSACTIONTYPECODE = 1
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 --Liability

                        )
                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0 
                            and PD.TRANSACTIONTYPECODE = 1 
                            and PAYMENTMETHODCODE = 205
                        )
                    )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID and DISCOUNT.TYPECODE = 5
            where PD.ISREVERSAL = 0
                and PD.TABLENAMECODE = 6
                and PD.TRANSACTIONTYPECODE = 0
        ) V1
        group by V1.ACCOUNT, V1.postdate, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end

    update @PD2 set ISUSED = 1 
    from @PD2 tv1 inner join
    (
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID 
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 1
            and (
                    (--Pledge transaction, gift application, debit mapping type

                        GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0
                        and PD.TRANSACTIONTYPECODE = 0
                    )
                    or ( --Membership installment plan transaction; gift, membership, add-on application; debit

                        GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)
                        and PD.TRANSACTIONTYPECODE = 0
                    )
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5 and PD.TRANSACTIONTYPECODE = 0)
                    or ( --AR Payments: Pledge, Grant Award, Order, Membership Installment Plan Credit Transactions

                        GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (2,8,10,203) 
                        and PD.TRANSACTIONTYPECODE = 1
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 --Liability

                    )
                    or (
                        GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0 
                        and PD.TRANSACTIONTYPECODE = 1 
                        and PAYMENTMETHODCODE = 205
                    )
                )

        union
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID and DISCOUNT.TYPECODE = 5
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 6
            and PD.TRANSACTIONTYPECODE = 0
    ) V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID
    option (recompile)

    --Revaluation gain/loss

    if @REVALUATIONGAINLOSSPOSTMETHOD = 0  -- Detail

    begin
        insert into @WORKTABLE 
            (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE), 'R',    case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, 
            PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD left outer join dbo.UNREALIZEDGAINLOSSADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 14
        option (recompile)
    end
    else -- Revaluation Gain/Loss Summary

    begin
        insert into @WORKTABLE 
            (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, V1.POSTDATE, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT),    V1.JOURNAL, @REVALUATIONGAINLOSSREFERENCE, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from (
            select PD.GLTRANSACTIONID, PD.ACCOUNT,     isnull(UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE, PD.POSTDATE) as PostDate, 
                PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on PD.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 14
        ) V1
        group by V1.ACCOUNT, V1.postdate, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end

    update @PD2 
    set ISUSED = 1 
    from @PD2 tv1 
    inner join (
        select PD.GLTRANSACTIONID
        from @PD2 as PD 
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 14
    ) V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID
    option (recompile)

    insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
    select V1.GLTRANSACTIONID, V1.ACCOUNT, COALESCE(ADJUSTMENT.POSTDATE, GIFTFEEADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, BAT2.POSTDATE, UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE, V1.POSTDATE),
        'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, V1.AMOUNT, V1.JOURNAL, left(case when V1.ISREVERSAL = 0 then V1.COMMENT else 'Reversal' + ' - ' + V1.COMMENT end, 255), 
        V1.BATCHID, V1.PROJECT, V1.GLACCOUNTID, @BASECURRENCYID, V1.ORGAMOUNT
    from (
        select distinct PD.GLTRANSACTIONID, PD.ACCOUNT, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.ISREVERSAL, PD.COMMENT, PD.BATCHID, PD.PROJECT, PD.ORGAMOUNT, PD.FINANCIALTRANSACTIONID, PD.POSTDATE, PD.GLACCOUNTID 
        from @PD2 as PD where ISUSED = 0
    ) V1
    left join dbo.JOURNALENTRY_EXT as JE1 on V1.GLTRANSACTIONID = JE1.ID and JE1.TABLENAMECODE = 1
    left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on JE1.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
    left join dbo.BANKACCOUNTDEPOSITPAYMENT on V1.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
    left join dbo.FINANCIALTRANSACTION as BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
    left join dbo.ADJUSTMENT on V1.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
    left join dbo.FINANCIALTRANSACTION as BAT2 on V1.FINANCIALTRANSACTIONID = BAT2.ID and BAT2.POSTSTATUSCODE = 1
    left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on V1.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1
    left join dbo.BENEFITADJUSTMENT ON v1.FINANCIALTRANSACTIONID = BENEFITADJUSTMENT.REVENUEID and BENEFITADJUSTMENT.POSTSTATUSCODE = 1
    left join dbo.GIFTFEEADJUSTMENT ON v1.FINANCIALTRANSACTIONID = GIFTFEEADJUSTMENT.REVENUEID and GIFTFEEADJUSTMENT.POSTSTATUSCODE = 1
    option (recompile)    

end -- if @SUMMARIZEDATE = 0

else  -- by Fiscal Period

begin    
    if @REVENUEPOSTMETHOD = 0 -- Detail

    begin
        insert into @WORKTABLE (GLTRANSACTIONID ,ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID,ORGANIZATIONAMOUNT)
        select PD.GLTRANSACTIONID,PD.ACCOUNT, coalesce(adj.POSTDATE, f.POSTDATE, PD.POSTDATE),
            'R',case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255),PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from  @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
        left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on PD.FINANCIALTRANSACTIONID = e.ID
        left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
        left outer join dbo.ADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0 
            and PD.TRANSACTIONTYPECODE = 1 
            and PD.TABLENAMECODE = 1
            and (
                    (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                    or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0) --Pledge transaction, gift application

                    or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201) --Non-bookable pledges

                    or (d.REVENUETRANSACTIONTYPECODE  = 15 and d.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                )

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(case PD.IsReversal when 0 then '' else 'Reversal - ' end + PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
        where PD.TABLENAMECODE = 6
            and (
                    (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                    or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                )

        union all
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE), 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from  @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
        left outer join dbo.BENEFITADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 5
        option (recompile)
    end
    else
    if @REVENUEPOSTMETHOD = 1  -- Summary

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.Account, GLFISCALPERIOD.ENDDATE, 'R', case when V1.TRANSACTIONTYPECODE = 1 THEN 'C' else 'D' end, sum(V1.AMOUNT), V1.JOURNAL, @REVENUEREFERENCE, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.id
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and PD.TRANSACTIONTYPECODE = 1 
                --Firebird branch has GLTransaction.PostStatusCode = 1 here.  It's not in the detail section above and is implied by inclusion in @InRecords

                and (
                        (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5)
                    )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT    
            from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
            where PD.TABLENAMECODE = 6
                and (
                        (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                        or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                    )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0))

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 3
                and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0))
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.JOURNAL, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID
        option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT,
            GLFISCALPERIOD.ENDDATE, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT), V1.JOURNAL, @BENEFITSUMMARIZED, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            left outer join dbo.BENEFITADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
            where PD.IsReversal = 0
            and PD.TABLENAMECODE = 5
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end
    else if @REVENUEPOSTMETHOD = 2  -- Summary by application type

        begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH,PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, 'R', case when V1.TRANSACTIONTYPECODE = 1 then 'C' else 'D' end, sum(V1.AMOUNT), V1.JOURNAL, @REVENUEREFERENCE+' - '+V1.APPLICATION, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATION, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.id
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and PD.TRANSACTIONTYPECODE = 1 
                and (
                    (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                    or GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5
                )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, CREDIT.[TYPE] APPLICATION, PD.GLACCOUNTID, PD.ORGAMOUNT        
            from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
            left join dbo.CREDITITEM_EXT EXT on EXT.ID = DISCOUNT.ID
            left join dbo.CREDIT on EXT.CREDITID = CREDIT.ID or REFUND.CREDITID = CREDIT.ID
            where PD.TABLENAMECODE = 6
            and (
                    (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                    or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) , PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTTRANSACTION.TRANSACTIONTYPE, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            left join dbo.JOURNALENTRY_EXT ON PD.GLTRANSACTIONID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 4
                and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0))

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPE, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 3
                and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0)) 
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.JOURNAL, V1.APPLICATION, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID
        option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.Account,
            GLFISCALPERIOD.ENDDATE, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT), V1.JOURNAL, @BENEFITSUMMARIZED, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from  
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, d.APPLICATION, PD.GLACCOUNTID, PD.ORGAMOUNT
            from
            @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            left outer join dbo.BENEFITADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
            where PD.IsReversal = 0
            and PD.TABLENAMECODE = 5
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end
    else  -- Summary by deposit

        begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.Account, GLFISCALPERIOD.ENDDATE, 'R', case when V1.TRANSACTIONTYPECODE = 1 THEN 'C' else 'D' end, sum(V1.AMOUNT), V1.JOURNAL,
            case when (V1.DEPOSITID IS null or @REVENUEREFERENCE != 'Summarized Revenue') then @REVENUEREFERENCE else @DEPOSITSUMMARIZED + ' - ' + Cast(BAT.TRANSACTIONNUMBER as nvarchar(10)) + ' - ' + BA.ACCOUNTNAME end, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.id
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and PD.TRANSACTIONTYPECODE = 1 
                --Firebird branch has GLTransaction.PostStatusCode = 1 here.  It's not in the detail section above and is implied by inclusion in @InRecords

                and (
                        (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5)
                    )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BADP.DEPOSITID
            from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
            left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = PD.FINANCIALTRANSACTIONID
            where PD.TABLENAMECODE = 6
            and (
                    (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                    or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                )

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BANKACCOUNTTRANSACTION.ID [DEPOSITID]
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0))

            union --all

            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT, BANKACCOUNTTRANSACTION.ID [DEPOSITID]
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 3
                and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0))
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        left join dbo.BANKACCOUNTTRANSACTION BAT on V1.DEPOSITID = BAT.ID
        left join dbo.BANKACCOUNT BA on BAT.BANKACCOUNTID = BA.ID
        group by V1.DEPOSITID, V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.JOURNAL, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
        option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT,
            GLFISCALPERIOD.ENDDATE, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT), V1.JOURNAL, @BENEFITSUMMARIZED, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
            left outer join dbo.BENEFITADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
            where PD.IsReversal = 0
                and PD.TABLENAMECODE = 5
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end

    update @PD2 set ISUSED = 1
    from @PD2 tv1 inner join
    (
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
        where PD.ISREVERSAL = 0 
            and PD.TRANSACTIONTYPECODE = 1 
            and PD.TABLENAMECODE = 1
            and (
                    (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11,13,18,19))
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18)) --Membership installment transaction; gift, membership, addon application

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5 and @REVENUEPOSTMETHOD != 0)
                )

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 0))
            and @REVENUEPOSTMETHOD != 0

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 3
            and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 0))
            and @REVENUEPOSTMETHOD != 0

        union all
        select PD.GLTRANSACTIONID 
        from @PD2 as PD
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 5

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
        where PD.TABLENAMECODE = 6
            and (
                    (DISCOUNT.TYPECODE = 5 and PD.TRANSACTIONTYPECODE = 1)
                    or (REFUND.ID is not null and PD.TRANSACTIONTYPECODE = 0)
                )
    ) V1 on tv1.GLTransactionID = V1.GLTRANSACTIONID
    option (recompile)

    if @CASHPOSTMETHOD = 0      -- Detail

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(adj.POSTDATE, f.POSTDATE, PD.POSTDATE),
        'R',case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from  @PD2 as PD
        inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
        left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on PD.FINANCIALTRANSACTIONID = e.ID
        left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
        left outer join dbo.ADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 1
            and (
                    (
                        d.REVENUETRANSACTIONTYPECODE = 0 
                        and d.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,10,11,13,18,19,203) 
                        and PD.TRANSACTIONTYPECODE = 0
                        and (
                            d.REVENUESPLITTYPECODE <> 19 /* Unearned revenue */ 
                            or d.TRANSACTIONTYPECODE = 0 /* Debit */
                        )
                    )
                    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
                    or (
                        d.REVENUETRANSACTIONTYPECODE = 201 
                        and d.APPLICATIONCODE = 201 
                        and PD.TRANSACTIONTYPECODE = 0
                        and (d.REVENUESPLITTYPECODE <> 19 OR d.TRANSACTIONTYPECODE = 0)
                    )
                )

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(bat.POSTDATE, PD.POSTDATE), 'R', 'C', PD.AMOUNT, PD.JOURNAL, left(PD.COMMENT,255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on REFUND.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID
        left join dbo.BANKACCOUNTTRANSACTION as BAT on BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID = BAT.ID
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 6
            and PD.TRANSACTIONTYPECODE = 1
        option (recompile)
    end
    else
    if @CASHPOSTMETHOD = 1     -- Summary

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.Account, GLFISCALPERIOD.EndDate, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, 
        sum(V1.AMOUNT), V1.JOURNAL, @CASHREFERENCE, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and (
                        (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and 
                            GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11,13,18,19,203) and 
                            PD.TRANSACTIONTYPECODE = 0
                        )
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 200 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 200)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201 and PD.TRANSACTIONTYPECODE = 0)
                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 10 
                            and PD.TRANSACTIONTYPECODE = 0
                            and (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 or GLPAYMENTMETHODREVENUETYPEMAPPING.TRANSACTIONTYPECODE = 0)
                        )
                    )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) , PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 4
            and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 0))

            union 
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 3
            and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 0))

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BAT.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on REFUND.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION as BAT on BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID = BAT.ID
            where PD.ISREVERSAL = 0
                and PD.TABLENAMECODE = 6
                and PD.TRANSACTIONTYPECODE = 1
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end
    else   -- Summary by deposit

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.Account, GLFISCALPERIOD.ENDDATE, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, 
        sum(V1.AMOUNT), V1.JOURNAL,
            case when (V1.DEPOSITID IS null or @CASHREFERENCE != 'Summarized Cash') then @CASHREFERENCE else @DEPOSITSUMMARIZED + ' - ' + Cast(BAT.TRANSACTIONNUMBER as nvarchar(10)) + ' - ' + BA.ACCOUNTNAME end, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTDEPOSITPAYMENT.DEPOSITID, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.id
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0  
                and PD.TABLENAMECODE = 1
                and (
                        (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11,13,18,19,203) 
                            and PD.TRANSACTIONTYPECODE = 0
                        )
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 200 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 200)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201 and PD.TRANSACTIONTYPECODE = 0)
                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 10 
                            and PD.TRANSACTIONTYPECODE = 0
                            and (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 or GLPAYMENTMETHODREVENUETYPEMAPPING.TRANSACTIONTYPECODE = 0)
                        )
                    )

            union 
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, null, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 4
                and 
                ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 0))

            union 
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BANKACCOUNTDEPOSITCORRECTION.DEPOSITID, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 3
                and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 0))

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BAT.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, BAT.ID, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
            left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on REFUND.ID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION as BAT ON BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID = BAT.ID
            where PD.ISREVERSAL = 0
                and PD.TABLENAMECODE = 6
                and PD.TRANSACTIONTYPECODE = 1
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        left join dbo.BANKACCOUNTTRANSACTION BAT on V1.DEPOSITID = BAT.ID
        left join dbo.BANKACCOUNT BA on BAT.BANKACCOUNTID = BA.ID
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.JOURNAL, V1.DEPOSITID, V1.TRANSACTIONTYPECODE, V1.GLACCOUNTID, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
        option (recompile)
    end

    update @PD2 set ISUSED = 1
    from @PD2 tv1 inner join
    (
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 1 
            and (
                    (
                        GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,2,3,4,5,6,7,8,11,13,18,19,203) 
                        and PD.TRANSACTIONTYPECODE = 0
                    )
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 200 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 200)
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201 and PD.TRANSACTIONTYPECODE = 0)
                    or (
                        GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 10
                        and (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19 or GLPAYMENTMETHODREVENUETYPEMAPPING.TRANSACTIONTYPECODE = 0)
                    )
                )

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
        where PD.ISREVERSAL = 0 
        and PD.TABLENAMECODE = 4
        and ((BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (17, 32) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTTRANSACTION.TRANSACTIONTYPECODE in (16, 33) and PD.TRANSACTIONTYPECODE = 0))
        and @CASHPOSTMETHOD != 0

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        where PD.ISREVERSAL = 0 
        and PD.TABLENAMECODE = 3
        and ((BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (0, 2) and PD.TRANSACTIONTYPECODE = 1) or (BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPECODE in (1, 3) and PD.TRANSACTIONTYPECODE = 0))
        and @CASHPOSTMETHOD != 0

        union all
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        where PD.ISREVERSAL = 0
        and PD.TABLENAMECODE = 6
        and PD.TRANSACTIONTYPECODE = 1
    ) V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID
    option (recompile)

    if @RECEIVABLEPOSTMETHOD = 0   -- Detail

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(adj.POSTDATE, f.POSTDATE, PD.POSTDATE),
        'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from  @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as d on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = d.ID 
        left outer join dbo.BANKACCOUNTDEPOSITPAYMENT as e on PD.FINANCIALTRANSACTIONID = e.ID
        left outer join dbo.BANKACCOUNTTRANSACTION f on f.ID = e.DEPOSITID
        left outer join dbo.ADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 1
            and (
                    (d.REVENUETRANSACTIONTYPECODE = 1  and d.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 0)
                    or (d.REVENUETRANSACTIONTYPECODE = 15 and d.APPLICATIONCODE in (0,5,18) and PD.TRANSACTIONTYPECODE = 0) --Membership installment plan transaction; gift, membership, add-on application; debit

                    or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5,1,16,18) and PD.TRANSACTIONTYPECODE = 0)
                    or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10,203) and PD.TRANSACTIONTYPECODE = 1 and d.REVENUESPLITTYPECODE <> 19)
                    or (d.REVENUETRANSACTIONTYPECODE = 1 and d.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205)
                )

        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, PD.AMOUNT, PD.JOURNAL, left(PD.COMMENT, 255),PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD inner join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID and DISCOUNT.TYPECODE = 5
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 6
            and PD.TRANSACTIONTYPECODE = 0
        option (recompile)
    end
    else -- Summary

    begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT ,AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select '99999999-9999-9999-9999-999999999999', V1.ACCOUNT,
        GLFISCALPERIOD.ENDDATE,
        'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
        sum(V1.AMOUNT), V1.JOURNAL ,@RECEIVABLEREFERENCE, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from  
        (
            select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID 
            left join dbo.BANKACCOUNTDEPOSITPAYMENT on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
            left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
            left join dbo.ADJUSTMENT on PD.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 1
                and (
                        (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 0)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5 and PD.TRANSACTIONTYPECODE = 0)
                        or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18) and PD.TRANSACTIONTYPECODE = 0) --Membership installment plan transaction; gift, membership, add-on application; debit

                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (2,8,10,203) 
                            and PD.TRANSACTIONTYPECODE = 1
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19
                        )
                        or (
                            GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 
                            and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0 
                            and PD.TRANSACTIONTYPECODE = 1 
                            and PAYMENTMETHODCODE = 205
                        )
                    )

            union
            select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD inner join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID and DISCOUNT.TYPECODE = 5
            where PD.ISREVERSAL = 0
                and PD.TABLENAMECODE = 6
                and PD.TRANSACTIONTYPECODE = 0
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end

    update @PD2 set ISUSED = 1 
    from @PD2 tv1 inner join
    (
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on PD.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID 
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 1
            and (
                    (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1  and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 0)
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 15 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,5,18) and PD.TRANSACTIONTYPECODE = 0) --Membership installment plan transaction; gift, membership, add-on application; debit

                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5 and PD.TRANSACTIONTYPECODE = 0)
                    or (
                        GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,2,8,203) 
                        and PD.TRANSACTIONTYPECODE = 1
                        and GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUESPLITTYPECODE <> 19
                    )
                    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 1 and PAYMENTMETHODCODE = 205)
                )

        union
        select PD.GLTRANSACTIONID
        from @PD2 as PD inner join dbo.FINANCIALTRANSACTIONLINEITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID and DISCOUNT.TYPECODE = 5
        where PD.ISREVERSAL = 0
            and PD.TABLENAMECODE = 6
            and PD.TRANSACTIONTYPECODE = 0
    ) V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID
    option (recompile)

    --Revaluation Gain/Loss

    if @REVALUATIONGAINLOSSPOSTMETHOD = 0   -- Detail

    begin
        insert into @WORKTABLE 
            (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select     PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE), 'R', case PD.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, 
            PD.AMOUNT, JOURNAL, left(PD.COMMENT, 255), 
            PD.BATCHID, PD.PROJECT, PD.GLACCOUNTID, @BASECURRENCYID, PD.ORGAMOUNT
        from @PD2 as PD left outer join dbo.UNREALIZEDGAINLOSSADJUSTMENT adj on PD.FINANCIALTRANSACTIONID = adj.REVENUEID and adj.POSTSTATUSCODE = 1
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 14
        option (recompile)
    end
    else   -- Revaluation Gain/Loss Summary

    begin
        insert into @WORKTABLE 
            (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
        select     '99999999-9999-9999-9999-999999999999', V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, 'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end,
            sum(V1.AMOUNT), V1.JOURNAL, @REVALUATIONGAINLOSSREFERENCE, null, '', V1.GLACCOUNTID, @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from (
            select PD.GLTRANSACTIONID,    PD.ACCOUNT, coalesce(UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, 
                PD.AMOUNT, PD.JOURNAL, PD.GLACCOUNTID, PD.ORGAMOUNT
            from @PD2 as PD left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on PD.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1
            where PD.ISREVERSAL = 0 
                and PD.TABLENAMECODE = 14
        ) V1 inner join dbo.GLFISCALPERIOD on cast(V1.PostDate as date) between GLFISCALPERIOD.STARTDATE and GLFISCALPERIOD.ENDDATE
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.TRANSACTIONTYPECODE, V1.JOURNAL, V1.GLACCOUNTID
        option (recompile)
    end

    update @PD2
    set ISUSED = 1 
    from @PD2 tv1 
    inner join
    (
        select PD.GLTRANSACTIONID
        from @PD2 as PD 
        where PD.ISREVERSAL = 0 
            and PD.TABLENAMECODE = 14
    ) V1 on tv1.GLTRANSACTIONID = V1.GLTRANSACTIONID
    option (recompile)

    insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT ,JOURNAL, REFERENCE, BATCH, PROJECT, GLACCOUNTID, BASECURRENCYID, ORGANIZATIONAMOUNT)
    select V1.GLTRANSACTIONID, V1.ACCOUNT, cast(coalesce(ADJUSTMENT.POSTDATE, BANKACCOUNTTRANSACTION.POSTDATE, BAT2.POSTDATE, UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE, V1.POSTDATE) as date),
        'R', case V1.TRANSACTIONTYPECODE when 0 then 'D' else 'C' end, V1.AMOUNT, V1.JOURNAL,
        left(case when V1.ISREVERSAL = 0 then V1.COMMENT else 'Reversal' + ' - ' + V1.COMMENT end, 255), 
        V1.BATCHID, V1.PROJECT, V1.GLACCOUNTID, @BASECURRENCYID, V1.ORGAMOUNT
    from
    (
        select distinct GLTRANSACTIONID, ACCOUNT, TRANSACTIONTYPECODE, AMOUNT, JOURNAL, ISREVERSAL, COMMENT, BATCHID, PROJECT, ORGAMOUNT, FINANCIALTRANSACTIONID, POSTDATE, GLACCOUNTID from @PD2 where ISUSED = 0
    ) V1 
    left join dbo.JOURNALENTRY_EXT as JE1 on V1.GLTRANSACTIONID = JE1.ID and JE1.TABLENAMECODE = 1
    left join dbo.BANKACCOUNTDEPOSITPAYMENT on V1.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
    left join dbo.FINANCIALTRANSACTION as BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTTRANSACTION.ID
    left join dbo.ADJUSTMENT on V1.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
    left join dbo.FINANCIALTRANSACTION as BAT2 on V1.FINANCIALTRANSACTIONID = BAT2.ID  and BAT2.POSTSTATUSCODE = 1
    left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on V1.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID AND UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1
    option (recompile)
end

return
end