/*
|| Script:  APSetup.sql
|| Version: 11.1.0.6.0
|| Object:  Creates sequences, tables, indexes, constraints, 
||          and views to illustrate a prototype Accounts Payable
||          (AP) system.
|| Author:  Jim Czuprynski (Fujitsu Consulting)
*/

/*
|| Create AP schema
*/

DROP ROLE ap_read_only;
CREATE ROLE ap_read_only;

DROP ROLE ap_secured;
CREATE ROLE ap_secured;

DROP USER ap CASCADE;
CREATE USER ap
    IDENTIFIED BY ap
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    PROFILE DEFAULT
    QUOTA UNLIMITED ON example 
;

GRANT CONNECT, RESOURCE TO ap;
GRANT CREATE PROCEDURE TO ap;
GRANT CREATE PUBLIC SYNONYM TO ap;
GRANT CREATE SEQUENCE TO ap;
GRANT CREATE SESSION TO ap;
GRANT CREATE SYNONYM TO ap;
GRANT CREATE TABLE TO ap;
GRANT DROP PUBLIC SYNONYM TO ap;
GRANT EXECUTE ANY PROCEDURE TO ap;

GRANT ap_read_only TO ap;
GRANT ap_read_only TO ap_secured; 

DROP TABLE ap.invoice_items CASCADE CONSTRAINTS PURGE;
DROP TABLE ap.invoices CASCADE CONSTRAINTS PURGE;
DROP TABLE ap.vendors CASCADE CONSTRAINTS PURGE;

/*
|| Create AP.VENDORS and related objects
*/

-- Create sequence 
DROP SEQUENCE ap.seq_vendor_id;
CREATE SEQUENCE ap.seq_vendor_id
    MINVALUE 1
    MAXVALUE 99999999999999999
    START WITH 100
    INCREMENT BY 1
    NOORDER
    CACHE 20;

GRANT SELECT ON ap.seq_vendor_id TO admin;

-- Create table
CREATE TABLE ap.vendors (
     vendor_id       NUMBER         NOT NULL
    ,active_ind      CHAR(1)        DEFAULT 'Y' NOT NULL
    ,name            VARCHAR2(128)  NOT NULL
    ,address_line_1  VARCHAR2(40)   DEFAULT 'UNDEFINED' NOT NULL
    ,address_line_2  VARCHAR2(40)   
    ,address_line_3  VARCHAR2(40)   
    ,city            VARCHAR2(40)   NOT NULL
    ,state           CHAR(2)
    ,country         VARCHAR2(30)   NOT NULL
    ,credit_card     VARCHAR2(16)   NOT NULL
    ,credit_limit    NUMBER         NOT NULL
)
    TABLESPACE example;

-- Create indexes and constraints
ALTER TABLE ap.vendors
    ADD CONSTRAINT vendors_pk 
    PRIMARY KEY (vendor_id)
    USING INDEX ( 
        CREATE INDEX ap.vendors_pk_idx
            ON ap.vendors (vendor_id)
            TABLESPACE example
        );

ALTER TABLE ap.vendors
    ADD CONSTRAINT vendors_active_ck 
    CHECK (active_ind IN ('Y','N'));

GRANT SELECT         ON ap.vendors TO ap_read_only;
GRANT INSERT, UPDATE ON ap.vendors TO ap_secured;

/*
|| Create AP.INVOICES and related objects
*/

GRANT REFERENCES ON oe.customers            TO ap;
GRANT SELECT     ON oe.customers            TO ap;
GRANT REFERENCES ON oe.product_information  TO ap;
GRANT SELECT     ON oe.product_information  TO ap;

DROP SEQUENCE ap.seq_invoice_id;
CREATE SEQUENCE ap.seq_invoice_id
    MINVALUE 1
    MAXVALUE 99999999999999999
    START WITH 100
    INCREMENT BY 1
    NOORDER
    CACHE 20;

GRANT SELECT ON ap.seq_invoice_id TO admin;

CREATE table ap.invoices (
     invoice_id     NUMBER          NOT NULL
    ,active_ind     CHAR(1)         DEFAULT 'Y' NOT NULL
    ,invoice_type   CHAR(1)         DEFAULT 'C' NOT NULL
    ,vendor_id      NUMBER          NOT NULL
    ,customer_id    NUMBER          NOT NULL
    ,balance_due    NUMBER(15,2)    DEFAULT 0
    ,taxable_amt    NUMBER(15,2)    DEFAULT 0
)
    TABLESPACE example;

ALTER TABLE ap.invoices
    ADD CONSTRAINT invoice_active_ck 
    CHECK (active_ind IN ('Y','N'));

ALTER TABLE ap.invoices
    ADD CONSTRAINT invoice_type_ck 
    CHECK (invoice_type IN ('C','D'));

