NEW on_set_person_inactive TRIGGER

CREATE OR REPLACE TRIGGER on_set_person_inactive
after UPDATE OF person_status_id ON person
FOR each ROW
DECLARE
    v_timezone operation_center.timezone%TYPE;
    v_date_day DATE;
    v_vehicle_owner_id NUMBER(10); -- only for Sixco for now
    v_gui_user_id NUMBER(10);
    v_temp_loginname VARCHAR(20);
BEGIN
    IF :old.person_status_id = 0 AND :NEW.person_status_id IN (1,2) THEN

        SELECT oc.timezone, vo.vehicle_owner_id
        INTO v_timezone, v_vehicle_owner_id
        FROM operation_center oc, vo_person_category vpc, vehicle_owner vo
        WHERE vpc.vo_person_category_id = :NEW.vo_person_category_id
        AND vo.vehicle_owner_id = vpc.vehicle_owner_id
        AND oc.operation_center_id = vo.default_operation_center_id;

        SELECT gu.gui_user_id
        INTO v_gui_user_id
        FROM gui_users gu
        WHERE gu.profiled_person_id = :old.person_id;

        IF v_gui_user_id IS NOT NULL THEN

            SELECT DBMS_RANDOM.string('X', 20)
            INTO v_temp_loginname
            FROM dual;

            UPDATE gui_users
            SET is_active='N',
            loginname = v_temp_loginname
            WHERE gui_user_id = v_gui_user_id;
        END IF;

        IF v_vehicle_owner_id = 101408 THEN

            v_date_day := TRUNC(touserdate(sysdateutc, v_timezone));

            DELETE FROM pd_day
            WHERE person_id = :NEW.person_id
            AND date_day >= v_date_day + 1;

            DELETE FROM pd_trf_day
            WHERE person_id = :NEW.person_id
            AND date_day >= v_date_day + 1;
        END IF;
    END IF;
  EXCEPTION WHEN OTHERS THEN
  NULL;
END;