27 AUG 2008 14:03 PacFIN Report: Table Column Descriptions Page: 1 coldsc_rpt_tbl_cols This report was generated using the column description (coldsc) table. This report includes descriptions for all columns for available tables. TABLE_NAME COLUMN_NAME DESCRIPTION -------------------- -------------------- ------------------------------------------------------------ LE_grnd_cnts .... TABLE; contains non-confidential summary fish-ticket data; contains one record for each LE vessel, MGRP, GRID, PCID, permit-list combination council specifies council (example: PFMC) cwvid coastwide vessel identifier: USCG Doc#, USCG small vessel identifier; examples: 1234567, CF1234X earliest earliest fish-ticket date for each CWVID-MGRP-GRID-PCID-PRMTLST combination latest latest fish-ticket date for each CWVID-MGRP-GRID-PCID-PRMTLST combination ntrips number of trips for each CWVID-MGRP-GRID-PCID-PRMTLST combination prmtlst permit list from NWR LE list of permits year year from fish-ticket LE_grnd_tkts .... TABLE; contains selected non-confidential fish-ticket data for each fish-ticket for each limited-entry (LE) vessel landing groundfish caught in the PFMC EEZ agid agency identifier from fish-ticket council specifies council (example: PFMC) cwvid coastwide vessel identifier: USCG Doc#, USCG small vessel identifier; examples: 1234567, CF1234XY ftid fish-ticket identifer from fish-ticket prmtlst permit list from NWR LE list of permits tdate date from fish-ticket year year from fish-ticket aar .... TABLE; contains agency-PacFIN areas relation agid agency id area state agency area_id arid PacFIN area code description description of area modified boolean(T/F); ='T' if this record had been modified ulid report number of initial data feed or of most recent modification acm .... TABLE; contains catch-by-area proportions agid agency id arid PacFIN area-id coeffvar coefficient of variance comptype type of composition value contained in PROPORTION; see COMP-TYPE in table cl day ignored if period = "M" or "Q"; if period="W" specifies that PROPORTION applies to the 7-day period ending on this YEAR-MONTH-DAY grade composition grade; same as in table ftl; may be null grid PacFIN gear or gear-group code(e.g.: 'TWL', 'TRL') modified boolean(T/F); ='T' if this record has been modified month month or quarter for which proportion applies; if period='Q' then specifies last month of quarter (3 => Q1, 6 => Q2, 9 => Q3, 12 => Q4) pcid PacFIN port-id; may be null period w=week; m=month; q=quarter proportion estimated proportion for the area within specified time-period, comptype, pcid, grid, spid, grade, and triptype samples number of samples spid PacFIN species-id triptype type of trip; see trip-type in table cl ulid report number of initial data feed or of most recent modification year year for which proportion applies ag .... TABLE; contains PacFIN agency_ids and attributes agid the agency identifier completion_90 the date that data of the agency is 90% completed description description of the agency lbstons boolean(T/F); ='T' if input is lbs; ='F' if input is .01 metric tons name the agency name pmfcinpfc boolean(T/F); ='T' if input is by psmfc areas; ='F' if input is by inpfc areas usdomestic boolean(T/F); ='T' if the agency is a u.s. domestic agency and not a data source for joint venture or foreign data and not a foreign agency supplying their domestic data agr .... TABLE; contains agency-PacFIN gears relation agid agency id description description of grid gear state agency gear_id grid PacFIN gear id modified boolean(T/F); ='T' if the record had been modified ulid report number of original data feed or of most recent modification apc .... TABLE; contains agency-PacFIN processor relation agid agency id description description of processor modified boolean(T/F); ='T' if this record has been modified processorid who processed the fish ulid report number of initial data feed or of most recent modification apr .... TABLE; contains agency-PacFIN ports relation agid agency id cdistrict Congressional district that the port is within. cid County id description description of port modified boolean(T/F); ='T' if this record has been modified pcid PacFIN port (& country & jv) code port state agency port-id ulid report number of initial data feed or of most recent modification ar .... TABLE; contains tree of PacFIN area-ids and attributes argroup the Area's area-group arid Area id: a groundfish, shrimp, or salmon area id arorder north-south order of areas council the council in which the area resides (N=npfmc; P=pfmc; *=neither council) description description of the area entered date row was entered into the database or date of last modification; format: yymmdd inpfc_arid the INPFC arid if type=1(psmfc), or type=2(inpfc), or type=7(inpfc subarea), or type=8(psmfc subarea), otherwise is null inputallowed boolean(T/F); ='T' if input transactions can use this area name the Area's short-name (for reporting) summarized boolean(T/F); ='T' if summarized data exists for this code type area type: 1=psmfc; 2=inpfc; 3=inpfc-grp; 4=shrimp; 5=salmon; 6=salmon-grp; 7=inpfc subarea; 8=psmfc subarea; 99=all asp .... TABLE; contains agency-PacFIN species-id relation agid agency id category state agency species-id (i.e. market category) description description of species modified boolean(T/F); ='T' if this record has been modified spid PacFIN species-id ulid report number of initial data feed or of most recent modification bds_ag .... TABLE; biological data sample_agency to sample_agid mapping agid PacFIN agency code sample_agency Agency sample agency code sample_agid PacFIN sample agency code bds_age .... TABLE; biological data age (many to 1 with fish) age Age of fish in years age_method Age method (WDFW only) age_no Sequential number for each age age_readability Age readability (WDFW only) age_struct PacFIN age structure code age_struct_agcode Agency age structure code aged_by Age reader (WDFW only cluster_no Sequential number for each cluster in sample date_aged date aged (WDFW only) fish_no Sequential number for each specimen (fish) in sample sample_no Unique number for each sample sample_year Year (YYYY) in which sample was obtained source_agid PacFIN code of source agency species_code Agency species code spid PacFIN species code bds_ar .... TABLE; biological data agency to PacFIN area code mapping agid PacFIN agency code area Agency area code arid PacFIN area code description Description of area modified Flag indicating whether modified since created bds_cluster .... TABLE; biological data clusters (many to 1 with samples) adj_clwt Weight (pounds) of the cluster that the specimen came from minus weight of fish that can't be aged (ODFW only) cluster_no Sequential number for each cluster in sample cluster_wgt Weight (pounds) of the cluster that the specimen came from frame_clwt Weight (pounds) of the cluster that the specimen frame came from in postprocessing sample (ODFW only) sample_no Unique number for each sample sample_year Year (YYYY) in which sample was obtained source_agid PacFIN code of source agency species_code Agency species code species_wgt Weight (pounds) of the target species spid PacFIN species code bds_df_acl .... TABLE; biological datafeed agency-code-list agency_code A valid agency code code_type The type of agency code being submitted. Values: S = agency species code, A = agency area code, G = agency gear code, P = agency port code. description If code_type = S then scientific name and/or common name and/or general description; if code_type = A then description of area; if code_type = G then description of gear; if code_type = P then name of port. pacfin_code A valid PacFIN species, area, gear or port code bds_df_report .... TABLE; biological datafeed report record (most recent) comm_desc Comment or description regarding this periodic report. rpt_date This is the date of report; (i.e. the date the datafeed was generated); two datafeeds from the same source may not have the same date. source Agency acronym (name in ag table) Values: WDFW = Washington Dept of Fish and Wildlife, ODFW = Oregon Dept of Fish and Wildlife, CDFG = California Dept of Fish and Game, NWFSC = Northwest Fisheries Science Center, AFSC = Alaska Fisheries Science Center. bds_df_warning .... TABLE; biological datafeed warnings (most recent) agid PacFIN agency code message Message describing what is wrong with the data more_info More information about the data that caused the warning sample_year Year (YYYY) in which samples were obtained trans_id Transaction id bds_fish .... TABLE; biological data fish (many to 1 with clusters) brood_yr Year of brooding (widow rockfish only)(ODFW only) cluster_no Sequential number for each cluster in sample fish_age_years_final Age of specimen (best age or final age) fish_length Length of specimen in millimeters fish_length_type Length type: A = alternate length F = fork length S = standard length T = total length fish_no Sequential number for each specimen (fish) in sample if bds_fish.freq = 1; if bds_fish.freq > 1 then represents a group of fish fish_weight Weight (pounds) of fish fork_length Fork lengthi(mm) of fish, either measured or estimated fork_length_estimate Flag indicating source of fork_length: T = True, fork_length was estimated F = False, fork_length was measured frame_wt Weight (pounds) of specimen frame in postprocessing sample (ODFW only) freq specifies the number of fish observed with the same characteristics specified in the other columns of each bds_fish record maturity PacFIN maturity code maturity_agcode Agency maturity code. W=?, O: 0=unknown, 1-2=immature, 3-8=mature, C: 1=immature,2,3,4,5,6,7,8=undeterminable, 9=not noted sample_no Unique number for each sample sample_year Year (YYYY) in which sample was obtained sex PacFIN sex code sex_agcode Agency sex code source_agid PacFIN code of source agency species_code Agency species code spid PacFIN species code bds_gr .... TABLE; biological data agency to PacFIN gear code mapping agid PacFIN agency code description Description of gear gear Agency gear code grid PacFIN gear code modified Flag indicating whether modified since created bds_pr .... TABLE; biological data agency to PacFIN port code mapping agid PacFIN agency code description Name of port modified Flag indicating whether modified since created pcid PacFIN port code port Agency port code bds_sample .... TABLE; biological data samples block State fishing area block number comments Comments cond PacFIN condition code cond_agcode Agency condition code data_type "C" = commercial sample, "S" = Survey dealer Dealer code or name of plant where sample was obtained depth_avg Average depth fished in fathoms depth_max Maximum depth fished in fathoms depth_min Minimum depth fished in fathoms disp PacFIN disposition code disp_agcode Agency disposition code drvid Derived vessel id females_num Number of females in sample females_wgt Weight (pounds) of females in sample ftid Fish ticket number gear Agency gear code grade PacFIN grade code grade_agcode Agency grade code grid PacFIN gear code inpfc_area Agency INPFC area code inpfc_arid PacFIN INPFC area code (type 2) latitude Latitude in decimal degrees longitude Longitude in decimal degrees males_num Number of males in sample males_wgt Weight (pounds) of males in sample market_category Agency market category (state spcode) of species sampled; market_category submitted by CA and OR; null for WA mesh_size Mesh size in tenths of inches. other_sample_no Other sample no. if received from another agency pcid PacFIN port code port Agency port code psmfc_area Agency PSMFC area code. W: 1C, 2A, 2B, 2C, 2E, 2F, 3A, 3B, 3C, 3N, 3S O: 1C, 2A, 2B, 2C, 2E, 2F, 3A, 3B, CS C: 1A, 1B, 1C psmfc_arid PacFIN PSMFC area code (type 1) sample_agency Agency code for agency that obtained sample. N=NMFS Newport, W, WA=WDFW, OR, O=ODFW, C,CA=CDFG, T=NMFS Tiburon, ED=EDCP, MS=Mackerel Studies, PW=Pacific Whiting, SP=Special projects, T-Treaty sample_agid PacFIN code for agency that obtained sample sample_day Day of month that sample was obtained sample_method Sample method. R=Random, S=Stratified, N=Systematic, P=Purposive, X=Special sample_month Month (MM) in which sample was obtained sample_no Unique number for each sample sample_strat Sample stratification sample_type Sample type. M=Market, R=Research, S=Special request, C=Commercial on-board sample_year Year (YYYY) in which sample was obtained sampler_code Sampler initials or number second_stage_unit Second stage of sample design (fixed number or weight) source_agid PacFIN code of source agency. W=Washington, O=Oregon, C=California, M=NWFSC, F=AKFSC total_clusters Number of clusters in sample total_fish Number of fish in sample total_wgt Total landed weight (in pounds) for just that species portion of the landed catch veid Vessel id weight_source Weight source wgtmax Top of weight range (pounds) that processor is using to define grade (NWFSC srtgrp) for this sample wgtmin Bottom of weight range (pounds) that processor is using to define grade (NWFSC srtgrp) for this sample bds_sample_odfw .... TABLE; biological data sample columns specific to ODFW adj_bothn Total number of "adjusted" fish in sample adj_fmnum Total number of females minus number of females that can't be aged adj_fmwt Total weight (pounds) of females minus estimated weight of females that can't be aged (estimates come from the most current length/weight equation accepted for that species and sex) adj_mnum Total number of males minus number of males that can't be aged adj_mwt Total weight (pounds) of males minus estimated weight of males that can't be aged (estimates come from the most current length/weight equation accepted for that species and sex) chan1 Descriptor of 1st loran coordinate chan2 Descriptor of 2nd loran coordinate exp_wt Weight (pounds) the sample will be expanded to (default is the landing weight). This value may be larger or smaller than total_wgt depending on various situations documented in text file ~pacfin/doc/bds_odfw_expanded_weight.txt fr_bothn Total number of frames in postprocessing sample fr_fmnm Total number of female frames in postprocessing sample fr_fmwt Total weight (pounds) of female frames in postprocessing sample fr_mnum Total number of male frames in postprocessing sample fr_mwt Total weight (pounds) of male frames in postprocessing sample hours Hours fishing keep Flag field indicating whether the sample can be aged (default is "True") max_sizein Maximum grade size (inches) for length graded species max_sizelb Maximum grade size (pounds) for weightgraded species min_sizein Minimum grade size (inches) for length graded species min_sizelb Minimum grade size (pounds) for weight graded species msec1 First loran coordinate of catch msec2 Second loran coordinate of catch nspec Number of specimens of sample quality Sample quality level. sample_no Unique number for each sample sample_year Year (YYYY) in which sample was obtained sampler Sampler name(s) source_agid PacFIN code of source agency start_bien Otolith tray number first otoliths go into state State area of catch (default is area of port if area of catch is not known) tow Trawl tow that was sampled (if known) unk_num Total number of sex unknown fish in sample unk_wgt Total weight (pounds) of sex unknown fish in sample vess_name Vessel name bds_sp .... TABLE; biological data agency to PacFIN species code mapping agid PacFIN agency code description Scientific name and/or common name and/or general description modified Flag indicating whether modified since created spcode Agency species code spid PacFIN species code bds_ul .... TABLE; biological datafeed update log agid PacFIN agency code processdate Date on which the datafeed was processed rptcomment Comment or description regarding the datafeed rptdate This is the date of report; (i.e. the date the datafeed was generated); two datafeeds from the same source may not have the same date. sample_year Year (YYYY) in which samples were obtained bds_units_conversion .... TABLE; unit of measure conversion factors conv_factor Conversion factor used to convert from_um to to_um from_um From unit of measure to_um To unit of measure unit_domain Unit domain ("WEIGHT" or "LENGTH") block_pos .... TABLE; center position of blocks agid agency ID block Block No. lat center latitude lon center longitude cg .... TABLE; contains coast guard vessels data adr1 vessel owner's address (line 1) adr2 vessel owner's address (line 2) adr3 vessel owner's address (line 3) bldyr year the vessel was built breadth breadth of beam, widest measure city vessel owner's city cname vessel owner's company name depth depth of hold fname vessel owner's first name grosswt vessel's gross weight (tons) hailing_port the port where vessel is based hp horse power of vessel hp_main_astern main power at stern hull_config configuration of hull hull_design type of hull hull_material material hull is composed hull_number hull number (identifier) hull_shape shape of hull latest boolean(T/F); ='T' if this record has the latest pubyr+ pubmo of the vid len length of vessel (feet) lname vessel owner's last name netwt vessel's net weight (tons) pubmo the month data of vessels published pubyr the year data of vessels published service vessel service type state vessel owner's state trade1 trade indicator; coastwise unrestricted trade2 trade indicator; Great lakes trade3 trade indicator; fisheries trade4 trade indicator; bowater trade5 trade indicator; registry trade6 trade indicator; recreation vid vessel id vname vessel name zip vessel owner's zip code cl .... TABLE; contains code list data code actual code description description of the code length length of code name name of the code list year code's effective year; 0=all years cnty .... TABLE; Contains PacFIN county ids amd attributes cid County id. corder Used to order counties from south to north. county Name of county. entered Date row was entered into the database or date of last mod. rid1 Regional identifier #1; groups counties into regions; see table cnty_region rid2 Regional identifier #2; groups counties into regions; see table cnty_region shortname Short name is used for reporting. state Two character state abbreviation cnty_region .... TABLE; Contains PacFIN county region identifiers and description region Description of the region rid Regional identifier; groups counties into regions; cor_gear .... TABLE; contains corrected gear info, columns are identical to FTL table with additional columns cor_gear, and rule. agid Agency id. area Source agency catch area code. areatype Type of area of this catch; see area-type in table cl. category State agency species-id or market_category. cdfg_origin Origin of catch for cdfg transactions only. Contains CDFG block number. cond Condition of catch at time of landing; see condition in table cl. cor_gear Gear group assigned by correction algorithm. day The day the catch was delivered. disp Disposition of this catch; see disposition in table cl. estimated Boolean(T/F); ='T' if dollar value is estimated. factor Round weight conversion factor scaled nn.nnn. ftid Fish ticket identifier. gear Source agency gear code. grade Grade and size category of this catch; see grade in table cl. landed_wt Landed weight, units=pounds. modified Boolean(T/F); ='T' if this record has been modified. month The month the catch was delivered. num_fish The number of fish caught; nominally for salmon only. For CDFG salmon for 1986 thru the present this statistic is a derived value based on average weights developed from samples. For CDFG for 1981-1985 numbers-of-fish for salmon are not available (i.e. num_fish is set to null). pargrp Participation group; see par-group in table cl. ppp Price per pound; units = dollars per pound (xxxx.xxx). rule Rule applied to correct gear. ulid Report number of original data feed or of most recent modification. worst_est Boolean(T/F); ='T' if worst $/lb estimate from species dataset was used for ftl ppp in this record. year The year the catch was delivered. dc .... TABLE; contains detail catch data agglvl detail aggregation level (D, W, M, or Y) agid agency of source data arid PacFIN area id cond condition of catch day day or week of catch estimated boolean(T/F); ='T' if estval $ value is an estimate estval actual or estimated revenue(cents) grade grade or size of catch grid PacFIN gear id heads Y = on; N = off; lbs weight of catch in pounds lbspriced catch weight that was priced lpok boolean(T/F); ='T' if lbspriced is not null modified boolean(T/F); ='T' if the record has been modified month month of catch nf number of fish nfok boolean(T/F); ='T' if nf is not null nl number of landings nlok boolean(T/F); ='T' if nl is not null pargrp participant group pcid PacFIN port or country id spid PacFIN species id ulid report number of initial data feed or of most recent modification valok boolean(T/F); ='T' if estval is not null worst_est boolean(T/F); ='T' if worst $/lb estimate was used year year of catch de .... TABLE; contains detail effort data agglvl detail aggregation level (D, W, M, or Y) agid agency of source data arid PacFIN area id day day or week of catch daysfished number of days fished (note: if originally reported as null then set to 1 here) deliveries number of deliveries grid PacFIN gear id mgrp management group modified boolean(T/F); ='T' if the record has been modified month month of catch pargrp participant group pcid PacFIN port or country id trawlhrs hours fished with trawl gear ulid report number of initial data feed or of most recent modification year year of catch dl .... TABLE; contains detailed updatelog information agid reporting agency catchmode A=aquaculture; C=commercial; I=indian; U=unspecified lbsadded number of pounds added lbschged number of pounds changed lbsdlted number of pounds deleted mgrp management group; example: 'GRND', 'SAMN', 'SRMP' period period of data update activity periodtype W=week; M=month; Q=quarter; Y=year recsadded number of records added recschged number of records changed recsdlted number of records deleted ulid used to identify the corresponding updatelog record year year of data update activity ft .... TABLE; contains fish ticket data agid agency id day the day the catch was delivered by the fisher. Numeric, # in month daysfished the number of days fished drvid the derived vessel id. contains the u.s. coast guard or state marine board number; this is a derived value set during the update process (ft_update module). The drvid should be considered the 'Coastwide Identifier'. ftid Fish ticket identifier. The ftid must be qualified at a minimum by agency id for uniqueness. A more complete criteria would include pargrp(C-"non-indian commercial" or I - "indian comercial") and also year, month, and day modified boolean(T/F); ='T' if this record has been modified month the month the catch was delivered pargrp the participant group; see par_group in table cl port the port-of-landing processorid The identifier for the processor company or buyer that processed, or received, the delivery of fish. For CDFG this column contains a buyer-id and the processor-id is derived by taking the first five characters only. removaltype Obsolete - no longer valid in table FT tdate ticket date ulid report number of original data feed or of most recent modification veid vessel id - The veid must be qualified by agid. vesseltype vessel type; see vid-type in table cl vesselused boolean(T/F); ='T' if a vessel was used year the year the catch was delivered ftl .... TABLE; contains fish ticket line data. adj_grid Adjusted grid. Set using data in column ftl.gear, table agr, and table cor_gear agid Agency id. area Source agency catch area code. areatype Type of area of this catch; see area-type in table cl. assignment Catch assignment. See code list for assignment. The list includes vessel, state agency, NOAA - revenue based. category State agency species-id or market_category. cdfg_origin Origin of catch for cdfg transactions only. Contains CDFG block number. cond Condition of catch at time of landing; see condition in table cl. day The day the catch was delivered. disp Disposition of this catch; see disposition in table cl. estimated Boolean(T/F); ='T' if dollar value is estimated. factor Round weight conversion factor scaled nn.nnn. ftid Fish ticket identifier. The ftid must be qualified at a minimum by agency id for uniqueness. A more complete criteria would include pargrp(C-"non-indian commercial" or I - "indian comercial") and also year, month, and day gear Source agency gear code. grade Grade and size category of this catch; see grade in table cl. landed_wt Landed weight, units=pounds. legal_removal Boolean(T/F); = 'T' if the removal was legal. modified Boolean(T/F); ='T' if this record has been modified. month The month the catch was delivered. num_fish The number of fish caught; nominally for salmon only. For CDFG salmon (i.e. chinook) for 1986 thru the present this statistic is a derived value based on average weights developed from samples. For CDFG for 1981-1985 num_fish for salmon are not available (i.e. num_fish is set to null). For ODFW num_fish is *NOT* provided (i.e. all values for num_fish are null). For WDFW salmon the num-fish values are actual counts of fish (i.e. no sampling for average weight). For WDFW num_fish is provided for a few non-salmon species. nwr_le_permid Limited entry pemit identifier overage Boolean(T/F); = 'T' if the catch exceeds the limit pargrp Participation group; see par-group in table cl. ppp Price per pound; units = dollars per pound (xxxx.xxx). product_form The form of the product. See code list for product_form product_use Intended use of the catch. See code list for product_use removal_type Removal type is either (commercial, EFP, personal use, research, or commercial (direct sale). See code list. ulid Report number of original data feed or of most recent modification. worst_est Boolean(T/F); ='T' if worst $/lb estimate from species dataset was used for ftl ppp in this record. year The year the catch was delivered. fusport .... TABLE: Table fusport includes total commercial landings by port (i.e. pcid). Table fusport includes catch for all species for all management groups, including shellfish and treaty-Indian from all catch areas including Puget Sound, Alaska Ocean areas, the Columbia River, and Washington coastal rivers. agid agency identifier nftids number of fish ticket identifiers nprocessors number of processors ntrips number of trips nvids number of vessel identifiers pcid port identifier port_name name of port rev revenue rwt_lbs round weight in lbs year year gr .... TABLE; contains tree of PacFIN gear-ids and attributes description description of the gear or gear-group entered date row was entered into the database or date of last modification; format: yymmdd grgroup This entry's gear-group grid Gear ID: a gear code, gear group or 'all' shortname the short name used for reporting summarized boolean(T/F); ='T' if summarized data exists for this code type 1=gear code; 2=gear group; 3='all' lbk_catch .... TABLE; trawl logbook catch records agid Data source agency ID apounds adjusted pounds(on trip level) for CDFG, ODFW, and WDFW. Applies to all three states. apounds_calculated values = Y/N; Y = calculated using FTL catch; N = apounds set equal to hpounds (no match) apounds_wdfw ticket adjusted pounds for WDFW only (representing missing logs as well) condition condition (possible future use) disposition disposition (possible future use) grade grade (possible future use) hpounds hailed pounds source source of column apounds; values: L = agency LBK datafeed; F = fish-ticket landed-catch using program lbk_set_apounds spcode species code townum Key to lbk_tow trip_id Key to lbk_trip warning Warning flag, see WARNING in table cl n-th charcter corresponds to flag for n-th column lbk_ftid .... TABLE; ftid-logbook matching tables agid key to ft and ftl tables in fishticket system ftid key to ft and ftl tables in fishticket system pargrp key to ft and ftl tables in fishticket system source source of tripid-ftid match; values: L = agency LBK datafeed F = FTL table using program lbk_set_apounds ticket_date date on ticket (use for key to ft) townum key to lbk_tow in trawl logbook subsytem trip_id key to lbk_trip in trawl logbook subsystem lbk_gr .... TABLE; logbook gear/net-type codes agid agency ID created date code was first created description description grid PacFIN gear code (link to gr) modified date code was last modified net_type gear code used on logbook lbk_pr .... TABLE; logbook port codes agid agency ID created date code was first created description description lbk_port port code used on logbook modified date code was last modified pcid PacFIN port code (link to pc) lbk_sp .... TABLE; logbook species codes agid agency ID category agency category code (link to asp) created date code was first created description description lbk_spcode species code used on logbook modified date code was last modified lbk_strategy .... TABLE; strategy codes agid agency ID created date code was first created lbk_strategy Strategy code used on logbook modified date code was last modified strategy PacFIN strategy code (link to strategy) lbk_tow .... TABLE; Trawl logbook tows adj_towtime wdfw adjusted tow time, represents missing logs as well, see documentation for details agid Data source agency ID area state area of set position arid_psmfc PSMFC area ID, might be filled from block_pos table set position block block number (10x10 minutes): set position block_or ODFW 5x5 minutes block number: set position ch_lat 1st Loran radio channel: set position ch_long 2nd Loran radio channel: set position depth1 1st depth in fathoms (see depth_type1) depth2 2nd depth in fathoms (see depth_type1) depth_type1 type of depth1 N=Net depth, B= Bottom depth, A=Average depth, + = Max depth, - = Min depth depth_type2 type of depth2 N=Net depth, B= Bottom depth, A=Average depth, + = Max depth, - = Min depth duration tow duration (up-set time) in hours latlong_type source of lat/long info L = Logbook entry C = Center of area, entered by agency staff B = Center position of block, from the block_pos table lbs_adj Adjusted weight in pounds(California and Oregon) at the trip level lbs_adjexp WDFW adjusted weight in pounds (representing missing logs as well) lbs_hail Hailed weight in pounds msec_lat milli seconds from 1st channel: set position msec_long milli seconds from 2nd channel: set position net_type net type pacfin_target Target species - pacfin code ps_grnd_code Puget Sound ground code, see maps in "History of WA State Marine fish management areas" WDFW technical report# MRD 97-04 ps_mgmt_area Puget Sound management area, see maps in "History of WA State Marine fish management areas" WDFW technical report# MRD 97-04 ryear Year of return set_lat latitude of set position converted from degrees/minutes to degrees.decimal set_long longitude of set position converted from degrees/minutes to degrees.decimal set_time time net was set (hhmm) target Target species/ strategy code (note that only 48% of all tows have a designated target) tow_date date tow occurred townum number of tow on trip trip_id Key to lbk_trip up_area state area of up position up_arid_psmfc PSMFC area ID: up position up_block block number (10x10 minutes): up position up_block_or ODFW 5x5 minutes block number: up position up_ch_lat 1st Loran radio channel: up position up_ch_long 2nd Loran radio channel: up position up_lat latitude of up position converted from degrees/minutes to degrees.decimal up_long longitude of up position converted from degrees/minutes to degrees.decimal up_msec_lat milli seconds from 1st channel: up position up_msec_long milli seconds from 2nd channel: up position up_time time net was hauled up (hhmm) warning Warning flag, see WARNING in table cl n-th charcter corresponds to flag for n-th column lbk_trip .... TABLE; trawl logbook trips agid Data source agency ID daysfished days fished (wdfw only) daysout days at sea (wdfw only) dday Date of departure, Orcale date truncated to 12 am dmonth Month of departure dport departure port drvid derived vessel ID, usually CG# dtime departure time (hhmm) dyear Year of departure ft_match_flag Trip has/has no (T/F) matching fish ticket ncrew Crewsize, including captain ngal Fuel consumed in Gallons(Note that only 13% of all trip_id's have a fuel value entered) ntows number of tows on trip (wdfw only) rday Date of return, Oracle date truncated to 12 am region region of trip (wdfw only) 1=Canada, 2=Alaska, 3=Coastal, 4=Straits&Gulf, 5=Puget Sound & Hood Canal rmonth Month of return rport return port rtime return time (hhmm) ryear Year of return towsrec number of tows recorded (wdfw only, prior to 19?? only every 4th tow was recorded) trip_id Trip ID, sequence generated veid Vessel ID as in source data vidtype type of veid, see vid-type in table cl warning Warning flag, see WARNING in table cl n-th charcter corresponds to flag for n-th column lbk_ul .... TABLE; logbook update log agid data source agency ID comments comments supplied by person processng the data data_year year of data datafile location of input data file as given to loading routine proctime time data was loaded in DB (down to the second) records number of records of this type loaded type record type A=adjusted pounds (WDFW only) C=catch, F= Fishticket match, H= Haul/Tow, T= Trip lep .... TABLE; contains limited entry permit data expdate permit expiration date gear_endor permit gear endorsement issdate permit issue date permid permit id permtype permit type lep_app .... TABLE; contains data of vessels that appealed the denial of a groundfish permit begdate begining of appeal date end1 permit gear endorsement enddate ending of appeal date permid permit id pstatus permit status code vid vessel id lep_status .... TABLE; contains domain of permit status code permit status code. 0=Pending renewal, 1=Under review, 2=Pending evidence, 3=Sanction, 4=Blank, 5=Cancelled, 6=Appeal, 7=Dead (expired), 8=Dead (combined), 9=Active description description of status lep_type .... TABLE; contains domain of permit type code permit type. Corresponds to permtype in lep. A=Permanent, B=Permanent, C=Provisional permanent, D=Temporary, E=None, U=Unknown sc agglvl M=Month, Y=Year description description of permit type npac4900 .... TABLE; contains daily summaries for the W-O-C at-sea fishery summarized from NORPAC tow-by-tow observations accumulated_by day, week, month, indicating species-comp aggregation level arid PacFIN area id catcher vessel id for cather vessel cdq_code community code (used to identify tribal) day day (1 - 31) grid PacFIN gear id month month (1-12) processor vessel id for processing vessel, CP or Mother ship spid PacFIN species id total-weight total catch in metric tons wt_retained retained catch in metric tons year year of catch nv .... TABLE; contains non-vessels data used in ft transaction agid agency id artvid artificial vessel id; used in construction of derived vessel id for ft transactions to ensure assignment of catch to correct vessel idtype vessel id type; see table cl veid vessel identifier year year of vessel nwr_lep_src .... TABLE; contains limited entry permits source data from NWR Permit Office dory_endr 1 => dory small fleet expdate permit expiration date ghid permit holder id goid permit owner id h_name permit holder name h_pob permit holder p.o. box h_pob_city permit holder p.o. box city h_pob_state permit holder p.o. box state h_pob_zip permit holder p.o. box zip code h_str permit holder street h_str_city permit holder street city h_str_state permit holder street state h_str_zip permit holder street zip code issdate permit issue date len_endor endorsed permit length (+- 5 feet) lgln_gear 1 => longline o_name permit owner name o_pob permit owner p.o. box o_pob_city permit owner p.o. box city o_pob_state permit owner p.o. box state o_pob_zip permit owner p.o. box zip code o_str permit owner street o_str_city permit owner street city o_str_state permit owner street state o_str_zip permit owner street zip code permid permit id platoon code in month ok to land fish if trawl pstatus permit status code sable_tier sablefish allowable catch tier 1, 2 or 3; 0 = no tier trap_gear 1 => trap trwl_gear 1 => trawl vid vessel id vname vessel name ok_gear .... TABLE; contains FTLs which should not be corrected by gear correction algorithm. columns as in FTL agid Agency id. area Source agency catch area code. areatype Type of area of this catch; see area-type in table cl. category State agency species-id or market_category. cond Condition of catch at time of landing; see condition in table cl. day The day the catch was delivered. disp Disposition of this catch; see disposition in table cl. ftid Fish ticket identifier. gear Source agency gear code. grade Grade and size category of this catch; see grade in table cl. landed_wt Landed weight, units=pounds. month The month the catch was delivered. pargrp Participation group; see par-group in table cl. year The year the catch was delivered. omibbfee .... TABLE; Vessel Summaries for verifying fee collections for 2003 vessel buy-back project. For NMFS Office of Operations Management and Information agid agency identifier; see table ag; W = WDFW; O = ODFW; C = CDFG buyer buyer or processor who processed the catch; see table apc council area designation: P = PFMC; N = NPFMC; * = neither lwt_lbs landed weight in pounds month month of landing; 2 digits revenue revenue in $US rwt_lbs round weight equivalent in pounds spid PacFIN species identifier; see codelists on PacFIN website tdate fish-ticket date; date landing received from fisher year year of data; 4 digits pc .... TABLE; contains codelist of PacFIN codes for ports and also codes for port groups, regions, subregions, counties, joint-ventures(81-90), and foreign countries(81-88). agid Agency ID associated with this code. cid obsolete; see column CID in table APR entered Date row was entered into the database or date of last modification; format: yymmdd. grouporder North to south order for code groups. name Name associated with this code. pcgroup Code Group (port group, region, or country group) pcid Code ID (port, port grp, region, subregion, jv, or country) pcorder North to south order for code. sgroup Port group for grouping samples of landed catch (i.e. port sampling). (Only used by CDFG.) shortname Short code name used for reporting. summarized Boolean(T/F); ='T' if summarized data exists for this code. type Type of code; 1=port/subregion/country/jv; 2=portgrp/region; 3=state/jvp/foreign; 4=nwafc(jvp+foreign)/ALP (all domestic); 5=all U.S. data sources. prin_cmplx .... TABLE; Table includes all vessels, and only vessels, registered to land marine species in W-O-C ports. This includes catch from all areas including Puget Sound, Columbia River, and Alaska areas. drvid vessel identifier gr_lbs primary gear for lbs gr_lr gear relation(same or diff) gr_rev primary gear for revenue nwr_le NWR limited entry permit(True or False) prncmp_lbs principal complex for lbs prncmp_lr complex relation(same or diff) prncmp_rev principal complex for revenue spid_lbs primary spid for lbs spid_lr spid relation(same or diff) spid_rev primary spid for revenue year year prmt_own .... TABLE; WOC permit owner information actdate date permit activated for a vessel or a fisher address Permit owners address agid Data source agency ID city city of residence expdate date permit expires or is de-activated, if not renewed owname Permit owners name permid Permit id permtype Agency defined permit type state state of residence year Year of permit zip zip code prmtdesc .... TABLE; WOC permit attributes agid Data source agency ID description description of permit permtype Agency defined permit type restricted restrictions on permit - Boolean T/F transfer transferability either N = not transferable; K = transferable, but vessel is NOT transferred (sold) with the permit V = transferable, and vessel IS transferred (sold) with the permit qar .... TABLE; contains area id of QSM reported catches entry arid groundfish area id of a QSM reported catch qdisp .... TABLE: contains QSM data to identify category of fish on various QSM reports arid Area id grid Gear id ord Dummy order for software development purpose qdisp text displayed on report spid Species id qdpar .... TABLE; contains QSM date parameters description detailed description of usage qdate date usage indicates where the date is used qhg .... TABLE; contains QSM harvest quideline data arid area id of harvest guideline description description of the harvest guideline grid gear id of harvest guideline hg quota units=metric-tons ord used for display purposes; matches with ord in qdisp table spid species id of harvest guideline year year of harvest quideline qovrd .... TABLE; contains QSM correction factors(CF) override data ag override agency ar override area id cf actual CF value; =null if surrogate exists gr override gear id sp override species id srag surrogate agency srar surrogate area id srgr surrogate gear id srsp surrogate species id qpc .... TABLE; contains QSM port id data pcid a valid port id of QSM subsystem qprompts .... TABLE; contains prompt data of QSM data entry ag agency of data entry arid area id of data entry disp_entry actual display grid gear of data entry lb_catch catches in pound ord order of prompt for data entry process_dt date the data entry is processed spid species of data entry qrc .... TABLE; contains QSM reported catches data agid agency id of reported catches arid area id of catch b_date beginning date of catch cum 'C' indicats catch is monthly cumulative, otherwise weekly e_date ending date of catch ent_date date that catch was entered into PacFIN database est_catch estimated catch in pound fleet L = Limited Entry; O = Open Access; T = Total Fleet grid gear id of catch mod_date date that catch was modified spid species id of catch unit unit of catch; 'P'=pound qsc .... TABLE; contains subset of summary catch used in QSM subsystem agglvl summary aggregation level (M or Y) arid area id in qar table grid gear id in qsp table lbs aggregated weight pcid port_country id in qpc table period month or year period spid species id in qsp table year year of catch qscsp .... TABLE; contains species id used in QSM and summary catch qsm_sp a valid species id of QSM subsystem sc_sp summary catch species id correspond to QSM species id qsp .... TABLE; contains species id and gear id for entering QSM reported catches grid a valid gear id of QSM data entry spid a valid species id of QSM data entry qti .... TABLE; contains WDFW treaty Indian SABL catches data lbs_catch catch in pounds lvl Y (year) or M (month) period period of catch year year of catch sc .... TABLE; contains summary catch data agglvl summary aggregation level (M or Y) arid PacFIN area id estval estimated revenue(cents) grid PacFIN gear id lbs aggregated weight lbspriced aggregated weight that was priced nf aggregated number of fish (salmon only) nl number of landings(port aggregation only) pcid PacFIN port_country id period month or year period spid PacFIN species id year year of catch sc_le_oa .... TABLE; contains summary catch data for limited entry and open access fleets. Used in Quota Species Monitoring (QSM) Subsystem. agid same as in table ag for WDFW, ODFW, and CDFG arid PacFIN area id estval estimated revenue (cents) fleet L = Limited Entry; O = Open Access grid = "ALL" unless spid = "SABL" then "TWL", "TWS", or "USP" lbspriced aggregated weight that was priced nf aggregated number of fish (salmon only) nl number of landingsi (port aggregation only) period MONTH = 1-12; YEAR = 99 spid PacFIN species id weight aggregated weight (pounds) year year of catch scm .... TABLE; contains species composition data agid agency id arid PacFIN PSMFC area code or PSMFC sub area code; may be NULL (i.e: no stratification by area) coeffvar coefficient of variance; day ignored if period='M' or 'Q'; if period='W' specifies that proportion applies to the 7-day period ending on this YEAR-MONTH-DAY grid PacFIN gear or gear-group code (e.g: 'TWL','GFT') modified boolean(T/F); ='T' if this record has been modified month month or quarter for which proportion applies; if period='Q' then specifies last month of quarter (3=Q1, 6=Q2, 9=Q3, 12=Q4) pcid PacFIN portcode; exception: if CDFG then contains a sample port group (see table PC); may be NULL (i.e: no stratification by port) period w=week; m=month; q=quarter proportion estimated proportion for species within the specified time-period, port, area, gear, and unspid sample_cond L = live; D = dead; 9 = all landing conditions samples number of samples spid PacFIN SPID code, usually a rockfish species (e.g. 'WDOW') ulid report number of initial data feed or of most recent modification unspid PacFIN unspecified species id (an SPID code) year year for which proportion applies scm_history .... TABLE; contains a history of all species composition data supplied by the state agencies(WOC). agid agency id of the data provider. arid PacFIN PSMFC area code or PSMFC sub area code; may be NULL (i.e: no stratification by area) coeffvar coefficient of variance; day ignored if period='M' or 'Q'; if period='W' specifies that proportion applies to the 7-day period ending on this YEAR-MONTH-DAY file_name The file name of the data file which was loaded to the database. grid PacFIN gear or gear-group code (e.g: 'TWL','GFT') modified boolean(T/F); ='T' if this record has been modified month month or quarter for which proportion applies; if period='Q' then specifies last month of quarter (3=Q1, 6=Q2, 9=Q3, 12=Q4) pcid PacFIN portcode; exception: if CDFG then contains a sample port group (see table PC); may be NULL (i.e: no stratification by port) period w=week; m=month; q=quarter proportion estimated proportion for species within the specified time-period, port, area, gear, and unspid report_date This date corresponds to the date in the header record of the original datafile supplied by the agency. sample_cond L = live; D = dead; 9 = all landing conditions samples number of samples spid PacFIN SPID code, usually a rockfish species (e.g. 'WDOW') trans_op Transaction operation. A code which describes what the data loading program should do with the associated record. Ex: update,insert,delete. ulid report number of initial data feed or of most recent modification unspid PacFIN unspecified species id (an SPID code) year year for which proportion applies se .... TABLE; contains summary effort data agglvl summary aggregation level (M or Y) arid PacFIN area arid daysfished number of aggregated days fished (note: if originally reported as null then set to 1 here) deliveries number of aggregated deliveries grid PacFIN gear id mgrp management group pcid PacFIN port or country id period month or year period trawlhrs hours fished with trawl gear: only applicable for grid within TWL,TWS subtrees; otherwise value is zero. (including at grid=ALL) year year of catch sp .... TABLE; contains PacFIN species-id and attributes actsp boolean(T/F); ='T' if SP table entry is for an actual scientifically-defined species bsacomplex used to group bsa species in subcomplexes bsaorder used to order species for bsa retrievals cname species common name complex species grouping complex2 additional species grouping complex3 additional species grouping complex4 additional species grouping cporder used to order species complex for retrieval entered date row was entered into the database or date of last modification; format: yymmdd grp_nom_act groups nominal and actual species codes together (e.g. YTR9 = YTRK, YTR1) gulfcomplex used to group gulf species in subcomplexes gulforder used to order species for gulf retrievals mgrp management group ncflag boolean(T/F); ='T' if the species used in npfmc reports nom_act Actual or nominal species (A,N or null) pcflag boolean(T/F); ='T' if the species used in pfmc reports ppp_lower lower bound of price per pound; units=dollars per pound ppp_upper upper bound of price per pound; units=dollars per pound regionflag boolean(T/F); ='T' if the species used in region wide rpts sname species scientific name spid a species code, complex or management group sporder used to order species for retrieval stateflag boolean(T/F); ='T' if the species used in state reports summarized boolean(T/F); ='T' if summary data exists for this code type species code type: 1 = s