Software
Instructions
Read Me
Read and understand.
All-In-One
- Create only one sequence
- Create all table, history and trigger
- create table
- create history table
- create triggers
- Detail Page
- create breadcrumb
- create Nav Menu button
- create table report
- SQL with fix column name and order by created desc
- report with extra link to edit page
- set link target items
- page mode
- give id to edit page
- set link target items
- create new button for new row
- color: green
- position: top left
- link to new edit page
- set link target items
- page mode
- set link target items
- Edit page
- create standard region for fix items
- id
- mode
- create sub region
- left regin
- create all fields of table column
- right region
- create all standard fields fo audit of table column
- left regin
- create 4 Button
- create
- color:
- position:
- update
- color:
- position:
- delete
- color:
- position:
- cancel
- color:
- position:
- create
- create page forword
- create new row
- go to detail page, if create button clicked
- delete row
- go to detail page, if delete button clicked
- create new row
- create new report region for history table
- sql with fix column name
- create popup region for delete confirmation
- create remark item
- create button to confirm
- color:
- position:
- create standard region for fix items
- procedure
- page load
- get audit data
- get row data
- click button
- set procedure
- cal set procedure
- get procedure
- call get procedure
- set procedure
- page load
This instruction will help you to do the work in easy standard format.
1. Create Global - Sequence & Audit
1.1 Sequence
/*=============================
====== TEST_SEQ ===============
=============================*/
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE
;
1.2 Get adit data [Specification]
/*============================= ====== audit_data ==PKS======== =============================*/ procedure audit_data( pi_type in varchar2 -- audit , po_created out varchar2 , po_created_by out varchar2 , po_updated out varchar2 , po_updated_by out varchar2 , po_valid_from out varchar2 , po_valid_to out varchar2 );
1.3 Get adit data [Body]
/*=============================
====== audit_data ==PKG========
=============================*/
procedure audit_data(
pi_type in varchar2
-- audit
, po_created out varchar2
, po_created_by out varchar2
, po_updated out varchar2
, po_updated_by out varchar2
, po_valid_from out varchar2
, po_valid_to out varchar2
)
as
begin
-- Get audit data for new row
if pi_type = 'GET_AUDIT' then
po_created := to_char(sysdate, 'DD.MM.YYYY HH24:MI');
po_created_by := v('APP_USER');
po_updated := to_char(sysdate, 'DD.MM.YYYY HH24:MI');
po_updated_by := v('APP_USER');
po_valid_from := to_char(sysdate, 'DD.MM.YYYY HH24:MI');
po_valid_to := '31.12.2999';
end if;
end;
1.4 Get audit data [APEX]
test_pkg.audit_data (
pi_type => 'GET_AUDIT'
, po_created => :P50_CREATED
, po_created_by => :P50_CREATED_BY
, po_updated => :P50_UPDATED
, po_updated_by => :P50_UPDATED_BY
, po_valid_from => :P50_VALID_FROM
, po_valid_to => :P50_VALID_TO
);
2. Create all table, history and trigger
2.1 Table
drop table test_table ; /*============================= ====== test_table ============= =============================*/ create table test_table ( id NUMBER DEFAULT TEST_SEQ.NEXTVAL PRIMARY KEY -- table column , name varchar2(4000) not null , test_1 varchar2(4000) DEFAULT null , test_2 NUMBER DEFAULT null , test_3 TIMESTAMP DEFAULT null -- foreign key , fk_reference_table number not null -- audit , remark varchar2(4000) DEFAULT null , created TIMESTAMP not null , created_by varchar2(4000) not null , updated TIMESTAMP not null , updated_by varchar2(4000) not null , valid_from TIMESTAMP not null , valid_to TIMESTAMP not null , deleted_yn varchar2(4) DEFAULT 'NO' );
2.2 History table
drop table h_test_table ; /*============================= ====== h_test_table =========== =============================*/ create table h_test_table ( h_id NUMBER DEFAULT TEST_SEQ.NEXTVAL PRIMARY KEY id NUMBER not null -- table column , name varchar2(4000) not null , test_1 varchar2(4000) DEFAULT null , test_2 NUMBER DEFAULT null , test_3 TIMESTAMP DEFAULT null -- foreign key , fk_reference_table number not null -- audit , remark varchar2(4000) DEFAULT null , created TIMESTAMP not null , created_by varchar2(4000) not null , updated TIMESTAMP not null , updated_by varchar2(4000) not null , valid_from TIMESTAMP not null , valid_to TIMESTAMP not null , deleted_yn varchar2(4) not null , h_created TIMESTAMP DEFAULT sysdate );
2.3 Trigger
/*=============================
====== trg_test_table =========
=============================*/
CREATE OR REPLACE TRIGGER trg_test_table
AFTER Insert or update
ON test_table
FOR EACH ROW
DECLARE
v_check_get_old_h_id number;
v_get_old_h_id number;
BEGIN
-- check if old history row exits
select count(*)
into v_check_get_old_h_id
from dual where exists(select *
from h_test_table
where id = :new.id
)
;
--if yes
if v_check_get_old_h_id = 1 then
-- get last max id
select max(h_id)
into v_get_old_h_id
from h_test_table
where id = :new.id
;
-- set valid to, to the max id row
update h_test_table
set valid_to = :new.valid_from
where h_id = v_get_old_h_id
;
end if;
INSERT INTO h_test_table(
id
-- table column
, name
, test_1
, test_2
, test_3
-- foreign key
, fk_reference_table
-- audit
, remark
, created
, created_by
, updated
, updated_by
, valid_from
, valid_to
, deleted_yn )
VALUES (
:new.id
-- table column
, :new.name
, :new.test_1
, :new.test_2
, :new.test_3
-- foreign key
, :new.fk_reference_table
-- audit
, :new.remark
, :new.created
, :new.created_by
, :new.updated
, :new.updated_by
, :new.valid_from
, :new.valid_to
, :new.deleted_yn )
;
END;
3. procedure
3.1 Page load - GET test_table procedure
3.1.1 Get test_table [Specification]
/*============================= ===== get_test_table ==PKS===== =============================*/ procedure get_test_table( pi_id in TEST_TABLE.id%TYPE -- table column , po_name out TEST_TABLE.name%TYPE , po_test_1 out TEST_TABLE.test_1%TYPE , po_test_2 out TEST_TABLE.test_2%TYPE , po_test_3 out TEST_TABLE.test_3%TYPE -- foreign key , po_fk_reference_table out TEST_TABLE.po_fk_reference_table%TYPE -- audit , po_remark out varchar2 , po_created out varchar2 , po_created_by out varchar2 , po_updated out varchar2 , po_updated_by out varchar2 , po_valid_from out varchar2 , po_valid_to out varchar2 , po_deleted_yn out varchar2 );
3.1.2 Get test_table [Body]
/*=============================
===== get_test_table ==PKG=====
=============================*/
procedure get_test_table(
pi_id in TEST_TABLE.id%TYPE
-- table column
, po_name out TEST_TABLE.name%TYPE
, po_test_1 out TEST_TABLE.test_1%TYPE
, po_test_2 out TEST_TABLE.test_2%TYPE
, po_test_3 out TEST_TABLE.test_3%TYPE
-- foreign key
, po_fk_reference_table out TEST_TABLE.po_fk_reference_table%TYPE
-- audit
, po_remark out varchar2
, po_created out varchar2
, po_created_by out varchar2
, po_updated out varchar2
, po_updated_by out varchar2
, po_valid_from out varchar2
, po_valid_to out varchar2
, po_deleted_yn out varchar2
)
as
begin
select name
, test_1
, test_2
, test_3
-- foreign key
, fk_reference_table
-- audit
, remark
, to_char(created, 'DD.MM.YYYY HH24:MI')
, created_by
, to_char(updated, 'DD.MM.YYYY HH24:MI')
, updated_by
, to_char(valid_from, 'DD.MM.YYYY HH24:MI')
, to_char(valid_to, 'DD.MM.YYYY HH24:MI')
, deleted_yn
into po_name
, po_test_1
, po_test_1
, po_test_1
-- foreign key
, po_fk_reference_table
-- audit
, po_remark
, po_created
, po_created_by
, po_updated
, po_updated_by
, po_valid_from
, po_valid_to
, po_deleted_yn
from test_table
where id = pi_id
;
end get_test_table;
3.1.3 Get test_table [APEX]
test_pkg.get_test_Table (
pi_id => :P50_ID
-- table column
, po_name => :P50_NAME
, po_test_1 => :P50_TEST_1
, po_test_2 => :P50_TEST_2
, po_test_3 => :P50_TEST_3
-- foreign key
, po_fk_reference_table => :P50_FK_REFERENCE_TABLE
--audit
, po_created => :P50_CREATED
, po_created_by => :P50_CREATED_BY
, po_updated => :P50_UPDATED
, po_updated_by => :P50_UPDATED_BY
, po_valid_from => :P50_VALID_FROM
, po_valid_to => :P50_VALID_TO
, po_deleted_yn => :P50_DELETED_YN
);
);
3.2 Submit process - set test_table procedure
3.2.1 Set test_table [Specification]
/*============================= ===== set_test_table ==PKS===== =============================*/ procedure set_test_table( pi_id in TEST_TABLE.id%TYPE default null , po_id out TEST_TABLE.id%TYPE , pi_mode in varchar2 default null , po_mode out varchar2 default null -- table column , pi_name in TEST_TABLE.name%TYPE default null , pi_test_1 in TEST_TABLE.test_1%TYPE default null , pi_test_1 in TEST_TABLE.test_2%TYPE default null , pi_test_1 in TEST_TABLE.test_3%TYPE default null -- foreign key , pi_fk_reference_table in TEST_TABLE.fk_reference_table%TYPE -- audit , pi_remark in TEST_TABLE.remark%TYPE );
3.2.2 Set test_table [Body]
/*=============================
===== set_test_table ==PKG=====
=============================*/
procedure set_test_table(
pi_id in TEST_TABLE.id%TYPE default null
, po_id out TEST_TABLE.id%TYPE
, pi_mode in varchar2 default null
, po_mode out varchar2 default null
-- table column
, pi_name in TEST_TABLE.name%TYPE default null
, pi_test_1 in TEST_TABLE.test_1%TYPE default null
, pi_test_1 in TEST_TABLE.test_2%TYPE default null
, pi_test_1 in TEST_TABLE.test_3%TYPE default null
-- foreign key
, pi_fk_reference_table in TEST_TABLE.fk_reference_table%TYPE
-- audit
, pi_remark in TEST_TABLE.remark%TYPE
)
as
begin
if pi_mode = 'NEW' then
insert into test_table (
name
, test_1
, test_2
, test_3
-- foreign key
, fk_reference_table
-- audit
, remark
, created
, created_by
, updated
, updated_by
, valid_from
, valid_to
) values (
pi_name
, pi_test_1
, pi_test_2
, pi_test_3
-- foreign key
, pi_fk_reference_table
-- audit
, pi_remark
, sysdate
, v('APP_USER')
, sysdate
, v('APP_USER')
, sysdate
, to_date('31.12.2999', 'DD.MM.YYYY') )
-- return id of row
return id into po_id
;
elsif pi_mode = 'EDIT' then
update test_table
set name = pi_name
, test_1 = pi_test_1
, test_2 = pi_test_2
, test_3 = pi_test_3
-- foreign key
, fk_reference_table= pi_fk_reference_table
-- audit
, remark = pi_remark
, updated = sysdate
, updated_by = v('APP_USER')
, valid_from = sysdate
where id = pi_id
;
-- return id of row
po_id := pi_id;
elsif pi_mode = 'DELETE' then
update test_table
set deleted_yn = 'YES'
-- audit
, remark = pi_remark
, updated = sysdate
, updated_by = v('APP_USER')
, valid_from = sysdate
, valid_to = sysdate
where id = pi_id
;
-- return id of row
po_id := pi_id;
end if;
-- return to edit page mode
po_mode := 'EDIT';
end set_test_table;
3.2.3 Set test_table [APEX]
test_pkg.set_test_Table (
pi_id => :P50_ID
, po_id => :P50_ID
, pi_mode => :P50_MODE
, po_mode => :P50_MODE
-- table column
, pi_name => :P50_NAME
, pi_test_1 => :P50_TEST_1
, pi_test_1 => :P50_TEST_2
, pi_test_1 => :P50_TEST_3
-- foreign key
, pi_fk_reference_table => :P50_FK_REFERENCE_TABLE
-- audit
, pi_remark => :P50_REMARK
);
);
4. Detail Page
4.1 Breadcrumb
Name of Breadcrumb: test_table detail
4.2 Nav Menu Button
Name of Menu Button: test_table
4.3 Table Report
select ID as ID
-- table column
, name as Name
, test_1 as test_1
, test_2 as test_2
, test_3 as test_3
-- audit
, case when length(remark) > 100 then
'... ' || SUBSTR(remark, 100)
else
remark end as Anmerkung
, to_char(created, 'DD.MM.YYYY HH24:MI:SS') as Erstellt_am
, created_by as Erstellt_von
, to_char(updated, 'DD.MM.YYYY HH24:MI:SS') as Aktualisiert_am
, updated_by as Aktualisiert_von
, to_char(valid_from, 'DD.MM.YYYY HH24:MI:SS') as Gueltig_ab
, to_char(valid_to, 'DD.MM.YYYY HH24:MI:SS') as Gueltig_bis
, deleted_yn as geloescht
from test_table
order by updated DESC;
Report with extra Link to edit page
Click to report and open the attributes TAB
4.4 Button
Name of Button: Create
5. Edit Page
5.1 create region, button and items
create like this:
5.2 Get procedure
create like this:
5.2.1 Get audit data – copy and paste the apex Call code
5.2.2 Get data (data of test_table) – copy and paste the apex Call code
5.3 Submit process
define 3 submit process for 3 buttons
5.3.1 Submit for create button
test_pkg.set_test_table(
pi_mode => 'NEW'
, pi_... => :P11_REMARK
, pi_.. => :P11_NAME
);
