maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_move_from_cnr()
Parameters
Name
Type
Mode
Definition
with q as (select d.serialnumber,d.lastgpsdate,t.clientenum,t.productonom from device d JOIN staging.vw_qcsntoday_cc t ON ltrim(d.serialnumber,'0') = t.serialnumber where idtechnicalaccount in (74,5) and lastgpsdate > now()::date and t.clientenum <> '000000' and t.statusfacturacion = 'Active' and t.servicio = 'Active'), q1 as ( update device d set status = 1, enabled = 't', idtechnicalaccount = (select idtechnicalaccount from technicalaccount t JOIN client c ON c.idclient = t.idclient JOIN q ON q.clientenum = c.erpid AND d.serialnumber = q.serialnumber and c.idproduct = (case q.productonom when 'AEF' THEN 1 WHEN 'SH' THEN 2 WHEN 'ARMOR' THEN 4 ELSE 0 END)) FROM q WHERE d.serialnumber = q.serialnumber RETURNING d.*) INSERT INTO staging.moved_devices(serialnumber,idtechnicalaccount) select q1.serialnumber,q1.idtechnicalaccount from q1;