Queries

This page contains step-by-step guidance for the most common queries in the Queries menu and their function, but will not be an exhaustive list. This information is primarily intended for Agency staff who need detailed eLandings information for reporting purposes. 

The different types of queries and their use can be found below.

Accessing the Queries Menu

To access the Delinquent Fish Ticket reports, simply click on the Queries drop down menu and again, select Queries.

Delinquent Fish Ticket Queries

The Delinquent Fish Ticket reports show up in the main query list. These reports help identify landing reports that have been created and saved in the eLandings repository database, but the signed copy of the fish ticket has not been either submitted to the local office of ADFG or has yet to be processed by staff.  This is a pretty simple report as it searches for and returns all records without batching information – the Office code, Batch number and Sequence number.

When you select the delinquent report, a dialog box opens and requests that you set the number of days delinquent.  If you do not set the days delinquent, then all un-batched fish ticket reports will be returned.  Please also keep in mind that all un-batched fish ticket reports, for all fisheries are returned.

Please note in the illustration below, highlighted in yellow, the range of CFEC permits, from groundfish to salmon.

To display and save only those reports that is pertinent to your fisheries management team, simply select the Excel filter feature. 

Select Sort & Filter and then Filter.  When you make this selection a down arrow will appear on each on the columns.  Click on the CFEC Fishery column and a complete summary of all permit fisheries in the spreadsheet will display.  All values will be selected.  Simply unclick the Select All option at the beginning of the list and then scroll thru the list to locate the CFEC Fisheries that you wish to review. 

Once the filter is determined, only those records that pertain to your requirements are displayed.

Permit Queries

Their are several types of Permit Queries that show up in the main query list. These queries will help identify various permits that exist in eLandings and the users that posses them.

CFEC Permit Query

This query will allow you to see all CFEC Permits and the information associated with a particular Permit.

Select CFEC Permits from the menu and click 'Run'.

A popup will open to allow you to enter your desired search criteria.

Enter a START_DATE.

Enter an END_DATE.

After you've entered the date range click 'Execute' to run the query.

After the query runs it will either email or download a .csv file depending on whether or not 'Email Results' is checked.

The results.csv file will contain each permit broken out into it's component parts and the data associated with that permit. (ex. Revoked or not, Name, etc.)

Dual Permit Queries

Dual Permit Salmon Landing Reports show those landing reports that have more than one CFEC permit associated with them.

Select one of the Dual Permit Queries from the menu and click 'Run'.

Enter or select a START_DATE and END_DATE for your query.

Click 'Execute' to generate your results.

The data will be available as a results.csv file.

Federal Permit Query

The Federal Permit Query provides a list of all the valid Federal Processor Permits (FPP) and Federal Fisheries Permits (FFP) from a specified time frame. This query will combine both the FFPs and the FPPs into .csv document. The information that is included is:

• Federal Permit Number (either the FFP or FPP)
• Processor Name
• At Sea Processor (Yes/No)
• License Year
• Effective Date

To run the Federal Permit Query, navigate to Queries/Tools > Queries, then navigate to page 2 using the page numbers at the bottom of the screen. 

Find the Federal Permit query, and select the "Run" button associated with it. 

Enter the START_DATE and the END_DATE. Then select "Execute" to generate the report. The report will either download immediately if you have unchecked the Email Results box, or be sent to the email address on file with your User ID. 

The results will download in a .CSV file for you to search and sort as needed. 

Bristol Bay Queries

The Assurance Report was developed at the request of Bristol Bay processors to assist them in identifying fish tickets that documented incorrect information.  Information contained in the report include:

• Date fishing began
• Date of landing
• Time of landing
• Statarea of harvest
• Gear
• Landing report id
• Fish Ticket Number
• Fishery (as defined by district and gear)

The procedure used by processors is to generate the Bristol Bay Weekly Report and if discrepancies are identified, run the Data Assurance Report to identify the specific report(s) containing incorrect information.  The ticket(s) is corrected and the Bristol Bay Weekly Report generated from eLandings again.

Select the Bristol Bay Data Assurance Report from the menu and click 'Run'.

A popup will open and allow you to select your search criteria. Enter a START_DATE and END_DATE.

Enter the PROC_CODE (a processor or F-code) for the processor whose data you desire. 

The results will be downloaded (or emailed) as results.csv. Below you can see a TEST version of the report.

Landing Report Queries

Fisheries Mgmt Pgm (Fisheries Management Program)

