aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_count_use INTEGER := 0;
   
    v_json_item json;
    v_item text;
    v_search_item boolean;
begin
	-- created by T.Supol 25-June-2025
	v_search_item:=false;
	
	v_json_item:= fn_sys_param('DEFAULT', 'ITEMIZERS', null);

    -- === loop check json in system_param
    for v_item in select json_array_elements_text(v_json_item)
    loop 
	     if upper(old.code) = upper(v_item) then
	     	v_search_item:=true;
	     end if;
     	
    end loop;
    
	
	if v_search_item=true then
	     -- RAISE EXCEPTION 'Cannot disable itemizer with code: %, because it is referenced by one or more departments.', OLD.code;
    	 if new.enabled = false then
		 	raise exception 'Cannot disable because the specified value exists in system_param';
		 end if;
	end if;
   
   
    -- === ลอก function trigger delete มา
    -- ==========================
	-- ตรวจสอบการใช้งานใน table department (array)
	-- ==========================
    SELECT COUNT(*) INTO v_count_use
    FROM department d
    WHERE d.itemizers IS NOT NULL
      AND OLD.code = ANY(d.itemizers);

    IF COALESCE(v_count_use, 0) > 0 then
       if new.enabled = false then
            RAISE EXCEPTION 'Cannot disable because the specified value exists in department';
       end if;
    END IF;

    -- ==========================
    -- ตรวจสอบการใช้งานใน table transactions (jsonb)
    -- ==========================
    SELECT COUNT(*) INTO v_count_use
    FROM transactions t
    JOIN LATERAL jsonb_array_elements(t.itemizers) AS elem ON t.itemizers IS NOT NULL
    WHERE elem ->> 'code' = OLD.code;

    IF COALESCE(v_count_use, 0) > 0 then
    	if new.enabled = false then
        	RAISE EXCEPTION 'Cannot disable because the specified value exists in transactions';
        end if;
    END IF;

    RETURN new;
END;

sp_trg_itemizer_before_update

Parameters

Definition