Selection Set 1C - add BMPPOI to existing selection set

[leeensminger]

Every target RestBMP output needs to also have an MDE.BMPPOI record. This includes Conversions, and when selection set 1C Part 1 records are loaded to MDE.RestBMP, we need to find associated BMPPOIs in the source, and load them to MDE.BMPPOI. The relationship in the target database (between RestBMP and BMPPOI) is expected to be 1:1. The data in the source is expected to support this - but ideally, we insert "orphan" in RestBMP.BMPPOI_ID when the data does not support this.

The template MDE geodatabase already provides for this relationship with a RestBMP.BMPPOI_ID field (required field). Currently, we populate this RestBMP.BMPPOI_ID field as "orphan".

• [ ] Please update the selection set logic as highlighted below:

• [ ] Update RestBMP.BMPPOI_ID to be populated with the MDE ID for the associated BMPPOI

• [ ] Implement the field-by-field source to target for BMPPOIs. The source to target is not different than it is for selection set 1D, but the original v8 Excel document has been updated to include this logic within selection set 1C, and those rows copied here:

Entity_Type	Entity_Name	Field_Name	Type	Length	Domain/Lookup_File	isNullable	ETL_STEP_NUM	Selection_Set_Reference	Source	Source_Type	Source_Length	Source_Domain	Source_isNullable	Nullable_Fields_Match	Translation
Feature Class	BMPPOI	APPR_DATE	Date	8		FALSE	822	1C	BMP_POIS.APPR_DATE	Date	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	BMPPOI_ID	String	13		FALSE	823	1C	BMP_POIS.BMP_POI_NUM	String	20		TRUE	Possible nullable mismatch	"BA" + YY +  BMP_POI_NUM YY = Rightmost 2 characters of REPORTING_YEAR
Feature Class	BMPPOI	BUILT_DATE	Date	8		FALSE	824	1C	BMP_POIS.APPR_DATE	Date	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	GEN_COMMENTS	String	255		TRUE	825	1C	BMP_POIS.MDE_COMMENTS	String	255		TRUE		None
Feature Class	BMPPOI	IMP_ACRES	Double	8		FALSE	826	1C	BMP_DAS.IMP_ACRES	Double	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	LAND_USE	SmallInteger	2	dMDPLandUse	FALSE	827	1C	Derive from associated spatial overlay view						None
Feature Class	BMPPOI	LAST_CHANGE	Date	8		FALSE	828	1C	None						Set all values as current system date of ETL processing.
Feature Class	BMPPOI	LU_COUNTY	String	25		TRUE	829	1C	None						None
Feature Class	BMPPOI	MD_EAST	Double	8		FALSE	830	1C	Derive on the fly						None
Feature Class	BMPPOI	MD_NORTH	Double	8		FALSE	831	1C	Derive on the fly						None
Feature Class	BMPPOI	PE_ADR	Double	8		FALSE	832	1C	BMP_POIS.PE_ADR	Double	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	PE_REQ	Double	8		FALSE	833	1C	BMP_POIS.PE_REQ	Double	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	PERMIT_NUM	String	11		FALSE	834	1C	target.PermitInfo.PERMIT_NUM	String			FALSE		Find in mde.PermitInfo where PermitInfo.REPORTING_YEAR = user-input Reporting Year, then get mde.PermitInfo.PERMIT_NUM.
Feature Class	BMPPOI	Q_POST	Double	8		TRUE	835	1C	BMP_POIS.Q_POST	Double	8		TRUE		None
Feature Class	BMPPOI	Q_PRE	Double	8		TRUE	836	1C	BMP_POIS.Q_PRE	Double	8		TRUE		None
Feature Class	BMPPOI	Q_WOODS	Double	8		TRUE	837	1C	BMP_POIS.Q_WOODS	Double	8		TRUE		None
Feature Class	BMPPOI	RCN_POST	SmallInteger	2		TRUE	838	1C	BMP_POIS.RCN_PRE	SmallInteger	2		TRUE		None
Feature Class	BMPPOI	RCN_PRE	SmallInteger	2		TRUE	839	1C	BMP_POIS.RCN_POST	SmallInteger	2		TRUE		None
Feature Class	BMPPOI	RCN_WOODS	SmallInteger	2		TRUE	840	1C	BMP_POIS.RCN_WOODS	SmallInteger	2		TRUE		None
Feature Class	BMPPOI	WATERSHED12DGT	String	12	dHUC12digit	TRUE	841	1C	Derive from associated spatial overlay view						None
Feature Class	BMPPOI	WATERSHED8DGT	String	8	dMD8digit	FALSE	842	1C	Derive from associated spatial overlay view						None

[leeensminger]

@gerrykelly The data in KCI dev has been updated to support this. For 38 records in SW_TRACKING for selection set 1C part 1, there are 38 records in BMPs, and now there are 38 records in BMP_POIS. BMP_POIS.MDE_COMMENTS = "Selection set 1C - part 1".

Don't forget there are two records in BMPS where MDE COMMENTS indiciates selection set 1C, but the reporting year is 2099, thus shouldn't be migrated.

[gerrykelly]

@leeensminger In a way it looks like the fix for #78 fixed this without me needing to do anything more. But there might be some quirks I still need to address. I'm just turning it back to you to see if you think that it now comes out right for all the set 1C features/records

[leeensminger]

@gerrykelly @dhenry-KCI Looks OK at KCI