A relentless commitment to excellence, we specialize in crafting tailored solutions that fuse cutting-edge technology with insightful strategy.
Read and understand.
1. Create only one sequence
2. Create all table, history and trigger
1. create table
2. create history table
3. create triggers
3. Detail Page
1. create breadcrumb
2. create Nav Menu button
3. create table report
1. SQL with fix column name and order by created desc
2. report with extra link to edit page
1. set link target items
1. page mode
2. give id to edit page
4. create new button for new row
1. color: green
2. position:top left
3. link to new edit page
1. set link target items
1. page mode
4. Edit page
1. create standard region for fix items
1. id
2. mode
2. create sub region
1. left regin
1. create all fields of table column
2. right region
1. create all standard fields fo audit of table column
3. create 4 Button
1. create
1. color:
2. position:
2. update
1. color:
2. position:
3. delete
1. color:
2. position:
4. cancel
1. color:
2. position:
4. create page forward
1. create new row
1. go to detail page, if create button clicked
2. delete row
1. go to detail page, if delete button clicked
5. create new report region for history table
1. sql with fix column name
6. create popup region for delete confirmation
1. create remark item
2. create button to confirm
1. color:
2. position:
5. procedure
1. page load
1. get audit data
2. get row data
2. click button
1. set procedure
1. cal set procedure
2. get procedure
1. call get procedure
This instruction will help you to do the work in easy standard format.
1 2 3 4 5 6 7 8 9 | /*=================================== TEST_SEQ ============================================*/CREATE SEQUENCE TEST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; |
1 2 3 4 5 6 7 8 9 10 11 12 13 | /*=================================== 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | /*=================================== 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 2 3 4 5 6 7 8 9 | 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 ); |
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');
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | /*=================================== 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; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /*================================== 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 ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | /*================================== 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; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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 ); ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | /*================================== 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 ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | /*================================== 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; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 ); ); |
Name of Breadcrumb: test_table detail
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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_table1 |
create like this:
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
define 3 submit process for 3 buttons
1 2 3 4 5 | test_pkg.set_test_table( pi_mode => 'NEW' , pi_... => :P11_REMARK , pi_.. => :P11_NAME ); |