maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_shipperconf_unitstodefaultcoverage()
Parameters
Name
Type
Mode
Definition
/* v20160608 Move any unassigned units in a shipperconf configuration to the default coverage */ DECLARE r_shipper record; r_units record; _iddefaultgroup int; counter int; BEGIN SET statement_timeout = '5 s'; counter := 0; /* What units can we see based on shipperconf configurations */ /* For each customer account that is receiving units */ FOR r_shipper IN (SELECT idclientshipper FROM shipperconf WHERE enabled = true and idclientshipper = 4473) LOOP SELECT cou.idunitgroup INTO _iddefaultgroup FROM unitgroup ug JOIN coverage_unitgroup cou ON cou.idunitgroup = ug.idunitgroup JOIN coverage co ON co.idcoverage = cou.idcoverage WHERE ug.idclient = r_shipper.idclientshipper AND co.alias ilike '%default%' AND ug.enabled = true AND co.enabled = true order by idunitgroup limit 1; /* Get a list of such units and extract the units that are already in one unit group from the shipper */ FOR r_units IN (SELECT distinct idunit FROM vw_shipperconf WHERE idclientshipper = r_shipper.idclientshipper EXCEPT SELECT idunit FROM unit_unitgroup WHERE idunitgroup = _iddefaultgroup AND enabled = true) LOOP IF _iddefaultgroup IS NOT NULL THEN EXECUTE format('INSERT INTO public.unit_unitgroup (idunit,idunitgroup,createbyuser,creationdate,lastmodifiedbyuser,lastmodificationdate,originatingsystem,enabled) VALUES (%s,%s,%s,%L,%s,%L,%L,%L) ON CONFLICT ON CONSTRAINT unit_unitgroup_pkey DO UPDATE SET enabled = true',r_units.idunit,_iddefaultgroup,0,now(),0,now(),'DBA-ShipperAddUnit',true); counter := counter + 1; END IF; END LOOP; END LOOP; RETURN counter; END;