UFN_POSTTOGLPROCESS_SUMMARIZE

CREATE function [dbo].[UFN_POSTTOGLPROCESS_SUMMARIZE] (@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),
    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 = 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), 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, ORGAMOUNT, 
    GLPAYMENTMETHODREVENUETYPEMAPPINGID, BATCHID, COMMENT, FINANCIALTRANSACTIONID, TABLENAMECODE, ISUSED, CREDITPAYMENTID, CREDITITEMID)
select InRecs.ID, GLACCOUNT.ACCOUNTNUMBER, InRecs.Flag, JOURNALENTRY.POSTDATE, JOURNALENTRY.TRANSACTIONTYPECODE, JOURNALENTRY.TRANSACTIONAMOUNT, JOURNALENTRY_EXT.JOURNAL,
    JOURNALENTRY_EXT.PROJECT, 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,BASECURRENCYID,ORGANIZATIONAMOUNT)
    select PD.GLTRANSACTIONID, PD.ACCOUNT, coalesce(ADJUSTMENT.POSTDATE, JE2.POSTDATE, BENEFITADJUSTMENT.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, @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))
                or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
                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, @BASECURRENCYID, PD.ORGAMOUNT
    from @PD2 PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
    left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
    where PD.TABLENAMECODE = 6
    and    ((DISCOUNT.ID is not null 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,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, '', @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.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))
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5))
        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.ORGAMOUNT
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
        where PD.TABLENAMECODE = 6
        and ((DISCOUNT.ID is not null 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.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.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
            option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,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, '', @BASECURRENCYID,sum(V1.ORGAMOUNT)
        from  
        (select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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
            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,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, '',@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.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))
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5))
        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, CREDIT.[TYPE] APPLICATION, PD.ORGAMOUNT            
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
    left join dbo.CREDIT on DISCOUNT.CREDITID = CREDIT.ID or REFUND.CREDITID = CREDIT.ID
        where PD.TABLENAMECODE = 6
        and ((DISCOUNT.ID is not null 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, GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATION, 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

        left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.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 not (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 10 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.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
            option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT,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,'',@BASECURRENCYID, sum(V1.ORGAMOUNT)
        from  
        (select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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                                                    
            option (recompile)
        end
    else  -- Summary by deposit

        begin
        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,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, '', @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.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.TRANSACTIONTYPECODE = 1 
        and PD.TABLENAMECODE = 1
        and ((GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,1,3,4,5,6,7,11))
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5))
        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.ORGAMOUNT, BADP.DEPOSITID
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
        left join BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = PD.FINANCIALTRANSACTIONID
        where PD.TABLENAMECODE = 6
        and ((DISCOUNT.ID is not null 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.ORGAMOUNT, BADP.DEPOSITID
        from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
        left join BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = PD.FINANCIALTRANSACTIONID
        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.ORGAMOUNT, BANKACCOUNTDEPOSITCORRECTION.DEPOSITID 
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = BANKACCOUNTDEPOSIT.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, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
            option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID,ACCOUNTSTRING,POSTDATE,TRANTYPE,DEBITCREDIT,AMOUNT,JOURNAL,REFERENCE,BATCH,PROJECT,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, '', @BASECURRENCYID,sum(V1.ORGAMOUNT)
        from  
        (select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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
            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))
    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
    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.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
where PD.TABLENAMECODE = 6
and ((DISCOUNT.ID is not null 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, 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), BATCHID, PD.PROJECT, @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) and PD.TRANSACTIONTYPECODE = 0)
    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
    or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 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), BATCHID, PD.PROJECT, @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), BATCHID, PD.PROJECT, @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), BATCHID, PD.PROJECT, @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                 

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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) 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))
        union 
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) , PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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.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.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
            option (recompile)
    else  -- Summary by deposit

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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) and PD.TRANSACTIONTYPECODE = 0)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 200 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 200)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201 and PD.TRANSACTIONTYPECODE = 0))
        union 
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, null, 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.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.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, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
        option (recompile)

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) 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))
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, 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), BATCHID, PD.PROJECT, @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 = 4 and d.APPLICATIONCODE = 0 and PD.TRANSACTIONTYPECODE = 0 )

    or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5,1,16) and PD.TRANSACTIONTYPECODE = 0)
    or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and PD.TRANSACTIONTYPECODE = 1)
    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), BATCHID, PD.PROJECT, @BASECURRENCYID, PD.ORGAMOUNT
    from @PD2 as PD inner join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
    where PD.ISREVERSAL = 0
    and PD.TABLENAMECODE = 6
    and PD.TRANSACTIONTYPECODE = 0
      option (recompile)
