UFN_POSTTOGLPROCESS_GETUNPOSTEDGLTRANSACTIONS_2

CREATE function dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDGLTRANSACTIONS_2(@POSTTOGLPROCESSID uniqueidentifier)
returns table
as
return
(
    select distinct case JOURNALENTRY_EXT.TABLENAMECODE 
        when 0 then coalesce(JOURNALENTRY_EXT.LOGICALREVENUEID, FT.PARENTID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
        when 1 then FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        when 2 then JOURNALENTRY_EXT.REVENUEPURCHASEID
        when 5 then JOURNALENTRY_EXT.LOGICALREVENUEID
        when 6 then isnull(JOURNALENTRY_EXT.CREDITPAYMENTID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
        when 7 then JOURNALENTRY_EXT.LOGICALREVENUEID
        when 8 then JOURNALENTRY_EXT.LOGICALREVENUEID
        when 9 then JOURNALENTRY_EXT.LOGICALREVENUEID
        when 10 then coalesce(JOURNALENTRY_EXT.LOGICALREVENUEID, FT.PARENTID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
        when 11 then coalesce(JOURNALENTRY_EXT.LOGICALREVENUEID, FT.PARENTID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
        when 12 then coalesce(JOURNALENTRY_EXT.LOGICALREVENUEID, FT.PARENTID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
        when 13 then coalesce(JOURNALENTRY_EXT.LOGICALREVENUEID, FT.PARENTID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
        else FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID end as REVENUEID
        ,JOURNALENTRY.ID as GLTRANSACTIONID
        ,JOURNALENTRY.TRANSACTIONTYPE
        ,GLACCOUNT.ACCOUNTNUMBER as ACCOUNT
        ,JOURNALENTRY_EXT.PROJECT
        ,JOURNALENTRY.TRANSACTIONAMOUNT as AMOUNT
        ,JOURNALENTRY.COMMENT as REFERENCE
        ,case FINANCIALTRANSACTIONLINEITEM.TYPECODE when 1 then 1 else 0 end as ISREVERSAL
        ,case when FTLI2.ID is not null then 1 else 0 end as ISADJUSTED
    from dbo.FINANCIALTRANSACTIONLINEITEM
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    inner join dbo.JOURNALENTRY on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
    inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
    inner join dbo.POSTTOGLPROCESS on GLACCOUNT.PDACCOUNTSYSTEMID = POSTTOGLPROCESS.PDACCOUNTSYSTEMID
    left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2
        on FTLI2.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
        and FTLI2.TYPECODE = 1
    left outer join dbo.CREDITPAYMENT as CP on JOURNALENTRY_EXT.CREDITPAYMENTID = CP.ID -- CREDITPAYMENTMETHOD

    left outer join dbo.RECONCILIATION as RECON on CP.RECONCILIATIONID = RECON.ID     --CREDITPAYMENTMETHOD

    left outer join dbo.CREDITITEM on JOURNALENTRY_EXT.CREDITITEMID = CREDITITEM.ID
    left outer  join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID        
    where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
        and POSTTOGLPROCESS.ID = @POSTTOGLPROCESSID     
        and (
            JOURNALENTRY_EXT.TABLENAMECODE in (0, 1, 2, 5, 7, 8, 9, 10, 11, 12, 13, 14)
            or ( 
                -- This is the only payment method that should be posted along with REVENUE post.

                JOURNALENTRY_EXT.TABLENAMECODE = 6 -- CREDITGLDISTRIBUTION

                and (RECON.STATUSCODE = 3 -- Deposited

                    or (CP.PAYMENTMETHODCODE = 10 -- Other Payment Method

                        and CP.OTHERPAYMENTMETHODCODEID in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD))
                    or (FT.TYPECODE=23 and FT.TRANSACTIONAMOUNT=0)) -- Refunds with amount 0

            )
            or ( 
                -- discounts

                JOURNALENTRY_EXT.TABLENAMECODE = 6 -- CREDITGLDISTRIBUTION

                and CREDIT.ID is not null and CREDITITEM.ID is not null
                and CREDIT.TYPECODE != 0  and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 99
            )        
        )
)