Oracle Portland Resume
Title
Oracle Portland
Primary Skills
Oracle PL/SQL Data Analysis Data Modeling
Location
US-OR-Portland
Posted
Apr-22-09
RESUME DETAILS
SUMMARY
I have 22+ years of Oracle experience, nearly all as an analyst, developer, and consultant, and two with MS SQLServer. My greatest strengths are my abilities to conceptualize and model systems, analyze data for relational integrity, and develop smooth and attractive user interfaces. I am an excellent writer and speaker, and was awarded two creative writing prizes in college.
SKILLS
Hardware and Operating Systems
IBM and compatible PC's
Sun Unix workstations
Sun, HPUX and Aix servers
MS Windows All Versions, MS-DOS and Windows Server
DEC VMS
Programming Languages
Oracle SQL and PL/SQL
Microsoft SQL Transact-SQL
Pro*C
Pro*Cobol
Pro*Fortran
Databases and Tools
Oracle RDBMS
Oracle Designer and Developer 2000
Visio
Erwin
SQLNavigator and Toad
SQLServer 2000
CVS and SourceSafe
Application Areas
Manufacturing
Transportation and Distribution
Petroleum Geology and Distribution
Health Care and Group Insurance
E-Mail Marketing
Data Warehousing, Data Marts,
Client Tracking, Commissions Computation and Payment, Sales Analysis,
Data Acquisition
ERP Systems
Oracle Accounts Receivable Data Model, Interfaces, and Application
Oracle Contracts Data Model, Interfaces, and Application
Oracle General Ledger Data Model and Interfaces
Oracle Inventory, Bill of Materials, Project Accounting and Fixed Assets Data Model
WORK EXPERIENCE
Client: Standard Insurance
May 2007 to July 2008
Data Analyst/Programmer
I developed two major software tools for The Standard. The company has a major client who provides nightly “delta” transactions capturing changes to its membership database. The client has placed table triggers that write the “after image” of every transaction to a set of “delta” tables which are extracted nightly and transferred to The Standard. I wrote a set of k-shell scripts and a complex 18 table SQL*Loader script to load the data into Oracle staging tables and then apply the deltas to the mirrored copy of the database maintained by The Standard. The delta application system is a self-configuring package uses interrogation of the system catalog and generation of dynamic SQL set operations to apply and verify the delta transactions.
The second system I created compares the data stored in two instances having the same schema design and automatically generates “delta” transactions to bring the target schema into agreement with a newer one. The Standard uses this to verify periodically that all transactions have been transmitted from the client and applied properly to the mirror database. It is also used once a year to generate a set of deltas representing the net result of a high-volume “end of year” process for which the client has chosen to inactivate the delta triggers, in order not to slow its process.
Client: YesMail
September 2006 to April 2007
Data Specialist
I processed custom data loads and wrote automations for repetitive processes. The environment was Oracle 8i because the mailing engine used by YesMail, BroadVision, required it. It was quite a challenge working with the terrible Oracle8 optimizer on un-partitioned tables of hundreds of millions of records.
The technologies were Oracle 8i, SQL*Plus and Toad, and Korn shell.
Client: Nike, Inc
February to August 2006
Data Warehouse ETL Developer
I enhanced several existing SQL scripts to process several new countries which had just been added to the SAP system for Nike. I also did some performance tuning of views and MERGE statements processing large volumes of data. During my last three months at Nike I analyzed data mismatch occurrences between the Apparel Product Design Windchill database and the Decision Support System data warehouse.
The technologies were Oracle 9i and 10g, SQLNavigator 4, SQL*Plus, and Windchill.
Client: Wells Fargo Bank
October 2005 to February 2006
Database Programmer (contract)
I worked to revamp an existing data model for a new project. As a primary task identified weaknesses in the existing model and produced a more fully controlled design using ERwin. There were a large number of foreign key references omitted from the previous iteration which I have added and I normalized two designed sections of the system. I then produced a Transact-SQL batch script to apply the changes to the database.
Client : Qsent Technologies
May to August 2005
Database Programmer
I was assigned to re-engineer an existing Oracle Forms 6i application to add the ability to make “adjustments” to usage statistics at client request. When I had completed that work I was given two ETL tasks for the Qsent system of record.
The first was to load Social Security Administration “Issuance Period” data from Experian and then to apply monthly updates from the Social Security website to it. The second was to load US Postal Service Zip/City relationship data from a vendor who preprocesses the Postal Service's cryptic mainframe style output into a format suitable for database pre-processing.
Client: General Electric-- Supra Division
July 2004 to May 2005
Sr. Consultant (contract)
Supra Corporation has been the leading vendor for shared security solutions for the real estate industry for nearly half a century. GE bought the company about three years ago and as a result of its policy of supporting Oracle Financials exclusively for enterprise accounting, Supra's accounting system has been brought into OFA. I was engaged as data administrator for an Oracle Contracts application used to bill real estate agents for their electronic key access service.
The custom programs which were written to interface the Supra key control systems to Oracle Financials were not written in a robust manner and have given rise to many data problems, which I spent about five months correcting as I learned the programs in preparation for re-factoring them.
Client: Columbia Ultimate Business Systems -- Ajent System
June 2003 to March 2004
Sr. Consultant
Columbia Ultimate Business Systems provides software for the collections industry. They have an industry standard legacy product hosted on the Pick system which they are replacing with a Microsoft SQL Server hosted three-tier product. They also plan to support Oracle and DB2 databases, and I was hired to port the SQL Server product to Oracle. However, the SQL Server version has proven so problematic that I have spent nearly all of my time writing Transact-SQL code, and have become fairly proficient at it.
Client: The Regence Group
August to November 2002
Sr. Developer (contract)
The Regence Group has been working to implement a new vendor-supplied membership and claims system for several years. As a part of that project, the company needed a data mart to store transaction detail for use by internal audit and compliance staff. My portion of the work was to automate the operation of the loading programs created by another Solutions IQ consultant using Unix shell scripting and PL/SQL accessing the IBM mainframe to obtain the extract files via FTP. I also corrected reports produced by a previous contractor.
Client: US Department of Agriculture
February 2001 to July 2002
DBA/Database Developer (contract)
I was initially contracted for two months with the Forest Service at the Regional Ecosystem Office to make enhancements to the Interagency Species Management System, a database for tracking endangered species in the area of the Northwest Forest Plan. I completed the enhancements to
the Oracle Forms application in about one month, but before that month was completed, the project DBA got a promotion to a different development group. Because the ISMS team needed a DBA my contract was extended to the end of June, 2001 and then again through October, 2001. During that time I did relatively simple functional DBA work, produced a Designer 2000 repository for the ISMS system, and continued with enhancements to the main and related ISMS systems.
In September the team received a requirement to build a system to store the results of vegetation research surveys. I was given the task of developing the database and interface for that system. I created an Oracle Forms 6i form for its interface and wrote a large body of PL/SQL packages and triggers to support the complex data model required for the research database.
After completing that project I was engaged to develop a similar research database for legacy data for an area larger than the ISMS project covers. For that project I had to develop a set of custom
loader tables for each of six ecology areas within Region 6 of the Forest Service. Because each area used its own data model, I could not load the data directly into the permanent tables, but instead had to develop a custom ETL script using PL/SQL for each of them. There were similarities, of course, because the data was largely the same, but very different treatments of it. To support the loaders I also built four fairly large packages to provide public query and privileged DML services to the database. These packages include query modules which use the Restrict_References pragma to allow use in "standard" SQL statements.
Client: Emery Worldwide
October 2000 to January 2001
Sr. Oracle Developer
Emery Worldwide is CNF's lead component in a large project to outsource GM's inbound and outbound vehicle logistics. I was engaged to build Oracle Forms for the user interfaces in the system. I was also assigned to be a PL/SQL programmer, and completed a set of over a dozen sample forms using a tabbed canvas and "navigator" trees which are served as the example to which the HTML programmers coded.
Client: Ashford.com, Houston, TX
June 2000
Oracle Consultant
Online retailer Ashford.com has a project to convert its e-commerce application from Sybase SQL Server to Oracle 8i. As a part of the conversion I was to convert the non-normal data model on which the existing application runs to a normalized model using ERWin and write data loaders and PL/SQL programs to populate the normalized Oracle model from the Sybase extraction.
When I arrived I noticed that there were many duplicated entities in the proposed Oracle model. Since they would have been a maintenance problem I suggested further normalizations that were accepted.
Client: CNF Transportation
April 1997 to May 2000
Sr. Oracle Consultant
I was responsible for the design and implementation of the interfaces between Con-Way's legacy billing and revenue accounting systems and Oracle Accounts Receivable. Because the Oracle system is product oriented and Con-Way sells a service, this was more than the simple development of loaders and PL/SQL processes. There is no “inventory item” to track, so most of the attributes of the invoice transaction lines must be built programmatically.
Together with a Con-Way employee, I analyzed the convoluted legacy system and identified the point at which to extract the data. This is a continuing process executed daily, not a one-time data load; it is analogous to a data warehouse ETL. I designed the interfaces for transactions and the customer conversion and built stored packages which populate the interface tables.
To feed its legacy system, CNF had developed a customized cash applications system designed to optimize the needs of a transportation provider. In order to maintain the speed and business customizations in that system, I built a complex custom system for cash applications using the 32-bit version of Oracle Forms. The system has a full set of over forty tables into which remittance advice applications are keyed by users and into which EDI transmissions are imported and held until the remittance to pay for them is received. The data tables generally mirror the Oracle cash application tables, but there are batch tracking tables which more closely a data warehouse model.
In addition, because CNF uses bank Lockbox processing for checks having as many as two thousand applications, the standard Oracle Lockbox process is completely inadequate. There is no "quick cash rule" which can handle volumes like that, and in the transportation industry it is a custom from ICC days only to apply remitted cash to the shipment directed by the customer. So, the system allows the clerks to key the remittance detail for lockbox batches for which no bank detail is available, but passes those for which "scanned" detail has been captured by the bank directly through to the Oracle system. In all cases Oracle Lockbox processing is used to "promote" the receipts from the custom system into the permanent Oracle A/R tables.
The system is used to process between 55,000 and 75,000 invoice applications per day, depending on the season, and validates the applications against the Oracle A/R database of over 30 million invoices. Bills are not purged from the A/R system for two years because customers have a legal right to challenge a bill for that period after shipment. The system uses indexing techniques similar to those used in data warehouse searches. It is so fast that one remarkable data entry clerk has several times applied more than 1,400 remittances in one hour.
The last seventeen months of my work for CNF was spent maintaining the cash system, developing a series of extensions to standard Oracle Accounts Receivable functionality required by the industry, and mentoring the CNF permanent staff in the Oracle Applications and database.
Client: Wells Fargo Bank
Jan 1997 to March 1997
Oracle Consultant
As the chief analyst and UNIX implementer, I made several improvements to the Wells Fargo Bank's Wire transfer transaction system. This system had experienced bottlenecks in its primary data feed from the mainframe to the UNIX/Oracle database. I moved an account purge which often exceeded its processing window to the weekend to remove it from the nightly critical path. I also recommended placing temporary indices on two journal tables, which were purged in the process. As a result of those simple changes the processing time fell from 2-1/2 hours to 12 minutes.
Second, the transaction purge often had to be restarted ten or fifteen times during a night by the operations staff. I converted the delete statements from a ROWID based purge to simple DELETE FROM