ERPstuff - Oracle EBS Community

Code: Select all

DECLARE
    v_input VARCHAR2(4000) := :P30_DEPTNAME_MANUAL;
    v_deptno NUMBER;
    v_deptno_list VARCHAR2(4000);
    v_exists NUMBER;
BEGIN
    FOR rec IN (
        SELECT TRIM(COLUMN_VALUE) AS dept_name
        FROM TABLE(APEX_STRING.SPLIT(v_input, ':'))
        WHERE TRIM(COLUMN_VALUE) IS NOT NULL
    )
    LOOP
        -- Check if department name already exists
        BEGIN
            SELECT DEPTNO INTO v_deptno
            FROM DEPT
            WHERE DNAME = rec.dept_name;
            
            -- Department exists, use existing DEPTNO
            
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- Department doesn't exist, insert new
                INSERT INTO DEPT (DNAME) 
                VALUES (rec.dept_name)
                RETURNING DEPTNO INTO v_deptno;
        END;
        
        -- Build pipe-separated list of DEPTNOs
        IF v_deptno_list IS NULL THEN
            v_deptno_list := v_deptno;
        ELSE
            v_deptno_list := v_deptno_list || ':' || v_deptno;
        END IF;
    END LOOP;
    
    -- Append new DEPTNOs to existing P30_DEPT_LIST
    IF :P30_DEPT_LIST IS NOT NULL THEN
        :P30_DEPT_LIST := :P30_DEPT_LIST || ':' || v_deptno_list;
    ELSE
        :P30_DEPT_LIST := v_deptno_list;
    END IF;
    
   
    apex_application.g_print_success_message := 'DEPTNOs: ' || v_deptno_list;
    
EXCEPTION
    WHEN OTHERS THEN
        apex_error.add_error(
            p_message => 'Error: ' || SQLERRM,
            p_display_location => apex_error.c_inline_in_notification
        );
        ROLLBACK;
END;