ALTER TABLE CONFTELA ADD DTINIVIGENCIA DATE
/
 
UPDATE CONFTELA
   SET DTINIVIGENCIA = TO_DATE('01/01/2019', 'DD/MM/YYYY')
 WHERE DTINIVIGENCIA IS NULL
/

ALTER TABLE CONFTELA MODIFY DTINIVIGENCIA NOT NULL
/

ALTER TABLE ITMENUCONFTE ADD DTINIVIGENCIA DATE
/

UPDATE ITMENUCONFTE
   SET DTINIVIGENCIA = TO_DATE('01/01/2019', 'DD/MM/YYYY')
 WHERE DTINIVIGENCIA IS NULL
/

ALTER TABLE ITMENUCONFTE MODIFY DTINIVIGENCIA NOT NULL
/

ALTER TABLE ITMENUCONFTE DROP CONSTRAINT PK_ITMENUCONFTE
/

DROP INDEX PK_ITMENUCONFTE
/

ALTER TABLE ITMENUCONFTE ADD CONSTRAINT PK_ITMENUCONFTE PRIMARY KEY
   (CDFILIAL, NRCONFTELA, IDTPBUTTON, NRBUTTON, NRPGCONFTELA, IDTPBUTONAUX, NRBUTTONAUX, NRPGCONFTAUX, IDTPBUTONAUX2, NRBUTTONAUX2, NRPGCONFTAUX2, DTINIVIGENCIA)
/

ALTER TABLE PRODAMBIENTE ADD DTINIVIGENCIA DATE
/

UPDATE PRODAMBIENTE
   SET DTINIVIGENCIA = TO_DATE('01/01/2019', 'DD/MM/YYYY')
 WHERE DTINIVIGENCIA IS NULL
/

ALTER TABLE PRODAMBIENTE MODIFY DTINIVIGENCIA NOT NULL
/

ALTER TABLE CAIXA DROP CONSTRAINT FKPDV00345CONFTELA
/

ALTER TABLE ITMENUCONFTE DROP CONSTRAINT FKPDV00332CONFTELA
/

ALTER TABLE PRODAMBIENTE DROP CONSTRAINT FKVND00515CONFTELA
/

ALTER TABLE CONFTELA DROP CONSTRAINT PK_CONFTELA
/

DROP INDEX PK_CONFTELA
/

ALTER TABLE CONFTELA ADD CONSTRAINT PK_CONFTELA PRIMARY KEY (NRCONFTELA, CDFILIAL, DTINIVIGENCIA)
/

CREATE INDEX VND00332_FK ON ITMENUCONFTE (NRCONFTELA ASC, CDFILIAL ASC, DTINIVIGENCIA ASC) 
/

ALTER TABLE ITMENUCONFTE ADD 
	CONSTRAINT FKVND00332CONFTELA FOREIGN KEY (NRCONFTELA, CDFILIAL, DTINIVIGENCIA)
		REFERENCES CONFTELA (NRCONFTELA, CDFILIAL, DTINIVIGENCIA)
/

CREATE INDEX VND00515_FK ON PRODAMBIENTE (NRCONFTELA ASC, CDFILIAL ASC, DTINIVIGENCIA ASC) 
/

ALTER TABLE PRODAMBIENTE ADD 
	CONSTRAINT FKVND00515CONFTELA FOREIGN KEY (NRCONFTELA, CDFILIAL, DTINIVIGENCIA)
		REFERENCES CONFTELA (NRCONFTELA, CDFILIAL, DTINIVIGENCIA)
/


CREATE TABLE CONFTELACAIXA (
   CDFILIAL       	VARCHAR2(4) 	NOT NULL,
   NRCONFTELA     	VARCHAR2(5) 	NOT NULL,
   DTINIVIGENCIA  	DATE 			NOT NULL,
   CDFILICAIXA    	VARCHAR2(04) 	NOT NULL,
   CDCAIXA       	VARCHAR2(03) 	NOT NULL,
   NRORG 		   	NUMBER 			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO        	VARCHAR2(1) 	DEFAULT 'S' NOT NULL,
      CONSTRAINT CKC_IDATIVO_CONFTELACAIXA CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO     	DATE 			NULL,
   DTULTATU       	DATE  			NULL,
   NRORGINCLUSAO  	NUMBER  		DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERINCLUSAO 	VARCHAR2(12) 	DEFAULT '1' NULL,
   NRORGULTATU    	NUMBER 			DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NULL,
   CDOPERULTATU 	VARCHAR2(12) 	DEFAULT '1'
)
/

ALTER TABLE CONFTELACAIXA ADD CONSTRAINT PK_CONFTELACAIXA PRIMARY KEY (CDFILIAL, NRCONFTELA, DTINIVIGENCIA, CDFILICAIXA, CDCAIXA, NRORG)
/

CREATE INDEX VND00769_FK ON CONFTELACAIXA (CDFILIAL ASC, NRCONFTELA ASC, DTINIVIGENCIA ASC) 
/

ALTER TABLE CONFTELACAIXA ADD 
	CONSTRAINT FKVND00769CONFTELA FOREIGN KEY (CDFILIAL, NRCONFTELA, DTINIVIGENCIA)
		REFERENCES CONFTELA (CDFILIAL, NRCONFTELA, DTINIVIGENCIA)
/

BEGIN
   DECLARE
      V_COUNT NUMBER;
   BEGIN
      FOR C_CONFCAIXA IN
         (SELECT CX.CDFILIAL CDFILICAIXA, CX.CDCAIXA, C.CDFILIAL, CX.NRCONFTELA, C.DTINIVIGENCIA
            FROM CAIXA CX, CONFTELA C
           WHERE CX.NRCONFTELA IS NOT NULL
             AND CX.NRCONFTELA = C.NRCONFTELA
             AND CX.CDFILICONFTE = C.CDFILIAL) LOOP
         V_COUNT := 0;
         BEGIN
            SELECT COUNT(*)
              INTO V_COUNT
              FROM CONFTELACAIXA
             WHERE NRCONFTELA = C_CONFCAIXA.NRCONFTELA
               AND CDFILIAL = C_CONFCAIXA.CDFILIAL
               AND DTINIVIGENCIA = C_CONFCAIXA.DTINIVIGENCIA
               AND CDFILICAIXA = C_CONFCAIXA.CDFILICAIXA
               AND CDCAIXA = C_CONFCAIXA.CDCAIXA;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_COUNT := 0;
         END;
         IF V_COUNT = 0 THEN
            INSERT INTO CONFTELACAIXA
               (CDFILIAL, NRCONFTELA, DTINIVIGENCIA, CDCAIXA, CDFILICAIXA)
            VALUES
               (C_CONFCAIXA.CDFILIAL, C_CONFCAIXA.NRCONFTELA, C_CONFCAIXA.DTINIVIGENCIA, C_CONFCAIXA.CDCAIXA, C_CONFCAIXA.CDFILICAIXA);
         END IF;
      END LOOP;
   END;
END;
/