-- ABS-70238/ ABS-71334

-- Correction on CLIENTDELIVERYNOTE
-- During new EMPTY DB creation, because non-unique indexes were created before PKs, the corresponding PK's index was not created anymore
-- CLIENTDELIVERYNOTE TABLE --
ALTER TABLE CLIENTDELIVERYNOTE
  DROP CONSTRAINT XPKCLIENTDELIVERYNOTE CASCADE;
  
ALTER TABLE CLIENTDELIVERYNOTE
  ADD CONSTRAINT XPKCLIENTDELIVERYNOTE PRIMARY KEY (CLIENTDELIVERYNOTE_ID);

--Next statements are needed because PK was dropped using CASCADE
ALTER TABLE CLIENTDELIVERYNOTE
  ADD CONSTRAINT CORRCLIENTDELNOTE_CLIENTDN FOREIGN KEY (CORRECTIONCLIENTDELNOTE_ID)
  REFERENCES CLIENTDELIVERYNOTE (CLIENTDELIVERYNOTE_ID) ON DELETE CASCADE;
  
ALTER TABLE CLIENTDELIVERYNOTE
  ADD CONSTRAINT CLIENTDELNOTE_CLIENTDELNOTE FOREIGN KEY (ADDITIONALTONOTE_ID)
  REFERENCES CLIENTDELIVERYNOTE (CLIENTDELIVERYNOTE_ID) ON DELETE SET NULL;
  
ALTER TABLE CLIENTDELIVERYNOTELINE
  ADD CONSTRAINT CLIENTDELIVERYNOTELINE_CLIENTD FOREIGN KEY (CLIENTDELIVERINOTE_ID)
  REFERENCES CLIENTDELIVERYNOTE (CLIENTDELIVERYNOTE_ID) ON DELETE CASCADE;
  
ALTER TABLE CLIENTDELIVERYNOTESPECCHARGE
  ADD CONSTRAINT CLIENTDELIVERYNOTESPECCHARGE_C FOREIGN KEY (CLIENTDELIVERINOTE_ID)
  REFERENCES CLIENTDELIVERYNOTE (CLIENTDELIVERYNOTE_ID) ON DELETE CASCADE;
  
ALTER TABLE CLIENTDELIVERYNOTETAX
  ADD CONSTRAINT CLIENTDELIVERYNOTETAX_CLIENTDE FOREIGN KEY (CLIENTDELIVERINOTE_ID)
  REFERENCES CLIENTDELIVERYNOTE (CLIENTDELIVERYNOTE_ID) ON DELETE CASCADE;
  
ALTER TABLE LTXSORTSESSIONLOCCONTENTS
  ADD CONSTRAINT LTXSORTSESSIONLOCCONT_CLIENTD FOREIGN KEY (CLIENTDELIVERYNOTE_ID)
  REFERENCES CLIENTDELIVERYNOTE (CLIENTDELIVERYNOTE_ID);
  
-- Next correction is done in addtion to changes done in Script_09.00.00.56.sql (old PK and sequence were dropped)
-- With this script PK was redefined, but XAK1CHARACTERISTICSTEMPLINE remained on DB
-- CHARACTERISTICSTEMPLINE TABLE --
ALTER TABLE CHARACTERISTICSTEMPLINE
  DROP CONSTRAINT XPKCHARACTERISTICSTEMPLINE CASCADE;
  
DROP INDEX XAK1CHARACTERISTICSTEMPLINE;
  
ALTER TABLE CHARACTERISTICSTEMPLINE
  ADD CONSTRAINT XPKCHARACTERISTICSTEMPLINE PRIMARY KEY (CHARACTERISTICSTEMPLATE_ID, CHARACTERISTIC_ID);

-- Next corrections were discovered after EXPORT_EMPTYDBSCRIPT package was changed to create PKs before rest of indexes
-- For example, on CONTRACTCOMPONENTBILLING table, XIF1CONTRACTCOMPBILLING index on CONTRACT_ID was created before PK
-- Therefor XPK index on CONTRACT_ID was not created anymore

-- CONTRACTCOMPONENTBILLING TABLE --
-- Recreate PK constraint because XIF1CONTRACT was created before XPKCONTRACTCOMPONENTBILLING index
ALTER TABLE CONTRACTCOMPONENTBILLING
  DROP CONSTRAINT XPKCONTRACTCOMPONENTBILLING CASCADE;
  
DROP INDEX XIF1CONTRACTCOMPBILLING;
  
ALTER TABLE CONTRACTCOMPONENTBILLING
  ADD CONSTRAINT XPKCONTRACTCOMPONENTBILLING PRIMARY KEY (CONTRACT_ID);

-- CONTRACTCOMPONENTGRMCHARGES TABLE --
ALTER TABLE CONTRACTCOMPONENTGRMCHARGES
  DROP CONSTRAINT XPKCONTRACTCOMPONENTGRMCHARGES CASCADE;
  
DROP INDEX XIF1CONTRACTCOMPGRMCHARGES;
  
ALTER TABLE CONTRACTCOMPONENTGRMCHARGES
  ADD CONSTRAINT XPKCONTRACTCOMPONENTGRMCHARGES PRIMARY KEY (CONTRACT_ID);

-- CONTRACTCOMPONENTPAYMENT TABLE --
ALTER TABLE CONTRACTCOMPONENTPAYMENT
  DROP CONSTRAINT XPKCONTRACTCOMPONENTPAYMENT CASCADE;
  
DROP INDEX XIF1CONTRACTCOMPPAY;
  
