CREATE PROCEDURE DBO.CancelBill @ABillNumber INT, @AUserIdNumber INT, @AHotelCustomerNumber INT, @Error SMALLINT OUTPUT, @Option SMALLINT = 0 AS
SET NOCOUNT ON
/*
nb20120701 bug Safe GetHotelsetup KeyValue fixed
nb20110907 bug allow for techotel caceling bills owned by another hotel revided (depXtab can be out of date)
nb20081219 ext allow for techotel caceling bills owned by another hotel  
bn20070905 Bug AUserIdNumber is not the same as UserIdNumber and use all user departments to locate the bill info
nb20070903 Ext with FkDepartment 
NB20060524: @Option parameter added read about/extend it when needed 
NB20060311: Created

CancelBill  moves a existing writed bill from today to cancelled bill numbers and cleanup

NOTES:
@Option - possibility to cancel bill in other ways then normal
0- normal
1- also on another date then today
*/
DECLARE @BillNumber INT
, @DateStamp DATETIME
, @Now DATETIME
, @UserIdNumber INT
, @InvoiceNumber INT
, @HotelCustomerNumber INT
, @FolioNumber INT
, @LocalHotel BIT
, @CentralOfficeType SMALLINT
, @FkDepartment INT

SELECT @LocalHotel = LocalHotel FROM Customer WITH (NOLOCK) WHERE CustomerNumber = @AHotelCustomerNumber
IF ISNULL(@AHotelCustomerNumber, 0) > 0 AND ISNULL(@LocalHotel, 0) = 1
  SELECT @BillNumber = BillNumber, @DateStamp = DateTimeStamp, @UserIdNumber = UserIdNumber, @InvoiceNumber = InvoiceNumber, @HotelCustomerNumber = HotelCustomerNumber, @FkDepartment = FkDepartment 
  FROM Invoices WITH (NOLOCK) 
  WHERE BillNumber = @ABillNumber AND HotelCustomerNumber IN (-1, @AHotelCustomerNumber) AND (ISNULL(dbo.GetHotelSetupKeyValue ('SeparateEconomyByStaffDep', @AHotelCustomerNumber), '0') <> '1' or FkDepartment IN (
    select FK_Department from PersonelDepartmentsXtab PDX WITH (nolock) where PDX.UserIdNumber = @AUserIdNumber))
ELSE
  SELECT @BillNumber = BillNumber, @DateStamp = DateTimeStamp, @UserIdNumber = UserIdNumber, @InvoiceNumber = InvoiceNumber, @HotelCustomerNumber = HotelCustomerNumber, @FkDepartment = FkDepartment  
  FROM Invoices WITH (NOLOCK) 
  WHERE BillNumber = @ABillNumber AND HotelCustomerNumber= @AHotelCustomerNumber AND (ISNULL(dbo.GetHotelSetupKeyValue ('SeparateEconomyByStaffDep', @AHotelCustomerNumber), '0') <> '1' or FkDepartment IN (
    select FK_Department from PersonelDepartmentsXtab PDX WITH (nolock) where PDX.UserIdNumber = @AUserIdNumber))

--nb20081219 allow for techotel caceling bills owned by another hotel  
IF ISNULL(@BillNumber, 0) = 0 AND @Option = 1 
  SELECT TOP 1 @BillNumber = BillNumber, @DateStamp = DateTimeStamp, @UserIdNumber = UserIdNumber, @InvoiceNumber = InvoiceNumber, @HotelCustomerNumber = HotelCustomerNumber, @FkDepartment = FkDepartment  
  FROM Invoices WITH (NOLOCK) 
  WHERE BillNumber = @ABillNumber 

IF ISNULL(@BillNumber, 0) > 0
BEGIN
  SELECT @Now = SYSTEMDATE FROM [SYSTEM] WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record
  SET @Now = CONVERT(DATETIME,@Now+' '+CONVERT(CHAR(8), CURRENT_TIMESTAMP,108)) -- endtil videre
  --IF CONVERT(VARCHAR, @Now, 112) = CONVERT(VARCHAR, @DateStamp, 112)
  --nb20060524 New Option parameter introduced 
  IF (@Option = 1) OR (CONVERT(VARCHAR, @Now, 112) = CONVERT(VARCHAR, @DateStamp, 112))

  BEGIN
    SELECT TOP 1 @FolioNumber = Folios.FolioNumber FROM Folios WITH (NOLOCK) 
      INNER JOIN Transactions WITH (NOLOCK) ON Transactions.FolioNumber = Folios.FolioNumber 
    WHERE Transactions.InvoiceBillNumber = @BillNumber   
    UPDATE Folios SET OpenFolio = 1 WHERE FolioNumber = @FolioNumber AND OpenFolio = 0
    IF (SELECT TOP 1 ISNULL(KeyValue, 7) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey LIKE '%CentralBookingType%') = 7
    BEGIN
      UPDATE Transactions SET InvoiceBillNumber=NULL WHERE InvoiceBillNumber = @BillNumber
      DELETE FROM FolioBillXtab WHERE BillNumber = @BillNumber AND HotelCustomerNumber = @HotelCustomerNumber
      DELETE FROM Invoices WHERE BillNumber = @BillNumber AND HotelCustomerNumber = @HotelCustomerNumber  
      INSERT INTO CanceledBillNumbers (BillNumber, DateTimeStamp, UserIdNumber, InvoiceNumber, HotelCustomerNumber, FkDepartment) 
        VALUES (@BillNumber, @Now, @AUserIdNumber, @InvoiceNumber, @HotelCustomerNumber, @FkDepartment)
    END
    ELSE
    BEGIN
      UPDATE Transactions SET InvoiceBillNumber=NULL WHERE InvoiceBillNumber = @BillNumber
      DELETE FROM FolioBillXtab WHERE BillNumber = @BillNumber
      DELETE FROM Invoices WHERE BillNumber = @BillNumber
      INSERT INTO CanceledBillNumbers (BillNumber, DateTimeStamp, UserIdNumber, InvoiceNumber, FkDepartment) 
        VALUES (@BillNumber, @Now, @AUserIdNumber, @InvoiceNumber, @FkDepartment)
    END
    SET @Error = 0
  END
  ELSE
    SET @Error = 2
END
ELSE 
  SET @Error = 1
