New Text Document.txt

create or replace TRIGGER INVENTORY_ASK
BEFORE INSERT ON INVENTORY
FOR EACH ROW
DECLARE
  employee varchar(20);
  cond varchar(100);
  prodtype varchar(100);
BEGIN
  cond := :NEW.PRODUCT_CONDITION;
  SELECT DISTINCT PRODUCT_TYPE INTO prodtype FROM PRODUCT_INFO JOIN BARCODE_TABLE
  USING(MODEL_NO) WHERE BARCODE_TABLE.BARCODE = :NEW.BARCODE;
    IF UPPER(cond) = 'BAD' THEN
      SELECT EMPLOYEE_ID INTO employee FROM DISSEMBLER JOIN EMPLOYEE USING(EMPLOYEE_ID) WHERE UPPER(EMPLOYEE.STATUS) = 'FREE'
      AND DISSEMBLER.PRODUCT_TYPE = prodtype;
      UPDATE EMPLOYEE SET STATUS = 'busy' WHERE EMPLOYEE_ID = employee;
      :NEW.CHECK_OUT_TO := employee;
    END IF;
END;


CREATE SEQUENCE  DUMP_SEQ  MINVALUE 1000000 MAXVALUE 999999999 INCREMENT BY 10 START WITH 1001000 CACHE 100 NOORDER ;


ALTER TABLE DUMP ADD CURRENT_QUANTITY NUMBER DEFAULT 0;
CREATE OR REPLACE VIEW DUMPINGVIEW(LOC, QUANTITY, CURR, MATERIALS, SAFETY)
AS
(SELECT DUMP.LOCATION,DUMP.AREA_QUANTITY,DUMP.CURRENT_QUANTITY,DUMPED_MATERIALS.MATERIAL,DUMP.SAFETY_LEVEL FROM DUMP JOIN
DUMPED_MATERIALS USING(STATION_ID));


CREATE OR REPLACE VIEW MAKERAWMATERIAL ("PRODUCT_CONDITION", "BARCODE" , "RECYCLER" , "ID") AS
  (SELECT INVENTORY.PRODUCT_CONDITION,INVENTORY.BARCODE,PROCESSOR.PROCESSOR_ID,INVENTORY.INVENTORY_ID
  FROM INVENTORY JOIN DISSEMBLER ON(DISSEMBLER.EMPLOYEE_ID = INVENTORY.CHECK_OUT_TO)
JOIN RECYCLING ON(DISSEMBLER.EMPLOYEE_ID =RECYCLING.PROCESSOR_ID) JOIN PROCESSOR ON(PROCESSOR.PROCESSOR_ID =RECYCLING.PROCESSOR_ID));

CREATE OR REPLACE VIEW PRODUCTNAME ("NAME","TYPE","BARCODE") AS
(SELECT PRODUCT_NAME,PRODUCT_TYPE,BARCODE_TABLE.BARCODE FROM PRODUCT_INFO JOIN BARCODE_TABLE USING(MODEL_NO));

ALTER TABLE EXTRACTION ADD INVENTORY_ID VARCHAR(20);

ALTER TABLE EXTRACTION ADD CONSTRAINT
EXTRACTION_INVENTORY_ID_FK FOREIGN KEY(INVENTORY_ID) REFERENCES INVENTORY(INVENTORY_ID)
ON DELETE CASCADE;

CREATE OR REPLACE TRIGGER INVENTORY_ASK
BEFORE INSERT ON INVENTORY
FOR EACH ROW
DECLARE
  employee varchar(20);
  cond varchar(100);
  prodtype varchar(100);
BEGIN
  cond := :NEW.PRODUCT_CONDITION;
  SELECT DISTINCT PRODUCT_TYPE INTO prodtype FROM PRODUCT_INFO JOIN BARCODE_TABLE
  USING(MODEL_NO) WHERE BARCODE_TABLE.BARCODE = :NEW.BARCODE;
    IF UPPER(cond) = 'BAD' THEN
      SELECT EMPLOYEE_ID INTO employee FROM DISSEMBLER JOIN EMPLOYEE USING(EMPLOYEE_ID) WHERE UPPER(EMPLOYEE.STATUS) = 'FREE'
      AND UPPER(DISSEMBLER.PRODUCT_TYPE) = UPPER(prodtype);
      UPDATE EMPLOYEE SET STATUS = 'busy' WHERE EMPLOYEE_ID = employee;
      :NEW.CHECK_OUT_TO := employee;
    END IF;
END;

CREATE OR REPLACE VIEW RECYCLABLE ("PRODUCT_NAME", "PRODUCT_PRICE", "PRODUCT_TYPE", "PRODUCT_CONDITION", "INVENTORY_ID") AS
(SELECT PRODUCT_INFO.PRODUCT_NAME,PRODUCT_INFO.PRODUCT_PRICE,
PRODUCT_INFO.PRODUCT_TYPE,INVENTORY.PRODUCT_CONDITION,INVENTORY.INVENTORY_ID FROM PRODUCT_INFO JOIN
BARCODE_TABLE ON(BARCODE_TABLE.MODEL_NO = PRODUCT_INFO.MODEL_NO) JOIN INVENTORY
ON(INVENTORY.BARCODE = BARCODE_TABLE.BARCODE) WHERE INVENTORY.CHECK_OUT_TO = NULL);

CCREATE OR REPLACE FORCE EDITIONABLE VIEW "C##RIDAY"."PRODUCTS" ("PRODUCT_NAME", "PRODUCT_PRICE", "PRODUCT_TYPE", "PRODUCT_CONDITION", "INVENTORY_ID", "CHECK_OUT_TO") AS
(SELECT PRODUCT_INFO.PRODUCT_NAME,PRODUCT_INFO.PRODUCT_PRICE,
PRODUCT_INFO.PRODUCT_TYPE,INVENTORY.PRODUCT_CONDITION,INVENTORY.INVENTORY_ID,INVENTORY.CHECK_OUT_TO FROM PRODUCT_INFO JOIN
BARCODE_TABLE ON(BARCODE_TABLE.MODEL_NO = PRODUCT_INFO.MODEL_NO) JOIN INVENTORY
ON(INVENTORY.BARCODE = BARCODE_TABLE.BARCODE) WHERE INVENTORY.INVENTORY_ID NOT IN(SELECT INVENTORY_ID FROM MAKES));