Re: [pgsql-es-ayuda] Tablas anidadas tarda en responder

Mon, 03 Jul 2017 07:04:48 -0700 

El 2017-06-30 12:38, Alvaro Herrera escribió:

> palichis escribió: 
> 
>> Estimados. 
>> 
>> Tengo una consulta con varias tablas anidadas (LEFT JOIN), el resultado
>> es de 8324470 registros, la consulta se demora +- 15minutos.
> 
> Por favor muestra la consulta y la definición de las tablas relevantes,
> en formato legible (un adjunto text/plain es lo mejor, aunque pegarlo
> directamente en el cuerpo del email también sirve si es que tu programa
> de correo no destruye el espacio en blanco).  Un "paste" en un sitio
> externo como gist.github.com sirve como apoyo, pero el material primario
> debería estar en los archivos de la lista.

-- 
SALUDOS CORDIALES 

PALICHIS 8A
CREATE TABLE product_uom
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  uom_type character varying NOT NULL, -- UoM Type
  category_id integer NOT NULL, -- UoM Category
  name character varying(64) NOT NULL, -- Name
  rounding numeric NOT NULL, -- Rounding Precision
  factor numeric NOT NULL, -- Ratio
  active boolean, -- Active
  CONSTRAINT product_uom_pkey PRIMARY KEY (id),
  CONSTRAINT product_uom_category_id_fkey FOREIGN KEY (category_id)
      REFERENCES product_uom_categ (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT product_uom_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_uom_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_uom_factor_gt_zero CHECK (factor <> 0::numeric)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE product_uom
  OWNER TO openerp;
GRANT ALL ON TABLE product_uom TO openerp;
GRANT SELECT ON TABLE product_uom TO lectura;
GRANT SELECT ON TABLE product_uom TO esb_msp;
GRANT SELECT ON TABLE product_uom TO consulta;
GRANT ALL ON TABLE product_uom TO inven_lectu;
GRANT SELECT ON TABLE product_uom TO "wladimir.quijije";
GRANT SELECT ON TABLE product_uom TO "diego.molina";
GRANT SELECT ON TABLE product_uom TO "fausto.suarez";
COMMENT ON TABLE product_uom
  IS 'Product Unit of Measure';
COMMENT ON COLUMN product_uom.uom_type IS 'UoM Type';
COMMENT ON COLUMN product_uom.category_id IS 'UoM Category';
COMMENT ON COLUMN product_uom.name IS 'Name';
COMMENT ON COLUMN product_uom.rounding IS 'Rounding Precision';
COMMENT ON COLUMN product_uom.factor IS 'Ratio';
COMMENT ON COLUMN product_uom.active IS 'Active';


-- Index: sgi_factor

-- DROP INDEX sgi_factor;

CREATE INDEX sgi_factor
  ON product_uom
  USING btree
  (factor);


CREATE TABLE product_product
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  ean13 character varying(13), -- EAN13
  color integer, -- Color Index
  price_extra numeric, -- Variant Price Extra
  default_code character varying(64), -- Reference
  active boolean, -- Active
  variants character varying(64), -- Variants
  product_tmpl_id integer NOT NULL, -- Product Template
  product_image bytea, -- Image
  price_margin numeric, -- Variant Price Margin
  track_production boolean, -- Track Manufacturing Lots
  valuation character varying NOT NULL, -- Inventory Valuation
  track_outgoing boolean, -- Track Outgoing Lots
  track_incoming boolean, -- Track Incoming Lots
  removal_time integer, -- Product Removal Time
  use_time integer, -- Product Use Time
  alert_time integer, -- Product Alert Time
  life_time integer, -- Product Life Time
  advertencia_edad_minima numeric, -- Edad mínima
  cadena_frio boolean, -- Cadena de frio
  es_vacuna boolean, -- Es una vacuna
  atc_id integer, -- ATC
  reacciones_adversas text, -- Reacciones adversas
  via_administracion_id integer, -- Vía de administración primaría
  nivel_atencion_3 boolean, -- Nivel atención III
  advertencia_edad_maxima numeric, -- Edad máxima
  nivel_atencion_2 boolean, -- Nivel atención II
  nivel_atencion_1 boolean, -- Nivel atención I
  nivel_prescripcion_moved0 character varying, -- Nivel prescripción
  concentracion_id integer, -- Concentración
  advertencia_embarazo boolean, -- Contraindicado en el embarazo
  advertencia_edad boolean, -- Existen advertencias de riesgo en edades
  es_medicamento boolean, -- Es un medicamento
  notas text, -- Información extra
  forma_farmaceutica_id integer, -- Forma farmaceutica
  estupefaciente_sicotropico boolean, -- Estupefaciente
  presentacion_id integer, -- Envase/Presentación
  condiciones_almacenamiento text, -- Condiciones de almacenamiento
  categoria_embarazo integer, -- Categoría de riesgo embarazo
  uom_dispensacion integer, -- UdM dispensación
  price_promedio numeric, -- Precio Promedio
  es_dispositivo boolean, -- Es dispositivo
  ecri integer, -- Código UMDNS
  especialidad integer, -- Especialidad
  riesgo integer, -- Nivel de riesgo
  empaque_primario boolean, -- Empaque primario
  empaque_secundario boolean, -- Empaque  secundario
  sinonimo character varying(128), -- Sinónimo
  envase_dispositivo_id integer, -- Presentación/Envase
  psicotropico boolean, -- Psicotrópico
  nivel_prescripcion integer, -- Nivel de prescripción
  estupefaciente boolean, -- Estupefaciente
  criterio_tecnico boolean, -- Criterio técnico
  aplicacion text, -- Aplicación
  name_template character varying(256),
  alerta_lactancia integer, -- Alerta lactancia
  nivel_atencion_1c boolean, -- Nivel atención I tipo C
  nivel_atencion_1b boolean, -- Nivel atención I tipo B
  contiene_mercurio boolean, -- Contiene Mercurio
  nivel_mercurio integer, -- Nivel de mercurio
  default_cnmb_code character varying(16), -- Referencia CNMB
  forma_farmac_cnmb_id integer, -- Forma Farmaceútica CNMB
  externalizacion boolean, -- Externalización
  tipo_ayuda_tecnica_id integer, -- Tipo Ayuda Técnica
  es_ay_tecnica boolean, -- Es ayuda técnica
  precio numeric, -- Precio Externalización
  CONSTRAINT product_product_pkey PRIMARY KEY (id),
  CONSTRAINT product_product_alerta_lactancia_fkey FOREIGN KEY 
(alerta_lactancia)
      REFERENCES sgi_alerta_lactancia (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_atc_id_fkey FOREIGN KEY (atc_id)
      REFERENCES sgi_atc (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_categoria_embarazo_fkey FOREIGN KEY 
(categoria_embarazo)
      REFERENCES sgi_categoria_embarazo (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_concentracion_id_fkey FOREIGN KEY 
(concentracion_id)
      REFERENCES sgi_concentracion (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_ecri_fkey FOREIGN KEY (ecri)
      REFERENCES sgi_ecri (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_envase_dispositivo_id_fkey FOREIGN KEY 
(envase_dispositivo_id)
      REFERENCES sgi_envase_dispositivo (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_especialidad_fkey FOREIGN KEY (especialidad)
      REFERENCES sgi_servicio (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_forma_farmac_cnmb_id_fkey FOREIGN KEY 
(forma_farmac_cnmb_id)
      REFERENCES sgi_forma_farmac_cnmb (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_forma_farmaceutica_id_fkey FOREIGN KEY 
(forma_farmaceutica_id)
      REFERENCES sgi_forma_farmaceutica (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_presentacion_id_fkey FOREIGN KEY (presentacion_id)
      REFERENCES sgi_envase (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_product_tmpl_id_fkey FOREIGN KEY (product_tmpl_id)
      REFERENCES product_template (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT product_product_riesgo_fkey FOREIGN KEY (riesgo)
      REFERENCES sgi_riesgo_dispositivo (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_tipo_ayuda_tecnica_id_fkey FOREIGN KEY 
(tipo_ayuda_tecnica_id)
      REFERENCES sgi_tipo_ayuda_tecnica (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_uom_dispensacion_fkey FOREIGN KEY 
(uom_dispensacion)
      REFERENCES product_uom (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_via_administracion_id_fkey FOREIGN KEY 
(via_administracion_id)
      REFERENCES sgi_via_administracion (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT product_product_default_code_unique UNIQUE (default_code)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE product_product
  OWNER TO openerp;
GRANT ALL ON TABLE product_product TO openerp;
GRANT SELECT ON TABLE product_product TO lectura;
GRANT SELECT ON TABLE product_product TO esb_msp;
GRANT SELECT ON TABLE product_product TO consulta;
GRANT ALL ON TABLE product_product TO inven_lectu;
GRANT SELECT ON TABLE product_product TO "wladimir.quijije";
GRANT SELECT ON TABLE product_product TO "diego.molina";
GRANT SELECT ON TABLE product_product TO "fausto.suarez";
COMMENT ON TABLE product_product
  IS 'Product';
COMMENT ON COLUMN product_product.ean13 IS 'EAN13';
COMMENT ON COLUMN product_product.color IS 'Color Index';
COMMENT ON COLUMN product_product.price_extra IS 'Variant Price Extra';
COMMENT ON COLUMN product_product.default_code IS 'Reference';
COMMENT ON COLUMN product_product.active IS 'Active';
COMMENT ON COLUMN product_product.variants IS 'Variants';
COMMENT ON COLUMN product_product.product_tmpl_id IS 'Product Template';
COMMENT ON COLUMN product_product.product_image IS 'Image';
COMMENT ON COLUMN product_product.price_margin IS 'Variant Price Margin';
COMMENT ON COLUMN product_product.track_production IS 'Track Manufacturing 
Lots';
COMMENT ON COLUMN product_product.valuation IS 'Inventory Valuation';
COMMENT ON COLUMN product_product.track_outgoing IS 'Track Outgoing Lots';
COMMENT ON COLUMN product_product.track_incoming IS 'Track Incoming Lots';
COMMENT ON COLUMN product_product.removal_time IS 'Product Removal Time';
COMMENT ON COLUMN product_product.use_time IS 'Product Use Time';
COMMENT ON COLUMN product_product.alert_time IS 'Product Alert Time';
COMMENT ON COLUMN product_product.life_time IS 'Product Life Time';
COMMENT ON COLUMN product_product.advertencia_edad_minima IS 'Edad mínima';
COMMENT ON COLUMN product_product.cadena_frio IS 'Cadena de frio';
COMMENT ON COLUMN product_product.es_vacuna IS 'Es una vacuna';
COMMENT ON COLUMN product_product.atc_id IS 'ATC';
COMMENT ON COLUMN product_product.reacciones_adversas IS 'Reacciones adversas';
COMMENT ON COLUMN product_product.via_administracion_id IS 'Vía de 
administración primaría';
COMMENT ON COLUMN product_product.nivel_atencion_3 IS 'Nivel atención III';
COMMENT ON COLUMN product_product.advertencia_edad_maxima IS 'Edad máxima';
COMMENT ON COLUMN product_product.nivel_atencion_2 IS 'Nivel atención II';
COMMENT ON COLUMN product_product.nivel_atencion_1 IS 'Nivel atención I';
COMMENT ON COLUMN product_product.nivel_prescripcion_moved0 IS 'Nivel 
prescripción';
COMMENT ON COLUMN product_product.concentracion_id IS 'Concentración';
COMMENT ON COLUMN product_product.advertencia_embarazo IS 'Contraindicado en el 
embarazo';
COMMENT ON COLUMN product_product.advertencia_edad IS 'Existen advertencias de 
riesgo en edades';
COMMENT ON COLUMN product_product.es_medicamento IS 'Es un medicamento';
COMMENT ON COLUMN product_product.notas IS 'Información extra';
COMMENT ON COLUMN product_product.forma_farmaceutica_id IS 'Forma farmaceutica';
COMMENT ON COLUMN product_product.estupefaciente_sicotropico IS 
'Estupefaciente';
COMMENT ON COLUMN product_product.presentacion_id IS 'Envase/Presentación';
COMMENT ON COLUMN product_product.condiciones_almacenamiento IS 'Condiciones de 
almacenamiento';
COMMENT ON COLUMN product_product.categoria_embarazo IS 'Categoría de riesgo 
embarazo';
COMMENT ON COLUMN product_product.uom_dispensacion IS 'UdM dispensación';
COMMENT ON COLUMN product_product.price_promedio IS 'Precio Promedio';
COMMENT ON COLUMN product_product.es_dispositivo IS 'Es dispositivo';
COMMENT ON COLUMN product_product.ecri IS 'Código UMDNS';
COMMENT ON COLUMN product_product.especialidad IS 'Especialidad';
COMMENT ON COLUMN product_product.riesgo IS 'Nivel de riesgo';
COMMENT ON COLUMN product_product.empaque_primario IS 'Empaque primario';
COMMENT ON COLUMN product_product.empaque_secundario IS 'Empaque  secundario';
COMMENT ON COLUMN product_product.sinonimo IS 'Sinónimo';
COMMENT ON COLUMN product_product.envase_dispositivo_id IS 
'Presentación/Envase';
COMMENT ON COLUMN product_product.psicotropico IS 'Psicotrópico';
COMMENT ON COLUMN product_product.nivel_prescripcion IS 'Nivel de 
prescripción';
COMMENT ON COLUMN product_product.estupefaciente IS 'Estupefaciente';
COMMENT ON COLUMN product_product.criterio_tecnico IS 'Criterio técnico';
COMMENT ON COLUMN product_product.aplicacion IS 'Aplicación';
COMMENT ON COLUMN product_product.alerta_lactancia IS 'Alerta lactancia';
COMMENT ON COLUMN product_product.nivel_atencion_1c IS 'Nivel atención I tipo 
C';
COMMENT ON COLUMN product_product.nivel_atencion_1b IS 'Nivel atención I tipo 
B';
COMMENT ON COLUMN product_product.contiene_mercurio IS 'Contiene Mercurio';
COMMENT ON COLUMN product_product.nivel_mercurio IS 'Nivel de mercurio';
COMMENT ON COLUMN product_product.default_cnmb_code IS 'Referencia CNMB';
COMMENT ON COLUMN product_product.forma_farmac_cnmb_id IS 'Forma Farmaceútica 
CNMB';
COMMENT ON COLUMN product_product.externalizacion IS 'Externalización';
COMMENT ON COLUMN product_product.tipo_ayuda_tecnica_id IS 'Tipo Ayuda 
Técnica';
COMMENT ON COLUMN product_product.es_ay_tecnica IS 'Es ayuda técnica';
COMMENT ON COLUMN product_product.precio IS 'Precio Externalización';


-- Index: product_product_default_code_idx

-- DROP INDEX product_product_default_code_idx;

CREATE INDEX product_product_default_code_idx
  ON product_product
  USING gin
  (default_code COLLATE pg_catalog."default" gin_trgm_ops);

-- Index: product_product_name_template_idx

-- DROP INDEX product_product_name_template_idx;

CREATE INDEX product_product_name_template_idx
  ON product_product
  USING gin
  (name_template COLLATE pg_catalog."default" gin_trgm_ops);

-- Index: product_product_name_template_index

-- DROP INDEX product_product_name_template_index;

CREATE INDEX product_product_name_template_index
  ON product_product
  USING btree
  (name_template COLLATE pg_catalog."default");

-- Index: product_product_sinonimo_idx

-- DROP INDEX product_product_sinonimo_idx;

CREATE INDEX product_product_sinonimo_idx
  ON product_product
  USING gin
  (sinonimo COLLATE pg_catalog."default" gin_trgm_ops);

-- Index: sgi_name_template

-- DROP INDEX sgi_name_template;

CREATE INDEX sgi_name_template
  ON product_product
  USING btree
  (name_template COLLATE pg_catalog."default");

-- Index: sgi_sinonimo

-- DROP INDEX sgi_sinonimo;

CREATE INDEX sgi_sinonimo
  ON product_product
  USING btree
  (sinonimo COLLATE pg_catalog."default");



CREATE TABLE stock_location
(
  id serial NOT NULL,
  parent_left integer,
  parent_right integer,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  comment text, -- Additional Information
  address_id integer, -- Location Address
  chained_delay integer, -- Chaining Lead Time
  chained_company_id integer, -- Chained Company
  active boolean, -- Active
  posz integer, -- Height (Z)
  posx integer, -- Corridor (X)
  posy integer, -- Shelves (Y)
  valuation_in_account_id integer, -- Stock Valuation Account (Incoming)
  location_id integer, -- Parent Location
  icon character varying(64), -- Icon
  valuation_out_account_id integer, -- Stock Valuation Account (Outgoing)
  scrap_location boolean, -- Scrap Location
  name character varying(64) NOT NULL, -- Location Name
  chained_location_id integer, -- Chained Location If Fixed
  chained_journal_id integer, -- Chaining Journal
  chained_picking_type character varying, -- Shipping Type
  company_id integer, -- Company
  chained_auto_packing character varying NOT NULL, -- Chaining Type
  complete_name character varying(256), -- Location Name
  usage character varying NOT NULL, -- Location Type
  chained_location_type character varying NOT NULL, -- Chained Location Type
  base_location boolean, -- Base
  excluir_reporte_stock boolean, -- Excluir del stock
  CONSTRAINT stock_location_pkey PRIMARY KEY (id),
  CONSTRAINT stock_location_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES res_partner_address (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_chained_company_id_fkey FOREIGN KEY 
(chained_company_id)
      REFERENCES res_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_chained_journal_id_fkey FOREIGN KEY 
(chained_journal_id)
      REFERENCES stock_journal (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_chained_location_id_fkey FOREIGN KEY 
(chained_location_id)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES res_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_valuation_in_account_id_fkey FOREIGN KEY 
(valuation_in_account_id)
      REFERENCES account_account (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_valuation_out_account_id_fkey FOREIGN KEY 
(valuation_out_account_id)
      REFERENCES account_account (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_location_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stock_location
  OWNER TO openerp;
GRANT ALL ON TABLE stock_location TO openerp;
GRANT SELECT ON TABLE stock_location TO lectura;
GRANT SELECT ON TABLE stock_location TO esb_msp;
GRANT SELECT ON TABLE stock_location TO consulta;
GRANT ALL ON TABLE stock_location TO inven_lectu;
GRANT SELECT ON TABLE stock_location TO "wladimir.quijije";
GRANT SELECT ON TABLE stock_location TO "diego.molina";
GRANT SELECT ON TABLE stock_location TO "fausto.suarez";
COMMENT ON TABLE stock_location
  IS 'Location';
COMMENT ON COLUMN stock_location.comment IS 'Additional Information';
COMMENT ON COLUMN stock_location.address_id IS 'Location Address';
COMMENT ON COLUMN stock_location.chained_delay IS 'Chaining Lead Time';
COMMENT ON COLUMN stock_location.chained_company_id IS 'Chained Company';
COMMENT ON COLUMN stock_location.active IS 'Active';
COMMENT ON COLUMN stock_location.posz IS 'Height (Z)';
COMMENT ON COLUMN stock_location.posx IS 'Corridor (X)';
COMMENT ON COLUMN stock_location.posy IS 'Shelves (Y)';
COMMENT ON COLUMN stock_location.valuation_in_account_id IS 'Stock Valuation 
Account (Incoming)';
COMMENT ON COLUMN stock_location.location_id IS 'Parent Location';
COMMENT ON COLUMN stock_location.icon IS 'Icon';
COMMENT ON COLUMN stock_location.valuation_out_account_id IS 'Stock Valuation 
Account (Outgoing)';
COMMENT ON COLUMN stock_location.scrap_location IS 'Scrap Location';
COMMENT ON COLUMN stock_location.name IS 'Location Name';
COMMENT ON COLUMN stock_location.chained_location_id IS 'Chained Location If 
Fixed';
COMMENT ON COLUMN stock_location.chained_journal_id IS 'Chaining Journal';
COMMENT ON COLUMN stock_location.chained_picking_type IS 'Shipping Type';
COMMENT ON COLUMN stock_location.company_id IS 'Company';
COMMENT ON COLUMN stock_location.chained_auto_packing IS 'Chaining Type';
COMMENT ON COLUMN stock_location.complete_name IS 'Location Name';
COMMENT ON COLUMN stock_location.usage IS 'Location Type';
COMMENT ON COLUMN stock_location.chained_location_type IS 'Chained Location 
Type';
COMMENT ON COLUMN stock_location.base_location IS 'Base';
COMMENT ON COLUMN stock_location.excluir_reporte_stock IS 'Excluir del stock';


-- Index: idx_complete_name_tmp_2

-- DROP INDEX idx_complete_name_tmp_2;

CREATE INDEX idx_complete_name_tmp_2
  ON stock_location
  USING btree
  (complete_name COLLATE pg_catalog."default");

-- Index: stock_location_company_id_index

-- DROP INDEX stock_location_company_id_index;

CREATE INDEX stock_location_company_id_index
  ON stock_location
  USING btree
  (company_id);

-- Index: stock_location_location_id_index

-- DROP INDEX stock_location_location_id_index;

CREATE INDEX stock_location_location_id_index
  ON stock_location
  USING btree
  (location_id);

-- Index: stock_location_parent_left_index

-- DROP INDEX stock_location_parent_left_index;

CREATE INDEX stock_location_parent_left_index
  ON stock_location
  USING btree
  (parent_left);

-- Index: stock_location_parent_right_index

-- DROP INDEX stock_location_parent_right_index;

CREATE INDEX stock_location_parent_right_index
  ON stock_location
  USING btree
  (parent_right);

-- Index: stock_location_usage_index

-- DROP INDEX stock_location_usage_index;

CREATE INDEX stock_location_usage_index
  ON stock_location
  USING btree
  (usage COLLATE pg_catalog."default");



CREATE TABLE stock_picking
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  origin character varying(64), -- Origin
  address_id integer, -- Address
  date_done timestamp without time zone, -- Date Done
  min_date timestamp without time zone, -- Expected Date
  date timestamp without time zone, -- Order Date
  location_id integer, -- Location
  stock_journal_id integer, -- Stock Journal
  backorder_id integer, -- Back Order of
  partner_id integer, -- Partner
  name character varying(64), -- Reference
  auto_picking boolean, -- Auto-Picking
  move_type character varying NOT NULL, -- Delivery Method
  company_id integer NOT NULL, -- Company
  invoice_state character varying NOT NULL, -- Invoice Control
  note text, -- Notes
  state character varying, -- State
  location_dest_id integer, -- Dest. Location
  max_date timestamp without time zone, -- Max. Expected Date
  type character varying NOT NULL, -- Shipping Type
  purchase_id integer, -- Purchase Order
  sale_id integer, -- Sales Order
  vacuna_id integer, -- ID Orden vacunación
  receta_id integer, -- ID Receta
  secuencia_bodega_id integer, -- Programa Nacional de Salud
  programa_id integer, -- Programa Nacional
  tiene_programa boolean, -- Programa
  nro_factura character varying(50), -- Factura
  encargado_egreso boolean, -- Es Encargado
  encargado boolean, -- Es Encargado
  global_loc_dest_id integer, -- Dest. global
  global_loc_id integer, -- Origen global
  estado_impreso character varying, -- unknown
  trans_intercompany boolean, -- Intercompañias
  nombre_recibi character varying(100), -- Nombre
  cargo_recibi character varying(100), -- Cargo
  document_type character varying, -- Tipo de documento
  cedula_recibi character varying(14), -- Cedula
  user_recibe integer, -- Valida recepción
  aprobacion_origen boolean, -- Aprobación origen de inventarios
  aprobacion_destino boolean, -- Aprobación destino de inventarios
  prescripcion_id integer, -- ID Prescripción dispositivos
  paciente_id integer, -- Paciente
  descargo_contra_paciente boolean, -- Descargar contra paciente
  nro_comprobante character varying(256),
  referencia character varying(64), -- Nro de Referencia
  global_loc_id_usr integer, -- Origen global
  global_loc_dest_id_usr integer, -- Dest. global
  motivo_cancelacion text, -- Motivo de la Cancelación
  fecha_validacion timestamp without time zone, -- Fecha de validación
  user_id integer, -- Guarda Almacen
  codigo_receta integer, -- Código receta
  CONSTRAINT stock_picking_pkey PRIMARY KEY (id),
  CONSTRAINT stock_picking_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES res_partner_address (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_backorder_id_fkey FOREIGN KEY (backorder_id)
      REFERENCES stock_picking (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES res_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_global_loc_dest_id_fkey FOREIGN KEY 
(global_loc_dest_id)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_global_loc_dest_id_usr_fkey FOREIGN KEY 
(global_loc_dest_id_usr)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_global_loc_id_fkey FOREIGN KEY (global_loc_id)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_global_loc_id_usr_fkey FOREIGN KEY 
(global_loc_id_usr)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_location_dest_id_fkey FOREIGN KEY (location_dest_id)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_location_id_fkey FOREIGN KEY (location_id)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_paciente_id_fkey FOREIGN KEY (paciente_id)
      REFERENCES res_partner (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_prescripcion_id_fkey FOREIGN KEY (prescripcion_id)
      REFERENCES sgi_prescripcion_dispositivos (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_programa_id_fkey FOREIGN KEY (programa_id)
      REFERENCES sgi_programa_nacional (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_purchase_id_fkey FOREIGN KEY (purchase_id)
      REFERENCES purchase_order (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_receta_id_fkey FOREIGN KEY (receta_id)
      REFERENCES sgi_receta (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_sale_id_fkey FOREIGN KEY (sale_id)
      REFERENCES sale_order (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_secuencia_bodega_id_fkey FOREIGN KEY 
(secuencia_bodega_id)
      REFERENCES sgi_bodegas_secuencias (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_stock_journal_id_fkey FOREIGN KEY (stock_journal_id)
      REFERENCES stock_journal (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_user_recibe_fkey FOREIGN KEY (user_recibe)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_vacuna_id_fkey FOREIGN KEY (vacuna_id)
      REFERENCES sgi_vacuna (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_picking_name_uniq UNIQUE (name, company_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stock_picking
  OWNER TO openerp;
GRANT ALL ON TABLE stock_picking TO openerp;
GRANT SELECT ON TABLE stock_picking TO esb_msp;
GRANT SELECT, UPDATE ON TABLE stock_picking TO lectura;
GRANT SELECT ON TABLE stock_picking TO consulta;
GRANT ALL ON TABLE stock_picking TO inven_lectu;
GRANT SELECT ON TABLE stock_picking TO "wladimir.quijije";
GRANT SELECT ON TABLE stock_picking TO "diego.molina";
GRANT SELECT ON TABLE stock_picking TO "fausto.suarez";
COMMENT ON TABLE stock_picking
  IS 'Picking List';
COMMENT ON COLUMN stock_picking.origin IS 'Origin';
COMMENT ON COLUMN stock_picking.address_id IS 'Address';
COMMENT ON COLUMN stock_picking.date_done IS 'Date Done';
COMMENT ON COLUMN stock_picking.min_date IS 'Expected Date';
COMMENT ON COLUMN stock_picking.date IS 'Order Date';
COMMENT ON COLUMN stock_picking.location_id IS 'Location';
COMMENT ON COLUMN stock_picking.stock_journal_id IS 'Stock Journal';
COMMENT ON COLUMN stock_picking.backorder_id IS 'Back Order of';
COMMENT ON COLUMN stock_picking.partner_id IS 'Partner';
COMMENT ON COLUMN stock_picking.name IS 'Reference';
COMMENT ON COLUMN stock_picking.auto_picking IS 'Auto-Picking';
COMMENT ON COLUMN stock_picking.move_type IS 'Delivery Method';
COMMENT ON COLUMN stock_picking.company_id IS 'Company';
COMMENT ON COLUMN stock_picking.invoice_state IS 'Invoice Control';
COMMENT ON COLUMN stock_picking.note IS 'Notes';
COMMENT ON COLUMN stock_picking.state IS 'State';
COMMENT ON COLUMN stock_picking.location_dest_id IS 'Dest. Location';
COMMENT ON COLUMN stock_picking.max_date IS 'Max. Expected Date';
COMMENT ON COLUMN stock_picking.type IS 'Shipping Type';
COMMENT ON COLUMN stock_picking.purchase_id IS 'Purchase Order';
COMMENT ON COLUMN stock_picking.sale_id IS 'Sales Order';
COMMENT ON COLUMN stock_picking.vacuna_id IS 'ID Orden vacunación';
COMMENT ON COLUMN stock_picking.receta_id IS 'ID Receta';
COMMENT ON COLUMN stock_picking.secuencia_bodega_id IS 'Programa Nacional de 
Salud';
COMMENT ON COLUMN stock_picking.programa_id IS 'Programa Nacional';
COMMENT ON COLUMN stock_picking.tiene_programa IS 'Programa';
COMMENT ON COLUMN stock_picking.nro_factura IS 'Factura';
COMMENT ON COLUMN stock_picking.encargado_egreso IS 'Es Encargado';
COMMENT ON COLUMN stock_picking.encargado IS 'Es Encargado';
COMMENT ON COLUMN stock_picking.global_loc_dest_id IS 'Dest. global';
COMMENT ON COLUMN stock_picking.global_loc_id IS 'Origen global';
COMMENT ON COLUMN stock_picking.estado_impreso IS 'unknown';
COMMENT ON COLUMN stock_picking.trans_intercompany IS 'Intercompañias';
COMMENT ON COLUMN stock_picking.nombre_recibi IS 'Nombre';
COMMENT ON COLUMN stock_picking.cargo_recibi IS 'Cargo';
COMMENT ON COLUMN stock_picking.document_type IS 'Tipo de documento';
COMMENT ON COLUMN stock_picking.cedula_recibi IS 'Cedula';
COMMENT ON COLUMN stock_picking.user_recibe IS 'Valida recepción';
COMMENT ON COLUMN stock_picking.aprobacion_origen IS 'Aprobación origen de 
inventarios';
COMMENT ON COLUMN stock_picking.aprobacion_destino IS 'Aprobación destino de 
inventarios';
COMMENT ON COLUMN stock_picking.prescripcion_id IS 'ID Prescripción 
dispositivos';
COMMENT ON COLUMN stock_picking.paciente_id IS 'Paciente';
COMMENT ON COLUMN stock_picking.descargo_contra_paciente IS 'Descargar contra 
paciente';
COMMENT ON COLUMN stock_picking.referencia IS 'Nro de Referencia';
COMMENT ON COLUMN stock_picking.global_loc_id_usr IS 'Origen global';
COMMENT ON COLUMN stock_picking.global_loc_dest_id_usr IS 'Dest. global';
COMMENT ON COLUMN stock_picking.motivo_cancelacion IS 'Motivo de la 
Cancelación';
COMMENT ON COLUMN stock_picking.fecha_validacion IS 'Fecha de validación';
COMMENT ON COLUMN stock_picking.user_id IS 'Guarda Almacen';
COMMENT ON COLUMN stock_picking.codigo_receta IS 'Código receta';


-- Index: stock_picking_backorder_id_index

-- DROP INDEX stock_picking_backorder_id_index;

CREATE INDEX stock_picking_backorder_id_index
  ON stock_picking
  USING btree
  (backorder_id);

-- Index: stock_picking_company_id_index

-- DROP INDEX stock_picking_company_id_index;

CREATE INDEX stock_picking_company_id_index
  ON stock_picking
  USING btree
  (company_id);

-- Index: stock_picking_date_index

-- DROP INDEX stock_picking_date_index;

CREATE INDEX stock_picking_date_index
  ON stock_picking
  USING btree
  (date);

-- Index: stock_picking_invoice_state_index

-- DROP INDEX stock_picking_invoice_state_index;

CREATE INDEX stock_picking_invoice_state_index
  ON stock_picking
  USING btree
  (invoice_state COLLATE pg_catalog."default");

-- Index: stock_picking_location_dest_id_index

-- DROP INDEX stock_picking_location_dest_id_index;

CREATE INDEX stock_picking_location_dest_id_index
  ON stock_picking
  USING btree
  (location_dest_id);

-- Index: stock_picking_location_id_index

-- DROP INDEX stock_picking_location_id_index;

CREATE INDEX stock_picking_location_id_index
  ON stock_picking
  USING btree
  (location_id);

-- Index: stock_picking_max_date_index

-- DROP INDEX stock_picking_max_date_index;

CREATE INDEX stock_picking_max_date_index
  ON stock_picking
  USING btree
  (max_date);

-- Index: stock_picking_min_date_index

-- DROP INDEX stock_picking_min_date_index;

CREATE INDEX stock_picking_min_date_index
  ON stock_picking
  USING btree
  (min_date);

-- Index: stock_picking_name_index

-- DROP INDEX stock_picking_name_index;

CREATE INDEX stock_picking_name_index
  ON stock_picking
  USING btree
  (name COLLATE pg_catalog."default");

-- Index: stock_picking_origin_index

-- DROP INDEX stock_picking_origin_index;

CREATE INDEX stock_picking_origin_index
  ON stock_picking
  USING btree
  (origin COLLATE pg_catalog."default");

-- Index: stock_picking_purchase_id_index

-- DROP INDEX stock_picking_purchase_id_index;

CREATE INDEX stock_picking_purchase_id_index
  ON stock_picking
  USING btree
  (purchase_id);

-- Index: stock_picking_sale_id_index

-- DROP INDEX stock_picking_sale_id_index;

CREATE INDEX stock_picking_sale_id_index
  ON stock_picking
  USING btree
  (sale_id);

-- Index: stock_picking_state_index

-- DROP INDEX stock_picking_state_index;

CREATE INDEX stock_picking_state_index
  ON stock_picking
  USING btree
  (state COLLATE pg_catalog."default");

-- Index: stock_picking_stock_journal_id_index

-- DROP INDEX stock_picking_stock_journal_id_index;

CREATE INDEX stock_picking_stock_journal_id_index
  ON stock_picking
  USING btree
  (stock_journal_id);

-- Index: stock_picking_type_index

-- DROP INDEX stock_picking_type_index;

CREATE INDEX stock_picking_type_index
  ON stock_picking
  USING btree
  (type COLLATE pg_catalog."default");

-- Index: "índice_id"

-- DROP INDEX "índice_id";

CREATE UNIQUE INDEX "índice_id"
  ON stock_picking
  USING btree
  (id);


-- Trigger: tgg_spubicaciondestino_mov on stock_picking

-- DROP TRIGGER tgg_spubicaciondestino_mov ON stock_picking;

CREATE TRIGGER tgg_spubicaciondestino_mov
  AFTER UPDATE
  ON stock_picking
  FOR EACH ROW
  EXECUTE PROCEDURE tgg_spubicaciondestino_mov();
ALTER TABLE stock_picking DISABLE TRIGGER tgg_spubicaciondestino_mov;



CREATE TABLE stock_move
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  origin character varying(64), -- Origin
  product_uos_qty numeric, -- Quantity (UOS)
  address_id integer, -- Destination Address
  product_uom integer NOT NULL, -- Unit of Measure
  price_unit numeric, -- Unit Price
  date_expected timestamp without time zone NOT NULL, -- Scheduled Date
  date timestamp without time zone NOT NULL, -- Date
  prodlot_id integer, -- Production Lot
  move_dest_id integer, -- Destination Move
  product_qty numeric NOT NULL, -- Quantity
  product_uos integer, -- Product UOS
  location_id integer NOT NULL, -- Source Location
  name character varying(250) NOT NULL, -- Name
  note text, -- Notes
  product_id integer NOT NULL, -- Product
  auto_validate boolean, -- Auto Validate
  price_currency_id integer, -- Currency for average price
  partner_id integer, -- Partner
  company_id integer NOT NULL, -- Company
  picking_id integer, -- Reference
  priority character varying, -- Priority
  state character varying, -- State
  location_dest_id integer NOT NULL, -- Destination Location
  tracking_id integer, -- Pack
  product_packaging integer, -- Packaging
  purchase_line_id integer, -- Purchase Order Line
  sale_line_id integer, -- Sales Order Line
  linea_vacuna_id integer, -- Línea de vacuna
  linea_receta_id integer, -- Línea de receta
  discount double precision, -- Descuento (%)
  trans_intercompany boolean, -- Intercompañias
  precio_unitario_lote numeric, -- Precio unitario
  linea_prescripcion_id integer, -- Línea de prescripcion de dispositivos
  type_rp character varying, -- Tipo RP
  fecha_caducidad_usr timestamp without time zone, -- Fecha Caducidad del lote
  caducidad character varying, -- Caducidad
  fecha_caducidad timestamp without time zone, -- Fecha Caducidad del lote
  precio_unitario_lote_usr numeric, -- Precio unitario
  edicion character varying, -- Edicion
  CONSTRAINT stock_move_pkey PRIMARY KEY (id),
  CONSTRAINT stock_move_address_id_fkey FOREIGN KEY (address_id)
      REFERENCES res_partner_address (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES res_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_linea_prescripcion_id_fkey FOREIGN KEY 
(linea_prescripcion_id)
      REFERENCES sgi_lineas_prescripcion (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_linea_receta_id_fkey FOREIGN KEY (linea_receta_id)
      REFERENCES sgi_lineas_receta (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_linea_vacuna_id_fkey FOREIGN KEY (linea_vacuna_id)
      REFERENCES sgi_lineas_vacuna (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_location_dest_id_fkey FOREIGN KEY (location_dest_id)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_location_id_fkey FOREIGN KEY (location_id)
      REFERENCES stock_location (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_move_dest_id_fkey FOREIGN KEY (move_dest_id)
      REFERENCES stock_move (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_picking_id_fkey FOREIGN KEY (picking_id)
      REFERENCES stock_picking (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_price_currency_id_fkey FOREIGN KEY (price_currency_id)
      REFERENCES res_currency (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_prodlot_id_fkey FOREIGN KEY (prodlot_id)
      REFERENCES stock_production_lot (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_product_id_fkey FOREIGN KEY (product_id)
      REFERENCES product_product (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_product_packaging_fkey FOREIGN KEY (product_packaging)
      REFERENCES product_packaging (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_product_uom_fkey FOREIGN KEY (product_uom)
      REFERENCES product_uom (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_product_uos_fkey FOREIGN KEY (product_uos)
      REFERENCES product_uom (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_purchase_line_id_fkey FOREIGN KEY (purchase_line_id)
      REFERENCES purchase_order_line (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_sale_line_id_fkey FOREIGN KEY (sale_line_id)
      REFERENCES sale_order_line (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_tracking_id_fkey FOREIGN KEY (tracking_id)
      REFERENCES stock_tracking (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT stock_move_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stock_move
  OWNER TO openerp;
GRANT ALL ON TABLE stock_move TO openerp;
GRANT SELECT ON TABLE stock_move TO esb_msp;
GRANT SELECT, UPDATE ON TABLE stock_move TO lectura;
GRANT SELECT ON TABLE stock_move TO consulta;
GRANT ALL ON TABLE stock_move TO inven_lectu;
GRANT SELECT ON TABLE stock_move TO "wladimir.quijije";
GRANT SELECT ON TABLE stock_move TO "diego.molina";
GRANT SELECT ON TABLE stock_move TO "fausto.suarez";
COMMENT ON TABLE stock_move
  IS 'Stock Move';
COMMENT ON COLUMN stock_move.origin IS 'Origin';
COMMENT ON COLUMN stock_move.product_uos_qty IS 'Quantity (UOS)';
COMMENT ON COLUMN stock_move.address_id IS 'Destination Address ';
COMMENT ON COLUMN stock_move.product_uom IS 'Unit of Measure';
COMMENT ON COLUMN stock_move.price_unit IS 'Unit Price';
COMMENT ON COLUMN stock_move.date_expected IS 'Scheduled Date';
COMMENT ON COLUMN stock_move.date IS 'Date';
COMMENT ON COLUMN stock_move.prodlot_id IS 'Production Lot';
COMMENT ON COLUMN stock_move.move_dest_id IS 'Destination Move';
COMMENT ON COLUMN stock_move.product_qty IS 'Quantity';
COMMENT ON COLUMN stock_move.product_uos IS 'Product UOS';
COMMENT ON COLUMN stock_move.location_id IS 'Source Location';
COMMENT ON COLUMN stock_move.name IS 'Name';
COMMENT ON COLUMN stock_move.note IS 'Notes';
COMMENT ON COLUMN stock_move.product_id IS 'Product';
COMMENT ON COLUMN stock_move.auto_validate IS 'Auto Validate';
COMMENT ON COLUMN stock_move.price_currency_id IS 'Currency for average price';
COMMENT ON COLUMN stock_move.partner_id IS 'Partner';
COMMENT ON COLUMN stock_move.company_id IS 'Company';
COMMENT ON COLUMN stock_move.picking_id IS 'Reference';
COMMENT ON COLUMN stock_move.priority IS 'Priority';
COMMENT ON COLUMN stock_move.state IS 'State';
COMMENT ON COLUMN stock_move.location_dest_id IS 'Destination Location';
COMMENT ON COLUMN stock_move.tracking_id IS 'Pack';
COMMENT ON COLUMN stock_move.product_packaging IS 'Packaging';
COMMENT ON COLUMN stock_move.purchase_line_id IS 'Purchase Order Line';
COMMENT ON COLUMN stock_move.sale_line_id IS 'Sales Order Line';
COMMENT ON COLUMN stock_move.linea_vacuna_id IS 'Línea de vacuna';
COMMENT ON COLUMN stock_move.linea_receta_id IS 'Línea de receta';
COMMENT ON COLUMN stock_move.discount IS 'Descuento (%)';
COMMENT ON COLUMN stock_move.trans_intercompany IS 'Intercompañias';
COMMENT ON COLUMN stock_move.precio_unitario_lote IS 'Precio unitario';
COMMENT ON COLUMN stock_move.linea_prescripcion_id IS 'Línea de prescripcion 
de dispositivos';
COMMENT ON COLUMN stock_move.type_rp IS 'Tipo RP';
COMMENT ON COLUMN stock_move.fecha_caducidad_usr IS 'Fecha Caducidad del lote';
COMMENT ON COLUMN stock_move.caducidad IS 'Caducidad';
COMMENT ON COLUMN stock_move.fecha_caducidad IS 'Fecha Caducidad del lote';
COMMENT ON COLUMN stock_move.precio_unitario_lote_usr IS 'Precio unitario';
COMMENT ON COLUMN stock_move.edicion IS 'Edicion';


-- Index: sgi_product

-- DROP INDEX sgi_product;

CREATE INDEX sgi_product
  ON stock_move
  USING btree
  (product_qty);

-- Index: sgi_product_uom

-- DROP INDEX sgi_product_uom;

CREATE INDEX sgi_product_uom
  ON stock_move
  USING btree
  (product_uom);

-- Index: sgi_state

-- DROP INDEX sgi_state;

CREATE INDEX sgi_state
  ON stock_move
  USING btree
  (state COLLATE pg_catalog."default");

-- Index: stock_move_company_id_index

-- DROP INDEX stock_move_company_id_index;

CREATE INDEX stock_move_company_id_index
  ON stock_move
  USING btree
  (company_id);

-- Index: stock_move_date_expected_index

-- DROP INDEX stock_move_date_expected_index;

CREATE INDEX stock_move_date_expected_index
  ON stock_move
  USING btree
  (date_expected);

-- Index: stock_move_date_index

-- DROP INDEX stock_move_date_index;

CREATE INDEX stock_move_date_index
  ON stock_move
  USING btree
  (date);

-- Index: stock_move_id_idx

-- DROP INDEX stock_move_id_idx;

CREATE INDEX stock_move_id_idx
  ON stock_move
  USING btree
  (id);

-- Index: stock_move_location_dest_id_index

-- DROP INDEX stock_move_location_dest_id_index;

CREATE INDEX stock_move_location_dest_id_index
  ON stock_move
  USING btree
  (location_dest_id);

-- Index: stock_move_location_id_index

-- DROP INDEX stock_move_location_id_index;

CREATE INDEX stock_move_location_id_index
  ON stock_move
  USING btree
  (location_id);

-- Index: stock_move_location_id_location_dest_id_product_id_state

-- DROP INDEX stock_move_location_id_location_dest_id_product_id_state;

CREATE INDEX stock_move_location_id_location_dest_id_product_id_state
  ON stock_move
  USING btree
  (product_id, state COLLATE pg_catalog."default", location_id, 
location_dest_id);

-- Index: stock_move_move_dest_id_index

-- DROP INDEX stock_move_move_dest_id_index;

CREATE INDEX stock_move_move_dest_id_index
  ON stock_move
  USING btree
  (move_dest_id);

-- Index: stock_move_name_index

-- DROP INDEX stock_move_name_index;

CREATE INDEX stock_move_name_index
  ON stock_move
  USING btree
  (name COLLATE pg_catalog."default");

-- Index: stock_move_partner_id_index

-- DROP INDEX stock_move_partner_id_index;

CREATE INDEX stock_move_partner_id_index
  ON stock_move
  USING btree
  (partner_id);

-- Index: stock_move_picking_id_index

-- DROP INDEX stock_move_picking_id_index;

CREATE INDEX stock_move_picking_id_index
  ON stock_move
  USING btree
  (picking_id);

-- Index: stock_move_prodlot_id_index

-- DROP INDEX stock_move_prodlot_id_index;

CREATE INDEX stock_move_prodlot_id_index
  ON stock_move
  USING btree
  (prodlot_id);

-- Index: stock_move_product_id_index

-- DROP INDEX stock_move_product_id_index;

CREATE INDEX stock_move_product_id_index
  ON stock_move
  USING btree
  (product_id);

-- Index: stock_move_purchase_line_id_index

-- DROP INDEX stock_move_purchase_line_id_index;

CREATE INDEX stock_move_purchase_line_id_index
  ON stock_move
  USING btree
  (purchase_line_id);

-- Index: stock_move_sale_line_id_index

-- DROP INDEX stock_move_sale_line_id_index;

CREATE INDEX stock_move_sale_line_id_index
  ON stock_move
  USING btree
  (sale_line_id);

-- Index: stock_move_state_index

-- DROP INDEX stock_move_state_index;

CREATE INDEX stock_move_state_index
  ON stock_move
  USING btree
  (state COLLATE pg_catalog."default");

-- Index: stock_move_tracking_id_index

-- DROP INDEX stock_move_tracking_id_index;

CREATE INDEX stock_move_tracking_id_index
  ON stock_move
  USING btree
  (tracking_id);

 SELECT min(m.id) AS id,
    m.date,
    m.address_id AS partner_id,
    m.location_id,
    m.product_id,
    pt.categ_id AS product_categ_id,
    l.usage AS location_type,
    m.company_id,
    m.state,
    m.prodlot_id,
    COALESCE(sum((- pt.standard_price) * m.product_qty * pu.factor / 
pu2.factor), 0.0) AS value,
    COALESCE(sum((- m.product_qty) * pu.factor / pu2.factor), 0.0) AS 
product_qty
   FROM stock_move m
     LEFT JOIN stock_picking p ON m.picking_id = p.id
     LEFT JOIN product_product pp ON m.product_id = pp.id
     LEFT JOIN product_template pt ON pp.product_tmpl_id = pt.id
     LEFT JOIN product_uom pu ON pt.uom_id = pu.id
     LEFT JOIN product_uom pu2 ON m.product_uom = pu2.id
     LEFT JOIN product_uom u ON m.product_uom = u.id
     LEFT JOIN stock_location l ON m.location_id = l.id
  GROUP BY m.id, m.product_id, m.product_uom, pt.categ_id, m.address_id, 
m.location_id, m.location_dest_id, m.prodlot_id, m.date, m.state, l.usage, 
m.company_id, pt.uom_id
UNION ALL
 SELECT - m.id AS id,
    m.date,
    m.address_id AS partner_id,
    m.location_dest_id AS location_id,
    m.product_id,
    pt.categ_id AS product_categ_id,
    l.usage AS location_type,
    m.company_id,
    m.state,
    m.prodlot_id,
    COALESCE(sum(pt.standard_price * m.product_qty * pu.factor / pu2.factor), 
0.0) AS value,
    COALESCE(sum(m.product_qty * pu.factor / pu2.factor), 0.0) AS product_qty
   FROM stock_move m
     LEFT JOIN stock_picking p ON m.picking_id = p.id
     LEFT JOIN product_product pp ON m.product_id = pp.id
     LEFT JOIN product_template pt ON pp.product_tmpl_id = pt.id
     LEFT JOIN product_uom pu ON pt.uom_id = pu.id
     LEFT JOIN product_uom pu2 ON m.product_uom = pu2.id
     LEFT JOIN product_uom u ON m.product_uom = u.id
     LEFT JOIN stock_location l ON m.location_dest_id = l.id
  GROUP BY m.id, m.product_id, m.product_uom, pt.categ_id, m.address_id, 
m.location_id, m.location_dest_id, m.prodlot_id, m.date, m.state, l.usage, 
m.company_id, pt.uom_id;

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda@postgresql.org)
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda

Responder a