
drop table interes;
drop table posliq;
drop table compliq;
drop table comprop;
drop table liqui;
drop table auxint;
drop table compliquid;
drop table cargos;
drop table liquidacion;



CREATE TABLE cargos (
    car_id integer NOT NULL,
    car_codemp integer DEFAULT 0 NOT NULL,
    car_idptov integer,
    car_division integer,
    car_centro integer,
    car_codigo integer,
    car_nombre character varying(340),
    car_rubcar integer,
    car_srubcar integer,
    car_genint integer DEFAULT 1 NOT NULL,
    car_interes numeric(15,5) DEFAULT 1,
    car_precio numeric(15,5) DEFAULT 0,
    car_tasa numeric(15,5) DEFAULT 0,
    car_estado integer DEFAULT 1,
    car_funcion text,
    car_leyen text
);


ALTER TABLE public.cargos OWNER TO sanjor;

CREATE SEQUENCE cargos_car_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public.cargos_car_id_seq OWNER TO sanjor;
ALTER SEQUENCE cargos_car_id_seq OWNED BY cargos.car_id;
ALTER TABLE ONLY cargos ALTER COLUMN car_id SET DEFAULT nextval('cargos_car_id_seq'::regclass);
ALTER TABLE ONLY cargos ADD CONSTRAINT cargos_pkey PRIMARY KEY (car_id);
ALTER TABLE ONLY cargos ADD CONSTRAINT "$1" FOREIGN KEY (car_codemp) REFERENCES empresa(emp_codigo);
CREATE INDEX idx_cargos_1 ON cargos USING btree (car_codemp, car_idptov, car_division, car_centro, car_codigo);
CREATE INDEX idx_cargos_2 ON cargos USING btree (car_codemp, car_idptov, car_codigo);
CREATE INDEX idx_cargos_3 ON cargos USING btree (car_codemp, car_idptov, car_rubcar, car_srubcar);

REVOKE ALL ON TABLE cargos FROM PUBLIC;
REVOKE ALL ON TABLE cargos FROM sanjor;
GRANT ALL ON TABLE cargos TO sanjor;
GRANT ALL ON TABLE cargos TO nobody;


--
-- Name: cargos_car_id_seq; Type: ACL; Schema: public; Owner: sanjor
--

REVOKE ALL ON SEQUENCE cargos_car_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE cargos_car_id_seq FROM sanjor;
GRANT ALL ON SEQUENCE cargos_car_id_seq TO sanjor;
GRANT UPDATE ON SEQUENCE cargos_car_id_seq TO nobody;



CREATE TABLE liquidacion (
    l_id serial,
    l_codemp integer NOT NULL,
    l_idptov integer DEFAULT 0 NOT NULL,
    l_division integer,
    l_centro integer DEFAULT 0 NOT NULL,
    l_anio integer NOT NULL,
    l_mes integer NOT NULL,
    l_perim integer NOT NULL,                -- Mes de liquidado
    l_peria integer NOT NULL,                -- Anio Liquidado
    l_feccie date NOT NULL,
    l_fecvto date NOT NULL,
    l_comprob integer NOT NULL,
    l_numero integer NOT NULL,
    l_estado integer DEFAULT 1 NOT NULL,
    l_fecreg timestamp without time zone DEFAULT now() NOT NULL,
    l_operador integer NOT NULL
);


ALTER TABLE public.liquidacion OWNER TO sanjor;
ALTER TABLE ONLY liquidacion ADD CONSTRAINT liquidacion_pkey PRIMARY KEY (l_id);
ALTER TABLE ONLY liquidacion ADD CONSTRAINT "$1" FOREIGN KEY (l_codemp) REFERENCES empresa(emp_codigo) MATCH FULL;
ALTER TABLE ONLY liquidacion ADD CONSTRAINT "$2" FOREIGN KEY (l_idptov) REFERENCES ptovta(ptv_id) MATCH FULL;
ALTER TABLE ONLY liquidacion ADD CONSTRAINT "$3" FOREIGN KEY (l_centro) REFERENCES centroc(cco_id) MATCH FULL;
ALTER TABLE ONLY liquidacion ADD CONSTRAINT "$4" FOREIGN KEY (l_division) REFERENCES division(div_id) MATCH FULL;

