USP_DATALIST_ORDERPAYMENT

            CREATE procedure dbo.USP_DATALIST_ORDERPAYMENT(@SALESORDERID uniqueidentifier)
            as
                set nocount on;

                select SALESORDERPAYMENT.ID,
                        SALESORDERPAYMENT.PAYMENTID,
                        REVENUE.DATE,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        SALESORDERPAYMENT.AMOUNT,
                        case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then
                            ''
                        when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then
                            (select 'Check #' + CHECKNUMBER + ', ' + convert(nvarchar(20), dbo.UFN_DATE_FROMFUZZYDATE(CHECKDATE), 101)
                                from dbo.CHECKPAYMENTMETHODDETAIL
                                where ID = REVENUEPAYMENTMETHOD.ID)
                        when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then
                            (select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) + ': ' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' - ' + CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE
                                from dbo.CREDITCARDPAYMENTMETHODDETAIL 
                                where ID = REVENUEPAYMENTMETHOD.ID)
                        when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then
                            (select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                                from dbo.OTHERPAYMENTMETHODDETAIL
                                where ID = REVENUEPAYMENTMETHOD.ID)
                        end as 'DETAILS',
                        case when REVENUEPOSTED.ID is null then 0 else 1 end as ISPOSTED,
                        0 as ISSECURITYDEPOSIT
                from dbo.REVENUE
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                inner join dbo.SALESORDERPAYMENT on REVENUE.ID = SALESORDERPAYMENT.PAYMENTID
                left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                where SALESORDERPAYMENT.SALESORDERID = @SALESORDERID

                union all

                select RESERVATIONSECURITYDEPOSITPAYMENT.ID,
                        RESERVATIONSECURITYDEPOSITPAYMENT.PAYMENTID,
                        REVENUE.DATE,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        RESERVATIONSECURITYDEPOSITPAYMENT.AMOUNT,
                        'Security deposit ' +
                        case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then
                            ''
                        when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then
                            (select case when dbo.UFN_DATE_FROMFUZZYDATE(CHECKDATE) is null then '' else 'Check #' + CHECKNUMBER + ', ' + convert(nvarchar(20), dbo.UFN_DATE_FROMFUZZYDATE(CHECKDATE), 101) end
                                from dbo.CHECKPAYMENTMETHODDETAIL
                                where ID = REVENUEPAYMENTMETHOD.ID)
                        when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then
                            (select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) + ': ' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' - ' + CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE
                                from dbo.CREDITCARDPAYMENTMETHODDETAIL 
                                where ID = REVENUEPAYMENTMETHOD.ID)
                        when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then
                            (select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                                from dbo.OTHERPAYMENTMETHODDETAIL
                                where ID = REVENUEPAYMENTMETHOD.ID)
                        end as 'DETAILS',
                        case when REVENUEPOSTED.ID is null then 0 else 1 end as ISPOSTED,
                        1 as ISSECURITYDEPOSIT
                from dbo.REVENUE
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                inner join dbo.RESERVATIONSECURITYDEPOSITPAYMENT on REVENUE.ID = RESERVATIONSECURITYDEPOSITPAYMENT.PAYMENTID
                left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                where RESERVATIONSECURITYDEPOSITPAYMENT.RESERVATIONID = @SALESORDERID