A‐Class Area of Use ‐ Seed Plan Zone

Description

This page details how SPZ (Seed Plan Zone) data is fetched, stored then displayed.

Fetch

When a seedlot is registered, there will be a primary Orchard ID associated with this seedlot, it is chosen by the user upon registration. A user can select up to 2 orchards for a seedlot but the first one will be defaulted as the primary orchard.

When the system (new postgres backend) receives a request to complete the registration of a seedlot, determined by the seedlot status going from pending -> submitted, the backend should do the following to fetch the SPZ data from oracle database.

1. Use the primary orchard id to get the spu_id from the postgres table active_orchard_spu
2. Send the spu_id to oracle backend service
3. The oracle backend service uses the spu_id to look up the TESTED_PT_ARE_OF_USE_ID in the TESTED_PT_ARE_OF_USE table.
   • If no TESTED_PT_ARE_OF_USE_ID is found with the provided spu_id, return an empty list.
4. With the found TESTED_PT_ARE_OF_USE_ID, query for a list of SEED_PLAN_ZONE_CODE that has the TESTED_PT_ARE_OF_USE_ID on the TESTED_PT_ARE_OF_USE_SPZ table. The SPZ_CODE that has the primary_ind = 'Y' will be used as the primary SPZ. The rest are additional SPZs.
5. The oracle backend service then sends this list of SPZs to the postgres backend that requested this service, in the format of

   • [
      {
        "code": "BV"
        "description": "Bulkley Valley"
        "primaryInd": true
      },
      ...
     ]

   • the description is fetched from the oracle SEED_PLAN_ZONE_CODE table.

Store

1. After receive the respond from oracle, our postgres backend will insert x = length of the response list amount of rows in to the seedlot_seed_plan_zone table on postgres.
   • Note that only one of the SPZ of a seedlot can have primaryInd = true. In the edge case where more than 1 spz have the primary_id = true, use the first option then set the rest as false.

Display

1. The SPAR front-end will fetch a list of seedlot_spz rows from postgres with a seedlot number, the row with primary_ind = true will be displayed as primary, then the rest in the additional spz section.
2. A authorized user can modify any of these SPZs by selecting a dropdown with a list of available SPZs. These SPZs are NOT a list from the SEED_PLAN_ZONE_CODE on oracle. They are unique SEED_PLAN_ZONE_CODE values from the TESTED_PT_ARE_OF_USE_SPZ table. In order to display this list, another endpoint will be used to fetch them from oracle.