Attachment3_Presale_Process_1.pdf

The Presale process is crucial to the LE Department in the lead up prior to a Lease Sale. The following outlines the various things that are involved in set up and running of the process.

A Workflow Chart of the Presale Process

General Assumptions:

- The following document is written with the base assumption that the user has some working knowledge of ArcGIS and TIMS.

For ArcGIS, this means that you know how to log on, connect to SDE if needed and have some basic rudimentary knowledge of how to add data. For TIMS, this means you know where and how to log into it and can find your way to a form or two.

Roles Needed

Before anything can be run in the process, you need to make sure that you have the proper TIMS roles to do so. The roles you should assign yourself in TIMS prior to starting will be the following:

PRESALE_SUID

SALE_MOCK

Area of Interest (AOI) or Clip Coverage Creation Prior to the step where you will Create an Available Blocks List, you will need to create an AOI to load into the system. This is typically done by making a subset of either the Block Area Polys (BAPALL) or Blocks Feature Class or Coverage. Typically, https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_process%20(1).jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_process%20(1).jpg this is done with BAPALL.

Start up ArcGIS and add the BAPALL Coverage. You will do this via the Add Data button and make a mapped drive connection to the /timsgis/data/gulf/nad27/bapoly directory and choose the bapall coverage there.

(Optional) If needed, add any of the line-work (8G, FedState, etc) and any other files necessary to frame the area you are looking to grab.

Zoom to an area and, using the Select Tool, drag an envelope over the area you are looking to grab. Optionally, if your area is smaller, you can select individual blocks by holding the Shift key down when you are making your selection. Finally, eliminate any Non-US blocks. This can be done via the Select by Attributes function where MMS_PLAN_AREA_CD is equal to EGM, CGM or WGM.

Right-click on the BAPALL layer in the Table of Contents and choose Selection -> Create Layer from Selected features. This will create a temporary feature class in the Table of Contents.

Launch the Feature Class to Coverage tool. Use the Bapall Selection feature layer as the input. Save the output to your UNIX home directory with an appropriate name. The tool normally defaults to a REGION as output type, this must be changed to POLYGON.

NEVER EVER move a coverage with Windows Explorer!!!

Mock Sale

Leading up to a Lease Sale, the LE Department will typically do a lot of preparation, part of which involves something called a Mock Sale. This is done to verify blocks available for the sale, any stipulations and deferrals, among other things. The process starts in TIMS and progresses to the GIS side (currently on UNIX) and involves a few things:

1. Header Creation

2. Mapping Preparation

3. Utilities to Facilitate the GIS Presale Process

Create Mock Sale Data

The information below this, including the Header Creation document that's attached, is a walk through of everything that is usually involved in the background of creating the data. A quicker route, if there's a previous sale in a similar area, is to do the following:

In TIMS, navigate to Sale -> Administration Forms -> Forms -> Create Data for Mock Sale https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_aoi_sel.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_aoi_sel.jpg

After clicking, you will be prompted with this screen:

Put in the Sale you want to migrate data from in the Old Sale Number field, then enter the New Sale Number in the other field. A mock sale is usually designated by a letter following a 3 digit number (Example: 227 is a sale number, 227B is a mock sale). Hit the Commit button and, if your commit is successful, you can exit out of the form and TIMS and proceed to the Initialize/Migrate part.

Header Creation

One of the first things done for a Mock Sale is the header creation. This is a series of forms in TIMS that are populated for the UNIX interface to properly do an Initialize/Migrate. Information on what's needed is found in the Presale Process Header Creation document that's attached to this page.

Mapping Preparation

The following steps must be completed before running the Presale Mapping Utilities for the current sale:

1. Set up the sale stage checklist. For an example see the WGOM Sale 200 stage check list.

2. IMPORTANT NOTE: The blocks type (ex. "stip3") may not be the same as the previous sale. Change the checklists as needed.

3. Verify that the Oracle Tables and corresponding coverages from the last sale of the same planning area are in sync.

