Final View in eDealer Schema

Year the product was landed and taken from the DATE_LANDED field. If this is not available, the year from DATE_PURCHASED is used.

Month information taken from the DATE_LANDED field. If this is not available, the month from DATE_PURCHASED is used.

Unique report identifier coming from the SAFIS system

Unique report identifier coming from a Trip Ticket system

Unique report identifier coming from the edealer reporting system (applied to all reports validated and pulled into the edealer production tables)

Identifies where the record is pulled. Sources include eDealer production tables (EDLR-P), eDealer records in stuck tables that have been verified as accurate (EDLR-S), records that include "Unclassified" landings and have been reapportioned to a species (REAPP; this information is used for quota monitoring and stock assessments), GulfFIN data (GFIN), ACCSP data (ACCSP), Pelagic longline vessel logbooks from the SEFSC (PLLLB), Caribbean vessel logbook data from Puerto Rico (CAR-PR) and the USVI (CAR-USVI). this is similar to the SOURCE information but more specific.

Indicates whether the data came directly from eDealer (via a "VALIDATED" or "STUCK" record) or a record was taken from an outside source and record was entered in manually into the VIEW ("MANUAL").

Description of where the report came from: Caribbean report, dealer report, vessel report, PLLLB. This is similar to the information displayed in REPORT_TYPE, but is categorical information.

Corporate name of dealer, if applicable

The SAFIS participant ID

The federal dealer permit number issued from GARFO

The federal dealer permit number issued from SERO

Identifiers denoting what data system HMS records were pulled from. These currently are: ACCSP-Cmpr (ACCSP data warehouse comparison), PWR-SEFSC Weigh out (weigh out data comparison), MFIN - P (paper form from GulfFIN), ACCSP (electronic record from ACCSP), SAFIS (electronic record from SAFIS), NCSCBI (electronic record from North Carolina state program), CCL-SEFSC Carib (Caribbean landings from SEFSC), GULFIN (electronic record from GulfFIN)

A foreign key from the SAFIS PERMITS table. Identifies the permit under which a Dealer/participant is reporting.

This is the state listed in the permit file for the dealer's business address. In some cases, it is more accurate to describe it as the State the dealer conducts business in.

This is the filename associated with a group of reports submitted by a dealer using a Trip Ticket or Florida VESL reporting program. This serve as a confirmation number that a dealer submitted a report to the federal agency. The contains a time/date stamp that corresponds to the date of submission.

Date fish product purchased; the date the dealer took possession of the product

Date fish product landed (when the boat comes to the dock). This date is used for quota monitoring.

This is the landing date taken from the vessel logbook record; this field is populated after a reconciliation event occurs in the Logbook Reconciliation tool

This is a code that indicates the port of landing in the STAG_HMS_DEALER_REPORTS; It is from the PORT code in the VALID_PORTS table and becomes the PORT code in the DEALER_REPORTS table

Name of port where fishing vessel landed and offloaded the product

The 2-number code used by the FINS (GulfFIN and ACCSP) to indicate the state of the port of landing.

State of the port where the fishing vessel landed and offloaded the product

This is the port state taken from the vessel logbook record; this field is populated after a reconciliation event occurs in the Logbook Reconciliation tool

State of the port where the fishing vessel landed and offloaded the product according to the Fisheries of the US (FUS). This is the same as the PORT_STATE except: 1) the state of Florida is split into "Florida-East" (FE) and "Florida-West" (FW); and 2) the nomenclature for Caribbean states is slightly different, per S&T request.

USCG documentation number or state registration number of the vessel dealer purchased product from as reported by the dealer

