USP_PROSPECTSEGMENTATION_PROSPECTS_CLEAR

      create procedure dbo.USP_PROSPECTSEGMENTATION_PROSPECTS_CLEAR(
          @PROSPECTSEGMENTATIONID uniqueidentifier,
          @CHANGEAGENTID uniqueidentifier 
      )as
        set nocount on;

        --Cache current context information 

        declare @CONTEXTCACHE varbinary(128); 
        set @CONTEXTCACHE = CONTEXT_INFO(); 

        --Set CONTEXT_INFO to @CHANGEAGENTID 

        if @CHANGEAGENTID is not null 
          set CONTEXT_INFO @CHANGEAGENTID; 

        delete 
            dbo.PROSPECTSEGMENTATIONPROSPECT
        from
            dbo.PROSPECTSEGMENTATIONPROSPECT PROSPECT

        --Join to Primary Segment/Variable Tables

        left join PROSPECTSEGMENTATIONSECTION PRIMARYSECTION on
            PROSPECT.PRIMARYSECTIONID =  PRIMARYSECTION.ID
        left join PROSPECTSEGMENTATIONVARIABLE PRIMARYVARIABLE on
          PRIMARYSECTION.VARIABLEID = PRIMARYVARIABLE.ID

        --Join to Secondary Segment/Variable Tables 

        left join PROSPECTSEGMENTATIONSECTION SECONDARYSECTION on
            PROSPECT.SECONDARYSECTIONID =  SECONDARYSECTION.ID
        left join PROSPECTSEGMENTATIONVARIABLE SECONDARYVARIABLE on
          SECONDARYSECTION.VARIABLEID = SECONDARYVARIABLE.ID

        where 
            PRIMARYVARIABLE.PROSPECTSEGMENTATIONID =   @PROSPECTSEGMENTATIONID or
          SECONDARYVARIABLE.PROSPECTSEGMENTATIONID = @PROSPECTSEGMENTATIONID 

        update
          dbo.PROSPECTSEGMENTATION
        set
          STATUSCODE=1
        where
          ID = @PROSPECTSEGMENTATIONID

        --Reset CONTEXT_INFO to previous value 

        if not @CONTEXTCACHE is null 
          set CONTEXT_INFO @CONTEXTCACHE;