65076 Entity Published / External 5610 Hawaii DAR Commercial Purchases Data Set Completed Main view for purchase report data. Joins main purchase report tables and useful look-up tables. Replaces annual tables generated by DRS. Data View Yes hawaii_dar_frds Main view for purchase report data. Joins main purchase report tables and useful look-up tables. Replaces annual tables generated by DRS. 1 purchase_report_id bigint Yes No 19 0 Active Identifies a unique purchase report. 0 2 data_source_code varchar 4 Yes No Active Code that is abbreviation of data source. Example: Dealer Report System, code = DRS; Online Dealer Reports, code = ODR. 3 report_type_code varchar 4 Yes No Active Code assigned to report type. Example: Tuna Handline Trip Report, code = TUNA; Cash Sales Report, code = CS, Purchase Report, code = P. 4 dealer_licensee_id bigint No No 19 0 Active 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. 0 5 dealer_license_number int No No 10 0 Active Commercial marine dealer license number assigned to dealer by HDAR. 6 dealer_name varchar 192 No No Active Full name of individual holding dealer license. 7 dealer_business_name varchar 64 No No Active Name of business holding the license. 8 fisher_licensee_id bigint No No 19 0 Active Identifies fisher who sold the catch listed on the purchase report. 0 9 fisher_license_number int No No 10 0 Active Commercial marine fishing license number assigned to fisher by HDAR. 10 fisher_name varchar 192 No No Active Full name of individual holding fishing license. 11 report_date date Yes No Active Date of purchase. 12 received_date date No No Active Date dealer submitted report online, or date staff received the paper report from the dealer. 13 vessel_id varchar 255 No No Active 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'. 14 vessel_name varchar 255 No No Active 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 DRS. ODR automatically fills this in based on vessel_id. 15 purchase_type_code int Yes No 10 0 Active Code assigned to a purchase type. 1 16 purchase_type_name varchar 25 No No Active Name of purchase type. Example: Initial Sale, Resale, etc. 17 purchase_report_comments varchar 1024 No No Active Comments submitted online through ODR regarding purchase. Different from view_purchases.purchase_comments, which are comments regarding purchases at the individual species level before ODR existed. 18 validation_status enum 21 No No Active Validation status when adding report to ODR. 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 ODR. 19 trip_number varchar 32 No No Active 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. 20 trip_type_code varchar 1 No No Active Code for fishing trip type. Example: Handline, code = H; Longline, code = L, etc. 21 trip_type varchar 50 No No Active Name of fishing trip type. Example: Bottomfish, Handline, Longline, etc. 22 purchase_id bigint No No 19 0 Active Identifies a unique purchase record. 0 23 species_id int No No 10 0 Active Code representing species that was purchased. 0 24 food_species_code int No No 10 0 Active 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. 25 aquarium_species_code int No No 10 0 Active 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. 26 food_name text 65535 No No Active Comma delimited list of names associated with a given species_id sold for food. Example: species_id = 2, food_name = Skipjack. 27 aquarium_name text 65535 No No Active Comma delimited list of names associated with a given species_id that are sold for aquariums. Example: species_id = 83, aquarium_name = Achilles Tang. 28 local_name text 65535 No No Active Comma delimited list of local names associated with a given species_id. Example: species_id = 2, local_name = Aku, Otado, Otaru. 29 display_name mediumtext 16777215 No No Active Primary name displayed by applications and reports. Name come from species_common_name.name where species_common_name_type_code = 5 (Display Name). If no records where species_common_name_type_code = 5, species.scientific_name is used. 30 rank varchar 20 No No Active Name of taxonomic ranking. Example: Kingdom, Subkingdom, Phylum, Subphylum, Class, etc. 31 scientific_name varchar 125 No No Active 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. 32 species_category varchar 40 No No Active Name of the category marine species falls under. Example: Shells, Seaweed, Freshwater fish, Bait Only, etc. 33 species_condition_code varchar 5 No No Active Condition of the species when purchased. Used to estimate the number of pounds sold (whole weight). Required field for Cash Sales from OFR. Not required for ODR. List merges conditions from both OFR and ODR. 34 species_condition varchar 100 No No Active Description of marine life condition when purchased. Example: Belly Only, Damage, Whole, etc. 35 pounds_bought decimal No No 17 2 Active Weight (pounds) of each species purchased. Required unless species is exempt from reporting weight. 36 pieces_bought decimal No No 17 2 Active 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. 37 total_value decimal No No 17 2 Active Amount paid for marine life (US dollars). 38 exported tinyint No No 3 0 Active If sold outside the state of Hawaii, value = 1; if not, value = 0. Only applicable to Aquarium Cash Sale Reports. 0 39 purchase_comments varchar 1024 No No Active Comments regarding the individual purchase before ODR existed. Different from view_purchases.purchase_report_comments, which are comments regarding the purchase from ODR. gov.noaa.nmfs.inport:65076 Bradley Gough 2021-07-27T02:22:46 Bradley Gough 2023-09-23T00:43:01 2023-09-23 Pacific Islands Fisheries Science Center PIFSC 1845 Wasp Blvd. Honolulu HI 96818 USA 808-725-5300 https://www.pifsc.noaa.gov 8:00 a.m. - 4:30 p.m. 1001 Public No 1 Year