CREATE TABLE PARAMPROGDIST
(
    CDFILIAL          VARCHAR2(4)                                                   NOT NULL,
    NRORDEM           INTEGER                                                       NOT NULL,
    CDPRODUTO         VARCHAR2(10)                                                  NOT NULL,
    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_DISTTRIBUICAOPROD CHECK (IDATIVO IN ('S','N')),
        CONSTRAINT PK_PARAMPROGDIST PRIMARY KEY (CDFILIAL, CDPRODUTO)
)
/
CREATE TABLE DISTTRIBUICAOPROD(
    NRSEQDISTRIBUICAO NUMBER NOT NULL,
    CDFILIAL          VARCHAR2(04)                NOT NULL,  
    DTDISTIBUICAO     DATE                        NOT NULL,
    IDSITDISTRIB      VARCHAR2(01)    DEFAULT 'P' NOT NULL,   
    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_DISTTRIBPROD CHECK (IDATIVO IN ('S','N')),
        CONSTRAINT UNIQUE_DISTTRIBPROD UNIQUE (CDFILIAL, DTDISTIBUICAO),
        CONSTRAINT PK_DISTTRIBUICAOPROD PRIMARY KEY (NRSEQDISTRIBUICAO)
)
/
CREATE TABLE DISTTRIBPLANPROD(
    NRSEQDISTRIBUICAO NUMBER                                                        NOT NULL,
    CDFILIAL          VARCHAR2(4)                                                   NOT NULL,
    CDPRODUTO         VARCHAR2(10)                                                  NOT NULL,
    NRORDEM           INTEGER                                                       NULL,
    QTSUGPROD         FLOAT                                                         NULL,
    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_DISTTRIBPLAN CHECK (IDATIVO IN ('S','N')),
        CONSTRAINT PK_DISTTRIBPLANPROD PRIMARY KEY (NRSEQDISTRIBUICAO, CDFILIAL, CDPRODUTO)
)
/
ALTER TABLE DISTTRIBPLANPROD
    ADD CONSTRAINT FKPED00468DISTTRIBU FOREIGN KEY  (NRSEQDISTRIBUICAO)
       REFERENCES DISTTRIBUICAOPROD (NRSEQDISTRIBUICAO) ON DELETE CASCADE
/

CREATE INDEX PED00468_FK  ON DISTTRIBPLANPROD (NRSEQDISTRIBUICAO ASC)
/

ALTER TABLE DISTTRIBPLANPROD
    ADD CONSTRAINT FKPED00469FILIAL FOREIGN KEY  (CDFILIAL)
       REFERENCES FILIAL (CDFILIAL)
/

CREATE INDEX PED00469_FK ON DISTTRIBPLANPROD (CDFILIAL ASC)
/

ALTER TABLE DISTTRIBPLANPROD
    ADD CONSTRAINT FKPED00470PROD FOREIGN KEY  (CDPRODUTO)
       REFERENCES PRODUTO (CDPRODUTO)
/
CREATE INDEX PED00470_FK ON DISTTRIBPLANPROD (CDPRODUTO ASC)
/
CREATE TABLE DISTTRIBPRODFILI(
    CDFILIAL          VARCHAR2(4)                                                   NOT NULL,
    NRSEQDISTRIBUICAO NUMBER                                                        NOT NULL,
    NRSEQDISTRIBFILI  INTEGER                                                       NOT NULL,
    IDSITDISTRIBFILI  VARCHAR2(01)  DEFAULT 'P'                                     NOT NULL,
    QTPRODFILIAL      FLOAT                                                         NULL,   
    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_DISTTRIBFILI CHECK (IDATIVO IN ('S','N')),
        CONSTRAINT CKC_IDSITDISTRIB_DISTTRIBFILI CHECK (IDSITDISTRIBFILI IN ('P','A', 'C')),
        CONSTRAINT PK_DISTTRIBPRODFILI PRIMARY KEY (NRSEQDISTRIBUICAO, CDFILIAL, NRSEQDISTRIBFILI)
)
/
ALTER TABLE DISTTRIBPRODFILI
    ADD CONSTRAINT FKPED00471DISTRIBFILI FOREIGN KEY  (NRSEQDISTRIBUICAO)
       REFERENCES DISTTRIBUICAOPROD (NRSEQDISTRIBUICAO)  ON DELETE CASCADE
/

CREATE INDEX PED00471_FK ON DISTTRIBPRODFILI (NRSEQDISTRIBUICAO ASC)
/
ALTER TABLE DISTTRIBPRODFILI
    ADD CONSTRAINT FKPED00472DISTRIBFILIAL FOREIGN KEY  (CDFILIAL)
       REFERENCES FILIAL (CDFILIAL)
/
CREATE INDEX PED00472_FK ON DISTTRIBPRODFILI (CDFILIAL ASC)
/
CREATE TABLE PRODDISTTRIBFILI(
    NRSEQDISTRIBUICAO NUMBER                                                        NOT NULL,
    NRSEQDISTRIBFILI  INTEGER                                                       NOT NULL,
    CDFILIAL          VARCHAR2(4)                                                   NOT NULL,
    CDPRODUTO         VARCHAR2(10)                                                  NOT NULL,    
    NRORDEM           INTEGER                                                       NULL,
    QTPRODUCAO        FLOAT                                                         NULL,   
    QTENTREGUE        FLOAT                                                         NULL,  
    QTDISTRIBUIR      FLOAT                                                         NULL,  
    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_PRODDISTTRIB CHECK (IDATIVO IN ('S','N')),
        CONSTRAINT PK_PRODDISTTRIBFILI PRIMARY KEY (NRSEQDISTRIBUICAO, CDFILIAL, NRSEQDISTRIBFILI, CDPRODUTO)
)
/
ALTER TABLE PRODDISTTRIBFILI
    ADD CONSTRAINT FKPED00473PRODDISTTRIB FOREIGN KEY  (NRSEQDISTRIBUICAO, CDFILIAL, NRSEQDISTRIBFILI)
       REFERENCES DISTTRIBPRODFILI (NRSEQDISTRIBUICAO, CDFILIAL, NRSEQDISTRIBFILI)   ON DELETE CASCADE
/

CREATE INDEX PED00473_FK ON PRODDISTTRIBFILI (NRSEQDISTRIBUICAO ASC, CDFILIAL ASC, NRSEQDISTRIBFILI ASC)
/

CREATE TABLE RELACDISTTRIBPED(
    CDFILIAL          VARCHAR2(4)                                                   NOT NULL,
    NRSEQDISTRIBUICAO NUMBER                                                        NOT NULL,
    NRSEQDISTRIBFILI  INTEGER                                                       NOT NULL,
    CDFILIALPED       VARCHAR2(04)                                                  NOT NULL,
    NRPEDIDO          VARCHAR2(10)                                                  NOT  NULL,
    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_RELACDISTTRIBI CHECK (IDATIVO IN ('S','N')),
        CONSTRAINT PK_RELACDISTTRIBPED PRIMARY KEY (NRSEQDISTRIBUICAO, CDFILIAL, NRSEQDISTRIBFILI, CDFILIALPED, NRPEDIDO)
)
/