--ABS-108048 / ABS-108139
--------- INVOICELINESUBTYPE_DESC -----------
UPDATE INVOICELINESUBTYPE_DESC ILSD
SET ILSD.DESCRIPTION = 'Code Label'
WHERE ILSD.INVOICELINESUBTYPE_ID = (SELECT ILS.INVOICELINESUBTYPE_ID 
                                    FROM INVOICELINESUBTYPE ILS
                                    WHERE ILS.INVOICELINETYPE_ID = 12 AND ILS.SUBTYPE = 11);
COMMIT;

----------- PRODUCT -----------
CREATE TABLE SEARCHCONDITION_PROD
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 = 'PRODUCT'
    AND    UTC.COLUMN_NAME = 'BENCHMARKRELEVANT'
    AND    UC.CONSTRAINT_TYPE = 'C' ORDER  BY UTC.COLUMN_ID));


CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_PROD AS
STMT VARCHAR2(100);
BEGIN
  FOR X IN (SELECT T.CONSTRAINT_NAME FROM SEARCHCONDITION_PROD T WHERE TO_CHAR(T.SEARCHCONDITION) = 'BENCHMARKRELEVANT IN (''Y'', ''N'')')
    LOOP
      BEGIN
      STMT := 'ALTER TABLE PRODUCT DROP CONSTRAINT '|| X.CONSTRAINT_NAME;
      EXECUTE IMMEDIATE(STMT);
      EXCEPTION WHEN OTHERS THEN NULL;
      END;
    END LOOP;
END DROPCONSTRAINT_PROD;
/

EXECUTE DROPCONSTRAINT_PROD;

DROP PROCEDURE DROPCONSTRAINT_PROD;
DROP TABLE SEARCHCONDITION_PROD;

ALTER TABLE PRODUCT ADD CONSTRAINT CHK_PRODUCT_BENCHMARKRELEVANT CHECK (BENCHMARKRELEVANT IN ('Y', 'N'));