UFN_POSTTOGLPROCESS_GETUNPOSTEDGLTRANSACTIONS_2_BULK

CREATE function dbo.UFN_POSTTOGLPROCESS_GETUNPOSTEDGLTRANSACTIONS_2_BULK ()
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
      ,FINANCIALTRANSACTIONLINEITEM.ID FINANCIALTRANSACTIONLINEITEMID
      ,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
      ,GLACCOUNT.PDACCOUNTSYSTEMID
    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
    left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2 on FINANCIALTRANSACTIONLINEITEM.ID = FTLI2.REVERSEDLINEITEMID
    left join dbo.CREDITPAYMENT as CP on JOURNALENTRY_EXT.CREDITPAYMENTID = CP.ID -- CREDITPAYMENTMETHOD

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

    left join dbo.CREDITITEM on JOURNALENTRY_EXT.CREDITITEMID = CREDITITEM.ID
    left join dbo.CREDIT on CREDITITEM.CREDITID = CREDIT.ID
    where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
      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

          and CP.PAYMENTMETHODCODE = 10 -- Other Payment Method

          and CP.OTHERPAYMENTMETHODCODEID in (
            select ID
            from dbo.NONDEPOSITABLEPAYMENTMETHOD
            )
          )
        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
          )
        )
    )