GitLab

actualizacion para envio de email desde la agenda, notificacion de eventos y generacion de procesos asociada a la agenda.-

ajustes para envio de email desde proceso y actividades, se le agregar funcionalidad htmlareaEmail para la edicion;
funcionalidad para realizar cambio de nivel de jerarquias;
actualizacion para optimizacion del docman;

fuonc. para inactivar grupos;
formato basico y avanzado para adjuntar documentos;

scrip para cambios en la estructura de BD del sistema de Gestion Interna, tanto en la base principal como secundario.-
=========================
CORRER EN STP_BPM
=========================

CREATE INDEX doc_reference_system_id_idx
  ON doc_reference
  USING btree
  (system_id);

CREATE INDEX doc_reference_table_id_idx
  ON doc_reference
  USING btree
  (table_id);

CREATE INDEX doc_ref_context_table_id_system_id_idx
  ON doc_ref_context
  USING btree
  (table_id, system_id);

CREATE TABLE doc_application
(
  id serial NOT NULL,
  description character varying(60),
  code character varying,
  CONSTRAINT pk_doc_application PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE doc_application
  OWNER TO bpm;
GRANT ALL ON TABLE doc_application TO bpm;

CREATE TABLE doc_application_context
(
  id_application integer NOT NULL,
  id_context integer NOT NULL,
  CONSTRAINT pk_doc_application_context PRIMARY KEY (id_application, id_context)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE doc_application_context
  OWNER TO bpm;
GRANT ALL ON TABLE doc_application_context TO bpm;

CREATE TABLE org_hierarchy
(
  id serial NOT NULL,
  code integer NOT NULL,
  name character varying(200),
  parent_id integer,
  boss_id integer,
  CONSTRAINT org_hierarchy_pkey PRIMARY KEY (id ),
  CONSTRAINT org_hierarchy_boss_id_fkey FOREIGN KEY (boss_id)
      REFERENCES user_system (user_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT org_hierarchy_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES org_hierarchy (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT org_hierarchy_code_key UNIQUE (code )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE org_hierarchy
  OWNER TO bpm;

CREATE TABLE org_hierarchy_group
(
  org_id integer NOT NULL,
  user_group_id integer NOT NULL,
  CONSTRAINT org_hierarchy_group_pkey PRIMARY KEY (org_id , user_group_id ),
  CONSTRAINT fk_org_hierarchy_id FOREIGN KEY (org_id)
      REFERENCES org_hierarchy (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_user_group_user_group_id FOREIGN KEY (user_group_id)
      REFERENCES user_group (user_group_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE org_hierarchy_group
  OWNER TO bpm;

CREATE INDEX fki_org_hierarchy_id
  ON org_hierarchy_group
  USING btree
  (org_id );

CREATE INDEX fki_user_group_user_group_id
  ON org_hierarchy_group
  USING btree
  (user_group_id );

CREATE TABLE org_hierarchy_user
(
  org_id integer NOT NULL,
  user_id integer NOT NULL,
  CONSTRAINT org_hierarchy_user_pkey PRIMARY KEY (org_id , user_id ),
  CONSTRAINT org_hierarchy_user_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES org_hierarchy (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT org_hierarchy_user_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES user_system (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE org_hierarchy_user
  OWNER TO bpm;

CREATE TABLE org_hierarchy_type
(
  id serial NOT NULL,
  name character varying(200),
  CONSTRAINT org_hierarchy_type_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE org_hierarchy_type
  OWNER TO bpm;

CREATE
    TABLE user_director
    (
        user_dir_id serial,
        dir_code INTEGER NOT NULL,
        name CHARACTER VARYING(200),
        jefe_id INTEGER,
        CONSTRAINT pk_director_cd PRIMARY KEY (user_dir_id)
    );
ALTER TABLE user_director OWNER TO bpm;

CREATE TABLE actividad_jerarquia
(
  id bigserial NOT NULL,
  dir_id integer,
  area_id integer,
  sub_area_id integer,
  user_id integer,
  process_id integer,
  activity_id integer,
  from_act character varying,
  to_act character varying,
  sub_user_area_id integer,
  CONSTRAINT actividad_jerarquia_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE actividad_jerarquia OWNER TO bpm;

CREATE OR REPLACE VIEW user_director AS
 SELECT oh.id AS user_dir_id, oh.code AS dir_code,
	oh.name, oh.boss_id AS jefe_id
 FROM org_hierarchy oh, org_hierarchy_type type
 WHERE type.id = 1
	AND type.id = oh.type_id
	AND oh.parent_id is null;

CREATE OR REPLACE VIEW user_division AS
 SELECT oh.id AS user_div_id, oh.code AS div_code,
	oh.name, oh.boss_id AS jefe_id,
	oh.parent_id AS dir_id
 FROM org_hierarchy oh, org_hierarchy_type type
 WHERE type.id = 2
	AND type.id = oh.type_id;

CREATE OR REPLACE VIEW user_department AS
 SELECT oh.id AS user_dep_id, oh.code AS dep_code,
	oh.name, oh.boss_id AS jefe_id,
	oh.parent_id AS div_id, null AS branch_id
 FROM org_hierarchy oh, org_hierarchy_type type
 WHERE type.id = 3
	AND type.id = oh.type_id;

CREATE TABLE t_pt_cause_req_prod
(
  process_type_id integer NOT NULL,
  cause_id integer NOT NULL,
  requirement character varying(2000) NOT NULL,
  secuence integer NOT NULL,
  product_id integer NOT NULL,
  CONSTRAINT t_pt_cause_req_prod_pkey PRIMARY KEY (process_type_id, cause_id, requirement, secuence, product_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_pt_cause_req_prod OWNER TO bpm;

CREATE TABLE process_req_prod
(
  requirement character varying(200) NOT NULL,
  process_type_id integer NOT NULL,
  product_id integer NOT NULL,
  CONSTRAINT pk_process_req_prod PRIMARY KEY (process_type_id, requirement, product_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE process_req_prod OWNER TO bpm;
COMMENT ON TABLE process_req_prod IS 'contiene la relacion de tipo de proceso requisito y producto, un requisito puede estar asociado a uno o varios productos';

CREATE TABLE t_process_req_prod
(
  id_requirement integer NOT NULL,
  requirement character varying(2000) NOT NULL,
  process_type_id integer NOT NULL,
  product_id integer NOT NULL,
  CONSTRAINT pk_t_process_req_prod PRIMARY KEY (process_type_id, id_requirement, requirement, product_id)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE pt_cause_req_prod
(
  process_type_id integer NOT NULL,
  cause_id integer NOT NULL,
  requirement character varying(2000) NOT NULL,
  secuence integer NOT NULL,
  product_id integer NOT NULL,
  CONSTRAINT pt_cause_req_prod_pkey PRIMARY KEY (process_type_id, cause_id, requirement, secuence, product_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE pt_cause_req_prod OWNER TO bpm;

CREATE TABLE proc_product
(
  process_type_id integer NOT NULL,
  product_id integer NOT NULL,
  CONSTRAINT pk_proc_product PRIMARY KEY (process_type_id, product_id),
  CONSTRAINT fk_proc_product_process_type FOREIGN KEY (process_type_id)
      REFERENCES process_type (process_type_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_proc_product_product_id FOREIGN KEY (product_id)
      REFERENCES product (product_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE proc_product OWNER TO bpm;
COMMENT ON TABLE proc_product IS 'contiene la relacion mucho a mucho de tipo de proceso con producto';

CREATE TABLE t_proc_product
(
  process_type_id integer NOT NULL,
  product_id integer NOT NULL,
  CONSTRAINT pk_t_proc_product PRIMARY KEY (process_type_id, product_id),
  CONSTRAINT fk_t_proc_product_product_id FOREIGN KEY (product_id)
      REFERENCES product (product_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_t_proc_responsible_process_type FOREIGN KEY (process_type_id)
      REFERENCES t_process_type (process_type_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);

ALTER TABLE document ADD COLUMN restricted smallint NOT NULL DEFAULT 0;
ALTER TABLE document ADD COLUMN unrestricted smallint NOT NULL DEFAULT 0;
ALTER TABLE document ADD COLUMN status_doc character varying(1);

ALTER TABLE org_hierarchy ADD COLUMN type_id integer NOT NULL DEFAULT 0;
ALTER TABLE org_hierarchy ADD CONSTRAINT org_hierarchy_type_id_fkey FOREIGN KEY (type_id)
      REFERENCES org_hierarchy_type (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE org_hierarchy ADD COLUMN prg_code character varying(100);

ALTER TABLE user_director RENAME TO user_director_;

ALTER TABLE user_division ADD COLUMN dir_id integer;
ALTER TABLE user_division RENAME TO user_division_;

ALTER TABLE user_department RENAME TO user_department_;

ALTER TABLE process ADD COLUMN branch_id integer;
ALTER TABLE process ADD COLUMN c_nro_doc character varying(15);
ALTER TABLE process ADD COLUMN c_tipo_doc integer;
ALTER TABLE process ADD COLUMN c_pais_doc character(2);
ALTER TABLE process ADD COLUMN e_ruc character varying(15);
ALTER TABLE process ADD COLUMN e_tipo_doc integer;
ALTER TABLE process ADD COLUMN e_pais_doc character(2);
ALTER TABLE process ADD COLUMN zona integer;
ALTER TABLE process ADD COLUMN region character varying(15);
ALTER TABLE process ADD COLUMN product_id integer;
COMMENT ON COLUMN process.product_id IS 'el id del producto correspondiente al proceso';
ALTER TABLE process ADD COLUMN product_name character varying(100);
COMMENT ON COLUMN process.product_name IS 'el nombre del producto a ser utilizado en el proceso';
ALTER TABLE process ADD COLUMN category_id integer;
COMMENT ON COLUMN process.category_id IS 'categoria del cliente con el que fue creado el proceso';
COMMENT ON COLUMN process.region IS 'contien el codigo de sucursal del usuario branch_id y se almacena en la variable glb_userBranch';
COMMENT ON COLUMN product.product_origen IS 'C- productos del cliente
B- productos del bpm';

ALTER TABLE proc_event ADD COLUMN process_id integer;

ALTER TABLE t_process_type ADD COLUMN code character varying(50);
COMMENT ON COLUMN t_process_type.code IS 'contiene el codigo del proceso utilizado en la programacion';
ALTER TABLE t_process_type ADD COLUMN prod_ind integer;
COMMENT ON COLUMN t_process_type.prod_ind IS 'product indicator
1- prod
0- no prod';

ALTER TABLE process_type ADD COLUMN code character varying(50);
COMMENT ON COLUMN process_type.code IS 'contiene el codigo del proceso utilizado en la programacion';

ALTER TABLE t_pt_cause ADD COLUMN code character varying(50);
COMMENT ON COLUMN t_pt_cause.code IS 'contiene el codigo del proceso utilizado en la programacion';

ALTER TABLE pt_cause ADD COLUMN code character varying(50);
COMMENT ON COLUMN pt_cause.code IS 'contiene el codigo del proceso utilizado en la programacion';

ALTER TABLE t_process_req_prod OWNER TO bpm;
COMMENT ON TABLE t_process_req_prod IS 'contiene la relacion de tipo de proceso requisito y producto, un requisito puede estar asociado a uno o varios productos';

ALTER TABLE appform_b_inst ADD COLUMN appform_id integer;
ALTER TABLE appform_b_inst ADD COLUMN process_id integer;
ALTER TABLE appform_b_inst ADD COLUMN proc_activity_id integer;
ALTER TABLE appform_b_val ALTER COLUMN "value" TYPE text;

ALTER TABLE t_proc_product OWNER TO bpm;
COMMENT ON TABLE t_proc_product IS 'contiene la relacion mucho a mucho de tipo de proceso con producto';

INSERT INTO org_hierarchy_type VALUES (0, 'No especificado');
INSERT INTO org_hierarchy_type VALUES (1, 'Direcciones');
INSERT INTO org_hierarchy_type VALUES (2, 'Divisiones');
INSERT INTO org_hierarchy_type VALUES (3, 'Departamentos');
INSERT INTO txt_msg VALUES ('activation_date', 'SP', 'Fecha de Activación', NULL);

update t_process_type set prod_ind=0;

update txt_msg set txt='Procesos Relacionados' where txt_id='re-calls';
update txt_msg set txt='Proceso Relacionado' where txt_id='re-call' AND lang_cd='SP';
update txt_msg set txt='Procesos Relacionados' where txt_id='re-calls' AND lang_cd='SP';
update txt_msg set txt='Número de Proceso Relacionado' where txt_id='re_call_process_number' AND lang_cd='SP';

update appform_b_inst set appform_id=a.appform_id
from appform a
where a.description = appform_b_inst.description;

update appform_b_inst set process_id=a.process_id
from proc_activity a
where a.inst_form_id = appform_b_inst.inst_form_id;

update appform_b_inst set proc_activity_id=a.proc_activity_id
from proc_activity a
where a.inst_form_id = appform_b_inst.inst_form_id;

insert into t_proc_product(process_type_id, product_id)
select process_type_id, product_id
	from t_process_type tp
	where not exists (select * from t_proc_product pp where pp.process_type_id=tp.process_type_id and pp.product_id=tp.product_id)
	and product_id is not null

insert into proc_product(process_type_id, product_id)
select process_type_id, product_id
	from process_type tp
	where not exists (select * from  proc_product pp where pp.process_type_id=tp.process_type_id and pp.product_id=tp.product_id)
	and product_id is not null

update process set product_id=(select product_id from process_type pt where process.process_type_id = pt.process_type_id)
where product_id is null

============================
CORRER EN STP_CRM
============================

-- DROP TABLE mysched_schedule_type;

CREATE TABLE mysched_schedule_type
(
  id_schedule_type serial NOT NULL,
  "name" character varying(50),
  CONSTRAINT mysched_schedule_type_pkey PRIMARY KEY (id_schedule_type)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE mysched_schedule_type OWNER TO bpm;

ALTER TABLE mysched_event ADD COLUMN proc_activity_id integer;
ALTER TABLE mysched_event ADD COLUMN id_schedule_type integer;

INSERT INTO mysched_schedule_type("name")   VALUES ('Legislativa');
INSERT INTO mysched_schedule_type("name")   VALUES ('Economica');
INSERT INTO mysched_schedule_type("name")   VALUES ('Social');
INSERT INTO mysched_schedule_type("name")   VALUES ('Personal');

Conflicts:
	html/map.jsp