Terramatch Manager

An Oracle Application Express Application for Data Management

Bryan Teahan

What is APEX?

Rapid Web application development tool for the Oracle database

• Requires only a web browser, limited programming experience

• Fast and Secure

• Fully supported by Oracle

• Free

http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html

Oracle Video

apex_demo.avihttp://www.oracle.com/pls/ebn/swf_viewer.load?p_shows_id

=6392594&p_referred=0&p_width=800&p_height=600

APEX Release 4.1

• Goal to improve the development of database-centric Web 2.0 applications and reports

• Released August, 2011

• Available for download from Oracle Technology Network

• http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html#new_in_41

New Features in Release 4.1

Error Handling Use of ROWID Data Upload

Calendar Websheets Tabular Forms

Plug-Ins Dynamic Actions Accessibility

Terramatch Manager

• Integrate, standardise and match data from different organisations

• Links addresses and parcels to valuation and title information

• Requirement for maintenance process for data matching

• Core System at Terralink

• Underpins all of Terralink’s products and services

• Critical to our business

Terramatch Manager

• Old system 10 years ago in Oracle Forms

• Migration 5 years ago to Shell-scripts using PL/SQL – not user friendly

• Requirement to move to GUI friendly system with minimal development and using existing code – APEX was the solution

Menu System

• Drop down or select options

Reports

Query Menu

Valuation Report

Title Report

Integration with Terraview

Terraview showing NZ

Terraview showing Wellington

Terraview showing Road Layer

Terraview showing Imagery

Terraview showing address and cadastral layer

Terraview showing Imagery and cadastral layer

Terraview showing street layer

Terraview at Parcel Level

Terraview Link to Apex Reports

High Resolution Imagery with links to APEX – aiding match

APEX report of all parcels in Terraview Screen

APEX report showing addresses on a parcel

Resolve form for matching – Link to Terraview

Resolve showing parcels matched to valuation

Resolve showing nearby matches

Terramatch Manager Development Environment

Lists

Breadcrumbs

Customised Buttons

Example of Report Code – PL/SQL block

Example PL/SQL code

Linking code between reports

Code to link to Terraview

Terraview Linking Function – ActiveXObject – Shell run

Terraview Delphi Code to link to APEX

• Uses the Windows ShellExecute API command to force the url to open in a browser. The URL is forced to open in IE as opposed to the system default browser, as IE seems to work better with APEX

if (TApex.GetInstance.UseIE) then

res := ShellExecute(0, 'open', 'iexplore', PChar(url), nil, SW_SHOWNORMAL)

else

res := ShellExecute(0, 'open', PChar(url), nil, nil, SW_SHOWNORMAL);

• The function will open a different APEX report depending on the menu button

if (Sender = ViewAPEXParcelExtent) then

page := TApex.GetInstance.page[20];

url := Format(apex, [Format(page, [XMin, YMin, XMax, YMax])]); OpenURL;

Terraview INI Code to link to APEX

• The URL hotlink that is created uses a .INI file setting or a hard-coded default URL location

[Apex]TVP.Path=\\Mistral\TERRAVIEW_HOME\Terraview.exe

URL=http://cyclone.local.terralink:7778/apex/f?p=111:%s

URL.Parcel=5:::NO:5:P5_PARCEL_ID:%d

URL.Title=17:::NO:17:P17_LAND_DISTRICT,P17_REFERENCE:%s,%s

URL.Valuation=16:::NO:16:P16_VALREF:%s

URL.Address=4:::NO:4:P4_PARCEL_ID:%d

MaxReports=10

Use.IE=1

Benefits

• Intuitive

• Easier to use

• Used existing code

• Quick development time

• Cost savings

• Corporate-wide deployment

• New releases simple, transparent to users

APEX.ORACLE.COM

APEX Plug-Ins

The framework can be extended

• Item Type

• Region Type

• Process Type

• Dynamic Action Type

• Community Plug-In Repositories (http://www.apex-plugin.com, http://skillbuilders.com/apexplugins )

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-41-new-features-459652.html#plugins

APEX Documentation and Tutorials

Extensive documentation and tutorials

• Full library

• Release notes

• Installation guide

• Developer’s guide

• Application builder user’s guide

• Migration guide

• SQL workshop guide

• API Reference

• Advanced Tutorials guide

http://www.oracle.com/technetwork/developer-tools/apex/documentation/index.html

APEX Packaged Applications

• Packaged applications and sample code have been removed because they no longer follow recommended coding practices and security.

• A select number of these applications are being revised and will include:

New Features

Timesheets

Asset Manager

Task Manager

Events

Employee Directory lookup

• Sample Objects from 2 day guide is included

http://www.oracle.com/technetwork/developer-tools/apex/application-express/packaged-apps-090453.html

APEX Forum

https://forums.oracle.com/forums/forum.jspa?forumID=137

Thank You

Questions?