Migrate historical requisitions' adjustments into stock data


This ticket adds a Data Migration that runs once, upon startup as a manually-triggered data transfer script to migrate all existing Requisitions in the Requisition Service to have their "Total Losses and Adjustments" data migrated to use Reasons from the Stock Management service. UI work is in a separate related ticket OLMIS-2694.

See https://openlmis.atlassian.net/wiki/spaces/OP/pages/114234797/Connecting+Stock+Management+and+Requisition+Services

State Before Data Migration

  1. Each Requisition Line Item may have any number of adjustments (zero, one, or multiple adjustments) saved in the Requisition service stock_adjustments database table. Each adjustment has a UUID called "reasonid" that points to a reason in the ReferenceData service stock_adjustment_reasons table.

  2. Each Requisition Line Item also has a "totallossesandadjustments" integer value saved in the requisition_line_items table.

  3. The Reasons are pulled from the ReferenceData service LIVE, as-needed directly by the UI. The UI seems to be caching the list of reasons for offline usage.

    • There is no "snapshot" functionality for reasons within the Requisition service. So if the list of reasons changes over time, the system is not smart enough to keep an older snapshot of the list with older requisitions and a newer snapshot of the list with newer requisitions. For more about the Snapshot concept, see OLMIS-2694.

  4. Each Reason in ReferenceData contains a Description, DisplayOrder, and Name, and must have one ProgramID. If one reason is

State After Data Migration

  1. Each Requisition Line Item may have any number of adjustments (zero, one, or multiple adjustments), but each adjustment now has a Reason UUID that points to a valid reason in the Stock Management service.

  2. Each Requisition Line Item still has a "totallossesandadjustments" integer value. This integer and the total of all the adjustments should still add up to the same number as before the migration.

  3. The Reasons are pulled from Stock Management service ONCE by the Requisition service, not over and over as-needed by the UI.

    • The list of valid reasons is stored in a Snapshot inside each Requisition. The snapshot of the list of reasons is a safer way to support offline editing when the list of reasons might change over time. For more about the Snapshot concept, see OLMIS-2694.

Edge Cases
After an implementation upgrades to a new version of OpenLMIS with this data migration and feature, there will be some users whose browsers have an older copy of OpenLMIS UI App cached and/or older copies of Requisitions cached. When those users try to save their old requisitions, they will get an error message. These edge cases are in the scope of related ticket OLMIS-2694.

Data Migration Steps
Some of these steps may happen in a Flyway schema migration, while others may happen in a stand-alone Data Transfer Script (referred to as "Option 2" when discussed at the Tech Committee). The data migration must include:

  1. Schema changes are applied inside Requisition database providing any tables or columns to hold the new information including the snapshot list of valid reasons with each requisition (probably this step happens using standard schemas with Flyway).

  2. Migrate all the Reasons in ReferenceData into Stock Management stock_card_line_item_reasons and valid_reason_assignments tables.

    • This is probably best done by calling the Stock Management APIs to configure reasons. The APIs are POST /api/stockCardLineItemReasons and POST /api/validReasons. These API calls can be done from Requisition Service to Stock Service with an admin/service token.

    • Note that the data model is slightly different: Stock reasons can apply to multiple Programs by having multiple valid_reason_assignments. ReferenceData Reasons are not configured by Facility Type. So when we insert our reasons into Stock, we will need to enable the reasons for ALL facility types. Later on admins can change that to be more granular (if only certain facility types should be allowed to use a reason). That is a new granularity not available with the ReferenceData reasons. Furthermore, because the ReferenceData reasons were only tied to a single Program, the data is repeated (eg, there is a different "Transfer In" reason with a different UUID for all 5 programs). When we migrate to Stock Management, if the reasons have the same properties (name, description, additive flag) then we want to just have one reason with one UUID that is assigned to all 5 programs.

    • Do not delete the reasons out of ReferenceData. We can leave them there. That table and those endpoints will be deprecated in and will go away soon.

  3. Update all pre-existing Requisitions to have a saved snapshot of valid reasons. (This step is a cross-service data transfer that would be part of the script.)

    • Query Stock Management GET /api/validReasons to get a current list of valid reasons for the program and facility type for each Requisition. Store that in Requisition service database. In related ticket we will be doing the UI work to use this snapshot list of reasons, and we will be creating a snapshot for each future requisition during Initiation.

    • When this date migration re-saves the requisitions, it will update their "modifiedDate" timestamp. This is important because it will mean that users who have a local cached copy of this requisition in their UI will get a message preventing them from saving the old one. The UI work and testing for this edge case is in OLMIS-2694. But in this ticket we need to make sure the timestamp is truly updated.

  4. Update all pre-existing Requisition Line Items to point to Stock reasons. (This step is also a cross-service data change that would be part of the script.)

    • Change the UUIDs in Requisition Line Items (requisition.stock_adjustments) to point to the snapshot list of reasons that was pulled from Stock Management service. Obviously we want to point to an equivalent reason with the same properties (Name, Additive, Description) and valid for Program and FacilityType.

  5. Set a flag (or equivalent mechanism) so that this data migration will not run again to prevent this one-time data migration from being run a second time. It should only be executed once, the very first time the Requisition service is booted up after upgrading manually triggered by the Administrator/Implementer likely using a command line interface. It should do all these steps and upgrade every existing Requisition at the time it runs. After that, new Requisitions initiated will get their snapshot.

