view_purchases
Entity (ENT) | Pacific Islands Fisheries Science Center (PIFSC)GUID: gov.noaa.nmfs.inport:65076 | Updated: August 9, 2022 | Published / External
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
![]() |
![]() |
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
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
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
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
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
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
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
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
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
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
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
Seq. Order: | 11 |
---|---|
Data Storage Type: | date |
Required: | Yes |
Primary Key: | No |
Status: | Active |
Description: |
Date of purchase. |
received_date
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 |