CREATE PROCEDURE [dbo].[GPIGetHotelSetupData](@SearchKey VARCHAR(60), @HotelNumber INTEGER, @DefaultValue VARCHAR(768),  @Result VARCHAR(768) OUT)
 AS
BEGIN 
/*
	Created 20180925 By Carsten Cremer.
	Purpose : Return a value from the HotelSetuo, either as definded per hotel or the general value common for all hotels.
	          if the key does not exists yet, create a new key, and insert the default value into it.

   CC 20200609 Adapted to change of logic using GPISetHotelSetupdata instead of a straight insert on a missing key thus enabling a new key
				   to be added straight into a hotel specific key.
		
	TestBlock:
	DECLARE @RESULT VarChar(768);
	SET @Result = 'EMPTY';
	EXECUTE GPIGetHotelSetupData 'Interface.DummyInterface',0,'DummyData', @Result OUT
	PRINT @Result;
*/

  DECLARE @HotelCustomerNumber integer;
  DECLARE @lRowCount integer;             -- Don't know if the volatility of @@rowcount is a bug or feature in T-SQL but without this QDF th e logic collapses 
  DECLARE @SetResult BIT;
  
  SET     @SetResult =0;	
  SET     @Result = @DefaultValue;        -- In case no record is found this is what is going to be inserted and returned
  SET     @HotelCustomerNumber = ISNULL( (SELECT CustomerNumber FROM Customer WITH(NOLOCK) WHERE ( (CustomerTypeNumber = 7) AND (HotelNumber = @HotelNumber) AND ISNULL( MARKASDELETED, 0) = 0)) , 0);
  SET	  @lRowCount = 0;
  
  IF @HotelCustomerNumber > 0
  BEGIN
    SELECT @Result = KeyValue FROM HotelSetupValue WITH (NOLOCK) INNER JOIN HotelSetupValueXTab WITH (NOLOCK) 
	ON HotelSetupValue.PK_ValueNumber = HotelSetupValueXTab.FK_ValueNumber AND
	HotelSetupValueXTab.CustomerNumber = @HotelCustomerNumber AND HotelSetupValueXTab.HotelSetupNumber = (SELECT HotelSetupNumber FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE @SearchKey)
    SET @lRowCount = @@rowcount;
  END

  IF (@lRowCount = 0) OR (@HotelCustomerNumber < 1)
  BEGIN
   SELECT @Result = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE @SearchKey 
   SET @lRowCount = @@rowcount;
  END

  IF (@lRowCount = 0) 
--    INSERT INTO HotelSetup (SearchKey, KeyValue) VALUES (@SearchKey, @DefaultValue ); -- CC 20200609
    EXECUTE GPISetHotelSetupData @SearchKey, @HotelNumber,@DefaultValue, @SetResult OUT -- CC 20200609

 --PRINT  'Result of insert :' + CAST( @SetResult AS VARCHAR(10));
 --PRINT  @Result
END