Acceptance Criteria:

  • Trigger this Migration: When an admin upgrades to a version of OpenLMIS Requisition Service with this feature, they can trigger a Data Transfer Script. Using either Flyway migrations and/or this script that it runs on service start-up all of the data migrations/updates will happen as described above. This can be an Application Runner Bean in Java Spring Boot. It would be set to run once on service start-up. It would have a way to record/check if it has been run before. It only ever runs can be executed once (if the admin accidentally tries to run it again, it tells them it cannot run a second time).

  • The script should ideally be dockerized - the implementer should only need to download a Docker image and run it to do the migration.

  • Any errors conducting the migration should write to the log file. If it succeeds, a friendly migration success message should print to the log.

  • This migration applies to all Requisitions anywhere in the workflow, both historical and current drafts (initiated, submitted, authorized, in_approval, approved, released, rejected). Test out that the migration works for a requisition in each status and that the requisition can still be viewed/edited as appropriate in the workflow.

  • Users who open the requisition do not see any change and can continue working with or viewing any requisition they have permissions to. The data itself about each loss/adjustment is pointing to reason UUIDs within the Stock service. The end-user experience before and after this ticket is not altered.

  • Cross-check with Acceptance Criteria of OLMIS-2694, because that ticket includes CHANGELOG and testing other edge cases.

  • Provide documentation of how the admin/implementer triggers this migration, and any setup needed in order to run it (do you have to configure the PostgreSQL data sources/targets?). This should be included in the CHANGELOG and we will reference it from the RELEASE NOTES as well.


Paweł Gesek
August 23, 2017, 4:32 PM

one more note, since the migration is Dockerized, it basically can be ran using a single line in the console:

We can also use the version with the conf file:

Paweł Gesek
August 23, 2017, 5:25 PM

I am currently working with on running the migration on an RDS instance with a Malawi dump.

Łukasz Lewczyński
August 24, 2017, 12:29 PM

I executed migration on production data (on malawi dev server):

Also I executed test sqls:

  • test-sql/refdata_reasons.sql -> 71 rows

  • test-sql/refdata_reasons_used_by_requisitions.sql -> 0 rows

  • test-sql/requisition_reason_snapshots.sql -> 3383982 rows

  • test-sql/stock_reasons.sql -> 15 rows

  • test-sql/stock_reasons_used_by_requisitions.sql -> 8 rows

  • test-sql/stock_valid_assignments.sql -> 923 rows


Paweł Gesek
August 24, 2017, 2:29 PM

In case someone is wondering about the 'WARNING', it wasn't a significant issue, but was fixed.

Brandon Bowersox-Johnson
August 24, 2017, 7:04 PM

One more edge case that we should specifically test is Emergency requisitions, to make sure if an in-progress Emergency Requisition migrates, after the migration its Total Losses and Adjustments values still work properly. That's probably assumed and probably already covered...but please do test for that if we have not already.



Paweł Gesek


Brandon Bowersox-Johnson


Story Points


Time tracking


Time remaining


Epic Link




Fix versions