Add SFCLAM metadata doc

[mle2718]

no SFCLAM documentation.

[mle2718]

Need to include this.

BACKGROUND: Since the inception of SF/OQ vessel logbook reporting, SF/OQ data has resided in its own schema, SFCLAM. Further, reported bycatch of non SF/OQ species from those trips were reported separately via a Vessel Trip Report (VTR) and resided in the VTR database commonly known as CATCH, IMAGES, DOCUMENT (CID).

As part of the regional effort to modernize our fisheries dependent data collection systems;

On November 1, 2024, GARFO will begin inserting all new SF/OQ data into CID while also continuing to insert it into SFCLAM.

All legacy data prior to November 1, 2024 will only be accessible in SFCLAM.

There are no plans at this time to migrate legacy SFCLAM data into CID.

If you have historically joined the SFCLAM and CID tables to query vessel records, you may need to modify your code to avoid duplicating results.

End users may notice a minor change to the SF/OQ data format; specifically, a new column (RECORD_SOURCE VARCHAR2 in SFCLAM.SFOQVR) indicating where the data has been sourced from. Possible data values are:

“Unconverted Due to Age” = Legacy data only accessible in SFCLAM and not in CID or the originating eVTR trip reports system.

“Converted Successfully”= SF/OQ data inserted into SFCLAM and CID originating in the eVTR trip reports system.

Of note, cage tags associated with Surf Clam catch will not be contained in CID. A sample query to join tag data to CID/SFCLAM is provided below. For assistance,please contact EvertJan Vantland, GARFO Lead Software Engineer, at evertjan.vantland@noaa.gov.

Sample query of how to join tag data to CID/SFCLAM:

select sfclam_trips.trip_num , tag_ranges.begtag , tag_ranges.endtag , indiv_tags.tagnum , d.de
from document d
join sfoqvr sfclam_trips on d.docid = sfclam_trips.trip_num
LEFT JOIN VRTAGR tag_ranges on tag_ranges.vr_rec_id = sfclam_trips.vr_rec_id
LEFT JOIN VRTAGI indiv_tags on indiv_tags.vr_rec_id = sfclam_trips.vr_rec_id
order by sfclam_trips.trip_num desc