Slide Menu
Search Help Show/Hide Menu

Item Identification

Title: view_purchases
Status: Completed
Abstract:

Main view for purchase report data. Joins main purchase report tables and useful look-up tables. Replaces annual tables generated by DRS.

Entity Information

Entity Type: Data View
Active Version?: Yes
Schema Name: hawaii_dar_frds
Description:

Main view for purchase report data. Joins main purchase report tables and useful look-up tables. Replaces annual tables generated by DRS.

Data Attributes

Attribute Summary

Rubric Score Primary Key? Name Type Description
100
purchase_report_id bigint Identifies a unique purchase report.
100
data_source_code varchar Code that is abbreviation of data source. Example: Aquarium Catch, code = AC; Online Dealer Reports, code = ODR.
100
report_type_code varchar Code assigned to report type. Example: MHI Deep 7 Bottomfish Fishing Trip Report, code = BF; Tuna Handline Trip Report, code = TUNA. The following rules apply to Online Fishing Reports: • By Default, code = FR (Fishing Report). • When one or more of Deep 7 Bottomfish are caught, code = BF. Deep 7 have view_catch.species_id = (15, 17, 19, 21, 22, 58, 97) and have view_catch.species_category = Bottomfish – Deep 7. • When the fishing method is “Tuna fishing method” and area is “Tuna area,” code = TUNA. Tuna fishing methods have view_catch.method_code = (8, 9, 10, 61, 62, 63, 92, 93, 97). Tuna areas are found where view_catch.area_code = (15818, 181, 16223F, 15717, 16019, 15217). • When one or more fishing methods is “NTD fishing method,” code = NTD. NTD fishing methods have view_catch.method_code = (13, 14, 21, 22, 23, 24, 25, 26, 27, 32, 33, 40, 51, 52, 53, 54). • When multiple criteria are met, only a single code is applied using the following priority: BF, TUNA, NTD, FR.
100
dealer_licensee_id bigint Identifies dealer who purchased catch for dealer reports, or the fisher who sold catch for Cash Sales Reports. For Cash Sales Reports, the fisher is also the dealer, therefore dealer_licensee_id = view_purchases.fisher_licensee_id.
100
dealer_license_number int Commercial marine dealer license number assigned to dealer by HDAR.
100
dealer_name varchar Full name of individual holding dealer license.
100
dealer_business_name varchar Name of business holding the license.
100
fisher_licensee_id bigint Identifies fisher who sold the catch listed on the purchase report.
100
fisher_license_number int Commercial marine fishing license number assigned to fisher by HDAR.
100
fisher_name varchar Full name of individual holding fishing license.
100
report_date date Date of purchase.
100
received_date date Date dealer submitted report online, or date staff received the paper report from the dealer.
100
vessel_id varchar Hawaii State (HA) or U.S. Coast Guard (UCSG) vessel registration/documentation number. Field will not be reported if a vessel was not used when fishing. Note: HA vessel registration number suffix indicates vessel use. Recreational use is denoted by letters 'A' through 'F'. Commercial fishing is denoted by 'CF'. Commercial passenger is denoted by 'CP'.
100
vessel_name varchar Name of fishing vessel that dealer purchased from. Vessel name is registered with State Division of Boating & Ocean Recreation. Field will not be reported if a vessel was not used when fishing. Was manually entered in Dealer Reporting System. Online Dealer Reports automatically fills this in based on view_purchases.vessel_id.
100
purchase_report_comments varchar Comments submitted online through Online Dealer Reports regarding purchase. Different from view_purchases.purchase_comments, which are comments regarding purchases at the individual species level before Online Dealer Reports existed.
100
validation_status enum Validation status when adding report to Online Dealer Reports. Values include: Approved, Pending, Approved - No Response. Originally, meant to be used for tracking communication with dealers who appeared on Error Reports. A value of Approved would clear the dealer from the error report. However, due to a design flaw, the validation status is not currently being used in Online Dealer Reports.
100
trip_number varchar Number generated by UFA and FIF dealers who buy from fishers on purchase reports. Different from view_catch.logbook_number, which is number printed on NWHI Bottomfish Trip Daily Log sheet.
100
trip_type_code varchar Code for fishing trip type. Example: Handline, code = H; Longline, code = L, etc.
100
trip_type varchar Name of fishing trip type. Example: Bottomfish, Handline, Longline, etc.
100
purchase_id bigint Identifies a unique purchase record.
100
species_id int Code representing species that was purchased.
100
food_species_code int Codes associated with species collected for food from all HDAR forms except Aquarium forms. Different from view_purchases.aquarium_species_code, which are codes associated with species collected for aquarium. Note: food_species_code and view_purchases.aquarium_species_code have overlapping species but species codes are unique to each column, meaning the same fish can have a different code in each column. Example: Achilles Tang, food_species_code = 85 and view_purchases.aquarium_species_code = 103.
100
aquarium_species_code int Codes associated with species collected for aquariums. Codes are from HDAR Aquarium Fish Trip Report. Different from view_purchases.food_species_code, which are codes associated with species collected for food. Note: view_purchases.food_species_code and aquarium_species_code have overlapping species but species codes are unique to each column, meaning the same fish can have a different code in each column. Example: Achilles Tang, view_purchases.food_species_code = 85 and aquarium_species_code = 103.
100
food_common_name text Comma delimited list of names associated with a given species_id sold for food. Example: species_id = 2, food_common_name = Skipjack.
100
aquarium_common_name text Comma delimited list of names associated with a given species_id that are sold for aquariums. Example: species_id = 83, aquarium_common_name = Achilles Tang.
100
local_name text Comma delimited list of local names associated with a given species_id. Example: species_id = 2, local_name = Aku, Otado, Otaru.
100
rank varchar Name of taxonomic ranking. Example: Kingdom, Subkingdom, Phylum, Subphylum, Class, etc.
100
scientific_name varchar Normally the binomial name, which consists of a generic name (the genus name) and the specific name (the species name). If species cannot be identified to the species level, it will be identified to the highest known taxonomic ranking, such as its phylum, class, infraclass, order, suborder, family, or subfamily. A genus name followed by 'spp.' refers to multiple species within that genus. A genus name followed by 'sp.' refers to one species. The scientific name for species identified at the rank of family or higher will be the name followed by the rank in parentheses. Example: Thunnus thynnus, Nerita spp., Scombridae (family), Anthozoa (class), etc. Different from common name.
100
species_category varchar Name of the category marine species falls under. Example: Shells, Seaweed, Freshwater fish, Bait Only, etc.
100
species_condition_code varchar Condition of the species when purchased. Used to estimate the number of pounds sold (whole weight). Required field for Cash Sales from Online Fishing Reports. Not required for Online Dealer Reports. List merges conditions from both Online Fishing Reports and Online Dealer Reports.
100
species_condition varchar Description of marine life condition when purchased. Example: Belly Only, Damage, Whole, etc.
100
pounds_bought decimal Weight (pounds) of each species purchased. Required unless species is exempt from reporting weight.
100
pieces_bought decimal Number of pieces purchased. If purchased in quantities such as akule, number of fish may have been estimated. Required unless species is exempt from reporting pieces.
100
total_value decimal Amount paid for marine life (US dollars).
100
exported tinyint If sold outside the state of Hawaii, value = 1; if not, value = 0. Only applicable to Aquarium Cash Sale Reports.
100
purchase_comments varchar Comments regarding the individual purchase before Online Dealer Reports existed. Different from view_purchases.purchase_report_comments, which are comments regarding the purchase from Online Dealer Reports.

