ALTER TABLE INFADICPROD ADD NRREGISTROMS VARCHAR2(15)
/

ALTER TABLE INFADICPROD ADD NRPRAZOVALPR FLOAT
/

ALTER TABLE INFADICPROD ADD IDTPPRAZOVAL VARCHAR2(1) DEFAULT 'M' NULL    
        CONSTRAINT CKC_IDTPPRAZOVAL CHECK (IDTPPRAZOVAL IS NULL OR (IDTPPRAZOVAL IN ('D','H','M')))
/

ALTER TABLE INFADICPROD ADD IDTPUSOMEDPR VARCHAR2(1) NULL    
        CONSTRAINT CKC_IDTPUSOMEDPR CHECK (IDTPUSOMEDPR IS NULL OR (IDTPUSOMEDPR IN ('1','2')))
/

ALTER TABLE INFADICPROD ADD IDCLASSEMEDPR VARCHAR2(1) NULL    
        CONSTRAINT CKC_IDCLASSEMEDPR CHECK (IDCLASSEMEDPR IS NULL OR (IDCLASSEMEDPR IN ('1','2')))
/

ALTER TABLE INFADICPROD ADD IDUSOPROLONG VARCHAR2(1) NULL    
        CONSTRAINT CKC_IDUSOPROLONG CHECK (IDUSOPROLONG IS NULL OR (IDUSOPROLONG IN ('S','N', 'A')))
/

ALTER TABLE INFADICPROD ADD IDUNFARMAMED VARCHAR2(1) NULL    
        CONSTRAINT CKC_IDUNFARMAMED CHECK (IDUNFARMAMED IS NULL OR (IDUNFARMAMED IN ('1','2', '3', '4')))
/

CREATE TABLE DADOSPACPED
(
    CDFILIAL            VARCHAR2(4)             NOT NULL,
    NRPEDIDO            VARCHAR2(10)           NOT NULL,
    NMPACIENTE        	VARCHAR2(200)         	NULL    ,
    IDCOMPRADOR    		VARCHAR2(01)           DEFAULT 'N' NOT NULL
         CONSTRAINT CKC_IDCOMPRADOR_DADOSPACPED CHECK (IDCOMPRADOR IN ('S','N'))  	,
    DTNACISCPAC         DATE                   NULL    ,
    IDGENEROPAC         VARCHAR2(01)           NULL
         CONSTRAINT CKC_IDGENEROPAC_DADOSPACPED CHECK (IDGENEROPAC IS NULL OR (IDGENEROPAC IN ('F','M'))),
    IDCIDPACIENTE      	VARCHAR2(50)           NULL    ,
    NRDOCCOMPRA     	VARCHAR2(50)           NULL    ,
    IDTPDOCCOMPRA	    VARCHAR2(01)           NULL
         CONSTRAINT CKC_IDTPDOCCOMPRA_DADOSPACPED CHECK (IDTPDOCCOMPRA IS NULL OR (IDTPDOCCOMPRA IN ('1','2', '3', '4'))),
    CDORGAOEXPE         VARCHAR2(10)           NULL    ,  
    CDPAISCOMP          VARCHAR2(04)           NULL    ,    
    SGESTADOCOMP        VARCHAR2(02)           NULL    ,    
    NMPRESCRITOR        VARCHAR2(200)          NULL    ,
    NRREGISTROP         VARCHAR2(50)           NULL    ,
    IDTPCONSPRE	        VARCHAR2(01)           NULL
      CONSTRAINT CKC_IDTPCONSPRE_DADOSPACPED CHECK (IDTPCONSPRE IS NULL OR (IDTPCONSPRE IN ('1','2', '3', '4'))), 
    CDPAISPRESC         VARCHAR2(04)           NULL    ,    
    SGESTADOPRESC       VARCHAR2(02)           NULL    ,    
    DTPRESCRICAO        DATE                   NULL    ,
    IDTIPORECEITA       VARCHAR2(01)           NULL
      CONSTRAINT CKC_IDTIPORECEITA_DADOSPACPED CHECK (IDTIPORECEITA IS NULL OR (IDTIPORECEITA IN ('1','2', '3', '4'))),
	NRORG 				NUMBER DEFAULT SYS_CONTEXT('nrorg_ctx','nrorg') NOT NULL,
	IDATIVO 			VARCHAR2(1) DEFAULT 'S' NOT NULL,
		CONSTRAINT CKC_IDATIVO_DADOSPACPED 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',
		CONSTRAINT PK_DADOSPACPED PRIMARY KEY (CDFILIAL, NRPEDIDO)
)
/

CREATE INDEX PED00437_FK ON DADOSPACPED (CDFILIAL ASC, NRPEDIDO ASC)
/

ALTER TABLE DADOSPACPED
    ADD CONSTRAINT FKPED00437PEDIDO FOREIGN KEY (CDFILIAL, NRPEDIDO)
       REFERENCES PEDIDO (CDFILIAL, NRPEDIDO) ON DELETE CASCADE
/

CREATE INDEX PED00438_FK ON DADOSPACPED (CDPAISPRESC ASC, SGESTADOCOMP ASC)
/

ALTER TABLE DADOSPACPED
    ADD CONSTRAINT FKPED00438ESTADO FOREIGN KEY  (CDPAISPRESC, SGESTADOCOMP)
       REFERENCES ESTADO (CDPAIS, SGESTADO)
/

CREATE INDEX PED00439_FK ON DADOSPACPED (CDPAISCOMP ASC,SGESTADOPRESC ASC)
/

ALTER TABLE DADOSPACPED
    ADD CONSTRAINT FKPED00439ESTADO FOREIGN KEY  (CDPAISCOMP,SGESTADOPRESC)
       REFERENCES ESTADO (CDPAIS, SGESTADO)