This is the vessel registration (USCG # or state registration number) taken from the vessel logbook record; this field is populated after a reconciliation event occurs in the Logbook Reconciliation tool

Internal identifier assigned by the SAFIS program and pulled into edealer

Federal fishing permit number; string concatenated with a comma

Swordfish fishing vessel permit category (Directed or Incidental)

VTR or logbook number of the fishing trip that the dealer purchased fish from

This is the VTR # taken from the vessel logbook record; this field is populated after a reconciliation event occurs in the Logbook Reconciliation tool

Dealer submits the reason a logbook was not submitted by a vessel; this is a number that corresponds to a standardized list of reasons of why a logbook # was not supplied by a dealer; this field is from DEALER_REPORTS table

Description of why a dealer did not submit a VTR # on a dealer report; corresponds to the number on the standardized list of reasons why no VTR # was supplied by the dealer; this field is from DEALER_REPORTS table

Name of the field in the STAG_HMS_DEALER_REPORTS table that hold the explanation of why no VTR # was provided by the dealer.

The dealer indicates the logbook identifier from the observer logbook; the presence of this value is used to identify shark research trips

ACCSP standard code associated with gear submitted on the report by the dealer

This is the gear type taken from the vessel logbook record; this field is populated after a reconcilation event occurs in the Logbook Reconciliation tool

This information should match the GEAR_NAME column as they exist in the Gear tables at the FINS and as they exist in FIN Standard codes.

Explanation provided by the dealer describing why a report was late

Entered by the dealer to explain changes on a report and/or if a transaction is not a duplicate entry (if there are mutliple entries that are the same within a report)

A "Y" indicates that the record was processed by edealer and put in the production DEALER_REPORTS and LANDINGS tables; a "N" indicates that the record has not been processed; this is taken from the STAG_HMS_DEALER_REPORTS table

A "Y" indicates that the reocrd was discarded; and "N" indicates that the record has not been discarded; this is taken from the STAG_HMS_DEALER_REPORTS table

Indicates if an error existed in the dealer report. Y (Yes) or N (No) values

The message describes the error associated with the dealer report.

Indicates how a dealer reported was entered and submitted to the federal agencies

"Y" indicates the dealer deleted the record; a NULL value indicates that the dealer has not deleted the record

Internal value assigned by the edealer program during a nightly data sycnchronization event with ACCSP; each unique value identifies a separate data synchronization event

"A" value indicates that the record is an "add" or initial submission; "U" value indicates that the record has been updated and the update is being shown

Date report entered into original reporting program

This indicates the date when a dealer submitted a report to federal and state agencies. This date should exist for reports submitted through a reporting program. This information does not exist with manually entered records (e.g., Caribbean data, vessel logbook data).

This indicates the dealer submitted a report. It is in Coordinated Universal Time report submission date/time. Used for reports from Trip Ticket programs

This indicates the dealer submitted an updated report. Coordinated Universal Time report update submission date/time. Used for reports from Trip Ticket programs

Indicates if a report submitted through a Trip Ticket program has a submission date/time stamp from the internet (‘UTC’) or from the local computer (‘LOCAL’).

Indicates the updated submission date/time stamp of an updated file. Submission time/date stamp from the internet (‘UTC’) or from the local computer (‘LOCAL’).

This indicates the date when a dealer updated a report and sent it to the federal and state agencies. This date should exist for reports submitted through a reporting program. This information does not exist with manually entered records (e.g., Caribbean data, vessel logbook data).

Text identifier of user who updated the record.

Date dealer report record was last updated.

Free text identifying who updated a record.

Date report loaded into eDealer STAG_HMS_DEALER_REPORTS table.

This is the date the HMS records were submitted to be loaded into this final view.

This is the date a HMS record was resubmitted/reloaded in this final view.

A Primary Key for a STAG_HMS_DEALER_REPORTS record. An auto-generated number.

This is a primary key that ACCSP assigns to the data. It links the stag_hms_dealer_reports and stag_jms_landings tables.

Primary Key for DEALER_REPORT records. An auto-generated number.

Foreign key to link between STAG_HMS_LANDINGS and LANDINGS table; do not need to enter this unless we want a certain version of a record to be in View

Unique identifier for individual landings in the STAG_HMS_LANDINGS table; foreign key to link additional fields manually loaded with records in the STAG_HMS_LANDINGS table; this value stays the same through time

Unique identifier for individual landings in the LANIDNGS table; this value will change with each submission of a report

Yes/No flag field indicating whether a landing record was processed. Taken from the STAG_HMS_LANDINGS table

Yes/No flag field indicating whether a landing record was discarded. Taken from the STAG_HMS_LANDINGS table

Yes/No flag field indicating whether a landing record had data validation errors. Taken from the STAG_HMS_LANDINGS table

Text field describing the issues causing a landing record to fail validation. Taken from the STAG_HMS_LANDINGS table

A Yes/No flag denoting if a landing record was deleted. Taken from the STAG_HMS_LANDINGS table

Yes/No flag indicating if landing record is active

Free text providing a general classification of an HMS species (BAYS TUNA, SHARK, SWORDFISH and UNCLASSIFIED SHARK)

A Primary Key for a SPECIES record. An auto-generated number.

ITIS code used by itis.gov to indicate species. NOTE: '159786' ("SHARKS, SMALL COASTAL") may be used to report "Shark Belly Flaps"; 159851’ ("SHARKS, PELAGIC, UNC") may represent "SHARK FINS"

Free text describing a common name for an organism.

This is the Common_Name used in the Caribbean vessel logbook data (CAR-PR and CAR-USVI)

"Y" indicates that the record was either a report of unclassified HMS-product which was apportioned to species-specific weights, or is a report to species originating from a report of unclassified HMS-product, which was determined by proportions of historical landings

This is a Key that would tie together original records that included "Unclassified" reported species with records added manually that reapportioned these unclassifieds to species-specific landings. The Key would be the same for all records in that group (e.g., 100 lb unclassified shark may break down to 4 different species and all records would have the same REAPP_KEY). Reapportioned records with species-specific information may or may not include landing and purchase dates, month, gear, disposition, market, vessel, port, or dealer information. Originally submitted records and reapportioned records will be differentiated via the "SAME_AS_WAREHOUSE" column, where "N" indicates a reapportioned record.

Percentages applied to unclassified HMS products to determine weights of species.

This field has a code noting the units the REPORTED_QUANTITY is reported in.

Weight of purchased fish.

A SAFIS code describing how a fish was used after purchase.

A free text description of the disposition code.

A "Y" in this field would indicate transactions from more than one dealer exist or are expected. The default for this field should be "N".

This is a Key that ties together all records from a single vessel trip. The Trip Key is the same for all records in that group. This is an internal, automated value created by the Trip Identification Tool

A "Y" in this field would indicate records that should be included for QM updates. A "N" in this field would indicate the record should be excluded for QM updates.

A "Y" in this field would indicate records that should be included for tallying FUS landings. An "N" in this field would indicate records that should be EXcluded for FUS total landings.

A "Y" in this field would indicate records that should be included in the FUS report. It is possible that a record may be included for FUS landings but not for FUS economics (FUS_ECONOMICS_1_0). Including all records with a "Y" in this field would provide the entire set of records needed to recreate that report, regardless of being excluded from FUS landings or FUS economics. An "N" in this field would indicate records which should be excluded for the FUS report.

ACCSP standard code used to describe the grade of the fish, or how the fish was cut up upon weighing

Free text description of the ACCSP gear code value

This is the reported weight converted to dressed/gutted weight

This is the reported weight converted to whole weight

This is the conversion factor used to calculate whole weight

This is the conversion factor used to calculate dressed/gutted weight

A code describing the quality of product.

Price the dealer paid a fisher per pound of fish product.

A "Y" in this field would indicate records that should be included for determining economic data for the FUS report. A "N" in this field indicates that this record should be Excluded from economic analyses for FUS.

Total Purchase Price of fish product

Total sales price for fish if no initial purchase occurred.

HMS Catch Area Code; Indicates where fish were caught in the ocean

Federal Fishing Area Code.

Federal Fishing Sub-Area Code.

This is a sub-area code that is most commonly used in the Northeast to identify shellfish beds

This is the region where the fish was landed as defined by the Councils and as indicated in FIN data. Council areas and HMS regions are different (GOM vs SAT around the tip of Florida and for defining the Atlantic regions). Including this as defined in the FINS would be helpful to easily identify errors in comparison. Council region areas are defined by AREA_FISHED and SUB_AREA_FISHED.

Quota group as defined for HMS quota management

Region as defined for HMS quota management

Sub-region as defined for HMS quota management

This is the base quota assigned to an HMS quota group in dressed weight (DW) metric tons (MT)

This is the base quota assigned to an HMS quota group in whole weight (WW) metric tons (MT)

This is the adjusted quota assigned to an HMS quota group in dressed weight (DW) metric tons (MT)

This is the adjusted quota assigned to an HMS quota group in whole weight (WW) metric tons (MT)

The date a particular quota management group was opened for a given season/year

The date a particular quota management group was closed for a given season/year

Data included from manually entered GulfFIN data. Needed to identify dealer.

Data included from manually entered GulfFIN data. Helpful to identify landing record in GulfFIN.

Data included from manually entered FIN data. Helpful to identify method landing report was submitted to FIN.

A "Y" in this field indicates the record has been purposefully changed and will not match records already housed in the FIN data warehouses. A null field indicates that the record has not been verified as being the same or different. Changes may exist between eDealer data and master FIN data if a state has made alterations to the record.

If a "Y" exists in DIFFER_FROM_WAREHOUSE_Y, then the type of difference is required: "NEW" means record does not exist in FIN warehouse; "Modified" means the record exists but was changed in the HMS Master View. If "DIFFER_FROM_WAREHOUSE" is null, then this field should be null.

Free form field to hold notes from administrators

Free text describing any record review comments by HMS staff

Free text identifying the HMS staffer making review comments

Date review comments entered into dealer report record.

To keep or exclude a record or landing based on value in the Trip Identification Tool

To keep or exclude a record's price information based on value in the Trip Identification Tool

A calculated value of the price / lb at the first purchase multiplied by the total lbs landed

Free form field to hold notes from economist

Phone: 240-338-3936

Atlantic Highly Migratory Species Management Division

Division Chief: Randy Blankinship