Initially in Demands, consumer code has the pattern like <PropertyId>:<Assessment No>. Now it has been decided to change the consumer code to pattern like <PropertyId> (just for reference) and check how the product works.

So to change the value of consumer code in database table(egbs_demand_v1), the migration script has been written the following step.

1. Create a backup of the table egbs_demand_v1.

2. Split the data from column consumercode base on the character ‘:’ like 

 String _first-part  : String _second-part  then column contains String _first-part  from old old pattern.

During these changes, the logic behind the other column has not been changed, those remained the same as they were earlier.The above steps is to split the old consumercode pattern and make a new one.

After split it has been found that there are  multiple demand records of the same consumer code whose status is active for some financial year. To correct this error, table data must be updated. So that for a particular financial year, out of the multiple records of the same consumer code, the demand record which is recently modified / created only that record have the status value set to ACTIVE and other records have status value set to INACTIVE  

Migration script:

CREATE TABLE demand_v3_copy
(
    id character varying(64) NOT NULL,
    consumercode character varying(250) NOT NULL,
    consumertype character varying(250) NOT NULL,
    businessservice character varying(250) NOT NULL,
    payer character varying(250),
    taxperiodfrom bigint NOT NULL,
    taxperiodto bigint NOT NULL,
    createdby character varying(256) NOT NULL,
    createdtime bigint NOT NULL,
    lastmodifiedby character varying(256),
    lastmodifiedtime bigint,
    tenantid character varying(250) NOT NULL,
    minimumamountpayable numeric(12,2),
    status character varying(64),
    additionaldetails json,
    billexpirytime bigint
);

INSERT INTO demand_v3_copy SELECT * FROM egbs_demand_v1;

UPDATE egbs_demand_v1 SET consumercode = SPLIT_PART(consumercode,':',1);

UPDATE egbs_demand_v1 SET status = 'INACTIVE' FROM (SELECT consumercode,taxperiodfrom,taxperiodto, MAX(lastmodifiedtime) AS MAXTIME FROM egbs_demand_v1 GROUP BY consumercode,taxperiodfrom,taxperiodto) AS demand WHERE egbs_demand_v1.consumercode = demand.consumercode AND egbs_demand_v1.taxperiodfrom = demand.taxperiodfrom AND egbs_demand_v1.taxperiodto = demand.taxperiodto AND egbs_demand_v1.lastmodifiedtime < demand.MAXTIME;