aonestar.public

Parameters

Name	Type	Mode
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 rc
    set enabled = false
    where rc.channel_id = $1;

    IF rate_count > 0 THEN
        INSERT INTO rate_plan_channels(rateplan_id, channel_id, enabled)
        SELECT unnest(rateplans), $1, true
        ON CONFLICT(rateplan_id, channel_id)
        DO UPDATE SET enabled = true;
    END IF;
        
    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 = $1;

    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), 
            'channels', to_jsonb(channels)  
        )
    );   
END