-- ============================================================
--   TABLE: CELULAPROD
-- ============================================================
CREATE TABLE CELULAPROD
(
  CDCENTRAL         VARCHAR2(4)            NOT NULL,
  DTPRODUCAO        DATE                   NOT NULL,
  NRDIASEMANA       VARCHAR2(1)            NOT NULL,
  CDTIPOPRAT        VARCHAR2(2)            NOT NULL,
  CDFILIAGREGA      VARCHAR2(4)            NOT NULL,
  CDSERVFILI        VARCHAR2(5)            NOT NULL,
  NRORG             NUMBER                 DEFAULT 1 NULL    ,
  IDATIVO           VARCHAR2(1)            DEFAULT 'S' NOT NULL
  CONSTRAINT CKC_IDATIVO_CELULAPROD CHECK (
            IDATIVO IN ('S','N')),
  DTINCLUSAO        DATE                   NULL    ,
  DTULTATU          DATE                   NULL    ,
  NRORGINCLUSAO     NUMBER                 DEFAULT 1 NULL,
  CDOPERINCLUSAO    VARCHAR2(12)           NULL    ,
  NRORGULTATU       NUMBER                 DEFAULT 1 NULL,
  CDOPERULTATU      VARCHAR2(12)           DEFAULT '1',
  CONSTRAINT PK_CELULAPROD PRIMARY KEY (CDCENTRAL, DTPRODUCAO, NRDIASEMANA, CDTIPOPRAT, CDFILIAGREGA, CDSERVFILI, NRORG)
)
/

CREATE INDEX PLA00884_FK ON CELULAPROD (CDCENTRAL, NRORG)
/

ALTER TABLE CELULAPROD
    ADD CONSTRAINT FKPLA00884CELULAPROD FOREIGN KEY  (CDCENTRAL, NRORG)
       REFERENCES FILIAL (CDFILIAL, NRORG)
/

CREATE INDEX PLA00885_FK ON CELULAPROD (CDTIPOPRAT, NRORG)
/

ALTER TABLE CELULAPROD
    ADD CONSTRAINT FKPLA00885CELULAPROD FOREIGN KEY  (CDTIPOPRAT, NRORG)
       REFERENCES TIPOPRAT (CDTIPOPRAT, NRORG)
/

CREATE INDEX PLA00886_FK ON CELULAPROD (CDFILIAGREGA, CDSERVFILI, NRORG)
/

ALTER TABLE CELULAPROD
    ADD CONSTRAINT FKPLA00886CELULAPROD FOREIGN KEY  (CDFILIAGREGA, CDSERVFILI, NRORG)
       REFERENCES SERVICO (CDFILIAL, CDSERVFILI, NRORG)
/

-- ============================================================
--   TABLE: CONSPLAN 
-- ============================================================
ALTER TABLE CONSPLAN ADD IDTPREQPROD VARCHAR2(1)
   CONSTRAINT CKC_IDTPREQPROD_CONSPLAN CHECK (IDTPREQPROD IN ('E','D','P'))
/

-- ============================================================
--   TABLE: EMBDISTCARD 
-- ============================================================
ALTER TABLE EMBDISTCARD ADD CDAGRUPEMB VARCHAR2(10) NULL
/

-- ============================================================
--   TABLE: CARDDISTPROD 
-- ============================================================
ALTER TABLE CARDDISTPROD MODIFY NRDISTCARD NUMBER NOT NULL
/

DROP INDEX PK_CARDDISTPROD
/
ALTER TABLE CARDDISTPROD DROP CONSTRAINT PK_CARDDISTPROD
/

ALTER TABLE CARDDISTPROD ADD CONSTRAINT PK_CARDDISTPROD PRIMARY KEY (NRDISTPROD, NRDISTCARD, NRORG)
/

-- ============================================================
--   TABLE: PLANRECPROD 
-- ============================================================
ALTER TABLE PLANRECPROD DROP CONSTRAINT FKPLA00452PPRODFILAGREG
/

ALTER TABLE PLANRECPROD DROP CONSTRAINT PK_PLANRECPROD
/

DROP INDEX PK_PLANRECPROD
/

ALTER TABLE PLANRECPROD ADD IDCOOKCHILL VARCHAR2(1) DEFAULT 'N'
   CONSTRAINT CKC_IDCOOKCHILL_PLANRECPROD CHECK (IDCOOKCHILL IN ('S','N'))
/

ALTER TABLE PLANRECPROD ADD IDFINALIZACAO VARCHAR2(1) DEFAULT 'M' 
   CONSTRAINT CKC_IDFINALIZACAO_PLANRECPROD CHECK (IDFINALIZACAO IN ('M','F'))
/

ALTER TABLE PLANRECPROD ADD IDTPPRODCHILL VARCHAR2(30) DEFAULT 'NENHUM'
   CONSTRAINT CKC_IDTPPRODCHILL_PLANRECPROD CHECK (IDTPPRODCHILL IN ('CONGELADO','RESFRIADO','NENHUM'))
/

ALTER TABLE PLANRECPROD ADD IDESTADO VARCHAR2(30) DEFAULT 'FINALIZACAO'
   CONSTRAINT CKC_IDESTADO_PLANRECPROD CHECK (IDESTADO IN ('PRODUCAO', 'FINALIZACAO'))
/

