OE Schema SQL Scripts

-- Generated by Oracle SQL Developer Data Modeler 3.1.0.691
--   at:        2013-04-25 21:30:09 IST
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



DROP VIEW OE.ACCOUNT_MANAGERS
;
DROP VIEW OE.BOMBAY_INVENTORY
;
DROP VIEW OE.CUSTOMERS_VIEW
;
DROP VIEW OE.OC_CORPORATE_CUSTOMERS
;
DROP VIEW OE.OC_CUSTOMERS
;
DROP VIEW OE.OC_INVENTORIES
;
DROP VIEW OE.OC_ORDERS
;
DROP VIEW OE.OC_PRODUCT_INFORMATION
;
DROP VIEW OE.ORDERS_VIEW
;
DROP VIEW OE.PRODUCTS
;
DROP VIEW OE.PRODUCT_PRICES
;
DROP VIEW OE.SYDNEY_INVENTORY
;
DROP VIEW OE.TORONTO_INVENTORY
;
DROP TABLE OE.CATEGORIES_TAB CASCADE CONSTRAINTS
;
DROP TABLE OE.CUSTOMERS CASCADE CONSTRAINTS
;
DROP TABLE OE.INVENTORIES CASCADE CONSTRAINTS
;
DROP TABLE OE.ORDERS CASCADE CONSTRAINTS
;
DROP TABLE OE.ORDER_ITEMS CASCADE CONSTRAINTS
;
DROP TABLE OE.PRODUCT_DESCRIPTIONS CASCADE CONSTRAINTS
;
DROP TABLE OE.PRODUCT_INFORMATION CASCADE CONSTRAINTS
;
DROP TABLE OE.PROMOTIONS CASCADE CONSTRAINTS
;
DROP TABLE OE.PURCHASEORDER CASCADE CONSTRAINTS
;
DROP TABLE OE.WAREHOUSES CASCADE CONSTRAINTS
;
CREATE USER HR
    IDENTIFIED BY 
    ACCOUNT UNLOCK
;

CREATE USER OE
    IDENTIFIED BY 
    ACCOUNT UNLOCK
;

CREATE USER SYS
    IDENTIFIED BY 
    ACCOUNT UNLOCK
;



CREATE OR REPLACE TYPE OE.ACTIONS_T
;
/




CREATE OR REPLACE TYPE OE.ACTION_T
;
/




CREATE OR REPLACE TYPE OE.CATALOG_TYP
;
/




CREATE OR REPLACE TYPE OE.CATEGORY_TYP
;
/




CREATE OR REPLACE TYPE OE.COMPOSITE_CATEGORY_TYP
;
/




CREATE OR REPLACE TYPE OE.CORPORATE_CUSTOMER_TYP
;
/




CREATE OR REPLACE TYPE OE.CUSTOMER_TYP
;
/




CREATE OR REPLACE TYPE OE.CUST_ADDRESS_TYP
;
/




CREATE OR REPLACE TYPE OE.INVENTORY_TYP
;
/




CREATE OR REPLACE TYPE OE.LEAF_CATEGORY_TYP
;
/




CREATE OR REPLACE TYPE OE.LINEITEMS_T
;
/




CREATE OR REPLACE TYPE OE.LINEITEM_T
;
/




CREATE OR REPLACE TYPE OE.ORDER_ITEM_TYP
;
/




CREATE OR REPLACE TYPE OE.ORDER_TYP
;
/




CREATE OR REPLACE TYPE OE.PART_T
;
/




CREATE OR REPLACE TYPE OE.PRODUCT_INFORMATION_TYP
;
/




CREATE OR REPLACE TYPE OE.PURCHASEORDER_T
;
/




CREATE OR REPLACE TYPE OE.REJECTION_T
;
/




CREATE OR REPLACE TYPE OE.SHIPPING_INSTRUCTIONS_T
;
/




CREATE OR REPLACE TYPE OE.WAREHOUSE_TYP
;
/




CREATE OR REPLACE TYPE OE.ACTION_V
    IS VARRAY ( 4 ) OF ACTION_T
;
/




CREATE OR REPLACE TYPE OE.INVENTORY_LIST_TYP
    IS TABLE OF INVENTORY_TYP
;
/




CREATE OR REPLACE TYPE OE.LINEITEM_V
    IS VARRAY ( 2147483647 ) OF LINEITEM_T
;
/




CREATE OR REPLACE TYPE OE.ORDER_ITEM_LIST_TYP
    IS TABLE OF ORDER_ITEM_TYP
;
/




CREATE OR REPLACE TYPE OE.ORDER_LIST_TYP
    IS TABLE OF ORDER_TYP
;
/




CREATE OR REPLACE TYPE OE.PHONE_LIST_TYP
    IS VARRAY ( 5 ) OF VARCHAR2 (25)
;
/




CREATE OR REPLACE TYPE OE.PRODUCT_REF_LIST_TYP
    IS TABLE OF NUMBER (6)
;
/




CREATE OR REPLACE TYPE OE.SUBCATEGORY_REF_LIST_TYP
    IS TABLE OF REF CATEGORY_TYP
;
/




CREATE OR REPLACE TYPE OE.ACTIONS_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        ACTION ACTION_V
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.ACTION_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        ACTIONED_BY VARCHAR2 (10) ,
        DATE_ACTIONED DATE
    ) FINAL
;
/




