Saturday, April 28, 2018

Advanced Form (Nested Form) development with Oracle APEX

Leave a Comment

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

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment