-- ABS-72555/ ABS-73241

-- CUSTOMER table --
CREATE TABLE SEARCHCONDITION_CUST
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 = 'CUSTOMER'
    AND    UTC.COLUMN_NAME = 'CHARGESERVICECOSTS'
    AND    UC.CONSTRAINT_TYPE = 'C' ORDER  BY UTC.COLUMN_ID));
    
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_CUST AS
STMT VARCHAR2(100);
BEGIN
  FOR X IN (SELECT T.CONSTRAINT_NAME FROM SEARCHCONDITION_CUST T WHERE TO_CHAR(T.SEARCHCONDITION) = 'CHARGESERVICECOSTS IN (''Y'', ''N'')') 
    LOOP
      BEGIN
      STMT := 'ALTER TABLE CUSTOMER DROP CONSTRAINT '|| X.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE(STMT);
      EXCEPTION WHEN OTHERS THEN NULL;
      END;
    END LOOP;
END DROPCONSTRAINT_CUST;
/

EXECUTE DROPCONSTRAINT_CUST;

DROP PROCEDURE DROPCONSTRAINT_CUST;

DROP TABLE SEARCHCONDITION_CUST;

ALTER TABLE CUSTOMER ADD CONSTRAINT CHARGESERVICECOSTS_VALID1 CHECK (CHARGESERVICECOSTS IN ('Y', 'N'));

-- FOLDINGPROGRAMDEFINITION table --
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_FP AS
  STMT VARCHAR2(100);
BEGIN
  FOR X IN (SELECT UTC.TABLE_NAME, UTC.COLUMN_NAME, UCC.CONSTRAINT_NAME
              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 = 'FOLDINGPROGRAMDEFINITION'
               AND UC.CONSTRAINT_TYPE = 'R'
             ORDER BY UTC.COLUMN_ID) LOOP
    BEGIN
      STMT := 'ALTER TABLE FOLDINGPROGRAMDEFINITION DROP CONSTRAINT ' ||
              X.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE (STMT);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END DROPCONSTRAINT_FP;
/

EXECUTE DROPCONSTRAINT_FP;

DROP PROCEDURE DROPCONSTRAINT_FP;

ALTER TABLE FOLDINGPROGRAMDEFINITION
  ADD CONSTRAINT FOLDINGPROGRAMDEFINITION_PFM FOREIGN KEY (PRODUCT_ID, FINISHINGMETHOD_ID)
  REFERENCES PRODUCTFINISHINGMETHOD (PRODUCT_ID, FINISHINGMETHOD_ID) ON DELETE CASCADE;
  
ALTER TABLE FOLDINGPROGRAMDEFINITION
  ADD CONSTRAINT FOLDINGPROGRAMDEFINITION_P FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

-- SORTINGDEFINITION table --
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_SD AS
  STMT VARCHAR2(100);
BEGIN
  FOR X IN (SELECT UTC.TABLE_NAME, UTC.COLUMN_NAME, UCC.CONSTRAINT_NAME
              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 = 'SORTINGDEFINITION'
               AND UC.CONSTRAINT_TYPE = 'R'
             ORDER BY UTC.COLUMN_ID) LOOP
    BEGIN
      STMT := 'ALTER TABLE SORTINGDEFINITION DROP CONSTRAINT ' ||
              X.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE (STMT);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END DROPCONSTRAINT_SD;
/

EXECUTE DROPCONSTRAINT_SD;

DROP PROCEDURE DROPCONSTRAINT_SD;

ALTER TABLE SORTINGDEFINITION
  ADD CONSTRAINT SORTINGDEFINITION_CUST FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE SORTINGDEFINITION
  ADD CONSTRAINT SORTINGDEFINITION_P FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

-- WCPCSPECIALCHARGE table --
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_WCPCSC AS
  STMT VARCHAR2(100);
BEGIN
  FOR X IN (SELECT UTC.TABLE_NAME, UTC.COLUMN_NAME, UCC.CONSTRAINT_NAME
              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 = 'WCPCSPECIALCHARGE'
               AND UC.CONSTRAINT_TYPE = 'R'
             ORDER BY UTC.COLUMN_ID) LOOP
    BEGIN
      STMT := 'ALTER TABLE WCPCSPECIALCHARGE DROP CONSTRAINT ' ||
              X.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE (STMT);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END DROPCONSTRAINT_WCPCSC;
/

EXECUTE DROPCONSTRAINT_WCPCSC;

DROP PROCEDURE DROPCONSTRAINT_WCPCSC;

ALTER TABLE WCPCSPECIALCHARGE
  ADD CONSTRAINT WCPCSPECIALCHARGE_WEIGHINGCAT FOREIGN KEY (WEIGHINGCATEGPERCUSTOMER_ID)
  REFERENCES WEIGHINGCATEGPERCUSTOMER (WEIGHINGCATEGPERCUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE WCPCSPECIALCHARGE
  ADD CONSTRAINT WCPCSPECIALCHARGE_SPECIALCH FOREIGN KEY (SPECIALCHARGE_ID)
  REFERENCES SPECIALCHARGE (SPECIALCHARGE_ID);