CREATE OR REPLACE TYPE OE.CATALOG_TYP
    UNDER COMPOSITE_CATEGORY_TYP (
        MEMBER FUNCTION GETCATALOGNAME
        RETURN VARCHAR2 ,
        OVERRIDING MEMBER FUNCTION CATEGORY_DESCRIBE
        RETURN VARCHAR2
    ) FINAL
;
/



CREATE OR REPLACE TYPE BODY OE.CATALOG_TYP
    AS
    MEMBER FUNCTION GETCATALOGNAME
        RETURN VARCHAR2
    AS
      BEGIN
    -- Return the category name from the supertype
    RETURN self.category_name;
  END;
    OVERRIDING MEMBER FUNCTION CATEGORY_DESCRIBE
        RETURN VARCHAR2
    AS
      BEGIN
    RETURN 'catalog_typ';
  END;
    END
;
/




CREATE OR REPLACE TYPE OE.CATEGORY_TYP
    AS OBJECT
    (
        CATEGORY_NAME VARCHAR2 (50) ,
        CATEGORY_DESCRIPTION VARCHAR2 (1000) ,
        CATEGORY_ID NUMBER (2) ,
        PARENT_CATEGORY_ID NUMBER (2) ,
        NOT INSTANTIABLE MEMBER FUNCTION CATEGORY_DESCRIBE
        RETURN VARCHAR2
    ) NOT FINAL NOT INSTANTIABLE
;
/




CREATE OR REPLACE TYPE OE.COMPOSITE_CATEGORY_TYP
    UNDER CATEGORY_TYP (
        SUBCATEGORY_REF_LIST SUBCATEGORY_REF_LIST_TYP ,
        OVERRIDING MEMBER FUNCTION CATEGORY_DESCRIBE
        RETURN VARCHAR2
    ) NOT FINAL
;
/



CREATE OR REPLACE TYPE BODY OE.COMPOSITE_CATEGORY_TYP
    AS
    OVERRIDING MEMBER FUNCTION CATEGORY_DESCRIBE
        RETURN VARCHAR2
    AS
        BEGIN
      RETURN 'composite_category_typ';
    END;
   
    END
;
/




CREATE OR REPLACE TYPE OE.CORPORATE_CUSTOMER_TYP
    UNDER CUSTOMER_TYP (
        ACCOUNT_MGR_ID NUMBER (6)
    ) FINAL
;
/




