aonestar.public

Parameters

Name	Type	Mode
p_enabled	boolean	IN (DEFAULT NULL)

Definition

DECLARE
    v_result json;
    v_state TEXT; v_message TEXT; v_detail TEXT; v_hint TEXT; v_context TEXT;
BEGIN
    WITH channel_data AS (
        SELECT
            c.id,
            c.name AS channel_name,
            c.company_name,
            c.type_id AS channel_type_id,
            ct.name AS channel_type,
            c.origin_id,
            o.name AS origin,
            c.nation_id AS nationality_id,
            n.name AS nationality,
            c.contact_country_id AS country_id,
            ctry.name AS country,
            c.folpat_id AS folio_pattern_id,
            fp.name AS folio_pattern,
            c.sales_id AS sales_staff_id,
            sf.name AS sales_staff,
            c.group_id AS channel_group_id,
            cg.name AS channel_group,
            c.payment_condition,
            c.enabled,
            (
                SELECT jsonb_agg(z)
                FROM (
                    SELECT cmk.market_id
                    FROM channel_market cmk
                    WHERE cmk.channel_id = c.id
                    ORDER BY cmk.market_id
                ) z
            ) AS channel_market,
            c.credit_limit
        FROM channel c
            LEFT JOIN channel_type ct ON c.type_id = ct.id
            LEFT JOIN origin o ON c.origin_id = o.id
            LEFT JOIN nationality n ON c.nation_id = n.id
            LEFT JOIN country ctry ON c.contact_country_id = ctry.id
            LEFT JOIN folio_pattern fp ON c.folpat_id = fp.id
            LEFT JOIN sales_staff sf ON c.sales_id = sf.id
            LEFT JOIN channel_group cg ON c.group_id = cg.id
        WHERE (p_enabled IS NULL OR c.enabled = p_enabled)
        ORDER BY c.id ASC
    )
    SELECT json_agg(channel_data) FROM channel_data
    INTO v_result;

RETURN coalesce(v_result, '[]'::json);
EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT,
        v_detail = PG_EXCEPTION_DETAIL, v_hint = PG_EXCEPTION_HINT, v_context = PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(v_state, v_message, v_detail, v_hint, v_context, 'fn_get_channel_list', null::jsonb);
END