Fixed Asset Code

-- select * from vw_codigo_activo ca;

CREATE VIEW vw_codigo_activo AS
SELECT a.u_activo_id AS id_activo,
       a.codigo AS cod_activo,
       a.nombre AS activo,
       ga.u_grupoactivo_id AS id_grupo,
       ga.codigo AS cod_grupo,
       ga.nombre AS grupo,
       o.ad_org_id AS id_sucursal,
       o.name AS sucursal,
       cs.codigo AS cod_sucursal,
       row_number() over(partition BY cast(cs.codigo AS integer)
                         ORDER BY cast(cs.codigo AS integer) ASC, cast(ga.codigo AS integer) ASC, a.u_activo_id ASC) AS consecutivo,
       ga.codigo || cs.codigo || row_number() over(partition BY cast(cs.codigo AS integer)
                                                   ORDER BY cast(cs.codigo AS integer) ASC, cast(ga.codigo AS integer) ASC, a.u_activo_id ASC) AS nuevo_codigo
FROM u_activo a
JOIN u_grupoactivo ga ON a.u_grupoactivo_id = ga.u_grupoactivo_id
JOIN ad_org o ON ga.ad_org_id = o.ad_org_id
JOIN nic_codigo_sucursal cs ON o.ad_org_id = cs.ad_org_id
WHERE a.ad_client_id = 1000001
  AND a.isactive = 'Y'
ORDER BY cast(cs.codigo AS integer) ASC, cast(ga.codigo AS integer) ASC, a.u_activo_id ASC;