maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_reasonproduct(character varying, integer)
Parameters
Name
Type
Mode
_serialnumber
character varying
IN
_idproduct
integer
IN
Definition
DECLARE r record; /* c prefix meaning current */ _cidtechnicalaccount INTEGER; _cidclient INTEGER; _cidproduct INTEGER; /* n prefix meaning new */ _nidtechnicalaccount INTEGER; _nidproduct INTEGER; BEGIN SELECT c.idclient, t.idtechnicalaccount, t.idproduct INTO _cidclient, _cidtechnicalaccount, _cidproduct FROM device d JOIN technicalaccount t ON t.idtechnicalaccount = d.idtechnicalaccount JOIN client c ON c.idclient = t.idclient WHERE ltrim(d.serialnumber,'0') = _serialnumber; IF _cidproduct <> _idproduct THEN /* Move it to the appropriate technical account if it exists */ SELECT t.idtechnicalaccount INTO _nidtechnicalaccount FROM technicalaccount t WHERE t.idclient = _cidclient AND t.idproduct = _nidproduct; IF _nidtechnicalaccount IS NOT NULL THEN UPDATE device d SET idtechnicalaccount = _nidtechnicalaccount WHERE ltrim(d.serialnumber,'0') = _serialnumber; ELSE INSERT INTO technicalaccount SELECT nextval('sec_technicalaccount'),_cidclient,'','',0,now(),0,now(),'DBA',true,0,null,null,_idproduct; UPDATE device d SET idtechnicalaccount = currval('sec_technicalaccount') WHERE ltrim(d.serialnumber,'0') = _serialnumber; END IF; END IF; RETURN true; END;