ALTER TABLE CONTRACTCOMPONENTPAYMENT
  ADD CONSTRAINT XPKCONTRACTCOMPONENTPAYMENT PRIMARY KEY (CONTRACT_ID);

-- ARTRANSACTIONDETAIL TABLE --
-- XPK index name different than PK constraint
ALTER TABLE ARTRANSACTIONDETAIL
  DROP CONSTRAINT XPKARTRANSACTIONDETAILS CASCADE;
  
--DROP INDEX XPKARTRANSACTIONDETAIL;
  
ALTER TABLE ARTRANSACTIONDETAIL
  ADD CONSTRAINT XPKARTRANSACTIONDETAIL PRIMARY KEY (ARTRANSACTION_ID, TURNOVERGROUP_ID, BUSINESSUNIT_ID);

-- BENCHMARKCATEGORY TABLE --
-- PK constraint had an Oracle generated name;so different than XPK index
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_BENCHMARKCAT AS
  CONSTRAINTNAME VARCHAR2(100);
  STMT           VARCHAR2(100);
BEGIN
  BEGIN
    SELECT CONSTRAINT_NAME
      INTO CONSTRAINTNAME
      FROM USER_CONSTRAINTS U
     WHERE U.CONSTRAINT_TYPE = 'P'
       AND U.TABLE_NAME = 'BENCHMARKCATEGORY';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN;
  END;

  BEGIN
    STMT := 'ALTER TABLE BENCHMARKCATEGORY DROP CONSTRAINT ' ||
            CONSTRAINTNAME || ' CASCADE';
    EXECUTE IMMEDIATE (STMT);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
END DROPCONSTRAINT_BENCHMARKCAT;
/

EXECUTE DROPCONSTRAINT_BENCHMARKCAT;

DROP PROCEDURE DROPCONSTRAINT_BENCHMARKCAT;

DROP INDEX XPKBENCHMARKCATEGORY;

ALTER TABLE BENCHMARKCATEGORY
  ADD CONSTRAINT XPKBENCHMARKCATEGORY PRIMARY KEY(BENCHMARKCATEGORY_ID);

ALTER TABLE COSTCENTER
  ADD CONSTRAINT COSTCENTER_BM FOREIGN KEY (BENCHMARKCATEGORY_ID)
  REFERENCES BENCHMARKCATEGORY (BENCHMARKCATEGORY_ID);

-- DBCHECKONSYSTEMSETTING TABLE --
-- XPK index exists without PK constraint
--DROP INDEX XPKDBCHECKONSYSTEMSETTING;
  
ALTER TABLE DBCHECKONSYSTEMSETTING
  ADD CONSTRAINT XPKDBCHECKONSYSTEMSETTING PRIMARY KEY (DBCONSISTENCYCHECK_ID, NAME);

-- NATIONALACCOUNT TABLE --
-- PK constraint has an Oracle generated name;so different than XPK index
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_NATIONALACC AS
  CONSTRAINTNAME VARCHAR2(100);
  STMT           VARCHAR2(100);
BEGIN
  BEGIN
    SELECT CONSTRAINT_NAME
      INTO CONSTRAINTNAME
      FROM USER_CONSTRAINTS U
     WHERE U.CONSTRAINT_TYPE = 'P'
       AND U.TABLE_NAME = 'NATIONALACCOUNT';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN;
  END;

  BEGIN
    STMT := 'ALTER TABLE NATIONALACCOUNT DROP CONSTRAINT ' ||
            CONSTRAINTNAME || ' CASCADE';
    EXECUTE IMMEDIATE (STMT);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
END DROPCONSTRAINT_NATIONALACC;
/

EXECUTE DROPCONSTRAINT_NATIONALACC;

DROP PROCEDURE DROPCONSTRAINT_NATIONALACC;

DROP INDEX XPKNATIONALACCOUNT;

ALTER TABLE NATIONALACCOUNT
  ADD CONSTRAINT XPKNATIONALACCOUNT PRIMARY KEY(NATIONALACCOUNT_ID);

alter table CUSTOMER
  add constraint CUST_NATIONALACCOUNT_ID foreign key (NATIONALACCOUNT_ID)
  references NATIONALACCOUNT (NATIONALACCOUNT_ID);

-- WCPCSPECIALCHARGE TABLE --
-- PK constraint has an Oracle generated name;so different than XPK index
CREATE OR REPLACE PROCEDURE DROPCONSTRAINT_WCPCSPECCH AS
  CONSTRAINTNAME VARCHAR2(100);
  STMT           VARCHAR2(100);
BEGIN
  BEGIN
    SELECT CONSTRAINT_NAME
      INTO CONSTRAINTNAME
      FROM USER_CONSTRAINTS U
     WHERE U.CONSTRAINT_TYPE = 'P'
       AND U.TABLE_NAME = 'WCPCSPECIALCHARGE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN;
  END;

  BEGIN
    STMT := 'ALTER TABLE WCPCSPECIALCHARGE DROP CONSTRAINT ' ||
            CONSTRAINTNAME || ' CASCADE';
    EXECUTE IMMEDIATE (STMT);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
END DROPCONSTRAINT_WCPCSPECCH;
/

EXECUTE DROPCONSTRAINT_WCPCSPECCH;

DROP PROCEDURE DROPCONSTRAINT_WCPCSPECCH;

DROP INDEX XPKWCPCSPECIALCHARGE;

ALTER TABLE WCPCSPECIALCHARGE
  ADD CONSTRAINT XPKWCPCSPECIALCHARGE PRIMARY KEY(WCPCSPECIALCHARGE_ID);