LandWorks Interface

The Excalibur Land Module can interface with LandWorks, Inc. LandScape GIS. This interface includes a UniBasic process that will run on a nightly basis. The purpose of this process is to build/refresh data tables that update LandScape GIS.

LandScape GIS currently depends on a common lease number for partial interest leases with the unique designation being provided by a suffix. Excalibur does not require a suffix designation. Although, most Excalibur users build in a suffix-style designation of varying types (ex., 1234-001, 1234,001, 1234A, etc.). The detail specifications prepared assume that the Excalibur account will use some type of built in suffix to their lease numbering scheme. Under this circumstance, an I-descriptor customized for each licensing account will be required to accommodate LandScape GIS.

Should any licensing party utilize next sequential numbering, or any other numbering scheme that does not provide the ability to group leases with shared lands by a common element within the lease number, then additional programming logic will need to be incorporated.

The UniBasic process noted above will build approximately 17 different tables. Because each company will have a different set of selection criteria (Activity Codes, Lease Type Codes, Company Numbers, etc.), a limited amount of time will need to be spent for each licensing company to determine specific selection criteria. This criterion will have to be incorporated on a custom basis for each account.

Creating the Initial Extracts

Run the VOC paragraph CREATE.LANDWORK.FILES. This paragraph selects and saves lists of all the client's leases, tracts, LTDs and leasehold owners, and invokes 17 VOC paragraphs (all of which have names like LW_...). Each LW_... paragraph runs a UniQuery, writing a "report" (also named LW_...) to the HOLD file, and then writes a reformatted copy of the report (named LW_..._CR or LW_…DAT) to the hold file. LandWorks will pick up the 17 LW_..._CR or LW_…DAT files and load them into its database.

'Incremental' Extracts

This process scans the LW.BEFORE.IMAGES file, selecting the leases, tracts, etc. that have changed since the last run, and runs the appropriate LW_... paragraphs for them, creating new HOLD files. It also clears the LW.BEFORE.IMAGES file and prints a report of any Excalibur records deleted (or "none"). The LandWorks import process does not handle deletions (it is a set of "load" utilities), so any such deletions must be made manually in the LandWorks system. Once the incremental-update procedure has been run successfully from the menu, it can be set up to run automatically each evening as a "chron" on a unix server or “at” on a Microsoft server (see "Automating..." below).

'Universal' Extracts

If the client wants to run universal extracts (rebuilding the entire LandWorks database each time), they can run the CREATE.LANDWORK.FILES paragraph from TCL whenever desired, until the process is automated. Once the universal-update procedure has been run successfully from the menu, it can be set up to run automatically each evening as a "chron" on a unix server or “at” on a Microsoft server (see "Automating..." below).

Automating the Process

After the initial extracts have been successfully loaded by LandWorks, the entire process can be set up to run unattended each night. There are three steps:

1. The Excalibur extract

2. Copying the files from the Excalibur machine to the LandWorks machine

3. The LandWorks load.

• Paradigm (STS) sets up step 1

• LandWorks sets up step 3

• Client's system administrator (if any) sets up step 2.

If the client has no one capable of doing this, then STS and/or LandWorks must do it. In any case, the automated jobs must be timed so that each step finishes before the next step's start time, and they should all run in the "window" from the end of the working day to the start of system backups.

When you have the timing worked out, ask STS to set up step 1 (and if necessary step 2), as "chron" or “at” jobs. The step-1 job needs only to start a UniData session in the live area and run the VOC paragraph CREATE.LANDWORK.FILES (for "universal" extract) or LW.REPORTS (for "incremental" extract). The step-2 job is typically done with ftp, moving the 17 LW_..._CR OR LW_...DAT files from /exc/_HOLD_ to wherever LandWorks is.

Customizing

There is very little UniBasic programming in the LandWorks Extract project. Most of the code is in the form of dictionary items and paragraphs, so any needed customization can usually be done by the account rep.

If the client wants to extract only one company's leases, the paragraph CREATE.LANDWORK.FILES can easily be modified. If the client is doing "universal" updates, only this one paragraph need be unique. However, if the client does "incremental" updates, then several of the LW_... query paragraphs must also be changed (alternatively, you could have a programmer add some selection logic to the program LW.REPORTS in EDI.BP).

Form of the LW_... Paragraphs

All seventeen LW_... paragraphs are similar in form (example LW_DEPTH_SEVERANCE follows).

• 001: PA Export a file of (updated) Depth Severances for LandWorks

• 002: SETPTR 0,1024,0,0,0,3,BANNER <<C1,Report Name>>,NFMT,NHEAD,BRIEF

• 003: DISPLAY Generating report <<C1,Report Name>> from file TRACT.MASTER... :

• 004: TIME

• 005: GET.LIST <<C1,List Name>>

• 006: SELECT TRACT.MASTER WITH LEASE.NUMBER\

• 007: WITH SIDE.FROM OR SIDE.TO\

• 008: OR DEPTH.FROM OR DEPTH.TO\

• 009: OR FORMATION.FROM OR FORMATION.TO

• 010: SORT TRACT.MASTER BY.EXP LEASE.NUMBER\

