maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_ovmrecon_custid()
Parameters
Name
Type
Mode
Definition
DECLARE r RECORD; _idprivilegegroup int; _idproduct int; _idtechnicalaccount int; _counter int := 0; BEGIN DROP TABLE IF EXISTS t; CREATE TEMPORARY TABLE t (serialnumber varchar, custid varchar); COPY t FROM '/db/ovm_recon/reason_custid.csv' header csv; FOR r IN SELECT serialnumber,custid FROM t LOOP /* Get the original idprivilege and idproduct */ SELECT c.idprivilegegroup,t.idproduct INTO _idprivilegegroup,_idproduct 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; /*RAISE WARNING 'idprivilegegroup % idproduct %',_idprivilegegroup,_idproduct;*/ /* Asign the new custid based on the original product attributes */ SELECT t.idtechnicalaccount INTO _idtechnicalaccount FROM technicalaccount t JOIN client c ON c.idclient = t.idclient WHERE c.idprivilegegroup = _idprivilegegroup AND c.erpid = r.custid AND t.idproduct = _idproduct; IF FOUND THEN /*RAISE WARNING 'new idtechnicalaccount %',_idtechnicalaccount;*/ UPDATE device d SET idtechnicalaccount = _idtechnicalaccount WHERE ltrim(serialnumber,'0') = r.serialnumber; _counter := _counter + 1; END IF; END LOOP; RETURN _counter; END;