CREATE INDEX idx_liquidacion_0 ON liquidacion USING btree (l_codemp, l_idptov, l_centro, l_division, l_anio, l_mes);
CREATE INDEX idx_liquidacion_1 ON liquidacion USING btree (l_codemp, l_idptov, l_anio, l_mes);
CREATE INDEX idx_liquidacion_2 ON liquidacion USING btree (l_idptov, l_anio, l_mes);

REVOKE ALL ON TABLE liquidacion FROM PUBLIC;
REVOKE ALL ON TABLE liquidacion FROM sanjor;
GRANT ALL ON TABLE liquidacion TO sanjor;
GRANT ALL ON TABLE liquidacion TO nobody;




CREATE TABLE compliquid (
    cl_id serial,
    cl_idliq integer NOT NULL,
    cl_rubcar integer,
    cl_cargo integer,
    cl_porc integer DEFAULT 1 NOT NULL,
    cl_leyen character varying(240),
    cl_importe numeric(15,2) DEFAULT 0,
    cl_estado integer DEFAULT 1 NOT NULL
);


ALTER TABLE public.compliquid OWNER TO sanjor;
ALTER TABLE ONLY compliquid ADD CONSTRAINT "$1" FOREIGN KEY (cl_idliq) REFERENCES liquidacion(l_id) MATCH FULL;

CREATE INDEX idx_compliquid_1 ON compliquid USING btree (cl_idliq);

REVOKE ALL ON TABLE compliquid FROM PUBLIC;
REVOKE ALL ON TABLE compliquid FROM sanjor;
GRANT ALL ON TABLE compliquid TO sanjor;
GRANT ALL ON TABLE compliquid TO nobody;




CREATE TABLE liqui (
    lq_id serial,
    lq_idliq integer NOT NULL,
    lq_idcopr integer NOT NULL,
    lq_rubcar integer,
    lq_cargo integer,
    lq_canti numeric(15,5) DEFAULT 1,
    lq_precio numeric(15,5) DEFAULT 1,
    lq_importe numeric(15,2) DEFAULT 0,
    lq_leyen text
);


ALTER TABLE public.liqui OWNER TO sanjor;
ALTER TABLE ONLY liqui ADD CONSTRAINT "$1" FOREIGN KEY (lq_idliq) REFERENCES liquidacion(l_id) MATCH FULL;
ALTER TABLE ONLY liqui ADD CONSTRAINT "$2" FOREIGN KEY (lq_idcopr) REFERENCES coprop(cop_id) MATCH FULL;
ALTER TABLE ONLY liqui ADD CONSTRAINT "$3" FOREIGN KEY (lq_cargo) REFERENCES cargos(car_id);
CREATE INDEX idx_liqui_1 ON liqui USING btree (lq_idliq, lq_idcopr);

REVOKE ALL ON TABLE liqui FROM PUBLIC;
REVOKE ALL ON TABLE liqui FROM sanjor;
GRANT ALL ON TABLE liqui TO sanjor;
GRANT ALL ON TABLE liqui TO nobody;




CREATE TABLE compliq (
    clq_id integer NOT NULL,
    clq_codemp integer NOT NULL,
    clq_idptov integer NOT NULL,
    clq_division integer,
    clq_centro integer,
    clq_idcopr integer NOT NULL,
    clq_fecha date DEFAULT date(now()),
    clq_fecvto date DEFAULT date(now()),
    clq_anio integer DEFAULT date_part('Y'::text, date(now())) NOT NULL,
    clq_mes integer DEFAULT date_part('month'::text, date(now())) NOT NULL,
    clq_comprob integer,
    clq_numero integer,
    clq_idliq integer,
    clq_rubcar integer NOT NULL,
    clq_cargo integer NOT NULL,
    clq_canti numeric(15,5) DEFAULT 1,
    clq_precio numeric(15,5) DEFAULT 0,
    clq_importe numeric(15,2) DEFAULT 0,
    clq_interes numeric(15,5) DEFAULT 1,
    clq_estado integer DEFAULT 1,
    clq_fecreg timestamp without time zone DEFAULT now() NOT NULL,
    clq_operad integer,
    clq_leyen text
);

