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;