--ABS-77640
---------------- PRODUCTLISTPERCUSTOMER -------------------

CREATE TABLE SEARCHCONDITION_PLPC
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 = 'PRODUCTLISTPERCUSTOMER'
    AND    UTC.COLUMN_NAME = 'CHARGESERVICECOSTS' OR UTC.COLUMN_NAME='CHARGESERVICECOSTSFOR'
    AND    UC.CONSTRAINT_TYPE = 'C' ORDER  BY UTC.COLUMN_ID));


CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_PLPC AS
STMT VARCHAR2(100);
BEGIN
  FOR X IN (SELECT T.CONSTRAINT_NAME FROM SEARCHCONDITION_PLPC T WHERE TO_CHAR(T.SEARCHCONDITION) = 'CHARGESERVICECOSTS IN (0, 1, 2)' OR TO_CHAR(T.SEARCHCONDITION) = 'CHARGESERVICECOSTSFOR IN (0, 1)')
    LOOP
      BEGIN
      STMT := 'ALTER TABLE PRODUCTLISTPERCUSTOMER DROP CONSTRAINT '|| X.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE(STMT);
      EXCEPTION WHEN OTHERS THEN NULL;
      END;
    END LOOP;
END DROPCONSTRAINT_PLPC;
/

EXECUTE DROPCONSTRAINT_PLPC;

DROP PROCEDURE DROPCONSTRAINT_PLPC;
DROP TABLE SEARCHCONDITION_PLPC;

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD CONSTRAINT CHARGESERVICECOSTS_VALID2 CHECK (CHARGESERVICECOSTS IN (0,1,2));
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD CONSTRAINT CHARGESERVICECOSTSFOR_VALID1 CHECK (CHARGESERVICECOSTSFOR IN (0,1));