ALTER TABLE ap.invoices
    ADD CONSTRAINT invoices_pk 
    PRIMARY KEY (invoice_id)
    USING INDEX ( 
        CREATE INDEX ap.invoices_pk_idx
            ON ap.invoices (invoice_id)
            TABLESPACE example
        );

ALTER TABLE ap.invoices
    ADD CONSTRAINT invoices_vendor_fk 
    FOREIGN KEY (vendor_id)
    REFERENCES ap.vendors (vendor_id);

ALTER TABLE ap.invoices
    ADD CONSTRAINT invoices_customer_fk 
    FOREIGN KEY (customer_id)
    REFERENCES oe.customers (customer_id);

CREATE INDEX ap.invoices_cust_idx
    ON ap.invoices (customer_id)
    TABLESPACE example;

GRANT SELECT         ON ap.invoices TO ap_read_only;
GRANT INSERT, UPDATE ON ap.invoices TO ap_secured;

/*
|| Create AP.INVOICE_ITEMS and related objects
*/

DROP SEQUENCE ap.seq_invoice_item_id;
CREATE SEQUENCE ap.seq_invoice_item_id
    MINVALUE 1
    MAXVALUE 99999999999999999
    START WITH 100
    INCREMENT BY 1
    NOORDER
    CACHE 20;

GRANT SELECT ON ap.seq_invoice_item_id TO admin;

CREATE table ap.invoice_items (
     invoice_id     NUMBER          NOT NULL
    ,line_item_nbr  NUMBER          NOT NULL
    ,active_ind     CHAR(1)         DEFAULT 'Y' NOT NULL
    ,product_id     NUMBER(9)       NOT NULL
    ,qty            NUMBER(9)       NOT NULL
    ,extended_amt   NUMBER(15,2)    DEFAULT 0
    ,taxable_ind    CHAR(1)         DEFAULT 'Y' NOT NULL
)
    TABLESPACE example;

ALTER TABLE ap.invoice_items
    ADD CONSTRAINT invoice_item_active_ck 
    CHECK (active_ind IN ('Y','N'));

ALTER TABLE ap.invoice_items
    ADD CONSTRAINT invoice_items_pk 
    PRIMARY KEY (invoice_id, line_item_nbr)
    USING INDEX ( 
        CREATE INDEX ap.invoice_items_pk_idx
            ON ap.invoice_items (invoice_id, line_item_nbr)
            TABLESPACE example
        );

ALTER TABLE ap.invoice_items
    ADD CONSTRAINT invoice_items_invoice_fk 
    FOREIGN KEY (invoice_id)
    REFERENCES ap.invoices (invoice_id);

ALTER TABLE ap.invoice_items
    ADD CONSTRAINT invoice_items_product_fk 
    FOREIGN KEY (product_id)
    REFERENCES oe.product_information (product_id);

CREATE INDEX ap.invoice_items_prod_idx
    ON ap.invoice_items (product_id)
    TABLESPACE example;

GRANT SELECT         ON ap.invoice_items TO ap_read_only;
GRANT INSERT, UPDATE ON ap.invoice_items TO ap_secured;

/*
|| Create reporting view for read-only reporting against
|| AP prototype system
*/

CREATE OR REPLACE VIEW ap.rv_invoice_details (
     vendor_name
    ,vendor_credit_card
    ,vendor_credit_limit
    ,customer_id
    ,customer_fullname   
    ,invoice_id
    ,invoice_status
    ,line_id
    ,taxable_ind
    ,product_id
    ,qty
    ,list_price
	,min_price
    ,extended_amt
)
AS
SELECT 
     V.name 
    ,V.credit_card
    ,V.credit_limit
    ,I.customer_id
    ,C.cust_last_name || ', ' || C.cust_first_name
    ,I.invoice_id
    ,I.active_ind
    ,ID.line_item_nbr
    ,ID.taxable_ind
    ,ID.product_id
    ,ID.qty
    ,P.list_price
	,P.min_price
    ,ID.extended_amt
  FROM 
     ap.vendors V
    ,ap.invoices I
    ,ap.invoice_items ID
    ,oe.customers C
    ,oe.product_information P
 WHERE ID.invoice_id = I.invoice_id
   AND I.vendor_id = V.vendor_id
   AND I.customer_id = C.customer_id
   AND ID.product_id = P.product_id
 WITH READ ONLY
;

GRANT SELECT ON ap.rv_invoice_details TO ap_read_only;

-----
-- Grant AP role-level privileges to HR, OE, and SH accounts
-----
GRANT ap_read_only  TO hr;
GRANT ap_secured    TO hr;
GRANT ap_read_only  TO oe;
GRANT ap_secured    TO oe;
GRANT ap_read_only  TO sh;
GRANT ap_secured    TO sh;