CREATE PROCEDURE dbo.ClearArrangementManualLines @AMainResArrival CHAR(8), @AMainResDeparture CHAR(8),  @AReferenceNumber INT AS

  --Created AV 20070925 (Code taken from delphi unit because of problems)

  -- Last changed:
  -- RN 2012 1121 Replaced IS NULL with ISNULL function.
  -- AV 2007 1025 rewrite to inner joins
DECLARE @arrnumber INT
DECLARE updatecur CURSOR FOR
/*
SELECT arrangementlinenumber FROM arrangementlines(nolock) WHERE
arrangementlinenumber IN (SELECT arrangementlinenumber FROM arrangementreservationsxtab(NOLOCK) WHERE referencenumber=@AReferencenumber)
AND arrangementlinenumber NOT IN (SELECT arrangementlinenumber FROM arrangementRoomXtab(NOLOCK))
AND (ArrangementLines.Date  < @AMainResArrival OR ArrangementLines.Date >@AMainResDeparture)
*/

SELECT DISTINCT Arrangementlines.Arrangementlinenumber 
FROM Arrangementlines WITH (NOLOCK) 
INNER JOIN ArrangementReservationsXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXTab.ArrangementLineNumber
LEFT OUTER JOIN ArrangementRoomXtab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber= ArrangementRoomXTab.ArrangementLineNumber
WHERE ArrangementReservationsXTab.ReferenceNumber = @AReferenceNumber 
  AND ISNULL(ArrangementRoomXTab.ArrangementLineNumber, 0) = 0
  AND (ArrangementLines.Date  < @AMainResArrival OR ArrangementLines.Date >@AMainResDeparture)

OPEN updatecur
FETCH NEXT FROM updatecur
INTO @Arrnumber
WHILE(@@Fetch_status=0)
BEGIN
  DELETE FROM Arrangementreservationsxtab WHERE arrangementlinenumber=@ArrNumber
  DELETE FROM ArrangementLineDepartmentXTab WHERE FK_ArrangementLineNumber=@arrnumber
  DELETE FROM Arrangementlines WHERE arrangementlinenumber=@arrnumber
FETCH NEXT FROM updatecur INTO @arrnumber
END
CLOSE updatecur
DEALLOCATE updatecur

