USP_DATALIST_SALESRECONCILIATIONREPORT_PAYMENTDETAILS

            CREATE procedure dbo.USP_DATALIST_SALESRECONCILIATIONREPORT_PAYMENTDETAILS
            (
                @RECONCILIATIONID uniqueidentifier = null,
        @ISSUPERVISOR bit = 0
            )
            as
                set nocount on;

                select
                    [REVENUEPAYMENTMETHOD].[ID] as [PAYMENTID],
                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
                    convert(nvarchar(20), SALESORDER.SEQUENCEID) as [SALESORDERNUMBER],
                    'http://' + CONVERT(nvarchar(36),SALESORDER.ID) as [SALESORDERLINK],
                    cast([SALESORDERPAYMENT].[PAYMENTDATEWITHTIMEOFFSET] as datetime) as [TRANSACTIONDATE],
                    [SALESORDERPAYMENT].[AMOUNT] as [AMOUNTPAID],
                    case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] 
                        when 1 then (
              select [CHECKNUMBER]
                              from dbo.[CHECKPAYMENTMETHODDETAIL]
                              where ID = [REVENUEPAYMENTMETHOD].[ID]
                        )
                        when 2 then (
                        select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER]
                            from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                            where ID = [REVENUEPAYMENTMETHOD].[ID]
                        )
                        when 10 then (
                        select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                            from dbo.[OTHERPAYMENTMETHODDETAIL]
                            where ID = [REVENUEPAYMENTMETHOD].[ID]
                        )
                    end as [PAYMENTDETAILS],
                    case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE]
                        when 2 then (
                            select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) 
                            from dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                            where ID = [REVENUEPAYMENTMETHOD].[ID]
                        )
                        when 10 then (
                            select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                            from dbo.[OTHERPAYMENTMETHODDETAIL]
                            where ID = [REVENUEPAYMENTMETHOD].[ID]
                        )
                        else ''
                    end as [PAYMENTTYPE],
                    dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.CONSTITUENTID) as [PATRONNAME],
          'http://www.blackbaud.com/ORDERID?ORDERID=' + CONVERT(nvarchar(36),SALESORDER.ID) as [SALESORDERREPORTLINK],
          case [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE]
            when 1 then
              (select case when [CHECKDATE] = '00000000' then null else dbo.UFN_DATE_FROMFUZZYDATE([CHECKDATE]) end from dbo.[CHECKPAYMENTMETHODDETAIL] where ID = [REVENUEPAYMENTMETHOD].[ID])
            end as [CHECKDATE]
                from dbo.[REVENUE]
                inner join dbo.[REVENUEPAYMENTMETHOD]
                    on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
                inner join (select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT, PAYMENTDATEWITHTIMEOFFSET from dbo.SALESORDERPAYMENT where [RECONCILIATIONID] = @RECONCILIATIONID and [DONOTRECONCILE] = 0
                                union all 
                                select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT, PAYMENTDATEWITHTIMEOFFSET from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where [RECONCILIATIONID] = @RECONCILIATIONID
                    ) as SALESORDERPAYMENT
                    on [REVENUE].[ID] = [SALESORDERPAYMENT].[PAYMENTID]
                inner join dbo.[SALESORDER]
                    on [SALESORDERPAYMENT].[SALESORDERID] = [SALESORDER].[ID]
                where
                    [SALESORDERPAYMENT].[RECONCILIATIONID] = @RECONCILIATIONID and
          -- LeeCh, 05/26/2009

          -- If @ISSUPERVISOR is true, include cash payments

                    ([REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] <> 0 or @ISSUPERVISOR = 1)and
                    (not ([REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 2 and (select [CREDITTYPECODEID] from dbo.[CREDITCARDPAYMENTMETHODDETAIL] where [ID] = [REVENUEPAYMENTMETHOD].[ID]) is null))
                order by 
                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] ASC,
                    [SALESORDERNUMBER] ASC,
                    [SALESORDERPAYMENT].[AMOUNT] ASC;

            return 0;