-- ABS-70242/ ABS-71183

CREATE TABLE SEARCHCONDITION
AS (SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, TO_LOB(SEARCH_CONDITION) AS SEARCHCONDITION FROM
  (SELECT UTC.TABLE_NAME,
         UTC.COLUMN_NAME,
         UCC.CONSTRAINT_NAME,
         UC.SEARCH_CONDITION
   FROM USER_TAB_COLUMNS  UTC
          LEFT OUTER JOIN USER_CONS_COLUMNS UCC
               ON (UTC.TABLE_NAME  = UCC.TABLE_NAME AND UTC.COLUMN_NAME = UCC.COLUMN_NAME)
          LEFT OUTER JOIN USER_CONSTRAINTS UC
               ON (UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME)
    WHERE  UTC.TABLE_NAME = 'STATUSQUOHEADER'
    AND    UTC.COLUMN_NAME = 'SUBTYPE'
    AND   (UC.CONSTRAINT_TYPE = 'C' OR UC.CONSTRAINT_TYPE IS NULL) ORDER  BY UTC.COLUMN_ID));
           
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT AS
CONSTRAINTNAME VARCHAR2(100);
STMT VARCHAR2(100);
BEGIN
  SELECT T.CONSTRAINT_NAME INTO CONSTRAINTNAME FROM SEARCHCONDITION T WHERE TO_CHAR(T.SEARCHCONDITION) = 'SUBTYPE IN (''0'', ''1'', ''2 '')';
  STMT := 'ALTER TABLE STATUSQUOHEADER DROP CONSTRAINT '|| CONSTRAINTNAME;
  
  EXECUTE IMMEDIATE(STMT);
  EXCEPTION WHEN OTHERS THEN NULL;
END DROPCONSTRAINT;
/

EXECUTE DROPCONSTRAINT;

DROP PROCEDURE DROPCONSTRAINT;

DROP TABLE SEARCHCONDITION;

ALTER TABLE STATUSQUOHEADER ADD CONSTRAINT SUBTYPE_VALID1_1 CHECK (SUBTYPE IN (0, 1, 2));