Delete CP Enquiries

begin
/*
 * NAME:		delete_duplicate_register_enquiry_ss
 * DESCRIPTION:	Script to delete a duplicate register enquiry.
 *             	Dupicate REGISTER_ENQUIRY.ID and associated PERSON_ID MUST be added to script before running
 * HISTORY:     1.0 gk 01/03/2021 PRB001656: Initial version     
 *				1.0 gk 08/03/2021 Improvements using cascade_delete
 */

	-- Collect enquiry_id and add to script
	declare @v_enquiry_id numeric(9);
	declare @v_person_id numeric(9);
	--
	declare @v_debug varchar(1) = 'N'; -- set to 'Y' to debug any issues
	declare @v_id_criteria varchar(100);
	--
	-- Set paramaters here
	set @v_enquiry_id = 0; -- set enquiry_id
	set @v_person_id = 0; -- set person_id
	--
	begin try
		--
		-- check enquiry for person exists:
		if  (
				select count(*) 
				from register_enquiries  
				where id = @v_enquiry_id
				and person_id = @v_person_id) > 0
		begin
			--
			begin transaction t1;
			--
			set @v_id_criteria = 'id = '+convert(varchar,@v_enquiry_id);
				--
				execute mosaic_delete_person.delete_cascade
				  'register_enquiries', -- p_top_level_table
				  @v_id_criteria, -- p_top_level_criteria
				  'reg_enquiry_session,reg_enquiry_session_step,event,reg_enquiry_search_results,reg_enquiry_search_criteria',
				  @v_debug
				;
				--
				print 'Register Enqiry '+convert(varchar,@v_enquiry_id)+' succesfully deleted';
				--
				commit transaction t1;
			end
		else -- reg_enquiry not found
			begin
				--
				print 'Register Enqiry '+convert(varchar,@v_enquiry_id)+' does not exist for person '+convert(varchar,@v_person_id);   
				--		  
			end;
	end try
	begin catch
		rollback transaction t1;
		--
		-- Preserve error details
		declare	@v_error_number		int,
				@v_error_message	nvarchar(4000),
				-- Required for pseudo-reraise
				@v_error_severity	int,
				@v_error_state		int;
		select
			@v_error_number = error_number(),
			@v_error_message = error_message(),
			@v_error_severity = error_severity(),
			@v_error_state = error_state();
		--
		-- Re-raise the exception
		-- NB. SQLServer doesn't currently have a re-raise facilty, but this is close
		raiserror(@v_error_message, @v_error_severity, @v_error_state);
		--
	end catch;
end;

declare 
  -- Collect enquiry_id and add to script
  v_enquiry_id numeric(9);
  v_person_id numeric(9);
  --
  v_record_count numeric(9);
  v_sqlerrm varchar2(4000);
  v_debug varchar2(1);
  v_id_criteria varchar2(100);
begin
/*
 * NAME:        delete_duplicate_register_enquiry_ora
 * DESCRIPTION: Script to delete a duplicate register enquiry.
 *              Dupicate REGISTER_ENQUIRIES.ID and associated PERSON_ID MUST be added to script before running
 * HISTORY:     1.0 gk 08/03/2021 PRB001656: Initial version        
 */
  --
  -- Set paramaters here
  v_enquiry_id := 0; -- set enquiry_id
  v_person_id := 0; -- set person_id
  --
  v_debug := 'N'; -- set to Y to debug any issues
  --
  --
    -- check enquiry for person exists:
    select coalesce(count(*),0) into v_record_count
    from register_enquiries  
    where id = v_enquiry_id
    and person_id = v_person_id;
    --
    if v_record_count = 0 then
      -- Enquiry/person combo does not exist
      dbms_output.put_line('Register Enqiry '||v_enquiry_id||' does not exist for person '||v_person_id);   
    else
    --
    v_id_criteria := 'id = '||cast(v_enquiry_id as varchar2);
      --
      -- Cascade delete through related tables:
      mosaic_delete_person.delete_cascade(
            'register_enquiries', -- p_top_level_table
            v_id_criteria, -- p_top_level_criteria
            'reg_enquiry_session,reg_enquiry_session_step,event,reg_enquiry_search_results,reg_enquiry_search_criteria', -- p_included_tables
            v_debug
      ); 
      --
      commit;
      --
      dbms_output.put_line('Register Enqiry '||v_enquiry_id||' succesfully deleted');
      --
    end if;
    --
    exception
    when others then
      v_sqlerrm := sqlerrm;
      --
      -- Reraise for main
      raise;
end;