Oracle/Unix Script
Documentation
The Oracle/Unix scripts described in this documentation serve three purposes - check data validity,
distribute information to an end user and provide access to the data in the TOW database
to an application written in Microsoft Access or a similar product.
In the first case, a SQL script is run against
an Oracle View, formatting the output to be inserted into the body of an email or appended to the
email as an attachment. This action is generally scheduled to run at a specific time of day, usually in
the morning so that the user will have a report available to them at the beginning of the day.
The vast majority of the scripts documented here run this type of data validity check.
In the second case, either a SQL script is run against an Oracle View and the output is distributed as above
or files are copied from an NT share into a Unix public directory where they are sent as
attachments in an email.
Third, there are a few miscellaneous SQL scripts and Views that have been created to allow a user to link
to the TOW/Aries database with Microsoft Access through an ODBC connection. These Views have been created to
hide complexity from the end user as well as to provide an interim solution to the problem of the owner of
the TOW/Aries tables being changed. In this case, a 'dummy' account has been created with Views pointing
to the actual tables. Current Access applications link to the Views owned by this 'dummy' account.
Due to the structure of some of the output and SQL scripts, this document is not particularly printer friendly.
Report Components
Email-
The majority of these scripts use email to communicate with their
intended audience. The shell script generates some sort of output
and either incorporates it into the body of an email or sends it as
an attachment. These emails are sent automatically and scheduled as
a cron job.
Each email contains information that will identify the computer and Unix shell script that ran the job.
The HOST is the name of the Unix server where the shell script is located. The SCRIPT contains the path and
name of the script.
Attachments-
Approximately half of the emails that are distributed have one or more files included as an attachment.
These attachments are either XLS or CSV files to be opened using Microsoft Excel. The other half of
the emails distributed contain the output of any queries in the body of the email.
Unix Shell Scripts-
The Unix Shell Script is the file that is executed to generate or collect any output, format it, attach a
cover letter and distribute the information via email to the intended recipient.
SQL Scripts-
The SQL scripts run by the shell script are run against Oracle Views in the OKCADMIN account on HOU_TOW.
The resulting dataset is formatted by these SQL scripts for inclusion into an email.
Oracle Views-
The Oracle Views actually do the calculations and generate the dataset that is sent out via email. The SQL
scripts described above only format the output. This method was chosen so that a user could link to a View
with Microsoft Access to check the dataset at a time that was not scheduled in the crontab, without re-distributing
any emails. If an email is required, the user should simply execute the appropriate shell script manually.
Report Descriptions
This report lists MOC operated producing oil (PO) or gas (PG) wells in OKC with no oil or
gas production for the past two weeks.
The report also shows the status on the last date of oil/gas production.
This report is run weekly on Monday morning at 8:00 am and distributed to Carol Schlegel with a copy being
sent to Rex Cravens and Kandy Lawson.
-
Each well completion can have a maximum of 4 gas meters assigned to it and each meter must
each have a unique designation of GasVol1 thru GasVol4. This report includes well completions
that have duplicate GasVol values in the USER_XREF field of the VC_EQUIPMENT table, whether
the current status is active or inactive.
This report is run weekly on Monday morning at 8:00 am and distributed to Kandy Lawson with a copy being
sent to Rex Cravens.
-
The Aries monthly production table should contain one record per well completion per month,
posted on the last day of the month. This list includes ARIES and TOW wells that have more
than one entry for production in a given month in the ARIES monthly production table (VC_ARIES_WC_MM).
This report is run every weekday morning at 8:00 am and distributed to Kandy Lawson with a copy being
sent to Rex Cravens.
-
This list includes wells where the gas allocation calculation may not have
run propely on the referenced entry date. It is compiled by comparing the allocated
production to the sum of all of the unallocated gas meter volumes for each completion.
If the allocated production is zero and the sum of the gas meter volumes is not zero,
then it is assumed that the allocation calculation did not run properly and the completion
is included in the list. The sum of the unallocated gas meter volumes is displayed for reference.
This list should only be used as a guideline as, depending on time of day and other factors,
a certain amount of disagreement between allocated and unallocated production is normal.
This report is run every morning at 8:00 am and distributed to Kandy Lawson with a copy being
sent to Rex Cravens.
-
The shrinkage calculation for KATF is calculated by comparing the sum of the volumes measured
at the check meters upstream of the plant with the sum of the volumes measured at the sales
meters downstream of the plant for the date range between and including the effective date
and the date of the report. The shrinkage is reported as a decimal value.
This report is run every Friday morning at 10:00 am and distributed to Suzi Thompson with a copy being
sent to Kandy Lawson and Rex Cravens.
-
The Aries monthly production table should contain one record per well completion per month,
posted on the last day of the month. This list includes ARIES and TOW wells where the production
value is posted on a day of the month other than the last day of the month in the ARIES
monthly production table (VC_ARIES_WC_MM).
This report is run every weekday morning at 8:00 am and distributed to Kandy Lawson with a copy being
sent to Rex Cravens.
-
This report contains attached spreadsheets which are 'quick look' gas rate, and tank
level reports for Scada wells for an area. The report columns include, Well/Gas
Meter Name, Tubing Press, Casing Press (if available), Gas Purchaser, Static
Press, Last Poll Time, Current Flow Rate, Yesterday's Average Flow, and Alert.
The reports are sorted by route. There are Alert indicators for Gas Volumes and
Tank Levels.
The report compares the Current Flow Rate with Yesterday's Flow Volume. Current
readings should update about every two hours.
The West Watonga report is run twice daily, once at 6:15 am and then again at 1:45 pm. It is
distributed to
Mike Lawson, Roy Green, Lee French, Ron Hays, John Brinska, Tim Brown, Mackie Hutchison, Jim Kornele and Diane Redger
with a copy being sent to
Allen Wilson, Paul Lupardus and Rod Caldwell.
A log of the action is sent to
Rex Cravens.
-
The VC_DOI_HDR table contains WI, NRI and Shrinkage values for each property. Each value (record)
has a beginning date and ending date. This report tests the validity of the dates in the table.
The list displays wells where the date ranges in the VC_DOI_HDR table are not contiguous.
It is compiled by counting the number of days in each date range for a particular property,
summing them and comparing to the count of days between the first date in the date range
for the property and the last date in the date range of the property. If the values are
different, there are overlaps, gaps, duplicate values, incorrect dates or dates in the wrong order.
This report is run weekly on Monday morning at 8:00 am and distributed to Kandy Lawson with a copy being
sent to Rex Cravens and Suzi Thompson.
-
These are additional scripts and files which do not fit into a single category or are not closely
attached to the rest of the procedures documented here.
-
The account on the server HOUUCT which owns these scripts is CODYMRP. Logging on to this account
by starting a command prompt and connecting with Telnet will take the user to the HOUUCT prompt.
-
A description of the directory structure on HOUUCT.
-
Cron schedules the Unix Shell scripts to run at a specific time.
-
To edit the Unix shell scripts, the user can work directly in Unix, which is the most straightforward method, or
FTP the script to a Windows computer, edit it there and then FTP it back to the Unix server.
-
The documentation files (this set of documents) for these scripts are located on the server HOUU40.
They can be viewed by pointing Internet Explorer to http://houu40.hst.moc.com/okcdoc/.
Editing these files can be accomplished by FTPing them to a local Windows computer, modifying them and then FTPing them back to HOUU40.
-
The .ora_env file sets the environment variables needed to access Oracle.
-
This Oracle View resides in the OKCADMIN account of HOU_TOW. It draws well header information
from a variety of tables in TOW and presents it in a more usable manner. It is used primarily
by Darla Bierig in an SPCC (Spill Prevention, Control and Countermeasure) database that is
written in Microsoft Access.
-
This SQL script generates a series of SQL scripts that will create a series of Views in the
OKCADMIN account on HOU_TOW. These Views select all records from and have the same name
as the corresponding tables in the TOWADMIN account. This allows a Microsoft
Access database, originally written in the Oklahoma City office and pointing to the OKCADMIN
account to access the tables in the TOWADMIN
account without requiring all of the table names to be changed, thus breaking the queries that
refer to the original table name. The tables will only have to be re-linked with a different
ODBC driver and the functionality will be restored.
The SQL scripts to generate the views is shown below in 'create_okcadmin_tow_views.sql'.
-
These SQL scripts, edited from the output of 'generate_okcadmin_tow_views.sql' above, will
generate views in the OKCADMIN account on HOU_TOW that will allow a Microsoft Access
database that originally had tables linked to the OKCADMIN account to access the tables in
the TOWADMIN account without completely re-writing all of the Access queries. The Access
tables will be re-linked to these Views in OKCADMIN using a new ODBC driver instead.
|