aonestar.public

Parameters

Name	Type	Mode
i_register_id	integer	IN
i_user_name	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE 
    _status char(1);
    _arrival date;
    _departure date;
    _booking_id int;
    _booking_item int;
    _room_id int;
    _folio_id int;
    system_date date;
    error text;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
    _guest_id int;
    res_error json;
    msg_prefix TEXT = '202';
    errors jsonb = $${
        "0": "Success",
        "20201": "Registration not found (%s)", 
        "20202": "Check-in is already cancelled",
        "20203": "Guest already checked out",
        "20204": "Cancellation is only allowed on the day of check-in", 
        "20205": "Unable to cancel check-in, there are transactions posted in guest folio"
    }$$;
   
    v_register record;
    v_count_row_block_room int4;
BEGIN
    -- CHECK WHETHER CANCEL-CHECKIN IS AVAILABLE
    /*
     1. /check status
     2. /check arrival
     3. /check existing transactions
     4. X check deposit transactions
     5. check group members
     */
    
    CALL sp_check_endday_process();
    CALL sp_check_record_lock(i_user_name, register_id := i_register_id);
   
    -- ============ Add by T.Supol 27-May-2025
   
    -- ### ต้องการถอยจาก inhouse เป็น block -> จังหวะ ถอยจาก inhouse เป็น block จะแยกเป็น 2 case คือ 
    --  1.กรณีมีการ block จะมีการ update room_block status เป็น B , block qty เป็น 1 
    --  2.assgin room ตรงๆ ไม่มีการ block ดังนั้น 
   
   
    -- ตรวจสอบว่าผ่านการ block มาหรือไม่ ? หรือ assign มาตรงๆเลย ไม่ผ่านการ block
   
    select r.* into v_register 
    from registration r
    where r.id = $1;
    
    SELECT status, arrival, departure, booking_id, booking_item, room_id, folio_id, guest_id
    FROM registration rg
    WHERE id = $1
    INTO _status, _arrival, _departure, _booking_id, _booking_item, _room_id, _folio_id, _guest_id;

    CALL sp_check_folio(_folio_id);

    if exists(select * from transactions t where t.folio_id = _folio_id and coalesce(t.post_flag,'') <> 'D') then
        PERFORM sp_raise_error('20205', errors->>'20205');
    end if;

    system_date = fn_system_date();
    i_user_name = coalesce(i_user_name, fn_current_user());

    error = CASE
        WHEN _status IS NULL THEN '01'
        WHEN _status = 'X' THEN '02'
        WHEN _status = 'O' THEN '03'
        WHEN _arrival <> system_date THEN '04'
        WHEN EXISTS(SELECT 1 FROM transactions WHERE folio_id = _folio_id AND COALESCE(post_flag,'.') <> 'D') THEN '05'
        ELSE '00'
    END;


    IF error = '00' THEN 
        DELETE FROM deposit WHERE register_id = $1;
    
        UPDATE booking_guests  
        SET registered = FALSE, register_id = null
        WHERE register_id = $1; 
        
        UPDATE room_block 
        SET checked_in = FALSE, status = 'B' --, register_id = null
        WHERE register_id = $1;

        /* Update inhouse_qty and block_qty in booking_items*/
        IF EXISTS(Select  1 from room_block rb where rb.register_id = $1) THEN
            UPDATE booking_items bi
            SET block_qty = block_qty+1 , inhouse_qty = inhouse_qty-1
            WHERE item_id = v_register.booking_item;
        ELSE
            UPDATE booking_items bi
            SET inhouse_qty = inhouse_qty-1
            WHERE item_id = v_register.booking_item;
        END IF;
    
        /* Revert deposit status from clearing to received and change folio type back to deposit folio */
        IF EXISTS(SELECT 1 FROM booking b WHERE b.id = _booking_id AND b.folio_id = _folio_id) THEN 
            UPDATE folio f 
            SET folio_type = 'B', register_id = NULL, booking_id = _booking_id 
            WHERE f.id = _folio_id;
            
            UPDATE deposit d
            SET status = 'R'
            WHERE d.booking_id = _booking_id AND d.status = 'C';
        ELSE 
            DELETE FROM folio WHERE register_id = $1;  
        END IF;
    
        /* Update registration's status */
        UPDATE registration SET status = 'X', folio_id = null WHERE id = $1;
    
        /* Update booking's status */ 
        IF NOT EXISTS(SELECT * FROM registration WHERE booking_id = _booking_id and status <> 'X') THEN 
            UPDATE booking 
            SET status = iif(confirm_no IS NULL, 'R', 'C')
            WHERE id = _booking_id AND status = 'I'; 
        END IF;
    
        IF NOT EXISTS(SELECT * FROM registration WHERE room_id = _room_id and status = 'I') THEN 
            UPDATE room_status 
            SET status = 'VAC', 
                hk_status = fn_get_param('ROOM_STATUS','RETURN_STATUS', 'DI')
            WHERE room_id = _room_id;
        END IF;
    