else        -- Summary

    insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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,@RECEIVABLEREFERENCE,null,'', @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.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 = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (2,8,10) and PD.TRANSACTIONTYPECODE = 1)
    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.ORGAMOUNT
    from @PD2 as PD inner join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
    where PD.ISREVERSAL = 0
    and PD.TABLENAMECODE = 6
    and PD.TRANSACTIONTYPECODE = 0
    ) V1
    group by V1.ACCOUNT, V1.postdate, V1.TRANSACTIONTYPECODE, V1.JOURNAL
  option (recompile)

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 = 5 AND PD.TRANSACTIONTYPECODE = 0)
or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0,2,8) and PD.TRANSACTIONTYPECODE = 1)
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.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
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    

    insert into @WORKTABLE 
    (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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), BATCHID, PD.PROJECT, @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)

else -- Revaluation Gain/Loss Summary

    insert into @WORKTABLE 
    (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @BASECURRENCYID, sum(V1.ORGAMOUNT)
    from  (
            select PD.GLTRANSACTIONID, PD.ACCOUNT,     isnull(UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE, PD.POSTDATE) as PostDate, 
                PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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
  option (recompile)

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, BASECURRENCYID, ORGANIZATIONAMOUNT)
select V1.GLTRANSACTIONID, V1.ACCOUNT, COALESCE(ADJUSTMENT.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, @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 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
    left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on V1.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1
option (recompile)    

end -- if @SUMMARIZEDATE = 0


else  -- by Fiscal Period

begin    
if @REVENUEPOSTMETHOD = 0      -- Detail    

    insert into @WORKTABLE (GLTRANSACTIONID ,ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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),BATCHID, PD.PROJECT, @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))
    or (d.REVENUETRANSACTIONTYPECODE  = 1 and d.APPLICATIONCODE = 0)
    or (d.REVENUETRANSACTIONTYPECODE  = 201 and d.APPLICATIONCODE = 201))
    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), BATCHID, PD.PROJECT, @BASECURRENCYID, PD.ORGAMOUNT
    from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
    left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
    where PD.TABLENAMECODE = 6
    and ((DISCOUNT.ID is not null 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), BATCHID, PD.PROJECT, @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)

else        
    if @REVENUEPOSTMETHOD = 1  -- Summary

        begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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))
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5))    
        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.ORGAMOUNT    
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID        
        where PD.TABLENAMECODE = 6
        and ((DISCOUNT.ID is not null 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.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.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
            option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from  
        (select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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
            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, 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, '', @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.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))
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
            or GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5
            )
        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, CREDIT.[TYPE] APPLICATION, PD.ORGAMOUNT        
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
    left join dbo.CREDIT on DISCOUNT.CREDITID = CREDIT.ID or REFUND.CREDITID = CREDIT.ID
        where PD.TABLENAMECODE = 6
        and ((DISCOUNT.ID is not null 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, GLPAYMENTMETHODREVENUETYPEMAPPING.Application, 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
        left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on JOURNALENTRY_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.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, BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPE, PD.ORGAMOUNT
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        left join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = 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 = 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
            option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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                                                    
    option (recompile)
        end
    else -- Summary by deposit

        begin
        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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))
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE  = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
            or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 5))    
        union
        select PD.GLTRANSACTIONID, PD.ACCOUNT, PD.POSTDATE, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, PD.ORGAMOUNT, BADP.DEPOSITID
        from @PD2 as PD left join dbo.CREDITPAYMENT as REFUND on PD.CREDITPAYMENTID = REFUND.ID
        left join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID        
        left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = PD.FINANCIALTRANSACTIONID
        where PD.TABLENAMECODE = 6
        and ((DISCOUNT.ID is not null 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.ORGAMOUNT, BADP.DEPOSITID
        from @PD2 as PD inner join dbo.BANKACCOUNTTRANSACTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTTRANSACTION.ID
        left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = PD.FINANCIALTRANSACTIONID
        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.ORGAMOUNT, BANKACCOUNTDEPOSITCORRECTION.DEPOSITID
        from @PD2 as PD inner join dbo.BANKACCOUNTDEPOSITCORRECTION on PD.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITCORRECTION.ID
        inner join BANKACCOUNTDEPOSIT BAD on BAD.ID = BANKACCOUNTDEPOSITCORRECTION.DEPOSITID
        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 BAT.ID = V1.DEPOSITID
        left join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID
        group by V1.DEPOSITID, V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.JOURNAL, V1.TRANSACTIONTYPECODE, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
            option (recompile)

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @BASECURRENCYID, sum(V1.ORGAMOUNT)
        from  
        (select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(adj.POSTDATE, PD.POSTDATE) as Postdate, PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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
            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))
    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 1 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 0)
    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 201 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 201)
    or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE = 10 and @REVENUEPOSTMETHOD != 0)
    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.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