• 011: ID.SUP HDR.SUP NOPAGE COUNT.SUP REQUIRE.SELECT LPTR\

• 012: LEASE.NO.PRE FMT '17L' COL.HDG 'AGMT_NUMBER '\

• 013: LEASE.NO.SFX FMT '17L' COL.HDG 'AGMT_SUFFIX '\

• 014: @ID FMT '18L' COL.HDG 'TRACT_NUMBER '\

• 015: SIDE.FROM FMT '30L' COL.HDG 'DEPTH_FROM_DESC '\

• 016: SIDE.TO FMT '30L' COL.HDG 'DEPTH_TO_DESC '\

• 017: DEPTH.FROM FMT '6L' COL.HDG 'DEPTH_FROM_MEASURE'\

• 018: DEPTH.TO FMT '6L' COL.HDG 'DEPTH_TO_MEASURE'\

• 019: FORMATION.FROM FMT '14L' COL.HDG 'FORMATION_FROM'\

• 020: FORMATION.TO FMT '12L' COL.HDG 'FORMATION_TO'

• 021: LW_REFORMAT <<C1,Report Name>>

Some of the paragraph's form must be retained through any customization.

1. The paragraph name, select-list name (see line 5) and hold-file name (see lines 2 and 21) must all be the same. The LW_REFORMAT program (invoked at the end) uses this identity.

2. The SETPTR page-size parameters on line 2 should be 1024,0 (i.e. 1024 wide and indefinitely long). You can reduce the width, provided you make it at least as wide as the report data - we don't want the UniQuery to flip to "vertical format"!

3. The report qualifiers on line 11 should be in every LW_... paragraph, standard or unique.

4. Note the REQUIRE.SELECT option on line 11. When the LIST/SORT statement (line 10 here) is invoked, it will generate a regular report from the records currently selected or, if no records are currently selected, it will (because of the REQUIRE.SELECT) generate a 2-line "no items" report (which is what LandWorks needs in this situation). Any WITH phrases which might lead to no records being reported should not be part of the LIST/SORT…REQUIRE.SELECT statement. They should be in a preceding SELECT statement (lines 6-9 here).

5. Each column of the report (lines 12-20 above) must be specified on a separate line, with both COL.HDG and FMT. The column headings and formats should not be changed without LandWorks’ concurrence. Program LW_REFORMAT (the final step of the paragraph) also uses this information to determine the report's full width.

6. Program LW_REFORMAT must follow the query. It copies the hold file LW_... to hold file LW_..._CR, replacing field marks with carriage-returns, eliminating any blank or overflow lines, and padding out each line to the full report width. The LW_…_CR file is in the format LandWorks needs. Don't try to Edit it!. However you can in Extremis Edit the LW_... hold file and then just run LW_REFORMAT on it, to regenerate the LW_..._CR hold file.

Add new trigger names to 11 files in the Audit Master. In Edge you can do this through the Audit Master Maintenance screen (trigger names are called Interface Processes there). In Sequel they are not yet on the screen, so you must use the editor to put the trigger name(s), multi-valued where necessary, on line 19 of each affected Audit Master record. Add the trigger name LW.UPDATE to all 11 files:

• LEASE.MASTER

• LEASE.TRACT.DETAIL

• NAME.ADDRESS.MASTER

• LEASE.TYPE.CODES

• COUNTY.MASTER

• STATE.MASTER

• PROSPECT.CODES

• MAP.COORDINATE.CODES

• LEASE.STATUS.CODES

• TYPE.INTEREST.MASTER

• TRACT.MASTER

On the TRACT.MASTER, also add a second trigger name LW.TRACT.UPDATE.

Run the VOC paragraph ACTIVATE.LANDWORK.TRIGGERS. (You must be logged in as "unidata" [or whatever is necessary to be logged in as the file owner] to do this). Updates to the 11 files will then start to be logged in the LW.BEFORE.IMAGES file.

Three dictionary I-items may need to be "uniqued," depending on how the client numbers leases and on the lease activity codes used. The items are LEASE.NO.PRE and LEASE.NO.SFX (on the Lease Master, Lease Tract Detail and Tract Master). These distinguish the "main" and "sub" parts of partial lease numbers. If in doubt, consult with client's land rep. Also, ACTIVE.INDICATOR on the Lease Master and Lease Tract Detail : this should be "Y" for leases considered active by the client.

Create an initial set of hold files for export to LandWorks, comprising all land leases and tracts, their owners, etc. Run the VOC paragraph CREATE.LANDWORK.FILES, to create 15 hold files:

• LW_AGREEMENT_LEVEL

• LW_TRACT_LEVEL,

• LW_OWNER

• LW_OWNERSHIP

• LW_CODE_AGMT_TYPE

• LW_CODE_COUNTY

• LW_CODE_STATE

• LW_CODE_PROSPECT

• LW_CODE_SURVEY_ABSTRACT

• LW_CODE_TRACT_STATUS

• LW_CODE_INTEREST_TYPE

• LW_REMARK

• LW_REMARK_TEXT

• LW_LEGAL_DESCRIPTION

• LW_DEPTH_SEVERANCE