Untitled

CREATE OR REPLACE FUNCTION find_message(p_step_uuid IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
    v_cur SYS_REFCURSOR;
    v_count NUMBER;
BEGIN
    -- 1️⃣ önce SMS tablosunda var mı diye bak
    SELECT COUNT(1)
    INTO v_count
    FROM sms
    WHERE step_uuid = p_step_uuid;

    -- 2️⃣ varsa SMS'i döndür
    IF v_count > 0 THEN
        OPEN v_cur FOR
            SELECT 'SMS' AS source, s.*
            FROM sms s
            WHERE s.step_uuid = p_step_uuid;
        RETURN v_cur;
    END IF;

    -- 3️⃣ yoksa EMAIL'e bak
    SELECT COUNT(1)
    INTO v_count
    FROM email
    WHERE step_uuid = p_step_uuid;

    IF v_count > 0 THEN
        OPEN v_cur FOR
            SELECT 'EMAIL' AS source, e.*
            FROM email e
            WHERE e.step_uuid = p_step_uuid;
        RETURN v_cur;
    END IF;

    -- 4️⃣ hiçbiri yoksa boş cursor döndür
    OPEN v_cur FOR
        SELECT 'NONE' AS source, NULL AS dummy_col FROM dual;
    RETURN v_cur;

END;
/