aonestar.public

Parameters

Name	Type	Mode
transfer_data	jsonb	IN
user_name	text	IN (DEFAULT NULL)
shift	t_shift	IN (DEFAULT NULL)

Definition

DECLARE
    des_folio_id int;
    des_folio_item int;
    des_trn_id int;
    des_folio_seq int;
    des_folio_name TEXT;
    src_folio_id int;
    src_folio_seq int;
    src_folio_item int;
    src_folio_items int[] = '{}';
    src_trn_id int;
    src_folio_name TEXT;
    folio_key TEXT;
    src_trans int[]; 
    --des_trans int[] = '{}';
    src record;
    trans_count int = 0;
    success_count int = 0; 
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text  TEXT;
BEGIN 
    /* SAMPLE transfer_data  
     {
        "src_folio_id": 22984,
        "des_folio_id": 21627,
        "transactions": {
            "folio1": [1101,1102,3412],
            "folio2": [1254,1103],
            "folio3": [3025],
            "folio4": null  
      }
     */
    CALL sp_check_endday_process();

    src_folio_id = (transfer_data->>'src_folio_id')::int;
    des_folio_id = (transfer_data->>'des_folio_id')::int;

    CALL sp_check_folio(src_folio_id); 
    CALL sp_check_folio(des_folio_id); 

    SELECT CASE f.folio_type
               WHEN 'R' THEN 'Room# '||(SELECT rm.room_number FROM registration rg INNER JOIN room rm ON rg.room_id = rm.id WHERE rg.id = f.register_id)
               WHEN 'B' THEN 'Booking# '||f.booking_id
               WHEN 'C' THEN 'Cash Sale: '||(SELECT cs.name FROM cash_sale cs WHERE cs.folio_id = src_folio_id) 
           END     
    FROM folio f WHERE f.id = src_folio_id
    INTO src_folio_name;

    SELECT CASE f.folio_type
               WHEN 'R' THEN 'Room '||(SELECT rm.room_number FROM registration rg INNER JOIN room rm ON rg.room_id = rm.id WHERE rg.id = f.register_id)
               WHEN 'B' THEN 'Booking '||f.booking_id
               WHEN 'C' THEN 'Cash Sale:'||(SELECT cs.name FROM cash_sale cs WHERE cs.folio_id = des_folio_id) 
           END     
    FROM folio f WHERE f.id = des_folio_id
    INTO des_folio_name;
    

    FOR des_folio_seq IN 1..4 LOOP
        folio_key = 'folio'||des_folio_seq;
        IF (transfer_data->'transactions' ? folio_key) AND (jsonb_array_length(transfer_data->'transactions'->folio_key) > 0) THEN 
        
            src_trans = ARRAY(SELECT jsonb_array_elements_text(transfer_data->'transactions'->folio_key));
            trans_count = trans_count + fn_length(src_trans);
        
           -- js_result = sp_internal_transfer_transactions(trans, des_folio_id, seq, user_name, shift);
        
           -- des_folio_item = (SELECT item_id FROM folio_items fi WHERE fi.folio_id = des_folio_id AND fi.folio_seq = seq);  
            CALL sp_check_folio_item(des_folio_id, des_folio_seq, des_folio_item);
        
            FOR src IN (
                 SELECT t.id AS trn_id, f.folio_id, f.folio_seq, f.item_id AS folio_item 
                 FROM transactions t LEFT join folio_items f on f.item_id = t.folio_item 
                 WHERE t.id = ANY(src_trans) AND NOT t.transfer_out AND NOT f.closed) 
            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 %s (Folio %s/%s)', des_folio_name, 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 %s (Folio %s/%s)', src_folio_name, src.folio_id::TEXT, src.folio_seq::TEXT), user_name, shift, src.trn_id); 
                
                    src_folio_items = src_folio_items || src.folio_item;
                    --des_trans       = des_trans || des_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;
            -- END INTERNAL
        
            PERFORM sp_recalc_folio_balance(des_folio_item);
        END IF;
    END LOOP;
   
    FOR src_folio_item IN (SELECT DISTINCT id FROM unnest(src_folio_items) AS items(id)) LOOP
        PERFORM sp_recalc_folio_balance(src_folio_item);
    END LOOP;
    
    IF success_count < trans_count THEN
        RETURN fn_result_warning(json_build_object('success_count', success_count), '30400', '%s transaction(s) transferred', format('%s/%s', success_count, trans_count));
    END IF;
    RETURN fn_result_success(json_build_object('success_count', success_count), '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_transfer_transactions');
END;

sp_transfer_transactions

Description

Parameters

Definition