CREATE PROCEDURE [dbo].[UpdateCustomerAddressNoCredit] @ACustomerNumber INT, @AValue BIT, @Customer BIT, @FutureReservations BIT, @InhouseReservations BIT, @HotelCustomerNumber INT AS
/*
  Changes:
    NB20140728 Created
  Function:
    Used to update PaymentTypeWareNumber field on Customer and ReservationAddress tables with "No Credit".
    @Customer = 1: Perform Update on Customer
    @FutureReservations = 1: Perform Update on Future reservations and Invoices
    @InhouseReservations = 1: Perform Update on Inhouse reservations only 
    @AValue = switch 0/1: when 0 clear the Payment; when 1 find the first ptNoCredit payment defined on hotel and update with it the chosen options.
*/

IF @ACustomerNumber > 0 AND (@AValue = 0 OR ISNULL(@HotelCustomerNumber, 0) > 0)
BEGIN
  DECLARE @WareNumber INT
  IF @AValue = 1
    SELECT TOP 1 @WareNumber = W1.WareNumber FROM dbo.Ware W1 WITH (NOLOCK) 
      INNER JOIN WareDepartmentsXtab WDX WITH (NOLOCK) on WDX.WareNumber = W1.WareNumber AND WDX.Type=1 --Cashier departments only
      INNER JOIN DEPARTMENTS D WITH (NOLOCK) on WDX.FK_Department = D.PK_Department 
	  INNER JOIN Customer C1 WITH (NOLOCK) ON C1.HotelNumber = D.HotelNumber AND C1.CustomerNumber = ISNULL(@HotelCustomerNumber, 0)
    WHERE W1.Payment = 20 AND W1.MarkAsDeleted IS NULL 
  ELSE
    SET @WareNumber = NULL
  IF @AValue = 0 OR ISNULL(@WareNumber, 0) > 0
  BEGIN  
    IF @Customer = 1 
      UPDATE Customer SET PaymentTypeWareNumber = @WareNumber WHERE CustomerNumber = @ACustomerNumber 
    IF @FutureReservations = 1 OR @InhouseReservations = 1
      IF @FutureReservations = 1 AND @InhouseReservations = 1
        UPDATE ReservationAddress SET PaymentTypeWareNumber = @WareNumber 
		WHERE AddressNumber IN (
	      SELECT RA.AddressNumber FROM ReservationAddress RA WITH (NOLOCK) 
            LEFT JOIN ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.AddressNumber = RA.AddressNumber
            LEFT JOIN Reservations R1 WITH (NOLOCK) ON RAX.ReferenceNumber = R1.ReferenceNumber AND RAX.GuestNumber = R1.GuestNumber
            LEFT JOIN Folios F1 WITH (NOLOCK) ON F1.ReservationAddressNumber = RA.AddressNumber 
          WHERE RA.LINK = @ACustomerNumber AND (R1.Status < 6 OR R1.Status IS NULL AND F1.OpenFolio = 1)
		)
	  ELSE IF @FutureReservations = 1 
        UPDATE ReservationAddress SET PaymentTypeWareNumber = @WareNumber 
		WHERE AddressNumber IN (
	      SELECT RA.AddressNumber FROM ReservationAddress RA WITH (NOLOCK) 
            LEFT JOIN ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.AddressNumber = RA.AddressNumber
            LEFT JOIN Reservations R1 WITH (NOLOCK) ON RAX.ReferenceNumber = R1.ReferenceNumber AND RAX.GuestNumber = R1.GuestNumber
          WHERE RA.LINK = @ACustomerNumber AND R1.Status < 5 
		)
	  ELSE
        UPDATE ReservationAddress SET PaymentTypeWareNumber = @WareNumber 
		WHERE AddressNumber IN (
	      SELECT RA.AddressNumber FROM ReservationAddress RA WITH (NOLOCK) 
            LEFT JOIN ReservationAddressXTab RAX WITH (NOLOCK) ON RAX.AddressNumber = RA.AddressNumber
            LEFT JOIN Reservations R1 WITH (NOLOCK) ON RAX.ReferenceNumber = R1.ReferenceNumber AND RAX.GuestNumber = R1.GuestNumber
            LEFT JOIN Folios F1 WITH (NOLOCK) ON F1.ReservationAddressNumber = RA.AddressNumber
          WHERE RA.LINK = @ACustomerNumber AND (R1.Status = 5 OR R1.Status IS NULL AND F1.OpenFolio = 1)
		)
  END
END 
  