USP_REPORT_SALESORDERBALANCEDUE

                create procedure dbo.USP_REPORT_SALESORDERBALANCEDUE
                (
                    @INCLUDERESERVATIONS bit = 1,
                    @INCLUDECANCELLED bit = 1,
                    @INCLUDEPAYONARRIVAL bit = 1,
                    @EXCLUDEBEFOREDATE date = null
                )
                as
                    set nocount on;  

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate(); 


                    declare @ORDERSWITHBALANCE table (RESERVATIONID nvarchar(255),
                                                    SALESORDERID nvarchar(255),                                                    
                                                    ORDERNUMBER integer, 
                                                    CONSTITUENTNAME nvarchar(255), 
                                                    ORDERDATE datetime,
                                                    DEPOSITDUEDATE datetime,
                                                    FINALDUEDATE datetime,
                                                    FINALDAYSPASTDUE integer,
                                                    DEPOSITDAYSPASTDUE integer, 
                                                    DEPOSITBALANCE money,
                                                    DEPOSITREQUIRED bit,
                                                    FINALBALANCE money,
                                                    TYPECODE tinyint,
                                                    SECURITYDEPOSITDAYSPASTDUE integer,
                                                    SECURITYDEPOSITBALANCE money,
                                                    SECURITYDEPOSITDUEDATE datetime,
                                                    SECURITYDEPOSITREQUIRED bit);

                    if @INCLUDERESERVATIONS = 1                                                    
                    begin                        
                        insert into @ORDERSWITHBALANCE
                        select
                            'http://www.blackbaud.com/RESERVATIONID?RESERVATIONID=' + CONVERT(nvarchar(36), SALESORDER.ID) RESERVATIONID,
                            '' SALESORDERID,
                            SALESORDER.SEQUENCEID ORDERNUMBER,
                            dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.CONSTITUENTID) CONSTITUENTNAME,
                            RES.ARRIVALDATE ORDERDATE,
                            RES.DEPOSITDUEDATE DEPOSITDUEDATE,
                            RES.FINALDUEDATE FINALDUEDATE,
                            RES.FINALDAYSPASTDUE FINALDAYSPASTDUE,
                            RES.DEPOSITDAYSPASTDUE DEPOSITDAYSPASTDUE,
                            RES.DEPOSITBALANCE DEPOSITBALANCE,
                            RES.DEPOSITREQUIRED DEPOSITREQUIRED, 
                            RES.FINALBALANCE FINALBALANCE,
                            0 TYPECODE,
                            RES.SECURITYDEPOSITDAYSPASTDUE,
                            RES.SECURITYDEPOSITBALANCE,
                            RES.SECURITYDEPOSITDUEDATE,
                            RES.SECURITYDEPOSITREQUIRED
                        from dbo.UFN_RESERVATION_GETRESERVATIONSWITHBALANCE(@CURRENTDATE) RES
                        inner join dbo.SALESORDER on SALESORDER.ID = RES.RESERVATIONID
                        where (RES.DEPOSITBALANCE > 0.0 or (RES.FINALBALANCE <> 0.0) or RES.SECURITYDEPOSITBALANCE > 0.0) 
                            and ((SALESORDER.STATUSCODE <> 5 and @INCLUDECANCELLED = 0) or (@INCLUDECANCELLED = 1))
                            and ((RES.ARRIVALDATE >= @EXCLUDEBEFOREDATE) or (@EXCLUDEBEFOREDATE is null))
                    end

                    if @INCLUDEPAYONARRIVAL = 1 
                    begin
                        insert into @ORDERSWITHBALANCE
                        select
                            '',
                            'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), SALESORDER.ID) SALESORDERID,
                            SALESORDER.SEQUENCEID,
                            dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.CONSTITUENTID),
                            SALESORDER.TRANSACTIONDATE,
                            null,
                            null,
                            null,
                            null,
                            null,
                            0,
                            dbo.UFN_SALESORDER_GETBALANCE(SALESORDER.ID),
                            1,
                            null,
                            null,
                            null,
                            0
                        from dbo.SALESORDER
                        where SALESORDER.STATUSCODE = 6
                            and ((SALESORDER.TRANSACTIONDATE >= @EXCLUDEBEFOREDATE) or (@EXCLUDEBEFOREDATE is null))
                    end 

                    select *
                    from @ORDERSWITHBALANCE;