The Fisheries Mgmt Pgm report allows a user to search for landings reports using a CFEC Fishery, a Federal Management Program, and a specified date range as parameters. The report returns the fish ticket number, landing report ID, vessel ADF&G as well as landing and catch data for each landing that matches the search criteria.

To retrieve this report, navigate to Queries/Tools > Queries, then choose page 2 from the page numbers at the bottom of the screen. 

Select the "Run" button associated with the Fisheries Mgmt Pgm query. 

Fill out the following information: CFEC Fishery, the federal fisheries management code (2-3 digit alpha numeric code for the federal fishery) and the date range. 

The parameters might be something like this:

When the parameters are entered, hit "execute." The report will either be sent directly to the email you have on file, or will automatically start downloading. If you have selected the direct download choice, the download will show up at the bottom of your screen, and will be titled result.csv

Clicking on the download will open the file in excel. From here you can search and manipulate the data as desired. 

Report Detail By...

There are multiple queries that begin with "Report Detail by..." They are:

• Report Detail by CFEC Fishery
• Report Detail by CFEC Fishery and Management Program
• Report Detail by Management Program
• Report Detail by Port and CFEC Fishery
• Report Detail by Port and Management Program

These reports can be found with the other queries on pages 4 and 5.

The five reports all provide landing details with results narrowed by different parameter combinations. All five are narrowed by a selected date range, then depending on your needs, you can choose one of the five reports to further narrow the search. Because they all have the same functionality and provide the same informational details, only one example is provided. However, this one example should give you an understanding of how to run these five reports effectively. 

To run the Report Detailsby CFEC Fishery and Management Program, first click the "Run" button associated with it.

Enter the CFEC Fishery, the Fishery Management Program, and the start and stop date. then hit execute. 

The Report Detail by CFEC Fishery and Management Program report will download. Landings include Management Program, Program ID, CFEC Permit number, ADF&G, date of landing, port of landing, Proc Code, Fish Ticket Number, gear code, species code, species condition code, landed weight, round weight, ice and slime, disposition code, harvest code, ADF&G Office code, Sequence ticket number, landing report number, report type and trip number. There will be a line for each species reported on each fish ticket. 

Operations Queries

Buying Station Operation Statuses Query

The Buying Station Operation Statuses Query is used to search for Buying Stations registered in eLandings that have a different status then the parent operation they are registered under.

The run this query navigate to Queries/Tools > Queries, then select the Buying Station Operation Statuses "Run" button. 

This report requires no parameters, to run the report, click "Execute"

The report will either be emailed to the address you have on file, or downloaded automatically as a .CSV file to be opened in excel. 

The file will contain a list that looks similar to the example above. The statuses of the Buying Station and parent operation will be listed next to the names for easy reference.

This list can be filtered or sorted at the user's convenience to extract the necessary data. 

Closed/Archived Operations with Reports

The "Closed/Archived Operations with Reports" query lists all the closed or archived operations and the number of reports that they have in the system. This is a useful tool for eLandings staff and eLandings developers to reference the number of reports a closed or archived operation has, and as a reference to make sure that no closed or archives operations with reports get deleted. The query contains the following information:

• Operation ID
• Operation Name
• Status of Operation
• Operation Type
• Number of Reports

To access this query, navigate to Queries/Tools > Closed/Archived Operations with Reports

Select the "Run" button on the "Closed/Archived Operations with Reports"

This search has built in parameters, so to run the report, just clicks "Execute"

A .CSV file will either be sent to the email on file, or automatically start downloading. The default list is sorted by status, and then by name. As you can see in the screen shot below, many archived or closed operations have no reports associated with them, some have a few, and others have many. 

To sort the list to see the operations with archived reports, choose the "Data" tab in excel, and then click "Sort." A new window will open and allow you to choose how you want to organize the data. In this case, we will sort by number of reports, and then change the order of the results to largest to smallest. Click "OK" to make the change. 

Immediately the list will sort by number of reports, and allow the user to see the closed or archived operations with the most number of reports. 

Operations for a User

The query "Operations for a User" lists all the operations an eLandings user is authorized on. To run this report navigate to Queries/Tools > Queries, and then using the page numbers at the bottom of the screen, find the "Operations for a User" query, and click the "Run" button associated with it. 

You will need to know the User ID that you wish to search for. Enter that User ID in the Value box, then click "Execute"

