DROP TABLE MDHE.ESTABLISHMENT_LICENSE CASCADE CONSTRAINTS;

CREATE TABLE MDHE.ESTABLISHMENT_LICENSE
(
  ESTABLISHMENT_ID          NUMBER              NOT NULL,
  APPROVED_BY               NUMBER,
  NOTES                     VARCHAR2(500 BYTE),
  LICENSE_TYPE_CODE         VARCHAR2(30 BYTE),
  ESTABLISHMENT_LICENSE_ID  NUMBER              NOT NULL,
  BEGIN_DATE                DATE,
  EXPIRATION_DATE           DATE,
  STATUS_CODE               VARCHAR2(30 BYTE)   DEFAULT 'VALID'               NOT NULL,
  IS_CONDITIONAL            CHAR(1 BYTE)        DEFAULT 'N'                   NOT NULL,
  LICENSE_FEE               NUMBER              DEFAULT 0                     NOT NULL,
  CONDITIONAL_REASON_ID     NUMBER(4),
  CONDITIONAL_DAYS_ID       NUMBER(4)
)
TABLESPACE DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

COMMENT ON TABLE MDHE.ESTABLISHMENT_LICENSE IS 'This table contains all the liceses that an establishment has had.
It will generate a history OF licenses AS they EXPIRE AND get renew.';

COMMENT ON COLUMN MDHE.ESTABLISHMENT_LICENSE.STATUS_CODE IS 'To determine the licese status (VALID, CANCEL, ON_HOLD, Etc)
NOTE: No ACTIVE status IS necesary, the Expiration DATE will determine IF license IS active OR NOT.';

COMMENT ON COLUMN MDHE.ESTABLISHMENT_LICENSE.IS_CONDITIONAL IS 'IF "N" then the license is a full license, other wise is a conditional (not to be renewed) license';


CREATE INDEX MDHE.ESTABLISHMENT_LICENSE_ID2 ON MDHE.ESTABLISHMENT_LICENSE
(LICENSE_TYPE_CODE)
LOGGING
TABLESPACE DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX MDHE.ESTABLISHMENT_LICENSE_IX1 ON MDHE.ESTABLISHMENT_LICENSE
(ESTABLISHMENT_ID)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE UNIQUE INDEX MDHE.PK_ESTABLISHMENT_LICENSE ON MDHE.ESTABLISHMENT_LICENSE
(ESTABLISHMENT_LICENSE_ID)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE OR REPLACE TRIGGER MDHE.SET_LICENSE_STATUS
BEFORE UPDATE OR INSERT ON MDHE.ESTABLISHMENT_LICENSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN


  /* first check to see if the exp date has changed, if it has and if the status is valid then check to see if its a valid end date */
  IF ((:NEW.STATUS_CODE = 'VALID') AND (TRUNC(:NEW.Expiration_date) < TRUNC(SYSDATE))) THEN
    -- IF  LicenseEndMonthDayOK(:new.expiration_date, :new.license_type_code) = 0 THEN
   -- IF LicenseEndMonthDayOK(to_date('07/28/2009','mm/dd/yyyy'), 32) = 0 THEN

             RAISE_APPLICATION_ERROR (-20999,'Invalid Expiration Date');
   --  END IF;
  END IF;
  IF :OLD.license_type_code IN  ('08','17','32','39','51','59','81')
     AND :OLD.Expiration_date < TRUNC(SYSDATE)
     AND :OLD.STATUS_CODE = 'VALID'
     AND :OLD.STATUS_CODE = :NEW.STATUS_CODE THEN
         :NEW.STATUS_CODE := 'CANCEL';
  END IF;
  IF :OLD.license_type_code NOT IN  ('08','17','32','39','51','59','81')
     AND :OLD.Expiration_date < TRUNC(SYSDATE)
     AND :OLD.STATUS_CODE = 'VALID'
     AND :OLD.STATUS_CODE = :NEW.STATUS_CODE THEN
       :NEW.STATUS_CODE := 'EXPIRED';
  END IF;

END;
/


ALTER TABLE MDHE.ESTABLISHMENT_LICENSE ADD (
  CONSTRAINT PK_ESTABLISHMENT_LICENSE
  PRIMARY KEY
  (ESTABLISHMENT_LICENSE_ID)
  USING INDEX MDHE.PK_ESTABLISHMENT_LICENSE
  ENABLE VALIDATE);

ALTER TABLE MDHE.ESTABLISHMENT_LICENSE ADD (
  CONSTRAINT FK_LICENCE_ESTABLISHMENT 
  FOREIGN KEY (ESTABLISHMENT_ID) 
  REFERENCES MDHE.ESTABLISHMENT (ESTABLISHMENT_ID)
  ENABLE VALIDATE
,  CONSTRAINT FK_LICENSE_TYPE 
  FOREIGN KEY (LICENSE_TYPE_CODE) 
  REFERENCES MDHE.LICENSE_TYPE (LICENSE_TYPE_CODE)
  DISABLE NOVALIDATE);

SQL Monitoring Report

SQL Text
------------------------------
select/*+ GATHER_PLAN_STATISTICS MONITOR */ e.establishment_id, el.establishment_id from establishment e full join establishment_license el on e.establishment_id = el.establishment_id

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)                                    
 Instance ID         :  3                                                  
 Session             :  MDHE (113:20617)                                   
 SQL ID              :  5mh955qrd56hb                                      
 SQL Execution ID    :  50331651                                           
 Execution Started   :  07/23/2019 11:52:04                                
 First Refresh Time  :  07/23/2019 11:52:04                                
 Last Refresh Time   :  07/23/2019 11:52:05                                
 Duration            :  1s                                                 
 Module/Action       :  TOAD Freeware 13.1.0.78/34909424,64289200,52344192 
 Service             :  mdhetst_dg                                         
 Program             :  Toad.exe                                           
 Fetch Calls         :  224                                                

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.21 |    0.07 |     0.14 |   224 |    453 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=975423473)
===================================================================================================================================================================
| Id |            Operation             |           Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                  |                           | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
===================================================================================================================================================================
|  0 | SELECT STATEMENT                 |                           |         |      |         2 |     +0 |     1 |     178K |     . |          |                 |
|  1 |   VIEW                           | VW_FOJ_0                  |    178K |   87 |         2 |     +0 |     1 |     178K |     . |          |                 |
|  2 |    HASH JOIN FULL OUTER          |                           |    178K |   87 |         2 |     +0 |     1 |     178K |   7MB |          |                 |
|  3 |     INDEX STORAGE FAST FULL SCAN | PK_ESTABLISHMENT          |   28057 |   13 |         1 |     +0 |     1 |    28057 |     . |          |                 |
|  4 |     INDEX STORAGE FAST FULL SCAN | ESTABLISHMENT_LICENSE_IX1 |    177K |   74 |         2 |     +0 |     1 |     177K |     . |          |                 |
===================================================================================================================================================================

BANNER,CON_ID
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production,0
PL/SQL Release 12.2.0.1.0 - Production,0
CORE	12.2.0.1.0	Production,0
TNS for Linux: Version 12.2.0.1.0 - Production,0
NLSRTL Version 12.2.0.1.0 - Production,0