ALTER TABLE public.compliq OWNER TO sanjor;

CREATE SEQUENCE compliq_clq_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.compliq_clq_id_seq OWNER TO sanjor;
ALTER SEQUENCE compliq_clq_id_seq OWNED BY compliq.clq_id;
ALTER TABLE ONLY compliq ALTER COLUMN clq_id SET DEFAULT nextval('compliq_clq_id_seq'::regclass);
ALTER TABLE ONLY compliq ADD CONSTRAINT compliq_pkey PRIMARY KEY (clq_id);

CREATE INDEX idx_compliq_2 ON compliq USING btree (clq_codemp, clq_idptov, clq_comprob, clq_numero);
CREATE INDEX idx_compliq_3 ON compliq USING btree (clq_codemp, clq_idptov, clq_anio, clq_mes);
CREATE INDEX idx_compliq_4 ON compliq USING btree (clq_codemp, clq_idptov, clq_anio, clq_mes, clq_rubcar, clq_cargo);

ALTER TABLE ONLY compliq ADD CONSTRAINT "$1" FOREIGN KEY (clq_codemp) REFERENCES empresa(emp_codigo);
ALTER TABLE ONLY compliq ADD CONSTRAINT "$2" FOREIGN KEY (clq_idptov) REFERENCES ptovta(ptv_id);
ALTER TABLE ONLY compliq ADD CONSTRAINT "$3" FOREIGN KEY (clq_division) REFERENCES division(div_id) MATCH FULL;
ALTER TABLE ONLY compliq ADD CONSTRAINT "$4" FOREIGN KEY (clq_centro) REFERENCES centroc(cco_id) MATCH FULL;
ALTER TABLE ONLY compliq ADD CONSTRAINT "$5" FOREIGN KEY (clq_comprob) REFERENCES comprob(comp_id);
ALTER TABLE ONLY compliq ADD CONSTRAINT "$6" FOREIGN KEY (clq_cargo) REFERENCES cargos(car_id);
ALTER TABLE ONLY compliq ADD CONSTRAINT "$7" FOREIGN KEY (clq_idcopr) REFERENCES coprop(cop_id) MATCH FULL;
ALTER TABLE ONLY compliq ADD CONSTRAINT "$8" FOREIGN KEY (clq_cargo) REFERENCES cargos(car_id);

REVOKE ALL ON TABLE compliq FROM PUBLIC;
REVOKE ALL ON TABLE compliq FROM sanjor;
GRANT ALL ON TABLE compliq TO sanjor;
GRANT ALL ON TABLE compliq TO nobody;

REVOKE ALL ON SEQUENCE compliq_clq_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE compliq_clq_id_seq FROM sanjor;
GRANT ALL ON SEQUENCE compliq_clq_id_seq TO sanjor;
GRANT UPDATE ON SEQUENCE compliq_clq_id_seq TO nobody;



CREATE TABLE comprop (
    cmpr_id integer NOT NULL,
    cmpr_codemp integer NOT NULL,
    cmpr_idptov integer NOT NULL,
    cmpr_division integer,
    cmpr_centro integer,
    cmpr_idcopr integer NOT NULL,
    cmpr_rubcar integer NOT NULL,
    cmpr_cargo integer NOT NULL,
    cmpr_canti numeric(15,5) DEFAULT 1,
    cmpr_precio numeric(15,5) DEFAULT 0,
    cmpr_importe numeric(15,2) DEFAULT 0,
    cmpr_estado integer DEFAULT 1,
    cmpr_fecalt date NOT NULL DEFAULT date(now()),
    cmpr_fecbaj date,
    clq_leyen text
);


