USP_REPORT_RECEIPTFORMISCELLANEOUSPAYMENT

CREATE procedure dbo.USP_REPORT_RECEIPTFORMISCELLANEOUSPAYMENT 
            (
                @ID uniqueidentifier = null
            )

            as
                set nocount on;

                /* First time we run the receipt report, we need to set receipt date and receipt number fields in the
                   RevenueReceipt table
                */

                declare @CHANGEAGENTID uniqueidentifier
                declare @CURRENTDATE datetime

                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
                set @CURRENTDATE = getdate()

                if not exists(select ID from dbo.REVENUERECEIPT where REVENUEID = @ID)
                    begin
                        declare @REVENUERECEIPTID uniqueidentifier
                        declare @RECEIPTNUMBER int

                        set @REVENUERECEIPTID = newid()

                        --select @RECEIPTNUMBER = max(RECEIPTNUMBER) + 1 from dbo.REVENUERECEIPT

                        select @RECEIPTNUMBER = dbo.UFN_REVENUE_NEXTAVAILABLERECEIPTNUMBER()

                        begin try
                            insert into dbo.REVENUERECEIPT
                                (ID,RECEIPTNUMBER,RECEIPTDATE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,REVENUEID)
                            values
                                (@REVENUERECEIPTID,@RECEIPTNUMBER,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID,
                                @CURRENTDATE,@CURRENTDATE,@ID)
                            declare @NEXTRECEIPTNUMBER int = @RECEIPTNUMBER + 1
                            declare @STACKID uniqueidentifier = dbo.UFN_RECEIPTSTACKINFO_GETDEFAULTRECEIPTSTACKID()
                            exec dbo.USP_REVENUE_SETNEXTRECEIPTNUMBER @STACKID, @NEXTRECEIPTNUMBER
                            end try                            
                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                    end

                if exists(select ID from dbo.REVENUE where ID = @ID and NEEDSRERECEIPT<>0)
                    begin
                        begin try
            /* cmc
                            update dbo.FINANCIALTRANSACTION set  CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE  where ID = @ID
              update dbo.REVENUE_EXT set NEEDSRERECEIPT = 0 where ID = @ID
            */
                            update dbo.REVENUE set NEEDSRERECEIPT = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE  where 
                            ID = @ID
                        end try

                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch
                    end

                select 
                        REVENUE.ID,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        IsNull(REVENUE.TRANSACTIONAMOUNT,    REVENUE.AMOUNT) as AMOUNT,
                        REVENUE.DATE,
                        isnull(REVENUEREFERENCE.REFERENCE, '') as [PAYMENTSOURCE],
                        case when [CHECK].CHECKDATE ='00000000' then NULL else cast([CHECK].CHECKDATE as date) end as CHECKDATE,
                        [CHECK].CHECKNUMBER,
                        case when [CASH].REFERENCEDATE = '00000000' then NULL else cast([CASH].REFERENCEDATE as date) end as [CASHREFERENCEDATE],
                        [CASH].REFERENCENUMBER as [CASHREFERENCENUMBER],
                        [CREDIT].CARDHOLDERNAME,
                        CREDITTYPECODE.DESCRIPTION as [CREDITTYPE],
                        case when [CREDIT].EXPIRESON = '00000000' then NULL else cast([CREDIT].EXPIRESON as date) end as [EXPIRESON],
                        [CREDIT].AUTHORIZATIONCODE,
                        case when len(coalesce([CREDIT].CREDITCARDPARTIALNUMBER, '')) = 0 then ''
                                        else replicate('*', 16 - len([CREDIT].CREDITCARDPARTIALNUMBER)) + [CREDIT].CREDITCARDPARTIALNUMBER end as [CREDITCARDPARTIALNUMBER],
                        [OTHERPAYCODE].DESCRIPTION as [OTHERPAYMETHODDESCRIPTION],    
                        case when [OTHERPAY].REFERENCEDATE = '00000000' then NULL else cast([OTHERPAY].REFERENCEDATE as date) end as [OTHERPAYREFERENCEDATE],
                        [OTHERPAY].REFERENCENUMBER as [OTHERPAYREFERENCENUMBER],
                        REVENUERECEIPT.RECEIPTDATE,
                        REVENUERECEIPT.RECEIPTNUMBER,
                        CURRENCYPROPERTIES.ID as CURRENCYID,
                        CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
                        CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
                        CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
                        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE            

                    from dbo.REVENUE 
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID and REVENUESPLIT.APPLICATIONCODE=11 and 
                        REVENUESPLIT.TYPECODE=8
                    left join dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID
                    left join dbo.REVENUERECEIPT on REVENUERECEIPT.REVENUEID = REVENUE.ID
                    left join dbo.CASHPAYMENTMETHODDETAIL as [CASH] on [CASH].ID = REVENUEPAYMENTMETHOD.ID
                    left join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID
                    left join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
                    left join dbo.OTHERPAYMENTMETHODDETAIL as [OTHERPAY] on [OTHERPAY].ID = REVENUEPAYMENTMETHOD.ID
                    left join dbo.OTHERPAYMENTMETHODCODE as [OTHERPAYCODE] on [OTHERPAYCODE].ID = [OTHERPAY].OTHERPAYMENTMETHODCODEID
                    left join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = [CREDIT].CREDITTYPECODEID
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(REVENUE.TRANSACTIONCURRENCYID) as CURRENCYPROPERTIES                
        where
                    (REVENUE.ID = @ID) 
                order by DATE desc;