Attribute Details

purchase_report_id

CC ID: 1076209
Seq. Order: 1
Data Storage Type: bigint
Required: Yes
Primary Key: No
Precision: 19
Scale: 0
Status: Active
Description:

Identifies a unique purchase report.

Default Value: 0

data_source_code

CC ID: 1076210
Seq. Order: 2
Data Storage Type: varchar
Max Length: 4
Required: Yes
Primary Key: No
Status: Active
Description:

Code that is abbreviation of data source. Example: Aquarium Catch, code = AC; Online Dealer Reports, code = ODR.

report_type_code

CC ID: 1076211
Seq. Order: 3
Data Storage Type: varchar
Max Length: 4
Required: Yes
Primary Key: No
Status: Active
Description:

Code assigned to report type. Example: MHI Deep 7 Bottomfish Fishing Trip Report, code = BF; Tuna Handline Trip Report, code = TUNA. The following rules apply to Online Fishing Reports:

• By Default, code = FR (Fishing Report).

• When one or more of Deep 7 Bottomfish are caught, code = BF. Deep 7 have view_catch.species_id = (15, 17, 19, 21, 22, 58, 97) and have view_catch.species_category = Bottomfish – Deep 7.

• When the fishing method is “Tuna fishing method” and area is “Tuna area,” code = TUNA. Tuna fishing methods have view_catch.method_code = (8, 9, 10, 61, 62, 63, 92, 93, 97). Tuna areas are found where view_catch.area_code = (15818, 181, 16223F, 15717, 16019, 15217).

