aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN (DEFAULT NULL)
folio_id	integer	IN (DEFAULT NULL)
register_ids	int4[]	IN (DEFAULT NULL)
folio_ids	int4[]	IN (DEFAULT NULL)
early_checkout_reason	text	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    system_date date = fn_system_date();
    close_result json;
    _early_checkout bool;
    bk_folio_id int;
    bk_departure date;
    _ooo_status char(3);
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    reg record;
    --is_last_room boolean = FALSE;
    log_title TEXT;
    log_msg TEXT;
    messages    jsonb = $${
        "20301": "Early checkout?",
        "20302": "Charge schedule not posted.",
        "20303": "Folio out of balance."
    }$$;
   
   
    v_check_charge_schedult record;
    v_post_date date;
BEGIN
    CALL sp_check_endday_process();
    CALL sp_check_record_lock(user_name, register_id := $1);
    
    user_name = coalesce(user_name, fn_current_user());

    IF folio_id IS NOT NULL THEN 
        folio_ids = folio_ids || folio_id;
    END IF;
    
    IF register_id IS NOT NULL THEN 
        folio_ids = folio_ids || (SELECT id FROM folio f WHERE f.register_id = $1);
    END IF; 

    IF register_ids IS NOT NULL THEN 
        folio_ids = folio_ids || (SELECT id FROM folio f WHERE f.register_id = ANY(register_ids));
    END IF;

    -- Check out guests
    FOR reg IN 
        SELECT f.id AS folio_id, f.folio_type, f.register_id, rg.booking_id, rg.status, rg.room_id, rg.guest_id, rm.room_number, 
               IIF(f.folio_type='B', b.arrival, rg.arrival) AS arrival, 
               IIF(f.folio_type='B', b.departure, rg.departure) AS departure
        FROM folio f 
           LEFT JOIN registration rg ON rg.id = f.register_id 
           LEFT JOIN room rm ON rm.id = rg.room_id 
           LEFT JOIN booking b ON b.id = f.booking_id
        WHERE f.id = ANY(folio_ids)
        ORDER BY f.folio_type DESC, f.id
    LOOP 
        CALL sp_check_folio(reg.folio_id);
 
        /* Check folio balances */
        IF EXISTS(SELECT * FROM folio_items fi WHERE fi.folio_id = reg.folio_id AND NOT fi.closed AND fi.balance <> 0) THEN 
            PERFORM sp_raise_error('20303', messages->>'20303');
        END IF;
       
       
      --  CALL sp_check_charge_schedule(reg.register_id);
            
        _early_checkout = reg.departure > system_date;
    
--        IF _early_checkout AND NOT force_early_checkout THEN
--          RETURN fn_result_confirm('20301', messages->>'20301');
--        END IF;
        
        --TODO: Check utilty charges
        --TODO: Check tax invoice issuing
       
        /* Check auto-post room charge */
        --IF EXISTS(SELECT * FROM sp_get_charge_schedule(folio_id => reg.folio_id, posted => FALSE, post_date => system_date)) THEN 
--        IF EXISTS(SELECT * FROM charge_schedule cs 
--                  WHERE cs.register_id = reg.register_id AND cs.charge_date = system_date AND NOT cs.posted) 
--        THEN
--          PERFORM sp_raise_error('20302', messages->>'20302');
--        END IF;
    
        /* Check auto-post other charges */
        -- TODO: 
       
    
        close_result = sp_close_folio(reg.folio_id, NULL, user_name, NULL, FALSE);
    
        IF close_result->>'result_code' = '4' THEN 
           PERFORM sp_raise_error(close_result);
        END IF;
        
 
        IF (reg.folio_type = 'R') THEN 
            UPDATE registration rg 
            SET status = 'O',
                departure = system_date,
                departure_time = current_time::time,
                checkout_time = current_timestamp,
                checkout_user = user_name,
                early_checkout_date = IIF(_early_checkout, rg.departure, NULL), 
                early_checkout = _early_checkout
            WHERE rg.id = reg.register_id;
        
            /* update room status, if no other guest stay in this room */
            IF NOT EXISTS(SELECT id FROM registration rg 
                          WHERE rg.id <> reg.register_id AND rg.room_id = reg.room_id AND rg.status='I') 
            THEN 
                _ooo_status = (SELECT oo.ooo_type FROM out_of_order oo
                               WHERE oo.room_id =reg.room_id AND oo.start_date = system_date
                               LIMIT 1);
                
                UPDATE room_status rs 
                SET status = COALESCE(_ooo_status, 'VAC'), 
                    hk_status = fn_get_param('ROOM_STATUS','CHECKOUT_STATUS', 'DI') 
                WHERE rs.room_id = reg.room_id; 
            END IF;
        
        --ELSEIF (reg.folio_type = 'B') THEN 
        
        END IF;
       
        /* reset other folios that charge to this folio */
        UPDATE folio_items fi
        SET charge_to_folio = NULL
        WHERE fi.charge_to_folio = reg.folio_id; 
    
        log_title := IIF(_early_checkout, 'Early check out', 'Check out');
        log_msg   := CASE _early_checkout
                        WHEN TRUE THEN 'Old departure: '||reg.departure::TEXT||', Reason: '||COALESCE(early_checkout_reason,'')
                        ELSE NULL 
                     END; 
    
        IF reg.folio_type = 'R' THEN
        
            /* Do booking checkout */
            --IF (reg.folio_type = 'R') 
            IF NOT EXISTS(SELECT * FROM registration r WHERE r.booking_id = reg.booking_id AND r.status='I') 
               AND NOT EXISTS(SELECT * FROM booking_items r WHERE r.booking_id = reg.booking_id and (qty-inhouse_qty) > 0) 
            THEN 
                UPDATE booking b 
                SET status = 'O', departure_time = current_time
                WHERE b.id = reg.booking_id AND b.status = 'I';
                PERFORM sp_log_booking(reg.booking_id, NULL, log_title, log_msg, user_name); 
            END IF;

            PERFORM sp_update_guest_stat(reg.register_id);

            PERFORM sp_log_inhouse(reg.register_id, log_title, log_msg, user_name); 

            IF is_interface_active('MSG-CHECKOUT') THEN
                PERFORM sp_send_message_to_guest(reg.guest_id, 
                    msg_template => 'guest-thankyou2', 
                    params       => jsonb_build_object(
                        'booking_id' , reg.booking_id,
                        'register_id', reg.register_id,
                        'room_number', reg.room_number,
                        'arrival'    , reg.arrival,
                        'departure'  , reg.departure
                    )
                );
            END IF;
        END IF;
         
    END LOOP;
    
    RETURN fn_result_success();
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT; 
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_check_out', 
        jsonb_build_object(
            'register_id', register_id,
            'folio_id', folio_id,
            'register_ids', register_ids,
            'folio_ids', folio_ids,
            'early_checkout_reason', early_checkout_reason
        )
    );
END

sp_check_out

Description

Parameters

Definition