set define '^' set verify off prompt ...create worksheet objects Rem Copyright (c) Oracle Corporation 1999 - 2007. All Rights Reserved. Rem Rem NAME Rem worksheet_tab.sql Rem Rem DESCRIPTION Rem Creation script for worksheet tables, triggers, and sequences. Rem Rem Rem MODIFIED (MM/DD/YYYY) Rem madelfio 03/30/2007 - created Rem jstraub 04/04/2007 - added wwv_flow_worksheet_geocache Rem mhichwa 04/05/2007 - improved trigger wwv_flow_ws_col_grp_trig Rem mhichwa 04/06/2007 - added worksheet description Rem mhichwa 04/06/2007 - added folders table Rem mhichwa 04/06/2007 - added web pages tables Rem mhichwa 04/09/2007 - Added display sequences and page_position for web pages Rem mhichwa 04/09/2007 - Added load order to rows table Rem madelfio 04/12/2007 - Added name and filter_expr_type columns to wwv_flow_worksheet_rpts table Rem madelfio 04/17/2007 - Added highlighting columns for reports, owner column for folders and webpages Rem mhichwa 04/17/2007 - Added worksheet docs Rem jstraub 04/17/2007 - Added name and description to wwv_flow_worksheet_docs Rem mhichwa 04/17/2007 - Added display_width and display_height columns Rem mhichwa 04/20/2007 - Expanded definition of worksheet pk to include flow_id, name, owner, folder_id, security_group_id Rem madelfio 04/25/2007 - Added wwv_flow_worksheet_highlights table Rem madelfio 04/27/2007 - Added display_sequence to wwv_flow_worksheet_col_grps Rem madelfio 04/30/2007 - Updated wwv_flow_privs table structure Rem mhichwa 05/01/2007 - Added History support Rem mhichwa 05/02/2007 - Expanded definition of list of values support Rem mhichwa 05/04/2007 - Added links Rem madelfio 05/09/2007 - Added column_identifier to worksheet_columns table Rem madelfio 05/09/2007 - Added parent timestamp updating to triggers Rem mhichwa 05/09/2007 - Added new.column_identifier to trigger Rem madelfio 05/09/2007 - Added COMPUTED column logic Rem mhichwa 05/09/2007 - Added SQL Query to worksheet Rem mhichwa 05/11/2007 - Added SQL PK options Rem mhichwa 05/15/2007 - Added shared_columns Rem mhichwa 05/16/2007 - Added ACL to domain of status column Rem mhichwa 05/16/2007 - Added row filter columns Rem mhichwa 05/16/2007 - Added distinct_value_filter Rem mhichwa 05/17/2007 - Added others_may_edit, others_may_view Rem mhichwa 05/17/2007 - Added control break columns to rpt table, added sync_column_label Rem mhichwa 05/17/2007 - Added description to folders table, added description to web pages table Rem madelfio 05/17/2007 - Default column view sharing to true Rem madelfio 05/17/2007 - Added view wwv_flow_worksheet_formats Rem mhichwa 05/23/2007 - Changed computation of distinct value filter Rem madelfio 05/24/2007 - Added display_rows to wwv_flow_worksheet_rpts Rem madelfio 05/25/2007 - Added name column to wwv_flow_worksheet_highlights Rem madelfio 05/25/2007 - Added logic to wwv_flow_worksheet_rpts_trig to maintain filter conditions Rem madelfio 05/30/2007 - Fixed wwv_flow_worksheet_rpts_trig to maintain filter conditions when clearing filters Rem madelfio 05/30/2007 - Added index on wwv_flow_worksheet_rows (worksheet_id) Rem mhichwa 05/30/2007 - Strip chr 13 on insert Rem madelfio 06/06/2007 - Added enabled column to wwv_flow_worksheet_highlights Rem madelfio 06/07/2007 - Updated several tables/triggers/views to sync with current database objects Rem madelfio 07/05/2007 - Added columns to support worksheets on a specific database table Rem madelfio 07/23/2007 - Added more columns to support table-based worksheets Rem cbcho 07/23/2007 - Added wwv_flow_worksheet_conditions Rem cbcho 07/26/2007 - Added view apex_worksheet_rpts Rem madelfio 07/27/2007 - Added condition_display column to wwv_flow_worksheet_conditions table Rem madelfio 07/27/2007 - Added status column to wwv_flow_worksheet_rpts table Rem madelfio 08/06/2007 - Removed trigger wwv_flow_worksheets_trig_ai Rem madelfio 08/09/2007 - Added detail_link column to wwv_flow_worksheets Rem madelfio 08/30/2007 - Merged into tab.sql, trigger.sql, and apex_views.sql ---------------------------------------- -- remove all worksheet database objects -- drop table wwv_flow_worksheets cascade constraints; drop table wwv_flow_worksheet_rows cascade constraints; drop table wwv_flow_worksheet_col_groups cascade constraints; drop table wwv_flow_worksheet_lovs cascade constraints; drop table wwv_flow_worksheet_lov_entries cascade constraints; drop table wwv_flow_worksheet_columns cascade constraints; drop table wwv_flow_worksheet_col_unq cascade constraints; drop table wwv_flow_worksheet_stick cascade constraints; drop table wwv_flow_worksheet_links cascade constraints; drop table wwv_flow_worksheet_docs cascade constraints; drop table wwv_flow_worksheet_privs cascade constraints; drop table wwv_flow_worksheet_rpts cascade constraints; drop table wwv_flow_folders cascade constraints; drop table wwv_flow_web_pages cascade constraints; drop table wwv_flow_web_pg_regions cascade constraints; drop table wwv_flow_web_pg_list_entries cascade constraints; drop table wwv_flow_worksheet_conditions cascade constraints; drop table wwv_flow_worksheet_history cascade constraints; drop table wwv_flow_workspace_geocache cascade constraints; drop sequence wwv_flow_worksheet_seq; -------------------------------------- -- Data Store Database Object Creation -- create sequence wwv_flow_worksheet_seq; ---------------------- -- wwv_flow_worksheets -- create table wwv_flow_worksheets ( id number not null, flow_id number not null, name varchar2(255) not null, worksheet_version_id number, folder_id number, -- base table base_table_or_view varchar2(4000), base_pk1 varchar2(30), base_pk2 varchar2(30), base_pk3 varchar2(30), -- status status varchar2(30) constraint wwv_flow_ws_status_ck check (status in ( 'AVAILABLE_FOR_OWNER', 'NOT_AVAILABLE', 'AVAILABLE', 'ACL')), expiration_date date, is_template varchar2(1) constraint wwv_flow_ws_is_template_ck check (is_template in ( 'Y','N')), -- worksheet level privileges allow_query varchar2(1) constraint wwv_flow_ws_allow_query_ck check (allow_query in ( 'Y','N')), allow_query_expires date, allow_insert varchar2(1) constraint wwv_flow_ws_allow_insert_ck check (allow_insert in ( 'Y','N')), allow_insert_expires date, allow_update varchar2(1) constraint wwv_flow_ws_allow_update_ck check (allow_update in ( 'Y','N')), allow_update_expires date, allow_delete varchar2(1) constraint wwv_flow_ws_allow_delete_ck check (allow_delete in ( 'Y','N')), allow_delete_expires date, -- runtime functionality allow_advanced_query varchar2(1) constraint wwv_flow_ws_advanced_query_ck check (allow_advanced_query in ( 'Y','N')), allow_pdf_printing varchar2(1) constraint wwv_flow_ws_pdf_printing_ck check (allow_pdf_printing in ( 'Y','N')), allow_csv_download varchar2(1) constraint wwv_flow_ws_csv_download check (allow_csv_download in ( 'Y','N')), allow_filtering varchar2(1) constraint wwv_flow_ws_filtering_ck check (allow_filtering in ( 'Y','N')), allow_group_reporting varchar2(1) constraint wwv_flow_ws_group_rep_ck check (allow_group_reporting in ( 'Y','N')), allow_sorting varchar2(1) constraint wwv_flow_ws_sorting_ck check (allow_sorting in ( 'Y','N')), allow_data_loading varchar2(1) constraint wwv_flow_ws_csv_data_load_ck check (allow_data_loading in ( 'Y','N')), -- relationships parent_worksheet_id number, on_delete varchar2(30) constraint wwv_flow_ws_on_del_ck check (on_delete in ( 'CASCADE','RESTRICT','CASCADE_WITH_CONFIRM')), -- sql_query clob, sql_allow_refresh varchar2(1) constraint wwv_flow_ws_refresh_ck check (sql_allow_refresh in ( 'Y','N')), sql_schema varchar2(255), sql_pk_01 varchar2(255), sql_pk_02 varchar2(255), sql_pk_03 varchar2(255), -- description varchar2(4000), owner varchar2(255), row_view_priv varchar2(255), row_edit_priv varchar2(255), detail_link varchar2(4000), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / alter table wwv_flow_worksheets add constraint wwv_flow_worksheets_pk primary key(id) / alter table wwv_flow_worksheets add constraint wwv_flow_worksheets_uk unique(flow_id, name, owner, folder_id, security_group_id) / create or replace trigger wwv_flow_worksheets_trig before insert or update on wwv_flow_worksheets for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- set owner -- if :new.owner is null then :new.owner := :new.created_by; end if; -- -- set status -- if :new.status is null then :new.status := 'AVAILABLE_FOR_OWNER'; end if; -- -- maintain column values -- if :new.worksheet_version_id is null then :new.worksheet_version_id := 1; end if; if :new.flow_id is null then :new.flow_id := wwv_flow.g_flow_id; end if; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors ---------- -- History -- create table wwv_flow_worksheet_history ( row_id number not null, worksheet_id number not null, -- column_name varchar2(255), old_value varchar2(4000), new_value varchar2(4000), -- application_user_id varchar2(255), change_date date, security_group_id number not null ) / create index wwv_flow_worksheet_hist_01 on wwv_flow_worksheet_history (worksheet_id, row_id) / -------------------------- -- wwv_flow_worksheet_rows -- create table wwv_flow_worksheet_rows ( id number not null, worksheet_id number not null, unique_value varchar2(255), c001 varchar2(4000), c002 varchar2(4000), c003 varchar2(4000), c004 varchar2(4000), c005 varchar2(4000), c006 varchar2(4000), c007 varchar2(4000), c008 varchar2(4000), c009 varchar2(4000), c010 varchar2(4000), c011 varchar2(4000), c012 varchar2(4000), c013 varchar2(4000), c014 varchar2(4000), c015 varchar2(4000), c016 varchar2(4000), c017 varchar2(4000), c018 varchar2(4000), c019 varchar2(4000), c020 varchar2(4000), c021 varchar2(4000), c022 varchar2(4000), c023 varchar2(4000), c024 varchar2(4000), c025 varchar2(4000), c026 varchar2(4000), c027 varchar2(4000), c028 varchar2(4000), c029 varchar2(4000), c030 varchar2(4000), c031 varchar2(4000), c032 varchar2(4000), c033 varchar2(4000), c034 varchar2(4000), c035 varchar2(4000), c036 varchar2(4000), c037 varchar2(4000), c038 varchar2(4000), c039 varchar2(4000), c040 varchar2(4000), c041 varchar2(4000), c042 varchar2(4000), c043 varchar2(4000), c044 varchar2(4000), c045 varchar2(4000), c046 varchar2(4000), c047 varchar2(4000), c048 varchar2(4000), c049 varchar2(4000), c050 varchar2(4000), n001 number, n002 number, n003 number, n004 number, n005 number, n006 number, n007 number, n008 number, n009 number, n010 number, n011 number, n012 number, n013 number, n014 number, n015 number, n016 number, n017 number, n018 number, n019 number, n020 number, n021 number, n022 number, n023 number, n024 number, n025 number, n026 number, n027 number, n028 number, n029 number, n030 number, n031 number, n032 number, n033 number, n034 number, n035 number, n036 number, n037 number, n038 number, n039 number, n040 number, n041 number, n042 number, n043 number, n044 number, n045 number, n046 number, n047 number, n048 number, n049 number, n050 number, d001 date, d002 date, d003 date, d004 date, d005 date, d006 date, d007 date, d008 date, d009 date, d010 date, d011 date, d012 date, d013 date, d014 date, d015 date, d016 date, d017 date, d018 date, d019 date, d020 date, d021 date, d022 date, d023 date, d024 date, d025 date, d026 date, d027 date, d028 date, d029 date, d030 date, d031 date, d032 date, d033 date, d034 date, d035 date, d036 date, d037 date, d038 date, d039 date, d040 date, d041 date, d042 date, d043 date, d044 date, d045 date, d046 date, d047 date, d048 date, d049 date, d050 date, document blob, clob001 clob, parent_row_id number, owner varchar2(255), geocode varchar2(512), load_order number, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / alter table wwv_flow_worksheet_rows add constraint wwv_flow_worksheet_rows_pk primary key(id) / alter table wwv_flow_worksheet_rows add constraint wwv_flow_worksheet_rows_fk foreign key( worksheet_id ) references wwv_flow_worksheets(id) on delete cascade / create index wwv_flow_worksheet_rows_idx1 on wwv_flow_worksheet_rows(worksheet_id) / create or replace trigger wwv_flow_worksheet_row_trig before insert or update on wwv_flow_worksheet_rows for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- history -- if updating then if nvl(:old.c001,'mJjOoH') != nvl(:new.c001,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c001', :old.c001, :new.c001, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c002,'mJjOoH') != nvl(:new.c002,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c002', :old.c002, :new.c002, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c003,'mJjOoH') != nvl(:new.c003,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c003', :old.c003, :new.c003, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c004,'mJjOoH') != nvl(:new.c004,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c004', :old.c004, :new.c004, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c005,'mJjOoH') != nvl(:new.c005,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c005', :old.c005, :new.c005, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c006,'mJjOoH') != nvl(:new.c006,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c006', :old.c006, :new.c006, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c007,'mJjOoH') != nvl(:new.c007,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c007', :old.c007, :new.c007, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c008,'mJjOoH') != nvl(:new.c008,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c008', :old.c008, :new.c008, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c009,'mJjOoH') != nvl(:new.c009,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c009', :old.c009, :new.c009, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c010,'mJjOoH') != nvl(:new.c010,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c010', :old.c010, :new.c010, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c010,'mJjOoH') != nvl(:new.c010,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c010', :old.c010, :new.c010, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c011,'mJjOoH') != nvl(:new.c011,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c011', :old.c011, :new.c011, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c012,'mJjOoH') != nvl(:new.c012,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c012', :old.c012, :new.c012, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c013,'mJjOoH') != nvl(:new.c013,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c013', :old.c013, :new.c013, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c014,'mJjOoH') != nvl(:new.c014,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c014', :old.c014, :new.c014, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c015,'mJjOoH') != nvl(:new.c015,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c015', :old.c015, :new.c015, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c016,'mJjOoH') != nvl(:new.c016,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c016', :old.c016, :new.c016, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c017,'mJjOoH') != nvl(:new.c017,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c017', :old.c017, :new.c017, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c018,'mJjOoH') != nvl(:new.c018,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c018', :old.c018, :new.c018, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c019,'mJjOoH') != nvl(:new.c019,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c019', :old.c019, :new.c019, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c020,'mJjOoH') != nvl(:new.c020,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c020', :old.c020, :new.c020, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c021,'mJjOoH') != nvl(:new.c021,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c021', :old.c021, :new.c021, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c022,'mJjOoH') != nvl(:new.c022,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c022', :old.c022, :new.c022, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c023,'mJjOoH') != nvl(:new.c023,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c023', :old.c023, :new.c023, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c024,'mJjOoH') != nvl(:new.c024,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c024', :old.c024, :new.c024, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c025,'mJjOoH') != nvl(:new.c025,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c025', :old.c025, :new.c025, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c026,'mJjOoH') != nvl(:new.c026,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c026', :old.c026, :new.c026, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c027,'mJjOoH') != nvl(:new.c027,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c027', :old.c027, :new.c027, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c028,'mJjOoH') != nvl(:new.c028,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c028', :old.c028, :new.c028, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c029,'mJjOoH') != nvl(:new.c029,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c029', :old.c029, :new.c029, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.c030,'mJjOoH') != nvl(:new.c030,'mJjOoH') then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'c030', :old.c030, :new.c030, sysdate, :new.security_group_id,v('APP_USER')); end if; -- -- numbers -- if nvl(:old.n001,867530731415911111) != nvl(:new.n001,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n001', :old.n001, :new.n001, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n002,867530731415911111) != nvl(:new.n002,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n002', :old.n002, :new.n002, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n003,867530731415911111) != nvl(:new.n003,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n003', :old.n003, :new.n003, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n004,867530731415911111) != nvl(:new.n004,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n004', :old.n004, :new.n004, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n005,867530731415911111) != nvl(:new.n005,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n005', :old.n005, :new.n005, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n006,867530731415911111) != nvl(:new.n006,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n006', :old.n006, :new.n006, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n007,867530731415911111) != nvl(:new.n007,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n007', :old.n007, :new.n007, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n008,867530731415911111) != nvl(:new.n008,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n008', :old.n008, :new.n008, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n009,867530731415911111) != nvl(:new.n009,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n009', :old.n009, :new.n009, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n010,867530731415911111) != nvl(:new.n010,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n010', :old.n010, :new.n010, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n010,867530731415911111) != nvl(:new.n010,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n010', :old.n010, :new.n010, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n011,867530731415911111) != nvl(:new.n011,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n011', :old.n011, :new.n011, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n012,867530731415911111) != nvl(:new.n012,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n012', :old.n012, :new.n012, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n013,867530731415911111) != nvl(:new.n013,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n013', :old.n013, :new.n013, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n014,867530731415911111) != nvl(:new.n014,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n014', :old.n014, :new.n014, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n015,867530731415911111) != nvl(:new.n015,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n015', :old.n015, :new.n015, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n016,867530731415911111) != nvl(:new.n016,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n016', :old.n016, :new.n016, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n017,867530731415911111) != nvl(:new.n017,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n017', :old.n017, :new.n017, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n018,867530731415911111) != nvl(:new.n018,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n018', :old.n018, :new.n018, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n019,867530731415911111) != nvl(:new.n019,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n019', :old.n019, :new.n019, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.n020,867530731415911111) != nvl(:new.n020,867530731415911111) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'n020', :old.n020, :new.n020, sysdate, :new.security_group_id,v('APP_USER')); end if; -- -- dates -- if nvl(:old.d001,to_date('10000101','YYYYMMDD')) != nvl(:new.d001,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd001', :old.d001, :new.d001, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d002,to_date('10000101','YYYYMMDD')) != nvl(:new.d002,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd002', :old.d002, :new.d002, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d003,to_date('10000101','YYYYMMDD')) != nvl(:new.d003,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd003', :old.d003, :new.d003, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d004,to_date('10000101','YYYYMMDD')) != nvl(:new.d004,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd004', :old.d004, :new.d004, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d005,to_date('10000101','YYYYMMDD')) != nvl(:new.d005,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd005', :old.d005, :new.d005, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d006,to_date('10000101','YYYYMMDD')) != nvl(:new.d006,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd006', :old.d006, :new.d006, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d007,to_date('10000101','YYYYMMDD')) != nvl(:new.d007,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd007', :old.d007, :new.d007, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d008,to_date('10000101','YYYYMMDD')) != nvl(:new.d008,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd008', :old.d008, :new.d008, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d009,to_date('10000101','YYYYMMDD')) != nvl(:new.d009,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd009', :old.d009, :new.d009, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d010,to_date('10000101','YYYYMMDD')) != nvl(:new.d010,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd010', :old.d010, :new.d010, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d010,to_date('10000101','YYYYMMDD')) != nvl(:new.d010,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd010', :old.d010, :new.d010, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d011,to_date('10000101','YYYYMMDD')) != nvl(:new.d011,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd011', :old.d011, :new.d011, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d012,to_date('10000101','YYYYMMDD')) != nvl(:new.d012,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd012', :old.d012, :new.d012, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d013,to_date('10000101','YYYYMMDD')) != nvl(:new.d013,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd013', :old.d013, :new.d013, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d014,to_date('10000101','YYYYMMDD')) != nvl(:new.d014,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd014', :old.d014, :new.d014, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d015,to_date('10000101','YYYYMMDD')) != nvl(:new.d015,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd015', :old.d015, :new.d015, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d016,to_date('10000101','YYYYMMDD')) != nvl(:new.d016,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd016', :old.d016, :new.d016, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d017,to_date('10000101','YYYYMMDD')) != nvl(:new.d017,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd017', :old.d017, :new.d017, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d018,to_date('10000101','YYYYMMDD')) != nvl(:new.d018,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd018', :old.d018, :new.d018, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d019,to_date('10000101','YYYYMMDD')) != nvl(:new.d019,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd019', :old.d019, :new.d019, sysdate, :new.security_group_id,v('APP_USER')); end if; if nvl(:old.d020,to_date('10000101','YYYYMMDD')) != nvl(:new.d020,to_date('10000101','YYYYMMDD')) then insert into wwv_flow_worksheet_history (row_id, worksheet_id, column_name, old_value,new_value,change_date,security_group_id,application_user_id) values (:new.id, :new.worksheet_id, 'd020', :old.d020, :new.d020, sysdate, :new.security_group_id,v('APP_USER')); end if; end if; -- -- inserting remove chr 13 -- if instr(:new.c001,chr(13)) > 0 then :new.c001 := replace(:new.c001,chr(13),null); end if; if instr(:new.c002,chr(13)) > 0 then :new.c002 := replace(:new.c002,chr(13),null); end if; if instr(:new.c003,chr(13)) > 0 then :new.c003 := replace(:new.c003,chr(13),null); end if; if instr(:new.c004,chr(13)) > 0 then :new.c004 := replace(:new.c004,chr(13),null); end if; if instr(:new.c005,chr(13)) > 0 then :new.c005 := replace(:new.c005,chr(13),null); end if; if instr(:new.c006,chr(13)) > 0 then :new.c006 := replace(:new.c006,chr(13),null); end if; if instr(:new.c007,chr(13)) > 0 then :new.c007 := replace(:new.c007,chr(13),null); end if; if instr(:new.c008,chr(13)) > 0 then :new.c008 := replace(:new.c008,chr(13),null); end if; if instr(:new.c009,chr(13)) > 0 then :new.c009 := replace(:new.c009,chr(13),null); end if; if instr(:new.c010,chr(13)) > 0 then :new.c010 := replace(:new.c010,chr(13),null); end if; if instr(:new.c011,chr(13)) > 0 then :new.c011 := replace(:new.c011,chr(13),null); end if; if instr(:new.c012,chr(13)) > 0 then :new.c012 := replace(:new.c012,chr(13),null); end if; if instr(:new.c013,chr(13)) > 0 then :new.c013 := replace(:new.c013,chr(13),null); end if; if instr(:new.c014,chr(13)) > 0 then :new.c014 := replace(:new.c014,chr(13),null); end if; if instr(:new.c015,chr(13)) > 0 then :new.c015 := replace(:new.c015,chr(13),null); end if; if instr(:new.c016,chr(13)) > 0 then :new.c016 := replace(:new.c016,chr(13),null); end if; if instr(:new.c017,chr(13)) > 0 then :new.c017 := replace(:new.c017,chr(13),null); end if; if instr(:new.c018,chr(13)) > 0 then :new.c018 := replace(:new.c018,chr(13),null); end if; if instr(:new.c019,chr(13)) > 0 then :new.c019 := replace(:new.c019,chr(13),null); end if; if instr(:new.c020,chr(13)) > 0 then :new.c020 := replace(:new.c010,chr(23),null); end if; if instr(:new.c021,chr(13)) > 0 then :new.c021 := replace(:new.c001,chr(23),null); end if; if instr(:new.c022,chr(13)) > 0 then :new.c022 := replace(:new.c002,chr(23),null); end if; if instr(:new.c023,chr(13)) > 0 then :new.c023 := replace(:new.c003,chr(23),null); end if; if instr(:new.c024,chr(13)) > 0 then :new.c024 := replace(:new.c004,chr(23),null); end if; if instr(:new.c025,chr(13)) > 0 then :new.c025 := replace(:new.c005,chr(23),null); end if; if instr(:new.c026,chr(13)) > 0 then :new.c026 := replace(:new.c006,chr(23),null); end if; if instr(:new.c027,chr(13)) > 0 then :new.c027 := replace(:new.c007,chr(23),null); end if; if instr(:new.c028,chr(13)) > 0 then :new.c028 := replace(:new.c008,chr(23),null); end if; if instr(:new.c029,chr(13)) > 0 then :new.c029 := replace(:new.c009,chr(23),null); end if; if instr(:new.c030,chr(13)) > 0 then :new.c030 := replace(:new.c030,chr(13),null); end if; if instr(:new.c031,chr(13)) > 0 then :new.c031 := replace(:new.c031,chr(13),null); end if; if instr(:new.c032,chr(13)) > 0 then :new.c032 := replace(:new.c032,chr(13),null); end if; if instr(:new.c033,chr(13)) > 0 then :new.c033 := replace(:new.c033,chr(13),null); end if; if instr(:new.c034,chr(13)) > 0 then :new.c034 := replace(:new.c034,chr(13),null); end if; if instr(:new.c035,chr(13)) > 0 then :new.c035 := replace(:new.c035,chr(13),null); end if; if instr(:new.c036,chr(13)) > 0 then :new.c036 := replace(:new.c036,chr(13),null); end if; if instr(:new.c037,chr(13)) > 0 then :new.c037 := replace(:new.c037,chr(13),null); end if; if instr(:new.c038,chr(13)) > 0 then :new.c038 := replace(:new.c038,chr(13),null); end if; if instr(:new.c039,chr(13)) > 0 then :new.c039 := replace(:new.c039,chr(13),null); end if; if instr(:new.c040,chr(13)) > 0 then :new.c040 := replace(:new.c040,chr(13),null); end if; if instr(:new.c041,chr(13)) > 0 then :new.c041 := replace(:new.c041,chr(13),null); end if; if instr(:new.c042,chr(13)) > 0 then :new.c042 := replace(:new.c042,chr(13),null); end if; if instr(:new.c043,chr(13)) > 0 then :new.c043 := replace(:new.c043,chr(13),null); end if; if instr(:new.c044,chr(13)) > 0 then :new.c044 := replace(:new.c044,chr(13),null); end if; if instr(:new.c045,chr(13)) > 0 then :new.c045 := replace(:new.c045,chr(13),null); end if; if instr(:new.c046,chr(13)) > 0 then :new.c046 := replace(:new.c046,chr(13),null); end if; if instr(:new.c047,chr(13)) > 0 then :new.c047 := replace(:new.c047,chr(13),null); end if; if instr(:new.c048,chr(13)) > 0 then :new.c048 := replace(:new.c048,chr(13),null); end if; if instr(:new.c049,chr(13)) > 0 then :new.c049 := replace(:new.c049,chr(13),null); end if; if instr(:new.c050,chr(13)) > 0 then :new.c050 := replace(:new.c050,chr(13),null); end if; -- -- update parent timestamp -- update wwv_flow_worksheets set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.worksheet_id; -- -- set owner -- if :new.owner is null then :new.owner := :new.created_by; end if; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors ---------------------------- -- data store columns -- create table wwv_flow_worksheet_col_groups ( id number not null, worksheet_id number not null, name varchar2(255) not null, description varchar2(4000), display_sequence number, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / alter table wwv_flow_worksheet_col_groups add constraint wwv_flow_worksheet_col_grps_pk primary key(id) / alter table wwv_flow_worksheet_col_groups add constraint wwv_flow_worksheet_col_grws_fk foreign key( worksheet_id ) references wwv_flow_worksheets(id) on delete cascade / alter table wwv_flow_worksheet_col_groups add constraint wwv_flow_worksheet_col_grps_uk unique(worksheet_id, name) / create or replace trigger wwv_flow_ws_col_grp_trig before insert or update on wwv_flow_worksheet_col_groups for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; -- -- -- if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- -- if :new.display_sequence is null then :new.display_sequence := 10; end if; -- -- update parent timestamp -- update wwv_flow_worksheets set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.worksheet_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors -- -- Lists of Values (LOV) -- create table wwv_flow_worksheet_lovs ( id number not null, worksheet_id number not null, name varchar2(255) not null, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / alter table wwv_flow_worksheet_lovs add constraint wwv_flow_worksheet_lovs_pk primary key(id) / alter table wwv_flow_worksheet_lovs add constraint wwv_flow_worksheet_lovs_fk foreign key( worksheet_id ) references wwv_flow_worksheets(id) on delete cascade / alter table wwv_flow_worksheet_lovs add constraint wwv_flow_worksheet_lovs_uk unique(worksheet_id, name) / create or replace trigger wwv_flow_ws_lovs_trig before insert or update on wwv_flow_worksheet_lovs for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; -- -- update stamps -- if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_worksheets set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.worksheet_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors create table wwv_flow_worksheet_lov_entries ( id number not null, worksheet_id number not null, lov_id number not null, display_sequence number not null, entry_text varchar2(255) not null, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / alter table wwv_flow_worksheet_lov_entries add constraint wwv_flow_worksheet_lov_ent_pk primary key(id) / alter table wwv_flow_worksheet_lov_entries add constraint wwv_flow_worksheet_lov_ent_fk foreign key( worksheet_id ) references wwv_flow_worksheets(id) on delete cascade / alter table wwv_flow_worksheet_lov_entries add constraint wwv_flow_worksheet_lov_ent_fk2 foreign key( lov_id ) references wwv_flow_worksheet_lovs(id) on delete cascade / alter table wwv_flow_worksheet_lov_entries add constraint wwv_flow_ws_lov_ents_uk unique(lov_id, entry_text) / create or replace trigger wwv_flow_ws_lov_ents_trig before insert or update on wwv_flow_worksheet_lov_entries for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; -- -- -- if :new.display_sequence is null then :new.display_sequence := 10; end if; -- -- update stamps -- if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_worksheet_lovs set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.lov_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors -- -- Columns -- create table wwv_flow_worksheet_columns ( id number not null, worksheet_id number not null, db_column_name varchar2(30) not null, display_order number not null, group_id number, column_identifier varchar2(2) not null, column_expr varchar2(4000), -- label column_label varchar2(4000) not null, report_label varchar2(4000) not null, sync_form_label varchar2(1) constraint wwv_flow_val_wscol_sync_lab check (sync_form_label in ('Y','N')), -- column filters use_custom varchar2(1), custom_filter varchar2(4000), base_column varchar2(30), allow_filters varchar2(1), -- security others_may_edit varchar2(1), others_may_view varchar2(1), -- display controls column_type varchar2(255) not null constraint wwv_flow_valid_ws_col_type check (column_type in ( 'STRING', 'DATE', 'NUMBER', 'FILE', 'COMPUTED')), display_as varchar2(255) not null constraint wwv_flow_ws_col_disp_as_ck check (display_as in ( 'READONLY', 'TEXT', 'TEXTAREA', 'SELECTLIST', 'CHECKBOX', 'RADIOGROUP', 'POPUP_LOV', 'DATE_PICKER')), max_length number, display_width integer, display_height integer, allow_null varchar2(1) constraint wwv_flow_wscol_allow_null check (allow_null in ('Y','N')), format_mask varchar2(255), distinct_value_filter varchar2(1) constraint wwv_flow_wscol_dvf check (distinct_value_filter in ('Y','N')), -- natural aggs compute_sum varchar2(1), compute_avg varchar2(1), compute_min varchar2(1), compute_max varchar2(1), -- list of values static_lov varchar2(4000), lov_null_text varchar2(255), lov_allow_new_values varchar2(1) constraint wwv_flow_ws_lov_allow_new check (lov_allow_new_values in ('Y','N')), lov_is_distinct_values varchar2(1) constraint wwv_flow_ws_lov_distinct_vals check (lov_is_distinct_values in ('Y','N')), lov_num_columns number, lov_id number, -- computation_type varchar2(255), computation_expr_1 varchar2(4000), computation_expr_2 varchar2(4000), -- validation_type varchar2(255), validation_expr_1 varchar2(4000), validation_expr_2 varchar2(4000), -- -- default values default_value varchar2(4000), default_when varchar2(30), -- help_text varchar2(4000), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_worksheet_columns is 'This table defines the column attributes for each column in a worksheet.'; comment on column wwv_flow_worksheet_columns.db_column_name is 'This stores the actual column name that the column maps to in the wwv_flow_worksheet_rows table.'; alter table wwv_flow_worksheet_columns add constraint wwv_flow_worksheet_columns_pk primary key(id) / alter table wwv_flow_worksheet_columns add constraint wwv_flow_worksheet_columns_fk foreign key( worksheet_id ) references wwv_flow_worksheets(id) on delete cascade / alter table wwv_flow_worksheet_columns add constraint wwv_flow_worksheet_col_grps_fk foreign key( group_id ) references wwv_flow_worksheet_col_groups(id) on delete cascade / alter table wwv_flow_worksheet_columns add constraint wwv_flow_worksheet_columns_uk2 unique(worksheet_id, db_column_name) / create index wwv_flow_worksheet_cols_idx1 on wwv_flow_worksheet_columns (worksheet_id, column_identifier) / create or replace trigger wwv_flow_worksheet_col_trig before insert or update on wwv_flow_worksheet_columns for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- default values -- if :new.report_label is null then :new.report_label := :new.column_label; elsif :new.column_label is null then :new.column_label := :new.report_label; end if; -- -- security columns -- if :new.others_may_edit is null then :new.others_may_edit := 'Y'; end if; if :new.others_may_view is null then :new.others_may_view := 'Y'; end if; -- maintian distinct_value_filter if :new.display_as = 'TEXTAREA' and :new.distinct_value_filter is null then :new.distinct_value_filter := 'N'; else :new.distinct_value_filter := 'Y'; end if; -- use for prototype to get next available col, need to alter logic later to fill gaps if inserting and :new.db_column_name is null then select decode(:new.column_type,'NUMBER','n','DATE','d','c')|| to_char(nvl(max(to_number(substr(db_column_name,2))),0) + 1,'FM009') into :new.db_column_name from wwv_flow_worksheet_columns where worksheet_id = :new.worksheet_id and replace(column_type,'COMPUTED','STRING') = replace(:new.column_type,'COMPUTED','STRING'); end if; if inserting and :new.display_order is null then select nvl(max(display_order),0) + 1 into :new.display_order from wwv_flow_worksheet_columns where worksheet_id = :new.worksheet_id; end if; if inserting then :new.column_identifier := wwv_flow_worksheet_standard.get_next_identifier(:new.worksheet_id); end if; -- -- sync column headings -- if :new.sync_form_label is null then :new.sync_form_label := 'Y'; end if; if :new.sync_form_label = 'Y' then :new.column_label := :new.report_label; end if; -- -- update parent timestamp -- update wwv_flow_worksheets set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.worksheet_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors create table wwv_flow_worksheet_col_unq ( id number not null, worksheet_id number not null, column_id number not null, column_1 number not null, column_2 number, column_3 number, column_4 number, column_5 number, column_6 number, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / create or replace trigger wwv_flow_worksheet_colu_trig before insert or update on wwv_flow_worksheet_col_unq for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_worksheets set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.worksheet_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors comment on table wwv_flow_worksheet_col_unq is 'This table is used to define unique column constraints' / alter table wwv_flow_worksheet_col_unq add constraint wwv_flow_worksheet_col_unq_pk primary key(id) / alter table wwv_flow_worksheet_col_unq add constraint wwv_flow_worksheet_colunq_fk foreign key( column_id ) references wwv_flow_worksheet_columns(id) on delete cascade / --------------------------- -- worksheet Privs create table wwv_flow_worksheet_privs ( id number not null, worksheet_id number not null, application_user varchar2(255), -- worksheet privs view_priv varchar2(1) constraint wwv_flow_worksheet_priv_q_ck check (view_priv in ('Y','N')), edit_priv varchar2(1) constraint wwv_flow_worksheet_priv_e_ck check (edit_priv in ('Y','N')), develop_priv varchar2(1) constraint wwv_flow_worksheet_priv_d_ck check (develop_priv in ('Y','N')), -- row privs row_filter_1_db_col varchar2(255), row_filter_1_expr varchar2(30), row_filter_1_value varchar2(4000), row_filter_2_db_col varchar2(255), row_filter_2_expr varchar2(30), row_filter_2_value varchar2(4000), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_worksheet_privs is 'Used to extend worksheet level priv model, by column, user, or row' / alter table wwv_flow_worksheet_privs add constraint wwv_flow_worksheet_priv_pk primary key(id) / alter table wwv_flow_worksheet_privs add constraint wwv_flow_worksheet_priv_fk foreign key( worksheet_id ) references wwv_flow_worksheets (id) on delete cascade / create or replace trigger wwv_flow_worksheet_priv_trig before insert or update on wwv_flow_worksheet_privs for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; if :new.develop_priv = 'Y' then :new.view_priv := 'Y'; :new.edit_priv := 'Y'; end if; -- -- update parent timestamp -- update wwv_flow_worksheets set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.worksheet_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors ------------------------------- -- Stickies -- create table wwv_flow_worksheet_stick ( id number not null, worksheet_id number not null, row_id number not null, content clob, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_worksheet_stick is 'Allows any number of sticky comments to be added to a row'; alter table wwv_flow_worksheet_stick add constraint wwv_flow_worksheet_stick_pk primary key(id) / alter table wwv_flow_worksheet_stick add constraint wwv_flow_worksheet_stick_fk foreign key ( row_id ) references wwv_flow_worksheet_rows(id) on delete cascade / create or replace trigger wwv_flow_worksheet_st_trig before insert or update on wwv_flow_worksheet_stick for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_worksheet_rows set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.row_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors ------------------------------- -- Links -- create table wwv_flow_worksheet_links ( id number not null, worksheet_id number not null, row_id number not null, link_name varchar2(255) not null, url varchar2(4000) not null, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_worksheet_links is 'Allows any number of url links to be added to a row'; alter table wwv_flow_worksheet_links add constraint wwv_flow_worksheet_links_pk primary key(id) / alter table wwv_flow_worksheet_links add constraint wwv_flow_worksheet_links_fk foreign key ( row_id ) references wwv_flow_worksheet_rows(id) on delete cascade / create or replace trigger wwv_flow_worksheet_lk_trig before insert or update on wwv_flow_worksheet_links for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_worksheet_rows set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.row_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors ------------------------------- -- Documents -- create table wwv_flow_worksheet_docs ( id number not null, row_id number not null, name varchar2(255) not null, description varchar2(4000), content blob, mime_type varchar2(48) not null, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_worksheet_docs is 'Allows any number of documents to be attached to a worksheet row'; alter table wwv_flow_worksheet_docs add constraint wwv_flow_worksheet_doc_pk primary key(id) / alter table wwv_flow_worksheet_docs add constraint wwv_flow_worksheet_doc_fk foreign key ( row_id ) references wwv_flow_worksheet_rows(id) on delete cascade / create or replace trigger wwv_flow_worksheet_doc_trig before insert or update on wwv_flow_worksheet_docs for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_worksheet_rows set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.row_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors ---------- -- Reports -- create table wwv_flow_worksheet_rpts ( id number not null, worksheet_id number not null, session_id number, base_report_id number, application_user varchar2(255), name varchar2(255), status varchar2(30) constraint wwv_flow_ws_rpt_status_ck check (status in ( 'PRIVATE', 'PUBLIC')), -- column display display_rows number, report_columns varchar2(4000), -- sorting sort_column_1 varchar2(255), sort_direction_1 varchar2(255), sort_column_2 varchar2(255), sort_direction_2 varchar2(255), sort_column_3 varchar2(255), sort_direction_3 varchar2(255), sort_column_4 varchar2(255), sort_direction_4 varchar2(255), sort_column_5 varchar2(255), sort_direction_5 varchar2(255), sort_column_6 varchar2(255), sort_direction_6 varchar2(255), --control break break_on varchar2(1), control_break_options varchar2(255), sum_columns_on_break varchar2(100), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_worksheet_rpts is 'Stores contents definition of user defined report'; alter table wwv_flow_worksheet_rpts add constraint wwv_flow_worksheet_rpts_pk primary key(id) / alter table wwv_flow_worksheet_rpts add constraint wwv_flow_worksheet_rpts_fk foreign key ( worksheet_id ) references wwv_flow_worksheets(id) on delete cascade / alter table wwv_flow_worksheet_rpts add constraint wwv_flow_worksheet_rpts_uk unique(worksheet_id, session_id, application_user, name) / create index wwv_flow_ws_base_rpt_idx on wwv_flow_worksheet_rpts (base_report_id, application_user) / create or replace trigger wwv_flow_worksheet_rpts_trig before insert or update on wwv_flow_worksheet_rpts for each row declare l_col_type varchar2(30); begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; if :new.application_user is null then :new.application_user := v('APP_USER'); end if; if :new.status is null then :new.status := 'PRIVATE'; end if; if :new.break_on is null then :new.break_on := '0'; end if; if :new.display_rows is null then :new.display_rows := 100; end if; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors -------------------- -- Folders -- create table wwv_flow_folders ( id number not null, name varchar2(255), parent_id varchar2(255), -- status varchar2(30) constraint wwv_flow_folder_status_ck check (status in ( 'AVAILABLE_FOR_OWNER', 'NOT_AVAILABLE', 'AVAILABLE', 'ACL')), owner varchar2(255), description varchar2(4000), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_folders is 'Allows for the organization of worksheets'; alter table wwv_flow_folders add constraint wwv_flow_folder_pk primary key(id) / create or replace trigger wwv_flow_folder_trig before insert or update on wwv_flow_folders for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- set owner -- if :new.owner is null then :new.owner := :new.created_by; end if; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors -------------- -- Web Pages -- create table wwv_flow_web_pages ( id number not null, name varchar2(255), folder_id number, -- status varchar2(30) constraint wwv_flow_fold_status_ck check (status in ( 'AVAILABLE_FOR_OWNER', 'NOT_AVAILABLE', 'AVAILABLE', 'ACL')), owner varchar2(255), description varchar2(4000), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_web_pages is 'Stores definition of web pages'; alter table wwv_flow_web_pages add constraint wwv_flow_webpage_pk primary key(id) / create or replace trigger wwv_flow_web_pages_trig before insert or update on wwv_flow_web_pages for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- set owner -- if :new.owner is null then :new.owner := :new.created_by; end if; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors create table wwv_flow_web_pg_regions ( id number not null, name varchar2(255), web_page_id number, -- display_sequence number, page_position varchar2(30) constraint wwv_flow_webpgreg_pgpos_ck check (page_position in ( 'TOP', 'BOTTOM', 'LEFT', 'RIGHT', 'CENTER' )), display_as varchar2(30) constraint wwv_flow_webpgreg_dispas_ck check (display_as in ( 'UNORDERED_LIST', 'ORDERED_LIST', 'TABS', 'BUTTONS', 'VERTICAL_LIST', 'HORIZONTAL_LIST')), heading_text varchar2(4000), content clob, footer_text varchar2(4000), -- status varchar2(30) constraint wwv_flow_webpgreg_status_ck check (status in ( 'AVAILABLE_FOR_OWNER', 'NOT_AVAILABLE', 'AVAILABLE', 'ACL')), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_web_pg_regions is 'Content region of a web page'; alter table wwv_flow_web_pg_regions add constraint wwv_flow_webpage_region_pk primary key(id) / alter table wwv_flow_web_pg_regions add constraint wwv_flow_web_pg_rg_fk foreign key ( web_page_id ) references wwv_flow_web_pages(id) on delete cascade / create or replace trigger wwv_flow_web_pg_reg_trig before insert or update on wwv_flow_web_pg_regions for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_web_pages set updated_on = sysdate, updated_by = nvl(wwv_flow.g_user,user) where id = :new.web_page_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors create table wwv_flow_web_pg_list_entries ( id number not null, name varchar2(255), region_id number, -- entry_name varchar2(4000), entry_link varchar2(4000), display_sequence number, -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_web_pg_list_entries is 'Content region of a web page'; alter table wwv_flow_web_pg_list_entries add constraint wwv_flow_webpglistentry_pk primary key(id) / alter table wwv_flow_web_pg_list_entries add constraint wwv_flow_web_pg_le_fk foreign key ( region_id ) references wwv_flow_web_pg_regions(id) on delete cascade / create or replace trigger wwv_flow_web_pg_lstent_trig before insert or update on wwv_flow_web_pg_list_entries for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; -- -- update parent timestamp -- update wwv_flow_web_pg_regions set updated_on = sysdate, updated_by = nvl(wwv_flow.g_user,user) where id = :new.region_id; -- -- -- if :new.display_sequence is null then :new.display_sequence := 10; end if; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors ---------- -- Views -- CREATE OR REPLACE FORCE VIEW WWV_FLOW_WORKSHEET_FORMATS ("D", "R") AS select to_char((5234 + 10/100),'FML999G999G999G999G990D00') d, 'FML999G999G999G999G990D00' r from dual union all select trim(to_char((5234 + 10/100),'999G999G999G999G990D00')) d, '999G999G999G999G990D00' r from dual union all select trim(to_char((5234 + 10/100),'999G999G999G999G990D0000')) d, '999G999G999G999G990D0000' r from dual union all select trim(to_char(5234,'999G999G999G999G999G999G990')) d, '999G999G999G999G999G999G990' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'DD-MON-RR') d, 'DD-MON-RR' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'DD-MON-YYYY') d, 'DD-MON-YYYY' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'DD-MON') d, 'DD-MON' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'RR-MON-DD') d, 'RR-MON-DD' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'YYYY-MM-DD') d, 'YYYY-MM-DD' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'fmDay, fmDD fmMonth, YYYY') d, 'fmDay, fmDD fmMonth, YYYY' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI') d, 'DD-MON-YYYY HH24:MI' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'DD-MON-YYYY HH24:MI:SS') d, 'DD-MON-YYYY HH24:MI:SS' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'DD-MON-YYYY HH:MIPM') d, 'DD-MON-YYYY HH:MIPM' r from dual union all select to_char(to_date('20040112143000','YYYYMMDDHH24MISS'),'Month') d, 'Month' r from dual / show errors CREATE OR REPLACE FORCE VIEW "WWV_FLOW_WORKSHEET_COLUMNS_V" ("ID", "WORKSHEET_ID", "DB_COLUMN_NAME", "DISPLAY_ORDER", "GROUP_ID", "COLUMN_LABEL", "REPORT_LABEL", "COLUMN_TYPE", "DISPLAY_AS", "MAX_LENGTH", "ALLOW_NULL", "FORMAT_MASK", "COMPUTE_SUM", "COMPUTE_AVG", "COMPUTE_MIN", "COMPUTE_MAX", "STATIC_LOV", "LOV_NULL_TEXT", "LOV_ALLOW_NEW_VALUES", "LOV_IS_DISTINCT_VALUES", "LOV_NUM_COLUMNS", "LOV_ID", "COMPUTATION_TYPE", "COMPUTATION_EXPR_1", "COMPUTATION_EXPR_2", "VALIDATION_TYPE", "VALIDATION_EXPR_1", "VALIDATION_EXPR_2", "HELP_TEXT", "CREATED_ON", "CREATED_BY", "UPDATED_ON", "UPDATED_BY", "SECURITY_GROUP_ID", "DISPLAY_WIDTH", "DISPLAY_HEIGHT", "DEFAULT_VALUE", "DEFAULT_WHEN", "COLUMN_IDENTIFIER", "DISTINCT_VALUE_FILTER", "OTHERS_MAY_EDIT", "OTHERS_MAY_VIEW", "SYNC_FORM_LABEL") AS select c."ID",c."WORKSHEET_ID",c."DB_COLUMN_NAME",c."DISPLAY_ORDER",c."GROUP_ID",c."COLUMN_LABEL",c."REPORT_LABEL",c."COLUMN_TYPE",c."DISPLAY_AS",c."MAX_LENGTH",c."ALLOW_NULL",c."FORMAT_MASK",c."COMPUTE_SUM",c."COMPUTE_AVG",c."COMPUTE_MIN",c."COMPUTE_MAX",c."STATIC_LOV",c."LOV_NULL_TEXT",c."LOV_ALLOW_NEW_VALUES",c."LOV_IS_DISTINCT_VALUES",c."LOV_NUM_COLUMNS",c."LOV_ID",c."COMPUTATION_TYPE",c."COMPUTATION_EXPR_1",c."COMPUTATION_EXPR_2",c."VALIDATION_TYPE",c."VALIDATION_EXPR_1",c."VALIDATION_EXPR_2",c."HELP_TEXT",c."CREATED_ON",c."CREATED_BY",c."UPDATED_ON",c."UPDATED_BY",c."SECURITY_GROUP_ID",c."DISPLAY_WIDTH",c."DISPLAY_HEIGHT",c."DEFAULT_VALUE",c."DEFAULT_WHEN",c."COLUMN_IDENTIFIER",c."DISTINCT_VALUE_FILTER",c."OTHERS_MAY_EDIT",c."OTHERS_MAY_VIEW",c."SYNC_FORM_LABEL" from WWV_FLOW_WORKSHEET_COLUMNS c, wwv_flow_worksheets w, (select nv('P2_ID') worksheet_id, v('APP_USER') app_user from dual) ss where w.id = ss.worksheet_id and c.worksheet_id = ss.worksheet_id and (w.owner = ss.app_user or c.others_may_view = 'Y') / show errors /* CREATE OR REPLACE FORCE VIEW "WWV_FLOW_WORKSHEET_PEOPLE" ("APP_USER_ID", "PERSON_ID", "EMPNO", "LAST_NAME", "FIRST_NAME", "AKA", "MIDDLE_NAME", "REVERSE_NAME", "FORWARD_NAME", "USERID", "EMAIL", "SECONDARY_EMAIL", "ACCOUNT_NAME", "CURRENT_FLAG", "PERSON_TYPE_ID", "PERSON_TYPE_DESC", "BUSINESS_GROUP_ID", "DATE_OF_BIRTH", "HIREDATE", "TITLE", "ASSIGNMENT_TYPE", "MGR_PERSON_ID", "ORGANIZATION", "COST_CENTER", "WORK_PHONE", "WORK_LOCATION_ID", "WORK_LOCATION_CODE", "WORK_BUILDING", "WORK_FLOOR", "WORK_OFFICE", "WORK_ADDR1", "WORK_ADDR2", "WORK_ADDR3", "WORK_CITY", "WORK_STATE", "WORK_ZIP", "WORK_COUNTRY", "WORK_COUNTY", "WORKS_AT_HOME_FLAG", "DIRECTS", "TOTAL_REPORTS", "IS_MANAGER") AS select substr(email,1,instr(email,'@')-1), person_id, empno, last_name, first_name, aka, middle_name, reverse_name, forward_name, userid, email, secondary_email, account_name, current_flag, person_type_id, person_type_desc, business_group_id, date_of_birth, hiredate, title, assignment_type, mgr_person_id, organization, cost_center, work_phone, work_location_id, work_location_code, work_building, work_floor, work_office, work_addr1, work_addr2, work_addr3, work_city, work_state, work_zip, work_country, work_county, works_at_home_flag, directs, total_reports, decode(attribute02,'M','Y','N') from aria_current / show errors */ create table wwv_flow_workspace_geocache ( address varchar2(4000), geocode varchar2(512) ) / comment on table wwv_flow_workspace_geocache is 'Table to cache results of calling google maps api to geocode addresses.' / create table wwv_flow_worksheet_conditions ( id number not null, worksheet_id number not null, report_id number not null, name varchar2(255), condition_type varchar2(255) constraint wwv_flow_ws_c_type_ck check (condition_type in ('FILTER','HIGHLIGHT')), -- filter expression column_name varchar2(255), operator varchar2(30), expr_type varchar2(255), expr varchar2(255), condition_text varchar2(4000), condition_display varchar2(4000), -- highlighting settings highlight_sequence number, highlight_enabled varchar2(1) not null constraint wwv_flow_ws_h_enabled_ck check (highlight_enabled in ('Y','N')), row_bg_color varchar2(255), row_font_color varchar2(255), row_format varchar2(3), column_bg_color varchar2(255), column_font_color varchar2(255), column_format varchar2(3), -- created_on date not null, created_by varchar2(255) not null, updated_on date, updated_by varchar2(255), security_group_id number not null ) / comment on table wwv_flow_worksheet_conditions is 'Allows users to add report filtering or row highlighting based on certain criteria'; alter table wwv_flow_worksheet_conditions add constraint wwv_flow_worksheet_cond_pk primary key(id) / alter table wwv_flow_worksheet_conditions add constraint wwv_flow_worksheet_cond_fk foreign key ( worksheet_id ) references wwv_flow_worksheets(id) on delete cascade / alter table wwv_flow_worksheet_conditions add constraint wwv_flow_worksheet_cond_fk2 foreign key ( report_id ) references wwv_flow_worksheet_rpts(id) on delete cascade / alter table wwv_flow_worksheet_conditions add constraint wwv_flow_worksheet_cond_uk unique(worksheet_id, report_id, name) / create or replace trigger wwv_flow_worksheet_cond_trig before insert or update on wwv_flow_worksheet_conditions for each row begin -- -- maintain pk and timestamps -- if inserting and :new.id is null then :new.id := wwv_flow_worksheet_standard.next_val; end if; if inserting then :new.created_on := sysdate; :new.created_by := nvl(wwv_flow.g_user,user); :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); elsif updating then :new.updated_on := sysdate; :new.updated_by := nvl(wwv_flow.g_user,user); end if; if inserting and :new.highlight_enabled is null then :new.highlight_enabled := 'Y'; end if; -- -- update parent timestamp -- update wwv_flow_worksheet_rpts set updated_on = :new.updated_on, updated_by = :new.updated_by where id = :new.report_id; -- -- vpd -- if :new.security_group_id is null then :new.security_group_id := wwv_flow.get_sgid; end if; end; / show errors create or replace view apex_worksheet_rpts as select id, worksheet_id, session_id, base_report_id, application_user, name, status, display_rows, report_columns, sort_column_1, sort_direction_1, sort_column_2, sort_direction_2, sort_column_3, sort_direction_3, sort_column_4, sort_direction_4, sort_column_5, sort_direction_5, sort_column_6, sort_direction_6, break_on, control_break_options, sum_columns_on_break, created_on, created_by, updated_on, updated_by from wwv_flow_worksheet_rpts where security_group_id = (select wwv_flow.get_sgid from dual); / grant select on apex_worksheet_rpts to public; create or replace public synonym apex_worksheet_rpts for apex_worksheet_rpts; create or replace view apex_worksheet_columns as select id, worksheet_id, db_column_name, display_order, group_id, report_label, column_type, display_as, from wwv_flow_worksheet_columns where security_group_id = (select wwv_flow.get_sgid from dual); grant select on apex_worksheet_columns to public; create or replace public synonym apex_worksheet_columns for apex_worksheet_columns;