CREATE TABLE TRAB_RASTREIAINSUMOOP
(
    NRRASTREIA       VARCHAR2(10) NOT NULL,
    NRSEQRASTREIAINS INTEGER      NOT NULL,
    CDFILIAL         VARCHAR2(04) NOT NULL,
    CDPRODUTO        VARCHAR2(10) NOT NULL,
    NRLOTEESTQ       VARCHAR2(20) NOT NULL,
    NRSUBLOTE        VARCHAR2(04) NOT NULL,
    NRORDPROD        VARCHAR2(10) NULL,
    NRSUBORDPROD     VARCHAR2(10) NULL,
    QTPRACABADO      FLOAT        NULL,
    DTHRFIMREAOP     DATE         NULL,
   NRORG                       NUMBER DEFAULT 	 SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_TRAB_RASINSOP CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER             DEFAULT 1 NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER             DEFAULT 1 NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1',
    CONSTRAINT PK_RASTREIAINSUMOOP PRIMARY KEY (NRRASTREIA, NRSEQRASTREIAINS)
)
/

CREATE INDEX FK_MAN02315 ON TRAB_RASTREIAINSUMOOP (NRRASTREIA ASC, NRSEQRASTREIAINS ASC, CDFILIAL ASC, CDPRODUTO, NRLOTEESTQ ASC, NRSUBLOTE ASC)
/

CREATE INDEX FK_MAN02307 ON TRAB_RASTREIAINSUMOOP (CDFILIAL ASC, CDPRODUTO ASC, NRLOTEESTQ ASC, NRSUBLOTE ASC)
/

CREATE INDEX FK_MAN02308 ON TRAB_RASTREIAINSUMOOP (CDFILIAL ASC, NRORDPROD ASC, NRSUBORDPROD ASC)
/
                        
-- ============================================================
--   TABLE: TRAB_ITRASTREIAINSOP
-- ============================================================
CREATE TABLE TRAB_ITRASTREIAINSOP
(
    NRRASTREIA         VARCHAR2(10) NOT NULL,
    NRSEQRASTREIAINS   INTEGER      NOT NULL,
    CDFILIAL           VARCHAR2(04) NOT NULL,
    CDPRODUTO          VARCHAR2(10) NOT NULL,
    NRLOTEESTQ         VARCHAR2(20) NOT NULL,
    NRSUBLOTE          VARCHAR2(04) NOT NULL,
    NRSEQITRASTREIAINS INTEGER      NOT NULL,
    CDPRODINSUMO       VARCHAR2(10) NULL,
    NRLOTEINSUMO       VARCHAR2(20) NULL,
    NRSUBLOTEINS       VARCHAR2(04) NULL,
    QTUTILIZADA        FLOAT        NULL,
    CDALMOXARIFE       VARCHAR2(06) NULL,
    CDLOCALESTOQ       VARCHAR2(06) NULL,
   NRORG                       NUMBER DEFAULT 	 SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_TRAB_ITRASINSOP CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER             DEFAULT 1 NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER             DEFAULT 1 NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1',
    CONSTRAINT PK_ITRASTREIAINSOP PRIMARY KEY (NRRASTREIA, NRSEQRASTREIAINS, NRSEQITRASTREIAINS)
)
/

CREATE INDEX FK_MAN02309 ON TRAB_ITRASTREIAINSOP (NRRASTREIA ASC, NRSEQRASTREIAINS ASC)
/

ALTER TABLE TRAB_ITRASTREIAINSOP
    ADD CONSTRAINT FKMAN02309RASTREIO FOREIGN KEY  (NRRASTREIA, NRSEQRASTREIAINS)
       REFERENCES TRAB_RASTREIAINSUMOOP (NRRASTREIA, NRSEQRASTREIAINS) ON DELETE CASCADE
/

CREATE INDEX FK_MAN02310 ON TRAB_ITRASTREIAINSOP (NRRASTREIA ASC, NRSEQRASTREIAINS ASC, NRSEQITRASTREIAINS ASC,  CDFILIAL ASC, CDPRODUTO ASC, NRLOTEESTQ ASC, NRSUBLOTE ASC)
/

