Oracle Apex 5.1 Custom Master Details

Question

Oracle Apex 5.1 Custom Master Details Problem I have created a page with two region, 1. dept (some text field, deptno, dname) 2. emp (interactive grid , ename, deptno) And one submit Button Flow : User input manually department name on 'dname' text field And add some employees row on grid and finally when user click on submit button then first create a department with deptno (auto increment) And then insert grid row with deptno which created first region. That means every deptno has some corresponding employee and both task will create same times. (after click on submit button) If possible and you understand about this please give me a solution.

Cristian_I Cristian_I · Accepted Answer · 2017-01-25T23:05:38

On your page create a hidden item that will have your new department id, named P1_depno in this example.

When you have created the Interactive Grid with the Attributes > Edit > Enabled set to Yes an Interactive Grid - Automatic Row Processing (DML) process (most probably named - Save Interactive Grid Data) should have been added to your page (if you don't have it simply create a new process with the above type).

Before this process create a new PL/SQL process (named Insert Department in my example) that will insert the department using this code:

declare
v_depno number;
begin  

    select deptno_sequence.nextval into :P1_depno from dual; --get the depno from your sequence

    insert into dept (deptno, dname)-- insert the new department
    values (:P1_depno,:P1_dname);
end;

Very important this process must run before the Save Interactive Grid Data process.

Now edit the Save Interactive Grid Data process and at Target Type select PL/SQL Code and add the following code:

begin  

    --insert the employees
     case :APEX$ROW_STATUS  
     when 'C' then -- C for Create but you also can check for U (Update) and D (Delete)
         insert into emp ( empno, ename, deptno )  
         values ( :EMPNO, :ENAME, :P1_depno )  
         returning rowid into :ROWID;  
     end case; 

end;

Oracle Apex 5.1 Custom Master Details

1 Answers