aonestar.public

Parameters

Name	Type	Mode
p_channel_id	integer	IN

Definition

DECLARE
    v_result json;
    v_state TEXT; v_message TEXT; v_detail TEXT; v_hint TEXT; v_context TEXT;
BEGIN
    SELECT row_to_json(r) INTO v_result
    FROM (
        SELECT
            c.id,
            c.name AS channel_name,
            c.contact_name,
            c.contact_addr1,
            c.contact_addr2,
            c.contact_country_id AS country_id,
            ctry.name AS country,
            c.contact_telephone,
            c.contact_mobile,
            c.contact_fax,
            c.contact_email,
            c.company_name,
            c.branch_no,
            c.tax_id,
            c.payment_term,
            c.credit_term,
            c.credit_limit,
            c.type_id AS channel_type_id,
            ct.name AS channel_type,
            c.group_id AS channel_group_id,
            cg.name AS channel_group,
            c.origin_id,
            o.name AS origin,
            c.folpat_id AS folio_pattern_id,
            fp.name AS folio_pattern,
            c.nation_id AS nationality_id,
            n.name AS nationality,
            c.sales_id AS sales_staff_id,
            sf.name AS sales_staff,
            c.coallot_channel,
            c.payment_condition,
            c.remark,
            c.otacode,
            c.enabled,
            (
                SELECT coalesce(jsonb_agg(jsonb_build_object('id', mk.id, 'name', mk.name) ORDER BY mk.id), '[]'::jsonb)
                FROM channel_market cm
                    LEFT JOIN market mk ON cm.market_id = mk.id
                WHERE cm.channel_id = c.id
            ) AS channel_market
        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 c.id = p_channel_id
    ) r;

IF v_result IS NULL THEN
        RETURN fn_result_error('50203', sys_msg('50203', 'Channel not found'));
    END IF;

RETURN fn_result_success(v_result);
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_detail',
        jsonb_build_object('channel_id', p_channel_id));
END