CREATE PROCEDURE DBO.CheckArrangement AS
SET NOCOUNT ON
--Last Changed; AV 2009 0319   delete from ArrangementLineDepartmentXTab before deleting the main line 
--Last Changed; AV 2009 0317 also delete from ArrangementLineDepartmentXTab

declare @refno int
Declare @MainResArrival varchar(8)
Declare @MainResDeparture varchar(8)
Declare @counter int set @counter = 0
DECLARE @arrnumber INT
DECLARE Rescur CURSOR FOR
SELECT referencenumber,ArrivalDate,DepartureDate FROM Reservations WITH (nolock) WHERE guestnumber=0 and Status <6
OPEN Rescur
FETCH NEXT FROM rescur
INTO @Refno,@MainResArrival,@MainResDeparture
WHILE(@@Fetch_status=0)
BEGIN
  DECLARE updatecur CURSOR FOR
  SELECT distinct arrangementlinenumber FROM arrangementlines WITH (nolock) WHERE
  arrangementlinenumber IN (SELECT arrangementlinenumber FROM arrangementreservationsxtab WITH (NOLOCK) WHERE referencenumber=@Refno)

  AND (ArrangementLines.Date <@MainResArrival OR ArrangementLines.Date >@MainResDeparture)
  OPEN updatecur
  FETCH NEXT FROM updatecur
  INTO @Arrnumber
  WHILE(@@Fetch_status=0)
  BEGIN
   DELETE FROM arrangementreservationsxtab WHERE arrangementlinenumber=@ArrNumber
   DELETE FROM arrangementRoomXtab WHERE arrangementlinenumber=@ArrNumber
   DELETE FROM ArrangementLineDepartmentXTab WHERE FK_ArrangementLineNumber = @arrnumber
   DELETE FROM ArrangementReservationConferenceLineXtab WHERE ArrangementLineNumber = @ArrNumber -- BA/20101002: CR017215
   DELETE FROM arrangementlines WHERE Arrangementlinenumber=@arrnumber

   set @counter = @Counter +1

   FETCH NEXT FROM updatecur INTO @arrnumber
  END
  CLOSE updatecur
  DEALLOCATE updatecur

 FETCH NEXT FROM rescur 
 INTO @Refno,@MainResArrival,@MainResDeparture
END
CLOSE ResCur
DEALLOCATE rescur
IF @Counter > 0
  print 'Number of Affected rows is: '+ convert (char(4),@Counter)