CREATE OR REPLACE TYPE OE.CUSTOMER_TYP
    AS OBJECT
    (
        CUSTOMER_ID NUMBER (6) ,
        CUST_FIRST_NAME VARCHAR2 (20) ,
        CUST_LAST_NAME VARCHAR2 (20) ,
        CUST_ADDRESS CUST_ADDRESS_TYP ,
        PHONE_NUMBERS PHONE_LIST_TYP ,
        NLS_LANGUAGE VARCHAR2 (3) ,
        NLS_TERRITORY VARCHAR2 (30) ,
        CREDIT_LIMIT NUMBER (9,2) ,
        CUST_EMAIL VARCHAR2 (30) ,
        CUST_ORDERS ORDER_LIST_TYP
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.CUST_ADDRESS_TYP
    AS OBJECT
    (
        STREET_ADDRESS VARCHAR2 (40) ,
        POSTAL_CODE VARCHAR2 (10) ,
        CITY VARCHAR2 (30) ,
        STATE_PROVINCE VARCHAR2 (10) ,
        COUNTRY_ID CHAR (2)
    ) FINAL
;
/




CREATE OR REPLACE TYPE OE.INVENTORY_TYP
    AS OBJECT
    (
        PRODUCT_ID NUMBER (6) ,
        WAREHOUSE WAREHOUSE_TYP ,
        QUANTITY_ON_HAND NUMBER (8)
    ) FINAL
;
/




CREATE OR REPLACE TYPE OE.LEAF_CATEGORY_TYP
    UNDER CATEGORY_TYP (
        PRODUCT_REF_LIST PRODUCT_REF_LIST_TYP ,
        OVERRIDING MEMBER FUNCTION CATEGORY_DESCRIBE
        RETURN VARCHAR2
    ) FINAL
;
/



CREATE OR REPLACE TYPE BODY OE.LEAF_CATEGORY_TYP
    AS
    OVERRIDING MEMBER FUNCTION CATEGORY_DESCRIBE
        RETURN VARCHAR2
    AS
        BEGIN
       RETURN  'leaf_category_typ';
    END;
   
    END
;
/




CREATE OR REPLACE TYPE OE.LINEITEMS_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        LINEITEM LINEITEM_V
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.LINEITEM_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        ITEMNUMBER NUMBER (38) ,
        DESCRIPTION VARCHAR2 (256) ,
        PART PART_T
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.ORDER_ITEM_TYP
    AS OBJECT
    (
        ORDER_ID NUMBER (12) ,
        LINE_ITEM_ID NUMBER (3) ,
        UNIT_PRICE NUMBER (8,2) ,
        QUANTITY NUMBER (8) ,
        PRODUCT_REF REF PRODUCT_INFORMATION_TYP
    ) FINAL
;
/




CREATE OR REPLACE TYPE OE.ORDER_TYP
    AS OBJECT
    (
        ORDER_ID NUMBER (12) ,
        ORDER_MODE VARCHAR2 (8) ,
        CUSTOMER_REF REF CUSTOMER_TYP ,
        ORDER_STATUS NUMBER (2) ,
        ORDER_TOTAL NUMBER (8,2) ,
        SALES_REP_ID NUMBER (6) ,
        ORDER_ITEM_LIST ORDER_ITEM_LIST_TYP
    ) FINAL
;
/




CREATE OR REPLACE TYPE OE.PART_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        PART_NUMBER VARCHAR2 (14) ,
        QUANTITY NUMBER (12,4) ,
        UNITPRICE NUMBER (14,2)
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.PRODUCT_INFORMATION_TYP
    AS OBJECT
    (
        PRODUCT_ID NUMBER (6) ,
        PRODUCT_NAME VARCHAR2 (50) ,
        PRODUCT_DESCRIPTION VARCHAR2 (2000) ,
        CATEGORY_ID NUMBER (2) ,
        WEIGHT_CLASS NUMBER (1) ,
        WARRANTY_PERIOD INTERVAL YEAR TO MONTH ,
        SUPPLIER_ID NUMBER (6) ,
        PRODUCT_STATUS VARCHAR2 (20) ,
        LIST_PRICE NUMBER (8,2) ,
        MIN_PRICE NUMBER (8,2) ,
        CATALOG_URL VARCHAR2 (50) ,
        INVENTORY_LIST INVENTORY_LIST_TYP
    ) FINAL
;
/




CREATE OR REPLACE TYPE OE.PURCHASEORDER_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        REFERENCE VARCHAR2 (30) ,
        ACTIONS ACTIONS_T ,
        REJECTION REJECTION_T ,
        REQUESTOR VARCHAR2 (128) ,
        USERID VARCHAR2 (10) ,
        COST_CENTER VARCHAR2 (4) ,
        SHIPPING_INSTRUCTIONS SHIPPING_INSTRUCTIONS_T ,
        SPECIAL_INSTRUCTIONS VARCHAR2 (2048) ,
        LINEITEMS LINEITEMS_T
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.REJECTION_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        REJECTED_BY VARCHAR2 (10) ,
        DATE_REJECTED DATE ,
        REASON_REJECTED VARCHAR2 (2048)
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.SHIPPING_INSTRUCTIONS_T
    AS OBJECT
    (
        SYS_XDBPD$ UNKNOWN ,
        SHIP_TO_NAME VARCHAR2 (20) ,
        SHIP_TO_ADDRESS VARCHAR2 (256) ,
        SHIP_TO_PHONE VARCHAR2 (24)
    ) NOT FINAL
;
/




CREATE OR REPLACE TYPE OE.WAREHOUSE_TYP
    AS OBJECT
    (
        WAREHOUSE_ID NUMBER (3) ,
        WAREHOUSE_NAME VARCHAR2 (35) ,
        LOCATION_ID NUMBER (4)
    ) FINAL
;
/


CREATE OR REPLACE DIRECTORY SS_OE_XMLDIR
    AS 'c:\app\Reswanth\product\11.2.0\dbhome_1\demo\schema\order_entry\'
;

CREATE OR REPLACE DIRECTORY SUBDIR
    AS 'c:\app\Reswanth\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep'
;

CREATE TABLE OE.CATEGORIES_TAB
    OF OE.CATEGORY_TYP
    (
    CONSTRAINT SYS_C0011038 PRIMARY KEY ( CATEGORY_ID )
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE USERS
        LOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;




CREATE TABLE OE.CUSTOMERS
    (
     CUSTOMER_ID NUMBER (6)  NOT NULL ,
     CUST_FIRST_NAME VARCHAR2 (20 BYTE)
     CONSTRAINT CUST_FNAME_NN NOT NULL ,
     CUST_LAST_NAME VARCHAR2 (20 BYTE)
     CONSTRAINT CUST_LNAME_NN NOT NULL ,
     CUST_ADDRESS OE.CUST_ADDRESS_TYP ,
     PHONE_NUMBERS OE.PHONE_LIST_TYP ,
     NLS_LANGUAGE VARCHAR2 (3 BYTE) ,
     NLS_TERRITORY VARCHAR2 (30 BYTE) ,
     CREDIT_LIMIT NUMBER (9,2) ,
     CUST_EMAIL VARCHAR2 (30 BYTE) ,
     ACCOUNT_MGR_ID NUMBER (6) ,
     CUST_GEO_LOCATION MDSYS.SDO_GEOMETRY ,
     DATE_OF_BIRTH DATE ,
     MARITAL_STATUS VARCHAR2 (20 BYTE) ,
     GENDER VARCHAR2 (1 BYTE) ,
     INCOME_LEVEL VARCHAR2 (20 BYTE)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    COLUMN CUST_ADDRESS NOT SUBSTITUTABLE AT ALL LEVELS
    COLUMN CUST_GEO_LOCATION NOT SUBSTITUTABLE AT ALL LEVELS
;



ALTER TABLE OE.CUSTOMERS
    ADD CONSTRAINT CUSTOMER_ID_MIN
    CHECK ( customer_id > 0)
;


ALTER TABLE OE.CUSTOMERS
    ADD CONSTRAINT CUSTOMER_CREDIT_LIMIT_MAX
    CHECK ( credit_limit <= 5000)
;


COMMENT ON TABLE OE.CUSTOMERS IS 'Contains customers data either entered by an employee or by the customer
him/herself over the Web.'
;

COMMENT ON COLUMN OE.CUSTOMERS.CUSTOMER_ID IS 'Primary key column.'
;

COMMENT ON COLUMN OE.CUSTOMERS.CUST_FIRST_NAME IS 'NOT NULL constraint.'
;

COMMENT ON COLUMN OE.CUSTOMERS.CUST_LAST_NAME IS 'NOT NULL constraint.'
;

COMMENT ON COLUMN OE.CUSTOMERS.CUST_ADDRESS IS 'Object column of type address_typ.'
;

COMMENT ON COLUMN OE.CUSTOMERS.PHONE_NUMBERS IS 'Varray column of type phone_list_typ'
;

COMMENT ON COLUMN OE.CUSTOMERS.CREDIT_LIMIT IS 'Check constraint.'
;

COMMENT ON COLUMN OE.CUSTOMERS.ACCOUNT_MGR_ID IS 'References hr.employees.employee_id.'
;

COMMENT ON COLUMN OE.CUSTOMERS.CUST_GEO_LOCATION IS 'SDO (spatial) column.'
;
CREATE INDEX OE.CUST_UPPER_NAME_IX ON OE.CUSTOMERS
    (
     UPPER("CUST_LAST_NAME"),UPPER("CUST_FIRST_NAME")
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.CUST_ACCOUNT_MANAGER_IX ON OE.CUSTOMERS
    (
     ACCOUNT_MGR_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.CUST_LNAME_IX ON OE.CUSTOMERS
    (
     CUST_LAST_NAME ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.CUST_EMAIL_IX ON OE.CUSTOMERS
    (
     CUST_EMAIL ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE OE.CUSTOMERS
    ADD CONSTRAINT CUSTOMERS_PK PRIMARY KEY ( CUSTOMER_ID ) ;


CREATE TABLE OE.INVENTORIES
    (
     PRODUCT_ID NUMBER (6)  NOT NULL ,
     WAREHOUSE_ID NUMBER (3)
     CONSTRAINT INVENTORY_WAREHOUSE_ID_NN NOT NULL ,
     QUANTITY_ON_HAND NUMBER (8)
     CONSTRAINT INVENTORY_QOH_NN NOT NULL
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE OE.INVENTORIES IS 'Tracks availability of products by product_it and warehouse_id.'
;

COMMENT ON COLUMN OE.INVENTORIES.PRODUCT_ID IS 'Part of concatenated primary key, references product_information.product_id.'
;

COMMENT ON COLUMN OE.INVENTORIES.WAREHOUSE_ID IS 'Part of concatenated primary key, references warehouses.warehouse_id.'
;
CREATE INDEX OE.INVENTORY_IX ON OE.INVENTORIES
    (
     WAREHOUSE_ID ASC ,
     PRODUCT_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.INV_PRODUCT_IX ON OE.INVENTORIES
    (
     PRODUCT_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE OE.INVENTORIES
    ADD CONSTRAINT INVENTORY_PK PRIMARY KEY ( PRODUCT_ID, WAREHOUSE_ID ) ;


CREATE TABLE OE.ORDERS
    (
     ORDER_ID NUMBER (12)  NOT NULL ,
     ORDER_DATE TIMESTAMP WITH LOCAL TIME ZONE
     CONSTRAINT ORDER_DATE_NN NOT NULL ,
     ORDER_MODE VARCHAR2 (8 BYTE) ,
     CUSTOMER_ID NUMBER (6)
     CONSTRAINT ORDER_CUSTOMER_ID_NN NOT NULL ,
     ORDER_STATUS NUMBER (2) ,
     ORDER_TOTAL NUMBER (8,2) ,
     SALES_REP_ID NUMBER (6) ,
     PROMOTION_ID NUMBER (6)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



ALTER TABLE OE.ORDERS
    ADD CONSTRAINT ORDER_MODE_LOV
    CHECK ( ORDER_MODE IN ('direct', 'online'))
;


ALTER TABLE OE.ORDERS
    ADD CONSTRAINT ORDER_TOTAL_MIN
    CHECK ( order_total >= 0)
;


COMMENT ON TABLE OE.ORDERS IS 'Contains orders entered by a salesperson as well as over the Web.'
;

COMMENT ON COLUMN OE.ORDERS.ORDER_ID IS 'PRIMARY KEY column.'
;

COMMENT ON COLUMN OE.ORDERS.ORDER_DATE IS 'TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.'
;

COMMENT ON COLUMN OE.ORDERS.ORDER_MODE IS 'CHECK constraint.'
;

COMMENT ON COLUMN OE.ORDERS.ORDER_STATUS IS '0: Not fully entered, 1: Entered, 2: Canceled - bad credit, -
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid'
;

COMMENT ON COLUMN OE.ORDERS.ORDER_TOTAL IS 'CHECK constraint.'
;

COMMENT ON COLUMN OE.ORDERS.SALES_REP_ID IS 'References hr.employees.employee_id.'
;

COMMENT ON COLUMN OE.ORDERS.PROMOTION_ID IS 'Sales promotion ID. Used in SH schema'
;
CREATE INDEX OE.ORD_SALES_REP_IX ON OE.ORDERS
    (
     SALES_REP_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.ORD_CUSTOMER_IX ON OE.ORDERS
    (
     CUSTOMER_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.ORD_ORDER_DATE_IX ON OE.ORDERS
    (
     ORDER_DATE ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE OE.ORDERS
    ADD CONSTRAINT ORDER_PK PRIMARY KEY ( ORDER_ID ) ;


CREATE TABLE OE.ORDER_ITEMS
    (
     ORDER_ID NUMBER (12)  NOT NULL ,
     LINE_ITEM_ID NUMBER (3)  NOT NULL ,
     PRODUCT_ID NUMBER (6)  NOT NULL ,
     UNIT_PRICE NUMBER (8,2) ,
     QUANTITY NUMBER (8)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE OE.ORDER_ITEMS IS 'Example of many-to-many resolution.'
;

COMMENT ON COLUMN OE.ORDER_ITEMS.ORDER_ID IS 'Part of concatenated primary key, references orders.order_id.'
;

COMMENT ON COLUMN OE.ORDER_ITEMS.LINE_ITEM_ID IS 'Part of concatenated primary key.'
;

COMMENT ON COLUMN OE.ORDER_ITEMS.PRODUCT_ID IS 'References product_information.product_id.'
;
CREATE UNIQUE INDEX OE.ORDER_ITEMS_UK ON OE.ORDER_ITEMS
    (
     ORDER_ID ASC ,
     PRODUCT_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.ITEM_ORDER_IX ON OE.ORDER_ITEMS
    (
     ORDER_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.ITEM_PRODUCT_IX ON OE.ORDER_ITEMS
    (
     PRODUCT_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE OE.ORDER_ITEMS
    ADD CONSTRAINT ORDER_ITEMS_PK PRIMARY KEY ( ORDER_ID, LINE_ITEM_ID ) ;


CREATE TABLE OE.PRODUCT_DESCRIPTIONS
    (
     PRODUCT_ID NUMBER (6)  NOT NULL ,
     LANGUAGE_ID VARCHAR2 (3 BYTE)  NOT NULL ,
     TRANSLATED_NAME NVARCHAR2 (50)
     CONSTRAINT TRANSLATED_NAME_NN NOT NULL ,
     TRANSLATED_DESCRIPTION NVARCHAR2 (2000)
     CONSTRAINT TRANSLATED_DESC_NN NOT NULL
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



COMMENT ON TABLE OE.PRODUCT_DESCRIPTIONS IS 'Non-industry-specific design, allows selection of NLS-setting-specific data
derived at runtime, for example using the products view.'
;

COMMENT ON COLUMN OE.PRODUCT_DESCRIPTIONS.PRODUCT_ID IS 'Primary key column.'
;

COMMENT ON COLUMN OE.PRODUCT_DESCRIPTIONS.LANGUAGE_ID IS 'Primary key column.'
;
CREATE UNIQUE INDEX OE.PRD_DESC_PK ON OE.PRODUCT_DESCRIPTIONS
    (
     PRODUCT_ID ASC ,
     LANGUAGE_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;
CREATE INDEX OE.PROD_NAME_IX ON OE.PRODUCT_DESCRIPTIONS
    (
     TRANSLATED_NAME ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE OE.PRODUCT_DESCRIPTIONS
    ADD CONSTRAINT PRODUCT_DESCRIPTIONS_PK PRIMARY KEY ( PRODUCT_ID, LANGUAGE_ID )
    USING INDEX OE.PRD_DESC_PK ;


CREATE TABLE OE.PRODUCT_INFORMATION
    (
     PRODUCT_ID NUMBER (6)  NOT NULL ,
     PRODUCT_NAME VARCHAR2 (50 BYTE) ,
     PRODUCT_DESCRIPTION VARCHAR2 (2000 BYTE) ,
     CATEGORY_ID NUMBER (2) ,
     WEIGHT_CLASS NUMBER (1) ,
     WARRANTY_PERIOD INTERVAL YEAR TO MONTH ,
     SUPPLIER_ID NUMBER (6) ,
     PRODUCT_STATUS VARCHAR2 (20 BYTE) ,
     LIST_PRICE NUMBER (8,2) ,
     MIN_PRICE NUMBER (8,2) ,
     CATALOG_URL VARCHAR2 (50 BYTE)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



ALTER TABLE OE.PRODUCT_INFORMATION
    ADD CONSTRAINT PRODUCT_STATUS_LOV
    CHECK ( PRODUCT_STATUS IN ('obsolete', 'orderable', 'planned', 'under development'))
;


COMMENT ON TABLE OE.PRODUCT_INFORMATION IS 'Non-industry-specific data in various categories.'
;

COMMENT ON COLUMN OE.PRODUCT_INFORMATION.PRODUCT_ID IS 'Primary key column.'
;

COMMENT ON COLUMN OE.PRODUCT_INFORMATION.PRODUCT_DESCRIPTION IS 'Primary language description corresponding to translated_description in
oe.product_descriptions, added to provide non-NLS text columns for OC views
to accss.'
;

COMMENT ON COLUMN OE.PRODUCT_INFORMATION.CATEGORY_ID IS 'Low cardinality column, can be used for bitmap index.
Schema SH uses it as foreign key'
;

COMMENT ON COLUMN OE.PRODUCT_INFORMATION.WEIGHT_CLASS IS 'Low cardinality column, can be used for bitmap index.'
;

COMMENT ON COLUMN OE.PRODUCT_INFORMATION.WARRANTY_PERIOD IS 'INTERVAL YEAER TO MONTH column, low cardinality, can be used for bitmap
index.'
;

COMMENT ON COLUMN OE.PRODUCT_INFORMATION.SUPPLIER_ID IS 'Offers possibility of extensions outside Common Schema.'
;

COMMENT ON COLUMN OE.PRODUCT_INFORMATION.PRODUCT_STATUS IS 'Check constraint. Appropriate for complex rules, such as "All products in
status PRODUCTION must have at least one inventory entry." Also appropriate
for a trigger auditing status change.'
;
CREATE INDEX OE.PROD_SUPPLIER_IX ON OE.PRODUCT_INFORMATION
    (
     SUPPLIER_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE OE.PRODUCT_INFORMATION
    ADD CONSTRAINT PRODUCT_INFORMATION_PK PRIMARY KEY ( PRODUCT_ID ) ;


CREATE TABLE OE.PROMOTIONS
    (
     PROMO_ID NUMBER (6)  NOT NULL ,
     PROMO_NAME VARCHAR2 (20 BYTE)
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;



ALTER TABLE OE.PROMOTIONS
    ADD CONSTRAINT PROMO_ID_PK PRIMARY KEY ( PROMO_ID ) ;


CREATE TABLE OE.PURCHASEORDER
    OF SYS.XMLTYPE
    XMLTYPE STORE AS OBJECT RELATIONAL
        XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
        ELEMENT "PurchaseOrder"
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE USERS
        LOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
;


CREATE UNIQUE INDEX OE.IX_LINEITEM_TABLE_MEMBERS ON OE.PURCHASEORDER
    (
     "XMLDATA"."LINEITEMS"."LINEITEM"
    )
    TABLESPACE USERS
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    LOGGING
;
CREATE UNIQUE INDEX OE.IX_ACTION_TABLE_MEMBERS ON OE.PURCHASEORDER
    (
     "XMLDATA"."ACTIONS"."ACTION"
    )
    TABLESPACE USERS
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    LOGGING
;


CREATE TABLE OE.WAREHOUSES
    (
     WAREHOUSE_ID NUMBER (3)  NOT NULL ,
     WAREHOUSE_SPEC XMLTYPE ,
     WAREHOUSE_NAME VARCHAR2 (35 BYTE) ,
     LOCATION_ID NUMBER (4) ,
     WH_GEO_LOCATION MDSYS.SDO_GEOMETRY
    )
        PCTFREE 10
        PCTUSED 40
        MAXTRANS 255
        TABLESPACE EXAMPLE
        NOLOGGING
        STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    XMLTYPE COLUMN WAREHOUSE_SPEC STORE AS CLOB
    (
        STORAGE (
            PCTINCREASE 0
            MINEXTENTS 1
            MAXEXTENTS UNLIMITED
            FREELISTS 1
            BUFFER_POOL DEFAULT
        )
        RETENTION
        ENABLE STORAGE IN ROW
        NOCACHE
    )
    COLUMN WH_GEO_LOCATION NOT SUBSTITUTABLE AT ALL LEVELS
;



COMMENT ON TABLE OE.WAREHOUSES IS 'Warehouse data unspecific to any industry.'
;

COMMENT ON COLUMN OE.WAREHOUSES.WAREHOUSE_ID IS 'Primary key column.'
;

COMMENT ON COLUMN OE.WAREHOUSES.WH_GEO_LOCATION IS 'Primary key column, references hr.locations.location_id.'
;
CREATE INDEX OE.WHS_LOCATION_IX ON OE.WAREHOUSES
    (
     LOCATION_ID ASC
    )
    TABLESPACE EXAMPLE
    PCTFREE 10
    MAXTRANS 255
    STORAGE (
        INITIAL 65536
        NEXT 1048576
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 2147483645
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT )
    NOLOGGING
;

ALTER TABLE OE.WAREHOUSES
    ADD CONSTRAINT WAREHOUSES_PK PRIMARY KEY ( WAREHOUSE_ID ) ;



ALTER TABLE OE.INVENTORIES
    ADD CONSTRAINT INVENTORIES_PRODUCT_ID_FK FOREIGN KEY
    (
     PRODUCT_ID
    )
    REFERENCES OE.PRODUCT_INFORMATION
    (
     PRODUCT_ID
    )
    NOT DEFERRABLE
;


ALTER TABLE OE.INVENTORIES
    ADD CONSTRAINT INVENTORIES_WAREHOUSES_FK FOREIGN KEY
    (
     WAREHOUSE_ID
    )
    REFERENCES OE.WAREHOUSES
    (
     WAREHOUSE_ID
    )
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE OE.ORDERS
    ADD CONSTRAINT ORDERS_CUSTOMER_ID_FK FOREIGN KEY
    (
     CUSTOMER_ID
    )
    REFERENCES OE.CUSTOMERS
    (
     CUSTOMER_ID
    )
    NOT DEFERRABLE
;


ALTER TABLE OE.ORDER_ITEMS
    ADD CONSTRAINT ORDER_ITEMS_ORDER_ID_FK FOREIGN KEY
    (
     ORDER_ID
    )
    REFERENCES OE.ORDERS
    (
     ORDER_ID
    )
    ON DELETE CASCADE
    NOT DEFERRABLE NOVALIDATE
;


ALTER TABLE OE.ORDER_ITEMS
    ADD CONSTRAINT ORDER_ITEMS_PRODUCT_ID_FK FOREIGN KEY
    (
     PRODUCT_ID
    )
    REFERENCES OE.PRODUCT_INFORMATION
    (
     PRODUCT_ID
    )
    NOT DEFERRABLE
;


ALTER TABLE OE.PRODUCT_DESCRIPTIONS
    ADD CONSTRAINT PD_PRODUCT_ID_FK FOREIGN KEY
    (
     PRODUCT_ID
    )
    REFERENCES OE.PRODUCT_INFORMATION
    (
     PRODUCT_ID
    )
    NOT DEFERRABLE
;

CREATE OR REPLACE VIEW OE.ACCOUNT_MANAGERS AS
SELECT          c.account_mgr_id                ACCT_MGR,
                cr.region_id                    REGION,
                c.cust_address.country_id       COUNTRY,
                c.cust_address.state_province   PROVINCE,
                count(*)                        NUM_CUSTOMERS
FROM            customers c, countries cr
WHERE           c.cust_address.country_id = cr.country_id
GROUP BY ROLLUP (c.account_mgr_id,
                 cr.region_id,
                 c.cust_address.country_id,
                 c.cust_address.state_province) ;



CREATE OR REPLACE VIEW OE.BOMBAY_INVENTORY AS
SELECT p.product_id
,      p.product_name
,      i.quantity_on_hand
FROM   inventories i
,      warehouses  w
,      products    p
WHERE  p.product_id = i.product_id
AND    i.warehouse_id = w.warehouse_id
AND    w.warehouse_name = 'Bombay' ;



CREATE OR REPLACE VIEW OE.CUSTOMERS_VIEW AS
SELECT
  c.customer_id,
  c.cust_first_name,
  c.cust_last_name,
  c.cust_address.street_address street_address,
  c.cust_address.postal_code postal_code,
  c.cust_address.city city,
  c.cust_address.state_province state_province,
  co.country_id,
  co.country_name,
  co.region_id,
  c.nls_language,
  c.nls_territory,
  c.credit_limit,
  c.cust_email,
  substr(get_phone_number_f(1,phone_numbers),1,25) Primary_Phone_number,
  substr(get_phone_number_f(2,phone_numbers),1,25) Phone_number_2,
  substr(get_phone_number_f(3,phone_numbers),1,25) Phone_number_3,
  substr(get_phone_number_f(4,phone_numbers),1,25) Phone_number_4,
  substr(get_phone_number_f(5,phone_numbers),1,25) Phone_number_5,
  c.account_mgr_id,
  c.cust_geo_location.sdo_gtype location_gtype,
  c.cust_geo_location.sdo_srid location_srid,
  c.cust_geo_location.sdo_point.x location_x,
  c.cust_geo_location.sdo_point.y location_y,
  c.cust_geo_location.sdo_point.z location_z
FROM
  countries co,
  customers c
WHERE
  c.cust_address.country_id = co.country_id(+) ;



CREATE OR REPLACE VIEW OE.OC_CUSTOMERS AS

 OF OE.CUSTOMER_TYP WITH OBJECT IDENTIFIER ( customer_id ) SELECT c.customer_id, c.cust_first_name, c.cust_last_name, c.cust_address,
           c.phone_numbers,c.nls_language,c.nls_territory,c.credit_limit,
           c.cust_email,
           CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                                    l.unit_price,l.quantity,
                                             MAKE_REF(oc_product_information,
                                                      l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                         FROM orders o
                         WHERE c.customer_id = o.customer_id)
                AS order_list_typ)
     FROM customers c ;



CREATE OR REPLACE VIEW OE.OC_INVENTORIES AS

 OF OE.INVENTORY_TYP WITH OBJECT IDENTIFIER ( product_id ) SELECT i.product_id,
           warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),
           i.quantity_on_hand
    FROM inventories i, warehouses w
    WHERE i.warehouse_id=w.warehouse_id ;



CREATE OR REPLACE VIEW OE.OC_ORDERS AS

 OF OE.ORDER_TYP WITH OBJECT IDENTIFIER ( order_id ) SELECT o.order_id, o.order_mode,MAKE_REF(oc_customers,o.customer_id),
        o.order_status,o.order_total,o.sales_rep_id,
       CAST(MULTISET(SELECT l.order_id,l.line_item_id,l.unit_price,l.quantity,
                       make_ref(oc_product_information,l.product_id)
                     FROM order_items l
                     WHERE o.order_id = l.order_id)
            AS order_item_list_typ)
    FROM orders o ;



CREATE OR REPLACE VIEW OE.OC_PRODUCT_INFORMATION AS

 OF OE.PRODUCT_INFORMATION_TYP WITH OBJECT IDENTIFIER ( product_id ) SELECT p.product_id, p.product_name, p.product_description, p.category_id,
           p.weight_class, p.warranty_period, p.supplier_id, p.product_status,
           p.list_price, p.min_price, p.catalog_url,
           CAST(MULTISET(SELECT i.product_id,i.warehouse,i.quantity_on_hand
                         FROM oc_inventories i
                         WHERE p.product_id=i.product_id)
                AS inventory_list_typ)
    FROM product_information p ;



CREATE OR REPLACE VIEW OE.ORDERS_VIEW AS
SELECT
  order_id,
  TO_DATE(TO_CHAR(order_date,'DD-MON-YY HH:MI:SS'),'DD-MON-YY HH:MI:SS') ORDER_DATE,
  order_mode,
  customer_id,
  order_status,
  order_total,
  sales_rep_id,
  promotion_id
FROM orders ;



CREATE OR REPLACE VIEW OE.PRODUCTS AS
SELECT i.product_id
,      d.language_id
,      CASE WHEN d.language_id IS NOT NULL
            THEN d.translated_name
            ELSE TRANSLATE(i.product_name USING NCHAR_CS)
       END    AS product_name
,      i.category_id
,      CASE WHEN d.language_id IS NOT NULL
            THEN d.translated_description
            ELSE TRANSLATE(i.product_description USING NCHAR_CS)
       END    AS product_description
,      i.weight_class
,      i.warranty_period
,      i.supplier_id
,      i.product_status
,      i.list_price
,      i.min_price
,      i.catalog_url
FROM   product_information  i
,      product_descriptions d
WHERE  d.product_id  (+) = i.product_id
AND    d.language_id (+) = sys_context('USERENV','LANG') ;



CREATE OR REPLACE VIEW OE.PRODUCT_PRICES AS
SELECT category_id
,      COUNT(*)        as "#_OF_PRODUCTS"
,      MIN(list_price) as low_price
,      MAX(list_price) as high_price
FROM   product_information
GROUP BY category_id ;



CREATE OR REPLACE VIEW OE.SYDNEY_INVENTORY AS
SELECT p.product_id
,      p.product_name
,      i.quantity_on_hand
FROM   inventories i
,      warehouses  w
,      products    p
WHERE  p.product_id = i.product_id
AND    i.warehouse_id = w.warehouse_id
AND    w.warehouse_name = 'Sydney' ;



CREATE OR REPLACE VIEW OE.TORONTO_INVENTORY AS
SELECT p.product_id
,      p.product_name
,      i.quantity_on_hand
FROM   inventories i
,      warehouses  w
,      products    p
WHERE  p.product_id = i.product_id
AND    i.warehouse_id = w.warehouse_id
AND    w.warehouse_name = 'Toronto' ;



CREATE OR REPLACE VIEW OE.OC_CORPORATE_CUSTOMERS AS

 OF OE.CORPORATE_CUSTOMER_TYP UNDER OE.OC_CUSTOMERS SELECT c.customer_id, c.cust_first_name, c.cust_last_name,
              c.cust_address, c.phone_numbers,c.nls_language,c.nls_territory,
              c.credit_limit, c.cust_email,
              CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                         l.unit_price,l.quantity,
                                         make_ref(oc_product_information,
                                                    l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                            FROM orders o
                            WHERE c.customer_id = o.customer_id)
              AS order_list_typ), c.account_mgr_id
     FROM customers c ;



CREATE SYNONYM OE.COUNTRIES
FOR HR.COUNTRIES
;
CREATE SYNONYM OE.DEPARTMENTS
FOR HR.DEPARTMENTS
;
CREATE SYNONYM OE.EMPLOYEES
FOR HR.EMPLOYEES
;
CREATE SYNONYM OE.JOBS
FOR HR.JOBS
;
CREATE SYNONYM OE.JOB_HISTORY
FOR HR.JOB_HISTORY
;
CREATE SYNONYM OE.LOCATIONS
FOR HR.LOCATIONS
;
CREATE OR REPLACE TRIGGER OE.INSERT_ORD_LINE
    BEFORE INSERT ON OE.ORDER_ITEMS
    FOR EACH ROW
DECLARE
    new_line number;
  BEGIN
    SELECT (NVL(MAX(line_item_id),0)+1) INTO new_line
      FROM order_items
      WHERE order_id = :new.order_id;
    :new.line_item_id := new_line;
  END;
/


CREATE OR REPLACE TRIGGER OE.ORDERS_ITEMS_TRG
    INSTEAD OF INSERT ON OE.OC_ORDERS
    FOR EACH ROW
DECLARE
    prod  product_information_typ;
BEGIN
    SELECT DEREF(:NEW.product_ref) INTO prod FROM DUAL;
    INSERT INTO order_items VALUES (prod.product_id, :NEW.order_id,
                                    :NEW.line_item_id, :NEW.unit_price,
                                    :NEW.quantity);
END;
/


CREATE OR REPLACE TRIGGER OE.ORDERS_TRG
    INSTEAD OF INSERT ON OE.OC_ORDERS
    FOR EACH ROW
BEGIN
   INSERT INTO ORDERS (order_id, order_mode, order_total,
                       sales_rep_id, order_status)
               VALUES (:NEW.order_id, :NEW.order_mode,
                       :NEW.order_total, :NEW.sales_rep_id,
                       :NEW.order_status);
END;
/


CREATE OR REPLACE TRIGGER OE.PURCHASEORDER$xd
    AFTER UPDATE OR DELETE ON OE.PURCHASEORDER
    FOR EACH ROW
BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('OE','PURCHASEORDER', :old.sys_nc_oid$, '2FAF5A664A60406C81560AD888D12779' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('OE','PURCHASEORDER', :old.sys_nc_oid$, '2FAF5A664A60406C81560AD888D12779', user ); END IF; END;
/



CREATE OR REPLACE FUNCTION OE.GET_PHONE_NUMBER_F
  (p_in INTEGER, p_phonelist phone_list_typ)
RETURN VARCHAR2 AS
  TYPE phone_list IS VARRAY(5) OF VARCHAR2(25);
  phone_out varchar2(25) := null;
  v_size INTEGER;
BEGIN
    IF p_phonelist.FIRST IS NULL OR
       p_in > (p_phonelist.LAST + 1) - p_phonelist.FIRST THEN
      RETURN phone_out;
    ELSE
      phone_out := p_phonelist(p_in);
      RETURN phone_out;
    END IF;
END;
/

























-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                            10
-- CREATE INDEX                            18
-- ALTER TABLE                             19
-- CREATE VIEW                             13
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          1
-- CREATE TRIGGER                           4
-- ALTER TRIGGER                            0
-- CREATE STRUCTURED TYPE                  20
-- CREATE COLLECTION TYPE                   8
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         2
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           6
-- CREATE TABLESPACE                        0
-- CREATE USER                              3
--
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
--
-- ERRORS                                   0
-- WARNINGS                                 0