CREATE PROCEDURE [dbo].[GetIdentityInvoice] @TableName VARCHAR (30), @Increase INT, @Identity INT OUTPUT, @HotelCustomerNumber INT = -1, @PkDepartment INT = -1 AS
SET NOCOUNT ON
/*
  NB 20151207 Fix for Error checkout Bill Numer = -1
  NB 20150622 new CashIdentity type 
  KJ 20150528 Fix for "Error checkout. Bill number = -1"
  KJ 20150226 Fix for "Error checkout no bill number if running domino and not invoiceseries per hotel". Case #CR084242
  NB20150223 SepEcoByStaffDep single hotel in domino and on -1 rec Department fixed. 
  NB20141120 Rewritten and secured CR079559
  NB 20121030: Created

  Note: 
*/
DECLARE @RowCount INT, @PropertyCount INT
IF @TableName = 'CASHIDENTITY'
BEGIN
  UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
  WHERE DBTableName=@TableName AND HotelCustomerNumber=@HotelCustomerNumber AND FkDepartment= @PkDepartment
  SET @RowCount = @@ROWCOUNT
  IF @RowCount = 0
  BEGIN
    SET @Identity = 1
    INSERT INTO dbo.IdentityInvoice (DBTableName, HotelCustomerNumber, IdentityField, FkDepartment) 
	VALUES (@TableName, @HotelCustomerNumber, @Identity, @PkDepartment)
  END
END
ELSE
BEGIN
  DECLARE @SepEcoByStaffDep BIT, @InvoiceSeries BIT, @EnterInvoiceSeriesLogic BIT

  IF (@TableName = 'BILLIDENTITY' OR @TableName = 'INVOICEIDENTITY')
    SET @EnterInvoiceSeriesLogic = 1
  ELSE
    SET @EnterInvoiceSeriesLogic = 0
  
  SELECT @SepEcoByStaffDep = isnull(KeyValue, '0') FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'SeparateeconomyBystaffDep'
  IF @EnterInvoiceSeriesLogic = 0 OR @SepEcoByStaffDep = 1 and @PkDepartment = -1 
    SET @SepEcoByStaffDep = 0
  SET @SepEcoByStaffDep = ISNULL(@SepEcoByStaffDep, 0)

  IF @EnterInvoiceSeriesLogic = 1
    SELECT @InvoiceSeries = ISNULL(ISNULL(v.KeyValue, h.KeyValue), 0) FROM dbo.HotelSetup h WITH (NOLOCK) 
      LEFT JOIN dbo.HotelSetupValueXTab vx WITH (NOLOCK) ON vx.HotelSetupNumber = h.HotelSetupNumber
      LEFT JOIN dbo.HotelSetupValue v WITH (NOLOCK) ON vx.fk_ValueNumber = v.Pk_ValueNumber
    WHERE UPPER(h.SearchKey)=UPPER('UseInvoiceSeries')
  ELSE
    SET @InvoiceSeries = 0
  SET @InvoiceSeries = ISNULL(@InvoiceSeries, 0)

  SELECT @PropertyCount = Count(*) FROM dbo.Customer WITH (NOLOCK) WHERE CustomerTypeNumber = 7 AND (LocalHotel=1 OR HotelNumber >= 0) AND ISNULL(MarkAsDeleted, 0) = 0
  SET @PropertyCount = ISNULL(@PropertyCount, 1)

  /*
  IF @InvoiceSeries = 0 AND @SepEcoByStaffDep = 0 OR @PropertyCount = 1 AND (SELECT TOP 1 HotelCustomerNumber FROM dbo.IdentityInvoice WITH (NOLOCK) WHERE DBTableName = @TableName) = -1
    SET @HotelCustomerNumber = -1
  */
  IF @InvoiceSeries = 0 AND @SepEcoByStaffDep = 0 OR @PropertyCount = 1 
  BEGIN
    SET @HotelCustomerNumber = 0
    SELECT TOP 1 @HotelCustomerNumber = HotelCustomerNumber FROM dbo.IdentityInvoice WITH (NOLOCK) 
	WHERE DBTableName = @TableName AND HotelCustomerNumber = -1
	IF ISNULL(@HotelCustomerNumber, 0) = 0
      SELECT TOP 1 @HotelCustomerNumber = II.HotelCustomerNumber FROM dbo.IdentityInvoice II WITH (NOLOCK) 
	    LEFT JOIN Customer C WITH (NOLOCK) ON II.HotelCustomerNumber = C.CustomerNumber
      WHERE DBTableName = @TableName AND ISNULL(C.MarkAsDeleted, 0) = 0
      ORDER BY C.LocalHotel DESC, C.HotelNumber
  END
--PRINT @HotelCustomerNumber

  SET @Identity = -1

-- - - - - - Separate bill and invoice by department series - - - - - -
  IF (@SepEcoByStaffDep = 1) 
  BEGIN
    UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
    WHERE DBTableName=@TableName AND HotelCustomerNumber=@HotelCustomerNumber AND FkDepartment= @PkDepartment
    SET @RowCount = @@ROWCOUNT
  END
  ELSE
    SET @RowCount= 0

  IF (@HotelCustomerNumber > -1 or @InvoiceSeries = 0) AND (@SepEcoByStaffDep = 0 or @RowCount = 0)
  BEGIN
    UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
    WHERE DBTableName=@TableName AND HotelCustomerNumber = @HotelCustomerNumber AND ISNULL(FkDepartment, -1) = -1
    SET @RowCount = @@ROWCOUNT
  END
--ON Invoiceseries enabled but identity records / hotel not yet created (transition period fix) 
  IF @InvoiceSeries = 1 AND @RowCount=0
    IF (SELECT COUNT(*) FROM dbo.IdentityInvoice WITH (NOLOCK) WHERE DBTableName = @TableName) = 1 AND 
      (SELECT TOP 1 HotelCustomerNumber FROM dbo.IdentityInvoice WITH (NOLOCK) WHERE DBTableName = @TableName) = -1
    BEGIN
      UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase WHERE DBTableName=@TableName
      SET @RowCount = @@ROWCOUNT
    END
--ON Invoiceseries enabled Fix for HotelCustomerNumber = -1 on InvoiceSeries and identity records for each hotel exist then probability for localhotel is the one to be used most likely 
  IF @InvoiceSeries = 1 AND @RowCount=0
    IF (SELECT COUNT(*) FROM dbo.IdentityInvoice WITH (NOLOCK) WHERE DBTableName = @TableName AND HotelCustomerNumber > -1) = @PropertyCount 
    BEGIN
      UPDATE dbo.IdentityInvoice SET @Identity = IdentityField = IdentityField + @Increase 
      WHERE DBTableName=@TableName AND HotelCustomerNumber IN (
        SELECT TOP 1 II.HotelCustomerNumber FROM dbo.IdentityInvoice II WITH (NOLOCK) 
          INNER JOIN dbo.Customer C WITH (NOLOCK) ON II.HotelCustomerNumber = C.CustomerNumber
        WHERE DBTableName=@TableName AND C.LocalHotel=1 ORDER BY II.IdentityField DESC)
      SET @RowCount = @@ROWCOUNT
    END

  IF @RowCount>0
    SET @Identity = @Identity - @Increase + 1

--PRINT @HotelCustomerNumber
--PRINT @InvoiceSeries
--PRINT @SepEcoByStaffDep
--PRINT @RowCount
END