Asset History

-- select * from vw_historial_activo ha;
 -- drop view vw_historial_activo;

CREATE VIEW vw_historial_activo AS
SELECT a.ad_client_id,
       o.ad_org_id AS id_centro_costo,
       o.name AS centro_costo,
       a.u_activo_id AS id_activo,
       a.codigo AS cod_activo,
       a.nombre AS activo,
       a.descripcion,
       a.serialno,
       ma.nic_marca_id AS id_marca,
       ma.name AS marca,
       mo.nic_modelo_id AS id_modelo,
       mo.name AS modelo,
       ga.u_grupoactivo_id AS id_grupo,
       ga.codigo AS cod_grupo,
       ga.nombre AS grupo,
       og.ad_org_id AS grupo_org_id,
       og.name AS grupo_org,
       vc.c_validcombination_id AS id_combinacion,
       vc.combination AS combinacion,
       vc.description AS desc_combinacion,
       vc.ad_org_id AS combinacion_org_id,
       vc.account_id AS combinacion_cta_id,
       ca.u_claseactivo_id AS id_clase,
       ca.codigo AS cod_clase,
       ca.nombre AS clase,
       ca.descripcion AS desc_clase,
       a.area_terreno,
       a.registro_catastral,
       a.registro_publico,
       a.num_factura,
       bp.c_bpartner_id AS id_comprador,
       bp.value AS cod_comprador,
       bp.name AS comprador,
       a.num_chasis,
       a.num_placa,
       a.bueno,
       a.regular,
       a.depreciado,
       a.baja,
       a.fechacompra,
       a.fechabaja,
       a.totalmeses,
       a.valoractual,
       a.valordepreciado,
       a.valorinicial,
       a.periodofinal,
       a.periodoinicial,
       ha.u_historialactivo_id AS id_historial_activo,
       e.c_bpartner_id AS id_empleado,
       e.value AS cod_empleado,
       e.name AS empleado,
       u.u_ubicacion_id AS id_ubicacion,
       u.codigo AS cod_ubicacion,
       u.nombre AS ubicacion,
       ed.u_edificio_id AS id_edificio,
       ed.codigo AS cod_edificio,
       ed.nombre AS edificio
FROM u_activo a
JOIN ad_org o ON a.ad_org_id = o.ad_org_id
LEFT OUTER JOIN nic_marca ma ON a.nic_marca_id = ma.nic_marca_id
LEFT OUTER JOIN nic_modelo mo ON a.nic_modelo_id = mo.nic_modelo_id
JOIN u_grupoactivo ga ON a.u_grupoactivo_id = ga.u_grupoactivo_id
JOIN ad_org og ON ga.ad_org_id = og.ad_org_id
LEFT OUTER JOIN c_validcombination vc ON ga.c_validcombination_id = vc.c_validcombination_id
JOIN u_claseactivo ca ON ga.u_claseactivo_id = ca.u_claseactivo_id
LEFT OUTER JOIN c_bpartner bp ON a.c_bpartner_id = bp.c_bpartner_id
LEFT OUTER JOIN u_historialactivo ha ON a.u_activo_id = ha.u_activo_id
LEFT OUTER JOIN c_bpartner e ON ha.c_bpartner_id = e.c_bpartner_id
LEFT OUTER JOIN u_ubicacion u ON ha.u_ubicacion_id = u.u_ubicacion_id
LEFT OUTER JOIN u_edificio ed ON u.u_edificio_id = ed.u_edificio_id
WHERE a.ad_client_id = 1000001
  AND a.isactive = 'Y';