/*==============================================================*/
/* DBMS name:      ORACLE Version 9i                            */
/* Created on:     30/01/2008 01:13:46 p.m.                     */
/*==============================================================*/


/*==============================================================*/
/* Table: CREAFIL                                               */
/*==============================================================*/
CREATE TABLE CREAFIL  (
   CIA                  CHAR(3)                         NOT NULL,
   AFILIADO             VARCHAR2(10)                    NOT NULL,
   PROVEEDOR            VARCHAR2(25)                    NOT NULL,
   DESCRIPCION          VARCHAR2(40)                    NOT NULL,
   USUARIO              VARCHAR2(30)                    NOT NULL,
   FECHA                DATE                            NOT NULL,
   AUTORIZACION         CHAR(1)                         NOT NULL
      CONSTRAINT CREAFIL_AUTORIZACI_CK CHECK (AUTORIZACION IN ('S','N')),
   CREDITO              CHAR(1)                         NOT NULL
      CONSTRAINT CREAFIL_CREDITO_CK CHECK (CREDITO IN ('S','N')),
   COMISION             NUMBER(5,2)                     NOT NULL
      CONSTRAINT CREAFIL_COMISION_CK CHECK (COMISION >= 0),
   MAXIMO               NUMBER(14,2)                    NOT NULL
      CONSTRAINT CREAFIL_MAXIMO_CK CHECK (MAXIMO >= 0),
   CONSVENTA            NUMBER(10)                      NOT NULL,
   CONSANULA            NUMBER(10)                      NOT NULL,
   CONSTRAINT CREAFIL_PK PRIMARY KEY (CIA, AFILIADO)
)
INITRANS 1
STORAGE
(
    INITIAL 10K
    NEXT 10K
);

/*==============================================================*/
/* Table: CREANUL                                               */
/*==============================================================*/
CREATE TABLE CREANUL  (
   CIA                  CHAR(3)                         NOT NULL,
   AFILIADO             VARCHAR2(10)                    NOT NULL,
   CONSECUTIVO          NUMBER(10)                      NOT NULL,
   VENTA                NUMBER(10)                      NOT NULL,
   CLIENTE              VARCHAR2(25)                    NOT NULL,
   FECHA                DATE                            NOT NULL,
   CONSTRAINT CREANUL_PK PRIMARY KEY (CIA, AFILIADO, CONSECUTIVO)
)
INITRANS 1
STORAGE
(
    INITIAL 10K
    NEXT 10K
);

/*==============================================================*/
/* Index: CREOPER_ANUL_FK2                                      */
/*==============================================================*/
CREATE INDEX CREOPER_ANUL_FK2 ON CREANUL (
   CIA ASC,
   AFILIADO ASC,
   VENTA ASC
);

/*==============================================================*/
/* Table: CREOPER                                               */
/*==============================================================*/
CREATE TABLE CREOPER  (
   CIA                  CHAR(3)                         NOT NULL,
   AFILIADO             VARCHAR2(10)                    NOT NULL,
   CONSECUTIVO          NUMBER(10)                      NOT NULL,
   SUCURSAL             CHAR(3)                         NOT NULL,
   LINEA                CHAR(3)                         NOT NULL,
   CLIENTE              VARCHAR2(25)                    NOT NULL,
   PERSONA              VARCHAR2(25),
   REFERENCIA           VARCHAR2(10),
   FECHA                DATE,
   AUTORIZACION         NUMBER(10),
   MONTO                NUMBER(14,2)                    NOT NULL
      CONSTRAINT CREOPER_MONTO_CK CHECK (MONTO >= 0),
   COMISION             NUMBER(5,2)                     NOT NULL
      CONSTRAINT CREOPER_COMISION_CK CHECK (COMISION >= 0),
   TIPOCOBRO            CHAR(3),
   DOCCOBRO             NUMBER(10),
   TIPOPAGO             CHAR(3),
   DOCPAGO              NUMBER(10),
   ESTADO               CHAR(1)                         NOT NULL
      CONSTRAINT CREOPER_ESTADO_CK CHECK (ESTADO IN ('P','A','N')),
   CONSTRAINT CREOPER_PK PRIMARY KEY (CIA, AFILIADO, CONSECUTIVO)
)
INITRANS 1
STORAGE
(
    INITIAL 10K
    NEXT 10K
);

/*==============================================================*/
/* Index: CREPARAM_OPER_FK2                                     */
/*==============================================================*/
CREATE INDEX CREPARAM_OPER_FK2 ON CREOPER (
   CIA ASC,
   LINEA ASC
);

/*==============================================================*/
/* Table: CREPARAM                                              */
/*==============================================================*/
CREATE TABLE CREPARAM  (
   CIA                  CHAR(3)                         NOT NULL,
   LINEA                CHAR(3)                         NOT NULL,
   SUCURSAL             CHAR(3)                         NOT NULL,
   AGENTECOBRO          CHAR(3)                         NOT NULL,
   TIPOCOBRO            CHAR(3)                         NOT NULL,
   TIPOPAGO             CHAR(3)                         NOT NULL,
   MAXIMO               NUMBER(14,2)                    NOT NULL
      CONSTRAINT CREPARAM_MAXIMO_CK CHECK (MAXIMO >= 0),
   CONSTRAINT CREPARAM_PK PRIMARY KEY (CIA, LINEA)
)
INITRANS 1
STORAGE
(
    INITIAL 10K
    NEXT 10K
);

ALTER TABLE CREANUL
   ADD CONSTRAINT CREAFIL_ANUL_FK FOREIGN KEY (CIA, AFILIADO)
      REFERENCES CREAFIL (CIA, AFILIADO);

ALTER TABLE CREANUL
   ADD CONSTRAINT CREOPER_ANUL_FK FOREIGN KEY (CIA, AFILIADO, VENTA)
      REFERENCES CREOPER (CIA, AFILIADO, CONSECUTIVO);

ALTER TABLE CREOPER
   ADD CONSTRAINT CREAFIL_OPER_FK FOREIGN KEY (CIA, AFILIADO)
      REFERENCES CREAFIL (CIA, AFILIADO);

ALTER TABLE CREOPER
   ADD CONSTRAINT CREPARAM_OPER_FK FOREIGN KEY (CIA, LINEA)
      REFERENCES CREPARAM (CIA, LINEA);

