UFN_POSTTOGLPROCESS_GETUNPOSTEDDEPOSITTRANSACTIONS_2

CREATE function dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDDEPOSITTRANSACTIONS_2(@POSTTOGLPROCESSID uniqueidentifier)
            returns table
            as
            return
      (
    select Distinct BANKACCOUNTDEPOSITPAYMENT.DEPOSITID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as RECORDID, JOURNALENTRY.ID as GLTRANSACTIONID, 
    JOURNALENTRY.TRANSACTIONTYPE, GLACCOUNT.ACCOUNTNUMBER as ACCOUNT, JOURNALENTRY_EXT.PROJECT, JOURNALENTRY.TRANSACTIONAMOUNT [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.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
    inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUEPAYMENTMETHOD.REVENUEID
    inner join dbo.BANKACCOUNTDEPOSITPAYMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
    left join dbo.FINANCIALTRANSACTION on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.POSTSTATUSCODE = 1
    left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2
        on FTLI2.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
        and FTLI2.TYPECODE = 1
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
    and POSTTOGLPROCESS.ID = @POSTTOGLPROCESSID
    and JOURNALENTRY_EXT.TABLENAMECODE in (0, 1, 11, 10, 12, 7, 5, 2, 8)
    and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0, 1, 2, 10)
    and BANKACCOUNTDEPOSITPAYMENT.DEPOSITID is not null

    union   

    select
     Distinct FINANCIALTRANSACTION.PARENTID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as RECORDID, JOURNALENTRY.ID as GLTRANSACTIONID, 
    JOURNALENTRY.TRANSACTIONTYPE, GLACCOUNT.ACCOUNTNUMBER as ACCOUNT, JOURNALENTRY_EXT.PROJECT, JOURNALENTRY.TRANSACTIONAMOUNT [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.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
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.POSTSTATUSCODE = 1 and FINANCIALTRANSACTION.TYPECODE in (24, 25)
    left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2
        on FTLI2.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
        and FTLI2.TYPECODE = 1
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
    and POSTTOGLPROCESS.ID = @POSTTOGLPROCESSID
    and JOURNALENTRY_EXT.TABLENAMECODE in (3)      

    union all

    select Distinct BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID, FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as RECORDID, JOURNALENTRY.ID as GLTRANSACTIONID, 
    JOURNALENTRY.TRANSACTIONTYPE, GLACCOUNT.ACCOUNTNUMBER as ACCOUNT, JOURNALENTRY_EXT.PROJECT, JOURNALENTRY.TRANSACTIONAMOUNT [AMOUNT], JOURNALENTRY.COMMENT as REFERENCE, 
    case FINANCIALTRANSACTIONLINEITEM.TYPECODE when 1 then 1 else 0 end as ISREVERSAL, 
    0 as ISADJUSTED
from dbo.FINANCIALTRANSACTIONLINEITEM
    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
    inner join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on JOURNALENTRY_EXT.CREDITPAYMENTID = BANKACCOUNTDEPOSITCREDITPAYMENT.ID
where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
    and POSTTOGLPROCESS.ID = @POSTTOGLPROCESSID
    and JOURNALENTRY_EXT.TABLENAMECODE = 6
    and BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID is not null

    union all
    -- Refund for discounts

    select distinct BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID, REFUND.CREDITID RECORDID, 
    JE.ID, JE.TRANSACTIONTYPE, GLACCOUNT.ACCOUNTNUMBER, JEX.PROJECT, JE.TRANSACTIONAMOUNT [AMOUNT], JE.COMMENT as REFERENCE, 0 as ISREVERSAL, 0 as ISADJUSTED
    from dbo.FINANCIALTRANSACTIONLINEITEM FL
    inner join dbo.JOURNALENTRY JE on FL.ID = JE.FINANCIALTRANSACTIONLINEITEMID
    inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
    inner join dbo.GLACCOUNT on JE.GLACCOUNTID = GLACCOUNT.ID    
    inner join dbo.CREDITITEM on JEX.DISCOUNTCREDITITEMID = CREDITITEM.ID
    inner join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID
    inner join dbo.CREDITITEM [REFUNDITEM] on JEX.CREDITITEMID = REFUNDITEM.ID
    inner join dbo.CREDITPAYMENT [REFUND] on REFUND.CREDITID = REFUNDITEM.CREDITID
    inner join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT on BANKACCOUNTDEPOSITCREDITPAYMENT.ID = REFUND.ID
    inner join dbo.POSTTOGLPROCESS on GLACCOUNT.PDACCOUNTSYSTEMID = POSTTOGLPROCESS.PDACCOUNTSYSTEMID
    left join dbo.ADJUSTMENT on FL.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
    where CREDIT.TYPECODE <> 0 and FL.POSTSTATUSCODE = 1 and BANKACCOUNTDEPOSITCREDITPAYMENT.DEPOSITID is not null
        and JEX.TABLENAMECODE = 6 and POSTTOGLPROCESS.ID = @POSTTOGLPROCESSID        

      )