--         Reset registered flag in booking_guests
--        IF _booking_id is not null THEN
--            update booking_guests bg set registered = False
--            where bg.booking_id = _booking_id and bg.guest_id = _guest_id and bg.registered = True; 
--        END IF;
    
        PERFORM sp_log_inhouse($1, 'Cancel check-in', NULL, i_user_name);
        IF _booking_id IS NOT NULL THEN
            PERFORM sp_log_booking(_booking_id, _booking_item, 'Cancel check-in', 'Cancel check-in : room '||fn_room_number(_room_id)||', register#'||$1, i_user_name);
        END IF;
    END IF;

    result_code = iif(error='00', '0', '4');
    msg_code = msg_prefix||error;
    IF error = '01' THEN
        result_msg = sys_msg_format(msg_code, errors->>msg_code, $1::text);
    ELSE
        result_msg = sys_msg(msg_code, errors->>msg_code);
    END IF; 
  
--  if (:arrival is null) then
--     exception db_error '{Guest account not found.'||f_crlf()||'It may be cancelled by another user.}';
-- 
--
--  if (:accstat <> 'I') then
--     exception db_error '{Invalid account status.}';
--
--  if (:postflag = 'Y') then
--     exception db_error '{Unable to cancel check-in, room already posted.}';
--
--  if (:arrival <> :curdate) then
--     exception db_error '{Check-in cancellation is allowed for guests checked in today only.}';
--
--  if (exists(select *
--            from deposit d left join gtran t on t.id = d.clearing_trn_id
--            where accno = :accno and (corrflag = 'Y' or txoutflag = 'Y'))) then
--     exception db_error '{Unable to cancel check-in, the deposit transaction has been voided or transfered out.}';
--
--  if (exists(select * from gtran t
--             where t.folno = :folno      
--                and not(t.refno = 'AUTO' and t.remark starting with 'Virtual Credit Card')
--                and (deposit = 'N')
--                and (id not in (select coalesce(clearing_trn_id,-1) from deposit where accno = :accno)))) then
--     exception db_error '{Unable to cancel check-in, there are some transactions has been posted.}';
-- 
--   
--   SELECT a, b,c 
--   FROM sp_get_result(error_code, prefix, '{01,02,03,04}', '{}') 
--    
--   ) 
--   INTO result_code, msg_code, result_msg;
--
--
--  /* HEADER ACCOUNT */
--  if (:book_header = 'Y' and :allow_recursive = 'Y') then
--  begin 
--     if (exists(select * from gtran t left join gfolio f on f.folno = t.folno where accno <> bkaccno and bkaccno = :bkaccno)) then
--     begin
--        if (:acctype = 'G') then
--          exception db_error '{Unable to cancel check-in, there are some transactions of group member has been posted.}';
--        else
--          exception db_error '{Unable to cancel check-in, there are some transactions of guest that come with this account has been posted.}';
--     end
--      
--     if (exists(select * from gaccount ac inner join gfolio fol on fol.accno = ac.accno
--                where fol.bkaccno = :accno and accstat = 'O')) then
--     begin
--        if (:acctype = 'G') then
--           exception db_error '{Unable to cancel group check-in, some guests have already checked out.}';
--        else
--           exception db_error '{Unable to cancel check-in.'||f_crlf()||'Some guests have already checked out.}';
--     end
--    
--     if (:acctype = 'G') then
--     begin
--        if (exists(select * from namelist where accno = :accno and flags containing 'J')) then
--            exception db_error '{Unable to cancel check-in, there are some guest joined this group}';
--     end
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _state    = RETURNED_SQLSTATE,
        result_msg= MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT; 
    res_error := fn_handle_error(_state, result_msg, _detail, _hint, _context, 'sp_cancel_checkin', 
                    jsonb_build_object('register_id', i_register_id, 'user_name', i_user_name)
                 );
    msg_code    := res_error->>'msg_code';
    result_code := res_error->>'result_code';
    result_msg  := res_error->>'result_msg';
END;