Read Me
Hello,
welcom to our Company. Plese read and try to understand.
Table of Contents
Here you have a overview of all topics.
This instruction will help you to do the work in easy standard format.
1 Create: Sequenz - Table - History Table - Trigger
1.1 Sequenz
/*=============================
====== TEST_SEQ ===============
=============================*/
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE
;
1.2 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 DEFAULT sysdate , created_by varchar2(4000) DEFAULT 'DEFAULT' , updated TIMESTAMP DEFAULT sysdate , updated_by varchar2(4000) DEFAULT 'DEFAULT' , valid_from TIMESTAMP DEFAULT sysdate , valid_to TIMESTAMP DEFAULT sysdate+99999 , deleted_yn varchar2(4) DEFAULT 'NO' );
1.3 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 DEFAULT sysdate , created_by varchar2(4000) DEFAULT 'DEFAULT' , updated TIMESTAMP DEFAULT sysdate , updated_by varchar2(4000) DEFAULT 'DEFAULT' , valid_from TIMESTAMP DEFAULT sysdate , valid_to TIMESTAMP DEFAULT sysdate+99999 , deleted_yn varchar2(4) DEFAULT 'NO' , h_created TIMESTAMP DEFAULT sysdate );
1.4 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;
2. Procedure - Submit process - set test_table procedure
2.1 Set test_table [Specification]
create or replace PACKAGE TEST_GET_SET AS -- this is the packge, in this pkg you can put many procedure /*============================= ===== 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 -- 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 default null ); END ABO_GET_SET;
2.2 Set test_table [Body]
create or replace PACKAGE BODY TEST_GET_SET AS
-- this is the packge BODY, in this pkg you can put many procedure
/*=============================
===== 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
-- 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 default null
)
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;
END ABO_GET_SET;
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
);
);
3. Procedure - Page load - GET test_table procedure
3.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.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.3 Get 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. Get adit data [Specification]
4.1 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 );
4.2 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;
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
);
