aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_result json;
    v_msg_text text;
    v_sqlstate text;
    v_detail text;
    v_hint text;
    v_context text;
BEGIN
   
    SELECT json_agg(row_to_json(dept_data)) INTO v_result
    FROM (
        SELECT
            d.id,
            d.code,
            d.name,
            dg.id as group_id,
            dg.name AS group,
            d.dept_type,
            d.require_info,
            d.taxes,
            d.enabled,
            d.itemizers
        FROM department d
        LEFT JOIN department_group dg ON d.group_id = dg.id
        WHERE d.post_type = 'P'
        ORDER BY d.code asc
    ) dept_data;
    
    
    IF v_result IS NULL THEN
        v_result := '[]'::json;
    END IF;
   
    return v_result;
    
   -- RETURN fn_result_success(v_result);

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_msg_text = message_text,
            v_sqlstate = returned_sqlstate,
            v_detail = pg_exception_detail,
            v_hint = pg_exception_hint,
            v_context = pg_exception_context;
            
        RETURN fn_handle_error(
            v_sqlstate,
            v_msg_text,
            v_detail,
            v_hint,
            v_context,
            'sp_get_department_json',
            null
        );
END;