/* 
|| CASTing and Table Function Examples - Listing 2
||
|| This script contains the DDL and DML statements necessary to create
|| new tables and modify existing tables in the HR schema of the Oracle
|| sample database for the demonstration of the CAST function and table
|| function features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle and 
|| should be carefully proofread before executing against any existing 
|| Oracle database to insure that no potential damage can occur.
||
*/

-----
-- Create and load new table in the HR schema to store Divisions
-----
DROP TABLE hr.divisions CASCADE CONSTRAINTS;
CREATE TABLE hr.divisions (
    division_id     NUMBER(5)       PRIMARY KEY,
    description     VARCHAR2(32)    NOT NULL
);

INSERT INTO hr.divisions (division_id, description)
VALUES (10000, 'Executive');
INSERT INTO hr.divisions (division_id, description)
VALUES (20000, 'Administrative');
INSERT INTO hr.divisions (division_id, description)
VALUES (30000, 'Construction');

COMMIT;

-----
-- Create and populate new DIVISION_ID column in the DEPARTMENTS table
-----
ALTER TABLE hr.departments ADD division_id NUMBER(5);
ALTER TABLE hr.departments 
    ADD CONSTRAINT department_division_fk
    FOREIGN KEY (division_id)
    REFERENCES hr.divisions(division_id);

-- Assign departments to Executive division
UPDATE hr.departments
   SET division_id = 10000
 WHERE department_id IN (10, 20, 40, 60, 70, 80, 90);

COMMIT;
 
-- Assign departments to Construction division 
UPDATE hr.departments
   SET division_id = 30000
 WHERE department_id IN (30, 50, 170, 180, 190, 200);

COMMIT;
 
-- Assign all other departments to Administrative division
UPDATE hr.departments
   SET division_id = 20000
 WHERE division_id IS NULL;

COMMIT;

-----
-- Create and load a new table in the HR schema to store Cost Centers
-----
DROP TABLE hr.cost_centers CASCADE CONSTRAINTS;
CREATE TABLE hr.cost_centers (
    cost_ctr_id     NUMBER(5)       PRIMARY KEY,
    description     VARCHAR2(32)    NOT NULL,
    selectable      CHAR(1)         NOT NULL
);

ALTER TABLE hr.cost_centers 
    ADD CONSTRAINT cc_selectable_ck 
    CHECK (selectable IN ('Y','N'));

INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (10000, 'Sales and Management', 'N');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (20000, 'Administrative', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (30000, 'Homebuilding', 'Y');

INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (11000, 'Owners', 'N');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (12000, 'Sales and Marketing', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (12100, 'Outside Sales', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (12200, 'Sales Support', 'Y');

INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (21000, 'Office Supplies', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (22000, 'Human Resources', 'N');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (23000, 'Architectural', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (23100, 'Blueprinting', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (23200, 'Planning', 'Y');

INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (31000, 'Exterior Construction', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (31100, 'Wall and Floor Setting', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (31200, 'Concrete and Foundation', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (31300, 'Rough Landscaping', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (31400, 'Finish Landscaping', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (32000, 'Carpentry - General', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (32100, 'Rough Carpentry', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (32200, 'Finish Carpentry', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (33000, 'Plumbing - General', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (33100, 'Rough-In Plumbing', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (33200, 'Finish Plumbing', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (34000, 'Physical Plant', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (34100, 'HVAC', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (35000, 'Interior Construction', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (35100, 'Wallboarding and Plastering', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (35200, 'Painting', 'Y');
INSERT INTO hr.cost_centers (cost_ctr_id, description, selectable)
VALUES (35300, 'Flooring', 'Y');

COMMIT;

-----
-- Create table for Cost Center Assignments
-----
DROP TABLE hr.cost_center_assignments CASCADE CONSTRAINTS;
CREATE TABLE hr.cost_center_assignments (
    entity_id       NUMBER(5)       NOT NULL,
    entity_type     CHAR(1)         NOT NULL,
    seq_nbr         NUMBER(5)       NOT NULL,
    cost_ctr_id     NUMBER(5)       NOT NULL
);

ALTER TABLE hr.cost_center_assignments
    ADD CONSTRAINT cost_center_assignments_pk
    PRIMARY KEY (entity_id, entity_type, seq_nbr);

ALTER TABLE hr.cost_center_assignments
    ADD CONSTRAINT cost_center_assignments_fk
    FOREIGN KEY (cost_ctr_id)
    REFERENCES hr.cost_centers(cost_ctr_id);

-- Load Division-level cost center assignments
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (10000, 'V', 1, 10000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (20000, 'V', 1, 20000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30000, 'V', 1, 30000);
COMMIT;

-- Load Department-level cost center assignments
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30, 'D', 1, 21000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30, 'D', 2, 23100);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30, 'D', 3, 23200);

INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 1, 31000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 2, 33000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 3, 33000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 4, 34000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 5, 35000);

INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 1, 12000);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 2, 12100);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 3, 12200);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 4, 21000);

INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (110, 'D', 1, 21000);

-- Load Employee-level cost center assignments

INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (114, 'E', 1, 35100);

INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (120, 'E', 1, 35100);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (120, 'E', 2, 35200);
INSERT INTO hr.cost_center_assignments (entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (120, 'E', 3, 35300);

COMMIT;

-----
-- Sample queries
-----
      
-- Show all Cost Center entries
SELECT * 
  FROM HR.cost_centers 
 ORDER BY cost_ctr_id

-- Show all Cost Center Assignment entries
SELECT * 
  FROM HR.cost_center_assignments 

-- Show all Division / Department / Employee hierarchy entries and details
SELECT 
    D.division_id,
    V.description,
    D.department_name,
    E.department_id,
    E.employee_id,
    E.last_name
  FROM 
    divisions V,
    departments D,
    employees E
 WHERE E.department_id = D.department_id
   AND d.division_id = V.division_id
  ORDER BY D.Division_Id, e.department_id, e.employee_id