aonestar.public

Parameters

Name	Type	Mode
folio_item_id	integer	IN
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    _folio_id int;
    _folio_seq int;
    _close_date date;
    _active_folio_item int;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    _vat_id   INT;
    v_result JSON;
BEGIN
    user_name := coalesce(user_name, fn_current_user());

    SELECT f.folio_id, f.folio_seq, f.close_date, f.vat_id    
    FROM folio_items f
    WHERE f.item_id = $1
    INTO _folio_id, _folio_seq, _close_date, _vat_id;

    _active_folio_item = (SELECT item_id FROM folio_items f 
                          WHERE f.folio_id = _folio_id AND f.folio_seq = _folio_seq AND NOT f.closed); 

    CASE
        WHEN _close_date < fn_system_date() THEN
            RETURN fn_result_error('30109', 'Cannot reopen outdated folio');
        WHEN EXISTS(SELECT id FROM transactions t WHERE t.folio_item = _active_folio_item) THEN 
            RETURN fn_result_error('30110', 'Unable to reopen, there are transactions in active folio');
        ELSE 
    END CASE;

    DELETE FROM folio_items WHERE item_id = _active_folio_item;

    UPDATE folio_items 
    SET closed = FALSE--, 
--      close_date = NULL,
--        close_time = NULL,
--        close_user = NULL,
--        close_shift = NULL
    WHERE item_id = $1;

    IF _vat_id is not null THEN
        v_result := rpc.salestax_cancel_tax_invoice(
            json_object('user_name': user_name)::jsonb,
            json_object(
                'vat_id': _vat_id,
                'remark': 'Reopen folio'
            )::jsonb
        );
        
        IF (v_result->>'status' <> '200') THEN
            RETURN fn_result_error('30310', 'Cancel tax invoice failed, %s', v_result->>'message');
        ELSIF (v_result->'data'->>'result_code' = '0') THEN
            RETURN fn_result_error('30310', 'Cancel tax invoice failed, %s', v_result->'data'->>'result_msg');
        END IF;
    END IF; 
    
    --TODO: cancel vat
    
    PERFORM sp_log_cashier(_folio_id, folio_item_id, 'Reopen folio', null, user_name);
    
    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, format('sp_reopen_folio(%L)', $1));
    
END;