ALTER TABLE public.comprop OWNER TO sanjor;
CREATE SEQUENCE comprop_cmpr_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER TABLE public.comprop_cmpr_id_seq OWNER TO sanjor;
ALTER SEQUENCE comprop_cmpr_id_seq OWNED BY comprop.cmpr_id;

ALTER TABLE ONLY comprop ALTER COLUMN cmpr_id SET DEFAULT nextval('comprop_cmpr_id_seq'::regclass);
ALTER TABLE ONLY comprop ADD CONSTRAINT comprop_pkey PRIMARY KEY (cmpr_id);
CREATE INDEX idx_comprop_1 ON comprop USING btree (cmpr_centro, cmpr_idcopr);
CREATE INDEX idx_comprop_2 ON comprop USING btree (cmpr_codemp, cmpr_idptov, cmpr_idcopr);
CREATE INDEX idx_comprop_3 ON comprop USING btree (cmpr_codemp, cmpr_idptov, cmpr_idcopr, cmpr_rubcar, cmpr_cargo);
ALTER TABLE ONLY comprop ADD CONSTRAINT "$1" FOREIGN KEY (cmpr_codemp) REFERENCES empresa(emp_codigo);
ALTER TABLE ONLY comprop ADD CONSTRAINT "$2" FOREIGN KEY (cmpr_idptov) REFERENCES ptovta(ptv_id);
ALTER TABLE ONLY comprop ADD CONSTRAINT "$3" FOREIGN KEY (cmpr_division) REFERENCES division(div_id) MATCH FULL;
ALTER TABLE ONLY comprop ADD CONSTRAINT "$4" FOREIGN KEY (cmpr_centro) REFERENCES centroc(cco_id) MATCH FULL;
ALTER TABLE ONLY comprop ADD CONSTRAINT "$5" FOREIGN KEY (cmpr_cargo) REFERENCES cargos(car_id);


REVOKE ALL ON TABLE comprop FROM PUBLIC;
REVOKE ALL ON TABLE comprop FROM sanjor;
GRANT ALL ON TABLE comprop TO sanjor;
GRANT ALL ON TABLE comprop TO nobody;

REVOKE ALL ON SEQUENCE comprop_cmpr_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE comprop_cmpr_id_seq FROM sanjor;
GRANT ALL ON SEQUENCE comprop_cmpr_id_seq TO sanjor;
GRANT UPDATE ON SEQUENCE comprop_cmpr_id_seq TO nobody;




CREATE TABLE posliq (
    plq_id integer NOT NULL,
    plq_codemp integer NOT NULL,
    plq_idptov integer NOT NULL,
    plq_division integer,
    plq_centro integer,
    plq_idcopr integer NOT NULL,
    plq_fecha date DEFAULT date(now()),
    plq_anio smallint DEFAULT date_part('Y'::text, date(now())) NOT NULL,
    plq_mes smallint DEFAULT date_part('month'::text, date(now())) NOT NULL,
    plq_comprob integer,
    plq_numero integer,
    plq_rubcar integer NOT NULL,
    plq_cargo integer NOT NULL,
    plq_importe numeric(15,2) DEFAULT 0,
    plq_estado integer DEFAULT 1,
    plq_fecreg timestamp without time zone DEFAULT now() NOT NULL,
    plq_operad integer,
    plq_leyen text
);


ALTER TABLE public.posliq OWNER TO sanjor;

CREATE SEQUENCE posliq_plq_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.posliq_plq_id_seq OWNER TO sanjor;
ALTER SEQUENCE posliq_plq_id_seq OWNED BY posliq.plq_id;
ALTER TABLE ONLY posliq ALTER COLUMN plq_id SET DEFAULT nextval('posliq_plq_id_seq'::regclass);

ALTER TABLE ONLY posliq ADD CONSTRAINT posliq_pkey PRIMARY KEY (plq_id);
CREATE INDEX idx_posliq_2 ON posliq USING btree (plq_codemp, plq_centro, plq_comprob, plq_numero);
CREATE INDEX idx_posliq_3 ON posliq USING btree (plq_codemp, plq_centro, plq_anio, plq_mes);
CREATE INDEX idx_posliq_4 ON posliq USING btree (plq_codemp, plq_idptov, plq_anio, plq_mes, plq_rubcar, plq_cargo);

