aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
dept_id	integer	IN
amount	t_money	IN
qty	integer	IN (DEFAULT 1)
folio_seq	integer	IN (DEFAULT NULL)
shift	character varying	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
reference	text	IN (DEFAULT NULL)
remark	text	IN (DEFAULT NULL)
description	text	IN (DEFAULT NULL)
charge_date	date	IN (DEFAULT NULL)
source_module	text	IN (DEFAULT NULL)
allow_redirect	boolean	IN (DEFAULT true)
post_flag	character	IN (DEFAULT NULL)
folio_id	integer	IN
dept_code	character varying	IN
amount	t_money	IN
qty	integer	IN (DEFAULT 1)
folio_seq	integer	IN (DEFAULT NULL)
shift	character varying	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
reference	text	IN (DEFAULT NULL)
remark	text	IN (DEFAULT NULL)
description	text	IN (DEFAULT NULL)
charge_date	date	IN (DEFAULT NULL)
source_module	text	IN (DEFAULT NULL)
allow_redirect	boolean	IN (DEFAULT true)
post_flag	character	IN (DEFAULT NULL)
details	jsonb	IN (DEFAULT NULL)

Definition

declare
    _register_id int;
    _folio_type t_folio_type;
    _room_posted boolean;
    _register_status char(1);
    _folio_status char(1);
    _room_id  int; 
    _folio_id int;
    _rateplan_id int;
    _folio_item int;
    _charge_to_folio int;
    _dept_name TEXT;
    _itemizers t_itemizer[];
    _charge_date date;
    dept_id int;
    default_room_charge_dept int;
    fol_booking_id int;
    fol_book_type char(1);
    is_online_post boolean = source_module IS NOT NULL;
    folio_closed boolean = FALSE;
    folio_locked boolean = FALSE;
    trn_id int;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text  TEXT;
    _shift character;
    msg_group TEXT = '301';
    messages  jsonb = $${
        "30100" : "Successful", 
        "30101" : "Invalid post amount (%s)", 
        "30102" : "Invalid folio sequence (%s)", 
        "30110" : "Invalid department code (%s)", 
        "30103" : "Guest account checked out", 
        "30112" : "Folio closed",     
        "30104" : "Room already post", 
        "30105" : "Folio %s does not exist", 
        "30106" : "Folio %s is already closed",     
        "30107" : "Folio locked", 
        "30108" : "Department not assign to any folio", 
        "30109" : "Host Offline", 
        "90001" : "End-day process in progress"
    }$$;
BEGIN
    CALL sp_check_endday_process();

    raise notice 'details: %', jsonb_pretty(details);

    /* INITIALIZE PARAMETERS AND VARIABLES */
    folio_seq   = nullif(folio_seq, 0);
    reference   = nullif(reference, '');
    remark      = nullif(remark, '');
    description = nullif(description, '');
    charge_date = coalesce(charge_date, fn_system_date());
    _charge_date = charge_date;

    IF user_name IS NULL THEN 
        user_name := fn_current_user();
        shift     := coalesce(shift, fn_current_shift());
    END IF;

    --dept_id = (select id from department where code = $2 AND post_type = 'P');

    /* VERIFY INPUT PARAMETERS */
    CASE 
        WHEN (amount <= 0 or amount > 999999999) THEN
            RETURN fn_result_error('30101', messages->>'30101', amount::text); 
        WHEN (folio_seq IS NOT NULL AND folio_seq not in (1,2,3,4,-99)) THEN   /* NULL = auto, -99 = temporary folio */
            RETURN fn_result_error('30102', messages->>'30102', folio_seq::text); 
        ELSE 
            SELECT id, name from department where (code = $2 collate CI) AND (post_type = 'P')
            INTO dept_id, _dept_name;
        
            IF (dept_id IS null) THEN
                RETURN fn_result_error('30110', messages->>'30110', $2::text); 
            END IF;
    END CASE;

    -- Check shift
    --_shift = shift;
    CALL sp_check_shift(shift, is_online_post, TRUE); -- sp_check_shift(shift, is_online_post, FALSE)
    --shift = _shift;

    SELECT f.register_id, f.folio_type, rg.room_posted, rg.status, f.status, rg.room_id, rg.rateplan_id, fb.book_type, f.booking_id, f.folio_locked
    FROM folio f 
        LEFT JOIN registration rg ON rg.id = f.register_id 
        LEFT JOIN booking fb ON fb.id = f.booking_id 
    WHERE f.id = $1
    INTO _register_id, _folio_type, _room_posted, _register_status, _folio_status, _room_id, _rateplan_id, fol_book_type, fol_booking_id, folio_locked;
    
    -- Check whether room charge is posted
    IF (dept_id = fn_get_room_charge_dept(_register_id, charge_date)) THEN
        IF (_folio_type = 'I' AND _room_posted) THEN 
            RETURN fn_result_error('30104', messages->>'30104');  -- Room is already posted  
        ELSEIF (_folio_type = 'B' AND fol_book_type = 'G' 
                AND EXISTS(SELECT * FROM booking bk INNER JOIN group_info g ON g.id = bk.group_id 
                           WHERE bk.id = fol_booking_id AND g.room_post_flag = 'A')) THEN 
            RETURN fn_result_error('30104', messages->>'30104');  -- Room is already posted (group booking) 
        END IF;
    END IF;

    -- Find target folio item
    folio_seq = coalesce(folio_seq, fn_get_target_folio_seq($1, dept_id, force_first_folio := is_online_post));
    --raise notice 'folio_id: %, dept_id: % -> folio_seq: %', $1, dept_id, folio_seq;
    
    SELECT fi.item_id, fi.closed, fi.charge_to_folio  
    FROM folio_items fi 
    WHERE fi.folio_id = $1 AND fi.folio_seq  = $5 
    ORDER BY fi.closed 
    LIMIT 1
    INTO _folio_item, folio_closed, _charge_to_folio; 

    CALL sp_check_folio_item(_folio_item);

    -- Perform transaction post
    INSERT INTO transactions (folio_item, original_folio_id, charge_date, tran_type, dept_id, description, 
        qty, amount, reference, remark, post_user, post_shift, post_source, tax_code, post_flag, itemizers, details)
    SELECT _folio_item, $1, charge_date, d.dept_type, dept_id, coalesce(description, d."name"), 
           qty, amount, reference, remark, user_name, shift, source_module, tax_code, post_flag, 
           fn_get_itemizer_amounts(amount, d.itemizers), details 
    FROM department d WHERE id = dept_id
    RETURNING id INTO trn_id;

    -- if this post is room charge, update room post flag to true
    default_room_charge_dept = fn_default('ROOM_CHARGE_DEPT', 0); 
    IF (_register_id IS NOT NULL) AND EXISTS(
        SELECT * FROM registration_rates r
        WHERE r.register_id = _register_id 
            AND r.charge_date = _charge_date 
            AND dept_id = COALESCE(r.room_charge_dept, default_room_charge_dept)) 
    THEN       
        UPDATE registration_rates r
        SET posted = TRUE 
        WHERE r.register_id = _register_id AND r.charge_date = _charge_date;
    END IF;

    PERFORM sp_recalc_folio_balance(_folio_item);

    PERFORM sp_log_trans(trn_id, 'Post', 'Post '||_dept_name, user_name, shift);

    RETURN fn_result_success(fn_get_transaction_js(trn_id, true));
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, format('sp_post_transaction(%L, %L, %L)', $1, $2, $3));
END;