CREATE PROCEDURE [dbo].[DeleteDominoHotel] @AHotelNumber INT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen july 2009

  Notes:
    Used to delete a hotel in a Domino solution.
    No Economy is deleted if not running bookkeeping per hotel
    Check that CASCADE is set for PersonalFollowupXTab(followup relation) and 
    Attachment (AttachmentReservationsXTab relation)
  Changes:
    KJ 20140915: Added missing tables regarding products.
    KJ 20140915: Bug: Fix for "Wrong errortext when different hotels has posted on same folio"
    KJ 20140701: Ext: Added ProductDescription.
    KJ 20140624: Ext: Menu ware split in whole amount. Req #1561
    KJ 20110216: Ext: Added delete of CustomerFieldsPerHotel regarding products
    KJ 20110215: Bug: Fix for "Handle that ware is in use in a package"
    KJ 20110202: Ext: Added delete of package, products, ware, waregroups and accounts
    KJ 20110121: Bug: Fix for "The DELETE statement conflicted with the REFERENCE constraint "FK_ArrangementRoomXtab_ArrangementLines". The conflict occurred in database "db2", table "dbo.ArrangementRoomXtab", column 'ArrangementLineNumber'."
    KJ 20110120: Bug: Fix for "The DELETE statement conflicted with the REFERENCE constraint "FK_TransactionsRelation_Transactions". The conflict occurred in database "db2", table "dbo.TransactionsRelation", column 'FK_TransactionNumber'." 
    KJ 20100603: Ext: Added delete of AllocationProfile and RoomAllocationProfileXTab
    KJ 20090708: Don't delete ReferenceNumber = 0

*/
DECLARE @ShowPrint BIT
SET @ShowPrint = 1

IF @ShowPrint = 1
  PRINT 'DeleteDominoHotel for HotelNumber: ' + CONVERT(VARCHAR, @AHotelNumber)

DECLARE @NewLocalHotel INT
DECLARE @CurDate VARCHAR(8)
DECLARE @WareNumber INT
DECLARE @StockNumber INT
DECLARE @WareGroupNumber INT
DECLARE @ProductNumber INT

SELECT @CurDate = SYSTEMDATE FROM System WITH(NOLOCK) WHERE NUMBER = 1

DECLARE @HotelCustomerNumber INT
DECLARE @LocalHotel INT
SELECT @HotelCustomerNumber = CustomerNumber, @LocalHotel = LocalHotel 
FROM Customer WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber

IF @ShowPrint = 1
  PRINT 'HotelCustomerNumber: ' + CONVERT(VARCHAR, @HotelCustomerNumber)

DECLARE @PrimaryKey INT

SELECT DISTINCT FolioNumber 
INTO #tab_FolioNumber
FROM Folios WITH(NOLOCK)
WHERE HotelCustomerNumber = @HotelCustomerNumber
OR ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

-- Delete Economy start
DECLARE @BookMixedResourcesEnabled INT
SELECT @BookMixedResourcesEnabled = CONVERT(INT, KeyValue) FROM HotelSetup WHERE SearchKey = 'BookMixedResourcesEnabled'
DECLARE @BookkeepingPerHotel INT
SELECT @BookkeepingPerHotel = CONVERT(INT, KeyValue) FROM HotelSetup WHERE SearchKey = 'BookkeepingPerHotel'

IF @ShowPrint = 1
  PRINT 'DELETE Economy START'
  
DECLARE @cnt INT
SET @cnt = NULL

SELECT @cnt = COUNT(DISTINCT HotelCustomerNumber)
FROM Transactions WITH (NOLOCK)
GROUP BY FolioNumber
HAVING COUNT(DISTINCT HotelCustomerNumber) > 1
  
IF @BookkeepingPerHotel <> 1 OR @BookMixedResourcesEnabled = 1 OR @cnt <> NULL
BEGIN
  IF @cnt IS NOT NULL 
    RAISERROR('[DeleteDominoHotel]: Not able to delete economy stuff when Transactions for differenct hotels exist on same folio',15,1)
  ELSE
    RAISERROR('[DeleteDominoHotel]: Not able to delete economy stuff when not running Bookkeeping Per Hotel (HotelSetup->Basic->Central Office -> Bookkeeping Per Hotel)',15,1)
