UFN_RECONCILIATION_GETOTHERRECEIPTS_FULL

CREATE function dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS_FULL
(
    @RECONCILIATIONID uniqueidentifier,
    @APPUSERID uniqueidentifier = null,
    @WORKSTATIONID uniqueidentifier = null
) returns table 
as return
    select
        (
            select ID 
            from dbo.RECONCILIATIONDETAIL
            where 
                RECONCILIATIONID = @RECONCILIATIONID
                and PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                and (CREDITTYPECODEID = CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID or CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID is null)
                and (OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID or OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID is null)
        ) as ID,
        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
        CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID,
        OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID,
        cast(0 as bit) as ISREFUND,
        cast(0 as bit) as ISLABEL,
        FT.BASEAMOUNT as TOTALAMOUNT,
        isnull(SALESORDER.TRANSACTIONDATE, cast(FT.DATE as datetime)) as DATE,
        CREDITCARDPAYMENTMETHODDETAIL.CARDHOLDERNAME,
        CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE,
        CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join (
        select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, APPUSERID
        from dbo.SALESORDERPAYMENT 
        where 
            (SALESORDERPAYMENT.APPUSERID = @APPUSERID or @APPUSERID is null)
            and SALESORDERPAYMENT.DONOTRECONCILE = 0

        union all 
        select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, APPUSERID
        from dbo.RESERVATIONSECURITYDEPOSITPAYMENT
        where
            (RESERVATIONSECURITYDEPOSITPAYMENT.APPUSERID = @APPUSERID or @APPUSERID is null)
    ) as SALESORDERPAYMENT on FT.ID = SALESORDERPAYMENT.PAYMENTID
    inner join
        dbo.REVENUEPAYMENTMETHOD on FT.ID = REVENUEPAYMENTMETHOD.REVENUEID
    inner join
        dbo.SALESORDER on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
    left join
        dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
    left join
        dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
    where 
        (
            SALESORDERPAYMENT.RECONCILIATIONID = @RECONCILIATIONID
            or (
                SALESORDERPAYMENT.RECONCILIATIONID is null
                and (select STATUSCODE from dbo.RECONCILIATION where ID = @RECONCILIATIONID) = 0 
                and (SALESORDERPAYMENT.APPUSERID = @APPUSERID or @APPUSERID is null)
            )
        )
        and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE <> 0  -- Cash

        and SALESORDER.SALESMETHODTYPECODE <> 2  -- Online Sales

        and FT.DELETEDON is null

    --Get refunds

    union all
    select
        @RECONCILIATIONID,
        null as PAYMENTMETHODCODE,
        null as PAYMENTMETHOD,
        null as CREDITTYPECODEID,
        null as OTHERPAYMENTMETHODCODEID,
        cast(1 as bit) as ISREFUND,
        cast(0 as bit) as ISLABEL,
        FT.TRANSACTIONAMOUNT as TOTALAMOUNT,
        cast(FT.DATE as datetime) as DATE,
        null as CARDHOLDERNAME,
        null as AUTHORIZATIONCODE,
        null as CREDITCARDPARTIALNUMBER
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    inner join
        dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = FT.ID
    left outer join
        dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
    where
        (CREDITPAYMENT.APPUSERID = @APPUSERID or @APPUSERID is null)
        and (SALESORDER.ID is not null or EXT.SALESORDERID is not null)
        and (
            CREDITPAYMENT.RECONCILIATIONID = @RECONCILIATIONID
            or (
                CREDITPAYMENT.RECONCILIATIONID is null
                and CREDITPAYMENT.REFUNDPROCESSED = 1
                and (select STATUSCODE from dbo.RECONCILIATION where ID = @RECONCILIATIONID) = 0
            )
        )