USP_PROSPECTPLAN_DELETE

                    CREATE procedure dbo.USP_PROSPECTPLAN_DELETE(@ID uniqueidentifier, @CHANGEAGENTID uniqueidentifier)
                    as begin
                        set nocount on;

                        --check deletion rules, if any


                        begin try         

                            -- Clear the reference on any planned gifts

                            update dbo.PLANNEDGIFT set 
                                PROSPECTPLANID = null,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = getdate()
                            where
                                PROSPECTPLANID = @ID;

                            --    AdamBu - Bug 21619 - When a plan gets removed, remove all its interaction steps.

                            --    -- clear the link field on the linked interaction (if applicable)

                            --    update dbo.INTERACTION set 

                            --        PROSPECTPLANID = null,

                            --        PROSPECTPLANSTATUSCODEID = null,

                            --        PLANOUTLINESTEPID = null,

                            --        CHANGEDBYID = @CHANGEAGENTID,

                            --        DATECHANGED = getdate()

                            --    where

                            --        PROSPECTPLANID = @ID

                            --        and INTERACTIONTYPECODEID is not null

                            --        and STATUSCODE <> 0;


                            declare @contextCache varbinary(128);
                            /* cache current context information */
                            set @contextCache = CONTEXT_INFO();
                            /* set CONTEXT_INFO to @CHANGEAGENTID */
                            if not @CHANGEAGENTID is null
                                set CONTEXT_INFO @CHANGEAGENTID;

                            delete from dbo.PLANPARTICIPANT where PROSPECTPLANID = @ID;

                            delete from dbo.INTERACTION where PROSPECTPLANID = @ID;

                            --delete any naming opportunity links or named recognitions for this opportunity

                            delete 
                                NAMINGOPPORTUNITYRECOGNITION
                            from 
                                dbo.NAMINGOPPORTUNITYRECOGNITION
                                inner join dbo.NAMINGOPPORTUNITYRECOGNITIONMGLINK on NAMINGOPPORTUNITYRECOGNITIONMGLINK.NAMINGOPPORTUNITYRECOGNITIONID = NAMINGOPPORTUNITYRECOGNITION.ID
                                inner join dbo.NAMINGOPPORTUNITYMGOPPORTUNITY on NAMINGOPPORTUNITYMGOPPORTUNITY.ID = NAMINGOPPORTUNITYRECOGNITIONMGLINK.MGOPPORTUNITYLINKID
                                inner join dbo.OPPORTUNITY on OPPORTUNITY.ID = NAMINGOPPORTUNITYMGOPPORTUNITY.OPPORTUNITYID
                            where 
                                OPPORTUNITY.PROSPECTPLANID = @ID;

                            delete from dbo.NAMINGOPPORTUNITYMGOPPORTUNITY where OPPORTUNITYID in (select ID from dbo.OPPORTUNITY where PROSPECTPLANID = @ID);
                            delete from dbo.BATCHREVENUE where OPPORTUNITYID in (select ID from dbo.OPPORTUNITY where PROSPECTPLANID = @ID);


                            -- remove any associations from stewardship plan steps

                            delete 
                                from dbo.STEWARDSHIPSTEPASSOCIATEDPLAN
                            where 
                                PROSPECTPLANID = @ID;

                            /*MAB 4/13/09 work item 31996.  Delete any prospect plan requests*/
                            delete 
                                PROSPECTASSIGNMENTREQUEST 
                            from 
                                dbo.PROSPECTASSIGNMENTREQUEST 
                                inner join dbo.PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID = PROSPECTASSIGNMENTREQUEST.ID
                            where
                                PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID = @ID;                                

                            /* reset CONTEXT_INFO to previous value */
                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;

                            exec USP_PROSPECTPLAN_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;
                    end