Advanced Form Development (Nested Form with Tabs) on Oracle APEX

This article will discuss about advanced form development (nested form with tabs) on Oracle APEX. Here we’re going to create a process of submission that involves three forms where each form in on different tab. The goal is we will fill some data into form1 on tab1, after completed then we will fill some data into form2 on tab2 and then we will fill some data into form3 on tab3. Data will be correlated among all of those three  forms.

The development phases will be divided into three, that are ER Diagram creation, Mockup Diagram creation and then following with coding phase.

1.       ER Diagram Creation

This article will use following ER Diagram that has 3 Entity Sets (Mahasiswa, DetilMahasiswa and UKM).

2.       Mockup Diagram Creation

Here are the three mockup diagrams that we will create (Form1, Form2, Form3):

3.       Coding Phase

After we design an ER Diagram, now we are ready for entering the coding phase. Here are the steps:

1.       Create 3 tables based on ER Diagram

a.       Tabel TMAHASISWA {#nim,nama,jk}

b.       Tabel TDETILMAHASISWA {alamat1,alamat2,telp,email,@nim}

c.       Tabel TMHS_UKM {tahun,jabatan,@idukm,@nim}

2.       Create new Database Application with a blank page

3.       Edit Page 1, Add HTML region namely form1

a.       Add following items (P1_JK static select list)

b.       Add a Next button, action when clicked is Submit.

c.       Fill this code on PL/SQL Process name it as simpan_form1:

BEGIN

                INSERT INTO tmahasiswa(nim,nama,jk) VALUES(:P1_NIM,:P1_NAMA,:P1_JK);

                COMMIT;

                htp.init;

                owa_util.redirect_url('f?p=&APP_ID.:2:&APP_SESSION.::::P2_NIM:'||:P1_NIM);

                apex_application.stop_apex_engine;

                apex_application.g_print_success_message := 'Berhasil';

END;

d.       Run the page, it should be look like this:

4.       Add new Blank Page

a.       Name it as Form2, Breadcrumb is enabled

b.       Choose Tab option enabled

c.       Finish the creation process the edit it. Add a HTML Region.

d.       Add following items on Form 2:

e.       Set P2_NAMA item with following descriptions:

f.        Add a button name it P2_PREV. Set the properties as follows:

g.       Add a button name it P2_NEXT, action when button clicked = Submit. Set the grid layout properties as follows:

h.       Create a PL/SQL Process name it as simpan_form2, assign it to P2_PREV button. Fill this code into source:

BEGIN

                INSERT INTO tdetilmahasiswa(alamat1,alamat2,telp,email,nim) VALUES(:P2_ALAMAT1,:P2_ALAMAT2,:P2_TELP,:P2_EMAIL,:P2_NIM);

                COMMIT;

                htp.init;

                owa_util.redirect_url('f?p=&APP_ID.:3:&APP_SESSION.::::P3_NIM:'||:P2_NIM);

                apex_application.stop_apex_engine;

END;

i.         Run the Page, it should be look like this:

5.       On SQL Command, create stored function name it as cek_ukm

create or replace function cek_ukm(p1 in varchar2, p2 in number, p3 in number) return varchar2

as

                vukm VARCHAR2(8);

                hasil varchar2(128);

begin

                select idukm into vukm from tmhs_ukm where idukm=p1 and nim=p2 and tahun=p3;

                select nama_ukm into hasil from tukm where id=vukm;

                return hasil;

end;

6.       Create stored function name it as cek_jabatan

create or replace function cek_jabatan(p1 in varchar2, p2 in number, p3 in number) return varchar2

as

                hasil varchar2(128);

begin

                select jabatan into hasil from tmhs_ukm where idukm=p1 and nim=p2 and tahun=p3;

                return hasil;

end;

7.       Create new Blank Page

a.       Name it as Form3, Breadcrumb is activated

b.       Activate the Tab set

c.        Finish the page creation the edit it. Add new HTML region.

d.       Add following items:

e.       Set P3_NAMA item properties as follows:

f.         Add a report region, fill this SQL into the source:

select NVL(cek_ukm(id,nim,tahun),APEX_ITEM.SELECT_LIST_FROM_QUERY(1,NULL,'SELECT NAMA_UKM,ID FROM TUKM')) as ORG, NVL(cek_jabatan(id,nim,tahun),APEX_ITEM.TEXT(2)) as JBT

from tukm a

left outer join (select * from tmhs_ukm where nim=:P3_NIM and tahun= :P3_TAHUN) b on a.id=b.idukm

Edit that region, go to Report Attributes

Edit column ORG and JBT, make sure the Column Attributes set to Display As Standard Report Column. Apply Changes.

g.       Add new button (P3_SIMPAN), action when button clicked=submit

h.       Create new PL/SQL Process name it as simpan_form3, assign to P3_SIMPAN button. Fill this code into the Source area:

begin

 for i in 1..apex_application.g_f01.count loop

  IF apex_application.g_f01(i) <> '%null%' THEN

  insert into TMHS_UKM (tahun,jabatan,idukm,nim)

                values (:P3_TAHUN,apex_application.g_f02(i),apex_application.g_f01(i),:P3_NIM);

  END IF;

 end loop;

 COMMIT;

end;

i.         Run the Page, it should be look like this:

The creation process has completed, now we’re going to try it.

1.       Check the TUKM table data

2.       Run the application, fill data on Form1 then click Next button

3.       Form2 will automatically shows along with Nama (Boby Siswanto). Fill data on Form2 then click on Next button

a.       Form3 will automatically shows, fill the text fields. Choose select list options on bellow area (Organisasi) and fill the Jabatan’s values. Click Simpan button.

b.       When the process is successful, success message will be shown:

c.       Check the TMAHASISWA table, new record with Boby Siswanto values will be inserted.

d.       Check the TMHS_UKM table, it should be contains some values:

That’s all for advanced form development (nested form with tabs) on Oracle APEX tutorial. You should be able to create one process divided into several forms.

Thank You - Bobsis