set define '^'
set verify off
prompt ...create flow triggers
Rem Copyright (c) Oracle Corporation 2001 - 2006. All Rights Reserved.
Rem
Rem NAME
Rem trigger.sql
Rem
Rem DESCRIPTION
Rem Flow tiggers creation script.
Rem
Rem RUNTIME DEPLOYMENT: YES
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jstraub 02/28/2001 - Created
Rem mhichwa 03/01/2001 - Added set security group id
Rem cbcho 03/01/2001 - Added SQL Workshop set security group id
Rem mhichwa 03/03/2001 - Added DATA_LOADER to initial provisioning
Rem mhichwa 03/04/2001 - Added wwv_biu_flow_platform_pres
Rem cbcho 03/13/2001 - Modified wwv_biu_flow_sw_stats trigger
Rem cbcho 03/13/2001 - Added SQL Workshop - Control File table triggers
Rem mhichwa 03/13/2001 - Added triggers for tree tables
Rem mhichwa 03/19/2001 - Added comments
Rem mhichwa 03/26/2001 - Added triggers
Rem sleuniss 03/28/2001 - Added security group id to import_export trigger
Rem mhichwa 04/03/2001 - Added trim white space to flows, page processes, conditions, branches
Rem mhichwa 04/04/2001 - Added to trigger on wwv flow to set global id
Rem cbcho 04/10/2001 - Added trigger for wwv_flow_sw_archives table
Rem cbcho 04/10/2001 - Changed wwv_flow_sw_archives table trigger
Rem mhichwa 04/11/2001 - Removed flow tree state table
Rem sdillon 04/18/2001 - Modified wwv_flows_biu_image_repo for shadow upper_image_name
Rem mhichwa 04/18/2001 - changed name of platform pres to prefs
Rem mhichwa 04/18/2001 - Added prompt to aid in debugging
Rem sdillon 05/04/2001 - Changed image_repos trigger to update image_tag column
Rem sdillon 05/15/2001 - Added css_repos trigger
Rem sdillon 05/22/2001 - Changed COMPANY_IMAGE to COMPANY_IMAGES, FLOW_IMAGE to FLOW_IMAGES
Rem sdillon 05/24/2001 - Added html_repos trigger
Rem mhichwa 06/07/2001 - added sgid to preferences trigger
Rem mhichwa 06/17/2001 - added update of last updated on and by columns
Rem mhichwa 06/18/2001 - extened capabilities of last updated functionality
Rem mhichwa 06/26/2001 - Changed trigger on flow developers table to expand admin to all privs.
Rem mhichwa 06/26/2001 - Added global file inserted boolean set to trigger on flow file objects.
Rem mhichwa 08/13/2001 - Added comment to trigger on sessions table
Rem mhichwa 08/21/2001 - Merged apps tab.sql
Rem mhichwa 08/21/2001 - Merged db auth tables.sql
Rem mhichwa 08/21/2001 - Removed reference to FLOWS schema and replaced with carot 3
Rem mhichwa 08/22/2001 - Updated trigger on security schemes to remove trailing whitespace.
Rem mhichwa 08/23/2001 - Added create index statements provided by tmuth to avoid unindexed foreign keys
Rem jstraub 08/24/2001 - Added :new.security_group_id to wwv_biu_flow_platform_prefs trigger
Rem mhichwa 10/04/2001 - Added trigger on new field template table
Rem tmuth 10/05/2001 - Removed trigger on wwv_flow_file_objects$, now in flows_files_new.sql
Rem mhichwa 10/09/2001 - Added collections
Rem mhichwa 10/16/2001 - Added trigger for updatable report column attributes table
Rem mhichwa 10/24/2001 - Added upper alias to page and flow table triggers
Rem jstraub 10/29/2001 - Added triggers for runtime distribution tables
Rem mhichwa 11/13/2001 - Extended branch types
Rem cbcho 11/27/2001 - Modified wwv_biu_fnd_user to insert or update upper(allow_access_to_schemas) value
Rem tmuth 11/27/2001 - Added XML import/export triggers
Rem mhichwa 12/05/2001 - Changed sequence on population of id for flow sessions $ table per Scott.Spadafore instructions
Rem cbcho 02/05/2002 - Removed remove white trailing space on process_sql column from wwv_flow_processing and wwv_flow_step_processing triggers
Rem jkallman 02/07/2002 - Set collection_changed flag for wwv_flow_collections$, update MD5 computation in wwv_biu_flow_coll_members
Rem mhichwa 03/07/2002 - Changed trigger on wwv flow steps to accomidate id2 column
Rem msewtz 03/14/2002 - Added trigger for wwv_flow_xml_upload table
Rem mhichwa 04/04/2002 - Removed wwv_flow_icon_bar_attributes
Rem mhichwa 04/16/2002 - Removed inserts into wwv_flow_company_schemas for PEOPLE, SCHEDULER, etc schemas
Rem jkallman 04/29/2002 - Set flow_id in trigger wwv_biu_flow_collection
Rem mhichwa 04/29/2002 - Added trigger on new table wwv_flow_popup_lov_template
Rem mhichwa 04/30/2002 - Added menu triggers for 3 new menu tables
Rem jstraub 07/08/2002 - Added wwv_flows_version$ trigger
Rem cbcho 07/12/2002 - Changed trigger on wwv_flow_mail_queue to populate timestamp column with sysdate
Rem mhichwa 07/17/2002 - Added wwv_biu_button_template trigger
Rem mhichwa 07/31/2002 - Added setting of alias in wwv_flow table to ensure it is not null
Rem sspadafo 09/24/2002 - Added custom_auth_setups trigger
Rem sspadafo 09/27/2002 - Added wwv_flow_entry_points,wwv_flow_entry_point_args,wwv_flow_step_branch_args triggers
Rem mhichwa 10/11/2002 - Added generic table trigger
Rem mhichwa 10/21/2002 - Fixed trigger for generic table
Rem tmuth 11/01/2002 - Removed reference to ^3, now using select user... technique
Rem cbcho 12/04/2002 - Obsoleted 3 DEVELOPER_ROLEs (BROWSE:USER_MANAGER:DB_MONITOR) from wwv_flow_developers trigger
Rem mhichwa 12/12/2002 - Modified trigger on tabs to set last updated by
Rem jstraub 02/26/2003 - bug 2823889, added raise_application_error in wwv_biu_flow_companies if
Rem provisioning_company_id = 20 and short_name != 'ORACLE'
Rem sspadafo 03/06/2003 - Add trigger to populate session_id hashed in wwv_flow_sessions$ (Bug 2837566)
Rem jstraub 03/10/2003 - Replaced select user with select sys_context( 'userenv', 'current_schema') for FLOW_OWNER
Rem jstraub 03/12/2003 - Replaced inserts into wwv_flow_languages with list derrived from IE 6.0 languages
Rem and attempted to map to NLS_LANGUAGE and NLS_TERRITORY in Globalization Support Guide
Rem jstraub 03/17/2003 - Added setting trans_flow_lang_code_root in trigger wwv_biu_flow_lang_map
Rem jstraub 03/25/2003 - Moved wwv_biu_flow_web_services from wstab.sql
Rem jstraub 03/25/2003 - Removed trigger on wwv_flow_dynamic_pages, table obsolete & removed from tab.sql
Rem sspadafo 04/01/2003 - Add delete triggers and biu logic to delete wwv_flow_compound_conditions rows (Bug 2860487)
Rem sspadafo 04/03/2003 - Adjust shortcuts trigger for null consideration_seq (Bug 2887077)
Rem sspadafo 04/03/2003 - Remove unneeded trigger wwv_bd_flows (Bug 2860487)
Rem sspadafo 04/03/2003 - Adjust wwv_flow_list_of_values_data triggers for wwv_flow_compound_conditions (Bug 2860487)
Rem cbcho 04/10/2003 - Added wwv_biu_flow_hnt_table_info, wwv_biu_flow_hnt_column_info triggers (Bug 2882452)
Rem jkallman 04/14/2003 - Modify trigger wwv_biu_flow_translatable_text to set default value for template_translatable
Rem cbcho 04/15/2003 - Added wwv_biu_flow_chart_ser_attr (Bug 2722911)
Rem jkallman 04/24/2003 - Added derivation of flow_id in trigger wwv_biu_flow_menu_options
Rem msewtz 04/28/2003 - Added wwv_biu_flow_region_report_filter
Rem msewtz 04/28/2003 - Added wwv_biu_flow_region_report_column
Rem msewtz 04/28/2003 - Added wwv_biu_flow_query_condition
Rem msewtz 04/28/2003 - Added wwv_biu_flow_query_column
Rem msewtz 04/28/2003 - Added wwv_biu_flow_query_object
Rem msewtz 04/28/2003 - Added wwv_biu_flow_query_definition
Rem cbcho 04/29/2003 - Added Sharon's wwv_biu_wwv_flow_hnt_procedure and wwv_biu_wwv_flow_hnt_argument
Rem jkallman 05/02/2003 - Corrected typo in wwv_bd_flowstepcomputations
Rem sspadafo 05/04/2003 - Change wwv_biu_flow_sessions to call function to set hashed session id
Rem jkallman 05/06/2003 - Reviewed and replaced with system messages
Rem jstraub 05/07/2003 - Added population of operator if null in wwv_biu_flow_query_condition
Rem jkallman 05/08/2003 - Added derivation of flow_id in trigger wwv_biu_flow_lists_of_valuesd
Rem mhichwa 05/12/2003 - Extended trigger on report columns to maintain flow id, bug 2861658
Rem jkallman 05/15/2003 - Convert to lowercase the flow_language of wwv_flows
Rem jstraub 05/29/2003 - Removed triggers on wwv_flow_xml_mapping, wwv_flow_xml_structure and wwv_flow_xml_upload, bug 2972214
Rem sspadafo 06/29/2003 - Add delete trigger on wwv_flow_items, expand delete trigger on wwv_flow_step_items
Rem - for cascade delete to flow_computations and flow_step_computations (Bug 3029349)
Rem sspadafo 06/30/2003 - Cascade update to flow/page computations when flow or page item name changes (Bug 3029349)
Rem jstraub 07/24/2003 - Removed trigger on wwv_flow_bugs, bug 3037774
Rem jkallman 08/01/2003 - Reduce list of wwv_flow_languages (Bug 3029420)
Rem jkallman 08/04/2003 - Add nls_sort values to wwv_flow_languages (Bug 3032142)
Rem jstraub 08/04/2003 - Moved trigger that populates trans_flow_lang_code_root of wwv_flow_languages to before insert statements
Rem jstraub 08/04/2003 - Fixed above, moved populating trans_flow_lang_code_root out of block that detects if import in progress
Rem sspadafo 08/18/2003 - Create default page alias in wwv_biu_flow_steps (Bug 3039513)
Rem sspadafo 08/19/2003 - Undo last change to set default page alias in wwv_biu_flow_steps (Bug 3039513)
Rem jstraub 10/16/2003 - Altered wwv_biu_flow_steps so last_updated_on and by are populated when using create flow wizard (Bug 3185170)
Rem msewtz 03/29/2004 - replaced company and flow image prefixes with workspace and app prefixes in wwv_flows_biu_image_repo (Bug 3426926)
Rem mhichwa 04/06/2004 - Changed trigger on page components to update page last updated on and by (bug 3557419)
Rem jkallman 04/19/2004 - Remove MD5 computation from trigger wwv_biu_flow_coll_members
Rem mhichwa 04/29/2004 - Add kris.rice calendar table and template triggers bug 360246
Rem jstraub 05/03/2004 - Added triggers for tables for improved web services feature
Rem msewtz 05/18/2004 - Updated biu_wwv_flow_hnt_table_info trigger
Rem msewtz 05/18/2004 - Updated wwv_flow_hnt_column_info trigger
Rem msewtz 05/18/2004 - Added biu_wwv_flow_hnt_lov_data trigger
Rem mhichwa 05/24/2004 - Added lock history
Rem jkallman 05/27/2004 - Remove references to ^FLOW_OWNER
Rem mhichwa 06/01/2004 - Added on delete row trigger to wwv_flow_lock_page table. This will log unlock of pages.
Rem sspadafo 06/06/2004 - Add triggers and indexes for tables wwv_flow_roles, wwv_flow_local_users, wwv_flow_user_roles
Rem sspadafo 06/07/2004 - Revise triggers and indexes for tables wwv_flow_roles, wwv_flow_local_users, wwv_flow_user_roles
Rem sspadafo 06/07/2004 - Remove triggers and indexes for tables wwv_flow_roles, wwv_flow_local_users, wwv_flow_user_roles
Rem jkallman 06/08/2004 - Add sgid to wwv_biu_wwv_flow_mail_queue, add trigger wwv_biu_wwv_flow_mail_log
Rem jkallman 06/09/2004 - Add en-gb to wwv_flow_languages (Bug 3565884)
Rem jkallman 06/10/2004 - Rename duplicate object names for triggers (Bug 3256811)
Rem mhichwa 06/10/2004 - Add trigger on wwv_flow_themes table
Rem sspadafo 06/10/2004 - Add mistakenly deleted trigger on wwv_flow_fnd_group_users (Bug 3684355)
Rem skutz 06/15/2004 - Add audit trigger and update (to sync wwv_flows) on wwv_flow_themes table
Rem jkallman 06/17/2004 - Remove trigger wwv_biu_flowthemes_audit (Bug 3699509)
Rem sspadafo 06/18/2004 - Add action to trigger for wwv_flow_fnd_user.web_password_raw
Rem mhichwa 07/08/2004 - Add trigger for wwv_flow_page_groups table
Rem jkallman 07/14/2004 - Added triggers for tables wwv_flow_online_help and wwv_flow_online_help_ja
Rem mhichwa 08/06/2004 - Set wwv_flow.g_import_in_progress := true when cascading last updated on to page
Rem mhichwa 08/06/2004 - Edited trigger on wwv_flow_step_items to cascade to flow to set last updated
Rem jstraub 08/09/2004 - Changed WWV_DICTIONARY$ to WWV_FLOW_DICTIONARY$
Rem sspadafo 09/08/2004 - Incorporate wwv_flow_audit.g_cascade into page component triggers to cascade audit columns to application (wwv_flows)
Rem jkallman 09/27/2004 - Lower message_language for wwv_flow_messages$
Rem sspadafo 01/14/2005 - Added wwv_flow_biu_qb_saved_qry
Rem sspadafo 01/17/2005 - Changed wwv_flow_biu_qb_saved_qry for rename of owner column to query_owner
Rem cbcho 01/27/2005 - Removed triggers wwv_biu_flow_sw_parameters,wwv_biu_flow_sw_stats,wwv_biu_flow_sw_detail_stats,wwv_biu_flow_sw_ctrl_files,wwv_biu_flow_sw_ctrl_file_det,wwv_biu_flow_sw_ctrlfile_stats,wwv_biu_flow_sw_archives
Rem cbcho 01/27/2005 - Added triggers wwv_biu_flow_sw_binds,wwv_biu_flow_sw_results,wwv_biu_flow_sw_detail_results
Rem skutz 01/17/2005 - Changed wwv_flow_biu_qb_saved_qry to wwv_flow_bi_qb_saved_qry and modified for insert only
Rem sspadafo 01/28/2005 - Added index for wwv_flow_biu_qb_saved_qry, triggers for wwv_flow_qb_saved_cond, wwv_flow_qb_saved_join, wwv_flow_qb_saved_tabs
Rem cbcho 01/31/2005 - Changed wwv_biu_flow_sw_results not to insert run_by
Rem cbcho 02/18/2005 - Removed wwv_biu_flow_sw_statements
Rem jkallman 02/21/2005 - Add wwv_biu_flow_sw_stmts from CJones
Rem sspadafo 02/22/2005 - Set display_type in wwv_bi_flowcals (Kris)
Rem sspadafo 02/27/2005 - Set checksum_salt_last_reset in wwv_biu_flows
Rem sspadafo 03/05/2005 - Added triggers on wwv_flow_models, wwv_flow_model_pages, wwv_flow_model_page_cols (msewtz)
Rem sspadafo 03/17/2005 - Change :new.computation_type := 'SQL_EXPRESSION'; to use standard speling for default value for wwv_biu_computations and wwv_biu_step_computations
Rem skutz 03/23/2005 - Added trigger wwv_flow_sc_trans_bi
Rem sspadafo 04/05/2005 - Revised triggers on wwv_flow_models, wwv_flow_model_pages, wwv_flow_model_page_cols (msewtz)
Rem sspadafo 04/05/2005 - Revised wwv_flow_bi_qb_saved_qry (skutz)
Rem jkallman 05/12/2005 - Augment list of wwv_flow_languages, add nls_windows_charset values
Rem jkallman 05/13/2005 - Correct territory for Afrkiaans in wwv_flow_languages
Rem jkallman 05/16/2005 - Add support for Zulu with nls_windows_charset of AL32UTF8
Rem sspadafo 06/07/2005 - Remove action from trigger for wwv_flow_fnd_user.web_password_raw (Bug 4023590)
Rem sspadafo 06/07/2005 - Restore action in trigger for wwv_flow_fnd_user using new wwv_flow_api.g_fnd_user_password_action boolean (Bug 4023590)
Rem jkallman 06/23/2005 - Altered trigger wwv_flow_bi_qb_saved_qry, updated created_by assignment
Rem sspadafo 07/06/2005 - Added wwv_flow_restricted_schema_biu, wwv_flow_rschema_exceptions_biu (Bug 4296276)
Rem sspadafo 07/13/2005 - Set wwv_flows last_updated_on, last_updated_by only if user <> 'SYS' so the columns do not change during an upgrade (Bug 3963331)
Rem sspadafo 07/13/2005 - Changed triggers on wwv_flow_page_generic_attr and wwv_flow_region_chart_ser_attr to cascade audit trail up to wwv_flow_steps (Bug 4434944)
Rem sspadafo 11/30/2005 - Change trigger on wwv_flow_sessions$ to compute and insert wwv_flow_security.g_crypto_salt into ip_address column on session creation (Bug 4690231)
Rem sspadafo 11/30/2005 - Add check for null :old.checksum_salt in wwv_flows trigger when updating
Rem jkallman 12/03/2005 - Remove triggers associated with runtime distribution
Rem madelfio 01/23/2006 - Added triggers associated with install wizard (wwv_biu_install_*)
Rem jkallman 01/26/2006 - Add support of mail_reply in wwv_biu_flow_mail_queue
Rem jkallman 02/15/2006 - Modify wwv_biu_flow_mail_queue to initialize mail_send_count
Rem mhichwa 03/07/2006 - Add on delete trigger for wwv_flow_sessions$ to track last_login date by workspace
Rem mhichwa 04/14/2006 - Add trigger wwv_flow_app_comments_t1
Rem sspadafo 04/26/2006 - Changed wwv_flow_biu_sessions trigger to use wwv_flow_utilities.db_version_is_at_least function (Bug 4773656)
Rem sspadafo 04/26/2006 - Changed wwv_flow_biu_sessions trigger to use wwv_flow_security.crypto_randombytes function for pre-10.1.0.3 (Bug 4773656)
Rem mhichwa 06/12/2006 - Add wwv_flow_page_cache table trigger, wwv_biu_flow_page_cache
Rem mhichwa 06/12/2006 - Extended wwv_biu_flow_steps trigger to capture new created_on and created_by columns
Rem msewtz 06/14/2006 - updated wwv_biu_flow_steps to always set created_by and created_on when inserting, even if new.id is not null
Rem msewtz 06/20/2006 - removed wwv_flow_application_tab and ...tab_set triggers (bug 5231754)
Rem sspadafo 11/26/2006 - set account_expiry to null on insert in wwv_biu_fnd_user; fixed end statements in several triggers ending in "end wwv_biu_fnd_user"
Rem cbcho 12/05/2006 - Added triggers for wwv_flow_flash_charts, wwv_flow_flash_chart_series tables
Rem jkallman 12/07/2006 - Remove unconditional setting of :new.security_group_id in wwv_biu_flows and wwv_biu_flow_collection (Bug 5682340)
Rem mhichwa 12/12/2006 - Added trigger biu_wwv_flow_prt_templ and biu_wwv_flow_shared_queries
Rem msewtz 12/13/2006 - Renamed biu_wwv_flow_prt_templ to biu_wwv_flow_report_layouts
Rem mhichwa 12/19/2006 - Improved wwv_flow_page_cache trigger
Rem mhichwa 01/03/2007 - Added :new.remote_addr := wwv_flow.g_remote_addr
Rem jkallman 01/17/2007 - Moved triggers from tab.sql
Rem cbcho 01/18/2007 - Added to populate flow_id in wwv_flow_flash_chart_series
Rem jkallman 01/23/2007 - Move insert into wwv_flow_languages to apex_install_data.sql
Rem cbcho 01/24/2007 - Changed wwv_flow_page_plugs,wwv_flow_flash_charts,wwv_flow_flash_series triggers to trim chr(13) from clob column
Rem mhichwa 01/25/2007 - changed trigger on cache table to always set sgid
Rem cbcho 01/30/2007 - Changed wwv_flow_flash_charts trigger to cascade update columns to page and application level
Rem cbcho 02/01/2007 - Changed wwv_flow_flash_chart_series trigger to cascade update to flash chart, page and application level
Rem madelfio 02/12/2007 - Added last updated logic for wwv_flow_install and child tables (Bug 5878569)
Rem sspadafo 02/13/2007 - Remove condition on "cascade update to application" section of wwv_biu_flow_steps (Bug 5876235)
Rem mhichwa 02/22/2007 - Changed wwv_biu_flow_page_cache trigger to record the applications owner sgid not the curr sgid
Rem sspadafo 02/23/2007 - Changed wwv_biu_fnd_user for 3.0 password format (Bugs 5613085,5597269)
Rem madelfio 08/16/2007 - Added triggers for apex worksheet tables
Rem jstraub 08/23/2007 - Added wwv_flow_worksheet_cat_trig
Rem jstraub 08/24/2007 - Added display_sequence logic to wwv_flow_worksheet_cat_trig
Rem madelfio 09/05/2007 - Converted all calls to wwv_flow_worksheet_standard.next_val to wwv_flow_id.next_val
Rem madelfio 09/07/2007 - Updated trigger on wwv_flow_worksheet_conditions to use new column name "enabled"
Rem madelfio 09/12/2007 - Updated trigger on wwv_flow_worksheets to set alias column if it is null
Rem madelfio 09/17/2007 - Added logic for report_list_mode column in wwv_flow_worksheets_trig
Rem madelfio 09/24/2007 - Updated for wwv_flow_worksheet_rpts flashback columns
Rem madelfio 10/03/2007 - Added trigger for wwv_flow_worksheet_computation table
Rem jkallman 10/04/2007 - Added trigger wwv_biu_flow_mail_att on wwv_flow_mail_attachments
Rem madelfio 10/06/2007 - Added defaults for wwv_flow_worksheets
Rem madelfio 10/09/2007 - Added repeat_control_break_headers column to wwv_flow_worksheets
Rem madelfio 10/11/2007 - Updated default display rows for dynamic query regions
Rem jstraub 10/11/2007 - Added wwv_flow_ws_lov_ents_trig
Rem jkallman 10/12/2007 - Remove references to worksheet_version_id in wwv_flow_worksheets_trig
Rem jstraub 10/16/2007 - Added wwv_flow_worksheet_seq and used to populate load_order in wwv_flow_worksheet_row_trig
Rem mhichwa 10/24/2007 - Added trigger on wwv_flow_application_groups
Rem madelfio 10/26/2007 - Updated worksheet triggers to populate new not null columns: report_seq and allow_delete
Rem madelfio 10/30/2007 - Removed repeat_control_break_headers from wwv_flow_worksheets trigger
Rem madelfio 11/01/2007 - Removed population of worksheet alias
Rem madelfio 11/05/2007 - Changed distinct_value_filter to rpt_distinct_lov
Rem msewtz 11/21/2007 - Added biu_wwv_flow_sqry_sql
Rem jstraub 11/28/2007 - Added defaults for new columns in wwv_flow_worksheet_col_trig and wwv_flow_worksheet_rpts_trig
Rem madelfio 12/03/2007 - Removed default value for wwv_flow_worksheets.break_on
Rem cbcho 12/07/2007 - Changed wwv_flow_worksheet_columns trigger to update display_text_as to 'ESCAPE_SC'
Rem hfarrell 12/10/2007 - Added trigger wwv_flow_model_page_regions on table wwv_flow_model_page_regions
Rem madelfio 12/10/2007 - Updated worksheet triggers for new columns, cascade changes
Rem madelfio 12/11/2007 - Fixed error with rpt_distinct_lov always defaulting to 'Y'
Rem madelfio 12/14/2007 - Added show_control_break to wwv_flow_worksheets
Rem madelfio 12/18/2007 - New default for wwv_flow_worksheet_columns.rpt_show_filter_lov
Rem cbcho 01/07/2008 - Changed trigger on wwv_flow_worksheet_columns to set column heading alignment to CENTER
Rem madelfio 01/09/2008 - Added logic with g_delete_in_progress to avoid mutating table errors
Rem jstraub 01/18/2008 - Added population of flow_id in wwv_flow_worksheet_cat_trig
Rem madelfio 03/31/2008 - Updated wwv_flow_worksheet_col_trig to not set column_identifier unless it is null (bug 6875685)
Rem sspadafo 01/17/2009 - Updated wwv_biu_fnd_user to insert into wwv_flow_password_history
Rem sspadafo 01/24/2009 - Updated wwv_biu_fnd_user, created wwv_aiu_fnd_user to insert into wwv_flow_password_history
Rem sspadafo 01/28/2009 - Updated wwv_biu_fnd_user to not reference wwv_flow_fnd_user_api global
Rem sspadafo 01/28/2009 - Undid last change which was a mistake
Rem jkallman 04/24/2009 - Support COM.ORACLE.APEX.APPLICATIONS with SGID=20 for wwv_flow_companies
Rem jkallman 05/19/2009 - Revise WWV_FLOW_SESSIONS$_T1 to delete from wwv_flow_debug and wwv_flow_worksheet_rpts (Bug 8530955)
prompt ...trigger on wwv_flow_platform_prefs
create or replace trigger wwv_biu_flow_platform_prefs
before insert or update on wwv_flow_platform_prefs
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create index wwv_flow_platform_prefs_fkidx on wwv_flow_platform_prefs(security_group_id,name)
/
--
--
--
prompt ...trigger on wwv_flow_companies
create or replace trigger wwv_biu_flow_companies
before insert or update on wwv_flow_companies
for each row
begin
if :new.provisioning_company_id = 20 and :new.short_name not in ('ORACLE','COM.ORACLE.APEX.APPLICATIONS') then
raise_application_error(-20001,wwv_flow_lang.system_message('TRIGGER.SGID_RESERVED'));
end if;
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
:new.short_name := upper(:new.short_name);
end;
/
prompt ...trigger on wwv_flow_company_schemas
create or replace trigger wwv_biu_flow_company_schemas
before insert or update on wwv_flow_company_schemas
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
:new.schema := upper(:new.schema);
end;
/
--
-- create company schema mappings
--
prompt ...set sgid = 10
begin
wwv_flow_security.g_security_group_id := 10;
end;
/
begin
insert into wwv_flow_company_schemas (security_group_id,schema) values (10,wwv_flow.g_flow_schema_owner);
commit;
end;
/
prompt ...trigger on wwv_flows
create or replace trigger wwv_biu_flows
before insert or update on wwv_flows
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.webdb_logging is null then
:new.webdb_logging := 'YES';
end if;
if inserting and :new.home_link is null then
:new.home_link := 'f?p='||:new.id;
end if;
:new.flow_language := lower(:new.flow_language);
:new.substitution_string_01 := upper(:new.substitution_string_01);
:new.substitution_string_02 := upper(:new.substitution_string_02);
:new.substitution_string_03 := upper(:new.substitution_string_03);
:new.substitution_string_04 := upper(:new.substitution_string_04);
:new.substitution_string_05 := upper(:new.substitution_string_05);
:new.substitution_string_06 := upper(:new.substitution_string_06);
:new.substitution_string_07 := upper(:new.substitution_string_07);
:new.substitution_string_08 := upper(:new.substitution_string_08);
:new.owner := upper(:new.owner);
if :new.build_status is null then
:new.build_status := 'RUN_AND_BUILD';
end if;
--
if inserting and :new.global_id is null then
:new.global_id := wwv_flow_id.next_val;
end if;
--
-- trim white space
--
for i in 1..10 loop
:new.CUSTOM_AUTHENTICATION_PROCESS := rtrim(:new.CUSTOM_AUTHENTICATION_PROCESS );
:new.CUSTOM_AUTHENTICATION_PROCESS := rtrim(:new.CUSTOM_AUTHENTICATION_PROCESS ,chr(10));
:new.CUSTOM_AUTHENTICATION_PROCESS := rtrim(:new.CUSTOM_AUTHENTICATION_PROCESS ,chr(13));
end loop;
:new.alias := upper(:new.alias);
if :new.alias is null then
:new.alias := 'F'||:new.id;
end if;
if updating and :old.checksum_salt is null or :new.checksum_salt <> :old.checksum_salt then
:new.checksum_salt_last_reset := sysdate;
end if;
if :new.alias is null then
:new.alias := 'F'||:new.id;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if user <> 'SYS' then
:new.last_updated_on := sysdate;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
end if;
end;
/
prompt ...trigger on wwv_flow_alternate_config
create or replace trigger wwv_biu_flow_alternate_config
before insert or update on wwv_flow_alternate_config
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger on wwv_flow_alt_config_detail
create or replace trigger wwv_biu_flow_alt_config_det
before insert or update on wwv_flow_alt_config_detail
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_flow_app_comments_t1
create or replace trigger wwv_flow_app_comments_t1
before insert or update on wwv_flow_app_comments
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- clean up whitespace in pages
--
for i in 1..10 loop
:new.pages := replace(:new.pages,' ',' ');
end loop;
:new.pages := trim(:new.pages);
:new.pages := replace(:new.pages,':',',');
:new.pages := replace(:new.pages,' ',',');
:new.pages := replace(:new.pages,',,',', ');
:new.pages := rtrim(:new.pages,',');
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.updated_on := sysdate;
:new.updated_by := wwv_flow.g_user;
if inserting then
:new.created_on := sysdate;
:new.created_by := wwv_flow.g_user;
end if;
update wwv_flows
set last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flow_sec_schemes
create or replace trigger wwv_biu_flow_sec_schemes
before insert or update on wwv_flow_security_schemes
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- remove trailing spaces
--
for i in 1..10 loop
:new.SCHEME_TEXT := rtrim(:new.SCHEME_TEXT );
:new.SCHEME_TEXT := rtrim(:new.SCHEME_TEXT ,chr(10));
:new.SCHEME_TEXT := rtrim(:new.SCHEME_TEXT ,chr(13));
end loop;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
create index WWV_FLOW_SECURITY_SCHEM_FKIDX ON WWV_FLOW_SECURITY_SCHEMES(FLOW_ID,name)
/
prompt ...trigger wwv_biu_flow_templ_prefs
create or replace trigger wwv_biu_flow_templ_prefs
before insert or update on wwv_flow_template_preferences
for each row
begin
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create index wwv_flow_template_prefe_fkidx on wwv_flow_template_preferences(flow_id,owner,template_preference)
/
prompt ...trigger wwv_biu_flow_banner
create or replace trigger wwv_biu_flow_banner
before insert or update on wwv_flow_banner
for each row
begin
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...trigger wwv_biu_flow_languages
create or replace trigger wwv_biu_flow_languages
before insert or update on wwv_flow_languages
for each row
begin
:new.lang_id_upper := upper(:new.lang_id);
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
prompt ...trigger wwv_biu_flow_lang_map
create or replace trigger wwv_biu_flow_lang_map
before insert or update on wwv_flow_language_map
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated and trans_flow_lang_code_root
--
:new.trans_flow_lang_code_root := substr(:new.translation_flow_language_code,1,2);
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_translatable_text
create or replace trigger wwv_biu_flow_translatable_text
before insert or update on wwv_flow_translatable_text$
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
if :new.template_translatable is null then
:new.template_translatable := 'N';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_dynamic_trans
create or replace trigger wwv_biu_flow_dynamic_trans
before insert or update on WWV_FLOW_DYNAMIC_TRANSLATIONS$
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create index WWV_FLOW_DYNAMIC_TRANSL_FKIDX ON WWV_FLOW_DYNAMIC_TRANSLATIONS$
(SECURITY_GROUP_ID,flow_id,TRANSLATE_TO_LANG_CODE)
/
prompt ...trigger wwv_biu_flow_page_cache
create or replace trigger wwv_biu_flow_page_cache
before insert or update on wwv_flow_page_cache
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
:new.cached_on := sysdate;
:new.cached_by := wwv_flow.g_user;
:new.request := wwv_flow.g_request;
if :new.language is null then
:new.language := upper(substr(wwv_flow.g_flow_language,1,2));
end if;
end if;
--
-- vpd
--
--:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
:new.security_group_id := nvl(wwv_flow_security.g_curr_flow_security_group_id,0);
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.updated_on := sysdate;
:new.updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_templates
create or replace trigger wwv_biu_flow_templates
before insert or update on wwv_flow_templates
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flow_fld_templates
create or replace trigger wwv_biu_flow_fld_templates
before insert or update on wwv_flow_field_templates
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_roles
create or replace trigger wwv_biu_flow_roles
before insert or update on wwv_flow_required_roles
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
create index wwv_flow_required_roles_fkidx on wwv_flow_required_roles(flow_id,role_name)
/
prompt ...trigger wwv_biu_flow_icon_bar
create or replace trigger wwv_biu_flow_icon_bar
before insert or update on wwv_flow_icon_bar
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.icon_bar_disp_cond_type = 'COMPOUND'
and nvl(:new.icon_bar_disp_cond_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.icon_bar_disp_cond)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bd_flowiconbar
create or replace trigger wwv_bd_flowiconbar
before delete on wwv_flow_icon_bar
for each row
begin
--
-- delete compound condition that may exist for this icon
--
if :old.icon_bar_disp_cond_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.icon_bar_disp_cond)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowiconbar;
/
prompt ...trigger wwv_biu_processing
create or replace trigger wwv_biu_processing
before insert or update on wwv_flow_processing
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.process_when_type = 'COMPOUND'
and nvl(:new.process_when_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.process_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bd_flowprocessing
create or replace trigger wwv_bd_flowprocessing
before delete on wwv_flow_processing
for each row
begin
--
-- delete compound condition that may exist for this process
--
if :old.process_when_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.process_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowprocessing;
/
create unique index wwv_flow_processing_fkidx on wwv_flow_processing(flow_id,id)
/
prompt ...trigger wwv_biu_flow_items
create or replace trigger wwv_biu_flow_items
before insert or update on wwv_flow_items
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
-----------------
-- default values
--
if :new.data_type is null then
:new.data_type := 'VARCHAR';
end if;
:new.name_length := length(:new.name);
:new.name := upper(:new.name);
if :new.is_Persistent is null then
:new.is_Persistent := 'Y';
end if;
--
-- name
--
:new.name := upper(:new.name);
--
-- cascade to computations
--
if updating and :new.name != upper(:old.name) then
begin
update wwv_flow_computations
set computation_item = :new.name
where flow_id = :new.flow_id
and upper(computation_item) = upper(:old.name);
--
update wwv_flow_step_computations
set computation_item = :new.name
where flow_id = :new.flow_id
and upper(computation_item) = upper(:old.name);
exception when others then null;
end;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bd_flowitems
create or replace trigger wwv_bd_flowitems
before delete on wwv_flow_items
for each row
begin
--
-- cascade delete flow and step computations referencing item
--
begin
delete wwv_flow_computations
where upper(computation_item) = upper(:old.name)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
delete wwv_flow_step_computations
where upper(computation_item) = upper(:old.name)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end wwv_bd_flowitems;
/
prompt ...trigger wwv_biu_computations
create or replace trigger wwv_biu_computations
before insert or update on wwv_flow_computations
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.compute_when_type = 'COMPOUND'
and nvl(:new.compute_when_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.compute_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
if :new.computation_point is null then
:new.computation_point := 'AFTER_SUBMIT';
end if;
if :new.computation_processed is null then
:new.computation_processed := 'REPLACE_EXISTING';
end if;
if :new.computation_type is null then
:new.computation_type := 'SQL_EXPRESSION';
end if;
--
-- set name
--
:new.computation_item := upper(:new.computation_item);
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bd_flowcomputations
create or replace trigger wwv_bd_flowcomputations
before delete on wwv_flow_computations
for each row
begin
--
-- delete compound condition that may exist for this computation
--
if :old.compute_when_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.compute_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowcomputations;
/
create index wwv_flow_computations_fkidx on wwv_flow_computations(flow_id,id)
/
prompt ...trigger wwv_biu_flow_tabs
create or replace trigger wwv_biu_flow_tabs
before insert or update on wwv_flow_tabs
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.display_condition_type = 'COMPOUND'
and nvl(:new.display_condition_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.tab_plsql_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
:new.tab_also_current_for_pages :=
replace(replace(replace(:new.tab_also_current_for_pages,':',','),' ',','),'|',',');
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_bd_flowtabs
create or replace trigger wwv_bd_flowtabs
before delete on wwv_flow_tabs
for each row
begin
--
-- delete compound condition that may exist for this tab
--
if :old.display_condition_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.tab_plsql_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowtabs;
/
prompt ...trigger wwv_biu_flow_tl_tabs
create or replace trigger wwv_biu_flow_tl_tabs
before insert or update on wwv_flow_toplevel_tabs
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.display_condition_type = 'COMPOUND'
and nvl(:new.display_condition_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.display_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bd_flowtopleveltabs
create or replace trigger wwv_bd_flowtopleveltabs
before delete on wwv_flow_toplevel_tabs
for each row
begin
--
-- delete compound condition that may exist for this parent tab
--
if :old.display_condition_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.display_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowtopleveltabs;
/
prompt ...trigger wwv_biu_flow_lists_of_values
create or replace trigger wwv_biu_flow_lists_of_values
before insert or update on wwv_flow_lists_of_values$
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.lov_query is null then
:new.lov_query := '.'||:new.id||'.';
elsif :new.lov_query = '.' then
:new.lov_query := '.'||:new.id||'.';
end if;
--
-- last updated
--
:new.LAST_UPDATED_BY := wwv_flow.g_user;
:new.LAST_UPDATED_ON := sysdate;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
show errors
prompt ...trigger wwv_biu_flow_lists_of_valuesd
create or replace trigger wwv_biu_flow_lists_of_valuesd
before insert or update on wwv_flow_list_of_values_data
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.lov_disp_cond_type = 'COMPOUND'
and nvl(:new.lov_disp_cond_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.lov_disp_cond)
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
if :new.lov_return_value is null then
:new.lov_return_value := :new.lov_disp_value;
end if;
if :new.lov_disp_value is null then
:new.lov_disp_value := :new.lov_return_value;
end if;
--
if :new.flow_id is null then
for c1 in (select flow_id
from wwv_flow_lists_of_values$
where id = :new.lov_id) loop
:new.flow_id := c1.flow_id;
exit;
end loop;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
show errors
prompt ...trigger wwv_bd_lists_of_valuesd
create or replace trigger wwv_bd_lists_of_valuesd
before delete on wwv_flow_list_of_values_data
for each row
begin
--
-- delete compound condition that may exist for this lov item
--
if :old.lov_disp_cond_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.lov_disp_cond)
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_lists_of_valuesd;
/
prompt ...trigger wwv_biu_wwv_flow_trees
create or replace trigger wwv_biu_wwv_flow_trees
before insert or update on wwv_flow_trees
for each row
begin
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
create unique index WWV_FLOW_TREES_FKIDX ON WWV_FLOW_TREES(FLOW_ID,ID)
/
prompt ...trigger wwv_biu_flow_steps
create or replace trigger wwv_biu_flow_steps
before insert or update on wwv_flow_steps
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_on := sysdate;
:new.created_by := wwv_flow.g_user;
end if;
if (inserting or updating) and :new.id2 is null then
:new.id2 := wwv_flow_id.next_val;
end if;
if :new.step_sub_title_type is null then
:new.step_sub_title_type := 'TEXT_WITH_SUBSTITUTIONS';
end if;
:new.alias := upper(:new.alias);
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
else
if :new.last_updated_on is null and wwv_flow.g_user is not null then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end if;
--
-- cascade update to application
--
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end;
/
prompt ...trigger wwv_biu_step_buttons
create or replace trigger wwv_biu_step_buttons
before insert or update on wwv_flow_step_buttons
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.button_condition_type = 'COMPOUND'
and nvl(:new.button_condition_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.button_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
for c1 in (select tab_name from wwv_flow_tabs where flow_id = :new.flow_id and tab_name = :new.button_name) loop
raise_application_error (-20001,wwv_flow_lang.system_message('TRIGGER.BUTTON_TAB_NAMES_NOT_EQUAL'));
exit;
end loop;
--
-- remove trailing spaces
--
for i in 1..10 loop
:new.button_condition := rtrim(:new.button_condition );
:new.button_condition := rtrim(:new.button_condition ,chr(10));
:new.button_condition := rtrim(:new.button_condition ,chr(13));
end loop;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.flow_step_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_bd_flowstepbuttons
create or replace trigger wwv_bd_flowstepbuttons
before delete on wwv_flow_step_buttons
for each row
begin
--
-- delete compound condition that may exist for this button
--
if :old.button_condition_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.button_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowstepbuttons;
/
create unique index wwv_flow_step_buttons_fkidx on wwv_flow_step_buttons(flow_id,flow_step_id,id)
/
prompt ...trigger wwv_biu_step_branches
create or replace trigger wwv_biu_step_branches
before insert or update on wwv_flow_step_branches
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.branch_condition_type = 'COMPOUND'
and nvl(:new.branch_condition_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.branch_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
if :new.branch_condition_type = 'REQUEST_IS_NUMERIC' then
:new.branch_condition := 'Numeric Check.';
end if;
if :new.branch_condition_type = '%null%' then
:new.branch_condition_type := null;
end if;
--
-- remove trailing spaces
--
for i in 1..10 loop
:new.branch_condition := rtrim(:new.branch_condition );
:new.branch_condition := rtrim(:new.branch_condition ,chr(10));
:new.branch_condition := rtrim(:new.branch_condition ,chr(13));
end loop;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.flow_step_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_bd_flowstepbranches
create or replace trigger wwv_bd_flowstepbranches
before delete on wwv_flow_step_branches
for each row
begin
--
-- delete compound condition that may exist for this branch
--
if :old.branch_condition_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.branch_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowstepbranches;
/
create unique index wwv_flow_step_branches_fkidx on wwv_flow_step_branches(flow_id,flow_step_id,id)
/
prompt ...trigger wwv_biu_flow_step_branch_args
create or replace trigger wwv_biu_flow_step_branch_args
before insert or update on wwv_flow_step_branch_args
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_step_items
create or replace trigger wwv_biu_step_items
before insert or update on wwv_flow_step_items
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating then
if :old.display_when_type = 'COMPOUND'
and nvl(:new.display_when_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.display_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
--
if :old.read_only_when_type = 'COMPOUND'
and nvl(:new.read_only_when_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.read_only_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end if;
-----------------
-- default values
--
if :new.begin_on_new_line is null then
:new.begin_on_new_line := 'YES';
end if;
if :new.begin_on_new_field is null then
:new.begin_on_new_field := 'YES';
end if;
if :new.colspan is null then
:new.colspan := 1;
end if;
if :new.rowspan is null then
:new.rowspan := 1;
end if;
if :new.label_alignment is null then
:new.label_alignment := 'LEFT';
end if;
if :new.field_alignment is null then
:new.field_alignment := 'LEFT';
end if;
if :new.lov_display_null is null then
:new.lov_display_null := 'NO';
end if;
if :new.accept_processing is null then
:new.accept_processing := 'REPLACE_EXISTING';
end if;
if :new.is_Persistent is null then
:new.is_Persistent := 'Y';
end if;
-- maintain the length of the item name
:new.name_length := length(:new.name);
-- force field into upper case
:new.name := upper(:new.name);
--
-- remove trailing whitespace from source column
:new.source := rtrim(rtrim(ltrim(:new.source)),chr(13)||chr(10));
--
if replace(:new.named_lov,'%null%') is not null then
begin
select replace(lov_query,chr(13),null) into :new.lov
from wwv_flow_lists_of_values$
where flow_id = :new.flow_id and lov_name = :new.named_lov;
exception when others then null;
end;
end if;
--
-- cascade to computations
--
if updating and :new.name != upper(:old.name) then
begin
update wwv_flow_computations
set computation_item = :new.name
where flow_id = :new.flow_id
and upper(computation_item) = upper(:old.name);
--
update wwv_flow_step_computations
set computation_item = :new.name
where flow_id = :new.flow_id
and upper(computation_item) = upper(:old.name);
exception when others then null;
end;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.flow_step_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
show errors
prompt ...trigger wwv_bd_flowstepitems
create or replace trigger wwv_bd_flowstepitems
before delete on wwv_flow_step_items
for each row
begin
--
-- delete compound conditions that may exist for this item
--
if :old.display_when_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.display_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
if :old.read_only_when_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.read_only_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
--
-- cascade delete flow and step computations referencing item
--
begin
delete wwv_flow_computations
where upper(computation_item) = upper(:old.name)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
delete wwv_flow_step_computations
where upper(computation_item) = upper(:old.name)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
--
-- cascade update to page
--
begin
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :old.flow_id and
id = :old.flow_step_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
exception when others then null;
end;
end wwv_bd_flowstepitems;
/
prompt ...trigger wwv_biu_step_item_help
create or replace trigger wwv_biu_step_item_help
before insert or update on wwv_flow_step_item_help
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
create index WWV_FLOW_STEP_ITEM_HELP_FKIDX ON WWV_FLOW_STEP_ITEM_HELP(FLOW_ITEM_ID)
/
prompt ...trigger wwv_biu_step_computations
create or replace trigger wwv_biu_step_computations
before insert or update on wwv_flow_step_computations
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.compute_when_type = 'COMPOUND'
and nvl(:new.compute_when_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.compute_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
if :new.computation_point is null then
:new.computation_point := 'AFTER_SUBMIT';
end if;
if :new.computation_processed is null then
:new.computation_processed := 'REPLACE_EXISTING';
end if;
if :new.computation_type is null then
:new.computation_type := 'SQL_EXPRESSION';
end if;
--
-- remove trailing spaces
--
for i in 1..10 loop
:new.compute_when := rtrim(:new.compute_when );
:new.compute_when := rtrim(:new.compute_when ,chr(10));
:new.compute_when := rtrim(:new.compute_when ,chr(13));
end loop;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.flow_step_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_bd_flowstepcomputations
create or replace trigger wwv_bd_flowstepcomputations
before delete on wwv_flow_step_computations
for each row
begin
--
-- delete compound condition that may exist for this computation
--
if :old.compute_when_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.compute_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowstepcomputations;
/
create unique index WWV_FLOW_STEP_COMPUTATI_FKIDX ON WWV_FLOW_STEP_COMPUTATIONS(FLOW_ID,FLOW_STEP_ID,ID)
/
prompt ...trigger wwv_biu_step_validations
create or replace trigger wwv_biu_step_validations
before insert or update on wwv_flow_step_validations
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.validation_condition_type = 'COMPOUND'
and nvl(:new.validation_condition_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.validation_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
:new.validation_condition := rtrim( :new.validation_condition, ' '||chr(10)||chr(13) );
--
-- remove trailing spaces
--
for i in 1..10 loop
:new.validation := rtrim(:new.validation );
:new.validation := rtrim(:new.validation ,chr(10));
:new.validation := rtrim(:new.validation ,chr(13));
end loop;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.flow_step_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_bd_flowstepvalidations
create or replace trigger wwv_bd_flowstepvalidations
before delete on wwv_flow_step_validations
for each row
begin
--
-- delete compound condition that may exist for this validation
--
if :old.validation_condition_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.validation_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowstepvalidations;
/
create unique index wwv_flow_step_validatio_fkidx on wwv_flow_step_validations(flow_id,flow_step_id,id)
/
prompt ...trigger wwv_biu_step_processing
create or replace trigger wwv_biu_step_processing
before insert or update on wwv_flow_step_processing
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.process_when_type = 'COMPOUND'
and nvl(:new.process_when_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.process_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.flow_step_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_bd_flowstepprocessing
create or replace trigger wwv_bd_flowstepprocessing
before delete on wwv_flow_step_processing
for each row
begin
--
-- delete compound condition that may exist for this process
--
if :old.process_when_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.process_when)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowstepprocessing;
/
create unique index wwv_flow_step_processin_fkidx on wwv_flow_step_processing(flow_id,flow_step_id,id)
/
prompt ...trigger wwv_biu_button_template
create or replace trigger wwv_biu_button_template
before insert or update on wwv_flow_button_templates
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bi_flowcals
create or replace trigger wwv_bi_flowcals
before insert or update on wwv_flow_cals
for each row
begin
--
-- ID
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- Default the display type
--
if :new.display_type is null then
:new.display_type := 'COL';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bi_flowcaltemplates
create or replace trigger wwv_bi_flowcaltemplates
before insert or update on wwv_flow_cal_templates
for each row
begin
--
-- ID
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_themes
create or replace trigger wwv_biu_flow_themes
before insert or update on wwv_flow_themes
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bu_flow_themes
create or replace trigger wwv_bu_flow_themes
before update on wwv_flow_themes
for each row
begin
for c1 in (select theme_id from wwv_flows where id = :new.flow_id
and security_group_id = :new.security_group_id) loop
if :new.theme_id = c1.theme_id then
update wwv_flows set
default_button_template = :new.default_button_template,
default_calendar_template = :new.default_calendar_template ,
default_chart_template = :new.default_chart_template ,
default_form_template = :new.default_form_template ,
default_label_template = :new.default_label_template ,
default_list_template = :new.default_list_template ,
default_listr_template = :new.default_listr_template ,
default_menu_template = :new.default_menu_template ,
default_menur_template = :new.default_menur_template ,
default_page_template = :new.default_page_template ,
default_region_template = :new.default_region_template ,
default_report_template = :new.default_report_template ,
default_reportr_template = :new.default_reportr_template ,
default_tabform_template = :new.default_tabform_template ,
default_wizard_template = :new.default_wizard_template ,
error_template = :new.error_template ,
printer_friendly_template = :new.printer_friendly_template
where id = :new.flow_id
and security_group_id = :new.security_group_id;
end if;
end loop;
end;
/
prompt ...trigger wwv_biu_flowpageplugtemp
create or replace trigger wwv_biu_flowpageplugtemp
before insert or update on wwv_flow_page_plug_templates
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flowrowtemplates
create or replace trigger wwv_biu_flowrowtemplates
before insert or update on wwv_flow_row_templates
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.row_template_before_rows is null then
:new.row_template_before_rows := ' ';
end if;
if :new.row_template_after_rows is null then
:new.row_template_after_rows := ' ';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flowlisttemplates
create or replace trigger wwv_biu_flowlisttemplates
before insert or update on wwv_flow_list_templates
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.list_template_before_rows is null then
:new.list_template_before_rows := ' ';
end if;
if :new.list_template_after_rows is null then
:new.list_template_after_rows := ' ';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flowpageplugs
create or replace trigger wwv_biu_flowpageplugs
before insert or update on wwv_flow_page_plugs
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.plug_display_condition_type = 'COMPOUND'
and nvl(:new.plug_display_condition_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.plug_display_when_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
if :new.plug_source_type is null then
:new.plug_source_type := 'STATIC_TEXT';
end if;
if :new.plug_query_row_template is not null then
:new.plug_query_format_out := 'TEMPLATE';
end if;
--
-- remove trailing spaces
--
for i in 1..10 loop
:new.plug_display_when_condition := rtrim(:new.plug_display_when_condition );
:new.plug_display_when_condition := rtrim(:new.plug_display_when_condition ,chr(10));
:new.plug_display_when_condition := rtrim(:new.plug_display_when_condition ,chr(13));
end loop;
if :new.plug_source is not null then
wwv_flow_page_cache_api.lob_replace(
p_lob => :new.plug_source,
p_what => chr(13),
p_with => null);
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.page_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
create index wwv_flow_page_plugs_fk on wwv_flow_page_plugs (flow_id,page_id)
/
prompt ...trigger wwv_biu_flow_pg_generic_attr
create or replace trigger wwv_biu_flow_pg_generic_attr
before insert or update on wwv_flow_page_generic_attr
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
for c1 in (select flow_id, page_id from wwv_flow_page_plugs
where id = :new.region_id
and security_group_id = :new.security_group_id) loop
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = c1.flow_id and
id = c1.page_id and
security_group_id = :new.security_group_id;
end loop;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_bd_flowpageplugs
create or replace trigger wwv_bd_flowpageplugs
before delete on wwv_flow_page_plugs
for each row
begin
--
-- cascade update to null items with this plug
-- ignore errors on mutating tables
--
begin
update wwv_flow_step_items
set item_plug_id = null
where flow_id = :old.flow_id and flow_step_id = :old.page_id and item_plug_id = :old.id;
exception when others then null;
end;
--
-- delete compound condition that may exist for this region
--
if :old.plug_display_condition_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.plug_display_when_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowpageplugs;
/
prompt ...trigger wwv_biu_flow_sessions
create or replace trigger wwv_biu_flow_sessions
before insert or update on wwv_flow_sessions$
for each row
begin
--
-- note: new.id is typically never null on insert
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
--
--
if inserting then
if :new.created_by is null then
:new.created_by := user;
end if;
if wwv_flow_utilities.db_version_is_at_least('10.1.0.3') then -- for 10.1.0.3 or higher database with dbms_crypto
wwv_flow_security.g_crypto_salt := wwv_flow_security.crypto_randombytes(p_numbytes => 32);
else -- for pre 10.1.0.3 database w/o dbms_crypto
--wwv_flow_security.g_crypto_salt := utl_raw.cast_to_raw(wwv_flow_element.g_element);
wwv_flow_security.g_crypto_salt := wwv_flow_security.crypto_randombytes;
end if;
if :new.session_id_hashed is null then
:new.session_id_hashed := wwv_flow_security.hash_session_id;
begin
:new.ip_address := rawtohex(wwv_flow_security.g_crypto_salt);
exception when others then
:new.ip_address := null;
end;
end if;
:new.created_on := sysdate;
:new.last_changed := sysdate;
if :new.remote_addr is null then
:new.remote_addr := wwv_flow.g_remote_addr;
end if;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create or replace trigger WWV_FLOW_SESSIONS$_T1
BEFORE
delete on WWV_FLOW_SESSIONS$
for each row
begin
delete from wwv_flow_debug where id = :old.id;
delete from wwv_flow_worksheet_rpts where session_id = :old.id;
--
update wwv_flow_companies
set last_login = trunc(:old.CREATED_ON)
where provisioning_company_id = :old.security_group_id and
(last_login < trunc(:old.CREATED_ON) or last_login is null);
end;
/
prompt ...trigger wwv_flow_purged_sess_biu
create or replace trigger wwv_flow_purged_sess_biu
before insert or update on wwv_flow_purged_sessions$
for each row
begin
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...trigger wwv_biu_flow_preferences
create or replace trigger wwv_biu_flow_preferences
before insert or update on wwv_flow_preferences$
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create index wwv_flow_preferences$_fkidx on wwv_flow_preferences$(security_group_id,user_id,preference_name)
/
prompt ...trigger wwv_biu_flow_messages$
create or replace trigger wwv_biu_flow_messages$
before insert or update on wwv_flow_messages$
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end if;
:new.name := upper(:new.name);
:new.message_language := lower(:new.message_language);
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger on wwv_flow_patches
create or replace trigger wwv_biu_flow_patches
before insert or update on wwv_flow_patches
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
create unique index wwv_flow_patches_fkidx on wwv_flow_patches(flow_id,id)
/
prompt ...trigger on wwv_flow_developers
create or replace trigger wwv_biu_flow_developers
before insert or update on wwv_flow_developers
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.user_id is null then
:new.user_id := :new.id;
end if;
end if;
:new.userid := upper(:new.userid);
--
-- set admin privs
--
if instr(:new.DEVELOPER_ROLE,'ADMIN') > 0 then
:new.DEVELOPER_ROLE := 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create index wwv_flow_developers_fkidx on wwv_flow_developers(security_group_id,userid)
/
prompt ...set security group id = 10
begin
wwv_flow_security.g_security_group_id := 10;
end;
/
prompt ...trigger wwv_biu_flow_lists
create or replace trigger wwv_biu_flow_lists
before insert or update on wwv_flow_lists
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end if;
if :new.list_status is null then
:new.list_status := 'PERSONAL';
end if;
if :new.list_displayed is null then
:new.list_displayed := 'BY_DEFAULT';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
create unique index wwv_flow_lists_fkidx on wwv_flow_lists(flow_id,id)
/
prompt ...trigger wwv_biu_flow_list_items
create or replace trigger wwv_biu_flow_list_items
before insert or update on wwv_flow_list_items
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
elsif updating
and :old.list_item_disp_cond_type = 'COMPOUND'
and nvl(:new.list_item_disp_cond_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.list_item_disp_condition)
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
if :new.list_item_owner is not null then
:new.list_item_owner := user;
end if;
for c1 in (select flow_id from wwv_flow_lists where id = :new.list_id) loop
:new.flow_id := c1.flow_id;
exit;
end loop;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger biu_wwv_flow_shared_queries
create or replace trigger biu_wwv_flow_shared_queries
before insert or update on wwv_flow_shared_queries
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
:new.created_on := sysdate;
:new.created_by := v('USER');
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger biu_wwv_flow_sqry_sql
create or replace trigger biu_wwv_flow_sqry_sql
before insert or update on wwv_flow_shared_qry_sql_stmts
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
:new.created_on := sysdate;
:new.created_by := v('USER');
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger biu_wwv_flow_report_layouts
create or replace trigger biu_wwv_flow_report_layouts
before insert or update on wwv_flow_report_layouts
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
:new.created_on := sysdate;
:new.created_by := v('USER');
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_bd_flowlistitems
create or replace trigger wwv_bd_flowlistitems
before delete on wwv_flow_list_items
for each row
begin
--
-- delete compound condition that may exist for this list item
--
if :old.list_item_disp_cond_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.list_item_disp_condition)
and flow_id = :old.flow_id
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowlistitems;
/
create index wwv_flow_list_items_fkidx on wwv_flow_list_items(list_id,flow_id,list_item_display_sequence)
/
prompt ...trigger bi_wwv_dictionary$_fer
create or replace trigger bi_wwv_dictionary$_fer
before insert on wwv_flow_dictionary$
for each row
begin
if :new.owner is null then
:new.owner := 'PUBLIC';
end if;
if :new.language is null then
:new.language := 'en-us';
end if;
:new.words_capitalized := upper( substr( :new.words, 1, 1 ) ) ||
lower( substr( :new.words, 2 ) );
:new.words_soundex := soundex( :new.words );
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create index wwv_flow_dictionary$_fkidx on wwv_flow_dictionary$(security_group_id)
/
prompt ...trigger wwv_flows_biu_image_repo
create or replace trigger wwv_flows_biu_image_repo
before insert or update on wwv_flow_image_repository
for each row
begin
:new.upper_image_name := upper(:new.image_name);
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
--
if :new.security_group_id = 10 then
:new.image_tag := '#IMAGE_PREFIX#' || :new.image_name;
else
if :new.flow_id = 0 then
:new.image_tag := '#WORKSPACE_IMAGES#' || :new.image_name;
else
:new.image_tag := '#APP_IMAGES#' || :new.image_name;
end if;
end if;
end;
/
create unique index wwv_flow_image_reposito_fkidx on wwv_flow_image_repository(security_group_id,flow_id,id)
/
prompt ...trigger wwv_flows_biu_css_repo
create or replace trigger wwv_flows_biu_css_repo
before insert or update on wwv_flow_css_repository
for each row
begin
:new.upper_css_name := upper(:new.css_name);
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
--
if :new.security_group_id = 10 then
:new.css_tag := '#IMAGE_PREFIX#' || :new.css_name;
else
:new.css_tag := '#COMPANY_IMAGES#' || :new.css_name;
end if;
end;
/
create index wwv_flow_css_repository_fkidx on wwv_flow_css_repository(security_group_id,id)
/
prompt ...trigger wwv_flows_biu_html_repo
create or replace trigger wwv_flows_biu_html_repo
before insert or update on wwv_flow_html_repository
for each row
begin
:new.upper_html_name := upper(:new.html_name);
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
--
if :new.security_group_id = 10 then
:new.html_tag := '#IMAGE_PREFIX#' || :new.html_name;
else
if :new.flow_id = 0 then
:new.html_tag := '#COMPANY_IMAGES#' || :new.html_name;
else
:new.html_tag := '#FLOW_IMAGES#' || :new.html_name;
end if;
end if;
end;
/
show errors
create unique index wwv_flow_html_repositor_fkidx on wwv_flow_html_repository(security_group_id,flow_id,id)
/
prompt ...trigger wwv_biu_flow_lov_values
create or replace trigger wwv_biu_flow_lov_values
before insert or update on wwv_flow_lov_values
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end if;
if :new.list_owner is null then
:new.list_owner := nvl(wwv_flow.g_user,user);
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
show errors
create or replace trigger wwv_bd_flowlovvalues
before delete on wwv_flow_lov_values
for each row
begin
--
-- delete compound condition that may exist for this lov item
--
if :old.list_display_cond_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.list_disp_cond)
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowlovvalues;
/
prompt ...insert branch types into lov values (obsolete as of 12/02)
begin
wwv_flow_security.g_security_group_id := 10;
end;
/
delete from wwv_flow_lov_values where list_name = 'BRANCH_TYPES';
insert into wwv_flow_lov_values
(id, list_owner, list_name, list_display_value, list_return_value, list_sequence,
list_disp_cond, list_display_cond_type, security_group_id)
values
(null,null,'BRANCH_TYPES','Branch to PAGE (Use Page Number or Page Alias)', 'BRANCH_TO_STEP', 10, null, null, null)
/
insert into wwv_flow_lov_values
(id, list_owner, list_name, list_display_value, list_return_value, list_sequence,
list_disp_cond, list_display_cond_type, security_group_id)
values
(null,null,'BRANCH_TYPES','Branch to PL/SQL Procedure', 'PLSQL', 10, null, null, null)
/
insert into wwv_flow_lov_values
(id, list_owner, list_name, list_display_value, list_return_value, list_sequence,
list_disp_cond, list_display_cond_type, security_group_id)
values
(null,null,'BRANCH_TYPES','Branch to URL (Redirect to this URL)', 'REDIRECT_URL', 10, null, null, null)
/
insert into wwv_flow_lov_values
(id, list_owner, list_name, list_display_value, list_return_value, list_sequence,
list_disp_cond, list_display_cond_type, security_group_id)
values
(null,null,'BRANCH_TYPES','Branch to Page identified by Item (Use Item Name)',
'BRANCH_TO_PAGE_IDENT_BY_ITEM', 10, null, null, null)
/
insert into wwv_flow_lov_values
(id, list_owner, list_name, list_display_value, list_return_value, list_sequence,
list_disp_cond, list_display_cond_type, security_group_id)
values
(null,null,'BRANCH_TYPES','Branch to URL identified by Item (Use Item Name)',
'BRANCH_TO_URL_IDENT_BY_ITEM', 10, null, null, null)
/
insert into wwv_flow_lov_values
(id, list_owner, list_name, list_display_value, list_return_value, list_sequence,
list_disp_cond, list_display_cond_type, security_group_id)
values
(null,null,'BRANCH_TYPES','Branch to Function Returning a URL',
'BRANCH_TO_FUNCTION_RETURNING_URL', 10, null, null, null)
/
commit;
prompt ...trigger wwv_biu_flow_eff_userid_map
create or replace trigger wwv_biu_flow_eff_userid_map
before insert or update on wwv_flow_effective_userid_map
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
show errors
create index WWV_FLOW_EFFECTIVE_USER_FKIDX ON WWV_FLOW_EFFECTIVE_USERID_MAP(SECURITY_GROUP_ID,flow_id,USERID)
/
prompt ...wwv_biu_flow_shortcuts
create or replace trigger wwv_biu_flow_shortcuts
before insert or update on wwv_flow_shortcuts
for each row
begin
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
:new.shortcut_consideration_seq := nvl(:new.shortcut_consideration_seq,1);
:new.shortcut_name := upper(:new.shortcut_name);
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
show errors
prompt ...trigger wwv_biu_flow_shortcut_um
create or replace trigger wwv_biu_flow_shortcut_um
before insert or update on wwv_flow_shortcut_usage_map
for each row
begin
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
show errors
prompt ...trigger wwv_biu_flow_mail_queue
create or replace trigger wwv_biu_flow_mail_queue
before insert or update on wwv_flow_mail_queue
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.mail_send_count is null then
:new.mail_send_count := 0;
end if;
end if;
:new.last_updated_on := sysdate;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create or replace trigger wwv_biu_flow_mail_log
before insert on wwv_flow_mail_log
for each row
begin
:new.last_updated_on := sysdate;
end;
/
create or replace trigger wwv_biu_flow_mail_att
before insert or update on wwv_flow_mail_attachments
for each row
begin
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end if;
:new.last_updated_on := sysdate;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...trigger wwv_biu_flow_sw_binds
create or replace trigger wwv_biu_flow_sw_binds
before insert or update on wwv_flow_sw_binds
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...trigger wwv_biu_flow_sw_results
create or replace trigger wwv_biu_flow_sw_results
before insert or update on wwv_flow_sw_results
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if inserting then
:new.started := sysdate;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...trigger wwv_biu_flow_sw_detail_results
create or replace trigger wwv_biu_flow_sw_detail_results
before insert or update on wwv_flow_sw_detail_results
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if inserting then
:new.started := sysdate;
elsif updating then
:new.last_updated := sysdate;
end if;
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...trigger wwv_biu_flow_sw_sql_cmds
create or replace trigger wwv_biu_flow_sw_sql_cmds
before insert or update on wwv_flow_sw_sql_cmds
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create unique index WWV_FLOW_SW_SQL_CMDS_FKIDX ON WWV_FLOW_SW_SQL_CMDS(SECURITY_GROUP_ID,PARSED_SCHEMA,id)
/
prompt ...wwv_biu_flow_import_export
create or replace trigger wwv_biu_flow_import_export
before insert or update on wwv_flow_import_export
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
create index WWV_FLOW_IMPORT_EXPORT_FKIDX1 ON WWV_FLOW_IMPORT_EXPORT(DATA_ID)
/
create unique index WWV_FLOW_IMPORT_EXPORT_FKIDX2 ON WWV_FLOW_IMPORT_EXPORT(SECURITY_GROUP_ID,DATA_ID,id)
/
prompt ...wwv_biu_flow_platform_pref
create or replace trigger wwv_biu_flow_platform_pref
before insert or update on wwv_flow_platform_pref
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
----------------------------
-- U S E R T A B L E E S
--
prompt ...wwv_flow_fnd_user trigger
create or replace trigger wwv_biu_fnd_user
before insert or update on wwv_flow_fnd_user
for each row
begin
if inserting and :new.user_id is null then
if :new.user_id is null then :new.user_id := wwv_flow_id.next_val; end if;
end if;
if inserting then
:new.creation_date := sysdate;
:new.created_by := nvl(wwv_flow.g_user,user);
end if;
if inserting and :new.account_expiry is null then
:new.account_expiry := sysdate;
end if;
if :new.start_date is null then
:new.start_date := sysdate;
end if;
if :new.end_date is null then
:new.end_date := :new.start_date + (365*20);
end if;
:new.user_name := upper(:new.user_name);
:new.allow_access_to_schemas := upper(:new.allow_access_to_schemas);
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_update_date := sysdate;
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
:new.user_name := upper(:new.user_name);
if INSERTING and :new.web_password is not null then
if wwv_flow_fnd_user_pw_pref.web_password_format is null or
wwv_flow_fnd_user_pw_pref.web_password_format = 'CLEAR_TEXT' then
-- not hashed, so hash it
:new.web_password2 := utl_raw.cast_to_raw(
wwv_crypt.one_way_hash_str(:new.web_password||:new.security_group_id||:new.user_name));
wwv_flow_fnd_user_api.g_password_save := utl_raw.cast_to_raw(wwv_crypt.one_way_hash_str(:new.web_password||:new.security_group_id));
:new.web_password := null;
:new.web_password_raw := null;
elsif wwv_flow_fnd_user_pw_pref.web_password_format = 'HEX_ENCODED_DIGEST' then
-- already hashed no need to do it again, however decode it
:new.web_password := utl_raw.cast_to_varchar2(:new.web_password);
if wwv_flow_api.g_fnd_user_password_action = true then
:new.web_password_raw := utl_raw.cast_to_raw(:new.web_password);
end if;
elsif wwv_flow_fnd_user_pw_pref.web_password_format = 'HEX_ENCODED_DIGEST_V2' then
-- apex v3.0 format already hashed with security group id and user name
:new.web_password2 := :new.web_password;
:new.web_password := null;
:new.web_password_raw := null;
elsif wwv_flow_fnd_user_pw_pref.web_password_format = 'DIGEST' then
-- its already as you want it so do nothing
null;
end if;
elsif UPDATING and :new.web_password is not null then
if :old.web_password is null or :new.web_password != :old.web_password then
if wwv_flow_fnd_user_pw_pref.web_password_format is null or
wwv_flow_fnd_user_pw_pref.web_password_format = 'CLEAR_TEXT' then
:new.web_password2 := utl_raw.cast_to_raw(
wwv_crypt.one_way_hash_str(:new.web_password||:new.security_group_id||:new.user_name));
wwv_flow_fnd_user_api.g_password_save := utl_raw.cast_to_raw(wwv_crypt.one_way_hash_str(:new.web_password||:new.security_group_id));
:new.web_password := null;
:new.web_password_raw := null;
:new.account_expiry := sysdate;
end if;
end if;
else
if :old.web_password2 is not null then
:new.web_password2 := :old.web_password2;
:new.web_password := null;
:new.web_password_raw := null;
elsif :old.web_password is not null then
:new.web_password := :old.web_password;
end if;
end if;
end wwv_biu_fnd_user;
/
show errors
prompt ...wwv_flow_fnd_user aiu trigger
create or replace trigger wwv_aiu_fnd_user
after insert or update on wwv_flow_fnd_user
for each row
declare
l_hist_id number;
l_found boolean;
begin
if INSERTING and wwv_flow_fnd_user_api.g_password_save is not null then
l_hist_id := wwv_flow_id.next_val;
insert into wwv_flow_password_history
(id, user_id, password, created, security_group_id)
values
(l_hist_id, :new.user_id, wwv_flow_fnd_user_api.g_password_save, trunc(sysdate), :new.security_group_id);
elsif UPDATING and wwv_flow_fnd_user_api.g_password_save is not null then
-- insert into history table only if this password with created date of today is not already saved
l_found := false;
for c1 in (select id into l_hist_id from wwv_flow_password_history
where user_id = :new.user_id and password = wwv_flow_fnd_user_api.g_password_save and created = trunc(sysdate) and security_group_id = :new.security_group_id) loop
l_found := true;
exit;
end loop;
if not l_found then
l_hist_id := wwv_flow_id.next_val;
insert into wwv_flow_password_history
(id, user_id, password, created, security_group_id)
values
(l_hist_id, :new.user_id, wwv_flow_fnd_user_api.g_password_save, trunc(sysdate), :new.security_group_id);
end if;
end if;
wwv_flow_fnd_user_api.g_password_save := null;
end wwv_aiu_fnd_user;
/
show errors
prompt ...index on wwv_flow_fnd_user
create unique index wwv_flow_fnd_user_idx1 on wwv_flow_fnd_user (USER_NAME, security_group_id);
create unique index WWV_FLOW_FND_USER_FKIDX ON WWV_FLOW_FND_USER(SECURITY_GROUP_ID,USER_NAME)
/
prompt ...package WWV_FLOW_FND_FUNCTION
create or replace package WWV_FLOW_FND_FUNCTION
is
function TEST (
function_name IN varchar2) return boolean;
end wwv_flow_fnd_function;
/
show errors
create or replace package body WWV_FLOW_FND_FUNCTION
as
function TEST (
function_name IN varchar2) return boolean
is
begin
return true;
end test;
end wwv_flow_fnd_function;
/
show errors
prompt ...package WWV_FLOW_FND_GLOBAL
create or replace package WWV_FLOW_FND_GLOBAL
is
user_id number;
end wwv_flow_fnd_global;
/
show errors
prompt ...trigger on wwv_flow_fnd_user_groups
create or replace trigger wwv_biu_fnd_user_groups
before insert or update on wwv_flow_fnd_user_groups
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end wwv_biu_fnd_user_groups;
/
show errors
create index WWV_FLOW_FND_USER_GROUP_FKIDX ON WWV_FLOW_FND_USER_GROUPS(SECURITY_GROUP_ID,GROUP_NAME)
/
prompt ...trigger on wwv_flow_fnd_group_users
create or replace trigger wwv_biu_fnd_group_users
before insert or update on wwv_flow_fnd_group_users
for each row
begin
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end wwv_biu_fnd_group_users;
/
create unique index wwv_flow_fnd_group_users_i1 on wwv_flow_fnd_group_users (group_id, user_id)
/
create unique index wwv_flow_fnd_group_users_i2 on wwv_flow_fnd_group_users (user_id, group_id)
/
------------------------
-- C O L L E C T I O N S
--
prompt ...wwv_biu_flow_collection
create or replace trigger wwv_biu_flow_collection
before insert or update on wwv_flow_collections$
for each row
begin
:new.user_id := wwv_flow.g_user;
:new.session_id := v('SESSION');
:new.collection_name := upper(:new.collection_name);
:new.flow_id := v('FLOW_ID');
if inserting then
if :new.id is null then
:new.id := wwv_flow_id.next_val;
:new.created_on := sysdate;
end if;
if :new.collection_changed is null then
:new.collection_changed := 'N';
else
:new.collection_changed := upper(:new.collection_changed);
end if;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...wwv_biu_flow_coll_members
create or replace trigger wwv_biu_flow_coll_members
before insert or update on wwv_flow_collection_members$
for each row
begin
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
------------------------
-- A U T H T A B L E S
--
create or replace trigger wwv_biu_flow_db_auth
before insert or update on wwv_flow_db_auth
for each row
begin
:new.db_auth_schema := upper(:new.db_auth_schema);
end;
/
create index WWV_FLOW_DB_AUTH_FKIDX ON WWV_FLOW_DB_AUTH(SECURITY_GROUP_ID,DB_AUTH_SCHEMA)
/
begin
insert into wwv_flow_db_auth (db_auth_schema,security_group_id) values (wwv_flow.g_flow_schema_owner,10);
commit;
end;
/
------------------------------
-- Updatable Report Attributes
--
prompt ...wwv_flow_biu_reg_upd_rpt_cols
create or replace trigger wwv_flow_biu_reg_upd_rpt_cols
before insert or update on wwv_flow_region_upd_rpt_cols
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end wwv_flow_biu_reg_upd_rpt_cols;
/
prompt ...trigger on wwv_flow_popup_lov_template
create or replace trigger wwv_biu_flow_popup_lov_tpl
before insert or update on wwv_flow_popup_lov_template
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
------------------------------------
-- M E N U S
--
prompt ...trigger on wwv_flow_menus
create or replace trigger wwv_biu_flow_menus
before insert or update on wwv_flow_menus
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger on wwv_flow_menu_options
create or replace trigger wwv_biu_flow_menu_options
before insert or update on wwv_flow_menu_options
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
elsif updating
and :old.display_when_cond_type = 'COMPOUND'
and nvl(:new.display_when_cond_type,'-123' ) <> 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.display_when_condition)
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
--
if :new.flow_id is null then
for c1 in (select flow_id
from wwv_flow_menus
where id = :new.menu_id) loop
:new.flow_id := c1.flow_id;
exit;
end loop;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_bd_flowmenuoptions
create or replace trigger wwv_bd_flowmenuoptions
before delete on wwv_flow_menu_options
for each row
begin
--
-- delete compound condition that may exist for this menu option
--
if :old.display_when_cond_type = 'COMPOUND' then
begin
delete wwv_flow_compound_conditions
where id = to_number(:old.display_when_condition)
and security_group_id = :old.security_group_id;
exception when others then null;
end;
end if;
end wwv_bd_flowmenuoptions;
/
prompt ...trigger on wwv_flow_menu_templates
create or replace trigger wwv_biu_flow_menu_templates
before insert or update on wwv_flow_menu_templates
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger on wwv_flow_version$
create sequence wwv_flow_version_seq
/
create or replace trigger wwv_flow_version_hist_trg
before insert on wwv_flow_version$
for each row
begin
select wwv_flow_version_seq.nextval,sysdate into :new.seq,:new.date_applied from dual;
end;
/
prompt ...trigger wwv_biu_flow_auth_setups
create or replace trigger wwv_biu_flow_auth_setups
before insert or update on wwv_flow_custom_auth_setups
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flow_entry_points
create or replace trigger wwv_biu_flow_entry_points
before insert or update on wwv_flow_entry_points
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flow_entry_point_args
create or replace trigger wwv_biu_flow_entry_point_args
before insert or update on wwv_flow_entry_point_args
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_web_services
create or replace trigger wwv_biu_flow_web_services
before insert or update on wwv_flow_shared_web_services
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flow_ws_operations
create or replace trigger wwv_biu_flow_ws_operations
before insert or update on wwv_flow_ws_operations
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_ws_parameters
create or replace trigger wwv_biu_flow_ws_parameters
before insert or update on wwv_flow_ws_parameters
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_ws_parms_map
create or replace trigger wwv_biu_flow_ws_parms_map
before insert or update on wwv_flow_ws_process_parms_map
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_flow_biu_lock_page
create or replace trigger wwv_flow_biu_lock_page
before insert or update on wwv_flow_lock_page
for each row
begin
--
-- ID
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.LOCKED_ON := sysdate;
end if;
--
-- maintain log
--
if inserting then
insert into wwv_flow_lock_page_log
(lock_id,lock_flow,lock_page,action,developer,lock_comment)
values
(:new.id,:new.flow_id,:new.object_id,'LOCK',:new.locked_by,:new.lock_comment);
end if;
if updating then
insert into wwv_flow_lock_page_log
(lock_id,lock_flow,lock_page,action,developer,lock_comment)
values
(:new.id,:new.flow_id,:new.object_id,'UPDATE',:new.locked_by,:new.lock_comment);
end if;
end;
/
create or replace trigger wwv_flow_lock_page_t2
BEFORE
delete on wwv_flow_lock_page
for each row
begin
insert into wwv_flow_lock_page_log (
lock_id,lock_flow,lock_page,ACTION,ACTION_DATE,DEVELOPER,lock_comment)
values (
:old.id, :old.flow_id, :old.object_id,'UNLOCK',sysdate,v('USER'),:old.lock_comment);
end;
/
prompt ...trigger wwv_flow_biu_lock_pg_log
create or replace trigger wwv_flow_biu_lock_pg_log
before insert or update on wwv_flow_lock_page_log
for each row
begin
--
-- ID
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.action_date := sysdate;
end if;
end;
/
prompt ...trigger wwv_biu_flow_compound_conds
create or replace trigger wwv_biu_flow_compound_conds
before insert or update on wwv_flow_compound_conditions
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
create or replace trigger biu_wwv_flow_hnt_table_info
before insert or update on wwv_flow_hnt_table_info
for each row
begin
if inserting and :new.table_id is null then
:new.table_id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
end if;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end;
/
prompt ...trigger wwv_biu_flow_hnt_column_info
create or replace trigger biu_wwv_flow_hnt_col_info
before insert or update on wwv_flow_hnt_column_info
for each row
begin
if inserting and :new.column_id is null then
:new.column_id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
end if;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end;
/
create or replace trigger biu_wwv_flow_hnt_lov_data
before insert or update on wwv_flow_hnt_lov_data
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end;
/
prompt ...trigger wwv_biu_flow_chart_ser_attr
create or replace trigger wwv_biu_flow_chart_ser_attr
before insert or update on wwv_flow_region_chart_ser_attr
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
for c1 in (select flow_id, page_id from wwv_flow_page_plugs
where id = :new.region_id
and security_group_id = :new.security_group_id) loop
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = c1.flow_id and
id = c1.page_id and
security_group_id = :new.security_group_id;
end loop;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_biu_flow_query_definition
create or replace trigger wwv_biu_flow_query_definition
before insert or update on wwv_flow_query_definition
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_query_object
create or replace trigger wwv_biu_flow_query_object
before insert or update on wwv_flow_query_object
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_query_column
create or replace trigger wwv_biu_flow_query_column
before insert or update on wwv_flow_query_column
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_query_condition
create or replace trigger wwv_biu_flow_query_condition
before insert or update on wwv_flow_query_condition
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.operator is null then
:new.operator := 'NONE';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_region_report_column
create or replace trigger wwv_biu_region_report_column
before insert or update on wwv_flow_region_report_column
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- sync flow id for translations
--
if :new.flow_id is null then
for c1 in (select flow_id
from wwv_flow_page_plugs
where id = :new.region_id) loop
:new.flow_id := c1.flow_id;
exit;
end loop;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_flow_region_report_filter
create or replace trigger wwv_biu_region_report_filter
before insert or update on wwv_flow_region_report_filter
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.last_updated_on := sysdate;
:new.last_updated_by := wwv_flow.g_user;
end if;
end;
/
prompt ...trigger wwv_biu_wwv_flow_hnt_procedure
create or replace trigger wwv_biu_wwv_flow_hnt_procedure
before insert or update on wwv_flow_hnt_procedure_info
for each row
begin
if :new.procedure_id is null then
:new.procedure_id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
end;
/
prompt ...trigger wwv_biu_wwv_flow_hnt_argument
create or replace trigger wwv_biu_wwv_flow_hnt_argument
before insert or update on wwv_flow_hnt_argument_info
for each row
begin
if :new.argument_id is null then
:new.argument_id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
end;
/
prompt ...trigger wwv_flow_biu_pg_groups
create or replace trigger wwv_flow_biu_pg_groups
before insert or update on wwv_flow_page_groups
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end wwv_flow_biu_pg_groups;
/
create or replace trigger wwv_biu_online_help
before insert on wwv_flow_online_help
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
create or replace trigger wwv_biu_online_help_ja
before insert on wwv_flow_online_help_ja
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
create or replace trigger wwv_flow_bi_qb_saved_qry
before insert on wwv_flow_qb_saved_query
for each row
begin
:new.id := nvl(:new.id,wwv_flow_id.next_val);
:new.query_owner := nvl(:new.query_owner,wwv_flow_user_api.get_default_schema);
:new.created_by := nvl(:new.created_by,wwv_flow.g_user);
:new.created_on := nvl(:new.created_on,sysdate);
:new.last_updated_by := wwv_flow.g_user;
:new.last_updated_on := sysdate;
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end;
/
create or replace trigger wwv_flow_bi_qb_saved_cond
before insert on wwv_flow_qb_saved_cond
for each row
begin
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
if not wwv_flow.g_import_in_progress then
update wwv_flow_qb_saved_query set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.id and
security_group_id = :new.security_group_id;
end if;
end;
/
create or replace trigger wwv_flow_bi_qb_saved_join
before insert on wwv_flow_qb_saved_join
for each row
begin
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
if not wwv_flow.g_import_in_progress then
update wwv_flow_qb_saved_query set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.id and
security_group_id = :new.security_group_id;
end if;
end;
/
create or replace trigger wwv_flow_bi_qb_saved_tabs
before insert on wwv_flow_qb_saved_tabs
for each row
begin
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
if not wwv_flow.g_import_in_progress then
update wwv_flow_qb_saved_query set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flow_sw_stmts
create or replace trigger wwv_biu_flow_sw_stmts
before insert or update on wwv_flow_sw_stmts
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
end;
/
prompt ...trigger wwv_biu_flow_models
create or replace trigger wwv_biu_flow_models
before insert or update on wwv_flow_models
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
end;
/
prompt ...trigger wwv_biu_flow_model_pages
create or replace trigger wwv_biu_flow_model_pages
before insert or update on wwv_flow_model_pages
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
end;
/
prompt ...trigger wwv_flow_model_page_regions
create or replace trigger wwv_flow_model_page_regions
before insert or update on wwv_flow_model_page_regions
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
end;
/
prompt ...trigger wwv_biu_flow_model_page_cols
create or replace trigger wwv_biu_flow_model_page_cols
before insert or update on wwv_flow_model_page_cols
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
end;
/
prompt ...trigger wwv_flow_sc_trans_bi
CREATE OR REPLACE TRIGGER wwv_flow_sc_trans_bi
before insert or update on wwv_flow_sc_trans
for each row
declare
l_tid number;
begin
-- set security group id if null
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
-- set t_id as I need to amintain the order of transactions as they occured
if inserting and :new.transaction_id is null then
select nvl(max(transaction_id),0)+1 into l_tid from wwv_flow_sc_trans where session_id = :new.session_id;
:new.transaction_id := l_tid;
:new.transaction_status := 'N';
:new.created_on := sysdate;
else
:new.updated_on := sysdate;
end if;
end;
/
prompt ...trigger wwv_flow_restricted_schema_biu
CREATE OR REPLACE TRIGGER wwv_flow_restricted_schema_biu
before insert or update on wwv_flow_restricted_schemas
for each row
begin
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
if inserting then
:new.created_on := sysdate;
:new.created_by := nvl(wwv_flow.g_user,user);
else
:new.last_updated_on := sysdate;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
end if;
end;
/
prompt ...trigger wwv_flow_rschema_exception_biu
CREATE OR REPLACE TRIGGER wwv_flow_rschema_exception_biu
before insert or update on wwv_flow_rschema_exceptions
for each row
begin
if :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
--
if inserting then
:new.created_on := sysdate;
:new.created_by := nvl(wwv_flow.g_user,user);
else
:new.last_updated_on := sysdate;
:new.last_updated_by := nvl(wwv_flow.g_user,user);
end if;
end;
/
prompt ...trigger wwv_biu_install
create or replace trigger wwv_biu_install
before insert or update on wwv_flow_install
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.flow_id is null then
:new.flow_id := v('FB_FLOW_ID');
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
update wwv_flows set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.flow_id and
security_group_id = :new.security_group_id;
end if;
:new.prompt_substitution_01_yn := nvl(:new.prompt_substitution_01_yn, 'N');
:new.prompt_substitution_02_yn := nvl(:new.prompt_substitution_02_yn, 'N');
:new.prompt_substitution_03_yn := nvl(:new.prompt_substitution_03_yn, 'N');
:new.prompt_substitution_04_yn := nvl(:new.prompt_substitution_04_yn, 'N');
:new.prompt_substitution_05_yn := nvl(:new.prompt_substitution_05_yn, 'N');
:new.prompt_substitution_06_yn := nvl(:new.prompt_substitution_06_yn, 'N');
:new.prompt_substitution_07_yn := nvl(:new.prompt_substitution_07_yn, 'N');
:new.prompt_substitution_08_yn := nvl(:new.prompt_substitution_08_yn, 'N');
:new.prompt_substitution_09_yn := nvl(:new.prompt_substitution_09_yn, 'N');
:new.prompt_substitution_10_yn := nvl(:new.prompt_substitution_10_yn, 'N');
:new.prompt_substitution_11_yn := nvl(:new.prompt_substitution_11_yn, 'N');
:new.prompt_substitution_12_yn := nvl(:new.prompt_substitution_12_yn, 'N');
:new.prompt_substitution_13_yn := nvl(:new.prompt_substitution_13_yn, 'N');
:new.prompt_substitution_14_yn := nvl(:new.prompt_substitution_14_yn, 'N');
:new.prompt_substitution_15_yn := nvl(:new.prompt_substitution_15_yn, 'N');
:new.prompt_substitution_16_yn := nvl(:new.prompt_substitution_16_yn, 'N');
:new.prompt_substitution_17_yn := nvl(:new.prompt_substitution_17_yn, 'N');
:new.prompt_substitution_18_yn := nvl(:new.prompt_substitution_18_yn, 'N');
:new.prompt_substitution_19_yn := nvl(:new.prompt_substitution_19_yn, 'N');
:new.prompt_substitution_20_yn := nvl(:new.prompt_substitution_20_yn, 'N');
end;
/
prompt ...trigger wwv_biu_install_scripts
create or replace trigger wwv_biu_install_scripts
before insert or update on wwv_flow_install_scripts
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.flow_id is null then
:new.flow_id := v('FB_FLOW_ID');
end if;
if :new.install_id is null then
for c1 in (select id from wwv_flow_install where flow_id = :new.flow_id) loop
:new.install_id := c1.id;
end loop;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
update wwv_flow_install set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.install_id and
security_group_id = :new.security_group_id;
end if;
end if;
end;
/
prompt ...trigger wwv_biu_install_checks
create or replace trigger wwv_biu_install_checks
before insert or update on wwv_flow_install_checks
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.flow_id is null then
:new.flow_id := v('FB_FLOW_ID');
end if;
if :new.install_id is null then
for c1 in (select id from wwv_flow_install where flow_id = :new.flow_id) loop
:new.install_id := c1.id;
end loop;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
elsif updating then
:new.last_updated_by := nvl(wwv_flow.g_user,user);
:new.last_updated_on := sysdate;
end if;
update wwv_flow_install set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.install_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_install_build_opt
create or replace trigger wwv_biu_install_build_opt
before insert or update on wwv_flow_install_build_opt
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if :new.flow_id is null then
:new.flow_id := v('FB_FLOW_ID');
end if;
if :new.install_id is null then
for c1 in (select id from wwv_flow_install where flow_id = :new.flow_id) loop
:new.install_id := c1.id;
end loop;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
update wwv_flow_install set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
id = :new.install_id and
security_group_id = :new.security_group_id;
end if;
end;
/
prompt ...trigger wwv_biu_flowflashcharts
create or replace trigger wwv_biu_flowflashcharts
before insert or update on wwv_flow_flash_charts
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.updated_on := sysdate;
:new.updated_by := wwv_flow.g_user;
end if;
if :new.chart_xml is not null then
wwv_flow_page_cache_api.lob_replace(
p_lob => :new.chart_xml,
p_what => chr(13),
p_with => null);
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.page_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
prompt ...trigger wwv_biu_flowflashchartseries
create or replace trigger wwv_biu_flowflashchartseries
before insert or update on wwv_flow_flash_chart_series
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
if inserting then
:new.created_by := nvl(wwv_flow.g_user,user);
:new.created_on := sysdate;
end if;
--
-- last updated
--
if not wwv_flow.g_import_in_progress then
:new.updated_on := sysdate;
:new.updated_by := wwv_flow.g_user;
end if;
if :new.flow_id is null then
for c1 in (select flow_id
from wwv_flow_flash_charts
where id = :new.chart_id) loop
:new.flow_id := c1.flow_id;
exit;
end loop;
end if;
if :new.series_query is not null then
wwv_flow_page_cache_api.lob_replace(
p_lob => :new.series_query,
p_what => chr(13),
p_with => null);
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
end if;
--
-- last updated flash chart, page, cascades to update application
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_flash_charts set
updated_on = sysdate,
updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.chart_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
create or replace trigger wwv_flow_biu_standard_icons
before insert or update
on wwv_flow_standard_icons
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
create or replace trigger wwv_flow_biu_standard_css
before insert or update
on wwv_flow_standard_css
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
create or replace trigger wwv_flow_biu_standard_js
before insert or update
on wwv_flow_standard_js
for each row
begin
if inserting and :new.id is null then
:new.id := wwv_flow_id.next_val;
end if;
end;
/
----------------------
-- W O R K S H E E T S
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_id.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 and not wwv_flow.g_import_in_progress 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;
--
-- by default, reports show up as TABS
--
if inserting and :new.report_list_mode is null then
:new.report_list_mode := 'TABS';
end if;
:new.SHOW_SELECT_COLUMNS := nvl(:new.SHOW_SELECT_COLUMNS,'Y');
:new.SHOW_FILTER := nvl(:new.SHOW_FILTER,'Y');
:new.SHOW_CONTROL_BREAK := nvl(:new.SHOW_CONTROL_BREAK,'Y');
:new.SHOW_SORT := nvl(:new.SHOW_SORT,'Y');
:new.SHOW_HIGHLIGHT := nvl(:new.SHOW_HIGHLIGHT,'Y');
:new.SHOW_AGGREGATE := nvl(:new.SHOW_AGGREGATE,'Y');
:new.SHOW_CHART := nvl(:new.SHOW_CHART,'Y');
:new.SHOW_CALENDAR := nvl(:new.SHOW_CALENDAR,'Y');
:new.SHOW_FLASHBACK := nvl(:new.SHOW_FLASHBACK,'Y');
:new.SHOW_RESET := nvl(:new.SHOW_RESET,'Y');
:new.SHOW_DOWNLOAD := nvl(:new.SHOW_DOWNLOAD,'Y');
:new.SHOW_COMPUTATION := nvl(:new.SHOW_COMPUTATION,'Y');
:new.SHOW_HELP := nvl(:new.SHOW_HELP,'Y');
:new.SHOW_DETAIL_LINK := nvl(:new.SHOW_DETAIL_LINK,'Y');
:new.ALLOW_REPORT_SAVING := nvl(:new.ALLOW_REPORT_SAVING,'Y');
:new.ALLOW_REPORT_CATEGORIES := nvl(:new.ALLOW_REPORT_CATEGORIES,'Y');
:new.ALLOW_EXCLUDE_NULL_VALUES := nvl(:new.ALLOW_EXCLUDE_NULL_VALUES,'Y');
:new.ALLOW_HIDE_EXTRA_COLUMNS := nvl(:new.ALLOW_HIDE_EXTRA_COLUMNS,'Y');
:new.SHOW_FINDER_DROP_DOWN := nvl(:new.SHOW_FINDER_DROP_DOWN,'Y');
:new.SHOW_DISPLAY_ROW_COUNT := nvl(:new.SHOW_DISPLAY_ROW_COUNT,'Y');
:new.SHOW_SEARCH_BAR := nvl(:new.SHOW_SEARCH_BAR,'Y');
:new.SHOW_SEARCH_TEXTBOX := nvl(:new.SHOW_SEARCH_TEXTBOX,'Y');
:new.SHOW_ACTIONS_MENU := nvl(:new.SHOW_ACTIONS_MENU,'Y');
--
-- maintain column values
--
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;
--
-- last updated page, cascades to update application
--
if not wwv_flow.g_import_in_progress and :new.flow_id != 4900 then
wwv_flow_audit.g_cascade := true;
update wwv_flow_steps set
last_updated_on = sysdate,
last_updated_by = wwv_flow.g_user
where
flow_id = :new.flow_id and
id = :new.page_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
create or replace trigger wwv_flow_worksheets_bd_trig
before delete on wwv_flow_worksheets
begin
wwv_flow_worksheet.g_delete_in_progress := true;
end;
/
create or replace trigger wwv_flow_worksheets_ad_trig
after delete on wwv_flow_worksheets
begin
wwv_flow_worksheet.g_delete_in_progress := false;
end;
/
create or replace trigger wwv_flow_worksheet_cat_trig
before insert or update on wwv_flow_worksheet_categories
for each row
begin
--
-- maintain pk and timestamps
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.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);
:new.flow_id := nvl(wwv_flow.g_flow_id,0);
elsif updating then
:new.updated_on := sysdate;
:new.updated_by := nvl(wwv_flow.g_user,user);
end if;
--
-- display sequence
--
if inserting and :new.display_sequence is null then
select nvl(max(display_sequence),0) + 1 into :new.display_sequence
from wwv_flow_worksheet_categories
where worksheet_id = :new.worksheet_id;
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := wwv_flow.get_sgid;
end if;
end;
/
create sequence wwv_flow_worksheet_seq
/
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_id.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);
for c1 in (select wwv_flow_worksheet_seq.nextval lo
from dual ) loop
:new.load_order := c1.lo;
end loop;
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;
/
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_id.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;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := wwv_flow.get_sgid;
end if;
--
-- update parent timestamp
--
if not wwv_flow.g_import_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_worksheets set
updated_on = :new.updated_on,
updated_by = :new.updated_by
where
id = :new.worksheet_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
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_id.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;
/
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_id.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
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_id.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;
if :new.is_sortable is null then
:new.is_sortable := 'Y';
end if;
:new.allow_sorting := nvl(:new.allow_sorting,'Y');
:new.allow_filtering := nvl(:new.allow_filtering,'Y');
:new.allow_ctrl_breaks := nvl(:new.allow_ctrl_breaks,'Y');
:new.allow_aggregations := nvl(:new.allow_aggregations,'Y');
:new.allow_computations := nvl(:new.allow_computations,'Y');
:new.allow_charting := nvl(:new.allow_charting,'Y');
if :new.display_text_as is null then
:new.display_text_as := 'ESCAPE_SC';
end if;
if :new.heading_alignment is null then
:new.heading_alignment := 'CENTER';
end if;
if :new.column_alignment is null then
:new.column_alignment := 'LEFT';
end if;
if :new.rpt_show_filter_lov is null then
:new.rpt_show_filter_lov := 'D';
end if;
if :new.rpt_filter_date_ranges is null then
:new.rpt_filter_date_ranges := 'ALL';
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;
-- maintain distinct_value_filter
if :new.display_as = 'TEXTAREA' and :new.rpt_distinct_lov is null then
:new.rpt_distinct_lov := 'N';
elsif :new.rpt_distinct_lov is null then
:new.rpt_distinct_lov := 'Y';
end if;
-- use for prototype to get next available col, alter logic later to fill gaps
if inserting and :new.db_column_name is null then
:new.db_column_name := wwv_flow_worksheet_standard.get_next_db_column_name(:new.worksheet_id, :new.column_type);
end if;
if inserting and :new.display_order is null then
:new.display_order := wwv_flow_worksheet_standard.get_next_display_order_number(:new.worksheet_id);
end if;
if inserting and :new.column_identifier is null 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;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := wwv_flow.get_sgid;
end if;
--
-- update parent timestamp
--
if not wwv_flow.g_import_in_progress and not wwv_flow_worksheet.g_delete_in_progress then
wwv_flow_audit.g_cascade := true;
update wwv_flow_worksheets set
updated_on = :new.updated_on,
updated_by = :new.updated_by
where
id = :new.worksheet_id and
security_group_id = :new.security_group_id;
wwv_flow_audit.g_cascade := false;
end if;
end;
/
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_id.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;
/
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_id.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;
/
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_id.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;
/
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_id.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;
/
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_id.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.display_rows is null then
:new.display_rows := 15;
end if;
if :new.flashback_enabled is null then
:new.flashback_enabled := 'N';
end if;
if :new.report_seq is null then
:new.report_seq := 10;
end if;
if :new.is_default is null then
:new.is_default := 'N';
end if;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := wwv_flow.get_sgid;
end if;
end;
/
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_id.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.enabled is null then
:new.enabled := 'Y';
end if;
if inserting and :new.allow_delete is null then
:new.allow_delete := '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;
/
create or replace trigger wwv_flow_ws_computation_trig
before insert or update on wwv_flow_worksheet_computation
for each row
begin
--
-- maintain pk and timestamps
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.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.db_column_name is null then
:new.db_column_name := wwv_flow_worksheet_standard.get_next_computed_column_name(:new.worksheet_id, :new.report_id);
end if;
if :new.column_identifier is null then
:new.column_identifier := wwv_flow_worksheet_standard.get_next_computed_identifier(:new.worksheet_id, :new.report_id);
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;
/
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_id.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;
/
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_id.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;
/
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_id.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;
/
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_id.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;
/
create or replace trigger wwv_flow_app_groups_trg
before insert or update on wwv_flow_application_groups
for each row
begin
--
-- maintain pk and timestamps
--
if inserting and :new.id is null then
:new.id := wwv_flow_id.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;
--
-- vpd
--
if :new.security_group_id is null then
:new.security_group_id := wwv_flow.get_sgid;
end if;
end;
/
prompt ...done create triggers