aonestar.public

Parameters

Name	Type	Mode
trans	int4[]	IN
des_folio_id	integer	IN
des_folio_seq	integer	IN
user_name	text	IN (DEFAULT NULL)
shift	t_shift	IN (DEFAULT NULL)

Definition

DECLARE
    des_folio_item int;
    des_trn_id int;
    src_folio_id int;
    src_folio_seq int;
    src_folio_item int;
    src_trn_id int;
    success_count int = 0; 
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text  TEXT;
BEGIN 
    CALL sp_check_endday_process();

    CALL sp_check_folio_item(des_folio_id, des_folio_seq, des_folio_item);

    SELECT f.item_id, f.folio_id, f.folio_seq 
    FROM transactions t LEFT join folio_items f on f.item_id = t.folio_item
    WHERE t.id = trans[1]
    INTO src_folio_item, src_folio_id, src_folio_seq;

    CALL sp_check_folio_item(src_folio_item);
     
    FOR src_trn_id IN (SELECT id FROM transactions WHERE id = ANY(trans) AND NOT transfer_out) 
    LOOP
        IF des_folio_id <> src_folio_id THEN 
            INSERT INTO transactions (folio_item, post_type, post_date, post_time, tran_type, dept_id, description, qty, amount, reference, remark, post_user, 
                post_shift, post_source, tax_code, card_id, transfer_in, transfer_src, splitted, splitted_src, original_folio_id, post_flag, voided, charge_date)
            SELECT des_folio_item, post_type, post_date, post_time, tran_type, dept_id, description, qty, amount, reference, remark, post_user, 
                post_shift, post_source, tax_code, card_id, TRUE, SRC_TRN_ID, splitted, splitted_src, original_folio_id, post_flag, voided, charge_date
            FROM transactions WHERE id = src_trn_id
            RETURNING id INTO des_trn_id; 
        
            UPDATE transactions
            SET transfer_out = TRUE, transfer_des = des_trn_id
            WHERE id = src_trn_id;
        
            PERFORM sp_log_trans(src_trn_id, 'Transfer out', format('Transfer to folio %s/%s', des_folio_id::TEXT, des_folio_seq::TEXT), user_name, shift, des_trn_id); 
            PERFORM sp_log_trans(des_trn_id, 'Transfer in', format('Transfer from folio %s/%s', src_folio_id::TEXT, src_folio_seq::TEXT), user_name, shift, src_trn_id); 
        ELSE
            UPDATE transactions
            SET folio_item = des_folio_item 
            WHERE id = src_trn_id;
            PERFORM sp_log_trans(src_trn_id, 'Move', format('Move from folio %s', src_folio_seq::TEXT), user_name, shift);
        END IF;
        success_count = success_count +1;
    END LOOP;

    IF success_count > 0 THEN
        PERFORM sp_recalc_folio_balance(src_folio_item);
        PERFORM sp_recalc_folio_balance(des_folio_item);
    END IF;
    
    RETURN fn_result_success('30400', '%s transaction(s) transferred', success_count::text);

EXCEPTION  
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE,
        _msg_text = MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;    
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_move_transactions');
END;