-- Stack Overflow Test Plan for:
-- http://stackoverflow.com/questions/24276153/oracle-pl-sql-with-automatic-values
-- (prepared by Richard Pascual)

DROP TABLE "PROCESSED_PRODUCT_WEIGHT";

CREATE table "PROCESSED_PRODUCT_WEIGHT" (
    "PRODUCT_NAME"  VARCHAR2(40) NOT NULL,
    "PROCESS1"      NUMBER,
    "PROCESS2"      NUMBER,
    "WEIGHT"        NUMBER,
    "TOTAL_WEIGHT"  NUMBER GENERATED ALWAYS AS ((PROCESS1 + PROCESS2)*WEIGHT) VIRTUAL,
    "RECORDED_DATE" DATE,
    constraint  "PROCESSED_PRODUCT_WEIGHT_PK" primary key ("PRODUCT_NAME", "RECORDED_DATE")
)
/

-- Test Case "Initialize"
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM1', 0, 0, 10, '01/01/2014')
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM2', 1, 1, 10, '01/01/2014')
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM3', 1, 1, 15, '01/01/2014')

-- Test Case ONE
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM1', 2, 5, 10, '02/14/2014')
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM2', 3, 1, 10, '02/14/2014')

-- Test Case TWO
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM1', 2, 5, 55, '03/14/2014')

-- Test Case THREE
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM3', 2, 1, 20, '04/01/2014')

/