• When one or more fishing methods is “NTD fishing method,” code = NTD. NTD fishing methods have view_catch.method_code = (13, 14, 21, 22, 23, 24, 25, 26, 27, 32, 33, 40, 51, 52, 53, 54).

• When multiple criteria are met, only a single code is applied using the following priority: BF, TUNA, NTD, FR.

dealer_licensee_id

CC ID: 1076212
Seq. Order: 4
Data Storage Type: bigint
Required: No
Primary Key: No
Precision: 19
Scale: 0
Status: Active
Description:

Identifies dealer who purchased catch for dealer reports, or the fisher who sold catch for Cash Sales Reports. For Cash Sales Reports, the fisher is also the dealer, therefore dealer_licensee_id = view_purchases.fisher_licensee_id.

Default Value: 0

dealer_license_number

CC ID: 1076213
Seq. Order: 5
Data Storage Type: int
Required: No
Primary Key: No
Precision: 10
Scale: 0
Status: Active
Description:

Commercial marine dealer license number assigned to dealer by HDAR.

dealer_name

CC ID: 1076214
Seq. Order: 6
Data Storage Type: varchar
Max Length: 192
Required: No
Primary Key: No
Status: Active
Description:

Full name of individual holding dealer license.

dealer_business_name

CC ID: 1076215
Seq. Order: 7
Data Storage Type: varchar
Max Length: 64
Required: No
Primary Key: No
Status: Active
Description:

Name of business holding the license.

fisher_licensee_id

CC ID: 1076216
Seq. Order: 8
Data Storage Type: bigint
Required: No
Primary Key: No
Precision: 19
Scale: 0
Status: Active
Description:

Identifies fisher who sold the catch listed on the purchase report.

Default Value: 0

fisher_license_number

CC ID: 1076217
Seq. Order: 9
Data Storage Type: int
Required: No
Primary Key: No
Precision: 10
Scale: 0
Status: Active
Description:

Commercial marine fishing license number assigned to fisher by HDAR.

fisher_name

CC ID: 1076218
Seq. Order: 10
Data Storage Type: varchar
Max Length: 192
Required: No
Primary Key: No
Status: Active
Description:

Full name of individual holding fishing license.

report_date

CC ID: 1076219
Seq. Order: 11
Data Storage Type: date
Required: Yes
Primary Key: No
Status: Active
Description:

Date of purchase.

received_date

CC ID: 1076220
Seq. Order: 12
Data Storage Type: date
Required: No
Primary Key: No
Status: Active
Description:

Date dealer submitted report online, or date staff received the paper report from the dealer.

vessel_id

CC ID: 1076221
Seq. Order: 13
Data Storage Type: varchar
Max Length: 255
Required: No
Primary Key: No
Status: Active
Description:

Hawaii State (HA) or U.S. Coast Guard (UCSG) vessel registration/documentation number. Field will not be reported if a vessel was not used when fishing. Note: HA vessel registration number suffix indicates vessel use. Recreational use is denoted by letters 'A' through 'F'. Commercial fishing is denoted by 'CF'. Commercial passenger is denoted by 'CP'.

vessel_name

CC ID: 1076222
Seq. Order: 14
Data Storage Type: varchar
Max Length: 255
Required: No
Primary Key: No
Status: Active
Description:

Name of fishing vessel that dealer purchased from. Vessel name is registered with State Division of Boating & Ocean Recreation. Field will not be reported if a vessel was not used when fishing. Was manually entered in Dealer Reporting System. Online Dealer Reports automatically fills this in based on view_purchases.vessel_id.