ALTER TABLE PLANRECPROD ADD CONSTRAINT PK_PLANRECPROD PRIMARY KEY (NRPLANPROD, CDPRODACABADO, CDFILIAGREG, CDSERVFILIAGREG, DTCONSUMO, CDPRODUTO, CDTIPOPRAT, IDCOOKCHILL, IDFINALIZACAO, IDTPPRODCHILL, IDESTADO, NRORG)
/

-- ============================================================
--   TABLE: PLANPRODFILIAGREGA 
-- ============================================================
ALTER TABLE PLANPRODFILIAGREGA DROP CONSTRAINT FKPLA00451PLANPRODACA
/

ALTER TABLE PLANPRODFILIAGREGA DROP CONSTRAINT PK_PLANPRODFILIAGREGA
/

DROP INDEX PK_PLANPRODFILIAGREGA
/

UPDATE PLANPRODFILIAGREGA SET IDCOOKCHILL = 'N', IDFINALIZACAO = 'M'
/

ALTER TABLE PLANPRODFILIAGREGA ADD IDTPPRODCHILL VARCHAR2(30) DEFAULT 'NENHUM'
   CONSTRAINT CKC_IDTPPRODCHILL_PLANPRODFIL CHECK (IDTPPRODCHILL IN ('CONGELADO','RESFRIADO','NENHUM'))
/

ALTER TABLE PLANPRODFILIAGREGA ADD IDESTADO VARCHAR2(30) DEFAULT 'FINALIZACAO'
   CONSTRAINT CKC_IDESTADO_PLANPRODFIL CHECK (IDESTADO IN ('PRODUCAO','FINALIZACAO'))
/

ALTER TABLE PLANPRODFILIAGREGA ADD CONSTRAINT PK_PLANPRODFILIAGREGA PRIMARY KEY (NRPLANPROD, CDPRODACABADO, CDFILIAGREG, CDSERVFILIAGREG, DTCONSUMO,IDCOOKCHILL,IDFINALIZACAO,IDTPPRODCHILL, IDESTADO, NRORG)
/

-- ============================================================
--   TABLE: PLANPRODACABADO 
-- ============================================================
ALTER TABLE PLANPRODACABADO DROP CONSTRAINT PK_PLANPRODACABADO
/

DROP INDEX PK_PLANPRODACABADO 
/

ALTER TABLE PLANPRODACABADO ADD IDCOOKCHILL VARCHAR2(1) DEFAULT 'N'
   CONSTRAINT CKC_IDCOOKCHILL_PLANPRODACA CHECK (IDCOOKCHILL IN ('S','N'))
/

ALTER TABLE PLANPRODACABADO ADD IDFINALIZACAO VARCHAR2(1) DEFAULT 'M' 
   CONSTRAINT CKC_IDFINALIZACAO_PLANPRODACA CHECK (IDFINALIZACAO IN ('M','F'))
/

ALTER TABLE PLANPRODACABADO ADD IDTPPRODCHILL VARCHAR2(30) DEFAULT 'NENHUM'
   CONSTRAINT CKC_IDTPPRODCHILL_PLANPRODACA CHECK (IDTPPRODCHILL IN ('CONGELADO','RESFRIADO','NENHUM'))
/

ALTER TABLE PLANPRODACABADO ADD IDESTADO VARCHAR2(30) DEFAULT 'FINALIZACAO'
   CONSTRAINT CKC_IDESTADO_PLANPRODACA CHECK (IDESTADO IN ('PRODUCAO', 'FINALIZACAO'))
/

ALTER TABLE PLANPRODACABADO ADD CONSTRAINT PK_PLANPRODACABADO PRIMARY KEY (NRPLANPROD, CDPRODACABADO,IDCOOKCHILL,IDFINALIZACAO,IDTPPRODCHILL,IDESTADO, NRORG)
/

-- ============================================================
--   TABLE: PLANPRODFILIAGREGA 
-- ============================================================
ALTER TABLE PLANPRODFILIAGREGA
    ADD CONSTRAINT FKPLA00451PLANPRODACA FOREIGN KEY  (NRPLANPROD, CDPRODACABADO,IDCOOKCHILL,IDFINALIZACAO,IDTPPRODCHILL,IDESTADO, NRORG)
       REFERENCES PLANPRODACABADO (NRPLANPROD, CDPRODACABADO,IDCOOKCHILL,IDFINALIZACAO,IDTPPRODCHILL,IDESTADO, NRORG)
/

-- ============================================================
--   TABLE: PLANRECPROD 
-- ============================================================
ALTER TABLE PLANRECPROD
    ADD CONSTRAINT FKPLA00452PPRODFILAGREG FOREIGN KEY  (NRPLANPROD, CDPRODACABADO, CDFILIAGREG, CDSERVFILIAGREG, DTCONSUMO,IDCOOKCHILL,IDFINALIZACAO,IDTPPRODCHILL,IDESTADO, NRORG)
       REFERENCES PLANPRODFILIAGREGA (NRPLANPROD, CDPRODACABADO, CDFILIAGREG, CDSERVFILIAGREG, DTCONSUMO,IDCOOKCHILL,IDFINALIZACAO,IDTPPRODCHILL,IDESTADO, NRORG)
/

-- VERSAO --
BEGIN ATUVEROBJ('ATUALIZACAO_ESTRUTURA_BD','0.00.000'); END;
/