Although they would have been verified for the last sale, the Oracle tables could have been updated with new information since the sale was completed. In this case, the Oracle table will not agree with the coverage from the last sale; update the coverage using the Presale Build Menu. For further instructions see Make Sure Tot_Blocks in Coverage is equal to available blocks in the Oracle Table.

4. The current sale must be in the (Tims Menu) Sale Schedules form and have blank (Oracle) header records associated for each coverage copied. Admin enters in the header records for each Mitigating Measure Form (Deferrals, Stipulations, ITL) and Administration Form (Lease Term, Minimum Bid, Bid System).

STEP-BY-STEP INSTRUCTIONS

https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/mock_sale2.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/mock_sale2.jpg

Presale Main Menu

1. Initialize/Migrate

2. Create an Available Blocks List

3. Create ISTEP Oracle Tables

4. PNOS and other stages

5. The FNOS Stage

Set the Presale Stage Status to N Set the Presale Stage Status to Y How to edit the data after the status has been set toY

6. Populate Tracts

7. Uses for the Presale Oracle Tables and Coverages

8. List of Oracle Tables and the sn_fk used during Presale mapping processes

9. OracleTables - Index to Field Names

10. Oracle Tables - Description of each table type

11. SQL queries used to check the Presale ORACLE tables

12. Build Sale Bids Coverage

13. Add Accessibility to ArcGIS Basemapping application

1. Initialize/Migrate

This option is run by the Presale user to set up a directory structure for the new sale data under the presale folder of the system data directory (ie: /timsgis/data/gulf/nad27/presale for Gulf implementation site).

https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_menu.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_menu.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/presale/temp.html

Initialize/Migrate Menu

Login to the TIMS Mapping Menu using login instructions . Using theTIMS Mapping Menu Presale utility, the Presale Mapping process begins by using the copying the Arc/Info coverages from the FNOS workspace of the last sale into the ISTEP workspace for the current sale. This is accomplished using the Initialize/Migrate function.

The Initialize/Migrate utility completes the following processes (listed in order):

Initialize creates a new workspace for the current sale under /timsgis/data/gulf/nad27/presale (ex. 171) and creates the new workspaces for each of the stages (ex. ISTEP) under the sale workspace.

Migrate uses the blocks coverage in the /timsgis directory and intersects it with the copies all the prior sale's FNOS coverages to create the new ISTEP coverages.

Sale Number = last sale number Migration Sale Number = current sale number Manually create a "def_5year" coverage for 5-year deferrals to use for mapping. (5-year deferrals are not put in any of the tables.)

2. Create an Available Blocks List https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_init.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_init.jpg

Update Oracle Menu

Before creating any of the new blocks lists, make a list of all blocks that are available for the sale by using the "Update Oracle Menu" function (avail_block_areas). This list will normally contain all of the blocks in the planning area, less any 5-year deferrals. When a table is created (using the Update Oracle Menu), a blocks list is first created from the existing coverage, this list is compared to the avail_block_areas list, and any block that is not in the avail_block_areastable is not included in the table that is being created.

Select "Update Oracle Menu" Change directory to /timsgis/data/gulf/nad27 In the "Existing subdirectories' menu, click on the appropriate planning area (ex. wgm_plan) In the "Coverages" menu, click once on the appropriate cover (ex. wgm_plan) Click on "Available Blocks" in the 'Please choose the Oracle table...' option Enter the sale number Apply Check the number of blocks in the table

Before you create the ISTEP tables:

Verify that the tot_blocks attribute value in the coverage is equal to available blocks in the OracleTable!

In the coverage, does the number of blocks listed in the tot_blocks attribute field of each ISTEP coverage match the total number of blocks that should be available for this sale?

If the block number does not match then:

Delete or rename the coverage that is in the new sale istep workspace.

If the Oracle table from the last sale, stage FNOS is the same as this sale is, then run the ORACLE Coverage routine from within the Presale Build Menu, using the Oracle table from the last sale's FNOS. Edit the"sale_number" and "presale_stage_id" attributes in the new coverage to match the current sale https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_oracle.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_oracle.jpg

