ALTER TABLE FCM_TAREFA MODIFY NRATIVIDADE NULL
/

CREATE TABLE FCM_ITEMCHECKLIST (
	NRITEMCHECKLIST   NUMBER   NOT NULL,
	NRATIVIDADE       NUMBER, 
	NRTAREFA          NUMBER,
	NRCRITERIOTAREFA  NUMBER,
	NRESCOPOSTAREFA   NUMBER,
	NRORG             NUMBER 	      DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 		NOT NULL,
    IDATIVO           VARCHAR2(1)     DEFAULT 'S'					NOT NULL,
    DTINCLUSAO        DATE            DEFAULT SYSDATE,
    DTULTATU          DATE            DEFAULT SYSDATE,
    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',
	CONSTRAINT CKC_IDATIVO_FCM_ITEMCHECKLIST CHECK (IDATIVO IN ('S','N'))
)
/
ALTER TABLE FCM_ITEMCHECKLIST ADD CONSTRAINT PK_FCM_ITEMCHECKLIST PRIMARY KEY (NRITEMCHECKLIST)
/
ALTER TABLE FCM_ITEMCHECKLIST 
  ADD CONSTRAINT FKFCM0157ATIVIDADE_ITCHECKLIST FOREIGN KEY (NRATIVIDADE)
      REFERENCES FCM_ATIVIDADE (NRATIVIDADE)
/	  
CREATE INDEX FCM00157_FK ON FCM_ITEMCHECKLIST (NRATIVIDADE ASC)
/
ALTER TABLE FCM_ITEMCHECKLIST 
  ADD CONSTRAINT FKFCM0158TAREFA_ITCHECKLIST FOREIGN KEY (NRTAREFA)
      REFERENCES FCM_TAREFA (NRTAREFA)
/
CREATE INDEX FCM00158_FK ON FCM_ITEMCHECKLIST (NRTAREFA ASC)
/

ALTER TABLE FCM_ITEMCHECKLIST 
  ADD CONSTRAINT FKFCM0159CRITERTAR_ITCHECKLIST FOREIGN KEY (NRCRITERIOTAREFA)
      REFERENCES FCM_CRITERIOTAREFA (NRCRITERIOTAREFA)
/
CREATE INDEX FCM00159_FK ON FCM_ITEMCHECKLIST (NRCRITERIOTAREFA ASC)
/
ALTER TABLE FCM_ITEMCHECKLIST 
  ADD CONSTRAINT FKFCM160ESCOPOSTAR_ITCHECKLIST FOREIGN KEY (NRESCOPOSTAREFA)
      REFERENCES FCM_ESCOPOSTAREFA (NRESCOPOSTAREFA)
/
CREATE INDEX FCM00160_FK ON FCM_ITEMCHECKLIST (NRESCOPOSTAREFA ASC)
/

CREATE TABLE FCM_ESCOPOSTAREFACHK (
	NRESCOPOSTAREFACHK NUMBER   NOT NULL,
	NRCHECKLIST        NUMBER, 
	NRTAREFACHK        NUMBER,
	NRESCOPOSTAREFA    NUMBER,
	NRORG              NUMBER 	       DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') 		NOT NULL,
    IDATIVO            VARCHAR2(1)     DEFAULT 'S'					NOT NULL,
    DTINCLUSAO         DATE            DEFAULT SYSDATE,
    DTULTATU           DATE            DEFAULT SYSDATE,
    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',
	CONSTRAINT CKC_IDATIVO_FCM_ESCOPOSTARCHK CHECK (IDATIVO IN ('S','N'))
)
/
ALTER TABLE FCM_ESCOPOSTAREFACHK ADD CONSTRAINT PK_FCM_ESCOPOSTAREFACHK PRIMARY KEY (NRESCOPOSTAREFACHK)
/

ALTER TABLE FCM_ESCOPOSTAREFACHK 
  ADD CONSTRAINT FKFCM0161CHECKLIST_ESCOPOTACHK FOREIGN KEY (NRCHECKLIST)
      REFERENCES FCM_CHECKLIST (NRCHECKLIST)
/
CREATE INDEX FCM00161_FK ON FCM_ESCOPOSTAREFACHK (NRCHECKLIST ASC)
/
ALTER TABLE FCM_ESCOPOSTAREFACHK 
  ADD CONSTRAINT FKFCM0162TAREFACHK_ESCOPOTACHK FOREIGN KEY (NRTAREFACHK)
      REFERENCES FCM_TAREFACHK (NRTAREFACHK)
/
CREATE INDEX FCM00162_FK ON FCM_ESCOPOSTAREFACHK (NRTAREFACHK ASC)
/
ALTER TABLE FCM_ESCOPOSTAREFACHK 
  ADD CONSTRAINT FKFCM0163ESCOPOSTAR_ESCTARCHK FOREIGN KEY (NRESCOPOSTAREFA)
      REFERENCES FCM_ESCOPOSTAREFA (NRESCOPOSTAREFA)
/
CREATE INDEX FCM00163_FK ON FCM_ESCOPOSTAREFACHK (NRESCOPOSTAREFA ASC)
/

DECLARE
V_NEWCODE VARCHAR2(20);

BEGIN
    FOR C_ESCOPOS IN (SELECT FCM_TAGSCHKTAREFA.NRTAGSTAREFA, FCM_TAGSCHKTAREFA.NRTAREFACHK, FCM_TAGSTAREFA.NRESCOPOSTAREFA, 
                             FCM_TAREFACHK.NRCHECKLIST, FCM_TAGSCHKTAREFA.CDOPERINCLUSAO, FCM_TAGSCHKTAREFA.NRORG
                        FROM FCM_TAGSCHKTAREFA
                        JOIN FCM_TAGSTAREFA 
                          ON FCM_TAGSTAREFA.NRTAGSTAREFA = FCM_TAGSCHKTAREFA.NRTAGSTAREFA
                        JOIN FCM_TAREFACHK
                          ON FCM_TAREFACHK.NRTAREFACHK = FCM_TAGSCHKTAREFA.NRTAREFACHK
                          WHERE FCM_TAGSCHKTAREFA.IDATIVO = 'S') LOOP
                          
        NOVO_CODIGO_SC('FCM_ESCOPOSTAREFACHK', V_NEWCODE, 1, C_ESCOPOS.NRORG);
        
        INSERT INTO FCM_ESCOPOSTAREFACHK (NRESCOPOSTAREFACHK, NRCHECKLIST, NRTAREFACHK, NRESCOPOSTAREFA, IDATIVO, 
                                          NRORG, DTINCLUSAO, CDOPERINCLUSAO, NRORGINCLUSAO)
             VALUES (V_NEWCODE, C_ESCOPOS.NRCHECKLIST, C_ESCOPOS.NRTAREFACHK, C_ESCOPOS.NRESCOPOSTAREFA, 'S', 
                      C_ESCOPOS.NRORG, SYSDATE,  C_ESCOPOS.CDOPERINCLUSAO, C_ESCOPOS.NRORG);
         
    END LOOP;
END;