Team assignments for active plans getting revoked

[Rkareko]

There have been reports on dsme channel concerning active plans that have there team assignments revoked.

This is the list of active plans with revoked team assignments

ef7c0827-6850-5699-b983-f6aebcf09423
d1fbdbdb-fa2e-5d67-92d5-49a26736e34c
34836c0d-66b8-5b74-971d-fe4de5e330fe
123dc0e8-6155-502b-9b6f-6685ae9127f7
c3734b31-36ae-511f-a4af-1a1c03ee95e4
ec8b8768-736d-571a-823c-d999125450b2
f9188a0d-e88e-48d6-8e7a-736d13eb4160
eaa14984-bdc6-4270-b4eb-ea1e83e9c2e4
148f5dfe-3796-429d-825b-f7691fa272e6

Here is a sample of team assignments for 2 plans

select * from  team.organization_location where plan_id in (select id from core.plan where identifier = '34836c0d-66b8-5b74-971d-fe4de5e330fe');
   id   | organization_id | location_id | plan_id | from_date  |  to_date   |        duration         
--------+-----------------+-------------+---------+------------+------------+-------------------------
 524378 |             198 |       19533 |    9449 | 2021-12-10 | 2021-12-31 | [2021-12-10,2021-12-31)
 524379 |             243 |       19533 |    9449 | 2021-12-10 | 2021-12-31 | [2021-12-10,2021-12-31)
 524380 |              67 |       19533 |    9449 | 2021-12-10 | 2021-12-31 | [2021-12-10,2021-12-31)
 524416 |             167 |       19533 |    9449 | 2021-12-09 | 2026-12-10 | [2021-12-09,2026-12-11)
 524553 |             191 |       19533 |    9449 | 2021-12-09 | 2026-12-10 | [2021-12-09,2026-12-11)

select * from team.organization_location where plan_id in (select id from core.plan where identifier = 'd1fbdbdb-fa2e-5d67-92d5-49a26736e34c');
id | organization_id | location_id | plan_id | from_date | to_date | duration
--------+-----------------+-------------+---------+------------+------------+-------------------------
524381 | 198 | 18814 | 9448 | 2021-12-10 | 2021-12-31 | [2021-12-10,2021-12-31)
524382 | 243 | 18814 | 9448 | 2021-12-10 | 2021-12-31 | [2021-12-10,2021-12-31)
524383 | 67 | 18814 | 9448 | 2021-12-10 | 2021-12-31 | [2021-12-10,2021-12-31)
524566 | 191 | 18814 | 9448 | 2021-12-09 | 2026-12-10 | [2021-12-09,2026-12-11)
524574 | 167 | 18814 | 9448 | 2021-12-09 | 2026-12-10 | [2021-12-09,2026-12-11)

[Rkareko]

Running a query to check for number of active plans with revoked team assignments returns 51 results

select distinct (plan.id), plan.identifier
from core.plan plan 
join team.organization_location ol on plan.id = ol.plan_id and ol.to_date < '2023-01-01' 
where  json->>'status' = 'active';

 id  |              identifier              