purchase_report_comments

CC ID: 1076223
Seq. Order: 15
Data Storage Type: varchar
Max Length: 1024
Required: No
Primary Key: No
Status: Active
Description:

Comments submitted online through Online Dealer Reports regarding purchase. Different from view_purchases.purchase_comments, which are comments regarding purchases at the individual species level before Online Dealer Reports existed.

validation_status

CC ID: 1076224
Seq. Order: 16
Data Storage Type: enum
Max Length: 21
Required: No
Primary Key: No
Status: Active
Description:

Validation status when adding report to Online Dealer Reports. Values include: Approved, Pending, Approved - No Response. Originally, meant to be used for tracking communication with dealers who appeared on Error Reports. A value of Approved would clear the dealer from the error report. However, due to a design flaw, the validation status is not currently being used in Online Dealer Reports.

trip_number

CC ID: 1076225
Seq. Order: 17
Data Storage Type: varchar
Max Length: 32
Required: No
Primary Key: No
Status: Active
Description:

Number generated by UFA and FIF dealers who buy from fishers on purchase reports. Different from view_catch.logbook_number, which is number printed on NWHI Bottomfish Trip Daily Log sheet.

trip_type_code

CC ID: 1076226
Seq. Order: 18
Data Storage Type: varchar
Max Length: 1
Required: No
Primary Key: No
Status: Active
Description:

Code for fishing trip type. Example: Handline, code = H; Longline, code = L, etc.

trip_type

CC ID: 1076227
Seq. Order: 19
Data Storage Type: varchar
Max Length: 50
Required: No
Primary Key: No
Status: Active
Description:

Name of fishing trip type. Example: Bottomfish, Handline, Longline, etc.

purchase_id

CC ID: 1076228
Seq. Order: 20
Data Storage Type: bigint
Required: No
Primary Key: No
Precision: 19
Scale: 0
Status: Active
Description:

Identifies a unique purchase record.

Default Value: 0

species_id

CC ID: 1076229
Seq. Order: 21
Data Storage Type: int
Required: No
Primary Key: No
Precision: 10
Scale: 0
Status: Active
Description:

Code representing species that was purchased.

Default Value: 0

food_species_code

CC ID: 1076230
Seq. Order: 22
Data Storage Type: int
Required: No
Primary Key: No
Precision: 10
Scale: 0
Status: Active
Description:

Codes associated with species collected for food from all HDAR forms except Aquarium forms. Different from view_purchases.aquarium_species_code, which are codes associated with species collected for aquarium. Note: food_species_code and view_purchases.aquarium_species_code have overlapping species but species codes are unique to each column, meaning the same fish can have a different code in each column. Example: Achilles Tang, food_species_code = 85 and view_purchases.aquarium_species_code = 103.

aquarium_species_code

CC ID: 1076231
Seq. Order: 23
Data Storage Type: int
Required: No
Primary Key: No
Precision: 10
Scale: 0
Status: Active
Description:

Codes associated with species collected for aquariums. Codes are from HDAR Aquarium Fish Trip Report. Different from view_purchases.food_species_code, which are codes associated with species collected for food. Note: view_purchases.food_species_code and aquarium_species_code have overlapping species but species codes are unique to each column, meaning the same fish can have a different code in each column. Example: Achilles Tang, view_purchases.food_species_code = 85 and aquarium_species_code = 103.

food_common_name

CC ID: 1076232
Seq. Order: 24
Data Storage Type: text
Max Length: 65535
Required: No
Primary Key: No
Status: Active
Description:

Comma delimited list of names associated with a given species_id sold for food. Example: species_id = 2, food_common_name = Skipjack.

aquarium_common_name

CC ID: 1076233
Seq. Order: 25
Data Storage Type: text
Max Length: 65535
Required: No
Primary Key: No
Status: Active
Description:

Comma delimited list of names associated with a given species_id that are sold for aquariums. Example: species_id = 83, aquarium_common_name = Achilles Tang.

local_name

CC ID: 1076234
Seq. Order: 26
Data Storage Type: text
Max Length: 65535
Required: No
Primary Key: No
Status: Active
Description:

