USP_DATALIST_SPONSORSHIPOPPORTUNITYRESERVEPROCESS

Returns a list of Sponsorship Opportunity Reserve Business process parameter sets.
Parameters

Parameter	Parameter Type	Mode	Description
@SHOWEXPIRED	bit	IN	Include inactive reservation keys
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_SPONSORSHIPOPPORTUNITYRESERVEPROCESS
      (
      @SHOWEXPIRED bit = 1
      )
      as
          set nocount on;

      select        
        SPO.ID,
        SPO.NAME,    
        SPP.STATUS,
        (select count(*) from dbo.SPONSORSHIPOPPORTUNITY where SPONSORSHIPOPPORTUNITY.RESERVATIONKEYID = SPO.ID) RESERVED,
        case when SPO.MAXKEYNUMBER > 0 then MAXKEYNUMBER else null end MAXKEYNUMBER,
        MAX(SPS.DATECHANGED) RESERVATIONDATE,         
        SPO.ENDDATE,
        dbo.UFN_CHANGEAGENT_GETUSERNAME(SPO.ADDEDBYID) ADDEDBY,
        dbo.UFN_CHANGEAGENT_GETUSERNAME(SPO.CHANGEDBYID) CHANGEDBY,
        SPO.DATECHANGED 
      from 
        dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS    SPO    
        left join dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESSSTATUS SPS on SPO.ID = SPS.PARAMETERSETID  
        inner join dbo.SPONSORSHIPOPPORTUNITYRESERVATIONSTATUS SPP on SPP.OPPORTUNITYRESERVATIONKEYID = SPO.ID
        where SPP.STATUSCODE = 1                                     
      group by SPO.ID, SPO.NAME, SPP.STATUS, SPO.MAXKEYNUMBER, SPO.ENDDATE, SPO.ADDEDBYID, SPO.CHANGEDBYID, SPO.DATECHANGED
      union
      select        
        SPO.ID,
        SPO.NAME,    
        SPP.STATUS,
        (select count(*) from dbo.SPONSORSHIPOPPORTUNITY where SPONSORSHIPOPPORTUNITY.RESERVATIONKEYID = SPO.ID) RESERVED,
        case when SPO.MAXKEYNUMBER > 0 then MAXKEYNUMBER else null end MAXKEYNUMBER,
        MAX(SPS.DATECHANGED) RESERVATIONDATE,         
        SPO.ENDDATE,      
        dbo.UFN_CHANGEAGENT_GETUSERNAME(SPO.ADDEDBYID) ADDEDBY,
        dbo.UFN_CHANGEAGENT_GETUSERNAME(SPO.CHANGEDBYID) CHANGEDBY,
        SPO.DATECHANGED 
      from 
        dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS    SPO    
        left join dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESSSTATUS SPS on SPO.ID = SPS.PARAMETERSETID  
        inner join dbo.SPONSORSHIPOPPORTUNITYRESERVATIONSTATUS SPP on SPP.OPPORTUNITYRESERVATIONKEYID = SPO.ID
        where @SHOWEXPIRED = 1 and SPP.STATUSCODE = 0                                 
      group by SPO.ID, SPO.NAME, SPP.STATUS, SPO.MAXKEYNUMBER, SPO.ENDDATE, SPO.ADDEDBYID, SPO.CHANGEDBYID, SPO.DATECHANGED                            
      order by SPO.NAME