aonestar.public

Parameters

Name	Type	Mode
data	jsonb	IN

Definition

DECLARE
    v_state TEXT; v_message TEXT; v_detail TEXT; v_hint TEXT; v_context TEXT;
    v_result json;
    v_ch record;
    v_channel_id int;
    v_markets jsonb;
BEGIN
    -- Parse input
    SELECT * INTO v_ch
    FROM jsonb_to_record(data) AS ch(
        id int,
        name text,
        contact_name text,
        contact_addr1 text,
        contact_addr2 text,
        contact_country_id int,
        contact_telephone text,
        contact_mobile text,
        contact_fax text,
        contact_email text,
        company_name text,
        branch_no text,
        tax_id text,
        payment_term text,
        credit_term smallint,
        credit_limit numeric,
        type_id int,
        group_id int,
        origin_id int,
        folpat_id int,
        nation_id int,
        sales_id int,
        coallot_channel int,
        payment_condition char(3),
        remark text,
        otacode char(3),
        enabled bool
    );

v_markets := data->'channel_market';

-- Validate unique name
    IF EXISTS(SELECT 1 FROM channel c WHERE c.name = v_ch.name AND v_ch.id IS DISTINCT FROM c.id) THEN
        RETURN fn_result_error('50201', sys_msg('50201', 'Channel name %s already exists'), v_ch.name);
    END IF;

-- Validate payment_condition
    IF v_ch.payment_condition IS NOT NULL AND v_ch.payment_condition NOT IN ('COD','COA','DRB','VCC') THEN
        RETURN fn_result_error('50202', sys_msg('50202', 'Invalid payment condition: %s'), v_ch.payment_condition);
    END IF;

-- INSERT or UPDATE
    IF (v_ch.id IS NULL) OR (v_ch.id < 1) THEN
        INSERT INTO channel (
            name, contact_name, contact_addr1, contact_addr2, contact_country_id,
            contact_telephone, contact_mobile, contact_fax, contact_email,
            company_name, branch_no, tax_id, payment_term, credit_term, credit_limit,
            type_id, group_id, origin_id, folpat_id, nation_id, sales_id,
            coallot_channel, payment_condition, remark, otacode, enabled
        ) VALUES (
            v_ch.name, v_ch.contact_name, v_ch.contact_addr1, v_ch.contact_addr2, v_ch.contact_country_id,
            v_ch.contact_telephone, v_ch.contact_mobile, v_ch.contact_fax, v_ch.contact_email,
            v_ch.company_name, v_ch.branch_no, v_ch.tax_id, v_ch.payment_term, v_ch.credit_term, v_ch.credit_limit,
            v_ch.type_id, v_ch.group_id, v_ch.origin_id, v_ch.folpat_id, v_ch.nation_id, v_ch.sales_id,
            v_ch.coallot_channel, coalesce(v_ch.payment_condition, 'COD'), v_ch.remark, v_ch.otacode, coalesce(v_ch.enabled, true)
        )
        RETURNING id INTO v_channel_id;
    ELSE
        UPDATE channel c SET
            name              = v_ch.name,
            contact_name      = v_ch.contact_name,
            contact_addr1     = v_ch.contact_addr1,
            contact_addr2     = v_ch.contact_addr2,
            contact_country_id = v_ch.contact_country_id,
            contact_telephone  = v_ch.contact_telephone,
            contact_mobile     = v_ch.contact_mobile,
            contact_fax        = v_ch.contact_fax,
            contact_email      = v_ch.contact_email,
            company_name       = v_ch.company_name,
            branch_no          = v_ch.branch_no,
            tax_id             = v_ch.tax_id,
            payment_term       = v_ch.payment_term,
            credit_term        = v_ch.credit_term,
            credit_limit       = v_ch.credit_limit,
            type_id            = v_ch.type_id,
            group_id           = v_ch.group_id,
            origin_id          = v_ch.origin_id,
            folpat_id          = v_ch.folpat_id,
            nation_id          = v_ch.nation_id,
            sales_id           = v_ch.sales_id,
            coallot_channel    = v_ch.coallot_channel,
            payment_condition  = coalesce(v_ch.payment_condition, c.payment_condition),
            remark             = v_ch.remark,
            otacode            = v_ch.otacode,
            enabled            = coalesce(v_ch.enabled, c.enabled)
        WHERE c.id = v_ch.id
        RETURNING c.id INTO v_channel_id;

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

-- Manage channel_market
    DELETE FROM channel_market WHERE channel_id = v_channel_id;

IF v_markets IS NOT NULL AND jsonb_array_length(v_markets) > 0 THEN
        INSERT INTO channel_market (channel_id, market_id)
        SELECT v_channel_id, (m->>'market_id')::int
        FROM jsonb_array_elements(v_markets) m
        ON CONFLICT DO NOTHING;
    END IF;

-- Return full detail
    RETURN fn_get_channel_detail(v_channel_id);

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, 'sp_save_channel', data);
END