aonestar.public

Parameters

Name	Type	Mode
rateplans	int4[]	IN
channels	int4[]	IN
single_rate_mode	boolean	IN (DEFAULT false)

Definition

declare 
    rate_count int = fn_length(rateplans);
    channel_count int = fn_length(channels);
    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');

    PERFORM sp_raise_error_if(not single_rate_mode and channel_count = 0, 'Please specify channels to map');
 
    update rate_plan_channels rc
    set enabled = false
    where rc.rateplan_id = any(rateplans);

    IF channel_count > 0 THEN
        INSERT INTO rate_plan_channels(rateplan_id, channel_id, enabled)
        SELECT m_rateplan_id, m_channel_id, true
        FROM unnest(rateplans) as m_rateplan_id, 
             unnest(channels) as m_channel_id
        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 = any(channels);

    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_channels_to_rateplan', 
        jsonb_build_object(
            'rateplans', to_jsonb(rateplans), 
            'channels', to_jsonb(channels)  
        )
    );   
END

sp_save_rateplan_channel_mapping

Parameters

Definition