This project involved developing an automated interface between the UniClaim (UCP) system and the Policy Data Mart (PDM). This UCP to PDM interface allowed all policy information needed to establish a Business Owner Policy (BOP) loss on the UCP system to be added to UCP through an automated process. The project further developed the PDM to a single repository used to store all commercial and personal lines business. This centralized database provided any external source that needs to obtain complete details on a policy.

Responsibilities:

Responsible for requirements gathering, analysis, design and development of any enhancements in the application.

Created and maintained database objects like Tables, Views, Materialized views, and Synonyms to support business requirements.

Developed Stored Procedures, Functions, Triggers and Packages to meet the business requirements and update the existing objects based on change requests.

Utilized database links to accommodate cross-database transactions.

Extensively used PL/SQL Collections and Records, Partition methods for oracle tables and indexes.

Worked with Bulk Collect to improve the performance of fetching data.

Performed Oracle Performance tuning using SQL_TRACE and EXPLAIN PLAN.

Implemented table partitioning and sub-partitioning to improve performance and data management.

Handled errors using Named System Exceptions such as INVALID_NUMBER, NO_DATA_FOUND and PRAGMA EXCEPTION_INIT.

Created aggregate materialized views in Data Mart.

Designed and developed data loading processes using PL/SQL and UNIX Shell scripts.

Involved in developing ETL Process to load the data from different sources.

Used SQL*Loader and external table to successfully massive data migration.

Used AUTONOMOUS_TRANSACTION PRAGMA to log the status when error occurred.

The BI solution will help the customer to define and setup a shared service environment for BI to get a greater return on existing information assets through better control, fewer data inconsistencies and greater alignment with business users. The engagement defined best practices for OLAP/reporting architecture and reduced BI proliferation through standardization of business intelligence products in the organization.

Responsibilities:

Gathered development requirements from client’s functional team and functional design specification documents.

Created logical and physical database objects - table spaces, tables, indexes, referential integrity constraints, and triggers to development needs.

Responsible for the development of pipelined functions using ref cursors and PL/SQL stored procedures as per client requirement.

Construct complex queries to classify customers based on the location and allocate a biller.

Optimized the Oracle PL/SQL queries by Performance Tuning.

Fine-tuning of PL/SQL Stored Procedures using Hints in EXPLAIN PLAN.

Authored the technical design documents for the given functional design.

Responsible for developing technical components for the following Oracle Applications modules: Custom Char Resolve, Conversion Resolve, Upc Resolve, Scope Resolve, View/SubTotal Resolve, Hierarch Resolve.

Run batch jobs for loading database tables from flat files using SQL* Loader.

Tested codes developed by self and team.

Fixed bugs while in production with top priority.