------+--------------------------------------
 1145 | 4755111a-b9cf-5f5c-8cd1-4dd97aa6133c
 1558 | b89f8852-46d5-5271-8433-8cf733d2f6f4
 3444 | b682b88d-3eff-525c-96d6-d4976f29038d
 4294 | 2b9fe4a7-f978-47ca-8e0c-25f9207d15ce
 4661 | 3ebf9d20-f640-5933-aa2c-90f8a9b48399
 4979 | 09828be7-2518-4cae-a627-100a17078d53
 5556 | ef4e3ec4-86f1-408f-9a6f-5f81e3381ea0
 6043 | 6a11675d-573a-4924-96cb-8e8a8f2f3dce
 6061 | a7bb4085-62d0-56d7-9210-92fb78bdcaa0
 6084 | 560b0dd4-5380-5f9f-a111-3342290407cb
 6436 | a2525e3e-5c38-5680-92ec-d4f38bb82d40
 6452 | d5dc0e4b-c8fe-43cd-894c-721aad7b99d9
 6499 | 38325eaf-5cae-483f-b202-15169c0ff8e6
 6545 | 44f89a88-b457-5e43-abab-62ca5fa8197c
 7850 | 148f5dfe-3796-429d-825b-f7691fa272e6
 8083 | fb3e72f4-ad75-5163-91cb-170e4a6ab03c
 8151 | 1880d0e2-66de-5702-b5d7-bb005b5e7fa5
 8275 | 2ac6db6a-8f13-5afc-98da-e614846e59d1
 8276 | 7cac76b0-5cda-51fe-9d77-d387a7d29a5b
 8277 | 25969016-9b00-5b1a-8a5e-5ad955172e3a
 8378 | 447de6c9-5699-591a-b937-a1180786cbea
 8379 | be51d873-e1e4-517e-84a5-63d5ccbaabe8
 8433 | a713191b-4a6e-5353-b423-acf86e7282eb
 8561 | eaa14984-bdc6-4270-b4eb-ea1e83e9c2e4
 8603 | f9188a0d-e88e-48d6-8e7a-736d13eb4160
 8683 | ca86f6b6-36bb-5078-876a-407821ce437e
 8684 | cb640759-ab2f-548f-9566-620f19ca1684
 8685 | b146b8db-3c02-46c9-a577-3a31dea0dcba
 8686 | ea22bd15-a608-5b82-9b7e-f9f452d58c0c
 8687 | ffb3ccce-3612-5617-9edf-ad12507e6e76
 8688 | c9620a41-079a-54e2-833c-47dad9e2c866
 8689 | 8578d0a2-76c7-5a62-81f4-89ec3a962e5a
 8690 | 13ab50ce-502c-4165-9936-8acaec92a525
 8691 | 877a0d0b-5602-4223-bd43-7a2acf2638b1
 8733 | 862f7d9e-8c32-5623-a9a4-c1eb39f9eb51
 8734 | 8608b9e5-1fcb-566c-bad9-383d8aebf2bf
 8738 | 630cd07a-934a-5d4e-830f-410be6263d39
 8787 | a01ee0b8-57a2-5304-9d0f-2284171d471c
 8790 | 6f4f2d26-1671-53e9-9593-b3b7e6e2c585
 9001 | 5417d1ab-eb25-529b-a8db-f8fc3a75d499
 9005 | ec8b8768-736d-571a-823c-d999125450b2
 9018 | 697cca6a-cc8f-435d-9f83-9f1f54ec4d26
 9442 | c3734b31-36ae-511f-a4af-1a1c03ee95e4
 9447 | 123dc0e8-6155-502b-9b6f-6685ae9127f7
 9448 | d1fbdbdb-fa2e-5d67-92d5-49a26736e34c
 9449 | 34836c0d-66b8-5b74-971d-fe4de5e330fe
 9547 | 9846d498-f6d5-5b48-ab6d-507bbc393914
 9548 | 7c1e5681-bdd3-5277-9eb4-ed93b9bb93c9
 9549 | 71c8daf3-885b-4d7a-b72d-563fb7d9ac78
 9550 | 7669151a-8d2e-4f76-b8fe-69dbbc36fe9d
 9997 | 4beceaaa-915a-577f-8d60-a8e5084f4621
(51 rows)

[Rkareko]

Related to #1007

[Rkareko]

This logic on opensrp server core is responsible for revoking team assignments if the revokeAssignments flag is true and the plan status is either retired or complete

For Thailand deployment we set the revoke_assignments flag to false when completing or retiring plans via the PUT API endpoint

[ciremusyoka]

@Rkareko there was a bug that we fixed and got deployed to production on 12/01/2022. Plan end date was being used as the team assignment end date instead of adding 5 years. Maybe some of the plans assignments above were done via the web and are victims of that bug.

[Rkareko]

Looks like this issue does not affect any plans since the above fix was deployed as seen below

select distinct (plan.id), plan.identifier
from core.plan plan
join team.organization_location ol on plan.id = ol.plan_id and ol.to_date < '2023-01-01' and from_date > '2022-01-12'
where json->>'status' = 'active';
 id | identifier 
----+------------
(0 rows)

[Rkareko]

@samkanga We will need to run a script to update the to_date and duration values for the affected team assignments