maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_featuresmigration_phase1()
Parameters
Name
Type
Mode
Definition
/* v20151109 RRB Initial release v20160217 RRB Initial release To migrate devices to their proper technical accounts for features release, where for each product there will be a technicalaccount At this stage the device is kept in the same customer account, in a subsequent phase customer accounts with the same erpid will be merged into one */ DECLARE nsrecord record; /* c prefix meaning current */ _cidtechnicalaccount INTEGER; _cidclient INTEGER; _cidproduct INTEGER; /* n prefix meaning new */ _nidtechnicalaccount INTEGER; _nidproduct INTEGER; BEGIN /* To align devices to their correspondent technicalaccount.idproduct */ FOR nsrecord IN SELECT * FROM staging.vw_qcsntoday_cc LOOP /* Get current device status */ 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') = nsrecord.serialnumber; /* Map NS product code to CC product catalog */ IF nsrecord.productonom = 'AEF' THEN _nidproduct := 1; ELSIF nsrecord.productonom = 'SH' THEN _nidproduct := 2; ELSIF nsrecord.productonom = 'ARMOR' THEN _nidproduct := 3; ELSIF nsrecord.productonom = 'TT Guardian' THEN _nidproduct := 4; ELSIF nsrecord.productonom = 'FSS' THEN _nidproduct := 5; ELSIF nsrecord.productonom = 'FSC' THEN _nidproduct := 6; ELSIF nsrecord.productonom = 'FSCC' THEN _nidproduct := 7; ELSIF nsrecord.productonom = 'FSE' THEN _nidproduct := 8; ELSIF nsrecord.productonom = 'SPY' THEN _nidproduct := 9; ELSIF nsrecord.productonom = 'FSCV2' THEN _nidproduct := 10; ELSIF nsrecord.productonom = 'FSCCV2' THEN _nidproduct := 11; ELSIF nsrecord.productonom = 'FSEV2' THEN _nidproduct := 12; ELSIF nsrecord.productonom = 'FSSV2' THEN _nidproduct := 13; ELSIF nsrecord.productonom = 'FSSV3' THEN _nidproduct := 14; ELSIF nsrecord.productonom = 'EE' THEN _nidproduct := 15; END IF; IF _cidproduct <> _nidproduct 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 RAISE WARNING 'serialnumber:% , old_idtechnicalaccount:% , new_idtechnicalaccount:% ',nsrecord.serialnumber,_cidtechnicalaccount,_nidtechnicalaccount; UPDATE device d SET idtechnicalaccount = _nidtechnicalaccount WHERE ltrim(d.serialnumber,'0') = nsrecord.serialnumber; ELSE INSERT INTO technicalaccount SELECT nextval('sec_technicalaccount'),_cidclient,'','',0,now(),0,now(),'DBA',true,0,null,null,_nidproduct; RAISE WARNING 'serialnumber:% , old_idtechnicalaccount:% , new_idtechnicalaccount:% ',nsrecord.serialnumber,_cidtechnicalaccount,currval('sec_technicalaccount'); UPDATE device d SET idtechnicalaccount = currval('sec_technicalaccount') WHERE ltrim(d.serialnumber,'0') = nsrecord.serialnumber; END IF; END IF; END LOOP; END;