aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN
folio_seq	integer	IN
payment_code	character	IN
amount	t_money	IN
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)
source_module	text	IN (DEFAULT NULL)
post_flag	character	IN (DEFAULT NULL)
folio_id	integer	IN
folio_seq	integer	IN
payment_id	integer	IN
amount	t_money	IN
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)
source_module	text	IN (DEFAULT NULL)
post_flag	character	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;
    _dept_type char(1);
    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;
    msg_group TEXT = '301';
    messages  jsonb = $${
        "30101" : "Invalid post amount (%s)", 
        "30102" : "Invalid folio sequence (%s)", 
        "30103" : "Guest account checked out", 
        "30112" : "Folio closed",     
        "30105" : "Folio %s does not exist", 
        "30106" : "Folio %s is already closed",     
        "30107" : "Folio locked", 
        "30109" : "Host Offline", 
        "90001" : "End-day process in progress"
    }$$;
BEGIN
    CALL sp_check_endday_process();

    /* INITIALIZE PARAMETERS AND VARIABLES */
    --folio_seq   = nullif(folio_seq, 0);
    reference   = nullif(reference, '');
    remark      = nullif(remark, '');
    description = nullif(description, '');
    
    user_name := coalesce(user_name, fn_current_user());
    shift     := coalesce(shift, fn_work_shift(user_name), fn_online_shift());
    --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 NULL or folio_seq not in (1,2,3,4,-99)) THEN   /* NULL = auto, -99 = temporary folio */
            RETURN fn_result_error('30102', messages->>'30102', COALESCE(folio_id::TEXT,'')||'/'||COALESCE(folio_seq::TEXT,''));         
        ELSE 
    END CASE;

    -- Check shift
    CALL sp_check_shift(shift, is_online_post, FALSE);
   
    CALL sp_check_folio_item(folio_id, folio_seq, _folio_item);

    SELECT dept_type, name from department where id = payment_id AND post_type = 'S'
    INTO _dept_type, _dept_name;

    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;

    -- Perform transaction post
    INSERT INTO transactions (folio_item, original_folio_id, post_type, tran_type, dept_id, description, 
        amount, reference, remark, post_user, post_shift, post_source, charge_date, post_flag)
    VALUES(_folio_item, $1, 'S', _dept_type, payment_id, coalesce(description, _dept_name), 
           amount, reference, remark, user_name, shift, source_module, fn_system_date(), post_flag)
    RETURNING id INTO trn_id;

    PERFORM sp_recalc_folio_balance(_folio_item);

    PERFORM sp_log_trans(trn_id, 'Payment', 'Paid '||_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_make_payment(%L, %L, %L, %L)', $1, $2, $3, $4));  
END;