END
ELSE
BEGIN
  -- Disable triggers (Audit log)
  EXEC EnableAuditLog 0
  
  IF @ShowPrint = 1
    PRINT 'FolioCardXTab'
  DELETE FolioCardXTab WHERE FolioNumber IN (SELECT FolioNumber FROM #tab_FolioNumber)

  IF @ShowPrint = 1
    PRINT 'TransactionKitchenStatementXTab'
  DELETE TransactionKitchenStatementXTab WHERE TransactionNumber IN (SELECT TransactionNumber FROM Transactions WITH(NOLOCK) WHERE HotelCustomerNumber = @HotelCustomerNumber)

  IF @ShowPrint = 1
    PRINT 'KitchenStatement'
  DELETE KitchenStatement WHERE FolioNumber IN (SELECT FolioNumber FROM #tab_FolioNumber)

  IF @ShowPrint = 1
    PRINT 'FolioBillXTab'
  DELETE FolioBillXTab WHERE FolioNumber IN (SELECT FolioNumber FROM #tab_FolioNumber)

  IF @ShowPrint = 1
    PRINT 'Invoices'
  DELETE Invoices WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'TransactionsRelation'
  DELETE TransactionsRelation WHERE FK_TransactionNumber IN (SELECT TransactionNumber FROM Transactions WITH(NOLOCK) WHERE HotelCustomerNumber = @HotelCustomerNumber)

  IF @ShowPrint = 1
    PRINT 'TransactionDeleteNote'
  DELETE TransactionDeleteNote WHERE FK_TransactionNumber IN (SELECT TransactionNumber FROM Transactions WITH(NOLOCK) WHERE HotelCustomerNumber = @HotelCustomerNumber)

  IF @ShowPrint = 1
    PRINT 'Transactions'
  DELETE Transactions WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'BillCardInfo'
  DELETE BillCardInfo WHERE TransactionNumber IN (SELECT TransactionNumber FROM Transactions WITH(NOLOCK) WHERE HotelCustomerNumber = @HotelCustomerNumber)

  IF @ShowPrint = 1
    PRINT 'FolioRestResTableChairsXTab'
  DELETE FolioRestResTableChairsXTab WHERE FolioNumber IN (SELECT FolioNumber FROM #tab_FolioNumber)

  IF @ShowPrint = 1
    PRINT 'Folios'
  DELETE Folios WHERE FolioNumber IN (SELECT FolioNumber FROM #tab_FolioNumber)

  IF @ShowPrint = 1
    PRINT 'AccountDaily'
  DELETE AccountDaily WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'CanceledBillNumbers'
  DELETE CanceledBillNumbers WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'DELETE Economy END'
  -- Delete Economy END

  -- Delete reservations START
  DECLARE #DeleteCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT AddressNumber 
    FROM ReservationAddressXTab WITH(NOLOCK) 
    WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)
  OPEN #DeleteCur

  IF @ShowPrint = 1
    PRINT 'ReservationAddressXTab'
  DELETE FROM ReservationAddressXTab WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  FETCH NEXT FROM #DeleteCur INTO @PrimaryKey

  IF @ShowPrint = 1
    PRINT 'ReservationAddress'
  WHILE @@FETCH_STATUS <> -1
  BEGIN
    DELETE FROM ReservationAddress WHERE AddressNumber=@PrimaryKey
    FETCH NEXT FROM #DeleteCur INTO @PrimaryKey
  END

  CLOSE #DeleteCur
  DEALLOCATE #Deletecur

  IF @ShowPrint = 1
    PRINT 'ReservationAttachmentXTab'
  DELETE FROM ReservationAttachmentXTab WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)
  -- Cascade delete in Attachment

  IF @ShowPrint = 1
    PRINT 'ReservationRoomLineXTab'
  DELETE ReservationRoomLineXTab WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  IF @ShowPrint = 1
    PRINT 'ReservationRoomLine'
  DELETE ReservationRoomLine WHERE LineNumber NOT IN (SELECT LineNumber FROM ReservationRoomLineXTab WITH(NOLOCK))
  
  IF @ShowPrint = 1
    PRINT 'ReservationRoomLineSubTypeXTab'
  DELETE ReservationRoomLineSubTypeXTab WHERE ReservationRoomLineNumber NOT IN (SELECT LineNumber FROM ReservationRoomLineXTab WITH(NOLOCK))

  -- Followup
  IF @ShowPrint = 1
    PRINT 'ReservationFollowUpXTab'
  DELETE FROM ReservationFollowUpXTab WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  IF @ShowPrint = 1
    PRINT 'FollowupRoomsXtab'
  DELETE FROM FollowupRoomsXtab WHERE FK_RoomNumber IN (SELECT RoomNumber FROM Rooms WHERE HotelNumber = @AHotelNumber)

  IF @ShowPrint = 1
    PRINT 'Followup'
  DELETE Followup WHERE FollowupNumber not IN (SELECT Followupnumber FROM ReservationFollowUpXTab WITH(NOLOCK)UNION SELECT Followupnumber FROM CustomerXTABFollowUP WITH(NOLOCK)) 
  -- cascade delete personelfollowupxtab

  -- Arrangement
  IF @ShowPrint = 1
    PRINT 'Arrangement'
  DECLARE #DeleteCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT ArrangementLineNumber 
    FROM ArrangementReservationsXtab WITH(NOLOCK) 
    WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)
  OPEN #DeleteCur

  DELETE FROM ArrangementReservationsXtab WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  FETCH NEXT FROM #DeleteCur INTO @PrimaryKey

  WHILE @@FETCH_STATUS <> -1
  BEGIN
    DELETE FROM ArrangementRoomXtab WHERE ArrangementLineNumber=@PrimaryKey
    DELETE FROM ArrangementLineDepartmentXTab WHERE FK_ArrangementLineNumber=@PrimaryKey
    DELETE FROM ArrangementReservationConferenceLineXTab WHERE ArrangementLineNumber=@PrimaryKey
    DELETE FROM ArrangementLines WHERE ArrangementLineNumber=@PrimaryKey
    FETCH NEXT FROM #DeleteCur INTO @PrimaryKey
  END

  CLOSE #DeleteCur
  DEALLOCATE #DeleteCUR

  IF @ShowPrint = 1
    PRINT 'ReservationMeetingXTab'
  DELETE ReservationMeetingXTab WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  IF @ShowPrint = 1
    PRINT 'ReservationConferenceLine'
  DELETE ReservationConferenceLine WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  IF @ShowPrint = 1
    PRINT 'ReservationNotes'
  DELETE ReservationNotes WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)
  
  IF @ShowPrint = 1
    PRINT 'ReservationCardXTab'
  DELETE ReservationCardXTab WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  IF @ShowPrint = 1
    PRINT 'Reservations'
  DELETE Reservations WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  IF @ShowPrint = 1
    PRINT 'ReservationMain'
  DELETE ReservationMain WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

  IF @ShowPrint = 1
    PRINT 'ProductPrice'
  DELETE FROM PRODUCTPRICE WHERE RoomTypeNumber IN (SELECT RoomTypeNumber FROM RoomTypes WHERE HotelNumber = @AHotelNumber)
  
  IF @ShowPrint = 1
    PRINT 'CustomerFieldsPerHotel'
  DELETE CustomerFieldsPerHotel WHERE HotelNumber = @AHotelNumber

  IF @ShowPrint = 1
    PRINT 'AllotmentLines'
  DELETE AllotmentLines WHERE AllotmentNumber IN (SELECT AllotmentNumber FROM Allotments WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber)

  IF @ShowPrint = 1
    PRINT 'Allotments'
  DELETE Allotments WHERE HotelNumber = @AHotelNumber

  IF @ShowPrint = 1
    PRINT 'AllocationProfile'

  DECLARE @apf INT
  SELECT @apf = AllocationProfileNumber FROM AllocationProfile WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber
  DELETE RoomAllocationProfileXTab WHERE AllocationProfileNumber = @apf
  DELETE AllocationProfile WHERE AllocationProfileNumber = @apf
  -- Delete reservations END
  
  -- Delete Package START
  IF @ShowPrint = 1
    PRINT 'PacketItem_NewDepartmentXTab'
  DELETE PacketItem_NewDepartmentXTab WHERE FK_Department IN (SELECT PK_Department FROM Departments WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber)

  IF @ShowPrint = 1
    PRINT 'PacketItem_NEWRoomTypeXTab'
  DELETE PacketItem_NEWRoomTypeXTab WHERE RoomTypeNumber IN (SELECT RoomTypeNumber FROM RoomTypes WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber)
  DELETE PacketItem_NEWRoomTypeXTab WHERE PacketItemNumber NOT IN (SELECT DISTINCT PacketItemNumber FROM PacketItem_NewDepartmentXTab WITH (NOLOCK))
  -- ArrangementRoomXTab should already be deleted because the reservations is deleted

  IF @ShowPrint = 1
    PRINT 'PacketItem_NEW'
  DELETE PacketItem_NEW WHERE PacketItemNumber NOT IN (SELECT DISTINCT PacketItemNumber FROM PacketItem_NewDepartmentXTab WITH (NOLOCK))

  IF @ShowPrint = 1
    PRINT 'Packet'
  UPDATE Packet SET MarkAsDeleted = @CurDate WHERE PacketNumber NOT IN (SELECT DISTINCT PacketNumber FROM PacketItem_NEW WITH (NOLOCK))
  -- Delete Package END

  -- Delete Wares START
  IF @ShowPrint = 1
    PRINT 'WareDepartmentsXtab'
  DELETE WareDepartmentsXtab WHERE FK_Department IN (SELECT PK_Department FROM Departments WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber)
  
  UPDATE Departments
  SET DefaultPayment = NULL
  WHERE HotelNumber = @AHotelNumber  
    
  SELECT WareNumber
  INTO #tab_Ware
  FROM Ware WITH (NOLOCK)
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.Payment IN (0,1)  -- only ware and payments
  WHERE WareNumber NOT IN (SELECT DISTINCT WareNumber FROM WareDepartmentsXtab WITH (NOLOCK) WHERE FK_Department IN (SELECT PK_Department FROM Departments WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber))
  AND Ware.WareNumber NOT IN (SELECT DISTINCT Transactions.WareNumber FROM Transactions WITH (NOLOCK))
  AND Ware.WareNumber NOT IN (SELECT DISTINCT PacketItem_NEW.WareNumber FROM PacketItem_NEW WITH (NOLOCK))
  DECLARE db_ware CURSOR FOR
    SELECT WareNumber FROM #tab_Ware WITH (NOLOCK)
  OPEN db_ware
  
  FETCH NEXT FROM db_ware INTO @WareNumber
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @ShowPrint = 1
    BEGIN
      PRINT '@WareNumber'
      PRINT @WareNumber
      PRINT 'MenuWareXTab'
    END
    
    UPDATE Customer
    SET LedgerWareNumber = NULL
    WHERE LedgerWareNumber = @WareNumber
    
    DELETE MenuWare WHERE FK_MenuNumber = @WareNumber

    DELETE MenuWareXTab WHERE WareNumber = @WareNumber -- Hmm, hvad nu når enkelte varer i en menu vare er slettet, så giver %-sats ikke samlet set 100%

    IF @ShowPrint = 1
      PRINT 'MenuWareXTab'
    DELETE MenuWareXTab WHERE MenuNumber = @WareNumber
    
    IF @ShowPrint = 1
      PRINT 'OnlineBookTypeWareXtab'
    DELETE OnlineBookTypeWareXtab WHERE WareNumber = @WareNumber
    
    -- DELETE Recurring
    SELECT FK_RecurringPatternId
    INTO #tab_Recurring
    FROM RecurringPatternWareXtab WITH (NOLOCK)
    WHERE FK_WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'RecurringPatternWareXtab'
    DELETE RecurringPatternWareXtab WHERE FK_WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'RecurringPatternSpecified'
    DELETE RecurringPatternSpecified WHERE FK_RecurringPatternId IN (SELECT FK_RecurringPatternId FROM #tab_Recurring)

    IF @ShowPrint = 1
      PRINT 'RecurringRule'
    DELETE RecurringRule WHERE FK_RecurringPatternId IN (SELECT FK_RecurringPatternId FROM #tab_Recurring)

    IF @ShowPrint = 1
      PRINT 'RecurringRuleRecurringRuleXTab'
    DELETE RecurringRuleRecurringRuleXTab WHERE FK_RecurringRuleId1 IN (SELECT FK_RecurringPatternId FROM #tab_Recurring)

    IF @ShowPrint = 1
      PRINT 'RecurringRuleRecurringRuleXTab'
    DELETE RecurringRuleRecurringRuleXTab WHERE FK_RecurringRuleId2 IN (SELECT FK_RecurringPatternId FROM #tab_Recurring)

    IF @ShowPrint = 1
      PRINT 'RecurringPattern'
    DELETE RecurringPattern WHERE Id IN (SELECT FK_RecurringPatternId FROM #tab_Recurring)

    DROP TABLE #tab_Recurring
    
    -- Delete Stock        
    SELECT StockNumber
    INTO #tab_Stock
    FROM StockWareXTab WITH (NOLOCK)
    WHERE ((WareNumber = @WareNumber) OR (StockDepartment IN (SELECT DepartmentNumber FROM Departments WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber)))

    IF @ShowPrint = 1
      PRINT 'StockWareXTab'
    DELETE StockWareXTab WHERE StockNumber IN (SELECT StockNumber FROM #tab_Stock)

    IF @ShowPrint = 1
      PRINT 'Stock'
    DELETE Stock WHERE StockNumber IN (SELECT StockNumber FROM #tab_Stock)

    DROP TABLE #tab_Stock
    
    IF @ShowPrint = 1
      PRINT 'WareDaily'
    DELETE WareDaily WHERE WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'OnlineWareDelivery'
    DELETE OnlineWareDelivery WHERE WareNumber = @WareNumber
    
    -- DELETE ArrangementLines (ArrangementLines should already be deleted because the reservations is deleted)
    -- DELETE Transactions (Transactions should already be deleted in the above)
    -- DELETE Invoices (Invoices should already be deleted in the above)
    
    IF @ShowPrint = 1
      PRINT 'WareBillSortTypesXTab'
    DELETE WareBillSortTypesXTab WHERE WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'TopWareList 1'
    DELETE TopWareList WHERE FK_Department IN (SELECT PK_Department FROM Departments WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber)

    IF @ShowPrint = 1
      PRINT 'TopWareList 2'
    DELETE TopWareList WHERE WareNumber = @WareNumber
    
    /*
     Reservations and ReservationAddress -- If warenumber is used in PaymentTypeWareNumber/MethodOfPayment then PaymentTypeWareNumber is illegal 
     (reservation belongs on one hotel and ware belongs to another hotel)
     Has to clear the PaymentTypeWareNumber to be able to delete the ware
    */
    IF @ShowPrint = 1
      PRINT 'Update Reservations, ReservationAddress and Customer'

    UPDATE Reservations SET MethodOfPayment = NULL WHERE MethodOfPayment = @WareNumber
    UPDATE ReservationAddress SET PaymentTypeWareNumber = NULL WHERE PaymentTypeWareNumber = @WareNumber
    UPDATE Customer SET PaymentTypeWareNumber = NULL WHERE PaymentTypeWareNumber = @WareNumber
    
    IF @ShowPrint = 1
      PRINT 'CreditCardTypeWareXTab'
    DELETE CreditCardTypeWareXTab WHERE WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'WareHallXTab'
    DELETE WareHallXTab WHERE FK_WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'WarePrinterXtab'
    DELETE WarePrinterXtab WHERE WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'WareRoomTypeXTab'
    DELETE WareRoomTypeXTab WHERE FK_WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'WareRoomXTab'
    DELETE WareRoomXTab WHERE FK_WareNumber = @WareNumber

    IF @ShowPrint = 1
      PRINT 'WareWeekDayXTab'
    DELETE WareWeekDayXTab WHERE FK_WareNumber = @WareNumber
    
    IF @ShowPrint = 1
      PRINT 'Ware'
    DELETE Ware WHERE WareNumber = @WareNumber
    
    FETCH NEXT FROM db_ware INTO @WareNumber 
  END
  CLOSE db_ware
  DEALLOCATE db_ware 
  
  DROP TABLE #tab_Ware 
  -- Delete Wares END
  
  -- Delete WareGroups START
  IF @ShowPrint = 1
    PRINT 'WareGroupsDepartmentsXtab'
  DELETE WareGroupsDepartmentsXtab WHERE FK_Department IN (SELECT PK_Department FROM Departments WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber)
  
  SELECT WareGroupNumber
  INTO #tab_WareGroups
  FROM WareGroups WITH (NOLOCK)
  WHERE WareGroupNumber NOT IN (SELECT WareGroupNumber FROM WareGroupsDepartmentsXtab WITH (NOLOCK))
  AND WareGroups.WareGroupNumber NOT IN (SELECT DISTINCT Ware.WareGroupNumber FROM Ware WITH (NOLOCK))
  
  DECLARE db_waregroup CURSOR FOR
    SELECT WareGroupNumber FROM #tab_WareGroups WITH (NOLOCK)
  OPEN db_waregroup

  FETCH NEXT FROM db_waregroup INTO @WareGroupNumber
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @ShowPrint = 1
      PRINT 'SplitBillWareGroupXTab'
    DELETE SplitBillWareGroupXTab WHERE WaregroupNumber = @WareGroupNumber

    IF @ShowPrint = 1
      PRINT 'WareGroupBillSortTypesXTab'
    DELETE WareGroupBillSortTypesXTab WHERE WaregroupNumber = @WareGroupNumber

    IF @ShowPrint = 1
      PRINT 'TopWareList'
    DELETE TopWareList WHERE FK_WareGroupNumber = @WareGroupNumber

    IF @ShowPrint = 1
      PRINT 'WareGroups'
    DELETE WareGroups WHERE WareGroupNumber = @WareGroupNumber

    FETCH NEXT FROM db_waregroup INTO @WareGroupNumber
  END
  CLOSE db_waregroup
  
  DROP TABLE #tab_WareGroups
  -- Delete WareGroups END
  
  -- Delete Products START
  IF @ShowPrint = 1
    PRINT 'ProductPrice'
  DELETE ProductPrice WHERE RoomtypeNumber IN (SELECT RoomTypeNumber FROM RoomTypes WHERE HotelNumber = @AHotelNumber)

  IF @ShowPrint = 1
    PRINT 'Update ProductPrice'
  UPDATE ProductPrice SET ArrivalPacketNumber = NULL WHERE ArrivalPacketNumber IN (SELECT PacketNumber FROM Packet WITH (NOLOCK) WHERE MarkAsDeleted = @CurDate)
  UPDATE ProductPrice SET StayPacketNumber = NULL WHERE StayPacketNumber IN (SELECT PacketNumber FROM Packet WITH (NOLOCK) WHERE MarkAsDeleted = @CurDate)
  UPDATE ProductPrice SET DeparturePacketNumber = NULL WHERE DeparturePacketNumber IN (SELECT PacketNumber FROM Packet WITH (NOLOCK) WHERE MarkAsDeleted = @CurDate)

  IF @ShowPrint = 1
    PRINT 'ProductPrice'
  DELETE ProductPrice WHERE 
  ISNULL(AdultPrice1,0)=0 AND ISNULL(AdultPrice2,0)=0 AND ISNULL(AdultPrice3,0)=0 AND ISNULL(AdultPrice4,0)=0 AND ISNULL(AdultPrice5,0)=0 AND ISNULL(AdultPrice6,0)=0 AND ISNULL(AdultPrice7,0)=0 AND ISNULL(AdultPrice8,0)=0 AND ISNULL(AdultPrice9,0)=0
  AND ISNULL(Children1Price1,0)=0 AND ISNULL(Children1Price2,0)=0 AND ISNULL(Children1Price3,0)=0 AND ISNULL(Children1Price4,0)=0 AND ISNULL(Children1Price5,0)=0 AND ISNULL(Children1Price6,0)=0 AND ISNULL(Children1Price7,0)=0 AND ISNULL(Children1Price8,0)=0 AND ISNULL(Children1Price9,0)=0
  AND ISNULL(Children2Price1,0)=0 AND ISNULL(Children2Price2,0)=0 AND ISNULL(Children2Price3,0)=0 AND ISNULL(Children2Price4,0)=0 AND ISNULL(Children2Price5,0)=0 AND ISNULL(Children2Price6,0)=0 AND ISNULL(Children2Price7,0)=0 AND ISNULL(Children2Price8,0)=0 AND ISNULL(Children2Price9,0)=0

  AND ISNULL(AdultArrivalAddon,0)=0 AND ISNULL(AdultStayAddon,0)=0 AND ISNULL(AdultDepartureAddon,0)=0 
  AND ISNULL(AdultArrivalAddon2,0)=0 AND ISNULL(AdultStayAddon2,0)=0 AND ISNULL(AdultDepartureAddon2,0)=0
  AND ISNULL(AdultArrivalAddon3,0)=0 AND ISNULL(AdultStayAddon3,0)=0 AND ISNULL(AdultDepartureAddon3,0)=0
  AND ISNULL(AdultArrivalAddon4,0)=0 AND ISNULL(AdultStayAddon4,0)=0 AND ISNULL(AdultDepartureAddon4,0)=0
  AND ISNULL(AdultArrivalAddon5,0)=0 AND ISNULL(AdultStayAddon5,0)=0 AND ISNULL(AdultDepartureAddon5,0)=0
  AND ISNULL(AdultArrivalAddon6,0)=0 AND ISNULL(AdultStayAddon6,0)=0 AND ISNULL(AdultDepartureAddon6,0)=0
  AND ISNULL(AdultArrivalAddon7,0)=0 AND ISNULL(AdultStayAddon7,0)=0 AND ISNULL(AdultDepartureAddon7,0)=0
  AND ISNULL(AdultArrivalAddon8,0)=0 AND ISNULL(AdultStayAddon8,0)=0 AND ISNULL(AdultDepartureAddon8,0)=0
  AND ISNULL(AdultArrivalAddon9,0)=0 AND ISNULL(AdultStayAddon9,0)=0 AND ISNULL(AdultDepartureAddon9,0)=0

  AND ISNULL(Children1ArrivalAddon,0)=0 AND ISNULL(Children1StayAddon,0)=0 AND ISNULL(Children1DepartureAddon,0)=0
  AND ISNULL(Children1ArrivalAddon2,0)=0 AND ISNULL(Children1StayAddon2,0)=0 AND ISNULL(Children1DepartureAddon2,0)=0
  AND ISNULL(Children1ArrivalAddon3,0)=0 AND ISNULL(Children1StayAddon3,0)=0 AND ISNULL(Children1DepartureAddon3,0)=0
  AND ISNULL(Children1ArrivalAddon4,0)=0 AND ISNULL(Children1StayAddon4,0)=0 AND ISNULL(Children1DepartureAddon4,0)=0
  AND ISNULL(Children1ArrivalAddon5,0)=0 AND ISNULL(Children1StayAddon5,0)=0 AND ISNULL(Children1DepartureAddon5,0)=0
  AND ISNULL(Children1ArrivalAddon6,0)=0 AND ISNULL(Children1StayAddon6,0)=0 AND ISNULL(Children1DepartureAddon6,0)=0
  AND ISNULL(Children1ArrivalAddon7,0)=0 AND ISNULL(Children1StayAddon7,0)=0 AND ISNULL(Children1DepartureAddon7,0)=0
  AND ISNULL(Children1ArrivalAddon8,0)=0 AND ISNULL(Children1StayAddon8,0)=0 AND ISNULL(Children1DepartureAddon8,0)=0
  AND ISNULL(Children1ArrivalAddon9,0)=0 AND ISNULL(Children1StayAddon9,0)=0 AND ISNULL(Children1DepartureAddon9,0)=0
  
  AND ISNULL(Children2ArrivalAddon,0)=0 AND ISNULL(Children2StayAddon,0)=0 AND ISNULL(Children2DepartureAddon,0)=0
  AND ISNULL(Children2ArrivalAddon2,0)=0 AND ISNULL(Children2StayAddon2,0)=0 AND ISNULL(Children2DepartureAddon2,0)=0
  AND ISNULL(Children2ArrivalAddon3,0)=0 AND ISNULL(Children2StayAddon3,0)=0 AND ISNULL(Children2DepartureAddon3,0)=0
  AND ISNULL(Children2ArrivalAddon4,0)=0 AND ISNULL(Children2StayAddon4,0)=0 AND ISNULL(Children2DepartureAddon4,0)=0
  AND ISNULL(Children2ArrivalAddon5,0)=0 AND ISNULL(Children2StayAddon5,0)=0 AND ISNULL(Children2DepartureAddon5,0)=0
  AND ISNULL(Children2ArrivalAddon6,0)=0 AND ISNULL(Children2StayAddon6,0)=0 AND ISNULL(Children2DepartureAddon6,0)=0
  AND ISNULL(Children2ArrivalAddon7,0)=0 AND ISNULL(Children2StayAddon7,0)=0 AND ISNULL(Children2DepartureAddon7,0)=0
  AND ISNULL(Children2ArrivalAddon8,0)=0 AND ISNULL(Children2StayAddon8,0)=0 AND ISNULL(Children2DepartureAddon8,0)=0
  AND ISNULL(Children2ArrivalAddon9,0)=0 AND ISNULL(Children2StayAddon9,0)=0 AND ISNULL(Children2DepartureAddon9,0)=0

  AND ISNULL(ArrivalPacketNumber,0)=0 AND ISNULL(StayPacketNumber,0)=0 AND ISNULL(DeparturePacketNumber,0)=0

  SELECT ProductNumber
  INTO #tab_Products
  FROM Products WITH (NOLOCK)
  WHERE ProductNumber NOT IN (SELECT ProductNumber FROM ProductPrice WITH (NOLOCK))
  
  DECLARE db_products CURSOR FOR
    SELECT ProductNumber FROM #tab_Products
  OPEN db_products
  
  FETCH NEXT FROM db_products INTO @ProductNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- Reservations, ReservationRoomLine and ReservationConferenceLine should already be deleted
    IF @ShowPrint = 1
      PRINT 'OnlineBookTypeProductXTab'
    DELETE OnlineBookTypeProductXTab WHERE ProductNumber = @ProductNumber

    IF @ShowPrint = 1
      PRINT 'ProductMinimumAvailableRoomsXTab'
    DELETE ProductMinimumAvailableRoomsXTab

    IF @ShowPrint = 1
      PRINT 'ProductSegmentsXTab'
    DELETE ProductSegmentsXTab WHERE ProductNumber = @ProductNumber

    IF @ShowPrint = 1
      PRINT 'ProductPOSXTab'
    DELETE ProductPOSXTab WHERE ProductNumber = @ProductNumber
    
    IF @ShowPrint = 1
      PRINT 'ProductDescription'
    DELETE ProductDescription WHERE FK_ProductNumber = @ProductNumber

    IF @ShowPrint = 1
      PRINT 'ProductExtended'
    DELETE ProductExtended WHERE FK_ProductNumber = @ProductNumber

    IF @ShowPrint = 1
      PRINT 'ProductHotelXTAB'
    DELETE ProductHotelXTAB WHERE ProductNumber = @ProductNumber

    IF @ShowPrint = 1
      PRINT 'CustomerFieldsPerHotel'
    DELETE CustomerFieldsPerHotel WHERE 
      OvernightProductNumber1 = @ProductNumber OR OvernightProductNumber2 = @ProductNumber OR OvernightProductNumber3 = @ProductNumber OR OvernightProductNumber4 = @ProductNumber OR
      OvernightProductNumber5 = @ProductNumber OR OvernightProductNumber6 = @ProductNumber OR OvernightProductNumber7 = @ProductNumber OR OvernightProductNumber8 = @ProductNumber OR
      NextOvernightProductNumber1 = @ProductNumber OR NextOvernightProductNumber2 = @ProductNumber OR NextOvernightProductNumber3 = @ProductNumber OR NextOvernightProductNumber4 = @ProductNumber OR
      NextOvernightProductNumber5 = @ProductNumber OR NextOvernightProductNumber6 = @ProductNumber OR NextOvernightProductNumber7 = @ProductNumber OR NextOvernightProductNumber8 = @ProductNumber OR
      DayProductNumber1 = @ProductNumber OR DayProductNumber2 = @ProductNumber OR DayProductNumber3 = @ProductNumber OR DayProductNumber4 = @ProductNumber OR 
      DayProductNumber5 = @ProductNumber OR DayProductNumber6 = @ProductNumber OR DayProductNumber7 = @ProductNumber OR  DayProductNumber8 = @ProductNumber OR
      NextDayProductNumber1 = @ProductNumber OR NextDayProductNumber2 = @ProductNumber OR NextDayProductNumber3 = @ProductNumber OR NextDayProductNumber4 = @ProductNumber OR
      NextDayProductNumber5 = @ProductNumber OR NextDayProductNumber6 = @ProductNumber OR NextDayProductNumber7 = @ProductNumber OR NextDayProductNumber8 = @ProductNumber

    IF @ShowPrint = 1
      PRINT 'Final Products Delete'
    DELETE Products WHERE ProductNumber = @ProductNumber

    FETCH NEXT FROM db_products INTO @ProductNumber
  END
  CLOSE db_products
  DEALLOCATE db_products
  -- Delete Products END
  
  -- Final Packet Delete
  IF @ShowPrint = 1
    PRINT 'Packet'
  DELETE Packet WHERE MarkAsDeleted = @CurDate

  -- Additional START
  IF @ShowPrint = 1
    PRINT 'ForecastDaily'
  DELETE ForecastDaily WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'BudgetMonth'
  DELETE BudgetMonth WHERE HotelNumber = @AHotelNumber
  --DELETE TurnOverGroups WHERE HotelNumber = @AHotelNumber

  IF @ShowPrint = 1
    PRINT 'AdditionalSalesFromTurnover'
  DELETE AdditionalSalesFromTurnover WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'ObjectHotelXTab'
  DELETE ObjectHotelXTab WHERE HotelNumber = @AHotelNumber

  IF @ShowPrint = 1
    PRINT 'CustomerOrdreBook'
  DELETE CustomerOrdreBook WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'MISBudget'
  DELETE MISBudget WHERE HotelNumber = @AHotelNumber

  IF @ShowPrint = 1
    PRINT 'MISTable'
  DELETE MISTable WHERE HotelCustomerNumber = @HotelCustomerNumber

  IF @ShowPrint = 1
    PRINT 'RoomBookingStatusYield'
  DELETE RoomBookingStatusYield WHERE HotelNumber = @AHotelNumber

  IF @ShowPrint = 1
    PRINT 'RoomBookingStatusInsale'
  DELETE RoomBookingStatusInsale WHERE HotelNumber = @AHotelNumber

  IF @ShowPrint = 1
    PRINT 'RoomBookingStatus'
  DELETE RoomBookingStatus WHERE HotelNumber = @AHotelNumber
  -- Addtional END

  UPDATE Customer SET MarkAsDeleted = 1 WHERE HotelNumber = @AHotelNumber
  -- Handle for: no longer a localhotel present
  IF @LocalHotel = 1
  BEGIN
    SELECT TOP 1 @NewLocalHotel = CustomerNumber
    FROM Customer WITH(NOLOCK)
    WHERE CustomerTypeNumber = 7
    AND ISNULL(MarkAsDeleted, 0) = 0
    AND HotelNumber >= 0
    ORDER BY HotelNumber

    UPDATE Customer SET LocalHotel = 1, DeletedDate = @CurDate WHERE CustomerNumber = @NewLocalHotel
  END

  DROP TABLE #tab_FolioNumber

  EXEC EnableAuditLog 1
END
/*
-- Other tables with hotel relation
ConferenceRoomTypes
ROOMTYPES
ConferenceRooms
CardTypeTemplateXTab
DEPARTMENTS
SMSLog
UserXTabHotel
ROOMS
Templates

-- tables with Department relation
Accounts
AccountDepartmentXtab
PERSONEL
DEPARTMENTS
PersonelDepartmentsXtab
TableTypeDepXtab
*/