UFN_SALESDEPOSITPROCESS_GETACCOUNTSYSTEMTRANSACTIONS

CREATE function dbo.UFN_SALESDEPOSITPROCESS_GETACCOUNTSYSTEMTRANSACTIONS
(
    @SALESDEPOSITPROCESSID uniqueidentifier
)
RETURNS TABLE AS
RETURN (
    with NOTPOSTEDTRANSACTIONS as (
        select FT.ID, FT.TRANSACTIONCURRENCYID, FT.PDACCOUNTSYSTEMID, FT.DATE, FT.TRANSACTIONAMOUNT
        from dbo.ADJUSTMENT
        inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = ADJUSTMENT.REVENUEID
        where
            ADJUSTMENT.POSTSTATUSCODE = 1  -- Not posted

            and FT.TYPECODE = 0  -- Payment

            and FT.DELETEDON is null

        union
        select ID, TRANSACTIONCURRENCYID, PDACCOUNTSYSTEMID, DATE, TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTION
        where
            TYPECODE = 0  -- Payment

            and DELETEDON is null
            and POSTSTATUSCODE <> 2  -- Posted

    )
    select
        PAYMENT.ID,
        SALESORDER.TRANSACTIONDATE,
        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
        coalesce(OTHERPAYMENTMETHODCODE.DESCRIPTION, CREDITTYPECODE.DESCRIPTION, REVENUEPAYMENTMETHOD.PAYMENTMETHOD) as PAYMENTMETHOD,
        SALESORDER.SALESMETHODTYPECODE as SALESMETHODCODE,
        SALESORDER.SALESMETHODTYPE as SALESMETHOD,
        CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID,
        OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID,
        PAYMENT.AMOUNT,
        0 as ISREFUND,
        FT.TRANSACTIONCURRENCYID
    from
        NOTPOSTEDTRANSACTIONS as FT
    inner join
        dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FT.ID
    inner join (
        select ID, AMOUNT, PAYMENTID, SALESORDERID, RECONCILIATIONID, DONOTRECONCILE from dbo.SALESORDERPAYMENT
        union all
        select ID, AMOUNT, PAYMENTID, RESERVATIONID as SALESORDERID, RECONCILIATIONID, 1 as DONOTRECONCILE from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
    ) as PAYMENT on PAYMENT.PAYMENTID = FT.ID
    inner join
        dbo.SALESORDER on SALESORDER.ID = PAYMENT.SALESORDERID
    left outer join
        dbo.RECONCILIATION on RECONCILIATION.ID = PAYMENT.RECONCILIATIONID
    left outer join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on CREDITCARDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    left outer join
        dbo.CREDITTYPECODE on CREDITTYPECODE.ID = CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID
    left outer join
        dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    left outer join
        dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID
    where
        (
            RECONCILIATION.STATUSCODE in (2, 3)  -- Approved, Deposited

            or (RECONCILIATION.ID is null and PAYMENT.DONOTRECONCILE = 1 and SALESORDER.STATUSCODE = 1 /* Complete */)
        )
        and (OTHERPAYMENTMETHODDETAIL.ID is null or OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD))
        and PAYMENT.PAYMENTID not in (select ID from dbo.BANKACCOUNTDEPOSITPAYMENT with (nolock) where DEPOSITID is not null)
        and (@SALESDEPOSITPROCESSID is null or FT.PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMID from dbo.SALESDEPOSITPROCESS where ID = @SALESDEPOSITPROCESSID))

    union all
    select 
        CP.ID
        ,cast(FT.DATE as datetime)
        ,CP.PAYMENTMETHODCODE
        ,COALESCE(O.[DESCRIPTION], CC.[DESCRIPTION], CP.PAYMENTMETHOD) as PAYMENTMETHOD
        ,isnull(SO.SALESMETHODTYPECODE, 4) as SALESMETHODTYPECODE
        ,COALESCE(SO.SALESMETHODTYPE, 'Back Office Revenue') as SALESMETHODTYPE
        ,case CP.PAYMENTMETHODCODE
      when 2 then
        CCD.CREDITTYPECODEID
      else
        null
    end as CREDITTYPECODEID
        ,CP.OTHERPAYMENTMETHODCODEID
        ,CP.AMOUNT
        ,1 ISREFUND
        ,FT.TRANSACTIONCURRENCYID
    from dbo.CREDITPAYMENT CP
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CP.CREDITID
    inner join dbo.CREDIT_EXT CX on CX.ID = FT.ID
    left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT BADCP on BADCP.ID = CP.ID
    left join dbo.RECONCILIATION R on R.ID = CP.RECONCILIATIONID
    outer apply (
        select top 1 ID
        from (
            select REVENUEPAYMENTMETHOD.ID from dbo.REVENUEPAYMENTMETHOD where REVENUEPAYMENTMETHOD.REVENUEID = CP.REVENUEID
            union
            select REVENUEPAYMENTMETHOD.ID
            from dbo.REVENUEPAYMENTMETHOD
            inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = REVENUEPAYMENTMETHOD.REVENUEID
            where LI.ID = CP.REVENUESPLITID
        ) as REVENUEPAYMENTMETHOD
    ) as REVENUEPAYMENTMETHOD
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL CCD on CCD.ID = REVENUEPAYMENTMETHOD.ID
    left join dbo.CREDITTYPECODE CC on CC.ID = CCD.CREDITTYPECODEID and PAYMENTMETHODCODE = 2
    left join dbo.OTHERPAYMENTMETHODCODE O on O.ID = CP.OTHERPAYMENTMETHODCODEID and PAYMENTMETHODCODE = 10
    left join dbo.SALESORDER SO on SO.ID = CX.SALESORDERID
    where
        (
            R.STATUSCODE in (2,3)  -- Approved, Deposited

            or SO.ID is null
        )
        and (CP.REFUNDPROCESSED = 1)
        and (FT.POSTSTATUSCODE <> 2)  -- Posted

        and (CP.OTHERPAYMENTMETHODCODEID is null or CP.OTHERPAYMENTMETHODCODEID not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD))
        and (BADCP.DEPOSITID is null)
        and (@SALESDEPOSITPROCESSID is null or FT.PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMID from dbo.SALESDEPOSITPROCESS where ID = @SALESDEPOSITPROCESSID))

    union all
    select distinct
        FT.ID,
        FT.DATE,
        PM.PAYMENTMETHODCODE,
        coalesce(O.[DESCRIPTION], CC.[DESCRIPTION], PM.PAYMENTMETHOD) as PAYMENTMETHOD,
        case RSX.APPLICATIONCODE when 11 then 5 else 4 end as SALESMETHODCODE,
        case RSX.APPLICATIONCODE 
            when 11 then 'Treasury Miscellaneous Payments' 
            else 'Back Office Revenue' end as SALESMETHOD,
        CCD.CREDITTYPECODEID,
        OD.OTHERPAYMENTMETHODCODEID,
        FT.TRANSACTIONAMOUNT,
        0 as ISREFUND
        ,FT.TRANSACTIONCURRENCYID
    from NOTPOSTEDTRANSACTIONS as FT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join dbo.REVENUEPAYMENTMETHOD PM on PM.REVENUEID = FT.ID
    inner join dbo.REVENUESPLIT_EXT RSX on RSX.ID = LI.ID
    left join dbo.BANKACCOUNTDEPOSITPAYMENT on FT.ID = BANKACCOUNTDEPOSITPAYMENT.ID
    left join dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = FT.ID
    left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = FT.ID
    left join dbo.ADJUSTMENT on ADJUSTMENT.REVENUEID = REVENUEPOSTED.ID
    left join dbo.OTHERPAYMENTMETHODDETAIL OD on OD.ID = PM.ID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL CCD on CCD.ID = PM.ID
    left join dbo.CREDITTYPECODE CC on CC.ID = CCD.CREDITTYPECODEID
    left join dbo.OTHERPAYMENTMETHODCODE O on O.ID = OD.OTHERPAYMENTMETHODCODEID
    where
        BANKACCOUNTDEPOSITPAYMENT.DEPOSITID is null
        and PM.PAYMENTMETHODCODE in (0,1,2,10)  -- Cash, Check, Credit Card, Other

        and not exists(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD where ID = OD.OTHERPAYMENTMETHODCODEID)
        and RSX.APPLICATIONCODE <> 10  -- Order

        and (@SALESDEPOSITPROCESSID is null or FT.PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMID from dbo.SALESDEPOSITPROCESS where ID = @SALESDEPOSITPROCESSID))
        and FT.ID not in (select PAYMENTID from dbo.SALESORDERPAYMENT)
)