Mensual

Home   »   Mensual

Select * from(
Select
c.tx_code Afiliado,
c.tx_name_commerce Nombre,
c.estado,
a.modelo modelo,
a.serial Serial,
a.propietario Propietario,
a.estado as "Estado Dispositivo",
trunc(sysdate)- b.fecha Dias,
to_char(b.fecha,'DD-MM-YYYY') "Ultima Transaccion"
from
(
select distinct d.nu_id,dt.nb_name modelo,d.tx_serial serial,do.nb_name propietario,decode(d.in_is_deleted,0,'Activo',1,'Dado de baja') Estado from c007t_device d,I011t_device_type dt,i030t_device_owner do where d.in_type=dt.nu_id and do.nu_id=d.in_owner) a,
(select distinct nu_device,nu_entity,trunc(max(fe_created_on)) fecha from c001t_transaction where in_state=1 and in_result=1 group by nu_device,nu_entity) b,
(select e.nu_id,e.tx_code , e.tx_name_commerce,nvl(l.nb_name,'') estado from c003t_entity e,i036T_state_location l where e.tx_state_code(+)=l.tx_state_code and e.in_is_deleted=0) c
where a.nu_id=b.nu_device and c.nu_id=b.nu_entity order by 3,7
) where dias>=0;
 

Leave a Reply

Your email address will not be published.