ALTER TABLE ONLY posliq ADD CONSTRAINT "$1" FOREIGN KEY (plq_codemp) REFERENCES empresa(emp_codigo);
ALTER TABLE ONLY posliq ADD CONSTRAINT "$2" FOREIGN KEY (plq_idptov) REFERENCES ptovta(ptv_id);
ALTER TABLE ONLY posliq ADD CONSTRAINT "$3" FOREIGN KEY (plq_comprob) REFERENCES comprob(comp_id);
ALTER TABLE ONLY posliq ADD CONSTRAINT "$4" FOREIGN KEY (plq_cargo) REFERENCES cargos(car_id);
ALTER TABLE ONLY posliq ADD CONSTRAINT "$5" FOREIGN KEY (plq_idcopr) REFERENCES coprop(cop_id) MATCH FULL;

REVOKE ALL ON TABLE posliq FROM PUBLIC;
REVOKE ALL ON TABLE posliq FROM sanjor;
GRANT ALL ON TABLE posliq TO sanjor;
GRANT ALL ON TABLE posliq TO nobody;

REVOKE ALL ON SEQUENCE posliq_plq_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE posliq_plq_id_seq FROM sanjor;
GRANT ALL ON SEQUENCE posliq_plq_id_seq TO sanjor;
GRANT UPDATE ON SEQUENCE posliq_plq_id_seq TO nobody;








CREATE TABLE auxint (
    ai_id serial,
    ai_idliq integer NOT NULL,
    ai_idcopr integer NOT NULL,
    ai_impgi  numeric(15,2) DEFAULT 0,     -- Saldo que genera intereses
    ai_impngi numeric(15,2) DEFAULT 0,     -- Saldo que no genera intereses
    ai_intacu  numeric(15,2) DEFAULT 0     -- Interes acumulado
);


ALTER TABLE public.auxint OWNER TO sanjor;
ALTER TABLE ONLY auxint ADD CONSTRAINT "$1" FOREIGN KEY (ai_idliq) REFERENCES liquidacion(l_id) MATCH FULL;
ALTER TABLE ONLY auxint ADD CONSTRAINT "$2" FOREIGN KEY (ai_idcopr) REFERENCES coprop(cop_id) MATCH FULL;
CREATE INDEX idx_auxint_1 ON auxint USING btree (ai_idliq, ai_idcopr);

REVOKE ALL ON TABLE auxint FROM PUBLIC;
REVOKE ALL ON TABLE auxint FROM sanjor;
GRANT ALL ON TABLE auxint TO sanjor;
GRANT ALL ON TABLE auxint TO nobody;



CREATE TABLE interes (
    int_codemp integer DEFAULT 0 NOT NULL,
    int_idptov integer DEFAULT 0 NOT NULL,
    int_centro integer,
    int_division integer,
    int_fecha date NOT NULL,
    int_valor numeric(15,5) DEFAULT 0
);
ALTER TABLE public.interes OWNER TO sanjor;
ALTER TABLE ONLY interes ADD CONSTRAINT interes_pkey PRIMARY KEY (int_codemp, int_fecha);
ALTER TABLE ONLY interes ADD CONSTRAINT interes_int_codemp_fkey FOREIGN KEY (int_codemp) REFERENCES empresa(emp_codigo) MATCH FULL;
ALTER TABLE ONLY interes ADD CONSTRAINT interes_int_idptov_fkey FOREIGN KEY (int_idptov) REFERENCES ptovta(ptv_id) MATCH FULL;

REVOKE ALL ON TABLE interes FROM PUBLIC;
REVOKE ALL ON TABLE interes FROM sanjor;
GRANT ALL ON TABLE interes TO sanjor;
GRANT ALL ON TABLE interes TO nobody;