CREATE INDEX FK_MAN02311 ON TRAB_ITRASTREIAINSOP (CDFILIAL ASC, CDPRODUTO ASC, NRLOTEESTQ ASC, NRSUBLOTE ASC)
/

CREATE INDEX FK_MAN02312 ON TRAB_ITRASTREIAINSOP (NRRASTREIA ASC, NRSEQRASTREIAINS ASC, NRSEQITRASTREIAINS ASC, CDPRODINSUMO ASC)
/

CREATE INDEX FK_MAN02313 ON TRAB_ITRASTREIAINSOP (NRRASTREIA ASC, NRSEQRASTREIAINS ASC, NRSEQITRASTREIAINS ASC, NRLOTEINSUMO ASC, NRSUBLOTEINS ASC)
/

-- ============================================================
--   TABLE: TRAB_ESTOQUEINSOP
-- ============================================================

CREATE TABLE TRAB_ESTOQUEINSOP
(
    NRRASTREIA         VARCHAR2(10) NOT NULL,
    NRSEQRASTREIAINS   INTEGER      NOT NULL,
    CDFILIAL           VARCHAR2(04) NOT NULL,
    CDPRODUTO          VARCHAR2(10) NOT NULL,
    NRLOTEESTQ         VARCHAR2(20) NOT NULL,
    NRSUBLOTE          VARCHAR2(04) NOT NULL,
    NRSEQITRASTREIAINS INTEGER      NOT NULL,
    NRSEQESTOQINS      INTEGER      NOT NULL,
    NRLANCESTQ         VARCHAR2(10) NULL,
    CDALMOXARIFE       VARCHAR2(06) NULL,
    CDLOCALESTOQ       VARCHAR2(06) NULL,
    NRORDPROD          VARCHAR2(10) NULL,
    NRSUBORDPROD       VARCHAR2(01) NULL,
    DTCONCLUSAO        DATE         NULL,
    NRLANCTONF         VARCHAR2(10) NULL,
    DTEMISSAO          DATE         NULL,
    NRNOTAFISC         VARCHAR2(20) NULL,
    VRNOTAFISC         FLOAT        NULL,
    DSLANCESTIT        VARCHAR2(60) NULL,
    DTLANCMOVI         DATE         NULL,
    IDTIPOMOVI         VARCHAR2(01) NULL,
   NRORG                       NUMBER DEFAULT 	 SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
   IDATIVO                    VARCHAR2(1)     DEFAULT 'S' NOT NULL
      CONSTRAINT CKC_IDATIVO_TRAB_ESTINSOP CHECK (IDATIVO IN ('S','N')),
   DTINCLUSAO             DATE                   NULL,
   DTULTATU                  DATE                  NULL,
   NRORGINCLUSAO      NUMBER             DEFAULT 1 NULL,
   CDOPERINCLUSAO    VARCHAR2(12)  DEFAULT '1' NULL,
   NRORGULTATU          NUMBER             DEFAULT 1 NULL,
   CDOPERULTATU        VARCHAR2(12)  DEFAULT '1',
    CONSTRAINT PK_ESTOQUEINSOP PRIMARY KEY (NRRASTREIA, NRSEQRASTREIAINS, NRSEQITRASTREIAINS, NRSEQESTOQINS)
)
/

CREATE INDEX FK_MAN02314 ON TRAB_ESTOQUEINSOP (NRRASTREIA ASC, NRSEQRASTREIAINS ASC, NRSEQITRASTREIAINS ASC)
/

ALTER TABLE TRAB_ESTOQUEINSOP
    ADD CONSTRAINT FKMAN02314RASTREIO FOREIGN KEY  (NRRASTREIA, NRSEQRASTREIAINS, NRSEQITRASTREIAINS)
       REFERENCES TRAB_ITRASTREIAINSOP (NRRASTREIA, NRSEQRASTREIAINS, NRSEQITRASTREIAINS) ON DELETE CASCADE
/