aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_save_channel
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