maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_ovmrecon_productonom()
Parameters
Name
Type
Mode
Definition
DECLARE r RECORD; _idclient int; _idproduct int; _idtechnicalaccount int; _counter int := 0; BEGIN DROP TABLE IF EXISTS t; CREATE TEMPORARY TABLE t (serialnumber varchar, productonom varchar); COPY t FROM '/db/ovm_recon/reason_productonom.csv' header csv; FOR r IN SELECT serialnumber,productonom FROM t LOOP /* Get the original customer */ SELECT c.idclient INTO _idclient FROM device d JOIN technicalaccount t ON t.idtechnicalaccount = d.idtechnicalaccount JOIN client c ON c.idclient = t.idclient WHERE ltrim(d.serialnumber,'0') = r.serialnumber; /* Asign the new technicalaccount */ SELECT t.idtechnicalaccount INTO _idtechnicalaccount FROM technicalaccount t JOIN client c ON c.idclient = t.idclient WHERE c.idclient = _idclient AND t.idproduct = (CASE r.productonom WHEN 'SH' THEN 2 WHEN 'AEF' THEN 1 WHEN 'ARMOR' THEN 3 END); IF FOUND THEN UPDATE device d SET idtechnicalaccount = _idtechnicalaccount WHERE ltrim(serialnumber,'0') = r.serialnumber; _counter := _counter + 1; END IF; END LOOP; RETURN _counter; END;