rem Script to update schema from Release 1.0.6 to UATREL300909 rem Run in SQL*Plus connected as schema owner rem $Id:$ connect &3/&4@&5 -------------------------------------- -- Changed table additional_items -- -------------------------------------- -- Add/modify columns alter table ADDITIONAL_ITEMS modify CODE VARCHAR2(80); -------------------------------- -- Changed table role_types -- -------------------------------- -- Drop check constraints alter table ROLE_TYPES drop constraint AVCON_1205497163_RT_TY_000; -- Create/Recreate check constraints alter table ROLE_TYPES add constraint AVCON_1252415686_RT_TY_000 check (RT_TYPE IN ('DORT', 'PART', 'RT', 'PCMRT', 'QURT', 'ENRT', 'PRT')); ---------------------------------- -- Changed table caveat_texts -- ---------------------------------- -- Add/modify columns alter table CAVEAT_TEXTS add ADIT_CODE VARCHAR2(80); alter table CAVEAT_TEXTS add CONDITION VARCHAR2(240); -- Create/Recreate primary, unique and foreign key constraints alter table CAVEAT_TEXTS add constraint CATE_ADIT_FK foreign key (ADIT_CODE) references ADDITIONAL_ITEMS (CODE); -- Create/Recreate indexes create index CATE_ADIT_FK_I on CAVEAT_TEXTS (ADIT_CODE); ----------------------------- -- Changed table parties -- ----------------------------- -- Drop check constraints alter table PARTIES drop constraint AVCON_1205497163_PRTY__000; alter table PARTIES drop constraint AVCON_1205497163_STATU_000; alter table PARTIES drop constraint AVCON_1205497163_TRIPA_000; -- Create/Recreate check constraints alter table PARTIES add constraint AVCON_1252415686_PRTY__000 check (PRTY_TYPE IN ('PRTY', 'IO', 'EO', 'MKTP', 'PERS', 'MANU', 'NEMP', 'EMP', 'ORG' )); alter table PARTIES add constraint AVCON_1252415686_STATU_000 check (STATUS IN ('OPEN', 'EXPIRED', 'LOCKED')); alter table PARTIES add constraint AVCON_1252415686_TRIPA_000 check (TRIPARTITE_MEMBER IN ('YES', 'NO')); -------------------------------------- -- Changed table meter_size_codes -- -------------------------------------- -- Drop check constraints alter table METER_SIZE_CODES drop constraint AVCON_1205497163_VALID_000; alter table METER_SIZE_CODES drop constraint AVCON_1205497163_VALID_001; -- Create/Recreate check constraints alter table METER_SIZE_CODES add constraint AVCON_1252415686_VALID_000 check (VALID_FOR_EXISTING_METER IN ('YES', 'NO')); alter table METER_SIZE_CODES add constraint AVCON_1252415686_VALID_001 check (VALID_FOR_NEW_METER IN ('YES', 'NO')); --------------------------- -- Changed table costs -- --------------------------- -- Drop function-based indexes drop index COST_UK; -- Add/modify columns alter table COSTS modify ADIT_CODE VARCHAR2(80); -- Drop check constraints alter table COSTS drop constraint AVCON_1205497163_COST__000; -- Create/Recreate check constraints alter table COSTS add constraint AVCON_1252415686_COST__000 check (COST_TYPE IN ('LACO', 'COST', 'MOCO', 'HOCO', 'BACO', 'AICO', 'MECO')); -- Create/Recreate indexes create unique index COST_UK on COSTS (VALID_FROM, REGI_CODE, COST_TYPE, CASE COST_TYPE WHEN 'MOCO' THEN MODU_CODE WHEN 'BACO' THEN BAS_CODE WHEN 'HOCO' THEN HOU_CODE WHEN 'MECO' THEN METR_CODE WHEN 'AICO' THEN ADIT_CODE WHEN 'LACO' THEN ENTY_CODE END, CASE COST_TYPE WHEN 'LACO' THEN SVCPT_CODE WHEN 'AICO' THEN SVCPT_CODE ELSE NULL END, CASE COST_TYPE WHEN 'LACO' THEN MESC_CODE WHEN 'AICO' THEN MESC_CODE ELSE NULL END, CASE COST_TYPE WHEN 'LACO' THEN METY_CODE WHEN 'AICO' THEN METY_CODE ELSE NULL END, CASE COST_TYPE WHEN 'AICO' THEN ENTY_CODE ELSE NULL END); ------------------------------- -- Changed table documents -- ------------------------------- -- Drop check constraints alter table DOCUMENTS drop constraint AVCON_1205497163_DOCU__000; -- Create/Recreate check constraints alter table DOCUMENTS add constraint AVCON_1252415686_DOCU__000 check (DOCU_TYPE IN ('DOCU', 'INDO', 'EXDO', 'FIDO')); ------------------------------- -- Changed table enquiries -- ------------------------------- -- Add/modify columns alter table ENQUIRIES add PURGING_REQUIRED VARCHAR2(2); -- Drop primary, unique and foreign key constraints alter table ENQUIRIES drop constraint ENQU_COMT_AGENT_FK; alter table ENQUIRIES drop constraint ENQU_MESC_EXISTING_FK; alter table ENQUIRIES drop constraint ENQU_MESC_REQUIRED_FK; alter table ENQUIRIES drop constraint ENQU_SVCP_REQUIRED_FK; -- Create/Recreate primary, unique and foreign key constraints alter table ENQUIRIES add constraint ENQU_COMT_FK foreign key (AGENT_COMT_CODE) references CONTACT_MECHANISM_TYPES (CODE); alter table ENQUIRIES add constraint ENQU_MESC_EXISTING_METER_SI_FK foreign key (EXISTING_MESC_CODE) references METER_SIZE_CODES (CODE); alter table ENQUIRIES add constraint ENQU_MESC_FK foreign key (REQUIRED_MESC_CODE) references METER_SIZE_CODES (CODE); alter table ENQUIRIES add constraint ENQU_SVCP_FK foreign key (REQUIRED_SVCP_CODE) references SERVICE_PRESSURES (CODE); -- Drop check constraints alter table ENQUIRIES drop constraint AVCON_1205497163_AMR_R_000; alter table ENQUIRIES drop constraint AVCON_1205497163_BASE__000; alter table ENQUIRIES drop constraint AVCON_1205497163_CONVE_000; alter table ENQUIRIES drop constraint AVCON_1205497163_DOWNS_000; alter table ENQUIRIES drop constraint AVCON_1205497163_EMS_R_000; alter table ENQUIRIES drop constraint AVCON_1205497163_EXIST_000; alter table ENQUIRIES drop constraint AVCON_1205497163_EXIST_001; alter table ENQUIRIES drop constraint AVCON_1205497163_EXIST_002; alter table ENQUIRIES drop constraint AVCON_1205497163_HOUSI_000; alter table ENQUIRIES drop constraint AVCON_1205497163_INDIC_000; alter table ENQUIRIES drop constraint AVCON_1205497163_LOAD__000; alter table ENQUIRIES drop constraint AVCON_1205497163_MARKE_000; alter table ENQUIRIES drop constraint AVCON_1205497163_TWIN__000; -- Create/Recreate check constraints alter table ENQUIRIES add constraint AVCON_1252415686_AMR_R_000 check (AMR_REQUIRED IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_BASE__000 check (BASE_REQUIRED IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_CONVE_000 check (CONVERTOR_REQUIRED IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_DOWNS_000 check (DOWNSTREAM_BOOSTER_OR_COMPRESS IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_EMS_R_000 check (EMS_REQUIRED IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_EXIST_000 check (EXISTING_LOGGER IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_EXIST_001 check (EXISTING_METER_OWNER IN ('S', 'T', 'C')); alter table ENQUIRIES add constraint AVCON_1252415686_EXIST_002 check (EXISTING_CONVERTOR IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_HOUSI_000 check (HOUSING_REQUIRED IN ('YES', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_INDIC_000 check (INDICATIVE_TIME IN ('DY', 'AM', 'PM')); alter table ENQUIRIES add constraint AVCON_1252415686_LOAD__000 check (LOAD_CONTROL_TYPE IN ('Constant', 'On/Off', 'Modulating')); alter table ENQUIRIES add constraint AVCON_1252415686_MARKE_000 check (MARKET_SECTOR_CODE IN ('I', 'D')); alter table ENQUIRIES add constraint AVCON_1252415686_PURGI_000 check (PURGING_REQUIRED IN ('TM', 'FC', 'NO')); alter table ENQUIRIES add constraint AVCON_1252415686_TWIN__000 check (TWIN_STREAM_REQUIRED IN ('YES', 'NO')); -- Drop indexes drop index ENQU_COMT_AGENT_FK_I; drop index ENQU_MESC_EXISTING_FK_I; drop index ENQU_SVCP_REQUIRED_FK_I; -- Create/Recreate indexes create index ENQU_COMT_FK_I on ENQUIRIES (AGENT_COMT_CODE); create index ENQU_MESC_EXISTING_METER_SI__I on ENQUIRIES (EXISTING_MESC_CODE); create index ENQU_SVCP_FK_I on ENQUIRIES (REQUIRED_SVCP_CODE); ---------------------------- -- Changed table quotes -- ---------------------------- -- Drop check constraints alter table QUOTES drop constraint AVCON_1205497163_QUTE__000; -- Create/Recreate check constraints alter table QUOTES add constraint AVCON_1252415686_QUTE__000 check (QUTE_TYPE IN ('AQ', 'MQ', 'QUTE')); ------------------------------------ -- Changed table document_roles -- ------------------------------------ -- Drop check constraints alter table DOCUMENT_ROLES drop constraint AVCON_1205497163_DORO__000; -- Create/Recreate check constraints alter table DOCUMENT_ROLES add constraint AVCON_1252415686_DORO__000 check (DORO_TYPE IN ('DRRO', 'DORO', 'ENDO', 'QUDO')); --------------------------------------- -- Changed table err$_caveat_texts -- --------------------------------------- -- Add/modify columns alter table ERR$_CAVEAT_TEXTS add ADIT_CODE VARCHAR2(4000); alter table ERR$_CAVEAT_TEXTS add CONDITION VARCHAR2(4000); -------------------------------- -- Changed table error_logs -- -------------------------------- -- Add/modify columns alter table ERROR_LOGS add CALL_STACK VARCHAR2(2000); alter table ERROR_LOGS add BACKTRACE VARCHAR2(2000); alter table ERROR_LOGS add ERROR_STACK VARCHAR2(2000); ---------------------------------------- -- Changed table ext_material_costs -- ---------------------------------------- -- Add/modify columns alter table EXT_MATERIAL_COSTS add METY_CODE VARCHAR2(240); alter table EXT_MATERIAL_COSTS add MESC_CODE VARCHAR2(240); alter table EXT_MATERIAL_COSTS add ENTY_CODE VARCHAR2(240); --------------------------------- -- Changed table quote_items -- --------------------------------- -- Add/modify columns alter table QUOTE_ITEMS modify ADIT_CODE VARCHAR2(80); -- Drop check constraints alter table QUOTE_ITEMS drop constraint AVCON_1205497163_QUIT__000; -- Create/Recreate check constraints alter table QUOTE_ITEMS add constraint AVCON_1252415686_QUIT__000 check (QUIT_TYPE IN ('MQI', 'HQI', 'BQI', 'LQI', 'QUIT', 'AQI')); ------------------------------------------ -- Changed table regi_enqu_exclusions -- ------------------------------------------ -- Add/modify columns alter table REGI_ENQU_EXCLUSIONS modify ADIT_CODE VARCHAR2(80); --------------------------- -- Changed view v_aico -- --------------------------- CREATE OR REPLACE VIEW V_AICO AS SELECT id ,adit_code ,selling_price ,cost_price ,delivery_cost ,regi_code ,enty_code ,mety_code ,mesc_code ,valid_from AS effective_from ,least(nvl(valid_to ,TO_DATE('2099' ,'yyyy')) ,nvl(next_valid_from ,TO_DATE('2099' ,'yyyy'))) AS effective_to FROM (SELECT id ,adit_code ,selling_price ,cost_price ,delivery_cost ,regi_code ,enty_code ,mety_code ,mesc_code ,valid_from ,valid_to ,lead(valid_from) over(PARTITION BY adit_code, regi_code, enty_code, mety_code, mesc_code ORDER BY valid_from) AS next_valid_from FROM costs cost WHERE cost_type = 'AICO'); ------------------------------------ -- Changed view v_quote_details -- ------------------------------------ CREATE OR REPLACE VIEW V_QUOTE_DETAILS AS SELECT enquiry_id ,quote_id ,enty_code ,module_code ,lead_time ,additional_items ,bas_code ,hou_code ,qmax ,qmin ,inlet_orientation ,outlet_orientation ,total_cost ,row_number FROM (SELECT q.enqu_id AS enquiry_id ,t.qute_id AS quote_id ,MAX(t.enty_code) over(PARTITION BY qute_id) AS enty_code ,MAX(t.modu_code) over(PARTITION BY qute_id) AS module_code ,MAX(t.lead_time) over(PARTITION BY qute_id ORDER BY qute_id) AS lead_time ,get_quote_items(qute_id) AS additional_items ,MAX(t.bas_code) over(PARTITION BY qute_id) AS bas_code ,MAX(t.hou_code) over(PARTITION BY qute_id) AS hou_code ,MAX(t.qmax) over(PARTITION BY qute_id) AS qmax ,MAX(t.qmin) over(PARTITION BY qute_id) AS qmin ,MAX(t.inlet_orientation) over(PARTITION BY qute_id) AS inlet_orientation ,MAX(t.outlet_orientation) over(PARTITION BY qute_id) AS outlet_orientation ,SUM(nvl(t.selling_price ,0) + nvl(t.delivery_price ,0)) over(PARTITION BY qute_id ORDER BY qute_id) AS total_cost ,row_number() over(PARTITION BY qute_id ORDER BY qute_id) AS row_number FROM quote_items t ,quotes q WHERE t.qute_id = q.id AND NOT (quit_type = 'AQI' AND adit_code IN ('LIFTING GEAR')) ORDER BY enqu_id ,quote_id ,t.id) all_quote_items WHERE row_number = 1; comment on column V_QUOTE_DETAILS.ENQUIRY_ID is 'The ID of the enquiry this quote relates to.'; comment on column V_QUOTE_DETAILS.QUOTE_ID is 'The unique ID for this particular quote.'; comment on column V_QUOTE_DETAILS.ENTY_CODE is 'The type of enquiry quoted for.'; comment on column V_QUOTE_DETAILS.MODULE_CODE is 'The name of the module quoted for.'; comment on column V_QUOTE_DETAILS.LEAD_TIME is 'The lead time for installation. This is the max of any additional item''s lead times.'; comment on column V_QUOTE_DETAILS.ADDITIONAL_ITEMS is 'Any additional items included in the quote.'; comment on column V_QUOTE_DETAILS.BAS_CODE is 'The module base code.'; comment on column V_QUOTE_DETAILS.QMAX is 'The meter module''s Q max.'; comment on column V_QUOTE_DETAILS.QMIN is 'The meter module''s Q min.'; comment on column V_QUOTE_DETAILS.INLET_ORIENTATION is 'The orientation of the meter inlet.'; comment on column V_QUOTE_DETAILS.OUTLET_ORIENTATION is 'The orientatino of the meter outlet.'; comment on column V_QUOTE_DETAILS.TOTAL_COST is 'The total cost of the quote. This figure excludes any lifting gear.'; exit