OR

Create a new coverage from blk_clip and edit the attributes to match the items in its category (ex. stip) When tot_blocks agrees with the number of blocks that are available, you can proceed to create new Oracle tables for the first stage (ISTEP).

3. Create new ISTEP Oracle tables for the current sale

IMP! Read the help box in step 2 above before you proceed with this step.

Select "Update ORACLE Menu" Change directory to /timsgis/data/gulf/nad27/presale/<current sale #>/istep Under the covererages menu, click on the coverage to use Click on the Oracle table to be updated Under "PLEASE ENTER THE KEY INFORMATION" enter the appropriate data in the blanks.

How the process works:

General info:

In general terms, the Presale 'Update ORACLE Menu' is used to create a new Oracle table for each block-list type (stips,deferrals, lease terms,bid systems, minbids). The TIMS Presale utilities accomplish this by using Arc/Info macros that intersect each coverage with the blocks coverage in order to create new 'blocks lists'. Each blocks list is then written to a new corresponding Oracle table (remember that a header record must already exist for this to work). The header for the new Oracle table includes the new stage and the date the header was created.

Proofing:

Once the ISTEP coverages and tables are created for the current sale, each must be proofed. (Note that differences are not always due to errors, i.e., changes to the Oracle tables could have occurred due to changes in blocks made available for leasing. Also, there has traditionally been a problem with dates between the blocks and blocks_geographs tables.) Proofing is mainly accomplished by counting the number of blocks in a table and comparing it to the number of blocks listed in the corresponding coverage.

To use SQL queries to check the Presale tables:

(see standard queries that are available located below under SQL Queries )

1. Find the sn_<table>_fk number

2. Use the fk number to count the number of blocks in the table.

If the number of blocks in the Oracle table is incorrect the missing/extra blocks are identified and corrected by SME (who enters the correct information in the table using the Tims Menu). Once the Oracle table is correct, Tara corrects the corresponding coverage by re-running the Presale Build utility.

If the number of blocks in the Arc/Info coverage is incorrect, or becomes 'out of sync' with the Oracle table (and the number of blocks in the Oracle table is correct) then the errors are generally found by performing one or more of the following steps:

re-run the Presale Build utility intersect the stipulation coverage with the blocks coverage and create a list of blocks to compare with the Oracle table check for slivers or other common Arc/Info errors

The Proofing process continues for each stage until the final notice of sale stage (FNOS) is correct and complete. Once the FNOS stage is finalized and approved by the Sales & Support Unit

Supervisor, the FNOS flag for each table is set to "Y".

4. PNOS and other stages

The original design plan for the Presale function was to process about 12 stages, starting with ISTEP and ending with FNOS.

At this time, these are the only stages are being processed. On occasion, the PNOS stage is run at the request of the Sales & Support Unit. See the FNOS stage for instructions.

5. The FNOS Stage

A. Presale_stage_id = "N"

Create new Oracle Tables for the FNOS stage Use the ISTEP Oracle tables to create the new FNOS Oracle tables. This is accomplished by using the 'Update ORACLE Menu. Using the Update ORACLE Menu, complete the following steps:

Change to the presale ISTEP workspace (/timsgis/data/gulf/nad27/presale/sale#/ISTEP) Under the list of coverages, click once on the name of the coverage Choose the Oracle table to be updated by clicking once on the item (someof the items in the menu below will become filled out) Fill in all blanks The Presale Stage ID is the stage of the new Oracle table.

The Unique Identifier is for lists that have more than 1 in their category.

For example, if there are 3 stipulation types (topo, MWA, mine warfare),then each stipulation type will have a number, or unique identifier such as stip_1, stip_2, stip_3. The unique identifier for leases would be 5(for 5-year term), 8 (for 8-year term), or 10 (for 10-year term).

To find the Presale Option Number, check the header record for that table.

The Presale Stage Status is 'N' until Sales & Support lets us know that the tables are finalized. When they are correct, rerun this option for every list with a Presale Stage Status of 'Y'.

Presale Build Menu https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_build.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_build.jpg

To create new Arc/Info coverages for the FNOS stage.

The Update Oracle menu does NOT copy the ISTEP coverages from the ISTEP workspace to the FNOS workspace.

Run the "Presale Build Menu" for each table.

The "Directory:" at top -doesn't matter, coverages are automatically saved in the home directory. Copy them to the FNOS workspace manually..

Select ORACLE->Coverage.

Under "SELECT ORACLE TABLE TO BUILD PRODUCT FROM:", click once on the tabletype.

Under "USER INFORMATION", fill in each blank

For Coverage/File Name, ex. coverage "stip_1" - use prefix (ex. stip); the program will add "_1" based on the number entered in the "Primary Term" blank After filling all blanks, click apply.

Verify that the coverage has the correct attributes, including the number of blocks in the coverage agree with the number of blocks in the oracle table.

Verify that the tot_blocks attribute is correct.

Verfiy that the polygons are the correct blocks.

IMP: Copy the 5-year deferral coverage (def_5year) to the FNOS workspace.

B. Set the Presale Stage Status to "Y":

This stage is often referred to as "finalizing" the tables. In this stage, another new set of Oracle tables are created for the FNOS stage, but the Presale Stage Status field is set to "Y".

IMPORTANT NOTE: In this stage, at the end of the update process, the utility kills the coverage in the presale FNOS directory; then it will copy the coverage you have selected BACK into the fnos directory. For this reason you should FIRST copy the coverages from the FNOS directory into your home directory and use those coverages in your home directory to run the update utility.

1. First copy the coverages in the FNOS directory to your home directory.

2. In the "Update" Menu, change to your home directory, click once on the name of the coverage in the coverages list

(the right menu).

3. In the Presale Stage Status, enter "Y"

4. A message box will pop up that says "A block lists already exists..continue?". Select Yes. Click Apply.

5. Verify the number of blocks in the Oracle table.

How to edit the data after the status has been set to "Y" Ex. After the status has been set to "Y', the data is considered final. However, blocks could be deferred before the lease sale takes place; theOracle tables and coverages must be updated to include these deferrals.

First, the Oracle table must be edited; change the status flag back to"N".

The blocks must be deleted from the Oracle table by Pat Bryars.

Verify which table needs to be edited. (For example, if 4 deferrals are added, you need to find out which type of deferral in order to edit the correct table.)

Rerun the Presale Build Menu to create a new coverage that excludes the new deferrals.

Rerun the Update Oracle Menu using the new coverage. Set the final status to "N".

Check the new Oracle table.

If the new table is correct, run the Update Oracle menu, setting the final status to "Y".

7. Populate Tracts This process populates several tables (tracts tables and blk_lease_areas tables.

Populate Tracts Menu

Important Note: If the block dates have been changed, rerun the Create an Available Blocks List utility before populating the tracts and blk_lease_areas tables.

Select the 'Populate Tracts' option from the Presale Main Menu.

Type in a cutoff date (for month enter the first 3 letters of the month) Select 'Choose a Planning Area and Sale Number' Enter the appropriate info (Sale Area and Sale Number) Select 'Populate BLK_LSE_AREAS and TRACTS tables' Using the Populate Tracts Menu, build coverages using the 'Build BLKLSE' coverage and 'Build TRACTS Coverage' menu.

8. Uses for the Presale Oracle Tables and Coverages

The Presale Oracle tables are used by the Sales & Support Unit for 'mock sales'. The final FNOS coverages will be used to make the Proposed and Final Notice of Sale Maps.

12. Build Sale Bids Coverage

1. From the Presale Main Menu, select "Build Sale Bid Coverage".

2. Enter the sale number in the Sale Number window.

3. The new bids coverage will be locate in /timsgis/data/gulf/nad27/presale/bids.

4. Create a shapefile from the coverage and submit to TIMS.

https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_tracts.jpg https://doimspp.sharepoint.com/sites/bsee-gis-wiki/techlib/how-do-i_presale-process/presale_tracts.jpg