where PD.TABLENAMECODE = 6
and ((DISCOUNT.ID is not null 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, 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), BATCHID, PD.PROJECT, @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) and PD.TRANSACTIONTYPECODE = 0)
    or (d.REVENUETRANSACTIONTYPECODE = 200 and d.APPLICATIONCODE = 200)
    or (d.REVENUETRANSACTIONTYPECODE = 201 and d.APPLICATIONCODE = 201 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), BATCHID, PD.PROJECT, @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)
else
    if @CASHPOSTMETHOD = 1     -- Summary            


        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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) 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)
            )
        union 
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE) , PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, 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.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.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
            option (recompile)

    else   -- Summary by deposit

        insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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) 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)
            )
        union 
        select PD.GLTRANSACTIONID, PD.ACCOUNT, isnull(BANKACCOUNTTRANSACTION.POSTDATE, PD.POSTDATE), PD.TRANSACTIONTYPECODE, PD.AMOUNT, PD.JOURNAL, null, 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.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.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 BAT.ID = V1.DEPOSITID
        left join dbo.BANKACCOUNT BA on BAT.BANKACCOUNTID = BA.ID
        group by V1.ACCOUNT, GLFISCALPERIOD.ENDDATE, V1.JOURNAL, V1.DEPOSITID, V1.TRANSACTIONTYPECODE, BAT.TRANSACTIONNUMBER, BA.ACCOUNTNAME
    option (recompile)

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) 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)
    )
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        

    insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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), BATCHID, PD.PROJECT, @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 = 4 and d.APPLICATIONCODE = 0  and PD.TRANSACTIONTYPECODE = 0 )

    or (d.REVENUETRANSACTIONTYPECODE = 5 and d.APPLICATIONCODE in (0,9,5,1,16) and PD.TRANSACTIONTYPECODE = 0)
    or (d.REVENUETRANSACTIONTYPECODE = 0 and d.APPLICATIONCODE in (2,8,10) and PD.TRANSACTIONTYPECODE = 1)
    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),BATCHID, PD.PROJECT, @BASECURRENCYID, PD.ORGAMOUNT
    from @PD2 as PD inner join dbo.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
    where PD.ISREVERSAL = 0
    and PD.TABLENAMECODE = 6
    and PD.TRANSACTIONTYPECODE = 0
  option (recompile)
else   -- Summary


    insert into @WORKTABLE (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT ,AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @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.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 = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (2,8,10) and PD.TRANSACTIONTYPECODE = 1)
    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.ORGAMOUNT
    from @PD2 as PD inner join dbo.CREDITITEM as DISCOUNT ON PD.CREDITITEMID = DISCOUNT.ID
    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
  option (recompile)

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 = 5 and PD.TRANSACTIONTYPECODE = 0)
or (GLPAYMENTMETHODREVENUETYPEMAPPING.REVENUETRANSACTIONTYPECODE = 0 and GLPAYMENTMETHODREVENUETYPEMAPPING.APPLICATIONCODE in (0, 2,8) and PD.TRANSACTIONTYPECODE = 1)
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.CREDITITEM as DISCOUNT on PD.CREDITITEMID = DISCOUNT.ID
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        

    insert into @WORKTABLE 
    (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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), 
        BATCHID, PD.PROJECT, @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)

else   -- Revaluation Gain/Loss Summary


    insert into @WORKTABLE 
    (GLTRANSACTIONID, ACCOUNTSTRING, POSTDATE, TRANTYPE, DEBITCREDIT, AMOUNT, JOURNAL, REFERENCE, BATCH, PROJECT, 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, '', @BASECURRENCYID, sum(V1.ORGAMOUNT)
    from      (select PD.GLTRANSACTIONID,    PD.ACCOUNT, coalesce(UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE, PD.POSTDATE) as PostDate, PD.TRANSACTIONTYPECODE, 
                PD.AMOUNT, PD.JOURNAL, 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
      option (recompile)

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, 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, @BASECURRENCYID, V1.ORGAMOUNT
from
(select distinct GLTRANSACTIONID, ACCOUNT, TRANSACTIONTYPECODE, AMOUNT, JOURNAL, ISREVERSAL, COMMENT, BATCHID, PROJECT, ORGAMOUNT, FINANCIALTRANSACTIONID, POSTDATE 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
left join dbo.UNREALIZEDGAINLOSSADJUSTMENT on V1.FINANCIALTRANSACTIONID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID AND UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1
option (recompile)



end

return
end