aonestar.public

Parameters

Name	Type	Mode
p_channel_id	integer	IN
rateplans	int4[]	IN

Definition

declare
    rate_count   int = fn_length(rateplans);
    mapped_count int;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
    _message TEXT;
BEGIN
    PERFORM sp_raise_error_if(rate_count = 0, 'Please specify rate plans to map');

UPDATE rate_plan_channels
    SET enabled = false
    WHERE channel_id = p_channel_id;

INSERT INTO rate_plan_channels(rateplan_id, channel_id, enabled)
    SELECT unnest(rateplans), p_channel_id, true
    ON CONFLICT(rateplan_id, channel_id)
    DO UPDATE SET enabled = true;

GET DIAGNOSTICS mapped_count := ROW_COUNT;

UPDATE rate_plan rp
    SET mapped_channels = (
        SELECT count(rc.channel_id)
        FROM rate_plan_channels rc
        WHERE rc.rateplan_id = rp.id AND rc.enabled
    )
    WHERE rp.id = ANY(rateplans);

UPDATE channel cn
    SET mapped_rateplans = (
        SELECT count(rc.rateplan_id)
        FROM rate_plan_channels rc
        WHERE rc.channel_id = cn.id AND rc.enabled
    )
    WHERE cn.id = p_channel_id;

RETURN fn_result_success('00', '%s items mapped', mapped_count::text);
EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _state   := RETURNED_SQLSTATE,
        _message := MESSAGE_TEXT,
        _detail  := PG_EXCEPTION_DETAIL,
        _hint    := PG_EXCEPTION_HINT,
        _context := PG_EXCEPTION_CONTEXT;
    RETURN fn_handle_error(_state, _message, _detail, _hint, _context, 'sp_map_rateplans_to_channel',
        jsonb_build_object(
            'rateplans', to_jsonb(rateplans),
            'channel_id', to_jsonb(p_channel_id)
        )
    );
END