Comma delimited list of local names associated with a given species_id. Example: species_id = 2, local_name = Aku, Otado, Otaru.

rank

CC ID: 1076235
Seq. Order: 27
Data Storage Type: varchar
Max Length: 20
Required: No
Primary Key: No
Status: Active
Description:

Name of taxonomic ranking. Example: Kingdom, Subkingdom, Phylum, Subphylum, Class, etc.

scientific_name

CC ID: 1076236
Seq. Order: 28
Data Storage Type: varchar
Max Length: 125
Required: No
Primary Key: No
Status: Active
Description:

Normally the binomial name, which consists of a generic name (the genus name) and the specific name (the species name). If species cannot be identified to the species level, it will be identified to the highest known taxonomic ranking, such as its phylum, class, infraclass, order, suborder, family, or subfamily. A genus name followed by 'spp.' refers to multiple species within that genus. A genus name followed by 'sp.' refers to one species. The scientific name for species identified at the rank of family or higher will be the name followed by the rank in parentheses. Example: Thunnus thynnus, Nerita spp., Scombridae (family), Anthozoa (class), etc. Different from common name.

species_category

CC ID: 1076237
Seq. Order: 29
Data Storage Type: varchar
Max Length: 40
Required: No
Primary Key: No
Status: Active
Description:

Name of the category marine species falls under. Example: Shells, Seaweed, Freshwater fish, Bait Only, etc.

species_condition_code

CC ID: 1076238
Seq. Order: 30
Data Storage Type: varchar
Max Length: 5
Required: No
Primary Key: No
Status: Active
Description:

Condition of the species when purchased. Used to estimate the number of pounds sold (whole weight). Required field for Cash Sales from Online Fishing Reports. Not required for Online Dealer Reports. List merges conditions from both Online Fishing Reports and Online Dealer Reports.

species_condition

CC ID: 1076239
Seq. Order: 31
Data Storage Type: varchar
Max Length: 100
Required: No
Primary Key: No
Status: Active
Description:

Description of marine life condition when purchased. Example: Belly Only, Damage, Whole, etc.

pounds_bought

CC ID: 1076240
Seq. Order: 32
Data Storage Type: decimal
Required: No
Primary Key: No
Precision: 17
Scale: 2
Status: Active
Description:

Weight (pounds) of each species purchased. Required unless species is exempt from reporting weight.

pieces_bought

CC ID: 1076241
Seq. Order: 33
Data Storage Type: decimal
Required: No
Primary Key: No
Precision: 17
Scale: 2
Status: Active
Description:

Number of pieces purchased. If purchased in quantities such as akule, number of fish may have been estimated. Required unless species is exempt from reporting pieces.

total_value

CC ID: 1076242
Seq. Order: 34
Data Storage Type: decimal
Required: No
Primary Key: No
Precision: 17
Scale: 2
Status: Active
Description:

Amount paid for marine life (US dollars).

exported

CC ID: 1076243
Seq. Order: 35
Data Storage Type: tinyint
Required: No
Primary Key: No
Precision: 3
Scale: 0
Status: Active
Description:

If sold outside the state of Hawaii, value = 1; if not, value = 0. Only applicable to Aquarium Cash Sale Reports.

Default Value: 0

purchase_comments

CC ID: 1076244
Seq. Order: 36
Data Storage Type: varchar
Max Length: 1024
Required: No
Primary Key: No
Status: Active
Description:

Comments regarding the individual purchase before Online Dealer Reports existed. Different from view_purchases.purchase_report_comments, which are comments regarding the purchase from Online Dealer Reports.

Catalog Details

Catalog Item ID: 65076
GUID: gov.noaa.nmfs.inport:65076
Metadata Record Created By: Bradley M Gough
Metadata Record Created: 2021-07-27 02:22+0000
Metadata Record Last Modified By: SysAdmin InPortAdmin
Metadata Record Last Modified: 2022-08-09 17:11+0000
Metadata Record Published: 2021-08-02
Owner Org: PIFSC
Metadata Publication Status: Published Externally
Do Not Publish?: N
Metadata Last Review Date: 2021-08-02
Metadata Review Frequency: 1 Year
Metadata Next Review Date: 2022-08-02