Advanced Form (Nested Form) development with Oracle APEX

This article will discuss about advanced form (nested form) development with Oracle APEX. This article gives an example about creating nested form on student registration process. We will use 3 tables based on following Entity Relationship Diagram (ERD):

Here is the mockup design of the input form that we’re going to create:

Form Pilih Mata Kuliah				atas
NIM	<Display Only (sesuai login)>	Semester Berjalan	<Select List (1-8)>
NAMA	<Display Only (sesuai login)>
Tgl Lahir	<Display Only (sesuai login)>	<Proses (button)>
JK	<Display Only (sesuai login)>
Daftar Mata Kuliah Semester <select list (1-6)>				bawah
NO	Kode	Mata Kuliah	Status
1	MI3001	Dasar PABD	<check box>
2	MI3002	PABD Lanjut	<check box>
3	MI3003	Proyek PABD	<check box>

Here are step by steps for creating the form:

1.       Create 3 tables based on the stated ER Diagram (XMAHASISWA, XMATAKULIAH, XMEMILIH)

a.       XMAHASISWA {#nim,nama,tgllahir,jk}

b.       XMATAKULIAH {#kdmatkul,matkul,semester,sks}

c.       XMEMILIH {tgl_perwalian,semester_berjalan,ket,@nim,@kdmatkul}

2.       Create new database application with one blank page.

3.       Add one HTML region namely atas

a.       Add some items as follows:

b.       On P8_NIM, fill this code on Default :

&APP_USER.

c.       On P8_NAMA, fill these codes on Default:

d.       On P8_TGLLAHIR, fill these codes on default:

e.       On P8_JK, fill these codes on default:

f.        On P8_SMTBERJALAN, create a static list of values

g.       Run the Application it should be looks like this (login if needed):

4.       Edit the page, add HTML region namely bawah

a.       At User Interface areas, choose atas(10) as Parent Region with No Template

b.       Add a Select List item (P8_DAFTARMATKUL) to bawah’s region

c.       Run the Page, it should be looks like this:

5.       Create a stored procedure (function) namely cek_status. Type following codes:

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

as

                hasil varchar2(128);

begin

                select ket into hasil from xmemilih where kdmatkul=p1 and nim=p2;

                return hasil;

end;

6.       Add one Report Region (Classic)

a.       Type following Source SQL:

SELECT a.kdmatkul,matkul,sks,

                NVL(cek_status(a.kdmatkul,:APP_USER),APEX_ITEM.CHECKBOX2(1,a.kdmatkul,'CHECKED')) as status,DECODE(cek_status(a.kdmatkul,:APP_USER),'BARU',tgl_perwalian,null) as tgl_perwalian

FROM xmatakuliah a

                LEFT OUTER JOIN xmemilih b ON a.kdmatkul=b.kdmatkul

WHERE semester = :P8_DAFTARMATKUL;

b.       Complete the report creation process

c.       Edit that report region, click on Report Attributes tab then edit on column STATUS

Make sure the Display as on Column attributes is Standard Report Column, click on  Apply Changes

7.       Create one PL/SQL Process namely simpan_matkul

a.       Type the following source code:

BEGIN

  FOR i IN 1..apex_application.g_f01.count loop

                INSERT INTO xmemilih(tgl_perwalian,semester_berjalan,ket,nim,kdmatkul)

                VALUES(sysdate,:P8_SMTBERJALAN,'BARU',:APP_USER,apex_application.g_f01(i));

  END LOOP;         

END;

b.       Isikan pada Success Messages ‘Berhasil’

8.       Create a Dynamic Action namely prosesmatkul

a.       When

b.       True Action = Submit Page

c.       Uncheck Fire On Load

9.       Create another Dynamic Action namely reset_daftarmatkul

a.       When

b.       True Action = Refresh

c.       Affected Elements

10.   Create another Dynamic Action namely reset_totalsks

a.       When

b.       True Action = Submit Page

11.   Edit P8_TOTALSKS Item, follow these rules

a.       Source Type = SQL Query (return single value)

b.       Source Expression :

SELECT SUM(sks)

                FROM xmatakuliah a

                                JOIN xmemilih b ON a.kdmatkul=b.kdmatkul

WHERE NIM= :P8_NIM  AND SEMESTER_BERJALAN= :P8_SMTBERJALAN;

c.       Apply Changes

12.   Run the application

a.       Check the XMAHASISWA table data

b.       Check the XMATAKULIAH table data

c.       Run the pade, if we choose 5 on Daftar Mata Kuliah, the grid data will be changed and showing only Matkul available on 5 semester. If there’s found checked Status means the Matkul hasn’t selected yet, otherwise it already taken. Click on the Proses button

d.       The ‘Berhasil’ message should be shown and the data should be inserted and the views will be changed automatically.

e.       Check the XMEMILIH table on Object Browser

There will be found new records based on the inserted data on the form.

That’s it the step by step processes for creating nested nor on Oracle APEX. Hopefully I will be able to create the video in a close time.

Thank You - Bobsis