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 (ReservationAttachmentXTab relation)
  Changes:
    KJ 20210921: Bug: Fix for "Possible to delete transactions even if folios exists with postings from more than one hotel"
    KJ 20210202: Bug: Fix for "Invalid object name '#tab_Products'".
    KJ 20210115: Added delete of Rate channels
    KJ 20210112: Added delete of ReportAutomation. Case #CR203632
    KJ 20201203: Added yet more missing tables
    KJ 20201202: Optimized delete of Reservation address, arrangement linies and more. Added missing tables
    KJ 20201201: Added TransactionOnlinePaymentXtab
    AMH 20200526: Added missing tables.
    KJ 20191212: Bug: Make sure that triggers are enabled after run
    KJ 20170515: Bug: Fix for "all records in InvoiceTransactionXTab is deleted regardless of what hotel number is deleted".
    KJ 20170510: Bug: Fix for "To many wares is deleted"
    KJ 20170504: Added missing table.
    KJ 20170501: Added missing tables.
    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 20131014: ProductPrice Rewrite.
    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 ISNULL(@cnt, 0) > 0
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
  BEGIN TRY
    IF @ShowPrint = 1
      PRINT 'FolioCardXTab'
    DELETE FolioCardXTab 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 '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 'InvoiceTransactionXTab'
    DELETE InvoiceTransactionXTab
    WHERE FkInvoice IN (SELECT PkInvoice FROM Invoice WITH (NOLOCK) WHERE FkCustomerHotel = @HotelCustomerNumber)

    IF @ShowPrint = 1
      PRINT 'ReceiptFiscal'
    DELETE ReceiptFiscal WHERE FkInvoice IN (SELECT PkInvoice FROM Invoice WITH (NOLOCK) WHERE FkCustomerHotel = @HotelCustomerNumber)
  
    IF @ShowPrint = 1
      PRINT 'DebtorAccounting'
    DELETE DebtorAccounting WHERE FK_Invoice IN (SELECT PkInvoice FROM Invoice WITH (NOLOCK) WHERE FkCustomerHotel = @HotelCustomerNumber)

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

    IF @ShowPrint = 1
      PRINT 'InvoiceTransactionXTab'
    DELETE InvoiceTransactionXTab WHERE FkTransaction IN (SELECT TransactionNumber FROM Transactions WITH(NOLOCK) 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 'TransactionOnlinePaymentXtab'
    DELETE TransactionOnlinePaymentXtab 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
    SELECT AddressNumber 
    INTO #tab_AddressNumber
    FROM ReservationAddressXTab WITH(NOLOCK) 
    WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)
    IF @ShowPrint = 1
      PRINT 'ReservationAddressXTab'
    DELETE FROM ReservationAddressXTab WHERE AddressNumber IN (SELECT AddressNumber FROM #tab_AddressNumber)

    IF @ShowPrint = 1
      PRINT 'ReservationAddress'
    DELETE FROM ReservationAddress WHERE AddressNumber IN (SELECT AddressNumber FROM #tab_AddressNumber)
    DROP TABLE #tab_AddressNumber

    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

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

    /* Delete ReservationChecklist */ 
    SELECT FK_CheckItem
    INTO #tab_CheckItem
    FROM ReservationCheckList WITH (NOLOCK) 
    WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

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

    IF @ShowPrint = 1
      PRINT 'CheckItem'
    DELETE FROM CheckItem WHERE PK_CheckItem IN (SELECT FK_CheckItem FROM #tab_CheckItem)
    DROP TABLE #tab_CheckItem  

    /* Delete Reservation External Xref */ 
    DECLARE #DeleteCur CURSOR STATIC FOR
      SELECT FK_ExternalRefID
      FROM ReservationExternalRefXtab WITH (NOLOCK) 
      WHERE FK_ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)
    OPEN #DeleteCur

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

    FETCH NEXT FROM #DeleteCur
    INTO @PrimaryKey

    IF @ShowPrint = 1
      PRINT 'ExternalReference'
    WHILE @@FETCH_STATUS <> -1
    BEGIN
      WITH ExtRef(ID, ParentID, Value, Qualifier) AS
      (
        SELECT ID, ParentID, VALUE, Qualifier
        FROM ExternalReference WITH (NOLOCK)
        Where ID = @PrimaryKey
        UNION ALL
        Select E.ID, E.ParentID, E.Value, E.Qualifier
        FROM ExternalReference E WITH (NOLOCK)
        INNER JOIN ExtRef ER On E.ParentID = ER.ID    
      )
      DELETE ExternalReference FROM ExternalReference
      INNER JOIN ExtRef ON ExternalReference.ID = ExtRef.ID 
      
      FETCH NEXT FROM #DeleteCur
      INTO @PrimaryKey
    END

    CLOSE #DeleteCur
    DEALLOCATE #DeleteCUR

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

    -- Arrangement
    IF @ShowPrint = 1
      PRINT 'Arrangement BEGIN'
    SELECT ArrangementLineNumber 
    INTO #tab_Arrangement
    FROM ArrangementReservationsXtab WITH(NOLOCK) 
    WHERE ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

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

    IF @ShowPrint = 1
      PRINT 'ArrangementRoomXtab'
    DELETE FROM ArrangementRoomXtab WHERE ArrangementLineNumber IN (SELECT ArrangementLineNumber FROM #tab_Arrangement)

    IF @ShowPrint = 1
      PRINT 'ArrangementLineDepartmentXTab'
    DELETE FROM ArrangementLineDepartmentXTab WHERE FK_ArrangementLineNumber IN (SELECT ArrangementLineNumber FROM #tab_Arrangement)

    IF @ShowPrint = 1
      PRINT 'ArrangementReservationConferenceLineXTab'
    DELETE FROM ArrangementReservationConferenceLineXTab WHERE ArrangementLineNumber IN (SELECT ArrangementLineNumber FROM #tab_Arrangement)

    IF @ShowPrint = 1
      PRINT 'ArrangementLines'
    DELETE FROM ArrangementLines WHERE ArrangementLineNumber IN (SELECT ArrangementLineNumber FROM #tab_Arrangement)

    DROP TABLE #tab_Arrangement

    IF @ShowPrint = 1
      PRINT 'Arrangement DONE'

    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 'ResvConfLineConfRmStatusXtab'
    DELETE ResvConfLineConfRmStatusXtab WHERE FK_ReservationConferenceLineNumber IN (SELECT ReservationConfLineNumber FROM ReservationConferenceLine WITH (NOLOCK) 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 'ReservationsExt'
    DELETE ReservationsExt WHERE FK_ReferenceNumber IN (SELECT ReferenceNumber FROM ReservationMain WITH(NOLOCK) WHERE HotelNumber = @AHotelNumber AND ReferenceNumber > 0)

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

    IF @ShowPrint = 1
      PRINT 'ReservationSendLog'
    DELETE ReservationSendLog WHERE FK_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 'ReservationOnlinePaymentXtab'
    DELETE ReservationOnlinePaymentXtab WHERE FK_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 'CustomerAllotmentsXTab'
    DELETE CustomerAllotmentsXTab WHERE HotelNumber = @AHotelNumber

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

    IF @ShowPrint = 1
      PRINT 'AllotmentLines'
    DELETE AllotmentLines 
    FROM AllotmentLines al
    INNER JOIN Roomtypes rt ON al.RoomTypeNumber = rt.RoomTypeNumber and rt.HotelNumber = @AHotelNumber
 
    IF @ShowPrint = 1
      PRINT 'AllotmentHotelXTAB'
    DELETE AllotmentHotelXTAB WHERE HotelCustomerNumber = @HotelCustomerNumber	

    IF @ShowPrint = 1
      PRINT 'AllotmentHotelNote'
    DELETE AllotmentHotelNote WHERE FK_HotelCustomerNumber = @HotelCustomerNumber	

/*
    DELETE Allotments 
    FROM Allotments A
    LEFT OUTER JOIN AllotmentHotelXTAB AHX ON A.AllotmentNumber = AHX.AllotmentNumber
    WHERE AHX.AllotmentNumber IS NULL
*/ -- KJ 20201203: Illegal reservations exists where allotment is set on reservations belonging to other hotel than the allotment

    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))

    IF @ShowPrint = 1
      PRINT 'ProductPricePacket'
    DELETE ProductPricePacket WHERE FK_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))
    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

      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'

    DELETE ProductPricePacket WHERE FK_PacketNumber 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

    IF @ShowPrint = 1
      PRINT 'ProductMinimumAvailableRoomsXTab'
    DELETE ProductMinimumAvailableRoomsXTab

    SELECT ProductNumber
    INTO #tab_Products
    FROM Products WITH (NOLOCK)
    WHERE ProductNumber NOT IN (SELECT ProductNumber FROM ProductPrice WITH (NOLOCK))
  
    UPDATE CustomerOTA SET FK_DefaultProductNumber = (SELECT TOP 1 ProductNumber FROM Products WHERE ProductNumber NOT IN (SELECT ProductNumber FROM #tab_Products))

    IF @ShowPrint = 1
      PRINT 'OnlineBookTypeProductXTab'
    DELETE OnlineBookTypeProductXTab WHERE ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'ProductSegmentsXTab'
    DELETE ProductSegmentsXTab WHERE ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'ProductPOSXTab'
    DELETE ProductPOSXTab WHERE ProductNumber IN (SELECT ProductNumber FROM #tab_Products)
    
    IF @ShowPrint = 1
      PRINT 'ProductDescription'
    DELETE ProductDescription WHERE FK_ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'ProductExtended'
    DELETE ProductExtended WHERE FK_ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'ProductHotelXTAB'
    DELETE ProductHotelXTAB WHERE ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'ProductColorXTAB'
    DELETE ProductColorXTAB WHERE ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'ProductPricePacket'
    DELETE ProductPricePacket WHERE FK_ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'ProductsImagesXTab'
    DELETE ProductsImagesXTab WHERE ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'RoomTypeDifferentialProductsXTab'
    DELETE RoomTypeDifferentialProductsXTab WHERE FKProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    IF @ShowPrint = 1
      PRINT 'RoomTypeDifferentialItem'
    DELETE RoomTypeDifferentialItem WHERE FK_ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

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

    IF @ShowPrint = 1
      PRINT 'Final Products Delete'
    DELETE Products WHERE ProductNumber IN (SELECT ProductNumber FROM #tab_Products)

    DROP TABLE #tab_Products
    -- Delete Products END
  
    -- Final Packet Delete
    IF @ShowPrint = 1
      PRINT 'Packet'
    DELETE Packet WHERE MarkAsDeleted = @CurDate

    -- Additional START
    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

    DELETE ReportAutomationCustomerXTab WHERE FK_CustomerNumber = @HotelCustomerNumber

    DELETE ReportAutomationLog WHERE FK_ReportAutomationId NOT IN (
      SELECT FK_ReportAutomationId FROM ReportAutomationCustomerXTab (NOLOCK)
    )
    DELETE LogPicasso WHERE LogNumber IN (
      SELECT FK_LogPicassoId FROM ReportAutomationLog WHERE FK_ReportAutomationId NOT IN (
        SELECT FK_ReportAutomationId FROM ReportAutomationCustomerXTab (NOLOCK)
      )
    )
    DELETE ReportAutomation WHERE PK_ReportAutomationId NOT IN (
      SELECT FK_ReportAutomationId FROM ReportAutomationCustomerXTab (NOLOCK)
    )
    -- Addtional END

    -- Delete Rate channel
    DELETE RateLevelChannelCustomerXTab WHERE FKCustomerNumber = @HotelCustomerNumber

    SELECT DISTINCT RateLevelChannelId
    INTO #tmpRateLevelChannel
    FROM RateLevelChannel WITH (NOLOCK) 
    WHERE RateLevelChannelId NOT IN (
      SELECT FKRateLevelChannelId FROM RateLevelChannelCustomerXTab WITH (NOLOCK)
    )

    DELETE OTAInvCountNotifLog WHERE FKRateLevelChannelId IN (SELECT RateLevelChannelId FROM #tmpRateLevelChannel)
    DELETE RateLevelRestrictionRateChannelXTab WHERE FKRateLevelChannelId IN (SELECT RateLevelChannelId FROM #tmpRateLevelChannel)
    DELETE RateLevelChannelRateLevelXTab WHERE FKRateLevelChannelId IN (SELECT RateLevelChannelId FROM #tmpRateLevelChannel)
    DELETE RateLevelChannelCustomerXTab WHERE FKRateLevelChannelId IN (SELECT RateLevelChannelId FROM #tmpRateLevelChannel)
    DELETE RateLevelChannel WHERE RateLevelChannelId IN (SELECT RateLevelChannelId FROM #tmpRateLevelChannel)

    SELECT DISTINCT RateLevelId
    INTO #tmpRateLevel
    FROM RateLevel WITH (NOLOCK)
    WHERE RateLevelId NOT IN (
      SELECT FKRateLevelId FROM RateLevelChannelRateLevelXTab WITH (NOLOCK)
    )

    DELETE RateLevelRoomTypeRestrictionXTab
    WHERE FKRateLevelRoomTypeXTabId IN (
      SELECT Id
      FROM RateLevelRoomTypeXTab WITH (NOLOCK)
      WHERE FKRateLevelId IN (SELECT RateLevelId FROM #tmpRateLevel WITH (NOLOCK))
    )

    DELETE RateLevelRoomTypeXTab WHERE FKRateLevelId IN (SELECT RateLevelId FROM #tmpRateLevel WITH (NOLOCK))
    DELETE RateLevel WHERE RateLevelId IN (SELECT RateLevelId FROM #tmpRateLevel WITH (NOLOCK))

    DROP TABLE #tmpRateLevel
    DROP TABLE #tmpRateLevelChannel
    -- Delete rate channel 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 TRY
  BEGIN CATCH
    EXEC EnableAuditLog 1

    DECLARE @ErrorMessage NVARCHAR(MAX)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
  END CATCH
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
*/