Stormwater - Update _NUM trigger on SW_TRACKING and BMP_POIS

[leeensminger]

Update the trigger on both SW_TRACKING and BMP_POIS so that the six-digit sequential number is unique across both tables - in other words, that the six digit number will not be duplicated within either of the tables: BMP_POIS.BMP_POI_NUM SW_TRACKING.SW_TRACKING_NUM

The reason being: The ETL gets all source BMPs that have a source POI, and loads them to the target database, using the BMP_POINUM. For source BMPs that do not have a source POI, the system takes those source BMPs, loads them to the target BMP table AND_ the target BMPPOI table. This is all by design. When this BMPPOI load happens, there is no MDE number available for the ETL to load. This is where the problem arises.

So, we will have to make the six digit number unique for both tables, and the ETL will:

1. Take the SW_TRACKING_NUM that gets used for the target BMP table load
2. Strip the "BMP" prefix
3. Apply the "POI" prefix
4. Load to the target BMPPOI table using this new number. Since the loaded records will sit parallel to actual, source BMP_POIS, the number can't be duplicated - hence the update to the trigger.

[leeensminger]

Self assigning to capture the thought and review again to confirm before passing to Ehsan and Gerry

[leeensminger]

@BC89 @dhenry-KCI Note the implications to the database; any other effects downstream we are not considering?

[ehsan67]

@leeensminger It is possible to implement the following proposal by using one Sequence for both triggers and it wouldn't affect other places in DB

"Update the trigger on both SW_TRACKING and BMP_POIS so that the six-digit sequential number is unique across both tables - in other words, that the six digit number will not be duplicated within either of the tables: BMP_POIS.BMP_POI_NUM SW_TRACKING.SW_TRACKING_NUM"

[ehsan67]

@leeensminger Updated in KCI

[leeensminger]

Confirmed good