IF (SELECT COUNT(*) FROM ReservationMain WITH (NOLOCK) WHERE FkCustomerHotel IS NULL) > 0
BEGIN
  DECLARE @CustomerNumber INT, @HotelNumber INT, @DeletedDate DATE, @FkCustomerHotel INT
  --Invalid CustomerHotel records
  DECLARE #HotelCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT CustomerNumber, HotelNumber, CONVERT(DATE, DeletedDate, 112) FROM Customer C WITH (NOLOCK) WHERE CustomerTypeNumber = 7 AND (ISNULL(C.HotelNumber, -1) >= 0 OR C.LocalHotel = 1)
    AND (ISNULL(C.MarkAsDeleted, 0) > 0 or C.ArchiveDate IS NULL)
  OPEN #HotelCur

  FETCH NEXT FROM #HotelCur
  INTO @CustomerNumber, @HotelNumber, @DeletedDate
  WHILE @@FETCH_STATUS = 0 -- safe
  BEGIN
    --For each invalid hotel fetch a matching by HotelNumber valid record 
    SET @FkCustomerHotel = 0
    SELECT @FkCustomerHotel = CustomerNumber FROM Customer C WITH (NOLOCK) 
    WHERE CustomerTypeNumber = 7 AND (ISNULL(C.HotelNumber, -1) >= 0 OR C.LocalHotel = 1)
	  AND HotelNumber = @HotelNumber AND ISNULL(C.MarkAsDeleted, 0) = 0 and NOT C.ArchiveDate IS NULL
    
	IF @FkCustomerHotel > 0
	BEGIN
	  --Update Res Folios created after hotel deleted date with new HotelNumber
      UPDATE Folios SET HotelCustomerNumber = @FkCustomerHotel WHERE FolioNumber IN (  
        SELECT F.FolioNumber FROM Folios F WITH (NOLOCK) 
        WHERE ISNULL(F.ReferenceNumber, 0) > 0 AND F.HotelCustomerNumber IS NULL OR F.HotelCustomerNumber = @CustomerNumber
        EXCEPT 
        SELECT DISTINCT T.FolioNumber FROM Transactions T WITH (NOLOCK) 
        WHERE T.HotelCustomerNumber = @CustomerNumber AND T.DateStamp <= @DeletedDate
	  )
    END
    FETCH NEXT FROM #HotelCur
    INTO @CustomerNumber, @HotelNumber, @DeletedDate
  END
  --Update empty Res Main FkCustomerHotel relation using fixed Folio data
  UPDATE ReservationMain SET FkCustomerHotel = F.HotelCustomerNumber
  FROM Folios F, ReservationMain RM 
  WHERE RM.FkCustomerHotel IS NULL AND F.ReferenceNumber = RM.ReferenceNumber
  CLOSE #HotelCur
  DEALLOCATE  #HotelCur
  IF (SELECT COUNT(*) FROM ReservationMain WITH (NOLOCK) WHERE FkCustomerHotel IS NULL) > 0
  BEGIN
    update ReservationMain SET FkCustomerHotel = H.CustomerNumber
    from ReservationMain RM, Customer H (NOLOCK)
    where RM.FkCustomerHotel IS NULL AND H.HotelNumber = RM.HOTELNUMBER and H.DeletedDate IS NULL
  END
  IF (SELECT COUNT(*) FROM ReservationMain WITH (NOLOCK) WHERE FkCustomerHotel IS NULL) > 0
  BEGIN
    DECLARE @HCN int, @HotelNumber1 int
    SELECT TOP 1 @HCN = CustomerNumber, @HotelNumber1 = HotelNumber FROM Customer (nolock) where CustomerTypeNumber = 7 and (HotelNumber >=0 or LocalHotel=1) AND MarkAsDeleted = 0
    order by LocalHotel desc, HotelNumber
    UPDATE ReservationMain SET FkCustomerHotel = @HCN, HotelNumber = @HotelNumber1 where HotelNumber IS NULL
  END
END