The .CSV file will download and can be opened in an excel spread sheet. The query will provide a list of all the operation names that the selected user is authorized on, as well as the operation ID, authorization type, and the status of the operation.

Processor Queries

SeaLandings Queries

There are three SeaLandings Queries available in the Agency Web. These three queries are used to help eLandings staff provide user support to seaLandings users. 

Sealandings Emaillf History

Sealandings Emaillf History provide programmers and eLandings support staff with a detailed log of file transmissions being sent in to the sealandings server. To get this information, sign into the Agency Web interface, then navigate to Queries/Tools > Queries. Use the page numbers at the bottom of the query page to navigate the queries and get to the Sealandings Emaillf History Query then click the "Run" button on the same line. 

No parameters are required, so clicking "Execute" will start the query download. 

A .CVS document will begin downloading. The document will have details about the IERS Monitor Date, the user ID, and the webservice method. 

Sealandings Emaillf User

The Sealandings Emaillf User provides important information about sealandings users to eLandings staff. This information is primarily used to assist sealandings users with troubleshooting problems that may arise. 

To run the Sealandings Emaillf User Query, use the eLandings Agency website to navigate to Queries/Tools > Queries, then use the page numbers at the bottom of the screen to navigate the to Sealandings Emaillf User Query. Click "Run" to start.

No parameters are needed to run this query. To begin the download, click "Execute."

Leaving the "Email Results" box checked will result in the query .csv file to be sent to the email address associated with your user name. If the box remains checked, once you click "Execute" the Execute Query dialog box will close and a green informational banner will appear at the top of your screen providing information on the success of the query request, and a time frame within which the results will be emailed. 

When the results are processed you will receive an email with a .csv attachment.  

Open the email and download the attachment

When you open the query file it will provide you with UserIDs, Schema version, the last date the user logged into sealandings, and the number of calls to the server for all active sealandings user IDs. 

Sealandings User Version

The Sealandings User Version query provides details about the operating system and software versions that sealandings users are operating with. This query takes the Sealandings Emaillf User query and expands on it, providing more details for troubleshooting or sending receipts. 

To run this query, go to Queries/Tools > Queries. Use the page numbers at the bottom of the screen to search through queries and find the query entitled "Sealandings User Version," select the "Run" button on the same line. 

No information is needed to give this search parameters, it will return information on every registered sealandings user.

To execute the query and start the download, click "Execute." The .CSV file will automatically begin downloading.

The information provided in the report includes the User ID, the operating system that the sealandings program is installed on, the java version, the release build they are operating, the schema version, last date of use, and number of calls to the server. 

To search or narrow the results, click on the Data tab, then hit "Filter." Next, click the down arrows on the column header to open options for filter the results. You can use the search bar, or sort the columns. 

Trip Number Queries

Logbook Queries

Flow Scale Tests Query

The Flow Scale Test Query provides a summary of all the flowscale tests recorded in a single vessel's logbook. To retrieve this information, use the Agency Web to navigate to Queries/Tools > Queries. Use the page numbers at the bottom of the browser screen to navigate through the queries until you find the Flow Scale Tests Query.

Select the "Run" button associated with the Flow Scale Query.

Enter the date range you wish to search for using the START_DATE and END_DATE, then enter target vessel's ADF&G. Click "Execute" to generate the report

The .CSV file will download and can be opened in an excel spreadsheet. The query will contain all the entered flow scale tests entered by the selected vessel within the date parameters set. Addition information will include Logbook ID, the entry sequence of the flow scale test, time of test, platform weight, flow scale test weight, the percentage error and any comments entered. 

Production Report Summary

The Production Report Summary is used to see a detailed summary of all production reports for an at sea or shoreside operation. The report provides a spreadsheet with each line on all production reports based on the date parameters. 

To run the Production Report Summary Query, navigate to Queries/Tools > Queries; then, using the page numbers at the bottom of the browser screen, navigate through the pages until you find the Production Report Summary Query. To start the query, click the "Run" button associated with it. 

Enter the federal permit number you wish to search (either the FFP or the FPP) and the date parameters, using the START_DATE and STOP_DATE. Click Execute to run the query. 

A detailed .CSV document will download. Users can sort and filter for species, disposition code, or management program as needed to narrow the detailed information. 

Other Queries

Voided Landing Reports

This query will let you see all the years (select when running) voided landing reports. 

Select the query from the list and click 'Run'.

Enter the Year and click 'Execute'.

The results will be a .csv file.